Problem using MySQL

[pjpbakker]

Hi,

When I run liferay-portal-6.1.20-ee-ga2, configured to use MySQL, then when I deploy the lr-i18n-editor-portlet-6.1.1.1.war, I get a lot of exceptions

12:45:23,589 ERROR [pool-2-thread-2][JDBCExceptionReporter:76] Table 'lportal.editor_messagesource' doesn't exist
com.liferay.portal.kernel.exception.SystemException: com.liferay.portal.kernel.dao.orm.ORMException: org.hibernate.exception.SQLGrammar
Exception: could not load an entity: [com.rcs.service.model.impl.MessageSourceImpl#component[key,locale]{locale=nl_NL, key=currency.BIF
}]
        at com.liferay.portal.service.persistence.impl.BasePersistenceImpl.processException(BasePersistenceImpl.java:193)
        at com.rcs.service.service.persistence.MessageSourcePersistenceImpl.fetchByPrimaryKey(MessageSourcePersistenceImpl.java:530)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
...
Caused by: com.liferay.portal.kernel.dao.orm.ORMException: org.hibernate.exception.SQLGrammarException: could not load an entity: [com.
rcs.service.model.impl.MessageSourceImpl#component[key,locale]{locale=nl_NL, key=currency.BIF}]
        at com.liferay.portal.dao.orm.hibernate.ExceptionTranslator.translate(ExceptionTranslator.java:30)
        at com.liferay.portal.dao.orm.hibernate.SessionImpl.get(SessionImpl.java:131)
        at com.liferay.portal.kernel.dao.orm.ClassLoaderSession.get(ClassLoaderSession.java:237)
        at com.rcs.service.service.persistence.MessageSourcePersistenceImpl.fetchByPrimaryKey(MessageSourcePersistenceImpl.java:524)
        ... 61 more
Caused by: org.hibernate.exception.SQLGrammarException: could not load an entity: [com.rcs.service.model.impl.MessageSourceImpl#compone
nt[key,locale]{locale=nl_NL, key=currency.BIF}]
        at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:92)
        at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
...
Caused by: com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: Table 'lportal.editor_messagesource' doesn't exist
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:936)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2985)
        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1631)
        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1723)
        at com.mysql.jdbc.Connection.execSQL(Connection.java:3256)
        at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1313)
...

Is this due to some configuration I omitted? I unzipped Liferay and added a portal-ext.properties with the database configured. Liferay starts fine.

Help is much appreciated Paul

[florenciagadea]

Hi Paul,

This portlet was built for the Community Edition, not the Enterprise Edition (the one you are using). Please test it in the Community Edition and if you have exceptions please let us know.

Regards,

Florencia.

[pjpbakker]

Hi Florencia,

I switched to liferay-portal-6.1.1-ce-ga2, bundled with tomcat and recreated a fresh empty lportal database:

drop database lportal;
create database lportal;
grant all on lportal.* to 'liferay'@'localhost' identified by 'liferay';

portal-ext.properies contains:

jdbc.default.driverClassName=com.mysql.jdbc.Driver
jdbc.default.url=jdbc:mysql://localhost/lportal?useUnicode=true&characterEncoding=UTF-8&useFastDateParsing=false
jdbc.default.username=liferay
jdbc.default.password=liferay

Unfortunately, the problem remains, with the same exceptions being thrown.

Paul

[florenciagadea]

Hi Paul,

Can you manually run what is inside lr-i18n-editor-portlet/docroot/WEB-INF/sql/create/create-mysql.sql ? Please do this, and then restart the server.

Regards,

Flor.

[pjpbakker]

Hi Florencia,

Thanks for your reply.
There is no lr-i18n-editor-portlet/docroot/WEB-INF/sql/create, but within lr-i18n-editor-portlet/docroot/WEB-INF/sql, there are indexes.sql and tables.sql. However, the sql in those files is not suitable to run in mysql. There is no STRING type in mysql. Looking at the hsql generated by liferay, key_ and value end up as VARCHAR(16777216), which corresponds to MEDIUMTEXT in mysql. The maximum index key prefix length is 767 and must be specified, so if I change the queries

for tables-mysql.sql:

create table editor_MessageSource (
   key_ MEDIUMTEXT not null,
   locale VARCHAR(75) not null,
   value MEDIUMTEXT null,
   bundle VARCHAR(75) null,
   PRIMARY KEY (key_(255), locale)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

and for indexes-mysql.sql:

create index IX_EE40F8CC on editor_MessageSource (bundle);
create index IX_57BC1CAA on editor_MessageSource (key_(767));
create index IX_784255F8 on editor_MessageSource (key_(767), locale);
create index IX_5DE2996E on editor_MessageSource (key_(767), locale, bundle);

the deployment works. The charset has to be UTF-8 because some 10 values fail to be inserted for latin1. Should the deploy phase be altered to use the correct sql syntax for a given database flavor?

Regards, Paul

[florenciagadea]

Hi Paul,

The sql files I mentioned are created once you compile with the Liferay IDE. Anyway, I just updated the project in github, you will see those files now. The portlet is supposed to detect the database you are working on and execute the corresponding files. This is Liferay's out-of-the-box functionality.

I hope this helps. Regards,

Flor.

[pjpbakker]

Hi Flor,

I haven't worked with the Liferay IDE a lot, normally using IDEA and maven to develop plugins. But I'm there now. I did as you suggested and ran the create-mysql.sql. That didn't work (ERROR 1146 (42S02) at line 7: Table 'lportal.editor_messagesource' doesn't exist) and it doesn't surprise me because the create/create-mysql.sql tries to create indexes, not tables. It does almost the same as indexes/indexes-mysql.sql.

Paul

[florenciagadea]

Hi Paul,

You are right, my mistake. So, did you open it with the Liferay IDE and compiled it? Because that is what most of my collegues did to make it work. I'll check the deployment again.

So, is it working now? Did it import the Language properties files?

Regards,

Flor.

[pjpbakker]

Hi Flor,

Yes I did build it using Liferay IDE and it produced the same sql files as you provided on GitHub. Only when I run the table creation scripts I posted previously and named accordingly by coincidence, can I deploy the portlet successfully and then it imports the Language properties files like a charm.

Best, Paul