Many maintenance procedures need to generate SQL as strings, and then need to execute that string as SQL. Use PREPARE for this.

CODE:CREATE DATABASE `admin`;
USE admin

CREATE PROCEDURE `execute`(in cmd text)
SQL SECURITY INVOKER
begin
  set @x = cmd; 
  prepare x from @x;   
  execute x;   
  drop prepare x; 
end

(as requested on Freenode:#mysql)

I requested this urgently for 5.1 and Brian made it happen: An information_schema table called processlist. This is the table version of the "SHOW FULL PROCESSLIST" output, and it is very cool. Using this table, you can create SQL to dig the processlist, and write stored procedures that spot, log and handle long running queries.

Even better: With the new log formats in 5.1, you can access the logs as CSV engine based SQL tables as well, and join the processlist table against the general query log or other logs. This allows you plenty of easy session tracing and easy profiling.

Even better: With the processlist table and 5.1 new feature "events" you can create new threads, monitor their execution, and in case of problems forcibly terminate them using other events or procedures. Or you can write a stored procedure using DROP …

Frank asked in Can MySQL triggers update another table just that.

Here is how to have a users table with a summary field, and a detail table with a value field. When records are inserted, modified or deleted with changing values in the detail table, the matching summaries in the users table are updated.

Continue reading "Triggers maintaining summaries"

Last week I was with a customer and came across a side problem that was of a more general nature. It is the problem of users having a number of things that use up ressources. The things may be mails, recordings, stamps or whatever - for simplicity we assume recordings, and the ressource consumed is size. The system has a quota system in place, which stores a ressource limit per user.

The quota problem is: if a user is over quota, we want to get a number of item ids to delete per user from old to new, until that user is under quota or just one item over quota. This is the quota problem, and I needed a quota query that solves it.

Here is the setup.

Continue reading "The Quota Query and Running Sums by Jan and Kai"

At work, I came across an interesting problem involving graphs for which I found no completely satisfactory solution.

A colleague tried to model applications and their dependencies as a directed graph. When editing dependencies for a given node, he wanted to show only nodes as new possible descendants which are not yet direct descendants of the current node. Additionally, when selecting new parents, nodes that are already direct parents should not be shown. Since he is still on MySQL 4.0, subselects could not be used.

Here is the data model and a bit of test data:




Continue reading "A graph problem"