Hi,
I have mysql 4.0 db with configuration:
set-variable = default-character-set=latin2
set-variable = character-set=latin2
now I'm trying to migrate to mysql 4.1.12 My current config is:
character-set-server=latin2
The problem is set. mysql 4.1 by default
uses 'latin1' so in all queries (from previously latin2 mysql 4.0 database) I
don't have latin2 characters just '?' chars. That's obvious because due to
default latin1 in 4.1.
SET NAMES 'latin2' from client side of coruse cures whole problem but I
can't fix every possible application that my users have installed (and if
there are thousands of users this is simply impossible).
What's the proper way of dealing with this problem? I've tried to use some
nasty hacks like init-connect="SET NAMES latin2" and this half-works - I see
latin2 characters in server responses but it breaks for example phpmyadmin
(characters are broken in results; when I drop init-connect hack phpmyadmin
works nicely). So init-connect isn't usable.
Now when connecting with mysql command line client then I by default get
latin1 but can change easily to latin2 using
[mysql]
default-character-set=latin2
in .my.cnf file. That's great but this works only for mysql cmd line client
_only_ while my primary concern is php.
Now is the funny part, there is no .my.cnf file, I connect with mysql cmdline.
mysql\s
mysql Ver 14.7 Distrib 4.1.12, for pld-linux-gnu (powerpc) using readline 4.3
Connection id: 2
Current database:
Current user: mysql@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 4.1.12-log
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: latin2
Db characterset: latin2
Client characterset: latin1
Conn. characterset: latin1
UNIX socket: /var/lib/mysql/mysql.sock
Uptime: 34 min 35 sec
Threads: 1 Questions: 12 Slow queries: 0 : 11 Flush tables: 1
tables: 0 Queries per second avg: 0.006
mysqlshow variables like '%char%';
| Variable_name | Value |
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | latin2 |
| character_set_results | latin1 |
| character_set_server | latin2 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
7 rows in set (0.00 sec)
As you can see client/connection/results are latin1 BUT:
mysqlselect @@global.character_set_client,
@@, @@global.character_set_database,
@@global.character_set_results, @@global.character_set_results,
@@global.character_set_server;
| @@global.character_set_client | @@ |
@@global.character_set_database | @@global.character_set_results |
@@global.character_set_results | @@global.character_set_server |
| latin2 | latin2 | latin2
| latin2 | latin2 | latin2
|
1 row in set (0.00 sec)
now it tells me that these are latin2!?
Anyway primary question is how to deal with characters in mysql 4.0->4.1
conversion (how to get latin2 as default in client connections)? I've talked
with few people on #mysql on freenode but we haven't seen any solution.

Hello.

For a pity, I could give explanations only for your query about
selecting @@global.xxx variables. I think server returns correct
results, because you're selecting global variables, while
character_set_client, character_set_connection, character_set_results
are session variables. And with SET NAMES you're setting
@@character_xxx variables which are synonym for @@session.character_xxx.
In what way have you done your upgrade? If you haven't used mysqldump
you could get some problems. Make the dump, and restore it setting
the correct connection variables for mysql program. Be aware of that
mysqldump could put SET NAMES at the beginning of the dump file. Use
set-names=latin2 for it. See:

Arkadiusz Miskiewicz <arekm (AT) pld-linux (DOT) orgwrote:
Hi,

I have mysql 4.0 db with configuration:
set-variable =3D default-character-set=3Dlatin2
set-variable =3D character-set=3Dlatin2

now I'm trying to migrate to mysql 4.1.12 My current config is:
character-set-server=3Dlatin2

The problem is set. mysql 4.1 by default=
=20
uses 'latin1' so in all queries (from previously latin2 mysql 4.0 database)=
I=20
don't have latin2 characters just '?' chars. That's obvious because due to=
=20
default latin1 in 4.1.

SET NAMES 'latin2' from client side of coruse cures whole problem but I=
=20
can't fix every possible application that my users have installed (and if=20
there are thousands of users this is simply impossible).

What's the proper way of dealing with this problem? I've tried to use some=
=20
nasty hacks like init-connect=3D"SET NAMES latin2" and this half-works - I =
see=20
latin2 characters in server responses but it breaks for example phpmyadm=
in=20
(characters are broken in results; when I drop init-connect hack phpmyadmin=
=20
works nicely). So init-connect isn't usable.

