Selecting a running position of the returned row in MSSQL

For some reason this concept is hard to summarize in one line. Basically we’re looking to return how many rows are in the table before the row returned in the result. Sort of like what position the rows is in line. If we have a table like this: Row: 1,2,3 Row: 2,4,5 Row: 3,6,7 And we want to find out the position of row 2, then it’s fairly simple because the primary key will tell us what order it is. However, if you delete row 1,...

Read More

MySQL Limiting Left Joins

Sometimes it’s nice to just use a left join to see if a column exists. That’s what left joins are for. Your base query will still return rows even if there are no rows in the joined table(s). However, what if there is a one to many relationship between the base table and the joined table, that is to say that for any row in the base table there could be any number of rows in the joined table. If you have more than one row in the...

Read More

Testing a replicated backup system

Testing a replicated backup system

If you have a high need for availability for a production application, but don’t have the money to invest in full fault tolerance networking, machinery, and access, you’ll need to have some sort of realtime backup system in place should your primary environment go down. This poses the issue of how do you keep one system on standbye, but be sure that it’s ready to go at all times. The image below shows one possible setup. The...

Read More

Resync a double replicated MySQL Database

Resync a double replicated MySQL Database

Certain scenarios need a master database replicated to multiple slaves instead of just one. If you have a very heavy read application you might benefit from a number of slaves. It also provides a sort of double backup if one slave drifts away from the data it’s supposed to have. Double replication also allows you to test a backup system. I have a separate article dedicated to that. With a double replication you’ll have a setup like...

Read More

Abstract MySQL Selects

Sometimes if you have a rapidly changing database, you have to do maintenance on tables, but you may not want to keep updating your script to account for new tables and/or columns you create. If this is the case, you have to make your code a little more abstract. MySQL has a few commands that help with this. For instance, if you are replicating a set of databases to another server, and want to make sure the replication is up to date, you could...

Read More