View unanswered posts    View active topics

All times are UTC - 6 hours





Post new topic Reply to topic  [ 35 posts ] 
Go to page Previous  1, 2, 3  Next

Print view Previous topic   Next topic  
Author Message
Search for:
 Post subject:
PostPosted: Fri Jul 11, 2008 10:54 pm 
Offline
Joined: Thu Sep 27, 2007 5:44 pm
Posts: 364
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
 Profile  
 
 Post subject: so..
PostPosted: Mon Jul 14, 2008 10:49 am 
Offline
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
 Profile  
 
 Post subject:
PostPosted: Mon Jul 14, 2008 11:49 am 
Offline
Joined: Thu Sep 27, 2007 5:44 pm
Posts: 364
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 :P


Top
 Profile  
 
 Post subject: hmm
PostPosted: Mon Jul 14, 2008 2:58 pm 
Offline
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
 Profile  
 
 Post subject:
PostPosted: Mon Jul 14, 2008 3:14 pm 
Offline
Joined: Thu Sep 27, 2007 5:44 pm
Posts: 364
I won't know for at least a couple days :(

Waiting for a hard drive to be delivered that I can dd to.


Top
 Profile  
 
 Post subject:
PostPosted: Tue Jul 15, 2008 11:18 am 
Offline
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
 Profile  
 
 Post subject:
PostPosted: Tue Jul 15, 2008 11:45 am 
Offline
Joined: Thu Sep 27, 2007 5:44 pm
Posts: 364
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
 Profile  
 
 Post subject:
PostPosted: Tue Jul 15, 2008 8:14 pm 
Offline
Joined: Thu Sep 27, 2007 5:44 pm
Posts: 364
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
 Profile  
 
 Post subject: sql diff
PostPosted: Tue Jul 15, 2008 8:33 pm 
Offline
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
 Profile  
 
 Post subject:
PostPosted: Tue Jul 15, 2008 9:00 pm 
Offline
Joined: Thu Sep 27, 2007 5:44 pm
Posts: 364
* - 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
 Profile  
 
 Post subject:
PostPosted: Tue Jul 15, 2008 9:37 pm 
Offline
Joined: Thu Sep 27, 2007 5:44 pm
Posts: 364
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
 Profile  
 
 Post subject:
PostPosted: Thu Jul 17, 2008 8:31 am 
Offline
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
 Profile  
 
 Post subject:
PostPosted: Thu Jul 17, 2008 10:53 am 
Offline
Joined: Thu Sep 27, 2007 5:44 pm
Posts: 364
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
 Profile  
 
 Post subject:
PostPosted: Sat Jul 19, 2008 6:31 am 
Offline
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
 Profile  
 
 Post subject:
PostPosted: Sat Jul 19, 2008 9:18 am 
Offline
Joined: Thu Mar 25, 2004 11:00 am
Posts: 9551
Location: Arlington, MA
This seems to be built into the MythTV backend server.


Top
 Profile  
 

Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 35 posts ] 
Go to page Previous  1, 2, 3  Next



All times are UTC - 6 hours




Who is online

Users browsing this forum: No registered users and 2 guests


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Jump to:  
Powered by phpBB® Forum Software © phpBB Group

Theme Created By ceyhansuyu