MySQL the.cnf won’t take any effect

linuxMySQL

Update 7

As DerekDowney suggested, I tried to use

[mysqld]
init_connect=SET NAMES 'utf8'

But I could not get the desired result. My client side java programme still says both session and global character_set_server and the others are latin1 unless I use the method in Update 6.

Update 6

I was able to achieve this by adding –character-set-server=utf8 to
mysqld command.
But I think my.cnf is still not applying. Even so, if there are anyone who's interested in how I made it 'kinda' work, I think it's worth putting how up here.
I am still working on my.cnf and all the other configuration thingy : )

Question
I'm experiencing issues using MySQL on Linux server.

I set some timeout and characterset options in my.cnf but they won't take any effect.
My goal is to set all character set default as utf8 and set wait_timeout and/or interactive_timeout to 30 seconds.

My environment

Linux CentOS 5.x
MySQL Server 5.6

MySQL client

I installed both MySQL Server and client as root using RPM.
I start MySQL server with

] mysqld -u root

I stop MySQL server with

service mysql stop

The reason why I use mysqld -u root is that it often says this.

[root@kserver145-208 ~]# service mysql stop
Shutting down MySQL....                                    [  OK  ]
[root@kserver145-208 ~]# service mysql start
Starting MySQL...The server quit without updating PID file [실패]lib/mysql/kserver145-208.pid).

Here is what mysql says from command line.

select @@session.wait_timeout, @@global.wait_timeout;
+------------------------+-----------------------+
| @@session.wait_timeout | @@global.wait_timeout |
+------------------------+-----------------------+
|                  28800 |                 28800 |
+------------------------+-----------------------+
1 row in set (0.00 sec)


 select @@session.character_set_database, @@global.character_set_database;                        
+----------------------------------+---------------------------------+
| @@session.character_set_database | @@global.character_set_database |
+----------------------------------+---------------------------------+
| latin1                           | latin1                          |
+----------------------------------+---------------------------------+
1 row in set (0.00 sec)

But I previously set my.cnf like below.
Sorry I't a bit long. I could have just cut the parts that count but I don't know what might be wrong with the way I set options in this file. So I'll just show everything to you here.

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html

[mysqld]

# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M

# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin

# These are commonly set, remove the # and set as required.
# basedir = .....
# datadir = .....
# port = .....
# server_id = .....
# socket = .....

# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
wait_timeout=30
interactive_timeout=30
character-set-server=utf8
collation-server=utf8_general_ci
character-set-database=utf8

#init_connect=SET collation_connection =utf8_general_ci
#init_connect=SET NAMES utf8
#init_connect=SET character_set_database = utf8

[client]
character-set-database=utf8
character-set-server=utf8
wait_timeout=30
interactive_timeout=30

[mysqldump]
#default-character-set=utf8

[mysql]
wait_timeout=30
interactive_timeout=30
character-set-database=utf8
character-set-server=utf8

I've been banging my head against this wall over 72 hours. What have I done wrong ?

PS
Here is what my kserver145-208.err says. Hope it helps understanding what's wrong.

130326 09:51:47 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
2013-03-26 09:51:47 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2013-03-26 09:51:47 13587 [Note] Plugin 'FEDERATED' is disabled.
^G/usr/sbin/mysqld: Can't find file: './mysql/plugin.frm' (errno: 13 - Permission denied)
2013-03-26 09:51:47 13587 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
2013-03-26 09:51:47 13587 [Note] InnoDB: The InnoDB memory heap is disabled
2013-03-26 09:51:47 13587 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2013-03-26 09:51:47 13587 [Note] InnoDB: Compressed tables use zlib 1.2.3
2013-03-26 09:51:47 13587 [Note] InnoDB: CPU does not support crc32 instructions
2013-03-26 09:51:47 13587 [Note] InnoDB: Using Linux native AIO
2013-03-26 09:51:47 13587 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2013-03-26 09:51:47 13587 [Note] InnoDB: Completed initialization of buffer pool
2013-03-26 09:51:47 13587 [Note] InnoDB: Highest supported file format is Barracuda.
2013-03-26 09:51:47 2ac0d44d9590  InnoDB: Operating system error number 13 in a file operation.
InnoDB: The error means mysqld does not have the access rights to
InnoDB: the directory.
2013-03-26 09:51:47 13587 [ERROR] InnoDB: Could not find a valid tablespace file for 'yoursmart/S_MEM_POINT'. See http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html for how to resolve the issue.
2013-03-26 09:51:47 13587 [ERROR] InnoDB: Tablespace open failed for '"yoursmart"."S_MEM_POINT"', ignored.
2013-03-26 09:51:47 13587 [Note] InnoDB: 128 rollback segment(s) are active.
2013-03-26 09:51:47 13587 [Note] InnoDB: Waiting for purge to start
2013-03-26 09:51:47 13587 [Note] InnoDB: 1.2.10 started; log sequence number 55877336
2013-03-26 09:51:47 13587 [ERROR] /usr/sbin/mysqld: unknown variable 'character-set-database=utf8'
2013-03-26 09:51:47 13587 [ERROR] Aborting

