Redis keys not unique to database when executing 0043_normalize_many_to_many

[melton-jason]

Describe the bug

California Academy of Sciences runs five Specify collections (botany, ich,
iz, ornmam, herp, geo) against a single MariaDB instance. Our Specify 7
stack has one worker container per collection, all sharing one Redis
container.

The bug

In specifyweb/specify/migrations/0043_normalize_many_to_many.py, the Redis
key used to stage rows during the "store -> drop -> recreate -> reload"
cycle is not qualified by database name:

def redis_table_key(table: str):
return f"migration:0043:{table}"

When three of our workers ... ran the migration in parallel
at the exact same second (confirmed via
information_schema.tables.update_time = '2026-04-12 06:02:12' on all three
databases), they all wrote their specifyuser_spprincipal rows into the
same Redis SET: migration:0043:specifyuser_spprincipal. The set ended up
containing a merged union of rows from all three databases.

From @foozleface (Joe Russack) at California Academy of Sciences

specify7/specifyweb/specify/migrations/0043_normalize_many_to_many.py

Lines 215 to 216 in 797c919

	def redis_table_key(table: str):
	return f"migration:0043:{table}"

To Reproduce
Steps to reproduce the behavior:

1. Have a setup with two or more Specify instances connecting to the same Redis database
2. For all Specify instances, ensure there is more than one record in the autonumsch_coll, autonumsch_dsp, autonumsch_div, specifyuser_spprincipal, spprincipal_sppermission, sp_schema_mapping, and/or project_colobj tables
3. Start the Specify deployment, and ensure the Specify instances run the specify.0043_normalize_many_to_many at the same time
4. See that that the records re-inserted into the tables is a union of records in the table from all Specify instances

Expected behavior
The records should be re-inserted exactly

Reported By
Joe Russack (@foozleface) from California Academy of Sciences

Additional Context and Proposed Solution

There was some steps to help generally alleviate this problem in #7761 (see below format_key):

specify7/specifyweb/backend/redis_cache/utils.py

Lines 15 to 27 in 797c919

	@overload
	def format_key(key: str) -> str: ...
	@overload
	def format_key(key: bytes) -> bytes: ...
	def format_key(key: str | bytes) -> str | bytes:
	"""Formats key to avoid collisions when specify instances are sharing a cache.
	Expected format: specify:{database}:app:name"""
	if isinstance(key, bytes):
	return key
	db_name = getattr(settings, "DATABASE_NAME")
	return key.format(database=db_name)

However, the solution still requires consumers of the Redis API to intentionally insert a {database} format in their key names, which can be as equally cumbersome as prefixing the desired key names with the database name.
I think we should generally take this idea and extend it further: that is, entirely handle uniquifying the Redis keys at the "lower-level" of the Redis adapter for Specify, so consumers of the API never have to be concerned with manually handling such keys.

I propose we push a targeted simple solution that is focused on resolving the bug while minimizing potential impact to other components and tag it 7.12.0.2.
We can then implement a more general solution to this Issue in v7.12.1 that can be more thoroughly tested.

[foozleface]

Might want to alert the community about deploying the new release until this is incorporated; it's a data loss bug. quick turnaround!

-Joe

[melton-jason]

Thank you for finding the Issue so quickly, Joe! This is a pretty embarrassing Issue to miss... 🙈
I've pushed v7.12.0.2 that includes a fix for the migration. The Dockerize GitHub Action has already finished, so pulls from latest, v7, v7.12, and v7.12.0 as of April 12, 11:31 PM (23:31) UTC will already include the fix.
I'll see if anyone else on the team is available tonight to send such an announcement, but it may be early tomorrow.

I did some testing locally and it seems to have resolved the Issue. I'll do some more comprehensive testing to make sure everything else is ok and there's no subtle edge cases we're missing.
The "meat" of the better, more general fix is already done: I'll be opening up a PR soon that should be incorporated into the next general release. (This is one that's definitely getting some unit tests).