Effect of logical fragmentation of range scan in SQL Server Table To the best of my knowledge, this is what happens during a range scan of a table for some data when the table has got a clustered index:

The lower range is first found out, traced to the leaf node where it is present, then traversing the leaf level page by page through the next page pointer as long as the largest value in that page is less than the larger value of the range scan condition.

Once such a page is reached where the last row exceeds that, the process stops, does a binary search within that page to find out that record, and returns all records found till now.

This bringing up page after page can be expensive I/O operation if the pages are not in memory, hence to mitigate this, read-ahead scans are done at one level up by the parent pages, so that by the time the process advances to a leaf-level page to verify it, it has already been brought to memory by one of the parent level pages.

I am watching some SQL Server tutorial videos where it says that if there is logical fragmentation at the leaf level, so that the physical page order does not coincide with the logical page order, the read-ahead scan fails and the db is forced to do a full I/O operation to retrieve all pages.

Can someone tell me why? Why is the physical order important at all? Isn't the entire traversal done through logical traversal via the next page pointers of the leaf level pages?

What effect does the logical fragmentation have on the read-ahead scan exactly?

Can someone tell me why? Why is the physical order important at all?

Please note, I am speculating as to why the decisions were made given the best possible information I have available to me.
AFAIK it's due to the optimization of IO using a specific API which in this case is called ReadFileScatter.
ReadFileScatter requires a starting offset and a number of bytes to read from that point. Once read the number of contiguous on disk bytes are transferred into non-contiguous memory locations (aka buffers).
Because Read-Ahead, depending on version and edition, can read different amounts of data it ends up being a single large IO instead of a bunch of tiny ones which makes the throughput of the overall storage system more albeit at a cost to latency.
/Start of pure speculation/
Please keep in mind that when these APIs and choices were being made, rotational media (aka spinning rust) was the main source of storage and SSDs did not exist. Thus, having all of the items contiguous on disk means we don't have to reposition the track heads between reads which makes the overall single contiguous read faster than many smaller disjointed ones.
References and further reading:

Reading Pages (Books Online)
Sequential Read Ahead by Craig Freedman
How It Works: Bob Dorr’s SQL Server I/O Presentation
What do I need to know about SQL Server database engine I/O?

The first reference includes a diagram you may find helpful:


Select rows for which at least one row per set meets a condition I have following table:

create table test (
  company_id integer not null,
  client_id integer not null,
  client_status text,
  unique (company_id, client_id)
);

insert into test values
  (1, 1, 'y'),    -- company1

  (2, 2, null),   -- company2

  (3, 3, 'n'),    -- company3

  (4, 4, 'y'),    -- company4
  (4, 5, 'n'),

  (5, 6, null),   -- company5
  (5, 7, 'n')
;

Basically, there are 5 different companies, each one has one or more clients and each client has status: 'y' or 'n' (might be null as well).

What I have to do is to select all pairs (company_id, client_id) for all companies for which there is at least one client whose status is not 'n' ('y' or null). So for the example data above, the output should be:

company_id;client_id
1;1
2;2
4;4
4;5
5;6
5;7

I tried something with window functions but I can't figure out how to compare the number of ALL clients with the number of clients with STATUS = 'n'.

select company_id,
count(*) over (partition by company_id) as all_clients_count
from test
-- where all_clients_count != ... ?

I figured out how to do this, but I am not sure if it's the right way:

select sub.company_id, unnest(sub.client_ids)
from (
  select company_id, array_agg(client_id) as client_ids
  from test
  group by company_id
  having count(*) != count( (case when client_status = 'n' then 1 else null end) )
) sub

Basically you are looking for the expression:
client_status IS DISTINCT FROM 'n'

The column client_status should really be data type boolean, not text, which would allow the simpler expression:
client_status IS NOT FALSE

The manual has details in the chapter Comparison Operators.

Assuming your actual table has a UNIQUE or PK constraint, we arrive at:
CREATE TABLE test (
  company_id    integer NOT NULL,
  client_id     integer NOT NULL,
  client_status boolean,
  PRIMARY KEY (company_id, client_id)
);

Queries
All of these do the same (what you asked), which is the fastest depends on data distribution:
SELECT company_id, client_id
FROM   test t
WHERE  EXISTS (
   SELECT FROM test
   WHERE  company_id = t.company_id
   AND    client_status IS NOT FALSE
   );

Or:
SELECT company_id, client_id
FROM   test t
JOIN  (
   SELECT company_id
   FROM   test t
   GROUP  BY 1
   HAVING bool_or(client_status IS NOT FALSE)
   ) c USING (company_id);

Or:
SELECT company_id, client_id
FROM   test t
JOIN  (
   SELECT DISTINCT company_id, client_status
   FROM   test t
   ORDER  BY company_id, client_status DESC
   ) c USING (company_id)
WHERE  c.client_status IS NOT FALSE;

Boolean values sort FALSE -> TRUE -> NULL in ascending sort order. So  FALSE comes last in descending order. If there's is any other value available, then that one's picked first ...

Sorting null values after all others, except special

The added PK is implemented with a useful index for these queries. If you want faster, yet, add a partial index for query 1:
CREATE INDEX test_special_idx ON test (company_id, client_id)
WHERE  client_status IS NOT FALSE;

You could use window functions, too, but that would be slower. Example with first_value():
SELECT company_id, client_id
FROM  (
   SELECT company_id, client_id
        , first_value(client_status) OVER (PARTITION BY company_id
                                           ORDER BY client_status DESC) AS stat
   FROM   test t
   ) sub
WHERE  stat IS NOT FALSE;

For lots of rows per company_id, one of these techniques may be faster, still:

Optimize GROUP BY query to retrieve latest record per user


How to take a remote SQL server database backup to local machine? I've to take a SQL database backup from remote machine to local Linux machine and through command line. Hence executed following command -
sqlcmd -S xxx.xxx.xxx.xxx -U <username> -P <password> -Q "BACKUP DATABASE [TestUser] TO DISK='TestUser.bak';"

But this takes backup on machine where database server is installed. Is there any way I can get backup on machine where it is executed?
**NOTE: ** I can see many questions which asks same but are old ones and answer - either this is not supported or use of third party tools. Hence asking question again, in case has solution with latest stuff.

try to use an unc path in this way:
sqlcmd -S xxx.xxx.xxx.xxx -U <username> -P <password> -Q "BACKUP DATABASE [TestUser] TO DISK='\\server\shared_folder\TestUser.bak';"

You have to give SQL Server Service User the right to write to the remote path
\\server\shared_folder\
To do this you have to configure a local samba share connected to the source domain (as a domain member)


Is it a normal practice when there're 2 columns and if one is true the other must false, and vise versa? I have something similar to this, but not precisely it:
I sell only coffee and tea, and for the next 50 years I'll be selling only these. In a table "drinks" I have 2 columns: is_coffee? and is_tea?. Only one of these can be true, obviously, and if one is true, the other must be false.
Also, I have some columns that are specific to only coffee, when is_coffee? is true, and others to only tea.
Does it make sense hold all of them in a single table? Is it a normal situatation when there're 2 columns and if one is true the other must false, and vise versa?

This is called transitive functional dependency and should be avoided to have a 3th normal form design.
To solve it you should move it to the type of product to another table.
And then you can have another table linked with specific attributes of coffee entity and another one with specific attributes of the tea entity.
Don't be scared of the number of the tables, be scared of the consequences of not be in normal form.
(i.e. lack of performance, space, scalability)


Why do PostgreSQL examples say `+ integer '7'`? The PostgreSQL date functions documentation contains the following example:

date '2001-09-28' + integer '7'

Why does it consist of converting a string to an integer? Why not phrase it like this:

date '2001-09-28' + 7

Is there some tangible reason why integer '7' is preferable to plain old 7?

Interesting question, and I don't know the answer.  My best guess is that all the other examples use the same way of casting, and someone thought it is better having it consistent.  If you are pissed off irked by this, you can scroll to the bottom of the page and send a complaint using the form linked there.


Drop All Columns in a Table that are not Keys This may seem like an odd request but let me explain, there may be a tool that I haven't found that will do what I need. I couldn't make SSMS do what I needed

Goal: To deliver a Database Diagram that contains tableName, their PKs and FKs for related tables and their relations for several groups of tables.

What I have done is to create a copy of the DB and plan on removing all columns that are not PK or FK from each table. Which I am not thrilled with doing since it could range from 150 tables to 950 tables.

So I would like either (or both :)) the name of a tool that will diagram as I need or a script that I can use to delete all columns that are not PK or FK from all non-system tables.

This T-SQL will generate a series of ALTER TABLE statements that you can copy-and-paste into a query window in SSMS to remove columns that are neither primary keys nor foreign keys.

;WITH ForeignKeys AS (
SELECT t.object_id
    , c.column_id
FROM sys.foreign_keys fk
    INNER JOIN sys.foreign_key_columns fkc ON fk.parent_object_id = fkc.parent_object_id
    INNER JOIN sys.columns c ON fkc.parent_object_id = c.object_id
         AND fkc.parent_column_id = c.column_id
    INNER JOIN sys.tables t ON fk.parent_object_id = t.object_id
), PrimaryKeys AS (
SELECT t.object_id
    , c.column_id
FROM sys.key_constraints kc
    INNER JOIN sys.indexes i ON kc.parent_object_id = i.object_id
    INNER JOIN sys.index_columns ic ON i.object_id = ic.object_id
         AND i.index_id = ic.index_id
    INNER JOIN sys.tables t ON i.object_id = t.object_id
    INNER JOIN sys.columns c ON ic.object_id = c.object_id
         AND ic.index_column_id = c.column_id
)
SELECT 'ALTER TABLE ' + QUOTENAME(s.name) + '.' + QUOTENAME(t.name) + '
    DROP COLUMN ' + QUOTENAME(c.name) + ';'
FROM sys.tables t
    INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
    INNER JOIN sys.columns c ON t.object_id = c.object_id
WHERE NOT EXISTS (
    SELECT 1
    FROM ForeignKeys fk
    WHERE fk.object_id = t.object_id
        AND fk.column_id = c.column_id
    )
    AND NOT EXISTS (
        SELECT 1
        FROM PrimaryKeys pk
        WHERE pk.object_id = t.object_id
            AND pk.column_id = c.column_id
        )

It will correctly handle tables in various schemas, as well.

You may also have to first remove any default constraints:

SELECT N'alter table ' + QUOTENAME(t.name) +
    ' drop constraint [' + OBJECT_NAME(so.object_id) + N']'
FROM sys.objects so
    INNER JOIN sys.tables t
        ON t.object_id = so.parent_object_id
WHERE OBJECT_NAME(so.OBJECT_ID) like 'DF%'


Determine least number of columns required for unique (clustered) index I'm in the situation where I am encouraged to tune an existing database and in the process either introduce or remove indexes as seem best fit.

I was looking at a certain table and noticed that the Database Tuning Adviser had been previously used, due to the fact that a lot of the indexes began with _dta_..... (Hooray!)

I had a glace at the Clustered Index on said table

CREATE CLUSTERED INDEX [_dta_index_TABLENAME_c_5_1970822083__K6_K8] ON [dbo].[TABLE_NAME]
([register] ASC, [deleted] ASC)
WITH (
         PAD_INDEX = OFF,
         STATISTICS_NORECOMPUTE = OFF,
         SORT_IN_TEMPDB = OFF,
         DROP_EXISTING = OFF,
         ONLINE = OFF,
         ALLOW_ROW_LOCKS = ON,
         ALLOW_PAGE_LOCKS = ON
     )
     ON [PRIMARY]
 GO

