26

I just read a comment by user @questionto42 that one of my cross-site queries isn't working. My educated guess is this is because a special database Data.StackExchangePOC was created two days ago:

enter image description here

(that's the result of running SELECT * FROM sys.databases WHERE database_id > 5 which many SEDE queries depend on). The next SEDE refresh is almost due (Sunday morning), it might disappear automatically, but I don't have any clue what's going on right now.

0

3 Answers 3

21

Sorry, this was me, and it is now fixed.

Before going on vacation this past weekend, I had started work on a mini-project to enable developers to test Data Explorer locally. To be portable, we can't require developers to restore 300+ databases, especially those the size of Stack Overflow. This meant creating a new Data.StackExchange database - where we store the list of sites available, among other things that make Data Explorer work - so that only a couple of small sites were listed. This makes the footprint small and makes it feasible for employees to get Data Explorer up and running on their laptops. I called this database Data.StackExchangePOC only because it was an internal proof-of-concept.

There is nothing nefarious or strange with this database, and nothing to be concerned about in terms of exposing PII or changing anything about how Data Explorer works for you. This was simply intended to unblock internal folks from testing the application locally and, as a responsible data steward accustomed to making copies of databases for engineers, the first thing I did upon restoring a copy was clear out tables with user information (like your queries).

Unfortunately, I did not consider that this database would suddenly be pulled into cross-database queries. Which explains why the Data Explorer login (svc_sede) wasn't explicitly prevented from accessing its existence in sys.databases (as has long been the case for Data.StackExchange).

I'll proceed with my work on a different Data Explorer server.


A feature request that would likely be easy for me to implement would be a view that provides a list of databases that:

  • are not system databases
  • are not secret POC databases
  • are online
  • are accessible by svc_sede
  • contain a Posts table

This would present you with a reliable list that will be updated when new sites are added. It would really reduce the grunt work in queries like Glorfindel's, and prevent you from having to parse this question for that same list. In fact, the view could be used to generate the markdown in that question.

18

While we wait for this rogue database to be dropped, hopefully accompanied with an answer explaining what Proof of Concept we can look forward for, let's fix our network-wide queries by checking if we have access to the database we like to include.

We can use has_dbaccess([database name]) which returns a 1 if we can access that db.

Here is how you would extend the where clause of the sys.databases select query:

SELECT database_id
     , name 
FROM  sys.databases
where has_dbaccess(name) = 1
and database_id > 4 -- we still assume master, msdb, tempdb and model to have ids under 5. 
                    -- Note that has_dbaccess('Data.StackExchange') also returns 0

With the change has_dbaccess(name) = 1 we reduce the risk that our network-wide queries blow-up due to a database we don't have access to. This comes at the expense that we're no longer alerted such new database appeared, whether that is planned or due to a misconfiguration.

4
  • 6
    Update from SE staff: The Data.StackExchangePoc is / was an experiment for a SEDE instance with less sites. The db will be dropped and testing will take place elsewhere.
    – rene
    Commented Feb 18 at 16:26
  • Are they so afraid from us that they don't post it themselves? Also, as comment, there's very big chance it will disappear any moment, and nobody will ever know about it. Commented Feb 18 at 20:37
  • 14
    @Shadow Not afraid. Just want to be fully engaged when I post an answer. It’s the weekend plus I’m on vacation with no computer. In the meantime, the symptom should be resolved.
    – Aaron Bertrand Staff
    Commented Feb 18 at 22:28
  • @AaronBertrand fair, enjoy the well deserved vacation! Commented Feb 19 at 7:42
8

The issue has been fixed. The database Data.StackExchangePOC is gone and cross-site queries are working fine without having to use rene's workaround.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .