Liferay not working with MySQL

[10年前 に Henry Ng によって更新されました。]

I am using Liferay 6.1.1 , tomcat 7 and Mysql 5.6 and eclipse.

mysql port : localhost 3306
http/1.1 : 8080
I followed the instruction given on their wiki Putting portal-ext.properties into the

{liferay-home}\{tomcat}\webapps\ROOT\WEB-INF\classes
With the following info

jdbc.default.driverClassName=com.mysql.jdbc.Driver
jdbc.default.url=jdbc:mysql://localhost/lportal?useUnicode=true&characterEncoding=UTF-8&useFastDateParsing=false
jdbc.default.username=root
jdbc.default.password=test
Created a database with

create database lportal character set utf8;
The server would run

Apr 21, 2013 6:47:36 PM org.apache.catalina.core.AprLifecycleListener init
INFO: The APR based Apache Tomcat Native library which allows optimal performance in production environments was not found on the java.library.path: C:\Users\Hnshandroid\Downloads\Liferay vid\liferay-portal-6.1.1-ce-ga2\tomcat-7.0.27\jre1.6.0_20\win\bin;.;C:\Windows\Sun\Java\bin;C:\Windows\system32;C:\Windows;C:\Program Files (x86)\NVIDIA Corporation\PhysX\Common;C:\Program Files\Common Files\Microsoft Shared\Windows Live;C:\Program Files (x86)\Common Files\Microsoft Shared\Windows Live;C:\Program Files (x86)\AMD APP\bin\x86_64;C:\Program Files (x86)\AMD APP\bin\x86;C:\Windows\system32;C:\Windows;C:\Windows\System32\Wbem;C:\Windows\System32\WindowsPowerShell\v1.0\;C:\Program Files (x86)\ATI Technologies\ATI.ACE\Core-Static;C:\Program Files (x86)\Windows Live\Shared;D:\Program Files\ATI Technologies\ATI.ACE\Core-Static;C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\;C:\Program Files\Microsoft SQL Server\100\Tools\Binn\;C:\Program Files\Microsoft SQL Server\100\DTS\Binn\;C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\;C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\;C:\Program Files\Intel\WiFi\bin\;C:\Program Files\Common Files\Intel\WirelessCommon\;C:\Program Files\Intel\WiFi\bin\;C:\Program Files\Common Files\Intel\WirelessCommon\
Apr 21, 2013 6:47:36 PM org.apache.coyote.AbstractProtocol init
INFO: Initializing ProtocolHandler ["http-bio-8080"]
Apr 21, 2013 6:47:36 PM org.apache.coyote.AbstractProtocol init
INFO: Initializing ProtocolHandler ["ajp-bio-8009"]
Apr 21, 2013 6:47:36 PM org.apache.catalina.startup.Catalina load
INFO: Initialization processed in 386 ms
Apr 21, 2013 6:47:36 PM org.apache.catalina.core.StandardService startInternal
INFO: Starting service Catalina
Apr 21, 2013 6:47:36 PM org.apache.catalina.core.StandardEngine startInternal
INFO: Starting Servlet Engine: Apache Tomcat/7.0.27
Apr 21, 2013 6:47:36 PM org.apache.catalina.startup.HostConfig deployDescriptor
INFO: Deploying configuration descriptor C:\Users\Hnshandroid\Downloads\Liferay vid\liferay-portal-6.1.1-ce-ga2\tomcat-7.0.27\conf\Catalina\localhost\ROOT.xml
Loading jar:file:/C:/Users/Hnshandroid/Downloads/Liferay%20vid/liferay-portal-6.1.1-ce-ga2/tomcat-7.0.27/webapps/ROOT/WEB-INF/lib/portal-impl.jar!/system.properties
Loading jar:file:/C:/Users/Hnshandroid/Downloads/Liferay%20vid/liferay-portal-6.1.1-ce-ga2/tomcat-7.0.27/webapps/ROOT/WEB-INF/lib/portal-impl.jar!/portal.properties
Loading file:/C:/Users/Hnshandroid/Downloads/Liferay%20vid/liferay-portal-6.1.1-ce-ga2/portal-ide.properties
Loading file:/C:/Users/Hnshandroid/Downloads/Liferay%20vid/liferay-portal-6.1.1-ce-ga2/tomcat-7.0.27/webapps/ROOT/WEB-INF/classes/portal-developer.properties
Loading file:/C:/Users/Hnshandroid/Downloads/Liferay%20vid/liferay-portal-6.1.1-ce-ga2/portal-setup-wizard.properties
Loading file:/C:/Users/Hnshandroid/Downloads/Liferay%20vid/liferay-portal-6.1.1-ce-ga2/tomcat-7.0.27/webapps/ROOT/WEB-INF/classes/portal-ext.properties
Apr 21, 2013 6:47:38 PM org.apache.catalina.core.ApplicationContext log
INFO: Initializing Spring root WebApplicationContext
18:47:42,768 INFO [pool-2-thread-1][DialectDetector:71] Determine dialect for MySQL 5
18:47:42,798 INFO [pool-2-thread-1][DialectDetector:136] Found dialect org.hibernate.dialect.MySQLDialect
Starting Liferay Portal Community Edition 6.1.1 CE GA2 (Paton / Build 6101 / July 31, 2012)
18:48:00,025 ERROR [pool-2-thread-1][JDBCExceptionReporter:76] Table 'lportal.lock_' doesn't exist
18:48:00,534 WARN [pool-2-thread-1][ReleaseLocalServiceImpl:151] Table 'lportal.release_' doesn't exist
18:48:00,535 INFO [pool-2-thread-1][ReleaseLocalServiceImpl:84] Create tables and populate with default data
Till "Create tables and populate with default data" Then it would hang there. If you press stop, this message will appear