So the index isn't unique and probably has a very special distribution in the histogram. Both columns register and deleted are defined as NULL in the table DDL:

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[TABLE__NAME](
    [delete]      [smallint] NULL,
    [timestamp]   [int] NULL,
    [root_id]     [int] NULL,
    [object_id]   [int] NULL,
    [object_type] [int] NULL,
    [register]    [int] NULL,
    [reg_type]    [int] NULL,
    [deleted]     [int] NULL
) ON [PRIMARY]

GO

...which got me thinking.

Question

How can I determine the least required number of columns to create an UNIQUE index with the following premises:

Seeing as delete will contain either a 1 or 0 I omitted this column from my train of thoughts
I don't know the data
I do know the number of records is 58'934'964 (and growing by 20 every second)
I do know the distinct counts of each column:

delete      = 1
timestamp   = 29'030'385
root_id     = 1'744'414
object_id   = 58'934'823
object_type = 33
register    = 9'229'854
reg_type    = 8
deleted     = 723'952

What I tried

I had a look at the sys.dm_db_missing_index_details DMV to determine if SQL Server knows when to create an UNIQUE index.

select

sddmid.index_handle,
db_name(sddmid.database_id),
so.name,
sddmid.object_id,
sddmid.equality_columns,
sddmid.inequality_columns,
sddmid.included_columns,
sddmid.statement

from sys.dm_db_missing_index_details sddmid
join sys.objects as so
    on so.object_id = sddmid.object_id
where database_id = db_id('DATABASE')
and so.name = 'TABLE_NAME'

But no details in that view.

Possible Solution

Would I really have to perform a statement over all columns like this:

select
-- distinct single column
count(distinct delete), count(distinct timestamp), count(distinct root_id),...
-- distinct double column
count(distinct delete,timestamp), count(distinct delete,root_id), ...
--- distinct triple column
count(distinct delete,timestamp,root_id), count(distinct delete,timestamp,object_id), ...
--- distinct quadruple column
count(distinct delete,timestamp,root_id,object_id), ...
...
from TABLE_NAME

until I have found a combination that is distinct/unique?

I guess there is a better solution...

I don't know how useful this will prove to be as I've not tested it out thoroughly, but you should be able to utilize Statistics Density Vector values to see what columns (and column combinations) will provide a higher likelihood of unique values.  The script listed below basically pulls all the Density Vector values for all stats on a given table.  This is not complex as it looks for the lowest Density Vector with the lowest Average Record Length.  Whatever records appear first in this result set should be your best candidates for unique columns.

OF NOTE: This only evaluates statistics that exist, so you may need to create multi-column statistics to fill in any gaps as auto-generated stats are limited to single columns.

Why is this better than the count approach?

You can specify sample rates with statistics which should reduce analysis times on larger tables, and the table scan (and locking that will come with it) when running the various COUNT aggregations also won't occur when creating the stats against a busy system.

Here's the script:

SET NOCOUNT ON

-- provide table name you wish to analyze
DECLARE @tableName NVARCHAR(256) = 'dbo.TABLE__NAME'
      , @statsName NVARCHAR(128), @dSQL NVARCHAR(1024)

DECLARE @stats_densityVector TABLE
(
    StatName    NVARCHAR(1024),
    AllDensity  REAL,
    AvgLength   BIGINT,
    Columns     NVARCHAR(1024)
)

DECLARE statsHeader CURSOR
FOR
    SELECT  name AS stats_name
    FROM    sys.stats s
    WHERE   s.object_id = OBJECT_ID(@tableName)

OPEN statsHeader
FETCH NEXT FROM statsHeader
INTO    @statsName

WHILE @@FETCH_STATUS = 0
BEGIN

    SET @dSQL = 'DBCC SHOW_STATISTICS (''' + @tableName + ''', ' + @statsName + ') WITH DENSITY_VECTOR, NO_INFOMSGS'
    INSERT INTO @stats_densityVector (AllDensity, AvgLength, Columns)
    EXEC (@dSQL)

    UPDATE @stats_densityVector
    SET StatName = @statsName
    WHERE StatName IS NULL

    FETCH NEXT FROM statsHeader
    INTO    @statsName
END

CLOSE statsHeader
DEALLOCATE statsHeader

SET NOCOUNT OFF

SELECT  OBJECT_SCHEMA_NAME(s.object_id) + '.' + OBJECT_NAME(s.object_id) AS table_name
        , dv.*
        , sp.rows_sampled
        , sp.rows
        , (1.0 * sp.rows_sampled) / (1.0 * sp.rows) AS sample_pct
FROM sys.stats s
        CROSS APPLY sys.dm_db_stats_properties (s.object_id, s.stats_id) sp
        INNER JOIN @stats_densityVector dv
            ON dv.StatName = s.name
WHERE s.object_id = OBJECT_ID(@tableName)
ORDER BY dv.AllDensity, dv.AvgLength

Here's a dbfiddle.uk example of how this would work on a contrived example.


How can I pivot a table then join multiple tables and change values based on information between tables The database I'm using has multiple tables storing different information types...it's very convoluted.

I'm trying to join this information into one table, with all of the details for a single ProductID on a single line. I know I need some combination of a pivot and joins, but I'm struggling with changing the values in the pivoted columns.

My Raw Tables are:

Inventory Table:

ProductID     ProductName
---------     -------------------------
S1810944      VIOLA ENDURIO PURE YELLOW
S1810949      ACHILLEA SUMMER PASTELS
S1841708      SPEARMINT

Property Table:

ID          PropertyName
----------- ------------
592         Category
595         Product Type
598         Sale
600         Group

PropertyChoice Table (each PropertyID from above has multiple choices):

ID          PropertyID  ChoiceName
----------- ----------- ------------
541         595         ORGANIC
544         595         REFINED
545         595         STANDARD
1050        598         Yes
1051        598         No
1509        592         FLOWER
1512        592         HERB
1502        600         ANNUAL
1503        600         PERENNIAL

and finally an InventoryProperty table (associating Inventory items to Property choices for a particular property):

ProductID       PropertyID  PropertyValue
-------------   ----------- -------------
S1810944        592         1509
S1810944        598         1051
S1810944        600         1502
S1810944        595         545
S1810949        592         1509
S1810949        598         1051
S1810949        600         1503
S1810949        595         544
S1841708        592         1512
S1841708        598         1050
S1841708        600         1502
S1841708        595         541

The output I'm going for should look like this:

ProductID     ProductName     Category  ProductType     Sale    Group
---------     ------------    --------- ------------    ------  -----------
S1810944      VIOLA           FLOWER    STANDARD        No      ANNUAL
S1810949      ACHILLEA        FLOWER    REFINED         No      PERENNIAL
S1841708      SPEARMINT       HERB      ORGANIC         Yes     ANNUAL

But the closest I have achieve are the Property Values listed like this:

ID          ProductName Category    ProductType Sale    Group
--------    ----------- --------    ----------- ----    -----
S1810944    VIOLA       1509        545         1051    1502
S1810949    ACHILLEA    1509        544         1051    1503
S1841708    SPEARMINT   1512        541         1050    1502

using this query:

SELECT
    I.ProductID,
    I.ProductName,
    P.[592] as Category,
    P.[595] as ProductType,
    P.[598] as Sale,
    P.[600] as [Group]
FROM InventoryProperty
PIVOT
    (
    MAX(PropertyValue)
    FOR PropertyID IN ([592],[595],[598],[600])
    ) as P
INNER JOIN Inventory as I on P.ProductID = I.ProductID

Obviously, I need to tie in the ChoiceName values on from the PropertyChoice table, but I'm not sure how to go about doing that without using CASE...I really want to avoid that because in my real tables, I have over 450 ChoiceName values. I'd also like to make this a query I can reuse even if I add in some new Choice names in the future.

Nevermind, I solved it myself using a short series of joins from the pivot fields:

SELECT
    I.ProductID,
    I.ProductName,
    A.ID as Category,
    B.ID as ProductType,
    C.ID as Sale,
    D.ID as [Group]
FROM InventoryProperty
PIVOT
    (
    MAX(PropertyValue)
    FOR PropertyID IN ([592],[595],[598],[600])
    ) as P
INNER JOIN Inventory as I on P.ProductID = I.ProductID
INNER JOIN PropertyChoice as A on P.[592] = A.ID
INNER JOIN PropertyChoice as B on P.[595] = B.ID
INNER JOIN PropertyChoice as D on P.[598] = C.ID
INNER JOIN PropertyChoice as E on P.[600] = D.ID


Cannot SUM the value of a sales amount field - various criteria, sales rep id and date ranges I wonder if someone can help me with this SQL Server 2016 query.
I have two tables with following data
TblSalesRep
This is the table holding the history of the territories that the sales rep looked after and the date range that they looked after it.

TerritoryId
SalesRep
FromDate
ToDate

15
Anne
2020-01-01
2020-06-02

15
Dave
2020-06-03
2020-06-16

15
Anne
2020-06-17
2020-06-22

18
Anne
2020-06-23
2999-12-31

tblSales
This is the table that holds the sales made by territory

TerritoryId
TransactionId
TransactionDate
ProductCode
Amount

15
1
2020-04-15
Bags
50

15
1
2020-04-15
Bags
50

15
2
2020-06-02
Bag
25

15
3
2020-06-03
Shoes
60

15
4
2020-06-07
Shoes
10

15
5
2020-06-17
Bags
15

15
6
2020-06-18
Bags
25

15
7
2020-06-25
Coat
100

18
8
2020-06-27
Bags
20

I’m trying to produce a report that displays the SalesRep and the total sold between two dates for a given SalesRep.
For example, if I provided a SalesRep = ‘Anne’ and a date range of ‘1 Jun 2020’ to ‘30 Jun 2020’ I would expect a result back of :
SalesRep    Sum
Anne        185

This is because it should exclude any transactions made between 03 Jun 2020 and 16 Jun 2020 as the territory was looked after by Dave for a couple of weeks and he conducted that business between those dates.
I’ve tried using the SUM() function whilst joining to TblSalesRep but the figures are not what I’m expecting.

There are more compact ways to write this, but this illustrates a way to do it.
First select the territories and periods that Anne was looking after that territory.
Then pull all the sales where the transaction date falls in one of the periods when Anne was covering the territory where the sale was made.
Then sum up the sales.
WITH
 territory_periods AS (
SELECT SalesRep, TerritoryID, FromDate, ToDate
  FROM tblSalesRep
 WHERE SalesRep = 'Anne'
)
,sales AS (
SELECT p.SalesRep, s.Amount
  FROM tblSales s
  JOIN territory_periods p
    ON s.TerritoryID = p.TerritoryID
   AND s.TransactionDate BETWEEN p.FromDate AND p.ToDate
)
SELECT SalesRep, Sum(Amount)
  FROM sales
GROUP
    BY SalesRep
;


Mysql replication is enabled but stuck at "waiting for binlog to be updated" I have set up replication between an existing database (server-id=23), and a new one (server-id=51).

I have followed the steps in this stackoverflow question, but replication is not happening.

On the master:

State: Has sent all binlog to slave; waiting for binlog to be updated

When I show slave hosts:

+-----------+------+------+-------------------+-----------+
| Server_id | Host | Port | Rpl_recovery_rank | Master_id |
+-----------+------+------+-------------------+-----------+
|        51 |      | 3306 |                 0 |        23 |
+-----------+------+------+-------------------+-----------+
1 row in set (0.01 sec)

On the slave:

State: Slave has read all relay log; waiting for the slave I/O thread to update it

show slave status

           Slave_IO_State: Waiting for master to send event
 ...snip...
         Master_Log_File: mysql-bin.000001
      Read_Master_Log_Pos: 98
           Relay_Log_File: mysqld-relay-bin.000002
            Relay_Log_Pos: 244
    Relay_Master_Log_File: mysql-bin.000001
         Slave_IO_Running: Yes
        Slave_SQL_Running: Yes

Is there something I need to do to get replication happening?

MySQL Replication is running just fine. How do I know ?

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

This indicates that the two replication threads are alive.

To verify this, run the following on the Slave:

SELECT COUNT(1) ReplicationThreadCount FROM information_schema.processlist
WHERE user = 'system user';

If ReplicationThreadCount > 1, then replication is running just fine.

You can also run SHOW PROCESSLIST; and look for two connections whose user is system user

The two states you mentioned for the Master and Slave are normal for a running replication status

As you add data to the Master, you can know that data is coming to the Slave by looking at

Read_Master_Log_Pos
Exec_Master_Log_Pos
Relay_Log_Space

in the output of SHOW SLAVE STATUS\G. These numbers will be changing, which indicate its working.


Conditional data flow task in SSIS Brief Architecture:

I am creating a SSIS package to generate Excel reports. There are different types of reports, So I have created a table to track each user request for report generation and tracking the status of request. A Sql job picks each request and generates the excel file on FTP server.

Problem:

In control flow, I am executing select on "ReportRequests" table and iterating recordset using a For Each Container. I have created a variable named "ReportType" to get column value from database.
In Data flow task, on basis of "ReportType" variable, I want to execute different queries on different databases to generate report. I will have 3-4 different ado.net sources with different queries. But those should work on basis of value of "ReportType"

How can I implement this conditional data flow in data flow task ?

You can create 3-4 different data flow tasks within your control flow (inside the Foreach loop container).

Place a sql script before the DF containers.  You can have the script actually do something or not.  The idea is to create a conditional from this task that leads to the 3-4 data flow tasks.

On the connector for each DF object, add a constraint that the variable == "somevalue".  Since each Connector has a different report type value, only one will execute.


Postgres text search has unexpected query conversion I have a dictionary built in this way:

DROP TEXT SEARCH DICTIONARY IF EXISTS simple_english_with_stopwords CASCADE;
CREATE TEXT SEARCH DICTIONARY simple_english_with_stopwords (TEMPLATE = pg_catalog.simple);
CREATE TEXT SEARCH CONFIGURATION simple_english_with_stopwords (copy = english);
ALTER TEXT SEARCH CONFIGURATION simple_english_with_stopwords
   ALTER MAPPING FOR asciihword, asciiword, hword, hword_asciipart, hword_part, word
   WITH simple_english_with_stopwords;

which should include not only generic terms but stop words.

Based on that I am expecting that something like:

SELECT to_tsvector('open plan') @@ to_tsquery('off <-> plan');

would return false as off is not in the text. But for some reason it is returning true.

If  I run just:

# SELECT to_tsquery('off <-> plan');
 to_tsquery
------------
 'plan'
(1 row)

which explains why the previous expression is returning true. But why the query has removed the off term? Any idea?

You have created a new dictionary and such, but you are not using it.

SELECT to_tsquery('simple_english_with_stopwords','off <-> plan');
    to_tsquery
------------------
 'off' <-> 'plan'
(1 row)

Or:

set default_text_search_config TO 'simple_english_with_stopwords';
SELECT to_tsquery('off <-> plan');
    to_tsquery
------------------
 'off' <-> 'plan'

The first, more verbose, option is better if you want to use FTS indexes, otherwise you run into mutability problems.


SQL Query to find the location of the running query? I am trying to get the information about the running queries for query performance monitoring purpose.

Is there a way to get the location of the running query in SQL-server?
For instance, if I have a stored procedure running in a server and if I'm getting all the activity monitoring data for the running query, I want to fetch the path of the stored procedure in which the query is running.

Is there a query to do this or any other way to get this path?

Example Path/hierarchy : sever/db/stored_procedure_name/query

I would use sp_whoisactive  with @get_full_inner_text and @get_outer_command :

--- download it from http://whoisactive.com
EXEC sp_WhoIsActive
    @filter = '',
    @filter_type = 'session',
    @not_filter = '',
    @not_filter_type = 'session',
    @show_own_spid = 0,
    @show_system_spids = 0,
    @show_sleeping_spids = 1,
    @get_full_inner_text = 1,
    @get_plans = 1,
    @get_outer_command = 1,
    @get_transaction_info = 0,
    @get_task_info = 1,
    @get_locks = 0,
    @get_avg_time = 0,
    @get_additional_info = 0,
    @find_block_leaders = 1,
    @delta_interval = 0,
    @output_column_list = '[dd%][session_id][sql_text][sql_command][login_name][wait_info][tasks][tran_log%][cpu%][temp%][block%][reads%][writes%][context%][physical%][query_plan][locks][%]',
    @sort_order = '[start_time] ASC',
    @format_output = 1,
    @destination_table = '',
    @return_schema = 0,
    @schema = NULL,
    @help = 0


FOUND_ROWS() returning incorrect value I have 5 databases, all of which have the same structure. One is local (MySQL version 5.6.17), the other four are on the same server (MySQL 5.6.11-log). Let's call them A, B, C and C1. C had some of its data imported from B, and C1 is a test database whose structure and data was imported from C.

I have the following query:

SELECT SQL_CALC_FOUND_ROWS
    customergroups.customergroupID AS 'Id',
    customergroups.customergroup AS 'Name'
FROM
    customergroups
ORDER BY customergroups.customergroupID ASC
LIMIT 10;
SELECT FOUND_ROWS();

It works correctly on local, A and B returning expected values (261, 278 and 36 respectively) however it returns 10 for C and C1 (instead of 14 and 16 respectively). Removing ORDER BY customergroups.customergroupID ASC fixes it, same as leaving only SELECT SQL_CALC_FOUND_ROWS customergroups.customergroupID AS 'Id' FROM customergroups

Oddly enough, a similar query on different tables on all databases work correctly:

SELECT SQL_CALC_FOUND_ROWS
    customers.customerID AS 'Id',
    customers.customer AS 'Name'
FROM
    customergroups
ORDER BY customers.customerID ASC
LIMIT 10;
SELECT FOUND_ROWS();

I am aware there is/was a relevant bug in MySQL (https://bugs.mysql.com/bug.php?id=68458) but I would prefer to avoid upgrading MySQL, especially as the query works correctly on other databases on the same server running the same MySQL version and it would take likely several hours if not longer to back everything up and then restore it due to a ton of data in some tables.

I have already compared the structure of the tables and they are identical (data is different though). Any idea what could be causing this weird behaviour and how to fix it? Adding GROUP BY to the statement didn't help, neither did optimize table customergroups.

Seems similar to this Bug #69166:  Wrong result of FOUND_ROWS() in MySQL 5.6:

  Description:

  Executing FOUND_ROWS() after SELECT SQL_CALC_FOUND_ROWS ... returns wrong result under certain conditions.
  Might be related to Bug #68458

  See steps to reproduce. ...

and of Bug #69119: Wrong FOUND_ROWS() on MySQL 5.6.11

  Description:

  FOUND_ROWS() return is wrong count.

It seems they have not been fixed.

In any case, it would be a good idea to upgrade to latest 5.6 (5.6.33 currently). Both of your installations use very old versions. After upgrading check again and if the error persists, report it in the above bug item (69119).

Related to the upgrade: I don't think you have to restore for an upgrade in same major version. I's good idea to take a backup first, before the upgrade procedure, just in case something goes wrong.


MySql Joining two tables I am running into an issue while trying to join two MySQL tables and getting output.

My first table named res having 14 columns related to trains bookings. It has all the information of all trains with their date of Journey, available tickets, and their fares.

Table definition for rest .

CREATE TABLE  `abcd`.`rest` (
`PNR` INT NOT NULL AUTO_INCREMENT ,
`Tr_Num` INT( 6 ) NULL ,
`Train_Name` VARCHAR( 60 ) NULL ,
`na` VARCHAR( 60 ) NULL ,
`age` INT( 3 ) NULL ,
`DOJ` DATE NULL ,
`seat` VARCHAR( 20 ) NULL ,
`src` VARCHAR( 60 ) NULL ,
`des` VARCHAR( 60 ) NULL ,
`class` VARCHAR( 60 ) NULL ,
PRIMARY KEY (  `PNR` )
) ENGINE = INNODB;

Table rest

This table has 10 columns

PNR=> Auto Increment. Primary. Int value
Train Number => Integer. Unique
Train Name => Varchar. Unique
Name of the passenger=> Varchar
Age => Int Value
date => date of Journey
Seating => Varchar
src => varchar
des => Varchar
class => varchar (ACI,ACII,ACIII,SL)

Table res and their attributes:

id=> Auto Increment.Primary Key
Train Number => Integer
Train Name => Varchar
src => Varchar
des => Varchar
date => date of Journey
AC_I => No of Tickets.Integer value
First_AC => Fare
AC_II => No of Tickets.Integer value
SEC_AC => Fare
AC_III => No of Tickets.Integer value
THIRD_Ac => Fare
SL=> No of Tickets for Sleeper Class.Integer value
SL_CLASS => Fare

The SQL command that I have used and was able to get something out of it but was not completely successful.

select rest.PNR, res.TRAIN_NUMBER,res.Tr_name,rest.na,rest.age, rest.doj,
rest.seat, rest.src, rest.des,rest.class, case when rest.class = 'AC I'then res.AC_I
  when rest.class = 'AC II' then res.AC_II
when rest.class = 'AC III' then res.AC_III
when rest.class = 'Sleeper Class' then res.SL_CLASS
end as Tickets
from rest, res where rest.DOJ = res.date order by PNR desc limit 2;

After running this command I get the output as below.

+-------+----------+----------+------+------+----------+------+------+------+------+--------+
| PNR   | TRAIN_NR | Tr_name  | Name | Age  | doj      | seat | src  | des  |class | Tickets|
+-------+----------+----------+------+------+----------+------+------+------+------+--------+
| 40    |  10008   |  Mum     |  S   |  42  |2018-04-27|  LB  |  M   |  H   | AC I |  480   |
+-------+----------+----------+------+------+----------+------+------+------+------+--------+

Now, What I am trying to do is along with this output I want to know fetch the Fare of the ticket.

I am expecting an output something like this.

+-------+----------+----------+------+------+----------+------+------+------+------+--------+--------+
| PNR   | TRAIN_NR | Tr_name  | Name | Age  | doj      | seat | src  | des  |class | Tickets| fare   |
+-------+----------+----------+------+------+----------+------+------+------+------+--------+--------+
| 40    |  10008   |  Mum     |  S   |  42  |2018-04-27|  LB  |  M   |  H   | AC I |  480   | ???    |
+-------+----------+----------+------+------+----------+------+------+------+------+--------+--------+

How can I achieve this?

select rest.PNR, res.TRAIN_NUMBER, res.Tr_name, res.AC_I, rest.na,rest.age
     , rest.doj, rest.seat, rest.src, rest.des, rest.class
     , case when rest.class = 'AC I' then res.FIRST_AC
            when rest.class = 'AC II' then res.SEC_AC
            when rest.class = 'AC III' then res.THIRD_AC
            when rest.class = 'Sleeper Class' then res.SL_CLASS
       end as fare
     , case when rest.class = 'AC I' then  res.AC_I
            when rest.class = 'AC II' then res.AC_II
            when rest.class = 'AC III' then res.AC_III
            when rest.class = 'Sleeper Class' then res.SL
       end as tickets
from res, rest
where rest.Tr_Num = res.TRAIN_NUMBER
order by PNR desc limit 4 ;


Monitoring Amazon RDS with pgtop I recently migrated a Postgres database to Amazon RDS and I am lacking a good live monitoring tool. I tried pgtop, but I can't get it to work on Amazon. I can see the header with the DB statistics (CPU, I/O, memory, etc.) but the query list is completely empty.

So, does anyone have any experience with pgtop specifically on RDS for PostgreSQL? I would like to know what queries are running, cpu usage, queries state, etc.

I don't know if that applies to Amazon RDS, but a possible reason for getting an empty list of queries with pgtop is when using a version older than 3.7.0 (released in August 2013) against a 9.2 or newer PostgreSQL instance.

I just noticed this with the ptop 3.6.2 package that currently ships with Ubuntu 12.04 LTS. I guess this may be also not fixed in other distributions.

The empty list problem occurs with older versions of pgtop because they expect the postgres process ID in  pg_stat_statements.procpid, and this column was renamed to pg_stat_statements.pid in PG 9.2.

This is combined with pgtop being unhelpful by not reporting any error when its SQL command fails. Instead it just displays a blank list of processes. The problematic query can be spotted in the server's log.


Efficient schema to handle time interval queries on a huge table? We have huge table (+10 millions rows) where we aggregate the values by a using a time interval search on a Datetime column. And right now, to build single page on our app, we are querying this table several times, resulting in a high delay on our queries.

This table have one particular property, the records are never updated after the insertion.

I see two solutions for this scenario, but I'm not sure which is better, and also which is recommended by database experts.

Optimize the query at maximum, trying to fetch everything in a single trip.
We can improve our database architecture to reduce the number of records, aggregating the old rows into auxiliar tables.

Example

My data look similar to a currency market, like this one: https://bitcoinity.org/markets. How they allow a quick query over different time intervals (minutes, hours, days, months, years...)?

Is there a well know solution for schemas like this one?

Background info

Ruby on Rails App;
MySQL
Few (or none) query optimization;
First rows dated from 2011.

Schema Details

Earnings

CREATE TABLE `earnings` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `earner_id` int(11) DEFAULT NULL,
  `sale_id` int(11) DEFAULT NULL,
  `amount` int(11) DEFAULT NULL,
  `created_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `index_earnings_on_sale_id` (`sale_id`),
  KEY `index_earnings_on_created_at` (`created_at`)
) ENGINE=Inno

Sample Query

SELECT DISTINCT *, count(amount) total FROM earnings
WHERE (created_at BETWEEN '2015-09-01 07:00:00' AND '2015-10-01 06:59:59')
  AND sale_id IN [....]
GROUP BY earner_id

Besides this query being very simple, it runs a lot of times for different timespan, like by month, or by the last 10 days. Thats why on my second idea (2.) I'm considering an aux table to cache the sums by each timespam. (see this example for the desired data aggregation https://bitcoinity.org/markets)

How many rows are being aggregated on each SELECT?

This should go without saying - ensure you have an index on the datetime column. Depending on what your query is doing you can also try putting a compound index on your datetime and the column you're aggregating to try to only use the index and avoid reading data blocks off the disk.

Please post an EXPLAIN EXTENDED on your SELECT query along with a CREATE TABLE and if you are doing any JOINS then include the CREATE TABLE on those tables too.

If you're using MyISAM switch to InnoDB for your table engine.

If your index is highly fragmented then you may want to consider partitioning your tables by date. If the index lookup isn't part of the problem then this won't help.

If the output of these queries can be cached and subsequent requests would still be valid with that data then cache as much of it as you can.

Also ensure your my.cnf is configured for your usage patterns.

As extra trivia, InnoDB benefits from having it's data files on fast storage and from high core speeds over more cores, especially when aggregation is being done.

[edit] Op posted SQL

You can likely drop a filesort by adding an ORDER BY NULL to the end of this query;

Without knowing how many rows are being aggregated in the query nor the cardinality of the sale_id and created_at columns it's difficult to guess if a compound index gives better performance. You may want to test it.

E.g.; drop separate indexes and create a compound (sale_id, created_at):

ALTER TABLE earnings DROP INDEX index_earnings_on_sale_id, DROP INDEX index_earnings_on_created_at, ADD INDEX index_sale_id_created_at (sale_id, created_at);

In your example you are doing a COUNT(amount) instead of SUM(amount). If your goal is to only count earnings by earner_id and not sum them then you can also try doing the select in an inner query and counting on the outer. This can sometimes improve performance depending on the query.

SELECT earner_id, COUNT(amount) AS total
FROM (
  SELECT earner_id, amount
  FROM earnings
  WHERE sale_id IN (428, 245) AND (created_at BETWEEN '2016-06-01 07:00:00' AND '2016-06-22 06:59:59')
) AS derived
 GROUP BY earner_id ORDER BY NULL


How to modify authentication plugin in mariadb starting with 10.4.1 to get mysql access for root user without password when migrating mysql to Mariadb At https://kofler.info/root-login-problem-mit-mariadb/ a method is described for modifying the plugin being used for authenticating a user

update mysql.user set plugin='' where user='root';

leading to

ERROR 1348 (HY000): Column 'plugin' is not updatable

https://mariadb.com/kb/en/library/mysqlglobal_priv-table/

explains that there is now a single column global_priv table instead and the original user table is now a view.

select * from mysql.global_priv where User='root';

is possible or

SELECT CONCAT(user, '@', host, ' => ', JSON_DETAILED(priv)) FROM mysql.global_priv where user='root';

for a more readable version.

Unfortunately the documentation says nothing about

How to modify the column under this design decision?
The motivation for the change?

It would be great to get these two questions answered - obviously #1 being the more pressing one.

see: https://mariadb.com/kb/en/library/authentication-plugin-unix-socket/

ALTER USER root@localhost identified via  unix_socket;
flush privileges;

also

mysql

now works for the root user without password.

The statement *"The unix_socket authentication plugin is also installed by default ..."

does have no effect if you migrate from Oracle's mysql to mariadb


Backup the whole SQL Server databases in one .bak file - SQL Server 2005 First I asked my question here:
Migrate all databases from one SQL Server 2005 to another

Many friends replied to it and I gathered lots of useful information.

I have another scenario in which we can have some hours of downtime. We want to close the SQL Server port on the firewall, backup the databases, move the backups to new server, restore them on our new server.

It can be achieved by creating backup of all databases including master DB. But in this case we will have 200 .bak files.

Is there any way to have a general .bak file including all of the data on our SQL Server? So we will easily restore this big .bak file in our new server?

Is there any internal option in SQL Server 2005 to restore the whole date on our new SQK Server 2005?

Thank you for your replies.

There is no built-in way to backup all the databases into a single file, and even if there was, that file would probably become impractically big.

I would go with the ideas you got from your original question, and implement those in an automated script that loops through all your databases; One script to back every database up, one script to restore those backups on the new server. Here's an idea on how to build such a script:

SELECT 'BACKUP DATABASE '+QUOTENAME([name])+' TO  DISK = N''X:\yz\.....'''
FROM master.sys.databases
WHERE [name] NOT IN ('master', 'tempdb', 'model', 'msdb');


What is the difference between sys.dm_hadr_ tables and sys.availability_groups / sys.availability_replicas? Browsing around some sys tables, I see:

sys.dm_hadr_availability_replica_cluster_nodes.group_name = sys.availability_groups.name

and

sys.dm_hadr_availability_replica_cluster_nodes.replica_server_name = sys.availability_replicas.replica_server_name

and

sys.dm_hadr_availability_replica_cluster_nodes.node_name = sys.availability_replicas.replica_server_name

and

sys.dm_hadr_database_replica_states.group_id = sys.availability_groups.group_id

Could someone please clarify for me whether the "hadr" tables are duplicating data or not? For example, sys.dm_hadr_availability_replica_nodes and sys.avilability_replicas sound like they're describing the same thing.

I guess the question is on how safe it is to join these tables given I can see data values equate - because after all, the same value (something like 'SQL01') could conceivably be used to describe different things.

Better yet - is there no entity-relationship diagram published by Microsoft for these sys tables? I can find old posts where people ask the same question about ERDs and there are answers regarding older versions of SQL Server, but all links to diagrams seem outdated and defunct.

It's not duplicating of the information, depending on the requirement we could utilize anyone them:

DMV: The prefix sys.dm_ represents DMV the under lying information from Dynamic Management views
Catalog View: No prefix sys. represents catalog views. For more details..


Clustered vs Nonclustered Index My database currently has a primary Key/Clustered index on the ID column for each table.  However, the application that connects to the database is always looking at views defined as:

SELECT * FROM Table WHERE DeletedDate IS NULL

Would it be best to add the DeletedDate to the clustered index or is it better practice to have a nonclustered index with the DeletedDate as an include?

Since we are talking about the clustered index, just because you defined the CI key column as ID, you still have the DeletedDate data in the leaf data pages of the index.  That's the nature of the clustered index:  It is the table data.

Because you are typically having queries that look like:

select *
from YourTable
where DeletedDate is null;

You will likely benefit from a filtered index.

create nonclustered index IX_YourFilteredNci
on YourTable(<Key Columns Here>)
where DeletedDate is not null;
go

I didn't explicitly put the key columns here (and nonkey columns through the use of the INCLUDE clause) because you didn't publish the DDL of your table.

As in my comment above to your question, the choice of key columns (not just columns, but also the order of the columns) will largely depend on your workload and the typical queries that would be using this index.

If you are looking to cover your query(ies), then you would need to ensure that the index satisfies all of the data required of the query(ies).  Not to mention, if you have other WHERE clauses (besides your NULL check on DeletedDate) or joins to consider, then the order of your key columns can be the deciding factor between a scan or a seek.  And even though it is filtered, and depending on how much data you have in the index, the penalty could be considerable.


Configuration.ini file feature codes I have searched but not been able to find a list of the codes and descriptions available when using the configuration.ini file.

Is anyone able to link me some information?

You may be able to use the command line options documentation to review your different configuration options:

https://msdn.microsoft.com/en-us/library/ms144259.aspx

Whether they all translate exactly over to what's in the configuration file (https://msdn.microsoft.com/en-us/library/dd239405.aspx)?  That I couldn't answer.


Find max content length of column of particular table from entire SQL server I have a 3 SQL server: Server1,Server2 and Server3.

Each search has approx 50-60 databases.

In a particular database there is a table name table1 and it has a column named column1 with datatype varchar(max).

Now I want to change it's datatype to Nvarchar(500) but I can't as I dont know how much data consist in that column, If I blindly update it's datatype then it may happens that some of the data may be lossed.

So what I want is,I want to prepare a query to server level which gives me max content length of that column from entire server So I can get an idea about what is the max content length of that column.

So Based on that I can get max length and according to that max length,I can planned to alter that column.

I will be very grateful to you guys if someone can help me to prepare this query.

To do it for each database on a server you could do this:

To do it on the 3 servers, either run it on each, or create a central management server.

declare @cmd nvarchar(max)

declare @dbname nvarchar(500)
declare c cursor for select name from sys.databases where database_id > 4 and state_desc = 'ONLINE'
open c
fetch next from c into @dbname
while @@FETCH_STATUS = 0

begin
set @cmd ='
use '+@dbname+'
select max(len(column1 )) from table1
'
--print(@cmd)
exec(@cmd)
fetch next from c into @dbname
end
close c
deallocate c


