Auto increment does not work from rails

2024-04-20

Tested on: rails7.1 + mysql8.0

No how the trouble was appeared. One of my system started to insert new record with ID=0. In Rails model, the ID is expected to be generated automatically. But now it's always trying to insert 0 which causes duplicated key error.

The unique point of this model is that the table exists as an view on MySQL server(to share among multiple systems). So I tested the difference between the cases.

First, create a simple model.

rails g model post title
rails db:migrate

And create a record. (the insert query is generated without ID column)

irb> Post.create(title: "first")
  Post Create (0.5ms)  INSERT INTO "posts" ("title", "created_at", "updated_at") VALUES (?, ?, ?) RETURNING "id"  [["title", "first"], ["created_at", "2024-04-20 00:54:13.695904"], ["updated_at", "2024-04-20 00:54:13.695904"]]

On the database console, replace the table with the view.

alter table posts rename to posts_table;
create view posts as select * from posts_table;

Then trying to create the second record.

irb> Post.create(title: "second")
  Post Create (0.3ms)  INSERT INTO "posts" ("id", "title", "created_at", "updated_at") VALUES (?, ?, ?, ?)  [["id", nil], ["title", "second"], ["created_at", "2024-04-20 00:56:23.190896"], ["updated_at", "2024-04-20 00:56:23.190896"]]

This time, the insert query is generated with ID column