Index not being added to wp_posts table

Which database server you’re using? MySQL or Maria? Which version? MySQL should be at least version 5.6, MariaDB needs to be at least 10.0.5. If you have an older database version, the required capabilities are unavailable, and you can’t use Relevanssi Light.

Suppose you do have a sufficient database server. In that case, it’s possible the database alteration step just fails to run otherwise. Because it’s a slow action, it’s run as an asynchronous ajax action. These sometimes fail for mysterious reasons.

You can manually make the required database changes if the installation does not work. It’s just two SQL commands:

ALTER TABLE wp_posts ADD COLUMN 'relevanssi_light_data' LONGTEXT;
ALTER TABLE wp_posts ADD FULLTEXT 'relevanssi_light_fulltext' ('post_title', 'post_content', 'post_excerpt', 'relevanssi_light_data' );

• This reply was modified 2 years, 3 months ago by Mikko Saari.

Hi Mikko,

Thanks for the quick reply. I’m going to let my engineer (Chandan) reply here shortly. My local is using MariaDB version 10.4.21.

Our development and production environments are setup on AWS using Amazon Aurora. We haven’t tested Relevanssi Light there just yet. We wanted to first see how this would work on our locals.

I happen to know that our Aurora database is soon going to be updated because Aurora MySQL 1 (with MySQL 5.6 compatibility) is reaching end of life soon. So we’ll be using the latest version there.

But we encountered this issue on our locals. Please also note that both of our locals had the same problem. Chandan and I are using two completely different local setups.

I’ll let Chandan speak with you separately to further troubleshoot this.

Thank you again for your help!

~ Michael

• This reply was modified 2 years, 3 months ago by Michael Samson.

Hi Mikko,

As Michael pointed out earlier, we have different setups for our local. I am using mysql 5.7.34.

To add-on, it’s not only the index that is not being added, the new column ‘relevanssi_light_data’ isn’t added to posts table either.

What you said about asynchronous ajax action failiing sometimes for mysterious reasons might be true here. I checked a little and found out that call to ‘wp_remote_post’ inside ‘relevanssi_light_launch_ajax_action’ returns an empty response. I can manually run the alter table queries on local but we make it a rule to never touch proudction database manually and will like to avoid that if possible.

Thanks much for your contribution.

The wp_remote_post() should return an empty response if everything goes well. You can debug further inside the relevanssi_light_database_alteration_action() function to see if it runs and if it does, what’s the response from $wpdb->query().

Unfortunately, the way the database setup function is done right now makes it hard to directly call the database creation function, as the nonce check is inside the function. I’ll fix that in a future version so that it’s easy to call the function directly to create the tables.

See here: https://github.com/msaari/relevanssi-light/blob/master/relevanssi-light.php.

This way, you can call the relevanssi_light_alter_table() directly to set up the database table if the AJAX action fails.

I didn’t mention that I had added a line in relevanssi_light_database_alteration_action to deliberately print a response. The request never reaches this function.

Anyway, if that’s the only way for now, I’ll make necessary edits on my own. I can run the alter table queries directly on admin_init and make other necessary adjustments.

Thanks again.

@msaari

Just wanted to say thank you for your earlier responses.

We were able to get Relevanssi Light working, but after considering several factors (mainly the need for flexibility), we decided to stick with the standard free version for now.

My thanks again for helping us!

~ Michael