Bug #53667 Command line tools should describe --no-defaults option in --help
Submitted: 15 May 2010 4:23 Modified: 18 May 2010 4:06
Reporter: FirstName LastName Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: General Severity:S3 (Non-critical)
Version:5.1.45 OS:Any
Assigned to: CPU Architecture:Any
Triage: Triaged: D4 (Minor)

[15 May 2010 4:23] FirstName LastName
Description:
MySQL consults /etc/mysql/my.cnf at all times. This causes problems with configuring and testing multiple instances of MySQL.

In my opinion, the policy of where my.cnf should be read from should be revised to follow in priority:
 - 1st, attempt to read @sysconfdir@/my.cnf. This means, honor the --sysconfdir parameter provided at ./configure
 - 2nd, if there's no @sysconfdir@/my.cnf, then read /etc/mysql/my.cnf

This suggestion comes after a discussion documented at:

http://lists.mysql.com/mysql/221567
http://bugs.mysql.com/bug.php?id=50336

Some things we have learned from that discussion are:

- Removing /etc/mysql/my.cnf makes it possible to run /tmp/msource/bin/mysql_install_db from a custom-configured directory (--prefix=/tmp/msource) without errors.

- Restoring /etc/mysql/my.cnf results in mysql_install_db to fail again.

How to repeat:
It's all documented at:

http://lists.mysql.com/mysql/221567

Basically:

1. configure and install a MySQL instance in /tmp/msource

./configure --prefix=/tmp/msource --exec-prefix=/tmp/msource
--bindir=/tmp/msource/bin --sbindir=/tmp/msource/sbin
--localstatedir=/tmp/msource/lib --sysconfdir=/tmp/msource
--enable-assembler --with-mysqld-ldflags=-all-static
--with-client-ldflags=-all-static --with-mysqld-user=tester
--with-unix-socket-path=/tmp/msource/var/run/mysql.sock

2. Manually create the following directories:

mkdir -p /tmp/msource/etc
mkdir -p /tmp/msource/var/run
mkdir -p /tmp/msource/var/log/mysql

3. Manually create /tmp/msource/etc/my.cnf to contain:

[mysqld]
user=tester
basedir=/tmp/msource
datadir=/tmp/msource/data
port=3318
socket=/tmp/msource/var/run/mysql.sock

[mysqld_safe]
user=tester
log-error=/tmp/msource/var/log/mysqld.log
pid-file=/tmp/msource/var/run/mysqld.pid
socket=/tmp/msource/var/run/mysql.sock
basedir=/tmp/msource
datadir=/tmp/msource/data

[client]
user=tester
port=3318
socket=/tmp/msource/var/run/mysql.sock

[mysqladmin]
user=tester
port=3318
socket=/tmp/msource/var/run/mysql.sock

[mysql]
user=tester
port=3318
socket=/tmp/msource/var/run/mysql.sock

[mysql_install_db]
user=tester
port=3318
basedir=/tmp/msource
datadir=/tmp/msource/data
socket=/tmp/msource/var/run/mysql.sock

4. "Have" an existing MySQL instance that has an /etc/mysql/my.cnf file. Probably the default MySQL installation.

5. Try to install the database tables:

/tmp/msource/bin/mysql_install_db  --basedir=/tmp/msource
--datadir=/tmp/msource/data

You will receive the error:

Installing MySQL system tables...
100513 21:53:35 [ERROR] /tmp/msource/libexec/mysqld: unknown option
'--skip-bdb'
100513 21:53:35 [ERROR] Aborting

This error occurs because mysql_install_db, "somehow", consults /etc/mysql/my.cnf, instead of /tmp/msource/etc/my.cnf.

This makes it impossible to test multiple mysql instances installed on the same machine if an existing MySQL database is already installed.

Suggested fix:
Revise the policy of where my.cnf should be read from to follow in priority:
 - 1st, attempt to read @sysconfdir@/my.cnf. This means, honor the --sysconfdir parameter provided at ./configure
 - 2nd, if there's no @sysconfdir@/my.cnf, then read /etc/mysql/my.cnf

Honoring @sysconfdir@ from ./configure is not uncommon. It allows configuring, building, and testing applications in a user's $HOME, or other, directory without needing root access to a machine.
[15 May 2010 7:51] Valeriy Kravchuk
Why not to use --no-defaults option at installation stage and explicit --defaults-file=/path/to/my.cnf in all other cases and/or --defaults-extra-file=/path/to/overwritemy.cnf? This is a kind of a workaround for many cases.

As http://dev.mysql.com/doc/refman/5.1/en/option-files.html described current behaviour clearly, I'd declare this a feature request.
[15 May 2010 13:42] FirstName LastName
How can --no-defaults be invoked ? Against which tools ?

# This comes up empty
$ mysql_install_db --help | grep defaults

