mongorestore - documents fail validation The similar question already have been asked: How to validate a mongorestore

But how to actually validate the documents in mongodumb and mongorestore?
And also - can I see the documents that fail validation and why do they fail it?

The commands I am using:

mongodump -d my_database -o ~/dump --gzip
mongorestore --drop --gzip dump

The mongo is of  4.x.x version (4.0.10 and 4.2.0).

If only needing to validate, the best combination would be to add --dryRun --objcheck --verbose or --dryRun --objcheck -vvvvv to your mongorestore command.

When running the command for real, drop the --dryRun.


Granting select on syscat.schemata - DB2 I was wondering if it is safe to grant select rights to syscat.schemata to users on a DB2 database? What could go "wrong"?
Some tool like DataGrip and DBeaver cannot load connection schemas because of this missing rights.
Others tools like AQT or SquirrelSQL load schemas that they see with not problem.
I am not really at home in DB2 environment.

Select privileges on all SYSCAT objects are granted by default to the PUBLIC pseudo-group (unless the database is created as "restrictive"), so you shouldn't have ended up with users not being able to access syscat.schemata unless you or someone before you chose to actively prevent that.

What could go "wrong"?

Users will be able to discover what schemas exist in your database. Obviously this doesn't give them any extra privileges to see or access in any way objects in those schemas. In other words, being able to read syscat.schemata is pretty harmless.


Stored Procedure: table-Value parameters We have a number of stored procedures where we pass in a user defined table type parameter. As per the MS doc below, my understanding is that SQL Server doesn't retain statistics on these (and therefore should only estimate a single row?).

https://docs.microsoft.com/en-us/sql/relational-databases/tables/use-table-valued-parameters-database-engine?view=sql-server-2017

Recently, we've seen issues with plan generation where the estimated number of rows in the table is a high as 2000 (normally only 1 or 2 rows are in passed in).  This is caching a plan which is highly inefficient (wrong join order with a largish table ~160M rows) and causing some issues.

Am I wrong here about what the estimates should be? I could force the join type/order but I'd rather avoid this if at all possible.

Would really appreciate any thoughts/feedback people might have.

Thanks
Dave

Check out this post from Erik Darling:

Table Valued Parameters: Unexpected Parameter Sniffing

The gist of it is that table-valued parameters are susceptible to parameter sniffing just like other parameters.  If the execution plan happened to get cached when 2,000 rows were passed in via the TVP, then that's the plan you're stuck with (until a recompile).

All of the usual solutions to parameter sniffing apply.  It would be helpful if you could provide an actual execution plan and query text to get a better idea of what's going on.

Probably the "simplest" solution is to add a RECOMPILE hint to the query that's performing the join to this big table.  This will incur higher CPU (due to more plan recompiles) and you lose the "predictability" of the query plan, but it could solve the immediate plan quality issue.

This should also help with the problem of fixed join estimates, mentioned in Erik's post:

  Non-join cardinality estimates behave like local variables (fixed estimates)

  ...we get 10% for equality, 30% for inequality, and 9% for two inequalities.


hide system databases sql server management studio I have installed sql server express 2008 and also sql server management studio. I've successfully configured remote connectivity and created a database user and password.
my problem is that in the remote sql server management studio - i want the user to only see the database i assign to them and not see System Databases

I've noticed that the sa login also appears in the folder tree in my remote session which i do not want

How do I go about hiding the system databases as well as logins so that the remote connection only sees logins and database relevant to them

You can hide the system database in SQL Management Studio following Way that I have mentioned below.

  Navigate Tools-->Options-->Environment-->General and choose "Hide
  system objects in Object Explorer".

OR

  In the SQL Server Management Studio, Right click the server and click
  "Properties". Click on "Permissions" and then select the "Public" role
  and remove "Grant" from "View Any Database".

Or You can use

USE <YourDatabase>

ALTER AUTHORIZATION ON DATABASE::<YourDatabase> to <Login>

USE MASTER

DENY VIEW ANY DATABASE TO <Login>


Index Seek scanning whole table dependent on parameter value I have a query:

SELECT  Id,
        ColumnA,
        ColumnB
FROM    MyTable
WHERE   ColumnA = @varA OR
        ColumnB = @varB

The table is defined as

CREATE TABLE MyTable
(
    Id INT IDENTITY(-2147483648,1) PRIMARY KEY,
    ColumnA VARCHAR(22)
    ColumnB VARCAHR(22)
)

and there is a non clustered index on the table

CREATE INDEX IX_MyIndex ON MyTable
(
    ColumnA
)

When I run the query with the parameters below:

DECLARE @varA nvarchar(4000) = ''
DECLARE @varB  nvarchar(8) = '10140730'

The execution plan shows an index seek on IX_MyIndex, however it shows number of rows read as 17million rows but actual number of rows as 0 (There are 0 rows in MyTable.ColumnA with the value '')
If I turn SET STATISTICS IO ON I can see the full table is being read

This makes sense as per: this article in the section "Here’s a “bad” index seek"

However, when I run the same query with the parameters:

DECLARE @varA nvarchar(8) = 'a'
DECLARE @varB  nvarchar(8) = '10140730'

The seek operator doesn't have a "number of rows read" property (there are 0 rows MyTable.ColumnA in with value 'a') and SET STATISTICS IO reports single figure logical reads

Incidentally, the plan has an implicit convert warning and the issue goes away when I change the query like so:

SELECT  Id,
        ColumnA,
        ColumnB
FROM    MyTable
WHERE   ColumnA = CONVERT(VARCHAR(22),@varA) OR
        ColumnB = CONVERT(VARCHAR(22),@varB)

Or change the underlying column to NVARCHAR

However, I am curious as to why the behaviour of the index seek with the two different values for @varA is different even though both of them return the same number of records in the table (0)

When there is a mismatch between the data types of the column and variable, SQL Server cannot directly use the seeking ability of a b-tree index to locate the correct range of values.

When the rules of data type precedence mean that the column data would have to be converted to the data type of the variable, this would mean scanning the whole table or index, converting each value and testing it against the variable as a residual predicate.

This is obviously not ideal, but so common (unfortunately) that SQL Server has a built-in way to achieve an index seek in these cases. It takes the supplied value and computes the range of values it maps to, accounting for the type conversion and collation.

This feature is known as a dynamic seek and the internal method that computes the mapped range is called GetRangeThroughConvert.

For example, when the nvarchar variable contains 'a', the mapped range of values for data type varchar might be 'a' to 'B' (the exact range depends on the collation). This means SQL Server can seek the varchar index between 'a' and 'B', testing only the matches for equality with 'a' (as nvarchar) as a residual predicate.

When the supplied value is the empty string, the computed range is infinite, so the whole index is effectively scanned.

For example:

DROP TABLE IF EXISTS dbo.MyTable;
GO
CREATE TABLE dbo.MyTable
(
    ColumnA varchar(22) COLLATE Latin1_General_CI_AS NOT NULL
);
GO
INSERT dbo.MyTable
    WITH (TABLOCKX)
    (ColumnA)
SELECT TOP (1000)
    REPLICATE(CHAR(65 + ROW_NUMBER() OVER (ORDER BY @@SPID) % 26), 22)
FROM master.sys.all_columns AS AC1
CROSS JOIN master.sys.all_columns AS AC2;
GO
CREATE INDEX IX_MyIndex_A ON dbo.MyTable(ColumnA);

The following query uses a dynamic seek with a range of 'a' to 'B' and a residual predicate of CONVERT_IMPLICIT(nvarchar(22),[dbo].[MyTable].[ColumnA],0)=[@varA]:

DECLARE @varA nvarchar(22) = N'a';

SELECT MT.ColumnA
FROM dbo.MyTable AS MT
WHERE MT.ColumnA = @varA;

The execution plan shows the dynamic seek shape with 38 rows qualified by the seek, but all ultimately rejected by the residual:

The 38 rows are those counted by the query:

SELECT COUNT_BIG(*)
FROM dbo.MyTable AS MT
WHERE MT.ColumnA > 'a'
AND MT.ColumnA < 'B';

When the variable contains an empty string, the calculated range is unbounded so the seek effectively scans the whole index:

DECLARE @varA nvarchar(22) = N''; -- empty string

SELECT MT.ColumnA
FROM dbo.MyTable AS MT
WHERE MT.ColumnA = @varA;

The execution plan shows all 1000 rows being read from the index (but again, discarded by the residual):

The empty string is a special case where GetRangeThroughConvert cannot produce a useful range. A single space character does produce a narrow seek range (plan).

Anyway, the message is to pay careful attention to data types.

db<>fiddle


What grants should I give a user if I want the user to see all tables and views (view definitions) in every database in the server I want a user to see all the tables and views in the each database on the server. I also want them to see the view definitions. By that I want to also be able to see how the views are created. That is, the query inside the view not the results

What server level access should I grant them to be able to do that? We have several databases and a huge amount of views and tables in each database. I don't want to do that manually one by one

Thanks

GRANT VIEW DEFINITION TO [user]; will allow the user to see the definitions of structures in the database, including tables, views, stored procedures, etc.

You'll need to do that for every database on the instance.  There is no server-wide equivalent, out of the box, that limits permissions to just the database level.

If you need to limit it to just a single schema, then you'd do:

GRANT VIEW DEFINITION ON schema::[name_of_schema] TO [user];

If you only want them to be able to see the definitions of tables and views (and not stored procs etc), then you'll need to do a single grant for each object you want them to see the definition for.  You could generate the GRANT statements for just tables and views at the database level with the following query:

SELECT
      ObjectType = o.type_desc
    , ObjectName = o.name
    , GrantStatement = N'GRANT VIEW DEFINITION ON ' + QUOTENAME(o.name) + N' TO [user];'
FROM sys.objects o
WHERE o.type = N'U'
    OR o.type = N'V'
ORDER BY o.type
    , o.name;

See the Microsoft Docs for more info at https://docs.microsoft.com/en-us/sql/t-sql/statements/grant-database-principal-permissions-transact-sql?view=sql-server-ver15

If you're comfortable assigning a lot more permissions to the login, at the server level, you could do:

GRANT VIEW ANY DEFINITION TO [name_of_login];

That will allow them to see the definition of any object in any database they have access to, and also will allow them to see definitions of things like endpoints, at the server level.


How to get a breakdown of CPU usage at each point in time? I have a SQL Server RDS instance, running on a db.m5.xlarge (4 vCPU, 16GB RAM) with Multi AZ enabled.

I am trying to determine the cause of a constant 25%-30% CPU load on the instance at a time when we know that there is very little client activity hitting the database. We've taken into account our most expensive queries and feel that they are not the cause. Regular DB maintenance such as index and statistics rebuilds has been done.

