Bug #54920 Stored functions are allowed in HANDLER statements, but broken.
Submitted: 30 Jun 2010 16:37 Modified: 20 Nov 2010 18:25
Reporter: Jon Olav Hauglid Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:5.1.49-bzr OS:Any
Assigned to: Jon Olav Hauglid CPU Architecture:Any
Triage: Triaged: D2 (Serious)

[30 Jun 2010 16:37] Jon Olav Hauglid
Description:
Statements such as 
  HANDLER t1 READ WHERE f1() = 1
are allowed, but:

- No metadata locks are taken for f1.
- No pre-locking is done for tables, views, etc. used by f1.
- f1 is not found unless it has been used before.
- Replication of statements inside the stored function is broken.

How to repeat:
Example 1:

CREATE TABLE t1 (a INT);
INSERT INTO t1 VALUES (1);
CREATE FUNCTION f1() RETURNS INT RETURN 1;
HANDLER t1 OPEN;
HANDLER t1 READ FIRST WHERE f1() = 1;

This will give: ERROR 1305 (42000): FUNCTION test.f1 does not exist
If e.g. SELECT f1() is done first, the statement completes.

Example 2:

CREATE TABLE t1 (a INT);
INSERT INTO t1 VALUES (1);
CREATE FUNCTION f1() RETURNS INT RETURN sleep(10);
SELECT f1();
HANDLER t1 OPEN;
HANDLER t1 READ FIRST WHERE f1() = 0;

It's now possible to DROP FUNCTION f1; from a different connection
while the HANDLER statement executes.

Example 3:

--source include/master-slave.inc
CREATE TABLE t1 (a INT);
CREATE TABLE t2 (a INT);
INSERT INTO t1 VALUES (1);
delimiter |;
CREATE FUNCTION f1() RETURNS INT
BEGIN
  INSERT INTO t2 VALUES (1);
  RETURN 1;
END|
delimiter ;|
SELECT f1();
DELETE FROM t2;
HANDLER t1 OPEN;
HANDLER t1 READ FIRST WHERE f1() = 1;
HANDLER t1 CLOSE;
--sync_slave_with_master

Neither the master nor the slave will have any tuples in t2 now,
even if f1() was executed by the HANDLER statement.
Before sync_slave_with_master, the master will have 1 tuple in t2,
the slave none.

Suggested fix:
Consider if stored functions should be supported for HANDLER statements.
[30 Jun 2010 17:15] Valeriy Kravchuk
Verified just as described:

valeriy-kravchuks-macbook-pro:5.1 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.1.49-debug Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CREATE TABLE t1 (a INT);
Query OK, 0 rows affected (0.06 sec)

mysql> INSERT INTO t1 VALUES (1);
Query OK, 1 row affected (0.00 sec)

mysql> CREATE FUNCTION f1() RETURNS INT RETURN 1;
Query OK, 0 rows affected (0.04 sec)

mysql> HANDLER t1 OPEN;
Query OK, 0 rows affected (0.00 sec)

mysql> HANDLER t1 READ FIRST WHERE f1() = 1;
ERROR 1305 (42000): FUNCTION test.f1 does not exist
mysql> select f1();
+------+
| f1() |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

mysql> HANDLER t1 READ FIRST WHERE f1() = 1;
+------+
| a    |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

and so on...
[21 Sep 2010 11:36] 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/118705

3141 Jon Olav Hauglid	2010-09-21
      Bug #54920 Stored functions are allowed in HANDLER statements,
                 but broken.
      
      Before this patch, it was allowed to use stored functions in
      HANDLER ... READ statements. The problem was that this functionality
      was not really supported by the code. Proper locking would for example
      not be performed, and it was also possible to break replication by
      having stored functions that performed updates.
      
      This patch disallows the use of stored functions in HANDLER ... READ.
      Any such statement will now give a syntax error. This is an incompatible
      change and should be reflected in the documentation.
      
      Test case added to handler_myisam/handler_innodb.test.
[23 Sep 2010 17:25] Dmitry Lenev
Approved with minor suggestion sent by e-mail.
[24 Sep 2010 7:32] 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/118995

3144 Jon Olav Hauglid	2010-09-24
      Bug #54920 Stored functions are allowed in HANDLER statements,
                 but broken.
      
      Before this patch, it was allowed to use stored functions in
      HANDLER ... READ statements. The problem was that this functionality
      was not really supported by the code. Proper locking would for example
      not be performed, and it was also possible to break replication by
      having stored functions that performed updates.
      
      This patch disallows the use of stored functions in HANDLER ... READ.
      Any such statement will now give an ER_NOT_SUPPORTED_YET error.
      This is an incompatible change and should be reflected in the
      documentation.
      
      Test case added to handler_myisam/handler_innodb.test.
[24 Sep 2010 7:33] Jon Olav Hauglid
Pushed to mysql-5.5-runtime (5.5.7-m3).
[9 Nov 2010 19:43] Bugs System
Pushed into mysql-5.5 5.5.7-rc (revid:sunanda.menon@sun.com-20101109182959-otkxq8vo2dcd13la) (version source revid:marko.makela@oracle.com-20100824081003-v4ecy0tga99cpxw2) (merge vers: 5.1.50) (pib:21)
[12 Nov 2010 0:46] Paul Dubois
Noted in 5.5.7 changelog.

HANDLER ... READ statements that invoke stored functions can cause
replication errors. Such statements are now disallowed and result in
an ER_NOT_SUPPORTED_YET error.
[13 Nov 2010 16:11] Bugs System
Pushed into mysql-trunk 5.6.99-m5 (revid:alexander.nozdrin@oracle.com-20101113155825-czmva9kg4n31anmu) (version source revid:marko.makela@oracle.com-20100824081003-v4ecy0tga99cpxw2) (merge vers: 5.1.50) (pib:21)
[13 Nov 2010 16:31] Bugs System
Pushed into mysql-next-mr (revid:alexander.nozdrin@oracle.com-20101113160336-atmtmfb3mzm4pz4i) (version source revid:marko.makela@oracle.com-20100824081003-v4ecy0tga99cpxw2) (pib:21)