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
– Nico Haase, Commented Jul 24, 2019 at 14:34
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
– RiggsFolly, Commented Jul 24, 2019 at 14:36
@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
– IgorM, Commented Jul 24, 2019 at 14:38
THe issue looks more like a node.js syntax issue rather than an SQL issue. — Mm-Art-In
– Mm-Art-In, Commented Jul 24, 2019 at 14:41
@IgorM please write an answer if you know what the answer was. Cheers. — Mm-Art-In
– Mm-Art-In, Commented Jul 24, 2019 at 16:28

IgorM · Accepted Answer · 2019-07-24 16:50:21Z

1

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]

answered Jul 24, 2019 at 16:50

IgorM

1,3413 gold badges12 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Andrey Sidorov Over a year ago

I'd recommend using object as single parameter or named placeholders. github.com/mysqljs/mysql#getting-the-id-of-an-inserted-row and github.com/sidorares/node-mysql2/blob/master/documentation/…

Collectives™ on Stack Overflow

Exception when running large INSERT query with mysql2

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related