MySQL Bugs: #72302: updateRow fails for non mentioned bit columns which have a default value

Bug #72302	updateRow fails for non mentioned bit columns which have a default value
Submitted:	10 Apr 2014 9:50	Modified:	25 Feb 11:54
Reporter:	a a	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	Connector / J	Severity:	S2 (Serious)
Version:	Connector 5.1.30 Server: 5.5.37	OS:	Windows
Assigned to:	Alexander Soklakov	CPU Architecture:	Any

Description:
I have a table with a NOT NULL bit column which have a default value.
I am able to create a new row without mentioning the bit column in the ResultSet.
But I am not able to update the row.

The connector seems to create a wrong update query.

How to repeat:
Create tables:
------------------------------------------------------------------------------

CREATE TABLE `test` (
  `Id` int(11) NOT NULL,
  `Value1` varchar(255) NULL,
  `Value2` bit(1) NOT NULL DEFAULT b'0',
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `test2` (
  `Id` int(11) NOT NULL,
  `Value1` varchar(255) NULL,
  `Value2` bit(1) NULL,
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

------------------------------------------------------------------------------

Java test program:

------------------------------------------------------------------------------
package test;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class TestMysqlProblem
{
	public static void main(String[] args)
	{
		//Connection öffnen
		try
		{
			String strServer = "192.168.70.32";
			String strDb = "test";
			String strUser = "test";
			String strPwd = "test";
			Class.forName("com.mysql.jdbc.Driver");
			Connection con = DriverManager.getConnection("jdbc:mysql://" + strServer + "/" + strDb + "?characterEncoding=utf8&amp;useUnicode=true&amp;dumpQueriesOnException=true",strUser,strPwd);
			
			//works
			doit("test2", con);
			//does not work
			doit("test", con);
			
		  con.close();
		}
		catch (Exception e)
		{
			System.out.println(e.getMessage());
		}
	}

	private static void doit(String strTable, Connection con) throws SQLException
  {
	  Statement sDel = con.createStatement();
	  sDel.execute("DELETE FROM " + strTable);
	  sDel.close();

	  Statement s = con.createStatement(ResultSet.TYPE_FORWARD_ONLY,ResultSet.CONCUR_UPDATABLE);
	  
	  String strSql = "SELECT * FROM " + strTable + " WHERE false";
	  ResultSet rs = s.executeQuery(strSql);
	  rs.moveToInsertRow();
	  
	  rs.updateInt("Id", 1);
	  rs.updateString("Value1","val1");
	  
	  rs.insertRow();
	  rs.last();
	  
	  //change Field
	  rs.updateString("Value1","val2");
	  rs.updateRow();
	  
	  rs.close();
	  s.close();
  }
}

------------------------------------------------------------------------------

It works for table test2 but fails for table test with:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1

Thanks for the report.

I confirm this behavior exists in latest driver version.

However server protocol doesn't provide Connector/J with information about default column values, but does provide NOT NULL flag. So c/J when updating ResultSet can base only on column list it already contains.

In your case generated query looks like:

UPDATE `test`.`test` SET `test`.`test`.`Id`=1,`test`.`test`.`Value1`='val2',`test`.`test`.`Value2`= WHERE `test`.`test`.`Id`<=>1

and query parsing failed at "`Value2`= WHERE"

To make your test work you just need to change
String strSql = "SELECT * FROM " + strTable + " WHERE false";
to
String strSql = "SELECT Id, Value1 FROM " + strTable + " WHERE false";
so that ResultSet contains only fields you're going to update.

I mark this bug as Verified, but please be aware that it might never be fixed.

Bug#75475 is a duplicate of this one.
This bug is possibly a duplicate of Bug#71143.

Posted by developer:
 
This bug is not reproducible with the latest Connector/J 8.0:

    @Test
    public void testBug72302() throws Exception {
        createTable("testBug72302_1",
                "(`Id` int(11) NOT NULL,`Value1` varchar(255) NULL,`Value2` bit(1) NOT NULL DEFAULT b'0',PRIMARY KEY (`Id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8");
        createTable("testBug72302_2",
                "(`Id` int(11) NOT NULL,`Value1` varchar(255) NULL,`Value2` bit(1) NULL,PRIMARY KEY (`Id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8");

        Properties props = new Properties();
        props.setProperty(PropertyKey.sslMode.getKeyName(), SslMode.DISABLED.name());
        props.setProperty(PropertyKey.allowPublicKeyRetrieval.getKeyName(), "true");
        Connection con = getConnectionWithProps(props);

        for (String tname : new String[] { "testBug72302_1", "testBug72302_2" }) {
            this.stmt.execute("DELETE FROM " + tname);

            Statement s = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);

            String strSql = "SELECT * FROM " + tname + " WHERE false";
            this.rs = s.executeQuery(strSql);
            this.rs.moveToInsertRow();

            this.rs.updateInt("Id", 1);
            this.rs.updateString("Value1", "val1");

            this.rs.insertRow();
            this.rs.last();

            //change Field
            this.rs.updateString("Value1", "val2");
            this.rs.updateRow();

            this.rs.close();
            s.close();

            this.rs = this.stmt.executeQuery("select * from " + tname);
            int cnt = this.rs.getMetaData().getColumnCount();
            while (this.rs.next()) {
                for (int i = 1; i <= cnt; i++) {
                    System.out.print(this.rs.getString(i) + " | ");
                }
                System.out.println();
            }
        }
    }

Output:

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Running test testsuite.regression.ResultSetRegressionTest#testBug72302()
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Connected to 8.0.28
1 | val2 | 0 | 
1 | val2 | null | 

Connector/J 5.1 series came to EOL on Feb 9th, 2021, see https://www.mysql.com/support/eol-notice.html, so this bug will not be fixed there.