• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Liutauras Vilda
  • Bear Bibeault
  • Junilu Lacar
  • Martin Vashko
Sheriffs:
  • Jeanne Boyarsky
  • Tim Cooke
  • Knute Snortum
Saloon Keepers:
  • Ron McLeod
  • Tim Moores
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
Bartenders:
  • Scott Selikoff
  • salvin francis
  • Piet Souris

Diagnosing problem with JDBC results

 
Ranch Hand
Posts: 255
Mac Eclipse IDE Safari
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I am in the process of writing a small program to illustrate a problem I'm trying to narrow down. I have a java application which uses a mySQL 8 database and mySQL JDBC driver for Java. When I read a date time field from my database using my App which is running on Windows or MacOS it comes back with a time component which is correct. When I run the same app on Linux the time component is wrong. There are a few variables to narrow down on.

On Mac and Windows I'm using Oracle Java 1.8 202 and on Ubuntu Linux I'm using OpenJDK 1.8 202

I was about to follow the route of checking if the problem went away if I run the Linux version under Oracle Java - but I have also noted that when I download the mySQL JDBC driver from their website they offer versions for

Ubuntu
Debian
Suze
Red Hat
Fedora
Platform Independent.

So - can someone explain why the jdbc has to have a Linux distribution specific file - at the moment I'm using the Platform independent version and I will try the Ubuntu version as well.







When running the java code under MacOS I get the time component "08:00"

When running the java code under Ubuntu 18 I get the time component "00:00"

MacOs is running Oracle Java and Linux OpenJDK but I've not determined if it's the Java Version or OS yet - the database is common between both.

Dave
 
Saloon Keeper
Posts: 2788
367
Android Eclipse IDE Angular Framework MySQL Database TypeScript Redhat Java Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

David Garratt wrote:



Does it make any difference if you refer to the name of the column using the same case as it was defined as in DDL?:
 
Ron McLeod
Saloon Keeper
Posts: 2788
367
Android Eclipse IDE Angular Framework MySQL Database TypeScript Redhat Java Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
MySQL 8.0 Reference Manual - 9.2.3 Identifier Case Sensitivity

MySQL 8.0 Reference Manual wrote:In MySQL, databases correspond to directories within the data directory. Each table within a database corresponds to at least one file within the database directory (and possibly more, depending on the storage engine). Triggers also correspond to files. Consequently, the case sensitivity of the underlying operating system plays a part in the case sensitivity of database, table, and trigger names. This means such names are not case-sensitive in Windows, but are case-sensitive in most varieties of Unix. One notable exception is macOS, which is Unix-based but uses a default file system type (HFS+) that is not case-sensitive. However, macOS also supports UFS volumes, which are case-sensitive just as on any Unix.

 
David Garratt
Ranch Hand
Posts: 255
Mac Eclipse IDE Safari
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Good idea - but the results the same.

Found this which is relevant webpage
 
Bartender
Posts: 1204
38
IBM DB2 Netbeans IDE Spring Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

David Garratt wrote:Good idea - but the results the same.

Found this which is relevant webpage



Web page you linked indicates that you need to handle explicitly datetimes on MySQL for Linux. Did you solve your problem?
 
David Garratt
Ranch Hand
Posts: 255
Mac Eclipse IDE Safari
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I have written this code and converted into a runnable jar.



The only external jar in my classpath is mysql-connector-java-8.0.18.jar which is the "platform independent version"

When run on MacOS the output is as follows.



When run on my Ubuntu 18 machine I get this.



Using mySQL workbench the value in the table is

INSERT INTO `app_material_batch`(`MATERIAL`, `BATCH_NUMBER`, `STATUS`, `EXPIRY_DATE`) VALUES ('43341711', '9291093350', 'Restricted', '2021-10-31 00:00:00');
 
David Garratt
Ranch Hand
Posts: 255
Mac Eclipse IDE Safari
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Further testing indicates its to do with the time zone set for the OS - the Ubuntu install was using USA whereas the Mac (host) was using UK.

They both now return the time as 01:00:00.0 which is consistent but not the same as mySQL Workbench running on MacOS.

I will need to keep digging.

Dave
 
Claude Moore
Bartender
Posts: 1204
38
IBM DB2 Netbeans IDE Spring Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Try to use CONVERT_TZ function when both writing and reading your table, so that data are always referring to the same timezone.
 
David Garratt
Ranch Hand
Posts: 255
Mac Eclipse IDE Safari
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Claude Moore wrote:Try to use CONVERT_TZ function when both writing and reading your table, so that data are always referring to the same timezone.



Thanks for this.

Dave
 
The human mind is a dangerous plaything. This tiny ad is pretty safe:
Java file APIs (DOC, XLS, PDF, and many more)
https://products.aspose.com/total/java
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!