News:

Want to get involved in developing SMF, then why not lend a hand on our github!

Main Menu

Errors since upgrading to 2.0.17

Started by anidea, January 25, 2020, 06:54:05 PM

Previous topic - Next topic

anidea

Hi all,

Can anyone tell me what I might have done wrong?  Since upgrading to 2.0.17, I'm getting the following errors.  I'm using the default templates although in the past I did use a custom template, but there were so many errors it wasn't worth it so I went back to Curve.  I also took off any mod that might have "buttons" or anything that would build as an index (since the bottom two errors in the file refer to buttons).

Database Error: Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '=' 
File: /home/public/smf/Sources/LogInOut.php
Line: 233

/smf/index.php?action=search
8: Undefined index: title
File: /home/public/smf/Themes/default/index.template.php
Line: 443

/smf/index.php?action=search
8: Undefined index: href
File: /home/public/smf/Themes/default/index.template.php
Line: 442

shawnb61

Two separate issues, I think...

For the collations issue, it appears you have a DB that is partially UTF8.  Two questions:
- What are the collations of all the fields in your members table?   (This is where I think the problem is...)
- What are the collations of all your tables?  (Just in case the problem is broader...)

I cannot reproduce the undefined index issues.  Two questions here, too:
- Can you reproduce the errors?  What actions cause the errors to be logged?
- What mods do you have installed?
Address the process rather than the outcome.  Then, the outcome becomes more likely.   - Fripp

Illori

we are also getting this *here* i believe we are fully UTF-8. minus the first error the OP is reporting.

i reported it to our site team in topic 570604

this was happening back when we were using 2.0.15.

Kindred

the undefined index issues are always due to a badly designed mod.
Слaва
Украинi

Please do not PM, IM or Email me with support questions.  You will get better and faster responses in the support boards.  Thank you.

"Loki is not evil, although he is certainly not a force for good. Loki is... complicated."

Illori

Quote from: Kindred on January 27, 2020, 08:47:48 AM
the undefined index issues are always due to a badly designed mod.

not in all cases. I don't think a mod is involved in this in our case. although this seems to be a rare error to get.

shawnb61

Quote from: shawnb61 on January 27, 2020, 12:41:54 AM
Two separate issues, I think...

For the collations issue, it appears you have a DB that is partially UTF8.  Two questions:
- What are the collations of all the fields in your members table?   (This is where I think the problem is...)
- What are the collations of all your tables?  (Just in case the problem is broader...)

I cannot reproduce the undefined index issues.  Two questions here, too:
- Can you reproduce the errors?  What actions cause the errors to be logged?
- What mods do you have installed?

anidea -
Just to be clear - I would still like answers to the questions above!   
Shawn
Address the process rather than the outcome.  Then, the outcome becomes more likely.   - Fripp

anidea

Quote from: shawnb61 on January 27, 2020, 05:42:39 PM
Quote from: shawnb61 on January 27, 2020, 12:41:54 AM
Two separate issues, I think...

For the collations issue, it appears you have a DB that is partially UTF8.  Two questions:
- What are the collations of all the fields in your members table?   (This is where I think the problem is...)
- What are the collations of all your tables?  (Just in case the problem is broader...)

I cannot reproduce the undefined index issues.  Two questions here, too:
- Can you reproduce the errors?  What actions cause the errors to be logged?
- What mods do you have installed?

