LinHES Forums http://forums.linhes.org/ |
|
Database Errors After Upgrade http://forums.linhes.org/viewtopic.php?f=1&t=18480 |
Page 2 of 3 |
Author: | knappster [ Fri Jul 11, 2008 10:54 pm ] |
Post subject: | |
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 |
Author: | greywire [ Mon Jul 14, 2008 10:49 am ] |
Post subject: | so.. |
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.. |
Author: | knappster [ Mon Jul 14, 2008 11:49 am ] |
Post subject: | |
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 |
Author: | greywire [ Mon Jul 14, 2008 2:58 pm ] |
Post subject: | hmm |
if that works, let me know.. Before I waste my time... |
Author: | knappster [ Mon Jul 14, 2008 3:14 pm ] |
Post subject: | |
I won't know for at least a couple days Waiting for a hard drive to be delivered that I can dd to. |
Author: | goofee [ Tue Jul 15, 2008 11:18 am ] |
Post subject: | |
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. |
Author: | knappster [ Tue Jul 15, 2008 11:45 am ] |
Post subject: | |
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... |
Author: | knappster [ Tue Jul 15, 2008 8:14 pm ] |
Post subject: | |
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. |
Author: | greywire [ Tue Jul 15, 2008 8:33 pm ] |
Post subject: | sql diff |
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. |
Author: | knappster [ Tue Jul 15, 2008 9:00 pm ] |
Post subject: | |
* - 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... |
Author: | knappster [ Tue Jul 15, 2008 9:37 pm ] |
Post subject: | |
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. |
Author: | goofee [ Thu Jul 17, 2008 8:31 am ] |
Post subject: | |
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 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?
< `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`) |
Author: | knappster [ Thu Jul 17, 2008 10:53 am ] |
Post subject: | |
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. |
Author: | jmacmythtv [ Sat Jul 19, 2008 6:31 am ] |
Post subject: | |
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! |
Author: | tjc [ Sat Jul 19, 2008 9:18 am ] |
Post subject: | |
This seems to be built into the MythTV backend server. |
Page 2 of 3 | All times are UTC - 6 hours |
Powered by phpBB® Forum Software © phpBB Group http://www.phpbb.com/ |