[Building Sakai] sakai-2.6.0-rc05: MySQL 5.1 ALTER_TABLE issues

[Anthony Whyte]

Last night while testing sakai-2.6.0-rc05 against MySQL 5.1.34 (Sakai  
QA tests against MySQL 5.0.*) I encountered startup issues involving  
failing in ALTER_TABLE statements in three out of five tests:, e.g.

2009-06-15 21:58:51,297  WARN main  
org 
.sakaiproject.springframework.orm.hibernate.AddableSessionFactoryBean  
- Unsuccessful schema statement: alter table ASN_AP_ITEM_T add index  
FK514CEE15935EEE07 (ID), add constraint FK514CEE15935EEE07 foreign key  
(ID) references ASN_SUP_ITEM_T (ID)
com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: Duplicate key  
name 'FK514CEE15935EEE07'

I have not encountered errors like this before when testing any of the  
various alpha, beta or rc tags against MySQL 5.0 nor has David Horwitz  
nor Matt Jones who tried out the tag on their respective QA servers.   
Matt Jones also tested rc05 against MySQL 5.1.31 on Ubuntu with no  
issues.  I reverted to MySQL 5.0.83 and could not replicate the errors.

MySQL 5.0/5.1 documentation has this to say:

"ALTER TABLE changes a table to the current character set. If you get  
a duplicate-key error during ALTER TABLE, the cause is either that the  
new character sets maps two keys to the same value or that the table  
is corrupted. In the latter case, you should run REPAIR TABLE on the  
table."

  http://dev.mysql.com/doc/refman/5.1/en/alter-table-problems.html

There is an ancient Hibernate issue that resembles the issue I  
encountered last night but it's so old I don't know what to make of  
it.  Sakai uses <sakai.hibernate.version>3.2.5.ga</ 
sakai.hibernate.version>

http://opensource.atlassian.com/projects/hibernate/browse/HB-1196

I have not been able to determine yet if this is a local problem  
(corrupted tables or db) or a more general one.  I'd appreciate it if  
others would try sakai-2.6.0-rc05 against MySQL 5.1 and then let me  
know if you encounter this error.

Cheers,

Anth

_______________________


5.1 environment

Mac OS X 10.5.7
Tomcat 5.5.26
MySQL 5.1.34  (InnoDB, default char set UTF-8)
MySQL Connector/J 5.1.5  (tried 5.1.6 as well)
Sakai 2.6.0-rc05/Kernel 1.0.9

5.0 environment

Mac OS X 10.5.7
Tomcat 5.5.26
MySQL 5.0.83 (InnoDB, default char set UTF-8)
MySQL Connector/J 5.1.6
Sakai 2.6.0-rc05/Kernel 1.0.9

sakai.properties

## MySQL settings - make sure to alter as appropriate
vendor at org.sakaiproject.db.api.SqlService=mysql
driverClassName at javax.sql.BaseDataSource=com.mysql.jdbc.Driver
hibernate.dialect=org.hibernate.dialect.MySQLDialect
url at javax.sql.BaseDataSource=jdbc:mysql://127.0.0.1:3306/sakai? 
useUnicode=true&characterEncoding=UTF-8
validationQuery at javax.sql.BaseDataSource=select 1 from DUAL
defaultTransactionIsolationString 
@javax.sql.BaseDataSource=TRANSACTION_READ_COMMITTED
-------------- next part --------------
A non-text attachment was scrubbed...
Name: smime.p7s
Type: application/pkcs7-signature
Size: 2417 bytes
Desc: not available
Url : http://collab.sakaiproject.org/pipermail/sakai-dev/attachments/20090616/a725eef3/attachment.bin