Category: MySQL

Migrating from Homebrew MySQL to Laravel Herd Pro Services

Posted on by Andrew Minion

Laravel Herd is an amazing application designed to make it as easy as possible to get started with PHP development.

I have been using Homebrew to run MySQL, Redis, Meilisearch, and more, but since we recently got Herd Pro, I figured it made sense to consolidate.

Here are a few other articles on how to migrate databases to Herd Pro:

My method is a combination of the two: copy the data files from the Homebrew MySQL to Herd Pro to save the time that it would take to dump and import.

Note: this only works if your homebrew MySQL and Herd Pro MySQL are on the same minor version (8.0.1 to 8.0.3 would work; 8.0.x to 8.4.x would not).

  1. Stop the homebrew MySQL service, if you haven’t already: brew services stop mysql (or maybe [email protected] if you’ve updated in the past few months)
  2. Stop the Herd MySQL service, if you haven’t already, using the Herd services UI
  3. Find the Herd data directory: right-click on the MySQL service and choose “Open data directory”
  4. Copy or move the files to retain a backup
  5. Find the homebrew data directory: in a terminal, run open $(brew --prefix)/var/mysql to open the directory in Finder
  6. Copy the files to the Herd data directory
  7. Restart Herd
  8. After you’ve confirmed everything is fine, maybe delete the homebrew mysql data directory and brew uninstall [email protected]

MySQL Table Size

Posted on by Andrew Minion

Ever wondered which database or tables are taking up disk space on a MySQL/MariaDB server?

This query will provide the size of each table:

SELECT TABLE_SCHEMA AS `Database`,
TABLE_NAME AS `Table`,
ROUND(((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024), 2) AS `Size (MB)`,
ROUND((data_free / 1024 / 1024), 2) AS `Reclaimable Size (MB)`
FROM information_schema.TABLES
-- WHERE `TABLE_SCHEMA` = 'database_name'
ORDER BY (DATA_LENGTH + INDEX_LENGTH) DESC;

Shimming MySQL Functions into SQLite for Laravel CI/CD Testing

Posted on by Andrew Minion

Colin DeCarlo presented a talk at Laracon Online where among other useful tips, he demonstrated how to shim MySQL functions in an SQLite database (e.g., add functions that MySQL has but SQLite does not).

Here are two examples that I just needed in a project (FLOOR and DATEDIFF):

use Illuminate\Support\Facades\DB;

DB::getPdo()->sqliteCreateFunction('floor', fn ($value) => floor($value));
DB::getPdo()->sqliteCreateFunction('datediff', fn ($date1, $date2) => Carbon::parse($date1)->diff(Carbon::parse($date2))->days);

Counting Distinct Values in a Single Field

Posted on by Andrew Minion

A quick MySQL snippet to count how many times a value appears in a single field—much easier to grok than multiple JOINs.

SELECT
COUNT(CASE WHEN meta_value = 'value1' THEN 1 END) AS value1,
COUNT(CASE WHEN meta_value = 'value2' THEN 1 END) AS value2
FROM wp_post_meta;

/** Results

| value1 | value2 |
|--------|--------|
| 75     | 56     |
*/

WooCommerce Downloadable Products: Granting Permissions to Previous Customers

Posted on by Andrew Minion

If you’re using WooCommerce to provide downloadable products and add a new download, previous customers do not automatically get access to it as of v3.x.

Here are a couple of options: Continue reading “WooCommerce Downloadable Products: Granting Permissions to Previous Customers”