News:

Wondering if this will always be free?  See why free is better.

Main Menu

Database Error on Advanced Search attempt.

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

Previous topic - Next topic

landyvlad

The basic search function is working fine.

I went to do an advanced search, searching topic titles only, and got the following 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 20
File: /home/gsxowner/public_html/forum/Sources/Search.php
Line: 1122

Note: It appears that your database may require an upgrade. Your forum's files are currently at version SMF 2.0.15 while your database is at version 2.0.13. The above error might possibly go away if you execute the latest version of upgrade.php

I'm confused by this as upgrades have been done in the usual manner, nothing fancy or different in that respect.

Not sure what they mean about checking the manual for the MariaDB server? What manual is that?

I can't access my cpanel at the moment (work computer blocks it) so can't state what mariadb version etc is running.

But I thought I post this as a starting point - someone may be able to shed some light?
"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.

GigaWatt

I don't think SMF officially supports MariaDB.

Basically, that error means that MariaDB doesn't understand what SMF is trying to pull from the database. Why? Because the syntax is wrong... and it's just telling you to correct the syntax in order to pull the data from the database... that's what that "please refer to the manual for the blah blah blah" means ;).

And about the database version, don't worry about that. SMF upgrades for 2.0.x never upgrade the version of the database since ever since 2.0 came out, there are no database changes (the structure is the same). It's "stuck" on 2.0.13 because that's probably the first version of 2.0.x you had running ;).
"This is really a generic concept about human thinking - when faced with large tasks we're naturally inclined to try to break them down into a bunch of smaller tasks that together make up the whole."

"A 500 error loosely translates to the webserver saying, "WTF?"..."

landyvlad

Sooo, how do I fix it so that the search function will work again? Any ideas?
"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.

GigaWatt

You could change your database engine (based on this post), either to MyISAM or InnoDB, but personally, I've never done it myself.
"This is really a generic concept about human thinking - when faced with large tasks we're naturally inclined to try to break them down into a bunch of smaller tasks that together make up the whole."

"A 500 error loosely translates to the webserver saying, "WTF?"..."

a10

Quote from: GigaWatt on July 08, 2018, 09:40:32 PM
I don't think SMF officially supports MariaDB.

Interesting to know more about this, have been on what the hosts labels MariaDB for 'an eternity', working perfect and never any problems (search is also fine).

They also list this under 'Sorage Engines':

Storage Engine    Description
CSV    CSV storage engine
MRG_MyISAM    Collection of identical MyISAM tables
SEQUENCE    Generated tables filled with sequential values
MyISAM    MyISAM storage engine
MEMORY    Hash based, stored in memory, useful for temporary tables
Aria    Crash-safe tables with MyISAM heritage
PERFORMANCE_SCHEMA    Performance Schema
InnoDB    Percona-XtraDB, Supports transactions, row-level locking, foreign keys and encryption for tables
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.

Kindred

MariaDB has some odd quirks --   for the most part, it mirrors mySQL and is a 1:1 replacement.

In some cases -- maybe based on MariaDB version or host configuration, it appears that the replacement is no longer 1:1

SMF, at the moment, does not officially support MariaDB. (in that we do not claim that it will work, nor do we claim that it will not work)


That being said...
landyvlad....  you know the shtick.
Please list the lines of code from around the line referenced in the error

Сл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."

landyvlad

Will do mate, hopefully tonight Aussie time :)
"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

First - I've checked and the database is MySQL, not MariaDB

Secondly - Here's the relevant section from File: /home/gsxowner/public_html/forum/Sources/Search.php


$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,
))
);

// If the database doesn't support IGNORE to make this


line 1122 is the expression  ); at the bottom of that block of code.


I've attached the file for easy reference.

Thanks
"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.

GigaWatt

Quote from: landyvlad on July 10, 2018, 08:24:49 AM
First - I've checked and the database is MySQL, not MariaDB



