Knex: Set MySQL sql_mode on knex pool connection

[lanceschi]

Hi,

i would like to set sql_mode to NO_ENGINE_SUBSTITUTION in order to bypass the default strict mode in MySQL server v5.7.x

One can check the sql_mode MySQL variable through typing SHOW VARIABLES LIKE 'sql_mode'; from MySQL client console.

• Default for MySQL server v5.6.x
  NO_ENGINE_SUBSTITUTION
• Default for MySQL server v5.7.x
  ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

My current workaround was to modify mysqld section in /etc/mysql/my.cnf file:
sql_mode='NO_ENGINE_SUBSTITUTION'

I would like to keep it on the webapplication level rather than the DB server level though. Any advices appreciated.

Thanks!

[lanceschi]

Hi @rhys-vdw ,

I don't know if it is the official way to achieve the above mentioned result, nonetheless overriding the afterCreate function in the pool section of connection settings does the trick for me. Here's the source:

dbase: {
          client: 'mysql',
          connection: {
            socketPath: '/var/run/mysqld/mysqld.sock',
            database : 'db_name',
            user: 'db_username',
            password : 'db_password',
            timezone: 'UTC',
            charset: 'utf8mb4_unicode_ci',
            supportBigNumbers:true
          },
          pool: {
            min: 2,
            max: 10,
            afterCreate: function(conn, cb) {
              conn.query('SET sql_mode="NO_ENGINE_SUBSTITUTION";', function (err) {
                cb(err, conn);
              });
            }
          }
        }

Best regards,
Luca

[rhys-vdw]

Thanks @lanceschi, I didn't respond because I had no idea what the solution is. I wonder if it would be worth adding something about this callback to the FAQ?

[wubzz]

Callback is documented, closing