Rhys Campbell | SQL Server Developer & DBA

Deleting sequential duplicates with TSQL

Posted on September 1, 2010, 6:02 pm, by Rhys, under T-SQL.

I was recently given a du-duping task which was much more difficult than I anticipated and taxed my SQL brain to its limits. I thought of using a CTE to do this but all of the examples I could find for deleting records with a CTE wouldn’t have worked in my situation.

Essentially the table had a business key consisting of 3 parts. Each record would also come with two values attached. The current system pumped data into this fairly regularly and we only wanted to keep values that were not sequentially duplicated. By “sequentially duplicated” I mean an insert into a feed table has the same values attached as the previously inserted record for the same business key. For example, assuming a single key, the below table illustrates the input sequence and the desired de-dupe result.

sequence	de-duped
1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1	1
1,1,1,1,1,1,1,2,1,1,1,1,1,1,1,1,1	1,2,1
1,2,3,4,5,4,3,2,1	1,2,3,4,5,4,3,2,1

Now for a SQL example;

^?View Code TSQL

-- Create a test table
CREATE TABLE dbo.TestDupes
(
	Id INTEGER NOT NULL PRIMARY KEY CLUSTERED IDENTITY(1,1),
	KeyPart1 INTEGER NOT NULL,
	KeyPart2 INTEGER NOT NULL,
	KeyPart3 INTEGER NOT NULL,
	value1 FLOAT NOT NULL,
	value2 FLOAT NOT NULL
);
GO
 
-- Insert some test data
INSERT INTO dbo.TestDupes
(
	KeyPart1,
	KeyPart2,
	KeyPart3,
	value1,
	value2
)
SELECT 1, 2, 3, 2.0, 2.5
UNION ALL
SELECT 1, 2, 3, 2.0, 2.5
UNION ALL
SELECT 1, 2, 3, 2.0, 2.5
UNION ALL
SELECT 1, 2, 3, 5.5, 2.5
UNION ALL
SELECT 1, 2, 3, 2.0, 2.5
UNION ALL
SELECT 1, 2, 3, 5.5, 2.5
UNION ALL
SELECT 1, 2, 3, 5.5, 2.5
UNION ALL
SELECT 1, 2, 3, 2.0, 2.5
UNION ALL
SELECT 1, 2, 3, 2.0, 2.5
UNION ALL
SELECT 1, 2, 3, 2.0, 2.5;
GO

The records considered to be duplicates are outlined in red in the image below.

A sequentially de-duped dataset would look like below.

The solution I came up with, after much head-scratching, involved the use of the DENSE_RANK & ROW_NUMBER functions combined with a Common Table Expression. The DENSE_RANK function organises each business key (KeyPart1, KeyPart2, KeyPart3) into groups. The ROW_NUMBER function gives us a sequence number for each record within the group. This is best illustrated with a select statement with another group thrown in for good measure.

^?View Code TSQL

TRUNCATE TABLE dbo.TestDupes;
-- Insert some test data
INSERT INTO dbo.TestDupes
(
	KeyPart1,
	KeyPart2,
	KeyPart3,
	value1,
	value2
)
SELECT 1, 2, 3, 2.0, 2.5
UNION ALL
SELECT 1, 2, 3, 2.0, 2.5
UNION ALL
SELECT 1, 2, 3, 2.0, 2.5
UNION ALL
SELECT 1, 2, 3, 5.5, 2.5
UNION ALL
SELECT 1, 2, 3, 2.0, 2.5
UNION ALL
SELECT 1, 2, 3, 5.5, 2.5
UNION ALL
SELECT 1, 2, 3, 5.5, 2.5
UNION ALL
SELECT 1, 2, 3, 2.0, 2.5
UNION ALL
SELECT 1, 2, 3, 2.0, 2.5
UNION ALL
SELECT 1, 2, 3, 2.0, 2.5
UNION ALL
-- Group 2 
SELECT 2, 2, 3, 2.0, 2.5
UNION ALL
SELECT 2, 2, 3, 2.0, 2.5
UNION ALL
SELECT 2, 2, 3, 2.0, 2.5
UNION ALL
SELECT 2, 2, 3, 5.5, 2.5
UNION ALL
SELECT 2, 2, 3, 2.0, 2.5
UNION ALL
SELECT 2, 2, 3, 5.5, 2.5
UNION ALL
SELECT 2, 2, 3, 5.5, 2.5
UNION ALL
SELECT 2, 2, 3, 2.0, 2.5
UNION ALL
SELECT 2, 2, 3, 2.0, 2.5
UNION ALL
SELECT 2, 2, 3, 2.0, 2.5;
GO
 
