[MXS-3588] BULK pipelining error Created: 2021-06-02  Updated: 2021-06-18  Resolved: 2021-06-18

Status: Closed
Project: MariaDB MaxScale
Component/s: Core
Affects Version/s: 2.5.9, 2.5.12
Fix Version/s: 2.5.14

Type: Bug Priority: Critical
Reporter: Diego Dupin Assignee: markus makela
Resolution: Fixed Votes: 0
Labels: CONNECTOR_RELATED

Sprint: MXS-SPRINT-133

 Description   

When pipelining COM_STMT_BULK_EXECUTE, First one got ok, other return an "Unknown prepared statement handler (1) given to mysqld_stmt_execute".

example in protocol :

 
==> conn:547 Prepare(0,73)
+--------------------------------------------------+
|  0  1  2  3  4  5  6  7   8  9  a  b  c  d  e  f |
+--------------------------------------------------+------------------+
| 45 00 00 00 16 49 4E 53  45 52 54 20 49 4E 54 4F | E....INSERT INTO |
| 20 60 62 69 67 42 61 74  63 68 57 69 74 68 31 36 |  `bigBatchWith16 |
| 6D 4D 61 78 41 6C 6C 6F  77 65 64 50 61 63 6B 65 | mMaxAllowedPacke |
| 74 60 20 76 61 6C 75 65  73 20 28 31 2C 20 3F 2C | t` values (1, ?, |
| 20 32 2C 20 3F 2C 20 33  29                      |  2, ?, 3)        |
+--------------------------------------------------+------------------+
 
<== conn:547 Prepare.readPrepareResultPacket (0,12)
+--------------------------------------------------+
|  0  1  2  3  4  5  6  7   8  9  a  b  c  d  e  f |
+--------------------------------------------------+------------------+
| 0C 00 00 01 00 08 00 00  00 00 00 02 00 00 00 00 | ................ |
+--------------------------------------------------+------------------+
 
