0

Question

How do I configure db_schema.xml to ignore columns inside the destination table when 'upgrading/renaming' a table?

Information

  • The module has been refactored and rebuilt with a few more requirements, but using an older module' information, so multiple destination tables have extra columns.

Suppose I have a source table userdata, and destination (new) table userdata_new with definitions as below:

EXPLAIN userdata;

id
name

EXPLAIN userdata_new;

user_id
firstname
surname
usertype_id

The table definition that I tried: I thought I might use onCreate migrateDataFrom() for the columns I need:

    <table name="userdata_new" onCreate="migrateDataFromAnotherTable(userdata)" resource="default" engine="innodb" comment="Table `userdata_new`">
        <column name="user_id" xsi:type="smallint" onCreate="migrateDataFrom(id)" padding="6" unsigned="true" nullable="false" identity="true" comment="User Entity Id"/>
        <column name="firstname" xsi:type="varchar" onCreate="migrateDataFrom(name)" disabled="false" nullable="false" comment="Name of User" length="64"/>
        <column name="surname" xsi:type="varchar" disabled="false" nullable="false" comment="Surname" length="64"/>
        <column name="usertype_id" xsi:type="smallint" padding="6" unsigned="true" nullable="false" identity="false" default="0" comment="Attribute ID"/>
        <!-- CONSTRAINTS AND INDEXES -->
        <constraint xsi:type="primary" referenceId="PRIMARY">
            <column name="user_id"/>
        </constraint>
        <constraint xsi:type="unique" referenceId="UNIQUE_USERDATA_USER_FULLNAME">
            <column name="firstname"/>
            <column name="surname"/>
        </constraint>
        <constraint xsi:type="foreign" referenceId="FK_USERDATA_USERTYPE_USERTYPE_ID" table="userdata_new" column="usertype_id" referenceTable="userdata_newtype" referenceColumn="usertype_id" onDelete="CASCADE"/>
        <index referenceId="IDX_GM_OSHU_USERTYPE_USERTYPE_ID" indexType="btree">
            <column name="usertype_id"/>
        </index>
        <!-- END CONTRAINTS AND INDEXES -->
    </table>

The issue:

  • Running bin/magento setup:upgrade to upgrade Schema creation/updates fails with:
    Insert value list does not match column list: 1136 Column count doesn't match value
 count at row 1, query was: INSERT INTO `userdata_new` SELECT `userdata`.* FROM `userdata`

Clearly from the error message SQL query, I can see it is using SELECT * FROM, so what is the purpose of migrateDataFrom() if that doesn't even get used in this query?

How can I Import using the source table, ignore columns (empty) (in destination table) so that it can become something like this:

INSERT INTO userdata_new (user_id, firstname) VALUES(12, 'FirstName').

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.