Bug 1010582 - Can not change MySQL character-set variables due to my.cnf lock
Can not change MySQL character-set variables due to my.cnf lock
Status: CLOSED WONTFIX
Product: OpenShift Online
Classification: Red Hat
Component: Containers (Show other bugs)
2.x
Unspecified Unspecified
unspecified Severity medium
: ---
: ---
Assigned To: Hiro Asari
libra bugs
: UpcomingRelease
: 1022964 (view as bug list)
Depends On:
Blocks:
  Show dependency treegraph
 
Reported: 2013-09-21 14:29 EDT by KonstantinAn
Modified: 2015-05-14 19:28 EDT (History)
5 users (show)

See Also:
Fixed In Version:
Doc Type: Bug Fix
Doc Text:
Story Points: ---
Clone Of:
Environment:
Last Closed: 2013-09-30 08:06:09 EDT
Type: Bug
Regression: ---
Mount Type: ---
Documentation: ---
CRM:
Verified Versions:
Category: ---
oVirt Team: ---
RHEL 7.3 requirements from Atomic Host:
Cloudforms Team: ---


Attachments (Terms of Use)

  None (edit)
Description KonstantinAn 2013-09-21 14:29:57 EDT
Description of problem:
I used to change the character-set variables for my MySQL DB by adding to the file my.cnf under [mysqld] variables like character-set-server=utf8, character-set-client=utf8... skip-character-set-client-handshake in order to be able to store data with different alphabet like Russian, Greek which have characters that are not included in latin1 which is the default value. Now that my.cnf is locked (read only) I can not do it again and my web application shows question marks when someone insert data with different character set (encoding).

Version-Release number of selected component (if applicable):
MySQL 5.1

How reproducible:


Steps to Reproduce:
1. SSH to the MY SQL gear
2. edit the file using the file path  ~/mysql/conf/my.cnf
3. Add the parameters like character-set-server=utf8 under [mysqld]

Actual results:
"Permission Denied"

Expected results:
Should be able to change the Database character set to UTF8

Additional info:
Is there any rhc env set command related to character set in order to change it?
my.cnf file does not mention anything about character-set variables that can be changed through rhc env command.
Comment 1 Michal Fojtik 2013-09-23 03:56:29 EDT
Hmm. Have you tried "SET NAMES charset" in your application? Also many (ruby) ORM frameworks support 'encoding' option that will change the default encoding.
Comment 2 KonstantinAn 2013-09-23 13:44:13 EDT
Thank you for your answer Michal.

Actually I do not use any framework or any ORM for my DB... my application is written from scratch in Java but I found a solution for UTF8 encoding.
I changed the connection parameters for DriveManager, it was 

DriverManager.getConnection("jdbc:mysql://mySql_IP:3306/myDatabase", "someUsername", "somePassword");

and now I have put ?useUnicode=true&characterEncoding=UTF-8 in the end of the first parameter so it became:

DriverManager.getConnection("jdbc:mysql://mySql_IP:3306/myDatabase?useUnicode=true&characterEncoding=UTF-8", "someUsername", "somePassword");

Now it seems to work fine!
But I think it might be good if we could change the character-set variables for MySQL just like OPENSHIFT_MYSQL_LOWER_CASE_TABLE_NAMES, sometimes it's easier to change these variables than change things in the code.
Comment 3 Hiro Asari 2013-09-26 11:39:54 EDT
These are potentially treacherous waters to tread; if the character set values (and a related variable, 'collation-server') are incorrect, MySQL server fails to start. And it could be difficult to troubleshoot.

We would like to avoid this situations altogether if we can.

As you noted, these values are dynamic, so that configuration via my.cnf is not necessary. You can use the driver to set it (as you showed above), or issue a MySQL command equivalent, like so:

mysql> SET GLOBAL character_set_server=utf8, collation_server=utf8_general_ci;                                                                                                    
Query OK, 0 rows affected (0.00 sec)

