DB column defaults are invalid

[Stuart Auchterlonie]

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',

[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.

[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.

[Stuart Auchterlonie]

Moving remaining open tickets to 0.28.2 milestone

[Stuart Auchterlonie]

Moving remaining open tickets to 29.2 milestone

[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.