MySQL Bugs: #112090: SHOW ENGINE command runs forever when using cursor fetch

Bug #112090	SHOW ENGINE command runs forever when using cursor fetch
Submitted:	16 Aug 2023 16:25	Modified:	19 Oct 12:23
Reporter:	Ulf Weiland	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / J	Severity:	S2 (Serious)
Version:	8.1.0, 8.0.33	OS:	MacOS
Assigned to:		CPU Architecture:	ARM

Description:
Running command show engine `BLACKHOLE` STATUS' runs forever when running using cursor fetch. Performing Statement.cancel does not help. 
Running the same command without cursor fetch runs OK.

The same error occurs for a number of storage engines listed by the "show engines" command. Example ARCHIVE, BLACKHOLE, CSV, MEMORY, MRG_MYISAM, MyISAM.

How to repeat:
The 

Run the attached java class ShowEngineStatusNotWorking with the driver in the classpath. The program issues show engine commands on separate connection.

The program also prints a result like the following. One can see that BLACKHOLE does timeout after waiting 30 seconds.

QUERY: 'show engine `InnoDB` STATUS' cursorFetch:true  Results: 1  Exec time:213ms
QUERY: 'show engine `InnoDB` STATUS' cursorFetch:false  Results: 1  Exec time:16ms
QUERY: 'show engine `BLACKHOLE` STATUS' cursorFetch:true   TimeoutException (30secs)
QUERY: 'show engine `BLACKHOLE` STATUS' cursorFetch:false  Results: none  Exec time:20ms

Versions used 
Database: MySQL 8.1.0
MySQL Connector/J: mysql-connector-j-8.1.0 (Revision: 7b6f9a337afe6ccb41823df485bf848ca7952b09)

Used to reproduce the error

Attachment: ShowEngineStatusNotWorking.java (application/octet-stream, text), 4.19 KiB.

Hello Ulf Weiland,

Thank you for the report and feedback.
Verified as described.

regards,
Umesh

Added the following note to the Connector/J 9.5.0 release notes:

When using cursor fetch with certain storage engines, such as BLACKHOLE, 
the SHOW ENGINE command may not complete.
Errors were returned similar to the following:     
      
        TimeoutException (30secs)