The user specified as a definer ('debian-sys-maint'@'localhost') does not exist

[craver84]

Hi,
I've just installed Debian 9.9.0 with zoneminder 1.32.3 and I imported my old DB backup (it came from Ubuntu 18.04)

Code: Select all

mysql  --user=root --password=[password] zm      < ZMbackup.SQL

but I have problems with the user that zm use to connect to the db

Code: Select all

ZM LOG:
TRIGGER command denied to user 'debian-sys-maint'@'localhost' for table 'Events_Month'
The user specified as a definer ('debian-sys-maint'@'localhost') does not exist

I tried to add a new user to mysql with the same root password, but it doesn't work...

Code: Select all

MariaDB [mysql]> select User,Password from mysql.user;
+------------------+-------------------------------------------+
| User             | Password                                  |
+------------------+-------------------------------------------+
| root             |                                           |
| zmuser           | *C1D2D6FC5C596AFB19FFC4331DF6DAA287749A3E |
| debian-sys-maint | *B332BC835CE65A37167E623E25F1F660A850C7BE |
+------------------+-------------------------------------------+

how can I solve it?
may I use zmuser to connect to the db?
many thanks
Paolo

[AfricanSwallow]

is there a reason your zoneminder is using debian-sys-maint user instead of zmuser? Does the user/pass configured in /etc/zm/zm.conf allow you to login using mysql client manually from command line?

mysql -u zmuser -pzmpass
or
mysql -u debian-sys-maint -pzmpass

are you using debian-sys-maint to perform backups? if so, zoneminder should not be using that user necessarily, but just give that user read permissions to the zm database so it can perform those backups. You can also change the password for that user if you do wish to continue using it for zoneminder, but that may break functionality elsewhere if you are using that same mysql user for other tasks on the system.

I'd say either use the existing zmuser/zmpass or create a new one specifically for zoneminder and restart:
CREATE USER 'zmuser'@'localhost' IDENTIFIED BY 'yourZMPass';
GRANT ALL PRIVILEGES ON zm.* TO 'zmuser'@'localhost';

Then edit your /etc/zm/zm.conf file and update accordingly. NOTE! You will also need to modify for your api configuration as i believe that is only configured during installation and if the msyql user is changed after that, api will break. I believe the file is /usr/share/zoneminder/www/api/app/Config/database.php and you'll want to modify this entry:
public $default = array(
'datasource' => 'Database/Mysql',
'persistent' => false,
'login' => ZM_DB_USER,
'password' => ZM_DB_PASS,
'database' => ZM_DB_NAME,
//'ssl_ca' => ZM_DB_SSL_CA_CERT,
//'ssl_key' => ZM_DB_SSL_CLIENT_KEY,
//'ssl_cert' => ZM_DB_SSL_CLIENT_CERT,
'prefix' => '',
'encoding' => 'utf8',

Restart zoneminder afterwards.

Hope that helps,
AfricanSwallow

[nixcamic]

Sorry to zombie this but I'm having the exact same problem. Where is zmstats.pl pulling the debian-sys-maint user from?

[nixcamic]

So, it seems zoneminder was pulling debian-sys-maint@localhost from the definer for the triggers under zones and events in the SQL database. I changed it to zmuser@localhost and now it's working fine.

For whoever next has this issue, hopefully you don't have to grep a string on your whole hard drive to find out where the heck ZM is getting it from

[zxarr]

So, it seems zoneminder was pulling debian-sys-maint@localhost from the definer for the triggers under zones and events in the SQL database. I changed it to zmuser@localhost and now it's working fine.

For whoever next has this issue, hopefully you don't have to grep a string on your whole hard drive to find out where the heck ZM is getting it from

Sorry, can you elaborate a little bit on this? I'm not quite sure where to find this. I too am using a remote MYSQL server and this issue has cropped up on a new install.

Not sure where to find 'the definer for the triggers under zones and events in the SQL database'.

Thanks.

[setovi]

Too old post but maybe it will help whoever gets here through a google search.

I think he's trolling you, that's not in a file or a ZoneMinder setting. It's in the database.

You can do a mysqldump and will find lines like:

/*!50003 CREATE*/ /*!50017 DEFINER=`debian-sys-maint`@`localhost`*/ /*!50003 TRIGGER `event_insert_trigger` AFTER INSERT ON `Events` FOR EACH ROW BEGIN


I solved it by substituting that user with the right zmuser for my environment, dropping the database, and restoring the mysqldump backup.

[lightguy48]

So I'm having a similar issue too, when I try to add a new zone I get the error: The user specified as a definer ('debian-sys-maint'@'localhost') does not exist.

But I'm not following substitute the right user? Then drop and restore?

I did have a hard drive failure and had to restore my DB from a backup so I'm not understanding why it would not be using the correct user?

[lightguy48]

Fixed!! Thanks!

I dumped the db, went in and searched the dump file and found two instances of the debian-sys-maint and replaced them with zmuser

Reloaded the DB and all good, thanks!