Fix Auto Increment Value Exhaustion

fix_auto_increment_exhaustion.sql

# 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 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)
*/

# 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 the data, but not value_id
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)
*/

/*
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

/*
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)
*/