MySQL cheat sheet
Last updated: January 24rd, 2006
- Viewing all mysql variables: mysqladmin variables
- Removing users: DROP USER ... (does not always work. To force this use the delete statement on mysql.user table )
- Show processes accessing database server: show processlist
- Starting mysql server: /etc/rc.d/init.d/mysqld start
- MySQL (stored) procedures. This procedure selects 1 of the 2 queries depending on a boolean. 1 query returns less columns than the other. We use a delimiter (//) to prevent execution of the individual statements during ingestion of the procedure. The procdure returns two tables. The second table is a count of the number of entries in the table(view)
DELIMITER //
DROP PROCEDURE IF EXISTS listMessages //
CREATE PROCEDURE listMessages(IN p_start INT, IN p_max INT,IN p_short BOOLEAN)
BEGIN
SET @p_start=p_start;
SET @p_max=p_max;
IF p_short THEN
PREPARE STMT FROM "SELECT ms_type.name as event,source.name as source,target.name
as dest,ms_message.time,ms_message.delay FROM ms_type,ms_message, ms_process
as source,ms_process as target WHERE ms_type.typeid=ms_message.type AND
source.procid=ms_message.source AND target.procid=ms_message.dest LIMIT ?,?";
ELSE
PREPARE STMT FROM "SELECT ms_type.name as event,source.name as source,
target.name as dest, ms_message.payload, ms_message.time,ms_message.delay
FROM ms_type,ms_message, ms_process as source,ms_process as target WHERE
ms_type.typeid=ms_message.type AND source.procid=ms_message.source
AND target.procid=ms_message.dest LIMIT ?,?";
END IF;
EXECUTE STMT USING @p_start,@p_max;
PREPARE STMT FROM "SELECT count(*) as available_messages FROM ms_type,
ms_message, ms_process as source,ms_process as target WHERE
ms_type.typeid=ms_message.type AND source.procid=ms_message.source AND
target.procid=ms_message.dest ";
EXECUTE STMT ;
END;
//