SQLGrammarException MySQLSyntaxErrorException

[Ranendra Das, modificado 12 Anos atrás.]

Hello,

I am getting an exception which I did nto encountred previously, the exception details are below.

I went throught the code base(6.0.6) CE:-

DLFileVersionPersistenceImpl.findByG_F_N() - this is where the problem is. Ultimately I looked at the SQL, if I removed the where clause, SQL looks like this
"SELECT * FROM DLFileVersion".

Now here is my confusions, my database name is "tfintranetdb" and under tfintranetdb db - there are 2 tables with same name - DLFileVersion(this does not have some columns which are in dlfieversion table) and dlfileversion. Why there are 2 same name tables? And following sql returns different results:-

SELECT * FROM `tfintranetdb`.`DLFileVersion`
SELECT * FROM `tfintranetdb`.`dlfileversion`
SELECT * FROM DLFileVersion

2011/08/02 18:13:45 WARN
com.liferay.portal.util.PortalImpl.sendError(PortalImpl.java:3848) [][http-8080-25] 
com.liferay.portal.kernel.exception.SystemException: com.liferay.portal.kernel.dao.orm.ORMException: org.hibernate.exception.SQLGrammarException: could not execute query
com.liferay.portal.kernel.exception.SystemException: com.liferay.portal.kernel.dao.orm.ORMException: org.hibernate.exception.SQLGrammarException: could not execute query
	at com.liferay.portal.service.persistence.impl.BasePersistenceImpl.processException(BasePersistenceImpl.java:195)
	at com.liferay.portlet.documentlibrary.service.persistence.DLFileVersionPersistenceImpl.findByG_F_N(DLFileVersionPersistenceImpl.java:623)
	at com.liferay.portlet.documentlibrary.service.impl.DLFileVersionLocalServiceImpl.getLatestFileVersion(DLFileVersionLocalServiceImpl.java:67)
	at sun.reflect.GeneratedMethodAccessor646.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
	at java.lang.reflect.Method.invoke(Method.java:597)
	at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:309)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:183)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150)
	at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:110)
	at com.liferay.portal.dao.jdbc.aop.DynamicDataSourceTransactionInterceptor.invoke(DynamicDataSourceTransactionInterceptor.java:44)
	at com.liferay.portal.spring.aop.ChainableMethodAdvice.invoke(ChainableMethodAdvice.java:58)
	at com.liferay.portal.spring.aop.ChainableMethodAdvice.invoke(ChainableMethodAdvice.java:58)
	at com.liferay.portal.spring.aop.ChainableMethodAdvice.invoke(ChainableMethodAdvice.java:58)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
	at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:89)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
	at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:202)
	at $Proxy146.getLatestFileVersion(Unknown Source)
	at com.liferay.portlet.documentlibrary.service.DLFileVersionLocalServiceUtil.getLatestFileVersion(DLFileVersionLocalServiceUtil.java:254)
	at com.liferay.portlet.documentlibrary.service.permission.DLFileEntryPermission.contains(DLFileEntryPermission.java:85)
	at com.liferay.portlet.documentlibrary.service.permission.DLFileEntryPermission.check(DLFileEntryPermission.java:45)
	at com.liferay.portlet.documentlibrary.service.impl.DLFileEntryServiceImpl.getFileEntryByTitle(DLFileEntryServiceImpl.java:196)
	at sun.reflect.GeneratedMethodAccessor644.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
	at java.lang.reflect.Method.invoke(Method.java:597)
	at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:309)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:183)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150)
	at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:110)
	at com.liferay.portal.dao.jdbc.aop.DynamicDataSourceTransactionInterceptor.invoke(DynamicDataSourceTransactionInterceptor.java:44)
	at com.liferay.portal.spring.aop.ChainableMethodAdvice.invoke(ChainableMethodAdvice.java:58)
	at com.liferay.portal.spring.aop.ChainableMethodAdvice.invoke(ChainableMethodAdvice.java:58)
	at com.liferay.portal.spring.aop.ChainableMethodAdvice.invoke(ChainableMethodAdvice.java:58)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
	at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:89)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
	at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:202)
	at $Proxy142.getFileEntryByTitle(Unknown Source)
	at com.liferay.portlet.documentlibrary.service.DLFileEntryServiceUtil.getFileEntryByTitle(DLFileEntryServiceUtil.java:124)
	at com.liferay.portal.webserver.WebServerServlet.sendFile(WebServerServlet.java:274)
	at com.liferay.portal.webserver.WebServerServlet.service(WebServerServlet.java:113)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
	at com.liferay.portal.kernel.servlet.BaseFilter.processFilter(BaseFilter.java:196)
	at com.liferay.portal.servlet.filters.autologin.AutoLoginFilter.processFilter(AutoLoginFilter.java:254)
	at com.liferay.portal.kernel.servlet.BaseFilter.doFilter(BaseFilter.java:123)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
	at com.liferay.portal.kernel.servlet.BaseFilter.processFilter(BaseFilter.java:196)
	at com.liferay.portal.servlet.filters.sso.ntlm.NtlmPostFilter.processFilter(NtlmPostFilter.java:81)
	at com.liferay.portal.kernel.servlet.BaseFilter.doFilter(BaseFilter.java:123)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
	at com.liferay.portal.kernel.servlet.BaseFilter.processFilter(BaseFilter.java:196)
	at com.liferay.portal.sharepoint.SharepointFilter.processFilter(SharepointFilter.java:179)
	at com.liferay.portal.kernel.servlet.BaseFilter.doFilter(BaseFilter.java:123)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
	at com.liferay.portal.kernel.servlet.BaseFilter.processFilter(BaseFilter.java:196)
	at com.liferay.portal.servlet.filters.virtualhost.VirtualHostFilter.processFilter(VirtualHostFilter.java:240)
	at com.liferay.portal.kernel.servlet.BaseFilter.doFilter(BaseFilter.java:123)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
	at com.liferay.portal.kernel.servlet.BaseFilter.processFilter(BaseFilter.java:196)
	at com.liferay.portal.kernel.servlet.BaseFilter.doFilter(BaseFilter.java:126)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
	at com.liferay.portal.kernel.servlet.BaseFilter.processFilter(BaseFilter.java:196)
	at com.liferay.portal.kernel.servlet.BaseFilter.doFilter(BaseFilter.java:126)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
	at org.tuckey.web.filters.urlrewrite.UrlRewriteFilter.doFilter(UrlRewriteFilter.java:738)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
	at com.liferay.portal.kernel.servlet.BaseFilter.processFilter(BaseFilter.java:196)
	at com.liferay.portal.servlet.filters.threadlocal.ThreadLocalFilter.processFilter(ThreadLocalFilter.java:35)
	at com.liferay.portal.kernel.servlet.BaseFilter.doFilter(BaseFilter.java:123)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
	at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
	at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
	at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:470)
	at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
	at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
	at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
	at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:298)
	at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:857)
	at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:588)
	at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:489)
	at java.lang.Thread.run(Thread.java:619)
