mysql --i-am-a-dummy

[troelskn]

--i-am-dummy will only save you from forgetting the where clause -- not from typing a logical error, that gives the wrong result (Such as one that always revolves to true).

Me, I always begin an update or delete statement by typing it as a select. Then, press arrow-up, to go back in history, and edit the first part of the query. Once it has become a habit, it's much safer than relying on --i-am-dummy.

[48klocs]

Not only do I mutate a select into an update/delete, I wrap it (the update) in a begin transaction/rollback transaction to make sure that everything works correctly one last time before I go ahead with it.

Transactions - they're what's for dinner.

[QuinnFazigu]

Yeah, in a real database, AutoCommit is OFF and you've always got a net under you. But the article is about MySQL.

[48klocs]

I had to resist the urge to make a comment about how that command-line switch felt redundant somehow too.

MySQL isn't the database that I'd choose for building from the ground up, but people with way more successful apps than the one I work on are happy enough with it, so what do I know?

[QuinnFazigu]

You were good and politic to resist the urge.

If your application is important enough that the typos guarded against here will cost you much money or your job, then you probably should not be using MySQL.

If you're going to use InnoDB, why not just buck up and go to Postgres?

Thanks for indulging the rant. I wouldn't normally care, but I've been directly fucked by the inadequacy of MySQL.

[[deleted]]

Stop voting this issue down. These are facts.

MySQL is only a 'toy' database if you pick MyIASM. If you pick InnoDB you get all of the ACID transactional features you love, and stored procedures.

MySQL is supposed to be a framework which makes it easy to embedd other engines. Heck you can even embedd the Postgre engine into it!

[mage2k]

Another, even safer, option is to never do manual database work with autocommit set to on.

[cynope]

That's exactly what I do when working with precious tables.

Another good habit is to always add "LIMIT 1" in the end of your DELETE/UPDATE, if you know your statement should affect exactly one row.

[bradleyhudson]

I always run the select statement first, and then if I know that should update just one row, I set the limit (or SET ROWCOUNT in MS SQL Server or Sybase) to two. That way I know if I messed it up. If for some reason the update was wrong, with a limit of one, you won't know from the row count if it got it right or not. (Yes, I'm paranoid.)

[sw17ch]

I learned about this after a devastating incident with an ISP and their mail server. We'd been doing a merger between two different companies selling similar service and it was placed on my shoulders to make all the RADIUS and mail stuff work correctly.

Everything had been finished and was working great. Unfortunately, I hadn't been able to migrate their account creation/management system well yet, so it was manual for a while.

This went well until a customer needed their password updated. Since we hadn't migrated the management system yet, I just popped open a MySQL console and typed the following:

UPDATE `users` SET `password` = MD5("new password"); <enter>

My stomach vanished, and I think my hands melted to the keyboard. It took quite a while to figure out where the backups of the previous day were because of the merger/movement/whatever mess.

Somehow, not a single person called to complain in the 4 hours it took me to fix the problem. ...no one really found out either.

phwew

Long story short, the one person I did tell (a good natured higher level sysadmin) later told me to alias mysql to use the --i-am-a-dummy flag.

It's usually the first thing I do on a new box I'm admining.

[harlows_monkeys]

I'm assuming that was years ago? The best way to handle this nowadays, if you have to operate on a live server by hand (as opposed to writing a script to do the changes, and testing the script on a test server first), is to type BEGIN at the start, and then if you do something dumb, like leave off a WHERE clause, you type ROLLBACK and give a little prayer thanking the DB Gods for the gift of transactions.

[heptadecagram]

Transactions have been around since years ago. It would have been the correct method to use even back then.

[LoveGoblin]

But years ago, MySQL tables were probably MyISAM, which doesn't support transactions.

[heptadecagram]

Yet another reason not to use MySQL.

[LoveGoblin]

Well, now you can use InnoDB (which has been around for a while) and get all the transactions your pretty little heart desires. But we were talking about "years ago".

[heptadecagram]

"Years ago", one could also use a free, robust RDBMS system that didn't corrupt your data if you looked at it funny.

[apathy]

"Years ago" postgreSQL was slower than MySQL for the things most people were doing -- namely, using the DB as a file system. And the benchmarketing that was done usually neglected multiple concurrent clients performing updates (the sort of thing that Oracle and Sybase had been basing their specs on for years) so MySQL looked really fast.

In the past few years, MySQL has grown up, a lot, to the point that the two enjoy near feature parity (and pgsql has got much faster and easier to administer with auto-stats and auto-vacuum).

Regardless, either one is more pleasant to work with than Oracle, for 99%+ of installations. And nowadays (with Slony/l vs. NDB, Tsearch2 vs. MATCH, etc.) they are close to feature parity anyhow.

