Get your MySQL table sizes in MB via a SQL query

Figuring out what tables are making your database excessively large can be really helpful when working with production database exports and keeping various environments up to date. This SQL query outputs that information for all databases on a given server:

SELECT
table_schema as `Database`,
table_name AS `Table`,
round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB`
FROM information_schema.TABLES
ORDER BY table_schema, (data_length + index_length) DESC;

Reference:

Generate MySQL INSERT statements for a few existing records

Occasionally, you’ll want to duplicate a couple of records from your production MySQL database for local use without dumping the entire table (which could be huge). As a solution, the mysqldump command allows you to specify a table and a WHERE query for that table, allowing you to select specific record ID #s and retrieve only those records as INSERT statements:

mysqldump -h {host} -P {port} -u {username} -p{password} {database_name} {table_name} --where="ID = 1" --no-create-info --no-create-db

Reference:

Import Environics demographics data into a SQL database via Laravel CLI command

Environics Analytics provides incredibly useful demographics, psychographics and segmentation data for Canada, but the raw data is challenging if the goal is to use it in a general SQL query: The raw data is in one file, the meanings of the columns are in a ‘metadata’ file, and the data provided doesn’t necessarily just list postal codes but other geographic areas as well.

Linked below is a Laravel CLI command that outputs a .SQL file that will handle the creation and insertion of data and definitions for you. The CLI command is invoked as follows:

php artisan environics_demographics:import {csv_demographics_definitions} {csv_demographics_data} {year_demographics_data} {dir_output_data}

Find the full script on GitHub Gist, or below:

Merge a PHP array without duplicates (array_merge_recursive_distinct)

Often enough in PHP, you’ll grab objects from a variety of sources and want to merge them into a single array of results. To merge without duplicates, add the following function to your codebase and make use of array_merge_recursive_distinct the same way you would array_merge_recursive:

// From: https://www.php.net/manual/en/function.array-merge-recursive.php#92195
if (! function_exists('array_merge_recursive_distinct')) {
    /**
     * @param array<int|string, mixed> $array1
     * @param array<int|string, mixed> $array2
     *
     * @return array<int|string, mixed>
     */
    function array_merge_recursive_distinct(array &$array1, array &$array2): array
    {
        $merged = $array1;
        foreach ($array2 as $key => &$value) {
            if (is_array($value) && isset($merged[$key]) && is_array($merged[$key])) {
                $merged[$key] = array_merge_recursive_distinct($merged[$key], $value);
            } else {
                $merged[$key] = $value;
            }
        }

        return $merged;
    }
}

Reference:

Upgrade a deprecated AWS Elastic Beanstalk environment in place using the CLI

When a major PHP version change (eg. 5.7 to 7.0, or 7.0 to 8.0) occurs, Amazon AWS Elastic Beanstalk typically does not allow for automated platform version updates but instead requires the cloning of an interface and a CNAME switch. For less available environments, use the following steps to upgrade-in-place using the AWS command line interface:

  1. Take a look at the list of currently supported PHP platforms on Elastic Beanstalk; you’ll want to decide which Solution Stack Name (eg. “64bit Amazon Linux 2 v3.4.1 running PHP 8.1”) corresponds to the platform you wish to upgrade to.
  2. In the Elastic Beanstalk web configuration page, get the environment ID for the environment you wish to upgrade (eg. “e-jwfjvy57r3”).
  3. Run the aws command to upgrade the environment in place:
    aws elasticbeanstalk update-environment --solution-stack-name "64bit Amazon Linux 2 v3.4.1 running PHP 8.1" --environment-id "e-2iuwivbgzc" --region "ca-central-1"

Reference:

Regular expressions: Find instances when the string does not equal a certain value

Another typical regex scenario: You’re looking through some data (like a JSON object) and are looking for when a value is actually being set. Let’s say our string contains:

"puttLength": null, 

Typically, you’d write a regular expression to capture the <em> tag by writing this:</em>

"puttLength": ((?!null).)*$

The use of ?! is the instruction to make a negative lookahead match, ensuring null does not exist before the line can be returned as a match. A terrific example / tool to test in your browser exists at https://www.regextester.com/15 .

Measure current IOPS usage on a Linux server

This is most easily measured by installing the sysstat package:

sudo yum install sysstat -y

And running the sar command, looking at the value of tps:

sar -d 1

Getting a recursive list of files sorted by last modified date in Linux

Here’s a quick way to get a recursive list of files, sorted in descending order by the last modified date, from any folder in *nix:

find . -type f -printf "%T@ %p\0" | sort -zk1nr | xargs -0 printf "%s\n"

Alpha males and females are the most empathetic… and the most stressed

A few months ago I was struck by the mention of a study of chimpanzee society that found that the leader tended to demonstrate deep caring and empathy for members of their pack; empathy ultimately being a necessary aspect of their personality that benefited their ability to rise and maintain a leadership position. Below, Leo Widrich has a terrific summary of the phenomenon:

Leo Widrich – The surprising science of alpha males & females: The most empathetic member of the group

One of the things that completely baffled me, but upon closer inspection, makes a lot of sense, was his finding that any alpha in a group of chimpanzees always happens to be the most empathetic member. He analyzed this by studying empathic behavior like consoling, hugging and other indicating moves. Although generally females in any group tend to offer more empathetic behavior to the members of the group, when it comes to the alpha, they show a level of empathy that is way off the charts compared to the rest.

A second insight that I took from de Waal’s research, was that the stress level of the highest-ranking members of a group, as he studied it in chimpanzees and bonobos were also the most stressed members of the group.

Permutations and combinations: What they are, and the formula to determine them

Permutations

A selection of objects in which the order of the objects matters.

nPk = n! / (n - k)!

Example: If we wanted to get the number of permutations in which 6 people could be seated into 4 chairs, we’d find that it would be 360 permutations in total:

6P4 = 6! / (6 - 4)! = 720 / 2 = 360

Combinations

The number of possible combination of r objects from a set on n objects.

nCk = n! / ( k! * (n - k)! )

Example: Let’s say instead we want to get the number of combinations in which 6 people could be seated into 4 chairs; we’d find that it would be 15 combinations in total:

6C4 = 6! / ( 4! * (6 - 4)! ) = 720 / ( 24 * 2 ) = 720 / 48 = 15

References