Bug #60344 hyphen/minus should not be used in server variables
Submitted: 4 Mar 2011 16:48 Modified: 19 Nov 2015 17:12
Reporter: Peter Laursen (Basic Quality Contributor) Email Updates:
Status: Unsupported Impact on me:
Category:MySQL Server: Options Severity:S4 (Feature request)
Version:any recent OS:Any
Assigned to: Georgi Kodinov CPU Architecture:Any
Tags: qc

[4 Mar 2011 16:48] Peter Laursen
Since long ago most start-up options can be specified as variables as well. Some variables have a hyphen in the values allowed.  It creates a weird effect as the parser interprets the hyphen as a "minus" operator.

How to repeat:
(with '' (empty) SQL_mode:)

SET tx_isolation = REPEATABLE-READ;
-- syntax error what makes sense as "-" is an operator ("minus").
SET tx_isolation = REPEATABLE_READ;
-- Error Code : 1231 - Variable 'tx_isolation' can't be set to the value of 'REPEATABLE_READ'
SET tx_isolation = `repeatable-read`; -- success
SET tx_isolation = 'repeatable-read'; -- success
SET tx_isolation = "repeatable-read"; -- success

Suggested fix:
1) I do not find it documented (but I could be wrong) here http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html (or elsewhere) that variables *may be* - and sometimes *must be* 'quoted' or `quoted` or "quoted". 

2) consider replace REPEATABLE-READ with REPEATABLE_READ (ie. first introduce latter as a synonym, later deprecate and remove the first form).

3) Alternatively there could be a fix in the parser.  However expression with '-' will sometimes make good sense for variables that are numerical

To me it looks like this was overlooked when (in 4.1, I guess) it became possible to set a variable from a client.
[4 Mar 2011 17:44] Valeriy Kravchuk
I clearly see the following in http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysvar_tx_isolation:

"If you set tx_isolation directly to an isolation level name that contains a space, the name should be enclosed within quotation marks, with the space replaced by a dash. For example:

SET tx_isolation = 'READ-COMMITTED';

Any unique prefix of a valid value may be used to set the value of this variable." 

Why this is a bug?
[4 Mar 2011 18:27] Peter Laursen
OK .. I should have checked this page too. 

But it is confusing. I think users could overlook it and they will (like me) need to spend time figuring it out. Other options and variables have been replaced/renamed before for consistency/clarity/friendlyness and I also think it should here. 

In my opinion the documentation here does mot make it *not a bug*, but just a *documented bug* instead. If InnoDB developers had known from the beginning that that this value could be SET from a client they would probably never have used a hyphen in any value 'filtered' by the parser.

Does not clarity, intuitivity and user-friendliness matter for MySQL/Oracle?
[4 Mar 2011 18:54] Valeriy Kravchuk
It's hard for me to speculate why (and by whom) this specific syntax was invented...

But this is MySQL-specific SQL extension, and syntax is documented, so definitely this is NOT a bug. You can just use SQL standard way to set transaction isolation level if you care about clarity and ease of use, http://dev.mysql.com/doc/refman/5.5/en/set-transaction.html.

I agree that this "design decision" may not be the best ever made by MySQL, but if we want to change it (and consider this report as a useful feature request) we should take into account existing code written by people who read the manual. This code may use current syntax. As a result, deprecation process is needed and it may be MySQL 7 or 8 when this change will happen (even if we all will do our best to force it and will not fail).

Do you really want to initiate this process?
[4 Mar 2011 18:56] Peter Laursen
Anyway .. let's change category to 'feature request'. The discussion leads nowhere. 

But to summarize my position: It is OK that docs team document current behavior. But 'current behavior' is not something that should be considered sacred if it can be done better. In MySQL documentation seems to be an ultimate reference even if it contradicts common sense. I never agreed with that.
[4 Mar 2011 18:58] Peter Laursen
I actually posted my previous comment before I saw Valeriy's last post, so let us make it S4 then.
[4 Mar 2011 19:05] Peter Laursen
And I think that using a VALUE fro a server variable that contains a operator contradicts common sense.

Nobody would ever dream of using


why then use



It was a mistake from the beginning.  In early InnoDB it was less important (as options specified in options file or from commandline are not 'filtered' by parser) but from 4.1 onwards the mistake has become more obvious.
[4 Mar 2011 19:09] Davi Arnaut
What is the issue here? tx_isolation is of enum type, which takes a string object. String objects are delimited by quotes.
[4 Mar 2011 19:10] Valeriy Kravchuk
See http://bugs.mysql.com/bug.php?id=60011 also about the deprecation process in similar case...
[4 Mar 2011 19:11] Valeriy Kravchuk
Let's say the issue is cosmetic...
[4 Mar 2011 19:23] Davi Arnaut
As documented in the manual, the SET syntax takes a expression. A expression can be, for example, a 1+1 or literal values, such as a string. This behavior is the same for all variables. What might be different is the type of the variable, which requires a argument expression of the same type (or that can be converted to the said type).
[4 Mar 2011 19:29] Peter Laursen
The bug is that there is no reason to use "-" (hyphen).  It would be better/more logical/more consistent to use "_" (underscore).  Then it can be evaluated as an expression at no risk (as underscore is not an operator).

