[MDEV-12524] mysql* ignores runtime-specified "--defaults-extra-file" my.cnf override path Created: 2017-04-19  Updated: 2017-05-18  Resolved: 2017-05-18

Status: Closed
Project: MariaDB Server
Component/s: Configuration, Documentation, Scripts & Clients, Server
Affects Version/s: N/A
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: pgnd Assignee: Ian Gilfillan
Resolution: Fixed Votes: 0
Labels: None


 Description   

I launch MariaDB 10.2 via systemd with

	cat /etc/systemd/system/mariadb-custom.service
 
		...
 
		[Service]
 
		...
 
		User=mysql
 
		Group=mysql
 
		PermissionsStartOnly=true
 
 
 
		PrivateDevices=true
 
		PrivateNetwork=false
 
		PrivateTmp=true
 
 
 
		ProtectHome=true
 
		ProtectSystem=full
 
 
 
		CapabilityBoundingSet=CAP_IPC_LOCK
 
		ExecStart=/usr/local/mariadb/bin/mysqld \
 
		          --defaults-file=/usr/local/etc/mariadb/my.cnf \
 
		          --defaults-extra-file=/usr/local/etc/mariadb/my.secure.cnf
 
		...

So that

	ps ax | grep mariadb
 
		16604 ?        Ssl    0:00 /usr/local/mariadb/bin/mysqld --defaults-file=/usr/local/etc/mariadb/my.cnf --defaults-extra-file=/usr/local/etc/mariadb/my.secure.cnf

Noting in the kb

Configuring MariaDB with my.cnf: Location in Linux, Unix, Mac

identifies locations & inheritance hierarchy for my.cnf.

With configs

	cat /usr/local/etc/mariadb/my.cnf
 
		...
 
		[client]
 
		user        = root
 
		password    = 'mypasswd'
 
		...
 
 
 
	cat /usr/local/etc/mariadb/my.secure.cnf
 
		(empty)

on (re)exec

	chown root:mysql /usr/local/etc/mariadb/my*.cnf
 
	chmod 660        /usr/local/etc/mariadb/my.cnf
 
	chmod 640        /usr/local/etc/mariadb/my.secure.cnf
 
	systemctl restart mariadb
 
	mysqlshow

I can successfully connect as 'root'; e.g.

	mysqlshow
 
		+--------------------+
 
		|     Databases      |
 
		+--------------------+
 
		| information_schema |
 
		| mysql              |
 
		| performance_schema |
 
		| test               |
 
		+--------------------+

But, if I move credentials to the 'defaults-extra-file',

	cat /usr/local/etc/mariadb/my.cnf
 
		...
 
		[client]
 
		#user        = root
 
		#password    = 'mypasswd'
 
		...
 
 
 
	cat /usr/local/etc/mariadb/my.secure.cnf
 
		user        = root
 
		password    = 'mypasswd'

and (re)exec

	systemctl restart mariadb

client access fails

	mysqlshow
 
		mysqlshow: Access denied for user 'root'@'localhost' (using password: NO)

However, if I instead

	cp -af \
 
	 /usr/local/etc/mariadb/my.secure.cnf \
 
	 ~/.my.cnf

It now succeeds

	mysqlshow
 
		+--------------------+
 
		|     Databases      |
 
		+--------------------+
 
		| information_schema |
 
		| mysql              |
 
		| performance_schema |
 
		| test               |
 
		+--------------------+

This since client does NOT appear to search the specified 'defaults-extra-file' path, but DOES '~/.my.cnf',

	strace mysqlshow &> tmp.txt
 
	grep cnf tmp.txt
 
		stat("/usr/local/etc/mariadb/my.cnf", {st_mode=S_IFREG|0660, st_size=9918, ...}) = 0
 
		open("/usr/local/etc/mariadb/my.cnf", O_RDONLY|O_CLOEXEC) = 3
 
		stat("/root/.my.cnf", {st_mode=S_IFREG|0640, st_size=110, ...}) = 0
 
		open("/root/.my.cnf", O_RDONLY|O_CLOEXEC) = 3



 Comments   
Comment by Elena Stepanova [ 2017-04-19 ]

I think you are misinterpreting the way the option works (which is not surprising, because the documentation is not clear about it and needs to be improved).

I see two problems in the usage that you have described.
First, you expect that the extra file will be read by the server; it won't.
Second, you expect that the extra file will be read by clients; it won't.

Server

What the documentation is trying to say is this.
Let's assume you have the following files:

/etc/my.cnf
 
~/.my.cnf
 
/tmp/my_main.cnf
 
/tmp/my_extra.cnf

and no other cnf files (for simplicity).

/etc/my.cnf is so-called "global" config file, which is defined by its location/name.
~/.my.cnf is so-called "local" config file, which is defined by its location/name.
/tmp/my_main.cnf and /tmp/my_extra.cnf are just some files and have no meaning for the server so far.

Now, if you start the server as

mysqld

it will read both /etc/my.cnf and ~/.my.cnf, in this order, and nothing else.

If you start the server as

mysqld --defaults-extra-file=/tmp/my_extra.cnf

it will read /etc/my.cnf, /tmp/my_extra.cnf and ~/.my.cnf, in this order, and nothing else.

If you start the server as

mysqld --defaults-file=/tmp/my_main.cnf

it will read only /tmp/my_main.cnf and nothing else.

And even if you start the server as

mysqld --defaults-file=/tmp/my_main.cnf --defaults-extra-file=/tmp/my_extra.cnf

it will still read only /tmp/my_main.cnf, and nothing else.

The last variant is what you have, so your server does not read the extra file, the option in the service configuration is redundant.

Client

But what you are actually trying to do is to make the clients read the extra file. Providing it to the server does not do it, clients don't know and don't care which config files the server is reading. If you want the client to use it, you need to run the client with this option.
The same logic as in the Server part applies. If you want the client to read /etc/my.cnf, /tmp/my_extra.cnf and ~/.my.cnf, in this order, you need to run the client with --defaults-extra-file option, e.g. 

mysqlshow --defaults-extra-file=/tmp/my_extra.cnf

Please let us know if the above explains the problem you are facing. If it does, the issue will be forwarded to documentation.

Comment by Ian Gilfillan [ 2017-05-18 ]

Documentation has been expanded

Generated at Thu Feb 08 07:58:23 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.