Server SQL mode and init command(s)?

[dkarlovi]

I'd like to set the server SQL mode on each new connection. This would be a query which should get executed on every new connection created, before all other queries.

Is there a way to specify the SQL mode or even to run an "init command"?

[dougwilson]

Hi @dkarlovi, there are two main ways you want to do this:

1. If you are using mysql.createPool:

You want to attach a connection event listener to your pool object (documentation https://github.com/felixge/node-mysql#connection) and perform your query in that listener.

2. If you are using mysql.createConnection:

You want to just call .query with your command right after you create the connection.

I hope that helps!

[dkarlovi]

@dougwilson it does, thank you. You could consider adding a setting which would enable this sort of functionality out of the box. For example, PHP's PDO has an "init command" which does exactly that.

Thanks.

[dougwilson]

@dkarlovi, thanks for that link! The "init" command looks like something we do provide out of the box, but maybe I'm mistaken in the comparison? For example, if I translated

$pdo = new PDO(
    'mysql:host=mysql.example.com;dbname=example_db',
    "username",
    "password",
    array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));

to this module, I would write it as:

var pool = mysql.createPool('mysql://username:password@mysql.example.com/example_db')
  .on('connection', (conn) => conn.query('SET NAMES utf8'));

They seem very much the same to me, just with the different language and slightly different concepts that exist between PHP and Node.js, no? If not, what kind of API would you suggest?

[dkarlovi]

@dougwilson the key difference I see is you're required to add a listener yourself which can be very tricky to do if your pool creation code is buried inside say Loopback's MySQL connector.

PDO uses an option to do it which enables you to specify it as any other connection param, you could add a connection flag INIT_COMMAND (or something similar) which would trigger the query / queries on connection, only difference is the lib provides the listener. Libraries like Doctrine then allow you to specify the option in your configuration file, without adding a line of code.

[dougwilson]

Ah, @dkarlovi, I see. So, this module doesn't particularly have influence over wrappers around it like that MySQL connector, rather we usually consider what it would be like using our module. For example, perhaps a solution here would be that the Loopback MySQL connector can take the array of init queries and it can handle the work of attaching the listener for you?

Otherwise, I'm still not quite sure what the API change here would look like. Could you provide some example JavaScript for how it would look? You described using a connection flag INIT_COMMAND, but if you are talking about the connection flags implemented in this module, they are just the literal flags that are sent to the MySQL server, nothing this module really interprets, and I don't quite understand how it will know the init queries to run?

Perhaps another possibility here is would you be willing to implement a pull request adding the feature as you envision it? Sometimes seeing the code helps a lot with understanding your vision.

[dkarlovi]

Yes, I see your point and am not trying to force a solution, especially since I'm rather new to the community and environment, am a firm believer in meritocracy. :)

So, let's say a change like this:

var opts = {username: 'foo'}
mysql.setInitCommands(['SET @variable := 123']);
var pool = mysql.createPool(opts); // executes the queries

Or:

var opts = {username: 'foo', initCommands: ['SET @variable := 123']}
var pool = mysql.createPool(opts); // executes the queries

This way, MySQL connector (or any other wrapper) might just propagate the functionality the same way as for example supportBigNumbers.

Thanks for your time and the answer.

[mnpenner]

How can I do this with node-mysql2?

e.g.

import MySql from 'mysql2/promise';
import config from '../config';
import formatSql from './formatSql';
import {log} from './debug';

const pool = MySql.createPool({
    ...config.database,
    queryFormat: formatSql,
    charset: 'utf8_unicode_ci',
    timezone: 'America/Vancouver',
});

log(Reflect.ownKeys(pool));

export default pool;

The only methods on there are:

[ 'getConnection', 'query', 'execute', 'end' ]

i.e., there is no "on" method.

Specifically, I want to make sure every connection/query is running as sql_mode='TRADITIONAL'.

[dougwilson]

Hi @mnpenner probably ask on the mysql2 issue tracker.

[sidorares]

@mnpenner if it's mysql2 specific question better post as mysql2 issue and reference this one for context

How can I do this with node-mysql2?

I don't think it's possible now, need to port latest pool feom mysqljs/mysql to mysql2 ( would be great if somebody could help with that)