Opened 17 years ago
Closed 17 years ago
Last modified 17 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)
Change History (6)
Changed 17 years ago by
| Attachment: | recordedprograms-db-fix.patch added |
|---|
comment:1 Changed 17 years ago by
looks like theres a bug in trac, my email address was replaced by anonymous for some reason.
comment:2 Changed 17 years ago by
| 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 17 years ago by
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 17 years ago by
| Resolution: | → wontfix |
|---|---|
| Status: | new → closed |
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 17 years ago by
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.
the patch