How to drop fulltext search index in Mysql

Show All indexes

mysql> SELECT table_schema, table_name, index_name, index_type, column_name FROM 
       -> information_schema.statistics WHERE non_unique = 1 AND 
       -> table_schema = "DB-NAME-HERE";
+--------------+-------------------+-----------------------------+------------+-------------------------+
| TABLE_SCHEMA | TABLE_NAME        | INDEX_NAME                  | INDEX_TYPE | COLUMN_NAME             |
+--------------+-------------------+-----------------------------+------------+-------------------------+
| DB-NAME      | wp_commentmeta    | comment_id                  | BTREE      | comment_id              |
| DB-NAME      | wp_commentmeta    | meta_key                    | BTREE      | meta_key                |
| DB-NAME      | wp_comments       | comment_post_ID             | BTREE      | comment_post_ID         |
| DB-NAME      | wp_comments       | comment_approved_date_gmt   | BTREE      | comment_date_gmt        |
| DB-NAME      | wp_comments       | comment_approved_date_gmt   | BTREE      | comment_approved        |
| DB-NAME      | wp_comments       | comment_date_gmt            | BTREE      | comment_date_gmt        |
| DB-NAME      | wp_comments       | comment_parent              | BTREE      | comment_parent          |
| DB-NAME      | wp_comments       | comment_author_email        | BTREE      | comment_author_email    |
| DB-NAME      | wp_links          | link_visible                | BTREE      | link_visible            |
| DB-NAME      | wp_options        | autoload                    | BTREE      | autoload                |
| DB-NAME      | wp_postmeta       | post_id                     | BTREE      | post_id                 |
| DB-NAME      | wp_posts          | post_author                 | BTREE      | post_author             |
+--------------+-------------------+-----------------------------+------------+-------------------------+
12 rows in set (0.01 sec)

mysql> drop index search_fulltext on wp_posts;
ERROR 1067 (42000): Invalid default value for 'post_date'

so to be able to drop the full text index, we need to change the sql_mode in current SESSION and remove “NO_ZERO_DATE“.

checking the session sql.mode

mysql> SELECT @@SESSION.sql_mode;
+-----------------------------------------------------------------------------------------------------------------------+
| @@SESSION.sql_mode                                                                                                    |
+-----------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
+-----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

setting the session sql.mode without the “NO_ZERO_DATE“:

mysql> SET @@SESSION.sql_mode= 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
Query OK, 0 rows affected, 1 warning (0.00 sec)

dropping the fulltext search index:

mysql> drop index search_fulltext_title on wp_posts;
Query OK, 0 rows affected (0.11 sec)