0

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

5
  • have you inserted a new row, to test if the new value is used.before using the command. Besides a query tab is also part of phpmyadmin and the you could run your alter command – nbk Oct 1 '20 at 18:35
  • 1
    i check that phpmyadmin show a ghsot value in auto_increment; is described in this post: github.com/phpmyadmin/phpmyadmin/issues/16378 – gestion soporte Oct 1 '20 at 18:36
  • It is usually "wrong" to change the AI value. Please describe your reason for doing it. – Rick James Oct 13 '20 at 19:39
  • Having 3 UNIQUE keys on a table is usually "wrong" database design. What is the reason? (Note: A PRIMARY KEY is a UNIQUE key, so you have a redundant index on IdAgency.) – Rick James Oct 13 '20 at 19:40
  • I have 3 unique columns that you should not interpret as if they were PK; They are keys that should not be repeated at any time, and I do not understand why you say that the PK is redundant if I have only one IdAgency there is no other PK. do you know the difference between PK and Unique Key? – gestion soporte Oct 13 '20 at 20:07
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.