zenactions.log says "The user specified as a definer ('root'@'%') does not exist"

Up to Discussions in zenoss-users

27061 Views 9 Replies Latest reply: Mar 8, 2010 8:55 AM by jmp242

Jason Sjöbeck

8 posts since
Jan 21, 2010

Currently Being Moderated

Feb 27, 2010 5:31 AM

Hey, hi, All,

We really dig Zenoss. Thanks to all who have made it. I have an error I need to resolve. As you can see below, something is not pointed in the correct location. This is an entirely stock install on centOS 5.4 with the only exception being that the mySQL DB is named "zenoss" on another server. That DB seems (I say seems, since I think it is) to be just fine. The zenactions complains to us & upon tailing the log, I see that it does not know who it is & where it is pointed. It would appear that it wants to use mySQL, but I didn't think that zenactions used mySQL, doesn't it use the built-in DB? I'm fine with it using mySQL, in fact, as far as I know, I think I prefer it. How does one manually configure this? I do not see (after much grepping of files) where this component configures its uid & pswd & DB & location. I must be missing something. Any & all advice is very appreciated.

Thank you, Jason Sjobeck

www.sjobeck.com

------------

[root@www glpi]# cd /opt/zenoss/log/
[root@www log]# tail -n 50 zenactions.log
    self.mainbody()
File "/opt/zenoss/Products/ZenEvents/zenactions.py", line 556, in mainbody
    self.maintenance(zem)
File "/opt/zenoss/Products/ZenEvents/zenactions.py", line 365, in maintenance
    self.execute(sql)
File "/opt/zenoss/Products/ZenEvents/zenactions.py", line 184, in execute
    result = curs.execute(stmt)
File "/opt/zenoss/lib/python/MySQLdb/cursors.py", line 137, in execute
    self.errorhandler(self, exc, value)
File "/opt/zenoss/lib/python/MySQLdb/connections.py", line 33, in defaulterrorhandler
    raise errorclass, errorvalue
OperationalError: (1449, "The user specified as a definer ('root'@'%') does not exist")
2010-02-27 02:22:37,266 INFO zen.ZenActions: Processed 0 commands in 0.000072
2010-02-27 02:22:37,267 DEBUG zen.ZenActions: call age_events(8, 3);
2010-02-27 02:22:37,267 DEBUG zen.DbConnectionPool: Retrieved a connection; Pool size: 0
2010-02-27 02:22:37,269 DEBUG zen.DbConnectionPool: Returned a connection; Pool size: 1
2010-02-27 02:22:37,270 ERROR zen.ZenActions: unexpected exception
Traceback (most recent call last):
File "/opt/zenoss/Products/ZenEvents/zenactions.py", line 565, in runCycle
    self.mainbody()
File "/opt/zenoss/Products/ZenEvents/zenactions.py", line 556, in mainbody
    self.maintenance(zem)
File "/opt/zenoss/Products/ZenEvents/zenactions.py", line 365, in maintenance
    self.execute(sql)
File "/opt/zenoss/Products/ZenEvents/zenactions.py", line 184, in execute
    result = curs.execute(stmt)
File "/opt/zenoss/lib/python/MySQLdb/cursors.py", line 137, in execute
    self.errorhandler(self, exc, value)
File "/opt/zenoss/lib/python/MySQLdb/connections.py", line 33, in defaulterrorhandler
    raise errorclass, errorvalue
OperationalError: (1449, "The user specified as a definer ('root'@'%') does not exist")
2010-02-27 02:23:37,273 INFO zen.ZenActions: Processed 0 commands in 0.000071
2010-02-27 02:23:37,274 DEBUG zen.ZenActions: call age_events(8, 3);
2010-02-27 02:23:37,274 DEBUG zen.DbConnectionPool: Retrieved a connection; Pool size: 0
2010-02-27 02:23:37,276 DEBUG zen.DbConnectionPool: Returned a connection; Pool size: 1
2010-02-27 02:23:37,277 ERROR zen.ZenActions: unexpected exception
Traceback (most recent call last):
File "/opt/zenoss/Products/ZenEvents/zenactions.py", line 565, in runCycle
    self.mainbody()
File "/opt/zenoss/Products/ZenEvents/zenactions.py", line 556, in mainbody
    self.maintenance(zem)