WITH Dupe_CTE (
			Id, 
			KeyPart1, 
			KeyPart2, 
			KeyPart3, 
			value1, 
			value2, 
			group_num, 
			group_row_num
		)
AS
(
	SELECT Id,
	       KeyPart1,
	       KeyPart2,
	       KeyPart3,
	       value1,
	       value2,
               DENSE_RANK() OVER(ORDER BY KeyPart1, KeyPart2, KeyPart3),
               ROW_NUMBER() OVER(PARTITION BY KeyPart1,
                                          KeyPart2,
                                          KeyPart3
                                 ORDER BY Id)
        FROM dbo.TestDupes
)
SELECT *
FROM Dupe_CTE;

Here you can see the data is organised into groups and each row, within the group, is numbered according to it’s position.

Using this information about each group we can execute a delete. The delete self-joins to the CTE comparing the values for value1 and value2.

^?View Code TSQL

WITH Dupe_CTE (
			Id, 
			KeyPart1, 
			KeyPart2, 
			KeyPart3, 
			value1, 
			value2, 
			group_num, 
			group_row_num
		)
AS
(
	SELECT Id,
		   KeyPart1,
		   KeyPart2,
		   KeyPart3,
		   value1,
		   value2,
           DENSE_RANK() OVER(ORDER BY KeyPart1, KeyPart2, KeyPart3),
           ROW_NUMBER() OVER(PARTITION BY KeyPart1,
                                          KeyPart2,
                                          KeyPart3
                             ORDER BY Id)
           FROM dbo.TestDupes
)
DELETE fg2
FROM Dupe_CTE fg1
INNER JOIN Dupe_CTE fg2
                ON fg1.Group_Num = fg2.Group_Num
                AND fg1.value1 = fg2.value1
                AND fg1.value2 = fg2.value2
WHERE fg1.group_row_num = fg2.group_row_num - 1;

This will remove any duplicates, keeping the oldest record in each case, giving us a clean dataset.

Can anyone think of a ~~CURSOR~~ better way of doing this?

Tags: duplicates, SQL Server, T-SQL, TSQL
No Comments »

Parse MySQL Slow Logs with mysqlsla

Posted on August 10, 2010, 8:36 pm, by Rhys, under DBA, MySQL.

Here’s a bash script that you can use to parse multiple MySQL Slow Query Log files, in one sweep, into something much more understandable. The script uses the handy utility mysqlsla so make sure this is in your path.

mysqlsla parses, filters, analyzes and sorts MySQL slow, general, binary and microslow patched logs in order to create a customizable report of the queries and their meta-property values. Since these reports are customizable, they can be used for human consumption or be fed into other scripts to further analyze the queries. For example, to profile with mk-query-profiler (a script from Baron Schwartz’s Maatkit) every unique SELECT statement using database foo from a slow log: source

Place all your slow logs into a directory. Change the sl_dir variable to point at this directory. When you execute the script it will create a directory, within your slow logs directory, called reports. This will contain the reports produced by mysqlsla.

^?View Code BASH

#!/bin/bash
 
# Script to process multiple mysql slow logs
# using mysqlsla http://hackmysql.com/mysqlsla
 
# Directory containing slow logs
sl_dir="/home/rhys/Desktop/slow_logs";
 
cd "$sl_dir";
#slow_logs=$(ls "$sl_dir");
 
# Folder for reports
if [ ! -d "$sl_dir"/reports ]; then
                mkdir "$sl_dir"/reports;
fi
 