The image below is a graph on a CloudWatch dashboard, monitoring database connections and CPU usage. Is there a way I can query or visualise the breakdown of CPU usage at each data point in the graph? (I'm happy to dive in and run manual queries if that is what is suggested).

You can turn on Enhanced Monitoring and Performance Insights for more detail by modifying the RDS instance in the portal.

RDS > Databases > databasename > Modify

Also, on an RDS instance you can run Glenn Berry's DMV scripts and most of the queries will work. The stuff that breaks generally centers around drives, the errorlog, agent jobs, and the registry. Just download his scripts for the version of SQL you are running and search on CPU. You can start with the one titled - "Get CPU Utilization History for last 256 minutes (in one minute intervals)".

https://glennsqlperformance.com/2020/02/04/sql-server-diagnostic-information-queries-for-february-2020/

Depending on the version of SQL your RDS instance is running (must be SQL 2016 or newer), you can turn on Query Store as well.


COUNT number of column values with INNER JOIN I'm new to PostgreSQL and I am making a Report. This is my SQL Fiddle:

http://sqlfiddle.com/#!15/6ce465/5

So basically I have two tables, responses and questions. The structure is

responses
->id
->question_id
->response

questions
->id
->question
->costperlead

For the column response there can only be 3 values: Yes, No, Possibly.

And my report should have the columns

question_id
# of Yes Responses
# of No Responses
# of Possibly Responses
Revenue

where

# of Yes Responses is the count of all Yes values in the response column,
# of No Responses is the count of all No values in the response column,
# of Possibly Responses is the count of all Possibly values in the response column,
Revenue is costperlead * ("# of Yes Responses" + "# of Possibly Responses").

I don't know how to construct the query. I'm new plus I came from MySQL, so some things will be different in PostgreSQL. In my SQL Fiddle sample most responses are Yes and Null – it's OK. Eventually, there will be Possibly and No.

So far I have only

SELECT a.question_id
FROM responses a INNER JOIN questions b ON a.question_id = b.id
WHERE a.created_at = '2015-07-17'
GROUP BY a.question_id ;

Will it work for you ?

SELECT a.id as question_id, a.costperlead, a.costperlead *(b.numYes+b.numPossible) as revenue,
b.numYes,b.numNo,b.numPossible
FROM questions a
LEFT JOIN
(
SELECT a.question_id,
count(case when a.response='Yes' then 1 end)AS numYes,
count(case when a.response='No' then 1 end) AS numNo,
count(case when a.response = 'Possibly' then 1 end) numPossible
FROM responses a
GROUP BY a.question_id
)b ON b.question_id = a.id;

(http://sqlfiddle.com/#!15/6ce465/46)

Note: questions without responses will be also included in resultset. If it's not needed , replace LEFT JOIN with INNER JOIN.


Statistics needs to be captured for query analysis What statistics are required to theoretically prove that X query work better than Y query. As per my knowledge I need to look for:

Query elapsed time
Logical read
Logical write
Physical read
Physical write

Do I need to have other statistics also to measure? Do I also need to measure

Memory pressure

TempDb space usage

Buffer usage

Query plan (Scan, Seek)
to get perfect analysis.

Ex scenario:
I have two queries one is using index and one without index. I need to show which query is performing well.

I assume you are talking about the same query, executing with different indexes.  You can't just arbitrarily compare 2 totally different queries that do 2 different things.  As Aaron stated in the comments, I can compare my 2001 VW to a 2015 Camaro and there will be no correlation, the Camaro may go faster but that's because it has a bigger engine.  Conversely, my VW will get better gas mileage because the engine is smaller.

That being said, I use the SET options: SET STATISTICS TIME, IO ON

Once those are on, the query will kick out all the CPU usage and reads that have been performed.  See below:

I then use www.statisticsparser.com to consolidate the data into an easy to read format.  That takes care of CPU/IO.

Execution plan can be pulled from the "Get Actual Execution plan" option in SSMS .  The execution plan will also have the memory grant (how much memory is allocated to the query).


Do values in `sys.dm_os_wait_stats` reset to 0 or stop accumulating when they hit the max value? When looking at sys.dm_os_wait_stats, the following columns are defined as BIGINT

waiting_tasks_count
wait_time_ms
max_wait_time_ms

If any of those values go above 9,223,372,036,854,775,807, do the counters reset to 0, or simply stop counting?

It's unclear from the table definition what goes on behind the scenes:

sp_helptext 'sys.dm_os_wait_stats'

Returns:

CREATE VIEW sys.dm_os_wait_stats AS
 SELECT *
 FROM OpenRowset(TABLE SYSWAITSTATS)

So that's a bit of a black box.

There are some DMVs where large numbers can turn negative. One example of this is the total_elapsed_time column in dm_exec_requests.

According to Docs:

  In SQL Server the wait-time counters are bigint values and therefore
  are not as prone to counter rollover as the equivalent counters in
  earlier versions of SQL Server.

I've seen this with earlier version of SQL Server where rollover happened and you'd get negative numbers because the values are signed.

I took a look at the source code and indeed these are 8-byte signed values. Since this is all C++, I created a simple repro:

#include "stdint.h"
#include <iostream>

using namespace std;

int main()
{
    int64_t i8 = 0;
    cout << "Size of integer: " << sizeof(i8) << " bytes." << endl;
    cout << "Max Value: " << INT64_MAX << endl;
    i8 = INT64_MAX + 1;
    cout << "Value of MAX + 1: " << i8 << endl;
    cin;
    return 0;
}

The output is similar to what I experienced in previous versions of SQL Server, the signed value is rolled over. Note that if you compile this, you should get a compiler warning that there will be rollover - I received compiler warning 4307.

Output of above:

Size of integer: 8 bytes.
Max Value: 9223372036854775807
Value of MAX + 1: -9223372036854775808

I'm NOT stating this is exactly how SQL Server works in all and every case as there are many nuances... but this should be a similar approximation.

What does this all really mean?

I highly doubt, as Aaron has said, that you'll ever encounter rollover on the 64-bit values in the current hardware climate. If you did, it would be easy to spot and really be more of reporting inaccuracy than any real issue in my opinion.


How do I map an IS-A relationship into a database? Consider the following:

entity User
{
    autoincrement uid;
    string(20) name;
    int privilegeLevel;
}

entity DirectLoginUser
{
    inherits User;
    string(20) username;
    string(16) passwordHash;
}

entity OpenIdUser
{
    inherits User;
    //Whatever attributes OpenID needs... I don't know; this is hypothetical
}

The different kinds of users (Direct Login users, and OpenID users) display an IS-A relationship; namely, that both types of users are users. Now, there are several ways this can be represented in an RDBMS:

Way One

CREATE TABLE Users
(
    uid INTEGER AUTO_INCREMENT NOT NULL,
    name VARCHAR(20) NOT NULL,
    privlegeLevel INTEGER NOT NULL,
    type ENUM("DirectLogin", "OpenID") NOT NULL,
    username VARCHAR(20) NULL,
    passwordHash VARCHAR(20) NULL,
    //OpenID Attributes
    PRIMARY_KEY(uid)
)

Way Two

CREATE TABLE Users
(
    uid INTEGER AUTO_INCREMENT NOT NULL,
    name VARCHAR(20) NOT NULL,
    privilegeLevel INTEGER NOT NULL,
    type ENUM("DirectLogin", "OpenID") NOT NULL,
    PRIMARY_KEY(uid)
)

CREATE TABLE DirectLogins
(
    uid INTEGER NOT_NULL,
    username VARCHAR(20) NOT NULL,
    passwordHash VARCHAR(20) NOT NULL,
    PRIMARY_KEY(uid),
    FORIGEN_KEY (uid) REFERENCES Users.uid
)

CREATE TABLE OpenIDLogins
(
    uid INTEGER NOT_NULL,
    // ...
    PRIMARY_KEY(uid),
    FORIGEN_KEY (uid) REFERENCES Users.uid
)

Way Three

CREATE TABLE DirectLoginUsers
(
    uid INTEGER AUTO_INCREMENT NOT NULL,
    name VARCHAR(20) NOT NULL,
    privlegeLevel INTEGER NOT NULL,
    username VARCHAR(20) NOT NULL,
    passwordHash VARCHAR(20) NOT NULL,
    PRIMARY_KEY(uid)
)

CREATE TABLE OpenIDUsers
(
    uid INTEGER AUTO_INCREMENT NOT NULL,
    name VARCHAR(20) NOT NULL,
    privlegeLevel INTEGER NOT NULL,
    //OpenID Attributes
    PRIMARY_KEY(uid)
)

I'm almost certain the third way is the wrong way, because it's not possible to do a simple join against users elsewhere in the database.

My real world example is not a users with different logins example though; I'm interested in how to model this relationship in the general case.

Way two is the correct way.

Your base class gets a table, and then child classes get their own tables with just the additional fields they introduce, plus foreign key references to the base table.

As Joel suggested in his comments on this answer, you can guarantee that a user will have either a direct login or an OpenID login, but not both (and also possibly neither) by adding a type column to each sub-type table that keys back to the root table. The type column in each sub-type table is restricted to have a single value representing the type of that table. Because this column is foreign keyed to the root table, only one sub-type row can link to the same root row at a time.

For example, the MySQL DDL would look something like:

CREATE TABLE Users
(
      uid               INTEGER AUTO_INCREMENT NOT NULL
    , type              ENUM("DirectLogin", "OpenID") NOT NULL
    // ...

    , PRIMARY_KEY(uid)
);

CREATE TABLE DirectLogins
(
      uid               INTEGER NOT_NULL
    , type              ENUM("DirectLogin") NOT NULL
    // ...

    , PRIMARY_KEY(uid)
    , FORIGEN_KEY (uid, type) REFERENCES Users (uid, type)
);

CREATE TABLE OpenIDLogins
(
      uid               INTEGER NOT_NULL
    , type              ENUM("OpenID") NOT NULL
    // ...

    PRIMARY_KEY(uid),
    FORIGEN_KEY (uid, type) REFERENCES Users (uid, type)
);

(On other platforms you would use a CHECK constraint instead of ENUM.) MySQL supports composite foreign keys so this should work for you.

Way one is valid, though you are wasting space in those NULL-able columns because their use depends on the type of user.  The advantage is that if you choose to expand what kinds of user types to store and those types don't require additional columns, you can just expand the domain of your ENUM and use the same table.

Way three forces any queries that reference users to check against both tables. This also prevents you from referencing a single users table via foreign key.


Getting sum result for last year divided quarterly I am trying to get financial data grouped by each quarter of last year and also grouped by the last three weeks.

Also, if it is possible, I want to have the type as a separate column with the type as the header.

This is the query I am using right now which is giving the result for last month.

Select
  to_char(add_months(last_day(TO_DATE(&DTE,'YYYYMMDD')),-1), 'MM')
    || '-'
    || to_char(add_months(last_day(TO_DATE(&DTE,'YYYYMMDD')),-1), 'YYYY') AS Month,

  case
     when (ap.asset = 2 or AP.ASSET_TYPE not like 'CFTD%' and
          AP.ASSET_TYPE not like 'CASH%' and AP.ASSET_TYPE != 'FTL') then
      'AUM'
     when (AP.ASSET_TYPE like 'CFTD%') then
      'Depostis'
     when (AP.ASSET_TYPE like 'CASH%') then
      'Cash'
     when (AP.ASSET_TYPE = 'FTL') then
      'Loan'
   end as Type,

   sum(ABS(AP.Eval_Market_Value)) as Total

from (select p.account,
           p.open_date    as Open_Date,
           ac.description as RM,
           s.*

      FROM k$portfolio p, k$client k
      LEFT JOIN k$client_role cr
        ON cr.client = k.id
       AND cr.role = 136
      LEFT JOIN k$actors ac
        ON cr.actor = ac.id,
     table(gtw$reporting.Statement(p.id,
                                      2,
                                      trunc(add_months(last_day(TO_DATE(&DTE,'YYYYMMDD')),-1)),
                                      trunc(add_months(last_day(TO_DATE(&DTE,'YYYYMMDD')),-1)),
                                      1,
                                      1002,
                                      1,
                                      'USD')) s
     wHERE s.line_type = 'P'
       and k.id = p.client
       and p.id = s.portfolio
       and p.portfolio_type = 'C'
       and p.status = 1

    ) ap

group by to_char(add_months(last_day(TO_DATE(&DTE,'YYYYMMDD')),-1), 'MM') ||'-'|| to_char(add_months(last_day(TO_DATE(&DTE,'YYYYMMDD')),-1), 'YYYY'),

      case
        when (ap.asset = 2 or AP.ASSET_TYPE not like 'CFTD%' and
             AP.ASSET_TYPE not like 'CASH%' and AP.ASSET_TYPE != 'FTL') then
         'AUM'
        when (AP.ASSET_TYPE like 'CFTD%') then
         'Depostis'
        when (AP.ASSET_TYPE like 'CASH%')  then
         'Cash'
     when (AP.ASSET_TYPE = 'FTL') then
         'Loan'
      end

=====================

Current output

Desired result

I'm not sure if I understood the problem correctly, but maybe you're looking for something like this?

Select
to_char(add_months(last_day(TO_DATE(&DTE,'YYYYMMDD')),-1), 'MM') ||'-'|| to_char(add_months(last_day(TO_DATE(&DTE,'YYYYMMDD')),-1), 'YYYY') AS Month,
sum(case when (AP.ASSET_TYPE like 'CFTD%') then ABS(AP.Eval_Market_Value) else 0 end) as 'Depostis',
sum(case when (AP.ASSET_TYPE like 'CASH%') then ABS(AP.Eval_Market_Value) else 0 end) as 'Cash',
sum(case when (AP.ASSET_TYPE = 'FTL') then ABS(AP.Eval_Market_Value) else 0 end) as 'Loan',
...


Weak entity and one-one or many-many relationship In Entity Relationship Model, can the supporting (or identifying) relationships of the weak entities be many-many or one-one rather than many-one?

Suppose I have a customer entity (C), a video entity (V) and a weak entity reservation (R). C - R has a one-many relationship and it's a supporting relationship for R. However between V - R the same one-many relationship wouldn't make sense, since a video shouldn't appear in two different reservations.

So I wonder if a one-one relationship can be a supporting one for the weak entity? Or should it be a normal one-one instead of a supporting one?

If you are running a video rental store, then whether or not your children eat depends very much on a video appearing in as many reservations as possible.

In your example, RESERVATION is a weak entity which relies on both the FK to CUSTOMER and the FK to VIDEO in addition to a further attribute, perhaps date, to comprise its primary key.

It is not possible for a M:N relationship to be supporting for a weak entity (although the intersection table which resolves a M:N relationship can be a weak entity).  This is because a supporting relationship needs to be to a particular entity object.  A set of entity objects can't identify something in relational algebra.

It is possible for a 1:1 relationship to be supporting for a weak entity, although this would be rare "in the wild".  I could imagine it coming up in the context of situations that would typically be 1:M, but where there is a business rule that restricts the child to one at a time.


Temporal tables extension error with array columns I'm using the temporal tables Postgres extension (http://pgxn.org/dist/temporal_tables/) on Postgres 9.5.4 on Windows. This works well for most cases, but I'm running into a weird issue with array columns.

I've created some minimal steps to reproduce this, the setup of the table and the temporal tables extension is the following:

CREATE EXTENSION IF NOT EXISTS temporal_tables;

DROP TABLE IF EXISTS test;
DROP TABLE IF EXISTS test_history;

CREATE TABLE test
(
  id SERIAL PRIMARY KEY,
  a integer,
  directories text[],
  sys_period tstzrange NOT NULL
);

CREATE TABLE test_history (LIKE test);
CREATE TRIGGER versioning_trigger BEFORE INSERT OR UPDATE OR DELETE ON test FOR EACH ROW EXECUTE PROCEDURE versioning('sys_period', 'test_history', true);

And in separate transactions the following two commands:

INSERT INTO test(a) VALUES (1);

UPDATE test SET a = 5 WHERE id = 1;

I get the following error:

  ERROR:  column "directories" of relation "test" is of type text[] but column "directories" of history relation "test_history" is of type text[]

The error is nonsensical to me, both columns have the same type and the error even states this. This only happens when a text[] column is present in the table. It is not necessary to write to that column, it just has to be present. If I don't create the array column, there is no error.

The extension does not mention anything about incompatibility with arrays, and I'd expect that kind of restriction to be large enough to be mentioned. I'm wondering if there is anything wrong with the way I set up the tables, or if I'm missing anything else here. Is there any underlying reason why two text[] array columns would be of different type?

Any idea what actually causes this error and how I could get rid of it?

The problem is versioning.c:412:

 errmsg("column \"%s\" of relation \"%s\" is of type %s (%i) but column \"%s\" of history relation \"%s\" is of type %s (%i)",
                NameStr(attr->attname),
                RelationGetRelationName(relation),
                format_type_with_typemod(attr->atttypid,
                                                                 attr->atttypmod),
                attr->attndims,
                NameStr(history_attr->attname),
                RelationGetRelationName(history_relation),
                format_type_with_typemod(history_attr->atttypid,
                                                                 history_attr->atttypmod), history_attr->attndims)));

