MSqlDatabase::Reconnect() should also reset session parameters set by OpenDatabase()

[Adrian Saul <sgtbundy@…>]

Since upgrading to 0.26 I have had an issue where upcoming scheduled recordings disappear. After much trouble shooting, it was tracked down to a combination of my timezone, and the schedulers use of NOW() to filter programs based on ending time.

When the scheduler starts it opens a dedicated database session, which in InitDatabase?() the mysql session.time_zone is set to UTC (+00:00). With some debugging code I was able to prove the problem to be that NOW() was returning local time instead of UTC as needed by the scheduler:

Sep 13 04:11:28 pvr mythlogserver: mythbackend[32223]: D Scheduler scheduler.cpp:2104 (HandleReschedule?) Result for NOW() is 2012-09-12T18:11:28
Sep 13 16:16:32 pvr mythlogserver: mythbackend[32223]: D Scheduler scheduler.cpp:2104 (HandleReschedule?) Result for NOW() is 2012-09-13T16:16:32

Just prior to this occurring there was a reconnect message for the Schedulers database session:

Sep 13 16:16:32 pvr mythlogserver: mythbackend[32223]: I Scheduler mythdbcon.cpp:243 (Reconnect) MySQL reconnected successfully

In Reconnect(), it closes and opens the database connection but does not re-apply the session parameters. This means should the schedulers session get timed out, the session will be re-established in local time (or the database default) and not UTC as needed by the scheduler. In my case being a GMT+10 timezone this was causing a scheduler SQL clause to fail resulting in anything ending in the next 2 hours to be dropped from the scheduled recordings.

I believe the fix is to force the session parameters to be reset when Reconnect() is called. I have attached a simple patch for this, but probably a cleaner method would be to define a SetupSession?() function which could be called by both InitDatabase?() and Reconnect() to properly setup the session should additional session parameters be needed in the future.

[Adrian Saul <sgtbundy@…>]

Suggested patch to mythdbcon.cpp to reset session properties

[David Engel <dengel@…>]

In 9b526cc800309b1791ea5ae1f5f29e8118a3d983/mythtv:

Reinitialize database session variables when reconnecting.

The session variables weren't being set when we reconnected after a
database connection went stale and led to problems. For example, the
timezone was not being set to UTC and caused serious scheduling
problems for users in far eastern time zones. Thanks to Adrian Saul
for initially spotting and then tracking down the problem.

Fixes #11086