Exception when running large INSERT query with mysql2

Question

I use mysql2 package to run queries against MySQL server

Here are the connection settings

getConnection: function () {


   let host = "hostname.com";
   let database = "database_name";
   let username = "username";
   let password = "pwd";


   let con = mysql.createConnection({
      host: host,
      user: username,
      password: password,
      database: database,
      connectTimeout: 15000
   });

   return con;
},

It works fine for all queries excepts INSERT query with 26 parameters:

    recordCreate: function (con, param1, param2,
       param3,  param4,  param5,  param6,  param7,
       param8,  param9,  param10, param11, param12,
       param13, param14, param15, param16, param17,
       param18, param19, param20, param21, param22,
       param23, param24, param25, param26, callback) {
       try {
         con.connect(function (err, result) {
                 try {
                      if (err) throw err;
                      con.query(“INSERT INTO tbldata (param1,
                           param2,  param3,  param4,  param5,  param6,
                           param7,  param8,  param9,  param10, param11,
                           param12, param13, param14, param15, param16,
                           param17, param18, param19, param20, param21,
                           param22, param23, param24, param25, param26
                   ) VALUES 
                   (?, ?, ?, ?, ?,
                    ?, ?, ?, ?, ?,
                    ?, ?, ?, ?, ?,
                    ?, ?, ?, ?, ?,
                    ?, ?, ?, ?, ?,
                    ?)”,[param1,  param2,  param3,  param4,  param5,
                         param6,  param7,  param8,  param9,  param10,
                         param11, param12, param13, param14, param15,
                         param16, param17, param18, param19, param20,
                         param21, param22, param23, param24, param25,
                         param26]
       , function (err) {
          try {
             if (err) throw err;
             callback(true);
          } catch (err) {
             callback(err);
          } finally {
             con.close();
          }
  });

I keep gettin the following error message:

C:\MyProjects\TMS\node_modules\mysql2\lib\commands\command.js:30 this.onResult(err); ^

TypeError: this.onResult is not a function at Query.execute (C:\MyProjects\TMS\node_modules\mysql2\lib\commands\command.js:30:14) at Connection.handlePacket (C:\MyProjects\TMS\node_modules\mysql2\lib\connection.js:449:32) at PacketParser.Connection.packetParser.p [as onPacket] (C:\MyProjects\TMS\node_modules\mysql2\lib\connection.js:72:12) at PacketParser.executeStart (C:\MyProjects\TMS\node_modules\mysql2\lib\packet_parser.js:76:16) at Socket.Connection.stream.on.data (C:\MyProjects\TMS\node_modules\mysql2\lib\connection.js:79:25) at Socket.emit (events.js:188:13) at addChunk (_stream_readable.js:288:12) at readableAddChunk (_stream_readable.js:269:11) at Socket.Readable.push (_stream_readable.js:224:10) at TCP.onStreamRead [as onread] (internal/stream_base_commons.js:145:17)

Process finished with exit code 1

It is important to state that those data elements (parameters) are really small. We talk here about short 10 chars strings and digits

For example

INSERT INTO tbldata (param1,  param2,  param3,  param4,  param5,
                     param6,  param7,  param8,  param9,  param10,
                     param11, param12, param13, param14, param15,
                     param16, param17, param18, param19, param20,
                     param21, param22, param23, param24, param25,
                     param26) VALUES 
                    (51, 'Test 51', '', '2019-12-01', '2019-12-01',
                      1, 1, 1, 3, 3, 1, 1, 0, 0, 0, 0, 0, 1, 1, 0, 
                      0, 1, 1, 1, 3, 3);

I tried to run the example query on the MySQL server and it works fine

Have you tried narrowing that query down to less parameters? Where does it start to go wrong? — Nico Haase
Means nothing to me TypeError: this.onResult is not a function at Query.execute but surley it should mean something to a node person — RiggsFolly
@NicoHaase, yes, but probably not enough. I removed two parameters. No success. I shall probably keep narrowing it. To be honest, I thought that this is a well-known issue with either mysql2 or MySQL server variables — IgorM
THe issue looks more like a node.js syntax issue rather than an SQL issue. — Martin
@IgorM please write an answer if you know what the answer was. Cheers. — Martin

IgorM IgorM · Accepted Answer · 2019-07-24T16:50:21

After narrowing the query I realized that it was a stupid syntax issue. I'd like to thank Nico Haase and Martin for the help with my "brainstorming".

I appreciate the mysql2 team's job. I also hope that they will address the issue with query method getting more than 2 parameters.

con.query("INSERT INTO tbldata (" +
                                "param1, " +
                                "param2, " +
                                "param3, " +
                                "param4, ", -- here is the syntax issue. Comma instead of plus
                                "param5) VALUES (?, ?, " +
                                "?, ?, " +
                                "?, ?, " +
                                "?)",
                                [param1,
                                    param2,
                                    param3,
                                    param4,
                                    param5]

Exception when running large INSERT query with mysql2

1 Answers