MySQL : Error of init db : Specified key was too long; max key length is 767 bytes;

[antoine_h]

Hello,

For a config of GateIn withMySQL, I get this exception :

21:52:43,397 INFO  [JDBCWorkspaceDataContainer] Using a dialect 'MySQL'
21:52:43,477 ERROR [JDBCWorkspaceDataContainer] Error of init db org.exoplatform.services.jcr.impl.util.jdbc.DBInitializerException: Could not create db schema of DataSource: 'system'. Reason: Specified key was too long; max key length is 767 bytes; . Last command: CREATE UNIQUE INDEX JCR_IDX_SITEM_PARENT ON JCR_SITEM(CONTAINER_NAME, PARENT_ID, NAME, I_INDEX, I_CLASS, VERSION DESC)
org.exoplatform.services.jcr.impl.util.jdbc.DBInitializerException: Could not create db schema of DataSource: 'system'. Reason: Specified key was too long; max key length is 767 bytes; . Last command: CREATE UNIQUE INDEX JCR_IDX_SITEM_PARENT ON JCR_SITEM(CONTAINER_NAME, PARENT_ID, NAME, I_INDEX, I_CLASS, VERSION DESC)
    at org.exoplatform.services.jcr.impl.util.jdbc.DBInitializer.init(DBInitializer.java:376)
    at org.exoplatform.services.jcr.impl.storage.jdbc.optimisation.CQJDBCWorkspaceDataContainer.initDatabase(CQJDBCWorkspaceDataContainer.java:252)
    at org.exoplatform.services.jcr.impl.storage.jdbc.JDBCWorkspaceDataContainer.<init>(JDBCWorkspaceDataContainer.java:394)
    at org.exoplatform.services.jcr.impl.storage.jdbc.optimisation.CQJDBCWorkspaceDataContainer.<init>(CQJDBCWorkspaceDataContainer.java:73)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingC

this exception is the first one (ie : the base error).

I use MySQL 5.1.35-community.

my config in "server/default/conf/gatein/configuration.properties" is :

# JCR
gatein.jcr.config.type=local
gatein.jcr.datasource.name=java:jdbcjcr
gatein.jcr.datasource.dialect=mysql
gatein.jcr.datasource.driver=com.mysql.jdbc.Driver
gatein.jcr.datasource.url=jdbc:mysql://localhost:3306/mydb_jcr_${name}?relaxAutoCommit=true&amp;autoReconnect=true&amp;useUnicode=true&amp;characterEncoding=latin1
gatein.jcr.datasource.username=myportal_user
gatein.jcr.datasource.password=

gatein.jcr.data.dir=${gatein.data.dir}/jcr
gatein.jcr.storage.data.dir=${gatein.jcr.data.dir}/values
gatein.jcr.cache.config=classpath:/conf/jcr/jbosscache/${gatein.jcr.config.type}/config.xml
gatein.jcr.lock.cache.config=classpath:/conf/jcr/jbosscache/${gatein.jcr.config.type}/lock-config.xml
gatein.jcr.index.data.dir=${gatein.jcr.data.dir}/lucene
gatein.jcr.index.changefilterclass=org.exoplatform.services.jcr.impl.core.query.DefaultChangesFilter
gatein.jcr.index.cache.config=classpath:/conf/jcr/jbosscache/cluster/indexer-config.xml
gatein.jcr.jgroups.config=classpath:/conf/jcr/jbosscache/cluster/udp-mux.xml

# IDM
gatein.idm.datasource.name=java:jdbcidm
gatein.idm.datasource.driver=com.mysql.jdbc.Driver
gatein.idm.datasource.url=jdbc:mysql://localhost:3306/mydb_idm_${name}?relaxAutoCommit=true&amp;autoReconnect=true&amp;useUnicode=true&amp;characterEncoding=latin1
gatein.idm.datasource.username=myportal_user
gatein.idm.datasource.password=

any idea of what this is coming from ?

thanks,

Antoine
JBoss Portal and GateIn (JSR-286), JSF, Richfaces, J2EE, Drools, BRMS.
http://www.sysemo.com/Sysemo-expertise-portails-jboss-portal.php

[antoine_h]

This error comes from the fact that my db was set by default to CharacterSet UTF8, for this project (for the I18n aspect of the EJB).

It is said in the doc :

GateIn 3.1 assumes the default encoding for your database is     latin1. You may need to change this parameter for     your database in order for GateIn 3.1 to work properly.

so that error is what happens if not in latin1...

so to create the database with mysql :

CREATE DATABASE `mydb_jcr_portal` CHARACTER SET latin1 COLLATE latin1_general_ci;

(and :

GRANT ALL ON mydb_jcr_portal.* TO gatein_user@localhost;)

Antoine
JBoss Portal and GateIn (JSR-286), JSF, Richfaces, J2EE, Drools, BRMS.
http://www.sysemo.com/Sysemo-expertise-portails-jboss-portal.php

[antoine_h]

make sure the database encoding is latin1.

so to create the database with mysql :

CREATE DATABASE `mydb_jcr_portal` CHARACTER SET latin1 COLLATE latin1_general_ci;

[snowbot]

Hi,

My company needs to create a portal for use in Asia, so latin1 database encoding will not work.  Will GateIn support UTF-8 if I use another database? What is the minimum key size required by GateIn? are there any other accomodations or limitations of GateIn in enabling UTF-8 support? (For example, jboss may require UTF-8 datasource connection url parameters.. anything else?)

[nfilotto]

If you have to use the utf-8 charset at your db level for your needs, you will need to configure explicitly the specific dialect for mysql in utf8 which is mysql-utf8