1

Here is my initial schema.xml , and run setup:upgrade, a table will be created

<table name="custom_channelintegration_sync_data">
    <column xsi:type="int" name="batch_id" padding="10" unsigned="true" nullable="false" identity="true" comment="primary key"/>
    <column xsi:type="varchar" name="ref_id" nullable="true" comment="ref_id"/>
    <column xsi:type="tinyint" name="status" padding="2" unsigned="true" nullable="false" comment="0:problematic 1:ok" default="1"/>
    <column xsi:type="text" name="data" nullable="true" comment="original data"/>
    <column xsi:type="timestamp" name="create_time" default="CURRENT_TIMESTAMP" on_update="false" comment="create time"/>
    <column xsi:type="timestamp" name="update_time" default="CURRENT_TIMESTAMP" on_update="false" comment="update time"/>
    <column xsi:type="varchar" name="sku" nullable="true" comment="sku"/>
    <constraint xsi:type="primary" referenceId="PRIMARY">
        <column name="batch_id"/>
    </constraint>
    <constraint xsi:type="index" referenceId="CHANNEL_REF_ID_INDEX">
        <column name="ref_id"/>
    </constraint>
</table>

after the table is created, i want to add a column

<table name="custom_channelintegration_sync_data">
        <column xsi:type="int" name="batch_id" padding="10" unsigned="true" nullable="false" identity="true" comment="primary key"/>
        <column xsi:type="varchar" name="ref_id" nullable="true" comment="ref_id"/>
        <column xsi:type="varchar" name="marketplace" nullable="true" comment="marketplace"/>
        <column xsi:type="tinyint" name="status" padding="2" unsigned="true" nullable="false" comment="0:problematic 1:ok" default="1"/>
        <column xsi:type="text" name="data" nullable="true" comment="original data"/>
        <column xsi:type="timestamp" name="create_time" default="CURRENT_TIMESTAMP" on_update="false" comment="create time"/>
        <column xsi:type="timestamp" name="update_time" default="CURRENT_TIMESTAMP" on_update="false" comment="update time"/>
        <column xsi:type="varchar" name="sku" nullable="true" comment="sku"/>
        <constraint xsi:type="primary" referenceId="PRIMARY">
            <column name="batch_id"/>
        </constraint>
        <constraint xsi:type="index" referenceId="CHANNEL_REF_ID_INDEX">
            <column name="ref_id"/>
        </constraint>
    </table>

except delete the table, and re-run the setup:upgrade, what is the normal procedure to upgrade table structure?

i'tried run

bin/magento setup:db-declaration:generate-whitelist --module-name=Custom_ChannelIntegration && bin/magento setup:upgrade

but there is an error

SQLSTATE[42000]: Syntax error or access violation: 1061 Duplicate key name 'CUSTOM_CHANNELINTEGRATION_SYNC_DATA_REF_ID', query was: ALTER TABLE `custom_channelintegration_sync_data` ADD INDEX `CUSTOM_CHANNELINTEGRATION_SYNC_DATA_REF_ID` (`ref_id`)

2 Answers 2

0

replace

<constraint xsi:type="index" referenceId="CHANNEL_REF_ID_INDEX">
            <column name="ref_id"/>
        </constraint>

by

<index referenceId="CHANNEL_REF_ID_INDEX" indexType="btree">
            <column name="ref_id"/>
        </index>
4
  • what is the meaning of btree?
    – hkguile
    Aug 17, 2022 at 9:14
  • this is not working, error Notice: Undefined index: type in /var/www/b2bdev/vendor/magento/framework/Setup/Declaration/Schema/Declaration/SchemaBuilder.php on line 352
    – hkguile
    Aug 17, 2022 at 9:20
  • developer.adobe.com/commerce/php/development/components/…. It defines the way mysql will organise the data behind the scene. Nothing to do with your issue though Aug 17, 2022 at 9:22
  • you may disable the module first (verify the schema is removed: that should be the case since you have a whitelist file in place) and then enable it again, the final schema should come to life. I have it working on my local and was able to replicate your issue before Aug 17, 2022 at 9:25
0

Unrelated to the topic directly, but i found it interresting to still show this here as a response to the comment

What is the meaning of btree ?

To illustrate it's just a data structure system that allows to store data in a balanced way (the depth of the tree is equal for all 'leaf node' ) and as a consequence to access these data faster cause you don't have to run threw hundreds of pointers to get the data you are looking for . I assume most of database works with btree for performance issue.

btree

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.