When I changed to show attndims, result was:

ERROR:  column "directories" of relation "test" is of type text[] (1) but column "directories" of history relation "test_history" is of type text[] (0)

I found the problem is in heap_openrv (PostgreSQL function) does not correctly fill this field for tables created by LIKE. I do not know how to repair it. But I found a solution:

CREATE TABLE test_history (
  id SERIAL PRIMARY KEY,
  a integer,
  directories text[],
  sys_period tstzrange NOT NULL
);

After that:

CREATE EXTENSION
DROP TABLE
DROP TABLE
CREATE TABLE
CREATE TABLE
CREATE TRIGGER
INSERT 0 1
UPDATE 1
 id | a | directories |                            sys_period
----+---+-------------+-------------------------------------------------------------------
  1 | 1 |             | ["2017-02-07 15:12:16.383449+02","2017-02-07 15:12:16.387419+02")
(1 row)


Disabling binary logging when restoring a compressed MySQL dump I am busy build a slave of an existing database. I don't want it to build bin logs for the data I import before bringing the slave into the same state as master.

This is mostly to save space on importing 100 G of data.

mysqldump somelargedb | gzip > /somewhere/withspace/dump/somelargedb.sql.gz

Uncompressed this file is in the 100 Gb range.
Compressed it is around 2Gb

I found this answer.

https://geert.vanderkelen.org/2009/disabling-binary-logging-when-restoring-a-mysql-dump/

bash $ (echo "SET SESSION SQL_LOG_BIN=0;"; cat dump.sql) > dump_nobinlog.sql

A better solution would have been the following.
Taken from the comments on the above site.
But as the volume of data is rather large I don't want to spend another few hours waiting for the data to be exported. This also does not include compress of the file at dump time.

$ echo "SET SESSION SQL_LOG_BIN=0;" > dumpfile
$ mysqldump .... >> dumpfile

I have adapted it as follows.

echo "SET SESSION SQL_LOG_BIN=0;" | gzip | zcat - /somewhere/withspace/dump/somelargedb.sql.gz | mysql -u root -p somelargedb


Monitoring InnoDB Index Utilization I would like to monitor how many queries are utilizing the indexes. Is there any program that can show me live query performance and index utilization?

Note: I am already aware of slow log file and its usage.

Instead of using the slow log (which are queries that have finished and logged), you may want to poll mysql for long running queries while they are still running.

You may need to try using mk-query-digest or pt-query-digest and poll the processlist.

I learned how to use mk-query-digest from this youtube video as a replacement for the slow log: http://www.youtube.com/watch?v=GXwg1fiUF68&feature=colike

Here is the script I wrote to run the query digest program

#!/bin/sh

RUNFILE=/tmp/QueriesAreBeingDigested.txt
if [ -f ${RUNFILE} ] ; then exit ; fi

MKDQ=/usr/local/sbin/mk-query-digest
RUNTIME=${1}
COPIES_TO_KEEP=${2}
DBVIP=${3}

WHICH=/usr/bin/which
DATE=`${WHICH} date`
ECHO=`${WHICH} echo`
HEAD=`${WHICH} head`
TAIL=`${WHICH} tail`
AWK=`${WHICH} awk`
SED=`${WHICH} sed`
CAT=`${WHICH} cat`
WC=`${WHICH} wc`
RM=`${WHICH} rm | ${TAIL} -1 | ${AWK} '{print $1}'`
LS=`${WHICH} ls | ${TAIL} -1 | ${AWK} '{print $1}'`

HAS_THE_DBVIP=`/sbin/ip addr show | grep "scope global secondary" | grep -c "${DBVIP}"`
if [ ${HAS_THE_DBVIP} -eq 1 ] ; then exit ; fi

DT=`${DATE} +"%Y%m%d_%H%M%S"`
UNIQUETAG=`${ECHO} ${SSH_CLIENT}_${SSH_CONNECTION}_${DT} | ${SED} 's/\./ /g' | ${SED} 's/ //g'`

cd /root/QueryDigest
OUTFILE=QP_${DT}.txt
HOSTADDR=${DBVIP}
${MKDQ} --processlist h=${HOSTADDR},u=queryprofiler,p=queryprofiler --run-time=${RUNTIME} > ${OUTFILE}

#
# Rotate out Old Copies
#

QPFILES=QPFiles.txt
QPFILES2ZAP=QPFiles2Zap.txt
${LS} QP_[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]_[0-9][0-9][0-9][0-9][0-9][0-9].txt > ${QPFILES}

LINECOUNT=`${WC} -l < ${QPFILES}`
if [ ${LINECOUNT} -gt ${COPIES_TO_KEEP} ]
then
        (( DIFF = LINECOUNT - COPIES_TO_KEEP ))
        ${HEAD} -${DIFF} < ${QPFILES} > ${QPFILES2ZAP}
        for QPFILETOZAP in `${CAT} ${QPFILES2ZAP}`
        do
                ${RM} ${QPFILETOZAP}
        done
fi

rm -f ${QPFILES2ZAP}
rm -f ${QPFILES}
rm -f ${RUNFILE}

Make sure

you a user called queryprofiler whose password is queryprofiler and who only has the PROCESS privilege
you put */20 * * * * /root/QueryDigest/ExecQueryDigest.sh 1190s 144 10.64.95.141
 in the crontab to run every 20 minutes (Each profile is 20 min less 10 seconds, Keeps the last 144 copies, and only runs if specfifc DBVIP is present [Alter script to bypass checking for DBVIPs])

The output produces a file with the 20 worst running queries based on the number of times the query was called X avg sec per query.

Here is the sample output of the query profiling summary of mk-query-digest

# Rank Query ID           Response time    Calls   R/Call     Item
# ==== ================== ================ ======= ========== ====
#    1 0x812D15015AD29D33   336.3867 68.5%     910   0.369656 SELECT mt_entry mt_placement mt_category
#    2 0x99E13015BFF1E75E    25.3594  5.2%     210   0.120759 SELECT mt_entry mt_objecttag
#    3 0x5E994008E9543B29    16.1608  3.3%      46   0.351321 SELECT schedule_occurrence schedule_eventschedule schedule_event schedule_eventtype schedule_event schedule_eventtype schedule_occurrence.start
#    4 0x84DD09F0FC444677    13.3070  2.7%      23   0.578567 SELECT mt_entry
#    5 0x377E0D0898266FDD    12.0870  2.5%     116   0.104199 SELECT polls_pollquestion mt_category
#    6 0x440EBDBCEDB88725    11.5159  2.3%      21   0.548376 SELECT mt_entry
#    7 0x1DC2DFD6B658021F    10.3653  2.1%      54   0.191949 SELECT mt_entry mt_placement mt_category
#    8 0x6C6318E56E149036     8.8294  1.8%      44   0.200667 SELECT schedule_occurrence schedule_eventschedule schedule_event schedule_eventtype schedule_event schedule_eventtype schedule_occurrence.start
#    9 0x392F6DA628C7FEBD     8.5243  1.7%       9   0.947143 SELECT mt_entry mt_objecttag
#   10 0x7DD2B294CFF96961     7.3753  1.5%      70   0.105362 SELECT polls_pollresponse
#   11 0x9B9092194D3910E6     5.8124  1.2%      57   0.101973 SELECT content_specialitem content_basecontentitem advertising_product organizations_neworg content_basecontentitem_item_attributes
#   12 0xA909BF76E7051792     5.6005  1.1%      55   0.101828 SELECT mt_entry mt_objecttag mt_tag
#   13 0xEBE07AC48DB8923E     5.5195  1.1%      54   0.102213 SELECT rssfeeds_contentfeeditem
#   14 0x3E52CF0261A7C3FF     4.4676  0.9%      44   0.101536 SELECT schedule_occurrence schedule_occurrence.start
#   15 0x9D0BCD3F6731195B     4.2804  0.9%      41   0.104401 SELECT mt_entry mt_placement mt_category
#   16 0x7961BD4C76277EB7     4.0143  0.8%      18   0.223014 INSERT UNION UPDATE UNION mt_session
#   17 0xD2F486BA41E7A623     3.1448  0.6%      21   0.149754 SELECT mt_entry mt_placement mt_category mt_objecttag mt_tag
#   18 0x3B9686D98BB8E054     2.9577  0.6%      11   0.268885 SELECT mt_entry mt_objecttag mt_tag
#   19 0xBB2443BF48638319     2.7239  0.6%       9   0.302660 SELECT rssfeeds_contentfeeditem
#   20 0x3D533D57D8B466CC     2.4209  0.5%      15   0.161391 SELECT mt_entry mt_placement mt_category

