Description:
MySQL Server side version is 8.0.34, and in my.cnf file, we set sql_mode like below: sql_mode=NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
then we run JDBC code to retrieve session sql_mode, it returns:
STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
Note, there is extra mode:STRICT_TRANS_TABLES, which is not expected.
I tested with MySQL workbench (C++ driver), and .NET driver, both are the same as server side setting, only for jdbc driver, it has extra STRICT_TRANS_TABLES.
How to repeat:
below is the repro code:
package com.ggg.demo;
import com.mysql.cj.protocol.Resultset;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class HelloWorld {
public static void main(String[] args)
{
System.out.println("hello world!");
Connection conn;
String driver = "com.mysql.cj.jdbc.Driver";
String url = "jdbc:mysql://127.0.0.1:28747/configdb?&useSSL=FALSE";
String user = "xxxxx";
String password = "xxxxxx";
try {
Class.forName(driver);
conn = DriverManager.getConnection(url, user, password);
if (!conn.isClosed()){
System.out.println("conn ok");
}
Statement stmt = conn.createStatement();
String sql = "select @@session.sql_mode as me";
ResultSet ret = stmt.executeQuery(sql);
while (ret.next())
{
String sql_mode = ret.getString("me");
System.out.println(sql_mode);
}
conn.close();
}
catch (Exception e)
{
System.out.println(e.getMessage());
}
}
}
It is expected to return NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION, but for JDBC driver, it return with:
STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
Suggested fix:
None
Description: MySQL Server side version is 8.0.34, and in my.cnf file, we set sql_mode like below: sql_mode=NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION then we run JDBC code to retrieve session sql_mode, it returns: STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION Note, there is extra mode:STRICT_TRANS_TABLES, which is not expected. I tested with MySQL workbench (C++ driver), and .NET driver, both are the same as server side setting, only for jdbc driver, it has extra STRICT_TRANS_TABLES. How to repeat: below is the repro code: package com.ggg.demo; import com.mysql.cj.protocol.Resultset; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; public class HelloWorld { public static void main(String[] args) { System.out.println("hello world!"); Connection conn; String driver = "com.mysql.cj.jdbc.Driver"; String url = "jdbc:mysql://127.0.0.1:28747/configdb?&useSSL=FALSE"; String user = "xxxxx"; String password = "xxxxxx"; try { Class.forName(driver); conn = DriverManager.getConnection(url, user, password); if (!conn.isClosed()){ System.out.println("conn ok"); } Statement stmt = conn.createStatement(); String sql = "select @@session.sql_mode as me"; ResultSet ret = stmt.executeQuery(sql); while (ret.next()) { String sql_mode = ret.getString("me"); System.out.println(sql_mode); } conn.close(); } catch (Exception e) { System.out.println(e.getMessage()); } } } It is expected to return NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION, but for JDBC driver, it return with: STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION Suggested fix: None