<== conn:547 Prepare.readPrepareParameterPacket (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:547 Prepare.readPrepareParameterPacket (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:547 Prepare.skipEofPacket (0,5)
+--------------------------------------------------+
|  0  1  2  3  4  5  6  7   8  9  a  b  c  d  e  f |
+--------------------------------------------------+------------------+
| 05 00 00 04 FE 00 00 03  00                      | .........        |
+--------------------------------------------------+------------------+
 
==> conn:547 BatchBulk(0,16777215)
+--------------------------------------------------+
|  0  1  2  3  4  5  6  7   8  9  a  b  c  d  e  f |
+--------------------------------------------------+------------------+
| FB FF FF 00 FA 08 00 00  00 80 00 03 00 FD 00 00 | ................ |
| 00 00 00 00 00 21 61 62  63 64 65 66 67 68 69 6A | .....!abcdefghij |
| 6B 66 6C 6D 6E 27 6F 70  71 72 74 75 76 77 78 F0 | kflmn'opqrtuvwx. |
| 9F A4 98 F0 9F 92 AA 00  01 00 00 00 00 21 61 62 | .............!ab |
| 63 64 65 66 67 68 69 6A  6B 66 6C 6D 6E 27 6F 70 | cdefghijkflmn'op |
| 71 72 74 75 76 77 78 F0  9F A4 98 F0 9F 92 AA 00 | qrtuvwx......... |
| 02 00 00 00 00 21 61 62  63 64 65 66 67 68 69 6A | .....!abcdefghij |
| 6B 66 6C 6D 6E 27 6F 70  71 72 74 75 76 77 78 F0 | kflmn'opqrtuvwx. |
| 9F A4 98 F0 9F 92 AA 00  03 00 00 00 00 21 61 62 | .............!ab |
| 63 64 65 66 67 68 69 6A  6B 66 6C 6D 6E 27 6F 70 | cdefghijkflmn'op |
| 71 72 74 75 76 77 78 F0  9F A4 98 F0 9F 92 AA 00 | qrtuvwx......... |
| 04 00 00 00 00 21 61 62  63 64 65 66 67 68 69 6A | .....!abcdefghij |
| 6B 66 6C 6D 6E 27 6F 70  71 72 74 75 76 77 78 F0 | kflmn'opqrtuvwx. |
| 9F A4 98 F0 9F 92 AA 00  05 00 00 00 00 21 61 62 | .............!ab |
| 63 64 65 66 67 68 69 6A  6B 66 6C 6D 6E 27 6F 70 | cdefghijkflmn'op |
| 71 72 74 75 76 77 78 F0  9F A4 98 F0 9F 92 AA 00 | qrtuvwx......... |...
+--------------------------------------------------+------------------+
 
==> conn:547 BatchBulk(0,16777215)
+--------------------------------------------------+
|  0  1  2  3  4  5  6  7   8  9  a  b  c  d  e  f |
+--------------------------------------------------+------------------+
| FB FF FF 00 FA 08 00 00  00 80 00 03 00 FD 00 00 | ................ |
| 66 66 06 00 00 21 61 62  63 64 65 66 67 68 69 6A | ff...!abcdefghij |
| 6B 66 6C 6D 6E 27 6F 70  71 72 74 75 76 77 78 F0 | kflmn'opqrtuvwx. |
| 9F A4 98 F0 9F 92 AA 00  67 66 06 00 00 21 61 62 | ........gf...!ab |
| 63 64 65 66 67 68 69 6A  6B 66 6C 6D 6E 27 6F 70 | cdefghijkflmn'op |
| 71 72 74 75 76 77 78 F0  9F A4 98 F0 9F 92 AA 00 | qrtuvwx......... |
| 68 66 06 00 00 21 61 62  63 64 65 66 67 68 69 6A | hf...!abcdefghij |
| 6B 66 6C 6D 6E 27 6F 70  71 72 74 75 76 77 78 F0 | kflmn'opqrtuvwx. |
| 9F A4 98 F0 9F 92 AA 00  69 66 06 00 00 21 61 62 | ........if...!ab |
| 63 64 65 66 67 68 69 6A  6B 66 6C 6D 6E 27 6F 70 | cdefghijkflmn'op |
| 71 72 74 75 76 77 78 F0  9F A4 98 F0 9F 92 AA 00 | qrtuvwx......... |
| 6A 66 06 00 00 21 61 62  63 64 65 66 67 68 69 6A | jf...!abcdefghij |
| 6B 66 6C 6D 6E 27 6F 70  71 72 74 75 76 77 78 F0 | kflmn'opqrtuvwx. |
| 9F A4 98 F0 9F 92 AA 00  6B 66 06 00 00 21 61 62 | ........kf...!ab |
| 63 64 65 66 67 68 69 6A  6B 66 6C 6D 6E 27 6F 70 | cdefghijkflmn'op |
| 71 72 74 75 76 77 78 F0  9F A4 98 F0 9F 92 AA 00 | qrtuvwx......... |...
+--------------------------------------------------+------------------+
 
==> conn:547 BatchBulk(0,6445615)
+--------------------------------------------------+
|  0  1  2  3  4  5  6  7   8  9  a  b  c  d  e  f |
+--------------------------------------------------+------------------+
| 2B 5A 62 00 FA 08 00 00  00 80 00 03 00 FD 00 00 | +Zb............. |
| CC CC 0C 00 00 21 61 62  63 64 65 66 67 68 69 6A | .....!abcdefghij |
| 6B 66 6C 6D 6E 27 6F 70  71 72 74 75 76 77 78 F0 | kflmn'opqrtuvwx. |
| 9F A4 98 F0 9F 92 AA 00  CD CC 0C 00 00 21 61 62 | .............!ab |
| 63 64 65 66 67 68 69 6A  6B 66 6C 6D 6E 27 6F 70 | cdefghijkflmn'op |
| 71 72 74 75 76 77 78 F0  9F A4 98 F0 9F 92 AA 00 | qrtuvwx......... |
| CE CC 0C 00 00 21 61 62  63 64 65 66 67 68 69 6A | .....!abcdefghij |
| 6B 66 6C 6D 6E 27 6F 70  71 72 74 75 76 77 78 F0 | kflmn'opqrtuvwx. |
| 9F A4 98 F0 9F 92 AA 00  CF CC 0C 00 00 21 61 62 | .............!ab |
| 63 64 65 66 67 68 69 6A  6B 66 6C 6D 6E 27 6F 70 | cdefghijkflmn'op |
| 71 72 74 75 76 77 78 F0  9F A4 98 F0 9F 92 AA 00 | qrtuvwx......... |
| D0 CC 0C 00 00 21 61 62  63 64 65 66 67 68 69 6A | .....!abcdefghij |
| 6B 66 6C 6D 6E 27 6F 70  71 72 74 75 76 77 78 F0 | kflmn'opqrtuvwx. |
| 9F A4 98 F0 9F 92 AA 00  D1 CC 0C 00 00 21 61 62 | .............!ab |
| 63 64 65 66 67 68 69 6A  6B 66 6C 6D 6E 27 6F 70 | cdefghijkflmn'op |
| 71 72 74 75 76 77 78 F0  9F A4 98 F0 9F 92 AA 00 | qrtuvwx......... |...
+--------------------------------------------------+------------------+
 
<== conn:547 BatchBulk.readResponsePacket (0,54)
+--------------------------------------------------+
|  0  1  2  3  4  5  6  7   8  9  a  b  c  d  e  f |
+--------------------------------------------------+------------------+
| 36 00 00 01 00 FD 66 66  06 00 03 00 00 00 2B 52 | 6.....ff......+R |
| 65 63 6F 72 64 73 3A 20  34 31 39 34 33 30 20 20 | ecords: 419430   |
| 44 75 70 6C 69 63 61 74  65 73 3A 20 30 20 20 57 | Duplicates: 0  W |
| 61 72 6E 69 6E 67 73 3A  20 30                   | arnings: 0       |
+--------------------------------------------------+------------------+
 
<== conn:547 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 31 29 20 67 69 76 65  6E 20 74 6F 20 6D 79 73 | (1) given to mys |
| 71 6C 64 5F 73 74 6D 74  5F 65 78 65 63 75 74 65 | qld_stmt_execute |
+--------------------------------------------------+------------------+
 
<== conn:547 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 31 29 20 67 69 76 65  6E 20 74 6F 20 6D 79 73 | (1) given to mys |
| 71 6C 64 5F 73 74 6D 74  5F 65 78 65 63 75 74 65 | qld_stmt_execute |
+--------------------------------------------------+------------------+

I'll add an example on how to reproduced it soon



 Comments   
Comment by markus makela [ 2021-06-02 ]

Can you check if this happens with the latest release?

Comment by Diego Dupin [ 2021-06-02 ]

i will test with 2.5.12

Comment by Diego Dupin [ 2021-06-02 ]

This is still the case with 2.5.12.
I've been pipelining other bulk command without issue.
So this is more complex than initial description.
One strange thing is that error message is always "Unknown prepared statement handler (1) given to mysqld_stmt_execute" whatever the statement Id (see initial example where statement id was "08 00 00 00")

Comment by Diego Dupin [ 2021-06-04 ]

tested with 2.4.17, and problem occurs, even if different :
sending COM_STMT_PREPARE + 3 COM_STMT_BULK_EXECUTE, but receive only 2 answer, so driver hang forever waiting for 3rd answer

==> conn:13 Prepare(0,73)
+--------------------------------------------------+
|  0  1  2  3  4  5  6  7   8  9  a  b  c  d  e  f |
+--------------------------------------------------+------------------+
| 45 00 00 00 16 49 4E 53  45 52 54 20 49 4E 54 4F | E....INSERT INTO |
| 20 60 62 69 67 42 61 74  63 68 57 69 74 68 31 36 |  `bigBatchWith16 |
| 6D 4D 61 78 41 6C 6C 6F  77 65 64 50 61 63 6B 65 | mMaxAllowedPacke |
| 74 60 20 76 61 6C 75 65  73 20 28 31 2C 20 3F 2C | t` values (1, ?, |
| 20 32 2C 20 3F 2C 20 33  29                      |  2, ?, 3)        |
+--------------------------------------------------+------------------+
 
<== conn:13 Prepare.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 02 00 00 00 00 | ................ |
+--------------------------------------------------+------------------+
 
<== conn:13 Prepare.readPrepareParameterPacket (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:13 Prepare.readPrepareParameterPacket (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:13 Prepare.skipEofPacket (0,5)
+--------------------------------------------------+
|  0  1  2  3  4  5  6  7   8  9  a  b  c  d  e  f |
+--------------------------------------------------+------------------+
| 05 00 00 04 FE 00 00 03  00                      | .........        |
+--------------------------------------------------+------------------+
 
==> conn:13 BatchBulk(0,16777215)
+--------------------------------------------------+
|  0  1  2  3  4  5  6  7   8  9  a  b  c  d  e  f |
+--------------------------------------------------+------------------+
| FB FF FF 00 FA 01 00 00  00 80 00 03 00 FD 00 00 | ................ |
| 00 00 00 00 00 21 61 62  63 64 65 66 67 68 69 6A | .....!abcdefghij |
| 6B 66 6C 6D 6E 27 6F 70  71 72 74 75 76 77 78 F0 | kflmn'opqrtuvwx. |
| 9F A4 98 F0 9F 92 AA 00  01 00 00 00 00 21 61 62 | .............!ab |
| 63 64 65 66 67 68 69 6A  6B 66 6C 6D 6E 27 6F 70 | cdefghijkflmn'op |
| 71 72 74 75 76 77 78 F0  9F A4 98 F0 9F 92 AA 00 | qrtuvwx......... |
| 02 00 00 00 00 21 61 62  63 64 65 66 67 68 69 6A | .....!abcdefghij |
| 6B 66 6C 6D 6E 27 6F 70  71 72 74 75 76 77 78 F0 | kflmn'opqrtuvwx. |
| 9F A4 98 F0 9F 92 AA 00  03 00 00 00 00 21 61 62 | .............!ab |
| 63 64 65 66 67 68 69 6A  6B 66 6C 6D 6E 27 6F 70 | cdefghijkflmn'op |
| 71 72 74 75 76 77 78 F0  9F A4 98 F0 9F 92 AA 00 | qrtuvwx......... |
| 04 00 00 00 00 21 61 62  63 64 65 66 67 68 69 6A | .....!abcdefghij |
| 6B 66 6C 6D 6E 27 6F 70  71 72 74 75 76 77 78 F0 | kflmn'opqrtuvwx. |
| 9F A4 98 F0 9F 92 AA 00  05 00 00 00 00 21 61 62 | .............!ab |
| 63 64 65 66 67 68 69 6A  6B 66 6C 6D 6E 27 6F 70 | cdefghijkflmn'op |
| 71 72 74 75 76 77 78 F0  9F A4 98 F0 9F 92 AA 00 | qrtuvwx......... |...
+--------------------------------------------------+------------------+
 
==> conn:13 BatchBulk(0,16777215)
+--------------------------------------------------+
|  0  1  2  3  4  5  6  7   8  9  a  b  c  d  e  f |
+--------------------------------------------------+------------------+
| FB FF FF 00 FA 01 00 00  00 80 00 03 00 FD 00 00 | ................ |
| 66 66 06 00 00 21 61 62  63 64 65 66 67 68 69 6A | ff...!abcdefghij |
| 6B 66 6C 6D 6E 27 6F 70  71 72 74 75 76 77 78 F0 | kflmn'opqrtuvwx. |
| 9F A4 98 F0 9F 92 AA 00  67 66 06 00 00 21 61 62 | ........gf...!ab |
| 63 64 65 66 67 68 69 6A  6B 66 6C 6D 6E 27 6F 70 | cdefghijkflmn'op |
| 71 72 74 75 76 77 78 F0  9F A4 98 F0 9F 92 AA 00 | qrtuvwx......... |
| 68 66 06 00 00 21 61 62  63 64 65 66 67 68 69 6A | hf...!abcdefghij |
| 6B 66 6C 6D 6E 27 6F 70  71 72 74 75 76 77 78 F0 | kflmn'opqrtuvwx. |
| 9F A4 98 F0 9F 92 AA 00  69 66 06 00 00 21 61 62 | ........if...!ab |
| 63 64 65 66 67 68 69 6A  6B 66 6C 6D 6E 27 6F 70 | cdefghijkflmn'op |
| 71 72 74 75 76 77 78 F0  9F A4 98 F0 9F 92 AA 00 | qrtuvwx......... |
| 6A 66 06 00 00 21 61 62  63 64 65 66 67 68 69 6A | jf...!abcdefghij |
| 6B 66 6C 6D 6E 27 6F 70  71 72 74 75 76 77 78 F0 | kflmn'opqrtuvwx. |
| 9F A4 98 F0 9F 92 AA 00  6B 66 06 00 00 21 61 62 | ........kf...!ab |
| 63 64 65 66 67 68 69 6A  6B 66 6C 6D 6E 27 6F 70 | cdefghijkflmn'op |
| 71 72 74 75 76 77 78 F0  9F A4 98 F0 9F 92 AA 00 | qrtuvwx......... |...
+--------------------------------------------------+------------------+
 
==> conn:13 BatchBulk(0,6445615)
+--------------------------------------------------+
|  0  1  2  3  4  5  6  7   8  9  a  b  c  d  e  f |
+--------------------------------------------------+------------------+
| 2B 5A 62 00 FA 01 00 00  00 80 00 03 00 FD 00 00 | +Zb............. |
| CC CC 0C 00 00 21 61 62  63 64 65 66 67 68 69 6A | .....!abcdefghij |
| 6B 66 6C 6D 6E 27 6F 70  71 72 74 75 76 77 78 F0 | kflmn'opqrtuvwx. |
| 9F A4 98 F0 9F 92 AA 00  CD CC 0C 00 00 21 61 62 | .............!ab |
| 63 64 65 66 67 68 69 6A  6B 66 6C 6D 6E 27 6F 70 | cdefghijkflmn'op |
| 71 72 74 75 76 77 78 F0  9F A4 98 F0 9F 92 AA 00 | qrtuvwx......... |
| CE CC 0C 00 00 21 61 62  63 64 65 66 67 68 69 6A | .....!abcdefghij |
| 6B 66 6C 6D 6E 27 6F 70  71 72 74 75 76 77 78 F0 | kflmn'opqrtuvwx. |
| 9F A4 98 F0 9F 92 AA 00  CF CC 0C 00 00 21 61 62 | .............!ab |
| 63 64 65 66 67 68 69 6A  6B 66 6C 6D 6E 27 6F 70 | cdefghijkflmn'op |
| 71 72 74 75 76 77 78 F0  9F A4 98 F0 9F 92 AA 00 | qrtuvwx......... |
| D0 CC 0C 00 00 21 61 62  63 64 65 66 67 68 69 6A | .....!abcdefghij |
| 6B 66 6C 6D 6E 27 6F 70  71 72 74 75 76 77 78 F0 | kflmn'opqrtuvwx. |
| 9F A4 98 F0 9F 92 AA 00  D1 CC 0C 00 00 21 61 62 | .............!ab |
| 63 64 65 66 67 68 69 6A  6B 66 6C 6D 6E 27 6F 70 | cdefghijkflmn'op |
| 71 72 74 75 76 77 78 F0  9F A4 98 F0 9F 92 AA 00 | qrtuvwx......... |...
+--------------------------------------------------+------------------+
 
<== conn:13 BatchBulk.readResponsePacket (0,54)
+--------------------------------------------------+
|  0  1  2  3  4  5  6  7   8  9  a  b  c  d  e  f |
+--------------------------------------------------+------------------+
| 36 00 00 01 00 FD 66 66  06 00 03 00 00 00 2B 52 | 6.....ff......+R |
| 65 63 6F 72 64 73 3A 20  34 31 39 34 33 30 20 20 | ecords: 419430   |
| 44 75 70 6C 69 63 61 74  65 73 3A 20 30 20 20 57 | Duplicates: 0  W |
| 61 72 6E 69 6E 67 73 3A  20 30                   | arnings: 0       |
+--------------------------------------------------+------------------+
 
<== conn:13 BatchBulk.readResponsePacket (0,54)
+--------------------------------------------------+
|  0  1  2  3  4  5  6  7   8  9  a  b  c  d  e  f |
+--------------------------------------------------+------------------+
| 36 00 00 01 00 FD 66 66  06 00 03 00 00 00 2B 52 | 6.....ff......+R |
| 65 63 6F 72 64 73 3A 20  34 31 39 34 33 30 20 20 | ecords: 419430   |
| 44 75 70 6C 69 63 61 74  65 73 3A 20 30 20 20 57 | Duplicates: 0  W |
| 61 72 6E 69 6E 67 73 3A  20 30                   | arnings: 0       |
+--------------------------------------------------+------------------+

Comment by Diego Dupin [ 2021-06-15 ]

some code to reproduce it:

https://github.com/rusher/mxs-3588/

index.js DB access to be changed,
in package.json, mariadb must point to a node.js repo on maintenance 3.x branch.

Comment by markus makela [ 2021-06-15 ]

OK, I managed to reproduce it and hit a debug assertion: debug assert at /home/markusjm/MaxScale/server/core/backend.cc:218 failed: is_waiting_result()

Comment by markus makela [ 2021-06-15 ]

Seems to be caused by a length miscalculation that didn't include the four bytes of header data into the overall network packet length. This in turn caused it to think these packets did not generate responses and were a part of a single large packet.

Comment by markus makela [ 2021-06-16 ]

Also found a related bug that could cause response packets to be discarded if the client sent batched statements. This only seems to happen with readconnroute in 2.5: MXS-3617

Generated at Thu Feb 08 04:22:30 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.