Now when connecting with mysql command line client then I by default get=20
latin1 but can change easily to latin2 using=20
[mysql]
default-character-set=3Dlatin2
in .my.cnf file. That's great but this works only for mysql cmd line client=
=20
_only_ while my primary concern is php.

Now is the funny part, there is no .my.cnf file, I connect with mysql cmdli=
ne.
mysql\s
=2D
mysql Ver 14.7 Distrib 4.1.12, for pld-linux-gnu (powerpc) using readline =
4.3

Connection id: 2
Current database:
Current user: mysql@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 4.1.12-log
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: latin2
Db characterset: latin2
Client characterset: latin1
Conn. characterset: latin1
UNIX socket: /var/lib/mysql/mysql.sock
Uptime: 34 min 35 sec

Threads: 1 Questions: 12 Slow queries: 0 : 11 Flush tables: 1 =
n=20
tables: 0 Queries per second avg: 0.006
=2D

mysqlshow variables like '%char%';

| Variable_name | Value |

| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | latin2 |
| character_set_results | latin1 |
| character_set_server | latin2 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |

7 rows in set (0.00 sec)

As you can see client/connection/results are latin1 BUT:

mysqlselect @@global.character_set_client,=20
@@, @@global.character_set_database,=20
@@global.character_set_results, @@global.character_set_results,=20
@@global.character_set_server;

=2D++
=2D++
| @@global.character_set_client | @@ |=20
@@global.character_set_database | @@global.character_set_results |=20
@@global.character_set_results | @@global.character_set_server |

=2D++
=2D++
| latin2 | latin2 | latin=
2 =20
| latin2 | latin2 | latin2 =
=20
|

=2D++
=2D++
1 row in set (0.00 sec)

now it tells me that these are latin2!?

Anyway primary question is how to deal with characters in mysql 4.0->4.1=20
conversion (how to get latin2 as default in client connections)? I've talke=
d=20
with few people on #mysql on freenode but we haven't seen any solution.

=2D-=20
Arkadiusz Mi=B6kiewicz PLD/Linux Team
http://www.t17.ds.pwr.wroc.pl/~misiek/ http://ftp.pld-linux.org/

Thursday 07 of July 2005 00:16, Gleb Paharenko wrote:
Hello.
--
For a pity, I could give explanations only for your query about
selecting @@global.xxx variables. I think server returns correct
results, because you're selecting global variables, while
character_set_client, character_set_connection, character_set_results
are session variables. And with SET NAMES you're setting
@@character_xxx variables which are synonym for @@session.character_xxx.
I see.

In what way have you done your upgrade? If you haven't used mysqldump
you could get some problems. Make the dump, and restore it setting
the correct connection variables for mysql program. Be aware of that
mysqldump could put SET NAMES at the beginning of the dump file. Use
set-names=latin2 for it. See:

Dump & restore was done properly. dump on 4.0, add set names latin2; load into
4.1. The problem is that by default connections from client are as latin1, db
is latin2 so servers needs to do conversion from latin2->latin1 which can't
be done and thus I'm getting '?' characters instead of latin2 characters.

The thing I need is how to force default latin2 in all client connections even
if client won't request latin2 by using set names.

Tried doing things like in mysqld.conf:

init-connect = SET @lchar = IF(@@session.character_set_client = _utf8"latin1",
@@global.character_set_client, @@session.character_set_client); SET
character_set_client = @lchar; SET character_set_results = @lchar; SET
character_set_connection = @lchar;

but that doesn't work unfortunately from init-connect (works from mysql
cmdline client) ;-(

Hello.

I've tested your solution. It doesn't work for users which have SUPER
privilege. This mentioned at:

However, it works with with ordinary users which don't have SUPER
privilege. Here are pieces of my my.cnf (the init_connect is one big string
without line breaks):

[client]

default_character_set=latin1

[mysqld]
default_character_set=latin2
init_connect='SET @lchar = IF(@@session.character_set_client =
_utf8"latin1", @@global.character_set_client,
@@session.character_set_client); set
@@session.character_set_client=@lchar; set
@@@lchar; set
@@session.character_set_results=@lchar; '

When root user connects init_connect doesn't execute and we see:
mysqlshow variables like '%char%';

| Variable_name | Value |

| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | latin2 |
| character_set_results | latin1 |
| character_set_server | latin2 |
| character_set_system | utf8 |
|

When user without SUPER privilege connects we see:
| Variable_name | Value |

