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)
Well not sure what you did to convert to utf8mb4. If you tried outside of the tool included with UBB. or what. But what are the tables collations? If it was set correctly then a simple test for 4 byte characters is try the windows emoji shortcut in a post Which is hold down the win key then press the period key Then click on the images you want to post 😊💋😁🐱🚀 It should post the image. If not then it is not setup for 4 byte characters correctly
Last edited by Ruben; 06/08/20201:21 PM.
Blue Man Group There is no such thing as stupid questions. Just stupid answers
Just as a guide all the tables should be utf8mb4_general_ci The database should be utf8mb4_general_ci Here is your query from a site that is working: character_set_client utf8mb4 character_set_connection utf8mb4 character_set_database utf8mb4 character_set_filesystem binary character_set_results utf8mb4 character_set_server latin1 character_set_system utf8 collation_connection utf8mb4_general_ci collation_database utf8mb4_general_ci collation_server latin1_swedish_ci
Blue Man Group There is no such thing as stupid questions. Just stupid answers
I thought this was resolved, but I am still seeing errors in the mysql log when people use an asterisks. INSERT INTO ubbt_POSTS... Script: /home/dbs/ubbthreads/frame/scripts/addpost.inc.php - Line: 491 Incorrect string value: '\x92
Yes, I used the built in tool, and the option now is not selectable in the GUI, assuming it has detected this. Is this possible a platform issue? I can enter posts with asterisks, but some of the forum users are running into this? Is there a per user/group setting that might cause this?
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.
Blue Man Group There is no such thing as stupid questions. Just stupid answers
Thanks, I will wait to hear from the programmers. This is not a 4 byte issue, it is asterisks, eg I'm causing problems, and it seems to be either platform or user specific. I can enter asterisks in my posts, I am thinking that something that masks the ticks in the sql statements is broke.
The only forum setting I am aware of that may be different from a admin and users may be , allow ubbcode and allow html for posts. It is not recommended to allow html posts for anyone. But usually everyone allows users UBB Code posts.
While waiting what I would do is first goto phpmyadmin and make sure every table is set to utf8mb4_general_ci and the database is set to utf8mb4_general_ci.
And report back if at least any of that was incorrect. But don't change anything yet.
To make the tool run again it is just a couple of simple clicks on UBB cp but I refrain from saying so till you hear from the experts.
Blue Man Group There is no such thing as stupid questions. Just stupid answers
Could you please supply the data you're attempting to post? You say it's erroring out on an asterix (*) however the last error that you posted is showing \x92 which is an apostrophe.
There isn't any filtering that happens server side for some users that don't for others, unless you're allowing HTML postring.
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
I have logging turned on, and you are correct, the * is not the problem it is the apostrophe. From the logs
Script: /home/dbs/ubbthreads/frame/scripts/addpost.inc.php - Line: 491 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 (0, 17928, 1, 1593347421, '50.89.134.149', 'PC 6/27 otherside', 'Left the dock @4:30, saw the weedline in 150� and kept running east. Saw the first pack of birds 6 miles from the corner and got a double knockdown the lines got wrapped so we only got one of the two fish. Gave the pack a few more passes w/ no luck before turning NE to run to the buoy. We get about 25 miles from the buoy and find a nice pack of birds and catch two bigger fish. It�s now 2pm and we have to turn for the 3hr ride home.
Awesome day. First time catching 3 YFT on my boat and certainly didn�t expect it the last wk of June. Any one else make it out?', 'Left the dock @4:30, saw the weedline in 150� and kept running east. Saw the first pack of birds 6 miles from the corner and got a double knockdown the lines got wrapped so we only got one of the two fish. Gave the pack a few more passes w/ no luck before turning NE to run to the buoy. We get about 25 miles from the buoy and find a nice pack of birds and catch two bigger fish. It�s now 2pm and we have to turn for the 3hr ride home.\r\n\r\nAwesome day. First time catching 3 YFT on my boat and certainly didn�t expect it the last wk of June. Any one else make it out?', 1, 'book.gif', '0', '0', 'markup', 218, 0, '0', 0, 'DaVapors', 'a0b80cd339387bb74628ea914ffbae6c') - Incorrect string value: '\x92 and ...' for column 'POST_BODY' at row 1
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.
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.
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.
I'm still waiting on the above screenshots.
Can you reproduce the error here or at UBBDev, with the exact same post data? If not, it's a server configuration issue, and there is little we can do to diagnose and fix problems with server configurations; we're a script provider and we assume that your server configuration is compatible.
You may just need to toggle the "MySQL Full Unicode Support (utf8mb4)" setting to see if your system will operate without errors.
You could try creating a fresh install of UBB.threads 7.7.4 from a fresh download from the member area in an empty database and see if it works as expected (creating utf8mb4 posts, actually creating and not previewing so it is written to the database) and compare.
A properly working utf8mb4 setup will allolw you to post emojis (as a test): 😎✌👌
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)?
I have character-set-server=utf8mb4 set in the my.cnf file
note that changes to the variables in config file (my.cnf) often require a mysql server restart. server variable changes take effect after a server restart since that's when they are read.
Did you know that you can uncheck that box, then go to Control Panel > Content Rebuilder and have the UBB.threads software convert your tables for you:
When you click that link, the utf8mb4 conversion process will begin. That process looks like this example:
(the conversion screenshots above were from my WIP/development server. For release/public code, "Tables not converted" instead will show the number of actual tables converted on your forum install.)
Then follow the "Enable "MySQL Full Unicode Support" from the Database Settings page" directions given once the tool finishes it's job.
Which will bring you back to the checkbox that Ruben, Gizmo and myself have asked you about earlier in this thread:
All that the checkbox does is tell the MySQLi connector to use the utf8mb4 character set for communication with the database server. Also when the box is checked, the hyperlink from "Update All Tables to UTF8mb4" within Content Rebuilder/Transition Actions will be disabled. That is all it does.
CODE:
The checkbox sets the default character set to be used when sending data from and to the database server.
Last edited by isaac; 06/30/20204:45 PM. Reason: added screenshots
I reran the table char set update. I am not sure if I had selected the "MySQL Full Unicode Support (utf8mb4)" check box when I did this the first time. I rebooted the system afterward. I will watch the log and see if I get any more error messages and report back. Thanks for the help.
I created a new test forum, and did not get any errors on the same instance. I cleared the test database, and imported from production, and it worked fine. I can only assume this had something to do with the migrations. I have promoted the test to production, and am having no problems with character formatting in insert. Not sure there is a root cause, if the developers want any of the config files, I have them from both the working, and non-working instances.