Besides I cannot accept that Davi (in his usual arrogant manner) denies a user the right to post a feature request.
[4 Mar 2011 19:35] Davi Arnaut
In the English language, the hyphen is a punctuation mark used to join words. Underscore is not a even punctuation mark.
[4 Mar 2011 19:39] Davi Arnaut
Also, you can request whatever you want. The status reflects our position that this is not a bug. Just because there is a special error message at a lower level (after the expression has been properly parsed), does not mean we will restrict everything so that you can get the said error message.
[4 Mar 2011 19:42] Davi Arnaut
> The bug is that there is no reason to use "-" (hyphen). 

Yes, there is. The hyphen is the punctuation mark that should be used to join words. :-)
[4 Mar 2011 19:47] Peter Laursen
In programming it is common to use "_" and not "-" as such 'punctuation mark'.  

I did not get a reply why I am denied the right to post a feature request that such values should use "_" rather than "-".

Davi's reference to "my English language" is just another example of his arrogance when he is in trouble. I also think I speak English pretty well. He will not need to teach me about "English language".

What are official MySQL/Oracle rules here?  Who takes the decision? Supporters or developers?
[4 Mar 2011 19:53] Davi Arnaut
I was just pointing out that there is a reason of why the hyphen is there. It's because of the English language rules. Do you disagree? :-)
[4 Mar 2011 19:56] Davi Arnaut
Also, if you are going to quote me, do it properly. I never wrote "my English language".
[4 Mar 2011 21:03] Peter Laursen
Let us cool down. And let us not make it a language discussion (I'd prefer to write Tibetan then - we do not use "-" we have another character!)

This is of course somewhat an 'edge' discussion (note that I marked it as S3 and later changed to S4). I do not agree with Valeriy that it is 'cosmetcial' - I think it is a little bit more. But I believe software should constantly improve in all respects.

The basic reason for this reprot is that it now seems to become not uncommon that MySQL is started with a init_connect setting specifying AUTOCOMMIT = 0. MySQL is approaching Oracle (and other transactional databases) in this respect.  But this is a change as compared to earlier and lots of clients and applications (and users) will be fooled if AUTOCOMMIT = 0 and transaction isolation level is CONSISTENT-READ at the same time. This applies to all GUI clients that I know (phpMyAdmin, SQLyog, Workbench, HeidiSQL etc.). A user operating such client (as well as hundreds of standard and very popular PHP applications - Blogging softwares, Forums softwares etc.) will need to change either (SESSION) AUTOCOMMIT or (SESSION) transaction isolation level to use the client efficiently if running on a server with (GLOBAL) AUTOCOMMIT = 0 and (GLOBAL) transaction isolation level = CONSISTENT-READ.

I think software should be 'helpful' to users. It should protect them against failures and errors (if feature-richness and performance is not compromised by doing so). In this case using values with hyphen is 'unhelpful'.  Not all users will see the operator in "CONSISTENT-READ" and will not understand how to handle the error and will waste time figuring it out, so I think "CONSISTENT_READ" is marginally better. That is what this request is about. 

And I still think that from the beginning "_" should have been used rather than "-" (and also rather than "+" - just to pin it out!). Using "-" was in my opinion a thoughtless naming of values from the beginning but as long as the setting was not *parsed* it was no issue. It should have been changed with 4.1 release, I think. I understand that non-compatible changes are never desirable, but I have seen quite a lot of other non-compatible changes over the last ~5 years that seem to me than not better justified this (such as how to specify logging options for the server, where I never understood what the reason was for changing how the options were named)
[19 Nov 2015 17:12] Georgi Kodinov
Thank you for the time you took to document this ergonomic problem.
I understand your point why a symbol that would require quoting is annoying to have in an option that would typically go without quoting. 
It is a good observation and has some ergonomic merit.
However I hope that you'd agree with me that retroactively changing values for existing variables will create more confusion than the need for quoting the values.

Thus I invite you to follow our new developments (e.g. via the MySQL DMRs) and report early values or option names that you find non-ergonomic. This is the best time in the development cycle for us to react on such feedback. In fact this is one of the reasons we publish the development milestones.

Our bug reporting policy (http://bugs.mysql.com/how-to-report.php) suggests that we report only one problem per bug. I interpret this to mean that the bugdb is not a good media for design guidelines that apply in principle. 
Thus I am closing this bug and am hoping to get concrete feedback on the DMRs as suggested above.