Bug #12472 CREATE TABLE t AS SELECT spfunc gives "Table not locked with LOCK TABLES" error
Submitted: 9 Aug 2005 20:59 Modified: 22 May 2006 20:09
Reporter: Sergey Petrunya Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Locking Severity:S3 (Non-critical)
Version:5.0-bk OS:Any (any)
Assigned to: Dmitry Lenev CPU Architecture:Any

[9 Aug 2005 20:59] Sergey Petrunya
Description:
Attempt to run a statement 
CREATE TABLE t AS SELECT spfunc() 
where spfunc uses a table produces "Table ... was not locked with LOCK TABLES".  

How to repeat:
Run:
create table t1 as select 1 a;
delimiter //
create function f2() returns int deterministic reads sql data 
begin  
  return (select max(a) from t1); 
end; //

delimiter //

create table t2 as select f2();

And get the error:
ERROR 1100 (HY000): Table 't2' was not locked with LOCK TABLES

Suggested fix:
I think such statements should be supported. If they aren't, we need to document what class of statements is not supported (e.g. no SP function calls in DDL statements??).
[2 Oct 2005 2:45] Markus Popp
I also just found this problem. I think this should be solved before MySQL 5 becomes a Production Release.

Here's my example:

mysql> DROP TABLE IF EXISTS t1;
Query OK, 0 rows affected (0.06 sec)

mysql> CREATE TABLE t1 (
    ->   id int(11) NOT NULL auto_increment,
    ->   `name` varchar(20) NOT NULL default '',
    ->   decval decimal(6,2) NOT NULL default '0.00',
    ->   PRIMARY KEY  (id),
    ->   KEY ind_decval (decval)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.08 sec)

mysql> INSERT INTO t1 (id, name, decval) VALUES (1, 'abc', 4.00);
Query OK, 1 row affected (0.06 sec)

mysql> INSERT INTO t1 (id, name, decval) VALUES (2, 'def', 9.70);
Query OK, 1 row affected (0.06 sec)

mysql> INSERT INTO t1 (id, name, decval) VALUES (3, 'ghi', 6.40);
Query OK, 1 row affected (0.06 sec)

mysql> INSERT INTO t1 (id, name, decval) VALUES (4, 'slf', 4.20);
Query OK, 1 row affected (0.05 sec)

mysql> DROP TABLE IF EXISTS t2;
Query OK, 0 rows affected, 1 warning (0.05 sec)

mysql> CREATE TABLE t2 (
    ->   id int(11) NOT NULL auto_increment,
    ->   id_t1 int(11) NOT NULL default '0',
    ->   decval decimal(6,2) NOT NULL default '0.00',
    ->   PRIMARY KEY  (id),
    ->   KEY id_t1 (id_t1),
    ->   KEY ind_decval (decval)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.09 sec)

mysql> INSERT INTO t2 (id, id_t1, decval) VALUES (1, 2, 8.50);
Query OK, 1 row affected (0.06 sec)

mysql> INSERT INTO t2 (id, id_t1, decval) VALUES (2, 1, 8.50);
Query OK, 1 row affected (0.05 sec)

mysql> INSERT INTO t2 (id, id_t1, decval) VALUES (4, 2, 6.20);
Query OK, 1 row affected (0.06 sec)

mysql> INSERT INTO t2 (id, id_t1, decval) VALUES (5, 3, 4.00);
Query OK, 1 row affected (0.05 sec)

mysql> INSERT INTO t2 (id, id_t1, decval) VALUES (6, 2, 4.20);
Query OK, 1 row affected (0.05 sec)

mysql> INSERT INTO t2 (id, id_t1, decval) VALUES (8, 3, 5.60);
Query OK, 1 row affected (0.06 sec)

mysql> INSERT INTO t2 (id, id_t1, decval) VALUES (9, 4, 8.10);
Query OK, 1 row affected (0.06 sec)

mysql> INSERT INTO t2 (id, id_t1, decval) VALUES (10, 2, 0.70);
Query OK, 1 row affected (0.06 sec)

mysql> INSERT INTO t2 (id, id_t1, decval) VALUES (12, 1, 4.50);
Query OK, 1 row affected (0.05 sec)

