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

SELECT joining two empty Columnstore tables and involving recursive CTE crashes server

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Duplicate
    • 6.2.1, 6.3.1
    • 23.02.1
    • PrimProc

    Description

      Consider the following tables:

      [root@c2eec0bd325e /]# mysql
      Welcome to the MariaDB monitor.  Commands end with ; or \g.
      Your MariaDB connection id is 4
      Server version: 10.6.7-3-MariaDB MariaDB Server
       
      Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
       
      Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
       
      MariaDB [(none)]> create database test;
      Query OK, 1 row affected (0.001 sec)
       
      MariaDB [(none)]> use test;
      Database changed
      MariaDB [test]> CREATE TABLE `d_currency` (   `currency_sk` int(11) unsigned NOT NULL COMMENT 'autoincrement=1',   `exchange_rate_date` date NOT NULL DEFAULT '0000-00-00',   `from_currency_code` char(3) NOT NULL DEFAULT 'Non',   `from_currency_desc` char(20) NOT NULL DEFAULT 'None',   `to_currency_code` char(3) NOT NULL DEFAULT 'Non',   `exchange_rate` decimal(10,4) NOT NULL DEFAULT 0.0000,   `to_currency_desc` char(20) NOT NULL DEFAULT 'None',   `rate_src` varchar(200) NOT NULL DEFAULT 'None',   `current_year_fl` tinyint(4) NOT NULL DEFAULT 0,   `curr_in` tinyint(4) NOT NULL DEFAULT 1,   `rec_eff_dt` date NOT NULL DEFAULT '1970-01-01',   `rec_exp_dt` date NOT NULL DEFAULT '2099-01-01',   `created_date` datetime NOT NULL,   `modified_date` datetime NOT NULL,   `hash_key` varchar(64) DEFAULT NULL ) ENGINE=Columnstore DEFAULT CHARSET=utf8mb3;
      Query OK, 0 rows affected (12.331 sec)
       
      MariaDB [test]> CREATE TABLE `f_bookings` (   `bookings_sk` int(11) unsigned NOT NULL,   `booking_id` varchar(200) NOT NULL DEFAULT '0',   `db_source_sk` int(11) unsigned NOT NULL,   `booking_type` varchar(200) NOT NULL DEFAULT 'None',   `booking_ts` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',   `valid_from_dt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',   `valid_end_dt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',   `status` varchar(200) NOT NULL DEFAULT 'None',   `order_id` varchar(50) NOT NULL DEFAULT 'None',   `ord_sk` int(11) unsigned NOT NULL DEFAULT 0,   `acct_id` varchar(128) NOT NULL DEFAULT 'None',   `acct_sk` int(11) unsigned NOT NULL DEFAULT 0,   `oppor_id` char(18) NOT NULL DEFAULT 'None',   `oppor_sk` int(11) unsigned NOT NULL DEFAULT 0,   `product_id` varchar(128) NOT NULL DEFAULT '0',   `prod_sk` int(11) unsigned NOT NULL DEFAULT 0,   `original_prod_sk` int(11) NOT NULL DEFAULT 0,   `currency_id` varchar(200) NOT NULL DEFAULT 'None',   `qty` int(11) NOT NULL DEFAULT 0,   `purchased_names` int(11) NOT NULL DEFAULT 0,   `purchased_wc_names` int(11) NOT NULL DEFAULT 0,   `local_amt` decimal(10,2) NOT NULL DEFAULT 0.00,   `sales_rep` varchar(50) NOT NULL DEFAULT 'None',   `order_platform` varchar(200) NOT NULL DEFAULT 'None',   `lifetime` int(11) NOT NULL DEFAULT 0,   `tax_amt` decimal(10,2) NOT NULL DEFAULT 0.00,   `multi_yr_fl` tinyint(4) NOT NULL DEFAULT 0,   `multi_yr_valid_till_dt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',   `pymnt_mthd` varchar(200) NOT NULL DEFAULT 'None',   `emp_sk` int(11) NOT NULL DEFAULT 0,   `booking_sales_mtn` varchar(200) NOT NULL DEFAULT 'None',   `bus_unit` varchar(20) NOT NULL DEFAULT 'None',   `created_date` datetime NOT NULL,   `modified_date` datetime NOT NULL,   `batch_id` bigint(20) NOT NULL DEFAULT 0,   `partner_rep` varchar(200) NOT NULL DEFAULT 'None',   `partner_name` varchar(200) NOT NULL DEFAULT 'None',   `sub_class` varchar(200) NOT NULL DEFAULT 'None',   `premium_vs_standard` varchar(20) NOT NULL DEFAULT 'Standard',   `is_frozen` tinyint(4) DEFAULT NULL,   `is_auto_invoice` tinyint(4) NOT NULL DEFAULT 0,   `sales_region` varchar(200) NOT NULL DEFAULT 'None',   `bookings_country` varchar(200) NOT NULL DEFAULT 'None',   `state_code` varchar(200) NOT NULL DEFAULT 'None' ) ENGINE=Columnstore DEFAULT CHARSET=utf8mb4;
      Query OK, 0 rows affected (19.321 sec)
      

      The following query crashes/restarts the server, with nothing useful in the error log:

      MariaDB [test]> WITH RECURSIVE periods AS (     SELECT         LAST_DAY(CURDATE()) AS period_end     UNION ALL     SELECT         period_end - INTERVAL 1 MONTH AS period_end     FROM          periods     WHERE          period_end - INTERVAL 1 MONTH >= (LAST_DAY(CURDATE()) - INTERVAL 3 MONTH) ) SELECT      periods.period_end,     SUM(ROUND(local_amt / current_fx.exchange_rate, 2)) AS apttus_arr FROM periods INNER JOIN f_bookings ON      f_bookings.booking_type IN ('tsg_apttus')  AND      periods.period_end BETWEEN f_bookings.valid_from_dt AND f_bookings.valid_end_dt LEFT JOIN d_currency current_fx ON         f_bookings.currency_id = current_fx.from_currency_code AND         YEAR(CURDATE()) = YEAR(current_fx.rec_eff_dt) GROUP BY     1;
      ERROR 2013 (HY000): Lost connection to server during query
      MariaDB [test]> select @@datadir, @@log_error;
      ERROR 2006 (HY000): Server has gone away
      No connection. Trying to reconnect...
      Connection id:    3
      Current database: test
       
      +-----------------+------------------------------------------------+
      | @@datadir       | @@log_error                                    |
      +-----------------+------------------------------------------------+
      | /var/lib/mysql/ | /var/log/mariadb/columnstore/mariadb-error.log |
      +-----------------+------------------------------------------------+
      1 row in set (0.012 sec)
       
      MariaDB [test]> exit
      Bye
      [root@c2eec0bd325e /]# tail -100 /var/log/mariadb/columnstore/mariadb-error.log
      2022-08-19 12:22:42 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
      2022-08-19 12:22:42 0 [Note] InnoDB: 10.6.7 started; log sequence number 51735; transaction id 33
      2022-08-19 12:22:42 0 [Note] Plugin 'FEEDBACK' is disabled.
      2022-08-19 12:22:42 0 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool
      2022-08-19 12:22:42 server_audit: MariaDB Audit Plugin version 2.4.1 STARTED.
      2022-08-19 12:22:42 server_audit: Query cache is enabled with the TABLE events. Some table reads can be veiled.
      2022-08-19 12:22:42 0 [Note] InnoDB: Buffer pool(s) load completed at 220819 12:22:42
      2022-08-19 12:22:42 0 [Note] Server socket created on IP: '0.0.0.0'.
      2022-08-19 12:22:42 0 [Note] Server socket created on IP: '::'.
      2022-08-19 12:22:42 0 [Note] /usr/sbin/mariadbd: ready for connections.
      Version: '10.6.7-3-MariaDB'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MariaDB Server
      2022-08-19 12:22:44 0 [Note] /usr/sbin/mariadbd (initiated by: unknown): Normal shutdown
      2022-08-19 12:22:44 0 [Note] InnoDB: FTS optimize thread exiting.
      2022-08-19 12:22:44 server_audit: STOPPED
      2022-08-19 12:22:44 0 [Note] InnoDB: Starting shutdown...
      2022-08-19 12:22:44 0 [Note] InnoDB: Dumping buffer pool(s) to /var/lib/mysql/ib_buffer_pool
      2022-08-19 12:22:44 0 [Note] InnoDB: Buffer pool(s) dump completed at 220819 12:22:44
      2022-08-19 12:22:45 0 [Note] InnoDB: Removed temporary tablespace data file: "./ibtmp1"
      2022-08-19 12:22:45 0 [Note] InnoDB: Shutdown completed; log sequence number 52149; transaction id 34
      2022-08-19 12:22:45 0 [Note] /usr/sbin/mariadbd: Shutdown complete
       
      220819 12:22:45 mysqld_safe mysqld from pid file /var/lib/mysql/c2eec0bd325e.pid ended
      220819 12:22:54 mysqld_safe Starting mariadbd daemon with databases from /var/lib/mysql
      2022-08-19 12:22:54 0 [Note] /usr/sbin/mariadbd (server 10.6.7-3-MariaDB) starting as process 850 ...
      220819 12:22:54 Columnstore: Started; Version: 6.2.1-1
      2022-08-19 12:22:54 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
      2022-08-19 12:22:54 0 [Note] InnoDB: Number of pools: 1
      2022-08-19 12:22:54 0 [Note] InnoDB: Using crc32 + pclmulqdq instructions
      2022-08-19 12:22:54 0 [Note] mariadbd: O_TMPFILE is not supported on /tmp (disabling future attempts)
      2022-08-19 12:22:54 0 [Note] InnoDB: Using Linux native AIO
      2022-08-19 12:22:54 0 [Note] InnoDB: Initializing buffer pool, total size = 134217728, chunk size = 134217728
      2022-08-19 12:22:54 0 [Note] InnoDB: Completed initialization of buffer pool
      2022-08-19 12:22:54 0 [Note] InnoDB: 128 rollback segments are active.
      2022-08-19 12:22:54 0 [Note] InnoDB: Creating shared tablespace for temporary tables
      2022-08-19 12:22:54 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
      2022-08-19 12:22:54 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
      2022-08-19 12:22:54 0 [Note] InnoDB: 10.6.7 started; log sequence number 52149; transaction id 33
      2022-08-19 12:22:54 0 [Note] Plugin 'FEEDBACK' is disabled.
      2022-08-19 12:22:54 0 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool
      2022-08-19 12:22:54 server_audit: MariaDB Audit Plugin version 2.4.1 STARTED.
      2022-08-19 12:22:54 server_audit: Query cache is enabled with the TABLE events. Some table reads can be veiled.
      2022-08-19 12:22:55 0 [Note] Server socket created on IP: '0.0.0.0'.
      2022-08-19 12:22:55 0 [Note] Server socket created on IP: '::'.
      2022-08-19 12:22:55 0 [Note] InnoDB: Buffer pool(s) load completed at 220819 12:22:55
      2022-08-19 12:22:55 0 [Note] /usr/sbin/mariadbd: ready for connections.
      Version: '10.6.7-3-MariaDB'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MariaDB Server
      220819 12:30:56 mysqld_safe Number of processes running now: 0
      220819 12:30:56 mysqld_safe mysqld restarted
      2022-08-19 12:30:56 0 [Note] /usr/sbin/mariadbd (server 10.6.7-3-MariaDB) starting as process 962 ...
      220819 12:30:57 Columnstore: Started; Version: 6.2.1-1
      2022-08-19 12:30:57 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
      2022-08-19 12:30:57 0 [Note] InnoDB: Number of pools: 1
      2022-08-19 12:30:57 0 [Note] InnoDB: Using crc32 + pclmulqdq instructions
      2022-08-19 12:30:57 0 [Note] mariadbd: O_TMPFILE is not supported on /tmp (disabling future attempts)
      2022-08-19 12:30:57 0 [Note] InnoDB: Using Linux native AIO
      2022-08-19 12:30:57 0 [Note] InnoDB: Initializing buffer pool, total size = 134217728, chunk size = 134217728
      2022-08-19 12:30:57 0 [Note] InnoDB: Completed initialization of buffer pool
      2022-08-19 12:30:57 0 [Note] InnoDB: Starting crash recovery from checkpoint LSN=52149,52149
      2022-08-19 12:30:57 0 [Note] InnoDB: 128 rollback segments are active.
      2022-08-19 12:30:57 0 [Note] InnoDB: Removed temporary tablespace data file: "./ibtmp1"
      2022-08-19 12:30:57 0 [Note] InnoDB: Creating shared tablespace for temporary tables
      2022-08-19 12:30:57 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
      2022-08-19 12:30:57 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
      2022-08-19 12:30:57 0 [Note] InnoDB: 10.6.7 started; log sequence number 52513; transaction id 33
      2022-08-19 12:30:57 0 [Note] Plugin 'FEEDBACK' is disabled.
      2022-08-19 12:30:57 0 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool
      2022-08-19 12:30:57 server_audit: MariaDB Audit Plugin version 2.4.1 STARTED.
      2022-08-19 12:30:57 server_audit: Query cache is enabled with the TABLE events. Some table reads can be veiled.
      2022-08-19 12:30:57 0 [Note] Server socket created on IP: '0.0.0.0'.
      2022-08-19 12:30:57 0 [Note] Server socket created on IP: '::'.
      2022-08-19 12:30:57 0 [Note] InnoDB: Buffer pool(s) load completed at 220819 12:30:57
      2022-08-19 12:30:57 0 [Note] /usr/sbin/mariadbd: ready for connections.
      Version: '10.6.7-3-MariaDB'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MariaDB Server
      220819 12:34:15 mysqld_safe Number of processes running now: 0
      220819 12:34:15 mysqld_safe mysqld restarted
      2022-08-19 12:34:15 0 [Note] /usr/sbin/mariadbd (server 10.6.7-3-MariaDB) starting as process 1024 ...
      220819 12:34:15 Columnstore: Started; Version: 6.2.1-1
      2022-08-19 12:34:15 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
      2022-08-19 12:34:15 0 [Note] InnoDB: Number of pools: 1
      2022-08-19 12:34:15 0 [Note] InnoDB: Using crc32 + pclmulqdq instructions
      2022-08-19 12:34:15 0 [Note] mariadbd: O_TMPFILE is not supported on /tmp (disabling future attempts)
      2022-08-19 12:34:15 0 [Note] InnoDB: Using Linux native AIO
      2022-08-19 12:34:15 0 [Note] InnoDB: Initializing buffer pool, total size = 134217728, chunk size = 134217728
      2022-08-19 12:34:15 0 [Note] InnoDB: Completed initialization of buffer pool
      2022-08-19 12:34:15 0 [Note] InnoDB: Starting crash recovery from checkpoint LSN=52149,52149
      2022-08-19 12:34:15 0 [Note] InnoDB: 128 rollback segments are active.
      2022-08-19 12:34:15 0 [Note] InnoDB: Removed temporary tablespace data file: "./ibtmp1"
      2022-08-19 12:34:15 0 [Note] InnoDB: Creating shared tablespace for temporary tables
      2022-08-19 12:34:15 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
      2022-08-19 12:34:15 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
      2022-08-19 12:34:15 0 [Note] InnoDB: 10.6.7 started; log sequence number 52525; transaction id 33
      2022-08-19 12:34:15 0 [Note] Plugin 'FEEDBACK' is disabled.
      2022-08-19 12:34:15 server_audit: MariaDB Audit Plugin version 2.4.1 STARTED.
      2022-08-19 12:34:15 server_audit: Query cache is enabled with the TABLE events. Some table reads can be veiled.
      2022-08-19 12:34:15 0 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool
      2022-08-19 12:34:16 0 [Note] Server socket created on IP: '0.0.0.0'.
      2022-08-19 12:34:16 0 [Note] Server socket created on IP: '::'.
      2022-08-19 12:34:16 0 [Note] /usr/sbin/mariadbd: ready for connections.
      Version: '10.6.7-3-MariaDB'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MariaDB Server
      2022-08-19 12:34:16 0 [Note] InnoDB: Buffer pool(s) load completed at 220819 12:34:16
      [root@c2eec0bd325e /]# 
      

      Crash does not happen with similar InnoDB tables.

      Attachments

        Issue Links

          Activity

            MCOL-4778 was included in 6.4.1. This merely stopped the crash. It now reports not supported. Adding support for Recursive CTE is MCOL-5142.

            David.Hall David Hall (Inactive) added a comment - MCOL-4778 was included in 6.4.1. This merely stopped the crash. It now reports not supported. Adding support for Recursive CTE is MCOL-5142 .

            This MCOL is marked as a duplicate of MCOL-4778, which has been verified and closed for release 6.4.1. It is not yet tested for 23.02.01 and it should have been sent to QA for verification.

            dleeyh Daniel Lee (Inactive) added a comment - This MCOL is marked as a duplicate of MCOL-4778 , which has been verified and closed for release 6.4.1. It is not yet tested for 23.02.01 and it should have been sent to QA for verification.

            Build verified:

            engine: b2206521cea8c15c0bf6e8d217a08783c483a963
            server: 2b721f6864d135aa9a1916798e8daf29e213211e
            buildNo: 6716

            Verified test cases in both MCOL-5202 and MCOL-4778

            dleeyh Daniel Lee (Inactive) added a comment - Build verified: engine: b2206521cea8c15c0bf6e8d217a08783c483a963 server: 2b721f6864d135aa9a1916798e8daf29e213211e buildNo: 6716 Verified test cases in both MCOL-5202 and MCOL-4778

            People

              David.Hall David Hall (Inactive)
              valerii Valerii Kravchuk
              Votes:
              0 Vote for this issue
              Watchers:
              4 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.