Table 'netxms.hibernate_sequence' for reporting

Started by troffasky, March 07, 2019, 04:06:50 PM

Previous topic - Next topic

troffasky

Inching my way towards a reporting server.
I have the server service up and running.
I have uploaded the sample report "Alarm Resolution Time". If I try Execute Report, it thinks for a bit but no report appears and nothing is output in workspace/output/4916972f-87c7-4a37-af2c-4d8522e9e645.
I get events like this logged:


[4916972f-87c7-4a37-af2c-4d8522e9e645]
Hibernate: select reportresu0_.id as id1_1_, reportresu0_.executionTime as executio2_1_, reportresu0_.jobId as jobId3_1_, reportresu0_.reportId as reportId4_1_, reportresu0_.userId as userId5_1_ from reporting_results reportresu0_ where reportresu0_.reportId=? and reportresu0_.userId=? order by reportresu0_.executionTime desc
Hibernate: select reportresu0_.id as id1_1_, reportresu0_.executionTime as executio2_1_, reportresu0_.jobId as jobId3_1_, reportresu0_.reportId as reportId4_1_, reportresu0_.userId as userId5_1_ from reporting_results reportresu0_ where reportresu0_.reportId=? and reportresu0_.userId=? order by reportresu0_.executionTime desc
Hibernate: select reportresu0_.id as id1_1_, reportresu0_.executionTime as executio2_1_, reportresu0_.jobId as jobId3_1_, reportresu0_.reportId as reportId4_1_, reportresu0_.userId as userId5_1_ from reporting_results reportresu0_ where reportresu0_.reportId=? and reportresu0_.userId=? order by reportresu0_.executionTime desc


If I add report to the schedule, I get a stack trace like so immediately:


Exception in thread "Thread-14" org.hibernate.exception.SQLGrammarException: error performing isolated work
        at org.hibernate.exception.internal.SQLExceptionTypeDelegate.convert(SQLExceptionTypeDelegate.java:63)
        at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:42)
        at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:109)
        at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:95)
        at org.hibernate.resource.transaction.backend.jdbc.internal.JdbcIsolationDelegate.delegateWork(JdbcIsolationDelegate.java:78)
        at org.hibernate.id.enhanced.TableStructure$1.getNextValue(TableStructure.java:125)
        at org.hibernate.id.enhanced.NoopOptimizer.generate(NoopOptimizer.java:40)
        at org.hibernate.id.enhanced.SequenceStyleGenerator.generate(SequenceStyleGenerator.java:412)
        at org.hibernate.event.internal.AbstractSaveEventListener.saveWithGeneratedId(AbstractSaveEventListener.java:101)
        at org.hibernate.event.internal.DefaultSaveOrUpdateEventListener.saveWithGeneratedOrRequestedId(DefaultSaveOrUpdateEventListener.java:192)
        at org.hibernate.event.internal.DefaultSaveEventListener.saveWithGeneratedOrRequestedId(DefaultSaveEventListener.java:38)
        at org.hibernate.event.internal.DefaultSaveOrUpdateEventListener.entityIsTransient(DefaultSaveOrUpdateEventListener.java:177)
        at org.hibernate.event.internal.DefaultSaveEventListener.performSaveOrUpdate(DefaultSaveEventListener.java:32)
        at org.hibernate.event.internal.DefaultSaveOrUpdateEventListener.onSaveOrUpdate(DefaultSaveOrUpdateEventListener.java:73)
        at org.hibernate.internal.SessionImpl.fireSave(SessionImpl.java:679)
        at org.hibernate.internal.SessionImpl.save(SessionImpl.java:671)
        at org.hibernate.internal.SessionImpl.save(SessionImpl.java:666)
        at com.radensolutions.reporting.service.impl.NotificationServiceImpl.create(NotificationServiceImpl.java:25)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:498)
        at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:333)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:190)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157)
        at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:99)
        at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:282)
        at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
        at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:213)
        at com.sun.proxy.$Proxy35.create(Unknown Source)
        at com.radensolutions.reporting.service.impl.SessionImpl.reportNotify(SessionImpl.java:258)
        at com.radensolutions.reporting.service.impl.SessionImpl.processMessage(SessionImpl.java:78)
        at com.radensolutions.reporting.service.impl.SessionWorker.run(SessionWorker.java:53)
        at java.lang.Thread.run(Thread.java:748)
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table 'netxms.hibernate_sequence' doesn't exist
        at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
        at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
        at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
        at com.mysql.jdbc.Util.handleNewInstance(Util.java:400)
        at com.mysql.jdbc.Util.getInstance(Util.java:383)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:980)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3847)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3783)
        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2447)
        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2594)
        at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2545)
        at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1901)
        at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:2002)
        at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:76)
        at org.hibernate.id.enhanced.TableStructure.executeQuery(TableStructure.java:224)
        at org.hibernate.id.enhanced.TableStructure.access$300(TableStructure.java:46)
        at org.hibernate.id.enhanced.TableStructure$1$1.execute(TableStructure.java:139)
        at org.hibernate.id.enhanced.TableStructure$1$1.execute(TableStructure.java:126)
        at org.hibernate.jdbc.WorkExecutor.executeReturningWork(WorkExecutor.java:55)
        at org.hibernate.jdbc.AbstractReturningWork.accept(AbstractReturningWork.java:34)
        at org.hibernate.resource.transaction.backend.jdbc.internal.JdbcIsolationDelegate.delegateWork(JdbcIsolationDelegate.java:56)
        ... 30 more


   
The report server is then unreachable and I eventually get a timeout in nxmc. The service is still running at this point and accepting connections, it seems, because I don't get an immediate error [like when it isn't installed].

I can't find any reference to hibernate in the report. I have tried adding           
<dialect>org.hibernate.dialect.MySQLDialect</dialect>
to nxreporting.xml but it doesn't make any difference.

troffasky

Bit more experimentation...I only get this stack trace if I schedule the report and include an email recipient. It doesn't matter if I choose to attach the report or not.

troffasky

Found IP Inventory.jrxml, tested it in Jasper Studio and it seems to work fine.
Tried to package it with mvn package but it fails with "Could not find artifact com.radensolutions:nxreporting:jar:2.0-SNAPSHOT".
Followed suggestion here:
https://www.netxms.org/forum/general-support/compiling-jasper-report-with-maven-dependency-error/msg20604/
and removed reference to nxreporting.
Still bombs out when running, I have noticed that it produces some files:
# file /opt/nxreporting/workspace/output/*/*
/opt/nxreporting/workspace/output/514cd0c0-077a-4832-ab93-e69e700826be/bc9543da-cf74-49c5-8eba-144790d548e1.jrprint: Java serialization data, version 5
/opt/nxreporting/workspace/output/514cd0c0-077a-4832-ab93-e69e700826be/d7d80e9a-4a26-4634-8343-5a16df2ac4e3.jrprint: Java serialization data, version 5
/opt/nxreporting/workspace/output/514cd0c0-077a-4832-ab93-e69e700826be/e3e791f6-106a-4818-82ae-039de1b91f7c.jrprint: Java serialization data, version 5

which coincide with when it crashes.

troffasky

After some research, I think I need to add

<property name="hibernate.id.new_generator_mappings" value="false"/>

to something, but where?

troffasky

In a fraction of the time so far spent on NetXMS reporting, I have managed to install Jasper server and get the sample "IP Inventory" report working with that. Just need to learn how to write reports now!