MySQL Bugs: #59311: Tables structure and related data not visible.

Bug #59311	Tables structure and related data not visible.
Submitted:	5 Jan 2011 16:15	Modified:	7 Jan 2011 10:16
Reporter:	Vivek V	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Information schema	Severity:	S2 (Serious)
Version:	5.0.89	OS:	Linux (Ubuntu 6.06)
Assigned to:		CPU Architecture:	Any

Description:
After a restart of the MySQL instance, I get the following errors on ALL tables in ONLY 1 database:
"
mysql> check table ZTEST;
+-------------------+-------+----------+-----------------------------------------+
| Table             | Op    | Msg_type | Msg_text                                |
+-------------------+-------+----------+-----------------------------------------+
| essdata_dev.ztest | check | Error    | Table 'essdata_dev.ztest' doesn't exist |
| essdata_dev.ztest | check | status   | Operation failed                        |
+-------------------+-------+----------+-----------------------------------------+
2 rows in set (0.00 sec)

mysql> repair table ZTEST;
+-------------------+--------+----------+-----------------------------------------+
| Table             | Op     | Msg_type | Msg_text                                |
+-------------------+--------+----------+-----------------------------------------+
| essdata_dev.ztest | repair | Error    | Table 'essdata_dev.ztest' doesn't exist |
| essdata_dev.ztest | repair | status   | Operation failed                        |
+-------------------+--------+----------+-----------------------------------------+
2 rows in set (0.00 sec)

mysql> show tables like 'ZTE%';
+------------------------------+
| Tables_in_essdata_dev (ZTE%) |
+------------------------------+
| ZTEST                        |
+------------------------------+
1 row in set (0.01 sec)
"

So I am able to run 'show table' on the tables in the database but am not able to view any information on it. I queried the 'information_schema' and it does not seem to hold any information on any tables within the database. I am able to list all the tables within the database on the filesystem.
I am also not able to run 'show table status' on the database, please see below:
"
mysql> show table status from essdata_dev;
Empty set (0.00 sec)
"

How to repeat:
Kill the mysql threads and start the mysql thread again. The mysql instance was initially started from the root but the restart was from within the user which is the owner of all the mysql files.

Suggested fix:
Requesting to know a fix.

Please, send the results of ls -l for that essdata_dev database directory. Also, please, send error log of your server.

Hello Valeriy,
   I hope you have the zip file I have attached with the details that you have mentioned?

Kind Regards,
Vivek.

I see files of this ZTEST table (MyISAM one):

-rw-rw---- 1 ccspsql ccsp  1024 2010-12-21 16:32 ZTEST.MYI
-rw-rw---- 1 ccspsql ccsp     0 2010-12-21 16:32 ZTEST.MYD
-rw-rw---- 1 ccspsql ccsp  8554 2010-12-21 16:32 ZTEST.frm

I'd say it is empty, for whatever reason. 

As you are on Linux, please, check if quoting table name helps:

check table `ZTEST`;

Please, send also the results of:

show global variables like 'lower%';

Hello Valeriy,
   Please see the results below:
"
mysql> check table `ZTEST`;
+-------------------+-------+----------+-----------------------------------------+
| Table             | Op    | Msg_type | Msg_text                                |
+-------------------+-------+----------+-----------------------------------------+
| essdata_dev.ztest | check | Error    | Table 'essdata_dev.ztest' doesn't exist |
| essdata_dev.ztest | check | status   | Operation failed                        |
+-------------------+-------+----------+-----------------------------------------+
2 rows in set (0.02 sec)

mysql> show global variables like 'lower%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| lower_case_file_system | OFF   |
| lower_case_table_names | 1     |
+------------------------+-------+
2 rows in set (0.03 sec)

mysql> check table `ALERT_RULE_TYPE`;
+-----------------------------+-------+----------+---------------------------------------------------+
| Table                       | Op    | Msg_type | Msg_text                                          |
+-----------------------------+-------+----------+---------------------------------------------------+
| essdata_dev.alert_rule_type | check | Error    | Table 'essdata_dev.alert_rule_type' doesn't exist |
| essdata_dev.alert_rule_type | check | status   | Operation failed                                  |
+-----------------------------+-------+----------+---------------------------------------------------+
2 rows in set (0.00 sec)
"

