Laravel Timestamp Being Updated Without Explicit Call To Do So

Question

So I'm running into an annoying problem with Laravel update and save. I have a model/table Invoice and invoices, that has a timestamp sent_at.

Invoice.php

class Invoice extends Model {
    protected $dates = [
        "sent_at",
    ];
}

I have the following function that updates the Invoice:

InvoicesController.php:

public function postPayInvoice(Request $request, $invoiceId){
    $user = $this->apiResponse->user;

    $invoiceItemIds = $request->input("invoice_item_ids");

    $invoice = Invoice::with(["invoiceItems" => function($subQuery) use($invoiceItemIds){
        return $subQuery->whereIn("invoice_items.id", $invoiceItemIds);
    }])->where("id", "=", $invoiceId)->first();

    \Log::info("Load: ".$invoice->sent_at);

    DB::beginTransaction();
    try {
        foreach($invoice->invoiceItems AS $invoiceItem){
            $invoiceItem->status = "paid";
            $invoiceItem->paid_at = Carbon::now();
            $invoiceItem->save();
        }

        $totalInvoices = $invoice->invoiceItems()->count();
        $paidInvoiceItems = $invoice->invoiceItems()->where("status", "=", "paid")->count();

        if($totalInvoices == $paidInvoiceItems){
            $invoice->status = "paid";
            $invoice->paid_at = Carbon::now();
        } else {
            $invoice->status = "partially_paid";
        }

        \Log::info("Pre: ".$invoice->sent_at);

        $invoice->save();

        \Log::info("Post: ".$invoice->sent_at);

    } catch(\Exception $ex){
        DB::rollBack();
        return $this->apiResponse->returnFail([], "Unable to Pay Invoice: ".$ex->getMessage(), 200);
    }

    DB::{$request->input("rollback", null) ? "rollback" : "commit"}();

    \Log::info("Post Commit: ".$invoice->sent_at);

    return $this->apiResponse->returnSuccess($invoice, "Invoice paid!", 200);
}

What this does is pays the selected InvoiceItems (child model of Invoice), and, if all InvoiceItems are marked as paid, then updates invoices.status to paid (or partially_paid) and invoices.paid_at to Carbon::now() (or null).

This all works fine, but somehow, this code is also updating sent_at (hence the \Log statements). When the code loads the Invoice, after applying all save logic, right after saving and finally right after committing, the sent_at attribute is logged:

[2019-05-08 12:43:24] local.INFO: Load: 2019-05-08 12:42:50
[2019-05-08 12:43:24] local.INFO: Pre: 2019-05-08 12:42:50
[2019-05-08 12:43:24] local.INFO: Post: 2019-05-08 12:42:50
[2019-05-08 12:43:24] local.INFO: Post Commit: 2019-05-08 12:42:50

As you can see, the sent_at timestamp is consistently 2019-05-08 12:42:50. But as soon as I re-query the database, the timestamp is 2019-05-08 12:43:24, which is the value of the paid_at and updated_at timestamps.

(status, sent_at, paid_at, created_at, updated_at)

Note this is called from an API, with a subsequent request to load a list of Invoice models, which has the following logic to determine so additional logic:

$cutoff = $this->sent_at->addDays(3)->endOfDay();

But I don't see how that could modify the sent_at column (no save/update is called following, and even if it did, 2019-05-08 12:43:24 does not equate to addDays(3)->endOfDay();

Has anyone seen this before? It's messing up some ordering logic in another view, so I need to fix it eventually...

Edit

If I disable $invoice->save();, it's updated_at timestamp is still updated, but I have no idea why. And, oddly enough, disabling $invoiceTransaction->save(); and $invoiceItem->save(); results in no change to updated_at... Does result in bad data, but this is still in development.

Secondary Edit

"CREATE TABLE `invoices` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`user_id` int(11) NOT NULL,
`account_id` int(11) NOT NULL,
`description` text,
`subtotal` decimal(10,2) NOT NULL,
`grand_total` decimal(10,2) NOT NULL,
`status` enum('pending','sent','partially_paid','paid') NOT NULL DEFAULT 
'pending',
`sent_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`paid_at` timestamp NULL DEFAULT NULL,
`created_at` timestamp NULL DEFAULT NULL,
`updated_at` timestamp NULL DEFAULT NULL,
`deleted_at` timestamp NULL DEFAULT NULL,
PRIMARY KEY (`id`)
)
ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8"

I believe there is an issue there:

sent_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

Nope; just a standard `extends Model`, with a couple normal functions, accessors and relationships. — Tim Lewis, May 08 '19 at 17:09
Here's your problem: `\`sent_at\` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP` — aynber, May 08 '19 at 17:21
@aynber Thanks for pointing that out. In the migration, I have it as `$table->timestamp("sent_at");`, vs `paid_at`, which is `$table->timestamp("paid_at")->nullable();`. Guess it just needs to be `nullable()`? — Tim Lewis, May 08 '19 at 17:22
I'm not sure how that would happen from the migration, since it it doesn't seem to be in the Blueprint at all. Very curious. I suppose you could always set that to nullable in the migration, and then just fix it manually in the database. (edit) Apparently it's a known issue?? https://ma.ttias.be/laravel-mysql-auto-adding-update-current_timestamp-timestamp-fields/ — aynber, May 08 '19 at 17:27
@aynber Yeah, never seen this issue before, and I've added plenty of additional timestamps. I've set it as `nullable()` and re-ran the migrations, now showing as `'sent_at timestamp NULL DEFAULT NULL,`. — Tim Lewis, May 08 '19 at 17:28
I hadn't seen it, either. It turns out, it's a MySQL 5.7 setting. `The first TIMESTAMP column in a table, if not explicitly declared with the NULL attribute or an explicit DEFAULT or ON UPDATE attribute, is automatically declared with the DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP attributes.` https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_explicit_defaults_for_timestamp — aynber, May 08 '19 at 17:29
@aynber Well there ya go. Lesson definitely learned. You're welcome to add all that as an answer if you'd like. Thanks for finding that, much appreciated as always. — Tim Lewis, May 08 '19 at 17:31

score 5 · Accepted Answer · answered May 08 '19 at 17:33

This is due to the MySQL 5.7 configuration, and not Laravel.

The first TIMESTAMP column in a table, if not explicitly declared with the NULL attribute or an explicit DEFAULT or ON UPDATE attribute, is automatically declared with the DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP attributes.

src - Mysql Docs

The fix is to set the timestamp to nullable in the migration, and/or alter the table manually.

ALTER TABLE invoices CHANGE sent_at sent_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP;

Laravel Timestamp Being Updated Without Explicit Call To Do So

1 Answers1

Linked

Related