MySQL Schema Issue inside User table and Enrollment table

[csandrew-dev]

Environment

• M1 Pro MacBook Pro
• nanomdm v0.8.0

$ mysql --version
mysql  Ver 9.4.0 for macos15.4 on arm64 (Homebrew)

Error

When trying to import the mysql schema into my mysql db. I receive the following error:

$ mysql -u root nanomdm < schema.sql
ERROR 6125 (HY000) at line 79: Failed to add the foreign key constraint. Missing unique key for constraint 'enrollments_ibfk_2' in the referenced table 'users'

Possible fix

In the mysql schema.sql file, on line 64 add UNIQUE(id),

nanomdm/storage/mysql/schema.sql

Lines 60 to 67 in 93afb5d

	created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
	updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
	PRIMARY KEY (id, device_id),
	FOREIGN KEY (device_id)
	REFERENCES devices (id)
	ON DELETE CASCADE ON UPDATE CASCADE,

After changing that line and removing the artifacts left in the DB after a partially ran sql script, it seems to build the DB fine and work.

Let me know what you think!

[jessepeterson]

Hmm. The point of the primary key being a compound key is to not require ID to be unique. I'll have to try this out — don't think I've seen this one before.

[csandrew-dev]

I don't have a 100% understanding of the consequences of changing that one line is. I only tested with a device enrollment so the user table is empty. But if that isn't what is needed, I think something needs to change in the enrollments table's foreign keys:

nanomdm/storage/mysql/schema.sql

Lines 112 to 121 in 93afb5d

	FOREIGN KEY (device_id)
	REFERENCES devices (id)
	ON DELETE CASCADE ON UPDATE CASCADE,
	FOREIGN KEY (user_id)
	REFERENCES users (id)
	ON DELETE CASCADE ON UPDATE CASCADE,
	UNIQUE (user_id),

Since the primary key in the users table is both the id and device_id:

nanomdm/storage/mysql/schema.sql

Line 63 in 93afb5d

PRIMARY KEY (id, device_id),

Thanks for checking it out.

EDIT:
Making this change also removes the error, again not entirely sure of the consequences of these changes.

Inside the enrollments table

    FOREIGN KEY (user_id, device_id)
        REFERENCES users (id, device_id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    UNIQUE (user_id, device_id),

[jessepeterson]

So I still haven't had a chance to review this. But I know we regular CI/CD that runs a mysql test which includes creating the schema every change. I wonder what's different? https://github.com/micromdm/nanomdm/actions/runs/18295736515/job/52093749091

[jessepeterson]

As a super quick test I wonder if we can duplicate the issue by simply bumping the MySQL version in the GH action from mysql:8.0 to mysql:9?

[csandrew-dev]

I tested this in my fork. It failed. I can submit the pr here to test here, up to you.

[jessepeterson]

@csandrew-dev aha so seems mysql9 is giving issues.

[csandrew-dev]

Yep, sounds like MySQL set stricter rules on foreign keys. So I think a minor schema change is in needed.

Why is the device id needed in the composite PK for the users table? Is this for a specific type of enrollment?