[LPS-33259] key too long for mysql database - Liferay Issues

Details

Type: Bug
Status: Closed
Resolution: Duplicate
Affects Version/s: 6.1.1 CE GA2, 6.2.0 CE M4
Fix Version/s: 6.1.1 CE GA2, 6.2.0 CE M4, 6.2.0 CE RC5
Component/s: Legacy Components, Legacy Components > Administration UI
Labels:
- community-verifier
Environment:
Windows 7 + MySQL 5.5+

Fix Priority:
3

Description

I am installing liferay-portal-tomcat-6.1.1-ce-ga2-20120731132656558. This is a clean install on a windows 7 machine (no previous version has been installed on the machine). I'm using MySql (mysql-installer-community-5.6.10.1). This was also a fresh install of MySql. to get it to semi-work, I had to replace the mysql.jar (mysql connector jar) with mysql-connector-java-5.1.23-bin.jar. However, I still see some troubling error messages. In particular, I'm concerned about the "Specified key was too long" messages. I'm assuming the 'lportal.lock_' message is just a result of it being the first time everything was started and I don't need to worry about that.

00:48:59,429 INFO [pool-2-thread-1][DialectDetector:71] Determine dialect for MySQL 5
00:48:59,458 INFO [pool-2-thread-1][DialectDetector:136] Found dialect org.hibernate.dialect.MySQLDialect
00:49:56,195 ERROR [pool-2-thread-1][JDBCExceptionReporter:76] Table 'lportal.lock_' doesn't exist
00:49:56,699 WARN [pool-2-thread-1][ReleaseLocalServiceImpl:151] Table 'lportal.release_' doesn't exist
00:49:56,700 INFO [pool-2-thread-1][ReleaseLocalServiceImpl:84] Create tables and populate with default data
01:00:22,390 WARN [pool-2-thread-1][BaseDB:428] Specified key was too long; max key length is 767 bytes: create index IX_7020130F on SCProductVersion (directDownloadURL);

01:00:22,391 ERROR [pool-2-thread-1][BaseDB:435] create index IX_7020130F on SCProductVersion (directDownloadURL);
01:01:47,407 WARN [pool-2-thread-1][BaseDB:428] Specified key was too long; max key length is 767 bytes: create index IX_89509087 on User_ (companyId, openId);

