Pound symbol acts as escape character

[mattrm]

Hi all,

Currently every time you put a £ symbol into a post everything after the £ symbol (including the £) is removed once you've posted the post.  eg

Type "test £ test" and the post will be "test"

This does happen for a couple of other symbols as well.  They are faily obscure, the main symbol that is causing the problem is the £.  I have tested this with other LAMP software (eg wordpress and phpbb) and this doesn't happen.  You can insert £ symbols directly into the database at a SQL level (via a query) however.

My phpinfo file is here - http://pathfinderlarp.org.uk/phpinfo.php [nofollow]

Thanks in advance.

[greyknight17]

Was this ever working for you in the past? Any changes made recently (maybe some language files installed)?

[mattrm]

This was working after the original install (1.0 iirc).  I then moved to 1.1 RC3 (with hindsight, I'm never moving to an RC again.  ever!) and just updated to 1.1.4.  It was after RC3 that the problems started.  The upgrade destroyed a lot of posts that had certain symbols in them (eg £).  I had hoped that upgrading to 1.1.4 would fix it.

As it might be relevant, I don't have UTF-8 turned on.

[greyknight17]

I was leaning towards converting to UTF-8, but don't know if it's a good idea to do that just for the Euro character. I have 1.1.4 running here without that issue.

Does your forum use other languages as well? That would be the only reason I suggest converting to UTF-8. Remember, it's not easy (or can't) go back once you convert. So make sure you have a good backup somewhere.

How long was the change to 1.1RC3? If not long, maybe restore the database for now until someone can figure out why this is happening. I'm not sure if the Euro character can be fixed if it's upgraded to 1.1.4 for the database.

I will ask one of the staff members who have more knowledge on this to drop by and help.

[Sarge]

Can you post the username and password for a test account (regular member)?

Also tell us where to post (a particular board or topic) for testing purposes.

[mattrm]

We changed to 1.1RC3 ages ago.  We don't use any special characters and no foriegn languages, so there's no point to convert to UTF-8.

We have a 'technical' section to the forum, it would be best to post there:

http://pathfinderlarp.org.uk/forum/index.php?board=4.0 [nofollow]

Login wise, I'll PM you details.  I don't want to put any login details on an open forum, understandably.

If you are an SMF support person and you get a login, please PM me on here you're username and I'll approve you straight away.

[Sarge]

Can you check from phpMyAdmin if the posts contain the £ character? If they don't, you'll have to use a backup to restore older messages.

What are the database, table and field (column) collations? You can check those in phpMyAdmin. For example, check out the collation for the 'smf_messages' table and the 'body' column in that table.

Also post the MySQL charset and collation settings.

[mattrm]

I've checked to see what is happening to the posts at a database level before and they don't make it to the database.  I've just double checked your post and it doesn't show the £ in the table.  I'm not worried about the lost messages (they were lost ages ago and we've no backups from back then) I just want to fix it for the future.

One thing that I'd completely forgot to mention was that this forum started life as a phpbb2 forum, which was then converted to smf 1.0 and then upgraded to 1.1RC3 (which was when we lost the posts and the £ problem started, it isn't just £ symbols, but that is the worst offender).

From the server settings:

collation connection     utf8_unicode_ci
(Global value)    utf8_general_ci
collation database    utf8_general_ci
collation server    utf8_general_ci

From the smf_messages table:

Row Statistics    Statements     Value
Format    dynamic
Collation    utf8_general_ci

Also I saw the below in that table:

UNIQUE and INDEX keys should not both be set for column `ID_TOPIC`
UNIQUE and INDEX keys should not both be set for column `ID_MEMBER`
More than one INDEX key was created for column `ID_MEMBER`

All of the tables are utf8_general_ci

I've not switched to utf8 support.  Unless this was done as part of the conversion process.

If you need more, just ask.

[Sarge]

Here's the problem:

Since the database uses utf8, that's the character set that MySQL uses when dealing with that database. I suspect the database was using latin1 before upgrading to SMF 1.1 RC3 and you exported and reimported the database at some point during the conversion, causing the character set to change.

(latin1 is the default character set in MySQL 4.0 and earlier; on some systems, later versions of MySQL can also be configured to use latin1.)

UTF-8 and ISO-8859-1 (latin1 in MySQL) encode only the lower (0-127) ASCII characters the same way. Upper ASCII characters (128-255), which include the pound sign and other non-standard characters must be re-encoded for UTF-8, otherwise they will cause illegality issues, including truncating text from the illegal character onward (at least that's how MySQL behaves).

The simplest solution in your case is to make SMF use UTF-8 as well. This can be done from Admin > Forum Maintenance > "Convert the database and data to UTF-8" (available in SMF 1.1 RC3 and above). The pound sign and other characters should work correctly after that.

[mattrm]

Ok,  I've converted to Utf-8 and it has fixed the problem.  It all seems ok now.  Thanks for your help.