Not Really a Blog

February 18, 2010

Alan Wilder live with Depeche Mode at the Royal Albert Hall

Filed under: Music — Tags: , — jesus @ 02:42

Tonight I had the honour to attend a Depeche Mode concert at the Royal Albert Hall in London. It was a special night (not that they usually perform at the Royal Albert Hall, usually The Wembley Arena or the O2) as they were performing for a charity, Teenage Cancer Trust.

So I got there by chance as Luis (many thanks), a friend from Spain who had an extra ticket. he’s spared it with me in exchange of accommodation in London. I wasn’t expecting something very unusual in this concert apart from a couple of changes in the set list. It all when good until they had a few guys with violins for 3 of the songs (something they did back in 1993). But, to my surprise and to the surprise of a few thousand people at the venue, Alan Wilder appeared on stage and played the piano with Martin L. Gore to perform Somebody.

Apologies for the quality of the recording, it’s all I could get.
So, for those of you who might find this boring, Alan Wilder left Depeche Mode in 1995, after a devastating (but glorious) Devotional Tour during which many weird things happened (among them, David Gahan on drugs, etc). Lots of people considered this as a turning point in Depeche Mode’s career as Wilder was probably a musical genius that brought everything together: Martin’s nice lyrics and ballads, Gahan’s voice and front lead, Andy’s face (pun intended) and Wilder’s sounds. It hasn’t been the same since.

But tonight, he was playing on stage, with Depeche Mode. Something that many have wished it came true. Too good to last though, as it seems like a one-only thing, kind of special occasion. People have moved on though. They (Depeche Mode and Alan Wilder) have moved on too. You can tell by their concerts. You can tell by the people who attended tonight (I felt very young among today’s crowd, and I started listening to Depeche Mode in 1989). They don’t attract new people these days, people get moved on by the good old classics from 15 years ago, people rock and dance with them in their concerts, not that much with the new stuff.

Anyway, people who know me know that I moved on as well. I no longer listen to much Depeche Mode these days. For me it’s like the memories of an ex-girlfriend for whom I was in love a long time ago. It brings nice memories, but nostalgic memories as well. And that’s why it needs to remain there, where it’s still sweet, otherwise…

However, tonight’s concert has moved me. To say that I’m excited is an understatement. Here I am, unable to get any sleep and writing this blog entry because I had the chance to watch something unique and something that’s moved me after all these years. I really enjoyed tonight, it’s been like making peace with that ex-girlfriend (yeah, but not what you are thinking, I can see you coming here… :-) )

So, thank you Depeche Mode.

I hope you guys enjoy the video.

As a side note, I found this with unseen footage from 1998, recorded by MTV which has also reminded me of the days I started listening to Depeche Mode. Hope you like it.

February 6, 2010

The effect of temporary tables on MySQL’s replication

Filed under: System Administration — Tags: , — jesus @ 23:05

The other day I needed to set up a new set of MySQL instances at work what would replicate from an existing node. I was setting these up because the master node is running out of disk space and is very slow.

Usually, when you need to restore a database you do it in three parts:

  1. Install the binaries
  2. Load the initial data from the most recent MySQL backup.
  3. Set it replicating from one of the nodes by specifying the binary log file and position from which you want it to replicate (which usually corresponds to the day you took the backup).

Now, because we usually compress the binary logs and because the master didn’t have enough disk space to have all these binary logs uncompressed (such that the new slave could replicate by connecting to the master and talking the MySQL protocol), I needed to transfer them to the new slave and pipe them into MySQL from there. Seems simple, huh?

Everything went fine on point 1 and 2. But then, while piping the contents of the MySQL binary logs into the new databases, it all went wrong. What I used to pipe them was:


for file in master-bin* ; do echo "processing $file" ;    ../mysql/bin/mysqlbinlog "$file" | ../mysql/bin/mysql -u root -ppassword  ; done

