You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
johannes opened this issue
Mar 29, 2016
· 3 comments
Labels
BugFor errors / faults / flaws / inconsistencies etc.wontfixIf you can reproduce this issue, please reopen the issue or create a new one describing it.
$ 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
The text was updated successfully, but these errors were encountered:
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).
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).
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.
BugFor errors / faults / flaws / inconsistencies etc.wontfixIf you can reproduce this issue, please reopen the issue or create a new one describing it.
Piwik sets a deprecated SQL Mode for MySQL:
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
The text was updated successfully, but these errors were encountered: