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

Timestamp:

06/23/12 15:00:31 (13 years ago)

Author:

Fran Boon

Comment:

--

UserGuidelines/Admin/DataMigration

@@ +1 @@
+= Data Migration =
+We now have a nifty script to help a lot with this process on MySQL.
+The script includes lots of detailed documentation steps:
+ * {{{static/scripts/tools/dbstruct.py}}}
+== Importing existing copy of the database into a fresh installation ==
+
+This 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).
+
+'''Warning use the following only if'''
+ * You have a SQL dump of the data.
+ * You are aware of the schema changes for the updated code.
+
+=== 1: Export Data ===
+
+ 1. Turn off the Webserver and Cron to make sure no other instance of web2py is hooking into the database.
+{{{
+/etc/init.d/apache2 stop
+/etc/init.d/cron stop
+}}}
+ 
+ 2. Take a data-only dump of the SQL database.
+  * For MySQL, disable "foreign key checks" - '''Note''': Do not include the structure of the database[[BR]]
+  The following screen shot shows the settings for exporting data via phpMyAdmin (for MySQL users):
+
+  [[Image(phpmyadmin-dump-sql.png)]]
+
+  * For SQLite users, dump the database using CSV:
+{{{
+python web2py.py -S eden -M -N
+db.export_to_csv_file(open('db.csv','wb'))
+quit()
+}}}
+
+=== 2: Drop the Database ===
+
+MySQL:
+{{{
+mysql -u root -p
+drop DATABASE sahana;
+create DATABASE sahana;
+GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,INDEX,ALTER ON sahana.* TO 'sahana'@'localhost' IDENTIFIED BY 'password';
+\q
+rm -rf databases/*
+}}}
+
+SQLite:
+{{{
+rm -rf databases/*
+}}}
+
+=== 3: Upgrade the Codebase ===
+{{{
+cd applications/eden
+bzr pull
+cd ../..
+}}}
+
+=== 4: Import Data ===
+
+ 1. Set the following in {{{models/000_config.py}}}
+{{{
+deployment_settings.base.prepopulate = False
+}}}
+
+ 2. Create the db structure via shell:
+{{{
+cd /path/to/web2py
+python web2py.py -S eden -M -N
+quit()
+}}}
+
+ 3. Fix permissions:
+{{{
+chown www-data:www-data applications/eden/databases/*
+}}}
+
+ 4. Import the existing data into the database
+  * If using MySQL:
+{{{
+mysql -u root -p
+\u sahana
+\. sahana.sql
+\q
+}}}
+  * If using sqlite then use the CSV export:
+{{{
+python web2py.py -S eden -M -N
+db.import_from_csv_file(open('db.csv','rb'))
+db.commit()
+quit()
+}}}
+
+ 5. Turn back on the Webserver and Cron.
+{{{
+/etc/init.d/apache2 start
+/etc/init.d/cron start
+}}}
+== Foreign Key problems when doing live migrations on MySQL ==
+Can't do a live migration on MySQL & get an error like this?
+{{{
+OperationalError: (1025, "Error on rename of './prod/#sql-372_f2' to './prod/drrpp_project' (errno: 150)") 
+}}}
+It's likely a problem with a foreign key...can see more information on the error through:
+{{{
+SHOW ENGINE INNODB STATUS;
+}}}
+Look for the section 'LATEST FOREIGN KEY ERROR'...you should see something like:
+{{{
+100615  0:27:09 Error in foreign key constraint of table prod/drrpp_project:
+there is no index in the table which would contain
+the columns as the first columns, or the data types in the
+table do not match to the ones in the referenced table
+or one of the ON ... SET NULL columns is declared NOT NULL. Constraint:
+,
+  CONSTRAINT drrpp_project_ibfk_2 FOREIGN KEY (drrpp_contact_id) REFERENCES drrpp_contact (id) 
+}}}
+So to resolve this, need to:
+{{{
+ALTER TABLE drrpp_project DROP FOREIGN KEY drrpp_project_ibfk_2;
+}}}
+You'll need to remove the failed attempt to ALTER_TABLE from the bottom of {{{databases/sql.log}}} & maybe also delete the Index.
+
+phpMyAdmin 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.
+
+Can completely disable checks using:
+{{{
+SET foreign_key_checks = 0;
+}}}
+
+== Other examples ==
+Here are some examples of Data migration used on the Haiti instance, some of which are more-generally applicable:
+ * DataMigration
+