Category: Databases

Immutable Database Schema

Immutable Database Schema

I’m adding functionality to a mature web application and need to add a field to a database table.  I started mentally walking the path of the change and what all it touches and the required testing and review that would ensue and it’s mildly daunting.

Then a voice in my head said, “immutability.”

The field I would add is not necessarily a record field – it’s not data, it’s relationship.  Is “relationship” data?

Instead of adding a field and reviewing and refactoring everywhere the model is used, I can simply add a relationship table, create a model and use it when needed.

Slightly more code, way less review.  But I don’t know if it’s a good approach, or if anyone else has tried this, so I’m documenting this online and asking for feedback.

Do you have an opinion about this?

How big is BIGINT?

As I was trying to decide if a bigint column would hold enough values to store historical data in a database, and storage problems aside, this helped me put it into perspective, and decide that, yes, for now, bigint would be big enough for me.

eighteen quintillion, four hundred forty six quadrillion, seven hundred forty four trillion, seventy three billion, seven hundred nine million, five hundred fifty one thousand, six hundred fifteen

Some perspective:
~ 1 trillion pennies (click for details) (Sear’s Tower, Empire State Building & others for scale)

~ 1 Quadrillion pennies (click for details)

~ 1 Quintillion pennies (click for details)

 

BIGINT provides 18.446 quintillion unique values.

SUMs, Joins and COALESCE in MySQL

Recently I ran into a problem with a query. I was trying to add the sums of two columns together, one of the columns happened to be in a table that was LEFT OUTER JOINed and the result was NULL when it should not have been.

This is for a purchase ordering system and is the calculation to get the total of inventory that is in transit, taking into account partial receipts of inventory.

Here is my table structure
sums-and-joins-tables

select SUM( po_item_qty ) - SUM( po_receipt_qty ) as on_order

from po_item

left outer join po_receipt on ( po_receipt_po_item_id = po_item_id )
left outer join po on ( po_item_po_id = po_id )
left outer join product on ( po_item_product_id = product_id )

where po_status = 'in_transit'

This produces a result of ‘NULL’ when I know perfectly good and well that I have in transit purchase orders with po_item records.

It turns out that subtracting the po_receipt_qty when there are no matching po_receipt records (LEFT OUTER JOIN records return null when missing ) turns into, for example, 100 – NULL which always returns NULL.

What I need is for this to turn into 100 – 0, returning 100.

COALESCE to the rescue!

The query that made it work:

select SUM( po_item_qty ) - COALESCE( SUM( po_receipt_qty ) ,0 ) as on_order

from po_item

left outer join po_receipt on ( po_receipt_po_item_id = po_item_id )
left outer join po on ( po_item_po_id = po_id )
left outer join product on ( po_item_product_id = product_id )

where po_status = 'in_transit'

Database and Error Logging in a Well-Structured Web Application

Problem:

You have a well-written web application. It’s throwing exceptions, you’re handling the exceptions.  You’re starting db transactions and committing or rolling them back.  You are logging exceptions and errors to your database for forensics and review.

Two to three years pass, the web application, because it’s well written, modular and de-coupled, has grown and you’ve added new features – perhaps even released several major updates – suddenly you realize that not all logging is getting recorded.  Somehow, through the complexity of exception handling and commits and rollbacks you have gotten to the point where you are accidentally rolling your logging inserts back.  You may have alerted the dev team but there’s nothing recorded for them to review.

I have good news.

There is a solution.  It’s simple, and obvious (perhaps so obvious you’ve already taken this step yourself).

Use a different database connection. Transactions are connection specific.  Rolling back on your main database connection will leave your logging connection untouched.

If you’re going to use a different database connection, you might as well take the time to move your logging to a separate database as a best practice.

You should also employ lazy connecting – don’t connect to your logging db unless you need to log something.

Warning

Make sure you know which db connection to use if you’re using mysql and need the last insert id from an auto-increment logging table id.

Mixing MySQL InnoDB and MyISAM tables with transactions

Just learned this from personal experience.  Hoping to save someone else some pain.

I tried the following searches:

mysql transaction partial rollback
does fetching commit an innodb transaction
innodb rollback not working

The symptoms I was experiencing included inserted records were rolled back, but an update to a table was not. My PHP code threw an exception inside a transaction, and the catch did a rollback, but the update still went through.

The problem was that the table I was updating happened to be MyISAM. MySQL silently ignored the fact that I included a table in my transaction that couldn’t be rolled back. When the rollback occurred, this was also silently ignored.

Moral of the story: use all InnoDB if you’re going to be doing transactions.