Debugging dynamic SQL errors and warnings of MySQL

    

create table log_trace(row_id int auto_increment primary key, msg text, utc timestamp, tag varchar(200));

DELIMITER ;;
CREATE PROCEDURE `sp_logger`(in p_msg text,in p_tag varchar(200))
BEGIN
  insert into log_trace (`msg`, `utc`, `tag`) values(p_msg, now(), p_tag);
END ;;
DELIMITER ;


DROP PROCEDURE IF EXISTS `sp_run_sql`;

DELIMITER ;;
CREATE PROCEDURE `sp_run_sql`(
  in p_sql text,
  in p_for varchar(50),
  out p_cnt int
)
BEGIN
   declare exit handler for sqlexception call sp_error_logger(p_sql, p_for);
   declare continue handler for sqlwarning call sp_error_logger(p_sql, p_for);
   set p_cnt = 0;
   set @txt = concat('begin sql: ',p_sql);
   call sp_logger(@txt, p_for);
   set @txt = p_sql;
   prepare stm from @txt;
   execute stm;
   set p_cnt = row_count();
   deallocate prepare stm;
   set @txt = concat(p_cnt,' row(s) affected/returned by sql: ',p_sql);
   call sp_logger(@txt, p_for);
END ;;
DELIMITER ;

show procedure status where db=database();
show tables;

DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_error_logger`(in p_msg text,in p_routine varchar(200))
BEGIN
  GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE,
   @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
  if @text is not null and 0 < length(@text) then 
   set @text = concat('sql:',ifnull(p_msg,'None'),'state: ',ifnull(@sqlstate,'None'),', code:', ifnull(@errno,'None'),', error: ',ifnull(@text,'None'));
    call sp_logger(@text, p_routine);
  end if;
END ;;
DELIMITER ;

select * from log_trace order by row_id desc limit 5\G

call sp_run_sql('create table fruits(row_id int auto_increment primary key, name varchar(20))', 'test create', @cnt);
select @cnt;
show tables;

call sp_run_sql('insert into fruits(name) values(''apple''),(''mango'')', 'test insert', @cnt);
select @cnt;

call sp_run_sql('select * from fruits', 'test select', @cnt);
select @cnt;

call sp_run_sql('select now()', 'test now', @cnt);
select @cnt;

select * from log_trace order by row_id desc limit 5\G

call sp_run_sql('select now(', 'catch errors', @cnt);

select * from log_trace order by row_id desc limit 5\G