Character Encoding Issue?

[Ian Cunningham]

I've just upgraded my server to PHP 5.3 and MySQL 5.6, however I'm getting some odd issues with character encoding. On PHP 5.2 and MySQL 5.5 the site worked fine, but now I get odd encoding issues. For example, every £ symbol on the site now reads as £ - even in the source code.

I also can't use some standard characters in the admincp prune section, as I get errors like this:

	Invalid SQL:


	  SELECT COUNT(*) AS count
	  FROM thread AS thread
	  LEFT JOIN forum AS forum ON (forum.forumid = thread.forumid)
	  WHERE 1=1 AND thread.sticky = 0 AND thread.open <> 10 AND thread.title LIKE '%£%' AND (thread.forumid = 2 OR forum.parentlist LIKE '%,2,%');


	MySQL Error   : Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation 'like'
	Error Number  : 1267
	MySQL Version : 5.6.10-log

Any ideas on what this could be?

[Trevor Hannant]

When vBulletin creates a new table in the upgrade process, it asks MySQL what the default collation for the database is and then uses that. Most MySQL installations default to latin1_swedish_ci. If you have changed collations for any reason on tables in the past then you should change the database to use that as the default to prevent this issue from happening in the future.

You will find more information in the MySQL manual here:

http://dev.mysql.com/doc/refman/5.1/en/charset.html

You will need to use phpMyAdmin and make sure that all tables and fields within them have the same collation. Make sure your database collations are appropriate and consistent throughout the entire database. You need to use program like phpmyadmin to view your collations:

http://www.phpmyadmin.net/home_page/index.php

When you click your database name in phpmyadmin it will list all tables, their collations, as well as the collation of the database itself at the bottom. And when you click the name of a table on the left it will list the collations of individual fields within that table.

1) To edit the collation of the database itself you need to click the database name on the left and then click "Operations" on the top.

2) To edit the collation of an individual table you need to click its name on the left and then click "Operations" on the top.

3) To edit the collation of an individual field within a table you need to click the table name on the left and then click the edit image (a little pencil icon) for that field.

For more information please see: http://www.vbulletin.com/forum/showt...ight=collation

Note: It is very very very important to make full database backups before proceeding if you are ever manually making any changes to your database, let it be issuing queries, or editing it via phpMyAdmin or any other interface. If done incorrectly, manually modifying the database can potentially cause irreversible damage to your database, and there is no way to perform a "partial restore" to restore just the part you may break.

[Ian Cunningham]

Forgot to mention, this is with VB 3.8.7 PL3.

I just tried PHP 5.2 with the same MySQL 5.6 DB and that seems to work fine - the PHP.ini files look almost identical. Does that give any clues?

[BirdOPrey5]

In your Admin CP, Language, Settings for your Language, what exactly is your HTML Character Set?

[Ian Cunningham]

It's just set as "English (US)" with "ISO-8859-1"

That's worked fine for years - it's only since upgrading to PHP 5.3 that the problem has arisen.

[Ian Cunningham]

When vBulletin creates a new table in the upgrade process, it asks MySQL what the default collation for the database is and then uses that. Most MySQL installations default to latin1_swedish_ci. If you have changed collations for any reason on tables in the past then you should change the database to use that as the default to prevent this issue from happening in the future. You will find more information in the MySQL manual here: http://dev.mysql.com/doc/refman/5.1/en/charset.html You will need to use phpMyAdmin and make sure that all tables and fields within them have the same collation. Make sure your database collations are appropriate and consistent throughout the entire database. You need to use program like phpmyadmin to view your collations: http://www.phpmyadmin.net/home_page/index.php When you click your database name in phpmyadmin it will list all tables, their collations, as well as the collation of the database itself at the bottom. And when you click the name of a table on the left it will list the collations of individual fields within that table. 1) To edit the collation of the database itself you need to click the database name on the left and then click "Operations" on the top. 2) To edit the collation of an individual table you need to click its name on the left and then click "Operations" on the top. 3) To edit the collation of an individual field within a table you need to click the table name on the left and then click the edit image (a little pencil icon) for that field. For more information please see: http://www.vbulletin.com/forum/showt...ight=collation Note: It is very very very important to make full database backups before proceeding if you are ever manually making any changes to your database, let it be issuing queries, or editing it via phpMyAdmin or any other interface. If done incorrectly, manually modifying the database can potentially cause irreversible damage to your database, and there is no way to perform a "partial restore" to restore just the part you may break.

Sorry I must have missed this when replying before.

In my.ini I've got this line:

[mysql]


default-character-set=utf8


However I my tables are all latin1. Would that be the problem? It just seems odd that it works fine with PHP 5.2.

[Ian Cunningham]

I've just tried changing the MySQL character set for that DB to "latin1" but it doesn't seem to make a difference.

I can manually run those queries listed above in SQLYOG (a MySQL interface), so it does seem to be a PHP problem (or at least that's where I can fix it).