| 58 | | |
| 59 | | = Data Migration = |
| 60 | | We now have a nifty script to help a lot with this process on MySQL. |
| 61 | | The script includes lots of detailed documentation steps: |
| 62 | | * {{{static/scripts/tools/dbstruct.py}}} |
| 63 | | == Importing existing copy of the database into a fresh installation == |
| 64 | | |
| 65 | | 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). |
| 66 | | |
| 67 | | '''Warning use the following only if''' |
| 68 | | * You have a SQL dump of the data. |
| 69 | | * You are aware of the schema changes for the updated code. |
| 70 | | |
| 71 | | === 1: Export Data === |
| 72 | | |
| 73 | | 1. Turn off the Webserver and Cron to make sure no other instance of web2py is hooking into the database. |
| 74 | | {{{ |
| 75 | | /etc/init.d/apache2 stop |
| 76 | | /etc/init.d/cron stop |
| 77 | | }}} |
| 78 | | |
| 79 | | 2. Take a data-only dump of the SQL database. |
| 80 | | * For MySQL, disable "foreign key checks" - '''Note''': Do not include the structure of the database[[BR]] |
| 81 | | The following screen shot shows the settings for exporting data via phpMyAdmin (for MySQL users): |
| 82 | | |
| 83 | | [[Image(phpmyadmin-dump-sql.png)]] |
| 84 | | |
| 85 | | * For SQLite users, dump the database using CSV: |
| 86 | | {{{ |
| 87 | | python web2py.py -S eden -M -N |
| 88 | | db.export_to_csv_file(open('db.csv','wb')) |
| 89 | | quit() |
| 90 | | }}} |
| 91 | | |
| 92 | | === 2: Drop the Database === |
| 93 | | |
| 94 | | MySQL: |
| 95 | | {{{ |
| 96 | | mysql -u root -p |
| 97 | | drop DATABASE sahana; |
| 98 | | create DATABASE sahana; |
| 99 | | GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,INDEX,ALTER ON sahana.* TO 'sahana'@'localhost' IDENTIFIED BY 'password'; |
| 100 | | \q |
| 101 | | rm -rf databases/* |
| 102 | | }}} |
| 103 | | |
| 104 | | SQLite: |
| 105 | | {{{ |
| 106 | | rm -rf databases/* |
| 107 | | }}} |
| 108 | | |
| 109 | | === 3: Upgrade the Codebase === |
| 110 | | {{{ |
| 111 | | cd applications/eden |
| 112 | | bzr pull |
| 113 | | cd ../.. |
| 114 | | }}} |
| 115 | | |
| 116 | | === 4: Import Data === |
| 117 | | |
| 118 | | 1. Set the following in {{{models/000_config.py}}} |
| 119 | | {{{ |
| 120 | | deployment_settings.base.prepopulate = False |
| 121 | | }}} |
| 122 | | |
| 123 | | 2. Create the db structure via shell: |
| 124 | | {{{ |
| 125 | | cd /path/to/web2py |
| 126 | | python web2py.py -S eden -M -N |
| 127 | | quit() |
| 128 | | }}} |
| 129 | | |
| 130 | | 3. Fix permissions: |
| 131 | | {{{ |
| 132 | | chown www-data:www-data applications/eden/databases/* |
| 133 | | }}} |
| 134 | | |
| 135 | | 4. Import the existing data into the database |
| 136 | | * If using MySQL: |
| 137 | | {{{ |
| 138 | | mysql -u root -p |
| 139 | | \u sahana |
| 140 | | \. sahana.sql |
| 141 | | \q |
| 142 | | }}} |
| 143 | | * If using sqlite then use the CSV export: |
| 144 | | {{{ |
| 145 | | python web2py.py -S eden -M -N |
| 146 | | db.import_from_csv_file(open('db.csv','rb')) |
| 147 | | db.commit() |
| 148 | | quit() |
| 149 | | }}} |
| 150 | | |
| 151 | | 5. Turn back on the Webserver and Cron. |
| 152 | | {{{ |
| 153 | | /etc/init.d/apache2 start |
| 154 | | /etc/init.d/cron start |
| 155 | | }}} |
| 156 | | == Foreign Key problems when doing live migrations on MySQL == |
| 157 | | Can't do a live migration on MySQL & get an error like this? |
| 158 | | {{{ |
| 159 | | OperationalError: (1025, "Error on rename of './prod/#sql-372_f2' to './prod/drrpp_project' (errno: 150)") |
| 160 | | }}} |
| 161 | | It's likely a problem with a foreign key...can see more information on the error through: |
| 162 | | {{{ |
| 163 | | SHOW ENGINE INNODB STATUS; |
| 164 | | }}} |
| 165 | | Look for the section 'LATEST FOREIGN KEY ERROR'...you should see something like: |
| 166 | | {{{ |
| 167 | | 100615 0:27:09 Error in foreign key constraint of table prod/drrpp_project: |
| 168 | | there is no index in the table which would contain |
| 169 | | the columns as the first columns, or the data types in the |
| 170 | | table do not match to the ones in the referenced table |
| 171 | | or one of the ON ... SET NULL columns is declared NOT NULL. Constraint: |
| 172 | | , |
| 173 | | CONSTRAINT drrpp_project_ibfk_2 FOREIGN KEY (drrpp_contact_id) REFERENCES drrpp_contact (id) |
| 174 | | }}} |
| 175 | | So to resolve this, need to: |
| 176 | | {{{ |
| 177 | | ALTER TABLE drrpp_project DROP FOREIGN KEY drrpp_project_ibfk_2; |
| 178 | | }}} |
| 179 | | You'll need to remove the failed attempt to ALTER_TABLE from the bottom of {{{databases/sql.log}}} & maybe also delete the Index. |
| 180 | | |
| 181 | | 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. |
| 182 | | |
| 183 | | Can completely disable checks using: |
| 184 | | {{{ |
| 185 | | SET foreign_key_checks = 0; |
| 186 | | }}} |
| 187 | | |
| 188 | | == Other examples == |
| 189 | | Here are some examples of Data migration used on the Haiti instance, some of which are more-generally applicable: |
| 190 | | * DataMigration |
| 191 | | |
| | 58 | * UserGuidelines/Admin/DataMigration |
