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:
None 
Category:Connector / J Severity:S3 (Non-critical)
Version:8.3.0 OS:Any
Assigned to: CPU Architecture:Any

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

Regards,
Ashwini Patil