Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
5.5(EOL), 10.0(EOL), 10.1(EOL), 10.2(EOL), 10.3(EOL)
-
None
Description
If you try to execute FLUSH TABLES on a view after read-locking that view with LOCK TABLES ... READ|WRITE, then you get an error message that doesn't seem to make a lot of sense.
For example, if we have this view:
CREATE TABLE t (qty INT, price INT);
|
CREATE VIEW v AS SELECT qty, price, qty*price AS value FROM t;
|
Then let's see what happens:
MariaDB [db1]> LOCK TABLES v READ;
|
Query OK, 0 rows affected (0.00 sec)
|
|
|
MariaDB [db1]> FLUSH TABLES v;
|
ERROR 1100 (HY000): Table 'v' was not locked with LOCK TABLES
|
If we execute the same statement on the base table, then we get a different error message:
MariaDB [db1]> LOCK TABLES t READ;
|
Query OK, 0 rows affected (0.00 sec)
|
|
|
MariaDB [db1]> FLUSH TABLES t;
|
ERROR 1099 (HY000): Table 't' was locked with a READ lock and can't be updated
|
Trying to flush a write-locked view has the same problem:
MariaDB [db1]> LOCK TABLES v WRITE;
|
Query OK, 0 rows affected (0.00 sec)
|
|
|
MariaDB [db1]> FLUSH TABLES v;
|
ERROR 1100 (HY000): Table 'v' was not locked with LOCK TABLES
|
But this does work for base tables:
MariaDB [db1]> LOCK TABLES t WRITE;
|
Query OK, 0 rows affected (0.00 sec)
|
|
|
MariaDB [db1]> FLUSH TABLES t;
|
Query OK, 0 rows affected (0.00 sec)
|
Is the error message wrong, or is FLUSH partially broken for views at the moment?
Attachments
Issue Links
- relates to
-
MDEV-15888 Implement FLUSH TABLES tbl_name [, tbl_name] ... WITH READ LOCK for views
-
- Closed
-
Activity
For the record, I agree with GeoffMontee that the error message is wrong in the sense that it's confusing and normal-user-unfriendly, even if it's factually correct. For example, ER_IT_IS_A_VIEW would make much more sense, and probably we have more of the kind.
Also, FWIW, while a table v of course wasn't locked here, the view v actually was:
MariaDB [test]> lock tables v read; |
Query OK, 0 rows affected (0.00 sec) |
|
|
MariaDB [test]> select * from information_schema.metadata_lock_info; |
+-----------+----------------------+---------------+---------------------+--------------+------------+ |
| THREAD_ID | LOCK_MODE | LOCK_DURATION | LOCK_TYPE | TABLE_SCHEMA | TABLE_NAME |
|
+-----------+----------------------+---------------+---------------------+--------------+------------+ |
| 9 | MDL_SHARED_READ | NULL | Table metadata lock | test | v | |
| 9 | MDL_SHARED_READ_ONLY | NULL | Table metadata lock | test | t | |
+-----------+----------------------+---------------+---------------------+--------------+------------+ |
2 rows in set (0.00 sec) |
LOCK TABLE puts read/write table locks, not only metadata locks. See SHOW OPEN TABLES. And there is no way to put a table lock on a view.
On the other hand, if LOCK TABLES locks view's tables, perhaps FLUSH TABLES should check whether view's tables were locked, not whether the view itself was locked?
After all FLUSH TABLES also flushes tables and not the view itself.
Let's see if it's possible to get rid of this “strange” error message completely by allowing FLUSH TABLES v to work under LOCK TABLES v.
From Sergei's review:
------------------------------------------------------
That's a pretty neat solution 
But it seems that - contrary to what I expected - that MariaDB actually
distinguishes between locking a view a locking all its tables. Consider
this test case:
create table t1 (a int);
create view v1 as select * from t1;
lock table v1 write;
insert v1 values (1);
select * from t1;
unlock tables;
drop view v1;
drop table t1;
this works and inserts 1 just fine. If you change the lock table
statement to lock t1, it'll with
Table 'v1' was not locked with LOCK TABLES
It'd be great if FLUSH TABLES will exhibit the same behavior, it needs
to know if the view is locked, not only if all underlying tables are
locked.
----------------------------------------------------
I don't see a bug here.