distal-attribute
distal-attribute
distal-attribute
distal-attribute

Charset problem.

User, date Message
Written by ozgurerdogan
11 months ago
Category: General
19 posts since Mon, 16 Sep 13
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?
Written by ansgar
11 months ago
4973 posts since Fri, 07 Apr 06
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.
Written by ozgurerdogan
11 months ago
19 posts since Mon, 16 Sep 13
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.
Written by ansgar
11 months ago
4973 posts since Fri, 07 Apr 06
May be, but it's still important to examine that table definition if you expect help.
Written by ozgurerdogan
11 months ago
19 posts since Mon, 16 Sep 13
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.
Written by ansgar
11 months ago
4973 posts since Fri, 07 Apr 06
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.
Written by ozgurerdogan
11 months ago
19 posts since Mon, 16 Sep 13
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;

Written by ansgar
11 months ago
4973 posts since Fri, 07 Apr 06
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.
Written by ozgurerdogan
11 months ago
19 posts since Mon, 16 Sep 13
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.
Written by ansgar
11 months ago
4973 posts since Fri, 07 Apr 06
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.
Written by ozgurerdogan
11 months ago
19 posts since Mon, 16 Sep 13
I have 8.0.0.4522. Still do not think it is a bug but it is usual to see such event.
Written by kalvaro
11 months ago
593 posts since Thu, 29 Nov 07
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.
Written by ozgurerdogan
11 months ago
19 posts since Mon, 16 Sep 13
Yes but I already tried utf-8 and also other possibilites. But current collation which is latin1 swedish_ci can display Turkish charsets.
Written by ansgar
11 months ago
4973 posts since Fri, 07 Apr 06
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.
Written by ozgurerdogan
11 months ago
19 posts since Mon, 16 Sep 13
ı ş ğ ü ç ö for example.
Written by jfalchMoney, Euro
11 months ago
386 posts since Sat, 17 Oct 09
"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.
Written by ansgar
11 months ago
4973 posts since Fri, 07 Apr 06
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.

ozgurerdogan wrote: ... 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?
Written by ozgurerdogan
11 months ago
19 posts since Mon, 16 Sep 13
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?
Written by ansgar
11 months ago
4973 posts since Fri, 07 Apr 06
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.
Written by ozgurerdogan
11 months ago
19 posts since Mon, 16 Sep 13
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...
Written by ozgurerdogan
11 months ago
19 posts since Mon, 16 Sep 13
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.
Written by ansgar
11 months ago
4973 posts since Fri, 07 Apr 06
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.
Written by ozgurerdogan
11 months ago
19 posts since Mon, 16 Sep 13
"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" "" "" "" ""
Written by ansgar
11 months ago
4973 posts since Fri, 07 Apr 06
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.
Written by ozgurerdogan
11 months ago
19 posts since Mon, 16 Sep 13
Please connect to my pc it is much easier and safer.
Written by ansgar
11 months ago
4973 posts since Fri, 07 Apr 06
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.
Written by ozgurerdogan
11 months ago
19 posts since Mon, 16 Sep 13
Please describe how to do that. I do not see an option in grid export dialog
Written by ansgar
11 months ago
4973 posts since Fri, 07 Apr 06
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
Written by ozgurerdogan
11 months ago
19 posts since Mon, 16 Sep 13
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, '', '', '', '');
Written by ansgar
11 months ago
4973 posts since Fri, 07 Apr 06
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?
Written by ozgurerdogan
11 months ago
19 posts since Mon, 16 Sep 13
I tried tickin "Convert Data" option next to table collation and now I can save these chars.
Written by ozgurerdogan
11 months ago
19 posts since Mon, 16 Sep 13
But still not ş char with even latin5 I guess after this point this is collotaion related?
Written by ansgar
11 months ago
4973 posts since Fri, 07 Apr 06
ş works here, using Latin5. Are you sure you have altered your table "domainlist" to use a Latin5 charset?
Written by ozgurerdogan
11 months ago
19 posts since Mon, 16 Sep 13
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.
Written by ansgar
11 months ago
4973 posts since Fri, 07 Apr 06
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.
Written by ozgurerdogan
11 months ago
19 posts since Mon, 16 Sep 13
ok thank you very much for your efforts.
Written by ansgar
11 months ago
4973 posts since Fri, 07 Apr 06
No problem. Thanks for your quick feedback.
 

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