Uploaded image for project: 'MariaDB ColumnStore'
  1. MariaDB ColumnStore
  2. MCOL-4480

Alter table add column leaves table unusable.

Details

    • Bug
    • Status: Closed (View Workflow)
    • Critical
    • Resolution: Fixed
    • 5.5.1, 23.02.4, 23.10.0
    • 23.10.2
    • None
    • None
    • CentOS 7
      MariaDB-server 10.5.8_5-1
    • 2024-1

    Description

      When a varchar column is added with "alter table add column" the resulting column fails on insert:

      MariaDB [test]> create table testtext2 (
          -> myvalue varchar(100)
          -> )engine=Columnstore CHARSET=utf8;
      Query OK, 0 rows affected (1.263 sec)
       
      MariaDB [test]> show create table testtext2\G
      *************************** 1. row ***************************
             Table: testtext2
      Create Table: CREATE TABLE `testtext2` (
        `myvalue` varchar(100) DEFAULT NULL
      ) ENGINE=Columnstore DEFAULT CHARSET=utf8
      1 row in set (0.000 sec)
       
      MariaDB [test]> alter table testtext2 add column myvalue2 text;
      Query OK, 0 rows affected (0.863 sec)
      Records: 0  Duplicates: 0  Warnings: 0
       
      MariaDB [test]> show create table testtext2\G
      *************************** 1. row ***************************
             Table: testtext2
      Create Table: CREATE TABLE `testtext2` (
        `myvalue` varchar(100) DEFAULT NULL,
        `myvalue2` text DEFAULT NULL
      ) ENGINE=Columnstore DEFAULT CHARSET=utf8
      1 row in set (0.000 sec)
       
      MariaDB [test]> insert into testtext2 (myvalue2) VALUES ('myvalue');
      ERROR 1815 (HY000): Internal error: CAL0001: Insert Failed:   opening a column file. The file was not found or was inaccessible.
       
      MariaDB [test]> create table testtext1 (
          -> myvalue varchar(100),  
          -> myvalue2 text
          -> )engine=Columnstore CHARSET=utf8;
      Query OK, 0 rows affected (1.633 sec)
       
      MariaDB [test]> insert into testtext1 (myvalue, myvalue2) VALUES ('myvalue', 'myvalue2');
      Query OK, 1 row affected (0.444 sec)
      

      debug.log:

      Dec 30 17:52:26 MariaDB-10-5-8-5-CS-C-133 dmlpackageproc[1256]: 26.219940 |4|20|0| D 21 CAL0001: Start SQL statement:  i
      nsert into testtext2 (myvalue2) VALUES ('myvalue'); |test|
      Dec 30 17:52:26 MariaDB-10-5-8-5-CS-C-133 IDBFile[1227]: 26.295222 |0|0|0| D 35 CAL0002: Failed to open file: /var/lib/c
      olumnstore/data1/128.dir/000.dir/000.dir/000.dir/000.dir/FILE000.cdf, exception: unable to open Buffered file
      Dec 30 17:52:26 MariaDB-10-5-8-5-CS-C-133 writeengine[1227]: 26.295271 |0|20|0| E 19 CAL0080: Compression Handling: Fail
      ed to open compressed data file /var/lib/columnstore/data1/128.dir/000.dir/000.dir/000.dir/000.dir/FILE000.cdf @line: 34
      0
      Dec 30 17:52:26 MariaDB-10-5-8-5-CS-C-133 dmlpackageproc[1227]: 26.295293 |0|0|0| E 21 CAL0001: Error opening dictionary
       file  oid:partition:segment 2147483648:0:0
      Dec 30 17:52:26 MariaDB-10-5-8-5-CS-C-133 dmlpackageproc[1256]: 26.303494 |4|20|0| D 21 CAL0001: End SQL statement
      Dec 30 17:52:26 MariaDB-10-5-8-5-CS-C-133 dmlpackageproc[1256]: 26.303568 |0|0|0| E 21 CAL0001: Insert Failed:   opening
       a column file. The file was not found or was inaccessible.
      

      Attachments

        Activity

          This only breaks with text (maybe blob) columns. varchar does not break in this manner.

          David.Hall David Hall (Inactive) added a comment - This only breaks with text (maybe blob) columns. varchar does not break in this manner.
          webb Webb Phillips added a comment - - edited

          FYI, this isn't minor to me; I'm currently trying to decide between postgres columnar, clickhouse, and mariadb columnstore for OLAP. One of my requirements is to be able to add a text column, and this bug makes it impossible to add a text column and then insert into the columnstore table, so I may be forced to drop it from consideration.

          webb Webb Phillips added a comment - - edited FYI, this isn't minor to me; I'm currently trying to decide between postgres columnar, clickhouse, and mariadb columnstore for OLAP. One of my requirements is to be able to add a text column, and this bug makes it impossible to add a text column and then insert into the columnstore table, so I may be forced to drop it from consideration.

          fixed in develop 26 March 2024

          kirill.perov@mariadb.com Kirill Perov (Inactive) added a comment - fixed in develop 26 March 2024

          People

            leonid.fedorov Leonid Fedorov
            juan.vera Juan
            Votes:
            1 Vote for this issue
            Watchers:
            7 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

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