# process each slow log file
for file in "$sl_dir"/*
do
                echo "Processing file: $file";
                filename=$(basename "$file")
                mysqlsla -lt slow "$file" &gt; "reports/$filename.rpt";
                echo "Finished processing file: $file";
done

The reports produced are much easier to work with than the raw mysql logs so this should be a good time saver when optimising those queries!

Tags: Bash, MySQL
No Comments »

Kill all processes by name with Powershell

Posted on August 10, 2010, 8:10 pm, by Rhys, under Powershell.

A reader commented on a previous post pointing out a deficiency in one of the scripts used to kill processes on remote computers. If more than one instance of the specified process was running on the target computer the script would buckle. This is pretty easy to rectify. The below script will kill all process instances on the target machine.

^?View Code POWERSHELL

# Kill all processes on a remote machine with a specific name
$computer = "localhost";
$processToKill = "notepad.exe";
$process = Get-WmiObject -Class Win32_Process -Filter "Name = '$processToKill'" -ComputerName $computer;
if($process -eq $null)
{              # If null then the process may not be running
                Write-Host -ForegroundColor Red "Couldn't get process $processToKill on $computer";
                sleep(10);
                exit;
}
else
{
                Write-Host "Attempting to Kill $processToKill on $computer";
}
 
# This original part of the script dies if $process is an array of more than one calc.exe process
# Kill the process and get exit status 0 = OK
# $status = $process.InvokeMethod("Terminate", $null);
# switch($status)
# {
#              0 { Write-Host -ForegroundColor Green "Killed $processToKill on $computer"};
#              default { Write-Host -ForegroundColor Red "Error, couldn't kill $processToKill on $computer"};
#};
 
$count = 1;
 
# This will work regardless if $process is an array or not
foreach ($ps in $process)
{
                Write-Host "Kill count = $count";
				Write-Host "Handle = " $ps.Handle;
                $status = $ps.InvokeMethod("Terminate", $null);
                switch($status)
                {
                                0 { Write-Host -ForegroundColor Green "Killed $processToKill on $computer"};
                                default { Write-Host -ForegroundColor Red "Error, couldn't kill $processToKill on $computer"};
                };
                $count++;
}

This will procedure output similar to below.

Attempting to Kill notepad.exe on localhost
Kill count = 1
Handle =  3788
Killed notepad.exe on localhost
Kill count = 2
Handle =  4916
Killed notepad.exe on localhost
Kill count = 3
Handle =  5884
Killed notepad.exe on localhost
Kill count = 4
Handle =  3488
Killed notepad.exe on localhost
Kill count = 5
Handle =  6232
Killed notepad.exe on localhost

A similar thing can be achieved , on the localhost, with a one-liner.

^?View Code POWERSHELL

# Kills all processes called 'calc' on the localhost
ps calc | kill;

With a little bit of Remoting, available in Powershell V2, it would be simple enough to achieve the same functionality in the one-liner to execute this on remote computers.

Tags: kill all processes, Powershell
No Comments »

Tweet-SQL Version 3 released

Posted on August 7, 2010, 8:23 pm, by Rhys, under Tweet-SQL.

Tweet-SQL version 3 has been released! Here’s a quick summary of the new features.

Full 64 bit OS Support. (Previously the installer would fail to created the required registry keys on 64 bit Operating Systems).
Full support for oAuth authentication.
Support for the new Twitter List methods. See sqlserverpedia list with Tweet-SQL for this in action.
Support for geo-tagging of status updates.
Support for the Twitter reweet methods.
Support for the saved search API methods.
Support for the suggested users API methods.
Support for TinyURL, TweetShrink and TwitterCounter.

I’ll be following up with a few cool blog posts showing what you can do with these new features. In the meantime checkout the Tweet-SQL User Documentation for more details and download Tweet-SQL Version 3 now!

Tags: Tweet-SQL
No Comments »

Comparing accented strings with TSQL

Posted on August 2, 2010, 9:38 pm, by Rhys, under T-SQL.

Today a colleague was running into some difficulties matching football team names containing accented characters. For example Olympique Alès and Olympique Ales were not matching when he wanted them to. The issue here is all to do with collations. We can use the Latin1_General_CI_AI collation in our queries to force the comparison to ignore accents (AI stands for Accent Insensitive).

^?View Code TSQL

DECLARE @string1 VARCHAR(30), @string2 VARCHAR(30)
 
SET @string1 = 'Olympique Alès'; -- With accented e
SET @string2 = 'Olympique Ales'; -- Without accented e
 
IF @string1 = @string2
BEGIN
	PRINT 'Strings do not match!'; -- This will not print
END
 
IF @string1 COLLATE Latin1_General_CI_AI = @string2 COLLATE Latin1_General_CI_AI
BEGIN
	PRINT 'Strings match if we use COLLATE Latin1_General_CI_AI!'; -- This will print
END

Here’s how it works with GROUP BY.

^?View Code TSQL

CREATE TABLE dbo.Places
(
	Id INTEGER IDENTITY(1,1) NOT NULL PRIMARY KEY,
	Place VARCHAR(30) NOT NULL
);
 
-- Insert some test data
INSERT INTO dbo.Places 
(
	Place
)
VALUES
(
	'Olympique Alès'
),
(
	'Olympique Ales'
),
(
	'Gazélec Ajaccio'
),
(
	'Gazelec Ajaccio'
);
 
 
SELECT Place, COUNT(*)
FROM dbo.Places
GROUP BY Place;

^?View Code TSQL

SELECT Place COLLATE Latin1_General_CI_AI, COUNT(*)
FROM dbo.Places
GROUP BY Place COLLATE Latin1_General_CI_AI;

You can also use this with JOINS.

^?View Code TSQL

-- No COLLATE
SELECT *
FROM dbo.Places t1
INNER JOIN dbo.Places t2
	ON t1.Place = t2.Place;

^?View Code TSQL

-- With COLLATE
SELECT *
FROM dbo.Places t1
INNER JOIN dbo.Places t2
	ON t1.Place COLLATE Latin1_General_CI_AI = t2.Place COLLATE Latin1_General_CI_AI;

So that’s how you can force strings to match if they contain accented characters. I was using this for a quick data mapping task but be wary of using the COLLATE clause, in any applications or processes, where performance may become an issue.

If you use the COLLATE clause then this will mean the database engine cannot use any index on the referenced column. You could replace the accented characters before inserting them into your database but here’s a solution I prefer using persisted computed columns.

^?View Code TSQL

-- Alter Places table. Note we specify the collation
ALTER TABLE dbo.Places ADD CleanPlaceName AS Place COLLATE Latin1_General_CI_AI PERSISTED;
-- Index this column!
CREATE INDEX idx_CleanPlaceName ON dbo.Places (CleanPlaceName);
 
-- Note accents in CleanPlaceName are preserved
SELECT *
FROM dbo.Places
 
-- No COLLATE needed!
SELECT CleanPlaceName, COUNT(*)
FROM dbo.Places
GROUP BY CleanPlaceName;

^?View Code TSQL

SELECT *
FROM dbo.Places t1
INNER JOIN dbo.Places t2
	ON t1.CleanPlaceName = t2.CleanPlaceName;

This solution removes the need to include the COLLATE clause in your queries and keeps the possibility of using indices open!

Tags: collations
No Comments »

Creating a sqlserverpedia list with Tweet-SQL

Posted on July 24, 2010, 5:24 pm, by Rhys, under Software, Tweet-SQL.

Many moons ago I posted an article illustrating how to befriend twitter users on the sqlserverpedia list with Tweet-SQL. Since Twitter have added various list methods to their API I thought it would be fun to rehash this post to create a list with Tweet-SQL.

First copy the list of users from the sqlserverpedia page.

Paste this into Excel and you should get something looking like below.

Remove the section titles, empty rows and any text after the url so we are just left with a list of Twitter profile pages.

Next we need to extract the Twitter username from the url. This little bit of Excel wizardry should do it.

^?View Code VBA

=MID(A1,SEARCH("http://twitter.com/",A1)+19, LEN(A1) - 19)

This formula may break as the page changes in the future so watch out for this. Review the list and remove any invalid values. At the time of writing there’s one of the list that doesn’t contain a twitter url. Save this as a csv and then import it into a database containing the Tweet-SQL Procedures. I’ve uploaded a copy of the file I produced today here. This file contains 261 Twitter users. I used the below table structure.

^?View Code TSQL

USE [TweetSQLV3]
GO
 
/****** Object:  Table [dbo].[sqlserverpedia]    Script Date: 07/24/2010 16:16:24 ******/
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
SET ANSI_PADDING ON
GO
 