# And this does not show this feature available:
$ ./configure --help|grep -i defaultsDefaults for the options are specified in brackets.
  CCAS        assembler compiler command (defaults to CC)
  CCASFLAGS   assembler compiler flags (defaults to CFLAGS)
[15 May 2010 14:53] Valeriy Kravchuk
--no-defaults can be used wiht mysql_install_db (or any mysql command line tools, even if their --help command does not say this). Had you tried to use it? Look:

valeriy-kravchuks-macbook-pro:5.1 openxs$ cat /etc/my.cnf 
[mysqld]
datadir=/tmp/mysql
valeriy-kravchuks-macbook-pro:5.1 openxs$ ls /tmp | grep mysql
valeriy-kravchuks-macbook-pro:5.1 openxs$ rm -rf var/*
valeriy-kravchuks-macbook-pro:5.1 openxs$ ls var/
valeriy-kravchuks-macbook-pro:5.1 openxs$ bin/mysql_install_db
Installing MySQL system tables...
OK
Filling help tables...
OK

To start mysqld at boot time you have to copy
support-files/mysql.server to the right place for your system

PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:

/Users/openxs/dbs/5.1/bin/mysqladmin -u root password 'new-password'
/Users/openxs/dbs/5.1/bin/mysqladmin -u root -h 77-52-28-215.dialup.umc.net.ua password 'new-password'

Alternatively you can run:
/Users/openxs/dbs/5.1/bin/mysql_secure_installation

which will also give you the option of removing the test
databases and anonymous user created by default.  This is
strongly recommended for production servers.

See the manual for more instructions.

You can start the MySQL daemon with:
cd /Users/openxs/dbs/5.1 ; /Users/openxs/dbs/5.1/bin/mysqld_safe &

You can test the MySQL daemon with mysql-test-run.pl
cd /Users/openxs/dbs/5.1/mysql-test ; perl mysql-test-run.pl

Please report any problems with the /Users/openxs/dbs/5.1/bin/mysqlbug script!

valeriy-kravchuks-macbook-pro:5.1 openxs$ ls var/
valeriy-kravchuks-macbook-pro:5.1 openxs$ ls /tmp | grep mysql
mysql
valeriy-kravchuks-macbook-pro:5.1 openxs$ ls /tmp/mysql/
mysql	test

So, as you can see, /etc/my.cnf setting is used by default by mysql_install_db, but let's try to use --no-defaults option:

valeriy-kravchuks-macbook-pro:5.1 openxs$ rm -rf /tmp/mysql
valeriy-kravchuks-macbook-pro:5.1 openxs$ ls /tmp | grep my
valeriy-kravchuks-macbook-pro:5.1 openxs$ bin/mysql_install_db --no-defaults
Installing MySQL system tables...
OK
Filling help tables...
OK

To start mysqld at boot time you have to copy
support-files/mysql.server to the right place for your system

PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:

/Users/openxs/dbs/5.1/bin/mysqladmin -u root password 'new-password'
/Users/openxs/dbs/5.1/bin/mysqladmin -u root -h 77-52-28-215.dialup.umc.net.ua password 'new-password'

Alternatively you can run:
/Users/openxs/dbs/5.1/bin/mysql_secure_installation

which will also give you the option of removing the test
databases and anonymous user created by default.  This is
strongly recommended for production servers.

See the manual for more instructions.

You can start the MySQL daemon with:
cd /Users/openxs/dbs/5.1 ; /Users/openxs/dbs/5.1/bin/mysqld_safe &

You can test the MySQL daemon with mysql-test-run.pl
cd /Users/openxs/dbs/5.1/mysql-test ; perl mysql-test-run.pl

Please report any problems with the /Users/openxs/dbs/5.1/bin/mysqlbug script!

valeriy-kravchuks-macbook-pro:5.1 openxs$ ls /tmp | grep my
valeriy-kravchuks-macbook-pro:5.1 openxs$ ls -l var
total 0
drwx------  71 openxs  staff  2414 15 тра 17:48 mysql
drwx------   2 openxs  staff    68 15 тра 17:48 test
valeriy-kravchuks-macbook-pro:5.1 openxs$ pwd
/Users/openxs/dbs/5.1

So, with this option mysql database is created in var subdirectory of a directory set using --prefix option of configure while building.
[15 May 2010 20:38] FirstName LastName
Calling mysql_install_db with --no-defaults works.

I would recommend this feature is reported with this tool is invoked with --help.
[17 May 2010 22:05] Omer Barnir
Seems from discussion issue was resolved
[18 May 2010 1:02] FirstName LastName
I want to point out that the issue has __NOT__ been resolved.

The issue is that --no-defaults is not documented under "mysql_install_db --help". That at least is a documentation bug.

For this issue to be resolved mysql_install_db should report the --no-defaults option when executed with --help.
[18 May 2010 4:06] Valeriy Kravchuk
So, mysql_install_db (and other command line tools) should describe --no-defaults option in --help results. This is a minor problem to fix. I've changed synopsis accordingly.