optimize_mythdb.pl failing

[jasonh@…]

Running optimize_mythdb.pl on Mythbuntu 16.04 and 0.28-fixes gives the following 2 errors.

8<....

Analyzed: mythconverg.websites DBD::mysql::db do failed: Invalid default value for 'starttime' at /usr/share/doc/mythtv-backend/contrib/maintenance/optimize_mythdb.pl line 38. DBD::mysql::db do failed: Invalid default value for 'starttime' at /usr/share/doc/mythtv-backend/contrib/maintenance/optimize_mythdb.pl line 42. Defragmented: filemarkup

[sphery]

Your MySQL server is configured to use strict mode (specifically NO_ZERO_DATE and/or NO_ZERO_IN_DATE), but MythTV uses zero dates as defaults for some fields.

See: ​https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html

I'm marking as feature request without patch because adding support for running MythTV against a database server using NO_ZERO_DATE or NO_ZERO_IN_DATE mode requires additional coding--either explicitly specifying mode for each connection (as at [dd2d7a14d] ) or changing the MythTV database schema.

If you disable NO_ZERO_DATE and NO_ZERO_IN_DATE (which, for MySQL 5.7.4 through 5.7.7 requires disabling STRICT_ALL_TABLES and STRICT_TRANS_TABLES) on your MySQL server configuration, the MythTV database schema will work properly.

[allanhblair@…]

OK so how do you go about actually doing that?

[sphery]

Set sql_mode for Perl bindings.

[sphery]

Please apply the attached patch, Perl_bindings-set_sql_mode.patch​ . You can do this quickly as a test by directly editing MythTV.pm (likely at /usr/lib/perl5/site_perl/5.*/MythTV.pm ) and adding the 2 new lines in the appropriate location. This should work on any version of MythTV.pm as long as it's placed properly, though the line numbers may differ.

[sphery]

Please report whether the patch fixes the issue for you. Thanks.

[allanhblair@…]

Thank you very much Jason, that worked excellently!

The optimise routine now does not report an error and my "Fix Seek" within MythTV Frontend now works as well.

I wonder why it "broke" in the first place? An update issue perhaps. I'd be interested to learn if this is a more general update related bug. It's never been a problem before, but I update regularly, and although it is sometimes hit and miss, I haven't yet lost my database (touch wood)!

[jasonh@…]

I have been updating with the 0.28 fixes apt repository. I didn't apply the patch above but running optimize_mythdb now doesn't generate the original error I reported for this ticket and it all seems to run to completion. I'll assume this patch has been applied to the latest 0.28 fixes.

[Stuart Auchterlonie]

Interesting, this patch hasn't been applied to 0.28, so that implies the default sql mode for the database has changed again.

[Stuart Auchterlonie]

Moving remaining open 0.28.1 tickets to 0.28.2

[stanger192@…]

Hi,

I am getting the same errors with myth 0.28-fixes on a ubuntu 16.04 install. After applying mdean's patch to /usr/share/perl5/MythTV.pm I no longer get the error.

Thanks mdean

[Karl Dietz <dekarl@…>]

In 83c67a0c7b5eec295e9cad757ff6edc1438078ff/mythtv:

Set MySQL session mode in Perl bindings

this allows optimize_mythdb.pl to work against newer (5.7+)
versions of MySQL.

Patch by Michael Dean

Refs #12871

[Karl Dietz <dekarl@…>]

In 0037461751becd40aedd2d3dd8bd2ad5e0836c9f/mythtv:

Set MySQL session mode in Perl bindings

this allows optimize_mythdb.pl to work against newer (5.7+)
versions of MySQL.

Patch by Michael Dean

Refs #12871

(cherry picked from commit 83c67a0c7b5eec295e9cad757ff6edc1438078ff)

[Karl Egly]

Sphery, I ran into this running latest master on Ubuntu 16.04.2. "works for me" so I committed it.