CREATE TABLE [dbo].[sqlserverpedia](
	[url] [varchar](255) NULL,
	[tweep] [varchar](255) NULL
) ON [PRIMARY]
 
GO
 
SET ANSI_PADDING OFF
GO

There’s a few dupes in the below list, because people are listed in multiple sections, so run the below TSQL script to de-duplicate it.

^?View Code TSQL

ALTER TABLE dbo.sqlserverpedia ADD Id INTEGER IDENTITY(1,1) NOT NULL PRIMARY KEY;
GO
 
DELETE t1
FROM dbo.sqlserverpedia AS t1
INNER JOIN dbo.sqlserverpedia AS t2
	ON t1.tweep = t2.tweep
WHERE t1.id &lt; t2.id;
 
-- Additional column we'll use later
ALTER TABLE dbo.sqlserverpedia ADD done BIT DEFAULT 0;

Next we’ll need to create the list. The below TSQL will do this (for SQL 2008). Make a note of the list id as we’ll need this later.

^?View Code TSQL

DECLARE @list_name VARCHAR(30) = 'sqlserverpedia list';
-- Turn on relational resultsets in Tweet-SQL
EXEC dbo.tweet_cfg_resultset_send 1;
-- Create the list 
EXEC dbo.tweet_list_post_lists @list_name, 'public', null;

