Bug #20748 Configuration files should not be read more than once
Submitted: 28 Jun 2006 8:28 Modified: 23 Jul 2008 18:21
Reporter: Domas Mituzas Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: General Severity:S3 (Non-critical)
Version:5.0, 5.1 OS:Linux (Linux)
Assigned to: Timothy Smith CPU Architecture:Any
Tags: bfsm_2007_08_16, bfsm_2007_10_18
Triage: D3 (Medium)

[28 Jun 2006 8:28] Domas Mituzas
Description:
There're multiple cases when my.cnf can be read twice - possible symlinks between say /usr/local/mysql/etc/my.cnf and /etc/my.cnf, or simply package compiled with --prefix=/ (as RPMs are). 

As there are entries, that have side effect if executed twice (like replicate-do-db options show up twice), as well as all /etc/my.cnf always overwrites ~/.my.cnf values then, this should be considered as a bug.

How to repeat:
# strace mysqld --print-defaults 2>&1 | egrep 'open.*my.cnf'
open("/etc/my.cnf", O_RDONLY|O_LARGEFILE) = 3
open("/root/.my.cnf", O_RDONLY|O_LARGEFILE) = 3
open("/etc/my.cnf", O_RDONLY|O_LARGEFILE) = 3

Suggested fix:
Keep a list of files already read, and skip them if encountered for second time.
[28 Jun 2006 8:44] Domas Mituzas
Verified at: ChangeSet@1.2183.17.1, 2006-06-24 13:11:09+02:00, knielsen@mysql.com

This may require intermediate fix by ensuring, that /etc/my.cnf isn't read twice (like specifying second configuration file to be /etc/mysql/my.cnf).
[31 Oct 2006 16:39] Sudhir Damle
I have same issue. strace mysqld --print-defaults 2>&1 | egrep 'open.*my.cnf' is showing two my.cnf open, also replicate-do-db is showing same database twice.

Any fix to this?

Sudhir
[10 Nov 2006 20:38] Brian c
Running 5.0.27 and also running into this problem. strace shows 2 connections to my.cnf.  Any word on a fix for this yet?

-b
[10 Nov 2006 22:08] Timothy Smith
The preferred solution for this bug would ensure that:
- $HOME/.my.cnf is always the last options file to be read, and
- all options are idempotent; that is, specifying them multiple times has the same effect as specifying them once

The problem is most likely to show up when using an RPM build of MySQL, since it will read /etc/my.cnf twice (as /etc/my.cnf and $MYSQL_HOME/my.cnf).

A number of workarounds may be possible.  One could specify --defaults-file=/etc/my.cnf to force MySQL to read just a single file.  Or /etc/my.cnf could be renamed to /root/.my.cnf.  Or probably setting MYSQL_HOME=/dev/null in the environment before mysqld is started would work.

None of those options is perfect, but hopefully they might help until a fix is available for this one.

Regards,

Timothy
[17 Oct 2007 5:24] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/35727

ChangeSet@1.2581, 2007-10-16 23:24:08-06:00, tsmith@ramayana.hindu.god +1 -0
  Bug #20748: Configuration files should not be read more than once
  
  Change init_default_directories() to remove duplicates from the
  list.
  
  Also, move --sysconfdir=<path> (DEFAULT_SYSCONFDIR) from the end
  to the middle of the list.  $HOME/.my.cnf should be last, so the
  user is able to override the system-wide settings.
[17 Oct 2007 7:05] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/35732

ChangeSet@1.2581, 2007-10-17 01:04:34-06:00, tsmith@ramayana.hindu.god +1 -0
  Bug #20748: Configuration files should not be read more than once
  
  Change init_default_directories() to remove duplicates from the
  list.
  
  Also, move --sysconfdir=<path> (DEFAULT_SYSCONFDIR) from the end
  to the middle of the list.  $HOME/.my.cnf should be last, so the
  user is able to override the system-wide settings.
[18 Oct 2007 21:15] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/35870

ChangeSet@1.2581, 2007-10-18 15:14:51-06:00, tsmith@ramayana.hindu.god +7 -0
  Bug #20748: Configuration files should not be read more than once
  
  A user could not override system-wide settings in their ~/.my.cnf,
  because the DEFAULT_SYSCONFDIR was being searched last.  Also, in
  some configurations (especially when the --sysconfdir compile-time
  option is set to /etc or /etc/mysql), the system-wide my.cnf file
  was read multiple times, causing confusion and potential problems.
  
  Move --sysconfdir=<path> (DEFAULT_SYSCONFDIR) from the last default
  directory to the middle of the list.  $HOME/.my.cnf should be last,
  so the user is able to override the system-wide settings.
  
  Change init_default_directories() to remove duplicates from the
  list.
[23 Oct 2007 1:16] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/36091

ChangeSet@1.2538, 2007-10-22 19:16:01-06:00, tsmith@ramayana.hindu.god +6 -0
  Bug #20748: Configuration files should not be read more than once
  
  A user could not override system-wide settings in their ~/.my.cnf,
  because the DEFAULT_SYSCONFDIR was being searched last.  Also, in
  some configurations (especially when the --sysconfdir compile-time
  option is set to /etc or /etc/mysql), the system-wide my.cnf file
  was read multiple times, causing confusion and potential problems.
  
  Rearrange default directories to conform to the manual and logic.
  Move --sysconfdir=<path> (DEFAULT_SYSCONFDIR) from the last default
  directory to the middle of the list.  $HOME/.my.cnf should be last,
  so the user is able to override the system-wide settings.
  
  Change init_default_directories() to remove duplicates from the
  list.
[23 Oct 2007 1:22] Timothy Smith
Latest patch against 5.0 instead of 5.1.  It was agreed that the old behavior was neither documented nor logical, and this behavior change is a pure bug fix.

See mysys/default.c commit comments in patch for details on before & after behavior of the server.

Versus the previous patch, this one contains code for OS/2, and tweaks the order of Windows defaults files to follow the manual more closely.

Docs team, the manual will need to be updated in the "Using Option Files" section.
[7 Nov 2007 22:25] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/37300

ChangeSet@1.2538, 2007-11-07 15:23:50-07:00, tsmith@ramayana.hindu.god +6 -0
  Bug #20748: Configuration files should not be read more than once
  
  A user could not override system-wide settings in their ~/.my.cnf,
  because the DEFAULT_SYSCONFDIR was being searched last.  Also, in
  some configurations (especially when the --sysconfdir compile-time
  option is set to /etc or /etc/mysql), the system-wide my.cnf file
  was read multiple times, causing confusion and potential problems.
  
  Rearrange default directories to conform to the manual and logic.
  Move --sysconfdir=<path> (DEFAULT_SYSCONFDIR) from the last default
  directory to the middle of the list.  $HOME/.my.cnf should be last,
  so the user is able to override the system-wide settings.
  
  Change init_default_directories() to remove duplicates from the
  list.
[7 Nov 2007 23:33] Timothy Smith
Queued to 5.0-, 5.1-, 6.0-build.

Docs team:  Detailed description of current behavior (and differences to old behavior) are in the default.c change comments.  Just look at the top of the patch (or ask me if anything is unclear).
[15 Nov 2007 23:00] Gregory Olson
I'm still seeing this issue in MySQL 5.1.22-0.rhel5.x86_64 rpms.   Any plans to get this fix into the 5.1 release?
[16 Nov 2007 2:43] James Day
Gregory, in the queue, probably in the next release, possibly a little later.
[21 Nov 2007 18:52] Bugs System
Pushed into 5.0.54
[21 Nov 2007 18:54] Bugs System
Pushed into 5.1.23-rc
[21 Nov 2007 18:54] Bugs System
Pushed into 6.0.4-alpha
[5 Dec 2007 18:16] Paul Dubois
Noted in 5.0.54, 5.1.23, 6.0.4 changelogs.

It was possible for option files to be read twice at program startup,
if some of the standard option file locations turned out to be the
same directory. Now duplicates are removed from the list of files to
be read.

Also, users could not override system-wide settings using ~/.my.cnf
because SYSCONFDIR/my.cnf was read last. The latter file now is read
earlier so that ~/.my.cnf can override system-wide settings.
[17 Jan 2008 14:45] Sergey Vojtovich
BUG#31486 was marked as a duplicate of this one.
[29 Jan 2008 14:18] Victoria Reznichenko
I can still reproduce this problem with 5.0.54 build:

mysql> show slave status\G
*************************** 1. row ***************************
             Slave_IO_State:
                Master_Host: localhost
                Master_User: test
                Master_Port: 3307
              Connect_Retry: 60
            Master_Log_File:
        Read_Master_Log_Pos: 4
             Relay_Log_File: victoria-bin.000001
              Relay_Log_Pos: 4
      Relay_Master_Log_File:
           Slave_IO_Running: No
          Slave_SQL_Running: No
            Replicate_Do_DB: test,test
        Replicate_Ignore_DB:
         Replicate_Do_Table:
     Replicate_Ignore_Table:
    Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
                 Last_Errno: 0
                 Last_Error:
               Skip_Counter: 0
        Exec_Master_Log_Pos: 0
            Relay_Log_Space: 411
            Until_Condition: None
             Until_Log_File:
              Until_Log_Pos: 0
         Master_SSL_Allowed: No
         Master_SSL_CA_File:
         Master_SSL_CA_Path:
            Master_SSL_Cert:
          Master_SSL_Cipher:
             Master_SSL_Key:
      Seconds_Behind_Master: NULL
1 row in set (0.00 sec)

mysql> select version();
+------------------------+
| version()              |
+------------------------+
| 5.0.54a-enterprise-gpl |
+------------------------+
1 row in set (0.00 sec)

