Advertisement:

Author Topic: Database Error on Advanced Search attempt.  (Read 1047 times)

Offline landyvlad

  • Full Member
  • ***
  • Posts: 604
    • Michael Reed on Facebook
    • GSX1400 Owners ORG
Database Error on Advanced Search attempt.
« 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?
 
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.

To paraphrase Kindred: "There are no technical solutions to social problems."

Offline GigaWatt

  • The Smiley Guy
  • Support Specialist
  • Sophist Member
  • *
  • Posts: 1,148
  • Gender: Male
    • Macedonian electronics forum
Re: Database Error on Advanced Search attempt.
« Reply #1 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 ;).
"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?"..."

Offline landyvlad

  • Full Member
  • ***
  • Posts: 604
    • Michael Reed on Facebook
    • GSX1400 Owners ORG
Re: Database Error on Advanced Search attempt.
« Reply #2 on: July 08, 2018, 10:20:14 PM »
Sooo, how do I fix it so that the search function will work again? Any ideas?
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.

To paraphrase Kindred: "There are no technical solutions to social problems."

Offline GigaWatt

  • The Smiley Guy
  • Support Specialist
  • Sophist Member
  • *
  • Posts: 1,148
  • Gender: Male
    • Macedonian electronics forum
Re: Database Error on Advanced Search attempt.
« Reply #3 on: July 09, 2018, 06:44:51 AM »
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?"..."

Offline a10

  • Charter Member
  • Sr. Member
  • *
  • Posts: 797
Re: Database Error on Advanced Search attempt.
« Reply #4 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
2.0.15, php 7.0.29, ssl. Mods: Contact Page, Like Posts, Responsive Curve, Search Focus Dropdown.

Offline Kindred

  • The Mean One
  • Support Specialist
  • SMF Legend
  • *
  • Posts: 56,942
  • Gender: Male
    • Kindred-999 on GitHub
Re: Database Error on Advanced Search attempt.
« Reply #5 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

Please do not PM, IM or Email me with support questions.  You will get better and faster responses in the support boards.  Thank you.

Offline landyvlad

  • Full Member
  • ***
  • Posts: 604
    • Michael Reed on Facebook
    • GSX1400 Owners ORG
Re: Database Error on Advanced Search attempt.
« Reply #6 on: July 09, 2018, 09:43:33 PM »
Will do mate, hopefully tonight Aussie time :)
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.

To paraphrase Kindred: "There are no technical solutions to social problems."

Offline landyvlad

  • Full Member
  • ***
  • Posts: 604
    • Michael Reed on Facebook
    • GSX1400 Owners ORG
Re: Database Error on Advanced Search attempt.
« Reply #7 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
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.

To paraphrase Kindred: "There are no technical solutions to social problems."

Offline GigaWatt

  • The Smiley Guy
  • Support Specialist
  • Sophist Member
  • *
  • Posts: 1,148
  • Gender: Male
    • Macedonian electronics forum
Re: Database Error on Advanced Search attempt.
« Reply #8 on: July 10, 2018, 08:52:18 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?"..."

Offline Kindred

  • The Mean One
  • Support Specialist
  • SMF Legend
  • *
  • Posts: 56,942
  • Gender: Male
    • Kindred-999 on GitHub
Re: Database Error on Advanced Search attempt.
« Reply #9 on: July 10, 2018, 09:00:24 AM »
Well, the error message clearly indicates that you have MariaDB. :P

Please do not PM, IM or Email me with support questions.  You will get better and faster responses in the support boards.  Thank you.

Offline Illori

  • Project Manager
  • SMF Master
  • *
  • Posts: 49,709
Re: Database Error on Advanced Search attempt.
« Reply #10 on: July 10, 2018, 09:23:07 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.

Offline landyvlad

  • Full Member
  • ***
  • Posts: 604
    • Michael Reed on Facebook
    • GSX1400 Owners ORG
Re: Database Error on Advanced Search attempt.
« Reply #11 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
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.

To paraphrase Kindred: "There are no technical solutions to social problems."

Offline Kindred

  • The Mean One
  • Support Specialist
  • SMF Legend
  • *
  • Posts: 56,942
  • Gender: Male
    • Kindred-999 on GitHub
Re: Database Error on Advanced Search attempt.
« Reply #12 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...
Please do not PM, IM or Email me with support questions.  You will get better and faster responses in the support boards.  Thank you.

Offline shawnb61

  • Support Specialist
  • Sr. Member
  • *
  • Posts: 901
    • sbulen on GitHub
Re: Database Error on Advanced Search attempt.
« Reply #13 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
Address the process rather than the outcome.  Then, the outcome becomes more likely.   - Fripp

Offline landyvlad

  • Full Member
  • ***
  • Posts: 604
    • Michael Reed on Facebook
    • GSX1400 Owners ORG
Re: Database Error on Advanced Search attempt.
« Reply #14 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?
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.

To paraphrase Kindred: "There are no technical solutions to social problems."

Offline shawnb61

  • Support Specialist
  • Sr. Member
  • *
  • Posts: 901
    • sbulen on GitHub
Re: Database Error on Advanced Search attempt.
« Reply #15 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. 
Address the process rather than the outcome.  Then, the outcome becomes more likely.   - Fripp

Offline shawnb61

  • Support Specialist
  • Sr. Member
  • *
  • Posts: 901
    • sbulen on GitHub
Re: Database Error on Advanced Search attempt.
« Reply #16 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...)
Address the process rather than the outcome.  Then, the outcome becomes more likely.   - Fripp

Offline shawnb61

  • Support Specialist
  • Sr. Member
  • *
  • Posts: 901
    • sbulen on GitHub
Re: Database Error on Advanced Search attempt.
« Reply #17 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...
Address the process rather than the outcome.  Then, the outcome becomes more likely.   - Fripp

Online albertlast

  • Development Contributor
  • Jr. Member
  • *
  • Posts: 327
Re: Database Error on Advanced Search attempt.
« Reply #18 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.

Offline landyvlad

  • Full Member
  • ***
  • Posts: 604
    • Michael Reed on Facebook
    • GSX1400 Owners ORG
Re: Database Error on Advanced Search attempt.
« Reply #19 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?
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.

To paraphrase Kindred: "There are no technical solutions to social problems."