Category Archives: MySQL

MySQL automatically set created and lastupdated timestamps on columns

ALTER TABLE `tablename` ADD COLUMN `created` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, ADD COLUMN `updated` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP; https://dev.mysql.com/doc/refman/5.6/en/timestamp-initialization.html https://dev.mysql.com/doc/refman/5.6/en/data-type-defaults.html Requires MySQL 5.6.5 or better

Transfering MySQL database between hosts

The single command approach mysqldump -h oldhost -u oldusername -poldpassword olddbname | mysql -h newhost -u newusername -pnewpassword newdbname make sure you can … mysql -h newhost -u newusername -pnewpassword newdbname … before attempting the transfer. Common issues are :- Firewall preventing access to MySQL server across the network User configured to only be able… Read More »

my MySQL default configuration

my.cnf file. Try and use this before you start MySQL Server for the first time! [mysqld] log-output=TABLE expire_logs_days=1 general_log=ON general_log_file=/var/db/mysql/mysqld-queries.log slow_query_log=ON slow_query_log_file=/var/db/mysql/mysqld-slow-queries.log log_queries_not_using_indexes=ON log-error=/var/db/mysql/mysqld-error.log default-storage-engine=innodb innodb_file_per_table Run the following SQL script to update the logs to use MyISAM (instead of CSV!). SET @old_log_state = @@global.general_log; SET GLOBAL general_log = ‘OFF’; TRUNCATE TABLE general_log; ALTER TABLE… Read More »

MySQL function to calculate the distance between two latitude and latitude GPS pairs

Selec All Code:1 2 3 4 5 6 7 8 9 10 CREATE DEFINER=`root`@`localhost` FUNCTION `FIND_DISTANCE`(`src_latitude` FLOAT,`src_longitude` FLOAT, `dest_latitude` FLOAT,`dest_longitude` FLOAT) RETURNS INT(11) BEGIN /* This function takes four parameterss, two sets of latitude and latitude pairs and returns the distance between them in miles.   Example use SELECT * , FIND_DISTANCE(home_latitude, home_longitude, 52.5, -1)… Read More »

MySQL Logs – Selecting General Query and Slow Query Log Output Destinations

[mysqld] … log-output=TABLE expire_logs_days=1 general_log=1 general_log_file=/var/log/mysqld-queries.log slow_query_log=1 slow_query_log_file=/var/log/mysqld-slow-queries.log …   Selec All Code:1 2 3 4 5 6 7 8 9 10 11 SET @old_log_state = @@global.general_log; SET GLOBAL general_log = ‘OFF’; ALTER TABLE mysql.general_log ENGINE = MyISAM; SET GLOBAL general_log = @old_log_state; SET GLOBAL general_log = ‘ON’;   SET @old_log_state = @@global.slow_query_log; SET GLOBAL… Read More »

degree2decimal

Selec All Code:1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 DROP FUNCTION IF EXISTS `degree2decimal`;   CREATE DEFINER = `root`@`localhost` FUNCTION `degree2decimal`(deg_coord FLOAT) RETURNS FLOAT BEGIN #converts NMEA/GPS Lat -Long TO DECIMAL Degrees. Code comments are SELF explanatory. #The code IS NOT graceful, but… Read More »