Next we’ll add the users onto this list. After each record is added to the Twitter list it is flagged as done so you can simply re-run the script if something fails halfway through.

^?View Code TSQL

DECLARE @tweep VARCHAR(30), @list_id INTEGER;
 
-- Set your list id here
SET @list_id = 17542298;
 
-- Turn off resultsets in Tweet-SQL
EXEC dbo.tweet_cfg_resultset_send 0;
 
DECLARE tweeps CURSOR LOCAL FAST_FORWARD FOR SELECT tweep
					 FROM dbo.sqlserverpedia
					 WHERE done IS NULL;
 
-- Open the cursor and get the first result
OPEN tweeps;
FETCH NEXT FROM tweeps INTO @tweep;
 
WHILE(@@FETCH_STATUS = 0)
BEGIN
 
	-- Add the tweep to the list
	EXEC dbo.tweet_list_post_list_members @list_id, @tweep, null;
	-- Flag the current record as done
	UPDATE dbo.sqlserverpedia
	SET done = 1
	WHERE tweep = @tweep;
	-- Wait for a bit so we don't annoy twitter
	WAITFOR DELAY '00:00:05'
	-- Fetch the next row
	FETCH NEXT FROM tweeps INTO @tweep;
 
END
 
-- Clean up
DEALLOCATE tweeps;
-- Turn Tweet-SQL resultsets back on
EXEC dbo.tweet_cfg_resultset_send 1;

See my finished list here and follow me on twitter for more TSQL tomfoolery!

No Comments »

Can’t reopen table: ‘t1′

Posted on July 19, 2010, 10:24 pm, by Rhys, under DBA, MySQL.

I’m quite often jumping between MySQL and SQL Server so remembering the quirks and limitations of each system can be difficult. With MySQL, if you attempt to reference a temporary table more than once in the same query, you will encounter the following error;

Error Code : 1137
Can't reopen table: 't1’

The following provides an example of this…

^?View Code MYSQL

USE test;
 
CREATE TEMPORARY TABLE test
(
                Id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY
);
 
SELECT *
FROM test AS t1
INNER JOIN test AS t2
     ON t1.Id = t2.Id;

It’s not just self-joins that have this issue UNIONS do as well;

^?View Code MYSQL

SELECT *
FROM test AS t1
UNION ALL
SELECT *
FROM test AS t2;

There’s a thread over on Stackoverflow discussing this problem. Here’s a solution I commonly use to get around the problem;

^?View Code MYSQL

USE test;
 
# Create temp table
CREATE TEMPORARY TABLE test
(
                Id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY
);
 
# Insert some test data
INSERT INTO test (Id) VALUES (1),(2),(3),(4),(5);
 
# Clone the table. This will do structure & indices but no data.
CREATE TEMPORARY TABLE test2 LIKE test;
 
# Insert the data into the new table
INSERT INTO test2
SELECT Id
FROM test;
 
# Now our queries will work if we use the tables clone
SELECT *
FROM test AS t1
INNER JOIN test2 AS t2
      ON t1.Id = t2.Id;
 
SELECT *
FROM test AS t1
UNION ALL
SELECT *
FROM test2 AS t2;
 
# Clean up
DROP TEMPORARY TABLE IF EXISTS test;
DROP TEMPORARY TABLE IF EXISTS test2;

No Comments »

Rename MySQL Stored Procedures

Posted on July 14, 2010, 9:47 pm, by Rhys, under DBA, MySQL.

I’ve previously blogged about the limitations of MySQL Alter Procedure Syntax and I came across a thread on the MySQL forums with a possible solution. I thought it might be handy to wrap this up into a stored procedure akin to SQL Server’s sp_rename.

This procedure will allow you to easily rename MySQL Stored Procedures in any database. Please be aware that this does update the MySQL system tables and has only had minimal testing. As with all tips you find on the Internet please use with caution!

^?View Code MYSQL

DELIMITER $$
 
USE `mysql`$$
 
DROP PROCEDURE IF EXISTS `mysp_rename_proc`$$
 
CREATE DEFINER=`root`@`%` PROCEDURE `mysp_rename_proc`(IN p_proc_name VARCHAR(64), IN p_new_name VARCHAR(64), IN p_db VARCHAR(64))
    MODIFIES SQL DATA
    SQL SECURITY INVOKER
    COMMENT 'Use to rename stored procedures.'
BEGIN
    proc: BEGIN
	# A few tests to see if the input is sensible
	IF CHARACTER_LENGTH(IFNULL(p_proc_name, '')) = 0 OR CHARACTER_LENGTH(IFNULL(p_new_name, '')) = 0
		OR CHARACTER_LENGTH(IFNULL(p_db, '')) = 0 THEN
	BEGIN
		SELECT 'Error: One of more of the input parameters is zero in length.' AS Error;
		LEAVE proc; 
	END;
	ELSEIF (SELECT COUNT(*)
		 FROM mysql.proc
		 WHERE `name` = p_proc_name
		 AND `type` = 'PROCEDURE'
		 AND db = p_db) = 0 THEN
	BEGIN
		SELECT 'Error: The procedure specified in p_proc_name does not exist in this database.' AS Error;
		LEAVE proc;
	END;
	ELSEIF (SELECT COUNT(*)
		 FROM mysql.proc
		 WHERE `name` = p_new_name
		 AND `type` = 'PROCEDURE'
		 AND db = p_db) = 1 THEN
	BEGIN
		SELECT 'Error: Unable to rename the procedure specified in p_proc_name as it already exists in this database.' AS Error;
		LEAVE proc;
	END;
	END IF;
 
	# Rename the proc
	UPDATE `mysql`.`proc`
	SET `name` = p_new_name,
	specific_name = p_new_name
	WHERE db = p_db 
	AND `name` = p_proc_name
	AND `type` = 'PROCEDURE';
 
	# Update any associated privileges
	UPDATE `mysql`.`procs_priv`
	SET Routine_name = p_new_name
	WHERE db = p_db 
	AND Routine_name = p_proc_name
	AND Routine_type = 'PROCEDURE';
 
	# Check update rowcount to see if privileges need to be flushed
	IF(SELECT ROW_COUNT()) > 0 THEN
	BEGIN
		FLUSH PRIVILEGES;
	END;
	END IF;
 
    END proc;
    END$$
 
DELIMITER ;

Usage is as follows;

^?View Code MYSQL

CALL mysp_rename_proc('usp_proc', 'usp_new_proc_name', 'database_proc_exists_in');

As the thread poster mentions the Stored Procedure is callable by it’s old name as well as the new one until you reconnect. There doesn’t seem to be any suitable FLUSH command to resolve this.

If you liked this you may like;

MySQL Clone of SP_SpaceUsed

MySQL Clone of SP_MsForEachTable

No Comments »

Purge MySQL Binary Logs

