moving servers from mysql 4 to mysql 5 - apparently my keys are too long... HELP

[uptoeleven]

Hi everyone 

I am moving servers from a shared host with 1&1 to a dedicated server with 1&1. As part of this I am moving all my sites over including two that were built using mysql 4.0.27. The new server runs mysql 5.

I thought that migrating would be easy.

1. go into phpmyadmin on the old server and do a dump.
2. remove the "create database" line because I am importing into a database that plesk has already set up for me.
3. upload dump to new database in phpmyadmin.

Problem

I get an error which says that: 'Specified key was too long; max key length is 1000 bytes'.

I've had a look round on the internet and I think the problem is something to do with utf-8?

Has anybody had this problem before or know how I can fix it so that the data I import into the new server will import properly?

Here is the error in full:

Error

SQL query:

--
-- Dumping data for table `cms_module_search_words`
--
-- --------------------------------------------------------
--
-- Table structure for table `cms_module_templates`
--
CREATE TABLE `cms_module_templates` (
`module_name` varchar( 200 ) default NULL ,
`template_name` varchar( 200 ) default NULL ,
`content` text,
`create_date` datetime default NULL ,
`modified_date` datetime default NULL ,
KEY `module_name` ( `module_name` , `template_name` )
) TYPE = MYISAM ;

MySQL said: Documentation
#1071 - Specified key was too long; max key length is 1000 bytes




help...

[fredp]

...
I've had a look round on the internet and I think the problem is something to do with utf-8?

Has anybody had this problem before or know how I can fix it so that the data I import into the new server will import properly?
...

Hi,

I haven't had this problem myself, but I seem to recall reading of a similar problem a while back.  IIRC, the problem was that the original mysql4 database used a multibyte character set or that mysqldump did a conversion to a multibyte character set. The error occurred on import into a new mysql5 database that had a single-byte character set, say latin1.

So, you might check what the original mysql4 db character set was and then verify that the new mysql5 db uses a compatible character set.  If that is okay, then my guess is that mysqldump did a silent conversion to utf8.  If it did, and you don't want utf8, you could try specifying the the proper character set with a command line option.  Here's a excerpt from the mysqldump man page from server:

--default-character-set=charset_name

         Use charset_name as the default character set. See Section 9.2, “The Character Set Used for Data and Sorting”. If no
         character set is specified, mysqldump uses utf8, and earlier versions use latin1.

It's a guess, but I hope it helps,
Fred P.

[uptoeleven]

I'll have a look at what character set the original was using. Any idea how I can find that out? myphpadmin isn't being very forthcoming

utf8 is fine with me though obviously not with mysql 5 

[reneh]

Just open your dump file and below each table its telling you the char setting.


If I recall correct its just some of the tables that holds keys that have problems with to long keys.

In fact these keys don't need to be utf8 (as far I know)
and you can manual edit the dump file to tell that these tables are latin1 - shouldn't be anything else than safe chars in these table anyway.  edit to this:
            ...... ENGINE=MyISAM DEFAULT CHARSET=latin1;
for each table you get error on. (But only for the tables you get error for to long key)
I myself edit the dump file for each time I got an error (yes its stupid long way to do it).

A tip:
I think MySQL Migration Toolkit can help you a lot with this problem. (But it of course requires you have external access to the db)

And note - I'm not at all any specialist on this subject! So I waranty anything!

[uptoeleven]

I have external access to the new db but not the old one, I don't think. But I can have a look at mysql migration tool. I can always temporarily install mysql 4 and copy the database over if I need to. But first I will try the editing you have suggested

Thanks

Simon

[fredp]

...utf8 is fine with me though obviously not with mysql 5 

Well then, if all else fails, you might try simply changing the character set of your mysql5 db to utf8 before the import. I haven't had the occasion to do this yet, but IIRC, there is an option for making this change in phpmyadmin.

Cheers,
Fred P.