Mysql: Set autocommit on in connection string

[johnrc]

I've tried using the following connection string on a database that has autocommit set to off by default:

const connString = 'mysql://user:pass@host/db?autocommit=1';

It doesn't seem to have any effect after connecting. Thus, I'm required to use transactions for each query. However, I can run query below before any other queries and it works.

conn.query('set autocommit=1', (err, results) => {
  if(err) console.error('Some error occurred');
  console.log(results);
});

If I want autocommit on, what's the best way to accomplish that if it doesn't work in the connection string? Should I run the set autocommit=1 first thing after connecting? Or should I just be using transactions/commits for every query?

[dougwilson]

Hi @johnrc the parameters you an provide in the URL-like string is any parameter that is a connection option (documented at https://github.com/mysqljs/mysql#connection-options). There is no autocommit connection option, so it doesn't have any meaning in the query string in your URL example. Since the status of the autocommit is not something that is done during the handshake of the MySQL protocol, it won't get added to the options. The only method to set it is to immediately make a query as you listed above.

[johnrc]

Thanks @dougwilson. Good to know.

It seems to work for a single connection. If I use pooling, will something like this work to ensure each connection has autocommit set correctly?

pool.on('acquire', function (connection) {
  connection.query('set autocommit=1', function(err, results) {
    if (!err) console.log(results);
  });
});

[dougwilson]

Yep, that would work, through just using the connection (https://github.com/mysqljs/mysql#connection) event should be good enough.