JDBC: statement.setBinaryStream fails iff mysql logging is disabled

[Hendrik Brummermann]

MySQL 5.1 fails with "Incorrect a rguments to mysq ld_stmt_execute" on the following prepared statement:
update rpzone set d ata=?, protocol_ version=33 where zone_id='int_se mos_tavern_0'
if and only if mysql logging is disabled.

The problem does not occur on Debian 5.0, Ubuntu 09.04, 09.10, 10.04.

Setting the following lines in /etc/mysql/my.conf makes the problem go away:
general_log_file = /var/log/mysql/mysql.log
general_log = 1

I tried it repeatedly and enabling logging really seems to make a difference. This was confirmed by rodneymillerpca in http://stendhalgame.org/chat/2010-10-19.html

I attached a network dump of both a good and a bad case. Note that the data sent from the client to the mysql server is bit by bit identical in both cases. The following java code is used to execute the statement:

 public int execute(String sql, InputStream... inputStreams) throws SQLException, IOException {
  String mySql = rewriteSql(sql);
  int res = -2;
  PreparedStatement statement = connection.prepareStatement(mySql);
  try {
   int i = 1; // yes, jdbc starts counting at 1.
   for (InputStream inputStream : inputStreams) {
    statement.setBinaryStream(i, inputStream, inputStream.available());
    i++;
   }
   res = statement.executeUpdate();
  } finally {
   statement.close();
  }
  return res;
 }

ProblemType: Bug
DistroRelease: Ubuntu 10.10
Package: mysql-server-5.1 5.1.49-1ubuntu8
ProcVersionSignature: Ubuntu 2.6.35-22.34-generic 2.6.35.4
Uname: Linux 2.6.35-22-generic x86_64
NonfreeKernelModules: nvidia
Architecture: amd64
Date: Tue Oct 19 21:21:02 2010
ProcEnviron:
 LANG=de_DE.utf8
 SHELL=/bin/bash
SourcePackage: mysql-5.1

[Hendrik Brummermann (nhnb)]

Sorry, the additional spaces in the error message and statement are caused by me copying them from the network dump. They are not there in reality.

[Thierry Carrez (ttx)]

Do most of the other statements execute without a problem ?

[Hendrik Brummermann (nhnb)]

Yes, there are only two statements failing:

update rpzone set data=?, protocol_version=33 where zone_id='int_semos_tavern_0';
and
update opobject set data=?, protocol_version=33 where object_id=12

They fail every time with different values.

All other statements (selects, updates, inserts) work without problems. There is a batch insert using a prepared statement into the npcs table. So prepared statement work, too.

What is unique about those two failing queries is that they both use statement.setBinaryStream. The tables are defined as:

mysql> show fields from rpobject;
+------------------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------------+---------+------+-----+---------+----------------+
| object_id | int(11) | NO | PRI | NULL | auto_increment |
| data | blob | YES | | NULL | |
| protocol_version | int(11) | YES | | NULL | |
+------------------+---------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql> show fields from rpzone;
+------------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------+-------------+------+-----+---------+-------+
| zone_id | varchar(32) | NO | PRI | NULL | |
| data | blob | YES | | NULL | |
| protocol_version | int(11) | YES | | NULL | |
+------------------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

They are the only tables with blob fields.

[Launchpad Janitor (janitor)]

[Expired for mysql-5.1 (Ubuntu) because there has been no activity for 60 days.]