The second format is often much shorter if configuration changes are not that frequent. While this is straight forward to do in a programming language in a single pass once you have the config_date ordered data, I can’t figure out how to do this with SQL and no programming. Celko´s books haven’t enlightened me either.

So do you know how to do this?

Published by

Simon J Mudd

Born in England, I now live in Spain, but spent a few years living in the Netherlands.
I previously worked in banking (financial markets) both in IT and as a broker, but IT has always had a stronger influence. Now working at booking.com as a Senior Database Administrator.
Other interests include photography, and travel.
Simon is married, with two children and lives in Madrid.
View all posts by Simon J Mudd

That’s nice and thanks. Your solution does indeed work perfectly. I’d forgotten about the non-standard way you can use MySQL to use variables to perform the task. Thanks and well done. This is a handy tip to know, and something I’m likely to use often.

Jeremy’s solution is nice but has a big disadvantage. I can’t hide the complexity of the SQL from the users in a VIEW which would be convenient. Also if you try to ignore the intial setting up of the variable then MySQL does NOT complain (which is bad) and the results may not be what you expect. So looking for a clean SQL solution to the problem in spite of Jeremy’s good first offer.

Hehe, I tried that solution first too, and quickly noticed that the result was not correct — suspecting that Simon slipped in the double-instance of “value_1” to catch that particular oversight.

Simon,

You could wrap the set of queries up into a stored procedure pretty easily, but you’re right, it’s not possible to do with a view.

I’m not sure that it’s actually possible to do it at all without user variables with the current schema, given the non-sequential nature of SQL.

However, if you could change your schema (updating your old data with a one-time somewhat inefficient query) with e.g. config_version incrementing for each unique *change* of the configuration variable, you could avoid them. In that case it becomes a simple:

select min(config_date) as config_from, max(config_date) as config_to, config_value from config group by config_value, config_version

Then each time you insert the data you compare to the previous version and increment config_version if config_value differs.

I should point out that there is another solutions that may be more efficient than my 3 way join solution. However, that relies on a SELECT in the FROM list, which cannot be used to create a MySQL view. Hence, I came up with the unwieldy but correct 3-way self join solution shown above. You should be warned that this could be a pretty poor performing query, and you really should have some process in place to extract and store the data for convenient reporting if you need good performance.

Hi Roland, yes, you made it. It also works. I’ve rewritten this so the formatting is a bit cleaner and easier to read. Also changed the table and column names to something a bit shorter to ease readability.

This sample table was quite small. The real tables I'm using have 70,000 "sets of configs", that is 1 of these rows represents a set of config values. Denormalised the table holds 18,000,000 rows. So it's likely that even if I'm only looking for config changes of a single server/config_item this select may be unworkable. I'll have to see. I also need to change this so that the assumption that there are consecutive days of config values is removed as while this should be the case it may not always be so.

Nevertheless, thanks for the solution. It works and points me to how to use it in my real life problem. This is also probably useful for a number of other people doing similar things.

From this you can get things out like:
1. show all changes current value vs previous value
2. show the changes for a particular host/config_variable overtime (the original point of this post)
3. show changes against previous date: variable name, number of hosts with a change
4. check the configuration of one server versus another one.
…

This is very handy for auditing and other purposes.
So there are a lot of “config variables”. Usual usage of this table (something I’m still working on) is to provide for a specific server a list of values over time for a specific value. Most of these values don’t really change.