Key collisions for form submissions

[faltjo]

Lately I discovered a strange issue regarding form submissions. We use eloquent-driver so all of our form submissions are stored in the database, which rarely gives us an error similar to this one

SQLSTATE[23505]: Unique violation: 7 ERROR: duplicate key value violates unique constraint "form_submissions_pkey" DETAIL: Key (id)=(1784189916.6542) already exists.

After taking a closer look it seems that two submissions generate the same key and the unique constraint for the primary key raises the exception above.

Interestingly the keys are floats and they are calculated like this (see c109de0#diff-358fac9529237bf028155a1c6d1bc93a76948631e15202c11c63e54fde1fe6d0R61)

  $micro = Carbon::now()->timestamp + Carbon::now()->micro / 1000000;

 return $this->id = $id ?: str_replace(',', '.', $micro);

I noticed that all floats resulting from this calculation only have 4 digits after the comma, although the second part (microseconds divided by 10**6) has more trailing digits. I suspect after adding both numbers php's limited float precision is leading to the resulting 4 digits after the comma (10 digits of the timestamp part + 6 digits of the microsecond part are reduced to 10 + 4): https://www.php.net/manual/en/language.types.float.php#:~:text=The%20size%20of%20a%20float,the%2064%20bit%20IEEE%20format).
On a side note the datatype used on database level would allow a larger number of digits.

When comparing the successfully saved submission to the data in the error message matching the same key, one can see that we indeed are dealing with two distinct submissions. This means we can rule out duplicate submissions (e.g. double click on submit button). Thus it seems that the keys of both submissions were calculated in the same 10**(-4)th second, which seems kind of unlikely to me.

My questions:

1. Why is the form submission's key stored as float in first place?
   The point in time of the submission can anyhow be determined from the created_at column.
2. Is there a mistake in my argument? We have an application with lots of form submissions, but is it reasonable that two submissions having their key calculated in the same 10**(-4)th of a second? At peak we have around 400 submissions per minute, although these are only the ones which made their way into the database. What I can see form the logs, at 800 k submissions there are around 380 such collision errors.
3. Can anyone think of a different reason for this collisions?

[jasonvarga]

It's a float because that's just how it's been since it was first implemented way back in 2016.
Too long ago to honestly give a reason to why, but that's just the way it was and there was no real reason to change it.
If you're using files, your traffic wouldn't have ever really lead to a multiple-submissions-per-fraction-of-a-second collision.

Then when the eloquent driver came along, I guess it just inherited the id logic.

You could override the logic yourself with a migration and a custom Submission object. Something like this:

namespace App\Statamic\Forms;

use Illuminate\Support\Str;
use Statamic\Eloquent\Forms\Submission as EloquentSubmission;

class Submission extends EloquentSubmission
{
    public function id($id = null)
    {
        return $this->fluentlyGetOrSet('id')
            ->getter(fn ($id) => $this->id = $id ?: (string) Str::orderedUuid())
            ->args(func_get_args());
    }
}

namespace App\Providers;

use App\Statamic\Forms\Submission;
use Illuminate\Support\ServiceProvider;
use Statamic\Contracts\Forms\Submission as SubmissionContract;

class AppServiceProvider extends ServiceProvider
{
    public function boot()
    {
        $this->app->bind(SubmissionContract::class, Submission::class);
    }
}

Migration — current column is decimal('id', 14, 4) with a unique index (from eloquent-driver's own 2024_05_15_100000_modify_form_submissions_id.php). Widen it to a string and keep the constraint. Splitting into separate Schema::table calls avoids driver quirks when changing a column type and its index in the same statement:

<?php

use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

return new class extends \Illuminate\Database\Migrations\Migration
{
    public function up()
    {
        Schema::table('form_submissions', function (Blueprint $table) {
            $table->dropUnique('form_submissions_id_unique');
        });

        Schema::table('form_submissions', function (Blueprint $table) {
            $table->string('id', 36)->change();
        });

        Schema::table('form_submissions', function (Blueprint $table) {
            $table->unique('id');
        });
    }

    public function down()
    {
        Schema::table('form_submissions', function (Blueprint $table) {
            $table->dropUnique('form_submissions_id_unique');
        });

        Schema::table('form_submissions', function (Blueprint $table) {
            $table->decimal('id', 14, 4)->change();
        });

        Schema::table('form_submissions', function (Blueprint $table) {
            $table->unique('id');
        });
    }
};

Give it a filename timestamp after eloquent-driver's 2024_05_15_100000_modify_form_submissions_id.php so it runs later. Existing decimal IDs remain valid strings after the type change — only new submissions get UUIDs, so no backfill needed.

[faltjo]

Thanks for the explanation and detailed suggestion to solve the problem.