--source include/have_innodb.inc
--source include/have_falcon.inc
let $engine_type = Falcon;
let $other_engine_type = MyISAM;
let $procedure_loops = 1030;

#
# Mantis bug #138: Error "Can't find record"
#   Slightly modified not to output randon numbers.
#
# Note: The actual test is to loop 1 million times. I reduced it 
# to 100000, but it takes still to much time on our internal servers.
# Therefore the loop is reduced to 10000. However, we should test it
# with 1 million from time to time.
#
--echo *** Bug #138 ***
--disable_warnings
DROP TABLE IF EXISTS t1;
DROP TABLE IF EXISTS t1_default;
DROP TABLE IF EXISTS t1_conn1;
DROP PROCEDURE IF EXISTS p1;
--enable_warnings

--echo # Establish connection conn1 (user = root)
connect (conn1,localhost,root,,);
SET @@autocommit = 1;

--echo # Switch to connection default
connection default;
SET @@autocommit = 1;
eval CREATE TABLE t1 (a int(11), KEY a (a)) ENGINE = $engine_type;
eval CREATE TABLE t1_default
(id INTEGER AUTO_INCREMENT, event_time TIME, event VARCHAR(50), PRIMARY KEY(id))
ENGINE = $other_engine_type;
CREATE TABLE t1_conn1 LIKE t1_default;

delimiter //;
--replace_result $procedure_loops <procedure_loops>
eval CREATE PROCEDURE p1 (my_connection VARCHAR(18))
begin
declare v1 int default 0;
declare v2 int;
declare continue handler for 1020
begin
  IF my_connection = 'default' THEN
    INSERT INTO t1_default SET event = '         got error 1020', event_time = CURTIME();
  END IF;
  IF my_connection = 'conn1' THEN
    INSERT INTO t1_conn1   SET event = '         got error 1020', event_time = CURTIME();
  END IF;
end;
declare continue handler for 1213
begin
  IF my_connection = 'default' THEN
    INSERT INTO t1_default SET event = '         got error 1213', event_time = CURTIME();
  END IF;
  IF my_connection = 'conn1' THEN
    INSERT INTO t1_conn1   SET event = '         got error 1213', event_time = CURTIME();
  END IF;
end;
while v1 < $procedure_loops do
  /* SELECT 'insert', v1; */
  INSERT INTO t1 VALUES (v1);
  IF my_connection = 'default' THEN
    INSERT INTO t1_default SET event = CONCAT(CAST(v1 AS CHAR),' inserted'), event_time = CURTIME();
  END IF;
  IF my_connection = 'conn1' THEN
    INSERT INTO t1_conn1   SET event = CONCAT(CAST(v1 AS CHAR),' inserted'), event_time = CURTIME();
  END IF;
  SET v2 = rand() * $procedure_loops;
  UPDATE t1 SET a = v2 WHERE a = v1;
  IF my_connection = 'default' THEN
    INSERT INTO t1_default SET event = CONCAT(CAST(v1 AS CHAR),' updated to ', CAST(v2 AS CHAR)), event_time = CURTIME();
  END IF;
  IF my_connection = 'conn1' THEN
    INSERT INTO t1_conn1   SET event = CONCAT(CAST(v1 AS CHAR),' updated to ', CAST(v2 AS CHAR)), event_time = CURTIME();
  END IF;
  /* SELECT ' update', v1; */
  SET v1 = v1 + 1;
  if v1 mod 50 = 0 then
    /* SELECT ' delete'; */
    DELETE FROM t1;
    IF my_connection = 'default' THEN
      INSERT INTO t1_default SET event = 'delete all', event_time = CURTIME();
    END IF;
    IF my_connection = 'conn1' THEN
      INSERT INTO t1_conn1   SET event = 'delete all', event_time = CURTIME();
    END IF;
  end if;
  end while;
  IF my_connection = 'default' THEN
    INSERT INTO t1_default SET event = 'end of while loop', event_time = CURTIME();
  END IF;
  IF my_connection = 'conn1' THEN
    INSERT INTO t1_conn1   SET event = 'end of while loop', event_time = CURTIME();
  END IF;
end//
delimiter ;//

--echo # default: Sent call p1() to the server but do not pull the results
--send call p1('default')

--echo # Switch to connection conn1
connection conn1;
call p1('conn1');

--echo # Switch to connection default
connection default;
--echo # default: Pull the results of the preceeding call p1()
reap;
--echo # default: Sent call p1() to the server but do not pull the results
--send call p1('default')

--echo # Switch to connection conn1
connection conn1;
call p1('conn1');

--echo # Switch to connection default
connection default;
--echo # default: Pull the results of the preceeding call p1()
reap;

SELECT * FROM t1_default WHERE event like '         got error 1%';
SELECT * FROM t1_conn1   WHERE event like '         got error 1%';

# Final cleanup.
--echo # Disconnect conn1
disconnect conn1;
DROP TABLE t1;
DROP PROCEDURE p1;