« first day (3619 days earlier)      last day (1232 days later) » 

Cade Roux
Cade Roux
12:39 AM
Does it let you COMMIT even though the third one failed?
 
 
4 hours later…
Vérace
Vérace
4:28 AM
@MaxVernon More inconsistent behaviour from MySQL - sometimes t/0 (see fiddle) throws a Division by 0 error (what I would expect), but other times it gives null - really poor stuff - If anybody asked about which F/LOSS database to choose/recommend, it was always PostgreSQL - I'll be even more enthusiastic now! Best one is this SELECT '1'/0; - result null!
 
Cade Roux
Cade Roux
4:39 AM
Is there a limit to sqlcmd input files? I just had a file from my build process throw a weird error: Incorrect syntax near 'INFORMATION_S'. And obviously it's truncated INFORMATION_SCHEMA. I took my manifest of templates and split that file into two in my build and they are working fine. The current total size of the two files is about 8.8MB, with the files being 8.3MB and .4MB now and working fine.
I am using -i obviously
-f o:65001
-I for quoted identifiers (don't remember why, but this is definitely needed)
-b for termination - I assume that's OK
-l 120 doesn't seem to be a login problem
-t 300 also doesn't seem to be a timeout of the script
Not sure why there's no input codepage, I might look at that.
I think that might be it. A unicode vertical bar has made its way in one of the component source files: ｜
 
Max Vernon
Max Vernon
@Vérace that is crazy stuff.
@CadeRoux I fairly regularly run > 20MB files through sqlcmd.exe
I can share the command-line in about 6 to 8 hours
 
Cade Roux
Cade Roux
4:54 AM
It's definitely the pipes
 
Max Vernon
Max Vernon
Ahhh cool
 
Cade Roux
Cade Roux
One of my system tests at the end of the build failed
I don't even know. I get so much weird shit pasted in from Word and Excel sometimes - I need to go through and make sure all the codepage settings on every PowerShell template and everything is UTF-8. Finding weird em-dashes and stuff like that.
Not sure why sqlcmd gets thrown that far off the end of the file for what looked like 16 occurrences of that character. Oh well. I guess I need to add i:65001 and start testing with that on the next iteration.
 
Max Vernon
Max Vernon
@CadeRoux I'm 99% certain 65001 is the codepage I'm using.
 
Cade Roux
Cade Roux
Yeah, I already had an item to review all the code pages in every step of this for the next iteration. I've had some code page issues in other parts of the system, like data dictionary generation (superscripts) and other stuff. Just never seen anything related to that fail in sqlcmd.
Although obviously I must have had a reason to put -f o:65001 already
Lol, github can't even find it at all
We couldn’t find any code matching '"-f o:65001"'
Code seems to find it, though
 
John aka hot2use
John aka hot2use
6:11 AM
Morning
@MaxVernon maybe this is part of the solution:
0
A: MySQL Transaction in Stored Procedure does not ROLLBACK

EvalsJust to keep this post up to date with the newest information: I found a solution to fix this error myself a few days/weeks ago. As it turned out, the problem was that "ROLLBACK" has to be called before "SIGNAL". If someone has a detailed explanation why that is the case, feel free to add that i...

 
John aka hot2use
John aka hot2use
6:26 AM
@AndriyM yes. That's it.
 
 
2 hours later…
Vérace
Vérace
8:12 AM
Good morning all - Dia dhaoibh go léir!
I asked this yesterday and have had no replies - I'd be grateful if anybody who has any relevant ideas/references/URLs could contribute:
19 hours ago, by Vérace
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.
TIA fellow Heapers!
 
Tom V
Tom V
8:38 AM
MySQL, sorry
 
Andriy M
Andriy M
@Vérace I'm not good at interpreting MySQL "explains" but as a general rule, I would expect a query where you hit a table once to perform better than a query where you hit the same table twice, particularly when conditions used in both queries are very similar and expected to use the same index(es).
Apart from the performance aspect, I think your two solutions are not entirely equivalent.
Matching against MAX, you can get more than one row per group, whereas with the ROW_NUMBER solution you are explicitly specifying to get one row per partition
Of course it's easy to adjust yours to be equivalent, you could either use RANK/DENSE_RANK if you want to stick with ranking functions, or you could use window MAX.
 
 
1 hour later…
Vérace
Vérace
10:10 AM
@AndriyM Thanks for that input Andriy - I am aware of the RANK/DENSE RANK Window functions and that MAX() can be used also - I implicitly assumed that a field called "id" was a PK - up to the OP to clarify if necessary. The question has now been closed - not sure why - it was a reasonable SQL question - but anyway! Thanks again...
 
Paul White
Paul White
10:42 AM
@Vérace I wasn't one of the closers, but it is a pretty low-effort question. They've not improved their question as requested either. Moreover, it is probably a duplicate of one of the many questions like this in greatest-n-per-group e.g. dba.stackexchange.com/questions/200181/…
That's not to say it shouldn't have been answered, it's just hard sometimes to know what's best to do with low-quality questions like that. I can see why people would see it as needing detail or clarity.
 
Vérace
Vérace
11:13 AM
@PaulWhite In fairness, for a new contributor, he did provide a fiddle and I can actually recall when I first was getting to grips with SQL, if somebody just simply said your problem is only a trivial "greatest-n-per-group" one, I would have thought that I'm not coming back here again. People aren't born knowing SQL and gentle pointers along with a solution are possibly the best way to tackle them - I've added to my answer pointing to the tag and encouraged the OP to check it out!
 
Paul White
Paul White
@Vérace Yes indeed. It's not terrible but several people asked them to update their question (including yourself, twice) and nothing came of it. A concerned citizen might want to make a helpful question edit for them. You never know, it might result in the question being reopened.
 
Vérace
Vérace
@PaulWhite I've tidied it up and VtR!
 
Paul White
Paul White
Cool
 
John aka hot2use
John aka hot2use
11:55 AM
@Vérace did a bit too
 
Vérace
Vérace
@Johnakahot2use - truly a work of art of which Giacometti would have been proud!
 
John aka hot2use
John aka hot2use
12:17 PM
@Vérace Giacometti was more into elongated sculptures than MySQL
;-)
Alberto Giacometti
Alberto Giacometti (UK: , US: , Italian: [alˈbɛrto dʒakoˈmetti]; 10 October 1901 – 11 January 1966) was a Swiss sculptor, painter, draftsman and printmaker. Beginning in 1922, he lived and worked mainly in Paris but regularly visited his hometown Borgonovo to see his family and work on his art. Giacometti was one of the most important sculptors of the 20th century. His work was particularly influenced by artistic styles such as Cubism and Surrealism. Philosophical questions about the human condition, as well as existential and phenomenological debates played a significant role in his work. Around...
The old 100 Swiss Francs bank note with Giacometti's sculptures
 
