|
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`;
----------------------
----------------------
| 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`;
-----------------------
-----------------------
| 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%"
-> ;
----------------------------------
----------------------------------
| 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`;
-----------------------
-----------------------
| 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 ?
|