  select 
      body 
    from 
      mysql.proc 
    where 
      db = rdebug_routine_schema and name = rdebug_routine_name 
    into rdebug_routine_body;
  if rdebug_routine_body is null then
    leave main_body;
  end if;

  set rdebug_routine_body := replace_sections(rdebug_routine_body, debug_code_start, debug_code_end, '');
  select 
    rdebug_routine_schema as routine_schema, 
    rdebug_routine_name as routine_name,
    rdebug_statement_id as statement_id,
    substring(rdebug_routine_body, rdebug_start_pos, (rdebug_end_pos - rdebug_start_pos)) as `statement`
  ;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `rdebug_start` */;
DELIMITER ;;
CREATE DEFINER=`percona`@`localhost` PROCEDURE `rdebug_start`(
  recipient_id int unsigned
  )
    MODIFIES SQL DATA
    DETERMINISTIC
    SQL SECURITY INVOKER
BEGIN
  do get_lock(_rdebug_get_lock_name(connection_id(), 'debugger'), 0);
  if not get_lock(_rdebug_get_lock_name(recipient_id, 'worker'), 0) then
    do release_lock(_rdebug_get_lock_name(connection_id(), 'debugger'));
    call throw(CONCAT('Cannot obtain recipient lock for thread ', recipient_id));
  end if;
  set @_rdebug_recipient_id := recipient_id;
  set @_rdebug_worker_done := false;
  delete from _rdebug_routine_variables_state where worker_id = @_rdebug_recipient_id;
  delete from _rdebug_stack_state where worker_id = @_rdebug_recipient_id;
  
  delete from _rdebug_step_hints where worker_id = @_rdebug_recipient_id;
  delete from _rdebug_stats where worker_id = @_rdebug_recipient_id;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `rdebug_step_into` */;
DELIMITER ;;
CREATE DEFINER=`percona`@`localhost` PROCEDURE `rdebug_step_into`()
    NO SQL
    DETERMINISTIC
    SQL SECURITY INVOKER
main_body: BEGIN
  call _rdebug_set_step_hint('step_into');
  

  
  call _rdebug_release_worker_and_wait_for_breakpoint();
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `rdebug_step_out` */;
DELIMITER ;;
CREATE DEFINER=`percona`@`localhost` PROCEDURE `rdebug_step_out`()
    NO SQL
    DETERMINISTIC
    SQL SECURITY INVOKER
main_body: BEGIN
  call _rdebug_set_step_hint('step_out');
  

  
  call _rdebug_release_worker_and_wait_for_breakpoint();
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `rdebug_step_over` */;
DELIMITER ;;
CREATE DEFINER=`percona`@`localhost` PROCEDURE `rdebug_step_over`()
    NO SQL
    DETERMINISTIC
    SQL SECURITY INVOKER
main_body: BEGIN
  call _rdebug_set_step_hint('step_over');
  

  
  call _rdebug_release_worker_and_wait_for_breakpoint();
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `rdebug_stop` */;
DELIMITER ;;
CREATE DEFINER=`percona`@`localhost` PROCEDURE `rdebug_stop`()
    MODIFIES SQL DATA
    DETERMINISTIC
    SQL SECURITY INVOKER
main_body: BEGIN
  do release_lock(_rdebug_get_lock_name(connection_id(), 'debugger'));
  if @_rdebug_recipient_id is null then
    leave main_body;
  end if;
  do release_lock(_rdebug_get_lock_name(@_rdebug_recipient_id, 'worker'));
  do release_lock(_rdebug_get_lock_name(@_rdebug_recipient_id, 'tic'));
  do release_lock(_rdebug_get_lock_name(@_rdebug_recipient_id, 'toc'));

  delete from _rdebug_routine_variables_state where worker_id = @_rdebug_recipient_id;
  delete from _rdebug_stack_state where worker_id = @_rdebug_recipient_id;
  delete from _rdebug_breakpoint_hints where worker_id = @_rdebug_recipient_id;
  delete from _rdebug_step_hints where worker_id = @_rdebug_recipient_id;
  delete from _rdebug_stats where worker_id = @_rdebug_recipient_id;
  
  set @_rdebug_recipient_id := null;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `rdebug_verbose` */;
DELIMITER ;;
CREATE DEFINER=`percona`@`localhost` PROCEDURE `rdebug_verbose`()
    NO SQL
    DETERMINISTIC
    SQL SECURITY INVOKER
main_body: BEGIN
  call rdebug_show_stack_state();
  call rdebug_watch_variables();
  call rdebug_show_statement();
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `rdebug_watch_variables` */;
DELIMITER ;;
CREATE DEFINER=`percona`@`localhost` PROCEDURE `rdebug_watch_variables`()
    READS SQL DATA
    DETERMINISTIC
    SQL SECURITY INVOKER
main_body: BEGIN
	
  select 
      routine_schema, routine_name, variable_name, variable_type, variable_value
    from 
      (select * from _rdebug_stack_state where worker_id = @_rdebug_recipient_id
       order by stack_level desc limit 1) select_current_stack_state
      join _rdebug_routine_variables using (routine_schema, routine_name)
      join _rdebug_routine_variables_state using (worker_id, stack_level, routine_schema, routine_name, variable_name)
    where
      statement_id between variable_scope_id_start and variable_scope_id_end
    order by 
      variable_name
    ;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `repeat_exec` */;
DELIMITER ;;
CREATE DEFINER=`percona`@`localhost` PROCEDURE `repeat_exec`(interval_seconds DOUBLE, execute_queries TEXT CHARSET utf8, stop_condition TEXT CHARSET utf8)
    MODIFIES SQL DATA
    SQL SECURITY INVOKER
_proc_body: begin
  declare repeat_complete TINYINT UNSIGNED DEFAULT 0;
  declare num_iterations INT UNSIGNED DEFAULT 0;
  declare stop_iterations INT UNSIGNED DEFAULT NULL;
  declare stop_seconds INT UNSIGNED DEFAULT NULL;
  declare start_timestamp TIMESTAMP DEFAULT NOW();
  declare stop_condition_type enum('invalid', 'no_limit', 'no_affected', 'iterations', 'time', 'query') DEFAULT 'invalid';
  
  if stop_condition IS NULL then
    set stop_condition_type := 'no_limit';
  elseif stop_condition = '0' then
    set stop_condition_type := 'no_affected';
  elseif stop_condition rlike '^[0-9]+$' then
    set stop_iterations := CAST(stop_condition AS UNSIGNED INTEGER);
    set stop_condition_type := 'iterations';
  elseif CHAR_LENGTH(stop_condition) <= 16 then
    
    set stop_seconds := shorttime_to_seconds(stop_condition);
    if stop_seconds IS NOT NULL then
      set stop_condition_type := 'time';
    end if;
  elseif _is_select_query(stop_condition) then
    set stop_condition_type := 'query';
    set @repeat_exec_stop_condition_query := CONCAT('SELECT (', stop_condition, ') INTO @repeat_exec_query_condition');
  end if;

  if stop_condition_type = 'invalid' then
    
    
    set @common_schema_error := 'repeat_exec: invalid stop_condition';
    leave _proc_body;
  end if;
  
  repeat
    set num_iterations := num_iterations + 1;
    set @_execute_queries := REPLACE(execute_queries, '${NR}', num_iterations);
    call exec(@_execute_queries);
    
    if stop_condition_type = 'no_limit' then
      
      set repeat_complete := 0;
    elseif stop_condition_type = 'no_affected' then
      if @common_schema_rowcount = 0 then
        set repeat_complete := 1;
      end if;
    elseif stop_condition_type = 'time' then
      if TIMESTAMPDIFF(SECOND, start_timestamp, SYSDATE()) >= stop_seconds then 
	    set repeat_complete := 1;
	  end if;
    elseif stop_condition_type = 'iterations' then
      if num_iterations >= stop_iterations then
        set repeat_complete := 1;
      end if;
    elseif stop_condition_type = 'query' then
      PREPARE st FROM @repeat_exec_stop_condition_query;
      EXECUTE st;
      DEALLOCATE PREPARE st;
      if @repeat_exec_query_condition then
        set repeat_complete := 1;
      end if;
    end if;
    
    if repeat_complete = 0 then
      if IFNULL(interval_seconds, 0) > 0 then
        DO SLEEP(interval_seconds);
      end if;
    end if;
  until repeat_complete
  end repeat;
end ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `run` */;
DELIMITER ;;
CREATE DEFINER=`percona`@`localhost` PROCEDURE `run`(
  in query_script text
)
    MODIFIES SQL DATA
    DETERMINISTIC
    SQL SECURITY INVOKER
    COMMENT 'Run given QueryScript text'
main_body: begin
  if (LEFT(query_script, 1) in ('/', '\\')) and (LEFT(query_script, 2) != '/*') then
    begin
	  declare query_script_file_name text;  
      
      set query_script_file_name := query_script;
      set query_script := LOAD_FILE(query_script_file_name);
      if query_script is null then
        call throw(CONCAT('Cannot load script file: ', query_script_file_name));
      end if;
    end;
  end if;
  call _interpret(query_script, TRUE);
end ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `run_file` */;
DELIMITER ;;
CREATE DEFINER=`percona`@`localhost` PROCEDURE `run_file`(
  in query_script_file_name text
)
    MODIFIES SQL DATA
    DETERMINISTIC
    SQL SECURITY INVOKER
    COMMENT 'Run given QueryScript file'
main_body: begin
  call run(LOAD_FILE(query_script_file_name));
end ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `security_audit` */;
DELIMITER ;;
CREATE DEFINER=`percona`@`localhost` PROCEDURE `security_audit`()
    READS SQL DATA
    SQL SECURITY INVOKER
    COMMENT 'Kills connections based on grantee term'
begin
  call _run_named_script('security_audit');
end ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `table_rotate` */;
DELIMITER ;;
CREATE DEFINER=`percona`@`localhost` PROCEDURE `table_rotate`(
    IN table_schema varchar(64) charset utf8, 
    IN table_name varchar(64) charset utf8,
    IN rotate_limit smallint unsigned
  )
    MODIFIES SQL DATA
    SQL SECURITY INVOKER
    COMMENT 'Rotate a table logrotate-style'
begin
  declare rotate_index smallint unsigned;
  declare rotate_statement TEXT charset utf8 default null;
    
  if not table_exists(table_schema, table_name) then
    call throw(concat('Cannot rotate non-existing table: ', mysql_qualify(table_schema), '.', mysql_qualify(table_name)));
  end if;
 
  if IFNULL(rotate_limit, 0) > 0 then
    
    set rotate_statement := concat(
        'DROP TABLE IF EXISTS ', 
        mysql_qualify(table_schema), '.', mysql_qualify(concat(table_name, '__', rotate_limit))
    );  
    call exec(rotate_statement);
  end if;
  
  
  set rotate_statement := concat(
      'CREATE TABLE ', 
      mysql_qualify(table_schema), '.', mysql_qualify(concat(table_name, '__0')), ' LIKE ',
      mysql_qualify(table_schema), '.', mysql_qualify(table_name)
  );  
  call exec(rotate_statement);
 
  
  set rotate_statement := '';
  set rotate_index := 1;
  while table_exists(table_schema, concat(table_name, '__', rotate_index)) do
    set rotate_statement := concat(
        mysql_qualify(table_schema), '.', mysql_qualify(concat(table_name, '__', rotate_index)),
          ' TO ', mysql_qualify(table_schema), '.', mysql_qualify(concat(table_name, '__', rotate_index+1)),
        ', ', rotate_statement
      );
    set rotate_index := rotate_index + 1;
  end while;
  set rotate_statement := concat(
      'RENAME TABLE ', rotate_statement, 
      mysql_qualify(table_schema), '.', mysql_qualify(table_name),
        ' TO ', mysql_qualify(table_schema), '.', mysql_qualify(concat(table_name, '__', 1)),
      ', ',
      mysql_qualify(table_schema), '.', mysql_qualify(concat(table_name, '__0')),
        ' TO ', mysql_qualify(table_schema), '.', mysql_qualify(table_name)
    );
  
  call exec(rotate_statement);
end ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `thread_notify` */;
DELIMITER ;;
CREATE DEFINER=`percona`@`localhost` PROCEDURE `thread_notify`(
    in thread_wait_name varchar(128) character set ascii)
    NO SQL
    DETERMINISTIC
    SQL SECURITY INVOKER
begin
  insert 
    into _waits (wait_name, wait_value)
    values (thread_wait_name, 1)
    on duplicate key update wait_value = wait_value + 1, last_entry_time = NOW()
  ;
end ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `thread_wait` */;
DELIMITER ;;
CREATE DEFINER=`percona`@`localhost` PROCEDURE `thread_wait`(
    in thread_wait_name varchar(128) character set ascii, 
    in poll_seconds double,
    in atomic_execute_query text charset utf8,
    in alternate_end_wait_condition text charset utf8)
    READS SQL DATA
    DETERMINISTIC
    SQL SECURITY INVOKER
begin
  declare current_wait_value bigint unsigned;
  
  if alternate_end_wait_condition is not null then
  	set alternate_end_wait_condition := concat('select (', alternate_end_wait_condition, ') is true into @common_schema_thread_wait_end_condition');
  end if;
  
  select 
      ifnull(max(wait_value), 0) 
    from _waits 
    where wait_name = thread_wait_name 
    into current_wait_value;
  commit;
  
  if atomic_execute_query is not null then
  	call exec_single(atomic_execute_query);
  end if;
  
  set @common_schema_thread_wait_end_condition := false;
  wait_loop: while (select ifnull(max(wait_value), 0) <= current_wait_value from _waits where wait_name = thread_wait_name) do
    if alternate_end_wait_condition is not null then
      call exec_single(alternate_end_wait_condition);
      if @common_schema_thread_wait_end_condition then
        leave wait_loop;
      end if;
    end if;  
    do sleep(poll_seconds + coalesce(0, 'common_schema_thread_wait'));
    commit;
  end while;
end ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `throw` */;
DELIMITER ;;
CREATE DEFINER=`percona`@`localhost` PROCEDURE `throw`(error_message VARCHAR(1024) CHARSET utf8)
    NO SQL
    DETERMINISTIC
    SQL SECURITY INVOKER
    COMMENT 'Raise an error'
main_body: begin
  declare error_statement VARCHAR(1500) CHARSET utf8;

  set @common_schema_error := error_message;
  
  SET @common_schema_error := LEFT(@common_schema_error, 128);
  SIGNAL SQLSTATE VALUE '91100' SET MESSAGE_TEXT = @common_schema_error;
   
  
  set error_statement := CONCAT('SELECT error FROM error.`', error_message, '`');
  call exec_single(error_statement);
end ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `tokenize` */;
DELIMITER ;;
CREATE DEFINER=`percona`@`localhost` PROCEDURE `tokenize`(txt TEXT CHARSET utf8, delimiter_text VARCHAR(255) CHARSET utf8)
    READS SQL DATA
    SQL SECURITY INVOKER
begin
  declare num_tokens INT UNSIGNED DEFAULT get_num_tokens(txt, delimiter_text);  
  SELECT n, split_token(txt, delimiter_text, n) AS token FROM numbers WHERE n BETWEEN 1 AND num_tokens;
end ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `_assign_input_local_variables` */;
DELIMITER ;;
CREATE DEFINER=`percona`@`localhost` PROCEDURE `_assign_input_local_variables`(
   variables_array_id int unsigned
)
    MODIFIES SQL DATA
    DETERMINISTIC
    SQL SECURITY INVOKER
    COMMENT 'Declares local variables'
main_body: begin
  declare num_variables int unsigned;
  declare variable_index int unsigned default 0;
  declare local_variable varchar(65) charset ascii;
  declare user_defined_variable_name varchar(65) charset ascii;
  declare reset_query text charset ascii;
  
  call _get_array_size(variables_array_id, num_variables);
  set variable_index := 1;
  while variable_index <= num_variables do
    call _get_array_element(variables_array_id, variable_index, local_variable);
    SELECT mapped_user_defined_variable_name FROM _qs_variables WHERE variable_name = local_variable INTO user_defined_variable_name;
    
    set reset_query := CONCAT('SET ', user_defined_variable_name, ' := @_query_script_input_col', variable_index);
    call exec_single(reset_query);
    
    set variable_index := variable_index + 1;
  end while;
end ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `_cleanup_script_tables` */;
DELIMITER ;;
CREATE DEFINER=`percona`@`localhost` PROCEDURE `_cleanup_script_tables`()
    MODIFIES SQL DATA
    DETERMINISTIC
    SQL SECURITY INVOKER
    COMMENT '...'
main_body: begin
  if not (@query_script_skip_cleanup is true) then
    delete from _sql_tokens;
    delete from _qs_variables;
    delete from _script_report_data;
    delete from _split_column_names_table;
  end if;
end ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `_consume_expression` */;
DELIMITER ;;
CREATE DEFINER=`percona`@`localhost` PROCEDURE `_consume_expression`(
   in   id_from      int unsigned,
   in   id_to      int unsigned,
   in   require_parenthesis tinyint unsigned,
   out  consumed_to_id int unsigned,
   out  expression text charset utf8,
   out  expression_statement text charset utf8,
   in   should_execute_statement tinyint unsigned
)
    READS SQL DATA
    DETERMINISTIC
    SQL SECURITY INVOKER
    COMMENT 'Reads expression'
main_body: begin
    declare first_state text;
    declare expression_level int unsigned;
    declare id_end_expression int unsigned; 
    declare expanded_variables TEXT CHARSET utf8;
    declare expanded_variables_found tinyint unsigned;
    
    set expression_statement := NULL ;
    
    call _skip_spaces(id_from, id_to);
    SELECT level, state FROM _sql_tokens WHERE id = id_from INTO expression_level, first_state;

    if (first_state = 'left parenthesis') then
      SELECT MIN(id) FROM _sql_tokens WHERE id > id_from AND state = 'right parenthesis' AND level = expression_level INTO id_end_expression;
  	  if id_end_expression IS NULL then
	    call _throw_script_error(id_from, 'Unmatched "(" parenthesis');
	  end if;
	  set id_from := id_from + 1;
      call _skip_spaces(id_from, id_to);
      
      call _expand_statement_variables(id_from, id_end_expression-1, expression, expanded_variables_found, should_execute_statement);
      
      
      
      SELECT token FROM _sql_tokens WHERE id = id_from AND state = 'alpha' INTO expression_statement;
      if ((expression is NULL) or (trim_wspace(expression) = '')) and (should_execute_statement or not expanded_variables_found) then
        call _throw_script_error(id_from, 'Found empty expression');
      end if;
      
      set consumed_to_id := id_end_expression;
    else
      if require_parenthesis then
        call _throw_script_error(id_from, 'Expected "(" on expression');
      end if;
    end if;
end ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `_consume_foreach_expression` */;
DELIMITER ;;
CREATE DEFINER=`percona`@`localhost` PROCEDURE `_consume_foreach_expression`(
   in   id_from      int unsigned,
   in   id_to      int unsigned,
   out  consumed_to_id int unsigned,
   in   depth int unsigned,
   out  collection text charset utf8,
   out  variables_array_id int unsigned,
   out  variables_delaration_id int unsigned,
   in should_execute_statement tinyint unsigned
)
    READS SQL DATA
    DETERMINISTIC
    SQL SECURITY INVOKER
    COMMENT 'Reads foreach() expression'
main_body: begin
    declare first_state text;
    declare expression_level int unsigned;
    declare id_end_expression int unsigned default NULL; 
    declare id_end_variables_definition int unsigned default NULL; 
    
    call _skip_spaces(id_from, id_to);
    SELECT level, state FROM _sql_tokens WHERE id = id_from INTO expression_level, first_state;

    if (first_state != 'left parenthesis') then
      call _throw_script_error(id_from, 'Expected "(" on foreach expression');
    end if;
    
    SELECT MIN(id) FROM _sql_tokens WHERE id > id_from AND state = 'right parenthesis' AND level = expression_level INTO id_end_expression;
  	if id_end_expression IS NULL then
	  call _throw_script_error(id_from, 'Unmatched "(" parenthesis');
	end if;
	
	
    SELECT MIN(id) FROM _sql_tokens WHERE id > id_from AND state = 'colon' AND level = expression_level INTO id_end_variables_definition;
  	if id_end_variables_definition IS NULL then
	  call _throw_script_error(id_from, 'foreach: expected ":" as in (variables : collection)');
	end if;
	
    set id_from := id_from + 1;
	
	
    call _expect_dynamic_states_list(id_from, id_end_variables_definition-1, 'query_script variable', variables_array_id);
	set variables_delaration_id := id_from;
	call _declare_local_variables(id_from, id_to, id_end_variables_definition, depth, variables_array_id);
		
    
	set id_from := id_end_variables_definition + 1;
    call _skip_spaces(id_from, id_to);

    call _expand_statement_variables(id_from, id_end_expression-1, collection, @_common_schema_dummy, should_execute_statement);
    

    set consumed_to_id := id_end_expression;
end ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `_consume_if_exists` */;
DELIMITER ;;
CREATE DEFINER=`percona`@`localhost` PROCEDURE `_consume_if_exists`(
   in   id_from      int unsigned,
   in   id_to      int unsigned,
   inout  consumed_to_id int unsigned,
   in   expected_token text charset utf8,
   in   expected_states text charset utf8,
   out  token_has_matched tinyint unsigned,
   out  matched_token text charset utf8
)
    READS SQL DATA
    DETERMINISTIC
    SQL SECURITY INVOKER
    COMMENT 'Consumes token or state if indeed exists'
main_body: begin
  call _skip_spaces(id_from, id_to);
  set token_has_matched := FALSE;
  SELECT token, ((token = expected_token) OR FIND_IN_SET(state, REPLACE(expected_states, '|', ','))) IS TRUE FROM _sql_tokens WHERE id = id_from INTO matched_token, token_has_matched;
  if token_has_matched then
    set consumed_to_id = id_from;
  end if;
end ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `_consume_script_statement` */;
DELIMITER ;;
CREATE DEFINER=`percona`@`localhost` PROCEDURE `_consume_script_statement`(
   in   id_from      int unsigned,
   in   id_to      int unsigned,
   in   statement_id_from      int unsigned,
   in   statement_id_to      int unsigned,
   in   depth int unsigned,
   in   script_statement text charset utf8,
   in should_execute_statement tinyint unsigned
)
    MODIFIES SQL DATA
    DETERMINISTIC
    SQL SECURITY INVOKER
    COMMENT 'Reads script statement'
main_body: begin
  declare tokens_array_id int unsigned;
  declare tokens_array_element text charset utf8;
  declare matched_token text charset utf8;
  declare statement_arguments text charset utf8;
  declare expanded_variables_found tinyint unsigned;
  
  call _expand_statement_variables(id_from+1, statement_id_to, statement_arguments, expanded_variables_found, should_execute_statement);
  
  set tokens_array_id := NULL;
  
  case script_statement
	when 'start' then begin
	    call _expect_token(statement_id_from, statement_id_to, 'transaction', false, @_common_schema_dummy, @_common_schema_dummy);
        if should_execute_statement then
          start transaction;
        end if;
	  end;
	when 'begin' then begin
		call _expect_nothing(statement_id_from, statement_id_to);
        if should_execute_statement then
          start transaction;
        end if;
	  end;
	when 'commit' then begin
		call _expect_nothing(statement_id_from, statement_id_to);
        if should_execute_statement then
          commit;
        end if;
	  end;
	when 'rollback' then begin
		call _expect_nothing(statement_id_from, statement_id_to);
        if should_execute_statement then
          rollback;
        end if;
	  end;
	when 'echo' then begin
        if should_execute_statement then
          select trim_wspace(statement_arguments) as echo;
        end if;
	  end;
	when 'eval' then begin
        if should_execute_statement then
          call eval(statement_arguments);
        end if;
	  end;
	when 'pass' then begin
	    call _expect_nothing(statement_id_from, statement_id_to);
	  end;
	when 'sleep' then begin
	    call _expect_state(statement_id_from, statement_id_to, 'integer|decimal|user-defined variable|query_script variable', false, @_common_schema_dummy, matched_token);
	    
        if should_execute_statement then
          call exec(CONCAT('SET @_common_schema_intermediate_var := ', statement_arguments));
          DO SLEEP(CAST(trim_wspace(@_common_schema_intermediate_var) AS DECIMAL(64, 2)));
        end if;
	  end;
	when 'throttle' then begin
	    call _expect_state(statement_id_from, statement_id_to, 'integer|decimal|user-defined variable|query_script variable', false, @_common_schema_dummy, matched_token);
	    
        if should_execute_statement then
          call exec(CONCAT('SET @_common_schema_intermediate_var := ', statement_arguments));
          call _throttle_script(CAST(trim_wspace(@_common_schema_intermediate_var) AS DECIMAL(64, 2)));
        end if;
	  end;
    when 'throw' then begin
	    call _expect_state(statement_id_from, statement_id_to, 'string|user-defined variable|query_script variable', false, @_common_schema_dummy, matched_token);
        if should_execute_statement then
          call exec(CONCAT('SET @_common_schema_intermediate_var := ', statement_arguments));
          call throw(@_common_schema_intermediate_var);
        end if;
	  end;
    when 'var' then begin
	    call _peek_states_list(statement_id_from, statement_id_to, 'query_script variable,assign', true, true, false, @_common_schema_dummy, @_common_schema_peek_to_id);
	    if @_common_schema_peek_to_id > 0 then
	      
	      call _declare_and_assign_local_variable(id_from, id_to, statement_id_from, @_common_schema_peek_to_id, statement_id_to, depth, should_execute_statement);
	    else
          call _expect_dynamic_states_list(statement_id_from, statement_id_to, 'query_script variable', tokens_array_id);
          call _declare_local_variables(id_from, id_to, statement_id_to, depth, tokens_array_id);
	    end if;
	  end;
    when 'input' then begin
	    if @_common_schema_script_loop_nesting_level > 0 then
          call _throw_script_error(id_from, CONCAT('Invalid loop nesting level for INPUT: ', @_common_schema_script_loop_nesting_level));
	    end if;
	    call _expect_dynamic_states_list(statement_id_from, statement_id_to, 'query_script variable', tokens_array_id);
		call _declare_local_variables(id_from, id_to, statement_id_to, depth, tokens_array_id);
        if should_execute_statement then
          call _assign_input_local_variables(tokens_array_id);
        end if;
	  end;
	when 'report' then begin
        if should_execute_statement then
          call _script_report(statement_arguments);
        end if;
	  end;
    else begin 
	    
	    call _throw_script_error(id_from, CONCAT('Unknown script statement: "', script_statement, '"'));
	  end;
  end case;
  if tokens_array_id is not null then
    call _drop_array(tokens_array_id);
  end if;
end ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `_consume_split_statement` */;
DELIMITER ;;
CREATE DEFINER=`percona`@`localhost` PROCEDURE `_consume_split_statement`(
   in   id_from      int unsigned,
   in   id_to      int unsigned,
   out  consumed_to_id int unsigned,
   in   depth int unsigned,
   out  split_table_schema tinytext charset utf8,
   out  split_table_name tinytext charset utf8,
   out  split_injected_action_statement text charset utf8,
   out  split_injected_text tinytext charset utf8,
   out	split_options varchar(2048) charset utf8,
   in should_execute_statement tinyint unsigned
)
    READS SQL DATA
    DETERMINISTIC
    SQL SECURITY INVOKER
    COMMENT 'Reads split() expression'
main_body: begin
    declare first_state text;
    declare expression_level int unsigned;
    declare id_end_action_statement int unsigned default NULL; 
    declare id_end_split_table_declaration int unsigned default NULL; 
    declare query_type_supported tinyint unsigned;
    declare tables_found varchar(32) charset ascii;
    declare colon_exists tinyint unsigned default false;
    declare found_explicit_table tinyint unsigned;
    declare found_any_params tinyint unsigned;
    declare found_possible_statement tinyint unsigned;
    
    call _skip_spaces(id_from, id_to);
    SELECT level, state FROM _sql_tokens WHERE id = id_from INTO expression_level, first_state;

    
    if (first_state != 'left parenthesis') then
      call _throw_script_error(id_from, 'Expected "(" on split expression');
    end if;
    
    SELECT MIN(id) FROM _sql_tokens WHERE id > id_from AND state = 'right parenthesis' AND level = expression_level INTO id_end_action_statement;
  	if id_end_action_statement IS NULL then
	  call _throw_script_error(id_from, 'Unmatched "(" parenthesis');
	end if;
	
	
    SELECT MIN(id) FROM _sql_tokens WHERE id > id_from AND state = 'colon' AND level = expression_level INTO id_end_split_table_declaration;
    set colon_exists := (id_end_split_table_declaration IS NOT NULL);
  	if id_end_split_table_declaration IS NULL then
  	  set id_end_split_table_declaration := id_end_action_statement;
	end if;
	
    set id_from := id_from + 1;
	
    set split_injected_text := '[:_query_script_split_injected_placeholder:]';
	
    set found_possible_statement := true;
    if colon_exists then
      call _consume_split_statement_params(id_from, id_end_split_table_declaration-1, should_execute_statement, split_table_schema, split_table_name, split_options, found_explicit_table, found_any_params);
      if not found_any_params then
        call _throw_script_error(id_from, 'split: must indicate table or options before colon; otherwise drop colon');
      end if;

      
      set id_from := id_end_split_table_declaration + 1;
      call _skip_spaces(id_from, id_to);
    else
      
      call _consume_split_statement_params(id_from, id_end_split_table_declaration-1, should_execute_statement, split_table_schema, split_table_name, split_options, found_explicit_table, found_any_params);
      if found_any_params then
        
        set found_possible_statement := false;
      end if;
    end if;
    
    if not (found_possible_statement or found_explicit_table) then
      call _throw_script_error(id_from, 'split: no statement nor table provided. Provide at least either one');
    end if;
    
    if found_possible_statement then
      if not found_explicit_table then
        call _skip_spaces(id_from, id_end_action_statement - 1);
        call _get_split_query_single_table(id_from, id_end_action_statement - 1, query_type_supported, tables_found, split_table_schema, split_table_name);
        call _expand_single_variable(id_from, id_end_action_statement - 1, split_table_schema, should_execute_statement);
        call _expand_single_variable(id_from, id_end_action_statement - 1, split_table_name, should_execute_statement);
        if should_execute_statement and ((split_table_schema is null) or (split_table_name is null)) then
          
          call _throw_script_error(id_from, 'split() cannot deduce split table name. Please specify explicitly');
        end if;
      end if;
      call _inject_split_where_token(id_from, id_end_action_statement - 1, split_injected_text, should_execute_statement, split_injected_action_statement);
    else
      set split_injected_action_statement := CONCAT('SELECT COUNT(NULL) FROM ', mysql_qualify(split_table_schema), '.', mysql_qualify(split_table_name), ' WHERE ', split_injected_text, ' INTO @_common_schema_dummy');
    end if;
    
    set consumed_to_id := id_end_action_statement;
end ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `_consume_split_statement_params` */;
DELIMITER ;;
CREATE DEFINER=`percona`@`localhost` PROCEDURE `_consume_split_statement_params`(
   in   id_from      int unsigned,
   in   id_to      int unsigned,
   in   should_execute_statement tinyint unsigned,
   out  split_table_schema tinytext charset utf8,
   out  split_table_name tinytext charset utf8,
   out  split_options varchar(2048) charset utf8,
   out  found_explicit_table tinyint unsigned,
   out  found_any_params tinyint unsigned
)
    READS SQL DATA
    DETERMINISTIC
    SQL SECURITY INVOKER
    COMMENT 'Reads split() parameters'
