Dump & Characters encoding

[mathieumg]

I took the dump from last night (15 October) to re-install it in my database but there is a problem.

My bulletin board is in French so there are characters with accents like "é", "è", "à" and etc.. The problem is that in my dump file, those characters looks like "é", "è", "à " and etc.

How can I get my old characters back?

[RTH10260]

I took the dump from last night (15 October) to re-install it in my database but there is a problem.

My bulletin board is in French so there are characters with accents like "é", "è", "à" and etc.. The problem is that in my dump file, those characters looks like "é", "è", "à " and etc.

How can I get my old characters back?
Please clarify, are the characters wrong after restore or did you only look at the dump file ? The dump file will have some encoding to represent the correct characterset, and the table definitions used on restore should be redefining the original character set again.

[mathieumg]

They are both like that. In the dump file and after the restore :(

[extras]

Check the first part of dump file.
Does it have a line like this? /*!40101 SET NAMES utf8 */;

I restored this dump without any code problem.
(The data showed up with correct chars. 'xézèqàx')

Example:

-- MySQL dump 10.9
--
-- Host: mysql0X.powweb.com Database: zzzzzz
-- ------------------------------------------------------
-- Server version 4.1.9-log

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `t4`
--

DROP TABLE IF EXISTS `t4`;
CREATE TABLE `t4` (
`id` bigint(20) unsigned NOT NULL default '0',
`name` varchar(28) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table `t4`
--


/*!40000 ALTER TABLE `t4` DISABLE KEYS */;
LOCK TABLES `t4` WRITE;
INSERT INTO `t4` VALUES (1,'asd'),(2,'xézèqà x');
UNLOCK TABLES;
/*!40000 ALTER TABLE `t4` ENABLE KEYS */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

[mathieumg]

Yes it does.


-- MySQL dump 10.8
--
-- Host: localhost Database: xxxx
-- ------------------------------------------------------
-- Server version 4.1.9-log

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE="NO_AUTO_VALUE_ON_ZERO" */;


In my phpMyAdmin they still appear with those weird symbols :s

[extras]

Did you check with the real application?
phpmyadmin may be using different charset for the browser.

[mathieumg]

Yes in the application there are there too. :(

[mathieumg]

The Dump you pasted, have you ran it with your CGI script?

[extras]

It seems that mysqldump saves in different code compared to mysql -e "select * from tabel" >data.file

You can use one of these till I find a solution.

<?php
echo `mysql -v -v -v -uUSERNAME -pPASSWORD -hmysql0x.powweb.com DBNAME <SQL_FILE.sql 2>&1`;
?>



#!/bin/sh
echo
mysql -v -v -v -uUSERNAME -pPASSWORD -hmysql0x.powweb.com DBNAME <SQL_FILE.sql 2>&1

[mathieumg]

#!/bin/sh
echo
mysql -v -v -v -uUSERNAME -pPASSWORD -hmysql0x.powweb.com DBNAME <SQL_FILE.sql 2>&1


Where do I run that?

And my dump is 16mb, wont it timeout?

Also my file is a .dmp not a .sql, is it still ok?

[extras]

You can put it in any protected directory and name it whatever with .cgi extension.
And set permission to 700.
Then just access it http://example.com/protected/whateverthename.cgi

And the name of the file doesn't matter as it's dump (.sql) file.created by mysqldump.

[mathieumg]

Thanks! It finally works :)

[extras]

I think I found the solution.
I'd appreciate if you can check with the new version, again.
http://check-these.info/tools/restore_cgi.txt

Note

The problem:
mysqldump converts the data to utf8 (from latin1, in case of PowWeb) by default.
But LOAD DATA INFILE or LOAD DATA LOCAL INFILE ignore "NAMES = charset_x;".


The fix:
Reading lots of manual pages revealed nothing really good.
But google search showed MySQL bug ticket http://bugs.mysql.com/bug.php?id=10195
and I found 'Sinisa Milivojevic' saying "LOAD DATA utilises database charset."
So, I added "SET character_set_database = 'utf8';" in the dump file, just before LOAD DATA,
and fortunately it seems to work, so far.
(Even DB charset is utf8, tables have their own charset,
and the data will be converted back from utf8 to the charset of the table.)

[mathieumg]

Wow thanks :)

I already updated my data with these tables but I can try your new solution on another database when I wake up to see if it works.

Thanks A LOT for your precious help :D