So what happened to Sid? Fired, quit, censured, or did he hide in the server room for 3 years living off condensation and the flesh of junior techies who wandered haplessly to their deaths?
If the thing you were doing earlier is 'drop table' commands, ctrl-c, ctrl-v is not your friend
Are you hoping to make it through the week without a major balls-up? El Reg's fingers are crossed for you – but remember, not everyone is that lucky. On the plus side, that does provide us – and you – with the fodder we need for our Who, Me? column. Our weekly trip down the memory lanes people would probably rather forget is …
COMMENTS
-
Tuesday 7th May 2019 07:21 GMT Michael H.F. Wilkinson
"I won't even have to take a backup of the database"
Just thinking that will immediately draw the attention of the God of Hubris, who will instantly think of ways to make your day (or even decade) thoroughly miserable.
Never even think of saying that. As Lu Tse says: "There is no such thing as too many backups"
-
Tuesday 7th May 2019 11:01 GMT m0rt
Re: "I won't even have to take a backup of the database"
Actually, that quote from Lu Tse, when not paraphrased, was:
"There is no such thing as too many backups as long as you removed the incriminating evidence...anyway whose round is it as it can't be mine as I was giving consultancy advice and that doesn't come cheap, and there had better be salted nuts too..."
He was a wise old bird.
Too wise to not quote him fully
-
-
-
Tuesday 7th May 2019 09:03 GMT Anonymous Coward
Transactions are one of the many SQL/RDMS-things that either didn't (maybe still doesn't?) work in MySQL at the time when people used MySQL for Everything, because "Fast SELECT" or "FOSS".
My own experiences with MySQL tells me that ROLLBACK would run without any errors, but, still silently trash *something*, making the database crash after the long while it takes for the server to run out of disk space, memory or both, depending on how the abomination that is "Java.Util.Logging" was misconfigured this time.
-
Tuesday 7th May 2019 18:05 GMT JLV
You know, in an extremely rare case of cross pollination java.util.logging was ported to the Python standard library. In theory it does everything and makes coffee too.
In practice, I have found it one of the rare corners of Python where I just can’t seem to grasp how it all ties together. Change one setting somewhere to log errors to email and now console logging stops in another module. It’s a big ball of nested factories, strategies and dependency injections. Typical Java enterprisey approach to stuff. It’s not so much the language as the deliberate architecting for complexity.
I know I should just sit down start with a simple case and play with settings while building up complexity. Life’s too short so I mostly put up with larger logfiles.
And, face it, using Mysql for anything else than a basic CRUD system (where it shines) is almost as bad as corporate “devs” thinking Access is suitable for real business systems because they are too lazy to do grunt dba work. Postgres is just as free and has a much richer, very stable, feature set.
-
-
-
-
Tuesday 7th May 2019 08:45 GMT Chloe Cresswell
When ever I have something powerful in my clipboard, when I've used it, I'll put it into a text file if I think I'll need it again.
Then I copy a single space to the clipboard.
I know how accident prone I am with mouse pasting, and if it's dangerous, I need to get it out of the clipboard before I fsck it up ;)
-
Tuesday 7th May 2019 12:50 GMT vtcodger
An alternative to Ctrl-c
For years I've loaded the clipboard by marking the stuff I want there, cutting it to the clipboard (shift-del) then pasting it back (shift-ins). I'm pretty sure that I developed that because ctrl-C could do horrible things in MSDOS/Unix if the system state isn't what one believes it to be. Very rarely, the cut fails presumably because I've pressed a wrong key. In which case, I KNOW the clipboard didn't load properly because the stuff I want in the clipboard is still on my screen after the cut.
Would that practice have prevented this incident? Almost for sure. Is it always a good idea? I'm not so sure about that. There may be scenarios where something truly awful can happen. I haven't encountered one, but that doesn't mean they don't exist.
-
-
Tuesday 7th May 2019 13:36 GMT juice
Re: An alternative to Ctrl-c
> Cut instead of Copy doesn't work if the thing you are copying from is copyable but not editable (like a web page).
Sad but true.
Equally, copy-pasta on Linux can be a bit confusing. At least on a fairly vanilla Ubuntu install:
First, in a console/terminal, crtl-C traditionally means "send a STOP request". Instead, you have to use crtl-shift-C and ctrl-shift-V to copy and/or paste.
Secondly, most applications and web browsers (e.g. Chrome, Google Docs) support the use of crtl-C and crtl-V to copy and paste
Thirdly, you can also implicitly copy-paste things by selecting text in the terminal with your mouse and clicking the middle mouse button
Fourthly, the middle mouse button will also paste whatever's been selected by pressing crtl-C
This can lead to occasional fun, as you copy something from a web page[*] and try to paste it into a terminal via the mouse, only to accidentally click-select some text in the terminal. Which then replaces whatever's in the clipboard with the text from the terminal, which may well include a newline or two.
Hey presto: if you're not paying attention, the instant execution of whatever random text you copy-pasta'd. Best hope it's not anything too destructive...
[*] Hello, stackoverflow!
-
Tuesday 7th May 2019 16:55 GMT vtcodger
Re: An alternative to Ctrl-c
Equally, copy-pasta on Linux can be a bit confusing. At least on a fairly vanilla Ubuntu install:
FWIW, Unix (actually X11) supports THREE different clipboards that no one can keep straight. They are called PRIMARY, SECONDARY and CLIPBOARD and each has different conventions. On top of which, Unix by default uses a "lazy" clipboard model where "copy" just claims ownership of the clipboard and no data is actually transferred unless/until a paste is requested. No wonder it's confusing
-
-
-
-
-
Tuesday 7th May 2019 09:01 GMT Anonymous Coward
Playing with "begin tran" doesn't help much if what's in the clipboard includes a commit or rollback :-)
I have to say that while I come from a CLI background I tend to either paste into a text file first and then execute that or into a GUI window which won't do anything until I click "execute".
Not that I'm claiming to be "holier than thou" but more that once bitten......
-
Tuesday 7th May 2019 11:56 GMT Tom Paine
+1 for a text editor, but can I get a shout out for always starting a line containing potentially dangeous commands with a couple of '#' characters.
One of the grizzled old veteran Unix admins who learned me a lot of what I think I know about it had a slightly annoying (at first) habit that after typing any command into a terminal, it took his hands off the keyboard and read it back through a couple of times before hitting RETURN. Saved a lot more time than it cost in the long run though.
-
Wednesday 8th May 2019 09:23 GMT Down not across
+1 for a text editor, but can I get a shout out for always starting a line containing potentially dangeous commands with a couple of '#' characters.
Since this was SQL, you would need to use couple of '-' instead of '#'. Principle is sound however, as it at least gives you one more chance to check.
-
-
Tuesday 7th May 2019 10:15 GMT juice
Wouldn't have helped here
DROP TABLE is a DDL[*] command, so can't be rolled back.
And before anyone goes snarking on MySQL (which is usually a valid target TBF), it's the same in Oracle. To quote asktom (https://asktom.oracle.com/pls/apex/asktom.search?tag=ddl-rollback):
> Oracle Database issues an implicit commit before and after any DDL statement ... this means even if the DDL fails, the preceding DML is still committed
So you can end up with a right mess if you attempt to combine DDL and DML in a single transaction which has a failure partway through.
Interestingly, SQL Server appears to support transactional DDL commands. Dunno about Postgres, since it's virtual Monday and I've pretty much run out of energy and caffeine...
[*]DDL: Data Definition Language - essentially, commands which control the structure which holds the data. Unlike DML (Data Manipulation Language) which is used to query and/or change the data held within the structure
-
Wednesday 8th May 2019 09:26 GMT Down not across
Re: Wouldn't have helped here
DROP TABLE is a DDL[*] command, so can't be rolled back.
And before anyone goes snarking on MySQL (which is usually a valid target TBF), it's the same in Oracle. To quote asktom (https://asktom.oracle.com/pls/apex/asktom.search?tag=ddl-rollback):
> Oracle Database issues an implicit commit before and after any DDL statement ... this means even if the DDL fails, the preceding DML is still committed
Recent versions of Oracle database might (depending on size of the table, amount of activity and the instance configuration) give you a way out with flashback to either effectively undo the drop, or query into a new table that you can then rename to the dropped table.
-
Tuesday 7th May 2019 13:34 GMT Richard Crossley
Not all RDBMS are the same
Some don't allow DDL inside a transaction.
Some commit the transaction once the DDL has been executed.
Probably best to have multiple levels of access:
1) Read only for "inspecting the data"
2) Read-Write for "modifying the data"
3) Read-Write-Destroy* for "altering the schema"
* Other adjectives are available, but given the nature of the story it seemed appropriate.
Just a thought, were the transaction logs available?
Was point-in-time recover available as an option?
-
Tuesday 7th May 2019 13:49 GMT Anonymous Coward
Always ALWAYS ALWAYS type START TRANSACTION before anything else on an interactive database shell console.
Always ALWAYS ALWAYS check you have not messed up before typing COMMIT.
All these years reading BOFH and they didn't pick up a *thing*. Sigh.
Obviously you start with Always ALWAYS ALWAYS making sure you have someone else to blame before you hack away at production systems. It's also much quicker than making a backup :)
-
-
Tuesday 7th May 2019 07:30 GMT Anonymous Coward
The CLI is not your friend, in such situations...
I prefer by far using GUI database tools which doesn't execute commands as soon as you past them because there are some CR and/or LF inside - and let me review everything before pressing the "run" button. CLI are OK to execute previously tested scripts for installs/upgrades, but require an extra layer of care when attempting to modify a database.
-
-
Tuesday 7th May 2019 11:41 GMT Robert Carnegie
Re: The CLI is not your friend, in such situations...
I have to use several different terminal emulators (...apparently) which have an inspiring array of different responses to copy-paste keystrokes or left and right mouse clicks. Such as right click = paste immediately. If you're expecting to drag-select text and then right-click to copy, the immediate paste comes as a disappointment.
If I overlooked someone mentioning "Write your destructive code so that it includes testing the condition that it is running where you intended it to run, before performing the destruction" - i.e. test server name, directory name, etc - then, excuse me. And anyway it's usually too much trouble, unless you can automate that.
-
Tuesday 7th May 2019 14:14 GMT Doctor Syntax
Re: The CLI is not your friend, in such situations...
"Write your destructive code so that it includes testing the condition that it is running where you intended it to run, before performing the destruction"
Which is another reason for doing it in a script. First write a SELECT* using the WHERE and HAVING clauses and run that. Only once your sure it returned what you expected do you edit that to the UPDATE or DELETE you intended. With a BEGIN at the front, of course.
*Just getting a count might be sufficient.
-
-
-
Tuesday 7th May 2019 12:39 GMT Charlie Clark
Re: The CLI is not your friend, in such situations...
There are lots of situations where you might not be able to use a GUI. The alarm bells should start ringing here with:
ssh production.system
Firstly, this kind of action on a production system should always go through some kind of tested code. It says there was more or less no admin interface, which tells us a lot about the company and the team. In any case, it does not take long to knock up the relevant SQL for this kind of thing and test it a bit.Secondly, with different users you can prevent the data update user from trashing the structure by not giving it the permissions to do so.
But I'm not going to pretend I'm perfect and I that I haven't made mistakes even production systems.
-
Tuesday 7th May 2019 12:59 GMT disgruntled yank
Re: The CLI is not your friend, in such situations...
Oh?
Years ago, some co-workers found an important table repeatedly disappearing. I suspected a big green button on the TOAD GUI, though they swore that no, they weren't hitting it. I ended up writing a database trigger that would raise an error if one tried to drop certain tables.
-
Wednesday 8th May 2019 09:31 GMT Down not across
Re: The CLI is not your friend, in such situations...
I prefer by far using GUI database tools which doesn't execute commands as soon as you past them because there are some CR and/or LF inside - and let me review everything before pressing the "run" button.
Most databases don't execute on CR/LF, but require an end of statement like ';'. '/' or 'go' or something similar.
-
-
Tuesday 7th May 2019 08:24 GMT imanidiot
Not an IT guy but..
Wouldn't it have been safer to make a small script that did this modification offline first, then run this? Also useful for future modifications. The only time you should be directly working on a live system (any sort, be it IT or something more hammer-able) is if there is absolutely no other way to prepare the work beforehand.
-
Tuesday 7th May 2019 08:37 GMT Anonymous Coward
Re: Not an IT guy but..
The easiest way is to use a transaction, which prevents writing data until you're satisfied. Eg in postgresql:
BEGIN; -- starts the transaction
DROP TABLE all_the_things; -- oops, that's not what I meant
ROLLBACK; -- phew, I'm in a transaction ( or COMMIT if you didn't screw up )
Of course a script would be a more permanent solution, but sometimes you just have to run SQL on the production server.
Another solution is to write the query to a text file on the server, then: psql < file.sql
-
Tuesday 7th May 2019 08:55 GMT Doctor Syntax
Re: Not an IT guy but..
"Of course a script would be a more permanent solution, but sometimes you just have to run SQL on the production server."
In which case you do it in a script. It doesn't have to be a permanently saved. Your script starts with BEGIN TRANSACTION. You run the script then - and only then - your type your COMMIT.
It's a production database, belt and braces are not amiss.
-
Tuesday 7th May 2019 09:05 GMT veti
Re: Not an IT guy but..
Yes, but copying and pasting too much of your offline console would screw you up anyway. Even with a transaction.
What boggles my mind is the thought of a console that executes commands the moment they're entered, without waiting for the user to click 'run' or press F5 or whatever.
-
Tuesday 7th May 2019 11:24 GMT Kubla Cant
Re: Not an IT guy but..
What boggles my mind is the thought of a console that executes commands the moment they're entered, without waiting for the user to click 'run' or press F5 or whatever.
That's what consoles do. If it's a console rather than some kind of GUI there won't be anything to click, and function keys probably won't do anything. This is so you can use them in a plain-vanilla terminal.
That said, some databases distinguish between the statement terminator (usually ';') and the action command (e.g. '/' at the start of a line in the appalling SQL*Plus or, IIRC, '/g' in MySQL). But that wouldn't help in the present case as the paste buffer would probably contain the action commands too.
-
-
Tuesday 7th May 2019 08:47 GMT Anonymous Coward
It's all gone a bit Pete Tong!
Did a similar thing with PIX firewalls many many moons ago which left me imagining my career disappear before my eyes..
I had only been with the company a few week and was working on "baby's first change" back in the days when change control was but a gleam in the CIO's eye. I'd been familiarising myself with the company firewalls that day when the clock ticked 6pm.. One change and it was beer o clock.. Yippee!!
I started the "it's only a one line change" beloved of coders and network engineers alike:
* Prepped the change in notepad as you did (It WAS a while ago), CHECK
* Past 18:00 ?? CHECK
* Are you logged into the prod firewall? CHECK
* conf t CHECK
* Ctrl-C the change and Ctrl-V into putty ERMM
Shit me, that's a lot of text.. Then the sickening realisation dawned (or more accurately smacked me over the head) that I'd fat fingered the Ctrl-C and pasted the interface and routing portion of the office PIX on top of the prod firewall, which promptly displayed its disgust by dropping offline, along with the whole datacentre !
There was I sitting in the office on the other side of the city from the DC with that sickly grin plastered across my face which only emerges when you've done something terminally stupid. My boss saw my face turn bright red roughly the same time our NMS did, and rushed over to my desk expecting the worst. He wasn't to be disappointed.
As I stuttered out what I thought I'd done, with the expectation I'd have to jump on the motorbike and dash through the rush hour traffic to the DC he told me we had someone on-site working in one of the patching cabs and to ring them asap. A couple of minutes later and after convincing him that no, I wasn't insane and yes, I really did want him to powercycle the main firewall cluster he pulled the plug and the longest five minutes pix reboot cycle of my life started.
Once we saw the firewall come back up and SNMPc took on it usual green hue, my boss hovered as I waited for my heart rate to subside and then made me do the original change again (correctly this time) with him looking over my shoulder.
Guess who paid for the sherbets that night down the local ?
The takeaway from that was to paste what you'd just copied back on top of itself before you pasted onto a live device. You should just see the text select disappear and that was the confirmation that there was nothing job threatening lurking in the paste buffer. Oh, and also to make sure you worked for a human being who realised that occasionally people fuck up, and all we could realistically do was to learn from said fuckups so we never made the same one again !
I've done a few other fuckups since then (all in the same company, they must be masochists) but that was the closest I've come to ever shitting myself that didn't involve a skinful or a dodgy ruby.
-
Tuesday 7th May 2019 11:02 GMT tip pc
Re: It's all gone a bit Pete Tong!
reload in 10 and also paste into a different notepad and compare.
i really don't know why cisco don't do something like junipers commit confirmed where it auto rolls back after a few mins if you've not confirmed your happy with the change. certainly quicker than waiting for a reload.
-
Tuesday 7th May 2019 16:47 GMT bmhughes
Re: It's all gone a bit Pete Tong!
https://www.cisco.com/c/en/us/td/docs/ios-xml/ios/config-mgmt/configuration/15-sy/config-mgmt-15-sy-book/cm-config-rollback.html
https://packetpushers.net/cisco-configuration-archive-rollback-using-revert-instead-of-reload/
It's been in there since 12.2.somthing.
-
-
Tuesday 7th May 2019 08:56 GMT HobartTas
Limited vocabulary?
One thing I hate is to destroy a ZFS snapshot the command is 'zfs destroy tank@snapname" whereas to destroy the pool itself is "zpool destroy tank" so it could be really easy to make a very bad mistake given the pool one is immediate without any warnings issued and I don't know if you included the snapshot name to the zpool command whether or not it would still work but probably not because the syntax wouldn't be correct but you can just imaging some bad cut and paste job could potentially be executed by accident, I have no idea why the original ZFS authors didn't chose a synonym for one of them instead and I probably would have preferred changing the pool one to something like "obliterate" given the level of damage it could do, surely the American ZFS designers have access to the full range of words in the English language much as anyone else does.
-
Tuesday 7th May 2019 09:42 GMT NATTtrash
Shortcut curious...
I notice a lot of people around me use CTRL+V and CTRL+C. Then again, my muscle memory always seem to go towards CTRL+INS SHIFT+INS. Had a discussion about that once (in the pub, where else) with an admin who was positive that the short cut people use are an indication of their "computer birth", a result of the system they first started on. Now, I started on VAX terminal, nothing fancy, just WordStar then, but for the love of God can't remember whether I picked it up there. Then again, it makes you wonder... I think we came to a conclusion, but can't remember any more what it was (I did say it was in the pub, right?)
-
Tuesday 7th May 2019 10:15 GMT SWCD
Re: Shortcut curious...
Don't mind a bit of CTRL/SHIFT-INS - and there are certain places a CTRL-V won't work for paste, but a SHIFT-INS does (Can't think of an example off the top of my head, the odd password box I come across though) CTRL-C/V now strongly ingrained, just easier as my left hand is over that way anyway while the right one is on the mouse :-)
Many will remember another "computer birth" indicator - how you'd reply to an email or a Usenet post. For years I'd dutifully bottom-post, ignoring those newer to the medium who'd ask why and that it made no sense. I can't remember the last time I exchanged an email with a bottom-poster now.. Top-posting won, largely due to the default reply setting on Outlook! :-)
-
Tuesday 7th May 2019 12:50 GMT Charlie Clark
Re: Shortcut curious...
I still always quote and post: who wants the punchline before the joke?
Interestingly, some colleagues are increasingly top-posting in Outlook but announcing at the top that they're answering inline. I think this has something to do with the fact that you can really quote and post properly in Outlook.
-
Tuesday 7th May 2019 13:53 GMT Richard Crossley
Re: Shortcut curious...
CTRL-DEL, CTRL-INS and SHIFT-INS was IBM's Common User Access
https://en.wikipedia.org/wiki/IBM_Common_User_Access
Yes I still use it.
My birth computer was "use the cursor keys to find the text on the screen you want to copy on to the command line and press the COPY button to the copy the characters and edit as necessary"
-
Tuesday 7th May 2019 12:47 GMT Charlie Clark
Re: Shortcut curious...
DOS and OS/2 used CTRL+INS and SHIFT+INS – that's what the DEL and INS keys are on the keyboard for. The DOS box in Windows supported them for a while then MS disabled them and made us do everything via the menu. Reasonably pleased to see in Windows 10 that keyboard shortcuts (of whatever flavour) have returned.
I also seem to recall there was quite a hoohah when Microsoft decided to repurpose CTRL + C for copying when they brought Windows out.
-
-
Tuesday 7th May 2019 10:28 GMT DJ Smiley
Just last week (thank god for 3 day weekend allowing me to slightly forget) I was debugging a quick script I'd be writing up, which kept choking on a password I was pasting in (yes, horrible copying and pasting passwords but 'reasons').
In the end I sighed, gave in and started to echo back what I was pasting in. It seems for some odd reason, it was only actually being received successfully 1/3rd of the times, the other times it only ever showed the line ending. Very weird, but satisfying to find once the proper solution (i..e no more copy pasting passwords) that it'd work correctly, all the time.
-
-
Tuesday 7th May 2019 12:29 GMT mdubash
Occasionally still see it on browser password fields, believe it or not. Keepass (my preferred password manager) types in passwords as fast as it can normally, but for one or two sites, I haveto insert a delay between characters or the (presumably) validaiton routine throws a wobbly. In this day n age too...
-
-
-
Tuesday 7th May 2019 11:12 GMT Anonymous Coward
Paste Back On Top
The comments here almost amount to a guide to database administration, which is a tribute to those contributing.
One pearl, (due to AC/Pete Tong) with wide applicability is to paste what you (think you've) just copied back on top of itself before trying to paste it elsewhere. If necessary, hit Ctrl-Z and try again.
This turned into a habit for me whilst using an aging keyboard, which I was too lazy to replace. Eventually I did replace it but the habit had taken root and proved itself useful, along with (eventually) developing a perpetual awareness of how much work I'd done since the last save.
-
-
-
Wednesday 8th May 2019 10:14 GMT magickmark
Re: Bobby Tables
To quote.....
And so Sid logged into the database cluster over SSH, fired up the database client and started typing the update command. He copied the chap's email and pasted it into the console.
"To my horror a huge amount of text appeared and the admin console dutifully executed it."
As it happened, just before he was asked to update the email, Sid had been working on the main database creation scripts for the staging server.
"My clipboard was full of 'drop table' commands."
For some reason, Sid's attempt to copy the address had failed and he had, of course, pasted the drop table commands directly into the live database
-
-
This post has been deleted by its author
-
Tuesday 7th May 2019 15:39 GMT Lee D
1) "We don't need no steenking backups!" - Sacked. Get out.
2) Log in as full privilege user to update a minor database table. Sacked. Get out.
3) Doesn't even bother to do it in a transaction that you could, probably, roll back. Sacked. Get out.
4) If pasting to a remote SSH session, doesn't just edit a file and paste into there (because if that string had anything in it, you could have ended up quitting out of the SQL shell and executing all kinds of stuff! It's easy to paste something with a handful of line returns, but quite hard to paste something that'll Ctrl-X you out of an editor, though). Sacked. Get out.
5) Executing SQL live on a production system underneath the application without prior testing on the staging system he admits he was working on! Sacked. Get out.
The excuse of "Oh this was before the days..." ... so pathetic. What you going to do? Roll back an in-production social network main database server via a Hyper-V checkpoint and "just forget" about all the transactions that occurred on it after the checkpoint but before it was rolled back?
Seriously, this is just people who have a habit of working dangerously around production systems and think it's funny. I use everything I can to make sure I don't mess up - checkpoints to backups to replicas to saving the table contents first to every privilege separation possible to literal-typing (prone to errors but you double/triple-check) to safety-hash (Putting a # before any dangerous command that you're tinkering with so you don't automatically execute it... SQL equivalent is "SELECT" until you're happy and THEN "UPDATE"), to literally never deleting a file in my professional life (rename / move it out of the way, sure, but why delete?). And sometimes there are still gulp moments.
These kinds of people are exactly why I do tight permissioning and delegating on admin functions, filesystems, table and database security, etc. My technicians have always hated it (having to ask to do certain things, and only getting a permanent permission granted once they'd proved themselves and acquired the same habits) right up to the moment that it saves their backside ("Thank God, I only had write permissions to that one table!").
-
Tuesday 7th May 2019 19:53 GMT matthewdjb
One weekend we were doing a system upgrade. I was told I'd be called Saturday to do my task; dropping an index of the general ledger, running some task, reinstating the index.
I didn't get the call.
Sunday at about 8pm, and after a couple of bottles of wine, the call came. I explained the deal was that the call me Saturday. Today is Sunday and I'm pissed.
I think the guy at the other end of phone was maybe a yank, as he persuaded me, eventually, to do my part.
I came within a "confirm y/n" of dropping the table rather than the index..