ER_TRUNCATED_WRONG_VALUE: Incorrect date value: '0' - NodeJS with correct DATE value

Question

I am trying to insert into a standard ISO date into MySQL database from nodejs and getting the following error:

ER_TRUNCATED_WRONG_VALUE: Incorrect date value: '0' for column 'lastmod' at row 1

All of the other questions on StackOverflow it seemed that the people weren't formatting their dates correctly.

If i run the query manually, it works after some modification - is it nodejs giving me a bad prepairation? or Am i sending the data in wrong some how?

First create the table:

    CREATE TABLE IF NOT EXISTS urls (
        id int(6) unsigned NOT NULL AUTO_INCREMENT,
        url varchar(255) NOT NULL,
        lastmod date NOT NULL,
        updatedAt timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
        createdAt timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
        PRIMARY KEY (url),
        UNIQUE KEY id (id)
    );

Then insert entries:

const mysql = require('mysql2')
const pool = mysql.createPool(settings);

async function upsert(entries){
    // entries = [{url, lastmod}, {url, lastmod}]

    const query = `
    INSERT INTO urls (
        url, lastmod )
        VALUES (?)
        ON DUPLICATE KEY UPDATE
        url=VALUES(url), 
        lastmod=VALUES(lastmod)
    `
    let q = pool.format(query, entries)
    console.log(q); 
    let result;
    try{
        result =  await pool.query(query, entries)
    } catch ( err ){
        console.log("Error in upsert", err)
    } finally {
        return result
    }
}

From the console.log, the insert statement will look like this:

 INSERT INTO urls (
        url,  lastmod
        )
        VALUES (
            `url` = 'https://test.com', 
            `lastmod` = '2020-12-30'
        )
        ON DUPLICATE KEY UPDATE 
        url=VALUES(url), 
        lastmod=VALUES(lastmod),

and produce the following error: ER_TRUNCATED_WRONG_VALUE: Incorrect date value: '0' for column 'lastmod' at row 1

The funny thing is if i remove the

`col`='value'

from my VALUES list and only make it 'value' instead of col='value', I can manually run the insert - why is node mysql preparing the values like this? Is there something i'm doing wrong?

Thank you!

Answer 1

I found the error was the VALUES (?) should be written as VALUES ?

Not exactly sure what the difference is but, solved my problem :-)