MySQL Bugs: #14239: mysql forgets where its databases are stored

Bug #14239	mysql forgets where its databases are stored
Submitted:	23 Oct 2005 14:11	Modified:	3 Nov 2005 7:27
Reporter:	Hanno Wagner	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.1.14	OS:	NetBSD-2.0G
Assigned to:		CPU Architecture:	Any

Description:
After some time of start, MySQL-Server forgets it's 'datadir'. This is visible for me when the blogs aren't working anymore (the error there is: "No database selected"), 

normal requests go like this:
angua# mysql -u rince mysql
ERROR 1049 (42000): Unknown database 'mysql'

angua:~> mysql    (as administrator)

mysql> show variables like 'datadir';
+---------------+-------------+
| Variable_name | Value       |
+---------------+-------------+
| datadir       | /var/mysql/ |
+---------------+-------------+
mysql> exit;

So, the client still knows the datadir.

angua:~> uname -a
NetBSD angua 2.0G NetBSD 2.0G (ANGUA) #0: Sun Jul 25 15:09:16 CEST 2004  src@angua:/usr/src/sys/arch/i386/compile/ANGUA i386

angua:~> pkg_info|grep mysql-server
mysql-server-4.1.14nb1 MySQL 4, a free SQL database (server)

angua:~> mysql --version
mysql  Ver 14.7 Distrib 4.1.14, for -netbsdelf (i386) using  EditLine wrapper
angua:~> /usr/pkg/libexec/mysqld --version
/usr/pkg/libexec/mysqld  Ver 4.1.14 for -netbsdelf on i386 (Source distribution)

angua# lsof /usr/pkg/libexec/mysqld
COMMAND  PID  USER   FD   TYPE DEVICE SIZE/OFF  NODE NAME
mysqld  9413 mysql  txt   VREG    4,4  4057652 77544 /usr/pkg/libexec/mysqld

there is nothing in the error-logfile angua.err. As mysql-User, I can easily change in the directory /var/mysql (which is local) and do an ls:

$ id
uid=1008(mysql) gid=75(mysql) groups=75(mysql)
$ pwd
/var/mysql
$ ls -ld mysql
drwxr--r--  2 mysql  mysql  1536 Apr 27 19:07 mysql
$ ls -lart  | tail -10
-rw-rw----   1 mysql  mysql      1024 Oct 22 16:29 #sql_10d3_3.MYI
drwx------   2 mysql  mysql      2560 Oct 22 16:58 maritsblog
drwx------   2 mysql  mysql      1024 Oct 22 19:42 b2evolution
-rw-rw----   1 mysql  mysql      1024 Oct 23 05:46 #sql_1abe_2.MYI
-rw-rw----   1 mysql  mysql  10485760 Oct 23 09:36 ibdata1
-rw-rw----   1 mysql  mysql   5242880 Oct 23 09:36 ib_logfile0
-rw-rw----   1 mysql  mysql         5 Oct 23 09:36 angua.pid
-rwx------   1 mysql  mysql    170082 Oct 23 09:36 angua.err
drwx------  32 mysql  mysql      2048 Oct 23 12:01 .
-rw-rw----   1 mysql  mysql      1024 Oct 23 12:01 #sql_24c5_3.MYI

I _guess_ that the last file gets generated while mysql has it's problems (but I can not confirm that yet). 

How to repeat:
Good question.
Start mysql, wait some hours (something between 3 and 72 hours). I _guess_ that it happens while there happens some indexing.

Thank you for a problem report. Never seen such a situation personally...

Please, send the complete results of the following commands:

ls -l /var/mysql   (there should be a mysql subdirectory)
ls -l /var/mysql/mysql
df -k 

(not sure that it is a right command for NetBSD... I need to know, is there any space left in the /var filesystem)

The content of your error log would be useful too.

okay. I'll do it as user mysql, since under that user runs the server:
angua:~> su -
Password:
Terminal type is screen.
angua# su - mysql
$

(will be in files)

ls -l /var/mysql

Attachment: ls-l.txt (text/plain), 4.79 KiB.

ls -l /var/mysql/mysql

Attachment: ls-l-mysql.txt (text/plain), 3.01 KiB.

df -k

Attachment: df-k.txt (text/plain), 885 bytes.

tail -f /var/mysql/angua.err

Attachment: error.log (text/x-log), 4.80 KiB.

Thank you for the additional information. The only visible problem for me is the following:

/dev/sd0f                                  843726   772530     29010    96%    /var

You have almost no free space in /var filesystem (and in /, where /tmp is located, I suppose, too). So, this may lead to problems with indexes and those *sql* files you got everywhere.

Is it possible for you to free some space in /var?

There are other ideas about possible reasons for this bug among developers, but I am not ready to give them to you right now. 

Can you try to compile and use 4.1.15, by the way? What threads library do you use? What configure options?

I moved my mySQL-Tables on another filesystem with a lot of space (/ and /tmp have enough space, I guess 75MB should be enough). I'll monitor wether the problem happens again. But none of the problems yet happened while there was a full filesystem...

Another advice I can give you, if you ever try to build MySQL (4.1.15 or whatever) from sources next time on NetBSD, please, add -DHAVE_BROKEN_REALPATH option to configure. See, for example, http://jeremy.zawodny.com/blog/archives/000697.html for details. 
Even if it is already fixed for FreeBSD, you may suffer from the similar problem.

Please, inform about the results.

I have the same error as before. 

/dev/sd0a                                 204M      97M       96M    50%    /
/dev/sd0h                                 3.4G     620M      2.6G    18%    /var/space

means: there is enough space for all files.

The last entries in the error-log are:
055324 17:53:41  Starting mysqld daemon with databases from /var/space/mysql
055324 17:53:41  mysqld started
051024 17:53:41  InnoDB: Started; log sequence number 0 43942
/usr/pkg/libexec/mysqld: ready for connections.
Version: '4.1.14-log'  socket: '/tmp/mysql.sock'  port: 3306  Source distribution
051025  0:09:45 [ERROR] /usr/pkg/libexec/mysqld: Can't find file: 'evo_posts.MYI' (errno: 2)

The last entry in angua.000003 is:
# Time: 051025  0:09:47
# User@Host: blognidhoegg[blognidhoegg] @ localhost []
SET insert_id=42176;
INSERT INTO evo_hitlog( visitTime, visitURL, hit_ignore, referingURL, baseDomain, ^M

                                                hit_blog_ID, hit_remote_addr, hit_user_agent ) ^M
                                        VALUES( FROM_UNIXTIME(1130191784), '/index.php?m=199201', 'no', ^M
                                                                        'http://cheap-viagra-online.pe.nu/', 'cheap-viagra-online.pe.nu', 1, ^M
                                                                        '219.93.174.106', 'Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1)');

(means: backtrack-Spam in a log)

OK. The problem was not really related to a free space. 

Have you tried to reconfigure and recompile 4.1.14 or 4.1.15 with -DHAVE_BROKEN_REALPATH as I recommended you to do in the last comment?

I just compiled 4.1.15 with the mentioned Directive. We'll see what happens.

OK. Inform about the (bad) results with 4.1.15 as soon as you'll get them.

Since the change nothing failed anymore, so I think we can close this bug. Thanks for your help!

Closed per reporter request. -DHAVE_BROKEN_REALPATH solves this problem on NetBSD. Thanks to Sergei Golubchik (and Jeremy Zavodny!) for the solution.