History of MySQL and character encoding
__Description:__
MySQL installation can cause bad characters to appear on your site, or in database.
__Symptoms: __
On your site, non-standard characters appear as ?. In your data base, they can appear as ößüäÜ. instead of äÜßçé and so on.
__Problem:__
The "encoding" of the data is not the same end-to-end between database and website. A lot of MySQL installations use Latin1, which doesn't work when it comes to Bitweaver handling the odd characters. "UTF-8" is what you should be aiming for.
__Remedy:__
===If=== you have your own server, then add this to the my.cnf file for MySQL:
{code}
[mysqld]
#added to enforce utf 8 encodings:
collation_server=utf8_general_ci
character_set_client=utf8
character_set_server=utf8
skip-character-set-client-handshake
{/code}
Then restart the MySQL engine.
===If=== you are on a shared host server:
edit the bitweaverinstallation/util/adodb/drivers/adodb-mysql.inc.php
as follows:
just before line 366, add:
if (mysql_query("SET NAMES 'UTF8'") === false) return false;
May help: add to the .htaccess file this single line:
AddDefaultCharset UTF-8
May help: add to the php.ini file (if you have one):
{code}
default_charset = "UTF-8"
{/code}
Also:
Your database may not be UTF-8 in it's collation settings. It is often easier to dump the SQL to a file, and use a good text editor (utf8 compliant) to alter it (changing the CHARSET=Latin1 to CHARSET=utf8). Create a new database, with Collation set to utf8_general_ci, and SET NAMES 'UTF8'. Using the text editor, find/replace every bad character with the correct character ( you will find a good Character Map handy ), and you will obviously need to know what character it was supposed to be.
This can take hours of search and destroy.
Then upload the cleaned database to the new, empty database.
Adjust the /kernel/config_inc.php file to reflect any change in database name.
Test and enjoy frontend-to-backend freedom of character input.
__Caveats:__
(:exclaim:)
This took a lot of trial and error. It may not work with your situation, but I found it worked with local installations of Ubuntu's MySQL, and remote Hosted one ( at Bluehost.com). It assumes you want to use UTF8 exclusively. I suggest you read up on this choice before commitment.
__Extra Reading:__
[http://dev.mysql.com/doc/refman/5.0/en/charset-connection.html|http://dev.mysql.com/doc/refman/5.0/en/charset-connection.html]
[http://tracker.moodle.org/browse/MDL-11743|http://tracker.moodle.org/browse/MDL-11743] - another CMS with same issue
[http://dev.mysql.com/doc/refman/5.0/en/charset-applications.html|http://dev.mysql.com/doc/refman/5.0/en/charset-applications.html]
Also, running SHOW VARIABLES; at the Mysql command prompt on your server can be very revealing.
Many thanks to Tommie-Lie and Xing.
MySQL installation can cause bad characters to appear on your site, or in database.
__Symptoms: __
On your site, non-standard characters appear as ?. In your data base, they can appear as ößüäÜ. instead of äÜßçé and so on.
__Problem:__
The "encoding" of the data is not the same end-to-end between database and website. A lot of MySQL installations use Latin1, which doesn't work when it comes to Bitweaver handling the odd characters. "UTF-8" is what you should be aiming for.
__Remedy:__
===If=== you have your own server, then add this to the my.cnf file for MySQL:
{code}
[mysqld]
#added to enforce utf 8 encodings:
collation_server=utf8_general_ci
character_set_client=utf8
character_set_server=utf8
skip-character-set-client-handshake
{/code}
Then restart the MySQL engine.
===If=== you are on a shared host server:
edit the bitweaverinstallation/util/adodb/drivers/adodb-mysql.inc.php
as follows:
just before line 366, add:
if (mysql_query("SET NAMES 'UTF8'") === false) return false;
May help: add to the .htaccess file this single line:
AddDefaultCharset UTF-8
May help: add to the php.ini file (if you have one):
{code}
default_charset = "UTF-8"
{/code}
Also:
Your database may not be UTF-8 in it's collation settings. It is often easier to dump the SQL to a file, and use a good text editor (utf8 compliant) to alter it (changing the CHARSET=Latin1 to CHARSET=utf8). Create a new database, with Collation set to utf8_general_ci, and SET NAMES 'UTF8'. Using the text editor, find/replace every bad character with the correct character ( you will find a good Character Map handy ), and you will obviously need to know what character it was supposed to be.
This can take hours of search and destroy.
Then upload the cleaned database to the new, empty database.
Adjust the /kernel/config_inc.php file to reflect any change in database name.
Test and enjoy frontend-to-backend freedom of character input.
__Caveats:__
(:exclaim:)
This took a lot of trial and error. It may not work with your situation, but I found it worked with local installations of Ubuntu's MySQL, and remote Hosted one ( at Bluehost.com). It assumes you want to use UTF8 exclusively. I suggest you read up on this choice before commitment.
__Extra Reading:__
[http://dev.mysql.com/doc/refman/5.0/en/charset-connection.html|http://dev.mysql.com/doc/refman/5.0/en/charset-connection.html]
[http://tracker.moodle.org/browse/MDL-11743|http://tracker.moodle.org/browse/MDL-11743] - another CMS with same issue
[http://dev.mysql.com/doc/refman/5.0/en/charset-applications.html|http://dev.mysql.com/doc/refman/5.0/en/charset-applications.html]
Also, running SHOW VARIABLES; at the Mysql command prompt on your server can be very revealing.
Many thanks to Tommie-Lie and Xing.