Modify

Opened 8 months ago

Last modified 8 months ago

#12979 new Bug Report - General

DB column defaults are invalid

Reported by: stuarta Owned by:
Priority: minor Milestone: 29.0
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',

Attachments (0)

Change History (2)

comment:1 Changed 8 months ago by stuarta

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 8 months 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.

Add Comment

Modify Ticket

Action
as new The ticket will remain with no owner.
Author


E-mail address and user name can be saved in the Preferences.

 
Note: See TracTickets for help on using tickets.