Default charset for table Links : key length (in bytes)

Hi list,

trying to restore a rt-3.4 database on a new (MySQL) server, I
encountered a small problem : encoding and charset for table Links.

The Links table has two varchar fields, Base and Target, which are 240
characters long.

I had created manually the new db, using :

create database mytest DEFAULT CHARACTER SET utf8 COLLATE utf8_bin;

I thought setting the charset was clever. However, at restore time, I had :

ERROR 1071 (42000): Specified key was too long; max key length is 1024 bytes

Seems that up to 4 bytes characters (with utf8) is too much as Base
and Target may result for key Links1 in (240 * 4 + 240 * 4 + 11) =
1931 bytes.
Sure it exceed InnoDB max key size. FYI, MyISAM limit is 1000 bytes,
BDB is 255 bytes.

I know that this should not be an issue as the MySQL DB is created
without asking MySQL for any charset in rt-setup-database, resulting
in a latin1 encoded database.
This is still not an issue as these two field should contain only
ASCII data (such as fsck.com-rt://example.com/ticket/1750).

But this may a problem be in the future, especially if non ASCII/Latin
users set up a Unicode RT domain name.

Therefore this mail for reference and not (yet?) a bug report.

I’ll try to post a page about this on the wiki next week (backup and
restore for MySQL ?)

Regards,

J.
Jérôme Fenal - jfenal AT gmail.com - http://fenal.org/
Paris.pm - http://paris.mongueurs.net/

Hello.
I think you talk about MySQL 4.1 and it’s very important because 4.0
has different CHARACTER SETs subsystem. Please, next time be more
specific.

How did you configure your mysql server? Is default charest UTF-8 for
your server?

Anyway your analyze of the problem is correct.On 10/20/05, Jérôme Fenal jfenal@gmail.com wrote:

Hi list,

trying to restore a rt-3.4 database on a new (MySQL) server, I
encountered a small problem : encoding and charset for table Links.

The Links table has two varchar fields, Base and Target, which are 240
characters long.

I had created manually the new db, using :

create database mytest DEFAULT CHARACTER SET utf8 COLLATE utf8_bin;

I thought setting the charset was clever. However, at restore time, I had :

ERROR 1071 (42000): Specified key was too long; max key length is 1024 bytes

Seems that up to 4 bytes characters (with utf8) is too much as Base
and Target may result for key Links1 in (240 * 4 + 240 * 4 + 11) =
1931 bytes.
Sure it exceed InnoDB max key size. FYI, MyISAM limit is 1000 bytes,
BDB is 255 bytes.

I know that this should not be an issue as the MySQL DB is created
without asking MySQL for any charset in rt-setup-database, resulting
in a latin1 encoded database.
This is still not an issue as these two field should contain only
ASCII data (such as fsck.com-rt://example.com/ticket/1750).

But this may a problem be in the future, especially if non ASCII/Latin
users set up a Unicode RT domain name.

Therefore this mail for reference and not (yet?) a bug report.

I’ll try to post a page about this on the wiki next week (backup and
restore for MySQL ?)

Regards,

J.

Jérôme Fenal - jfenal AT gmail.com - http://fenal.org/
Paris.pm - http://paris.mongueurs.net/


Rt-devel mailing list
Rt-devel@lists.bestpractical.com
http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-devel

Best regards, Ruslan.

Hello.
I think you talk about MySQL 4.1 and it’s very important because 4.0
has different CHARACTER SETs subsystem. Please, next time be more
specific.

Oops, sorry, forgot that one :
$ rpm -q MySQL-Max
MySQL-Max-4.1.12-3mdk

How did you configure your mysql server? Is default charest UTF-8 for
your server?

Standard Mandriva Cooker (soon to be 2006.0) configuration for MySQL,
but locale set to full utf-8 (fr_FR.UTF-8).

Regards,

J.
Jérôme Fenal - jfenal AT gmail.com - http://fenal.org/
Paris.pm - http://paris.mongueurs.net/