0

I am attempting to export the Magento database from my current installation to a test installation before I have the test installation go live. Both installations are version 1.9.2.1. I can successfully export the database. I've tried two methods of importing it. The first via SSH after uploading it via FTP to the server. The next via phpMyAdmin after exporting it as a gzip file first. I have to do it via a gzip file for phpMyAdmin because the database is way too big otherwise. For both SSH and phpMyAdmin, I get this same error with trying to import the database...

SQL Import Error

When done via SSH, the line in the picture under "My SQL said:" is what appears. phpMyAdmin seems to show a little more info. I have no idea what this error is all about. I emptied all of the logs from the tables as well as the admin notifications table. I just can't understand where this error is coming from.

Am I missing something? Do I need to delete all of the tables from the target database before importing the one from my live site? I feel like there's something simple that I'm overlooking or not doing correctly.

0

Yes, faced this issue quite often while migration of Magento databases. You need to check & remove all the duplicate entries from the SQL file manually. You could also look for the mg_catqalog_category_entity_decimal from phpMyAdmin and check for the foreign key checks.

  • Is there an easy way to do this? – Lucas Sep 24 '15 at 12:50
0

The query that's failing is an UPDATE which is probably triggered by one of the constraints already existing in the DB you're importing to.

Like you have in the comments on your other question, How to Move Products, Attributes, Images, and Customers From One Magento Installation to Another, you need to clear all the tables from the DB on the target server, before loading in the new dump.

DROP DATABASE $DB_NAME;
CREATE DATABASE $DB_NAME;

Another thing that you can do, although not specific to the error at hand but might be helpful, is to temporarily disable FKs; see https://stackoverflow.com/a/15501754/64944

P.S. Make sure you have proper backups before dropping anything ;)

  • I backed everything up because I figured I'd mess something up. First of all, I went into phpMyAdmin and attempted to drop the database. I received a message stating that the "drop database" command was disabled. So, I manually dropped all of the tables and left the database empty. I then attempted to import a gzipped version of the database from my production Magento site. It said it imported successfully, yet it looks like all of my tables have no values in them at all. – Lucas Sep 24 '15 at 12:47
  • So, I then attempted to import a non-zipped version of the database via SSH and get this error... ERROR 1064 (42000) at line 378722: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '<! <div class="error"><h1>Error</h1> <p><strong>SQL query:</strong> <a href=' at line 1 – Lucas Sep 24 '15 at 12:47
  • Try to clean everything up between attempts as previous failed attempts will mess your feng-shuey. Check what's at line 378722 in the dump file (read this before you attempt to open the file in an editor: stackoverflow.com/questions/6022384/…), maybe it will provide a clue as to what's going on. If I were to take a very wild guess, I would say there are some trigger definitions or variable changes for which your user doesn't have permissions. – Adi Sep 24 '15 at 12:57
  • I tried this and seems successful. However, importing the database has royally screwed up my theme. Should I install all of the same extensions on the test site before I import the database? I noticed after importing, all of my custom pages appeared on the test site which overwrote the custom pages for theme on the test site. – Lucas Sep 24 '15 at 17:00
  • Yes, if you want the same database on both environments, you should run the same code too so you should install all extensions on the test environment then probably you'll need to re-import the database as well. – Adi Sep 25 '15 at 8:24

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.