Bug #80075 "SET sql_mode=..." generates an error when @@character_set_connection=ucs2

• Submitted: 20 Jan 2016 13:26
• Modified: 21 Jan 2016 5:52
• Reporter: Yura Sorokin (OCA)
• Status: Verified
• Category: MySQL Server: Charsets
• Severity: S3 (Non-critical)
• Version: 5.5.47, 5.6.28, 5.7.10
• OS: Any
• CPU Architecture: Any
• Tags: character_set_connection, SQL_MODE

[20 Jan 2016 13:26] Yura Sorokin

Description:
When @@character_set_connection is set to 'ucs2', "SET sql_mode = '<mode_list>'" generates the following error
1231: Variable 'sql_mode' can't be set to the value of '<mode_list>'

How to repeat:
Run the following code fragment in MTR environment

****************************************
SET character_set_connection=ucs2;
SET sql_mode = 'NO_ENGINE_SUBSTITUTION';
****************************************

Output:
**********************************************************************
SET character_set_connection=ucs2;
main.a                                   [ fail ]
        Test ended at 2016-01-20 15:22:18

CURRENT_TEST: main.a
mysqltest: At line 2: query 'SET sql_mode = 'NO_ENGINE_SUBSTITUTION'' failed: 1231: Variable 'sql_mode' can't be set to the value of 'NO_ENGINE_SUBSTITUTION'
**********************************************************************

Suggested fix:
"SET sql_mode='<mode_list>'" must work properly regardless of the current value of @@character_set_connection.

[21 Jan 2016 5:52] MySQL Verification Team

Hello Yura,

Thank you for the report.

Thanks,
Umesh