« first day (3618 days earlier)      last day (56 days later) » 

6:34 AM
Morning
 
6:45 AM
Morning
 
 
2 hours later…
8:26 AM
Morning
 
Morning all - Dia dhaoibh go léir!
 
 
5 hours later…
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!
 
2:32 PM
good morning
only 60 unread emails after being out of the office for three working days
 
3:30 PM
3 non-automated emails, all to "all company alias" so I should take another week off since no one noticed me
 
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?
 
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
> 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?
 
@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?
--^
:-)
 
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.
 
no sorry. Might be an issue. Will have another bash.
Wasn't executing without ;
Will try at work tomorrow
 
 
2 hours later…
10:20 PM
0
Q: Restoring Datawarehouse DB from one SQL Server to DEV & UAT on a different SQL Server

sqllover2020Currently 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
 
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?
The examples in the manual show it too.
 
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?
 
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/…
 
I see, yes it would error that batch and not attempt the rollback. Does MySQL even have TRY/CATCH. How do you trap the divide by zero without that?
 
Perhaps not in SQL proper, as MySQL does differentiate between that and procedural extension, unlike SQL Server with its Transact-SQL.
 

« first day (3618 days earlier)      last day (56 days later) »