Composite multicolumn index for geopoint range and numeric range query I am building an app where the server needs to select rows based on some criteria/filters. One of them is the location of the user and the radius at which the user want's to see posts and other filters such date range and filter for a value of another column. This is going to be for an ad-hoc event discovery app.
I have read about PostGIS, its geometry,geography types and I know there is a native point datatype. Based on this answer I understood that it is better to order from equality to range columns, even though I feel like geo point column should be the first.
Suppose the following few rows of a simplified events table (disregard the validity position data):
id  event_title                  event_position   event_type  is_public  start_date
    (varchar)                    (point lat/lon)  (smallint)  (boolean)  (timestamptz)
--  ---------------------------  ---------------  ---------   ---------  ----
 1  "John's Party"               (122,35)         0           0          2020-07-05
 2  "Revolution then Starbucks"  (123,30)         1           1          2020-07-06
 3  "Study for math exam"        (120,36)         2           1          2020-07-07
 4  "Party after exam"           (120,36)         1           1          2020-07-08
 5  "Hiking next to the city"    (95,40)          3           1          2020-07-09
 6  "Football match"             (-42,31)         4           1          2020-07-10

Imagine the table contains several thousand records at least, obviously not only 6.
So in this table a user would be able to query public events close to (122,34) by 100km (suppose first three rows fall into this area) and of event types 0, 1 or 2 falling between dates 2020-07-05 and 2020-07-07. The user would get the rows with ID 2 and 3.
This is the query I want to optimize with an appropriate index. My question is, how is it possible to create such an index? I thought about GiST or GIN index but not sure how these could help. Thanks!

@jjanes' answer on stackoverflow that I accepted

With the help of the btree_gist extension, you can include the
event_type and start_date columns into the GiST index along with the
event_position.  However, the event_type won't be very useful in the
index as long as the restriction clause is something like event_type in (0, 1, 2).  (But if the list only contains one element, it will be
rewritten into an equality, and in that case it can use that column in
the index efficiently.)  So using the other two columns would be my
starting point.  I would put the usually more selective one first.  If
you are going to leave past events in the table rather than clearing
them out, then the date is probably going to eventually be the more
selective one.
If the location condition is not very selective at all (e.g. most of
your events are in NYC, and most of your users are in NYC, and almost
everything is within 100km of almost everything else), then you might
want a different approach.  Just make a default BTREE index on
(event_type, start_date).  Unlike GiST, such a BTREE can effectively
use a condition like event_type in (0, 1, 2) along with AND start_date between x and y.
I don't think that there is a fundamental reason GiST can't use the
in-list effectively while BTREE can.  Maybe that will be fixed for
GiST in some future version.


MySQL:- Fetch records between two specific months and years I need a Query to fetch records between two specific months and years also, Presently I am fetching only current year records like this

SELECT material_status, COUNT(*) c
FROM purchase_order
WHERE YEAR(date_created) = YEAR(CURDATE()) AND material_status='Closed';

But I want the records between Current year "April Month" to Upcoming year April Month" means 2016-April to 2017-April .

Try this :

    SELECT material_status, COUNT(*) c
    FROM purchase_order
    WHERE date_created
         between str_to_date(CONCAT(YEAR(CURDATE()),'-04-01'),'%Y-%m-%d')
            AND DATE_ADD(str_to_date(concat(YEAR(CURDATE()),'-04-01'),'%Y-%m-%d'),INTERVAL 1 YEAR)
     AND material_status='Closed'
     GROUP BY 1;

But: BETWEEN is inclusive.  If date_created is really a DATE, then this includes 4/1/2017, but not the rest of 4/2017. Use last_day() function on to date, i.e.

last_day( str_to_date(CONCAT(YEAR(CURDATE()),'-04-01'),'%Y-%m-%d'))


Oracle 11g: "With Clause" very slow I have written a query utilizing Oracle's with clause. I have identified one particular with clause in a larger query to be really, really slow.  Here are three versions of the same query, they all have the same execution plan!

Version 1 (takes 30 seconds):

with abc as
(
  select count(distinct id) as my_count, some_column
  from  large_view v where some_column in (...constant values here...)
  group by some_column
) select my_count from abc;

Version 2 (takes 50ms):

  select count(distinct id) as my_count, some_column
  from  large_view v where some_column in (...constant values here...)
  group by some_column;

Version 3 (takes 50ms);

select my_count from (
  select count(distinct id) as my_count, some_column
  from  large_view v where some_column in (...constant values here...)
  group by some_column
);

I'd really like to use the with clause to keep the large query tidy. However, the difference in execution time is dramatic. Version 2 and 3 only take a few ms and Version 1 runs for about 30 seconds.

What is it that oracle does so that the statement with the with clause is that much slower and how can I get rid of it? Any explanation appreciated. I suspect that Version 1 is materializing every time but don't know for sure and how I can test it. Also, if that's the case, can I get rid of it (with a hint or a setting)? However, I could be wrong on this because with the materialize hint explicitly set the query runs even slower.

Oracle version:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit
Production PL/SQL Release 11.2.0.1.0 - Production
"CORE    11.2.0.1.0  Production"
TNS for Linux: Version 11.2.0.1.0 -
Production NLSRTL Version 11.2.0.1.0 - Production

Try the hint INLINE

with abc as
(
  select /*+ INLINE */ count(distinct id) as my_count, some_column
  from  large_view v where some_column in (...constant values here...)
  group by some_column
) select my_count from abc;


how much overhead an error in RDBMS has? I have a class in my project. On the class I need to do two inner select, to know is there any duplication or not, but I think if I get duplication error and then manage that is better than to select.
Error overhead Vs. Two inner select, which one?

If the unique field is properly indexed, a UNIQUE violation should be fairly quick.

Honestly though, I think doing a "two inner select" (I think I know what you mean here) is a better solution.  Even with trapping for an exception (at the application level), that's just not a very clean way to handle programatic logic.  Exception handling should be reserved for those rare occasions where you have an "exception to the rule."