01:01:47,408 ERROR [pool-2-thread-1][BaseDB:435] create index IX_89509087 on User_ (companyId, openId);
01:02:57,745 INFO [pool-2-thread-1][BaseDB:455] Database does not support case sensitive queries
01:03:03,216 INFO [pool-2-thread-1][VerifyProcess:81] Verifying com.liferay.portal.verify.VerifyProcessSuite
01:03:03,217 INFO [pool-2-thread-1][VerifyProcess:81] Verifying com.liferay.portal.verify.VerifyProperties
01:03:03,227 INFO [pool-2-thread-1][VerifyProcess:81] Verifying com.liferay.portal.verify.VerifyDB2
01:03:03,228 INFO [pool-2-thread-1][VerifyProcess:81] Verifying com.liferay.portal.verify.VerifyMySQL
01:03:03,270 INFO [pool-2-thread-1][VerifyProcess:81] Verifying com.liferay.portal.verify.VerifyOracle
01:03:03,271 INFO [pool-2-thread-1][VerifyProcess:81] Verifying com.liferay.portal.verify.VerifySQLServer
01:03:03,272 INFO [pool-2-thread-1][VerifyProcess:81] Verifying com.liferay.portal.verify.VerifyCounter
01:03:03,402 INFO [pool-2-thread-1][VerifyProcess:81] Verifying com.liferay.portal.verify.VerifyUUID
01:03:03,408 INFO [pool-2-thread-1][VerifyProcess:81] Verifying com.liferay.portal.verify.VerifyPermission
01:03:06,324 INFO [pool-2-thread-1][VerifyProcess:81] Verifying com.liferay.portal.verify.VerifyRole
01:03:06,325 INFO [pool-2-thread-1][VerifyProcess:81] Verifying com.liferay.portal.verify.VerifyAsset
01:03:06,329 INFO [pool-2-thread-1][UpgradeProcess:207] Upgrading com.liferay.portal.upgrade.util.UpgradeAssetPublisherManualEntries
01:03:06,330 INFO [pool-2-thread-1][VerifyProcess:81] Verifying com.liferay.portal.verify.VerifyBlogs
01:03:06,362 INFO [pool-2-thread-1][VerifyProcess:81] Verifying com.liferay.portal.verify.VerifyBookmarks
01:03:06,365 INFO [pool-2-thread-1][VerifyProcess:81] Verifying com.liferay.portal.verify.VerifyCalendar
01:03:06,702 INFO [pool-2-thread-1][VerifyProcess:81] Verifying com.liferay.portal.verify.VerifyDocumentLibrary
01:03:06,762 INFO [pool-2-thread-1][VerifyProcess:81] Verifying com.liferay.portal.verify.VerifyGroup
01:03:06,777 INFO [pool-2-thread-1][VerifyProcess:81] Verifying com.liferay.portal.verify.VerifyJournal
01:03:06,795 INFO [pool-2-thread-1][VerifyProcess:81] Verifying com.liferay.portal.verify.VerifyLayout
01:03:06,808 INFO [pool-2-thread-1][VerifyProcess:81] Verifying com.liferay.portal.verify.VerifyMessageBoards
01:03:06,824 INFO [pool-2-thread-1][VerifyProcess:81] Verifying com.liferay.portal.verify.VerifyOrganization
01:03:06,825 INFO [pool-2-thread-1][VerifyProcess:81] Verifying com.liferay.portal.verify.VerifyResourcePermissions
01:03:06,826 INFO [pool-2-thread-1][VerifyProcess:81] Verifying com.liferay.portal.verify.VerifySocial
01:03:06,827 INFO [pool-2-thread-1][VerifyProcess:81] Verifying com.liferay.portal.verify.VerifyUser
01:03:06,835 INFO [pool-2-thread-1][VerifyProcess:81] Verifying com.liferay.portal.verify.VerifyWiki
01:03:06,922 INFO [pool-2-thread-1][ServerDetector:154] Server supports hot deploy
01:03:06,928 INFO [pool-2-thread-1][PluginPackageUtil:1030] Reading plugin package for the root context
01:03:18,301 INFO [pool-2-thread-1][AutoDeployDir:85] Creating missing directory C:\liferay\bundles\deploy
01:03:18,303 INFO [pool-2-thread-1][AutoDeployDir:106] Auto deploy scanner started for C:\liferay\bundles\deploy

The only thing I've changed was to add the portal-ext.properties file in to define the mysql instance.

Issue Links

is duplicated by

LPS-34881 key too long for mysql database

Closed

is fixed by

LPS-58589 Fix ServiceBuilder MySQL 5.6 indexes generation compatibility issue

Closed

Activity

Ascending order - Click to sort in descending order

Hide

Permalink

Aniceto P Madrid (Inactive) added a comment - 23/Feb/13 8:47 AM

The magic 767 number appears in mysql documentation http://dev.mysql.com/doc/refman/5.1/en/create-index.html
This is also a reference to that problem http://stackoverflow.com/questions/1814532/1071-specified-key-was-too-long-max-key-length-is-767-bytes
Why mysql didn't report that error until 5.6.10 I don't know. It seems it's related with a compilation parameter.
As of mysql 5.6.10 version, Liferay doesn't work on mysql

Show

Aniceto P Madrid (Inactive) added a comment - 23/Feb/13 8:47 AM The magic 767 number appears in mysql documentation http://dev.mysql.com/doc/refman/5.1/en/create-index.html This is also a reference to that problem http://stackoverflow.com/questions/1814532/1071-specified-key-was-too-long-max-key-length-is-767-bytes Why mysql didn't report that error until 5.6.10 I don't know. It seems it's related with a compilation parameter. As of mysql 5.6.10 version, Liferay doesn't work on mysql

Hide

Permalink

Aniceto P Madrid (Inactive) added a comment - 23/Feb/13 8:49 AM

At least on Windows 7, and Windows 7x64

