sql_mode not updating, even though I've changed it in my.cnf

Question

I'm trying to tweak a database that we moved from a mySQL 5.5 server, to a 5.7 server. It's complaining about the date being 0000-00-00 format. I'm trying to fix this, but I can't get past the errors.

In my.cnf, I have:

sql_mode=ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,TRADITIONAL,ALLOW_INVALID_DATES

I restart mySQL, and then do this in phpmyadmin:

SHOW VARIABLES LIKE 'sql_mode'

But it shows a totally wrong/different list!

sql_mode
STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ALLOW_INVALID_DATES,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

What am I doing wrong? This is driving me nuts!

Maybe wrong my.cnf? Executed upgrade script? I prefer MySQL Workbench over phpMyAdmin. https://stackoverflow.com/questions/35565128/mysql-incorrect-datetime-value-0000-00-00-000000 — BitAccesser, Oct 02 '17 at 07:07
@BitAccesser mmm I'm pretty sure. I used VestaCP for the control system, and in the online tool it shows as `/etc/mysql/my.cnf` (I'm editing it manually myself, and the online tool is showing my changes as well. Very odd :/ — Andrew Newby, Oct 02 '17 at 07:14
@BitAccesser all I was saying, is that I use VestaCP to *administer* the server (bit like CPanel), and from that panel I can see the my.cnf location is /etc/mysql/my.cnf , which is the one I'm editing. — Andrew Newby, Oct 02 '17 at 07:19
OK, thought it was a MySQL-Tool. A my.cnf file in the file-system has no meaning. Important is the used my.cnf file. You should find the path in phpMyAdmin. — BitAccesser, Oct 02 '17 at 07:22
@BitAccesser nah its just a WHM GUI. Looks like Solarflare got to the bottom of it. What I was seeing with the `SHOW VARIABLES LIKE 'sql_mode' ` is actually the computized value. Because I had `TRADITIONAL` as part of my variables, that was causing a few others to get added (STRICT_TRANS_TABLES, STRICT_ALL_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER), which is where the problem was coming from. All good now :) Thanks for your offers though! — Andrew Newby, Oct 02 '17 at 07:35

score 2 · Accepted Answer · edited Jun 20 '20 at 09:12

According to the manual, TRADITIONAL includes several other modes:

TRADITIONAL

Before MySQL 5.7.4, and in MySQL 5.7.8 and later, TRADITIONAL is equivalent to STRICT_TRANS_TABLES, STRICT_ALL_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER, and NO_ENGINE_SUBSTITUTION.

From MySQL 5.7.4 though 5.7.7, TRADITIONAL is equivalent to STRICT_TRANS_TABLES, STRICT_ALL_TABLES, NO_AUTO_CREATE_USER, and NO_ENGINE_SUBSTITUTION. The NO_ZERO_IN_DATE, NO_ZERO_DATE, and ERROR_FOR_DIVISION_BY_ZERO modes are not named because in those versions their effects are included in the effects of strict SQL mode (STRICT_ALL_TABLES or STRICT_TRANS_TABLES). Thus, the effects of TRADITIONAL are the same in all MySQL 5.7 versions (and the same as in MySQL 5.6). For additional discussion, see SQL Mode Changes in MySQL 5.7.

So the expansion of the mode is expected and your two modes are equivalent for MySQL 5.7.8+. The sql mode setting is actually evaluated: you can see e.g. that your final mode includes the non-default ALLOW_INVALID_DATES, as defined in your configuration file.

Ah now that is interesting! Sure enough, removing `TRADITIONAL`, rebooting mysql, and then looking at sql_mode in phpmyadmin shows: ` ALLOW_INVALID_DATES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION` , and now it lets me update the DB as expected! :) Thanks! — Andrew Newby, Oct 02 '17 at 07:33
And two people on earth smarter (someday I have to upgrade too!) :-) — BitAccesser, Oct 02 '17 at 07:40

sql_mode not updating, even though I've changed it in my.cnf

1 Answers1