[Solved] Search not working in wpforo - DOUBLE value is out of range

Hi,

Was this ever resolved?

I have the exact same issue. This is the query that fails when you try to search the forum:

SELECT p.`postid`, t.`topicid`, t.`private`, t.`status`, t.`forumid`, p.`userid`, t.`title`, p.`created`, p.`body`, MATCH(t.`title`) AGAINST('discussion*' IN BOOLEAN MODE) + MATCH(p.`title`) AGAINST('discussion*' IN BOOLEAN MODE) + MATCH(p.`body`) AGAINST('discussion*' IN BOOLEAN MODE) AS matches FROM `wp_wpforo_posts` p INNER JOIN `wp_wpforo_topics` t ON t.`topicid` = p.`topicid` WHERE ( MATCH(t.`title`) AGAINST('discussion*' IN BOOLEAN MODE) OR MATCH(p.`title`, p.`body`) AGAINST('discussion*' IN BOOLEAN MODE) ) ORDER BY MATCH(t.`title`) AGAINST('discussion*') + MATCH(p.`title`) AGAINST('discussion*') + MATCH(p.`body`) AGAINST('discussion*') DESC, MATCH(t.`title`) AGAINST('discussion*' IN BOOLEAN MODE) + MATCH(p.`title`) AGAINST('discussion*' IN BOOLEAN MODE) + MATCH(p.`body`) AGAINST('discussion*' IN BOOLEAN MODE) DESC LIMIT 15

Server is being hosted on DigitalOcean and managed by ServerPilot. It's using MySQL 5.7. Here are screenshots of the two tables used in the above query:

http://d.pr/i/35DP/22YJFvLs

http://d.pr/i/mwEI/2ooareSy

Maybe it's the Collation/Encoding used in the table?

Unfortunately, switching themes does not resolve this issue for us.

Warning: mysqli_query(): (22003/1690): DOUBLE value is out of range in '((match `wp-original`.`t`.`title` against ('discuss*' in boolean mode)) + (match `wp-original`.`p`.`title` against ('discuss*' in boolean mode)))' in /var/www/public/wp-includes/wp-db.php on line 1877

WordPress database error: [DOUBLE value is out of range in '((match `wp-original`.`t`.`title` against ('discuss*' in boolean mode)) + (match `wp-original`.`p`.`title` against ('discuss*' in boolean mode)))']

SELECT p.`postid`, t.`topicid`, t.`private`, t.`status`, t.`forumid`, p.`userid`, t.`title`, p.`created`, p.`body`, MATCH(t.`title`) AGAINST('discuss*' IN BOOLEAN MODE) + MATCH(p.`title`) AGAINST('discuss*' IN BOOLEAN MODE) + MATCH(p.`body`) AGAINST('discuss*' IN BOOLEAN MODE) AS matches FROM `wp_wpforo_posts` p INNER JOIN `wp_wpforo_topics` t ON t.`topicid` = p.`topicid` WHERE ( MATCH(t.`title`) AGAINST('discuss*' IN BOOLEAN MODE) OR MATCH(p.`title`, p.`body`) AGAINST('discuss*' IN BOOLEAN MODE) ) ORDER BY MATCH(t.`title`) AGAINST('discuss*') + MATCH(p.`title`) AGAINST('discuss*') + MATCH(p.`body`) AGAINST('discuss*') DESC, MATCH(t.`title`) AGAINST('discuss*' IN BOOLEAN MODE) + MATCH(p.`title`) AGAINST('discuss*' IN BOOLEAN MODE) + MATCH(p.`body`) AGAINST('discuss*' IN BOOLEAN MODE) DESC LIMIT 15

In case anyone comes across this issue, I resolved it by changing the Encoding for the following two tables from utf8mb4 to utf8, and Collation from utf8mb4_unicode_520_ci to utf8_general_ci. 

We are running MySQL  5.7.19. The issue seems to be related to using Boolean mode on utf8mb4 encoded tables with columns using utf8mb4_unicode_520_ci collation.

HTH

You can change it in website cPanel > phpMyAdmin database manager:

thank you!