If you’re getting an error that reads “This version of MySQL doesn’t yet support ‘EXPLAIN ANALYZE with JSON format’” in MySQL, it’s probably because you’re trying to run EXPLAIN ANALYZE with the wrong output format.

The EXPLAIN ANALYZE statement only supports the TREE output format. However, there are a few things to be aware of regarding this.

Either way, the quickest way to fix the error is to use FORMAT=TREE in your EXPLAIN ANALYZE statement.

Example of Error

Here’s an example of code that produces the error:

EXPLAIN ANALYZE FORMAT=JSON
SELECT * FROM valbumsartists;

Result:

ERROR 1235 (42000): This version of MySQL doesn't yet support 'EXPLAIN ANALYZE with JSON format'

This error happened because I explicitly set FORMAT to a non-supported format (JSON).

We get the same error if we try to set it to TRADITIONAL:

EXPLAIN ANALYZE FORMAT=TRADITIONAL
SELECT * FROM valbumsartists;

Result:

ERROR 1235 (42000): This version of MySQL doesn't yet support 'EXPLAIN ANALYZE with TRADITIONAL format'

The error can still happen even if we don’t explicitly set FORMAT to a non-supported format.

As of MySQL 8.0.32, if we don’t explicitly set FORMAT, then EXPLAIN ANALYZE takes its value from our explain_format system variable (which was introduced in MySQL 8.0.32). This may or may not cause issues, depending on the value of that variable. Basically, it will cause issues if it’s set to JSON. But if it’s set to TREE, TRADITIONAL, or DEFAULT, then EXPLAIN ANALYZE will use TREE.

To demonstrate, let’s set explain_format to JSON and run EXPLAIN ANALYZE without explicitly setting the format:

SET @@explain_format=JSON;

And now run EXPLAIN ANALYZE again:

EXPLAIN ANALYZE
SELECT * FROM valbumsartists;

Result:

ERROR 1235 (42000): This version of MySQL doesn't yet support 'EXPLAIN ANALYZE with JSON format'

We got the same error because EXPLAIN ANALYZE took its format from our explain_format variable, which specifies a value of that isn’t supported.

Solution 1

The quickest and easiest solution is to explicitly set the output format to TREE in our EXPLAIN ANALYZE statement. That’s because the EXPLAIN ANALYZE statement only supports TREE as the output format, and setting it explicitly overrides our explain_format system variable.

EXPLAIN ANALYZE FORMAT=TREE
SELECT * FROM valbumsartists;

Result:

-> Nested loop inner join  (cost=16.2 rows=20) (actual time=0.0964..0.266 rows=20 loops=1)
    -> Nested loop inner join  (cost=9.25 rows=20) (actual time=0.0867..0.149 rows=20 loops=1)
        -> Filter: (albums.GenreId is not null)  (cost=2.25 rows=20) (actual time=0.0617..0.0772 rows=20 loops=1)
            -> Table scan on albums  (cost=2.25 rows=20) (actual time=0.0602..0.0735 rows=20 loops=1)
        -> Single-row covering index lookup on genres using PRIMARY (GenreId=albums.GenreId)  (cost=0.255 rows=1) (actual time=0.00195..0.00326 rows=1 loops=20)
    -> Single-row index lookup on artists using PRIMARY (ArtistId=albums.ArtistId)  (cost=0.255 rows=1) (actual time=0.00554..0.00558 rows=1 loops=20)

This time it worked, even though my explain_format system variable is still set to JSON. It worked because using FORMAT=TREE overrode my explain_format system variable.

Solution 2

If you don’t want to explicitly set the format in the EXPLAIN ANALYZE statement, then you will need to make sure your explain_format system variable is set to either TREE, TRADITIONAL, or DEFAULT. This is because of the following rule:

• If the explain_format system variable is set to TRADITIONAL, DEFAULT or TREE, then TREE is used.
• If the explain_format system variable is JSON, then an error is returned, unless FORMAT=TREE is specified as part of the statement.

So even though we got an error when we explicitly used FORMAT=TRADITIONAL, MySQL makes an allowance when our explain_format is set to TRADITIONAL.

Anyway, let’s change our explain_format variable to one of the supported formats:

SET @@explain_format=TRADITIONAL;

Now let’s run EXPLAIN ANALYZE again (without explicitly specifying a format):

EXPLAIN ANALYZE
SELECT * FROM valbumsartists;

Result:

-> Nested loop inner join  (cost=16.2 rows=20) (actual time=0.129..0.2 rows=20 loops=1)
    -> Nested loop inner join  (cost=9.25 rows=20) (actual time=0.117..0.157 rows=20 loops=1)
        -> Filter: (albums.GenreId is not null)  (cost=2.25 rows=20) (actual time=0.0881..0.105 rows=20 loops=1)
            -> Table scan on albums  (cost=2.25 rows=20) (actual time=0.0868..0.101 rows=20 loops=1)
        -> Single-row covering index lookup on genres using PRIMARY (GenreId=albums.GenreId)  (cost=0.255 rows=1) (actual time=0.00217..0.00224 rows=1 loops=20)
    -> Single-row index lookup on artists using PRIMARY (ArtistId=albums.ArtistId)  (cost=0.255 rows=1) (actual time=0.00178..0.00184 rows=1 loops=20)

This time it ran without error.