| Bug #68643 | sql_mode is unkind in my.cnf created by mysql_install_db | ||
|---|---|---|---|
| Submitted: | 12 Mar 2013 1:49 | Modified: | 9 May 2014 10:14 |
| Reporter: | Tsubasa Tanaka (OCA) | Email Updates: | |
| Status: | Duplicate | Impact on me: | |
| Category: | MySQL Server: Installing | Severity: | S4 (Feature request) |
| Version: | 5.6.10 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | my.cnf, mysql_install_db | ||
[12 Mar 2013 1:49]
Tsubasa Tanaka
[12 Mar 2013 10:00]
James Day
Seems like a good idea to check the config files that are read first and concatenate non-conflicting settings in them onto the one in the default my.cnf file. James Day, MySQL Senior Principal Support Engineer, Oracle
[28 Mar 2013 20:21]
Arnaud Adant
This issue is verified. mysql_install_db creates this configuration file under the basedir : # For advice on how to change settings please see # http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html [mysqld] # Remove leading # and set to the amount of RAM for the most important data # cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%. # innodb_buffer_pool_size = 128M # Remove leading # to turn on a very important data integrity option: logging # changes to the binary log between backups. # log_bin # These are commonly set, remove the # and set as required. # basedir = ..... # datadir = ..... # port = ..... # server_id = ..... # socket = ..... # Remove leading # to set options mainly useful for reporting servers. # The server defaults are faster for transactions and fast SELECTs. # Adjust sizes as needed, experiment to find the optimal values. # join_buffer_size = 128M # sort_buffer_size = 2M # read_rnd_buffer_size = 2M sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES so that it overrides sql_mode for the new installation This is documented here : http://dev.mysql.com/doc/refman/5.6/en/mysql-install-db.html " With one exception, the settings in the default option file are commented and have no effect. The exception is that the file changes the sql_mode system variable from its default of NO_ENGINE_SUBSTITUTION to also include STRICT_TRANS_TABLES. This setting produces a server configuration that results in errors rather than warnings for bad data in operations that modify transactional tables. See Section 5.1.7, “Server SQL Modes”. " So if you need to comment this line or remove the file if you want /etc/my.cnf Changing the status to "Not a bug".
[13 Apr 2013 15:20]
Tsubasa Tanaka
I know that its behaver is correct and described in manual. This is Not a Bug but Feature Request. I think this marks as Won't Fix. But I wonder who hopes additional my.cnf with sql_mode which isn't default value? If MySQL needs new sql_mode, set it as implicit defaut value is better way.
[16 Jul 2013 2:42]
Tsubasa Tanaka
I want mysql_install_db to get to be like this.
*** scripts/mysql_install_db.org 2013-06-04 12:02:51.000000000 +0900
--- scripts/mysql_install_db 2013-07-16 11:41:40.811779537 +0900
***************
*** 75,80 ****
--- 75,81 ----
--help Display this help and exit.
--ldata=path The path to the MySQL data directory. Same as --datadir.
--no-defaults Don't read default options from any option file.
+ --no-create-config Don't create my.cnf in basedir.
EOF1
if ( $^O !~ m/^(MSWin32|cygwin)$/ ) {
print <<EOF2;
***************
*** 152,157 ****
--- 153,159 ----
"rpm",
"help",
"random-passwords",
+ "no-create-config",
# These options will also be pased to mysqld.
"defaults-file=s",
***************
*** 441,446 ****
--- 443,451 ----
$copy_cfg_file =~ s/my.cnf/my-new.cnf/;
# Too early to print warning here, the user may not notice
}
+
+ if (! $opt->{'no-create-config'})
+ {
open (TEMPL, $cfg_template) or error($opt, "Could not open config template $cfg_template");
if (open (CFG, "> $copy_cfg_file"))
{
***************
*** 457,462 ****
--- 462,468 ----
$failed_write_cfg= 1;
}
close TEMPL;
+ }
# ----------------------------------------------------------------------
# Now we can get arguments from the groups [mysqld] and [mysql_install_db]
[16 Jul 2013 8:05]
martin fuxa
right, install MySQL-server from RPM wrote New default config file was created as /usr/my.cnf and will be used by default by the server when you start it. You may edit this file to change server settings I'm admin of mysql server for a long time, but this is something new shit. Conf is always /etc/my.cnf. good old days :)
[23 Jul 2013 1:56]
James Day
I've set this feature request to verified because it's a request to add an option not to create a my.cnf file for a server that doesn't have one but instead use only the global one, assuming it exists. Perhaps also or instead a request to have a feature that would let the global settings in etc/my.cnf take precedence over the server-specific ones in $MYSQL_HOME/my.cnf I don't think it should be implemented. The fix is easy enough: have a my.cnf for the server in $MYSQL_HOME. If that file exists, the installer won't change it. So it does exactly what the option change request would do but persistently across all future upgrades, whether they are the regular frequent ones or bigger version changes. That reduces the chance that someone will do a patch upgrade and forget the option. Itsubasa, This is working as it's supposed to: etc/my.cnf is for global settings shared by all MySQL copies/versions running on the server. Not just one server. It would be wrong to put a change that is intended just for MySQL 5.6 there because it would also affect 5.5 and other versions. $MYSQL_HOME/my.cnf is where server-specific settings go. > If MySQL needs new sql_mode, set it as implicit defaut value is better way. We considered that and rejected it for 5.6. If we had done that it would have affected upgraded servers with no SQL mode setting in a my.cnf file as well as new installations. That would have been a big deal for some people: the new setting will cause data loss for applications that rely on the way non-safe mode works. To protect upgraders we put this change in the my.cnf file for the specific server. If there is an existing server default file the installer won't overwrite it, so an upgraded setup won't get the new setting and won't break. Instead it'll write a new file called my-default.cnf to show what would have been done. The server will not try to use that my-default.cnf file. It affected you because you don't have a my.cnf file for the specific server, so the leave the existing my.cnf alone protection didn't work for you. The best fix is for you to use the per-server settings location for your my.cnf file. That's the way people are supposed to do it. If all of the servers you have on the box have the same settings you can put an empty file in $MYSQL_HOME/my.cnf for each of them and the installer won't touch it. We might do the same in 5.7 if there is some option we want new installations to have but are trying to avoid having it affect existing installations because we think it would be bad for them. At the moment I don't know of anything new where we might do that but this could change between now and 5.7 release. Martin, It's been this way since at least MySQL 3.23 as you can see if you look at http://dev.mysql.com/doc/refman/4.1/en/option-files.html : etc.my.cnf for settings shared by all servers. $MYSQL_HOME/my.cnf (called just DATADIR back then because the ability to set the home location wasn't around) for settings for individual servers. What's different is that if you happen not to have a my.cnf file for a MySQL server, the default one will be created. If you have only ever had one server per box and have access rights to change that file I can understand you finding someone put a my.cnf file in etc/my.cnf and thinking that etc/my.cnf is where all of the settings go. Is there something that we could have put in the documentation that you'd have seen and learned of this? Maybe something about it in the upgrade instructions in the manual that you would have seen while reading that? Did you see that message from the RPM installer letting you know about this? I'm wondering whether it was prominent enough or effective enough or whether we might need to do something more? I'm sorry that this surprised you, even though it is working the way we expect it to work. How did you end up with a my.cnf file in etc/my.cnf? MySQL (MySQL AB, Sun or Oracle) RPM installer or distro maybe? Some other way? I'm interested in knowing how this caused you to be caught out because we tried to do this so people wouldn't be. Maybe knowing that will help us to avoid hassle for others later. James Day, MySQL Senior Principal Support Engineer, Oracle
[23 Jul 2013 2:50]
Tsubasa Tanaka
Hello James, Thank you for telling me your opinion. I understand that MySQL recomends to use $MYSQL_HOME/my.cnf for each mysqld instance. And I'll change my mind to use $MYSQL_HOME/my.cnf instead of /etc/my.cnf, it's okay, there's no problem at all, *AT LEAST FOR ME*. But most rpm package redistributers(ex. remi, rpmforge, and on) don't approve yet. Their rpm package still creates /etc/my.cnf in its installation. I don't like this behavier but it is used many lightweight MySQL users in fact.. After that mysql_install_db creates /usr/my.cnf and sets sql_mode which is *NOT* implicit default value of MySQL 5.5 nor 5.6. It leads to confusion especially for light weight users. After I heard you, I change my mind that the best way is package redistributers don't create /etc/my.cnf and guide users to use /usr/my.cnf in their package. regards,
[23 Jul 2013 13:35]
Joe Koenig
I'd like to add my $0.02 to this issue. I recently upgraded from 5.5 to 5.6. I had a customized config file in /etc/my.cnf. I restarted my server and had some queries start failing. I checked the error messages and realized they were related to STRICT_TRANS_TABLES. The manual states that the default value for sql-mode is '' (see http://dev.mysql.com/doc/refman/5.6/en/server-options.html#option_mysqld_sql-mode). Despite that, I went ahead and edited /etc/my.cnf to explicitly set sql-mode to ''. That changed nothing. I searched the web and found others talking of a new /usr/my.cnf file. I looked, and sure enough, the upgrade had installed a /usr/my.cnf, which had an uncommented line that set sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES. So, I'm also in the camp of people that doesn't understand why a new file is created that sets settings to something other than what the manual says in the default setting. Finally, any reason the manual states the Option-File Format for the variable is "sql-mode", yet the new /usr/my.cnf file has "sql_mode"?
[24 Jul 2013 19:35]
James Day
Joe, You can use - or _ and either not working would normally merit a bug report. The documentation team tries to stick to whatever form was used by the developer who added the option to the source code. Others tend to use whatever seems easiest to them and _ is by far the most common choice. The contents of the option file wasn't written by the documentation team so it uses _. When you installed, did you receive and notice the message that martin mentioned: "New default config file was created as /usr/my.cnf and will be used by default by the server when you start it. You may edit this file to change server settings" I'm asking because if you did, that might indicate that it's useful to add a mention of SQL mode to that message. Did you install from one of our builds? If not, did the one that you used also give that message? I'm asking to check whether any change we make to this message might be seen by others. In the manual page you linked to there is currently this just after the box that gives the server compiled-in default values: "Note MySQL installation programs may configure the SQL mode during the installation process. For example, mysql_install_db creates a default option file named my.cnf in the base installation directory. This file contains a line that sets the SQL mode; see Section 4.4.3, “mysql_install_db — Initialize MySQL Data Directory”. If the SQL mode differs from the default or from what you expect, check for a setting in an option file that the server reads at startup." It's there because I asked the documentation team to mention this. Was that text there when you read the page? Did you notice it? Did you read http://dev.mysql.com/doc/refman/5.6/en/upgrading-from-previous-series.html before upgrading? If you did, did you notice the Note that the page starts with: "Note Beginning with MySQL 5.6.6, several MySQL Server parameters have defaults that differ from previous releases. See the notes regarding these changes later in this section, particularly regarding overriding them to preserve backward compatibility if that is a concern." That page mentions the compiled-in changes in 5.6 but not this one. I'm asking because if you read it that would help to illustrate the value of the documentation team mentioning this there because it could have reduced the chance of you being surprised. Did you read What Is New in MySQL 5.6 at http://dev.mysql.com/doc/refman/5.6/en/mysql-nutshell.html and/or the Changes to Server Defaults page it links to at http://dev.mysql.com/doc/refman/5.6/en/server-default-changes.html ? I'm asking because those don't mention the default config file and I'm wondering if a mention in either place might have helped. I'm sorry that the settings change broke things for you for a while. Avoiding that is why we put it in the server-specific config file instead of changing the compiled-in default, had the config file not overwrite the existing server-specific config file, have the message on installation and mention it in the documentation for the option. If anyone else was caught out by this and can say more about what they did or didn't see or where they did or didn't look before or during installation that would be helpful. We don't want people to be surprised by this and I'll be using that feedback to request changes to cut the chance of surprise further. As more advance notice, you can expect that we will change the compiled-in value in 5.7. If you haven't already done it now please ensure that you have a server-specific configuration file before then. Doing it now would be good, because that will ensure that you don't get the change later after one of the regular updates. James Day, MySQL Senior Principal Support Engineer, Oracle
[9 May 2014 10:14]
Terje Røsten
Duplicate of Bug #71600.
