Diagnosing problem with JDBC results

[David Garratt]

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.

 CREATE TABLE `APP_MATERIAL_BATCH` (
  `MATERIAL` varchar(20) NOT NULL,
  `BATCH_NUMBER` varchar(12) NOT NULL,
  `STATUS` varchar(20) DEFAULT NULL,
  `EXPIRY_DATE` datetime DEFAULT NULL,
  PRIMARY KEY (`MATERIAL`,`BATCH_NUMBER`),
  UNIQUE KEY `BATCH_PK` (`MATERIAL`,`BATCH_NUMBER`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
;

INSERT INTO `APP_MATERIAL_BATCH`(`MATERIAL`, `BATCH_NUMBER`, `STATUS`, `EXPIRY_DATE`) 
VALUES ('43341403', '9307093340', 'Restricted', '2021-11-30 08:00:00');

rs.getTimestamp("expiry_date")

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

[Ron McLeod]

David Garratt wrote: CREATE TABLE `APP_MATERIAL_BATCH` (  `MATERIAL` varchar(20) NOT NULL,  `BATCH_NUMBER` varchar(12) NOT NULL,  `STATUS` varchar(20) DEFAULT NULL,  `EXPIRY_DATE` datetime DEFAULT NULL,  PRIMARY KEY (`MATERIAL`,`BATCH_NUMBER`),  UNIQUE KEY `BATCH_PK` (`MATERIAL`,`BATCH_NUMBER`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; ;

rs.getTimestamp("expiry_date")

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?:

rs.getTimestamp("EXPIRY_DATE")

[Ron McLeod]

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]

Good idea - but the results the same.

Found this which is relevant webpage

[Claude Moore]

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]

I have written this code and converted into a runnable jar.

package jdbcTest;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class StartTest
{

	public static void main(String[] args)
	{
		try
		{
			Class.forName("com.mysql.cj.jdbc.Driver");
			Connection con = DriverManager.getConnection("jdbc:mysql://192.168.1.60:3306/mydatabase", myuser", "mypass");
			Statement stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery("select * from APP_MATERIAL_BATCH WHERE MATERIAL = '43341711' AND BATCH_NUMBER = '9291093350'");
			while (rs.next())
				System.out.println(rs.getTimestamp("EXPIRY_DATE"));
			con.close();
		}
		catch (Exception e)
		{
			System.out.println(e);
		}
	}

}

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.

2021-10-31 01:00:00.0

When run on my Ubuntu 18 machine I get this.

2021-10-30 17:00:00.0

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]

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]

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]

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