Bug #76688 -DMYSQL_DATADIR impact on compiling MySQL with cmake
Submitted: 14 Apr 2015 13:47 Modified: 6 Jul 2015 11:54
Reporter: Shahriyar Rzayev (OCA) Email Updates:
Status: Analyzing Impact on me:
None 
Category:MySQL Server: Compiling Severity:S1 (Critical)
Version:5.6.24, 5.7.7-rc OS:Linux (CentOS 7)
Assigned to: CPU Architecture:Any

[14 Apr 2015 13:47] Shahriyar Rzayev
Description:
Dear experts, i have some issues with compiling MySQL and understanding how it works. The documentation information is not enough at all. So maybe the information i provide is not a bug, or exactly documentation bug.

Trying install MySQL 5.6.24 on CentOS 7 using CMAKE:

After executing commands:

cmake -DCMAKE_INSTALL_PREFIX=/opt/mysql -DMYSQL_DATADIR=/opt/mysql/datadir -DSYSCONFDIR=/opt/mysql -DWITH_SSL=system -DMYSQL_TCP_PORT=3307 -DMYSQL_UNIX_ADDR=/opt/mysql/mysqld.sock -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci -DWITH_DEBUG=1 -DCOMPILATION_COMMENT="Shahriyar Rzayev's MySQL" -DOPTIMIZER_TRACE=1 -DWITH_ZLIB=system -DWITH_VALGRIND=1 -DCMAKE_C_FLAGS=-DHAVE_purify -DCMAKE_CXX_FLAGS=-DHAVE_purify

make 
make install

[root@centos7 opt]# cd mysql/
[root@centos7 mysql]# ls
bin  COPYING  data  docs  include  INSTALL-BINARY  lib  man  mysql-test  README  scripts  share  sql-bench  support-files

As you see datadir directory is not created in basedir folder (/opt/mysql/datadir). 

I also wonder where and how port, socket , collation, charset and other MySQL related  options stored after compiling (there is no such information in documentation). If they are written to config file(my.cnf), as you see there is also no such file in specified path.

Then, running following command also interesting:

[root@centos7 mysql]# /opt/mysql/scripts/mysql_install_db --user=mysql
Installing MySQL system tables...2015-04-14 17:51:06 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2015-04-14 17:51:06 0 [Note] ./bin/mysqld (mysqld 5.6.24-debug) starting as process 4588 ...
2015-04-14 17:51:06 4588 [Note] InnoDB: Using atomics to ref count buffer pool pages
2015-04-14 17:51:06 7fa1824b5840 InnoDB: !!!!!!!! UNIV_DEBUG switched on !!!!!!!!!
2015-04-14 17:51:06 7fa1824b5840 InnoDB: !!!!!!!! UNIV_SYNC_DEBUG switched on !!!!!!!!!
2015-04-14 17:51:06 4588 [Note] InnoDB: Completed initialization of buffer pool
2015-04-14 17:51:06 4588 [ERROR] InnoDB: Unable to lock ./ibdata1, error: 11
2015-04-14 17:51:06 4588 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log files.
2015-04-14 17:51:06 4588 [Note] InnoDB: Retrying to lock the first data file

If you list process table, it will reveal that datadir is still default "/var/lib/mysql", but we have specified it above as cmake option.

[root@centos7 ~]# ps aux | grep mysql
root      4585  0.2  0.3 143428  5608 pts/2    S+   17:51   0:00 /usr/bin/perl /opt/mysql/scripts/mysql_install_db --user=mysql
mysql     4588  0.1  1.8 324600 34052 pts/2    Sl+  17:51   0:00 ./bin/mysqld --bootstrap --basedir=. --datadir=/var/lib/mysql --log-warnings=0 --loose-skip-ndbcluster --max_allowed_packet=8M --default-storage-engine=MyISAM --net_buffer_length=16K --user=mysql

Still we must specify datadir manually:

/opt/mysql/scripts/mysql_install_db --user=mysql --datadir=/opt/mysql/datadir

And then run mysqld_safe as below again manually:

