MySQL Bugs: #34631: my.cnf no longer recognized

Bug #34631	my.cnf no longer recognized
Submitted:	18 Feb 2008 1:30	Modified:	1 Apr 2008 8:36
Reporter:	Paul Laudanski	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Server: General	Severity:	S2 (Serious)
Version:	5.1.23-rc	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	FreeBSD 6.3, mysql-server-5.1.23

Description:
/bin/sh /usr/local/bin/mysqld_safe --defaults-extra-file=/path/to/my.cnf ...

Prior to 5.1.23, the my.cnf was processed and reflected appropriately in the mysql client connection.

So a key_buffer setting to 1G would show up as such.  Now, it is not recognized at all and mysql-server defaults are in place.

How to repeat:
Upgraded from 5.1.22 to 5.1.23 on FreeBSD via ports.

Many thanks for writing a bug report.

Please look at process table, which my.cnf is choosen.

$ ps ax | grep mysql
....
43415  p7  I      0:00,04 /bin/sh ./bin/mysqld_safe --defaults-extra-file=/home/myhome/mysql51bk/etc/my.cnf
43468  p7  S      0:00,64 [mysqld]
....

It should look similar to this.

ps ax | grep mysql

/bin/sh /usr/local/bin/mysqld_safe --defaults-extra-file=/path/to/my.cnf --user=mysql --datadir=/path/to --pid-file=/path/to/hostname.pid
/usr/local/libexec/mysqld --defaults-extra-file=/path/to/my.cnf --basedir=/usr/local --datadir=/path/to --user=mysql --log-error=/path/to/hostname.err --pid-file=/path/to/hostname.pid

Note: I replaced the real value of the path with "/path/to" and the name of the pid and err files with "hostname" to hide internal path locations.  Basedir is correctly shown (not modified), and the datadir points to different path.

Paul,

you miss understood me. I wanted you to make a "ps ax" because I wanted to know, if they show you the same paths as you choose for starting mysqld_safe.

I think, there is a my.cnf somewhere on your system, that occurs the error.

Please look also, if there is a my.cnf at /var/db/myql/ or somewhere else i.e. /etc/mysql/my.cnf

You can try:
$ mysql --help

at the middle there is something like this:

Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf ~/.my.cnf /usr/home/miracee/mysql51rl/etc/my.cnf

Maybe there is an conflict with another my.cnf.

Hi Susan, no I understand your comment and thanks for the quick reply.  I've pasted in the actual results hiding it from the public.  I simply don't want the paths and filenames publicly revealed for security reasons.

Forgot to add, on 5.1.22-rc the same my.cnf was loaded up just fine.  On 5.1.23-rc, it no longer loads.  Same startup routine as before using the rc.d scripts.

Paul,

have you looked on your system if there is somewhere a second my.cnf?

For what its worth, here is the my.cnf setup:

[client]
socket      = /tmp/mysql.sock

[mysqld]
socket                          = /tmp/mysql.sock
server-id                       = 1
max_connections                 = 500
concurrent_insert               = 2
thread_concurrency              = 2
max_allowed_packet              = 16M
key_buffer                      = 2G
key_cache_division_limit        = 100
myisam_sort_buffer_size         = 64M
join_buffer_size                = 2M
read_buffer_size                = 1M
sort_buffer_size                = 10M
read_rnd_buffer_size            = 524288
table_open_cache                = 2048
thread_cache_size               = 200
tmp_table_size                  = 1G
query_cache_type                = 1
query_alloc_block_size          = 16K
query_prealloc_size             = 8K
query_cache_limit               = 2M
query_cache_size                = 100M
query_cache_min_res_unit        = 1K
wait_timeout                    = 100
interactive_timeout             = 100
log_warnings                    = 0
general_log                     = 0
log                             = 0
log_output                      = NONE
max_seeks_for_key               = 1000
myisam_recover                  = BACKUP,FORCE
safe-show-database
skip-locking
skip-innodb

