database engines: 2 questions
Frank Van Damme
Recently I have installed a web application called mediamosa which uses
innodb as its storage engine. At first however, I didn't pay attention to
the fact that the database server wasn't actually configured to enable
innodb. During installation mediamosa installs its database scheme, creating
tables like with this query:
CREATE TABLE `mediamosa_data`.`aut_name` (
`app_id` int(10) unsigned default NULL,
`aut_name_id` int(10) unsigned NOT NULL auto_increment,
`aut_group_id` int(10) unsigned default NULL,
`aut_name` varchar(255) default NULL,
`aut_prefix` varchar(128) default NULL,
`aut_type` enum('DOMAIN','REALM','USER','USER_GROUP','APP') default
NULL,
PRIMARY KEY (`aut_name_id`),
UNIQUE KEY `uni_type_groupid_name_prefix`
(`app_id`,`aut_type`,`aut_name`,`aut_prefix`),
KEY `idx_groupid` (`aut_group_id`),
KEY `idx_type_groupid_name_prefix`
(`app_id`,`aut_type`,`aut_group_id`,`aut_name`,`aut_prefix`),
KEY `app_id` (`app_id`,`aut_name`,`aut_prefix`,`aut_type`)
) ENGINE=InnoDB AUTO_INCREMENT=83 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT
And two strange things happened.
1. most tables were created succesfully. They were created in MyISAM format,
MySQL did not refuse to execute the query but instead quietly selected
another storage engine. Why?
2. The above query failed because the indexes were too large. It seems that
in innodb there is a different maximum key length; just out of curiosity: is
this some sort of design decision?
If the creation of the keys had been succesful, we would never have noticed
the problem with the storage engine...
--
No part of this copyright message may be reproduced, read or seen,
dead or alive or by any means, including but not limited to telepathy
without the benevolence of the author.