Server Liferay v6.1 CE Server (Tomcat 7) at localhost failed to start.
Hope someone has a solution for this

[10年前 に vinod kumar によって更新されました。]

hi henry
have u created lportal schema in your my sql database
if not create it now and restart the server


vinod.

[10年前 に Henry Ng によって更新されました。]

Hi Vinod

Thanks for your reply .
Is it?
schema.run.enabled=true
schema.run.minimal=true
jdbc.default.driverClassName=com.mysql.jdbc.Driver
jdbc.default.url=jdbc:mysql://localhost/lportal?useUnicode=true&characterEncoding=UTF-8&useFastDateParsing=false
jdbc.default.username=root
jdbc.default.password=test

[10年前 に vinod kumar によって更新されました。]

no henry

open your database browser (OR) sql command prompt
create new schema named lportal .

ex:-open mysql command prompt run the following code

create database lportal;

vinod

[10年前 に Henry Ng によって更新されました。]

I have used this

create database lportal character set utf8;

[10年前 に Manish Yadav によって更新されました。]

Hello Henry,
Could you please check, any table created in lportal after starting server???
if your removed database-properties for portal-ext.properties then its correctly started with hsql database or not??

[10年前 に Henry Ng によって更新されました。]

Hi

Table is populated in lportal.

Yes , if i remove it would run perfectly well on the default database.

[10年前 に vinod kumar によって更新されました。]

when you configure mysql database in portal-ext.properties
after restarting your server have you seen any tables generated in the lportal schema.

[10年前 に Henry Ng によって更新されました。]

Before running the server. The database lportal would be empty.

After running , lportal will have lots of table .

[10年前 に Manish Yadav によって更新されました。]

Are you able to see liferay home page : http://localhost:<portNumber>

What configuration of machine you are using????Ram Size

if not then please provide fresh logs files present in below location

1. <liferay server directory >/logs
2. <liferay server directory >/tomcat/logs

[10年前 に Henry Ng によって更新されました。]

I manage to get it working

Turns out I was using mysql 5.6.
I downgraded it to 5.5 and it works .

[10年前 に Andre Skunde によって更新されました。]

Hi,

i had the same issues with liferay-portal-6.1.1-ce-ga2 bundle.

