MySQL Explain Plan or View Execution Plan in SQL PI fails with an error message (4374576)

When Explain Plan is used on some of the collected SQL Statements for a MySQL server, the UI generates an error message like:

• #42000
• You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '* ) FROM `rental` WHERE `return_date` IS NOT NULL' at line 2
• You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '? WHERE `actor_id` = ?' at line 4
• You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 37

MySQLAgent is collecting data normally and the login has permission to run EXPLAIN command.

There are 3 scenarios that cause these issues, these examples were generated with the sakila sample database for MySQL, but the issues themselves can happen in any database or schema.

Scenario 1: Normalized SQL Statements have a space after the function names

MySQLAgent collects Normalized SQL Statement text from the MySQL server and this normalization process reformats the SQL statement text.

-- example query doesn't have the space between function name and opening parenthesis
SELECT title, COUNT(*) AS rental_count FROM rental r JOIN inventory i ON r.inventory_id = i.inventory_id JOIN film f ON i.film_id = f.film_id GROUP BY title ORDER BY rental_count DESC;

This is discovered in MySQL Statements tab dashboard with the text as:

SELECT `title` , COUNT ( * ) AS `rental_count` FROM `rental` `r` JOIN `inventory` `i` ON `r` . `inventory_id` = `i` . `inventory_id` JOIN `film` `f` ON `i` . `film_id` = `f` . `film_id` GROUP BY `title` ORDER BY `rental_count` DESC

Matching the digest_text in performance_schema.events_statements_summary_by_digest

SELECT DIGEST_TEXT FROM performance_schema.events_statements_summary_by_digest WHERE DIGEST = '6f5e6f3b745a3f4c9d8ed867e45f8da417cf325c7937da199d0a60d99e720be9';

-- Output: SELECT `title` , COUNT ( * ) AS `rental_count` FROM `rental` `r` JOIN `inventory` `i` ON `r` . `inventory_id` = `i` . `inventory_id` JOIN `film` `f` ON `i` . `film_id` = `f` . `film_id` GROUP BY `title` ORDER BY `rental_count` DESC

From MySQL Workbench, the same Explain statement fails:

EXPLAIN SELECT `title` , COUNT ( * ) AS `rental_count` FROM `rental` `r` JOIN `inventory` `i` ON `r` . `inventory_id` = `i` . `inventory_id` JOIN `film` `f` ON `i` . `film_id` = `f` . `film_id` GROUP BY `title` ORDER BY `rental_count` DESC;

-- Output: Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '* ) AS `rental_count` FROM `rental` `r` JOIN `inventory` `i` ON `r` . `inventory' at line 1

Scenario 2: Normalized SQL Statements replace literals with placeholder question marks (?)

Same as Scenario 1, the SQL Statement normalization process replaces literals with questions marks.

-- example has literals
SELECT * FROM actor WHERE last_name LIKE 'S%' LIMIT 5;

This is discovered in MySQL Statements tab dashboard with the text as:

SELECT * FROM `actor` WHERE `last_name` LIKE ? LIMIT ?

Matching the ;digest_text in performance_schema.events_statements_summary_by_digest

SELECT DIGEST_TEXT FROM performance_schema.events_statements_summary_by_digest WHERE DIGEST = '3b609c49b6471e43f1d3bd10a2246c8da64daae2a39a38761db6fabc1944813f';

-- Output: SELECT * FROM `actor` WHERE `last_name` LIKE ? LIMIT ?

Explain plan from Foglight and from MySQL Workbench return the same result:

EXPLAIN SELECT * FROM `actor` WHERE `last_name` LIKE ? LIMIT ?;

-- Output: Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '? LIMIT ?' at line 1

Scenario 3: Normalized SQL Statements was truncated due to agent limits and/or MySQL server limits.

A very long SQL statement can exceed default 1024 characters limit in MySQL server defined in performance_schema_max_sql_text_length; or exceed the default 1000 characters limit in the MySQLAgent property Statement Text Length.

These errors are less predictable since it changes depending on the point of truncation.

Scenario 1: Spaces after function names

WORKAROUND

Server SQL modes can potentially be changed to include IGNORE_SPACE in the my.cnf or my.ini file.

Note: Use with caution since there are some Function Resolution considerations when setting sql_mode to IGNORE_SPACE as explained in the documentation

Scenario 2: Literals replaced with placeholders

WORKAROUND

None

STATUS

Enhancement request FOGMYSQL-167 has been submitted to Development for consideration in a future version of Foglight for MySQL

Scenario 3: Statements truncated

WORKAROUND

Increase the statement size

In the MySQL server, edit my.cnf or my.ini file and change the value for performance_schema_max_sql_text_length. This requires the MySQL service to be restarted.

In the MySQLAgent properties, change the value for Statement Text Length.