[MDEV-15890] Strange error message if you try to FLUSH TABLES <view> after LOCK TABLES <view> - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Critical
Resolution: Fixed
Affects Version/s: 5.5(EOL), 10.0(EOL), 10.1(EOL), 10.2(EOL), 10.3(EOL)
Fix Version/s: 10.3.11, 10.2.19, 10.1.38
Component/s: Admin statements, Locking
Labels:
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

Ascending order - Click to sort in descending order

Geoff Montee (Inactive) created issue - 2018-04-16 19:07

Geoff Montee (Inactive) made changes - 2018-04-16 19:10

Field	Original Value	New Value
Summary	Wrong error message if you try to FLUSH TABLES <view> after LOCK TABLES <view> READ	Strange error message if you try to FLUSH TABLES <view> after LOCK TABLES <view>

Geoff Montee (Inactive) made changes - 2018-04-16 19:11

Description

If you try to execute FLUSH TABLES on a view after read-locking that view with LOCK TABLES ... READ, then you get an error message that doesn't seem to make a lot of sense.

For example, if we have this view:

{noformat}
CREATE TABLE t (qty INT, price INT);
CREATE VIEW v AS SELECT qty, price, qty*price AS value FROM t;
{noformat}

Then let's see what happens:

{noformat}
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
{noformat}

If we execute the same statement on the base table, then we get a different error message:

{noformat}
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
{noformat}

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:

{noformat}
CREATE TABLE t (qty INT, price INT);
CREATE VIEW v AS SELECT qty, price, qty*price AS value FROM t;
{noformat}

Then let's see what happens:

{noformat}
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
{noformat}

If we execute the same statement on the base table, then we get a different error message:

{noformat}
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
{noformat}

Trying to flush a write-locked view has the same problem:

{noformat}
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
{noformat}

But this does work for base tables:

{noformat}
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)
{noformat}

Is the error message wrong, or is FLUSH partially broken for views at the moment?

Elena Stepanova made changes - 2018-04-16 19:18

Component/s		Locking [ 10900 ]
Fix Version/s		10.2 [ 14601 ]
Assignee		Sergey Vojtovich [ svoj ]

Sergei Golubchik made changes - 2018-04-17 09:06

Affects Version/s		10.2 [ 14601 ]
Affects Version/s		10.3 [ 22126 ]
Affects Version/s		5.5 [ 15800 ]
Affects Version/s		10.0 [ 16000 ]
Affects Version/s		10.1 [ 16100 ]
Affects Version/s	10.2.14 [ 22911 ]

Sergei Golubchik added a comment - 2018-04-17 09:16

I don't see a bug here.

FLUSH TABLE requires write lock since 5.5. This is intentional.
The error message is not wrong, table 'v' was not locked with LOCK TABLES, indeed. Even view wasn't, LOCK TABLES v locks the underlying table t.

Sergei Golubchik added a comment - 2018-04-17 09:16 I don't see a bug here. FLUSH TABLE requires write lock since 5.5. This is intentional. The error message is not wrong, table 'v' was not locked with LOCK TABLES, indeed. Even view wasn't, LOCK TABLES v locks the underlying table t.

Sergei Golubchik made changes - 2018-04-17 09:16

Fix Version/s		N/A [ 14700 ]
Fix Version/s	10.2 [ 14601 ]
Assignee	Sergey Vojtovich [ svoj ]	Sergei Golubchik [ serg ]
Resolution		Not a Bug [ 6 ]
Status	Open [ 1 ]	Closed [ 6 ]

Elena Stepanova added a comment - 2018-04-17 10:19 - edited

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)

Elena Stepanova added a comment - 2018-04-17 10:19 - edited 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)

Sergei Golubchik added a comment - 2018-04-24 07:06

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.

Sergei Golubchik added a comment - 2018-04-24 07:06 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.

Sergei Golubchik added a comment - 2018-04-30 16:17

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.

Sergei Golubchik added a comment - 2018-04-30 16:17 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 .

Sergei Golubchik made changes - 2018-04-30 16:17

Assignee	Sergei Golubchik [ serg ]	Alexey Botchkov [ holyfoot ]
Resolution	Not a Bug [ 6 ]
Status	Closed [ 6 ]	Stalled [ 10000 ]

Sergei Golubchik made changes - 2018-04-30 16:17

Fix Version/s		10.2 [ 14601 ]
Fix Version/s		10.3 [ 22126 ]
Fix Version/s		10.1 [ 16100 ]
Fix Version/s	N/A [ 14700 ]

Alexey Botchkov made changes - 2018-06-06 12:15

Status

Stalled [ 10000 ]

In Progress [ 3 ]

Alexey Botchkov added a comment - 2018-06-11 12:06

http://lists.askmonty.org/pipermail/commits/2018-June/012606.html

Alexey Botchkov added a comment - 2018-06-11 12:06 http://lists.askmonty.org/pipermail/commits/2018-June/012606.html

Alexey Botchkov made changes - 2018-06-11 12:06

Assignee	Alexey Botchkov [ holyfoot ]	Sergei Golubchik [ serg ]
Status	In Progress [ 3 ]	In Review [ 10002 ]

Sergei Golubchik made changes - 2018-06-12 16:16

Assignee	Sergei Golubchik [ serg ]	Alexey Botchkov [ holyfoot ]
Status	In Review [ 10002 ]	Stalled [ 10000 ]

Julien Fritsch made changes - 2018-06-15 09:29

Comment

[ A comment with security level 'Developers' was removed. ]

Alexey Botchkov added a comment - 2018-06-21 12:50

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.
----------------------------------------------------