Above this output are histograms of these 20 top worst-performing queries

Example of the first entry's histogram

# Query 1: 0.77 QPS, 0.28x concurrency, ID 0x812D15015AD29D33 at byte 0 __
# This item is included in the report because it matches --limit.
#              pct   total     min     max     avg     95%  stddev  median
# Count         36     910
# Exec time     58    336s   101ms      2s   370ms   992ms   230ms   393ms
# Lock time      0       0       0       0       0       0       0       0
# Users                  1      mt
# Hosts                905 10.64.95.74:54707 (2), 10.64.95.74:56133 (2), 10.64.95.80:33862 (2)... 901 more
# Databases              1     mt1
# Time range 1321642802 to 1321643988
# bytes          1   1.11M   1.22k   1.41k   1.25k   1.26k   25.66   1.20k
# id            36   9.87G  11.10M  11.11M  11.11M  10.76M    0.12  10.76M
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms
# 100ms  ################################################################
#    1s  ###
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `mt1` LIKE 'mt_entry'\G
#    SHOW CREATE TABLE `mt1`.`mt_entry`\G
#    SHOW TABLE STATUS FROM `mt1` LIKE 'mt_placement'\G
#    SHOW CREATE TABLE `mt1`.`mt_placement`\G
#    SHOW TABLE STATUS FROM `mt1` LIKE 'mt_category'\G
#    SHOW CREATE TABLE `mt1`.`mt_category`\G
# EXPLAIN
SELECT `mt_entry`.`entry_id`, `mt_entry`.`entry_allow_comments`, `mt_entry`.`entry_allow_pings`, `mt_entry`.`entry_atom_id`, `mt_entry`.`entry_author_id`, `mt_entry`.`entry_authored_on`, `mt_entry`.`entry_basename`, `mt_entry`.`entry_blog_id`, `mt_entry`.`entry_category_id`, `mt_entry`.`entry_class`, `mt_entry`.`entry_comment_count`, `mt_entry`.`entry_convert_breaks`, `mt_entry`.`entry_created_by`, `mt_entry`.`entry_created_on`, `mt_entry`.`entry_excerpt`, `mt_entry`.`entry_keywords`, `mt_entry`.`entry_modified_by`, `mt_entry`.`entry_modified_on`, `mt_entry`.`entry_ping_count`, `mt_entry`.`entry_pinged_urls`, `mt_entry`.`entry_status`, `mt_entry`.`entry_tangent_cache`, `mt_entry`.`entry_template_id`, `mt_entry`.`entry_text`, `mt_entry`.`entry_text_more`, `mt_entry`.`entry_title`, `mt_entry`.`entry_to_ping_urls`, `mt_entry`.`entry_week_number` FROM `mt_entry` INNER JOIN `mt_placement` ON (`mt_entry`.`entry_id` = `mt_placement`.`placement_entry_id`) INNER JOIN `mt_category` ON (`mt_placement`.`placement_category_id` = `mt_category`.`category_id`) WHERE (`mt_entry`.`entry_status` = 2  AND `mt_category`.`category_basename` IN ('business_review' /*... omitted 3 items ...*/ ) AND NOT (`mt_entry`.`entry_id` IN (53441))) ORDER BY `mt_entry`.`entry_authored_on` DESC LIMIT 4\G

Give it a Try !!!


Why database snapshot creation is faster than normal database backups? Could you tell me any reason why creation of database snapshot is faster than database normal database backups in MS SQL server

Mostly because a snapshot isn't really a backup.

As explained in How database snapshots work

  The snapshot uses one or more
  sparse files. Initially, a sparse file is an essentially empty file
  that contains no user data and has not yet been allocated disk space
  for user data. As more and more pages are updated in the source
  database, the size of the file grows. When a snapshot is taken, the
  sparse file takes up little disk space.

A snapshot doesn't actually contain any data until you start making changes to the original database.  Basically any time you change a page in the database that original page gets written to the snapshot.  If you then make another change to the same page nothing needs to happen since the original already exists in the snapshot. Because of this the size of the snapshot will be from 0 to the size of the database at the time of the snapshot.

So when you restore back to the snapshot it just writes back those changed pages.

A backup (a full one at least) is a copy of the database written to a file.

When you restore from backup it writes ALL of the pages, not just changes (again assuming a full backup).

That's why creating/recovering from a snapshot is so much faster.

Here is some further reading:

Database Snapshots


Create LOG files when creating multiple DATA files for tempdb? I've a question that I can't seem to find an answer to (and I can't test it out for a few weeks so I'm doing research now during my downtime).

Should I be creating corresponding LOG files for every DATA file I create?

eg. if I create tempdev3.mdf, should I also create templog3.ldf?

Or do the LOG files get created automatically as and when needed by the SQL Server?

I'm using MS SQL 2008 R2 EE but I think the answer equally applies to 2005 and 2012.

No, SQL Server only writes to 1 LOG file at a time. So it doesn't help to create multiple log files for better IO performance. It will use the first file and only when that one is full and no VLFs are cleared it will continue in the second file, etc. etc.

You might want to create a second log file if for example you run into an emergency situation where you run out of disk space on disk X and you want SQL server to continue logging in a second file on disk Y.


Select distinct value from row if condition exists I am new to SQL and I am having trouble constructing a SELECT statement.

What I would like to do is select distinct parts that have a location of A1 if it exists. If a part has no locations of A1 I would like to select distinct material with no condition of location.

Is there a way to make this select statement 1 query or do I need to break it up in selecting the distinct condition true values and selecting the distinct condition false values?

Sample data:

+------+------+----------+
| PART | TYPE | Location |
+------+------+----------+
| A    |    1 | A1       |
| A    |    2 | A1       |
| A    |    1 | A2       |
| A    |    2 | A2       |
| B    |    1 | A2       |
| B    |    1 | A3       |
+------+------+----------+

How about a little 2 in 1 using CTE?

;with cte as (
    select top 1 loc = 1
    from parts
    where Location = 'A1'
)
select distinct Part --, Type, Location
from parts left join cte on 1=1
where loc is null or Location = 'A1';


SELECT in federated table is to slow When i test select * from myFederatedTable (for test there is 50k rows) it take 0.3732 seconds
now I have two federated tables with data < 20000 in both tables, and have query like :

SELECT i.id_news, i.id_news_type, i.id_agency, i.add_date, i.update_date, j.title, k.image_name_latin, m.folder_path
FROM tbl i
INNER JOIN tbl_languages j on i.id_news = j.id_news and j.lang = 'en'
INNER JOIN federated_Table1 k on i.id_image_groups = k.id_image_groups
INNER JOIN federated_table2 m on k.id_folder_groups = m.id_folder_groups
ORDER BY i.update_date DESC
LIMIT 14

It takes 16 seconds

Without joins to federated tables

SELECT i.id_news, i.id_news_type, i.id_agency, i.add_date, i.update_date, j.title
    FROM tbl i
    INNER JOIN tbl_languages j on i.id_news = j.id_news and j.lang = 'en'
    ORDER BY i.update_date DESC
    LIMIT 14

0.0496 seconds.
I have few questions

1.Is there any way to optimize it ?
2.when i select from federated table (really i select from main database?) or when i join to federated table I really join to main database?
3.Task is copy data from one database tables to another database tables. Is there best way to replicate this 2 tables for quick
selection ?

Federated tables are going to be inherently slow, especially when doing joins.  It's basically copying down an entire copy of the table for each join.

Federated tables should simply be avoided.


Time Out issue, can I set the Ping Property? I have some reports which I access through the Report Manager interface and view using a Report Viewer. If I leave a report open in the viewer then my session never times out. I believe this is because the viewer "pings" just before the session is due to time out, thus keeping the session alive.

This isn't very desirable and I would like to somehow set the viewer so that this "ping" doesn't happen. I have been trying to find a way to do this and have been coming up short as there is no obvious reference to this property in the config files or the site settings. I have found a number of references on the web to people having timeout issues using the reportviewer control in a .NET application, but none just using SSRS.

Is it possible to change this "ping" setting somehow? If so could somebody be so kind as to guide me through it as I am struggling.

You are probably looking for the KeepSessionAlive property of the ReportViewer control. To quote the linked MSDN page:

  ReportViewer.KeepSessionAlive Property

  Property Value
  true to keep the user session from expiring; otherwise, false. The default is true.

  Setting this property to true causes the ReportViewer control to keep the ASP.NET session from expiring if session is enabled in your ASP.NET application. Also, in remote processing mode, it causes the ReportViewer control to keep the report server session from expiring.

You should be able to set this property to false to prevent the keep-alive calls.


How many cores are used for serial plans in SQL Server? I understand that a parallel plan will use (at most) as many cores as set up in MAXDOP configuration (per parallel zone).

I have configured my MAXDOP to 4 and my “cost threshold for parallelism” to 55. So, my question is...will all the serial plans with a cost less than 55 use only the first core?

If that’s the case...if I have mostly low-cost queries, my 1st core would be exhausted while the rest of the cores would be idle.

Your query execution plan can, depending on the complexity, contain both serial and parallel components.

In SQL Server speak your complex or simple query/batch is called a request and as such is monitored via the sys.dm_exec_requests management view.

  Returns information about each request that is executing in SQL Server.

The request is then split into different workloads which in SQL Server is represented as a task. A request can be a number of sequential or parallel tasks which all belong to the same request. Task are monitored in the sys.dm_os_tasks management view.

  Returns one row for each task that is active in the instance of SQL Server. A task is the basic unit of execution in SQL Server.

The tasks in turn are handed over to worker threads which are logical representations of Operating System threads. A sqlsrvr.exe process will have a number of threads which can perform computation on the processor (core).

When the thread is waiting for example on data being returned, then it would be better if the thread could be freed to perform another task while the current task is waiting. This is performed by the scheduler which is ...

  ...also known as SOS scheduler, manages worker threads that require processing time to carry out work on behalf of tasks. Each scheduler is mapped to an individual processor (CPU). The time a worker can remain active in a scheduler is called the OS quantum, with a maximum of 4 ms. After its quantum time expires, a worker yields its time to other workers that need to access CPU resources, and changes its state. This cooperation between workers to maximize access to CPU resources is called cooperative scheduling, also known as non-preemptive scheduling. In turn, the change in worker state is propagated to the task associated with that worker, and to the request associated with the task.

The threads are not bound to a specific core/processor unless process affinity has been turned on a the OS level.

(emphasis mine)

  By default, each instance of SQL Server starts each thread, and the operating system distributes threads from instances of SQL Server among the processors (CPUs) on a computer, based on load. If process affinity has been enabled at the operating system level, then the operating system assigns each thread to a specific CPU. In contrast, the SQL Server Database Engine assigns SQL Server worker threads to schedulers that distribute the threads evenly among the CPUs.

  To carry out multitasking, for example when multiple applications access the same set of CPUs, the operating system sometimes moves worker threads among different CPUs.

Reference: Thread and Task Architecture Guide (Microsoft | SQL Docs)

Answering Your Question

  So, my question is...will all the serial plans with a cost less than 55 use only the first core?