Vérace
Vérace
12:43 PM
@Johnakahot2use Given your residence I was looking for a Swiss artist - and Giocometti was the most famous one I could find - I simply meant that your edits had enhanced the aesthetics of the post - I'm don't know if Giacometti was a database expert in his spare time - he might have been around the original IBM mainframe days...
 
Max Vernon
Max Vernon
1:31 PM
@Vérace it's open now. Good work on the edit, btw.
 
John aka hot2use
John aka hot2use
2:26 PM
@Vérace Pulling your leg
Jean Tinguely is another great Swiss artist.
 
Vérace
Vérace
I know - that's why I continued in the same vein - somehow I doubt if Giacometti was an IBM man! :-)
 
John aka hot2use
John aka hot2use
Jean Tinguely
Jean Tinguely (22 May 1925 – 30 August 1991) was a Swiss sculptor best known for his kinetic art sculptural machines (known officially as metamechanics) that extended the Dada tradition into the later part of the 20th century. Tinguely's art satirized automation and the technological overproduction of material goods. == Life == Born in Fribourg, Tinguely grew up in Basel, but moved to France in 1952 with his first wife, Swiss artist Eva Aeppli, to pursue a career in art. He belonged to the Parisian avantgarde in the mid-twentieth century and was one of the artists who signed the New Reali...
metamechanics ; I like that
 
