mysql/phpMyAdmin problem and solution

[bbfuller]

The problem was that phpMyAdmin would allow browsing of tables but would not show any database structure nor show any fields in the search function.

From the command line using:

show columns from table;

specifying the actual table name in place of "table" of course and having previously specified the database to use, generated an error:

Error 1 (HY000: Can't create/write to file '/tmp/#sql_4cc_0.MYI' (Errcode: 2)

Solution was to add a line in the file:

/etc/my.cnf

in the "mysqld" section reading:

tmpdir=/tmp

and restart mysqld.

Never had to do that before and there is no "tmpdir" specified in "my.cnf" on Fedora 16.

I guess that'll do as a solution until someone comes up with a better fix.

Anyone got any idea why it reared its head? Or is it just my installation?

[shannonmpoole]

I had this exact problem.

I was working with a fresh install of Fedora 17.
AMP stack and PhpMyAdmin were installed from yum with standard configuration.

I'm not sure what the cause of this was but your fix worked so thanks.

[tomiii]

I'm having this same problem. I find that restarting mysqld is sufficient without actually changing any configuration files, but the problem eventually shows up again. Here's what I see on my fc16 machine:

Symptom: Reboot machine. It runs perfectly for a month or so. Then all of a sudden some SQL statements execute fine while others fail. Example:

mysql> desc members;
ERROR 1 (HY000): Can't create/write to file '/tmp/#sql_412_0.MYI' (Errcode: 2)
mysql>

When the failure occurs, here's what strace on mysqld in another window shows me:

[pid 24090] read(46, "\3desc members", 13) = 13
[pid 24090] fcntl(46, F_SETFL, O_RDWR|O_NONBLOCK) = 0
[pid 24090] setsockopt(46, SOL_SOCKET, SO_RCVTIMEO, "\36\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0", 16) = 0
[pid 24090] lstat("/tmp", {st_dev=makedev(9, 127), st_ino=786481, st_mode=S_IFDIR|S_ISVTX|0777, st_nlink=0, st_uid=0, st_gid=27, st_blksize=4096, st_blocks=8, st_size=0, st_atime=2012/12/20-16:01:33, st_mtime=2012/12/27-23:23:14, st_ctime=2013/01/07-00:16:13}) = 0
[pid 24090] lstat("/tmp/#sql_412_0.MYI", 0x7f39981049b0) = -1 ENOENT (No such file or directory)
[pid 24090] open("/tmp/#sql_412_0.MYI", O_RDWR|O_CREAT|O_EXCL|O_TRUNC|O_NOFOLLOW, 0660) = -1 ENOENT (No such file or directory)
[pid 24090] lstat("/tmp/#sql_412_0.MYI", 0x7f3998107060) = -1 ENOENT (No such file or directory)
[pid 24090] unlink("/tmp/#sql_412_0.MYI") = -1 ENOENT (No such file or directory)
[pid 24090] write(46, "F\0\0\1\377\1\0#HY000Can't create/write "..., 74) = 74

WTF? lstat() on /tmp works great, shows it's a directory with 1777 permissions, but a subsequent open(O_CREAT) fails in that directory with ENOENT? (Insert a Tim Allen style "Huh?" here.)

Ok, let's take a look at /tmp:

[root@ds1 ~]# ls -ldi /tmp
786433 drwxrwxrwt. 11 root root 4096 Jan 10 00:20 /tmp
[root@ds1 ~]#

Wait, that inode number is wrong. It doesn't match what lstat() returned. Is there some kind of strange chroot thing going on here?

[root@ds1 ~]# ls -l /proc/24090/root
lrwxrwxrwx. 1 mysql mysql 0 Jan 9 23:44 /proc/24090/root -> /
[root@ds1 ~]#

Nope. How is this possible? Kernel bug? I'm running almost the latest greatest (3.6.10-2.fc16.x86_64). fsck shows no filesystem corruption.

Without rebooting, if I restart mysqld (e.g. "systemctl restart mysqld.service") the problem goes away...for another few weeks. Then it mysteriously comes back. I can't find anything in any log file that might explain this behavior. Any ideas?