Error using REST webservice based on MySQL / PostgreSQL

User 0d64032b61

09-05-2014 15:41:20

Hi,


I has imported 500M+ structures to mysql database with JChem Manager, and installed JChem REST webservice at Tomcat, setup the ws-config.xml at the folder .chemaxon. While I initiated the database config by sending a POST query (.../webservices/rest-v0/data/jcwsmy/init) or .../webservices/rest-v0/data/jcwsmy, an error message always appeared :


{"errorCode":1,"errorMessage":"Specified key was too long; max key length is 1000 bytes","rootCause":"SQLException","rootErrorMessage":"Specified key was too long; max key length is 1000 bytes"}


Seems that there are keys whose column(s)'length exceeds the max value. However, I can confirm that exactly three columns in the table hosting structures are of INTEGER and one is of VARCHAR(255) - although the default character-set is UTF-8, they should not have a length more than 1000 bytes. Even when I drop the index on the column of VARCHAR(255), the error still existed. So what caused the problem?


MySQL: 5.5.34
JChem/Base: 6.2.1
JChem Webservice 6.2.0.b7
Tomcat: 7


In order to compare a different environment, I then tried the Webservice to connect PostgreSQL 9.2, then I got the following error message, in which I defined the "jchemmetadatatbl" as the metaDataTable:


{"errorCode":1,"errorMessage":"ERROR: relation \"jchemmetadatatbl\" does not exist\n  Position: 13","rootCause":"SQLException","rootErrorMessage":"ERROR: relation \"jchemmetadatatbl\" does not exist\n  Position: 13"}


Would you please help me to resolve these problems? Many thanks in advance.


CT

ChemAxon 13811e1703

09-05-2014 18:32:03

Hi CT,

Can you attach related logs from the server? ({catalina_home}/logs/chemaxon.log) 

(In case of importing with JChem Manager, there is no need to init the database with the web services)

Thanks,
Peter 

User 0d64032b61

10-05-2014 12:10:52

Hi Peter, attached is the log file after the following 3 webservice operations:


1. .../webservices/rest-v0/data/sample
2. .../webservices/rest-v0/data/svjcmy
3. .../webservices/rest-v0/data/svjcpg


