MySQL and Jdbc with Rewritebatchedstatements=True

MySQL and JDBC with rewriteBatchedStatements=true

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.

mysql jdbc not batching queries even after rewriteBatchedStatements = true

For others having the issue of jdbcTemplate connection url not honoring rewriteBatchedStatements = true Check your mysql-connector-java version in your pom.xml

At the time of writing this question I had

<dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>5.1.9</version>
</dependency>

Since my batch writes were happening one by one it appears that version 5.1.9 doesn't support batch updates, and falls back as noted in the spring docs

batchUpdate() -- Will fall back to separate updates on a single Statement if the JDBC driver does not support batch updates.

Bumping this to version 5.1.18 gave me proper batch updates, verified in mysql general log.

Also a bug I ran into which might save others some time. In version 5.1.23, when you configure the db url to include profileSQL=true which I imagine most do, there's a bug with the driver and profileSQL.

why does rewriteBatchedStatements default to false for mysql connector/j?

The connection property rewriteBatchedStatements provides a non-JDBC compliant feature. Not all statements can be re-written and it may held unexpected results. Particularly with regard to handling errors and returning update counts.

Also note that rewriteBatchedStatements can modify the original SQL string beyond a simple concatenation of queries which, in many cases, is not expected or desirable.

For those reasons, and many others, this property is false by default.

How programatically set rewriteBatchedStatements for mysql jdbc driver?

If you don't want to do it through the URL, you can use the Properties object with DriverManager:

Properties props = new Properties();
props.setProperty("user", ...);
props.setProperty("password", ...);
props.setProperty("rewriteBatchedStatements", "true");
Connection connection = DriverManager.getConnection(url, props);

If you use a MysqlDataSource or MysqlConnectionPoolDataSource then you need to set the property rewriteBatchedStatements (or call setter setRewriteBatchedStatements(boolean)

To change this at runtime after you have obtained a connection, you should be able to use:

((com.mysql.jdbc.ConnectionProperties) connection).setRewriteBatchedStatements(true);

Note: I have only looked at the MySQL Connector/J sources for this last option, I haven't tested it.

UPDATED

For c3p0 you can use the following:

ComboPooledDataSource cpds = ...
Connection connection = cpds.getConnection();
connection.unwrap(com.mysql.jdbc.ConnectionProperties.class).setRewriteBatchedStatements(true);

c3p0 should be com.mchange:c3p0:0.9.5.2, be carefull with com.mchange - with other groupId this code does not work.

PySpark JDBC Write to MySQL (TiDB)

You should add ?rewriteBatchedStatements=true to your JDBC URI so that the DML statements are batched. Not only the write will be faster but you wont hit your database transaction limit so easily.

---

Related Topics