Long primary key names throw exception but are actually never used by SingleStore

[larskoole]

When migrating a table with a very long primary key name (usually a set of 2 or more keys) Laravel generates a very long primary key name. This results in an exception in SingleStore saying it can't save a long primary key name.

But the thing is, the name is never used. It always defaults to be named PRIMARY.

Example I've go the following migration:

return new class extends Migration
{
    public function up(): void
    {
        Schema::create('redacted', function (Blueprint $table) {
            $table->ulid('redacted_1_id');
            $table->ulid('very_long_column_name_that_is_redacted_2_id');
            $table->timestamps();

            $table->primary([
                'redacted_1_id',
                'very_long_column_name_that_is_redacted_2_id',
            ]);
            $table->sortKey([
                'redacted_1_id',
                'very_long_column_name_that_is_redacted_2_id',
            ]);
        });
    }
};

When I run php artisan migrate I get the following exception:

 Illuminate\Database\QueryException 

  SQLSTATE[42000]: Syntax error or access violation: 1059 Identifier name 'redacted_redacted_1_id_very_long_column_name_that_is_redacted_2_id_primary' is too long (Connection: singlestore, SQL: create table `redacted` (`redacted_1_id` char(26) not null, `very_long_column_name_that_is_redacted_2_id` char(26) not null, `created_at` timestamp null, `updated_at` timestamp null, primary key `redacted_redacted_1_id_very_long_column_name_that_is_redacted_2_id_primary`(`redacted_1_id`, `very_long_column_name_that_is_redacted_2_id`), sort key(`redacted_1_id` asc, `very_long_column_name_that_is_redacted_2_id` asc)) default character set utf8mb4 collate 'utf8mb4_unicode_ci')

  at vendor/laravel/framework/src/Illuminate/Database/Connection.php:795
    791▕         // If an exception occurs when attempting to run a query, we'll format the error
    792▕         // message to include the bindings with SQL, which will make this exception a
    793▕         // lot more helpful to the developer instead of just the database's errors.
    794▕         catch (Exception $e) {
  ➜ 795▕             throw new QueryException(
    796▕                 $this->getName(), $query, $this->prepareBindings($bindings), $e
    797▕             );
    798▕         }
    799▕     }

The way I solve it now is to add "placeholder" as the second paramater in the $table->primary() method.

It now migrates and when I look at the table I can see that "placeholder" isn't used but it's named PRIMARY:

---

On another note, I'm not sure where the redacted_1_id index comes from, is that the sort key name? If so, might be better to name it _SORTKEY or something for the sake of consistency.

[AdalbertMemSQL]

Hey @larskoole Thanks for reaching out. I created an internal task to investigate this issue.