Rails' default primary/foreign keys are different from schema.rb in MySQL

[mamhoff]

Steps to reproduce

Add a migration that uses t.references to an existing Alchemy table in an installation with MySQL. The migration will fail because the new foreign key will be different from the existing primary key.

Expected behavior

The foreign key column should be made with a foreign key.

Actual behavior

The foreign key cannot be created because of a type mismatch between :integer and :bigint

Proposed solutions

PR to Rails

We could PR to Rails that references takes the existing primary key into account. This would be best as it would also work for existing installations

Change default migration to not be a copy of schema.rb

Using schema.rb as a template for a migration seems to not yield identical results to using migrations as generated by rails g migrate. We could change the migration, and migrate on CI.

System configuration

• Alchemy Version:
• Rails Version:

[rmparr]

I have dug into this and found the issue:

Since Rails 5.1, ActiveRecord migrations use bigint as the default for primary keys (issue). As AlchemyCMS supports Rails 5.0 and forward, all of the migrations are >= 5.0.

One solution to make a fresh AlchemyCMS install use bigint is to change all occurrences of create_table to include id: :bigint, and then update all of the referenced fields to bigint (these are mostly in AlchemyFourPointZero, but also exist in some later migrations).

Alternatively, a user could update all of the migrations to >= 5.1, and then update all of the referenced fields as above.

Neither of the above would address existing applications. A migration to update existing applications could be very long running, depending on the size of the database, as every reference would have to be dropped, the fields' types updated, and then the foreign keys added back in.

As far as existing applications go, the solution I have found is to specify the type when creating the table:

create_table :children do |t|
  t.references :parent, type: :integer, null: false, foreign_key: true
end

[tvdeyen]

Using schema.rb as a template for a migration seems to not yield identical results to using migrations as generated by rails g migrate. We could change the migration, and migrate on CI.

@mamhoff we already migrate and create the correct schema on CI

Since Rails 5.1, ActiveRecord migrations use bigint as the default for primary keys. As AlchemyCMS supports Rails 5.0 and forward, all of the migrations are >= 5.0.

@rmparr we could drop Rails 5.0 support with Alchemy 5 and make all our migrations be [5.2]

Will prepare a PR for the Rails support drop. Rails 5.0 and 5.1 are out of official support anyway.