Bug #9163 SHOW CREATE TABLE truncates COMMENT if it has 8bit chars
Submitted: 14 Mar 2005 11:44 Modified: 10 Mar 2006 12:12
Reporter: Domas Mituzas Email Updates:
Status: Unsupported Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:4.1, 5.0 OS:Linux (Linux)
Assigned to: CPU Architecture:Any

[14 Mar 2005 11:44] Domas Mituzas
Description:
mysqldump incorrectly dumps international characters in mysql comments, truncates strings and does not put ending quotes:

--
-- Table structure for table `FirmRubric`
--
 
CREATE TABLE FirmRubric (
  FirmId mediumint(9) default NULL,
  RubricId smallint(6) default NULL,
  KEY FirmId (FirmId),
  KEY RubricId (RubricId)
) ENGINE=MyISAM DEFAULT CHARSET=cp1257 COMMENT='Greita firm? paie?ka pagal rubrik;

Here comment is "Greita firmų paieška pagal rubriką";

This makes invalid database dumps. 

How to repeat:
Try creating databases with i8n characters in comments.

Suggested fix:
At least try to add quotes at the end of comment, fix charsets here :)
[28 Mar 2005 6:31] Alexander Barkov
I could not repeat this problem.
Can you please attach mysqldump output example?

Thank you!
[22 Apr 2005 12:05] Domas Mituzas
mysql 4.1.9: 

mysql> show create table FirmRubric;
+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table      | Create Table                                                                                                                                                                                                                                        |
+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| FirmRubric | CREATE TABLE `FirmRubric` (
  `FirmId` mediumint(9) default NULL,
  `RubricId` smallint(6) default NULL,
  KEY `FirmId` (`FirmId`),
  KEY `RubricId` (`RubricId`)
) ENGINE=MyISAM DEFAULT CHARSET=cp1257 COMMENT='Greita firm? paie?ka pagal rubrik |
+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
[22 Apr 2005 12:16] Domas Mituzas
reproduced same behaviour on 4.1.10
[26 Sep 2005 23:17] Denis Sokolov
Same problem in 4.1.14

COMMENTS='something;

Must be COMMENTS='something';
[30 Sep 2005 17:27] MySQL Verification Team
I was unable to repeat also, could you please attach here
a file with the table create script instead of paste it here.

Thanks in advance.
[30 Sep 2005 18:07] MySQL Verification Team
Hi Denis,

Please upload it at: ftp://ftp.mysql.com:/pub/mysql/upload

with a name like bug9163.zip

Thanks.
[30 Sep 2005 19:17] Domas Mituzas
Reproduced as following:

on 3.23.58:

CREATE TABLE t1 (a INT) COMMENT="ąčęė" (8bit iso8859-13 characters)

copied tables (frm,MYD,MYI) directly to 4.1.14.

SHOW CREATE TABLE showed truncated comment:

mysql> show create table t2 \G
*************************** 1. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (
  `a` int(11) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='???
1 row in set (0.00 sec)
[30 Sep 2005 19:44] Domas Mituzas
these are binary t2 table files, bug survived even after ALTER TABLE.

Attachment: 9169-testcase.tgz (application/x-tgz, text), 407 bytes.

[15 Oct 2005 21:12] Denis Sokolov
Lead: Bugs System 
OS: Linux 

Not only Linux, FreebBSD too
[24 Oct 2005 20:04] Denis Sokolov
Was bug resolved in 4.15?
[10 Mar 2006 12:12] Domas Mituzas
As the only way to reproduce this problem is with unsupported 3.23 (which did put wrong comment header) and easy workaround exists (removing or changing COMMENT), I'm marking this bug as not supported.
[27 May 2007 7:02] Xingsheng Qian
Hi There,

I encountered an issue with replication.
"show slave status" return an error message like this 
"Error 'Invalid utf8 character string: 'ô_M'' on query. Default database: ...."
If you have experiened the same issue and can share the solution, it would be greatly appreciated.

Thanks
qian