Error encountered during MySQL operation

[The PIT]

I thought I'd start a new topic since there's been a few changes since my last update.

I'm now running the latest build of CumulusMX files copied across from the update files a suggested by several users.
.net 4.8 is installed without any errors.
I'm also now running the latest version of mysql.

I'm using mysql to generate some images so it needs to be on. Since the latest update I get the following error Error encountered during MySQL operation = The Command Timeout expired before the operation completed.

Apart from that everything seems to be fine. There are no errors logged under mysql as far as I can see.

Stopping the image generation doesn't have any effect the error is still there.

I've enabled debugging and attached the mxdiags file and also the logs from mysql.

[freddie]

Is there some other software locking your Realtime database table? CumulusMX is waiting for the lock to be released on the table (waiting on average 40 seconds).

[The PIT]

Nope even if I reboot and just leave it idling to itself it still does the same despite there being no image creation being processed.
It may not have been logged under the old version but never had a timeout under that.

[freddie]

Did CumulusMX create your database table? Could be there's something wrong with the DDL otherwise...

[The PIT]

Yup it did a long time ago though.

[mcrossley]

If you upgraded from a release prior to 3.7.0 - b3089, then you will need to update your MySQL tables using the script in the /MXutils folder

[The PIT]

No I haven't upgraded from a release prior to 3.7.0 I was just a few releases behind.

I went from build 3149 to 3160

[The PIT]

Just a quick update I've checked the realtime table for errors and is come back clean and it is updating as well.

[The PIT]

Checked the logs this morning and the mxdiags was full off timeout errors.

So I turned graph recreation off to double check that this wasn't the problem and the log continued to fill up.

Restarted everything and left graph creation off and the errors carried on.

Just did a full virus scan just in case and that came back clean.

I stopped cumulus and mysql.

Restarted mysql and then copied the backup of the cumulsmx I made before the update.

Restarted cumulsmx

No errors logged.

Looking at the workbench where there were 11 connections before now there's only five.
The buffer pool never gets above 50% before it was getting to 97%

So it looks like a bug in the new mysql that has caused the issue somehow by duplicating the number of users for reasons unknown.

[mcrossley]

The fact that you are getting timeout errors is odd. That would seem to mean that MX is sending the connection request, but not getting any sort of response back.

You would expect an immediate error back if the login fails, or the table fails to update because the structure is wrong.

Doubly odd because you say the real time table *is* updating, so that connection is logging in OK - and both connections use exactly the same connection details.

[mcrossley]

Actually - are you still getting those very slow responses to the real time table deletes?

It may be that your MySQL is just running so slowly that it is too busy doing the real time insert/delete and that the request to connect/insert into the monthly table is timing out before it gets a look in.

Do you have high CPU utilisation, or hit I/O limits on your server.

But I think you also said that you upgraded the version of MySQL - has that worked properly, do any of the data schemas need updating to go with that upgrade?

[The PIT]

No errors what so ever now in the logs.

CPU is idling along at 10%. 6.2 gig of 16 gig ram in use. CPU under latest version of MX was a lot higher about 60 to 70% and running at full pelt. Right now it's at 1ghz.

Hardly any disk activity where before the disk activity was pretty high.

Mysql reckons it's running at 95% efficiency and buffers still at 50% where before it was going up to 97%.

The upgrade went fine and I checked the realtime database for errors and it came back clean.

The clue seems to be in regards to the extra connections which is now just five while with the new MX it's 11 which suggesting it's duplicating the connections for some reason.

[mcrossley]

The connection mechanism remains the same as before. I suspect the "extra" connections, are just the previous connections that have yet to time-out on the server because the connection actually failed and so CMX could not log it off.

I really think the issue is in your MySQL install - 37+ seconds to delete some records from the real time table - that should be taking a few milliseconds on a machine like that.

[The PIT]

Doubtful as it's now working happily on the older version. The extra connections appeared straight away on startup so no previous connections were active.It would be interesting to try later versions of the connector too see what happens.

I did think about a new database as the realtime is rather large by now. However the question really is where and how are these extra connections are forming. I could understand it if we started with five then went to six and then went to seven but it doesn't. Instant extra connections.

[mcrossley]

The latest version only makes the same number of connections as previous versions - normally one made/dropped every real-time, and another made/dropped per archive interval.

Do you have encryption enabled on your MySQL connections? There was a change made to TLS support on the latest release, it now supports TLS 1.3, but it has to be available and enabled in the host OS.