Solved

MySQL gives unexpected error about key length

Posted on 2011-09-12
4
634 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

Secure Your Active Directory - April 20, 2017

Active Directory plays a critical role in your company’s IT infrastructure and keeping it secure in today’s hacker-infested world is a must.
Microsoft published 300+ pages of guidance, but who has the time, money, and resources to implement? Register now to find an easier way.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Do Not Reply on Exchange 2010 1 62
Unable to print PDF document on Mac laptop 6 52
MySQL-Design Help 12 44
How to use 2 ON statements in inner join 3 27
It’s a strangely common occurrence that when you send someone their login details for a system, they can’t get in. This article will help you understand why it happens, and what you can do about it.
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
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 …
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.

730 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