[SOLVED] Database update 1.32.2 -> 1.32.3 going fubar

[Nocifer]

Hi,

I'm trying to update from 1.32.2 to the just released 1.32.3 and I'm having some issues with the update process. At first zmupdate would report that "the database is already at version 1.32.2" and it would skip the update process altogether; a while later I redownloaded the upstream package and I think I noticed that its checksum had changed, so I assume there was some last minute change applied to it. In any case, now when I run the updater I get the following error message:

Code: Select all

Upgrading DB to 1.32.3 from 1.32.2
ERROR 1419 (HY000) at line 6: You do not have the SUPER privilege and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)
Output: 
Command 'mysql -hlocalhost -uzmuser -p"zmpass" zm < /usr/share/zoneminder/db/zm_update-1.32.3.sql' exited with status: 1

If I set log_bin_trust_function_creators=1, I then get the following error message:

Code: Select all

Upgrading DB to 1.32.3 from 1.32.2
ERROR 1142 (42000) at line 6: TRIGGER command denied to user 'zmuser'@'localhost' for table 'Events_Hour'
Output: 
Command 'mysql -hlocalhost -uzmuser -p"zmpass" zm < /usr/share/zoneminder/db/zm_update-1.32.3.sql' exited with status: 1

So, is this some kind of misconfiguration on my end? Should I be giving some extra privileges to zmuser? Or is it something else?

EDIT: Just to clarify, my question isn't really about how to fix this, since granting 'zmuser' all privileges on database 'zm' makes everything work correctly. This is more of a "should I be granting 'zmuser' all privileges on 'zm' database, when the Wiki clearly states that I should be only giving it select, insert, update, delete, create, alter, index and lock tables?"

EDIT 2: Alright, I think found the culprit - bad documentation. I followed the instructions for Debian, and there's this list of privileges:

Code: Select all

'grant lock tables,alter,create,select,insert,update,delete,index on zm.* to 'zmuser'@localhost identified by "zmpass";'

But in the instructions for Ubuntu, there is instead this:

Code: Select all

"grant lock tables,alter,drop,select,insert,update,delete,create,index,alter routine,create routine, trigger,execute on zm.* to 'zmuser'@localhost identified by 'zmpass';"

So I can only guess that at some point the required privileges were changed, but for some reason the documentation for Debian remained unchanged - either that, or Debian's database setup has different requirements than Ubuntu's.