Comment On The Seven Levels of SQL

Apart from the nicely trimmed, minimalist, efficient code - what really catches my eye is the "CREATE TABLE IF NOT EXISTS"... now, I don't know if this is a vendor-specific version of a temporary table (#whatever or @whatever in SqlServer for my shame - possibly (but rarely) ##whatever), but how often should a production system expect to find a key table missing? Empty maybe... missing? Just create the damned table!

They're making a pretty dangerous assumption when they don't check if the uaccounts table exists before accessing it.

 At least they're handling dates properly.
 

Ummm... I got lost around step 2... crap

The user should manually enter the SQL query. This would be so much simpler.

Yes, for the user.
 

Oh dear, that much SQL makes me feel somehow dirty. Lots of redundancy, but hopefully the db caches all those selects from Dispatchers_tmp.

Gotta love step 11 with all that table locking. I guess that's called "atomic operations". What, concurrency? Hogwash.

Are they using some kind of ORMs?

Tim Gallagher:

This just absolutely amazes me.

RJ Writes, "When I first began working at the company in question, I was trying to get a handle on the flow of database queries during a fund transfer, so I enabled query logging, and stepped through an example transaction. (Platform, PHP/MySQL on Linux). For any who read this: DON'T PANIC - the system never saw real production use until after it was completely revamped in a proper DBMS.

 

This is clearly an OMFG.  Not to mention the absolutely abysmal design, the opportunity for SQL injection is rampant!  Not quite what I want from my bank...but it might be a good feature for someone else's bank...what is the URL for their production website?     

Anonymous:

Tim Gallagher:

This just absolutely amazes me.

RJ Writes, "When I first began working at the company in question, I was trying to get a handle on the flow of database queries during a fund transfer, so I enabled query logging, and stepped through an example transaction. (Platform, PHP/MySQL on Linux). For any who read this: DON'T PANIC - the system never saw real production use until after it was completely revamped in a proper DBMS.

This is clearly an OMFG.  Not to mention the absolutely abysmal design, the opportunity for SQL injection is rampant!  Not quite what I want from my bank...but it might be a good feature for someone else's bank...what is the URL for their production website?     

I don't understand your remark about SQL injection. Isn't he just
logging the queries as they arrive at the DBMS? There's no way to tell
if they have been safely escaped in the application.

This is fabulous. People who write pants code like this make even semi-skilled, ageing contractors like me look competent, or even guru-like. I love it when someone brings you "non-performant" code, and a quick squiz tells you it was written by a complete muppet, and fixing it (if only to ones own level of competence) will be trivial. That always gives me a warm, wealthy feeling all over... So go ahead - write some really bad code and save a Dinosaur today.

Oh good grief. I got about 1/4 of the way through and quit.  Needless to say it looks like overkill as the author suggested.

> DON'T PANIC - the system never saw real production use until after it was completely revamped in a proper DBMS

Ah excellent. It wasn't bad coding that was the problem, it was MySQL. If it's free, it must be bad!

MySQL has a bad rep these days for some reason, but it's not really a terrible RDMBS. It works for big sites like mixi, livejournal, vox, slashdot, etc., so you shouldn't discount it immediately.

Captcha: poprocks.

discount MySQL NOW

free is not cheap enough for that pile of turd

This is nothing (as in: could be worse): Imagine a table, with a header row on the top (showing arrangements) and the left (showing customer names). In between are the deadlines.

 

The first query selects all names for the arrangements (with some tricky joins to get only the arrangements which should be shown), the second query the customer names, and the third query getting all arrangements. Then, for each customer name, the arrangements for each school are gathered and then picked from the results of the third query. Are you still with me?

 This still was quick enough: no load on the server, and only a few customers (yet) using these arrangements. The bigger problem is that the guy who wrote this didn't understand the relations between the tables (not his fault: no documentation, and old & unused tables hanging around, the bigger WTF), so it didn't do what it was supposed to do. And the biggest problem is that I had to fix it :(

I should have rewritten it, but I was half-way when I recognized that...

Thank you for this excellent WTF.

My brain hurts.

 

Maybe I'm too used to being spoon fed by these stories... I got to step 4 and without seeing a punchline speeding towards me like a bullet and gave up. Or maybe it was because I knew what the punchline was - more of the same bad, bad SQL.

 

Captcha: craptastic .. indeed!

Anonymous:

> DON'T PANIC - the system never saw real production use until after it was completely revamped in a proper DBMS

Ah excellent. It wasn't bad coding that was the problem, it was MySQL. If it's free, it must be bad!

MySQL has a bad rep these days for some reason, but it's not really a terrible RDMBS. It works for big sites like mixi, livejournal, vox, slashdot, etc., so you shouldn't discount it immediately.

Captcha: poprocks.

Right, thats it: MySQL is a pile of dung, even compared to other free RDMBSs. Don't believe me? Go check out the PosgreSQL manual, far more features (including the basics missing from MYSQL).

Generally MySQL has a bad reputation because it sucks arse!
 

Tim Gallagher:

I don't understand your remark about SQL injection. Isn't he just
logging the queries as they arrive at the DBMS? There's no way to tell
if they have been safely escaped in the application.

 Look at the variables in the SQL:

        update uaccount set balance=balance+42.45,available=available+42.45 where uid=2191

 

They're actual values (as in monetary amounts).  I should see bind variable names in their place...otherwise, I could put arbitrary SQL snippets into various Web form input fields and get all sorts of really useful information about other users in the system.  Far better than that, I could probably work out a way to add money to my own account without any sort of audit trail.

 

Tim Gallagher:

I don't understand your remark about SQL injection. Isn't he just
logging the queries as they arrive at the DBMS? There's no way to tell
if they have been safely escaped in the application.

Sorry, I didn't address your question fully...it perhaps depends on the tracing mechanism of MySQL, but in other databases I've worked with the trace includes *exactly* the SQL that was passed to the parser, not a version of SQL that has substituted variable values.  Thus, it would seem to me that what has been passed to the query parser is literals...

 

Tim Gallagher:

  d.  select date_format(curdate(),'%W,  %M %D,  %Y') 

This reminds me of a bit in one circa 1998 computer joke book...  it described various business cases and how to handle them with the new "Chaotic Management" style. Once solving the business case with the most chaotic way imaginable, almost all of the case solution descriptions ended with "...and when you're done with this, the management team will go out to the track to drive around with go-karts."

Thanks to the miracle of CodeSOD, we've finally discovered the programming equivalent of that thing!

and the scary thing is, it's the joes who write this kind of sh**t that get the big bucks.

Apart from the obvious WTF of misunderstanding use case descriptions,
the indexing in case 11 is plain wrong: 'z' should be followed by 'aa'
and not 'zz'. What if you needed to add another 20 or so meaningless
points after that? You'd end up with some monstrocities like
'zzzzzzzzzzzzzzzzzzzzz' and 'zzzzzzzzzzzzzzzzzzzzzz' instead of simple
'at' and 'au'. And if you need to include even more steps in your
description, you'll end up with a whole pages full of z's when you
could've done it with 'xrz'. This I simply cannot forgive.

Machine generated code is dumb that way. Thats how dumb assembler generated code looks for a advanced assembler code.

  e. select * from Dispatchers_tmp where tid='0351454703'

  f. select * from Dispatchers_tmp where tid='0351454703'; //WTF? again?

 ===

e.  ldx #40

f.  ldx  #40; //WTF? again?

Theres also bad design here, but maybe the real wtf is not the original one.
 

cheesy:

Ummm... I got lost around step 2... crap

dmcimini:

Oh good grief. I got about 1/4 of the way through and quit.  Needless to say it looks like overkill as the author suggested.

Anonymous:

Maybe I'm too used to being spoon fed by these stories... I got to step 4 and without seeing a punchline speeding towards me like a bullet and gave up. Or maybe it was because I knew what the punchline was - more of the same bad, bad SQL.

This CodeSOD will soon be available in a MySQL database on my website. Can all subsequent posters please refer to the relevant part(s) by number and letter?

Anonymous:

Apart from the obvious WTF of misunderstanding use case descriptions, the indexing in case 11 is plain wrong: 'z' should be followed by 'aa' and not 'zz'. <snip/>

Yes, that was a bit of a problem. I had to write a stored procedure to deal with that.

Anonymous:

Right, thats it: MySQL is a pile of dung, even compared to other free RDMBSs. Don't believe me? Go check out the PosgreSQL manual, far more features (including the basics missing from MYSQL).

Anonymous:

I don't understand your remark about SQL injection. Isn't he just
logging the queries as they arrive at the DBMS? There's no way to tell
if they have been safely escaped in the application.

I think the point was that with One Hundred Plus Queries™, there is ample opportunity for mistakes to be made and vulnerabilities to pop up.

 

Anonymous:

Anonymous:

Right, thats it: MySQL is a pile of dung, even compared to other free RDMBSs. Don't believe me? Go check out the PosgreSQL manual, far more features (including the basics missing from MYSQL).

Yes, obviously more features means better software.

PostgreSQL sucks about as much as MySQL does. It's just a different kind of suck. 

SELECT (rdbms_name || ' sucks') AS divine_truth FROM rdbms ORDER BY rdbms_name

Autonuke:

Thanks, guys, for your overwhelmingly convincing arguments. Now I don't have to try either system for myself. 

As a service to other readers, may I point out that 

SELECT (rdbms_name || ' sucks') AS divine_truth FROM rdbms ORDER BY rdbms_name

That said, I'd draw the line at using MySQL for money transfers.

MySQL is between SQLlite and PostGress/Firebird, but is moving upward to SQL Server. Anyway SQL Server is a moving target because seems moving upward to Oracle.  Oracle can't move upward, and is becoming a OS itself.

I think money is betwen SQL Server and Firebird/PostGress and up. But maybe you can use a newer version of MySQL.  Older ones are a bad idea (v3), because seems to corrupt tables on faulty hardware (lack of redundancy?). Theres also a horrible lack of features on v3 and older. Also theres the thing MySQL is newbie friendly, and not standard nazi. And this is a very bad thing if you are nazi about SQL code. While If you are a happy Access coder, or maybe SQL Server+VB coder,  something like Firebird will looks almost terrible because is nazi-compliant. I have ear that you can order pizzas with Oracle secret features.

 

What did it take to get a redesign?  A system as messed as this must've been supported by some of the management to a degree that doesn't make sense... for some reason, I bet it took more than a simple "look how bad this is!" .  It must've cost a fortune to redo it!

Anonymous:

Autonuke:

That said, I'd draw the line at using MySQL for money transfers.

MySQL is between SQLlite and PostGress/Firebird, but is moving upward to SQL Server. <snip/>

Good point. I should have said 'the current version of MySQL'.

select tid from Dispatchers_tmp where tid='0351634373'

WTF? Where's the point on that one? Something like this?

if(tid == "0351634373") {
tid = "0351634373";
}

MySQL got it's bad reputation from the total lack of transactions in early versions, and the text from the manual saying that "transactions are not really needed, becasue the application can just lock the table insted). (And subselect are not needed either, because you can just do many selects insted)

It also had a feature(And may still have), where it would just silently truncate data that were to big to be inserted into the database.

And I don't know how it is in mysql5, but with mysql4 you might think that you could just create a database, then create a table and then use

begin to start; a transaction, but no becasue the default tabletype for mysql4 did not support transactions, so it would just ignore(No warning, just ignore) your begin and comit.

Slashdot and livejournal can live with mysql, because they need the speed(And mysql is fast when running without transactions), and not the features. I mean who cares if a race condition causes a few posts on slashdot to go missing, or be attached to the wrong thread.
 

  h. update uaccount set balance=balance-42.45,available=available-42.45 where uid=2192
  i. update uaccount set balance=balance+42.45,available=available+42.45 where uid=2191
  k. update uaccount set balance=balance-1,available=available-1 where uid=2192
  l.  update uaccount set balance=balance+1,available=available+1 where uid=2191
  n. update uaccount set balance=balance-1,available=available-1 where uid=2192

Make the transaction....then swap over another dollar...then charge one more, just for fun?

That should teach them to make a financial system in PHP. 

Anonymous:

> DON'T PANIC - the system never saw real production use until after it was completely revamped in a proper DBMS

Ah excellent. It wasn't bad coding that was the problem, it was MySQL. If it's free, it must be bad!

MySQL has a bad rep these days for some reason, but it's not really a terrible RDMBS. It works for big sites like mixi, livejournal, vox, slashdot, etc., so you shouldn't discount it immediately.

Captcha: poprocks.

OK, so I took a cheap shot at MySQL. I must explain that when this system was written, version 3.x of MySQL was in use, which did not have transactions, foreign key constraints (or any constraints, really), and which would allow for a '0000-00-00' date value, as well as silently truncate values without warning, or insert a default value in place of a NULL, etc...

So yes, "proper DBMS", in the domain of monetary transactions, means one that at least has foreign key constraints, triggers and constraints: all the things which allow you to actually make sure your data is as intended. Nowadays, I suppose MySQL 5.x could be coerced into providing most of these things, although I simply haven't the patience.

To me it looks as if someone didn't know how to pass parameters in an object. It looks as if all revelant information is stored under a key (which should have been an internal object) and then all fields are treated as ... well, members of that object. This is object-oriented SQL! This is Truly Enterprisey!

Wy all the locking? If they used InnoDB tables then they could use ACID
compliant transactions...  Yes, I'd expect MyISAM tables to
support transactions too, but it's not like they are totally absent.

Autonuke:

Anonymous:

Autonuke:

That said, I'd draw the line at using MySQL for money transfers.

MySQL is between SQLlite and PostGress/Firebird, but is moving upward to SQL Server.

Good point. I should have said 'the current version of MySQL'.

Yes.  Future versions of MySQL make a perfectly sensible choice for banking applications.

Anonymous:

Anonymous:

> DON'T PANIC - the system never saw real production use until after it was completely revamped in a proper DBMS Ah excellent. It wasn't bad coding that was the problem, it was MySQL. If it's free, it must be bad! MySQL has a bad rep these days for some reason, but it's not really a terrible RDMBS. It works for big sites like mixi, livejournal, vox, slashdot, etc., so you shouldn't discount it immediately. Captcha: poprocks.

Right, thats it: MySQL is a pile of dung, even compared to other free RDMBSs. Don't believe me? Go check out the PosgreSQL manual, far more features (including the basics missing from MYSQL).

Generally MySQL has a bad reputation because it sucks arse!
 

 Actually, when MYSQL first came out, it was perhaps THE BEST reasonably priced SQL!  Prostgres used POSTQUEL, and ingres used QUEL.  About the only other game in town was MSQL, and MYSQL ALWAYS blew IT away.

Do more than read the manual and learn SQL from a specific product, and you'll think a bit differently.

Steve

ArSing:

Are they using some kind of ORMs?

I would hazard to guess mebbe some POOP (PHP Object Oriented Programming) code where classes kept on filling private members from the database...?

I was a bit disappointed not to find a "DROP TABLE 'Dispatchers_tmp';" anywhere there.  Why delete from temporary tables when you can just drop them! :oP

It's a good think I have no idea what this is about, because otherwise I'd probably be pretty torqued.

 The WTF for me in all these SQL-related WTFs is that, well, it ain't programming.  I thought this was about programming.  You know, C, C++, Fortran, hell, even LISP, but SQL?

More power to all you DBAs out there, and to those of you who have to write queries and generate reports.  It's a legit biz, but face it--you're users, not programmers.

h. LOCK TABLES uaccount WRITE
        update uaccount set balance=balance-42.45,available=available-42.45 where uid=2192
        UNLOCK TABLES
i. LOCK TABLES uaccount WRITE
        update uaccount set balance=balance+42.45,available=available+42.45 where uid=2191
        UNLOCK TABLES

 

Hmm, so we're trying to get somewhat transactional, but then we unlock in between the two steps of the transaction.  Genius.

 

 

Captcha: cluless

Ya think?

eric0000:

Autonuke:

Anonymous:

Autonuke:

That said, I'd draw the line at using MySQL for money transfers.

MySQL is between SQLlite and PostGress/Firebird, but is moving upward to SQL Server.

Good point. I should have said 'the current version of MySQL'.

Yes.  Future versions of MySQL make a perfectly sensible choice for banking applications.

:-)

I admire your foresight.

mrprogguy:

It's a good think I have no idea what this is about, because otherwise I'd probably be pretty torqued.

 The WTF for me in all these SQL-related WTFs is that, well, it ain't programming.  I thought this was about programming.  You know, C, C++, Fortran, hell, even LISP, but SQL?

More power to all you DBAs out there, and to those of you who have to write queries and generate reports.  It's a legit biz, but face it--you're users, not programmers.

 

Speaking as a programmer who also does SQL, you are quite, quite wrong.

 

Rich 

That's funny, we use MySQL for production and it works just fine.  We use ACID transactions, stored procedures, triggers and we replicate specific critical tables in realtime to an offsite system.  We do hot backups of not only specific tables, but the entire DB without issue.

Sure, you can go off and create tables that use non-ACID safe database engines, but you get what you pay for. 

I'm not sure what your specific issue with it has been in the past, but so far we've had none.

 

mrprogguy:

It's a good think I have no idea what this is about, because otherwise I'd probably be pretty torqued.

 The WTF for me in all these SQL-related WTFs is that, well, it ain't programming.  I thought this was about programming.  You know, C, C++, Fortran, hell, even LISP, but SQL?

More power to all you DBAs out there, and to those of you who have to write queries and generate reports.  It's a legit biz, but face it--you're users, not programmers.

