Tag: database

Issue

I am using Flywheel’s Local (5.6.x) app for running WordPress development instances on my MacBook locally.

One day, I logged in to a WordPress installation, clicked on Add a New Post and was greeted with a somewhat more bare than usual edit screen:

• All post’s custom meta boxes were missing.
• Publish button was missing and replaced with Submit for review button.
• When clicking on Submit for review button I was shown an error saying that I did not have enough permissions perform that action.

Investigation

I de-activated all the plugins to rule out any interference of their code with WordPress core – the issues remained with all plugins de-activated.

I checked the current user and confirmed that it has correct administrator roles set up. I also logged in as another administrator account and observed the same issue with adding posts.

I restarted the web and MySQL servers – it didn’t help.

Then, I turned my attention to PHP error logs – I ran “tail -f error_log” command and tried adding a new post. The recorded in the log error showed that SQL query failed because a post with ID 0 already existed:

Duplicate entry '0' for key 'PRIMARY' for query INSERT INTO wp_posts...

I quickly checked if auto-increment was enabled for the ID field in wp_posts table and … it was not!

Solution

I found a problem – there was no auto-increment enabled for the ID field in the wp_posts table.

When, I started my MySQL database client and enabled auto-increment, I could not save the changes. MySQL server complained that the default value (0000-00-00 00:00:00) for post_date field was invalid.

Incorrect datetime value: '0000-00-00 00:00:00' for column 'post_date' at row ...

Errors like above in MySQL happen when “NO_ZERO_DATE” is set as one of the MySQL modes. Please read https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html for more details.

To change the value of MySQL mode when running it in Flywheel’s Local app you need to edit a template for mysql.conf file called my.conf.hbs. It is located in /app/cnf/ directory of your site within Local app. Add the following to [mysqld] section. You need to make sure that there are no NO_ZERO_IN_DATE and NO_ZERO_DATE in the value of sql-mode variable.

[mysqld]

sql-mode="ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, ERROR_FOR_DIVISION_BY_ZERO, NO_ENGINE_SUBSTITUTION"

Once saved, restart your site within Local app, edit the wp_posts table add auto-increment to ID field.

Once the changes are saved, you can remove the line you added to my.conf.hbs file and restart the server.