Fóruns

Início » Liferay Portal » English » 3. Development

Visualização combinada Visão plana Exibição em árvore
Tópicos [ Anterior | Próximo ]
toggle
Ranen Das
SQLGrammarException MySQLSyntaxErrorException
2 de Agosto de 2011 12:56
Resposta

Ranen Das

Ranking: Regular Member

Mensagens: 137

Data de entrada: 6 de Abril de 2011

Mensagens recentes

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

  1
  22011/08/02 18:13:45 WARN
  3com.liferay.portal.util.PortalImpl.sendError(PortalImpl.java:3848) [][http-8080-25]
  4com.liferay.portal.kernel.exception.SystemException: com.liferay.portal.kernel.dao.orm.ORMException: org.hibernate.exception.SQLGrammarException: could not execute query
  5com.liferay.portal.kernel.exception.SystemException: com.liferay.portal.kernel.dao.orm.ORMException: org.hibernate.exception.SQLGrammarException: could not execute query
  6    at com.liferay.portal.service.persistence.impl.BasePersistenceImpl.processException(BasePersistenceImpl.java:195)
  7    at com.liferay.portlet.documentlibrary.service.persistence.DLFileVersionPersistenceImpl.findByG_F_N(DLFileVersionPersistenceImpl.java:623)
  8    at com.liferay.portlet.documentlibrary.service.impl.DLFileVersionLocalServiceImpl.getLatestFileVersion(DLFileVersionLocalServiceImpl.java:67)
  9    at sun.reflect.GeneratedMethodAccessor646.invoke(Unknown Source)
 10    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
 11    at java.lang.reflect.Method.invoke(Method.java:597)
 12    at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:309)
 13    at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:183)
 14    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150)
 15    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:110)
 16    at com.liferay.portal.dao.jdbc.aop.DynamicDataSourceTransactionInterceptor.invoke(DynamicDataSourceTransactionInterceptor.java:44)
 17    at com.liferay.portal.spring.aop.ChainableMethodAdvice.invoke(ChainableMethodAdvice.java:58)
 18    at com.liferay.portal.spring.aop.ChainableMethodAdvice.invoke(ChainableMethodAdvice.java:58)
 19    at com.liferay.portal.spring.aop.ChainableMethodAdvice.invoke(ChainableMethodAdvice.java:58)
 20    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
 21    at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:89)
 22    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
 23    at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:202)
 24    at $Proxy146.getLatestFileVersion(Unknown Source)
 25    at com.liferay.portlet.documentlibrary.service.DLFileVersionLocalServiceUtil.getLatestFileVersion(DLFileVersionLocalServiceUtil.java:254)
 26    at com.liferay.portlet.documentlibrary.service.permission.DLFileEntryPermission.contains(DLFileEntryPermission.java:85)
 27    at com.liferay.portlet.documentlibrary.service.permission.DLFileEntryPermission.check(DLFileEntryPermission.java:45)
 28    at com.liferay.portlet.documentlibrary.service.impl.DLFileEntryServiceImpl.getFileEntryByTitle(DLFileEntryServiceImpl.java:196)
 29    at sun.reflect.GeneratedMethodAccessor644.invoke(Unknown Source)
 30    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
 31    at java.lang.reflect.Method.invoke(Method.java:597)
 32    at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:309)
 33    at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:183)
 34    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150)
 35    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:110)
 36    at com.liferay.portal.dao.jdbc.aop.DynamicDataSourceTransactionInterceptor.invoke(DynamicDataSourceTransactionInterceptor.java:44)
 37    at com.liferay.portal.spring.aop.ChainableMethodAdvice.invoke(ChainableMethodAdvice.java:58)
 38    at com.liferay.portal.spring.aop.ChainableMethodAdvice.invoke(ChainableMethodAdvice.java:58)
 39    at com.liferay.portal.spring.aop.ChainableMethodAdvice.invoke(ChainableMethodAdvice.java:58)
 40    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
 41    at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:89)
 42    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
 43    at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:202)
 44    at $Proxy142.getFileEntryByTitle(Unknown Source)
 45    at com.liferay.portlet.documentlibrary.service.DLFileEntryServiceUtil.getFileEntryByTitle(DLFileEntryServiceUtil.java:124)
 46    at com.liferay.portal.webserver.WebServerServlet.sendFile(WebServerServlet.java:274)
 47    at com.liferay.portal.webserver.WebServerServlet.service(WebServerServlet.java:113)
 48    at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
 49    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
 50    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
 51    at com.liferay.portal.kernel.servlet.BaseFilter.processFilter(BaseFilter.java:196)
 52    at com.liferay.portal.servlet.filters.autologin.AutoLoginFilter.processFilter(AutoLoginFilter.java:254)
 53    at com.liferay.portal.kernel.servlet.BaseFilter.doFilter(BaseFilter.java:123)
 54    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
 55    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
 56    at com.liferay.portal.kernel.servlet.BaseFilter.processFilter(BaseFilter.java:196)
 57    at com.liferay.portal.servlet.filters.sso.ntlm.NtlmPostFilter.processFilter(NtlmPostFilter.java:81)
 58    at com.liferay.portal.kernel.servlet.BaseFilter.doFilter(BaseFilter.java:123)
 59    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
 60    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
 61    at com.liferay.portal.kernel.servlet.BaseFilter.processFilter(BaseFilter.java:196)
 62    at com.liferay.portal.sharepoint.SharepointFilter.processFilter(SharepointFilter.java:179)
 63    at com.liferay.portal.kernel.servlet.BaseFilter.doFilter(BaseFilter.java:123)
 64    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
 65    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
 66    at com.liferay.portal.kernel.servlet.BaseFilter.processFilter(BaseFilter.java:196)
 67    at com.liferay.portal.servlet.filters.virtualhost.VirtualHostFilter.processFilter(VirtualHostFilter.java:240)
 68    at com.liferay.portal.kernel.servlet.BaseFilter.doFilter(BaseFilter.java:123)
 69    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
 70    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
 71    at com.liferay.portal.kernel.servlet.BaseFilter.processFilter(BaseFilter.java:196)
 72    at com.liferay.portal.kernel.servlet.BaseFilter.doFilter(BaseFilter.java:126)
 73    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
 74    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
 75    at com.liferay.portal.kernel.servlet.BaseFilter.processFilter(BaseFilter.java:196)
 76    at com.liferay.portal.kernel.servlet.BaseFilter.doFilter(BaseFilter.java:126)
 77    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
 78    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
 79    at org.tuckey.web.filters.urlrewrite.UrlRewriteFilter.doFilter(UrlRewriteFilter.java:738)
 80    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
 81    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
 82    at com.liferay.portal.kernel.servlet.BaseFilter.processFilter(BaseFilter.java:196)
 83    at com.liferay.portal.servlet.filters.threadlocal.ThreadLocalFilter.processFilter(ThreadLocalFilter.java:35)
 84    at com.liferay.portal.kernel.servlet.BaseFilter.doFilter(BaseFilter.java:123)
 85    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
 86    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
 87    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
 88    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
 89    at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:470)
 90    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
 91    at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
 92    at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
 93    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:298)
 94    at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:857)
 95    at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:588)
 96    at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:489)
 97    at java.lang.Thread.run(Thread.java:619)
 98Caused by: com.liferay.portal.kernel.dao.orm.ORMException: org.hibernate.exception.SQLGrammarException: could not execute query
 99    at com.liferay.portal.dao.orm.hibernate.ExceptionTranslator.translate(ExceptionTranslator.java:30)