Show

Aniceto P Madrid (Inactive) added a comment - 23/Feb/13 8:49 AM At least on Windows 7, and Windows 7x64

Hide

Permalink

Philippe added a comment - 19/Mar/13 4:35 AM

mysql 5.6.10 indexes are limited to 256 utf8 chars, or 1024 with barracuda file format.
You may consider :

(1).add to my.cnf (to use the upper limit):
innodb_file_per_table = 1
innodb_file_format = Barracuda
innodb_large_prefix = 1

(2).use create-minimal-mysql.sql DB script with following changes:
create table SCProductVersion (
...
) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
create index IX_7020130F on SCProductVersion (directDownloadURL(1024));
create table User_ (
...
) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
create index IX_89509087 on User_ (companyId, openId(1021));

Show

Philippe added a comment - 19/Mar/13 4:35 AM mysql 5.6.10 indexes are limited to 256 utf8 chars, or 1024 with barracuda file format. You may consider : (1).add to my.cnf (to use the upper limit): innodb_file_per_table = 1 innodb_file_format = Barracuda innodb_large_prefix = 1 (2).use create-minimal-mysql.sql DB script with following changes: create table SCProductVersion ( ... ) ENGINE=InnoDB ROW_FORMAT=DYNAMIC; create index IX_7020130F on SCProductVersion (directDownloadURL(1024)); create table User_ ( ... ) ENGINE=InnoDB ROW_FORMAT=DYNAMIC; create index IX_89509087 on User_ (companyId, openId(1021));

Hide

Permalink

Philippe added a comment - 19/Mar/13 5:48 AM

Using mysql 5.6.10, you may consider adding to my.cnf:
binlog_format = row

Or you won't be able to start Liferay with UncategorizedSQLException: Hibernate flushing: Could not execute JDBC batch update.

Show

Philippe added a comment - 19/Mar/13 5:48 AM Using mysql 5.6.10, you may consider adding to my.cnf: binlog_format = row Or you won't be able to start Liferay with UncategorizedSQLException: Hibernate flushing: Could not execute JDBC batch update.

Hide

Permalink

Aniceto P Madrid (Inactive) added a comment - 21/Mar/13 5:23 AM - edited

As you see this has been Selected for Development, and has been assigned a Business Value of 4. I'm sure the development team will thank any help.
Thanks Phillipe.

But.... maybe YOU are part of the development team

Show

Aniceto P Madrid (Inactive) added a comment - 21/Mar/13 5:23 AM - edited As you see this has been Selected for Development, and has been assigned a Business Value of 4. I'm sure the development team will thank any help. Thanks Phillipe. But.... maybe YOU are part of the development team

Hide

Permalink

Miguel Ángel Pastor Olivar added a comment - 25/Jul/13 5:49 AM - edited

The warning message is not a problem (the link will get created anyway but using a length of 255 (lengths are measured in num of elements, not in bytes))

show index from scproductversion;

will show you something like this

 scproductversion |          0 | PRIMARY     |            1 | productVersionId  | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| scproductversion |          1 | IX_7020130F |            1 | directDownloadURL | A         |           0 |      255 | NULL   | YES  | BTREE      |         |               |
| scproductversion |          1 | IX_8377A211 |            1 | productEntryId    | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |

You can create an index by your own and check the results

create index IX_MyTest on SCProductVersion (directDownloadURL);

Thx!

Show

Miguel Ángel Pastor Olivar added a comment - 25/Jul/13 5:49 AM - edited The warning message is not a problem (the link will get created anyway but using a length of 255 (lengths are measured in num of elements, not in bytes)) show index from scproductversion; will show you something like this scproductversion | 0 | PRIMARY | 1 | productVersionId | A | 0 | NULL | NULL | | BTREE | | | | scproductversion | 1 | IX_7020130F | 1 | directDownloadURL | A | 0 | 255 | NULL | YES | BTREE | | | | scproductversion | 1 | IX_8377A211 | 1 | productEntryId | A | 0 | NULL | NULL | YES | BTREE | | | You can create an index by your own and check the results create index IX_MyTest on SCProductVersion (directDownloadURL); Thx!

