@johannes opened this Issue on March 29th 2016

Piwik sets a deprecated SQL Mode for MySQL:

$ grep -rn SQL_MODE . 2>/dev/null
./core/Db/Adapter/Mysqli.php:61:        $this->_connection->query('SET sql_mode = "' . Db::SQL_MODE . '"');
./core/Db/Adapter/Pdo/Mysql.php:79:        $this->_connection->exec('SET sql_mode = "' . Db::SQL_MODE . '"');
./core/Db.php:36:    const SQL_MODE = 'NO_AUTO_VALUE_ON_ZERO';

mysql> SET sql_mode = "NO_AUTO_VALUE_ON_ZERO";
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                        |
+---------+------+------------------------------------------------------------------------------------------------+
| Warning | 3090 | Changing sql mode 'NO_AUTO_CREATE_USER' is deprecated. It will be removed in a future release. |
+---------+------+------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

The way this is set this also disables other default values. In 5.7 the default is ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

@tsteur commented on March 29th 2016 Owner

I presume this would mean we need to set NO_AUTO_CREATE_USER in case it is a supported mode as we currently support 4.1+ and this mode was introduced in 5.7 from my understanding. Maybe we better let users customize the SQL mode in config? This could introduce some regressions eg when a user configures ONLY_FULL_GROUP_BY but we could remove some modes from the configured list where we know Piwik is not compatible with (eg ONLY_FULL_GROUP_BY).

Also to be considered that this mode will be removed again at some point. From http://dev.mysql.com/doc/refman/5.7/en/sql-mode.html

NO_AUTO_CREATE_USER will be removed in a future MySQL release, at which point its effect will be enabled at all times (GRANT will not create accounts).

Grüße :)

@johannes commented on March 29th 2016

Actually I haven't looked deeper into this - was looking at other errors on the server and noticed the piwik related notices :)

I don't think the user should configure this. This will likely break the application (i.e. ANSI_QUOTES, IGNORE_SPACE, NO_BACKSLASH_ESCAPES can easily break SQL working on some machines in subtle ways) Setting things like ONLY_FULL_GROUP_BY is useful to ensure that you select daa you actually want to retrieve. Without full group by it is possible to retrieve "random" data.

Do you really have a use case to support MySQL 4.1? - 4.1 is out of support in upstream since end of 2008. Even 5.1 is ten years old.

@mattab commented on March 31st 2016 Owner

Thanks for the report. In this case, since the MySQL setting will be removed later, I suggest we ignore this warning for now.

Do you really have a use case to support MySQL 4.1? - 4.1 is out of support in upstream since end of 2008. Even 5.1 is ten years old.

We don't anymore: next major version of Piwik will require MySQL 5.5: https://github.com/piwik/piwik/issues/9107

This Issue was closed on March 31st 2016
Powered by GitHub Issue Mirror