Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

MySQL gives unexpected error about key length

Posted on 2011-09-12
4
Medium Priority
?
640 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 80

Accepted Solution

by:
arnold earned 2000 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Article by: Justin
In light of the WannaCry ransomware attack that affected millions of Windows machines, you might wonder if your Mac needs protecting. Yes, it does and here is how to do it.
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 …
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

609 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