Differences

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

--- tutorials:server:db_restore [2009/03/07 15:57]
jonyo
+++ tutorials:server:db_restore [2014/09/25 16:55] (current)
@@ Line 17: / Line 17: @@
 <tip c w>**Important:** Ensure that your .sql file contains DROP TABLES for each table within the code. Failure to do so will result in error messages being displayed in phpMyAdmin. If you followed the steps in the "Backing up Your Database" Tutorial, then your .sql file should have drop tables in the code. See example below of what the first line in each table should look like.</tip>
-droptable
+{{ :tutorials:server:droptable.gif |Drop Table}}
->
+<
   - Log in to phpMyAdmin.
@@ Line 25: / Line 25: @@
   - Select the database name you want to restore from the dropdown.
-myadmin_7
+{{ :tutorials:server:myadmin_7.gif |}}
 <
   - When the screen refreshes you will see a list of tables that are currently present within the database you just selected. The same set of table names will display on both sides of the screen. (If this is a new database, or if you have previously 'dropped' the database tables, you will not see any.) You will see several tabs at the top of the screen. Click on the "Import" tab.
@@ Line 31: / Line 31: @@
 <tip c n>**Note:** Older versions of phpMyAdmin will not have an "import" tab. Instead, you will need to click on the "SQL" tab, as it will be your only option for importing SQL.</tip>
-myadmin_8
+{{ :tutorials:server:myadmin_8.gif |}}
 <
-  - You should now see a screen that looks similar to the image below. Click on the "Browse" button and upload the first your 5 or 6 .sql files you created in step 1 above using the popup window that appears. Once you have selected the file, click the "Go" button to upload the file. In a few moments you should see the screen refresh and the first set of SQL tables will be entered into the database. Repeat this process until you have uploaded all of your .sql files in order.
+  - You should now see a screen that looks similar to the image below. Click on the "Browse" button (#5) and upload the first your 5 or 6 .sql files you created in step 1 above using the popup window that appears. Once you have selected the file, click the "Go" button (#6) to upload the file. In a few moments you should see the screen refresh and the first set of SQL tables will be entered into the database. Repeat this process until you have uploaded all of your .sql files in order.
-myadmin_9
+{{ :tutorials:server:myadmin_9.gif |}}
 <
   - You should now be able to log in to the software's admin panel using your admin username and password. If you are unable to, you can reset the admin username and password as described in the "Reset Admin Password" tutorial.
@@ 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