Springle has submitted this change and it was merged. 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(-) Approvals: Springle: Looks good to me, approved jenkins-bot: Verified 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: merged Gerrit-Change-Id: I47116d13b80556c0fb0898750349a247e6b21201 Gerrit-PatchSet: 1 Gerrit-Project: operations/software Gerrit-Branch: master Gerrit-Owner: Springle <sprin...@wikimedia.org> Gerrit-Reviewer: Springle <sprin...@wikimedia.org> Gerrit-Reviewer: jenkins-bot <> _______________________________________________ MediaWiki-commits mailing list MediaWiki-commits@lists.wikimedia.org https://lists.wikimedia.org/mailman/listinfo/mediawiki-commits