Solved An impossible problem

This is such a weird problem that I can't even imagine where to begin diagnosing it.

mariadb 10.4.11 is installed and running. The config file is /usr/local/etc/my.cnf, and is presumably being read:
Code:
5:48:43 Sun, 05 Jan                                                                                               
[root/fastcat]~> ps | grep mysql
764  -  Is      0:00.02 /bin/sh /usr/local/bin/mysqld_safe --defaults-extra-file=/usr/local/etc/my.cnf --user=mysql --
976  -  I       0:01.97 /usr/local/libexec/mysqld --defaults-extra-file=/usr/local/etc/my.cnf --basedir=/usr/local --d
1917  1  S+      0:00.00 grep mysql

I start a copy of the command-line console, pointing it at the config file, and confirm that datadir is /Work/tables:
Code:
5:48:56 Sun, 05 Jan                                                                                                     [root/fastcat]~> mysql --defaults-file="/usr/local/etc/my.cnf" --user=root
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 31
Server version: 10.4.6-MariaDB-log FreeBSD Ports

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> show variables like "datadir%" ;
+---------------+---------------+
| Variable_name | Value         |
+---------------+---------------+
| datadir       | /Work/tables/ |
+---------------+---------------+
1 row in set (0.001 sec)

MariaDB [(none)]>


Listing the databases shows that the datadir it's using is NOT /Work/tables because it doesn't list the mapdata database:
Code:
MariaDB [(none)]> show databases ;
+--------------------+
| Database           |
+--------------------+
| TIPOFF             |
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.000 sec)
/Work/tables/mapdata does exist, and should be being listed.
Code:
6:01:13 Sun, 05 Jan                                                                                                     [root/fastcat]~> ls /Work/tables
total 272208
drwxr-xr-x  2 root   mysql          512 Aug 16 12:56 SSD/
-rw-rw----  1 mysql  mysql    164085760 Jan  3 18:00 aria_log.00000036
-rw-rw----  1 mysql  mysql           52 Jan  3 18:00 aria_log_control
-rw-rw----  1 mysql  mysql       276354 Dec  4 19:01 fastcat.local.lan.err
-rw-rw----  1 mysql  mysql            5 Dec 23 08:46 fastcat.pid
-rw-r-----  1 mysql  mysql          956 Jan  3 18:00 ib_buffer_pool
-rw-rw----  1 mysql  mysql     50331648 Jan  3 18:00 ib_logfile0
-rw-rw----  1 mysql  mysql     50331648 Aug 17 15:09 ib_logfile1
-rw-rw----  1 mysql  mysql     12582912 Jan  3 18:00 ibdata1
drwx------  2 mysql  mysql        15872 Dec 25 18:02 mapdata/
-rw-rw----  1 mysql  mysql            0 Apr 27  2018 multi-master.info
drwx------  2 mysql  mysql         2560 Apr 27  2018 mysql/
-rw-rw----  1 mysql  mysql          365 Nov 26 15:44 mysql-bin.000100
-rw-rw----  1 mysql  mysql          365 Nov 27 09:46 mysql-bin.000101
-rw-rw----  1 mysql  mysql          365 Nov 28 19:31 mysql-bin.000102

MariaDB [(none)]>
[/code]

So where is it pointing, since it's obvious not pointing at /Work/tables as it should be?

I created that database TIPOFF as a test.
But trying to add a table to that database fails with a "no such file" error:
Code:
MariaDB [(none)]> use TIPOFF ;
Database changed
MariaDB [TIPOFF]> create table foo ( bar int ) ;
ERROR 1 (HY000): Can't create/write to file '/Work/tables/TIPOFF/foo.MAI' (Errcode: 2 "No such file or directory")
MariaDB [TIPOFF]>

And if I do a find / -type d -name "TIPOFF" , it also fails to find it.
Code:
DING! Sun, 05 Jan                                                                                                 
[root/fastcat]~> find / -type d -name TIPOFF

6:01:01 Sun, 05 Jan                                                                                               
[root/fastcat]~>

Calling show binary logs shows 4 of them:
Code:
MariaDB [TIPOFF]> show binary logs ;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |     28452 |
| mysql-bin.000002 |       342 |
| mysql-bin.000003 |       365 |
| mysql-bin.000004 |       475 |
+------------------+-----------+
4 rows in set (0.000 sec)

