Pythian Group

A few days ago, we faced a Duplicate entry problem in the mysqld server on one of our customer’s slave after cold backup. We do regular data sync checks for all of our customer’s as daily reports and check for data discrepancies between master and slave server’s, if any got picked up by our checks to investigate, and there was no issues for the particular server and with the report. When checked the entry details, there is a row with the same data in the table that was mentioned by Slave status Last_error entity.

Quick check on the error log showed that the slave was started after cold back’s and immediately stopped with a duplicate entry error and the mysqld server version is 5.0.77-log Source distribution. From the analysis of the below statements, we found that the slave SQL thread was stopped with the error at a position backwards than the SQL thread initialized after cold backup’s. Read the rest of this entry . . .

Hello Dear Reader,

This weekend was most probably my longest oncall weekend ever. As part of our work at Pythian each team member covers weekends on regular basis. There are quiet weekends and weekends like this one ;)

One of the tasks that I was involved in was 700GB database emergency cloning for an important functional issue troubleshooting. It was 10.2.0.5 (10GR2) 64 Bit database on Linux. I used the following command to clone:

export NLS_DATE_FORMAT='YYYY-MM-DD:HH24:MI:SS'

rman target / nocatalog auxiliary sys/syspwd@TARGET  log=dup_TERGET.log

run {
set until time "to_date('10-JAN-2010 23:05:25', 'DD-MON-YYYY HH24:MI:SS')";
allocate auxiliary  channel c1 type disk;
allocate auxiliary  channel c2 type disk;
duplicate target database to TARGET;
}

Read the rest of this entry . . .

After I moved back to Europe and Malta in order to set up our operations here, I was approached by a old friend of mine who wanted to know how to add a UNIQUE constraint and remove duplicates on a table, while keeping the newest records. He had been trying with ALTER TABLE but ran into problems as the older values were taken.

So, to help him out, I first solved it based on his original idea, and then figured I would post a small note about the solution here.

So, let’s say we have the following structure . . .

sql01 blogs> SHOW CREATE TABLE post1164\G
*************************** 1. row ***************************
       Table: post1164
Create Table: CREATE TABLE `post1164` (
  `a` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `b` varchar(5) DEFAULT NULL,
  `c` varchar(5) DEFAULT NULL,
  PRIMARY KEY (`a`)
) ENGINE=MyISAM AUTO_INCREMENT=7 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

. . . with a small amount of data in it:

sql01 blogs> SELECT * FROM post1164;
+---+------+------+
| a | b    | c    |
+---+------+------+
| 1 | a    | a1   |
| 2 | a    | a2   |
| 3 | b    | b1   |
| 4 | c    | c2   |
| 5 | b    | b2   |
| 6 | c    | c1   |
+---+------+------+
6 rows in set (0.00 sec)

Now, if I were to use his original SQL, I would get a result similar to this:

Read the rest of this entry . . .