4

Magento 2.3

Question

Is there any place where db_schema.xml for your module is cached/restored from?

Issue

I need to refresh/get the recently changed db_schema.xml changes to apply, which is not happening.

Expected result:

Updated (shortened) names for indexes.

IDX_GM_CTA_CUSTOMER_ENTITY_ID
IDX_GM_CTA_TRADE_ACCOUNT_STATE_TYPE_ID

Current incorrect result:

Ignores my currently updated (shortened) names and somehow **still uses old versions of names:

VENDOR_MODULENAME_TABLENAME_CUSTOMER_ENTITY_ID
IDX_0C25F86D281C81FDAE652EC2E495584F

I confirmed after setup:upgrade (deleted the database tables) that the table generated in database has these OLD index names.

Further information

This is what the db_schema_whitelist.xml currently shows:

This generated information is old, I have recently updated the index referenceId names (see below):

    "vendor_modulename_tablename": {
        "column": {
            "table_name_id": true,
            "customer_entity_id": true,
            "table_name_state_type_id": true,
            "table_name_discount": true
        },
        "index": {
            "VENDOR_MODULENAME_TABLENAME_CUSTOMER_ENTITY_ID": true,
            "IDX_0C25F86D281C81FDAE652EC2E495584F": true
        },
        "constraint": {
            "PRIMARY": true
        }
    },

The current db_schema.xml for the above generated whitelist:

    <table name="vendor_modulename_tablename" resource="default" engine="innodb" comment="vendor_modulename_tablename Table">
        <column xsi:type="int" name="table_name_id" padding="10" unsigned="true" nullable="false" identity="true" comment="Table Name ID"/>
        <column name="customer_entity_id" nullable="false" xsi:type="int" comment="Customer Entity ID (from customer_entity)" identity="false" unsigned="true"/>
        <column name="table_name_state_type_id" nullable="false" xsi:type="smallint" comment="Table Name State Type ID" unsigned="true"/>
        <column name="table_name_discount" nullable="false" xsi:type="float" comment="Table Name Discount" default="5.10"/>

        <constraint xsi:type="primary" referenceId="PRIMARY">
            <column name="table_name_id"/>
        </constraint>
        <index referenceId="IDX_GM_CTA_CUSTOMER_ENTITY_ID" indexType="btree">
            <column name="customer_entity_id"/>
        </index>
        <index referenceId="IDX_GM_CTA_TRADE_ACCOUNT_STATE_TYPE_ID" indexType="btree">
            <column name="table_name_state_type_id"/>
        </index>
    </table>

The old (previous commit version for the constraints):

        <constraint xsi:type="primary" referenceId="PRIMARY">
            <column name="table_name_id"/>
        </constraint>
        <index referenceId="VENDOR_MODULENAME_TABLENAME_CUSTOMER_ENTITY_ID" indexType="btree">
            <column name="customer_entity_id"/>
        </index>
        <index referenceId="VENDOR_MODULENAME_TABLENAME_APPLICATION_ACCOUNT_STATE_ACCOUNT_TYPE_ID" indexType="btree">
            <column name="table_name_state_type_id"/>
        </index>

MORE INFORMATION

        "index": {
            "VENDOR_MODULENAME_TABLENAME_CUSTOMER_ENTITY_ID": true,
            "IDX_0C25F86D281C81FDAE652EC2E495584F": true
        },

OLD version of db_schema.xml had these:

  • VENDOR_MODULENAME_TABLENAME_APPLICATION_ACCOUNT_STATE_ACCOUNT_TYPE_ID index name is too long (more than 64 characters), so Magento 2 shortened it to auto-generated name: IDX_0C25F86D281C81FDAE652EC2E495584F.

  • VENDOR_MODULENAME_TABLENAME_CUSTOMER_ENTITY_ID remains VENDOR_MODULENAME_TABLENAME_CUSTOMER_ENTITY_ID because the character limit is not reached.

NOTE: When generating the name originally, I wasn't fully aware of the character limit, so I recently updated all field/table/index names in module.

Help needed

Where is db_schema.xml cached for your module, so that I can delete it and regenerate the updated white list, or what am I missing?

EDIT: What I tried again now

In summary:

Where are these entries cached? I even tried to do text-searches (grep) inside the whole folder structure of Magento 2 and could not find anything. I restarted MySQL because I thought maybe it's cached there, even though that would be absolutely weird if that is the case!

