5

I have a 47 GB MySQL dump of a single table:

http://dumps.wikimedia.org/commonswiki/latest/commonswiki-latest-image.sql.gz

I ultimately want it into PostgreSQL, but since I didn't figure out an easy way to transform the MySQL SQL to PostgreSQL SQL I figured, I will get it into MySQL and then write a small ETL script to do this.

I initially tried to just load it using MySQL WorkBench Data Import/Restore, but it was failing.

Now I've run split -l 2000 commonswiki-latest-image.sql and have 20 files of roughly 2 GB each, but it still fails with:

23:12:28 Restoring C:\temp\commonswiki\xaa.sql

Running: mysql.exe --defaults-extra-file="c:\users\jmurdoch\appdata\local\temp\tmpi_ltz8.cnf"  --host=127.0.0.1 --user=root --port=3306 --default-character-set=utf8 --comments --database=mediawikiimages < "C:\\temp\\commonswiki\\xaa.sql"

ERROR 2013 (HY000) at line 331: Lost connection to MySQL server during query


Operation failed with exitcode 1

00:54:17 Import of C:\temp\commonswiki\xaa.sql has finished with 1 errors

It's also terribly slow as it only imported 209236 rows during the nearly 2 hours, but I think there are about 20M items to import, so at that rate it would take 200 hours to import.

I'm using Windows 7, MySQL 5.6.14 and MySQL Workbench 6.

My main questions:

  • Is there a script which would feed MySQL with the splitted files and perform automatic error recovery in case it times out?

  • Is there a conversion tool which would convert MySQL SQL to PostgreSQL SQL without first loading it into a database?

  • Is this the schema you are using? – cerd Feb 11 '14 at 4:50
  • It should not take this long to load. Are you using InnoDB or myisam for these tables? How much RAM do you have on this machine? From show variables give us values for innodb_buffer_pool_size, innodb_write_io_threads etc. – cerd Feb 11 '14 at 4:57
  • Also see: stackoverflow.com/questions/1112069/… – cerd Feb 11 '14 at 4:57
  • I was using InnoDB with 16 GB RAM although I'm not sure how much MySQL was ready to use. I actually appear to have solved the issue through increasing the 'max_allowed_packet'. – John M Feb 11 '14 at 8:51
3

Using

max_allowed_packet = 256M

in MySQL config allowed to import the files without errors.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.