+ Post New Thread
Results 1 to 2 of 2

Thread: How i changed bad character set encoding in mysql database and restored it

  1. #1
    Fli
    Fli is online now
    Administrator Fli's Avatar
    Join Date
    Mar 2013
    Posts
    2,471

    How i changed bad character set encoding in mysql database and restored it



    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: čĹŻĂ*Ĺ

    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:

    Code:
    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...onverter_e.htm
    If you know script behind that which can achieve it on linux, please share.
    Attached Files Attached Files

  2. #2
    Junior Member WebCare360's Avatar
    Join Date
    Jul 2013
    Location
    WebCare360
    Posts
    8


    Is this useful / helpfull? Yes | No
    +1 .. Nice

+ Post New Thread

Similar Threads

  1. How to Back Up and Restore a MySQL Database?
    By MelissaCaddy1 in forum General Hosting Discussion
    Replies: 4
    Last Post: 08-14-2017, 08:10 AM
  2. Replies: 0
    Last Post: 04-15-2017, 09:16 AM
  3. Is inconsistent mysql database file restorable / how?
    By Fli in forum MySQL, Apache, PHP
    Replies: 0
    Last Post: 03-29-2015, 03:26 PM
  4. Unable to import mySQL database for CPanel webhosting?
    By webc in forum PHP, MySQL Forum
    Replies: 1
    Last Post: 03-23-2014, 04:18 PM
  5. Replies: 1
    Last Post: 08-08-2013, 01:16 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
 Protected by : ZB BLOCK  &  StopForumSpam