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 ?
I tested this using a MariaDB 10.5 docker-container ( https://hub.docker.com/r/mariadb/columnstore ) and the issue didn't occur there.
It seems that this is a Bug in the version that is currently in the Debian-Stable APT-repositories.
Thanks to Akina and Bill Karwin for help