Bug #33851 Passing UNSIGNED param to EXECUTE returns ERROR 1210
Submitted: 13 Jan 2008 18:15 Modified: 15 Mar 2008 10:53
Reporter: Ross Smith Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Prepared statements Severity:S3 (Non-critical)
Version:5.0.54, 5.0.45-community-nt-log OS:Microsoft Windows (XP SP2)
Assigned to: Davi Arnaut CPU Architecture:Any
Tags: 1210, regression
Triage: D2 (Serious) / R3 (Medium) / E3 (Medium)

[13 Jan 2008 18:15] Ross Smith
Description:
Executing the following in 5.0.45:

DROP PROCEDURE IF EXISTS e1210;
DELIMITER //
CREATE PROCEDURE e1210(u_rows INT UNSIGNED)
BEGIN
  SET @u_rows  = IFNULL(u_rows, 10);
  PREPARE stmt FROM "SELECT * FROM mysql.user LIMIT ?";
  EXECUTE stmt USING @u_rows;
  DEALLOCATE PREPARE stmt;
END;
//
DELIMITER ;
CALL e1210(10);

produces

ERROR 1210 (HY000): Incorrect arguments to EXECUTE

This problem was not in 5.0.41.

How to repeat:
See above.

Suggested fix:
There are several workarounds:

1. Replace

  SET @u_rows = IFNULL(u_rows, 10);

with

  SET @u_rows = u_rows;
  IF @u_rows IS NULL THEN
    SET @u_rows = 10;
  END IF;

2. Replace

  SET @u_rows = IFNULL(u_rows, 10);

with

  SET @u_rows = CONVERT(IFNULL(u_rows, 10), SIGNED);

3. Change u_rows to an INT or INT SIGNED parameter.
[14 Jan 2008 4:39] Valeriy Kravchuk
Thank you for a bug report. Verified just as described with MySQL 5.0.54 also.
[22 Jan 2008 1:27] Ross Smith
I think http://bugs.mysql.com/bug.php?id=33859 refers to the same bug
[19 Feb 2008 16:58] Davi Arnaut
A reduced test case:

  SET @limit  = CONVERT(10, DECIMAL);
  PREPARE stmt FROM "SELECT 1 LIMIT ?";
  EXECUTE stmt USING @limit;
  DEALLOCATE PREPARE stmt;
[27 Feb 2008 23:16] Konstantin Osipov
Regression caused by a fix for Bug#28464
[28 Feb 2008 2:59] Bugs System
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/43125

ChangeSet@1.2591, 2008-02-27 23:59:18-03:00, davi@mysql.com +9 -0
  Bug#33851 Passing UNSIGNED param to EXECUTE returns ERROR 1210
  
  The problem is that passing anything other than a integer to a limit
  clause in a prepared statement would fail. This limitation was introduced
  to avoid replication problems (e.g: replicating the statement with a
  string argument would cause a parse failure in the slave).
  
  The solution is to convert arguments to the limit clause to a integer
  value and use this converted value when persisting the query to the log.
[28 Feb 2008 17:56] Bugs System
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/43167

ChangeSet@1.2591, 2008-02-28 11:34:08-03:00, davi@mysql.com +11 -0
  Bug#33851 Passing UNSIGNED param to EXECUTE returns ERROR 1210
  
  The problem is that passing anything other than a integer to a limit
  clause in a prepared statement would fail. This limitation was introduced
  to avoid replication problems (e.g: replicating the statement with a
  string argument would cause a parse failure in the slave).
  
  The solution is to convert arguments to the limit clause to a integer
  value and use this converted value when persisting the query to the log.
[28 Feb 2008 22:31] Davi Arnaut
Queued in 5.0-runtime
[28 Feb 2008 22:41] Davi Arnaut
Note that if the converted or the original value is negative, the error will be thrown (1210 - Incorrect arguments to EXECUTE).
[3 Mar 2008 18:14] Bugs System
Pushed into 5.0.58
[3 Mar 2008 18:18] Bugs System
Pushed into 5.1.24-rc
[3 Mar 2008 18:18] Bugs System
Pushed into 6.0.5-alpha
[15 Mar 2008 10:53] Jon Stephens
Documented bugfix in the 5.0.58, 5.1.24, and 6.0.5 changelogs as follows:

        Passing anything other than a integer to a LIMIT clause in a prepared
        statement would fail. (This limitation was introduced to avoid
        replication problems; for example, replicating the statement with a
        string argument would cause a parse failure in the slave). Now,
        arguments to the LIMIT clause are converted to integer values, and these
        converted values are used when logging the statement.

Changed category from SP to PS to reflect the actual circumstances of the bug.
[2 Apr 2008 17:53] Jon Stephens
Fix also noted in the 5.1.23-ndb-6.3.11 changelog.