MySQL Table Repair - Printable Version

MySQL Table Repair - Printable Version

+- XBMC Community Forum (http://forum.xbmc.org)
+-- Forum: Development (/forumdisplay.php?fid=32)
+--- Forum: Feature Suggestions (/forumdisplay.php?fid=9)
+--- Thread: MySQL Table Repair (/showthread.php?tid=124210)

MySQL Table Repair - bleze - 2012-02-28 23:45

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.

- jmarshall - 2012-02-29 00:22

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?

- bleze - 2012-02-29 00:25

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.