For years we had a custom JDBC connection pool class in Java that we developed in house and an old MariaDB JDBC class.
Recently, we began to test in house an upgrade consisting of:
We ran into one huge problem. Historically we turned off strict mode because of thousands of legacy programs that disregard data truncation.
When I turned on JDBC driver debugging, to my surprise I find this:
[15:34:35.219] /* conn id 237527 clock: 1679340875219 / SET sql_mode='NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES'; [15:34:35.228] / conn id 237524 clock: 1679340875228 */ SET autocommit=1;
So what is happening is, STRICT_TRANS_TABLES is being turned on at the connection even though we neither requested or expected that.
I can find no mention in Hikari, AWS, or Connector J documentation of Google searches - exactly why did this happen - for sure our application does not request this.
One clue is that this ONLY HAPPENS WITH Hikari. When we stop using Hikari and go back to our own pool it stops. Yet I can find nothing in Hikari documentation about setting sql_mode.
When we revert to our own pooling class this behavior stops.
When we stop using Hikari this behavior stops.
We can find no mention in Hikari doc of sql_mode or how to set/ prevent changes.
I could not find anywhere in Hikari source code where "STRICT" is in any text.
I think I have found the answer. Somehow during the conversion we dropped the jdbcCompliantTruncation=false on Hikari that we used on our custom pooling class.
Apparently in the absence of that, the JDBC driver sets NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES regardless of what you have set on the server.