Microseconds on JDBC timestamp literals

Help
1

When executing the following query through JDBC (notice the JDBC timestamp literals using the {ts ...} notation) against MemSQL:

select extract(microsecond from {ts '2015-02-02 14:30:45.123'}) from dual

the returned result is 0, which is wrong. This was tested using the standard MySQL JDBC driver. When executing the same query against a MySQL 8.0 database the correct result of 123000 is returned.

2

Thanks for reporting this. I’m not familiar with syntax your example uses ({ts ...}). When I tried the query with a string literal or with our cast syntax, I got the expected result:

| extract(microsecond from '2015-02-02 14:30:45.123') |
+-----------------------------------------------------+
|                                              123000 |
+-----------------------------------------------------+
1 row in set (0.00 sec)

memsql> select extract(microsecond from '2015-02-02 14:30:45.123' :> timestamp(6)) from dual;
+---------------------------------------------------------------------+
| extract(microsecond from '2015-02-02 14:30:45.123' :> timestamp(6)) |
+---------------------------------------------------------------------+
|                                                              123000 |
+---------------------------------------------------------------------+
1 row in set (0.01 sec)

The ts syntax is a client option so given that we see MemSQL can return the correct result it could be that the client is stripping the microseconds only for MemSQL and not for MySQL. For connector compatibility reasons, MemSQL reports itself as a v5.5 MySQL. However, this version of MySQL did not support timestamps with microsecond precision. It could be that JDBC is truncating the timestamp because it does not think the microseconds will be supported. We can increase the compat version and see if that helps. Can try running:

set global compat_version = '5.6.4';
3

Thanks for your response. The {ts ...} syntax is in fact standardized as part of the JDBC specification and given that the query returns the correct result when I use the very same JDBC driver to connect to a MySQL 8.0 database, it doesn’t appear to be a limitation of the driver. But I will give it a try with the compat_version setting as you propose and let you know if that helped or not.

Which JDBC driver is the recommended driver to use when connecting to MemSQL, the MySQL driver or the MariaDB driver? In the documentation I see both being mentioned separately on SingleStoreDB Cloud · SingleStore Documentation and on Client and Driver Downloads · SingleStore Documentation respectively?

Also, are there any options which are recommended to be set on the driver or in the database session when connecting with JDBC? I couldn’t find anything in the documentation.

4

The set global compat_version = '5.6.4' command indeed resolved the problem.

5

Thanks for the update. I’ll make sure this gets added to the docs for using JDBC.