Tags: MySQL

To solve this error I pulled my hair and scrolled through so many blog articles and forum. Finally today I came up with different solutions either can help you.

Note in Public Interest: MySQL has strange behavior, I don't know which kinda developer working on it? So if you are facing an issue right now may not occur after day or so.

Scenario: Most of our clients using Microsoft SQL Server but two or three client want MySQL Server on their server so we've to work on it. We deployed our code on MySQL Server. Our product has so many Stored Procedures, It was working fine but two days back they were facing same error.

Solution 1: Check your query related column's collation using following query and change as per your requirement.

/* Show all columns and its collation */
SELECT * 
FROM information_schema.columns 
WHERE table_schema = DATABASE();
/* ALTER TABLE  MODIFY  VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci; to change column collation */
ALTER TABLE address MODIFY address1 VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci;

Solution 2: Check your table's collation using following query and change as per your requirement.

/* Show all tables and its collation */
SHOW TABLE STATUS;
/* ALTER TABLE <table_name> CONVERT TO CHARACTER SET utf8 COLLATE <collation>; to change table collation. */
ALTER TABLE actor CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;

Solution 3: Check collation of your database and change as per your requirement.

/* To check your database collation */
SELECT * 
FROM information_schema.SCHEMATA S
WHERE schema_name = DATABASE(); 
/* ALTER DATABASE <database_name> CHARACTER SET utf8 COLLATE <collation>; To change database collation */
ALTER DATABASE test CHARACTER SET utf8 COLLATE utf8_unicode_ci;

I did above suggested all changes still I was facing same error. I dig down further and found that my connection's collation is utf8_general_ci and database collation is utf8_unicode_ci. To understand the my ground follow below image.



Solution 4: Use COLLATE within inline query as follow.

SELECT *
FROM actor 
WHERE name = tmp_VARIABLE
COLLATE utf8_unicode_ci; 

Solution 5: Change parameter's character set in case of Stored Procedure or functions.

CREATE PROCEDURE proc_test (param1 VARCHAR (10) charset utf8, param2 int)

Solution 6: MySQL configuration changes
Place following lines under [mysqld] tag in MySQL configuration file lying under /etc/mysq/my.cnf [Linux] and C:\ProgramData\MySQL\MySQL Server 5.6\my.ini [Windows].

character-set-server=utf8
collation-server=utf8_unicode_ci

After change it'll look like as follow...

[mysqld]
character-set-server=utf8
collation-server=utf8_unicode_ci

Now restart MySQL Server.

In my case solution 4 and 5 worked like charm. I tried to change collation_connection variable using mysql configuration file but didn't get success to solve this issue. Please share your solution if you have any.

Special Thanks to:
- http://forums.mysql.com/read.php?103,265345,265579#msg-265579
- http://www.excindex.com/mysql/589131.html

Tags: MySQL