2013-03-26 09:51:47 13587 [Note] Binlog end
2013-03-26 09:51:47 13587 [Note] Shutting down plugin 'partition'
2013-03-26 09:51:47 13587 [Note] Shutting down plugin 'BLACKHOLE'
2013-03-26 09:51:47 13587 [Note] Shutting down plugin 'PERFORMANCE_SCHEMA'
2013-03-26 09:51:47 13587 [Note] Shutting down plugin 'ARCHIVE'
2013-03-26 09:51:47 13587 [Note] Shutting down plugin 'INNODB_SYS_DATAFILES'
2013-03-26 09:51:47 13587 [Note] Shutting down plugin 'INNODB_SYS_TABLESPACES'
2013-03-26 09:51:47 13587 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN_COLS'
2013-03-26 09:51:47 13587 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN'
2013-03-26 09:51:47 13587 [Note] Shutting down plugin 'INNODB_SYS_FIELDS'
2013-03-26 09:51:47 13587 [Note] Shutting down plugin 'INNODB_SYS_COLUMNS'
2013-03-26 09:51:47 13587 [Note] Shutting down plugin 'INNODB_SYS_INDEXES'
2013-03-26 09:51:47 13587 [Note] Shutting down plugin 'INNODB_SYS_TABLESTATS'
2013-03-26 09:51:47 13587 [Note] Shutting down plugin 'INNODB_SYS_TABLES'
2013-03-26 09:51:47 13587 [Note] Shutting down plugin 'INNODB_FT_INDEX_TABLE'
2013-03-26 09:51:47 13587 [Note] Shutting down plugin 'INNODB_FT_INDEX_CACHE'
2013-03-26 09:51:47 13587 [Note] Shutting down plugin 'INNODB_FT_CONFIG'
2013-03-26 09:51:47 13587 [Note] Shutting down plugin 'INNODB_FT_BEING_DELETED'
2013-03-26 09:51:47 13587 [Note] Shutting down plugin 'INNODB_FT_DELETED'
2013-03-26 09:51:47 13587 [Note] Shutting down plugin 'INNODB_FT_INSERTED'
2013-03-26 09:51:47 13587 [Note] Shutting down plugin 'INNODB_FT_DEFAULT_STOPWORD'
2013-03-26 09:51:47 13587 [Note] Shutting down plugin 'INNODB_METRICS'
2013-03-26 09:51:47 13587 [Note] Shutting down plugin 'INNODB_BUFFER_POOL_STATS'
2013-03-26 09:51:47 13587 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE_LRU'
2013-03-26 09:51:47 13587 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE'
2013-03-26 09:51:47 13587 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX_RESET'
2013-03-26 09:51:47 13587 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX'
2013-03-26 09:51:47 13587 [Note] Shutting down plugin 'INNODB_CMPMEM_RESET'
2013-03-26 09:51:47 13587 [Note] Shutting down plugin 'INNODB_CMPMEM'
2013-03-26 09:51:47 13587 [Note] Shutting down plugin 'INNODB_CMP_RESET'
2013-03-26 09:51:47 13587 [Note] Shutting down plugin 'INNODB_CMP'
2013-03-26 09:51:47 13587 [Note] Shutting down plugin 'INNODB_LOCK_WAITS'
2013-03-26 09:51:47 13587 [Note] Shutting down plugin 'INNODB_LOCKS'
2013-03-26 09:51:47 13587 [Note] Shutting down plugin 'INNODB_TRX'
2013-03-26 09:51:47 13587 [Note] Shutting down plugin 'InnoDB'
2013-03-26 09:51:47 13587 [Note] InnoDB: FTS optimize thread exiting.
2013-03-26 09:51:47 13587 [Note] InnoDB: Starting shutdown...
2013-03-26 09:51:49 13587 [Note] InnoDB: Shutdown completed; log sequence number 55877346
2013-03-26 09:51:49 13587 [Note] Shutting down plugin 'MRG_MYISAM'
2013-03-26 09:51:49 13587 [Note] Shutting down plugin 'MEMORY'
2013-03-26 09:51:49 13587 [Note] Shutting down plugin 'CSV'
2013-03-26 09:51:49 13587 [Note] Shutting down plugin 'MyISAM'
2013-03-26 09:51:49 13587 [Note] Shutting down plugin 'sha256_password'
2013-03-26 09:51:49 13587 [Note] Shutting down plugin 'mysql_old_password'
2013-03-26 09:51:49 13587 [Note] Shutting down plugin 'mysql_native_password'
2013-03-26 09:51:49 13587 [Note] Shutting down plugin 'binlog'
2013-03-26 09:51:49 13587 [Note] /usr/sbin/mysqld: Shutdown complete

