Troubleshooting Audit on MySQL 5
Novell Cool Solutions: Tip
Digg This -
Posted: 6 Dec 2006
A Forum reader recently asked:
"I've tried setting up Audit 2.02 on MySQL 5. As far as I can tell everything is running. I can run lengine -d and I will get the Novell Nsure Audit Console 2.0 screen, which is a good thing according to what I have read. The MySQL server seems to be running OK. The wheels seem to fall off when I try to run a query using the iManager interface. I end up with an error stating the following:
Error: com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: Table 'naudit.nauditlog' doesn't exist
Unfortunately I don't really know what this means. If someone has a suggestion I'd love to hear it."
And here's the response from several other Forum readers ...
"Naudit" is the name of the database itself, as defined within MySQL. The specific Table NSure Audit writes to is called "log" by default. I've seen this error on my system when setting up some time ago. The correct format should be "naudit.log". Could it be that your Database configured within the Query options in Imanager/Auditing and Logging are wrong?
In Query Configuration you should have created a database object representing the SQL database, for the queries to use. Settings should be:
Name: [servername ]Naudit (or whatever, just a text identifier) JDBC Class: com.mysql.jdbc.Driver (case sensitive - note capital D in 'Driver') JDBC URL: jdbc:mysql://[server ip address]:3306/naudit Table: log Username: root (or another account with rights) Password: novell (or whatever)
Failing that, is your MySQL channel object setup correctly? It should be:
Host: 127.0.0.1 (should actually be exactly this, tells the defined Secure Logging Server object to use a local db) Name: naudit Table: log User: root Password: (whatever)
There is a Test Credentials button here that will confirm if the database can be connected to.
I ran into the same issue and after a few hours of troubleshooting I fixed the problem by following TID 10088726. I ran step 12 like this:
GRANT ALL on naudit.* to auditusr@localhost IDENTIFIED by 'yourpwd';
On the server console,
Unload lengine unload mysqld reload
It was OK after that.
I've set up Audit 2.0.2 w/MySQL 5 and it works wonderfully with the same connector and configuration. If you're able you may want to try the following to assist in the proper setup of your queries/filters/verification/etc.
At the server console where MySQL is running use the following commands:
mysql -u root -p [enter password] #Assuming your database is named 'naudit'... use naudit; show tables;
You should at this point see a list of tables, probably just one, in the database. Use that table and database name in your iManager configuration.
As a note after making these changes you should be able to simply run "flush privileges;" and your login information should be updated and ready to use without restarting mysql. lengine should have the correct information already so its restart shouldn't be needed either.
On another note, the '%' in step 12 of the TID means "user logging in from anywhere". Having that should take care of localhost, 127.0.0.1, or any other value in the 'Host' field in the user table in the mysql database. If you have that one separate, though, you might have multiple entries in there and that could make life interesting since MySQL treats credentials as username, password, and where you are coming from (local box, remote box, anywhere, etc.). You will probably want to look into this if you have rights issues in the future. MySQL documentation is fantastic.
Novell Cool Solutions (corporate web communities) are produced by WebWise Solutions. www.webwiseone.com