[SOLVED] Daily Report didn't run today and has this error

dan

Hey Guys, I had a daily water level report coming through each day, all of a sudden today it didn't come through, when I tried running it manually I get this error.
What could have changed in the background to cause this?

I tailed the log file also..


administrator@water:~$ tail -f logs/ma.log
        at org.springframework.jdbc.core.StatementCreatorUtils.setParameterValue(StatementCreatorUtils.java:163)
        at com.serotonin.db.spring.ArgPreparedStatementSetter.setValues(ArgPreparedStatementSetter.java:24)
        at com.serotonin.db.spring.ExtendedJdbcTemplate$1.doInPreparedStatement(ExtendedJdbcTemplate.java:117)
        at com.serotonin.db.spring.ExtendedJdbcTemplate$1.doInPreparedStatement(ExtendedJdbcTemplate.java:112)
        at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:586)
        ... 51 more
Caused by: java.sql.SQLException: An attempt was made to get a data value of type 'BLOB' from a data value of type 'java.io.ByteArrayInputStream'.
        at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source)
        at org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(Unknown Source)
        ... 66 more

Interestingly, I removed the report template and attempted to re-add it and the two points I was monitoring are greyed out?

Cheers
Dan

Attachment: download link

JoelHaggar

Very strange, you might want to try clearing your cache and see if the grayed out points come back. I'm not sure on the other errors, I'll have to let someone else take a look at that tomorrow. Let us know if you still have problems.

dan

Is that the browser cache? I'll try from another PC. thanks for that.

dan

I've restarted the instance of Mango and still the same error FYI.

Attached, a little more from the logs...




