Navicat WTF



  • So I get tasked to write a quick SELECT query on the production database. The query will have to make use of one of the several custom functions included in the database schema. No problemo.

    I fire up my trusty $800 worth navicat premium and dig into the database to find the function signature I'll need to call.

    Hmm, let's see, which one is it... is it this one?

    Oops! I guess you have to be careful when clicking on these names, because navicat will for some reason offer to rename EVERYTHING you touch. As if that's the default behavior you want when dealing with databases (WTF #1, we'll get back to this).

    So, I'll just click out of dangerous edit field and cancel the edits.

    There! And that's the function I need. Great!

    I write the query, email the results and move on wit-

    -skype blares

    WTF DID YOU DO WITH PRODUCTION!?

    "Me? I.... nothing... wha?"

    Everything is broken. Clients are whining. Boss is yelling. WHAT IS HAPPENING!?

    I trace the problem to a missing database function on which everything depends.

    But how!? I can see it in navicat! The function is right there! ... wait a minute....

    WHAT THE FREAKING FUCK!?

    And in details view:

    So let's count WTF-s

    1. Navicat "helpfully" offers to rename everything you click in a database. For those not database inclined, let's just say you should VERY rarely need to do something like that, verging on FUCKING NEVER.

    2. It then doesn't have any special interface for change confirmation once you're done editing. You would expect there to be little "Check" and "X" buttons and "Are you sure?" dialog. But no. My guess is they just handle "onLostFocus", check if what you've typed differs from the old name and if yes, fire up A FREAKING ALTER WHATEVER query.

    3. And after all that, THEY HAVE THE GALL TO SCREW IT UP! Apparently, instead of just the function name, the entire function signature enters the edit box. So when you click OK... excuse me, when you exit focus, the function is renamed to its old name plus the signature. No warnings. No confirmation boxes. Just a silent fucking ALTER query that flies out of navicat AND FUCKS YOU RIGHT IN THE ASS. Fucking unbelievable.

    What a freaking amateur hour in an otherwise pretty good product.



  • @cartman82 said:

    cancel != cancel

    Oh man. It's shadow over innsmouth again.

    Usually I prefer political middle-management stories about companies that limp along in spite of being an abomination unto Charles Darwin. This technical WTF, however, is so siren-blaringly terrifying that I am fascinated by it. It redefines the old phrase "so bad it's good" and holds it to a transcendental standard.

    For a moment I thought this had reminded me of a story, but I clearly don't remember it right now. Maybe the healing has begun.

    Can you connect navicat to the database by a user without ALTER permissions? This is obviously only helpful if you only use navicat to read the database, unless you fetch a priest and give them ALTAR permissions.



  • Sorry, but I don't see what changed.

    Saw it! Shouldn't that fail as bad syntax?



  • @Eldelshell said:

    Saw it! Shouldn't that fail as bad syntax?

    In Postgres, if you place it under double quotes, you can have all sorts of stuff in object names.



  • @Shoreline said:

    Can you connect navicat to the database by a user without ALTER permissions? This is obviously only helpful if you only use navicat to read the database, unless you fetch a priest and give them ALTAR permissions.

    Yeah, I suppose I can have gimped user, but who's gonna think to switch to that in the middle of a busy day?

    Better to NEVER EVER click on function names.



  • @cartman82 said:

    Yeah, I suppose I can have gimped user, but who's gonna think to switch to that in the middle of a busy day?

    For logging into production? Doesn't that override your busy day?



  • @boomzilla said:

    For logging into production? Doesn't that override your busy day?

    Not really. If I was hanging around the server all the time and regularly running scripts (multiple SQL queries open), maybe I'd consider it. If you're just in and out, with a specific SELECT query, there's no much danger.

    Unless the software stabs you in the back.



  • @cartman82 said:

    Unless the software stabs you in the back.

    I maintain that you guys are stabbing yourselves with sloppy access control of your production data. Not that my group isn't guilty of this. You were on the wrong side of the hatch, dude.

    Also, not that that isn't a shitty UI. Though I'm surprised that this is the first place to bite you.



  • Ssms does the click and rename stuff too, use escape.

    Also use a read only account if it's not your intent to change data, you are the wtf here, along with Navi.



  • @Matches said:

    Ssms does the click and rename stuff too, use escape.

    No it doesn't. You have to click, then either wait for the double-click timeout (times two, IIRC) and click again, or hit Enter, to get into rename mode. (You know, like EVERY FUCKING GUI APP IS FUCKING SUPPOSED TO FUCKING WORK! It always amazes me when apps get super-basic shit like this wrong. It ain't fucking hard.)



  • @boomzilla said:

    Also, not that that isn't a shitty UI. Though I'm surprised that this is the first place to bite you.

    The second. The first time it was an out-of-the-way function, I spent a hour trying to figure out what has happened, thinking there's a rogue script or database corruption or something.

    @Matches said:

    Ssms does the click and rename stuff too, use escape.

    Database GUI-s should ease off with that shit. It just creates problems and encourages bad practices.

    @Matches said:

    Also use a read only account if it's not your intent to change data, you are the wtf here, along with Navi.

    Yeah, yeah, I know, mommy. The fact that I only wanted SELECT is incidental here. I could have actually needed an admin account, and the shitty software would have fucked me just the same.



  • @blakeyrat said:

    No it doesn't. You have to click, then either wait for the double-click timeout (times two, IIRC) and click again, or hit Enter, to get into rename mode. (You know, like EVERY FUCKING GUI APP IS FUCKING SUPPOSED TO FUCKING WORK! It always amazes me when apps get super-basic shit like this wrong. It ain't fucking hard.)

    That's still shit. Too easy to get into edit mode as you're clicking around. IMO they should leave it as the context-menu action at best.



  • @cartman82 said:

    IMO they should leave it as the context-menu action at best.

    Better to make you write the SQL so you can store it in source control.



  • @boomzilla said:

    Better to make you write the SQL so you can store it in source control.

    That's the point. Make it so you can do it if needed, but don't encourage it with "Hey guys, it's just like renaming files in explorer, just on a production server! Easy-peasy!"



  • @cartman82 said:

    That's still shit. Too easy to get into edit mode as you're clicking around. IMO they should leave it as the context-menu action at best.

    I disagree. The renaming method should be consistent between all apps. It's equally "dangerous" in all of them. The Undo command is all you need to "fix" an error like this.

    If you were spazzing around with the mouse so much you didn't even notice the name change, then you got bigger problems.

    @boomzilla said:

    Better to make you write the SQL so you can store it in source control.

    I design my databases graphically then have SSMS spit out the SQL for me.



  • @blakeyrat said:

    I disagree. The renaming method should be consistent between all apps.

    No it doesn't. In one paradigm, you need to rename stuff all the time, so renaming should be easily accessible. In other, you should almost never need to rename things, so renaming should be buried.

    Different activities call for different interfaces.

    @blakeyrat said:

    It's equally "dangerous" in all of them. The Undo command is all you need to "fix" an error like this.

    It's equally dangerous to rename an mp3 song in explorer, and "transaction-log" table on a production database server?

    @blakeyrat said:

    I design my databases graphically then have SSMS spit out the SQL for me.

    I was doing that as well last time I programmed on Windows, but I won't next time. IMO this is a bad practice in MS programming and it should go away. Rails' idea with migrations is the correct way forward.



  • @cartman82 said:

    Rails' idea with migrations is the correct way forward.

    No, it's bullshit.

    You gotta design your database right in the first place. If there's any component of the application you should say that about, it's the database. Rails' system gives you the impression you can just keep tweaking it as you go, which is totally crap.

    Altering the database of a live system should be so rare that it's not a huge burden to do the scripting manually, because you only do it once every 5 years or less.


  • I survived the hour long Uno hand

    Email I just got:

    On Friday, [Coworker] spotted a discrepancy between a SQL script I had placed in the Staging Queue and its counterpart he had moved to Build. Long story short, I had unintentionally changed the Staging Queue version of the script while verifying that the job had been staged correctly. I tracked this down to an autosave setting in the SQL History feature of SSMS Tools Pack:

    I have unchecked this checkbox and recommend you do likewise.



  • @blakeyrat said:

    No, it's bullshit.

    You gotta design your database right in the first place. If there's any component of the application you should say that about, it's the database. Rails' system gives you the impression you can just keep tweaking it as you go, which is totally crap.

    Altering the database of a live system should be so rare that it's not a huge burden to do the scripting manually, because you only do it once every 5 years or less.

    That's not how things work in practice, and you know it.



  • @cartman82 said:

    That's not how things work in practice, and you know it.

    Altering the DB with manual scripts should not be a big deal because you're doing stuff incrementally to go along with your code changes. But having a script and a system for dealing with them keeps you sane, just like having your application code in source control does.

    If you have a way to record all of your clicks and whatever, then that's fine, but it seems simpler to just use normal text files like everything else.



  • Just to be clear, when I say "migrations", I mean "every time you want to update, write a script and save it to source control, instead of clicking in GUI manually for each DB you want to update".



  • @cartman82 said:

    Just to be clear, when I say "migrations", I mean "every time you want to update, write a script and save it to source control, instead of clicking in GUI manually for each DB you want to update".

    That makes perfect sense to me. I wasn't sure if you were talking about having ruby auto-update your schema based on code changes or something, which I would find scary.

    Mostly I was being lazy with my quoting and just jumping in WRT blakey taboo about changing a DB schema.



  • @cartman82 said:

    That's not how things work in practice, and you know it.

    It is in systems I design. Because I am the best.



  • @blakeyrat said:

    It is in systems I design. Because I am the best.

    Or you get fired before you have to maintain the crap you make.

    Filed under: buuuuuuuuuuuuuuuuuurrrrrrrrrrnnnnn!!!!!


  • Discourse touched me in a no-no place

    @boomzilla said:

    I wasn't sure if you were talking about having ruby auto-update your schema based on code changes or something, which I would find scary.

    It would be scary as hell if it was a production DB, but not so bad if it was a development DB. (Test? Between those two extremes.)



  • @dkf said:

    It would be scary as hell if it was a production DB, but not so bad if it was a development DB. (Test? Between those two extremes.)

    OK, but now you need a mechanism to transform that into something reasonable to push up the chain (dev -> test -> prod). Easier to just do it right in the first place.



  • @boomzilla said:

    That makes perfect sense to me. I wasn't sure if you were talking about having ruby auto-update your schema based on code changes or something, which I would find scary.

    Actually, AFAIK you can have it done two ways

    1. Execute a script that generates the migration
    2. Write the migration yourself

    Either way, you end up with a little update script, except you use ruby and ActiveRecord, instead of SQL.

    Then there's a different script that you can call that figures out the current state of database and executes all needed "migrations" to bring it to the current state. Or to any previous state you desire (if you write two-way migrations, which isn't always possible).

    At least that's my limited understanding. I think there's a ruby guy or two around, so they can explain it better.



  • There are tools that let you "diff" MS SQL databases. I'm not sure I'd allow one to run on production data without a lot of testing.



  • @boomzilla said:

    Easier to just do it right in the first place.

    There's never time to do it right.

    There's always time to do it again...


    ... at least when someone else is deciding what to do with your time.

    Or, if... you are the TRWTF.



  • Re: get it "right" in the first place.

    That's not the point. The point is to be able to develop database alongside with the project and organize schema changes in a manageable way.

    Think of it this way:
    working with migrations = using source control
    working with SQL dumps = sharing code through FTP

    Except migrations aren't quite as powerful as VCS, but maybe one day.



  • I stand corrected, two clicks that are several seconds spaced apart trigger it, but a single click does not, in fact, trigger the rename.

    The double click rename allows for too many seconds to pass (3, when i tested just now) but I don't know if that's a configuration thing, a windows thing, or an ssms thing.



  • @cartman82 said:

    Database GUI-s should ease off with that shit. It just creates problems and encourages bad practices.

    No argument here. Renaming queries in your project/solution: OK

    Renaming database items: NOT OK


  • Discourse touched me in a no-no place

    @cartman82 said:

    The point is to be able to develop database alongside with the project and organize schema changes in a manageable way.

    That's reasonable for the case where a site and database evolve in concert. I suspect a lot of people here are coming from the perspective that the DB was there first and will be there after the site is long gone; it's contents are more valuable than the way in which it is accessed. In such a situation, keeping the developers way off the production DB schema is critical; at best they can suggest changes to it, nothing more.

    Deciding to magically put the parameter description into the function name on click out was a good WTF, a true moment of “what on earth were the developers thinking there?!” while still being all to easy to see the steps leading to it.


  • BINNED

    @boomzilla said:

    @cartman82 said:
    IMO they should leave it as the context-menu action at best.

    Better to make you write the SQL so you can store it in source control.

    pgAdmin does both, nearly. You can either right click -> properties, then change it in the properties window (that's why I said nearly, there's no actual "rename" menu entry), or right click -> scripts -> CREATE script and it will present you with the SQL that was used to generate the function you can play with.



  • Now if only it wasn't a buggy unreliable piece of crap, I could use it more often.

    Unfortunately, there's no really good GUI for working with PostgreSQL. SSMS had spoiled me.


  • BINNED

    @cartman82 said:

    Now if only it wasn't a buggy unreliable piece of crap, I could use it more often.

    Yeah, it does get on my nerves at times. Mostly the inability to alter a column if creating a table using the GUI (you have to create it and then ALTER it, either with a query or through GUI that DOES allow you to change it then), and I did have it crash multiple times. Now, I never lost any work due to it, but I guess most of the credit goes to postgres itself for not fucking up despite pgAdmin crashing.



  • You should check out JetBrains new SQL IDE - It's not perfect, but it's pretty nice in general.

    It's currently in beta so you have to sign up for it, but it's pretty much an automatic provisioning. The exe download doesn't appear to have any passwords or verification either.

    0xDBE



  • @Matches said:

    Ssms does the click and rename stuff too, use escape.

    Also use a read only account if it's not your intent to change data, you are the wtf here, along with Navi.

    Was just about to say that.....I've done that a few times in non-prod environments.



  • @cartman82 said:

    Either way, you end up with a little update script, except you use ruby and ActiveRecord, instead of SQL.

    Then there's a different script that you can call that figures out the current state of database and executes all needed "migrations" to bring it to the current state. Or to any previous state you desire (if you write two-way migrations, which isn't always possible).

    At least that's my limited understanding. I think there's a ruby guy or two around, so they can explain it better.

    Yeah, this is pretty correct. Here's some examples:

    The "Display username" was later changed into the "Full name" that you all have filled out.

    An example of creating a new table

    An example of raw SQL, which is mandated for all data movement



  • @cartman82 said:

    Rails' idea with migrations everything ever is the correct way forward. worst of the worst

    Post can't be empty. My post can consist entirely of a shitty meme-image, but not a modified quote, apparently.


  • Discourse touched me in a no-no place

    <!--but comments work!-->


  • SockDev

    @dkf said:

    <!--but comments work!-->

    indeed they do.



  • <even malformed html!



  • handi-capable-ist !!


  • SockDev

    @boomzilla said:

    <even malformed html!

    i've abused that myself.

    @zoidberg (and by extension @sockbot) uses the html comments method to mark his posts



  • Yes master Accalia Fairyfox, I shall appear as summoned.

    <!-- Posted by SockBot 0.13.0 "Devious Daine" on Wed Nov 05 2014 08:37:13 GMT-0500 (EST)-->


  • Hooray! A happy ending for the rich people.

    <!-- Posted by SockBot 0.13.0 "Devious Daine" on Wed Nov 05 2014 08:37:19 GMT-0500 (EST)-->

  • BINNED

    Oh, so they work. But discoursebot doesn't.

    Days since last @discoursebot bug: 0


  • SockDev

    i don't run discoursebot.

    i run sockbot, zoidberg, translator, me, and sockpuppet.



  • @Zoidberg said:

    Hooray! A happy ending for the rich people.

    <!-- Posted by SockBot 0.13.0 "Devious Daine" on Wed Nov 05 2014 08:37:19 GMT-0500 (EST)-->

    First world problem thread is :arrows_counterclockwise: that way


Log in to reply
 

Looks like your connection to What the Daily WTF? was lost, please wait while we try to reconnect.