Charset problem.

[expired user #7268]'s profile image [expired user #7268] posted 11 years ago in General Permalink
I do not think I faced this issue before. When I type a Turkish char it turns into ? and saves so. I am using latest version. And other client softwares are fine. I also tried different collocations but still same. Is this a bug?
ansgar's profile image ansgar posted 11 years ago Permalink
No clue, as I need your table definition code to give you some help here. I don't think this is a HeidiSQL bug, as charset problems were faced well in the past, and thoroughly tested. But as said, it could be your table definition is the key to this problem.
[expired user #7268]'s profile image [expired user #7268] posted 11 years ago Permalink
This is awbs - a ready script which is widely used - www.awbs.com not my db. I am just editing its value as I always used to do.
ansgar's profile image ansgar posted 11 years ago Permalink
May be, but it's still important to examine that table definition if you expect help.
[expired user #7268]'s profile image [expired user #7268] posted 11 years ago Permalink
It is not only one table. Any table and any database... I tried different collactions but did not help. Why would charset issue is related definition? It is char issue not function I guess.
ansgar's profile image ansgar posted 11 years ago Permalink
You don't need to post all table definitions here, just one does it. 95% of the charset issues discussed here in the forum were table definition related in the end.
[expired user #7268]'s profile image [expired user #7268] posted 11 years ago Permalink
Here itis:

CREATE TABLE `users` (
`id` INT(8) NOT NULL AUTO_INCREMENT,
`username` VARCHAR(255) NULL DEFAULT NULL,
`password` VARCHAR(150) NULL DEFAULT NULL,
`fname` VARCHAR(75) NULL DEFAULT NULL,
`lname` VARCHAR(75) NULL DEFAULT NULL,
`email` VARCHAR(255) NULL DEFAULT NULL,
`second_email` VARCHAR(255) NOT NULL DEFAULT '',
`jobtitle` VARCHAR(75) NULL DEFAULT NULL,
`add1` VARCHAR(100) NULL DEFAULT NULL,
`add2` VARCHAR(100) NULL DEFAULT NULL,
`city` VARCHAR(50) NULL DEFAULT NULL,
`state` VARCHAR(50) NULL DEFAULT NULL,
`org` VARCHAR(50) NULL DEFAULT NULL,
`zip` VARCHAR(15) NULL DEFAULT NULL,
`country` VARCHAR(50) NULL DEFAULT NULL,
`fax` VARCHAR(15) NULL DEFAULT NULL,
`phone` VARCHAR(15) NULL DEFAULT NULL,
`date` VARCHAR(25) NULL DEFAULT NULL,
`spam` INT(5) NOT NULL DEFAULT '1',
`admin` INT(5) NOT NULL DEFAULT '0',
`active` INT(5) NOT NULL DEFAULT '0',
`province` VARCHAR(50) NULL DEFAULT NULL,
`rspchoice` VARCHAR(5) NULL DEFAULT NULL,
`discount` DECIMAL(10,3) NOT NULL DEFAULT '0.000',
`countrycode` VARCHAR(100) NULL DEFAULT NULL,
`lang` VARCHAR(100) NULL DEFAULT NULL,
`instant_credit` INT(5) NOT NULL DEFAULT '0',
`lasttrans` VARCHAR(25) NULL DEFAULT NULL,
`lastlogin` VARCHAR(25) NULL DEFAULT NULL,
`lastip` VARCHAR(20) NULL DEFAULT NULL,
`signup_date` VARCHAR(25) NULL DEFAULT NULL,
`billing_type` VARCHAR(255) NULL DEFAULT NULL,
`billing_last4` VARCHAR(255) NULL DEFAULT NULL,
`billing_card` VARCHAR(255) NULL DEFAULT NULL,
`billing_num` VARCHAR(255) NULL DEFAULT NULL,
`billing_exp_mo` VARCHAR(255) NULL DEFAULT NULL,
`billing_exp_yr` VARCHAR(255) NULL DEFAULT NULL,
`billing_name` VARCHAR(255) NULL DEFAULT NULL,
`billing_address` VARCHAR(255) NULL DEFAULT NULL,
`billing_city` VARCHAR(255) NULL DEFAULT NULL,
`billing_state` VARCHAR(255) NULL DEFAULT NULL,
`billing_zip` VARCHAR(255) NULL DEFAULT NULL,
`billing_country` VARCHAR(255) NULL DEFAULT NULL,
`billing_phone` VARCHAR(35) NOT NULL DEFAULT '',
`billing_bank` VARCHAR(255) NULL DEFAULT NULL,
`billing_bankno` VARCHAR(255) NULL DEFAULT NULL,
`billing_code` VARCHAR(5) NULL DEFAULT NULL,
`billing_misc1` VARCHAR(50) NULL DEFAULT NULL,
`billing_misc2` VARCHAR(50) NULL DEFAULT NULL,
`billing_misc3` VARCHAR(50) NULL DEFAULT NULL,
`refill_notify` INT(5) NOT NULL DEFAULT '1',
`suspend_exempt` INT(5) NOT NULL DEFAULT '0',
`mail_type` INT(5) NOT NULL DEFAULT '1',
`custom1` VARCHAR(255) NOT NULL DEFAULT '',
`custom2` VARCHAR(255) NOT NULL DEFAULT '',
`custom3` VARCHAR(255) NOT NULL DEFAULT '',
`custom4` VARCHAR(255) NOT NULL DEFAULT '',
`custom5` VARCHAR(255) NOT NULL DEFAULT '',
`custom6` VARCHAR(255) NOT NULL DEFAULT '',
`custom7` VARCHAR(255) NOT NULL DEFAULT 'all',
`di_username` VARCHAR(255) NULL DEFAULT NULL,
`di_cid` VARCHAR(255) NULL DEFAULT NULL,
`di_dcid` VARCHAR(255) NULL DEFAULT NULL,
`di_password` VARCHAR(255) NULL DEFAULT NULL,
`custom8` VARCHAR(255) NULL DEFAULT NULL,
`custom9` VARCHAR(255) NULL DEFAULT NULL,
`custom10` VARCHAR(255) NULL DEFAULT NULL,
`lpquestion` VARCHAR(255) NOT NULL DEFAULT '0',
`lpanswer` VARCHAR(255) NOT NULL DEFAULT '',
`is_validated` INT(5) NOT NULL DEFAULT '0',
`fraud_bypass` INT(5) NOT NULL DEFAULT '0',
`fraud_phone_bypass` INT(5) NOT NULL DEFAULT '0',
`fraud_phone_cc_verified` INT(5) NOT NULL DEFAULT '0',
`fraud_phone_ca_verified` INT(5) NOT NULL DEFAULT '0',
`fraud_distance` VARCHAR(10) NOT NULL DEFAULT '',
`fraud_country_match` VARCHAR(10) NOT NULL DEFAULT '',
`fraud_anon_proxy` VARCHAR(10) NOT NULL DEFAULT '',
`fraud_proxy_score` VARCHAR(10) NOT NULL DEFAULT '',
`fraud_spam_score` VARCHAR(10) NOT NULL DEFAULT '',
`cdmember` INT(5) NOT NULL DEFAULT '0',
`forum_acct` INT(5) NOT NULL DEFAULT '0',
`groupname` INT(5) NOT NULL DEFAULT '1',
`tax_exempt` INT(5) NOT NULL DEFAULT '0',
`resell_max` INT(5) NOT NULL DEFAULT '0',
`resell_count` INT(5) NOT NULL DEFAULT '0',
`resell_accts` MEDIUMTEXT NULL,
`trusted_user` INT(5) NOT NULL DEFAULT '0',
`signature` VARCHAR(255) NOT NULL DEFAULT '',
`flagged_text` MEDIUMTEXT NULL,
`kay_user` VARCHAR(255) NULL DEFAULT NULL,
`kay_pass` VARCHAR(255) NULL DEFAULT NULL,
`react_override` INT(5) NOT NULL DEFAULT '0',
`dcc_override` INT(5) NOT NULL DEFAULT '0',
`fraud_phone_count` INT(5) NOT NULL DEFAULT '0',
`terms` INT(5) NOT NULL DEFAULT '0',
`bayi` INT(11) NOT NULL DEFAULT '0',
`seviye_no` INT(11) NOT NULL,
`indirim_orani` TEXT NOT NULL,
`indirim_kuponu` TEXT NOT NULL,
`onlycred` INT(5) NOT NULL DEFAULT '0',
`epassword` VARCHAR(255) NOT NULL,
`binv` INT(5) NOT NULL DEFAULT '0',
`designation` VARCHAR(255) NOT NULL DEFAULT '',
PRIMARY KEY (`id`),
INDEX `username` (`username`),
INDEX `password` (`password`),
INDEX `active` (`active`),
INDEX `admin` (`admin`)
)
COLLATE='latin1_swedish_ci'
ENGINE=MyISAM
AUTO_INCREMENT=1339;
ansgar's profile image ansgar posted 11 years ago Permalink
latin1_swedish_ci collation? That's the problem, man. Well I'm not sure if there are turkish characters in Latin1 but if not then they get broken here.

Or, as you say when you type such a char, "it turns into ?". So, you're probably using a font which is not capable of displaying such characters. You can change the font in Tools > Preferences > Data appearance.
[expired user #7268]'s profile image [expired user #7268] posted 11 years ago Permalink
latin1_swedish_ci supports Turkish charset, I have all db on this charset and runs fine. Also when I type char in Türkish, keyboard types it correctly but after saving it it turns into ?.
I tried different font but no change.
ansgar's profile image ansgar posted 11 years ago Permalink
Ok, then we have indeed some other issue here.

Which HeidiSQL revision are you using? I will try to reproduce here with the current build.
[expired user #7268]'s profile image [expired user #7268] posted 11 years ago Permalink
I have 8.0.0.4522. Still do not think it is a bug but it is usual to see such event.
kalvaro's profile image kalvaro posted 11 years ago Permalink
The Turkish alphabet has 3 characters that cannot fit into Latin 1: "ğ", "ı" (not to be confused with "i") and "ş". I wouldn't be suprised that those are the ones that get lost.
[expired user #7268]'s profile image [expired user #7268] posted 11 years ago Permalink
Yes but I already tried utf-8 and also other possibilites. But current collation which is latin1 swedish_ci can display Turkish charsets.
ansgar's profile image ansgar posted 11 years ago Permalink
Please post some characters which appear broken in your table, and please name the column in which you insert these, so I can try to reproduce now.
[expired user #7268]'s profile image [expired user #7268] posted 11 years ago Permalink
ı ş ğ ü ç ö for example.
jfalch's profile image jfalch posted 11 years ago Permalink
"But current collation which is latin1 swedish_ci can display Turkish charsets."
The collation by itself does nothing like that; it only determines the sort order of the table - cf here.
As its prefix says, latin1_swedish_ci implies usage of the charset latin1, which has the above mentioned limitations. If you want to set a different charset for your table, you have to use a collation that implies another character set, eg utf8_general_ci or perhaps latin5_turkish_ci.
ansgar's profile image ansgar posted 11 years ago Permalink
I can reproduce that. And exactly as kalvaro said, the three first characters fail to get stored well (ı ş ğ) while the others do (ü ç ö).

After altering the column's charset to latin5, as jfalch said, the three critical characters also store well.

... And other client softwares are fine. I also tried different collocations but still same. Is this a bug?


Which software did you use btw, and which collations did you test out? I guess you did not test with turkish characters, did you?
[expired user #7268]'s profile image [expired user #7268] posted 11 years ago Permalink
I did not change any collation. But anyway with latin5, altering value makes "0 rows updated when that should have been 1" error. Why is that then?
ansgar's profile image ansgar posted 11 years ago Permalink
Hm? You said "I also tried different collocations but still same"...

Which other software did you use?

That "0 rows updated when that..." warning is harmless, and is an effect of silent changes on the server. If you want details here, please post more details of what you did.
[expired user #7268]'s profile image [expired user #7268] posted 11 years ago Permalink
Toad for MySQL 6.7 does not turn char to ? but instead to i from ı.
Anyway I guess something is wrong with my db. I will try to see why it is being so...
[expired user #7268]'s profile image [expired user #7268] posted 11 years ago Permalink
No way, with latin5_sweedish_ci or some other colloation, I can not update a row with some chars like ı
With latin5.. I get 0 rows updated when that should have been 1 which does not allow changes to be saved. You may connect my pc and see your self.
ansgar's profile image ansgar posted 11 years ago Permalink
Please post an INSERT command for the row you are editing (rightclick the row in HeidiSQL > Export grid rows > [*] Selected rows)

Then, I can try to reproduce that here with your above CREATE TABLE definition.
[expired user #7268]'s profile image [expired user #7268] posted 11 years ago Permalink
"tld" "sld" "registrar" "domainid" "expiredate" "ownerid" "regtype" "regstatus" "seed" "dseed" "id" "transferid" "date" "f1" "f2" "f3" "f4" "f5" "DNS" "fs" "afid" "auto_renew" "f6" "f7" "f8" "f9" "f10" "coupon" "lost_date" "locked" "registrar_type" "pps" "idp_date" "idp_email" "custip" "profile" "arinv" "config_1" "config_2" "config_3" "idn" "idnname" "designation" "price_override" "price_override_expires"
"abc.tr" "abcdefg?" "Generic" "" "1399248000" "somevalue" "Renew" "Closed" "512759580" "385108099" "875" \N "1273093200" "0" "0" "0" "0" "0" \N \N "" "1" "0" "0" "0" "0" "0" "" "" "0" "Generic" "0" "" \N "" "0" "4684" "" "" "" "0" "" "" "" ""
ansgar's profile image ansgar posted 11 years ago Permalink
This is not SQL, and it has less values than your table "users" has. Please post a critical INSERT from the users table, and check "SQL INSERT" in the grid export dialog.
[expired user #7268]'s profile image [expired user #7268] posted 11 years ago Permalink
Please connect to my pc it is much easier and safer.
ansgar's profile image ansgar posted 11 years ago Permalink
I'm afraid I'm just sitting behind a firewall which allows me to go out on port 80 but nothing more.. So, please just post that INSERT here, should not be too hard.
[expired user #7268]'s profile image [expired user #7268] posted 11 years ago Permalink
Please describe how to do that. I do not see an option in grid export dialog
ansgar's profile image ansgar posted 11 years ago Permalink
1. Select the row on which you got "0 rows updated when..." in the data grid
2. right click on that row, click "Export grid rows"
3. Select output target: "Copy to clipboard"
4. Select format: "SQL INSERTs"
5. Select selection: "Selected rows (1 row)"
6. Click OK button
7. Paste here and post that
[expired user #7268]'s profile image [expired user #7268] posted 11 years ago Permalink
INSERT INTO `domainlist` (`tld`, `sld`, `registrar`, `domainid`, `expiredate`, `ownerid`, `regtype`, `regstatus`, `seed`, `dseed`, `id`, `transferid`, `date`, `f1`, `f2`, `f3`, `f4`, `f5`, `DNS`, `fs`, `afid`, `auto_renew`, `f6`, `f7`, `f8`, `f9`, `f10`, `coupon`, `lost_date`, `locked`, `registrar_type`, `pps`, `idp_date`, `idp_email`, `custip`, `profile`, `arinv`, `config_1`, `config_2`, `config_3`, `idn`, `idnname`, `designation`, `price_override`, `price_override_expires`) VALUES ('gen.tr', 'somevalue?', 'Generic', '', '1399248000', 'somevalue', 'Renew', 'Closed', '512759580', '385108099', 875, NULL, '1273093200', 0, 0, 0, 0, 0, NULL, NULL, '', 1, 0, 0, 0, 0, 0, '', '', 0, 'Generic', 0, '', NULL, '', 0, 4684, '', '', '', 0, '', '', '', '');
ansgar's profile image ansgar posted 11 years ago Permalink
I have no problem inserting a "ı" character, for example in the "registrar" column, by changing the "Generic" value to "Generıc. Works like a charm. Of course I have created that domainlist table with latin5 charset.

Which column did you update when getting that "0 rows updated when..." error?
[expired user #7268]'s profile image [expired user #7268] posted 11 years ago Permalink
I tried tickin "Convert Data" option next to table collation and now I can save these chars.
[expired user #7268]'s profile image [expired user #7268] posted 11 years ago Permalink
But still not ş char with even latin5 I guess after this point this is collotaion related?
ansgar's profile image ansgar posted 11 years ago Permalink
ş works here, using Latin5. Are you sure you have altered your table "domainlist" to use a Latin5 charset?
[expired user #7268]'s profile image [expired user #7268] posted 11 years ago Permalink
Ok thanks for helps. I did not tick convert data option with latin5 so now it is ok. Thanks for enterprise level help :)

All now fine. One question, do charset validations usualy made at code level? I did not want to damage db.
ansgar's profile image ansgar posted 11 years ago Permalink
The right charset must be used by the application in question, in a mysql client like heidi the mysql_set_charset() function should be set to utf8 in order to cover all possible characters. On the table level, a Latin1 charset supports fewer characters. That's why you can *type* all utf8 chars, but the table on the server returns broken chars because it cannot *store* all of them. In a web application, which is probably what you meant, you need to do the same thing: connect to mysql and call mysql_set_charset() (Not "SET NAMES utf8" by the way!). Then, if you PHP files or whatever contain critical characters, you need to store these in the same characters set.
[expired user #7268]'s profile image [expired user #7268] posted 11 years ago Permalink
ok thank you very much for your efforts.
ansgar's profile image ansgar posted 11 years ago Permalink
No problem. Thanks for your quick feedback.

Please login to leave a reply, or register at first.