MySQL Bugs: #27996: Error: Cannot fetch table information

Bug #27996	Error: Cannot fetch table information
Submitted:	21 Apr 2007 0:55	Modified:	8 Jun 2007 10:31
Reporter:	Brad Kaenel	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	1.2.11	OS:	Windows (XP SP2)
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	fetch, file, schema, table, temp

Description:
Right-clicking on a table name in the Schemata listview, and selecting "Edit Table" from the popup menu, generates an error dialog box stating:

A MySQL error was encountered.  The message is:
Cannot fetch table information.
 The following error occured: Can't create/write to file 'C:\WINNT\TEMP\#sql_b28_0.MYD' (Errcode: 17) (1)

I suspect the Edit Table feature is trying to create a temporary file.  However, this path does not exist on my system.  My Windows directory is C:\WINDOWS, *not* C:\WINNT.  Perhaps the function is not calling the WinAPI to return the Windows directory? Or is not using the WinAPI function for generating a temporary filename?

To try a workaround, I manually created a C:\WINNT\TEMP directory and reran the function, but received the identical error.

I believe this error is related to, or perhaps is identical to, an error I logged today on the Administrator tool.  Since both bugs are a failure to "fetch table information" (or to "fetch schema table status"), it seems likely they both may be calling the same function in a shared library.

At any rate, the code that generates the tempfile path is clearly broken.  Unless the error message text itself is an incorrect red-herring, masking a different error.  I scanned my registry, and could not find any system config entries that contained C:\WINNT or C:\WINNT\TEMP.  Could this actually be a hard-coded path in someone's code?  I doubt it came from the WinAPI...

How to repeat:
Right-click on a table name in the Schemata listview, selecting "Edit Table" from the popup menu.

Suggested fix:
Call the WinAPI to determine the actual Windows directory and/or TEMP path.

Thank you for a problem report. Do you have any my.ini file on your machine?

Hmmm...  No, there's no my.ini on my machine.  I searched the whole sub-tree c:\program files\mysql and didn't find any .ini files at all.  I only have the GUI Tools installed on my machine; the MySQL server is installed on a network server that I don't have file-level access to.  But if you need info about the server itself, I *can* get it.

Thank you for the feedback. Could you please provide the create
table script and the privileges you have to connect with the
remote server?. Thanks in advance.

Results of SHOW GRANTS FOR devteam;

'GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE ON *.* TO 'devteam'@'%' IDENTIFIED BY PASSWORD '*35D788DF08469BE202FE5B611FF17D318FC6C287' WITH GRANT OPTION'

'GRANT ALL PRIVILEGES ON `devteam`.* TO 'devteam'@'%' WITH GRANT OPTION'

Excerpt from the MySQL Migration script:

-- ----------------------------------------------------------------------
-- MySQL Migration Toolkit
-- SQL Create Script
-- ----------------------------------------------------------------------

SET FOREIGN_KEY_CHECKS = 0;

CREATE DATABASE IF NOT EXISTS `prod_pactrac_rev0319`
  CHARACTER SET latin1 COLLATE latin1_swedish_ci;
USE `prod_pactrac_rev0319`;
-- -------------------------------------
-- Tables

DROP TABLE IF EXISTS `prod_pactrac_rev0319`.`C_PACKAGE`;
CREATE TABLE `prod_pactrac_rev0319`.`C_PACKAGE` (
  `BATCH_ID` CHAR(15) NOT NULL,
  `TRACKING_NUMBER` VARCHAR(40) NOT NULL,
  `SCAN_UTC` DATETIME NOT NULL,
  `OPERATOR_CONTACT_ID` CHAR(15) NULL,
  `OPERATOR_NAME` VARCHAR(60) NOT NULL,
  `RECIPIENT_CONTACT_ID` CHAR(15) NULL,
  `RECIPIENT_NAME` VARCHAR(60) NOT NULL,
  `COURIER_ID` CHAR(15) NOT NULL,
  `COURIER_SUB_GROUP_ID` CHAR(15) NULL,
  `DELIVERED_TO_DP_SNAP_SERIALIZED` LONGBLOB NULL,
  `LASTMOD_UTC` DATETIME NOT NULL
)
ENGINE = INNODB;

SET FOREIGN_KEY_CHECKS = 1;

-- ----------------------------------------------------------------------
-- EOF

There are more tables created, but none more or less complex than this one.

Is this what you need?

Please also provide output of DESC TABLE `prod_pactrac_rev0319`.`C_PACKAGE`; statement.

Better if you issue this statement in the mysql command line client: I want to check if described behaviour occurs only with GUI Tools or also with regular client.

mysql> desc prod_pactrac_rev0319.c_package;

ERROR 1 (HY000): Can't create/write to file 'C:\WINNT\TEMP\#sql_b28_0.MYD' (Errcode: 17)

Exactly the same error.

