🐛 Bug Report: Deleting an Organization doesn't Delete Projects

[stnguyen90]

I ran into another issue that if I created an organization then create a project with fixed ID, let say, test. After that I delete the organization. I can no longer use 'test' as project ID ever again, even in another organization.

Originally posted by @meepeek in #4966 (comment)

👟 Reproduction steps

1. Create an Organization
2. Create a Project with a custom ID (like delete)
3. Delete the Organization
4. Recreate the Project from step 2

👍 Expected behavior

The project is created.

👎 Actual Behavior

Server Error:

appwrite logs:

[Error] Timestamp: 2023-01-13T21:59:45+00:00
[Error] Method: POST
[Error] URL: /v1/projects
[Error] Type: Utopia\Database\Exception\Duplicate
[Error] Message: Duplicated document: Duplicate entry 'delete' for key '_uid'
[Error] File: /usr/src/code/vendor/utopia-php/database/src/Database/Adapter/MariaDB.php
[Error] Line: 547

The project is not deleted from the _console_projects table:

MariaDB [appwrite]> select _id as "Internal ID", _uid as "Project ID", name as "Project Name", teamId as "Organization ID" from _console_projects;
+-------------+----------------------+----------------+----------------------+
| Internal ID | Project ID           | Project Name   | Organization ID      |
+-------------+----------------------+----------------+----------------------+
|           1 | 63bf36e5906da6c28fe3 | Project Test 2 | 63bf36e58edb72109000 |
|           2 | 63bf36e5cd58640413a1 | Project Test   | 63bf36e58edb72109000 |
|           3 | 63bf36e6199aaa841d6b | Project Test 2 | 63bf36e618743399e3c0 |
|           4 | 63bf372e1e2bf7b4379b | Project Test   | 63bf372e1ce56da2637c |
|           6 | 63bf3a492dcf9b6301f5 | Project Test 2 | 63bf3a492c60fd3b97bb |
|           7 | 63bf3a496ff04b20f178 | Project Test   | 63bf3a492c60fd3b97bb |
|           8 | 63bf3a49ada3c7ae2c12 | Project Test 2 | 63bf3a49ac8b37992212 |
|           9 | 63bf3a91bca3ab9fa040 | Project Test   | 63bf3a91bb4ed88f9a9c |
|          11 | 63bf3b0b3e9b43c2a619 | Project Test 2 | 63bf3b0b3d93b9b56ddb |
|          12 | 63bf3b0b7e869d1c8476 | Project Test   | 63bf3b0b3d93b9b56ddb |
|          13 | 63bf3b0bc94b56aae0d4 | Project Test 2 | 63bf3b0bc817a18ff46f |
|          14 | 63bf3b540338163c8209 | Project Test   | 63bf3b5401d43c2ffdb8 |
|          16 | test                 | Test           | 63bf3b953e9b465a5946 |
|          17 | delete               | delete         | 63c1d430688de7cae0b4 |
+-------------+----------------------+----------------+----------------------+
14 rows in set (0.001 sec)

The project tables are not deleted:

MariaDB [appwrite]> show tables like '_17_%';
+----------------------------+
| Tables_in_appwrite (_17_%) |
+----------------------------+
| _17__metadata              |
| _17__metadata_perms        |
| _17_abuse                  |
| _17_abuse_perms            |
| _17_attributes             |
| _17_attributes_perms       |
| _17_audit                  |
| _17_audit_perms            |
| _17_buckets                |
| _17_buckets_perms          |
| _17_builds                 |
| _17_builds_perms           |
| _17_cache                  |
| _17_cache_perms            |
| _17_certificates           |
| _17_certificates_perms     |
| _17_databases              |
| _17_databases_perms        |
| _17_deployments            |
| _17_deployments_perms      |
| _17_domains                |
| _17_domains_perms          |
| _17_executions             |
| _17_executions_perms       |
| _17_functions              |
| _17_functions_perms        |
| _17_indexes                |
| _17_indexes_perms          |
| _17_keys                   |
| _17_keys_perms             |
| _17_memberships            |
| _17_memberships_perms      |
| _17_platforms              |
| _17_platforms_perms        |
| _17_projects               |
| _17_projects_perms         |
| _17_realtime               |
| _17_realtime_perms         |
| _17_sessions               |
| _17_sessions_perms         |
| _17_stats                  |
| _17_stats_perms            |
| _17_teams                  |
| _17_teams_perms            |
| _17_tokens                 |
| _17_tokens_perms           |
| _17_users                  |
| _17_users_perms            |
| _17_variables              |
| _17_variables_perms        |
| _17_webhooks               |
| _17_webhooks_perms         |
+----------------------------+
52 rows in set (0.008 sec)

