MySQL server has gone away

[Louis De Lange]

While there seem to be great excitement for the new meteotemplate uploader, I previously wrote a service that updates my meteotemplate database directly without an intermediate php script.  In the service i use a ."getSql("INSERT INTO" statement.  This works really well and I even resolved the dayrain issue that is now being discussed on another thread.

There is one annoying bug and I dont know how to solve it.  My meteotemplate install is located on a different server than weewx.  It can sometimes happen that the meteotemplate server is temporarily unavailable.  To overcome this scenario I use a "try" statement when inserting SQL data, so that it will just skip the upload attempt if the MySQL server is not available.  The bug is that the mysql insert command continue to fail after the meteotemplate server becomes available again, and the only way to correct it is to restart weewx. 

The fail message is "Error (2006, 'MySQL server has gone away')" and it continues to be displayed after the meteotemplate server is back on line.

Any ideas how to resolve this will be appreciated.

Louis

[Craig Thom]

This is not an answer to your question, but, if you have root access to the database on the remote server, you can create one local to weewx and set up replication, with the weewx machine db the master and the remote one the slave.

Then you would just update the local one.  The remote db will automatically keep itself up to date.  If it is offline for a while, it will catch up when it gets back.

[Thomas Keffer]

Most likely, the connection to the MySQL server needs to be reestablished as well.

When weewx gets a MySQL server error, it restarts from the top, which effectively causes the connection to be re-established. If you raise a WeeWxIOError (or subclass) instead of ignoring the error, it should do this.

-tk

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

[Paul Bartholdi]

Hello,  I faced the same problem once or twice in the past, even with both weewx and mysql on the same host. In all cases I could point to the culprit on me.  I had updated mysql or restarted altogether the host just at the time weewx wanted to insert new records. In both cases I had to stop and restart weewx, with no data lost.
Paul

[Louis De Lange]

Tom,

I tried you recommendation, but it did not quite have the result that I had in mind.

Rasing a WeeWxIOError shut down the engine, which retries again and starts up again if the MySQL server is back online.  The problem is that it no longer updates the local database either.

If there is an exception to be raised that would restart the connection to the database without shutting down the engine, it would be my preference.

To unsubscribe from this group and stop receiving emails from it, send an email to weewx-user+...@googlegroups.com.

[Andrew Milner]

what does the log show during the period a)loss of Mysql, b) weewx restart from top, c) reacquire MySQL external db, d) a couple of archive intervals.

how (what order) are the services listed in weewx.conf?

[Thomas Keffer]

Rasing a WeeWxIOError shut down the engine, which retries again and starts up again if the MySQL server is back online.  The problem is that it no longer updates the local database either.

If there is an exception to be raised that would restart the connection to the database without shutting down the engine, it would be my preference.

​That would require a rewrite of the MySQL driver. It also introduces a problem: if the retries are tried in the driver, it will cause weewx to block in the driver until the database server comes back online. So, the retries would have to be limited to 60 seconds or so at the most.

Let me think about this.

Posted as issue #259.

-tk

​

[Louis De Lange]

Tom, 

I can monitor for the  "(2006, 'MySQL server has gone away')" error with the try statement.  Perhaps I can use that to restart the connection to the mysql server, as long as I know the command to use?  This way you dont have to change the driver.  

[Thomas Keffer]

I've been thinking along these lines as well. It's better to do the retry higher up in the stack, where there is some context. Perhaps even in the class Manager. 

-tk

--

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

To unsubscribe from this group and stop receiving emails from it, send an email to weewx-user+unsubscribe@googlegroups.com.

[Tom Van Dyck]

If someone has this 2006, MySQL server has gone away error after the second archive update (so after 5 minutes), and their first insert after connecting works. Or maybe if they have a bit of a flaky connection to their remote server:

for me to solve this I had to set this session variable after connecting (after setting the isolation level thing in the mysql.py):

        self.connection.query("set session wait_timeout=3600")

It turned out that my webhost had this on global setting set to 60 seconds (check it first), which is too low given my 5 minute archive interval. I would set it to at least 360 seconds.
Maybe other users out there with the same problem can try and see if it helps. It is a better solution than re-establishing the connection each time in this case.

Tom

Op woensdag 31 mei 2017 04:22:16 UTC+2 schreef Louis De Lange: