Error: ER_OPERAND_COLUMNS: Operand should contain 1 column(s)

Question

My program creates a table like so :

exports.createTablesPhase2= function(tableprefix,cb){
  var s3 = 'CREATE TABLE IF NOT EXISTS productData (
           `id` int NOT NULL AUTO_INCREMENT,
           `url` varchar(255) NOT NULL,
           `baseSite` varchar(255) DEFAULT NULL,
           `img_url` varchar(255) DEFAULT NULL,
           `name` varchar(255) DEFAULT NULL,
           `price` varchar(15) NOT NULL,
           `sale_price` varchar(15) DEFAULT NULL,
           `description` text DEFAULT NULL,
           `extra` varchar(255) DEFAULT NULL,
           `brand` varchar(255) DEFAULT NULL,
           `colors` varchar(255) DEFAULT NULL,
           `sizes` varchar(255) DEFAULT NULL,
           `date` date NOT NULL ,
           PRIMARY KEY `id` (`id`),UNIQUE `url` (`url`));';
  client.query(s3, function(err, result) {
    if (err) throw err;
    cb();
  });
};

That bit works, I've just put it there so you see the structure.

Then after a while, the following function tries to insert values in the db :

exports.insertProducts = function(products,tableprefix,cb){
    var values = [];
    var date=(new Date()).toISOString().substring(0, 19).replace(/T.*/gi, '');
    for (var i = 0; i < products.length; i++) {
        var p = products[i];
        values.push(['null',p.url,p.baseSite,p.imgUrl,p.name,p.price,p.saleprice,p.description,p.moreInfo,p.brand,p.color,p.sizes,date]);
    }
    console.log(values);
    var sql = "INSERT INTO `productData` VALUES ? 
               ON DUPLICATE KEY UPDATE `price` =  VALUES (`price`),`sale_price` = VALUES (`sale_price`), `date` = VALUES (`date`)";
    client.query(sql,[values],function(err){
        if (err) throw err;
        cb();
    });
};

And I get the following error : Error: ER_OPERAND_COLUMNS: Operand should contain 1 column(s)

I have seen this before when running complex queries, but this one seems straightforward... I must be missing something really simple. I've checked the values and they all look ok.

EDIT : Following suggestions in comments, I have tried adding the column names but it doesn't change anything. I'll leave them out to not clog the code.

Answer 1

If you do a bulk insert with INSERT table VALUES ? syntax in node-mysql, make sure that array of arrays sent into .query() has all the values typed correspondingly - usually as primitives (strings or numbers).

In your case one of these elements was an array - and that was wrapped into parens by the node-mysql query builder, messing up VALUES line into something like...

VALUES(null, '123', (123, 456)...)

That'll make MySQL throw that pesky Operand should contain 1 column(s) error.