And (without knowing which language you're coding in) if you're just trapping for a "SQLException", it's important to remember that those can be thrown for a variety of reasons. Your application might produce unexpected results if you incorrectly assume why an exception happened.

Bottom-line, I would say that the best approach is to do the SELECT to check for duplication.


Why does MySQL fail to use an index effectively when LIMIT offset is large? I do not understand when querying 5 rows from a table of 500,000 rows, the following query takes 0.00sec:

SELECT id, username FROM Users FORCE INDEX (PRIMARY) ORDER BY id LIMIT 20,5;

But querying the exact same rows in the reverse takes 2.50sec:

SELECT id, username FROM Users FORCE INDEX (PRIMARY) ORDER BY id DESC LIMIT 499975,5;

Furthermore, if I don't force index for the second query, MySQL will choose not to use it.

I note that there is a plan to introduce sort order for index, but that does not explain why MySQL cannot use the same index effectively when LIMIT offset is large since it knows the total row count.

Yes, the large offset is a problem. MySQL does early row lookups, which do not allow throwing data away as easy as one imagines. MySQL retrieves the whole records as it processes the database and each record may be of variable length. Throwing rows away afterwards becomes as cumbersome as throwing each single row away. It's not like "throw away x*y bytes".

There are tricks to force MySQL into late row lookup, which means process the index and do all operations concerning that, than retrieve the row. This offers the possibility to throw rows away much faster.

See

this stackoverflow thread for an easy answer
or this page for in-depth info.


Why should an application not use the sa account My first question ever, please be gentle. I understand that the sa account enables complete control over a SQL Server and all the databases, users, permissions etc.

I have an absolute belief that applications should not use the sa password without a perfected, business person focused reason why. Answers to This Question include a lot of my reasoning for an IT focused discussion

I am being forced into accepting a new service management system that WILL NOT work unless it uses the sa password. I never had time to work out why when setting up an evaluation but the server team tried to install it to use a fixed role I had set up incorporating db_creater and other permissions I thought it would require. which failed. I then let the server team install with the sa account but run under an account in the dbo role for its database but that failed too. Grumpily I tried to get it to run with an account in the sysadmin role but even that failed and not with useful error messages that enabled me to work out what was going on without spending more time than I had available. It will only work with the sa account and the password stored in clear text in the config file.

When I queried this and the server team talked to the vendor they got the worrying answer of 'What's the problem with that?' and then 'well we can look at scrambling the password' scrambling ffs

I know that there are ways and means to restrict access to the file but it is just another weakness in the security in my opinion

Anyway, My question is, could someone point me at some documentation that I can use to explain to the business the reason why this is a bad thing and should be a big no no. I work in a field that means that I need to take security seriously and have been struggling to make the business understand and ultimately may be out-ranked anyway but I need to try.

It depends on your business, but the main thing in most cases is to make sure it is not seen as an IT issue. It is a security issue and while the two overlap massively business peopel are more likely to listen if you say "security" than if you are just "moaning about general IT stuff".

Do you work with any clients that have security requirements? That is a good place to start. If we ran an app with sa level access, or just app that didn't properly secure its credentials even if not using priveleged access (we strongly prefer Windows Integrated rather than stored user/pass where possible), and we were subject to a security audit, that audit would fail and we would risk losing customers and/or having to give money back as to our groups's clients (banking organisations in the case of the product I work on mostly, other parts of the group deal with the police and health authorities and so forth) security is part of our offering being fit for purpose. Business people will understand the severity of that potential threat even if they usually pay no more than lip service to IT recommendations.

Even ignoring client imposed requirements, if you strive to meet various industry standard security standards then again this sort of application authentication is going to fail you in the face of an audit as it is so far from best practise as to be something generally considered to be on the "simply shouldn't be done" list. Make clear to your business descision makers that security is an important part of the application, and the fact that this vendor does not seem to be aware of (or at least appropriately concerned about) makes you have doubts about what else they might not capable of dealing with: knowing about DB security best practise is (well, should be) part of their job and imlpementing it is not difficult.

Also point out that you (the purchaser) should be dictating reasonable security requirements to the vendor, not the other way around. It is your data, so they are not qualified to state what is considered secure enough.


How to upgrade Mysql 5.5 to 5.6 without data loss on aws-ec2? MySQL server (5.5) is running on aws-ec2 instance with following linux details
NAME="Amazon Linux AMI"
VERSION="2015.09"
ID="amzn"
ID_LIKE="rhel fedora"
VERSION_ID="2015.09"
PRETTY_NAME="Amazon Linux AMI 2015.09"
ANSI_COLOR="0;33"
CPE_NAME="cpe:/o:amazon:linux:2015.09:ga"
HOME_URL="http://aws.amazon.com/amazon-linux-ami/"

Since Django 2.2 doesn't support Mysql 5.5
https://docs.djangoproject.com/en/2.2/ref/databases/#mysql-notes
. I want to upgrade MSQL to 5.6 .

We don't have backup server and we don't want new server  .
Creating sql dump , removing MySQL 5.5 then installing MySQL 5.6 and importing dump will it work  ? Since server has 100 GB of data importing the dump will be a long process and we will have considerable amount of downtime .There can be some issue with data while importing dump in 5.6 .

Is there a way to upgrade MySQL without data loss and less downtime ?

You don't need to dump and import the databases, a copy of the folder /var/lib/mysql should be ok for backup.
I suggest you upgrade to MySQL 5.7 as its the currently supported release and has better performance,
here are the steps, I implemented it many times with no issues
1. # service mysql stop

backup your databases before the upgrade
$ cp /var/lib/mysql /var/lib/mysql.original

Download the MySQL 5.7 RPM
$ wget http://repo.mysql.com/mysql57-community-release-el7.rpm -P /tmp/

Remove the MySQL-Community RPM that contains MySQL 5.5
$ yum remove mysql-community-release

Install the MySQL 5.7 RPM
$ rpm -ivh /tmp/mysql57-community-release-el7.rpm

Update the MySQL Repository to 5.7
$ yum update mysql

Let MySQL know that it received an upgrade
service mysql start
mysql_upgrade


How can I remove info message and column lines from SQL query result file? For example, I have a query like below and I want to remove info/warning messages (like 1 rows affected) and header's lines (like -------------) rows from SQL output file. I can disable info/warning messages on SQLCMD with -m 1 -W parameters but I want to do this with T-SQL. How can I do that?

DECLARE @tab char(1) = CHAR(9)
    EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'sqldba',
    @from_address = 'DISPLAYNAME <hostname@domain.com>' ,
    @subject = 'SQL Instance Info' ,
    @recipients = 'to1@domain.com;to2@domain.com',
    @copy_recipients= 'cc1@domain.com;cc2@domain.com',
    @body = 'Results on attachment',
    @body_format = 'HTML',
    @query = N'
SET ANSI_WARNINGS OFF
SET NOCOUNT ON
    SELECT  SERVERPROPERTY(''ServerName'') as ''Instance_Name'',
    exc.local_net_address as ''IP_Address'',  exc.local_tcp_port as ''Port''
    FROM sys.dm_exec_connections exc
    WHERE  exc.session_id = @@SPID
    ',
    @attach_query_result_as_file = 1,
    @query_attachment_filename = 'InstanceInfo.csv',
    @query_result_separator=@tab,
    @query_result_width =32767,
    @query_result_no_padding=1,
    @exclude_query_output=1,
    @query_result_header=1

Results seems like below:

Instance_Name   IP_Address  Port
-------------   ----------  ----
HOSTNAME    123.456.789.0   1433
(1 rows affected)

It should be:

Instance_Name   IP_Address  Port
HOSTNAME    123.456.789.0   1433

I played around with your example and was able to hardcode the expected column names and used UNION ALL against the actual result set.  I did have to convert the port column to varchar(10) to get the UNION ALL to work.  I then added the @query_result_header = 0 parameter to eliminate the query header.  I never did get the 'Rows Affected' message in my example because I was using the SET NOCOUNT like you did.

DECLARE @tab CHAR(1) = CHAR(9)

EXEC msdb.dbo.sp_send_dbmail @profile_name = 'SqlServerEmailProfile'
    ,@recipients = '<recipients>'
    ,@subject = 'query'
    ,@body_format = 'TEXT'
    ,@query = N'
SET ANSI_WARNINGS OFF
SET NOCOUNT ON
    SELECT ''Instance_Name'' as Instance_Name, ''IP_Address'' as IP_Address, ''Port'' as Port
    UNION ALL
    SELECT  SERVERPROPERTY(''ServerName'') as ''Instance_Name'',
    exc.local_net_address as ''IP_Address'',  convert(varchar(10),exc.local_tcp_port) as ''Port''
    FROM sys.dm_exec_connections exc
    --WHERE  exc.session_id = @@SPID
    '
    ,@attach_query_result_as_file = 1
    ,@query_attachment_filename = 'InstanceInfo.txt'
    ,@query_result_separator = @tab
    ,@query_result_width = 32767
    ,@query_result_no_padding = 1
    ,@exclude_query_output = 1
    ,@query_result_header = 0


Mysql date_add returns wrong row in python, right row in phpmyadmin I am trying to get the first and last record from a month.  In phpmyadmin I ge the correct result.  In python I don't.  In fact python returns the same row for both of these queries.  First number is the table primary key for the rows.

SELECT *
FROM mcs
WHERE time_stamp < DATE_ADD( DATE_ADD( LAST_DAY( NOW( ) ) ,
                                       INTERVAL 25 HOUR ) ,
                             INTERVAL -2 MONTH)
  AND wot_id = 1010965979
LIMIT 1

838831, '1010965979', datetime.datetime(2015, 3, 6, 22, 0), Decimal('444.88'), Decimal('0.00')

SELECT *
FROM mcs
WHERE time_stamp < DATE_ADD( DATE_ADD( LAST_DAY( NOW( ) ) ,
                                       INTERVAL 24 HOUR ) ,
                             INTERVAL -1 MONTH)
  AND wot_id = 1010965979
LIMIT 1

838831, '1010965979', datetime.datetime(2015, 3, 6, 22, 0), Decimal('444.88'), Decimal('0.00')

phpmyadmin shows this row for the first query,

839037 1010965979 2015-04-01 00:00:00 437.36 0.00

and this for the second:

953868 1010965979 2015-04-30 23:00:00 479.67 0.00

using MySQLdb and python 3

What is wrong?  is this a bug in MySQLdb?  I would not think so as it just passing a SQL string to mysql

First, a crucial mistake of the queries:

LIMIT without ORDER BY does not guarantee consistent results.
If you get correct results using LIMIT without ORDER BY, it's only by accident.

The results you got from running the queries in PHPMyAdmin was only accidental. There is no guarantee that you'll get the same results if you run the same queries tomorrow.

Adding ORDER BY (either ASC or DESC) will solve the issue.

There is also another problem with the 1st query. Adding 25 hours instead of 24 might be a nice hack - if it worked. It might work with the current data but it will stop, if there are ever rows that are not limited to full hours or there are no rows for the first hour of a month. You need to use 24 hours (or 1 day) and timestamp >= ... and ORDER BY .. ASC to get the first row from a period.

Queries rewritten (I used date arithmetic instead of your DATE_ADD(), this is entirely personal preference, the DATE_ADD() is fine in your queries):

-- First row in previous month (or later)
SELECT *
FROM mcs
WHERE time_stamp >= LAST_DAY(NOW()) + INTERVAL 1 DAY + INTERVAL -2 MONTH
  AND wot_id = 1010965979
ORDER BY time_stamp ASC
LIMIT 1 ;

-- Last row in previous month (or earlier)
SELECT *
FROM mcs
WHERE time_stamp <  LAST_DAY(NOW()) + INTERVAL 1 DAY + INTERVAL -1 MONTH
  AND wot_id = 1010965979
ORDER BY time_stamp DESC
LIMIT 1 ;


How to create a cascading query finding parent/child rows from table I hoping someone can help with what seems like a simple query, but seems to be harder than I anticipated.
I'm looking to find the parent rows for the child row based on the corresponding ID.

TABLE1
------------------------------
ID  LEVEL   VALUE   PARENTID
------------------------------
1   COUNTRY UK      NULL
2   COUNTRY FRA     NULL
3   CITY    LONDON  1
4   CITY    PARIS   2
5   RIVER   SEINE   4
6   RIVER   THAMES  3

.

SELECT VALUE FROM TABLE1 WHERE VALUE = 'THAMES'
...ID = PARENTID
...ETC
;

So for example with the above table I can pass in 'THAMES' and it will find the parent and parent's parent and so on, until it gets to a NULL value. for example:

THAMES
LONDON
UK

Thanks in advance.

with table1 as
(
  select 1 as id, 'COUNTRY' as lvl, 'UK' as value, null as parentid from dual union all
  select 2 as id, 'COUNTRY' as lvl, 'FRA' as value, null as parentid from dual union all
  select 3 as id, 'CITY' as lvl, 'LONDON' as value, 1 as parentid from dual union all
  select 4 as id, 'CITY' as lvl, 'PARIS' as value, 2 as parentid from dual union all
  select 5 as id, 'RIVER' as lvl, 'SEINE' as value, 4 as parentid from dual union all
  select 6 as id, 'RIVER' as lvl, 'THAMES' as value, 3 as parentid from dual
)
select value from table1
connect by id = prior parentid
start with value = 'THAMES';

VALUE
------
THAMES
LONDON
UK


Azure SQL very slow INSERT in stored procedure I have the following stored procedure, inserting new rows in a join table:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[InsertNewRows]
    @PropertyId int,
    @ItemGroupId int,
    @Discriminator nvarchar(max)
AS
BEGIN
    SET NOCOUNT ON;

    INSERT INTO ItemProperty (PropertyId, Discriminator, ItemId)
    SELECT @PropertyId, @Discriminator, Id FROM Item WHERE ItemTypeId IN
        (SELECT Id From ItemType WHERE ItemGroupId = @ItemGroupId)
END;

In this case, items belong to a type which in turn belongs to a group. I want to create the rows for all items in a group.

I execute the stored procedure for test purposes using:

BEGIN TRANSACTION

EXEC InsertNewRows 1, 10, 'Discriminator';
EXEC InsertNewRows 2, 10, 'Discriminator';
EXEC InsertNewRows 3, 10, 'Discriminator';
EXEC InsertNewRows 4, 10, 'Discriminator';
EXEC InsertNewRows 5, 10, 'Discriminator';
EXEC InsertNewRows 6, 10, 'Discriminator';
EXEC InsertNewRows 7, 10, 'Discriminator';
EXEC InsertNewRows 8, 10, 'Discriminator';
EXEC InsertNewRows 9, 10, 'Discriminator';
EXEC InsertNewRows 10, 10, 'Discriminator';

COMMIT

The group used for testing contains around 80,000 items.

On my local SQL Server 2014 Developer Edition, the execution takes around 10 seconds to perform. When i try to run it on a S2 instance of Azure SQL it takes around 1 minute per row so in total over 10 minutes. I even tried scaling up to a P15 for testing, and it performs worse (twice the time) than my local SQL Server, running on a regular laptop. Disabling the indices for ItemId and PropertyId before executing roughly halves the speed both locally and in Azure, but the relative performance is the same.

I am aware of the fact that Azure SQL is generally quite slow on large INSERTs, but it seems like at least a P15 instance should outperform a local SQL Server 2014 Developer Edition. Is there anything i can do to tweak my stored procedure or database setup to make it work faster in Azure?

EDIT: Here is the execution plan for Azure:

... and local:

This probably will not help but could you give it a try

INSERT INTO ItemProperty (PropertyId, Discriminator, ItemId)
SELECT @PropertyId, @Discriminator, Id
  FROM Item
  JOIN ItemType
    ON ItemType.ID = Item.ItemTypeId
   AND ItemType.ItemGroupId = @ItemGroupId


cannot convert yyyymmdd to yyyy-mm-dd - arithmetic overflow error I am trying to do something that is very simple and am stumped why it is not working.

I have a column (sysjobschedules.next_run_date from msdb) that is a varchar formatted like yyyymmdd.  I want to convert that to a datetime.  I use the rather straight-forward command CONVERT(datetime,next_run_date,112) but I keep getting the error Arithmetic overflow error converting expression to data type datetime.

The three distinct values in this column are 20120802, 20120803, and 20120806.  Using that same function above, I was able to successfully convert all three strings.

I feel like I must be missing something obvious...?

The column is actually stored as an int, and it means something different in datetime, so you'll need one extra step in your CONVERT:

CONVERT(DATETIME, CONVERT(CHAR(8), next_run_date), 112);

To show why you can't do this directly to an int:

SELECT CONVERT(DATETIME, 0);
SELECT CONVERT(DATETIME, 1);

When it's an int, it's actually the number of days since 1900-01-01.


Contradiction detection I am trying to learn Table Partitioning. However, I struggling to understand why SQL Server behaving stage for a simple query.

Test dataset

   /* --------------------------------------------------
-- Create helper function GetNums by Itzik Ben-Gan
-- http://sqlmag.com/sql-server/virtual-auxiliary-table-numbers
-- GetNums is used to insert test data
-------------------------------------------------- */

-- Drop helper function if it already exists
IF OBJECT_ID('GetNums') IS NOT NULL
    DROP FUNCTION GetNums;
GO

-- Create helper function
CREATE FUNCTION GetNums(@n AS BIGINT) RETURNS TABLE AS RETURN
  WITH
  L0   AS(SELECT 1 AS c UNION ALL SELECT 1),
  L1   AS(SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B),
  L2   AS(SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B),
  L3   AS(SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B),
  L4   AS(SELECT 1 AS c FROM L3 AS A CROSS JOIN L3 AS B),
  L5   AS(SELECT 1 AS c FROM L4 AS A CROSS JOIN L4 AS B),
  Nums AS(SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS n FROM L5)
  SELECT TOP (@n) n FROM Nums ORDER BY n;
GO

/* ------------------------------------------------------------
-- Create example Partitioned Table (Heap)
-- The Partition Column is a DATE column
-- The Partition Function is RANGE RIGHT
-- The Partition Scheme maps all partitions to [PRIMARY]
------------------------------------------------------------ */

-- Drop objects if they already exist
IF EXISTS (SELECT * FROM sys.tables WHERE name = N'Sales')
    DROP TABLE Sales;
IF EXISTS (SELECT * FROM sys.partition_schemes WHERE name = N'psSales')
    DROP PARTITION SCHEME psSales;
IF EXISTS (SELECT * FROM sys.partition_functions WHERE name = N'pfSales')
    DROP PARTITION FUNCTION pfSales;

-- Create the Partition Function
CREATE PARTITION FUNCTION pfSales (DATE)
AS RANGE right FOR VALUES
('2013-01-01', '2014-01-01', '2015-01-01');

-- Create the Partition Scheme
CREATE PARTITION SCHEME psSales
AS PARTITION pfSales
ALL TO ([Primary]);

-- Create the Partitioned Table (Heap) on the Partition Scheme
CREATE TABLE Sales
(
    SalesDate DATE constraint ck_date check(SalesDate<'2016-01-01'),
    Quantity INT
) ON psSales(SalesDate);

-- Insert test data
INSERT INTO Sales(SalesDate, Quantity)
SELECT DATEADD(DAY,dates.n-1,'2012-01-01') AS SalesDate, qty.n AS Quantity
FROM GetNums(DATEDIFF(DD,'2012-01-01','2016-01-01')) dates
CROSS JOIN GetNums(1000) AS qty;

Qery

select count(1) from sales where SalesDate>'20160101'

Please see the execution plan below.

Execution Plan

Questions

Why SQL doing CONVERT_IMPLICIT
Why SQL is not doing the contradiction detection to avoid the table scan.

Many thanks

You're getting a trivial and simple parameterized plan for your simple query.

Add AND 1 = (SELECT 1); to the end of your query, and you'll get the constant scan/contradiction detection.

Using just AND 1 = 1 doesn't always work if the aim is to also avoid trivial plans. See Query Plans: Trivial Optimization vs Simple Parameterization by Erik Darling.

You should also get into the habit of being careful about data types. A string is not a date, and there is no special T-SQL syntax to specify a date literal. You can use CONVERT to be explicit, e.g. SalesDate > CONVERT(date, '20160101', 112).

Related reading: Why Doesn’t Partition Elimination Work? by Paul White.


SQL to get performance metrics shown in EM Express 12C Resources I would like to start recording some historical performance metrics on my Oracle database server.  I can use these metrics to evaluate growth of database resource requirements over time.  When it's time to upgrade or replace the server, I can use this data to choose the right sized server.
The important metrics for me to collect are CPU percentage used, memory usage broken out by process, and although it isn't shown in EM, disk latency metrics would also help.  Below is a screenshot:

We don't have any of the additional performance packs licensed, but I have to figure if EM can query it every minute then I should be able to get it too.  Can someone give an example of a query or queries that can provide this data?

STATSPACK comes out of the box with Oracle Database Standard Edition. It works by comparing two snapshots.

https://docs.oracle.com/cd/E13160_01/wli/docs10gr3/dbtuning/statsApdx.html

Install:

cd $ORACLE_HOME/rdbms/admin/
sqlplus "/ as sysdba" @spcreate.sql

Take a snapshot:

sqlplus perfstat/<password>
execute statspack.snap(i_snap_level=>7);

Generate a report:

cd $ORACLE_HOME/rdbms/admin/
sqlplus perfstat/<password> @spreport


Why does this simple transaction deadlock with MySQL? I'm encountering intermittent deadlocks in the following proc:

DELIMITER $$
CREATE PROCEDURE pr_set_user_password (IN user_id INT, IN algorithm INT, IN hash VARBINARY(256))
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL;
    END;

    START TRANSACTION;
        UPDATE `UserPassword` SET `Active` = 0 WHERE `UserId` = user_id;
        INSERT INTO `UserPassword` (`UserId`, `Active`, `Algorithm`, `Hash`) VALUES (user_id, 1, algorithm, hash);
COMMIT;
END $$
DELIMITER ;

This table has a composite index on UserId and Active (IX_UserPassword_UserId_Active).

Someone tried to run a load test that created many users in parallel, and they encountered the deadlock very consistently. Looking at the logs, I see that the deadlock occurs when this proc runs with adjacent user_id values.

I'm really looking for a real explanation of why this results in a deadlock. I've had various mixed and conflicting answers, none of which make sense, ranging from because one transaction has the lock and the other lock can't acquire it (even though there's only one lock in question) to that's just how MySQL works, deal with it.

