[MCOL-4164] Multi-table DROP Created: 2020-07-12  Updated: 2021-01-12  Resolved: 2020-07-13

Status: Closed
Project: MariaDB ColumnStore
Component/s: DDLProc
Affects Version/s: 1.5.3
Fix Version/s: 5.4.1

Type: Bug Priority: Blocker
Reporter: Roman Assignee: Daniel Lee (Inactive)
Resolution: Fixed Votes: 1
Labels: None

Issue Links:
Relates
relates to MCOL-4109 A ColumnStore warning returned when d... Closed
relates to MDEV-11412 Ensure that table is truly dropped wh... Closed

 Description   

This feature should work for both the "force drop" introduced by MDEV-11412 and for multi-table drop. Currently it throws an error when doing a multi table drop statement on ANY engine if Columnstore is installed, but the table being dropped does not exist.

This is very easy to reproduce. The following is an example of loading the sample "Employees" dataset from datacharmer. These are InnoDB tables and yet we still get a Columnstore error:

[root@mcs1 vagrant]# git clone https://github.com/datacharmer/test_db.git
 
Cloning into 'test_db'...
 
remote: Enumerating objects: 105, done.
 
remote: Total 105 (delta 0), reused 0 (delta 0), pack-reused 105
 
Receiving objects: 100% (105/105), 74.27 MiB | 16.35 MiB/s, done.
 
Resolving deltas: 100% (54/54), done.
 
 
 
[root@mcs1 vagrant]# cd test_db/
 
 
 
[root@mcs1 test_db]# mysql < employees.sql
 
INFO
 
CREATING DATABASE STRUCTURE
 
ERROR 1178 (42000) at line 31: The storage engine for the table doesn't support The syntax or the data type(s) is not supported by Columnstore. Please check the Columnstore syntax guide for supported sy
 
ntax or data types.

This is the statement that throws the error:

DROP TABLE IF EXISTS dept_emp,
 
                     dept_manager,
 
                     titles,
 
                     salaries,
 
                     employees,
 
                     departments;



 Comments   
Comment by Roman [ 2020-07-12 ]

Plz review.

Comment by Gagan Goel (Inactive) [ 2020-07-13 ]

Patch is merged.

For QA: Please test that the multi-table drop statements work as expected with the fix.

Comment by Gregory Dorman (Inactive) [ 2020-07-13 ]

dleeyh - specifically:

1. drop IF EXISTS on multiple non existent tables. Make sure no error messages are given, but warning messages are (like we have on single node)
2. retest single non existent drop.
3. Just in case retest drop of existing tables, single and multiple.
4. Let us know the behavior of IF EXISTS case when some tables exist and others don't. Some inno tables and some cs tables.

Comment by Daniel Lee (Inactive) [ 2020-07-13 ]

Build verified: 1.5.3 (Jenkins b368)

Tested on Centos 7

MariaDB [(none)]> create database d1;
Query OK, 1 row affected (0.001 sec)

MariaDB [(none)]> use d1;
Database changed
MariaDB [d1]> drop table t1;
ERROR 1051 (42S02): Unknown table 'd1.t1'
MariaDB [d1]> drop table if exists t1;
Query OK, 0 rows affected, 1 warning (0.038 sec)

MariaDB [d1]> show warnings;
--------------------------------

Level Code Message

--------------------------------

Note 1051 Unknown table 'd1.t1'

--------------------------------
1 row in set (0.001 sec)

MariaDB [d1]> DROP TABLE dept_emp,
-> dept_manager,
-> titles,
-> salaries,
-> employees,
-> departments;
ERROR 1051 (42S02): Unknown table 'd1.dept_emp,d1.dept_manager,d1.titles,d1.salaries,d1.employees,d1.departments'
MariaDB [d1]> DROP TABLE IF EXISTS dept_emp,
-> dept_manager,
-> titles,
-> salaries,
-> employees,
-> departments;
Query OK, 0 rows affected, 1 warning (0.175 sec)

MariaDB [d1]> show warnings;
--------------------------------------------------------------------------------------------------------

Level Code Message

--------------------------------------------------------------------------------------------------------

Note 1051 Unknown table 'd1.dept_emp,d1.dept_manager,d1.titles,d1.salaries,d1.employees,d1.departments'

--------------------------------------------------------------------------------------------------------
1 row in set (0.001 sec)

MariaDB [d1]> use mytest
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [mytest]> show tables;
------------------

Tables_in_mytest

------------------

lineitem
orders
orders2
quicktest

------------------
4 rows in set (0.001 sec)

MariaDB [mytest]> drop table lineitem;
Query OK, 0 rows affected (0.225 sec)

MariaDB [mytest]> drop table orders, orders2, quicktest;
Query OK, 0 rows affected (1.253 sec)

MariaDB [mytest]> show tables;
Empty set (0.001 sec)

Generated at Thu Feb 08 02:48:17 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.