List:General Discussion« Previous MessageNext Message »
From:Richard Edward Horner Date:November 28 2007 5:34pm
Subject:Re: Determining Table Storage Engine Type on Crashed Table
View as plain text  
FYI, this did not work :)

Thanks though!

Rich(ard)

On Nov 23, 2007 3:37 AM, Paul McCullagh <paul.mccullagh@stripped> wrote:
> Maybe this will work:
>
> SHOW CREATE TABLE table_name;
>
>
> On Nov 21, 2007, at 9:42 PM, Richard Edward Horner wrote:
>
> > Hey everybody,
> >
> > Hopefully some of you are already enjoying time off. I am not...yet :)
> >
> > Anyway, is there a way to determine what storage engine a table is
> > using if it's crashed?  When it's fine, I can just run:
> >
> > mysql> show table status like 'table_name';
> > +-------------+--------+---------+------------+--------
> > +----------------+-------------+------------------+--------------
> > +-----------+----------------+---------------------
> > +---------------------+---------------------+-------------------
> > +----------+----------------+---------+
> > | Name        | Engine | Version | Row_format | Rows   |
> > Avg_row_length | Data_length | Max_data_length  | Index_length |
> > Data_free | Auto_increment | Create_time         | Update_time
> > | Check_time          | Collation         | Checksum | Create_options
> > | Comment |
> > +-------------+--------+---------+------------+--------
> > +----------------+-------------+------------------+--------------
> > +-----------+----------------+---------------------
> > +---------------------+---------------------+-------------------
> > +----------+----------------+---------+
> > | table_name | MyISAM |      10 | Fixed      | 985984 |             13
> > |    12817792 | 3659174697238527 |     34238464 |         0 |
> > 1182153 | 2007-11-15 17:44:28 | 2007-11-21 15:28:07 | 2007-11-21
> > 15:28:18 | latin1_swedish_ci |     NULL |                |         |
> > +-------------+--------+---------+------------+--------
> > +----------------+-------------+------------------+--------------
> > +-----------+----------------+---------------------
> > +---------------------+---------------------+-------------------
> > +----------+----------------+---------+
> > 1 row in set (0.00 sec)
> >
> > As you can see, the second column returned is the Engine. In this
> > case, MyISAM. Now, if I crash the table, it doesn't work:
> >
> > mysql> show table status like 'table_name';
> > +-------------+--------+---------+------------+------
> > +----------------+-------------+-----------------+--------------
> > +-----------+----------------+-------------+-------------
> > +------------+-----------+----------+----------------
> > +---------------------------------------------------------------------
> > -------+
> > | Name        | Engine | Version | Row_format | Rows | Avg_row_length
> > | Data_length | Max_data_length | Index_length | Data_free |
> > Auto_increment | Create_time | Update_time | Check_time | Collation |
> > Checksum | Create_options | Comment
> >                                 |
> > +-------------+--------+---------+------------+------
> > +----------------+-------------+-----------------+--------------
> > +-----------+----------------+-------------+-------------
> > +------------+-----------+----------+----------------
> > +---------------------------------------------------------------------
> > -------+
> > | table_name | NULL   |    NULL | NULL       | NULL |           NULL |
> >        NULL |            NULL |         NULL |      NULL |
> > NULL | NULL        | NULL        | NULL       | NULL      |     NULL |
> > NULL           | Table './blah/table_name' is marked as crashed and
> > should be repaired |
> > +-------------+--------+---------+------------+------
> > +----------------+-------------+-----------------+--------------
> > +-----------+----------------+-------------+-------------
> > +------------+-----------+----------+----------------
> > +---------------------------------------------------------------------
> > -------+
> > 1 row in set (0.00 sec)
> >
> > Now, let's assume for a moment this were an InnoDB table. If I were to
> > try and run repair, it would say that the storage engine does not
> > support repair so clearly it knows what the storage engine is. How do
> > I get it to tell me? Or I guess a broader more helpful question would
> > be, "What are all the ways to determine a table's storage engine
> > type?"
> >
> > Thanks,
> > --
> > Richard Edward Horner
> > Engineer / Composer / Electric Guitar Virtuoso
> > rich@stripped
> > http://richhorner.com - updated June 28th
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe:    http://lists.mysql.com/mysql?
> > unsub=paul.mccullagh@stripped
> >
>
>



-- 
Richard Edward Horner
Engineer / Composer / Electric Guitar Virtuoso
rich@stripped
http://richhorner.com - updated June 28th
Thread
Determining Table Storage Engine Type on Crashed TableRichard Edward Horner21 Nov
  • Re: Determining Table Storage Engine Type on Crashed TableMicah Stevens22 Nov
    • Re: Determining Table Storage Engine Type on Crashed TableRichard Edward Horner23 Nov
  • Re: Determining Table Storage Engine Type on Crashed TablePaul McCullagh23 Nov
    • Re: Determining Table Storage Engine Type on Crashed TableRichard Edward Horner23 Nov
    • Re: Determining Table Storage Engine Type on Crashed TableRichard Edward Horner28 Nov
      • Re: Determining Table Storage Engine Type on Crashed TableWm Mussatto28 Nov
      • Re: Determining Table Storage Engine Type on Crashed TableAlex Arul Lurthu2 Dec
        • Re: Determining Table Storage Engine Type on Crashed TableTodd Lyons5 Dec