Changes between Version 9 and Version 10 of UserGuidelines/Admin/Upgrade


Ignore:
Timestamp:
06/23/12 14:58:47 (9 years ago)
Author:
Fran Boon
Comment:

merge

Legend:

Unmodified
Added
Removed
Modified
  • UserGuidelines/Admin/Upgrade

    v9 v10  
    11= Upgrades =
     2[[TOC]]
    23
    34== Fabfile ==
     
    5657 * MaintenanceGuidelines
    5758
     59= Data Migration =
     60We now have a nifty script to help a lot with this process on MySQL.
     61The 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
     65This 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{{{
     87python web2py.py -S eden -M -N
     88db.export_to_csv_file(open('db.csv','wb'))
     89quit()
     90}}}
     91
     92=== 2: Drop the Database ===
     93
     94MySQL:
     95{{{
     96mysql -u root -p
     97drop DATABASE sahana;
     98create DATABASE sahana;
     99GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,INDEX,ALTER ON sahana.* TO 'sahana'@'localhost' IDENTIFIED BY 'password';
     100\q
     101rm -rf databases/*
     102}}}
     103
     104SQLite:
     105{{{
     106rm -rf databases/*
     107}}}
     108
     109=== 3: Upgrade the Codebase ===
     110{{{
     111cd applications/eden
     112bzr pull
     113cd ../..
     114}}}
     115
     116=== 4: Import Data ===
     117
     118 1. Set the following in {{{models/000_config.py}}}
     119{{{
     120deployment_settings.base.prepopulate = False
     121}}}
     122
     123 2. Create the db structure via shell:
     124{{{
     125cd /path/to/web2py
     126python web2py.py -S eden -M -N
     127quit()
     128}}}
     129
     130 3. Fix permissions:
     131{{{
     132chown www-data:www-data applications/eden/databases/*
     133}}}
     134
     135 4. Import the existing data into the database
     136  * If using MySQL:
     137{{{
     138mysql -u root -p
     139\u sahana
     140\. sahana.sql
     141\q
     142}}}
     143  * If using sqlite then use the CSV export:
     144{{{
     145python web2py.py -S eden -M -N
     146db.import_from_csv_file(open('db.csv','rb'))
     147db.commit()
     148quit()
     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 ==
     157Can't do a live migration on MySQL & get an error like this?
     158{{{
     159OperationalError: (1025, "Error on rename of './prod/#sql-372_f2' to './prod/drrpp_project' (errno: 150)")
     160}}}
     161It's likely a problem with a foreign key...can see more information on the error through:
     162{{{
     163SHOW ENGINE INNODB STATUS;
     164}}}
     165Look for the section 'LATEST FOREIGN KEY ERROR'...you should see something like:
     166{{{
     167100615  0:27:09 Error in foreign key constraint of table prod/drrpp_project:
     168there is no index in the table which would contain
     169the columns as the first columns, or the data types in the
     170table do not match to the ones in the referenced table
     171or 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}}}
     175So to resolve this, need to:
     176{{{
     177ALTER TABLE drrpp_project DROP FOREIGN KEY drrpp_project_ibfk_2;
     178}}}
     179You'll need to remove the failed attempt to ALTER_TABLE from the bottom of {{{databases/sql.log}}} & maybe also delete the Index.
     180
     181phpMyAdmin 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
     183Can completely disable checks using:
     184{{{
     185SET foreign_key_checks = 0;
     186}}}
     187
     188== Other examples ==
     189Here are some examples of Data migration used on the Haiti instance, some of which are more-generally applicable:
     190 * DataMigration
     191
     192
    58193----
    59194
    60195BluePrintUpgrades
    61196
    62 UserGuidelines
     197UserGuidelines/Admin