MAMP 2.1.1 Character Set Issues

For those that use the latest MAMP 2.1.1 to develop your Joomla products, you may have noticed strange characters in your text output from the database. Like the strange diamond question mark U+FFFD �.  This is caused by inadvertently using different character sets when communicating between PHP and MySQL. I’m using MAMP 2.1.1, which now uses PHP 5.4.4 and MySQL 5.5.25, with Joomla 2.5.6.

Here is the solution I came up with.

Set PHP 5.4.4 php.ini to utf8.

/Applications/MAMP/bin/php/php5.4.4/conf/php.ini
default_charset = "UTF-8"

Let’s see what MySQL reports:

Bernard:~ Bernard$ /Applications/MAMP/Library/bin/mysqladmin variables|grep latin1
| character_set_client          | latin1
| character_set_connection      | latin1
| character_set_database        | latin1
| character_set_results         | latin1
| character_set_server          | latin1
| collation_connection          | latin1_swedish_ci
| collation_database            | latin1_swedish_ci
| collation_server              | latin1_swedish_ci
Bernard:~ Bernard$

Let’s force all connections to MySQL to be utf8.

Bernard:~ Bernard$ cat /Applications/MAMP/conf/my.cnf
[mysql]
 default-character-set=utf8
[client]
 default-character-set=utf8
[mysqld]
 skip-character-set-client-handshake
 character-set-server=utf8
 collation-server=utf8_general_ci
 init-connect='SET NAMES utf8'
 Bernard:~ Bernard$

Restart MAMP 2.1.1.

Let’s see what MySQL reports:

Bernard:~ Bernard$ /Applications/MAMP/Library/bin/mysqladmin variables|grep utf8
| character_set_client           | utf8
| character_set_connection       | utf8
| character_set_database         | utf8
| character_set_results          | utf8
| character_set_server           | utf8
| character_set_system           | utf8
| collation_connection           | utf8_general_ci
| collation_database             | utf8_general_ci
| collation_server               | utf8_general_ci
| init_connect                   | SET NAMES utf8
Bernard:~ Bernard$

Looks real good so far.

setUTF() automatically runs in JDatabaseMySQLi :

mysqli_query($this->connection, "SET NAMES 'utf8'");

So, MySQL and JDatabaseMySQLi are set for utf8 but a dump of the connection shows something different.

In my component (Joomla is configured to use MySQLi):

$db = JFactory::getDbo();
var_dump(mysqli_get_charset($db->getConnection())->charset);
string(6) "latin1"

Um, WTH? What went wrong? PHP 5.4.4, MySQL 5.5.25, and Joomla! 2.5.6 are all set for utf8 not latin1. At this point I have no idea. But, after hours of head scratching, the fix is very simple.

In Joomla! edit the JDatabaseMySQLi file located at:

/Applications/MAMP/htdocs/Joomla_2.5.X/libraries/joomla/database/database/mysqli.php

Add this to line 456 within setUTF() :

mysqli_set_charset($this->connection,'utf8');

Now run our dump again we get:

$db = JFactory::getDbo();
var_dump(mysqli_get_charset($db->getConnection())->charset);
string(4) "utf8"

Final thoughts. This is obviously not a long term solution. As soon as you upgrade to Joomla! 2.5.7, it will overwrite your changes to the library. Hopefully this issue will correct itself in the future.

Happy Coding.

bernard

One thought on “MAMP 2.1.1 Character Set Issues

Comments are closed.