From f9b9d659a1638389e6250110ce54d18e9d9bd59c Mon Sep 17 00:00:00 2001 From: rxu Date: Sat, 4 Nov 2017 19:14:04 +0700 Subject: [ticket/15224] Fix some MySQL fulltext index searching errors PHPBB3-15224 --- phpBB/phpbb/search/fulltext_mysql.php | 19 +++++++++++++++++-- 1 file changed, 17 insertions(+), 2 deletions(-) (limited to 'phpBB/phpbb/search/fulltext_mysql.php') diff --git a/phpBB/phpbb/search/fulltext_mysql.php b/phpBB/phpbb/search/fulltext_mysql.php index da1aad1c3a..c8df1951e3 100644 --- a/phpBB/phpbb/search/fulltext_mysql.php +++ b/phpBB/phpbb/search/fulltext_mysql.php @@ -591,6 +591,7 @@ class fulltext_mysql extends \phpbb\search\base WHERE MATCH ($sql_match) AGAINST ('" . $this->db->sql_escape(htmlspecialchars_decode($this->search_query)) . "' IN BOOLEAN MODE) $sql_where_options ORDER BY $sql_sort"; + $this->db->sql_return_on_error(true); $result = $this->db->sql_query_limit($sql, $this->config['search_block_size'], $start); while ($row = $this->db->sql_fetchrow($result)) @@ -602,7 +603,7 @@ class fulltext_mysql extends \phpbb\search\base $id_ary = array_unique($id_ary); // if the total result count is not cached yet, retrieve it from the db - if (!$result_count) + if (!$result_count && count($id_ary)) { $sql_found_rows = 'SELECT FOUND_ROWS() as result_count'; $result = $this->db->sql_query($sql_found_rows); @@ -1004,6 +1005,11 @@ class fulltext_mysql extends \phpbb\search\base } } + if (!isset($this->stats['post_text'])) + { + $this->db->sql_query('ALTER TABLE ' . POSTS_TABLE . ' ADD FULLTEXT post_text (post_text)'); + } + $this->db->sql_query('TRUNCATE TABLE ' . SEARCH_RESULTS_TABLE); return false; @@ -1039,6 +1045,11 @@ class fulltext_mysql extends \phpbb\search\base $alter[] = 'DROP INDEX post_content'; } + if (isset($this->stats['post_text'])) + { + $alter[] = 'DROP INDEX post_text'; + } + if (sizeof($alter)) { $this->db->sql_query('ALTER TABLE ' . POSTS_TABLE . ' ' . implode(', ', $alter)); @@ -1059,7 +1070,7 @@ class fulltext_mysql extends \phpbb\search\base $this->get_stats(); } - return isset($this->stats['post_subject']) && isset($this->stats['post_content']); + return isset($this->stats['post_subject']) && isset($this->stats['post_content']) && isset($this->stats['post_text']); } /** @@ -1103,6 +1114,10 @@ class fulltext_mysql extends \phpbb\search\base { $this->stats['post_subject'] = $row; } + else if ($row['Key_name'] == 'post_text') + { + $this->stats['post_text'] = $row; + } else if ($row['Key_name'] == 'post_content') { $this->stats['post_content'] = $row; -- cgit v1.2.1 From f8fbe3793680af1dae2db2829cfc84068831c52f Mon Sep 17 00:00:00 2001 From: rxu Date: Wed, 28 Jun 2017 00:58:03 +0700 Subject: [ticket/14972] replace all occurrences of sizeof() with the count() PHPBB3-14972 --- phpBB/phpbb/search/fulltext_mysql.php | 22 +++++++++++----------- 1 file changed, 11 insertions(+), 11 deletions(-) (limited to 'phpBB/phpbb/search/fulltext_mysql.php') diff --git a/phpBB/phpbb/search/fulltext_mysql.php b/phpBB/phpbb/search/fulltext_mysql.php index c8df1951e3..51c5fe8b76 100644 --- a/phpBB/phpbb/search/fulltext_mysql.php +++ b/phpBB/phpbb/search/fulltext_mysql.php @@ -232,9 +232,9 @@ class fulltext_mysql extends \phpbb\search\base $this->split_words = $matches[1]; // We limit the number of allowed keywords to minimize load on the database - if ($this->config['max_num_search_keywords'] && sizeof($this->split_words) > $this->config['max_num_search_keywords']) + if ($this->config['max_num_search_keywords'] && count($this->split_words) > $this->config['max_num_search_keywords']) { - trigger_error($this->user->lang('MAX_NUM_SEARCH_KEYWORDS_REFINE', (int) $this->config['max_num_search_keywords'], sizeof($this->split_words))); + trigger_error($this->user->lang('MAX_NUM_SEARCH_KEYWORDS_REFINE', (int) $this->config['max_num_search_keywords'], count($this->split_words))); } // to allow phrase search, we need to concatenate quoted words @@ -361,7 +361,7 @@ class fulltext_mysql extends \phpbb\search\base // remove too short or too long words $text = array_values($text); - for ($i = 0, $n = sizeof($text); $i < $n; $i++) + for ($i = 0, $n = count($text); $i < $n; $i++) { $text[$i] = trim($text[$i]); if (utf8_strlen($text[$i]) < $this->config['fulltext_mysql_min_word_len'] || utf8_strlen($text[$i]) > $this->config['fulltext_mysql_max_word_len']) @@ -563,12 +563,12 @@ class fulltext_mysql extends \phpbb\search\base $sql_select = ($type == 'posts') ? $sql_select . 'p.post_id' : 'DISTINCT ' . $sql_select . 't.topic_id'; $sql_from = ($join_topic) ? TOPICS_TABLE . ' t, ' : ''; $field = ($type == 'posts') ? 'post_id' : 'topic_id'; - if (sizeof($author_ary) && $author_name) + if (count($author_ary) && $author_name) { // first one matches post of registered users, second one guests and deleted users $sql_author = ' AND (' . $this->db->sql_in_set('p.poster_id', array_diff($author_ary, array(ANONYMOUS)), false, true) . ' OR p.post_username ' . $author_name . ')'; } - else if (sizeof($author_ary)) + else if (count($author_ary)) { $sql_author = ' AND ' . $this->db->sql_in_set('p.poster_id', $author_ary); } @@ -580,7 +580,7 @@ class fulltext_mysql extends \phpbb\search\base $sql_where_options = $sql_sort_join; $sql_where_options .= ($topic_id) ? ' AND p.topic_id = ' . $topic_id : ''; $sql_where_options .= ($join_topic) ? ' AND t.topic_id = p.topic_id' : ''; - $sql_where_options .= (sizeof($ex_fid_ary)) ? ' AND ' . $this->db->sql_in_set('p.forum_id', $ex_fid_ary, true) : ''; + $sql_where_options .= (count($ex_fid_ary)) ? ' AND ' . $this->db->sql_in_set('p.forum_id', $ex_fid_ary, true) : ''; $sql_where_options .= ' AND ' . $post_visibility; $sql_where_options .= $sql_author; $sql_where_options .= ($sort_days) ? ' AND p.post_time >= ' . (time() - ($sort_days * 86400)) : ''; @@ -660,7 +660,7 @@ class fulltext_mysql extends \phpbb\search\base public function author_search($type, $firstpost_only, $sort_by_sql, $sort_key, $sort_dir, $sort_days, $ex_fid_ary, $post_visibility, $topic_id, $author_ary, $author_name, &$id_ary, &$start, $per_page) { // No author? No posts - if (!sizeof($author_ary)) + if (!count($author_ary)) { return 0; } @@ -737,7 +737,7 @@ class fulltext_mysql extends \phpbb\search\base { $sql_author = $this->db->sql_in_set('p.poster_id', $author_ary); } - $sql_fora = (sizeof($ex_fid_ary)) ? ' AND ' . $this->db->sql_in_set('p.forum_id', $ex_fid_ary, true) : ''; + $sql_fora = (count($ex_fid_ary)) ? ' AND ' . $this->db->sql_in_set('p.forum_id', $ex_fid_ary, true) : ''; $sql_topic_id = ($topic_id) ? ' AND p.topic_id = ' . (int) $topic_id : ''; $sql_time = ($sort_days) ? ' AND p.post_time >= ' . (time() - ($sort_days * 86400)) : ''; $sql_firstpost = ($firstpost_only) ? ' AND p.post_id = t.topic_first_post_id' : ''; @@ -890,7 +890,7 @@ class fulltext_mysql extends \phpbb\search\base $id_ary = array_unique($id_ary); } - if (sizeof($id_ary)) + if (count($id_ary)) { $this->save_ids($search_key, '', $author_ary, $result_count, $id_ary, $start, $sort_dir); $id_ary = array_slice($id_ary, 0, $per_page); @@ -997,7 +997,7 @@ class fulltext_mysql extends \phpbb\search\base $alter_list[] = $alter_entry; } - if (sizeof($alter_list)) + if (count($alter_list)) { foreach ($alter_list as $alter) { @@ -1050,7 +1050,7 @@ class fulltext_mysql extends \phpbb\search\base $alter[] = 'DROP INDEX post_text'; } - if (sizeof($alter)) + if (count($alter)) { $this->db->sql_query('ALTER TABLE ' . POSTS_TABLE . ' ' . implode(', ', $alter)); } -- cgit v1.2.1 From 013f9819b00bb941faee9c0578bd6fb2f3378a72 Mon Sep 17 00:00:00 2001 From: kasimi Date: Sun, 28 Jan 2018 16:14:29 +0100 Subject: [ticket/15537] Add core.search_(native|mysql|postgres|sphinx)_index_before PHPBB3-15537 --- phpBB/phpbb/search/fulltext_mysql.php | 28 ++++++++++++++++++++++++++++ 1 file changed, 28 insertions(+) (limited to 'phpBB/phpbb/search/fulltext_mysql.php') diff --git a/phpBB/phpbb/search/fulltext_mysql.php b/phpBB/phpbb/search/fulltext_mysql.php index 51c5fe8b76..cc8180ec69 100644 --- a/phpBB/phpbb/search/fulltext_mysql.php +++ b/phpBB/phpbb/search/fulltext_mysql.php @@ -918,6 +918,34 @@ class fulltext_mysql extends \phpbb\search\base $words = array_unique(array_merge($split_text, $split_title)); + /** + * Event to modify method arguments and words before the MySQL search index is updated + * + * @event core.search_mysql_index_before + * @var string mode Contains the post mode: edit, post, reply, quote + * @var int post_id The id of the post which is modified/created + * @var string message New or updated post content + * @var string subject New or updated post subject + * @var int poster_id Post author's user id + * @var int forum_id The id of the forum in which the post is located + * @var array words List of words added to the index + * @var array split_text Array of words from the message + * @var array split_title Array of words from the title + * @since 3.2.3-RC1 + */ + $vars = array( + 'mode', + 'post_id', + 'message', + 'subject', + 'poster_id', + 'forum_id', + 'words', + 'split_text', + 'split_title', + ); + extract($this->phpbb_dispatcher->trigger_event('core.search_mysql_index_before', compact($vars))); + unset($split_text); unset($split_title); -- cgit v1.2.1 From a6dc32b381dea235c979d089fc7897dd7e213011 Mon Sep 17 00:00:00 2001 From: kasimi Date: Fri, 16 Feb 2018 21:31:36 +0100 Subject: [ticket/15561] Add events for adding columns to search index PHPBB3-15561 --- phpBB/phpbb/search/fulltext_mysql.php | 57 ++++++++++++++++++++++++++++++----- 1 file changed, 50 insertions(+), 7 deletions(-) (limited to 'phpBB/phpbb/search/fulltext_mysql.php') diff --git a/phpBB/phpbb/search/fulltext_mysql.php b/phpBB/phpbb/search/fulltext_mysql.php index 51c5fe8b76..0d07f5fbbf 100644 --- a/phpBB/phpbb/search/fulltext_mysql.php +++ b/phpBB/phpbb/search/fulltext_mysql.php @@ -997,17 +997,37 @@ class fulltext_mysql extends \phpbb\search\base $alter_list[] = $alter_entry; } - if (count($alter_list)) + $sql_queries = []; + + foreach ($alter_list as $alter) { - foreach ($alter_list as $alter) - { - $this->db->sql_query('ALTER TABLE ' . POSTS_TABLE . ' ' . implode(', ', $alter)); - } + $sql_queries[] = 'ALTER TABLE ' . POSTS_TABLE . ' ' . implode(', ', $alter); } if (!isset($this->stats['post_text'])) { - $this->db->sql_query('ALTER TABLE ' . POSTS_TABLE . ' ADD FULLTEXT post_text (post_text)'); + $sql_queries[] = 'ALTER TABLE ' . POSTS_TABLE . ' ADD FULLTEXT post_text (post_text)'; + } + + $stats = $this->stats; + + /** + * Event to modify SQL queries before the MySQL search index is created + * + * @event core.search_mysql_create_index_before + * @var array sql_queries Array with queries for creating the search index + * @var array stats Array with statistics of the current index (read only) + * @since 3.2.3-RC1 + */ + $vars = array( + 'sql_queries', + 'stats', + ); + extract($this->phpbb_dispatcher->trigger_event('core.search_mysql_create_index_before', compact($vars))); + + foreach ($sql_queries as $sql_query) + { + $this->db->sql_query($sql_query); } $this->db->sql_query('TRUNCATE TABLE ' . SEARCH_RESULTS_TABLE); @@ -1050,9 +1070,32 @@ class fulltext_mysql extends \phpbb\search\base $alter[] = 'DROP INDEX post_text'; } + $sql_queries = []; + if (count($alter)) { - $this->db->sql_query('ALTER TABLE ' . POSTS_TABLE . ' ' . implode(', ', $alter)); + $sql_queries[] = 'ALTER TABLE ' . POSTS_TABLE . ' ' . implode(', ', $alter); + } + + $stats = $this->stats; + + /** + * Event to modify SQL queries before the MySQL search index is deleted + * + * @event core.search_mysql_delete_index_before + * @var array sql_queries Array with queries for deleting the search index + * @var array stats Array with statistics of the current index (read only) + * @since 3.2.3-RC1 + */ + $vars = array( + 'sql_queries', + 'stats', + ); + extract($this->phpbb_dispatcher->trigger_event('core.search_mysql_delete_index_before', compact($vars))); + + foreach ($sql_queries as $sql_query) + { + $this->db->sql_query($sql_query); } $this->db->sql_query('TRUNCATE TABLE ' . SEARCH_RESULTS_TABLE); -- cgit v1.2.1 From e33e5727413543c74b3ede43ad437bb261c72839 Mon Sep 17 00:00:00 2001 From: Casey Peel Date: Mon, 8 Jul 2019 05:46:41 +0000 Subject: [ticket/16096] Use InnoDB fulltext limits for InnoDB tables The max and min search length for the MySQL database vary based on the engine for the underlying table. For MyISAM tables, the variables are ft_max_word_len and ft_min_word_len, but for InnoDB tables the variables are innodb_ft_max_token_size and innodb_ft_min_token_size. Take the posts table type into account when setting the max and min search length. PHPBB3-16096 --- phpBB/phpbb/search/fulltext_mysql.php | 14 +++++++++++--- 1 file changed, 11 insertions(+), 3 deletions(-) (limited to 'phpBB/phpbb/search/fulltext_mysql.php') diff --git a/phpBB/phpbb/search/fulltext_mysql.php b/phpBB/phpbb/search/fulltext_mysql.php index 137ed7433d..1105d0892f 100644 --- a/phpBB/phpbb/search/fulltext_mysql.php +++ b/phpBB/phpbb/search/fulltext_mysql.php @@ -188,7 +188,7 @@ class fulltext_mysql extends \phpbb\search\base } $sql = 'SHOW VARIABLES - LIKE \'ft\_%\''; + LIKE \'%ft\_%\''; $result = $this->db->sql_query($sql); $mysql_info = array(); @@ -198,8 +198,16 @@ class fulltext_mysql extends \phpbb\search\base } $this->db->sql_freeresult($result); - $this->config->set('fulltext_mysql_max_word_len', $mysql_info['ft_max_word_len']); - $this->config->set('fulltext_mysql_min_word_len', $mysql_info['ft_min_word_len']); + if ($engine === 'MyISAM') + { + $this->config->set('fulltext_mysql_max_word_len', $mysql_info['ft_max_word_len']); + $this->config->set('fulltext_mysql_min_word_len', $mysql_info['ft_min_word_len']); + } + else if ($engine === 'InnoDB') + { + $this->config->set('fulltext_mysql_max_word_len', $mysql_info['innodb_ft_max_token_size']); + $this->config->set('fulltext_mysql_min_word_len', $mysql_info['innodb_ft_min_token_size']); + } return false; } -- cgit v1.2.1