[5.x]: Running craft gc/run wipes the usertable

[BAM-Dennis]

What happened?

Description

Topic

Steps to reproduce

1. run craft gc/run

Expected behavior

Clean trashed item

Actual behavior

Cleans all of the stuff

Craft CMS version

5.0.5

PHP version

8.2

Operating system and version

No response

Database type and version

pgsql

Image driver and version

No response

Installed plugins and versions

"craftcms/ckeditor": "4.0.4",
"craftcms/cms": "5.0.5",
"craftpulse/craft-colour-swatches": "5.0.0",
"nystudio107/craft-imageoptimize": "5.0.0",
"nystudio107/craft-seomatic": "5.0.2",
"nystudio107/craft-vite": "5.0.0",
"putyourlightson/craft-blitz": "5.1.4",
"vaersaagod/dospaces": "3.0.0-beta.1",
"vlucas/phpdotenv": "^5.4.0",
"yiisoft/yii2-redis": "~2.0.0"

[brandonkelly]

It’s expected that the gc command will clear out several things in the database, which shouldn’t be needed anymore, including:

• User data which exists in the elements table, whose corresponding users table rows have already been deleted.
• Stale user data, per the purgePendingUsersDuration config setting.

Just tested locally and both of those are working as expected (and nothing more, as far as users go).

Can you elaborate on what you’re seeing?

[BAM-Dennis]

hey @brandonkelly

so what i did is, add a homepage entry, add some matrix fields add those to the homepage entry.

After playing around with the new amazing features, i changed some search indexes and used the cli command to resave all entries craft resave/entries --update-search-index i got some errors about some elements with invalid owner ids, so i ran craft gc/run to clean those.

After this i wasn't able to log in anymore, so i checked the cli. No entries anymore. The entry for the homepage section was deleted. My Admin user was deleted, basically everything was deleted, that was ion any relation to the before mentioned invalied elements.

[brandonkelly]

Do you happen to have a database backup from before running gc?

[BAM-Dennis]

Unfortunately not, as I only did a little testing I did not create a backup.

i also tried to reproduce it somehow but i think it could occur if there are entries whose ownerid no longer exists

[Anubarak]

@brandonkelly I have the same issue here,
gc/run ----deleteAllTrashed will clear the entire users table. I send a DB backup file with a composer.lock / json to support@craftcms.com

Let me know if you need further details

[Anubarak]

Our live DB ended up without any users this morning so I investigated a little bit more.
I was able to track down the issue to this query

delete from "elements"
USING "elements" "el"
INNER JOIN "entries" "t" ON "t"."id" = "el"."id"
LEFT JOIN "elements_owners" "eo" ON "eo"."elementId" = "el"."id"
WHERE "t"."fieldId" IS NOT NULL AND "eo"."elementId" IS NULL

in

cms/src/services/Gc.php

Lines 563 to 569 in 5f69bc4

	$sql1 = <<<SQL
	DELETE FROM $elementsTable
	USING $elementsTable [[el]]
	INNER JOIN $table [[t]] ON [[t.id]] = [[el.id]]
	LEFT JOIN $elementsOwnersTable [[eo]] ON [[eo.elementId]] = [[el.id]]
	WHERE [[t.$fieldFk]] IS NOT NULL AND [[eo.elementId]] IS NULL
	SQL;

When the function

$this->deleteOrphanedNestedElements(Entry::class, Table::ENTRIES);

is called here
https://github.com/craftcms/cms/blob/5f69bc4f699e6f97a90c057584e720a808c80b98/src/services/Gc.php#L130C9-L130C75

However when I try to select the elements that should be deleted with the following query

select el.id, el.type from "elements" "el"
INNER JOIN "entries" "t" ON "t"."id" = "el"."id"
LEFT JOIN "elements_owners" "eo" ON "eo"."elementId" = "el"."id"
WHERE "t"."fieldId" IS NOT NULL AND "eo"."elementId" IS NULL

it will only show 19 entries, so I have absolutely no idea why this query will delete all users

Additional note: I have about 30k elements in my table but the delete statement takes about 5 minutes, while the select statement executes immediately

explain analyze delete from "elements"
USING "elements" "el"
INNER JOIN "entries" "t" ON "t"."id" = "el"."id"
LEFT JOIN "elements_owners" "eo" ON "eo"."elementId" = "el"."id"
WHERE "t"."fieldId" IS NOT NULL AND "eo"."elementId" IS NULL

will output the following data

Delete on elements (cost=1180.46..2718468.43 rows=217233170 width=24) (actual time=262043.882..262043.891 rows=0 loops=1)
-> Nested Loop (cost=1180.46..2718468.43 rows=217233170 width=24) (actual time=384.356..618.586 rows=574142 loops=1)
-> Seq Scan on elements (cost=0.00..832.12 rows=31912 width=6) (actual time=363.176..372.631 rows=30218 loops=1)
-> Materialize (cost=1180.46..2341.03 rows=6807 width=18) (actual time=0.001..0.004 rows=19 loops=30218)
-> Hash Anti Join (cost=1180.46..2306.99 rows=6807 width=18) (actual time=21.160..41.528 rows=19 loops=1)
Hash Cond: (el.id = eo."elementId")
-> Hash Join (cost=618.64..1534.55 rows=9830 width=16) (actual time=8.219..25.414 rows=9076 loops=1)
Hash Cond: (el.id = t.id)
-> Seq Scan on elements el (cost=0.00..832.12 rows=31912 width=10) (actual time=0.040..9.021 rows=30218 loops=1)
-> Hash (cost=495.77..495.77 rows=9830 width=10) (actual time=7.920..7.922 rows=9076 loops=1)
Buckets: 16384 Batches: 1 Memory Usage: 518kB
-> Bitmap Heap Scan on entries t (cost=116.47..495.77 rows=9830 width=10) (actual time=0.698..4.905 rows=9076 loops=1)
Recheck Cond: ("fieldId" IS NOT NULL)
Heap Blocks: exact=211
-> Bitmap Index Scan on idx_mubjgrepekrddpavubvsiuhrxzgqufomucgn (cost=0.00..114.01 rows=9830 width=0) (actual time=0.644..0.644 rows=9830 loops=1)
Index Cond: ("fieldId" IS NOT NULL)
-> Hash (cost=310.25..310.25 rows=20125 width=10) (actual time=12.308..12.309 rows=19141 loops=1)
Buckets: 32768 Batches: 1 Memory Usage: 1079kB
-> Seq Scan on elements_owners eo (cost=0.00..310.25 rows=20125 width=10) (actual time=0.066..5.399 rows=19141 loops=1)
Planning Time: 4.380 ms
JIT:
Functions: 23
Options: Inlining true, Optimization true, Expressions true, Deforming true
Timing: Generation 4.881 ms, Inlining 117.097 ms, Optimization 155.081 ms, Emission 90.437 ms, Total 367.496 ms
Execution Time: 262129.999 ms

[brandonkelly]

Thanks @Anubarak! We managed to reproduce with your database, and just tagged Craft 4.12.3 and 5.4.4 with a fix.