anidea -
Just to be clear - I would still like answers to the questions above!   
Shawn
Hi Shawn, as soon as I figure out where to find the answers (to the db UTF8 question I mean), I will let you know!  AFAIK, I never converted to UTF8, the reason is that way back when I first installed SMF, I got too many errors. (since then I've started over brand new several times)  The site is bi/tri lingual, so UTF8 makes sense, I think.

- Can you reproduce the errors?  What actions cause the errors to be logged?
I can't reproduce it myself.  If you are correct about it being about members, maybe it's when new members register?  No new members over the weekend (I think...I need to be more certain about this) and no new errors yesterday, but today we got a bunch of new members, and the error table started growing again.

Mods are the following:
Similar Topics   0.7    
Sorted Package Manager Listing   1.0.2    
Tidy Child Boards   2.0    
Ad Managment   3.5d    
NiceTooltips   1.9    
Yet Another YouTube BBCode Tag   3.11    
Book of Unknown Action   1.0    
Drafts      
Send Locked Topics To Bottom   1.0    
Regbar Warning   1.0.5    
SMF 2.0.17 Update   1.0    
Say Thanks   1.3.6    
Amazon MP3   1.1    
Amazon Search   1.1
Join Reason   1.5    
Join Reason Language Pack



shawnb61

OK, so a few questions.  I don't want you do DO anything yet, just some initial research so we can figure out what to do. 

1)  Under Admin | Maintenance | Forum Maintenance | Database, do you see an option to "Convert the database and data to UTF8"?  See pic 1.

2)  Can you go to phpmyadmin & take a look at the tables?  I'm mostly interested in the collations - what are the collations?  See pic 2.

3)  If, while still in phpmyadmin, you can drill down on the members table, you will see a "Structure" tab at the top.  Click on that & let's see the collations for all the fields in the members table.  See pic 3.
Address the process rather than the outcome.  Then, the outcome becomes more likely.   - Fripp

anidea

Thank you for the pictures!  That really helps.

Quote from: shawnb61 on January 28, 2020, 12:31:40 AM
OK, so a few questions.  I don't want you do DO anything yet, just some initial research so we can figure out what to do. 

1)  Under Admin | Maintenance | Forum Maintenance | Database, do you see an option to "Convert the database and data to UTF8"?  See pic 1.
Convert HTML-entities to UTF-8 characters
This function will convert all characters that are stored in the database as HTML-entities to UTF-8 characters. This is especially useful when you have just converted your forum from a character set like ISO-8859-1 while non-latin characters were used on the forum. The browser then sends all characters as HTML-entities. For example, the HTML-entity α represents the greek letter α (alpha). Converting entities to UTF-8 will improve searching and sorting of text and reduce storage size.
Quote
2)  Can you go to phpmyadmin & take a look at the tables?  I'm mostly interested in the collations - what are the collations?  See pic 2.
Aha, most of them say "latin1_swedish_ci" but some of them do say UTF8.
Quote
3)  If, while still in phpmyadmin, you can drill down on the members table, you will see a "Structure" tab at the top.  Click on that & let's see the collations for all the fields in the members table.  See pic 3.
Aha, most of them say "latin1_swedish_ci" but some of them say nothing at all.  (I think mostly they are from old mods I removed a while back, probably because of errors.)

So if I convert everything to UTF8, will I be okay?

shawnb61

What are the primary languages used on the forum? 

Do you have a lot of text/posts/subjects with accents, Cyrillic letters, Greek letters, etc?  Or is it all English?
Address the process rather than the outcome.  Then, the outcome becomes more likely.   - Fripp

anidea

Quote from: shawnb61 on January 28, 2020, 05:03:50 PM
What are the primary languages used on the forum? 

Do you have a lot of text/posts/subjects with accents, Cyrillic letters, Greek letters, etc?  Or is it all English?
English (70%), Spanish (20%), French (10%).  We don't typically get Cyrillic or Greek, it's mostly accents, and it's only occasionally.

shawnb61

So part of the problem is that you are not properly utf8, but your forum thinks you are.  Let's work on that problem first. 

Steps:
1)  Backup your forum database.  You MUST be able to restore if things go wrong.
2)  Have your host backup your forum database.  You must be able to enlist their help if needed.  Two backups are better than one.  If you cannot restore if needed, that's on you... 
3)  In your settings.php file, there will be a line for $db_character_set (see pic 1).  Delete that entire line & save the settings file. 
4)  Under Admin | Maintenance | Forum Maintenance | Database, you should now see an option to "Convert the database and data to UTF8".  See pic 2.  Click on "Run task now".
5)  You should now be on the page for "Convert the database and data to UTF-8".  Make sure your settings are exactly as shown in pic 3.  Your "Data character set" should say ISO-8859-1.  Click to Proceed.
6)  DO NOT NAVIGATE AWAY FROM THIS PAGE UNTIL IT COMPLETES!!!!  This may take some time!!!  This is where I think folks break their utf8 conversions!!!
7)  When done, you will see a completion message, as in pic 4. 

Navigate around your forum & confirm the results.  Look for things like:
- Posts look OK, no funky characters
- Subjects look OK, no funky characters
- Member names look OK, no funky characters
- Check out your tables in phpmyadmin as above, your table collations should all be utf8_general_ci
- Check out your members table in phpmyadmin, collations for all columns should be utf8_general_ci
- Check out your messages table in phpmyadmin, collations for all columns should be utf8_general_ci
- That $db_character_set row should be back in your settings.php file

Now you are fully utf8.  You should no longer get the collation errors. 
Address the process rather than the outcome.  Then, the outcome becomes more likely.   - Fripp

anidea

Awesome.  I will try this at the next possible moment!  Well, when I have a chunk of time possible moment, because I don't want to make an error.

Thank you so much for the very detailed step by step!  I totally appreciate it.  Will report back shortly.

shawnb61

anidea -

Regarding the 'undefined' issues, a couple questions:
- Do you use sphinx for search?
- What are your cache settings?   
- Do you use memcache?
- Do you use SSI?  Do you have a "landing page" that is not the forum?

Finally, could you attach a copy of index.template.php?

Thanks,
Address the process rather than the outcome.  Then, the outcome becomes more likely.   - Fripp

Chen Zhen


I made a utility mod some time ago to make it easier to adjust the character sets & collations for the database & its tables.
This modification has been updated to work on either branch.

You may want to try it out since it will make the conversion much easier.
Just make sure to backup your database prior initiating any changes.
UTF8 must first be opted as your default setting within SMF prior to being able to use the utility.

ref.
https://web-develop.ca/index.php?action=downloads;area=stable_smf_utilities;file=36

My SMF Mods & Plug-Ins

WebDev

"Either you repeat the same conventional doctrines everybody is saying, or else you say something true, and it will sound like it's from Neptune." - Noam Chomsky

anidea

Hi shawnb, I finally did this and it worked!  So far no more errors.  I am hopeful again!  Yay!  Thank you so much!

Thank you Chen Zhen, I didn't get to try your utility.  If it happens again, I most certainly will give it a try!  Thank you for your help!


anidea

Oh, I totally missed this.

I don't know what sphynx is, so if it's something I have to opt into, I didn't do it.  Same answer for memcache.  My cache settings are whatever the forum defaults to (I didn't change this, I think there are warnings in that section about changing things).

I do use SSI, and I do have a landing page that is not the forum.  Is that a problem?

Quote from: shawnb61 on January 31, 2020, 02:04:38 PM
anidea -

Regarding the 'undefined' issues, a couple questions:
- Do you use sphinx for search?
- What are your cache settings?   
- Do you use memcache?
- Do you use SSI?  Do you have a "landing page" that is not the forum?

Finally, could you attach a copy of index.template.php?

Thanks,

shawnb61

Ok, just to confirm:  Are you still getting the undefined index errors for title & xref?
Address the process rather than the outcome.  Then, the outcome becomes more likely.   - Fripp

anidea

Quote from: shawnb61 on February 22, 2020, 09:14:30 PM
Ok, just to confirm:  Are you still getting the undefined index errors for title & xref?

So far I haven't seen them!  Yay!

anidea

I spoke too soon:

http://www.mysite.org/smf/index.php?scheduled=task;ts=1583971200
8: Undefined index: utf8
File: /home/public/smf/Sources/Subs-Post.php
Line: 1262

other variations include Line 1264, Line 627, Line 1219


Advertisement: