Bug #47452 small inconsistency with --skip-innodb
Submitted: 19 Sep 2009 11:41 Modified: 21 Sep 2009 7:18
Reporter: Peter Laursen (Basic Quality Contributor) Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.1.40 snapshot OS:Windows (probably any)
Assigned to: CPU Architecture:Any
Tags: qc

[19 Sep 2009 11:41] Peter Laursen
Description:
SHOW ENGINES / SELECT FROM I_S.ENGINES returns "NO" of InnoBD if InnoDB was disabled with --skip-innodb (5.1.40 snapshot). 5.0.85 returns "DISABLED".

Due to problem reported here: 
http://bugs.mysql.com/?id=47447
.. I cannot see what 5.1.38 does. Maybe this was introduced with the plugin?

How to repeat:
MySQL 5.0.85
============;

SHOW ENGINES;
/* 
...
InnoDB      DISABLED  Supports transactions ....
...
*/
SHOW VARIABLES LIKE 'have_innodb';
/*
Variable_name  Value   
-------------  --------
have_innodb    DISABLED
*/

MySQL 5.1.40 SNAPSHOT
======================;

SHOW ENGINES;
/* 
...
InnoDB      NO       Supports transactions ...
...
*/
SHOW VARIABLES LIKE 'have_innodb';
/*
Variable_name  Value   
-------------  --------
have_innodb    DISABLED
*/

Suggested fix:
I think "DISABLED" should be returned instead of "NO". To my knowledge "NO" means that server was not compiled with InnoDB.
[19 Sep 2009 11:44] Peter Laursen
oops .. forgot to mention that server is started with --no-defaults --skip-innodb from command lime.
[19 Sep 2009 12:46] Peter Laursen
It seems there is more than that.  Looks like 'skip-innodb' is now non-functional when specified in coniguration with both 5.1.38 and 5.1.39 from ftp://mirrors.dotsrc.org/mysql/Downloads/MySQL-5.1/
(maybe not officially released byt file ftp://mirrors.dotsrc.org/mysql/Downloads/MySQL-5.1/mysql-5.1.39-winx64.msi exists). Isn't this supposed to work from configuration?

In configuration

[mysqld]
skip-innodb
..

C:\Program Files\MySQL\MySQL Server 5.1\bin>net start mysql51
Tjenesten MySQL51 starter.
Tjenesten MySQL51 kunne ikke startes.

Der opstod en systemfejl.

Systemfejlen 1067 opstod.

Processen sluttede uventet.

C:\Program Files\MySQL\MySQL Server 5.1\bin>

(the Windows error 1067 is failure to start service). If I remove 'skip-innodb' from configuration the service starts.
[19 Sep 2009 13:02] Peter Laursen
I am not sure if last post should have been a seperate report. Sometimes it is not easy for an 'outsider' to judge.
[19 Sep 2009 16:00] Peter Laursen
After being able to start 5.1.38/5.1.39 (this is 5.1.39 from FTP-mirror):

C:\Program Files\MySQL\MySQL Server 5.1\bin>mysqld --no-defaults --datadir="c:\p
rogramdata\mysql\mysql server 5.1\data

SHOW ENGINES;
/*
Engine      Support  Comment                                                         Transactions  XA      Savepoints
----------  -------  --------------------------------------------------------------  ------------  ------  ----------
MyISAM      DEFAULT  Default engine as of MySQL 3.23 with great performance          NO            NO      NO        
CSV         YES      CSV storage engine                                              NO            NO      NO        
MRG_MYISAM  YES      Collection of identical MyISAM tables                           NO            NO      NO        
BLACKHOLE   YES      /dev/null storage engine (anything you write to it disappears)  NO            NO      NO        
FEDERATED   NO       Federated MySQL storage engine                                  (NULL)        (NULL)  (NULL)    
MEMORY      YES      Hash based, stored in memory, useful for temporary tables       NO            NO      NO        
ARCHIVE     YES      Archive storage engine                                          NO            NO      NO   
*/ 

SELECT * FROM information_Schema.engines;

/*
ENGINE      SUPPORT  COMMENT                                                         TRANSACTIONS  XA      SAVEPOINTS
----------  -------  --------------------------------------------------------------  ------------  ------  ----------
MyISAM      DEFAULT  Default engine as of MySQL 3.23 with great performance          NO            NO      NO        
CSV         YES      CSV storage engine                                              NO            NO      NO        
MRG_MYISAM  YES      Collection of identical MyISAM tables                           NO            NO      NO        
BLACKHOLE   YES      /dev/null storage engine (anything you write to it disappears)  NO            NO      NO        
FEDERATED   NO       Federated MySQL storage engine                                  (NULL)        (NULL)  (NULL)    
MEMORY      YES      Hash based, stored in memory, useful for temporary tables       NO            NO      NO        
ARCHIVE     YES      Archive storage engine                                          NO            NO      NO   
*/

.. so here it is neĆ­ther "NO" nor "DISABLED".  InnoDB does not appear in the output at all.
[19 Sep 2009 16:06] Peter Laursen
this should have been included in my last post:

SELECT VERSION();
/*
version()       
----------------
5.1.39-community
*/

However this is not related to '--skip-innodb' because that option was not used. But my judgement is that all are related so much there there will be one fix for all and a single report is best option.
[19 Sep 2009 16:09] Peter Laursen
but with 

C:\Program Files\MySQL\MySQL Server 5.1\bin>mysqld --no-defaults --skip-innodb -
-datadir="c:\programdata\mysql\mysql server 5.1\data

the line 

InnoDB      NO       Supports transactions, row-level locking, and foreign keys      (NULL)        (NULL)  (NULL)   

appears in SHOW ENGINES output (still 5.1.39).
[19 Sep 2009 16:18] Peter Laursen
hmmm .. a details

http://dev.mysql.com/doc/refman/5.1/en/news-5-1-39.html reads
"Changes in MySQL 5.1.39 (04 September 2009)"

File on FTP mirror 
ftp://mirrors.dotsrc.org/mysql/Downloads/MySQL-5.1/
has timestamp of 15-09-2009

http://dev.mysql.com/downloads/mysql/5.1.html#winx64
.. still links to 5.1.38 ...
[19 Sep 2009 20:03] Peter Laursen
ok .. also here the conflict was skip-innodb and default-engine=innodb that were both there (but I'd expect then that "default-engine = innodb" was ignored if not no_engine_substitution sql_mode was ON - but anyway this is a minor detail).

But 5.1.39 from FTP also returns NO where it should return DISABLED in my understanding (when server is started with 'skip-innodb').
[21 Sep 2009 7:17] Sveta Smirnova
Thank you for the report.

Verified as described.

This is inconsistent with http://dev.mysql.com/doc/refman/5.1/en/show-engines.html:

NO 	The engine is not supported
DISABLED 	The engine is supported but has been disabled
[21 Sep 2009 7:18] Sveta Smirnova
Really this is duplicate of bug #45876