appwrite-worker-deletes logs only show the deleted org/team:

appwrite-worker-deletes  | [notice] Starting work on (Job{v1-deletes} | ID: 43d2ff1389722ece8ba2693dfea291b0 | DeletesV1 | [{"project":{"$id":"console","$internalId":"console","name":"Appwrite","$collection":"projects","description":"Appwrite core engine","logo":"","teamId":-1,"webhooks":[],"keys":[],"platforms":[{"$collection":"platforms","name":"Localhost","type":"web","hostname":"localhost"},{"$collection":"platforms","name":"Current Host","type":"web","hostname":"localhost"}],"legalName":"","legalCountry":"","legalState":"","legalCity":"","legalAddress":"","legalTaxId":"","auths":{"limit":0,"duration":31536000},"authWhitelistEmails":[],"authWhitelistIPs":[]},"type":"document","document":{"name":"Delete","total":1,"search":"63c1d430688de7cae0b4 Delete","$id":"63c1d430688de7cae0b4","$internalId":"14","$createdAt":"2023-01-13T21:59:12.428+00:00","$updatedAt":"2023-01-13T21:59:12.428+00:00","$permissions":["read(\"team:63c1d430688de7cae0b4\")","update(\"team:63c1d430688de7cae0b4\/owner\")","delete(\"team:63c1d430688de7cae0b4\/owner\")"],"$collection":"teams"},"resource":null,"datetime":null,"hourlyUsageRetentionDatetime":null}])
appwrite-worker-deletes  | Deleting chunk #1. Found 0 documents
appwrite-worker-deletes  | Deleted 0 document by group in 0.00057506561279297 seconds
appwrite-worker-deletes  | [notice] (Job{v1-deletes} | ID: 43d2ff1389722ece8ba2693dfea291b0 | DeletesV1 | [{"project":{"$id":"console","$internalId":"console","name":"Appwrite","$collection":"projects","description":"Appwrite core engine","logo":"","teamId":-1,"webhooks":[],"keys":[],"platforms":[{"$collection":"platforms","name":"Localhost","type":"web","hostname":"localhost"},{"$collection":"platforms","name":"Current Host","type":"web","hostname":"localhost"}],"legalName":"","legalCountry":"","legalState":"","legalCity":"","legalAddress":"","legalTaxId":"","auths":{"limit":0,"duration":31536000},"authWhitelistEmails":[],"authWhitelistIPs":[]},"type":"document","document":{"name":"Delete","total":1,"search":"63c1d430688de7cae0b4 Delete","$id":"63c1d430688de7cae0b4","$internalId":"14","$createdAt":"2023-01-13T21:59:12.428+00:00","$updatedAt":"2023-01-13T21:59:12.428+00:00","$permissions":["read(\"team:63c1d430688de7cae0b4\")","update(\"team:63c1d430688de7cae0b4\/owner\")","delete(\"team:63c1d430688de7cae0b4\/owner\")"],"$collection":"teams"},"resource":null,"datetime":null,"hourlyUsageRetentionDatetime":null}]) has finished

🎲 Appwrite version

Version 1.2.x

💻 Operating system

MacOS

🧱 Your Environment

Tested using Appwrite running from source

👀 Have you spent some time to check if this issue has been raised before?

• I checked and didn't find similar issue

🏢 Have you read the Code of Conduct?

• I have read the Code of Conduct

[zvikfir]

I was able to reproduce the issue. Can I work on this?