MySQL Bugs: #28140: mysql could not fetch schema table status

Bug #28140	mysql could not fetch schema table status
Submitted:	27 Apr 2007 17:02	Modified:	2 Aug 2007 13:34
Reporter:	A Campa	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:		OS:	Windows (XP sp2)
Assigned to:		CPU Architecture:	Any

Description:
Hi there,

when trying to access the databases trough the MYSQL Administrator I get the following error:

mysql could not fetch schema table status

I can access my databases using the command prompt no problem.  I have uninstalled and reinstalled the Mysql server 5.0. and Mysql Administrator but I keep receiving the same error message.

Any suggestions?

How to repeat:
Click on Catalogs then click on any of the available databases, you should receive the error message.

Thank you for a problem report. What exact version of MySQL server and MySQL Administrator do you use?

The MySql version is: MySQL 5.0.37-community-nt via TCP/IP and the MYSQL Administrator is version 1.2.11.

Have you tried to login as root in MySQL Administrator? Do you have this problem for any database, including mysql?

I have only logged in as root the server host is localhost.  I have four databases.

information_schema
mysql
test
dvdrentals

I created the last one. I can work on dvdrentals using the command prompt.

Thank you for the feedback. I was not able to repeat either on XP or
Vista. Could you please, stop the service service, edit the my.ini
file adding the general query log:

[mysqld]

# The TCP/IP Port the MySQL Server will listen on
port=3307
log

then start the service again, test the issue reported with MA and
then stop the server and print here the contain od computer_name.log
file, something like:

C:\Program Files\MySQL\MySQL Server 5.0\bin\mysqld-nt, Version: 5.0.37-community-nt-log (MySQL Community Edition (GPL)). started with:
TCP Port: 3307, Named Pipe: (null)
Time                 Id Command    Argument
070502 12:56:46	      1 Connect     root@localhost on 
		      1 Query       SELECT @@sql_mode
		      1 Query       SET SESSION sql_mode='STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'
		      1 Query       SET NAMES utf8
		      2 Connect     root@localhost on 
		      2 Query       SELECT @@sql_mode
		      2 Query       SET SESSION sql_mode='STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'
		      2 Query       SET NAMES utf8
		      2 Quit       
070502 12:56:48	      1 Query       show databases
070502 12:56:50	      1 Init DB     mysql
		      1 Query       SHOW TABLE STATUS
070502 12:56:51	      1 Query       SHOW INDEX FROM `columns_priv`
		      1 Query       DESCRIBE `columns_priv`
		      1 Query       SHOW INDEX FROM `db`
		      1 Query       DESCRIBE `db`

also take a look if there are error messages in your *.err file.
Thanks in advance.

my.ini file

Attachment: my.ini (application/octet-stream, text), 9.03 KiB.

.err file

Attachment: Ellimax.err (application/octet-stream, text), 40.68 KiB.

Zip file containing my.ini .err. logfile0 and logfile1

Attachment: bugfiles.zip (application/x-zip-compressed, text), 70.31 KiB.

I am not sure what you want me to do however I have sent you some files that may solve the bug.

Thank you for the feedback. I asked you to enable the General Query log:

[mysqld]

# The TCP/IP Port the MySQL Server will listen on
port=3307
log   #<<<< that key in the my.ini file

and what you attached were the InnoDB files. The query log is a
file created in the Data directory with the name of computer_name.log.

What I wanted to see what MA is sending to the server when doing what
you reported. Thanks.

Hi,

I have two my.ini files one in the C:\Program Files\MySQL\MySQL Server 5.0 and one in the C:\WINDOWS directory.  Which one do you want me to update with (log)?

I checked in the C:\Program Files\MySQL\MySQL Server 5.0\data for the .log file but it is not there.  Where should it be?

Thanks

I think I got it.

C:\Program Files\MySQL\MySQL Server 5.0\bin\mysqld-nt, Version: 5.0.37-community-nt-log (MySQL Community Edition (GPL)). started with:
TCP Port: 3306, Named Pipe: (null)
Time                 Id Command    Argument
070508 17:43:36	      1 Connect     root@localhost on dvdrentals
		      1 Query       SET NAMES utf8
		      1 Query       show databases
		      1 Query       show databases
		      2 Connect     root@localhost on 
		      2 Query       SELECT @@sql_mode
		      2 Query       SET SESSION sql_mode='STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'
		      2 Query       SET NAMES utf8
		      2 Quit       
070508 17:43:44	      1 Init DB     dvdrentals
		      1 Query       SHOW TABLE STATUS
		      1 Query       SHOW INDEX FROM `customers`
		      1 Query       DESCRIBE `customers`
		      1 Init DB     dvdrentals
