News:

SMF 2.1.4 has been released! Take it for a spin! Read more.

Main Menu

2.0.15: MariaDB Search

Started by StarredSkies, July 16, 2018, 03:52:20 PM

Previous topic - Next topic

StarredSkies

Hello! I've noticed that SMF has troubled concerning MariaDB search.

The error message I[admin] am receiving reads: "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 1200' at line 21." This error happens when a user selects advanced search, checks "Search in topic subjects only" and then presses search.  Checking the line that it supplies (1139) within Search.php.... it's nothing but ");" so I find that it's not the problem directly. I've looked at several other solutions, including package-related ones, for this MariaDB problem, but I have yet to find one that truly works for my problem. I am open to switching search functions/databases if need be, along with tables, as I have seen in other solutions.

What can I do to fix this?

My website is https://beastsofbeyond.com and I am using CPanel to control everything.
Here are my current packages installed:
1.   @mention members   1.1.2 
2.   SMF Staff Page   1.7 
3.   markItUp! for SMF   0.5.5     
4.   Current Signature Mod   1.2     
5.   SMF Post Prefix   2.0.3     
6.   More Genders   1.0.1     
7.   SMFShop   3.3.5     
8.   Stop Spammer   2.3.9     
9.   Posting Template SMFSimple   1.0.3     
10.   SubAccounts Mod   1.0.1     
11.   Yet Another Spoiler Mod   1.1     
12.   Ignore Topics   2.3     [
13.   Drafts   
14.   Ultimate Profile   0.9.1     
15.   BBCode with style   1.5.1     
16.   Bookmarks   2.5
17.   Yet Another YouTube BBCode Tag   4.12     
18.   Topics Created And Participated In   2.9 

Arantor

Well, the line it posts is the last line of a multi-line statement, as in all the lines immediately preceding it are also part of the problem.

What are those lines?


shawnb61

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

Arantor

First step would be to work out what query it is that is failing - not sure why albertlast added debug code when there's a perfectly good option to log the offending query directly into the error log... ;)

StarredSkies

Already attempted switching phpMyAdmin tables to  InnoDB. No results, sadly. Still going to run on InnoDB though, as it seems to be the better option for the moment.

Line 1102 to 1139:
$ignoreRequest = $smcFunc['db_search_query']('insert_log_search_results_subject',
($smcFunc['db_support_ignore'] ? '
INSERT IGNORE INTO {db_prefix}log_search_results
(id_search, id_topic, relevance, id_msg, num_matches)' : '') . '
SELECT
{int:id_search},
t.id_topic,
1000 * (
{int:weight_frequency} / (t.num_replies + 1) +
{int:weight_age} * CASE WHEN t.id_first_msg < {int:min_msg} THEN 0 ELSE (t.id_first_msg - {int:min_msg}) / {int:recent_message} END +
{int:weight_length} * CASE WHEN t.num_replies < {int:huge_topic_posts} THEN t.num_replies / {int:huge_topic_posts} ELSE 1 END +
{int:weight_subject} +
{int:weight_sticky} * t.is_sticky
) / {int:weight_total} AS relevance,
' . (empty($userQuery) ? 't.id_first_msg' : 'm.id_msg') . ',
1
FROM ' . $subject_query['from'] . (empty($subject_query['inner_join']) ? '' : '
INNER JOIN ' . implode('
INNER JOIN ', $subject_query['inner_join'])) . (empty($subject_query['left_join']) ? '' : '
LEFT JOIN ' . implode('
LEFT JOIN ', $subject_query['left_join'])) . '
WHERE ' . implode('
AND ', $subject_query['where']) . (empty($modSettings['search_max_results']) ? '' : '
LIMIT ' . ($modSettings['search_max_results'] - $numSubjectResults)),
array_merge($subject_query_params, array(
'id_search' => $_SESSION['search_cache']['id_search'],
'weight_age' => $weight['age'],
'weight_frequency' => $weight['frequency'],
'weight_length' => $weight['length'],
'weight_sticky' => $weight['sticky'],
'weight_subject' => $weight['subject'],
'weight_total' => $weight_total,
'min_msg' => $minMsg,
'recent_message' => $recentMsg,
'huge_topic_posts' => $humungousTopicPosts,
'is_approved' => 1,
))
);

albertlast

Quote from: Arantor on July 16, 2018, 04:16:26 PM
First step would be to work out what query it is that is failing - not sure why albertlast added debug code when there's a perfectly good option to log the offending query directly into the error log... ;)
Because it's look like that the query doesn't get catched and
so long we had not the generated query,
is further discussing/helping senseless.

So provide the generated query by using the debug code or by searching in the log (im realy sure that the query doesn't get log)

a10

Out of interest, have done quite a large number of search tests on my forum, 10.1.30-MariaDB-1~xenial, it's working flawlessly and fast. No index. & no members have ever complained about problems. So seems it's not inherent to all and any mariadb. Server info: https://www.one-docs.com/mysql
2.0.19, php 8.0.23, MariaDB 10.5.15. Mods: Contact Page, Like Posts, Responsive Curve, Search Focus Dropdown, Add Join Date to Post.

Arantor

Which search method are you using?

a10

#9
^^^ specially tested the one mentionned to make trouble, "Search in topic subjects only".

Am no expert (at all, just a user), reading from the server info in my post above, "default_storage_engine   InnoDB".
2.0.19, php 8.0.23, MariaDB 10.5.15. Mods: Contact Page, Like Posts, Responsive Curve, Search Focus Dropdown, Add Join Date to Post.

Arantor

No, not what I asked.

There are three principle search methods: standard, fulltext, custom. Which are you using?

a10

2.0.19, php 8.0.23, MariaDB 10.5.15. Mods: Contact Page, Like Posts, Responsive Curve, Search Focus Dropdown, Add Join Date to Post.

albertlast

i guess you need mariadb 10.2 or higher to get this issue.

Advertisement: