Maksold/Database Fix for AUTO_INCREMENT exceeds max value.md

## Database Fix for AUTO_INCREMENT exceeds max value.md

      
    Raw
  

              Database Fix for AUTO_INCREMENT exceeds max value.md
            
          
    AUTO_INCREMENT grows on every INSERT ... ON DUPLICATE on InnoDB tables #28387

magento/magento2#28387
Symptom:

General system exception happened.
SQLSTATE[22003]: Numeric value out of range: 167
Out of range value for column 'value_id' at row 1,
query was: INSERT INTO catalog_product_entity_varchar
(entity_id,attribute_id,store_id,value)
VALUES (?, ?, ?, ?), (?, ?, ?, ?), (?, ?, ?, ?), (

This script copies out the data into a temp table, fixes the auto-increment, then pipes the data back in cleanly.
Run each line independently and note the instructions for continuing.
Take notes on the counts, etc.
I highly recommend taking a database dump before beginning, just in case.
Fair warning, there are a lot of steps here, but most of them are double-checking. Since you're most likely going to be running this in production, it's best to be safe rather than sorry.
What you'll need


command line to the Magento instance
SQL edit access to the database
connection of choice for downloading backups/exports


Steps

1. Put the site in maintenance mode

php bin/magento maintenance:enable

2. Get the current max value_id used in the table

select
    max(value_id)
from
    catalog_product_entity_varchar;

Note the output: current max id =


3. Get the current value of the auto increment for the table

select
    `AUTO_INCREMENT`
from
    INFORMATION_SCHEMA.TABLES
where
    TABLE_NAME = 'catalog_product_entity_varchar';

Note the output: current autoincrement =


4. (optional) Dump the original table to csv and/or sql using whatever method you choose, just in case.


5. Create a temporary table to hold the data

drop table if exists catalog_product_entity_varchar_copy;
create table catalog_product_entity_varchar_copy like catalog_product_entity_varchar;

6. Pull all of the rows into the temp table.

This will probably take a few minutes.
insert into catalog_product_entity_varchar_copy
select
    *
from
    catalog_product_entity_varchar;

7. Confirm equal row counts

select
    'catalog_product_entity_varchar' tablename,
    count(value_id) as rowcount
from
    catalog_product_entity_varchar
group by
    tablename
union
select
    'catalog_product_entity_varchar_copy' tablename,
    count(value_id) as rowcount
from
    catalog_product_entity_varchar_copy
group by
    tablename;

8. Truncate the table

This will also reset the auto increment.
truncate catalog_product_entity_varchar;
analyze table `catalog_product_entity_varchar`;

9. Confirm that the autoincrement is now 1

select
    table_schema,TABLE_NAME,
    `AUTO_INCREMENT`
from
    INFORMATION_SCHEMA.TABLES
where
    TABLE_NAME = 'catalog_product_entity_varchar';

9. Pipe the data back into the table

This will probably take a few minutes.
insert into catalog_product_entity_varchar (attribute_id, store_id, `value`, entity_id)
select
    attribute_id,
    store_id,
    `value`,
    entity_id
from
    catalog_product_entity_varchar_copy;

If successful, no errors, note the record count and time it took:
If no errors, skip down to step 10.


⚠️ How to handle errors at this step before proceeding ⚠️

Example Error:


Cannot add or update a child row: a foreign key constraint fails (magento_replica_prod.catalog_product_entity_varchar, CONSTRAINT CAT_PRD_ENTT_VCHR_ATTR_ID_EAV_ATTR_ATTR_ID FOREIGN KEY (attribute_id) REFERENCES eav_attribute (attribute_id) ON DEL)
This is caused by data existing that doesn't comply with constraints that were defined on the table after the data was in there or perhaps a cascade was interrupted.
Either way, the following steps help you identify and double check that you can ignore these rows as they are not valid.

⚠️ Find the problem attribute ids

select distinct
    attribute_id
from
    catalog_product_entity_varchar_copy cpev_c
where
    not exists (
        select
            *
        from
            eav_attribute eav
        where
            eav.attribute_id = cpev_c.attribute_id);

Note the attribute_ids that no longer exist:


⚠️ Confirm that these are not in the Catalog EAV table

select
    *
from
    catalog_eav_attribute
where
    attribute_id in (`your list here`);

⚠️ Confirm that these are not in the main EAV table

select
    *
from
    eav_attribute
where
    attribute_id in (`your list here`);

⚠️ Count how many "good records" we have

select
    count(value_id)
from
    catalog_product_entity_varchar_copy
where
    attribute_id not in (`your list here`);

Note the output: good records =


⚠️ Count how many "dangling records" we have

select
    count(value_id)
from
    catalog_product_entity_varchar_copy
where
    attribute_id in (`your list here`);

Note the output: dangling records =

⚠️ Repeating step 9, but exclude the "dangling" records

This will probably take a few minutes.
insert into catalog_product_entity_varchar (attribute_id, store_id, `value`, entity_id)
select
    attribute_id,
    store_id,
    `value`,
    entity_id
from
    catalog_product_entity_varchar_copy
where
    attribute_id not in (`your list here`);
This will probably take a few minutes.

10. Double-check everything

Check that the value_id is now reset

select
    'catalog_product_entity_varchar_copy' tablename,
    max(value_id) as max_id
from
    catalog_product_entity_varchar_copy
group by
    tablename
union
select
    'catalog_product_entity_varchar' tablename,
    max(value_id) as max_id
from
    catalog_product_entity_varchar
group by
    tablename;

confirm the auto increment value for the updated table

analyze table `catalog_product_entity_varchar`;

select
    table_schema,
    auto_increment
from
    information_schema.TABLES
where (TABLE_NAME = 'catalog_product_entity_varchar');

confirm both row counts

select
    'catalog_product_entity_varchar_copy' tablename,
    count(value_id) as rowcount
from
    catalog_product_entity_varchar_copy
group by
    tablename
union
select
    'catalog_product_entity_varchar' tablename,
    count(value_id) as rowcount
from
    catalog_product_entity_varchar
group by
    tablename;

10. Re-index Magento

bin/magento indexer:reset && bin/magento indexer:reindex && bin/magento c:f


11. Take Magento out of maintenance mode

bin/magento maintenance:disable

11. Test and Confirm Product text attribute values


Admin
GraphQL
REST
Frontend


12. Clean up

drop table if exists catalog_product_entity_varchar_copy;