070508 17:43:46	      1 Init DB     dvdrentals
		      1 Query       SHOW TABLE STATUS
		      1 Init DB     dvdrentals
		      1 Init DB     dvdrentals
		      1 Query       SHOW PROCEDURE STATUS
		      1 Init DB     dvdrentals
070508 17:43:48	      1 Init DB     test
		      1 Query       SHOW TABLE STATUS
070508 17:43:49	      1 Query       SHOW INDEX FROM `new table`
		      1 Query       DESCRIBE `new table`
		      1 Init DB     dvdrentals
070508 17:43:50	      1 Init DB     test
		      1 Query       SHOW TABLE STATUS
		      1 Init DB     dvdrentals
		      1 Init DB     test
		      1 Query       SHOW PROCEDURE STATUS
		      1 Init DB     dvdrentals
		      1 Init DB     mysql
		      1 Query       SHOW TABLE STATUS
070508 17:43:51	      1 Query       SHOW INDEX FROM `columns_priv`
		      1 Query       DESCRIBE `columns_priv`
		      1 Init DB     dvdrentals
070508 17:43:52	      1 Init DB     mysql
		      1 Query       SHOW TABLE STATUS
		      1 Init DB     dvdrentals
		      1 Init DB     mysql
		      1 Query       SHOW PROCEDURE STATUS
		      1 Init DB     dvdrentals
		      1 Init DB     information_schema
		      1 Query       SHOW TABLE STATUS
		      1 Query       SHOW INDEX FROM `CHARACTER_SETS`
		      1 Query       DESCRIBE `CHARACTER_SETS`
		      1 Init DB     dvdrentals
070508 17:43:53	      1 Init DB     information_schema
		      1 Query       SHOW TABLE STATUS
		      1 Init DB     dvdrentals
		      1 Init DB     information_schema
		      1 Query       SHOW PROCEDURE STATUS
		      1 Init DB     dvdrentals

thanks

Bug #28451 was marked as duplicate of this one

Thank you for the provided log file.

Strange why it doesn't contain "show databases" query.

Please describe your actions step-by-step since you open MySQL Administrator until you get "Could  not fetch schema table status error"

Hi there,

As A Campa said...

when trying to access the databases trough the MYSQL Administrator I get the
following error:

mysql could not fetch schema table status

The solution for me was to change the tmpdir out of the windows tree, and then add an exception to the McAfee real time analyzer so that the tmpdir will not be analyzed by McAfee. 

I hope this help.

First I launch the MySQL Administrator, then I enter the username (root) and password.
Then I click on Catalogs to get a list of my databases.
I have 4:

dvdrentals
information_schema
mysql
test

Then if I click on any of the above I get the error message.

Thanks

Thank you for the report.

Could you please run in the mysql command line client next sequence of queries:

USE mysql;
SHOW TABLE STATUS;
SHOW INDEX FROM `columns_priv`;
DESCRIBE `columns_priv`;
SHOW INDEX FROM `db`;
DESCRIBE `db`;

Please inform us about any error you get or if you don't get an error.

After entering any of the queries below:

SHOW INDEX FROM `columns_priv`;
DESCRIBE `columns_priv`;
SHOW INDEX FROM `db`;
DESCRIBE `db`;

I get the ERROR 1064 (42000): You have an error in your SQL syntax: check the manual that corresponds to your MySQL server version for the right syntax to use near ‘’columns_priv’’ at line 1.

or 

I get the ERROR 1064 (42000): You have an error in your SQL syntax: check the manual that corresponds to your MySQL server version for the right syntax to use near ‘’db’’ at line 1.

Thanks

Thank you for the feedback.

> After entering any of the queries below:

> SHOW INDEX FROM `columns_priv`;

> I get the ERROR 1064 (42000): You have an error in your SQL syntax: check the
> manual that corresponds to your MySQL server version for the right syntax to use
> near ‘’columns_priv’’ at line 1.

One strange thing iin the error message.

In the SHOW INDEX FROM `columns_priv`; expression `columns_priv` is in backticks, but in the error message same expression is in apostrophes. Please check if you enter query correctly: with backticks and paste results again.

Sorry about the missing backticks.

When I enter any of the following entries:

describe `columns_priv`;
describe `db`;

I get the following error message:

ERROR 1 (HY000): Can't create/write to file 'C:\DOCUME#1\MASSIM#1\LOCALS#1\Temp\#sql_194_0.MYI (Errcode: 2)

Regards

Thank you for the feedback.

