Using trigger to update table with multiple inserts is off by one.

[jycor]

Steps to reproduce:

tmp_db> create table mytbl (i int);
tmp_db> create table seqtbl (x int);
tmp_db> insert into seqtbl values (1000);
Query OK, 1 row affected
tmp_db> create trigger trig before insert on mytbl for each row begin update seqtbl set x = (select count(i) from mytbl); end;
tmp_db> insert into mytbl values (1), (2), (3), (4), (5);
Query OK, 5 rows affected
tmp_db> select * from mytbl;
+---+
| i |
+---+
| 2 |
| 5 |
| 3 |
| 1 |
| 4 |
+---+
tmp_db> select * from seqtbl;
+---+
| x |
+---+
| 3 |
+---+

seqtbl should have 4 instead of 3.

When stepping through this issue, the first insert updates seqtbl correctly, but from the second one onwards, they are all one behind the correct value. Shouldn't be an issue with caching, as this was discovered after fix that disables subquerying for trigger blocks. I think it has something to do with not flushing enough.