ERROR 2014-08-08 13:54:57,462 (com.serotonin.m2m2.web.dwr.util.ExceptionDetectionFilter.doFilter:38) - DWR invocation exception
org.springframework.dao.DataIntegrityViolationException: PreparedStatementCallback; SQL [insert into reportInstances   (userId, reportId, name, template, includeEvents, includeUserComments, reportStartTime, reportEndTime, runStartTime,      runEndTime, recordCount, preventPurge, mapping)   values (?,?,?,?,?,?,?,?,?,?,?,?,?)]; An attempt was made to get a data value of type 'BLOB' from a data value of type 'java.io.ByteArrayInputStream'.; nested exception is java.sql.SQLDataException: An attempt was made to get a data value of type 'BLOB' from a data value of type 'java.io.ByteArrayInputStream'.
        at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:245)
        at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
        at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:602)
        at com.serotonin.db.spring.ExtendedJdbcTemplate.update(ExtendedJdbcTemplate.java:112)
        at com.serotonin.db.spring.ExtendedJdbcTemplate.update(ExtendedJdbcTemplate.java:95)
        at com.serotonin.db.spring.ExtendedJdbcTemplate.update(ExtendedJdbcTemplate.java:87)
        at com.serotonin.db.DaoUtils.doInsert(DaoUtils.java:152)
        at com.serotonin.m2m2.reports.ReportDao.saveReportInstance(ReportDao.java:212)
        at com.serotonin.m2m2.reports.web.ReportWorkItem.queueReport(ReportWorkItem.java:81)
        at com.serotonin.m2m2.reports.web.ReportsDwr.runReport(ReportsDwr.java:223)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:606)
        at org.directwebremoting.impl.ExecuteAjaxFilter.doFilter(ExecuteAjaxFilter.java:34)
        at org.directwebremoting.impl.DefaultRemoter$1.doFilter(DefaultRemoter.java:428)
        at com.serotonin.m2m2.web.dwr.util.TranslationsFilter.doFilter(TranslationsFilter.java:37)
        at org.directwebremoting.impl.DefaultRemoter$1.doFilter(DefaultRemoter.java:428)
        at com.serotonin.m2m2.web.dwr.util.ExceptionDetectionFilter.doFilter(ExceptionDetectionFilter.java:26)
        at org.directwebremoting.impl.DefaultRemoter$1.doFilter(DefaultRemoter.java:428)
        at com.serotonin.m2m2.web.dwr.util.DwrPermissionFilter.doFilter(DwrPermissionFilter.java:38)
        at org.directwebremoting.impl.DefaultRemoter$1.doFilter(DefaultRemoter.java:428)
        at org.directwebremoting.impl.DefaultRemoter.execute(DefaultRemoter.java:431)
        at org.directwebremoting.impl.DefaultRemoter.execute(DefaultRemoter.java:283)
        at org.directwebremoting.servlet.PlainCallHandler.handle(PlainCallHandler.java:52)
        at org.directwebremoting.servlet.UrlProcessor.handle(UrlProcessor.java:101)
        at org.directwebremoting.servlet.DwrServlet.doPost(DwrServlet.java:146)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:727)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:820)
        at org.eclipse.jetty.servlet.ServletHolder.handle(ServletHolder.java:538)
        at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1352)
        at org.eclipse.jetty.servlets.UserAgentFilter.doFilter(UserAgentFilter.java:77)
        at org.eclipse.jetty.servlets.GzipFilter.doFilter(GzipFilter.java:141)
        at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1323)
        at org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:476)
        at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:119)
        at org.eclipse.jetty.security.SecurityHandler.handle(SecurityHandler.java:517)
        at org.eclipse.jetty.server.session.SessionHandler.doHandle(SessionHandler.java:225)
        at org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:937)
        at org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:406)
        at org.eclipse.jetty.server.session.SessionHandler.doScope(SessionHandler.java:183)
        at org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:871)
        at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:117)
        at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:110)
        at org.eclipse.jetty.server.Server.handle(Server.java:346)
        at org.eclipse.jetty.server.HttpConnection.handleRequest(HttpConnection.java:589)
        at org.eclipse.jetty.server.HttpConnection$RequestHandler.content(HttpConnection.java:1065)
        at org.eclipse.jetty.http.HttpParser.parseNext(HttpParser.java:823)
        at org.eclipse.jetty.http.HttpParser.parseAvailable(HttpParser.java:214)
        at org.eclipse.jetty.server.HttpConnection.handle(HttpConnection.java:411)
        at org.eclipse.jetty.io.nio.SelectChannelEndPoint.handle(SelectChannelEndPoint.java:535)
        at org.eclipse.jetty.io.nio.SelectChannelEndPoint$1.run(SelectChannelEndPoint.java:40)
        at org.eclipse.jetty.util.thread.QueuedThreadPool$3.run(QueuedThreadPool.java:529)
        at java.lang.Thread.run(Thread.java:744)
Caused by: java.sql.SQLDataException: An attempt was made to get a data value of type 'BLOB' from a data value of type 'java.io.ByteArrayInputStream'.
        at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown Source)
        at org.apache.derby.impl.jdbc.Util.newEmbedSQLException(Unknown Source)
        at org.apache.derby.impl.jdbc.Util.newEmbedSQLException(Unknown Source)
        at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source)
        at org.apache.derby.impl.jdbc.EmbedConnection.newSQLException(Unknown Source)
        at org.apache.derby.impl.jdbc.ConnectionChild.newSQLException(Unknown Source)
        at org.apache.derby.impl.jdbc.EmbedPreparedStatement.dataTypeConversion(Unknown Source)
        at org.apache.derby.impl.jdbc.EmbedPreparedStatement.setObject(Unknown Source)
        at org.springframework.jdbc.core.StatementCreatorUtils.setValue(StatementCreatorUtils.java:351)
        at org.springframework.jdbc.core.StatementCreatorUtils.setParameterValueInternal(StatementCreatorUtils.java:216)
        at org.springframework.jdbc.core.StatementCreatorUtils.setParameterValue(StatementCreatorUtils.java:163)
        at com.serotonin.db.spring.ArgPreparedStatementSetter.setValues(ArgPreparedStatementSetter.java:24)
        at com.serotonin.db.spring.ExtendedJdbcTemplate$1.doInPreparedStatement(ExtendedJdbcTemplate.java:117)
        at com.serotonin.db.spring.ExtendedJdbcTemplate$1.doInPreparedStatement(ExtendedJdbcTemplate.java:112)
        at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:586)
        ... 51 more
