Ubuntu 16.04 and MySQL 5.7 - can't access events

[kurros]

Ubuntu 16.04 recently changed the MySQL metapackages to point to 5.7. It seemed fine but I've noticed that accessing events is broken due the changes in GROUP BY behavior to apparently make it more complaint with SQL99.

These are the errors when attempting to access an event:

zms ERR Can't run query: Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'zm.M.Id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

web_php FAT SQL-ERR 'SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'zm.Events.MonitorId' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by', statement was 'select Events.Id as Id, MonitorId, Name, Cause, StartTime, date_format( StartTime, '%y/%m/%d %H:%i:%S' ) as StartTimeShort, EndTime, Width, Height, Length, Frames, AlarmFrames, TotScore, AvgScore, MaxScore, Archived, Videoed, Uploaded, Emailed, Messaged, Executed, Notes, min(Frames.FrameId) as MinFrameId, max(Frames.FrameId) as MaxFrameId, min(Frames.Delta) as MinFrameDelta, max(Frames.Delta) as MaxFrameDelta from Events left join Frames on Events.Id = Frames.EventId where Events.Id = ? group by Frames.EventId limit 1'

I looked over the preliminary 16.04 information on the Wiki and didn't see mention of this. I'm running 1.29.0+dfsg-1ubuntu1.

In the meantime I've downgraded to mysql 5.6.

[bbunge]

I've just run a fresh install with the 16.04 final beta. See the change you need to make to MySQL:

https://wiki.zoneminder.com/Ubuntu_Serv ... _with_LAMP

or use MariaDB server:

https://wiki.zoneminder.com/Install_MariaDB_Server_10.0

[Josh Bellamy]

I ran into this same problem.
Removing the mysql sql-option ONLY_FULL_GROUP_BY allowed for backwards compatibility and seems to work for me.

Code: Select all

1) login and determine what sql-modes you are using, remove only mode ONLY_FULL_GROUP_BY
mysql> SELECT concat('sql-mode=', REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY,',''));
+---------------------------------------------------------------------------------------------------------------------------------+
| concat('sql-mode=', REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY,',''))                                                               |
+---------------------------------------------------------------------------------------------------------------------------------+
| sql-mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+---------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)


2) add the output of the above sql command to the bottom to /etc/mysql/mysql.conf.d/mysqld.cnf
# zoneminder settings
sql-mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

3) restart system
# stop zm
sudo service zoneminder stop
# restart DB
sudo service mysql restart
# start zm
sudo service zoneminder start

[tehownt]

Thank you, it worked.

[bbunge]

The WIKI install instructions work quite well! Just did a fresh install of 16.04 and 1.29.0 with no issues...

https://wiki.zoneminder.com/Ubuntu_Serv ... e_easy_way

[emoseman]

I know this is several months after the fact, but there is a small error in the above sql mode string. Sql mode is set using the string 'sql_mode' and not 'sql-mode', an underscore and not a hyphen. I wanted to mention this because I set the sql mode using sql-mode and restarted mysql and it didn't throw an error, but it didn't change the setting either.

From the mysql console you can confirm by using the following:

Code: Select all

SET GLOBAL sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

This is for a fresh default install of mysql 5.7.17-0ubuntu0.16.04.1.

[bbunge]

This is what is in the WIKI instructions:

In the [mysqld] section add the following

sql_mode = NO_ENGINE_SUBSTITUTION