MySQL Bugs: #112640: Visual Explain fails with error in logs

Bug #112640	Visual Explain fails with error in logs
Submitted:	5 Oct 2023 16:38	Modified:	23 Sep 14:08
Reporter:	Adam Bailey	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Workbench: SQL Editor	Severity:	S3 (Non-critical)
Version:	8.0.34	OS:	Windows
Assigned to:		CPU Architecture:	Any

Description:
Working with Sakila database on a local 8.1.0 Community server.

When choosing Visual Explain the dropdown box reverts back to Tabular Explain and the following error appears in the log file:

[ERR][wb_query_analysis_grt.py:__init__:455]: No JSON data for explain

I have tried SET @@explain_format = JSON; to influence the explain format and that breaks both Tabular and Visual explain. 

How to repeat:
Query -> Explain Current Statement on:
SELECT last_name, first_name, ROUND(AVG(length), 0) AS average
FROM actor
INNER JOIN film_actor ON film_actor.actor_id = actor.actor_id
INNER JOIN film ON film_actor.film_id = film.film_id
WHERE title = "ALONE TRIP"
GROUP BY last_name, first_name
ORDER BY average;

Tabular Explain is the only option that shows. 

Suggested fix:
When clicking Visual Explain the following query should be sent to the server.

EXPLAIN FORMAT=JSON
SELECT last_name, first_name, ROUND(AVG(length), 0) AS average
FROM actor
INNER JOIN film_actor ON film_actor.actor_id = actor.actor_id
INNER JOIN film ON film_actor.film_id = film.film_id
WHERE title = "ALONE TRIP"
GROUP BY last_name, first_name
ORDER BY average;

I am able to run this manually, but unable to connect this to the visual explain. That would be another cool feature, to produce a visual explain for any valid JSON explain data.

I wonder if this is because MySQL Workbench does not believe MySQL Server version 8.1.0 is supported, as I received a warning when trying to edit the options file.

Hello Adam,

Thank you for the bug report.
Imho this is duplicate of Bug #97595, please see Bug #97595.

Regards,
Ashwini Patil

I am experiencing a different problem than the bug you referenced. 

In that bug, the user is able to choose Visual Explain and the MySQL Workbench attempts to render a result, albeit, incorrectly. 

When I try to choose Visual Explain, the selection returns back to Tabular Explain and refuses to even try Visual Explain because of the error listed in the log file.

Hello Adam,

Could you please check with WB 8.0.38? I quickly ran your query and able to see the visual explain ( checked MySQL Workbench Community (GPL) for macOS version 8.0.38 CE build 4270059 (64 bit) on macOS 14.x Sonoma arm64). Thank you.

Related - Bug #116152

regards,
Umesh

Having the WB the same version or higher than the Server should work just fine. I am at 8.0.36 WB/Server and it works OK. I do not have permissions to install a newer version. 

The problem appears to be related to WB must identify an official Server release to allow the Visual Explain Plan; having a newer server with an older client will cause issues.