The option character-set-database should not be configured in my.cnf
.
Please note what the MySQL Documentation says on character-set-database
:
The character set used by the default database. The server sets this variable whenever the default database changes. If there is no default database, the variable has the same value as character_set_server.
Footnote : This option is dynamic, but only the server should set this information. You should not set the value of this variable manually.
Even the Documentation says it is dynamic, it not supposed to be dynamically by any manual intervention against my.cnf
. If you look inside the database subfolder, you will find a file called db.opt
. EXAMPLE : When you run use dbname
in the mysql client, the file /var/lib/mysql/dbname/db.opt
is read in order to set character-set-specific database options contained in that file. For this reason, the variable has to be dynamic.
If you cannot access the database from the OS to see db.opt
, simply run this command:
SHOW CREATE DATABASE dbname;
on any database and you will see what db.opt
contains (or defaults if db.opt
is not there)
mysql> show create database mysql;
+----------+------------------------------------------------------------------+
| Database | Create Database |
+----------+------------------------------------------------------------------+
| mysql | CREATE DATABASE `mysql` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql>
In light of this, you should try setting character-set-server
in my.cnf
only (or at least remove character-set-database
from my.cnf
). Then, run service mysql restart
.
Give it a Try !!!
UPDATE #1
I sort of dealt with a question like this before : Why default character_set_server is latin1?
Looking back at my old link, I had an idea: I ran this:
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.6.10 |
+-----------+
1 row in set (0.00 sec)
mysql> select * from information_schema.collations where COLLATION_NAME like '%kor%';
+-----------------+--------------------+----+------------+-------------+---------+
| COLLATION_NAME | CHARACTER_SET_NAME | ID | IS_DEFAULT | IS_COMPILED | SORTLEN |
+-----------------+--------------------+----+------------+-------------+---------+
| euckr_korean_ci | euckr | 19 | Yes | Yes | 1 |
+-----------------+--------------------+----+------------+-------------+---------+
1 row in set (0.00 sec)
mysql>
You could set a Korean character set if need to.
UPDATE #2
You should leave wait_timeout and interactive_timeout out of the [client]
and [mysql]
groups.
Since Zapier is coming in from outside your instance, a possible cause of your problem is the security group for your instance. Most ports on EC2 instances (except the SSH port) are normally blocked by default unless you explicitly open them.
So you would need to open the mysql port 3306 on your instance up to the ip address Zapier is coming in from. (preferably you would not open it up to everyone in the world.)
See Using Amazon RDS/EC2 on the Zapier site, starting from the section where it says Can't Connect to My Database. They continue on to provide specific instructions for the security group settings.
You will also probably need to grant permission on the database using the mysql GRANT command to the user coming in from the Zapier ip address.
Something like this should work :
GRANT ALL PRIVILEGES
ON mydb.*
TO 'user'@'54.86.9.50'
IDENTIFIED BY 'newpassword';
Or you can allow that user to come in from any ip adress like this:
GRANT ALL PRIVILEGES
ON mydb.*
TO 'user'@'%'
IDENTIFIED BY 'newpassword';
(Incoming requests would still be limited to Zapier ip addresses due to the security group settings.)
Best Answer
You have the option of setting character sets/collations on the fly:
While trial-and-error may be necessary, don't go willy-nilly on these variables in /etc/my.cnf. You are better off setting them dynamically during any trial-and-error testing.
To make sure of any corner cases, look at the initial variables of any mysqldump and see if character sets or collations are set in the beginning and reset at the bottom.
In fact, here is a sample for a mysqldump:
See the variables
You could perhaps set these options
[mysqldump]
group section