Textpattern CMS support forum
You are not logged in. Register | Login | Help
- Topics: Active | Unanswered
Converting a latin1 database to utf8 (strange characters)
My host upgraded me to a new server today, after I backed up my database about 6 times (twice with rss_db). My old server was running MySQL 3.23.58, but my new one uses 4.1.19. I noticed on most of my pages, I have weird symbols, like ’ in place of an apostrophe. I found this from the Textbook, but I don’t get it. What do I insert, and where? I’m more than willing to drop the database and reimport, if I can (still have the sql files).
My old server used Plesk WHM, instead of cPanel, so I was able to access phpmyadmin with a specific user. This cPanel install doesn’t allow it though, so I can’t change the database encoding to latin1 with the root user.
Diagnostics:
<pre>
Textpattern version: 4.0.3 (r1188)
last_update: 2006-01-22 10:22:30/2006-07-07 21:03:41
Document root: /home/jmdeldin/public_html
$path_to_site: /home/jmdeldin/public_html
Textpattern path: /home/jmdeldin/public_html/textpattern
Permanent link mode: section_title
Temp folder: /home/jmdeldin/public_html/textpattern/tmp
Site URL: www.jmdeldin.com
PHP version: 4.4.2
server_time: 2006-07-07 20:45:06
MySQL: 4.1.19-standard
Locale: en_US.UTF-8
Server: Apache/1.3.36 (Unix) mod_auth_passthrough/1.8 mod_log_bytes/1.2 mod_bwlimited/1.4 FrontPage/5.0.2.2635.SR1.2 mod_ssl/2.8.27 OpenSSL/0.9.7a PHP-CGI/0.1b
php_sapi_mode: cgi
os_version: Linux 2.6.8-022stab067.1-enterprise
————————————
Charset (default/config): latin1/utf8
character_set_client: utf8
character_set_connection: utf8
character_set_database: latin1
character_set_results: utf8
character_set_server: latin1
character_set_system: utf8
character_sets_dir: /usr/share/mysql/charsets/
18 Tables: textpattern is latin1, txp_category is latin1, txp_css is latin1, txp_discuss is latin1, txp_discuss_ipban is latin1, txp_discuss_nonce is latin1, txp_file is latin1, txp_form is latin1, txp_image is latin1, txp_lang is latin1, txp_link is latin1, txp_log is latin1, txp_page is latin1, txp_plugin is latin1, txp_prefs is latin1, txp_priv is latin1, txp_section is latin1, txp_users is latin1
PHP extensions: zip, xmlrpc/0.51, xml, tokenizer/0.1, swf, standard/4.4.2, sockets, session, pspell, posix, overload, mysql, mhash, mcrypt, mbstring, imap, gettext, gd, ftp, exif/1.4 $Id: exif.c,v 1.118.2.37.2.4 2006/01/01 13:46:52 sniper Exp $, domxml/20020815, curl, ctype, calendar, bcmath, zlib/1.1, pcre, openssl, Zend Optimizer
/include/txp_category.php: r1154 (dffd7979531373149c74e3eedfbb50e2)
/include/txp_plugin.php: r1167 (c2c7d7fc1e4918c79cd3658c62e4fbda)
/include/txp_auth.php: r1166 (3e2eaca7b151e14561125ab68e722203)
/include/txp_form.php: r947 (c0e4393328985742bd81cda61d10d5ed)
/include/txp_section.php: r1154 (d85c1ce405fccd9dac364c8ef4eb72eb)
/include/txp_tag.php: r1134 (3e2dfbd6fdccf0693c147ba22a9705e7)
/include/txp_list.php: r1042 (e85d2de1c63b41f0e5bdcf268e3c487b)
/include/txp_page.php: r876 (e22dc37b465b87757722732a225b25f3)
/include/txp_discuss.php: r1170 (1e56e08cc95c255783410076db12529f)
/include/txp_prefs.php: r1170 (c806be1b1f238e75a5ff6b7cf8432d17)
/include/txp_log.php: r906 (68c4f32b70ce41a349f197b6bba6c200)
/include/txp_preview.php: r628 (a16f84b6610fc66db0bce51e25b7af34)
/include/txp_image.php: r1168 (e8ec79ff9b3a21c356ee23da7973b1f0)
/include/txp_article.php: r1170 (88689d7469d16c13d51bd4adff03ebd7)
/include/txp_css.php: r922 (0c596d9968052b89581d1ca65a77c5de)
/include/txp_admin.php: r1074 (7e87ff0e29101593fb170b1a2de9fd59)
/include/txp_link.php: r1154 (7c0c55ff6580793dff0626f169e64ad6)
/include/txp_diag.php: r1154 (5d66b90f7e7720dd0e3526ca1b24cf89)
/lib/admin_config.php: r877 (9aea12193cc76eea1964eb0383ec6513)
/lib/txplib_misc.php: r1170 (7000a1b27aa40c90978211e6cff358ec)
/lib/taglib.php: r628 (493878f922262157601c26a999a1c478)
/lib/txplib_head.php: r1055 (5b61e8e1ae7a54290c3e1d501c9ebe69)
/lib/classTextile.php: r1141 (b066c2bfa94a26ae3d70b245dcf81369)
/lib/txplib_html.php: r1166 (bd11de6e6daa3391eab75a46db55ae36)
/lib/txplib_db.php: r1170 (5a2c3a1e328cd343a067cbb8fd25cd79)
/lib/IXRClass.php: r765 (2efb0fb05b55c634072b0bae9af38bdd)
/lib/txplib_forms.php: r950 (74da2c67abe4786c353ea416c13656eb)
/publish/taghandlers.php: r1188 (8cf2006b24cafdf07349e72771f975d6)
/publish/atom.php: r1117 (14c1aa23c708898d7723b3d6b9c89050)
/publish/log.php: r864 (c3d16d3dfe375819a19fcd62398e4b86)
/publish/comment.php: r1187 (bd55a690d625c961bf71a36e3e04386b)
/publish/search.php: r884 (80f942f1dc301857c2fa9dcb16aa1246)
/publish/rss.php: r1099 (ce0d82b9435d8f1de01c8e2b6c585f93)
/publish.php: r1186 (d5c20bf2298e87969575f9e622bf2797)
/index.php: r1172 (52f753ebb51c1f7d5312485d9dbad7e4)
/css.php: r944 (df1c754417bdfb4e97a2c83ae5b454ee)
</pre>
Also, the pages are being sent as UTF-8 with a php header().
Last edited by deldindesign (2006-07-08 02:16:22)
Offline
Re: Converting a latin1 database to utf8 (strange characters)
hi deldindesign.
have you converted your DB to utf-8?
also, remember to change the charset in config.php
let me search for some links:
http://forum.textpattern.com/viewtopic.php?pid=102085#p102085
Offline
Re: Converting a latin1 database to utf8 (strange characters)
Thanks,
I should have clarified—I performed a “clean install” of textpattern, including a new config.php ($txpcfg['dbcharset'] = 'utf8';). I then restored the content using rss_db manager. The old config.php (still have it) used latin1 on the $txpcfg[‘dbcharset’].
Last edited by deldindesign (2006-07-08 06:21:10)
Offline
Re: Converting a latin1 database to utf8 (strange characters)
Well just to make sure, there is no misunderstanding: You don’t have to do any of this. If you hadn’t done anything, everything would have just continued to work, without any funny characters or anything.
I should have clarified—I performed a “clean install” of textpattern, including a new config.php ($txpcfg[‘dbcharset’] = ‘utf8’;). I then restored the content using rss_db manager.
Two solutions:
Alternative 1) Delete all tables (you have a ful backup anyhow, right?), remove the line about utf8 from the config-file (or change it to latin1 – doesn’t matter), and then import your backup. The tables will be latin1, but that’s not a problem – things will continue to work just as always. If you have never saved anything to the db, you can skip the delete and re-import step.
Alternative 2) You want to have your db-tables in utf8, in that case you already had the correct page in the manual – it’s just that they expalin additional stuff that is relevant for mysql-administrators, not mysql-users . You “just” need to convert the data. Given that we had been storing unicode data already in those tables, you want to follow the instructions here:
http://dev.mysql.com/doc/refman/4.1/en/charset-conversion.html
But again: If you have saved any data in the meantime, chances are those changes will still appear broken. You can start from a clean backup (as above), or you can try to manually fix those (probably re-editing and resaving should fix them).
Last edited by Sencer (2006-07-08 10:49:41)
Offline
Re: Converting a latin1 database to utf8 (strange characters)
Sencer, you rock! I went with #1 and had my site running weird-character free immediately. For anyone else moving to newer databases:
- Install rss_admin_db to backup your site. My phpmyadmin backups were corrupted somehow, but the rss_admin ones were fine.
- Back your site up multiple times (databases twice from rss_admin, and twice from phpmyadmin (just to be safe).
- On your new server/host, create a new database
- in phpmyadmin, choose “Import”. Import the file as latin1.
- Upload your old, or new TXP install, but make sure the line in config.php says
$txpcfg['dbcharset'] = 'latin1';
Offline