Migrating Liferay 6.0.5 from MySQL to Oracle 11.1.0.7.0

[Rajat Srivastava，修改在10 年前。]

Hi All,
In one of our test environments we are using Liferay 6.0.5 bundled with Tomcat. Liferay was configured with MySQL as our portal database initially.
We later decided to go with Oracle 11 database for production roll out and that's when we decided to migrate test environment portal database from MySQL to Oracle too.

Post migration we found that Liferay user registrations started failing sporadically with Unique constraint violations unable to insert records.

It was found that there is a counter maintained in COUNTER_ table which is used as the ceiling limit on counter. It is incremented by a margin of 100 every time counter limit is reached.

Our counter value was reset to a lower limit when migrating from MySQL to Oracle and as the data was migrated too, there were several users with holding user ids as higher number than the counter. Whenever counter reached those numbers the user creation will fail with unique constraint violation.

As this was test environment we got rid off this issue by creating some 50 users in a batch call, which made the counter cross the user id with the highest number it reached in MySQL instance!

Discovered features prone to errors:
The counter is handled programatically it is not a database sequence.
During User creation Single counter is been used to insert 5 or 6 records in 3 different tables: CONTACT_, USER_ and LAYOUTSET.
During migration, there was no mechanism which could correctly calculate maximum ID number already taken and set COUNTER value to 1 higher than the maximum, (Or is there something in place to take care of this that we missed to enable?)

[David H Nebinger，修改在10 年前。]

Liferay is built to support many different databases, including those that do not support sequences such as oracle.

The sequence is incremented by 100 every time because it represents fewer updates to the database (99 fewer, in fact) for counter allocation. The 100 internal values are managed at runtime by the Liferay instance. At restart or consumption of the 100 values, the counter value is incremented by another 100. You can manage the amount of the increment in portal-ext.properties.

You are the reason your keys are now colliding as you incorrectly thought that you could manage the Liferay database and did not treat it like the black box it is meant to be treated as. You manipulated the counters on your own. You changed values you shouldn't have because you did not understand the ramifications of the change.

[Kevin Zhang，修改在9 年前。]

I am doing db migration for Liferay 6.0.5 from MySQL5.5.37 to my local Oracle XE 11g. I use "root" user to access MySQL lportal db. I kept getting the following error:
FATAL [ConvertProcessMessageListener:35] Unable to process message {destinationName=liferay/convert_process, responseDestinationName=null, responseId=null, payload=com.liferay.portal.convert.ConvertDatabase, values=null} com.liferay.portal.convert.ConvertException: com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: Table 'counter' already exists
at com.liferay.portal.convert.ConvertProcess.convert(ConvertProcess.java:53)
Then I changed the name of table 'counter' in the MySQL DB, then at the same spot instead of "table already exists" error, I'm getting "table not exists error":
FATAL [ConvertProcessMessageListener:35] Unable to process message {destinationName=liferay/convert_process, responseDestinationName=null, responseId=null, payload=com.liferay.portal.convert.ConvertDatabase, values=null} com.liferay.portal.convert.ConvertException: com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: Table 'lportal.counter' doesn't exist
at com.liferay.portal.convert.ConvertProcess.convert(ConvertProcess.java:53)

Seems to me it hasn't reached the Oracle DB yet, the problem seems internal to the MySQL. But I'm really not sure what I did wrong. The Liferay has been running on the MySQL DB without any problem.

[Kevin Zhang，修改在9 年前。]

I have figured out this problem by looking into the source code. In the liferay "Control Panel / Server Administration / Data Migration / Migrate data from one database to another", you are asked to enter the following information:
JDBC Driver Class Name:
JDBC URL:
JDBC User Name:
JDBC Password:
In the code (com.liferay.portal.dao.jdbc.util.DataSourceFactoryBean.createInstance()), it merges these values with the default ones, which is the DB I'm currently using, defined in the portal-ext.properties file as following:
jdbc.default.jndi.name=jdbc/LiferayPool
jdbc.default.driverClassName=com.mysql.jdbc.Driver
jdbc.default.url=jdbc:mysql://localhost/lportal?useUnicode=true&characterEncoding=UTF-8&useFastDateParsing=false
jdbc.default.username=my-username
jdbc.default.password=my-password
It's the first line (jndi.name) got me into trouble. Since the entered values don't include jndi name, after the merging, the resulted property would have the same jndi name. Then the code does a jndi search and find the default data source (the MySQL one). So rather than pointing to the target DB, I'm pointing back to the source DB.
After commenting out the first line about JNDI name, I was able to go through the DB Migration process successfully.