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

Charset problem.

ozgurerdogan posted 1 year ago in General
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 posted 1 year ago
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.
ozgurerdogan posted 1 year ago
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 posted 1 year ago
May be, but it's still important to examine that table definition if you expect help.
ozgurerdogan posted 1 year ago
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 posted 1 year ago
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.
ozgurerdogan posted 1 year ago
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 posted 1 year ago
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.
ozgurerdogan posted 1 year ago
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 posted 1 year ago
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.
ozgurerdogan posted 1 year ago
I have 8.0.0.4522. Still do not think it is a bug but it is usual to see such event.
kalvaro posted 1 year ago
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.
ozgurerdogan posted 1 year ago
Yes but I already tried utf-8 and also other possibilites. But current collation which is latin1 swedish_ci can display Turkish charsets.
ansgar posted 1 year ago
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.
ozgurerdogan posted 1 year ago
ı ş ğ ü ç ö for example.
jfalch posted 1 year ago
"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 posted 1 year ago
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?
ozgurerdogan posted 1 year ago
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 posted 1 year ago
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.
ozgurerdogan posted 1 year ago
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...
ozgurerdogan posted 1 year ago
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 posted 1 year ago
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.
ozgurerdogan posted 1 year ago
"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 posted 1 year ago
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.
ozgurerdogan posted 1 year ago
Please connect to my pc it is much easier and safer.
ansgar posted 1 year ago
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.
ozgurerdogan posted 1 year ago
Please describe how to do that. I do not see an option in grid export dialog

ansgar posted 1 year ago
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
ozgurerdogan posted 1 year ago
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 posted 1 year ago
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?
ozgurerdogan posted 1 year ago
I tried tickin "Convert Data" option next to table collation and now I can save these chars.
ozgurerdogan posted 1 year ago
But still not ş char with even latin5 I guess after this point this is collotaion related?
ansgar posted 1 year ago
ş works here, using Latin5. Are you sure you have altered your table "domainlist" to use a Latin5 charset?
ozgurerdogan posted 1 year ago
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 posted 1 year ago
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.
ozgurerdogan posted 1 year ago
ok thank you very much for your efforts.

ansgar posted 1 year ago
No problem. Thanks for your quick feedback.

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