Install Problem - 1071: Specified key was too long. Max key length is 500

Install Problem - 1071: Specified key was too long. Max key length is 500

by W Page -
Number of replies: 14

Hello All!

I am getting the following error message when attempting to install Moodle 1.6 development (2005053000).

(mysql): CREATE TABLE `mdldev_config_plugins` ( `id` int(10) unsigned NOT NULL auto_increment, `plugin` varchar(255) NOT NULL default 'core', `name` varchar(255) NOT NULL default '', `value` text NOT NULL default '', PRIMARY KEY (`id`), UNIQUE KEY `plugin_name` (`plugin`, `name`)) TYPE=MyISAM COMMENT='Moodle modules and plugins configuration variables'  

1071: Specified key was too long. Max key length is 500

      
  

Error

I get the following text box message at the bottom of the page.

Error: Main databases NOT set up successfully

The rest of the installation occured without a problem.

What should I do to correct this problem.

WP1

Average of ratings: -
In reply to W Page

Re: Install Problem - 1071: Specified key was too long. Max key length is 500

by Martin Dougiamas -
Picture of Core developers Picture of Documentation writers Picture of Moodle HQ Picture of Particularly helpful Moodlers Picture of Plugin developers Picture of Testers
What version of MySQL are you using?
Average of ratings: -
In reply to Martin Dougiamas

Re: Install Problem - 1071: Specified key was too long. Max key length is 500

by W Page -

Hi Martin!

I am using "MySQL 4.0.24".

WP1

Average of ratings: -
In reply to Martin Dougiamas

Re: Install Problem - 1071: Specified key was too long. Max key length is 500

by Martín Langhoff -
OK - Resolved. It was within 500 chars for upgrades, but new installs still tried to use 510. Thanks for the heads up.
Average of ratings: -
In reply to Martín Langhoff

Re: Install Problem - 1071: Specified key was too long. Max key length is 500

by W Page -

Hi Martin L!!

Thanks for your action on this problem.

Just wanted to ask

  • Will this affect the installation?  The rest of the install went very well and I experienced no further problems or errors.
  • What was the reason for the error message?
  • How could I have corrected it?

Thanks in advance for your response.

WP1

Average of ratings: -
In reply to W Page

Re: Install Problem - 1071: Specified key was too long. Max key length is 500

by Martín Langhoff -
It was a bug -- I fixed it now. It'll be OK in tomorrow's 'daily' of 1.5 or if you do cvs update. If you want to fix it, you can put on your programmer's hat, and work on lib/db/mysql.sql, in the definition of the config_plugins table.
Average of ratings: -
In reply to W Page

Re: Install Problem - 1071: Specified key was too long. Max key length is 500

by Martín Langhoff -
Hang on. I had this problem and fixed it before committing. Let me see.
Average of ratings: -
In reply to W Page

Re: Install Problem - 1071: Specified key was too long. Max key length is 500

by Matthew Johnson -
I am seeing the a similar error during the installation of Moodle 1.5 on the following software stack:

Windows Server 2003 + IIS 6.0
MySQL Essential 4.1.12a
PHP 5.0.4

Based on some other reading that I have done, this might be related to the fact that I set up the MySQL database instance with the UTF8 character set:

http://bugs.mysql.com/bug.php?id=6604

I originally selected this character set so that I could experiment with international languages such as Japanese and Chinese.  I did not see anything in the Moodle documentation that specifies what character set should be used for the database.  So...

1. What character set should the MySQL database be set to for a proper installation of Moodle?

2. If Moodle supports the UTF8 character set, is this a bug?

The error message text is included below:

