SNOW-1196082: Inserting and reading timestamps is not symetric if too much columns inserted with batch
Closed this issue · 17 comments
Please answer these questions before submitting your issue.
In order to accurately debug the issue this information is required. Thanks!
-
What version of JDBC driver are you using?
3.15.0 -
What operating system and processor architecture are you using?
Mac or Alma Linux -
What version of Java are you using?
Java 8 -
What did you do?
After inserting rows with timestamps values using PreparedStatement and batch, when reading back the rows , some values are shifted. The shift actually depends of the timezone of the machine you are running on ( be sure not to be on UTC to see the problem)
Note that this does not happen with older driver version like 3.13.15
It depends on 'how many columns' are inserted. With 3, there was no problem, with 8, the problem appears
The following program describe the problem (just change the boolean bug
to false or true to exercise with 3 or 8 columns )
-
What did you expect to see?
The values read should be the one that was written
-
Can you set logging to DEBUG and collect the logs?
https://community.snowflake.com/s/article/How-to-generate-log-file-on-Snowflake-connectors
-
What is your Snowflake account identifier, if any? (Optional)
Hello @ericcournarie ,
Thank you for raising the issue and sample application, we are taking a look.
Regards,
Sujan
Hello @ericcournarie ,
I tried the sample java application with the latest Snowflake JDBC driver 3.15.0, its giving the expected output, not throwing error.
The timezone is set to stmt.execute("alter session set timezone = 'America/New_York'");
Here is the application
if inserting ps.setTimestamp(2, new Timestamp(1403049600000L), utc);
Getting 1403049600000
if inserting ps.setTimestamp(2, new Timestamp(1388016000000L), utc);
Getting 1388016000000
Regards,
Sujan
Hi Sujan,
thanks for testing but
you should not reduce the number of lines so much, or the bug will not appear
Hello @ericcournarie ,
I tried even the full sample jdbc application as it is but still its not throwing any incorrect output.
I tried with latest jdbc 3.15.0 and set timezone for the user as timezone='UTC' etc.
Please try the following code, could you please try from a different client machine and use latest snowflake jdbc driver, please capture jdbc log as well.
Regards,
Sujan
Hi Sujan,
it works with yours, but this is not the one I gave you
please test with if (row % 10000 == 0) {
instead of your if (row % 1000 == 0) {
in my case it fails while it was working with yours
the bug should appear . it looks like it is related to a 'sizing' effect, as with less columns and less lines, the bug does not appear
thanks
Hello @ericcournarie ,
Thanks for the update.
I used same code if (row % 10000 == 0)
and checked with by setting various timezone ( and confirmed they set properly ) , but still the issue not getting reproduced, no error being thrown, no incorrect output.
stmt.execute("alter session set timezone = 'America/New_York'"); // CurrentTimestamp -> 1967-06-23 03:00:00.123 -0400
stmt.execute("alter session set timezone = 'UTC'"); // CurrentTimestamp -> 1967-06-23 07:00:00.123 Z
stmt.execute("alter session set timezone = 'Australia/Sydney'"); // CurrentTimestamp -> 1967-06-23 17:00:00.123 +1000
Here is the updated jdbc application, you can try running from a different client machine and capture the jdbc trace if able to reproduce.
Regards,
Sujan
Hello Sujan,
Sorry, but once again, your program is not correct and not the one you should run
you have for (int row = 0; row < 30; row++) {
when the one I provided to you is for (int row = 0; row < 30000; row++) {
by just changing this, I ran into the problem with your app.
Thanks
Hello Eric,
With loop (int row = 0; row < 30000; row++), its not throwing error but just a warning, but thats is not related to this issue.
WARNING: JAXB is unavailable. Will fallback to SDK implementation which may be less performant.If you are using Java 9+, you will need to include javax.xml.bind:jaxb-api as a dependency.
There is no other error being thrown, could you please try from a different client machine and capture jdbc logs.
Regards,
Sujan
Hello Sujan,
I have tried on several machine (Unix and Mac) with several Java version (8,11,17). As said, the Java machine should not be on UTC to see the bug.
This is different from the timezone set on Snowflake session
Can you add the following at the start of your program to test, so to be sure
System.out.println(" Timezone :" + TimeZone.getDefault().toString());
// change default Java timezone
TimeZone.setDefault(TimeZone.getTimeZone("Australia/Sydney"));
System.out.println(" Timezone :" + TimeZone.getDefault().toString());
Thanks
For some reason, cannot get the log . I have added 'tracing=ALL' , but nothing is outputted..
Hello Eric,
Thanks for the update.
When I put the TimeZone.setDefault(TimeZone.getTimeZone("Australia/Sydney")); st starting after connection and statement, I am able to reproduce it. We are working on it.
Regards,
Sujan
Hello @ericcournarie ,
The engineering team updated that "the timezone setting for the snowflake server is not the same as the timezone on the application" This is the root cause of the issue.
This issue did not happen on version 3.13.15 because the driver had uploaded the timestamp with only the UTC zone before version 3.13.22. A new feature has been applied since version 3.13.22 was released on bdb8546, and you can see that the timestamp value is converted with the timezone based on the system time zone( the customer machine).
However, when timestamp data is uploaded with the regular insert query, this data does not affect the timezone of the client machine, but the timezone of the snowflake server. This is to say that if the application does not change the timezone setting of the server with "alter session set timezone", the timestamp value will not be converted.
Therefore, if you do not set the timezone the same as the application timezone, the first row of the data will have a different timezone value on the sample application code. To resolve this issue, could you please execute the alter session set timezone = "the timezone you want " before inserting the data.
Regards,
Sujan
Hello Sujan,
Sadly, setting the timezone does not change the behaviour, the data when reading back is still not the same as the one written.
This workaround does not seem to work.
Even with a alter session set timezone = 'UTC'
Regards,
Eric
Hello @ericcournarie ,
The engineering team working on the fix, meanwhile could you let us know the value of the session parameter 'CLIENT_TIMESTAMP_TYPE_MAPPING' .
You can get it this way
`rstime= stmt.executeQuery("SHOW PARAMETERS LIKE 'CLIENT_TIMESTAMP_TYPE_MAPPING'");
// Processing the result set
while (rstime.next()) {
String parameterName = rstime.getString("key");
String parameterValue = rstime.getString("value");
System.out.println("Parameter Name: " + parameterName);
System.out.println("Parameter Value: " + parameterValue);
}`
Regards,
Sujan
Hello @sfc-gh-sghosh
@ericcournarie being in vacation, I will respond.
The command returns:
Parameter Name: CLIENT_TIMESTAMP_TYPE_MAPPING
Parameter Value: TIMESTAMP_LTZ
If needed few others params that you may be interested in:
Parameter Name: TIMEZONE
Parameter Value: Europe/Paris
Parameter Name: TIMESTAMP_TYPE_MAPPING
Parameter Value: TIMESTAMP_NTZ
Parameter Name: JDBC_USE_SESSION_TIMEZONE
Parameter Value: true
Parameter Name: JDBC_TREAT_TIMESTAMP_NTZ_AS_UTC
Parameter Value: false
Regards,
Aurelien
Hello @ericcournarie ,
The fix has been delivered in Snowflake JDBC 3.17.0, I checked as well, its working fine now, no error.
Please check.
Regards,
Sujan
Hello Sujan,
sorry I was out this summer . I confirm it's ok, thanks