Vérace
Vérace
@Johnakahot2use "metamechanics ; I like that" - I'm more of an impressionist man myself - AFAIC, anything after about 1920 is derivative and/or just plain crap - the Reina Sophia museum in Madrid was full of these things that looked like children's mobiles and bits of metal on the ground...
 
John aka hot2use
John aka hot2use
3:12 PM
And not to forget H.R. Giger the designer of Alien
H. R. Giger
Hans Ruedi Giger ( GHEE-gər; German: [ˈɡiːɡər]; 5 February 1940 – 12 May 2014) was a Swiss artist best known for his airbrushed images of humans and machines connected in cold biomechanical relationships. Giger later abandoned airbrush for pastels, markers and ink. He was part of the special effects team that won an Academy Award for the visual design of Ridley Scott's 1979 sci-fi horror film Alien. His work is on permanent display at the H.R. Giger Museum in Gruyères. His style has been adapted to many forms of media, including record album covers, furniture and tattoos. == Early life == Giger...
 
Vérace
Vérace
3:42 PM
@Johnakahot2use - Alien - really liked that movie - had a thing for Sigourney Weaver for a while after seeing it! One thing you can say about Giger - his work certainly wasn't cheesy :-) - my most humble and abject apologies, but I just couldn't resist...
 
billinkc
billinkc
4:04 PM
 
mustaccio
mustaccio
Some folks seem to approve edits that really need improvement..
 
bbaird
bbaird
5:06 PM
Ok, need to write some scripts - do I use the venerable, terrible swiss army knife SAS, or swear at Python until I get the whitespace correct.
Write AND execute about 200-300 statements
 
Cade Roux
Cade Roux
PowerShell is my first goto now.
Curly brackets FTW
Python whitespace had to be an intentional joke.
I'm having to use it a little bit on my Raspberry Pi appliance project and I am one of those rare people that doesn't care about the spaces vs tabs war, but what happens in Python if things have to be indented to the same level and the tabs->spaces conversion numbers aren't teh same?
Python programmers must have to have the tab character indicators turned on in their editor?
 
bbaird
bbaird
Trying to see if I can do everything in Powershell (write SQL, SSH, execute remotely, rinse, repeat)
@CadeRoux I think everyone just uses code formatters and hopes their nested IFs don't get too out of control
 
Cade Roux
Cade Roux
I have not done SSH in Powershell. But code templating, a ton. SQL execution etc, a ton. Not had to use remote execution - they are usually transferred and executed by Team City or Octopus so it's all effectively "local" at that point.
 
bbaird
bbaird
5:22 PM
I'm stuck in a dumb analytic/dev environment without anything fancy. Might just end up doing SAS since all the data connections are present and it's really easy to drop parameters into SQL statements.
Also, other people in the organization would be able to read/execute the same code, so there's that
 
Andriy M
Andriy M
@bbaird I thought SAS had to do with British Air Force rather than Swiss Army
 
bbaird
bbaird
If only SAS was as capable as the SAS
 
 
2 hours later…
Max Vernon
Max Vernon
7:09 PM
@bbaird lol 'cuz that never happens
 
Colin 't Hart
Colin 't Hart
7:27 PM
SQL Server compatibility mode for Postgres.
 
Max Vernon
Max Vernon
7:43 PM
wow
 
Max Vernon
Max Vernon
7:57 PM
For those interested in the MySQL transaction rollback issue identified earlier, take a look at this:
0
Q: Transaction not rolling back in MySQL

Max VernonConsider the following setup, which you can tinker with at dbfiddle.uk: In the first batch we setup three simple tables, and add a row to each table: CREATE TABLE t1 (x int NOT NULL); CREATE TABLE t2 (y int NOT NULL); CREATE TABLE t3 (z int NOT NULL); INSERT INTO t1 (x) VALUES (1); INSERT INTO t...

