Bug #94483 Execution Plan doesn't work when the connection uses a defaults file
Submitted: 26 Feb 2019 23:11 Modified: 20 Mar 2019 4:17
Reporter: Yoseph Phillips Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Workbench Severity:S3 (Non-critical)
Version:8.0.15 OS:Windows
Assigned to: CPU Architecture:Any
Tags: execution plan, readDefaultFile

[26 Feb 2019 23:11] Yoseph Phillips
Description:
Execution Plan doesn't work when the connection properties are reading from a defaults file.

When we run a complex query and click on the Execution Plan then Workbench pops up with a dialog showing 'Execution Plan' and a message 'An internal error occurred while building the execution plan, please file a bug report.' with an 'OK' button.

Note this is not an issue for small queries.
It looks like joins on 8 tables with a DISTINCT or GROUP BY or joins on 9 tables even without a DISTINCT or GROUP BY are the minimum complexity for this to cause an error.

Likewise when using EXPLAIN FORMAT=JSON then we see Error Code: 2020 
Got packet bigger than 'max_allowed_packet' bytes.	

When we show variables and global variables for max_allowed_packet, both show 1073741824 which is already the maximum.

When we remove the reading from the defaults file then it works again.
All of the variables appear to be identical both with and without reading from a defaults files except for the time_zone one. When we change the time_zone one to make them completely match it makes no difference.

We have tested on multiple computers and get the same error on each of them.

How to repeat:
Create a my.ini file somewhere with the following lines:
[mysql]
init_command = "SET time_zone = SYSTEM" 
no-beep
max_allowed_packet = 1GB

On the connection properties, open the connection tab, then the advanced tab inside of that and add the following lines to the Others section:
readDefaultFile=C:\Program Files\MySQL\MySQL Server 5.7\my.ini
readDefaultGroup=mysql

(Obviously making sure that readDefaultFile points to the my.ini created above)

Run a complex query and then click on Execution Plan.

Alternatively just run EXPLAIN FORMAT=JSON followed by the complex query.
[15 Mar 2019 22:52] MySQL Verification Team
Thank you for the bug report. I could not repeat with my own database and query, so I will appreciate if you could attach here a complete dump file (create table + data insert + query) which is repeatable on your side. Thanks in advance.
[20 Mar 2019 4:17] Yoseph Phillips
The following causes the issue every time for us when the connection is using the defaults file. 
No data required. 
Notice that some of the index names here are very long as making them too short doesn't reproduce the issue.

CREATE TABLE IF NOT EXISTS test_table_1(
  testField1 VARCHAR(191) NOT NULL, 
  testField2 INT NOT NULL,
  testField3 VARCHAR(10000), 
  testField4 FLOAT, 
  testField5 DATETIME, 
  testField6 BOOLEAN,
  PRIMARY KEY (testField1)
);
  
CREATE TABLE IF NOT EXISTS test_table_2(
  testField1 VARCHAR(191) NOT NULL,
  testField2 VARCHAR(191) NOT NULL,
  PRIMARY KEY (testField1, testField2)
);

CREATE TABLE IF NOT EXISTS test_table_3(
  id INT UNSIGNED AUTO_INCREMENT NOT NULL,
  testField1 VARCHAR(191) NOT NULL,
  PRIMARY KEY (id),
  UNIQUE INDEX index1(testField1)
);

CREATE TABLE IF NOT EXISTS test_table_4(
  testField1 int(11) NOT NULL,
  testField2 varchar(191) NOT NULL,
  testField3 int(11) NOT NULL,
  testField4 int(11) NOT NULL,
  testField5 timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  testField6 int(11) DEFAULT NULL,
  testField7 bit(1) DEFAULT NULL,
  testField8 int(11) NOT NULL,
  testField9 int(11) NOT NULL,
  testField10 int(11) DEFAULT NULL,
  PRIMARY KEY (testField1),
  INDEX index2(testField2),
  INDEX index3(testField3),
  INDEX index4(testField4),
  INDEX index5(testField5),
  INDEX index6(testField6),
  INDEX index7(testField8),
  INDEX index8(testField9)
); 
  
CREATE TABLE IF NOT EXISTS test_table_5(
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  testField1 INT(11) NOT NULL,
  testField2 INT(10) UNSIGNED NOT NULL,
  testField3 VARCHAR(10000),
  INDEX index9(testField2),
  INDEX index10(testField1, testField2),
  PRIMARY KEY (id)
);
  
CREATE TABLE IF NOT EXISTS test_table_6(
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  testField1 INT(11) NOT NULL,
  testField2 INT(10) UNSIGNED NOT NULL,
  testField3 float DEFAULT NULL,
  INDEX a_very_long_index_name_1(testField2),
  INDEX a_very_long_index_name_2(testField1, testField2),
  PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS test_table_7(
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  testField1 INT(11) NOT NULL,
  testField2 INT(10) UNSIGNED NOT NULL,
  testField3 DATETIME DEFAULT NULL,
  INDEX an_extremely_long_index_name_which_will_help_to_cause_an_issue_1(testField2),
  INDEX an_extremely_long_index_name_which_will_help_to_cause_an_issue_2(testField1, testField2),
  PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS test_table_8(
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  testField1 INT(11) NOT NULL,
  testField2 INT(10) UNSIGNED NOT NULL,
  testField3 BOOLEAN DEFAULT NULL,
  INDEX an_extremely_long_index_name_which_will_help_to_cause_an_issue_3(testField2),
  INDEX an_extremely_long_index_name_which_will_help_to_cause_an_issue_4(testField1, testField2),
  PRIMARY KEY (id)
);
  
EXPLAIN FORMAT=JSON
SELECT *
FROM test_table_1 t1
INNER JOIN test_table_2 t2 ON t2.testField1 = t1.testField1 
INNER JOIN test_table_3 t3 ON t3.testField1 = t1.testField1
INNER JOIN test_table_4 t4 ON t4.testField2 = t2.testField2 AND t4.testField5 < now()
LEFT JOIN test_table_5 t5 ON t1.testField2 = 1 AND t5.testField1 = t4.testField1 AND t5.testField2 = t3.id
LEFT JOIN test_table_6 t6 ON t1.testField2 = 2 AND t6.testField1 = t4.testField1 AND t6.testField2 = t3.id
LEFT JOIN test_table_7 t7 ON t1.testField2 = 3 AND t7.testField1 = t4.testField1 AND t7.testField2 = t3.id
LEFT JOIN test_table_8 t8 ON t1.testField2 = 4 AND t8.testField1 = t4.testField1 AND t8.testField2 = t3.id
GROUP BY t1.testField1;