Methods to open huge .sql.gz file?

Question

I have huge 12 GB .sql.gz file that I need to unzip on my Windows 10 computer. So far, I have tried using WinZip, 7-Zip, and the gunzip command in Cygwin. I received the error messages shown below for each of those attempts.

I know that the error messages indicate that this file is invalid, but I'm not sure whether these messages are due to the fact that it is so large, or that the file is actually corrupt; the file download process appeared to complete successfully, although it is possible that even a corrupted file would not show any indication that the download process was corrupted.

When try to open with WinZip, the error message is:

Cannot open file 'file_v1.2.sql.gz'. It does not appear to be a valid archive.
If you downloaded this file, try downloading this file again.

When try to open with 7-Zip, the error message is:

Cannot open the file as [gzip] archive
Is not archive

When try to open in Cygwin using gunzip, the error message is:

gzip: file_v1.2.sql.gz: not in gzip format

I have been looking for additional potential solutions to this issue online, but so far haven't located any useful suggestions. If anyone has ideas about what might work, I appreciate your input.

You probably can't open it with WinZip/7Zip if it is just a sql dump (text) compressed with gzip. `gzip -d file_v1.2.sql.gz` should decompress your file if it is not corrupted. — sebasth, Aug 06 '17 at 17:03
Thanks for your responses: The output of "gzip -d file_v1.2.sql.gz" is: gzip: file_v1.2.sql.gz: not in gzip format Output of file file_v1.2.sql.gz in Cygwin: file_v1.2.sql.gz: ASCII text, with very long lines — Bob Loblaw, Aug 06 '17 at 19:38
Given the `file` result, my guess would be that despite the extension, this is not compressed, but is directly the SQL file. Have you tried opening it with an editor or maybe just outputting the first few lines (`head file_v1.2.sql.gz`) or characters if the lines are very long (`head -c 100 file_v1.2.sql.gz`)? — user2313067, Aug 06 '17 at 21:02
@user2313067 You are correct that I am able to directly view the content; specifically, I used UltraEdit to open it. I'm going to experiment to see whether I can perform operations to extract the content from this file format. — Bob Loblaw, Aug 07 '17 at 04:56
@user2313067 Yes, this file is viewable in UltraEdit; if I open it in .sql.gz form, it displays as plain text, but when I change the file extension to .sql as you suggested below, it appears as a colorful SQL file with keywords displayed in different colors. — Bob Loblaw, Aug 07 '17 at 17:20

score 0 · Answer 1 · answered Aug 06 '17 at 18:24

You could try Gzip for Windows, i.e., run it outside of Cygwin, though, I would suspect that if you received an error message under Cygwin, you would receive a similar error message again. Another alternative is the free Zip Reader from Pkware, which can uncompress a variety of compressed file types, including the gzip .gz files, though it doesn't provide any compression capabilities. You will be prompted to provide an email address to download it, but there's no need to confirm an email address; when you submit the form you are immediately taken to a page where you can download the software. You could also try WinRAR, since it can decompress .gz files.

A valid gzip file should have a 10-byte header containing a magic number (1f 8b), a version number and a timestamp. As a check on whether the file is a valid gzip file, you could check the file with a hexadecimal editor to see if the file has such a header. On Microsoft Windows systems, I use the Freeware Hex Editor XVI32, but unfortunately, due to the size of the file, that likely won't work in your case, since the developer states in its feature list "XVI32 allows to edit files up to 2 GB (enough virtual memory provided, of course)." UltraEdit can be used as a hexadecimal editor; the developer states "Regarding large files, UltraEdit handles files in excess of 4GB. UltraEdit is disk based. This means it only loads small portions of the file at once into memory so it does not use all the memory and stop other applications from running. However, it does make a temporary copy of the file to achieve this and this can take time for large files." I haven't used that product myself, but it is free for a 30-day trial period. If the file doesn't have a valid header, it probably can't be processed by any program that uncompresses .gz files.

Thanks for your awesome response; I am in the process of investigating the software you suggested. To follow up on the issue of checking the header: Displayed in UltraEdit, the file begins with the following content: -- MySQL dump 10.13 Distrib 5.6.24, for Linux (x86_64) -- -- Host: localhost Database: tcrd4 -- ------------------------------------------------------ -- Server version 5.6.24 /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; — Bob Loblaw, Aug 06 '17 at 19:44
@bobloblaw the heading you show indicates this is simply an sql file, despite it's extension. There is no compression here. — user2313067, Aug 06 '17 at 21:04
@user2313067 Thanks for the clarification. I'm not sure how to work with the file in .sql.gz format *without* first unzipping it, even if there is no compression, but I will keep your observation in mind as I move forward... — Bob Loblaw, Aug 06 '17 at 23:14
Is it a problem that this header content indicates that it is "for Linux", but I am using a Windows machine? — Bob Loblaw, Aug 06 '17 at 23:24
@bobloblaw The "for Linux" is part of the identification of the MySQL binary that made the dump. It has nothing to do with which is the dump is intended for. You should rename the script to .sql instead of .sql.gz to reflect its content. — user2313067, Aug 07 '17 at 05:02
@user2313067 Excellent! I just removed the .gz from the end of the filename, and now the file is displaying in UltraEdit as an SQL file rather than a plain text file. Thanks for your very helpful advice. — Bob Loblaw, Aug 07 '17 at 16:35

Methods to open huge .sql.gz file?

1 Answers1