Bug #19230 mysql restore backup, after turkish characters showing question marks.
Submitted: 20 Apr 2006 20:38 Modified: 3 Jul 2006 15:40
Reporter: orhan sevki Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server: Command-line Clients Severity:S2 (Serious)
Version:4.1.18 OS:Windows (windows xp & centos & debian)
Assigned to: CPU Architecture:Any

[20 Apr 2006 20:38] orhan sevki
Description:
Hello,

today, i successfully backup my database, but when i restore this backup another server, command line and site page turkish characters showing question marks. like: '?'

I opened via notepad backup sql file, characters showing fine, but after restore to new server characters showing: '?'.

Also, new entries same database no problem after importing to site, but current data completely lost!. and command line select * from tablename; Still showing characters incorrect.

My database and tables using: latin5 charset and collations: latin5_turkish_ci. I have MyISAM tables.

I created new server database latin5 charset and latin5_turkish_ci collation example:

CREATE DATABASE dbname CHARSET=latin5 COLLATE=latin5_turkish_ci;

I tired both backup and restore command, but problem still continued;

mysqldump -u dbuser -p dbname - > /path/backup.sql

mysqldump --default-character-set=latin5 -u dbuser -p dbname - > /path/backup.sql

And Restore;

mysql -u dbuser -p dbname < /path/backup.sql

mysql --default-character-set=latin5 -u dbuser -p dbname < /path/backup.sql

Still characters showing site and console: '?' question marks.

I also put this line my.ini [mysqld]:

init-connect="SET NAMES latin5"

After restart mysql and viewing site, current data, characters showing fine. But command line console still showing question marks. '?'.

My current server is shared hosting, no have init-connect value in my.cnf file and characters showing fine.

Is this bug?

Here is the my database and tables example:

--
-- Table structure for table `access`
--

DROP TABLE IF EXISTS `access`;
CREATE TABLE `access` (
  `userid` int(10) unsigned NOT NULL default '0',
  `forumid` smallint(5) unsigned NOT NULL default '0',
  `accessmask` smallint(5) unsigned NOT NULL default '0',
  PRIMARY KEY  (`userid`,`forumid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin5;

Here is the mysql config file:

[client]
default-character-set="latin5"
port=3306

[mysql]
default-character-set="latin5"

[mysqld]
default-character-set="latin5"
# The TCP/IP Port the MySQL Server will listen on
port=3306

basedir="C:/MySQL/"

#Path to the database root
datadir="C:/MySQL/Data/"

init-connect="SET NAMES latin5"
# The default storage engine that will be used when create new tables when
default-storage-engine=MYISAM

max_connections=100

query_cache_size=0

table_cache=256

tmp_table_size=5M

thread_cache_size=8

myisam_max_sort_file_size=100G

myisam_sort_buffer_size=8M

key_buffer_size=8M

read_buffer_size=64K
read_rnd_buffer_size=256K

sort_buffer_size=212K

skip-innodb

innodb_additional_mem_pool_size=2M

innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=1M
innodb_buffer_pool_size=8M
innodb_log_file_size=10M
innodb_thread_concurrency=8

I'm waiting confirmation from you.

Thanks.

How to repeat:
create new database with turkish character support and insert any table & turkish word etc. View the site characters showing fine, but not console (command line).

After backup this database via mysqldump.

once backup, create new empty database and restore them. Turkish characters showing site and console like: '?' question marks.
[21 Apr 2006 0:55] orhan sevki
Today i downloaded latest version mysql 4.1.18, still same problem.

Also, when i restore my backup to 4.1.18 server, i'm getting dublicate key error.

current backup is restored without problem 4.1.17 server, but not 4.1.18.
[21 Apr 2006 23:21] orhan sevki
Hello again, I'm post to you our mysql '%char%' and '%colla%' variables, both system variables are same;

| character_set_client            | latin5                           |
| character_set_connection        | latin5                           |
| character_set_database          | latin5                           |
| character_set_results           | latin5                           |
| character_set_server            | latin5                           |
| character_set_system            | utf8                             |
| character_sets_dir              | /usr/local/share/mysql/charsets/ |
| collation_connection            | latin5_turkish_ci                |
| collation_database              | latin5_turkish_ci                |
| collation_server                | latin5_turkish_ci

we trying to restore backup to centos linux system and still same problem.
[22 Apr 2006 0:44] orhan sevki
finally, issue solved with PHP5 mysqli extension.

I think php4 & mysql4.1.x not compatible with character sets and collations.
[22 Apr 2006 14:07] Valeriy Kravchuk
So, as problem is solved, can we close this report? As for php4 vs. php5 + mysqli, it is clearly documented (http://php.net/manual/en/ref.mysqli.php):

"The mysqli extension allows you to access the functionality provided by MySQL 4.1 and above. More information about the MySQL Database server can be found at http://www.mysql.com/". 

The problem can be also with mysqlclient library your php4 was linked with.
[24 Apr 2006 23:20] orhan sevki
Dear Sir,

do not close please, because problem still persist with php4 and mysql4.1.18

Also, all turkish peoples having same problem, i can help you fixing this issue, please investigate.
[3 Jun 2006 15:40] Valeriy Kravchuk
OK, so, it is not PHP-related problem. Please, send the

echo $LANG

command results from the same shell window you run mysqldump and mysql command line clients.

Please, provide the smallest possible but complete test case that shows incorrect behaviour you described. With CREATE DATABASE, CREATE TABLE, INSERT for some data, exact mysqldump and mysql commands used to dump and restore.
[3 Jul 2006 23:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".