Opened 13 years ago

Closed 13 years ago

Last modified 13 years ago

#3725 closed patch (wontfix)

DB Warning from "REPLACE INTO recordedprogram" in ProgramInfo::StartedRecording

Reported by: anonymous Owned by: danielk
Priority: minor Milestone: unknown
Component: mythtv Version: 0.20-fixes
Severity: medium Keywords:
Cc: Ticket locked: no

Description

When i was looking in the recordedprogram table i noticed that i had years in the category_type field instead of text like series.

i tracked this to the "REPLACE INTO recordedprogram" query in ProgramInfo::StartedRecording?
For some reason mysql messes up the fields when using * to specify all the fields. Because this only generates a database warning the backed doesn't log anything about the problem.

Executing the same query manually and turning on warnings results in this:

mysql> warnings;
Show warnings enabled.
mysql> REPLACE INTO recordedprogram
    -> SELECT *
    -> FROM program
    -> where chanid=3130 and starttime = '2007-07-10 15:30:00';
Query OK, 2 rows affected, 1 warning (0.00 sec)
Records: 1  Duplicates: 1  Warnings: 0

Warning (Code 1366): Incorrect integer value: 'series' for column 'previouslyshown' at row 1

I don't understand why this sql query doesn't do what it's supposed to.
it looks as if mysql doesn't match the field names in the select query with the replace query properly.

I've attached a small patch against 0.20-fixes that instead of using * specifies all fields for the query. After this change recordings get inserted properly.

Attachments (1)

recordedprograms-db-fix.patch (1.5 KB) - added by torbjorn.jansson@… 13 years ago.
the patch

Download all attachments as: .zip

Change History (6)

Changed 13 years ago by torbjorn.jansson@…

the patch

comment:1 Changed 13 years ago by torbjorn.jansson@…

looks like theres a bug in trac, my email address was replaced by anonymous for some reason.

comment:2 Changed 13 years ago by danielk

Owner: changed from Isaac Richards to danielk

Here's what I get with svn-head (I'll try with fixes later)

mysql> warnings;
Show warnings enabled.
mysql> REPLACE INTO recordedprogram
    -> SELECT *
    -> FROM program
    -> where chanid = 1021 AND starttime = '2007-07-14 10:00:00';
Query OK, 1 row affected (0.03 sec)
Records: 1  Duplicates: 0  Warnings: 0

Here are my tables, I suspect your recordedprogram and program tables may be further out of sync.

mysql> describe recordedprogram
    -> ;
+-------------------------+------------------+------+-----+---------------------+-------+
| Field                   | Type             | Null | Key | Default             | Extra |
+-------------------------+------------------+------+-----+---------------------+-------+
| chanid                  | int(10) unsigned | NO   | PRI | 0                   |       |
| starttime               | datetime         | NO   | PRI | 0000-00-00 00:00:00 |       |
| endtime                 | datetime         | NO   | MUL | 0000-00-00 00:00:00 |       |
| title                   | varchar(128)     | NO   | MUL |                     |       |
| subtitle                | varchar(128)     | NO   |     |                     |       |
| description             | text             | NO   |     |                     |       |
| category                | varchar(64)      | NO   |     |                     |       |
| category_type           | varchar(64)      | NO   |     |                     |       |
| airdate                 | year(4)          | NO   |     | 0000                |       |
| stars                   | float unsigned   | NO   |     | 0                   |       |
| previouslyshown         | tinyint(4)       | NO   |     | 0                   |       |
| title_pronounce         | varchar(128)     | NO   | MUL |                     |       |
| stereo                  | tinyint(1)       | NO   |     | 0                   |       |
| subtitled               | tinyint(1)       | NO   |     | 0                   |       |
| hdtv                    | tinyint(1)       | NO   |     | 0                   |       |
| closecaptioned          | tinyint(1)       | NO   |     | 0                   |       |
| partnumber              | int(11)          | NO   |     | 0                   |       |
| parttotal               | int(11)          | NO   |     | 0                   |       |
| seriesid                | varchar(40)      | NO   | MUL |                     |       |
| originalairdate         | date             | YES  |     | NULL                |       |
| showtype                | varchar(30)      | NO   |     |                     |       |
| colorcode               | varchar(20)      | NO   |     |                     |       |
| syndicatedepisodenumber | varchar(20)      | NO   |     |                     |       |
| programid               | varchar(40)      | NO   | MUL |                     |       |
| manualid                | int(10) unsigned | NO   | PRI | 0                   |       |
| generic                 | tinyint(1)       | YES  |     | 0                   |       |
| listingsource           | int(11)          | NO   |     | 0                   |       |
| first                   | tinyint(1)       | NO   |     | 0                   |       |
| last                    | tinyint(1)       | NO   |     | 0                   |       |
+-------------------------+------------------+------+-----+---------------------+-------+
29 rows in set (0.04 sec)

