-- ----------------------------------------------------------------------------
-- Example 1: 17 rows

create table MY_TABLE (
  col1 smallint not null auto_increment,
  col2 smallint not null,
  primary key (col1)
)
  engine = innodb;

insert into MY_TABLE(col2)
select seq
from seq_1_to_17;

select count(*), max(col1) from MY_TABLE;

select AUTO_INCREMENT
from information_schema.TABLES
where TABLE_NAME = 'MY_TABLE';
-- 17
-- Not OK, expected 18 not next power of two.

-- ----------------------------------------------------------------------------
-- Example 2: 42 rows

create table MY_TABLE (
  col1 smallint not null auto_increment,
  col2 smallint not null,
  primary key (col1)
)
  engine = innodb;

insert into MY_TABLE(col2)
select seq
from seq_1_to_42 seq;

select count(*), max(col2) from MY_TABLE;

select AUTO_INCREMENT
from information_schema.TABLES
where TABLE_NAME = 'MY_TABLE';
-- 64
-- Not OK, expected 43 not next power of two.

-- ----------------------------------------------------------------------------
-- Example 3: 20,000 rows

create table MY_TABLE (
  col1 smallint not null auto_increment,
  col2 smallint not null,
  primary key (col1)
)
  engine = innodb;

insert into MY_TABLE(col2)
select seq
from seq_1_to_20000 seq;

select count(*), max(col1) from MY_TABLE;

select AUTO_INCREMENT
from information_schema.TABLES
where TABLE_NAME = 'MY_TABLE';
-- 32768
-- Not OK, expected 20,001 not next power of two.

insert into MY_TABLE(col2)
values (123);
-- [22003][167] (conn=3121) Out of range value for column 'col1' at row 1
-- Not OK

-- ----------------------------------------------------------------------------
-- Example 4: int instead of smallint

create table MY_TABLE (
  col1 int not null auto_increment,
  col2 smallint not null,
  primary key (col1)
)
  engine = innodb;

insert into MY_TABLE(col2)
select seq
from seq_1_to_20000 seq;

select count(*), max(col1) from MY_TABLE;

select AUTO_INCREMENT
from information_schema.TABLES
where TABLE_NAME = 'MY_TABLE';
-- 32768
-- Not OK, expected 20,001 not next power of two.

-- ----------------------------------------------------------------------------
-- Example 5: unsigned int instead of smallint

create table MY_TABLE (
  col1 int unsigned not null auto_increment,
  col2 smallint not null,
  primary key (col1)
)
  engine = innodb;

insert into MY_TABLE(col2)
select seq
from seq_1_to_20000 seq;

select count(*), max(col1) from MY_TABLE;

select AUTO_INCREMENT
from information_schema.TABLES
where TABLE_NAME = 'MY_TABLE';
-- 32768
-- Not OK, expected 20,001 not next power of two.

-- ----------------------------------------------------------------------------
-- Example 6: Aria storage engine: OK

create table MY_TABLE (
  col1 int not null auto_increment,
  col2 smallint not null,
  primary key (col1)
)
  engine = aria;

insert into MY_TABLE(col2)
select seq
from seq_1_to_20000 seq;

select count(*), max(col1) from MY_TABLE;

select AUTO_INCREMENT
from information_schema.TABLES
where TABLE_NAME = 'MY_TABLE';
-- 20001
-- OK ;-)