mariadb: jdbc: setTimestamp truncates milliseconds

Question

It seems to me that milliseconds gets truncated if I insert them in my mariadb using a preparedStatement. Googling it was not successfull, I found lots of similar problems which are either resolved or do not apply. But it's hard to believe that I am the only one having this problem so I wanted to ask here first before submitting a bug to mariadb. The code is very simple:

Table:

create table tt (id decimal(10), create_time timestamp(6) default 0);
or
create table tt (id decimal(10), create_time datetime(6) default 0);

Javacode:

Class.forName("org.mariadb.jdbc.Driver");
Connection conn = DriverManager.getConnection(DB_URL,"root","abc");
Statement insert1 = conn.createStatement();
insert1.execute("insert into tt (id, create_time) values (1,'2013-07-18 13:44:22.123456')");
PreparedStatement insert2 = conn.prepareStatement(
  "insert into tt (id, create_time) values (?,?)");
insert2.setInt(1, 2);
insert2.setTimestamp(2, new Timestamp(1273017612999L));
insert2.execute();

Output on my DOS-console:

MariaDB [duc]> select * from tt;
+------+----------------------------+
| id   | create_time                |
+------+----------------------------+
|    1 | 2013-07-18 13:44:22.123456 |
|    2 | 2010-05-05 02:00:12.000000 |
+------+----------------------------+
2 rows in set (0.00 sec)

==> Milliseconds are lost for id=2.

Does this look like a bug to you? Or did I do something wrong?

OS: windows7, 64 bit MariaDB Version: 10.0.12 JDBC connector: 1.1.7 (mariadb) OR 5.1.32 (mysql)

I tested the same code on an Oracle database: Milliseconds get inserted.

Thanks for any help...

Update: I reported this to the mariaDB bug database: https://mariadb.atlassian.net/browse/CONJ-107

Answer 1

You need to enable "useFractionalSeconds" in the MariaDB JDBC driver. For example, "jdbc:mariadb://127.0.0.1:3306/somedb?useFractionalSeconds=true".

(IMHO this should be enabled by default, I don't know why it's not.)