MySQL Forums - Character Sets, Collation, Unicode

mysql can not to insert unicode16 uncommon word (5 replies)

mzy ma — Fri, 17 May 2013 03:02:36 +0000

connect with ODBC “DRIVER={MySQL ODBC 5.2w Driver};SERVER=%s;PORT=%d;DATABASE=%s;UID=%s;PASSWORD=%s;OPTION=3;”
szSQL.Format(_T("INSERT INTO test")
_T("(Name)")
_T("VALUES('%s')"), _T("𢈢"));
m_Conn.pConn->Execute((_bstr_t)szSQL, &RecordsAffected, adCmdText);

The program does not return，This problem has troubled me for a long time，Who can help me answer this question thanks

mysql_query and embarcadero c++ (1 reply)

tawab zawiti — Sat, 06 Apr 2013 17:53:21 +0000

Hello,
I use MySQL C API with Embacadero c++ without any problems.
But when I try to send any wide character (UNICODE) like Arabic symbols, appears as not readable characters in MySQL database. because the SQL string that I send it by mysql_query function must be (const char *) and not (const wchar_t *).

thanks for any helps.

Characters not getting stored (3 replies)

Andrea Croci — Sat, 06 Apr 2013 05:07:57 +0000

Hello, I have yet another problem with character sets. I have read lots of threads here and the very informative page at http://mysql.rjweb.org/doc.php/charcoll, but I found no answer.

Here is the issue: I'm designing a new database from scratch on MySQL version 5.5.27-log and I would like to use utf8 with collation utf8_unicode_ci throughout, because it will have to store names in German, Italian and English.

I defined the database to use DEFAULT CHARACTER SET = utf8 COLLATE utf8_unicode_ci, I declared each table with DEFAULT CHARACTER SET = utf8 and I even repeated the same thing in most columns of the tables.

The only two tables I double checked with SHOW CREATE TABLE tbl show everything as utf8 with collate utf8_unicode_ci. The others don't matter because I have the problem already with one of these.

I tried different things and basically tried to store 'Forlì' and (just to mess with it) 'Förlìßen'.

With the first one I get a warning "Incorrect string value '\x8D' for column 'birthplace' at row 1" and when I select it, it shows just 'Forl' without the final 'ì'. The second one too gets truncated at the first "invalid" character that it encounters: ö, with an "Incorrect string value" error message. That means it stores only 'F'.

The test with 'birthplace', hex(brithplace), length(birthplace) and char_length(birthplace) shows the following:

for 'Forlì': 'Forl', 466F726C, 4, 4.
for Förlìßen: 'F', 46, 1, 1.

