I have developers come to me occasionally mystified by this error messages like this: ERROR 1055 (42000): ‘<tablename>.<columnname>’ isn’t in GROUP BY What does that mean, exactly, and why is the query getting kicked back? To illustrate, let’s say we have a tabe, geodata, which has this data for all US cities–the table has just ..
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 ..
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 ..
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 ..
Web based management interface for ProxySQL: https://proxyweb.org https://github.com/edmodo/proxyw..
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 ..
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 ..
Since this is an area not everyone has to deal with, I thought a quick one-liner was in order. MySQL can have one of three different binary log formats, which are used to facilitate replication. MySQL masters have binary logging enabled and the binary logs are streamed to all the replicas, where the logs are ..
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 <– ..
The attached scripts simple reverse a repliation flow (e.g. serverA –> serverB becomes serverB –> serverA). automate..
Edit to spec as necessary: DELIMITER $$ CREATE PROCEDURE trimTable() BEGIN DECLARE ROWS INT; DECLARE rows_deleted INT; SET ROWS = 1; SET rows_deleted = 10000; WHILE ROWS > 0 DO DELETE FROM db.tabA WHERE predicate_col < CURDATE() - INTERVAL 90 DAY ORDER BY `id` LIMIT 10000; SET ROWS = ROW_COUNT(); SET rows_deleted = rows_deleted + ..
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 ..
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 + | | ..
MySQL 5.7 was the last entry into the MySQL world before 8.x, and it added a dependable multi-threaded replication applier. This article will focus on the performance of the multi-threaded slave applier (MTS), and about its scalability in particular. For those in a hurry, here is a summary of what follows: The multi-threaded applier can ..
#!/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; ..
— 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 ..
#!/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 ..
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 ..
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 ..
Unfortunately, MySQL does not support the INTERSECT operator. However, you can simulate the INTERSECT operator. Let’s create some sample data for the demonstration. The following statements create tables t1 and t2, and then insert data into both tables. 1 2 3 4 5 6 7 8 9 CREATE TABLE t1 ( id INT PRIMARY KEY ); ..
The “last packet successfully received from the server XXXXX milliseconds ago” errors are virtually always caused by stale connections. There are a number of possible solutions, most having to do with small code changes. Here are things to try to alleviate the issue. I apologize in advance if these have already been tried; I’m shooting ..
By now, we’re all aware that a utf8 charset and collation is the “right” thing to do, with most folks opting for utf8_general_ci or utf8_unicode_ci. However, lots of installations still default to some form of latin1, which is unfortunate. There are multiple ways to do this, all which functionally do the same thing. Be aware ..
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 ..
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: ..
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 * ..
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 ..
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 ..
..
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 ..
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 ..
These are online, self-guided ways for developers wishing to gain more RDBMS understanding (or have a refresher course) in the two most ubiquitous platforms — MySQL and PostgreSQL. I add and remove from this list from time to time as material becomes outdated or better bits are found. Disclaimer: I didn’t have a hand in ..
If ever there was a TL;DR, this is it. However, it is important to understand the “whys” in my world, so here goes. A full discussion of character sets and encoding is beyond the scope of this document. (If you want more background, I recommend checking out the wikipedia article for a good place to ..
(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 ..
I hates the FKs, my precious. SELECT CONCAT( ‘ALTER TABLE ‘, table_schema, ‘.’, table_name, ‘ DROP FOREIGN KEY ‘, constraint_name, ‘;’ ) FROM information_schema.table_constraints WHERE constraint_type = ‘FOREIGN KEY’ AND table_schema LIKE ‘sch..