/opt/mysql/bin/mysqld_safe --defaults-file=/opt/mysql/my.cnf --user=mysql --datadir=/opt/mysql/datadir --socket=/opt/mysql/datadir/mysqld-new.sock --pid-file=/opt/mysql/datadir/mysqld-new.pid --port=3307  --log-error=/opt/mysql/datadir/error.err &

So if we provide these information again, why to write them as cmake option for compiling?

Tried to connect 

[root@centos7 ~]# /opt/mysql/bin/mysql -u root --port=3307
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/opt/mysql/mysqld.sock' (2)

:) It seems that only socket information persists.

How to repeat:
Download and try to compile MySQL with Cmake with specified options in description.

Suggested fix:
1. More informative documentation about exact impact of cmake options to build process.
2. Make available datadir, socket, config file and port etc. possible information for mysql_install_db and mysqld_safe for further usage from cmake as default. If somebody wants to change the specified compiler options at run time , let to specify explicitly as i did.
[29 Jun 2015 6:33] Shahriyar Rzayev
Reproducible with 5.7.7-rc,
Even with explicitly specifying -DMYSQL_DATADIR with cmake, it has no effect to --initialize option:

cmake -DCMAKE_INSTALL_PREFIX=/opt/mysql -DMYSQL_DATADIR=/opt/mysql/datadir -DSYSCONFDIR=/opt/mysql -DWITH_SSL=system -DMYSQL_TCP_PORT=3307 -DMYSQL_UNIX_ADDR=/opt/mysql/mysqld.sock -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci -DWITH_DEBUG=1 -DCOMPILATION_COMMENT="Shahriyar Rzayev's MySQL" -DOPTIMIZER_TRACE=1 -DWITH_ZLIB=system  -DCMAKE_C_FLAGS=-DHAVE_purify -DCMAKE_CXX_FLAGS=-DHAVE_purify  -DWITH_BOOST=/opt/boost -DWITH_VALGRIND=1

[root@centos7_vm mysql]# bin/mysqld --initialize --user=mysql
2015-06-29T06:27:15.707831Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2015-06-29T06:27:17.058138Z 0 [Warning] InnoDB: New log files created, LSN=45790
2015-06-29T06:27:17.615638Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2015-06-29T06:27:18.241961Z 0 [Warning] CA certificate ca.pem is self signed.

So datadir will be default = /var/lib/mysql again:

[root@centos7_vm mysql]# ls /var/lib/mysql/
ca-key.pem  client-cert.pem  ib_buffer_pool  ib_logfile0  mysql               private_key.pem  server-cert.pem  sys
ca.pem      client-key.pem   ibdata1         ib_logfile1  performance_schema  public_key.pem   server-key.pem
[30 Jun 2015 7:31] Umesh Shastry
Hi Shahriyar,

Thank you for the report and sorry for the delay in getting back to you on this.  I checked this but somehow missed earlier. Could you check this again at your end?

// Download source
[root@localhost Downloads]# md5sum mysql-5.6.24.tar.gz 
68e1911f70eb1b02170d4f96bf0f0f88  mysql-5.6.24.tar.gz
[root@localhost Downloads]# ls -l mysql-5.6.24.tar.gz 
-rw-rw-r--. 1 ushastry ushastry 33178592 Apr 15 07:24 mysql-5.6.24.tar.gz
[root@localhost Downloads]# 
// uncompress and build
[root@localhost Downloads]#tar -zxvf mysql-5.6.24.tar.gz
[root@localhost Downloads]# cd mysql-5.6.24
[root@localhost mysql-5.6.24]# cmake -DCMAKE_INSTALL_PREFIX=/opt/mysql -DMYSQL_DATADIR=/opt/mysql/datadir -DSYSCONFDIR=/opt/mysql -DWITH_SSL=system -DMYSQL_TCP_PORT=3307 -

DMYSQL_UNIX_ADDR=/opt/mysql/mysqld.sock -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci -DWITH_DEBUG=1 -DCOMPILATION_COMMENT="XXXXXXXXXXXXX" -DOPTIMIZER_TRACE=1 -DWITH_ZLIB=system -

DWITH_VALGRIND=1 -DCMAKE_C_FLAGS=-DHAVE_purify -DCMAKE_CXX_FLAGS=-DHAVE_purify
[root@localhost mysql-5.6.24]# make && make install

