CODEWITHSUNDEEP
mysql
Sriram
Sriram

Reputation: 31

Update trigger definer in MySQL?

How to change definer in procedure and trigger using MySQL?

UPDATE `mysql`.`trig` p
    SET definer = 'user@%'
    WHERE definer='root@%'`

Upvotes: 3

Views: 6444

Answers (1)

ndberg
ndberg

Reputation: 4001

For Triggers and Views you can use this, as I have described here. For Procedures the same thing should work with the table information_schema.routines.

Original Post: I know it's an old post, but perhaps it can help someone.

I use this sql query to generate a DROP and a CREATE command:

SELECT CONCAT("DROP TRIGGER ", trigger_name, ";", " CREATE TRIGGER ", TRIGGER_NAME, " AFTER ", EVENT_MANIPULATION, " ON ", EVENT_OBJECT_SCHEMA, ".", EVENT_OBJECT_TABLE, " FOR EACH ROW ", ACTION_STATEMENT, ";") AS sqlCommand FROM information_schema.triggers WHERE EVENT_OBJECT_SCHEMA = "yourdatabase";

I use this in my app when I take the production database to my development machine and go it with a foreach over all commands and recreate the triggers automatically. This gives me the option to automate it.

Example in PHP/Laravel:

    $this->info('DROP and CREATE TRIGGERS');
    $pdo = DB::connection()->getPdo();
    $sql = 'SELECT CONCAT("DROP TRIGGER ", trigger_name, ";", " CREATE TRIGGER ", TRIGGER_NAME, " AFTER ", EVENT_MANIPULATION, " ON ", EVENT_OBJECT_SCHEMA, ".", EVENT_OBJECT_TABLE, " FOR EACH ROW ", ACTION_STATEMENT, ";") AS sqlCommand FROM information_schema.triggers WHERE EVENT_OBJECT_SCHEMA = "mydatabase";';
    $stmt = $pdo->prepare($sql, [PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => true]);
    $stmt->execute();
    $result = $stmt->fetchAll(PDO::FETCH_ASSOC);
    $stmt->closeCursor();

    foreach($result as $rs){
        $pdo = DB::unprepared($rs['sqlCommand']);
        break;
    }

Hint: I have to do it with pdo because of the mysql buffer query problem, described here

The same I do for my views (Here you can use ALTER TABLE):

    $pdo = DB::connection()->getPdo();
    $sql = 'SELECT CONCAT("ALTER DEFINER=`homestead` VIEW ", table_name," AS ", view_definition,";") AS sqlCommand FROM  information_schema.views WHERE table_schema="mydatabase";';
    $stmt = $pdo->prepare($sql, [PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => true]);
    $stmt->execute();
    $result = $stmt->fetchAll(PDO::FETCH_ASSOC);
    $stmt->closeCursor();
    $this->info('View definer changed');

    foreach($result as $rs){
        $pdo = DB::unprepared($rs['sqlCommand']);
    }

Hope it helps.

Upvotes: 1

Related Questions