[MDEV-25997] ALTER TABLE command works on one table, but not the other - Jira

XML

Word

Printable

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Duplicate
Affects Version/s: 10.3.29
Fix Version/s: N/A
Component/s: Data Definition - Alter Table
Labels:
None
Environment:

Hide
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)

Show
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)

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

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

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

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

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

duplicates

MDEV-25672 table alias from previous statement interferes later commands

Closed

Activity

People

Assignee:: Unassigned

Reporter:: Sven Daniel Mönig

Votes:: 0 Vote for this issue

Watchers:: 2 Start watching this issue

Dates

Created:: 2021-06-23 10:32

Updated:: 2021-06-23 12:52

Resolved:: 2021-06-23 12:52

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.