The CMS themer blog

Tag: flywheel

MySQL binlog files are taking up all disk space

I am running Flywheel’s Local app on a MacBook Pro and noticed that I was constantly running out space on my MacBook’s SSD. Even after I cleared big chunks of space it would quickly fill up and run out of space.

The problem was MySQL and its binary log files (located at /Users/<username>/Library/ApplicationSupport/Local/run/<random-hash>/mysql/data) – the new binlog files kept on appearing and eventually would fill up all the available space on the SSD.

Add the following line to the [mysqld] section of the MySQL server configuration file template (my.conf.hbs). It is located in the /app/cnf/ directory of your Local’s site.

expire_logs_days = 3

The following will tell the MySQL server to only keep the binary log files for up to 3 days and delete the older ones.

Alternatively, you can add the following (which will completely disable the logging)

skip-log-bin

Restart the MySQL server and you will see that the old bin files will shortly disappear.

Note: do not delete the bin files manually – let the MySQL server handle it by using the expire_logs_days configuration.

Can’t edit database schema in WordPress running on Flywheel Local 5.x

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.