search

mariadb-corporation / mariadb-connector-nodejs

MariaDB Connector/Node.js is used to connect applications developed on Node.js to MariaDB and MySQL databases. MariaDB Connector/Node.js is LGPL licensed.
GNU Lesser General Public License v2.1
363 stars 93 forks source link

Insert fails with SQL Error with mariadb 3.2.3 on Linux. Same code works with 2.5.6 on Linux and works with 3.2.3 on Windows #274

Closed markddrake closed 2 months ago

markddrake commented 5 months ago

Given the following code

root@47a34bcff7c1:/usr/src/YADAMU# cat insertIssue3.js
"use strict"
import mariadb from 'mariadb';
import fs from 'fs';

async function main() {

    const connectionDetails = {
            host      : "MARIA11-01"
           ,user      : "root"
           ,password  : "oracle"
           ,port      : 3306
           ,database  : "mysql"
           ,multipleStatements: true
           ,typeCast:true
                   ,bigNumberStrings:true
                   ,dateStrings:true
                   ,rowsAsArray:true}

  let results;

  try {

    const pool = mariadb.createPool(connectionDetails);
    const conn = await pool.getConnection();
    results = await conn.query(`SET AUTOCOMMIT = 0, TIME_ZONE = '+00:00',SESSION INTERACTIVE_TIMEOUT = 600000, WAIT_TIMEOUT = 600000, SQL_MODE='ANSI_QUOTES,PAD_CHAR_TO_FULL_LENGTH', GROUP_CONCAT_MAX_LEN = 1024000, GLOBAL LOCAL_INFILE = 'ON';`);
    results = await conn.query(`drop schema if exists "YADAMU_TEST";`);
    results = await conn.query(`create schema "YADAMU_TEST";`);

    results = await conn.query(`USE "YADAMU_TEST"`);
    results = await conn.query(`create table  if not exists "YADAMU_TEST"."character_types"(
  "character_col" char(1) ,"character_varying_col" longtext ,"character_max_col" mediumtext ,"character_varying_max_col" mediumtext ,"character_4000_col" varchar(4000) ,"character_varying_4000_col" varchar(4000) ,"character_64k_col" text ,"character_varying_64k_col" text ,"text_col" longtext)`);

    const testData = fs.readFileSync('TEST_DATA.json');
        const data = JSON.parse(testData)
        console.log('ROWS',data.length)

        const sql = `insert into "YADAMU_TEST"."character_types" ("character_col","character_varying_col","character_max_col","character_varying_max_col","character_4000_col","character_varying_4000_col","character_64k_col","character_varying_64k_col","text_col") values  (?,?,?,?,?,?,?,?,?)`;

    try {

          results = await conn.beginTransaction()
          results = await conn.query(`SAVEPOINT YADAMU_INSERT; `)

      console.log(1)
      for (let row =0; row < data.length; row++) {
        const nextRow = data[row]
        const results = await conn.query(sql,nextRow);
            console.log(2)
          }
          console.log(3)
          results = await conn.commit()
    } catch (e) {
          console.log(4)
      console.log(e)
    }

   conn.end()
   pool.end();
 } catch(e) {
   console.log(e);
 }
}

main().then(() => {console.log("Success")}).catch((e) => {console.log(e)})

Running with node 3.2.3 on Linux results in

root@47a34bcff7c1:/usr/src/YADAMU# node -v
v21.6.1
root@47a34bcff7c1:/usr/src/YADAMU# npm ls
yadamu@1.0.0 /usr/src/YADAMU
+-- @aws-sdk/client-s3@3.507.0
+-- @aws-sdk/lib-storage@3.507.0
+-- @azure/storage-blob@12.17.0
+-- @electron/remote@2.1.2
+-- bootstrap-icons@1.11.3
+-- bootstrap@5.3.2
+-- cookie-parser@1.4.6
+-- csv-parser@3.0.0
+-- electron-packager@17.1.2
+-- electron@27.3.1
+-- express-session@1.18.0
+-- express@4.18.2
+-- font-awesome@4.7.0
+-- ibm_db_electron@npm:ibm_db@3.2.3
+-- ibm_db@3.2.3
+-- install@0.13.0
+-- jquery@3.7.1
+-- mariadb@3.2.3
+-- mime-types@2.1.35
+-- mongodb@6.3.0
+-- mssql@10.0.2
+-- mysql@2.18.1
+-- npm@10.4.0
+-- oracledb@6.3.0
+-- pg-copy-streams@6.0.6
+-- pg-query-stream@4.5.3
+-- pg@8.11.3
+-- readable-stream@4.5.2
+-- snowflake-sdk@1.9.3
+-- uuid@9.0.1
`-- wkx@0.5.0

root@47a34bcff7c1:/usr/src/YADAMU# node insertIssue3.js
ROWS 4
1
4
SqlError: (conn=20787, no: 1064, SQLState: 42000) You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'CD-ABCD-ABCD-ABCD-ABCD-ABCD-ABCD-ABCD-ABCD-ABCD-ABCD-ABCD-ABCD-ABCD-ABCD-ABCD...' at line 1
sql: insert into "YADAMU_TEST"."character_types" ("character_col","character_varying_col","character_max_col","character_varying_max_col","character_4000_col","character_varying_4000_col","character_64k_col","character_varying_64k_col","text_col") values  (?,?,...
    at module.exports.createError (/usr/src/YADAMU/node_modules/mariadb/lib/misc/errors.js:64:10)
    at PacketNodeEncoded.readError (/usr/src/YADAMU/node_modules/mariadb/lib/io/packet.js:589:19)
    at Query.readResponsePacket (/usr/src/YADAMU/node_modules/mariadb/lib/cmd/parser.js:62:28)
    at PacketInputStream.receivePacketBasic (/usr/src/YADAMU/node_modules/mariadb/lib/io/packet-input-stream.js:85:9)
    at PacketInputStream.onData (/usr/src/YADAMU/node_modules/mariadb/lib/io/packet-input-stream.js:135:20)
    at Socket.emit (node:events:519:28)
    at addChunk (node:internal/streams/readable:559:12)
    at readableAddChunkPushByteMode (node:internal/streams/readable:510:3)
    at Readable.push (node:internal/streams/readable:390:5)
    at TCP.onStreamRead (node:internal/stream_base_commons:190:23) {
  sqlMessage: "You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'CD-ABCD-ABCD-ABCD-ABCD-ABCD-ABCD-ABCD-ABCD-ABCD-ABCD-ABCD-ABCD-ABCD-ABCD-ABCD...' at line 1",
  sql: 'insert into "YADAMU_TEST"."character_types" ("character_col","character_varying_col","character_max_col","character_varying_max_col","character_4000_col","character_varying_4000_col","character_64k_col","character_varying_64k_col","text_col") values  (?,?,...',
  fatal: false,
  errno: 1064,
  sqlState: '42000',
  code: 'ER_PARSE_ERROR'
}
Success
root@47a34bcff7c1:/usr/src/YADAMU#

Reverting to 2.5.6 

root@47a34bcff7c1:/usr/src/YADAMU# npm install mariadb@2.5.6

added 2 packages, changed 1 package, and audited 895 packages in 736ms

112 packages are looking for funding
  run `npm fund` for details

found 0 vulnerabilities
root@47a34bcff7c1:/usr/src/YADAMU# node -v
v21.6.1
root@47a34bcff7c1:/usr/src/YADAMU# npm ls
yadamu@1.0.0 /usr/src/YADAMU
+-- @aws-sdk/client-s3@3.507.0
+-- @aws-sdk/lib-storage@3.507.0
+-- @azure/storage-blob@12.17.0
+-- @electron/remote@2.1.2
+-- bootstrap-icons@1.11.3
+-- bootstrap@5.3.2
+-- cookie-parser@1.4.6
+-- csv-parser@3.0.0
+-- electron-packager@17.1.2
+-- electron@27.3.1
+-- express-session@1.18.0
+-- express@4.18.2
+-- font-awesome@4.7.0
+-- ibm_db_electron@npm:ibm_db@3.2.3
+-- ibm_db@3.2.3
+-- install@0.13.0
+-- jquery@3.7.1
+-- mariadb@2.5.6
+-- mime-types@2.1.35
+-- mongodb@6.3.0
+-- mssql@10.0.2
+-- mysql@2.18.1
+-- npm@10.4.0
+-- oracledb@6.3.0
+-- pg-copy-streams@6.0.6
+-- pg-query-stream@4.5.3
+-- pg@8.11.3
+-- readable-stream@4.5.2
+-- snowflake-sdk@1.9.3
+-- uuid@9.0.1
`-- wkx@0.5.0

root@47a34bcff7c1:/usr/src/YADAMU# node insertIssue3.js
ROWS 4
1
2
2
2
2
3
Success
root@47a34bcff7c1:/usr/src/YADAMU#

Running on Windows.

C:\Development\YADAMU\src>node -v
v20.7.0

C:\Development\YADAMU\src>npm ls
yadamu@1.0.0 C:\Development\YADAMU\src
+-- @aws-sdk/client-s3@3.507.0
+-- @aws-sdk/lib-storage@3.507.0
+-- @azure/storage-blob@12.17.0
+-- @electron/remote@2.1.2
+-- bootstrap-icons@1.11.3
+-- bootstrap@5.3.2
+-- cookie-parser@1.4.6
+-- csv-parser@3.0.0
+-- electron-packager@17.1.2
+-- electron@27.3.1
+-- express-session@1.18.0
+-- express@4.18.2
+-- font-awesome@4.7.0
+-- ibm_db_electron@npm:ibm_db@3.2.3
+-- ibm_db@3.2.3
+-- install@0.13.0
+-- jquery@3.7.1
+-- mariadb@3.2.3
+-- mime-types@2.1.35
+-- mongodb@6.3.0
+-- mssql@10.0.2
+-- mysql@2.18.1
+-- npm@10.4.0
+-- oracledb@6.3.0
+-- pg-copy-streams@6.0.6
+-- pg-query-stream@4.5.3
+-- pg@8.11.3
+-- readable-stream@4.5.2
+-- snowflake-sdk@1.9.3
+-- uuid@9.0.1
`-- wkx@0.5.0

C:\Development\YADAMU\src>node scratch\mariadb\insertIssue3.js
ROWS 4
1
2
2
2
2
3
Success

C:\Development\YADAMU\src>
markddrake commented 5 months ago

Test Data TEST_DATA.zip

rusher commented 5 months ago

It seems similar to #273, i couldn't reproduced the issue either on windows or unix with the same node.js version. could you send aditional results like #273 (max_allowed_packet value and debug information) to permit the issue identification

rusher commented 5 months ago

in fact, reproduced. This randomly fails

markddrake commented 4 months ago

Does this fix all 3 of the issues I reported. When will this be available. Can I just pick up the changed file and drop it into my 3.2.3 installation ?

rusher commented 4 months ago

This solve your 3 issues and another one reported (#267) . Technically, you can replace the change from lib/io/packet-output-stream.js but since release will occur by the end of this week, i woud recommand to wait for official release

markddrake commented 4 months ago

Please ignore my post from yesterday, now deleted. It was, as I suspected, a configuration issue. Am re-testing now.

rusher commented 2 months ago

closing since this must have been corrected in 3.3.0. If that's not the case, feel free to create new issue related to this one