As for the MySQL, the problem should be caused by the length of two columns, ENTRY_KEY, ENTRY_SCOPE, in the table JCHemMetaDataTbl, exceeds 1000 bytes: (200 + 200) * 3 = 1200 > 1000 (3 for UTF-8 encoding). The table is created by the webservice processing automatically:
mysql> show create table JCHemMetaDataTbl;
CREATE TABLE `JCHemMetaDataTbl` (
  `ENTRY_KEY` varchar(200) COLLATE utf8_unicode_ci DEFAULT NULL,
  `ENTRY_VALUE` longtext COLLATE utf8_unicode_ci,
  `ENTRY_SCOPE` varchar(200) COLLATE utf8_unicode_ci DEFAULT NULL,
  `ENTRY_MODIFIED` timestamp NULL DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci


As for the PostgreSQL, the log says the problem is caused by org.jooq.exception.DataAccessException: SQL [delete from JChemMetaDataTbl where entry_scope = ?]; ERROR: relation "jchemmetadatatbl" does not exist


Thanks.


CT

ChemAxon 13811e1703

10-05-2014 17:58:06

Hi CT,

Thanks for the detailed error report!

Is your MySQL configured with the InnoDB storage engine? If not, please try to configure it with:


set GLOBAL storage_engine='InnoDb';

For PostgreSQL please try to use uppercase table name like the default JCHEMMETADATATABLE for the meta data table as a workaround. 


We are working on finding solution for these bugs as soon as possible.

Regards,
Peter 

User 0d64032b61

11-05-2014 13:52:12

Many thanks Peter,


I drop the old tables, and did what you instructed, the webservices returned the following message:


1. for MySQL: {"productName":"MySQL","productVersion":"5.5.34-log","driverName":"MySQL-AB JDBC Driver","driverVersion":"mysql-connector-java-5.1.10 ( Revision: ${svn.Revision} )","initialized":true}


2. for PostgreSQL: {"productName":"PostgreSQL","productVersion":"9.2.7","driverName":"PostgreSQL Native Driver","driverVersion":"PostgreSQL 9.0 JDBC4 (build 801)","initialized":true}


Do they mean that the webservices begin to work properly?


As an extended question, your instruction regarding MySQL sets the new tables using InnoDb engine. How about InnoDb for the table hosting the structures? I think MyISAM may offer better performance of tables which take much more reading operations than writing or updating.


Sure, I think the problems I reported are a bit unreasonable, and maybe should be treated as bugs. Hope they can be fixed soon.


CT

ChemAxon 13811e1703

11-05-2014 16:05:21

Hi!

It seems the databases now connected and initialized correctly, you can try some basic requests with the interactive apidocs page on url:

.../webservices/apidocs

Regards,
Peter 

User 0d64032b61

13-05-2014 10:19:49

Hi Peter,


The webservice works well for MySQL; however, for PostgreSQL, for example when I sent the query:


.../webservices/rest-v0/data/svjcpg/table/pg_a_table_name


it always returned the error message:


{"errorCode":0,"errorMessage":"java.lang.NullPointerException","rootCause":"java.lang.NullPointerException"}


Following is the relative section in chemaxon.log:


18:14:32.361 [http-bio-8020-exec-5] DEBUG c.j.w.util.RestServiceFilter - 127.0.0.1 remaining quota: 100 (251.2 \u03bcs)
18:14:32.372 [http-bio-8020-exec-5] DEBUG c.j.webservice2.util.JooqMetaHelper - schema query: select current_schema(): 1.402 ms
18:14:32.376 [http-bio-8020-exec-5] DEBUG c.j.w.model.jchem.JChemDBUtils - allTAbles: 6.278 ms
18:14:32.429 [http-bio-8020-exec-5] DEBUG c.j.w.d.AbstractDBMetaDataProvider - table meta read: 3.772 ms
18:14:32.429 [http-bio-8020-exec-5] DEBUG c.j.w.d.AbstractDBMetaDataProvider - table meta parse: 9.289 \u03bcs
18:14:32.437 [http-bio-8020-exec-5] DEBUG c.j.w.m.j.CachingRegenerationChecker - isRegenerationNeeded: 5.227 ms
18:14:32.440 [http-bio-8020-exec-5] DEBUG c.j.w.d.AbstractDBMetaDataProvider - column meta read: 1.349 ms
18:14:32.444 [http-bio-8020-exec-5] ERROR c.c.c.c.jersey.RestExceptionMapper - java.lang.NullPointerException
com.google.common.util.concurrent.UncheckedExecutionException: java.lang.NullPointerException
    at com.google.common.cache.LocalCache$Segment.get(LocalCache.java:2258) ~[guava-13.0.1.jar:na]
    at com.google.common.cache.LocalCache.get(LocalCache.java:3990) ~[guava-13.0.1.jar:na]
    at com.google.common.cache.LocalCache$LocalManualCache.get(LocalCache.java:4793) ~[guava-13.0.1.jar:na]
    at chemaxon.jchem.webservice2.model.jchem.CachingJChemDBUtils.listStructureTables(CachingJChemDBUtils.java:136) ~[CachingJChemDBUtils.class:na]
    at chemaxon.jchem.webservice2.model.jchem.JChemDBUtils.getStructureTableMap(JChemDBUtils.java:145) ~[JChemDBUtils.class:na]
    at chemaxon.jchem.webservice2.model.jchem.JChemDBUtils.getStructureTable(JChemDBUtils.java:155) ~[JChemDBUtils.class:na]
    at chemaxon.jchem.webservice2.model.jchem.JChemContextAware.getStructureTable(JChemContextAware.java:47) ~[JChemContextAware.class:na]
    at chemaxon.jchem.webservice2.model.jchem.JChemTable.getTableInfo(JChemTable.java:795) ~[JChemTable.class:na]
    at chemaxon.jchem.webservice2.model.CachingChTable.getTableInfo(CachingChTable.java:245) ~[CachingChTable.class:na]
    at chemaxon.jchem.webservice2.restservices.v0.AbstractTableService.tableInfo(AbstractTableService.java:70) ~[AbstractTableService.class:na]
    at chemaxon.jchem.webservice2.restservices.v0.AbstractTableService$$FastClassByCGLIB$$bc57d480.invoke(<generated>) ~[spring-core-3.2.3.RELEASE.jar:na]
    at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204) ~[spring-core-3.2.3.RELEASE.jar:3.2.3.RELEASE]
    at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:698) ~[spring-aop-3.2.3.RELEASE.jar:3.2.3.RELEASE]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150) ~[spring-aop-3.2.3.RELEASE.jar:3.2.3.RELEASE]
    at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:91) ~[spring-aop-3.2.3.RELEASE.jar:3.2.3.RELEASE]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172) ~[spring-aop-3.2.3.RELEASE.jar:3.2.3.RELEASE]
    at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:631) ~[spring-aop-3.2.3.RELEASE.jar:3.2.3.RELEASE]
    at chemaxon.jchem.webservice2.restservices.v0.TableServiceImpl$$EnhancerByCGLIB$$56eb4902.tableInfo(<generated>) ~[spring-core-3.2.3.RELEASE.jar:na]
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.7.0_51]
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57) ~[na:1.7.0_51]
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.7.0_51]
    at java.lang.reflect.Method.invoke(Method.java:606) ~[na:1.7.0_51]
    at com.sun.jersey.spi.container.JavaMethodInvokerFactory$1.invoke(JavaMethodInvokerFactory.java:60) ~[jersey-server-1.17.1.jar:1.17.1]
    at com.sun.jersey.server.impl.model.method.dispatch.AbstractResourceMethodDispatchProvider$TypeOutInvoker._dispatch(AbstractResourceMethodDispatchProvider.java:185) ~[jersey-server-1.17.1.jar:1.17.1]
    at com.sun.jersey.server.impl.model.method.dispatch.ResourceJavaMethodDispatcher.dispatch(ResourceJavaMethodDispatcher.java:75) ~[jersey-server-1.17.1.jar:1.17.1]
    at com.sun.jersey.server.impl.uri.rules.HttpMethodRule.accept(HttpMethodRule.java:302) ~[jersey-server-1.17.1.jar:1.17.1]
    at com.sun.jersey.server.impl.uri.rules.ResourceClassRule.accept(ResourceClassRule.java:108) ~[jersey-server-1.17.1.jar:1.17.1]
    at com.sun.jersey.server.impl.uri.rules.RightHandPathRule.accept(RightHandPathRule.java:147) ~[jersey-server-1.17.1.jar:1.17.1]
    at com.sun.jersey.server.impl.uri.rules.RootResourceClassesRule.accept(RootResourceClassesRule.java:84) ~[jersey-server-1.17.1.jar:1.17.1]
    at com.sun.jersey.server.impl.application.WebApplicationImpl._handleRequest(WebApplicationImpl.java:1511) [jersey-server-1.17.1.jar:1.17.1]
    at com.sun.jersey.server.impl.application.WebApplicationImpl._handleRequest(WebApplicationImpl.java:1442) [jersey-server-1.17.1.jar:1.17.1]
    at com.sun.jersey.server.impl.application.WebApplicationImpl.handleRequest(WebApplicationImpl.java:1391) [jersey-server-1.17.1.jar:1.17.1]
    at com.sun.jersey.server.impl.application.WebApplicationImpl.handleRequest(WebApplicationImpl.java:1381) [jersey-server-1.17.1.jar:1.17.1]
    at com.sun.jersey.spi.container.servlet.WebComponent.service(WebComponent.java:416) [jersey-servlet-1.17.1.jar:1.17.1]
    at com.sun.jersey.spi.container.servlet.ServletContainer.service(ServletContainer.java:538) [jersey-servlet-1.17.1.jar:1.17.1]
    at com.sun.jersey.spi.container.servlet.ServletContainer.service(ServletContainer.java:716) [jersey-servlet-1.17.1.jar:1.17.1]
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:727) [servlet-api.jar:na]
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:303) [catalina.jar:7.0.52]
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208) [catalina.jar:7.0.52]
    at chemaxon.jchem.webservice2.util.license.LicensingFilter.doFilter(LicensingFilter.java:43) [LicensingFilter.class:na]
    at org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:346) [spring-web-3.2.3.RELEASE.jar:3.2.3.RELEASE]
    at org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:259) [spring-web-3.2.3.RELEASE.jar:3.2.3.RELEASE]
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241) [catalina.jar:7.0.52]
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208) [catalina.jar:7.0.52]
    at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52) [tomcat7-websocket.jar:7.0.52]
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241) [catalina.jar:7.0.52]
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208) [catalina.jar:7.0.52]
    at org.springframework.security.web.FilterChainProxy.doFilterInternal(FilterChainProxy.java:186) [spring-security-web-3.1.4.RELEASE.jar:3.1.4.RELEASE]
    at org.springframework.security.web.FilterChainProxy.doFilter(FilterChainProxy.java:160) [spring-security-web-3.1.4.RELEASE.jar:3.1.4.RELEASE]
    at org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:346) [spring-web-3.2.3.RELEASE.jar:3.2.3.RELEASE]
    at org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:259) [spring-web-3.2.3.RELEASE.jar:3.2.3.RELEASE]
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241) [catalina.jar:7.0.52]
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208) [catalina.jar:7.0.52]
    at chemaxon.jchem.webservice2.util.ResponseCachingFilter.doFilter(ResponseCachingFilter.java:25) [ResponseCachingFilter.class:na]
    at org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:346) [spring-web-3.2.3.RELEASE.jar:3.2.3.RELEASE]
    at org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:259) [spring-web-3.2.3.RELEASE.jar:3.2.3.RELEASE]
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241) [catalina.jar:7.0.52]
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208) [catalina.jar:7.0.52]
    at org.eclipse.jetty.servlets.UserAgentFilter.doFilter(UserAgentFilter.java:82) [jetty-servlets-7.6.8.v20121106.jar:7.6.8.v20121106]
    at org.eclipse.jetty.servlets.GzipFilter.doFilter(GzipFilter.java:243) [jetty-servlets-7.6.8.v20121106.jar:7.6.8.v20121106]
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241) [catalina.jar:7.0.52]
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208) [catalina.jar:7.0.52]
    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:220) [catalina.jar:7.0.52]
    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:122) [catalina.jar:7.0.52]
    at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:501) [catalina.jar:7.0.52]
    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:170) [catalina.jar:7.0.52]
    at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:98) [catalina.jar:7.0.52]
    at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:950) [catalina.jar:7.0.52]
    at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:116) [catalina.jar:7.0.52]
    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:408) [catalina.jar:7.0.52]
    at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1040) [tomcat-coyote.jar:7.0.52]
    at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:607) [tomcat-coyote.jar:7.0.52]
    at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:313) [tomcat-coyote.jar:7.0.52]
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145) [na:1.7.0_51]
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615) [na:1.7.0_51]
    at java.lang.Thread.run(Thread.java:744) [na:1.7.0_51]
