Database error, need a workaround

[beartest]

I have earlier posted about a database error

Incorrect string value: '\x92s Now...' for column 'body' at row 1
File: /home/customer/www/*****.com/public_html/Sources/Subs-Post.php
Line: 1842

Here

Incorrect string value: '\x92s Now...' for column 'body' at row 1
File: /home/customer/www/*****.com/public_html/Sources/Subs-Post.php
Line: 1842

I get this error when ever I am trying to post something now

Any ideas how can I get rid of this?

I contacted siteground support, where I am hosting my website. They told me that I should contact the developer. Well, I am now writing the posts in html, typing the whole thing and then pasting it in smf. I am not getting the database error mentioned above again.

I need to copy paste some thing from some websites and then when I try to post it , I get the error again. I even use textedit on my mac to get rid of any formatting that the copied text might have. I still get the error when I copy paste from any website.

I need to know if I can use some app or website online which can strip the copied text from anything that causes smf to throw error in the database.

I am also attaching screenshots of the database collations that I have right now for my forum.

If there is some workaround , I would love to implement it.

Its really a pain typing long posts when I can copy paste them.

Attached is the screenshot of my forum's dbases.

https://www.dropbox.com/s/leybmpo80uv3xn7/Screenshot%202020-04-07%2020.41.12.png?dl=0

I get this error when I proceed

Column 'subject' cannot be part of FULLTEXT index
File: /home/customer/www/fightc19india.com/public_html/Sources/ManageMaintenance.php
Line: 664

Hope I could explain what issue I am facing.

Any pointers to fix this are appreciated.

[shawnb61]

I have earlier posted about a database error

Incorrect string value: '\x92s Now...' for column 'body' at row 1
File: /home/customer/www/*****.com/public_html/Sources/Subs-Post.php
Line: 1842
...
I need to copy paste some thing from some websites and then when I try to post it , I get the error again. I even use textedit on my mac to get rid of any formatting that the copied text might have. I still get the error when I copy paste from any website.

I need to know if I can use some app or website online which can strip the copied text from anything that causes smf to throw error in the database.

\x92 is an invalid value in utf8.  It is a valid windows-1252 character for a single quote: '.  You need to tell textedit you want to work in utf8. 

(To locate the problem text in your source, I think it will look like: 's Now.)

This might help show you where to change the setting in textedit:
https://discussions.apple.com/thread/7660195

I don't know textedit...  Macs scare me... 

Attached is the screenshot of my forum's dbases.

https://www.dropbox.com/s/leybmpo80uv3xn7/Screenshot%202020-04-07%2020.41.12.png?dl=0
...
I get this error when I proceed

Column 'subject' cannot be part of FULLTEXT index
File: /home/customer/www/fightc19india.com/public_html/Sources/ManageMaintenance.php
Line: 664

Any pointers to fix this are appreciated.

This looks like you are attempting to convert your forum to UTF-8, but it is failing due to having a FULLTEXT index. 

I do think you want to go to UTF8 (everybody should).  To do so, remove the fulltext index under Search Settings, then try again.  After the UTF8 conversion, go back to search settings.  If fulltext isn't allowed, then build a custom index.

Hope this helps,

[beartest]

Thanks for your reply. I did the settings in textedit.

I wish I had the foresight to do it myself.

For the dbase error, I am getting this error when I build a custom index


UPDATE: getting the error

Code Select Expand

Incorrect string value: '\x96 but ...' for column 'body' at row 1
File: /home/customer/www/fightc19india.com/public_html/Sources/Subs-Post.php
Line: 1843

I opened a new document and saved it using the UTF-8 encoding.

Code Select Expand

Database Error
Data too long for column 'word' at row 2383
File: /home/customer/www/******.com/public_html/Sources/ManageMaintenance.php
Line: 664






When I switch to fulltext index, I get this error

Code Select Expand

An Error Has Occurred!
Your messages table is using a fulltext index for use when searching. You cannot proceed in converting to UTF-8 until that index is removed. You can re-create it after the conversion has been completed.

[shawnb61]

OK, we have at least 2 different activities going on here...

The '\x96' tells us that you are still getting invalid values when trying to post.  So your textedit isn't filtering as you expect/hope.  You need to find a better way to strip funky characters.  I don't know of one, to be honest.  Copying/pasting is problematic, and should be avoided. 

The 'data too long' error appears to be from an attempt at a utf8 conversion. 

The 'fulltext' error also appears to be from an attempt at a utf8 conversion. 

So...  My first question, is whether you really are UTF8 or not. 

Could you provide a screenshot of the collations for each of your tables?  It will show up in phpmyadmin when you first select the database.  It should look something like the attached.

[beartest]

Yes, it seems I will have problems copying and pasting.

I took a print of the DBase and I am uploading it here now

I hope it helps to diagnose what needs to be done.

Thanks for all the help, I really appreciate it.

[beartest]

any ideas?

[shawnb61]

Two more questions:

In your Settings.php file, do you see an entry for:

$db_character_set = 'utf8';

In your settings table, do you see an entry for:
- variable: global_character_set
- value: UTF-8

In phpMyAdmin, you can figure that out by running this query:

Code Select Expand

SELECT * FROM `soft_settings` WHERE `variable` like "glob%";

I am thinking that at this point we need to complete the conversion to UTF8 by hand. 

I think some of the issues you are seeing are due to the mixture of collations.

If you answer the two questions above, we can provide some specific things to do to properly go UTF8.

[beartest]

Thanks for taking out the time to reply.

I did what you asked.

Code Select Expand

$db_character_set = 'utf8';

No, I don't have this line in settings.php

https://www.dropbox.com/s/mtk5tjbl0ktxqzy/Screenshot%202020-04-10%2011.14.04.png?dl=0

Kindly have a look.

Also, I ran the query, you asked me to.

Code Select Expand

SELECT * FROM `soft_settings` WHERE `variable` like "glob%";


It returned 0 results

have a look

https://www.dropbox.com/s/iifbxbe5mp7de5p/Screenshot%202020-04-10%2011.16.42.png?dl=0

I hope I did it right, what you asked.

[shawnb61]

Yes, that all looks good. 

So...  First off, it looks like your DB conversion to UTF8 stopped & didn't complete.  But most tables are there.  We will need to complete it by hand. 

Here are the steps I would take:
1.  BACKUP.  If you're not comfortable with backups & restores, contact your host and have them do the backup.  Do both a DB and a filesystem backup.  ***If there are problems & you cannot restore, we can't help you.  BACKUP.***

2.  Delete your FULLTEXT search index.  I think this is what has been holding you up. 

3.  Complete the UTF8 conversion by aligning all the tables onto the utf8_general_ci collation.  I believe you can do this in phpMyAdmin most of the time.  Depending on your version of phpMyAdmin/MySQL, you can do this in one step.  You will want to select the 'change all tables' and 'change all tables columns' options.  See pic below. 

4.  Add the $db_character_set entry to Settings.php:

Code Select Expand

$db_character_set = 'utf8';

5.  Add the global_character_set entry to your soft_settings table, with a value of UTF-8.  (See info above)

6.  Change your language files to utf8.  Follow the instructions here, starting on step 6:
https://wiki.simplemachines.org/smf/UTF-8_Readme

7.  Run "Convert HTML-entities to UTF-8 characters" under Admin | Maintenance | Forum Maintenance | Database. 

8.  Build your search index anew.  If FULLTEXT isn't allowed (there are some limitations) then create a custom index.

TEST EVERYTHING, including search functionality.   

If things go sideways, document it as best you can & then have the host restore using the restore from step 1. 

If you have ANY friends who are admins, they are usually willing to help when there is beer involved. 

[beartest]

thanks, I seem to have solved the problem.

Search index full text made and search is working

Tested using search also

Copied and pasted HTML content and I am not facing any errors.

Tested in tinyportal as well as forum topics and replies.

Everything seems to be working fine.

finally, problem solved.

A big thank you for the help that you provided.

If you have ANY friends who are admins, they are usually willing to help when there is beer involved. 

I don't have any admin friends, but I am happy to buy you a beer

The website that I am developing means lots to me and given the full time work that I have, it was getting irritating to get errors every now and then.

I chose SMF because it is very fast to load. I wanted to use wordpress first with a forum option, but SMF is faster I believe.

I don't know if I have mentioned this earlier, but you guys are really doing a good job.

Stay safe.