Can’t edit database schema in WordPress running on Flywheel Local 5.x
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.
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!
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.