Opened 3 years ago

Closed 3 years ago

#12871 closed Bug Report - General (Fixed)

optimize_mythdb.pl failing

Reported by: jasonh@… Owned by: sphery
Priority: minor Milestone: 0.28.2
Component: MythTV - General Version: Master Head
Severity: medium Keywords:
Cc: Ticket locked: no

Description

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

Attachments (1)

Perl_bindings-set_sql_mode.patch (545 bytes) - added by sphery 3 years ago.
Set sql_mode for Perl bindings.

Download all attachments as: .zip

Change History (13)

comment:1 Changed 3 years ago by sphery

Resolution: Feature request without patch
Status: newclosed

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.

comment:2 Changed 3 years ago by allanhblair@…

OK so how do you go about actually doing that?

Changed 3 years ago by sphery

Set sql_mode for Perl bindings.

comment:3 Changed 3 years ago by sphery

Resolution: Feature request without patch
Status: closednew

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.

comment:4 Changed 3 years ago by sphery

Status: newinfoneeded_new

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

comment:5 Changed 3 years ago by 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)!

comment:6 Changed 3 years ago by 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.

comment:7 Changed 3 years ago by Stuart Auchterlonie

Owner: set to sphery
Status: infoneeded_newassigned

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

comment:8 Changed 3 years ago by Stuart Auchterlonie

Milestone: 0.28.10.28.2

Moving remaining open 0.28.1 tickets to 0.28.2

comment:9 Changed 3 years ago by 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

comment:10 Changed 3 years ago by 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

comment:11 Changed 3 years ago by 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)

comment:12 Changed 3 years ago by Karl Egly

Resolution: Fixed
Status: assignedclosed

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

Note: See TracTickets for help on using tickets.