| Bug #25596 | InnoDB Alter Table Failures | ||
|---|---|---|---|
| Submitted: | 12 Jan 2007 21:13 | Modified: | 20 Feb 2007 20:30 |
| Reporter: | Richard Harms | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: InnoDB storage engine | Severity: | S2 (Serious) |
| Version: | 5.0.32, 5.0.36-bk | OS: | Linux (Linux, Windows, Mac OS X 10.4.8) |
| Assigned to: | Marko Mäkelä | CPU Architecture: | Any |
[15 Jan 2007 7:38]
Sveta Smirnova
Thank you for the report. Please provide output of SHOW CREATE TABLE for some of tables, adding FOREIGN KEY to which causes error.
[15 Jan 2007 12:20]
Heikki Tuuri
Hi! Hmm... there were not that many changes between 5.0.30 and .32. I will diff the InnoDB source files. The bug looks like memory corruption, or a stray pointer. Regards, Heikki
[16 Jan 2007 16:23]
Heikki Tuuri
This may be a duplicate of http://bugs.mysql.com/bug.php?id=25653
[16 Jan 2007 16:35]
Axel Schwenke
Modified according to duplicate bug#25653
[16 Jan 2007 18:04]
Heikki Tuuri
The bug is probably that strchr(" \v\f\t\r\n", c) checks also if c matches to the last character in the string, and the last character is \0! It is a nul-terminated string.
Thus, the parser thinks that also the ending nul character is a space!
This is a gotcha of standard C. We need to check all occurrences of strchr() in InnoDB if there is a similar bug somewhere.
Assigning this to Marko.
Fix: replace
strchr(" \v\f\t\r\n", c)
with
(strchr(" \v\f\t\r\n", c) && c != '\0')
[16 Jan 2007 18:32]
Richard Harms
I made the change that you suggested to this file:
innobase/dict/dict0dict.c:#define ib_isspace(c) strchr(" \v\f\t\r\n", c)
And it does appear to correct the problem.
Thank you!
[16 Jan 2007 20:08]
Marko Mäkelä
My bad: my draft copy of ISO/IEC 9899:1999 indeed says: "The strchr function locates the first occurrence of c (converted to a char) in the string pointed to by s. The terminating null character is considered to be part of the string." The GNU libc documentation of strchr did not mention that the terminating null character is considered to be part of the string.
[16 Jan 2007 20:30]
Marko Mäkelä
The only other occurrence of strchr with the second parameter being something else than '/' is in mem_strdupq(), where the second parameter is always the single quote. The only place where the terminating null character of the string passed to strchr() makes a difference is the definition of the ib_isspace() macro.
[14 Feb 2007 15:00]
Chad MILLER
Available in 4.1.23, 5.0.36, and 5.1.16-beta.
[14 Feb 2007 19:01]
Marko Mäkelä
Chad, 5.1 was never affected by Bug #24299, whose fix in 4.1 and 5.0 caused this bug. Thus, I do not understand how any fix could be available in 5.1.16-beta.
[20 Feb 2007 20:30]
Paul DuBois
Noted in 4.1.23, 5.0.36 (but not 5.1.16) changelogs. The InnoDB parser sometimes did not account for null bytes, causing spurious failure of some queries.
[20 Feb 2007 20:48]
Chad MILLER
Sorry, Marko, I didn't notice that what went into 5.1 was a null-merge changeset that affected nothing.
[7 Feb 2008 3:01]
rajeshwar reddy
com.mysql.jdbc.SQLError.createSQLException(SQLError.java:946)
[7 Feb 2008 3:02]
rajeshwar reddy
com.mysql.jdbc.SQLError.createSQLException(SQLError.java:946) com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2870) com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:812) com.mysql.jdbc.MysqlIO.secureAuth411(MysqlIO.java:3269) com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1182) com.mysql.jdbc.Connection.createNewIO(Connection.java:2670) com.mysql.jdbc.Connection.<init>(Connection.java:1531) com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:266) java.sql.DriverManager.getConnection(Unknown Source) java.sql.DriverManager.getConnection(Unknown Source) com.mysql.grt.modules.ReverseEngineeringGeneric.establishConnection(ReverseEngineeringGeneric.java:141) com.mysql.grt.modules.ReverseEngineeringMysqlJdbc.getSchemata(ReverseEngineeringMysqlJdbc.java:42) sun.reflect.GeneratedMethodAccessor1.invoke(Unknown Source) sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) java.lang.reflect.Method.invoke(Unknown Source) com.mysql.grt.Grt.callModuleFunction(Unknown Source)
[7 Feb 2008 7:09]
Timothy Smith
Rajeshwar, Hi. From what you've posted, it looks unrelated to the present bug. I suggest that, if you have a MySQL support contract, you open a new support issue. The support engineer will help diagnose what's happening, and open a new bug report if needed on your behalf. Or, please post to our user forums (forums.mysql.com), perhaps in the JDBC and Java forum. Post more than just the stack trace - a complete program that demonstrates the problem is best, but at least give a description of what you're doing. Regards, Timothy
Description: We have a piece of software that creates about 810 tables, followed by the indexes, followed by the foreign keys. This appears to be a new problem with 5.0.32, everything was working properly with 5.0.30. During attempts 1-3, the query "drop database XYZ; create database XYZ;" was executed before the program was run. Before attempt 4, I stopped by MySQL server and restarted it, in addition to dropping and recreating the database. "show engine innodb status;" was executed after each attempt, in order to get the details of the error. Each time, it reported a rather odd "Syntax error close to:" portion, usually being just a single letter 's', although one of the times, it reported "sATE SYS_F" - which is not even present in the query. Attempt 1: [java] (db.DatabaseUtilitiesForMySQL 94 ) ALTER TABLE glaccountdetails ADD CONSTRAINT gladt_gla_glaccountid_fk FOREIGN KEY (gladt_gla_glaccountid) REFERENCES glaccounts(gla_glaccountid) ON DELETE CASCADE [java] Exception in thread "main" java.sql.SQLException: Can't create table './darkrealms/#sql-134_4c.frm' (errno: 150) [java] at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:946) [java] at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2870) [java] at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1573) [java] at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1665) [java] at com.mysql.jdbc.Connection.execSQL(Connection.java:3170) [java] at com.mysql.jdbc.Connection.execSQL(Connection.java:3099) [java] at com.mysql.jdbc.Statement.execute(Statement.java:695) [java] at com.darkrealms.utils.db.DatabaseUtilities.createMissingForeignKey(DatabaseUtilities.java:642) [java] at com.darkrealms.utils.db.DatabaseUtilities.executeForeignKeyUpdates(DatabaseUtilities.java:648) [java] at com.darkrealms.utils.db.DatabaseUtilities.executeUpdates(DatabaseUtilities.java:694) [java] at com.darkrealms.utils.db.DatabaseUtilities.checkDatabase(DatabaseUtilities.java:83) [java] at DatabaseUtility.main(DatabaseUtility.java:87) [java] Java Result: 1 ------------------------ LATEST FOREIGN KEY ERROR ------------------------ 070112 14:54:02 Error in foreign key constraint of table darkrealms/#sql-134_4c: FOREIGN KEY (gladt_gla_glaccountid) REFERENCES glaccounts(gla_glaccountid) ON DELETE CASCADE: Syntax error close to: s Attempt 2: [java] (db.DatabaseUtilitiesForMySQL 94 ) ALTER TABLE associatedetails ADD CONSTRAINT ascdt_createdbyid_fk FOREIGN KEY (ascdt_createdbyid) REFERENCES entityinstances(eni_entityinstanceid) ON DELETE CASCADE [java] Exception in thread "main" java.sql.SQLException: Can't create table './darkrealms/#sql-134_4d.frm' (errno: 150) [java] at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:946) [java] at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2870) [java] at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1573) [java] at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1665) [java] at com.mysql.jdbc.Connection.execSQL(Connection.java:3170) [java] at com.mysql.jdbc.Connection.execSQL(Connection.java:3099) [java] at com.mysql.jdbc.Statement.execute(Statement.java:695) [java] at com.darkrealms.utils.db.DatabaseUtilities.createMissingForeignKey(DatabaseUtilities.java:642) [java] at com.darkrealms.utils.db.DatabaseUtilities.executeForeignKeyUpdates(DatabaseUtilities.java:648) [java] at com.darkrealms.utils.db.DatabaseUtilities.executeUpdates(DatabaseUtilities.java:694) [java] at com.darkrealms.utils.db.DatabaseUtilities.checkDatabase(DatabaseUtilities.java:83) [java] at DatabaseUtility.main(DatabaseUtility.java:87) [java] Java Result: 1 ------------------------ LATEST FOREIGN KEY ERROR ------------------------ 070112 14:57:40 Error in foreign key constraint of table darkrealms/#sql-134_4d: FOREIGN KEY (ascdt_createdbyid) REFERENCES entityinstances(eni_entityinstanceid) ON DELETE CASCADE: Syntax error close to: sATE SYS_F Attempt 3: [java] (db.DatabaseUtilitiesForMySQL 94 ) ALTER TABLE associatedetails ADD CONSTRAINT ascdt_createdbyid_fk FOREIGN KEY (ascdt_createdbyid) REFERENCES entityinstances(eni_entityinstanceid) ON DELETE CASCADE [java] Exception in thread "main" java.sql.SQLException: Can't create table './darkrealms/#sql-134_4e.frm' (errno: 150) [java] at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:946) [java] at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2870) [java] at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1573) [java] at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1665) [java] at com.mysql.jdbc.Connection.execSQL(Connection.java:3170) [java] at com.mysql.jdbc.Connection.execSQL(Connection.java:3099) [java] at com.mysql.jdbc.Statement.execute(Statement.java:695) [java] at com.darkrealms.utils.db.DatabaseUtilities.createMissingForeignKey(DatabaseUtilities.java:642) [java] at com.darkrealms.utils.db.DatabaseUtilities.executeForeignKeyUpdates(DatabaseUtilities.java:648) [java] at com.darkrealms.utils.db.DatabaseUtilities.executeUpdates(DatabaseUtilities.java:694) [java] at com.darkrealms.utils.db.DatabaseUtilities.checkDatabase(DatabaseUtilities.java:83) [java] at DatabaseUtility.main(DatabaseUtility.java:87) [java] Java Result: 1 ------------------------ LATEST FOREIGN KEY ERROR ------------------------ 070112 14:59:50 Error in foreign key constraint of table darkrealms/#sql-134_4e: FOREIGN KEY (ascdt_createdbyid) REFERENCES entityinstances(eni_entityinstanceid) ON DELETE CASCADE: Syntax error close to: s Attempt 4: [java] (db.DatabaseUtilitiesForMySQL 94 ) ALTER TABLE glaccountdetails ADD CONSTRAINT gladt_gla_glaccountid_fk FOREIGN KEY (gladt_gla_glaccountid) REFERENCES glaccounts(gla_glaccountid) ON DELETE CASCADE [java] Exception in thread "main" java.sql.SQLException: Can't create table './darkrealms/#sql-35e_2.frm' (errno: 150) [java] at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:946) [java] at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2870) [java] at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1573) [java] at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1665) [java] at com.mysql.jdbc.Connection.execSQL(Connection.java:3170) [java] at com.mysql.jdbc.Connection.execSQL(Connection.java:3099) [java] at com.mysql.jdbc.Statement.execute(Statement.java:695) [java] at com.darkrealms.utils.db.DatabaseUtilities.createMissingForeignKey(DatabaseUtilities.java:642) [java] at com.darkrealms.utils.db.DatabaseUtilities.executeForeignKeyUpdates(DatabaseUtilities.java:648) [java] at com.darkrealms.utils.db.DatabaseUtilities.executeUpdates(DatabaseUtilities.java:694) [java] at com.darkrealms.utils.db.DatabaseUtilities.checkDatabase(DatabaseUtilities.java:83) [java] at DatabaseUtility.main(DatabaseUtility.java:87) [java] Java Result: 1 ------------------------ LATEST FOREIGN KEY ERROR ------------------------ 070112 15:02:20 Error in foreign key constraint of table darkrealms/#sql-35e_2: FOREIGN KEY (gladt_gla_glaccountid) REFERENCES glaccounts(gla_glaccountid) ON DELETE CASCADE: Syntax error close to: s How to repeat: It appears to occur after a large number (several thousand) 'create table' and 'alter table' queries are executed.