Category : Snippets


This requires that metadata lock instrumentation be enabled. UPDATE performance_schema.setup_instruments SET ENABLED = ‘YES’ WHERE NAME = ‘wait/lock/metadata/sql/mdl’; Once that’s in place, you can do this: SELECT   ml.object_schema,   ml.object_name,   p.id,   p.user,   p.state,   ml.lock_type,   ml.lock_duration,   ml.lock_status,   p.time,   LEFT(p.info, 100) FROM   performance_schema.metadata_locks ml   JOIN performance_schema.threads ..

Read more


In attempting to use the mysqldump utility provided with MySQL 8.x against a 5.7 server, you may receive the following error message: mysqldump: Couldn’t execute ‘SELECT COLUMN_NAME, JSON_EXTRACT(HISTOGRAM, ‘$.”number-of-buckets-specified”‘) This is due to the INFORMATION_SCHEMA in 8.0 having more columns to support several new features in 8.x.  This error can be silenced by adding the ..

Read more


gron transforms JSON into discrete assignments to make it easier to grep for what you want and see the absolute ‘path’ to it. It eases the exploration of APIs that return large blobs of JSON but have terrible documentation. ▶ gron “https://api.github.com/repos/tomnomnom/gron/commits?per_page=1” | fgrep “commit.author” json[0].commit.author = {}; json[0].commit.author.date = “2016-07-02T10:51:21Z”; json[0].commit.author.email = “mail@tomnomnom.com”; json[0].commit.author.name ..

Read more


Step 1: There were two nasty queries active that wouldn’t cancel/terminate: pid | seconds | user | querytxt ——-+———+—————+—————————————————————————————————— 9315 | 22223 | rsrootuser | /*MTLN-1.37.4 (build 264)*/ + | | | DROP TABLE IF EXISTS “analysis_ready”.”airdna_airbnb_properties” CASCADE 23925 | 14125 | rsrootuser | /*MTLN-1.37.4 (build 264)*/ + | | | select + | | ..

Read more


