Author |
Message |
knappster
|
Posted: Fri Jul 11, 2008 10:54 pm |
|
Joined: Thu Sep 27, 2007 5:44 pm
Posts: 580
|
I have not yet upgraded to 5.5, but looking at my database I see that in my settings table, DBSchemaVer data is 1214. I had some automatic upgrades from a frontend with mythtv 0.21 before, too. I'm trying to follow what happened to your upgrade and what you had to do to resolve it. I think that this could be an issue for several others, so it might be good to come up with a tutorial or script for resolving it.
Could you explain the sequence (database specific) that someone should follow when upgrading from R5F27 to R5.5 and their DBSchemaVer is 1214? I would appreciate it
|
|
Top |
|
|
greywire
|
Posted: Mon Jul 14, 2008 10:49 am |
|
Joined: Mon Oct 18, 2004 12:22 pm
Posts: 66
Location:
orange county ca
|
In my case, I think the issue is that I have a partially upgraded database because I had a front end do an auto upgrade (by accident) and then I went in by hand and fixed the sql errors I saw, leaving my database in a quasi upgraded state, and that is causing the 5.5 upgrade to bork.
So, if I can make sure that my database is fully updated, then the 5.5 upgrade should work.
So what is needed is to take that log, and create a script that's smart enough to check as its going and not bork if say a table is already there, etc.
I can do that...
Because I am sure there's going to be a few people with this problem..
_________________ --
Lead Developer, Trixbox CE.
Fonality, inc.
|
|
Top |
|
|
knappster
|
Posted: Mon Jul 14, 2008 11:49 am |
|
Joined: Thu Sep 27, 2007 5:44 pm
Posts: 580
|
I'm sure that I will be one of those people. But if a solution is to upgrade a remote frontend to myth 0.21 and let it auto-messup the database just before upgrading then that's obviously something I can do. That's what got me in this mess in the first place
|
|
Top |
|
|
greywire
|
Posted: Mon Jul 14, 2008 2:58 pm |
|
Joined: Mon Oct 18, 2004 12:22 pm
Posts: 66
Location:
orange county ca
|
if that works, let me know..
Before I waste my time...
_________________ --
Lead Developer, Trixbox CE.
Fonality, inc.
|
|
Top |
|
|
knappster
|
Posted: Mon Jul 14, 2008 3:14 pm |
|
Joined: Thu Sep 27, 2007 5:44 pm
Posts: 580
|
I won't know for at least a couple days
Waiting for a hard drive to be delivered that I can dd to.
|
|
Top |
|
|
goofee
|
Posted: Tue Jul 15, 2008 11:18 am |
|
Joined: Sat Feb 03, 2007 12:52 am
Posts: 187
Location:
Manitoba, Canada
|
I'm pretty certain that it won't work. My first attempt was to reset the DBSchemaVer value back to R5F27 and have the backend auto upgrade it for me. It started to then I got errors like Quote: ERROR 1060 (42S21): Duplicate column name 'lnb_pol_inv' Database upgrade failed.
I was playing around on the weekend and after formating the log I was able to use Code: mysql -u mythtv -p -D mythconverg < backend.log -f I had to use the -f force option or else it would fail as did the backend upgrade attempts. The problem with that might be lines like Code: SELECT cardid FROM capturecard WHERE cardtype='FIREWIRE'; followed by Code: DELETE FROM capturecard WHERE cardtype = 'FIREWIRE';
I don't have any firewire cards, if I did, it might have copied it to a temperary table and back as it did a couple of other places. It might not be that big of a deal, you would just have to re-add you card in mythtv-setup.
I have also posted for your viewing pleasure 2 fresh databases. New auto installs with no cards setup or guide/listing data.
http://www.mts.net/~wfee/mythconverg.sql.fresh.R5F27
http://www.mts.net/~wfee/mythconverg.sql.fresh.R5.5
I tried to run the diff command on them to see if it would show just the difference in table structures but... I think that it's a little beyond me at this time. I was just wondering if it would be easier/safer to fully downgrade the database to R5F27 and let the backend autoupgrade it.
|
|
Top |
|
|
knappster
|
Posted: Tue Jul 15, 2008 11:45 am |
|
Joined: Thu Sep 27, 2007 5:44 pm
Posts: 580
|
There appears to be some software on the net called sql diff, but I haven't had much chance to look into it. There is some antiquated software called sqlt diff too, but I was unable to even get it to compile on my machine. I would like to downgrade my database prior to the upgrade, but I don't know if I'm up to foraging through 2 giant databases to figure out the differences are. There has got to be a better way...
|
|
Top |
|
|
knappster
|
Posted: Tue Jul 15, 2008 8:14 pm |
|
Joined: Thu Sep 27, 2007 5:44 pm
Posts: 580
|
Update: I installed sql diff and while it is useful for locating changes in tables and so forth, it is not real useful in pointing them out. I am starting to go through the two databases that goofee posted table by table looking for changes. I will post a very poorly formatted log and mention the point that I have left off when I run out of steam.
|
|
Top |
|
|
greywire
|
Posted: Tue Jul 15, 2008 8:33 pm |
|
Joined: Mon Oct 18, 2004 12:22 pm
Posts: 66
Location:
orange county ca
|
yeah there are a few tools out there but none of them are that good and none of them are going to spit out something we can use.
I am quite an expert on databases these days and I can certainly create a smart script to do what we need. I just have to find the time to do it. That you have already posted the schemas and the log will certainly help things along.
I have just now managed to get the first step of my upgrade complete, which was replaceing the harddrive with a bigger one. Now I can focus on the R5.5 upgrade.
_________________ --
Lead Developer, Trixbox CE.
Fonality, inc.
|
|
Top |
|
|
knappster
|
Posted: Tue Jul 15, 2008 9:00 pm |
|
Joined: Thu Sep 27, 2007 5:44 pm
Posts: 580
|
* - Table name
I tried to sum up the changes in each table and separate them by asterisks.
Code: R5F27 -> R5.5
*archiveitems Data type change
Changed data type of size from int(10) to bigint(20) type default from blank to 0
--------------------------------------------------------------------
*callsignnetworkmap No changes
--------------------------------------------------------------------
*capturecard
Removed dvb_recordts int(11) Default 1, dvb_hw_decoder int(11) Default 0, parentid int(10) Default 0 Added dvb_eitscan tinyint(1) Default 1
--------------------------------------------------------------------
*cardinput
Removed childcardid int(10) Default 0 Added quicktune tinyint(4) Default 0
--------------------------------------------------------------------
*channels
Added last_record datetime Default blank, default_authority varchar(32) Default blank, commmethod int(11) Default -1
--------------------------------------------------------------------
*codecparams
Inserted Entries into table
profile name value -------------------------------------------- 21 transcodelossless 0 21 transcoderesize 0 21 mpeg4bitrate 2200 21 mpeg4maxquality 2 21 mpeg4minquality 15 21 mpeg4qualdiff 3 21 scalebitrate 1 21 mpeg4optionvhq 1 21 mpeg4option4mv 1 21 mpeg4optionidct 0 21 mpeg4optionime 0 21 encodingthreadcount 1 21 mpeg2bitrate 4500 21 hardwaremjpegquality 100 21 hardwaremjpeghdecimation 4 21 hardwaremjpegvdecimation 4 21 mpeg2streamtype MPEG-2 PS 21 mpeg2maxbitrate 6000 21 samplerate 48000 21 volume 100 22 transcodelossless 0 22 transcoderesize 0 22 mpeg4bitrate 2200 22 mpeg4maxquality 2 22 mpeg4minquality 15 22 mpeg4qualdiff 3 22 scalebitrate 1 22 mpeg4optionvhq 1 22 mpeg4option4mv 1 22 mpeg4optionidct 0
----------------------------------------------------------------
*credits
NO CHANGES
----------------------------------------------------------------
*customexample
Added search tinyint(4) Default 0
----------------------------------------------------------------
*diseqc_config
NO CHANGES
----------------------------------------------------------------
*diseqc_tree
Add lnb_pol_inv tinyint(4) Default 0
----------------------------------------------------------------
*displayprofilegroups NEW TABLE
Add name varchar(128) Default blank, hostname varchar(255) Default blank, profilegroupid int(10)unsigned Default blank
Indexes: Keyname Type Cardinality Field PRIMARY PRIMARY 6 name hostname profilegroupid UNIQUE 6 profilegroupid
name hostname profilegroupid -------------------------------------- CPU++ mythtv 1 CPU+ mythtv 2 `CPU-- mythtv 3 High Quality mythtv 4 Normal mythtv 5 Slim mythtv 6
----------------------------------------------------------------
*displayprofiles NEW TABLE
Add profilegroupid int(10)unsigned Default blank, profileid int(10)unsigned Default blank (autoincrement), value varchar(128) Default blank, data varchar(255) Default blank
Indexes: Keyname Type Cardinality Field profilegroupid INDEX 6 profilegroupid profileid INDEX None profileid value profileid_2 INDEX 24 profileid
profilegroupid profileid value data ------------------------------------------------------------ 1 1 pref_priority 1 1 1 pref_cmp0 > 0 0 1 1 pref_decoder ffmpeg 1 1 pref_max_cpus 1 1 1 pref_videorenderer xv-blit 1 1 pref_osdrenderer softblend 1 1 pref_osdfade 1 1 1 pref_deint0 bobdeint 1 1 pref_deint1 linearblend 1 1 pref_filters 1 2 pref_priority 2 1 2 pref_cmp0 > 0 0 1 2 pref_decoder ffmpeg 1 2 pref_max_cpus 1 1 2 pref_videorenderer quartz-blit 1 2 pref_osdrenderer softblend 1 2 pref_osdfade 1 1 2 pref_deint0 linearblend 1 2 pref_deint1 linearblend 1 2 pref_filters 2 3 pref_priority 1 2 3 pref_cmp0 <= 720 576 2 3 pref_cmp1 > 0 0 2 3 pref_decoder ffmpeg 2 3 pref_max_cpus 1 2 3 pref_videorenderer xv-blit 2 3 pref_osdrenderer softblend 2 3 pref_osdfade 1 2 3 pref_deint0 bobdeint 2 3 pref_deint1 linearblend
----------------------------------------------------------------------
*dtv_multiplex NO CHANGES
----------------------------------------------------------------------
*dtv_privatetypes Emptied Populated Table Following entries removed
sitype networkid private_type private_value ----------------------------------------------------- atsc 1793 guide_fixup 3 dvb 9018 channel_numbers 131
-----------------------------------------------------------------------
*dvdbookmark NEW TABLE
Add serialid varchar(16) Default blank, name varchar(32) Default NULL, title smallint(6) Default 0, audionum tinyint(4) Default -1, subtitlenum tinyint(4) Default -1, framenum bigint(20) Default 0, timestamp timestamp (ON UPDATE CURRENT_TIMESTAMP) Defaul CURRENT_TIMESTAMP
Indexes: Keyname Type Cardinality Field PRIMARY PRIMARY 0 serialid
------------------------------------------------------------------------
*dvdinput Emptied Populated Table Following entries removed
intid hsize vsize ar_num ar_denom fr_code letterbox v_format 1 720 480 16 9 1 1 ntsc 2 720 480 16 9 1 0 ntsc 3 720 480 4 3 1 1 ntsc 4 720 480 4 3 1 0 ntsc 5 720 576 16 9 3 1 pal 6 720 576 16 9 3 0 pal 7 720 576 4 3 3 1 pal 8 720 576 4 3 3 0 pal
----------------------------------------------------------------------------
First 12 tables of the 92 in the database for 5.5
One of the things that might come back to bite me on this is that I don't think that this process has been double checking that none of the primary keys, unique keys, etc. has changed...
|
|
Top |
|
|
knappster
|
Posted: Tue Jul 15, 2008 9:37 pm |
|
Joined: Thu Sep 27, 2007 5:44 pm
Posts: 580
|
While looking for a simpler way, I found this information on the mythtv WIKI FAQs...
http://www.mythtv.org/wiki/index.php/Fr ... lease_X.3F
I believe it...
Quote: If a backup of the mythconverg database was not made before upgrading, a downgrade will likely be impossible without recreating the database (thereby losing all recordings, settings etc in the process - basically starting from scratch). See the "Miscellaneous" section of the MythTV Documentation for more details. Newer versions of MythTV often upgrade the database schema (tables & columns change structure etc) and older code is unlikely to work with newer database schemas.
If a pre-upgrade database backup is available, dump the current database and reinsert the old one.
While it may seem that MythTV works fine after downgrading versions, any data being added to the mythconverg database is likely being corrupted. And--if nothing else--new data is being inserted into the database in the old format. Therefore, running MythTV with the "upgraded" database after downgrading versions is a time-bomb. While MythTV may work fine now with the older version, it will surely fail when you upgrade later. So, the longer it is run in this broken state, the more data will need to be fixed (or the more data will be lost) upon later upgrades. Therefore, regardless of whether it seems to be working, the pre-upgrade database (from your backup) should always be reverted when downgrading MythTV versions.
|
|
Top |
|
|
goofee
|
Posted: Thu Jul 17, 2008 8:31 am |
|
Joined: Sat Feb 03, 2007 12:52 am
Posts: 187
Location:
Manitoba, Canada
|
I was thinking about this again. I stole some commands from other scripts so they may not be ideal but they seem to work. Code: lynx -dump http://www.mts.net/~wfee/mythconverg.sql.fresh.R5F27 | sed -n '/^-- Table structure for table `recorded`$/,/^-- Dumping data for table `recorded`$/p' | sed '/^-- Table structure for table `recorded`$\|^-- Dumping data for table `recorded`$/d' > R5F27_recorded.txt gives me just the structure of the R5F27 database. Code: lynx -dump http://www.mts.net/~wfee/mythconverg.sql.fresh.R5.5 | sed -n '/^-- Table structure for table `recorded`$/,/^-- Dumping data for table `recorded`$/p' | sed '/^-- Table structure for table `recorded`$\|^-- Dumping data for table `recorded`$/d' > R5.5_recorded.txt Then Code: diff R5F27_recorded.txt R5.5_recorded.txt tells me all the differences in that table. Code: 20,21c20,21 < `seriesid` varchar(12) NOT NULL default '', < `programid` varchar(20) NOT NULL default '', --- > `seriesid` varchar(40) NOT NULL default '', > `programid` varchar(40) NOT NULL default '', 34c34 < `basename` varchar(128) NOT NULL default '', --- > `basename` varchar(255) NOT NULL, 41a42 > `storagegroup` varchar(32) NOT NULL default 'Default', 47c48,50 < KEY `recordid` (`recordid`) --- > KEY `recordid` (`recordid`), > KEY `deletepending` (`deletepending`,`lastmodified`), > KEY `recgroup` (`recgroup`,`endtime`)
I then compared my tainted databse with the R5.5 one , they where the same. I'm thinking that the front end upgraded the entire DB and the only tables that are wrong are the ones that where manually downgraded.(capturecard) Probably if one backed up and only restored the tables needed for recorded programs ( http://mysettopbox.tv/phpBB2/viewtopic.php?t=17867) then things _should_ just work?
|
|
Top |
|
|
knappster
|
Posted: Thu Jul 17, 2008 10:53 am |
|
Joined: Thu Sep 27, 2007 5:44 pm
Posts: 580
|
As I mentioned in this post:
http://mysettopbox.tv/phpBB2/viewtopic.php?p=114188
I am working on cloning my hard drive so that I can test what will happen. If the auto-upgrade doesn't work, I may just use the technique that you linked. It's basically damage control.
|
|
Top |
|
|
jmacmythtv
|
Posted: Sat Jul 19, 2008 6:31 am |
|
Joined: Sun Mar 19, 2006 10:42 am
Posts: 89
|
I am in the "my separate frontend updated my backend db" club too (Debian Lenny). Just to clarify: the consensus is that the r5.5 upgrade fails if the db schema is fully updated to 1214?
If so, is there a way to remove the db update section from the update script - or an option to ignore the "table already exists" errors?
tks!
|
|
Top |
|
|
tjc
|
Posted: Sat Jul 19, 2008 9:18 am |
|
Joined: Thu Mar 25, 2004 11:00 am
Posts: 9551
Location:
Arlington, MA
|
This seems to be built into the MythTV backend server.
|
|
Top |
|
|