MySQL

Updated: 10 May 2025

Check the status of the mysql service

sudo systemctl status mysql

Restart the mysql service

sudo systemctl stop mysql.service
sudo systemctl start mysql.service

Updated: 06 July 2024

Dynamically create a MySQL table which accommodates a CSV file, then import that CSV file.

#!/bin/bash

# create-table.sql
# CREATE TABLE `csv` (
#     `id` int(11) NOT NULL AUTO_INCREMENT,
#     PRIMARY KEY (`id`)
# );
mysql -u user -ppassword < create-table.sql

# Read column headers into an array.
IFS=',' read -ra COLS <<< $(head -n1 file.csv)

# Loop through the array and create a
# new table column for each csv column.
for COL in "${COLS[@]}"; do
    mysql -u user -ppassword -e "ALTER TABLE csv ADD \`$COL\` TEXT DEFAULT NULL;"
done

# load-csv-into-table.sql 
# LOAD DATA LOCAL INFILE 'file.csv'
# INTO TABLE `csv`
# FIELDS TERMINATED BY ',' ENCLOSED BY '"'
# LINES TERMINATED BY '\n'
# IGNORE 1 ROWS;
mysql -u user -ppassword < load-csv-into-table.sql

Updated: 11 April 2024

Insert into table the result of a select query

INSERT INTO orders (col1, col2, col3)
SELECT foo, bar, baz
FROM our_table
WHERE condition;

Updated: 11 April 2024

Subqueries with ANY

The ANY keyword means “return TRUE if the comparison is TRUE for ANY of the values in the column that the subquery returns.”

SELECT s1 FROM t1 WHERE s1 > ANY (SELECT s1 FROM t2);

Correlated Subqueries

A correlated subquery is a subquery that contains a reference to a table that also appears in the outer query

SELECT * FROM t1
WHERE column1 = ANY (
    SELECT column1 FROM t2
    WHERE t2.column2 = t1.column2
);

Updated: 11 April 2024

Unexpected results can occur if mysql mode is not set. On a fresh cloud9 workspace (for example) mysql server has no mode settings

mysql> SELECT @@SQL_MODE;
+------------+
| @@SQL_MODE |
+------------+
|            |
+------------+

Consequently truncation and attempts to insert the wrong types can pass without error

mysql> CREATE TABLE TEST (boolfield TINYINT(1), varcharfield VARCHAR(3));
mysql> INSERT INTO TEST VALUES ('A', 'wxyz');                                                                    
Query OK, 1 row affected, 2 warnings (0.01 sec)

mysql> SELECT * FROM TEST;                                                                                        
+-----------+--------------+
| boolfield | varcharfield |
+-----------+--------------+
|         0 | wxy          |
+-----------+--------------+

Two warnings are issued but the query does run ok and results in an unintended boolfield value and a truncation.

Now set (one of many possible) sql modes

mysql> SET @@SQL_MODE = 'STRICT_TRANS_TABLES';

mysql> INSERT INTO TEST (boolfield) VALUES ('A');                                                          
ERROR 1366 (HY000): Incorrect integer value: 'A' for column 'boolfield' at row 1

and

mysql> INSERT INTO TEST (varcharfield) VALUES ('wxyz');                                                                                                                                 
ERROR 1406 (22001): Data too long for column 'varcharfield' at row 1

Updated: 14 October 2024

This is how to enable the log table and query it directly with SQL

SET GLOBAL log_output = 'TABLE';
SET GLOBAL general_log = 'ON';
SELECT * FROM mysql.general_log ORDER BY event_time DESC;

Check the value of log_output

SELECT @@global.log_output;

Enable logging temporarily and write the log entries to a file

SET GLOBAL log_output = 'FILE';
SET GLOBAL general_log_file='/var/log/mysql/mysql-all.log';
SET GLOBAL general_log = 1;

Updated: 13 November 2024

Create a remote user

CREATE USER 'sammy'@'remote_server_ip' IDENTIFIED WITH mysql_native_password|caching_sha2_plugin BY 'password';  
GRANT CREATE, ALTER, DROP, INSERT, UPDATE, DELETE, SELECT, REFERENCES, RELOAD on *.* TO 'sammy'@'remote_server_ip' WITH GRANT OPTION;
FLUSH PRIVILEGES;

Change host for a user

UPDATE mysql.user SET Host='%' WHERE Host='localhost' AND User='username';
FLUSH PRIVILEGES;

Show the authentication plugin for each user

select user, plugin from mysql.user;