Alexey Botchkov added a comment - 2018-06-21 12:50 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. ----------------------------------------------------

Alexey Botchkov made changes - 2018-06-22 08:29

Status

Stalled [ 10000 ]

In Progress [ 3 ]

Alexey Botchkov added a comment - 2018-06-24 19:46

Fix proposal v2
http://lists.askmonty.org/pipermail/commits/2018-June/012645.html

Alexey Botchkov added a comment - 2018-06-24 19:46 Fix proposal v2 http://lists.askmonty.org/pipermail/commits/2018-June/012645.html

Alexey Botchkov made changes - 2018-06-24 19:46

Assignee	Alexey Botchkov [ holyfoot ]	Sergei Golubchik [ serg ]
Status	In Progress [ 3 ]	In Review [ 10002 ]

Sergei Golubchik made changes - 2018-08-31 13:08

Assignee	Sergei Golubchik [ serg ]	Alexey Botchkov [ holyfoot ]
Status	In Review [ 10002 ]	Stalled [ 10000 ]

Alexey Botchkov made changes - 2018-09-02 06:00

Status

Stalled [ 10000 ]

In Progress [ 3 ]

Alexey Botchkov added a comment - 2018-09-02 06:00

http://lists.askmonty.org/pipermail/commits/2018-September/012870.html

Alexey Botchkov added a comment - 2018-09-02 06:00 http://lists.askmonty.org/pipermail/commits/2018-September/012870.html

Alexey Botchkov made changes - 2018-09-02 06:00

Fix Version/s		10.1.36 [ 23117 ]
Fix Version/s	10.2 [ 14601 ]
Fix Version/s	10.1 [ 16100 ]
Fix Version/s	10.3 [ 22126 ]
Resolution		Fixed [ 1 ]
Status	In Progress [ 3 ]	Closed [ 6 ]

Alexey Botchkov made changes - 2018-09-03 13:26

Resolution	Fixed [ 1 ]
Status	Closed [ 6 ]	Stalled [ 10000 ]

Alexey Botchkov added a comment - 2018-09-03 13:27

http://lists.askmonty.org/pipermail/commits/2018-September/012873.html

Alexey Botchkov added a comment - 2018-09-03 13:27 http://lists.askmonty.org/pipermail/commits/2018-September/012873.html

Alexey Botchkov made changes - 2018-09-03 13:27

Assignee	Alexey Botchkov [ holyfoot ]	Sergei Golubchik [ serg ]
Status	Stalled [ 10000 ]	In Review [ 10002 ]

Sergei Golubchik made changes - 2018-09-04 08:38

Assignee	Sergei Golubchik [ serg ]	Alexey Botchkov [ holyfoot ]
Status	In Review [ 10002 ]	Stalled [ 10000 ]

Sergei Golubchik made changes - 2018-09-07 18:45

Fix Version/s		10.1 [ 16100 ]
Fix Version/s	10.1.36 [ 23117 ]

Alexey Botchkov added a comment - 2018-09-11 07:14

http://lists.askmonty.org/pipermail/commits/2018-September/012905.html

Alexey Botchkov added a comment - 2018-09-11 07:14 http://lists.askmonty.org/pipermail/commits/2018-September/012905.html

Alexey Botchkov made changes - 2018-09-11 07:14

Assignee	Alexey Botchkov [ holyfoot ]	Sergei Golubchik [ serg ]
Status	Stalled [ 10000 ]	In Review [ 10002 ]

Rasmus Johansson (Inactive) made changes - 2018-09-17 08:27

Link

This issue relates to ~~MDEV-15888~~ [ ~~MDEV-15888~~ ]

Julien Fritsch made changes - 2018-09-25 13:42

Priority

Major [ 3 ]

Critical [ 2 ]

Sergei Golubchik added a comment - 2018-10-17 18:37

ok to push

Sergei Golubchik added a comment - 2018-10-17 18:37 ok to push

Sergei Golubchik made changes - 2018-10-17 18:37

Assignee	Sergei Golubchik [ serg ]	Alexey Botchkov [ holyfoot ]
Status	In Review [ 10002 ]	Stalled [ 10000 ]

Sergei Golubchik made changes - 2018-10-30 16:57

Fix Version/s

10.2 [ 14601 ]

Sergei Golubchik made changes - 2018-10-30 16:57

Fix Version/s

10.3 [ 22126 ]

Alexey Botchkov made changes - 2018-11-04 05:19

Fix Version/s		10.1.38 [ 23209 ]
Fix Version/s	10.2 [ 14601 ]
Fix Version/s	10.1 [ 16100 ]
Fix Version/s	10.3 [ 22126 ]
Resolution		Fixed [ 1 ]
Status	Stalled [ 10000 ]	Closed [ 6 ]

Alexey Botchkov made changes - 2018-11-05 09:24

Fix Version/s		10.3.11 [ 23141 ]
Fix Version/s		10.2.19 [ 23207 ]

Sergei Golubchik made changes - 2021-12-06 21:47

Workflow

MariaDB v3 [ 86584 ]

MariaDB v4 [ 154167 ]

Jira Automation (IT) made changes - 2024-07-04 07:39

Zendesk Related Tickets

163917

People

Assignee:: Alexey Botchkov

Reporter:: Geoff Montee (Inactive)

Votes:: 0 Vote for this issue

Watchers:: 4 Start watching this issue

Dates

Created:: 2018-04-16 19:07

Updated:: 2024-07-08 00:05

Resolved:: 2018-11-04 05:19

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server

Details

Description

Attachments

Issue Links

Activity

People

Dates

Git Integration