MySQL Bugs: #44765: mysqld does not recognise if NO mysql database/tables have been created

Bug #44765	mysqld does not recognise if NO mysql database/tables have been created
Submitted:	10 May 2009 12:02	Modified:	12 May 2009 12:14
Reporter:	Simon Mudd (OCA)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: General	Severity:	S3 (Non-critical)
Version:	5.1.32	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
From a message on the mysql mailing list I see a list member complain about mysql saying that a table is missing and mysql_upgrade should be run.

However the real problem appeared to be that mysql_install_db had not been run.

How to repeat:
compukat@videotron.ca (michel) writes:

> I might have it fixed! I tried /home/qsys/mysql-5.1.32/libexec/mysqld
> and I would get
>
> /home/qsys/mysql-5.1.32/libexec/mysqld: Table 'mysql.plugin' doesn't exist
>
> 090510 0:19:54 [ERROR] Can't open the mysql.plugin table. Please run
> mysql_upgrade to create it.
>
> 090510 0:19:54 [ERROR] Fatal error: Can't open and lock privilege
> tables: Table 'mysql.host' doesn't exist

Actually the fix is to just:

> From reading around I tried running
>
> /home/qsys/mysql-5.1.32/bin/mysql_install_db
>
> /home/qsys/mysql-5.1.32/libexec/mysqld
>
> and now I get
>
> 090510 0:32:38 [Note] Event Scheduler: Loaded 0 events
>
> 090510 0:32:38 [Note] /home/qsys/mysql-5.1.32/libexec/mysqld: ready
> for connections.
>
> Version: '5.1.32' socket: '/home/qsys/mysql-5.1.32/mysql.sock' port:
> 3305 Source distribution

Suggested fix:
Make mysqld check on startup that all "base" mysql tables exist and if not suggest they are created, perhaps using mysql_upgrade.
If NO mysql tables exist suggest that mysql_install_db is run to create the initial database.

When there no tables in mysql database you will usually get the following message:

090510 17:13:49 [ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.host' doesn't exist

You will get the same message if only this table is missing.  But if you start server with --skip-grant-tables you will see different messages (and started server) even without any tables in the mysql directory:

090510 17:19:52  InnoDB: Started; log sequence number 0 46409
090510 17:19:52 [Warning] Can't open and lock time zone table: Table 'mysql.time_zone_leap_second' doesn't exist trying to live without them
090510 17:19:52 [ERROR] Can't open and lock privilege tables: Table 'mysql.servers' doesn't exist
090510 17:19:52 [ERROR] Cannot open mysql.db
090510 17:19:52 [ERROR] Cannot open mysql.user
090510 17:19:52 [ERROR] Cannot open mysql.event
090510 17:19:52 [ERROR] Event Scheduler: An error occurred when initializing system tables.
090510 17:19:52 [Note] /Users/openxs/dbs/5.1/libexec/mysqld: ready for connections.

In some cases (like initial installation) new error message you propose may be reasonable. But in other cases it can be misleading. So, maybe we should better just leave errors and warnings as is and solve the problem on a case by case basis?

I'm not sure what the original mailing list member was actually doing. From what he said it didn't seem like he was doing an upgrade, but based on what you say it does seem that perhaps a partial mysql db did exist which led to the use mysql_upgrade message.

Perhaps then the mysqld startup needs to be a little clearer about the problems it sees:

-- snip --
Not all the expected system tables were found in the mysql database in /path/to/the/mysql/db/directory. This suggests that your installation is incomplete, or the version of the mysql database was created by a different version of mysql.

Please revise the contents of this directory and if upgrading use mysql_upgrade to upgrade the system tables.
-- snip --

I would also suggest that on startup the datadir is CLEARLY indicated in the startup message. Why? for anyone installing a new system or having multiple instances running it's quite easy to use the wrong path and affect the wrong instance. Mysqld does NOT startup frequently (under normal circumstances) so there should be no harm in confirming where the "data files" are located.

I see that 5.1 does show the location of the mysqld on startup and that safe_mysqld shows how mysqld is being started.

090508 08:52:22 mysqld_safe Starting mysqld daemon with databases from /mysql/xxxxxxxx/data
090508 8:52:24 InnoDB: Started; log sequence number 772 1540828231
090508 8:52:25 [Note] Event Scheduler: Loaded 0 events
090508 8:52:25 [Note] /usr/local/mysql-advanced-gpl-5.1.34-linux-x86_64-glibc23/bin/mysqld: ready for connections.
Version: '5.1.34-enterprise-gpl-advanced-log' socket: '/mysql/xxxxxxx/data/mysql.sock' port: 3306 MySQL Enterprise Server - Advanced Edition (GPL)

However it seems better that mysqld itself shows where it's datadir is located as this then avoids any confusion.

So adding the datadir should help and also if the startup error message is a little bit longer and more detailed (showing which "system" tables are missing, and giving the error message I suggest) this might avoid some common install/setup errors.

I'd say that your last comment contains 2 reasonable feature requests:

1. More detailed warning/error message in case on some tables missed in mysql database

2. mysqld itself should output datadir used upon startup (we do not use mysqld_safe on Windows - additional reason).