Category : Database


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


This is a step-by-step tutorial to install Python and Jupyter Notebook to Windows 10 (64 bit). Python 3.3 or greater, or Python 2.7 is required to install the Jupyter Notebook. Download Python 3.7.4 from “https://www.python.org/downloads/release/python-374/” url 2. Choose and select “x86–64 executable installer” for Windows 10–64 bit computer 3. Select location to save the executable ..

Read more


Here is the standard mysqldump syntax I always use.  This dumps routines and triggers, does so in the most expeditious manner possible, and adds DROP xxxxx statements before all CREATE statements, just in case.  It also adds the FORCE parameter in case it runs into issues with views. The “xxxx” at the end can be ..

Read more


MySQL 8.x not only requires that the datadir be empty when initializing, but also the directory designated as the temp directory.  If the temp directory is not empty, the –initialize flag will not throw an error as it does if the datadir isn’t clean; rather, it will cause a “Data Dictionary initialization failed” message on ..

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


A sample service check, annotated:    host_name                       dbsrp2076                  <– name of server     service_description             SSH                        <– service being monitored     servicegroups                   PROD-ssh                   <– service groups     is_volatile                     0                          <– Does this service spontaneously start and stop (always 0 for “no”)     check_period                    24×7                       <– during what hours is this service checked?     max_check_attempts              10                         <– ..

Read more


After you configure all PXC nodes, initialize the cluster by bootstrapping the first node. The initial node should be the one that contains all your data, which you want to be replicated to other nodes. Bootstrapping implies starting the node without any known cluster addresses. If the wsrep_cluster_address variable is empty, Percona XtraDB Cluster assumes ..

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


#!/usr/bin/env perl # mysqltuner.pl – Version 1.7.9 # High Performance MySQL Tuning Script # Copyright (C) 2006-2018 Major Hayden – major@mhtx.net # Inspired by Matthew Montgomery’s tuning-primer.sh script: # http://forge.mysql.com/projects/view.php?id=44 # # This version further hacked and slashed by rlbyrd off and on since 2009. package main; use 5.005; use strict; use warnings; use diagnostics; ..

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


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


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


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


NORMALIZATION DIAGRAM   ACID Atomicity Transactions are often composed of multiple statements. Atomicity guarantees that each transaction is treated as a single “unit”, which either succeeds completely, or fails completely: if any of the statements constituting a transaction fails to complete, the entire transaction fails and the database is left unchanged. An atomic system must ..

Read more


Let us face it. The following mysql> prompt is boring. Nobody wants to see it. Let us change the default mysql> prompt to something functional and useful. $ mysql -u root -pyour-password Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 10 Server version: 5.1.25-rc-community MySQL Community Server ..

Read more


(2012-05-12 update:  Please replace all mentions of “utf8” with “utf8mb4” to get a full utf8 character set.  utf8_general_ci is just a half-assed implementation of utf8. Similarly, replace all representations of utf8_xxxxxx_xx collations with their utf8mb4 variant.  In for a penny, in for a pound.  NO ONE wants to do two utf8 migrations, ever. The methodology ..

Read more