Simple Machines Community Forum

SMF Support => SMF 2.0.x Support => Topic started by: landyvlad on July 08, 2018, 09:08:43 PM

Title: Database Error on Advanced Search attempt.
Post by: landyvlad on July 08, 2018, 09:08:43 PM
The basic search function is working fine.

I went to do an advanced search, searching topic titles only, and got the following error.

Quote
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/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?
 
Title: Re: Database Error on Advanced Search attempt.
Post by: GigaWatt on July 08, 2018, 09:40:32 PM
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 ;).
Title: Re: Database Error on Advanced Search attempt.
Post by: landyvlad on July 08, 2018, 10:20:14 PM
Sooo, how do I fix it so that the search function will work again? Any ideas?
Title: Re: Database Error on Advanced Search attempt.
Post by: GigaWatt on July 09, 2018, 06:44:51 AM
You could change your database engine (based on this (https://www.simplemachines.org/community/index.php?topic=560209.msg3979698#msg3979698) post), either to MyISAM or InnoDB, but personally, I've never done it myself.
Title: Re: Database Error on Advanced Search attempt.
Post by: a10 on July 09, 2018, 07:29:22 AM
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
Title: Re: Database Error on Advanced Search attempt.
Post by: Kindred on July 09, 2018, 08:42:05 AM
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

Title: Re: Database Error on Advanced Search attempt.
Post by: landyvlad on July 09, 2018, 09:43:33 PM
Will do mate, hopefully tonight Aussie time :)
Title: Re: Database Error on Advanced Search attempt.
Post by: landyvlad on July 10, 2018, 08:24:49 AM
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

Code: [Select]
$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
Title: Re: Database Error on Advanced Search attempt.
Post by: GigaWatt on July 10, 2018, 08:52:18 AM
First - I've checked and the database is MySQL, not MariaDB

(https://s20.postimg.cc/dz149exal/SHOT0033.png)

Are you sure it's not MyISAM?
Title: Re: Database Error on Advanced Search attempt.
Post by: Kindred on July 10, 2018, 09:00:24 AM
Well, the error message clearly indicates that you have MariaDB. :P

Title: Re: Database Error on Advanced Search attempt.
Post by: Illori on July 10, 2018, 09:23:07 AM
First - I've checked and the database is MySQL, not MariaDB

(https://s20.postimg.cc/dz149exal/SHOT0033.png)

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.
Title: Re: Database Error on Advanced Search attempt.
Post by: landyvlad on July 10, 2018, 09:48:52 AM
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
Title: Re: Database Error on Advanced Search attempt.
Post by: Kindred on July 10, 2018, 10:22:31 AM
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
Code: [Select]
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...
Title: Re: Database Error on Advanced Search attempt.
Post by: shawnb61 on July 10, 2018, 10:47:42 AM
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
Title: Re: Database Error on Advanced Search attempt.
Post by: landyvlad on July 10, 2018, 07:50:47 PM
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?
Title: Re: Database Error on Advanced Search attempt.
Post by: shawnb61 on July 10, 2018, 11:04:33 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. 
Title: Re: Database Error on Advanced Search attempt.
Post by: shawnb61 on July 10, 2018, 11:14:16 PM
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...)
Title: Re: Database Error on Advanced Search attempt.
Post by: shawnb61 on July 10, 2018, 11:50:45 PM
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...
Title: Re: Database Error on Advanced Search attempt.
Post by: 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:
Code: [Select]
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:
Code: [Select]
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.
Title: Re: Database Error on Advanced Search attempt.
Post by: landyvlad on July 11, 2018, 02:46:03 AM
OK well my host has responded and it is indeed Maria.

Quote
The server is running MariaDB 10.2.15

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

Quote
Forum 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?
Title: Re: Database Error on Advanced Search attempt.
Post by: a10 on July 11, 2018, 03:24:39 AM
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 ?
Title: Re: Database Error on Advanced Search attempt.
Post by: shawnb61 on July 11, 2018, 08:38:43 AM
OK well my host has responded and it is indeed Maria.

Quote
The server is running MariaDB 10.2.15

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

Quote
Forum 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?
Title: Re: Database Error on Advanced Search attempt.
Post by: landyvlad on July 11, 2018, 08:00:01 PM
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.
Title: Re: Database Error on Advanced Search attempt.
Post by: shawnb61 on July 11, 2018, 10:13:43 PM
What version of MariaDB?

(It was patched again recently...  If you are now ar 10.2.16, then I think all is explained....)
Title: Re: Database Error on Advanced Search attempt.
Post by: landyvlad on July 11, 2018, 10:53:34 PM
Admin - Support and Credits still states "MySQL version: 10.2.15-MariaDB-cll-lve"
Title: Re: Database Error on Advanced Search attempt.
Post by: landyvlad on July 12, 2018, 05:39:32 AM
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.