1.63.0-g migration fails on MariaDB 11.4 (blob_id bigint mismatch)

[anthony-arnoux]

🐛 Bug Report

pwpush fails on db:migrate with pglombardo/pwpush:1.63.0-g and mariadb:11.4.

✅ Expected

Container pwpush starts with successful database migration.

❌ Actual

ActiveRecord migration fails on Active Storage foreign key (fresh install) :

ActiveRecord::MismatchedForeignKey: Column "blob_id" on table "active_storage_attachments"
does not match column "id" on "active_storage_blobs" (type bigint(20)).
To resolve: change "blob_id" to :bigint.
Original: Mysql2::Error: Can"t create table "db"."active_storage_attachments"
(errno: 150 "Foreign key constraint is incorrectly formed")

🔬 How To Reproduce (docker-compose.yml)

services:
  pwpush:
    container_name: pwpush
    image: pglombardo/pwpush:1.63.0-g
    restart: unless-stopped
    environment:
      - DATABASE_URL=mysql2://user:password@pwpush-db:3306/db
    depends_on:
      - pwpush-db
    volumes:
      - /data/pwpush/storage:/opt/PasswordPusher/storage

  pwpush-db:
    container_name: pwpush-db
    image: mariadb:11.4
    restart: unless-stopped
    environment:
      MARIADB_USER: "user"
      MARIADB_PASSWORD: "password"
      MARIADB_DATABASE: "db"
      MARIADB_RANDOM_ROOT_PASSWORD: "yes"
    volumes:
      - /data/pwpush/db:/var/lib/mysql

🧩 Environment

OS: Debian 13 amd64
Docker: 28.5.1
Images:

• pwpush: pglombardo/pwpush:1.63.0-g
• mariadb: mariadb:11.4

🧠 Hypothesis (IA)

active_storage_attachments.blob_id created as int(11) while active_storage_blobs.id is bigint(20), producing a type mismatch and preventing foreign key creation.

🛠️ Workarounds (IA)

ALTER TABLE active_storage_attachments MODIFY blob_id BIGINT(20) NOT NULL;

[pglombardo]

Thanks for testing and reporting this @anthony-arnoux! Allow me some time and I'll post back soon.

[pglombardo]

@ozovalihasan could you take a look at this when you have a chance? This is a blocker for us to release v1.63.0-g. When I looked, it seemed like we would have to rewrite indexes for MariaDB/Mysql but then I got pulled away. I want to limit risky changes so let's figure out best path for now.

[ozovalihasan]

Hi all,

@anthony-arnoux Thanks for reporting this issue.

@pglombardo I reproduced this issue on my local with MySQL database settings;
-> Update v1.62.1 with following changes;

# Gemfile

- gem "rails", "~> 7.2.2"
+ gem "rails", "8.0.1"
- gem "rails-i18n", "~> 7.0.10"
+ gem "rails-i18n", "~> 8.0.0"
- gem "administrate", "~> 0.20.1"
+ gem "madmin"

-> if database exists, run bin/rails db:drop
-> run bin/rails db:setup

then this issue is shown. Rails v8.0.1 has some changes on Active Record. Possibly, one of these changes is causing this issue.

I will inform you when I find a solution.

[pglombardo]

Just an update on this bug: MySQL/MariaDB has always been the problem child database for this project historically and this latest issue has been blocking the v1.63.0 release for way too long.

I wrote up a summary here but in short, we are not going to support MySQL/MariaDB going forward.

We will be updating documentation and examples in the coming weeks.

@anthony-arnoux Since this is a fresh installation, can you deploy with PostgreSQL instead?

[adepretis]

@pglombardo did you try to reproduce this issue with Vanilla MySQL or Percona? - In my point of view MariaDB drifted too far away from MySQL to be considered as "MySQL/MariaDB". Percona on the other hand is quite level with Vanilla MySQL, including versioning.

[pglombardo]

Hi @adepretis - that's a fair point. I haven't tried other mysql-like databases yet. It's on my list to loop back to this.

[xpufx]

I did some reading about (errno: 150 "Foreign key constraint is incorrectly formed") as reported by @anthony-arnoux "active_storage_attachments.blob_id created as int(11) while active_storage_blobs.id is bigint(20), producing a type mismatch and preventing foreign key creation."

As per https://medium.com/@DanialEskandari/resolving-mysql-error-150-incorrectly-formed-foreign-key-constraint-4b5e231d46f8 and other sources this indeed seems to be the case and the type of the fields need to match. I doubt that requries rewriting indexes. It might be as simple as altering the int(11) field to be a bigint(20) too. Don't know how postgres and sqlite would like this change of course.

I wanted to chime in because of your decision to drop MySQL. I appreciate that this one bug is not the sole reason but requiring foreign keys to have the same type kind of seems reasonable to me. :) Wouldn't we have unpredictable results after the id is bigger than int(11) as it stands? If that is not going to happen then perhaps converting the big boy to be an int(11) would be less invasive.

I would imagine mariadb would also have this by the way. A cursory search confirms this but not conclusively.

In addition. As per https://dev.mysql.com/doc/refman/8.4/en/numeric-type-attributes.html the number in parentheses is "display width" not something that concerns what the size these integers fields can hold meaning a conversion would probably be straightforward. No zero padding or the like to worry about. This was news to me, so sharing just in case.

Can somebody who's already set up for testing just convert the bigint to int and see what happens?

[pglombardo]

Hi @xpufx - this is exactly the issue.

For other databases (SQLite3, PostgreSQL etc.), these DB specifics are hidden away by ActiveRecord so we don't have to have if..then..else statements everywhere for every database. But for MySQL this has been a type of repeat issue that often needs special treatment in database migrations.

Not all hope is lost though. It's on my list to revisit this. When I filed this issue, there wasn't much commentary about this in the Rails community that I could find. My hope is that will have changed by the time I revisit this.

I appreciate the research above. I'll use it as reference soon once I clear a few current tasks. I know the direct DB commands it would take to fix this but that might in turn break ActiveRecord so TBD.

For those who may want to migrate away from MySQL, there is pgloader which claims to populate a new PostgreSQL database from a MySQL source. It looks slick but has never been tested (yet). If anyone feels adventurous...

So this issue is open but the research/investigation is still pending...