ER_UNKNOWN_STMT_HANDLER when executing a batch insert

[privatmamtora]

Trying to do a batch insert, but I keep hitting a ER_UNKNOWN_STMT_HANDLER error.

I managed to rewrite it using query, but I would like to figure out how to do it using batch

Here is the code I have:

result = await conn.batch("INSERT INTO `XYZ`.`TEST_questions` (`name`,`address`,`text`,`match`,`type`) VALUES (?,?,?,?,?)", [[ 'Four', 'add one', 'te `', 'Yes', 'No' ],[ 'Five', 'add one', 'te `', 'No', 'No' ]]);

Full Error:

(conn=1136, no: 1243, SQLState: HY000) Unknown prepared statement handler (0) given to mysqld_stmt_execute
sql: INSERT INTO `XYZ`.`TEST_questions` (`name`,`address`,`text`,`match`,`type`) VALUES (?,?,?,?,?) -
parameters:[['Four','add one','te `','Yes','No'],['Five','add one','te `','No','No']]

MariaDB Version: 10.3.13

[rusher]

Connector never send value 0 as statement id (= prepared statement handler), so this is more probably a server issue, but i cannot be sure without additional info. If reproductible, would you add debug: true to connection options, to see exactly what went wrong ?

Do you use galera ? I've seen a galera issue some time ago resulting with same kind of error.

[privatmamtora]

I turned on the debug. (It might be galera, not sure how to check)
But I think its pretty much the same info

