PUBLIC - Liferay Portal Community Edition

key too long for mysql database

Details

  • Fix Priority:
    3
  • Similar Issues:
    Show 5 results 

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

Bug - A problem which impairs or prevents the functions of the product. LPS-34881 key too long for mysql database

  • Closed - The issue is considered finished, the resolution is correct. Issues which are not closed can be reopened.

Activity

Ascending order - Click to sort in descending order
Hide
Permalink
Aniceto P Madrid added a comment -

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 added a comment - 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 added a comment -

At least on Windows 7, and Windows 7x64

Show
Aniceto P Madrid added a comment - At least on Windows 7, and Windows 7x64
Hide
Permalink
Philippe added a comment -

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 - 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 -

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 - 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 added a comment - - 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 added a comment - - 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 Pastor added a comment - - 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 Pastor added a comment - - 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 -

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 - 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

People

Vote (1)
Watch (7)

Dates

  • Created:
    Updated:
    Days since last comment:
    25 weeks, 4 days ago