MySQL miscellaneous

Updated: 01 March 2024

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;

log_bin_trust_function_creators
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;