The issue is not only with 1 table but ALL the tables within ONLY this database. All other databases are OK.

Eagerly awaiting your update.

Thanks and Regards,
Vivek.

That's because you have lower_case_table_names = 1 now, and this means that table names are in lower case at filesystem level (http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysvar_lower_case_tabl...):

"If set to 1, table names are stored in lowercase on disk and comparisons are not case sensitive."

while your ZTEST is NOT in lower case on disk. Looks like it was created before you changes this setting. Why not to use 0, as it is normal for Linux?

Hello Valeriy,
   I have set the following line within my.cnf:
"
set-variable = lower_case_table_names=0
"

But it doesn't seem to work. I have also tried:
"
lower_case_table_names=0
"

But the lower_case_table_names variable still shows as 1 on restart on the MySQL instance!

Also the tables in the database are still not visible. Requesting to know whether there is any tool through which I can update the MySQL metadata with the database info.

Eagerly awaiting your update.

Thanks and Kind Regards,
Vivek.

This setting:

lower_case_table_names=0

should work. Are you sure you changed correct my.cnf, the one that is used by your server?

Please, send the results of:

find / -name my.cnf -print 2>/dev/null

find / -name .my.cnf -print 2>/dev/nul

ps aux | grep mysqld

if in doubts.

Hello Valeriy,
   I am sure that I have changed the correct my.cnf as if the file is not explicitly mentioned then MySQL chooses the my.cnf in the basedir location. I have changed another variable along with that which is:
# ft_min_word_len=3

and that has got implemented. Please see below:
"
mysql> show global variables like 'ft_min%';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| ft_min_word_len | 4     |
+-----------------+-------+
"

Eagerly awaiting your update.

Thanks and Kind Regards,
Vivek.

4 is the default value, so the fact that it was used does not prove 100% that it was read for the file you had edited. It could be just not set in the file that is really used.

I'd prefer to see the results of commands I've asked about. Or, alternatively, a complete repeatable test case, that is, sequence of actions to be performed on just installed recent (5.0.91) version of MySQL server that leads to the problem each and every time.

Hello Valeriy,
    Please find the results as advised:
"

find / -name my.cnf 2>/dev/null 
/etc/my.cnf 
/etc/mysql/my.cnf 
/home.local/ccspsql/my.cnf
/usr/local/mysql/my.cnf
"

I use the following script to start mysql:
"
#!/bin/csh -f
set m = /usr/local/mysql
cd $m
bin/mysqld_safe --lower_case_table_names=1 --socket=/tmp/mysql.sock -port=3306 --datadir=$m/data
"

The processes running are:
"
wasdev-c:~/bin> ps auxf | grep -i mysql
ccspsql  11314  0.0  0.0   2888   816 pts/2    S+   09:44   0:00  |           \_ grep -i mysql
ccspsql  17657  0.0  0.0   4196  1724 pts/4    S    Jan06   0:00  |           |   \_ /bin/sh bin/mysqld_safe --lower_case_table_names=1 --socket=/tmp/mysql.sock --port=3306 --datadir=/usr/local/mysql/data
ccspsql  17687  0.0  3.7 1420824 156612 pts/4  Sl   Jan06   1:17  |           |       \_ /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --pid-file=/usr/local/mysql/data/wasdev-c.ucl-0.ucl.ac.uk.pid --skip-external-locking --port=3306 --socket=/tmp/mysql.sock --lower_case_table_names=1
"

Kindly awaiting your update.

Thanks and Regards,
Vivek.

So, you have --lower_case_table_names=1 explicitly added to the mysqld_safe command line... It has higher priority than any setting in any of my.cnf files. 

Please, set it to 0 in your script (or remove it entirely) and then check if you can access your ZTEST table.

Hello Valeriy,
   Thanks You very much! That has resolved the issue :-)

Thanks and Kind Regards,
Vivek.

Issue resolved.