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!

[jerrypaulick]

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!

Thanks, this seemed to help.
- I've traced the issue back to the day where I moved my whole ZM instance from physical host onto brand new VM, while re-importing the database from a instance that I was using for years. Where did the debian default user came from, I have no idea. What is funny that the ZM seemed to have been recording and creating events, while in fact it was not. Which was only noticed after zero-sized backups. Which were noticed after significant drop on space used. Which was noticed by total accident.

Here are my notes. Obviously use at you own risk and create VM snapshot/backup somewhere else than /tmp; adjust accordingly with you db name and user.

Export db
mysqldump -u zmuser -p zm > /tmp/zmdump.sql

Verify the issue
cat /tmp/zmdump.sql | grep debian-sys-maint

Code: Select all

root@zoneminder:~# cat /tmp/zmdump.sql | grep debian-sys-maint
/*!50003 CREATE*/ /*!50017 DEFINER=`debian-sys-maint`@`localhost`*/ /*!50003 TRIGGER event_insert_trigger AFTER INSERT ON Events
/*!50003 CREATE*/ /*!50017 DEFINER=`debian-sys-maint`@`localhost`*/ /*!50003 TRIGGER event_update_trigger AFTER UPDATE ON Events
/*!50003 CREATE*/ /*!50017 DEFINER=`debian-sys-maint`@`localhost`*/ /*!50003 TRIGGER event_delete_trigger BEFORE DELETE ON Events
/*!50003 CREATE*/ /*!50017 DEFINER=`debian-sys-maint`@`localhost`*/ /*!50003 TRIGGER Events_Day_update_trigger AFTER UPDATE ON Events_Day
/*!50003 CREATE*/ /*!50017 DEFINER=`debian-sys-maint`@`localhost`*/ /*!50003 TRIGGER Events_Day_delete_trigger BEFORE DELETE ON Events_Day
/*!50003 CREATE*/ /*!50017 DEFINER=`debian-sys-maint`@`localhost`*/ /*!50003 TRIGGER Events_Hour_update_trigger AFTER UPDATE ON Events_Hour
/*!50003 CREATE*/ /*!50017 DEFINER=`debian-sys-maint`@`localhost`*/ /*!50003 TRIGGER Events_Hour_delete_trigger BEFORE DELETE ON Events_Hour
/*!50003 CREATE*/ /*!50017 DEFINER=`debian-sys-maint`@`localhost`*/ /*!50003 TRIGGER Events_Month_update_trigger AFTER UPDATE ON Events_Month
/*!50003 CREATE*/ /*!50017 DEFINER=`debian-sys-maint`@`localhost`*/ /*!50003 TRIGGER Events_Month_delete_trigger BEFORE DELETE ON Events_Month
/*!50003 CREATE*/ /*!50017 DEFINER=`debian-sys-maint`@`localhost`*/ /*!50003 TRIGGER Events_Week_update_trigger AFTER UPDATE ON Events_Week
/*!50003 CREATE*/ /*!50017 DEFINER=`debian-sys-maint`@`localhost`*/ /*!50003 TRIGGER Events_Week_delete_trigger BEFORE DELETE ON Events_Week
/*!50003 CREATE*/ /*!50017 DEFINER=`debian-sys-maint`@`localhost`*/ /*!50003 TRIGGER Zone_Insert_Trigger AFTER INSERT ON Zones
/*!50003 CREATE*/ /*!50017 DEFINER=`debian-sys-maint`@`localhost`*/ /*!50003 TRIGGER Zone_Delete_Trigger AFTER DELETE ON Zones

cat /tmp/zmdump.sql | grep zmuser

Do in-place replace of debian-sys-maint with zmuser using sed
sed -i 's/debian-sys-maint/zmuser/g' /tmp/zmdump.sql

Verify if fixed
cat /tmp/zmdump.sql | grep debian-sys-maint
cat /tmp/zmdump.sql | grep zmuser

Drop old db and create new one, NOTE: if the user exists already, it will fail.
first open db console via mariadb command
DROP DATABASE zm;
CREATE DATABASE zm;
CREATE USER zmuser@localhost IDENTIFIED BY 'zmpass';
GRANT ALL ON zm.* TO zmuser@localhost;
FLUSH PRIVILEGES;
exit;

Put fixed database back
mysql -u zmuser -p zm < /tmp/zmdump.sql

Clean up after you are sure you don't need the export anymore, otherwise /tmp gets wiped with each reboot
rm /tmp/zmdump.sql