Bug: Mysql Error With Explain

[pest]

I'm sorry that I can't write down the cirteria to reproduce the bug.

Each session it happes at one time, that an MySQL Error window appears with the SQL “EXPLAIN LIMIT 0,1000”. After first error it comes each time. If I click the ok button I can see the result of my SQL.

One of my thoughts was, that the limit 1000 comes from the result tab of limit rows. So I changed to 500 and the error comes up with LIMIT 0,500. After clicking ok the limit was reset to 1000.

The error disappears by switching off: Tools –> Preferences –> Power Tools –> Explain Result

Bye,

pest

[peterlaursen]

1) Please tell the MySQL server version you are using (execute “SELECT VERSION();“)

2) The EXPLAIN statement that SQLyog sends (if it does) can be found in HISTORY tab. Please try to execute this statement from the editor.

[peterlaursen]

I have turned the EXPLAIN option OFF in preferences .. powertools .. query profiler settings. Please see my settings in attached image:

[attachment=1840:settings.jpg]

I have this simple table:

CREATE TABLE `s` (


`a` INT(11) DEFAULT NULL


) ENGINE=INNODB DEFAULT CHARSET=utf8

I execute from the editor:

INSERT INTO s VALUES (1);


SELECT * FROM s;


SELECT * FROM s LIMIT 100;

In HISTORY I have:

/*[14:43:23][2 ms]*/ SHOW VARIABLES LIKE 'profiling';

/*[14:43:23][0 ms]*/ SET PROFILING = 1;

/*[14:43:23][0 ms]*/ SET profiling_history_size = 0;

/*[14:43:23][0 ms]*/ SET profiling_history_size = 15;

/*[14:43:23][6 ms]*/ SHOW STATUS;

/*[14:43:23][3 ms]*/ SHOW STATUS;

/*[14:43:23][5 ms]*/ INSERT INTO s VALUES (1);

/*[14:43:23][3 ms]*/ SHOW STATUS;

/*[14:43:23][0 ms]*/ SHOW PROFILES;

/*[14:43:23][65 ms]*/ SELECT state, ROUND(SUM(duration),5) AS `duration (summed) in sec` FROM information_schema.profiling WHERE query_id = 5 GROUP BY state ORDER BY `duration (summed) in sec` DESC;

/*[14:43:23][0 ms]*/ SET PROFILING = 0;

/*[14:44:55][0 ms]*/ SET PROFILING = 1;

/*[14:44:55][1 ms]*/ SET profiling_history_size = 0;

/*[14:44:55][0 ms]*/ SET profiling_history_size = 15;

/*[14:44:55][4 ms]*/ SHOW STATUS;

/*[14:44:55][1 ms]*/ SELECT * FROM s;

/*[14:44:55][3 ms]*/ SHOW STATUS;

/*[14:44:55][0 ms]*/ SHOW PROFILES;

/*[14:44:55][1 ms]*/ SELECT state, ROUND(SUM(duration),5) AS `duration (summed) in sec` FROM information_schema.profiling WHERE query_id = 11 GROUP BY state ORDER BY `duration (summed) in sec` DESC;

/*[14:44:55][0 ms]*/ SET PROFILING = 0;

/*[14:45:53][0 ms]*/ SET PROFILING = 1;

/*[14:45:53][3 ms]*/ SET profiling_history_size = 0;

/*[14:45:53][0 ms]*/ SET profiling_history_size = 15;

/*[14:45:53][5 ms]*/ SHOW STATUS;

/*[14:45:53][1 ms]*/ SELECT * FROM s LIMIT 100;

/*[14:45:53][2 ms]*/ SHOW STATUS;

/*[14:45:53][0 ms]*/ SHOW PROFILES;

/*[14:45:53][1 ms]*/ SELECT state, ROUND(SUM(duration),5) AS `duration (summed) in sec` FROM information_schema.profiling WHERE query_id = 17 GROUP BY state ORDER BY `duration (summed) in sec` DESC;

/*[14:45:53][0 ms]*/ SET PROFILING = 0;

This is expected. There is no EXPLAIN statement executed. And I do not get such error then of course.

If you get a server syntax error complaining about an incorrect EXPLAIN statement, it must either mean that an EXPLAIN statement is executed – and one with an incorrect syntax – or it is a server bug.

Can you provide a detailed test case similar to above?

[pest]

