Details
-
Task
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
None
Description
BULK Command
Protocol
- byte<1> (250) COM_STMT_BULK_EXECUTE
- int<4> stmt id
- byte<2> bulk_flags
- if BIT_IS_SET(bulk_flags,SEND_TYPES_TO_SERVER)
- for each parameter :
- byte<1>: field type
- byte<1>: parameter flag (128 = unsigned)
- for each parameter :
- until end of packet :
- for each parameter :
- byte<1>: parameter indicator (see after)
- if indicator == NONE
- byte<n>: binary parameter value
- for each parameter :
BULK response:
- OK_PACKET (with MORE_RESULT_EXISTS if NEED_GENERATED_IDS)
- if BIT_IS_SET(bulk_flags,NEED_GENERATED_IDS)
- insert ids resultset encoded in binary
- ERROR_PACKET something bad happened, also if stmt would return result set
Flags
Bulk flags:
| 64 | Return generated auto-increment IDs |
| 128 | Send types to server |
Indicator parameter:
| 0 | NONE | Value follow |
| 1 | NULL | Value is null |
| 2 | DEFAULT | For INSERT/UPDATE, value is default |
| 3 | IGNORE | Value is default one for insert, Is ignored for update |
Generated IDs result-set :
result-set encoded in binary format.
Columns
| Column name | type | meaning |
|---|---|---|
| “Id” | Unsigned 8 byte numeric | Start ID of the sequence |
| “Len” | Unsigned 8 byte numeric | Sequence length (number of sequential Ids) |
| “Inc” | Signed 8 byte numeric | Increment of the sequence (same for all records of this row) |
Example
The following result set
Id Len Inc
1 2 1
4 3 2
15 1 1
Is returned if generated ids are 1,2, 4,6 ,8, 15
Attachments
Issue Links
- blocks
-
CONC-240 Bulk Insert (Row-wise Binding) inserts wrong data in db when array_size= 1
-
- Closed
-
- relates to
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
Activity
When sending only one "row" with BULK format, i have an example where 2 results are inserted.
try (Statement stmt = connection.createStatement()) { |
stmt.execute("DROP TABLE IF EXISTS test_batch"); |
stmt.execute("create table test_batch (bit1 SMALLINT , bit2 SMALLINT)"); |
}
|
|
|
PreparedStatement ps = connection.prepareStatement("INSERT INTO test_batch (bit1,bit2) VALUE (?,?)"); |
connection.createStatement().execute("truncate test_batch"); |
|
|
ps.setByte(1, (byte) 0x01); |
ps.setByte(2, (byte) 0x03); |
ps.addBatch();
|
ps.executeBatch();
|
|
|
//!!!! 2 datas are inserted not one !!!! |
//normally must be one row with values 1 and 3 |
//but results are first row with value 0 and 1, second row with values 0 and 3 |
|
|
try (Statement stmt = connection.createStatement()) { |
ResultSet rs = stmt.executeQuery("SELECT * FROM test_batch"); |
while (rs.next()) { |
System.out.println("bit1:" + rs.getInt(1) + " bit2:"+ rs.getInt(2) |
);
|
}
|
}
|
// will result in: |
// bit1:0 bit2:1 |
// bit1:0 bit2:3 |
}
|
The exchanges are as expected, but for the 2 results:
2017-05-09 16:16:45 [main] TRACE o.m.j.i.i.o.AbstractPacketOutputStream - send: conn:29
|
2F 00 00 00 16 49 4E 53 45 52 54 20 49 4E 54 4F /....INSERT INTO
|
20 74 65 73 74 5F 62 61 74 63 68 20 28 62 69 74 test_batch (bit
|
31 2C 62 69 74 32 29 20 56 41 4C 55 45 20 28 3F 1,bit2) VALUE (?
|
2C 3F 29 ,?)
|
|
|
2017-05-09 16:16:45 [main] TRACE o.m.j.i.i.i.StandardPacketInputStream - read: conn:29
|
0C 00 00 01 ....
|
00 01 00 00 00 00 00 02 00 00 00 00 ............
|
|
|
2017-05-09 16:16:45 [main] TRACE o.m.j.i.i.i.StandardPacketInputStream - read: conn:29
|
17 00 00 02 ....
|
03 64 65 66 00 00 00 01 3F 00 0C 3F 00 00 00 00 .def....?..?....
|
00 FD 80 00 00 00 00 .......
|
|
|
2017-05-09 16:16:45 [main] TRACE o.m.j.i.i.i.StandardPacketInputStream - read: conn:29
|
17 00 00 03 ....
|
03 64 65 66 00 00 00 01 3F 00 0C 3F 00 00 00 00 .def....?..?....
|
00 FD 80 00 00 00 00 .......
|
|
|
2017-05-09 16:16:45 [main] TRACE o.m.j.i.i.o.AbstractPacketOutputStream - send: conn:29
|
14 00 00 00 03 74 72 75 6E 63 61 74 65 20 74 65 .....truncate te
|
73 74 5F 62 61 74 63 68 st_batch
|
|
|
2017-05-09 16:16:45 [main] TRACE o.m.j.i.i.i.StandardPacketInputStream - read: conn:29
|
07 00 00 01 ....
|
00 00 00 02 00 00 00 .......
|
|
|
2017-05-09 16:16:45 [main] TRACE o.m.j.i.i.o.AbstractPacketOutputStream - send: conn:29
|
0F 00 00 00 FA 01 00 00 00 80 00 01 00 01 00 00 ................
|
01 00 03 ...
|
|
|
2017-05-09 16:16:45 [main] TRACE o.m.j.i.i.i.StandardPacketInputStream - read: conn:29
|
2E 00 00 01 ....
|
00 02 00 02 00 00 00 26 52 65 63 6F 72 64 73 3A .......&Records:
|
20 32 20 20 44 75 70 6C 69 63 61 74 65 73 3A 20 2 Duplicates:
|
30 20 20 57 61 72 6E 69 6E 67 73 3A 20 30 0 Warnings: 0
|
|
|
2017-05-09 16:16:45 [main] TRACE o.m.j.i.i.o.AbstractPacketOutputStream - send: conn:29
|
19 00 00 00 03 53 45 4C 45 43 54 20 2A 20 46 52 .....SELECT * FR
|
4F 4D 20 74 65 73 74 5F 62 61 74 63 68 OM test_batch
|
|
|
2017-05-09 16:16:45 [main] TRACE o.m.j.i.i.i.StandardPacketInputStream - read: conn:29
|
01 00 00 01 ....
|
02 .
|
|
|
2017-05-09 16:16:45 [main] TRACE o.m.j.i.i.i.StandardPacketInputStream - read: conn:29
|
37 00 00 02 7...
|
03 64 65 66 05 74 65 73 74 6A 0A 74 65 73 74 5F .def.testj.test_
|
62 61 74 63 68 0A 74 65 73 74 5F 62 61 74 63 68 batch.test_batch
|
04 62 69 74 31 04 62 69 74 31 0C 3F 00 06 00 00 .bit1.bit1.?....
|
00 02 00 00 00 00 00 .......
|
|
|
2017-05-09 16:16:45 [main] TRACE o.m.j.i.i.i.StandardPacketInputStream - read: conn:29
|
37 00 00 03 7...
|
03 64 65 66 05 74 65 73 74 6A 0A 74 65 73 74 5F .def.testj.test_
|
62 61 74 63 68 0A 74 65 73 74 5F 62 61 74 63 68 batch.test_batch
|
04 62 69 74 32 04 62 69 74 32 0C 3F 00 06 00 00 .bit2.bit2.?....
|
00 02 00 00 00 00 00 .......
|
|
|
2017-05-09 16:16:45 [main] TRACE o.m.j.i.i.i.StandardPacketInputStream - read: conn:29
|
04 00 00 04 ....
|
01 30 01 31 .0.1
|
|
|
2017-05-09 16:16:45 [main] TRACE o.m.j.i.i.i.StandardPacketInputStream - read: conn:29
|
04 00 00 05 ....
|
01 30 01 33 .0.3
|
|
|
2017-05-09 16:16:45 [main] TRACE o.m.j.i.i.i.StandardPacketInputStream - read: conn:29
|
07 00 00 06 ....
|
FE 00 00 22 00 00 00 ..."...
|
|
The exact same command without the ""truncate test_batch" query works perfectly !?
Result would be exactly that if there was no indicator for each value, but bulk always use a byte indicator.
executing a COM_STMT_EXECUTE after prepare make this indicator flag fail !?
last issue was corrected.
Tests using java connector are completed.
I have 2 questions :
support of BULK when there is no parameters
This is exotic (there is no reallistic reason to support that).
when there is no parameters for the query, the exact same byte will be send, for 1 or 1000 execution. (=i.e will be executed only one time).
query example : "UPDATE test_batch set t1 = t1 + 1"
value will be only executed once.
C/C and C/J won't use bulk protocol if there is no parameter, but it would be great if server send an exception if using bulk with no parameters.
charset difference .
There is some difference in charset handling when using COM_STMT_BULK_EXECUTE compared to using COM_STMT_EXECUTE.
Table created with charset "create table fooLatin1 (x longtext) DEFAULT CHARSET=latin1".
when sending a utf-8 data "ÄÖÜ" with query "INSERT INTO fooLatin1 VALUES
" (right, that's weird to send utf-8 data in a latin1 table, but still) :
when sending in COM_STMT_EXECUTE:
15 00 00 00 17 01 00 00 00 00 01 00 00 00 00 01 ................
|
.. COM_STMT_EXECUTE header
|
.. .. .. .. statement id
|
.. flag
|
.. .. .. .. Iteration count
|
.. null bitmap
|
.. send type to server flag (= send)
|
0F 00 06 C3 84 C3 96 C3 9C .........
|
.. field type
|
.. parameter flag
|
.. .. .. .. .. .. .. = binary parameter value = ÄÖÜ
|
|
same send using COM_STMT_BULK_EXECUTE :
11 00 00 00 FA 01 00 00 00 80 00 0F 00 00 06 C3 ................
|
.. COM_STMT_BULK_EXECUTE
|
.. .. .. .. stmt id
|
.. .. bulk_flags
|
.. .. field type
|
.. indicator
|
.. ..
|
84 C3 96 C3 9C
|
.. .. .. .. .. = binary parameter value = ÄÖÜ
|
|
data won't be store the same way :
if selecting result are not the same : "ÄÖÜ" for the data send with bulk, "ÄÖÜ" otherwise.
Previous problem are corrected.
Remain now only the implementation with flag that return "generated auto-increment IDs". to test when added
revision-id: 53893cc05983bf865f78956ed1019ac558642620 (mariadb-10.2.5-128-g53893cc0598)
parent(s): a60bdcba649d14bc0b0f5de23ae11dabe7aa8e7f
committer: Oleksandr Byelkin
timestamp: 2017-05-02 17:10:04 +0200
message:
MDEV-12471: BULK CommandCore changes
—