title: MySQL 5.6 GET DIAGNOSTICS into log table and ROLLBACK previous DML
body: <p>Apparently I cannot combine the GET DIAGNOSTICS and ROLLBACK statements inside a stored procedure: besides catching the error, I want to be able to reverse all the previous processed data, not just to stop the execution. Please find bellow my script:</p>
  
  <ul>
  <li>Create the log table:</li>
  </ul>
  
  <pre>
  CREATE TABLE tbl_logs (
  
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  
  `txt` VARCHAR(100) NOT NULL,
  
  PRIMARY KEY (`id`));
  </pre>
  
  <ul>
  <li>Create the stored procedure:</li>
  </ul>
  
  <pre>
  DELIMITER $$
  
  CREATE PROCEDURE `test`() 
  
  BEGIN 
  
  DECLARE state CHAR(5) DEFAULT ’00000′;
  
  DECLARE msg TEXT;
  
  DECLARE EXIT HANDLER FOR SQLEXCEPTION 
  
  begin 
  
     rollback;
  
     GET DIAGNOSTICS CONDITION 1 state = RETURNED_SQLSTATE, msg = MESSAGE_TEXT;
  
     insert into tbl_logs (txt) select concat(‘Error ‘,state,’: ‘,msg);
  
   end;
  
  start transaction; 
  
     insert into tbl_logs (txt) select state;
  
     – drop table no_such_table;
  
     insert into tbl_logs (txt) select ‘commit’; 
  
  commit; 
  
  END
  </pre>
  
  <ul>
  <li>call the procedure:</li>
  </ul>
  
  <pre>
  call test(); 
  
  select * from tbl_logs;
  </pre>
  
  <p>=> check the table: 2 rows</p>
  
  <p><strong>00000</strong></p>
  
  <p><strong>commit</strong></p>
  
  <hr>
  
  <ul>
  <li><p>Alter the procedure:</p>
  
  <p>Take the comment out, making the drop table visible.</p></li>
  </ul>
  
  <hr>
  
  <ul>
  <li>call the procedure:</li>
  </ul>
  
  <pre>
  call test();
  
  select * from tbl_logs;
  </pre>
  
  <p>=> check the table: 2 new rows instead of just one, the last</p>
  
  <p><strong>00000</strong></p>
  
  <p><strong>Error 42S02: Unknown table ‘no_such_table’</strong></p>
  
  <hr>
  
  <p>=> the handler catches the error and stops the execution, is just that doesn’t consider the rollback (no matter what/where other DML are previously made, they are committed)…</p>
  
  <p>What am I doing wrong?</p>