MySQL character set problems
Recently I’ve ran into a problem with using character set other than default – cp125 (Cyrillic) in my case. The problem isn’t very well documented, so I’ve decided to recap all the troubles I’ve encountered, in hopes that it may help a person or two :)
The problem
All of the strings in non default character set would show up as a bunch of question marks (привет = ??????). I should note that this happened on my local machine, production server with similar data functioned just fine.
Attempts to fix the problem
If you’re only interested in the solution, you may skip to the conclusion.
Step 1:
Naturally, first thing I did was searching MySQL documentation. The solution was to change default character set by adding the following line to MySQL configuration file:
init-connect='SET NAMES cp1251'
The above line, basically, changes character set at the time of connection.
Unfortunately the above code did not work for me.
Step 2:
Since tweaking MySQL settings did not yield any positive results, I’ve decided to check apache language configuration. I’ve added extra encoding options and changed language priorities – nothing.
Step 3:
Did more searching and found that using SET NAMES command should definitely work from within the script. Gave it a shot and it did, in fact, work. Theoretically, that solves the problem. On practice, however, is not very convenient, nor does it lead to portable code.
Step 4:
I figured that the command SET NAMES worked just fine, but for some reason did not execute from the MySQL configuration file – my assumption was correct.
The script was establishing connection under ‘root’ user, which has all of the privileges – ironically that is what caused the problem. Apparently, any connection established from a user with SUPER privilege, will not trigger init-connect.
Sure enough, creating a user with appropriate privileges solved the problem.
Conclusion
Just to recap – the following things should be done in order to solve the problem:
1. Add the following line to MySQL configuration file, somewhere in [mysqld] section.
init-connect='SET NAMES cp1251'
2. Make sure that your script is connecting with a user that does not have SUPER privilege.
Categories
Search
Popular posts
- Mootools Slide Effect: expand all, collapse all or use as accordion
- Live search with CodeIgniter and Mootools
- Unobtrusive AJAX with CodeIgniter and Mootools
- Mootools text drop-shadow
- CodeIgniter model productivity methods
oron says:
thanks, very thanks!
today i have the same problem …
i dont know why
init-coonect=SET NAMES utf8
dont works, i try it on my test pc
and of course i use root for mysql access
very thanks for your work!
oron