DaemonForums  

Go Back   DaemonForums > Miscellaneous > Programming

Programming C, bash, Python, Perl, PHP, Java, you name it.

Reply
 
Thread Tools Display Modes
  #1   (View Single Post)  
Old 12th June 2008
stukov's Avatar
stukov stukov is offline
Real Name: Jean-Michel Philippon-Nadeau
Package Pilot
 
Join Date: May 2008
Location: Sherbrooke, Qc, Canada
Posts: 167
Thanked 6 Times in 6 Posts
Default Changing encoding of text in MySQL database

Hola,

I have a database full of text. I had to change the encoding of the tables and the database themselves. Now all my French accents are screwed up. Does anyone know/have a script that could de-corrupt my database and it's poor accents?

Thanks!
__________________
"Any intelligent fool can make things bigger, more complex, and more violent. It takes a touch of genius -- and a lot of courage -- to move in the opposite direction."

Last edited by stukov; 15th June 2008 at 06:22 PM.
Reply With Quote
  #2   (View Single Post)  
Old 19th June 2008
ohauer ohauer is offline
Port Guard
 
Join Date: May 2008
Location: germany
Posts: 32
Thanked 2 Times in 2 Posts
Default

Hopefully you have a good dump before changes are applied.

You can try to convert the dump with iconv.

Dump the tables without data
$> mysqldump -d --database SourceDB -u YourMySQLAdmin -p > SourceDB_tables.dump

Dump the data only
$> mysqldump -n -t --database SourceDB -u YourMySQLAdmin -p > SourceDB_data.dump

Replace the encoding in the SourceDB_tables.dump file with new encoding and create a new database to test the next steps with this file.

To see all encodings that can be used with iconv
$> iconv -l

Now convert the data to the new encoding.
$> cat SourceDB_data.dump | iconv -f US-ASCII -t UTF-8 > SourceDB_data.utf8

Now import the converted data to the new database, if all went OK you are fine.

I cannot describe the exact steps for the ex/import since i don't use MySQL but i hope the way is clear now.
Reply With Quote
  #3   (View Single Post)  
Old 25th June 2008
stukov's Avatar
stukov stukov is offline
Real Name: Jean-Michel Philippon-Nadeau
Package Pilot
 
Join Date: May 2008
Location: Sherbrooke, Qc, Canada
Posts: 167
Thanked 6 Times in 6 Posts
Default

Thanks for the reply ohauer. Sorry if it took some time for me to answer.

I followed your steps and understood the explanations. Thanks, it was very clear. However, I get the following error message when running "cat db.sql | iconv -f US-ASCII -t UTF-8 > db-utf8.sql":
Quote:
iconv: (stdin):40:531: cannot convert
What are the possible causes of this error?

Thank you very much.
__________________
"Any intelligent fool can make things bigger, more complex, and more violent. It takes a touch of genius -- and a lot of courage -- to move in the opposite direction."
Reply With Quote
  #4   (View Single Post)  
Old 25th June 2008
ohauer ohauer is offline
Port Guard
 
Join Date: May 2008
Location: germany
Posts: 32
Thanked 2 Times in 2 Posts
Default

The error is about a sign that is not in US-ASCII table and iconv cannot translate it.
error is at line: 40, row 531

Take a look at this sign with your favor editor, maybe it is ISO-... or someting else.

Since you will transfer from a database witch has french chars, try the following if it works.
Code:
iconf -f ISO-8859-1 -t UTF-8 < source_dump.sql > target_dump.utf8
Maybe change ISO-8859-1 to something else
Reply With Quote
  #5   (View Single Post)  
Old 15th July 2008
stukov's Avatar
stukov stukov is offline
Real Name: Jean-Michel Philippon-Nadeau
Package Pilot
 
Join Date: May 2008
Location: Sherbrooke, Qc, Canada
Posts: 167
Thanked 6 Times in 6 Posts
Default

Thanks for the solution ohauer, however, it looks like some part of the database is encoded two to three times or with another encoding. I had to rewrite the accents one by one in the DB.
__________________
"Any intelligent fool can make things bigger, more complex, and more violent. It takes a touch of genius -- and a lot of courage -- to move in the opposite direction."
Reply With Quote
  #6   (View Single Post)  
Old 15th July 2008
lvlamb's Avatar
lvlamb lvlamb is offline
Real Name: Louis V. Lambrecht
Spam Deminer
 
Join Date: May 2008
Location: .be
Posts: 221
Thanked 25 Times in 24 Posts
Default

This is an application level encoding problem. In no way related to the underlaying OS.
Tables can be encoded in any code-page you may wish, either locally, or per users, or per table, or system wide.
Answers on http:://mysql.org
__________________
da more I know I know I know nuttin'
Reply With Quote
Reply

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Read Excel Data and store into Mysql Database using PHP cksraj Programming 2 3rd June 2009 12:09 PM
Encoding issue with GNU Screen TerryP General software and network 3 28th December 2008 07:04 AM
Roundcube Mail Can't Connect to MYSQL Database jrs665 OpenBSD Packages and Ports 1 17th November 2008 03:54 PM
PHP database interfaces TerryP Programming 6 11th September 2008 01:03 PM
Slocate database coverage ivanatora FreeBSD General 2 4th August 2008 08:51 AM


All times are GMT. The time now is 05:16 PM.


Powered by vBulletin® Version 3.8.4
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
Content copyright © 2007-2010, the authors
Daemon image copyright ©1988, Marshall Kirk McKusick