[Building Sakai] MySQL Error on upgrade script for version 2.9 to version 10

[Niebel, William D. (Bill) (wdn5e)]

Not a solution for you, but adding some info.

We also run MySql with UTF-8.

We reported a related issue SAK-27581 which remains unaddressed, that BasicLTI wrongly reports that an auto.ddl restart is required for tool database conversion, even after the conversion has actually been done -- after being done repeatedly by auto.ddl and then manually.  In looking at that problem, I saw that UTF-8 characters -- at least with mySql -- require 1-4 bytes, depending on the actual characters in the column value's string.  So either of "your" keys could require 255 characters * 4 bytes/character = 1020 bytes > 1000 bytes.

(FWIW, "our" problem also involves java ResultSetMetaData.getColumnDisplaySize() returning 3/4 of the column byte-width -- same for ResultSetMetaData.getPrecision() -- as if these methods return, though I think undocumented, only an approximation of the storage requirement.  So for that problem, using the Java API just doesn't give the right comparison and the BasicLTI code wrongly advised another auto.ddl run.  In that case, the warning in the log prompts multiple retries until you see how the tool has this wrong.)

Is pre-release testing done only with Oracle, and not with mySql?

Bill Niebel
University of Virginia


________________________________
From: sakai-dev-bounces at collab.sakaiproject.org [sakai-dev-bounces at collab.sakaiproject.org] on behalf of James Scoble [scoble.james at gmail.com]
Sent: Wednesday, November 12, 2014 7:16 AM
To: sakai-dev at collab.sakaiproject.org
Subject: [Building Sakai] MySQL Error on upgrade script for version 2.9 to version 10

Hi folks
In the script to upgrade the database to version 10, the following entry is found:

-- ------------------------
--
-- SAK-23812 Peer Review feature for Assignments
--
-- ------------------------

CREATE TABLE ASN_PEER_ASSESSMENT_ITEM_T  (
    SUBMISSION_ID       varchar(255) NOT NULL,
    ASSESSOR_USER_ID    varchar(255) NOT NULL,
    ASSIGNMENT_ID       varchar(255) NOT NULL,
    SCORE               int(11) NULL,
    REVIEW_COMMENT             varchar(6000) NULL,
    REMOVED             bit(1) NULL,
    SUBMITTED             bit(1) NULL,
    PRIMARY KEY(SUBMISSION_ID,ASSESSOR_USER_ID)
);

create index PEER_ASSESSOR_I on ASN_PEER_ASSESSMENT_ITEM_T (SUBMISSION_ID, ASSESSOR_USER_ID);
create index PEER_ASSESSOR2_I on ASN_PEER_ASSESSMENT_ITEM_T (ASSIGNMENT_ID, ASSESSOR_USER_ID);

-- --------------------
--
-- END SAK-23812 Peer Review feature for Assignments
--
-- --------------------


When I try to run this, I get the following error:

#1071 - Specified key was too long; max key length is 1000 bytes

This seems to be based on the varchar length - I'm guessing UTF-8 chars take up more than one byte.
How have others worked around this problem?

regards
James

-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://collab.sakaiproject.org/pipermail/sakai-dev/attachments/20141112/9498fe19/attachment.html