PDO utf-8 issues

Had issues with json_encode complaining:

Invalid UTF-8 sequence in argument 

Fixed it by adding

PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"

to the PDO creation line.. ie:

$pdo = new PDO(
    'mysql:host=mysql.example.com;dbname=example_db',
    "username",
    "password",
    array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));

source

Displaying and Configuring Character Sets in MySQL

Display all character sets:

show variables like 'character_set%';

Change the character set for a table:

ALTER TABLE tbl_name CONVERT TO CHARACTER SET charset_name;

BUT with the caveat that

The CONVERT TO operation converts column values between the character sets. This is not what you want if you have a column in one character set (like latin1) but the stored values actually use some other, incompatible character set (like utf8). In this case, you have to do the following for each such column:

ALTER TABLE t1 CHANGE c1 c1 BLOB;
ALTER TABLE t1 CHANGE c1 c1 TEXT CHARACTER SET utf8;

The reason this works is that there is no conversion when you convert to or from BLOB columns.


The process to completely change all mysql's character sets from latin-1 and make this garbage:
(using the show variables command from above)

+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | latin1                     |
| character_set_connection | latin1                     |
| character_set_database   | latin1                     |
| character_set_filesystem | binary                     |
| character_set_results    | latin1                     |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+

look beautiful like this:

+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | utf8                       |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+

involves simply adding the following to the [mysqld] section of your my.cnf (or /etc/mysql/my.cnf if you like, and are on a debian based machine)

collation_server=utf8_unicode_ci
character_set_server=utf8

and adding the following to BOTH the [client] section AND the [mysqld] section:

default-character-set=utf8


This is simply in case you cannot go back and re-compile mysql using
./configure --with-charset=utf8 --with-collation=utf8_general_ci

Mysql connection error 2003 (HY000) (146)

Tried logging into a remote mysql server and got an error:

ERROR 2003 (HY000): Can't connect to MySQL server on 'someserver' (146)

In this case, it was due to bind-address being set to 127.0.0.1in the /etc/my.cnf file (/opt/local/etc/my.cnf). The issue arose on a machine that used to be a slave after I had converted it back into a stand alone mysql server.