Sqlyog Paste Sql Statements Are Invalid And Will Error On Mysql Reserv

[pixelchutes]

I'd like to address a SQLyog bug that I could not find reference to in the forum or Google Group, and that is the generated SQL from “Table > Paste SQL Statements”. When the table you are working with contains column(s) named after a MySQL reserved word, the generated SQL itself does not run without throwing an error (error code: 1064).

Consider this table:

Code:

CREATE TABLE `example` (
`condition` ENUM('example_value') NOT NULL,
`value` VARCHAR(75) NOT NULL
) ENGINE=MYISAM;

If you were to perform “Table > Paste SQL Statements > SELECT” (Alt + Shift + S), what you get it is:

Code:

SELECT CONDITION, VALUE FROM db_name.example LIMIT 0, 75;

Unfortunately, running this generated SQL throws Error code: 1064 🙁

If it were to generate the following, this could be prevented:

Code:

SELECT `condition`, `value` FROM db_name.example LIMIT 0, 75;

NOTE: In this case, the table name itself is also a reserved word. It does not error because of the database concatenation. At a minimum, it would be ideal if the column references in the generated SQL were automatically escaped via `backticks`, or at least the reserved words to prevent erroneously generated SQL.

You may want to go as far as escaping the database and table names as well, in the event they are reserved words, or perhaps contain spaces:

Code:

CREATE TABLE `my example` (
`condition` ENUM('example_value') NOT NULL,
`value` VARCHAR(75) NOT NULL
) ENGINE=MYISAM;

Generates this erroneous SQL:

Code:

SELECT CONDITION, VALUE FROM db_name.my EXAMPLE LIMIT 0, 75;

…ideally it would be this:

Code:

SELECT `condition`, `value` FROM `db_name`.`my example` LIMIT 0, 75;

Have any of you ran into this scenario before? The only current way around it with even the most recent build of SQLyog is to prevent creating (or working with) schemas that use MySQL reserved words…often however, this is not always an option.

Keep up the great work on this amazing tool! Next step? SQLyog for Mac OS X 😉

[peterlaursen]

Please see this setting.

[attachment=1316:backquotes.jpg]

Did it help?

[pixelchutes]

'peterlaursen' wrote on '16:

Please see this setting.

[attachment=1316:backquotes.jpg]

Did it help?

Boy do I feel stupid!

That is exactly what I was looking for, thank you, Peter. My apologies for misidentifying as a SQLyog Bug.

+1 for making this setting activated by default! 😉

[Author]

Posts