Re: Problem with CREATE TABLE/DROP TABLE
On Jun 24, 2008, at 2:57 AM, Gwynne Raskind wrote: I'm having the issue with CREATE TABLE described by Bug #30513 (http://bugs.mysql.com/bug.php?id=30513 ). To summarize, a table which previously existed, and then is dropped by DROP TABLE IF EXISTS, becomes randomly unable to be recreated. Here is my comment on that bug: Having this same issue using MySQL 5.1.24-rc and 5.1.25-rc and an InnoDB table. Only solution I found was to dump and recreate my database, which is a ridiculous inconvenience since I'm having the issue with a test table I need to drop and recreate often. Did NOT have this issue before upgrading from 5.0.51. There is NO stray .frm file in the database directory, and the InnoDB tablespace/table monitors show no errors. No unusual entries appear in the MySQL error log. The table in question has the structure: CREATE TABLE TestData ( nameVARCHAR(64) NOT NULL, dateFormat VARCHAR(32) NOT NULL, loginForOne INT(1) UNSIGNED NOT NULL, loginForTwo INT(1) UNSIGNED NOT NULL, indexText MEDIUMTEXT NOT NULL ) ENGINE=InnoDB DEFAULT CHARACTER SET 'utf8'; It is correct that the table has no indexes. I tried `-quoting the table name and changing engines and character sets to no avail. Changing the table's name only resulted in the same thing starting to happen again with the same table. The only special thing about the table is that it's at the end of a batch file. Has anyone else had this problem, and more especially, does anyone know a useable workaround for it? I'm at my wits' end, and downgrading to 5.0.51 isn't a viable option for my environment; this isn't a production system and I'm using some 5.1-specific features as of my upgrade. I've discovered more information about this problem since, as posted in the bug report: I've tracked down this bug such that it only happens when the table in question is accessed via INFORMATION_SCHEMA after being created. A FLUSH TABLES command eliminates the error without need for any other intervention. I use INFORMATION_SCHEMA this way: SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME=TestData This also happens with any other table accessed in this way; based on the output of mysqladmin extended-status it looks like the server isn't closing the table definition correctly after accessing it via INFORMATION_SCHEMA. If anyone has any information about how to deal with this problem, or fix it, please let me know; it's very annoying. -- Gwynne, Daughter of the Code This whole world is an asylum for the incurable. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problem with CREATE TABLE/DROP TABLE
I'm having the issue with CREATE TABLE described by Bug #30513 (http://bugs.mysql.com/bug.php?id=30513 ). To summarize, a table which previously existed, and then is dropped by DROP TABLE IF EXISTS, becomes randomly unable to be recreated. Here is my comment on that bug: Having this same issue using MySQL 5.1.24-rc and 5.1.25-rc and an InnoDB table. Only solution I found was to dump and recreate my database, which is a ridiculous inconvenience since I'm having the issue with a test table I need to drop and recreate often. Did NOT have this issue before upgrading from 5.0.51. There is NO stray .frm file in the database directory, and the InnoDB tablespace/table monitors show no errors. No unusual entries appear in the MySQL error log. The table in question has the structure: CREATE TABLE TestData ( nameVARCHAR(64) NOT NULL, dateFormat VARCHAR(32) NOT NULL, loginForOne INT(1) UNSIGNED NOT NULL, loginForTwo INT(1) UNSIGNED NOT NULL, indexText MEDIUMTEXT NOT NULL ) ENGINE=InnoDB DEFAULT CHARACTER SET 'utf8'; It is correct that the table has no indexes. I tried `-quoting the table name and changing engines and character sets to no avail. Changing the table's name only resulted in the same thing starting to happen again with the same table. The only special thing about the table is that it's at the end of a batch file. Has anyone else had this problem, and more especially, does anyone know a useable workaround for it? I'm at my wits' end, and downgrading to 5.0.51 isn't a viable option for my environment; this isn't a production system and I'm using some 5.1-specific features as of my upgrade. -- Gwynne, Daughter of the Code This whole world is an asylum for the incurable. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem with CREATE TABLE/DROP TABLE
On Jun 24, 2008, at 10:13 AM, Rolando Edwards wrote: My first impression is to say: Sounds like the InnoDB internal data dictionary still has the table recorded somewhere. According to Page 566 Paragraph 3 of MySQL Administrator's Guide and Language Reference (ISBN 0-672-32870-4), InnoDB always needs the shared tablespace because it puts its internal data dictionary and undo logs there. This is may explain why the bug persists. Innobase Oy never got to fix it, and now Oracle owns Innobase Oy (I am still in mourning over that). This was my first thought, but the original bug report suggests that the bug occurs with other table types besides InnoDB. You may want to look into Falcon if you want ACID transaction supported tables. Transactional support isn't critical to my implementation, but foreign keys are. Also, it's my undertanding that Falcon is unsupported before MySQL 6, and I'm not prepared to upgrade that far yet. -- Gwynne, Daughter of the Code This whole world is an asylum for the incurable. -Original Message- From: Gwynne Raskind [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 24, 2008 2:58 AM To: mysql@lists.mysql.com Subject: Problem with CREATE TABLE/DROP TABLE I'm having the issue with CREATE TABLE described by Bug #30513 (http://bugs.mysql.com/bug.php?id=30513 ). To summarize, a table which previously existed, and then is dropped by DROP TABLE IF EXISTS, becomes randomly unable to be recreated. Here is my comment on that bug: Having this same issue using MySQL 5.1.24-rc and 5.1.25-rc and an InnoDB table. Only solution I found was to dump and recreate my database, which is a ridiculous inconvenience since I'm having the issue with a test table I need to drop and recreate often. Did NOT have this issue before upgrading from 5.0.51. There is NO stray .frm file in the database directory, and the InnoDB tablespace/table monitors show no errors. No unusual entries appear in the MySQL error log. The table in question has the structure: CREATE TABLE TestData ( nameVARCHAR(64) NOT NULL, dateFormat VARCHAR(32) NOT NULL, loginForOne INT(1) UNSIGNED NOT NULL, loginForTwo INT(1) UNSIGNED NOT NULL, indexText MEDIUMTEXT NOT NULL ) ENGINE=InnoDB DEFAULT CHARACTER SET 'utf8'; It is correct that the table has no indexes. I tried `-quoting the table name and changing engines and character sets to no avail. Changing the table's name only resulted in the same thing starting to happen again with the same table. The only special thing about the table is that it's at the end of a batch file. Has anyone else had this problem, and more especially, does anyone know a useable workaround for it? I'm at my wits' end, and downgrading to 5.0.51 isn't a viable option for my environment; this isn't a production system and I'm using some 5.1-specific features as of my upgrade. -- Gwynne, Daughter of the Code This whole world is an asylum for the incurable. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
