vBulletin character set & collation, double check phpmyadmin / MySQL for charset settings
2).
Another way to check is direct via SSH telnet via MySQL client command line
type
| character_set_client | latin1 | | character_set_connection | latin1 | | character_set_database | latin1 | | character_set_filesystem | binary | | character_set_results | latin1 | | character_set_server | latin1 | | character_set_system | utf8 | | character_sets_dir | /usr/share/mysql/charsets/ | | collation_connection | latin1_swedish_ci | | collation_database | latin1_swedish_ci | | collation_server | latin1_swedish_ci |
You’ll be prompted for MySQL root password. The resulting output showing latin1 defaults.
[root@centos6 ~]# mysqladmin -u root -p var | grep -E '(character|collation)' | character_set_client | latin1 | | character_set_connection | latin1 | | character_set_database | latin1 | | character_set_filesystem | binary | | character_set_results | latin1 | | character_set_server | latin1 | | character_set_system | utf8 | | character_sets_dir | /usr/share/mysql/charsets/ | | collation_connection | latin1_swedish_ci | | collation_database | latin1_swedish_ci | | collation_server | latin1_swedish_ci |
3).
Third location to find what your MySQL server default character set and collation are is via vB AdminCP control panel, go to Import & Maintenance -> Diagnostic and select MySQL Variables output, you’ll get the similar output list, and look for the relevant variables for their values.
4).
Fourth method is via a bash shell script I wrote, checkcharset.sh just to figure out the current state of MySQL server and vBulletin databases character set, collation and encoding values.
You’ll need to edit a few variables in checkcharset.sh script:
# answer y or n # is MySQL version > 5.1.xx ? MYSQLVER='y' # your mysql server hostname set # in vB config.php # $config['MasterServer']['servername'] = MYSQLHOST='localhost' # mysql username/password MYSQLUSER='yourmysqlusername' MYSQLPASS='yourmysqlpassword' # your database name # vB config.php # $config['Database']['dbname'] = DBNAME='yourvb_databasename' # if you set table prefix in vB config.php # $config['Database']['tableprefix'] = DBPREFIX='' # vB config.php full path # to find what you set # in vB admincp > Language Manage -> Edit settings -> HTML Character Set VBCONFIGPATH='/full/path/to/your/vbforum/includes/config.php' # How to set POSTID # find a post preferably with short text i.e. 1 or 2 lines of text # which has incorrectly displayed characters or create a new post with # 1 or 2 lines of mixed english and native charset of your forum text # note the posts' postid i.e. postid = 1 POSTID='1' |
to run it
chmod +x checkcharset_v0.03.sh ./checkcharset_v0.03.sh > charsetinfo.txt |
Output from charsetinfo.txt file. I snipped some of the vB database table listing for easier display here.
Note, the script doesn’t report the individual column, fields’ collation values, so even if you set everything as UTF8 and this script reports UTF8 defaults from MySQL server, database level, double check your column and fields have correction collations. In case of UTF8 character set, needs utf8_general_ci collation. The script is just a quick check to ensure you have majority of settings correct for UTF8 or whatever character set and collation you set for your vBulletin forums.
---------------------------------------------------------------------------------------------------- Check vB config.php settings: ---------------------------------------------------------------------------------------------------- $config['Database']['dbtype'] = 'mysql'; // $config['Mysqli']['charset'] = 'utf8'; ---------------------------------------------------------------------------------------------------- Check MySQL server default character set and collation ---------------------------------------------------------------------------------------------------- | character_set_client | latin1 | | character_set_connection | latin1 | | character_set_database | latin1 | | character_set_filesystem | binary | | character_set_results | latin1 | | character_set_server | latin1 | | character_set_system | utf8 | | character_sets_dir | /usr/share/mysql/charsets/ | | collation_connection | latin1_swedish_ci | | collation_database | latin1_swedish_ci | | collation_server | latin1_swedish_ci | ---------------------------------------------------------------------------------------------------- Check vB database default character set and collation ---------------------------------------------------------------------------------------------------- +--------------+------------+ | languagecode | charset | +--------------+------------+ | en | ISO-8859-1 | +--------------+------------+ *************************** 1. row *************************** Name: language Engine: MyISAM Version: 10 Row_format: Dynamic Rows: 1 Avg_row_length: 678000 Data_length: 686268 Max_data_length: 281474976710655 Index_length: 2048 Data_free: 8268 Auto_increment: 2 Create_time: 2011-10-05 19:27:53 Update_time: 2011-10-05 19:34:02 Check_time: NULL Collation: latin1_swedish_ci Checksum: NULL Create_options: Comment: ---------------------------------------------------------------------------------------------------- Check vB database table default character set and collation ---------------------------------------------------------------------------------------------------- +--------------------------+-------------------+--------+ | table_name | table_collation | engine | +--------------------------+-------------------+--------+ | access | latin1_swedish_ci | MyISAM | | action | latin1_swedish_ci | MyISAM | | ad | latin1_swedish_ci | MyISAM | | adcriteria | latin1_swedish_ci | MyISAM | | adminhelp | latin1_swedish_ci | MyISAM | | administrator | latin1_swedish_ci | MyISAM | | adminlog | latin1_swedish_ci | MyISAM | | adminmessage | latin1_swedish_ci | MyISAM | | adminutil | latin1_swedish_ci | MyISAM | | album | latin1_swedish_ci | MyISAM | | albumupdate | latin1_swedish_ci | MyISAM | | announcement | latin1_swedish_ci | MyISAM | | announcementread | latin1_swedish_ci | MyISAM | | userpromotion | latin1_swedish_ci | MyISAM | | usertextfield | latin1_swedish_ci | MyISAM | | usertitle | latin1_swedish_ci | MyISAM | | visitormessage | latin1_swedish_ci | MyISAM | | visitormessage_hash | latin1_swedish_ci | MyISAM | +--------------------------+-------------------+--------+ ---------------------------------------------------------------------------------------------------- Checking to see what the actual data in table returns ---------------------------------------------------------------------------------------------------- *************************** 1. row *************************** postid: 1 threadid: 1 parentid: 0 username: eva2000 userid: 1 title: Test web server dateline: 1286366159 pagetext: Test web server allowsmilie: 1 showsignature: 0 ipaddress: 220.253.xxx.xxx iconid: 0 visible: 1 attach: 0 infraction: 0 reportthreadid: 0 htmlstate: on_nl2br ---------------------------------------------------------------------------------------------------- |
