Modify DEFINER on Many Views

Question

I have am having problems backing up my databases after an update. I have been poking around on my system trying to figure out why. One query I ran returned this result.

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

After some investigation it appears that the definer for these views is an old developer account that has been purged from the system. The databases and views with this problem are used very infrequently, and most being kept around for archival purposes.

There is about 40 views with a definer that no longer exists. Is there an easy way to change the definer to a different account on everything at once? Is there a way to get mysqldump to simply dump all the views out to a file so I could edit that file and recreate the views?

Answer 1

Create a text file with all the view definitions:

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

You edit AllMyViews.sql from there. Then, Drop the Views

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

After editing AllMyViews.sql reload them

mysql -uusername -ppassword -A < AllMyViews.sql

Give it a Try !!!

Answer 2

You can use ALTER VIEW in conjunction with the information schema. You mentioned dumping it out to a text file, so perhaps something like this:

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

Mix this with the mysql command line (assuming *nix, not familiar with windows):

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

Sidenote: You can't alter the information_schema entries directly. Note2: This works for only one database at a time, if you leave off WHERE table_schema you need to insert USE commands between each.

Answer 3

Automating on the Derek's solution, this will change DEFINER to root@localhost and set SQL SECURITY INVOKER (make sure you want that first!) in all views in all databases:

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

WARNING: make sure you've made a full mysql backup (eg. by stopping mysqld, and backing up all files in /var/lib/mysql) before you run it - just in case... It worked for me, but YMMV.

you should also check all your tables/views with:

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'

it should no longer complain about invalid definers in views.

Answer 4

Export all the views of the database <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

or:

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

Edit views.sql (change definer) and recreate them:

cat views.sql | mysql <DB>

Specify -u and -p switches if necessary.

Answer 5

I created a simple python script that replaces production db definer with local db definer. This script will modify dump files and automatically import specified databases.

GIT repo: https://github.com/mjakal/db-auto-import

import getpass
import os
import time

# Dump file path - here you can set your own path 
dump_path = "./dumps/"
# Set the list of dump file names to import
dump_files = ["testing.sql"]
# Set the list of definer names (inside dump file) you want to replace with your local db definer
dump_definers = ["DEFINER=`prod`@`some-host.com`"]

# MySQL local root username
db_root_user = "root"
# Set db names for each dump file - this must be in correct order
db_names = ["testing"]

# Local db user info
db_user = "testing"
db_password = "testing"
db_host = "localhost"

# Generate definer name from local db config - desired definer
# You can hard-code this value if you wish 
local_definer = f"DEFINER=`{db_user}`@`{db_host}`"

# ------------------------------------------------------------------------------
# You are all set - Leave the rest of the code as it is
# ------------------------------------------------------------------------------

# SQL connection config
mysql_config = f"mysql -u {db_root_user}"


def fix_dump_definer(dump_file):
    dump_file_path = dump_path + dump_file

    print("Fixing dump file:", dump_file)

    # Read file
    with open(dump_file_path, "rb") as file:
        file_data = file.read()

    # Replace the target string bytes
    for dump_definer in dump_definers:
        file_data = file_data.replace(dump_definer.encode(), local_definer.encode())

    # Write the file out again
    with open(dump_file_path, "wb") as file:
        file.write(file_data)

    print("Fixing dump file done:", dump_file)


def create_db(db_name):
    drop_db = f"DROP DATABASE IF EXISTS {db_name};"
    recreate_db = f"CREATE DATABASE {db_name} CHARACTER SET utf8 COLLATE utf8_general_ci;"
    # default mysql
    grant_db_permissions = f"GRANT ALL PRIVILEGES ON {db_name}.* TO {db_user}@{db_host} IDENTIFIED BY {db_password};"
    # percona specific
    # grant_db_permissions = f"GRANT ALL PRIVILEGES ON {db_name}.* TO {db_user}@{db_host};"
    flush_db_privileges = "FLUSH PRIVILEGES;"

    recreate_db_queries = f'{mysql_config} -e "{drop_db}{recreate_db}{grant_db_permissions}{flush_db_privileges}"'

    print("Creating database:", db_name)

    os.system(recreate_db_queries)

    print("Await MySQL restart")

    time.sleep(10)

    print("Creating database done:", db_name)


