Thread Like Summary
isaac
Total Likes: 4
Original Post (Thread Starter)
#263913 06/08/2020 12:09 AM
by jjjjj
jjjjj
I am not sure if this is a bug, or possibly configuration issue.

I am seeing errors when the users are inserting records into POSTS that have an apostrophe in them, ex, the work "can't" would fail. In the logs, this is what I see I am seeing <92> replacing an apostrophe, and then seeing an incorrect string value below. Is there a configuration in php, or mysql that I don't have correct? or is this a bug?

INSERT INTO
ubbt_POSTS
(POST_PARENT_ID, TOPIC_ID, POST_IS_TOPIC, POST_POSTED_TIME, POST_POSTER_IP, POST_SUBJECT, POST_BODY, POST_DEFAULT_BODY, POST_IS_APPROVED, POST_ICON, POST_HAS_POLL, POST_HAS_FILE, POST_MARKUP_TYPE, USER_ID, POST_PARENT_USER_ID, POST_ADD_SIGNATURE, POST_LAST_EDITED_TIME, POST_POSTER_NAME, POST_MD5)
VALUES
(153023, 17905, 0, 1591546837, '208.90.218.178', 'Posts title', 'as I can<92>t talk about this it ', 'as I can<92>t talk about this it', 1, 'book.gif', '0', '0', 'none', 8, 104, 1, 0, 'mrb', '')
- Incorrect string value: '\x92t tal...' for column 'POST_BODY' at row 1

Doing some looking around, it may have to do with the character set. Could someone check to see if I need to change the character_set_database to utf8mb4?


Database changed
mysql> SHOW VARIABLES
-> WHERE Variable_name LIKE 'character\_set\_%'
-> OR Variable_name LIKE 'collation%';
+--------------------------+--------------------+
| Variable_name | Value |
+--------------------------+--------------------+
| character_set_client | utf8mb4 |
| character_set_connection | utf8mb4 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | utf8mb4 |
| character_set_server | utf8mb4 |
| character_set_system | utf8 |
| collation_connection | utf8mb4_general_ci |
| collation_database | utf8_general_ci |
| collation_server | utf8mb4_general_ci |
+--------------------------+--------------------+
10 rows in set (0.01 sec)
Liked Replies
#263960 Jun 29th a 06:31 PM
by Gizmo
Gizmo
Looks like something in your database schema isn't utf8mb4, your system should have no problem posting an apostrophe if EVERYTHING was utf8mb4.

Originally Posted by Gizmo
Additionally, could you open the offending table and take a screenshot of the table structure so we can validate that the cells are indeed utf8mb4_general_ci
Please take screenshots of PHPMyAdmin of your listing of tables and within the ubbt_POSTS table's schema to validate everything is utf8mb4.

Reading:
https://www.google.com/search?client=firefox-b-1-d&q=why+mysql+errors+%5Cx92
https://forums.mysql.com/read.php?132,365097,365097
https://stackoverflow.com/questions...avoid-mysql-incorrect-string-value-error
1 member likes this
#263961 Jun 29th a 06:55 PM
by Gizmo
Gizmo
Originally Posted by jjjjj
mysql> SHOW VARIABLES
-> WHERE Variable_name LIKE 'character\_set\_%'
-> OR Variable_name LIKE 'collation%';
+--------------------------+--------------------+
| Variable_name | Value |
+--------------------------+--------------------+
| character_set_client | utf8mb4 |
| character_set_connection | utf8mb4 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | utf8mb4 |
| character_set_server | utf8mb4 |
| character_set_system | utf8 |
| collation_connection | utf8mb4_general_ci |
| collation_database | utf8_general_ci |
| collation_server | utf8mb4_general_ci |
+--------------------------+--------------------+
10 rows in set (0.01 sec)

The database character_set_database and collation_database affect inserting data into the database; as you stated above your character_set_database value is utf8 and not utf8mb4. You're attempting to insert 4 bytes of data into a field that doesn't allow it, hence the error.
1 member likes this
#263965 Jun 29th a 09:08 PM
by Gizmo
Gizmo
I'm really not sure, the schema looks correct; I'm assuming your MySQL logs show new entries after you updated your schemas before right? (don't just take users at their word, a log will be written when an error occurs).

Do you have the attached function enabled in UBB.threads (its in db settings)?
Attached Images
1 member likes this
#263955 Jun 29th a 04:36 PM
by Ruben
Ruben
Then why did you need to go back in and change it again?
To solve the first problem
.
The directions are clear in Control Panel > Paths & Database..

Quote
MySQL Full Unicode Support (utf8mb4)
Sets the character set when sending data from and to the database server to utf8mb4 (full unicode support). Confirm that your table collations are utf8mb4 (eg. utf8mb4_general_ci) before enabling.

MySQL by default only uses a three byte encoding and so values in the four byte range (eg. Asian characters and Emojis) can not be stored. Any attempt to enter a text that contains four byte characters will result in those characters being returned as errors. MySQL does provide full four byte UTF-8 support, but it requires certain database settings to be configured. Converting to utf8mb4_unicode_ci is preferred. 4-Byte UTF-8 Unicode requires MySQL 5.5.3 or above.

Before you do anything again I would suggest you wait for a response from the programmers.
1 member likes this
ShoutChat
Comment Guidelines: Do post respectful and insightful comments. Don't flame, hate, spam.
Recent Topics
PM limits
by jjjjj - 01/07/2021 12:59 PM
FAQ is blank?
by Bad Frog - 01/06/2021 12:01 PM
IP Addresses Of Users
by tinker123 - 01/03/2021 3:07 PM
User display name gets chopped
by - 12/25/2020 1:08 PM
Who's Online Now
0 members (), 32 guests, and 54 robots.
Key: Admin, Global Mod, Mod
Random Gallery Image
Latest Gallery Images
gallery test
gallery test
by Mors, September 23
Los Angeles
Los Angeles
by isaac, August 6
3D Creations
3D Creations
by JAISP, December 30
Artistic structures
Artistic structures
by isaac, August 29
Powered by UBB.threads™ PHP Forum Software 7.7.5