Are you sure fix went to 5.0.54?

Tested with MySQL-server-enterprise-gpl-5.0.54a-0.sles10.i586.rpm
[1 Feb 2008 20:49] Timothy Smith
The problem is that, for example, "/etc/" and "/etc" are counted as different directories.  Obviously incorrect, and the path names must be normalized properly.
[22 Feb 2008 21:07] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/42880

ChangeSet@1.2576, 2008-02-22 14:06:53-07:00, tsmith@ramayana.hindu.god +1 -0
  Bug #20748: Configuration files should not be read more than once
  
  Normalize directory names before adding them to default_directories.
[14 Mar 2008 20:36] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/44047

ChangeSet@1.2576, 2008-03-14 14:35:17-06:00, tsmith@ramayana.hindu.god +1 -0
  Bug #20748: Configuration files should not be read more than once
  
  Normalize directory names before adding them to default_directories.
[17 Jun 2008 22:49] Chad MILLER
Excellent use of "__attribute__((unused))"!  Good with me.
[19 Jun 2008 23:21] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/48208

2640 Timothy Smith	2008-06-19
      Bug #20748: Configuration files should not be read more than once
        
      Normalize directory names before adding them to default_directories.
[20 Jun 2008 0:16] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/48210

2672 Timothy Smith	2008-06-19 [merge]
      Merge fix for Bug #20748 from 5.0
[25 Jun 2008 1:33] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/48441

2640 Timothy Smith	2008-06-24
      Bug #20748: Configuration files should not be read more than once
      
      Normalize directory names before adding them to default_directories.
[25 Jun 2008 6:23] Valeriy Kravchuk
Bug #37619 was marked as a duplicate of this one.
[27 Jun 2008 20:53] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/48681

2679 Timothy Smith	2008-06-27 [merge]
      Up-merge from 5.0: Fix for Bug#20748, Configuration files should not be read more than once
      
      Differences in 5.1: include "/etc/mysql/" in include directories; no OS/2 support.
[27 Jun 2008 21:48] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/48684

2687 Timothy Smith	2008-06-27 [merge]
      Up-merge from 5.1: Fix for Bug#20748, Configuration files should not be read more than once
[27 Jun 2008 22:19] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/48685

2687 Timothy Smith	2008-06-27 [merge]
      Up-merge from 5.1: Fix for Bug#20748, Configuration files should not be read more than once
[27 Jun 2008 22:19] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/48686

2679 Timothy Smith	2008-06-27 [merge]
      Up-merge from 5.0: Fix for Bug#20748, Configuration files should not be read more than once
      
      Differences in 5.1: include "/etc/mysql/" in include directories; no OS/2 support.
[28 Jun 2008 0:11] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/48690

2688 Timothy Smith	2008-06-27 [merge]
      Up-merge from 5.1: Merge from upstream (my:5.0-bugteam)

 2687 Timothy Smith	2008-06-27 [merge]
      Up-merge from 5.1: Fix for Bug#20748, Configuration files should not be read more than once
[28 Jun 2008 0:12] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/48691

2680 Timothy Smith	2008-06-27 [merge]
      Up-merge from 5.0: Merge from upstream (my:5.0-bugteam)

 2679 Timothy Smith	2008-06-27 [merge]
      Up-merge from 5.0: Fix for Bug#20748, Configuration files should not be read more than once
      
      Differences in 5.1: include "/etc/mysql/" in include directories; no OS/2 support.
[9 Jul 2008 12:39] Georgi Kodinov
Pushed into 5.0.66
[23 Jul 2008 9:07] Georgi Kodinov
Pushed into 5.1.28 and 6.0.7-alpha
[23 Jul 2008 18:21] Paul Dubois
The push into 6.0.x was for 6.0.6, not 6.0.7.

Noted in 5.0.66, 5.1.28, 6.0.6 changelogs:

An additional correction to the original MySQL 5.0.64/5.1.28/6.0.6 fix was made to
normalize directory names before adding them to the list of
directories. This prevents /etc/ and /etc from being considered
different, for example.
[28 Jul 2008 13:46] Bugs System
Pushed into 5.0.68  (revid:kpettersson@mysql.com-20080715152926-s5kgnqhtu93b3c8v) (pib:2)
(Retry automatic marking, to ensure nothing is missed. cm01)
[28 Jul 2008 16:46] Bugs System
Pushed into 5.1.28  (revid:joerg@mysql.com-20080714105031-88hmr2baz5di9xej) (version source revid:joerg@mysql.com-20080714105031-88hmr2baz5di9xej) (pib:3)
[29 Apr 2010 21:56] Garen Parham
I'm still seeing this issue on OpenSolaris with mysql 5.1.37.  In /etc/mysql I noticed a symlink (/etc/mysql/my.cnf -> 5.1/my.cnf); after removing the symlink and restarting mysqld, the duplicate DB entries in Replicate_Do_DB went away.