The Heap™ – Consultancy ©®

6:34 AM

Morning

Tom V

6:45 AM

Morning

2 hours later…

McNets

8:26 AM

Morning

Vérace

Morning all - Dia dhaoibh go léir!

5 hours later…

Vérace

1:36 PM

Afternoon fellow Heapers - I answered this question using a MySQL 8.0.22 fiddle - the OP specified 5.7, so obviously my answer isn't applicable (I urged the OP to upgrade - see my answer). @Akina 's solution was the accepted one and it was good, simple and elegant.

However, my question here is - I ran the new MySQL EXPLAIN ANALYZE functionality for both solutions. My plan is a bit shorter (nomally a good thing?), but I"m not sure how to interpret the different plans. I would be grateful if anyone could (pardon the pun) explain the EXPLAIN ANALYZEs - it seems that mine is quicker - but some detailed comment would be appreciated!

I also put in a couple of extra indexes which don't appear to have changed much - hardly surprising with so few records - again, any comments appreciated!

bbaird

2:32 PM

good morning

only 60 unread emails after being out of the office for three working days

billinkc

3:30 PM

3 non-automated emails, all to "all company alias" so I should take another week off since no one noticed me

bbaird

3:57 PM

Maybe 5-6 weren't meeting cancellation notifications/corp stuff

3 hours later…

6:48 PM

Shouldn't this throw some kind of an error and not commit the transaction? Or is this yet another reason to hate MySQL?

Vérace

7:13 PM

@MaxVernon - WOW!!! I was actually starting to be impressed with MySQL's recently acquired features (generated fields, CHECK constraints and Window functions...). Allowing division by zero is a novel feature not supported by many other servers - what is it that the MySQL team know that we don't? Ignoring ROLLBACK is also not mainstream functionality - interesting!

that's kind of what I thought too.

I might ask that on Database Administrators

7:28 PM

@MaxVernon dev.mysql.com/doc/refman/5.6/en/commit.html

> By default, MySQL runs with autocommit mode enabled. This means that, when not otherwise inside a transaction, each statement is atomic, as if it were surrounded by START TRANSACTION and COMMIT. You cannot use ROLLBACK to undo the effect; however, if an error occurs during statement execution, the statement is rolled back.

yah I tried it with set autocommit = 0;, no discernible difference.

unless I'm doing that wrong too

So it might be that Jack has DBfidle running on autocommit

did you try on a pure MySQL instance?

dbfiddle.uk/…

@Johnakahot2use not yet.

I saw that, but db<>fiddle has some tweaking in the background.

You might want to ask Jack how things work regarding transactions on the db<>fiddle MySQL engine.

@Johnakahot2use cool

7:44 PM

@MaxVernon Better?

dbfiddle.uk/…

--^

:-)

apparently.

weird.

; isn't always a good idea

so it's the semi colon on the begin transaction statement messing things up. interesting.

funny thing is I was certain I tried that.

obviously not.

Q: Restoring Datawarehouse DB from one SQL Server to DEV & UAT on a different SQL Server

no sorry. Might be an issue. Will have another bash.

Wasn't executing without ;

Will try at work tomorrow

2 hours later…

billinkc

10:20 PM

Currently in our Production server there is a webservice call being made. The call works fine on PROD, but not in UAT and DEV. This is due to inconsistencies in the third party managed database. Every month, there is a excel spreadsheet we have to grab from an external source (website) that will ...

Anyone want to tilt at understanding what they are interested in or just close the sucker

mustaccio

10:40 PM

Sounds very much like an A-Z problem.

11:02 PM

@MaxVernon That's nasty behavior with ;

11:18 PM

@CadeRoux MySQL is touchy

@MaxVernon Why I don't touch it

What does it actually do, turn the BEGIN TRAN into a NOOP?

Seems like it must be some interaction with the procs?

mysqltutorial.org/mysql-transaction.aspx this shows using ;

dev.mysql.com/doc/refman/8.0/en/commit.html

The examples in the manual show it too.

Andriy M

dbfiddle.uk/…

To me it looks like the ROLLBACK inside a batch does nothing

It's only when I issue it separately that the transaction is rolled back

And autocommit doesn't seem to have any effect (and it shouldn't, according to the manual, since START TRANSACTION/BEGIN WORK should implicitly disable auto-commit).

I mean I don't think it has any effect when used before either START TRANSACTION or BEGIN WORK

So the problem is the ROLLBACK needs to be in its own batch separate from the other statements?

Andriy M

11:34 PM

Not sure. Perhaps the error in proc3 simply prevents the subsequent ROLLBACK from executing. Which makes sense. The only issue then, why the error isn't shown.

Perhaps that's how error handling works in MySQL when inside an explicit transaction? Because the error is displayed if I run CALL proc3(); outside a(n explicit) transaction: dbfiddle.uk/…