MySQL Table Repair

  Thread Rating:
  • 0 Votes - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Post Reply
bleze Offline
Senior Member
Posts: 215
Joined: Nov 2009
Reputation: 0
Post: #1
When upgrading from Beta2 to Beta3/RC1 XBMC crashed when migrating the MySQL database I use.
First I thought it was a bug in the new source code, but when RC1 also crashed I set out to locate my problem.

What I found while debugging XBMC source code, was that it crashed when copying actorlinkmovie table from 58 database.

I could see that it had copied 23,587 of the 38,671 rows, so I wanted to find the row it crashed on, so I tried this:

Code:
SELECT *
FROM xbmc_video58.actorlinkmovie
WHERE idActor NOT
IN (
SELECT idActor
FROM xbmc_video60.actorlinkmovie
)

This gave me an interresting error message:

Quote:#1194 - Table 'actorlinkmovie' is marked as crashed and should be repaired

Googling this I found the REPAIR TABLE command and tried that;

Code:
REPAIR TABLE xbmc_video58.actorlinkmovie

This fixed my table dropping 2 rows it seems:

Quote:Table Op Msg_type Msg_text
xbmc_video58.actorlinkmovie repair info Key 1 - Found wrong stored record at 882592
xbmc_video58.actorlinkmovie repair warning Number of rows changed from 38671 to 38669
xbmc_video58.actorlinkmovie repair status OK

Now XBMC crashed in MysqlDatabase::copy for me.

Code:
...
      // copy the table data
      sprintf(sql, "INSERT INTO %s.%s SELECT * FROM %s",
              backup_name, row[0], row[0]);

      if ( (ret=query_with_reconnect(sql)) != MYSQL_OK )  <- crashed here
        throw DbErrors("Can't copy data for table '%s'\nError: %s", db.c_str(), ret);

My suggestion is that perhaps XBMC could either log something more user friendly than 'db' which is actually the database name and not the table 'row[0]' having the problem Smile
Or it could, in case of problem with INSERT INTO, try a REPAIR TABLE of the old table and then another INSERT INTO before actually failing. (Of course a DELETE of already copied rows must be there too). I understand repairing tables and thereby losing rows, might end up with a crippled database which does not work after all. Might be better? I don't know since I have no experience with MySQL or these corrupt tables.

I'm "lucky" since I'm a developer myself, I could figure out how to debug and locate my actual problem and save my database. A normal user would not stand a chance, and would probably end up deleting database, xbmc settings and start over.
find quote
jmarshall Offline
Team-XBMC Developer
Posts: 26,228
Joined: Oct 2003
Reputation: 177
Post: #2
I think the least we can do is fix the log message Smile

I don't think we'll gain much by attempting to repair.

I'm interested though in why you think it screwed up in the first place - anything out of the ordinary?

Always read the XBMC online-manual, FAQ and search the forum before posting.
Do not e-mail XBMC-Team members directly asking for support. Read/follow the forum rules.
For troubleshooting and bug reporting please make sure you read this first.


[Image: badge.gif]
find quote
bleze Offline
Senior Member
Posts: 215
Joined: Nov 2009
Reputation: 0
Post: #3
In my case the repair fixed the database. I understand why you don't want such a feature.

I guess it has happened during beta2 period. Well I exit xbmc the hard way some times, and my server is set to turn off at 1 in the night. Perhaps this have caused it to crash if something was going on at the time.
find quote