MariaDB native UUID support

[rixafy]

Feature Request

Q	A
New Feature	yes
BC Break	yes

Summary

Hi, I just hear there is 4.0 beta, please don't forget that there is missing a native UUID support for MariaDB 10.7+ and it should be added into 4.0, because it's a BC break.

I'm using it like this https://github.com/Rixafy/SDK/blob/master/src/Doctrine/Platform/MariaDb1070Platform.php, long time ago I made a PR but it was problematic to generate migrations, because switching PKs is complex thing and I think when I last tried it, it didn't drop primary indexes, but I can provide my migration for changing PKs if needed, to provide it as template in the docs or something.

Another thing is that I need to extend MariaDb1027Platform (deprecated) instead of MariaDbPlatform because of this error https://github.com/doctrine/migrations/issues/1281 and I didn't have time provide a reproducer, but I think in dbal 4.0, it should work as expected, since default platform will be MariaDbPlatform, since old is deprecated, now when I try that new platform in 3.0, that error in migration appears, even when I don't extend it with UUID support.

[derrabus]

We won't ship new features with DBAL 4.0, but we might release a 3.6. If you want to work on this feature, please open a PR against 3.5.x for now. We can retarget to 3.6 once we open that branch. If this feature involves breaking changes, make it opt-in, so people upgrading from a previous DBAL 3 release won't end up with a broken application.

[derrabus]

Related: #4998

[rixafy]

Well, migrations would generate unnecessary queries, otherwise application shouldn't be broken, most people who are using UUID use uuid_binary type with binary(16) column type from ramsey/uuid-doctrine library, others use uuid type and varchar column type, in both cases app will work just fine, but migration will produce unnecessary queries for changing id type. And in both cases, user must have MariaDB 10.7+, and so far, last LTS is 10.6, so it's very rare.

Considering it, could it be opt-out in 4.0? Because we are sacrificing UX for all new users/apps with MariaDB 10.7+ at the expense of causing BC breaks for few people that can opt-out and still use string instead of uuid in 4.0.

[derrabus]

#5990

[karstennilsen]

We have been using https://github.com/doctrine/dbal/pull/4998 as a patch on doctrine/dbal quite some time now without any issues on a big software system with over 100K customers. Our database size shrunk by approx 40%.

Useful findings:

• Database migrations for existing tables are not being made automatically. We used this snipped to create our own migration:

        $this->write('Dropping all foreign keys');
        $dbName = $this->connection->getParams()['dbname'];
        $query = "SELECT table_name, constraint_name 
          FROM information_schema.table_constraints 
          WHERE constraint_type = 'FOREIGN KEY' 
          AND table_schema = :dbName;";
        $query = "SELECT TABLE_NAME, COLUMN_NAME, IS_NULLABLE 
          FROM INFORMATION_SCHEMA.COLUMNS 
          WHERE DATA_TYPE = 'char' 
          AND CHARACTER_MAXIMUM_LENGTH = 36 
          AND COLUMN_COMMENT = '(DC2Type:guid)' 
          AND TABLE_SCHEMA = :dbName";

        $columns = $this->connection->executeQuery($query, ['dbName' => $dbName])->fetchAllAssociative();

        $tableModifications = [];

        foreach ($columns as $column) {
            $nullable = $column['IS_NULLABLE'] === 'YES' ? 'NULL' : 'NOT NULL';
            $tableModifications[$column['TABLE_NAME']][] = "MODIFY {$column['COLUMN_NAME']} UUID $nullable";
        }

        $i = 0;
        foreach ($tableModifications as $tableName => $modifications) {
            $this->write("Modifying table $tableName to UUID(s)" . sprintf(' (%d/%d)', ++$i, count($tableModifications)));
            $alterQuery = "ALTER TABLE `$tableName` " . implode(', ', $modifications) . ';';
            $this->connection->executeQuery($alterQuery);
        }

• OPTIONAL: After this you want to use doctrine:schema:update --dump-sql to get the query to recreate all foreign key constraints again. For us there were a lot of them so we used this to group them and improve execution time:

        $constraints = [];
        $constraints[] = 'ALTER TABLE xxx ADD CONSTRAINT FK_47CC8C92B03A8386 FOREIGN KEY (created_by_id) REFERENCES yyy (id)';
        $constraints[] = 'ALTER TABLE xxx ADD CONSTRAINT FK_47CC8C9299049ECE FOREIGN KEY (modified_by_id) REFERENCES yyy (id)';

        // Initialize an associative array to hold the ALTER TABLE commands for each table
        $tableAlterations = [];

        // Parse each ALTER TABLE command and organize them by table
        foreach ($constraints as $constraint) {
            preg_match('/ALTER TABLE (\S+) ADD CONSTRAINT (\S+) FOREIGN KEY \(([^)]+)\) REFERENCES (\S+) \(([^)]+)\)/', $constraint, $matches);

            if (count($matches) !== 6) {
                throw new \Exception("Could not parse ALTER TABLE command: $constraint");
            }
            $tableName = $matches[1];
            $constraintDefinition = "ADD CONSTRAINT {$matches[2]} FOREIGN KEY ({$matches[3]}) REFERENCES {$matches[4]} ({$matches[5]})";

            // Add the constraint to the appropriate table in the array
            $tableAlterations[$tableName][] = $constraintDefinition;
        }

        $i = 0;
        // Generate and print a single ALTER TABLE statement for each table
        foreach ($tableAlterations as $table => $alterations) {
            $this->write(sprintf('Processing table %s (%d/%d)', $table, ++$i, count($tableAlterations)));
            $alterCommand = "ALTER TABLE $table " . implode(', ', $alterations) . ';';
            $this->connection->executeQuery($alterCommand);
        }

• You'll want to use MariaDB 10.9.8+ because of [UUIDs](https://mariadb.com/kb/en/uuid-data-type/) version >= 6 are now stored without byte-swapping, UUIDs with version >=8 and variant=0 are now considered invalid, old tables are supported, old (always byte swapped) and new (swapped for version < 6) UUIDs can be compared and converted transparently ([MDEV-29959](https://jira.mariadb.org/browse/MDEV-29959)) in https://mariadb.com/kb/en/mariadb-10-9-8-release-notes/. Before this the sorting of UUID (and therefore e.g. pagination or chunking) was completly messed up. I would therefore suggest to bump the version check to 10.9.8+.

Don't know if there is a way to sponsor the finalization of this PR, but our company could contribute a bit to phase out the patch :)

[derrabus]

Thank you for the additional insights.

Migrating UUID columns that are part of a FK constraint was the tricky part that I missed in #5990. If you want to pick up that PR, please feel free to do so.

[karstennilsen]

Thank you for the additional insights.

Migrating UUID columns that are part of a FK constraint was the tricky part that I missed in #5990. If you want to pick up that PR, please feel free to do so.

Unfortunately, I currently have too little time for this. As I mentioned: Don't know if there is a way to sponsor the finalization of this PR, but our company could contribute financially to phase out the patch :)

[derrabus]

You can sponsor the Doctrine project in general or individual team members directly through GitHub sponsors, but to my knowledge there is no processes within the Doctrine project for sponsoring a specific feature.

I personally can be hired as a freelancer, but I'm pretty busy at the moment. But feel free to send me an email if you want to discuss that option.