I followed these steps:

  • Removed module files (deleted Vendor/Module_Name).
  • In other words, there is no generated db_schema_whitelist.
  • Ran bin/magento setup:upgrade
  • Deleted database tables related to this module.
  • Stopped web services.
  • Stopped MySQL service, and restarted.
  • Started web services.
  • Added module files back (inside Vendor/Module_Name)
  • BEFORE RUNNING setup:upgrade I tried to run bin/magento setup:db-declaration-generate-whitelist --module-name=Vendor/Module_Name but of course nothing was generated since the module has not been reinstalled.
  • Ran bin/magento setup:upgrade.
  • Checked database and OLD NAMES were back :(
  • Ran bin/magento setup:db-declaration-generate-whitelist --module-name=Vendor/Module_Name and it had the old generated names.

The behaviour/feature implemented by Magento 2 does not make sense

  • After the above steps I went to the **database and ran:
ALTER TABLE database.tablename RENAME INDEX old_index_name TO new_index_name
  • So I manually renamed these indexes from the Database side: These indexes are then changed, but **as soon as I run bin/magento setup:upgrade it adds those old names back, so now I have old_index_name and _new_index_name` indexes for the same coluns.

Every single time I am applying changes and need to run bin/magento setup:upgrade I currently would need to rename the OLD indexes to NEW index names.

EDIT

I investigated further, and on debugging I discovered that the core code does not apply referenceId in any way for naming indexes in the database.

I ended up creating a new module to create Plugins for features to use referenceId as my custom names for any modules I make in the future. I created filter information to ensure I only apply these renamed index names to the desired modules (in other words target column tables for the module/s I want to use this custom plugin for)

1
  • Any help would be appreciated, still have not been able to find cached content of this db_schema.xml; it is still using the old names. Commented Oct 19, 2022 at 7:57

1 Answer 1

1

EDIT

I investigated further, and on debugging I discovered that the core code does not apply referenceId in any way for naming indexes in the database.

I ended up creating a new module to create Plugins for features to use referenceId as my custom names for any modules I make in the future. I created filter information to ensure I only apply these renamed index names to the desired modules (in other words target column tables for the module/s I want to use this custom plugin for)

Previous (Discovered Information)

For reference to anyone that might find this useful:

An update on the index name issue:

  • *referenceId appears to only be used by whitelist as a reference for the index, but does not apply this name, even though it appeared from the documentation to do that.

I do not know if the documentation I read from Adobe is accurate or not, but it always hinted that referenceId can be used as the index name in database table as well.

Investigation

  • Looking at the code while debugging I found the relevant files:

*All of these are in vendor/magento/framework/Setup:

./Setup/Declaration/Schema/Declaration/SchemaBuilder.php 

SchemaBuilder::build() -> SchemaBuilder::processTable() -> $table->addIndexes($this->processIndexes($tableData, $resource, $table))

Relevant code for processIndexes:

    private function processIndexes(array $tableData, string $resource, Table $table): array
    {
...... trimmed
            $indexData['name'] = $this->elementNameResolver->getFullIndexName(
                $table,
                $indexData['column'],
                $indexData['indexType'] ?? null
            );
... trimmed
    }

./Setup/Declaration/Schema/Declaration/TableElement/ElementNameResolver.php:

    public function getFullIndexName(
        Table $table,
        array $columns,
        ?string $type = AdapterInterface::INDEX_TYPE_INDEX
    ): string {
        if (AdapterInterface::INDEX_TYPE_PRIMARY === $type) {
            return strtoupper(AdapterInterface::INDEX_TYPE_PRIMARY);
        }

        /**
         * Temporary solution.
         * @see MAGETWO-91365
         */
        $isIndexTypeOutOfList = false === array_search(
            $type,
            [AdapterInterface::INDEX_TYPE_FULLTEXT, AdapterInterface::INDEX_TYPE_UNIQUE]
        );
        if ($type && $isIndexTypeOutOfList) {
            $type = AdapterInterface::INDEX_TYPE_INDEX;
        }

        $tableName = $this->tableNameResolver->getNameOfOriginTable($table->getName());

        return $this->resourceConnection
            ->getIdxName(
                $tableName,
                $columns,
                $type
            );
    }

The definition appears to be the same of the commit mentioned, and there appears to be nothing here defined that uses the indexed (referenceId) name.

Summary (Solution)

I have two options:

The first option seems to be easier to implement:

  • I guess creating a Plugin for ElementNameResolver::getFullIndexName() would be what I need to do (and then make sure I only change the new custom module/s I want to update, keeping everything else intact.

  • Or alternatively fiddle with the DB table creation and creating a function renameIndex() that handles existing index renames, at first I thought there is a function that does this, but it appears only to have ./Setup/Declaration/Schema/Dto/Table::addIndexes (no rename index / update index)

    /**
     * Add indexes
     *
     * This is workaround, as any DTO object couldnt be changed after instantiation.
     * However there is case, when we depends on column definition we need modify our indexes
     *
     * @param array $indexes
     */
    public function addIndexes(array $indexes)
    {
        $this->indexes = array_replace($this->indexes, $indexes);
    }

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.