News for hackers. Stuff that matters.
Re:Dammit, MySQL by jbooth on Thursday July 22, 2010 @05:05PM (#1539536)
Every one of his complaints is a practice I've constantly yelled at everyone around me not to use -- for my whole career.

Application logic belongs in the application. Don't rely on a database to do it for you, or you're going to wind up with a giant ball of mud driven by side-effects.

Re:Dammit, MySQL by Lewisham on Thursday July 22, 2010 @05:33PM (#1539602)
I don't see anywhere where he is calling for application logic. He's making sure that the data has integrity, and the database absolutely should be in charge of that. If your database can't enforce integrity, then you're in a very, very bad place.

Putting in sane integrity checks to a database should be a priority. You can fix broken applications, but you can't fix broken data (especially if you collected it from users, sensors).

Re:Dammit, MySQL by masklinn on Thursday July 22, 2010 @06:04PM (#1539688)
There was nothing about application logic in the post. Quite the opposite in fact (transactional DDL and deferred constraints are things which have no reason to be handled in the applicative layer). It was only about the internal consistency of the data.
Re:Dammit, MySQL by kingkilr on Thursday July 22, 2010 @05:09PM (#1539551)
Don't rely on the database to not put null in a column marked not nulls? That's like saying don't rely on C to not put a struct in a char*. It's got nothing to do with application logic.
Re:Dammit, MySQL by ars on Thursday July 22, 2010 @06:27PM (#1539742)
He's wrong about that BTW.

It doesn't put a NULL there, it puts zero for a number field, or a blank (0 length) string for a string field. Date fields also get 0 (00-00-0000).

Edit: I get a downmod for this? The group think here is amazing. I suppose if I bash MySQL I'll get some upmods even if the bashing is incorrect?

Re:Dammit, MySQL by dagheti on Thursday July 22, 2010 @06:40PM (#1539769)
Inserting a default value is even worse than inserting NULL. Now you can't tell what data was wrongly inserted if you tried to clean up the mess later.
Re:Dammit, MySQL by kls on Friday July 23, 2010 @07:48AM (#1540922)
Correct, NULL is NULL, if the system is designed that it has to have a default value it should at least scream at you "I can't do NULL, you are going to get a 0 here". Or better yet just error at design time, not run time, and say sorry put a value here I don't do NULL. Run-time errors are the worst kind of errors and it is little unexpected behaviors like that, that can create huge problem in a run time environment.
Re:Dammit, MySQL by ars on Thursday July 22, 2010 @06:53PM (#1539794)
What?

Have you ever programmed SQL? Because what you wrote doesn't make sense.

No data is inserted wrongly - you are simply leaving out a field. There is no mess. Just an unused field.

Are you thinking it's like csv where if you leave out a column all the others are shifted? It's not like that.

The standard says if you leave out a column that does not have a default the SQL should return an error. Instead MySQL puts in a default (but only if you tell it too in the configuration). Putting a NULL in a non-NULL field would be much worse.

Re:Dammit, MySQL by dagheti on Thursday July 22, 2010 @07:03PM (#1539823)
Oh I agree they are both very bad. Putting NULL in a non-NULL field is totally wrong.

However I really do think that inserting an unexpected default value is worse than inserting NULL into a NON-NULL field. The NULLs will cause problems, but they are problems you can see and resolve.

The default values are silent errors that will corrupt your data and be very difficult to recover from in the future. You can only guess which data was wrongly inserted.

Re:Dammit, MySQL by ars on Thursday July 22, 2010 @07:30PM (#1539888)
I still don't see how it's possible to insert wrong data, or corrupt it??

There IS no data. How do you corrupt something that doesn't exist?

And NULL doesn't help either. NULL is valid data, NULL is not a replacement for programming errors (which is what this is).

This argument is pointless. People love to bash on MySQL, they look for the silliest things. The more popular something is the more people bash on it.

I understand that, but at least bash on real problems? Like the transaction DDL - that's a real problem. This? This is nonsense. (It's actually a very useful - and optional - feature BTW.)

Re:Dammit, MySQL by Lewisham on Thursday July 22, 2010 @08:33PM (#1540015)
I'm not sure you understand the use of NULL.

NULL is not "default value" or "I don't care", NULL signifies "this might have a value, I just don't know what it is".

There is a very significant difference between a payroll record which states your pay is "0" vs. NULL. If the database is putting in default values, you have no way of knowing whether the employee really did have a salary, but it was incorrectly inserted as NULL, or whether the employee is unpaid.

Re:Dammit, MySQL by ars on Thursday July 22, 2010 @09:10PM (#1540075)
I understand NULL very well, and that's not the only use for a NULL.

NULL also means "value does not exist", not just "value is unknown". For example if a student is not in a class, put NULL in the class id.

NULL is perfectly valid data, and is not a replacement for a programming bug.

And with mysql if your salary field is defined as accepting NULL then you will get a NULL in there.

And to use your example if the field accepts NULL, you would also have no way of knowing if the salary was not negotiated vs a programming bug.

If you want to argue the insert should fail, then fine, no problem. (And MySQL can do that.)

But arguing that putting in NULL is better (in a field that does not accept NULL), is simply wrong. I'll say it again: NULL is not a replacement for a programming bug - NULL is valid data, and should not be used to find programming errors.

Re:Dammit, MySQL by anthonyb on Friday July 23, 2010 @03:13AM (#1540573)
> For example if a student is not in a class, put NULL in the class id.

I think you mean "don't insert a row in the student_class table, which is a many-to-many join between student and class".

As a general rule of thumb, if your data schema requires NULLs for things like that, then your schema is wrong, for most of the reasons that people are trying to point out. NULLs are the absence of data, and should really only be used for exceptional circumstances - hence the reason that silently inserting NULLs into NOT NULL fields is a Bad Thing(tm).

Re:Dammit, MySQL by AlisdairO on Friday July 23, 2010 @03:01AM (#1540552)
Pretty sure he's arguing that it should be an error.
Re:Dammit, MySQL by ora600 on Thursday July 22, 2010 @07:56PM (#1539945)
In any database comes a time where a developer or administrator will have to make manual changes to the data through an ad-hoc script. It can be straight sql or perl/php/ruby thing.

The easier you'll make it on him not to cause horrible corruptions in the data by forgetting to update that additional table that depends on whatever he is updating - the better life will be.

Triggers, foreign keys and constraints are all excellent ways to do just that.

Re:Dammit, MySQL by dasil003 on Thursday July 22, 2010 @05:38PM (#1539615)
What if you have multiple applications talking to the same database?
Re:Dammit, MySQL by derefr on Thursday July 22, 2010 @06:00PM (#1539677)
I think, in 2010, a "cloud-based web platform with a RESTful API" is 1990's "database." We don't let multiple applications talk to a single database; we stick a web server in front of the database and make them talk to that. If all the web API is doing is allowing CRUD on your data (with validation and some minimal triggered interactions) then, in effect, you're just creating your own, completely custom DBMS by doing this.
Re:Dammit, MySQL by dasil003 on Thursday July 22, 2010 @07:43PM (#1539914)
Yes, and this has a lot of advantages. One advantage is openness of APIs for mashups etc. But the big advantage driving the NoSQL/custom DB is of course scalability and performance. Incidentally performance was also a key consideration 40 years ago when everyone rolled their own custom data stores. The modern SQL database evolved as a response to all the problems that created, which by now are long forgotten.

My point is not to throw the baby out with the bathwater. If you need massive scale then of course you need fresh approaches, but you are sacrificing a lot by abandoning a centralized DB.

If your data is moderately sized, then you will be trading a lot of data integrity, queryability and flexibility by giving up an SQL databases. Certainly consumer web services of the type that are en vogue in silicon valley need new approaches. However the majority of applications out there are probably still best served primarily by a relational DB, especially when you consider the value of a byte of corporate data vs the value of a byte of facebook data.

Re:Dammit, MySQL by mmt on Thursday July 22, 2010 @07:19PM (#1539861)
in effect, you're just creating your own, completely custom DBMS by doing this.

I've argued for years that this is what the "sharding" hype (and to some degree the current NoSQL hype) was mostly about.

I'm not suggesting a custom DBMS can't possibly be the right answer, but it seems silly to layer it on top of something as heavyweight as a relational database.

Re:Dammit, MySQL by randallsquared on Thursday July 22, 2010 @07:37PM (#1539901)
If you don't use relational databases, you're throwing away decades of work into a problem that's very similar to the one you're trying to solve. If you have a brilliant new scheme that forces you to do that, fine, but throwing away finely tuned systems because complex and relatively opaque might not be the best first step.
Re:Dammit, MySQL by mmt on Thursday July 22, 2010 @08:55PM (#1540053)
Exactly. I would advocate not throwing any of it away, absent rare circumstances.

I implied, though foolishly didn't state outright, that I believe a custom DBMS is only very rarely the best answer.

Re:Dammit, MySQL by tomjen3 on Thursday July 22, 2010 @07:06PM (#1539834)
I made the same argument to my database teacher. He pointed out that isn't going to do you much good when you have more than one application talking to your database, since you would then have to write the code to validate it in each application.
Re:Dammit, MySQL by ryanpetrich on Thursday July 22, 2010 @07:46PM (#1539922)
Better yet, require that all applications call through some common API (such as a library or web service) to access the data.
Re:Dammit, MySQL by electrum on Friday July 23, 2010 @02:16AM (#1540508)
Why not put that API inside the database (i.e. stored procedures and views)?
Re:Dammit, MySQL by ora600 on Thursday July 22, 2010 @08:00PM (#1539958)
You can require this, but its tricky to enforce. Especially on DBAs.