Do you agree?
Comment 4 KonstantinAn 2013-09-30 07:12:03 EDT
(In reply to Hiro Asari from comment #3)
> These are potentially treacherous waters to tread; if the character set
> values (and a related variable, 'collation-server') are incorrect, MySQL
> server fails to start. And it could be difficult to troubleshoot.
> 
> We would like to avoid this situations altogether if we can.
> 
> As you noted, these values are dynamic, so that configuration via my.cnf is
> not necessary. You can use the driver to set it (as you showed above), or
> issue a MySQL command equivalent, like so:
> 
> mysql> SET GLOBAL character_set_server=utf8,
> collation_server=utf8_general_ci;                                           
> 
> Query OK, 0 rows affected (0.00 sec)
> 
> Do you agree?

Thank you for your explanation, yes these two solutions (DriveManager and SET GLOBAL values for mysql) worked perfectly for me.
Comment 5 Hiro Asari 2013-09-30 08:06:09 EDT
In that case, then, I am closing this ticket as WONTFIX.

Thank you.
Comment 6 Hiro Asari 2013-10-24 15:08:18 EDT
*** Bug 1022964 has been marked as a duplicate of this bug. ***
Comment 7 Hanine HAMZIOUI ALMADANI 2013-10-25 12:40:57 EDT
Well! Just one question please!
logging in ssh mode and executing the MySQL command :
mysql> SET GLOBAL character_set_server=utf8, collation_server=utf8_general_ci; 
does indeed change the server character set and collation to UTF8, yet does the change persist!
Whenever there is a git push, a cartridge upgrade or a cartridge startup, the charset is back to latin.

Does it mean that I have to issue the above MySQL command each time I git push?

Thanks in advance.
Comment 8 Hiro Asari 2013-10-25 13:31:46 EDT
In essence, yes, you'd need to run that command on each MySQL startup.

However, you can automate this with the action hook (say, 'post_start_mysql' or 'deploy'):

#!/bin/bash

/usr/bin/mysql -h $OPENSHIFT_MYSQL_DB_HOST -u $OPENSHIFT_MYSQL_DB_USERNAME \
 --password=$OPENSHIFT_MYSQL_DB_PASSWORD -P $OPENSHIFT_MYSQL_DB_PORT \
 -e 'SET GLOBAL character_set_server=utf8, collation_server=utf8_general_ci;'

See http://openshift.github.io/documentation/oo_user_guide.html#action-hooks for more details on action hooks.
Comment 9 Hanine HAMZIOUI ALMADANI 2013-10-26 15:03:49 EDT
Sorry for being annoying.
Afte sevral checks, it seems the above command is not useful when using a Java EE application that connects to MySQL through a DataSource.

At first I tried appending the charset variables values in my.cnf :

pathtofile="/var/lib/openshift/xxxxxxxxxxxxxxxxxxxxxxxx/mysql/conf/my.cnf"
sed -i '/mysqld]/ a\collation-server=utf8_general_ci' $pathtofile
sed -i '/mysqld]/ a\character-set-server=utf8' $pathtofile

but since, it was protected, permission to write was denied of course, whether using deploy or post-deploy hooks.

Adding phpmyadmin 4.0 cartridge and setting global charset values to utf8, did not help neither.

At last, the solution was inspired from the previous user (KonstantinAn):

I added to property nodes to persistence.xml :

<property name="hibernate.connection.useUnicode" value="true" />
            <property name="hibernate.connection.characterEncoding" value="UTF-8" />  

and under: MY_APPLICATION/.openshift/config/standalone.xml:

 <datasource jndi-name="java:jboss/datasources/MysqlDS" pool-name="MysqlDS" use-java-context="true" use-ccm="true">
                    <connection-url>jdbc:mysql://xxx.x.xxx.xxx:xxxx/databaseName?useUnicode=yes&amp;characterEncoding=UTF-8</connection-url>
                    <driver>mysql</driver>
                    <pool>
                        <flush-strategy>IdleConnections</flush-strategy>
                    </pool>
                    <security>
                        <user-name>myDBusername</user-name>
                        <password>myDBpassword</password>
                    </security>
                    <validation>
                        <check-valid-connection-sql>SELECT 1</check-valid-connection-sql>
                        <background-validation>true</background-validation>
                    </validation>
                </datasource>

Restarting the application or performing a git push does not affect charset anymore.

If you believe this is a solution, then it should be mentioned in OpenShift Documentation (Java EE profile) that the user is responsible for forcing the use of a specific CHARSET like UTF8.

Thanks in advance.
Comment 10 Hiro Asari 2013-10-28 08:09:50 EDT
Thank you for your input. I created Bug 1023944 for documenting this.

Note You need to log in before you can comment on or make changes to this bug.