List:General Discussion« Previous MessageNext Message »
From:Richard Edward Horner Date:November 23 2007 5:31pm
Subject:Re: Determining Table Storage Engine Type on Crashed Table
View as plain text  
Micah,

I don't think this will work in all cases. Both a memory table and a
blackhole table only have an .frm file. Admittedly, we can ignore
blackhole table for practical purposes. But, while we're discussing
practical purposes, an InnoDB table's data is in the main InnoDB
storage file unless you use innodb_file_per_table which I've actually
never seen any of my clients use in any deployment I've worked on.

So, this can work with either some configuration or some prior
knowledge of the scheme (i.e. no memory tables) but I don't think it's
a be all end all or is there more that you know that I don't?

Thanks, Rich(ard)

On Nov 22, 2007 12:43 PM, Micah Stevens <micah@stripped> wrote:
> Look at the data files. The extension of the file will tell you.
>
>
>
> On 11/21/2007 12: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
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