// confirm installation
[root@localhost mysql-5.6.24]# ls -l /opt/mysql/
total 156
drwxr-xr-x.  2 root root   4096 Apr 15 09:25 bin
-rw-r--r--.  1 root root  17987 Mar 25 12:34 COPYING
drwxr-xr-x.  3 root root     17 Apr 15 09:25 data
drwxr-xr-x.  2 root root     52 Apr 15 09:25 docs
drwxr-xr-x.  3 root root   4096 Apr 15 09:25 include
-rw-r--r--.  1 root root 102986 Mar 25 12:35 INSTALL-BINARY
drwxr-xr-x.  3 root root   4096 Apr 15 09:25 lib
drwxr-xr-x.  4 root root     28 Apr 15 09:25 man
drwxr-xr-x. 10 root root   4096 Apr 15 09:26 mysql-test
-rw-r--r--.  1 root root   2496 Mar 25 12:34 README
drwxr-xr-x.  2 root root     29 Apr 15 09:25 scripts
drwxr-xr-x. 28 root root   4096 Apr 15 09:26 share
drwxr-xr-x.  4 root root   4096 Apr 15 09:26 sql-bench
drwxr-xr-x.  2 root root   4096 Apr 15 09:26 support-files
[root@localhost mysql-5.6.24]# 

// Confirmed that datadir, socket, port etc are as is provided for cmake

[root@localhost mysql]# bin/mysqld --verbose --help|grep datadir
**
*
datadir                                                    /opt/mysql/datadir/
general-log-file                                           /opt/mysql/datadir/localhost.log
pid-file                                                   /opt/mysql/datadir/localhost.pid
slow-query-log-file                                        /opt/mysql/datadir/localhost-slow.log

[root@localhost mysql]# bin/mysqld --verbose --help|grep socket
..

socket                                                     /opt/mysql/mysqld.sock

###  It might be necessary to specify other options such as --basedir or --datadir (known and documented behavior https://dev.mysql.com/doc/refman/5.6/en/mysql-install-db.html)

[root@localhost mysql]# scripts/mysql_install_db --basedir=/opt/mysql/ --datadir=/opt/mysql/datadir
..

[root@localhost mysql]# ls -l /opt/mysql/datadir/
total 110600
-rw-rw----. 1 root root 12582912 Apr 15 09:43 ibdata1
-rw-rw----. 1 root root 50331648 Apr 15 09:43 ib_logfile0
-rw-rw----. 1 root root 50331648 Apr 15 09:43 ib_logfile1
drwx------. 2 root root     4096 Apr 15 09:43 mysql
drwx------. 2 root root     4096 Apr 15 09:43 performance_schema
drwx------. 2 root root        6 Apr 15 09:43 test
[root@localhost mysql]# 

## Startup mysqld - notice that it uses default datadir configured at the time of compiling

[root@localhost mysql]# bin/mysqld --user=root &
[1] 18899
..
2015-04-15 09:47:07 18899 [Note] InnoDB: Waiting for purge to start
2015-04-15 09:47:07 18899 [Note] InnoDB: 5.6.24 started; log sequence number 1625987
2015-04-15 09:47:07 18899 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: e8799b8d-e375-11e4-936c-

0800274ac80e.
2015-04-15 09:47:07 18899 [Note] RSA private key file not found: /opt/mysql/datadir//private_key.pem. Some authentication plugins will not work.
2015-04-15 09:47:07 18899 [Note] RSA public key file not found: /opt/mysql/datadir//public_key.pem. Some authentication plugins will not work.
2015-04-15 09:47:07 18899 [Note] Server hostname (bind-address): '*'; port: 3307
2015-04-15 09:47:07 18899 [Note] IPv6 is available.
2015-04-15 09:47:07 18899 [Note]   - '::' resolves to '::';
2015-04-15 09:47:07 18899 [Note] Server socket created on IP: '::'.
2015-04-15 09:47:08 18899 [Note] Event Scheduler: Loaded 0 events
2015-04-15 09:47:08 18899 [Note] bin/mysqld: ready for connections.
Version: '5.6.24-debug'  socket: '/opt/mysql/mysqld.sock'  port: 3307  XXXXXXXXXXXXX

// confirmed that it takes all specified defaults

