| View previous topic :: View next topic |
| Author |
Message |
depontius
Advocate
Joined: 05 May 2004
Posts: 3509
|
 Posted: Thu Feb 11, 2010 10:20 pm Post subject: |
 |
|
Looks bad...
| Code: | Testing people...
Corruption detected altering table temp_people, Warnings: 1415
Testing recorded...
Corruption detected altering table temp_recorded, Warnings: 2
Testing oldrecorded...
Corruption detected altering table temp_oldrecorded, Warnings: 22
Testing oldprogram...
Corruption detected altering table temp_oldprogram, Warnings: 212
Failures detected on 4 tables |
Aren't "recorded", "oldrecorded", and "oldprogram" the tables they try to keep if you have "partial corruption"?
If this can be scripted, can it be resolved? I may have a look at your script, and see if I can dump details.
_________________
.sigs waste space and bandwidth |
|
| Back to top |
|
 |
tld
Veteran
Joined: 09 Dec 2003
Posts: 1816
|
| Posted: Thu Feb 11, 2010 11:13 pm Post subject: |
|
|
Here's a version that will give you some detail as to the warnings:
http://digitalaudiorock.com/scripts/mythtv_0_22_corruption_test_det.pl
The recorded table is obviously important, as that represents your current recordings. oldrecorded is important for scheduling to know what's been recorded. Those seem to be the least of your errors, which is good.
As I mentioned previously, I think that clearing the oldprogram will affect nothing more than the ability to to a "New Titles" search. I also believe that people table can be cleared of anything not referenced in credits or recordedcredits.
DISCLAIMER: I can't as yet guarantee any of this!
Here's what I did from a mysql prompt to reduce my errors dramatically:
| Code: | DELETE FROM people WHERE NOT EXISTS (SELECT * FROM credits WHERE person=people.person) AND NOT EXISTS (SELECT * FROM recordedcredits WHERE person=people.person);
TRUNCATE TABLE oldprogram; |
If you want to try that on that test database, the test scripts can be rerun after.
I also have a really dangerous update script I've been trying. I hesitate to give that out as it could be VERY dangerous. What that does is to take all the rows with warnings and attempts to convert any of the non-ASCII accented characters (in the REAL tables in the database you run against...NOT the temporary tables) to the closest non-accented character. That is for example, it would change "Gérard Depardieu" to "Gerard Depardieu" in the people table. That's of course not correct, but would at least be enough to get me to a point that the database should update.
It wouldn't work on oldprogram without causing a ton unique index conflicts. That's why I gave up on that table. It also only translates the accented characters that I happened to find in my database. If I end up actually using it, it will ONLY be safe to use just before the upgrade. If you attempted to use it ahead of time, you could get new table entries just like the old ones that couldn't be renamed in this manner without unique index conflicts.
If you want to test that one, I'd refer if you email me at the contact link on my site (http://digitalaudiorock.com) as I really don't want to be responsible for that thing.
Tom |
|
| Back to top |
|
|
depontius
Advocate
Joined: 05 May 2004
Posts: 3509
|
| Posted: Thu Feb 11, 2010 11:42 pm Post subject: |
|
|
| Code: | Forbidden
You do not have permission to access this document. |
I think there's a permission issue on the path to your script, and I also see that this latest one isn't at the list archives, at least not yet. At first blush, this looks pretty bad to me, but you say it could be worse. Looking more at your script, I see that there are deeper conditional clauses that aren't printing out for me, so I presume if I were getting messages from there, it would be even worse?
To be perfectly honest, I don't really do (Though I have had to tweak on occasion, I'm not good at it.) perl or sql, so wading through your script it looks to me as if near the line | Code: | | print "Corruption detected altering table temp_$tablename, Warnings: ${row[0]}\n"; |
I could add some more print statements, and it would tell me what entry triggered the action. What would I print? In such a case, perhaps I could go in through the regular Myth interface, delete some stuff, and get rid of the problems.
2 errors I can clear by hand, even 22, probably even 212. 1415 errors will take some automation. What's in the "people" table - I would expect that to be actors/directors, etc?
_________________
.sigs waste space and bandwidth |
|
| Back to top |
|
|
tld
Veteran
Joined: 09 Dec 2003
Posts: 1816
|
|
| Back to top |
|
|
tld
Veteran
Joined: 09 Dec 2003
Posts: 1816
|
| Posted: Fri Feb 12, 2010 12:04 am Post subject: |
|
|
| depontius wrote: |
2 errors I can clear by hand, even 22, probably even 212. 1415 errors will take some automation. What's in the "people" table - I would expect that to be actors/directors, etc? |
As far as I can tell, yes, that's what it is. The only references I see to it appear to be in the credits and recordedcredits tables. It appears that entries on those two tables get deleted eventually, but the people records they reference stay out there. That's why I decided to clean them up. If one of the deleted entries is ever needed again for a new program, it'll just get created with a new id.
I posted a question to the mythtv users list to verify that, and also to verify that the oldprogram table isn't used for anything but the "New Titles" search.
Tom |
|
| Back to top |
|
|
depontius
Advocate
Joined: 05 May 2004
Posts: 3509
|
| Posted: Fri Feb 12, 2010 12:47 am Post subject: |
|
|
Thanks, got it. I've looked through the results, and on their own there's not a lot to go by. When I have more time I'll grab a dump, and see how I can cross-reference the two together.
_________________
.sigs waste space and bandwidth |
|
| Back to top |
|
|
depontius
Advocate
Joined: 05 May 2004
Posts: 3509
|
| Posted: Fri Feb 12, 2010 3:06 am Post subject: |
|
|
OK, I've chased down the two entries in "recorded".
"Eureka" subtitle "Noche de Suenos" - with an accent on that last "n" that caused the problem.
"The Hour Holiday Special" with Michael Buble' in the description, and that accented "e".
I'm not sure what to do with this, other than delete those 2 shows, scrap the whole table, or try your script.
I notice that you focused on 4 tables, and in discussion of fixing "partial corruption" I believe that they focus on 4 tables, also. Same 4?
---------------- edit ----------------------------
After further thought, I need to restate this...
Does your script indicate that I have a "uniformly corrupted," (not good) "partially corrupted," (bad) or "unfixable by published means" (really bad) database?
In other words, is simply attempting to upgrade the database by running the mythtv-0.22 mythtv-setup likely to fail?
If I do the suggested backup/drop/restore, then upgrade is that also likely to fail?
If I follow the directions for a "partially corrupt" database, losing all of my configuration in the process, then upgrade is that too likely to fail?
How about if I nuvexport, then delete those 2 offending shows, then give up on everything except my existing "recorded" database?
Oddly enough, had I just lived with the "bad" my.cnf from day 1, I'd probably have a "uniformly corrupt" database now, and have a fairly easy time of the upgrade. Because I saw instructions to "fix" it, either in forums, news, or something, THEN somehow my.cnf got back to utf8, I'm in a royal mess.
---
On another tack... I found the accented characters your script pointed out as the problem. Does the flagging by your script mean that those strings are present in the data multiple places, sometimes in latin1 and sometimes in utf8? Is the difference visible if I look at the raw mysql dump? It's a big file, but I've grubbed through it before, and I keep thinking about a script to parse it out a bit, making it more readable.
One question though... Is mysql whitespace-insensitive, as long as the whitespace is in the right place? One of the first things I'm thinking of is "s/),(/),\n(/g" to split table rows onto separate lines. I'm also thinking of a formatted print, to make them look more like a text table or spreadsheet. Beyond that, maybe a pair of utilities to split the dump into file-per-table-in-a-directory and another to glom them back together, again. Certainly good for reading and learning, but could mysql read the result of a round-trip?
_________________
.sigs waste space and bandwidth |
|
| Back to top |
|
|
tld
Veteran
Joined: 09 Dec 2003
Posts: 1816
|
| Posted: Fri Feb 12, 2010 2:12 pm Post subject: |
|
|
The problem itself as I understand it was caused when the client connection was utf8 and things such as accented characters were involved. In that situation MythTV ended up trying to insert utf8 data into tables with latin1 character columns. This creates corrupted string data that can not be properly converted to utf8 later on.
The "incorrect string value" warnings you get in the test on rows with bad data actually causes data to be truncated. The four tables they test before attempting the actual upgrade are: people, oldprogram, recorded, and oldrecorded. I believe that the reason they focus on these is that bad data in some of their columns can cause a situation where the conversion to utf8 causes multiple rows to have the same value where unique indexes or primary keys are involved, which would cause the upgrade of the table to fail. I think that another reason they focus on these may be that they are the tables specifically susceptible to this, as they get their data from sources such as schedules direct which can frequently involve such characters.
Here's an example. I'm doing this test in a database that is latin1 by default, but with a client connection of uft8:
| Code: | create table tom (name varchar(255));
Query OK, 0 rows affected (0.00 sec)
insert into tom values ('Gérard Depardieu');
Query OK, 1 row affected (0.00 sec)
alter table tom modify name varbinary(255);
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
alter table tom modify name char(255) CHARACTER SET utf8 COLLATE utf8_bin;
Query OK, 1 row affected, 1 warning (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
show warnings;
+---------+------+-------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+-------------------------------------------------------------------+
| Warning | 1366 | Incorrect string value: '\xE9rard ...' for column 'name' at row 1 |
+---------+------+-------------------------------------------------------------------+
select * from tom;
+------+
| name |
+------+
| G |
+------+
1 row in set (0.00 sec)
|
Tom |
|
| Back to top |
|
|
tld
Veteran
Joined: 09 Dec 2003
Posts: 1816
|
| Posted: Mon Feb 15, 2010 5:06 pm Post subject: |
|
|
Somehow I missed your questions above...I think you added them after my last reply. By the way...I was able to upgrade and everything's working fine.
| depontius wrote: |
After further thought, I need to restate this...
Does your script indicate that I have a "uniformly corrupted," (not good) "partially corrupted," (bad) or "unfixable by published means" (really bad) database?
In other words, is simply attempting to upgrade the database by running the mythtv-0.22 mythtv-setup likely to fail?
If I do the suggested backup/drop/restore, then upgrade is that also likely to fail?
If I follow the directions for a "partially corrupt" database, losing all of my configuration in the process, then upgrade is that too likely to fail?
|
My understanding is that you must do the backup/restore fix if you've been running with a uft8 connection. If my test script shows errors after you've done that, the upgrade will fail as well. My script is doing the same tests as the upgrade.
| Quote: |
How about if I nuvexport, then delete those 2 offending shows, then give up on everything except my existing "recorded" database?
|
When you say "give up on everything except...", I think that the only safe way to do that would be a partial restore as described here:
http://www.mythtv.org/wiki/Database_Backup_and_Restore#Partial_restore_of_a_backup
...and if you've already upgraded and need an empty database with the 0.21-fixes schema, you can get that here:
http://www.gossamer-threads.com/lists/mythtv/users/406111#406111
That method can save you're recordings, but requires pretty much reconfiguring mythtv.
Once I deleted orphaned records on the 'people' table and cleared oldprogram altogther, I used that rather scary script I told you about that took the remaining problem rows (44 in total, mostly on 'people' with a few on 'recorded' and 'oldrecorded') and simply replaced the offending characters with non-accented characters that looked close. If there's a way it could have actually corrected them, it would require putting together some binary data that's beyond my understanding for sure, and it only would have ended up actually causing unique key conflicts with correct records anyway. The worst that my approach may have done to me is a few slightly incorrect titles or descriptions and the possibility of eventually ended up with two entries in the people table for the same person with and without accented characters...all pretty harmless. Again however, that wasn't feasible on the oldprogram table, as it would cause all sorts of unique key violations.
| Quote: |
Oddly enough, had I just lived with the "bad" my.cnf from day 1, I'd probably have a "uniformly corrupt" database now, and have a fairly easy time of the upgrade. Because I saw instructions to "fix" it, either in forums, news, or something, THEN somehow my.cnf got back to utf8, I'm in a royal mess.
---
On another tack... I found the accented characters your script pointed out as the problem. Does the flagging by your script mean that those strings are present in the data multiple places, sometimes in latin1 and sometimes in utf8? Is the difference visible if I look at the raw mysql dump? It's a big file, but I've grubbed through it before, and I keep thinking about a script to parse it out a bit, making it more readable.
|
I don't know if there's any way to answer the question really. I believe that it's not really an issue of a mix of utf8 and latin1 data, as one where utf8 data was written to the table as if it were latin1. When you've run with both latin1 and utf8 connections, you can have some that was written correctly and some that was not. In that case I believe that the backup/restore fix can't possibly handle both correctly.
When my script shows the count of warnings, that indicates that there were string values that could not be converted to utf8 properly. If a table passes that part of the test, but gets an error such as "Corruption detected creating unique index on temp_...", that means that there was a mix of data that would cause two records with the same string, violating a unique key.
| Quote: |
One question though... Is mysql whitespace-insensitive, as long as the whitespace is in the right place? One of the first things I'm thinking of is "s/),(/),\n(/g" to split table rows onto separate lines. I'm also thinking of a formatted print, to make them look more like a text table or spreadsheet. Beyond that, maybe a pair of utilities to split the dump into file-per-table-in-a-directory and another to glom them back together, again. Certainly good for reading and learning, but could mysql read the result of a round-trip? |
Outside of literal quoted text, white space is fine, but that's a big except. A global replace like you're talking about would split any literal quoted string that happened to have a ')' in it. The resulting sql would actually work, but you'd end up with a newline in a column value.
Tom |
|
| Back to top |
|
|
depontius
Advocate
Joined: 05 May 2004
Posts: 3509
|
| Posted: Mon Feb 15, 2010 8:23 pm Post subject: |
|
|
Thanks for the detailed response. To make a long story short, I think I'm going to...
1 - Mask 0.22 until I'm good and ready, and that means getting Eureka and a few other shows transcoded and OUT of mythtv.
2 - Back up my database. (both as a mysqldump and a "cp -a" of the whole blinking mysql directory)
3 - Back up the backup. (mysqldump only, let's not get too ridiculous)
4 - Make sure I've got the source for all of my myth packages and qt3 saved away.
5 - Do a qpkg of existing mythtv packages, just to save time.
Obviously all of this is to keep myself well covered.
6 - Do the drop/reload on the database, since it's seems obvious that it's not going upgrade as-is.
7 - Try the upgrade, and hope.
8 - If that fails, follow the instructions for the partial upgrade, and hope.
9 - If that fails, start over and be glad I did step #1 above.
Back on playing with reformatting the mysql dump, the string I was looking at was "),(" to insert the newline. So the false newlines would not be added to every close-paren, but to places where there was a comma between a close paren and an open paren, as 3 consecutive characters. I'll admit the possibility of a false positive, but I suspect they'd be pretty rare.
Somewhere in this line, I just thought of the idea of converting a mysql dump into python code (with pysql bindings) that would recreate that database. At that point, I'm not sure what the whole point of the exercise is any more unless it would be possible to somehow make the record numbers/pointers symbolic in some way, so it would be possible to say, move a line in the "people" table and not have the whole thing come unglued. I suspect I'd be better off looking into one of the php/mysql interfaces (Isn't that really what mythweb is?) or the ability for OpenOffice to interface to mysql.
_________________
.sigs waste space and bandwidth |
|
| Back to top |
|
|
tld
Veteran
Joined: 09 Dec 2003
Posts: 1816
|
| Posted: Mon Feb 15, 2010 11:21 pm Post subject: |
|
|
| depontius wrote: |
Somewhere in this line, I just thought of the idea of converting a mysql dump into python code (with pysql bindings) that would recreate that database. At that point, I'm not sure what the whole point of the exercise is any more unless it would be possible to somehow make the record numbers/pointers symbolic in some way, so it would be possible to say, move a line in the "people" table and not have the whole thing come unglued. I suspect I'd be better off looking into one of the php/mysql interfaces (Isn't that really what mythweb is?) or the ability for OpenOffice to interface to mysql. |
You've completely lost me there. Anything programmed in any language would simply need to be doing all the sql in the mysqldump output anyway.
Also, your recommendation on that bug regarding this (that people hold off upgrading) isn't really an option unless they're going to just totally stop upgrading altogether, as qt3 is going away.
Tom |
|
| Back to top |
|
|
depontius
Advocate
Joined: 05 May 2004
Posts: 3509
|
| Posted: Tue Feb 16, 2010 2:30 am Post subject: |
|
|
I'm not saying to hold off for long, just long enough to do a measured job.
I'd rather not see another "expat" mess.
Besides, it's for people who already have mythtv installed, in which case they've already got qt3 installed. Clearly neither are getting upgrades, but both remain functional for a transition. Outside of this thread, the bugzilla thread, and general mythtv rumble, there isn't a lot of warning. I don't know how many simply use mythtv like any other piece of software - as an appliance, and how many keep track of developments. Someone not cued in might not be aware, might let the upgrade just happen without being fully backed up, and lose data. (Or at least indexes to data, leaving them with a bunch of numbered files.)
_________________
.sigs waste space and bandwidth |
|
| Back to top |
|
|
depontius
Advocate
Joined: 05 May 2004
Posts: 3509
|
| Posted: Sat Feb 20, 2010 2:44 am Post subject: |
|
|
Next question...
What about "nuvexport"? There is one version in portage, and it is hard-tied to mythtv-0.21*. Looking at the mythtv pages, nuvexport still exists, as part of what they call "mythextras", though there is no package of that name in portage.
_________________
.sigs waste space and bandwidth |
|
| Back to top |
|
|
depontius
Advocate
Joined: 05 May 2004
Posts: 3509
|
| Posted: Sat Feb 20, 2010 12:01 pm Post subject: |
|
|
One more question...
I was planning to set aside time today to do the MythTV upgrade, and started reading through the "Fixing Corrupt Database Encoding" guide again, and got to here:
| Code: | When do I need to fix my database configuration?
You must fix your database configuration before upgrading to post-r16789 SVN trunk or to 0.22. If you plan to run MythTV 0.21-fixes, you may continue to use your system without fixing your database configuration. To prevent an "equally-corrupt" database from becoming "partially-corrupt" (and, therefore, making clean up /much/ more difficult), it probably makes sense to wait until you upgrade to post-r16789 SVN trunk or to 0.22 before fixing your database. |
I checked my portage logs, those that I haven't pruned, and my earliest entry is for mythtv-0.21_p18314-r1 back in July 2009. If I read the guide right, the schema upgrade was put in place back at mythtv-0.21-r16789 - quite a while ago. (Assuming their "-r16789 SVN trunk" maps to Gentoo's "-p16789".) There isn't even anything that old in portage, now.
Assuming one has kept Gentoo fully up-to-date, and one is happily running an up-to-date MythTV now, is the whole database corruption thing a non-issue?
This is the biggest gap I have in my recording schedule for a while, so I was thinking of doing the upgrade today, even before Gentoo stabilizes 0.22. I'm doing the standard updates now...
_________________
.sigs waste space and bandwidth |
|
| Back to top |
|
|
tld
Veteran
Joined: 09 Dec 2003
Posts: 1816
|
| Posted: Sat Feb 20, 2010 4:33 pm Post subject: |
|
|
| depontius wrote: |
Assuming one has kept Gentoo fully up-to-date, and one is happily running an up-to-date MythTV now, is the whole database corruption thing a non-issue?
|
Read it carefully...it say "post-r16789 SVN trunk"...not post r16789 0.21-fixes. You need to do the fix when upgrading from 0.21-fixes.
Tom |
|
| Back to top |
|
|
depontius
Advocate
Joined: 05 May 2004
Posts: 3509
|
| Posted: Sat Feb 20, 2010 6:02 pm Post subject: |
|
|
But I've looked at the "fixing the database", and it all seems to be about changing "SET NAMES utf8" to "SET NAMES latin1", which occurs once in the dump of mythconverg. I took a fresh dump this morning, did a grep for "SET NAMES", and it already says "SET NAMES latin1". I remember doing something like this a year or so back, fixing my.cnf, dropping and reloading the database, though I don't remember editing anything. It seems to me that
| Code: | | zcat mythconverg-1214-20081217145744.sql.gz | sed 's/SET NAMES utf8/SET NAMES latin1/' > mythconverg-to_uncorrupt.sql |
really isn't going to do anything to my database, so there's no point in the drop/reload, is there?
I've got a qpkg of mythtv and mythtv-themes. I need to get a qpkg of qt-3. I've got safe copies of the source for both mythtv packages, and need to save a copy of the source for qt-3.
Then I just think I need to make sure I'm backed up, and give it a try.
_________________
.sigs waste space and bandwidth |
|
| Back to top |
|
|
tld
Veteran
Joined: 09 Dec 2003
Posts: 1816
|
| Posted: Sat Feb 20, 2010 7:06 pm Post subject: |
|
|
| depontius wrote: | | But I've looked at the "fixing the database", and it all seems to be about changing "SET NAMES utf8" to "SET NAMES latin1", which occurs once in the dump of mythconverg. I took a fresh dump this morning, did a grep for "SET NAMES", and it already says "SET NAMES latin1". |
That's interesting. Do you have your my.cnf set to use latin1 already? My backups started using "SET NAMES latin1" only after I did the fix and changed my my.cnf to use latin1 connections. Once I upgraded I set everything back to utf8.
In any case I think you're correct in that the backup/restore thing is moot if you're already using latin1. That however doesn't mean you won't have issues. If my test is giving you errors the upgrade will fail for sure.
Tom |
|
| Back to top |
|
|
depontius
Advocate
Joined: 05 May 2004
Posts: 3509
|
| Posted: Sat Feb 20, 2010 8:56 pm Post subject: |
|
|
Toast. (My setup, that is - failed upgrade, flagged as "partial corruption".)
Sometime a year or two back, there was some sort of notice about diddling with the mythtv database. I followed all of the directions. I know there was something in there about setting my.cnf to latin1, and something about dropping and reloading mythconverg. I don't remember too much more, only that I followed directions carefully.
When getting prepped for 0.22, I noticed that my.cnf once again had utf8. I don't know how that happened, whether the directory wasn't properly protected from automatic updates, or whether I missed one when doing etc-update. THAT is what I blame for the current problem - I had "corrected" the database long ago, following directions, and some time later my.cnf got switched back. I also connected my daughter's Ubuntu machine as a client, but I'm under the impression that only Gentoo keeps the default utf8 - everyone else switches it to latin1. At any rate, I can't easily inspect her machine, at the moment.
I tried your "fixit" script and it failed:
| Quote: | user@localhost ~/computers/mythtv $ ./mythtv_0_22_corruption_upd.pl
Testing people...
DBD::mysql::db do failed: Duplicate entry 'Frank Hubner' for key 2 at ./mythtv_0_22_corruption_upd.pl line 149.
Error updating with: UPDATE people SET name=? WHERE person=? | Frank Hubner 47378
|
Not sure where I'll go, at the moment. I have everything saved so I could go back to 0.21 in case there's some other way to correct the mess.
A couple of thoughts about your fixit script... I don't presume to get you to be coding for me - I presume you want this for yourself or others in this same pickle. I also don't really know perl or mysql. It looks to me like you:
1 - Found a string that was the name of a row, and that string had a bad character init.
2 - "Fixed" the string by changing the bad character to the nearest legal character.
3 - Tried to update the row, and in this case failed. (Frank Hubner - 2 dots over the "u", is a movie producer - 45 entries as producer, one in the works, and 2 credits as "other" in IMDB.)
It looks as if you made the update request, mysql failed it, and you reported that failure.
If you're interested might I suggest something slightly different?
4 - If the update fails, translate the string again, and try the update with that.
I suspect that in most cases there won't be many ways a collision can happen - probably umlauts (the 2 dots?) and the funny "~" sign being the most common. I'm not sure, but I think the "modifiers" tend to be different for consonants than vowels, making collisions less likely. Anyway, lets say "Hubner" with the umlaut collides with "Hubner" without the umlaut, causing a failure. In that event, I would suggest changing it to HUbner (umlaut "u" to capital "U") and see if that works. In other words, for a first collision simply flip the case. It leaves the rows unique, and the presence of a capital in the middle of a word, or a lower-case at the beginning, makes it pretty obvious what has happened.
If there's a second collision, I would suggest "Hu1bner", assuming the string length can change, "Hu2bner" for a third collision, etc. Ugly, but obvious. Perhaps even obvious enough to craft a few lines to manually fix.
One other question, and that's if it's possible to query if the new row name exists prior to trying the UPDATE. Perhaps cleaner, but I don't know if there's any real value to that, anyway.
Again, I don't presume to be asking you to code for me, I figure you're interested in this for your own purposes. I've looked at your code a few times, and I can get out of a paper bag pretty well with python, but not much perl, and never mysql. So I don't know what are perl-isms and what are mysql-isms.
By the way, I did this entire process against a "cp -a" copy of "/var/lib/mysql", so I still have my original as well as an extra copy of the original. I'm trying to decide now what to do. From everything I've seen, it looks to me like the "partial corruption" recovery isn't going to work, because the very tables they're trying to rescue are corrupted. Ironically, since I don't touch the configuration that often, very likely the tables they're throwing away are clean.
While I've got 0.22 installed, along with good backups of 0.21 and my data, I may well try the "partial corruption" fix and see what the results look like. I guess first I can do a drop/reload too, and see if that does any good.
Assuming everything "normal" fails, and at the moment I am, I've got to either decide to go back to 0.21, in case I/we can figure out a way to repair, or blow it all away and go to 0.22.
_________________
.sigs waste space and bandwidth |
|
| Back to top |
|
|
depontius
Advocate
Joined: 05 May 2004
Posts: 3509
|
| Posted: Sun Feb 21, 2010 1:08 am Post subject: |
|
|
Latest status... still toasty
I did a "partial restore", which should have left my previously recorded programs available - but it didn't. I've effectively started completely from scratch. It's fetching SchedulesDirect right now, and I'm going to exercise things a little. I'm alsogoing to take a look at the "partial restore" database done right before the conversion, and see what the heck was in there. Then I'm going to dump the post-conversion database and see what is in that.
All in all, I think I'm headed back to 0.21 shortly. I've got 0.22 working, and I'll probably qpkg it, so I can get back quickly. I'll also probably both dump and "cp -a" save the database.
But I'd really like to experiment more with "fixing" my database. Maybe it's time to get the python/mysql bindings and start learning. Still, I had things pretty well emptied before even trying this - I'd just like to try a little more at preserving my old stuff.
--- edit ---
Just checked my dumps. When you run mythtv-setup it makes a backup of your old database. This was the result of my "partial restore". I pulled it over to /tmp, gunzipped it, and started looking. At the very least, there's content in there. Then I did a dump of the new 0.22 database, and it's empty of old content - as if I'd completely started over. No "oldrecorded", no "recorded", a smaller "people" that's likely the result of the first pull from SchedulesDirect.
Time to qpkg, save the database, and head back to 0.21 and python/mysql info. I also want to head to the list, to understand why it threw out my whole database after the partial restore. At this point, I can probably set things up to swap back and forth fairly quickly.
_________________
.sigs waste space and bandwidth |
|
| Back to top |
|
|
tld
Veteran
Joined: 09 Dec 2003
Posts: 1816
|
| Posted: Sun Feb 21, 2010 3:01 pm Post subject: |
|
|
| depontius wrote: | Latest status... still toasty
I did a "partial restore", which should have left my previously recorded programs available - but it didn't. I've effectively started completely from scratch. It's fetching SchedulesDirect right now, and I'm going to exercise things a little. I'm alsogoing to take a look at the "partial restore" database done right before the conversion, and see what the heck was in there. Then I'm going to dump the post-conversion database and see what is in that.
|
Just to be clear...did you start with an empty 0.21 database and do the partial restore using your 0.21 backup? If so that certainly should have worked.
As far as attempting another fix of the original database...you could try manually updating that people table record in the original database and then try rerunning my update script...it can be run multiple times. You could find the record by using:
| Code: | | select * from people where name like 'frank h%'; |
...or something like that. You'll see the record involved and the 'person' column (it's id), so you can update it manually to whatever you want with:
| Code: | | update people set name='<whatever>' where person=nnn |
...where nnn is the person id column value.
By the way...if you're trying to do this with the oldprogram table intact there's almost no chance it'll get past that. That's why I just truncated mine. Also, if you're trying to fix your people table as-is there's going to be a lot that it needs to try and fix and more chances of conflicts. That's why I chose to get rid of any unreferenced records in my people table.
It's surprising that you got a key conflict there...that sounds as though you already had two records for that person with differing types of corruption...that would cause my script to attempt to rename them both to the same net name.
Tom |
|
| Back to top |
|
|
depontius
Advocate
Joined: 05 May 2004
Posts: 3509
|
| Posted: Sun Feb 21, 2010 4:08 pm Post subject: |
|
|
Fixed.
I just went back and re-ran the upgrade, and re-did the partial database restore. I believe I found the problem with the last time I tried, though I don't understand exactly why what I did produced the results that it did.
The partial restore instructions have you drop the database, recreate the database, restore the blank 1214 snapshot, and restore your own backup. The problem was that those instructions didn't have the "--partial_restore" flag on the command that restored my old backup. I can easily see that being wrong, though I don't understand why it created a full-sized database. On my first upgrade attempt, the pre-upgrade database backup was approximately "full-sized". On this upgrade attempt, the pre-upgrade database was approximately "half-sized" and my first post-upgrade database backup is approximately "2/3-sized".
I'm done. I'm not going to tempt fate any longer. Time to move the other machines up to 0.22, then to go back and fudge up some sort of nuvexport installation, since there isn't any nuvexport in portage to go with 0.22.
I do need to go back on the mythtv list to explain what happened. I also need to go back on bugzilla and make sure that the partial-restore instructions are properly corrected.
_________________
.sigs waste space and bandwidth |
|
| Back to top |
|
|
yngwin
Retired Dev
Joined: 19 Dec 2002
Posts: 4572
Location: Suzhou, China
|
|
| Back to top |
|
|
dnm
n00b
Joined: 07 Oct 2002
Posts: 58
Location: Stockholm/Sweden
|
| Posted: Fri Feb 26, 2010 12:56 am Post subject: my machine does more than mythtv |
|
|
| Wow, I am definitively holding off upgrading mythtv to 0.22 for as long as possible. I am a longtime user and I have partial corruption (sigh). I also use other databases than mythconverg, so I am also thinking that changing a "misconfigured server" /etc/mysql/my.conf (as explained by the Fixing Corrupt Database Encoding) might not be a good idea. Also please wait with removing qt3 and mythtv 0.21 until there is a clear guide, reading up on it at this moment is really making my head hurt. Every case/how-to is just ignoring the fact that there might be more than mythtv's database. And what little text there is about that, is so vague, that it does not enlighten. |
|
| Back to top |
|
|
depontius
Advocate
Joined: 05 May 2004
Posts: 3509
|
| Posted: Fri Feb 26, 2010 1:37 am Post subject: |
|
|
I wouldn't count on them holding off on qt-3 very long. The big push for mythtv-0.22 is really the push to get qt-3 out of portage.
Just back up your qt-3 - back up the source file, back up the ebuild - same with mythtv-0.21, if your so inclined. Make your own portage overlay (look for PORTDIR_OVERLAY) and put the mythtv-0.21 and qt-3 in it, in the appropriate categories, make sure you do run "ebuild ... digest" against them. Either make sure you have the source files, or run "emerge -f =x11-libs/qt-3### =media-tv/mythtv-0.21###" to get the source files - then mark them readonly, if not "chatter +i" so they don't get accidentally erased.
At this point, you should be able to rebuild, if anything goes wrong - from your own private overlay.
I was really worried about the upgrade - and it did take me 2 tries. Look earlier on this thread for my experiences and "partial corruption upgrade guide". It's also worth mentioning that once you're at mythtv-0.22, the character set configuration of the database won't matter any more. So actually right now you're more brittle. Once you're able to get to mythtv-0.22, you'll be better off.
I will also mention that I did my backups, upgraded, failed to upgrade the database, and went back to mythtv-0.21. On my second try I succeeded, and the key was adding the "--partial_restore" flag to the published instructions when I loaded my database over the "blank" database.
For your multiple database situation, I'd quiesce everything else while you're doing the mythtv upgrade. As I said, once you're done with the upgrade, the default character set won't matter any more, so you can put it all back to where you started. Then restart your other applications. While I had mysqldumps, my primary backup method was to stop all db applications, stop mysql, and use "cp -a" against "/var/lib/mysql". I just copied/moved everything at once.
_________________
.sigs waste space and bandwidth |
|
| Back to top |
|
|
yngwin
Retired Dev
Joined: 19 Dec 2002
Posts: 4572
Location: Suzhou, China
|
| Posted: Fri Feb 26, 2010 12:14 pm Post subject: |
|
|
It looks like the news item will be posted on Monday, which will mean the stabilization can go ahead immediately. Qt3 will be masked along with everything that depends on it the same day. Users who want to hang on to it can use the kde-sunset overlay.
_________________
"Those who deny freedom to others deserve it not for themselves." - Abraham Lincoln
Free Culture | Defective by Design | EFF |
|
| Back to top |
|
|
|
|
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 vote in polls in this forum
|
|
Multimedia
