[Contrib: Evaluation System] EVALSYS broken for MySQL???

[Stephen Marquard]

This obviously needs a short-term solution, but longer-term, the key
that seems to be failing here is:

unique (USER_ID, GROUP_ID, ASSIGN_TYPE, EVALUATION_FK)

which is intended to avoid duplicates in EVAL_ASSIGN_USER, but in
practice can be of dubious value because it can lead to "fake" deadlocks
from multiple inserts to this table from key collisions, viz.
http://jira.sakaiproject.org/browse/EVALSYS-781

Note Aaron's comment there that it may be preferable just to drop the
constraint. Also I think it may be worth a discussion about whether
always populating EVAL_ASSIGN_USER Is helpful, as it has caused us some
local support issues with users added to a site after an evaluation was
published.

Regards
Stephen 
 
>>> Jim Eng <jimeng at umich.edu> 6/16/2010 9:07 AM >>> 
> A better solution would be to build an index using a hash of the
strings rather than the strings themselves. 


Would that be done with a code change in evaluation? 

I am using version 5.1.47 of MySQL, along with version 5.0.5 of the
JDBC connector.

Jim


On Jun 16, 2010, at 11:04 AM, Ellis, Richard wrote:

> There’s a long thread on this topic (years worth) in the MySQL lit.
Gonzalo got around it by changing the character set so fewer bytes were
stored in the strings. A better solution would be to build an index
using a hash of the strings rather than the strings themselves.  That
should be faster, too.
> 
> Dick
> From: Jim Eng <jimeng at umich.edu>
> Date: Wed, 16 Jun 2010 10:58:55 -0400
> To: evaluation <evaluation at collab.sakaiproject.org>
> Subject: [Contrib: Evaluation System] EVALSYS broken for MySQL???
> 
> I think we've discussed this before, but I am not finding a record of
it.
> 
> I am attempting to build trunk of evaluation along with the 1.2.5 tag
of hierarchy in sakai trunk.  It builds and deploys just fine but fails
to start up due to SQL errors.  In particular, there are a number of
errors relating to primary keys that exceed MySQL's 1000-byte maximum
key length.  Here's an example:
> 
> WARN main
org.sakaiproject.springframework.orm.hibernate.AddableSessionFactoryBean
- Unsuccessful schema statement: create table EVAL_ASSIGN_USER (ID
bigint not null auto_increment, EID varchar(255), LAST_MODIFIED datetime
not null, OWNER varchar(255) not null, USER_ID varchar(255) not null,
GROUP_ID varchar(255) not null, ASSIGN_TYPE varchar(255) not null,
ASSIGN_STATUS varchar(255) not null, LIST_ORDER integer not null,
ASSIGN_GROUP_ID bigint, EVALUATION_FK bigint not null, primary key (ID),
unique (USER_ID, GROUP_ID, ASSIGN_TYPE, EVALUATION_FK))
> com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: Specified key
was too long; max key length is 1000 bytes
> 
> Have other people seen this with MySQL?  Is there an existing fix or
workaround?
> 
> Thanks.
> 
> Jim
> _______________________________________________
> evaluation mailing list
> evaluation at collab.sakaiproject.org
> http://collab.sakaiproject.org/mailman/listinfo/evaluation
> 
> TO UNSUBSCRIBE: send email to
evaluation-unsubscribe at collab.sakaiproject.org with a subject of
"unsubscribe"
> 




 

###
UNIVERSITY OF CAPE TOWN 

This e-mail is subject to the UCT ICT policies and e-mail disclaimer
published on our website at
http://www.uct.ac.za/about/policies/emaildisclaimer/ or obtainable from
+27 21 650 4500. This e-mail is intended only for the person(s) to whom
it is addressed. If the e-mail has reached you in error, please notify
the author. If you are not the intended recipient of the e-mail you may
not use, disclose, copy, redirect or print the content. If this e-mail
is not related to the business of UCT it is sent by the sender in the
sender's individual capacity.

###