#97 mysql_create_schema.sql exceeds key length constraint

version 1.50
open
nobody
5
2009-03-17
2009-03-17
10function
No

While deploying OSRM under a mysql database , i ran into the following error :

seb@pixies:~/osrmt/v1_50/client/schema$ mysql -p OSRMT -u osrmt < mysql_create_schema.sql
Enter password:
ERROR 1071 (42000) at line 610: Specified key was too long; max key length is 1000 bytes
$ mysql --version
mysql Ver 14.12 Distrib 5.0.51a, for debian-linux-gnu (i486) using readline 5.2

This is due to the fact than in UTF-8 charset each caracter requires a 3 bytes space
So
- storage_file_name varchar(200),
- storage_directory varchar(255),

When use as a key takes
(200+255)*3=1365 bytes !!

Here's a proposed patch : (reducing storage_file_name to 161 caracters and storage_directory to 162 caracters

@@ -588,8 +588,8 @@
file_type_ref_id int not null default 0,
version double not null default 0,
source_file varchar(255),
- storage_file_name varchar(200),
- storage_directory varchar(255),
+ storage_file_name varchar(161),
+ storage_directory varchar(162),
file_name varchar(255),
active_version_ind int not null default 1,
description varchar(255),
@@ -620,8 +620,8 @@
file_type_ref_id int not null default 0,
version double not null default 0,
source_file varchar(255),
- storage_file_name varchar(200),
- storage_directory varchar(255),
+ storage_file_name varchar(161),
+ storage_directory varchar(162),
file_name varchar(255),
active_version_ind int not null default 1,
description varchar(255),

Hope this helps ! Thanks for all the good job ! OSRMT rocks !

Discussion