MySQL – How to Set Auto Increment Value in phpMyAdmin

MySQLphpmyadmin

I am following these instructions to reset the value of auto_increment to the last highest value using phpMyAdmin.

Example:

If I have this table and data:

DROP TABLE IF EXISTS `Tbl_Lis_Agencias`;
CREATE TABLE IF NOT EXISTS `Tbl_Lis_Agencias` (
  `IdAgency` int(3) UNSIGNED ZEROFILL NOT NULL AUTO_INCREMENT,
  `AgencyCodU` int(3) UNSIGNED ZEROFILL NOT NULL DEFAULT '000',
  `AgencyName` varchar(40) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  `Agency_Order` int UNSIGNED DEFAULT NULL,
  `AgencyStatus` int UNSIGNED NOT NULL DEFAULT '1',
  PRIMARY KEY (`IdAgency`),
  UNIQUE KEY `IdAgency` (`IdAgency`),
  UNIQUE KEY `Agency_Order` (`Agency_Order`),
  UNIQUE KEY `AgencyName` (`AgencyName`),
  KEY `xAgencyStatus` (`AgencyStatus`)
) ENGINE=InnoDB AUTO_INCREMENT=12345 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


INSERT INTO `Tbl_Lis_Agencias` VALUES
(001, 001, 'Panama', 1, 1),
(002, 020, 'Aguadulce', 2, 1),
(003, 080, 'David', 3, 1),
(004, 010, 'Vacamonte', 4, 1),
(005, 060, 'Prueba', 5, 1);

If I run this:

enter image description here

phpMyAdmin says that is was a succeful update:

enter image description here

but when I switch to the operation tab I see this again:

enter image description here

What I don't want to do and I am forced is to go to the terminal and run this:

ALTER TABLE Tbl_Lis_Agencias AUTO_INCREMENT = 5

Then I look at the operations tab again

enter image description here

Why isn't the operations tab updated in the first example?

The solution in this post is not useful, it is mandatory for me to do everything through phpMyAdmin, not through the terminal.

update

i have check that phpmyadmin show ghost value; related to this post: https://github.com/phpmyadmin/phpmyadmin/issues/16378

Best Answer

https://stackoverflow.com/questions/64214549/mysql-information-schema-bugged/64214609#64214609

MySQL 8.0 tries to cache the statistics about tables, but there seem to be some bugs in the implementation. Sometimes it shows table statistics as NULL, and sometimes it shows values, but fails to update them as you modify table data.

See https://bugs.mysql.com/bug.php?id=83957 for example, a bug that discusses the problems with this caching behavior.

You can disable the caching. It may cause queries against the INFORMATION_SCHEMA or SHOW TABLE STATUS to be a little bit slower, but I would guess it's no worse than in versions of MySQL before 8.0.

SET GLOBAL information_schema_stats_expiry = 0;

The integer value is the number of seconds MySQL keeps statistics cached. If you query the table stats, you may see old values from the cache, until they expire and MySQL refreshes them by reading from the storage engine.

The default value for the cache expiration is 86400, or 24 hours. That seems excessive.

See https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_information_schema_stats_expiry

Related Question