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
| 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> |
| 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? |
| Component/s | Locking [ 10900 ] | |
| Fix Version/s | 10.2 [ 14601 ] | |
| Assignee | Sergey Vojtovich [ svoj ] |
| 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 ] |
| 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 ] |
| Assignee | Sergei Golubchik [ serg ] | Alexey Botchkov [ holyfoot ] |
| Resolution | Not a Bug [ 6 ] | |
| Status | Closed [ 6 ] | Stalled [ 10000 ] |
| Fix Version/s | 10.2 [ 14601 ] | |
| Fix Version/s | 10.3 [ 22126 ] | |
| Fix Version/s | 10.1 [ 16100 ] | |
| Fix Version/s | N/A [ 14700 ] |
| Status | Stalled [ 10000 ] | In Progress [ 3 ] |
| Assignee | Alexey Botchkov [ holyfoot ] | Sergei Golubchik [ serg ] |
| Status | In Progress [ 3 ] | In Review [ 10002 ] |
| Assignee | Sergei Golubchik [ serg ] | Alexey Botchkov [ holyfoot ] |
| Status | In Review [ 10002 ] | Stalled [ 10000 ] |
| Comment | [ A comment with security level 'Developers' was removed. ] |
| Status | Stalled [ 10000 ] | In Progress [ 3 ] |
| Assignee | Alexey Botchkov [ holyfoot ] | Sergei Golubchik [ serg ] |
| Status | In Progress [ 3 ] | In Review [ 10002 ] |
| Assignee | Sergei Golubchik [ serg ] | Alexey Botchkov [ holyfoot ] |
| Status | In Review [ 10002 ] | Stalled [ 10000 ] |
| Status | Stalled [ 10000 ] | In Progress [ 3 ] |
| 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 ] |
| Resolution | Fixed [ 1 ] | |
| Status | Closed [ 6 ] | Stalled [ 10000 ] |
| Assignee | Alexey Botchkov [ holyfoot ] | Sergei Golubchik [ serg ] |
| Status | Stalled [ 10000 ] | In Review [ 10002 ] |
| Assignee | Sergei Golubchik [ serg ] | Alexey Botchkov [ holyfoot ] |
| Status | In Review [ 10002 ] | Stalled [ 10000 ] |
| Fix Version/s | 10.1 [ 16100 ] | |
| Fix Version/s | 10.1.36 [ 23117 ] |
| Assignee | Alexey Botchkov [ holyfoot ] | Sergei Golubchik [ serg ] |
| Status | Stalled [ 10000 ] | In Review [ 10002 ] |
| Link |
This issue relates to |
| Priority | Major [ 3 ] | Critical [ 2 ] |
| Assignee | Sergei Golubchik [ serg ] | Alexey Botchkov [ holyfoot ] |
| Status | In Review [ 10002 ] | Stalled [ 10000 ] |
| Fix Version/s | 10.2 [ 14601 ] |
| Fix Version/s | 10.3 [ 22126 ] |
| 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 ] |
| Fix Version/s | 10.3.11 [ 23141 ] | |
| Fix Version/s | 10.2.19 [ 23207 ] |
| Workflow | MariaDB v3 [ 86584 ] | MariaDB v4 [ 154167 ] |
| Zendesk Related Tickets | 163917 |