Details
-
Type:
Bug
-
Status: Closed
-
Priority:
Major
-
Resolution: Not a Bug
-
Affects Version/s: None
-
Fix Version/s: None
-
Component/s: None
-
Labels:None
Description
1) Open a console and run:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 8
Server version: 5.5.30-MariaDB mariadb.org binary distribution
Copyright (c) 2000, 2013, Oracle, Monty Program Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> DELIMITER ||
MariaDB [(none)]> DROP FUNCTION IF EXISTS `test`.`f`;
-> CREATE FUNCTION `test`.`f`() RETURNS TINYINT UNSIGNED
-> BEGIN
-> RETURN 1;
-> END;
-> ||
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.24 sec)
MariaDB [(none)]>
2) Open ANOTHER console and run:
MariaDB [test]> SELECT test.f();
+----------+
| test.f() |
+----------+
| 1 |
+----------+
1 row in set (0.00 sec)
MariaDB [test]>
3) Go back to the first console, and run:
MariaDB [(none)]> DROP FUNCTION IF EXISTS `test`.`f`;
-> ||
Everything freezes. Then I ask for a processlist using the second console:
MariaDB [test]> SHOW FULL PROCESSLIST; +----+------+----------------+------+---------+------+-------------------------------------------+------------------------------------+----------+ | Id | User | Host | db | Command | Time | State | Info | Progress | +----+------+----------------+------+---------+------+-------------------------------------------+------------------------------------+----------+ | 6 | root | localhost:3504 | test | Query | 0 | NULL | SHOW FULL PROCESSLIST | 0.000 | | 8 | root | localhost:3508 | NULL | Query | 594 | Waiting for stored function metadata lock | DROP FUNCTION IF EXISTS `test`.`f` | 0.000 | +----+------+----------------+------+---------+------+-------------------------------------------+------------------------------------+----------+ 2 rows in set (0.00 sec) MariaDB [test]>
This always happens with 5.5.30 and 10.0.2 on WinXP. Didn't try Linux or older Maria versions. Happens with both functions and procedures.
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
Hi Elena,
You are right, it's 0 by default, sorry. I verified that this doesn't happen in 5.2, even with autocommit=0... (I'm missing something, I suppose?)
No, you aren't missing anything, it's yet another side-effect of the new metadata-locking system introduced in 5.5 (MySQL 5.5.3, if I remember correctly). Basically, the objects are now locked for the duration of the transaction, even if the objects themselves are not "transactional" (e.g. it would happen with a MyISAM table as well). It's been discussed with authors of the MDL, for example here: http://lists.mysql.com/internals/38583. If you want to resume the discussion, please feel free to do so.
Please note that the lock is not "eternal", it does have a timeout (lock_wait_timeout, it's just that the default value for the timeout is 1 YEAR. You can reduce it in your config file to any value down to 1 sec, to get rid of the "hang"; but of course the behavior will still be not the same as it was before, the DDL operation will fail with the timeout.
Closing as not a bug, please comment to re-open if the explanation is not satisfactory.
Hi Federico,
It would normally happen if you have AUTOCOMMIT=0 in your second connection (the one that does SELECT). Is it the case? It can be somewhere in config files, options, etc.
You can check it by executing
SELECT @@autocommit;