We have a table called site_tags with a primary key made up of 2 fields - id and tag.
Some of the tag fields are in UTF-8 which we want to convert to ISO-8859-1.
In this table there are 2 tags Seany and seány and we want to convert the latter from UTF-8.
When we try to do this using...
DELETE FROM site_tags WHERE id = '1325133476' AND tag = 'seány'
INSERT INTO site_tags (id, tag, active) VALUES ('1325133476', 'seány', '0')
MySQL gives the error on the insert:
Duplicate entry '1325133476-seány' for key 'PRIMARY'
This seems because it is finding the Seany tag so thinks it's a duplicate, MySQL is not being character set sensitive (even though the delete worked on the correct record).
The database is using the latin1_swedish_ci collation and MySQL is v5.1 (InnoDB)
Any advice on how we can acheive this?
I would expect an UPDATE statement to work, but I might be missing something. (I don't have to deal with character sets very much.) Alter your foreign keys to cascade updates if you need to. Untested . . .
update site_tags
set tag = 'seány'
where id = '1325133476' AND tag = 'seány';
But I think the safer approach might be to create a new column that has the right encoding, update it through MySQL's convert() function, then fix up the keys. (Create a new column, create a unique constraint on {id, new_column}--that's in addition to the existing constraints--update the new column, etc.)
update site_tags
set new_column = convert(tag using latin2)
where new_column is null;
I'm not sure latin2 is right; MySQL docs describe it as "ISO 8859-2 Central European".
If you still get duplicate key errors, I'd expect to find that two different values in UTF8 map to a single value in ISO-8859-1.
It must be the way PHP is passing the concatenated string to MySql. Please, to debug, use echo instead of mysqli.query (or whatever) and be sure (that at least for debugging purposes) you are using the same charset on header and connection
mysqli.query(SET CHARACTER SET latin1;); // latin1 matches ISO-8859-1 on mysql connection
header('Content-Type: text/html; charset=iso-8859-1');