No, your serial tasks (not plans) will be executed on any available core depending on the overall workload of the system.


MySQL: ORDER BY useless? CREATE TEMPORARY TABLE tt;
INSERT INTO tt; many times
SELECT FROM tt ORDER BY id LIMIT ?,1000;

The temp table is created at the beginning of the script having an "id" auto_increment column (and others). Part 1 of the script fills up the table using various SELECTs. Part 2 should process all selected rows in chunks of 1000 but will never ever write the table again.

I assume that these two commands are the same

SELECT FROM tt ORDER BY id LIMIT ?,1000;
SELECT FROM tt LIMIT ?,1000;

but is this true? Is there any chance that mySQL will retrive the rows in a different order on multiple SELECTs even if the table is not written in between?

Sorting the temp table is the longest part while reading it and I'd be happy to get rid of the ORDER BY part but I don't want to miss a record because the order changed between SELECT LIMIT 0,1000 and SELECT LIMIT 1000,1000.

This isn't directly related to ORDER BY, but be careful using the LIMIT N,M pattern.  This is often used for paginating results, e.g.

... limit 0,10;
... limit 10,10;
... limit 20,10;

Each time through mysql will "select" the first N rows and just return the last M to the client. As you go deeper and deeper you're selecting out more and more of rows, only to be thrown away.

In short each "page" takes longer to load.  If your datasets are small and queries are using indexes well this may seem inconsequential, but the effect will become evident as you grow.  I feel it's generally better to put these constraints in the where clause.  e.g.

... where id between 0 and 10;
... where id between 11 and 20;
... where id between 21 and 30;

Finally, if you're worried about the ordering to the limiting effectively creates the correct range restrictions for you then this concern is completely eliminated to begin with.


How to create new column with value from other table I have a table called 'menus':

 menu_id | sort | page_id | parent_page_id
---------|------|---------|----------------
 1       | 0    | 1       | NULL
 1       | 1    | 2       | 1
 1       | 2    | 3       | 1
 1       | 3    | 4       | 1

And a table called 'pages':

 id | url
----|-----------
 1  | vehicles
 2  | car
 3  | bicycle
 4  | bus

I chose to store the data into separate tables because one page can be included in multiple menus.

Now I want to do a query from which the result is:

 menu_id | page_id | sort | url      | parent_url
---------|---------|------|----------|------------
 1       | 1       | 0    | vehicles | NULL
 1       | 2       | 1    | car      | vehicles
 1       | 3       | 2    | bicycle  | vehicles
 1       | 4       | 3    | bus      | vehicles

The columns menu_id, page_id, sort and url can easily be retrieved using a LEFT JOIN:

SELECT
    menus.menu_id,
    pages.id AS page_id,
    menus.sort,
    pages.url
FROM menus
LEFT JOIN pages
ON menus.page_id = pages.id
ORDER BY sort

But how do I get the column parent_url in the query result?

You will want to JOIN to the pages table a second time to get the parentURL.  The first time that you join to pages use the page_id, then the second join will use the parent_page_id:

SELECT
    m.menu_id,
    p1.id AS page_id,
    m.sort,
    p1.url,
    p2.url parentUrl
FROM menus m
LEFT JOIN pages p1
  ON m.page_id = p1.id
LEFT JOIN pages p2
  ON m.parent_page_id = p2.id
ORDER BY m.sort;

See SQL Fiddle with Demo.  This gives a result:

| MENU_ID | PAGE_ID | SORT |      URL | PARENTURL |
|---------|---------|------|----------|-----------|
|       1 |       1 |    0 | vehicles |    (null) |
|       1 |       2 |    1 |      car |  vehicles |
|       1 |       3 |    2 |  bicycle |  vehicles |
|       1 |       4 |    3 |      bus |  vehicles |


Auditing which tables get joined most often I'm trying to establish some metrics associated with which tables in our database get joined together the most often. How can I go about accomplishing this?

edit: I should explain my reasoning; we are moving to a microservices-based architecture, and I would like to have join data in order to best inform the domain-driven-design.

Seeing which tables need to be joined most often would allow me to design certain data layer microservices in a way that they will always have access to easy joins of the most frequently-joined tables.

So if Table1 and Table2 are ALWAYS being joined, I can assess this and have them controlled by the same service. If not, I'll have different services control them.

I don't know if you can differentiate joins vs straight queries, but you can use index activity (what version of SQL Server, by the way?) to get an idea of your "hot" tables.  This query should get you started, I use it to identify heavily fragmented or heavily used indexes, but it should still get you there.  I don't think it will work with heaps that well, but you really shouldn't have many of those (or any) without a good reason.

I've written this mostly myself but of course cribbed and borrowed bits and pieces from others work.  My thanks go out to everyone who has ever posted code to the internet.

/**
    Author: Jonathan Fite

    Index Analyzer

    Requires CONTROL permission in the database, VIEW DATABASE STATE and VIEW SERVER STATE

*/

/** Initialize Variables.

    @DatabaseID - The database we want to examine, must be specified, but defaults to the current database...
    @IndexScanLevel - The level of detail you want to scan to.
        - LIMITED - This is the default or NULL value, only looks at the parent level of the index.
        - SAMPLED - Detailed look at ~1% of the pages in the index. (Heaps and indexes smaller than 10K Pages use DETAILED)
        - DETAILED - full scan of all pages, heavy IO
    @MinFragmentation - If you want to see only indexes fragmented to a certain level.
    @MinPageCount - To limit to indexes that are over a certain page count.  Small indexes don't typically benefit
        from being defragmented.  A good number to start with is 100

*/

DECLARE @DatabaseID INT;
SET @DatabaseID = (SELECT DB_ID());

DECLARE @IndexScanLevel NVARCHAR(10);
SET @IndexScanLevel = 'SAMPLED';

DECLARE @MinFragmentation DECIMAL(4,2);
SET @MinFragmentation = 0.0;

DECLARE @MinPageCount INT;
SET @MinPageCount = 100;

/** Begin Work on the Query *********************************************************/

--Get Column List Information
DECLARE @ColumnList AS TABLE
    (
    object_id INT NOT NULL
    , index_id  INT NOT NULL
    , IndexedColumns VARCHAR(8000) NULL
    , IncludedColumns VARCHAR(8000) NULL
    );

WITH CTE_ColumnList AS
(
    SELECT IC.object_id
        , IC.index_id, C.name
        , IC.key_ordinal AS Position
        , DisplayName = C.name + CASE WHEN IC.is_descending_key = 1 THEN ' DESC' ELSE '' END
        , IC.is_included_column
    FROM sys.index_columns IC
        INNER JOIN sys.columns C ON C.object_id = IC.object_id AND IC.column_id = C.column_id
)
INSERT INTO @ColumnList
        ( object_id ,
          index_id ,
          IndexedColumns ,
          IncludedColumns
        )
SELECT DISTINCT
    C.object_id
    , C.index_id
    , IndexedColumns = STUFF((SELECT ', ' + E.DisplayName FROM CTE_ColumnList E WHERE E.object_id = C.object_id AND E.index_id = C.index_id AND E.is_included_column = 0 ORDER BY E.Position FOR XML PATH('')), 1,1,'')
    , IncludedColumns  = STUFF((SELECT ', ' + E.DisplayName FROM CTE_ColumnList E WHERE E.object_id = C.object_id AND E.index_id = C.index_id AND E.is_included_column = 1 ORDER BY E.Position FOR XML PATH('')), 1,1,'')
FROM CTE_ColumnList C;

SELECT DB_NAME(IPS.database_id) AS DatabaseName
    , SchemaName = S.name
    , ObjectName = O.name
    , O.type_desc AS ObjectType
    , I.name AS IndexName
    , IPS.index_type_desc
    , IPS.alloc_unit_type_desc
    , IPS.avg_fragmentation_in_percent
    , IPS.avg_page_space_used_in_percent
    , IPS.page_count
    , PageSize_MB = (IPS.page_count/128.0)
    , I.is_unique
    , I.fill_factor
    , I.is_disabled
    , I.has_filter
    , I.filter_definition
    , IUS.user_seeks
    , IUS.user_scans
    , IUS.user_lookups
    , IUS.user_updates
    , UsageActivityTotal = IUS.user_seeks + IUS.user_scans + IUS.user_lookups + IUS.user_updates
    , IOS.leaf_insert_count
    , IOS.leaf_update_count
    , IOS.leaf_delete_count
    , OperationalActivityTotal = IOS.leaf_insert_count + IOS.leaf_update_count + IOS.leaf_delete_count
    , LastAccessed = IUS_LastUsed.LastUsed
    , CL.IndexedColumns
    , CL.IncludedColumns
FROM sys.dm_db_index_physical_stats(@DatabaseID, NULL, NULL, NULL, @IndexScanLevel) AS IPS
    INNER JOIN sys.objects O ON O.object_id = IPS.object_id
    INNER JOIN sys.schemas S ON S.schema_id = O.schema_id
    INNER JOIN sys.indexes I ON I.object_id = IPS.object_id AND I.index_id = IPS.index_id
    LEFT OUTER JOIN @ColumnList CL ON CL.object_id = IPS.object_id AND CL.index_id = IPS.index_id
    LEFT OUTER JOIN sys.dm_db_index_usage_stats IUS ON IUS.database_id = IPS.database_id AND IUS.object_id = IPS.object_id AND IUS.index_id = IPS.index_id
    LEFT OUTER JOIN sys.dm_db_index_operational_stats(@DatabaseID, NULL, NULL, NULL) IOS ON IOS.object_id = IPS.object_id AND IOS.index_id = IPS.index_id
    LEFT OUTER JOIN (
                    SELECT S.database_id
                        , S.object_id
                        , S.index_id
                        , MAX(S.lastused) AS LastUsed
                    FROM sys.dm_db_index_usage_stats UNPIVOT (lastused FOR nlastused IN (last_user_seek, last_user_scan, last_user_lookup, last_user_update)) AS S
                    GROUP BY S.database_id
                        , S.object_id
                        , S.index_id
                    ) IUS_LastUsed ON IUS_LastUsed.database_id = IPS.database_id AND IUS_LastUsed.object_id = IPS.object_id AND IUS_LastUsed.index_id = IPS.index_id
WHERE ISNULL(O.is_ms_shipped, 0x0) = 0x0 --Exclude System Generated Objects
    AND ISNULL(I.is_disabled, 0x0) = 0x0 --Exclude Disabled Indexes
    AND ISNULL(IPS.page_count, 0) >= @MinPageCount --Only worry about indexes with worthwhile page counts
    AND ISNULL(IPS.avg_fragmentation_in_percent, 0.0) >= @MinFragmentation --Only display indexes that exceed fragmentation threshold
ORDER BY IPS.avg_fragmentation_in_percent DESC;


How to set ENVREFERENCE when creating a SQL Server job SQL Server 2014 (if it matters)

When I am migrating SSIS packages and jobs between environments, I need to update the /ENVREFERENCE parameter to the command for each job step.  Is there anyway to find the correct value and script the job step to use this value.

Pseudocode

Query for ENVREFERENCE where Environment name = N'QA' -- how to do this?
EXEC sp_update_jobstep
    @job_name = N'My Job',
    @step_id = 30,
    @command= first part + /ENVREFERENCE= value from query + last part;

I've used code like the following in my job scripts to associate the step with the proper environment:

