"DROP FUNCTION IF EXISTS not_exists" cause job dead

[asiroliu]

Description

DROP FUNCTION IF EXISTS not_exists cause job dead

Steps to reproduce the issue

0. execute on dest MySQL

mysql> GRANT CREATE ROUTINE, SUPER ON *.* TO 'test_dest'@'%';

2. create dtle mysql to mysql job

{
  "job_id": "ddl_function",
  "is_password_encrypted": true,
  "task_step_name": "all",
  "failover": true,
  "retry": 2,
  "src_task": {
    "task_name": "src",
    "node_id": "6bb58ed5-d845-3867-c4e8-165e3d1a7531",
    "mysql_src_task_config": {
      "gtid": "",
      "binlog_relay": false,
      "expand_syntax_support": true
    },
    "drop_table_if_exists": true,
    "skip_create_db_table": false,
    "repl_chan_buffer_size": 120,
    "chunk_size": 2000,
    "group_max_size": 1,
    "group_timeout": 100,
    "connection_config": {
      "database_type": "MySQL",
      "host": "172.100.9.3",
      "port": 3306,
      "user": "test_src",
      "password": "Wk6cdmBGfccREf7T6krFwsmgBL0X2MJ7tjzf7kINyAqUlwT1omW76IBsB2de1SReNTHcKgknbIuaus8wv0asNSl/L0QrW+iyCHXyN7VgxzVpr06QnVPxeK4+/XNdMEVyDbKiSbw+JDxdr7pIKAMn6YPyj8Gyi2fKOO9JesNEAQI="
    },
    "replicate_do_db": [
      {
        "table_schema": "action_db_1"
      }
    ]
  },
  "dest_task": {
    "task_name": "dest",
    "node_id": "519342ac-bc81-8740-9a35-dbaae5c94255",
    "mysql_dest_task_config": {
      "use_my_sql_dependency": false,
      "dependency_history_size": 2500,
      "parallel_workers": 32
    },
    "connection_config": {
      "database_type": "MySQL",
      "host": "172.100.9.2",
      "port": 3306,
      "user": "test_dest",
      "password": "ZITUZbQDOMv8h4FC0w3sRfQLOKr/JuomDpbyrz+OYJteAf5ojUPBfbrz6fDD9/6EyPVMahQ6BGB4DV0wz7oK4OE3nWGKaKyUgrxaJYLKe6chYXX1RryCB+wQdrpvGuGjV807cEpCwm98jYMWOjj9fejWqd7ozEgMwWRCA2Z0ouA="
    }
  }
}

3. exec sql on src MySQL drop with if exists and the function is not exists.

DROP FUNCTION IF EXISTS not_exists;

4. job dead

# src dtle log
2021-12-31T13:45:47.837+0800 [DEBUG] client.driver_mgr.dtle: publish: driver=dtle gno=134 job=ddl_function-migration partLen=244 @module=dtle.extractor iSeg=0 subject=ddl_function-migration_incr_hete timestamp=2021-12-31T13:45:47.837+0800
2021-12-31T13:45:47.837+0800 [DEBUG] client.driver_mgr.dtle: publish.after: driver=dtle n=1 @module=dtle.extractor gno=134 job=ddl_function-migration timestamp=2021-12-31T13:45:47.837+0800
2021-12-31T13:45:47.838+0800 [ERROR] client.driver_mgr.dtle: onError: driver=dtle @module=dtle.extractor err="applier error/restart: tx.Exec. gno 134 iEvent 0 queryBegin DROP FUNCT workerIdx 0: Error 1370: alter routine command denied to user 'test_dest'@'%' for routine 'action_db_1.not_exists'" job=ddl_function-migration timestamp=2021-12-31T13:45:47.838+0800
2021-12-31T13:45:47.839+0800 [DEBUG] client.driver_mgr.dtle: extractor shutdown: driver=dtle @module=dtle.extractor job=ddl_function-migration timestamp=2021-12-31T13:45:47.838+0800
2021-12-31T13:45:47.839+0800 [INFO]  client.driver_mgr.dtle: extractor shutdown: driver=dtle @module=dtle.extractor job=ddl_function-migration timestamp=2021-12-31T13:45:47.839+0800
2

