SQL Error 1210 in batch update (when batch parameters buffer length becomes close to 16384 bytes ?)

[jfbrazeau]

Hi,

I develop a nodejs based application that uses a mariadb database. In my application I use a batch update that fails for a strange reason. I have isolated the problem in a very simple test case. The failure is sensitive to the data that is used, so I used my own business data that I obfuscated as much as I could (I mean as long as the bug remains reproducible).

Here is a trivial node project with one javascript file (index.js) that reproduces the failure : https://gitlab.com/jean-francois.brazeau/mariadb-batch-failure

There is one only javascript file that :

• initializes batch updates parameters (see BATCH_UPDATE_PARAMETERS variable)
• connects to mariadb
• creates the sample table that will be used for the test
• performs the batch update (raising a 1210 failure)

At the end of the script, the BATCH_UPDATE_PARAMETERS is changed by only appending two chars to the parameters. Once this very litte change is performed, the batch update becomes successful !

Along with the javascript file, I've put a start-mariadb.sh script that helps to start mariadb through docker.

If you checkout the code, you simply have to open a terminal, go into the module folder and perform :

• npm install
• ./start-mariadb.sh(and wait until mariadb is initialized)
• node index.js

The first batch update will fail with a 1210 error code (SqlError: (conn=3, no: 1210, SQLState: HY000) Incorrect arguments to mysqld_stmt_bulk_execute). The second will be successful.

Note : I use utf8mb4 charset.

[jfbrazeau]

Here is the console output :

[jfbrazeau]