Posted on July 12, 2010, 11:58 am, by Rhys, under DBA, MySQL.

From time-to-time you may need to manually purge binary logs on your MySQL slaves to free up a bit of disk space. We can achieve this by using the PURGE BINARY LOGS command from the MySQL command line client. MySQL advises the following procedure when purging these logs

To safely purge binary log files, follow this procedure:

On each slave server, use SHOW SLAVE STATUS to check which log file it is reading.

Obtain a listing of the binary log files on the master server with SHOW BINARY LOGS.

Determine the earliest log file among all the slaves. This is the target file. If all the slaves are up to date, this is the last log file on the list.

Make a backup of all the log files you are about to delete. (This step is optional, but always advisable.)

Purge all log files up to but not including the target file. source

The below example will purge all binary logs older than the one called backup-master.001271.

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2640654
Server version: 5.1.34-log SUSE MySQL RPM

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> PURGE BINARY LOGS TO 'backup-master.001271'

Provided your slave is running this command is reasonably safe as MySQL will prevent you from purging any log files that are currently being read. If the slave thread is not running then you do have to make sure you are not purging a needed file. If you purge a needed file the slave will break when it is restarted. You can also use the expire_logs_days variable to automatically control when binary logs are purged.

No Comments »

For RANGE partitions each partition must be defined

Posted on July 9, 2010, 1:08 pm, by Rhys, under DBA, MySQL.

If you encounter the following error when trying to create a partitioned table in MySQL

Error Code : 1492
For RANGE partitions each partition must be defined

Assuming you have defined your partitions then you probably have a syntax error. Take the following incorrect example.

^?View Code MYSQL

CREATE TABLE People
(
	PersonId INTEGER NOT NULL AUTO_INCREMENT,
	FirstName VARCHAR(50) NOT NULL,
	LastName VARCHAR(50) NOT NULL,
	DateOfBirth DATE NOT NULL,
	Telephone VARCHAR(30) NULL,
	Email VARCHAR(200) NULL,
	GroupId SMALLINT NOT NULL,
	PRIMARY KEY (PersonId, GroupId)
)
PARTITION BY RANGE (GroupId)
PARTITION p0 VALUES LESS THAN (100),
PARTITION p1 VALUES LESS THAN (200),
PARTITION p2 VALUES LESS THAN (300),
PARTITION p3 VALUES LESS THAN (400),
PARTITION p4 VALUES LESS THAN (500),
PARTITION p5 VALUES LESS THAN (600),
PARTITION p6 VALUES LESS THAN (700);

The above statement is not syntactically correct but the error thrown in this case is not particularly helpful. All that is missing here is a couple of braces around the partition range definitions. The below DDL statement is correct.

^?View Code MYSQL

CREATE TABLE People
(
	PersonId INTEGER NOT NULL AUTO_INCREMENT,
	FirstName VARCHAR(50) NOT NULL,
	LastName VARCHAR(50) NOT NULL,
	DateOfBirth DATE NOT NULL,
	Telephone VARCHAR(30) NULL,
	Email VARCHAR(200) NULL,
	GroupId SMALLINT NOT NULL,
	PRIMARY KEY (PersonId, GroupId)
)
PARTITION BY RANGE (GroupId)
(
	PARTITION p0 VALUES LESS THAN (100),
	PARTITION p1 VALUES LESS THAN (200),
	PARTITION p2 VALUES LESS THAN (300),
	PARTITION p3 VALUES LESS THAN (400),
	PARTITION p4 VALUES LESS THAN (500),
	PARTITION p5 VALUES LESS THAN (600),
	PARTITION p6 VALUES LESS THAN (700)
);

Tags: MySQL, partition, partitioning
No Comments »

Deleting sequential duplicates with TSQL

Parse MySQL Slow Logs with mysqlsla

Kill all processes by name with Powershell

Tweet-SQL Version 3 released

Comparing accented strings with TSQL

Creating a sqlserverpedia list with Tweet-SQL

Can’t reopen table: ‘t1′

Rename MySQL Stored Procedures

Purge MySQL Binary Logs

For RANGE partitions each partition must be defined

Recent Posts

Recent Comments

Meta

Cool SQL Links

Blog Archive