Back (Current repo: scraps)

db_logging.sql (1291B)

USE some_schema;

CREATE TABLE some_schema.sp_logging (
    `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    `runtime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP(),
    `user` VARCHAR(255) NOT NULL,
    `server_id` INT UNSIGNED NOT NULL,
    `sp` VARCHAR(255) NOT NULL,
    `db` VARCHAR(255) NOT NULL,
    `comment` VARCHAR(512) NOT NULL,
    `processlist` TEXT DEFAULT NULL,
    PRIMARY KEY (`id`, server_id),
    KEY sp_logging_idx1 (`sp`, `runtime`)
) ;

DELIMITER $$
CREATE PROCEDURE some_schema.log(IN i_sp VARCHAR(255), IN i_db VARCHAR(255), i_comment VARCHAR(512))
    MODIFIES SQL DATA
BEGIN
    INSERT INTO some_schema.sp_logging VALUES (null, null, CURRENT_USER(), (SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_VARIABLES WHERE VARIABLE_NAME = 'server_id'), i_sp, i_db, i_comment, (SELECT GROUP_CONCAT('[', CONCAT('USER: ', user, 'DB: ', db, 'TIME: ', NOW(), 'SQL: ', LEFT(info,512)), ']') FROM information_schema.processlist WHERE info IS NOT NULL AND id<>connection_id()));

END $$

DELIMITER ;

-- THE DEFINER OF THIS SP SHOULD HAVE EXECUTE RIGHTS TO IT, AS WELL AS SELECT AND INSERT to this some_schema.sp_logging table!!!
-- GIVE EXEC RIGHTS TO WHICHEVER USERS NEED TO LOG STUFF INTO THEIR TABLE

-- CALL some_schema.log('the sp', 'the schema', 'some message');