Hide

Permalink

JR Houn added a comment - 22/Oct/13 11:56 AM

Hi Guys -

I'm reopening this issue because while the portal will start with the WARN (as previously mentioned) - if a user tries to run the "ant rebuild-mysql" command from the /sql/ folder - the process will break on >= MySQL 5.6. This will cause disruptions for the QA group and other groups that might use that command.

liferay@LIFERAY-JHOUN /d/projects/liferay/portal/master/sql (master)
$ ant rebuild-mysql
Buildfile: d:\projects\liferay\portal\master\sql\build.xml

rebuild-mysql:
     [exec] Result: 1

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

Total time: 2 minutes 31 seconds

Show

JR Houn added a comment - 22/Oct/13 11:56 AM Hi Guys - I'm reopening this issue because while the portal will start with the WARN (as previously mentioned) - if a user tries to run the "ant rebuild-mysql" command from the /sql/ folder - the process will break on >= MySQL 5.6. This will cause disruptions for the QA group and other groups that might use that command. liferay@LIFERAY-JHOUN /d/projects/liferay/portal/master/sql (master) $ ant rebuild-mysql Buildfile: d:\projects\liferay\portal\master\sql\build.xml rebuild-mysql: [exec] Result: 1 BUILD FAILED ERROR 1071 (42000) at line 4034: Specified key was too long ; max key length is 767 bytes Total time: 2 minutes 31 seconds

Hide

Permalink

Tamas Molnar added a comment - 12/Jun/14 2:37 AM

This issue is reproducible using mysl version >= 5.6.

According to http://dev.mysql.com/doc/refman/5.6/en/innodb-restrictions.html, the length should be silently reduced to the maximum length. However, if version >= 5.6 this doesn't happen.

This bug is already reported:
http://bugs.mysql.com/bug.php?id=68453
http://bugs.mysql.com/bug.php?id=71613

Possible workaround could be to create indexes that use only the leading part of column values.
See http://dev.mysql.com/doc/refman/5.6/en/create-index.html.

create index IX_7020130F on SCProductVersion (directDownloadURL(255));
create index IX_89509087 on User_ (companyId, openId(255));

Setting the limit to 255 solves the issue. This solution would result the same behavior as if the lenght was reduced silently. This way we can workaround the bug at the mysql side.

Show

Tamas Molnar added a comment - 12/Jun/14 2:37 AM This issue is reproducible using mysl version >= 5.6. According to http://dev.mysql.com/doc/refman/5.6/en/innodb-restrictions.html , the length should be silently reduced to the maximum length. However, if version >= 5.6 this doesn't happen. This bug is already reported: http://bugs.mysql.com/bug.php?id=68453 http://bugs.mysql.com/bug.php?id=71613 Possible workaround could be to create indexes that use only the leading part of column values. See http://dev.mysql.com/doc/refman/5.6/en/create-index.html . create index IX_7020130F on SCProductVersion (directDownloadURL(255)); create index IX_89509087 on User_ (companyId, openId(255)); Setting the limit to 255 solves the issue. This solution would result the same behavior as if the lenght was reduced silently. This way we can workaround the bug at the mysql side.

Hide

Permalink

Mika Koivisto added a comment - 22/Jun/16 1:20 AM

This issue seems to be fixed by ~~LPS-58589~~

Show

Mika Koivisto added a comment - 22/Jun/16 1:20 AM This issue seems to be fixed by LPS-58589

People

Assignee:

Miguel Ángel Pastor Olivar

Reporter:

Scott Smith (Inactive)

Recent user:

Michael Saechang

Participants of an Issue:

Aniceto P Madrid, JR Houn, Miguel Ángel Pastor Olivar, Mika Koivisto, Philippe, Scott Smith, Tamas Molnar

Votes:

3 Vote for this issue

Watchers:

10 Start watching this issue

Dates

Created:

22/Feb/13 5:26 PM

Updated:

19/Aug/16 4:02 PM

Resolved:

22/Jun/16 1:20 AM

Days since last comment:

13 weeks, 5 days ago

Development

Agile

View on Board

Subcomponents