Generate a random password for an RDS MySQL instance

I needed to generate random master passwords for several Amazon RDS MySQL instances.

The specification is as follows:

The password for the master database user can be any printable ASCII character except "/", """, or "@". Master password constraints differ for each database engine.

MySQL, Amazon Aurora, and MariaDB

  • Must contain 8 to 41 characters.

I came up with this:

head -n 1 < <(fold -w 41 < <(tr -d '/"@' < <(LC_ALL=C tr -dc '[:graph:]' < /dev/urandom)))

If you prefer to use pipes (rather than process substitution) the command would look like this:

cat /dev/urandom | LC_ALL=C tr -dc '[:graph:]' | tr -d '/"@' | fold -w 41 | head -n 1

Notes:

  • take a stream of random bytes
  • remove all chars not in the set specified by [:graph:], ie. get rid of everything that is not a printable ASCII character
  • remove the chars that are explicitly not permitted by the RDS password specification
  • split the stream into lines 41 characters long, ie. the maximum password length
  • stop after the first line

Everything is a Freaking DNS problem 2013-07-10 20:08:58

People often wonder why DBA's used to hate developers, and with DBA's also the System Engineers,
(note that I just expanded devops by adding dba's to the picture..)

So let me tell you a story ..

A couple of weeks ago one of our customers wanted to start experimenting with a new type of CRM. A gamified CRM.
Zurmo ...

So we set this thing up in a dev environment and started playing with it , while at first it looks nice ..
the application actually felt pretty slow.. however given that is a low resource development environment we looked no further.

Yet the next step is that we run into missing features, such as the fact that every contact you create by default is
set to private .. which really isn't productive for a CRM system where you want to be able to follow up on different
customer and share information.

So we tried figuring out what the database changes to do this in bulk would mean, surely it had to be a flag on the contact record .
Wrong, Zurmo uses an ORM for their database connectivity ...so their data model wasn't really trivial.

So we decided to look at the MySQL log file to figure out what db changes happened when updating the record
Yes there's better approaches but this one learned us a lot ..
The procedure I followed was pointing my browser to the page where I wanted to switch the checkbox,
log on to the mysql box, set global logging on . Clicked the checkbox and stopped global logging.

This gave me a log file with all the database actions required to make that one single change.
I had to cross check a number of times ... the file created by this short and small action was.
about 70K

Puzzled you start looking at the queries ...
The query list was full with "SELECT * FROM " stanza's ..
70K whopping K of queries that make your hair turn grey ...

I figured I'd file a bug .. but I couldn't find no bugtracker for Zurmo, only a forum (and forums are the most broken form of communication imvho) , yet the developers responded on Twitter.

The feedback wasn't really satisfying so we quickly decided that supporting this application was not something we would like to do..
and abandonned it..

The real question is who needs a Gamified CRM anyhow...

PS. So while finishing up this article on a late evening this week I might not have put in clear enough that the generated logfile was 70Kb .. I fear some people misunderstood that it generated 70.000 queries. Obviously a huge difference. But still the log file shouldn't have been bigger han 1Kb There should have been 2-3 queries max (https://github.com/KrisBuytaert/snippets/tree/master/zurmo)

But imvho if the size of the queries you are generating is bigger than the page you are generating you are most often doing it wrong.

Updating every nth row in MySQL table

I wanted to set a field on a MySQL table to one of 4 values for testing purposes. Let's say I want to set the "pet" field to one of {cat,dog,rabbit,hamster}.

First, add a new field to the table:

alter table test add column `id` int(10) unsigned unique key autoincrement;

Now insert each of the four values:

update test set pet = 'cat' where MOD(id, 4) = 1;
update test set pet = 'dog' where MOD(id+3, 4) = 1;
update test set pet = 'rabbit' where MOD(id+2, 4) = 1;
update test set pet = 'hamster' where MOD(id+1, 4) = 1;

Finally, drop the additional field:

alter table test drop column `id`;

I'm always interesting hearing better/alternative ways to do this sort of thing.

Gentwerpen Devops Meetup & Conference Season Update

A couple of us have been taking about it a lot already .. we wanted to host a one day #devops event in .be already last year.. then talks about starting a meetup group started again with @wonko_be but it was @fs111 pushing the final button and calling the rest of the .be community to order, we've set a date
and the first session will take place (agenda still needs to be detirmined)

So all you Belgian devops enthousiasts, maark October 11th in your calendar and go register here

We already have 2 other venues (Gent, Boom) lined up .. but let's get this first one started :)

Next to that here's an update for the rest of my upcoming Conference Season :

  • Later this month I`ll be heading to San Francisco for a talk at PuppetConf 2012. I'll probably be around in the valley a bit earlier so if you anyone wants to meet up I`m open for suggestions.
    (Yes I asked Nick Stielau of Pantheon to host a #monitoringsucks / #devops meetup about Sensu but I should have predicted it was about to clash with the PuppetConf speakers dinner :((
  • I was thinking to swing by the MySQL- Connect conference but given the pricetag I don't think I`ll bother ... I am however thinking about crashing the hallway track , or tricking the Foreman Meetup to be colocated with a MySQL event again just like at Fosdem earlier this year
  • I will be attending the Jenkins User conference in San Francisco however before flying back to Europe
  • If you haven't noticed yet , Devopsdays is going to be in Rome this year on october 5 and 6. Registration
    is still open !
  • During the last weekend of october it's time for t-dose.org again. No news on the program yet.
  • And one week later I`ll be heading to Barcelona to speak at LinuxCon Europe I`m really looking forward to that last one again as it looks like the good old LinuxKongresses in Germany .. deep technical topics !

Devops in Munich

Devopsdays Mountainview sold out in a short 3 hours .. but there's other events that will breath devops this summer.
DrupalCon in Munich will be one of them ..

Some of you might have noticed that I`m cochairing the devops track for DrupalCon Munich,
The CFP is open till the 11th of this month and we are still actively looking for speakers.

We're trying to bridge the gap between drupal developers and the people that put their code to production, at scale.
But also enhancing the knowledge of infrastructure components Drupal developers depend on.

We're looking for talks both on culture (both success stories and failure) , automation,
specifically looking for people talking about drupal deployments , eg using tools like Capistrano, Chef, Puppet,
We want to hear where Continuous Integration fits in your deployment , do you do Continuous Delivery of a drupal environment.
And how do you test ... yes we like to hear a lot about testing , performance tests, security tests, application tests and so on.
... Or have you solved the content vs code vs config deployment problem yet ?

How are you measuring and monitoring these deployments and adding metrics to them so you can get good visibility on both
system and user actions of your platform. Have you build fancy dashboards showing your whole organisation the current state of your deployment ?

We're also looking for people talking about introducing different data backends, nosql, scaling different search backends , building your own cdn using smart filesystem setups.
Or making smart use of existing backends, such as tuning and scaling MySQL, memcached and others.

So lets make it clear to the community that drupal people do care about their code after they committed it in source control !

Please submit your talks here

Using mock to build 32-bit shared libraries on 64-bit platform

Most of the servers I manage are 64-bit. I have one linode box that is 32-bit. I chose 32-bit because it has better memory usage than 64-bit, which is possibly important with a 512MB instance. This was probably a mistake as the management overhead involved with maintaining a 32-bit infrastructure for just one 32-bit machine is silly. No matter – we are where we are…!

I use the fnv_64 user-defined function from maatkit with MySQL. So, I need to build a 32-bit version for use on the 32-bit server.

Here's how to use mock to create a 32-bit build environment (in this case, for CentOS 5) on a 64-bit machine (the host is actually a Fedora 15 server).

The basic strategy as as follows:

  1. Initialise the environment
  2. Install any dependencies
  3. Copy in the code you want to build
  4. chroot into the build shell
  5. Build the code
  6. Exit the build shell
  7. Copy out the results of the build process

Here's what that looks like with mock:

mock -r epel-5-i386 --init
mock -r epel-5-i386 --install mysql-devel
mock -r epel-5-i386 --copyin fnv_udf.cc /fnv_udf.cc
mock -r epel-5-i386 shell
gcc -fPIC -Wall -I/usr/include/mysql -shared -o fnv_udf.so fnv_udf.cc
exit
mock -r epel-5-i386 --copyout /fnv_udf.so .

Error 400 on SERVER: Exported resource Sshkey[foo] cannot override local resource on node bar.example.com

I'm sure we've all seen this message from time to time when using puppet with exported resources:

Error 400 on SERVER: Exported resource Sshkey[foo] cannot override local resource on node bar.example.com

It's actually pretty easy to fix. Simply delete the exported resource for node foo.

Assuming you are using MySQL for your DB, something like this will do the trick:

mysql -e "delete from resources where restype like 'sshkey' and exported=1 and host_id = (select id from hosts where name 'foo')" puppet

At Fosdem

  • on Friday evening , apparently having a confirmed reservation in a resto is not enough to actually be welcome at that restaurant.
  • at DrupalDevdays, only 2 laptops were open during our presentation
  • at DrupalDevdays, almost nobody in the room was already using CI
  • at Fosdem , the parking lot is full before 11:30 on a saturday
  • at Fosdem , much less Macs than last years .
  • at Fosdem , way too much rooms are already at full capacity so you need to have 2-3 backup alternatives ..
  • at Fosdem , people expect me to be in certain rooms, at the same time
  • at Fosdem , even with too much rooms already full one still misses a bunch of interresting talks
  • at Fosdem , one doesn't even realize friends are speaking there too ..
  • at Fosdem , Android is the standard ...
  • at Fosdem , you are confronted with the fact you probably forgot more names of people than you remember ;(
  • at Fosdem , you are surrounded by famous open source people, that aren't even on the schedule
  • at the MySQL Meetup Dinner, Monty brings Salmiakki
  • at Fosdem , you wonder how many other people have survived their 11th edition
  • at Fosdem , you can't get into any devroom on sunday morning
  • at Fosdem , begging on Twitter to get in to a devroom from the other side of the door works (at least for me :))
  • at Fosdem , netbooks are much less popular as opposed to 2-3 years ago ..
  • after fosdem ... you crash ..