Optimizing regex based query in sqlite I'm using an sqlite database to store manually created labels for some data automatically queried from a live system. The data from the live system consists primarily of an address, comprised of 3 parts. Let's use URLs an example, the three parts being the protocol, the domain and the path. Initially, I would load a couple 100k worth of addresses into a table with each field of the address being a column and together building the primary key. The labels are then in additional columns.
CREATE TABLE OldWebsites (
    protocol VARCHAR (255) NOT NULL,
    domain   VARCHAR (255) NOT NULL,
    path     VARCHAR (255) NOT NULL,
    label1   INTEGER,
    label2   TEXT,
    CONSTRAINT address PRIMARY KEY (
        protocol,
        domain,
        path
    )
);

I found myself repeating labels over and over based on certain patterns that the address would match. Since I would always extend this table with new data and remove old data, this became too much of a hazzle, so I tried a different approach, namely just loading the existing addresses into one table and then have other tables for the data where I would write regex matchers for the address components
CREATE TABLE Websites (
    protocol VARCHAR (255) NOT NULL,
    domain   VARCHAR (255) NOT NULL,
    path     VARCHAR (255) NOT NULL,
    CONSTRAINT address PRIMARY KEY (
        protocol,
        domain,
        path
    )
);

CREATE TABLE Label1 (
    protocol_re VARCHAR (255) NOT NULL,
    domain_re   VARCHAR (255) NOT NULL,
    path_re     VARCHAR (255) NOT NULL,
    label1   INTEGER
    CONSTRAINT address_matcher PRIMARY KEY (
        protocol_re,
        domain_re,
        path_re
    )
);

CREATE TABLE Label2 (
    protocol_re VARCHAR (255) NOT NULL,
    domain_re   VARCHAR (255) NOT NULL,
    path_re     VARCHAR (255) NOT NULL,
    label2   TEXT,
    CONSTRAINT address_matcher PRIMARY KEY (
        protocol_re,
        domain_re,
        path_re
    )
);

Assume that I have already (using other queries) guaranteed, that there is exactly one match in each label table for each address in the Websites table. I would now like to write a query that reconstructs a table like the original OldWebsites one by matching labels and automatically queried data.
Something like this
SELECT Websites.*,
       Label1.label1,
       Label2.label2
  FROM Websites
       JOIN
       Label1 ON (Websites.protocol REGEXP '^' || Label1.protocol_re || '$' AND
                  Websites.domain REGEXP '^' || Label1.domain_re || '$' AND
                  Websites.path REGEXP '^' || Label1.path_re || '$')
       JOIN
       Label2 ON (Websites.protocol REGEXP '^' || Label2.protocol_re || '$' AND
                  Websites.domain REGEXP '^' || Label2.domain_re || '$' AND
                  Websites.path REGEXP '^' || Label2.path_re || '$');

Now.. this is really slow, especially for more label tables, using PCRE sqlite3 extension for the REGEXP function.
I would like to know if there's way to optimize this query using either parallelization (the query should run ideally from python) or using the knowledge that there is exactly 1 match in each Label table.
From my understanding, multiple inner joins should take at most the sum of the individual joins, correct?
Perhaps indexes are also helpful, but I have only a basic idea of what they are and no idea whether they would be of help here.

I noticed that my assumption that multiple inner joins should just take the sum in time of the individual joins is apparently not true.
I got the query up to a decent speed by preprocessing the parts to be joined, so a more efficient join strategy can be used:
SELECT Websites.*,
       L1.label1,
       L2.label2
  FROM Websites
       JOIN
       (
           SELECT Websites.*,
                  Label1.label1
             FROM Websites
                  JOIN
                  Label1 ON (Websites.protocol REGEXP '^' || Label1.protocol_re || '$' AND
                             Websites.domain REGEXP '^' || Label1.domain_re || '$' AND
                             Websites.path REGEXP '^' || Label1.path_re || '$')
       )
       AS L1 USING (
           protocol,
           domain,
           path
       )
       JOIN
       (
           SELECT Websites.*,
                  Label2.label2
             FROM Websites
                  JOIN
                  Label1 ON (Websites.protocol REGEXP '^' || Label2.protocol_re || '$' AND
                             Websites.domain REGEXP '^' || Label2.domain_re || '$' AND
                             Websites.path REGEXP '^' || Label2.path_re || '$')
       )
       AS L2 USING (
           protocol,
           domain,
           path
       );

It may not look like much, but it works very well.


Is it possible to emulate the WITH command in MySQL 5.1.73? I have the following query:

SELECT
    COUNT(*) AS `count`
FROM
    agreements
WHERE
    start_date >= STR_TO_DATE('14-01-2015','%d-%m-%Y')
    AND
    start_date < STR_TO_DATE('15-01-2015','%d-%m-%Y')
    AND
    active = 1
    AND
    (driv_id, veh_id) NOT IN (
        SELECT
            a.driv_id, a.veh_id
        FROM
            agreements a
        INNER JOIN
            types t
            ON
            a.agreement_type_id = t.id
        WHERE
            DATE_ADD(a.start_date,INTERVAL t.initial_term YEAR) <
                DATE_SUB(STR_TO_DATE('14-01-2015','%d-%m-%Y'),INTERVAL 2 MONTH)
    )

I then have to run the same query, having made a single change - removing the NOT from the (driv_id, veh_id) NOT IN ... part of the WHERE clause.

This is obviously not good because it means I literally copy and paste the query minus one word which violates DRY.

I googled around and discovered the WITH command, but it's not available in MySQL 5.1.73. The query I would have executed using WITH was:

WITH tuples AS
(
        SELECT
            a.driv_id, a.veh_id
        FROM
            agreements a
        INNER JOIN
            types t
            ON
            a.agreement_type_id = t.id
        WHERE
            DATE_ADD(a.start_date,INTERVAL t.initial_term YEAR) <
                DATE_SUB(STR_TO_DATE('14-01-2015','%d-%m-%Y'),INTERVAL 2 MONTH)
)
SELECT
    SUM(IF((a.driv_id, a.veh_id) IN `tuples`, 1, 0)) as in_sum
    SUM(IF((a.driv_id, a.veh_id) NOT IN `tuples`, 1, 0)) as not_in_sum
FROM
    agreements
WHERE
    start_date >= STR_TO_DATE('14-01-2015','%d-%m-%Y')
    AND
    start_date < STR_TO_DATE('15-01-2015','%d-%m-%Y')
    AND
    active = 1

I tried doing this with a temporary table as well by storing the result of the subquery in a temp table called tuples and selecting (in my main query):

SUM(IF((a.driv_id, a.veh_id) IN tuples, 1, 0)) as in_sum
SUM(IF((a.driv_id, a.veh_id) NOT IN tuples, 1, 0)) as not_in_sum

But this would throw a syntax error (not quite sure why).

Is there a way to do this, perhaps using joins (I tried to work out a join query but didn't have any breakthroughs)?

Yes, it is possible to return both results without repeating the subquery by rewriting your query using an outer join. The following will return same results as your version:

SELECT
    COUNT(*) AS `count`
FROM
    agreements AS ag
LEFT JOIN
    (
        SELECT
            a.driv_id, a.veh_id
        FROM
            agreements a
        INNER JOIN
            types t
            ON
            a.agreement_type_id = t.id
        WHERE
            DATE_ADD(a.start_date,INTERVAL t.initial_term YEAR) < DATE_SUB(STR_TO_DATE('14-01-2015','%d-%m-%Y'),INTERVAL 2 MONTH)
    ) AS s ON ag.driv_id = s.driv_id AND ag.veh_id = s.veh_id
WHERE
    ag.start_date >= STR_TO_DATE('14-01-2015','%d-%m-%Y')
    AND
    ag.start_date < STR_TO_DATE('15-01-2015','%d-%m-%Y')
    AND
    ag.active = 1
    AND
    s.driv_id IS NULL
;

That would effectively be your not_in_sum. In order to return both counts at once, you just remove the NULL check from the where clause and use COUNT(s.driv_id) in SELECT like this:

SELECT
    COUNT(s.driv_id) AS in_sum,
    COUNT(*) - COUNT(s.driv_id) AS not_in_sum
FROM
    ...

If the subquery can return duplicates, add DISTINCT to it, otherwise in_sum may return an incorrect result.

As for the WITH clause, there is no mention of it even in the manual for the not yet officially released version 5.7, but using a temporary table to emulate it would probably make sense. The error you got while trying that method was because the right side of IN/NOT IN in your attempts was just a table name whereas it should have been a subquery:

(a.driv_id, a.veh_id) IN (SELECT driv_id, veh_id FROM tuples)


sp_BlitzCache slow results on @SortOrder='cpu' I've been using sp_BlitzCache to investigate a few problems with a DB of mine for a few days. It's been a huge help, but just today it started acting a little odd and I'm curious if anyone has any ideas what's up.

Yesterday I ran many checks with @SortOrder as either 'reads' or 'CPU' and it worked fine, and always returned the results in about the same amount of time.

Today, 'reads' comes back in about the same amount of time as yesterday, but 'cpu' takes 10X or more as long.  It gets to 'Checking for functions' and then sits there for a very long time.  'xpm' is also as fast as usual.

I'm using v5.7(20170901)

I realized my SQL session had been open for +24hours, and thought that the ##temp files were perhaps getting too big.  But after I restarted the session and those tables went away, cpu sort order is still really slow.  So that wasn't it.

Any thoughts on what would be causing this?

The most likely reason for this is a very large execution plan in the cache that doesn't show up in other sort orders. That can certainly hold things up when we hit the RelOp section of the XML.

You can use the @SkipAnalysis parameter, and set it to 1. This will skip all the XML analysis, but it will get you results. You'll want to use @ExpertMode to get the SqlHandle and QueryHash for the big plan that's holding things up.

EXEC sp_BlitzCache @ExpertMode = 1, @SkipAnalysis = 1

When you figure out which plan it is, you can use the @IgnoreSqlHandles or @IgnoreQueryHashes to skip over that plan in the cache in the future.

EXEC sp_BlitzCache @IgnoreQueryHashes ='', @IgnoreSqlHandles = ''

Hope this helps!


Select for indexed field with >= is much faster than with = Execution time for the query

SELECT id
FROM Deals
WHERE is_demo = 0
AND asset_id >= 71
AND exp_time >= UNIX_TIMESTAMP(UTC_TIMESTAMP() - INTERVAL 30 HOUR);

is 0.5s, but if we change asset_id>=71 to asset_id=71 the execution time is about 10 seconds.

Why >=71 if much faster than simple equal operation =71?

Indexes for all fields are used.

CREATE TABLE `Deals`
(
    id INT(11) PRIMARY KEY NOT NULL AUTO_INCREMENT,
    user_id INT(11) NOT NULL,
    is_demo TINYINT(1) NOT NULL,
    asset_id INT(11) NOT NULL,
    exp_time INT(11) NOT NULL,
);
CREATE INDEX asset_idx ON `Deal` (asset_id);
CREATE INDEX exp_timex ON `Deal` (exp_time);
CREATE INDEX is_demo ON `Deal` (is_demo);
CREATE INDEX user_idx ON `Deal` (user_id);

With >=, the optimizer had two indexes to choose from.  Apparently it chose (exp_time).  Please provide EXPLAIN SELECT ... to confirm this. Also run EXPLAIN FORMAT=JSON SELECT ...

With =, the optimizer still has two to choose from, but probably picked = (asset_id). But there were a lot of 71's, so it turned out to be a bad choice.

That choice can be improved by having INDEX(asset_id, exp_time) in place of simply (asset_id).  This may run faster than even 0.5s.  This 'composite' index can use both parts of the WHERE clause, thereby being more efficient.  (The >= version can't use both columns of a composite index because it can't handle 2 ranges at the same time.)

And even better than  (asset_id, exp_time): (is_demo, asset_id, exp_time) or (asset_id, is_demo, exp_time).

It could be that the "statistics" are stale -- run ANALYZE TABLE Deals to see if things improve.  But don't hold your breath; this is not a panacea.


Get dates between two months Can I know why am I getting weird output in my SQL statement whereby my table has two dates of the month May but it only appears for one month, the same things I test for dates range it is working.

    select distinct To_Char (attendance_date,'dd/MM/yyyy')
    from DIT_2010MAR_CIT4114A_FYP1_NO
    where attendance_Date between To_Date ('05', 'MM') and
    To_Date ('05', 'MM');

Actually your query doesn't return anything in my test :(. The reason is the attendance_date doesn't fall in between the condition that you have specified(To_date('05','MM') and
 To_date('05','MM') is returning the same value).

You can change your code as
  ...where attendance_date between To_date('05','MM') and To_date('06','MM'); to select all the dates of the May month.

SQL> desc atten;
 Name                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ATTENDANCE_DATE                    DATE

SQL> select * from atten;

ATTENDANC
---------
05-MAY-16
03-JUN-16
03-JUL-16
03-MAY-16

SQL> select distinct To_Char (attendance_date,'dd/MM/yyyy') from atten where attendance_Date between To_Date ('05', 'MM') and To_Date ('05', 'MM');

no rows selected

You can try the following to get the result.

SQL> select to_char(attendance_date, 'dd/mm/yyyy') from atten
where extract(month from attendance_date) = 5;  2

TO_CHAR(AT
----------
05/05/2016
03/05/2016

OR If you wish to display attendance date of months between 5 and 6(As you question's title suggests).

select to_char(attendance_date, 'dd/mm/yyyy') from atten
where extract(month from attendance_date) = 5 OR extract(month from attendance_date)=6;
TO_CHAR(AT
----------
05/05/2016
03/06/2016
03/05/2016


Changing compatibility level from 2008 to 2016 I have a database with hundreds of stored procedure, views, triggers, function, ...
The database Compatibility Level is 2008 (100) and we would like to move it to 2016 (130).

The SQL Server version is 2016. We read that migrating the Compatibility Level could/would improve performance.

What is the best way to verify each object of the database to ensure working compatibility of the database? I already checked the "Upgrade Advisor" and it doesn't support the version of the database.

For this number of jumps in compatibility level (or if you are being appropriately paranoid with a production application, for any jumps in compatibility level) a full application regression test is the only way to be absolutely sure.

Even if you have ensured that all the matters listed in the release notes either don't affect you or have been mitigated, you might find instances where code is relying on officially undefined behaviour which has changed (changes like this may not appear in release notes). For instance, when moving some of our work from 2005 to 2008r2 we experienced problems with a change in how some (admitedly odd) queries using FOR XML generated their output.


Should i use subquery to limit table before a join? In the case of a join followed by a where clause, would it be better to use a subquery to limit the results, and then do the join?  Example:

SELECT *
FROM Customers
    NATURAL JOIN Orders
WHERE shipped=1

In this case, it seams the DBMS will join the entire Customers table with the entire Orders table, and then filter the results based on the where clause. An equivalent query using subquery would be:

SELECT *
FROM Customers
    NATURAL JOIN (SELECT *
                     FROM Orders
                     WHERE shipped=1) AS O

Here, there is potentially a smaller Orders table to JOIN with.  Likewise, if there was a where clause that limited both Customers and Orders:

SELECT *
FROM Customers
    NATURAL JOIN Orders
WHERE country='US' AND shipped=1
(assuming country attribute belongs to Customers table)

An equivalent subquery query:

SELECT *
FROM (SELECT *
            FROM Customers
            WHERE country='US') AS C
    NATURAL JOIN (SELECT *
                                 FROM Orders
                                 WHERE shipped=1) AS O

The answer to your question depends on the specific database and version you are using. In any case most current databases will optimize the query and end-up having the same execution plan for all cases.

I'd go for the simplest syntax that clearly states your purpose. It is probably the one that the database will be able to best optimize. And, specially, it is the one that will be easier to interpret by yourself, or anyone modifying any application later on. If you find out that some query seems to underperform, then you should check execution plans, alternatives, and find out if it can actually be improved.

You can check it with most DBs with a statement similar to EXPLAIN SELECT * FROM Customers NATURAL JOIN Orders WHERE shipped=1 and check what the execution plan is.

This is how you would do it when using PostgreSQL:

Creation and population of (mockup) tables:

 CREATE TABLE Customers
 (
     customer_id integer primary key,
     customer_name text not null
 ) ;

 CREATE TABLE Orders
 (
     order_id integer primary key,
     customer_id integer NOR NULL REFERENCES Customers(customer_id),
     whatever text,
     shipped boolean
 ) ;

 -- We invent 1_000 customers
 INSERT INTO
     Customers (customer_id, customer_name)
 SELECT
     i, 'Name ' || i
 FROM
     generate_series (1, 1000) as s(i) ;

 -- and 25_000 orders
 INSERT INTO
     Orders (order_id, customer_id, whatever, shipped)
 SELECT
     i AS order_id,
     1 + 999*random() AS customer_id,
     'a text' AS whatever,
     (random() < 0.85) AS shipped
 FROM
     generate_series(1, 25000) AS s(i) ;

Make sure the database has proper statistics:

ANALYZE Orders;
ANALYZE Customers;

Check for the exeuction plan of the simplest query:

EXPLAIN ANALYZE
SELECT * FROM Customers NATURAL JOIN Orders WHERE shipped

 | QUERY PLAN                                                                                |
 | :-------------------------------------------------------------------------------------------------------------------- |
 | Hash Join  (cost=28.50..705.05 rows=21131 width=24) (actual time=0.946..29.305 rows=21131 loops=1)                    |
 |   Hash Cond: (orders.customer_id = customers.customer_id)                                                             |
 |   ->  Seq Scan on orders  (cost=0.00..386.00 rows=21131 width=16) (actual time=0.008..10.089 rows=21131 loops=1)      |
 |         Filter: shipped                                                                                |
 |         Rows Removed by Filter: 3869                                                                                |
 |   ->  Hash  (cost=16.00..16.00 rows=1000 width=12) (actual time=0.906..0.906 rows=1000 loops=1)                       |
 |         Buckets: 1024  Batches: 1  Memory Usage: 52kB                                                                 |
 |         ->  Seq Scan on customers  (cost=0.00..16.00 rows=1000 width=12) (actual time=0.005..0.445 rows=1000 loops=1) |
 | Planning time: 0.419 ms                                                                                |
 | Execution time: 34.613 ms                                                                                |

Check for the execution plan of the second version of the query:

 EXPLAIN ANALYZE
 SELECT * FROM Customers NATURAL JOIN (SELECT * FROM Orders WHERE shipped) AS O

 | QUERY PLAN                                                                                |
 | :-------------------------------------------------------------------------------------------------------------------- |
 | Hash Join  (cost=28.50..705.05 rows=21131 width=24) (actual time=0.693..24.537 rows=21131 loops=1)                    |
 |   Hash Cond: (orders.customer_id = customers.customer_id)                                                             |
 |   ->  Seq Scan on orders  (cost=0.00..386.00 rows=21131 width=16) (actual time=0.007..8.534 rows=21131 loops=1)       |
 |         Filter: shipped                                                                                |
 |         Rows Removed by Filter: 3869                                                                                |
 |   ->  Hash  (cost=16.00..16.00 rows=1000 width=12) (actual time=0.676..0.676 rows=1000 loops=1)                       |
 |         Buckets: 1024  Batches: 1  Memory Usage: 52kB                                                                 |
 |         ->  Seq Scan on customers  (cost=0.00..16.00 rows=1000 width=12) (actual time=0.003..0.312 rows=1000 loops=1) |
 | Planning time: 0.263 ms                                                                                |
 | Execution time: 29.083 ms                                                                                |

You can see that, in this case, PostgreSQL (9.6.2) uses exactly the same execution plan. This is what will happen most often.

dbfiddle here

A slightly different version, where one index is defined (and where we decided that shipped orders were minority, not majority):

CREATE INDEX idx_shipped_orders ON Orders(shipped, order_id) ;

... works with a different plan, but again, the same execution plan for both the simple and the shipped filtered first queries:

 | QUERY PLAN                                                                                |
 | :--------------------------------------------------------------------------------------------------------------------------------------- |
 | Hash Join  (cost=28.78..214.66 rows=3822 width=24) (actual time=0.750..5.985 rows=3822 loops=1)                                          |
 |   Hash Cond: (orders.customer_id = customers.customer_id)                                                                                |
 |   ->  Index Scan using shipped_orders_idx on orders  (cost=0.28..133.61 rows=3822 width=16) (actual time=0.008..1.873 rows=3822 loops=1) |
 |   ->  Hash  (cost=16.00..16.00 rows=1000 width=12) (actual time=0.730..0.730 rows=1000 loops=1)                                          |
 |         Buckets: 1024  Batches: 1  Memory Usage: 52kB                                                                                |
 |         ->  Seq Scan on customers  (cost=0.00..16.00 rows=1000 width=12) (actual time=0.008..0.345 rows=1000 loops=1)                    |
 | Planning time: 0.209 ms                                                                                |
 | Execution time: 6.863 ms                                                                                |

dbfiddle here

NOTE: The small differences in execution time can be due to any external factors, such as other processes using the database at the same time or not. They should only be taken into consideration if you perform the queries a statistically significant number of times, and perform the adequate statistical test for significance.

NOTE 2: I've used the NATURAL JOIN as you did, to not introduce more elements to the analysis. In practice, I'd rather use either ON t1.col = t2.col (compatible with mostly all databases) or USING (col). The meaning of both remains constant. NATURAL JOIN can change meanings if you add columns to your tables. For instance, you might decide to add columns for last_modified_at, last_modified_by, to track the "age" of your data ... and your NATURAL JOINs stop working. In short: don't use them. NATURAL JOIN does not refer to joining using the columns participating in a foreign key constraint, as you might have thought. They just refer to columns having the same name in both tables. And that's risky. I have plenty of tables with columns called created_at and last_modified_at, for instance, and it doesn't make any sense to use them to JOIN.


How to join two tables in postgresql First table

 Complaint_id   Status  Reopen
    127493          5   1
    127494          5   0

  Second table

Parent_id   Currentstatus   Reopen  datetime    usertype
127493  0   0   2019-04-08 12:40:31.209103+05:30    c
127493  5   1   2019-04-08 12:40:31.209103+05:30    c
127493  0   0   2019-04-08 12:36:22.662995+05:30    c
127493  5   1   2019-04-08 12:36:22.662995+05:30    c
127493  0   0   2019-04-08 12:34:25.459444+05:30    c
127493  5   1   2019-04-08 12:34:25.459444+05:30    c
127493  5   0   2019-04-08 12:29:58.581771+05:30    E
127493  5   0   2019-05-08 12:29:58.581771+05:30    E

  Output

complaintid currenstatus usertype
127493        0            c

I am trying using below query

SELECT
 currentstatus
FROM
  (
    SELECT
      Currentstatus,
      Datetime,
      Open_reopen_status,
      Parent_id,
      MAX(Datetime) OVER (PARTITION BY Parent_id) AS MaxDatetime
    FROM
      secondtable
  ) AS derived
WHERE Datetime = MaxDatetime

but i don't know how to connect with first table basically i want the records form first table is where reopen=1

select * from firsttable where reopen=1

and fetch the current status and usertype from the second table according to max datetime

SELECT t1.Complaint_id,
       t2.currentstatus,
       t2.usertype
FROM firsttable t1
JOIN ( SELECT currentstatus,
              usertype,
              Parent_id
       FROM ( SELECT Currentstatus,
                     Datetime,
                     Parent_id,
                     usertype,
                     MAX(Datetime) OVER (PARTITION BY Parent_id) AS MaxDatetime
              FROM secondtable
            ) AS derived
       WHERE Datetime = MaxDatetime
     ) t2 ON t1.Complaint_id = t2.Parent_id
         AND t1.reopen = 1

?

PS. Does secondtable (Parent_id, Datetime) is unique by index/constraint?

PPS. Use CTE to make the query more readable...


How badly do SQL Compilations impact the performance of SQL Server? I am profiling an instance of a SQL Server 2005 and I, via PerfMon's SQLServer:SQL Statistics - SQL Compilations/sec metric, I see that the average is about 170 or so.

I whipped out SQL Profiler and looked for SP:Compile or SQL:Compile events.  Apparently they do not exist.  I did find Stored Procedure/SP:Recompile and TSQL/SQL:StmtRecompile events.  The amount of data I see in the Profiler suggests that these are the wrong events to look at, though I am not sure.

So my questions.  Answers to any of these would be great.

How can I see what exactly is compiling in SQL Server?
Did I pick the wrong metrics to look at?  In either Perfmon or SQL Profiler?
With regards to Stored Procedure/SP:Recompile and TSQL/SQL:StmtRecompile events in SQL Profiler...they do not include the Duration metric.  How can I gauge the impact of these events to the system if they provide no way to see the timing impact to the system.

SQL Compilations/sec is a good metric, but only when coupled with Batch Requests/sec.  By itself, compilations per sec doesn't really tell you much.

You are seeing 170.  If batch req per sec is only 200 (a little exaggerated for effect) then yes, you need to get down to the bottom of the cause (most likely an overuse of ad hoc querying and single-use plans).  But if your batch req per sec is measuring about 5000 then 170 compilations per sec is not bad at all.  It's a general rule of thumb that Compilations/sec should be at 10% or less than total Batch Requests/sec.

If you really want to drill down into what's being cached, run the following query that utilizes the appropriate DMVs:

select
    db_name(st.dbid) as database_name,
    cp.bucketid,
    cp.usecounts,
    cp.size_in_bytes,
    cp.objtype,
    st.text
from sys.dm_exec_cached_plans cp
cross apply sys.dm_exec_sql_text(cp.plan_handle) st

To get all single-use plans (a count):

;with PlanCacheCte as
(
    select
        db_name(st.dbid) as database_name,
        cp.bucketid,
        cp.usecounts,
        cp.size_in_bytes,
        cp.objtype,
        st.text
    from sys.dm_exec_cached_plans cp
    cross apply sys.dm_exec_sql_text(cp.plan_handle) st
)
select count(*)
from PlanCacheCte
where usecounts = 1

To get a ratio of how many single-use count plans you have compared to all cached plans:

declare @single_use_counts int, @multi_use_counts int

;with PlanCacheCte as
(
    select
        db_name(st.dbid) as database_name,
        cp.bucketid,
        cp.usecounts,
        cp.size_in_bytes,
        cp.objtype,
        st.text
    from sys.dm_exec_cached_plans cp
    cross apply sys.dm_exec_sql_text(cp.plan_handle) st
    where cp.cacheobjtype = 'Compiled Plan'
)
select @single_use_counts = count(*)
from PlanCacheCte
where usecounts = 1

;with PlanCacheCte as
(
    select
        db_name(st.dbid) as database_name,
        cp.bucketid,
        cp.usecounts,
        cp.size_in_bytes,
        cp.objtype,
        st.text
    from sys.dm_exec_cached_plans cp
    cross apply sys.dm_exec_sql_text(cp.plan_handle) st
    where cp.cacheobjtype = 'Compiled Plan'
)
select @multi_use_counts = count(*)
from PlanCacheCte
where usecounts > 1

select
    @single_use_counts as single_use_counts,
    @multi_use_counts as multi_use_counts,
    @single_use_counts * 1.0 / (@single_use_counts + @multi_use_counts) * 100
        as percent_single_use_counts

As for durations captured through a SQL Server Trace, it is not available for the Recompile events.  It isn't so significant to see the duration or pain that plan compilation is causing, as there's not much you can do for a case-by-case situation.  The solution is to attempt to limit compilations and recompilations through plan re-use (parameterized queries, stored procedures, etc.).


DB STARTUP command active for an online database We had a maintenance activity done on our SQL Servers and after that, we restarted the services. Now the databases took their sweet time to recover and after recovery, we started some jobs thinking that everything is good. Now one of the job runs was blocked by a spid that has its command as DB STARTUP. Suspiciously, I checked the SQL Server logs and there it was showing that one database was still recovering. However, in the object explorer I am able to see that database as online and also I can query the database. I even did some fake updates and truncated some tables too in that database. So, what recovery is exactly going on here? The databases are in AOAG, if that helps.

As confirmed by you the SQL server is of enterprise version and database was not starting after restore so the recovery process can utilize fast recovery.  In enterprise edition the database would come online after second phase (this is called Fast recovery which was introduced from SS 2005 onwards) and you would see database as online but still internally third phase, the UNDO, phase is going on and would be accessible completely only after third phase completes. What I mean by completely is For fast recovery, a query may bump into one of the locks being held to allow fast recovery – in which case it will have to wait for that lock to be dropped as UNDO progresses

I suggest you also read

When is fast recovery used
3 phases of recovery


sqlplus command not found after installing oracle 12c I have going at it for the last 12 hours... I installed Oracle 12c on rhel7, the first installation didn’t go well so Installed a second one without changing the first installation; I created a new folder and proceeded with the installation. However I have not been able to connect to the database in anyway. When I try the command: env|grep -i ORACLE_HOME I get : ORACLE_HOME= /u01/app/oracle/product/12.1.0/dbhome_2/spfileorcl.ora. Can you suggest a solution on how to setup my environment variable based on the configurations in the picture?

export ORACLE_HOME=/home/christian/app/christian/product/12.1.0/dbhome_2
export PATH=$ORACLE_HOME/bin:$PATH
export ORACLE_SID=orcl

Alternatively:

. oraenv

And provide orcl as ORACLE_SID.


Is it possible for MySQL v5.7.6 to to implement mesh replication with multi-source replication As I know starting from MySQL v5.7.6 the multi-source replication is possible. So If we can have a slave that can get data from multiple masters. Can we have mesh replication, where each node act as a slave for all other nodes ?

Mesh Replication

If we have 4 nodes (N1, N2, N3, N4)

N1 is a slave for N2, N3, N4.
N2 is a slave for N1, N3, N4.
N3 is a slave for N1, N2, N4.
N4 is a slave for N1, N2, N3.

If yes we can do that ? How nodes detect duplicated events in such scenario:

A new change in N1 is happened.
N1 pushes this change into N2, N3, N4.
The new change is received at N2 (and the other nodes).
How N2 does't replicate it again ! Otherwise we will have a infinite loop !

It's called Galera.

A write to any node is automatically replicated to all other nodes.

Galera is usually deployed in 3-node configuration, but 4 is possible.

It is available from Percona (PXC), MariaDB, or an addon to MySQL.

Please explain why you want that configuration.

And, no, Galera won't get into an infinite loop.  Nor will any other multi-master setup -- as long as you set server-id to different values on each Master.


How to add Reporting services sharePoint features in SQL Server 2008R2 I am facing some issues related to SSRS on SQL Server 2008R2,
how can we have install below features:

Reporting Services- SharePoint

Reporting Services Add-in for SharePoint Products

Any one could you please help me.

Thanks

The Microsoft SQL Server 2008 R2 Reporting Services Add-in for Microsoft SharePoint 2010 products is installed by the SharePoint 2010 Products Preparation tool (PreRequisiteInstaller.exe). If you want to download and install the add-in separately, the most up-to-date version of this file is available online at the Microsoft Download Center [1]

Download the add-in From https://www.microsoft.com/en-in/download/details.aspx?id=622 and follow the instructions

Step 1: Install a SharePoint technology instance.

Step 2: Install SQL Server 2008 R2 Reporting Services and specify that the report server use SharePoint Integrated mode.

Step 3: Configure Reporting Services.

Step 4: Download the Reporting Services Add-in by clicking the rsSharePoint.msi link later. To start the installation immediately, click Run. To install at a later time, click Save.

Step 5: Install the Reporting Services Add-in and configure the report server on the SharePoint technology instance.

Step 6: Set permissions and add Reporting Services content types.

Step 7: Verify the installation.

Detailed version : https://msdn.microsoft.com/en-in/library/aa905871.aspx

Known Issues, Another useful link


Optimise Percona 5.7 my.cnf for a 32GB mixed nginx/php/mysql server I have a dedicated server which I run my website from.  It's got 32GB of ram,   Xeon E5-1650 3.2Ghz (6 core, 12 with HT) and two 3TB drives in RAID 1.

I'm running Percona 5.7.18

99% of my tables are InnoDB

My ibdata1 file is 500MB.

It's mainly SELECT queries, but some pretty complex joins/queries.  With a proportion of INSERT/UPDATE too though.

The server spends some of its time resizing JPEGs that users have uploaded, so I need to be mindful of that when allocating memory etc.

Is there anything else I could tell you that would help?

I do plan to implement some decent webapp-level caching at some point, but would like to get the mysql config improved in the meantime.

The site runs pretty well on normal traffic days, but can struggle a bit on busy days (like today...)

I'm a sole dev, so full-stack and spread thin, and will happily admit that my DBA skills are fairly poor.  I can write a decent query, but I know nothing about server set up.  What's in my my.cnf has been cobbled together from various different sources over the years, and I'm guessing it's far from optimal!

[client]
default-character-set           = utf8mb4

[mysql]
default-character-set           = utf8mb4

[mysqld]
user                            = mysql
default-storage-engine          = InnoDB
socket                          = /var/lib/mysql/mysql.sock
pid-file                        = /var/run/mysqld/mysqld.pid

datadir                         = /var/lib/mysql

skip-name-resolve

max-allowed-packet              = 16M
max-connect-errors              = 1000000

# is this a silly thing to do..?
tmpdir                          = /dev/shm

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links                  = 0

character-set-client-handshake  = FALSE
character_set_server            = utf8mb4
collation_server                = utf8mb4_unicode_ci

ft_min_word_len                 = 2
group_concat_max_len            = 4096

max_connections                 = 500

tmp-table-size                 = 32M
max-heap-table-size            = 32M
thread-cache-size              = 50
open-files-limit               = 65535
table-definition-cache         = 1024
table-open-cache               = 2048

join_buffer_size                = 2M
sort_buffer_size                = 2M
read_rnd_buffer_size            = 2M

slow-query-log                  = 1
long-query-time                 = 1
slow-query-log-file             = /var/lib/mysql/slow_log

log-error                       = /var/lib/mysql/mysite.err

max_allowed_packet              = 256M

query_cache_size                = 268435456
query_cache_limit               = 1048576
query_cache_type                = 1

innodb_buffer_pool_size         = 12G
innodb_buffer_pool_instances    = 12
innodb_read_io_threads          = 12
innodb_write_io_threads         = 12
thread_pool_size                = 24

sql_mode                        = ""

[mysqld_safe]
log-error=/var/lib/mysql/mysite.err
pid-file=/var/run/mysqld/mysqld.pid

Any help/guidance would be hugely appreciated, I love to learn and more importantly I need to learn!

Thank you!

32GB RAM, 12G buffer_pool --> lots of room left over for resizing images.
12G buffer_pool, 0.5GB ibdata1 and 5.7 --> innodb_file_per_table is probably ON.  Is it?  If so, you have not given us a clue of how much data you have.  Look at the .ibd files.

query_cache_size = 256M is probably bad.  Any write to a table causes the purging of all QC entries for that table.  The purging is proportional to the size of the QC.  Keep it under 50M.  (Or turn it off.)

A Rule of Thumb:  "Don't put a cache in front of a cache."  MySQL does a lot of caching internally; adding a webapp-level cache may not help, and may rob RAM from the buffer_pool, where it is important.

Do you know if you are I/O-bound?  CPU-bound?

I see that you have the slowlog on.  Use pt-query-digest to summarize it.  Then, let's discuss the worst couple of queries.  Fixing slow queries is more likely to give your performance a boost than any tuning.

But, if you would like more tuning advice, let's see the GLOBAL STATUS.  In particular, follow the directions here .


Exporting Access DB to SQL Server: unexpected EOF? How do I correctly export a table from an old Access .mdb to SQL Server 2012?

In Access I do: right click on table, export as text file and use comma or tab separator.
My line endings are CRLF as seen from Notepad++

Then when I try to import into my RDS SQL Server with

bcp dbname.dbo.tablename in C:\AllModels.txt -n
    -S servername -U username -P password -b 10000

I am getting:

  Starting copy...
  SQLState = S1000, NativeError = 0
  Error = [Microsoft][SQL Server Native Client 11.0]Unexpected EOF encountered in BCP data-file

help help

First, use tab delimited files unless you want to use -t to specify a different delimiter (such as a comma). By default it will accept the tab.

Next, unless you're exporting data with the -n flag, use -c to import the data.

Finally, based on your comments, it looks like there is a mis-match between the size of your table columns and the actual width of the data. That's why you're getting notified the data is being truncated. Match those two up and you'll be all set.

You can check out the MSDN page on bcp for more information on flags, etc.


Benefits of using Staging Database while designing Data Warehouse I am in process of designing a Data Warehouse Architecture.
While exploring various options to Extract data from Production and putting into Data Warehouse, I came across many articles which mainly suggested following two approaches -

  Production DB ----> Data Warehouse (Star Schema) ----> OLAP Cube
  Production DB ----> Staging Database ----> Data Warehouse (Star Schema) ----> OLAP Cube

I am still not sure which one is the better approach in terms of Performance and reducing processing load on Production database.

Which approach you find better while designing Data Warehouse ?

There are a few potential advantages of using an intermediary staging database, which may or may not apply to your situation.  There is no perfect, one-size fits all solution.  Some of the potential advantages include:

If it is appropriate, you can take a snapshot of your production database (you may have a daily backup or hot-site snapshot already) and then do your ETL from the restored backup or snapshot.  This could save load on your production database.
You may need complicated processing for your ETL which requires many intermediate tables which have no use except for the ETL process.  You may not want to clutter your data warehouse with these intermediary tables.
Your raw data may not be available all at once and you need somewhere to accumulate it before starting your ETL process to build your data warehouse.
Your data warehouse may have production window requirements which can't be met by your ETL and so you need to stage your "output" (i.e. new records for the data warehouse) rather than or in addition to your production database.
It may be that your ETL process creates large intermediate tables.  Sometimes space management is easier if you start with an empty model database for your ETL staging area and then "throw it away" each day rather than trying to recover the space in a more surgical way, as you might do with a production or reporting database.

There are possible disadvantages too, which may or may not matter to you.  Chief among these is having to have another database server.  A lot of the advantages could be meaningless if you are using the same server to host the production and/or data warehouse databases.


Best way to document Dwh views I works in a company that provide Dwh views as an API for external Customers (We use MS-SQL 2008 & 2012).

every once in a while we release new views and updates the existing views.

We have difficult to track and document all the updates and changes (currently it is documented in WORD document).

In addition we got complains from customers telling us that the documentation isn't detailed enough and they want to see Entity Relationship Diagram (ERD), primary and foreign keys - in addition to the descriptions of the views.

My first thought is to make it available online (like other APIs documentation I saw) rather then WORD or PDF document.

My question is, do you have any experience or any suggestion what is the best way to document Dwh views?
Do you know any automation tools (free or not) that can help with this task?

Thanks,

There are basically two ways to do this in a maintainable way:

Use a metadata framework to generate the data warehouse
Use Extended properties
(http://technet.microsoft.com/en-us/library/ms190243(v=sql.105).aspx)

Ad 1) The reason you are having this issue on the first place is that you are not creating metadata first and then using that metadata (with a simple code generator) to generate the tables and views in the database. There are several frameworks available that will allow you to have an almost pure meta driven data warehouse model. Check out TimeExtender (http://www.timextender.com/) and Vulcan (https://vulcan.codeplex.com/) for a few examples. With metadata driven modelling, documentation happens as a natural part of the modeling process

Ad 2) If you already have a model (which it sounds like you do) a great way to document it to end users, is to add Extended properties to the views as you develop/maintain them. These properties be added both to the view itself and to do the columns in the view. With a simple piece of code in your favourite programming languages - you can query the extended properties to auto generate documentation. A good way to expose this documentation to the end user is to render web pages, so they can navigate the data model from there. Another advantage of this model is that the documentation now lives inside the database (you can back it up as part of your routine backup procedure) and the BI developers have it available at their fingertips (nobody likes to document code in Word documents)


Is it possible to make database owner read only? The database owner can revoke certain privileges from themselves such as INSERT but is it possible to revoke further privileges such as DROP for read-only operation?

The privilege to DROP an object does not exist as such and thus can't be revoked. Owning an object implies the right to drop it, and having the right to drop an object implies owning it. Here's a relevant excerpt from GRANT's documentation, which is pretty explicit about this point:

  The right to drop an object, or to alter its definition in any way, is
  not treated as a grantable privilege; it is inherent in the owner, and
  cannot be granted or revoked. (However, a similar effect can be
  obtained by granting or revoking membership in the role that owns the
  object; see below.) The owner implicitly has all grant options for the
  object, too.


RESOURCE_SEMAPHORE_QUERY_COMPILER waits in Microsoft SQL Server 2008 Enterprise Could fragmented indexes on Microsoft SQL Server 2008 Enterprise cause RESOURCE_SEMAPHORE_QUERY_COMPILER waits?

The last two weeks one of our applications has had downtime due to RESOURCE_SEMAPHORE_QUERY_COMPILER waits.  We've "fixed" them by rebuilding indexes; it was just a shot in the dark.

The application vendor has "dug in" and "found the root cause" which they claim is index fragmentation and their recommendation was to implement the workaround we'd already put in place: rebuild indexes weekly (was in place at time of issue) and reorganize nightly (new).

My concern is the application vendor just took our work and claimed it as their own.  When we originally stumbled across the fix,  we considered it a temporary workaround unlikely to cause issues (its hard to object to rebuilding/reorganizing indexes). This issue started just after our largest user's busy time, so usage and index fragmentation was down. We had just purged a significant amount of data, so percentage fragmentation would get higher easier (it peaked about 22% when we were having the semaphore issue) but the percentages were still significantly down.

My concern is just we're putting a band-aide on the situation and that its just a matter of time before we have issues again.  If the query was taking a long time, I could understand, but it seems odd to me that we'd be having memory issues while compiling the query.

Random bits of information:

Two application servers with about 130 concurrent users
Dedicated database server

Microsoft SQL Server 2008 Enterprise
2 x Xeon E5-2650 @ 2.3 GHz
64 GB of ram (don't know details)
Gigabit Ethernet x 3

The datafile for the database is 199GB
According to sp_spaceused

database_size is 37831736 KB
index_size is 54547920 KB

The log and datafile are one dedicated drives; both drives are on iSCSI SAN and are RAID10
There is no partitioning in the database

I apologize if I left off something important; let me know and I'll try to add it.

Edit:

When we reorganize daily, we are recompiling statistics and obviously when we rebuilt indexes that would be done, so I'll spend some time looking into our query plans.  I really appreciate the guidance.

Could fragmented indexes on Microsoft SQL Server 2008 Enterprise cause RESOURCE_SEMAPHORE_QUERY_COMPILER waits?

No. At least not directly, and even indirectly is not fragmentation but could be outdated stats at play (which rebuilding index also fixes). Read Plan Caching and Recompilation in SQL Server 2012, even though is 2012 a lot of it applies to 2008 still. The problem is that your plans are not reused and you suffer from high re-compilations. Read the white-paper, it has tonnes of resources and explains a lot.


Mongo Zone Sharding Implementation Issue As I am new with MongoDB.

I have configured 2 config server, 2 shards and all are connected with mongod client. Also, One Mongos client is UP and Running.

I am currenctly implementing Sharding using Mongo Zone. As I have to store the data accourding to country/Zone. But facing below error in shards:

2018-05-03T16:22:40.200+0530 W SHARDING [conn13] Chunk move failed :: caused by ::
      NoShardingEnabled: Cannot accept sharding commands if not started with --shardsvr
2018-05-03T16:27:11.223+0530 I SHARDING [Balancer] Balancer move testdb.user:
      [{ zipcode: "380001" }, { zipcode: "39000" }), from rs1, to rs0 failed :: caused by ::
      NoShardingEnabled: Cannot accept sharding commands if not started with --shardsvr

I have already started server with --shardsvr and also mentioned in config file as well. But still I am facing issue.

Below are the all config files:

mongosconfig.cfg

systemLog:
  destination: file
  path: "c:\\data\\log\\mongos.log"
  logAppend: true
sharding:
  configDB: "rs0/192.168.1.113:27017,192.168.1.112:27017"
net:
  bindIp: 192.168.1.113
  port: 27018

mongoclientconfig.cfg - configserver1

systemLog:
  destination: file
  path: "c:\\data\\log\\mongoclient.log"
  logAppend: true
storage:
  dbPath: "c:\\data\\db\\configdb"
  journal:
    enabled: true
sharding:
  clusterRole: "configsvr"
net:
  bindIp: 192.168.1.113
  port: 27017
replication:
  replSetName: "rs0"

mongodconfig.cfg - shard1

systemLog:
  destination: file
  path: "c:\\data\\log\\mongod.log"
  logAppend: true
storage:
  dbPath: "c:\\data\\db\\clientdb"
  journal:
    enabled: true
sharding:
  clusterRole: "shardsvr"
net:
  bindIp: 192.168.1.113
  port: 27019
replication:
  replSetName: "rs0"

mongoclientconfig.cfg - configserver2

systemLog:
  destination: file
  path: "D:\\data\\log\\mongoclient.log"
  logAppend: true
storage:
  dbPath: "D:\\data\\db\\configdb"
  journal:
    enabled: true
sharding:
  clusterRole: "configsvr"
replication:
  replSetName: "rs1"
net:
  bindIp: 192.168.1.112
  port: 27017

mongodconfig.cfg - Shard2

systemLog:
  destination: file
  path: "D:\\data\\log\\mongod.log"
  logAppend: true
storage:
  dbPath: "D:\\data\\db\\clientdb"
  journal:
    enabled: true
sharding:
  clusterRole: "shardsvr"
net:
  bindIp: 192.168.1.112
  port: 27019
replication:
  replSetName: "rs1"

Below are the commands which i have used for zone wise sharding as per the mongodb documentation(official):

rs.initiate({_id: "rs0",members: [{ _id : 0, host : "192.168.1.113:27019" }]}) //for shard1
rs.initiate({_id: "rs1",members: [{ _id : 1, host : "192.168.1.112:27019" }]}) //for shard2

sh.addShard("rs0/192.168.1.113:27019"); //Adding shard1 from mongos client
sh.addShard("rs1/192.168.1.112:27019"); //Adding shard2 from mongos client
sh.addShardToZone("rs0","IND");
sh.addShardToZone("rs1","UK");
sh.addTagRange("testdb.user", {zipcode: "380001"}, {zipcode: "390000"},
"IND");
sh.addTagRange("testdb.user", {zipcode: "400000"}, {zipcode: "600000"},
"UK");
sh.shardCollection("testdb.user", { zipcode: 1 } );

Please help me get this resolve.

Thanks in Advance.

Problem here is your replSetName: parameters.

BOTH config servers must be at same replica set, f.ex. conf and then those shards (data nodes) both should have different replica set names. f.ex. rs0 and rs1.

Now your config1 is rs0 and config2 is rs1.


Is it bad practice to calculate within the insert query? I am trying to do a web application for time sheets.
I have three options to calculate the value of hours (AFAIK):

In the form
In the backend
In the query

Is it smart or bad practice to do this calculation in the SQL query?
with list as (select code_id,code_ppu from code)

insert into hr values
(default,'JD','20-0001','PM','testing',1.25,
(select code_ppu from list where code_id='PM'),
(select 1.25 * code_ppu from list where code_id='PM')),

(default,'JD','20-0001','COPY','testing',2.25,
(select code_ppu from list where code_id='COPY'),
(select 2.25 * code_ppu from list where code_id='COPY'));

db fiddle https://www.db-fiddle.com/f/f8nZn5LQfbmKxAdxX2fdej/50

INSERT INTO hr (hr_sign, hr_job, hr_code, hr_subject, hr_qty, hr_ppu, hr_sum)
SELECT 'JD',
       '20-0001',
       code_id,
       'testing',
       CASE code_id WHEN 'PM' THEN 1.25
                    WHEN 'COPY' THEN 2.25
                    END,
       code_ppu,
       code_ppu * CASE code_id WHEN 'PM' THEN 1.25
                               WHEN 'COPY' THEN 2.25
                               END
FROM code
WHERE code_id IN ('PM','COPY');

PS. It is safe to store the relation between code_id and according coefficient (set into hr_qty and used during hr_sum calculation) in separate table and use this table in data source instead of CASEs.

Is it smart or bad practice to do this calculation in the SQL query?

Calculations is a norma. But shown subqueries usage - hardly...

How do I transfer them as parameters?

For example, something like (not tested)
INSERT INTO hr (hr_sign, hr_job, hr_code, hr_subject, hr_qty, hr_ppu, hr_sum)
WITH
cte1 AS ( SELECT one_row
          FROM REGEXP_SPLIT_TO_TABLE(@packed_csv, ',') AS parameters(one_row) ),
cte2 AS ( SELECT SPLIT_PART(one_row, ':', 1) AS code_id,
                 SPLIT_PART(one_row, ':', 2)::DECIMAL AS multiplier
          FROM cte1 )
SELECT 'JD',
       '20-0001',
       code.code_id,
       'testing',
       cte2.multiplier,
       code.code_ppu,
       code.code_ppu * cte2.multiplier
FROM code
JOIN cte2 ON code.code_id = cte2.code_id;

where @packed_csv is a string like 'PM:1.25,COPY:2.25',


Best relational database structure for this data I'm in the process of creating a database scheme for the following scenario:

There are users
Users have roles (such as "Developer" or "CEO")
Roles have applications (such as "Topdesk")
Applications have permissions (such as "Update Knowledgebase")
A role can have permissions, if the role already has access to the application

Assuming no high-performance environment (no need to optimize for speed), what would be the best way to implement this schema? The database environment can be MySQL, MSSQL... it's more about the relational database design.

I myself have come up with the following:

The part I'm most uncertain about is of course the Applications_Permissions_Roles table. It is a linking table on top of another linking table. I've never used or seen this before. Another way to do it would be to replace it with a linking table between Roles and Permissions, and then use code or constrains to ensure the required relations... but that doesn't seem like a good solution to me. These things should be enforced on database-level if at all possible (and it seems possible), not on code-level.

Secondly, is the link between Permissions.Application and Applications.Id required? I use it because there may not be any rows in Roles_Applications (such as when you've just added a new application) and then it's not possible to work out which permissions belong to which application. It also is a single point of reference to lookup to which application a permission belongs. I guess this is right, but it also makes a circle in the database design. MSSQL errors on it when trying to set ON_DELETE or ON_UPDATE to cascade.

Any suggestions, or is this how it's supposed to be done? Any other suggestions regarding naming convention and such are also welcome by the way (perhaps as comment).

Thanks,
Luc

Edit: Changed title, hopefully making it clearer. The previous one was more comprehensive, but probably too convoluted.

The way you've modelled it is fine.  Your model ensures that the business rules are enforced by the database.

There are a couple of things you could do as an alternative.  One would be to eliminate the surrogate key on Roles_Applications.  As an intersection table, you could use the two foreign keys together as a composite primary key.  If you did this, that would propagate Role and Application down to your Applications_Permissions_Roles table.  This would have the advantage of giving you more of a "one stop shop" for your application permission data (i.e. fewer joins) without compromising your normalization in any way.

Another way you could go would be to simplify slightly and define a default permission for each application.  You could call it whatever you like, such as "default access" or "basic access" or "user" or whatever makes sense to you.  This would allow you to flatten your model and essentially drop the Roles_Applications table and join Applications_Permissions_Roles straight to Roles.  This would change the nature of the query that you would use to ask "which roles can access which applications?" but your business rules would still be enforced by the schema.


How to split url in sql server like this Here is the url

www.abc.com/a/b/c/d/e/f/g/h/i

I want result like this

www.abc.com/a/b/c/d/e/f/g/h/i
www.abc.com/a/b/c/d/e/f/g/h
www.abc.com/a/b/c/d/e/f/g
www.abc.com/a/b/c/d/e/f
www.abc.com/a/b/c/d/e
www.abc.com/a/b/c/d
www.abc.com/a/b/c
www.abc.com/a/b
www.abc.com/a
www.abc.com/

Please look out this code..

            declare @length as int
            declare @Totallength as int
            declare @min as int
            declare @strURL as varchar(max)
            set @strURL='www.abc.com/a/b/c/d/e/f/g/h/i'
            set @min=1
            set @length=(SELECT LEN(@strURL) - LEN(REPLACE(@strURL, '/', '')))
            set @Totallength=(select len(@strURL))
            if(@length>1)
            begin
            while(@min<=@length)
            begin
            --select @strURL=left(@strURL,charindex('/',@strURL)-1)
            SELECT  @strURL = SUBSTRING( @strURL, CHARINDEX('/', @strURL) + 1, @Totallength)
            print @strURL
            set @min=@min+1
            -- print @min
            end
            end

Here's one which is heavily based on Jeff Moden's DelimitedSplit8k:

declare @url VARCHAR(8000), @delimiter char(1)

set @url = 'www.abc.com/aaa/bb/c/ddd/ee/ff/g/h/iii'
set @delimiter = '/'

;WITH E1(N) AS (
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
                ),                          --10E+1 or 10 rows
       E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
       E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
 cteTally(N) AS (
                 SELECT TOP (ISNULL(DATALENGTH(@url),0)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
                ),
ctePos(N1) AS (
                 SELECT t.N-1 FROM cteTally t WHERE SUBSTRING(@url,t.N,1) = @delimiter
                 union all
                 select datalength(@url)
                )
select left(@url, N1) + case when ROW_NUMBER() OVER(ORDER BY N1) = 1 then '/' else '' end as URL
from ctePos order by N1 desc

This should outperform any loop / recursive code if used as a function.

DelimitedSplit8k source: http://www.sqlservercentral.com/articles/Tally+Table/72993/


MySQL order by rand but result must exist different specific value I'm trying the get random values from SQL but I want results must have minimum one specific value from one column, how can I do it?

For example;

TABLE;

id  |  category  |  name
----------------------------
1   |      1     |  A
2   |      2     |  B
3   |      3     |  C
4   |      1     |  D
5   |      3     |  E
6   |      3     |  F
7   |      2     |  G
8   |      3     |  H

When I query random four data with category id, results not have all different categories, sometimes result coming like this;

SELECT * FROM table WHERE category IN (1,2,3) ORDER BY RAND LIMIT 4

id  |  category  |  name
----------------------------
1   |      1     |  A
4   |      1     |  D
3   |      3     |  C
8   |      3     |  H

Category 2 is missing in this example but I want every category must in results like that;

id  |  category  |  name
----------------------------
3   |      3     |  C <-- One from category 3
4   |      1     |  D <-- One from category 1
7   |      2     |  G <-- One from category 2
5   |      3     |  E <-- All categories ok, it can be random from any of them

Sorry for my english, I hope I could explain what I wanted. Results must have minimum one row from where clause "category IN (X,Y,Z)".

Thank you.

WITH
cte AS ( SELECT id, category, name,
                ROW_NUMBER() OVER (PARTITION BY category ORDER BY RAND()) rn
         FROM test )
SELECT id, category, name
FROM cte
ORDER BY rn > 1, RAND()
LIMIT ? ;

fiddle

For MySQL version below 8 calculate rn in subquery using user-defined variables.


How to create a new instance of PostgresSQL on OS-X 10.8 Server After upgrading my MacMini home server from OS-X 10.7 (Lion) Server to OS-X 10.8 (ML) + Server.app, I saw PostgreSQL 9.1.4 was released.

Unfortunately, the setup process for PostgreSQL 9.1.4 has created these brand new databases: collab, webauth, postgres, tempalte0, template1; all the other databases and roles created by me seem to have been deleted!

To better understand what has happened:

> Which postgres

OSX 10.7.4: /usr/bin/postgres
OSX 10.8: /Applications/Server.app/Contents/ServerRoot/usr/bin/postgre

$PGDATA is now on:

OSX 10.7.4: "/var/pgsql"
OSX 10.8 "/Library/Server/PostgreSQL/Data"

After the update the /var/pgsql folder is gone.

Since I do not want to repeat this experience in future updates, I think it is a good idea to create my database on a separate cluster/instance.

How would advise me from here?

Is there any other way to avoid losing my databases?
Where and how should I create my own databases without interfering with Apple's databases?
How can I configure and start a second instance of PostgreSQL on the same OS-X server?

"all others databases and roles created by my own are lost!"

I can't speak to the best practices for creating a new database, but as part of the Mountain Lion Server upgrade all of the "old" Postgres data should have been backed up to a subdirectory under /Library(/Server?)  Unfortunately as soon as I discovered this I moved-and-renamed the directory for safekeeping so I don't have the exact name!

I have a completely separate install of Postgres that I built myself for webapp purposes, but I certainly wouldn't call what my approach a "best practice". Largely what I did was build a "stock" install using a very similar set of configure parameters as Apple did (discovered by running pg_config) but with a different install location and default port setting. Following that I coptied-and-tweaked /Applications/Server.app/Contents/ServerRoot/System/Library/LaunchDaemons/org.postgresql.postgres.plist and that's worked fine for me so far.


2 B-tree indices OR 1 GiST index on tsrange -- which will perform better? I have a table which is storing reservation data using the columns starts_at & ends_at Whenever I'm querying the table to find overlapping reservations, I have an option of using one of the following queries:

SELECT * FROM reservations
WHERE starts_at < '2014-01-03 00:00:00'
AND   ends_at   >='2014-01-01 00:00:00';

Or

SELECT * FROM reservations
WHERE tsrange(starts_at, ends_at) && ('2014-01-01 00:00:00', '2014-01-03 00:00:00')

I have regular B-Tree indices on the starts_at and ends_at columns, therefore the first query is always using them. However, unless I define a functional GiST index on the tsrange, the second query does a complete scan.

create index tsrange_idx on reservations using gist(tsrange(starts_at, ends_at));

My question is, as the table grows, which index is going to be faster? Probably the answer is obvious looking at the query execution plan, but I'm not well-versed with reading EXPLAIN ANALYZE output.

Timestamps with B-tree index
I suggest a third option: as long as your table holds two timestamp columns (which seem to be defined NOT NULL) I would use a single multicolumn index with opposed sort order (if no other considerations apply):
CREATE INDEX reservations_range_idx ON reservations using gist(starts_at, ends_at DESC);

More in these related answer:

Optimizing queries on a range of timestamps (two columns)
PostgreSQL index not used for query on range

As for the query, have a look at the SQL-standard operator OVERLAPS:
SELECT * FROM reservations
WHERE (starts_at, ends_at) OVERLAPS ('2014-01-01 00:00:00', '2014-01-03 00:00:00');

More in this related question on SO:

Find overlapping date ranges in PostgreSQL

Should be faster than two B-tree indices. Less disk space and cheaper to maintain. The burden on write operations is small
Range type with GiST index
With big tables, a GiST index on a range type is most probably faster, because it scales better. Storage on disk is considerably bigger and index maintenance a bit more expensive, though.
If you go that route, it would be more efficient to store your timestamp as range (tsrange or tstzrange) to begin with. A plain GiST index without the functional aspect is a bit faster.
CREATE TABLE reservation (
  reservation_id serial PRIMARY KEY
 ,span tsrange
 , ...
);

CREATE INDEX reservation_span_gist_idx on reservations USING GiST (span);

With the && "overlap" operator you already displayed in the question:
SELECT *
FROM   reservation
WHERE  span && ('2014-01-01 00:00:00', '2014-01-03 00:00:00');

Also, you might be interested in an exclusion constraint to rule out overlaps by design, which implements a GiST index like the one above automatically. There is a code example in the manual. This related answer on SO has more details:

Preventing adjacent/overlapping entries with EXCLUDE in PostgreSQL


Foreign key constraint based on conditional column I'm trying to figure out the best way to handle this situation:

I have a table set up as follows:

LEARNING CONTEXT
================
LearningContextID - INT - Surrogate Primary Key
EntityID          - INT - Foreign Key to an Entity Table
OutcomeID         - INT - Foreign Key to an Outcome Table
Context           - VARCHAR - Name of the Entity Table

I have 3 tables:
   University, Program, and Course which are all "entities".

How can I set up a foreign key constraint that will know which table to be linked to?  I suppose it's a composite key of EntityID and Context, but how can I make that conditionally constrain against the given Context's table?

How can I set up a foreign key constraint that will know which table to be linked to?

Either you can't, or you shouldn't. :)

The standard way to structure this is to have a parent Entity table, and a table-per-child for each of University, Program, and Course. The Entity table optionally contains a denormalized column that specifies the type of entity -- this has a bunch of advantages, so I recommend it. Also, at least in the development stages, I would recommend creating triggers to validate that rows in this structure end up in the right place (i.e., for a given EntityID, there should be only 1 matching row in all the child tables).

Then all you do is create the foreign key to the primary key of Entity.

What I've described looks like this (SQL Server syntax):

CREATE TABLE dbo.EntityType
(
    EntityTypeID tinyint NOT NULL
        PRIMARY KEY
);

CREATE TABLE dbo.Entity
(
    EntityID int NOT NULL IDENTITY
        PRIMARY KEY,
    EntityTypeID tinyint NOT NULL
        FOREIGN KEY REFERENCES dbo.EntityType(EntityTypeID)
);

CREATE TABLE dbo.University
(
    EntityID int NOT NULL
        PRIMARY KEY
        FOREIGN KEY REFERENCES dbo.Entity(EntityID)
);

CREATE TABLE dbo.LearningContext
(
    LearningContextID int NOT NULL IDENTITY
        PRIMARY KEY,
    EntityID int NOT NULL
        FOREIGN KEY REFERENCES dbo.Entity(EntityID)
);

This is the equivalent of subclassing in a database, if you're familiar with client-side programming constructs.

Note how this structure easily allows adding a new derived Entity subtype without changing the existing schema objects.


Roles and Schduled jobs I have some instances were some of the BI teams can create their own schduled jobs in the sql agent.

However i also have a couple house keeping jobs i don't want them to interfere with. Can I create a role and specify only people with this role can update the house keeping jobs?

If so how?

Based on the information in SQL Server Agent Fixed Database Roles, it appears that non-sysadmin users must be a member of one of the following fixed database roles in msdb to utilize the SQL Agent:

SQLAgentUserRole
SQLAgentReaderRole
SQLAgentOperatorRole

Each of the above fixed database roles progressively allows more authority than the previous one, but with regards to being able to modify jobs, all three indicate that permissions are granted for owned jobs only.

The way I interpret the information in this post is, if you are not a member of the sysadmin fixed role, you only have the permissions to modify jobs that you own, whether that be an individual or a windows group.

You should be able to easily test this theory by creating two or more users, assigning them to one of the above fixed database roles (making sure they are not members of sysadmin), creating individual jobs under each user and then attempting to alter jobs not owned by that user.

I've included some information below about the permissions for each of fixed database roles mentioned above (highlighting mine).

SQLAgentUserRole Permissions

  SQLAgentUserRole is the least privileged of the SQL Server Agent fixed
  database roles. It has permissions on only operators, local jobs, and
  job schedules. Members of SQLAgentUserRole have permissions on only
  local jobs and job schedules that they own. They cannot use
  multiserver jobs (master and target server jobs), and they cannot
  change job ownership to gain access to jobs that they do not already
  own. SQLAgentUserRole members can view a list of available proxies
  only in the Job Step Properties dialog box of SQL Server Management
  Studio. Only the Jobs node in SQL Server Management Studio Object
  Explorer is visible to members of SQLAgentUserRole.

SQLAgentReaderRole Permissions

  SQLAgentReaderRole includes all the SQLAgentUserRole permissions as
  well as permissions to view the list of available multiserver jobs,
  their properties, and their history. Members of this role can also
  view the list of all available jobs and job schedules and their
  properties, not just those jobs and job schedules that they own.
  SQLAgentReaderRole members cannot change job ownership to gain access
  to jobs that they do not already own. Only the Jobs node in SQL Server
  Management Studio Object Explorer is visible to members of the
  SQLAgentReaderRole.

SQLAgentOperatorRole Permissions

  SQLAgentOperatorRole is the most privileged of the SQL Server Agent
  fixed database roles. It includes all the permissions of
  SQLAgentUserRole and SQLAgentReaderRole. Members of this role can also
  view properties for operators and proxies, and enumerate available
  proxies and alerts on the server.

  SQLAgentOperatorRole members have additional permissions on local jobs
  and schedules. They can execute, stop, or start all local jobs, and
  they can delete the job history for any local job on the server. They
  can also enable or disable all local jobs and schedules on the server.
  To enable or disable local jobs or schedules, members of this role
  must use the stored procedures sp_update_job and sp_update_schedule.
  Only the parameters that specify the job or schedule name or
  identifier and the @enabled parameter can be specified by members of
  SQLAgentOperatorRole. If they specify any other parameters, execution
  of these stored procedures fails. SQLAgentOperatorRole members cannot
  change job ownership to gain access to jobs that they do not already
  own.

  The Jobs, Alerts, Operators, and Proxies nodes in SQL Server
  Management Studio Object Explorer are visible to members of
  SQLAgentOperatorRole. Only the Error Logs node is not visible to
  members of this role.


Need help with correlated subquery I have two PostgreSQL tables that each contain a column called date_of_incident.

They each also have a column called incident_id and this is how the tables are related.

I want to update all rows of table 1 by setting date_of_incident equal to date_of_incident from the corresponding row of table 2 where incident_id from table 1 equals incident_id from table 2.

Can anyone can give a sample UPDATE? I'd appreciate it.

incident_id is unique in both tables and it is how the tables are related, though not by foreign key or constraint (sadly).  I have the extra condition of status = closed.  The only complication is that table 1 has a trigger function on update and I need to examine to see how the SQL will affect things.

Postgres allows to join tables in an UPDATE statement:

update table_1
   set date_of_incident = t2.date_of_incident
from table_2 t2
where table_1.incident_id = t2.incident_id;

This assumes that incident_id is unique in both tables.


Can a contained database SQL User be mapped to a remote user in a linked server? Using SQL Server 2017 Standard CU8 on Windows Server 2016.  I've create a contained database user (user defined only within a database with Containment type: partial) with type SQL user with password (so, not a Windows-based user account).  In a linked server definition, on the Security tab, in the Local Server login to remote server login area, is there any way to specify this user as a local user (in order to specifically map it to a remote user)?  I've tried just the username, and also prefixed with the database name (separated by \ and by .) but always get an error Error: ‘contained_user’ is not a valid login or you do not have permission.

In a way this makes sense, since a linked server is an instance-level object and having a contained database dependent on it would be somewhat contrary to the containment idea, but I haven't seen this limitation documented anywhere (such as https://docs.microsoft.com/en-us/sql/relational-databases/databases/contained-databases?view=sql-server-2017#Limitations or https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-addlinkedsrvlogin-transact-sql?view=sql-server-2017) so wondered if I was just getting the syntax wrong.

By the way, a Windows login-based user created within a contained database CAN be associated with a linked server.

thanks!

Martin

FYI, per Microsoft Support, this is expected behavior - a contained database user cannot be configured as the Local Login for a local-to-remote security mapping in a linked server.

thanks

Martin


Designing an ad banner tracker I'm creating an ad banner tracker for the ad banners on my website by using PHP. The tracker would track unique impressions, total impressions, unique clicks and total clicks of each banner.

The method I would use for detecting unique impressions/clicks is using IP. I'm thinking that the following four tables would be needed:

Banner - For storing information of each banner.
Unique Impression Information - Mainly for storing IPs of visitors who view the banners.
Unique Click Information - For storing detailed information (IP, country, device, browser, operation system, etc) of unique visitors who clicked the banners.
Total Click Information - For storing detailed information (IP, country, device, browser, operation system, etc) of every visitors who clicked the banners.

For storing the counts of impressions and clicks, I would use these four columns of the Banner table: "Unique Impression", "Total Impression", "Unique Click" and "Total Click".

The logic of counting impressions (or clicks) would be like:

If a visitor's IP is not found in the database, the IP would be stored in the database and the visitor would increase the values of both the Unique and Total Impression (or Click) columns of the Banner table by 1.
If a visitor's IP is found in the database, the visitor would only increase the value of the Total Impression (or Click) column of the Banner table again by 1.

I for now have no desire to know detailed information of visitors who only view but don't click the banners. That's why the visitor information stored in the Unique Impression Informaion table is only IP.

Advice on the efficiency of such a database design would be much appreciated.

== Update ==

The CREATE TABLE statements would be:

CREATE TABLE `Banner`
(
    `ID` int(11) NOT NULL AUTO_INCREMENT,
    `Unique Impression` int(15) NOT NULL,
    `Total Impression` int(15) NOT NULL,
    `Unique Click` int(15) NOT NULL,
    `Total Click` int(15) NOT NULL,
    /* Columns like `Name`, `Url`, `Src`, etc are omitted at here */
    PRIMARY KEY (ID)
);

/*
CREATE TABLE `Unique Impression IP` */ -- before changing table name
CREATE TABLE `Unique Impression Information`
(
    `ID` int(15) NOT NULL AUTO_INCREMENT,
    `IP` VARCHAR(255) NOT NULL,
    `Banner ID` int(11) NOT NULL,
    PRIMARY KEY (ID)
);

CREATE TABLE `Unique Click Information`
(
    `ID` int(15) NOT NULL AUTO_INCREMENT,
    `IP` VARCHAR(255) NOT NULL,
    `Banner ID` int(11) NOT NULL,
    /* Columns like `Country`, `Device`, `Browser`, etc are omitted at here */
    PRIMARY KEY (ID)
);

CREATE TABLE `Total Click Information`
(
    `ID` int(15) NOT NULL AUTO_INCREMENT,
    `IP` VARCHAR(255) NOT NULL,
    `Banner ID` int(11) NOT NULL,
    /* Columns like `Country`, `Device`, `Browser`, etc are omitted at here */
    PRIMARY KEY (ID)
);

As an example, the MySQL quies that will be used for detecting unique impressions would be:

$check_ip = mysqli_query($conn, "SELECT `IP`, `Banner ID` from `Unique Impression Information` WHERE `IP` = '$visitor_ip' AND `Banner ID` = '$banner_id'");

if (mysqli_num_rows($check_ip) === 0)
{
    mysqli_query($conn, "INSERT INTO `Unique Impression IP` (`IP`, `Banner ID`) VALUES ('$visitor_ip', '$banner_id')");
    mysqli_query($conn, "UPDATE `Banner` SET `Unique Impression` = `Unique Impression` + 1, `Total Impression` = `Total Impression` + 1 WHERE `ID` = '$banner_id'");
}
else
{
    mysqli_query($conn, "UPDATE `Banner` SET `Total Impression` = `Total Impression` + 1 WHERE `ID` = '$banner_id'");
}

For Unique Impression IP as there is no need for a autoincrement primary key, use VARBINARY(16) for IP address, and as the primary key. Use INET6_ATON() when inserting it to keep it in a compact form.

Use:

INSERT IGNORE INTO `Unique Impression IP` VALUES (INET6_ATON($var))

And look at rows affected to see if that exists or not for your UPDATE Banner statement branch. As it's a unique key (primary keys are unique) it will only be inserted once. This removes a race condition in your current code.


Replace specific part of a string I've got a table with multiple columns, APGRPID, VIRTKEY, LFDNR and PARAMETERLIST. I want to change a part of some strings in the PARAMETERLIST column.
In the PARAMETERLIST I want to replace the "value" for the PATH= setting.
Table:

APGRPID
VIRTKEY
LFDNR
PARAMETERLIST

1091.000000
121.000000
1
KF=138;SCANANDSEND=TRUE;ENDBARCODE=999999999999;PATH=K:\SSTEST;BARCODELENGTH=8

2092.000000
130.000000
8
KF=138;  PATH=s:\

2101.000000
114.000000
3
DESIGNATION=JOB;  KF=81;  PATH=w:;  SENDALLDATA=TRUE

2102.000000
116.000000
3
KF=76;  PATH=\srv05\L3OERAPMFC;  SENDALLDATA=TRUE

2303.000000
114.000000
2
KF=386;  PATH=W:;  SENDALLDATA=TRUE

The main problem I have is the randomness of the position of the PATH= parameter in the PARAMETERLIST column and the variability of the number of elements in the column if I try to CROSS APPLY a STRING_SPLIT() or anything.
Platform: Microsoft SQL Server Enterprise (Ver. 14.0.1000.169)

Given your test data, this will parse out the section of the string you want between either the first semi-colon after the PATH=, or the end of the string if that doesn't exist. From here, you should be able to use an update to replace that section of the string.
Further reading: Get The Text Between Two Delimiters
SELECT
    x.*,
    SUBSTRING(x.PARAMETERLIST,
        CHARINDEX('PATH=', x.PARAMETERLIST) + LEN('PATH='),
        CASE WHEN x.PARAMETERLIST NOT LIKE '%PATH=%;%'
             THEN LEN(x.PARAMETERLIST)
             ELSE CHARINDEX(';', x.PARAMETERLIST, CHARINDEX('PATH=', x.PARAMETERLIST) + LEN('PATH='))
                  - LEN('PATH=') - CHARINDEX('PATH=', x.PARAMETERLIST)
        END
             ) AS parsed_string
FROM
(
    VALUES
        ('KF=138;SCANANDSEND=TRUE;ENDBARCODE=999999999999;PATH=K:\SSTEST;BARCODELENGTH=8'),
        ('KF=138; PATH=s:\'),
        ('DESIGNATION=JOB; KF=81; PATH=w:; SENDALLDATA=TRUE'),
        ('KF=76; PATH=\srv05\L3OERAPMFC; SENDALLDATA=TRUE'),
        ('KF=386; PATH=W:; SENDALLDATA=TRUE')
) AS x(PARAMETERLIST);


Cannot create schema : permission issue I am trying to create a schema.

But I am getting this error :

mysql> create schema myBase;
ERROR 13 (HY000): Can't get stat of './myBase' (Errcode: 13 -
Permission denied)

When I do a ls -l on /var/lib I am getting :

drw-rw-rw- 8 mysql         mysql         4096 juin   6 14:41 mysql

I don't know what is wrong here.
I anyone knows..

You also need to have execute otherwise you cannot "CD" into the directory nor MySQL will be able to.

Run this to fix directories recursively:

for dir in $(find /var/lib/mysql -type d); do chmod u+x $dir; done


Oracle Global Statistics preference NO_INVALIDATE From the documentation, NO_INVALIDATE is a Global statistics preference Parameter.

  ..controls the invalidation of dependent cursors of the tables for which statistics are being gathered. It does not invalidate the dependent cursors if set to TRUE. The procedure invalidates the dependent cursors immediately if set to FALSE. Use DBMS_STATS.AUTO_INVALIDATE to have the system decide when to invalidate dependent cursors. This is the default.

My question is what is the meaning of dependent cursors of the table?

Simply put, they are the parsed SQL statements in the shared pool related to the table. When they are invalidated, they need to be hard parsed again the next time they run.

A typical use case for this: the database generates a suboptimal execution plan because of stale statistics on a table. The database fails to recognize this mistake on subsequent executions, but you notice it and decide to gather statistics. With NO_INVALIDATE => true, this will not have any effect on the SQL with poor performance, the database will continue to use the old, suboptimal plan. That is why you should gather statistics with the NO_INVALIDATE => false option in such cases.


How do I set the MySQL error level? Given the following table:

CREATE TABLE Test (
 id int(10) unsigned NOT NULL PRIMARY KEY,
 value int(10) unsigned NOT NULL
) ENGINE=InnoDB;

If I insert a value with only the id field set:

INSERT INTO Test (id) VALUES (1);

On my development server, I get a warning:

  Warning: #1366 Incorrect integer value: '' for column 'value' at row 1

On my production server, I get an error:

  ERROR 1364 (HY000): Field 'value' doesn't have a default value

What should I configure on my development server to make it behave like my production server?

Have a look at http://dev.mysql.com/doc/refman/5.5/en/sql-mode.html

You can set this for your current session, or in your mysql config

my.cnf (Unix operating systems) or my.ini (Windows)

sql-mode="modes"

On your production server use the following query to determine the current mode:

SELECT @@GLOBAL.sql_mode;
SELECT @@SESSION.sql_mode;

On your development server you can set it for the current session with

SET GLOBAL sql_mode = 'modes';
SET SESSION sql_mode = 'modes';


Creating a database structure for a quiz My app has this menu:

TestMode - make a quiz with 26 rndom questions from the database with a timer of 30 minutes.
LearningMode - make a quiz with all the questions from my db, displaying the correct answers.
Statistics- display % of quiz passed and the questions with wrong answers (displaying the correct answers).

each question have 3 choices(1 choice can be true, 2 choices can be true, or all can be true).
question can have a picture or not.

For now my db structure looks like this:

I don't know if is better if I put answers in questions; and if I do that, how to store the correct answer.

The correct answer is the property of the question.

Thus, the question table should be improved by a column named correct_answer_id, which will be a foreign key to answer(id_answer).

We can see some similar in the database of the Stack Exchange - here the accepted answers are in the AcceptedAnswerId column.

This structure has a possibility of inconsistence - nothing guarantees that the correct_answer_id refers to an answer which belongs to the current question. Ideally, you should use contraints to ensure that.


Finding what the source of a specific PostgreSQL configuration setting If I get it right, settings are hierarchical:

postgres.conf->
postgres.auto.conf (ALTER SYSTEM)->
ALTER DATABASE(where applicable)->
ALTER USER(where applicable)->
SET SESSION(where applicable)->
SET LOCAL(where applicable)

let's say I have enable_seqscan set to off at some point. SHOW or current_setting( or pg_settings will show me the current value only. But to check why I have it off, I have to check whole chain. Eg I suspect somebody set it per user or per database, or in auto.conf - to find how wide the setting is I have to check em all. Otherwise resetting value can fail, eg:

vao=# show enable_seqscan;
 enable_seqscan
----------------
 off
(1 row)

vao=# set enable_seqscan to default;
SET
vao=# show enable_seqscan;
 enable_seqscan
----------------
 off
(1 row)

Because it is so for user vao, or for database vao, or deeper...

What is a short way to find the primary source of current value?.. Or preferably matrix of all sources for the setting. Is there any interface or monkey hack?

update to reflect brilliant Abelisto answer:

source, sourcefile from pg_settings is a wonderful source of information, still I'm asking for a monkey way to check for those reasons: to see the database or user in source I have to relogin - obviously. So before they actually are applied to the session, I can't check them. local and session set shows both SESSION (which kind of reasonable too) and lastly I still have to reset to default step by step all hierarchy. Eg:

vao=# select setting, source, sourcefile from pg_settings where name = 'enable_seqscan';
 setting |  source  | sourcefile
---------+----------+------------
 off     | database |
(1 row)

vao=# select * from pg_file_settings where name = 'enable_seqscan';
                    sourcefile                     | sourceline | seqno |      name      | setting | applied | error
---------------------------------------------------+------------+-------+----------------+---------+---------+-------
 /etc/postgresql/9.6/main/a                        |          1 |     1 | enable_seqscan | off     | f       |
 /var/lib/postgresql/9.6/main/postgresql.auto.conf |          3 |    26 | enable_seqscan | on      | t       |
(2 rows)

So If I don't check every source my changes can be not respected.

pg_settings - look for source*

For example, after

alter system set array_nulls to off;

(and reloading configuration)

select * from pg_settings where name = 'array_nulls';

╔═[ RECORD 1 ]════╤════════════════════════════════════════════════════════════════════════════════════════════════════════════╗
║ name            │ array_nulls                                                                                ║
║ setting         │ off                                                                                ║
║ unit            │ ░░░░                                                                                ║
║ category        │ Version and Platform Compatibility / Previous PostgreSQL Versions                                          ║
║ short_desc      │ Enable input of NULL elements in arrays.                                                                   ║
║ extra_desc      │ When turned on, unquoted NULL in an array input value means a null value; otherwise it is taken literally. ║
║ context         │ user                                                                                ║
║ vartype         │ bool                                                                                ║
║ source          │ configuration file                                                                                ║
║ min_val         │ ░░░░                                                                                ║
║ max_val         │ ░░░░                                                                                ║
║ enumvals        │ ░░░░                                                                                ║
║ boot_val        │ on                                                                                ║
║ reset_val       │ off                                                                                ║
║ sourcefile      │ /var/lib/postgresql/9.5/main/postgresql.auto.conf                                                          ║
║ sourceline      │ 3                                                                                ║
║ pending_restart │ f                                                                                ║
╚═════════════════╧════════════════════════════════════════════════════════════════════════════════════════════════════════════╝

Update
And there is the query to get db/role settings, without relogin required:

with
  opt_db as (
    select s.*, d.datname, opt_name, opt_value from
      pg_db_role_setting s join
      pg_database d on (s.setdatabase = d.oid) cross join lateral (
        select
          split_part(opt, '=',1) as opt_name,
          split_part(opt, '=', 2) as opt_value
        from
          unnest(setconfig) as opt) as opt
    ),
  opt_role as (
    select s.*, r.rolname, opt_name, opt_value from
      pg_db_role_setting s join
      pg_roles r on (s.setrole = r.oid) cross join lateral (
        select
          split_part(opt, '=', 1) as opt_name,
          split_part(opt, '=', 2) as opt_value
        from
          unnest(setconfig) as opt) as opt
  )

select
  datname,
  rolname,
  opt_name,
  opt_db.opt_value as db_value,
  opt_role.opt_value as role_value
from opt_db full join opt_role using(opt_name);

Possible result:

╔══════════╤══════════╤═════════════╤═════════════════╤═════════════════╗
║ datname  │ rolname  │  opt_name   │    db_value     │   role_value    ║
╠══════════╪══════════╪═════════════╪═════════════════╪═════════════════╣
║ postgres │ nd       │ search_path │ "$user", public │ "$user", public ║
║ postgres │ postgres │ array_nulls │ off             │ on              ║
║ ░░░░     │ nd       │ foo.bar     │ ░░░░            │ 3               ║
╚══════════╧══════════╧═════════════╧═════════════════╧═════════════════╝


Sql Server (2017?) Lat/Long index and query TLDR: Given a set of LAT/LON => index and return "nearby" results.

I'm working my way through a MEAN Node.js/MongoDB/Express/Angular book. It's definitely not something I'm comfortable with - I'm more comfortable with Sql Server. I'm also looking at Sql Server 2017 in docker containers.

The exercise I'm working on has a Yelp Type application: Given a location, show nearby businesses and give the user the ability to Rate those businesses.

Where I'm at is planning on moving the backend database/api from MongoDB/Express/Node into Sql Server, Asp.net MVC.

Given the sample layout

Is this where I should be looking? I don't see exactly what I'm looking for...

Create table with lat/long coords
index the coords
query nearby

I'm still looking, but I'm not finding what I'm looking for...

edit: Close question here, but no cigar

edit: getting closer (mainly from this blog post... Now trying to figure out how to index and quickly find "close" points

USE master
GO

IF EXISTS(select * from sys.databases where name='MEAN')
    DROP DATABASE MEAN
GO

CREATE DATABASE MEAN
GO

USE MEAN;
GO

CREATE TABLE [dbo].[Landmark] (
    [ID]                INT IDENTITY(1, 1),
    [LandmarkName]      VARCHAR(100),
    [Location]          VARCHAR(50),
    [Latitude]          FLOAT,
    [Longitude]         FLOAT
)
GO

INSERT INTO [dbo].[Landmark] ( [LandmarkName], [Location], [Latitude], [Longitude] )
VALUES ( 'Statue of Liberty', 'New York, USA', 40.689168,-74.044563 ),
       ( 'Eiffel Tower', 'Paris, France', 48.858454, 2.294694),
       ( 'Leaning Tower of Pisa', 'Pisa, Italy', 43.72294, 10.396604 ),
       ( 'Great Pyramids of Giza', 'Cairo, Egypt', 29.978989, 31.134632 ),
       ( 'Sydney Opera House', 'Syndey, Australia', -33.856651, 151.214967 ),
       ( 'Taj Mahal', 'Agra, India', 27.175047, 78.042042 ),
       ( 'Colosseum', 'Rome, Italy', 41.890178, 12.492378 )
GO

ALTER TABLE [dbo].[Landmark]
ADD [GeoLocation] GEOGRAPHY
GO

UPDATE [dbo].[Landmark]
SET [GeoLocation] = geography::Point([Latitude], [Longitude], 4326);

GO

DECLARE @source geography = geography::Point(40.689168,y    -74.044563, 4326);

SELECT *, @source.STDistance([GeoLocation])
FROM [dbo].[Landmark]

There is a couple of approaches that can be used here depending on the exact requirements.

First of all you will need to create a spatial index on the Landmark table

CREATE SPATIAL INDEX Landmark_GeoLocation_SDX
ON [dbo].[Landmark] ([GeoLocation])
USING GEOGRAPHY_AUTO_GRID;

Then to query for all points within a specified distance you can do a query similar to the following

DECLARE @withinDistance float = 500.0; -- This is specified in metres
DECLARE @source geography = geography::Point(40.689168, -74.044563, 4326);

SELECT *, @source.STDistance([GeoLocation])
FROM [dbo].[Landmark]
WHERE @source.STBuffer(@withinDistance).STContains([GeoLocation]) = 1;

The other common query for this type of thing is to return the n closest points, otherwise known as a nearest neighbour or knn query.

DECLARE @neighbourCount int = 5; -- This is specified in metres
DECLARE @source geography = geography::Point(40.689168, -74.044563, 4326);

SELECT TOP (@neighbourCount) *, @source.STDistance([GeoLocation])
FROM [dbo].[Landmark]
WHERE @source.STDistance([GeoLocation]) IS NOT NULL
ORDER BY @source.STDistance([GeoLocation])


Differential Backups with Replication? I am pretty sure that Differential Backups with Replication is ok, but I am not finding anything that clearly says it. Nor am I finding anything indicating issues with it. Just looking for validation that two are completely compatible.

I have a server that has transaction replication and am considering a change to start using differential backups.

If the replication has to be rebuilt, a snapshot "copy" of the database is taken.  I am not seeing any way to ensure that happens with COPY_ONLY.

  Transactional replication is implemented by the SQL Server Snapshot Agent, Log Reader Agent, and Distribution Agent. The Snapshot Agent prepares snapshot files containing schema and data of published tables and database objects, stores the files in the snapshot folder, and records synchronization jobs in the distribution database on the Distributor.

I know that a full back up that does not use COPY_ONLY will distroy the backup chain if using Differential Backups.

  A copy-only backup is a SQL Server backup that is independent of the sequence of conventional SQL Server backups. Usually, taking a backup changes the database and affects how later backups are restored.

Question
Is there anything I need to address or adjust for if using Differential Backups and Replication?

I used to manage an environment where we had transactional replication and the backup schedule was Weekly Full, Daily Diff and log backups several times per day.

The backups didn't have any effect on replication. We had a few dozen publications consisting of hundreds of articles. If we had an issue with one, we would reset it and it would simply snapshot the tables in that publication.

The way it worked was that SQL did something similar to a select * from the tables involved along with reading their schema and uploading them to snapshot files on the distributor.  Then it dropped or truncated the destination tables on the replica server and used the files to rebuild them.


Can we have two primaries for the same shard in mongodb? Can we have a Mongodb deployment in two data centers in which the data set is divided into two shards A and B.

Data Center 1                     | Data Center 2

Shard A, Shard B                  | Shard A, Shard B

So some clients connect to Data Center 1 and write to the primary of Shard A and some other clients connect to Data Center 2 and write to the primary of Shard A there. Then some process does the reconciliation between Shards in two data centers.
The advantage of this setup is that clients can do a write to the data center that is closest to them and they can still read the latest data which is combined from Data Center 1 and 2.

MongoDB does not currently support a multi-master setup such as you describe.

If you were looking to provide localized regional writes via MongoDB, your best bet is to setup a sharded collection, using shard tag ranges. You would have 1 (or more) shard per region, with the primary residing within the region it manages and remote secondaries. Region would be the first member in your shard key and you would then peg a region's range to the shard that owns that region.

Reads for that region's data performed from a different region could then use a read preference of "nearest", which would result in a secondary read to the local secondary.


What is the best and easiest way to turn off SQL Server when not in use? Situation: developer workstations that typically just use the client tools to connect to other non-local sql servers. However, there are rare times that having the full version of sql server to do local development will be beneficial. In an effort to not waste system resources running sql server all the time I would like to know the best way to "turn it off" while still allowing usage of client tools (management studio, etc).

You may need to access SQL Server Configuration from the Start menu by selecting Microsoft SQL Server and then Configuration Tools, and then follow Thomas' path.

You can also configure those services so that they don't start automatically. I have mine set on "Manual", so if I want to use the SQL Server server, I just start the service. Ditto for SSIS, SSAS, SSRS, etc.

Since I have two versions of SQL Server running on my development machine, I don't want all of this stuff eating up my resources.


Viewing disk usage of temporary buffers of running queries in Postgres I know how to get the sizes of relations or total table disk usage per table in Postgres 9.5 (https://wiki.postgresql.org/wiki/Disk_Usage), but I have some queries running whose temporary disk buffers use significant amounts of space. How can I see how much each is using? My disk is slowly running out of space due to many long-running queries, and I need to kill one of them.

If you have queries generating temporary files, it could indicate that work_mem is not sufficiently sized or that the query is not optimised.

Try setting the parameter log_temp_files = 0 in your postgresql.conf.  This will log the creation of temporary files and the statement being executed.

Additionally an explain analyze of your query may show disk sort happening.

Taking the log output, you will see the size of temp  files created by your query.  You can use this to either fine tune the query or fine tune the work_mem parameter.