Data Migration

There is a script available which can automate a lot with this process on MySQL:

• static/scripts/tools/dbstruct.py

The script includes lots of detailed documentation steps.

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 (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
     The following screen shot shows the settings for exporting data via phpMyAdmin (for MySQL users):

• 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

Note: As of January 2012, BZR/Launchpad info for eden is deprecated. Please visit the GitHub page. Thanks.

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