MySQL: Wrong order of generated primary keys #5665

New Issue

admin · 2026-01-22T15:14:05+01:00

admin commented

2026-01-22 15:14:05 +01:00

Originally created by @fxOne on GitHub (Sep 1, 2017).

I defined in my address.orml.yml:

Address:
  type: entity
  table: addresses

  id:
    user:
      id: true
      associationKey: true
      column: user_id
    addressType:
      id: true
      type: EnumAddressType
      nullable: false
      column: address_type

  fields:
    ....

Generates the following MySQL Code on the Symfony doctrine:migration:diff command:

CREATE TABLE addresses 
(address_type ENUM('billing','shipping') COMMENT '(DC2Type:EnumAddressType)' NOT NULL COMMENT '(DC2Type:EnumAddressType)', 
extranet_2level_customer_id INT NOT NULL,
...
PRIMARY KEY(address_type, user_id));

I would expect that the primary key would be user_id, address_type as I defined it in this order.
Is this a bug or do I must define something else to get the primary key in my expected order?

Originally created by @fxOne on GitHub (Sep 1, 2017). I defined in my `address.orml.yml`: ``` Address: type: entity table: addresses id: user: id: true associationKey: true column: user_id addressType: id: true type: EnumAddressType nullable: false column: address_type fields: .... ``` Generates the following MySQL Code on the Symfony `doctrine:migration:diff` command: ``` CREATE TABLE addresses (address_type ENUM('billing','shipping') COMMENT '(DC2Type:EnumAddressType)' NOT NULL COMMENT '(DC2Type:EnumAddressType)', extranet_2level_customer_id INT NOT NULL, ... PRIMARY KEY(address_type, user_id)); ``` I would expect that the primary key would be `user_id, address_type` as I defined it in this order. Is this a bug or do I must define something else to get the primary key in my expected order?

admin commented

2026-01-22 15:14:06 +01:00

@Ocramius commented on GitHub (Sep 2, 2017):

This is possibly because associations are processed after normal fields: you will likely need to check the identifier field order in the ClassMetadata instance and try reproducing a test case from there.

@Ocramius commented on GitHub (Sep 2, 2017): This is possibly because associations are processed after normal fields: you will likely need to check the identifier field order in the `ClassMetadata` instance and try reproducing a test case from there.

admin commented

2026-01-22 15:14:07 +01:00

@Ocramius commented on GitHub (Sep 2, 2017):

Also, I think this might be an ORM bug, not a DBAL bug.

@Ocramius commented on GitHub (Sep 2, 2017): Also, I think this might be an ORM bug, not a DBAL bug.

admin commented

2026-01-22 15:14:07 +01:00

@raalderink commented on GitHub (Apr 29, 2022):

I just ran into the same issue, and this is definitely a DBAL bug. I traced in to the following piece of code (in doctrine/dbal 2,13,3, but the code is still the same in the latest version)

The AbstractPlatform::getCreateTableSQL() does the following:

$options['primary'] = $index->getQuotedColumns($this);

getQuotedColumns is this function:

    public function getQuotedColumns(AbstractPlatform $platform)
    {
        $subParts = $platform->supportsColumnLengthIndexes() && $this->hasOption('lengths')
            ? $this->getOption('lengths') : [];

        $columns = [];

        foreach ($this->_columns as $column) {
            $length = array_shift($subParts);

            $quotedColumn = $column->getQuotedName($platform);

            if ($length !== null) {
                $quotedColumn .= '(' . $length . ')';
            }

            $columns[] = $quotedColumn;
        }

        return $columns;
    }

As you can see, this simply goes through the known columns, which is fields first, association keys second. It does not take into account the order of the fields listed in de primary key.

