I18n issues with 4.1.1 and 5.0

Hi guys and Happy Easter to all of you,
I've tried to upgrade to either 4.1.1 or 5.0 recently and spent a week
trying to make my apps work with 4.1.1 or 5.0 and than downgrading back
to 4.0.18.
That's a pity it nearly worked and seems so easy to fix and still
rendered my apps practically unusable.
Now back to details. I have a bunch of database, table and column names
in cyrillic (koi8r).
Generally it's a bad practice to use non-ascii names but offers an easy
localization of
some db-driven apps. The main problem is there's no specific encoding
assosiated
with user, database, table and column values in system tables. They are
now all utf8 internally
but still mysql_install_db declares all these fields BINARY. If client
encoding is anything else but utf8
you won't get readable results doing SELECTs on system tables.
What is more the behaviour of various administrative commands which
retrieve or set user, db ot table values
in system tables like GRANT/SHOW GRANTS, SHOW CREATE TABLE, SHOW BINLOG
EVENTS and so on is inconsistent.
For example you use GRANT smth on a table with cyrillic name and it
works and you can SELECT values
from mysql.db and get meaningful results IF(!) your client encoding is
set to UTF8.
But SHOW GRANTS yields unreadable results no matter what your client
encoding is.
All cyrillic names in SHOW GRANTS output are screwed up. I've tried
different combinations
of client encodings and encodings explicitly set for user/db/table
fields in system tables. No luck.
Same stands for master binary logs where a slave chokes with every query
with non-ascii db/table name.
Viewing the logs (SHOW BINLOG EVENTS) one may notice the queries are OK
and show up properly converted to encoding specified by the client. But
all USE `db` statements inserted by the master are in God knows what
encoding
and show up as meaningless rubbish.
I would suggest to explicitly declare user, db, table and column fields
in system tables to be utf8 and
use normal character set conversion rules when fetching data for the
client (it seems to be working well for userland tables).
A slave can always request the data to be in utf8 format and this seems
to be compatible
with new MySQL layout where each column may use different encoding.
If the requested data can't be converted to the encoding specified by
the client MySQL could either fetch a binary value
or produce a fatal runtime error which is probably a better choice.
Thanks for your help and looking forward for fully working i18n support
in MySQL.
Cheers,
Sasha

PS
I didn't mention minor issues like error messages for languages other
than english are not translated to client encoding,
Also MySQL server treats all file names as utf8 (LOAD DATA, SELECT *
INTO OUTFILE) even if
local file sytem uses different encoding. But that's a real pain in the
tail if you are trying to rebuild
your database from pre 4.1 dumps.


--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org

Re: I18n issues with 4.1.1 and 5.0

Alexander Kirillov writes:
> Hi guys and Happy Easter to all of you,
> I've tried to upgrade to either 4.1.1 or 5.0 recently and spent a week
> trying to make my apps work with 4.1.1 or 5.0 and than downgrading back
> to 4.0.18.
> That's a pity it nearly worked and seems so easy to fix and still
> rendered my apps practically unusable.
> Now back to details. I have a bunch of database, table and column names
> in cyrillic (koi8r).
> Generally it's a bad practice to use non-ascii names but offers an easy
> localization of
> some db-driven apps. The main problem is there's no specific encoding
> assosiated
> with user, database, table and column values in system tables. They are
> now all utf8 internally
> but still mysql_install_db declares all these fields BINARY. If client
> encoding is anything else but utf8
> you won't get readable results doing SELECTs on system tables.
> What is more the behaviour of various administrative commands which
> retrieve or set user, db ot table values
> in system tables like GRANT/SHOW GRANTS, SHOW CREATE TABLE, SHOW BINLOG
> EVENTS and so on is inconsistent.
> For example you use GRANT smth on a table with cyrillic name and it
> works and you can SELECT values
> from mysql.db and get meaningful results IF(!) your client encoding is
> set to UTF8.
> But SHOW GRANTS yields unreadable results no matter what your client
> encoding is.
> All cyrillic names in SHOW GRANTS output are screwed up. I've tried
> different combinations
> of client encodings and encodings explicitly set for user/db/table
> fields in system tables. No luck.
> Same stands for master binary logs where a slave chokes with every query
> with non-ascii db/table name.
> Viewing the logs (SHOW BINLOG EVENTS) one may notice the queries are OK
> and show up properly converted to encoding specified by the client. But
> all USE `db` statements inserted by the master are in God knows what
> encoding
> and show up as meaningless rubbish.
> I would suggest to explicitly declare user, db, table and column fields
> in system tables to be utf8 and
> use normal character set conversion rules when fetching data for the
> client (it seems to be working well for userland tables).
> A slave can always request the data to be in utf8 format and this seems
> to be compatible
> with new MySQL layout where each column may use different encoding.
> If the requested data can't be converted to the encoding specified by
> the client MySQL could either fetch a binary value
> or produce a fatal runtime error which is probably a better choice.
> Thanks for your help and looking forward for fully working i18n support
> in MySQL.
> Cheers,
> Sasha

