CODEWITHSUNDEEP
mysqlauto-increment
eastwater
eastwater

Reputation: 5630

MySql: AUTO_INCREMENT is missing from tables

MySql: AUTO_INCREMENT is missing from some tables after running for about one month.

Initially: (show create table Foo)

CREATE TABLE `Foo` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) NOT NULL,
  `type` tinyint(2) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8

After one month:

CREATE TABLE `Foo` (
  `id` bigint(20) NOT NULL,
  `name` varchar(10) NOT NULL,
  `type` tinyint(2) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

AUTO_INCREMENT is missing. What is the cause?

Mysql Server version: 5.6.25, Linux

Upvotes: 0

Views: 2473

Answers (5)

samhaz
samhaz

Reputation: 1

The same for me - in production - migrating from MySQL to MySQL. There's a bug in with MySQL: https://bugs.mysql.com/bug.php?id=96231&thanks=3&notify=195

Working fix by:

  • removing all foreign keys in all related tables with data
  • modifying the auto-increment field in the affected table (ALTER TABLE tablename MODIFY fieldname INTEGER NOT NULL AUTO_INCREMENT;)
  • re-creating all foreign keys removed. (I used the same key names etc.)

If related tables have no data yet there's no need to remove the foreign key first.

Upvotes: 0

anoldermark
anoldermark

Reputation: 381

Yes happened to me to. Only on my dev machine so not a disaster yet. I think it can do this when the system crashes OR possibly when the machine is simply shut down but MySql has not been shut down first. It never used to happen ever. Maria DB 10.4.27

Upvotes: 0

Roger Krueger
Roger Krueger

Reputation: 303

Happened exactly the same way to me. Not doing any work that involves database changes. No one else with database edit access. Suddenly I have user complaints AI is turned off for a field it's been running successfully on for seven years/10k inserts. Aurora (AWS clone of MySQL 5.6) not MySQL per SE.

At least this is a management-facing table. Terrified of this happening to a customer transaction table.

Upvotes: 0

code_kbd
code_kbd

Reputation: 486

Just a thought.

If you have binary logs, you may see the alter query on the logs and when it was run. :) Check if the binary log is enabled by

show variable like 'log_bin';

If binary log is enabled, find the likely period that the query could have been executed and then use mysqlbinlog to help you find it.

If binary log is not enabled, bad luck - as the previous post by Bill Karwin has suggested mysql does not change it on its own - someone must have changed it.

Upvotes: 1

Bill Karwin
Bill Karwin

Reputation: 562791

Someone must have changed it. This change does not happen spontaneously.

I can reproduce this change myself:

CREATE TABLE Foo ( id BIGINT AUTO_INCREMENT, ...

ALTER TABLE Foo MODIFY COLUMN id BIGINT;

SHOW CREATE TABLE Foo\G

*************************** 1. row ***************************
       Table: foo
Create Table: CREATE TABLE `foo` (
  `id` bigint(20) NOT NULL,
  `name` varchar(10) NOT NULL,
  `type` tinyint(2) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Now the column shows it is BIGINT but not AUTO_INCREMENT.

Every time you MODIFY COLUMN or CHANGE COLUMN, you must repeat all the column options like NOT NULL and AUTO_INCREMENT and DEFAULT, or else it will revert to defaults (i.e. not auto-increment).

So I would interpret this shows that someone did an ALTER TABLE and didn't remember to include the AUTO_INCREMENT column option.

Upvotes: 3

Related Questions