mysql> describe program;
+-------------------------+------------------+------+-----+---------------------+-------+
| Field                   | Type             | Null | Key | Default             | Extra |
+-------------------------+------------------+------+-----+---------------------+-------+
| chanid                  | int(10) unsigned | NO   | PRI | 0                   |       |
| starttime               | datetime         | NO   | PRI | 0000-00-00 00:00:00 |       |
| endtime                 | datetime         | NO   | MUL | 0000-00-00 00:00:00 |       |
| title                   | varchar(128)     | NO   | MUL |                     |       |
| subtitle                | varchar(128)     | NO   |     |                     |       |
| description             | text             | NO   |     |                     |       |
| category                | varchar(64)      | NO   |     |                     |       |
| category_type           | varchar(64)      | NO   |     |                     |       |
| airdate                 | year(4)          | NO   |     | 0000                |       |
| stars                   | float            | NO   |     | 0                   |       |
| previouslyshown         | tinyint(4)       | NO   | MUL | 0                   |       |
| title_pronounce         | varchar(128)     | NO   | MUL |                     |       |
| stereo                  | tinyint(1)       | NO   |     | 0                   |       |
| subtitled               | tinyint(1)       | NO   |     | 0                   |       |
| hdtv                    | tinyint(1)       | NO   |     | 0                   |       |
| closecaptioned          | tinyint(1)       | NO   |     | 0                   |       |
| partnumber              | int(11)          | NO   |     | 0                   |       |
| parttotal               | int(11)          | NO   |     | 0                   |       |
| seriesid                | varchar(40)      | NO   | MUL |                     |       |
| originalairdate         | date             | YES  |     | NULL                |       |
| showtype                | varchar(30)      | NO   |     |                     |       |
| colorcode               | varchar(20)      | NO   |     |                     |       |
| syndicatedepisodenumber | varchar(20)      | NO   |     |                     |       |
| programid               | varchar(40)      | NO   | MUL |                     |       |
| manualid                | int(10) unsigned | NO   | PRI | 0                   |       |
| generic                 | tinyint(1)       | YES  |     | 0                   |       |
| listingsource           | int(11)          | NO   |     | 0                   |       |
| first                   | tinyint(1)       | NO   |     | 0                   |       |
| last                    | tinyint(1)       | NO   |     | 0                   |       |
+-------------------------+------------------+------+-----+---------------------+-------+
29 rows in set (0.00 sec)

comment:3 Changed 13 years ago by torbjorn.jansson@…

maybe it's the ordering of the fields in the two tables that causes the problem.