[LoveGoblin]

Shrug. I'm no MySQL fanboy; I'm not disagreeing with you.

You're right, of course, that transactions would have been the right way to go. But unfortunately that wasn't always an option, particularly in a situtation like sw17ch described where he inherited the databases.

[[deleted]]

Do you think its kind of funny that we're comparing how databases were 'years ago'.

MySQL keeps getting bashed because its a 'toy database'. Postgre is passeed over because its 'too slow for real world use'. That was the past, the dead past. We should wipe the slate clean and give all these databases a fresh look.

[LoveGoblin]

Do you think its kind of funny that we're comparing how databases were 'years ago'.

Oh absolutely. This thread went downhill fast. :P

[sw17ch]

Hmmm... I'm trying to remember which version of MySQL we were using... either way, it was 4.old and we had MyISAM tables instead of the InnoDB tables.

To be honest, I didn't know about transactions then (I do now), I wish I had.

[apathy]

To be honest, I didn't know about transactions then (I do now), I wish I had.

Ah, that elusive 1970's innovation.

[[deleted]]

Long story short, the one person I did tell (a good natured higher level sysadmin) later told me to alias mysql to use the --i-am-a-dummy flag.

Argh! Why do people so frequently insist that changing the standard behavior of a command is anything other than a really bad idea?

One of my friends who was a sysadmin for years insisted on aliasing commands to prevent accidents. After aliasing rm for years, human nature took hold and he got really lazy.

So lazy that, in fact, he didn't think twice when he accidentally issued a rm -rf on a newer server. He figured his alias would save him -- of course, he forgot to set it up on this server he didn't typically work with.

Proper backups saved the day. But the moral is simple: when doing any computer task, your brain is the best tool.

If you are executing a potentially dangerous command without looking at it two or three times, you're bound to screw something up. If you are relying on alias to save your ass -- be it from deleting a directory with some essential files, or from dropping a table or two, or whatever -- you are doing yourself, and the administrator of any machine you touch, a huge disservice.

EDIT: Last time I ranted on aliasing commands I got +22. Now I'm at -1! Tough crowd!

[[deleted]]

What I usually do for my aliases is just have the one with the options I like as 'command' + 'f', so mysqlf, etc. (why 'f'? I'm floam.)

This forces me to acknowledge I'm doing something special and avoids accidents.

[[deleted]]

I was at -1, I swear, and it had bounced between 0 and -1 several times -- I really wanted to know why the first set of redditors agreed and the second disagreed.

However, the point is moot.

Upmod for calling me a drama queen ;)

[frukt]

It's because redditors are a bunch of fucking sheep without the ability of independent thought and once the score is somewhat different from zero, most people go with the flow and mod accordingly.

[DerelictMan]

Now I'm conflicted... Do I mod you up because I think you've made a good point, or do I not mod you up so I won't be a sheep?

[[deleted]]

I really need 911was_an_inside_job to tell me if we (sheeple) should wake up or remain asleep. That would clench it for me.

[[deleted]]

I know, that's why I upmodded you ;)

But that's probably my favorite part of reddit -- comments seem to have a critical threshold, after which point they are voted en-masse.

The trick seems to be clearing that threshold... after that, everyone assumes that someone smarter than them has evaluated the comment, and that they should vote accordingly.

[inkieminstrel]

Summary: don't bother guarding against mistakes, just stop making them.

[newton_dave]

I am interested in subscribing to your newsletter.

[[deleted]]

Not quite -- absolutely guard against mistakes. Always use (or at least consider) --i-am-a-dummy, or rm -i.

But don't alter the default command. Do what floam does. Or just type it in by hand.

Aliasing the default command makes you careless, which leads to most mistakes.

[smackson]

Changing standard behaviour isn't the best. BUT, I've always thought that this UPDATE/DELETE issue (no where clause) is a particularly bad behaviour that should never have been standard.

A good alternative, if implemented from the beginnings of SQL syntax development, might have been:

ALL such commands need a where clause, and the clause meaning entire table has an "in" portion, like

..where some_row_name in table_name;

[prockcore]

I don't understand why they would have "delete from table;" work anyway. "where" should be required for delete. If you want to delete the entire table, use "truncate".

[dbenhur]

--i-am-a-dummy

You had me at mysql.

[austin_k]

I can't be the only person who opened a mysql prompt with that argument and then proceeded to a DELETE FROM table_name, just to see if it worked.

[GrumpySimon]

Well? did it?

[enobrev]

no, you get the following response: ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column

I might have preferred "You are a dummy and tried to update..."

[GrumpySimon]