Caused by: java.sql.SQLException: An attempt was made to get a data value of type 'BLOB' from a data value of type 'java.io.ByteArrayInputStream'.
        at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source)
        at org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(Unknown Source)
        ... 66 more

JoelHaggar

What version of the reports module do you have? You might want to check and make sure it's the latest version: http://store.infiniteautomation.com/module/reports

dan

ah, reports 2.0.12
I recently updated Mango itself, maybe reports isn't included with the modules pack?

oh I read that wrong, it is running the latest according to that.

dan

BUMP

terrypacker

Dan,

I'm a developer for Infinite Automation and am happy to try and help you out.

It looks like something went wrong in the upgrade of the reports module.

What version were you previously using? And what was your report configuration if you have the old JSON export or are able to get the existing one back out?

I will dig into this first thing tomorrow morning and let you know what I find.

Thanks,
Terry

dan

Gidday. Thanks for your message. I think it started playing up before I upgraded as I upgraded in an attempt to fix this issue I think.

Cheers
Dan

terrypacker

Dan,

Can you tell me the previous version of the Reports Module you were using when the report starting playing up?

Thanks,
Terry

terrypacker

Dan,

Another option that would fix this if you don't have reports that you need to keep would be to mark the module for deletion from the modules page, restart Mango. Then shut mango down and install the latest module.

Thanks,
Terry

dan

Gidday, I'm not sure what version was running before. I think Mango itself was 2.2.2 maybe.
I will try the delete option.

Cheers
Dan

dan

Hey Guys, I'm just trying this again, I'll see if deleting the module altogether helps.

dan

Hrm, it's still not running any reports, I've tried creating all new ones. It almost looks like the DB table might be corrupt?

I'm familiar with MYSQL somewhat but not with the Mango DB ?

Attachment: download link

dan

This was the error log:


ERROR 2014-09-16 12:02:43,812 (com.serotonin.m2m2.web.dwr.util.ExceptionDetectionFilter.doFilter:38) - DWR invocation exception
org.springframework.dao.DataIntegrityViolationException: PreparedStatementCallback; SQL [insert into reportInstances   (userId, reportId, name, template, includeEvents, includeUserComments, reportStartTime, reportEndTime, runStartTime,      runEndTime, recordCount, preventPurge, mapping)   values (?,?,?,?,?,?,?,?,?,?,?,?,?)]; An attempt was made to get a data value of type 'BLOB' from a data value of type 'java.io.ByteArrayInputStream'.; nested exception is java.sql.SQLDataException: An attempt was made to get a data value of type 'BLOB' from a data value of type 'java.io.ByteArrayInputStream'.

JoelHaggar

It looks like something is wrong in the database. When you deleted the module did you use the Mark for Deletion button on the modules page and then restarted Mango? This should remove the old table. You can then put the latest reports module back in the modules folder and restart again. After the first restart you can use the SQL console to check and make sure the reports table is removed.

dan

Gidday.. yeh I used the mark for deletion button and it greyed out the whole reports module in red. Then I hit restart this instance button..

JoelHaggar

Hi Dan,

I'm not sure what to say at this point. Probably best to get on a remote support session. Contact me tomorrow as early as you can if possible.

Joel.

dan

Attached the output of the show tables command..

I can't see a reports table in there now that I've deleted the reports module.

If I was to delete the whole instance of Mango, if I backed up the config script, does that take all the scripts and data points with that?
Then how do I back the Graphical Overview up? Or would I have to set that one up manually again?

Cheers
Dan

Attachment: download link

JoelHaggar

I don't see any of the reports tables. Is this with the module installed? If not then I think it needs to be installed again. I'm sure you know but just to clarify, to install a module you place the zip file in the modules folder and DON'T unzip it. When mango starts up it will install it the module and create it's needed database tables.