MySQL Import Database Error because of Extended Inserts

Just edit the dump file so that after 300 inserts you start a new statement:

INSERT INTO `myTable` (`id`, `field1`, `field2`) VALUES
    (1, 'a', 'b'),
    (2, 'c', 'd'),
    (3, 'e', 'f'),
    -- snip --
    (300, 'w', 'x');
INSERT INTO `myTable` (`id`, `field1`, `field2`) VALUES
    (301, 'y', 'z'),
    (302, ... etc

You can do this by just opening your <backup>.sql file in any text editor, it's just plain text. Sometimes a backup might be a .gz or .bz file, but they're just zips: if you decompress these gzipped or bzipped files, you'll end up with a single .sql file which you can edit easy.

It probably wouldn't be too hard to write a script to split each statement after an arbitrary number of lines. The basic program logic would look like this, perhaps:

1. find a line that starts with "INSERT".
2. copy the line up until the end of the word "VALUES"
3. step through all the following lines, counting up as you go.
4. if your counter reaches 300, then add a semicolon to the end of the last line and paste in the "INSERT ... VALUES" statement from before. Reset your counter to 1 and go to step 3.
5. if you reach a line that starts with "INSERT", reset the counter to 1 and go to step 2.

Though, you could just do this one manually, and then make sure that all future backups don't use extended inserts. Also, phpMyAdmin lets you set a maximal statement size when creating an export, so it'll do the above for you, basically.

Though, what you did with the memory limit is probably a much easier solution. For anyone else in a similar situation who can't change those limits, try the above.

For me it worked as follows:

Search for

$max_query_lines = 300;

then simply replaced to

$max_query_lines = 3000;

You don't need to make any changes to .sql file: just open bigdump.php in any text editor, find the line

define ('MAX_QUERY_LINES',300);

and replace 300 with anything you want.

I was able to import the database successfully after increasing the memory buffer and doing an ordinary mysql import via SSH.

I used the following command with the absolute path:

mysql -u <username> -p < /home/dir/dumpfile.sql

Best Nick