--Call SSIS package. Figure out the server and Environment Reference variables
DECLARE @EnvReference   INT = (SELECT TOP 1 EnvironmentReferenceID FROM SSISDB.rpt.Package WHERE PackagePathName = '\SSISDB\Folder\Project\Package.dtsx\');
DECLARE @Command        VARCHAR(2000) = FORMATMESSAGE(N'/ISSERVER "\"\SSISDB\Folder\Project\Package.dtsx\"" /SERVER %s /ENVREFERENCE %i /Par "\"$ServerOption::LOGGING_LEVEL(Int16)\"";1 /Par "\"$ServerOption::SYNCHRONIZED(Boolean)\"";True /CALLERINFO SQLAGENT /REPORTING E', @@SERVERNAME, @EnvReference);
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Job Name',
                @step_id=1,
                @on_success_action=3,
                @on_fail_action=3,
                @subsystem=N'SSIS',
                @command=@Command,
                @database_name=N'master',
                @output_file_name=N'E:\Log\Job.log',
                @flags=40;

By the way the view rpt.Package is just this:

SELECT  PackagePathName = FORMATMESSAGE('\SSISDB\%s\%s\%s\', F2.name, PJ.name, PK.name),
    EnvironnmentPathName = FORMATMESSAGE('\SSISDB\%s\%s', F.name, E.name),
    EnvironmentReferenceID = ER.reference_id,
    ProjectFolder = F.name,
    Project = PJ.name,
    Package = PK.name,
    EnvironmentFolder = F2.name,
    Environment = E.name
FROM    catalog.folders AS F
    INNER JOIN catalog.environments AS E ON E.folder_id = F.folder_id
    INNER JOIN catalog.environment_references AS ER ON (ER.reference_type = 'A'
            AND ER.environment_folder_name = F.name
            AND ER.environment_name = E.name)
        OR (ER.reference_type = 'R'
            AND ER.environment_name = E.name)
    INNER JOIN catalog.projects AS PJ ON PJ.project_id = ER.project_id AND PJ.folder_id = F.folder_id
    INNER JOIN catalog.packages AS PK ON PK.project_id = PJ.project_id
    INNER JOIN catalog.folders AS F2 ON F2.folder_id = PJ.folder_id

Make sure to replace the references: Folder, Project, Package, Job with your own strings.


How to select specific items from table A which appear with all items of some column in table B? I have 2 tables with the following schemas:

t1(id, type) where id and type are the primary key

t2(type, rate) where type is the primary key

I want to find all id's which appear with all types from t2.

For example given the tables:

t1:

 id | type
 ---------
  1 | a
  1 | b
  1 | c
  2 | a
  2 | b

and t2:

 type | rate
 -----------
    a | 100
    b | 100
    c | 200

We can see that there're 3 unique types in t2 and only id 2 from t1 appears with all of them.

I figured out a way to get the id's by counting the number of unique types and then grouping t1 by id's and selecting only those whose count equals of the total number of types from t2.

But I was wondering if there're other logical constructs like in/not in, exists or Cartesian product which can also be used here with more elegance?

I dont think there is any logical constructs like in/not in.

But you can check array equality like below,

select id from
(select array_agg(type)as type, id from t1  group by id) table_1
join
(select array_agg(type)as type from t2)table_2
on table_1.type=table_2.type;

May be this is useful to you :)


Set encoding from within SQL*Plus I have a large set of files with SQL commands that I run with a SQL*Plus script that uses the @@ operator. I run the script on third-party computers (or even send it by e-mail) so I'd like to make it as self-contained as possible.

Files are encoded as Win-1252. Currently, I need to set the NLS_LANG environment variable before running the script:

C:\> SET NLS_LANG=SPANISH_SPAIN.WE8MSWIN1252

C:\> echo exit | sqlplus foo/bar@//example.com/xe @install.sql

This works but I wonder if it's possible to change the client encoding with a SQL or SQL*Plus command. (Or at least read the value and abort execution if it doesn't match.)

NLS_LANG can't be changed from inside a session, however other settings can.

You can't change the character set once a database connection has been established (ie: the 2nd part of NLS_LANG), but you can change the language with:

alter session set NLS_LANGUAGE=SPANISH

... and the territory with:

alter session set NLS_TERRITORY=SPAIN


SQL vs NoSQL: Fetching All records in DynamoDB vs SQL Database I am designing database for a project which requires frequent querying of all the records. The total number of records will be less than 500K. One of the databases I am considering is AWS DynamoDB where scan operation meant for this is very inefficient. What I fail to understand is whether it is inefficient in a SQL based database or there is one better than other for frequent calls for fetching all records.

Scan operations aren't usually optimal but 500,000 records isn't something too crazy for the standard RDBMS. For example, I've seen Microsoft SQL Server serve up 1 million records on a decently wide table (about 100 columns) in a few seconds on a pretty basic server (4 core, 32 GB of RAM).
I'm not an expert on NoSQL databases or DynamoDB but I've researched into its use cases in the past and my understanding is it's most performant when caching smaller amounts of data, and recalling smaller "singletonish" datasets, such as paging product information on a catalog, or a specific user's settings. So I would say NoSQL databases (or at least DynamoDB) is likely not great for scanning large amounts of data, but a modern RDBMS should be able to handle scanning a few million records on basic architecture these days.


Filter Table by a Historical 'Last Day of the Month' I have a daily appending table, that I need a formula for that would filter the table results to just the last day of any given historical month.  For example:

Table:

DwDate            Balance
20181231           $10
20190131           $80
20190228           $75
20190331           $50

99% of the time I just need the MAX(DwDate), which would be:

@MaxDate = (SELECT MAX(DwDate) FROM dbo.TABLE)

However, occasionally I need a formula that would allow me to go back 1 or 2 months:

@MaxDate - 2 Months

To subract a month from a date you can use dateadd().

For example

SET @MaxDate = dateadd(month, -1, @MaxDate);

would subtract one month from your @MaxDate. That would keep the day of the month though. (Unless this day doesn't exists for the month, then it's the last day of the month, so e.g. the 31th day might become the 30th.)

To get the date of the last day of a month a date is in eomonth() can be used.

SET @MaxDate = eomonth(@MaxDate);

would set @MaxDate to the last day of the month @MaxDate was previously in.

eomonth() optionally takes a second argument, which is an integer to add to the month. So to set @MaxDate to the last day of the previous month of @MaxDate you might use

SET @MaxDate = eomonth(@MaxDate, -1);


Why do/should DBMS cache at the application layer? This article describes DBMS as caching data at the application layer, which is why they often use Direct IO to bypass the file buffer cache.

My question is: what benefits are provided by the DBMS caching information at the application layer? How are these benefits affected when the DBMS is on a server that is remote to the application?

Databases typically implement a lot of the OS style functionality themselves. At a high level, this is done to improve performance and scalability.

To give you some examples:

Operating system file caches are generic, least frequently used, style caches. A database needs much finer control of what goes into the cache and what does not. For example, when you calculate hash tables for queries, you want to give them preference over file data while the query runs (it is cheaper to bring in some new file data than to page out hash tables). A database also need to be in very careful control of the memory allocator to avoid fragmenting the heap. Most operating systems are simply not up to the task of handling high the speed allocations that databases need and database typically implement their own memory manager, purpose build for the database engine.

Operating system caches also don't guarantee persistence unless you call flush in Linux or ask for unbuffered I/O in Windows. To guarantee consistency and ACID properties, a database needs much finer control over when data is on disk and when it is cached. Because of this, there is very little benefit to using synchronous, buffered I/O.

Furthermore, most OS caches are relatively poorly implemented. Windows file system caches only got really good NUMA style scale support around Windows 2008 and Linux still has very poor file system performance at high core count. FreeBSD doesn't even know what NUMA is. Since database need to run on very large servers (in some cases up to 32 CPU sockets) database vendors often implement their own caching data structures that are much more scalable than what the OS provides by default.

And last but not least: Database are very hungry for I/O and can drive the file system much harder than a typical file server. Relying on traditional, synchronous I/O is simply too slow for most database and they instead use aggressive, core affinitised, asynchronous I/O completion to gain scale. The I/O subsystem of database tends to be much more advanced than what a standard OS provides.

In general, you will find much more advanced control of machine resource in database source code than in operating system kernels. In fact, most high scale database implement their own user space kernel to avoid using the operating system kernel primitives. If you see a database do a lot of kernel transitions, start looking around for a better product.

Great Question!


Turn off (suppress) Msg 3013 "BACKUP DATABASE is terminating abnormally" When backup operation fails, it produces 2 error msgs

Msg 911, Level 16, State 11, Line 22
Database 'not_existing_db' does not exist. Make sure that the name is entered correctly.

Msg 3013, Level 16, State 1, Line 22
BACKUP DATABASE is terminating abnormally.

It is making it very hard to capture the "root cause" error message (example 911) instead of that generic 3013 message

Is there any way to suppress message 3013 so SQL Server does not throw it anymore ?

There is no way to "suppress" the error, nor would I personally want to. The error is letting you know something happened and it's up to you to go figure out what that something was.

In your example, it says the database doesn't exist. Let's say you lost connectivity to disks, or there was corruption, or ... ad nausea. In this way, you can key off of the 3013 error as a general "backup database issue" (alert) and then investigate further.

Assuming you could suppress the error message, you'd either have to have an alert or line item for each individual error that could possibly happen or just go about your day until someone or something complains. Either way, not so good.


MySQL Master Slave Replication where master is not allowed to talk to slave I want to set up an Master Slave Replication between 2 different servers. The specialty is, that the Master is not allowed to talk to the slave, but the slave can talk with the master. I can not change slave to master, because the data inserted has to be on the master which can not talk (ping) with the slave. Can anyone tell me if I have to look at something when setting it up or can provide a guide which works good? Thanks in advance

All that is required is for the slave to be able to establish and maintain a connection on the master's MySQL TCP port (3306 by default).  There is no need for the master to be able to establish a connection to the slave.  If you can telnet from the slave to port 3306 (or whatever port mysqld is running on) on the master, that should be all the networking you need.

There are a lot of guides, but the best one is the MySQL manual, see Chapter 17 Replication.


runuser error on CentOS 7 Postgres 9.4 I'm having trouble getting Postgres 9.4 working on a clean installation of CentOS 7 (fully patched) when using a non-default data directory.

It's taken me a while to get here using various articles but now I'm stumped.  The directory I want the database in is /opt/pgsql/data/  Here's what I've done:

disable SELinux and reboot

yum install postgresql94-server postgresql94-contrib pgadmin3_94
sudo mkdir -p /opt/pgsql/data
sudo chown -R postgres:postgres /opt/pgsql/
sudo chmod -R 700 /opt/pgsql/
sudo cp /usr/lib/systemd/system/postgresql-9.4.service /etc/systemd/system/postgresql-9.4.service
sudo vi /etc/systemd/system/postgresql-9.4.service

change Environment=PGDATA=/var/lib/pgsql/9.4/data/ to Environment=PGDATA=/opt/pgsql/data

sudo systemctl daemon-reload
sudo vi /var/lib/pgsql/.pgsql_profile

add: PGDATA=/opt/pgsql/data

The next command is the one that fails:

sudo su - postgres -c '/usr/pgsql-9.4/bin/postgresql94-setup initdb'

It throws this error: Initializing database ... failed, see /var/lib/pgsql/9.4/initdb.log

Looking in the log file I see this: runuser: may not be used by non-root users

I've tried su-ing to postgres with the same result (e.g. sudo su - postgres -c '/usr/pgsql-9.4/bin/postgresql94-setup initdb')

I've also checked the $PGDATA variable is correctly set when becoming the 'postgres' user.

Any pointers would be very welcome.

The /usr/pgsql-9.4/bin/postgresql94-setup script needs to be executed as root.

It detects the data directory parsing the service file.

This is the full command help:

Usage: postgresql94-setup {initdb|upgrade} [SERVICE_NAME]

Script is aimed to help sysadmin with basic database cluster administration.

The SERVICE_NAME is used for selection of proper unit configuration file; For
more info and howto/when use this script please look at the docu file
$README_RPM_DIST.  The 'postgresql'
string is used when no SERVICE_NAME is explicitly passed.

Available operation mode:
  initdb        Create a new PostgreSQL database cluster.  This is usually the
                first action you perform after PostgreSQL server installation.
  upgrade   Upgrade PostgreSQL database cluster to be usable with new
                server.  Use this if you upgraded your PostgreSQL server to
                newer major version (currently from $PREVMAJORVERSION \
to $PGMAJORVERSION).