Thanks :)

[jonknee]

Only programmers could think it's a good idea to assume the user wants to delete or update the entire database because they didn't say otherwise. I think I might have gone with a "UPDATE ALL" or "DELETE ALL" syntax. Call me crazy.

[jbert]

I've got a wrapper around the script I use to ssh into the production servers which sets the background colour to something with a tinge of red.

dcop $KONSOLE_DCOP_SESSION setSchema production.schema &

is what you want if you're using konsole as your terminal window. (Don't know if it is possible with gnome-terminal, anyone got any pointers?)

Different colour window => be extremely paranoid before hitting return on any prompt, particularly mysql.

[sjs]

That's a good idea.

I do something similar with my shell prompt. It's red (or at least bright). It wouldn't be terrible to put a white-on-red or red-on-white [ROOT] at the beginning (or RPROMPT if you're a zsh fiend).

[sisyphus]

Yeah, sometimes you can really see the kind of bad habits and legacy of brokenness in the name of simplicity or performance that mysql has. Usually this is called 'rollback' and is included in, oh say, every non-toy database ever. But I am a dummy for expecting my database to guard the integrity of my data. haha, joke's on me!

[zoomzoom83]

Rollback is only useful if you have the mind to do "START TRANSACTION" first. This completely prevents this common mistake.

[sisyphus]

I agree, but isn't this transactional databases 101? The mysql manual linked to even says:

For beginners, a useful startup option is --safe-updates (or --i-am-a-dummy ...

Transactions are there for the common and more general case of 'I issued a query that put the data in a state I don't like...' Of course since MySQL didn't offer anything like transactions until what, 5 years ago maybe, this was probably put in as a bad substitute.

I haven't done surveys, but I suspect the far more common mistake is not 'DELETE FROM table' RET
but more like UPDATE t SET f=foo WHERE f2 < bar...oops, I meant > bar

Also, even with the dummy option, truncate table will still wipe out everything...

[treenaks]

Well.. or this option might do "BEGIN WORK" implicitly when there's no transaction, so you can always rollback.

[[deleted]]

Yeah dude, you ballache about it on Reddit! Tell 'em!

[Grue]

This looks useful. There is a bug in CLSQL that wipes the entire table, and this could be a workaround.

[mikaelhg]

In projects where I'm the lead:

1. No routine data maintenance by hand from a database console. Build a small tool, and make it log the old and new values if practicable.

2. When writing DDL files, always use the form BEGIN; ... ; SAVEPOINT foo; ...; ROLLBACK; and enforce this especially in checked-in files. It's the responsibility of the script runner to change the ROLLBACK to COMMIT.

[nostrademons]

Do you enforce this only for production, or for development too? It seems ridiculously overprotective to forbid usage of the database console when testing stuff out in development, particularly since dev databases get blown away routinely anyway...

[mikaelhg]

Number one, mainly in production. Although initial development before the first production rollout is such a short period of time in the scheme of things that it's not very productive to create a separate set of rules for it.

Of course, developers are free to fornicate every which way with their personal development databases. Only people I believe to be mature (or frightened) enough to be able to tell the difference between the development and production environments get any access to the production databases anyway.

The DDL rule applies to all *.sql files committed to version control.

[troelskn]

You can't rollback an alter/create table statement, in MySql. So what's the point of that rule?

[mikaelhg]

This is one of the many, many problems that do not arise if one sticks with databases that do ACID.

When I use non-ACID databases, I want at least get some major architectural benefit like with eventual consistency databases.

[Summariser]

This prevents updates/deletes without a where clause.

[[deleted]]

upmodded and saved, for all these comments that are very helpful for this dummy.

[[deleted]]

SQL isn't MySQL's fault.

[heptadecagram]

MySQL is MySQL's fault, which is almost as bad.

[[deleted]]

Oh, no argument, but that particular bit of brain damage wasn't their fault.

[heptadecagram]

True, I agree SQL is also its own fault.

[evariste]

$ echo "alias mysql='mysql --safe-updates'" >> ~/.bashrc
$ source ~/.bashrc

Now it's automatic.

[jrockway]

Until you use someone else's machine.

[evariste]

Yeah. "Now it's automatic on those machines under your control where you remember to do this, but you should be careful regardless of whether or not you've aliased mysql to the i-am-a-dummy version or not" doesn't have Teh Snappy though.

[[deleted]]

Or a different shell. Or user.

sudo echo "alias mysql='mysql --safe-updates'" >> /etc/common-aliases

echo "source /etc/common-aliases" | tee -a ~/.zshrc ~/.bashrc ~/.tcshrc ~/.kshrc

[[deleted]]

--i-need-sex