See an example of the deadlock below:

------------------------
LATEST DETECTED DEADLOCK
------------------------
2016-08-26 00:21:52 2b30caecc700
*** (1) TRANSACTION:
TRANSACTION 71925393848, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 5 lock struct(s), heap size 1184, 3 row lock(s), undo log entries 1
MySQL thread id 4901, OS thread handle 0x2b30cc1d7700, query id 37117819 <server> update
INSERT INTO `UserPassword` (`UserId`, `Active`, `Algorithm`, `Hash`) VALUES ( NAME_CONST('user_id',72332427), 1,  NAME_CONST('algorithm',2),  NAME_CONST('hash',_binary'<data>' COLLATE 'binary'))
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 388 page no 117300 n bits 784 index `IX_UserPassword_UserId_Active` of table `users`.`UserPassword` trx id 71925393848 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 714 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 844fb48c; asc  O  ;;
 1: len 1; hex 81; asc  ;;
 2: len 4; hex 818106f9; asc     ;;

*** (2) TRANSACTION:
TRANSACTION 71925393846, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
6 lock struct(s), heap size 1184, 4 row lock(s), undo log entries 1
MySQL thread id 4909, OS thread handle 0x2b30caecc700, query id 37117815 <server> update
INSERT INTO `UserPassword` (`UserId`, `Active`, `Algorithm`, `Hash`) VALUES ( NAME_CONST('user_id',72332426), 1,  NAME_CONST('algorithm',2),  NAME_CONST('hash',_binary'<data>' COLLATE 'binary'))
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 388 page no 117300 n bits 784 index `IX_UserPassword_UserId_Active` of table `users`.`UserPassword` trx id 71925393846 lock_mode X locks gap before rec
Record lock, heap no 714 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 844fb48c; asc  O  ;;
 1: len 1; hex 81; asc  ;;
 2: len 4; hex 818106f9; asc     ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 388 page no 117300 n bits 784 index `IX_UserPassword_UserId_Active` of table `users`.`UserPassword` trx id 71925393846 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 714 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 844fb48c; asc  O  ;;
 1: len 1; hex 81; asc  ;;
 2: len 4; hex 818106f9; asc     ;;

*** WE ROLL BACK TRANSACTION (1)

I'd say it's an illustration on how gap locks work -http://dev.mysql.com/doc/refman/5.7/en/innodb-locking.html#innodb-gap-locks  .

Say you have adjacent user id , 1 and 2.  When procedure executed simultaneously from 2 different sessions, each of them put a gap lock on two index-records (with user_id values 1 and 2 - maybe 0 ,4,5 as well ,but let's assume just 2 for simplicity sake), and each of them has to wait for another one to release the lock to perform an insert.

  Gap locks in InnoDB are “purely inhibitive”, which means they only
  stop other transactions from inserting to the gap. They do not prevent
  different transactions from taking gap locks on the same gap. Thus, a
  gap X-lock has the same effect as a gap S-lock *."

To disable locks,

  Gap locking can be disabled explicitly. This occurs if you change the
  transaction isolation level to READ COMMITTED or enable the
  innodb_locks_unsafe_for_binlog system variable (which is now
  deprecated). Under these circumstances, gap locking is disabled for
  searches and index scans and is used only for foreign-key constraint
  checking and duplicate-key checking.

Using READ COMMITTED sounds like a reasonable solution to me, assuming it doesn't break application logic.


Selecting, filtering and aggregating in MongoDB if we have two dataset 1-First dataset contains population data of cities in the United States of America. Each record in the dataset has the following format:

{
     "city": "ACMAR",
     "loc": [
         -86.51557,
         33.584132
     ],
     "pop": 6055,
     "state": "AL",
     "_id": "35004"
}

Second dataset contains log data from a geo-locational service. Each record in the dataset has the following format:

{
    "_id": {
        "$oid": "5191f53b1b76a5666a8cbd64"
    },
    "status": 200,
    "requrest_time": 504,   // in milliseconds
    "type": "stationboard",
    "datetime": {
        "$date": "2014-02-19T07:03:32.000Z"
    }
}

how we can get all the states that have at least 2 cities with population > 1000000 (1M). And we need to  remember that city can be split into multiple records, and we should aggregate its population first. (zips dataset)
we need find a day (date) with the largest number of HTTP 500 errors (status=500).

how we can get all the states that have at least 2 cities with
  population > 1000000 (1M). And we need to remember that city can be
  split into multiple records, and we should aggregate its population
  first. (zips dataset)

I have zips collection database having 29353 records.

> db.zips.find().count()
29353
> db.zips.count()
29353

and the documents structure inside the collection is such as

> db.zips.find().pretty()
{
        "_id" : "01007",
        "city" : "BELCHERTOWN",
        "loc" : [
                -72.410953,
                42.275103
        ],
        "pop" : 10579,
        "state" : "MA"
}
{
        "_id" : "01002",
        "city" : "CUSHMAN",
        "loc" : [
                -72.51565,
                42.377017
        ],
        "pop" : 36963,
        "state" : "MA"
}
{
        "_id" : "01008",
        "city" : "BLANDFORD",
        "loc" : [
                -72.936114,
                42.182949
        ],
        "pop" : 1240,
        "state" : "MA"
}
{
        "_id" : "01011",
        "city" : "CHESTER",
        "loc" : [
                -72.988761,
                42.279421
        ],
        "pop" : 1688,
        "state" : "MA"
}
{
        "_id" : "01010",
        "city" : "BRIMFIELD",
        "loc" : [
                -72.188455,
                42.116543
        ],
        "pop" : 3706,
        "state" : "MA"
}

which is the same as mention documents structure by the OP.

{
     "city": "ACMAR",
     "loc": [
         -86.51557,
         33.584132
     ],
     "pop": 6055,
     "state": "AL",
     "_id": "35004"
}

Suppose that here i want to write an aggregation query with a single match phase that filters for zipcodes with greater than 100,000 people.

Here i will write down the simple query with $match (aggregation) and Operator $pop to filter the documents.
$match Filters the documents to pass only the documents that match the specified condition(s) to the next pipeline stage.

> db.zips.aggregate([{$match:{pop:{$gt:100000}}}])
{ "_id" : "10021", "city" : "NEW YORK", "loc" : [ -73.958805, 40.768476 ], "pop" : 106564, "state" : "NY" }
{ "_id" : "10025", "city" : "NEW YORK", "loc" : [ -73.968312, 40.797466 ], "pop" : 100027, "state" : "NY" }
{ "_id" : "11226", "city" : "BROOKLYN", "loc" : [ -73.956985, 40.646694 ], "pop" : 111396, "state" : "NY" }
{ "_id" : "60623", "city" : "CHICAGO", "loc" : [ -87.7157, 41.849015 ], "pop" : 112047, "state" : "IL" }
>

In the above example i have found out the 4 states and their respective cities . Where population is > 100000.

  we need find a day (date) with the largest number of HTTP 500 errors
  (status=500).

For spherical queries, use the 2dsphere index result.

As MongoDB documented Here The use of 2d index for spherical queries may lead to incorrect results, such as the use of the 2d index for spherical queries that wrap around the poles.

MongoDB geospatial queries on  GeoJSON objects calculate on a sphere; MongoDB uses the WGS84 reference system for geospatial queries on GeoJSON objects.


Can't seem to understand why STUFF isn't working I've got two tables that store questions and possible answers.
The questions table can have multiple possible answers to it.

I want to display the possible answers it can have per question..
For instance lets say I have two questions

1) Hows the weather?
2) Do you love MI?

