Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-25997

ALTER TABLE command works on one table, but not the other

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Duplicate
    • Affects Version/s: 10.3.29
    • Fix Version/s: N/A
    • Labels:
      None
    • Environment:

      Description

      See my question over at StackOverflow: https://stackoverflow.com/questions/68083269/mariadb-alter-table-command-works-on-one-table-but-not-the-other?noredirect=1#68083269

      TLDR: Bug happens on the version that is currently in the Debian-Stable APT-Repo, but not on 10.5

      Heres that text if you don't like clicking on links:
      I have two tables (Django Models) in a MariaDB database: `coredb_vsatservicerate` and `coredb_simservicerate`.

      MariaDB [servicedbtest]> SHOW CREATE TABLE `coredb_simservicerate`;
      -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

      Table Create Table

      -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

      coredb_simservicerate CREATE TABLE `coredb_simservicerate` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `name` varchar(80) NOT NULL,
      `service_type` int(11) DEFAULT NULL,
      `category` int(11) NOT NULL,
      `old` tinyint(1) NOT NULL,
      `year` smallint(5) unsigned DEFAULT NULL CHECK (`year` >= 0),
      `reseller` tinyint(1) DEFAULT NULL,
      `description` longtext NOT NULL,
      `description_update_ts` datetime(6) DEFAULT NULL,
      `currency` int(11) DEFAULT NULL,
      `price` decimal(9,2) DEFAULT NULL,
      PRIMARY KEY (`id`)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

      -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      1 row in set (0.000 sec)

      And

      MariaDB [servicedbtest]> SHOW CREATE TABLE `coredb_vsatservicerate`;
      ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

      Table Create Table

      ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

      coredb_vsatservicerate CREATE TABLE `coredb_vsatservicerate` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `name` varchar(80) NOT NULL,
      `cir_download` int(11) NOT NULL,
      `cir_upload` int(11) NOT NULL,
      `mir_download` int(11) NOT NULL,
      `mir_upload` int(11) NOT NULL,
      `price` decimal(7,2) DEFAULT NULL,
      `currency` int(11) DEFAULT NULL,
      `sector` int(11) DEFAULT NULL,
      `coverage_id` int(11) NOT NULL,
      `category` int(11) NOT NULL,
      `old` tinyint(1) NOT NULL,
      `year` smallint(5) unsigned DEFAULT NULL CHECK (`year` >= 0),
      `reseller` tinyint(1) DEFAULT NULL,
      `bandwidth_id` int(11) DEFAULT NULL,
      `description` longtext NOT NULL,
      `description_update_ts` datetime(6) DEFAULT NULL,
      PRIMARY KEY (`id`),
      KEY `coredb_vsatservicera_coverage_id_737c0f97_fk_coredb_co` (`coverage_id`),
      KEY `coredb_vsatservicera_bandwidth_id_b8943d2d_fk_coredb_da` (`bandwidth_id`),
      CONSTRAINT `coredb_vsatservicera_bandwidth_id_b8943d2d_fk_coredb_da` FOREIGN KEY (`bandwidth_id`) REFERENCES `coredb_dataratecombo` (`id`),
      CONSTRAINT `coredb_vsatservicera_coverage_id_737c0f97_fk_coredb_co` FOREIGN KEY (`coverage_id`) REFERENCES `coredb_coverage` (`id`)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

      ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      1 row in set (0.014 sec)

      For `coredb_simservicerate` this command works fine:

      MariaDB [servicedbtest]> ALTER TABLE `coredb_simservicerate` MODIFY `price` numeric(9, 2) NULL;
      Query OK, 0 rows affected (0.001 sec)
      Records: 0 Duplicates: 0 Warnings: 0

      For `coredb_vsatservicerate` this similar command throws an error:

      MariaDB [servicedbtest]> ALTER TABLE `coredb_vsatservicerate` MODIFY `price` decimal(9, 2) NULL;
      ERROR 1054 (42S22): Unknown column '`servicedbtest`.`U0`.`year`' in 'CHECK'

      I have looked at MariaDB's documentation and still no idea why.

      Can you help me?
      Is this a Bug in MariaDB?

      *EDIT:*

      MariaDB [servicedbtest]> show variables like "%version%"
      -> ;
      ---------------------------------------------------------------------------+

      Variable_name Value

      ---------------------------------------------------------------------------+

      in_predicate_conversion_threshold 1000
      innodb_version 10.3.29
      protocol_version 10
      slave_type_conversions  
      system_versioning_alter_history ERROR
      system_versioning_asof DEFAULT
      version 10.3.29-MariaDB-0+deb10u1
      version_comment Debian 10
      version_compile_machine x86_64
      version_compile_os debian-linux-gnu
      version_malloc_library system
      version_source_revision 4f143a88bcb36e94e9edba8a3c5b4a350dcd9bf9
      version_ssl_library YaSSL 2.4.4
      wsrep_patch_version wsrep_25.24

      ---------------------------------------------------------------------------+
      14 rows in set (0.004 sec)

      *EDIT 2*: replaced the DESCRIBE output with SHOW CREATE TABLE output.

      *EDIT 3:* If I remove the check constraint from `year`, the aforementioned `ALTER TABLE` seems to work.

      MariaDB [servicedbtest]> ALTER TABLE `coredb_vsatservicerate` MODIFY `year` smallint(5) unsigned DEFAULT NULL;
      Query OK, 0 rows affected (0.000 sec)
      Records: 0 Duplicates: 0 Warnings: 0

      MariaDB [servicedbtest]> SHOW CREATE TABLE `coredb_vsatservicerate`;
      --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

      Table Create Table

      --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

      coredb_vsatservicerate CREATE TABLE `coredb_vsatservicerate` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `name` varchar(80) NOT NULL,
      `cir_download` int(11) NOT NULL,
      `cir_upload` int(11) NOT NULL,
      `mir_download` int(11) NOT NULL,
      `mir_upload` int(11) NOT NULL,
      `price` decimal(9,2) DEFAULT NULL,
      `currency` int(11) DEFAULT NULL,
      `sector` int(11) DEFAULT NULL,
      `coverage_id` int(11) NOT NULL,
      `category` int(11) NOT NULL,
      `old` tinyint(1) NOT NULL,
      `year` smallint(5) unsigned DEFAULT NULL,
      `reseller` tinyint(1) DEFAULT NULL,
      `bandwidth_id` int(11) DEFAULT NULL,
      `description` longtext NOT NULL,
      `description_update_ts` datetime(6) DEFAULT NULL,
      `internal_comment` varchar(200) DEFAULT NULL,
      PRIMARY KEY (`id`),
      KEY `coredb_vsatservicera_coverage_id_737c0f97_fk_coredb_co` (`coverage_id`),
      KEY `coredb_vsatservicera_bandwidth_id_b8943d2d_fk_coredb_da` (`bandwidth_id`),
      CONSTRAINT `coredb_vsatservicera_bandwidth_id_b8943d2d_fk_coredb_da` FOREIGN KEY (`bandwidth_id`) REFERENCES `coredb_dataratecombo` (`id`),
      CONSTRAINT `coredb_vsatservicera_coverage_id_737c0f97_fk_coredb_co` FOREIGN KEY (`coverage_id`) REFERENCES `coredb_coverage` (`id`)
      ) ENGINE=InnoDB AUTO_INCREMENT=1424 DEFAULT CHARSET=latin1

      --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      1 row in set (0.000 sec)

      MariaDB [servicedbtest]> ALTER TABLE `coredb_vsatservicerate` MODIFY `old` decimal(9, 2) NULL;
      Query OK, 374 rows affected (0.090 sec)
      Records: 374 Duplicates: 0 Warnings: 0

      Is this a Bug in MariaDB 10.3 ?

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              Unassigned Unassigned
              Reporter:
              SMönig Sven Daniel Mönig
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved:

                  Git Integration