Database Error on Advanced Search attempt.

Started by landyvlad, July 08, 2018, 09:08:43 PM

Previous topic - Next topic

a10

Version on my forum, no search problems (or any other errors).

Database Server: MariaDB
MySQL version: 10.1.30-MariaDB-1~xenial
PHP: 7.0.29
Server version: Apache

Maybe differences between 10.1.30 & 10.2.15, and host upgraded lately ?
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.

shawnb61

Quote from: landyvlad on July 11, 2018, 02:46:03 AM
OK well my host has responded and it is indeed Maria.

QuoteThe server is running MariaDB 10.2.15

the full version information provided under Admin | Support and Credits is -

QuoteForum version: SMF 2.0.15 (more detailed)
Current SMF version: SMF 2.0.15
GD version: bundled (2.1.0 compatible)
Database server: MariaDB
MySQL version: 10.2.15-MariaDB-cll-lve
PHP: 5.6.36
Server version: LiteSpeed


Does this help as far as the troubleshooting is concerned?

It may explain a change in behavior.  That version was released on 5/18/18.  So it was patched very recently.

https://downloads.mariadb.org/mariadb/+releases/

It would be very helpful to see the sql.  Could you try albertlast's suggestion above?
Address the process rather than the outcome.  Then, the outcome becomes more likely.   - Fripp

landyvlad

OK will do, when I get the opportunity (tonight hopefully). Ta.



Having said that I've just checked and the search appears to be working OK again. Odd.
I'll check again later and see if its working later.
"Put as much effort into your question as you'd expect someone to give in an answer"

Please do not PM, IM or Email me with questions on astrophysics or theology.  You will get better and faster responses by asking homeless people in the street. Thank you.

Be the person your dog thinks you are.

shawnb61

What version of MariaDB?

(It was patched again recently...  If you are now ar 10.2.16, then I think all is explained....)
Address the process rather than the outcome.  Then, the outcome becomes more likely.   - Fripp

landyvlad

Admin - Support and Credits still states "MySQL version: 10.2.15-MariaDB-cll-lve"
"Put as much effort into your question as you'd expect someone to give in an answer"

Please do not PM, IM or Email me with questions on astrophysics or theology.  You will get better and faster responses by asking homeless people in the street. Thank you.

Be the person your dog thinks you are.

landyvlad

and the host confirms that version is correct they have not yet updated.

Search is still working fine.  VERY odd. 

I'll mark this as resolved but come back to it with more details on the error, as per albertlasts's suggestion, if it comes up again.

Thanks all.
"Put as much effort into your question as you'd expect someone to give in an answer"

Please do not PM, IM or Email me with questions on astrophysics or theology.  You will get better and faster responses by asking homeless people in the street. Thank you.

Be the person your dog thinks you are.

Gandrel

Quote from: albertlast on July 11, 2018, 12:14:43 AM
It very important to get the real sql query,
for this you could do this:
Go to Subs-Db-mysql.php,
search for:

if ($ret === false && empty($db_values['db_error_skip']))
$ret = smf_db_error($db_string, $connection);

Should be around line 392 and replace this with:

if ($ret === false && empty($db_values['db_error_skip']))
{
var_dump($db_string);
$ret = smf_db_error($db_string, $connection);
}

On the next error the complet sql query appears.

Mariadb shouldn't be the root of the error.

Sorry to hijack this thread, but it sounds like this didn't really get resolved and I'm having the same problem when I do an advanced search as well. I tried the above suggestion to see if I can get some more info out of the error, but it produced the exact same error as before I made the edit to Subs-Db-mysql.php:
https://outerrimgarrison.com/forums/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 1200' at line 20

File: /home/outerrim/public_html/forums/Sources/Search.php

Line: 1120


It's also throwing this error at the same time, for what it's worth:
https://outerrimgarrison.com/forums/index.php?action=search2

8: Undefined index: prefix

File: /home/outerrim/public_html/forums/Sources/Search.php

Line: 1069


Anyone have any other thoughts?

This is my server setup:
cPanel Version   84.0 (build 19)
Apache Version   2.4.41
PHP Version   7.2.25
MySQL Version   10.1.43-MariaDB
Architecture   x86_64
Operating System   linux
Path to Sendmail   /usr/sbin/sendmail
Path to Perl   /usr/bin/perl
Perl Version   5.10.1
Kernel Version   2.6.32-754.24.3.el6.x86_64

I'm running SMF 2.0.15 with PHP 7.1 and the SunRise theme.

Gandrel

Quote from: albertlast on July 11, 2018, 12:14:43 AM
It very important to get the real sql query,
for this you could do this:
Go to Subs-Db-mysql.php,
search for:

if ($ret === false && empty($db_values['db_error_skip']))
$ret = smf_db_error($db_string, $connection);

Should be around line 392 and replace this with:

if ($ret === false && empty($db_values['db_error_skip']))
{
var_dump($db_string);
$ret = smf_db_error($db_string, $connection);
}

On the next error the complet sql query appears.

Mariadb shouldn't be the root of the error.
While looking around some of the admin settings, I came across "Include database query in the error log" under Admin > Security and Moderation > General and this finally got it to spit out more info in the error log!:

INSERT IGNORE INTO smf_log_search_results
(id_search, id_topic, relevance, id_msg, num_matches)
SELECT
31,
t.id_topic,
1000 * (
30 / (t.num_replies + 1) +
25 * CASE WHEN t.id_first_msg < 36110 THEN 0 ELSE (t.id_first_msg - 36110) / 15477 END +
20 * CASE WHEN t.num_replies < 200 THEN t.num_replies / 200 ELSE 1 END +
15 +
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)
WHERE subj1.word LIKE '%patch%'
AND t.id_board IN (21)
AND t.id_prefix =
LIMIT 1200

Advertisement: