Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm just reading a book and in one of the examples I get notices:

  • That the engine in the information_schema.table is null, how is that possible?
  • Can I create tables without any engine??, is there any pro in that?enter image description here
share|improve this question
1  
I'n not an expert in MySQL but the objects that have a "NULL engine" are all views. So maybe a view doesn't have an engine because it is not a table and it might not make sense to even discuss the "engine for a view". –  FrustratedWithFormsDesigner Apr 26 '12 at 17:12
    
I think you are right, I didn't see the relation between the "base table" and view. I learned something new today –  jcho360 Apr 26 '12 at 17:21
    
What version of MySQL is this ??? –  RolandoMySQLDBA Apr 26 '12 at 17:24
    
Server version: 5.5.9 Source distribution –  jcho360 Apr 26 '12 at 17:30

2 Answers 2

up vote 3 down vote accepted

I actually wrote a post back in July 2011 ( Modify DEFINER on Many Views ) about how to access views for modification.

It is a fact that when the storage engine is NULL, it is always a View.

The actual definition of the View is

MySQL 5.1/5.5

mysql> desc information_schema.views;
+----------------------+--------------+------+-----+---------+-------+
| Field                | Type         | Null | Key | Default | Extra |
+----------------------+--------------+------+-----+---------+-------+
| TABLE_CATALOG        | varchar(512) | NO   |     |         |       |
| TABLE_SCHEMA         | varchar(64)  | NO   |     |         |       |
| TABLE_NAME           | varchar(64)  | NO   |     |         |       |
| VIEW_DEFINITION      | longtext     | NO   |     | NULL    |       |
| CHECK_OPTION         | varchar(8)   | NO   |     |         |       |
| IS_UPDATABLE         | varchar(3)   | NO   |     |         |       |
| DEFINER              | varchar(77)  | NO   |     |         |       |
| SECURITY_TYPE        | varchar(7)   | NO   |     |         |       |
| CHARACTER_SET_CLIENT | varchar(32)  | NO   |     |         |       |
| COLLATION_CONNECTION | varchar(32)  | NO   |     |         |       |
+----------------------+--------------+------+-----+---------+-------+
10 rows in set (0.02 sec)

MySQL 5.0

mysql> desc information_schema.views;
+-----------------+--------------+------+-----+---------+-------+
| Field           | Type         | Null | Key | Default | Extra |
+-----------------+--------------+------+-----+---------+-------+
| TABLE_CATALOG   | varchar(512) | YES  |     | NULL    |       |
| TABLE_SCHEMA    | varchar(64)  | NO   |     |         |       |
| TABLE_NAME      | varchar(64)  | NO   |     |         |       |
| VIEW_DEFINITION | longtext     | NO   |     | NULL    |       |
| CHECK_OPTION    | varchar(8)   | NO   |     |         |       |
| IS_UPDATABLE    | varchar(3)   | NO   |     |         |       |
| DEFINER         | varchar(77)  | NO   |     |         |       |
| SECURITY_TYPE   | varchar(7)   | NO   |     |         |       |
+-----------------+--------------+------+-----+---------+-------+
8 rows in set (0.00 sec)

To see what the view definition is for sakila.actor_info you can either run

SELECT * FROM information_schema.views
WHERE table_schema='sakila' AND table_name='actor_info'\G

or

SHOW CREATE VIEW sakila.actor_info\G
share|improve this answer

I thought I'd add something interesting. Based on some testing I found that the engine is NULL for MyISAM tables if they are corrupted:

mysql> repair table TEST.test_myisam;
^CCtrl-C -- sending "KILL QUERY 10" to server ...
Ctrl-C -- query aborted.
+---------------------+--------+----------+------------------------------+
| Table               | Op     | Msg_type | Msg_text                     |
+---------------------+--------+----------+------------------------------+
+---------------------+--------+----------+------------------------------+
2 rows in set (0.37 sec)

mysql> select table_type, engine, table_rows from information_schema.tables where table_schema = 'TEST' and table_name = 'test_myisam';
+------------+--------+------------+
| table_type | engine | table_rows |
+------------+--------+------------+
| BASE TABLE | NULL   |       NULL |
+------------+--------+------------+
1 row in set, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+----------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                |
+---------+------+----------------------------------------------------------------------------------------+
| Warning |  144 | Table './TEST/test_myisam' is marked as crashed and last (automatic?) repair failed |
+---------+------+----------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

This was done on 5.6.21.

I also tested corrupting the data file manually with something like

echo "1" >> /path/to/db/TEST/test_myisam.MYD

and then doing:

mysql> check table TEST.test_myisam;
+------------------+-------+----------+---------------------------------------------------------+
| Table            | Op    | Msg_type | Msg_text                                                |
+------------------+-------+----------+---------------------------------------------------------+
| TEST.test_myisam | check | warning  | Size of datafile is: 41943042       Should be: 41943040 |
| TEST.test_myisam | check | error    | got error: 127 when reading datafile at record: 2097152 |
| TEST.test_myisam | check | error    | Corrupt                                                 |
+------------------+-------+----------+---------------------------------------------------------+
3 rows in set (0.28 sec)

which yields the same result:

mysql> select table_type,engine, table_rows from information_schema.tables where table_schema = 'TEST' and table_name = 'test_myisam';
+------------+--------+------------+
| table_type | engine | table_rows |
+------------+--------+------------+
| BASE TABLE | NULL   |       NULL |
+------------+--------+------------+
1 row in set, 1 warning (0.00 sec)
share|improve this answer
    
Very Dangerous and Thought Provoking. +1 for this outside-of-the-box perspective. –  RolandoMySQLDBA Feb 23 at 19:18

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.