[im using mysql workbench, and i have a table that has a column which auto increments when i add new records for the rest of the columns in it, so i deleted some rows in the table several times and again i add a column, and its id is 9 so. now my table’s ids look like this (1,2,3,4,9),
the confusing part begging when i execute this sql query,

SELECT AUTO_INCREMENT 
FROM information_schema.TABLES 
WHERE TABLE_SCHEMA = 'new_cloth_shop_test' 
AND TABLE_NAME = 'stock';

this gives me 7 as the output, eventhough i reset the auto increment value to 10 by using this query ALTER TABLE stock AUTO_INCREMENT = 10;. then SHOW TABLE STATUS LIKE 'stock'; this sql query show me that the table has 7 for the auto increment. but how it happen even i reset it. it doent give me expected value which is 10. seems like it stoped at 7 in the management system but it also increment the value when i add rows but it doesnt return the next auto increment value. how can i solve this.

i just tried to get the the current auto increment id for my ui use case. and it was stoped at 7 even when i add rows after deleting some rows. but i was expecting 10 as the output but it gives 7 al the time]

this is table image : enter image description here

this is table status image : enter image description here

this is the current auto imcrement value : enter image description here