[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

2 thoughts on “[ERROR] COLLATION ‘utf8_general_ci’ is not valid for CHARACTER SET ‘latin1’

    • Good Question Arnd.

      Hosting provider should provide some interface to add variables in mysql configuration file. For example, Amazon RDS provides an option on GUI dashboard to add variable in mysql configuration file and after a restart those variable will be applied/reflect for mysqld server.
      We can set these variables dynamically(while mysql server is running), but in our case server not started yet and we have to set these variables in mysql configuration file.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s