[Cosmo-dev] mysql migration script checked in

[Randy Letness]

Jared Rhine wrote:
> * 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.
The  problem I ran into was that I had some tables in MyISAM, and some 
tables in InnoDB and the script choked when trying to create a foreign 
key constraint between a MyISAM and InnoDB table.  MyISAM doesn't 
support this.  The reason we require InnoDB is that we want foreign key 
constraints, and more importantly, we want full transaction support.  
Cosmo will work against MyISAM tables, but if exceptions occur its 
possible to get the database into an invalid state because all the data 
in the transaction won't be rolled back.

> 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.

I can change the scripts to create the new tables using InnoDB.  On a 
new installation, we really on Hibernate's auto schema generation, which 
does not support setting the table type.  That's the reason you need to 
set the default for the database (not the server as a whole) to InnoDB.

> * 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.

+1

>
> * 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?
>

I can remove the utf version.  I was using the Query Browser as an 
editor and it always wanted to save in utf8.

> * 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.
>

Yes good idea.

> * 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.
>

For the proposed 0.6.1 sharing release, there probably will be changes.

>> It should work for the most part, but there will probably be bugs.
>
> What are the most likely areas where bugs might arise?
>

Most likely in minor schema differences (length of fields, correct 
indexes, constraints, etc.).


-Randy