We generally set the primary key of a database table as “auto-increment”. The auto-increment column of your MySQL table has a maximum value based on the integer type. When you have a lot of data in your table, the auto-increment value can be exhausted by reaching its maximum value. After that, no data can be inserted into that particular table.
Maximum value of Integer Types
Different integer types have different maximum values. Here’s a list of the different integer types in MySQL and their maximum value:
Integer Types Maximum Value Signed
TINYINT - 127
UNSIGNED TINYINT - 255
SMALLINT - 32767
UNSIGNED SMALLINT - 65535
MEDIUMINT - 8388607
UNSIGNED MEDIUMINT - 16777215
INT - 2147483647
UNSIGNED INT - 4294967295
BIGINT - 9223372036854775807
UNSIGNED BIGINT - 18446744073709551615
In MySQL, you can set the display width for the INT data type. The display width is the number in parentheses that represents the output length, i.e. how many characters should be displayed in the column when retrieving data for the column. Thus, it matters only when displaying the output.
Consider the following: If you specify 5 as a value for the int(1), int(6), int(10), and int(11) columns, they all will output the same value – 5. Actually, display width makes difference when you set the ZEROFILL attribute. In this case, the result will display 5, 000005, 0000000005, and 00000000005 respectively.
Note that the display width does not influence the maximum and minimum value ranges. The minimum and maximum values of the column and the storage size (in bytes) depend only on the integer type you use but not on the length. The values int(1), int(6), int(10), and int(11) will have the maximum value in a range that equals 2147483647 (for signed INT) and 4294967295 (for unsigned INT).
Get the auto-increment value of database table
Get the auto-increment value of all tables
SELECT `AUTO_INCREMENT`,`TABLE_NAME` FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA ='your_database_name' ORDER BY AUTO_INCREMENT ASC;
Get the auto-increment value of a single table
SHOW TABLE STATUS FROM `your_database_name` WHERE `name` LIKE 'your_table_name' \G;
Solution
There can be two ways to fix the auto_increment ID exhaustion issue. We can either change the data type to Unsigned Int or Big Int which has higher maximum value. Or, we can create a copy of the table and update the auto-increment value of that table copy.
1) Change the column type to Unsigned Int or BigInt
If you are using “INT” integer type and the auto-increment value of your table got exhausted then you may consider using “UNSIGNED INT” or “BIGIINT” integer type for your auto-increment field/column.
CREATE TABLE mytable_new LIKE mytable;
ALTER TABLE mytable_new MODIFY COLUMN col_name BIGINT AUTO_INCREMENT;
// or, change the column type to Unsigned Int
// ALTER TABLE mytable_new MODIFY COLUMN col_name INT(11) UNSIGNED NOT NULL AUTO_INCREMENT;
RENAME TABLE mytable TO mytable_archive, mytable_new TO mytable;
2) Create a copy of the table and update the auto-increment value
If there are a lot of insert and delete operations on your database table then you can create a copy of the table and update the auto-increment value of the new table.
In the example steps below, I have used the following database and table name:
- Database Name =
magento - Table Name =
catalog_product_entity_text
The table catalog_product_entity_text has auto_increment value exhausted.
Check the auto_increment value of the table
# Get the auto_increment value of all the tables of a database
SELECT `AUTO_INCREMENT`,`TABLE_NAME` FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA ='magento' ORDER BY AUTO_INCREMENT asc;
# Alternative way to check the auto_increment value of a table
SHOW TABLE STATUS FROM `magento` WHERE `name` LIKE 'catalog_product_entity_text' \G;
Create the copy of the table
# Create the new table
CREATE TABLE catalog_product_entity_text2 LIKE catalog_product_entity_text;
/*
MariaDB [magento]> desc catalog_product_entity_text2;
+--------------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+----------------------+------+-----+---------+----------------+
| value_id | int(11) | NO | PRI | NULL | auto_increment |
| attribute_id | smallint(5) unsigned | NO | MUL | 0 | |
| store_id | smallint(5) unsigned | NO | MUL | 0 | |
| row_id | int(10) unsigned | NO | MUL | NULL | |
| value | mediumtext | YES | | NULL | |
+--------------+----------------------+------+-----+---------+----------------+
5 rows in set (0.002 sec)
MariaDB [magento]> select count(*) from catalog_product_entity_text2;
+----------+
| count(*) |
+----------+
| 0 |
+----------+
1 row in set (0.001 sec)
*/
/*
MariaDB [magento]> show create table catalog_product_entity_text2 \G;
*************************** 1. row ***************************
Table: catalog_product_entity_text2
Create Table: CREATE TABLE `catalog_product_entity_text2` (
`value_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Value ID',
`attribute_id` smallint(5) unsigned NOT NULL DEFAULT 0 COMMENT 'Attribute ID',
`store_id` smallint(5) unsigned NOT NULL DEFAULT 0 COMMENT 'Store ID',
`row_id` int(10) unsigned NOT NULL COMMENT 'Version Id',
`value` mediumtext DEFAULT NULL COMMENT 'Value',
PRIMARY KEY (`value_id`),
UNIQUE KEY `CATALOG_PRODUCT_ENTITY_TEXT_ROW_ID_ATTRIBUTE_ID_STORE_ID` (`row_id`,`attribute_id`,`store_id`),
KEY `CATALOG_PRODUCT_ENTITY_TEXT_ATTRIBUTE_ID` (`attribute_id`),
KEY `CATALOG_PRODUCT_ENTITY_TEXT_STORE_ID` (`store_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='Catalog Product Text Attribute Backend Table'
1 row in set (0.000 sec)
*/
Create foreign keys in the newly copied table
The foreign keys are not set when we copy the table. Hence, we need to add the original table’s foreign keys manually into the copied table.
Note: The foreign key name length limit is 64 characters.
# Re-create the foreign keys
ALTER TABLE catalog_product_entity_text2 ADD CONSTRAINT CATALOG_PRODUCT_ENTITY_TEXT2_STORE_ID_STORE_STORE_ID FOREIGN KEY (`store_id`) REFERENCES `store` (`store_id`) ON DELETE CASCADE;
ALTER TABLE catalog_product_entity_text2 ADD CONSTRAINT CAT_PRD_ENTT_TEXT2_ATTR_ID_EAV_ATTR_ATTR_ID FOREIGN KEY (`attribute_id`) REFERENCES `eav_attribute` (`attribute_id`) ON DELETE CASCADE;
ALTER TABLE catalog_product_entity_text2 ADD CONSTRAINT CAT_PRODUCT_ENTITY_TEXT2_ROW_ID_CATALOG_PRODUCT_ENTITY_ROW_ID FOREIGN KEY (`row_id`) REFERENCES `catalog_product_entity` (`row_id`) ON DELETE CASCADE;
/*
MariaDB [magento]> show create table catalog_product_entity_text2 \G;
*************************** 1. row ***************************
Table: catalog_product_entity_text2
Create Table: CREATE TABLE `catalog_product_entity_text2` (
`value_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Value ID',
`attribute_id` smallint(5) unsigned NOT NULL DEFAULT 0 COMMENT 'Attribute ID',
`store_id` smallint(5) unsigned NOT NULL DEFAULT 0 COMMENT 'Store ID',
`row_id` int(10) unsigned NOT NULL COMMENT 'Version Id',
`value` mediumtext DEFAULT NULL COMMENT 'Value',
PRIMARY KEY (`value_id`),
UNIQUE KEY `CATALOG_PRODUCT_ENTITY_TEXT_ROW_ID_ATTRIBUTE_ID_STORE_ID` (`row_id`,`attribute_id`,`store_id`),
KEY `CATALOG_PRODUCT_ENTITY_TEXT_ATTRIBUTE_ID` (`attribute_id`),
KEY `CATALOG_PRODUCT_ENTITY_TEXT_STORE_ID` (`store_id`),
CONSTRAINT `CATALOG_PRODUCT_ENTITY_TEXT2_STORE_ID_STORE_STORE_ID` FOREIGN KEY (`store_id`) REFERENCES `store` (`store_id`) ON DELETE CASCADE,
CONSTRAINT `CAT_PRD_ENTT_TEXT2_ATTR_ID_EAV_ATTR_ATTR_ID` FOREIGN KEY (`attribute_id`) REFERENCES `eav_attribute` (`attribute_id`) ON DELETE CASCADE,
CONSTRAINT `CAT_PRODUCT_ENTITY_TEXT2_ROW_ID_CATALOG_PRODUCT_ENTITY_ROW_ID` FOREIGN KEY (`row_id`) REFERENCES `catalog_product_entity` (`row_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='Catalog Product Text Attribute Backend Table'
1 row in set (0.001 sec)
*/
Insert data into table copy
Insert data into the newly copied table.
# Insert the data, but not value_id (i.e. the primary key)
INSERT INTO catalog_product_entity_text2
(attribute_id, store_id, row_id, value)
SELECT attribute_id, store_id, row_id, value
FROM catalog_product_entity_text;
/*
MariaDB [magento]> select count(*) from catalog_product_entity_text2;
+----------+
| count(*) |
+----------+
| 209768 |
+----------+
1 row in set (0.041 sec)
MariaDB [magento]> select value_id, attribute_id, store_id, row_id from catalog_product_entity_text2 order by value_id desc limit 5;
+----------+--------------+----------+--------+
| value_id | attribute_id | store_id | row_id |
+----------+--------------+----------+--------+
| 209768 | 1936 | 0 | 90100 |
| 209767 | 1936 | 0 | 180299 |
| 209766 | 1898 | 0 | 88775 |
| 209765 | 1898 | 0 | 88777 |
| 209764 | 1898 | 0 | 88778 |
+----------+--------------+----------+--------+
5 rows in set (0.001 sec)
*/
Rename the tables
/*
MariaDB [magento]> show tables like "%catalog_product_entity_text%";
+---------------------------------------------------+
| Tables_in_magento (%catalog_product_entity_text%) |
+---------------------------------------------------+
| catalog_product_entity_text |
| catalog_product_entity_text2 |
+---------------------------------------------------+
2 rows in set (0.004 sec)
*/
# Swap the tables
RENAME TABLE catalog_product_entity_text TO catalog_product_entity_text_OLD, catalog_product_entity_text2 TO catalog_product_entity_text;
/*
MariaDB [magento]> show tables like "%catalog_product_entity_text%";
+---------------------------------------------------+
| Tables_in_magento (%catalog_product_entity_text%) |
+---------------------------------------------------+
| catalog_product_entity_text |
| catalog_product_entity_text_OLD |
+---------------------------------------------------+
2 rows in set (0.003 sec)
*/
Reset AUTO_INCREMENT
SELECT MAX(value_id) FROM catalog_product_entity_text;
/*
MariaDB [magento]> SHOW TABLE STATUS FROM `magento` WHERE `name` LIKE 'catalog_product_entity_text' \G;
*************************** 1. row ***************************
Name: catalog_product_entity_text
Engine: InnoDB
Version: 10
Row_format: Dynamic
Rows: 200280
Avg_row_length: 165
Data_length: 33095680
Max_data_length: 0
Index_length: 13156352
Data_free: 4194304
Auto_increment: 262141
Create_time: 2022-12-02 19:39:29
Update_time: 2022-12-02 19:37:10
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options: row_format=DYNAMIC
Comment: Catalog Product Text Attribute Backend Table
Max_index_length: 0
Temporary: N
1 row in set (0.004 sec)
MariaDB [magento]> SELECT `AUTO_INCREMENT` FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'magento' AND TABLE_NAME = 'catalog_product_entity_text';
+----------------+
| auto_increment |
+----------------+
| 262141 |
+----------------+
1 row in set (0.001 sec)
*/
/*
MariaDB [magento]> SELECT MAX(value_id) FROM catalog_product_entity_text;
+---------------+
| MAX(value_id) |
+---------------+
| 209768 |
+---------------+
1 row in set (0.001 sec)
*/
ALTER TABLE catalog_product_entity_text AUTO_INCREMENT = [MAX_VALUE_ID];
# MAX_VALUE_ID above is 209768
# Confirm the auto_increment value
/*
MariaDB [magento]> SELECT `AUTO_INCREMENT` FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'magento' AND TABLE_NAME = 'catalog_product_entity_text';
+----------------+
| AUTO_INCREMENT |
+----------------+
| 209769 |
+----------------+
1 row in set (0.001 sec)
*/
Confirm the fix
SELECT COUNT(*) FROM catalog_product_entity_text;
/*
MariaDB [magento]> SELECT COUNT(*) FROM catalog_product_entity_text;
+----------+
| COUNT(*) |
+----------+
| 209768 |
+----------+
1 row in set (0.044 sec)
*/
SELECT MAX(value_id) FROM catalog_product_entity_text;
/*
MariaDB [magento]> SELECT MAX(value_id) FROM catalog_product_entity_text;
+---------------+
| MAX(value_id) |
+---------------+
| 209768 |
+---------------+
1 row in set (0.001 sec)
*/
SHOW CREATE TABLE catalog_product_entity_text \G;
/*
MariaDB [magento]> SHOW CREATE TABLE catalog_product_entity_text \G;
*************************** 1. row ***************************
Table: catalog_product_entity_text
Create Table: CREATE TABLE `catalog_product_entity_text` (
`value_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Value ID',
`attribute_id` smallint(5) unsigned NOT NULL DEFAULT 0 COMMENT 'Attribute ID',
`store_id` smallint(5) unsigned NOT NULL DEFAULT 0 COMMENT 'Store ID',
`row_id` int(10) unsigned NOT NULL COMMENT 'Version Id',
`value` mediumtext DEFAULT NULL COMMENT 'Value',
PRIMARY KEY (`value_id`),
UNIQUE KEY `CATALOG_PRODUCT_ENTITY_TEXT_ROW_ID_ATTRIBUTE_ID_STORE_ID` (`row_id`,`attribute_id`,`store_id`),
KEY `CATALOG_PRODUCT_ENTITY_TEXT_ATTRIBUTE_ID` (`attribute_id`),
KEY `CATALOG_PRODUCT_ENTITY_TEXT_STORE_ID` (`store_id`),
CONSTRAINT `CATALOG_PRODUCT_ENTITY_TEXT2_STORE_ID_STORE_STORE_ID` FOREIGN KEY (`store_id`) REFERENCES `store` (`store_id`) ON DELETE CASCADE,
CONSTRAINT `CAT_PRD_ENTT_TEXT2_ATTR_ID_EAV_ATTR_ATTR_ID` FOREIGN KEY (`attribute_id`) REFERENCES `eav_attribute` (`attribute_id`) ON DELETE CASCADE,
CONSTRAINT `CAT_PRODUCT_ENTITY_TEXT2_ROW_ID_CATALOG_PRODUCT_ENTITY_ROW_ID` FOREIGN KEY (`row_id`) REFERENCES `catalog_product_entity` (`row_id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=209769 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='Catalog Product Text Attribute Backend Table'
1 row in set (0.001 sec)
*/
Final clean up
# Drop the old table
DROP TABLE catalog_product_entity_text_OLD;
# Fix the foreign key IDs
ALTER TABLE catalog_product_entity_text DROP FOREIGN KEY CATALOG_PRODUCT_ENTITY_TEXT2_STORE_ID_STORE_STORE_ID;
ALTER TABLE catalog_product_entity_text ADD CONSTRAINT CATALOG_PRODUCT_ENTITY_TEXT_STORE_ID_STORE_STORE_ID FOREIGN KEY (`store_id`) REFERENCES `store` (`store_id`) ON DELETE CASCADE;
ALTER TABLE catalog_product_entity_text DROP FOREIGN KEY CAT_PRD_ENTT_TEXT2_ATTR_ID_EAV_ATTR_ATTR_ID;
ALTER TABLE catalog_product_entity_text ADD CONSTRAINT CAT_PRD_ENTT_TEXT_ATTR_ID_EAV_ATTR_ATTR_ID FOREIGN KEY (`attribute_id`) REFERENCES `eav_attribute` (`attribute_id`) ON DELETE CASCADE;
ALTER TABLE catalog_product_entity_text DROP FOREIGN KEY CAT_PRODUCT_ENTITY_TEXT2_ROW_ID_CATALOG_PRODUCT_ENTITY_ROW_ID;
ALTER TABLE catalog_product_entity_text ADD CONSTRAINT CATALOG_PRODUCT_ENTITY_TEXT_ROW_ID_CATALOG_PRODUCT_ENTITY_ROW_ID FOREIGN KEY (`row_id`) REFERENCES `catalog_product_entity` (`row_id`) ON DELETE CASCADE;
SHOW CREATE TABLE catalog_product_entity_text \G;
/*
MariaDB [magento]> SHOW CREATE TABLE catalog_product_entity_text \G;
*************************** 1. row ***************************
Table: catalog_product_entity_text
Create Table: CREATE TABLE `catalog_product_entity_text` (
`value_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Value ID',
`attribute_id` smallint(5) unsigned NOT NULL DEFAULT 0 COMMENT 'Attribute ID',
`store_id` smallint(5) unsigned NOT NULL DEFAULT 0 COMMENT 'Store ID',
`row_id` int(10) unsigned NOT NULL COMMENT 'Version Id',
`value` mediumtext DEFAULT NULL COMMENT 'Value',
PRIMARY KEY (`value_id`),
UNIQUE KEY `CATALOG_PRODUCT_ENTITY_TEXT_ROW_ID_ATTRIBUTE_ID_STORE_ID` (`row_id`,`attribute_id`,`store_id`),
KEY `CATALOG_PRODUCT_ENTITY_TEXT_ATTRIBUTE_ID` (`attribute_id`),
KEY `CATALOG_PRODUCT_ENTITY_TEXT_STORE_ID` (`store_id`),
CONSTRAINT `CATALOG_PRODUCT_ENTITY_TEXT_ROW_ID_CATALOG_PRODUCT_ENTITY_ROW_ID` FOREIGN KEY (`row_id`) REFERENCES `catalog_product_entity` (`row_id`) ON DELETE CASCADE,
CONSTRAINT `CATALOG_PRODUCT_ENTITY_TEXT_STORE_ID_STORE_STORE_ID` FOREIGN KEY (`store_id`) REFERENCES `store` (`store_id`) ON DELETE CASCADE,
CONSTRAINT `CAT_PRD_ENTT_TEXT_ATTR_ID_EAV_ATTR_ATTR_ID` FOREIGN KEY (`attribute_id`) REFERENCES `eav_attribute` (`attribute_id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=209769 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='Catalog Product Text Attribute Backend Table'
1 row in set (0.001 sec)
*/
The step-by-step procedure for creating a copy of the table and fixing the auto-increment ID issue is present in the following gist:
https://gist.github.com/chapagain/8c1ac9af5d10bc9c6beec7d3758c688f
Hope this helps. Thanks.