MySQL IF EXISTS - example removing users

It wasn't until MySQL 5.7 where the CREATE USER IF NOT EXISTS and DROP USER IF EXISTS syntax became available. Previous to that you could use SELECT IF (EXISTS... commands to do the same thing.

Here is an example of using SELECT IF (EXISTS... to drop a user.

-- Step 1: First create two test users with different ip ranges:

CREATE USER 'test'@'%' IDENTIFIED BY 'blabla';
CREATE USER 'test'@'10.%' IDENTIFIED BY 'blabla';


-- Step 2: Next set the user you want to drop:

SET @user = 'test';
SELECT (SELECT host
FROM mysql.user WHERE user = @user LIMIT 1) into @ip_range;

-- Step 3: Confirm variables have values

SELECT concat('\'',@user,'\'@\'',@ip_range,'\'');

-- Step 4: Run the SELECT IF (EXISTS and then run the prepared statement

SELECT IF (EXISTS(
         SELECT DISTINCT user
         FROM mysql.user
         WHERE user = @user )
         ,concat('drop user \'',@user,'\'@\'',@ip_range,'\';')
         ,concat('select \'user does not exist: ',@user,'\';')) into @a;
     

PREPARE stmt1 FROM @a;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;

-- Step 5: Check and see if there are additional users with the same name but different IP range, if so repeat

SELECT IF (EXISTS(
         SELECT DISTINCT user
         FROM mysql.user
         WHERE user = @user )
         ,concat('Another user called \'',@user,'\'',' still exists.')
         ,concat('User \'',@user,'\' no longer exists.')) into @b;


SELECT @b;


Here are some actual examples I've written:

-- Clean up monitor_user user which doesn't have host value as %
SET sql_log_bin = 0;
SET @user = 'monitor_user';
SELECT IFNULL((SELECT host
FROM mysql.user WHERE user = @user AND host <> '%' LIMIT 1),'HOST_DOES_NOT_EXISTS') into @ip_range;
SELECT concat('\'',@user,'\'@\'',@ip_range,'\'');
SELECT IF (EXISTS(
         SELECT DISTINCT user
         FROM mysql.user
         WHERE user = @user
         AND host = @ip_range)
         ,concat('drop user \'',@user,'\'@\'',@ip_range,'\';')
         ,concat('select \'user does not exist: ',@user,'@',@ip_range,'\';')) into @a;
         SELECT @a;
PREPARE stmt1 FROM @a;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;
SET sql_log_bin = 1;

-- Clean up root user that has the host value which is the same as the hostname
SET sql_log_bin = 0;
SET @user = 'root';
SELECT IFNULL((SELECT host
FROM mysql.user WHERE user = @user AND host <> '%' AND host = @@hostname LIMIT 1),'HOST_DOES_NOT_EXISTS')  into @ip_range;
SELECT concat('\'',@user,'\'@\'',@ip_range,'\'');
SELECT IF (EXISTS(
         SELECT DISTINCT user
         FROM mysql.user
         WHERE user = @user
          AND host = @ip_range)
         ,concat('drop user \'',@user,'\'@\'',@ip_range,'\';')
         ,concat('select \'user does not exist: ',@user,'\';')) into @a;
         SELECT @a;
PREPARE stmt1 FROM @a;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;
SET sql_log_bin = 1;

-- Remove root user that has host value from previous co-master
SET sql_log_bin = 0;

SELECT (SELECT host
FROM mysql.user
WHERE host NOT IN ('%','localhost','::1','127.0.0.1')
AND host LIKE CONCAT(LEFT(host, LENGTH(host) - 1),'%') LIMIT 1) into @old_co_master;
SELECT @old_co_master;
SET @user = 'root';
SELECT IFNULL((SELECT host
FROM mysql.user WHERE user = @user AND host <> '%' AND host = @old_co_master LIMIT 1),'HOST_DOES_NOT_EXISTS')  into @ip_range;
SELECT concat('\'',@user,'\'@\'',@ip_range,'\'');
SELECT IF (EXISTS(
         SELECT DISTINCT user
         FROM mysql.user
         WHERE user = @user
          AND host = @ip_range)
         ,concat('drop user \'',@user,'\'@\'',@ip_range,'\';')
         ,concat('select \'user does not exist: ',@user,'\';')) into @a;
         SELECT @a;
PREPARE stmt1 FROM @a;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;

SET sql_log_bin = 1;

Concatenate in bash the output of two commands without newline character

I'm working on a bash script and I needed to import some data that I've compressed. However, the tables could also have foreign key constraints that I want ignored. So as I'm importing the dump files, I add "SET FOREIGN_KEY_CHECKS=0" to the file like this:

       { echo "SET FOREIGN_KEY_CHECKS=0;" ; gunzip < my_dump_file.sql.gz ; } | mysql -u${USERNAME} -p${PASSWORD} -h${SERVER} ${DATABASE} 

Found a good example on stackoverflow which taught me how to do this:

Reference:

http://stackoverflow.com/questions/20871534/concatenate-in-bash-the-output-of-two-commands-without-newline-character

Question:

What I need:

Suppose I have two commands, A and B, each of which returns a single-line string (i.e., a string with no newline character, except possibly 1 at the very end). I need a command (or sequence of piped commands) C that concatenates the output of commands A and B on the same line and inserts 1 space character between them.

Answer:

You can use tr:

{ echo "The quick"; echo "brown fox"; } | tr "\n" " "

OR using sed:

{ echo "The quick"; echo "brown fox"; } | sed -e 'N;s/\n/ /'

OUTPUT:

The quick brown fox 

Exporting MySQL results from remote instance to local file system

Why does exporting to a CSV locally from a remote MySQL server have to be so hard?

If you are running MySQL on RDS or don't have access to the file system on the remote server you cannot use the recommend approach to create a CSV file. This would be the recommend way...

SELECT order_id,product_name,qty
FROM orders
WHERE foo = 'bar'
INTO OUTFILE '/tmp/orders.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

I like all the creative ways people on this post came up with to get over this:

http://stackoverflow.com/questions/356578/how-to-output-mysql-query-results-in-csv-format

If you know your data doesn't have commas or tabs then many of these solutions will work just fine. Such as this Example 1. Example 2 seems to account for even having commas or tabs in your data. But extra quotations in the data might be problematic.

Example 1:

mysql --user=wibble --password wobble -B -e "select * from vehicle_categories;" | sed "s/'/\'/;s/\t/\",\"/g;s/^/\"/;s/$/\"/;s/\n//g" > vehicle_categories.csv

Regex Explanation:

• s/// means substitute what's between the first // with what's between the second //
• the "g" at the end is a modifier that means "all instance, not just first"
• ^ (in this context) means beginning of line
• $ (in this context) means end of line

So, putting it all together:

s/'/\'/          replace ' with \'
s/\t/\",\"/g     replace all \t (tab) with ","
s/^/\"/          at the beginning of the line place a "
s/$/\"/          at the end of the line place a "
s/\n//g          replace all \n (newline) with nothing

Example 2:

select concat_ws(',',
    concat('"', replace(field1, '"', '""'), '"'),
    concat('"', replace(field2, '"', '""'), '"'),
    concat('"', replace(field3, '"', '""'), '"'))

from your_table where etc;

Explanation:

1. Replace " with "" in each field --> replace(field1, '"', '""')
2. Surround each result in quotation marks --> concat('"', result1, '"')
3. Place a comma between each quoted result --> concat_ws(',', quoted1, quoted2, ...)

I liked the Example 2 which I have listed above. I've created a script that dynamically generates that type of SQL and then I execute that dynamically generated SQL.

My script runs three commands like this:

# Generate the header
mysql -uUSERNAME -pPASSWORD -hHOSTNAME  -s -e "(SELECT GROUP_CONCAT(CONCAT('\"',COLUMN_NAME,'\"'),'' '' SEPARATOR ',')
        FROM INFORMATION_SCHEMA.COLUMNS c WHERE TABLE_NAME = 'MY_TABLE_NAME' AND TABLE_SCHEMA = 'MY_DATABASE_NAME');" > my_report.csv

# Create SQL that will be used to get the data
mysql -uUSERNAME -pPASSWORD -hHOSTNAME -s -e "SET SESSION group_concat_max_len = 1000000;
        (SELECT CONCAT('SELECT CONCAT_WS(\',\', ', GROUP_CONCAT('CONCAT(\'\"\', REPLACE(',column_name, ',\'\"\', \'\"\"\'), \'\"\')'), ') FROM MY_TABLE_NAME')
        FROM information_schema.columns c
        WHERE c.table_schema = MY_DATABASE_NAME
        AND c.TABLE_NAME = 'MY_TABLE_NAME');" > dynamically_generated_code.sql

# Run the above SQL to get the data
mysql -uUSERNAME -pPASSWORD -hHOSTNAME  -s MY_DATABASE_NAME < dynamically_generated_code.sql >> my_report.csv


Explanation:

1. First query puts the header values for the CSV file and writes them to the CSV file
2. Second query creates the SQL to pull the data so that it will be in comma separated value format.
3. The third query executes the SQL that was generated from step 2 and pulls the data for the CSV file and appends it to the file created in step one.

     

Default location for reading my.cnf file

Running this command will give you some useful information and inform you on where the default options are read from and in what order:

mysqld --verbose --help | head -15

On my system this is what it returns:

# mysqld --verbose --help | head -15

2016-06-08 11:24:30 0 [Warning] Using unique option prefix log-err instead of log_error is deprecated and will be removed in a future release. Please use the full name instead.

2016-06-08 11:24:30 0 [Note] mysqld (mysqld 5.6.24-72.2-log) starting as process 25165 ...

2016-06-08 11:24:30 25165 [Note] Plugin 'FEDERATED' is disabled.

mysqld  Ver 5.6.24-72.2-log for Linux on x86_64 (Source distribution)

Copyright (c) 2009-2015 Percona LLC and/or its affiliates

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Starts the MySQL database server.

Usage: mysqld [OPTIONS]

Default options are read from the following files in the given order:

/etc/my.cnf /etc/mysql/my.cnf ~/.my.cnf 

The following groups are read: mysqld server mysqld-5.6

2016-06-08 11:24:30 25165 [Note] Binlog end

2016-06-08 11:24:30 25165 [Note] Shutting down plugin 'CSV'

2016-06-08 11:24:30 25165 [Note] Shutting down plugin 'MyISAM'