Fli
07-02-2013, 12:40 PM
Hi, i had old mysql backup of the PHPBB forum and when i look into that, it had latin 1 characters instead latin2
i mean original forum had characters like ě č ř , but in mysql dump file it was like these: čĹŻĂ*Ĺ (http://internetlifeforum.com/general-server-webmaster-discussion/473-how-convert-file-one-character-set-another-via-command-line/)
i had no ability to do backup again, so what i did?
i have VPS server so i have access to command line via SSH. I created list of commands to search and replace these bad characters in my sql dump file. You can find these commands there: http://pastebin.com/73qH1xsB (or as an attachment of this post ) please note that i used them one by one, not run all at once because it started somehow randomly executing it.
After done, i executed search replace command sed 's/latin1/latin2/g' mysqlfilename.sql (backup your file first) so this command replaced: latin1 to latin2 (so importing will work)
ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin2_czech_cs AUTO_INCREMENT=9 ;
we need latin2 so the import is done properly with new latin2 characters we replaced
so after sql file is changed i imported it into mysql by command:
mysql -u root -pmymysqlpassword mymysqldatabasename < mymysqldumpfileijustedited.sql --default-character-set=latin2
and it worked. After import done, i got errors like:
No Language Found! (ensure /language folder has proper language flder in it)
Unknown column 't.template_inherits_id' in 'field list' [1054] (in this case run yourforumname.com/install/database_update.php . if you dont have isntall folder, upload it from default phpbb installation)
============
here is also file convertors which can achieve similar result. You can try it: http://www.convertutf8.com/utf8_char_encoding_converter_e.htm
If you know script behind that which can achieve it on linux, please share.
i mean original forum had characters like ě č ř , but in mysql dump file it was like these: čĹŻĂ*Ĺ (http://internetlifeforum.com/general-server-webmaster-discussion/473-how-convert-file-one-character-set-another-via-command-line/)
i had no ability to do backup again, so what i did?
i have VPS server so i have access to command line via SSH. I created list of commands to search and replace these bad characters in my sql dump file. You can find these commands there: http://pastebin.com/73qH1xsB (or as an attachment of this post ) please note that i used them one by one, not run all at once because it started somehow randomly executing it.
After done, i executed search replace command sed 's/latin1/latin2/g' mysqlfilename.sql (backup your file first) so this command replaced: latin1 to latin2 (so importing will work)
ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin2_czech_cs AUTO_INCREMENT=9 ;
we need latin2 so the import is done properly with new latin2 characters we replaced
so after sql file is changed i imported it into mysql by command:
mysql -u root -pmymysqlpassword mymysqldatabasename < mymysqldumpfileijustedited.sql --default-character-set=latin2
and it worked. After import done, i got errors like:
No Language Found! (ensure /language folder has proper language flder in it)
Unknown column 't.template_inherits_id' in 'field list' [1054] (in this case run yourforumname.com/install/database_update.php . if you dont have isntall folder, upload it from default phpbb installation)
============
here is also file convertors which can achieve similar result. You can try it: http://www.convertutf8.com/utf8_char_encoding_converter_e.htm
If you know script behind that which can achieve it on linux, please share.