Environment:
  PGSETUP_INITDB_OPTIONS     Options carried by this variable are passed to
                             subsequent call of \`initdb\` binary (see man
                             initdb(1)).  This variable is used also during
                             'upgrade' mode because the new cluster is actually
                             re-initialized from the old one.
  PGSETUP_PGUPGRADE_OPTIONS  Options in this variable are passed next to the
                             subsequent call of \`pg_upgrade\`.  For more info
                             about possible options please look at man
                             pg_upgrade(1).
  PGSETUP_DEBUG              Set to '1' if you want to see debugging output.


Normalized Database Diagram So I am working on my DB design skills, and have tried to create a normalized MySQL database. Here is the diagram:

The reason I added the Address table is to break the address fields into separate columns, making the geography more searchable (i.e. search for zipcode).
Employer to Post will be a 1 (employer) to many (post(s))
My idea for position, since there will be a set number of positions would be to seed a table (Position) and store the position id in the Post table
Location works under the same premise of Address on the Employer table, however it may vary from the address on the employer table, earning it it's own table.
Finally, Industry will be seeded just like the Position table.

I would appreciate constructive criticism, as I am still working on becoming better at my schema design.

When two tables have the some column structure (as Location and Address do here) that is almost always an indication that they should be the same table (conceptually and logically, even if separated physically for performance reasons).

So ask yourself, "What is the design assumption that leads me to think that Address and Location are separate entities rather than the same one?"

Answer:
The relationship between Employer and Address is 1-N; but the relationship between Post and Location is 1-1. Therefore, each Address must have a FK to the Employer, and each Post must have a FK to the Address, with a transitive relationship to the Employer via the Address table.


Looking for solution to add double quotes in field which has commas in SSIS I have a field called something like TYPE & the values are variant in it. I want the SSIS to export the result to CSV & while exporting, if it finds the , in column TYPE only that line/value should be double quoted.
Eg:

TYPE
Begin Bag 1,000 mL
Miscellaneous 20ml
Final 2,000 mL
Begin Bag 1 mL

the result should be (in the csv format exported):
TYPE
"Begin Bag 1,000 mL",
Miscellaneous 20ml,
"Final 2,000 mL",
Begin Bag 1 mL

I tried using the Derived column "\""+ TYPE +"\"" but that just adds Double quotes to every value for column TYPE.

Please help!

THanks,
Atulya

You should be able to use SSIS CASE syntax in your derived column node as follows:

FINDSTRING(TextLine,",",1) > 0 ? "\"" + TextLine + "\"" : TextLine

The expression basically says - if a comma is found in TextLine, THEN wrap it in double quotes ELSE just keep Textline


MySQL does not read user specific options in ~/.my.cnf I want the server to run an initial SQL query every time it starts:

[mysqld]
init-connect='SET NAMES utf8mb4'

and I want it to be only user specific not globally. so I created a .my.cnf in my user's home directory(~/.my.cnf) but when I try to see if it has affected the server it does not show my added setting and it seems that my settings are being ignored by the server:

    mysqld --print-defaults
------------------------------------
--user=mysql
--pid-file=/var/run/mysqld/mysqld.pid
--socket=/var/run/mysqld/mysqld.sock
--port=3306
--basedir=/usr
--datadir=/var/lib/mysql
--tmpdir=/tmp
--lc-messages-dir=/usr/share/mysql
--skip-external-locking
--bind-address=0.0.0.0 --

key_buffer_size=16M
--max_allowed_packet=16M
--thread_stack=192K
--thread_cache_size=8
--myisam-recover-options=BACKUP
--query_cache_limit=1M
--query_cache_size=16M
--log_error=/var/log/mysql/error.log
--expire_logs_days=10
--max_binlog_s

ize=100M
--default_password_lifetime=0

also this is the result of  SHOW VARIABLES LIKE 'character_set_%'; :

I want theses params to be set to 'utf8mb4' wach time the server boots.

character_set_client
character_set_results
character_set_connection

also I should say that I use Laravel Vagrant as my development environment

init-connect is ignored when you connect as the MySQL root user.

Check the file config/database.php for Laravel.  It needs to say something like: 'mysql' => [..., 'charset' => 'utf8mb4', 'collation' => 'utf8mb4_unicode_ci', ...]


Prevent SSMS from seeing the server's file system I have several users who share an MS SQL server 2017 under my administration. They should not see (or even be aware) of the other users and their data on that server. Each user has his own database. They can do whatever they want with their database.

I'm using SQL Server's Partial Containment feature to lock the users in place. The logins are created inside the database. This works well, as they don't see other user accounts or databases this way. The DB logins are added to a Database Role that I create with this command:

USE dbname
CREATE ROLE dbrole
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE TABLE, CREATE VIEW, ALTER ANY SCHEMA TO dbrole
DENY EXECUTE TO dbrole

I freshly create a db login account and only add it to said role. The user has no other permissions (that I'm aware of).

The only problem left is that SSMS is still able to browse the server's file system. If I right click on the database and choose Tasks -> Restore -> Database, then select Device: -> [...] and Add a file. This allows SSMS to browse the server's file system, which I would like to deny. The user cannot actually restore the DB, but he can browse the file system.

This question here suggests that SSMS is using the stored procedures xp_fixeddrives, xp_dirtree and xp_fileexist. However, those stored procedures return empty results when executed as a user with the permissions of said group. I've read that this is the behaviour when a user is not a member of the sysadmin role. This already confuses me slightly since I explicitely deny EXECUTE to the dbrole, yet the user can still execute the stored procedures. But still, when browsing the file system through SSMS, it is not empty.

Where does SSMS get the file system information from and how can I prevent this?

edit: I also just noticed that SSMS is able to retrieve a list of all DB backups existing on the server for all databases. Again, I don't how it gets this information and how I can prevent it.

Tracing the queries

When tracing the queries executed, below query is found that lists the folders on the drives one by one.

declare @Path nvarchar(255)
declare @Name nvarchar(255)

select @Path = N'D:\'

select @Name = null;

        create table #filetmpfin (Name nvarchar(255) NOT NULL, IsFile bit NULL, FullName nvarchar(300) not NULL)
        declare @FullName nvarchar(300)
        if exists (select 1 from sys.all_objects where name = 'dm_os_enumerate_filesystem' and type = 'IF' and is_ms_shipped = 1)
        begin
          if (@Name is null)
          begin
              insert #filetmpfin select file_or_directory_name, 1 - is_directory, full_filesystem_path from sys.dm_os_enumerate_filesystem(@Path, '*') where [level] = 0
          end
          if (NOT @Name is null)
          begin
            if(@Path is null)
              select @FullName = @Name
            else
              select @FullName = @Path  + convert(nvarchar(1), serverproperty('PathSeparator')) + @Name
              create table #filetmp3 ( Exist bit NOT NULL, IsDir bit NOT NULL, DirExist bit NULL )
              insert #filetmp3 select file_exists, file_is_a_directory, parent_directory_exists from sys.dm_os_file_exists(@FullName)
              insert #filetmpfin select @Name, 1-IsDir, @FullName from #filetmp3 where Exist = 1 or IsDir = 1
              drop table #filetmp3
          end
        end
        else
        begin
          if(@Name is null)
          begin
            if (right(@Path, 1) = '\')
              select @Path= substring(@Path, 1, len(@Path) - charindex('\', reverse(@Path)))
            create table #filetmp (Name nvarchar(255) NOT NULL, depth int NOT NULL, IsFile bit NULL )
            insert #filetmp EXECUTE master.dbo.xp_dirtree @Path, 1, 1
            insert #filetmpfin select Name, IsFile, @Path + '\' + Name from #filetmp f
            drop table #filetmp
          end
          if(NOT @Name is null)
          begin
            if(@Path is null)
              select @FullName = @Name
            else
              select @FullName = @Path +  '\' + @Name
            if (right(@FullName, 1) = '\')
              select @Path= substring(@Path, 1, len(@FullName) - charindex('\', reverse(@FullName)))
            create table #filetmp2 ( Exist bit NOT NULL, IsDir bit NOT NULL, DirExist bit NULL )
            insert #filetmp2 EXECUTE master.dbo.xp_fileexist @FullName
            insert #filetmpfin select @Name, 1-IsDir, @FullName from #filetmp2 where Exist = 1 or IsDir = 1
            drop table #filetmp2
          end
        end

SELECT
Name AS [Name],
IsFile AS [IsFile],
FullName AS [FullName]
FROM
#filetmpfin
ORDER BY
[IsFile] ASC,[Name] ASC
drop table #filetmpfin

The main function used is sys.dm_os_enumerate_filesystem, for each folder that is opened, it goes a level deeper, an example of a second level:

select @Path = N'D:\Data\'

For regular logins

For regular logins it is as easy as denying the select permissions on this TVF to make the user not able to list the folders.

DENY SELECT ON master.sys.dm_os_enumerate_filesystem TO [Domain\LoginName]

When trying to select a backup, the user should see this message:

The user will then be able to only see the drive letters.

For contained users

For the contained user, denying the select on the TVF directly does not work

The contained user can succesfully run the next query example

declare @Path nvarchar(255)
declare @Name nvarchar(255)

select @Path = N'D:\'
select file_or_directory_name, 1 - is_directory, full_filesystem_path from sys.dm_os_enumerate_filesystem(@Path, '*') where [level] = 0

And.... this does not work:

use [PartialDb]
GO
DENY SELECT ON [sys].[dm_os_enumerate_filesystem] TO [PartialUser];
GO

  Msg 4629, Level 16, State 10, Line 34 Permissions on server scoped
  catalog views or system stored procedures or extended stored
  procedures can be granted only when the current database is master.

Below statements work but they do not restrict the user, even if it is not part of the dbrole role

DENY VIEW DATABASE STATE TO [PartialUser];

DENY VIEW DEFINITION ON SCHEMA :: information_schema TO [PartialUser];

DENY VIEW DEFINITION ON SCHEMA :: sys TO [PartialUser];

DENY SELECT ON SCHEMA :: information_schema TO [PartialUser];

DENY SELECT ON SCHEMA :: sys TO [PartialUser];

What does work? In theory

Since the contained user uses the guest account / public role to connect and select from dmv's, (the public role has access to certain objects by default) we could try restricting the public role.

This is not ideal due to a number of reasons. For example, deny > grant and as a result only members in the sysadmin role will be able to select from this TVF.

Another important thing to note is that changing the guest user / public role can have unknown side-effects on the instance or certain functionalities.

USE MASTER
GO
DENY SELECT ON [sys].[dm_os_enumerate_filesystem] TO public;
GO

Changing public / guest permissions is not an ideal scenario.

For example, disabling the guest user can break the msdb database.

Rerunning the select in the context of the contained user:

  Msg 229, Level 14, State 5, Line 7 The SELECT permission was denied on
  the object 'dm_os_enumerate_filesystem', database
  'mssqlsystemresource', schema 'sys'.

There may or may not be a way around this far from ideal approach, I have not found it.

An example of the public role's permissions:

These are granted for a reason, as such certain functionalities might break when denying / revoking these objects. Proceed with caution.

Some more information on the guest user / public role here


How can I query data from a linked server, and pass it parameters to filter by? I have a really big query that needs to be run on multiple databases, and the results appended to a temp table and returned.

The basic syntax looks something like this:

INSERT INTO #tmpTable (Id, ...)

SELECT T1.Id, ...
FROM Server.Database.dbo.Table1 as T1
INNER JOIN #tmpIds as T ON T1.Id = T.Id

INNER JOIN Server.Database.dbo.Table2 as T2 ON T1.Id = T2.Id
INNER JOIN Server.Database.dbo.Table3 as T3 ON T2.Id = T3.Id
LEFT OUTER JOIN Server.Database.dbo.Table4 as T4 ON T3.Id = T4.Id
LEFT OUTER JOIN Server.Database.dbo.Table5 as T5 ON T4.Id = T5.Id
LEFT OUTER JOIN Server.Database.dbo.Table6 as T6 ON T5.Id = T6.Id

The query runs quickly if run locally on the the individual servers, however it takes a long time to run if it's run from a linked server using the 4-part names like above.

The problem appears to be it's querying the linked server for the unfiltered result set first, then joining it to the #tmpIds table on the local server afterwards, which makes the query take an very long time to run.

If I hardcode the Ids to filter the result set on the linked server, such as

SELECT T1.Id, ...
FROM Server.Database.dbo.Table1 as T1
-- INNER JOIN #tmpIds as T ON T1.Id = T.Id
INNER JOIN Server.Database.dbo.Table2 as T2 ON T1.Id = T2.Id
INNER JOIN Server.Database.dbo.Table3 as T3 ON T2.Id = T3.Id
LEFT OUTER JOIN Server.Database.dbo.Table4 as T4 ON T3.Id = T4.Id
LEFT OUTER JOIN Server.Database.dbo.Table5 as T5 ON T4.Id = T5.Id
LEFT OUTER JOIN Server.Database.dbo.Table6 as T6 ON T5.Id = T6.Id

WHERE T1.Id IN (1, 2, 3)

it runs quickly in just a few seconds.

Is there a way to run this query so it filters the result set of the query from the linked server by the #tmpId table first, before returning the result set to the local server?

Some things to note

The query is very large and complex, and Dynamic SQL is not a viable option for it due to the maintenance nightmare that causes.

I would be open to suggestions about how to use Dynamic SQL for something else though, such as running a stored procedure or UDF if there's a way to do that over a linked server (tried a few different methods such as sp_executeSQL, OPENROWSET, and OPENQUERY, but those all failed).
Because it uses the 4-part naming convention, I cannot use a UDF on the remote server
Distributed Transactions are disabled, so the following does not work

INSERT INTO #table
EXEC Server.Database.dbo.StoredProcedure @ids

The performance problem actually had to do with the LEFT OUTER JOIN tables. If I changed them to INNER JOIN, or if I excluded their data from the SELECT columns, the query ran fine.

What I ended up doing was creating a View on the linked server containing all the data I wanted from it, then simply joining to it from the primary server with the #tmpIds table.

I didn't think this would work since I thought joining everything and pulling it down to the second server before filtering was the same as what I was doing now, and would lead to the same performance problem, but surprisingly that doesn't appear to be the case.

CREATE VIEW MyView
AS

SELECT T1.Id, T2.ColA, ...
FROM Table1 as T1
INNER JOIN Table2 as T2 ON T1.Id = T2.Id
INNER JOIN Table3 as T3 ON T2.Id = T3.Id
LEFT OUTER JOIN Table4 as T4 ON T3.Id = T4.Id
LEFT OUTER JOIN Table5 as T5 ON T4.Id = T5.Id
LEFT OUTER JOIN Table6 as T6 ON T5.Id = T6.Id

GO

and

INSERT INTO #tmpTable (Id, ...)

SELECT T1.Id, T1.ColA, ...
FROM Server.Database.dbo.MyView as T1
INNER JOIN #tmpIds as T ON T1.Id = T.Id

All the joined columns were correctly indexed, however according to this answer

  Even though there may be indexing on tables on the remote server, SQL may not be able to take advantage of them while it can build a local query plan that does take advantages of indexing.

And this one

  Let the linked server do as much as possible.
  It is impossible for SQL Server to optimise a query on a linked server, even another SQL Server

so I am guessing that the query plan used for the query was not using the Indices defined, and SQL Server was generating a poor query plan for the LEFT OUTER JOIN tables.


How to use a relative path in sqlcmd script Can someone tell me how to swap this absolute path with a relative one for my sqlcmd upgrade script?

-- how can I get rid of the following absolute path variable from my script?
:SETVAR ScriptDir "C:\absolutePath\"

:SETVAR DeployLog "upgradeDb.out.log"
:SETVAR ErrorLog "upgradeDb.err.log"
:Error $(ScriptDir)$(ErrorLog)
:OUT $(ScriptDir)$(DeployLog)

:ON ERROR EXIT
print ' run scripts a and b'
:r $(ScriptDir)"a.sql"
:r $(ScriptDir)"b.sql"

PRINT 'finished'
GO

:EXIT
print 'problem...'
GO

Originally posted (in the wrong place) here: https://softwareengineering.stackexchange.com/questions/318033/how-to-use-a-relative-path-in-sqlcmd-script

If you want a relative path, just use relative path notation.
:SETVAR ScriptDir "../../"

That will set your ScriptDir to two directories higher than wherever you invoked sqlcmd from.

or
:SETVAR ScriptDir "moo/oink/bah/"

That will look for scripts in the moo/oink/bah subdirectory of wherever you invoked sqlcmd from.


Can I put a CTE inside a cross join? I have a main query that have a bunch of cross joins in it such as:

SELECT val1, val2, ...
FROM
 (  SELECT top 1 id as val1
    FROM TableA
    ) A
CROSS JOIN
 (  SELECT top 1 id as val2
    FROM TableB
     ) B
CROSS JOIN
 ( ?
  ) C

Inside the third CROSS JOIN C I want to place a query that contains a CTE in it (this one basically allows me to execute a query only if another query has no results), which looks like the following:

WITH r AS (
  SELECT * FROM film WHERE length = 120
)
SELECT CASE WHEN @flag = 'Y' THEN (
    SELECT * FROM r
    UNION ALL
    SELECT * FROM film
    WHERE length = 130
    AND NOT EXISTS (
      SELECT * FROM r
    )
) ELSE NULL END val3

Is this possible?

Some SQL products – like PostgreSQL, for instance – allow you to have nested CTE definitions, enabling you to write your queries like this:

SELECT val1, val2, ...
FROM
 (  SELECT top 1 id as val1
    FROM TableA
    ) A
CROSS JOIN
 (  SELECT top 1 id as val2
    FROM TableB
     ) B
CROSS JOIN
 (
    WITH r AS (                              -- nested CTE definition
      SELECT * FROM film WHERE length = 120
    )
    SELECT CASE WHEN @flag = 'Y' THEN (
        SELECT * FROM r
        UNION ALL
        SELECT * FROM film
        WHERE length = 130
        AND NOT EXISTS (
          SELECT * FROM r
        )
    ) ELSE NULL END val3
  ) C

SQL Server does not support this, though, as ypercubeᵀᴹ explains in a comment.

However, as per another comment by ypercubeᵀᴹ, you can move the CTE definition to the beginning of the entire query. That way you you can still reference it in the C derived table. So your query will look like this:

WITH r AS (                              -- the CTE definition goes here
  SELECT * FROM film WHERE length = 120
)
SELECT val1, val2, ...
FROM
 (  SELECT top 1 id as val1
    FROM TableA
    ) A
CROSS JOIN
 (  SELECT top 1 id as val2
    FROM TableB
     ) B
CROSS JOIN
 (
    SELECT CASE WHEN @flag = 'Y' THEN (
        SELECT * FROM r                  -- this CTE reference is valid
        UNION ALL
        SELECT * FROM film
        WHERE length = 130
        AND NOT EXISTS (
          SELECT * FROM r                -- as is this one
        )
    ) ELSE NULL END val3
  ) C


Mysql configuration and power failure I'd like to know if there are certain configurations of Mysql that should be considered to keep the effect of the power failure as low as possible. Which configuration points should be noted and what values should they have.

The setup for maximal resiliency from power failure is to have 3 Galera nodes, one in each of three geographically separate locations.

You seem to be using MariaDB, the newer versions of which include Galera Clustering.  There is, of course, some setup for clustering and some coding/admin differences than vanilla MariaDB.

When a node (or even the entire datacenter it is in) goes down, the other nodes are available to take reads and writes.  The only issue is pointing your clients to them.  There are several Proxy servers (etc) that provide this.

3 is a magic number.  It allows for a single-point-of-failure, with the other 2 nodes voting to say "we have a quorum" and will stay up.  The one that went down might actually be alive, but hidden behind a network outage.  To prevent "split brain", that node realizes that id does not have a quorum, so it refuses to take writes, since they might compromise the consistency of unique keys.

For a single 'home' system:

Use InnoDB, not MyISAM.  InnoDB will recover graceful from power failures.
Plug into a UPS.  This will give you 'some' time before the power really fails.
Use a laptop, not a desktop -- laptops have a battery that will last 'some' time.


Why is it not possible to alter a view with a "CREATE OR REPLACE"? The Postgresql documentation is clear on this point: you can not change the columns of an existing view.

  The new query must generate the same columns that were generated by the existing view query (that is, the same column names in the same order and with the same data types) (...)

Reference: http://www.postgresql.org/docs/9.3/static/sql-createview.html

Why on earth do I have to either DROP the view or ALTER it for modifying it ?

It's an implementation limitation. It's theoretically possible, of course, but nobody's written the code to handle it yet.

To cope with column removals or type changes, PostgreSQL would have to scan every view that references the view being modified (using pg_catalog.pg_depend) to see if any of them relied on the column. It'd also need to look for whole-row references and disallow changes in those cases.

It's less clear why adding a column is not permitted. Again, I suspect that's down to whole-row references. If pg_depend was checked for whole-row references without finding any, and the new column appeared at the end, it'd be OK to add it.

However, views created with SELECT * FROM wouldn't "inherit" the new column, because * gets expanded into a column-list during view creation, though. So if you had view_A that does a SELECT * FROM view_B, and you added a column to view_B, it wouldn't appear in view_A. Yet if you dropped and re-created view_A, the column would appear. Needless to say, that's not good. To cope with that, PostgreSQL would have to keep track of whether a given view's column list ("targetlist" in PostgreSQL internal terms) came from a * wildcard. Which is more complicated than you'd think because you can write someview.* too.

All in all - it's complicated, and nobody's wanted it enough to do the work to implement it.


How to remove an element from a col with a CSV of ints in PostgreSQL 9.2? I have PostgreSQL 9.2 database and a table:

id integer,
allowed_types character varying(255)`

