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


Ignore:
Timestamp:
06/23/12 15:00:31 (10 years ago)
Author:
Fran Boon
Comment:

--

Legend:

Unmodified
Added
Removed
Modified
  • 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/dbstruct.py}}}
     5== Importing existing copy of the database into a fresh installation ==
     6
     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).
     8
     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.
     12
     13=== 1: Export Data ===
     14
     15 1. Turn off the Webserver and Cron to make sure no other instance of web2py is hooking into the database.
     16{{{
     17/etc/init.d/apache2 stop
     18/etc/init.d/cron stop
     19}}}
     20 
     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):
     24
     25  [[Image(phpmyadmin-dump-sql.png)]]
     26
     27  * For SQLite users, dump the database using CSV:
     28{{{
     29python web2py.py -S eden -M -N
     30db.export_to_csv_file(open('db.csv','wb'))
     31quit()
     32}}}
     33
     34=== 2: Drop the Database ===
     35
     36MySQL:
     37{{{
     38mysql -u root -p
     39drop DATABASE sahana;
     40create DATABASE sahana;
     41GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,INDEX,ALTER ON sahana.* TO 'sahana'@'localhost' IDENTIFIED BY 'password';
     42\q
     43rm -rf databases/*
     44}}}
     45
     46SQLite:
     47{{{
     48rm -rf databases/*
     49}}}
     50
     51=== 3: Upgrade the Codebase ===
     52{{{
     53cd applications/eden
     54bzr pull
     55cd ../..
     56}}}
     57
     58=== 4: Import Data ===
     59
     60 1. Set the following in {{{models/000_config.py}}}
     61{{{
     62deployment_settings.base.prepopulate = False
     63}}}
     64
     65 2. Create the db structure via shell:
     66{{{
     67cd /path/to/web2py
     68python web2py.py -S eden -M -N
     69quit()
     70}}}
     71
     72 3. Fix permissions:
     73{{{
     74chown www-data:www-data applications/eden/databases/*
     75}}}
     76
     77 4. Import the existing data into the database
     78  * If using MySQL:
     79{{{
     80mysql -u root -p
     81\u sahana
     82\. sahana.sql
     83\q
     84}}}
     85  * If using sqlite then use the CSV export:
     86{{{
     87python web2py.py -S eden -M -N
     88db.import_from_csv_file(open('db.csv','rb'))
     89db.commit()
     90quit()
     91}}}
     92
     93 5. Turn back on the Webserver and Cron.
     94{{{
     95/etc/init.d/apache2 start
     96/etc/init.d/cron start
     97}}}
     98== Foreign Key problems when doing live migrations on MySQL ==
     99Can't do a live migration on MySQL & get an error like this?
     100{{{
     101OperationalError: (1025, "Error on rename of './prod/#sql-372_f2' to './prod/drrpp_project' (errno: 150)")
     102}}}
     103It's likely a problem with a foreign key...can see more information on the error through:
     104{{{
     105SHOW ENGINE INNODB STATUS;
     106}}}
     107Look for the section 'LATEST FOREIGN KEY ERROR'...you should see something like:
     108{{{
     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:
     114,
     115  CONSTRAINT drrpp_project_ibfk_2 FOREIGN KEY (drrpp_contact_id) REFERENCES drrpp_contact (id)
     116}}}
     117So to resolve this, need to:
     118{{{
     119ALTER TABLE drrpp_project DROP FOREIGN KEY drrpp_project_ibfk_2;
     120}}}
     121You'll need to remove the failed attempt to ALTER_TABLE from the bottom of {{{databases/sql.log}}} & maybe also delete the Index.
     122
     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.
     124
     125Can completely disable checks using:
     126{{{
     127SET foreign_key_checks = 0;
     128}}}
     129
     130== Other examples ==
     131Here are some examples of Data migration used on the Haiti instance, some of which are more-generally applicable:
     132 * DataMigration
     133