MySQL and JDBC with rewriteBatchedStatements=true

Question

I've been reading around, here, here and here about the advantages of using rewriteBatchedStatements=true

If I understood it right, with rewriteBatchedStatements=true the JDBC will pack as many queries as possible into a single network packet, lowering this way the network overhead. Am I right?

Then it comes into my attention that the value defined in the MySQL server for the max_allowed_packet may cause problems with the queries (queries not being executed on the server).

So my second question is, does JDBC knows the value assigned to max_allowed_packet and therefore make the packet smaller than the defined value for max_allowed_packet or that is something that the developer has to take in consideration?

If I understood something wrong, please let me know as well.

is rewriteBatchedStatements and hibernate.jdbc.batch_size property related? I am in an assumption that batch_size property drives how many values would get appended in one Insert. Please clarify! — Dhruv Bansal, Nov 14 '18 at 13:09

Gord Thompson · Accepted Answer · 2016-10-27T00:34:19.570

with rewriteBatchedStatements=true the JDBC will pack as many queries as possible into a single network packet, lowering this way the network overhead. Am I right?

Yes. The following code

String myConnectionString =
        "jdbc:mysql://localhost:3307/mydb?" +
        "useUnicode=true&characterEncoding=UTF-8";
try (Connection con = DriverManager.getConnection(myConnectionString, "root", "whatever")) {
    try (PreparedStatement ps = con.prepareStatement("INSERT INTO jdbc (`name`) VALUES (?)")) {
        for (int i = 1; i <= 5; i++) {
            ps.setString(1, String.format(
                    "Line %d: Lorem ipsum dolor sit amet, consectetur adipisicing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat.", 
                    i));
            ps.addBatch();
        }
        ps.executeBatch();
    }
}

will send individual INSERT statements even though I have created a Batch

INSERT INTO jdbc (`name`) VALUES ('Line 1: Lorem ipsum ...')
INSERT INTO jdbc (`name`) VALUES ('Line 2: Lorem ipsum ...')

However, if I change the connection string to include rewriteBatchedStatements=true

String myConnectionString =
        "jdbc:mysql://localhost:3307/mydb?" +
        "useUnicode=true&characterEncoding=UTF-8" +
        "&rewriteBatchedStatements=true";

then JDBC will send one or more multi-row INSERT statements

INSERT INTO jdbc (`name`) VALUES ('Line 1: Lorem ipsum ...'),('Line 2: Lorem ipsum ...')

does JDBC knows the value assigned to max_allowed_packet and therefore make the packet smaller than the defined value for max_allowed_packet ... ?

Yes. If you enable the MySQL general log and check it you will see that MySQL Connector/J inspects a bunch of variables when it connects, one of which is max_allowed_packet. You can also set a small max_allowed_packet value and verify that JDBC splits a batch into several multi-row INSERT statements if a single such statement for the whole batch would exceed max_allowed_packet.

but there is a side effect about ACID: without rewriteBatchedStatements we may insert a part of rows, but now we may insert nothing even if there is only one duplicated key. — auntyellow, Dec 31 '14 at 14:47
Just a side note: You should not close `con` and `ps` because it will be closed automatically by the try-with-resources block. — Benjamin, Apr 28 '16 at 12:42
@GordThompson any idea why does rewriteBatchedStatements default to false for mysql ? Is there any cost/drawback of enabling it ? — Suren Aznauryan, Nov 14 '18 at 17:26
With something like this, it is better to have an opt-in philosophy, vs a forced upon you one. But to the question, it (like all code bases) could have bugs in it (hopefully not). https://www.google.com/search?q=rewriteBatchedStatements+bug+site:bugs.mysql.com — granadaCoder, Mar 31 '21 at 17:09

MySQL and JDBC with rewriteBatchedStatements=true

1 Answers1

Linked