Database Operational Error: MySQL server has gone away

[Josiah]

For the last few weeks I am constantly getting the error "wxengine: Caught database OperationalError: (2006, 'MySQL server has gone away')". I am uploading weewx data to a remote mysql server. Some research online suggests I should change some parameters of the mysql database however I'm not sure I'll be able to do that. Is there something in weewx I can do to fix this or have it retry once it receives this error?

I'm using a raspberry pi and the latest version of weewx with a vantage pro2.

Thanks.

[Thomas Keffer]

Right now, this is an unfortunate limitation in the MySQL implementation. It does not do any retries. So, it's possible to crash weewx if you're accessing MySQL over a flaky network connection.

It's something I've been thinking about for V3. Like most things, it's turning out not to be as simple as a retry: if the data cannot be stored, then none of the reporting can be done either. 

-tk

--
You received this message because you are subscribed to the Google Groups "Weewx user's group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to weewx-user+...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Paul Bartholdi]

Your error message indicates that for some reason you have no access to the mysql server from the Raspberry.pi.

To checkn: from your Raspberry (shell running on the RPi), can you access your mysql server, using the 'weewx' mysql name and pwd? Something like:

$ mysql  -u weewx  -h mysql-server  -D weewx_archive  -p

$ mysql  -u weewx  -h mysql-server  -D weewx_stats  -p

and then do some  'SELECT' (read access)  and  'UPDATE'  or  'INSERT' (write access). (to be erased after the test!).

NB: 

1. the mysql user 'weewx' must have all necessary rights from a remote host, not just from the local host (default).

2. the mysql port (usually 3306) must be open on the server.

Good luck!     Paul

[Josiah]

Thanks. I am able to connect to the server just fine via terminal. Everything has been working fine for the last 6+ months, only in the last month has this issue started.

Based on the logs weewx is reporting the error every 10 minutes. Most of the time weewx retries in two minutes and doesn't have a problem. Frequently though it causes weewx to crash. It seems odd that it is is happening every 10 minutes. My station archive is set to 5 minutes.

Perhaps my wifi adapter is flaking out. I should try connecting it to a hub and also test out ethernet to see if it improves. But for now I setup Monit to monitor the weewx service and restart weewx when it crashes. So far so good.

[Thomas Keffer]

​The behaviour I would expect is for weewx to sleep 2 minutes, then retry from the top. But, it shouldn't crash in the sense the program exits. 

Could you please post the logs?

-tk​

--

[Josiah]

A dump from the log is attached.

There are two instances today where weewx crashed that  in the log:
Aug  3 11:14:46
Aug  3 13:30:28

I am using the latest version of weewx but this also occurred with 2.5.1 (recently upgrade). Weewx can go anywhere from minutes to days before it will crash like this.

Let me know if there is something else you need to troubleshoot.

[Thomas Keffer]

Your adapter may well be flaking out, but it should not be crashing weewx. But, I think I know why it is.

When a network connection is lost, a MySQL exception is raised. Weewx (more exactly, its database abstraction layer, weedb) normally catches this and converts it into a weewx exception. This is happening with straightforward database queries, but I forgot to catch exceptions generated during a rollback. This has been corrected in the attached. Replace file bin/weedb/mysql.db with it.

It won't fix your network problem, but at least weewx shouldn't crash.

-tk

[Andrew Milner]

Have you tried disabling the OWM service - since that seems to always get errors anyway! And also I notice that the dewpoint for OWM looks a little suspect - like a factor of 10 adrift ('dewpoint': 291.6)

I have had my RPi start to do odd things when reading from the USB when the network is in heavy use (a couple of PCs streaming video for example) and I wonder if you are seeing symptoms of the reverse - if the USB is in heavy use the network starts to show problems.  Can mySQL database be installed on the RPi as an experiment to see if things improve?

[Josiah]

Thanks Tom! The updated file seems to be working, weewx is not crashing anymore.

I only recently setup OWM. The problem exists with OWM disabled. I should not have that much network traffic, even during hours when there should be virtually no traffic on my network the issue exists and I don't have issues with my other raspberry pi's. I'll do some additional troubleshooting, but at least the data is still being uploaded and now I don't have to constantly restart weewx.

[Bram Baert]

Has this fix also been included in the official releases?

I was running the wee_database --backfill-daily and I got this exact error while being on a dedicated home network. I did a diff between the file here and my current one (version 3.3.1) and there was to much changed for me to find out what the change was.

Kind Regards

Bram

[Thomas Keffer]

These fixes were included in v2.7.0, released 10/11/14.

You're going to have to be more specific about the error you received. Did you mean you got the OperationalError and weewx crashed? Or, just that you got the OperationalError, but weewx recovered?

If the former, please include the log. If the latter, then that's the expected behavior.

-tk

--
You received this message because you are subscribed to the Google Groups "weewx-user" group.