Description: ------------ I am trying to dump the schema of a wordpress database to an XML file as a test. The dump fails with the following error: there was already an primary index or autoincrement field in "wp_options" before "PRIMARY" The schema of the wp_options table is as follows: CREATE TABLE `wp_options` ( `option_id` bigint(20) NOT NULL AUTO_INCREMENT, `blog_id` int(11) NOT NULL DEFAULT '0', `option_name` varchar(64) NOT NULL DEFAULT '', `option_can_override` enum('Y','N') NOT NULL DEFAULT 'Y', `option_type` int(11) NOT NULL DEFAULT '1', `option_value` longtext NOT NULL, `option_width` int(11) NOT NULL DEFAULT '20', `option_height` int(11) NOT NULL DEFAULT '8', `option_description` tinytext NOT NULL, `option_admin_level` int(11) NOT NULL DEFAULT '1', `autoload` enum('yes','no') NOT NULL DEFAULT 'yes', PRIMARY KEY (`option_id`,`blog_id`,`option_name`), KEY `option_name` (`option_name`) ) ENGINE=MyISAM AUTO_INCREMENT=119 DEFAULT CHARSET=utf8 Looks like the MDB2_Schema_Validator does not like the multi-column primary key? Test script: --------------- require_once 'MDB2/Schema.php'; $dsn = 'mysql://root@localhost/WordPress'; $manager =& MDB2_Schema::factory($dsn, array('parser' => 'MDB2_Schema_Parser2')); if (MDB2::isError($manager)) { die($manager->getUserinfo()); } print '<h1>Reverse Engineering Database</h1>'; $def = $manager->getDefinitionFromDatabase(); print_r($def); print '<h1>Dumping to XML</h1>'; $oldxml = $manager->dumpDatabase($def, array('output_mode' => 'file','output' => 'dump.xml'), MDB2_SCHEMA_DUMP_STRUCTURE); var_dump($oldxml); Expected result: ---------------- The validation should pass and the schema should be dumped to the dump.xml file. Actual result: -------------- [0] => Array ( [file] => /pinf/packages/pear.MDB2_Schema/MDB2/Schema.php [line] => 2617 [function] => PEAR_Error [class] => PEAR_Error [type] => -> [args] => Array ( [0] => MDB2_Schema Error: schema validation error [1] => -3 [2] => 1 [3] => 1024 [4] => there was already an primary index or autoincrement field in "wp_options" before "primary" ) )

Hello Christoph, This is a known issue. MDB2_Schema creates indices after the table creation. MySQL requires an auto increment field to be a primary key this implying we are going to have a primary key with a single field at the table creation time. If we accept your primary key with multiple fields (one being an auto increment field) MDB2_Schema will try to create that constraint again which will fail. Instead, we should have a mechanism to detect that conflict and fix it dropping and adding a new primary key (with all the fields) in a single query. In that particular case it would be : ALTER TABLE `wp_options` DROP PRIMARY KEY , ADD PRIMARY KEY ( `option_id` , `blog_id` , `option_name` ) We would need some support in the MDB2 side as well. This bug is suspended until we get their fix done. regards, iGor.

I don't think the issue is with MDB2. All the correct meta information about the table is being passed to MDB2_Schema. I think the problem lies in the validation logic. I have uploaded a patch for your review. The patch solves the problem in my case and should be generic for other cases as well. If it does not meet all validation requirements let me know and I can expand.

Hello Christoph, Yes this is exactly the patch I have applied in MDB2_Schema. But as I said, we *need* some changes in MDB2 as well. Have you tried to create the database defined in the resulting XML ? You will get an error in MySQL. regards, iGor.

Ok I understand the problem now.

Igor, now MDB2 should support a PK constraint on multiple fields (even with one autoincrement field). If I understand the issue correctly, Christoph can't get it working because MDB2_Schema adds the indices and then the constraints, and treats a PK as an index. If that's true, then allow me to bring on something we've already discussed previously: a PK is a constraint, and not an index ;-)

Lorenzo, agreed. making PKs a constraint would require a re-design of MDB2_Schema, so it should be a feature request. regards, Igor.