[#MDEV-3943] MySQL opening .frm even when table is in table definition cache

Comment by Sergei Golubchik [ 2012-12-17 ]

According to Monty, this only happens for LOCK TABLES on a view and SHOW TABLES

Comment by Arjen Lentz [ 2012-12-18 ]

The original post indicates that it happens for various DDL operations, but also any general "open table".
Of course it's easily checked if you have a trace or debug mysqld going, which I currently don't.

Will just doing a select on a table twice should do the trick? If we have a method to trigger the situation (if it still exists), then someone testing something else with a debug or trace could check this on the side some time soon - or I can do it.

Comment by Michael Widenius [ 2012-12-18 ]

Just doing select on a table will not open the .frm twice (just verified it).
In other words, it does not happen for a general open table.

It can easily be checked by examining 'opened_files' in show status.

Comment by Michael Widenius [ 2012-12-18 ]

This is mainly a problem with VIEW and DROP and RENAME TABLE.

At least the DROP TABLE issue should be worth fixing.

Comment by Arjen Lentz [ 2012-12-18 ]

Stewart Smith pasted something into #maria when Monty, he and I were discussing this topic 2012-12-18.

I think the bit I was talking about is op open_table() in sql_base.cc, namely this bit

/*
Note that we can't be 100% sure that it is a view since it's
possible that we either simply have not found unused TABLE
instance in THD::open_tables list or were unable to open table
during prelocking process (in this case in theory we still
should hold shared metadata lock on it).
*/

if (dd_frm_type(thd, path, &not_used) == FRMTYPE_VIEW)
...

Comment by Elena Stepanova [ 2014-10-20 ]

It looks like it was fixed in 10.0.2, my suspicion is – as a part of table discovery implementation.

See strace fragments related to t1.frm and t2.frm for 10.0.1 and 10.0.2 for the following test case:

create table t1 (i int);

insert into t1 values (1);

rename table t1 to t2;

insert into t2 values (2);

drop table t2;

strace fragments from 10.0.1
write(8, "\t\t 2 Query\tcreate table t1 (i"..., 38) = 38
...
access("./test/t1.frm", F_OK) = -1 ENOENT (No such file or directory)
...
access("./test/t1.frm", F_OK) = -1 ENOENT (No such file or directory)
access("./test/t1.frm", F_OK) = -1 ENOENT (No such file or directory)
...
open("./test/t1.frm", O_RDWR\|O_CREAT\|O_TRUNC, 0660) = 51
...
open("./test/t1.frm", O_RDONLY) = 51
...

write(8, "\t\t 2 Query\tinsert into t1 val"..., 40) = 40
...
open("./test/t1.frm", O_RDONLY) = 51
...

write(8, "\t\t 2 Query\trename table t1 to"..., 36) = 36
...
access("./test/t2.frm", F_OK) = -1 ENOENT (No such file or directory)
access("./test/t2.frm", F_OK) = -1 ENOENT (No such file or directory)
...
open("./test/t1.frm", O_RDONLY) = 51
...
rename("./test/t1.frm", "./test/t2.frm") = 0
...

write(8, "\t\t 2 Query\tinsert into t2 val"..., 40) = 40
...
open("./test/t2.frm", O_RDONLY) = 57
...

write(8, "\t\t 2 Query\tdrop table t2\n", 28) = 28
...
access("./test/t2.frm", F_OK) = 0
...
open("./test/t2.frm", O_RDONLY) = 57
...
unlink("./test/t2.frm") = 0
...

strace fragments from 10.0.2

write(8, "\t\t 2 Query\tcreate table t1 (i"..., 38) = 38
...
access("./test/t1.frm", F_OK) = -1 ENOENT (No such file or directory)
...
access("./test/t1.frm", F_OK) = -1 ENOENT (No such file or directory)
...
open("./test/t1.frm", O_RDWR\|O_CREAT\|O_TRUNC, 0660) = 51
...

write(8, "\t\t 2 Query\tinsert into t1 val"..., 40) = 40
...
open("./test/t1.frm", O_RDONLY) = 51
...

write(8, "\t\t 2 Query\trename table t1 to"..., 36) = 36
...
access("./test/t2.frm", F_OK) = -1 ENOENT (No such file or directory)
...
rename("./test/t1.frm", "./test/t2.frm") = 0
...

write(8, "\t\t 2 Query\tinsert into t2 val"..., 40) = 40
...
open("./test/t2.frm", O_RDONLY) = 57
...

write(8, "\t\t 2 Query\tdrop table t2\n", 28) = 28
...
unlink("./test/t2.frm") = 0

So, I'm closing it as fixed in 10.0 (set 10.0.6 in Fix version because it's the lowest available on the list, but in fact it's 10.0.2).
If anyone disagrees, please comment and/or re-open.

[MDEV-3943] MySQL opening .frm even when table is in table definition cache Created: 2012-12-17 Updated: 2014-10-20 Resolved: 2014-10-20
Status:	Closed
Project:	MariaDB Server
Component/s:	OTHER
Affects Version/s:	10.0.0, 5.5.28a, 5.3.11, 5.2.13, 5.1.66
Fix Version/s:	10.0.6

[MDEV-3943] MySQL opening .frm even when table is in table definition cache Created: 2012-12-17 Updated: 2014-10-20 Resolved: 2014-10-20