Dear All, I’m trying to move over the testing infrastructure to the new infrastructure based on ubuntu 14.04 (we are on 12.04 now). 12.04 uses MySQL 5.5 and 14.04 allows the use of MySQL 5.6, which we say we are compatible with. MySQL does not store fractional seconds. Until version 5.6.4 (https://dev.mysql.com/doc/refman/5.6/en/fractional-seconds.html) it cuts off fractional seconds at comparison time, eg. comparing “2016-01-01 00:00:00.000001” against what is stored in MySQL “2016-01-01 00:00:00” would return a tuple in 5.6.4 but will fail beyond 5.6.4. The issue presents itself if you use the “@once” schedule interval.
Other databases (Postgres, SQLite, etc) store fractional seconds by default so do not exhibit this error. Since MySQL 5.6.4 it can also store fractional seconds, but for backwards compatibility it needs to be specified in the schema. Also note that MySQL behavior (not storing fractional seconds) goes against SQL standards as is noted by themselves (http://dev.mysql.com/doc/refman/5.7/en/fractional-seconds.html). There are two solutions to this issue: 1. Update the schema for MySQL to include fractional seconds. PRO: - no coding changes - makes mysql behave conform standards - easier to maintain - future proof CON: - needs to maintain schema - requires an update to the schema of running mysql instances 2. Change the code to remove fractional settings (particularly .now() invocations) PRO: - No impact on running MySQL instances CON: - Impact on other databases that now loose precision, and might for a brief time show different behavior - Code to maintain, cannot use .now() directly - Be very careful when using date time and accessing the DB There was some back and forth discussion on bitter about this, but we don’t seem to reach a conclusion. Hence I would like to call for a vote - at this election day :). Of course with arguments if needed. If there is a better way I’m of course open to that. I vote for OPTION 1. Bolke
