hello
in the smf admin panel options, you can make a custom index.
you can choose between a small, medium or large index.
on the net they say it doesn't work or not well with mysql, and SMF is based on mysql.
So why offer it?
And if I didn't understand what was said on the net, does it really bring anything, and if so, which one to choose?
Custom was long recommended over fulltext (and is *designed* for MySQL) to be faster than fulltext for searching. No-one has benchmarked it since to verify if that's still true.
Large takes more space but if you have a really large forum it will give you better results than medium - unless you're at 100k posts or more, medium is fine.
ok thanks for the information
I created an index, average because less than 100 000 topics (77 000 topics). And when I checked "custom index" in "search method" and do a search... it returns me, that I have a database error.
QuoteYou have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'LIMIT 1200' at line 21
Fichier: /home/*********/public_html/****/Sources/Search.php
Ligne: 1120
Note: Your database may need to be updated. Currently the version of the forum files is SMF 2.0.17, while your database is in SMF 2.0.3. The error reported above could perhaps be fixed by running the latest version of upgrade.php.
But I couldn't find a database update on the SMF site.
what can I do? please
there is no database update. that is a generic message which matches the current version against the database version and is incorrect, since there were no database changes.
the actual problem is MariaDB, I think.... although MariaDB most often works, technically, I don't believe that we officially support it... and sometimes MariaDB has changed just enough from MySQL to need some custom coding.
so, I have to make a modification in the search.php file?
And if yes, which one?
And if not what should I do?
First thing I would try is to remove the entry for 'Maximum results to show' under Search | Settings. Set it to 0 to see if that clears the issue.
If you are on 2.0.17, that line # doesn't make sense in Search.php. If you have any mods that alter search, I would uninstall those & try again.
SMF version?
PHP version?
MariaDB version?
Last thing I'd do is get & share the actual DB query. You can enable logging of the query under Admin | Security & Configuration | General.
Enable the setting is 'Include database query in the error log'.
Try the search again, go to your error log, & share the query here.
We have seen this issue reported a couple times over the last few years. I don't think we've gotten to the bottom of it. I actually suspect something is wrong with your Search.php somehow...
Quote from: shawnb61 on August 25, 2020, 12:57:15 PM
First thing I would try is to remove the entry for 'Maximum results to show' under Search | Settings. Set it to 0 to see if that clears the issue.
Same error
Quote from: shawnb61 on August 25, 2020, 12:57:15 PMIf you are on 2.0.17, that line # doesn't make sense in Search.php. If you have any mods that alter search, I would uninstall those & try again.
here are my installed mods.
I don't think one of them has made any modifications in the search part...
or else tell me which one, that I uninstall it to see the difference.
[spoiler]
1. Separate the sticky topics 1.0.1
2. Enhancements to purge inactive members 1.3.8
3. Tidy Child Boards 2.0
4. Space Between Posts 2.0
5. Force Read Unread Pms 1.0
6. SimpleColorizer 1.1
7. Say Thanks 1.3.6
8. Quick Spoiler 1.2.5
9. Dougiefresh's Reorganized Admin Area 1.3
10. Users Online Today 2.1
11. Yet Another YouTube BBCode Tag 4.14
12. Profile Moderator Management 2.0
13. Invitation System 1.3
14. Alert v.1.0
15. BBCode with style 1.5.1
16. Hide Content 1.1.4
17. SMFPacks Shoutbox Pro 1.4.6
18. 6 Custom buttons / tabs with Sub Menus (4) 0.5
19. SMF Post Prefix 2.1.1
20. SimplePortal 2.3.7
21. SP-Shoutbox Options 1.74
[/spoiler]
Quote from: shawnb61 on August 25, 2020, 12:57:15 PMSMF version?
SMF 2.0.17
Quote from: shawnb61 on August 25, 2020, 12:57:15 PMPHP version?
PHP 7.3
Quote from: shawnb61 on August 25, 2020, 12:57:15 PMMariaDB version?
MariaDB 10.1.46
Quote from: shawnb61 on August 25, 2020, 01:08:27 PM
Last thing I'd do is get & share the actual DB query. You can enable logging of the query under Admin | Security & Configuration | General.
Enable the setting is 'Include database query in the error log'.
Try the search again, go to your error log, & share the query here.
here is the result found in the error log
[spoiler]Error type: database
https://******/index.php?action=search2
Database error: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'LIMIT 6000' at line 23
INSERT IGNORE INTO smf_log_search_results
(id_search, id_topic, relevance, id_msg, num_matches)
SELECT
17,
t.id_topic,
1000 * (
15 / (t.num_replies + 1) +
0 * CASE WHEN t.id_first_msg < 539465 THEN 0 ELSE (t.id_first_msg - 539465) / 231200 END +
0 * CASE WHEN t.num_replies < 200 THEN t.num_replies / 200 ELSE 1 END +
55 +
0 * t.is_sticky
) / 100 AS relevance,
t.id_first_msg,
1
FROM smf_topics AS t
INNER JOIN smf_log_search_subjects AS subj1 ON (subj1.id_topic = t.id_topic)
INNER JOIN smf_log_search_subjects AS subj2 ON (subj2.id_topic = subj1.id_topic)
WHERE t.approved = 1
AND subj1.word LIKE '%top%'
AND subj2.word LIKE '%gun%'
AND t.id_board != 49
AND t.id_prefix =
LIMIT 6000
Fichier: /home/******/public_html/*****/Sources/Search.php
Ligne: 1120 [/spoiler]
Quote from: grandseb59 on August 25, 2020, 01:30:54 PM
AND t.id_prefix =
LIMIT 6000
THAT's a problem...
Could you share your version of Search.php?
my version of Search.php is : @version 2.0.9
Seems like the post prefix mod might have a bug.
Quote from: Arantor on August 25, 2020, 01:55:58 PM
Seems like the post prefix mod might have a bug.
Yes, it introduces that line in the search query.
This needs an entry over in that mod's support thread:
https://www.simplemachines.org/community/index.php?topic=536401.0
grandseb - I suggest you followup over in the support thread for SMF Post Prefix, link above.
I removed 2 mods:
"Profile Moderator Management"
because I had forgotten to remove it because it was unusable with SMF 2.0.17 but it doesn't seem to interfere with this problem and the "SMF post prefix".
And now the search works again, without errors.
but I'm going to follow carefully the subject on the mod prefix because I'm very interested in it.
On the other hand I tried the same search on my production forum where I didn't create a custom index... and I don't see any difference ... is it really useful to make a custom index?
For info on my forum it is:
Total posts:273850
Total subjects:76696
thank you very much for your help ;)
plop
Yes, yes it is; beyond 100k posts you definitely need something. At 250k plus, we tend to suggest Sphinx but it's not easy to set up.
ok thank you very much :)