[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

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 Fix For: 10.0.0.CR1 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