Solved

MySQL gives unexpected error about key length

Posted on 2011-09-12
4
636 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 18

Expert Comment

by:Garry Glendown
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 78

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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Unified and professional email signatures help maintain a consistent company brand image to the outside world. This article shows how to create an email signature in Exchange Server 2010 using a transport rule and how to overcome native limitations …
This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
The purpose of this video is to demonstrate how to set up a Mailchimp Template which will let the user create a uniform look for all of their campaigns. This will be demonstrated using a Windows 8 PC. Mailchimp will be used. Log into your Mail…
This Micro Tutorial will demonstrate the easy use of Gmail embedding images in your email so the recipient of your email can view them in context.

729 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