MariaDB [TIPOFF]>

But find / -name "mysql-bin\.000001" fails to find them:
Code:
6:09:13 Sun, 05 Jan                                                                                               
[root/fastcat]~> find / -name "mysql-bin\.000001"

6:18:10 Sun, 05 Jan
[root/fastcat]~>

And fstat -v -p doesn't tell me what mysql thinks it's doing:
Code:
6:18:10 Sun, 05 Jan                                                                                               
[root/fastcat]~> ps | grep mysql
764  -  Is      0:00.02 /bin/sh /usr/local/bin/mysqld_safe --defaults-extra-file=/usr/local/etc/my.cnf --user=mysql --
976  -  I       0:02.33 /usr/local/libexec/mysqld --defaults-extra-file=/usr/local/etc/my.cnf --basedir=/usr/local --d
1920  1  I+      0:00.02 mysql --defaults-file=/usr/local/etc/my.cnf --user=root
1985  2  S+      0:00.00 grep mysql
6:20:22 Sun, 05 Jan                                                                                               
[root/fastcat]~> fstat -v -p 1920
USER     CMD          PID   FD MOUNT      INUM MODE         SZ|DV R/W
root     mysql       1920 text /        22814184 -rwxr-xr-x  4127096  r
root     mysql       1920 ctty /dev        134 crw--w----   pts/1 rw
root     mysql       1920   wd /        61797120 drwxr-xr-x    1536  r
root     mysql       1920 root /             2 drwxr-xr-x    1024  r
root     mysql       1920    0 /dev        134 crw--w----   pts/1 rw
root     mysql       1920    1 /dev        134 crw--w----   pts/1 rw
root     mysql       1920    2 /dev        134 crw--w----   pts/1 rw
root     mysql       1920    3* local stream fffff8002e3a5d20 <-> fffff8002e6d1a50

I'm utterly stumped.
 
For the fstat at the end - won't it be the mysqld process that you need to look at? So pid 976 rather than 1920?

I've been caught out by mysql reading and processing more than one my.cnf file but that doesn't seem to be the issue here.
 
I just ran a little scratch program to read mapdata records and it failed (db not found) so you're right--the problem seems to be with the daemon, not the console as I first thought.

Unfortunately, fstat -v -p 976 -f /Work/tables says that that process has nothing open. smbd has 20 or 30 file descriptors attached, but mysqld has nothing. I also tried seeing whether the daemon had something was open in /var/db or /usr/local/var/db but nothing there either.

This'll bake your noodle (it baked mine, anyway): after killing the processes, I called /usr/local/libexec/mysqld --defaults-file="/usr/local/etc/my.cnf" --user=mysql and then started the console. The daemon is now looking at /Work/tables, and I can query mapdata from the console!!

My /usr/local/etc/rc.d/mysql-server was a mess, and I may have somehow made a bigger mess trying to edit out the bugs. Like a nitwit I didn't think to save a copy of the original version, so now I have to scrounge one up somehow (there isn't a copy in the /usr/ports/.../files subtree which is where I thought one would be).
 
Problem solved (fsvo solved): mariadb/mysql reads the disc for databases only once.

When the daemon starts up, it reads the disc and puts the names of the databases into a table. That table never gets updated except by create and drop commands. It also, if the root of datadir is empty, writes fresh copies of the default files into it without notice.

So I can have the datadir pointing at the correct place, and have mounted the drive after deadstarting, but show databases doesn't show me the current state because it already looked at the disc before I called mount and won't ever look again until it's restarted.

This is a version of the same problem discussed by SirDice and ralphbsz a few weeks ago, a kind of really slooow "race" condition in which something changes, but awareness of the change doesn't get propagated. If I don't have the datadir drive mounted, mariadb doesn't care. Even if I've purged the datadir mountpoint of default files, the daemon will simply create new default files there and think it's done a good day's work. So everything looks right, but it's not right.

There doesn't seem to be a way to tell the daemon to re-read, even though the system certainly knows that the situation has changed.

It makes sense, I suppose, but it certainly created a lot of confusion!
 
Back
Top