mysql> describe program;
+-------------------------+------------------+------+-----+---------------------+-------+
| Field                   | Type             | Null | Key | Default             | Extra |
+-------------------------+------------------+------+-----+---------------------+-------+
| chanid                  | int(10) unsigned | NO   | PRI | 0                   |       |
| starttime               | datetime         | NO   | PRI | 0000-00-00 00:00:00 |       |
| endtime                 | datetime         | NO   | MUL | 0000-00-00 00:00:00 |       |
| title                   | varchar(128)     | NO   | MUL |                     |       |
| subtitle                | varchar(128)     | NO   |     |                     |       |
| description             | text             | NO   |     |                     |       |
| category                | varchar(64)      | NO   |     |                     |       |
| airdate                 | year(4)          | NO   |     | 0000                |       |
| stars                   | float unsigned   | NO   |     | 0                   |       |
| previouslyshown         | tinyint(4)       | NO   |     | 0                   |       |
| category_type           | varchar(64)      | NO   |     |                     |       |
| title_pronounce         | varchar(128)     | NO   | MUL |                     |       |
| stereo                  | tinyint(1)       | NO   |     | 0                   |       |
| subtitled               | tinyint(1)       | NO   |     | 0                   |       |
| hdtv                    | tinyint(1)       | NO   |     | 0                   |       |
| closecaptioned          | tinyint(1)       | NO   |     | 0                   |       |
| partnumber              | int(11)          | NO   |     | 0                   |       |
| parttotal               | int(11)          | NO   |     | 0                   |       |
| seriesid                | varchar(12)      | NO   | MUL |                     |       |
| originalairdate         | date             | YES  |     | NULL                |       |
| showtype                | varchar(30)      | NO   |     |                     |       |
| colorcode               | varchar(20)      | NO   |     |                     |       |
| syndicatedepisodenumber | varchar(20)      | NO   |     |                     |       |
| programid               | varchar(20)      | NO   | MUL |                     |       |
| manualid                | int(10) unsigned | NO   | PRI | 0                   |       |
| generic                 | tinyint(1)       | YES  |     | 0                   |       |
| listingsource           | int(11)          | NO   |     | 0                   |       |
| first                   | tinyint(1)       | NO   |     | 0                   |       |
| last                    | tinyint(1)       | NO   |     | 0                   |       |
+-------------------------+------------------+------+-----+---------------------+-------+
29 rows in set (0.00 sec)
mysql> describe recordedprogram;
+-------------------------+------------------+------+-----+---------------------+-------+
| Field                   | Type             | Null | Key | Default             | Extra |
+-------------------------+------------------+------+-----+---------------------+-------+
| chanid                  | int(10) unsigned | NO   | PRI | 0                   |       |
| starttime               | datetime         | NO   | PRI | 0000-00-00 00:00:00 |       |
| endtime                 | datetime         | NO   | MUL | 0000-00-00 00:00:00 |       |
| title                   | varchar(128)     | NO   | MUL |                     |       |
| subtitle                | varchar(128)     | NO   |     |                     |       |
| description             | text             | NO   |     |                     |       |
| category                | varchar(64)      | NO   |     |                     |       |
| category_type           | varchar(64)      | NO   |     |                     |       |
| airdate                 | year(4)          | NO   |     | 0000                |       |
| stars                   | float unsigned   | NO   |     | 0                   |       |
| previouslyshown         | tinyint(4)       | NO   |     | 0                   |       |
| title_pronounce         | varchar(128)     | NO   | MUL |                     |       |
| stereo                  | tinyint(1)       | NO   |     | 0                   |       |
| subtitled               | tinyint(1)       | NO   |     | 0                   |       |
| hdtv                    | tinyint(1)       | NO   |     | 0                   |       |
| closecaptioned          | tinyint(1)       | NO   |     | 0                   |       |
| partnumber              | int(11)          | NO   |     | 0                   |       |
| parttotal               | int(11)          | NO   |     | 0                   |       |
| seriesid                | varchar(12)      | NO   | MUL |                     |       |
| originalairdate         | date             | YES  |     | NULL                |       |
| showtype                | varchar(30)      | NO   |     |                     |       |
| colorcode               | varchar(20)      | NO   |     |                     |       |
| syndicatedepisodenumber | varchar(20)      | NO   |     |                     |       |
| programid               | varchar(20)      | NO   | MUL |                     |       |
| manualid                | int(10) unsigned | NO   | PRI | 0                   |       |
| generic                 | tinyint(1)       | YES  |     | 0                   |       |
| listingsource           | int(11)          | NO   |     | 0                   |       |
| first                   | tinyint(1)       | NO   |     | 0                   |       |
| last                    | tinyint(1)       | NO   |     | 0                   |       |
+-------------------------+------------------+------+-----+---------------------+-------+
29 rows in set (0.01 sec)

as you can see, begining at category_type the order of the fields is different in the two tables. i've had this database for a long time, several years maybe so it's been thru a lot of db updates.

a clean database will most likely not have this problem.

comment:4 Changed 13 years ago by danielk

Resolution: wontfix
Status: newclosed

I think this was do to out of order DB changes. This is only likely to effect developers who apply dbcheck DB updates manually.

comment:5 Changed 13 years ago by anonymous

My tables look identical, so I doubt it's a user error of the previous reporter. I've been using mythtv for a long time now (back before dbcheck). So this is probably due to a bug in the previous db generation/upgrade scripts.

It seems likely to me that you'll find that everybody with an old database has this problem.

Note: See TracTickets for help on using tickets.