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
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?
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.
the undefined index issues are always due to a badly designed mod.
Lainaus käyttäjältä: Kindred - tammikuu 27, 2020, 08:47:48 AP
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.
Lainaus käyttäjältä: shawnb61 - tammikuu 27, 2020, 12:41:54 AP
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
Lainaus käyttäjältä: shawnb61 - tammikuu 27, 2020, 05:42:39 IP
Lainaus käyttäjältä: shawnb61 - tammikuu 27, 2020, 12:41:54 AP
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
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.
Thank you for the pictures! That really helps.
Lainaus käyttäjältä: shawnb61 - tammikuu 28, 2020, 12:31:40 AP
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.
Lainaa
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.
Lainaa
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?
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?
Lainaus käyttäjältä: shawnb61 - tammikuu 28, 2020, 05:03:50 IP
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.
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.
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.
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,
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
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!
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?
Lainaus käyttäjältä: shawnb61 - tammikuu 31, 2020, 02:04:38 IP
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,
Ok, just to confirm: Are you still getting the undefined index errors for title & xref?
Lainaus käyttäjältä: shawnb61 - helmikuu 22, 2020, 09:14:30 IP
Ok, just to confirm: Are you still getting the undefined index errors for title & xref?
So far I haven't seen them! Yay!
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
Thanks for following up!
The Subs-Post errors for codes like 'utf8' and 'server' are known, & are in the Bug Reports board. We believe these are harmless, just a nuisance:
https://www.simplemachines.org/community/index.php?topic=546745.00
The original errors for 'title' and 'href' when action=search appear to be gone - and that's a good thing.
If you want to stop the errors, just define the indexes (indices?) -
Subs-Post.php
/// Fudge to remove undefined errors from scheduled tasks
if (!isset($context['server']))
$context['server'] = null;
// This line makes all languages *theoretically* work even with the wrong charset ;).
$message = preg_replace('~&#(\d{4,5}|[2-9]\d{2,4}|1[2-9]\d);~', '&#$1;', $message);
/// Fudge to remove undefined errors from scheduled tasks
if (!isset($context['server']))
$context['server'] = null;
// Use sendmail if it's set or if no SMTP server is set.
$use_sendmail = empty($modSettings['mail_type']) || $modSettings['smtp_host'] == '';
/// Fudge to remove undefined errors from scheduled tasks
if (!isset($context['character_set']))
$context['character_set'] = null;
$charset = $custom_charset !== null ? $custom_charset : $context['character_set'];
/// Fix undefined index error
if (!isset ($context['utf8']))
$context['utf8'] = '';
// Convert all special characters to HTML entities...just for Hotmail :-\
if ($hotmail_fix && ($context['utf8'] || function_exists('iconv') || $context['character_set'] === 'ISO-8859-1'))
Cool. I'm glad it's mostly solved then. Thank you for the code! I'll report back success/failure.
On a side note, I seem to get a whole lot of people having log in problems (forgetting their passwords)...out of proportion to the number of active members on the forum. I thought that this last update fixed the log in issue? Could there be another reason why so many people have to enter their passwords multiple times to log in?
And I got a new error today, but it's only one user generating this error (although 7 times!), and I've never seen it before. I have everyone set to the default Curve theme, I stopped using themes because of all the weird errors, and I've confirmed that this user is set to default. So maybe it's just a matter of time before other users start generating errors?
http://www.mysite.com/smf/index.php?action=logout;a0ebafcb2=ea609e0a1505dd5142c82766cbf8c855
8: Undefined index: actual_theme_dir
File: /home/public/smf/Sources/Load.php
Line: 2139
Lainaus käyttäjältä: anidea - maaliskuu 15, 2020, 12:08:33 IP
Could there be another reason why so many people have to enter their passwords multiple times to log in?
There is definitely an issue with folks who have multi-byte characters in their passwords. Bug reported here:
https://www.simplemachines.org/community/index.php?topic=571384.0
There is a temporary code workaround here:
https://www.simplemachines.org/community/index.php?topic=571082.msg4042818#msg4042818
Fix definitely coming in 2.0.18. Before applying 2.0.18 you will need to undo the temporary fix.