def import_db(db_name, dump_file):
    # Set dump file path
    dump_file_path = f"{dump_path}{dump_file}"

    print("Importing db:", db_name)

    import_db = f"{mysql_config} {db_name} < {dump_file_path}"

    os.system(import_db)

    time.sleep(5)

    print("Import done:", db_name)


def manage_db_import(db_name, dump_file):
    fix_dump_definer(dump_file)
    create_db(db_name)
    import_db(db_name, dump_file)


def main_script():
    try:
        print("--------------------------------------------------------------------------------")
        print("WARNING - This script is used for development purposes only.")
        print("Please don't try roning it on production environment!")
        print("--------------------------------------------------------------------------------")

        # Get db_names and dump_files list length
        db_count = len(db_names)
        dump_count = len(dump_files)

        # Check if lists have the same length
        if db_count != dump_count:
            return "db_names and dump_files must have the same length."

        # e.g. db_count = 11 result will be 100 - 1
        import_all_option = (10 ** ((db_count // 10) + 1)) - 1

        print("Please select one of the options below:")

        for index, dump_file in enumerate(dump_files):
            db_name = db_names[index]

            print(f"{index} - Import {db_name}")

        print(f"{import_all_option} - Import all databases")
        print(f"{import_all_option + 1} - Exit script")

        selected_option = int(input("Select option: "))

        # Exit the script
        if selected_option == import_all_option + 1:
            return "Exiting script."

        # Input MySQL root password
        db_root_password = getpass.getpass(prompt="Enter MySQL root password: ", stream=None)

        # If password exists, modify mysql_config global variable
        if db_root_password:
            global mysql_config
            mysql_config = f"{mysql_config} -p{db_root_password}"

        print("If you continue with this operation existing database(s) will be deleted.")
        continue_import = input("Would you like to continue (y/n)? ")

        if continue_import != "y":
            return "Exiting script."

        if selected_option == import_all_option:
            print("Importing all databases please wait...")

            for index, dump_file in enumerate(dump_files):
                db_name = db_names[index]

                manage_db_import(db_name, dump_file)
        elif selected_option in range(0, db_count):
            db_name = db_names[selected_option]
            dump_file = dump_files[selected_option]

            print(f"Importing {db_name} please wait...")

            manage_db_import(db_name, dump_file)
        else:
            return "Invalid input, exiting script."

        return "DB import finished."
    except Exception as ex:
        print(str(ex))
        return "Something went wrong, exiting script."


# Call the main function
main_script_output = main_script()

print(main_script_output)

DB Auto Import Script

This script resolves user definer issue while importing databases from one sql server to another (e.g. production server to local dev server). It modifies user definer entry on all views/stored procedures inside sql dump files. After modifying the definer it will automatically import specified databases.

Requirements

• OS - Linux/MacOS
• Python >= 3.6
• MySQL, MariaDB or Percona

NOTE - If you are using Percona, comment out line 58 and uncomment line 60

# default mysql
# grant_db_permissions = f"GRANT ALL PRIVILEGES ON {db_name}.* TO {db_user}@{db_host} IDENTIFIED BY {db_password};"
# percona specific
grant_db_permissions = f"GRANT ALL PRIVILEGES ON {db_name}.* TO {db_user}@{db_host};"

Configuration

Open the db_auto_import.py script in your favorite text editor and edit the lines below.

# Dump file path - here you can set your own path 
dump_path = "./dumps/"
# Set the list of dump file names to import
dump_files = ["testing.sql"]
# Set the list of definer names (inside dump file) you want to replace with your local db definer
dump_definers = ["DEFINER=`prod`@`some-host.com`"]

# MySQL local root username
db_root_user = "root"
# Set db names for each dump file - this must be in correct order
db_names = ["testing"]

# Local db user info
db_user = "testing"
db_password = "testing"
db_host = "localhost"

How to run the script

First we need to copy/paste all production dump files to /dumps/ folder. Dump file names must match the names specified in dump_files list. Double check your config and run the command below.

python db_auto_import.py

Answer 6

Another simple way out. When I had this issue, I just created another user with the name in the error, say "cittool" with host name "%" and I made sure it has the same password as the other user too. It works.

Answer 7

solution :

1. in phpmyadmin go "database_name" click on options of toggle menu above triggers.
2. search for respective table from which you want to change definer.
3. go to edit option and then scroll down to below definer option.
4. change definer to "whaterver you want".