0

The table's AUTO_INCREMENT property does not increase by any value every after INSERT - it is stuck on a specific integer value.

I'm testing my project on my Local Computer. As it is, i have a (After INSERT) Trigger that is highly dependent on the AUTO_INCREMENT property of a table as i am updating a field and inserting a string with the LAST INSERTED ID appended as a Suffix. However, no matter how many times i run an INSERT Query, the AUTO_INCREMENT value does not increment.

Here is what the table's content looks like: table content

And this is the table's properties/option values: table properties/option values

I have also consulted information_schema.TABLES but it also says that AUTO_INCREMENT has the value of 4 - which should technically be 5... right?: information_schema.TABLES

i am worried that this issue might get replicated on the Production Server, as this - my trigger is highly dependent on "that" AUTO_INCREMENT's value. Having it not solved would result in duplicating values, which is unacceptable to the system i am working on. This is the Trigger Definition that is dependent on the AUTO_INCREMENT value: Trigger Definition

i expect that the AUTO_INCREMENT value should equal the table's ID field with Auto Increment enabled. in this case they (the ID and the AUTO_INCREMENT value should both be the value of 5.

my database is a copy of the live database which has been dumped into a .sql file and restored on my local mysql instance:

CREATE TABLE `imei`  (
  `imei_id` int(11) NOT NULL AUTO_INCREMENT,
  `identity_imei` varchar(15) CHARACTER SET utf8 COLLATE utf8_general_ci GENERATED ALWAYS AS ((case when (`imei_from` = _utf8mb3'board') then `board_imei` when (`imei_from` = _utf8mb3'dcover') then `auto_generated_imei` when (`imei_from` = _utf8mb3'sim_tray') then `auto_generated_imei` when (`imei_from` = _utf8mb3'sticker') then `auto_generated_imei` end)) VIRTUAL NOT NULL,
  `board_imei` varchar(15) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `auto_generated_imei` varchar(15) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '',
  `expected_imei` varchar(15) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `imei_from` enum('board','dcover','sim_tray','sticker') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `generated_by_id` int(11) NOT NULL,
  `date_generated` datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`imei_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 21 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
Nii
  • 450
  • 6
  • 25
  • If you do an insert with an explicit `imei_id`, it won't auto-increment. It only increments when you leave out that column or specify `NULL` as the value. – Barmar Feb 04 '19 at 22:21
  • @Barmar i have tried specifying both `NULL` and `DEFAULT` as the value for `imei_id` and nothing works. Also, i have noticed that this issue only happens on copied databases, the original one on the server where it was created, doesn't experience the same issue. – Nii Feb 04 '19 at 22:26
  • When you copied the table, it filled in all the `imei_id` columns explicitly from the original table. So they don't necessarily match the auto increment value. The next time you insert something it will notice the discrepancy and fix it. – Barmar Feb 04 '19 at 22:28
  • i have suspected the same thing. however, no matter how many times i insert to the same table - the AUTO_INCREMENT value does not increment. it remains on the original value as it was prior to importing the .sql file – Nii Feb 04 '19 at 22:31

2 Answers2

2

I assume you want auto_generated_imei to be based on the generated id in imei_id on the same row.

But your trigger is a BEFORE INSERT trigger, which means the auto-increment value for the current row hasn't been generated yet.

If you were to use an AFTER INSERT trigger, it's too late to modify auto_generated_imei. You can't SET the value of any NEW.* columns in an AFTER trigger, because it executes after the row has already been inserted.

You also can't reliably read the correct AUTO_INCREMENT value from INFORMATION_SCHEMA in the way you're doing. The table's AUTO_INCREMENT is not limited by transaction scope. Any other inserts run in concurrent sessions will increment the AUTO_INCREMENT value, independently of your current session's transaction. You can't use this to pre-read the id that your INSERT will use for the current row it's inserting. You have a race condition.

The only solution is to do your INSERT without the value for auto_generated_imei. Let the AUTO_INCREMENT do its work to generate one id, and then follow the INSERT with an UPDATE to change the auto_generated_imei column to the format you want, based on LAST_INSERT_ID() which will report the correct value in the session scope you call it from.

This problem is related to these past answers:

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • however what bugs me is the fact that this only happens on the database copies on local machines. the original one on the main server where the schema was created has its AUTO_INCREMENT property work as expected. Anyway, what you're trying to tell me is that there is no better way than to run 2 queries? `INSERT` then get the inserted ID via `LAST_INSERT_ID()` then `UPDATE` the row via that `ID`? – Nii Feb 05 '19 at 09:43
1

Try

ANALYZE TABLE `my_table`;
SELECT AUTO_INCREMENT FROM information_schema.TABLES WHERE (TABLE_NAME = 'my_table');

This clear cache for table, in BD

Vanom
  • 31
  • 1