MySQL Bugs: #26677: mysqldump - invalid sql for fulltext indexes on mediumtext and timestamp vals

Bug #26677	mysqldump - invalid sql for fulltext indexes on mediumtext and timestamp vals
Submitted:	27 Feb 2007 10:37	Modified:	1 Mar 2007 12:02
Reporter:	Tom Melly	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: Backup	Severity:	S3 (Non-critical)
Version:	4.1.10-standard-log	OS:	Liniux Redhat 7
Assigned to:		CPU Architecture:	Any
Tags:	fulltext, INDEX, mysqldump, timestamp

Description:
I have a simple test-case table, containing one row of data. The storage is myISAM, and the table has a fulltext index on a mediumtext column. Mysqldump produces the following output:

CREATE TABLE test_bug (
  oid mediumint(8) unsigned zerofill NOT NULL auto_increment,
  description mediumtext,
  time timestamp DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (oid),
  KEY MTXT (description)
);

# Dumping data for table 'test_bug'

INSERT INTO test_bug VALUES (00000001,'test',2007-02-28 12:00:00);

If I try to restore this data, I will have two problems:

1. The CREATE TABLE statement will fail when it tries to create KEY MTXT (description). Presumably it doesn't know to create a fulltext index?

2. The unquoted timestamp value, 2007-02-28 12:00:00, cannot be loaded - it needs quotes.

Since I came across this while trying to restore a bugzilla db, I'm fairly sure that this is either a known bug or not-a-bug, but I couldn't find anything on it in the bug db. Apologies for any time-wasting...

How to repeat:
Run the following sql to create a table and load data:

CREATE TABLE test_bug (
  oid mediumint(8) unsigned zerofill NOT NULL auto_increment,
  description mediumtext,
  time timestamp DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (oid),
);
INSERT INTO test_bug VALUES (00000001,'test','2007-02-28 12:00:00');

Then add a fulltext index to the 'description' column.

Then dump the table using mysqldump, then drop the table, then run the mysqldump output to create and load the table (assuming your mysqldump has the key entry for description and the unquoted timestamps).

Suggested fix:
Well, I ran a perl-script to fix the dates (on a bugzilla restore), and tracked down the bad indexes and removed them from the CREATE TABLE statements, but I don't know what the proper fix is...

Thank you for the report.

Version 4.1.10 is quite old.

Please try with current 4.1.22 version and say us result.

An associate at permonks.org has verified this bug on v 5+ (sorry, I can't remember which version he/she was on, except that it was 5+).

Sounds like it might be worth checking, but add comment if you require further info, and I'll try and a) get more version info from my associate and b) try and set up an environment to check it myself.

Just tested on win32 (2k) 5.0.27-community-nt and cannot reproduce. Table and index type accurately specified (so no index problem), and timestamp is quoted in mysqldump (so no insert problem).

Note this is windows, but mark as fixed/unverified?

Thank you for the feedback.

Marked as "Can't repeat"