MySQL Bugs: #4023: mysqldump limits varchar key fields to 34

Bug #4023	mysqldump limits varchar key fields to 34
Submitted:	6 Jun 2004 14:41	Modified:	10 Jun 2004 0:38
Reporter:	Michael Berg	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: mysqldump Command-line Client	Severity:	S2 (Serious)
Version:	mysqldump Ver 10.4 Distrib 4.1.1a-alpha	OS:	Windows (Windows XP)
Assigned to:		CPU Architecture:	Any

Description:
If you create a table with a key of type varchar, and then try to use mysqldump to take a backup of that table, mysqldump outputs a "create table" statement with a length limit on the varchar key field equal to 34 (?).

This behaviour is dangerous. It means if you try to restore the table later on, tables will be recreated with these length limitations even though this was never the intention of the db administrator.

If data is added at some later time with a key value longer than 34, this data is truncated in the index, resulting in non-unique keys and other unforeseen behaviour.

How to repeat:
create table table1(
  longkey varchar(120) character set utf8,
  primary key(longkey)
) type=innoDB;

insert into table1(longkey) values('this is not so long');

Then execute a mysqldump command on this table:

C:\> mysqldump --opt mydatabase table1

--
-- Table structure for table `table1`
--

DROP TABLE IF EXISTS table1;
CREATE TABLE table1 (
  longkey varchar(120) character set utf8 NOT NULL default '',
  PRIMARY KEY  (longkey(34))  <------- What?
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Thank you for your bug report. This issue has already been fixed
in the latest released version of that product, which you can download at 
http://www.mysql.com/downloads/

Additional info:

I verified that it occures on 4.1.1 but could not reproduce it
on either 4.0.20 or 4.1.2 so i assue it is fixed

Can I ask if this is a client issue (bug in mysqldump) or something on the server?

My server is hosted and I am not able to tell my hosting provider to upgrade their MySQL server. However, I can easily upgrade my own sql clients.

As far as i remember i did use the same $PATH settings 
and thus the same client binaries for all tests so i'm
afraid that it is a server issue, but i will re-check
to be sure ...

I tested 4.0.11 server with the mysqldump binary 
from 4.0.12 but it does still show the wrong
behavior, so it is definetly a server issue.