|
Home > Archive > MySQL ODBC Connector > January 2006 > help with character sets and collation
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
help with character sets and collation
|
|
|
| I think I have a problem with mysql related character sets and collation.
With language English (en-utf-8), MySQL charset UTF-8 Unicode and a MySQL
connection collation: ascii_general_ci. I can execute a sql statement in
phpmyadmin, like "INSERT INTO mytable (id, name) VALUES ('5','Unterwinkel
Straáe')"
But if I try to execute the insert using a php script I get the error 1406
record too long. Using the same insert but without the á character, the sql
statement works. INSERT INTO mytable (id, name) VALUES ('5','Unterwinkel
StraXe')"
How would I configure mysql so characters like á can be inserted without
problems.
Thanks
chris
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw
| |
| Gleb Paharenko 2006-01-26, 4:56 pm |
| Hello.
Please, execute the following statements in mysql command line and php,
and provide its output to the list:
show variables like '%char%';
show variables like '%collation%';
Include the CREATE statement for your table as well.
Chris wrote:
> I think I have a problem with mysql related character sets and collation.
> With language English (en-utf-8), MySQL charset UTF-8 Unicode and a MySQL
> connection collation: ascii_general_ci. I can execute a sql statement in
> phpmyadmin, like "INSERT INTO mytable (id, name) VALUES ('5','Unterwinkel
> Stra�e')"
>
> But if I try to execute the insert using a php script I get the error 1406
> record too long. Using the same insert but without the � character, the sql
> statement works. INSERT INTO mytable (id, name) VALUES ('5','Unterwinkel
> StraXe')"
>
> How would I configure mysql so characters like � can be inserted without
> problems.
>
> Thanks
> chris
>
>
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Gleb Paharenko
/ /|_/ / // /\ \/ /_/ / /__ Gleb.Paharenko@stripped
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET
<___/ www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw
| |
|
| Sorry, I am unable to work the command line. I have used this script
instead.
$sql = "show variables like '%char%'";
$result = mysql_query($sql) or die("Couldn't Select " .mysql_error());
$count = mysql_num_rows($resu
lt);
//echo $count;
while ($row = mysql_fetch_row($res
ult)) foreach($row as $key=>$value) echo
"$key=>$value<BR>";
echo "<BR>";
$sql = "show variables like '%collation%'";
$result = mysql_query($sql) or die("Couldn't Select " .mysql_error());
while ($row = mysql_fetch_row($res
ult)) foreach($row as $key=>$value) echo
"$key=>$value<BR>";
hope this provides the appropriate info.
Thanks
"Gleb Paharenko" <Gleb.Paharenko@ensita.net> wrote in message
news:20060126161300.611.qmail@lists.mysql.com...
> Hello.
>
> Please, execute the following statements in mysql command line and php,
> and provide its output to the list:
> show variables like '%char%';
0=> character_set_client
1=>latin1
0=> character_set_connec
tion
1=>latin1
0=> character_set_databa
se
1=>ascii
0=> character_set_result
s
1=>latin1
0=> character_set_server
1=>latin1
0=> character_set_system
1=>utf8
0=>character_sets_dir
1=>C:\Program Files\MySQL\MySQL Server 5.0\share\charsets\
> show variables like '%collation%';
0=> collation_connection
1=>latin1_swedish_ci
0=>collation_database
1=>ascii_general_ci
0=>collation_server
1=>latin1_swedish_ci
>
> Include the CREATE statement for your table as well.
CREATE TABLE my_table (location_id varchar(20) NOT NULL default '',name
varchar(50) NOT NULL default '',PRIMARY KEY (location_id)) TYPE=MyISAM
>
>
> Chris wrote:
>
>
> --
> For technical support contracts, goto https://order.mysql.com/?ref=ensita
> This email is sponsored by Ensita.NET http://www.ensita.net/
> __ ___ ___ ____ __
> / |/ /_ __/ __/ __ \/ / Gleb Paharenko
> / /|_/ / // /\ \/ /_/ / /__ Gleb.Paharenko@stripped
> /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET
> <___/ www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw
| |
| Gleb Paharenko 2006-01-27, 9:23 am |
| Hello.
I do not see the CHARACTER SET of your table (usually SHOW CREATE
includes it, may be you have NO_TABLE_OPTIONS in @@sql_mode), so I
assume it is the same as database character set - ascii. Check if the
problem disappears after changing the character set of your fields to utf8.
Chris wrote:
> Sorry, I am unable to work the command line. I have used this script
> instead.
>
> $sql = "show variables like '%char%'";
> $result = mysql_query($sql) or die("Couldn't Select " .mysql_error());
> $count = mysql_num_rows($resu
lt);
> //echo $count;
> while ($row = mysql_fetch_row($res
ult)) foreach($row as $key=>$value) echo
> "$key=>$value<BR>";
> echo "<BR>";
> $sql = "show variables like '%collation%'";
> $result = mysql_query($sql) or die("Couldn't Select " .mysql_error());
> while ($row = mysql_fetch_row($res
ult)) foreach($row as $key=>$value) echo
> "$key=>$value<BR>";
>
> hope this provides the appropriate info.
> Thanks
>
> "Gleb Paharenko" <Gleb.Paharenko@ensita.net> wrote in message
> news:20060126161300.611.qmail@lists.mysql.com...
>
>
>
>
> 0=> character_set_client
> 1=>latin1
> 0=> character_set_connec
tion
> 1=>latin1
> 0=> character_set_databa
se
> 1=>ascii
> 0=> character_set_result
s
> 1=>latin1
> 0=> character_set_server
> 1=>latin1
> 0=> character_set_system
> 1=>utf8
> 0=>character_sets_dir
> 1=>C:\Program Files\MySQL\MySQL Server 5.0\share\charsets\
>
>
>
> 0=> collation_connection
> 1=>latin1_swedish_ci
> 0=>collation_database
> 1=>ascii_general_ci
> 0=>collation_server
> 1=>latin1_swedish_ci
>
>
>
>
> CREATE TABLE my_table (location_id varchar(20) NOT NULL default '',name
> varchar(50) NOT NULL default '',PRIMARY KEY (location_id)) TYPE=MyISAM
>
>
>
>
>
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Gleb Paharenko
/ /|_/ / // /\ \/ /_/ / /__ Gleb.Paharenko@stripped
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET
<___/ www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw
| |
|
| I'm sorry but I do not know what you mean by NO_TABLE_OPTIONS in
@@sql_mode).
The database has a Collation = ascii_general_ci. The only other option is
ascii_bin.
With respect to the table, it also has Collation of the same,
ascii_general_ci. There are many Collation types which the table may be
change to, including several of the utf8 verity (utf8.bin,
utf8.danish.ci,....) but no utf8 without an extension. I tried setting the
table to utf8.unicode.ci, but still encounter the INSERT error as before.
Thanks
cw
"Gleb Paharenko" <Gleb.Paharenko@ensita.net> wrote in message
news:20060127134742.2667.qmail@lists.mysql.com...
> Hello.
>
> I do not see the CHARACTER SET of your table (usually SHOW CREATE
> includes it, may be you have NO_TABLE_OPTIONS in @@sql_mode), so I
> assume it is the same as database character set - ascii. Check if the
> problem disappears after changing the character set of your fields to
> utf8.
>
>
>
> Chris wrote:
>
>
> --
> For technical support contracts, goto https://order.mysql.com/?ref=ensita
> This email is sponsored by Ensita.NET http://www.ensita.net/
> __ ___ ___ ____ __
> / |/ /_ __/ __/ __ \/ / Gleb Paharenko
> / /|_/ / // /\ \/ /_/ / /__ Gleb.Paharenko@stripped
> /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET
> <___/ www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw
| |
| Gleb Paharenko 2006-01-28, 7:23 am |
| Hello.
Chris, the collation is subordinated to the character set. You should
work with the character sets, and only after with collations. The data
which you store in your table is silently converted to ascii character
set. Are you sure that the characters which you want to store are
present in ascii character set? You should change the character set
of the fields of your table to that one which can hold non English
characters. Another question - are you sure that the data which you're
passing to MySQL is in latin1 encoding? See:
http://dev.mysql.com/doc/refman/5.0...connection.html
http://dev.mysql.com/doc/refman/5.0...et-general.html
How to change the character set of the fields is described at:
http://dev.mysql.com/doc/refman/5.0/en/alter-table.html
Chris wrote:
> I'm sorry but I do not know what you mean by NO_TABLE_OPTIONS in
> @@sql_mode).
>
> The database has a Collation = ascii_general_ci. The only other option is
> ascii_bin.
>
> With respect to the table, it also has Collation of the same,
> ascii_general_ci. There are many Collation types which the table may be
> change to, including several of the utf8 verity (utf8.bin,
> utf8.danish.ci,....) but no utf8 without an extension. I tried setting the
> table to utf8.unicode.ci, but still encounter the INSERT error as before.
>
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Gleb Paharenko
/ /|_/ / // /\ \/ /_/ / /__ Gleb.Paharenko@stripped
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET
<___/ www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw
| |
|
| Thank you Gleb,
I'm afraid I'm in over my head on this subject as I am restricted to make db
changes via sql or phpmyadmin.It seem from your references the database
character_set needs to be set to latin? I'm speculating to change
character_sets and collations I need access to other utilites, programs or
server administrative functions I am not aware of.
I have inspected other servers I work on which enable me to insert accented
characters and it appears they all have the following settings:
character_set: latin1
character_sets latin1 big5 czech euc_kr gb2312 gbk latin1_de sjis...
Am I correct to assume changes to character sets must be done via command
line?
cw
"Gleb Paharenko" <Gleb.Paharenko@ensita.net> wrote in message
news:20060128082108.18200.qmail@lists.mysql.com...
> Hello.
>
> Chris, the collation is subordinated to the character set. You should
> work with the character sets, and only after with collations. The data
> which you store in your table is silently converted to ascii character
> set. Are you sure that the characters which you want to store are
> present in ascii character set? You should change the character set
> of the fields of your table to that one which can hold non English
> characters. Another question - are you sure that the data which you're
> passing to MySQL is in latin1 encoding? See:
> http://dev.mysql.com/doc/refman/5.0...connection.html
> http://dev.mysql.com/doc/refman/5.0...et-general.html
>
> How to change the character set of the fields is described at:
> http://dev.mysql.com/doc/refman/5.0/en/alter-table.html
>
>
>
> Chris wrote:
>
> --
> For technical support contracts, goto https://order.mysql.com/?ref=ensita
> This email is sponsored by Ensita.NET http://www.ensita.net/
> __ ___ ___ ____ __
> / |/ /_ __/ __/ __ \/ / Gleb Paharenko
> / /|_/ / // /\ \/ /_/ / /__ Gleb.Paharenko@stripped
> /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET
> <___/ www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw
|
|
|
|
|