MySQL Bugs: #29071: invalid Open_files or Slave_open_temp

Bug #29071	invalid Open_files or Slave_open_temp_tables value in show global status
Submitted:	13 Jun 2007 14:15	Modified:	4 Jul 2008 6:38
Reporter:	Shane Bester
Status:	Analyzing
Category:	Server: I_S	Severity:	S3 (Non-critical)
Version:	5.0.66, 5.0.72	OS:	Microsoft Windows (vista, xp)
Assigned to:	Shane Bester	Target Version:
Tags:	Slave_open_temp_tables, status variables, Open_files
Triage:	D3 (Medium) / R2 (Low) / E2 (Low)

Description:
I saw this today:

mysql> show global status like '%ope%';
+------------------------+------------+
| Variable_name          | Value      |
+------------------------+------------+
| Com_ha_open            | 0          |
| Com_show_open_tables   | 2          |
| Open_files             | 4294967173 |<--------
| Open_streams           | 0          |
| Open_table_definitions | 2          |
| Open_tables            | 1          |
| Opened_tables          | 7319       |
| Slave_open_temp_tables | 0          |
+------------------------+------------+
8 rows in set (0.00 sec)

How to repeat:
no testcase yet.

ran mysqld with --flush-time=1 --table_open_cache=300 --open_files_limit=2030

and did a bunch of operations on tables with over 1000 partitions.

Suggested fix:
either find the code that forgets to increment/decrement the counter - or protect the
variable with a mutex to avoid dirty reads by show global status.

Saw this again today.  While running long-running tests for bug #29838 on windows, I had
not correctly set the maximum number of open files mysqld-nt.exe can access.  Also, I
guess too many connections.  All this caused many error 24 to be returned.

DBD::mysql::st execute failed: Can't open file: '.\test\test0.frm' (errno: 24) at go.pl
DBD::mysql::st execute failed: Can't open file: '.\test\test0.frm' (errno: 24) at go.pl
DBD::mysql::st execute failed: Can't open file: '.\test\test0.frm' (errno: 24) at go.pl
DBD::mysql::st execute failed: Can't open file: '.\test\test2.frm' (errno: 24) at go.pl
DBD::mysql::st execute failed: Can't open file: '.\test\test2.frm' (errno: 24) at go.pl

mysql> show global status like '%open%';
+------------------------+------------+
| Variable_name          | Value      |
+------------------------+------------+
| Com_ha_open            | 0          |
| Com_show_open_tables   | 1          |
| Open_files             | 4294956328 | <-------- impossible
| Open_streams           | 0          |
| Open_tables            | 1          |
| Opened_tables          | 684873     |
| Slave_open_temp_tables | 0          |
+------------------------+------------+
7 rows in set (0.00 sec)

I guess some path in the code is not decrementing the counter. Some path can be when
either MYI or MYD isn't opened?

start with --skip-grant-tables --open_files_limit=16 --table_cache=1 --max_connections=1
and paste the sql..

Attachment: bug29071_testcase.sql (application/octet-stream, text), 6.55 KiB.

above testcase ends like this on my 5.0.48 on Vista:

00 rows in set (4.92 sec)

ysql> show global status like '%open_files%';
---------------+------------+
 Variable_name | Value      |
---------------+------------+
 Open_files    | 4294967208 |
---------------+------------+
 row in set (0.00 sec)

I suspect that my_file_opened is decremented too many times.

tested on Windows XP and Linux; cannot reproduce there;
presumed fixed by the Bug#31066 / Bug#31177 / Bug#32757 patch family.

More specifically, test with current bk version 5.0.56

yes, 5.1.24 does indeed look fixed:

...
mysql> show global status like '%open_files%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Open_files    | 2     |
+---------------+-------+
1 row in set (0.01 sec)

mysql> select version();
+-----------------+
| version()       |
+-----------------+
| 5.1.24-rc-debug |
+-----------------+
1 row in set (0.00 sec)

reopening this bug and assigning to myself.  the problem still exists in 5.0.66:

mysql> show global status where variable_name in ('open_files');
+---------------+------------+
| Variable_name | Value      |
+---------------+------------+
| Open_files    | 4294965401 |
+---------------+------------+
1 row in set (0.02 sec)

mysql> select version();
+--------------+
| version()    |
+--------------+
| 5.0.66-debug |
+--------------+
1 row in set (0.03 sec)

easy to reproduce the bug on 5.0.72 when alot of temporary tables are created and dropped.