Caused by: com.liferay.portal.kernel.dao.orm.ORMException: org.hibernate.exception.SQLGrammarException: could not execute query
	at com.liferay.portal.dao.orm.hibernate.ExceptionTranslator.translate(ExceptionTranslator.java:30)
	at com.liferay.portal.dao.orm.hibernate.QueryImpl.list(QueryImpl.java:78)
	at com.liferay.portal.kernel.dao.orm.QueryUtil.list(QueryUtil.java:67)
	at com.liferay.portal.kernel.dao.orm.QueryUtil.list(QueryUtil.java:52)
	at com.liferay.portlet.documentlibrary.service.persistence.DLFileVersionPersistenceImpl.findByG_F_N(DLFileVersionPersistenceImpl.java:619)
	... 90 more
Caused by: org.hibernate.exception.SQLGrammarException: could not execute query
	at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:92)
	at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
	at org.hibernate.loader.Loader.doList(Loader.java:2297)
	at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2172)
	at org.hibernate.loader.Loader.list(Loader.java:2167)
	at org.hibernate.hql.classic.QueryTranslatorImpl.list(QueryTranslatorImpl.java:936)
	at org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:196)
	at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1258)
	at org.hibernate.impl.QueryImpl.list(QueryImpl.java:102)
	at com.liferay.portal.dao.orm.hibernate.QueryImpl.list(QueryImpl.java:68)
	... 93 more
