MySQL Table Repair

  Thread Rating:
  • 0 Votes - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Post Reply
bleze Offline
Senior Member
Posts: 214
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: 25,688
Joined: Oct 2003
Reputation: 169
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: 214
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
johnreinagan Offline
Junior Member
Posts: 1
Joined: Mar 2014
Reputation: 0
Post: #4
Backup your table (just copy your table files to somewhere else). Than execute "repair table yourtablename". Mostly table corruptions are on index files and easyly recoverable. Unexpected shutdowns or disk space problems may have results like this.

In case first proposition didn't help you. Use repair & restore your corrupt MySQL database by MySQL Repair Toolbox. It is read only in nature and successful restore table corruption in MySQL database. You can download free demo version to see the preview of your corrupt database http://www.mysql.repairtoolbox.com/
find quote