Caused by: java.lang.NullPointerException: null
    at chemaxon.jchem.webservice2.model.jchem.JChemDBUtils.listStructureTables(JChemDBUtils.java:668) ~[JChemDBUtils.class:na]
    at chemaxon.jchem.webservice2.model.jchem.CachingJChemDBUtils.access$301(CachingJChemDBUtils.java:44) ~[CachingJChemDBUtils.class:na]
    at chemaxon.jchem.webservice2.model.jchem.CachingJChemDBUtils$4.call(CachingJChemDBUtils.java:139) ~[CachingJChemDBUtils$4.class:na]
    at chemaxon.jchem.webservice2.model.jchem.CachingJChemDBUtils$4.call(CachingJChemDBUtils.java:136) ~[CachingJChemDBUtils$4.class:na]
    at com.google.common.cache.LocalCache$LocalManualCache$1.load(LocalCache.java:4796) ~[guava-13.0.1.jar:na]
    at com.google.common.cache.LocalCache$LoadingValueReference.loadFuture(LocalCache.java:3589) ~[guava-13.0.1.jar:na]
    at com.google.common.cache.LocalCache$Segment.loadSync(LocalCache.java:2374) ~[guava-13.0.1.jar:na]
    at com.google.common.cache.LocalCache$Segment.lockedGetOrLoad(LocalCache.java:2337) ~[guava-13.0.1.jar:na]
    at com.google.common.cache.LocalCache$Segment.get(LocalCache.java:2252) ~[guava-13.0.1.jar:na]
    ... 75 common frames omitted
18:14:32.447 [http-bio-8020-exec-5] DEBUG c.j.w.util.RestServiceFilter - 85.49 ms - http://localhost:8020/webservices/rest-v0/data/svjcpg/table/svmolcol


Regards,


CT