drop database if exists bug26503;
create database bug26503 character set utf8 collate utf8_unicode_ci;
use bug26503;
set charset utf8;

create table users (
  id		int unsigned not null auto_increment, unique(id),
  username	char(32) not null,
  primary key(username)
) engine=InnoDB;

create table transactions (
  id		bigint unsigned not null auto_increment, unique(id),
  type		tinyint unsigned not null,
  status	tinyint unsigned not null,
  updated	timestamp not null,
  entered	timestamp not null,
  user		int unsigned not null,
  amount	decimal not null,
  key(entered desc),
  key(user),
  foreign key(user) references users (id) on delete cascade on update cascade
) engine=InnoDB;

delimiter //

create trigger trx_bins before insert on transactions
for each row
begin
  set new.status=2;
  if new.type=2 then
    set new.amount=(select amount from transactions where type=1 and user=new.user order by entered desc limit 1);
  end if;
end;//

create procedure proc1(out tid bigint unsigned)
language sql
not deterministic
modifies sql data
sql security definer
begin
  set tid=null;
retry:
  repeat
    begin
      declare continue handler for 1205 iterate retry;
      declare continue handler for sqlstate '40001' iterate retry;
      start transaction;
      insert into transactions (type,entered,user,amount) values (20,null,1,0);
      set tid=last_insert_id();
      commit;
    end;
  until true end repeat retry;
end;//

delimiter ;

insert into users set id=1,username='test';
insert into transactions set type=1,entered=null,user=1,amount=0;