MySQL Bugs: #111203: "value" as a substring in table column lead to invalidation of batch insert

Bug #111203	"value" as a substring in table column lead to invalidation of batch insert
Submitted:	30 May 2023 17:35	Modified:	1 Jun 2023 7:25
Reporter:	Jerome Chyi	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	Connector / J	Severity:	S3 (Non-critical)
Version:	8.0.29,8.0.30	OS:	CentOS
Assigned to:		CPU Architecture:	x86

Description:
When using "rewriteBatchedStatements=true", the INSERT statements cannot be rewritten if any of the table columns contain the substring "value". This leads to statements that should be batchable to being inserted one by one.

How to repeat:
Create a Insert Statement with a query that includes a column with substring "value" in one of the columns.

MySQL version is 5.7.41
Connector/J version is 8.0.29
the connect url must contain rewriteBatchedStatements=true
the create table SQL is “create table test(a varchar(10) not null, b_value varchar(10), primary key(a)) default charset utf8”
the modify table SQL is “insert into test(a,b_value) values(?,?)”

the Batch process SQL is
String sql = "insert into test (a, b_value) values (?, ?)";
Connection conn = .....
conn.setAutoCommit(false);
PreparedStatement ps = comm.prepareStatement(sql);
for(int i=1;i<100;i++) {
  ps.setString(1, String.valueOf(i));
  ps.setString(2, String.valueOf(i));
  ps.addBatch();
}
ps.executeBatch();
ps.close();
conn.commit();

Suggested fix:
Check the 'value' substring in table column name to distinguish from  the 'values' clause of the insert statement.

Hello Jerome Chyi,

Thank you for the report and feedback.
I can confirm that this issue is no longer seen and most likely fixed after Bug #109377 which is fixed in 8.0.33 per change log. Could you please check at your end and confirm the same? Once I have your confirmation then I'll set this as duplicate of Bug #109377. Thank you.

--
import java.sql.*;

public class Bug111203 {

    public static void main(String[] args) throws Exception {

        try ( Connection con = getMySqlConnection()) {

            try ( PreparedStatement ps = con.prepareStatement("insert into test(a,b_value) values(?,?)")) {
                    for(int i=1;i<100;i++) {
                        ps.setString(1, String.valueOf(i));
                        ps.setString(2, String.valueOf(i));
                        ps.addBatch();
                    }
                    ps.executeBatch();
                    ps.close();
                }
        }
    }

    public static Connection getMySqlConnection() throws Exception {
        String driver = "com.mysql.cj.jdbc.Driver";
        String url = "jdbc:mysql://localhost/test?user=root&password=mysql123&rewriteBatchedStatements=true";

        Class.forName(driver);
        Connection conn = DriverManager.getConnection(url);
        DatabaseMetaData meta = conn.getMetaData();
        System.out.println("MySQL Server: " + meta.getDatabaseProductVersion());
        System.out.println("Driver Name & Ver: " + meta.getDriverName() + meta.getDriverVersion());
        return conn;
    }
}

-- from general log

Timestamp, Thread, Command Type, Detail
2023-05-31 14:46:19.009106, 31, Query, insert into test(a,b_value) values('1','1'),('2','2'),('3','3'),('4','4'),('5','5'),('6','6'),('7','7'),('8','8'),('9','9'),('10','10'),('11','11'),('12','12'),('13','13'),('14','14'),('15','15'),('16','16'),('17','17'),('18','18'),('19','19'),('20','20'),('21','21'),('22','22'),('23','23'),('24','24'),('25','25'),('26','26'),('27','27'),('28','28'),('29','29'),('30','30'),('31','31'),('32','32'),('33','33'),('34','34'),('35','35'),('36','36'),('37','37'),('38','38'),('39','39'),('40','40'),('41','41'),('42','42'),('43','43'),('44','44'),('45','45'),('46','46'),('47','47'),('48','48'),('49','49'),('50','50'),('51','51'),('52','52'),('53','53'),('54','54'),('55','55'),('56','56'),('57','57'),('58','58'),('59','59'),('60','60'),('61','61'),('62','62'),('63','63'),('64','64'),('65','65'),('66','66'),('67','67'),('68','68'),('69','69'),('70','70'),('71','71'),('72','72'),('73','73'),('74','74'),('75','75'),('76','76'),('77','77'),('78','78'),('79','79'),('80','80'),('81','81'),('82','82'),('83','83'),('84','84'),('85','85'),('86','86'),('87','87'),('88','88'),('89','89'),('90','90'),('91','91'),('92','92'),('93','93'),('94','94'),('95','95'),('96','96'),('97','97'),('98','98'),('99','99') [truncated, 1.2 KB total]

regards,
Umesh

Hello Umesh,

I check this issue in 8.0.33 and no longer see it too. Thanks for your help.

Thank you for confirming. Closing as a duplicate of Bug #109377 for now.

regards,
Umesh