File "/opt/zenoss/Products/ZenEvents/zenactions.py", line 365, in maintenance
    self.execute(sql)
File "/opt/zenoss/Products/ZenEvents/zenactions.py", line 184, in execute
    result = curs.execute(stmt)
File "/opt/zenoss/lib/python/MySQLdb/cursors.py", line 137, in execute
    self.errorhandler(self, exc, value)
File "/opt/zenoss/lib/python/MySQLdb/connections.py", line 33, in defaulterrorhandler
    raise errorclass, errorvalue
OperationalError: (1449, "The user specified as a definer ('root'@'%') does not exist")

------------------

Like (0)

Tags: database, zenactions, root

phonegi
446 posts since
Apr 15, 2009

Currently Being Moderated

1. Feb 27, 2010 9:29 PM (in response to Jason Sjöbeck)
Re: zenactions.log says "The user specified as a definer ('root'@'%') does not exist"

Maybe this can help?

docs/DOC-3788

pg

Report Abuse

Like (0)
phonegi
446 posts since
Apr 15, 2009

Currently Being Moderated

2. Feb 27, 2010 9:40 PM (in response to phonegi)
Re: zenactions.log says "The user specified as a definer ('root'@'%') does not exist"

This might be good too:

docs/DOC-3875

Report Abuse

Like (0)
Jason Sjöbeck
8 posts since
Jan 21, 2010

Currently Being Moderated

3. Mar 4, 2010 7:33 PM (in response to phonegi)
Re: zenactions.log says "The user specified as a definer ('root'@'%') does not exist"

Yes, helpful, thank you.

At the same time not perfectly what it would seem that we would need. I hope I'm not being thick here. I just dont get it. I dont see where zenactions "points" to its DB. Or where to configure that. Or why it broke in the first place.

Any one out there know how to configure the inner DB connection for zen actions / zenactions?

As I wrote earlier, this is an entirely stock install except for the mySQL DB (which if I understand correctly, zenactions does not use, since it uses the ZEODB, yes?) which is on a different server.