• Creating lportal database with mysql
• Creating user sa
• Setting Grants for user sa on lportal
• Starting JBoss with standalone.bat
• Liferay setip wizards starts with "...Create tables and populate with default .."
• tables and indexes was created
• after some minutes i found "11:20:22,001 INFO [stdout] (http--127.0.0.1-8080-2) Caused by: java.sql.SQLException: Cannot execute statement: impossible to write to binary log since BINLOG_FORMAT = STATEMENT and at least one table uses a storage engine limited to row-based logging. InnoDB is limited to row-logging when transaction isolation level is READ COMMITTED or READ UNCOMMITTED." in jboss log.

One major changes in release of mysql 5.6 is starting with innodb as default storange engine. Therefore binary log settings becomes more important.
After i have changed binlog_format="STATEMENT" to binlog_format="MIXED" i could finish installation and sucessfully login.

I hope this helps
Andre

[10年前 に Andre Skunde によって更新されました。]

Hi,

another issue with mysql 5.6 is that creation of some indexes during installation fails.

[BaseDB:428] Specified key was too long; max key length is 767 bytes: create index IX_89509087 on User_ (companyId, openId);

[[BaseDB:428] Specified key was too long; max key length is 767 bytes: create index IX_7020130F on SCProductVersion (directDownloadURL);

When you check database with
show indexes from User_ where column_name='openId';
show indexes from SCProductVersion where column_name='directDownloadURL';
you will see that this indexes are missing.

Depending on page size we have innodb key length restrictions. But for a 16K Innodb_page_size this should be 3072 bytes.

Why does report above statements a problem with key length of 767 bytes?

Thanks in advance
Andre

[10年前 に Daniel Tyger によって更新されました。]

Andre Skunde:

Hi,

another issue with mysql 5.6 is that creation of some indexes during installation fails.

[BaseDB:428] Specified key was too long; max key length is 767 bytes: create index IX_89509087 on User_ (companyId, openId);

[[BaseDB:428] Specified key was too long; max key length is 767 bytes: create index IX_7020130F on SCProductVersion (directDownloadURL);

When you check database with
show indexes from User_ where column_name='openId';
show indexes from SCProductVersion where column_name='directDownloadURL';
you will see that this indexes are missing.

Depending on page size we have innodb key length restrictions. But for a 16K Innodb_page_size this should be 3072 bytes.

Why does report above statements a problem with key length of 767 bytes?

Thanks in advance
Andre

+1 - we are also experiencing this when applying indexes post a 6.1 upgrade:

ERROR 1071 (42000) at line 410: Specified key was too long; max key length is 767 bytes

which is due to this statement:

create unique index IX_DD635956 on Lock_ (className, key_, owner);

Is it ok to ignore this? How to work around it?

[10年前 に Rob Silver によって更新されました。]

Not to sound simple here - But can we all agree there is currently no way to get MySQL version 5.6 to work with Liferay 6.1.0 or 6.1.1?
Does anyone know about MySQL version 5.7 ???

[10年前 に David H Nebinger によって更新されました。]

The key issue has been opened as a bug on the 6.2 line (and also applies to 6.1 line).

It is not a high priority (I believe) because an index is just a performance improving thing, and lack of an index does not break the code.

You can use newer versions of MySQL for Liferay, but you must update the mysql jar in the global lib folder (lib/ext in Tomcat).

[10年前 に Rob Silver によって更新されました。]

To which version of mysql.jar ?? e.g MySQL 5.6 ? MySQL 5.7 ??

Note I have recently been able to get MariaDB
5.3.3-MariaDB-rc Source distribution

to work with Liferay
but to the MANIFEST in mysql.jar:

Manifest-Version: 1.0
Ant-Version: Apache Ant 1.6.5
Created-By: 1.4.2-b28 (Sun Microsystems Inc.)
Built-By: mysqldev
Name: common
Specification-Title: JDBC
Specification-Version: 3.0
Specification-Vendor: Sun Microsystems Inc.
Implementation-Title: MySQL Connector/J
Implementation-Version: 5.0.7
Implementation-Vendor-Id: com.mysql
Implementation-Vendor: MySQL AB


Can you specify the version of mysql.jar that in fact works with later versions of MySQL please!

[10年前 に David H Nebinger によって更新されました。]

Liferay doesn't do anything magical w/ mysql, you just need the version of the connector j jar that works with your version of mysql. Replace the mysql.jar in tomcat's lib/ext directory with the jar you download and you should be fine.

[10年前 に Rob Silver によって更新されました。]

Can you point me to the download link for the mysql.jar file you speak of please?

[10年前 に Mika Koivisto によって更新されました。]

You can download MySQL connector from dev.mysql.com

[10年前 に Sharath Babu A によって更新されました。]

Hi Guys,

I am using
Liferay6.1.0
MySql5.6 with workbench.

I have configured the DB as we do generally in Liferay earlier versions by configuring in the portal-ext.properties and started the server from the Eclipse IDE.

Mar 4, 2014 6:45:08 PM org.apache.coyote.AbstractProtocol init
INFO: Initializing ProtocolHandler ["http-bio-8080"]
Mar 4, 2014 6:45:08 PM org.apache.coyote.AbstractProtocol init
INFO: Initializing ProtocolHandler ["ajp-bio-8009"]
Mar 4, 2014 6:45:08 PM org.apache.catalina.startup.Catalina load
INFO: Initialization processed in 537 ms
Mar 4, 2014 6:45:08 PM org.apache.catalina.core.StandardService startInternal
INFO: Starting service Catalina
Mar 4, 2014 6:45:08 PM org.apache.catalina.core.StandardEngine startInternal
INFO: Starting Servlet Engine: Apache Tomcat/7.0.23
Mar 4, 2014 6:45:08 PM org.apache.catalina.startup.HostConfig deployDescriptor
INFO: Deploying configuration descriptor G:\liferay-portal-6.1.0-ce-ga1\tomcat-7.0.23\conf\Catalina\localhost\PortlDetails-portlet.xml
Mar 4, 2014 6:45:08 PM org.apache.catalina.startup.HostConfig deployDescriptor
WARNING: A docBase G:\liferay-portal-6.1.0-ce-ga1\tomcat-7.0.23\webapps\PortlDetails-portlet inside the host appBase has been specified, and will be ignored
Mar 4, 2014 6:45:08 PM org.apache.catalina.startup.SetContextPropertiesRule begin
WARNING: [SetContextPropertiesRule]{Context} Setting property 'source' to 'org.eclipse.jst.jee.serverortlDetails-portlet' did not find a matching property.
Mar 4, 2014 6:45:08 PM org.apache.catalina.startup.HostConfig deployDescriptor
INFO: Deploying configuration descriptor G:\liferay-portal-6.1.0-ce-ga1\tomcat-7.0.23\conf\Catalina\localhost\ROOT.xml
Loading jar:file:/G:/liferay-portal-6.1.0-ce-ga1/tomcat-7.0.23/webapps/ROOT/WEB-INF/lib/portal-impl.jar!/system.properties
18:45:10,126 INFO [EasyConf:122] Refreshed the configuration of all components
18:45:10,460 INFO [ConfigurationLoader:56] Properties for jar:file:/G:/liferay-portal-6.1.0-ce-ga1/tomcat-7.0.23/webapps/ROOT/WEB-INF/lib/portal-impl.jar!/portal loaded from [file:/G:/liferay-portal-6.1.0-ce-ga1/tomcat-7.0.23/webapps/ROOT/WEB-INF/classes/portal-developer.properties, file:/G:/liferay-portal-6.1.0-ce-ga1/portal-ide.properties, file:/G:/liferay-portal-6.1.0-ce-ga1/portal-setup-wizard.properties, file:/G:/liferay-portal-6.1.0-ce-ga1/tomcat-7.0.23/webapps/ROOT/WEB-INF/classes/portal-ext.properties, jar:file:/G:/liferay-portal-6.1.0-ce-ga1/tomcat-7.0.23/webapps/ROOT/WEB-INF/lib/portal-impl.jar!/portal.properties]
Loading jar:file:/G:/liferay-portal-6.1.0-ce-ga1/tomcat-7.0.23/webapps/ROOT/WEB-INF/lib/portal-impl.jar!/portal.properties
Loading file:/G:/liferay-portal-6.1.0-ce-ga1/tomcat-7.0.23/webapps/ROOT/WEB-INF/classes/portal-ext.properties
Loading file:/G:/liferay-portal-6.1.0-ce-ga1/portal-setup-wizard.properties
Loading file:/G:/liferay-portal-6.1.0-ce-ga1/portal-ide.properties
Loading file:/G:/liferay-portal-6.1.0-ce-ga1/tomcat-7.0.23/webapps/ROOT/WEB-INF/classes/portal-developer.properties
18:45:16,477 INFO [DialectDetector:80] Determine dialect for MySQL 5
18:45:16,512 INFO [DialectDetector:145] Found dialect org.hibernate.dialect.MySQLDialect
Starting Liferay Portal Community Edition 6.1.0 CE (Paton / Build 6100 / January 6, 2012)
18:45:51,291 ERROR [JDBCExceptionReporter:75] Table 'lportal.lock_' doesn't exist
18:45:51,869 WARN [ReleaseLocalServiceImpl:140] Table 'lportal.release_' doesn't exist
18:45:51,870 INFO [ReleaseLocalServiceImpl:84] Create tables and populate with default data
18:49:45,402 WARN [BaseDB:382] Specified key was too long; max key length is 767 bytes: create index IX_7020130F on SCProductVersion (directDownloadURL);

18:49:45,404 ERROR [BaseDB:389] create index IX_7020130F on SCProductVersion (directDownloadURL);
18:50:21,583 ERROR [BaseDB:389] create index IX_89509087 on User_ (companyId, openId);
18:50:41,620 INFO [BaseDB:409] Database does not support case sensitive queries
18:50:47,493 INFO [VerifyProcess:76] Verifying com.liferay.portal.verify.VerifyProcessSuite
18:50:47,921 INFO [VerifyProcess:76] Verifying com.liferay.portal.verify.VerifyProperties
18:50:47,931 INFO [VerifyProcess:76] Verifying com.liferay.portal.verify.VerifyMySQL

(And all table are verified and then)

com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'OPTION SQL_SELECT_LIMIT=DEFAULT' at line 1
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:3250)
at com.mysql.jdbc.Connection.unsetMaxRows(Connection.java:5820)
at com.mysql.jdbc.Statement.realClose(Statement.java:2023)
at com.mysql.jdbc.PreparedStatement.realClose(PreparedStatement.java:2185)
at com.mysql.jdbc.PreparedStatement.close(PreparedStatement.java:687)
at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.close(NewProxyPreparedStatement.java:1811)
at org.quartz.impl.jdbcjobstore.StdJDBCDelegate.closeStatement(StdJDBCDelegate.java:3241)
at org.quartz.impl.jdbcjobstore.StdJDBCDelegate.selectTriggerToAcquire(StdJDBCDelegate.java:2581)
at org.quartz.impl.jdbcjobstore.JobStoreSupport.acquireNextTrigger(JobStoreSupport.java:2778)
at org.quartz.impl.jdbcjobstore.JobStoreSupport$41.execute(JobStoreSupport.java:2752)
at org.quartz.impl.jdbcjobstore.JobStoreSupport.executeInNonManagedTXLock(JobStoreSupport.java:3807)
at org.quartz.impl.jdbcjobstore.JobStoreSupport.acquireNextTriggers(JobStoreSupport.java:2748)
at org.quartz.core.QuartzSchedulerThread.run(QuartzSchedulerThread.java:264)
18:51:22,298 ERROR [ErrorLogger:109] An error occurred while scanning for the next triggers to fire.
org.quartz.JobPersistenceException: Couldn't acquire next trigger: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'OPTION SQL_SELECT_LIMIT=1' at line 1 [See nested exception: com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'OPTION SQL_SELECT_LIMIT=1' at line 1]
at org.quartz.impl.jdbcjobstore.JobStoreSupport.acquireNextTrigger(JobStoreSupport.java:2837)
at org.quartz.impl.jdbcjobstore.JobStoreSupport$41.execute(JobStoreSupport.java:2752)
at org.quartz.impl.jdbcjobstore.JobStoreSupport.executeInNonManagedTXLock(JobStoreSupport.java:3807)
at org.quartz.impl.jdbcjobstore.JobStoreSupport.acquireNextTriggers(JobStoreSupport.java:2748)
at org.quartz.core.QuartzSchedulerThread.run(QuartzSchedulerThread.java:264)

And for all the portlets,below error registering portlets message is coming

Loading file:/G:/liferay-portal-6.1.0-ce-ga1/tomcat-7.0.23/webapps/wsrp-portlet/WEB-INF/classes/portlet.properties
Loading file:/G:/liferay-portal-6.1.0-ce-ga1/tomcat-7.0.23/webapps/wsrp-portlet/WEB-INF/classes/portlet.properties
18:55:33,912 ERROR [JDBCExceptionReporter:75] You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'OPTION SQL_SELECT_LIMIT=DEFAULT' at line 1
18:55:33,913 ERROR [HotDeployUtil:114] com.liferay.portal.kernel.deploy.hot.HotDeployException: Error registering portlets for wsrp-portlet

Tables are created but the home page is not opening in the browser.

Can anybody get me out of this..

[10年前 に Rob Silver によって更新されました。]

I have been told this is a MAVEN support issue, however, I have not tried it with Life Ray (LR) 6.2
Also I have not yet tried the latest version of MariaDB as well. Has anyone had any experiences with the latest MariaDB 10.0.8 enterprise version? Last time I tried I ran Mariadb as if it were a MySQL database but rant into problems due to some mistakes made in executing a regular MySQL database server on the same box and someone accidently started the MySQL server on the Mariadb datadir. Not good.
I do not know what if any support exists for MariaDB as well.
Sadly this does not sound good it has been a while since this problem with MySQL 5.6 happened - I would have hoped for a better answer byy now!!!

[10年前 に Sharath Babu A によって更新されました。]

At last i have started the server successfully.

I have replaced the 'mysql.jar' file of tomcat\lib\ext with the new downloaded .jar file 'mysql-connector-java-5.1.23.bundle.jar'.

And able to view the home page & Sign-In.

But while creating & populating the tables in the DB,few warnings came like,

WARN [BaseDB:382] Specified key was too long; max key length is 767 bytes: create index IX_89509087 on User_ (companyId, openId);
15:51:13,584 ERROR [BaseDB:389] create index IX_89509087 on User_ (companyId, openId);

Specified key was too long; max key length is 767 bytes: create index IX_2A17A23F on Chat_Entry (fromUserId, toUserId, content);

[9年前 に Marcel Brueckner によって更新されました。]

The solutions described above are not entirely reflecting the actual issue(s) here:

Basically the update from MySQL 5.5.x to 5.6.x change some defaults and therefore it "looks" like Liferay has some compatibility issues.

The steps you need to execute are as follows:
1) Make sure your mysql-connector is up to date (just as described earlier, copy a new connector into your /tomcat/lib/ext directory
2) Drop your old database, or migrate it with the mysql toolset (see link further down below)
3) Make sure your _new_ database is using the right storage engine (it should be InnoDB, see links for further details)

