-->

Tuesday, February 18, 2020

[Solved] Difference between the Variable vs Global variable in Amazon RDS

Recently faced the issue after making changes in the RDS Parameters and querying the same within the mysql rds in the Amazon AWS.

 mysql> SHOW VARIABLES WHERE Variable_name LIKE 'character_set_%' OR Variable_name LIKE 'collation%';  
 +--------------------------+-------------------------------------------+  
 | Variable_name      | Value                   |  
 +--------------------------+-------------------------------------------+  
 | character_set_client   | utf8                   |  
 | character_set_connection | utf8                   |  
 | character_set_database  | utf8mb4                  |  
 | character_set_filesystem | binary                  |  
 | character_set_results  | utf8                   |  
 | character_set_server   | utf8mb4                  |  
 | character_set_system   | utf8                   |  
 | character_sets_dir    | /rdsdbbin/mysql-5.7.22.R5/share/charsets/ |  
 | collation_connection   | utf8_general_ci              |  
 | collation_database    | utf8mb4_unicode_ci            |  
 | collation_server     | utf8mb4_unicode_ci            |  
 +--------------------------+-------------------------------------------+  
 11 rows in set (0.01 sec)  
 mysql> SHOW GLOBAL VARIABLES WHERE Variable_name LIKE 'character_set_%' OR Variable_name LIKE 'collation%';  
 +--------------------------+-------------------------------------------+  
 | Variable_name      | Value                   |  
 +--------------------------+-------------------------------------------+  
 | character_set_client   | utf8mb4                  |  
 | character_set_connection | utf8mb4                  |  
 | character_set_database  | utf8mb4                  |  
 | character_set_filesystem | binary                  |  
 | character_set_results  | utf8mb4                  |  
 | character_set_server   | utf8mb4                  |  
 | character_set_system   | utf8                   |  
 | character_sets_dir    | /rdsdbbin/mysql-5.7.22.R5/share/charsets/ |  
 | collation_connection   | utf8mb4_unicode_ci            |  
 | collation_database    | utf8mb4_unicode_ci            |  
 | collation_server     | utf8mb4_unicode_ci            |  
 +--------------------------+-------------------------------------------+  
 11 rows in set (0.00 sec)  

Cause:-
session variables are getting overridden is because the client auto detects which character set to use based on the operating system setting. 

Reproduce:-
for reproducing the case two different MySQL clients running on separate servers. One was installed on an Ubuntu subsystem running on my local machine and the other was installed on a Ubuntu Linux server running on an EC2 instance. MySQL client running on my local machine the variables were not overridden. However, on the Ubuntu Linux server running on EC2 the session variables got overridden. 

Workaround/Resolution:-
setting the 'skip-character-set-client-handshake' parameter to 1 using you custom parameter group. This will ignore the character set information detected by the client and therefore set the session character set variable to be the same value as your global variables

0 comments:

Post a Comment