Incorrect datetime value in JDBCMessageArchiveRepository (#67)

Wojciech Kapcia (Tigase) opened 5 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

Related
- RuntimeException: Error retrieving messages from database (Incorrect datetime value: '1969-12-31 23:59:59.999' for column '_from' ) (#78) Closed
- MysqlDataTruncation: Data truncation: Incorrect datetime value: "1969-12-31 16:00:00" (tigase/_server/tigase-muc#130) Closed

Activities

Wojciech Kapcia (Tigase) commented 5 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 5 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.
Login to comment

Type	Bug
Priority	Normal
Assignee	Wojciech Kapcia (Tigase)
Version	tigase-server-8.2.0

Issue Votes (0)

Watchers (0)

Reference

tigase/_server/tigase-message-archiving#67