Are you sure it's not MyISAM?
"This is really a generic concept about human thinking - when faced with large tasks we're naturally inclined to try to break them down into a bunch of smaller tasks that together make up the whole."

"A 500 error loosely translates to the webserver saying, "WTF?"..."

Kindred

Well, the error message clearly indicates that you have MariaDB. :P

Сл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: GigaWatt on July 10, 2018, 08:52:18 AM
Quote from: landyvlad on July 10, 2018, 08:24:49 AM
First - I've checked and the database is MySQL, not MariaDB



Are you sure it's not MyISAM?

all that means is the storage engine of the database, not which type of database is being used. MariaDB should have the same options as it should be a drop in replacement for MySQL.

landyvlad

Well on my host cpanel it has a databases section. If I click on MySQL it has the smf database in there.
There are no options that I can see for other database types.

see snips attached
"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.

Kindred

They may call it mySQL, but the error message clearly states MariaDB (I'm inclined to trust the error message)


from a rough glance, it appears that the error is in this code

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,
))


Thing is.....  that matches unmodified SMF code....
That being said, you obviously have some mods installed since that end line is 1108 in the original...
Сл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."

shawnb61

Our problems with MariaDB have fallen into 2 areas.  First, that their version #s are now different. So normally when we have logic to see if a function is supported, e.g., testing if fulltext searches are supported for InnoDB, we look for MySQL DB version 5.6.  But it might be version 10.2 in MariaDB, so our compares won't work going forward.  We may need to code around that in the future, due to the popularity of MariaDB.   

The second major issue with MariaDB is that some installations (not all) consider many SMF column names to be keywords.  The only way to make them work is to enclose them in quotes.  This affects some MariaDB installations, not all.  We've never been able to drill down on exactly why some do & some don't - a particular extension, a particular build, whatever.   But certain column names need to be in quotes.  Here is an example where MariaDB wanted any column named value to be in quotes, e.g., 'value':
   https://www.simplemachines.org/community/index.php?topic=557046.msg3947614#msg3947614

My first suspicion is that this is the problem here.  You would need to find the offending term & enclose it in quotes.


Pretty interesting history:
https://en.wikipedia.org/wiki/MariaDB
Address the process rather than the outcome.  Then, the outcome becomes more likely.   - Fripp

landyvlad

Hmm, very confusing.

I haven't changed any mods between when search was working, and wasn't. (but yes there are a number of mods in place which could well explain the difference in line numbers).

I've logged a ticket with my host asking them to confirm precisely what database type and version  is in use, as a starting point for further discussion.

Thanks

Oh as an aside (but a related one) is it possible (and practical / useful) to change a database from MariaDB to MySQL, assuming the latter were available?
"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

Quote from: landyvlad on July 10, 2018, 07:50:47 PM
I've logged a ticket with my host asking them to confirm precisely what database type and version  is in use, as a starting point for further discussion.

Oh as an aside (but a related one) is it possible (and practical / useful) to change a database from MariaDB to MySQL, assuming the latter were available?

I would also ask them if they have applied any MariaDB patches recently (e.g., security, whatever...).  If so, details would be very helpful...   

If you could drop in MySQL, it would be better supported by SMF for the reasons specified above. 
Address the process rather than the outcome.  Then, the outcome becomes more likely.   - Fripp

shawnb61

Also, could you provide the full version information provided under Admin | Support and Credits? 

It would be helpful to document the exact MariaDB version here. 

(FYI, MariaDB published a bunch of patches late June 2018...)
Address the process rather than the outcome.  Then, the outcome becomes more likely.   - Fripp

shawnb61

Of course, MariaDB my be a red herring here...  There could be something wrong with $subject_query values...

If there were a way to see the SQL query log, or error log, it would be easier to spot what it's complaining about...
Address the process rather than the outcome.  Then, the outcome becomes more likely.   - Fripp

albertlast

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.

landyvlad

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?
"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.

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: