MySQL Charset problems

[Renich]

Ok, I have read a lot of threads talking about this simple problem and i don't have a clue what else to try.

The problem:
I can't post data to my database using UTF-8.

If I try to post foreign characters like ñ, á, etc... you get strange signs

If you go to the mysql prompt and type:

mysql>show variables like '%char%';

you will see the diferent variables for character sets. In FC4 you have some configured like 'latin1'. This pretty much limits it to eng chars.

If you go to the /etc/my.cnf and VIMize it. You can add the following lines:

[mysqld]
default-character-set=utf8

[mysql]
default-character-set=utf8

if you restart your MySQL and checkout the "show variables" thingy, you'll see that ALL your character variables changed to UTF-8.

This DOES NOT SOLVE THE PROBLEM!

when you have a webpage configured to be in UTF-8 and you try to post something foreign, you'll get some strange chars. If you have a table named with foreign characters, it will never find it.

If you check your mysql db files, located at /var/lib/mysqlcd, you will see your files with strange names... for example, if you named a table something like "cóm" you will see its file name is "cA3m".

I confess... I was using pma2 so i could edit my databases from my other windows machine. WROOOOONG!

When you create dabasases via phpmyadmin from a windows-based computer, it creates it using latin1 charset... even if you choose utf-8 at the login screen (as long as you have cookies config enabled).

Solution:
Create your DB using your mysql prompt or PMA from a linux computer. Don't commit my same mistakes...

If you've got the solution for creating databases from a windows-based computer to a linux based computer using phpmyadmin with UTF-8 charset, please email me.

p.s. I had a sudden change of mind beacause, while I was writing this post, it came to me... i hope it helps

[mmarinho]

sorry I diddn´t get you ?

I am having the same character problem ..

[Renich]

Ok,

Firs of all, I wanna say I have spent a whole week looking for answers elsewhere. The reason of this message is to look for a practical solution.

My Systems:
Server:
Fedora Core 4 (up2dated)
PHP5.04 (cli)
MySQL 14.7 Distrib 4.1.12
using mysqli extension
Apache/2.0.54 (Fedora)

Workstation:
Windows XP SP2
Zend Studio Enterprise 4.02

Well, let me explain the problem:

I'm developing some aplications that require the use of foreign characters. The language is spanish, so you use accends or tilde on some works (example, comisión, niño, etc).

I have a simple query that uses tables (named with this foreign characters). Example:

directorio
- Empresa
- Dirección
- Asistente de Dirección

A query to this database should be as follows:

SELECT *
FROM `Asistente de Dirección`;

This returns an error like:

Table 'directorio.Asistente de Dirección' doesn't exist.

I have changed EVERYTHING to utf-8 charset:

httpd.conf - AddDefaultCharset = UTF-8
php.ini - default_charset = "UTF-8"
mbstring.internal_encodign = UTF-8
mbstring.http_input = UTF-8
mbstring.http_output = UTF-8
mbstring.detect_order = UTF-8,SJIS,ASCII
my.cnf - [mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
old_passwords=1
default-character-set=utf8
[mysql.server]
user=mysql
basedir=/var/lib

[mysqld_safe]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

[mysql]
default-character-set=utf8

This config sets ALL my servers to utf-8

- Apache works perfectly fine in UTF-8, no problems
- PHP encodes everything to utf-8... at least thats what i think
- All my MySQL char variables are set to UTF-8, except "default-character-result" its set to NULL

As far as I know, there is no other config made to be done.

My theory is this:

Consider:

<?php

$link = mysqli_connect('mysql.fedora', 'root', 'idontrecall', 'directorio');

if ( isset($_POST['submit']) && $_POST['submit'] == 'Search' )
{
$query = "SELECT *
FROM `Empresa`,
`Presidente`,
`Asistente de Presidencia`,
`Director`,
`Asistente de Dirección`, `
Centro de Distribución 01`,
`Centro de Distribución 02`,
`Centro de Distribución 03`,
`Centro de Distribución 04`,
`Centro de Distribución 05`,
`Centro de Distribución 06`,
`Centro de Distribución 07`,
`Centro de Distribución 08`,
`Centro de Distribución 09`,
`Centro de Distribución 10`

WHERE Empresa.Emp_Gro LIKE '%$_POST[input]%'
AND Empresa.ID = Presidente.ID
AND Empresa.ID = `Asistente de Presidencia`.ID
AND Empresa.ID = Director.ID
AND Empresa.ID = `Asistente de Dirección`.ID
AND Empresa.ID = `Centro de Distribución 01`.ID
AND Empresa.ID = `Centro de Distribución 02`.ID
AND Empresa.ID = `Centro de Distribución 03`.ID
AND Empresa.ID = `Centro de Distribución 04`.ID
AND Empresa.ID = `Centro de Distribución 05`.ID
AND Empresa.ID = `Centro de Distribución 06`.ID
AND Empresa.ID = `Centro de Distribución 07`.ID
AND Empresa.ID = `Centro de Distribución 08`.ID
AND Empresa.ID = `Centro de Distribución 09`.ID
AND Empresa.ID = `Centro de Distribución 10`.ID";

// Make the query
$result = mysqli_query($link,$query) or die(mysqli_error($link));

// Fetch the results
while ($data = mysqli_fetch_assoc($result))
{
// Print the data obtained
print_my_data($data);
}

}
// Close conection
mysqli_close($link);

?>

if you add a "print $query;" just before it makes the query; and as long as you have your webpage with the charset meta-tag set to "utf-8" and everything utf-8 encoded... you'll get the right query...

this is

SELECT * FROM `Empresa`, `Presidente`, `Asistente de Presidencia`, `Director`, `Asistente de Dirección`, ` Centro de Distribución 01`, `Centro de Distribución 02`, `Centro de Distribución 03`, `Centro de Distribución 04`, `Centro de Distribución 05`, `Centro de Distribución 06`, `Centro de Distribución 07`, `Centro de Distribución 08`, `Centro de Distribución 09`, `Centro de Distribución 10` WHERE Empresa.Emp_Gro LIKE '%try this%' AND Empresa.ID = Presidente.ID AND Empresa.ID = `Asistente de Presidencia`.ID AND Empresa.ID = Director.ID AND Empresa.ID = `Asistente de Dirección`.ID AND Empresa.ID = `Centro de Distribución 01`.ID AND Empresa.ID = `Centro de Distribución 02`.ID AND Empresa.ID = `Centro de Distribución 03`.ID AND Empresa.ID = `Centro de Distribución 04`.ID AND Empresa.ID = `Centro de Distribución 05`.ID AND Empresa.ID = `Centro de Distribución 06`.ID AND Empresa.ID = `Centro de Distribución 07`.ID AND Empresa.ID = `Centro de Distribución 08`.ID AND Empresa.ID = `Centro de Distribución 09`.ID AND Empresa.ID = `Centro de Distribución 10`.ID

following the error
Table 'directorio.Asistente de Dirección' doesn't exist

As you can see, this is not logical. You had your query printed just above and it printed out fine!... Sorry, if i got a little carried away...

This is why i think that the mysqli extension haves a bug. I have my connection, server, etc... variables set to UTF-8 in MySQL. The one that is converting the code while using the "mysqli_fetch_assoc()" function is php...

If anybody knows how to correct this... or how to make the example work... please tell me. I have worked on this too much.

I know that if I change the tablenames, it will work. In fact, a query like

SELECT *
FROM `Empresa`;

Works perfectly fine.

<dramatically> Help me and all the foreign php and mysql users use our own characters! </dramatically>

Things I have checked:
- Searched my.cnf, httpd.conf and php.ini for any "ISO-8859-1","latin1" strings.
- Checked the db file names (/var/lib/mysql/)
- Checked if the script files are UTF-8 encoded
- Checked if the browser is accepting the UTF-8 ch

[mmarinho]

It works the same for my brasilian characters.

I will re-create all DB directlly at comand line interface not using phpmyadmin to this .

Thanks

[Renich]

Actually, I found out that if you do a "set names utf8" query before any query, using php or whatever, will solve the problem.

Remember, just use this query BEFORE any other query.

$query = "set names utf8;";

I hope MySQL adds an option to this in the my.cnf file. Its a pain to do this every time... gotta rewrite a lot of code!

[n0n0x]

I'm also having the same problem and set names utf8 doesn't work. The difference with my problem is that my DB is on a Gentoo box, but there shouldn't be any problem at all. Right now my company is migrating to fedora and at this point we can't do it because of this, i already tryed everything and the data is still being encoding wrong. I don't know what else to do

[Renich]

Well, if you're migrating to fedora, it's great news.

Fedora haves MySQL 5.0 and this version does not have utf-8 problems.

Maybe you don't have utf-8 charset installed on your gentoo distro. It has to be compiled in. In any case, I'm sure you can find something on the MySQL online manual. Here: http://dev.mysql.com/doc/refman/5.0/en/index.html