The Events seems to be using that separat eserver for the mySQL DB just fine (unless I'm missing something).

Thanks all.

Jason Sjobeck
www.sjobeck.com

Report Abuse

Like (0)
guyverix
846 posts since
Jul 10, 2007

Currently Being Moderated

4. Mar 4, 2010 11:02 PM (in response to Jason Sjöbeck)
Re: zenactions.log says "The user specified as a definer ('root'@'%') does not exist"

What version of Zenoss are you running? Also did you change the default user to root for the database?

Looking here, perhaps this user being root is part of your problem. Usually user zenoss is creating the databases / updating etc. Perhaps you might want to change the user back to what zenoss normally uses, and then re-create the database? I dont know how much of a pain that could be for you though.. I am guessing that you are doing this for some kind of HA system, but it really looks like you are going to have to define the database and user the way zenoss intended if you want things to start running the way you expect.... (just guessing looking at the info on mysql.com)

http://majid.mmxgroup.net/?p=64

It looks like others have had issues with not explicitly defining who the definer is??

http://bugs.mysql.com/bug.php?id=44331

Report Abuse

Like (0)
phonegi
446 posts since
Apr 15, 2009

Currently Being Moderated

5. Mar 4, 2010 11:29 PM (in response to Jason Sjöbeck)
Re: zenactions.log says "The user specified as a definer ('root'@'%') does not exist"

Did you actually read the links I posted earlier? What you described is that your trying to run your mysql server on another machine. This walks you through the setup.

docs/DOC-3866

Report Abuse

Like (0)
guyverix
846 posts since
Jul 10, 2007

Currently Being Moderated

6. Mar 4, 2010 11:44 PM (in response to phonegi)
Re: zenactions.log says "The user specified as a definer ('root'@'%') does not exist"

phonegi wrote:

Did you actually read the links I posted earlier? What you described is that your trying to run your mysql server on another machine. This walks you through the setup.

docs/DOC-3866
I think he managed to create more complex problems than what that particular doc is covering. It looks like he might have the config correct, but mysql itself was not set up in a zenoss fashion for the events database and is spitting errors on certain events/updates/inserts that zenactions is dealing with.

Report Abuse

Like (0)
Jason Sjöbeck
8 posts since
Jan 21, 2010

Currently Being Moderated

7. Mar 7, 2010 2:49 AM (in response to guyverix)
Re: zenactions.log says "The user specified as a definer ('root'@'%') does not exist"

Hey, Hi, All Zenoss Peeps,

So, just to close this thread, here is what we learned. When you create the Zenoss DB on a separate DB server & then point the Zenoss app' over at that DB server, you must create the DB, as shown here (jsut for reference sake only):

docs/DOC-3866

... in a very specific way. When you create that DB as root (which we would usually have no other choice to do, right) then it creates the stored procedure & the trigger as that user. OK, if you allow the mySQL root user (not the OS root user) to connect to that DB server from the world, maybe that would work, but, if you are sane, and you do not allow root to connect from any where (ie: in our case, and as ought to be the case) root is only allowed to connect from the localhost, well then, the SP & Trigger will not be allowed to be called & run. So, they fail, and the zenactions.log shows this failing over & over again forever. If you painstakingly go back & edit the SP & Trigger to run as the "zenoss" DB user & specifically allow those 2 objects to be run from the host that they are being called from, then they succeed.

So, there.

I'm sure that every single person who has ever, or ever will, run Zenoss on one box & mySQL on another box, needs to know this, and it is shocking to me that no one else has come across this before now. Well, sorry, I mean posted in these forums these notes & steps taken & required. This means one of two things: everyone is running another mySQL on the same host as Zenoss is running & they are running that as root. Bad. Not best practices. Or if they are running these two items on separate servers, like we are, they are running as root. Bad. Not even close to best practices.

I hope this saga of mine helps others get past this issue for years to come. (if someone wants to drop this in to the manual, please be our guest, please do, thanks)

Cheers.

Jason Sjobeck
www.sjobeck.com
Sjobeck Integration Professionals

Report Abuse

Like (0)
guyverix
846 posts since
Jul 10, 2007

Currently Being Moderated

8. Mar 7, 2010 6:04 AM (in response to Jason Sjöbeck)
Re: zenactions.log says "The user specified as a definer ('root'@'%') does not exist"

Just for a bit of clarification, since you had root privs on the remote mysql instance, with what you discovered couldn't you have made a zenoss database user and granted it proper privs and had it work?

Report Abuse

Like (0)
jmp242
4,060 posts since
Mar 7, 2007

Currently Being Moderated

9. Mar 8, 2010 8:55 AM (in response to Jason Sjöbeck)
Re: zenactions.log says "The user specified as a definer ('root'@'%') does not exist"

Thanks, I've posted this in the Community FAQ part 2.
--
James Pulver
Information Technology Area Supervisor
LEPP Computer Group
Cornell University

Jason Sjöbeck wrote, On 3/7/2010 2:49 AM:
Hey, Hi, All Zenoss Peeps,

So, just to close this thread, here is what we learned. When you create the Zenoss DB on a separate DB server & then point the Zenoss app' over at that DB server, you must create the DB, as shown here (jsut for reference sake only):

docs/DOC-3866

... in a very specific way. When you create that DB as root (which we would usually have no other choice to do, right) then it creates the stored procedure & the trigger as that user. OK, if you allow the mySQL root user (not the OS root user) to connect to that DB server from the world, maybe that would work, but, if you are sane, and you do not allow root to connect from any where (ie: in our case, and as ought to be the case) root is only allowed to connect from the localhost, well then, the SP & Trigger will not be allowed to be called & run. So, they fail, and the zenactions.log shows this failing over & over again forever. If you painstakingly go back & edit the SP & Trigger to run as the "zenoss" DB user & specifically allow those 2 objects to be run from the host that they are being called from, then they succeed.

So, there.

I'm sure that every single person who has ever, or ever will, run Zenoss on one box & mySQL on another box, needs to know this, and it is shocking to me that no one else has come across this before now. Well, sorry, I mean posted in these forums these notes & steps taken & required. This means one of two things: everyone is running another mySQL on the same host as Zenoss is running & they are running that as root. Bad. Not best practices. Or if they are running these two items on separate servers, like we are, they are running as root. Bad. Not even close to best practices.

I hope this saga of mine helps others get past this issue for years to come. (if someone wants to drop this in to the manual, please be our guest, please do, thanks)

Cheers.

Jason Sjobeck
www.sjobeck.com
Sjobeck Integration Professionals
>

Report Abuse

Like (0)