 | | | Determining Table Storage Engine Type on Crashed Table | Determining Table Storage Engine Type on Crashed Table 2007-12-01 - By Alex Arul Lurthu
Back
How about looking at the .frm file of the table.
On 11/28/07, Richard Edward Horner <rich@(protected)> wrote:
> FYI, this did not work :)
>
> Thanks though!
>
> Rich(ard)
>
> On Nov 23, 2007 3:37 AM, Paul McCullagh <paul.mccullagh@(protected)>
> 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@(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?
> > > unsub=paul.mccullagh@(protected)
> > >
> >
> >
>
>
>
> --
> Richard Edward Horner
> Engineer / Composer / Electric Guitar Virtuoso
> rich@(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?unsub=alex.lurthu@(protected)
>
>
--
Sent from Gmail for mobile | mobile.google.com
Thanks
Alex
http://alexlurthu.wordpress.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=mysql@(protected)
|
|
|