From 1b49b20f21ba973d9d3840fcd57a16eae6e76f94 Mon Sep 17 00:00:00 2001
From: Oleksandr Peresypkin <oleksandr.peresypkin@gmail.com>
Date: Mon, 1 May 2023 15:08:43 +0200
Subject: [PATCH] Bug #78004 https://bugs.mysql.com/bug.php?id=78004

Fix the issue where inserting a penultimate auto_increment value
causes it to become the column's maximum value.
---
 .../innodb/r/innodb-autoinc-78004.result      | 47 +++++++++++++++++++
 .../suite/innodb/t/innodb-autoinc-78004.test  | 35 ++++++++++++++
 storage/innobase/handler/ha_innodb.cc         |  2 +-
 3 files changed, 83 insertions(+), 1 deletion(-)
 create mode 100644 mysql-test/suite/innodb/r/innodb-autoinc-78004.result
 create mode 100644 mysql-test/suite/innodb/t/innodb-autoinc-78004.test

diff --git a/mysql-test/suite/innodb/r/innodb-autoinc-78004.result b/mysql-test/suite/innodb/r/innodb-autoinc-78004.result
new file mode 100644
index 000000000000..b4699dbef9a0
--- /dev/null
+++ b/mysql-test/suite/innodb/r/innodb-autoinc-78004.result
@@ -0,0 +1,47 @@
+drop table if exists `t78004_tinyint`, `t78004_smallint`, `t78004_mediumint`, `t78004_int`, `t78004_bigint`;
+create table `t78004_tinyint` (`id` tinyint not null auto_increment primary key) engine=innodb;
+insert into `t78004_tinyint` (`id`) values (124);
+insert into `t78004_tinyint` (`id`) values (null);
+insert into `t78004_tinyint` (`id`) values (null);
+select `id` from `t78004_tinyint`;
+id
+124
+125
+126
+create table `t78004_smallint` (`id` smallint not null auto_increment primary key) engine=innodb;
+insert into `t78004_smallint` (`id`) values (32764);
+insert into `t78004_smallint` (`id`) values (null);
+insert into `t78004_smallint` (`id`) values (null);
+select `id` from `t78004_smallint`;
+id
+32764
+32765
+32766
+create table `t78004_mediumint` (`id` mediumint not null auto_increment primary key) engine=innodb;
+insert into `t78004_mediumint` (`id`) values (8388604);
+insert into `t78004_mediumint` (`id`) values (null);
+insert into `t78004_mediumint` (`id`) values (null);
+select `id` from `t78004_mediumint`;
+id
+8388604
+8388605
+8388606
+create table `t78004_int` (`id` int not null auto_increment primary key) engine=innodb;
+insert into `t78004_int` (`id`) values (2147483644);
+insert into `t78004_int` (`id`) values (null);
+insert into `t78004_int` (`id`) values (null);
+select `id` from `t78004_int`;
+id
+2147483644
+2147483645
+2147483646
+create table `t78004_bigint` (`id` bigint not null auto_increment primary key) engine=innodb;
+insert into `t78004_bigint` (`id`) values (9223372036854775804);
+insert into `t78004_bigint` (`id`) values (null);
+insert into `t78004_bigint` (`id`) values (null);
+select `id` from `t78004_bigint`;
+id
+9223372036854775804
+9223372036854775805
+9223372036854775806
+drop table `t78004_tinyint`, `t78004_smallint`, `t78004_mediumint`, `t78004_int`, `t78004_bigint`;
diff --git a/mysql-test/suite/innodb/t/innodb-autoinc-78004.test b/mysql-test/suite/innodb/t/innodb-autoinc-78004.test
new file mode 100644
index 000000000000..939e9dab1a6c
--- /dev/null
+++ b/mysql-test/suite/innodb/t/innodb-autoinc-78004.test
@@ -0,0 +1,35 @@
+--disable_warnings
+drop table if exists `t78004_tinyint`, `t78004_smallint`, `t78004_mediumint`, `t78004_int`, `t78004_bigint`;
+--enable_warnings
+
+create table `t78004_tinyint` (`id` tinyint not null auto_increment primary key) engine=innodb;
+insert into `t78004_tinyint` (`id`) values (124);
+insert into `t78004_tinyint` (`id`) values (null);
+insert into `t78004_tinyint` (`id`) values (null);
+select `id` from `t78004_tinyint`;
+
+create table `t78004_smallint` (`id` smallint not null auto_increment primary key) engine=innodb;
+insert into `t78004_smallint` (`id`) values (32764);
+insert into `t78004_smallint` (`id`) values (null);
+insert into `t78004_smallint` (`id`) values (null);
+select `id` from `t78004_smallint`;
+
+create table `t78004_mediumint` (`id` mediumint not null auto_increment primary key) engine=innodb;
+insert into `t78004_mediumint` (`id`) values (8388604);
+insert into `t78004_mediumint` (`id`) values (null);
+insert into `t78004_mediumint` (`id`) values (null);
+select `id` from `t78004_mediumint`;
+
+create table `t78004_int` (`id` int not null auto_increment primary key) engine=innodb;
+insert into `t78004_int` (`id`) values (2147483644);
+insert into `t78004_int` (`id`) values (null);
+insert into `t78004_int` (`id`) values (null);
+select `id` from `t78004_int`;
+
+create table `t78004_bigint` (`id` bigint not null auto_increment primary key) engine=innodb;
+insert into `t78004_bigint` (`id`) values (9223372036854775804);
+insert into `t78004_bigint` (`id`) values (null);
+insert into `t78004_bigint` (`id`) values (null);
+select `id` from `t78004_bigint`;
+
+drop table `t78004_tinyint`, `t78004_smallint`, `t78004_mediumint`, `t78004_int`, `t78004_bigint`;
diff --git a/storage/innobase/handler/ha_innodb.cc b/storage/innobase/handler/ha_innodb.cc
index 8b9786c8b7fc..8549c9d68837 100644
--- a/storage/innobase/handler/ha_innodb.cc
+++ b/storage/innobase/handler/ha_innodb.cc
@@ -2708,7 +2708,7 @@ ulonglong innobase_next_autoinc(
 
     ulonglong free = max_value - current;
 
-    if (free < offset || free - offset <= block) {
+    if (free < offset || free - offset < block) {
       next_value = max_value;
     } else {
       next_value = 0;