Solved

MySQL gives unexpected error about key length

Posted on 2011-09-12
4
629 Views
Last Modified: 2012-05-12
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

http://islandlinux.org/howto/mysql-error-specified-key-was-too-long-max-key-length-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.
0
Comment
Question by:jasimon9
  • 2
4 Comments
 
LVL 17

Expert Comment

by:Garry-G
ID: 36527448
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 ...
0
 
LVL 77

Accepted Solution

by:
arnold earned 500 total points
ID: 36527465
Check the default character set on this and the other system.
http://bugs.mysql.com/bug.php?id=4541

check show variables from each.
0
 

Author Comment

by:jasimon9
ID: 36554666
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.
0
 

Author Comment

by:jasimon9
ID: 36897721
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.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

We could spend the next millennium discussing the differences of the Mac and Windows platforms. The next century will continue to have fanatics on both side of the equation and neither side will win the war. However, that’s not why we are here. W…
This is my first article on Expert Exchange on the Manual Method of Exporting Office 365 Mailboxes to PST format by using the eDiscovery mechanism of Office. Hope you will enjoy the article.
The purpose of this video is to demonstrate how to use PicMonkey software to customize images for a Mailchimp campaign. Picmonkey is free and simple online software which can be used by users who don’t have robust editing software such as Photoshop,…
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question