MySQLSyntaxErrorException: Unknown column '' in 'field list'

• Type: Bug
• Status: Closed
• Priority: Minor
• Resolution: Duplicate
• Affects Version/s: 6.0.5 GA, 6.0.6 GA
• Fix Version/s: None
• Component/s: Infrastructure, Infrastructure > DBs
• Labels: None
• Environment: Windows XP Professional ; Glassfish v3 ; Sun JDK 1.6.0_21 ; MySQL Server 5.1.48 @ Red Hat Enterprise Linux 5.5 ; Liferay 6.0.5 GA (manually deploy on Glassfish)
• Similar Issues: Show 5 results ; ;

  LPS-4699	UpgradeSocial - java.sql.BatchUpdateException: Unknown column 'ype_' in 'field list'
  LPS-14583	Sharded database throws "Unknown column 'Role_.title' in 'field list'" exception
  LPS-34198	Upgrade to 6.2.x fails because there is an unknown column 'Layout.hidden" in 'field list'
  LPS-26896	Upgrading from 5.2.8 to 6.2.x fails causing Unknown column 'journalart0_.folderId' in 'field list'
  LPS-32928	Cannot upgrade from 5.2.3, 6.0.6, and 6.1.1 to Trunk - "Unknown column 'Layout.hidden' in 'field list'"

After

-) setting up the database connection on Glassfish
-) creating the file portal-ext.properties \

portal-ext.properties

jdbc.default.jndi.name=jdbc/LiferayPool
transaction.isolation.portal=4

-) and dropping dropping portlet.jar, portal-service.jar & mysql-connector-5.1.13.jar into glassfish's domains\domain1\lib directory

I'll get the following exception during auto-deployment:

[#|2010-08-24T08:09:57.399+0200|INFO|glassfish3.0.1|javax.enterprise.system.std.com.sun.enterprise.v3.services.impl|_ThreadID=25;_ThreadName=Thread-1;|08:09:57,399 ERROR [SchedulerMessageListener:53] Unable to process message {destinationName=liferay/scheduler_engine, responseDestinationName=null, responseId=null, payload=com.liferay.portal.kernel.scheduler.messaging.SchedulerRequest@cbb4da, values=null}
com.liferay.portal.kernel.scheduler.SchedulerException: Unable to start scheduler
	at com.liferay.portal.scheduler.quartz.QuartzSchedulerEngineImpl.start(QuartzSchedulerEngineImpl.java:276)
	at com.liferay.portal.scheduler.messaging.SchedulerMessageListener.doReceive(SchedulerMessageListener.java:85)
	at com.liferay.portal.scheduler.messaging.SchedulerMessageListener.receive(SchedulerMessageListener.java:50)
	at com.liferay.portal.kernel.messaging.InvokerMessageListener.receive(InvokerMessageListener.java:63)
	at com.liferay.portal.kernel.messaging.ParallelDestination$1.run(ParallelDestination.java:61)
	at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908)
	at java.lang.Thread.run(Thread.java:619)
