MySQL gives unexpected error about key length

We are in the process of converting a MySQL-using application from one system (the "source") to another (the "target"). The application is simply to load a mysqldump file into a database and do some sanity checking on it. The mysqldump is created from a database that supports a postfix application; however, this "should not" be significant.

 The "source" machine has been in production use for years.

The versions of MySQL on the two machines are as follows:

source: mysql-server-5.0.90_2 Multithreaded SQL database (server)
target: Distrib 5.1.57, for apple-darwin10.7.0

We have run into a couple of issues. The primary issue at this point is shown by the following error message which we are getting on the target machine, but never had on the source machine.

ERROR 1071 (42000) at line 368: Specified key was too long; max key length is 1000 bytes

This error message is strange for the following two reasons:

1. It does not occur on the source machine.

2. There should not be keys anywhere near that length in any case.

So I am wondering what is going on.
Who is Participating?
arnoldConnect With a Mentor Commented:
Check the default character set on this and the other system.

check show variables from each.
Garry GlendownConsulting and Network/Security SpecialistCommented:
It would help if you could post the schema of the table affected, including the keys (or even better, the key that is causing this error).
If the table has multiple keys and you can't say which one is causing this error, you could for starters try to import the table without any keys defined, then manually add the keys one by one. That way at least you can pinpoint the culprit. Once you have that information, it should be easier to get some pointers towards the solution ...
jasimon9Author Commented:
We have resolved the issue. The people working on it did not tell me what the resolution was, but it has something to do with the character set and UTF-8. They said that UTF-8 took 3 bytes per character, so conceivably that could lead to tripling the key length They found the solution without the benefit of the input from arnold, who did suggest checking the character set.

So arnold may have had the germ of the solution, but I cannot confirm that at this time. When I get the resolution from the team, I'll come back to award points.
jasimon9Author Commented:
Turns out that our staff came up with a solution by running the data through a filter to convert the character set before importing.

The "right solution" would be to recompile mysql with built in character set support. However, that version of mysql is not in macports at this time accordingly to what I have been told. So the workaround of filtering the data was adopted as the most practical approach.

So even though my team came up with this solution independently from the responder to this question, I feel that points should be awarded because of the right direction towards the solution.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.