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

Bug #7901 Schema Update Fails
Submitted: 2006-06-14 19:50 UTC
From: michael at apison dot com Assigned: lsmith
Status: Closed Package: MDB2_Schema (version 0.5.0)
PHP Version: 5.1.4 OS: Mandriva 2006
Roadmaps: (Not assigned)    
Subscription  


 [2006-06-14 19:50 UTC] michael at apison dot com (Michael Caplan)
Description: ------------ Hi there, I'm trying to get MDB2_Schema to update a table based on changes I made to the schema definition. In this case (see below), the diff a new column added, and a new column added to the primary key index. MDB2_Schema ends up running an invalid DDL statement: "CREATE INDEX primary_idx ON files ()" and then bombs. I've included a code snippet, the two schemas, and the generated backtrace. Any ideas? Thanks, Michael (BTW, I'm running MDB2 2.0.3 and MDB2_Driver_mysqli 1.0.3) Test script: --------------- /* @var $schema MDB2_Schema */ $schema = MDB2_Schema::factory($fw->db); /* @var $schema MDB2_Schema */ try { var_dump($schema->updateDatabase(ROOT . 'modules/files/db/schema.xml', ROOT . 'modules/files/db/schema_previous.xml')); } catch (Framework_Exception $e) { var_dump($e); } Schema previous ------------------ <?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE database SYSTEM "http://cvs.php.net/viewcvs.cgi/*checkout*/pear/MDB2_Schema/docs/MDB.dtd"> <database> <name>framework</name> <create>true</create> <overwrite>false</overwrite> <table> <name>files</name> <declaration> <field> <name>files_path</name> <type>text</type> <length>255</length> <notnull>true</notnull> <default>/</default> </field> <field> <name>files_name</name> <type>text</type> <length>255</length> <notnull>true</notnull> <default></default> </field> <field> <name>files_type</name> <type>integer</type> <unsigned>false</unsigned> <length>1</length> <notnull>true</notnull> <default>0</default> </field> <field> <name>files_data</name> <type>blob</type> <notnull>false</notnull> </field> <field> <name>files_mime</name> <type>text</type> <length>255</length> <notnull>false</notnull> <default></default> </field> <field> <name>files_right</name> <type>integer</type> <unsigned>false</unsigned> <notnull>true</notnull> <default>0</default> </field> <field> <name>files_modified</name> <type>timestamp</type> <notnull>true</notnull> <default>0000-00-00 00:00:00</default> </field> <field> <name>files_owner</name> <type>text</type> <unsigned>false</unsigned> <length>32</length> <notnull>true</notnull> <default>0</default> </field> <index> <name>primary</name> <primary>true</primary> <field> <name>files_path</name> <sorting>ascending</sorting> </field> <field> <name>files_name</name> <sorting>ascending</sorting> </field> <field> <name>files_type</name> <sorting>ascending</sorting> </field> </index> </declaration> </table> </database> Schema new ---------- <?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE database SYSTEM "http://cvs.php.net/viewcvs.cgi/*checkout*/pear/MDB2_Schema/docs/MDB.dtd"> <database> <name>framework</name> <create>true</create> <overwrite>false</overwrite> <table> <name>files</name> <declaration> <field> <name>files_path</name> <type>text</type> <length>255</length> <notnull>true</notnull> <default>/</default> </field> <field> <name>files_name</name> <type>text</type> <length>255</length> <notnull>true</notnull> <default></default> </field> <field> <name>files_type</name> <type>integer</type> <unsigned>false</unsigned> <length>1</length> <notnull>true</notnull> <default>0</default> </field> <field> <name>files_locale</name> <type>text</type> <length>5</length> <notnull>true</notnull> <default>en_CA</default> </field> <field> <name>files_data</name> <type>blob</type> <notnull>false</notnull> </field> <field> <name>files_mime</name> <type>text</type> <length>255</length> <notnull>false</notnull> <default></default> </field> <field> <name>files_right</name> <type>integer</type> <unsigned>false</unsigned> <notnull>true</notnull> <default>0</default> </field> <field> <name>files_modified</name> <type>timestamp</type> <notnull>true</notnull> <default>0000-00-00 00:00:00</default> </field> <field> <name>files_owner</name> <type>text</type> <unsigned>false</unsigned> <length>32</length> <notnull>true</notnull> <default>0</default> </field> <index> <name>primary</name> <primary>true</primary> <field> <name>files_path</name> <sorting>ascending</sorting> </field> <field> <name>files_name</name> <sorting>ascending</sorting> </field> <field> <name>files_type</name> <sorting>ascending</sorting> </field> <field> <name>files_locale</name> <sorting>ascending</sorting> </field> </index> </declaration> </table> </database> Expected result: ---------------- success! Actual result: -------------- object(Framework_Exception)#28 (6) { ["message:protected"]=> string(248) "MDB2 Error: syntax error _doQuery: Could not execute statement[Native code: 1064] [Native message: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 1] " ["string:private"]=> string(0) "" ["code:protected"]=> int(-2) ["file:protected"]=> string(51) "/home/michael/www/framework/lib/Framework/Error.php" ["line:protected"]=> int(291) ["trace:private"]=> array(13) { [0]=> array(4) { ["function"]=> string(10) "PEAR_Error" ["class"]=> string(15) "Framework_Error" ["type"]=> string(2) "::" ["args"]=> array(1) { [0]=> object(MDB2_Error)#26 (8) { ["error_message_prefix"]=> string(0) "" ["mode"]=> int(16) ["level"]=> int(1024) ["code"]=> int(-2) ["message"]=> string(24) "MDB2 Error: syntax error" ["userinfo"]=> string(223) "_doQuery: Could not execute statement[Native code: 1064] [Native message: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 1] " ["backtrace"]=> NULL ["callback"]=> array(2) { [0]=> &string(15) "Framework_Error" [1]=> string(10) "Pear_Error" } } } } [1]=> array(4) { ["file"]=> string(40) "/home/michael/www/framework/lib/PEAR.php" ["line"]=> int(901) ["function"]=> string(14) "call_user_func" ["args"]=> array(2) { [0]=> array(2) { [0]=> &string(15) "Framework_Error" [1]=> string(10) "Pear_Error" } [1]=> object(MDB2_Error)#26 (8) { ["error_message_prefix"]=> string(0) "" ["mode"]=> int(16) ["level"]=> int(1024) ["code"]=> int(-2) ["message"]=> string(24) "MDB2 Error: syntax error" ["userinfo"]=> string(223) "_doQuery: Could not execute statement[Native code: 1064] [Native message: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 1] " ["backtrace"]=> NULL ["callback"]=> array(2) { [0]=> &string(15) "Framework_Error" [1]=> string(10) "Pear_Error" } } } } [2]=> array(6) { ["file"]=> string(40) "/home/michael/www/framework/lib/MDB2.php" ["line"]=> int(951) ["function"]=> string(10) "PEAR_Error" ["class"]=> string(10) "PEAR_Error" ["type"]=> string(2) "->" ["args"]=> array(5) { [0]=> string(24) "MDB2 Error: syntax error" [1]=> int(-2) [2]=> int(16) [3]=> array(2) { [0]=> &string(15) "Framework_Error" [1]=> string(10) "Pear_Error" } [4]=> string(223) "_doQuery: Could not execute statement[Native code: 1064] [Native message: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 1] " } } [3]=> array(6) { ["file"]=> string(40) "/home/michael/www/framework/lib/PEAR.php" ["line"]=> int(563) ["function"]=> string(10) "MDB2_Error" ["class"]=> string(10) "MDB2_Error" ["type"]=> string(2) "->" ["args"]=> array(4) { [0]=> int(-2) [1]=> int(16) [2]=> array(2) { [0]=> &string(15) "Framework_Error" [1]=> string(10) "Pear_Error" } [3]=> string(223) "_doQuery: Could not execute statement[Native code: 1064] [Native message: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 1] " } } [4]=> array(6) { ["file"]=> string(40) "/home/michael/www/framework/lib/MDB2.php" ["line"]=> int(1357) ["function"]=> string(10) "raiseError" ["class"]=> string(4) "PEAR" ["type"]=> string(2) "->" ["args"]=> array(7) { [0]=> NULL [1]=> int(-2) [2]=> NULL [3]=> NULL [4]=> string(223) "_doQuery: Could not execute statement[Native code: 1064] [Native message: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 1] " [5]=> string(10) "MDB2_Error" [6]=> bool(true) } } [5]=> array(6) { ["file"]=> string(54) "/home/michael/www/framework/lib/MDB2/Driver/mysqli.php" ["line"]=> int(494) ["function"]=> string(10) "raiseError" ["class"]=> string(18) "MDB2_Driver_Common" ["type"]=> string(2) "->" ["args"]=> array(4) { [0]=> NULL [1]=> NULL [2]=> NULL [3]=> string(37) "_doQuery: Could not execute statement" } } [6]=> array(6) { ["file"]=> string(40) "/home/michael/www/framework/lib/MDB2.php" ["line"]=> int(2112) ["function"]=> string(8) "_doQuery" ["class"]=> string(18) "MDB2_Driver_mysqli" ["type"]=> string(2) "->" ["args"]=> array(4) { [0]=> string(36) "CREATE INDEX primary_idx ON files ()" [1]=> bool(true) [2]=> object(mysqli)#19 (0) { } [3]=> string(9) "framework" } } [7]=> array(6) { ["file"]=> string(62) "/home/michael/www/framework/lib/MDB2/Driver/Manager/mysqli.php" ["line"]=> int(669) ["function"]=> string(4) "exec" ["class"]=> string(18) "MDB2_Driver_Common" ["type"]=> string(2) "->" ["args"]=> array(1) { [0]=> string(36) "CREATE INDEX primary_idx ON files ()" } } [8]=> array(6) { ["file"]=> string(47) "/home/michael/www/framework/lib/MDB2/Schema.php" ["line"]=> int(1405) ["function"]=> string(11) "createIndex" ["class"]=> string(26) "MDB2_Driver_Manager_mysqli" ["type"]=> string(2) "->" ["args"]=> array(3) { [0]=> &string(5) "files" [1]=> &string(7) "primary" [2]=> &array(1) { ["change"]=> bool(true) } } } [9]=> array(6) { ["file"]=> string(47) "/home/michael/www/framework/lib/MDB2/Schema.php" ["line"]=> int(1504) ["function"]=> string(20) "alterDatabaseIndexes" ["class"]=> string(11) "MDB2_Schema" ["type"]=> string(2) "->" ["args"]=> array(2) { [0]=> &string(5) "files" [1]=> &array(1) { ["change"]=> array(1) { ["primary"]=> array(1) { ["change"]=> bool(true) } } } } } [10]=> array(6) { ["file"]=> string(47) "/home/michael/www/framework/lib/MDB2/Schema.php" ["line"]=> int(1612) ["function"]=> string(19) "alterDatabaseTables" ["class"]=> string(11) "MDB2_Schema" ["type"]=> string(2) "->" ["args"]=> array(3) { [0]=> &array(1) { ["files"]=> array(3) { ["fields"]=> array(9) { ["files_path"]=> array(5) { ["type"]=> string(4) "text" ["length"]=> string(3) "255" ["notnull"]=> bool(true) ["default"]=> string(1) "/" ["was"]=> string(10) "files_path" } ["files_name"]=> array(5) { ["type"]=> string(4) "text" ["length"]=> string(3) "255" ["notnull"]=> bool(true) ["default"]=> string(0) "" ["was"]=> string(10) "files_name" } ["files_type"]=> array(6) { ["type"]=> string(7) "integer" ["unsigned"]=> bool(false) ["length"]=> string(1) "1" ["notnull"]=> bool(true) ["default"]=> int(0) ["was"]=> string(10) "files_type" } ["files_locale"]=> array(5) { ["type"]=> string(4) "text" ["length"]=> string(1) "5" ["notnull"]=> bool(true) ["default"]=> string(5) "en_CA" ["was"]=> string(12) "files_locale" } ["files_data"]=> array(3) { ["type"]=> string(4) "blob" ["notnull"]=> bool(false) ["was"]=> string(10) "files_data" } ["files_mime"]=> array(5) { ["type"]=> string(4) "text" ["length"]=> string(3) "255" ["notnull"]=> bool(false) ["default"]=> NULL ["was"]=> string(10) "files_mime" } ["files_right"]=> array(5) { ["type"]=> string(7) "integer" ["unsigned"]=> bool(false) ["notnull"]=> bool(true) ["default"]=> int(0) ["was"]=> string(11) "files_right" } ["files_modified"]=> array(4) { ["type"]=> string(9) "timestamp" ["notnull"]=> bool(true) ["default"]=> string(19) "0000-00-00 00:00:00" ["was"]=> string(14) "files_modified" } ["files_owner"]=> array(6) { ["type"]=> string(4) "text" ["unsigned"]=> bool(false) ["length"]=> string(2) "32" ["notnull"]=> bool(true) ["default"]=> string(1) "0" ["was"]=> string(11) "files_owner" } } ["indexes"]=> array(1) { ["primary"]=> array(3) { ["primary"]=> bool(true) ["fields"]=> array(4) { ["files_path"]=> array(1) { ["sorting"]=> string(9) "ascending" } ["files_name"]=> array(1) { ["sorting"]=> string(9) "ascending" } ["files_type"]=> array(1) { ["sorting"]=> string(9) "ascending" } ["files_locale"]=> array(1) { ["sorting"]=> string(9) "ascending" } } ["was"]=> string(7) "primary" } } ["was"]=> string(5) "files" } } [1]=> &array(1) { ["files"]=> array(3) { ["fields"]=> array(8) { ["files_path"]=> array(5) { ["type"]=> string(4) "text" ["length"]=> string(3) "255" ["notnull"]=> bool(true) ["default"]=> string(1) "/" ["was"]=> string(10) "files_path" } ["files_name"]=> array(5) { ["type"]=> string(4) "text" ["length"]=> string(3) "255" ["notnull"]=> bool(true) ["default"]=> string(0) "" ["was"]=> string(10) "files_name" } ["files_type"]=> array(6) { ["type"]=> string(7) "integer" ["unsigned"]=> bool(false) ["length"]=> string(1) "1" ["notnull"]=> bool(true) ["default"]=> int(0) ["was"]=> string(10) "files_type" } ["files_data"]=> array(3) { ["type"]=> string(4) "blob" ["notnull"]=> bool(false) ["was"]=> string(10) "files_data" } ["files_mime"]=> array(5) { ["type"]=> string(4) "text" ["length"]=> string(3) "255" ["notnull"]=> bool(false) ["default"]=> NULL ["was"]=> string(10) "files_mime" } ["files_right"]=> array(5) { ["type"]=> string(7) "integer" ["unsigned"]=> bool(false) ["notnull"]=> bool(true) ["default"]=> int(0) ["was"]=> string(11) "files_right" } ["files_modified"]=> array(4) { ["type"]=> string(9) "timestamp" ["notnull"]=> bool(true) ["default"]=> string(19) "0000-00-00 00:00:00" ["was"]=> string(14) "files_modified" } ["files_owner"]=> array(6) { ["type"]=> string(4) "text" ["unsigned"]=> bool(false) ["length"]=> string(2) "32" ["notnull"]=> bool(true) ["default"]=> string(1) "0" ["was"]=> string(11) "files_owner" } } ["indexes"]=> array(1) { ["primary"]=> array(3) { ["primary"]=> bool(true) ["fields"]=> array(3) { ["files_path"]=> array(1) { ["sorting"]=> string(9) "ascending" } ["files_name"]=> array(1) { ["sorting"]=> string(9) "ascending" } ["files_type"]=> array(1) { ["sorting"]=> string(9) "ascending" } } ["was"]=> string(7) "primary" } } ["was"]=> string(5) "files" } } [2]=> &array(1) { ["change"]=> array(1) { ["files"]=> array(2) { ["add"]=> array(1) { ["files_locale"]=> array(5) { ["type"]=> string(4) "text" ["length"]=> string(1) "5" ["notnull"]=> bool(true) ["default"]=> string(5) "en_CA" ["was"]=> string(12) "files_locale" } } ["indexes"]=> array(1) { ["change"]=> array(1) { ["primary"]=> array(1) { ["change"]=> bool(true) } } } } } } } } [11]=> array(6) { ["file"]=> string(47) "/home/michael/www/framework/lib/MDB2/Schema.php" ["line"]=> int(2030) ["function"]=> string(13) "alterDatabase" ["class"]=> string(11) "MDB2_Schema" ["type"]=> string(2) "->" ["args"]=> array(3) { [0]=> &array(4) { ["name"]=> string(9) "framework" ["create"]=> bool(true) ["overwrite"]=> bool(false) ["tables"]=> array(1) { ["files"]=> array(3) { ["fields"]=> array(9) { ["files_path"]=> array(5) { ["type"]=> string(4) "text" ["length"]=> string(3) "255" ["notnull"]=> bool(true) ["default"]=> string(1) "/" ["was"]=> string(10) "files_path" } ["files_name"]=> array(5) { ["type"]=> string(4) "text" ["length"]=> string(3) "255" ["notnull"]=> bool(true) ["default"]=> string(0) "" ["was"]=> string(10) "files_name" } ["files_type"]=> array(6) { ["type"]=> string(7) "integer" ["unsigned"]=> bool(false) ["length"]=> string(1) "1" ["notnull"]=> bool(true) ["default"]=> int(0) ["was"]=> string(10) "files_type" } ["files_locale"]=> array(5) { ["type"]=> string(4) "text" ["length"]=> string(1) "5" ["notnull"]=> bool(true) ["default"]=> string(5) "en_CA" ["was"]=> string(12) "files_locale" } ["files_data"]=> array(3) { ["type"]=> string(4) "blob" ["notnull"]=> bool(false) ["was"]=> string(10) "files_data" } ["files_mime"]=> array(5) { ["type"]=> string(4) "text" ["length"]=> string(3) "255" ["notnull"]=> bool(false) ["default"]=> NULL ["was"]=> string(10) "files_mime" } ["files_right"]=> array(5) { ["type"]=> string(7) "integer" ["unsigned"]=> bool(false) ["notnull"]=> bool(true) ["default"]=> int(0) ["was"]=> string(11) "files_right" } ["files_modified"]=> array(4) { ["type"]=> string(9) "timestamp" ["notnull"]=> bool(true) ["default"]=> string(19) "0000-00-00 00:00:00" ["was"]=> string(14) "files_modified" } ["files_owner"]=> array(6) { ["type"]=> string(4) "text" ["unsigned"]=> bool(false) ["length"]=> string(2) "32" ["notnull"]=> bool(true) ["default"]=> string(1) "0" ["was"]=> string(11) "files_owner" } } ["indexes"]=> array(1) { ["primary"]=> array(3) { ["primary"]=> bool(true) ["fields"]=> array(4) { ["files_path"]=> array(1) { ["sorting"]=> string(9) "ascending" } ["files_name"]=> array(1) { ["sorting"]=> string(9) "ascending" } ["files_type"]=> array(1) { ["sorting"]=> string(9) "ascending" } ["files_locale"]=> array(1) { ["sorting"]=> string(9) "ascending" } } ["was"]=> string(7) "primary" } } ["was"]=> string(5) "files" } } } [1]=> &array(4) { ["name"]=> string(9) "framework" ["create"]=> bool(true) ["overwrite"]=> bool(false) ["tables"]=> array(1) { ["files"]=> array(3) { ["fields"]=> array(8) { ["files_path"]=> array(5) { ["type"]=> string(4) "text" ["length"]=> string(3) "255" ["notnull"]=> bool(true) ["default"]=> string(1) "/" ["was"]=> string(10) "files_path" } ["files_name"]=> array(5) { ["type"]=> string(4) "text" ["length"]=> string(3) "255" ["notnull"]=> bool(true) ["default"]=> string(0) "" ["was"]=> string(10) "files_name" } ["files_type"]=> array(6) { ["type"]=> string(7) "integer" ["unsigned"]=> bool(false) ["length"]=> string(1) "1" ["notnull"]=> bool(true) ["default"]=> int(0) ["was"]=> string(10) "files_type" } ["files_data"]=> array(3) { ["type"]=> string(4) "blob" ["notnull"]=> bool(false) ["was"]=> string(10) "files_data" } ["files_mime"]=> array(5) { ["type"]=> string(4) "text" ["length"]=> string(3) "255" ["notnull"]=> bool(false) ["default"]=> NULL ["was"]=> string(10) "files_mime" } ["files_right"]=> array(5) { ["type"]=> string(7) "integer" ["unsigned"]=> bool(false) ["notnull"]=> bool(true) ["default"]=> int(0) ["was"]=> string(11) "files_right" } ["files_modified"]=> array(4) { ["type"]=> string(9) "timestamp" ["notnull"]=> bool(true) ["default"]=> string(19) "0000-00-00 00:00:00" ["was"]=> string(14) "files_modified" } ["files_owner"]=> array(6) { ["type"]=> string(4) "text" ["unsigned"]=> bool(false) ["length"]=> string(2) "32" ["notnull"]=> bool(true) ["default"]=> string(1) "0" ["was"]=> string(11) "files_owner" } } ["indexes"]=> array(1) { ["primary"]=> array(3) { ["primary"]=> bool(true) ["fields"]=> array(3) { ["files_path"]=> array(1) { ["sorting"]=> string(9) "ascending" } ["files_name"]=> array(1) { ["sorting"]=> string(9) "ascending" } ["files_type"]=> array(1) { ["sorting"]=> string(9) "ascending" } } ["was"]=> string(7) "primary" } } ["was"]=> string(5) "files" } } } [2]=> &array(1) { ["tables"]=> array(1) { ["change"]=> array(1) { ["files"]=> array(2) { ["add"]=> array(1) { ["files_locale"]=> array(5) { ["type"]=> string(4) "text" ["length"]=> string(1) "5" ["notnull"]=> bool(true) ["default"]=> string(5) "en_CA" ["was"]=> string(12) "files_locale" } } ["indexes"]=> array(1) { ["change"]=> array(1) { ["primary"]=> array(1) { ["change"]=> bool(true) } } } } } } } } } [12]=> array(6) { ["file"]=> string(36) "/home/michael/www/framework/main.php" ["line"]=> int(59) ["function"]=> string(14) "updateDatabase" ["class"]=> string(11) "MDB2_Schema" ["type"]=> string(2) "->" ["args"]=> array(2) { [0]=> &string(55) "/home/michael/www/framework/modules/files/db/schema.xml" [1]=> &string(63) "/home/michael/www/framework/modules/files/db/schema_current.xml" } } } }

Comments

 [2006-06-15 16:52 UTC] lsmith (Lukas Smith)
I will try to run your example on the next days. But it looks a bit wierd. For constraints it should be using ALTER TABLE and not CREATE INDEX. Are you sure you are using the MDB2 and mysqli driver versions you claim? Also its generally a really a bad idea to call your primary key "primary". This will make your schema unportable. Give it another name. MDB2_Schema will automatically create it with the name "primary" for MySQL even if you give it another name because MySQL requires one to do so.
 [2006-06-15 16:57 UTC] lsmith (Lukas Smith)
You could do me a favor if you could dump the $changes array in the updateDatabase method and paste it here. I have a hunch that maybe the changes array is not generated properly.
 [2006-06-27 15:25 UTC] lsmith (Lukas Smith)
This bug has been fixed in CVS. If this was a documentation problem, the fix will appear on pear.php.net by the end of next Sunday (CET). If this was a problem with the pear.php.net website, the change should be live shortly. Otherwise, the fix will appear in the package's next release. Thank you for the report and for helping us make PEAR better.