Springle has uploaded a new change for review.
https://gerrit.wikimedia.org/r/144421
Change subject: Some schema sanity-check scripts rescued from unversioned $HOME
labyrinth on terbium.
......................................................................
Some schema sanity-check scripts rescued from unversioned $HOME
labyrinth on terbium.
Change-Id: I47116d13b80556c0fb0898750349a247e6b21201
---
A dbtools/show_column.sh
A dbtools/show_column_missing.sh
A dbtools/show_index_missing.sh
3 files changed, 263 insertions(+), 0 deletions(-)
git pull ssh://gerrit.wikimedia.org:29418/operations/software
refs/changes/21/144421/1
diff --git a/dbtools/show_column.sh b/dbtools/show_column.sh
new file mode 100755
index 0000000..5316546
--- /dev/null
+++ b/dbtools/show_column.sh
@@ -0,0 +1,83 @@
+#!/bin/bash
+
+set -e
+
+usage() {
+ echo $0 --host=... --dblist=... --table... \"SQL\"
+ exit
+}
+
+confirm() {
+ read -p "continue? yes/no " yn
+ if [[ ! "$yn" =~ ^y ]]; then
+ echo "abort"
+ exit 1
+ fi
+}
+
+user="root"
+hosts=""
+dblist=""
+table=""
+column=""
+
+for var in "$@"; do
+
+ if [[ "$var" =~ ^--hosts=(.+) ]]; then
+ hosts="${BASH_REMATCH[1]}"
+ fi
+
+ if [[ "$var" =~ ^--user=(.+) ]]; then
+ user="${BASH_REMATCH[1]}"
+ fi
+
+ if [[ "$var" =~ ^--dblist=(.+) ]]; then
+ dblist="${BASH_REMATCH[1]}"
+ fi
+
+ if [[ "$var" =~ ^--table=(.+) ]]; then
+ table="${BASH_REMATCH[1]}"
+ fi
+
+ if [[ "$var" =~ ^--column=(.+) ]]; then
+ column="${BASH_REMATCH[1]}"
+ fi
+
+done
+
+[ "$hosts" ] || usage
+[ "$dblist" ] || usage
+[ "$table" ] || usage
+[ "$column" ] || usage
+
+if [ ! -f "$dblist" ] ; then
+ echo "ERROR: $dblist not found"
+ exit 1
+fi
+
+echo "Hosts : $hosts"
+echo "Databases : $dblist"
+echo "Table : $table"
+echo "Column : $column"
+
+confirm
+
+for db in $(cat $dblist) ; do
+
+ for host in $hosts; do
+
+ port="3306"
+
+ if [[ "$host" =~ ^(.+):([0-9]+)$ ]]; then
+ host="${BASH_REMATCH[1]}"
+ port="${BASH_REMATCH[2]}"
+ fi
+
+ echo "host $host, port $port, database $db..."
+
+ sql="select column_name, column_type, column_default from
columns where table_schema = '$db' and table_name = '$table' and column_name =
'$column'"
+ mysql -u $user -h $host -P $port --skip-column-names
information_schema -e "$sql"
+
+ done
+
+done
diff --git a/dbtools/show_column_missing.sh b/dbtools/show_column_missing.sh
new file mode 100755
index 0000000..91e7ac9
--- /dev/null
+++ b/dbtools/show_column_missing.sh
@@ -0,0 +1,89 @@
+#!/bin/bash
+
+set -e
+
+usage() {
+ echo $0 --host=... --dblist=... --table... \"SQL\"
+ exit
+}
+
+confirm() {
+ read -p "continue? yes/no " yn
+ if [[ ! "$yn" =~ ^y ]]; then
+ echo "abort"
+ exit 1
+ fi
+}
+
+user="root"
+hosts=""
+dblist=""
+table=""
+column=""
+
+for var in "$@"; do
+
+ if [[ "$var" =~ ^--hosts=(.+) ]]; then
+ hosts="${BASH_REMATCH[1]}"
+ fi
+
+ if [[ "$var" =~ ^--user=(.+) ]]; then
+ user="${BASH_REMATCH[1]}"
+ fi
+
+ if [[ "$var" =~ ^--dblist=(.+) ]]; then
+ dblist="${BASH_REMATCH[1]}"
+ fi
+
+ if [[ "$var" =~ ^--table=(.+) ]]; then
+ table="${BASH_REMATCH[1]}"
+ fi
+
+ if [[ "$var" =~ ^--column=(.+) ]]; then
+ column="${BASH_REMATCH[1]}"
+ fi
+
+done
+
+[ "$hosts" ] || usage
+[ "$dblist" ] || usage
+[ "$table" ] || usage
+[ "$column" ] || usage
+
+if [ ! -f "$dblist" ] ; then
+ echo "ERROR: $dblist not found"
+ exit 1
+fi
+
+echo "Hosts : $hosts"
+echo "Databases : $dblist"
+echo "Table : $table"
+echo "Column : $column"
+
+confirm
+
+for db in $(cat $dblist) ; do
+
+ for host in $hosts; do
+
+ port="3306"
+
+ if [[ "$host" =~ ^(.+):([0-9]+)$ ]]; then
+ host="${BASH_REMATCH[1]}"
+ port="${BASH_REMATCH[2]}"
+ fi
+
+ echo -n "host $host, port $port, database $db..."
+
+ sql="select * from columns where table_schema = '$db' and
table_name = '$table' and column_name = '$column'"
+ state=$(mysql -u $user -h $host -P $port --skip-column-names
information_schema -e "$sql" | wc -l)
+
+ if [ $state -gt 0 ]; then
+ echo "present"
+ else
+ echo "missing"
+ fi
+
+ done
+
+done
diff --git a/dbtools/show_index_missing.sh b/dbtools/show_index_missing.sh
new file mode 100755
index 0000000..dc20329
--- /dev/null
+++ b/dbtools/show_index_missing.sh
@@ -0,0 +1,91 @@
+#!/bin/bash
+
+set -e
+
+osctool=$(which pt-online-schema-change)
+
+usage() {
+ echo $0 --host=... --dblist=... --table... \"SQL\"
+ exit
+}
+
+confirm() {
+ read -p "continue? yes/no " yn
+ if [[ ! "$yn" =~ ^y ]]; then
+ echo "abort"
+ exit 1
+ fi
+}
+
+user="root"
+hosts=""
+dblist=""
+table=""
+index=""
+
+for var in "$@"; do
+
+ if [[ "$var" =~ ^--hosts=(.+) ]]; then
+ hosts="${BASH_REMATCH[1]}"
+ fi
+
+ if [[ "$var" =~ ^--user=(.+) ]]; then
+ user="${BASH_REMATCH[1]}"
+ fi
+
+ if [[ "$var" =~ ^--dblist=(.+) ]]; then
+ dblist="${BASH_REMATCH[1]}"
+ fi
+
+ if [[ "$var" =~ ^--table=(.+) ]]; then
+ table="${BASH_REMATCH[1]}"
+ fi
+
+ if [[ "$var" =~ ^--index=(.+) ]]; then
+ index="${BASH_REMATCH[1]}"
+ fi
+
+done
+
+[ "$hosts" ] || usage
+[ "$dblist" ] || usage
+[ "$table" ] || usage
+[ "$index" ] || usage
+
+if [ ! -f "$dblist" ] ; then
+ echo "ERROR: $dblist not found"
+ exit 1
+fi
+
+echo "Hosts : $hosts"
+echo "Databases : $dblist"
+echo "Table : $table"
+echo "index : $index"
+
+confirm
+
+for db in $(cat $dblist) ; do
+
+ for host in $hosts; do
+
+ port="3306"
+
+ if [[ "$host" =~ ^(.+):([0-9]+)$ ]]; then
+ host="${BASH_REMATCH[1]}"
+ port="${BASH_REMATCH[2]}"
+ fi
+
+ echo -n "host $host, port $port, database $db..."
+
+ sql="select * from statistics where table_schema = '$db' and
table_name = '$table' and index_name = '$index'"
+ state=$(mysql -u $user -h $host -P $port --skip-column-names
information_schema -e "$sql" | wc -l)
+
+ if [ $state -gt 0 ]; then
+ echo "present"
+ else
+ echo "missing"
+ fi
+
+ done
+
+done
--
To view, visit https://gerrit.wikimedia.org/r/144421
To unsubscribe, visit https://gerrit.wikimedia.org/r/settings
Gerrit-MessageType: newchange
Gerrit-Change-Id: I47116d13b80556c0fb0898750349a247e6b21201
Gerrit-PatchSet: 1
Gerrit-Project: operations/software
Gerrit-Branch: master
Gerrit-Owner: Springle <[email protected]>
_______________________________________________
MediaWiki-commits mailing list
[email protected]
https://lists.wikimedia.org/mailman/listinfo/mediawiki-commits
