Web reports unable to connect to database server - is there any way to get more detailed logs?

+1 vote
asked Mar 4, 2019 in EventSentry Web Reports by epettersson (180 points)
We're doing some testing of latest EventSentry on MySQL 8.0.15 database in preparation for a planned upgrade, however running into some issues as after setup & config assistant is run, launching web reports we are unable to proceed and simply get error "Could not create connection to database server". Creating an ODBC data source with same server/database/username/password works however so I was wondering if there is any way to get more verbose logs from EventSentry to see if we can get more info on why it's unable to connect?

2 Answers

+2 votes
answered Mar 4, 2019 by Sally (2,460 points)
selected Mar 6, 2019 by epettersson
Best answer
Did you get error 1045 by chance?  https://www.eventsentry.com/kb/365

You might need to run the database wizard again in the EventSentry console after you run through that article.  Select your database action and click "Initialize/Update Database" to run the wizard.  That should fix error 1045 if that's the error your web reports were getting.
commented Mar 5, 2019 by epettersson (180 points)
Thanks for the response! The error comes during initial setup of web reports so I am unable to test connection from inside web reports unfortunately.

That error did seem like a likely culprit though so I gave it a shot, but the actual MySQL query in the link appears to no longer be valid for MySQL8:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'identified by '[PASSWORD]'' at line 1 (1064) (ret=-1)

Interestingly, this is the same type of error I received when going through the intial config assistant wizard, where I needed to manually create the eventsentry_svc/eventsentry_web users and assign them privileges and then run the wizard, as the wizard would seemingly fail to create the users and therefore wasn't able to set permissions for them.

I've noticed that the installer also wants to install MySQL 5.1 ODBC connectors rather than latest 8.0 connectors (which in turn results in issues unless MySQL is installed with legacy authentication mode), so I'm wondering if maybe MySQL 8 is not yet fully supported?
commented Mar 5, 2019 by Sally (2,460 points)
If you don't have MySQL 8 set up to use legacy authentication, that would definitely be the problem.  Thankfully that's a setting that you can change after installation so that you don't have to reinstall your database.  It should work much better after that!

Any of the newer 8.0-compatible drivers that were tested last year were prone to crashing and performance problems, and other customers found that continuing to use the 5.1 driver provided the best performance and stability.  MySQL compatibility actually got downgraded to tier 3 (essentially the "It'll run with our product but good luck" tier) with EventSentry 3.5 due to how badly MySQL 8.0 performs by comparison, so you might want to stick with MySQL 5.x if possible or plan to eventually switch to Postgres or Microsoft SQL for a more stable experience.

Even at tier 3 compatibility level, MySQL shouldn't be getting syntax errors like that, so the support department should be able to give you an updated copy of the database wizard and/or an updated copy of the web reports to fix those issues.
commented Mar 6, 2019 by epettersson (180 points)
Yup, got some additional help from support but essentially legacy authentication was required + I needed to install web reports version build 33090 in order for it work with MySQL 8.

There were also some manual MySQL queries required due to database wizard trying to run some queries that won't work on MySQL8. If I understood correctly a fix for this is underway but in the meantime should anyone else encounter same issue the required queries prior to running database wizard were as follows:

CREATE DATABASE eventsentry;
CREATE USER 'eventsentry_svc'@'%' IDENTIFIED BY 'password';
CREATE USER 'eventsentry_web'@'%' IDENTIFIED BY 'password';
GRANT USAGE ON *.* TO 'eventsentry_svc'@'%';
GRANT USAGE ON *.* TO 'eventsentry_web'@'%';

Anyway thanks a lot for your assistance!
+2 votes
answered Mar 4, 2019 by Sally (2,460 points)
You didn't get any error messages from testing the database connection under Settings > Profiles > Test Connection?  Usually whatever error it shows for the connection test is the same error that you would find when digging around in the log files.

If you still wanted to dig around in the log files, when looking in the bottom right corner of the web reports pages it will say (yourname | Preferences | Support | Logout)

You can click "Support" there and either view or download the log files on the page it takes you to.  The topmost log file is the newest one and should contain any connection errors.
Welcome to EventSentry Q&A, where you can ask questions and receive answers from other members of the community.