MySQL Bugs: #26199: Replication of stored procedures with BIT parameters fails

Bug #26199	Replication of stored procedures with BIT parameters fails
Submitted:	8 Feb 2007 22:04	Modified:	28 Nov 2007 16:42
Reporter:	John Thompson	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S2 (Serious)
Version:	5.0.27, 5.0, 5.1 BK	OS:	Any (Windows, Linux)
Assigned to:	Andrei Elkin	CPU Architecture:	Any

Description:
In my MySQL Master Server, numerous stored procedures execute
to update the database. These must be replicated by a Slave.

The Master runs successfully.

Within the Slave, SOME stored procedures in an update
cycle run successfully. SOME DO NOT. The ones that fail have
a BIT input parameter type in them. In the error message (from the
Slave) below, the BIT type happens to be in the SIXTH parameter position
(see 'line 6' below).

(NOTE that there is NO ERROR in the original SQL - Master ran fine!)

070207 9:39:08 [ERROR] Slave: Error '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 6' on query.
Default database: 'webusage'. Query:
'UPDATE Limits SET username = NAME_CONST('a_username',_latin1'xyz'),
max_bbb = NAME_CONST('a_max_bbb',-1),
max_ccc = NAME_CONST('a_max_ccc',-1),
max_ddd = NAME_CONST('a_max_ddd',-1),
max_eee = NAME_CONST('a_max_eee',-1),
disabled = NAME_CONST('a_disabled',', Error_code: 1064
070207 9:39:08 [ERROR] Error running query, slave SQL thread aborted.
Fix the problem, and restart the slave SQL thread with "SLAVE START".
We stopped at log 'BinaryLog_ReplicationServer.000007' position 16961

The above Error message is a consequence of the Stored Procedure code
executing - in other words, the stored procedure has this update stmt
that fails because it has a bit-type input parameter.

ALSO NOTE that the stored procedure WILL execute correctly on the Slave when invoked from a command line tool - it is in the context of the Replication step, probably being wrapped in the NAME_CONST function, that the problem occurs.

How to repeat:
Create a stored procedure with a type 'bit' input parameter (one or more parameters may be required).

Define this stored procedure in the Master and the Slave database servers. Invoke the stored procedure from the Master. It will fail in the slave.

NOTE that the stored procedure WILL execute correctly on the Slave when invoked from a command line tool - it is in the context of the Replication step - duplicating a Master server update and, probably due to being wrapped in the NAME_CONST function, that the problem occurs.

Suggested fix:
The work around I found was to CHANGE MY STORED PROCs to use INT not BIT input type arguments.

Thank you for the bug report. Could you please provide a repeatable test
case sometimes the description isn't enough to be repeatable on our side:

- dump of the table with insert commands
- create procedure definition 
- the master and slave my.ini files

Thanks in advance.

Here is the table being updated (that updates properly by stored proc on the Master, and fails to update properly on the Slave):

mysql> desc webusage.usage;
+-----------+--------------+------+-----+---------+-------+
| Field     | Type         | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| username  | varchar(50)  | NO   | PRI |         |       |
| use_date  | datetime     | NO   | PRI |         |       |
| appname   | varchar(60)  | NO   | PRI |         |       |
| type      | varchar(32)  | NO   | PRI |         |       |
| uses      | int(10)      | NO   |     | 0       |       |
| layername | varchar(128) | NO   | PRI | None    |       |
+-----------+--------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

Here is the stored proc that updates that table (usage)
NOTE THE bit DATA TYPE ON INPUT PARAMETERS (THE Culprit!):

USE `webusage`;

DROP PROCEDURE IF EXISTS `webusage`.`sp_LimitsUpdateCommand_2`;
delimiter //
CREATE PROCEDURE sp_LimitsUpdateCommand_2
(
	a_username varchar(50),
	a_max_geocodes int,
	a_max_distance_calcs int,
	a_max_pips int,
	a_max_logins int,
	a_disabled bit,
	a_geocode_uses int,
	a_distance_uses int,
	a_pip_uses int,
	a_login_uses int,
	a_expire_date datetime,
	a_Original_username varchar(50),
	a_Original_disabled bit,
	a_Original_distance_uses int,
	a_Original_geocode_uses int,
	a_Original_login_uses int,
	a_Original_max_distance_calcs int,
	a_Original_max_geocodes int,
	a_Original_max_logins int,
	a_Original_max_pips int,
	a_Original_pip_uses int,
	a_Original_expire_date datetime
)
BEGIN
	
	UPDATE Limits SET username = a_username, 
		max_geocodes = a_max_geocodes, 
		max_distance_calcs = a_max_distance_calcs, 
		max_pips = a_max_pips, 
		max_logins = a_max_logins, 
		disabled = a_disabled, 
		geocode_uses = a_geocode_uses, 
		distance_uses = a_distance_uses, 
		pip_uses = a_pip_uses, 
		login_uses = a_login_uses, 
		expire_date = a_expire_date 
	WHERE (username = a_Original_username) 
	AND (disabled = a_Original_disabled) 
	AND (distance_uses = a_Original_distance_uses) 
	AND (geocode_uses = a_Original_geocode_uses) 
	AND (login_uses = a_Original_login_uses) 
	AND (max_distance_calcs = a_Original_max_distance_calcs) 
	AND (max_geocodes = a_Original_max_geocodes) 
	AND (max_logins = a_Original_max_logins) 
	AND (max_pips = a_Original_max_pips) 
	AND (pip_uses = a_Original_pip_uses) 
	AND (expire_date = a_Original_expire_date OR (a_Original_expire_date IS NULL AND expire_date IS NULL));
	CALL sp_LimitsSelectCommand_2 (a_username);

END;  //
delimiter ;

Here is a command line invocation that WORKS on BOTH Master and Slave:

call sp_LimitsUpdateCommand_2('nationwide', -1, -1, -1, -1, 0, 0, 0, 0, 58, NULL, 'nationwide', 0, 0, 0, 57, -1, -1, -1, -1, 0, NULL);

Here is the error from the Slave that occurs when Replication logic executes:

070209 13:52:26 [ERROR] Slave: Error '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 6' on query. Default database: 'webusage'. Query: 'UPDATE Limits SET username =  NAME_CONST('a_username',_latin1'nationwide'), 
					max_geocodes =  NAME_CONST('a_max_geocodes',-1), 
					max_distance_calcs =  NAME_CONST('a_max_distance_calcs',-1), 
					max_pips =  NAME_CONST('a_max_pips',-1), 
					max_logins =  NAME_CONST('a_max_logins',-1), 
					disabled =  NAME_CONST('a_disabled',', Error_code: 1064

WORK-AROUND:

Change the Stored Procedure input parameters from type bit to type int.

My Apologies - I gave the wrong table desc.  Here is the correct one:

mysql> desc limits;
+--------------------+-------------+------+-----+---------+-------+
| Field              | Type        | Null | Key | Default | Extra |
+--------------------+-------------+------+-----+---------+-------+
| username           | varchar(50) | NO   | PRI |         |       |
| max_geocodes       | int(10)     | NO   |     | -1      |       |
| max_distance_calcs | int(10)     | NO   |     | -1      |       |
| max_pips           | int(10)     | NO   |     | -1      |       |
| max_logins         | int(10)     | NO   |     | -1      |       |
| disabled           | tinyint(1)  | NO   |     | 0       |       |
| geocode_uses       | int(10)     | NO   |     | 0       |       |
| distance_uses      | int(10)     | NO   |     | 0       |       |
| pip_uses           | int(10)     | NO   |     | 0       |       |
| login_uses         | int(10)     | NO   |     | 0       |       |
| expire_date        | datetime    | YES  |     | NULL    |       |
+--------------------+-------------+------+-----+---------+-------+
11 rows in set (0.01 sec)

Thank you for the report.

I tried it with yesterday sources and could repeat, but I can not with today sources. So seems bug has fixed now.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html

MYSQL 5.0.18
Very similar problem with parameter being a datetime.
Fails with same type of error as this bug report.
The update works on master server and fails on slave.
The parameter data being passed to the stored procedure is correct but looks
like garbage on the slave replication statement.
Last_Errno: 1064
Last_Error: Error '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 '♥@é≡☻D±B♥$·_' at line 2' on query. Default database: 'onvicenter'. Query: 'UPDATE dpqueue SET status =  NAME_CONST('OUTSTATUS_WAITING_PORT',0),
                     retryTime =  NAME_CONST('_TEMP_TIME',4εB♥@é≡☻D±B♥$·_'

Bug reoccured in current versions.

Verified as described in bug #30194 using following test:

source include/master-slave.inc;

CREATE TABLE t (b BIT(1));
DELIMITER $$;
CREATE PROCEDURE testbug(bitvalue BIT)
BEGIN
  INSERT INTO t SET b = bitvalue;
END $$
DELIMITER ;$$
CALL testbug(1);
SELECT * FROM t;

sleep 5;

connection slave;

SELECT * FROM t;

--vertical_results

show slave status;

Bug #30194 was marked as duplicate of this one.

Thanks Sveta for verifying the bug, posting my comments over here, and reopening the bug.  You saved me a lot of work.  (and taught me a new MySQL command I didn't know.  :)  )

The only workaround we've been able to really use is to change all our bit parameters to integers.  It's not what we wanted, but it will do.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/34275

ChangeSet@1.2501, 2007-09-14 17:01:53+03:00, aelkin@koti.dsl.inet.fi +3 -0
  Bug #26199  	Replication Failure on Slave when using stored procs with bit-type parameters.
  
  The value of the actual argument of BIT-type-arg stored procedure was binlogged as non-espaped sequence of
  bytes corresponding to internal representation of the bit value.
  
  The patch enforces binlogging of the bit-argument as a valid literal: prefixing the quoted bytes sequence with
  _binary.
  Note, that behaviour of Item_field::var_str for field_type() of MYSQL_TYPE_BIT is exceptional in that
  the returned string contains the binary representation even though result_type() of the item is INT_RESULT.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/35995

ChangeSet@1.2501, 2007-10-21 18:37:37+03:00, aelkin@dsl-hkibras1-ff5fc300-23.dhcp.inet.fi +3 -0
  Bug #26199  	Replication Failure on Slave when using stored procs with bit-type parameters.
  
  The value of the actual argument of BIT-type-arg stored procedure was binlogged as non-escaped
  sequence of bytes corresponding to internal representation of the bit value.
  
  The patch enforces binlogging of the bit-argument as a valid literal: prefixing the quoted bytes
  sequence with _binary.
  Note, that behaviour of Item_field::var_str for field_type() of MYSQL_TYPE_BIT is exceptional
  in that the returned string contains the binary representation even though result_type() of
  the item is INT_RESULT.

Pushed into 5.0.54

Pushed into 5.1.23-rc

Pushed into 6.0.4-alpha

Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html

Documented bugfix in 5.0.54, 5.1.23, and 6.0.4 changelogs as follows:

        Stored procedures having BIT parameters
        were not replicated correctly.