Bug #72302 updateRow fails for non mentioned bit columns which have a default value

• Submitted: 10 Apr 2014 9:50
• Modified: 10 Apr 2014 12:07
• Reporter: a a
• Status: Verified
• Category: Connector / J
• Severity: S2 (Serious)
• Version: Connector 5.1.30 Server: 5.5.37
• OS: Microsoft Windows
• Assigned to: Alexander Soklakov
• CPU Architecture: Any

[10 Apr 2014 9:50] a a

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&useUnicode=true&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

[10 Apr 2014 12:02] Alexander Soklakov

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.

[10 Apr 2014 12:07] Alexander Soklakov

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

[12 Jan 2015 18:36] Filipe Silva

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