main_body: begin
    declare expanded_statement mediumtext charset utf8;
    declare options_value mediumtext charset utf8;
    
    call _skip_spaces(id_from, id_to);

    set split_table_schema := null;
    set split_table_name := null;
    set split_options := null;
    set found_explicit_table := false;

    call _expand_statement_variables(id_from, id_to, expanded_statement, @_common_schema_dummy, should_execute_statement);
    
    if _is_options_format(expanded_statement) then
      set split_options := expanded_statement;
      set options_value := get_option(split_options, 'table');
      if options_value is not null then
        if get_num_tokens(options_value, '.') = 2 then
          set split_table_schema := unquote(split_token(options_value, '.', 1));
          set split_table_name := unquote(split_token(options_value, '.', 2));
          set found_explicit_table := true;
        else
          call _throw_script_error(id_from, '''table'' option in split(): expected format schema_name.table_name');
        end if;
      end if;
    else
      begin
        
        declare peek_match_to int unsigned default NULL; 
        declare table_array_id varchar(16);
        
        call _create_array(table_array_id);
        call _peek_states_list(id_from, id_to, 'alpha|alphanum|quoted identifier|expanded query_script variable,dot,alpha|alphanum|quoted identifier|expanded query_script variable', false, false, true, table_array_id, peek_match_to);
        if peek_match_to > 0 then
          
          call _get_array_element(table_array_id, '1', split_table_schema);		
          call _get_array_element(table_array_id, '3', split_table_name);
          call _expand_single_variable(id_from, id_to, split_table_schema, should_execute_statement);
          call _expand_single_variable(id_from, id_to, split_table_name, should_execute_statement);
          set split_table_schema := unquote(split_table_schema);
          set split_table_name := unquote(split_table_name);

          set found_explicit_table := true;
        end if;
        call _drop_array(table_array_id);
      end;
    end if;
    set found_any_params := (split_options is not null) or (found_explicit_table);
end ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `_consume_sql_statement` */;
DELIMITER ;;
CREATE DEFINER=`percona`@`localhost` PROCEDURE `_consume_sql_statement`(
   in   id_from      int unsigned,
   in   statement_id_to      int unsigned,
   in   should_execute_statement tinyint unsigned
)
    MODIFIES SQL DATA
    DETERMINISTIC
    SQL SECURITY INVOKER
    COMMENT 'Consumes & executes SQL statement'
main_body: begin
  declare mysql_statement TEXT CHARSET utf8;
  
  
  call _expand_statement_variables(id_from, statement_id_to, mysql_statement, @_common_schema_dummy, should_execute_statement);
  if should_execute_statement then
    call exec_single(mysql_statement);
    set @query_script_rowcount := @common_schema_rowcount;
    set @query_script_found_rows := @common_schema_found_rows;
  end if;
end ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `_consume_statement` */;
DELIMITER ;;
CREATE DEFINER=`percona`@`localhost` PROCEDURE `_consume_statement`(
   in   id_from      int unsigned,
   in   id_to      int unsigned,
   in   expect_single tinyint unsigned,
   out  consumed_to_id int unsigned,
   in depth int unsigned,
   in should_execute_statement tinyint unsigned
)
    MODIFIES SQL DATA
    DETERMINISTIC
    SQL SECURITY INVOKER
    COMMENT 'Reads (possibly nested) statement'
main_body: begin
    declare first_token text;
    declare first_state text;
    declare statement_level int unsigned;
    declare id_end_statement int unsigned; 
    
    declare statement_delimiter_found tinyint unsigned;
    
    declare expression text charset utf8;
    declare expression_statement text charset utf8;
    declare expression_result tinyint unsigned;
    
    declare peek_match tinyint unsigned;
    declare matched_token text charset utf8;
    
    declare loop_iteration_count bigint unsigned;
    
    declare while_statement_id_from int unsigned;
    declare while_statement_id_to int unsigned;
    declare while_otherwise_statement_id_from int unsigned;
    declare while_otherwise_statement_id_to int unsigned;
    
    declare foreach_statement_id_from int unsigned;
    declare foreach_statement_id_to int unsigned;
    declare foreach_otherwise_statement_id_from int unsigned;
    declare foreach_otherwise_statement_id_to int unsigned;
    
    declare split_statement_id_from int unsigned;
    declare split_statement_id_to int unsigned;
    declare split_options varchar(2048) charset utf8;

    declare if_statement_id_from int unsigned;
    declare if_statement_id_to int unsigned;
    declare else_statement_id_from int unsigned;
    declare else_statement_id_to int unsigned;

    declare try_statement_error_found tinyint unsigned;
    declare try_statement_id_from int unsigned;
    declare try_statement_id_to int unsigned;
    declare catch_statement_id_from int unsigned;
    declare catch_statement_id_to int unsigned;

    declare foreach_variables_statement text charset utf8;
    declare foreach_collection text charset utf8;
    declare foreach_variables_array_id int unsigned;
    declare foreach_variables_delaration_id int unsigned;
    
    declare split_table_schema tinytext charset utf8;
    declare split_table_name tinytext charset utf8;
    declare split_injected_action_statement text charset utf8;
    declare split_injected_text text charset utf8;
    
    declare reset_query text charset utf8;

    statement_loop: while id_from <= id_to do
      if @_common_schema_script_break_type IS NOT NULL then
         set consumed_to_id := id_to;
         leave statement_loop;
      end if;

      SELECT level, token, state FROM _sql_tokens WHERE id = id_from INTO statement_level, first_token, first_state;
      
      case
        when first_state in ('whitespace', 'single line comment', 'multi line comment') then begin
	        
	        set id_from := id_from + 1;
	        iterate statement_loop;
	      end;
        when first_state = 'left braces' then begin
	        
            SELECT MIN(id) FROM _sql_tokens WHERE id > id_from AND state = 'right braces' AND level = statement_level INTO id_end_statement;
  	        if id_end_statement IS NULL then
	          call _throw_script_error(id_from, 'Unmatched "{" brace');
	        end if;
	        call _consume_statement(id_from+1, id_end_statement-1, FALSE, @_common_schema_dummy, depth+1, should_execute_statement);
	        set consumed_to_id := id_end_statement;
          end;
        when first_state = 'alpha' AND (SELECT COUNT(*) = 1 FROM _script_statements WHERE _script_statements.statement = first_token) then begin
	        
	        call _validate_statement_end(id_from, id_to, id_end_statement, statement_delimiter_found);
	        call _resolve_and_consume_sql_or_script_statement(id_from, id_to, id_from + 1, id_end_statement - IF(statement_delimiter_found, 1, 0), depth, first_token, should_execute_statement);
  	        set consumed_to_id := id_end_statement;
          end;
        when first_state = 'alpha' AND first_token = 'while' then begin
	        call _consume_expression(id_from + 1, id_to, TRUE, consumed_to_id, expression, expression_statement, should_execute_statement);
	        set id_from := consumed_to_id + 1;
	        
            set @_common_schema_script_loop_nesting_level := @_common_schema_script_loop_nesting_level + 1;
	        call _consume_statement(id_from, id_to, TRUE, consumed_to_id, depth+1, FALSE);
            set @_common_schema_script_loop_nesting_level := @_common_schema_script_loop_nesting_level - 1;
	        set while_statement_id_from := id_from;
	        set while_statement_id_to := consumed_to_id;
	        
	        set while_otherwise_statement_id_from := NULL;
	        call _consume_if_exists(consumed_to_id + 1, id_to, consumed_to_id, 'otherwise', NULL, peek_match, @_common_schema_dummy);
	        if peek_match then
	          set id_from := consumed_to_id + 1;
              call _consume_statement(id_from, id_to, TRUE, consumed_to_id, depth+1, FALSE);
	          set while_otherwise_statement_id_from := id_from;
              set while_otherwise_statement_id_to := consumed_to_id;
	        end if;
            if should_execute_statement then
              
              set loop_iteration_count := 0;
              interpret_while_loop: while TRUE do
                
                if @_common_schema_script_break_type IS NOT NULL then
                  if @_common_schema_script_break_type = 'break' then
                    set @_common_schema_script_break_type := NULL;
                  end if;
                  leave interpret_while_loop;
                end if;
                
                call _evaluate_expression(expression, expression_statement, expression_result);
                if NOT expression_result then
                  leave interpret_while_loop;
                end if;
                
                set loop_iteration_count := loop_iteration_count + 1;
                call _consume_statement(while_statement_id_from, while_statement_id_to, TRUE, @_common_schema_dummy, depth+1, TRUE);
              end while;
              if loop_iteration_count = 0 then
                
                
                if while_otherwise_statement_id_from IS NOT NULL then
                  call _consume_statement(while_otherwise_statement_id_from, while_otherwise_statement_id_to, TRUE, @_common_schema_dummy, depth+1, TRUE);
                end if;
              end if;
            end if;
	      end;
        when first_state = 'alpha' AND first_token = 'loop' then begin
	        
	        set id_from := id_from + 1;
            set @_common_schema_script_loop_nesting_level := @_common_schema_script_loop_nesting_level + 1;
	        call _consume_statement(id_from, id_to, TRUE, consumed_to_id, depth+1, FALSE);
            set @_common_schema_script_loop_nesting_level := @_common_schema_script_loop_nesting_level - 1;
	        set while_statement_id_from := id_from;
	        set while_statement_id_to := consumed_to_id;
	        call _consume_if_exists(consumed_to_id + 1, id_to, consumed_to_id, 'while', NULL, peek_match, @_common_schema_dummy);
	        if peek_match then
	          call _consume_expression(consumed_to_id + 1, id_to, TRUE, consumed_to_id, expression, expression_statement, should_execute_statement);
	          set id_from := consumed_to_id + 1;
            else
              call _throw_script_error(id_from, CONCAT('Expcted "while" on loop-while expression'));
	        end if;
	        call _expect_statement_delimiter(consumed_to_id + 1, id_to, consumed_to_id);
            set id_from := consumed_to_id + 1;

            if should_execute_statement then
              interpret_while_loop: while TRUE do
                
                if @_common_schema_script_break_type IS NOT NULL then
                  if @_common_schema_script_break_type = 'break' then
                    set @_common_schema_script_break_type := NULL;
                  end if;
                  leave interpret_while_loop;
                end if;
                
                call _consume_statement(while_statement_id_from, while_statement_id_to, TRUE, @_common_schema_dummy, depth+1, TRUE);
                
                call _evaluate_expression(expression, expression_statement, expression_result);
                if NOT expression_result then
                  leave interpret_while_loop;
                end if;
              end while;
            end if;
	      end;
        when first_state = 'alpha' AND first_token = 'foreach' then begin
	        call _consume_foreach_expression(id_from + 1, id_to, consumed_to_id, depth, foreach_collection, foreach_variables_array_id, foreach_variables_delaration_id, should_execute_statement);

	        set id_from := consumed_to_id + 1;
	        
            set @_common_schema_script_loop_nesting_level := @_common_schema_script_loop_nesting_level + 1;
	        call _consume_statement(id_from, id_to, TRUE, consumed_to_id, depth+1, FALSE);
            set @_common_schema_script_loop_nesting_level := @_common_schema_script_loop_nesting_level - 1;
	        set foreach_statement_id_from := id_from;
	        set foreach_statement_id_to := consumed_to_id;
	        update _qs_variables set scope_end_id = foreach_statement_id_to where declaration_id = foreach_variables_delaration_id;
	        
	        set foreach_otherwise_statement_id_from := NULL;
	        call _consume_if_exists(consumed_to_id + 1, id_to, consumed_to_id, 'otherwise', NULL, peek_match, @_common_schema_dummy);
	        if peek_match then
	          set id_from := consumed_to_id + 1;
              call _consume_statement(id_from, id_to, TRUE, consumed_to_id, depth+1, FALSE);
	          set foreach_otherwise_statement_id_from := id_from;
              set foreach_otherwise_statement_id_to := consumed_to_id;
	        end if;
            if should_execute_statement then
              call _foreach(foreach_collection, NULL, foreach_statement_id_from, foreach_statement_id_to, TRUE, @_common_schema_dummy, foreach_variables_array_id, depth+1, TRUE, loop_iteration_count);
              if loop_iteration_count = 0 then
                
                
                if foreach_otherwise_statement_id_from IS NOT NULL then
                  call _consume_statement(foreach_otherwise_statement_id_from, foreach_otherwise_statement_id_to, TRUE, @_common_schema_dummy, depth+1, TRUE);
                end if;
              end if;
            end if;
            call _drop_array(foreach_variables_array_id);
	      end;
        when first_state = 'alpha' AND first_token = 'split' then begin
	        call _consume_split_statement(id_from + 1, id_to, consumed_to_id, depth, split_table_schema, split_table_name, split_injected_action_statement, split_injected_text, split_options, should_execute_statement);

	        set id_from := consumed_to_id + 1;
	        
            set @_common_schema_script_loop_nesting_level := @_common_schema_script_loop_nesting_level + 1;
	        call _consume_statement(id_from, id_to, TRUE, consumed_to_id, depth+1, FALSE);
            set @_common_schema_script_loop_nesting_level := @_common_schema_script_loop_nesting_level - 1;
	        set split_statement_id_from := id_from;
	        set split_statement_id_to := consumed_to_id;
            if should_execute_statement then
             begin end;
               
               call _split(split_table_schema, split_table_name, split_options, split_injected_action_statement, split_injected_text, split_statement_id_from, split_statement_id_to, TRUE, @_common_schema_dummy, depth+1, TRUE);
            end if;
	      end;
        when first_state = 'alpha' AND first_token = 'if' then begin
	        call _consume_expression(id_from + 1, id_to, TRUE, consumed_to_id, expression, expression_statement, should_execute_statement);
	        set id_from := consumed_to_id + 1;
	        
	        call _consume_statement(id_from, id_to, TRUE, consumed_to_id, depth+1, FALSE);
	        set if_statement_id_from := id_from;
	        set if_statement_id_to := consumed_to_id;
	        
	        set else_statement_id_from := NULL;
	        call _consume_if_exists(consumed_to_id + 1, id_to, consumed_to_id, 'else', NULL, peek_match, @_common_schema_dummy);
	        if peek_match then
	          set id_from := consumed_to_id + 1;
              call _consume_statement(id_from, id_to, TRUE, consumed_to_id, depth+1, FALSE);
	          set else_statement_id_from := id_from;
              set else_statement_id_to := consumed_to_id;
	        end if;
            if should_execute_statement then
              
              call _evaluate_expression(expression, expression_statement, expression_result);
              if expression_result then
                
                call _consume_statement(if_statement_id_from, if_statement_id_to, TRUE, @_common_schema_dummy, depth+1, TRUE);
              else
                
                if else_statement_id_from IS NOT NULL then
                  call _consume_statement(else_statement_id_from, else_statement_id_to, TRUE, @_common_schema_dummy, depth+1, TRUE);
                end if;
              end if;
            end if;
	      end;
        when first_state = 'alpha' AND first_token = 'try' then begin
	        
            set id_from := id_from + 1;
            call _consume_statement(id_from, id_to, TRUE, consumed_to_id, depth+1, FALSE);
            set try_statement_id_from := id_from;
            set try_statement_id_to := consumed_to_id;
            
            call _consume_if_exists(consumed_to_id + 1, id_to, consumed_to_id, 'catch', NULL, peek_match, @_common_schema_dummy);
	        if peek_match then
              set id_from := consumed_to_id + 1;
              call _consume_statement(id_from, id_to, TRUE, consumed_to_id, depth+1, FALSE);
              set catch_statement_id_from := id_from;
              set catch_statement_id_to := consumed_to_id;
            else
              call _throw_script_error(id_from, CONCAT('Expected "catch" on try-catch block'));
            end if;
            
            if should_execute_statement then
              call _consume_try_statement(try_statement_id_from, try_statement_id_to, TRUE, @_common_schema_dummy, depth+1, TRUE, try_statement_error_found);
              if try_statement_error_found then
                call _consume_statement(catch_statement_id_from, catch_statement_id_to, TRUE, @_common_schema_dummy, depth+1, TRUE);
              end if;
            end if;
	      end;
        when first_state = 'alpha' AND first_token in ('break', 'return') then begin
	        call _expect_statement_delimiter(id_from + 1, id_to, consumed_to_id);
	        if should_execute_statement then
	          set @_common_schema_script_break_type := first_token;
	        end if;
	      end;
        when first_state = 'statement delimiter' then begin
            call _throw_script_error(id_from, CONCAT('Empty statement not allowed. Use {} instead'));
	      end;
        when first_state = 'start' then begin
            if expect_single then
              call _throw_script_error(id_from, CONCAT('Unexpected end of script. Expected statement'));
            end if;
	        set consumed_to_id := id_from;
	      end;
	    else begin 
		    call _throw_script_error(id_from, CONCAT('Unsupported token: "', first_token, '"'));
		  end;
      end case;
      if expect_single then
         leave statement_loop;
      end if;
      set id_from := consumed_to_id + 1;
    end while;
    set id_from := consumed_to_id + 1;

    
    
    SELECT GROUP_CONCAT('SET ', mapped_user_defined_variable_name, ' := NULL ' SEPARATOR ';') FROM _qs_variables WHERE declaration_depth = depth INTO reset_query;
    call exec(reset_query);
    UPDATE _qs_variables SET value_snapshot = NULL WHERE declaration_depth = depth;
end ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `_consume_try_statement` */;
DELIMITER ;;
CREATE DEFINER=`percona`@`localhost` PROCEDURE `_consume_try_statement`(
   in   id_from      int unsigned,
   in   id_to      int unsigned,
   in   expect_single tinyint unsigned,
   out  consumed_to_id int unsigned,
   in depth int unsigned,
   in should_execute_statement tinyint unsigned,
   out try_statement_error_found int unsigned
)
    MODIFIES SQL DATA
    DETERMINISTIC
    SQL SECURITY INVOKER
    COMMENT 'Invokes statement in try{} block'
main_body: begin
  
  
  declare continue handler for SQLEXCEPTION set try_statement_error_found = true;
  
  set try_statement_error_found := false;
  call _consume_statement(id_from, id_to, expect_single, consumed_to_id, depth, should_execute_statement);
  
end ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `_create_array` */;
DELIMITER ;;
CREATE DEFINER=`percona`@`localhost` PROCEDURE `_create_array`(
   out  array_id VARCHAR(16) charset ascii
)
    MODIFIES SQL DATA
    DETERMINISTIC
    SQL SECURITY INVOKER
    COMMENT 'Creates an array, returning its ID'
main_body: begin
  select _create_mxarray() into array_id;
end ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `_declare_and_assign_local_variable` */;
DELIMITER ;;
CREATE DEFINER=`percona`@`localhost` PROCEDURE `_declare_and_assign_local_variable`(
   in   id_from      int unsigned,
   in   id_to        int unsigned,
   in   statement_id_from      int unsigned,
   in   assign_id    int unsigned,
   in   statement_id_to      int unsigned,
   in   depth int unsigned,
   in should_execute_statement tinyint unsigned
)
    MODIFIES SQL DATA
    DETERMINISTIC
    SQL SECURITY INVOKER
    COMMENT 'Declares local variables'
main_body: begin
  declare local_variable varchar(65) charset ascii;
  declare user_defined_variable_name varchar(65) charset ascii;
  declare set_expression text charset utf8;
  declare set_statement text charset utf8;
  declare declaration_is_new tinyint unsigned default 0;
    
  call _expect_state(statement_id_from, id_to, 'query_script variable', true, @_common_schema_dummy, local_variable);

  SELECT (COUNT(*) = 0) FROM _qs_variables WHERE declaration_id = id_from INTO declaration_is_new;
  if declaration_is_new then
    set user_defined_variable_name := CONCAT('@__qs_local_var_', session_unique_id());
    call _declare_local_variable(id_from, statement_id_to, id_to, depth, local_variable, user_defined_variable_name, TRUE);
  end if;
  
  if should_execute_statement then
    call _expand_statement_variables(assign_id+1, statement_id_to, set_expression, @_common_schema_dummy, should_execute_statement);
  
    
    select CONCAT('SET ', mapped_user_defined_variable_name, ' := ', set_expression) from _qs_variables where variable_name = local_variable and declaration_depth = depth into set_statement;
    call exec(set_statement);
    
    
  end if;
end ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `_declare_local_variable` */;
DELIMITER ;;
CREATE DEFINER=`percona`@`localhost` PROCEDURE `_declare_local_variable`(
   in   id_variable_declaration      int unsigned,
   in   id_from    int unsigned,
   in   id_to      int unsigned,
   in   depth int unsigned,
   in	local_variable varchar(65) charset ascii,
   in	user_defined_variable_name varchar(65) charset ascii,
   in	throw_when_exists tinyint unsigned
)
    MODIFIES SQL DATA
    DETERMINISTIC
    SQL SECURITY INVOKER
    COMMENT 'Declare a local variable'
main_body: begin
  
  delete from _qs_variables WHERE variable_name = local_variable and ((scope_end_id < id_variable_declaration) or (declaration_id >= id_variable_declaration));

  
  
  
  
  
   
  INSERT IGNORE INTO _qs_variables (server_id, session_id, variable_name, mapped_user_defined_variable_name, declaration_depth, declaration_id, scope_end_id) 
    VALUES (_get_server_id(), CONNECTION_ID(), local_variable, user_defined_variable_name, depth, id_variable_declaration, id_to);
  if ROW_COUNT() = 0 and throw_when_exists then
    call _throw_script_error(id_variable_declaration, CONCAT('Duplicate local variable: ', local_variable));
  end if;
  
  

  
  
  
end ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `_declare_local_variables` */;
DELIMITER ;;
CREATE DEFINER=`percona`@`localhost` PROCEDURE `_declare_local_variables`(
   in   id_from      int unsigned,
   in   id_to      int unsigned,
   in   statement_id_to      int unsigned,
   in   depth int unsigned,
   variables_array_id int unsigned
)
    MODIFIES SQL DATA
    DETERMINISTIC
    SQL SECURITY INVOKER
    COMMENT 'Declares local variables'
main_body: begin
  declare num_variables int unsigned;
  declare variable_index int unsigned default 0;
  declare local_variable varchar(65) charset ascii;
  declare user_defined_variable_name varchar(65) charset ascii;
  declare declaration_is_new tinyint unsigned default 0;
  
  SELECT (COUNT(*) = 0) FROM _qs_variables WHERE declaration_id = id_from INTO declaration_is_new;
  if not declaration_is_new then
    
    
    leave main_body;
  end if;
  
  
  call _get_array_size(variables_array_id, num_variables);
  set variable_index := 1;
  while variable_index <= num_variables do
    call _get_array_element(variables_array_id, variable_index, local_variable);
    set user_defined_variable_name := CONCAT('@__qs_local_var_', session_unique_id());
    
    call _declare_local_variable(id_from, statement_id_to, id_to, depth, local_variable, user_defined_variable_name, TRUE);
    
    set variable_index := variable_index + 1;
  end while;
end ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `_drop_array` */;
DELIMITER ;;
CREATE DEFINER=`percona`@`localhost` PROCEDURE `_drop_array`(
   in  array_id int unsigned
)
    MODIFIES SQL DATA
    DETERMINISTIC
    SQL SECURITY INVOKER
    COMMENT 'Drops an array'
main_body: begin
  select _drop_mxarray(array_id) into @_common_schema_dummy;
end ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `_evaluate_expression` */;
DELIMITER ;;
CREATE DEFINER=`percona`@`localhost` PROCEDURE `_evaluate_expression`(
   in  expression text charset utf8,
   in  expression_statement text charset utf8,
   out expression_result tinyint unsigned
)
    MODIFIES SQL DATA
    DETERMINISTIC
    SQL SECURITY INVOKER
    COMMENT 'Evaluates expression, returns boolean value'
main_body: begin
  declare read_row_count tinyint unsigned default FALSE; 
  
  case
    when expression_statement IN ('insert', 'replace', 'update', 'delete') then begin
	    set read_row_count := TRUE;
	  end;    
    else begin
	    set expression := CONCAT('SELECT ((', expression, ') IS TRUE) INTO @_common_schema_script_expression_result'); 
	  end;
  end case;
  call exec_single(expression);
  set @query_script_rowcount := @common_schema_rowcount;
  if read_row_count then
    set expression_result := ((@query_script_rowcount > 0) IS TRUE);
  else
    set expression_result := @_common_schema_script_expression_result;
  end if;
end ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `_expand_single_variable` */;
DELIMITER ;;
CREATE DEFINER=`percona`@`localhost` PROCEDURE `_expand_single_variable`(
   in   id_from            int unsigned,
   in   id_to              int unsigned,
   inout   variable_token     text charset utf8,
   in   should_execute_statement tinyint unsigned
)
    MODIFIES SQL DATA
    DETERMINISTIC
    SQL SECURITY INVOKER
    COMMENT 'Returns an expanded value'
main_body: begin
  declare expanded_variable_name TEXT CHARSET utf8;
  
  set expanded_variable_name := _extract_expanded_query_script_variable_name(variable_token);
  if expanded_variable_name is null then
    
    leave main_body;
  end if;
  
  
  if not should_execute_statement then
    leave main_body;
  end if;
  
  call _take_local_variables_snapshot(expanded_variable_name);
 
  SELECT 
    MIN(_qs_variables.value_snapshot)
  FROM 
    _sql_tokens 
    LEFT JOIN _qs_variables ON (
      
      (state = 'expanded query_script variable' AND _extract_expanded_query_script_variable_name(token) = _qs_variables.variable_name AND _qs_variables.variable_name  = expanded_variable_name)  
      and (id_from between _qs_variables.declaration_id and _qs_variables.scope_end_id)
    )
  where 
    (id between id_from and id_to) 
  INTO 
    variable_token;
end ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `_expand_statement_variables` */;
DELIMITER ;;
CREATE DEFINER=`percona`@`localhost` PROCEDURE `_expand_statement_variables`(
   in   id_from      int unsigned,
   in   id_to      int unsigned,
   out  expanded_statement text charset utf8,
   out  expanded_variables_found tinyint unsigned,
   in should_execute_statement tinyint unsigned
)
    MODIFIES SQL DATA
    DETERMINISTIC
    SQL SECURITY INVOKER
    COMMENT 'Returns an expanded script statement'
main_body: begin
  declare expanded_variables TEXT CHARSET utf8;
  
  SELECT GROUP_CONCAT(DISTINCT _extract_expanded_query_script_variable_name(token)) from _sql_tokens where (id between id_from and id_to) and (state = 'expanded query_script variable') INTO expanded_variables;
  set expanded_variables_found := (expanded_variables IS NOT NULL); 
  if expanded_variables_found and should_execute_statement then
    call _take_local_variables_snapshot(expanded_variables);
  end if;
  SELECT 
    GROUP_CONCAT(
      case
        when _qs_variables.mapped_user_defined_variable_name IS NOT NULL then
          case
            when state = 'expanded query_script variable' then _qs_variables.value_snapshot  
            else _qs_variables.mapped_user_defined_variable_name 
          end
        else token 
      end 
      ORDER BY id SEPARATOR ''
    ) 
  FROM 
    _sql_tokens 
    LEFT JOIN _qs_variables ON (
      
      (
        (state = 'expanded query_script variable' AND _extract_expanded_query_script_variable_name(token) = _qs_variables.variable_name)  
        or (state = 'query_script variable' AND token = _qs_variables.variable_name) 
      )
      and (id_from between _qs_variables.declaration_id and _qs_variables.scope_end_id)
    )
  where 
    (id between id_from and id_to) 
  INTO 
    expanded_statement;
  set expanded_statement := trim_wspace(expanded_statement);
end ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `_expect_dynamic_states_list` */;
DELIMITER ;;
CREATE DEFINER=`percona`@`localhost` PROCEDURE `_expect_dynamic_states_list`(
   in   id_from      int unsigned,
   in   id_to      int unsigned,
   in   expected_states text charset utf8,
   out  tokens_array_id VARCHAR(16) charset ascii
)
    READS SQL DATA
    DETERMINISTIC
    SQL SECURITY INVOKER
    COMMENT 'Expects a state or raises error'
main_body: begin
  call _match_states(id_from, id_to, expected_states, false, false, 0, 'comma', true, true, @_common_schema_dummy, tokens_array_id, @_common_schema_dummy, @_common_schema_dummy);
end ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `_expect_exact_states_list` */;
DELIMITER ;;
CREATE DEFINER=`percona`@`localhost` PROCEDURE `_expect_exact_states_list`(
   in   id_from      int unsigned,
   in   id_to      int unsigned,
   in   expected_states_list text charset utf8,
   out  tokens_array_id VARCHAR(16) charset ascii
)
    READS SQL DATA
    DETERMINISTIC
    SQL SECURITY INVOKER
    COMMENT 'Expects states or raises error'
main_body: begin
  call _match_states(id_from, id_to, expected_states_list, false, false, 1, null, true, true, @_common_schema_dummy, tokens_array_id, @_common_schema_dummy, @_common_schema_dummy);
end ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `_expect_nothing` */;
DELIMITER ;;
CREATE DEFINER=`percona`@`localhost` PROCEDURE `_expect_nothing`(
   in   id_from      int unsigned,
   in   id_to      int unsigned
)
    READS SQL DATA
    DETERMINISTIC
    SQL SECURITY INVOKER
    COMMENT 'Expect nothing or whitespace only'
main_body: begin
  declare consumed_to_id int unsigned;
  declare token_has_matched tinyint unsigned;

  call _skip_spaces(id_from, id_to);
  if id_from > id_to then
    
    leave main_body;
  end if;
  
  call _consume_if_exists(id_from, id_to, consumed_to_id, NULL, 'statement delimiter|start', token_has_matched, @_common_schema_dummy);
  if not token_has_matched then
    call _throw_script_error(id_from, 'Nothing more expected');
  end if;
end ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `_expect_state` */;
DELIMITER ;;
CREATE DEFINER=`percona`@`localhost` PROCEDURE `_expect_state`(
   in   id_from      int unsigned,
   in   id_to      int unsigned,
   in   expected_states text charset utf8,
   in   allow_trailing_states tinyint unsigned,
   out  consumed_to_id int unsigned,
   out  matched_token text charset utf8
)
    READS SQL DATA
    DETERMINISTIC
    SQL SECURITY INVOKER
    COMMENT 'Expects a state or raises error'
main_body: begin
  declare state_has_matched tinyint unsigned default FALSE;

  call _consume_if_exists(id_from, id_to, consumed_to_id, NULL, expected_states, state_has_matched, matched_token);
  if not state_has_matched then
    call _throw_script_error(id_from, CONCAT('Expected ', REPLACE(expected_states, '|', '/')));
  end if;
  if not allow_trailing_states then
    set id_from := consumed_to_id + 1;
    call _expect_nothing(id_from, id_to);
  end if;
end ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `_expect_statement_delimiter` */;
DELIMITER ;;
CREATE DEFINER=`percona`@`localhost` PROCEDURE `_expect_statement_delimiter`(
   in   id_from      int unsigned,
   in   id_to      int unsigned,
   out  consumed_to_id int unsigned
)
    READS SQL DATA
    DETERMINISTIC
    SQL SECURITY INVOKER
    COMMENT 'Expects ";" or raises error'
main_body: begin
  call _expect_state(id_from, id_to, 'statement delimiter', true, consumed_to_id, @_common_schema_dummy);
end ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `_expect_token` */;
DELIMITER ;;
CREATE DEFINER=`percona`@`localhost` PROCEDURE `_expect_token`(
   in   id_from      int unsigned,
   in   id_to      int unsigned,
   in   expected_tokens text charset utf8,
   in   allow_trailing tinyint unsigned,
   out  consumed_to_id int unsigned,
   out  matched_token text charset utf8
)
    READS SQL DATA
    DETERMINISTIC
    SQL SECURITY INVOKER
    COMMENT 'Expects a token or raises error'
main_body: begin
  declare token_has_matched tinyint unsigned default FALSE;

  call _consume_if_exists(id_from, id_to, consumed_to_id, expected_tokens, NULL, token_has_matched, matched_token);
  if not token_has_matched then
    call _throw_script_error(id_from, CONCAT('Expected "', REPLACE(expected_tokens, '|', '/'), '"'));
  end if;
  if not allow_trailing then
    set id_from := consumed_to_id + 1;
    call _expect_nothing(id_from, id_to);
  end if;
end ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `_foreach` */;
DELIMITER ;;
CREATE DEFINER=`percona`@`localhost` PROCEDURE `_foreach`(
   collection TEXT CHARSET utf8, 
   execute_queries TEXT CHARSET utf8,
   in   id_from      int unsigned,
   in   id_to      int unsigned,
   in   expect_single tinyint unsigned,
   out  consumed_to_id int unsigned,
   in   variables_array_id int unsigned,
   in depth int unsigned,
   in should_execute_statement tinyint unsigned,
   out iteration_count bigint unsigned
)
    MODIFIES SQL DATA
    SQL SECURITY INVOKER
    COMMENT 'Invoke queries per element of given collection'
main_body: begin
  
  declare sql_query_num_columns TINYINT UNSIGNED DEFAULT 9;
  
  declare iteration_number INT UNSIGNED DEFAULT 0;
  
  set @__group_concat_max_len := @@group_concat_max_len;
  set @@group_concat_max_len := 32 * 1024 * 1024;
  
  
  if collection = 'schema' then
    set collection := 'schema ~ /.*/';
  end if;
  if collection RLIKE '^schema[ ]+like[ ]+[^ ]+[ ]*$' then
    begin
	  
	  declare like_expression TEXT CHARSET utf8 DEFAULT unquote(trim_wspace(split_token(collection, ' like ', 2)));
	  set collection := CONCAT('schema ~ /', like_to_rlike(like_expression), '/');
    end;
  end if;
  if collection RLIKE '^schema[ ]*~[ ]*[^ ]+[ ]*$' then
    begin
	  
	  
	  
	  declare re TEXT CHARSET utf8 DEFAULT unquote(trim_wspace(split_token(collection, '~', 2)));
	  set collection := CONCAT(
	    'SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME RLIKE ',
	    '''', re, '''');
      set execute_queries := REPLACE(execute_queries, '${schema}', '${1}');
	end;
  end if;
  if collection RLIKE '^table[ ]+in[ ]+[^ ]+[ ]*$' then
    begin
	  declare db TEXT CHARSET utf8 DEFAULT unquote(trim_wspace(split_token(collection, ' in ', 2)));
	  set collection := CONCAT(
	    'SELECT TABLE_NAME, TABLE_SCHEMA, ENGINE, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA=',
	    '''', db, ''' AND TABLE_TYPE=''BASE TABLE''');
      set execute_queries := REPLACE(execute_queries, '${table}', '${1}');
      set execute_queries := REPLACE(execute_queries, '${schema}', '${2}');
      set execute_queries := REPLACE(execute_queries, '${engine}', '${3}');
      set execute_queries := REPLACE(execute_queries, '${create_options}', '${4}');
	end;
  end if;
  if collection RLIKE '^table[ ]+like[ ]+[^ ]+[ ]*$' then
    begin
	  
	  declare like_expression TEXT CHARSET utf8 DEFAULT unquote(trim_wspace(split_token(collection, ' like ', 2)));
	  set collection := CONCAT('table ~ /', like_to_rlike(like_expression), '/');
    end;
  end if;

  
  
  
  if collection RLIKE '^table[ ]*~[ ]*[^ ]+[ ]*$' then
    begin
	  
	  
      
      
	  
      declare current_db TEXT CHARSET utf8 DEFAULT NULL;
      declare re TEXT CHARSET utf8 DEFAULT unquote(trim_wspace(split_token(collection, '~', 2)));
      declare count_tables SMALLINT UNSIGNED DEFAULT 0;
      declare table_name TEXT CHARSET utf8 DEFAULT NULL;
      declare table_engine TEXT CHARSET utf8 DEFAULT NULL;
      declare table_create_options TEXT CHARSET utf8 DEFAULT NULL;
      declare tables_details TEXT CHARSET utf8 DEFAULT NULL;
      declare table_details TEXT CHARSET utf8 DEFAULT NULL;
      declare done INT DEFAULT 0;
      declare table_counter INT UNSIGNED DEFAULT 1;
      declare db_cursor cursor for SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA;
      declare continue handler for NOT FOUND set done = 1;
	  
	  open db_cursor;
      db_loop: loop
        fetch db_cursor into current_db; 
        if done then
          leave db_loop;
        end if;

        set @_foreach_tables_query:= CONCAT(
          'SELECT GROUP_CONCAT(TABLE_NAME, ''\\0\\b'', ENGINE, ''\\0\\b'', CREATE_OPTIONS SEPARATOR ''\\n\\b'') FROM INFORMATION_SCHEMA.TABLES ',
          'WHERE TABLE_SCHEMA = ''',current_db,''' AND TABLE_NAME RLIKE ''',re,''' AND TABLE_TYPE=''BASE TABLE'' ',
          'INTO @_common_schema_foreach_tables_details');
        call exec(@_foreach_tables_query);
        set tables_details := @_common_schema_foreach_tables_details;

        set count_tables := get_num_tokens(tables_details, '\n\b');
        set iteration_number := 1;
        while iteration_number <= count_tables do
          set table_details := split_token(tables_details, '\n\b', iteration_number);
          
          set table_name := split_token(table_details, '\0\b', 1);
          set table_engine := split_token(table_details, '\0\b', 2);
          set table_create_options := split_token(table_details, '\0\b', 3);
          
          set @_foreach_exec_query := execute_queries;
          set @_foreach_exec_query := REPLACE(@_foreach_exec_query, '${table}', '${1}');
          set @_foreach_exec_query := REPLACE(@_foreach_exec_query, '${1}', table_name);
          set @_foreach_exec_query := REPLACE(@_foreach_exec_query, '${schema}', '${2}');
          set @_foreach_exec_query := REPLACE(@_foreach_exec_query, '${2}', current_db);
          set @_foreach_exec_query := REPLACE(@_foreach_exec_query, '${engine}', '${3}');
          set @_foreach_exec_query := REPLACE(@_foreach_exec_query, '${3}', table_engine);
          set @_foreach_exec_query := REPLACE(@_foreach_exec_query, '${create_options}', '${4}');
          set @_foreach_exec_query := REPLACE(@_foreach_exec_query, '${4}', table_create_options);
          set @_foreach_exec_query := REPLACE(@_foreach_exec_query, '${NR}', table_counter);
          set
            @_query_script_input_col1 := table_name, 
            @_query_script_input_col2 := current_db,
            @_query_script_input_col3 := table_engine,
            @_query_script_input_col4 := table_create_options;

          call _run_foreach_step(@_foreach_exec_query, id_from, id_to, expect_single, consumed_to_id, variables_array_id, depth, should_execute_statement);
          if @_common_schema_script_break_type IS NOT NULL then
            if @_common_schema_script_break_type = 'break' then
              set @_common_schema_script_break_type := NULL;
            end if;
            leave main_body;
          end if;
          set iteration_number := iteration_number + 1;
          set table_counter := table_counter + 1;
        end while;
      end loop;
      close db_cursor;
	end;
  elseif _is_select_query(collection) then
    
	
	
    DROP TEMPORARY TABLE IF EXISTS _tmp_foreach;
    
    set @_foreach_iterate_query := collection;
    call _wrap_select_list_columns(@_foreach_iterate_query, sql_query_num_columns, @common_schema_error);
    set @_foreach_iterate_query := CONCAT('CREATE TEMPORARY TABLE _tmp_foreach ', @_foreach_iterate_query);

    PREPARE st FROM @_foreach_iterate_query;
    EXECUTE st;
    DEALLOCATE PREPARE st;
    
    
    begin	
      declare foreach_col1, foreach_col2, foreach_col3, foreach_col4, foreach_col5, foreach_col6, foreach_col7, foreach_col8, foreach_col9 VARCHAR(4096) CHARSET utf8;
      declare done INT DEFAULT 0;
      declare query_cursor cursor for SELECT * FROM _tmp_foreach;
      declare continue handler for NOT FOUND set done = 1;
      
      set iteration_number := 1;
      open query_cursor;
      read_loop: loop
        fetch query_cursor into foreach_col1, foreach_col2, foreach_col3, foreach_col4, foreach_col5, foreach_col6, foreach_col7, foreach_col8, foreach_col9;
        if done then
          leave read_loop;
        end if;
        set
          @_query_script_input_col1 := foreach_col1, 
          @_query_script_input_col2 := foreach_col2, 
          @_query_script_input_col3 := foreach_col3, 
          @_query_script_input_col4 := foreach_col4, 
          @_query_script_input_col5 := foreach_col5, 
          @_query_script_input_col6 := foreach_col6, 
          @_query_script_input_col7 := foreach_col7, 
          @_query_script_input_col8 := foreach_col8, 
          @_query_script_input_col9 := foreach_col9;

        
        
        set @_foreach_exec_query := execute_queries;
        set @_foreach_exec_query := REPLACE(@_foreach_exec_query, '${1}', IFNULL(foreach_col1, 'NULL'));
        set @_foreach_exec_query := REPLACE(@_foreach_exec_query, '${2}', IFNULL(foreach_col2, 'NULL'));
        set @_foreach_exec_query := REPLACE(@_foreach_exec_query, '${3}', IFNULL(foreach_col3, 'NULL'));
        set @_foreach_exec_query := REPLACE(@_foreach_exec_query, '${4}', IFNULL(foreach_col4, 'NULL'));
        set @_foreach_exec_query := REPLACE(@_foreach_exec_query, '${5}', IFNULL(foreach_col5, 'NULL'));
        set @_foreach_exec_query := REPLACE(@_foreach_exec_query, '${6}', IFNULL(foreach_col6, 'NULL'));
        set @_foreach_exec_query := REPLACE(@_foreach_exec_query, '${7}', IFNULL(foreach_col7, 'NULL'));
        set @_foreach_exec_query := REPLACE(@_foreach_exec_query, '${8}', IFNULL(foreach_col8, 'NULL'));
        set @_foreach_exec_query := REPLACE(@_foreach_exec_query, '${9}', IFNULL(foreach_col9, 'NULL'));
        set @_foreach_exec_query := REPLACE(@_foreach_exec_query, '${NR}', iteration_number);

        call _run_foreach_step(@_foreach_exec_query, id_from, id_to, expect_single, consumed_to_id, variables_array_id, depth, should_execute_statement);
        if @_common_schema_script_break_type IS NOT NULL then
          if @_common_schema_script_break_type = 'break' then
            set @_common_schema_script_break_type := NULL;
          end if;
          leave main_body;
        end if;
        
        set iteration_number := iteration_number + 1; 
      end loop;
      close query_cursor;
    end; 
    
    DROP TEMPORARY TABLE IF EXISTS _tmp_foreach;
  elseif collection RLIKE '^-?[0-9]+:-?[0-9]+,-?[0-9]+:-?[0-9]+$' then
    begin
	  
      
      
      declare first_start_index int signed default NULL;
      declare first_end_index int signed default NULL;
      declare second_start_index int signed default NULL;
      declare second_end_index int signed default NULL;
      declare first_loop_index int signed default NULL;
      declare second_loop_index int signed default NULL;
      
      set @_foreach_first_range := split_token(collection, ',', 1);
      set @_foreach_second_range := split_token(collection, ',', 2);

      set first_start_index := CAST(split_token(@_foreach_first_range, ':', 1) AS SIGNED INTEGER);
      set first_end_index := CAST(split_token(@_foreach_first_range, ':', 2) AS SIGNED INTEGER);
      set second_start_index := CAST(split_token(@_foreach_second_range, ':', 1) AS SIGNED INTEGER);
      set second_end_index := CAST(split_token(@_foreach_second_range, ':', 2) AS SIGNED INTEGER);

      set iteration_number := 1;
      set first_loop_index := first_start_index;
      while first_loop_index <= first_end_index do
        set second_loop_index := second_start_index;
        while second_loop_index <= second_end_index do
          set @_foreach_exec_query := execute_queries;
          set @_foreach_exec_query := REPLACE(@_foreach_exec_query, '${1}', first_loop_index);
          set @_foreach_exec_query := REPLACE(@_foreach_exec_query, '${2}', second_loop_index);
          set @_foreach_exec_query := REPLACE(@_foreach_exec_query, '${NR}', iteration_number);
          set
            @_query_script_input_col1 := first_loop_index, 
            @_query_script_input_col2 := second_loop_index;
        
          call _run_foreach_step(@_foreach_exec_query, id_from, id_to, expect_single, consumed_to_id, variables_array_id, depth, should_execute_statement);
          if @_common_schema_script_break_type IS NOT NULL then
            if @_common_schema_script_break_type = 'break' then
              set @_common_schema_script_break_type := NULL;
            end if;
            leave main_body;
          end if;

          set iteration_number := iteration_number + 1;
          set second_loop_index := second_loop_index + 1;
        end WHILE;
        set first_loop_index := first_loop_index + 1;
      end while;
    end;
  elseif collection RLIKE '^-?[0-9]+:-?[0-9]+$' then
    begin
	  
      
      
      declare _foreach_start_index INT SIGNED DEFAULT CAST(split_token(collection, ':', 1) AS SIGNED INTEGER);
      declare _foreach_end_index INT SIGNED DEFAULT CAST(split_token(collection, ':', 2) AS SIGNED INTEGER);
      declare _foreach_loop_index INT SIGNED DEFAULT NULL;
      
      set iteration_number := 1;
      set _foreach_loop_index := _foreach_start_index;
      while _foreach_loop_index <= _foreach_end_index do
        set @_foreach_exec_query := execute_queries;
        set @_foreach_exec_query := REPLACE(@_foreach_exec_query, '${1}', _foreach_loop_index);
        set @_foreach_exec_query := REPLACE(@_foreach_exec_query, '${NR}', iteration_number);
        set
          @_query_script_input_col1 := _foreach_loop_index;
      
        call _run_foreach_step(@_foreach_exec_query, id_from, id_to, expect_single, consumed_to_id, variables_array_id, depth, should_execute_statement);
        if @_common_schema_script_break_type IS NOT NULL then
          if @_common_schema_script_break_type = 'break' then
            set @_common_schema_script_break_type := NULL;
          end if;
          leave main_body;
        end if;

        set iteration_number := iteration_number + 1;
        set _foreach_loop_index := _foreach_loop_index + 1;
      end while;
    end;
  elseif collection RLIKE '^{.*}$' then
    begin
	  
      
      
      declare _foreach_iterate_tokens TEXT CHARSET utf8 DEFAULT '';
      declare _foreach_num_tokens INT UNSIGNED DEFAULT 0;
      declare _foreach_token TEXT CHARSET utf8;
      declare _foreach_token_delimiter TEXT CHARSET utf8;
      declare _foreach_row_number INT UNSIGNED DEFAULT 1;
      
      set _foreach_iterate_tokens := _retokenized_text(unwrap(collection), ' ,', '"''`', TRUE, 'skip');
      set _foreach_num_tokens := @common_schema_retokenized_count;
      set _foreach_token_delimiter := @common_schema_retokenized_delimiter;
      
      set iteration_number := 1;
      constant_tokens_loop: while iteration_number <= _foreach_num_tokens do
        set _foreach_token := split_token(_foreach_iterate_tokens, _foreach_token_delimiter, iteration_number);
        set _foreach_token := unquote(_foreach_token);
        set iteration_number := iteration_number + 1;

        set @_foreach_exec_query := execute_queries;
        set @_foreach_exec_query := REPLACE(@_foreach_exec_query, '${1}', _foreach_token);
        set @_foreach_exec_query := REPLACE(@_foreach_exec_query, '${NR}', _foreach_row_number);
        set
          @_query_script_input_col1 := _foreach_token;
      
        call _run_foreach_step(@_foreach_exec_query, id_from, id_to, expect_single, consumed_to_id, variables_array_id, depth, should_execute_statement);
        if @_common_schema_script_break_type IS NOT NULL then
          if @_common_schema_script_break_type = 'break' then
            set @_common_schema_script_break_type := NULL;
          end if;
          leave main_body;
        end if;

        set _foreach_row_number := _foreach_row_number + 1;
      end while;
    end;
  else
    call throw(CONCAT('foreach(): unrecognized collection format: \"', collection, '\"'));
  end if;    
  set iteration_count = GREATEST(iteration_number - 1, 0);
  set @@group_concat_max_len := @__group_concat_max_len;
end ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `_get_array_element` */;
DELIMITER ;;
CREATE DEFINER=`percona`@`localhost` PROCEDURE `_get_array_element`(
   in  array_id VARCHAR(16) charset ascii,
   in array_key varchar(127) charset utf8,
   out element text charset utf8
)
    READS SQL DATA
    DETERMINISTIC
    SQL SECURITY INVOKER
    COMMENT 'Creates an array, returning its ID'
main_body: begin
  select _get_mxarray_element(array_id, array_key) into element;
end ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `_get_array_size` */;
DELIMITER ;;
CREATE DEFINER=`percona`@`localhost` PROCEDURE `_get_array_size`(
   in  array_id VARCHAR(16) charset ascii,
   out array_size int unsigned
)
    READS SQL DATA
    DETERMINISTIC
    SQL SECURITY INVOKER
    COMMENT 'Creates an array, returning its ID'
main_body: begin
  select _get_mxarray_size(array_id) into array_size;
end ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `_get_json_token` */;
DELIMITER ;;
CREATE DEFINER=`percona`@`localhost` PROCEDURE `_get_json_token`(
    in      p_text      text charset utf8
,   inout   p_from      int unsigned
,   inout   p_level     int
,   out     p_token     text charset utf8
,   in      allow_script_tokens int
,   inout   p_state     enum(
                            'alpha'
                        ,   'alphanum'
                        ,   'colon'
                        ,   'comma'                        
                        ,   'decimal'
                        ,   'error'
                        ,   'integer'
                        ,   'number'
                        ,   'minus'
                        ,   'object_begin'
                        ,   'object_end'
                        ,   'array_begin'
                        ,   'array_end'
                        ,   'start'
                        ,   'string'
                        ,   'whitespace'
                        )               
)
    NO SQL
    DETERMINISTIC
    SQL SECURITY INVOKER
    COMMENT 'Reads a token according to lexical rules for JSON'
begin    
    declare v_length int unsigned default character_length(p_text);
    declare v_char, v_lookahead, v_quote_char    varchar(1) charset utf8;
    declare v_from int unsigned;
    declare negative_number bool default false;

    if p_from is null then
        set p_from = 1;
    end if;
    if p_level is null then
        set p_level = 0;
    end if;
    if p_state = 'object_end' then
        set p_level = p_level - 1;
    end if;
    if p_state = 'array_end' and allow_script_tokens then
        set p_level = p_level - 1;
    end if;
    set v_from = p_from;
    
    set p_token = ''
    ,   p_state = 'start';
    my_loop: while v_from <= v_length do
        set v_char = substr(p_text, v_from, 1)
        ,   v_lookahead = substr(p_text, v_from+1, 1)
        ;
        if v_char = '-' then
            set negative_number := true, v_from = v_from + 1;
            iterate my_loop;
        end if;
        state_case: begin case p_state
            when 'error' then 
                set p_from = v_length;
                leave state_case;            
            when 'start' then
                case
                    when v_char = '-' then
                        set p_state = 'minus', v_from = v_from + 1;
                    when v_char between '0' and '9' then 
                        set p_state = 'integer';
                    when v_char between 'A' and 'Z' 
                    or   v_char between 'a' and 'z' 
                    or   v_char = '_' then
                        set p_state = 'alpha';                        
                    when v_char = ' ' then 
                        set p_state = 'whitespace'
                        ,   v_from = v_length - character_length(ltrim(substring(p_text, v_from)))
                        ;
                        leave state_case;
                    when v_char in ('\t', '\n', '\r') then 
                        set p_state = 'whitespace';
                    when v_char = '"' then
                        set p_state = 'string', v_quote_char = v_char;
                    when v_char = '.' then
                        if substr(p_text, v_from + 1, 1) between '0' and '9' then
                            set p_state = 'decimal', v_from = v_from + 1;
                        else
                            set p_state = 'error';
                            leave my_loop;
                        end if;
                    when v_char = ',' then
                        set p_state = 'comma', v_from = v_from + 1;
                        leave my_loop;
                    when v_char = ':' then 
                        set p_state = 'colon', v_from = v_from + 1;
                        leave my_loop;
                    when v_char = '{' then 
                        set p_state = 'object_begin', v_from = v_from + 1, p_level = p_level + 1;
                        leave my_loop;
                    when v_char = '}' then
                        set p_state = 'object_end', v_from = v_from + 1;
                        leave my_loop;
                    when v_char = '[' then 
                        set p_state = 'array_begin', v_from = v_from + 1, p_level = p_level + 1;
                        leave my_loop;
                    when v_char = ']' then 
                        set p_state = 'array_end', v_from = v_from + 1;
                        leave my_loop;
                    else 
                        set p_state = 'error';
                end case;
            when 'alpha' then 
                case
                    when v_char between 'A' and 'Z' 
                    or   v_char between 'a' and 'z' 
                    or   v_char = '_' then
                        leave state_case;
                    when v_char between '0' and '9' then 
                        set p_state = 'alphanum';
                    else
                        leave my_loop;
                end case;
            when 'alphanum' then 
                case
                    when v_char between 'A' and 'Z' 
                    or   v_char between 'a' and 'z' 
                    or   v_char = '_'
                    or   v_char between '0' and '9' then 
                        leave state_case;
                    else
                        leave my_loop;
                end case;
            when 'integer' then
                case 
                    when v_char between '0' and '9' then 
                        leave state_case;
                    when v_char = '.' then 
                        set p_state = 'decimal';
                    else
                        leave my_loop;                        
                end case;
            when 'decimal' then
                case 
                    when v_char between '0' and '9' then 
                        leave state_case;
                    else
                        leave my_loop;
                end case;
            when 'whitespace' then
                if v_char not in ('\t', '\n', '\r') then
                    leave my_loop;                        
                end if;
            when 'string' then
                set v_from = locate(v_quote_char, p_text, v_from);
                if v_from then
                    if substr(p_text, v_from + 1, 1) = v_quote_char then
                        set v_from = v_from + 1;
                    elseif substr(p_text, v_from - 1, 1) != '\\' then
                        set v_from = v_from + 1;
                        leave my_loop;
                    end if;
                else
                    set p_state = 'error';
                    leave my_loop;
                end if;
            else
                leave my_loop;            
        end case; end state_case;
        set v_from = v_from + 1;
    end while my_loop;
    set p_token = substr(p_text, p_from, v_from - p_from) collate utf8_general_ci;
    set p_from = v_from;
    if p_state in ('decimal', 'integer') then
      set p_state := 'number';
    end if;
    if p_state = 'alphanum' then
      set p_state := 'alpha';
    end if;
    if negative_number and (p_state != 'number') then
    	set p_token := NULL;
    end if;
end ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `_get_split_query_single_table` */;
DELIMITER ;;
CREATE DEFINER=`percona`@`localhost` PROCEDURE `_get_split_query_single_table`(
   in  id_from      int unsigned,
   in  id_to      int unsigned,
   out query_type_supported tinyint unsigned,
   out tables_found enum ('none', 'single', 'multi'),
   out table_schema varchar(80) charset utf8, 
   out table_name varchar(80) charset utf8
)
    READS SQL DATA
    DETERMINISTIC
    SQL SECURITY INVOKER
main_body: begin
	declare split_query_type varchar(32) charset ascii;
    declare table_definitions_found tinyint unsigned;
    declare table_definitions_id_from int unsigned;
    declare table_definitions_id_to int unsigned;
    
    set table_schema := null;
    set table_name := null;

    
    call _get_split_query_type(id_from, id_to, split_query_type, @_common_schema_dummy, @_common_schema_dummy);
    if split_query_type = 'unsupported' then
      set query_type_supported := false;
      leave main_body;
    end if;
    set query_type_supported := true;

    
    call _get_split_query_table_definitions_clause(id_from, id_to, split_query_type, 
      table_definitions_found, table_definitions_id_from, table_definitions_id_to);
    if not table_definitions_found then
      set tables_found := 'none';
      leave main_body;
    end if;
    
    
    call _get_split_single_table_from_table_definitions(
      table_definitions_id_from,
      table_definitions_id_to,
      tables_found,
      table_schema,
      table_name
      );
end ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `_get_split_query_table_definitions_clause` */;
DELIMITER ;;
CREATE DEFINER=`percona`@`localhost` PROCEDURE `_get_split_query_table_definitions_clause`(
   in   id_from      int unsigned,
   in   id_to      int unsigned,
   in   split_query_type enum (
     'unsupported', 'delete', 'update', 'select', 'insert_select', 'replace_select'),
   out	table_definitions_found tinyint unsigned,
   out	table_definitions_id_from int unsigned,
   out	table_definitions_id_to   int unsigned
)
    READS SQL DATA
    DETERMINISTIC
    SQL SECURITY INVOKER
    COMMENT 'Get type of query supported by split() statement'
main_body: begin
    declare statement_level int unsigned;

    declare starting_id int unsigned;
    declare terminating_id int unsigned;
    declare following_select_id int unsigned;
    
    set table_definitions_found := false;
    set table_definitions_id_from := null;
    set table_definitions_id_to := null;
    
    if split_query_type = 'unsupported' then
      leave main_body;
    end if;
    
    select level from _sql_tokens where id = id_from into statement_level;

    if split_query_type = 'update' then
      select MIN(id) from _sql_tokens where (id between id_from and id_to) and level = statement_level and state = 'alpha' and LOWER(token) = 'set' into terminating_id;
      if terminating_id is not null then
        set table_definitions_found := true;
        set table_definitions_id_from := id_from + 1;
        set table_definitions_id_to := terminating_id - 1;
      end if;
      leave main_body;    
    end if;

    if split_query_type = 'delete' then
      
      select MIN(id) from _sql_tokens where (id between id_from and id_to) and level = statement_level and state = 'alpha' and LOWER(token) = 'from' into starting_id;
      if starting_id is not null then
        set table_definitions_found := true;
        set table_definitions_id_from := starting_id + 1;
        
        select MIN(id) from _sql_tokens where (id between table_definitions_id_from and id_to) and level = statement_level and state = 'alpha' and LOWER(token) = 'using' into starting_id;
        if starting_id is not null then
          set table_definitions_id_from := starting_id + 1;
        end if;
        
        select MIN(id) from _sql_tokens where (id between table_definitions_id_from and id_to) and level = statement_level and state = 'alpha' and LOWER(token) in ('where', 'order', 'limit') into terminating_id;
        if terminating_id is not null then
          set table_definitions_id_to := terminating_id - 1;
        else
          set table_definitions_id_to := id_to;
        end if;
      end if;
      leave main_body;    
    end if;

    if split_query_type in ('insert_select', 'replace_select') then
      
      
      
      set split_query_type := 'select';
    end if;

    if split_query_type = 'select' then
      
      select MIN(id) from _sql_tokens where (id between id_from and id_to) and level = statement_level and state = 'alpha' and LOWER(token) = 'from' into starting_id;
      if starting_id is not null then
        set table_definitions_found := true;
        set table_definitions_id_from := starting_id + 1;
        
        select MIN(id) from _sql_tokens where (id between table_definitions_id_from and id_to) and level = statement_level and state = 'alpha' and LOWER(token) in ('where', 'group', 'having', 'order', 'limit', 'procedure', 'into', 'for', 'lock') into terminating_id;
        if terminating_id is not null then
          set table_definitions_id_to := terminating_id - 1;
        else
          set table_definitions_id_to := id_to;
        end if;
      end if;
      leave main_body;    
    end if;
end ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `_get_split_query_type` */;
DELIMITER ;;
CREATE DEFINER=`percona`@`localhost` PROCEDURE `_get_split_query_type`(
   in   id_from      int unsigned,
   in   id_to      int unsigned,
   out  split_query_type enum (
     'unsupported', 'delete', 'update', 'select', 'insert_select', 'replace_select'),
   out  split_query_id_from int unsigned,
   out  split_query_following_select_id int unsigned
)
    READS SQL DATA
    DETERMINISTIC
    SQL SECURITY INVOKER
    COMMENT 'Get type of query supported by split() statement'
main_body: begin
    declare statement_level int unsigned;
    declare statement_type tinytext charset utf8 default null;
    
    set split_query_type := 'unsupported';
    set split_query_following_select_id := null;
    
    call _skip_spaces(id_from, id_to);
    SELECT id, level, LOWER(token) FROM _sql_tokens WHERE id = id_from AND state = 'alpha' INTO split_query_id_from, statement_level, statement_type;
    
	if statement_type in ('insert', 'replace') then
      SELECT MIN(id) FROM _sql_tokens WHERE id > id_from AND id <= id_to AND level = statement_level AND state = 'alpha' AND LOWER(token) = 'select' INTO split_query_following_select_id;
    end if;
    
    if statement_type = 'delete' then
      set split_query_type := 'delete';
    elseif statement_type = 'update' then
      set split_query_type := 'update';
    elseif statement_type = 'select' then
      set split_query_type := 'select';
    elseif statement_type = 'insert' and split_query_following_select_id is not null then
      set split_query_type := 'insert_select';
    elseif statement_type = 'replace' and split_query_following_select_id is not null then
      set split_query_type := 'replace_select';
    end if;
end ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `_get_split_single_table_from_table_definitions` */;
DELIMITER ;;
CREATE DEFINER=`percona`@`localhost` PROCEDURE `_get_split_single_table_from_table_definitions`(
   in  id_from      int unsigned,
   in  id_to      int unsigned,
   out tables_found enum ('none', 'single', 'multi'),
   out table_schema varchar(80) charset utf8, 
   out table_name varchar(80) charset utf8
)
    READS SQL DATA
    DETERMINISTIC
    SQL SECURITY INVOKER
    COMMENT 'Get table_schema, table_name from table definitions clause'
main_body: begin
    declare statement_level int unsigned;
	declare multi_table_indicator_id int unsigned;
    declare tokens_array_id int unsigned;
    declare peek_match_to int unsigned;
     
    set table_schema := null;
    set table_name := null;
    set tables_found := 'none';
    
    select level from _sql_tokens where id = id_from into statement_level;
    
    
    
    select MIN(id) from _sql_tokens where 
      (id between id_from and id_to) 
      and (
        ((state = 'comma') and (level = statement_level))
        or 
        ((state = 'alpha') and (LOWER(token) in ('join', 'straight_join')) and (level = statement_level))
        or 
        ((state = 'left parenthesis') and (level = statement_level + 1))
      )
      into multi_table_indicator_id;
      
    if multi_table_indicator_id is not null then
       set tables_found := 'multi';
       leave main_body;
    end if;
    
    
    
    
    
    
    

    call _skip_tokens_and_spaces(id_from, id_to, 'ignore,low_priority');
    call _peek_states_list(id_from, id_to, 'alpha|alphanum|quoted identifier|expanded query_script variable,dot,alpha|alphanum|quoted identifier|expanded query_script variable', false, false, true, tokens_array_id, peek_match_to);
    if peek_match_to > 0 then
      call _get_array_element(tokens_array_id, '1', table_schema);		
      call _get_array_element(tokens_array_id, '3', table_name);
      set table_schema := unquote(table_schema);
      set table_name := unquote(table_name);
      set tables_found := 'single';
    end if;
    call _drop_array(tokens_array_id);
end ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `_get_sql_dependencies_internal` */;
DELIMITER ;;
CREATE DEFINER=`percona`@`localhost` PROCEDURE `_get_sql_dependencies_internal`(
    IN p_sql               TEXT charset utf8
,   IN p_default_schema    VARCHAR(64) charset utf8
,   IN return_result_set   TINYINT UNSIGNED
,   OUT result_success      TINYINT UNSIGNED
)
    DETERMINISTIC
my_main: begin
    declare v_from, v_old_from int unsigned;
    declare v_token text charset utf8;
    declare v_level int unsigned default 0;
    declare v_state varchar(32) charset utf8;
    declare v_scan_state varchar(32) charset utf8 default 'start';
    declare v_schema_name, v_object_name, v_object_type, v_definer, v_action varchar(64) charset utf8 default null;
    declare v_error_message text charset utf8 default '';

    set @old_autocommit = @@autocommit
    ,   autocommit = off
    ;
  my_error: begin
    
    declare exit handler for 1339
        set v_error_message = concat('case not defined for state: "', v_scan_state, '" ("', v_state, '")');
    declare exit handler for 1265
        set v_error_message = concat('not valid for enum ', v_token);

    set result_success := 1;
        
    drop temporary table if exists _sql_dependencies;
    create temporary table if not exists _sql_dependencies(
        id              int unsigned auto_increment primary key
    ,   start           int unsigned
    ,   action          enum('alter', 'call', 'create', 'delete', 'drop', 'insert', 'replace', 'select', 'truncate', 'update')
    ,   object_type     enum('event', 'function', 'index', 'procedure', 'table', 'trigger', 'view')
    ,   schema_name     varchar(64)
    ,   object_name     varchar(64)
    );
    
    my_loop: repeat 
        set v_old_from = v_from;
        call _get_sql_token(p_sql, v_from, v_level, v_token, 'sql', v_state);
        set v_token = v_token collate utf8_general_ci;
        if v_state in ('whitespace', 'single line comment', 'multi line comment') then
            iterate my_loop;
        elseif v_state = 'statement delimiter' then
            if v_scan_state = 'expect dot' then
                insert 
                into _sql_dependencies (start, schema_name, object_name, object_type, action) 
                values (v_from, v_schema_name, v_object_name, v_object_type, v_action);
            end if;
            set v_scan_state = 'start'
            ,   v_action = null;
        end if;
        if @debug_get_sql_dependencies then
            select v_scan_state, v_from, v_token, v_state;
        end if;

        case v_scan_state
            when 'start' then
                set v_schema_name = p_default_schema, v_object_name = null, v_object_type = null, v_definer = null;
                if v_state = 'alpha' then
                    if v_token in ('alter', 'call', 'create', 'delete', 'drop', 'insert', 'replace', 'select', 'truncate') then
                        set v_action = lower(v_token) collate utf8_general_ci
                        ,   v_scan_state = v_action
                        ;
                    elseif v_token in ('update') then
                        set v_action = lower(v_token) collate utf8_general_ci
                        ,   v_scan_state = 'expect table'
                        ;
                    elseif v_token in ('join', 'from') then
                        set v_scan_state = 'expect table';
                    end if;
                end if;
            when 'select' then
                set v_scan_state = 'expect from';
                set v_action = 'select';
            when 'insert' then
                set v_scan_state = 'expect table';
            when 'update' then
                set v_scan_state = 'expect table';
            when 'delete' then
                set v_scan_state = 'expect table';
            when 'expect from' then
                if v_state = 'alpha' and v_token = 'from' then
                    set v_scan_state = 'expect table';
                end if;
            when 'call' then
                set v_object_type = 'procedure'
                ,   v_object_name = v_token
                ,   v_scan_state = 'expect dot'
                ;
            when 'alter' then
                if v_state = 'alpha' then
                    if v_token in ('database', 'event', 'function', 'procedure', 'schema', 'server', 'table', 'tablespace', 'view') then
                        set v_object_type = v_token
                        ,   v_scan_state = 'expect identifier1';
                    elseif v_token = 'logfile' then
                        set v_scan_state = 'expect logfile group';
                    elseif v_token in ('online', 'offline', 'ignore') then
                        set v_scan_state = 'expect object type';
                    elseif v_token = 'definer' then
                        set v_scan_state = 'definer';
                    else 
                        set v_error_message = concat('"', v_token, '" is not a valid object type for alter ', v_scan_state);
                        leave my_error;
                    end if;
                else
                    set v_error_message = concat('expected alpha ', v_scan_state);
                    leave my_error;
                end if;
            when 'create' then
                if v_state = 'alpha' then
					if v_token in ('database', 'event', 'function', 'procedure', 'schema', 'server', 'table', 'tablespace', 'view') then
                        set v_object_type = v_token
                        ,   v_scan_state = 'expect identifier1';
                    end if;
                else
                    set v_error_message = concat('expected alpha ', v_scan_state);
                    leave my_error;
                end if;
            when 'drop' then
                if v_state = 'alpha' then
                    if v_token in ('database', 'event', 'function', 'procedure', 'schema', 'server', 'table', 'tablespace', 'view') then
                        set v_object_type = v_token
                        ,   v_scan_state = 'expect identifier1';
                    end if;
                else
                    set v_error_message = concat('expected alpha ', v_scan_state);
                    leave my_error;
                end if;
            when 'expect logfile group' then
                if v_state = 'alpha' and v_token = 'group' then
                    set v_object_type = 'logfile group'
                    ,   v_scan_state = 'expect identifier2';
                else 
                    set v_error_message = concat('expected group keyword');
                    leave my_error;
                end if;
            when 'expect definer user' then
                if v_state in ('alpha') and v_token = 'CURRENT_USER' then 
                    set v_scan_state = 'expect object type';
                elseif v_state = 'string'  then
                    set v_scan_state = 'expect definer host';
                else
                    set v_error_message = concat('expected alpha or alphanum', v_scan_state);
                    leave my_error;
                end if;
            when 'expect definer host' then
                if v_state = 'user-defined variable' then
                    set v_scan_state = 'expect object type';
                else
                    set v_error_message = concat('expected hostname, not ', v_state);
                    leave my_error;
                end if;
            when 'definer' then
                if v_state = 'equals' then
                    set v_scan_state = 'expect definer user';
                else
                    set v_error_message = concat('expected equals in state', v_scan_state);
                    leave my_error;
                end if;            
            when 'expect create or replace' then
                if v_state = 'alpha' and v_token = 'replace' then
                    set v_scan_state = 'expect object type';
                else
                    set v_error_message = concat('expected replace in state', v_scan_state);
                    leave my_error;
                end if;
            when 'expect object type' then
                if  v_state = 'alpha' then
                    if v_token in ('event', 'function', 'index', 'procedure', 'schema', 'table', 'trigger', 'view') then
                        set v_object_type = v_token
                        ,   v_scan_state = 'expect identifier1'
                        ;                    
                    elseif v_token = 'definer' then
                        set v_scan_state = 'definer';
                    elseif v_token = 'or' then
                        set v_scan_state = 'expect create or replace';
                    elseif v_token = 'temporary' then
                        set v_scan_state = 'expect object type';
                    else
                        set v_error_message = concat('invalid object type ', v_token);
                        leave my_error;
                    end if;
                else
                    set v_error_message = concat('expected alpha in state ', v_scan_state);
                    leave my_error;
                end if;
            when 'expect identifier1' then
                if v_state = 'quoted identifier' then
                    set v_object_name = substr(v_token, 2, character_length(v_token) - 2);                    
                elseif v_state in ('alpha', 'alphanum') then
                    if v_token not in ('if', 'not', 'exists') then
                        set v_object_name = v_token;
                        set v_scan_state = 'expect dot';
                    end if;
                else
                    set v_error_message = concat('expected identifier ', v_scan_state);
                    leave my_error;
                end if;
            when 'expect identifier2' then
                if v_state in ('quoted identifier', 'alpha', 'alphanum') then
                    set v_schema_name = v_object_name;
                    if v_state = 'quoted identifier' then
                        set v_object_name = substr(v_token, 2, character_length(v_token) - 2)
                        ;
                    elseif v_state in ('alpha', 'alphanum') then
                        set v_object_name = v_token;
                    end if;
                    
                    insert 
                    into _sql_dependencies (start, schema_name, object_name, object_type, action) 
                    values (v_from, v_schema_name, v_object_name, v_object_type, v_action);

                    if v_object_type = 'table' or v_object_type = 'view' then 
                        set v_scan_state = 'expect join';
                    else 
                        set v_scan_state = 'start';
                    end if;
                else
                    set v_error_message = concat('expected identifier ', v_scan_state);
                    leave my_error;
                end if;
            when 'expect dot' then
                if v_state = 'dot' then
                    set v_scan_state = 'expect identifier2';
                else
                    insert 
                    into _sql_dependencies (start, schema_name, object_name, object_type, action) 
                    values (v_from,v_schema_name, v_object_name, v_object_type, v_action);
                    
                    if v_object_type = 'table' or v_object_type = 'view' then 
                        if  (v_state = 'alpha' and v_token in ('into', 'where', 'group', 'having', 'order', 'limit'))
                        or  v_action != 'select'
                        then
                            set v_scan_state = 'start';
                        elseif v_state = 'comma' then
                            set v_scan_state = 'expect table';
                        else
                            set v_scan_state = 'expect join';
                        end if;
                    else 
                        set v_scan_state = 'start';
                    end if;
                end if;
            when 'expect join' then
                if v_state = 'alpha' then
                    if v_token = 'join' then
                        set v_scan_state = 'expect table';
                    elseif v_token = 'on' then
                        set v_scan_state = 'expect join';
                    elseif v_token = 'select' then
                        set v_scan_state = 'select';
                    elseif v_token in ('into', 'where', 'group', 'having', 'order', 'limit') then
                        set v_scan_state = 'start';
                    end if;
                elseif v_state = 'comma' then 
                    set v_scan_state = 'expect table';
                end if;
            when 'expect table' then
                set v_object_type = 'table';
                case 
                    when v_state = 'quoted identifier' then
                        set v_object_name = substr(v_token, 2, character_length(v_token) - 2)
                        ,   v_scan_state = 'expect dot'
                        ;
                    when v_state = 'alpha' and v_token in ('low_priority', 'delayed', 'high_priority', 'ignore', 'into') 
                    or   v_token = '(' 
                    then
                        do null;
                    when v_state = 'alpha' and v_token = 'select' then 
                        set v_scan_state = 'select';
                    when v_state in ('alpha', 'alphanum') then
                        set v_object_name = v_token
                        ,   v_scan_state = 'expect dot'
                        ;
                    else
                        set v_error_message = concat('unexpected state ', v_scan_state, ' (', v_state,')');
                        do null;
                end case;
            when 'expect identifier' then
                if v_state in ('quoted identifier', 'alpha', 'alphanum') then
                    set v_schema_name = v_object_name;
                    if v_state = 'quoted identifier' then
                        set v_object_name = substr(v_token, 2, character_length(v_token) - 2);
                    else
                        set v_object_name = v_token;
                    end if;
                    insert 
                    into _sql_dependencies (start, schema_name, object_name, object_type, action) 
                    values (v_from, v_schema_name, v_object_name, v_object_type, v_action) 
                    ;
                end if;
                set v_scan_state = 'start';
            else 
                set v_error_message = concat('unexpected state ', v_scan_state);
                leave my_error;
        end case;
    until 
        v_old_from = v_from
    end repeat;

    commit;
    set autocommit = @old_autocommit;
    
    if return_result_set then
      select distinct schema_name, object_name, object_type, action
      from _sql_dependencies
      order by schema_name, object_name, object_type, action
      ;
    end if;
    leave my_main;
  end;
  set result_success := 0;
  if return_result_set then
    select concat('Error: ', v_error_message) error;
  end if;
end ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `_get_sql_token` */;
DELIMITER ;;
CREATE DEFINER=`percona`@`localhost` PROCEDURE `_get_sql_token`(
    in      p_text      text charset utf8
,   inout   p_from      int unsigned
,   inout   p_level     int
,   out     p_token     text charset utf8
,   in      language_mode enum ('sql', 'script', 'routine')

,   inout   p_state     enum(
                            'alpha'
                        ,   'alphanum'
                        ,   'and'
                        ,   'assign'
                        ,   'bitwise and'
                        ,   'bitwise or'
                        ,   'bitwise not'
                        ,   'bitwise xor'
                        ,   'colon'
                        ,   'comma'
                        ,   'conditional comment'
                        ,   'decimal'
                        ,   'delimiter'
                        ,   'divide'
                        ,   'dot'
                        ,   'equals'
                        ,   'error'
                        ,   'greater than'
                        ,   'greater than or equals'
                        ,   'integer'
                        ,   'label'
                        ,   'left braces'
                        ,   'left parenthesis'
                        ,   'left shift'
                        ,   'less than'
                        ,   'less than or equals'
                        ,   'minus'
                        ,   'modulo'
                        ,   'multi line comment'
                        ,   'multiply'
                        ,   'not equals'
                        ,   'null safe equals'
                        ,   'or'
                        ,   'plus'
                        ,   'quoted identifier'
                        ,   'right braces'
                        ,   'right parenthesis'
                        ,   'right shift'
                        ,   'single line comment'
                        ,   'start'
                        ,   'statement delimiter'
                        ,   'string'
                        ,   'system variable'
                        ,   'user-defined variable'
                        ,   'query_script variable'
                        ,   'expanded query_script variable'
                        ,   'whitespace'
                        ,   'not'
                        )
)
    NO SQL
    DETERMINISTIC
    SQL SECURITY INVOKER
    COMMENT 'Reads a token according to lexical rules for SQL'
begin
    declare v_length int unsigned default character_length(p_text);
    declare v_no_ansi_quotes        bool default find_in_set('ANSI_QUOTES', @@sql_mode) = FALSE;
    declare v_char, v_lookahead, v_quote_char    char(1) charset utf8;
    declare v_from int unsigned;
    declare allow_script_tokens tinyint unsigned;

    set allow_script_tokens := (language_mode = 'script');

    if p_from is null then
        set p_from = 1;
    end if;
    if p_level is null then
        set p_level = 0;
    end if;
    if p_state = 'right parenthesis' then
        set p_level = p_level - 1;
    end if;
    if p_state = 'right braces' and allow_script_tokens then
        set p_level = p_level - 1;
    end if;
    set v_from = p_from;

    set p_token = ''
    ,   p_state = 'start';

    my_loop: while v_from <= v_length do
        set v_char = substr(p_text, v_from, 1)
        ,   v_lookahead = substr(p_text, v_from+1, 1)
        ;

        state_case: begin case p_state
            when 'error' then
                set p_from = v_length;
                leave state_case;
            when 'start' then
                case
                    when v_char between '0' and '9' then
                        set p_state = 'integer';
                    when v_char between 'A' and 'Z'
                    or   v_char between 'a' and 'z'
                    or   v_char = '_' then
                        set p_state = 'alpha';
                    when v_char = ' ' then
                        set p_state = 'whitespace'
                        ,   v_from = v_length - character_length(ltrim(substring(p_text, v_from)))
                        ;
                        leave state_case;
                    when v_char in ('\t', '\n', '\r') then
                        set p_state = 'whitespace';
                    when v_char = '''' or v_no_ansi_quotes and v_char = '"' then
                        set p_state = 'string', v_quote_char = v_char;
                    when v_char = '`' or v_no_ansi_quotes = FALSE and v_char = '"' then
                        set p_state = 'quoted identifier', v_quote_char = v_char;
                    when v_char = '@' then
                        if v_lookahead = '@' then
                            set p_state = 'system variable', v_from = v_from + 1;
                        else
                            set p_state = 'user-defined variable';
                            if v_lookahead = '''' then
                                set v_from = v_from + 1;
                                leave my_loop;
                            end if;
                        end if;
                    when v_char = '$' and allow_script_tokens then
                        set p_state = 'query_script variable';
                    when v_char = '.' then
                        if substr(p_text, v_from + 1, 1) between '0' and '9' then
                            set p_state = 'decimal', v_from = v_from + 1;
                        else
                            set p_state = 'dot', v_from = v_from + 1;
                            leave my_loop;
                        end if;
                    when v_char = ';' then
                        set p_state = 'statement delimiter', v_from = v_from + 1;
                        leave my_loop;
                    when v_char = ',' then
                        set p_state = 'comma', v_from = v_from + 1;
                        leave my_loop;
                    when v_char = '=' then
                        set p_state = 'equals', v_from = v_from + 1;
                        leave my_loop;
                    when v_char = '*' then
                        set p_state = 'multiply', v_from = v_from + 1;
                        leave my_loop;
                    when v_char = '%' then
                        set p_state = 'modulo', v_from = v_from + 1;
                        leave my_loop;
                    when v_char = '/' then
                        if v_lookahead = '*' then
                            set v_from = locate('*/', p_text, p_from + 2);
                            if v_from then
                                set p_state = if (substr(p_text, p_from + 2, 1) = '!', 'conditional comment', 'multi line comment')
                                ,   v_from = v_from + 2
                                ;
                                leave my_loop;
                            else
                                set p_state = 'error';
                            end if;
                        else
                            set p_state = 'divide', v_from = v_from + 1;
                            leave my_loop;
                        end if;
                    when v_char = '-' then
                        case
                            when v_lookahead = '-' and substr(p_text, v_from + 2, 1) = ' ' then
                                set p_state = 'single line comment'
                                ,   v_from = locate('\n', p_text, p_from)
                                ;
                                if not v_from then
                                    set v_from = v_length;
                                end if;
                                set v_from = v_from + 1;
                                leave my_loop;
                            else
                                set p_state = 'minus', v_from = v_from + 1;
                                leave my_loop;
                        end case;
                    when v_char = '#' then
                        set p_state = 'single line comment'
                        ,   v_from = locate('\n', p_text, p_from)
                        ;
                        if not v_from then
                            set v_from = v_length;
                        end if;
                        set v_from = v_from + 1;
                        leave my_loop;
                    when v_char = '+' then
                        set p_state = 'plus', v_from = v_from + 1;
                        leave my_loop;
                    when v_char = '<' then
                        set p_state = 'less than';
                    when v_char = '>' then
                        set p_state = 'greater than';
                    when v_char = ':' then
                        if v_lookahead = '=' then
                            set p_state = 'assign', v_from = v_from + 2;
                            leave my_loop;
                        elseif v_lookahead = '$' and allow_script_tokens then
                            set p_state = 'expanded query_script variable';
                        else
                            set p_state = 'colon', v_from = v_from + 1;
                            leave my_loop;
                        end if;
                    when v_char = '{' and allow_script_tokens then
                        set p_state = 'left braces', v_from = v_from + 1, p_level = p_level + 1;
                        leave my_loop;
                    when v_char = '}' and allow_script_tokens then
                        set p_state = 'right braces', v_from = v_from + 1;
                        leave my_loop;
                    when v_char = '(' then
                        set p_state = 'left parenthesis', v_from = v_from + 1, p_level = p_level + 1;
                        leave my_loop;
                    when v_char = ')' then
                        set p_state = 'right parenthesis', v_from = v_from + 1;
                        leave my_loop;
                    when v_char = '^' then
                        set p_state = 'bitwise xor', v_from = v_from + 1;
                        leave my_loop;
                    when v_char = '~' then
                        set p_state = 'bitwise not', v_from = v_from + 1;
                        leave my_loop;
                    when v_char = '!' then
                        if v_lookahead = '=' then
                            set p_state = 'not equals', v_from = v_from + 2;
                        else
                            set p_state = 'not', v_from = v_from + 1;
                        end if;
                        leave my_loop;
                    when v_char = '|' then
                        if v_lookahead = '|' then
                            set p_state = 'or', v_from = v_from + 2;
                        else
                            set p_state = 'bitwise or', v_from = v_from + 1;
                        end if;
                        leave my_loop;
                    when v_char = '&' then
                        if v_lookahead = '&' then
                            set p_state = 'and', v_from = v_from + 2;
                        else
                            set p_state = 'bitwise and', v_from = v_from + 1;
                        end if;
                        leave my_loop;
                    else
                        set p_state = 'error';
                end case;
            when 'less than' then
                case v_char
                    when '=' then
                        set p_state = 'less than or equals';
                        leave state_case;
                    when '>' then
                        set p_state = 'not equals';
                    when '<' then
                        set p_state = 'left shift';
                    else
                        do null;
                end case;
                leave my_loop;
            when 'less than or equals' then
                if v_char = '>' then
                    set p_state = 'null safe equals'
                    ,   v_from = v_from + 1
                    ;
                end if;
                leave my_loop;
            when 'greater than' then
                case v_char
                    when '=' then
                        set p_state = 'greater than or equals';
                    when '>' then
                        set p_state = 'right shift';
                    else
                        set p_state = 'error';
                end case;
                leave my_loop;
            when 'multi line comment' then
                if v_char = '*' and v_lookahead = '/' then
                    set v_from = v_from + 2;
                    leave my_loop;
                end if;
            when 'alpha' then
                case
                    when v_char between 'A' and 'Z'
                    or   v_char between 'a' and 'z'
                    or   v_char = '_' then
                        leave state_case;
                    when v_char between '0' and '9'
                    or   v_char = '$' then
                        set p_state = 'alphanum';
                    else



                        leave my_loop;
                end case;
            when 'alphanum' then
                case
                    when v_char between 'A' and 'Z'
                    or   v_char between 'a' and 'z'
                    or   v_char = '_'
                    or   v_char between '0' and '9' then
                        leave state_case;
                    else



                        leave my_loop;
                end case;
            when 'integer' then
                case
                    when v_char between '0' and '9' then
                        leave state_case;
                    when v_char = '.' then
                        set p_state = 'decimal';
                    else
                        leave my_loop;
                end case;
            when 'decimal' then
                case
                    when v_char between '0' and '9' then
                        leave state_case;
                    else
                        leave my_loop;
                end case;
            when 'whitespace' then
                if v_char not in ('\t', '\n', '\r') then
                    leave my_loop;
                end if;
            when 'string' then
                
                set v_from = locate(v_quote_char, p_text, v_from);
                if v_from then  
                    if substr(p_text, v_from - 1, 1) = '\\' then
                        
                        
                        backslahses: begin
                            declare v_backslash int unsigned default v_from - 2;
                            while substr(p_text, v_backslash, 1) = '\\' do
                                
                                
                                if substr(p_text, v_backslash - 1, 1) = '\\' then
                                    
                                    set v_backslash = v_backslash - 2;
                                else
                                    
                                    
                                    leave backslahses;
                                end if;
                            end while;
                            
                            
                            
                            set v_from = v_from + 1;
                            if v_from > v_length then
                                set p_state = 'error';
                                leave my_loop;
                            else
                                iterate my_loop;
                            end if;
                        end backslahses;
                    end if;

                    
                    
                    if substr(p_text, v_from + 1, 1) = v_quote_char then
                        
                        
                        
                        set v_from = v_from + 2;
                        if v_from > v_length then
                            set p_state = 'error';
                            leave my_loop;
                        else
                            iterate my_loop;
                        end if;
                    else
                        
                        
                        set v_from = v_from + 1;
                        leave my_loop;
                    end if;
                else  
                    set p_state = 'error', v_from = v_length;
                    leave my_loop;
                end if;
            when 'quoted identifier' then
                if v_char != v_quote_char then
                    leave state_case;
                else
                    set v_from = v_from + 1;
                    leave my_loop;
                end if;
            when 'user-defined variable' then
                if v_char in (';', ',', ' ', '\t', '\n', '\r', '!', '~', '^', '%', '>', '<', ':', '=', '+', '-', '&', '*', '|', '(', ')') then
                    leave my_loop;
                elseif allow_script_tokens and v_char in ('{', '}') then
                    leave my_loop;
                end if;
            when 'query_script variable' then
                if v_char in (';', ',', ' ', '\t', '\n', '\r', '!', '~', '^', '%', '>', '<', ':', '=', '+', '-', '&', '*', '|', '(', ')') then
                    leave my_loop;
                elseif allow_script_tokens and v_char in ('{', '}', '.') then
                    leave my_loop;
                end if;
            when 'expanded query_script variable' then
                if v_char in (';', ',', ' ', '\t', '\n', '\r', '!', '~', '^', '%', '>', '<', ':', '=', '+', '-', '&', '*', '|', '(', ')') then
                    leave my_loop;
                elseif allow_script_tokens and v_char in ('{', '}', '.') then
                    leave my_loop;
                end if;
            when 'system variable' then
                if v_char in (';', ',', ' ', '\t', '\n', '\r', '!', '~', '^', '%', '>', '<', ':', '=', '+', '-', '&', '*', '|', '(', ')') then
                    leave my_loop;
                elseif allow_script_tokens and v_char in ('{', '}') then
                    leave my_loop;
                end if;
            else
                leave my_loop;
        end case; end state_case;
        set v_from = v_from + 1;
    end while my_loop;
    set p_token = substr(p_text, p_from, v_from - p_from) collate utf8_general_ci;
    set p_from = v_from;
end ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `_get_sql_tokens` */;
DELIMITER ;;
CREATE DEFINER=`percona`@`localhost` PROCEDURE `_get_sql_tokens`(
    in p_text text
)
    MODIFIES SQL DATA
    DETERMINISTIC
    SQL SECURITY INVOKER
    COMMENT 'Reads tokens according to lexical rules for SQL'
begin
    declare v_from, v_old_from int unsigned;
    declare v_token text;
    declare v_level int;
    declare v_state varchar(32);
    declare _sql_tokens_id int unsigned default 0;
    
    delete from _sql_tokens;
    start transaction;
    repeat 
        set v_old_from = v_from;
        call _get_sql_token(p_text, v_from, v_level, v_token, 'script', v_state);
        set _sql_tokens_id := _sql_tokens_id + 1;
        insert into _sql_tokens(server_id, session_id, id, start, level, token, state) 
        values (_get_server_id(), CONNECTION_ID(), _sql_tokens_id, v_from, v_level, v_token, v_state);
    until 
        v_old_from = v_from
    end repeat;
    
    if @common_schema_debug then
      select * 
      from _sql_tokens
      order by id;
    end if;
    commit;
end ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `_inject_split_where_token` */;
DELIMITER ;;
CREATE DEFINER=`percona`@`localhost` PROCEDURE `_inject_split_where_token`(
   in   id_from      int unsigned,
   in   id_to      int unsigned,
   in   split_injected_text tinytext charset utf8,
   in   should_execute_statement tinyint unsigned,
   out  split_injected_action_statement text charset utf8
)
    READS SQL DATA
    DETERMINISTIC
    SQL SECURITY INVOKER
    COMMENT 'Injects a magical token into the WHERE statement'
main_body: begin
    declare statement_level int unsigned;
    declare id_where_clause int unsigned default NULL; 
    declare id_where_clause_end int unsigned default NULL; 
    declare id_end_split_table_declaration int unsigned default NULL;
    declare query_part_prefix text charset utf8 default '';
    declare query_part_where_clause text charset utf8 default '';
    declare query_part_suffix text charset utf8 default '';
 	declare split_query_type varchar(32) charset ascii;
 	declare split_query_id_from int unsigned;
    declare split_query_following_select_id int unsigned;
   
    call _skip_spaces(id_from, id_to);
    SELECT level FROM _sql_tokens WHERE id = id_from INTO statement_level;
    
    
    call _get_split_query_type(id_from, id_to, split_query_type, split_query_id_from, split_query_following_select_id);
    
    if split_query_type = 'unsupported' then
      call _throw_script_error(id_from, 'split(): unsupported query type');
    end if;

    SELECT 
      MIN(id) FROM _sql_tokens 
      WHERE 
        level = statement_level AND state = 'alpha' and LOWER(token) = 'where' 
        AND id between id_from and id_to
      INTO id_where_clause;
    if id_where_clause is NULL then
      
      

      
      
      
      SELECT 
        MIN(id) FROM _sql_tokens 
        WHERE 
          level = statement_level 
          AND state = 'alpha' and LOWER(token) IN ('group', 'having', 'order', 'limit', 'into') 
          AND id between GREATEST(id_from, split_query_id_from, IFNULL(split_query_following_select_id, split_query_id_from)) and id_to
        INTO id_where_clause_end;
      if id_where_clause_end is NULL then
        
        call _expand_statement_variables(id_from, id_to, query_part_prefix, @common_schema_dummy, should_execute_statement);
      else
        
        
        call _expand_statement_variables(id_from, id_where_clause_end - 1, query_part_prefix, @common_schema_dummy, should_execute_statement);
        call _expand_statement_variables(id_where_clause_end, id_to, query_part_suffix, @common_schema_dummy, should_execute_statement);
      end if;
      
      set split_injected_action_statement := CONCAT(
          query_part_prefix, ' WHERE ', split_injected_text, ' ', query_part_suffix
        );
    else
      
      call _expand_statement_variables(id_from, id_where_clause, query_part_prefix, @common_schema_dummy, should_execute_statement);
      
      SELECT 
        MIN(id) FROM _sql_tokens 
        WHERE 
          level = statement_level 
          AND state = 'alpha' and LOWER(token) IN ('group', 'having', 'order', 'limit', 'into') 
          AND id between id_from and id_to
          AND id > id_where_clause
        INTO id_where_clause_end;
      if id_where_clause_end is NULL then
        
        
        call _expand_statement_variables(id_where_clause + 1, id_to, query_part_where_clause, @common_schema_dummy, should_execute_statement);
      else
        call _expand_statement_variables(id_where_clause + 1, id_where_clause_end - 1, query_part_where_clause, @common_schema_dummy, should_execute_statement);
        call _expand_statement_variables(id_where_clause_end, id_to, query_part_suffix, @common_schema_dummy, should_execute_statement);
      end if;
      
      set split_injected_action_statement := CONCAT(
          query_part_prefix, ' (', query_part_where_clause, ') AND ', split_injected_text, ' ', query_part_suffix
        );
    end if;
end ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `_interpret` */;
DELIMITER ;;
CREATE DEFINER=`percona`@`localhost` PROCEDURE `_interpret`(
  in query_script text,
  in should_execute_statement tinyint unsigned
)
    MODIFIES SQL DATA
    DETERMINISTIC
    SQL SECURITY INVOKER
    COMMENT '...'
main_body: begin
  declare id_from int unsigned;
  declare id_to int unsigned;
  declare end_level int;
  declare negative_level_id int unsigned;
  declare expanded_variables_ids text charset ascii;
  declare num_expanded_variables_ids int unsigned;
  declare expanded_variable_index int unsigned;
  declare current_expanded_variable_id int unsigned;
  
  DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
    BEGIN
      call _cleanup_script_tables();
      RESIGNAL;
    END;
   
  
  set @@max_sp_recursion_depth := 127;
  set @__script_group_concat_max_len := @@group_concat_max_len;
  set @@group_concat_max_len := CONVERT( GREATEST( @@group_concat_max_len, 2+32*1024*1024 ), INT );
  
  call _get_sql_tokens(query_script);
  SELECT MIN(id), MAX(id) FROM _sql_tokens INTO id_from, id_to;
  SELECT MIN(id) FROM _sql_tokens WHERE level < 0 INTO negative_level_id;
  if negative_level_id IS NOT NULL then
    call throw(CONCAT('Negative nesting level detected at id ', negative_level_id));
  end if;
  SELECT level FROM _sql_tokens WHERE id = id_to INTO end_level;
  if end_level != 0 then
    call throw('Invalid nesting level detected at end of script');
  end if;
  delete from _qs_variables;
  
  
  
  
  select group_concat(id order by id) from _sql_tokens where state='expanded query_script variable' and token=':$' into expanded_variables_ids;
  if expanded_variables_ids then
    set num_expanded_variables_ids := get_num_tokens(expanded_variables_ids, ',');
    set expanded_variable_index := 1;
    while expanded_variable_index <= num_expanded_variables_ids do
      set current_expanded_variable_id := split_token(expanded_variables_ids, ',', expanded_variable_index);
      select 
          GROUP_CONCAT(token ORDER BY id SEPARATOR '') AS expanded_variable_tokens, 
          (
            GROUP_CONCAT(state ORDER BY id) = 'left braces,alpha,right braces' OR
            GROUP_CONCAT(state ORDER BY id) = 'left braces,alphanum,right braces' 
          ) AS expanded_variable_match
        from _sql_tokens where id between current_expanded_variable_id+1 and current_expanded_variable_id+3
        into @_expanded_variable_tokens, @_expanded_variable_match;
      if @_expanded_variable_match then
        
        
        update _sql_tokens set token = CONCAT(':$', @_expanded_variable_tokens) where id = current_expanded_variable_id;
        update _sql_tokens set token = '', state = 'whitespace', level = level - 1 where id between current_expanded_variable_id+1 and current_expanded_variable_id+3;
      end if;
      
      set expanded_variable_index := expanded_variable_index + 1;
    end while;    
  end if;
  
  set @_common_schema_script_break_type := NULL;
  set @_common_schema_script_loop_nesting_level := 0;
  set @_common_schema_script_throttle_chunk_start := NULL;
  set @_common_schema_script_start_timestamp := NOW();
  set @_common_schema_script_report_used := false;
  set @_common_schema_script_report_delimiter := '';
  
  
  call _declare_local_variable(0, 0, id_to, 0, '$rowcount', '@query_script_rowcount', FALSE);
  call _declare_local_variable(0, 0, id_to, 0, '$found_rows', '@query_script_found_rows', FALSE);
  
  
  call _consume_statement(id_from, id_to, FALSE, id_to, 0, FALSE);
  
  if should_execute_statement then
    
    call _consume_statement(id_from, id_to, FALSE, id_to, 0, TRUE);
  end if;
  
  if @_common_schema_script_report_used then
    call _script_report_finalize();
  end if;
  
  set @@group_concat_max_len := @__script_group_concat_max_len;
  call _cleanup_script_tables();
end ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `_match_states` */;
DELIMITER ;;
CREATE DEFINER=`percona`@`localhost` PROCEDURE `_match_states`(
   in   id_from      int unsigned,
   in   id_to      int unsigned,
   in   expected_states_list text charset utf8,
   in	allow_spaces_between_states tinyint unsigned,
   in	allow_trailing_states tinyint unsigned,
   in   repeat_count int unsigned,
   in   repeat_delimiter_state text charset utf8,
   in	return_tokens_array_id tinyint unsigned,
   in   throw_on_mismatch tinyint unsigned,
   out  states_have_matched tinyint unsigned,
   out  tokens_array_id VARCHAR(16) charset ascii,
   out  single_matched_token text charset utf8,
   out	consumed_to_id int unsigned
)
    READS SQL DATA
    DETERMINISTIC
    SQL SECURITY INVOKER
    COMMENT 'Expects a state or raises error'
main_body: begin
  declare state_has_matched tinyint unsigned default FALSE;
  declare num_states int unsigned default 0;
  declare states_index int unsigned;
  declare repeat_index int unsigned;
  declare expected_states text charset utf8;
  
  if return_tokens_array_id then
    call _create_array(tokens_array_id);
  end if;

  set num_states := get_num_tokens(expected_states_list, ',');
  if num_states = 0 then
    call _throw_script_error(id_from, 'Internal error: num_states = 0 in _match_states');
  end if;
  
  
  set states_have_matched := true;
  set repeat_index := 1;
  repeat_loop: while (repeat_index <= repeat_count) or (repeat_count = 0) do
    call _skip_spaces(id_from, id_to);
    set states_index := 1;

    while states_index <= num_states do
      
      if allow_spaces_between_states then
        call _skip_spaces(id_from, id_to);
      else
        
        
        call _skip_empty_spaces(id_from, id_to);
      end if;
      set expected_states := split_token(expected_states_list, ',', states_index);

      select token, FIND_IN_SET(state, REPLACE(expected_states, '|', ',')) is true from _sql_tokens where id = id_from into single_matched_token, state_has_matched;
      if state_has_matched then
        set consumed_to_id := id_from;
        if return_tokens_array_id then
          call _push_array_element(tokens_array_id, single_matched_token);
        end if;
      else
        set states_have_matched := false;
        leave repeat_loop;
      end if;
      set id_from := id_from + 1;
      
      set states_index := states_index + 1;
    end while;
    
    
    if repeat_delimiter_state != 'whitespace' then
      
      
      call _skip_spaces(id_from, id_to);
    end if;
    if repeat_delimiter_state is not null then
      select token, (state = repeat_delimiter_state) from _sql_tokens where id = id_from into @_common_schema_dummy, state_has_matched;
      if not state_has_matched then
        
        
        
        
        if (repeat_index < repeat_count) then
          set states_have_matched := false;
        end if;
        leave repeat_loop;
      end if;
      set id_from := id_from + 1;
    end if;
    
    set repeat_index := repeat_index + 1; 
  end while;

  if states_have_matched then
    
    if allow_trailing_states then
      
      leave main_body;
    end if;
 
    
    call _skip_spaces(id_from, id_to);
    call _skip_end_of_statement(id_from, id_to);
    if id_from <= id_to then
      set states_have_matched := false;
    end if;
  end if;
  
  if not states_have_matched then
    
    if throw_on_mismatch then
      call _throw_script_error(id_from, CONCAT('Expected ', REPLACE(expected_states, '|', '/')));
    else
      leave main_body;
    end if;
  end if;

end ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `_peek_states_list` */;
DELIMITER ;;
CREATE DEFINER=`percona`@`localhost` PROCEDURE `_peek_states_list`(
   in   id_from					int unsigned,
   in   id_to					int unsigned,
   in   expected_states_list	text charset utf8,
   in	allow_spaces			tinyint unsigned,
   in	allow_trailing_states	tinyint unsigned,
   in	return_tokens_array_id	tinyint unsigned,
   out  tokens_array_id			varchar(16) charset ascii,
   out	tokens_matched_to		int unsigned
)
    READS SQL DATA
    DETERMINISTIC
    SQL SECURITY INVOKER
    COMMENT 'Check if given states list apply'
main_body: begin
  declare states_have_matched tinyint unsigned default false;

  call _match_states(id_from, id_to, expected_states_list, allow_spaces, allow_trailing_states, 1, null, return_tokens_array_id, false, states_have_matched, tokens_array_id, @_common_schema_dummy, tokens_matched_to);
  if not states_have_matched then
    set tokens_matched_to := 0;
  end if;
end ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `_push_array_element` */;
DELIMITER ;;
CREATE DEFINER=`percona`@`localhost` PROCEDURE `_push_array_element`(
   in  array_id VARCHAR(16) charset ascii,
   in  element text charset utf8
)
    MODIFIES SQL DATA
    DETERMINISTIC
    SQL SECURITY INVOKER
    COMMENT 'Pushes new element, key becomes incrementing number'
main_body: begin
  do _push_mxarray_element(array_id, element);
end ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `_rdebug_analyze_routine` */;
DELIMITER ;;
CREATE DEFINER=`percona`@`localhost` PROCEDURE `_rdebug_analyze_routine`(
  in rdebug_routine_schema varchar(128) charset utf8,
  in rdebug_routine_name   varchar(128) charset utf8
)
    MODIFIES SQL DATA
    DETERMINISTIC
    SQL SECURITY INVOKER
main_body: BEGIN
  declare current_id int unsigned;
  declare current_pos int unsigned;
  declare current_token text charset utf8;
  declare current_state varchar(32);
  declare is_beginning_of_statement tinyint unsigned default false;
  declare is_labeled_statement tinyint unsigned default false;
  declare expected_token varchar(32) charset ascii default null;
  declare next_nesting_block_id int unsigned default 0;
  declare declare_variables_statement_id int unsigned default null;
  declare valid_breakpoint_statement_id int unsigned default null;
  declare valid_breakpoint_statement_pos int unsigned default null;
  declare first_begin_id int unsigned default null;
  declare first_statement_id int unsigned default null;
  
  declare cursor_done tinyint unsigned default false;
  declare tokens_cursor cursor for select id, start, token, state from _routine_tokens;
  declare continue handler for not found set cursor_done := true;

  update _routine_tokens set nesting_level = 0, is_valid_for_breakpoint = 0, is_declare_variables_statement = 0, is_first_statement = 0;
  delete from _rdebug_routine_statements where routine_schema = rdebug_routine_schema and routine_name = rdebug_routine_name;
  delete from _rdebug_routine_variables where routine_schema = rdebug_routine_schema and routine_name = rdebug_routine_name;
  delete from _rdebug_routine_variables_state where routine_schema = rdebug_routine_schema and routine_name = rdebug_routine_name;
  delete from _rdebug_breakpoint_hints where routine_schema = rdebug_routine_schema and routine_name = rdebug_routine_name;

  open tokens_cursor;
  tokens_cursor_loop: loop
    fetch tokens_cursor into current_id, current_pos, current_token, current_state; 
    if cursor_done then
      leave tokens_cursor_loop;
    end if;
    set current_token := lower(current_token);
    if current_state in ('whitespace', 'single line comment', 'multi line comment') then
      iterate tokens_cursor_loop;
    end if;
    if current_state in ('colon') and first_begin_id is not null then
      
      
      set is_beginning_of_statement := true;
      set is_labeled_statement := true;
      set expected_token := '';
      iterate tokens_cursor_loop;
    end if;
    if (current_state, current_token) = ('alpha', 'begin') then
      if first_begin_id is null then
        set first_begin_id := current_id;
      end if;
      update _routine_tokens set nesting_level = nesting_level + 1 where id >= current_id;
      set next_nesting_block_id := next_nesting_block_id + 1;
    end if;
    
    if current_state = 'user-defined variable' then
      insert ignore into 
        _rdebug_routine_variables (routine_schema, routine_name, variable_name, variable_scope_id_start, variable_scope_id_end, variable_type)
        values (rdebug_routine_schema, rdebug_routine_name, current_token, 0, NULL, 'user_defined');
    end if;
    
    if (current_state, current_token) = ('alpha', 'begin') or (current_state in ('statement delimiter', 'start')) then
      set is_beginning_of_statement := true;
      if declare_variables_statement_id is not null then
        
        update _routine_tokens set is_declare_variables_statement = 1 where id = declare_variables_statement_id;
        
        call _rdebug_analyze_routine_declare_statement(rdebug_routine_schema, rdebug_routine_name, declare_variables_statement_id, current_id);
        set declare_variables_statement_id := null;
      end if;
      if valid_breakpoint_statement_id is not null then
        
        insert into 
          _rdebug_routine_statements (routine_schema, routine_name, statement_id, statement_start_pos, statement_end_pos)
        values
          (rdebug_routine_schema, rdebug_routine_name, valid_breakpoint_statement_id, valid_breakpoint_statement_pos, current_pos);
        set valid_breakpoint_statement_id := null;
      end if;
      iterate tokens_cursor_loop;
    end if;
    if expected_token = '' then
      set expected_token := null;
    end if;
    if expected_token is not null and expected_token != current_token then
      iterate tokens_cursor_loop;
    end if;
    if expected_token = current_token then
      
      set expected_token := null;

      
      if valid_breakpoint_statement_id is not null then
        
        insert into 
          _rdebug_routine_statements (routine_schema, routine_name, statement_id, statement_start_pos, statement_end_pos)
        values
          (rdebug_routine_schema, rdebug_routine_name, valid_breakpoint_statement_id, valid_breakpoint_statement_pos, current_pos);
        set valid_breakpoint_statement_id := null;
      end if;
      
      iterate tokens_cursor_loop;
    end if;
    if declare_variables_statement_id is not null and current_token in ('cursor', 'handler', 'condition') then
      
      set declare_variables_statement_id := null;
    end if;
    
    
    if is_beginning_of_statement then
      if first_statement_id is null then
        update _routine_tokens set is_first_statement = 1 where id = current_id;    
        set first_statement_id := current_id;
      end if;
      case current_token 
        when 'if' then set expected_token := 'then';
        when 'else' then set expected_token := '';
        when 'while' then set expected_token := 'do';
        when 'loop' then set expected_token := '';
        when 'repeat' then set expected_token := '';
        when 'when' then set expected_token := 'then';
        else begin end;
      end case;
      if current_token in ('if', 'while', 'repeat', 'loop', 'case') then
        update _routine_tokens set nesting_level = nesting_level + 1 where id >= current_id;
        set next_nesting_block_id := next_nesting_block_id + 1;
      end if;
      if current_token = 'end' then
        update _routine_tokens set nesting_level = nesting_level - 1 where id > current_id;
      end if;
      if (current_token = 'declare') then
        set declare_variables_statement_id := current_id;
      elseif (expected_token is null) or (current_token in ('if', 'while', 'loop', 'repeat')) then
        if not is_labeled_statement then
          
          
          
          
          update _routine_tokens set is_valid_for_breakpoint = 1 where id = current_id;
          set valid_breakpoint_statement_id := current_id;
          set valid_breakpoint_statement_pos := current_pos;
        end if;
      end if;
      set is_labeled_statement := false;
      if expected_token is not null then
        iterate tokens_cursor_loop;
      end if;
      set is_beginning_of_statement := false;
    end if;
  end loop;
  close tokens_cursor;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `_rdebug_analyze_routine_declare_statement` */;
DELIMITER ;;
CREATE DEFINER=`percona`@`localhost` PROCEDURE `_rdebug_analyze_routine_declare_statement`(
  in rdebug_routine_schema varchar(128) charset utf8,
  in rdebug_routine_name   varchar(128) charset utf8,
  in declare_id int unsigned,
  in declare_end_id int unsigned
)
    MODIFIES SQL DATA
    DETERMINISTIC
    SQL SECURITY INVOKER
main_body: BEGIN
  declare current_id int unsigned;
  declare current_token text charset utf8;
  declare current_state varchar(32);
  declare variable_expected tinyint unsigned default true;
  
  declare cursor_done tinyint unsigned default false;
  declare variables_cursor cursor for select id, token, state from _routine_tokens where id between declare_id + 1 and declare_end_id;
  declare continue handler for not found set cursor_done := true;

  open variables_cursor;
  cursor_loop: loop
    fetch variables_cursor into current_id, current_token, current_state; 
    if cursor_done then
      leave cursor_loop;
    end if;
    if current_state = 'whitespace' then
      iterate cursor_loop;
    end if;
    if current_state = 'comma' then
      set variable_expected := true;
      iterate cursor_loop;
    end if;
    if not variable_expected then
      leave cursor_loop;
    end if;
    
    insert ignore into 
      _rdebug_routine_variables (routine_schema, routine_name, variable_name, variable_scope_id_start, variable_scope_id_end, variable_type)
      values (rdebug_routine_schema, rdebug_routine_name, current_token, declare_id, NULL, 'local');
    set variable_expected := false;
  end loop;
  close variables_cursor;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `_rdebug_analyze_routine_params` */;
DELIMITER ;;
CREATE DEFINER=`percona`@`localhost` PROCEDURE `_rdebug_analyze_routine_params`(
  in rdebug_routine_schema varchar(128) charset utf8,
  in rdebug_routine_name   varchar(128) charset utf8,
  in variables_routine_param_list blob,
  in quoting_characters VARCHAR(5)
)
    MODIFIES SQL DATA
    DETERMINISTIC
    SQL SECURITY INVOKER
main_body: BEGIN
  begin
    
    declare param_index int unsigned default 1;
    declare param_text text charset utf8;
  
    set variables_routine_param_list := replace_sections(variables_routine_param_list, '(', ')', '');
    set variables_routine_param_list := common_schema._retokenized_text(variables_routine_param_list, ',', quoting_characters, true, 'skip');
  
    while param_index <= ifnull(@common_schema_retokenized_count, 0) do
      set param_text := split_token(variables_routine_param_list, @common_schema_retokenized_delimiter, param_index);
      set param_text := replace_all(param_text, ' \n\r\b\t', ' ');
      if (locate('in ', lower(param_text)) = 1) or (locate('out ', lower(param_text)) = 1) or (locate('inout ', lower(param_text)) = 1) then
        set param_text = trim(substring(param_text, locate(' ', param_text)));
      end if;
      set param_text = trim(left(param_text, locate(' ', param_text)));
      insert into 
        _rdebug_routine_variables (routine_schema, routine_name, variable_name, variable_scope_id_start, variable_scope_id_end, variable_type)
        values (rdebug_routine_schema, rdebug_routine_name, param_text, 0, NULL, 'param');
      set param_index := param_index + 1;
    end while;
  end;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `_rdebug_analyze_routine_variables_scope` */;
DELIMITER ;;
CREATE DEFINER=`percona`@`localhost` PROCEDURE `_rdebug_analyze_routine_variables_scope`(
  in rdebug_routine_schema varchar(128) charset utf8,
  in rdebug_routine_name   varchar(128) charset utf8
)
    MODIFIES SQL DATA
    DETERMINISTIC
    SQL SECURITY INVOKER
main_body: BEGIN
  declare current_variable_name varchar(128);
  declare current_variable_scope_id_start int unsigned;
  declare current_nesting_level int unsigned;
  declare scope_end_id int unsigned;
  
  declare cursor_done tinyint unsigned default false;
  declare variables_cursor cursor for 
    select variable_name, variable_scope_id_start, nesting_level
    from 
      _rdebug_routine_variables
      join _routine_tokens on (variable_scope_id_start = _routine_tokens.id)
    where routine_schema = rdebug_routine_schema and routine_name = rdebug_routine_name and variable_type = 'local';
  declare continue handler for not found set cursor_done := true;

  open variables_cursor;
  cursor_loop: loop
    fetch variables_cursor into current_variable_name, current_variable_scope_id_start, current_nesting_level; 
    if cursor_done then
      leave cursor_loop;
    end if;
    
    select min(id) - 1 
      from _routine_tokens
      where id >= current_variable_scope_id_start and nesting_level < current_nesting_level
      into scope_end_id;

    update _rdebug_routine_variables set variable_scope_id_end = scope_end_id 
      where 
        routine_schema = rdebug_routine_schema 
        and routine_name = rdebug_routine_name 
        and variable_name = current_variable_name 
        and variable_scope_id_start = current_variable_scope_id_start;
  end loop;
  close variables_cursor;
  
  select max(id) from _routine_tokens into scope_end_id;
  update _rdebug_routine_variables set variable_scope_id_end = scope_end_id 
    where routine_schema = rdebug_routine_schema and routine_name = rdebug_routine_name and variable_type in ('user_defined', 'param');
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `_rdebug_evaluate_condition` */;
DELIMITER ;;
CREATE DEFINER=`percona`@`localhost` PROCEDURE `_rdebug_evaluate_condition`(
    in  rdebug_condition text,
    out rdebug_condition_result bool
  )
    READS SQL DATA
    DETERMINISTIC
    SQL SECURITY INVOKER
main_body: begin
  if rdebug_condition is null or rdebug_condition = '' then
    set rdebug_condition_result := true;
    leave main_body;
  end if;
  select 
      concat('
        select (', rdebug_condition, ') is true into @_common_schema_rdebug_condition_result'
      )
    into @_rdebug_command
  ;

  prepare st from @_rdebug_command;
  execute st;
  deallocate prepare st;

  set rdebug_condition_result := @_common_schema_rdebug_condition_result;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `_rdebug_export_variable_state` */;
DELIMITER ;;
CREATE DEFINER=`percona`@`localhost` PROCEDURE `_rdebug_export_variable_state`(
    in breakpoint_id int unsigned,
    in rdebug_routine_schema varchar(128) charset utf8,
    in rdebug_routine_name   varchar(128) charset utf8
  )
    NO SQL
    DETERMINISTIC
    SQL SECURITY INVOKER
BEGIN
  select 
      concat('
        insert into _rdebug_routine_variables_state
          (worker_id, stack_level, routine_schema, routine_name, variable_name, variable_value)
        values
        ', 
          group_concat(
            '(CONNECTION_ID(), @_rdebug_stack_level_, ', QUOTE(rdebug_routine_schema), ',', QUOTE(rdebug_routine_name),
            ',', QUOTE(variable_name), ',', IF(variable_type IN ('param', 'local'), CONCAT('@$_$', variable_name), variable_name), ')'
           ),
        ' on duplicate key update variable_value=VALUES(variable_value)'
      )
    from
      _rdebug_routine_variables 
    where
      breakpoint_id between _rdebug_routine_variables.variable_scope_id_start and _rdebug_routine_variables.variable_scope_id_end
      and routine_schema = rdebug_routine_schema and routine_name = rdebug_routine_name
    into @_rdebug_command
  ;
  if @_rdebug_command is not null then
    
    prepare st from @_rdebug_command;
    execute st;
    deallocate prepare st;
  end if;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `_rdebug_import_variable_state` */;
DELIMITER ;;
CREATE DEFINER=`percona`@`localhost` PROCEDURE `_rdebug_import_variable_state`(
    in breakpoint_id int unsigned,
    in rdebug_routine_schema varchar(128) charset utf8,
    in rdebug_routine_name   varchar(128) charset utf8
  )
    NO SQL
    DETERMINISTIC
    SQL SECURITY INVOKER
BEGIN
  select 
      concat('
        select ',
          group_concat('max(if(variable_name=', QUOTE(variable_name), ', variable_value, NULL))' order by variable_name), '
        from
          _rdebug_routine_variables_state
        where
          worker_id = CONNECTION_ID()
          and stack_level = @_rdebug_stack_level_
          and routine_schema = ', QUOTE(rdebug_routine_schema), ' and routine_name = ', QUOTE(rdebug_routine_name), '
        into ',
          group_concat(IF(variable_type IN ('param', 'local'), CONCAT('@$_$', variable_name), variable_name) order by variable_name)
      )
    from
      _rdebug_routine_variables 
    where
      breakpoint_id between _rdebug_routine_variables.variable_scope_id_start and _rdebug_routine_variables.variable_scope_id_end
      and routine_schema = rdebug_routine_schema and routine_name = rdebug_routine_name
    into @_rdebug_command
  ;
  
  if @_rdebug_command is not null then
    
    prepare st from @_rdebug_command;
    execute st;
    deallocate prepare st;
  end if;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `_rdebug_invalidate_routine_cache` */;
DELIMITER ;;
CREATE DEFINER=`percona`@`localhost` PROCEDURE `_rdebug_invalidate_routine_cache`()
    MODIFIES SQL DATA
    DETERMINISTIC
BEGIN
  create or replace view _rdebug_invalidate_routine_cache_view as select 1 as `invalidated`;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `_rdebug_on_breakpoint` */;
DELIMITER ;;
CREATE DEFINER=`percona`@`localhost` PROCEDURE `_rdebug_on_breakpoint`(
    in breakpoint_id int unsigned,
    in rdebug_routine_schema varchar(128) charset utf8,
    in rdebug_routine_name   varchar(128) charset utf8
  )
    NO SQL
    DETERMINISTIC
    SQL SECURITY INVOKER
main_body: BEGIN
  declare is_active_breakpoint bool default true;
  declare is_dedicated_breakpoint bool default true;
  declare rdebug_conditional_expression text default null;
  declare have_waited bool default false;

  select count(*) > 0 from _rdebug_step_hints 
    where worker_id = connection_id() 
    and (
      (hint_type = 'step_into')
      or (hint_type = 'step_over' and @_rdebug_stack_level_ <= stack_level)
      or (hint_type = 'step_out' and @_rdebug_stack_level_ < stack_level)
    )
    into is_active_breakpoint
  ;

  if not is_active_breakpoint then
    
    select count(statement_id) > 0, min(conditional_expression)
      from _rdebug_breakpoint_hints
      where 
        worker_id = connection_id()
        and routine_schema = rdebug_routine_schema
        and routine_name = rdebug_routine_name
        and statement_id = breakpoint_id
      into is_dedicated_breakpoint, rdebug_conditional_expression;
    if is_dedicated_breakpoint and rdebug_conditional_expression is null then
      
      set is_active_breakpoint := true;
    end if;
  end if;

  if not is_active_breakpoint then
    leave main_body;
  end if;

  if is_used_lock(_rdebug_get_lock_name(connection_id(), 'tic')) then
    while is_used_lock(_rdebug_get_lock_name(connection_id(), 'tic')) != connection_id() do
      if not have_waited then
        call _rdebug_on_breakpoint_start_wait(breakpoint_id, rdebug_routine_schema, rdebug_routine_name);
      end if;
      do sleep(0.1 + coalesce(0, 'rdebug_worker_waiting_on_breakpoint'));
      set have_waited := true;
    end while;
  else
    while is_used_lock(_rdebug_get_lock_name(connection_id(), 'toc')) != connection_id() do
      if not have_waited then
        call _rdebug_on_breakpoint_start_wait(breakpoint_id, rdebug_routine_schema, rdebug_routine_name);
      end if;
      do sleep(0.1 + coalesce(0, 'rdebug_worker_waiting_on_breakpoint'));
      set have_waited := true;
    end while;
  end if;

  if have_waited then
    call _rdebug_on_breakpoint_end_wait(breakpoint_id, rdebug_routine_schema, rdebug_routine_name);
  end if;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `_rdebug_on_breakpoint_end_wait` */;
DELIMITER ;;
CREATE DEFINER=`percona`@`localhost` PROCEDURE `_rdebug_on_breakpoint_end_wait`(
    in breakpoint_id int unsigned,
    in rdebug_routine_schema varchar(128) charset utf8,
    in rdebug_routine_name   varchar(128) charset utf8
  )
    NO SQL
    DETERMINISTIC
    SQL SECURITY INVOKER
main_body: BEGIN
  call _rdebug_import_variable_state(breakpoint_id, rdebug_routine_schema, rdebug_routine_name);
  
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `_rdebug_on_breakpoint_start_wait` */;
DELIMITER ;;
CREATE DEFINER=`percona`@`localhost` PROCEDURE `_rdebug_on_breakpoint_start_wait`(
    in breakpoint_id int unsigned,
    in rdebug_routine_schema varchar(128) charset utf8,
    in rdebug_routine_name   varchar(128) charset utf8
  )
    NO SQL
    DETERMINISTIC
    SQL SECURITY INVOKER
main_body: BEGIN
  delete from _rdebug_stack_state 
    where worker_id = connection_id() and stack_level > @_rdebug_stack_level_;
  delete from _rdebug_routine_variables_state 
    where worker_id = connection_id() and stack_level > @_rdebug_stack_level_;
    
  insert into _rdebug_stack_state 
      (worker_id, stack_level, routine_schema, routine_name, statement_id) 
    values 
      (connection_id(), @_rdebug_stack_level_, rdebug_routine_schema, rdebug_routine_name, breakpoint_id)
    on duplicate key update 
      routine_schema = VALUES(routine_schema),
      routine_name = VALUES(routine_name),
      statement_id = VALUES(statement_id);
  
  call _rdebug_export_variable_state(breakpoint_id, rdebug_routine_schema, rdebug_routine_name);

  call _rdebug_send_breakpoint_clock();
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `_rdebug_on_routine_entry` */;
DELIMITER ;;
CREATE DEFINER=`percona`@`localhost` PROCEDURE `_rdebug_on_routine_entry`(
    in rdebug_routine_schema varchar(128) charset utf8,
    in rdebug_routine_name   varchar(128) charset utf8
  )
    NO SQL
    DETERMINISTIC
    SQL SECURITY INVOKER
main_body: BEGIN	
  if is_used_lock(_rdebug_get_lock_name(connection_id(), 'worker')) is null then
    leave main_body;
  end if;

  insert into _rdebug_stats (
    worker_id, routine_schema, routine_name, statement_id, count_visits)
    values (connection_id(), rdebug_routine_schema, rdebug_routine_name, 0, 1)
    on duplicate key update count_visits = count_visits + 1;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `_rdebug_release_worker_and_wait_for_breakpoint` */;
DELIMITER ;;
CREATE DEFINER=`percona`@`localhost` PROCEDURE `_rdebug_release_worker_and_wait_for_breakpoint`()
    READS SQL DATA
    DETERMINISTIC
    SQL SECURITY INVOKER
BEGIN
  call thread_wait(
    concat('breakpoint_clock_', @_rdebug_recipient_id), 
    0.1, 
    'call _rdebug_send_clock()', 
    concat('
       (select ifnull(max(command)=''Sleep'', true) from information_schema.processlist where id=', @_rdebug_recipient_id, ')
       and (select count(*) > 0 from _rdebug_stack_state where worker_id=',@_rdebug_recipient_id, ')
    ')
  );
  select ifnull(max(command)='Sleep', true) from information_schema.processlist where id=@_rdebug_recipient_id into @_rdebug_worker_done;
  if @_rdebug_worker_done then
    
    delete from _rdebug_stack_state where worker_id = @_rdebug_recipient_id;
  end if;
  if @_rdebug_verbose and not @_rdebug_worker_done then
    call rdebug_verbose();
  end if;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `_rdebug_send_breakpoint_clock` */;
DELIMITER ;;
CREATE DEFINER=`percona`@`localhost` PROCEDURE `_rdebug_send_breakpoint_clock`()
    NO SQL
    DETERMINISTIC
    SQL SECURITY INVOKER
main_body: BEGIN
  call thread_notify(concat('breakpoint_clock_', connection_id()));
  leave main_body;
  
  select 
    if(
      is_used_lock(_rdebug_get_lock_name(connection_id(), 'breakpoint_tic')) = connection_id(),
      release_lock(_rdebug_get_lock_name(connection_id(), 'breakpoint_tic')) + get_lock(_rdebug_get_lock_name(connection_id(), 'breakpoint_toc'), 100000000),
      release_lock(_rdebug_get_lock_name(connection_id(), 'breakpoint_toc')) + get_lock(_rdebug_get_lock_name(connection_id(), 'breakpoint_tic'), 100000000)
    )
  into @common_schema_dummy;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `_rdebug_send_clock` */;
DELIMITER ;;
CREATE DEFINER=`percona`@`localhost` PROCEDURE `_rdebug_send_clock`()
    NO SQL
    DETERMINISTIC
    SQL SECURITY INVOKER
BEGIN
  select 
    if(
      is_used_lock(_rdebug_get_lock_name(@_rdebug_recipient_id, 'tic')) = connection_id(),
      release_lock(_rdebug_get_lock_name(@_rdebug_recipient_id, 'tic')) + get_lock(_rdebug_get_lock_name(@_rdebug_recipient_id, 'toc'), 100000000),
      release_lock(_rdebug_get_lock_name(@_rdebug_recipient_id, 'toc')) + get_lock(_rdebug_get_lock_name(@_rdebug_recipient_id, 'tic'), 100000000)
    )
  into @common_schema_dummy;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `_rdebug_set_step_hint` */;
DELIMITER ;;
CREATE DEFINER=`percona`@`localhost` PROCEDURE `_rdebug_set_step_hint`(
  rdebug_hint_type varchar(32)
)
    MODIFIES SQL DATA
    DETERMINISTIC
    SQL SECURITY INVOKER
main_body: BEGIN
  declare rdebug_stack_level int unsigned default null;
  
  select
      ifnull(max(stack_level), 1)
    from _rdebug_stack_state
    where worker_id = @_rdebug_recipient_id
    group by worker_id
    into rdebug_stack_level;

  insert into 
      _rdebug_step_hints (worker_id, hint_type, stack_level, is_consumed)
    values 
      (@_rdebug_recipient_id, rdebug_hint_type, ifnull(rdebug_stack_level,1), 0)
    on duplicate key update 
      hint_type = values(hint_type), stack_level = values(stack_level), is_consumed = values(is_consumed) 
    ;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `_rdebug_wait_for_breakpoint_clock` */;
DELIMITER ;;
CREATE DEFINER=`percona`@`localhost` PROCEDURE `_rdebug_wait_for_breakpoint_clock`()
    NO SQL
    DETERMINISTIC
    SQL SECURITY INVOKER
main_body: BEGIN
  declare have_waited bool default false;

  
  
  while is_used_lock(_rdebug_get_lock_name(@_rdebug_recipient_id, 'breakpoint_tic')) is null
    and is_used_lock(_rdebug_get_lock_name(@_rdebug_recipient_id, 'breakpoint_toc')) is null do 
      do sleep(0.1);
      set have_waited := true;
  end while;
  if have_waited then
    leave main_body;
  end if;
  
  
  if is_used_lock(_rdebug_get_lock_name(@_rdebug_recipient_id, 'breakpoint_tic')) then
    while is_used_lock(_rdebug_get_lock_name(@_rdebug_recipient_id, 'breakpoint_tic')) = @_rdebug_recipient_id do
      do sleep(0.1);
    end while;
  else
    while is_used_lock(_rdebug_get_lock_name(@_rdebug_recipient_id, 'breakpoint_toc')) = @_rdebug_recipient_id do
      do sleep(0.1);
    end while;
  end if;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `_resolve_and_consume_sql_or_script_statement` */;
DELIMITER ;;
CREATE DEFINER=`percona`@`localhost` PROCEDURE `_resolve_and_consume_sql_or_script_statement`(
   in   id_from      int unsigned,
   in   id_to      int unsigned,
   in   statement_id_from      int unsigned,
   in   statement_id_to      int unsigned,
   in   depth int unsigned,
   in   statement_token text charset utf8,
   in   should_execute_statement tinyint unsigned
)
    MODIFIES SQL DATA
    DETERMINISTIC
    SQL SECURITY INVOKER
    COMMENT 'Reads script statement'
main_body: begin
  declare resolve_statement_type text charset utf8;
  declare token_has_matched tinyint unsigned default FALSE;
  
  select statement_type from _script_statements where statement = statement_token into resolve_statement_type;
  
  case resolve_statement_type
    when 'sql' then begin
	    call _consume_sql_statement(id_from, statement_id_to, should_execute_statement);
	  end;
    when 'script' then begin
	    call _consume_script_statement(id_from, id_to, statement_id_from, statement_id_to, depth, statement_token, should_execute_statement);
	  end;
    when 'script,sql' then begin
        if statement_token = 'start' then
          
          set @_common_schema_dummy := 0;
          call _consume_if_exists(statement_id_from, statement_id_to, @_common_schema_dummy, 'transaction', NULL, token_has_matched, @_common_schema_dummy);
          if token_has_matched then
            call _consume_script_statement(id_from, id_to, statement_id_from, statement_id_to, depth, statement_token, should_execute_statement);
          else
            call _consume_sql_statement(id_from, statement_id_to, should_execute_statement);
          end if;
        end if;
	  end;
  end case;
end ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `_run_foreach_step` */;
DELIMITER ;;
CREATE DEFINER=`percona`@`localhost` PROCEDURE `_run_foreach_step`(
   execute_query TEXT CHARSET utf8,
   in   id_from      int unsigned,
   in   id_to      int unsigned,
   in   expect_single tinyint unsigned,
   out  consumed_to_id int unsigned,
   in   variables_array_id int unsigned,
   in depth int unsigned,
   in should_execute_statement tinyint unsigned
)
    MODIFIES SQL DATA
    SQL SECURITY INVOKER
    COMMENT 'Invoke queries/statement per foreach step'
main_body: begin
  if execute_query IS NOT NULL then
    call run(execute_query);
  elseif id_from IS NOT NULL then
    call _assign_input_local_variables(variables_array_id);
    call _consume_statement(id_from, id_to, expect_single, consumed_to_id, depth, should_execute_statement);
  else
    
    call throw('_run_foreach_step(): neither queries nor script position provided');
  end if;
end ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `_run_named_script` */;
DELIMITER ;;
CREATE DEFINER=`percona`@`localhost` PROCEDURE `_run_named_script`(
  named_script_name varchar(64) CHARACTER SET ascii
)
    MODIFIES SQL DATA
    DETERMINISTIC
    SQL SECURITY INVOKER
    COMMENT 'Run script from _named_scripts table'
main_body: begin
  declare query_script text charset utf8;
  
  select script_text from _named_scripts where script_name = named_script_name into query_script;
  if query_script is null then
    call throw(CONCAT('Unknown script: ', named_script_name));
  end if;
  call run(query_script);
end ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `_script_report` */;
DELIMITER ;;
CREATE DEFINER=`percona`@`localhost` PROCEDURE `_script_report`(report_params TEXT CHARSET utf8)
    MODIFIES SQL DATA
    DETERMINISTIC
    SQL SECURITY INVOKER
begin
  declare report_query text charset utf8;
  
  declare is_header tinyint unsigned default 0;
  declare is_paragraph tinyint unsigned default 0;
  declare is_bullet tinyint unsigned default 0;
  declare is_code tinyint unsigned default 0;
  declare is_horizontal_ruler tinyint unsigned default 0;
  
  if not @_common_schema_script_report_used then
    
    delete from _script_report_data;
    set @_common_schema_script_report_used := true;
  end if;
  
  set report_params := trim_wspace(report_params);
  set @_common_schema_script_report_prefix_len := 0;
  if (@_common_schema_script_report_prefix_len := starts_with(report_params, 'h1 ')) then
    set is_header := 1;
  elseif (@_common_schema_script_report_prefix_len := starts_with(report_params, 'p ')) then
    set is_paragraph := 1;
  elseif (@_common_schema_script_report_prefix_len := starts_with(report_params, 'li ')) then
    set is_bullet := 1;
  elseif (@_common_schema_script_report_prefix_len := starts_with(report_params, 'code ')) then
    set is_code := 1;
  elseif (@_common_schema_script_report_prefix_len := starts_with(report_params, 'hr ')) then
    set is_horizontal_ruler := 1;
  end if;
  set report_params := substring(report_params, @_common_schema_script_report_prefix_len + 1);
  
  set report_query := CONCAT('set @_query_script_report_line := CONCAT_WS(@_common_schema_script_report_delimiter, ', report_params, ')');
  call exec_single(report_query);
  
  set @_query_script_report_line := trim_wspace(@_query_script_report_line);


  if is_header then
    set @_query_script_report_line := CONCAT('\n', @_query_script_report_line,
      '\n', REPEAT('=', CHAR_LENGTH(@_query_script_report_line)));
  end if;
  if is_bullet then
    set @_query_script_report_line := CONCAT('- ', @_query_script_report_line);
  end if;
  if is_code then
    set @_query_script_report_line := CONCAT('> ', @_query_script_report_line);
  end if;
  if is_paragraph then
    set @_query_script_report_line := CONCAT('\n', @_query_script_report_line);
  end if;
  if is_horizontal_ruler then
    set @_query_script_report_line := CONCAT('---\n', @_query_script_report_line);
  end if;
  
  insert into 
    _script_report_data (server_id, session_id, info) 
  SELECT 
    _get_server_id(), CONNECTION_ID(), split_token(@_query_script_report_line, '\n', n)
  FROM
    numbers
  WHERE 
    numbers.n BETWEEN 1 AND get_num_tokens(@_query_script_report_line, '\n')
  ORDER BY 
    n ASC;

  set @_query_script_report_line := NULL;
end ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `_script_report_finalize` */;
DELIMITER ;;
CREATE DEFINER=`percona`@`localhost` PROCEDURE `_script_report_finalize`()
    MODIFIES SQL DATA
    DETERMINISTIC
    SQL SECURITY INVOKER
begin
  call _script_report(
    CONCAT('hr ', QUOTE('Report generated on '), QUOTE(NOW()))
  );

  select info as `report` from _script_report_data order by id;
end ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `_skip_empty_spaces` */;
DELIMITER ;;
CREATE DEFINER=`percona`@`localhost` PROCEDURE `_skip_empty_spaces`(
   inout   id_from      int unsigned,
   in   id_to      int unsigned
)
    READS SQL DATA
    DETERMINISTIC
    SQL SECURITY INVOKER
    COMMENT 'Skips empty whitespace tokens'
main_body: begin
    select min(id) from _sql_tokens 
      where id >= id_from and id <= id_to 
      and (state, CHAR_LENGTH(token)) != ('whitespace', 0) 
    into id_from;
    if id_from is null then
      set id_from := id_to + 1;
    end if;
end ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `_skip_end_of_statement` */;
DELIMITER ;;
CREATE DEFINER=`percona`@`localhost` PROCEDURE `_skip_end_of_statement`(
   inout   id_from      int unsigned,
   in   id_to      int unsigned
)
    READS SQL DATA
    DETERMINISTIC
    SQL SECURITY INVOKER
    COMMENT 'Skips enf of statement state'
main_body: begin
	call _skip_states(id_from, id_to, 'statement delimiter,start', null);
end ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `_skip_spaces` */;
DELIMITER ;;
CREATE DEFINER=`percona`@`localhost` PROCEDURE `_skip_spaces`(
   inout   id_from      int unsigned,
   in   id_to      int unsigned
)
    READS SQL DATA
    DETERMINISTIC
    SQL SECURITY INVOKER
    COMMENT 'Skips whitespace tokens'
main_body: begin
    call _skip_tokens_and_spaces(id_from, id_to, null);
end ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `_skip_states` */;
DELIMITER ;;
CREATE DEFINER=`percona`@`localhost` PROCEDURE `_skip_states`(
   inout   id_from      int unsigned,
   in   id_to      int unsigned,
   in	states_list text charset utf8,
   in	tokens_list text charset utf8
)
    READS SQL DATA
    DETERMINISTIC
    SQL SECURITY INVOKER
    COMMENT 'Skips given states'
main_body: begin
	set states_list := IFNULL(states_list, '');
	set tokens_list := LOWER(IFNULL(tokens_list, ''));
    select min(id) from _sql_tokens 
      where id >= id_from and id <= id_to 
      and FIND_IN_SET(state, states_list) = 0 
      and FIND_IN_SET(LOWER(token), tokens_list) = 0 
    into id_from;
    if id_from is null then
      set id_from := id_to + 1;
    end if;
end ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `_skip_tokens_and_spaces` */;
DELIMITER ;;
CREATE DEFINER=`percona`@`localhost` PROCEDURE `_skip_tokens_and_spaces`(
   inout   id_from      int unsigned,
   in   id_to      int unsigned,
   in	tokens_list text charset utf8
)
    READS SQL DATA
    DETERMINISTIC
    SQL SECURITY INVOKER
    COMMENT 'Skips whitespace and given tokens'
main_body: begin
    call _skip_states(id_from, id_to, 'whitespace,single line comment,multi line comment,start', tokens_list);
end ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `_split` */;
DELIMITER ;;
CREATE DEFINER=`percona`@`localhost` PROCEDURE `_split`(
  split_table_schema varchar(128), 
  split_table_name varchar(128),
  split_options varchar(2048) charset utf8,
  split_injected_action_statement TEXT CHARSET utf8, 
  split_injected_text TEXT CHARSET utf8,
  in   id_from      int unsigned,
  in   id_to      int unsigned,
  in   expect_single tinyint unsigned,
  out  consumed_to_id int unsigned,
  in depth int unsigned,
  in should_execute_statement tinyint unsigned
  )
    MODIFIES SQL DATA
    SQL SECURITY INVOKER
    COMMENT 'split values by columns...'
main_body: begin
  declare is_overflow tinyint unsigned;
  declare is_range_identical tinyint unsigned;
  declare is_empty_range tinyint unsigned;
  declare deadlock_detected tinyint unsigned;
  declare split_range_size int unsigned;
  declare comparison_clause text charset utf8;
  declare action_statement text charset utf8;
  declare continue handler for 1205 SET deadlock_detected = TRUE;

  set @_split_is_first_step_flag := true;
  
  call _split_deduce_columns(split_table_schema, split_table_name, get_option(split_options, 'index'));
  call _split_init_variables();
  call _split_assign_min_max_variables(id_from, split_table_schema, split_table_name, split_options, is_empty_range);
  
  if is_empty_range then
    leave main_body;
  end if;
  
  call _split_assign_initial_range_start_variables();
  
  set @_query_script_split_step_index := 0;
  set @_query_script_split_total_rowcount := 0;
  set @query_script_split_start_time := SYSDATE();
  
  call _declare_local_variable(id_from, id_from, id_to, depth, '$split_columns', '@query_script_split_columns', FALSE);
  call _declare_local_variable(id_from, id_from, id_to, depth, '$split_index', '@query_script_split_index_name', FALSE);
  call _declare_local_variable(id_from, id_from, id_to, depth, '$split_min', '@query_script_split_min', FALSE);
  call _declare_local_variable(id_from, id_from, id_to, depth, '$split_max', '@query_script_split_max', FALSE);
  call _declare_local_variable(id_from, id_from, id_to, depth, '$split_clause', '@query_script_split_comparison_clause', FALSE);
  call _declare_local_variable(id_from, id_from, id_to, depth, '$split_step', '@query_script_split_step_index', FALSE);
  call _declare_local_variable(id_from, id_from, id_to, depth, '$split_range_start', '@query_script_split_range_start_snapshot', FALSE);
  call _declare_local_variable(id_from, id_from, id_to, depth, '$split_range_end', '@query_script_split_range_end_snapshot', FALSE);
  call _declare_local_variable(id_from, id_from, id_to, depth, '$split_rowcount', '@query_script_split_rowcount', FALSE);
  call _declare_local_variable(id_from, id_from, id_to, depth, '$split_total_rowcount', '@query_script_split_total_rowcount', FALSE);
  call _declare_local_variable(id_from, id_from, id_to, depth, '$split_total_elapsed_time', '@query_script_split_total_elapsed_time', FALSE);
  call _declare_local_variable(id_from, id_from, id_to, depth, '$split_table_schema', '@query_script_split_table_schema', FALSE);
  call _declare_local_variable(id_from, id_from, id_to, depth, '$split_table_name', '@query_script_split_table_name', FALSE);
  
  set split_range_size := least(50000, greatest(100, floor(ifnull(get_option(split_options, 'size'), 1000))));
  _split_step_loop: loop
    call _split_is_range_start_overflow(is_overflow);
    if is_overflow and not @_split_is_first_step_flag then
      leave _split_step_loop;
    end if;
    call _split_assign_range_end_variables(split_table_schema, split_table_name, split_range_size);
    
    call _split_is_range_start_end_identical(is_range_identical);
    if is_range_identical and not @_split_is_first_step_flag then
      leave _split_step_loop;
    end if;
    
    call _split_get_step_comparison_clause(comparison_clause);

    set action_statement := REPLACE(split_injected_action_statement, split_injected_text, comparison_clause);

    repeat
      set deadlock_detected := false;
      call exec_single(action_statement);
      if deadlock_detected then
        select 'deadlock detected...' as msg;
      end if;

      set @_query_script_split_step_index := @_query_script_split_step_index + 1;
      set @query_script_split_step_index := @_query_script_split_step_index;
      set @query_script_split_rowcount := @common_schema_rowcount;
      set @_query_script_split_total_rowcount := @_query_script_split_total_rowcount + GREATEST(@query_script_split_rowcount, 0);
      set @query_script_split_total_rowcount := @_query_script_split_total_rowcount;
      set @query_script_split_total_elapsed_time := TIMESTAMPDIFF(MICROSECOND, @query_script_split_start_time, SYSDATE())/1000000.0;
      set @query_script_split_table_schema := split_table_schema;
      set @query_script_split_table_name := split_table_name;
      set @query_script_split_min := @_query_script_split_min;
      set @query_script_split_max := @_query_script_split_max;
      set @query_script_split_columns := @_query_script_split_columns;
      set @query_script_split_index_name := @_query_script_split_index_name;
      call _split_set_step_clause_and_ranges_local_variables(comparison_clause);
    
      call _consume_statement(id_from, id_to, expect_single, consumed_to_id, depth, should_execute_statement);

      if @_common_schema_script_break_type IS NOT NULL then
        if @_common_schema_script_break_type = 'break' then
          set @_common_schema_script_break_type := NULL;
        end if;
        leave _split_step_loop;
      end if;
    until deadlock_detected = false
    end repeat;

    call _split_assign_next_range_start_variables();
    set @_split_is_first_step_flag := false;
  end loop;
end ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `_split_assign_initial_range_start_variables` */;
DELIMITER ;;
CREATE DEFINER=`percona`@`localhost` PROCEDURE `_split_assign_initial_range_start_variables`()
    READS SQL DATA
    SQL SECURITY INVOKER
begin
  declare queries text default NULL;

  select 
    GROUP_CONCAT(
      'set ', range_start_variable_name, ' := ', min_variable_name, ';'
      separator ''
    )
    from _split_column_names_table
    into queries;
    
  call exec(queries);
  
  set @_split_column_variable_range_end_1 := NULL;
end ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `_split_assign_min_max_variables` */;
DELIMITER ;;
CREATE DEFINER=`percona`@`localhost` PROCEDURE `_split_assign_min_max_variables`(
  id_from      int unsigned,
  split_table_schema varchar(128), 
  split_table_name varchar(128),
  split_options varchar(2048) charset utf8,
  out is_empty_range tinyint unsigned
  )
    READS SQL DATA
    SQL SECURITY INVOKER
begin
  declare query text default NULL;
  declare manual_min_max_params_used tinyint unsigned default false;
  declare column_names text default _split_get_columns_names();
  declare columns_order_ascending_clause text default _split_get_columns_order_ascending_clause();
  declare min_variables_names text default _split_get_min_variables_names();
  declare columns_order_descending_clause text default _split_get_columns_order_descending_clause();
  declare max_variables_names text default _split_get_max_variables_names();
  declare columns_count tinyint unsigned default _split_get_columns_count();
  declare start_values text default get_option(split_options, 'start');
  declare stop_values text default get_option(split_options, 'stop');
  
  set is_empty_range := false;

  set query := CONCAT(
    'select ', column_names, ' from ',
    mysql_qualify(split_table_schema), '.', mysql_qualify(split_table_name), 
    ' order by ', columns_order_ascending_clause,
    ' limit 1 ',
    ' into ', min_variables_names
  );
  call exec_single(query);

  set query := CONCAT(
    'select ', column_names, ' from ',
    mysql_qualify(split_table_schema), '.', mysql_qualify(split_table_name), 
    ' order by ', columns_order_descending_clause,
    ' limit 1 ',
    ' into ', max_variables_names
  );
  call exec_single(query);
  
  if start_values is not null then
    if columns_count = get_num_tokens(start_values, ',') then
      select 
          group_concat('set ', min_variable_name, ' := ', QUOTE(split_token(start_values, ',', column_order)), ';' order by column_order separator '')
        from
          _split_column_names_table
        into query;
      call exec(query);
      set manual_min_max_params_used := true;
    else
      call _throw_script_error(id_from, CONCAT(get_num_tokens(start_values, ','), ' ''start'' value[s] provided; chosen index has ', columns_count, ' columns'));
    end if;
  end if;
  if stop_values is not null then
    if columns_count = get_num_tokens(stop_values, ',') then
      select 
          group_concat('set ', max_variable_name, ' := ', QUOTE(split_token(stop_values, ',', column_order)), ';' order by column_order separator '')
        from
          _split_column_names_table
        into query;
      call exec(query);
      set manual_min_max_params_used := true;
    else
      call _throw_script_error(id_from, CONCAT(get_num_tokens(stop_values, ','), ' ''stop'' value[s] provided; chosen index has ', columns_count, ' columns'));
    end if;
  end if;
  if manual_min_max_params_used then

    
    
    select 
      CONCAT(
        'SELECT (',
          GROUP_CONCAT(min_variable_name order by column_order), ') > (', GROUP_CONCAT(max_variable_name order by column_order),
        ') INTO @_split_is_empty_range_result'
        )
      from _split_column_names_table
      into query;

    call exec_single(query);
    
    if @_split_is_empty_range_result then
      set is_empty_range := true;
    end if;
    
  end if;

  
  select 
    CONCAT(
      'SELECT (',
      GROUP_CONCAT(
        min_variable_name, ' IS NULL'
        separator ' AND '
      ),
      ') INTO @_split_is_empty_range_result'
      )
    from _split_column_names_table
    into query;

  call exec_single(query);
  if @_split_is_empty_range_result then
    set is_empty_range := true;
  end if;

  
  set @_query_script_split_min := TRIM(TRAILING ',' FROM CONCAT_WS(',',
  	IF(columns_count >= 1, QUOTE((SELECT @_split_column_variable_min_1)), ''),
  	IF(columns_count >= 2, QUOTE((SELECT @_split_column_variable_min_2)), ''),
  	IF(columns_count >= 3, QUOTE((SELECT @_split_column_variable_min_3)), ''),
  	IF(columns_count >= 4, QUOTE((SELECT @_split_column_variable_min_4)), ''),
  	IF(columns_count >= 5, QUOTE((SELECT @_split_column_variable_min_5)), ''),
  	IF(columns_count >= 6, QUOTE((SELECT @_split_column_variable_min_6)), ''),
  	IF(columns_count >= 7, QUOTE((SELECT @_split_column_variable_min_7)), ''),
  	IF(columns_count >= 8, QUOTE((SELECT @_split_column_variable_min_8)), ''),
  	IF(columns_count >= 9, QUOTE((SELECT @_split_column_variable_min_9)), '')
  ));
  set @_query_script_split_max := TRIM(TRAILING ',' FROM CONCAT_WS(',',
  	IF(columns_count >= 1, QUOTE((SELECT @_split_column_variable_max_1)), ''),
  	IF(columns_count >= 2, QUOTE((SELECT @_split_column_variable_max_2)), ''),
  	IF(columns_count >= 3, QUOTE((SELECT @_split_column_variable_max_3)), ''),
  	IF(columns_count >= 4, QUOTE((SELECT @_split_column_variable_max_4)), ''),
  	IF(columns_count >= 5, QUOTE((SELECT @_split_column_variable_max_5)), ''),
  	IF(columns_count >= 6, QUOTE((SELECT @_split_column_variable_max_6)), ''),
  	IF(columns_count >= 7, QUOTE((SELECT @_split_column_variable_max_7)), ''),
  	IF(columns_count >= 8, QUOTE((SELECT @_split_column_variable_max_8)), ''),
  	IF(columns_count >= 9, QUOTE((SELECT @_split_column_variable_max_9)), '')
  ));
  
end ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `_split_assign_next_range_start_variables` */;
DELIMITER ;;
CREATE DEFINER=`percona`@`localhost` PROCEDURE `_split_assign_next_range_start_variables`()
    READS SQL DATA
    SQL SECURITY INVOKER
begin
  declare queries text default NULL;

  select 
    GROUP_CONCAT(
      'set ', range_start_variable_name, ' := ', range_end_variable_name, ';'
      separator ''
    )
    from _split_column_names_table
    into queries;
    
  call exec(queries);
end ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `_split_assign_range_end_variables` */;
DELIMITER ;;
CREATE DEFINER=`percona`@`localhost` PROCEDURE `_split_assign_range_end_variables`(
  split_table_schema varchar(128),
  split_table_name varchar(128),
  split_range_size int unsigned
  )
    READS SQL DATA
    SQL SECURITY INVOKER
begin
  declare query text default NULL;
  declare column_names text default _split_get_columns_names();
  
  declare columns_order_ascending_clause text default _split_get_columns_order_ascending_clause();
  declare range_end_variables_names text default _split_get_range_end_variables_names();
  
  declare columns_order_descending_clause text default _split_get_columns_order_descending_clause();
  declare max_variables_names text default _split_get_max_variables_names();
  
  declare columns_count tinyint unsigned default _split_get_columns_count();
  
  declare as_of_range_start_comparison_clause text default _split_get_columns_comparison_clause('>', 'range_start', _split_is_first_step(), columns_count);
  declare limit_by_max_comparison_clause text default _split_get_columns_comparison_clause('<', 'max', true, columns_count);

  set query := CONCAT(
    'select ', column_names, ' from (',
      'select ', column_names, ' from ',
      mysql_qualify(split_table_schema), '.', mysql_qualify(split_table_name), 
      ' where ', as_of_range_start_comparison_clause,
      ' and ', limit_by_max_comparison_clause,
      ' order by ', columns_order_ascending_clause,
      ' limit ', split_range_size,
    ') sel_split_range ',
    ' order by ', columns_order_descending_clause,
    ' limit 1 ',
    ' into ', range_end_variables_names
  );
  call exec_single(query);
end ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `_split_cleanup_dependency_tables` */;
DELIMITER ;;
CREATE DEFINER=`percona`@`localhost` PROCEDURE `_split_cleanup_dependency_tables`()
    MODIFIES SQL DATA
    SQL SECURITY INVOKER
    COMMENT 'drop temporary tables'
begin
  drop temporary table if exists _split_unique_key_columns;
  drop temporary table if exists _split_i_s_columns;
  drop temporary table if exists _split_candidate_keys;
  drop temporary table if exists _split_candidate_keys_recommended;
end ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `_split_deduce_columns` */;
DELIMITER ;;
CREATE DEFINER=`percona`@`localhost` PROCEDURE `_split_deduce_columns`(split_table_schema varchar(128), split_table_name varchar(128), requested_index_name varchar(128))
    MODIFIES SQL DATA
    SQL SECURITY INVOKER
    COMMENT 'split values by columns...'
begin
  declare split_column_names varchar(2048) default NULL;
  declare split_num_column tinyint unsigned;

  call _split_generate_dependency_tables(split_table_schema, split_table_name, requested_index_name);
  
  SELECT 
      column_names, count_column_in_index, index_name
    FROM 
      _split_candidate_keys_recommended 
    WHERE 
      table_schema = split_table_schema AND table_name = split_table_name 
    INTO split_column_names, split_num_column, @_query_script_split_index_name
  ;
    
  call _split_cleanup_dependency_tables();

  if (requested_index_name is not null) and ((requested_index_name = @_query_script_split_index_name) IS NOT TRUE) then
    call throw(CONCAT('split: index: ', requested_index_name, ' requested, but not found/not unique'));
  end if;
  if split_column_names is null then
    call throw(CONCAT('split: no key or definition found for: ', split_table_schema, '.', split_table_name));
  end if;

  
  
  delete from _split_column_names_table;
  insert into _split_column_names_table
    select
      _get_server_id(),
      CONNECTION_ID(),
      n,
      split_table_name,
      @_query_script_split_index_name,
      split_token(split_column_names, ',', n),
      CONCAT('@_split_column_variable_min_', n),
      CONCAT('@_split_column_variable_max_', n),
      CONCAT('@_split_column_variable_range_start_', n),
      CONCAT('@_split_column_variable_range_end_', n)
    from
      numbers
    where 
      n between 1 and split_num_column
  ;
  
  select
    group_concat(mysql_qualify(column_name) order by column_order)
  from
    _split_column_names_table
  into
    @_query_script_split_columns;    
end ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `_split_generate_dependency_tables` */;
DELIMITER ;;
CREATE DEFINER=`percona`@`localhost` PROCEDURE `_split_generate_dependency_tables`(split_table_schema varchar(128), split_table_name varchar(128), requested_index_name varchar(128))
    MODIFIES SQL DATA
    SQL SECURITY INVOKER
    COMMENT 'analyze recommended keys'
begin
  declare split_column_names varchar(2048) default NULL;
  declare split_num_column tinyint unsigned;
  
  drop temporary table if exists _split_unique_key_columns;
  create temporary table _split_unique_key_columns engine=MyISAM
   SELECT
      TABLE_SCHEMA,
      TABLE_NAME,
      INDEX_NAME,
      COLUMN_NAME,
      SEQ_IN_INDEX,
      (NULLABLE = 'YES') AS has_nullable,
      (INDEX_NAME = 'PRIMARY') AS is_primary
    FROM INFORMATION_SCHEMA.STATISTICS
    WHERE
      TABLE_SCHEMA = split_table_schema
      AND TABLE_NAME = split_table_name
      AND NON_UNIQUE=0
  ;
  
  drop temporary table if exists _split_i_s_columns;
  create temporary table _split_i_s_columns engine=MyISAM
   SELECT
      TABLE_SCHEMA,
      TABLE_NAME,
      COLUMN_NAME,
      CASE LOWER(DATA_TYPE)
          WHEN 'tinyint' THEN 1
          WHEN 'smallint' THEN 2
          WHEN 'mediumint' THEN 4
          WHEN 'int' THEN 4
          WHEN 'bigint' THEN 8
          WHEN 'timestamp' THEN 4
          WHEN 'time' THEN 4
          WHEN 'date' THEN 4
          WHEN 'datetime' THEN 8
          WHEN 'float' THEN 4
          WHEN 'double' THEN 8
          WHEN 'decimal' THEN CEILING(NUMERIC_PRECISION/2)
          WHEN 'bit' THEN CEILING(NUMERIC_PRECISION/8)
          WHEN 'set' THEN 8
          WHEN 'enum' THEN 8
          WHEN 'year' THEN 2
          WHEN 'char' THEN CHARACTER_OCTET_LENGTH
          WHEN 'varchar' THEN CHARACTER_OCTET_LENGTH
          WHEN 'binary' THEN CHARACTER_OCTET_LENGTH
          WHEN 'varbinary' THEN CHARACTER_OCTET_LENGTH
          WHEN 'tinytext' THEN CHARACTER_OCTET_LENGTH
          WHEN 'text' THEN CHARACTER_OCTET_LENGTH
          WHEN 'mediumtext' THEN CHARACTER_OCTET_LENGTH
          WHEN 'longtext' THEN CHARACTER_OCTET_LENGTH
          WHEN 'tinyblob' THEN CHARACTER_OCTET_LENGTH
          WHEN 'blob' THEN CHARACTER_OCTET_LENGTH
          WHEN 'mediumblob' THEN CHARACTER_OCTET_LENGTH
          WHEN 'longblob' THEN CHARACTER_OCTET_LENGTH
          ELSE 255
        END AS column_type_length
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE
      TABLE_SCHEMA = split_table_schema
      AND TABLE_NAME = split_table_name
  ;
  
  drop temporary table if exists _split_candidate_keys;
  create temporary table _split_candidate_keys engine=MyISAM
   SELECT
      TABLE_SCHEMA,
      TABLE_NAME,
      INDEX_NAME,
      MAX(has_nullable) AS has_nullable,
      MAX(is_primary) AS is_primary,
      COUNT(*) AS count_column_in_index,
      GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX ASC) AS COLUMN_NAMES,
      SUM(column_type_length) AS covered_columns_length
    FROM 
      _split_unique_key_columns
      JOIN _split_i_s_columns USING(TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME)
    GROUP BY TABLE_SCHEMA, TABLE_NAME, INDEX_NAME
  ;

  drop temporary table if exists _split_candidate_keys_recommended;
  create temporary table _split_candidate_keys_recommended engine=MyISAM
    SELECT
      *
    FROM 
      _split_candidate_keys
    ORDER BY   
      (INDEX_NAME = requested_index_name) IS TRUE DESC,
      has_nullable ASC, covered_columns_length ASC, is_primary DESC
    LIMIT 1
  ;
  
end ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `_split_get_step_comparison_clause` */;
DELIMITER ;;
CREATE DEFINER=`percona`@`localhost` PROCEDURE `_split_get_step_comparison_clause`(
    out comparison_clause text charset utf8
  )
    READS SQL DATA
    DETERMINISTIC
    SQL SECURITY INVOKER
BEGIN	
  declare columns_count tinyint unsigned default _split_get_columns_count();
  declare range_start_comparison_clause text default _split_get_columns_comparison_clause('>', 'range_start', @_split_is_first_step_flag, columns_count);
  declare range_end_comparison_clause text default _split_get_columns_comparison_clause('<', 'range_end', true, columns_count);

  set comparison_clause := CONCAT(
  	'(', range_start_comparison_clause, ' AND ', range_end_comparison_clause, ')'
  );
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `_split_init_variables` */;
DELIMITER ;;
CREATE DEFINER=`percona`@`localhost` PROCEDURE `_split_init_variables`()
    READS SQL DATA
    SQL SECURITY INVOKER
begin
  declare queries text default NULL;

  select 
    GROUP_CONCAT(
      'set ', min_variable_name, ' := NULL; ',
      'set ', max_variable_name, ' := NULL; ',
      'set ', range_start_variable_name, ' := NULL; ',
      'set ', range_end_variable_name, ' := NULL; '
      separator ''
    )
    from _split_column_names_table
    into queries;
    
  call exec(queries);
end ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `_split_is_range_start_end_identical` */;
DELIMITER ;;
CREATE DEFINER=`percona`@`localhost` PROCEDURE `_split_is_range_start_end_identical`(out is_identical tinyint unsigned)
    READS SQL DATA
    SQL SECURITY INVOKER
begin
  declare query text default NULL;

  declare range_start_variables_names text default _split_get_range_start_variables_names();
  declare range_end_variables_names text default _split_get_range_end_variables_names();

  set query := CONCAT(
    'select (', range_start_variables_names, ') = (', range_end_variables_names, ') into @_split_is_identical'
  );
  call exec_single(query);
  set is_identical := @_split_is_identical;
end ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `_split_is_range_start_overflow` */;
DELIMITER ;;
CREATE DEFINER=`percona`@`localhost` PROCEDURE `_split_is_range_start_overflow`(out is_overflow tinyint unsigned)
    READS SQL DATA
    SQL SECURITY INVOKER
begin
  declare query text default NULL;

  declare range_start_variables_names text default _split_get_range_start_variables_names();
  declare max_variables_names text default _split_get_max_variables_names();

  set query := CONCAT(
    'select (', range_start_variables_names, ') >= (', max_variables_names, ') into @_split_is_overflow'
  );
  call exec_single(query);
  set is_overflow := @_split_is_overflow;
end ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `_split_set_step_clause_and_ranges_local_variables` */;
DELIMITER ;;
CREATE DEFINER=`percona`@`localhost` PROCEDURE `_split_set_step_clause_and_ranges_local_variables`(
    in comparison_clause text charset utf8
  )
    READS SQL DATA
    DETERMINISTIC
    SQL SECURITY INVOKER
BEGIN
  declare columns_count tinyint unsigned default _split_get_columns_count();

  set @query_script_split_comparison_clause := comparison_clause;
  set @query_script_split_comparison_clause := REPLACE(@query_script_split_comparison_clause, '@_split_column_variable_range_start_1', QUOTE((SELECT @_split_column_variable_range_start_1)));
  set @query_script_split_comparison_clause := REPLACE(@query_script_split_comparison_clause, '@_split_column_variable_range_start_2', QUOTE((SELECT @_split_column_variable_range_start_2)));
  set @query_script_split_comparison_clause := REPLACE(@query_script_split_comparison_clause, '@_split_column_variable_range_start_3', QUOTE((SELECT @_split_column_variable_range_start_3)));
  set @query_script_split_comparison_clause := REPLACE(@query_script_split_comparison_clause, '@_split_column_variable_range_start_4', QUOTE((SELECT @_split_column_variable_range_start_4)));
  set @query_script_split_comparison_clause := REPLACE(@query_script_split_comparison_clause, '@_split_column_variable_range_start_5', QUOTE((SELECT @_split_column_variable_range_start_5)));
  set @query_script_split_comparison_clause := REPLACE(@query_script_split_comparison_clause, '@_split_column_variable_range_start_6', QUOTE((SELECT @_split_column_variable_range_start_6)));
  set @query_script_split_comparison_clause := REPLACE(@query_script_split_comparison_clause, '@_split_column_variable_range_start_7', QUOTE((SELECT @_split_column_variable_range_start_7)));
  set @query_script_split_comparison_clause := REPLACE(@query_script_split_comparison_clause, '@_split_column_variable_range_start_8', QUOTE((SELECT @_split_column_variable_range_start_8)));
  set @query_script_split_comparison_clause := REPLACE(@query_script_split_comparison_clause, '@_split_column_variable_range_start_9', QUOTE((SELECT @_split_column_variable_range_start_9)));
  set @query_script_split_comparison_clause := REPLACE(@query_script_split_comparison_clause, '@_split_column_variable_range_end_1', QUOTE((SELECT @_split_column_variable_range_end_1)));
  set @query_script_split_comparison_clause := REPLACE(@query_script_split_comparison_clause, '@_split_column_variable_range_end_2', QUOTE((SELECT @_split_column_variable_range_end_2)));
  set @query_script_split_comparison_clause := REPLACE(@query_script_split_comparison_clause, '@_split_column_variable_range_end_3', QUOTE((SELECT @_split_column_variable_range_end_3)));
  set @query_script_split_comparison_clause := REPLACE(@query_script_split_comparison_clause, '@_split_column_variable_range_end_4', QUOTE((SELECT @_split_column_variable_range_end_4)));
  set @query_script_split_comparison_clause := REPLACE(@query_script_split_comparison_clause, '@_split_column_variable_range_end_5', QUOTE((SELECT @_split_column_variable_range_end_5)));
  set @query_script_split_comparison_clause := REPLACE(@query_script_split_comparison_clause, '@_split_column_variable_range_end_6', QUOTE((SELECT @_split_column_variable_range_end_6)));
  set @query_script_split_comparison_clause := REPLACE(@query_script_split_comparison_clause, '@_split_column_variable_range_end_7', QUOTE((SELECT @_split_column_variable_range_end_7)));
  set @query_script_split_comparison_clause := REPLACE(@query_script_split_comparison_clause, '@_split_column_variable_range_end_8', QUOTE((SELECT @_split_column_variable_range_end_8)));
  set @query_script_split_comparison_clause := REPLACE(@query_script_split_comparison_clause, '@_split_column_variable_range_end_9', QUOTE((SELECT @_split_column_variable_range_end_9)));
  
  set @query_script_split_range_start_snapshot := TRIM(TRAILING ',' FROM CONCAT_WS(',',
  	IF(columns_count >= 1, QUOTE((SELECT @_split_column_variable_range_start_1)), ''),
  	IF(columns_count >= 2, QUOTE((SELECT @_split_column_variable_range_start_2)), ''),
  	IF(columns_count >= 3, QUOTE((SELECT @_split_column_variable_range_start_3)), ''),
  	IF(columns_count >= 4, QUOTE((SELECT @_split_column_variable_range_start_4)), ''),
  	IF(columns_count >= 5, QUOTE((SELECT @_split_column_variable_range_start_5)), ''),
  	IF(columns_count >= 6, QUOTE((SELECT @_split_column_variable_range_start_6)), ''),
  	IF(columns_count >= 7, QUOTE((SELECT @_split_column_variable_range_start_7)), ''),
  	IF(columns_count >= 8, QUOTE((SELECT @_split_column_variable_range_start_8)), ''),
  	IF(columns_count >= 9, QUOTE((SELECT @_split_column_variable_range_start_9)), '')
  ));
  set @query_script_split_range_end_snapshot := TRIM(TRAILING ',' FROM CONCAT_WS(',',
  	IF(columns_count >= 1, QUOTE((SELECT @_split_column_variable_range_end_1)), ''),
  	IF(columns_count >= 2, QUOTE((SELECT @_split_column_variable_range_end_2)), ''),
  	IF(columns_count >= 3, QUOTE((SELECT @_split_column_variable_range_end_3)), ''),
  	IF(columns_count >= 4, QUOTE((SELECT @_split_column_variable_range_end_4)), ''),
  	IF(columns_count >= 5, QUOTE((SELECT @_split_column_variable_range_end_5)), ''),
  	IF(columns_count >= 6, QUOTE((SELECT @_split_column_variable_range_end_6)), ''),
  	IF(columns_count >= 7, QUOTE((SELECT @_split_column_variable_range_end_7)), ''),
  	IF(columns_count >= 8, QUOTE((SELECT @_split_column_variable_range_end_8)), ''),
  	IF(columns_count >= 9, QUOTE((SELECT @_split_column_variable_range_end_9)), '')
  ));
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `_take_local_variables_snapshot` */;
DELIMITER ;;
CREATE DEFINER=`percona`@`localhost` PROCEDURE `_take_local_variables_snapshot`(
   expanded_variables text charset utf8
)
    MODIFIES SQL DATA
    DETERMINISTIC
    SQL SECURITY INVOKER
    COMMENT 'Declares local variables'
main_body: begin
  declare num_variables int unsigned;
  declare variable_index int unsigned default 0;
  declare local_variable varchar(65) charset ascii;
  declare user_defined_variable_name varchar(65) charset ascii;
  declare snapshot_query text charset ascii;
  
  set num_variables := get_num_tokens(expanded_variables, ',');
  set variable_index := 1;
  while variable_index <= num_variables do
    set local_variable := split_token(expanded_variables, ',', variable_index);
    SELECT mapped_user_defined_variable_name FROM _qs_variables WHERE variable_name = local_variable INTO user_defined_variable_name;
    
    set snapshot_query := CONCAT('UPDATE _qs_variables SET value_snapshot = ', user_defined_variable_name, ' WHERE variable_name = ', QUOTE(local_variable));
    call exec_single(snapshot_query);
    
    set variable_index := variable_index + 1;
  end while;
end ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `_throttle_script` */;
DELIMITER ;;
CREATE DEFINER=`percona`@`localhost` PROCEDURE `_throttle_script`(throttle_ratio DOUBLE)
    READS SQL DATA
    DETERMINISTIC
    SQL SECURITY INVOKER
begin
  set @_common_schema_script_throttle_sysdate := SYSDATE();
  
  set @_common_schema_script_throttle_chunk_start := IFNULL(@_common_schema_script_throttle_chunk_start, @_common_schema_script_start_timestamp);
  set @_common_schema_script_throttle_timediff := TIMESTAMPDIFF(SECOND, @_common_schema_script_throttle_chunk_start, @_common_schema_script_throttle_sysdate);
  set @_common_schema_script_throttle_sleep_time := @_common_schema_script_throttle_timediff * throttle_ratio;
  
  if @_common_schema_script_throttle_sleep_time > 0 then
    DO SLEEP(@_common_schema_script_throttle_sleep_time);
    set @_common_schema_script_throttle_chunk_start := SYSDATE();
  end if;
end ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `_throw_script_error` */;
DELIMITER ;;
CREATE DEFINER=`percona`@`localhost` PROCEDURE `_throw_script_error`(
   in id_from      int unsigned,
   in message varchar(1024) charset utf8
)
    READS SQL DATA
    DETERMINISTIC
    SQL SECURITY INVOKER
    COMMENT 'Raises error and quites from script'
main_body: begin
    declare full_message varchar(2048);
    declare error_pos int unsigned;

    SELECT LEFT(GROUP_CONCAT(token ORDER BY id SEPARATOR ''), 80), SUBSTRING_INDEX(GROUP_CONCAT(start ORDER BY id), ',', 1) FROM _sql_tokens WHERE id >= id_from INTO full_message, error_pos;
    
    set full_message := CONCAT('QueryScript error: [', message, '] at ', error_pos, ': "', full_message, '"');
    call throw(full_message);
end ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `_validate_statement_end` */;
DELIMITER ;;
CREATE DEFINER=`percona`@`localhost` PROCEDURE `_validate_statement_end`(
   in   id_from      int unsigned,
   in   id_to      int unsigned,
   out  id_end_statement int unsigned,
   out	statement_delimiter_found tinyint unsigned
)
    READS SQL DATA
    DETERMINISTIC
    SQL SECURITY INVOKER
    COMMENT 'Validates delimiter or end of block'
main_body: begin
  declare state_end_statement text charset utf8 default NULL;
  
  set id_end_statement := NULL;
  
  
  SELECT id, state FROM _sql_tokens WHERE id > id_from AND id <= id_to AND state IN ('statement delimiter', 'left braces') ORDER BY id ASC LIMIT 1 INTO id_end_statement, state_end_statement;
  if state_end_statement = 'left braces' then
    call _throw_script_error(id_from, 'Missing '';'' statement delimiter');
  end if; 
  if id_end_statement IS NULL then
    
    set id_end_statement := id_to;
  end if;
  set statement_delimiter_found := (state_end_statement = 'statement delimiter');
end ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `_wrap_select_list_columns` */;
DELIMITER ;;
CREATE DEFINER=`percona`@`localhost` PROCEDURE `_wrap_select_list_columns`(
    inout p_text        text    
,   in p_column_count   int     
,   out p_error         text    
)
my_proc: begin
    declare v_from, v_old_from int unsigned;
    declare v_token text charset utf8;
    declare v_level int unsigned;
    declare v_state varchar(32);
    declare v_whitespace varchar(1) default '';
    declare v_done bool default FALSE;
    declare v_statement text;
    declare v_expression text default '';
    declare v_column_number int unsigned default 0;
    declare v_prev_tokens text default '';
    declare v_token_separator char(1) default '~';
    declare v_token_separator_esc char(1) default '_';
    declare v_handle text;
    declare v_substr_length int unsigned default 0;
    set @_wrap_select_num_original_columns := 0;
    
    my_main: begin
    
    my_loop: repeat 
        set v_old_from = v_from;
        call _get_sql_token(p_text, v_from, v_level, v_token, 'sql', v_state);
        case 
            when v_state in ('whitespace', 'single line comment', 'multi line comment', 'conditional comment') then
                iterate my_loop;
            when v_state = 'alpha' and v_token = 'select' then
                set v_statement = substr(p_text, 1, v_from);
                leave my_loop;
            else 
                set p_error = 'No Select found';
                leave my_proc;
        end case;
    until 
        v_old_from = v_from
    end repeat;
    
    
    columns_loop: repeat 
        set v_old_from = v_from;
        call _get_sql_token(p_text, v_from, v_level, v_token, 'sql', v_state);
        if v_state = 'error' then
            set p_error = 'Tokenizer returned error state';
            leave my_main;
        elseif (v_column_number = 0) and ((v_state, v_token) = ('alpha', 'distinct')) then
            set v_statement = concat(v_statement, 'distinct ');
        elseif (v_column_number < p_column_count) or (p_column_count = 0)  then
            if  v_level = 0 and (
                (v_state, v_token) in (
                    ('alpha', 'from')
                ,   ('comma', ',')
                ) 
                or v_old_from = v_from
            ) then
                
                
                if v_token = 'from' 
                and substr(v_prev_tokens, character_length(v_prev_tokens) + 1 - character_length(v_token_separator)) = v_token_separator then
                    set v_prev_tokens = substr(v_prev_tokens, 1, character_length(v_prev_tokens) - character_length(v_token_separator));
                end if;
                
                if character_length(v_prev_tokens) - character_length(replace(v_prev_tokens, v_token_separator, '')) > 1 then
                    
                    set v_handle = substring_index(substring_index(v_prev_tokens, v_token_separator, -2), v_token_separator, 1)
                    
                    ,   v_substr_length = character_length(v_expression)
                    
                    ,   v_substr_length = v_substr_length - case 
                            when UPPER(v_handle) = 'AS' then    
                                2 + character_length(substring_index(v_expression, v_handle, -1))
                            when coalesce(v_handle, '') not in (  
                                '', 'AND', 'BINARY', 'COLLATE', 'DIV', 'ESCAPE', 'IS', 'LIKE', 'MOD', 'NOT', 'OR', 'REGEXP', 'RLIKE', 'XOR'
                            ,   '+', '-', '/', '*', '%'
                            ,   '||', '&&', '!' 
                            ,   '<', '<=', '=>', '>', '<=>', '=', '!=', ':='
                            ,   '|', '&', '~', '^', '<<', '>>'
                            ,   '.'
                            ) and not (   
                                    v_handle = '_bin' 
                                or  v_handle LIKE '_%'
                                and exists (
                                    select  null 
                                    from    information_schema.character_sets 
                                    where   character_set_name = substring(v_handle, 2)
                                )
                            ) then
                                1+character_length(substring_index(v_prev_tokens, v_token_separator, -1))
                            else 0
                        end
                    
                    ,   v_expression = substring(v_expression, 1, v_substr_length)
                    ;
                end if;
                
                set v_statement = concat(
                        v_statement
                    ,   if (v_column_number, ', ', '')
                    ,   TRIM(v_expression)
                    ,   ' AS col', v_column_number + 1  
                    )
                ,   v_column_number = v_column_number + 1
                ,   v_expression = ''
                ,   v_prev_tokens = ''
                ;
            else
                set v_expression = concat(v_expression, v_token);
                set v_prev_tokens = concat(
                    v_prev_tokens
                ,   if( v_level != 0
                    or  v_state not in (
                            'whitespace'
                        ,   'multi line comment'
                        ,   'single line comment'
                        )
                    ,   concat(
                            if(v_level, '', v_token_separator)
                        ,   replace(v_token, v_token_separator, v_token_separator_esc)
                        )
                    ,   ''
                    )
                );
            end if;            
        end if;
    until 
        v_old_from = v_from or v_token = 'from'
    end repeat;

    
    set @_wrap_select_num_original_columns := v_column_number;
    while v_column_number < p_column_count do
        set v_column_number = v_column_number + 1
        ,   v_statement = concat(v_statement, ', NULL as col', v_column_number)
        ;
    end while;

    end my_main;
    
    set p_text= concat(
                    v_statement
                ,   if(v_token = 'from', ' from', '')
                ,   substr(p_text, v_from)
                );
end ;;
DELIMITER ;

--
-- Current Database: `cr_debug`
--

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `cr_debug` /*!40100 DEFAULT CHARACTER SET utf8 */;

USE `cr_debug`;

--
