[MDEV-24274] ALTER TABLE with CHECK CONSTRAINTS gives "Out of Memory" error Created: 2020-11-24  Updated: 2021-04-19  Resolved: 2021-02-08

Status: Closed
Project: MariaDB Server
Component/s: Data Definition - Alter Table
Affects Version/s: 10.4.16, 10.5.8, 10.4, 10.5
Fix Version/s: 10.4.18, 10.5.9

Type: Bug Priority: Major
Reporter: Nilnandan Joshi Assignee: Sergei Golubchik
Resolution: Fixed Votes: 0
Labels: CONSTRAINT, regression

Issue Links:
Duplicate
is duplicated by MDEV-24785 ALTER TABLE with CHECK CONSTRAINTS gi... Closed

 Description   

ALTER TABLE with CHECK CONSTRAINTS gives "Out of Memory" error even table is empty and just created. Also, already confirmed that there is enough memory on the instance. 

MariaDB [test]> CREATE TABLE `tab` (
 
    ->   `id` char(36) NOT NULL,
 
    ->   `created` datetime(6) NOT NULL,
 
    ->   `updated` datetime(6) NOT NULL,
 
    ->   `convid` varchar(50) NOT NULL,
 
    ->   `caller_id` varchar(2) NOT NULL,
 
    ->   `subject` varchar(1024) NOT NULL,
 
    ->   `body` varchar(1024) NOT NULL,
 
    ->   `is_read` bit(1) NOT NULL DEFAULT b'0',
 
    ->   `is_reply` bit(1) NOT NULL DEFAULT b'0',
 
    ->   `dest_id` char(2) NOT NULL,
 
    ->   `is_error` bit(1) NOT NULL DEFAULT b'0',
 
    ->   PRIMARY KEY (`id`),
 
    ->   CONSTRAINT `dest_id` CHECK (`dest_id` regexp '^[A-Z]{2}$'),
 
    ->   CONSTRAINT `caller_id` CHECK (`caller_id` regexp '^[A-Z]{2}$')
 
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;
 
Query OK, 0 rows affected (0.028 sec)
 
 
 
MariaDB [test]> alter table tab force;
 
ERROR 1041 (HY000): Out of memory.
 
MariaDB [test]> alter table tab encrypted=yes;
 
ERROR 1041 (HY000): Out of memory.

But if we remove CHECK CONSTRAINTS, it works. 

MariaDB [test]> CREATE TABLE `tab1` (
 
    ->   `id` char(36) NOT NULL,
 
    ->   `created` datetime(6) NOT NULL,
 
    ->   `updated` datetime(6) NOT NULL,
 
    ->   `convid` varchar(50) NOT NULL,
 
    ->   `caller_id` varchar(2) NOT NULL,
 
    ->   `subject` varchar(1024) NOT NULL,
 
    ->   `body` varchar(1024) NOT NULL,
 
    ->   `is_read` bit(1) NOT NULL DEFAULT b'0',
 
    ->   `is_reply` bit(1) NOT NULL DEFAULT b'0',
 
    ->   `dest_id` char(2) NOT NULL,
 
    ->   `is_error` bit(1) NOT NULL DEFAULT b'0',
 
    ->   PRIMARY KEY (`id`)
 
    ->  ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;
 
Query OK, 0 rows affected (0.028 sec)
 
 
 
MariaDB [test]> alter table tab1 force;
 
Query OK, 0 rows affected (0.084 sec)
 
Records: 0  Duplicates: 0  Warnings: 0
 
 
 
MariaDB [test]> alter table tab1 encrypted=yes;
 
ERROR 1478 (HY000): Table storage engine 'InnoDB' does not support the create option 'ENCRYPTION_KEY_ID'
 
MariaDB [test]>



 Comments   
Comment by Alice Sherepa [ 2020-11-24 ]

Repeatable with InnoDB/MyIsam, on 10.4-10.5

CREATE TABLE t1 ( id varchar(2), CONSTRAINT id CHECK (id regexp '[a-z]')) ;
 
ALTER TABLE t1 FORCE;

regression appeared after 76063c2a13 (MDEV-20494)

Comment by pk [ 2021-02-04 ]

Any updates on this?

Comment by pk [ 2021-02-05 ]

please let know if this is fixed and since when this issue is happening, so that we can plan other versions for our installs.

Comment by Sergei Golubchik [ 2021-02-05 ]

yes, it is planned to be fixed in the next release

Comment by Alice Sherepa [ 2021-02-05 ]

(introduced in 10.4.13)

Comment by pk [ 2021-02-10 ]

can you confirm the exact release version and release date for the availability of the fix please?

Comment by Sergei Golubchik [ 2021-02-10 ]

The exact release version is above, in the "Fix Version/s" field.
The planned release date is on the jira start page.

Generated at Thu Feb 08 09:28:45 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.