| character_set_client | latin2 |
| character_set_connection | latin2 |
| character_set_database | latin2 |
| character_set_results | latin2 |
| character_set_server | latin2 |
| character_set_system | utf8 |

So it works for me.

Dump & restore was done properly. dump on 4.0, add set names latin2; load i=
nto=20
4.1. The problem is that by default connections from client are as latin1, =
db=20
is latin2 so servers needs to do conversion from latin2->latin1 which can't=
=20
be done and thus I'm getting '?' characters instead of latin2 characters.

The thing I need is how to force default latin2 in all client connections e=
ven=20
if client won't request latin2 by using set names.

Tried doing things like in mysqld.conf:

init-connect =3D SET @lchar =3D IF(@@session.character_set_client =3D _utf8=
"latin1",=20
@@global.character_set_client, @@session.character_set_client); SET=20
character_set_client =3D @lchar; SET character_set_results =3D @lchar; SET=
=20
character_set_connection =3D @lchar;

but that doesn't work unfortunately from init-connect (works from mysql=20
cmdline client) ;-(
=2D-=20
Arkadiusz Mi=B6kiewicz PLD/Linux Team
http://www.t17.ds.pwr.wroc.pl/~misiek/ http://ftp.pld-linux.org/

Monday 11 of July 2005 16:24, Gleb Paharenko wrote:
Hello.

I've tested your solution. It doesn't work for users which have SUPER
privilege. This mentioned at:

However, it works with with ordinary users which don't have SUPER
privilege. Here are pieces of my my.cnf (the init_connect is one big string
without line breaks):

[client]

default_character_set=latin1

[mysqld]
default_character_set=latin2
init_connect='SET @lchar = IF(@@session.character_set_client =
_utf8"latin1", @@global.character_set_client,
@@session.character_set_client); set
@@session.character_set_client=@lchar; set
@@@lchar; set
@@session.character_set_results=@lchar; '
[]
So it works for me.

I've ended doing this in a different way. I've created a patch which forces a
file to be read - /etc/mysql/mysql-client.conf (which is the same as .my.cnf)
at mysql_init() time. All clients that use libmysqlclient.so should read it
now.

Now I can put defaults in that global config:

[arekm@arm ~]$ more /etc/mysql/mysql-client.conf
[client]
default-character-set=latin2

Now all my clients connect with latin2 as default.

Any possible problems with this approach?

Hello.

Any possible problems with this approach?

It is good, if it solved your problems.


>So it works for me.

I've ended doing this in a different way. I've created a patch which forces=
a=20
file to be read - /etc/mysql/mysql-client.conf (which is the same as .my.cn=
f)=20
at mysql_init() time. All clients that use libmysqlclient.so should read it=
=20
now.

ev=3D1.1

Now I can put defaults in that global config:

[arekm@arm ~]$ more /etc/mysql/mysql-client.conf
[client]
default-character-set=3Dlatin2

Now all my clients connect with latin2 as default.

Any possible problems with this approach?

=2D-=20
Arkadiusz Mi=B6kiewicz PLD/Linux Team
http://www.t17.ds.pwr.wroc.pl/~misiek/ http://ftp.pld-linux.org/

Hello.

It is not clear from you message what's the problem. From your
calculations I can see that the length of int field in a flat
file row (BTW please send a piece of your file) is different
in mysqldump programs with different versions? How is the length
of integer fields related to character set? Please provide
more information about the problem and if it is possible send a test
case.

Vivian Wang <vivianwang (AT) tcindex (DOT) comwrote:
[-- text/plain, encoding 7bit, charset: us-ascii, 132 lines --]

the version is 4.1.12.
show variables like this,

| character_set_client |
latin1
| character_set_connection |
latin1
| character_set_database |
latin1
| character_set_results | latin1
| character_set_server |
latin1
| character_set_system |
utf8
| character_sets_dir |
/usr/share/mysql/charsets/
| collation_connection |
latin1_swedish_ci
| collation_database |
latin1_swedish_ci
| collation_server | latin1_swedish_ci

If the table is like test(name char(30), id1 int(4), id2 int(4))
When I use mysqldump mysql version 3.23.?, l have the flat file row
length is 30+4+4=38.
When I use mysqldump mysql verson 4.1.12, I have the flat file row
length is 30+11+11=52.

I tried mysqldump , I still got a row length=52.

What I should do?

Gleb Paharenko wrote: