Issue: Model.create(...) possibly corrupting other items
Author: peepo5
State: closed
Created: 2022-04-19 08:49:35

I have a database where I store user information. I have an API endpoint using quart (async flask) where I can submit information to be put in a database. I have a weird bug however, where for some reason when I use my discord account's data, it updates every other user's data to be the same (except for discord_id which is primary key and also payments linked to that id) and also replaces any further inserts.

This may be a logic error, or could possibly be a peewee error somehow.

Here is an example of how data is changed.

#### Example 1

Discriminator on insert for person 1: 3606
Discriminator on insert for person x: 0661

Discriminators after person x inserted: 0232, 0232

#### Example 2

So I thought it was some indexing issue so I tried this:

Discriminator on insert for person 1: 3606
Discriminator on insert for person 2: 0434

Discriminators after person x inserted: 3606, 0434

Discriminator on insert for person x: 0232

Discriminators after person x inserted: 0232, 0232, 0232

This is very confusing to me, as the code to create a new user replaces the data of other users. As you see, person 2 does not change person 1 but person x does. However, this gets even more confusing, as when person 2 is inserted after person x, person 2 has the same data (except for primary key) as person x.

### Possible reasons

- Person x has "()" in their username, which is put in the database (EDIT: after changing my username, nothing changed)
- Person x is my current user (logic error in code as access token effects somehow?)
- Past that I do not know

### Relevant code
(mind my spaghet code)
```py
@QUART.route(API_PRE + "add_payment", methods=["POST"])
async def add_payment():
    """
    Add a new payment to existing or new user.
    """
    if request.method == "POST":
        request_parameters = await request.get_json(force=True, silent=True)
        discord_id = request_parameters["discord_id"]
        amount_payed_usd = request_parameters["amount_payed_usd"]
        subtype = request_parameters["subtype"]
        pre_payment = request_parameters["pre_payment"]
        if (
            request_parameters["discord_access_token"] is not None
            and discord_id is not None
            and amount_payed_usd is not None
            and subtype is not None
            and pre_payment is not None
        ):
            try:
                member = Member(request_parameters["discord_access_token"], MAIN_BOT)
                if member.is_manager == True:
                    user_data = MAIN_BOT.get_data_from_id(discord_id)

                    last_seen_username = user_data["user"]["username"]
                    print(last_seen_username)
                    last_seen_discriminator = user_data["user"]["discriminator"]
                    print(last_seen_discriminator)

                    # get pfp URL
                    if user_data["user"]["avatar"] is None:
                        last_seen_pfp = ""
                    else:
                        last_seen_pfp = f"https://cdn.discordapp.com/avatars/{discord_id}/{user_data['user']['avatar']}"
                    
                    # find or add user
                    user = User.get_or_none(User.discord_id == discord_id)
                    if user is None:
                        # suspect code
                        user = User.create(
                            discord_id=discord_id,
                            last_seen_username=last_seen_username,
                            last_seen_discriminator=last_seen_discriminator,
                            last_seen_pfp=last_seen_pfp,
                        )
                        debug(f"NEW USER ADDED BY MANAGER ADDITION ({discord_id})")

                    print(vars(user))

                    if pre_payment == True:
                        # pre-payment logic
                        update_q = User.select(User.discord_id==discord_id).update(pre_payment=True, pre_payment_amount_payed_usd=amount_payed_usd)
                        update_q.execute()

                        debug(f"ADDING PRE-PAYMENT ({subtype}) FOR {discord_id}")
                    else:
                        # non pre-payment logic
                        Payment.create(
                            discord_id=discord_id,
                            subtype=subtype,
                            ms_initiated=current_milli_time(),
                            amount_payed_usd=amount_payed_usd,
                        )

                        debug(f"PAYMENT COMPLETED (NOT PRE-PAYMENT) ({subtype}) for user {discord_id}")


                    return jsonify(is_error=False)
                else:
                    return jsonify(is_error=True, error_msg="You do not have the permission to use this endpoint")

            except Exception as error:
                MAIN_BOT.error_to_developer(error, "add_payment()")
                return jsonify(is_error=True, error_msg="Could not add payment (User Discord ID may be invalid)")
        else:
            return jsonify(is_error=True, error_msg="Required data not passed (access token)")
```

The only two operations here are .create and .update, but the the .update is, first of all, not even run when the bad operation happens (as I do not pass pre_payment as true), but also the update  is on a .select(). The only suspect code I see to make a difference is the User.create(...) clause.

Also for further reference, "access token" is a token that managers pass to the API via a web interface (which is checked for appropriate roles), checked by the Member class creation, however this is never referenced again past role check

By the way, it is not an issue about data creation, but one about possible data corruption either by my own error or peewee error, however I cannot conclude anything from this, but I would appreciate any support, thank you.

If you are able to write a unit-test or very small script that replicates the issue I will take a look.

I'd suggest logging SQL and running the problematic code, then feel free to paste the resulting SQL here in this issue.

```python
# at top of your main python module add this
import logging
logger = logging.getLogger('peewee')
logger.addHandler(logging.StreamHandler())
logger.setLevel(logging.DEBUG)
```

Then you can examine the SQL being executed when you hit your issue.

I was just about to ask if I should log the sql, you caught me before I could ask, I will check the code soon. Also, I do not understand why you closed this issue when the issue is still active, I understand you may want a clean repository but it is misrepresentative of the issue status. Anyways, will check it soon and return insights.

In absence of a traceback or a clear indication this is a Peewee issue, I tend to close stuff like this until it's proven to be a peewee problem (as it seems to me this is very likely user error). Fundamental things like inserts and atomic updates are very well-tested across sqlite/mysql/postgres/cockroachdb.

Alright, I guess I understand.

For the issue, I ran the logging code, however the terminal was giving me quite a lot due to my scheduled tasks, however, apart from the selects, here is what I got for the insert logs.

Person 1
```SQL
('INSERT INTO "user" ("discord_id", "telegram_id", "last_seen_username", "last_seen_discriminator", "last_seen_pfp", "subscription_warning_level", "telegram_code", "telegram_url", "pre_payment", "pre_payment_amount_payed_usd") VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)', ['937789306049802303', '', 'Farami', '3606', '', 0, '9591092', '', '', Decimal('0')])
('INSERT INTO "payment" ("discord_id", "subtype", "ms_initiated", "amount_payed_usd", "coinpayments_id", "coupon_used") VALUES (?, ?, ?, ?, ?, ?)', ['937789306049802303', 'htfbot', 1650385811538, '0', '', ''])
```

Person 2
```SQL
('INSERT INTO "user" ("discord_id", "telegram_id", "last_seen_username", "last_seen_discriminator", "last_seen_pfp", "subscription_warning_level", "telegram_code", "telegram_url", "pre_payment", "pre_payment_amount_payed_usd") VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)', ['485777016923226133', '', 'ati', '4675', '', 0, '9591092', '', '', Decimal('0')])
('INSERT INTO "payment" ("discord_id", "subtype", "ms_initiated", "amount_payed_usd", "coinpayments_id", "coupon_used") VALUES (?, ?, ?, ?, ?, ?)', ['485777016923226133', 'htfbot', 1650385933012, '0', '', ''])
```

Person x (overtaking all other data)
```SQL
('INSERT INTO "user" ("discord_id", "telegram_id", "last_seen_username", "last_seen_discriminator", "last_seen_pfp", "subscription_warning_level", "telegram_code", "telegram_url", "pre_payment", "pre_payment_amount_payed_usd") VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)', ['915560264353587211', '', 'friendly frog', '0661', '', 0, '8070074', '', '', Decimal('0')])
('INSERT INTO "payment" ("discord_id", "subtype", "ms_initiated", "amount_payed_usd", "coinpayments_id", "coupon_used") VALUES (?, ?, ?, ?, ?, ?)', ['485777016923226133', 'htfbot', 1650385933012, '0', '', ''])
```

If these provide no reason for the issue, let me know what other steps I can take, thanks (or if I should provide select queries).

I think I found something, payment insert for person x is not correct, i will have to check if i pasted correctly however. But just a bit of conversation - which operation can permanently change all future inserts? Seems like a weird thing. Also when I access the model fields directly they change to be the same. Confusing. I will check around for logic errors and will try to find something.

Alright I think I found the problem of the issue. It is when using:

user = User.get_or_none(...)

And then doing an update operation on that user like

user.update(...)

I think it effects all the users instead of one as seen below.

('UPDATE "user" SET "last_seen_username" = ?, "last_seen_discriminator" = ?, "last_seen_pfp" = ?', ['friendly frog', '0661', 'https://cdn.discordapp.com/avatars/915534564353587210/4a4e597510bcsfddf8f6d57c3543ee6fd'])

---

I feel like the functionality should be different. It would be nice to be able to get an item with get_or_none and then do operations on it without an extra .where.

Looking at the wrapper docs, I see there is item.delete_instance(...) but not item.update_instance(...) would be nice to have the second

You should probably use the more idiomatic `save()` instead of `update()`:

```python
user = User.get_or_none(...)
if user is not None:
    user.last_seen... = xyz
    user.save()
```

@peepopoggers If you prefer the SQL style over the ORM style:

    User.update(foo='bar').where(id=user.id)
    
But if you prefer the ORM style use the regular `save` like Coleifer mentioned.