felixge/node-mysql — Issue #152: Query queing makes auto_increment columns hard to use
Author: uberbrady
Created: 2011-12-07 23:33:45+00:00
State: open

In an environment where you aren't sure about what order your queries will execute, using MySQL's "LAST_INSERT_ID()" to determine which auto_increment value was used doesn't seem to work.

```js
#!/usr/local/bin/node
"use strict";

// Test table (you must create this):
// create table foo (id integer not null auto_increment primary key,val text);

var util = require('util');
var sys=require('sys');

var username='USERNAME',dbname='DBNAME';

var conn = new require("mysql").createClient({user: username,database: dbname});

conn.query("INSERT INTO foo (val) VALUES ('string1')",function (err,results,fields) {
conn.query("SELECT LAST_INSERT_ID()",function (err,results,fields) {
console.log("string1: ",results);
});
});

conn.query("INSERT INTO foo (val) VALUES ('second')",function (err,results,fields) {
conn.query("SELECT LAST_INSERT_ID()",function (err,results,fields) {
console.log("second: ",results);
});
});
```

From the above code, I would expect to see:

string1: [ { 'LAST_INSERT_ID()': 1 } ]
second: [ { 'LAST_INSERT_ID()': 2 } ]

Or maybe even

string1: [ { 'LAST_INSERT_ID()': 2 } ]
second: [ { 'LAST_INSERT_ID()': 1 } ]

But instead I get

string1: [ { 'LAST_INSERT_ID()': 2 } ]
second: [ { 'LAST_INSERT_ID()': 2 } ]

This makes using an auto_increment column in a web application difficult.

The only fix I can think of (since SQL statement queueing is a necessity) is to permit multiple statement execution.

This *may* be a documentation bug, if it will work when you chain the statements - if so, the documentation should be updated to state that.