PHP Programming - new phpMyAdmin collate and convert oddness

Author

new phpMyAdmin collate and convert oddness

news@celticbear.com

2005-12-13, 6:59 pm

We just upgraded our phpMyAdmin GUI to the latest stable version, and
now the SQL queries are doing odd additions.
First, without changing the database it's connecting to at all, there's

a new column "collation" for all the tables, and the value in all of
them is: "latin1_swedish_ci".
I'm doubting that's what it should be.

And any SQL querey formulates like:

SELECT *
FROM `accounts`
WHERE `company` LIKE CONVERT( _utf8 'cyber'
USING latin1 )
COLLATE latin1_swedish_ci
LIMIT 0 , 30

with new CONVERT and COLLATE, instead of the usual queries like:

SELECT *
FROM `accounts`
WHERE `company` LIKE '%cyber%'
LIMIT 0 , 30

I can't find in the documentation why it's doing this, and looking
through conf.inc.php, can't find any setting that may apply.

Any clue what I should look at?
Is it simply displaying some quality about the mySQL database that had
heretofore not been exposed? If Collation is an attribute that is there

and must now be taken into account, it looks like I can change it in
"Operations," what's the proper, standard English setting?
"ascii_gen_cli"?

Thanks for any feedback!
-Liam

Juliette

2005-12-13, 9:56 pm

news@celticbear.com wrote:
> We just upgraded our phpMyAdmin GUI to the latest stable version, and
> now the SQL queries are doing odd additions.
> First, without changing the database it's connecting to at all, there's
>
> a new column "collation" for all the tables, and the value in all of
> them is: "latin1_swedish_ci".
> I'm doubting that's what it should be.
>
>
> And any SQL querey formulates like:
>
>
> SELECT *
> FROM `accounts`
> WHERE `company` LIKE CONVERT( _utf8 'cyber'
> USING latin1 )
> COLLATE latin1_swedish_ci
> LIMIT 0 , 30
>
>
> with new CONVERT and COLLATE, instead of the usual queries like:
>
>
> SELECT *
> FROM `accounts`
> WHERE `company` LIKE '%cyber%'
> LIMIT 0 , 30
>
>
> I can't find in the documentation why it's doing this, and looking
> through conf.inc.php, can't find any setting that may apply.
>
>
> Any clue what I should look at?
> Is it simply displaying some quality about the mySQL database that had
> heretofore not been exposed? If Collation is an attribute that is there
>
> and must now be taken into account, it looks like I can change it in
> "Operations," what's the proper, standard English setting?
> "ascii_gen_cli"?
>
>
> Thanks for any feedback!
> -Liam
>

You are using a MySQL version >= 4.1 which supports charsets and
collation. Have a look at the mysql documentation.

For English charset you could use latin1_general_ci

Grz, Jrf

news@celticbear.com

2005-12-14, 6:59 pm

Juliette wrote:
> news@celticbear.com wrote:
>
>
> You are using a MySQL version >= 4.1 which supports charsets and
> collation. Have a look at the mysql documentation.
>
> For English charset you could use latin1_general_ci
>
> Grz, Jrf

Ah, yes, I forgot we updated our mySQL to 4.1.12 as well.
Gawd it's insane trying to get plain, understandable (to me) clear info
in the mySQL site!
What I don't understand, and can't find, is why the appearant setting
for collation on the database is swedish, when we never set any
character set or collation.
But taking a closer look, on the phpMyAdmin home page, the language is
set to "English (en-utf-8)", the MySQL charset to "UTF-8 Unicode
(utf8)", and MySQL connection collation to "utf8_general_ci", but on
all the tables and databases, the collation is "latin1_swedish_ci".

Do I have to alter every table (GAH!) to match thestated collation? Is
having these swedish collation settings causing some unseen issues I
should be aware of (aside from making SQL queries in phpMyAdmin
extremely annoying.)

Thanks for any feedback!
-Liam

sanjose95148

2005-12-20, 1:56 am

I had the same MySQL charset and MySQL connection collation as yours. They are probably coming from default. So I changed MySQL connection collation to utf8_unicode_ci and set the collation of field to utf8_unicode_ci in all tables in databases. Now MySQL connection collation and table collation are the same, there is no need to convert the collation while fetching a query. Hope it will work at your system.

quote:
Originally posted by news@celticbear.com
Juliette wrote:
> news@celticbear.com wrote:
>
>
> You are using a MySQL version >= 4.1 which supports charsets and
> collation. Have a look at the mysql documentation.
>
> For English charset you could use latin1_general_ci
>
> Grz, Jrf

Ah, yes, I forgot we updated our mySQL to 4.1.12 as well.
Gawd it's insane trying to get plain, understandable (to me) clear info
in the mySQL site!
What I don't understand, and can't find, is why the appearant setting
for collation on the database is swedish, when we never set any
character set or collation.
But taking a closer look, on the phpMyAdmin home page, the language is
set to "English (en-utf-8)", the MySQL charset to "UTF-8 Unicode
(utf8)", and MySQL connection collation to "utf8_general_ci", but on
all the tables and databases, the collation is "latin1_swedish_ci".

Do I have to alter every table (GAH!) to match thestated collation? Is
having these swedish collation settings causing some unseen issues I
should be aware of (aside from making SQL queries in phpMyAdmin
extremely annoying.)

Thanks for any feedback!
-Liam

Sponsored Links