==> conn:1788 BatchBulk(0,104)
+--------------------------------------------------+
|  0  1  2  3  4  5  6  7   8  9  a  b  c  d  e  f |
+--------------------------------------------------+------------------+
| 64 00 00 00 16 49 4E 53  45 52 54 20 49 4E 54 4F | d....INSERT INTO |
| 20 60 61 31 31 79 55 49  44 42 60 2E 60 54 45 53 |  `XYZ`.`TES |
| 54 5F 71 75 65 73 74 69  6F 6E 73 60 20 28 60 6E | T_questions` (`n |
| 61 6D 65 60 2C 60 61 64  64 72 65 73 73 60 2C 60 | ame`,`address`,` |
| 74 65 78 74 60 2C 60 6D  61 74 63 68 60 2C 60 74 | text`,`match`,`t |
| 79 70 65 60 29 20 56 41  4C 55 45 53 20 28 3F 2C | ype`) VALUES (?, |
| 3F 2C 3F 2C 3F 2C 3F 29                          | ?,?,?,?)         |
+--------------------------------------------------+------------------+
==> conn:1788 BatchBulk(0,80)
+--------------------------------------------------+
|  0  1  2  3  4  5  6  7   8  9  a  b  c  d  e  f |
+--------------------------------------------------+------------------+
| 4C 00 00 00 FA FF FF FF  FF 80 00 0F 00 0F 00 0F | L............... |
| 00 0F 00 0F 00 00 04 46  6F 75 72 00 07 61 64 64 | .......Four..add |
| 20 6F 6E 65 00 04 74 65  20 60 00 03 59 65 73 00 |  one..te `..Yes. |
| 02 4E 6F 00 04 46 69 76  65 00 07 61 64 64 20 6F | .No..Five..add o |
| 6E 65 00 04 74 65 20 60  00 02 4E 6F 00 02 4E 6F | ne..te `..No..No |
+--------------------------------------------------+------------------+
<== conn:1788 BatchBulk.readPrepareResultPacket (0,12)
+--------------------------------------------------+
|  0  1  2  3  4  5  6  7   8  9  a  b  c  d  e  f |
+--------------------------------------------------+------------------+
| 0C 00 00 01 00 01 00 00  00 00 00 05 00 00 00 00 | ................ |
+--------------------------------------------------+------------------+
<== conn:1788 BatchBulk.skipParameterPacket (0,23)
+--------------------------------------------------+
|  0  1  2  3  4  5  6  7   8  9  a  b  c  d  e  f |
+--------------------------------------------------+------------------+
| 17 00 00 02 03 64 65 66  00 00 00 01 3F 00 0C 3F | .....def....?..? |
| 00 00 00 00 00 06 80 00  00 00 00                | ...........      |
+--------------------------------------------------+------------------+
<== conn:1788 BatchBulk.skipParameterPacket (0,23)
+--------------------------------------------------+
|  0  1  2  3  4  5  6  7   8  9  a  b  c  d  e  f |
+--------------------------------------------------+------------------+
| 17 00 00 03 03 64 65 66  00 00 00 01 3F 00 0C 3F | .....def....?..? |
| 00 00 00 00 00 06 80 00  00 00 00                | ...........      |
+--------------------------------------------------+------------------+
<== conn:1788 BatchBulk.skipParameterPacket (0,23)
+--------------------------------------------------+
|  0  1  2  3  4  5  6  7   8  9  a  b  c  d  e  f |
+--------------------------------------------------+------------------+
| 17 00 00 04 03 64 65 66  00 00 00 01 3F 00 0C 3F | .....def....?..? |
| 00 00 00 00 00 06 80 00  00 00 00                | ...........      |
+--------------------------------------------------+------------------+
<== conn:1788 BatchBulk.skipParameterPacket (0,23)
+--------------------------------------------------+
|  0  1  2  3  4  5  6  7   8  9  a  b  c  d  e  f |
+--------------------------------------------------+------------------+
| 17 00 00 05 03 64 65 66  00 00 00 01 3F 00 0C 3F | .....def....?..? |
| 00 00 00 00 00 06 80 00  00 00 00                | ...........      |
+--------------------------------------------------+------------------+
<== conn:1788 BatchBulk.skipParameterPacket (0,23)
+--------------------------------------------------+
|  0  1  2  3  4  5  6  7   8  9  a  b  c  d  e  f |
+--------------------------------------------------+------------------+
| 17 00 00 06 03 64 65 66  00 00 00 01 3F 00 0C 3F | .....def....?..? |
| 00 00 00 00 00 06 80 00  00 00 00                | ...........      |
+--------------------------------------------------+------------------+
<== conn:1788 BatchBulk.skipEofPacket (0,5)
+--------------------------------------------------+
|  0  1  2  3  4  5  6  7   8  9  a  b  c  d  e  f |
+--------------------------------------------------+------------------+
| 05 00 00 07 FE 00 00 02  00                      | .........        |
+--------------------------------------------------+------------------+
<== conn:1788 BatchBulk.readResponsePacket (0,76)
+--------------------------------------------------+
|  0  1  2  3  4  5  6  7   8  9  a  b  c  d  e  f |
+--------------------------------------------------+------------------+
| 4C 00 00 01 FF DB 04 23  48 59 30 30 30 55 6E 6B | L......#HY000Unk |
| 6E 6F 77 6E 20 70 72 65  70 61 72 65 64 20 73 74 | nown prepared st |
| 61 74 65 6D 65 6E 74 20  68 61 6E 64 6C 65 72 20 | atement handler  |
| 28 30 29 20 67 69 76 65  6E 20 74 6F 20 6D 79 73 | (0) given to mys |
| 71 6C 64 5F 73 74 6D 74  5F 65 78 65 63 75 74 65 | qld_stmt_execute |
+--------------------------------------------------+------------------+
==> conn:1788 BatchBulk(0,9)
+--------------------------------------------------+
|  0  1  2  3  4  5  6  7   8  9  a  b  c  d  e  f |
+--------------------------------------------------+------------------+
| 05 00 00 00 19 01 00 00  00                      | .........        |
+--------------------------------------------------+------------------+
SqlError: (conn=1788, no: 1243, SQLState: HY000) Unknown prepared statement handler (0) given to mysqld_stmt_execute
sql: INSERT INTO `XYZ`.`TEST_questions` (`name`,`address`,`text`,`match`,`type`) VALUES (?,?,?,?,?) - parameters:[['Four','add one','te `','Yes','No'],['Five','add one','te `','No','No']]
    at Object.module.exports.createError (C:\Users\abc\Repos\API\node_modules\mariadb\lib\misc\errors.js:61:10)
    at PacketNodeEncoded.readError (C:\Users\abc\Repos\API\node_modules\mariadb\lib\io\packet.js:505:19)
    at BatchBulk.readResponsePacket (C:\Users\abc\Repos\API\node_modules\mariadb\lib\cmd\resultset.js:46:28)
    at PacketInputStream.receivePacketDebug (C:\Users\abc\Repos\API\node_modules\mariadb\lib\io\packet-input-stream.js:93:9)
    at PacketInputStream.onData (C:\Users\abc\Repos\API\node_modules\mariadb\lib\io\packet-input-stream.js:169:20)
    at Socket.emit (events.js:314:20)
    at Socket.EventEmitter.emit (domain.js:483:12)
    at addChunk (_stream_readable.js:297:12)
    at readableAddChunk (_stream_readable.js:272:9)
    at Socket.Readable.push (_stream_readable.js:213:10)
    at TCP.onStreamRead (internal/stream_base_commons.js:188:23) {
  text: 'Unknown prepared statement handler (0) given to mysqld_stmt_execute',
  sql: "INSERT INTO `XYZ`.`TEST_questions` (`name`,`address`,`text`,`match`,`type`) VALUES (?,?,?,?,?) - parameters:[['Four','add one','te `','Yes','No'],['Five','add one','te `','No','No']]",
  fatal: false,
  errno: 1243,
  sqlState: 'HY000',
  code: 'ER_UNKNOWN_STMT_HANDLER'
}
==> conn:1788 Reset(0,5)
+--------------------------------------------------+
|  0  1  2  3  4  5  6  7   8  9  a  b  c  d  e  f |
+--------------------------------------------------+------------------+
| 01 00 00 00 1F                                   | .....            |
+--------------------------------------------------+------------------+
<== conn:1788 Reset.readResetResponsePacket (0,7)
+--------------------------------------------------+
|  0  1  2  3  4  5  6  7   8  9  a  b  c  d  e  f |
+--------------------------------------------------+------------------+
| 07 00 00 01 00 00 00 02  00 00 00                | ...........      |
+--------------------------------------------------+------------------+

[rusher]

Thanks for debug report, that show me that something clearly is wrong server side.
You can identify if server is galera for example by executing command SHOW STATUS LIKE 'wsrep_cluster_size': if there is a record returned, then this is a galera cluster.

I've created https://jira.mariadb.org/browse/CONJS-174 for more investigation, to identify server issue.