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 <[EMAIL PROTECTED]> 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 [EMAIL PROTECTED] http://richhorner.com - updated June 28th -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
