When i use phpmyadmin to add a full text index for wp_posts table, i get this error: “#1067 – Invalid default value for ‘post_date'”. In this tutorial, we will introduce how to fix this problem.
sql_mode
This error is caused by mysql sql_mode, which determines what is the value of post_date.
In phpmyadmin, we can see it use this sql:
show variables like 'sql_mode';
Then we will see:
From the result, we can find mysql can not allow date is zero.
How to fix this error?
We should change the value of sql_mode.
Open my.ini in mysql, we can change it to:
sql_mode=STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
Restart mysql, we find this error is fixed.
Moreover, if you have no permission to edit my.ini. You also can change the data type of post_date to fix this error.
For example, as to wordpress wp_posts table, there are four columns that data type is datetime. We set the date type to be timestamp.
We will see:
The error is also can be fixed.
Moreover, you also can run sql code below to fix this error:
SET sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'; ALTER TABLE wp_posts ADD FULLTEXT INDEX crp_realted(post_title, post_content)
Then, you also can find fulltext index crp_realted is ceated successfully.