And the other table I have possible answers

1) Good
1) Fair
2) Yes
2) No

My expecting result would be

1 Good, Fair
2 Yes, No

But instead I get:

1 Good, Fair, Yes, No
2 Yes, No, Good, Fair

Here's what I am doing...

SELECT x.SurveyQuestionID, PossibleAnswers = STUFF((
    SELECT ', ' + Answer FROM SurveyQuestionPossibleAnswer sqpa
    INNER JOIN SurveyQuestion sq
    ON sq.SurveyQuestionID = sqpa.SurveyQuestionID
    FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 2, '')
FROM SurveyQuestionPossibleAnswer AS x
GROUP BY x.SurveyQuestionID

I'm getting the right number of rows due to the group by, but the possible answers seems to be giving me every possible answer for each question.

The problem is that you are missing a way to tie the outer table to the data inside of the STUFF. You can do this by adding a WHERE clause to the subquery:

SELECT x.SurveyQuestionID,
  PossibleAnswers
    = STUFF((
              SELECT ', ' + Answer
              FROM SurveyQuestionPossibleAnswer sqpa
              INNER JOIN SurveyQuestion sq
                ON sq.SurveyQuestionID = sqpa.SurveyQuestionID
              WHERE x.SurveyQuestionID = sq.SurveyQuestionID
              FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 2, '')
FROM SurveyQuestionPossibleAnswer AS x
GROUP BY x.SurveyQuestionID;

See SQL Fiddle with Demo. Or to shorten the code, you could even rewrite the query to:

SELECT x.SurveyQuestionID,
  PossibleAnswers
    = STUFF((
              SELECT ', ' + Answer
              FROM SurveyQuestionPossibleAnswer sqpa
              WHERE x.SurveyQuestionID = sqpa.SurveyQuestionID
              FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 2, '')
FROM SurveyQuestion AS x
GROUP BY x.SurveyQuestionID;

See SQL Fiddle with Demo. Both will give you the same result.


Recover dropped mysql sys schema I dropped the sys schema while tinkering around mysql 8(current version). Is there a way to recover it. I am using ubuntu 20.04. I tried using the mysql documentation but I couldn't find anything relevant. Could someone with better experience help me with this? Thank you.

Go to the folder of the github
repo.

Download the sys_schema folder using
gitzip or any other available
extensions. I personally used gitzip. Needless to say you can
download the whole mysql-server repo but that would be inefficient
as you do not need everything and it has a heavy download size.

Extract it to a folder and cd into it

Log in to the mysql client
mysql -u root -p

Import the sys_57.sql file. Don't mind the name, it is compatible
with mysql 8. using
source ./sys_57.sql

There you go, you have your sys schema back. Thanks to danblack for giving me a cue.

P.S akina this is
computer, there is always a way


How to know if a table has a non-unique clustered index? Give a user table ‘MyTable’. How to know whether the table contains a non-unique clustered index by using SQL query?

Thanks

Create a simple table with a non-unique clustered index:

CREATE TABLE dbo.TestCluster
(
    TestClusterID INT NOT NULL
);

CREATE CLUSTERED INDEX CX_TestCluster --note there is no "UNIQUE" keyword here
ON dbo.TestCluster(TestClusterID);

This query shows if the given index is unique or not:

SELECT SchemaName = s.name
    , TableName = o.name
    , IndexName = i.name
    , IndexType = i.type_desc
    , IsUnique = i.is_unique
FROM sys.indexes i
    INNER JOIN sys.objects o ON i.object_id = o.object_id
    INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
WHERE s.name = 'dbo'
    AND o.name = 'TestCluster'
    AND i.type_desc = 'CLUSTERED';

It's always vitally important that you specify/show the schema, as I have done above, since it very easy that objects with the same name might exist in multiple schemas.


PLSQL Package - Catching Errors in procedure (Oracle) I have a problem with my package (and procedure) not catching exceptions.

I have the below code:

CREATE OR REPLACE PACKAGE process_orders
IS
    PROCEDURE add_order (
        order_num   NUMBER,
        cust_num    NUMBER,
        rec_date    DATE);
END process_orders;
/

CREATE OR REPLACE PACKAGE BODY process_orders
IS
    PROCEDURE add_order (
        order_num   NUMBER,
        cust_num    NUMBER,
        rec_date    DATE)
        IS
        status      VARCHAR2(50);
    BEGIN
        INSERT INTO orders VALUES (order_num, cust_num,
            NULL, rec_date, NULL);
    EXCEPTION
        WHEN OTHERS THEN
            status := SUBSTR(SQLERRM,1,50);
            INSERT INTO orders_errors VALUES (SYSDATE,
                order_num, status);
    END;
END process_orders;
/

If in sqlplus I execute: exec process_orders.add_order(2000,1/0,'25-DEC-91'); (notice the div by 0 error in the 2nd arg) I do not get anything inserted into the orders_errors table (and no errors except for the div by 0 error).

This is happening because the division by zero error is being raised before the procedure is even executed - It's happening when the arguments are actually passed in to the procedure.

You can verify this with a small proc that doesn't do anything:

create or replace procedure do_nothing(divbyzero number)
IS
BEGIN
  NULL;
END;
/

Test:

SQL> exec do_nothing(1/0);
BEGIN do_nothing(1/0); END;

*
ERROR at line 1:
ORA-01476: divisor is equal to zero
ORA-06512: at line 1

SQL>


Can't connect to Analysis Services or Reporting Services on localhost I'm able to connect to my SQL Server 2008R2 localhost Database Engine and Integration Services.

However, when I try to connect to Analysis Services, it fails with the message: "A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond."

I didn't get an error when I tried to connect to Reporting Services, but didn't get a response of any kind after 10 minutes and canceled the connection attempt.

What can I do to fix this?

I believe the original installation was bad. I uninstalled and re-installed and now everything is working as it should.


I am upgrading from 11.1.0.7.0 to 11.2.0.3 with R12.1.3 while pre upgrade/catupgrd.sql erroring out ORA-00600 While upgrading from 11.1.0.7.0 to 11.2.0.3 with R12.1.3, during running the pre upgrade script catupgrd.sql, it is erroring out

ORA-00600: internal error code, arguments: [qcisSetPlsqlCtx:tzi
 init], [], [], [], [], [], [], [], [], [], [], []

I applied patch 10272702, the patch got applied successfully but still the persist.

This problem is related to timezone stuff. Did you execute the pre-upgrade (utlu112i.sql) scripts? And did you follow the instructions?


Different CREATE_TIME for tables in Master and Slave We have found that the value of CREATE_TIME column in information_schema.tables for many tables was different in MASTER and SLAVE. Upon going through the logs, it was found that the CREATE_TIME value in the SLAVE was that of the scheduled weekly OPTIMIZE routine. Hence it can be inferred that the OPTIMIZE TABLE statements are causing the TABLES to be recreated in SLAVE. However, the scheduled job runs on MASTER only. But the CREATE_TIME in MASTER is not getting affected.

Why is the OPTIMIZE TABLE command behaving differently on MASTER and SLAVE? And if the table is being reconstructed at SLAVE, is the table becoming temporarily unavailable during that period?

The variable innodb_optimize_fulltext_only was ON in master but OFF in slave. As a result, the OPTIMIZE TABLE commands were being skipped in Master server.


Best way to get current year minus 5 years I'm actually have the next query that I use to obtain the current year minus 5 years in january in this format 'YYYYMM'.

select TO_CHAR(ADD_MONTHS(TRUNC(SYSDATE,'YEAR'),12*5*-1),'YYYYMM') from dual;

I want to know if this way is the best way to do this.

There are different ways to achieve the goal.
SQL> select to_char(trunc(add_months(sysdate,-12*5),'YEAR'),'YYYYMM') from dual;

OR
SQL>  select to_char(trunc(SYSDATE - interval '5' year,'YEAR'),'YYYYMM') from dual;

Regarding the second one, what happens if the SYSDATE or the current date supplied happens to be a leap day?
SQL> select to_char(trunc(to_Date('2016-02-29','YYYY-MM-DD') - interval '5' year,'YEAR'),'YYYYMM')
    from dual;
select to_char(trunc(to_Date('2016-02-29','YYYY-MM-DD') - interval '5' year,'YEAR'),'YYYYMM')
from dual
                                                        *
ERROR at line 1:
ORA-01839: date not valid for month specified

What happens if we use the first method?
SQL> select to_char(trunc(add_months(to_Date('2016-02-29','YYYY-MM-DD'),-12*5),'YEAR'),'YYYYMM')
     from dual;

TO_CHA
------
201101


different login credentials based on user I run a PHP symfony-framework web app for my company and it uses a single database login for all connections, whether the user is an admin or a client.

I know that higher security systems often use different logins for different levels of users.

Whether or not a use a framework, is this a security feature I should make use of?

Does it matter if the application is web-based or if it's intranet based?

To clarify:
Should I concern myself with creating a plugin for symfony to implement multiple db logins and should I do this for any other apps I create that are not based on a framework?

In case you're wondering, I'm using PHP5 and MySQL.

You typically have to weight the cost of doing so vs. the benefits ... but benefit in risk management is difficult to quantify.

Basically, it comes down to what the cost of an exploit would be, and what the likihood of it happening are.

So, having to restore from backup because someone managed to drop a table which creates a denial of service, and being down for a day has a cost to the company in terms of what profit they'd have made in that given time, but there's also an issue of reputation loss (ie, customers/users who stop doing business with you, or potential users who are less likely to do busines in the future) ... but we have to balance this by the likehood of someone successfully attacking the site and causing this.

If you're not storing credit cards, and you're not a big target (the type of site people would brag about taking out), you're less likely to be hacked ... although, if you're running commonly distributed software, you still risk attacks by script kiddies who are just looking for people running software with a known exploit.

...

What our security folks don't seem to understand is that it's a balancing act -- some changes for security will create a burden on your users.  And sometimes, the security itself will cause outages (eg, one of our external partners moves IP ranges ... but the new holes in the firewall weren't made, and due to a "network hold" we can't get any changes made for over a week) or just performance degredation.

Sometimes it's just that it takes longer to code, or more headaches to maintain, etc.

But it's something you have to answer for yourself -- is the cost worth the benefit of having made the change?  (and sometimes, if the cost is just in man-power, was there an opportunity cost; ie, could you have been doing something else that would derive even more benefit with your time?)