Hi there!

I turned the feature on again to get errors again.

Here is the first:

(I'm not able to upload jpeg, so I will describe: When execute the follwing query I get an MySQL Error 1064. Here is the History

/*[12:19:19][0 ms]*/ SHOW PROFILES;

/*[12:19:19][0 ms]*/ SELECT state, ROUND(SUM(duration),5) AS `duration (summed) in sec` FROM information_schema.profiling WHERE query_id = 89 GROUP BY state ORDER BY `duration (summed) in sec` DESC;

/*[12:19:19][0 ms]*/ SET PROFILING = 0;

/*[12:19:19][2 ms]*/ DROP TABLE IF EXISTS `__pb2__`;

/*[12:19:21][0 ms]*/ EXPLAIN FROM produktion.`__pb2__` prozbe LEFT JOIN produktion.`__pb2__` prozbe2 ON (prozbe.prozessid=prozbe2.`prozessid` AND prozbe.schrittnr_modul=prozbe2.`schrittnr_modul` AND prozbe2.`timekind`=”end”) LEFT JOIN `produktion`.`modul` mo ON (mo.id = prozbe.modulid) LEFT JOIN `userverwaltung`.`technologie` te ON (te.idtechnologie=mo.idtechnologie) WHERE prozbe.timekind='start' AND prozbe2.`timekind` IS NULL HAVING IF(te.`DurchlaufMax_h` IS NULL,1,Dauer<2*te.`DurchlaufMax_h`) ) AS a GROUP BY technologie LIMIT 0, 1000;

Seems that he drops the first word from the statement…

It was possible to produce this error on the following MySQL-Versions:

5.1.41

5.1.50

5.5.27

Regards,

pest

[pest]

Now there was an error like described first:

MySQL Error 1064: EXPLAIN LIMIT 0, 1000

/*[13:22:26][0 ms]*/ SET PROFILING = 1;

/*[13:22:26][0 ms]*/ SET profiling_history_size = 0;

/*[13:22:26][0 ms]*/ SET profiling_history_size = 15;

/*[13:22:26][2 ms]*/ SHOW STATUS;

/*[13:22:26][14 ms]*/ SELECT proz.`id`, .proz.`name` AS Wafer FROM `produktion`.`prozess` proz WHERE proz.`erledigt` IS NULL AND proz.`prioritaet`<4 AND proz.`starttimestamp` IS NULL AND proz.`naechster_schritt_basisschritt` IS NOT NULL LIMIT 0, 1000;

/*[13:22:26][1 ms]*/ SHOW STATUS;

/*[13:22:26][0 ms]*/ SHOW PROFILES;

/*[13:22:26][0 ms]*/ SELECT state, ROUND(SUM(duration),5) AS `duration (summed) in sec` FROM information_schema.profiling WHERE query_id = 370 GROUP BY state ORDER BY `duration (summed) in sec` DESC;

/*[13:22:26][0 ms]*/ SET PROFILING = 0;

/*[13:22:26][0 ms]*/ EXPLAIN LIMIT 0, 1000;

MySQL Version: 5.5.27

Bye,

pest

[shubh]

Hello,

Please let us know what SQLyog version you are using as we are unable to reproduce.

What type of SQLyog connection you are using (HTTP/SSH/Direct)?

You can attach screen-shots by clicking on “More reply options” in the right bottom corner close to “Post” button.

Regards,

Shubhansh

[pest]

Hi!

I'm using the new SqlYog Version 10.5 with direct MySQL connection

Regards,

pest

[Troy]

Using the latest version, I get the same thing. A dialog box pops up telling me I have an error in my syntax and shows a query starting with “EXPLAIN…”. The results tab shows the correct results from the query. I have narrowed it down to any SELECT query with comments (any style) in it. Remove the comments and the error goes away. Unfortunately, nearly all my scripts have comments so this is extremely annoying to have to close the dialog every time I run a query. I turned off the setting mentioned above and that solved the problem.

Troy

[shubh]

Hi,

Bug is reproducible in v10.5. We will fix it at priority. Fix will be available in next public release.

Regards,

Shubhansh

[sathish]

Hello,

We have released 10.51 with the fix for this. Please refer:

http://blog.webyog.com/2012/12/24/sqlyog-mysql-gui-10-51-released/

Regards,

Sathish

[Troy]

Great!

Troy

[Author]

Posts