[jboss-jira] [JBoss JIRA] (WFLY-5199) On MariaDB 5.3+ and MySQL 5.6.4+, EJB timers in a JDBC storage don't work correctly due to timestamp rounding

[Jan Martiska (JIRA)]

     [ https://issues.jboss.org/browse/WFLY-5199?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Jan Martiska reassigned WFLY-5199:
----------------------------------

    Assignee: Stuart Douglas  (was: Jan Martiska)


> On MariaDB 5.3+ and MySQL 5.6.4+, EJB timers in a JDBC storage don't work correctly due to timestamp rounding
> -------------------------------------------------------------------------------------------------------------
>
>                 Key: WFLY-5199
>                 URL: https://issues.jboss.org/browse/WFLY-5199
>             Project: WildFly
>          Issue Type: Bug
>          Components: EJB
>    Affects Versions: 10.0.0.Beta2
>         Environment: MariaDB 5.3+, Mysql 5.6.4+
>            Reporter: Jan Martiska
>            Assignee: Stuart Douglas
>
> EJB timers don't execute, because the DatabaseTimerPersistence.shouldRun method returns false for them, because it is unable to SELECT them from the database using a java.sql.Timestamp.
> On MariaDB 5.3+ and MySQL 5.6.4+, the behavior of the DATETIME type has changed compared to MySQL 5.5 and older.
> In previous versions, it was possible to insert into DATETIME columns with JDBC using a java.sql.Timestamp which contained fractional seconds, the fraction was truncated/rounded and a subsequent SELECT statement with the same java.sql.Timestamp returned such rows, because the fraction was truncated/rounded from the queried Timestamp too.
> On MySQL 5.6.4+ and MariaDB 5.3+, when a java.sql.Timestamp including a fractional second is inserted into a DATETIME, attempting to SELECT with the same Timestamp doesn't return the row, because the DATETIME type now defaults to DATETIME(0) which truncates/rounds to whole seconds, and the queried Timestamp no longer gets truncated/rounded during the query execution. It would only work if the column was declared as DATETIME(6), which ensures that inserted values don't get truncated/rounded. Documentation: https://dev.mysql.com/doc/refman/5.7/en/fractional-seconds.html
> MySQL rounds the timestamp, MariaDB truncates it (always rounds down).
> However, declaring DATETIME(6) is not possible in older versions of MySQL or MariaDB, so to support both new and old versions, we have probably these options:
> 1. introduce a new DDL file(s) for MySQL 5.6.4+ and MariaDB 5.3+ and use it depending on the detected version - this DDL would use DATETIME(6) instead of DATETIME
> 2. change the DatabaseTimerPersistence implementation so that it will not insert fractional seconds into the database at all (EJB timers don't support timing with higher precision than whole seconds anyway)
> 3. same as option 2, but only for MariaDB/MySQL dialects 
> 4. any other idea?
> I personally like number 3 the best, it is the least risky and introduces only minimal changes.. but it's still somewhat ugly.
> This seems to resolve it for me (implementation suggestion of option 3): https://github.com/jmartisk/wildfly/commits/mysql-mariadb-timer-suggestion



--
This message was sent by Atlassian JIRA
(v6.3.15#6346)