Error on bulk insert with execute()

[martinharder]

When I try to insert multiple rows with a bulk insert and use the execute() method I get the error:

Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '?' at line 1

It works, when I use the query() method.

This is the interesting part of my code:

const con = await getConnection()
await con.beginTransaction()

const values = [name, comments, levelId, constructionId, id]
const sql = "UPDATE structures SET name = ?, comments = ?, level = ?, construction_id = ? WHERE structure_id = ?;"
await con.execute(sql, values)

const sql2 = "INSERT IGNORE INTO structure_categories (structure_id, category_id) VALUES ?;"
const values2 = categoryIds.map(categoryId => {
    return [id, categoryId]
})
await con.execute(sql2, [values2])

await con.commit()
await con.end()

Seems like the replacement of the ? on bulk inserts doesn't work properly with the execute() method.

[sidorares]

your query ( including "?" symbols ) must be a valid input for PREPARE command, which is likely isn't

also number of placeholders should be exactly the same as number of parameters.
Few options here:

• just use .query() - that would interpolate parameters on the driver side and send long sql query with all the data in it
• dynamically insert a bunch of placeholders ( new Array(10).fill('?').join(',') ) to match the number of parameters. That way you'll have statement prepared each time number is different but reused for same number of parameters, and you also benefit from separate processing of query and data which is potentially safer from sql injection point of view
• create a stored procedure that accepts array as JSON object and can iterate all elements in it and insert data server side: conn.execute('CALL sp_bulk_insert(?)', [JSON.stringify(data)])

[sidorares]

see similar issues/questions in #958 #945 #141

[pihentagy]

Hi!

I have a similar issue, makes me really mad. I'd go with option1: just using a query.

table DDL

CREATE TABLE tmp_throughput_onair_4g_50
(
    id         INT AUTO_INCREMENT PRIMARY KEY,
    cell_id_4g INT      NOT NULL,
    eov        POINT    NOT NULL,
    throughput INT      NOT NULL,
    created_at DATETIME NULL,
    updated_at DATETIME NULL
) COLLATE = utf8mb4_unicode_ci;
CREATE INDEX tmp_throughput_onair_4g_50_cell_id_4g ON tmp_throughput_onair_4g_50 (cell_id_4g);
CREATE SPATIAL INDEX tmp_throughput_onair_4g_50_eov ON tmp_throughput_onair_4g_50 (eov);
CREATE INDEX tmp_throughput_onair_4g_50_throughput ON tmp_throughput_onair_4g_50 (throughput);

const promisePool = mysql.createPool(config).promisePool;
const sql = `insert into tmp_throughput_onair_4g_50 
(cell_id_4g,eov,throughput,created_At,updated_At)
values (?,POINT(?, ?),?,?,?)`
const rows = [
  [
    "93",
    "221325",
    "680975",
    "16",
    "2022-04-07T19:51:47.202Z",
    "2022-04-07T19:51:47.202Z"
  ],
  [
    "93",
    "221375",
    "680975",
    "14",
    "2022-04-07T19:51:47.203Z",
    "2022-04-07T19:51:47.203Z"
  ]
];
await promisePool.query(sql, rows)

and it throws:

{
  "message": "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' '680975', '14', '2022-04-07 19:51:47.203', '2022-04-07 19:51:47.203', ?),?,?,?)' at line 1",
  "code": "ER_PARSE_ERROR",
  "errno": 1064,
  "sql": "insert into tmp_throughput_onair_4g_50 (cell_id_4g,eov,throughput,created_At,updated_At) values ('93', '221325', '680975', '16', '2022-04-07 19:51:47.202', '2022-04-07 19:51:47.202',POINT('93', '221375', '680975', '14', '2022-04-07 19:51:47.203', '2022-04-07 19:51:47.203', ?),?,?,?)",
  "sqlState": "42000",
  "sqlMessage": "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' '680975', '14', '2022-04-07 19:51:47.203', '2022-04-07 19:51:47.203', ?),?,?,?)' at line 1"
}

What do I miss?

[sidorares]

What do I miss?

to me it looks that sql query after parameters interpolation still contains some ? placeholders, worth double checking that ?s match parameters

[pihentagy]

No. It seems that the whole first row (row[0]) is expanded by the first placeholder and don't know why. Six question mark, each row has 6 elements.

[sidorares]

it doesn't work like that. You have 2 elements array as parameters list, first goes as first ? and second as second ?. sqlstring library that both mysql2 and mysql use does not look into semantics of sql, it only sanitizes the input, see docs at https://github.com/mysqljs/sqlstring

[pihentagy]

So, is there a way to bulk insert say 10000 rows, which contain a POINT like I provided in the example?

I thought if you provide an array in the second arg of query, it will insert one row for one element in the array.

[pihentagy]

Maybe my question should go to #234

[sidorares]

@pihentagy have a look at this example:

class Point {
  constructor(x, y) {
    this.x = x;
    this.y = y;
  }

  toSqlString() {
    return `POINT(${this.x},${this.y})`;
  }
}

 const sql = /*sql*/ `
           insert into tmp_throughput_onair_4g_50 
              (cell_id_4g,eov,throughput,created_At,updated_At)
              values ?
        `;
    const rows = [
      [[
        '93',
        new Point(221325, 680975),
        '16',
        new Date('2022-04-07T19:51:47.202Z'),
        new Date('2022-04-07T19:51:47.202Z'),
      ],
      [
        '93',
        new Point(221375, 680975),
        '14',
        new Date('2022-04-07T19:51:47.203Z'),
        new Date('2022-04-07T19:51:47.203Z'),
      ]]
    ];
    await conn.query(sql, rows);

See https://github.com/mysqljs/sqlstring readme for more explanation:

• Nested arrays are turned into grouped lists (for bulk inserts), e.g. [['a', 'b'], ['c', 'd']] turns into ('a', 'b'), ('c', 'd')
• Objects that have a toSqlString method will have .toSqlString() called and the returned value is used as the raw SQL.

[sidorares]

also LOAD DATA INFILE might have slightly better performance as you send your data as csv. You can pass the content of the file with the query, doesn't have to be "real" file - see https://github.com/sidorares/node-mysql2/blob/master/documentation/Extras.md#sending-tabular-data-with-load-infile-and-local-stream

https://stackoverflow.com/questions/51673896/load-data-infile-and-spatial-data might be helpful