Breaking changes on composite primary keys containing NULL

[larskoole]

Hey we've got this weird issue where migrations don't create composite primary keys the same way they used to. We used to be able to migrate nullable primary keys but now they don't get created correctly anymore.

Our production table on SingleStore Cloud:

Our staging and local table:

Our migration (which hasn't changed):

This is the case for all our pivot tables which have a nullable column.

I'm suspecting the driver because the local environment hasn't been updated in months and still experienced this change/issue.

Production isn't impacted (for now) but having to manually run queries to fix the columns every time we use migrate:fresh kinda sucks. Let met know if you need more info to debug.

[AdalbertMemSQL]

Hey @larskoole
Nullable primary key should be forbidden in SingleStore.

MySQL [db]> create table t(a char(26), b char(26) null, primary key(a, b));
Query OK, 0 rows affected, 1 warning (0.058 sec)

MySQL [db]> show warnings;
+---------+------+--------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                    |
+---------+------+--------------------------------------------------------------------------------------------+
| Warning | 1171 | All parts of a PRIMARY KEY must be NOT NULL; if you need NULL in a key, use UNIQUE instead |
+---------+------+--------------------------------------------------------------------------------------------+
1 row in set (0.000 sec)

MySQL [db]> show create table t;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                     |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t     | CREATE TABLE `t` (
  `a` char(26) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '',
  `b` char(26) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '',
  UNIQUE KEY `PRIMARY` (`a`,`b`) USING HASH,
  SHARD KEY `__SHARDKEY` (`a`,`b`),
  KEY `__UNORDERED` () USING CLUSTERED COLUMNSTORE
) AUTOSTATS_CARDINALITY_MODE=INCREMENTAL AUTOSTATS_HISTOGRAM_MODE=CREATE AUTOSTATS_SAMPLING=ON SQL_MODE='STRICT_ALL_TABLES' |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.000 sec)

[AdalbertMemSQL]

I'm impressed that it was possible to create a nullable column which is a part of the primary key.
Do you use the same version of the SingleStore in the Cloud and local/staging environment?
Can you share versions of your databases and DDL queries used to create tables?

[larskoole]

Hah lol, I have no idea why it worked but it did make the dev experience amazing (thinking uniqueness was being enforced with a nullable column).

The production and staging environment are both on SingleStore Cloud in the same workspace (saving costs). I have no idea what the version is but this is the next pending update:

The local DB is version 8.1.1 using https://github.com/singlestore-labs/singlestoredb-dev-image

Is there a way to recall what query was used to create a table?
If not then this is the migration which I used:

<?php

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

return new class extends Migration
{
    public function up(): void
    {
        Schema::create('integration_product_media', function (Blueprint $table) {
            $table->ulid('product_id');
            $table->ulid('media_id');
            $table->ulid('integration_id')->nullable();

            $table->primary([
                'product_id',
                'media_id',
                'integration_id',
            ], 'integration_product_media_primary');
            $table->sortKey([
                'product_id',
                'integration_id',
                'sort_order',
            ]);
        });
    }
};

On another note: do you have a suggestion on how to enforce uniqueness without relying on locks or "wonky" solutions like https://usefathom.com/laravel-tips/multiple-uniques-singlestore?
If not then it is what it is :)

[larskoole]

Btw, another weird thing is that DESCRIBE integration_product_media; (the first 2 images of my post) shows that media_id doesn't have KEY => PRI like product_id and integration_id. But maybe that's not relevant.

[AdalbertMemSQL]

To check, what query is used to create a table, you can run this

show create table integration_product_media

[larskoole]

Production:
CREATE TABLE `integration_product_media` ( `product_id` char(26) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, `media_id` char(26) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, `integration_id` char(26) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL, `sort_order` int(10) unsigned NOT NULL, SHARD KEY `__SHARDKEY` (`product_id`,`media_id`,`integration_id`), UNIQUE KEY `integration_product_media_primary` (`product_id`,`media_id`,`integration_id`) USING HASH, KEY `product_id` (`product_id`,`integration_id`,`sort_order`) USING CLUSTERED COLUMNSTORE ) AUTOSTATS_CARDINALITY_MODE=INCREMENTAL AUTOSTATS_HISTOGRAM_MODE=CREATE AUTOSTATS_SAMPLING=ON SQL_MODE='STRICT_ALL_TABLES,NO_AUTO_CREATE_USER'

Staging:
CREATE TABLE `integration_product_media` ( `product_id` char(26) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, `media_id` char(26) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, `integration_id` char(26) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '', `sort_order` int(10) unsigned NOT NULL, UNIQUE KEY `PRIMARY` (`product_id`,`media_id`,`integration_id`) USING HASH, KEY `product_id` (`product_id`,`integration_id`,`sort_order`) USING CLUSTERED COLUMNSTORE, SHARD KEY `__SHARDKEY` (`product_id`,`media_id`,`integration_id`) ) AUTOSTATS_CARDINALITY_MODE=INCREMENTAL AUTOSTATS_HISTOGRAM_MODE=CREATE AUTOSTATS_SAMPLING=ON SQL_MODE='STRICT_ALL_TABLES,NO_AUTO_CREATE_USER'

(ignore the random sort_order column)

[AdalbertMemSQL]

Hmm...
Looks like the table in production contains a unique key (not a primary) and DESCRIBE shows incorrect information.
I found a ticket where DESCRIBE showed incorrect information and asked if this could be connected.