I migrated several production systems by now and they all lived happy ever after :-)

MySQL Migration Steps:
MySQL migration information

MySQL Issue related to the above stated errors Liferay spits out on index creation:
For reference see this links that basically has some in-depth information:
MySQL issue description

[9年前 に Fuad Efendi によって更新されました。]

Hi Marcel,

I am wondering how would you fix that:

20:50:12,146 WARN  [localhost-startStop-1][BaseDB:458] Specified key was too long; max key length is 3072 bytes: create index IX_89509087 on User_ (companyId, openId);_ [Sanitized]
20:50:12,147 ERROR [localhost-startStop-1][BaseDB:465] create index IX_89509087 on User_ (companyId, openId);

It happens right now with Liferay 7 CE M3.

The problem is that single "utf8" character can take from 1 byte to 4 bytes, and MySQL estimates 4000 bytes for "openId" field (defined as VARCHAR(1000)).

Do you know solution for this?

Thanks!


P.S.
Perhaps, right solution is to change some Liferay core staff... to use MD5 hash and HashMap for lookups of OpenID (which is URI, and which length is inspecified - can be higher that 1000; and definitely it is not UTF8)
As a workaround (depends on application specifics) we can quickly set "openId" to VARCHAR(700) and create index manually (if we use this feature)

[9年前 に David H Nebinger によって更新されました。]

