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
SET NAMES 'utf8'
?