Bug #114615 mysql-connector-j turn sql_mode STRICT_TRANS_TABLE on even server turned it off
Submitted: 10 Apr 16:13 Modified: 12 Apr 11:59
Reporter: Ronggang Yu Email Updates:
Status: Duplicate Impact on me:
Category:Connector / J Severity:S3 (Non-critical)
Version:8.3.0 OS:Any
Assigned to: CPU Architecture:Any

[10 Apr 16:13] Ronggang Yu
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:


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://";
        String user = "xxxxx";
        String password = "xxxxxx";
        try {
            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");

        catch (Exception e)

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:

Suggested fix:
[12 Apr 11:59] MySQL Verification Team
Hello Ronggang Yu,

Thank you for the bug report.
Imho this is duplicate of Bug #23371, please see Bug #23371.

Ashwini Patil