@raalderink commented on GitHub (Apr 29, 2022): I just ran into the same issue, and this is definitely a DBAL bug. I traced in to the following piece of code (in doctrine/dbal 2,13,3, but the code is still the same in the latest version) The `AbstractPlatform::getCreateTableSQL()` does the following: ``` $options['primary'] = $index->getQuotedColumns($this); ``` `getQuotedColumns` is this function: ``` public function getQuotedColumns(AbstractPlatform $platform) { $subParts = $platform->supportsColumnLengthIndexes() && $this->hasOption('lengths') ? $this->getOption('lengths') : []; $columns = []; foreach ($this->_columns as $column) { $length = array_shift($subParts); $quotedColumn = $column->getQuotedName($platform); if ($length !== null) { $quotedColumn .= '(' . $length . ')'; } $columns[] = $quotedColumn; } return $columns; } ``` As you can see, this simply goes through the known columns, which is fields first, association keys second. It does not take into account the order of the fields listed in de primary key.

admin commented

2026-01-22 15:14:07 +01:00

@beberlei commented on GitHub (Apr 29, 2022):

The columbs are put into the index in wrong order by the SchemaTool though. The order is defined in ClassMetadata in a property. Maybe YamlDriver parses the document weirdly. Is this also a problem with XmlDriver?

@beberlei commented on GitHub (Apr 29, 2022): The columbs are put into the index in wrong order by the SchemaTool though. The order is defined in ClassMetadata in a property. Maybe YamlDriver parses the document weirdly. Is this also a problem with XmlDriver?

admin commented

2026-01-22 15:14:07 +01:00

@raalderink commented on GitHub (Apr 29, 2022):

You are correct, we went down the rabbithole and this is indeed an issue in the YamlDriver. Where exactly we've not found out yet. Unsure about the XmlDriver.

@raalderink commented on GitHub (Apr 29, 2022): You are correct, we went down the rabbithole and this is indeed an issue in the YamlDriver. Where exactly we've not found out yet. Unsure about the XmlDriver.

admin commented

2026-01-22 15:14:08 +01:00

@Yoshix commented on GitHub (Sep 1, 2022):

I can confirm that the XmlDriver has the same problem.

My guess is it's this line:
5d11648767/lib/Doctrine/ORM/Mapping/Driver/XmlDriver.php (L373)

Out of curiosity, I added the following code at the end of that method:

if (isset($xmlRoot->{'identifier-order'})) {
    if (isset($xmlRoot->{'identifier-order'}['columns']) && ! empty($xmlRoot->{'identifier-order'}['columns'])) {
        $order = explode(',', (string) $xmlRoot->{'identifier-order'}['columns']);

        if ([] === array_diff($metadata->getIdentifier(), $order)) {
            $metadata->setIdentifier($order);
        }
        else {
            // should throw ...
        }
    }
}

With the corresponding xml tag in one of my mapping files:

<identifier-order columns="user,token"/>

And it "fixed" the problem.

@Yoshix commented on GitHub (Sep 1, 2022): I can confirm that the XmlDriver has the same problem. My guess is it's this line: https://github.com/doctrine/orm/blob/5d1164876707c4485671b418f123f9e57b6bd923/lib/Doctrine/ORM/Mapping/Driver/XmlDriver.php#L373 Out of curiosity, I added the following code at the end of that method: if (isset($xmlRoot->{'identifier-order'})) { if (isset($xmlRoot->{'identifier-order'}['columns']) && ! empty($xmlRoot->{'identifier-order'}['columns'])) { $order = explode(',', (string) $xmlRoot->{'identifier-order'}['columns']); if ([] === array_diff($metadata->getIdentifier(), $order)) { $metadata->setIdentifier($order); } else { // should throw ... } } } With the corresponding xml tag in one of my mapping files: <identifier-order columns="user,token"/> And it *"fixed"* the problem.

admin commented

2026-01-22 15:14:09 +01:00

@uncaught commented on GitHub (May 10, 2023):

I've found the problem in the YamlDriver: While it iterates over the ids before the fields, it skips those with associationKey: true until after the fields.

See 70477d81e9/lib/Doctrine/ORM/Mapping/Driver/YamlDriver.php (L334)

This causes the association key ids to be added after the fields.

I've overridden the loadMetadataForClass to fix the order to the one defined in the yaml. The ids of any base entity class won't be in the yaml though, so this has to be taken into account and I've also added an idOrder to override if the outcome is not desired when working with extending classes:

class YamlDriver extends \Doctrine\ORM\Mapping\Driver\YamlDriver {

  private function validateWantedIdOrderExists(array $loadedOrder, array $wantedOrder, string $className): void {
    $diff = array_diff($loadedOrder, $wantedOrder);
    if ($diff) {
      throw new \RuntimeException('ids mismatch: ' . json_encode([
          'wanted' => $wantedOrder,
          'loaded' => $loadedOrder,
          'class' => $className,
        ], JSON_THROW_ON_ERROR));
    }
  }

  public function loadMetadataForClass($className, PersistenceClassMetadata $metadata) {
    parent::loadMetadataForClass($className, $metadata);

    $element = $this->getElement($className);
    if ($metadata instanceof ClassMetadataInfo) {
      if (isset($element['idOrder'])) {
        $wantedOrder = $element['idOrder'];
        $loadedOrder = $metadata->getIdentifier();
        $this->validateWantedIdOrderExists($loadedOrder, $wantedOrder, $className);
        $metadata->setIdentifier($wantedOrder);
      } elseif (isset($element['id'])) {
        $wantedOrder = array_keys($element['id']);
        //These can be more than $wantedOrder if you extend an entity with ids - we assume the counts are correct,
        // otherwise the diff should notice a mismatch:
        $loadedOrder = $metadata->getIdentifier();
        $loadedBase = array_slice($loadedOrder, 0, -count($wantedOrder));
        $loadedOwn = array_slice($loadedOrder, count($wantedOrder));
        $this->validateWantedIdOrderExists($loadedOwn, $wantedOrder, $className);
        $metadata->setIdentifier([...$loadedBase, ...$wantedOrder]);
      }
    }
  }
}

@uncaught commented on GitHub (May 10, 2023): I've found the problem in the YamlDriver: While it iterates over the `id`s before the `fields`, it skips those with `associationKey: true` until after the `fields`. See https://github.com/doctrine/orm/blob/70477d81e96c0044ad6fd8c13c37b2270d082792/lib/Doctrine/ORM/Mapping/Driver/YamlDriver.php#L334 This causes the association key ids to be added after the fields. I've overridden the `loadMetadataForClass` to fix the order to the one defined in the yaml. The ids of any base entity class won't be in the yaml though, so this has to be taken into account and I've also added an `idOrder` to override if the outcome is not desired when working with extending classes: ```php class YamlDriver extends \Doctrine\ORM\Mapping\Driver\YamlDriver { private function validateWantedIdOrderExists(array $loadedOrder, array $wantedOrder, string $className): void { $diff = array_diff($loadedOrder, $wantedOrder); if ($diff) { throw new \RuntimeException('ids mismatch: ' . json_encode([ 'wanted' => $wantedOrder, 'loaded' => $loadedOrder, 'class' => $className, ], JSON_THROW_ON_ERROR)); } } public function loadMetadataForClass($className, PersistenceClassMetadata $metadata) { parent::loadMetadataForClass($className, $metadata); $element = $this->getElement($className); if ($metadata instanceof ClassMetadataInfo) { if (isset($element['idOrder'])) { $wantedOrder = $element['idOrder']; $loadedOrder = $metadata->getIdentifier(); $this->validateWantedIdOrderExists($loadedOrder, $wantedOrder, $className); $metadata->setIdentifier($wantedOrder); } elseif (isset($element['id'])) { $wantedOrder = array_keys($element['id']); //These can be more than $wantedOrder if you extend an entity with ids - we assume the counts are correct, // otherwise the diff should notice a mismatch: $loadedOrder = $metadata->getIdentifier(); $loadedBase = array_slice($loadedOrder, 0, -count($wantedOrder)); $loadedOwn = array_slice($loadedOrder, count($wantedOrder)); $this->validateWantedIdOrderExists($loadedOwn, $wantedOrder, $className); $metadata->setIdentifier([...$loadedBase, ...$wantedOrder]); } } } } ```

