Package home | Report new bug | New search | Development Roadmap Status: Open | Feedback | All | Closed Since Version 2.5.0b5

Bug #11831 getDefinitionFromDatabase() fails if table has two primary index columns
Submitted: 2007-08-13 10:46 UTC Modified: 2008-03-11 14:59 UTC
From: floele Assigned: quipo
Status: Closed Package: MDB2
PHP Version: Irrelevant OS: Irrelevant
Roadmaps: (Not assigned)    
Subscription  


 [2007-08-13 10:46 UTC] floele (Florian Schmitz)
Description: ------------ Hi again. When I try to upgrade a database (MySQL) that contains the following table (even though the table is not modified and doesn't need to be, it's completely unrelated to the database tables I am dealing with), getDefinitionFromDatabase() fails: CREATE TABLE `lum_comment` ( `CommentID` int(8) NOT NULL auto_increment, `DiscussionID` int(8) NOT NULL default '0', `AuthUserID` int(10) NOT NULL default '0', `DateCreated` datetime default NULL, `EditUserID` int(10) default NULL, `DateEdited` datetime default NULL, `WhisperUserID` int(11) default NULL, `Body` text, `FormatType` varchar(20) default NULL, `Deleted` enum('1','0') NOT NULL default '0', `DateDeleted` datetime default NULL, `DeleteUserID` int(10) NOT NULL default '0', `RemoteIp` varchar(100) default '', PRIMARY KEY (`CommentID`,`DiscussionID`), KEY `comment_user` (`AuthUserID`), KEY `comment_whisper` (`WhisperUserID`), KEY `comment_discussion` (`DiscussionID`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=37 ; with the following error: MDB2_Schema Error: schema validation error: there was already an primary index or autoincrement field in "LUM_Comment" before "PRIMARY" The problem seems to be in Validate.php, line 230. To fix the problem, I'd replace } elseif ($primary) { with } elseif ($primary && count($index['fields']) == '1') { Regards, Florian

Comments

 [2007-08-13 15:23 UTC] ifeghali (Igor Feghali)
Hi Florian, are you running the latest CVS version ?
 [2007-08-13 23:34 UTC] floele (Florian Schmitz)
Yes. I just updated at all my files to be sure, but the error is still the same.
 [2007-08-14 05:07 UTC] ifeghali (Igor Feghali)
so you are talking about line 231 right ?
 [2007-08-14 05:50 UTC] floele (Florian Schmitz)
Yes, right.
 [2007-08-15 12:36 UTC] ifeghali (Igor Feghali)
I would need Lorenzo help here again. MDB2 automatically creates a primary key for auto increment fields (MySQL) so we would need to drop the primary key and then recreate it (with all the columns) in a single query. for example: ALTER TABLE `lum_comment` DROP PRIMARY KEY , ADD PRIMARY KEY ( `commentid` , `discussionid` ); I may be wrong but it seems that MDB2 can't do that at the moment.
 [2007-08-15 12:49 UTC] floele (Florian Schmitz)
Yep, I think you are wrong. As I said in my first post, I do *not* intend to change/alter/update this table *at all*. It doesn't even belong to my application. Its existence alone causes the upgrade to fail, not the fact that MDB2 can't alter it.
 [2007-08-15 13:45 UTC] ifeghali (Igor Feghali)
Hi Florian, This is a *bug* and is supposed to be fixed, not worked around for your particular case. Cheers, iGor.
 [2007-08-15 13:52 UTC] floele (Florian Schmitz)
It certainly is a bug. However, while you seem to be concerned about being able to alter such double primary columns, this bug is about the problem that the validation of an irrelevant database table fails and thus causes the whole upgrade to fail. Even if MDB2 supported those doubled indices, the validation would still fail. So I'd say supporting those indices (creation, alteration) and fixing this bug are two different issues.
 [2008-01-30 09:26 UTC] ifeghali (Igor Feghali)
Florian, It seems that I wasn't clear enough... *If* I let your table validates the way you suggested, I will end up with a XML like that: <table> <name>Packages</name> <declaration> <field> <name>id</name> <type>integer</type> <default>0</default> <notnull>true</notnull> <autoincrement>1</autoincrement> <unsigned>true</unsigned> <length>4</length> </field> [...] <index> <name>Packages_pKey</name> <primary>true</primary> <field> <name>id</name> <sorting>ascending</sorting> </field> <field> <name>name</name> <sorting>ascending</sorting> </field> </index> </declaration> </table> Trying to load that XML into the database will debug: query(1): DROP DATABASE `MDB2Example` createDatabase(1): Overwritting database: MDB2Example query(1): CREATE DATABASE `MDB2Example` beginTransaction(1): Starting transaction/savepoint query(1): START TRANSACTION query(1): SHOW /*!50002 FULL*/ TABLES/*!50002 WHERE Table_type = 'BASE TABLE'*/ query(1): CREATE TABLE `Packages` (`id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, `name` VARCHAR(128) DEFAULT '' NOT NULL, `summary` LONGTEXT NOT NULL, `creation` DATE DEFAULT NULL) ---------------------- AT THAT POINT MYSQL ALREADY CREATED A PRIMARY FOR FIELD ID ---------------------- query(1): SHOW INDEX FROM `Packages` query(1): SHOW CREATE TABLE Packages query(1): ALTER TABLE `Packages` ADD PRIMARY KEY (`id`, `name`) We are going to fail here because we already have a primary key. To fix that we would have to do either: 1) full create table statement, including indices or 2) ALTER TABLE `Packages` DROP PRIMARY KEY, ADD PRIMARY KEY (`id`, `name`) I would go with number 2 because we already have createTable() and createTableIndexes() as separated methods. Lorenzo is it possible to perform queries such as (2) with MDB2? If so please set the bug back to MDB2_Schema. If not, do you have any suggestion ? regards, iGor.
 [2008-03-11 14:59 UTC] quipo (Lorenzo Alberton)
I've added support for tables with a multi-field PRIMARY KEY where one field is defined as AUTO_INCREMENT in createTable(). Igor, make sure that Florian's concerns are addressed in MDB2_Schema too, from his comments it's evident that it's not just a problem with MDB2.