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:
Category:MySQL Server Severity:S2 (Serious)
Version:4.1.14 OS:NetBSD-2.0G
Assigned to: CPU Architecture:Any

[23 Oct 2005 14:11] Hanno Wagner
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
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
$ 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.
[23 Oct 2005 14:44] Valeriy Kravchuk
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.
[23 Oct 2005 15:18] Hanno Wagner
okay. I'll do it as user mysql, since under that user runs the server:
angua:~> su -
Terminal type is screen.
angua# su - mysql

(will be in files)
[23 Oct 2005 15:18] Hanno Wagner
ls -l /var/mysql

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

[23 Oct 2005 15:19] Hanno Wagner
ls -l /var/mysql/mysql

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

[23 Oct 2005 15:19] Hanno Wagner
df -k

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

[23 Oct 2005 15:19] Hanno Wagner
tail -f /var/mysql/angua.err

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

[24 Oct 2005 13:18] Valeriy Kravchuk
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?
[24 Oct 2005 15:57] Hanno Wagner
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...
[24 Oct 2005 16:28] Valeriy Kravchuk
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.
[24 Oct 2005 22:19] Hanno Wagner
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
                                                                        '', 'Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1)');

(means: backtrack-Spam in a log)
[25 Oct 2005 11:39] Valeriy Kravchuk
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?
[25 Oct 2005 14:54] Hanno Wagner
I just compiled 4.1.15 with the mentioned Directive. We'll see what happens.
[25 Oct 2005 16:48] Valeriy Kravchuk
OK. Inform about the (bad) results with 4.1.15 as soon as you'll get them.
[2 Nov 2005 19:19] Hanno Wagner
Since the change nothing failed anymore, so I think we can close this bug. Thanks for your help!
[3 Nov 2005 7:27] Valeriy Kravchuk
Closed per reporter request. -DHAVE_BROKEN_REALPATH solves this problem on NetBSD. Thanks to Sergei Golubchik (and Jeremy Zavodny!) for the solution.