100    at com.liferay.portal.dao.orm.hibernate.QueryImpl.list(QueryImpl.java:78)
101    at com.liferay.portal.kernel.dao.orm.QueryUtil.list(QueryUtil.java:67)
102    at com.liferay.portal.kernel.dao.orm.QueryUtil.list(QueryUtil.java:52)
103    at com.liferay.portlet.documentlibrary.service.persistence.DLFileVersionPersistenceImpl.findByG_F_N(DLFileVersionPersistenceImpl.java:619)
104    ... 90 more
105Caused by: org.hibernate.exception.SQLGrammarException: could not execute query
106    at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:92)
107    at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
108    at org.hibernate.loader.Loader.doList(Loader.java:2297)
109    at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2172)
110    at org.hibernate.loader.Loader.list(Loader.java:2167)
111    at org.hibernate.hql.classic.QueryTranslatorImpl.list(QueryTranslatorImpl.java:936)
112    at org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:196)
113    at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1258)
114    at org.hibernate.impl.QueryImpl.list(QueryImpl.java:102)
115    at com.liferay.portal.dao.orm.hibernate.QueryImpl.list(QueryImpl.java:68)
116    ... 93 more
117Caused by: com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: Unknown column 'dlfilevers0_.folderId' in 'field list'
118    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:936)
119    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2985)
120    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1631)
121    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1723)
122    at com.mysql.jdbc.Connection.execSQL(Connection.java:3256)
123    at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1313)
124    at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1448)
125    at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:76)
126    at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:208)
127    at org.hibernate.loader.Loader.getResultSet(Loader.java:1849)
128    at org.hibernate.loader.Loader.doQuery(Loader.java:718)
129    at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:270)
130    at org.hibernate.loader.Loader.doList(Loader.java:2294)
131    ... 100 more
132-----
David H Nebinger
RE: SQLGrammarException MySQLSyntaxErrorException
2 de Agosto de 2011 13:50
Resposta

David H Nebinger

Community Moderator

Ranking: Liferay Legend

Mensagens: 11788

Data de entrada: 1 de Setembro de 2006

Mensagens recentes

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?
Ranen Das
RE: SQLGrammarException MySQLSyntaxErrorException
2 de Agosto de 2011 14:38
Resposta

Ranen Das

Ranking: Regular Member

Mensagens: 137

Data de entrada: 6 de Abril de 2011

Mensagens recentes

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
RE: SQLGrammarException MySQLSyntaxErrorException
3 de Agosto de 2011 06:45
Resposta

David H Nebinger

Community Moderator

Ranking: Liferay Legend

Mensagens: 11788

Data de entrada: 1 de Setembro de 2006

Mensagens recentes

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.
Ranen Das
RE: SQLGrammarException MySQLSyntaxErrorException
4 de Agosto de 2011 09:24
Resposta

Ranen Das

Ranking: Regular Member

Mensagens: 137

Data de entrada: 6 de Abril de 2011

Mensagens recentes

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
RE: SQLGrammarException MySQLSyntaxErrorException
4 de Agosto de 2011 16:33
Resposta

Hitoshi Ozawa

Ranking: Liferay Legend

Mensagens: 7949

Data de entrada: 23 de Março de 2010

Mensagens recentes

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 .