Hi!

Thank you for writting to us.

For the time being, the privilege and errors / warnings system will
work only as described above.

We have on our todo full localization of the both, but it will take
some time to be implemented.

--

Sincerely,

--
For technical support contracts, go to https://order.mysql.com/?ref=msmi
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Sinisa Milivojevic
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB
/_/ /_/\_, /___/\___\_\___/ Full time Developer and Support Coordinator
<___/ www.mysql.com Larnaca, Cyprus



--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org

Ordering of Numbers with NULL records

Hi,

I have a table of tinyints where these records can also be NULL. I want to
return an ordered list of numbers, placing the NULL values at the end of the
list. By default, MySQL doesn't do this, so I had to modify the query used.

See below:

mysql> create table test (number tinyint unsigned);
Query OK, 0 rows affected (0.00 sec)

mysql> describe test;
+--------+---------------------+------+-----+---------+----- --+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+----- --+
| number | tinyint(3) unsigned | YES | | NULL | |
+--------+---------------------+------+-----+---------+----- --+
1 row in set (0.00 sec)

mysql> insert test set number=8;
Query OK, 1 row affected (0.00 sec)

mysql> insert test set number=2;
Query OK, 1 row affected (0.00 sec)

mysql> insert test set number=66;
Query OK, 1 row affected (0.00 sec)

mysql> insert test set number=13;
Query OK, 1 row affected (0.00 sec)

mysql> insert test set number=null;
Query OK, 1 row affected (0.00 sec)

mysql> insert test set number=91;
Query OK, 1 row affected (0.01 sec)

mysql> select * from test;
+--------+
| number |
+--------+
| 8 |
| 2 |
| 66 |
| 13 |
| NULL |
| 91 |
+--------+
6 rows in set (0.01 sec)

mysql> select * from test order by number;
+--------+
| number |
+--------+
| NULL |
| 2 |
| 8 |
| 13 |
| 66 |
| 91 |
+--------+
6 rows in set (0.00 sec)


This order is NOT what I want because all NULL values are first in the list,
so I tried the other queries below:


QUERY 1

mysql> select * from test order by number is null, number;
+--------+
| number |
+--------+
| 2 |
| 8 |
| 13 |
| 66 |
| 91 |
| NULL |
+--------+
6 rows in set (0.00 sec)

QUERY 2

mysql> select * from test order by number is not null, number;
+--------+
| number |
+--------+
| NULL |
| 2 |
| 8 |
| 13 |
| 66 |
| 91 |
+--------+
6 rows in set (0.00 sec)

QUERY 3

mysql> select * from test order by number, number is null;
+--------+
| number |
+--------+
| NULL |
| 2 |
| 8 |
| 13 |
| 66 |
| 91 |
+--------+
6 rows in set (0.00 sec)

QUERY 4

mysql> select * from test order by number, number is not null;
+--------+
| number |
+--------+
| NULL |
| 2 |
| 8 |
| 13 |
| 66 |
| 91 |
+--------+
6 rows in set (0.00 sec)


Query 1 appears to give the result I want (with null values being last in
the list and numbers being in order). However, is this query actually
correct? - because I would read this as:

-> select all records from table test ordered first by records which ARE
NULL (eg, records which contain NO number) and then order by number (with
records which DO contain a number). This would suggest that the result
should actually have NULL entires first, just like all the other results of
queries 2, 3 and 4 (or am I missing something?).

