Changing field collation to utf-8 causes duplicate key error

Question

Why does simply changing the collation cause a duplicate key error?

mysql> describe phppos_items;
+-----------------------+--------------+------+-----+---------+----------------+
| Field                 | Type         | Null | Key | Default | Extra          |
+-----------------------+--------------+------+-----+---------+----------------+
| name                  | varchar(255) | NO   | MUL | NULL    |                |
| category              | varchar(255) | NO   | MUL | NULL    |                |
| supplier_id           | int(11)      | YES  | MUL | NULL    |                |
| item_number           | varchar(255) | YES  | UNI | NULL    |                |
| description           | varchar(255) | NO   |     | NULL    |                |
| cost_price            | double(15,2) | NO   |     | NULL    |                |
| unit_price            | double(15,2) | NO   |     | NULL    |                |
| quantity              | double(15,2) | NO   |     | 0.00    |                |
| reorder_level         | double(15,2) | NO   |     | 0.00    |                |
| location              | varchar(255) | NO   |     | NULL    |                |
| item_id               | int(10)      | NO   | PRI | NULL    | auto_increment |
| allow_alt_description | tinyint(1)   | NO   |     | NULL    |                |
| is_serialized         | tinyint(1)   | NO   |     | NULL    |                |
| deleted               | int(1)       | NO   | MUL | 0       |                |
+-----------------------+--------------+------+-----+---------+----------------+
14 rows in set (0.01 sec)

mysql> ALTER TABLE  `phppos_items` CHANGE  `name`  `name` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL , CHANGE  `category`  `category` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL , CHANGE  `item_number`  `item_number` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL , CHANGE  `description`  `description` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL , CHANGE  `location`  `location` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL;
ERROR 1062 (23000): Duplicate entry ' ' for key 'item_number'

Create table:

| phppos_items | CREATE TABLE `phppos_items` (
  `name` varchar(255) CHARACTER SET latin1 NOT NULL,
  `category` varchar(255) CHARACTER SET latin1 NOT NULL,
  `supplier_id` int(11) DEFAULT NULL,
  `item_number` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
  `description` varchar(255) CHARACTER SET latin1 NOT NULL,
  `cost_price` double(15,2) NOT NULL,
  `unit_price` double(15,2) NOT NULL,
  `quantity` double(15,2) NOT NULL DEFAULT '0.00',
  `reorder_level` double(15,2) NOT NULL DEFAULT '0.00',
  `location` varchar(255) CHARACTER SET latin1 NOT NULL,
  `item_id` int(10) NOT NULL AUTO_INCREMENT,
  `allow_alt_description` tinyint(1) NOT NULL,
  `is_serialized` tinyint(1) NOT NULL,
  `deleted` int(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`item_id`),
  UNIQUE KEY `item_number` (`item_number`),
  KEY `phppos_items_ibfk_1` (`supplier_id`),
  KEY `name` (`name`),
  KEY `category` (`category`),
  KEY `deleted` (`deleted`),
  CONSTRAINT `phppos_items_ibfk_1` FOREIGN KEY (`supplier_id`) REFERENCES `phppos_suppliers` (`person_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1560 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |

Answer 1

To fix that, you need to know which rows have duplicate utf-8 values.

You can do that by running this query:

SELECT * FROM (
    SELECT CONVERT(YOUR_COLUMN USING utf8mb4) AS c FROM YOUR_TABLE
) AS A GROUP BY c HAVING COUNT(*) > 1