Question 76: How do you repair the table?

Now we create and break a table:

drop table if exists t1;
set @a:=0;
create table t1 (primary key (tid),index(ttype,tdate))
  engine=myisam
  select @a:=@a+1 as tid,rand()*99999999 as tsum, floor(rand()*3) as ttype,
         adddate('2010-01-01',interval rand()*360 day) as tdate
  from information_schema.columns, information_schema.tables limit 40000 ;
create table if not exists break_it like t1;
flush tables;

select concat(@@global.datadir,database()) as dbdir;

 
While keeping the mysql session open, go to the dbdir directory and delete t1.MYI .
Then copy break_it.MYI to t1.MYI .
In the mysql session, issue a “check table t1;”

you should see something like this:

MariaDB [tt]> check table t1;
+-------+-------+----------+-------------------------------------------------+
| Table | Op    | Msg_type | Msg_text                                        |
+-------+-------+----------+-------------------------------------------------+
| tt.t1 | check | warning  | Table is marked as crashed                      |
| tt.t1 | check | warning  | Size of datafile is: 1693848       Should be: 0 |
| tt.t1 | check | error    | Record-count is not ok; is 45360   Should be: 0 |
| tt.t1 | check | warning  | Found 45360 key parts. Should be: 0             |
| tt.t1 | check | error    | Corrupt                                         |
+-------+-------+----------+-------------------------------------------------+
5 rows in set (0.12 sec)

How do you repair it again?

a) fix table t1;
b) alter table t1 engine myisam;
c) repair table t1;
d) optimize table t1;

 
[MyISAM table maintenance - Investigate and repair broken tables]