Can someone clarify what is going on here? (I'm not sure if this is a bug or
not).

Regards...

--
Mark


--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org

Re: Ordering of Numbers with NULL records

Hi Mark,

There is no bug. :-) Query 1 does what you want and behaves correctly.
When a non-NULL number is compared with IS NULL, it returns 0; whereas
NULL IS NULL returns 1. Since 0 obviously sorts before 1, you can see
how you get those results. You can verify this with a simple query:

SELECT 123 IS NULL, NULL IS NULL;


Hope that helps.


Matt


----- Original Message -----
From: "Mark Hedges"
Sent: Tuesday, April 13, 2004 4:41 PM
Subject: Ordering of Numbers with NULL records


> Hi,
>
> I have a table of tinyints where these records can also be NULL. I
want to
> return an ordered list of numbers, placing the NULL values at the end
of the
> list. By default, MySQL doesn't do this, so I had to modify the query
used.
>
> See below:
>
> mysql> create table test (number tinyint unsigned);
> Query OK, 0 rows affected (0.00 sec)
>
> mysql> describe test;
> +--------+---------------------+------+-----+---------+----- --+
> | Field | Type | Null | Key | Default | Extra |
> +--------+---------------------+------+-----+---------+----- --+
> | number | tinyint(3) unsigned | YES | | NULL | |
> +--------+---------------------+------+-----+---------+----- --+
> 1 row in set (0.00 sec)
>
> mysql> insert test set number=8;
> Query OK, 1 row affected (0.00 sec)
>
> mysql> insert test set number=2;
> Query OK, 1 row affected (0.00 sec)
>
> mysql> insert test set number=66;
> Query OK, 1 row affected (0.00 sec)
>
> mysql> insert test set number=13;
> Query OK, 1 row affected (0.00 sec)
>
> mysql> insert test set number=null;
> Query OK, 1 row affected (0.00 sec)
>
> mysql> insert test set number=91;
> Query OK, 1 row affected (0.01 sec)
>
> mysql> select * from test;
> +--------+
> | number |
> +--------+
> | 8 |
> | 2 |
> | 66 |
> | 13 |
> | NULL |
> | 91 |
> +--------+
> 6 rows in set (0.01 sec)
>
> mysql> select * from test order by number;
> +--------+
> | number |
> +--------+
> | NULL |
> | 2 |
> | 8 |
> | 13 |
> | 66 |
> | 91 |
> +--------+
> 6 rows in set (0.00 sec)
>
>
> This order is NOT what I want because all NULL values are first in the
list,
> so I tried the other queries below:
>
>
> QUERY 1
>
> mysql> select * from test order by number is null, number;
> +--------+
> | number |
> +--------+
> | 2 |
> | 8 |
> | 13 |
> | 66 |
> | 91 |
> | NULL |
> +--------+
> 6 rows in set (0.00 sec)
>
> QUERY 2
>
> mysql> select * from test order by number is not null, number;
> +--------+
> | number |
> +--------+
> | NULL |
> | 2 |
> | 8 |
> | 13 |
> | 66 |
> | 91 |
> +--------+
> 6 rows in set (0.00 sec)
>
> QUERY 3
>
> mysql> select * from test order by number, number is null;
> +--------+
> | number |
> +--------+
> | NULL |
> | 2 |
> | 8 |
> | 13 |
> | 66 |
> | 91 |
> +--------+
> 6 rows in set (0.00 sec)
>
> QUERY 4
>
> mysql> select * from test order by number, number is not null;
> +--------+
> | number |
> +--------+
> | NULL |
> | 2 |
> | 8 |
> | 13 |
> | 66 |
> | 91 |
> +--------+
> 6 rows in set (0.00 sec)
>
>
> Query 1 appears to give the result I want (with null values being last
in
> the list and numbers being in order). However, is this query actually
> correct? - because I would read this as:
>
> -> select all records from table test ordered first by records which
ARE
> NULL (eg, records which contain NO number) and then order by number
(with
> records which DO contain a number). This would suggest that the
result
> should actually have NULL entires first, just like all the other
results of
> queries 2, 3 and 4 (or am I missing something?).
>
> Can someone clarify what is going on here? (I'm not sure if this is a
bug or
> not).
>
> Regards...
>
> --
> Mark


--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org