New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Project Nami Full Text Search seems not working #287

Open
benoithamet opened this Issue Jun 20, 2018 · 24 comments

Comments

Projects
None yet
3 participants
@benoithamet
Copy link

benoithamet commented Jun 20, 2018

Hi there
I'm sorry but I did not find anything so I'm posting
It seems the Project Nami Full Text Search (from the latest release (1.9.6) is not working
When searching the WP site, always getting 'Nothing found' while I should have result
Is there anything specific I need to do to get the search working?

@patrickebates

This comment has been minimized.

Copy link
Member

patrickebates commented Jun 20, 2018

Hello. Let's start with your PHP error_log. By any chance do you have any errors from when you activated the plugin? There are several SQL queries which execute at activation to enable Full Text features in your database.

@benoithamet

This comment has been minimized.

Copy link
Author

benoithamet commented Jun 20, 2018

Thanks Patrick
I did not had any error when activating the plugin and it is currently activated
for the PHP error log, I'm not finding any log
image

@patrickebates

This comment has been minimized.

Copy link
Member

patrickebates commented Jun 20, 2018

That appears to be a Jetpack-specific log folder.

Are you running locally on Windows? If so, you may need to consult your PHP settings to find the log file location. This may help.
https://stackoverflow.com/questions/15604695/php-error-logs-on-iis-7-5-windows-server-2008

Also, assuming your DB is local on Windows as well, when SQL was installed were the Full Text search components included?

@benoithamet

This comment has been minimized.

Copy link
Author

benoithamet commented Jun 20, 2018

Ok, thanks for the log folder
I'm running on Windows 2016 indeed; SQL is 2017 on remote server with the Full Text search component installed
I even have manually created a Full Text Catalog (maybe this is not required) but I tried because the search was not working

below the last logs; seems the fulltext view can not be used
[20-Jun-2018 03:11:26 UTC] WordPress database error [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Cannot use a CONTAINS or FREETEXT predicate on table or indexed view 'wp_fulltext_search' because it is not full-text indexed. Code - 7601 for query SELECT COUNT( wp_posts.ID ) as [found_rows] FROM wp_posts INNER JOIN CONTAINSTABLE(wp_fulltext_search, *, 'azure') as pnftsearch on pnftsearch.[KEY] = wp_posts.ID WHERE 1=1 AND wp_posts.post_type IN ('post', 'page', 'attachment') AND (wp_posts.post_status = 'publish') made by require('W:\inetpub\wordpress_blog\wp-blog-header.php'), wp, WP->main, WP->query_posts, WP_Query->query, WP_Query->get_posts
[20-Jun-2018 03:11:26 UTC] WordPress database error [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Cannot use a CONTAINS or FREETEXT predicate on table or indexed view 'wp_fulltext_search' because it is not full-text indexed. Code - 7601 for query SELECT COUNT( wp_posts.ID ) as [found_rows] FROM wp_posts INNER JOIN CONTAINSTABLE(wp_fulltext_search, , 'azure') as pnftsearch on pnftsearch.[KEY] = wp_posts.ID WHERE 1=1 AND wp_posts.post_type IN ('post', 'page', 'attachment') AND (wp_posts.post_status = 'publish') made by require('W:\inetpub\wordpress_blog\wp-blog-header.php'), wp, WP->main, WP->query_posts, WP_Query->query, WP_Query->get_posts
[20-Jun-2018 03:11:26 UTC] WordPress database error [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Cannot use a CONTAINS or FREETEXT predicate on table or indexed view 'wp_fulltext_search' because it is not full-text indexed. Code - 7601 for query SELECT wp_posts.
, wp_posts.post_date FROM wp_posts INNER JOIN CONTAINSTABLE(wp_fulltext_search, , 'azure') as pnftsearch on pnftsearch.[KEY] = wp_posts.ID WHERE 1=1 AND wp_posts.post_type IN ('post', 'page', 'attachment') AND (wp_posts.post_status = 'publish') ORDER BY pnftsearch.[RANK] desc OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY made by require('W:\inetpub\wordpress_blog\wp-blog-header.php'), wp, WP->main, WP->query_posts, WP_Query->query, WP_Query->get_posts
[20-Jun-2018 03:11:26 UTC] WordPress database error [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Cannot use a CONTAINS or FREETEXT predicate on table or indexed view 'wp_fulltext_search' because it is not full-text indexed. Code - 7601 for query SELECT wp_posts.
, wp_posts.post_date FROM wp_posts INNER JOIN CONTAINSTABLE(wp_fulltext_search, *, 'azure') as pnftsearch on pnftsearch.[KEY] = wp_posts.ID WHERE 1=1 AND wp_posts.post_type = 'post' AND ((wp_posts.post_status = 'publish')) ORDER BY pnftsearch.[RANK] desc OFFSET 0 ROWS FETCH NEXT 5 ROWS ONLY made by require('W:\inetpub\wordpress_blog\wp-blog-header.php'), require_once('W:\inetpub\wordpress_blog\wp-includes\template-loader.php'), include('W:\inetpub\wordpress_blog\wp-content\themes\twentysixteen\search.php'), get_sidebar, locate_template, load_template, require_once('W:\inetpub\wordpress_blog\wp-content\themes\twentysixteen\sidebar.php'), dynamic_sidebar, WP_Widget->display_callback, WP_Widget_Recent_Posts->widget, WP_Query->__construct, WP_Query->query, WP_Query->get_posts
[20-Jun-2018 03:12:46 UTC] WordPress database error [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Cannot use a CONTAINS or FREETEXT predicate on table or indexed view 'wp_fulltext_search' because it is not full-text indexed. Code - 7601 for query SELECT COUNT( wp_posts.ID ) as [found_rows] FROM wp_posts INNER JOIN CONTAINSTABLE(wp_fulltext_search, *, 'ure') as pnftsearch on pnftsearch.[KEY] = wp_posts.ID WHERE 1=1 AND wp_posts.post_type IN ('post', 'page', 'attachment') AND (wp_posts.post_status = 'publish') made by require('W:\inetpub\wordpress_blog\wp-blog-header.php'), wp, WP->main, WP->query_posts, WP_Query->query, WP_Query->get_posts
[20-Jun-2018 03:12:46 UTC] WordPress database error [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Cannot use a CONTAINS or FREETEXT predicate on table or indexed view 'wp_fulltext_search' because it is not full-text indexed. Code - 7601 for query SELECT COUNT( wp_posts.ID ) as [found_rows] FROM wp_posts INNER JOIN CONTAINSTABLE(wp_fulltext_search, , 'ure') as pnftsearch on pnftsearch.[KEY] = wp_posts.ID WHERE 1=1 AND wp_posts.post_type IN ('post', 'page', 'attachment') AND (wp_posts.post_status = 'publish') made by require('W:\inetpub\wordpress_blog\wp-blog-header.php'), wp, WP->main, WP->query_posts, WP_Query->query, WP_Query->get_posts
[20-Jun-2018 03:12:46 UTC] WordPress database error [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Cannot use a CONTAINS or FREETEXT predicate on table or indexed view 'wp_fulltext_search' because it is not full-text indexed. Code - 7601 for query SELECT wp_posts.
, wp_posts.post_date FROM wp_posts INNER JOIN CONTAINSTABLE(wp_fulltext_search, , 'ure') as pnftsearch on pnftsearch.[KEY] = wp_posts.ID WHERE 1=1 AND wp_posts.post_type IN ('post', 'page', 'attachment') AND (wp_posts.post_status = 'publish') ORDER BY pnftsearch.[RANK] desc OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY made by require('W:\inetpub\wordpress_blog\wp-blog-header.php'), wp, WP->main, WP->query_posts, WP_Query->query, WP_Query->get_posts
[20-Jun-2018 03:12:46 UTC] WordPress database error [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Cannot use a CONTAINS or FREETEXT predicate on table or indexed view 'wp_fulltext_search' because it is not full-text indexed. Code - 7601 for query SELECT wp_posts.
, wp_posts.post_date FROM wp_posts INNER JOIN CONTAINSTABLE(wp_fulltext_search, *, 'ure') as pnftsearch on pnftsearch.[KEY] = wp_posts.ID WHERE 1=1 AND wp_posts.post_type = 'post' AND ((wp_posts.post_status = 'publish')) ORDER BY pnftsearch.[RANK] desc OFFSET 0 ROWS FETCH NEXT 5 ROWS ONLY made by require('W:\inetpub\wordpress_blog\wp-blog-header.php'), require_once('W:\inetpub\wordpress_blog\wp-includes\template-loader.php'), include('W:\inetpub\wordpress_blog\wp-content\themes\twentysixteen\search.php'), get_sidebar, locate_template, load_template, require_once('W:\inetpub\wordpress_blog\wp-content\themes\twentysixteen\sidebar.php'), dynamic_sidebar, WP_Widget->display_callback, WP_Widget_Recent_Posts->widget, WP_Query->__construct, WP_Query->query, WP_Query->get_posts
[20-Jun-2018 03:13:03 UTC] WordPress database error [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Cannot use a CONTAINS or FREETEXT predicate on table or indexed view 'wp_fulltext_search' because it is not full-text indexed. Code - 7601 for query SELECT COUNT( wp_posts.ID ) as [found_rows] FROM wp_posts INNER JOIN CONTAINSTABLE(wp_fulltext_search, *, 'ad') as pnftsearch on pnftsearch.[KEY] = wp_posts.ID WHERE 1=1 AND wp_posts.post_type IN ('post', 'page', 'attachment') AND (wp_posts.post_status = 'publish') made by require('W:\inetpub\wordpress_blog\wp-blog-header.php'), wp, WP->main, WP->query_posts, WP_Query->query, WP_Query->get_posts
[20-Jun-2018 03:13:03 UTC] WordPress database error [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Cannot use a CONTAINS or FREETEXT predicate on table or indexed view 'wp_fulltext_search' because it is not full-text indexed. Code - 7601 for query SELECT COUNT( wp_posts.ID ) as [found_rows] FROM wp_posts INNER JOIN CONTAINSTABLE(wp_fulltext_search, , 'ad') as pnftsearch on pnftsearch.[KEY] = wp_posts.ID WHERE 1=1 AND wp_posts.post_type IN ('post', 'page', 'attachment') AND (wp_posts.post_status = 'publish') made by require('W:\inetpub\wordpress_blog\wp-blog-header.php'), wp, WP->main, WP->query_posts, WP_Query->query, WP_Query->get_posts
[20-Jun-2018 03:13:03 UTC] WordPress database error [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Cannot use a CONTAINS or FREETEXT predicate on table or indexed view 'wp_fulltext_search' because it is not full-text indexed. Code - 7601 for query SELECT wp_posts.
, wp_posts.post_date FROM wp_posts INNER JOIN CONTAINSTABLE(wp_fulltext_search, , 'ad') as pnftsearch on pnftsearch.[KEY] = wp_posts.ID WHERE 1=1 AND wp_posts.post_type IN ('post', 'page', 'attachment') AND (wp_posts.post_status = 'publish') ORDER BY pnftsearch.[RANK] desc OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY made by require('W:\inetpub\wordpress_blog\wp-blog-header.php'), wp, WP->main, WP->query_posts, WP_Query->query, WP_Query->get_posts
[20-Jun-2018 03:13:03 UTC] WordPress database error [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Cannot use a CONTAINS or FREETEXT predicate on table or indexed view 'wp_fulltext_search' because it is not full-text indexed. Code - 7601 for query SELECT wp_posts.
, wp_posts.post_date FROM wp_posts INNER JOIN CONTAINSTABLE(wp_fulltext_search, *, 'ad') as pnftsearch on pnftsearch.[KEY] = wp_posts.ID WHERE 1=1 AND wp_posts.post_type = 'post' AND ((wp_posts.post_status = 'publish')) ORDER BY pnftsearch.[RANK] desc OFFSET 0 ROWS FETCH NEXT 5 ROWS ONLY made by require('W:\inetpub\wordpress_blog\wp-blog-header.php'), require_once('W:\inetpub\wordpress_blog\wp-includes\template-loader.php'), include('W:\inetpub\wordpress_blog\wp-content\themes\twentysixteen\search.php'), get_sidebar, locate_template, load_template, require_once('W:\inetpub\wordpress_blog\wp-content\themes\twentysixteen\sidebar.php'), dynamic_sidebar, WP_Widget->display_callback, WP_Widget_Recent_Posts->widget, WP_Query->__construct, WP_Query->query, WP_Query->get_posts

below screenshot of the db and ft catalog (which shows the full text component is there and working)
image

@patrickebates

This comment has been minimized.

Copy link
Member

patrickebates commented Jun 20, 2018

Here's the commands used during activation to create the necessary full text systems. It appears that the first two have completed, but you may want to attempt to run them all anyway. Run them in SSMS one at a time.

// Create the full text catalog if it does not exist
if not exists (select * from sys.dm_fts_active_catalogs where name = 'ftCatalog') create fulltext catalog ftCatalog

// Create the full text view if it does not exist
if not exists (select * from INFORMATION_SCHEMA.TABLES where table_name = 'wp_fulltext_search') exec('CREATE VIEW [dbo].[wp_fulltext_search] WITH SCHEMABINDING AS select wp_posts.ID, display_name + '' '' + post_title + '' '' + post_excerpt + '' '' + post_content as search_text from dbo.wp_posts inner join dbo.wp_users on wp_posts.post_author = wp_users.ID')

// Create the clustered index if it does not exist
if not exists (select * from sys.indexes where name = 'CLU_wp_fulltext_search') CREATE UNIQUE CLUSTERED INDEX [CLU_wp_fulltext_search] ON [dbo].[wp_fulltext_search] ([ID] ASC)

// Create the full text index if it does not exist
if not exists (select * from sys.fulltext_indexes where object_id = object_id('wp_fulltext_search')) CREATE FULLTEXT INDEX ON wp_fulltext_search(search_text) KEY INDEX CLU_wp_fulltext_search ON ftCatalog

// Enable the full text index
ALTER FULLTEXT INDEX ON wp_fulltext_search ENABLE
@benoithamet

This comment has been minimized.

Copy link
Author

benoithamet commented Jun 20, 2018

Thanks Patrick
Will do; do I have to delete the FT Catalog I have manually created first?I think so but just to confirm

@patrickebates

This comment has been minimized.

Copy link
Member

patrickebates commented Jun 20, 2018

No, it should be fine. A database can have multiple catalogs.

@benoithamet

This comment has been minimized.

Copy link
Author

benoithamet commented Jun 20, 2018

Thanks
I will delete it anyway as it was to try to get the search working :)

@benoithamet

This comment has been minimized.

Copy link
Author

benoithamet commented Jun 20, 2018

all good
this is now working :)
seems the initial FT catalog creation did not execute
thanks for your help Patrick

@NoralK

This comment has been minimized.

Copy link

NoralK commented Dec 9, 2018

It would be helpful to add a wiki page or additional content to the README.md on how to setup the database for the "Project Nami Full Text Search" plugin.

Perhaps the plugin itself could do this work as part of the activation or maybe a settings page to create/delete the entries needed for full-text search and re-index support.

@patrickebates

This comment has been minimized.

Copy link
Member

patrickebates commented Dec 9, 2018

The plugin does do the work, unless something has changed that we haven't seen yet.

Has full text support been installed on your SQL instance? Without that installed first, the plugin can't perform the steps to configure the database.

@patrickebates patrickebates reopened this Dec 9, 2018

@patrickebates

This comment has been minimized.

Copy link
Member

patrickebates commented Dec 9, 2018

I might have just identified the issue. Seems the query to detect the existing full text catalog might be incorrectly seeing one declared for a different database on the same SQL instance. I was able to reproduce this on a test DB running alongside a live DB on the same SQL instance.

Looking for solutions.

@patrickebates

This comment has been minimized.

Copy link
Member

patrickebates commented Dec 9, 2018

Corrected with commit 5cb6a8a

Updated file is currently available in the Master branch. Will deploy to everyone with the next release.

@NoralK

This comment has been minimized.

Copy link

NoralK commented Dec 13, 2018

Sorry for not getting back sooner. Yes, full text has been installed on SQL Server is 2016.

@patrickebates

This comment has been minimized.

Copy link
Member

patrickebates commented Dec 13, 2018

Please install PN 2.0.1 and see if you can successfully activate the plugin.

@NoralK

This comment has been minimized.

Copy link

NoralK commented Dec 14, 2018

SQL Server 2016 - this fix did not work. It seems these three lines are not being run:

if not exists (select * from sys.dm_fts_active_catalogs where name = 'ftCatalog') create fulltext catalog ftCatalog

if not exists (select * from sys.fulltext_indexes where object_id = object_id('wp_fulltext_search')) CREATE FULLTEXT INDEX ON wp_fulltext_search(search_text) KEY INDEX CLU_wp_fulltext_search ON ftCatalog

ALTER FULLTEXT INDEX ON wp_fulltext_search ENABLE

If I run these statements manually fulltext works as expected.

@patrickebates

This comment has been minimized.

Copy link
Member

patrickebates commented Dec 14, 2018

to confirm, you reactivated the plug after installing PN 2.0.1?

@NoralK

This comment has been minimized.

Copy link

NoralK commented Dec 15, 2018

I did deactivate and then reactivated the plugin. I then ran the T-SQL queries you presented on how to get fulltext running(query only), about half way in this issue, and that is how I came up with my previous response.

I have also deleted the database and installed a fresh WordPress site (Project Nami 2.0.1) with a new SQL database, then enabled the plug in with the same results as above.

@patrickebates patrickebates reopened this Dec 15, 2018

@patrickebates

This comment has been minimized.

Copy link
Member

patrickebates commented Dec 15, 2018

So far I've been unable to reproduce what you described after the fix to filter out full text catalogs based on DB_ID. I'm reopening this issue for further review.

@NoralK

This comment has been minimized.

Copy link

NoralK commented Dec 15, 2018

Thanks - I am specifically using SQL Server 2016 with SP2 no CU's.

Please let me know if you would like me to try anything - this is a dev box.

@patrickebates

This comment has been minimized.

Copy link
Member

patrickebates commented Dec 15, 2018

Is PN connecting to the DB as a different user than you are connecting with? Wonder if we are looking at a permissions problem.

@NoralK

This comment has been minimized.

Copy link

NoralK commented Dec 15, 2018

Yes - The WordPress(Project Nami) user is a SQL User that is the owner of the database ... no additional permissions were granted.

@patrickebates

This comment has been minimized.

Copy link
Member

patrickebates commented Dec 15, 2018

Per https://docs.microsoft.com/en-us/sql/t-sql/statements/create-fulltext-catalog-transact-sql?view=sql-server-2017

Permissions
User must have CREATE FULLTEXT CATALOG permission on the database, or be a member of the db_owner, or db_ddladmin fixed database roles.

I know you said the SQL user is owner of the database, but could you please confirm it meets these permission requirements?

@NoralK

This comment has been minimized.

Copy link

NoralK commented Dec 16, 2018

I saw that too and it did not work for me.

I kept searching and while keeping least amount of privilege in mind - what I did find was to provide the View Server State permission to the Active Roles service account. After I granted the permission, deactivated/reactivated the plugin automatically created everything.

What I did was, from the SQL Server Management Studio via GUI:

  1. Connect to the SQL instance
  2. Navigate to Security | Logins
  3. Right Click the ARS Service Account | Select Properties
  4. Select the Securables page
  5. In the bottom pane, scroll to the bottom and Grant - View Server State

OR from the SQL Server Management Studio via statement:

  1. Connect to the SQL instance
  2. Select New Query
  3. Use the following statement GRANT VIEW SERVER STATE TO "AccountName"
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment