[MDEV-25699] Cannot create new column - check constraint issue reported on an existing json column Created: 2021-05-17  Updated: 2022-02-18  Resolved: 2021-05-17

Status: Closed
Project: MariaDB Server
Component/s: N/A
Affects Version/s: 10.5.10
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Thomas Müller Assignee: Unassigned
Resolution: Duplicate Votes: 0
Labels: None
Environment:

Debian Linux and mariadb docker 10.5.10


Issue Links:
Duplicate
is duplicated by MDEV-25672 table alias from previous statement i... Closed

 Description   

I have an existing table:

CREATE TABLE `Users` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`handle` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`username` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
`password` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`first_name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`last_name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`display_name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`email` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
`phonenumber` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`verified` int(11) NOT NULL DEFAULT 0,
`settings` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL CHECK (json_valid(`settings`)),
`last_login` datetime DEFAULT NULL,
`locked` tinyint(1) NOT NULL DEFAULT 0,
`api_token` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`created_at` timestamp NULL DEFAULT NULL,
`updated_at` timestamp NULL DEFAULT NULL,
`deleted_at` timestamp NULL DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `users_username_unique` (`username`),
UNIQUE KEY `users_email_unique` (`email`),
UNIQUE KEY `Users_handle_uindex` (`handle`),
UNIQUE KEY `users_api_token_unique` (`api_token`)
) ENGINE=InnoDB AUTO_INCREMENT=188 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

Then I want to add another columns

alter table Users add foo int;

The error I see is:

[2021-05-17 16:27:48] [42S22][1054] (conn=1773) Unknown column '`<<database-name>>`.`U`.`settings`' in 'CHECK'

This is properly working on 10.5.9 and 10.5.8

Already known? Happy to dig deeper if helpful! THX



 Comments   
Comment by Alice Sherepa [ 2021-05-17 ]

Thanks for the report! This is the same problem as MDEV-25672, about using alias

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