Uploaded image for project: 'Debezium'
  1. Debezium
  2. DBZ-5273

Mysql parser is not able to handle variables in KILL command

    XMLWordPrintable

Details

    Description

      Using local variable in killing connection in stored procedure (e.g. kill connection @lastBeginConId;) results into

      no viable alternative at input 'CREATE DEFINER=`root`@`%` PROCEDURE `prodlims`.`stex_check_running_study_sample_status_sgtra_event`(forceKill bit(1))\nbegin\n    declare exit handler for sqlexception, sqlwarning\n    begin\n        get diagnostics condition 1 @sqlstate = returned_sqlstate,\n            @errno = mysql_errno, @text = message_text;\n        if @sqlstate = '45001' then\n            resignal;\n        end if;\n        if @sqlstate = '45002' then\n            set @action = 'info';\n            set @full_error = @text;\n        else\n            set @action = 'error';\n            set @full_error = concat("error ", @errno, " (", @sqlstate, "): ", @text);\n        end if;\n\n        prepare logStmt from 'insert into `prodlims`.`stex_study_sample_status_sgtra_job_log` (`connection_id`, `action`, `info`) values (?, ?, ?)';\n        set @connection_id = CONNECTION_ID();\n        execute logStmt using @connection_id, @action, @full_error;\n        deallocate prepare logStmt;\n\n        select @full_error;\n    end;\n\n    select @lastBegin := ifnull((select max(s.id) from `prodlims`.`stex_study_sample_status_sgtra_job_log` s where s.`action` = 'begin'), null) as lastBegin;\n    select @lastBeginConId := s.`connection_id`, @lastBeginRunningHours := time_to_sec(timediff(now(), s.created_at))/3600\n    from `prodlims`.`stex_study_sample_status_sgtra_job_log` s where s.id = @lastBegin;\n    select @lastBeginThreadId := ifnull((select t.`THREAD_ID`\n            from `performance_schema`.`threads` t where t.processlist_id = @lastBeginConId), null) as threadId;\n    select @lastEnd := ifnull((select max(s.id) from `prodlims`.`stex_study_sample_status_sgtra_job_log` s where s.`action` = 'end'), null) as lastEnd;\n    select @lastEndConId := s.`connection_id` from `prodlims`.`stex_study_sample_status_sgtra_job_log` s where s.id = @lastEnd;\n\n    if @lastBegin is null then\n            signal sqlstate '45002'\n            set message_text = 'Job has never been started yet';\n    end if;\n\n    prepare logStmt from 'insert into `prodlims`.`stex_study_sample_status_sgtra_job_log` (`connection_id`, `action`, `info`) values (?, ?, ?)';\n    set @connection_id = CONNECTION_ID();\n\n    if forceKill > 0 and @lastBeginThreadId is not null then\n        set @action = 'kill';\n        set @info := concat('@lastBeginConId=', @lastBeginConId, ', @lastBeginThreadId=', @lastBeginThreadId);\n        execute logStmt using @connection_id, @action, @info;\n        deallocate prepare logStmt;\n\n        kill connection @lastBeginConId'
      at io.debezium.antlr.ParsingErrorListener.syntaxError(ParsingErrorListener.java:43)
      at org.antlr.v4.runtime.ProxyErrorListener.syntaxError(ProxyErrorListener.java:41)
      at org.antlr.v4.runtime.Parser.notifyErrorListeners(Parser.java:544)
      at org.antlr.v4.runtime.DefaultErrorStrategy.reportNoViableAlternative(DefaultErrorStrategy.java:310)
      at org.antlr.v4.runtime.DefaultErrorStrategy.reportError(DefaultErrorStrategy.java:136)
      at io.debezium.ddl.parser.mysql.generated.MySqlParser.sqlStatements(MySqlParser.java:1194)
      

      See also https://debezium.zulipchat.com/#narrow/stream/302529-users/topic/ddl.2Efilter.20doesn't.20work.20for.20stored.20procedure

      Attachments

        Activity

          People

            vjuranek@redhat.com Vojtech Juranek
            vjuranek@redhat.com Vojtech Juranek
            Votes:
            0 Vote for this issue
            Watchers:
            4 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved: