Differences

This shows you the differences between two versions of the page.

--- tutorials:server:db_restore [2009/03/07 16:23]
jonyo
+++ tutorials:server:db_restore [2014/09/25 16:55] (current)
@@ Line 42: / Line 42: @@
 <
+===== Working Through Problems When Running Queries =====
+We have had a bit of experience importing databases and can provide a few pointers.
+Break your export into several "bite size" pieces so that each file doesn't exceed the "max file size" displayed within the "file import" feature of the Phpmyadmin tool you are using.  Then make sure to import these files in order as they were in the one original file.  There could have been a "table create" statement created in a previous file allowing subsequent files to insert into properly.
+The geodesic_classifieds table is the the hardest to import.  It is usually the largest table especially on larger sites but the main problem is that table contains user inserted content.  Almost all problems we encounter in this table are in the description field of this table.  What we have done in the past is run a file (as broken up as suggested above).  If you see an error go to the database table currently in your database and find the last sql query run.  Basically go to the end of the table and see what the last listing id is (this is the "id" field) in that table.  Return to your import file and go to the next insert query that failed.  Hopefully these are sequential by the "id" field.  At this point you can do a couple things.
+You can remove this query from this import file and place it within a specific place to be run later individually.  This may be the best way to get through the bulk of the import.  Depends on what you feel for your situation but our experience shows there to be few queries with a problem.  If there are many queries like this you may just need to stop and deal now with the error or stopping every few queries can cost more time.  If you decide to set it aside for future debugging set aside this query (cut and paste out of this file) in a "to do later" file.  You may need to add other queries to this file as you work through the import.  Once this query has been removed from the import file you will need to remove all previously successfully run queries in the same import file.  If you try these same queries again the database will not be corrupted in most cases but you will see an "already exists in table" error.  The previous holds true in the case of tables that have keys/primary keys where there can be no duplicates.  If the table you are working on does not have a unique key field within it you may need to delete that specific table and retract in the import file to the point that table creation statement and start importing from that point on (removing all queries before the table creation statement needing to be run).
+You can also choose to deal with the query error at this point before going further into the import.  You will certainly have to deal with the error now if the specific query causing the problem is a "table creation" statement.  Run the problem query individually.  You usually can see better where the problem is if the query is run individually.  Most of the problems happen in user inserted content.  At this point you may need to make a judgment call.  Many times just changing the user submitted  content a bit so that the meaning/intent is still there is what's needed.  You may also do a search of the problem string through the queries not already run to see if you can do a simple search and replace to fix the problem in many queries.  Hopefully the problem you see is unique.
+If you stopped the import process to deal with a specific sql query make sure to remove all "already run" queries before you run the same import file before you start the import process again.
+You may not decide to run the query at all.  You have to do a little investigation and possibly a question or two to support.  We suggest you run all sql queries to make sure you have a complete duplicate of the state of the database at the time the backup was taken.  But if the query you are trying to run if for a listing (geodesic_classifieds table) that is about to be archived (at 30 days expired by default) or a listing that has been archived (geodesic_classifieds_expired table) for years.  This is a judgment call we suggest that you question support about.

Geodesic Solutions Community Wiki

User Tools

Site Tools

Differences

Page Tools