Tag: data-management

  • Bash Script: Calculate before/after 2: Calculate Harder

    Bash Script: Calculate before/after 2: Calculate Harder

    As an update, or an evolution of my earlier script that did some simple math for me, I’ve made one that will full-on test a URL while I’m making changes to see what the impact performance is of my updates.

    $ abtesturl.sh --url=https://example.com/ --count=10
    Press any key to run initial tests...
    Initial average TTFB: 3.538 seconds
    Press any key to re-run tests...
    
    Running second test...
    Second average TTFB: 1.975 seconds
    Before TTFB: 3.538 seconds
    After TTFB: 1.975 seconds
    Change in TTFB: -1.563 seconds
    Percentage Change: -44.00%Code language: JavaScript (javascript)

    It makes it much simpler to gather data to write reports or figure out of a change is worth the effort.

    Well, that’s about it so here’s the script:

    #!/bin/bash
    
    function show_usage() {
    	echo "Usage: $0 --url=<URL> [--count=<number of requests>]"
    	echo "  --url        Specifies the URL to test."
    	echo "  --count      Optional. Specifies the number of requests to send. Default is 6."
    	echo
    	echo "Example: $0 --url=https://example.com/ --count=5"
    	exit
    }
    
    function average_ttfb() {
    	local URL=""
    	local COUNT=6 # Default COUNT to 6 if not supplied
    	local CURL_OPTS="-s"
    
    	# Parse arguments
    	for arg in "$@"; do
    		case $arg in
    			--url=*)
    			URL="${arg#*=}"
    			shift # Remove argument from processing
    			;;
    		--count=*)
    			COUNT="${arg#*=}"
    			shift # Remove argument from processing
    			;;
    		*)
    			# Unknown option
    			;;
    		esac
    	done
    
    	if [[ -z "$URL" ]]; then
    		exit 1
    	fi
    
    	local total_time=0
    	local count_success=0
    
    	for ((i=1; i<=COUNT; i++))
    	do
    		# Perform the curl command, extracting the time to first byte
    		ttfb=$(curl $CURL_OPTS -o /dev/null -w "%{time_starttransfer}\n" $URL)
    		
    		# Check if the curl command was successful
    		if [ $? -eq 0 ]; then
    			total_time=$(echo "$total_time + $ttfb" | bc)
    			((count_success++))
    		else
    			echo "Request $i failed." >&2
    		fi
    	done
    
    	if [ $count_success -eq 0 ]; then
    		echo "All requests failed." >&2
    		return 1
    	fi
    
    	# Calculate the average TTFB
    	average_time=$(echo "scale=3; $total_time / $count_success" | bc)
    	echo $average_time # This line now simply outputs the average time
    }
    
    function ab_test_ttfb() {
    	# Run initial test
    	read -p "Press any key to run initial tests..." -n 1 -r
    	initial_ttfb=$(set -e; average_ttfb "$@"; set +e)
    	echo "Initial average TTFB: $initial_ttfb seconds"
    	
    	# Wait for user input to proceed
    	read -p "Press any key to re-run tests..." -n 1 -r
    	echo # Move to a new line
    
    	# Run second test
    	echo "Running second test..."
    	second_ttfb=$(average_ttfb "$@")
    	echo "Second average TTFB: $second_ttfb seconds"
    
    	# Calculate and output the difference and percentage change
    	difference=$(echo "$second_ttfb - $initial_ttfb" | bc)
    	percent_change=$(echo "scale=2; ($difference / $initial_ttfb) * 100" | bc)
    
    	echo "Before TTFB: $initial_ttfb seconds"
    	echo "After TTFB: $second_ttfb seconds"
    	echo "Change in TTFB: $difference seconds"
    	echo "Percentage Change: $percent_change%"
    }
    
    # Check if help is requested or no arguments are provided
    if [[ " $* " == *" --help "* ]] || [[ "$#" -eq 0 ]]; then
    	show_usage
    fi
    
    # Check if --url is in the arguments
    url_present=false
    for arg in "$@"; do
    	if [[ $arg == --url=* ]]; then
    		url_present=true
    		break
    	fi
    done
    
    if [ "$url_present" = false ]; then
    	echo "Error: --url argument is required."
    	show_usage
    fi
    
    ab_test_ttfb "$@"Code language: Bash (bash)

    Don’t break anything!

  • Gathering database performance with WP-CLI

    Gathering database performance with WP-CLI

    Recently at work, my team was asked to help gather data about database server performance before and after an upgrade. To help with this, we collected a number of heavy database pages on some WordPress sites, dumped every query running to generate the page, and grabbed them to profile.

    I whipped up this quick and dirty WP-CLI command that will run a list of SQL queries 1,000 times and give you the minimum time, maximum time, average time, and standard deviation for each query.

    With this data, you can re-run the same queries again after a server change to determine if there has been any major SQL performance differences:

    /**
     * Profiles DB performance by running SQL queries and returning timing statistics.
     */
    public function db_profile( $args, $assoc_args ) {
    	$format = WP_CLI\Utils\get_flag_value( $assoc_args, 'format', 'table' );
    
    // Put all of your queries here!
    $site_sql_lines = <<<END
    SHOW TABLES LIKE 'wp_posts';
    END;
    
    	$lines = explode( PHP_EOL, $site_sql_lines );
    
    	global $wpdb;
    
    	$stats         = array();
    	$total_time_us = 0;
    	$runs          = 100;
    	$progress      = \WP_CLI\Utils\make_progress_bar( sprintf( 'Running %s Queries', number_format( count( $lines ) * $runs ) ), count( $lines ) * $runs );
    
    	for ( $run = 1; $run <= $runs; $run++ ) {
    		foreach ( $lines as $index => $line ) {
    			if ( ! isset( $stats[ $index ] ) || ! is_array( $stats[ $index ] ) ) {
    				$stats[ $index ] = array(
    					'query' => $line,
    					'runs'  => array(),
    				);
    			}
    
    			$start_time = hrtime( true );
    			$results    = count( $wpdb->get_results( $line, ARRAY_N ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.NotPrepared
    			$end_time   = hrtime( true );
    			$time_us    = ( $end_time - $start_time ) / 1000;
    
    			$total_time_us += $time_us;
    
    			$stats[ $index ]['runs'][ $run ] = $time_us;
    			$progress->tick();
    		}
    	}
    
    	$data = array();
    
    	foreach ( $stats as $index => $stat ) {
    		$stats[ $index ]['stats'] = array(
    			'min' => min( $stats[ $index ]['runs'] ),
    			'max' => max( $stats[ $index ]['runs'] ),
    			'avg' => array_sum( $stats[ $index ]['runs'] ) / count( $stats[ $index ]['runs'] ),
    		);
    
    		$data[ $index ] = array(
    			'Query'  => $stat['query'],
    			'Min'    => number_format( (float) $stats[ $index ]['stats']['min'] / 1000, 3 ),
    			'Max'    => number_format( (float) $stats[ $index ]['stats']['max'] / 1000, 3 ),
    			'Avg'    => number_format( (float) $stats[ $index ]['stats']['avg'] / 1000, 3 ),
    			'StdDev' => number_format( (float) $this->stats_standard_deviation( $stats[ $index ]['runs'] ) / 1000, 3 ),
    		);
    
    		if ( 'csv' === $format ) {
    			$data[ $index ]['Runs'] = wp_json_encode( $stats[ $index ]['runs'] );
    		}
    	}
    
    	$progress->finish();
    
    	// Output data.
    	WP_CLI\Utils\format_items( $format, $data, array_keys( $data[0] ) );
    
    	// Output totals if we're not piping somewhere.
    	if ( ! WP_CLI\Utils\isPiped() ) {
    		WP_CLI::success(
    			sprintf(
    				'Total queries ran: %s, DB Time Taken: %s',
    				WP_CLI::colorize( '%g' . number_format( count( $lines ) * $runs, 0 ) . '%n' ),
    				WP_CLI::colorize( '%g' . $this->convert_to_human_readable( $total_time_us ) . '%n' ),
    			)
    		);
    	}
    }
    
    Code language: PHP (php)

    You can then run it and gather the data as a table, or as a CSV file (--format=csv) which is much more likely to be useful.

  • Half Baked Idea: Limiting WordPress Image Upload Sizes

    Half Baked Idea: Limiting WordPress Image Upload Sizes

    If you want to be able to limit images (or any attachments) from being uploaded into the media library if they are too large, you can use the wp_handle_upload_prefilter filter to do this.

    Below is a really basic example I whipped up in a few minutes that I shared with a customer not too long ago:

    /**
     * Limits the maximum size of images that can be uploaded.
     *
     * @param array $file {
     *     Reference to a single element from `$_FILES`.
     *
     *     @type string $name     The original name of the file on the client machine.
     *     @type string $type     The mime type of the file, if the browser provided this information.
     *     @type string $tmp_name The temporary filename of the file in which the uploaded file was stored on the server.
     *     @type int    $size     The size, in bytes, of the uploaded file.
     *     @type int    $error    The error code associated with this file upload.
     * }
     *
     * @return array       Filtered file array, with a possible error if the file is too large.
     */
    function blarg_limit_max_image_upload_size( $file ) {
        if ( str_starts_with( $file['type'], 'image/' ) ) {
            $max_size = 1 * 1024; // 1kb.
    
            if ( $file['size'] > $max_size ) {
                // translators: %s: Human readable maximum image file size.
                $file['error'] .= sprintf( __( 'Uploaded images must be smaller than %s.' ), size_format( $max_size ) );
            }
        }
    
        return $file;
    
    }
    add_filter( 'wp_handle_upload_prefilter', 'blarg_limit_max_image_upload_size', 10, 1 );Code language: PHP (php)

    Good luck if you use any of my hot garbage in production 😀

  • Fixing a broken ATOM Feed

    Fixing a broken ATOM Feed

    My city is not known for being technologically adept, and I’m at least lucky they have a website with a CMS. Sadly though, the website offers only a broken ATOM 1.0 feed, a standard that’s old enough to drink in some countries.

    Unfortunately, this doesn’t work with NewsBlur, so I had to sort to building a proxy that would parse the XML and output a JSON Feed.

    Through the power of Phpfastcache (only for a little bit of caching), I am embarassed to show you this cobbled together mess:

    <?php
    define( 'DEBUG', false );
    
    if ( defined( 'DEBUG') && DEBUG ) {
    	ini_set('display_errors', 1);
    	ini_set('display_startup_errors', 1);
    	error_reporting(E_ALL);
    }
    
    use Phpfastcache\Helper\Psr16Adapter;
    require 'vendor/autoload.php';
    
    $cache     = new Psr16Adapter( 'Files' );
    $url       = 'https://www.cityoflinton.com/egov/api/request.egov?request=feed;dateformat=%25B%20%25d%20at%20%25X%23%23%25b%2B%2B%25d;featured=3;title=Upcoming%20Events;ctype=1;order=revdate';
    $cache_key = 'atom-feed_' . md5( $url );
    
    // Get and/or fill the cache.
    if ( ! $cache->has( $cache_key ) ) {
    	$atom_feed = file_get_contents( $url );
    	$cache->set( $cache_key, $atom_feed, 60 * 60 * 1 ); // 1 hour.
    } else {
    	$atom_feed = $cache->get( $cache_key );
    }
    
    $feed_data = new SimpleXMLElement( $atom_feed );
    
    $json_feed = array(
    	'version'       => 'https://jsonfeed.org/version/1.1',
    	'title'         => filter_var( trim( $feed_data->title ) ?? 'Upcoming Events for the City of Linton', FILTER_SANITIZE_STRING ),
    	'home_page_url' => 'https://www.cityoflinton.com/',
    	'feed_url'      => 'https://decarbonated.org/tools/cityoflinton-rss/',
    	'language'      => 'en-US',
    	'items'         => array(),
    );
    
    foreach( $feed_data->entry as $entry ) {
    	$json_feed['items'][] = array(
    		'id'            => md5( $entry->id ),
    		'url'           => filter_var( trim( $entry->link['href'] ) ?? 'NO LINK FOUND', FILTER_SANITIZE_URL ),
    		'title'         => filter_var( trim( $entry->title ) ?? 'NO TITLE FOUND', FILTER_SANITIZE_STRING ),
    		'content_text'  => filter_var( trim( $entry->summary ) ?? 'NO CONTENT FOUND', FILTER_SANITIZE_STRING ),
    		'date_modified' => ( new DateTime( trim( $entry->updated ) ?? now(), new DateTimeZone( 'America/New_York' ) ) )->format( DateTimeInterface::RFC3339 ),
    	);
    }
    
    if ( defined( 'DEBUG ' ) && DEBUG ) {
    	header( 'Content-Type: text/plain' );
    	var_dump( $json_feed );
    } else {
    	header( 'Content-Type: application/feed+json' );
    	echo json_encode( $json_feed );
    }
    Code language: HTML, XML (xml)

    This will convert the XML from (prettified):

    <?xml version="1.0" encoding="ISO-8859-1"?>
    <feed xmlns="http://www.w3.org/2005/Atom">
    	<title>Upcoming Events</title>
    	<link rel="self" href="https://www.cityoflinton.com/egov/api/request.egov?request=feed;dateformat=%25B%20%25d%20at%20%25X%23%23%25b%2B%2B%25d;featured=3;title=Upcoming%20Events;ctype=1;order=revdate" />
    	<updated>2021-12-09T10:14:40</updated>
    	<id>https://www.cityoflinton.com/egov/api/request.egov?request=feed;dateformat=%25B%20%25d%20at%20%25X%23%23%25b%2B%2B%25d;featured=3;title=Upcoming%20Events;ctype=1;order=revdate</id>
    	<author>
    		<name>Organization Information</name>
    	</author>
    	<entry>
    		<title>City Hall Closed</title>
    		<link rel="alternate" href="https://www.cityoflinton.com/egov/apps/events/calendar.egov?view=detail;id=501" />
    		<updated>2021-12-09T10:14:40</updated>
    		<id>https://www.cityoflinton.com/egov/apps/events/calendar.egov?view=detail;id=501</id>
    		<featured>0</featured>
    		<summary type="html">City Hall Closed</summary>
    	</entry>
    </feed>Code language: HTML, XML (xml)

    to JSON like:

    {
      "version": "https://jsonfeed.org/version/1.1",
      "title": "Upcoming Events",
      "home_page_url": "https://www.cityoflinton.com/",
      "feed_url": "https://decarbonated.org/tools/cityoflinton-rss/",
      "language": "en-US",
      "items": [
        {
          "id": "9b0bcc229cdc4266e539a785d77b4a8f",
          "url": "https://www.cityoflinton.com/egov/apps/events/calendar.egov?view=detail;id=501",
          "title": "City Hall Closed",
          "content_text": "City Hall Closed",
          "date_modified": "2021-12-09T10:14:40-05:00"
        }
      ]
    }Code language: JSON / JSON with Comments (json)

    That’s all ¯\_(ツ)_/¯

  • Quick Tip: Get Size of Revisions in WordPress

    Quick Tip: Get Size of Revisions in WordPress

    One thing that you might not think of when watching the size of a large WordPress site grow, is unnecessary data in the database. With the introduction of the block editor years ago, there has been a large increase in the number of revisions a post makes when being edited.

    This can create a lot of revisions in the database if you’re not setting a limit.

    If you’d like to do a quick and dirty audit of your revision data, you can use a very ugly SQL query like this:

    SELECT COUNT( ID ) as revision_count, ( SUM( CHAR_LENGTH( ID ) ) + SUM( CHAR_LENGTH( post_author ) ) + SUM( CHAR_LENGTH( post_date ) ) + SUM( CHAR_LENGTH( post_date_gmt ) ) + SUM( CHAR_LENGTH( post_content ) ) + SUM( CHAR_LENGTH( post_title ) ) + SUM( CHAR_LENGTH( post_excerpt ) ) + SUM( CHAR_LENGTH( post_status ) ) + SUM( CHAR_LENGTH( comment_status ) ) + SUM( CHAR_LENGTH( ping_status ) ) + SUM( CHAR_LENGTH( post_password ) ) + SUM( CHAR_LENGTH( post_name ) ) + SUM( CHAR_LENGTH( to_ping ) ) + SUM( CHAR_LENGTH( pinged ) ) + SUM( CHAR_LENGTH( post_modified ) ) + SUM( CHAR_LENGTH( post_modified_gmt ) ) + SUM( CHAR_LENGTH( post_content_filtered ) ) + SUM( CHAR_LENGTH( post_parent ) ) + SUM( CHAR_LENGTH( guid ) ) + SUM( CHAR_LENGTH( menu_order ) ) + SUM( CHAR_LENGTH( post_type ) ) + SUM( CHAR_LENGTH( post_mime_type ) ) + SUM( CHAR_LENGTH( comment_count ) ) ) as post_size FROM wp_posts WHERE post_type='revision' GROUP BY post_type ORDER BY revision_count DESC;Code language: JavaScript (javascript)

    This will give you the number of revisions you have, and the approximate amount of data its using in the database:

    revision_count post_size
    441419 2842450412

    You can see now a very large WordPress site can amass a lot of unnecessary data in its database over a number of years. 2.8 Gigabytes of revisions is a lot of stuff if you’re never going to use them again.

  • My Favorite Firefox Addons

    My Favorite Firefox Addons

    For my own posterity in case I ever lose them, or if anyone is curious, here’s what I use:

    https://addons.mozilla.org/en-US/firefox/addon/1password-x-password-manager/
    https://addons.mozilla.org/en-US/firefox/addon/clearurls/
    https://addons.mozilla.org/en-US/firefox/addon/edit-cookie/
    https://addons.mozilla.org/en-US/firefox/addon/decentraleyes/
    https://addons.mozilla.org/en-US/firefox/addon/duckduckgo-for-firefox/
    https://addons.mozilla.org/en-US/firefox/addon/facebook-container/
    https://addons.mozilla.org/en-US/firefox/addon/image-search-options/
    https://addons.mozilla.org/en-US/firefox/addon/old-reddit-redirect/
    https://addons.mozilla.org/en-US/firefox/addon/recipe-filter/
    https://addons.mozilla.org/en-US/firefox/addon/reddit-enhancement-suite/
    https://addons.mozilla.org/en-US/firefox/addon/soundcloud-dl/
    https://addons.mozilla.org/en-US/firefox/addon/sponsorblock/
    https://addons.mozilla.org/en-US/firefox/addon/the-camelizer-price-history-ch/
    https://addons.mozilla.org/en-US/firefox/addon/ublock-origin/
    https://addons.mozilla.org/en-US/firefox/addon/view-image-in-google-images/
  • Getting WordPress Database Size via WP-CLI

    Getting WordPress Database Size via WP-CLI

    One WP-CLI command that I’ve found handy is this db-size command. It allows you to output a site’s registered database tables along with the data and index size in any format that WP-CLI natively supports, with multiple sort options:

    /**
     * Gets size of database tables for the current site.
     *
     * ## OPTIONS
     *
     * [--raw]
     * : Outputs full size in bytes instead of human readable sizes.
     *
     * [--order_by=<Total><total>]
     * : Allows custom ordering of the data.
     * ---
     * default: Total
     * options:
     *   - Table
     *   - Data Size
     *   - Index Size
     *   - Total
     * ---
     *
     * [--order=<asc><asc>]
     * : Allows custom ordering direction of the data.
     * ---
     * default: asc
     * options:
     *   - asc
     *   - desc
     * ---
     *
     * [--format=<format><format>]
     * : Render output in a particular format.
     * ---
     * default: table
     * options:
     *   - table
     *   - csv
     *   - json
     *   - count
     *   - yaml
     * ---
     *
     * @subcommand db-size
     */
    public function db_size( $args, $assoc_args ) {
    	global $wpdb;
    
    	$output   = array();
    	$db_size  = array();
    	$order_by = WP_CLI\Utils\get_flag_value( $assoc_args, 'order_by', 'Total' );
    	$order    = WP_CLI\Utils\get_flag_value( $assoc_args, 'order', 'asc' );
    	$format   = WP_CLI\Utils\get_flag_value( $assoc_args, 'format', 'table' );
    	$raw      = (bool) WP_CLI\Utils\get_flag_value( $assoc_args, 'raw', false );
    
    	// Fetch list of tables from database.
    	$tables = array_map(
    		function( $val ) {
    			return $val[0];
    		},
    		$wpdb->get_results( 'SHOW TABLES;', ARRAY_N ) // phpcs:ignore WordPress.DB.DirectDatabaseQuery
    	);
    
    	// Fetch table information from database.
    	$report = array_map(
    		function( $table ) use ( $wpdb ) { // phpcs:ignore WordPress.DB.DirectDatabaseQuery
    			return $wpdb->get_row( "SHOW TABLE STATUS LIKE '$table'" ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
    		},
    		$tables
    	);
    
    	foreach ( $report as $table ) {
    		// Keep a running total of sizes.
    		$db_size['data']  += $table->Data_length; // phpcs:ignore WordPress.NamingConventions.ValidVariableName.UsedPropertyNotSnakeCase
    		$db_size['index'] += $table->Index_length; // phpcs:ignore WordPress.NamingConventions.ValidVariableName.UsedPropertyNotSnakeCase
    
    		// Format output for WP-CLI's format_items function.
    		$output[] = array(
    			'Table'      => $table->Name, // phpcs:ignore WordPress.NamingConventions.ValidVariableName.UsedPropertyNotSnakeCase
    			'Data Size'  => $table->Data_length, // phpcs:ignore WordPress.NamingConventions.ValidVariableName.UsedPropertyNotSnakeCase
    			'Index Size' => $table->Index_length, // phpcs:ignore WordPress.NamingConventions.ValidVariableName.UsedPropertyNotSnakeCase
    			'Total'      => $table->Data_length + $table->Index_length, // phpcs:ignore WordPress.NamingConventions.ValidVariableName.UsedPropertyNotSnakeCase
    		);
    	}
    
    	// Sort table data.
    	self::sort_table_by( $order_by, $output, $order );
    
    	if ( ! $raw ) {
    		// Make data human readable.
    		foreach ( array_keys( $output ) as $key ) {
    			$output[ $key ]['Data Size']  = size_format( $output[ $key ]['Data Size'] );
    			$output[ $key ]['Index Size'] = size_format( $output[ $key ]['Index Size'] );
    			$output[ $key ]['Total']      = size_format( $output[ $key ]['Total'] );
    		}
    	}
    
    	// Output data.
    	WP_CLI\Utils\format_items( $format, $output, array( 'Table', 'Data Size', 'Index Size', 'Total' ) );
    
    	// Output totals if we're not piping somewhere.
    	if ( ! WP_CLI\Utils\isPiped() ) {
    		WP_CLI::success(
    			sprintf(
    				'Total size of the database for %s is %s. Data: %s; Index: %s',
    				home_url(),
    				WP_CLI::colorize( '%g' . size_format( $db_size['data'] + $db_size['index'] ) . '%n' ),
    				WP_CLI::colorize( '%g' . size_format( $db_size['data'] ) . '%n' ),
    				WP_CLI::colorize( '%g' . size_format( $db_size['index'] ) . '%n' )
    			)
    		);
    	}
    }
    
    /**
     * Sorts a table by a specific field and direction.
     *
     * @param string $field The field to order by.
     * @param array  &$array The table array to sort.
     * @param string $direction The direction to sort. Ascending ('asc') or descending ('desc').
     */
    private function sort_table_by( $field, &$array, $direction ) {
    	// Taken from https://joshtronic.com/2013/09/23/sorting-associative-array-specific-key/ Thanks!
    	usort(
    		$array,
    		function ( $a, $b ) use ( $field, $direction ) {
    			$a = $a[ $field ];
    			$b = $b[ $field ];
    
    			if ( $a === $b ) {
    				return 0;
    			}
    
    			switch ( $direction ) {
    				case 'asc':
    					return ( $a < $b ) ? -1 : 1;
    				case 'desc':
    					return ( $a > $b ) ? -1 : 1;
    				default:
    					return 0;
    			}
    		}
    	);
    	return true;
    }</format></asc></total>Code language: PHP (php)

    Here’s some example output from one of my test sites:

    $ wp test db-size
     +----------------------------+-----------+------------+-------+
     | Table                      | Data Size | Index Size | Total |
     +----------------------------+-----------+------------+-------+
     | wp_mlp_relationships       | 16KB      | 0B         | 16KB  |
     | wp_redacted_table          | 16KB      | 0B         | 16KB  |
     | wp_redacted_table          | 16KB      | 0B         | 16KB  |
     | wp_3_links                 | 16KB      | 16KB       | 32KB  |
     | wp_term_relationships      | 16KB      | 16KB       | 32KB  |
     | wp_site                    | 16KB      | 16KB       | 32KB  |
     | wp_registration_log        | 16KB      | 16KB       | 32KB  |
     | wp_mlp_site_relations      | 16KB      | 16KB       | 32KB  |
     | wp_mlp_languages           | 16KB      | 16KB       | 32KB  |
     | wp_mlp_content_relations   | 16KB      | 16KB       | 32KB  |
     | wp_links                   | 16KB      | 16KB       | 32KB  |
     | wp_redacted_table          | 16KB      | 16KB       | 32KB  |
     | wp_3_term_relationships    | 16KB      | 16KB       | 32KB  |
     | wp_blog_versions           | 16KB      | 16KB       | 32KB  |
     | wp_2_links                 | 16KB      | 16KB       | 32KB  |
     | wp_2_term_relationships    | 16KB      | 16KB       | 32KB  |
     | wp_2_term_taxonomy         | 16KB      | 32KB       | 48KB  |
     | wp_2_commentmeta           | 16KB      | 32KB       | 48KB  |
     | wp_2_postmeta              | 16KB      | 32KB       | 48KB  |
     | wp_term_taxonomy           | 16KB      | 32KB       | 48KB  |
     | wp_3_commentmeta           | 16KB      | 32KB       | 48KB  |
     | wp_2_termmeta              | 16KB      | 32KB       | 48KB  |
     | wp_2_terms                 | 16KB      | 32KB       | 48KB  |
     | wp_termmeta                | 16KB      | 32KB       | 48KB  |
     | wp_commentmeta             | 16KB      | 32KB       | 48KB  |
     | wp_blogmeta                | 16KB      | 32KB       | 48KB  |
     | wp_blogs                   | 16KB      | 32KB       | 48KB  |
     | wp_2_a8c_cron_control_jobs | 16KB      | 32KB       | 48KB  |
     | wp_redacted_table          | 16KB      | 32KB       | 48KB  |
     | wp_3_terms                 | 16KB      | 32KB       | 48KB  |
     | wp_3_termmeta              | 16KB      | 32KB       | 48KB  |
     | wp_3_term_taxonomy         | 16KB      | 32KB       | 48KB  |
     | wp_redacted_table          | 16KB      | 32KB       | 48KB  |
     | wp_terms                   | 16KB      | 32KB       | 48KB  |
     | wp_3_postmeta              | 16KB      | 32KB       | 48KB  |
     | wp_usermeta                | 16KB      | 32KB       | 48KB  |
     | wp_sitemeta                | 16KB      | 32KB       | 48KB  |
     | wp_users                   | 16KB      | 48KB       | 64KB  |
     | wp_postmeta                | 16KB      | 48KB       | 64KB  |
     | wp_posts                   | 16KB      | 64KB       | 80KB  |
     | wp_signups                 | 16KB      | 64KB       | 80KB  |
     | wp_2_posts                 | 16KB      | 64KB       | 80KB  |
     | wp_3_posts                 | 16KB      | 64KB       | 80KB  |
     | wp_2_comments              | 16KB      | 80KB       | 96KB  |
     | wp_comments                | 16KB      | 80KB       | 96KB  |
     | wp_3_comments              | 16KB      | 80KB       | 96KB  |
     | wp_2_options               | 80KB      | 32KB       | 112KB |
     | wp_3_options               | 80KB      | 32KB       | 112KB |
     | wp_options                 | 176KB     | 32KB       | 208KB |
     +----------------------------+-----------+------------+-------+
     Success: Total size of the database for https://www.example.com is 2.6MB. Data: 1MB; Index: 1.5MBCode language: JavaScript (javascript)

    Enjoy!

  • Quick Tip: Export WordPress SQL output via WP-CLI

    Quick Tip: Export WordPress SQL output via WP-CLI

    If for some reason you can’t run wp db query, but need to export SQL output to a CSV or other file, then have a look at this small WP-CLI command I whipped up that should allow this:

    /**
     * Runs a SQL query against the site database.
     *
     * ## OPTIONS
     *
     * 
     * : SQL Query to run.
     *
     * [--format=]
     * : Render output in a particular format.
     * ---
     * default: table
     * options:
     * - table
     * - csv
     * - json
     * - count
     * - yaml
     * ---
     *
     * [--dry-run=]
     * : Performa a dry run
     *
     * @subcommand sql
     */
    public function sql( $args, $assoc_args ) {
         global $wpdb;
     
         $sql     = $args[0];
         $format  = WP_CLI\Utils\get_flag_value( $assoc_args, 'format', 'table' );
         $dry_run = WP_CLI\Utils\get_flag_value( $assoc_args, 'dry-run', 'true' );
     
         // Just some precautions.
         if ( preg_match( '/[update|delete|drop|insert|create|alter|rename|truncate|replace]/i', $sql ) ) {
             WP_CLI::error( 'Please do not modify the database with this command.' );
         }
     
         if ( 'false' !== $dry_run ) {
             WP_CLI::log( WP_CLI::colorize( '%gDRY-RUN%n: <code>EXPLAIN</code> of the query is below: https://mariadb.com/kb/en/explain/' ) );
             $sql = 'EXPLAIN ' . $sql;
         }
     
         // Fetch results from database.
         $results = $wpdb->get_results( $sql, ARRAY_A ); // phpcs:ignore WordPress.DB
     
         // Output data.
         WP_CLI\Utils\format_items( $format, $results, array_keys( $results[0] ) );
    }Code language: PHP (php)

    I’d add an example here, but I don’t have any right now that I can share 😐 I’ll try to find one later (don’t hold your breath on me remembering to do that)

  • phpMyAdmin and MariaDB with Docker

    phpMyAdmin and MariaDB with Docker

    I used to run a MariaDB server on an old Linux machine for working with database dumps and other things, but after moving all of that to a new Raspberry Pi 4, I never really set it back up.

    So instead I thought I’d play with this fancy new Docker stuff all the cool kids are talking about. I ended up getting phpMyAdmin and MariaDB working with Docker on my MacBook Pro.

    Caveat:

    To start up the MariaDB container thing, I ran this command:

    docker run -d \
     -v $HOME:/home/hosthome \
     --name mariadb \
     -e MYSQL_ROOT_PASSWORD=hunter2 \
     -e MYSQL_DATABASE='default_db' \
     mariadbCode language: JavaScript (javascript)

    A few things here you might want to make note of:

    • hunter2 – My MariaDB root password.
    • default_db – The default database created, just to make things easy.
    • $HOME – This attached my local machine’s home directory to the MariaDB container so I can import/export files.

    Secondly, I ran this to start phpMyAdmin and connect it to the Docker container:

    docker run -d \
     --name phpmyadmin \
     --link mariadb:db \
     -p 8081:80 \
     -e UPLOAD_LIMIT='4096M' \
     phpmyadmin/phpmyadminCode language: JavaScript (javascript)

    A few things here you might want to make note of:

    • 8081 – This is the local machine port that I will connect to via HTTP
    • 4096M – The default upload limit, set to 4 Gigs.

    Now, once this done, you should be able to connect to phpMyAdmin via http://localhost:8081/ and do all sorts of terrible things.

    Here’s a few more commands that may come in handy:

    Start a shell in the MariaDB container: docker exec -it mariadb bash

    Import a SQL file: docker exec -i mariadb sh -c 'exec mysql -uroot -phunter2 default_db' < /some/path/on/your/host/all-databases.sql

    Start a MariaDB “mysql” shell: docker exec -it mariadb sh -c 'exec mysql -uroot -phunter2 default_db'

    References that helped me: