tag:blogger.com,1999:blog-59869565959086248822024-03-13T23:08:14.604-07:00Glynn's Thoughts on DatabasesGlynnhttp://www.blogger.com/profile/13474827028438980648noreply@blogger.comBlogger4125tag:blogger.com,1999:blog-5986956595908624882.post-82933859001076152262010-01-11T11:17:00.000-08:002010-01-11T13:38:57.348-08:00Finding Updateable ColumnsA customer says: "I am building a form that presents columns in a table as fields in the form. I want to use the end user's database account to discover which columns are updateable and present the corresponding fields appropriately to the end user: I will gray-out the fields that are not updateable. How do I discover the updateable columns in a table?"<br /><br />This is a straight-forward set of queries on the data dictionary, which is presented in the information_schema database. The only thing to note is that a user's access to table columns is determined at 4 levels: server level, database level, table level, and column level. These privileges are reported in the information_schema tables USER_PRIVILEGES, SCHEMA_PRIVILEGES, TABLE_PRIVILEGES, and COLUMN_PRIVILEGES. The access I have is the union of all the privileges I have been granted. So, if I have been granted access to all tables in the world database with, say:<br /><br /><span style="font-family:courier new;"> <span style="color: rgb(0, 0, 153);">GRANT UPDATE ON world.* TO 'user';</span></span><br /><br />, and also to the world.city table with:<br /><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > GRANT UPDATE ON world.city TO 'user';</span><br /><br />, the second GRANT doesn't really matter at all, as the first GRANT already gives me UPDATE access to all the tables in the world database, regardless of access given at the finer level of just one table. So, to find if I have access to a given column, I need to look in up to 4 data dictionary tables, but once I find a suitable privilege, I need look no further to be sure I have that access.<br /><br />If I'm working interactively, I can always find out the total privileges I have by executing the command SHOW GRANTS, but it's a different matter to write a program that responds to this information dynamically.<br /><br />Following are a couple of stored functions to do the job. Note that both functions use SECURITY INVOKER, as we are interested in the access allowed to the invoker of the function, not the definer. Note also that both functions attempt to avoid redundant work: once a result is known, a return is made without further database queries. (And by the way, don't forget to grant execute on these functions to the users you want to have access to them.)<br /><br />I. Function 'updateable'<br />Takes as input database, table, and column names, and returns a boolean result to tell the user whether the column in question is updateable.<br /><br />II. Function 'updatelist'<br />Takes as input a database and table name, and a flag. If the flag is TRUE, returns the columns in the table that are updateable for this user; if FALSE, returns the columns that are not updateable for this user. In both cases, the return is a string containing a comma-separated list of column names.<br /><br />I. Function 'updateable' definition:<br /><pre><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" >DROP FUNCTION IF EXISTS Updateable;</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" >DELIMITER ;;</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" >CREATE FUNCTION Updateable(db VARCHAR(64),</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > tab VARCHAR(64),</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > col VARCHAR(64)) RETURNS BOOL</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > DETERMINISTIC</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > SQL SECURITY INVOKER</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" >BEGIN</span><br /><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > # Given a database, table, and column in parameters (db, tab, col),</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > # return TRUE if the function invoker can update that column,</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > # FALSE otherwise.</span><br /><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > DECLARE userId VARCHAR(81);</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > DECLARE rowCount INT;</span><br /><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > # Put userId into form 'user'@'host' (with quotes) for matches</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > # with information_schema GRANTEE column values.</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > SET userId = CONCAT("'",</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > REPLACE(CURRENT_USER(), "@", "'@'"),</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > "'");</span><br /><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > # Look for UPDATE privilege server-wide.</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > SELECT COUNT(*)</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > INTO rowCount</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > FROM information_schema.USER_PRIVILEGES</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > WHERE GRANTEE = userId</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > AND PRIVILEGE_TYPE = 'UPDATE';</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > IF rowCount > 0 THEN RETURN TRUE; END IF;</span><br /><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > # Look for database-level privilege.</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > SELECT COUNT(*)</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > INTO rowCount</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > FROM information_schema.SCHEMA_PRIVILEGES</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > WHERE GRANTEE = userId</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > AND TABLE_SCHEMA = db</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > AND PRIVILEGE_TYPE = 'UPDATE';</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > IF rowCount > 0 THEN RETURN TRUE; END IF;</span><br /><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > # Look for table-level privilege.</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > SELECT COUNT(*)</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > INTO rowCount</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > FROM information_schema.TABLE_PRIVILEGES</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > WHERE GRANTEE = userId</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > AND TABLE_SCHEMA = db</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > AND TABLE_NAME = tab</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > AND PRIVILEGE_TYPE = 'UPDATE';</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > IF rowCount > 0 THEN RETURN TRUE; END IF;</span><br /><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > # Look for column-level privilege.</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > SELECT COUNT(*)</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > INTO rowCount</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > FROM information_schema.COLUMN_PRIVILEGES</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > WHERE GRANTEE = userId</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > AND TABLE_SCHEMA = db</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > AND TABLE_NAME = tab</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > AND COLUMN_NAME = col</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > AND PRIVILEGE_TYPE = 'UPDATE';</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > IF rowCount > 0 THEN RETURN TRUE; END IF;</span><br /><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > # No UPDATE privilege found: return FALSE.</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > RETURN FALSE;</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" >END;;</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" >DELIMITER ;</span><br /></pre><br />II. Function 'updatelist' definition:<br /><pre><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" >DROP FUNCTION IF EXISTS UpdateList;</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" >DELIMITER ;;</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" >CREATE FUNCTION UpdateList(db VARCHAR(64),</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > tab VARCHAR(64),</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > upd BOOL) RETURNS MEDIUMTEXT</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > DETERMINISTIC</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > SQL SECURITY INVOKER</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" >BEGIN</span><br /><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > # Given database and table name in parameters (db, tab),</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > # and an update flag in parameter (upd):</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > # if upd is TRUE, return a list of the names of columns</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > # that the invoker can update;</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > # if upd is FALSE, return a list of the names of columns</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > # that the invoker cannot update.</span><br /><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > DECLARE userId VARCHAR(81);</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > DECLARE rowCount INT;</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > DECLARE allColumnsUpdateable BOOL DEFAULT FALSE;</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > DECLARE result MEDIUMTEXT DEFAULT '';</span><br /><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > # Put userId into form 'user'@'host' (with quotes) for matches</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > # with information_schema GRANTEE column values.</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > SET userId = CONCAT("'",</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > REPLACE(CURRENT_USER(), "@", "'@'"),</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > "'");</span><br /><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > # Big IF-THEN structure to find if all columns are updateable.</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > # Look for UPDATE privilege server-wide.</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > SELECT COUNT(*)</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > INTO rowCount</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > FROM information_schema.USER_PRIVILEGES</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > WHERE GRANTEE = userId</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > AND PRIVILEGE_TYPE = 'UPDATE';</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > IF rowCount > 0 THEN</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > SET allColumnsUpdateable = TRUE;</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > ELSE</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > # Look for database-level privilege.</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > SELECT COUNT(*)</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > INTO rowCount</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > FROM information_schema.SCHEMA_PRIVILEGES</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > WHERE GRANTEE = userId</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > AND TABLE_SCHEMA = db</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > AND PRIVILEGE_TYPE = 'UPDATE';</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > IF rowCount > 0 THEN</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > SET allColumnsUpdateable = TRUE;</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > ELSE</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > # Look for table-level privilege.</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > SELECT COUNT(*)</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > INTO rowCount</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > FROM information_schema.TABLE_PRIVILEGES</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > WHERE GRANTEE = userId</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > AND TABLE_SCHEMA = db</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > AND TABLE_NAME = tab</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > AND PRIVILEGE_TYPE = 'UPDATE';</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > IF rowCount > 0 THEN</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > SET allColumnsUpdateable = TRUE;</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > END IF;</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > END IF;</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > END IF;</span><br /><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > IF upd THEN</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > # Get updateable column list.</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > IF allColumnsUpdateable THEN</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > # As determined above, report all columns.</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > SELECT GROUP_CONCAT(COLUMN_NAME)</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > INTO result</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > FROM information_schema.COLUMNS</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > WHERE TABLE_SCHEMA = db</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > AND TABLE_NAME = tab;</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > ELSE</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > # Report column-level privilege, or empty string for none.</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > SELECT IFNULL(GROUP_CONCAT(COLUMN_NAME), '')</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > INTO result</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > FROM information_schema.COLUMN_PRIVILEGES</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > WHERE GRANTEE = userId</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > AND TABLE_SCHEMA = db</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > AND TABLE_NAME = tab</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > AND PRIVILEGE_TYPE = 'UPDATE';</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > END IF;</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > ELSE</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > # Get non-updateable column list.</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > IF allColumnsUpdateable THEN</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > SET result = '';</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > ELSE</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > SELECT IFNULL(GROUP_CONCAT(COLUMN_NAME), '')</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > INTO result</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > FROM information_schema.COLUMNS</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > WHERE TABLE_SCHEMA = db</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > AND TABLE_NAME = tab</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > AND COLUMN_NAME NOT IN (</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > SELECT COLUMN_NAME</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > FROM information_schema.COLUMN_PRIVILEGES</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > WHERE GRANTEE = userId</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > AND TABLE_SCHEMA = db</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > AND TABLE_NAME = tab</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > AND PRIVILEGE_TYPE = 'UPDATE');</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > END IF;</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > END IF;</span><br /><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > RETURN result;</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" >END;;</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" >DELIMITER ;</span><br /></pre><br />You could write similar functions to discover which columns are insertable or selectable for a user.<br /><br />Enjoy!Glynnhttp://www.blogger.com/profile/13474827028438980648noreply@blogger.com1tag:blogger.com,1999:blog-5986956595908624882.post-791222288180513272009-10-04T10:24:00.000-07:002009-10-04T10:59:55.180-07:00Rolling Time-based PartitionsFairly often I hear customers say that they plan a table that accumulates millions of rows per day, and they want to keep around, say, the last 30 days worth of data. (For the sake of examples, I'm going to make it the last 3 days.) So this is a kind of round-robin table, with rolling addition of new data and removal of the oldest data.<br /><br />With a high volume of data, this sounds like a table partitioned on day boundaries (in MySQL 5.1). See Sarah's blog and her links for a quick ramp-up on time-based table partitioning (http://everythingmysql.ning.com/profiles/blogs/partitioning-by-dates-the). One great benefit of table partitioning is that you can drop a partition to lose millions of rows in one quick statement, much faster than deleting millions of rows. Sort of like a partial TRUNCATE TABLE.<br /><br />First create the table with 4 partitions, and then, once a day, drop the oldest partition and add another partition to store the next day's rows. (The table will really have exactly 3 days worth data at the moment of this transformation and will accumulate one more day's worth until the next such transformation.)<br /><pre style="color: rgb(0, 0, 153);"><span style="color: rgb(0, 0, 153);font-family:courier new;" >CREATE DATABASE IF NOT EXISTS demotimeparts;</span><span style="color: rgb(0, 0, 153);font-family:courier new;" ><br />USE demotimeparts;</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" >DROP TABLE IF EXISTS pagehits;</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" >CREATE TABLE pagehits (</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > urlviewed VARCHAR(255),</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > whodone VARCHAR(40) DEFAULT NULL,</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > whendone DATETIME NOT NULL DEFAULT '0001-01-01 00:00:00',</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > PRIMARY KEY (id, whendone)</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" >) ENGINE=MyISAM DEFAULT CHARSET=latin1</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" >PARTITION BY RANGE (to_days(whendone))</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" >(PARTITION p20090930 VALUES LESS THAN (TO_DAYS('2009-09-30')),</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > PARTITION p20091001 VALUES LESS THAN (TO_DAYS('2009-10-01')),</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > PARTITION p20091002 VALUES LESS THAN (TO_DAYS('2009-10-02')),</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > PARTITION p20091003 VALUES LESS THAN (TO_DAYS('2009-10-03')));</span><br /></pre> A few notes about this table:<br /><ol><li>The date boundary of a partition is in the partition's name. We'll use this. (It's also convenient for metadata reports.)</li><li>The column whendone is included in the primary key because of the rule that your partitioning column must participate in every unique index.</li><li>For this time-based table to benefit from partition pruning, in which the optimizer eliminates some of the partitions from query execution, your partitioned column must be of type DATE or DATETIME, not TIMESTAMP.</li><li>The default value on the column whendone is to accommodate a SQL_MODE including NO_ZERO_DATE, NO_ZERO_IN_DATE.</li></ol>So far, so good: The table is set up to have 4 partitions, one for each of 4 consecutive days. Now, how to accomplish the "rolling" part? Here's one way. The procedure below takes a DATETIME argument and "rolls" the table to accept rows up to the limit of that date, not inclusive. It uses prepared statements to drop the oldest partition in the table, and add a new partition using the DATE limit you've given.<br /><pre><span style="color: rgb(0, 0, 153);font-family:courier new;" >USE demotimeparts;</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" >DROP PROCEDURE IF EXISTS RotateTimePartition;</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" >DELIMITER ;;</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" >CREATE PROCEDURE RotateTimePartition (newPartValue DATETIME)</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" >BEGIN</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > -- Setup</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > DECLARE keepStmt VARCHAR(2000) DEFAULT @stmt;</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > DECLARE partitionToDrop VARCHAR(64);</span><br /><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > -- Find and drop the first partition in the table.</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > SELECT partition_name</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > INTO partitionToDrop</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > FROM INFORMATION_SCHEMA.PARTITIONS</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > WHERE table_schema='demotimeparts'</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > AND table_name='pagehits'</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > AND partition_ordinal_position=1;</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > SET @stmt = CONCAT('ALTER TABLE pagehits DROP PARTITION ', </span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > partitionToDrop);</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > PREPARE pStmt FROM @stmt;</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > EXECUTE pStmt;</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > DEALLOCATE PREPARE pStmt;</span><br /><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > -- Add a new partition using the input date for a value limit.</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > SET @stmt = CONCAT('ALTER TABLE pagehits ADD PARTITION (PARTITION p',</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > DATE_FORMAT(newPartValue, '%Y%m%d'),</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > ' VALUES LESS THAN (TO_DAYS(\'', </span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > DATE_FORMAT(newPartValue, '%Y-%m-%d'),</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > '\')))');</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > PREPARE pStmt FROM @stmt;</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > EXECUTE pStmt;</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > DEALLOCATE PREPARE pStmt;</span><br /><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > -- Cleanup</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > SET @stmt = keepStmt;</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" >END;;</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" >DELIMITER ;</span><br /></pre>So, before calling RotateTimePartition, the pagehits table definition includes:<br /><pre><span style="color: rgb(0, 0, 153);font-family:courier new;" >/*!50100 PARTITION BY RANGE (to_days(whendone))</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" >(PARTITION p20090930 VALUES LESS THAN (734045) ENGINE = MyISAM,</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > PARTITION p20091001 VALUES LESS THAN (734046) ENGINE = MyISAM,</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > PARTITION p20091002 VALUES LESS THAN (734047) ENGINE = MyISAM,</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > PARTITION p20091003 VALUES LESS THAN (734048) ENGINE = MyISAM) */</span><br /></pre>Then, after:<br /><pre><span style="color: rgb(0, 0, 153);font-family:courier new;" >CALL RotateTimePartition('2009-10-04');</span><br /></pre>, the table includes:<br /><pre><span style="color: rgb(0, 0, 153);font-family:courier new;" >/*!50100 PARTITION BY RANGE (to_days(whendone))</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" >(PARTITION p20091001 VALUES LESS THAN (734046) ENGINE = MyISAM,</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > PARTITION p20091002 VALUES LESS THAN (734047) ENGINE = MyISAM,</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > PARTITION p20091003 VALUES LESS THAN (734048) ENGINE = MyISAM,</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > PARTITION p20091004 VALUES LESS THAN (734049) ENGINE = MyISAM) */</span><br /></pre>Notice that the generated partition names let you easily see the date boundaries.<br /><br />Then you can issue:<br /><pre><span style="color: rgb(0, 0, 153);font-family:courier new;" >CALL RotateTimePartition(NOW() + INTERVAL 1 DAY);</span><br /></pre>, to get:<br /><pre><span style="color: rgb(0, 0, 153);font-family:courier new;" >/*!50100 PARTITION BY RANGE (to_days(whendone))</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" >(PARTITION p20091002 VALUES LESS THAN (734047) ENGINE = MyISAM,</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > PARTITION p20091003 VALUES LESS THAN (734048) ENGINE = MyISAM,</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > PARTITION p20091004 VALUES LESS THAN (734049) ENGINE = MyISAM,</span><br /><span style="color: rgb(0, 0, 153);font-family:courier new;" > PARTITION p20091005 VALUES LESS THAN (734050) ENGINE = MyISAM) */</span><br /></pre>There: Lose a partition, add a partition. Now you can easily write an event to call this procedure daily, to automatically maintain your storage for the table.<br /><br />This table design and procedure work just as well for a table with 31 day-sized partitions, 5 week-sized partitions, 25 month-sized partitions, or whatever. The procedure takes a date input, so it doesn't care whether you're using day, week, month, or any other intervals.<br /><br />Enjoy!Glynnhttp://www.blogger.com/profile/13474827028438980648noreply@blogger.com6tag:blogger.com,1999:blog-5986956595908624882.post-80016328940114437222009-09-06T10:34:00.000-07:002009-09-06T10:59:23.433-07:00SQL from SQLThis is a simple technique, to write a report that generates SQL syntax, that you can then turn around and execute. I've heard this called "SQL from SQL" or sometimes "dynamic SQL"--although it doesn't use prepared statements or anything like that.<br /><br />There are a few simple options in SQL*Plus (Oracle's command-line tool) to accomplish this, and a few simple--but different--options in mysql (MySQL's command-line tool).<br /><br />Try this line:<br /><br /><span style="color: rgb(255, 102, 0);">shell> mysql --silent --skip-column-names -e "select concat('describe ', table_name, ';') from information_schema.tables where table_schema = 'world'"</span><br /><br />(Note I'm supplying my username and password to the session from an option file.) This sends the following to standard output:<br /><br /><span style="color: rgb(255, 102, 0);">describe City;</span><br /><span style="color: rgb(255, 102, 0);">describe Country;</span><br /><span style="color: rgb(255, 102, 0);">describe CountryLanguage;</span><br /><br />The trick is to select the data you want from your system, combined with the literal syntax you want to generate. It's a simple matter to execute these commands, by directing this output stream into another mysql session:<br /><span style="color: rgb(255, 102, 0);"><br />shell> mysql --silent --skip-column-names -e "select concat('describe ', table_name, ';') from information_schema.tables where table_schema = 'world'" <span style="color: rgb(153, 51, 0);">| mysql -v -v -v world</span></span><br /><br />(The -v -v -v options give extra verbose output suitable to this particular report.) If you like, save the final result in a file:<br /><span style="color: rgb(255, 102, 0);"><br />shell> mysql --silent --skip-column-names -e "select concat('describe ', table_name, ';') from information_schema.tables where table_schema = 'world'" <span style="color: rgb(153, 51, 0);">| mysql -v -v -v world <span style="color: rgb(0, 102, 0);">> worldtables.txt</span></span></span><br /><br />It should come as no surprise that the meta-SQL statements you write will often use the meta-data in your database: the information_schema tables. You can use this technique to optimize all the base tables in a database, collect checksum reports, and so forth.<br /><br />Enjoy!Glynnhttp://www.blogger.com/profile/13474827028438980648noreply@blogger.com3tag:blogger.com,1999:blog-5986956595908624882.post-56956249813330775862009-09-06T09:19:00.000-07:002009-09-06T09:57:55.002-07:00IntroductionFolks ---<br /><br />I'm inspired by two of my colleagues: Sarah Sproehnle (http://sarahdba.blogspot.com) and George Trujillo (http://mysql-dba-journey.blogspot.com/). Sarah and George are two excellent MySQL instructors, and their blogs clearly work as tools of the trade. I intend to use this space for a similar purpose: to address questions and techniques of general interest that arise in my MySQL classes.<br /><br />Enjoy!<br /><br />--- GlynnGlynnhttp://www.blogger.com/profile/13474827028438980648noreply@blogger.com1