Here is the code (index.js of https://gitlab.com/jean-francois.brazeau/mariadb-batch-failure) :

var mariadb = require('mariadb');

const BATCH_UPDATE_PARAMETERS = [
  [
    62470,
    'XXX',
    'XXX',
    'XXXXX',
    'XXX',
    `XXXXXXXXXXXXXXX:1596120310576XXXXXXXXXX:XXXXXXXXXXXXXXXXX:XXXXXXXXXXXX:XXXXXXXX:XXXXXXXXXXXXXXXXX:XXXXXXXXXXXXXXXXXXXXXXXXXXXX:X19/03/2019XXXXXXXXXXX:XXX-556-XXXXXXXXXXXXX:29665XXXXXXXX:X208XXXXXXXXX100XXXX&XXX;XXXXX5XXXXXXXXXXXXXXXXXXXXXX:XXXXXXXXXX:XXX3XXXXXXXX037550X}XXXXXXXXXXX:[]XXXXXXXXXXXXXXXX:XXXXXXXXXXXXXXXXXX:[XXXXX:X1X2:XX8XXXXX0X:XXXXXXXXXXXXX:XXXXXXXXXXXXXXXXXXX/XXXXXXX:XXXXXXXXXXXXXXXX:1596120310576}]XXXXXXXXXXXXXXXXXXXX:XXXXXXXXXXXXXXXXXXXXXXX:XXXXXXXXXXXXXXXX:XXXXXXXXXXXXXXXXXXXXXXXXXXXXX'XXXXXXXXXXXX:XXXXX'XXXXXX:4XXXXX°:6X}XXXXXXXXXXXX:[]XXXXXXXXXXXXXXX:[XXXXX:X1X2:XX8XXXXX0X:XXXXXXXXXXXXXXXXXXXXXX:X3:X6X2XXX911:XXXXXXXXX:XXXXXXXXXXXXX:XXXXXXXXXXXXXX(250XXXX)XXXXXXXXXXXXXX:XXXXXXXXXXX:XXXXXXXXXXXX:XXXXXXXXXX:100XXXXXXXXXXX:1.7XXXXXXXXXXXX:XXXXXXXXXXXXX:XXXXXXXXXXXXXXXXXX:X}XXXXXXXXXXXXX:[XXXXX:X1X2:XX8XXXXX0X:XXXXXXXXXXXXXXX:0XXXXXXXX:XXéXXXXXXXX'XXXXXXXXXXXXX(250XXXX)XXXXXXXXXXXX:1.7XXXXXXXXXX:XXXXXXXXXXXXXXXXXXXXXXX:XX8:X8XXX0X002:XXXXXXXXX:X}XXXXXXXXXXX:XXXXX}]XXXXXXXXXXXXXX:[]XXXXXXXXXXXXX:[]}XXXXXX:X1X2:XX8XXXXX01:XXXXXXXXXXXXXXXXXXXXXX:X35:X70XXXXX00:XXXXXXXXX:XXXXXXXXXXXXXXX:XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX:XXXXXXXXXXX:XXXXXXXXXXXX:XXXXXXXXXX:0XXXXXXXXXXX:0XXXXXXXXXXXX:XXXXXXXXXXXXX:XXXXXXXXXXXXXXXXXX:X}XXXXXXXXXXXXX:[XXXXX:X1X2:XX8XXXXX0X:XXXXXXXXXXXXXXX:0XXXXXXXX:XXXXXXXXXXXXXXXXéXXXXXXXXXXXXXXXXXXXXXXXXX:0XXXXXXXXXX:XXXXXXXXXXXXXXXXXXXXXXXXX:X35:X70XXXXX00:XXXXXXXXX:X}XXXXXXXXXXX:XXXXX}XXXXXX:X1X2:XX8XXXXX0X:XXXXXXXXXXXXXXX:1XXXXXXXX:XXXXXXXXXXXXXXéXXXXXXXXXXXXXXXXXXXXXXXXX:0XXXXXXXXXX:XXXXXXXXXXXXXXXXXXXXXXXXX:X3X:X71XXXXX00:XXXXXXXXX:X}XXXXXXXXXXX:XXXXX}XXXXXX:X1X2:XX8XXXXX0X:XXXXXXXXXXXXXXX:2XXXXXXXX:XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX:0XXXXXXXXXX:XXXXXXXXXXXXXXXXXXXXXXXXX:X3X:X71XX1XX00:XXXXXXXXX:X}XXXXXXXXXXX:XXXXX}XXXXXX:X1X2:XX8XXXXX10:XXXXXXXXXXXXXXX:3XXXXXXXX:XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX:0XXXXXXXXXX:XXXXXXXXXXXXXXXXXXXXXXXXX:XX8:X905XXX800:XXXXXXXXX:X}XXXXXXXXXXX:XXXXX}XXXXXX:X1X2:XX8XXXXX00:XXXXXXXXXXXXXXX:4XXXXXXXX:XXXXXXXXXXXXXXXXXXXXXX:0.1XXXXXXXXXX:XXXXXXXXXXXXXXXXXXXXXXX:XX8:XXXXXX7X00:XXXXXXXXX:X}XXXXXXXXXXX:XXXXX}]XXXXXXXXXXXXXX:[]XXXXXXXXXXXXX:[]}XXXXXX:X1X2:XX8XXXXX03:XXXXXXXXXXXXXXXXXXXXXX:X3:X6X2XXX919:XXXXXXXXX:XXXXXXXXXXXXX:XXXXXXXXXXXXXXXXXXXXX:XXXXXXXXXXX:XXXXXXXXXXXX:XXXXXXXXXX:20XXXXXXXXXXX:1XXXXXXXXXXXX:XXXXXXXXXXXXXX:XXXXXXXXXXXXXXXXXX:X}XXXXXXXXXXXXX:[XXXXX:X1X2:XX8XXXXX02:XXXXXXXXXXXXXXX:0XXXXXXXX:XXXXXXXXXXXXXXXXXXXXXXXXXXXXXX:0.25XXXXXXXXXX:XXXXXXXXXXXXXXXXXXXXXXXX:XX8:X905XXXX00:XXXXXXXXX:X}XXXXXXXXXXX:XXXXX}]XXXXXXXXXXXXXX:[]XXXXXXXXXXXXX:[]}XXXXXX:X1X2:XX8XXXXX05:XXXXXXXXXXXXXXXXXXXXXX:X35:X70XXX2100:XXXXXXXXX:XXXXXXXXXXXX:XXXXXXXXXXXXXèXX(X)XXXXXXXXXXXXXX:XXXXXXXXXXX:XXXXXXXXXXXX:XXXXXXXXXX:0XXXXXXXXXXX:0XXXXXXXXXXXX:XXXXXXXXXXXXXX:XXXXXXXXXXXXXXXXXX:X}XXXXXXXXXXXXX:[XXXXX:X1X2:XX8XXXXX04:XXXXXXXXXXXXXXX:0XXXXXXXX:XXXXXXXXXXXXXXXXXXXXXX:0XXXXXXXXXX:XXXXXXXXXXXXXXXXXXXXXX:X35:X70XXXXX00:XXXXXXXXX:X}XXXXXXXXXXX:XXXXX}]XXXXXXXXXXXXXX:[]XXXXXXXXXXXXX:[]}XXXXXX:X1X2:XX8XXXXX09:XXXXXXXXXXXXXXXXXXXXXX:X3:X6X2XXX90X:XXXXXXXXX:XXXXXXXXXXXXXX:XXXXXXôXXXXXXXXXéXXXXXXXXXXXXXX:XXXXXXXXXXX:XXXXXXXXXXXX:XXXXXXXXXX:0XXXXXXXXXXX:0.5XXXXXXXXXXXX:XXXXXXXXXXXXXX:XXXXXXXXXXXXXXXXXX:X}XXXXXXXXXXXXX:[XXXXX:X1X2:XX8XXXXX06:XXXXXXXXXXXXXXX:0XXXXXXXX:XXXXXXôXXXXXXXXXéXXXXXXXXXXXXXXXXXX:0.5XXXXXXXXXX:XXXXXXXXXXXXXXXXXXXXXXXX:XX8:X8XXX0X000:XXXXXXXXX:X}XXXXXXXXXXX:XXXXX}XXXXXX:X1X2:XX8XXXXX07:XXXXXXXXXXXXXXX:1XXXXXXXX:XXXXXXXXXXXXXXXXXXXXXXXXXXXXXéXXXXXXXXXXXXXXXX:0.15XXXXXXXXXX:XXXXXXXXXXXXXXXXXXXXXXXX:XX8:X8XXX0X001:XXXXXXXXX:X}XXXXXXXXXXX:XXXXX}XXXXXX:X1X2:XX8XXXXX08:XXXXXXXXXXXXXXX:2XXXXXXXX:XXXXXXXXXXXXXXXXXéXXXXXXXXXXXXXXXXXXXX'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXX:0.1XXXXXXXXXX:XXXXXXXXXXXXXXXXXXXXXXXX:XX8:XX4XXXX800:XXXXXXXXX:X}XXXXXXXXXXX:XXXXX}]XXXXXXXXXXXXXX:[]XXXXXXXXXXXXX:[]}XXXXXX:X1X2:XX8XXXXX0X:XXXXXXXXXXXXXXXXXXXXXX:X3:X6X2XXX913:XXXXXXXXX:XXXXXXXXXXXXXX:XXXXXXXXXXXXXXXXXXXXXXXXXXX:XXXXXXXXXXX:XXXXXXXXXXXX:XXXXXXXXXX:5XXXXXXXXXXX:0XXXXXXXXXXXX:XXXXXXXXXXXXXX:XXXXXXXXXXXXXXXXXX:X}XXXXXXXXXXXXX:[]XXXXXXXXXXXXXX:[]XXXXXXXXXXXXX:[]}XXXXXX:X1X2:XX8XXXXX0X:XXXXXXXXXXXXXXXXXXXXXX:X3:X6X2XXX91X:XXXXXXXXX:XXXXXXXXXXXXXX:XXXéXXXXXXXXXXXXXéXXXXXXXXXXXXXXXXXXXXXX:XXXXXXXXXXX:XXXXXXXXXXXX:XXXXXXXXXX:70XXXXXXXXXXX:1.3XXXXXXXXXXXX:XXXXXXXXXXXXXX:XXXXXXXXXXXXXXXXXX:X}XXXXXXXXXXXXX:[XXXXX:X1X2:XX8XXXXX0X:XXXXXXXXXXXXXXX:0XXXXXXXX:XXXXXXXXXXXXXéXXXXXXXXXXXXXéXXXXXXXXXXXXXXXXX:1.3XXXXXXXXXX:XXXXXXXXXXXXXXXXXXXXXXXX:XX8:X8XXX0X102:XXXXXXXXX:X}XXXXXXXXXXX:XXXXX}]XXXXXXXXXXXXXX:[]XXXXXXXXXXXXX:[]}XXXXXX:X1X2:XX8XXXXX0X:XXXXXXXXXXXXXXXXXXXXXX:X3:X6X2XXX90X:XXXXXXXXX:XXXXX1XXXXXXXXX:XXXXXXXXXXX(X/X)X:XXXXXXXX'X1XXXXXXXXXXXXXXXXX:XXXXXXXXXXX:XXXXXXXXXXXX:XXXXXXXXXX:0XXXXXXXXXXX:0XXXXXXXXXXXX:XXXXXXXXXXXXXX:XXXXXXXXXXXXXXXXXX:X}XXXXXXXXXXXXX:[XXXXX:X1X2:XX8XXXXX0X:XXXXXXXXXXXXXXX:0XXXXXXXX:XXXXXXXXXXXXXXXXXXXXXXXXXXXXX:0.2XXXXXXXXXX:XXXXXXXXXXXXXXXXXXXXXXX:XX8:X8XXXXXX00:XXXXXXXXX:X}XXXXXXXXXXX:XXXXX}]XXXXXXXXXXXXXX:[]XXXXXXXXXXXXX:[]}XXXXXX:X1X2:XX8XXXXX0X:XXXXXXXXXXXXXXXXXXXXXX:X3:X6X2XXX903:XXXXXXXXX:XXXXXXXXXXXXXXX:XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX:XXXXXXXXXXX:XXXXXXXXXX:XXXXXXXXÉXXXXXXXXXXX:XXXXXXXXXX:0XXXXXXXXXXX:0.1XXXXXXXXXXXX:XXXXXXXXXXXXXX:XXXXXXXXXXXXXXXXXX:X}XXXXXXXXXXXXX:[XXXXX:X1X2:XX8XXXXX0X:XXXXXXXXXXXXXXX:0XXXXXXXX:XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX:0.1XXXXXXXXXX:XXXXXXXXXXXXXXXXXXXXXXXX:XX8:X8XXXXXX0X:XXXXXXXXX:X}XXXXXXXXXXX:XXXXX}]XXXXXXXXXXXXXX:[]XXXXXXXXXXXXX:[XXXXX:X1X2:XX8XXXXX0X:XXXXXXXXXX:XXXXXXXXXXXXXXXXXXXXXXXX:XXXX}]}XXXXXX:X1X2:XX8XXXXX0X:XXXXXXXXXXXXXXXXXXXXXX:X3:X6X2XXX90X:XXXXXXXXX:XXXXXXXXXXXXXXXX:XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX:XXXXXXXXXXX:XXXXXXXXXXXX:XXXXXXXXXX:0XXXXXXXXXXX:0.1XXXXXXXXXXXX:XXXXXXXXXXXXXX:XXXXXXXXXXXXXXXXXX:X}XXXXXXXXXXXXX:[XXXXX:X1X2:XX8XXXXX0X:XXXXXXXXXXXXXXX:0XXXXXXXX:XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX:0.2XXXXXXXXXX:XXXXXXXXXXXXXXXXXXXXXXXX:XX8:X8XXXXXX0X:XXXXXXXXX:X}XXXXXXXXXXX:XXXXX}]XXXXXXXXXXXXXX:[]XXXXXXXXXXXXX:[]}XXXXXX:X1X2:XX8XXXXX0X:XXXXXXXXXXXXXXXXXXXXXX:XX8:XX62XXX900:XXXXXXXXX:XXXXXXXXXXXXXXXXX:XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX:XXXXXXXXXXX:XXXXXXXXXXXX:XXXXXXXXXX:0XXXXXXXXXXX:0.1XXXXXXXXXXXX:XXXXXXXXXXXXXX:XXXXXXXXXXXXXXXXXX:X}XXXXXXXXXXXXX:[XXXXX:X1X2:XX8XXXXX0X:XXXXXXXXXXXXXXX:0XXXXXXXX:XXéXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX:0.05XXXXXXXXXX:XXXXXXXXXXXXXXXXXXXXXXXX:XX8:XX62XXXX00:XXXXXXXXX:X}XXXXXXXXXXX:XXXXX}XXXXXX:X1X2:XX8XXXXX0X:XXXXXXXXXXXXXXX:1XXXXXXXX:XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX:0.05XXXXXXXXXX:XXXXXXXXXXXXXXXXXXXXXXXX:XX8:XX62X5XX00:XXXXXXXXX:X}XXXXXXXXXXX:XXXXX}]XXXXXXXXXXXXXX:[]XXXXXXXXXXXXX:[]}]XXXXXXXXXXX:XXXX-XXXXXXXXXXXXXX:XXXXXXXXXXXXX:X227052XXXXXXXXXXXXXXX:XXXXXXXXXXXXXXXXXX:X4502054397XXXXXXXXXX:X49646XXXXXXXXX:X0XXXXXXXXXXXXXXXXX:XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX:XXXXXXXXXXXXXX:XXXXXXXXXX(XX)X}XXXXXXXX:XXXXXXXXXXXXXX:XXXXXXXXXXX:XXXXXXXXXXXXX:XXXXXXXXXXXXXXXXXX:XXXXXXôXXXXXXXXXXXXXXXXXX:XXXX✓XXXX✗XXXX\\XXXXXXXXXXXXXé(X)XXXXXX:XXXX1XXXX2XXXXXXXXXXXXXXXXXéX:XXXX✓XXXX✗XXXX\\XXXXXXXX'XXXXXXXXXXXXXXXXX:XXXX✓XXXX✗XXXX\\XXXXXXXXX'XXXXXXXXXXXXXXX:XXXX✓XXXX✗XXXX\\XXXXXXXXàXXXXXXXXXXXXXXXXXX:XXXX✓XXXX✗XXXX\\XXXéXXXXXXXXXXXXXXXXXXXXXXXXXXX:XXXX✓XXXX✗XXXX\\XXXéXXXXXXXXXXXXXXXXèXXXXXXXXX:XXXX✓XXXX✗XXXX\\XXXXXXXXéXXXXXXôXXXXXXXXXXXXXXXXXXXX:XXXX✓XXXX✗XXXX\\XXXXXXXXX-XXXXXXXXXX(1)XXXXXXXXXXXXXXXXXXXX(2)XXXXX:XXXX(1)XXXX(2)XXXXXXXXXXXXXXXXXéX:XXXX✓XXXX✗XXXX\\X\\X(XX)XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX\\XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX\\XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX\\XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX\\XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX\\X\\XXéXXXXXXXXXXX(XXXXXXXXXXXXXXXXXX)X:X\\XXXX-XX\\X\\XXXéXXXXXXXàXXXXXXXXXXXXXXXXXXXXX:XXXXXXXXXXXXXXXX}}}`,
    1596120311320,
    'XXXX1',
    'XXXXXXXXXX',
    '1X2:XX8XXXXX0X:X',
    '1X2:XX8XXXXX00:X'
  ],
  [
    65398,
    'X8',
    'XXXXXX',
    'XXXXXX',
    'XXXXXXX',
    `XXXXXXXXXXXX:[XXXXX:XX8:XXXXXX4X00:XXXXXXXXXXXXXX:[XXXXX:XX8:XXXXXX4X01:XXXXXXXXXXXX:1596699901845}]XXXXXXXXXX:1596699901845}]XXXXXXXXXXXXXXX:[XXXXX:XX8:XXXX0XXX00:XXXXXXXXX:XXXXXXXXXXXXXXXXXXXXXXX:XXXXX:XXX:X84XX3XX00:XXXXXXXXXX:XXXXXXXXXXXXXXXXXX:[XXXXXX]XXXXXXXX:154XXXXXXXXXXX:XXXXXX}XXXXXXXXXX:XXXXXXXXXXXXXXXX:[]XXXXXXXXXXX:0.1XXXXXXXX:XXXXXXXXXXXXXXXXX'XXXXXXXXXXXXXXXXXXXXXXXXXXXX:X3:X6X2XXX412:XXXXXXXXXX:0XXXXXXXXX:XXXXXXXXXXXXXXXXXXXXXXX:XXXXXXXXXXXX:XXXXXXXXX:X}XXXXXXXXXXXXX:[XXXXX:XX8:XXXX0XXX01:XXXXXXXXXX:XXXXXXXXXXXXXXXXX'XXXXXXXXXXXXXXXXXXXXXXXXXX:XX8:X905XXX80X:XXXXXXXXXXXXXXX:0XXXXXXXXXXX:0.05XXXXXXXXXX:XXXXXXXXXXXXXXXXX:XXXXXXXXXXXX:X}}]XXXXXXXXXXXXX:[XXXXX:XX8:XXXX0XXX02:XXXXXXXXXX:XXXXXXX'XXXXXXXXXXXXXXXXXXXX:XX}]}XXXXXX:XX8:XXXX1XXX00:XXXXXXXXX:XXXX1XXXXXXXXXXXXXX:XXXXX:X2:X6X4787X01:XXXXXXXXXX:XXXXXXXXXXXXXXXX:[XXXXXX]XXXXXXXX:53XXXXXXXXXXX:XXXXXX}XXXXXXXXXX:XXXXXXXXXXXXXXXX:[XXXXX:XX8:XXXX2XX500:XXXXXXXXXXX:XXXXXXXXXXXXXXXXXX:XXXXXX-00.XXXX}]XXXXXXXXXXX:1XXXXXXXX:XXéXXXXXXXXXXXXXXXXXX(X1XXX)XXXXXXXXXXXXXXXXXXXXX:X3:X6X2XXX61X:XXXXXXXXXX:55XXXXXXXXX:XXXXXXXXXXXXXXXXXXXXXXX:XXXXXXXXXXXX:XXXXXXXXX:X}XXXXXXXXXXXXX:[XXXXX:XX8:XXXX1XXX01:XXXXXXXXXX:XXéXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX:XX8:X8XXX0XX0X:XXXXXXXXXXXXXXX:0XXXXXXXXXXX:0.7XXXXXXXXXX:XXXXXXXXXXXXXXXXX:XXXXXXXXXXXX:X}}]XXXXXXXXXXXXX:[]}XXXXXX:XX8:XXXX2XXX00:XXXXXXXXX:XXXXXXXXXXXXXXXXXXXXX:XXXXX:X2:X6X4787X01:XXXXXXXXXX:XXXXXXXXXXXXXXXX:[XXXXXX]XXXXXXXX:53XXXXXXXXXXX:XXXXXX}XXXXXXXXXX:XXXXXXXXXXXXXXXX:[]XXXXXXXXXXX:2.7XXXXXXXX:XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX:X3:X6X2XXX30X:XXXXXXXXXX:150XXXXXXXXX:XXXXXXXXXXXXXXXXXXXXXXX:XXXXXXXXXXXX:XXXXXXXXX:X}XXXXXXXXXXXXX:[XXXXX:XX8:XXXX2XXX01:XXXXXXXXXX:XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX:XX8:X8XXXXXX00:XXXXXXXXXXXXXXX:0XXXXXXXXXXX:1XXXXXXXXXX:XXXXXXXXXXXXXXXXX:XXXXXXXXXXXX:X}}XXXXXX:XX8:XXXX2XXX02:XXXXXXXXXX:XXXéXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX:XX8:X905XXX804:XXXXXXXXXXXXXXX:1XXXXXXXXXXX:1.5XXXXXXXXXX:XXXXXXXXXXXXXXXXX:XXXXXXXXXXXX:X}}]XXXXXXXXXXXXX:[XXXXX:XX8:XXXX2XX300:XXXXXXXXXX:XXXXXXXXXXXX(XXXX)XXXXXXXXXXXXX:XX}]}XXXXXX:XX8:XXXX3X9X00:XXXXXXXXX:XXXX1XXXXXXXXXXXXXX:XXXXX:X2:X6X4787X0X:XXXXXXXXXX:XXXXXXXXXXXXXXXXXXX:[XXXXXXX_XXX]XXXXXXXX:76XXXXXXXXXXX:XXXXXX}XXXXXXXXXX:XXXXXXXXXXXXXXXX:[XXXXX:XX8:XXXX3XXX00:XXXXXXXXXXX:XXXXXXXXXXXXXXXXXX:X15966969057241656938564876125626.XXXX}]XXXXXXXXXXX:1XXXXXXXX:XXéXXXXXXXXXXXXXXXXXX(X1XXX)XXXXXXXXXXXXXXXXXXXXX:X3:X6X2XXX61X:XXXXXXXXXX:55XXXXXXXXX:XXXXXXXXXXXXXXXXXXXXXXX:XXXXXXXXXXXX:XXXXXXXXX:X}XXXXXXXXXXXXX:[XXXXX:XX8:XXXX3X9X01:XXXXXXXXXX:XXéXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX:XX8:X8XXX0XX0X:XXXXXXXXXXXXXXX:0XXXXXXXXXXX:0.7XXXXXXXXXX:XXXXXXXXXXXXXXXXX:XXXXXXXXXXXX:X}}]XXXXXXXXXXXXX:[]}XXXXXX:XX8:XXXX3X9700:XXXXXXXXX:XXXXXXXXXXXXXXXXXXX:XXXXX:X2:X6X4787X0X:XXXXXXXXXX:XXXXXXXXXXXXXXXXXXX:[XXXXXXX_XXX]XXXXXXXX:76XXXXXXXXXXX:XXXXXX}XXXXXXXXXX:XXXXXXXXXXXXXXXX:[]XXXXXXXXXXX:2.7XXXXXXXX:XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX:X3:X6X2XXX41X:XXXXXXXXXX:150XXXXXXXXX:XXXXXXXXXXXXXXXXXXXXXXX:XXXXXXXXXXXX:XXXXXXXXX:X}XXXXXXXXXXXXX:[XXXXX:XX8:XXXX3X9701:XXXXXXXXXX:XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX:XX8:X8XXXXXX08:XXXXXXXXXXXXXXX:0XXXXXXXXXXX:1XXXXXXXXXX:XXXXXXXXXXXXXXXXX:XXXXXXXXXXXX:X}}XXXXXX:XX8:XXXX3X9702:XXXXXXXXXX:XXXéXXXXXXXXXXXXXXXXXXXXXXXXXXXXX:XX8:X905XXX902:XXXXXXXXXXXXXXX:1XXXXXXXXXXX:0.5XXXXXXXXXX:XXXXXXXXXXXXXXXXX:XXXXXXXXXXXX:X}}]XXXXXXXXXXXXX:[]}XXXXXX:XX8:XXXX4X7300:XXXXXXXXX:XXXX1XXXXXXXXXXXXXX:XXXXX:X2:X6X4787X04:XXXXXXXXXX:XXXXXXXXXXXXXXXXXXXX:[XXXXX_XXX]XXXXXXXX:95XXXXXXXXXXX:XXXXXX}XXXXXXXXXX:XXXXXXXXXXXXXXXX:[XXXXX:XX8:XXXX4XXX00:XXXXXXXXXXX:XXXXXXXXXXXXXXXXXX:X15966972589648649378334764430637.XXXX}]XXXXXXXXXXX:1XXXXXXXX:XXéXXXXXXXXXXXXXXXXXX(X1XXX)XXXXXXXXXXXXXXXXXXXXX:X3:X6X2XXX61X:XXXXXXXXXX:55XXXXXXXXX:XXXXXXXXXXXXXXXXXXXXXXX:XXXXXXXXXXXX:XXXXXXXXX:X}XXXXXXXXXXXXX:[XXXXX:XX8:XXXX4X7301:XXXXXXXXXX:XXéXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX:XX8:X8XXX0XX0X:XXXXXXXXXXXXXXX:0XXXXXXXXXXX:0.7XXXXXXXXXX:XXXXXXXXXXXXXXXXX:XXXXXXXXXXXX:X}}]XXXXXXXXXXXXX:[]}XXXXXX:XX8:XXXX4X4X00:XXXXXXXXX:XXXXXXXXXXXXXXXXXXX:XXXXX:X2:X6X4787X04:XXXXXXXXXX:XXXXXXXXXXXXXXXXXXXX:[XXXXX_XXX]XXXXXXXX:95XXXXXXXXXXX:XXXXXX}XXXXXXXXXX:XXXXXXXXXXXXXXXX:[]XXXXXXXXXXX:2.7XXXXXXXX:XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX:X3:X6X2XXX603:XXXXXXXXXX:150XXXXXXXXX:XXXXXXXXXXXXXXXXXXXXXXX:XXXXXXXXXXXX:XXXXXXXXX:X}XXXXXXXXXXXXX:[XXXXX:XX8:XXXX4X4X01:XXXXXXXXXX:XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX:XX8:X8XXXXXX05:XXXXXXXXXXXXXXX:0XXXXXXXXXXX:1.1XXXXXXXXXX:XXXXXXXXXXXXXXXXX:XXXXXXXXXXXX:X}}XXXXXX:XX8:XXXX4X4X02:XXXXXXXXXX:XXXéXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX:XX8:X905XXX90X:XXXXXXXXXXXXXXX:1XXXXXXXXXXX:0.6XXXXXXXXXX:XXXXXXXXXXXXXXXXX:XXXXXXXXXXXX:X}}]XXXXXXXXXXXXX:[]}XXXXXX:XX8:XXXX53XX00:XXXXXXXXX:XXXXXXXXXXXXXXXXXX:XXXXX:XX8:X875X91000:XXXXXXXXXX:XXXXX-XXXXXXXX(XXXXXXXXXXXXXX)XXXXXXXXXX:[XXXXXXX_XXXXXX_XX]XXXXXXXX:113XXXXXXXXXXX:XXXXXX}XXXXXXXXXX:XXXXXXXXXXXXXXXX:[]XXXXXXXXXXX:2.7XXXXXXXX:XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX:X3:X6X2XXX40X:XXXXXXXXXX:150XXXXXXXXX:XXXXXXXXXXXXXXXXXXXXXXX:XXXXXXXXXXXX:XXXXXXXXX:X}XXXXXXXXXXXXX:[XXXXX:XX8:XXXX53XX01:XXXXXXXXXX:XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX:XX8:X8XXXXXX06:XXXXXXXXXXXXXXX:0XXXXXXXXXXX:1XXXXXXXXXX:XXXXXXXXXXXXXXXXX:XXXXXXXXXXXX:X}}XXXXXX:XX8:XXXX53XX02:XXXXXXXXXX:XXXéXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX:XX8:X905XXX80X:XXXXXXXXXXXXXXX:1XXXXXXXXXXX:0.5XXXXXXXXXX:XXXXXXXXXXXXXXXXX:XXXXXXXXXXXX:X}}]XXXXXXXXXXXXX:[]}XXXXXX:XX8:XXXX580X00:XXXXXXXXX:XXXX1.5XXXXXXXXXXXXXX:XXXXX:XX8:X875X91000:XXXXXXXXXX:XXXXX-XXXXXXXX(XXXXXXXXXXXXXX)XXXXXXXXXX:[XXXXXXX_XXXXXX_XX]XXXXXXXX:113XXXXXXXXXXX:XXXXXX}XXXXXXXXXX:XXXXXXXXXXXXXXXX:[XXXXX:XX8:XXXX5XXX00:XXXXXXXXXXX:XXXXXXXXXXXXXXXXXX:X15966973749091020028521713094160.XXXX}XXXXXX:XX8:XXXX5XXX01:XXXXXXXXXXX:XXXXXXXXXXXXXXXXXX:X15966973839702765344779012373103.XXXX}]XXXXXXXXXXX:1.5XXXXXXXX:XXéXXXXXXXXXXXXXXXXXX(X1X5XXXX)XXXXXXXXXXXXXXXXXXXXX:X3X:X72XXX5200:XXXXXXXXXX:85XXXXXXXXX:XXXXXXXXXXXXXXXXXXXXXXX:XXXXXXXXXXXX:XXXXXXXXX:X}XXXXXXXXXXXXX:[XXXXX:XX8:XXXX580X00:XXXXXXXXXX:XXéXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX:XX8:X8XXX0X103:XXXXXXXXXXXXXXX:0XXXXXXXXXXX:1XXXXXXXXXX:XXXXXXXXXXXXXXXXX:XXXXXXXXXXXX:X}}]XXXXXXXXXXXXX:[]}XXXXXX:XX8:XXXX7XXX00:XXXXXXXXX:XXXXXXXXXXXXXXXXXXX:XXXXX:X2:X6X4X8XX00:XXXXXXXXXX:XXXXXXXXéXXXXXXXXXXXXXXX:[XXXXXXXXXXXXX_XXXXXXXXX_XXXXXXXXX_XXXXXXXXX_XXXXXXXXXXX_XXXXXXXXXXX_XXXXXXXXXXX_XXXXXXXXXXX_XXXXXXXXXXXXXXXXXXXXXXXX_XXXXXXXXXX_XXXXXXXXXX_XXXXXXXXXX_XXXXXXXXXX]XXXXXXXX:159XXXXXXXXXXX:XXXXXX}XXXXXXXXXX:XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX:[]XXXXXXXXXXX:0.3XXXXXXXX:XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX:X3:X6X2XXX51X:XXXXXXXXXX:15XXXXXXXXX:XXXXXXXXXXXXXXXXXXXXXXX:XXXXXXXXXXXX:XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX:X}XXXXXXXXXXXXX:[XXXXX:XX8:XXXX7XXX01:XXXXXXXXXX:XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX:XX8:X8XXX0XX02:XXXXXXXXXXXXXXX:0XXXXXXXXXXX:0.2XXXXXXXXXX:XXXXXXXXXXXXXXXXX:XXXXXXXXXXXX:X}}]XXXXXXXXXXXXX:[]}XXXXXX:XX8:XXXX983X00:XXXXXXXXX:XXXXXXXXXXXXXXXXXXX:XXXXX:X2:X6X4787X02:XXXXXXXXXX:XXXXXXXXXXXXXXX:[XXXXXX]XXXXXXXX:155XXXXXXXXXXX:XXXXXX}XXXXXXXXXX:XXXXXXXXXXXXXXXX:[XXXXX:XX8:XXXX9XX500:XXXXXXXXXXX:XXXXXXXXXXXXXXXXXX:XXXXXX-01.XXXX}XXXXXX:XX8:XXXX9XX501:XXXXXXXXXXX:XXXXXXXXXXXXXXXXXX:X15966974567405427192278950114191.XXXX}]XXXXXXXXXXX:1XXXXXXXX:XXéXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX:X3:X6X2XXX60X:XXXXXXXXXX:55XXXXXXXXX:XXXXXXXXXXXXXXXXXXXXXXX:XXXXXXXXXXXX:XXXXXXXX≤X2X5XXX\\XXXXX5XXXXXXXXXXXXXXXX:X}XXXXXXXXXXXXX:[XXXXX:XX8:XXXX983X01:XXXXXXXXXX:XXéXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX:XX8:X8XXX0XX07:XXXXXXXXXXXXXXX:0XXXXXXXXXXX:0.1XXXXXXXXXX:XXXXXXXXXXXXXXXX:XXXXXXXXXXXX:X}}]XXXXXXXXXXXXX:[]}]XXXXXXXX:XXXXXXXXXXXXXX:XXXXXXXX:XXXXXXôXXXXXXXXXXXXXXXXXX:XXXX✓XXXXX\\XXXXXXXXXXXXXé(X)XXXXXX:XXXX2XXXXXXXXXXXXXXXXXéX:XXXX✓XXXX\\XXXXXXXX'XXXXXXXXXXXXXXXXX:XXXX✓XX\\XXXXXXXXX'XXXXXXXXXXXXXXX:XXXX✓XXXX\\XXXXXXXXàXXXXXXXXXXXXXXXXXX:XXXX✓XXXX\\XXXéXXXXXXXXXXXXXXXXXXXXXXXXXXX:XXXXXXXXXX\\XXXéXXXXXXXXXXXXXXXXèXXXXXXXXX:XXXX✓XXXXXX\\XXXXXXXXéXXXXXXôXXXXXXXXXXXXXXXXXXXX:XXXX✓XXX(XXXXXXXéX:X03/2019)XXXX\\XXXXXXXXX-XXXXXXXXXX(1)XXXXXXXXXXXXXXXXXXXX(2)XXXXX:XXXXXXX(2)XXXXXXXXXXXXXXXXXéX:XXXX✓XXX\\X\\X(XX)XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX\\XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX\\XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX\\XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX\\XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX\\X\\XXéXXXXXXXXXXX(XXXXXXXXXXXXXXXXXX)X:X\\XXXX-XXXXXXXXXXXXXXXXXXXéXXXXXXXXXXXXXXXXXXXXXXXXX\\XXXX-XXXXXXXXXXXXXXXXXéXXXXXXXX(XXXXXXXXXXXXXé)\\X\\XXXéXXXXXXXàXXXXXXXXXXXXXXXXXXXXX:XXXXXXXXXXXXXXXX}}}`,
    1596699902742,
    'XXXX1',
    'XXXXXXXXXX',
    '1X2:XX8XXXXX0X:X',
    'j8:XX8XXXXX0X:X'
  ]
];

// Encapsulates the bacth update
async function runBatchUpdate(tx) {
  await tx.batch(
    'update MYTABLE set sequenceId=?, browserId=?, browserLabel=?, type=?, userLogin=?, payload=?, timestamp=? where companyId=? and siteId=? and entityId=? and id=?',
    BATCH_UPDATE_PARAMETERS
  );
}

(async () => {

  /**
   * Connect to mariadb.
   */
  const mariadbPool = mariadb.createPool({
    host: 'localhost',
    port: 3306,
    database: "mydatabase",
    user: "dbuser",
    password: 'secret',
    connectionLimit: 10,
    acquireTimeout: 5000
  });
  const tx = await mariadbPool.getConnection();

  /**
   * Create the test table.
   */
  // drop table
  await tx.query(`drop table if exists MYTABLE;`);
  // create table
  await tx.query(`create table MYTABLE (
    companyId    varchar(  50) not null,
    siteId       varchar(  50) not null,
    entityId     varchar(  50) not null,
    id           varchar(  50) not null,
    type         varchar(  10) not null,
    sequenceId   bigint unsigned not null,
    browserId    varchar(  50) not null,
    browserLabel varchar(  50) not null,
    userLogin    varchar(  50) not null,
    payload      text not null default ('{}'),
    timestamp    bigint unsigned not null,
    constraint MYTABLE_PK primary key (companyId, siteId, entityId, id)
  ) engine=innodb;
  `);

  /**
   * Performs a batch update with the given params : the update fails.
   */
  try {
    // batch update (even if the table is empty, the update shouldn't raise a 1210 error)
    await runBatchUpdate(tx);
    console.log("The batch update was supposed to fail...")
  }  
  catch (e) {
    console.error("** The batch update failed !", e);
  }

  /**
   * Simply append two chars in one parameter and rerun the batch update : now it works !
   */
  BATCH_UPDATE_PARAMETERS[0][1] = BATCH_UPDATE_PARAMETERS[0][1] + 'XX';
  await runBatchUpdate(tx);
  console.log("** The batch update is successful !")

  // Close the database connection
  await tx.end();
  await mariadbPool.end();
})();

[jfbrazeau]

Additional note : I show in this testcase that the fact of appending two chars to one of the parameters strings allows the query to become successful, but if I remove several characters, it also make the query become successful. I guess it could be a problem of charset/encoding management in mariadb driver ? Maybe a buffer overflow when dealing with UTF-8 characters ?

[jfbrazeau]

Additional note #2 : if I run the query in normal mode (not batch) with the given parameters (see BATCH_UPDATE_PARAMETERS in index.js), the queries are successful

[jfbrazeau]

I have managed to simplify my test case and I have isolated more precisely the problem. In my new test case, my table has only two columns :

create table MYTABLE (
    id           varchar(  50) not null,
    payload      text not null default ('{}'),
    constraint MYTABLE_PK primary key (id)
  ) engine=innodb;

I perform a batch update with the following SQL request : update MYTABLE set payload=? where id=? and two parameters (dataLength takes several values, as we will see after) :

[
  [
    ''.padStart(dataLength, 'X'), // generates a string with 'dataLength' length
    '1'
  ],
  [
    'X',
    '2'
  ]
]

According to dataLength value, the batch update is successful or the 1210 error is raised :

• if dataLength = 16365 : 👍 success
• if dataLength = 16366 : 💥 failure (SqlError: (conn=302, no: 1210, SQLState: HY000) Incorrect arguments to mysqld_stmt_bulk_execute)
• if dataLength = 16367 : 👍 success

16366 is very close to 16384 (=2^14)... Maybe is it a problem of memory allocation ? Maybe a check is performed to see if the whole data length is <= 16384 instead of a strict comparison (<) ?

The simplified version of the testcase is in the simplifiedversion branch of the git repository : https://gitlab.com/jean-francois.brazeau/mariadb-batch-failure/-/tree/simplifiedversion

[jfbrazeau]

Here is the simplified testcase (https://gitlab.com/jean-francois.brazeau/mariadb-batch-failure/-/blob/simplifiedversion/index.js) :

var mariadb = require('mariadb');

// Encapsulates the batch update
async function runBatchUpdate(tx, dataLength) {
  console.log(`Run query with ${dataLength} characters...`)
  try {
    await tx.batch(
      'update MYTABLE set payload=? where id=?',
      [
        [
          ''.padStart(dataLength, 'X'),
          '1'
        ],
        [
          'X',
          '2'
        ]
      ]
    );
    console.log("-> Successful")
  }
  catch (e) {
    console.error("-> Failure", e)
  }
}

(async () => {

  /**
   * Connect to mariadb.
   */
  const mariadbPool = mariadb.createPool({
    host: 'localhost',
    port: 3306,
    database: "mydatabase",
    user: "dbuser",
    password: 'secret',
    connectionLimit: 10,
    acquireTimeout: 5000
  });
  const tx = await mariadbPool.getConnection();

  /**
   * Create the test table.
   */
  // drop table
  await tx.query(`drop table if exists MYTABLE;`);
  // create table
  await tx.query(`create table MYTABLE (
    id           varchar(  50) not null,
    payload      text not null default ('{}'),
    constraint MYTABLE_PK primary key (id)
  ) engine=innodb;
  `);

  // attempt with 16365: successful
  await runBatchUpdate(tx, 16365);

  // attempt with 16366 : failure 
  await runBatchUpdate(tx, 16366);

  // attempt with 16367 characters : successful
  await runBatchUpdate(tx, 16367);

  // Close the database connection
  await tx.end();
  await mariadbPool.end();
})();

[jfbrazeau]

16366 is close to 16384, which is MEDIUM_BUFFER_SIZE in bulk-packet.jssource file.

Thanks to the other constants of bulk-packet.jssource file, I've found other failing values :

• failures close to LARGE_BUFFER_SIZE (131072) : 131053, 131055
• failures close to BIG_BUFFER_SIZE (1048576) : 1048557, 1048559
• failure close to MAX_BUFFER_SIZE (16777219) : 16777194

The bug is maybe in bulk-packet.js file ? Maybe is there a side effect with MEDIUM_BUFFER_SIZE / LARGE_BUFFER_SIZE / BIG_BUFFER_SIZE / MAX_BUFFER_SIZE ? (I must admit that it is not easy for me to figure out what could be wrong in this component ! 😊 )

[rusher]

Many thanks for the detailed issue description @jfbrazeau !!

Problem is identified and is now corrected (release will occur soon)

Issue was parameter length (that can be 0 up to 9 bytes) wasn't taken in account when growing the internal buffer. That is a very edge case, not easy to found without reproducting it.