ZoneMinder Forums

Posted: **Sun Apr 10, 2016 12:07 pm**

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.

Posted: **Sun Apr 10, 2016 12:46 pm**

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

Posted: **Thu May 12, 2016 11:43 am**

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

Posted: **Fri Jun 03, 2016 11:03 pm**

Thank you, it worked.

Posted: **Sun Jun 05, 2016 2:35 pm**

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

Posted: **Fri Mar 24, 2017 2:41 pm**

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.

Posted: **Fri Mar 24, 2017 8:25 pm**

This is what is in the WIKI instructions:

In the [mysqld] section add the following

sql_mode = NO_ENGINE_SUBSTITUTION

ZoneMinder Forums

Ubuntu 16.04 and MySQL 5.7 - can't access events

Ubuntu 16.04 and MySQL 5.7 - can't access events

Re: Ubuntu 16.04 and MySQL 5.7 - can't access events

Re: Ubuntu 16.04 and MySQL 5.7 - can't access events

Re: Ubuntu 16.04 and MySQL 5.7 - can't access events

Re: Ubuntu 16.04 and MySQL 5.7 - can't access events

Re: Ubuntu 16.04 and MySQL 5.7 - can't access events

Re: Ubuntu 16.04 and MySQL 5.7 - can't access events