I have an error in a query that is causing this message to be related in the log multiple times per second. I can see it’s obviously an SQL date issue, but is there any way to track down where this is as its not showing as a warning in my developer and I cant see any obvious reason why it would repeat multiple times per second.
I have a single cron job running every 15 mins which is not the problem and no Batch processors
Many thanks
Gordon
java.sql.SQLException: Value ‘0000-00-00 00:00:00’ can not be represented as java.sql.Timestamp
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1078)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:989)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:975)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:920)
at com.mysql.jdbc.ResultSetRow.getTimestampFast(ResultSetRow.java:1102)
at com.mysql.jdbc.ByteArrayRow.getTimestampFast(ByteArrayRow.java:127)
at com.mysql.jdbc.ResultSetImpl.getTimestampInternal(ResultSetImpl.java:6588)
at com.mysql.jdbc.ResultSetImpl.getTimestamp(ResultSetImpl.java:6188)
at org.apache.commons.dbcp.DelegatingResultSet.getTimestamp(DelegatingResultSet.java:250)
at org.apache.commons.dbcp.DelegatingResultSet.getTimestamp(DelegatingResultSet.java:250)
at org.apache.commons.dbcp.DelegatingResultSet.getTimestamp(DelegatingResultSet.java:250)
at org.apache.commons.dbcp.DelegatingResultSet.getTimestamp(DelegatingResultSet.java:250)
at com.servoy.j2db.server.dataprocessing.Zw.createBufferedDataSet(Zw.java:406)
at com.servoy.j2db.server.dataprocessing.Zw.Za(Zw.java:88)
at com.servoy.j2db.server.dataprocessing.Zw.Za(Zw.java:1067)
at com.servoy.j2db.server.dataprocessing.Zw.Za(Zw.java:1406)
at com.servoy.j2db.server.dataprocessing.Zw.performQuery(Zw.java:902)
at com.servoy.j2db.dataprocessing.RelatedFoundSet.createRelatedFoundSets(RelatedFoundSet.java:291)
at com.servoy.j2db.dataprocessing.FoundSetManager.getRelatedFoundSet(FoundSetManager.java:695)
at com.servoy.j2db.dataprocessing.FoundSet.getRelatedFoundSet(FoundSet.java:5923)
at com.servoy.j2db.dataprocessing.Record.getRelatedFoundSet(Record.java:689)
at com.servoy.j2db.dataprocessing.Record.getRelatedFoundSet(Record.java:717)
at com.servoy.j2db.dataprocessing.Record.getValue(Record.java:247)
at com.servoy.j2db.dataprocessing.Record.getValue(Record.java:167)
at com.servoy.j2db.dataprocessing.DisplaysAdapter.setRecord(DisplaysAdapter.java:114)
at com.servoy.j2db.dataprocessing.DataAdapterList.setRecord(DataAdapterList.java:403)
at com.servoy.j2db.server.headlessclient.dataui.WebDataRenderer.refreshRecord(WebDataRenderer.java:342)
at com.servoy.j2db.FormController.refreshAllPartRenderers(FormController.java:729)
at com.servoy.j2db.BasicFormController.valueChanged(BasicFormController.java:469)
at javax.swing.DefaultListSelectionModel.fireValueChanged(DefaultListSelectionModel.java:184)
at com.servoy.j2db.util.model.AlwaysRowSelectedSelectionModel.fireValueChanged(AlwaysRowSelectedSelectionModel.java:245)
at javax.swing.DefaultListSelectionModel.fireValueChanged(DefaultListSelectionModel.java:154)
at com.servoy.j2db.util.model.AlwaysRowSelectedSelectionModel.setSelectedRows(AlwaysRowSelectedSelectionModel.java:225)
at com.servoy.j2db.util.model.AlwaysRowSelectedSelectionModel.setSelectedRow(AlwaysRowSelectedSelectionModel.java:190)
at com.servoy.j2db.util.model.AlwaysRowSelectedSelectionModel.setSelectedRow(AlwaysRowSelectedSelectionModel.java:158)
at com.servoy.j2db.dataprocessing.SwingRelatedFoundSet.setSelectedIndex(SwingRelatedFoundSet.java:135)
at com.servoy.j2db.server.headlessclient.dataui.WebEventExecutor.setSelectedIndex(WebEventExecutor.java:695)
at com.servoy.j2db.server.headlessclient.MainPage.respond(MainPage.java:2233)
at com.servoy.j2db.server.headlessclient.PageContributor$EventCallbackBehavior.respond(PageContributor.java:617)
at org.apache.wicket.ajax.AbstractDefaultAjaxBehavior.onRequest(AbstractDefaultAjaxBehavior.java:312)
at org.apache.wicket.request.target.component.listener.BehaviorRequestTarget.processEvents(BehaviorRequestTarget.java:157)
at org.apache.wicket.request.AbstractRequestCycleProcessor.processEvents(AbstractRequestCycleProcessor.java:92)
at org.apache.wicket.RequestCycle.processEventsAndRespond(RequestCycle.java:1279)
at org.apache.wicket.RequestCycle.step(RequestCycle.java:1358)
at org.apache.wicket.RequestCycle.steps(RequestCycle.java:1465)
at org.apache.wicket.RequestCycle.request(RequestCycle.java:545)
at org.apache.wicket.protocol.http.WicketFilter.doGet(WicketFilter.java:486)
at com.servoy.j2db.server.servlets.Zt.doGet(Zt.java:6)
at org.apache.wicket.protocol.http.WicketServlet.doGet(WicketServlet.java:138)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:617)
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 org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:103)
at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:615)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:293)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:861)
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:606)
at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:489)
at java.lang.Thread.run(Thread.java:745)
Hi Gorden,
MySQL has a nasty behavior of allowing invalid dates (0000-00-00 00:00:00 is very much a non-existing date). Java on the other hand tries to load that value into a Date object which does require it to be a valid date.
So this is where your error comes from, your data. You need to fix it there.
And if I read your Stacktrace correctly you are using Webclient (i.e. Wicket), which polls the server every 5 seconds to see if any data has changed (databroadcast). Perhaps it triggers multiple errors this way every 5 seconds.
Note: with Servoy 8’s NG-Client this polling will be history since it will have a stateful connection using web sockets so any changes will be pushed to the client like with Smart client.
Hope this helps.
Hi Robert
Thanks for the reply …
I have literally hundreds of queries that this could apply over several hundred forms. Can you suggest a way to verify the dates in this context ? I am not quite sure where this would be done ie I am passing in a from/to date and I need to ensure this a valid date format ??
With regards to Servoy 8 I am really interested in the whole NG-Client, but I am struggling to convince colleagues and clients what the benefits of using Servoy 8 are over just going direct to Angular/html. The demos so far have had issues and much as I can see the potential it’s quite an ask to suggest we pay a vast amount to upgrade to a product that is largely available free. Fairly obviously there is more to Servoy than just NG-Client but when you need say 200 licenses its a bit more involved.
Best
Gordon
Hi Gordon,
Gordon McLean:
I have literally hundreds of queries that this could apply over several hundred forms. Can you suggest a way to verify the dates in this context ? I am not quite sure where this would be done ie I am passing in a from/to date and I need to ensure this a valid date format ??
It’s not the forms or the solution. You have these invalid dates in your database that Java (by way of your jdbc driver) can’t handle. I can’t imagine that your servoy solution is producing these invalid dates so my guess is you have some external process that creates these. Perhaps a CMS or an import ?
Gordon McLean:
With regards to Servoy 8 I am really interested in the whole NG-Client, but I am struggling to convince colleagues and clients what the benefits of using Servoy 8 are over just going direct to Angular/html. The demos so far have had issues and much as I can see the potential it’s quite an ask to suggest we pay a vast amount to upgrade to a product that is largely available free. Fairly obviously there is more to Servoy than just NG-Client but when you need say 200 licenses its a bit more involved.
I have done pure AngularJS solutions (hooked up to Servoy using Velocity) and it’s pretty involved, Servoy 8 has the potential to significantly reduce the development time with this. Especially with the new form editor.
So (as always) it’s a matter of balancing the pros and cons. More development time and less licenses or easy/quick development/maintenance and more licenses. Of course other factors like available server resources/budget might also play a role in your decision.
ROCLASI:
Hi Gordon,
It’s not the forms or the solution. You have these invalid dates in your database that Java (by way of your jdbc driver) can’t handle. I can’t imagine that your servoy solution is producing these invalid dates so my guess is you have some external process that creates these. Perhaps a CMS or an import ?
OK this is strange as all the dates as far as I know were generated in Servoy I wonder if its the MySQL datetime format thats causing the problem most of them are only dates so I guess this could be changed relatively easily
I have done pure AngularJS solutions (hooked up to Servoy using Velocity) and it’s pretty involved, Servoy 8 has the potential to significantly reduce the development time with this. Especially with the new form editor.
So (as always) it’s a matter of balancing the pros and cons. More development time and less licenses or easy/quick development/maintenance and more licenses. Of course other factors like available server resources/budget might also play a role in your decision.
I figured this might be the case, I have also done some work on AngularJS/Velocity and Servoy the results are impressive but as you say its involved. I would also guess you’re losing some of the benefits too in terms of sockets etc that Servoy is offering and are not present in the AVS version.
I have asked Jan Aleman to contact me earlier re the cost and I will take deep breaths in anticipation. There is no doubt a servoy based solution would vastly reduce the learning curve.
best
Gordon
Cheers
Gordon
Hi Gordon,
Gordon McLean:
ROCLASI:
It’s not the forms or the solution. You have these invalid dates in your database that Java (by way of your jdbc driver) can’t handle. I can’t imagine that your servoy solution is producing these invalid dates so my guess is you have some external process that creates these. Perhaps a CMS or an import ?
OK this is strange as all the dates as far as I know were generated in Servoy I wonder if its the MySQL datetime format thats causing the problem most of them are only dates so I guess this could be changed relatively easily
Hmm…if I recall correctly what could have happened is that you have date columns that are not nullable but you didn’t insert dates so MySQL will ‘help’ you and insert a (bogus) value in there so it’s not NULL. I believe this is setup as a default value on the column.
Can you check your tables for these invalid dates and see if those columns are nullable or not?
By the way if you use MySQL in Strict-Sql-Mode you don’t really have these issues. MySQL will then throw an error (like every other RDBMS vendor does) when you try to insert invalid data.
Strict sql mode is however not a default setting so most MySQL instances don’t have it enabled.
Also take a look at Constraints on invalid data.
ROCLASI:
Hmm…if I recall correctly what could have happened is that you have date columns that are not nullable but you didn’t insert dates so MySQL will ‘help’ you and insert a (bogus) value in there so it’s not NULL. I believe this is setup as a default value on the column.
Can you check your tables for these invalid dates and see if those columns are nullable or not?
Firstly I have found a number of create dates that were set to timestamp for some reason, I am going to switch these to date time. I will download the structure and check all dates can go to NULL by default at the same time. I need to be careful obviously as it’s currently live and we have a lot of users accessing it.
ROCLASI:
By the way if you use MySQL in Strict-Sql-Mode you don’t really have these issues. MySQL will then throw an error (like every other RDBMS vendor does) when you try to insert invalid data.
Strict sql mode is however not a default setting so most MySQL instances don’t have it enabled.
Also take a look at Constraints on invalid data.
I will switch this over at the weekend so I can test it thoroughly
With some luck we may find this ![:)]( "Smile")
and it would make a HUGE difference !
Best
Gordon
I just saw that there is a change of behaviour in different JDBC drivers. Older drivers converted such a zero date to null, newer version don’t do that anymore. Have you maybe updated your jdbc driver? It seems you can add a parameter to the connection URL that will fix this: zeroDateTimeBehavior=convertToNull.
patrick:
I just saw that there is a change of behaviour in different JDBC drivers. Older drivers converted such a zero date to null, newer version don’t do that anymore. Have you maybe updated your jdbc driver? It seems you can add a parameter to the connection URL that will fix this: zeroDateTimeBehavior=convertToNull.
I recently had to upgrade the MySQL driver !
Can you tell me how to add this parameter to the driver ?
Gordon
e.g. this kind of URL in the connection settings
jdbc:mysql://localhost:3306/databasename?zeroDateTimeBehavior=convertToNull.
Interesting.
Using TIMESTAMP datatypes does seem to behave weirdly with that setting according to the following links. Using DATETIME datatypes seems to be the solution.
Then again these posts are at least 1 year old so who knows it still holds water.
http://stackoverflow.com/questions/2269 … vior-issue
http://stackoverflow.com/questions/4092 … -timestamp
Hope this helps.
Guys
Thank you this appears to have worked and cured a long term problem, sorry about the daft question - engage brain before hitting submit ![:oops:]( "Embarassed")
Gordon