2006-11-09

Money Matters

Today's iteration in the series of horror stories is going to be about the transaction management system. Combined with the transaction management system is the pricing system, so I'm going to present these two systems today in a double-whammy of fun.

MySQL Sucks. MySQL, with the MyISAM storage engine does Table Locking On Writes, and has no transactions. This system dates back to the early 3.x days of MySQL where all these new features like transactions and subqueries were nothing but "unnecessary features that databases don't really need" (paraphrasing a '02 era MySQL manual there).

Our transaction management system was the system that represented the fact that a customer had committed to paying for a service rendered. The services were often delivered in real time (we resold data via the web), and a bill would be sent out at the end of the month. In order to record a transaction, a single record was written to the 'transactions'[1] table in a database on the webserver. Each record in this table recorded Who used What and How much they paid for it. The table contained about 7 more columns than it needed to in order to figure out exactly What the transaction was linked to.

I don't recall precisely, but somewhere between $100 and $1000 worth of (gross, not net) transactions were inserted into this table every minute, and at any point it contained 60 days worth of data (the rest was archived off to 'transactions_archive'[2]).

Gentle Reader. If you don't think the above is insane, I don't expect you're the audience for my blogging at the moment, regardless, I think it was nucking futs. We were trusting our 10's of thousands of dollars per hour business to a single MySQL table running on a production web server (now you understand why space on said server was at a premium).

Sure, it was replicated, but MySQL replication breaking meant you had to take down the entire website for 6 hours on a weekend to copy all the data across again so you could restart the replication. When replication broke, that meant that you were running with all-your-eggs-in-one-basket for every hour between when it stopped working and when you could get it running again.

Okay, that's the transaction management system, now lets do pricing.

Each product had a hard cost. This was the cost to us (the data supplier) to get that data. I shall not going into details here, but the data is things like government files on people or companies, or state government data on land titles. This number was in a database.

Each client had what was known as a delivery fee. This is a fee that we charge over and above the hard cost in order to 'deliver' the data to the client.

For example. A Property title might cost $20. Your delivery fee might be $2. You would have to click 'okay, charge me please' after being asked to pay $22.

If the data cost $0.50 or $0, you'd pay $2.50 or $2 respectively. If the data cost $190, you'd pay $192.

The scary thing is, because of the way the technical systems were set up, operations and sales staff simply could not comprehend a pricing schedule that wasn't based on delivery fees, and would not even entertain the concept of perhaps, charging what the service was worth to the customer.

[1] I could not make that name up. Seriously.
[2] Nor this.

3 comments:

Anonymous said...

If you think older MySql is nucking futz, try this on MySql 4.x or 5.x:

create table test (id integer, stuff varchar(20));
insert into test (id, stuff) values (1, 'stuff1');
insert into test (id, stuff) values (2, 'stuff2');
insert into test (id, stuff) values (3, 'stuff3');

Do a query that should work, and returns a result:

select * from test where id in (1,2,3);

-- returns all rows

Now do a query that should NOT work, but still returns a result:

select * from test where id in ('1,2,3');

MySql 4.x AND 5.x return the row with ID == 1.

4.x returns no error in the interactive interpreter. 5.x suggests there might be something awry in the interactive interpreter (but its lib api might not. I can't be bothered to check, I just won't use MySql).

Other databases do the right thing and raise some sort of a cast error. Postgres for example raises an atoi error and returns NO results (which is sensible, and more to the point, SAFE).

Cory said...

Maybe they should have called that table 'teh_moneyz'.

Dylan said...

...gragenftttz....

This sort of thing makes me want to cry.

Why? Why can't people just use databases properly in a safe manner?

*{Sob}*