1

I am doing the export from Magento database on one instalation via phpMyAdmin, and I have tried exporting it in .sql and in tar.gz.

When I import that database in another instalation, I get no errors but there are tables missing, so I have only half of the tables and I see that after letter L (log_visitors table) there are no tables.

Its very strange, could someone give me idea what is it about?

Oleg Ishenko
  • 2,243
  • 1
  • 15
  • 16
xul
  • 210
  • 5
  • 15
  • Are you sure the tables are missing? Some versions of phpMyAdmin paginate the tables overview and won't show all tables at one page – thaJeztah Feb 06 '13 at 22:00
  • yep the tables are missing, i get this http://metalhouse.devpassion.eu/ and when I look into mysql there are again only 255 tables imported.. – xul Feb 07 '13 at 12:39
  • It also mentions this error: `The PDO extension is required for this adapter but the extension is not loaded`. Usually I manage to migrate a Magento database by; enclosing it in a transaction and disable foreign-key-checks during the import (these options can be set for MySQL dumps). Large databases should be imported via the commandline because PhpMyadmin often times out – thaJeztah Feb 07 '13 at 13:03
  • Years later this is still an issue. I've found that by default Magento stores 180 days worth of visitor logs. Which causes the sql dump to fail on the log table. mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table `mg_log_visitor` at row: 535319 Change the log settings: [1] clearing-magneto-log-data [http://stackoverflow.com/questions/3638361/clearing-magento-log-data] What for the cron to run, then dump a new.sql file. – Michael Garito Jan 02 '17 at 04:53

3 Answers3

2

On my version of phpMyAdmin, there is a checkbox with the following text on the Import page:

Allow the interruption of an import in case the script detects it is close to the PHP timeout limit. (This might be good way to import large files, however it can break transactions.)

Make sure that checkbox is unchecked (by default it is checked), else it may be causing your problem.

Willem Renzema
  • 5,177
  • 1
  • 17
  • 24
1

So we turned on the "enclose in transaction" and "disable foreign keys" checkfields on phpMyAdmin while exporting, and also seperated export in two .sql files. It worked.

Note that the problem wasnt standard php timeout or standard phpMyadmin limitations on uploading big files, as we previousuly also tried bigdumb and ssh commands for import and it didnt worked.

xul
  • 210
  • 5
  • 15
0

As @Willem stated, you could have that option checked when you import the sql file.

But if you're still not be able to import it after unchecking that option, this means that the file you are importing is too big. In this case you can use the mysql command line utility, if you have SSH access to your server or you can try bigdump, a php script that let's you import a database splitting the SQL query in chunks!

Hope it helps :)

endorama
  • 498
  • 7
  • 15
  • Tried this, doesnt work. Also tried to used BigDump sw and it just wont import whole sql. Ive tried many options while exporting, tried export it as tar gz, wont work.. :( – xul Feb 07 '13 at 12:40
  • How big is the file?? Also, have you tried to import it in a local environment where you can control max_upload_size and max_post_size? So that you can exclude the file dimension has the source of the problem... – endorama Feb 21 '13 at 17:01
  • We seperated exports in two .sql files and uploaded then. It worked. While exporting, we turned on the "enclose in transaction" and "disabled foreign keys" checkfields on phpMyAdmin. – xul Feb 22 '13 at 12:03
  • Glad you have solved! Please accept your own answer, to carify how this problem was solved... cheers :) – endorama Feb 22 '13 at 14:26