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


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

--

Legend:

Unmodified
Added
Removed
Modified
  • UserGuidelines/Admin/Upgrade

    v10 v11  
    5656
    5757 * MaintenanceGuidelines
    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
    19259
    19360----