Duplicate index during update process

[Carlos Tilve, geändert vor 7 Jahren.]

Hi you all,

We are having an issue while upgrading a test server to liferay 7 from 6.1, in a windows x64 system, over a MySql database and using tomcat. Also, we are using the bundled version.

The process just stops with the following error.

com.liferay.portal.kernel.upgrade.UpgradeException: com.liferay.portal.kernel.upgrade.UpgradeException: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrity
ConstraintViolationException: Duplicate entry '10172-1-/manage-es_ES' for key 'IX_A6FC2B28'
        at com.liferay.portal.kernel.upgrade.UpgradeProcess.upgrade(UpgradeProcess.java:91)
        at com.liferay.portal.upgrade.Pre7UpgradeProcess.upgrade(Pre7UpgradeProcess.java:42)
        at com.liferay.portal.kernel.upgrade.util.UpgradeProcessUtil._upgradeProcess(UpgradeProcessUtil.java:175)
        at com.liferay.portal.kernel.upgrade.util.UpgradeProcessUtil.upgradeProcess(UpgradeProcessUtil.java:143)
        at com.liferay.portal.kernel.upgrade.util.UpgradeProcessUtil.upgradeProcess(UpgradeProcessUtil.java:125)
        at com.liferay.portal.events.StartupHelper.upgradeProcess(StartupHelper.java:164)
        at com.liferay.portal.events.StartupHelperUtil.upgradeProcess(StartupHelperUtil.java:81)
        at com.liferay.portal.tools.DBUpgrader.upgrade(DBUpgrader.java:157)
        at com.liferay.portal.tools.DBUpgrader.main(DBUpgrader.java:103)
Caused by: com.liferay.portal.kernel.upgrade.UpgradeException: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate e
ntry '10172-1-/manage-es_ES' for key 'IX_A6FC2B28'
        at com.liferay.portal.kernel.upgrade.UpgradeProcess.upgrade(UpgradeProcess.java:91)
        at com.liferay.portal.kernel.upgrade.UpgradeProcess.upgrade(UpgradeProcess.java:115)
        at com.liferay.portal.upgrade.UpgradeProcess_6_2_0.doUpgrade(UpgradeProcess_6_2_0.java:77)
        at com.liferay.portal.kernel.upgrade.UpgradeProcess.upgrade(UpgradeProcess.java:88)
        ... 8 more
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry '10172-1-/manage-es_ES' for key 'IX_A6FC2B28'
        at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
        at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
        at java.lang.reflect.Constructor.newInstance(Unknown Source)
        at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
        at com.mysql.jdbc.Util.getInstance(Util.java:386)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1040)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4120)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4052)
        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2503)
        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2664)
        at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2794)
        at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2155)
        at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2458)
        at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2375)
        at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2359)
        at com.liferay.portal.upgrade.v6_2_0.UpgradeLayoutFriendlyURL.addLayoutFriendlyURL(UpgradeLayoutFriendlyURL.java:89)
        at com.liferay.portal.upgrade.v6_2_0.UpgradeLayoutFriendlyURL.addLayoutFriendlyURL(UpgradeLayoutFriendlyURL.java:51)
        at com.liferay.portal.upgrade.v6_2_0.UpgradeLayoutFriendlyURL.doUpgrade(UpgradeLayoutFriendlyURL.java:95)
        at com.liferay.portal.kernel.upgrade.UpgradeProcess.upgrade(UpgradeProcess.java:88)
        ... 11 more

So, by using the index name, we were able to track the error to the layoutfriendlyurl table and performed a few queries to check the unicity of the implicated fields and values; it would seem, however, that the duplicated entry does not exist (or perhaps it's being added during the upgrade again by some reason).

By checking the log again i came across a few WARN stating that certain tables already existed and could not be created, but since the program didn't halt there, i assumed it's unrelated. I realized just now, the affected table is one of them.

12:23:43,421 WARN  [default-1][BaseDB:466] Table 'layoutfriendlyurl' already exists: create table LayoutFriendlyURL (_	uuid_ varchar(75) null,_	layoutFriendlyURLId bigint not null primary key,_	groupId bigint,_	companyId bigint,_	userId bigint,_	userName varchar(75) null,_	createDate datetime(6) null,_	modifiedDate datetime(6) null,_	plid bigint,_	privateLayout tinyint,_	friendlyURL varchar(255) null,_	languageId varchar(75) null_) engine InnoDB;_ [Sanitized]

Has anyone experienced a similar problem, or has anyone any insight on how to resolve this?

Just in case it weren't blindingly obvious, i'm absolutely new to this.

Any help would be appreciated.

Thanks in advance and kind regards!

[Alberto Chaparro, geändert vor 7 Jahren.]

Hi Carlos,

If you check where this exception occurs:
https://github.com/liferay/liferay-portal/blob/master/portal-impl/src/com/liferay/portal/upgrade/v6_2_0/UpgradeLayoutFriendlyURL.java#L89

This can happen for two reasons:
1- You have 2 layout records with the following data:
groupId: 10172
privateLayout: 1
FriendlyUrl: "/manage"
(but this shouldn't happen because the following index which existed in 6.1 too: create unique index IX_BC2C4231 on Layout (groupId, privateLayout, friendlyURL);

2- The upgrade process creates a record in the layoutfriendlyurl before executing UpgradeLayoutFriendlyURL

Please, take this into account and let us know about which of both possibilities is in your case.

Thanks.

[Carlos Tilve, geändert vor 7 Jahren.]

Hi Alberto, thanks for taking the time.

So, i've been looking through both your answer, the link you attached, and the records in our database to make sure we didn't have any duplicates (there wern't any). This makes think we are dealing with the second case.

There is one thing i find strange, however. The doUpgrade() method reads all the layout rows and then inserts them in the layoutfriendlyurl table without checking existence prior to insertion.
So this got me thinking, if i found this strange it means it's something way too obvious to skip, and can be no mistake. As i mentioned, we tried to upgrade to Liferay 6.2 previously and due to some failures (and deadlines) this was postponed. Can it be that this table should not exist in our production environment (version 6.1) but somehow does? (a partial or incomplete upgrade) As i recall, the database had been backuped and restored, but i am concerned that it wasn't done properly.

This would explain both the warnings and the duplicate insertions.

[Alberto Chaparro, geändert vor 7 Jahren.]

Hi Carlos,

That explains everything. The layoutfriendlyurl table didn't exist in 6.1 so probably you restore the backup in your previous upgrade but you didn't remove new created tables during your your previous attempt. You should remove then before executing the upgrade again, you can check the tables which are created during the upgrade to 6.2 here:
https://github.com/liferay/liferay-portal/blob/master/sql/update-6.1.1-6.2.0.sql

Please, do a backup before doing this just in case.

Regards!

[Carlos Tilve, geändert vor 7 Jahren.]

Hi again, Alberto.

You were right, i did as you said (backup included) and this time it worked just fine.

I have come across another problem which i thought unrelated (related to locals and country names, it would seem), but while looking at the update script it might not be so, so i guess i'm going to give it a more in-depth look before starting panicking again.

Thanks a lot!