SELECT u.usename, s.schemaname, has_schema_privilege (u.usename, s.schemaname, ‘create’) AS user_has_select_permission, has_schema_privilege (u.usename, s.schemaname, ‘usage’) AS user_has_usage_permission FROM pg_user u CROSS JOIN (SELECT DISTINCT schemaname FROM pg_tables) s WHERE u.usename = ‘tylerbullen’ AND s.schemaname = ‘vacasa’; SELECT u.usename, t.schemaname || ‘.’ || t.tablename, has_table_privilege (u.usename, t.tablename, ‘select’) AS user_has_select_permission, has_table_privilege (u.usename, t.tablename, ‘insert’) AS user_has_insert_permission, has_table_privilege (u.usename, ..

Read more


SELECT * FROM stl_scan ss JOIN pg_user pu ON ss.userid = pu.usesysid JOIN svl_query_metrics_summary sqms ON ss.query = sqms.query JOIN temp_mone_tables tmt ON tmt.table_id = ss.tbl AND tmt.table = ss.perm_table_name; SELECT perm_table_name, SUM(ROWS), SUM(bytes) SUM(fetches) FROM stl_scan WHERE starttime >= ‘2018-09-01 00:00:00’ GROUP BY perm_table_name ORDER BY SUM(bytes) DESC..

Read more


Save the SQL part as dump_rs_grants.sql, then run this bash bit: cd /usr/local/cron/dump_rs_grants PGPASSWORD=xxxxxxxxx psql -h redshiftFQDN -p 5439 -Uxxxxxx -dyyyyyy < dump_rs_grants.sql > current_rs_grants.txt dump_rs_grants.sql: WITH object_list(schema_name,object_name,permission_info) AS ( SELECT N.nspname, C.relname, array_to_string(relacl,’,’) FROM pg_class AS C INNER JOIN pg_namespace AS N ON C.relnamespace = N.oid WHERE C.relkind in (‘v’,’r’) AND N.nspname NOT IN ..

Read more


#SCHEMA=”${2}” STARTIME=`date` echo “Starting rowcount update at $STARTIME” # fivetran database DBASE=”fivetran” SCHEMALIST=”greenhouse hubspot jira mandrill marketo” # Truncate rollup table echo “Deleting from rollup table for ${DBASE} schema…” PGPASSWORD=xxxxxxxxxx psql -t -h warehouse.vacasa.services -p 5439 -Uvacasaroot -d${DBASE} -n -q -c “TRUNCATE TABLE admin.overall_rowcount;” # Iterate through schemata for SCHEMA in ${SCHEMALIST} do TABLES=`PGPASSWORD=xxxxxxxxxx psql ..

Read more


#!/bin/bash ################################################################################# # findlockblocks.sh # # Dead-stupid script that leverages existing RS queries and does a mashup that reports # the current running queries that are blocking others, sorted by time running. # # Nice, simple way to see if there’s actually a problem or if RS is just swamped. # # v1.01 2019-06-14 rlbyrd ..

Read more


#!/bin/bash ################################################################################# # do_analyze_by_batch.sh # # Using this query as the source… # # SELECT database, schema || ‘.’ || “table” AS “table”, stats_off # FROM svv_table_info # WHERE stats_off > 5 # ORDER BY 3 DESC, 2; # # …which displays all tables and their stats_off percentage in descending order. # # # Then ..

Read more


— Table level permissions SELECT * FROM ( SELECT schemaname ,objectname ,usename ,HAS_TABLE_PRIVILEGE(usrs.usename, fullobj, ‘select’) AND has_schema_privilege(usrs.usename, schemaname, ‘usage’) AS sel ,HAS_TABLE_PRIVILEGE(usrs.usename, fullobj, ‘insert’) AND has_schema_privilege(usrs.usename, schemaname, ‘usage’) AS ins ,HAS_TABLE_PRIVILEGE(usrs.usename, fullobj, ‘update’) AND has_schema_privilege(usrs.usename, schemaname, ‘usage’) AS upd ,HAS_TABLE_PRIVILEGE(usrs.usename, fullobj, ‘delete’) AND has_schema_privilege(usrs.usename, schemaname, ‘usage’) AS del ,HAS_TABLE_PRIVILEGE(usrs.usename, fullobj, ‘references’) AND has_schema_privilege(usrs.usename, schemaname, ‘usage’) ..

Read more


select relacl , ‘grant ‘ || substring( case when charindex(‘r’,split_part(split_part(array_to_string(relacl, ‘|’),pu.groname,2 ) ,’/’,1)) > 0 then ‘,select ‘ else ” end ||case when charindex(‘w’,split_part(split_part(array_to_string(relacl, ‘|’),pu.groname,2 ) ,’/’,1)) > 0 then ‘,update ‘ else ” end ||case when charindex(‘a’,split_part(split_part(array_to_string(relacl, ‘|’),pu.groname,2 ) ,’/’,1)) > 0 then ‘,insert ‘ else ” end ||case when charindex(‘d’,split_part(split_part(array_to_string(relacl, ‘|’),pu.groname,2 ) ,’/’,1)) ..

Read more


It’s columnar, donchaknow. drop table if exists example.reservation_finance_item_dedupe; create table example.reservation_finance_item_dedupe as (select distinct * from example.reservation_finance_item); select count(*) from example.reservation_finance_item; select count(*) from example.reservation_finance_item_dedupe; drop table if exists example.reservation_finance_item_old; set search_path=example; alter table reservation_finance_item rename to reservation_finance_item_old; alter table reservation_finance_item_dedupe rename to reservation_finance_item; select count(*) from example.reservation_finance_item; select count(*) from example.reservation_finance..

Read more


— SQL snippet to calculate table fragmentation. SELECT table_schema, TABLE_NAME, ROUND(DATA_LENGTH / 1024 / 1024) AS data_length, ROUND(INDEX_LENGTH / 1024 / 1024) AS index_length, ROUND(DATA_FREE / 1024 / 1024) AS data_free, CONCAT( ROUND( ( data_free / (index_length + data_length) ) * 100 ), ‘%’ ) AS frag_ratio FROM information_schema.tables WHERE DATA_FREE > 0 AND TABLE_SCHEMA ..

Read more


#!/bin/bash # Expects: # If no *.last exists, make one. # if one does, diff it and report # Set this manually. Everything else will branch off this. BASEDIR=”/data/backups/ddl/” DBUSER=”svc_dba_adhoc” DBPASS=”ADXEZKZLbgdvcB24nvPu” TZ=”America/Los_Angeles” EMAILTO=”richard.byrd@example.com,mark.butler@example.com,rlbyrd@rlbyrd.com” if [ “$2” == “” ] then echo ” ” echo “USAGE: ddldiff ” echo ” ” fi HOST=${1} SCHEMA=${2} TMPFILE=”/tmp/${HOST}-${SCHEMA}.tmp” /bin/rm ..

Read more


Traverses all integer columns checking for capacity limits. #!/usr/bin/env perl #use 5.010; #use strict; #use warnings; # Richard L. Byrd, written sometime in the 90s and improved bit by bit over the next 20 years # # 2009-01-27: While originally written to take a configfile input on the CLI (–configfile=xxxxx.cfg) I’ve hacked # that out ..

Read more


Another useful database auditor to quickly find possible issues. Expects user and pass on command line. Currently expects to be executed on the same server as the mysqld process. #!/bin/sh # Another useful database auditor to quickly find possible issues. Expects user and pass on command line. # Currently expects to be executed on the ..

Read more


Using the AWS console. Login to the AWS Management Console. Navigate to Redshift dashboard at https://console.aws.amazon.com/redshift/. In the left navigation panel, under Redshift Dashboard, click Clusters. Click Launch Cluster button from the dashboard top menu to start the cluster setup process. On the Cluster Details configuration page, enter a unique name for your new cluster ..

Read more


BASH Shell Redirect Output and Errors To /dev/null How do I redirect output and errors to /dev/null under bash / sh shell scripting? How do I redirect the output of stderr to stdout, and then redirect this combined output to /dev/null? You can send output to /dev/null, by using command >/dev/null syntax. However, this will ..

Read more


Suppose I try to create a table with a primary key that’s varchar(500), and MySQL complains the key length is longer than the maximum of 1000 bytes. 500 is less than 1000. What’s happening? Plus, a tasty (yet apparently harmless) bug in MySQL. Here’s a statement that will fail on most servers: CREATE TABLE test(c ..

Read more


This is a simple script that interactively converts an IP address to its decimal equivalent. This can sometimes be used to bypass web content filtering devices as not all will convert the decimal to an IP and then a hostname. #!/usr/bin/perl # # prompt for an IP print “Enter an IP Address: “; # get ..

Read more


1. Determine what existing range files exist: # cd /etc/sysconfig/network-scripts/ # ls ifcfg-eth1-range* You will see at least one file, possibly several. Find the highest number following the “range” and add one to it. This will be the new range number. For example, if you see ifcfg-eth1-range0 and ifcfg-eth1-range1, your new range number will be ..

Read more


#!/usr/bin/expect -f # catch the date passed on the command line and assign it to a variable set thedate [lindex $argv 0] # connect to remote server spawn scp “user@server:/backups/*$thedate*” /backups_archives ####################### expect { -re “.*es.*o.*” { exp_send “yesr” exp_continue } -re “.*sword.*” { exp_send “your_passwordrr” } } interact Read more about expect here: http://linux.die.net/man/1/expect ..

Read more


To rename a schema (emulating the now-dropped RENAME DATABASE functionality): SELECT concat(‘RENAME TABLE oldSchema.’,table_name, ‘ TO newSchema.’,table_name, ‘;’) FROM information_schema.TABLES WHERE table_schema=’oldSchema’; Execute the resultant sql, then drop the now-empty old sche..

Read more


MySQL is SO irritating in its timestamp limitations, in that you can only have one default timestamp column. (Update 6/12/2013: in version 5.6.x, this restriction will be lifted, so I hear) However, you *can* trick MySQL like this. You can use only one of the definitions in one table. Create both timestamp columns like so: ..

Read more


Various ways to get sizing, counts, etc. — Total rows and size for an entire server, excluding system schemata, ordered by size DESC SELECT table_schema,table_name, CONCAT(ROUND(SUM(table_rows) / 1000000,2),’M’) rows, CONCAT(ROUND(SUM(data_length) / (1024 * 1024 * 1024),2), ‘G’) DATA, CONCAT(ROUND(SUM(index_length) / (1024 * 1024 * 1024), 2),’G’) idx, CONCAT(ROUND(SUM(data_length + index_length) / (1024 * 1024 * ..

Read more


It sucks, but sometimes ya gotta. You can’t DROP multiple tables, a parent table or a child table until you disable foreign key checks four your current database. Or for that matter, TRUNC the table, either. To disable/enable foreign key checks, use the following syntax. (Don’t ever disable @@GLOBAL, unless you’re really irritated and have ..

Read more


mysqldump will backup by default all the triggers but NOT the stored procedures/functions. There are 2 mysqldump parameters that control this behavior: * –routines – FALSE by default * –triggers – TRUE by default This means that if you want to include in an existing backup script also the triggers and stored procedures you only ..

Read more


Call it this way… array_sort($array,’index1′,’index2′,etc.); You can place a ! in front of the index name (e.g., !index1) to indicate a reverse sort. Ensure these two functions are available in the source or associated includes: function array_sort_func($a,$b=NULL) { static $keys; if($b===NULL) return $keys=$a; foreach($keys as $k) { if(@$k[0]==’!’) { $k=substr($k,1); if(@$a[$k]!==@$b[$k]) { return strcmp(@$b[$k],@$a[$k]); } ..

Read more


Possible telnet test output for another database and their meaning is listed below: a) Result when firewall is blocking connection for a IP and port combination: >telnet 20.25.127.140 3306 Trying 20.25.127.140… telnet: Unable to connect to remote host: Connection timed out b) Result when firewall is allowing connections through for IP address and port number ..

Read more


Various crap. SELECT d.id, d.name FROM ireit_analysis.v_ireit_house d, ireit_reference.dictionary_newage r WHERE d.name REGEXP r.word AND ( r.word LIKE ‘%finance%’ || r.definition LIKE ‘%finance%’ || r.word LIKE ‘%bank%’ || r.definition LIKE ‘%bank%’ || r.word LIKE ‘%money%’ || r.definition LIKE ‘%money%’); SELECT d.id, d.name FROM ireit_analysis.v_ireit_house d WHERE d.name STRCMP(SELECT r.word FROM ireit_reference.dictionary_newage r WHERE r.word LIKE ..

Read more


Useful when migrating. Point at source DB instance, retrieve grants using this script, pipe through the Add a semicolon in PERL script, and voila, recreate grants on destination instance. #!/bin/bash ( mysql –batch –skip-column-names -e “SELECT user, host FROM user” mysql ) | while read user host do echo “# $user @ $host” mysql –batch ..

Read more


CONVERT ALL THE THINGS. Well, almost all the things. #!/bin/bash for database in aaaaa bbbbb ccccc ddddd eeee do thesetables=`mysql -N -B -e “SELECT CONCAT(TABLE_SCHEMA,’.’,TABLE_NAME) \ FROM information_schema.TABLES where TABLE_SCHEMA = \”$database\”” \ AND TABLE_SCHEMA NOT IN (‘mysql’,’information_schema’,’performance_schema’)` alltables=`echo $alltables $thesetables` # change the schema itself mysql -e “ALTER DATABASE $database CHARACTER SET utf8 COLLATE ..

Read more


SELECT d.id, d.name, CONCAT_WS(‘ -> ‘,c5.name,c4.name,c3.name,c2.name,c1.name) as cat_string FROM domain d LEFT JOIN category c1 ON c1.id = d.category_id LEFT JOIN category c2 ON c1.parent_id = c2.id LEFT JOIN category c3 ON c2.parent_id = c3.id LEFT JOIN category c4 ON c3.parent_id = c4.id LEFT JOIN category c5 ON c4.parent_i..

Read more


Quickie to add a semicolon to end of non-commented lines (useful paired with the SHOW GRANTS bash script) #!/usr/bin/perl — open DATA, “$ARGV[0]”; while () { $liner=$_; if (“$liner” =~ /^#/) { print $liner; } else { chomp($liner); print “$liner” . “;n”; } } c..

Read more