Lets see... you admit you have no idea what this is about (and that's a "good think"), but you know it ain't programming. One could argue that if you've ever used strcat() in C you are a user, not a programmer. If you have ever stored data in the filesystem, you are a user, not a programmer. Also, I don't suppose you are aware that a real DBMS allows you to do such things as create custom datatypes, functions, and other such embarrassing "user" tasks.

Anyway, I believe the subtitle to this website is "Curious Perversions
in Information Technology", not "Programming WTFs".

Ok, heres a selection of some MySQL problems I've hit.

 

Spaces at the end of a char or varchar:

4.x => Spaces stripped from both

5.x => Spaces stripped from chars, left on varchars 

 

NULLs dont compare equal. Whoopdeedoo. Now look at your queries where you join on an index that may be null. Byebye cardinality. SET  VARIABLE myisam_stats_method="nulls_equal";

 

Auto completion: http://bugs.mysql.com/bug.php?id=24624

^^ That one is deserving of a WTF of its own "Hey, lets add all these SQL commands to the auto completion hash AND THEN WIPE THE HASH". Nice one. Check out how one feature was added in 2000, another feature added in 2001, no-one notices that the second introduces a regression until 2006. 

 

The query analzyer changes massively between versions. It changed massively from 4.0 to 4.1, from 4.1 to 5.0. Rewrite all your queries every six months sir?

 

However, the most shocking thing about MySQL is how poor it performs with large numbers of joins. Anything above about 16 joins, and you might as well forget it. The suggested fix? Denormalisation. Yay! Forget all that BCNF crap you learnt, you no longer need it!

 

(Other topics I might have mentioned: mysqldumps between different versions, hotcopy,  date handling, REGRESSION TESTING, shoddy performance, quantum queries..... I dont doubt I'd encounter bugs *like* these if I used another DB vendor, but I doubt I'd hit them as frequently...)

Anonymous:

NULLs dont compare equal. Whoopdeedoo. Now look at your queries where you join on an index that may be null. Byebye cardinality. SET  VARIABLE myisam_stats_method="nulls_equal";

err.. NULLs don't compare equal in Oracle either

This almost looks like an attempt at convolution to secure employment security. Oops, didn't work.

Re mySQL being free, it's only free for commercial use if you violate their license. See http://www.mysql.com/buy-mysql/

Re mySQL sucks, Oracle Corp. has provided at least limited endorsement by purchasing Innobase, allowing mySQL and Oracle to screw up the same data sets. See: http://www.mysql.com/news-and-events/news/article_968.html
 

RJ:

Anonymous:

> DON'T PANIC - the system never saw real production use until after it was completely revamped in a proper DBMS

Ah excellent. It wasn't bad coding that was the problem, it was MySQL. If it's free, it must be bad!

MySQL has a bad rep these days for some reason, but it's not really a terrible RDMBS. It works for big sites like mixi, livejournal, vox, slashdot, etc., so you shouldn't discount it immediately.

Captcha: poprocks.

OK, so I took a cheap shot at MySQL. I must explain that when this system was written, version 3.x of MySQL was in use, which did not have transactions, foreign key constraints (or any constraints, really), and which would allow for a '0000-00-00' date value, as well as silently truncate values without warning, or insert a default value in place of a NULL, etc...

So yes, "proper DBMS", in the domain of monetary transactions, means one that at least has foreign key constraints, triggers and constraints: all the things which allow you to actually make sure your data is as intended. Nowadays, I suppose MySQL 5.x could be coerced into providing most of these things, although I simply haven't the patience.

Actually, foreign key constraints themselves may be a WTF in the case where the fact that data is missing has a specific meaning within the DB design. Read your E.F. Codd.