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 : MySQL
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 ..
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 ..
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 ..
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 ); ..
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 ..
(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 ..