[root@localhost mysql]# bin/mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.24-debug XXXXXXXXXXXXX

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql> show variables like 'port';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| port          | 3307  |
+---------------+-------+
1 row in set (0.02 sec)

mysql> show variables like 'socket';
+---------------+------------------------+
| Variable_name | Value                  |
+---------------+------------------------+
| socket        | /opt/mysql/mysqld.sock |
+---------------+------------------------+
1 row in set (0.00 sec)

mysql> show variables like 'datadir';
+---------------+---------------------+
| Variable_name | Value               |
+---------------+---------------------+
| datadir       | /opt/mysql/datadir/ |
+---------------+---------------------+
1 row in set (0.00 sec)

mysql> show variables like '%version%';
+-------------------------+---------------+
| Variable_name           | Value         |
+-------------------------+---------------+
| innodb_version          | 5.6.24        |
| protocol_version        | 10            |
| slave_type_conversions  |               |
| version                 | 5.6.24-debug  |
| version_comment         | XXXXXXXXXXXXX |
| version_compile_machine | x86_64        |
| version_compile_os      | Linux         |
+-------------------------+---------------+
7 rows in set (0.00 sec)

mysql> \s
--------------
bin/mysql  Ver 14.14 Distrib 5.6.24, for Linux (x86_64) using  EditLine wrapper

Connection id:		2
Current database:	
Current user:		root@localhost
SSL:			Not in use
Current pager:		stdout
Using outfile:		''
Using delimiter:	;
Server version:		5.6.24-debug XXXXXXXXXXXXX
Protocol version:	10
Connection:		Localhost via UNIX socket
Server characterset:	utf8
Db     characterset:	utf8
Client characterset:	utf8
Conn.  characterset:	utf8
UNIX socket:		/opt/mysql/mysqld.sock
Uptime:			4 min 5 sec

Threads: 1  Questions: 11  Slow queries: 0  Opens: 67  Flush tables: 1  Open tables: 60  Queries per second avg: 0.044
--------------

Thanks,
Umesh
[6 Jul 2015 11:42] Shahriyar Rzayev
Hi Umesh, 

still getting same thing with 5.6.25:

cmake -DCMAKE_INSTALL_PREFIX=/opt/mysql-5.6 -DMYSQL_DATADIR=/opt/mysql-5.6/datadir -DSYSCONFDIR=/opt/mysql-5.6 -DWITH_SSL=system -DMYSQL_TCP_PORT=3307 -DMYSQL_UNIX_ADDR=/opt/mysql-5.6/mysqld.sock -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci -DWITH_DEBUG=1 -DCOMPILATION_COMMENT="Shahriyar Rzayev's MySQL" -DOPTIMIZER_TRACE=1 -DWITH_ZLIB=system -DWITH_VALGRIND=1 -DCMAKE_C_FLAGS=-DHAVE_purify -DCMAKE_CXX_FLAGS=-DHAVE_purify

make & make install

[root@centos7_vm mysql-5.6]# bin/mysqld --verbose --help | grep datadir
datadir                                                    /var/lib/mysql/

[root@centos7_vm mysql-5.6]# bin/mysqld --verbose --help | grep socket
performance-schema-max-socket-classes                      10
performance-schema-max-socket-instances                    -1
socket                                                     /var/lib/mysql/mysql.sock

[root@centos7_vm mysql-5.6]# bin/mysqld --user=root &
[1] 25351
[root@centos7_vm mysql-5.6]# 2015-07-06 16:23:48 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2015-07-06 16:23:48 0 [Note] bin/mysqld (mysqld 5.6.25-debug) starting as process 25351 ...
2015-07-06 16:23:48 25351 [Warning] Can't create test file /var/lib/mysql/centos7_vm.lower-test
2015-07-06 16:23:48 25351 [Warning] Can't create test file /var/lib/mysql/centos7_vm.lower-test
bin/mysqld: Can't change dir to '/var/lib/mysql/' (Errcode: 2 - No such file or directory)
2015-07-06 16:23:48 25351 [ERROR] Aborting
[6 Jul 2015 11:54] Umesh Shastry
Hi Shahriyar,

Let me retry and get back to you in a day or two.

Thanks,
Umesh