[MDEV-12471] BULK Command Created: 2017-04-07  Updated: 2018-05-23  Resolved: 2017-06-20

Status: Closed
Project: MariaDB Server
Component/s: Scripts & Clients
Fix Version/s: 10.2.7

Type: Task Priority: Major
Reporter: Oleksandr Byelkin Assignee: Oleksandr Byelkin
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Blocks
blocks CONC-240 Bulk Insert (Row-wise Binding) insert... Closed
Relates
relates to MDEV-16278 Missing DELETE operation in COM_STMT_... Closed
relates to CONJ-389 implement bulk operation 10.2 Closed
relates to MDEV-9114 Bulk operations (Array binding) Closed
relates to MDEV-11419 Report all INSERT ID for bulk operati... Closed

 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)
  • until end of packet :
    • for each parameter :
      • byte<1>: parameter indicator (see after)
      • if indicator == NONE
        • byte<n>: binary parameter value

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



 Comments   
Comment by Oleksandr Byelkin [ 2017-05-02 ]

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 Command

Core changes

—

Comment by Oleksandr Byelkin [ 2017-05-02 ]

github tree is bb-10.2-MDEV-12471

Comment by Vladislav Vaintroub [ 2017-05-03 ]

review done (in github).

Comment by Diego Dupin [ 2017-05-09 ]

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 !?

Comment by Diego Dupin [ 2017-05-15 ]

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.

Comment by Diego Dupin [ 2017-05-17 ]

Previous problem are corrected.
Remain now only the implementation with flag that return "generated auto-increment IDs". to test when added

Generated at Thu Feb 08 07:57:59 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.