CODEWITHSUNDEEP
mysqlnode.jsmysql2
IgorM
IgorM

Reputation: 1356

Exception when running large INSERT query with mysql2

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

Upvotes: 2

Views: 1928

Answers (1)

IgorM
IgorM

Reputation: 1356

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]

Upvotes: 1

Related Questions