mysql> INSERT INTO t2 (id, id_t1, decval) VALUES (29, 3, 5.60);
Query OK, 1 row affected (0.06 sec)

mysql> INSERT INTO t2 (id, id_t1, decval) VALUES (30, 4, 8.10);
Query OK, 1 row affected (0.05 sec)

mysql> INSERT INTO t2 (id, id_t1, decval) VALUES (31, 2, 0.70);
Query OK, 1 row affected (0.06 sec)

mysql> INSERT INTO t2 (id, id_t1, decval) VALUES (33, 1, 4.50);
Query OK, 1 row affected (0.05 sec)

mysql> ALTER TABLE `t2`
    ->   ADD CONSTRAINT t2_ibfk_1 FOREIGN KEY (id_t1) REFERENCES t1 (id);
Query OK, 13 rows affected (0.08 sec)
Records: 13  Duplicates: 0  Warnings: 0

mysql> DELIMITER $$
mysql>
mysql> DROP FUNCTION IF EXISTS getValue$$
Query OK, 0 rows affected (0.11 sec)

mysql> CREATE FUNCTION  getValue(_id INT) RETURNS decimal(6,2)
    -> BEGIN
    ->   DECLARE _retval DECIMAL(6,2);
    ->
    ->   SELECT decval FROM t1 WHERE id = _id INTO _retval;
    ->
    ->   RETURN _retval;
    ->
    -> END$$
Query OK, 0 rows affected (0.06 sec)

mysql>
mysql> DELIMITER ;
mysql> drop table if exists t3;
Query OK, 0 rows affected, 1 warning (0.05 sec)

mysql> show warnings;
+-------+------+--------------------+
| Level | Code | Message            |
+-------+------+--------------------+
| Note  | 1051 | Unknown table 't3' |
+-------+------+--------------------+
1 row in set (0.06 sec)

mysql> create table t3 select id, getValue(id_t1) as decval_t1, decval as decval
_t2 from t2;
ERROR 1100 (HY000): Table 't3' was not locked with LOCK TABLES
mysql>
[19 Jan 2006 6:36] Gregg Green
I have the same basic problem. I want to create a table from a VIEW that contains calculated columns that use functions. If I remove the calculated columns from the view the problem goes away. I can view all records in the view with a select statement, so I would expect to be able to create a table using the same select statement.
[8 Feb 2006 11:32] Dmitry Lenev
Bug #14150 is marked as duplicate of this bug.
[3 Apr 2006 15:01] Beat Vontobel
The original bug description "where spfunc uses a table" is not completely correct. The bug occurs also on a deterministic function that doesn't make use of any tables if it resides in another database:

localhost-test [root]> use test;
Database changed
localhost-test [root]> create function f() returns int deterministic return 1;
Query OK, 0 rows affected (0.52 sec)

localhost-(none) [root]> create database test2;
Query OK, 1 row affected (0.00 sec)

localhost-(none) [root]> use test2
Database changed
localhost-test2 [root]> create table t1 (i INT);
Query OK, 0 rows affected (0.00 sec)

localhost-test2 [root]> insert into t1 values (1), (2);
Query OK, 2 rows affected (0.24 sec)
Records: 2  Duplicates: 0  Warnings: 0

localhost-test2 [root]> CREATE TABLE t2 SELECT test.f() FROM t1;
ERROR 1100 (HY000): Table 't2' was not locked with LOCK TABLES
localhost-test2 [root]> CREATE TABLE test2.t2 SELECT test.f() FROM test2.t1;
ERROR 1100 (HY000): Table 't2' was not locked with LOCK TABLES

It's the same behaviour from a users point of view (that's why I added it here), but it might come from a different source internally (so please tell me, if I should open a separate report for this), it could for example be related to my bug #18444.
[20 Apr 2006 20:51] Konstantin Osipov
This bug is fixed by the same patch that fixes Bug#15137
[18 May 2006 21:06] Dmitry Lenev
Fixed in 5.0.22 and 5.1.11
[18 May 2006 21:12] Dmitry Lenev
CREATE TABLE ... SELECT ... statement which was explicitly or implicitly
(through view) using stored function gave "Table not locked" error.
[22 May 2006 20:09] Paul DuBois
Noted in 5.0.22, 5.1.11 changelogs.