Fuad Efendi:

It happens right now with Liferay 7 CE M3.

Any issues with the LR7 milestones should be opened as JIRA tickets, not forum comments. Liferay is building towards LR7 and need bug reports in order to find and resolve issues.

That being said, older versions of Liferay shipped with older versions of the mysql jdbc jar. I'd check to be sure you're using the latest one. Might not be the issue, but it's a place to start.

[9年前 に Fuad Efendi によって更新されました。]

This is not LR 7 CE 3 specific.

It also happens with ALL previous versions (including 6.2.1, 6.2.0, 6.1.x, including even commercial EE versions) ,where Liferay introduced VARCHAR(1024) for User_.openId field (and index(companyId, openId)), which is estimated to be 4096 bytes by MySQL. I am going to open JIRA ticket for that.

I found this Forum post via Google, and I responded to it asking questions from people more experienced than me; at that moment I was still looking for right answers.

Simplest solution for DBA (in case if OpenID usage expected) is to alter "user_" table with "openId" field defined in ASCII instead of UTF8 (but I'd triple-check Open ID specs). Without index... full-table scan.

Another solution: custom rebuilt MySQL; yes, it is possible to alter some preconfigured values, and rebuild it.

P.S. Yes, I tried mysql-connector-java-5.1.34-bin.jar with both, 6.2 CE GA2, and 7.0 CE M3.

P.P.S. Apache open source projects advice at first ask about in DEV mailing lists - perhaps someone is working on similar issue already - and only after confirmation (or its' absence) submit JIRA issue.


Thanks,

[9年前 に Fuad Efendi によって更新されました。]

I forgot to add:

1. Some versions of MySQL used to silently WARN users and automatically reduce index size, see MySQL Bug 68453
2. Of course, DBA can issue "create index IX_89509087 on User_ (companyId, openId(200));" (second column truncated to 200 characters) and it will work & it will improve performance
3. I have error reported "3072 bytes" instead of typical "767 bytes" max size, because I am using "innodb_file_format = Barracuda"
and "innodb_large_prefix = true". It will give for InnoDB 3500 bytes of total index size (for all columns); but MySQL (on top of InnoDB ) restricts it to 3072 bytes. See MySQL InnoDB Restrictions
4. VARCHAR(1024) in MySQL is 1024*3 + 2 + 1 bytes = 3075 bytes if encoding is utf8.

I believe this is not a bug in Liferay; it cannot be reported as a bug. Also, MySQL driver version does not play any role here. Thanks,

[9年前 に Neeraj Gautam によって更新されました。]

The issue is with MySQL 5.6.x version with any liferay version i.e from Liferay 6.1.1 GA2 to Liferay 6.2.2 GA3. Also, this issue is resolvable by using any of the MySQL 5.5.x version but hopefully MySQL resolve this issue with 5.7 version.

MySQL Bug id:- http://bugs.mysql.com/bug.php?id=68453

[8年前 に Rob Silver によって更新されました。]

Note under Linux I got the ga5 bundle to work but it was with Linux and not windows 7
Server: MariaDB
Server version: 5.5.44-MariaDB MariaDB Server
Java 1.8.0 update 66 - Oracle Java

Not Oracle CE MySql. Maybe Windows 7 was the problem. It never was with 6.11gal2 and a MySQL 5.5

[8年前 に Rob Silver によって更新されました。]

That may be:
http://www.liferaysavvy.com/2013/12/liferay-62-portal-installation_11.html
suggests going back to MySQL 5.1 java 1.6 and that seems silly
I checked: mysql-connector-java-5.1.23-bin.jar the MySQL.jar file in bundle 6.2ga5 is the latest and greatest MySQL connector

So what do I update the jar file to?