David, I'm still doing some more testing with the scripts, but there are definitely a few kinks still left to iron out. To be honest, if it's failing only when it gets the lines that drop/add the renamed constraints and indexes then your db should be fine.
unfortunately the way the script is setup right now it may have dropped all your indexes, then failed :( to fix this you'd just need to go into the script and run the relevant parts to re-add the indexes again. -- Allen On Wed, 2005-09-21 at 15:37, David J. Graff wrote: > Allen, > > I ran into this with the new constraints on the website table under > HSQL 1.8.0.x. If the table has any data in it, the SQL will not > function and kick out. I may have been doing this wrong too. > > Since I'm a bit of a n00b with respect to Roller, what is the > appropriate method of upgrading roller to the newer database? > > Thanks > > Allen Gilliland wrote: > > So, I've been doing some testing of the db upgrade procedure in preparation > > for the upcoming deployment of 2.0 to blogs.sun.com and I found that the > > UpgradeDatabase.upgradeTo200() application level upgrade process took me > > ~20 hrs to run with 1650 blogs :( > > > > I'm not sure why it was so slow to be honest, but I decided to try doing > > the same steps using pure sql and came up with this ... > > > > -- update website handles using existing usernames > > UPDATE website,rolleruser SET website.handle = rolleruser.username WHERE > > website.userid = rolleruser.id AND website.handle = ''; > > > > -- update PUBLISHED weblog entries with userids and status > > UPDATE website,rolleruser,weblogentry SET weblogentry.userid=rolleruser.id, > > weblogentry.status='PUBLISHED' WHERE website.userid=rolleruser.id AND > > website.id=weblogentry.websiteid AND weblogentry.userid = '' AND > > weblogentry.publishentry = 1; > > > > -- update DRAFT weblog entries with userids and status > > UPDATE website,rolleruser,weblogentry SET weblogentry.userid=rolleruser.id, > > weblogentry.status='DRAFT' WHERE website.userid=rolleruser.id AND > > website.id=weblogentry.websiteid AND weblogentry.userid = '' AND > > weblogentry.publishentry = 0; > > > > -- populate new permissions table > > INSERT into roller_user_permissions(id, website_id, user_id, > > permission_mask, pending) SELECT concat(website.id, > > 'p'),website.id,rolleruser.id,3,0 FROM website,rolleruser WHERE > > website.userid=rolleruser.id; > > > > -- update db version property > > UPDATE roller_properties SET value = '200' WHERE name = > > 'roller.database.version'; > > > > I can commit these changes back to the upgrade script, but I wasn't sure if > > this sql would work on all databases. I've only tested on mysql 4.1.10 so > > far. > > > > I'm guessing most dbs would allow for the multi-table update statements, > > but the Insert ... Select statement may not be supported. Anyone else want > > to give this a stab on any other dbs they use and see if we should make any > > of these part of the standard 2.0 upgrade script? > > > > -- Allen > > > > > > > > > >