mysql transaction
Please let me know if you feel there are any problems. I've tested it on dbfiddle.uk and on my local test MySQL 5.7 instance.
For me, the takeaway here would be to never use MySQL unless you're forced to at gunpoint. And if you do have to, make sure you get immunity from prosecution.
 
ypercubeᵀᴹ
ypercubeᵀᴹ
8:10 PM
@MaxVernon have you tested this in a local mysql installation?
 
Max Vernon
Max Vernon
@ypercubeᵀᴹ yes
 
ypercubeᵀᴹ
ypercubeᵀᴹ
and you got the same behaviour?
 
Max Vernon
Max Vernon
through SQL Workbench on 5.7.20
yes. The answer I posted shows how to make it work the way we think it should.
I think the problem really is that its hard to do error handling outside of a proc or function.
as in there is no TRY...CATCH like there is in SQL Server
 
ypercubeᵀᴹ
ypercubeᵀᴹ
what happens if you try something like?:
 
Max Vernon
Max Vernon
I do find it curious that no error is returned to the client though
 
ypercubeᵀᴹ
ypercubeᵀᴹ
8:13 PM
START TRANSACTION;
BEGIN
    CALL proc1();
    CALL proc2();
    CALL proc3();
    ROLLBACK;
END;
 
Max Vernon
Max Vernon
> Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CALL proc1()' at line 2
if you do this you get a divide-by-zero error: 
START TRANSACTION;
BEGIN;
    CALL proc1();
    CALL proc2();
    CALL proc3();
    ROLLBACK;
END;
 
ypercubeᵀᴹ
ypercubeᵀᴹ
it works in dbfiddle: dbfiddle.uk/…
 
Max Vernon
Max Vernon
perhaps that is a difference between 8.0.22 and 5.7.20
 
ypercubeᵀᴹ
ypercubeᵀᴹ
but I'm not sure if the ROLLBACK actually is executed
 
Max Vernon
Max Vernon
I need to get a local 8.0.x installation
@ypercubeᵀᴹ yah, I believe it's not. And I think it's not for my example either.
 
ypercubeᵀᴹ
ypercubeᵀᴹ
8:16 PM
The DECLARE exit handler is the proper way
This works by the way: dbfiddle.uk/…
 
Max Vernon
Max Vernon
interesting. I wonder how that transaction lasts across batches.
 
ypercubeᵀᴹ
ypercubeᵀᴹ
and you might like this "improvement" of your answer: dbfiddle.uk/…
with some select/prints to show that it doesn't reach the 33 part.
 
Max Vernon
Max Vernon
yah that's cool
 
ypercubeᵀᴹ
ypercubeᵀᴹ
It would also be good to have a COMMIT; just after the CALL proc3 and before the END.
It's not a path taken in your example but if they change proc3 to not throw an error, the transaction needs a commit.
See what could happen if there is no commit there: dbfiddle.uk/…
 
Peter Vandivier
Peter Vandivier
9:18 PM
@MaxVernon i actually spruced up my localhost last night 'cause i wanted to try that before chiming in. looks like it runs as you'd hope in mysql shell
the db<>fiddle driver for mysql not handling the same as the default shell client has bit me before a bit with minor syntax differences
Aug 21 '19 at 9:03, by Peter Vandivier
am i having a stroke or does this warrant a question on main?
but yea - BEGIN...END aren't cross-compatible between the two. neither is the CREATE PROC syntax though so.... 🤷‍♂️
 
Peter Vandivier
Peter Vandivier
9:40 PM
@PeterVandivier of course, when i say "spruced up"... i'm being generous... twitter.com/PeterVandivier/status/1333731592913772544
 
Max Vernon
Max Vernon
Running the original (from my question) on MySQL 8.0.22 via MySQL Workbench does return an error, at least. If I add a semicolon to the BEGIN TRANSACTION and ROLLBACK then it returns Error Code: 1048 Column 'z' cannot be null, which is much better than 5.7.
 
Peter Vandivier
Peter Vandivier
10:09 PM
well my advocacy for MySQL as the Superior Platform is well documented
 

« first day (3619 days earlier)      last day (1232 days later) »