MySQL Bugs: #60344: hyphen/minus should not be used in server variables

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:	None
Category:	MySQL Server: Options	Severity:	S4 (Feature request)
Version:	any recent	OS:	Any
Assigned to:	Georgi Kodinov	CPU Architecture:	Any
Tags:	qc

Description:
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.

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?

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?

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?

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.

I actually posted my previous comment before I saw Valeriy's last post, so let us make it S4 then.

And I think that using a VALUE fro a server variable that contains a operator contradicts common sense.

Nobody would ever dream of using

THIS+VAR
THIS*VAR
THIS/VAR

why then use

THIS-VAR

?? 

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.

What is the issue here? tx_isolation is of enum type, which takes a string object. String objects are delimited by quotes.

See http://bugs.mysql.com/bug.php?id=60011 also about the deprecation process in similar case...

Let's say the issue is cosmetic...

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).

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.

In the English language, the hyphen is a punctuation mark used to join words. Underscore is not a even punctuation mark.

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.

> 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. :-)

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?

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? :-)

Also, if you are going to quote me, do it properly. I never wrote "my English language".

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)

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.