# dest dtle log
2021-12-31T13:45:47.838+0800 [DEBUG] client.driver_mgr.dtle: execQuery: driver=dtle @module=dtle.applier job=ddl_function-migration query="USE `action_db_1`" timestamp=2021-12-31T13:45:47.838+0800
2021-12-31T13:45:47.838+0800 [DEBUG] client.driver_mgr.dtle: ParseQueryEventFlags: driver=dtle @module=dtle.applier.ApplyBinlogEvent.ParseQueryEventFlags bytes=0000000000012000a055000000000603737464042d002d000800 job=ddl_function-migration timestamp=2021-12-31T13:45:47.838+0800
2021-12-31T13:45:47.838+0800 [DEBUG] client.driver_mgr.dtle: execQuery: driver=dtle @module=dtle.applier job=ddl_function-migration query="DROP FUNCTION IF EXISTS not_exists /*dtle_gtid1 ddl_function-migration 11a020af-69f9-11ec-8f22-0242ac640903 134 dtle_gtid*/" timestamp=2021-12-31T13:45:47.838+0800
2021-12-31T13:45:47.838+0800 [ERROR] client.driver_mgr.dtle: Exec sql error: driver=dtle @module=dtle.applier.ApplyBinlogEvent err="tx.Exec. gno 134 iEvent 0 queryBegin DROP FUNCT workerIdx 0: Error 1370: alter routine command denied to user 'test_dest'@'%' for routine 'action_db_1.not_exists'" job=ddl_function-migration timestamp=2021-12-31T13:45:47.838+0800
2021-12-31T13:45:47.838+0800 [ERROR] client.driver_mgr.dtle: onError: driver=dtle err="tx.Exec. gno 134 iEvent 0 queryBegin DROP FUNCT workerIdx 0: Error 1370: alter routine command denied to user 'test_dest'@'%' for routine 'action_db_1.not_exists'" job=ddl_function-migration @module=dtle.applier timestamp=2021-12-31T13:45:47.838+0800
2021-12-31T13:45:47.838+0800 [DEBUG] client.driver_mgr.dtle: onError. nats published: driver=dtle job=ddl_function-migration @module=dtle.applier timestamp=2021-12-31T13:45:47.838+0800
2021-12-31T13:45:47.838+0800 [INFO]  client.driver_mgr.dtle: Shutting down: driver=dtle @module=dtle.applier job=ddl_function-migration timestamp=2021-12-31T13:45:47.838+0800
2021-12-31T13:45:47.838+0800 [DEBUG] client.driver_mgr.dtle: Shutdown. a.ai.wg.Wait. after: driver=dtle @module=dtle.applier job=ddl_function-migration timestamp=2021-12-31T13:45:47.838+0800
2021-12-31T13:45:47.838+0800 [DEBUG] client.driver_mgr.dtle: Shutdown. a.wg.Wait. after: driver=dtle @module=dtle.applier job=ddl_function-migration timestamp=2021-12-31T13:45:47.838+0800
2021-12-31T13:45:47.838+0800 [DEBUG] client.driver_mgr.dtle: Shutdown. CloseDB. after: driver=dtle @module=dtle.applier job=ddl_function-migration timestamp=2021-12-31T13:45:47.838+0800
2021-12-31T13:45:47.838+0800 [DEBUG] client.driver_mgr.dtle: Shutdown. CloseConns. after: driver=dtle @module=dtle.applier job=ddl_function-migration timestamp=2021-12-31T13:45:47.838+0800
2021-12-31T13:45:47.838+0800 [INFO]  client.driver_mgr.dtle: Shutdown: driver=dtle @module=dtle.applier job=ddl_function-migration timestamp=2021-12-31T13:45:47.838+0800
20

3. "DROP FUNCTION f3;" and "DROP FUNCTION IF EXISTS f4;" work fine, if the function f3 and f4 is exists.

Output of ./dtle version:**

9.9.9.9-master-92cc3be

[asiroliu]

"DROP PROCEDURE IF EXISTS not_exists" cause same issue

[ghost]

An experiment:

$ mysql -h 10.186.62.40 -P3307 -uuser1 -ppassword

...

mysql> show grants for current_user();
+------------------------------------------------------------------------------------------------------------------------+
| Grants for user1@%                                                                                                     |
+------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'user1'@'%' |
+------------------------------------------------------------------------------------------------------------------------+

mysql> DROP FUNCTION IF EXISTS not_exists;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> use db1;
Database changed
mysql> DROP FUNCTION IF EXISTS not_exists;
ERROR 1370 (42000): alter routine command denied to user 'user1'@'%' for routine 'db1.not_exists'

[ghost]

推测回放连接之前use了schema. 如

use db1;
create table tb1 (...);

会产生一个具有Schema="db1", Query="create ..."的QueryEvent.

另外, create schema db1也会使QueryEvent.Schema="db1", 尽管执行时没有用use.

目前没有清空连接(session)上的use schema的办法. 没有修复方案.

[ghost]

注: 这是一个能记入binlog, 但schema为空的DDL的例子.