Seems this report is same as bug #27996.

Please check system temporary directory. To do it, please, click Start,
then right-click My Computer, then find Advanced tab and click Environment
button.

Please paste value of TMP and TEMP for system user.

Also check user MySQL running as. You can find it in Control
Panel->Administartion->Services

Hi;
Same symptoms appeared with my server. All was going well until I tried to save a stored procedure. After that the server stopped. I reloaded the MySql service and each time I try to fetch databases structures, server stops (administrator or query browser). I was worried that I lost a month of work.
What is strange is that a server application I'm working on, connects correctly to the server and gets all the data.
Same as Campa, I removed installation et reinstalled server (version 5) with no results.

Hello there,

Under the User variables the value of TEMP and TMP are %USERPROFILE%\Local Settings\Temp
 
Under the System variables the value of TEMP and TMP are %SystemRoot%\TEMP

The MySQL server is always started automatically.

Thanks.

Thank you for the feedback.

Please find Windows user name MySQL running as. You can find it in Control
Panel->Administartion->Services->MySQL->Properties.

Under the General tab in MySQL>Properties the Service name is: MySQL the Display name is: MySQL the Path to executable is:

"C:\Program Files\MySQL\MySQL Server 5.0\bin\mysqld-nt" --defaults-file="C:\Program Files\MySQL\MySQL Server 5.0\my.ini" MySQL

and Startup type: Automatic

Regards

User who service is running as is shown on "Enter in System" tab of property window. I want to check if MySQL is running as you or as system user (which should be by default). Also check if directory "C:\DOCUME#1\MASSIM#1\LOCALS#1\Temp" is exists.

Hi,

Just letting you know that I am getting the exact same problem (cant create/write to file in temp directory) with MySQL running on Windows Vista Business. 

It happens if the client (MySQL QueryBrowser) runs locally or remotely.

