Moving Database

[Darkness Soul]

Yo,

 

Guys, I never saw it before... I've an application what is using MySQL, and the search engine use an Union command. The project has about one year old, and now I move the database to a new host, but, before, there are all collations equal LATIN1, now they are UTF8. Ok, I convert all the strings, setup all the tables and the database to use UTF8 as default. No problems yet. So, I try to search something with "a" in the name...

 

Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (latin1_swedish_ci,COERCIBLE) for operation 'UNION'

 

"Ok" I had think, "its just some field/table I had missed to convert...". After several minutes looking to the database, nothing was wrong.

 

I try to print the SQL, it's look perfect; So I try to copy-past it inside the "SQL execute" field into phpMyAdmin. It's works wonderful...

 

"hmmmm, what now?" So I try to convert the php file to UTF8 (it was ANSI) (desperation); try to use $sql = utf8_convert ( $string ); and now, I don't know 'what to do'.

 

I think someone else may know what is wrong, or had the same problem. So, may I be helped?

 

Thank you, guys...

 

dSoul

[fenway]

There's obviously some collation that was missed... post the detailed column info for each table involved.

[Darkness Soul]

Our server is accent-sensitive, so, we need a lot of replaces..

 

(
    SELECT
        tbE.id, "E" AS filtro, tbE.nomefantasia AS nomefantasia, tbE.endereco AS endereco, tbE.numero AS numero, tbE.complemento AS complemento, 
        tbBar.bairro AS bairro, tbE.cep AS cep, tbCid.cidade AS cidade, tbUF.sigla AS sigla, tbE.telefone AS telefone,
        tbE.telefone2 AS telefone2, tbE.tel0800 AS tel0800, tbE.email AS email, tbE.site AS site, tbE.logotipo AS logotipo ,
        IF(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(LCASE(palavrachave_vip),"-"," "),";"," "),"í","I"),"ä","A"),"ë","E"),"ü","U"),"é","E"),"á","A"),"ú","U"),"ã","A"),"õ","O"),"ê","E"),"ô","O"),"ç","C") LIKE "%a%" AND palavrachave_vip != "", "V", IF(tbE.cliente = "S", "S", "N")) AS vip
    FROM tbEmpresas AS tbE
    INNER JOIN tbEstados AS tbUF ON tbUF.id = tbE.idestado
    INNER JOIN tbCidades AS tbCid ON tbCid.id = tbE.idcidade
    INNER JOIN tbBairros AS tbBar ON tbBar.id = tbE.idbairro
    INNER JOIN tbCategoria AS tbCat ON tbCat.id = tbE.idcategoria
    INNER JOIN tbSubCategoria AS tbSub ON tbSub.id = tbE.idsubcategoria
    WHERE REPLACE(LCASE(tbE.nomefantasia),"-"," ")
    LIKE "%%" AND(
        REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(LCASE(palavrachave),"-"," "),";"," "),"í","I"),"ä","A"),"ë","E"),"ü","U"),"é","E"),"á","A"),"ú","U"),"ã","A"),"õ","O"),"ê","E"),"ô","O"),"ç","C")
        LIKE "%a%" OR REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(LCASE(palavrachave_vip),"-"," "),";"," "),"í","I"),"ä","A"),"ë","E"),"ü","U"),"é","E"),"á","A"),"ú","U"),"ã","A"),"õ","O"),"ê","E"),"ô","O"),"ç","C")
        LIKE "%a%" OR REPLACE(UCASE(tbBar.bairro),"-"," ")
        LIKE "%A%" OR REPLACE(UCASE(tbE.cep),"-"," ")
        LIKE "%A%" OR REPLACE(UCASE(tbE.endereco),"-"," ")
        LIKE "%A%" OR REPLACE(UCASE(tbE.nomefantasia),"-"," ")
        LIKE "%A%"
    )
    AND REPLACE(UCASE(tbE.endereco),"-"," ")
    LIKE "%%" AND(
        REPLACE(tbE.telefone,"-","")
        LIKE "%%" OR REPLACE(tbE.telefone2,"-","")
        LIKE "%%" OR REPLACE(tbE.tel0800,"-","")
        LIKE "%%"
    )
    AND(
        REPLACE(UCASE(tbCat.descricao),"-"," ")
        LIKE "%%" OR UCASE(tbSub.descricao)
        LIKE "%%" OR REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(LCASE(palavrachave),"-"," "),";"," "),"í","I"),"ä","A"),"ë","E"),"ü","U"),"é","E"),"á","A"),"ú","U"),"ã","A"),"õ","O"),"ê","E"),"ô","O"),"ç","C")
        LIKE "%%"
    )
    AND tbE.id > 0 AND tbE.idcidade = 1 
    AND(tbE.datacadastro != "0000-00-00 00:00:00" OR tbE.datacadastro != "00000000000000")
    AND(tbE.cliente = "S" OR tbE.cliente = "N") AND tbE.ativo = "S"
)
UNION
(
    SELECT
        tbTel.id, "T" AS filtro, tbTel.str_nome AS nome_fantasia, "" AS endereco, "" AS numero, "" AS complemento,
        "" AS bairro, "" AS cep, "" AS cidade, "" AS sigla, tbTel.str_telefone AS telefone, "" AS telefone2, "" AS tel0800,
        "" AS email, "" AS site, "" AS logotipo, "N" AS vip
    FROM tbCidades_Telefones AS tbTel 
    WHERE(tbTel.str_nome LIKE "%%" AND tbTel.str_nome LIKE "%A%" AND tbTel.str_nome LIKE "%%" )
    AND tbTel.flag_ativo = "A" AND tbTel.str_telefone LIKE "%%" AND tbTel.id_cidade = 1 
) ; 

[fenway]

Yikes... can't you collate it "down" without have to do all those replaces..? Still we have to narrow down the column that's the problem, and I don't see collation info.