SQL ERROR [ mysql4 ] - Incorrect string value

themrb · Post by **themrb** » Fri Mar 04, 2011 1:14 am

Support Request Template
What version of phpBB are you using? phpBB 3.0.8
What is your board's URL? http://bushido.edgebuilding.com.au
Who do you host your board with? Unsure
How did you install your board? I used the download package from phpBB.com
Is your board a fresh install or a conversion? Fresh Install
Do you have any MODs installed? No
Is registration required to reproduce this issue? No
What styles do you currently have installed? DVGFX
What language(s) is your board currently using? British English
Which database type/version are you using? MySQL 4
What is your level of experience? New to PHP and phpBB
When did your problem begin?

Problem happens when I try to post the character below, which I think is 'Box Light Horizontal', U+2500

"─────────────────"

(interestingly, the support template didn't like this character either and changed it to a bunch of ?s)

The error is:

SQL ERROR [ mysql4 ]

Incorrect string value: '\xE2\x94\x80\xE2\x94\x80...' for column 'post_text' at row 1 [1366]

SQL

INSERT INTO phpbb_posts (forum_id, poster_id, icon_id, poster_ip, post_time, post_approved, enable_bbcode, enable_smilies, enable_magic_url, enable_sig, post_username, post_subject, post_text, post_checksum, post_attachment, bbcode_bitfield, bbcode_uid, post_postcount, post_edit_locked, topic_id) VALUES (6, 54, 0, '120.151.48.71', 1299200214, 1, 1, 1, 1, 1, '', 'Re: test', '─────────────────', '1e7a5cce5963f92ff8eda2ca823ce608', 0, '', '2wf0pfvq', 1, 0, 24)

BACKTRACE

FILE: includes/db/mysql.php
LINE: 175
CALL: dbal->sql_error()

FILE: includes/functions_posting.php
LINE: 1985
CALL: dbal_mysql->sql_query()

FILE: posting.php
LINE: 1137
CALL: submit_post()

Is this an unavoidable problem with the mysql database ... ? Would asking my host to upgrade to mysql 5 solve the problem?

Post by **Noxwizard** » Fri Mar 04, 2011 3:04 am

Follow the instructions in this article: Doesn't have a default value errors

Thanks in advance · Post by **Thanks in advance** » Sun Sep 18, 2011 10:24 pm

I have the same problem "Incorrect string value..." whenever I cleared the board cache (after pressing F5 in my browser the forum / main page loads normally again). I used the MySQL Upgrader from the Support Tool Kit ("Run"), but after finishing the problem still exists. Are there any other possible solutions to fix this? Thanks!

Oyabun1 · Post by **Oyabun1** » Sun Sep 18, 2011 10:39 pm

Did you get a message 'The MySQL Upgrader has been run successfully'? If so, it could be the data is cached, so via FTP open the cache folder on the server and delete everything except index.htm and .htaccess (if you can see it).

Thanks in advance · Post by **Thanks in advance** » Sun Sep 18, 2011 10:47 pm

Oyabun1 wrote:Did you get a message 'The MySQL Upgrader has been run successfully'?

Yes.

I deleted the board cache. The problem always occurs when I do this: I get the error message, but when I (or somebody else) reloads the main page, it goes away.

D¡cky · Post by **D¡cky** » Sun Sep 18, 2011 11:55 pm

themrb wrote:Which database type/version are you using? MySQL 4

Is the database version 4.0.x or 4.1.x? The version will be shown in the Administration Control Panel.

Thanks in advance · Post by **Thanks in advance** » Mon Sep 19, 2011 5:54 am

It's 5.1.49

Thanks in advance · Post by **Thanks in advance** » Mon Sep 19, 2011 2:21 pm

Does the limitation discussed in that thread still exist with phpBB 3.0.8 or 3.0.9 and might be the reason? Does the MySQLUpgrader solve the problem or is the mentioned work-around the "solution"? Shall I use UTF8mb4? uft8_bin? utf8_general_ci? utf8_unicode_ci? *really confused*

D¡cky · Post by **D¡cky** » Tue Sep 20, 2011 3:11 am

Thanks in advance wrote:Does the limitation discussed in that thread still exist with phpBB 3.0.8 or 3.0.9 and might be the reason? Does the MySQLUpgrader solve the problem or is the mentioned work-around the "solution"

The limitation in that thread is not the same as your problem. The mysql_upgrader should have fixed your problem.

Thanks in advance wrote:Shall I use UTF8mb4? uft8_bin? utf8_general_ci? utf8_unicode_ci? *really confused*

What are you using now? The post_text field in the posts table should be utf8_bin. If it is not, set it with this query:

Code: Select all

ALTER TABLE phpbb_posts CHANGE post_text post_text MEDIUMTEXT CHARACTER SET utf8 COLLATE utf8_bin NOT NULL

Thanks in advance · Post by **Thanks in advance** » Tue Sep 20, 2011 5:36 am

*deleted*

Thanks in advance · Post by **Thanks in advance** » Tue Sep 20, 2011 10:13 am

post_text mediumtext utf8_unicode_ci

D¡cky · Post by **D¡cky** » Tue Sep 20, 2011 4:02 pm

Thanks in advance wrote:post_text mediumtext utf8_unicode_ci

post_text should be mediumtext utf8_bin
post_subject should be varchar (255) utf8_unicode_ci

Change post_text to utf8_bin to see if that makes a difference.

Thanks in advance · Post by **Thanks in advance** » Thu Sep 22, 2011 2:46 pm

Is "post_text should be mediumtext utf8_bin" also correct for German boards? Just want to be sure, thanks!

Post by **stevemaury** » Thu Sep 22, 2011 3:01 pm

Yes.

Ja.

Thanks in advance · Post by **Thanks in advance** » Thu Sep 22, 2011 3:34 pm

Thanks, Danke!

advertisements