Feature: Charset Option For V4.2

[sdozono]

It is unrealistic to expect that always each server, and php and MYSQL has proper (and desirable) Charset settings. And so far, we can not synchronize MYSQL 4.0 to 4.1 because of charset problem. (especially direct connecting. garbage characters) So, it will be great if SQLyog has 'Charset' option….

*IDEA1*

http://www.yourdomain.com/SQLyogtunnel.php

30000

localhost

abc_test

****** 3306

abc_test

MYSQL_CHARSET_CODE

*IDEA2(for MYSQL 4.1 or later…)*

`test_table`

CHARSET_CODE

(I hope if there is no specific setting, SQLyog sets default charset codes..)

and for SQLyogtunnel.php, please publish “SET NAMES **charset**” each time after connecting to MYSQL. Otherwise, it will not work properly.

Thank you for your help in advance!

[Ritesh]

Quote:

(I hope if there is no specific setting, SQLyog sets default charset codes..)

Currently on successful connection, SJA executes:

Code:

show variables like '%character%'

in both source and target servers. It then executes:

Code:

Set character_set_connection=charset
Set character_set_results=charset
Set character_set_client=charset

with charset being the value returned from show variables query.

[peterlaursen]

A question:

Can't CHARSET set be specific for each DB, table and even column ?

And what about COLLATION with MySQL 4.1 ++

[sdozono]

Sorry, my explanation wasn't enough…

The cause of this problem was SQLyogtunnel.php.

I tested with two servers with “MYSQL DIRECT connection”, and it worked properly!

MYSQL 4.0 -> MYSQL 4.1 Japanese encoding(ujis). => successful!

MYSQL 4.1 -> MYSQL 4.1 Japanese encoding(ujis). => successful!

But still there is a problem for php tunneling.

When you use php, you need to publish “Set character_set….” everytime after connecting to MYSQL.

In my experiment. SQLyogtunnel.php publishes “mysql_connect” repeatedly.

['Connected' shows SQLyog reconnect to MYSQL. Am I right?]

[MYSQL 4.1(DIRECT connection) => MYSQL 4.1(SQLyogtunnel.php)]

[Table copy]

=================

Connected

select version()

Connected

show variables like '%character%'

Connected

Set character_set_connection=ujis

Connected

Set character_set_results=ujis

Connected

Set character_set_client=ujis

Connected

show databases

Connected

use `test`

Connected

SET FOREIGN_KEY_CHECKS=0

Connected

drop table if exists `test`.`TestTest`

Connected

CREATE TABLE `TestTest` (

`id` int(11) NOT NULL auto_increment,

`name` varchar(100) NOT NULL default '',

PRIMARY KEY (`id`)

) ENGINE=MyISAM DEFAULT CHARSET=ujis

Connected

insert into `TestTest` values ('1', 'ujis??????ujis' );insert into `TestTest` values ('2', '???????' );insert into `TestTest` values ('10', '???????' );insert into `TestTest` values ('4', '?????' );insert into `TestTest` values ('5', '????????' );insert into `TestTest` values ('6', '??????' )

Connected

SET FOREIGN_KEY_CHECKS=1

=================

I think SQLyog will work if the script goes like..

=================

Connected

select version()

show variables like '%character%'

Set character_set_connection=ujis

Set character_set_results=ujis

Set character_set_client=ujis

show databases

use `test`

SET FOREIGN_KEY_CHECKS=0

drop table if exists `test`.`TestTest`

CREATE TABLE `TestTest` (

`id` int(11) NOT NULL auto_increment,

`name` varchar(100) NOT NULL default '',

PRIMARY KEY (`id`)

) ENGINE=MyISAM DEFAULT CHARSET=ujis

insert into `TestTest` values ('1', 'ujisORIGINALujis' );insert into `TestTest` values ('2', 'ORIGINAL' );insert into `TestTest` values ('10', 'ORIGINAL' );insert into `TestTest` values ('4', 'ORIGINAL' );insert into `TestTest` values ('5', 'ORIGINAL' );insert into `TestTest` values ('6', 'ORIGINAL' )

SET FOREIGN_KEY_CHECKS=1

=================

or,

=================

Connected

select version()

Connected

show variables like '%character%'

Connected

SET NAMES ujis

show databases

Connected

SET NAMES ujis

use `test`

Connected

SET NAMES ujis

SET FOREIGN_KEY_CHECKS=0

Connected

SET NAMES ujis

drop table if exists `test`.`TestTest`

Connected

SET NAMES ujis

CREATE TABLE `TestTest` (

`id` int(11) NOT NULL auto_increment,

`name` varchar(100) NOT NULL default '',

PRIMARY KEY (`id`)

) ENGINE=MyISAM DEFAULT CHARSET=ujis

Connected

SET NAMES ujis

insert into `TestTest` values ('1', 'ujisORIGINAL_KANJIujis' );insert into `TestTest` values ('2', 'ORIGINAL_KANJI' );insert into `TestTest` values ('10', 'ORIGINAL_KANJI' );insert into `TestTest` values ('4', 'ORIGINAL_KANJI' );insert into `TestTest` values ('5', 'ORIGINAL_KANJI' );insert into `TestTest` values ('6', 'ORIGINAL_KANJI' )

Connected

SET NAMES ujis

SET FOREIGN_KEY_CHECKS=1

=================

Thank you for reading!

🙂

[peterlaursen]

still character and collation can be defined on a per table or per column basis

If for instance you want to create a japanese-danish dictonary application it would be relevant to have different setting for different colums.

I don't believe it a crazy example. International companies could have names of various items (that they sell for instance) in various languages.

But I have no idea of how to implement in SJA and the TUNNELing file.

[Ritesh]

Quote:

Can't CHARSET set be specific for each DB, table and even column ?

And what about COLLATION with MySQL 4.1 ++

These features are planned for v4.3.

@sdozono: HTTP being stateless in nature, SQLyogTunnel.php makes a new connection using mysql_connect() everytime. It then executes the query and disconnects. I guess in tunneling, with every connection we have to execute

Code:

SET names=xxx

. This is not required in direct connection as its persistent 🙂

I have added it in the TO-DO list of v4.2. Should be an easy hack 🙂

@sdozono: Did you get my PM regarding BLOGS?

[sdozono]

To peterlaursen:

peterlaursen wrote on Aug 22 2005, 10:00 AM:

But I have no idea of how to implement in SJA and the TUNNELing file.

[post=”6959″]<{POST_SNAPBACK}>[/post]

phpMyAdmin can set character encodings per column basis.

Though, in my opinion, it is not user-friendly. (If you have 10 fields, and want to change encodings, you need to go to the pages of each columns, and use drop-down select, and go to next pages….. ten times!)

If SQLyog can set encodings of each columns in one page at once, it will be great!

(But, it's ok with me if SQLyog doesn't have this feature. Mainly, I'm using this SQLyog for synchronization! 😆 )

(I think the primary purpose of setting the encodings is to make sorting functions work properly.

So, if you don't need to sort Japanese data, you can put the data in 'latin1' table.)

[peterlaursen]

Quote:

I think the primary purpose of setting the encodings is to make sorting functions work properly

the primary purpose of COLLATIONS is to make sorting functions and such things work properly, I believe. The primary purpose of CHARSET is to assoicalte a graphic image a 'letter' or a 'sign' to a binary value. That is as of 4.1 ++. before 4.1 MySQL to keep both functionalities in CHARSET. But of course there is a cooperation os the OS-settings and the MySQL server on how thing s should be presented graphically ?

For instance German telephone books uses a very special sorting. MySQL created a german collation only for having the ability to sort the same way.

to my best knowing 🙂

BTW: If you put your japanese strings in a 'latin1' table and I read it from a Danish windows' version, how do they look then on my screen ?

[peterlaursen]

and BTW charset settings have a “top-down” approach.

Th server has a default. If not another default is specified for a DB, the server's default applies. If not another default is specified for a a table the default of the DB applies etc …

So I think PhPAdmin could have done it much better

[peterlaursen]

here

http://dev.mysql.com/doc/mysql/en/charset-general.html

charset's and collations are explained.

[Author]

Posts