MySQL

Updated: 30 April 2023

SQLSTATE[HY000]: General error: 1418 This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)

select @@log_bin_trust_function_creators;
SET GLOBAL log_bin_trust_function_creators = 1;
select @@log_bin_trust_function_creators;

Updated: 09 March 2023

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: 10 September 2022

Enable logging temporarily

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

Updated: 24 May 2023

Run script on database, redirect output to log

mysql -u root -p db_name < sql_statement_file.sql >> query.log 2>&1

Check if remote mysqld requires secure connections

mysql --ssl-mode=DISABLED -h example.com -u root -p

Execute a statement directly

mysql -u root -p db_name -e "SELECT * FROM tbl_foo LIMIT 5"

Updated: 13 January 2023

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;

Updated: 10 September 2022

User defined variables can be assigned a value in one statement and referred to in another statement.

User variables are written as @var_name

For SET, either = or := can be used as the assignment operator.

User variables can be assigned a value from a limited set of data types: integer, decimal, floating-point, binary or nonbinary string, or NULL.

One way to set a user-defined variable is by issuing a SET statement

SET @var_name = expr [, @var_name = expr] ...

Also see SELECT ... INTO statement.

Updated: 20 September 2022

Show columns statement

SHOW COLUMNS FROM City;

Query for big tables

SELECT 
     table_schema as `Database`, 
     table_name AS `Table`, 
     round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB` 
FROM information_schema.TABLES 
WHERE table_schema = "my_table"
ORDER BY (data_length + index_length) DESC;

Disable foreign key checks

SET foreign_key_checks = 0;
SET foreign_key_checks = 1;