Zmodyfikuj DEFINER na wielu widokach

Mam problemy z utworzeniem kopii zapasowej moich baz danych po aktualizacji. Grzebię w moim systemie, próbując dowiedzieć się, dlaczego. Jedno zapytanie, które uruchomiłem, zwróciło ten wynik.

Got error: 1449: The user specified as a definer ('cittool'@'%') does not exist when using LOCK TABLES

Po pewnym dochodzeniu wydaje się, że definicją tych widoków jest stare konto programisty, które zostało usunięte z systemu. Bazy danych i widoki z tym problemem są używane bardzo rzadko, a większość z nich jest przechowywana do celów archiwalnych.

Istnieje około 40 widoków z definicjorem, który już nie istnieje. Czy istnieje prosty sposób na zmianę definicji konta na inne konto na raz? Czy istnieje sposób, aby mysqldump po prostu zrzucił wszystkie widoki do pliku, abym mógł edytować ten plik i odtworzyć te widoki?

Utwórz plik tekstowy ze wszystkimi definicjami widoku:

mysql -uusername -ppassword -A --skip-column-names -e"SELECT CONCAT('SHOW CREATE VIEW ',table_schema,'.',table_name,'\\G') FROM information_schema.tables WHERE engine IS NULL" | mysql -uusername -ppassword -A --skip-column-names > AllMyViews.sql

Stamtąd edytujesz AllMyViews.sql. Następnie upuść widoki

mysql -uusername -ppassword -A --skip-column-names -e"SELECT CONCAT('DROP VIEW ',table_schema,'.',table_name,';') FROM information_schema.tables WHERE engine IS NULL" | mysql -uusername -ppassword -A

Po edycji AllMyViews.sql załaduj je ponownie

mysql -uusername -ppassword -A < AllMyViews.sql

Spróbuj !!!

Możesz użyć ALTER VIEW w połączeniu ze schematem informacyjnym . Wspomniałeś o zrzuceniu go do pliku tekstowego, więc może coś takiego:

SELECT CONCAT("ALTER DEFINER=`youruser`@`host` VIEW `",table_name,"` AS ", view_definition,";") 
FROM information_schema.views WHERE table_schema='databasename'

Wymieszaj to z wierszem poleceń mysql (zakładając, że * nix nie zna systemu Windows):

> echo "*abovequery*" | mysql -uuser -p > alterView.sql
> mysql -uuser -p databasename < alterView.sql

Sidenote: Nie można bezpośrednio zmieniać wpisów w schemacie informacji . Uwaga 2: Działa to tylko dla jednej bazy danych na raz, jeśli odrzucisz GDZIE schemat_tabeli, musisz wstawić między nimi polecenia USE.

Automatyzacja rozwiązania Dereka spowoduje zmianę DEFINERA na root@localhosti ustawienie SQL SECURITY INVOKER(upewnij się, że chcesz tego najpierw!) We wszystkich widokach we wszystkich bazach danych:

mysql -BNe 'show databases' | \
egrep -v '^(information_schema|performance_schema)$' | \
while read DB
do 
    mysql -BNe "SELECT CONCAT(\"ALTER DEFINER=\`root\`@\`localhost\` SQL SECURITY INVOKER VIEW \",table_name,\" AS \", view_definition,\";\") FROM information_schema.views WHERE table_schema=\"$DB\"" | \
    mysql $DB
done

OSTRZEŻENIE: upewnij się, że wykonałeś pełną kopię zapasową mysql (np. Zatrzymując mysqld i wykonując kopię zapasową wszystkich plików w katalogu / var / lib / mysql) przed jego uruchomieniem - na wszelki wypadek ... Dla mnie zadziałało, ale YMMV .

powinieneś również sprawdzić wszystkie swoje tabele / widoki za pomocą:

mysql -BNe 'show databases' | \
egrep -v '^(information_schema|performance_schema)$' | \
while read DB
do 
   mysql -BNe 'show tables' $DB | \
   while read t
   do
      echo "check table $t;"
   done | mysql -BN $DB
done | \
egrep -v 'status\s*OK'

nie powinien już narzekać na nieprawidłowe definicje w widokach.

Wyeksportuj wszystkie widoki bazy danych <DB>:

mysql -BNe "SELECT TABLE_NAME FROM TABLES WHERE TABLE_SCHEMA = '<DB>' AND TABLE_TYPE = 'VIEW'" \
    information_schema | xargs mysqldump --single-transaction --no-data <DB> >views.sql

lub:

mysql -BNe "SELECT TABLE_NAME FROM VIEWS WHERE TABLE_SCHEMA = '<DB>'" \
    information_schema | xargs mysqldump --single-transaction --no-data <DB> >views.sql

Edytuj views.sql(zmień definicję) i utwórz je ponownie:

cat views.sql | mysql <DB>

Określ -ui -pprzełącz w razie potrzeby.