1 Topic by JLavau

  • Registered: 2009-06-27
  • Posts: 3

Topic: [Fixed] Need to convert a Latin1 database to UTF8. How to do ?

Good morning.

Club-internet has been bought by SFR, and we must transfer and convert all our personal sites.
Done for the html, but a big problem arises with the database of the FluxBB forum :
The original at http://lavaujac.club.fr/disputatio/ is encoded in Latin1.
The target at http://jacques.lavau.perso.sfr.fr/disputatio/ should be in UTF8.

The MyAdmin at the target declines the import, because of the size of the file.
BigDump does not work, too.
MySQLDumper works at last, but fails in taking the accentuated characters. The result is that any posts and topics and forums are cut at the first accentuated character.
Unusable so.

I tried a conversion through Wordpad. The file has doubled in size, but MySQLDumper fails in restore operation, does not understand a request in Unicode...

Do you know a converter that works for our SQL databases ?

Thank you for replies !

Last edited by JLavau (2009-06-28 12:48:27)

2 Reply by Smartys

  • Registered: 2008-04-27
  • Posts: 2,614

Re: [Fixed] Need to convert a Latin1 database to UTF8. How to do ?

It's a little more complicated than that. wink

The charset for pages on both sites is ISO-8859-1 (latin1). That means neither of the sites are using 100% UTF-8. My guess is that the original is using a plain PunBB/FluxBB setup (complete iso-8859-1) and the target is using partial UTF-8 (at the very least, the database tables are set to UTF-8: the database connection should also be UTF-8, but I can't be sure if that's so). Without knowing more information about the setup of the databases and the form of the database dump (ie: mysqldump in later versions always dumps in UTF-8, which sometimes messes up these conversions), it's impossible to suggest how to fix the issue. My suggestion would be to migrate the target forum to full UTF-8 first and then worry about merging in the original.

Smartys's Website

3 Reply by zaher

  • From: Damascus, Syria
  • Registered: 2008-07-12
  • Posts: 36

Re: [Fixed] Need to convert a Latin1 database to UTF8. How to do ?

Idea

Dump your data as SQL text, then use Notepad++ to remove the 

character set latin1 collate latin1_bin

DEFAULT CHARSET=latin1

just search for latin1 in your sql file.

import your data to new database created with UTF-8 character set

zaher's Website

4 Reply by JLavau

  • Registered: 2009-06-27
  • Posts: 3

Re: [Fixed] Need to convert a Latin1 database to UTF8. How to do ?

Thank you.
All the tables but search_words are fixed now.
Categories, forums, users, et ranks by hand pasting.
Topics by separate import, specifying "Latin1" for the format of the imported file.
Zipped posts, so on.

Only search_words firmly resists : duplicate key !

I did not know this feature of Notepad++, but here for search_words, it does not help.

5 Reply by JLavau

  • Registered: 2009-06-27
  • Posts: 3

Re: [Fixed] Need to convert a Latin1 database to UTF8. How to do ?

Done.
Just to export search_words in binary, not hexadecimal, and zipped.
Import as Latin1.

All done, now.

6 Reply by zaher

  • From: Damascus, Syria
  • Registered: 2008-07-12
  • Posts: 36

Re: [Fixed] Need to convert a Latin1 database to UTF8. How to do ?

Do not export data for search_* 3 tables you can regenerate it in admin panel

zaher's Website