Solved

polish characters in mysql change to ????

Posted on 2009-04-05
17
1,833 Views
Last Modified: 2013-12-12
I need to know the correct setup for using Polish characters.
I've tried a few collation types, but when I paste into the longtext field (using navicat):

BDó[|z
ACÓZ{y

it pastes correctly but as soon as I click back in the field it becomes:

????????ó???
????????Ó???
0
Comment
Question by:freshwaterwest
  • 10
  • 7
17 Comments
 

Author Comment

by:freshwaterwest
ID: 24072898
unfortunately the polish characters have come out wrong in the question above also - the correct characters are attached as png
Picture-4.png
0
 
LVL 15

Expert Comment

by:oobayly
ID: 24072958
What charset are you using, I've just tried it successfuly with this column:
VARCHAR(255) CHARACTER SET utf8 COLLATION utf8_genersal_ci

Used the polish chars from this page:
http://www.geocities.com/click2speak/unicode/chars_pl.html
0
 

Author Comment

by:freshwaterwest
ID: 24073004
I've tried with CHARACTER SET utf8 COLLATION utf8_genersal_ci but still won't work -
Should I be able to copy a character and paste it straight in - or do I need to convert and use code?
0
 

Author Comment

by:freshwaterwest
ID: 24073020
tried phpmyadmin and got the following:
 Warning: #1366 Incorrect string value: '\xC4\x84' for column 'text' at row 1

0
 

Author Comment

by:freshwaterwest
ID: 24073043
this was attempting to copy/paste the A first example from the chars link in your first comment
0
 
LVL 15

Expert Comment

by:oobayly
ID: 24073100
I tested mine in Navicat too, using copy and paste. The error phpmyadmin returns has been reported as a bug, but apparently isn't a bug: http://bugs.mysql.com/bug.php?id=30131
You may have seen this page already.

Can you show the result from SHOW CREATE TABLE tablename
0
 

Author Comment

by:freshwaterwest
ID: 24073118
CREATE TABLE `polish` (
  `id` int(10) NOT NULL auto_increment,
  `text` longtext character set latin1 NOT NULL,
  `text2` varchar(255) NOT NULL default '',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=utf8

As you can see I've tried a longtext and a varchar, although I would need to use longtext ideally

thanks
0
 
LVL 15

Expert Comment

by:oobayly
ID: 24073169
Column type shouldn't make a difference. I've tried your table in MySql on Win2K3 & Linux and text2 can contain the unicode characters 0xc484 where as text cannot.

That seems correct as `text2` is using the table's default charset of utf8, whereas latin1 used by `text` cannot store unicode chars.
What I can't understand is that you can't get text2 to store unicode chars, I see no reason for that.
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 

Author Comment

by:freshwaterwest
ID: 24073182
sorry just noticed, I had also removed the char set latin from the 'text' field so the table was utf8 general ci
0
 
LVL 15

Expert Comment

by:oobayly
ID: 24073242
A couple of questions, is utf8 int the list when you execute this:
SHOW CHARSET;
This will list all the character sets allowed, though I imagine utf8 will be there as it allows you to create a table using it.

Also, do you get the "ogenok" ( I think that's the correct term)
SELECT _utf8 0xc484;

Finally, what OS and version of MySql are you using?
0
 

Author Comment

by:freshwaterwest
ID: 24073275
Yes I get utf / utf8_general_ci in the list

but not the other
I'm using MAMP for testing php5/mysql5 on MAC OSX10.5.6

big5      Big5 Traditional Chinese      big5_chinese_ci      2
dec8      DEC West European      dec8_swedish_ci      1
cp850      DOS West European      cp850_general_ci      1
hp8      HP West European      hp8_english_ci      1
koi8r      KOI8-R Relcom Russian      koi8r_general_ci      1
latin1      cp1252 West European      latin1_swedish_ci      1
latin2      ISO 8859-2 Central European      latin2_general_ci      1
swe7      7bit Swedish      swe7_swedish_ci      1
ascii      US ASCII      ascii_general_ci      1
ujis      EUC-JP Japanese      ujis_japanese_ci      3
sjis      Shift-JIS Japanese      sjis_japanese_ci      2
hebrew      ISO 8859-8 Hebrew      hebrew_general_ci      1
tis620      TIS620 Thai      tis620_thai_ci      1
euckr      EUC-KR Korean      euckr_korean_ci      2
koi8u      KOI8-U Ukrainian      koi8u_general_ci      1
gb2312      GB2312 Simplified Chinese      gb2312_chinese_ci      2
greek      ISO 8859-7 Greek      greek_general_ci      1
cp1250      Windows Central European      cp1250_general_ci      1
gbk      GBK Simplified Chinese      gbk_chinese_ci      2
latin5      ISO 8859-9 Turkish      latin5_turkish_ci      1
armscii8      ARMSCII-8 Armenian      armscii8_general_ci      1
utf8      UTF-8 Unicode      utf8_general_ci      3
ucs2      UCS-2 Unicode      ucs2_general_ci      2
cp866      DOS Russian      cp866_general_ci      1
keybcs2      DOS Kamenicky Czech-Slovak      keybcs2_general_ci      1
macce      Mac Central European      macce_general_ci      1
macroman      Mac West European      macroman_general_ci      1
cp852      DOS Central European      cp852_general_ci      1
latin7      ISO 8859-13 Baltic      latin7_general_ci      1
cp1251      Windows Cyrillic      cp1251_general_ci      1
cp1256      Windows Arabic      cp1256_general_ci      1
cp1257      Windows Baltic      cp1257_general_ci      1
binary      Binary pseudo charset      binary      1
geostd8      GEOSTD8 Georgian      geostd8_general_ci      1
cp932      SJIS for Windows Japanese      cp932_japanese_ci      2
eucjpms      UJIS for Windows Japanese      eucjpms_japanese_ci      3

0
 
LVL 15

Expert Comment

by:oobayly
ID: 24073319
Sorry, do you get null returned, no rows returned, or is the character displayed just plain incorrect?

SELECT _utf8 0xc498 AS `utf8 char`, HEX(_utf8 0xc498) AS `hex`;

This is what I get:
mysql-utf8.png
0
 

Author Comment

by:freshwaterwest
ID: 24073338
I get:

utf8 char
?

hex
C498
0
 
LVL 15

Expert Comment

by:oobayly
ID: 24073372
Utterly bizarre. I can't see any reason why utf8 shouldn't work on OSX.

I doubt this will tell me anything much, but can you show what charset & collation the server & clients are using:
SHOW VARIABLES LIKE '%character%';
SHOW VARIABLES LIKE '%collation%';

0
 

Author Comment

by:freshwaterwest
ID: 24073425
character_set_client      latin1
character_set_connection      latin1
character_set_database      latin1
character_set_filesystem      binary
character_set_results      latin1
character_set_server      latin1
character_set_system      utf8

maybe this is the reason, although not sure how I can alter it in navicat?

collation_connection      latin1_swedish_ci
collation_database      latin1_swedish_ci
collation_server      latin1_swedish_ci



0
 
LVL 15

Accepted Solution

by:
oobayly earned 500 total points
ID: 24073467
Yup, that's the cause alright. The client connection isn't using unicode so unicode chars are being lost so to speak.
You can issue the command:
SET NAMES 'utf8';
However, I'm doubt this will persist when the server reboots, so it'll be best to update the server confirguration to use utf8 by default.

In the MySql configuration file (my.cnf, though I've no idea where it could be on OSX), add this lines under both the [client] and [mysqld] sections:
default-character-set=utf8

Reboot the server, and by default it should be using utf8 for everything. Any existing tables may have to be altered as they'll be using the charset they were created with.


0
 

Author Closing Comment

by:freshwaterwest
ID: 31566815
thanks, I reset the db in phpmyadmin and tried inserting the characters through my test php pages - this time it worked a treat.. many thanks..
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now