Caused by: com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: Unknown column 'dlfilevers0_.folderId' in 'field list'
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:936)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2985)
	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1631)
	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1723)
	at com.mysql.jdbc.Connection.execSQL(Connection.java:3256)
	at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1313)
	at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1448)
	at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:76)
	at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:208)
	at org.hibernate.loader.Loader.getResultSet(Loader.java:1849)
	at org.hibernate.loader.Loader.doQuery(Loader.java:718)
	at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:270)
	at org.hibernate.loader.Loader.doList(Loader.java:2294)
	... 100 more
----- 

[David H Nebinger, modificado 12 Anos atrás.]

MySQL, especially on the Linux side, is case-sensitive with regard to table names. In Linux, "TABLE" is different from "Table" which is different from "table"...

There are parameters you can set for MySQL on the Linux side to disable case-sensitivity, but it applies across the board to all tables in your MySQL database.

Windows MySQL is not case sensitive - TABLE is the same as Table is the same as table.

Liferay will use the same case for the table that you define in your service.xml file. Most of the time it turns out that the database was initially created on one OS and then moved to another OS, and this case-sensitivity issue rears it's ugly head.

Sound like something that happened on your end?

[Ranendra Das, modificado 12 Anos atrás.]

Hi David,

Thanks for your reply. This is what happen.

I have my Liferay application up and running in prod machine, liferya is CE 6.0.6

Now business owner wants to migrate to EE6.0.6. So I was taking backup of my database which is connected to the production server and then tried to restore the db locally, after restoring - I tried to connect the restored db with EE and run the server, it was not 100% successful. I was not getting everything like contents/images etc.

After few hours I directly connected to production db from my local EE and I was getting most of the links working fine except few images.

After that I returned to the current application which is running in CE and there most of the links are not working, these links are linked to pdf files/docs.

And I saw there are 2 sets of tables.

Why do I have 370 tables? And then top of that I was that exception, so how can I solve this issue? How can I solve this exception? Any idea

Regards,
RD

[David H Nebinger, modificado 12 Anos atrás.]

Yes, but what OS are you running in production vs locally? If prod is linux and local is windows, you can have issues unless your MySQL is configured correctly. In fact, even if both are the same OS but you have the settings different for case-sensitive table names, you can end up w/ the 370 tables like you have.

The docs breakages are due to how docs and images are stored. They are typically stored on the filesystem, with links in the database. For example, if you store a doc in production, it will be stored on the prod filesystem with a link in the database; so then you copy the database locally (but not the filesystem info), so the link appears in the local liferay but clicking on it will fail (because the doc is not in the local filesystem). Same thing can happen when you load the docs locally and push only the database to production.

[Ranendra Das, modificado 12 Anos atrás.]

Hi David,

Thanks for your mail.

I noticed a difference in tables, like, DLFileVersion and dlfileversion do not have same number of columns,specially the 'folderId' column was missing in the new tables, I don't know why. Then I got the following link

http://www.liferay.com/community/forums/-/message_boards/message/8796572

So, basically I have to clean up my documents related tables and then again uploaded, now its working, but this is not a good solution. But it works with few hours effort.

Now, I think Liferay should not allow the application to create new table with limited columns. for example, DLFileVersion does not have fodlerid and name as a column, but dlfileversion has got folderId and name as column. I need to look at liferray source code how this new tables are created.


Regards,
Ranendra

[Hitoshi Ozawa, modificado 12 Anos atrás.]

This isn't a Liferay issue but more of your mysql installation/setup issue. You should have set lower_case_table_names=1 in your my.cnf file under [mysqld].