Reproduction with latin1: Server version: 5.0.51a-community-log MySQL Community Edition (GPL) Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> show variables like '%colla%'; +----------------------+-------------------+ | Variable_name | Value | +----------------------+-------------------+ | collation_connection | latin1_swedish_ci | | collation_database | latin1_spanish_ci | | collation_server | latin1_spanish_ci | +----------------------+-------------------+ 3 rows in set (0.00 sec) mysql> set collation_connection=latin1_spanish_ci; Query OK, 0 rows affected (0.00 sec) mysql> show variables like '%char%'; +--------------------------+----------------------------+ | 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/ | +--------------------------+----------------------------+ 8 rows in set (0.00 sec) mysql> use test mysql> show full columns from documents; +------------+--------------+-------------------+------+-----+---------+----------------+---------------------------------+---------+ | Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment | +------------+--------------+-------------------+------+-----+---------+----------------+---------------------------------+---------+ | id | int(11) | NULL | NO | PRI | NULL | auto_increment | select,insert,update,references | | | group_id | int(11) | NULL | NO | | NULL | | select,insert,update,references | | | group_id2 | int(11) | NULL | NO | | NULL | | select,insert,update,references | | | date_added | datetime | NULL | NO | | NULL | | select,insert,update,references | | | title | varchar(255) | latin1_spanish_ci | YES | UNI | NULL | | select,insert,update,references | | | content | varchar(255) | latin1_spanish_ci | YES | | NULL | | select,insert,update,references | | +------------+--------------+-------------------+------+-----+---------+----------------+---------------------------------+---------+ mysql> insert into documents values (1,6,6,'2008-06-05 10:24:13', 'spanish char test (index)', 'spanish char test ññññíóú'); Query OK, 1 row affected (0.00 sec) mysql> select * from documents; +----+----------+-----------+---------------------+---------------------------+---------------------------+ | id | group_id | group_id2 | date_added | title | content | +----+----------+-----------+---------------------+---------------------------+---------------------------+ | 1 | 6 | 6 | 2008-06-05 10:24:13 | spanish char test (index) | spanish char test ññññíóú | +----+----------+-----------+---------------------+---------------------------+---------------------------+ 1 row in set (0.00 sec) mysql> select * from documents; +----+----------+-----------+---------------------+---------------------------+---------------------------+ | id | group_id | group_id2 | date_added | title | content | +----+----------+-----------+---------------------+---------------------------+---------------------------+ | 1 | 6 | 6 | 2008-06-05 10:24:13 | spanish char test (index) | spanish char test ññññíóú | | 2 | 6 | 6 | 2008-06-05 10:24:13 | tilde and ñ test íoúúóé | spanish char test ññññíóú | +----+----------+-----------+---------------------+---------------------------+---------------------------+ mysql> insert into documents values (3,6,6,'2008-06-05 10:24:13', 'ívantheterrible', 'spanish char test ññññíóú'); Query OK, 1 row affected (0.00 sec) mysql> insert into documents values (4,6,6,'2008-06-05 10:24:13', 'çatcopy', 'spanish char test ññññíóú'); Query OK, 1 row affected (0.00 sec) mysql> insert into documents values (5,6,6,'2008-06-05 10:24:13', 'ñothingtodo', 'spanish char test ññññíóú'); Query OK, 1 row affected (0.00 sec) mysql> select * from documents; +----+----------+-----------+---------------------+---------------------------+---------------------------+ | id | group_id | group_id2 | date_added | title | content | +----+----------+-----------+---------------------+---------------------------+---------------------------+ | 1 | 6 | 6 | 2008-06-05 10:24:13 | spanish char test (index) | spanish char test ññññíóú | | 2 | 6 | 6 | 2008-06-05 10:24:13 | tilde and ñ test íoúúóé | spanish char test ññññíóú | | 3 | 6 | 6 | 2008-06-05 10:24:13 | ívantheterrible | spanish char test ññññíóú | | 4 | 6 | 6 | 2008-06-05 10:24:13 | çatcopy | spanish char test ññññíóú | | 5 | 6 | 6 | 2008-06-05 10:24:13 | ñothingtodo | spanish char test ññññíóú | +----+----------+-----------+---------------------+---------------------------+---------------------------+ 5 rows in set (0.00 sec) Now we go for the inserts of no tilde, n, and c: mysql> insert into documents values (6,6,6,'2008-06-05 10:24:13', 'nothingtodo', 'spanish char test ññññíóú'); Query OK, 1 row affected (0.00 sec) mysql> insert into documents values (7,6,6,'2008-06-05 10:24:13', 'catcopy', 'spanish char test ññññíóú'); ERROR 1062 (23000): Duplicate entry 'catcopy' for key 2 mysql> insert into documents values (8,6,6,'2008-06-05 10:24:13', 'ivantheterrible', 'spanish char test ññññíóú'); ERROR 1062 (23000): Duplicate entry 'ivantheterrible' for key 2 =================================================== Reproduction with utf8: mysql> show variables like '%colla%'; +----------------------+-------------------+ | Variable_name | Value | +----------------------+-------------------+ | collation_connection | latin1_swedish_ci | | collation_database | latin1_spanish_ci | | collation_server | latin1_spanish_ci | +----------------------+-------------------+ 3 rows in set (0.00 sec) mysql> set collation_connection=utf8_spanish_ci; Query OK, 0 rows affected (0.00 sec) mysql> set collation_database=utf8_spanish_ci; Query OK, 0 rows affected (0.00 sec) mysql> set collation_server=utf8_spanish_ci; Query OK, 0 rows affected (0.00 sec) mysql> show variables like '%char%'; +--------------------------+----------------------------+ | Variable_name | Value | +--------------------------+----------------------------+ | character_set_client | latin1 | | character_set_connection | utf8 | | character_set_database | utf8 | | character_set_filesystem | binary | | character_set_results | latin1 | | character_set_server | utf8 | | character_set_system | utf8 | | character_sets_dir | /usr/share/mysql/charsets/ | +--------------------------+----------------------------+ mysql> show variables like '%char%'; +--------------------------+----------------------------+ | Variable_name | Value | +--------------------------+----------------------------+ | character_set_client | latin1 | | character_set_connection | utf8 | | character_set_database | utf8 | | character_set_filesystem | binary | | character_set_results | latin1 | | character_set_server | utf8 | | character_set_system | utf8 | | character_sets_dir | /usr/share/mysql/charsets/ | +--------------------------+----------------------------+ 8 rows in set (0.00 sec) mysql> set character_set_client=utf8; Query OK, 0 rows affected (0.00 sec) mysql> set character_set_results=utf8; Query OK, 0 rows affected (0.00 sec) mysql> show full columns from documents; +------------+--------------+-------------------+------+-----+---------+----------------+---------------------------------+---------+ | Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment | +------------+--------------+-------------------+------+-----+---------+----------------+---------------------------------+---------+ | id | int(11) | NULL | NO | PRI | NULL | auto_increment | select,insert,update,references | | | group_id | int(11) | NULL | NO | | NULL | | select,insert,update,references | | | group_id2 | int(11) | NULL | NO | | NULL | | select,insert,update,references | | | date_added | datetime | NULL | NO | | NULL | | select,insert,update,references | | | title | varchar(255) | latin1_spanish_ci | YES | UNI | NULL | | select,insert,update,references | | | content | varchar(255) | latin1_spanish_ci | YES | | NULL | | select,insert,update,references | | +------------+--------------+-------------------+------+-----+---------+----------------+---------------------------------+---------+ 6 rows in set (0.00 sec) mysql> alter table documents change title title varchar(255) CHARACTER SET utf8 COLLATE utf8_spanish_ci; Query OK, 6 rows affected (0.00 sec) Records: 6 Duplicates: 0 Warnings: 0 mysql> alter table documents change content content varchar(255) CHARACTER SET utf8 COLLATE utf8_spanish_ci; Query OK, 6 rows affected (0.01 sec) Records: 6 Duplicates: 0 Warnings: 0 mysql> insert into documents values (1,6,6,'2008-06-05 10:24:13', 'spanish char test (index)', 'spanish char test ññññíóú'); Query OK, 1 row affected (0.00 sec) mysql> insert into documents values (2,6,6,'2008-06-05 10:24:13', 'tilde and ñ test íoúúóé', 'spanish char test ññññíóú'); Query OK, 1 row affected (0.00 sec) mysql> insert into documents values (3,6,6,'2008-06-05 10:24:13', 'ívantheterrible', 'spanish char test ññññíóú'); Query OK, 1 row affected (0.00 sec) mysql> insert into documents values (4,6,6,'2008-06-05 10:24:13', 'çatcopy', 'spanish char test ññññíóú'); Query OK, 1 row affected (0.00 sec) mysql> insert into documents values (5,6,6,'2008-06-05 10:24:13', 'ñothingtodo', 'spanish char test ññññíóú'); Query OK, 1 row affected (0.00 sec) mysql> select * from documents; +----+----------+-----------+---------------------+---------------------------+---------------------------+ | id | group_id | group_id2 | date_added | title | content | +----+----------+-----------+---------------------+---------------------------+---------------------------+ | 1 | 6 | 6 | 2008-06-05 10:24:13 | spanish char test (index) | spanish char test ññññíóú | | 2 | 6 | 6 | 2008-06-05 10:24:13 | tilde and ñ test íoúúóé | spanish char test ññññíóú | | 3 | 6 | 6 | 2008-06-05 10:24:13 | ívantheterrible | spanish char test ññññíóú | | 4 | 6 | 6 | 2008-06-05 10:24:13 | çatcopy | spanish char test ññññíóú | | 5 | 6 | 6 | 2008-06-05 10:24:13 | ñothingtodo | spanish char test ññññíóú | +----+----------+-----------+---------------------+---------------------------+---------------------------+ 5 rows in set (0.00 sec) mysql> insert into documents values (6,6,6,'2008-06-05 10:24:13', 'nothingtodo', 'spanish char test ññññíóú'); Query OK, 1 row affected (0.00 sec) mysql> insert into documents values (7,6,6,'2008-06-05 10:24:13', 'catcopy', 'spanish char test ññññíóú'); ERROR 1062 (23000): Duplicate entry 'catcopy' for key 2 mysql> insert into documents values (8,6,6,'2008-06-05 10:24:13', 'ivantheterrible', 'spanish char test ññññíóú'); ERROR 1062 (23000): Duplicate entry 'ivantheterrible' for key 2