[ERROR] COLLATION ‘utf8_general_ci’ is not valid for CHARACTER SET ‘latin1’

Recently came across the problem where mysql server stop running and refusing to start with an error

The server quit without updating PID file (/var/run/mysqld/mysqld.pid)

Checked Mysql error log and found that an invalid usage of charset with collation causing problem.

error log:
141017 12:20:41 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data
while 15088 [ERROR] COLLATION 'utf8_general_ci' is not valid for CHARACTER SET 'latin1'
15088 [ERROR] Aborting

In this case mysqld trying to start  with  character-set-server = latin1 and collation-server = utf8_general_ci, which is not valid.
The following is the right charset value for COLLATION ‘utf8_general_ci’

node1 [localhost] {msandbox} ((none)) > SHOW COLLATION LIKE ‘utf8_general_ci’;

+—————–+———+—-+———+———-+———+

| Collation       | Charset | Id | Default | Compiled | Sortlen |

+—————–+———+—-+———+———-+———+

| utf8_general_ci | utf8    | 33 | Yes     | Yes      |       1 |

+—————–+———+—-+———+———-+———+

Solution:

Add following options in my.cnf

character-set-server = utf8
collation-server = utf8_general_ci
character-set-client-handshake = false

And start  mysql server.It will start without any error.

ALL SET.

Advertisements