Cannot change sql_mode in Settings -> Server parameters blade

Category: azure database for mysql


MarkAnstice on Tue, 19 Sep 2017 17:05:19


Azure Database for MySQL is unable to save my changes to the sql_mode parameter in the Settings -> Server parameters blade, I get an error when selecting STRICT_ALL_TABLES, NO_AUTO_CREATE_USER and NO_ENGINE_SUBSTITUTION.

The Activity log contains this status message:

 "statusMessage": "{\"status\":\"Failed\",\"error\":{\"code\":\"ResourceOperationFailure\",\"message\":\"The resource operation completed with terminal provisioning state 'Failed'.\",\"details\":[{\"code\":\"InvalidConfigurationValue\",\"message\":\"The value ',STRICT_ALL_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' for configuration 'sql_mode' is not valid. The allowed values are 'ALLOW_INVALID_DATES,ANSI_QUOTES,ERROR_FOR_DIVISION_BY_ZERO,HIGH_NOT_PRECEDENCE,IGNORE_SPACE,NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,NO_BACKSLASH_ESCAPES,NO_DIR_IN_CREATE,NO_ENGINE_SUBSTITUTION,NO_FIELD_OPTIONS,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_UNSIGNED_SUBTRACTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ONLY_FULL_GROUP_BY,PAD_CHAR_TO_FULL_LENGTH,PIPES_AS_CONCAT,REAL_AS_FLOAT,STRICT_ALL_TABLES,STRICT_TRANS_TABLES'.\"}]}}"

That leading comma before STRICT_ALL_TABLES looks like it should not be there.

Is there a workaround?




Jason M. Anderson on Tue, 19 Sep 2017 19:58:27

Looks like you found a bug - thank you for reporting it!  I'll open it up with our engineering team.

MarkAnstice on Wed, 20 Sep 2017 10:44:20

Thanks Jason, if you could provide a timeframe for a fix that would be much appreciated as this is a blocker for me.

I also see that I'm unable to pick a single option for sql_mode as the save button doesn't get enabled.

Jason M. Anderson on Wed, 20 Sep 2017 14:20:47

I noticed that as well.  One way to get around the save button issue is to change another parameter, then change it back before saving and the save button will be enabled for sql_mode.  However the trailing "," may not be only happening to STRICT_TABLES so it may be moot anyway. 

MarkAnstice on Sun, 24 Sep 2017 19:34:17

As a workaround I can set sql_mode with using the Azure CLI e.g:

az mysql server configuration set --server-name <name> --resource-group <group> --name sql_mode --value "NO_AUTO_CREATE_USER,STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION"

When querying with MySQL Workbench instead of an empty string (the default) I get:

select @@sql_mode;