« first day (2787 days earlier)      last day (390 days later) » 

7:12 AM
7:41 AM
Evening and Morning
8:28 AM
Tender Loving Care applied to Queue
8:50 AM
Q: How to describe Newid(), Rand(), and Sysutcdatetime() query vs row behaviors?

crokusekWithin a single query, all current time functions like sysutcdatetime() return a single point in time that is re-used throughout the query for each instance (e.g. of sysutcdatetime()) no matter how complex (afaik). However NewId() and Rand() do not follow this behavior and diverge from each oth...

Very interesting question. Just wish it was asked better. And no DBMS tag makes it absolutely useless.
Also, very quiet here this week. Everyone must be busy working. Lots of post-holidays issues? And taking care of babies? :-)
...and the New Contributor Wave is so adorable.
9:22 AM
I would hate being a new contributor now with that thing attached to me. Apparently I didn't pay much attention to the wording when the feature was first introduced on Meta, or I perhaps would've commented on it. But anyway, now that I see it in use, it looks very condescending to me.
Other than that, good morning :)
9:33 AM
@AndriyM Do they see that themselves?
@TomV That's an interesting question. At first I thought they might not, but then I discovered I could see it in my browser's Private Mode (where I'm not logged in). So whether I as a new contributor would be able to see it or not, I wouldn't be happy to know that others can see it.
9:55 AM
More tag mess:
Both logs and log exist.
But search for tags including the word log and it gets worse:
Both transaction-log and logs have wiki summaries that are basically the same, IMHO
a list/history of modification executed by a database management system to guarantee ACID properties over crashes or hardware
A sequential journal of database write operations that facilitates robust atomic write operations.
Can you tell which wiki summary belongs to which tag?
Then there are also separate tags for binlog, mysqlbinlog
And aso
archive-log, logging
Also logon and logins
Then slow-log -- what's that?
How do we get this mess cleaned up?
Oh, also logging
Most questions with that tag are about error or warning logging (there's also a tag error-log for that), but some questions are about transaction logging
10:32 AM
either to discuss the problem(s) or propose a solution
slow log is a mysql thing afaik that logs slow queries
Looks like the hand of a drowning person to me
even more cynically:
A: What is that "New contributor" icon supposed to be?

user6655984It looks like the hand is covering something. I interpret it as a statement about the current direction of SO-the-company leadership.

Shame that didn't one-box.
Apparently, it is also a Greek insult
11:14 AM
@PaulWhite Should I post one question about all tags including the word "log" ?
It started because I saw a question tagged with both log and logs...
And it spiraled from there...
@Colin'tHart You could do, but it might be a bit broad. Maybe start with what to do about one particularly egregious example like log vs logs.
1 hour later…
12:46 PM
What SQL do you have now? — Colin 't Hart 3 hours ago
@Colin'tHart SQL Server — Sudharsh Ram 8 mins ago
Where's that facepalm emoji?
@Colin'tHart right under his profile picture, next to the "new contributor"
1:49 PM
Well, back to contracting now. I've got an offer.
First Oracle gig in about 6 years.
@ConcernedOfTunbridgeWells The shame
2:51 PM
yo yo
I'm having so much "fun" with updating MySQL on AWS through a linked server on SQL Server, you wouldn't believe it
> The OLE DB provider "MSDASQL" for linked server "MYSTUFF_AWS" could not UPDATE table "[MSDASQL]". The rowset was using optimistic concurrency and the value of a column has been changed after the containing row was last fetched or resynchronized.
seems I need a pass-through, like exec @sql at MYSTUFF_AWS - like here
is there a way to do this without updating 1 row at a time?
I'd like to avoid writing a cursor for this
		set @sql = replace(replace('
		declare @updateCount int = (
		select count(*)
		from openquery($svr$, ''select * from $db$.target'') a
		inner join openquery($svr$, ''select * from $db$.target_info where filter1=11 and filter2=22'') b on a.target_info_id = b.id
		inner join SomeOtherDb.dbo.SourceOfTruth i on b.key=i.Key
		where a.something <> i.Something);

		declare @msg nvarchar(max) = concat(''Updating [target]: '', @updateCount, '' rows.'');
		exec SomeOtherDb.dbo.LogEntry ''DYNSQL@$svr$.$db$'', @msg;
^ what I don't understand is why this works, but the other one I need to do, doesn't
(it's essentially the same logic, just another target table)
cya guys
		update a
		set a.value1=i.Value1
		from openquery($svr$, ''select * from $db$.target'') a
		inner join SomeOtherDb.dbo.SourceOfTruth i on a.key=i.Key and a.filter1=11 and a.filter2=22
		where a.value1 <> i.Value1
^ blows up
can't be "pass-through" because the "source of truth" lives on SQL Server
so, ..stumped :(
3:09 PM
@MathieuGuindon you'd probably need to send the SQL Server data to the remote MySQL Server, then run the update statement there. Updating rows via a linked server is almost certainly going to result in a cursor behind the scenes any way.
@MaxVernon good point.
hmm so it's actually simpler to write the cursor myself anyway then
Do you have spot over there where you can stage the data and then run the update?
not on the AWS side :(
@MathieuGuindon it's going to be non-optimal, but yes that may be the only way to do it if you can't get at the data directly on the MySQL side.
prepare for it to be slow
3:12 PM
yeeesh. You don't have access to like pragmatic works SSIS tools by any chance do you?
I do have SSIS
I thought I could do this with just stored procedures and an agent job
how many rows are being sent/updated in MySQL?
Yeah there are some ways that you can do that on SSIS that might be more efficient than building a cursor. Still Likely to be a RBAR operation since staging isn't an option.
if it's 20, no big deal. If it's 2 million, that may be a big problem.
^^^ Indeed
3:14 PM
nah, it needs to run every 15 minutes to update inventories on some PHP website's backend
it's just a handful of rows every time
if any
@MathieuGuindon I'd just write it and see how it runs. If perf becomes an issue, then revisit it.
you likely have bigger fish to fry
remember not to prematurely optimize
funny, at first I had a cursor... then I realized update...from...join worked, rewrote the two updates.. and now only one of them works
I'll re-cursorize it :)
no problem
4:12 PM
So I'm cleaning up our file system because the previous DBA had a massive boner for partitions and tons of files/filegroups.
We currently have a table that is 50MB and is partitioned across 3 data files lol
The whole thing is such overkill that it's hilarious.
All 3 files exist to support this 1 table.
@Zane sounds like he was trying to make himself indispensable, and failed.
good example of premature optimization
@A_V Tuktoyaktuk ?
lol I just found one that is similar but only has 76 rows in it.
@Zane crazy.
@ypercubeᵀᴹ intersect and except. nice.
temporal tables is a pretty cool feature too.
I wonder if the stupid interesting MySQL GROUP BY issues have been fixed by them.
Yes, they have gone a long way since they forked from mysql
I wonder if MariaDB would work as the backend of WordPress. heads over to wordpress.se
I bet the procedural update of columns in MySQL has screwed up more than a couple of tables.
4:47 PM
@MaxVernon there is a peculiar group of DBA's who like to add massive amounts of work for seemingly no lift.
My predecessors fall into that group. What's especially funny is all of these have default by 5% auto growth on so even with all this micromanaging they missed the parts that matters.
@Zane d'oh!!!
I wonder how many VLFs are in the log file.
Dude... guess.
lol, 10s of thousands is my guess
which is more than I'd like. Luckily most of these tiny baby files never outgrew their 50MB size limit lol
@Zane well, that's not soooo bad then. still something you'd want to fix :-)
4:56 PM
other dudes doing that while I handle these dumb files.
It's the last thing I want to get done before I quite this gig
5:44 PM
@gbn They're paying me to do it. I can be an old-school unix geek and tell people to get off my lawn.
@ypercubeᵀᴹ So has MySQL. Which now has binary json.
No mariadb 10.3 on dbfiddle =(
1 hour later…
7:08 PM
@TomV I really don't know much about data warehouses, but I think that asking for their current OLTP design, as you mention, would be really helpful and would avoid further misunderstandings; it would also facilitate proposing a proper DWH arrangement without more toing a froing. The OP has made several efforts in clarifying so far, but I don't think the scenario is clear enough yet.
2 hours later…
8:43 PM
> Could not execute statement on remote server 'AWS'
hitting roadbumps at every. step. of. the. way.
so frustrating
what's wrong with exec (@remoteSql) at AWS;?
(@remoteSql contains an update statement that looks very much legal to run on MySQL)
8:56 PM
FWIW exec ('the actual update query') at AWS also says "could not execute statement on remote server 'AWS'"

« first day (2787 days earlier)      last day (390 days later) »