Sample data as below:

id  allowed_types
1   3,4,5,13,14

How I can remove 4 and 5 from allowed_types which is a comma separated varchar list?

After removing, the result should be allowed_types = 3,13,14.

There are many records on that table, each allowed_types could contain different numbers, separated by comma.

I considered string_to_array() and array_remove(), but array_remove() is not in version 9.2, yet.

Proof of concept for regular expressions:
SELECT trim(regexp_replace(',' || '3,4,5,13,14' || ',', ',(4|5)(?=,)', '', 'g'), ',');

Returns:
3,13,14

Pad commas to cover corner cases at begin and end. The regular expression ',(4|5)(?=,)' explained:
, ... literal comma
(4|5) ... two branches: either 4 or 5
(?=,) ... positive lookahead: next character is a literal comma
Would need a functional trigram index on (',' || allowed_types || ',') for good performance with big tables (only slightly bigger than a regular index due to the added commas). Details:

How is LIKE implemented?

Or, with a more sophisticated regular expression, you can work with the original column and a trigram index on just (allowed_types):
SELECT ltrim(regexp_replace('3,4,5,13,14', '((,|^)(4|5))(?=,|$)', '', 'g'), ',');

dbfiddle here
But I expect the first solution to be faster: complex regular expressions are more expensive.
The according UPDATE
UPDATE tbl
SET    allowed_types = trim(regexp_replace(',' || allowed_types || ',', ',(4|5)(?=,)', '', 'g'), ',')
WHERE  ',' || allowed_types || ',' ~ ',(4|5),';  -- here, plain comma is good

What I would really do:
Update to a current version of Postgres (pg 9.2 reaches EOL Sept 2017) and probably use a normalized 1:n design.