Caused by: org.quartz.SchedulerConfigException: Failure occured during job recovery. [See nested exception: org.quartz.JobPersistenceException: Couldn't retrieve trigger: Unknown column '' in 'field list' [See nested exception: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column '' in 'field list']]
	at org.quartz.impl.jdbcjobstore.JobStoreSupport.schedulerStarted(JobStoreSupport.java:623)
	at org.quartz.core.QuartzScheduler.start(QuartzScheduler.java:466)
	at org.quartz.impl.StdScheduler.start(StdScheduler.java:143)
	at com.liferay.portal.scheduler.quartz.QuartzSchedulerEngineImpl.start(QuartzSchedulerEngineImpl.java:273)
	... 7 more
Caused by: org.quartz.JobPersistenceException: Couldn't retrieve trigger: Unknown column '' in 'field list' [See nested exception: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column '' in 'field list']
	at org.quartz.impl.jdbcjobstore.JobStoreSupport.retrieveTrigger(JobStoreSupport.java:1567)
	at org.quartz.impl.jdbcjobstore.JobStoreSupport.recoverMisfiredJobs(JobStoreSupport.java:946)
	at org.quartz.impl.jdbcjobstore.JobStoreSupport.recoverJobs(JobStoreSupport.java:830)
	at org.quartz.impl.jdbcjobstore.JobStoreSupport$2.execute(JobStoreSupport.java:802)
	at org.quartz.impl.jdbcjobstore.JobStoreSupport$41.execute(JobStoreSupport.java:3708)
	at org.quartz.impl.jdbcjobstore.JobStoreSupport.executeInNonManagedTXLock(JobStoreSupport.java:3742)
	at org.quartz.impl.jdbcjobstore.JobStoreSupport.executeInNonManagedTXLock(JobStoreSupport.java:3704)
	at org.quartz.impl.jdbcjobstore.JobStoreSupport.recoverJobs(JobStoreSupport.java:798)
	at org.quartz.impl.jdbcjobstore.JobStoreSupport.schedulerStarted(JobStoreSupport.java:621)
	... 10 more
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column '' in 'field list'
	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
	at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
	at com.mysql.jdbc.Util.handleNewInstance(Util.java:409)
	at com.mysql.jdbc.Util.getInstance(Util.java:384)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1054)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3566)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3498)
	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1959)
	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2113)
	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2568)
	at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2113)
	at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:2275)
	at com.mysql.jdbc.BlobFromLocator.length(BlobFromLocator.java:338)
	at org.quartz.impl.jdbcjobstore.StdJDBCDelegate.getObjectFromBlob(StdJDBCDelegate.java:3463)
	at org.quartz.impl.jdbcjobstore.StdJDBCDelegate.selectTrigger(StdJDBCDelegate.java:2132)
	at org.quartz.impl.jdbcjobstore.JobStoreSupport.retrieveTrigger(JobStoreSupport.java:1549)
	... 18 more
|#]

It seems to be the same issue as LPS-12129 but is has been closed without providing a solution for the problem.

After some debugging I think the exception occurs when liferay is trying to read some information out of QUARTZ_TRIGGERS using a SELECT statement without aliases.

[Marek Wianecki]

As I commented in LPS-12129 in my case problem is emulateLocators=true in connection string (I did it for lucene cluster configuration)
for example
url="jdbc:mysql://ourhost.somedomain.pl:3306/liferay?emulateLocators=true&useUnicode=true&characterEncoding=UTF-8&useFastDateParsing=false"

[Gordon Augat]

I get the same error with the Liferay 6.0.5 / Tomcat bundle and MySQL 5.0.51 on Windows 7. I am using the mysql-connector-java-5.0.7-bin.jar JDBC driver. The error started to happen when I set emulateLocators=true on the connect string.

[Gordon Augat]

I just upgraded my mysql jdbc driver to mysql-connector-java-5.1.13-bin.jar and it seems to have fixed the problem. Still testing.

[Gordon Augat]

Upgrading the mysql jdbc driver did not work. Still getting the above exception.

[Hitoshi Ozawa]

This should be a major priority because it disallows database clustering.
It seems be a sql command syntax error.

[SP]

I agree with Hitoshi, the priority should be higher.

[Piotr Bubiński]

I had the same problem with posgresql. I download from http://jdbc.postgresql.org/download.html JDBC3 Postgresql Driver, Version 9.0-801, remove posgresql from liferay-portal-6.0.6/tomcat-6.0.29/lib/ext/ and it resolve problem. LPS-13281 similar issue.

[Sophia Zhang]

Hi Michael,Thank you for your report. I was able to reproduce this issue in Liferay Portal 6.0.5GA.I have also tested this issue on trunk revision 91680 and I was unable to reproduce. This issue should be fixed in the next version of Liferay Portal.
Actually we can use command to deploy current portal to Glassfish ,it's very convenient.Why don't you update your portal version?

[zzzz8]

I have a possible workaround for this issue. See the following forum post: http://www.liferay.com/community/forums/-/message_boards/message/24395763.