okay, after looking at this more and more I am starting to think that it would probably be too hard to try and ensure that all roller mysql users are using InnoDB tables.
if you first install roller with MyISAM tables then all the constraints are basically useless. they will be created, but don't actually do anything. you *can* update the table type to InnoDB pretty easily using "alter table foo TYPE=INNODB;", however this will not transfer any constraints, so what you end up with is a roller db that is missing all the constraints :( this basically means that upgrading users would be really tough. what i would suggest instead is that beginning with the 2.0 install guide we add a section about setting the default table type for mysql to ensure users get InnoDB tables. we won't actually explicitly set the table type in our sql scripts which means that we are leaving a bit of the responsiblity up to whoever is deploying roller, but we can try and offer some guidance via the install guide. anyone have any other feelings about this? -- Allen On Wed, 2005-09-21 at 11:23, Allen Gilliland wrote: > 4.1.10-standard on Solaris 10 ... defaults to MyISAM. > > you're right that if the db is set to use InnoDB by default then the tables > will be created properly using InnoDB, however that is up to the db owner. > > "If you omit the ENGINE or TYPE option, the default storage engine is used. > Normally this is MyISAM, but you can change it by using the > --default-storage-engine or --default-table-type server startup option, or by > setting the storage_engine or table_type system variable." > > that's from the mysql documentation. MyISAM seems to still be the default, > so i'm not sure how you got your tables to be InnoDB. > > even if InnoDB becomes the default at some later point in time I think it's > ideal to explicitly set the table type to InnoDB, that way there's no room > for error. then if someone wants to actually hack the Roller sql scripts and > force MyISAM or some other table type then they can do that at their own risk. > > It also looks like you can change table types simply by doing "alter table > foo TYPE=InnoDB;" I've tested this with mysql 4.1.x on a small test install > and it worked fine. I still want to try it on mysql 4.0.x and with some > large data sets to make sure though. > > -- Allen > > > On Tue, 2005-09-20 at 23:17, Anil Gangolli wrote: > > That's odd. > > > > What version of MySQL are you running? > > > > I've been using InnoDb, transactions, rollback etc. since starting to > > use Roller with MySQL (4.1.7). There is no change required to the > > createdb script if your database is setup to use innodb as the default > > engine. > > > > I think InnoDB became the "default default" (set as default storage > > engine by default in the installation) in MySQL 4.1.5 at least on > > Windows, and I thought also on Linux/Unix. > > > > My own MySQL 4.1.7 Linux Roller 1.2 installation is running on it, and I > > don't remember having done anything special. (And my my.cnf only sets > > default-character-set=UTF8, nothing else). > > > > If running MySQL 4.1.5 or higher, try this check on yours (connect using > > the mysql client program, then at its prompt type): > > > > mysql> show table status from roller like 'rolleruser'; > > > > Here "roller" is the db name, which may differ in yours. Look at the > > Engine column in the result. Mine is "InnoDB". > > > > --a. > > > > Allen Gilliland wrote: > > > > >I have been thinking about the same thing and it's definitely a little > > >complicated. > > > > > >I agree that making the createdb script use InnoDB starting with Roller > > >2.0 is a good start, but then you create an opportunity for things to > > >get out of sync. i.e. the 3.0 upgrade script would have to use InnoDB > > >tables, so old MyISAM users would be facing the same mixed table type > > >upgrade situation. > > > > > >I think the best thing is to offer a MyISAM to InnoDB script which can > > >be run independently of the 2.0 upgrade script. > > > > > >-- Allen > > > > > > > > >On Tue, 2005-09-20 at 13:49, Matt Raible wrote: > > > > > > > > >>After thinking about this a bit more - is this really a good idea? It > > >>might really screw up existing users. Migrating from an existing > > >>database that uses MyISAM to InnoDB might be pretty tough. I would > > >>say that the createdb.sql script use InnoDB, but leave it out of > > >>upgrade scripts. > > >> > > >>Matt > > >> > > >>On 9/20/05, Elias Torres <[EMAIL PROTECTED]> wrote: > > >> > > >> > > >>>+1 > > >>> > > >>>On 9/20/05, Allen Gilliland <[EMAIL PROTECTED]> wrote: > > >>> > > >>> > > >>>>team, > > >>>> > > >>>>how do folks feel about making the mysql schema default to using InnoDB > > >>>>type mysql tables? which table type to choose is a pretty complex > > >>>>issue, but the big bonus for InnoDB is that it offers referential > > >>>>integrity. right now a default install of Roller on mysql will yield > > >>>>all MyISAM tables, which means that our foreign keys mean nothing. > > >>>>InnoDB would help fix this. > > >>>> > > >>>>what do others think? should we make InnoDB the default table type? > > >>>> > > >>>>-- Allen > > >>>> > > >>>> > > >>>> > > >>>> > > > > > > > > > > > > > > >
