MySQL 5.6 with microsecond precision

[miyagawa]

MySQL 5.6 supports microsecond precision on TIME, DATETIME and TIMESTAMP columns.

Saving the microsecond with INSERT/UPDATE works fine if you set up the date format with usec manually, but retrieving them with mysql2 gem fails, because it appears that at the Mysql2::Result level the precision is chopped off to the second.

https://github.com/brianmario/mysql2/blob/master/ext/mysql2/result.c#L279

Guess the parser for DATETIME, TIME and TIMESTAMP has to be updated to support microseconds for mysql 5.6 or later.

require 'mysql2'

conn = Mysql2::Client.new(:socket => "/tmp/mysql56.sock", :database => 'test')
results = conn.query("INSERT INTO items (id, updated_at) VALUES (100, '2012-11-11 12:34:56.789012')")

conn.query('SELECT * FROM items WHERE id = 100').each do |row|
  p row['updated_at'].usec
end

emits 0, while in the actual database:

mysql> select * from items;
+-----+----------------------------+
| id  | updated_at                 |
+-----+----------------------------+
| 100 | 2012-11-11 12:34:56.789012 |
+-----+----------------------------+
1 row in set (0.00 sec)

[brianmario]

Thanks for reporting this!

For now, you can turn casting off and mysql2 will pass the strings it gets back from the server straight through so you (or an ORM) can deal with parsing the timestamp.

[miyagawa]

Ah I see! As it might be obvious from the example, i'd like to test it with ActiveRecord's magic updated_at column to save the microsec precision for the cache key. Let's see how to turn the casting off with AR...

[brianmario]

You should be able to just put cast: fast in database.yml

[sodabrew]

sscanf matches as much as it can from left to right, so you should be able to change sscanf to this: tokens = sscanf(string, "%4u-%2u-%2u %2u:%2u:%2u.%6u", &year, &month, &day, &hour, &min, &sec, &msec);

If tokens == 6 then a non-microsecond date was matched. If tokens == 7 then the date has microseconds.

I amped up compiler warnings in a little foo.c, and realized that the outvars are unsigned int, but the scanf is %d (signed int). scanf specifically does not initialize the outvars for unmatched positions, so a short match may leave stack frame leftovers in the outvars. We should probably clean up the scanf's a little bit...

gcc foo.c -o foo -Wall -pedantic -std=c99:

#include <stdio.h>
#include <inttypes.h>

int main()
{
  char *string = "2012-10-10 08:07:06";

  int tokens;
  unsigned int year=0, month=0, day=0, hour=0, min=0, sec=0, msec=100;
  uint64_t seconds;

  tokens = sscanf(string, "%4u-%2u-%2u %2u:%2u:%2u.%6u", &year, &month, &day, &hour, &min, &sec, &msec);
  seconds = (year*31557600ULL) + (month*2592000ULL) + (day*86400ULL) + (hour*3600ULL) + (min*60ULL) + sec;

  printf("%u %"PRIu64".%u\n", tokens, seconds, msec);

  return 0;
}

Output: 6 63520704426.100

Hi @miyagawa! :) Did you know that @brianmario sits in our old office?

[miyagawa]

Cool, I tested with cast: false and seems to be able to get nanosec in cache_key, although i needed to patch ActiveRecord side to inflate microsec as well: https://gist.github.com/4062186

[jeremy]

:+1:

[brianmario]

this should have been added in #326