US Monitoring Database Reaches Limit, Quits Tracking Felons and Parolees 270
An anonymous reader writes "Thousands of US sex offenders, prisoners on parole and other convicts were left unmonitored after an electronic tagging system shut down because of data overload. BI Incorporated, which runs the system, reached its data threshold — more than two billion records — on Tuesday. This left authorities across 49 states unaware of offenders' movement for about 12 hours."
As the astonished submitter asks, "2 billion records?"
Now.. (Score:4, Insightful)
They just need to upgrade it so they can track the other 4 billion properly.
Damn sick criminals! ALL OF THEM.
I think more than 2147483648 records (Score:2, Insightful)
Seems like it took them a few hours to change the key column from unsigned +/- 2^31 to signed 0-2^32-1
Re:Now.. (Score:4, Insightful)
Re:2 billion... (Score:3, Insightful)
Which just goes to show how well designed it was. Exactly how often do they need to track a negative number of people?
I know that in some programming languages, like java, you have to jump through hoops to get unsigned values. For all we know that database was fine, but the server frontend trunctuated values down to signed ints.
Re:Well no wonder (Score:3, Insightful)
Wasn't present in 2007, either. But have you ever tried loading a spreadsheet with more than 30,000 records, let alone one with more than 100,000 records?
Hope you have enough RAM, and that nothing else is open on your system....
Re:Now.. (Score:3, Insightful)
The joy of using centralised versus distributed systems. We live in the age of the network. Load balancing data sets across multiple databases, machines and systems and merging them whenever they are needed is trivial. Designing for such load balancing is trivial as well.
Anyone designing a system that piles up everything on a single box gets whatever christmas they deserve. By the way, considering the name of the company I am not surprised. It says everything that there is to be said about their design methodology...
Not that most of government contracts in the UK or USA are any different. They have been taken over the BI crowd and competence in design has been replaced by competence in explaining how it is not your fault that a f*ck up has occured. PRINCE, ISO, TOGAF, ZAMAN all have this as their primary function and they are now the only requirement towards jobs in this area. It is quite scary - you look at an advert for an architect and see these listed _WITHOUT_ any technical knowledge domain whatsoever...
Re:Oh dear oh dear oh dear (Score:5, Insightful)
And you are clearly completely unaware of the accounting world.
I have yet to meet an accountant that knows much of anything about access or any other database system. On the other hand the majority of them have complained about the 65000 line limit in excel.
They ALL do this. You're telling thousands of accountants to change how they do things, and honestly, not for the better. They know how to use excel and know how to make things balance with excel.
A large portion of them took accounting because it was supposed to make them a lot of money, these people don't even use 1/10th of the functionality provided in excel, lets not try to make them learn another entirely different software skill set, ok?
Even if you're currently working in IT and are like "Oh, no, our accountants have access to all this stuff in our system and they would never do that". Trust me, they do. It all ends up in an excel sheet somewhere eventually.
Re:32 bit signed integer strikes again (Score:2, Insightful)
2 billion? That's awkwardly close to 2147483647... This is why your ID field should be BIGINT and not INT....
And I see no reason why someone would use a signed integer for an ID field. You're wasting half of the type's range (assuming negative ID's are not used).
Re:Now.. (Score:5, Insightful)
I really doubt space was the actual problem because TFA says "BI Incorporated, which runs the system, reached its data threshold - more than two billion records - on Tuesday. " The max value of a signed 32 bit int is 2 147 483 647. It is much more likely that someone set an index value on the database to int years ago and then forgot about it.
Maybe the answer isn't better software (Score:5, Insightful)
Maybe the answer isn't better software, but fewer criminals to fill up the database with.
I keep seeing articles here and there how the U.S. has more people imprisoned than China. A large chunk of the prison population are inmates convicted of drug crimes and a large portion of that set of people were convicted on marijuana laws.
I don't smoke, but as a tax payer I would rather see the government make marijuana into a tax revenue generator instead of a huge expense to paid for with taxes.
Re:1 in 31 US Citizens in custody or parole (Score:1, Insightful)
At least, not BEFORE they did time...
Comment removed (Score:5, Insightful)
Re:Maybe the answer isn't better software (Score:4, Insightful)
Re:Now.. (Score:2, Insightful)
Are people really this dumb?
A record does not equate to a single individual. They are tracking movements so in the movement table, I am sure they have a lot of records for each person they are tracking, not to mention the other tables that contain multiple entries for people
Re:Oh dear oh dear oh dear (Score:3, Insightful)
And you are clearly completely unaware of the contracting world.
I have yet to meet a contractor that knows much of anything about screwdrivers or any other tool than a hammer. On the other hand the majority of them have complained about how hard it is to drive screws with the hammer.
They ALL do this. You're telling thousands of contractors to change how they do things, and honestly, not for the better. They know how to use a hammer and know how to drive nails.
A large portion of them took contracting because it was supposed to make them a lot of money, these people don't even use 1/10th of the functionality provided by a hammer, lets not try to make them learn another entirely different tool skill set, ok?
Even if you're currently working in contracting supply and are like "Oh, no, our contractors have access to all this stuff and they would never do that". Trust me, they do. It all ends up pounded by a hammer somewhere eventually.
Re:1 in 31 US Citizens in custody or parole (Score:5, Insightful)
Not before their incarceration, no. But after surviving lock-up in a Darwinian environment in which "fittest" equates to "most dangerous", then re-entering a society in which convicts are denied the right to a good job, there's a pretty good chance they will. We have a criminal justice system that develops criminals.
You have it backwards. (Score:4, Insightful)
There is no authorization in the constitution for laws that control what you do personally or consensually. The criminals, as Mark Twain told us, are in the legislature.
And as long as the government is out of compliance with the constitution, the government is a criminal organization. Law-breakers and oath-breakers, both.
Re:Oh dear oh dear oh dear (Score:3, Insightful)
Re:2 billion... (Score:3, Insightful)
unsigned integers only, which are rarely used, especially in databases.
What? Intro DB courses all mention using unsigned columns for numeric/incremented indices. I use unsigned ints by habit for numbered indices. I'll grant you I've seen plenty of really terrible DB designs in the wild that happen to use signed ints, but "especially in databases" unsigned ints are more frequently used, at least by the competent pros I've met.
Also, if the system's already down, there's no load on it. The admin can mount the schemas (maybe rollback a bit), apply the changes, and go get some coffee. It's not that it wouldn't take some time, but days? I think not. Sure, with 2B records there's going to be some fun disk action while you reindex everything, but you've got the entire server's power to do it with. An afternoon, maybe, depending on the (probably HA) hardware and the (admittedly in this case lacking) sanity in table design. If (in this case admittedly unlikely) they're partitioned by timestamp values or something, I think (but I'm not sure) that you could get the most-recent partitions of the table altered and running and get the system up right away, then take historical partitions and convert them on backup hardware or during low load periods. Not a good solution, but it'd bring the system up in the least time. Or depending on the features available in the DBMS, and diskspace available, they should be able to do something like Oracle's online table redefinition; just copy table to new tablespace, but include the modified column definition when writing, then swap it.
*shrug* My $0.02 CAD. I work on DBs but usually on smaller systems, so that's my perspective. Maybe there's some deep magic involved in larger recordsets that can't be compensated for by good design.
Re:Oh dear oh dear oh dear (Score:2, Insightful)
You're missing the point. They don't want a solution - they want to quickly look at some data, make a decision based on it, and move on. No-one's going to pay for/install an app; deploy stuff to a website etc. I can do the sql query, export to csv, import into Excel, spend 1 min on it and email it on. Done - on to my next task. Access is a piece of shit, and getting data from Oracle/SQL Server and putting it into Access is just bizarre, even if I could guarantee all end users would have it installed.
> Also, chances are, if you think any typical business data set is best represented by a spreadsheet, you are probably not qualified to make the call.
You're not qualified to make this call if you can't understand that I'm giving the users what they want, quickly and cheaply; they'd tell me if it wasn't any use. They want the numbers, not a "solution".