Project

General

Profile

Bug #1805

UTF8 Migration errors

Added by Omar Arino over 9 years ago. Updated about 9 years ago.

Status:
Feature implemented
Priority:
High
Category:
-
Target version:
Start date:
03/08/2010
Due date:
% Done:

100%

Estimated time:
Complexity:
Normal
SCRUM pts - complexity:
?

Description

In the process of upgrading to Camilo 1.8.7 or 1.8.7.1 encounter the following errors in the log:

[03-Aug-2010 08:51:51] Starting migration process from 1.8.6.1 (1280836311)
[03-Aug-2010 08:52:57] Getting courses for files updates: SELECT * FROM dokeos_main.course

[03-Aug-2010 08:53:03] Error in ALTER TABLE `settings_current` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;: Specified key was too long; max key length is 1000 bytes
[03-Aug-2010 08:53:03] Error in ALTER TABLE `settings_options` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;: Specified key was too long; max key length is 1000 bytes

[03-Aug-2010 08:54:29] Error in ALTER TABLE `course_setting` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;: Specified key was too long; max key length is 1000 bytes
[03-Aug-2010 08:54:38] Error in ALTER TABLE `course_setting` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;: Specified key was too long; max key length is 1000 bytes
[03-Aug-2010 08:54:56] Error in ALTER TABLE `course_setting` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;: Specified key was too long; max key length is 1000 bytes
[03-Aug-2010 08:55:42] Error in ALTER TABLE `course_setting` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;: Specified key was too long; max key length is 1000 bytes
[03-Aug-2010 08:55:50] Error in ALTER TABLE `course_setting` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;: Specified key was too long; max key length is 1000 bytes
[03-Aug-2010 08:55:59] Error in ALTER TABLE `course_setting` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;: Specified key was too long; max key length is 1000 bytes
[03-Aug-2010 08:56:08] Error in ALTER TABLE `course_setting` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;: Specified key was too long; max key length is 1000 bytes
[03-Aug-2010 08:56:17] Error in ALTER TABLE `course_setting` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;: Specified key was too long; max key length is 1000 bytes
[03-Aug-2010 08:57:46] Error in ALTER TABLE `course_setting` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;: Specified key was too long; max key length is 1000 bytes
[03-Aug-2010 08:58:37] Error in ALTER TABLE `course_setting` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;: Specified key was too long; max key length is 1000 bytes

I searched in the MySQL forums and this error is a limitation to create indexes using UTF8. The solution presented is to create indices by assigning a character size in each field used in it.

History

#1

Updated by Ivan Tcholakov about 9 years ago

  • Target version set to 1.8.8 alpha

I think that the problematic indexes should be removed before the conversion. I compared the structure of the problematic tables in Dokeos 1.8.6.1 and in Chamilo 1.8.7.1. In Chamilo 1.8.7.1 such not very useful indexes do not exist - this is true for the tables settings_current, settings_options. About the tables *.course_setting I wonder at the moment why those errors appear.

#2

Updated by Ivan Tcholakov about 9 years ago

I discovered that in a newly installed system the unique indexes are not created. For eample, there is a statement
ALTER TABLE settings_current ADD UNIQUE unique_setting ( variable , subkey , category, access_url) ;
but it has no implications during the installation, probably due to the same reason - vary long fields.

"The solution presented is to create indexes by assigning a character size in each field used in it." - I agree with this proposal. The indexes may be restored according to the authors' SQL statements.

#3

Updated by Ivan Tcholakov about 9 years ago

  • Status changed from New to Assigned
  • Assignee set to Ivan Tcholakov
  • Priority changed from Normal to High

I am taking this task.

#4

Updated by Ivan Tcholakov about 9 years ago

Here is an example, within the file db_main.sql the statement

ALTER TABLE settings_current ADD UNIQUE unique_setting ( variable , subkey , category, access_url) ;

has to be changed as follows:

ALTER TABLE settings_current ADD UNIQUE unique_setting (variable(110) , subkey(110) , category(110), access_url);

In this statement, numbers greater than 110 cause error 1071, "Specified key was too long; max key length is 1000 bytes".

More:

ALTER TABLE settings_options ADD UNIQUE unique_setting_option (variable, value);

should be:

ALTER TABLE settings_options ADD UNIQUE unique_setting_option (variable(165), value(165));

#5

Updated by Ivan Tcholakov about 9 years ago

In the file migrate-db-1.8.5-1.8.6-pre.sql there is the following statement:

ALTER TABLE course_setting ADD INDEX unique_setting (variable,subkey,category);

A statement like this one:

ALTER TABLE course_setting ADD INDEX unique_setting (variable(110), subkey(110), category(110));

would be safe for the migration to UTF-8. But the introduced index unique_setting does not bring
uniqueness as its name claims. Also, when a new course is created, in the table course_setting this
index does not exist.

I intend somwhere in the migration scripts to place a statement for removing the index:

ALTER TABLE course_setting DROP INDEX unique_setting;

#6

Updated by Ivan Tcholakov about 9 years ago

12430:1de805fe711f Bug #1805 - Migration scripts: An index has been removed from the tables course_setting which prevents conversion of the same tables to UTF-8.
http://code.google.com/p/chamilo/source/detail?r=1de805fe711f3bb4239f17c5c0253e97c84b07fe&repo=classic

#7

Updated by Ivan Tcholakov about 9 years ago

12431:c7035967c478 Bug #1805 - Migration scripts: Removing comments introduced through the previous transaction.
http://code.google.com/p/chamilo/source/detail?r=c7035967c478c759a1510e70f68464caffad6b99&repo=classic

#8

Updated by Ivan Tcholakov about 9 years ago

12432:f1057d3ec97f Bug #1805 - New installation scripts: Some index definitions have been modified in order the correspondent indexes not to exceed the maximum length of 1000 bytes (the table is created with UTF-8 encoding).
http://code.google.com/p/chamilo/source/detail?r=f1057d3ec97f1b3062af568d35b570ea099e227a&repo=classic

#9

Updated by Ivan Tcholakov about 9 years ago

  • % Done changed from 0 to 50

12433:54e6765d3584 Bug #1805 - Migration scripts to 1.8.8: Fixing the encoding conversion failure due to some too long indexes. Adding again in the current migration statements for encoding conversion to UTF-8 for some tables.
http://code.google.com/p/chamilo/source/detail?r=54e6765d35840a5f66da6613087645e4d89e1010&repo=classic

Testing is next to be done at convenient time.

#10

Updated by Ivan Tcholakov about 9 years ago

  • Status changed from Assigned to Feature implemented
  • % Done changed from 50 to 100

I have just done a testing upgrade from Dokeos (tm) 1.8.6.1 to Chamilo 1.8.8alpha-. There are other warnings, but they are not related to encoding conversion of the tables. I think, this bug has been fixed, so I am closing the task. If there are doubts you may reopen it, no problem.

Also available in: Atom PDF