[Cosmo-dev] mysql migration script checked in

[Jared Rhine]

Randy Letness wrote:
> I just checked in the first pass at a Cosmo 0.5 to 0.6 migration 
> script.

Great!

I've tested the script against a recent snapshot of the osaf.us 
production data.

Steps:

* create a new instance based on Cosmo r3002, pointed at db cosmo_r3002
* start and test the r3002 instance
* copy the last regular backup of osaf.us's db (*.sql file); bunzip it
* create a new blank database: cosmo_r3002_mig
* import backup's *.sql into cosmo_r3002_mig
* run ANSI migration script on cosmo_r3002_mig
* stop test instance
* update instance server.xml to point to cosmo_r3002_mig
* start test instance
* login as jared, see my 3 collections are there

Migration output:

-----

jared at lab-hibernate:/home/test$ mysqladmin -uroot create cosmo_r3002_mig
jared at lab-hibernate:/home/test$ time mysql -uroot cosmo_r3002_mig < 
cosmo_dogfood_02.sql

real    0m12.502s
user    0m0.630s
sys     0m0.050s
jared at lab-hibernate:/home/test$ time mysql -uroot cosmo_r3002_mig < 
/home/cosmos/r3002/cosmo/migration/db/mysql/0.5-to-0.6-ansi.sql
attributes processed    @counter
attributes processed    449
calendars processed     @counter
calendars processed     165
events processed        @counter
events processed        5988

real    2m1.966s
user    0m0.010s
sys     0m0.000s

-----

So, no reported errors, 6000 events in osaf.us, took 2 minutes to do the 
migration, and the post-migrated instance starts.

Notes:

* The migrated instance is available at http://lab.osaf.us:7001/ I've 
done no real testing yet but anyone is welcome to use this instance to 
think about the migration testing method we're developing.

* The migration README says errors will occur unless InnoDB is the 
default mysql db.  This isn't the case; default mysql installs often 
default to MyISAM (like Debian etch, which we're running).  I didn't 
change the default, and no errors were generated.

In general, if it's possible to have the migration scripts set InnoDB 
for temp tables if needed, that'd be wise.  Not all admins will feel 
comfortable changing their database defaults, as Cosmo may not be the 
only application running on that db server.

This probably extends to actual table creation during initial creation. 
  I know I saw a warning somewhere that tables should be InnoDB (or 
other transactional type).  I dunno if Hibernate supports setting the 
table type, but if it does, it's worth considering.  Again, changing 
whole-server defaults has some drawbacks.  Cosmo starts and runs 
reliably (YMMV) with MyISAM.

* I assume this migration script, as it uses procedures, means that only 
MySQL 5+ is supported.  I think Cosmo will work against MySQL 4, but as 
procedures were only added in 5, we should note MySQL 5.x as the minimum 
supported version.

* What's up with the ANSI and UTF-8 versions?  The README mentions the 
two versions' existence, but no reason why someone would use the UTF-8 
version in preference to the ANSI one?

* A general pattern which might be helpful to incorporate is checking 
for allowed version numbers.  Check if cosmo.schemaVersion is '0.5.0' 
and abort if not true.  Pros and cons here.

* There's some pros and cons to switching to explicit schema versions 
(ala "1", "2", etc).  People who install 0.6.0 will get that as a 
version, 0.6.1 will get that, even though the schema is likely to be 
identical.

> It should work for the most part, but 
> there will probably be bugs.

What are the most likely areas where bugs might arise?

-- Jared