----- (mysql): CREATE TABLE `mdl_config_plugins` ( `id` int(10) unsigned NOT NULL auto_increment, `plugin` varchar(250) NOT NULL default 'core', `name` varchar(250) NOT NULL default '', `value` text NOT NULL default '', PRIMARY KEY (`id`), UNIQUE KEY `plugin_name` (`plugin`, `name`)) TYPE=MyISAM COMMENT='Moodle modules and plugins configuration variables' ----- 1071: Specified key was too long; max key length is 1000 bytes
      ADODB_mysql._Execute(CREATE TABLE `mdl_config_plugins` (  `id`         int(10) unsigned NOT NULL auto_increment,  `plugin`     varchar(250) NOT NULL ..., false) % line  837, file: adodb.inc.php
   ADODB_mysql.Execute(CREATE TABLE `mdl_config_plugins` (  `id`         int(10) unsigned NOT NULL auto_increment,  `plugin`     varchar(250) NOT NULL ...) % line   55, file: datalib.php
execute_sql(CREATE TABLE `mdl_config_plugins` (  `id`         int(10) unsigned NOT NULL auto_increment,  `plugin`     varchar(250) NOT NULL ...) % line  185, file: datalib.php
modify_database(c:\Inetpub\wwwroot\moodle/lib/db/mysql.sql) % line  114, file: index.php
Error


Average of ratings: -
In reply to Matthew Johnson

Re: Install Problem - 1071: Specified key was too long. Max key length is 500

by Roberto Fonseca -

I'm having the same problem.

I've tried, several times, to upgrade my existing installation, but I can't fix this 1071 error.

Windows Server 2003 + IIS 6.0
MySQL Essential 4.1.11
PHP 5.0.4

Average of ratings: -
In reply to Matthew Johnson

Re: Install Problem - 1071: Specified key was too long. Max key length is 500

by Eloy Lafuente (stronk7) -
Picture of Core developers Picture of Documentation writers Picture of Moodle HQ Picture of Peer reviewers Picture of Plugin developers Picture of Testers
Hi,

if I'm not wrong the 1071 MySQL Error is specially noticeable because, under UTF-8, every character uses three bytes to get stored. So, everything over 500/3 (1000/3 in latest versions, I've read) characters will throw such error (under UTF-8).

So, we should be really careful about what are we indexing and their length, indexing fields partially if necessary.

333 seems to be the limit for UTF-8...

Ciao smile
Average of ratings: -
In reply to Eloy Lafuente (stronk7)

Re: Install Problem - 1071: Specified key was too long. Max key length is 500

by Melissa Fernandez -
Similar/Same problem attempting to upgrade to Moodle 1.5

Error Message: 1071: Specified key was too long. Max key length is 1000
MySQL: Version 4.1
PHP: Version 4.3.10

I have tried downloading the latest daily download from the CVS with no luck.  Like the other posters i have my database configured to default to UTF-8 encoding.

Average of ratings: -
In reply to Melissa Fernandez

Re: Install Problem - 1071: Specified key was too long. Max key length is 500

by Melissa Fernandez -
bump.  Anyone been able to come up with a solution to this problem?
Average of ratings: -
In reply to Melissa Fernandez

Re: Install Problem - 1071: Specified key was too long. Max key length is 500

by Melissa Fernandez -
I know we are on the verge of the release of 1.5.1 and wonder whether this has been resolved or if someone has come up with an ad-hoc solution.  MySQL is not my strong suit but it appears to me the entire database would need to be recreated using a different character set in order to ensure there wouldn't be an issue furthur down the line.  Is this correct?  Would changing the character set of only that particular table work?  Is it even possible? 

Thanks in advance.
Average of ratings: -
In reply to Melissa Fernandez

Re: Install Problem - 1071: Specified key was too long. Max key length is 500

by Leonhard Küllinger -

I am having the same problem since march with the 1.4.5 an now with 1.5+. There were strange errors, but it seemed that moodle isn´t affected.

since one Week moodle hangs. I have backups, but i need a hint how to resolve the prob. I tried to create a new moodle and restore the backup. This works, but wikis are shown as surveys, and don´t work.

config:

W3Kserver, iis6, mysql 4.1.9 and php 5.04

Average of ratings: -
In reply to W Page

Re: Install Problem - 1071: Specified key was too long. Max key length is 500

by Anthony Borrow -
Picture of Core developers Picture of Plugin developers Picture of Testers
I too had this error with 1.5.1.

Here is the solution that I used - it is related the UTF8 character set as indicated above. Either the index needs to be shortened (which is up to the developers) or avoid using UTF8 (not desirable for the long term but its the quick and easy solution).

MySQL server is setup for UTF8 by default so I dropped the database and (re)created the moodle database with the following (using PHPMyAdmin):

CREATE DATABASE `moodle15` DEFAULT CHARACTER SET latin1 COLLATE latin1_bin;

Then, I deleted the config.php file and went back to http://server.com/moodle/index.php and allowed the installation routine to (re)create the tables. Hope this helps.
Average of ratings: -