admin commented

2026-01-22 15:14:09 +01:00

@tehmaestro commented on GitHub (Nov 5, 2025):

Hey, I recently had to deal with this problem.
The way I fixed it is by adding a custom compiler pass that

class OrderedPrimaryKeyCompiler implements CompilerPassInterface
{
    private const string PARAM_DOCTRINE_DRIVER = 'doctrine.orm.default_xml_metadata_driver';

    public function process(ContainerBuilder $container): void
    {
        if (!$container->hasDefinition(self::PARAM_DOCTRINE_DRIVER)) {
            return;
        }

        $definition = $container->getDefinition(self::PARAM_DOCTRINE_DRIVER);
        $definition->setClass(XmlOrderedPrimaryKeyDriver::class);
    }
}

because I wanted to keep the arguments for the definition the same, so I just switch the class in-place

Then, in my custom class I do:

class XmlOrderedPrimaryKeyDriver extends SimplifiedXmlDriver
{
    public function loadMetadataForClass($className, PersistenceClassMetadata $metadata): void
    {
        parent::loadMetadataForClass($className, $metadata);

        $xmlRoot = $this->getElement($className);
        $primaryKeys = [];
        foreach ($xmlRoot->id ?? [] as $idElement) {
            $primaryKeys[] = (string) $idElement['name'];
        }

        // If the primary keys are the same as the identifier, we can set the identifier to the primary keys, keeping the desired order.
        if (array_diff($primaryKeys, $metadata->getIdentifier()) === []) {
            $metadata->setIdentifier($primaryKeys);
        }
    }
}

@tehmaestro commented on GitHub (Nov 5, 2025): Hey, I recently had to deal with this problem. The way I fixed it is by adding a custom compiler pass that ```php class OrderedPrimaryKeyCompiler implements CompilerPassInterface { private const string PARAM_DOCTRINE_DRIVER = 'doctrine.orm.default_xml_metadata_driver'; public function process(ContainerBuilder $container): void { if (!$container->hasDefinition(self::PARAM_DOCTRINE_DRIVER)) { return; } $definition = $container->getDefinition(self::PARAM_DOCTRINE_DRIVER); $definition->setClass(XmlOrderedPrimaryKeyDriver::class); } } ``` because I wanted to keep the arguments for the definition the same, so I just switch the class in-place Then, in my custom class I do: ```php class XmlOrderedPrimaryKeyDriver extends SimplifiedXmlDriver { public function loadMetadataForClass($className, PersistenceClassMetadata $metadata): void { parent::loadMetadataForClass($className, $metadata); $xmlRoot = $this->getElement($className); $primaryKeys = []; foreach ($xmlRoot->id ?? [] as $idElement) { $primaryKeys[] = (string) $idElement['name']; } // If the primary keys are the same as the identifier, we can set the identifier to the primary keys, keeping the desired order. if (array_diff($primaryKeys, $metadata->getIdentifier()) === []) { $metadata->setIdentifier($primaryKeys); } } } ```

admin referenced this issue

2026-01-22 16:14:04 +01:00

[PR #10547] Compute the commit order (inserts/deletes) on the entity level #12445

admin referenced this issue

2026-01-22 16:14:05 +01:00

[PR #10548] Failing test: UoW unable to break cycles when removing entities without DB-level cascade #12450

admin referenced this issue

2026-01-22 16:14:06 +01:00

[PR #10566] Commit order for removals has to consider `SET NULL`, not `nullable` #12458

Sign in to join this conversation.