Re: help with character sets and collation
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" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > 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/en/charset-connection.html > http://dev.mysql.com/doc/refman/5.0/en/charset-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 > / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] > /_/ /_/\_, /___/\___\_\___/ 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/[EMAIL PROTECTED]
Re: help with character sets and collation
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/en/charset-connection.html http://dev.mysql.com/doc/refman/5.0/en/charset-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 / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ 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/[EMAIL PROTECTED]
Re: help with character sets and collation
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" <[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
> 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($result);
>> //echo $count;
>> while ($row = mysql_fetch_row($result)) foreach($row as $key=>$value)
>> echo
>> "$key=>$value";
>> echo "";
>> $sql = "show variables like '%collation%'";
>> $result = mysql_query($sql) or die("Couldn't Select " .mysql_error());
>> while ($row = mysql_fetch_row($result)) foreach($row as $key=>$value)
>> echo
>> "$key=>$value";
>>
>> hope this provides the appropriate info.
>> Thanks
>>
>> "Gleb Paharenko" <[EMAIL PROTECTED]> wrote in message
>> news:[EMAIL PROTECTED]
>>
>>>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_connection
>> 1=>latin1
>> 0=>character_set_database
>> 1=>ascii
>> 0=>character_set_results
>> 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:
>>>
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
>>>/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
>>>/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET
>>> <___/ www.mysql.com
>>
>>
>>
>
>
> --
> For technical support contracts, goto https://order.mysql.com/?ref=ensita
> This email is sponsored by Ensita.NET http://www.ensita.net/
> __ ___ ___ __
> / |/ /_ __/ __/ __ \/ /Gleb Paharenko
> / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
> /_/ /_/\_, /___/\___\_\___/ 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/[EMAIL PROTECTED]
Re: help with character sets and collation
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($result);
> //echo $count;
> while ($row = mysql_fetch_row($result)) foreach($row as $key=>$value) echo
> "$key=>$value";
> echo "";
> $sql = "show variables like '%collation%'";
> $result = mysql_query($sql) or die("Couldn't Select " .mysql_error());
> while ($row = mysql_fetch_row($result)) foreach($row as $key=>$value) echo
> "$key=>$value";
>
> hope this provides the appropriate info.
> Thanks
>
> "Gleb Paharenko" <[EMAIL PROTECTED]> wrote in message
> news:[EMAIL PROTECTED]
>
>>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_connection
> 1=>latin1
> 0=>character_set_database
> 1=>ascii
> 0=>character_set_results
> 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:
>>
>>>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
>>/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
>>/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET
>> <___/ www.mysql.com
>
>
>
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ /Gleb Paharenko
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ 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/[EMAIL PROTECTED]
Re: help with character sets and collation
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($result);
//echo $count;
while ($row = mysql_fetch_row($result)) foreach($row as $key=>$value) echo
"$key=>$value";
echo "";
$sql = "show variables like '%collation%'";
$result = mysql_query($sql) or die("Couldn't Select " .mysql_error());
while ($row = mysql_fetch_row($result)) foreach($row as $key=>$value) echo
"$key=>$value";
hope this provides the appropriate info.
Thanks
"Gleb Paharenko" <[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
> 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_connection
1=>latin1
0=>character_set_database
1=>ascii
0=>character_set_results
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:
>> 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
> / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
> /_/ /_/\_, /___/\___\_\___/ 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/[EMAIL PROTECTED]
Re: help with character sets and collation
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
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ 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/[EMAIL PROTECTED]
