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