Morning

Evening and Morning

TLCa2Q

Tender Loving Care applied to Queue

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.

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 :)

@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.

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

and

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

@Colin'tHart Database Administrators Meta

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:

Shame that didn't one-box.

Haha

Apparently, it is also a Greek insult

@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.

Where's that facepalm emoji?

@Colin'tHart right under his profile picture, next to the "new contributor"

Well, back to contracting now. I've got an offer.

First Oracle gig in about 6 years.

@ConcernedOfTunbridgeWells The shame

yo yo

hi!

> 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 :(

@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

yeeesh. You don't have access to like pragmatic works SSIS tools by any chance do you?

hmm

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

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

indeed!

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 :)

thanks!

no problem

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 ?

What’s New in MariaDB 10.3

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.

@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

1780

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 :-)

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

@gbn They're paying me to do it. I can be an old-school unix geek and tell people to get off my lawn.

assets.amuniversal.com/6b08abb09fbb012f2fe600163e41dd5b

@ypercubeᵀᴹ So has MySQL. Which now has binary json.

No mariadb 10.3 on dbfiddle =(

poop

@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.

> 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)

FWIW exec ('the actual update query') at AWS also says "could not execute statement on remote server 'AWS'"