MySQL - How to test user variable and use it in DROP VIEW

Question

I'm new to user variables in MySQL and have run into some errors when trying to use them for anything but a SELECT statement. I'm trying to run a batch job to delete temporary views that are created in my database. The process is:

Step 1: identify the temporary views and put the list in a variable.

Step 2: test the variable to see if it is null

Step 3: drop the views

Issue 1: the drop is not working, even if I don't test for null. I receive this message: ERROR 1064 (42000) at line 13: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '@VIEWLIST_PRE' at line 1

Issue 2: the null test is not working when included. I receive this error: mysql: [Warning] Using a password on the command line interface can be insecure. ERROR 1064 (42000) at line 12: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IF(@VIEWLIST_PRE IS NULL) THEN DROP VIEW @VIEWLIST_PRE' at line 1

Below is the code. How must this be written to work? I really appreciate the help! Thank you!!

SET SESSION group_concat_max_len = 18446744073709551615;

-- Get the PreSummary views
SET @VIEWLIST_PRE = (SELECT
                    CONCAT(
                        GROUP_CONCAT(TABLE_NAME)
                    ) AS stmt
                    FROM information_schema.TABLES
                    WHERE TABLE_SCHEMA = "raptor" AND TABLE_NAME LIKE "%Pre%");

SELECT @VIEWLIST_PRE; -- THIS WORKS!
IF(@VIEWLIST_PRE IS NULL) THEN  -- BREAKS
    DROP VIEW @VIEWLIST_PRE;  -- BREAKS
END IF;

* SOLUTION *

I tweaked Used_By_Already's solution (only one command can be executed at a time in a prepared statement, from what I gather). Here's the final code for the bulk DROP:

SET @VIEWLIST_PRE = (SELECT
                    CONCAT(
                        GROUP_CONCAT(TABLE_NAME)
                    ) AS stmt
                    FROM information_schema.TABLES
                    WHERE TABLE_SCHEMA = "raptor" AND TABLE_NAME LIKE "%Pre%");
SET @DROP_VIEW_PRE = CONCAT('DROP VIEW IF EXISTS ', @VIEWLIST_PRE);
PREPARE stmt1 FROM @DROP_VIEW_PRE;
EXECUTE stmt1;

A good question was raised about why a targeted drop is not being used. The reason is that it's not executing successfully from PHP, but no error was received. However, after much searching, I found the issue and can now do the targeted drop.

I see the aggregation method **GROUP_CONCAT()** but no **GROUP BY** clause, you sure that **GROUP_CONCAT()** have sense? — Shidersz, Oct 06 '18 at 00:36
Why are you creating "temporary views"? Seems like an odd thing to do. In addition you would need to form "dynamic sql" to use variables **as** SQL. — Paul Maxwell, Oct 06 '18 at 01:04
The code works in gathering a list of views (I took it from another stack overflow post). The views are generated by separate code, not posted, in the creation of pivot tables for reports. :-) — Joe, Oct 06 '18 at 13:50

score 0 · Answer 1 · answered Oct 06 '18 at 01:40

0

I cannot be sure why or how you create these temporary views, but instead of attempting some bulk clean-up on a regular basis, why not just use:

drop view IF EXISTS some_view_name;

Just prior to the code you currently use to create each temporary view.

This way it will not matter if the view exists or not.

If you are going to proceed with what looks like a bulk clean-up then the query would need to look more like this:

SET @VIEWLIST_PRE = (concat((select group_concat(concat('drop view if exists ',table_name) SEPARATOR '; ')  
                             from information_schema.views 
                             where table_schema = 'raptor' and table_name  like'%Pre%')
                            ,';'))

then the generated string would look like this:

drop view if exists Pre1; drop view if exists Pre2;

Then you need to use a prepared statement to execute that generated SQL. You CANNOT just include a user variable AS a string of SQL code as a query.

See: MySQL Prepared Sattement Syntax

answered Oct 06 '18 at 01:40

Paul Maxwell

33,002
3
32
51

Thank you! I will try that. – Joe Oct 06 '18 at 13:52
Used_By_Already - with a little tweaking of your solution (I'll post above), I was able to get the bulk drop to work. To elaborate on the reason I'm using the bulk drop instead of a targeted drop (which would be cleaner): a) the views are generated by executing MySQL form PHP. b) the drop is not working from PHP, but I'm also not receiving an error code. When I copy/paste an echo of the command from PHP into MySQL Workbench, the code executes perfectly in MySQL. Thus, I'm unable to locate the issue. Therefore, the bulk drop is a hack, but something I can get to work. – Joe Oct 06 '18 at 15:00
One more consideration - how do I test for an empty / null user variable? I don't want to execute the command if there is nothing to drop. Thanks again - I appreciate it! – Joe Oct 06 '18 at 15:26

Joe · Accepted Answer · 2018-10-07T15:54:45.247

I tweaked Used_By_Already's solution (only one command can be executed at a time in a prepared statement, from what I gather). Here's the final code for the bulk DROP:

SET @VIEWLIST_PRE = (SELECT
                    CONCAT(
                        GROUP_CONCAT(TABLE_NAME)
                    ) AS stmt
                    FROM information_schema.TABLES
                    WHERE TABLE_SCHEMA = "raptor" AND TABLE_NAME LIKE "%Pre%");
SET @DROP_VIEW_PRE = CONCAT('DROP VIEW IF EXISTS ', @VIEWLIST_PRE);
PREPARE stmt1 FROM @DROP_VIEW_PRE;
EXECUTE stmt1;

A good question was raised about why a targeted drop is not being used. The reason is that it was not executing successfully from PHP, but no error was received. However, after much searching, I found the issue and can now do the targeted drop.

MySQL - How to test user variable and use it in DROP VIEW

2 Answers2