The MySQL service runs as Local System user (I've also tried running as a domain user who is an administrator on the server, but it made no difference).

The temp driectories on my system are:
TEMP = %USERPROFILE%\AppData\Local\Temp
TMP = %USERPROFILE%\AppData\Local\Temp
TMPDIR = %USERPROFILE%\AppData\Local\Temp

The MySQL server version is 5.0.4.1 community-net
QueryBrowser version is 1.2.12

Problem happens whenever I do anything in querybrowser. MySQL (on the server) creates a temp file in the temp directory (c:\windows\temp), after that I can not see the stored procedure list or the table column information until I manually delete this file.
These files are not locked by anything. I know this because I have created a scheduled task to clear the temp directory every 1 minute and this alleviates the problem. i.e. after 1 minute the problem goes away.

I have also tried setting the --tmpdir option in MySQL to a different temp directory on my hard disk, e.g. c:\MySQLTempFiles.  MySQL then uses this new directory, but the problem still occurs as soon as one file is created there.

The temp files have names like: #sql_23d0_0.MYD

Please help!

Thanks,

Adrian

Hi,

Maybe I am looking in the wrong place.
After accessing the MySQL Properties (Local Computer) I am presented with 4 tabs:
General
Log On
Recovery
Dependencies

In the Log On tab the Local System account is selected for Log on as:

The directory C:\DOCUME#1\MASSIM#1\LOCALS#1\Temp does not exist.

Regards

Thank you for the report.

Verified "MySQL server chooses not existant temporary directory" error.

How to repeat:

1. Change system TEMP and/or TMP variables to point to not existant directory.
2. Start MySQL server.
3. Try issue DESC some_table statement.

Suggested fix:

don't choose not existant/not writable temporary dir or write error in the error log at least. See also Bug #28942

Thanks for your response.

What do I have to do to fix the problem?  Could you give me the instructions step by step please?

Regards

Duplicate of bug#28942.

Received this error intermittently for a month.  Turned off real-time file scanning in McAfee and the error stopped.  If encountering this problem, turn off file scanning to see if it fixes the problem.

An alternative to turning off all on-access scan is to specify in McAfee that "low-risk" processes should have different detection rules, and then turn off writing and reading scans in those rules. Then you add <mysql \bin dir>\mysqld-nt.exe as a "low-risk" process.

This is safer than tuening off all on-access scans.

If your virus checker is controlled by your IT department, you may find that your on-access scan rules get set back to the full settings after a while... :(

I was receiving the same message running MySQL on Windows Server 2003 and McAfee.  To test this, I turned the McAfee anti-virus off and tried to show the schema.  It worked every time.  I am writing McAfee to see how to make the scanning less agressive on the MySQL directories.

-Vernon Robinson

Hi,

I've been following this bug as it has happened to me on a production server.  I don't run McAfee or any virus scanning software on the server that task is handled elsewhere.

I have heard of the default temp directory location issue before and have changed the location to c:\temp and given the user mysql that the service runs under Full Control of that directory.

I have run the following commands as suggested:

USE mysql;
SHOW TABLE STATUS;
SHOW INDEX FROM `columns_priv`;
DESCRIBE `columns_priv`;
SHOW INDEX FROM `db`;
DESCRIBE `db`;

And all return positive results except for the last command.
DESCRIBE `db` returns the error:
ERROR 1 (HY000): Can't create/write to file 'c:\temp\#sql_220e8_0.MYD' (Errcode: 17)

This is unusual as the service has complete control of that directory. The only thing I can think of is that one thread has that file locked when another needs to modify it.

Try adding the temp directory from the "do not scan" options in McAfee, that way you don't have to disable McAfee. I got this from:

http://forums.mysql.com/read.php?34,169966,170024#msg-170024 

This fixed the problem for me.

I had the same problem.  MysqlAdministrator giving the 'mysql could not fetch schema table status'. Even mysqldump was affected - I could not dump my databases successfully. 

I tried the fix for changing the temporary directory  but it dit not solve the problem. 

What saved me was to follow the steps for disabling on-access scan in mcafee - which worked beautifully. I went ahead to make mysqld-nt.exe a low risk process in mcafee and disabled on-access scan only for low risk processes. 

Thanks a million for your help, your time and your good work. 

Pascal.

I got this from the error log. I followed one of the links which suggested that I set innodb_force_recovery=4. I did that. I still can't select or use mysqldump. 

090609 12:36:56 - mysqld got exception 0xc0000005 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help diagnose
the problem, but since we have already crashed, something is definitely wrong
and this may fail.

key_buffer_size=26214400
read_buffer_size=65536
max_used_connections=1
max_threads=100
threads_connected=1
It is possible that mysqld could use up to 
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 58230 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

thd: 0x2ccd978
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
006A72D3    mysqld.exe!ha_innobase::innobase_get_index()[ha_innodb.cc:4460]
006AAF87    mysqld.exe!ha_innobase::innobase_initialize_autoinc()[ha_innodb.cc:2365]
006AB32F    mysqld.exe!ha_innobase::open()[ha_innodb.cc:2595]
00443D46    mysqld.exe!handler::ha_open()[handler.cc:2030]
005BD64B    mysqld.exe!open_table_from_share()[table.cc:1881]
00524B47    mysqld.exe!open_unireg_entry()[sql_base.cc:3926]
0052819D    mysqld.exe!open_table()[sql_base.cc:2933]
00529122    mysqld.exe!open_tables()[sql_base.cc:4585]
0052967A    mysqld.exe!open_and_lock_tables_derived()[sql_base.cc:4988]
004B6761    mysqld.exe!simple_open_n_lock_tables()[mysql_priv.h:1535]
00554F72    mysqld.exe!mysql_execute_command()[sql_parse.cc:3441]
7C9100B8    ntdll.dll!RtlFreeHeap()
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 02D1E150=LOCK TABLES `accounting_documents` READ /*!32311 LOCAL */,`auth_group` READ /*!32311 LOCAL */,`auth_group_permissions` READ /*!32311 LOCAL */,`auth_message` READ /*!32311 LOCAL */,`auth_permission` READ /*!32311 LOCAL */,`auth_user` READ /*!32311 LOCAL */,`auth_user_groups` READ /*!32311 LOCAL */,`auth_user_user_permissions` READ /*!32311 LOCAL */,`business_objects` READ /*!32311 LOCAL */,`data_dictionary_configurations` READ /*!32311 LOCAL */,`django_admin_log` READ /*!32311 LOCAL */,`django_content_type` READ /*!32311 LOCAL */,`django_session` READ /*!32311 LOCAL */,`django_site` READ /*!32311 LOCAL */,`java_classes` READ /*!32311 LOCAL */,`maintenance_documents` READ /*!32311 LOCAL */,`method_definitions` READ /*!32311 LOCAL */,`method_invocations` READ /*!32311 LOCAL */,`module_configurations` READ /*!32311 LOCAL */,`modules` READ /*!32311 LOCAL */,`ojb_classes` READ /*!32311 LOCAL */,`ojb_collections` READ /*!32311 LOCAL */,`ojb_configurations` READ /*!32311 LOCAL */,`ojb_fields` READ /*!32311 LOCAL */,`
thd->thread_id=1
thd->killed=NOT_KILLED
The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.