Hi Jesse,
I got rather busy yesterday and didn't find the time to respond until now.
On 12/1/05, Jesse Erlbaum [EMAIL PROTECTED] wrote:
I hear what you're saying, and there is some truth to it. Assuming
you're using MyISAM (non-transactional) tables, it would require table
locking to accomplish something similar to transactions, and in that
case you still wouldn't get rollbacks.
However(!), a lot of this is solved by application architecture. Take
the need for a rollback. I am of the school of thought that the
application should measure twice, cut once -- IOW, don't go and try to
do something you shouldn't do. That is the typical case where you'd
need a rollback. For example, if you tried to add a customer who
already existed. If you look at my code in that situation you would see
me first check, THEN insert. As opposed to the rollback model where (I
suppose) you'd insert, wait for an error, and then rollback. (Examples
over-simplified and contrived for obvious reasons.)
Actually, I do the same thing. I always test all my data before
trying to add to or update the database. However, I assume that you
still test to make sure that your database update succeeded by
checking for error conditions. The fact that I get an error message
when blatantly invalid data (2005-02-30) is inserted into the
database, is still a good thing in my opinion.
The other thing to keep in mind is race conditions. If you check for
the existence of a user, and then insert into the DB based on that
info, you potentially have a race condition (another process could
have inserted the same user into the DB in the time between
instructions). That danger is of course removed by using transactions
or locking the table.
There are tonnes of other annoyances with MySQL but I'll only list a
I disagree. I believe there are only tons. ;-)
:-)
I actually go back and forth on the spelling of ton, but in Canada, I
think it is actually spelled tonne. The Hitchhikers Guide says this
about the Tonne:
http://en.wikipedia.org/wiki/Tonne
couple that 'really' annoy me:
- the first timestamp field in any table is always updated on
every UPDATE
- -00-00 is a valid date in MySQL
- 2005-02-30 is a valid date in MySQL
- insert NULL into a NOT NULL column and MySQL will give it a
default value
- overflowing data is truncated instead of returning an error
Some of these I agree with you, others I don't. Perhaps it is because I
wasn't born into the ANSI SQL world, but I do believe it is the job of
the application layer to check data before it blindly goes and inserts
it into the database.
This system becomes hard to manage when multiple applications need to
access the same database (you have to trust that everyone else does it
correctly). If you leave it up to the application to ensure your data
is clean when putting it into the database, then you must also check
to make sure it is valid when pulling stuff out of the database.
Granted we were talking about web applications which usually means one
app accessing the database, but even then you often have cron jobs
accessing the DB for certains things. I cover that by writing modules
that interface to the database, and make sure all my code uses those
modules. But the extra protection in the DB is still a good safety
net.
MySQL is very Perl-ish in these ways. It is thoroughly DWIM. MySQL
also adheres to the Perl idea of let the simple things be simple, and
the difficult things be possible. (With PgSQL, it's in for a dime, in
for a dollar -- even simple things are relatively complicated.)
I guess we are going to have to agree to disagree here. I don't
really think MySQL follows the DWIM mantra at all. For example, if I
create a new table and tell it I want it to be an InnoDB table, why
does it go and create it as a MyISAM table if InnoDB support is turned
off in the server? That isn't what I asked for (not having used the
latest MySQL, I am not sure if this is still the case).
Here is a more onerous example. Say I have a cost field in a database
defined as DECIMAL(5,2). With a field like that some might think that
the maximum number you can place in that column is 9.99. But in
fact it can only handle .99 in MySQL (in Postgres and most other
databases it actually only holds a max of 999.99, but that is not the
real problem here). If you decide to insert a number higher than the
max in that column when using MySQL, it just truncates it to the
maximum allowed value. So inserting 10,000 will result in a row that
contains 9,999.99. In fact inserting anything over 9,999.99 will
result in 9,999.99. That seems to me to be a very easy mistake to
make in an application that can have some nasty consequences.
I'm sure that the majority of MySQL users know about these things and
have no problems dealing with this in their code, but for new users it
is important to know what the database actually does with your
information.
As for
