Novell Home

Troubleshooting Audit on MySQL 5

Novell Cool Solutions: Tip

Digg This - Slashdot This

Posted: 6 Dec 2006
 

Problem

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 ...

Solution

(Steve Law)

"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.

(GST Amour)

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.

(Aaron Burgemeister)

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

© 2014 Novell