public class IsolationLevelLostOnReconnect {

  @Test

  public void isolationLevelResets() throws SQLException {

    try (Connection c = DriverManager.getConnection("jdbc:mariadb://localhost:3306/test", "test", "test")) {

      long max = maxPacket(c);

      if (max > Integer.MAX_VALUE - 10) {

        fail("max_allowed_packet to high for this test");

      }

      c.prepareStatement("create table if not exists foo (x longblob)").execute();

      c.setTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITTED);

      assertThat(level(c), is("READ-UNCOMMITTED"));

      try (PreparedStatement st = c.prepareStatement("insert into foo (?)")) {

        st.setBytes(1, data((int) (max + 10)));

        st.execute();

        fail();

      } catch (SQLException e) {

        assertThat(e.getMessage(), containsString("max_allowed_packet"));

        // we still have a working connection

        assertThat(maxPacket(c), is(max));

        // but our isolation level changed:

        assertThat(level(c), is("READ-UNCOMMITTED"));

      }

    }

  }

  private String level(Connection c) throws SQLException {

    try (ResultSet rs = c.prepareStatement("select @@tx_isolation").executeQuery()) {

      rs.next();

      return rs.getString(1);

    }

  }

  private long maxPacket(Connection c) throws SQLException {

    try (ResultSet rs = c.prepareStatement("select @@max_allowed_packet").executeQuery()) {

      rs.next();

      return rs.getLong(1);

    }

  }

  private byte[] data(int size) {

    byte[] data = new byte[size];

    Arrays.fill(data, (byte) 'a');

    return data;

  }

}

     * Attempts to change the transaction isolation level for this

     * <code>Connection</code> object to the one given.