Unless you have been hiding under a rock, you have one way or the other heard about the king of pop passing away. This is in my opinion a great loss to the world, as this man was like a god to me when I was a little boy.
Besides it being really sad that he died, it is also a good test for the systems us techies take care of: the web has seen some of it’s biggest spikes in traffic on june 25th.
That is not over yet though. Next tuesday there will be a memorial service in LA, which will also be live broadcasted on the web. For any system administrator and/or DBA responsible for a site that is news or social media related, this will be yet another good real-life load test.
Rest assured that many people will find their favorite news site or social media application to get the latest on this event.
Now might be a good time to make sure you are ready for a spike like that, and if you were planning a day off for tuesday, you might want to inform your replacement for the day about your phone number. Then again: if your design is dodgy, you are in trouble anyway.
Tag: mysql
Good Practice / Bad Practice: Off-site Backups
In today’s gp/bp an open door will be kicked in: take your backups offsite!
I was actually tempted to create a poll to see how many of you do not have proper backups, and how many of you do not take those backups offsite. It is a simple piece of advice and relatively simple to set up. Offsite in this case would ideally be physically offsite: to a different server in a different building in a different location. A start however is to take them to a different server. And don’t make the mistake of thinking a different VPS on the same physical server is good enough. True, that will protect you from operating system failure of the guest, but it will likely not protect you from hardware failure, or operating system failure on the host OS.
Also, take good care of how you are getting your backups offsite. A normal FTP connection might do the job, but it is hardly secure. Ideally, use SFTP or rsync over ssh to stream your backups offsite.
Some people still take their backups offsite by physically moving a cd, dvd or tape from one location to another. It’s a good start, but in this age of cheap broadband, you might want to think about doing this online. A cron-job is much less likely to not run than it is for you to forget to move that tape.
In our good practice / bad practice series, we will provide you with byte/bite sized pieces of advice on what we consider good (or bad) practice in MySQL-land. The topics can be just about anything, so expect random things to come up. Also, the level of advancedness greatly varies. A topic might be a no-brainer for some, a reminder for others and a revelation for a third person. We strive to tender to all of you!
MySQL projects on launchpad, sourceforge, google code and forge.mysql.com
While doing some work for the MySQL MMM project, I got distracted and browsed around for a bit. I started searching for MySQL on google code, and then expanded that search into launchpad, sourceforge and of course forge.mysql.com.
I found that there are literally thousands of FOSS MySQL projects on these sites. No surprise really, but still not something we stop to think about every day.
I thought I would share that with you here so you can go and have a look for yourself to see if you see anything that might be useful to you. Of course if you do, please blog about it so we can all benefit from it.
DISCLAIMER: many of these projects are in development, it depends on the project really. Before you go off and use a new tool you have never heard of in production, make sure to test it properly beforehand.
Finding out some tool doesn’t work as advertised on a production system is pretty nasty.
Fab tool: watch
A post by Ronald reminded me I wanted to write about watch. The Linux watch command is a fab tool, it allows you to run a program at a set interval, such as every 5 seconds. Handy for keeping an eye on stuff!
Update 2009-06-30: yesterday I wrote “… watch does not re-load and run the program, but goes in and resets the program counter.” (and was un-recommending it for use on production systems for that reason). I was blindly quoting an otherwise trusted source, and it was wholly wrong. Numerous people had a peek at the source code (all the way back to 1997) and found the popen(). So, I stand corrected and my apologies for not doing extra homework in the first place before posting! (I’m leaving the comments as they are)
From Daily WTF: Death by Delete
The Daily WTF collects excellent tales from the real world. These days, the dismal dramatic sagas are often (at least in part) about mistakes involving databases; no surprise there, they’re so prolific…
Anyway, if you can learn from other people’s mistakes, that’s cheap and efficient education! I thought I’d share today’s edition with you: it’s called Death by Delete. Read and enjoy^H^H^H^H^Hlearn.
OSDC 2009 – call for papers reminder
The call for papers for OSDC 2009 is open until 30 June 2009; yes that’s only a few more days. Submit your abstract and do a talk at this fab conference!
This is a grassroots style conference designed by developers for developers. It covers Perl, Python, Ruby/Rails, PHP, Java/Grails and Open Source operating systems as well as some business aspects. If you’d like to cover something else as well that is Open Source themed, please feel free.
The Call for Papers can be found at: http://2009.osdc.com.au/call-for-papers
The important dates are:
- Call for Papers Closes 30 June, 2009
- Proposal acceptance 20 July, 2009
- Accepted paper submissions 14 September, 2009
- OSDC 2009 Main Conference! 25th to 27th November, 2009
OSDC 2009 will be held at the Bardon Conference Center in Brisbane this year. This is a fantastic venue a short drive from the Brisbane CBD, totally surrounded by lush greenery.
Just another day at the office…
Life as a Location Independent Professional can be hard at times, and very good at others!
Replication fail with “There is no ‘username’@’host’ registered”
This post doesn’t contain a tip, I’m asking the q and I don’t know the answer yet. Could just be a logic error on my part, in which case it was just a personal mystery and I thank you for helping!
MySQL error 1449 “There is no ‘username’@'host’ registered”
You can get a replication fail with this error if you have triggers, views or stored routines that run as their creator (which is usual) but the slave does not have that user. So far so good.
We just encountered a replication fail on multiple slaves (i.e. not a single isolated mishap) with this error on an UPDATE statement accessing a single base table (i.e. no view), and no triggers defined or routines called in the query. Indeed the slave did not have the reported user (intentionally), but that shouldn’t have mattered. Creating the user allowed replication to continue successfully – that’s no surprise either.
The question is, what condition actually caused this error?
For ref, the original error message; obfucated but structurally unchanged:
[ERROR] Slave: Error ‘There is no ’someuser’@'%’ registered’ on query. Default database: ’somedb’. Query: ‘UPDATE `t1` SET `name` = ’some text string’, `description` = ‘another text string’, `date_taken` = ”, `place_taken` = ”, `modified` = ‘2009-06-24 12:06:16′, `updated_by` = 1, `modified_on` = 1245773176 WHERE `t1`.`id` = 361′, Error_code: 1449
UPDATE (2009-06-26) ok it was an Arjen fail, on a recheck of all the possible situations where permissions are checked (views, stored procs, triggers), I found that there’s 1 trigger in the entire system and it happens to be on this table. So, no bug and my mistake. Issue resolved! Thanks to all those helping!
Book: Pro Linux System Administration
Peter Lieverdink (also known as cafuego on IRC/identi.ca, engineer on OurDelta builds and for Open Query) has co-authored a book that’s available since Monday. The title is Pro Linux System Administration published by Apress.
These days some people don’t want to bother with system administration, and either hire or outsource. Others want to find out more and do things themselves (home and small office use), and that’s the intended audience for this book.
Good Practice / Bad Practice: CREATE TABLE and the Storage Engine
When you write your create table statements, always make sure that you make them non-ambiguous. That way even though other servers might have different configurations, you make sure your table will be created in the same way.
Imagine for instance you are developing an application on a development server, nicely storing all the scripts you need to create the same database on your production server. If the same script creates a table differently on both servers, that might cause you a lot of headache later on. At Open Query, we strive to minimise (or preferrably eliminate) headaches.
One of the parts of the create table statement that has the largest impact is the storage engine specification. When you omit the storage engine from the create table statement, your table is automatically created with the default storage engine type configured for the server. Since the storage engine is a very important choice when designing your tables, you want to make sure that it is always the correct type.
Here’s an example: instead of writing CREATE TABLE city (city_id int, city_name varchar(100)) you should write: CREATE TABLE city (city_id int, city_name varchar(100)) ENGINE=InnoDB
It is a simple adjustment, but it will save you from possible problems if you just make a habit out of specifying the storage engine.
But wait, there one more thing! It’s also very important you have sql_mode=NO_ENGINE_SUBSTITUTION in your my.cnf, otherwise your table may still silently become a MyISAM table if your desired engine (for any reason) is disabled in the binary, configuration, or at runtime. With this setting error, such a situation will cause an error – so you know for sure.
In our good practice / bad practice series, we will provide you with byte/bite sized pieces of advice on what we consider good (or bad) practice in MySQL-land. The topics can be just about anything, so expect random things to come up. Also, the level of advancedness greatly varies. A topic might be a no-brainer for some, a reminder for others and a revelation for a third person. We strive to tender to all of you!