[----------------------------------------]
[ Susanne Ebrecht said:
[ Paul,
[ 
[ have you looked on your system if there is somewhere a second my.cnf?
[----------------------------------------]

Hi Susanne and all readers... I replied in full detail privately, but also wanted to leave something public.  

Yes I did search, and there are three instances of my.cnf.  Settings for each are very similar if not exact.  For instance, thread_cache_size is positive integer, yet, the running mysqld reports via the mysql client a value of 0 for this variable.  Another is I have skip-innodb set in all three, yet mysqld launches with innodb started.

Thank you for the feedback.

I can not repeat described behavior on Linux. Could you please also provide error log file?

Linux?  This is on FreeBSD 6.3 via /usr/ports install.

Will paste the results of the err log privately.  For the public, there are no errors.  Here is a modified version:

080218 14:46:48 mysqld_safe mysqld from pid file /path/to/hostname.pid ended
080218 14:46:51 mysqld_safe Starting mysqld daemon with databases from /path/to
080218 14:46:51  InnoDB: Started; log sequence number 0 46409
080218 14:46:51 [Note] Event Scheduler: Loaded 0 events
080218 14:46:51 [Note] /usr/local/libexec/mysqld: ready for connections.
Version: '5.1.23-rc'  socket: '/tmp/mysql.sock'  port: 3306  FreeBSD port: mysql-server-5.1.23

However, one error does show:

Thread 847b6800 has exited with leftover thread-specific data after 4 destructor iterations

But that is not in prior error log entries when my.cnf was being read by mysqld.

Just to be clear, I've never seen this issue under FreeBSD ports mysql-server 5.0 or 5.1 until the new 5.1.23-rc.  Nor have I seen it via source build on Linux under 4.1 or prior.  Is this an isolated case?  I don't know, but everything seems to be in order on the server.

Someone not affiliated with any of this pointed out to me that mysql by default checks /etc for my.cnf. 

ln -s /path/to/my.cnf /etc/my.cnf

The above symlink in /etc/my.cnf points to the real my.cnf that mysql is being told to load on startup via "--defaults-extra-file=".  The --defaults-extra-file= apparently doesn't work, but the symlink does.  Now mysqld loads up my.cnf via the default /etc location.

So this is clearly a bug.  Is it a mysql bug?  Or is it a freebsd bug?  Or both?

Thank you for the feedback.

> The --defaults-extra-file= apparently doesn't work, but the symlink does.
> Now mysqld loads up my.cnf via the default /etc location.

> So this is clearly a bug.  Is it a mysql bug?  Or is it a freebsd bug?  Or both?

Could you please check with our binaries accessible from http://dev.mysql.com/downloads/mysql/5.1.html#freebsd to determine if it is MySQL problem of problem with FreeBSD port?

[-----------]
Could you please check with our binaries accessible from
http://dev.mysql.com/downloads/mysql/5.1.html#freebsd to determine if it is MySQL problem
of problem with FreeBSD port?
[-----------]

You want me to install the freebsd port bin from the link above to see?

Yes, I want you to install and test binaries from the above link.

Sure I'll give it a go, but that'll have to wait for the moment as I finally got my.cnf loaded.  It depends on my fixing www.castlecops.com mysqld performance.  Can you help in that arena?

In the meantime, I've emailed the maintainer of the port:

http://www.freebsd.org/cgi/ports.cgi?query=mysql-server&stype=all

According to your last request. This request is not related to defects in MySQL code. Bugs database is not supposed for such discussion. Support on using MySQL products is available both free in our forums at http://forums.mysql.com/ and for a reasonable fee direct from our skilled support engineers at http://www.mysql.com/support/ Please use these resources.

Additionally I just tested described behavior on FreeBSD machine and could not repeat the problem. So we need results of your tests with our binaries.

Last night I had to downgrade mysql to 5.0.x, amongst other things to help our perf.  Looks like I'll be staying with the 5.0.x for now.  In the meantime, I have another freebsd box I can upgrade mysql-server to 5.1.23.  I'll see if it is replicated.

Further, I've not had a response from the FreeBSD folks.

Paul, thank you for informing us about this.

We will wait results of your tests.

Paul,

this is not a bug. It's an expected behaviour.

When you have three my.cnf mysql will get confused.

Please look with:

$ mysql --help

where the system will search the my.cnf.

Usually there is something like this:

Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf ~/.my.cnf /usr/home/miracee/mysql51rl/etc/my.cnf

this means, the system first looks at /etc/my.cnf when there is a my.cnf it will take this one. Is there no my.cnf it will take the one from /etc/mysql/my.cnf and so on.

You have to delete/rename the my.cnf files from the other places and all will work fine.

Btw. this is not a FreeBSD only bug, this occurs also on Linux.

"/etc/my.cnf /etc/mysql/my.cnf ~/.my.cnf
/usr/home/miracee/mysql51rl/etc/my.cnf"

my.cnf was not in /etc, /etc/mysql, or in the user home.  It was in the dbdpath setup in rc.conf via /etc.  MySQL worked just fine with 5.1.22 and below in this configuration.  5.1.23 did not.  I had to physically put my.cnf into /etc, or create a symbolic link there.

This is most certainly a bug.

Unfortunatly I can't reproduce this:

$ bin/mysqld_safe --defaults-file=/PATH/my.cnf &
works fine

$ bin/mysqld_safe --defaults-extra-file=/PATH/my.cnf
works fine

$ mkdir /PATH/etc
$ mv /PATH/my.cnf /PATH/etc/

$ bin/mysqld_safe --defaults-extra-file=/PATH/etc/my.cnf
works fine

$ bin/mysqld_safe --defaults-file=/PATH/etc/my.cnf
works fine

tested version:
MySQL 5.1.23-rc

See

http://www.freebsd.org/cgi/query-pr.cgi?pr=ports/121900

for an example fix.

If the bug is (according to the patch) that default.c needs to search more directories than MAX_DEFAULT_DIRS (and extra directories are, natually, ignored), then it's FreeBSD bug - that is, a bug in the port. It adds two more directories to search (as compared to the vanilla MySQL 5.1), and does not adjust MAX_DEFAULT_DIRS.

See http://www.freebsd.org/cgi/cvsweb.cgi/ports/databases/mysql51-server/files/patch-mysys_def...

It would be the best to have these FreeBSD patches merged back to the upstream, then you wouldn't need to waste your time on port-specific bugs. The way to achieve it is to create a bug report for every problem that a particular port patch tries to fix and let us fix it in the MySQL.