Incorrect datetime value in JDBCMessageArchiveRepository (#67)

Activities

wojciech.kapcia@tigase.net opened 4 years ago

java.lang.RuntimeException: Error retrieving messages from database
	at tigase.xmpp.mam.modules.QueryModule.process(QueryModule.java:82)
	at tigase.component.modules.StanzaProcessor.process(StanzaProcessor.java:164)
	at tigase.component.modules.StanzaProcessor.processPacket(StanzaProcessor.java:85)
	at tigase.component.AbstractKernelBasedComponent.processPacket(AbstractKernelBasedComponent.java:81)
	at tigase.server.AbstractMessageReceiver$QueueListener.run(AbstractMessageReceiver.java:1405)
Caused by: tigase.db.TigaseDBException: Cound not retrieve items
	at tigase.archive.db.JDBCMessageArchiveRepository.queryItems(JDBCMessageArchiveRepository.java:187)
	at tigase.archive.db.JDBCMessageArchiveRepository.queryItems(JDBCMessageArchiveRepository.java:50)
	at jdk.internal.reflect.GeneratedMethodAccessor150.invoke(Unknown Source)
	at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.base/java.lang.reflect.Method.invoke(Method.java:566)
	at tigase.stats.StatisticsInvocationHandler.invoke(StatisticsInvocationHandler.java:75)
	at com.sun.proxy.$Proxy58.queryItems(Unknown Source)
	at tigase.archive.db.MessageArchiveRepositoryPool.queryItems(MessageArchiveRepositoryPool.java:92)
	at tigase.archive.db.MessageArchiveRepositoryPool.queryItems(MessageArchiveRepositoryPool.java:45)
	at tigase.xmpp.mam.modules.QueryModule.process(QueryModule.java:80)
	... 4 more
Caused by: com.mysql.cj.jdbc.exceptions.MysqlDataTruncation: Data truncation: Incorrect datetime value: &apos;1970-01-01 00:00:00.0&apos; for column &apos;_from&apos; at row 1
	at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:104)
	at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:955)
	at com.mysql.cj.jdbc.ClientPreparedStatement.executeQuery(ClientPreparedStatement.java:1005)
	at com.mysql.cj.jdbc.CallableStatement.executeQuery(CallableStatement.java:886)
	at jdk.internal.reflect.GeneratedMethodAccessor9.invoke(Unknown Source)
	at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.base/java.lang.reflect.Method.invoke(Method.java:566)
	at tigase.db.jdbc.PreparedStatementInvocationHandler.invoke(PreparedStatementInvocationHandler.java:38)
	at com.sun.proxy.$Proxy38.executeQuery(Unknown Source)
	at tigase.archive.db.JDBCMessageArchiveRepository.getItemsCount(JDBCMessageArchiveRepository.java:535)
	at tigase.archive.db.JDBCMessageArchiveRepository.queryItems(JDBCMessageArchiveRepository.java:175)
	... 13 more

wojciech.kapcia@tigase.net commented 4 years ago

Packet:

[2020-08-29 18:19:45:837] [SEVERE  ] [  in_6-message-archive ] StanzaProcessor.processPacket()  : Error retrieving messages from database when processing from=sess-man@ip-10-0-7-38.us-west-2.compute.internal, to=message-archive@ip-10-0-7-38.us-west-2.compute.internal, DATA=[iq type="set" id="6d1dba18-7092-4b85-b547-32c527b6a3f1" from="mchl.rgn@tigase.im/dino.2158e156" xmlns="jabber:client"][query xmlns="urn:xmpp:mam:2"][x type="submit" xmlns="jabber:x:data"][field type="hidden" var="FORM_TYPE"][value]CData size: 14[/value][/field][field var="start"][value]CData size: 20[/value][/field][/x][/query][/iq], SIZE=341, XMLNS=jabber:client, PRIORITY=NORMAL, PERMISSION=AUTH, TYPE=set, STABLE_ID=null

Andrzej Wójcik (Tigase) commented 4 years ago

I've added workaround to check if the specified date for MySQL is 1970-01-01 00:00:00 and if so and database type is MySQL to use null as start and end parameters to overcome this issue as it is impossible to have entries earlier than on 1970 in MAM.

@wojtek Please review my change to make sure that condition is correct.

Type	Bug
Priority	Normal
Assignee	wojciech.kapcia@tigase.net
Version	tigase-server-8.2.0

#78 Closed

RuntimeException: Error retrieving messages from database (Incorrect datetime value: '1969-12-31 23:59:59.999' for column '_from' )
tigase-private/systems-maintenance/tigase-im-service#85 You are not authorized to access this issue
tigase/_server/tigase-muc#130 Closed

MysqlDataTruncation: Data truncation: Incorrect datetime value: "1969-12-31 16:00:00"

Issue Votes (0)

Watchers (0)

Reference

tigase/_server/tigase-message-archiving#67