MySQL Bugs: #60970: Inconsistent reporting of InnoDB availability in SHOW VARIABLES and SHOW ENGINES

Bug #60970	Inconsistent reporting of InnoDB availability in SHOW VARIABLES and SHOW ENGINES
Submitted:	25 Apr 2011 8:24	Modified:	25 Apr 2011 10:50
Reporter:	Peter Laursen (Basic Quality Contributor)	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.5.10	OS:	Windows (probably any)
Assigned to:		CPU Architecture:	Any
Tags:	qc

Description:
1) Start server with --default-storage-engine=myisam.  It has no effect.  InnoDB is still default what also SHOW ENGINES tells.

2) start server with --default-storage-engine=myisam --skip inno-db.  Server refuses to start. 

Docs at http://dev.mysql.com/doc/refman/5.5/en/server-options.html#option_mysqld_default-storage-e... says
"--skip-innodb 

Disable the InnoDB storage engine. In this case, the server will not start if the default storage engine is set to InnoDB. Use --default-storage-engine to set the default to some other engine if necessary."

This is what I am doing with case 2.

(I also noticed that in some earlier 5.5.x version InnoDB could in fact be disabled just like in 5.0/5.1 but "SHOW VARIABLES LIKE 'have_innodb'" still returned YES (and not DISABLED))

How to repeat:
see above

Suggested fix:
If InnoDB cannot be disabled anymore 8what could be 'policy') and not even disabled as the default engine docs should be updated accordingly. 

This is "S2" because users are not probably informed in docs that upgrade to a recent 5.5.x may not be possible if they have applications requiring InnoDB to be disabled or disabled as default engine.

For me everything works as expected and documented in current mysql-5.5 on Mac:

macbook-pro:5.5 openxs$ bin/mysqld_safe --skip-innodb --default-storage-engine=MyISAM &
[1] 19914
macbook-pro:5.5 openxs$ 110425 11:35:55 mysqld_safe Logging to '/Users/openxs/dbs/5.5/data/macbook-pro.err'.
chown: /Users/openxs/dbs/5.5/data/macbook-pro.err: Operation not permitted
110425 11:35:55 mysqld_safe Starting mysqld daemon with databases from /Users/openxs/dbs/5.5/data

macbook-pro:5.5 openxs$ bin/mysql -uroot testReading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.13-debug Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show variables like 'default%';
+------------------------+--------+
| Variable_name          | Value  |
+------------------------+--------+
| default_storage_engine | MyISAM |
| default_week_format    | 0      |
+------------------------+--------+
2 rows in set (0.01 sec)

mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| MyISAM             | DEFAULT | MyISAM storage engine                                          | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| InnoDB             | NO      | Supports transactions, row-level locking, and foreign keys     | NULL         | NULL | NULL       |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)

mysql> create table tmi(c1 int);
Query OK, 0 rows affected (0.06 sec)

mysql> show create table tmi\G
*************************** 1. row ***************************
       Table: tmi
Create Table: CREATE TABLE `tmi` (
  `c1` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

So, please, be more specific about the case you described.

Configuratio reads

[mysqld]
port=3307
lower_case_table_names = 2
default-storage-engine=myisam
skip-innodb
...

C:\Users\Peter>net start mysql55
Tjenesten MySQL55 starter.
Tjenesten MySQL55 kunne ikke startes.
Der opstod en systemfejl.
Systemfejlen 1067 opstod.
Processen sluttede uventet.

C:\Users\Peter>

@Valeriy: could you please also try "SHOW VARIABLES LIKE 'have_innoDB'" on your environment?

I preferably not to upgrade to 5.5.11 now due to another bug.

From the same environment:

mysql> show variables like 'have_in%';
+---------------+----------+
| Variable_name | Value    |
+---------------+----------+
| have_innodb   | DISABLED |
+---------------+----------+
1 row in set (0.00 sec)

mysql> select version();
+--------------+
| version()    |
+--------------+
| 5.5.13-debug |
+--------------+
1 row in set (0.00 sec)

Please, try to put skip-innodb option first, before default-storage-engine in my.ini. 

I can not test on Windows right now, but will check on official 5.5.11 tomorrow.

Reversing the commands make no difference.

With this file original issue is reproducible

Attachment: my.ini (application/octet-stream, text), 5.03 KiB.

SELECT VERSION(); -- 5.5.10-log
SHOW VARIABLES LIKE 'have_innodb'; -- YES
SHOW ENGINES;
/* ... 
excerpt

InnoDB              NO       
... */

With 5.5.9, 5.5.10 and 5.5.11 (both 32 bit and 64 bit) on a wide variety of Windows systems (XP, 7, 2008).

Original issue is that "SHOW VARIABLES LIKE 'have_innodb'" returns wrong (YES and not DISABLED.  This is originally a customer report and causes monitoring tools to behave wildly incorrectly.

The refusal to start with another configuration is what I enocuntered while trying to reproduce the original issue.  Want this configuration too?

I have changed the synopsis.  

Let us concentrate on this ("SHOW VARIABLES LIKE 'have_innodb'" is incorrect) here.  I will post a new report about the other issue.

With current synopsis it is a duplicate of Bug #59393.