All of this happens when I set the "character_set_*" variables to utf8 (other than the filesystem, which I didn't touch).

With other combinations of "character_set_*" I do get different symbols on the screen and different hex numbers in the table, but I have not got the right one yet: if I set names to latin1 or cp850 (which appears to be the default in my installation, the initial set-up I get when starting the console is that) and leave character_set_database at utf8, then it does not truncate the strings at the first non-english character, but the results are only shown correct if I enter the data from console.

I tried importing the data from a file, saved both as latin1 and as utf8, and it does store something, but not the right thing, regardles of the variables setting.

I dropped the database and recreated it several times with different settings of the variables at the beginning, but I don't get it right.

In all these situations I'm entering the letters from a german keyboard under Windows 7, if that can make any difference.

I'm completely confused: it seems it only works if I enter the data from the keyboard, and this again if I don't set names to utf8 (which I thought would actually be the right thing to do). Also I would like to import them from a file because sometimes I don't have access to the server and it would be more convenient for me to prepare a file and then import it all at once.

Any help would be appreciated.

Thank you.

Is STR_TO_DATE function doesn't work with UCS2 charset? (4 replies)

Jagadish Talluri — Fri, 22 Mar 2013 14:15:07 +0000

Dear All,

Pleaes help me with this quick query.

[Server version: 5.5.29-log MySQL Community Server (GPL)]
I am working with a table which is having default charset ucs2. One of the coloumn is varchar(64) contains datetime information.

I need to convert that varchar column to datetime in order to do some business logic. I used STR_TO_DATE function, It didn't work.
I got a NULL, with a warning code 1411 and message - Incorrect datetime value: '' for function str_to_date.

I did a quick experiment, changed the column charset to latin1. It's working.

My query is, does the function STR_TO_DATE is incompatible with ucs2 charset?
Is there any documentation available which mention about this incompatibility with certian set of charsets?(I serched this entire week and didn't find one, help me if you find one)

For some function like char_length() are mentioned as multi-byte safe.
Therefore, do I need to consider, the functions which are not mentioned the same way are incompatible with multi-byte?

Kindly help.

accented letters, display and search (1 reply)

Alessandro Abiusi — Fri, 01 Feb 2013 07:12:59 +0000

Hi,
as the title, I have trouble with these blessed accented letters.
I know that the 'subject was covered billions of times, but just can not seem to solve.
Mysql version 5.5.29 64bit.
I have the following problem:
I create a database starting with character encoding to utf8 with the command

"Crate the official database;"

I create a table (up to some versions ago), the column names I could put an accent not anymore (first issue), but this matters little, then run

"Create table customer (id int not null auto_increment, City varchar (255) not null, primary key (id));"

I'm going to create my table insert (from ms-dos prompt), created in the column, the character "ò" through

"Insert into customers (City) VALUES ('o');"

and here comes the first problem, the terminal answers me

"Mysql> insert into customers (City) values (" ò ");
ERROR 1366 (HY000): Incorrect string value: '\ x95' for column 'City' at row 1
mysql> "

then I force the connection to the database with the command

"Set names latin1"

Rieseguo the 'insert, is magically takes me an accented letter.
Now, to business needs, the database will be 'backupped' (pass the word) 1 time per day.
Then running back, I like to check the correct data import, what 'now there is a need, then I get disconnected mysql, and run the command

"Mysqldump-u user-p official> backup.sql"

OnRPG create a database to test the 'import

"I try create database;"

at this point the backup through reimporto

"Mysqldump-u user-p I try
Result I find a table with columns 'id and Citta' but completely empty.

I tried one of the many attempts, forcing the 'exports in latin1, in that case I inserted the data into the database, but when executing an

"Select * from clients where City LIKE '%ò%';"

Comes back to me empty, but if I try '%o%', I found the field. But ò stressed, had a strange character, but by the carattare phpmyadmin I saw him right!
Does anyone know how to solve this problem permanently boring?

Thanks for any replies

Alessandro

Special Characters (äöüß), switching to UTF8, collation-problems (1 reply)

C.A. Pelle — Thu, 31 Jan 2013 15:02:00 +0000

Hi all,

we have a larger DBMS (not an oracle-product) that is able to export data into a mySQL system. Or more precisely we are currently at a stage where the mySQL export is about to turn productive.
With more test-data available, we are encountering more problems. One of the larger problems we've run into recently was the handling of special characters, namely german "umlauts", which are äöü and ß. Others might be of importance and not yet discovered.
It turned out that somewhere between out application and mySQL server, the statement was converted into UTF8 (supposedly dbx-driver-component), and we haven't been able to change this despite trying a myriad of different settings as well programmatically (e.g. Connection.SetParameter("CharSet", "..."); )
as in mySQL table-wise or DB-wise (alter table/db, set charset= collate=).

So we finally resorted to converting all db-tables to UTF8. The transfer of special-characters now works. However all collations for UTF8 are not case-sensitive except for the bin-collation. The bin-collation however is not usable, since we need to sort data using "ORDER BY" in our selects instead of internal sorting algorithms local to our applications.
However, our source-DBMS is case-sensitive and we do have unique indices on many of our varchar-columns, which do not translate into this mySQL-configuration.

Finally, the question is: how can we handle case-sensitive mySQL with special characters as äöüß ?

Thanks a lot for any input!
Regards,
P.

Use of [] square-brackets to enclose table/column names (1 reply)

Doug Johnston — Sat, 12 Jan 2013 19:11:36 +0000

I am trying to connect into a MySQL schema via an existing application. That application allows you to specify a connection string which I was able to implment ... the connection now works.

Unfortunately, the application includes the use of [] (square brackets) to enclose table and column names when interacting with the MySQL tables ... it's hard-coded into the application so, I can't change that.

As you know, MySQL can be configured to use `, " or '.

Does anyone know of a MySQL configuration parameter that would allow me to specify that this MySQL schema should use [] instead? In all the reading I've done so far, I fear the answer is no.

Thank you for anything you can offer.

Doug

can I use latin1 and utf8 at the same time? (2 replies)

Greg K. — Thu, 10 Jan 2013 07:48:30 +0000

hi
Currenty I've got latin1, but I'd like to set utf8 as default charset for all levels (server,db,table,session,client).
Please, let me know if having set this in my.ini in client and server sections (of course it applies only for new databases and tables) I can still use my old databases and tables in latin1 fashion.
Or, will it collide when quering the data. Then should I unify?
As for backuping, I'm performing offline backups (not mysqldump), so restoring shouldn't be an issue.
I wouldn't like to do it as I have a TB db and the convertion will be troublesome.

it seems like each schema is independent, having own db.opt setting's file.

so now the only thing I try to figure out id what the application/client should be aware of.

Can you see here any risks to jumble the database?

utf8_general_mysql500_ci performance (1 reply)

Jose Angel — Fri, 21 Sep 2012 04:55:52 +0000

Hi,

I've been looking but haven't found any performance comparison between utf8_general_mysql500_ci and utf8_general_ci in terms of performance.

I know utf8_general_mysql500_ci is supposed to behave the same as utf8_general_ci pre v5.1.24 in terms of ordering but what about performance? will it be the same?

Thanks.
- Jose -

Default collation used in STRCMP() function (4 replies)

Sachin Vyas — Tue, 28 Aug 2012 04:18:01 +0000

Hello,
What is the default collation used in a STRCMP() function if no collation is specified?
(I know that we can explicitly specify a collation for comparing the 2 strings in STRCMP()).

From where does MySQL gets this value - collation_database, collation_server, collation of the table?

Regards,
Sachin Vyas.

Problem setting the "default-character-set" parameter in my.ini file. (7 replies)

Sachin Vyas — Fri, 03 Aug 2012 10:46:21 +0000

Hello,
I am trying to change the value for the variable "default-character-set" to latin1 as below in the "my.ini" file:

before change
===============

mysql> show variables like 'character%';
+--------------------------+---------------------------------------------------------+
| Variable_name | Value |
+--------------------------+---------------------------------------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | C:\Program Files\MySQL\MySQL Server 5.5\share\charsets\ |
+--------------------------+---------------------------------------------------------+
8 rows in set (0.00 sec)

Now, I made the following change o "my.ini" file:

[mysql]
default-character-set=latin1

Now, after making the above change, when I save the file & reconnect as "root" user & execute the command "show variables like 'character%';" I get the following output:
(I even tried restarting the mysql service after making the changes to the "my.ini" file, but did not get the expected results.)

mysql> show variables like 'character%';
+--------------------------+---------------------------------------------------------+
| Variable_name | Value |
+--------------------------+---------------------------------------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | C:\Program Files\MySQL\MySQL Server 5.5\share\charsets\ |
+--------------------------+---------------------------------------------------------+
8 rows in set (0.00 sec)

Query
=====
Shouldn't the values for :
1) character_set_client
2) character_set_connection
3) character_set_server
be = latin1.

When I try doing the same change from command line using the command, it works as expected :

mysql -u root -pmanager --default-character-set=latin1

Now, I exceute the following at mysql prompt:

mysql> show variables like 'character%';
+--------------------------+---------------------------------------------------------+
| Variable_name | Value |
+--------------------------+---------------------------------------------------------+
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | latin1 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | C:\Program Files\MySQL\MySQL Server 5.5\share\charsets\ |
+--------------------------+---------------------------------------------------------+
8 rows in set (0.00 sec)

As per MySQL documentation http://dev.mysql.com/doc/refman/5.5/en/server-options.html#option_mysqld_default-character-set
I changed the variable name to character-set-server, but that did n't help either.

But, If the above link is true then how did it work for the command:
mysql -u root -pmanager --default-character-set=latin1
since --default-character-set is deprecated from MySQl 5.5.3 ?

How to support full Unicode in MySQL databases (no replies)

Edwin Desouza — Mon, 30 Jul 2012 16:52:48 +0000

How to support full Unicode in MySQL databases
http://mathiasbynens.be/notes/mysql-utf8mb4

'þ' Will shows '?' in table when I read it from text file (1 reply)

Chintan Patel — Tue, 31 Jul 2012 02:01:16 +0000

Hello,

I am facing problem with using 'þ' character.
I am reading this character from text file which was generated using "INTO OUTFILE..." now I am trying to insert data from this text file to database table using "LOAD DATA INFILE" After performing this steps In my database table data is not written properly it shows '?' everywhere where 'þ' is.

Can you give me tips what is wrong here ..............

Below is the my current settings for mysql variables
=====================================
'character_set_client', 'utf8'
'character_set_connection', 'utf8'
'character_set_database', 'latin1'
'character_set_filesystem', 'binary'
'character_set_results', 'utf8'
'character_set_server', 'latin1'
'character_set_system', 'utf8'

'collation_connection', 'utf8_general_ci'
'collation_database', 'latin1_bin'
'collation_server', 'latin1_bin'
=====================================

Thanks
Chintan Patel

Reading text from a BLOB field (2 replies)

Margaret Nelson — Tue, 31 Jul 2012 20:35:47 +0000

I was having trouble with question marks being displayed in the text read back from a blob field in MySql. I found a partial solution when I read this conversation: http://stackoverflow.com/questions/948174/how-do-i-convert-from-blob-to-text-in-mysql Converting to utf8 (aka UTF-8) solved part of the problem but my text truncated when it ran into a special character (the em or long dash). The CONVERT() function is still the answer, but I had to figure out which character set our database was using. Below are the steps I followed to figure this out. I'm new to MySQL, so there are probably better ways to get at this information.
1. SHOW FULL COLUMNS FROM mytable IN mydatabase; -- note the collation on the text fields;
2. SHOW CHARACTER SET; -- find the collation you saw in step one in either the Description or Default collation columns. Copy the value from the Charset column.
3. In your SQL SELECT statement add a field like this: CONVERT(someBlobTextField, USING copiedCharsetValue)
4. Run your query and review your data. Viola! It works.

I also ran across a PHP function called nl2br() that adds the line breaks the user had entered back in.

sort / search / display - columns (1 reply)

Dominik Meyer — Tue, 17 Jul 2012 05:02:40 +0000

Hello guys,

We have a problem with collations and sorting behavior of mysql.

We have in a table "names" names like this:
Müller, Měyer, Śebastian, Ałan, ..

Now we want find the row "Müller" when we search after Mueller, Müller (not on Muller)
We want find the row "Měyer" when we search after Měyer, Meyer
We want find the row "Ałan" when we search after Ałan, Alan

So ä = ae and ě = e and ł = l.... (also on sorting it must be the same)

We also needs "no sorting words".

For example, we put on the name "Wolfgang Johann {von}"
{-signs to say the system that it is a non sorting word.

Our solution:
we have 3 columns with the same name. "name, name_sort, name,search".
name_search is used for boolean fulltext search. (on save we change ü/ä/ö/ě/ł.. to ue/ae/oe/e/l..), name_sort is used for sorting (we do the same + remove the words in {}-signs), name is only the display column(we romve the {- signs on output)

is there a way to do this without having this 3 colums ?
this (and other tables, like titles) have many rows, so we cant transform before a query.

saving 'url_encode' with no modifications (1 reply)

Jairo Supelano — Mon, 18 Jun 2012 01:27:03 +0000

hi, i am using php with mysql, and for security reasons, i save all strings as 'urlencode', wich among other things, converts quotes, double quotes (which are string end chars) and basically all that is not a letter or a number into url encode, or a '%' followed by a number. i expect it to save messages written in spanish, so chars like á, ñ, é and others, are parsed by the url encode. there are some scape functions included in php to make a string 'database safe', which works more or less the same (escape end of string chars and other stuff), but most of my code has urlencodes everywhere (so if your answer is 'use those functions, please dont'). when i store the string, urlencoded into the databse, and then retrieve it, i dont find the same symbols i stored, for example, if i store an 'ñ', what i get after i save it and then read it, is a sequence of werid chars, for example, in 'españa', what i get is espaÃ±a. for a previos project i made a replace function, but i find it like cutting corners, so how do i store my &## and retrieve the same %## i save? somebody told me i should check the definition of the table, what definition should i use? (i use the default engines when declaring tables, since i do not specify a particular engine)
tnx

WHERE without COLLATE does not work (1 reply)

Martin W — Wed, 27 Jun 2012 09:13:45 +0000

UTF-8 vs UCS-2 (especially on ndb) (no replies)

Mirko Raner — Wed, 11 Apr 2012 00:53:29 +0000

I'm in the process of migrating a InnoDB database to ndb, and we're frequently running into the 14000-byte row size limit imposed by ndb. There are quite a number of VARCHAR columns in our DB, and we're using the utf8 character set.
My question is: what (if any) is the benefit of using UTF-8 over UCS-2?
Since VARCHAR needs to allocate memory for the worst-case scenario (i.e., the maximum length) VARCHARs in UTF-8 require 3 times the length, whereas UCS-2 only requires 2 times the length. UTF-8 is optimized for scenarios with mainly one- and two-byte characters, but if the storage mechanism has to assume three bytes anyway, UCS-2 seems to be the better choice.
Am I overlooking something here? It seems like using UTF-8 for VARCHARs is a waste of space (especially problematic for MySQL Cluster with the smaller row memory limit of 14000 bytes).
Any insights?

Latin1 character set driving me mad! (2 replies)

Justin P — Sat, 14 Apr 2012 06:33:58 +0000

I have a database that I am moving between webhosts. On the prior webhost, this SQL code worked perfectly:

SELECT addresses.address AS address, storedbits.bits AS bits, addresses.id AS id
FROM addresses LEFT JOIN storedbits ON storedbits.addressesid = addresses.id
WHERE address='$address' COLLATE utf8_bin AND block != -1 AND block < $lastconf ORDER BY block, addresses.id, storedbits.bits LIMIT 1

But, after dumping the table as a .sql, and importing it into the new webhost, I get the following error:

Error in query: SELECT addresses.address AS address, storedbits.bits AS bits, addresses.id AS id FROM addresses LEFT JOIN storedbits ON storedbits.addressesid = addresses.id WHERE address='blahblahblah' COLLATE utf8_bin AND block != -1 AND block < 173962 ORDER BY block, addresses.id, storedbits.bits LIMIT 1. COLLATION 'utf8_bin' is not valid for CHARACTER SET 'latin1'

Ok, fine. So I check my tables. They are set to utf8 - default collation. I check my database. It is set to utf8 - default collation. I check my rows. They are set to utf8 - utf8_bin.

If I remove the COLLATE statement, and instead, use BINARY just before the address comparison, then it works - the query runs fine. But it isn't fast enough. It takes 3-4 times as long to run the query as it does using collation on the old host. I don't know if I can attribute this speed difference entirely to query differences, as the hosts could very well have different speed of CPUs available, but I certainly want to be sure I am doing everything I can to make the queries run quickly and efficiently as possible.

So, where are the latin1 characters, if none of my database is set up with them? Why am I getting this error on the new host, but not the old one with the same query?

EDIT: Also, I tried the guide located here: http://docs.moodle.org/22/en/Converting_your_MySQL_database_to_UTF8

I did these steps:
mysqldump -uusername -ppassword -c -e --default-character-set=utf8 --single-transaction --skip-set-charset --add-drop-database -B dbname > dump.sql
cp dump.sql dump-fixed.sql
vim dump-fixed.sql
:%s/DEFAULT CHARACTER SET latin1/DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci/
:%s/DEFAULT CHARSET=latin1/DEFAULT CHARSET=utf8/
:wq
mysql -uusername -ppassword < dump-fixed.sql

The two find/replace commands in VIM didn't find anything, but I re-imported the dump-fixed.sql anyway. Still comes up with the latin1 problem. I don't understand where/why these latin1 characters are coming from!

UpperCase -> Lowercase (1 reply)

Werner Broser — Sat, 07 Apr 2012 16:44:44 +0000

After executing these statements:

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL';

CREATE SCHEMA IF NOT EXISTS `Salma_WTF` DEFAULT CHARACTER SET latin2 COLLATE latin2_general_ci ;
USE `Salma_WTF` ;

-- -----------------------------------------------------
-- Table `Salma_WTF`.`stCountries`atclubfeesatclublocationsatclublocations
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `Salma_WTF`.`stCountries` (
`fid` INT NOT NULL AUTO_INCREMENT ,
`fISO3` CHAR(3) NULL ,
`fName` VARCHAR(45) NULL ,
PRIMARY KEY (`fid`) )
ENGINE = InnoDB;

why are then the fieldnames as defined, but the tablename is in lowercase ?

kr/Werner

File .SQL set collation LATIN1 to UTF8. (1 reply)

Andrews Dumith — Sun, 01 Apr 2012 07:10:46 +0000

Hello everyone;

I´m using MYSQL-Server 5.5.20 running on Centos; I have develop a script to make backup of my databases but when the SQL file is done and I try to import again I get that the collation is set to latin1 instead of UTF8.

This is the command that I´m using:
mysqldump --skip-set-charset --default-character-set=UTF8 db_masterdb > db-test1-dump.sql

Do you have any idea to help me?

TIA....

replication from 4.1 to 5.5 and character set change (3 replies)

Gaspare Siclari — Sat, 18 Feb 2012 04:15:18 +0000

Hello everyone,
in our society we want to migrate our mysql db from an obsolete version 4.1 to a slightly newer version (5.5)

Obviously replication between these two versions is not feasible.
For this we will use the bridge (with engine blackhole)

So the process will be:
4.1 (iso) -> 5.0 (iso) -> 5.1 (utf8) -> 5.5 (utf8).
My question is:
I exclude the ustanza 5.1?
So, I can replicate the 5.5 (utf8) from 5.0 (iso)?
Do you know if there are any contraindications?
We have also some tables with a blob field, stored in latin. How can i replicate the data having the blob content converted into utf8?

Thanks to all
Gaspare

storing hungarian characters (1 reply)

akos fortagh — Mon, 23 Jan 2012 06:50:46 +0000

Hi there, I need to store special hungarian accuted characters and no mater how I tried to change the charsets and collation in mysql it inserts/diplays a '?' for some of the characters.
Any help would be much appreciated.

Storing & Displaying Chinese characters [at the same time] (1 reply)

Gregori M — Sat, 14 Jan 2012 21:52:24 +0000

Hi,

I'm a newbie in this issue.

I have a form on a web. The web is coded with UTF8 and it has different languages.

I've been able to write with Chinese characters with this form and display this characters afterwards from the database. So the problem is not displaying this characters in my website.

My problem, or what I would like to do is, that when I check the data directly with MySqlAdmin client, Chinese symbols aren't there. They are coded in hexadecimal (I guess). This field in my database is coded in UTF8. (I tried with utf8_general_ci and utf8_unicode_ci and other utf8 and the result is the same)

If I change in mysqladmin the collate character to gb2312 or gb2312_chinese_ci, and then I edit the field and I write chinese characters in it, I can modify the field and I can store it with the chinese charecters. But then, the website doesn't display correctly the chinese field.

I would like to be able to show the chinese characters in the website and store chinese characters in the database at the same time (is it possible??) because I want to export this database afterwards.

I'm using a MySql5.5 + PHP

Thank you so much for your help.

change calendar type (2 replies)

zabltn khan — Tue, 03 Jan 2012 11:17:36 +0000

Hi everybody,

I want to create a database to store data other than English language, in Persian language, and in Persian(farsi) language we have a different calendar by the name of Shamsi Calendar(persian calendar). Now my question is that how can i change the default Gregorian date to Shamsi date in mysql to store Persian dates.

note:
2011/12/19 = 1390/9/28

Your help and reply will be my pleasure, i appreciate it.
Kind Regards.

Question about Double Encoding (1 reply)

Shu Chow — Thu, 01 Dec 2011 02:24:55 +0000

Hi, all. I was lucky to find Rick James' post on double encoding. I believe that is the exact problem I'm having. My tables are in UTF8, but the relevant system variables are all in latin1. I'm getting the same symptoms described. I do have two questions, though.

First, I don't full understand how the characters are being translated from latin1 to utf8. Specifically, how:

⚈ latin1 E1 = utf8 C3A1
⚈ latin1 83 = utf8 C692
⚈ latin1 A1 = utf8 C2A1

The first and last ones are correct (a with accent and inverted exclamation, respectively). In UTF8, c6 92 is a "small f with hook" while in latin, 83 is a capital S.

Second, in diagnosing this problem, when we looked at the selected columns through the MySQL command line client, the characters actually looked correct. When we set set names utf8, it then appeared garbled. How was the client able to correctly interpret it?

Thanks for any insight.

charset problem (3 replies)

László Köváry — Fri, 25 Nov 2011 04:06:28 +0000

Hello Everybody,

I have a table named 'words' and it contains words. /id long, word char(50)/

I executed a select statement, for search word. The statement doesn't differentiate between two words 'cipó' and 'cipö'

select * from words where word = 'cipö'
The query found the word 'cipó' too. Why?

The charset is utf8 and the collation is utf8_generail_ci.

I tried with this

select * from words where BINARY word = 'cipö'
It works perfectly. But, the HIBERNATE what I use doesn't know this syntax.

Thanks

MATCH AGAINST (1 reply)

kuno vaik — Wed, 09 Nov 2011 10:02:47 +0000

Hi.

Could it be, how can I work for the search StopWords
Match(name) AGAINST('+it +service' IN BOOLEAN MODE)

Illegal mix of collation during replication (3 replies)

Tuan Ta — Sat, 29 Oct 2011 19:39:15 +0000

Occasionally, I get this error message in the mysql error log: "Error 'Illegal mix of collations (utf8_general_ci,COERCIBLE) and (latin1_swedish_ci,IMPLICIT) for operation '='' on query." during replication. The master is a "5.0.91-log MySQL Community Server", and the slave is "5.5.14-log MySQL Community Server". "utf8" is not configured anywhere in the my.cnf file. If I execute the statement manually on the slave, it still fails with the same error message. If I issue "SET character_set_connection=latin1;", I can continue replication by executing the statement manually, skip the statement in the replication, and start slave again.

I'm not sure if this is a bug, or I have something misconfigured. Any help is greatly appreciated.

Failed Query:
UPDATE A, B SET A.colA1 = B.B_PRIMARY_ID WHERE A.colA1='value' AND (INET_NTOA(A.IPADDR) = B.IPADDR) or A.IPADDR = B.IPADDR);

SHOW CREATE TABLE A:

CREATE TABLE A (
`colA1` int(10),
`IPADDR` varchar(128) NOT NULL DEFAULT '',

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

SHOW FULL COLUMNS FROM A:
+------------------+---------------------+-------------------+------+-----+---------+----------------+---------------------------------+--------+
| Field | Type | Collation | Null | Key | Default | Extra | Privileges |Comment |
+-------------------+--------------------+-------------------+------+-----+---------+----------------+---------------------------------+--------+
| colA1 | int(10) unsigned | NULL | NO | PRI | 0 | | select,insert,update,references | |
| IPADDR | varchar(128) | latin1_swedish_ci | NO | | | | select,insert,update,references | |
+------------------+---------------------+-------------------+------+-----+---------+----------------+---------------------------------+--------+

SHOW CREATE TABLE B:

CREATE TABLE B (
`B_PRIMARY_ID` int(10),
`IPADDR` varchar(15) NOT NULL DEFAULT '',
PRIMARY KEY (`B_PRIMARY_ID`)
) ENGINE=InnoDB AUTO_INCREMENT=XXX DEFAULT CHARSET=latin1;

SHOW FULL COLUMNS FROM B:
+------------------+---------------------+-------------------+------+-----+---------+----------------+---------------------------------+---------+
| Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment |
+------------------+---------------------+-------------------+------+-----+---------+----------------+---------------------------------+---------+
| B_PRIMARY_ID | int(10) unsigned | NULL | NO | PRI | NULL | auto_increment | select,insert,update,references | |
| IPADDR | varchar(15) | latin1_swedish_ci | NO | MUL | | | select,insert,update,references | |
+------------------+---------------------+-------------------+------+-----+---------+----------------+---------------------------------+---------+

mysql> show global variables like '%collation%';
+----------------------+-------------------+
| Variable_name | Value |
+----------------------+-------------------+
| collation_connection | latin1_swedish_ci |
| collation_database | latin1_swedish_ci |
| collation_server | latin1_swedish_ci |
+----------------------+-------------------+
3 rows in set (0.00 sec)

Fixing Double Enocoding With mysqldump (3 replies)

Peter Berry — Wed, 26 Oct 2011 18:15:44 +0000

Hi, I've just diagnosed my problem as "Double Encoding" as described by Rick James' incredibly helpful page at http://mysql.rjweb.org/doc.php/charcoll.

Indeed I have UTF8 data going in, a connection (erroneously) set to Latin1, and a database and tables set to UTF8. One 2 byte UTF8 character in ends up encoded as 4 bytes in my tables.

My question is about fixing it. It seems like by far the easiest thing to do would be:

1. mysqldump --default-character-set=latin1 .... my_database > my_database_latin1.sql
2. Edit my_database_latin1.sql to set NAMES=utf8 at the top.
3. mysql ... < mydatabase.sql

The dump should convert the 4 bytes which it thinks are 2 utf8 characters back down to 2 bytes (2 latin1 characters). The file should actually contain UTF8, though it thinks it's latin1. So change the encoding of the connection set at the top of the file to UTF8, and import this data back into the same database.

Since Rick doesn't quite mention this fix, I'm assuming I'm missing something. A quick test does look ok so far.

Am I missing something?

Thanks for the help - Peter