Bug #25728 show engine status doesn't error out with nonexistent engine
Submitted: 20 Jan 2007 0:21 Modified: 20 Jan 2007 6:18
Reporter: Jon Stephens Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.1.15-bk-20070113 OS:Linux (SuSE 10.2/x86_64, SuSE 10.0/x86)
Assigned to: Marc ALFF CPU Architecture:Any

[20 Jan 2007 0:21] Jon Stephens
Description:
When the name of a nonexistent storage engine is used with SHOW ENGINE ... STATUS, the client returns the output from 

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

mysql> show engine jon status\G
*************************** 1. row ***************************
  Type: ndbcluster
  Name: connection
Status: cluster_node_id=8, connected_host=192.168.0.103, connected_port=1186, number_of_data_nodes=4, number_of_ready_data_nodes=4, connect_count=11
*************************** 2. row ***************************
  Type: ndbcluster
  Name: NdbTransaction
Status: created=4, free=4, sizeof=216
*************************** 3. row ***************************
  Type: ndbcluster
  Name: NdbOperation
Status: created=8, free=8, sizeof=660
*************************** 4. row ***************************
  Type: ndbcluster
  Name: NdbIndexScanOperation
Status: created=0, free=0, sizeof=744
*************************** 5. row ***************************
  Type: ndbcluster
  Name: NdbIndexOperation
Status: created=0, free=0, sizeof=664
*************************** 6. row ***************************
  Type: ndbcluster
  Name: NdbRecAttr
Status: created=0, free=0, sizeof=60
*************************** 7. row ***************************
  Type: ndbcluster
  Name: NdbApiSignal
Status: created=16, free=16, sizeof=136
*************************** 8. row ***************************
  Type: ndbcluster
  Name: NdbLabel
Status: created=0, free=0, sizeof=196
*************************** 9. row ***************************
  Type: ndbcluster
  Name: NdbBranch
Status: created=0, free=0, sizeof=24
*************************** 10. row ***************************
  Type: ndbcluster
  Name: NdbSubroutine
Status: created=0, free=0, sizeof=68
*************************** 11. row ***************************
  Type: ndbcluster
  Name: NdbCall
Status: created=0, free=0, sizeof=16
*************************** 12. row ***************************
  Type: ndbcluster
  Name: NdbBlob
Status: created=0, free=0, sizeof=264
*************************** 13. row ***************************
  Type: ndbcluster
  Name: NdbReceiver
Status: created=0, free=0, sizeof=68
*************************** 14. row ***************************
  Type: ndbcluster
  Name: binlog
Status: latest_epoch=0, latest_trans_epoch=241860, latest_received_binlog_epoch=0, latest_handled_binlog_epoch=0, latest_applied_binlog_epoch=0
*************************** 15. row ***************************
  Type: InnoDB
  Name:
Status:
=====================================
070120 10:07:20 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 40 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 0, signal count 0
Mutex spin waits 0, rounds 0, OS waits 0
RW-shared spins 7, OS waits 0; RW-excl spins 0, OS waits 0
------------
TRANSACTIONS
------------
Trx id counter 0 770
Purge done for trx's n:o < 0 0 undo n:o < 0 0
History list length 0
Total number of lock structs in row lock hash table 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 0, not started, process no 4864, OS thread id 1115433904
MySQL thread id 22, query id 305516 localhost root
show engine jon status
---TRANSACTION 0 0, not started, process no 4864, OS thread id 1106049968
MySQL thread id 1, query id 0 Waiting for event from ndbcluster
--------
FILE I/O
--------
I/O thread 0 state: waiting for i/o request (insert buffer thread)
I/O thread 1 state: waiting for i/o request (log thread)
I/O thread 2 state: waiting for i/o request (read thread)
I/O thread 3 state: waiting for i/o request (write thread)
Pending normal aio reads: 0, aio writes: 0,
 ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
0 OS file reads, 38 OS file writes, 16 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2,
0 inserts, 0 merged recs, 0 merges
Hash table size 34679, used cells 0, node heap has 1 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number 0 46409
Log flushed up to   0 46409
Last checkpoint at  0 46409
0 pending log writes, 0 pending chkp writes
11 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 19910438; in additional pool allocated 862080
Dictionary memory allocated 21752
Buffer pool size   512
Free buffers       333
Database pages     178
Modified db pages  0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 0, created 178, written 189
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
1 read views open inside InnoDB
Main thread process no. 4864, id 1104718768, state: waiting for server activity
Number of rows inserted 0, updated 0, deleted 0, read 0
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

15 rows in set, 1 warning (0.00 sec)

mysql> show warnings;
+-------+------+----------------------------+
| Level | Code | Message                    |
+-------+------+----------------------------+
| Error | 1286 | Unknown table engine 'jon' |
+-------+------+----------------------------+
1 row in set (0.00 sec)

How to repeat:
See Description.

Suggested fix:
The statement should fail with an error.

Note: The statement works correctly in this regard in 5.0.28-enterprise.
[20 Jan 2007 1:45] Jon Stephens
Sorry! The first paragraph in the Description should read:

When the name of a nonexistent storage engine is used with SHOW ENGINE ...
STATUS, the client returns the output that you would expect from running both SHOW ENGINE NDB STATUS and SHOW ENGINE INNODB STATUS.
[20 Jan 2007 1:46] Jon Stephens
See also Bug#25719 and Bug#24392 -- possibly related to one or both of these.
[20 Jan 2007 6:18] Marc ALFF
The root cause of this report traces back to Bug#24392

When the engine name is unknown, and when SQL_MODE MODE_NO_ENGINE_SUBSTITUTION
is not set :
- a (malformed) warning is raised (which is a bug)
- Lex->create_info.db_type is set to NULL (which is a bug)
This cause the code to later implement the command :
SHOW ENGINE ALL STATUS,
displaying the status of all engines.

Closing as a duplicate of Bug#24392.