Trouble turning off strict mode for mysql56-server (Error 1364)

[periscopepete]

Hello,

Last week I installed a new server with FreeBSD 9.1-RELEASE. On this server I did a clean install of mysql56-server. I have several others where I upgraded from earlier versions of mysql to 5.6 but on this server I installed 5.6 first.

On the new server with the clean install, mysql is in a strict mode where any column inserted without a default value it throws an error, saying

Error Code: 1364. Field 'FOO' doesn't have a default value.

I have determined that this is because the sql_mode is set to STRICT_TRANS_TABLES. I have a bunch of old applications that need to be updated before using this strict mode is feasible. In the meantime, In phpMyAdmin, I can send the command SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO" and inserts will work without an error. However, I cannot seem to configure the server to start in this mode.

I have edited /etc/my.cnf and removed STRICT_TRANS_TABLES. All it says now is:

sql_mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

The server does not seem to be reading this from the configuration and I'm not sure why. Any help or insight would be greatly appreciated.

If it's helpful, here is the error log from the last restart and failed insert:

130818 13:24:09 mysqld_safe Starting mysqld daemon with databases from /var/db/mysql
2013-08-18 13:24:09 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2013-08-18 13:24:58 829fd4400 InnoDB: Error: Table "mysql"."innodb_table_stats" not found.
2013-08-18 13:24:58 829fd4400 InnoDB: Error: Fetch of persistent statistics requested for table "FOO"."USERS" but the required system tables mysql.innodb_table_stats and mysql.innodb_index_stats are not present or have unexpected structure. Using transient stats instead.
2013-08-18 13:24:58 829fd4400 InnoDB: Error: Table "mysql"."innodb_table_stats" not found.
2013-08-18 13:24:58 829fd4400 InnoDB: Error: Fetch of persistent statistics requested for table "MIGHTCLUB"."MIGHT_FLOCK" but the required system tables mysql.innodb_table_stats and mysql.innodb_index_stats are not present or have unexpected structure. Using transient stats instead.

Thanks in advance!

 

[Herrick]

Hi,

I'm not sure if it will help you, but I find it strange that you are modifying /etc/my.cnf (unless you changed its default path or it's a typo in your email). The default path for this file is /usr/local/my.cnf.

Other than that, if you restarted the MySQL process after ( /usr/local/etc/rc.d/mysql-server restart) I don't see what can be wrong, I just did the fix you are referring to (to remove STRICT_TRANS_TABLES) and it worked like a charm.

Regards

 

[SirDice]

I'm not sure if it will help you, but I find it strange that you are modifying /etc/my.cnf (unless you changed its default path or it's a typo in your email). The default path for this file is /usr/local/my.cnf.

The default path is actually ${mysql_dbdir}/my.cnf and depends on what you set mysql_dbdir to in rc.conf. If it's not set it defaults to /var/db/mysql/. Have a look in /usr/local/etc/rc.d/mysql-server.

 

[periscopepete]

Thank you for the replies. This issue has been resolved (although as a noob I am not sure how to mark the issue as resolved).

I believe the problem was in the configuration, although I am not sure exactly what it was. (I stumbled onto a solution about a week after I posted the initial request.) I verified that MySQL was reading /etc/my.cnf. I was worried that MySQL couldn't read the configuration file, so I changed the permissions on /etc/my.cnf to 777. After doing so, the error log read:

Warning: World-writable config file '/etc/my.cnf' is ignored

So I changed the permissions back and made sure the file was owned by MySQL. Restarted and it was still in strict mode. Uninstalled MySQL, reinstalled, restarted.

All of this did nothing. So I eventually deleted the /etc/my.cnf and copied /usr/local/my-new.cnf to /etc/my.cnf and modified that. Restarted MySQL and it worked.

I have not modified the default configuration path but MySQL now reads the configuration from /etc/my.cnf.

I don't really know how to explain why it wasn't working or why it works now. I did take a look at /usr/local/etc/rc.d/mysql-server and I see that the default location for my.cnf is {mysql_dbdir} as @SirDice stated. So I don't know why it's now reading /etc/my.cnf.

 

[SirDice]

I believe the problem was in the config, although I am not sure exactly what it was. (I stumbled onto a solution about a week after I posted the initial request.) I verified that mysql was reading /etc/my.cnf. I was worried that mysql couldn't read the config file, so I changed the permissions on /etc/my.cnf to 777.

So, to fix possible read issues you gave the whole world write access? Common newbie mistake, just don't do that again and read up on permissions.

So I changed the permissions back and made sure the file was owned by mysql.

That shouldn't be needed. If the permissions are set to 644, everybody (including the kitchen-sink) can read the file regardless of the ownership.

So I eventually deleted the /etc/my.cnf and copied /usr/local/my-new.cnf to /etc/my.cnf and modified that. Restarted mysql and it worked.

There may have been an error in your my.cnf that prevented it from loading correctly. But I would have expected MySQL to complain loudly about that.

I don't really know how to explain why it wasn't working or why it works now. I did take a look at /usr/local/etc/rc.d/mysql-server and I see that the default location for

my.cnf

is {mysql_dbdir} as SirDice stated. So I don't know why it's now reading /etc/my.cnf.

That is odd and not what I expected. Perhaps it's something with version 5.6 as 5.5 (and previous versions) get it from ${mysql_dbdir}/my.cnf.

 

[glocke]

On a MySQL 5.0 server:

mysqladmin
...
[snip]
...
Default options are read from the following files in the given order:
/etc/my.cnf /usr/local/etc/my.cnf /usr/local/etc/mysql/my.cnf ~/.my.cnf

These are the global config file locations, additionally it tries to read from {mysql_dbdir}