I am using the MySQLdb python module to connect to a database. The method I am connecting to it is the following:

import MySQLdb as mysql

mysql_connection = mysql.connect(user=user, passwd=password, db=db,
                                 charset='utf8', use_unicode=True)
cursor           = mysql_connection.cursor()

# error checking snip here
# (ommitted for brevity)

return (mysql_connection, cursor)

Against this connection, I am executing queries that contain utf-8 strings (unicode objects in python), like this:

[DEBUG] INSERT INTO Clients(clientid, login, pname, email) VALUES (304, 'sample_username', 'Φώτης Κ', '[email protected]');

However I find that the data entered in the actual database are wrong, and are actually presented like this:

??????? ????????

I have actually confirmed that mysql is setup to accept unicode strings, as I have executed queries by hand that contain utf-8 characters and they are successful.

The result of the SHOW VARIABLES LIKE "character_set%" command is the following:

mysql> SHOW VARIABLES LIKE "character_set%";
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | latin1                     |
| character_set_connection | latin1                     |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | latin1                     |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

and my schema for that particular table (at least for the relevant columns) is this:

DROP TABLE IF EXISTS `Clients`;
CREATE TABLE `Clients` (
    ...
    `login` VARCHAR(200) CHARACTER SET utf8,
    `pname` VARCHAR(255) CHARACTER SET utf8,
    `email` VARCHAR(255) CHARACTER SET utf8,
    ...
    );

Also, my terminal is setup to have $LC_ALL and $LANG setup to el_GR.utf8. What is possibly at fault here?

Sigh...

As with everything, I found the answer after I experimented a bit more myself. It seems that everything inserted is actually there, just that the MySQL Client didn't show it correctly.

As you could see from the character set results I posted in the question:

mysql> SHOW VARIABLES LIKE "character_set%";
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | latin1                     |
| character_set_connection | latin1                     |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | latin1                     |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

character_set_results are set to latin1. Executing SET character_set_results=utf8; managed to fix everything and it's working as expected.