Skip to content

Instantly share code, notes, and snippets.

Oliver Tappin olivertappin

Block or report user

Report or block olivertappin

Hide content and notifications from this user.

Learn more about blocking users

Contact Support about this user’s behavior.

Learn more about reporting abuse

Report abuse
View GitHub Profile
@olivertappin
olivertappin / show-blocking-transactions.sql
Created Nov 16, 2019
Run this query to check for a blocking transaction
View show-blocking-transactions.sql
SELECT
trx_w.trx_id AS waiting_trx_id,
trx_w.trx_mysql_thread_id AS waiting_process_id,
TIMESTAMPDIFF(SECOND, trx_w.trx_wait_started, CURRENT_TIMESTAMP) AS wait_time,
trx_w.trx_query AS waiting_query,
l.lock_table AS waiting_table_lock,
trx_b.trx_id AS blocking_trx_id,
trx_b.trx_mysql_thread_id AS blocking_process_id,
CONCAT(pl.user, '@', pl.host) AS blocking_user,
pl.command,
@olivertappin
olivertappin / database-table-counts.sql
Created May 10, 2019
Show counts of all table rows from a particular database using information_schema
View database-table-counts.sql
SELECT
TABLE_NAME AS DATABASE_NAME,
FORMAT(TABLE_ROWS, 0) AS Rows
FROM information_schema.tables
WHERE
table_schema = 'DATABASE_NAME'
AND TABLE_ROWS > 0
;
@olivertappin
olivertappin / column-entry-creation.sql
Created Mar 26, 2019
Return the column entry for a SHOW CREATE TABLE (based on primary key or column name)
View column-entry-creation.sql
SELECT RTRIM(CONCAT(
'`',
COLUMN_NAME,
'`',
' ',
COLUMN_TYPE,
' ',
IF (COLLATION_NAME IS NULL, '', CONCAT('COLLATE ', COLLATION_NAME, ' ')),
IF (IS_NULLABLE = 'YES', '', 'NOT NULL '),
IF (COLUMN_DEFAULT IS NULL, '', CONCAT('DEFAULT ', COLUMN_DEFAULT, ' ')),
@olivertappin
olivertappin / gcloud-helpers.sh
Created Feb 25, 2019
Google Cloud helper functions to access instances from the command line
View gcloud-helpers.sh
# SSH into an instance using the instance name (supports partial matching)
# Usage: gcessh <instance-name>
gcessh() { $(gcloud compute instances list --filter="name~'$1'" --sort-by name | tail -n +2 | head -1 | awk '{print "ssh "$1}' ) 2>/dev/null; };
# Describe an instance using the instance name (exact match only)
# Usage: gcedesc <instance-name>
gcedesc() { $(gcloud compute instances list | grep $1 | awk '{print "gcloud compute instances describe "$1" --zone "$2}'); };
# List all matching instances using a partially matched instance name (perfect for listing nodes from an instance group)
# Usage: gcelsn <instance-name>
@olivertappin
olivertappin / consul-kv-export.php
Created Feb 13, 2019
Export all data from the Consul KV store into consul kv put commands
View consul-kv-export.php
<?php
$output = shell_exec('consul kv export');
if (null === $output) {
echo 'Could not run `consul kv export`. Is consul installed?' . PHP_EOL;
exit(1);
}
$data = json_decode($output, true);
@olivertappin
olivertappin / deploy.sh
Created Jan 31, 2019
Atomic symlinked deployments
View deploy.sh
BASE="/var/www/project"
RELEASES="$BASE/releases"
RELEASE="$RELEASES/`date +%s`"
SHARED="$BASE/shared"
CACHE="$BASE/deploy-cache"
cp -a $CACHE $RELEASE
cd $RELEASE
####### [Pre-launch commands] #######
@olivertappin
olivertappin / PostcodeLookupController.php
Created Dec 9, 2018
Postcode Lookup using the Google Geocoding API
View PostcodeLookupController.php
<?php
namespace App\Http\Controllers\Api;
use GuzzleHttp\Client;
use Illuminate\Http\Request;
use App\Http\Controllers\Controller as BaseController;
use Illuminate\Http\Response;
use App\Mappers\Response\Address as AddressMapper;
@olivertappin
olivertappin / vote.php
Created Oct 17, 2018
Automatically vote on a SurveyMonkey survey, and make it go your way.
View vote.php
<?php
// Usage: Upload to a random server of your choice and send the URL to http://browsershots.org (or similar)
// Prerequisites:
// composer require fabpot/goutte
include 'vendor/autoload.php';
use Goutte\Client;
@olivertappin
olivertappin / update-server-datetime.sh
Created Sep 24, 2018
Update server date time using Google
View update-server-datetime.sh
date -s "$(curl -s --head http://google.com | grep ^Date: | sed 's/Date: //g')"
@olivertappin
olivertappin / track-disk-usage.sh
Last active Aug 8, 2018
Track disk related issues for MySQL ERROR 1034 (HY000) during an ALTER TABLE
View track-disk-usage.sh
# During an ALTER TABLE run, if you experience the following error:
#
# mysql> ALTER TABLE `my_table` ALGORITHM=INPLACE, ENGINE=InnoDB;
# ERROR 1034 (HY000): Incorrect key file for table 'my_table'; try to repair it
#
# It may be disk related. To track this, run the following commands (in a screen session)
# and leave them running whilst running the ALTER again. After the error displays, check
# the files for obvious signs of disk related issues. Please note, the results from the
# df-full.log may not be accurate (on their own), so use this with the lsof.log
# Also, please ensure the following commands are run seperatley in parallel (with seperate
You can’t perform that action at this time.