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 (
name VARCHAR(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]
