=== modified file 'mysql-test/suite/ndb/r/ndb_auto_increment.result' --- mysql-test/suite/ndb/r/ndb_auto_increment.result 2008-01-25 09:43:30 +0000 +++ mysql-test/suite/ndb/r/ndb_auto_increment.result 2009-09-01 10:09:00 +0000 @@ -3,6 +3,9 @@ DROP TABLE IF EXISTS t1; set @old_auto_increment_offset = @@session.auto_increment_offset; set @old_auto_increment_increment = @@session.auto_increment_increment; set @old_ndb_autoincrement_prefetch_sz = @@session.ndb_autoincrement_prefetch_sz; +set @old_auto_increment_offset = @@session.auto_increment_offset; +set @old_auto_increment_increment = @@session.auto_increment_increment; +set @old_ndb_autoincrement_prefetch_sz = @@session.ndb_autoincrement_prefetch_sz; flush status; create table t1 (a int not null auto_increment primary key) engine ndb; insert into t1 values (NULL); @@ -443,3 +446,56 @@ set auto_increment_offset = @old_auto_in set auto_increment_increment = @old_auto_increment_increment; set ndb_autoincrement_prefetch_sz = @old_ndb_autoincrement_prefetch_sz; drop table t1; +set auto_increment_offset = @old_auto_increment_offset; +set auto_increment_increment = @old_auto_increment_increment; +set ndb_autoincrement_prefetch_sz = @old_ndb_autoincrement_prefetch_sz; +CREATE TABLE `t1` ( +`id` int(10) unsigned NOT NULL AUTO_INCREMENT, +`k` int(10) unsigned NOT NULL DEFAULT '0', +`c` char(120) NOT NULL DEFAULT '', +`pad` char(60) NOT NULL DEFAULT '', +PRIMARY KEY (`id`), +KEY `k` (`k`) +) ENGINE=ndbcluster; +CREATE TABLE `t2` ( +`evend_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, +`timestamp` int(11) NOT NULL, +`server_id` int(11) NOT NULL, +PRIMARY KEY (`evend_id`) +) ENGINE=ndbcluster; +insert into t1 values (null,1,'',''),(null,2,'',''); +CREATE TRIGGER tr1 +AFTER UPDATE ON t1 +FOR EACH ROW +BEGIN +insert into t2(timestamp, server_id) values(UNIX_TIMESTAMP(),@@global.server_id); +end; +| +CREATE TRIGGER tr1 +AFTER UPDATE ON t1 +FOR EACH ROW +BEGIN +insert into t2(timestamp, server_id) values(UNIX_TIMESTAMP(),@@global.server_id); +end; +| +update t1 set c='foobar' where id=1; +update t1 set c='foobar' where id=1; +update t1 set c='foobar' where id=1; +update t1 set c='foobar' where id=1; +update t1 set c='foobar' where id=1; +update t1 set c='foobar' where id=1; +update t1 set c='foobar' where id=1; +update t1 set c='foobar' where id=1; +select evend_id,server_id from t2 order by evend_id; +evend_id server_id +1 1 +2 2 +3 1 +4 2 +5 1 +6 2 +7 1 +8 2 +drop trigger tr1; +drop table t1, t2; +drop trigger if exists tr1; === modified file 'mysql-test/suite/ndb/r/ndb_trigger.result' --- mysql-test/suite/ndb/r/ndb_trigger.result 2009-02-01 21:05:19 +0000 +++ mysql-test/suite/ndb/r/ndb_trigger.result 2009-09-01 10:09:00 +0000 @@ -340,4 +340,68 @@ trigger_name='trg1'; trigger_name event_object_table trg1 t2 drop table t2; +create table t1(c1 int(11) not null auto_increment primary key, c2 int(11) not null) engine=ndb; +create trigger bi_t1 before insert on t1 +FOR EACH ROW BEGIN +SET new.c2 = last_insert_id(); +End +| +INSERT INTO t1 (c2) VALUES (17); +INSERT INTO t1 (c2) VALUES (17); +INSERT INTO t1 (c2) VALUES (17); +select c1 from t1 where c1=last_insert_id(); +c1 +3 +select * from t1 order by c1; +c1 c2 +1 0 +2 1 +3 2 +start transaction; +INSERT INTO t1 (c2) VALUES (17); +INSERT INTO t1 (c2) VALUES (17); +select c1 from t1 where c1=last_insert_id(); +c1 +5 +select * from t1 order by c1; +c1 c2 +1 0 +2 1 +3 2 +4 3 +5 4 +rollback; +select c1 from t1 where c1=last_insert_id(); +c1 +select * from t1 order by c1; +c1 c2 +1 0 +2 1 +3 2 +start transaction; +INSERT INTO t1 (c2) VALUES (17); +INSERT INTO t1 (c2) VALUES (17); +select c1 from t1 where c1=last_insert_id(); +c1 +7 +select * from t1 order by c1; +c1 c2 +1 0 +2 1 +3 2 +6 5 +7 6 +commit; +select c1 from t1 where c1=last_insert_id(); +c1 +7 +select * from t1 order by c1; +c1 c2 +1 0 +2 1 +3 2 +6 5 +7 6 +drop trigger bi_t1; +drop table t1; End of 5.1 tests === modified file 'mysql-test/suite/ndb/t/ndb_auto_increment.test' --- mysql-test/suite/ndb/t/ndb_auto_increment.test 2007-11-06 09:57:49 +0000 +++ mysql-test/suite/ndb/t/ndb_auto_increment.test 2009-09-01 10:09:00 +0000 @@ -6,6 +6,9 @@ connection server1; DROP TABLE IF EXISTS t1,t2; connection server2; DROP TABLE IF EXISTS t1; +set @old_auto_increment_offset = @@session.auto_increment_offset; +set @old_auto_increment_increment = @@session.auto_increment_increment; +set @old_ndb_autoincrement_prefetch_sz = @@session.ndb_autoincrement_prefetch_sz; connection server1; --enable_warnings @@ -291,3 +294,83 @@ set auto_increment_increment = @old_auto set ndb_autoincrement_prefetch_sz = @old_ndb_autoincrement_prefetch_sz; drop table t1; + +connection server2; +set auto_increment_offset = @old_auto_increment_offset; +set auto_increment_increment = @old_auto_increment_increment; +set ndb_autoincrement_prefetch_sz = @old_ndb_autoincrement_prefetch_sz; + +# bug#46712 +connection server1; + +CREATE TABLE `t1` ( + `id` int(10) unsigned NOT NULL AUTO_INCREMENT, + `k` int(10) unsigned NOT NULL DEFAULT '0', + `c` char(120) NOT NULL DEFAULT '', + `pad` char(60) NOT NULL DEFAULT '', + PRIMARY KEY (`id`), + KEY `k` (`k`) +) ENGINE=ndbcluster; + +CREATE TABLE `t2` ( + `evend_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, + `timestamp` int(11) NOT NULL, + `server_id` int(11) NOT NULL, + PRIMARY KEY (`evend_id`) +) ENGINE=ndbcluster; + + +insert into t1 values (null,1,'',''),(null,2,'',''); + +DELIMITER |; + +CREATE TRIGGER tr1 + AFTER UPDATE ON t1 + FOR EACH ROW + BEGIN + insert into t2(timestamp, server_id) values(UNIX_TIMESTAMP(),@@global.server_id); + end; +| + +DELIMITER ;| + +connection server2; + +DELIMITER |; + +CREATE TRIGGER tr1 + AFTER UPDATE ON t1 + FOR EACH ROW + BEGIN + insert into t2(timestamp, server_id) values(UNIX_TIMESTAMP(),@@global.server_id); + end; +| + +DELIMITER ;| + +connection server1; +update t1 set c='foobar' where id=1; +connection server2; +update t1 set c='foobar' where id=1; +connection server1; +update t1 set c='foobar' where id=1; +connection server2; +update t1 set c='foobar' where id=1; +connection server1; +update t1 set c='foobar' where id=1; +connection server2; +update t1 set c='foobar' where id=1; +connection server1; +update t1 set c='foobar' where id=1; +connection server2; +update t1 set c='foobar' where id=1; +connection server1; +select evend_id,server_id from t2 order by evend_id; + +drop trigger tr1; +drop table t1, t2; + +connection server2; +--disable_warnings +drop trigger if exists tr1; +--enable_warnings === modified file 'mysql-test/suite/ndb/t/ndb_trigger.test' --- mysql-test/suite/ndb/t/ndb_trigger.test 2009-02-01 21:05:19 +0000 +++ mysql-test/suite/ndb/t/ndb_trigger.test 2009-09-01 10:09:00 +0000 @@ -258,4 +258,42 @@ trigger_name='trg1'; connection server1; drop table t2; +# bug#38034 +create table t1(c1 int(11) not null auto_increment primary key, c2 int(11) not null) engine=ndb; + +delimiter |; + +create trigger bi_t1 before insert on t1 +FOR EACH ROW BEGIN + SET new.c2 = last_insert_id(); +End +| + +delimiter ;| + + +INSERT INTO t1 (c2) VALUES (17); +INSERT INTO t1 (c2) VALUES (17); +INSERT INTO t1 (c2) VALUES (17); +select c1 from t1 where c1=last_insert_id(); +select * from t1 order by c1; +start transaction; +INSERT INTO t1 (c2) VALUES (17); +INSERT INTO t1 (c2) VALUES (17); +select c1 from t1 where c1=last_insert_id(); +select * from t1 order by c1; +rollback; +select c1 from t1 where c1=last_insert_id(); +select * from t1 order by c1; +start transaction; +INSERT INTO t1 (c2) VALUES (17); +INSERT INTO t1 (c2) VALUES (17); +select c1 from t1 where c1=last_insert_id(); +select * from t1 order by c1; +commit; +select c1 from t1 where c1=last_insert_id(); +select * from t1 order by c1; +drop trigger bi_t1; +drop table t1; + --echo End of 5.1 tests === modified file 'sql/ha_ndbcluster.cc' --- sql/ha_ndbcluster.cc 2009-08-25 19:44:04 +0000 +++ sql/ha_ndbcluster.cc 2009-09-01 10:20:14 +0000 @@ -4230,6 +4230,8 @@ int ha_ndbcluster::reset() m_ignore_dup_key= FALSE; m_use_write= FALSE; m_ignore_no_key= FALSE; + m_rows_inserted= (ha_rows) 0; + m_rows_to_insert= (ha_rows) 1; m_delete_cannot_batch= FALSE; m_update_cannot_batch= FALSE; @@ -4299,10 +4301,19 @@ void ha_ndbcluster::start_bulk_insert(ha if (rows == (ha_rows) 0) { /* We don't know how many will be inserted, guess */ - m_rows_to_insert= m_autoincrement_prefetch; + m_estimated_batched_insert= TRUE; + m_rows_to_insert= + (m_autoincrement_prefetch > NDB_DEFAULT_AUTO_PREFETCH) + ? m_autoincrement_prefetch + : NDB_DEFAULT_AUTO_PREFETCH; + m_autoincrement_prefetch= m_rows_to_insert; } else - m_rows_to_insert= rows; + { + m_rows_to_insert= rows; + if (m_autoincrement_prefetch < m_rows_to_insert) + m_autoincrement_prefetch= m_rows_to_insert; + } DBUG_VOID_RETURN; } @@ -4580,11 +4591,7 @@ int ha_ndbcluster::init_handler_for_stat DBUG_ENTER("ha_ndbcluster::init_handler_for_statement"); // store thread specific data first to set the right context m_force_send= thd->variables.ndb_force_send; - m_autoincrement_prefetch= - (thd->variables.ndb_autoincrement_prefetch_sz > - NDB_DEFAULT_AUTO_PREFETCH) ? - (ha_rows) thd->variables.ndb_autoincrement_prefetch_sz - : (ha_rows) NDB_DEFAULT_AUTO_PREFETCH; + m_autoincrement_prefetch= thd->variables.ndb_autoincrement_prefetch_sz; m_thd_ndb= thd_ndb; DBUG_ASSERT(m_thd_ndb->trans); // Start of transaction @@ -6530,27 +6537,38 @@ void ha_ndbcluster::get_auto_increment(u ulonglong *nb_reserved_values) { uint cache_size; + uint remaining; + uint min_prefetch; Uint64 auto_value; THD *thd= current_thd; DBUG_ENTER("get_auto_increment"); DBUG_PRINT("enter", ("m_tabname: %s", m_tabname)); Ndb *ndb= get_ndb(table->in_use); - - if (m_rows_inserted > m_rows_to_insert) + uint retries= NDB_AUTO_INCREMENT_RETRIES; + int retry_sleep= 30; /* 30 milliseconds, transaction */ + + cache_size= m_autoincrement_prefetch; + if (m_estimated_batched_insert && m_rows_inserted > m_rows_to_insert) { - /* We guessed too low */ + /* + We guessed too low, increase estimate by the + defined prefetch size. + */ m_rows_to_insert+= m_autoincrement_prefetch; } - uint remaining= m_rows_to_insert - m_rows_inserted; - uint min_prefetch= + /* + Set the prefetch to estimated remaining rows, but not lower + than user setting of the prefetch size. + */ + remaining= m_rows_to_insert - m_rows_inserted; + min_prefetch= (remaining < thd->variables.ndb_autoincrement_prefetch_sz) ? thd->variables.ndb_autoincrement_prefetch_sz : remaining; cache_size= ((remaining < m_autoincrement_prefetch) ? min_prefetch : remaining); - uint retries= NDB_AUTO_INCREMENT_RETRIES; - int retry_sleep= 30; /* 30 milliseconds, transaction */ + for (;;) { Ndb_tuple_id_range_guard g(m_share); === modified file 'sql/ha_ndbcluster.h' --- sql/ha_ndbcluster.h 2009-05-26 18:53:34 +0000 +++ sql/ha_ndbcluster.h 2009-09-01 10:09:00 +0000 @@ -699,7 +699,10 @@ private: ha_rows m_rows_to_insert; // TODO: merge it with handler::estimation_rows_to_insert? ha_rows m_rows_inserted; ha_rows m_rows_changed; - bool m_delete_cannot_batch; + union { + bool m_delete_cannot_batch; + bool m_estimated_batched_insert; + }; bool m_update_cannot_batch; uint m_bytes_per_write; bool m_skip_auto_increment;