130326 09:51:49 mysqld_safe mysqld from pid file /var/lib/mysql/kserver145-208.pid ended

Update 1
I changed my.cnf to replace character-set-database to character_set_database along with the other variables in my.cnf as Phill suggested. However, kserver***.err still says

2013-03-26 10:14:32 14300 [ERROR] /usr/sbin/mysqld: unknown variable 'character_set_database=utf8'

Also

mysql> select @@session.wait_timeout, @@global.wait_timeout;
+------------------------+-----------------------+
| @@session.wait_timeout | @@global.wait_timeout |
+------------------------+-----------------------+
|                  28800 |                 28800 |
+------------------------+-----------------------+
1 row in set (0.00 sec)

mysql> select @@session.character_set_database, @@global.character_set_database;
+----------------------------------+---------------------------------+
| @@session.character_set_database | @@global.character_set_database |
+----------------------------------+---------------------------------+
| latin1                           | latin1                          |
+----------------------------------+---------------------------------+
1 row in set (0.01 sec)

Update 2

I have modified my.cnf as rolaldo suggested. kserver***.err does not show those signs of error anymore. So it's more promising.
But when I issue this command

mysql> show create database yoursmart;
+-----------+--------------------------------------------------------------------+
| Database  | Create Database                                                    |
+-----------+--------------------------------------------------------------------+
| yoursmart | CREATE DATABASE `yoursmart` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+-----------+--------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select @@session.character_set_server, @@global.character_set_server;
+--------------------------------+-------------------------------+
| @@session.character_set_server | @@global.character_set_server |
+--------------------------------+-------------------------------+
| latin1                         | latin1                        |
+--------------------------------+-------------------------------+
1 row in set (0.00 sec)

It still says character_set_server is latin1. I don't understand…
Maybe this is normal ?
The database contains Korean characters and I'm not able to do like search with korean words.

Update 4
Whoa, I think this is getting lengthy but here is what I've found out so far.
Thanks to Rolando's suggestions and links, it came to my mind to check the characterset
at connection time from the Java side.

Here is what I used

            PreparedStatement pstmt = c.prepareStatement("SELECT @@global.character_set_connection, @@session.character_set_connection" +
                    ", @@global.character_set_server, @@session.character_set_server");
            ResultSet rs = pstmt.executeQuery();

            int i=0;
            while(rs.next()){
                i++;
                System.out.println("@@global.character_set_connection: " + rs.getString(1));
                System.out.println("@@session.character_set_connection: " + rs.getString(2));
                System.out.println("@@global.character_set_server: " + rs.getString(3));
                System.out.println("@@session.character_set_server: " + rs.getString(4));

                System.out.println("@@global.character_set_connection : " + rs.toString());
                ResultSetMetaData rsmd = rs.getMetaData();
                System.out.println("@@getColumnCount() : " + rsmd.getColumnCount());
            }

And the result was, rather surprising, well, at least to me.

@@global.character_set_connection: latin1
@@session.character_set_connection: latin1
@@global.character_set_server: latin1
@@session.character_set_server: latin1

Update 5
Realising the above, I tried to set the character set at the connection time but without luck.

I added connection parameter.

ip:port/databaseName?zeroDateTimeBehavior=convertToNull&initstmt=SET CHARACTER SET utf8

Best Answer

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.

Best Answer

UPDATE #1

UPDATE #2

Related Solutions

MySql: Change from latin1 to utf8

MySQL InnoDB logfiles resized – review thesql-err.log for potential problems

Related Question