Opened 3 years ago

Closed 33 hours ago

#12979 closed Bug Report - General (Invalid)

DB column defaults are invalid

Reported by: Stuart Auchterlonie Owned by:
Priority: minor Milestone: unknown
Component: MythTV - General Version: Master Head
Severity: medium Keywords:
Cc: Ticket locked: no

Description

Steps to reproduce (on master)

  1. Duplicate existing table
    mysql> create table channel2 like channel;
    ERROR 1067 (42000): Invalid default value for 'last_record'
    
  2. Review existing table defaults..
    mysql> show create table channel\G
    *************************** 1. row ***************************
           Table: channel
    Create Table: CREATE TABLE `channel` (
      `chanid` int(10) unsigned NOT NULL DEFAULT '0',
      `channum` varchar(10) NOT NULL DEFAULT '',
      `freqid` varchar(10) DEFAULT NULL,
      `sourceid` int(10) unsigned DEFAULT NULL,
      `callsign` varchar(20) NOT NULL DEFAULT '',
      `name` varchar(64) NOT NULL DEFAULT '',
      `icon` varchar(255) NOT NULL DEFAULT '',
      `finetune` int(11) DEFAULT NULL,
      `videofilters` varchar(255) NOT NULL DEFAULT '',
      `xmltvid` varchar(255) NOT NULL DEFAULT '',
      `recpriority` int(10) NOT NULL DEFAULT '0',
      `contrast` int(11) DEFAULT '32768',
      `brightness` int(11) DEFAULT '32768',
      `colour` int(11) DEFAULT '32768',
      `hue` int(11) DEFAULT '32768',
      `tvformat` varchar(10) NOT NULL DEFAULT 'Default',
      `visible` tinyint(1) NOT NULL DEFAULT '1',
      `outputfilters` varchar(255) NOT NULL DEFAULT '',
      `useonairguide` tinyint(1) DEFAULT '0',
      `mplexid` smallint(6) DEFAULT NULL,
      `serviceid` mediumint(8) unsigned DEFAULT NULL,
      `tmoffset` int(11) NOT NULL DEFAULT '0',
      `atsc_major_chan` int(10) unsigned NOT NULL DEFAULT '0',
      `atsc_minor_chan` int(10) unsigned NOT NULL DEFAULT '0',
      `last_record` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
      `default_authority` varchar(32) NOT NULL DEFAULT '',
      `commmethod` int(11) NOT NULL DEFAULT '-1',
      `iptvid` smallint(6) unsigned DEFAULT NULL,
      PRIMARY KEY (`chanid`),
      KEY `channel_src` (`channum`,`sourceid`),
      KEY `sourceid` (`sourceid`,`xmltvid`,`chanid`),
      KEY `visible` (`visible`)
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8
    1 row in set (0.00 sec)
    

Problematic column is

`last_record` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',

Change History (7)

comment:1 Changed 3 years ago by Stuart Auchterlonie

There may be other columns whose defaults for datetime columns are incorrect, however i've not reviewed the DB schema for these yet.

comment:2 Changed 3 years ago by paulh

This sounds like the same problem as all the MythWeb bugs where the default sql_mode doesn't work well with the MythTV database? You probably just need to either set your sql_mode in a temporary session like we do in Myth and now do in MythWeb and some of the bindings or do what I do since it affects several of the Mysql DB's I use and just setup Mysql to permanently use a less strict mode.

comment:3 Changed 2 years ago by Stuart Auchterlonie

Milestone: 29.029.1

comment:4 Changed 22 months ago by Stuart Auchterlonie

Milestone: 29.10.28.2

Moving remaining open tickets to 0.28.2 milestone

comment:5 Changed 22 months ago by Stuart Auchterlonie

Milestone: 0.28.229.2

Moving remaining open tickets to 29.2 milestone

comment:6 Changed 39 hours ago by Klaas de Waal

This problem is not present in the current master (Fedora 31, MariaDB); duplication of table channel does not give an error.

In the current master database schema the 'last record' column is created with:

`last_record` datetime NOT NULL,

The last update that refers to last_record is in schema update to version 1337, commit f51a20bc0da9cdce35eae67b09c7e6ccb494fffd, "Allow some record table columns to be NULL." d.d. Feb 18, 2015.

In this update the default values of "0000-00-00 00:00:00" are changed to NULL.

I suggest that this ticket can be closed.

comment:7 Changed 33 hours ago by Stuart Auchterlonie

Milestone: 29.2unknown
Resolution: Invalid
Status: newclosed
Note: See TracTickets for help on using tickets.