`GET /_synapse/admin/v1/rooms` is often missing the room creator and instead provides an empty string

[Gnuxie]

Further, this implies that the rooms table is also often missing creator.

synapse/synapse/storage/databases/main/room.py

Lines 729 to 741 in 73a3838

	info_sql = """
	SELECT state.room_id, state.name, state.canonical_alias, curr.joined_members,
	curr.local_users_in_room, rooms.room_version, rooms.creator,
	state.encryption, state.is_federatable, rooms.is_public, state.join_rules,
	state.guest_access, state.history_visibility, curr.current_state_events,
	state.room_type
	FROM room_stats_state state
	INNER JOIN room_stats_current curr USING (room_id)
	INNER JOIN rooms USING (room_id)
	{where}
	ORDER BY {order_by} {direction}, state.room_id {direction}
	LIMIT ?
	OFFSET ?

synapse/synapse/rest/admin/rooms.py

Line 255 in 4a803e8

rooms, total_rooms = await self.store.get_rooms_paginate(

Seems to be missing it more often than not?

[Gnuxie]

This is important because it makes it a lot harder to block rooms by creator. it essentially ruins the possibility tbh

[Gnuxie]

It seems the issue is unrelated to room version 11's omission of the m.room.create creator property as we have verified that there rooms in the table with blank creator in all room versions.

[kegsay]

The creator is set to "" in https://github.com/element-hq/synapse/blob/develop/synapse/handlers/federation.py#L1118 - when you receive an invite for a federated room you didn't know about. Subsequent invites do not keep setting the creator to "" due to the DSL synapse uses for upserts:

        await self.db_pool.simple_upsert(
            desc="maybe_store_room_on_outlier_membership",
            table="rooms",
            keyvalues={"room_id": room_id},
            values={},
            insertion_values={
                "room_version": room_version.identifier,
                "is_public": False,
                # We don't worry about setting the `creator` here because
                # we don't process any messages in a room while a user is
                # invited (only after the join).
                "creator": "",
                "has_auth_chain_index": has_auth_chain_index,
            },
        )

where insertion_values is:

insertion_values: additional key/values to use only when inserting

The problem is when we actually join a room in do_invite_join we fail to update the creator field, and only update the room_version field as per upsert_room_on_join.

The fix is to actually replace the creator field when upserting, to remove "" which was set when receiving the invite. In practice this means moving creator from insertion_values to values.

        await self.db_pool.simple_upsert(
            desc="upsert_room_on_join",
            table="rooms",
            keyvalues={"room_id": room_id},
            values={
                "room_version": room_version.identifier,
                "creator": room_creator,
            },
            insertion_values={
                "is_public": False,
                # was "creator": room_creator,
                "has_auth_chain_index": has_auth_chain_index,
            },
        )

Unfortunately this need a background job to fix up all the affected rooms.

It looks like this broke in 3e99528 as the act of eagerly inserting a row into the rooms table broke the ability to insert the correct creator in do_invite_join.