NodeJs & MySql: results.insertId always 0 when the table id is a UUID

Question

I'm inserting records to a MySql table using NodeJs mysql package, then trying to get the inserted record id by asking for results.insertId. But although the record is inserted correctly, the returned id is always zero.

The issue is probably that the table's Primary Key is a UUID (BINARY 16). If I change it to INT (11) the insertId returns the correct value. So is there a way around it? Can I get the insertId if it's a UUID?

This is my code:

pool.query('insert into my_table (id, title) '
                    + ' values(uuid(), ?', [ title ],
  function (error, results, fields) {
    if (error) {
      console.log(error);
      reject(error);
    } else {
      resolve(results);
    }
 }

The full results object:

OkPacket {
  fieldCount: 0,
  affectedRows: 1,
  insertId: 0,
  serverStatus: 2,
  warningCount: 0,
  message: '',
  protocol41: true,
  changedRows: 0 }

Answer 1

your id colum nis not AUTO_INCREMENT column. That is why you cannot use insertId

you may consider to generate uuid on code and insert it as parameter to your query.

or perform 2 separated queries:

SELECT UUID()
INSERT INTO my_table (id, title) VALUES ($uuid, ?)