UTF 8

Hey everyone,

I have some problems with changing our database to UTF 8.
Someone told me I just need to switch the editor font to Arial Unicode MS and I need to set the connection for utf8 by running the query 'set character set 'utf8''. Unfortunatly this is not enough =( My test table still shows some cryptic letters and I have no idea how to change it.
Can anybody help me with this problem?

Thanks
Vanessa

Never run SET NAMES utf8 in HeidiSQL. Newer servers are automatically used in utf8 mode, and HeidiSQL expects this to be set when sending INSERTs and UPDATEs!

If your table shows broken characters, this is mostly due to a wrong collation of the table itself. See this similar thread.

To analyze this, you should post the CREATE TABLE statement, you can get it with

SHOW CREATE TABLE mytable;

Thanks by now!
Here is the create Table statement:

CREATE TABLE `mailings_utf8` (
`ID` int(111) unsigned NOT NULL auto_increment,
`datum` datetime default NULL,
`sesid` varchar(50) default NULL,
`bild` int(11) default NULL,
`price` double default NULL,
`e_geschlecht` varchar(50) NOT NULL default '',
`e_nachname` varchar(40) NOT NULL default '',
`e_vorname` varchar(40) NOT NULL default '',
`e_strasse` varchar(40) NOT NULL default '',
`e_ort` varchar(40) NOT NULL default '',
`e_plz` varchar(10) NOT NULL default '',
`e_land` varchar(20) NOT NULL default '',
`e_email` varchar(100) default NULL,
`e_fon` varchar(250) default NULL,
`e_fax` varchar(100) default NULL,
`e_mobile` varchar(250) default NULL,
`a_geschlecht` varchar(5) NOT NULL default '',
`a_nachname` varchar(100) NOT NULL default '',
`a_vorname` varchar(80) NOT NULL default '',
`a_strasse` varchar(40) NOT NULL default '',
`a_ort` varchar(40) NOT NULL default '',
`a_plz` varchar(6) NOT NULL default '',
`a_land` varchar(25) NOT NULL default '',
`a_email` varchar(45) default NULL,
`a_fon` varchar(45) default NULL,
`a_fax` varchar(40) default NULL,
`a_mobile` varchar(40) default NULL,
`message` text NOT NULL,
`ipadr` varchar(32) default NULL,
`KundenID` int(11) NOT NULL default '0',
`e_firma` varchar(100) default NULL,
`TNR` varchar(255) default '0',
`Rueckseite` tinyint(3) unsigned NOT NULL default '1',
`newsletter` tinyint(3) unsigned NOT NULL default '0',
`kat` int(10) unsigned NOT NULL default '1',
`gedruckt` int(10) unsigned NOT NULL default '0',
`test` tinyint(1) unsigned NOT NULL default '0',
`new` int(10) unsigned NOT NULL default '0',
`format` int(11) unsigned NOT NULL default '1',
`bildpfad` varchar(255) default NULL,
`messagebild` varchar(250) NOT NULL default '',
`upload` tinyint(1) unsigned NOT NULL default '0',
`a_firma` varchar(40) NOT NULL default '',
`code` varchar(255) default NULL,
`fg_price` varchar(40) NOT NULL default '',
`fg_sessionid` text,
`fg_userid` varchar(100) NOT NULL default '',
`fg_contentid` varchar(255) NOT NULL default '',
`adresse_komplett` tinytext,
`bezahlt` tinyint(1) unsigned NOT NULL default '0',
`font` int(10) unsigned NOT NULL default '3',
PRIMARY KEY (`ID`)
) ENGINE=MyISAM AUTO_INCREMENT=64 DEFAULT CHARSET=utf8

I am sorry I not that much familiar with all this database stuff - so I don't know if there is anything wrong =(

Looks ok to me so far, default charset is UTF8. Now it is most likely a data problem. Mostly because that table was copied between server upgrades from a pre 4.1 version to a newer one.

Do you have access to your data directory of the server? If so, could you mail me these files - of course only if they don't contain confidential information?

Anse, could you explain better that 'never run SET NAMES utf8 in HeidiSQL'? It's not something that I use often, but I recall doing this when copying stuff from some remote server database to a local copy - last time must have been 2008 or so. I found that that command was the key to a safe transfer of non-ASCII content both in HeidiSQL and in MySQL Workbench...

Does using SET NAMES break something? Every database I could concievably connect to has a UTF-8 charset and is tipically populated by a PHP script that uses SET NAMES as soon as it connects.

Yes, sure, HeidiSQL also sets the character set to UTF-8, but that is done internally if server version allows Unicode communication. If not, many internal functions rely on result stuff being Ansi strings. So, in order not to confuse things, SET NAMES should never be fired by the user in Heidi, at least not if you don't know exactly what you are doing. Imagine you call "SET NAMES latin1" and Heidi still expects results to be utf8 - your data grids and much other stuff should appear with broken characters.

Thank you.