Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

polish characters in mysql change to ????

Posted on 2009-04-05
17
Medium Priority
?
1,927 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

 

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
 

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 2000 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to dynamically set the form action using jQuery.

721 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