Bug #52644 mysql_install_db: 'Unknown database mysql' with non-default tmpdir

Description:
When trying to run mysql_install_db with a non-default tmpdir, it gives:
    ERROR: 1049  Unknown database 'mysql'
    100406 21:29:06 [ERROR] Aborting

Changing the tmpdir setting to /tmp fixes it.

Here's the my.cnf I'm using:

[mysql]
    #customize this
    prompt      = "SQL-SANDBOX> \u@\h:\p \d> "

[client]
    user        = mysqlsj
    password    = aba2f7df-bc45-4382-b7a1-b5d493ae92f9

#used for localhost connections
    socket      = /proj/pdsql2/pdsql3-data/data/mysql.sock

#used for remote connections
    host        = scvpdsql03
    port        = 24500

[mysqld]
#location of the mysql isntallation
    basedir     = /tool/aticad/1.0/platform/RH4
#user that the data files are owned by
    user        = mysqlsj
    datadir     = /proj/pdsql2/pdsql3-data/data

    pid-file    = /proj/pdsql2/pdsql3-data/data/mysqld.pid
    socket      = /proj/pdsql2/pdsql3-data/data/mysql.sock
    port        = 24500

    tmpdir      = /proj/pdsql2/pdsql3-data/tmp
    skip-external-locking

# vim:syntax=conf

How to repeat:
use the above my.cnf and run:
    mysql_install_db --defaults-file=my.cnf

Suggested fix:
This should 'just work.'

Thank you for the report.

I can not repeat described behavior. Does directory /proj/pdsql2/pdsql3-data/tmp exist?

If yes please provide output of `ls -la /proj/pdsql2/pdsql3-data/tmp`

I believe you're right.
The tmpdir didn't exist.
Should change this bug to a feature request.

One of:
 a) better error message
 b) mkdir -p $tmpdir

Thanks for looking at it.
--Buck

I might also put this, for good measure:

chmod -f 1777 $tmpdir

Thank you for the feedback.

Verified as feature request. I'd prefer to fix error message.

This is a server bug. When the temporary directory can't be used, creating a temporary table shows a misleading error that the database doesn't exist. (The SQL script that mysql_install_db runs creates temporary tables, hence the error is visible there.)

Can be reproduced like this (mysqld command line is for a clean, built bzr tree):

$ mkdir -p datadir/mysql ; (echo "use mysql;"; echo "create temporary table foo (a int);") | ./sql/mysqld --no-defaults --bootstrap --basedir=. --language=./sql/share/english --datadir=datadir --bootstrap --tmpdir=/non/existant/directory

ERROR: 1049  Unknown database 'mysql'
100410 22:33:13 [ERROR] Aborting

Bug #54966 was marked as duplicate of this one

Excellent, I am so glad I found this thread.  I just had this same problem, and then realized the tmpdir in my.cnf did not exist.  After I created the directory the problem was solved.  I was using the my.cnf from another of my servers that had a custom temp dir specified.

5.6.21$ ./scripts/mysql_install_db --defaults-extra-file=/tmp/my.cnf --datadir=/tmp/data56 --lc-messages-dir=/home/dveeden/mysql/5.6.21/share
Installing MySQL system tables...2014-12-03 08:47:49 0 [Warning] Using unique option prefix key_buffer instead of key_buffer_size is deprecated and will be removed in a future release. Please use the full name instead.
2014-12-03 08:47:49 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).

5.6.21$ cat /tmp/err.log 
2014-12-03 08:47:49 14411 [Warning] Buffered warning: Changed limits: max_open_files: 1024 (requested 5000)

2014-12-03 08:47:49 14411 [Warning] Buffered warning: Changed limits: table_cache: 431 (requested 2000)

2014-12-03 08:47:49 14411 [Note] InnoDB: Using atomics to ref count buffer pool pages
2014-12-03 08:47:49 14411 [Note] InnoDB: The InnoDB memory heap is disabled
2014-12-03 08:47:49 14411 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2014-12-03 08:47:49 14411 [Note] InnoDB: Memory barrier is not used
2014-12-03 08:47:49 14411 [Note] InnoDB: Compressed tables use zlib 1.2.3
2014-12-03 08:47:49 14411 [Note] InnoDB: Using Linux native AIO
2014-12-03 08:47:49 14411 [Note] InnoDB: Using CPU crc32 instructions
./bin/mysqld: Can't create/write to file '/tmp/nowhere/ib0IVY2O' (Errcode: 2 - No such file or directory)
2014-12-03 08:47:49 7fef8c4b5780  InnoDB: Error: unable to create temporary file; errno: 2
2014-12-03 08:47:49 14411 [ERROR] Plugin 'InnoDB' init function returned error.
2014-12-03 08:47:49 14411 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
2014-12-03 08:47:49 14411 [ERROR] Unknown/unsupported storage engine: InnoDB
2014-12-03 08:47:49 14411 [ERROR] Aborting

2014-12-03 08:47:49 14411 [Note] Binlog end
2014-12-03 08:47:49 14411 [Note] ./bin/mysqld: Shutdown complete

5.6.21$ cat /tmp/my.cnf
[mysqld]
tmpdir=/tmp/nowhere
log-error=/tmp/err.log

5.7.5-m15$ ./bin/mysql_install_db --defaults-extra-file=/tmp/my.cnf --datadir=/tmp/data57 --lc-messages-dir=/home/dveeden/mysql/5.7.5-m15/bin/share/mysql

5.7 didn't create a error log. It did create a fine looking datadir. It failed on startup with a message similar to 5.6 in the error log.

Posted by developer:
 
5.0 is obsolete by now. In newer versions, the server itself can bootstrap code. Closing this. Please migrate to 8.4 and see if the bootstrap process there works for you.