Which is how you usually do these things, but this is what I got:


db@slave:~/binlogs$ for i in master-bin* ; do echo "processing $file" ;    ../mysql/bin/mysqlbinlog "$file" | ../mysql/bin/mysql -u root -ppassword  ; done
processing master-bin.1853
processing master-bin.1854
processing master-bin.1855
processing master-bin.1856
processing master-bin.1857
processing master-bin.1858
processing master-bin.1859
processing master-bin.1860
ERROR 1146 at line 10024: Table 'av.a2' doesn't exist
processing master-bin.1861
ERROR 1216 at line 1378: Cannot add or update a child row: a foreign key constraint fails
processing master-bin.1862
ERROR 1216 at line 22825: Cannot add or update a child row: a foreign key constraint fails
processing master-bin.1863

So, table av.a2 does not exist. WTF?

Investigating a bit about this table, it seems there’s a script which executes the following stuff on it everyday:


...

if test $ZZTEST -lt 300000; then
 echo "ERROR: Less than 300k"
 exit 1
fi
cat > sql << EOF
create temporary table a1 (mzi char(16) default null, key mzi(mzi));
create temporary table a2 (mzi char(16) default null, key mzi(mzi));
create temporary table a3 (mzi char(16) default null, key mzi(mzi));
EOF
cat v | grep ^447.........$ | awk '

...

Now, create temporary table, if you read about it on MySQL docs you’ll see that temporary tables are only visible to the current connection and are dropped automatically when that connection finishes. There are a few problems with replication and temporary tables, but this could not possibly be the same problem as these were the binary logs from the master. So, what’s going on here?

The problem here comes from the binary logs being rotated and the way I was inserting them. It just happened that the three SQL statements:


create temporary table a1 (mzi char(16) default null, key mzi(mzi));
create temporary table a2 (mzi char(16) default null, key mzi(mzi));
create temporary table a3 (mzi char(16) default null, key mzi(mzi));

were created at the end of binary log file master-bin.1859 and then there was a SQL statement which made it fail on file master-bin.1860 (inserting data into av.a2) because it was expecting those temporary tables to exist (and they didn’t). This happened  because we are using a for loop in bash to insert the binary logs, so there’s one mysql connection for each binary log file and thus, when file master-bin.1859 finished it automatically made MySQL drop the three temporary tables (that connection was finished) and then on the next connection (file master-bin.1860) these tables were missing.

There are a few ways in which you can work around this.

One approach is to get one big sql file and pipe that into MySQL, something like:


for file in master-bin.1* ; do echo "Using $file" ; ../mysql/bin/mysqlbinlog "$file" >> all.sql; date  ;  done
cat all.sql > ../mysql/bin/mysql -u root -ppassword

Alternatively, doing something like:

(for file in master-bin.1*; do echo “Using $file” 1>&2; ../mysql/bin/mysqlbinlog $file; date 1>&2; done) | ../mysql/bin/mysql -u root -ppassword

If you want to avoid creating one big fat file.

Which should work as in this case it’s only going to be one connection.

But, these ways have an obvious setback, which is that you cannot have a look at what failed (well, sort of, but extremely difficult) if something goes wrong; It’ll fail on one of the files and then will fail with the rest of them.

The better approach, as discussed on High Performance MySQL is to use a log server, that’s it, a MySQL server that would not use any storage but will only be used to replay binary logs, so you won’t have this problem and also, it will let you interact with the server and its diagnostic messages in case something goes awry.

Use temporary tables?

My advice here would be to encourage you not to use CREATE TEMPORARY TABLE because it can break replication in mysterious ways, but that could be too harsh. There are a few workarounds that can be done from an application level that you can read in http://www.xaprb.com/blog/2007/05/11/how-to-eliminate-temporary-tables-in-mysql/ which I think they could be worth thinking about.

Any experience with these problems?

Customized Shocking Blue Green Theme Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 2,886 other followers