It occurs to me that this may be a permissions error, since the code fails even when a C:\WINNT\TEMP directory pre-exists (see my initial post).

This same error is manifesting itself in the Query Browser tool (see my bug report), and also (I discovered today) in the Visual Studio Plug-In when you select the Alter Table function from the popup menu of a table name.  All of these features are attempting to retrieve table schema information, and are failing (apprarently) in their attempt to create a local temp file.

Thank you for the feedback.

I changed category of the report, because new findings.

Please provide output of 

echo %TMPDIR% %TEMP% %TMP%

You should run this command in the Windows command line (cmd.exe)

Thank you, Sveta, for all of your timely responses.  Here's the temp file paths on my system:

=============================================================================
Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.

C:\Documents and Settings\bkaenel>echo %tmpdir% %temp% %tmp%
%tmpdir% C:\DOCUME~1\bkaenel\LOCALS~1\Temp C:\DOCUME~1\bkaenel\LOCALS~1\Temp
==============================================================================

This *is* the correct path on my box (expanded>> C:\Documents and Settings\bkaenel\Local Settings\Temp) -- no C:\WINNT\TEMP in sight :>)

Thank you, Brad, for the feedback.

Please also provide output of SHOW VARIABLES LIKE 'tmpdir';

Ahh; looks like you're on to something. 'tmpdir' contains a non-existent path:

*****************************************************************************
C:\Program Files\MySQL\MySQL Server 5.0\bin>mysql --host=ns02 --user=devteam --password=devteam prod_pactrac_rev0319
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 183
Server version: 5.0.24a-community-nt MySQL Community Edition (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show variables like 'tmpdir';
+---------------+----------------+
| Variable_name | Value          |
+---------------+----------------+
| tmpdir        | C:\WINNT\TEMP\ |
+---------------+----------------+
1 row in set (0.00 sec)

Seems you have my.ini somewhere.

Please open Control panels -> Administration -> Services, then find MySQL, double-click on it and examine --defults-file setting of the server.

And there's actually a second VARIABLE that also contains the wrong path:

*******************************************************************************
C:\Program Files\MySQL\MySQL Server 5.0\bin>mysql --host=ns02 --user=devteam
--password=devteam prod_pactrac_rev0319
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 183
Server version: 5.0.24a-community-nt MySQL Community Edition (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show variables;
+-------------------+----------------+
| Variable_name     | Value          |
+-------------------+----------------+
          <excerpt...>
+-------------------+----------------+
| slave_load_tmpdir | C:\WINNT\TEMP\ |
| tmpdir            | C:\WINNT\TEMP\ |
+-------------------+----------------+

**********************************************************************

I tried to SET SESSION tmpdir = 'C:\Documents and Settings\bkaenel\Local Settings\Temp';  (this is the correct path)  but received an error saying that 'tmpdir' was a read-only variable.   Doh! :>)

Oh, now I see what you're getting at.  The error message isn't refering to a path on *my workstation*, it's refering to a path on the *server*.  It's the *server* that can't create the temp file, not the tool that's running on my workstation.

I don't have access to the server box; I'll have to get my admin to check the contents of the --defaults-file and/or mysql.ini

I'll post them when he forwards them to me...

Thank you for the update, Brad.

Will wait feedback about mysql.ini from you.

Sysop reports that MySql server is "Running As A Local System Account. And SYSTEM has full Rights To That Directory (C:\WINNT\TEMP)

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

 
MY.INI (comments removed)
=========================
[client]
port=3306

[mysql]
default-character-set=latin1

[mysqld]
port=3306
basedir="C:/Program Files/MySQL/MySQL Server 5.0/"
datadir="C:/Program Files/MySQL/MySQL Server 5.0/Data/"
default-character-set=latin1
default-storage-engine=INNODB
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
max_connections=100
query_cache_size=0
table_cache=256
tmp_table_size=18M
thread_cache_size=8
myisam_max_sort_file_size=100G
myisam_max_extra_sort_file_size=100G
myisam_sort_buffer_size=35M
key_buffer_size=25M
read_buffer_size=64K
read_rnd_buffer_size=256K
sort_buffer_size=256K
innodb_additional_mem_pool_size=2M
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=1M
innodb_buffer_pool_size=47M
innodb_log_file_size=24M
innodb_thread_concurrency=8

Just to say I am also experiencing the same problem so would be interested in any fixes.
I am using the MYSQL GUI tools with MYSQL 3.22.16a so wondered if there was a compatibility problem..? 
NB I changed my.ini to include 'old-passwords' in order to use some php functionality.

From what I noticed.  Lately Mysql Browser doesn't let us edit tables with capital letters in the name.  why is that?

Thank you, Brad, for the feedback.

Please also 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

Can be considered as duplicate of Bug #28140

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

Delete the file #sql_7a4_0.MYD in C:/windows/temp if exits.