Changes between Initial Version and Version 1 of UserGuidelines/Admin/DataMigration

06/23/12 15:00:31 (10 years ago)
Fran Boon



  • UserGuidelines/Admin/DataMigration

    v1 v1  
     1= Data Migration =
     2We now have a nifty script to help a lot with this process on MySQL.
     3The script includes lots of detailed documentation steps:
     4 * {{{static/scripts/tools/}}}
     5== Importing existing copy of the database into a fresh installation ==
     7This is needed for some incompatible Schema modifications, such as changing the length of the UUID field ([wiki:InstallationGuidelinesMySQL MySQL]-only for this - SQLite is fine).
     9'''Warning use the following only if'''
     10 * You have a SQL dump of the data.
     11 * You are aware of the schema changes for the updated code.
     13=== 1: Export Data ===
     15 1. Turn off the Webserver and Cron to make sure no other instance of web2py is hooking into the database.
     17/etc/init.d/apache2 stop
     18/etc/init.d/cron stop
     21 2. Take a data-only dump of the SQL database.
     22  * For MySQL, disable "foreign key checks" - '''Note''': Do not include the structure of the database[[BR]]
     23  The following screen shot shows the settings for exporting data via phpMyAdmin (for MySQL users):
     25  [[Image(phpmyadmin-dump-sql.png)]]
     27  * For SQLite users, dump the database using CSV:
     29python -S eden -M -N
     34=== 2: Drop the Database ===
     38mysql -u root -p
     39drop DATABASE sahana;
     40create DATABASE sahana;
     43rm -rf databases/*
     48rm -rf databases/*
     51=== 3: Upgrade the Codebase ===
     53cd applications/eden
     54bzr pull
     55cd ../..
     58=== 4: Import Data ===
     60 1. Set the following in {{{models/}}}
     62deployment_settings.base.prepopulate = False
     65 2. Create the db structure via shell:
     67cd /path/to/web2py
     68python -S eden -M -N
     72 3. Fix permissions:
     74chown www-data:www-data applications/eden/databases/*
     77 4. Import the existing data into the database
     78  * If using MySQL:
     80mysql -u root -p
     81\u sahana
     82\. sahana.sql
     85  * If using sqlite then use the CSV export:
     87python -S eden -M -N
     93 5. Turn back on the Webserver and Cron.
     95/etc/init.d/apache2 start
     96/etc/init.d/cron start
     98== Foreign Key problems when doing live migrations on MySQL ==
     99Can't do a live migration on MySQL & get an error like this?
     101OperationalError: (1025, "Error on rename of './prod/#sql-372_f2' to './prod/drrpp_project' (errno: 150)")
     103It's likely a problem with a foreign key...can see more information on the error through:
     107Look for the section 'LATEST FOREIGN KEY ERROR' should see something like:
     109100615  0:27:09 Error in foreign key constraint of table prod/drrpp_project:
     110there is no index in the table which would contain
     111the columns as the first columns, or the data types in the
     112table do not match to the ones in the referenced table
     113or one of the ON ... SET NULL columns is declared NOT NULL. Constraint:
     115  CONSTRAINT drrpp_project_ibfk_2 FOREIGN KEY (drrpp_contact_id) REFERENCES drrpp_contact (id)
     117So to resolve this, need to:
     119ALTER TABLE drrpp_project DROP FOREIGN KEY drrpp_project_ibfk_2;
     121You'll need to remove the failed attempt to ALTER_TABLE from the bottom of {{{databases/sql.log}}} & maybe also delete the Index.
     123phpMyAdmin can show all FK relationships & allow you to clear them: under table structures there is a link to 'relation view', as well as a list of the Indexes.
     125Can completely disable checks using:
     127SET foreign_key_checks = 0;
     130== Other examples ==
     131Here are some examples of Data migration used on the Haiti instance, some of which are more-generally applicable:
     132 * DataMigration