Jcrespo has uploaded a new change for review.
https://gerrit.wikimedia.org/r/315915
Change subject: Fix DEFINER and binary log switching; delete old script
......................................................................
Fix DEFINER and binary log switching; delete old script
* Delete old, non-working redact.sh - redact_standard_output.sh
works and has extra fixes. It also outputs to standard output
for checking before real execution
* Enforce definer to be root@localhost- otherise, if the user
that creates the triggers is deleted, the triggers fail
* Disable bianry log for trigger creation; enforce it for UPDATEs
Change-Id: Id920b059d2c4de59db2e165a7fd468de5fb79687
---
D scripts/redact.sh
M scripts/redact_standard_output.sh
2 files changed, 3 insertions(+), 91 deletions(-)
git pull ssh://gerrit.wikimedia.org:29418/operations/software/redactatron
refs/changes/15/315915/1
diff --git a/scripts/redact.sh b/scripts/redact.sh
deleted file mode 100755
index 6723613..0000000
--- a/scripts/redact.sh
+++ /dev/null
@@ -1,88 +0,0 @@
-#!/bin/bash
-
-host="$1"
-port="$2"
-
-if [ -z "$host" ] || [ -z "$port" ]; then
- echo "usage: <script> <host> <port>" 1>&2
- exit 1
-fi
-
-if [[ ! "$host" =~ ^db105[347]$ ]]; then
- echo "unexpected sanitarium host! $host" 2>&1
- exit 1
-fi
-
-if [[ ! "$port" =~ ^330[678]$ ]]; then
- echo "unexpected sanitarium port! $port" 2>&1
- exit 1
-fi
-
-dsn="-h $host -P $port"
-query="mysql --skip-column-names $dsn -e "
-
-for db in $($query "select schema_name from schemata where schema_name like
'%wik%'" information_schema); do
-
- read -p "$db, go?" yn
-
- for tbl in $(egrep ',F' cols.txt | awk -F ',' '{print $1}' | uniq); do
-
- echo "-- $tbl"
-
- insert="CREATE TRIGGER ${tbl}_insert BEFORE INSERT ON ${tbl} FOR EACH
ROW SET"
- update="CREATE TRIGGER ${tbl}_update BEFORE UPDATE ON ${tbl} FOR EACH
ROW SET"
- remove="UPDATE ${tbl} SET"
-
- for col in $(egrep "${tbl},.*,F" cols.txt | awk -F ',' '{print $2}');
do
-
- datatype=$($query "select data_type from columns where
table_schema = '${db}' and table_name = '${tbl}' and column_name = '${col}'"
information_schema)
-
- if [ -n "$datatype" ]; then
-
- echo "-- -- $col found"
-
- if [[ "$datatype" =~ int ]]; then
- insert="$insert NEW.${col} = 0,"
- update="$update NEW.${col} = 0,"
- remove="$remove ${col} = 0,"
- else
- insert="$insert NEW.${col} = '',"
- update="$update NEW.${col} = '',"
- remove="$remove ${col} = '',"
- fi
-
- else
- echo "-- -- $col MISSING"
- fi
-
- done
-
- if [[ "$insert" =~ ^(.*),$ ]]; then
- insert="${BASH_REMATCH[1]}"
- fi
-
- if [[ "$update" =~ ^(.*),$ ]]; then
- update="${BASH_REMATCH[1]}"
- fi
-
- if [[ "$remove" =~ ^(.*),$ ]]; then
- remove="${BASH_REMATCH[1]}"
- fi
-
- $query "DROP TRIGGER IF EXISTS ${tbl}_insert" $db
- $query "DROP TRIGGER IF EXISTS ${tbl}_update" $db
-
- if [[ ! "$insert" =~ SET$ ]]; then
-
- echo "-- -- -- $insert"
- $query "$insert;" $db
- echo "-- -- -- $update"
- $query "$update;" $db
- echo "-- -- -- $remove"
- $query "set session binlog_format = 'STATEMENT'; $remove;" $db
-
- fi
-
- done
-
-done
diff --git a/scripts/redact_standard_output.sh
b/scripts/redact_standard_output.sh
index 6c82182..1c542b4 100755
--- a/scripts/redact_standard_output.sh
+++ b/scripts/redact_standard_output.sh
@@ -26,9 +26,9 @@
echo "-- $tbl"
- insert="CREATE TRIGGER ${db}.${tbl}_insert BEFORE INSERT ON ${db}.${tbl}
FOR EACH ROW SET"
- update="CREATE TRIGGER ${db}.${tbl}_update BEFORE UPDATE ON ${db}.${tbl}
FOR EACH ROW SET"
- remove="UPDATE ${db}.${tbl} SET"
+ insert="SET SESSION sql_log_bin = 0; CREATE DEFINER='root'@'localhost'
TRIGGER ${db}.${tbl}_insert BEFORE INSERT ON ${db}.${tbl} FOR EACH ROW SET"
+ update="SET SESSION sql_log_bin = 0; CREATE DEFINER='root'@'localhost'
TRIGGER ${db}.${tbl}_update BEFORE UPDATE ON ${db}.${tbl} FOR EACH ROW SET"
+ remove="SET SESSION sql_log_bin = 1; UPDATE ${db}.${tbl} SET"
for col in $(egrep "${tbl},.*,F" cols.txt | awk -F ',' '{print $2}'); do
--
To view, visit https://gerrit.wikimedia.org/r/315915
To unsubscribe, visit https://gerrit.wikimedia.org/r/settings
Gerrit-MessageType: newchange
Gerrit-Change-Id: Id920b059d2c4de59db2e165a7fd468de5fb79687
Gerrit-PatchSet: 1
Gerrit-Project: operations/software/redactatron
Gerrit-Branch: master
Gerrit-Owner: Jcrespo <jcre...@wikimedia.org>
_______________________________________________
MediaWiki-commits mailing list
MediaWiki-commits@lists.wikimedia.org
https://lists.wikimedia.org/mailman/listinfo/mediawiki-commits
