How to get hierarchical data from two tables in oracle? I have two tables table1 and table2, I need hierarchical output by joining of both of the tables.

Table1 contains three categories CAT1, CAT2 and CAT3 with one Foreign key i.e. F_ID which is a primary key of another table table2 which contains one more column i.e.VAL.

TABLE 1
-----------------
CAT1        CAT2        CAT3        F_ID
A           a           aa          1
A           a           ab          2
A           b           ba          3
A           b           bb          4
B           c           ca          5
B           c           cb          6
B           d           da          7

TABLE 2
-------------------
F_ID    VAL
1       4
2       6
3       4
4       1
5       6
6       6
7       9

Now i need the data in the below format, where every category and sub-category contains the total sum of VAL.

Need Data in below Format from the above table
----------------------------------------------
A       -       -       15
A       a       -       10
A       a       aa      4
A       a       ab      6
A       b       -       5
A       b       ba      4
A       b       bb      1
B       -       -       21
B       c       -       12
B       c       ca      6
B       c       cb      6
B       d       -       9
B       da      da      9

Please help me.

Use grouping sets. You can try it here.

select cat1, cat2, cat3, sum(val) as val
  from t1 join t2 on t1.f_id = t2.f_id
group by grouping sets ((cat1),(cat1, cat2),(cat1, cat2, cat3))
order by cat1, cat2 nulls first, cat3 nulls first

OUTPUT

cat1 cat2 cat3 val
A           15
A   a       10
A   a   aa  4
A   a   ab  6
A   b       5
A   b   ba  4
A   b   bb  1
B           21
B   c       12
B   c   ca  6
B   c   cb  6
B   d       9
B   d   da  9

If you prefer underscore than null use NVL

select cat1, nvl(cat2,'_') as cat2, nvl(cat3, '_') as cat3, sum(val)
  from t1 join t2 on t1.f_id = t2.f_id
group by grouping sets ((cat1),(cat1, cat2),(cat1, cat2, cat3))
order by cat1, cat2 nulls first, cat3 nulls first


Correct 'lines terminated by' for query I am trying to query two tables of data and send them to a csv file for upload to a website. However i am having trouble getting the formatting to play ball. I want headings to separate into columns and then the relavant data to line up with the columns in the body of the data.

I am currently closest to achieving my aims with -

FIELDS TERMINATED BY ',' ENCLOSED BY '' LINES TERMINATED BY ''

Which returns -

However you can see my issue. Any part numbers that have a '\' render into the wrong columns and the data does not line up with the heading.

Now please bear in mind, the tables are always updating every second, and i have no control over how the data is uploaded. I cannot remove the '\' from the part#'s.
I need a workaround that ignores the '\'.

This is quite hard to explain so...

TLDR:- I need the data to all line up like the majority of the rows in the picture, what are the 'Fields/lines terminated by' parameters?

See the mydata.csv file at bottom of the answer.

I created a table xy

CREATE TABLE `xy`
(
  `fred` int(11) DEFAULT NULL,
  `mary` int(11) DEFAULT NULL,
  `billy` varchar(50) DEFAULT NULL
);

From the documentation here, I tried this

LOAD DATA INFILE 'mydata.txt' INTO TABLE tbl_name
  FIELDS TERMINATED BY ',' ENCLOSED BY '"'
  LINES TERMINATED BY '\r\n'

But that failed (only one line imported). I changed \r\n to \n (Unix), that was better (all lines imported, but no \ in the VARCHAR field - they were simply deleted).

Further Googling led me here

So I tried this (from the answer)

LOAD DATA INFILE 'mydata.csv' INTO TABLE xy
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
ESCAPED BY ''
LINES TERMINATED BY '\n';

(note ESCAPED BY '' - i.e. nothing - the \ is the default escape character).

And that worked (sample of four lines in table xy below)

|    2 |    3 | asf\dsadfs |
|    2 |    3 | asfdsadfs  |
|    2 |    3 | asfdsadfs  |
|    2 |    3 | asf\dsadfs |

My sample mydata.csv file

2,3,asfdsadfs
2,3,asfdsadfs
2,3,asfdsadfs
2,3,asfdsadfs
2,3,asfdsadfs
2,3,asfdsadfs
2,3,asfdsadfs
2,3,asf\dsadfs
2,3,asfdsadfs
2,3,asfdsadfs
2,3,asf\dsadfs
2,3,asfdsadfs
2,3,asfdsadfs
2,3,a\sfdsadfs
2,3,asfdsadfs
2,3,asfdsadfs


Pessimistic locking worst case We are using Java EE and are making an application where in the worst case, a lot of message queue messages will come form the same User.

Therefore, we are looking on Pessimistic, SELECT FOR UPDATE style locking. Which in theory and first tests solves our problems.

Yet, we are afraid of deadlocks. Not the classical ones: User X locks A, User Y locks But more of scenarios like: system crash, network problems,... and so on. Database system locking up, for no know reason. We will use modern databases like: Oracle, MS SQL and PostgreSQL.

What we would like to know is pessimistic locking used in production and what practical problems to expect?

Thanks in advance!

Pessimistic locking is a good thing used well.

The big thing though is that your issues are all going to be concurrency issues and things which interfere with concurrency will interfere with performance.  For example if your network switch starts dropping 10% of packets randomly, this will make a network connection take a lot longer, and your locks are going to suffer.  These will not be deadlocks per se and will only make a problem that would be painful under the best of scenarios a good deal worse.

System crashes should not cause deadlocks.  If it is the client, the network connection will time out so you may have a sudden issue with locks that goes away right away.  If the client is middleware however, this is likely to be a non-issue because all the other connections will be broken too.  If it is the server, the transactions will abort.

The big issues are developer short-sightedness.  For example, suppose you write a stored procedure that calls a web service and the network connections there are timing out.  Or suppose you make a connection back to the client computer to request input on whether to continue a stored procedure and the user never responds.  In general when you pause a transaction to wait for something outside the database, bad things happen.


SQL Server Version - are hotfixes for previous versions implied? tl;dr: If I identify an SQL Server as having build X, and find out that X implies hotfix Y, does it also imply that all hotfixes listed for versions below X are installed? Or that only the hotfix for build X is installed?

I found the following list of Microsoft SQL Server version numbers and their meaning in terms of service packs, hotfixes, product versions, etc.:

SQL Server Version Database @ sqlsecurity.com

My question is best illustrated with an example, an excerpt from that list:

2005 RTM+Q915793        9.00.1502  <-- interested in this
2005 RTM+Q910416        9.00.1500
2005 RTM+Q932557        9.00.1406

In the above list, if I find out that the SQL Server I am connected to is version 1502, does this imply that:

only hot-fix Q915793 is installed?
or that all of hot-fixes Q915793, Q910416 and Q932557 are installed?

ie. does the build imply that all previous hotfixes are installed?

Follow-up bonus question: Is this a fixed pattern for all the versions, or are there exceptions to the rule?

All hotfixes are cumulative unless otherwise stated.

Random pick, http://support.microsoft.com/?Kbid=971409

  Because the builds are cumulative, each new fix release contains all the hotfixes and all the security fixes that were included with the previous SQL Server 2005 fix release. Microsoft recommends that you consider applying the most recent fix release that contains this hotfix.

The "otherwise stated" is like these examples where you have some dependencies (sorry. bit old, for SQL Server 2000 and SP4). http://support.microsoft.com/?Kbid=941203 and http://support.microsoft.com/kb/959420


Convert column value stripping html tags into sql view with rows and columns I have a table named data with a column pid & display_data.
Pid is the serial number of all rows.
The value of display_data column is as below :
For each row display_Data has values in the format =>
For PID= 1:
The labels are constant for all rows which would become column headers. Values are changing with respect to labels.
There are total 400 such rows hence, the query needs to be dynamic.
I want to parse this column value,strip html tags and into a new view split it using sql query(Regexp_Replace maybe) such that:
All label values become columns, i.e.
The actual data is much more with many labels and values however this is just sample to get help.

TL;DR Answer
Convert HTML to XML and parse with XMLTABLE()
Long Answer
HTML and XML are both descendants of SGML.
From the sample data you have posted, it looks like you just need to remove the
 tags and wrap desc_data in a root element (eg <row>) for the conversion into XML.
Once in XML format, you can parse the data with XMLTABLE() to give you:

instance of  ( via for ordinality, column name RN )
value of @label
value of

This is done within the CTE named xml_data in my example.
Assuming value always follows label, you can convert the instance of  ( RN ) to have the same value for each pair. (this is also done in xml_data)
From there, you can PIVOT the data into a table that has:

your row identifier (RID)
value for label (LABEL is a key word so I used LABEL_TXT )
value for value (VALUE is a key word so I used VALUE_TXT )

This is done within the CTE labeled key_val in my example.
For the final query, the final columns names MUST BE KNOWN at compile time.  Once you know the columns, you can just do another PIVOT for a proper table.
Notes
As you can see, HTML is not a good data format to use for parsing data and/or using the data within a computer (eg a database).
Unless desc_data is used for display, I recommend that the data is stored in XML or JSON format.  (I'm assuming you can change the source)
Once data is in a parseable format, PL/SQL is no longer needed.
Example Code
with data as (
    select q'[Project Name
 Digital Dept Name
 FinanceLevel
 Level 5]'
        as txt
        ,1 AS RID
    from dual
),xml_data as (
select RID, a.rn - mod(a.rn+1,2) rn,a.label_txt, a.val
from data d, xmltable( '/row/span'
    passing xmltype( '<row>' || replace(d.txt,'
','') || '</row>' )
    columns
        rn for ordinality,
        label_txt varchar2(20)  path '/span/@class',
        val       varchar2(20)  path '/span'
    ) a
), key_val as (
    select rid, label_txt, value_txt
    from xml_data
        pivot (
            min( val )
            for label_txt in ( 'label' as LABEL_TXT, 'value' as VALUE_TXT)
        )
)
select RID, PNAME, LVL, DNAME
from key_val
    pivot (
        min( VALUE_TXT )
        for LABEL_TXT in ('Project Name' as PNAME, 'Level' as LVL, 'Dept Name' as DNAME )
)


oracle performance on date range query I am trying to count the number of orders passed by a certain organization over a certain time range. But I found that the below query (time range of 2 days) is much much slower that doing two seperate queries each for one day.

 SELECT COUNT(*)  FROM ORDER_HISTORY
 WHERE organization = 'BA' AND  TIMESTAMP > = TO_DATE('2016-01-05', 'YYYY-MM-DD')
 AND TIMESTAMP<= TO_DATE('2016-01-05', 'YYYY-MM-DD')+2;

I've an index on the column timestamps and another index on column organization

Here is the schema of my table. the column timestamp is of type DATE.

The execution plan of query over 2 days uses the index on organization:

The execution plan of query over 1 day uses the index on timestamp:

To have some stats:

   SELECT COUNT(*)  FROM ORDER_HISTORY
   WHERE ORGANIZATION = 'BA' ;

gives 2359847.

   SELECT COUNT(*)  FROM ORDER_HISTORY
   WHERE TIMESTAMP > = TO_DATE('2016-01-05', 'YYYY-MM-DD')
   AND TIMESTAMP<= TO_DATE('2016-01-05', 'YYYY-MM-DD')+1;

gives 9260. and same query over 2 days gives 16510.

Why could I get the kind of strange behaviour of the oracle DB engine?

The reason is that the cost-based optimizer has a plan in memory from a previous select, where the number of records for a given organization, namely 'LOL', was much smaller, some 14000.  When the plan is in memory, the CBO doesn't care about the parameter values anymore.  Use:

WHERE organization || '' = 'BA'


Change date precision values Exposition: I have an oracle database table with date format as 'mm/dd/yyyy' and an unique constraint that contains the date column. My issue is that I can't seem to find a method to change the internal date format, which I need to get down to the second (ie 'mm/dd/yyyy hh12:mi:ss').  I do NOT want to change the query of the output format, I want to change the stored value to be updated.

Question: Is it even possible to get the database precision down to the second? If so how, I assume its an update or alter command?

I have an oracle database table with date format as 'mm/dd/yyyy'

No you don't.. ;)

You have an Oracle database table with a date datatype.  Period.

Any type of "format" is for display only, and is easily modified.

  SQL> create table junk ( id number,  dd  date );

  Table created.

  SQL> insert into junk values ( 1, sysdate );

  1 row created.

  SQL> commit;

  Commit complete.

  SQL> select * from junk;

          ID DD
  ---------- ---------
           1 31-MAY-16

  SQL>
  SQL> alter session set nls_date_format='dd-mon-yyyy hh24:mi:ss'
    2  /

  Session altered.

  SQL> select * from junk;

          ID DD
  ---------- --------------------
           1 31-may-2016 11:17:44

  SQL>

An Oracle DATE datatype holds Year + Month + Day + Hours + Minutes + Seconds information.
It's up to you how to display/use it.

Note that an Oracle TIMESTAMP datatype holds milliseconds in addition to what a DATE holds.

It would be much easier to give you a more specific answer if you'd actually show your table definition, and your query and explain the problem you're having, and what you want to see.


How to speed up ORDER BY sorting when using GIN index in PostgreSQL? I have a table like this:

CREATE TABLE products (
  id serial PRIMARY KEY,
  category_ids integer[],
  published boolean NOT NULL,
  score integer NOT NULL,
  title varchar NOT NULL);

A product can belong to multiple categories. category_ids column holds a list of ids of all product's categories.

Typical query looks like this (always searching for single category):

SELECT * FROM products WHERE published
  AND category_ids @> ARRAY[23465]
ORDER BY score DESC, title
LIMIT 20 OFFSET 8000;

To speed it up I use the following index:

CREATE INDEX idx_test1 ON products
  USING GIN (category_ids gin__int_ops) WHERE published;

This one helps a lot unless there are too many products in one category. It quickly filters out products that belong to that category but then there is a sort operation that has to be done the hard way (without index).

A have installed btree_gin extension allowing me to build multi-column GIN index like this:

CREATE INDEX idx_test2 ON products USING GIN (
  category_ids gin__int_ops, score, title) WHERE published;

But Postgres does not want to use that for sorting. Even when I remove DESC specifier in the query.

Any alternative approaches to optimize the task are very welcome.

Additional information:

PostgreSQL 9.4, with intarray extension
total number of products currently is 260k but expected to grow significantly (upto 10M, this is multi-tenant e-commerce platform)
products per category 1..10000 (can grow up to 100k), average is below 100 but those categories with large number of products tend to attract many more requests

The following query plan was obtained from smaller test system (4680 products in selected category, 200k products total in the table):

Limit  (cost=948.99..948.99 rows=1 width=72) (actual time=82.330..82.341 rows=20 loops=1)
  ->  Sort  (cost=948.37..948.99 rows=245 width=72) (actual time=80.231..81.337 rows=4020 loops=1)
        Sort Key: score, title
        Sort Method: quicksort  Memory: 928kB
        ->  Bitmap Heap Scan on products  (cost=13.90..938.65 rows=245 width=72) (actual time=1.919..16.044 rows=4680 loops=1)
              Recheck Cond: ((category_ids @> '{292844}'::integer[]) AND published)
              Heap Blocks: exact=3441
              ->  Bitmap Index Scan on idx_test2  (cost=0.00..13.84 rows=245 width=0) (actual time=1.185..1.185 rows=4680 loops=1)
                    Index Cond: (category_ids @> '{292844}'::integer[])
Planning time: 0.202 ms
Execution time: 82.404 ms

Note #1: 82 ms might not look that scary but that is because sort buffer fits into memory. Once I select all columns from products table (SELECT * FROM ... and in real life there are about 60 columns) it goes to Sort Method: external merge  Disk: 5696kB doubling execution time. And that is only for 4680 products.

Action point #1 (comes from Note #1): In order to reduce memory footprint of sort operation and therefore speed it up a little it would be wise to fetch, sort and limit product ids first, then fetch full records:

SELECT * FROM products WHERE id IN (
  SELECT id FROM products WHERE published AND category_ids @> ARRAY[23465]
  ORDER BY score DESC, title LIMIT 20 OFFSET 8000
) ORDER BY score DESC, title;

This brings us back to Sort Method: quicksort  Memory: 903kB and ~80 ms for 4680 products. Still can be slow when number of products grows to 100k.

I've done a lot of experimenting and here are my findings.

GIN and sorting

GIN index currently (as of version 9.4) can not assist ordering.

  Of the index types currently supported by PostgreSQL, only B-tree can produce sorted output — the other index types return matching rows in an unspecified, implementation-dependent order.

work_mem

Thanks Chris for pointing out to this configuration parameter.
It defaults to 4MB, and in case your recordset is larger, increasing work_mem to proper value (can be found from EXPLAIN ANALYSE) can significantly speed up sort operations.

ALTER SYSTEM SET work_mem TO '32MB';

Restart the server for change to take effect, then double check:

SHOW work_mem;

Original query

I've populated my database with 650k products with some categories holding up to 40k products. I've simplified query a bit by removing published clause:

SELECT * FROM products WHERE category_ids @> ARRAY [248688]
ORDER BY score DESC, title LIMIT 10 OFFSET 30000;

Limit  (cost=2435.62..2435.62 rows=1 width=1390) (actual time=1141.254..1141.256 rows=10 loops=1)
  ->  Sort  (cost=2434.00..2435.62 rows=646 width=1390) (actual time=1115.706..1140.513 rows=30010 loops=1)
        Sort Key: score, title
        Sort Method: external merge  Disk: 29656kB
        ->  Bitmap Heap Scan on products  (cost=17.01..2403.85 rows=646 width=1390) (actual time=11.831..25.646 rows=41666 loops=1)
              Recheck Cond: (category_ids @> '{248688}'::integer[])
              Heap Blocks: exact=6471
              ->  Bitmap Index Scan on idx_products_category_ids_gin  (cost=0.00..16.85 rows=646 width=0) (actual time=10.140..10.140 rows=41666 loops=1)
                    Index Cond: (category_ids @> '{248688}'::integer[])
Planning time: 0.288 ms
Execution time: 1146.322 ms

As we can see work_mem was not enough so we had Sort Method: external merge  Disk: 29656kB (the number here is approximate, it needs slightly more than 32MB for in-memory quicksort).

Reduce memory footprint

Don't select full records for sorting, use ids, apply sort, offset and limit, then load just 10 records we need:

SELECT * FROM products WHERE id in (
  SELECT id FROM products WHERE category_ids @> ARRAY[248688]
  ORDER BY score DESC, title LIMIT 10 OFFSET 30000
) ORDER BY score DESC, title;

Sort  (cost=2444.10..2444.11 rows=1 width=1390) (actual time=707.861..707.862 rows=10 loops=1)
  Sort Key: products.score, products.title
  Sort Method: quicksort  Memory: 35kB
  ->  Nested Loop  (cost=2436.05..2444.09 rows=1 width=1390) (actual time=707.764..707.803 rows=10 loops=1)
        ->  HashAggregate  (cost=2435.63..2435.64 rows=1 width=4) (actual time=707.744..707.746 rows=10 loops=1)
              Group Key: products_1.id
              ->  Limit  (cost=2435.62..2435.62 rows=1 width=72) (actual time=707.732..707.734 rows=10 loops=1)
                    ->  Sort  (cost=2434.00..2435.62 rows=646 width=72) (actual time=704.163..706.955 rows=30010 loops=1)
                          Sort Key: products_1.score, products_1.title
                          Sort Method: quicksort  Memory: 7396kB
                          ->  Bitmap Heap Scan on products products_1  (cost=17.01..2403.85 rows=646 width=72) (actual time=11.587..35.076 rows=41666 loops=1)
                                Recheck Cond: (category_ids @> '{248688}'::integer[])
                                Heap Blocks: exact=6471
                                ->  Bitmap Index Scan on idx_products_category_ids_gin  (cost=0.00..16.85 rows=646 width=0) (actual time=9.883..9.883 rows=41666 loops=1)
                                      Index Cond: (category_ids @> '{248688}'::integer[])
        ->  Index Scan using products_pkey on products  (cost=0.42..8.45 rows=1 width=1390) (actual time=0.004..0.004 rows=1 loops=10)
              Index Cond: (id = products_1.id)
Planning time: 0.682 ms
Execution time: 707.973 ms

Note Sort Method: quicksort  Memory: 7396kB. Result is much better.

JOIN and additional B-tree index

As Chris advised I've created additional index:

CREATE INDEX idx_test7 ON products (score DESC, title);

First I tried joining like this:

SELECT * FROM products NATURAL JOIN
  (SELECT id FROM products WHERE category_ids @> ARRAY[248688]
  ORDER BY score DESC, title LIMIT 10 OFFSET 30000) c
ORDER BY score DESC, title;

Query plan differs slightly but result is the same:

Sort  (cost=2444.10..2444.11 rows=1 width=1390) (actual time=700.747..700.747 rows=10 loops=1)
  Sort Key: products.score, products.title
  Sort Method: quicksort  Memory: 35kB
  ->  Nested Loop  (cost=2436.05..2444.09 rows=1 width=1390) (actual time=700.651..700.690 rows=10 loops=1)
        ->  HashAggregate  (cost=2435.63..2435.64 rows=1 width=4) (actual time=700.630..700.630 rows=10 loops=1)
              Group Key: products_1.id
              ->  Limit  (cost=2435.62..2435.62 rows=1 width=72) (actual time=700.619..700.619 rows=10 loops=1)
                    ->  Sort  (cost=2434.00..2435.62 rows=646 width=72) (actual time=697.304..699.868 rows=30010 loops=1)
                          Sort Key: products_1.score, products_1.title
                          Sort Method: quicksort  Memory: 7396kB
                          ->  Bitmap Heap Scan on products products_1  (cost=17.01..2403.85 rows=646 width=72) (actual time=10.796..32.258 rows=41666 loops=1)
                                Recheck Cond: (category_ids @> '{248688}'::integer[])
                                Heap Blocks: exact=6471
                                ->  Bitmap Index Scan on idx_products_category_ids_gin  (cost=0.00..16.85 rows=646 width=0) (actual time=9.234..9.234 rows=41666 loops=1)
                                      Index Cond: (category_ids @> '{248688}'::integer[])
        ->  Index Scan using products_pkey on products  (cost=0.42..8.45 rows=1 width=1390) (actual time=0.004..0.004 rows=1 loops=10)
              Index Cond: (id = products_1.id)
Planning time: 1.015 ms
Execution time: 700.918 ms

Playing with various offsets and product counts I could not make PostgreSQL use additional B-tree index.

So I went classical way and created junction table:

CREATE TABLE prodcats AS SELECT id AS product_id, unnest(category_ids) AS category_id FROM products;
CREATE INDEX idx_prodcats_cat_prod_id ON prodcats (category_id, product_id);

SELECT p.* FROM products p JOIN prodcats c ON (p.id=c.product_id)
WHERE c.category_id=248688
ORDER BY p.score DESC, p.title LIMIT 10 OFFSET 30000;

Limit  (cost=122480.06..122480.09 rows=10 width=1390) (actual time=1290.360..1290.362 rows=10 loops=1)
  ->  Sort  (cost=122405.06..122509.00 rows=41574 width=1390) (actual time=1264.250..1289.575 rows=30010 loops=1)
        Sort Key: p.score, p.title
        Sort Method: external merge  Disk: 29656kB
        ->  Merge Join  (cost=50.46..94061.13 rows=41574 width=1390) (actual time=117.746..182.048 rows=41666 loops=1)
              Merge Cond: (p.id = c.product_id)
              ->  Index Scan using products_pkey on products p  (cost=0.42..90738.43 rows=646067 width=1390) (actual time=0.034..116.313 rows=210283 loops=1)
              ->  Index Only Scan using idx_prodcats_cat_prod_id on prodcats c  (cost=0.43..1187.98 rows=41574 width=4) (actual time=0.022..7.137 rows=41666 loops=1)
                    Index Cond: (category_id = 248688)
                    Heap Fetches: 0
Planning time: 0.873 ms
Execution time: 1294.826 ms

Still not using B-tree index, resultset did not fit work_mem, hence poor results.

But under some circumstances, having large number of products and small offset PostgreSQL now decides to use B-tree index:

SELECT p.* FROM products p JOIN prodcats c ON (p.id=c.product_id)
WHERE c.category_id=248688
ORDER BY p.score DESC, p.title LIMIT 10 OFFSET 300;

Limit  (cost=3986.65..4119.51 rows=10 width=1390) (actual time=264.176..264.574 rows=10 loops=1)
  ->  Nested Loop  (cost=0.98..552334.77 rows=41574 width=1390) (actual time=250.378..264.558 rows=310 loops=1)
        ->  Index Scan using idx_test7 on products p  (cost=0.55..194665.62 rows=646067 width=1390) (actual time=0.030..83.026 rows=108037 loops=1)
        ->  Index Only Scan using idx_prodcats_cat_prod_id on prodcats c  (cost=0.43..0.54 rows=1 width=4) (actual time=0.001..0.001 rows=0 loops=108037)
              Index Cond: ((category_id = 248688) AND (product_id = p.id))
              Heap Fetches: 0
Planning time: 0.585 ms
Execution time: 264.664 ms

This is in fact quite logical as B-tree index here does not produce direct result, it is only used as a guide for sequential scan.

Let's compare with GIN query:

SELECT * FROM products WHERE id in (
  SELECT id FROM products WHERE category_ids @> ARRAY[248688]
  ORDER BY score DESC, title LIMIT 10 OFFSET 300
) ORDER BY score DESC, title;

Sort  (cost=2519.53..2519.55 rows=10 width=1390) (actual time=143.809..143.809 rows=10 loops=1)
  Sort Key: products.score, products.title
  Sort Method: quicksort  Memory: 35kB
  ->  Nested Loop  (cost=2435.14..2519.36 rows=10 width=1390) (actual time=143.693..143.736 rows=10 loops=1)
        ->  HashAggregate  (cost=2434.71..2434.81 rows=10 width=4) (actual time=143.678..143.680 rows=10 loops=1)
              Group Key: products_1.id
              ->  Limit  (cost=2434.56..2434.59 rows=10 width=72) (actual time=143.668..143.670 rows=10 loops=1)
                    ->  Sort  (cost=2433.81..2435.43 rows=646 width=72) (actual time=143.642..143.653 rows=310 loops=1)
                          Sort Key: products_1.score, products_1.title
                          Sort Method: top-N heapsort  Memory: 68kB
                          ->  Bitmap Heap Scan on products products_1  (cost=17.01..2403.85 rows=646 width=72) (actual time=11.625..31.868 rows=41666 loops=1)
                                Recheck Cond: (category_ids @> '{248688}'::integer[])
                                Heap Blocks: exact=6471
                                ->  Bitmap Index Scan on idx_products_category_ids_gin  (cost=0.00..16.85 rows=646 width=0) (actual time=9.916..9.916 rows=41666 loops=1)
                                      Index Cond: (category_ids @> '{248688}'::integer[])
        ->  Index Scan using products_pkey on products  (cost=0.42..8.45 rows=1 width=1390) (actual time=0.004..0.004 rows=1 loops=10)
              Index Cond: (id = products_1.id)
Planning time: 0.630 ms
Execution time: 143.921 ms

GIN's result is much better. I checked with various combinations of number of products and offset, under no circumstances junction table approach was any better.

The power of real index

In order for PostgreSQL to fully utilize index for sorting, all query WHERE parameters
as well as ORDER BY parameters must reside in single B-tree index.
To do this I have copied sort fields from product to junction table:

CREATE TABLE prodcats AS SELECT id AS product_id, unnest(category_ids) AS category_id, score, title FROM products;
CREATE INDEX idx_prodcats_1 ON prodcats (category_id, score DESC, title, product_id);

SELECT * FROM products WHERE id in (SELECT product_id FROM prodcats WHERE category_id=248688 ORDER BY score DESC, title LIMIT 10 OFFSET 30000) ORDER BY score DESC, title;

Sort  (cost=2149.65..2149.67 rows=10 width=1390) (actual time=7.011..7.011 rows=10 loops=1)
  Sort Key: products.score, products.title
  Sort Method: quicksort  Memory: 35kB
  ->  Nested Loop  (cost=2065.26..2149.48 rows=10 width=1390) (actual time=6.916..6.950 rows=10 loops=1)
        ->  HashAggregate  (cost=2064.83..2064.93 rows=10 width=4) (actual time=6.902..6.904 rows=10 loops=1)
              Group Key: prodcats.product_id
              ->  Limit  (cost=2064.02..2064.71 rows=10 width=74) (actual time=6.893..6.895 rows=10 loops=1)
                    ->  Index Only Scan using idx_prodcats_1 on prodcats  (cost=0.56..2860.10 rows=41574 width=74) (actual time=0.010..6.173 rows=30010 loops=1)
                          Index Cond: (category_id = 248688)
                          Heap Fetches: 0
        ->  Index Scan using products_pkey on products  (cost=0.42..8.45 rows=1 width=1390) (actual time=0.003..0.003 rows=1 loops=10)
              Index Cond: (id = prodcats.product_id)
Planning time: 0.318 ms
Execution time: 7.066 ms

And this is the worst scenario with large number of products in chosen category and large offset. When offset=300 execution time is just 0.5 ms.

Unfortunately maintaining such a junction table requires extra effort. It could be accomplished via indexed materialized views,
but that is only useful when your data updates rarely, cause refreshing such materialized view is quite a heavy operation.

So I am staying with GIN index so far, with increased work_mem and reduced memory footprint query.


Column cannot be converted from type 'int' to type 'bigint(20) unsigned'? I have a Percona MySQL cluster, with a master and a few slaves. Each of them is running "Ver 14.14 Distrib 5.5.40-36.1" of MySQL. Replication is row based.

I ran an alter query on one of the tables of the slave only. The plan is to run this query on all slaves and then do a master switch as we cannot afford to lock tables in master.

The query is:

ALTER TABLE order_item_units
MODIFY parent_id BIGINT(20) unsigned ;

And post this when I checked the slave using show slave status, I see the replication is broken with following error:

  Column 3 of table 'database_name.order_item_units' cannot be converted from type 'int' to type 'bigint(20) unsigned'

And when I checked the column, it did convert into BIGINT.

And now I am not able to fix this. I did stop slave and start slave, didn't help. Did stop slave and MySQL restart, didn't help. Did a skip counter, didn't help either.

Either the column should not have gotten converted, then the error would have be just, but then may be there should not have been any error at all.

And if the column did get converted, then why the error?

Any clue what am I missing here?

I'm not sure why you are getting that error. I have 2 solutions. The solutions will give you an error if your data truly can't be converted to unsigned bigint.

ALTER TABLE `order_item_units` ADD COLUMN `parent_id2` BIGINT(255) UNSIGNED AFTER `parent_id`;
UPDATE `order_item_units` SET `parent_id2` = `parent_id`;
SELECT * FROM `order_item_units` WHERE `parent_id2` != `parent_id` OR `parent_id` IS NULL;

Verify data from the 3rd query.
Once you're comfortable that the data is in place, drop the old column. Then rename the new column to the old column name. It is imperative that you verify your data before dropping.

ALTER TABLE `order_item_units` DROP COLUMN `parent_id`;
ALTER TABLE `order_item_units` CHANGE `parent_id2` `parent_id` BIGINT(255) UNSIGNED;

Don't forget to add any constraints to the new column.

You could also do the same process with a temporary table OR creating a new table with the new column definition. Then selecting your old table into it. Verify your data. Rename your original table by appending "_old" to it. Then rename your corrected table to the original table name


How to determine SQL server version install history? I.E. When it was updated last I have various SQL server installs across several machines, and a different IT staff will sometimes randomly update each machine with different service packs and cause version issues.

Is there a way to see the version update history for the different SQL machines?

Did you try this folder?

C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Update Cache

I once needed it. And found all the previous setups there. You can check their last modified dates. Maybe it helps.


Easiest way to import access db (.accdb) into SQL Server 2016? I have a .accfb file from an access 2016 database and am trying to import this database into a SQL Server 2016 developer edition instance.

While I found posts online discussing using import/export data tools in SQL Server and also migration assistant for access, neither have worked out for me.

Does anyone know how to easily move a database from access 2016 to sql server 2016? More specifically an "accdb" file type?

All suggestions are greatly appreciated!

1.Open MS SQL Server Import and Export Wizard.

2.For “Data Source” choose “Microsoft Office 12.0 Access Database Engine OLE DB Provider”.

3.Click “Properties” enter the location of the .accdb file in the “Data Source” field. That is the physical location of the Access database file, like C:\Temp.

4.Under login information, choose a blank username and be sure to check “Blank Password”.

5.Test the connection. If it works, click OK.

6.For the “Data Destination” choose “Microsoft OLE DB Provider for SQL Server”.

7.Enter the MS SQL Server name or choose from the dropdown menu.

8.Enter your SQL credentials and select the database you wish to import the data to, the database should show up in the dropdown menu if your MS SQL info is correct.

9.Select “Copy data from one or more tables or views”.

10.Select the tables you want copied and edit the mappings if needed.

11.Continue through the wizard and hit finish to begin the import.


sqlcmd returning literal 'NULL' for null columns when outputting to CSV I'm using sqlcmd (SQL Server 2008 R2) to execute a simple query to output data to a .csv file. In the SQL file, I have the following code:

USE [OSCAR_UK]
SELECT DISTINCT ISNULL(LTRIM(RTRIM(il.[LifeInsuredFirstName])),'') AS [First Name]
  ,ISNULL(LTRIM(RTRIM(il.[LifeInsuredSurname])),'') AS [Last Name]
  ,CONVERT(varchar(26),il.[DateOfBirth],23) AS [Date Of Birth]
  ,ISNULL(LTRIM(RTRIM(il.[Gender])),'') AS [Gender]
  ,ISNULL(LTRIM(RTRIM(il.[PostCode])),'') AS [Postcode]
  ,ISNULL(LTRIM(RTRIM(ce.[PolicyNumber])),'') AS [Policy Number]
FROM [dbo].[Insured_List] il
LEFT OUTER JOIN [dbo].[Cession] ce
ON il.[pk_Cession] = ce.[pk_Cession]

My problem is that any field containing a NULL value is returned to the .csv file as the string literal 'NULL' rather than an empty string, despite my use of ISNULL to trap that condition.

My searches so far have really only found people complaining that it all worked OK in SQL Server 2005. Have I missed something simple? If anyone could point me towards a research source that would be very kind.

Thanks in advance.

EDIT: It appears that the problem occurs only when the output is redirected to a file, not if it is allowed to simply appear on-screen. I'll have to do some sort of global replace after the fact. It's a pain but if that's the way it is, it's the way it is.

I cannot reproduce the problem you're seeing.

I'm using a SQL Server 2008 R2 instance, connecting to it via sqlcmd.exe using the following test command, which I've wrapped for readability:

.\sqlcmd -S "My2008R2Instance" -E -d tempdb -Q "set nocount on;
   create table #t (col1 varchar(10) null, col2 varchar(10) null);
   insert into #t(col1, col2) values (null, null);
   select coalesce(col1, '') as col1, coalesce(col2, '') as col2
   from #t;" -o C:\temp\test.csv -s "," -h -1

The output consists of a single line file with a comma between the two columns:

          ,

As you can see, there are no 'NULL' values returned; they've been replaced by the zero-length-string, as required by the COALESCE function.


Log Shipping Server Restart Problem I have Log Shipping setup from Primary Server A to Destination B. Server B is both Destination and Monitoring server.
Yesterday, we restarted both servers around the same time for patching. On the Log Shipping Status report, it says Primary Source Backups are Not Up to Date and Failing. So I run the LS backup manually, and it still says backup has not occurred in 2000 minutes??

The Destination Monitoring Server says Copies and Restores are Success.
How do I resolve this problem on Source Server A?

I would recommend looking at the stored procedures that are provided to help manage Log Shipping.  If you Google "sp_refresh_log_shipping_monitor" you will find that it's just one of many stored procedures available.  It appeared to be the most likely to help you in this situation.  Hopefully, it will keep you from having to resort to tearing down Log Shipping and reconfiguring it.


SQL Query with optional WHERE clause parameter for date range? I have a procedure that takes, among other things,  start and end date parameters. These two parameters are optional inputs and default to NULL. What I would like to do is modify one of the current SELECT statements so that if the start and end dates are supplied it will return only the results between those two dates. I considered just throwing a CASE WHEN... in the WHERE statement but that that is messy and does not seem right. Is that the appropriate way to deal with this or is there something more efficient?

Existing SELECT clause:

SELECT  do.OrdrNmbr AS 'Order Number',
        mb.BOLNmbr AS 'BOL Number',
        do.DlvryDt  AS 'Deliver Date',
        mh.Text3 AS 'Truck',
        do.DlvrdQntty AS 'Gallons Delivered',
        ba.BANme AS 'Carier Business Associate'
FROM dbo.DeliveryOrder AS do WITH (NOLOCK)
INNER JOIN dbo.ManifestBOL AS mb WITH (NOLOCK)
    ON do.OrdrNmbr = mb.OrdrNmbr
INNER JOIN dbo.BusinessAssociate AS ba WITH (NOLOCK)
    ON do.CrrrBAID = ba.BAID
INNER JOIN SRA.dbo.MovementDocument as md WITH (NOLOCK)
    ON md.MvtDcmntExtrnlDcmntNbr = 'DOD' + CONVERT(VARCHAR, mb.OrdrNmbr)
INNER JOIN SRA.dbo.MovementHeader AS mh WITH (NOLOCK)
   ON mh.MvtHdrMvtDcmntID = md.MvtDcmntID
WHERE mb.OrdrNmbr = @OrderNumber;

and the start/end dates would run against do.DlvryDt.

P.S. I know the table/column names are horrid but this is a 3rd party application the query is running against.

Assuming that your start date and end date are compared against DlvryDt in DeliveryOrder table, I suggest to use a very low date value for start date and a very high date value for end date in cases where these date variables are null. Please see following query.

SELECT  do.OrdrNmbr AS 'Order Number',
        mb.BOLNmbr AS 'BOL Number',
        do.DlvryDt  AS 'Deliver Date',
        mh.Text3 AS 'Truck',
        do.DlvrdQntty AS 'Gallons Delivered',
        ba.BANme AS 'Carier Business Associate'
FROM dbo.DeliveryOrder AS do WITH (NOLOCK)
INNER JOIN dbo.ManifestBOL AS mb WITH (NOLOCK)
    ON do.OrdrNmbr = mb.OrdrNmbr
INNER JOIN dbo.BusinessAssociate AS ba WITH (NOLOCK)
    ON do.CrrrBAID = ba.BAID
INNER JOIN SRA.dbo.MovementDocument as md WITH (NOLOCK)
    ON md.MvtDcmntExtrnlDcmntNbr = 'DOD' + CONVERT(VARCHAR, mb.OrdrNmbr)
INNER JOIN SRA.dbo.MovementHeader AS mh WITH (NOLOCK)
   ON mh.MvtHdrMvtDcmntID = md.MvtDcmntID
WHERE mb.OrdrNmbr = @OrderNumber
and do.DlvryDt >= ISNULL(@StartDate, '19000101')
and do.DlvryDt < ISNULL(dateadd(day, 1, @EndDate), '21000101');


How does SQL Server handle SSIS project deployments, and what is best practice executing these packages? I'm quite new to SSIS (actually SSDT with VS2015), and have a .sln that uses 3 packages:

Main.dtsx
ArchiveFiles.dtsx
FTP.dtsx

The 'entry' point (is this the correct way of thinking of it?) is Main.dtsx, which will execute the 'child' packages (no. 2 and no. 3) at various points in the flow.

To deploy, I simply just copied the wizard (the .ispac file in /bin/Development) which seemed to recreate my project within the Integration Services Catalogs folder on SQL Server.

I have been able to execute the SSIS solution by right clicking and executing the Main.dtsx package from SQL Server. Same with creating a scheduled job (just tell it to execute the Main.dtsx package and everything works).

I'm deploying to SQL Server 2016 and SQL Server 2012.

Questions:

Since the project seems to have been copied verbatim to SQL Server, and the code is just XML, could I edit the deployed packages in the future? (I'm using Git for source control so I would NOT want anyone editing the package directly and then someone else deploying from the source control)
Is my deployment method correct and best practice? Seems like this is too simple and intuitive for the Microsoft world... (considering the headache and pain involved in working with SSIS in general)

Could I edit the deployed packages in the future?
You can import from GIT, open the package in SSDT and deploy again. Overwriting the existing deployed package.
You can also export using SSMS save and open in SSDT. Once you make your changes deploy again and update your source control.
As far as not letting anybody else modify or deploy packages this document will give you a good start.  A SSISDB is considered another user database.
Is my deployment method correct and best practice?
As you are using datatools to create your solution I would follow this method (assuming you have the privilege).

Deploy packages by using SQL Server Data Tools (Visual Studio)
In Visual Studio, with an Integration Services project open, select
the package or packages that you want to deploy.
Right-click and
select Deploy Package. The Deployment Wizard opens with the selected
packages configured as the source packages.
Complete the wizard.
Follow the remaining steps described in Package Deployment Model.

You can also do the same using SSMS. Details here.
In case you are not aware that you can deploy package from SSMS using tsql, details here.


Help ordering table query I have a MySQL table with this structure:
Gene  |  Impact  |  Frequency
---------------------------
SCN1A | LOW      |  0.8
---------------------------
CFTR  | MODERATE |  0.1
---------------------------
ADA   | HIGH     |  0.2
---------------------------
ACADM | LOW      |  0.1
---------------------------
SCN1A | HIGH     |  0
---------------------------
CFTR  | LOW      |  0.5
---------------------------

I need to order the table in a SELECT query according values in 'Impact' column (HIGH > MODERATE > LOW) and Frequency descending values when 'Impact' values are the same, like this:
Gene  | Impact   |  Frequency
---------------------------
SCN1A | HIGH     | 0
---------------------------
ADA   | HIGH     | 0.2
---------------------------
CFTR  | MODERATE | 0.1
---------------------------
ACADM | LOW      | 0.1
---------------------------
CFTR  | LOW      | 0.5
---------------------------
SCN1A | LOW      | 0.8
---------------------------

Up to this point the query must contain the next statement:
ORDER BY FIELD(Impact,'HIGH','MODERATE','LOW'), Frequency

The problem is that, after ordering, I need to go up those rows that have same value in first column  in order to have all rows with same Gene name together, like this:
Gene  | Impact   |  Frequency
---------------------------
SCN1A | HIGH     |  0
---------------------------
SCN1A | LOW      | 0.8
---------------------------
ADA   | HIGH     | 0.2
---------------------------
CFTR  | MODERATE | 0.1
---------------------------
CFTR  | LOW      | 0.5
---------------------------
ACADM | LOW      | 0.1
---------------------------

Does anyone can help me with the query?

Imagine that ADA has Frequency=0. Should it be returned before the SCN1A group? If not, then additionally imagine that SCN1A with Impact=HIGH has Frequency=0.1`.

The order of the gene does not matter when both have the same Impact and Frequency value, although the ideal would be to order them alphabetically, so ADA would be first in your example. If SCN1A has Impact HIGH and Frequency 0.1 should be in third position as found in the example.

WITH
-- enumerate all rows according to specified ordering
cte1 AS (SELECT *, ROW_NUMBER() OVER (ORDER BY FIELD(Impact,'HIGH','MODERATE','LOW'), Frequency) rn
         FROM test),
-- find the most upper occurence for each `Gene`
cte2 AS (SELECT Gene, MIN(rn) rn
         FROM cte1
         GROUP BY Gene)
-- select desired data
SELECT t1.*
FROM test t1
JOIN cte2 USING(Gene)
-- use found "most upper occurence" for groups pre-sorting,
-- then apply specified sorting
ORDER BY cte2.rn, FIELD(Impact,'HIGH','MODERATE','LOW'), Frequency;

fiddle

the order of the gene does not matter when both have the same Impact and Frequency value, although the ideal would be to order them alphabetically, so ADA wuould be first in your example.

If so simply add , Gene to the end of ordering expression.


Trouble Shooting "Unable to complete login process" Issue
On one of our SQL Server witness servers (This is part of our Database Mirroring setup for SQL Server 2008 R2) we have just started getting the below event in the windows event log.

  The description for Event ID 17052 from source MSSQLSERVER cannot be found.
      Either the component that raises this event is not installed on your local
      computer or the installation is corrupted. You can install or repair the
      component on the local computer.

  If the event originated on another computer, the display information had to
      be saved with the event.

  The following information was included with the event:

  Severity: 16 Error:0, OS: 0 [Microsoft][SQL Server Native Client 10.0]Unable
      to complete login process due to delay in opening server connection

  the message resource is present but the message is not found in the
      string/message table

These events are happening at varying times and so far we have had 22 errors starting on 27/08/2015.  I have looked through our change log and I can't see any changes for that day that look relevant.

I have done some investigation and found the following article. This looks at the xml information in the below table; however none of the events in the result set correspond to the times of any of the failures.

SELECT CAST(record as xml) as record
FROM sys.dm_os_ring_buffers
WHERE ring_buffer_type = 'RING_BUFFER_CONNECTIVITY'

To make matters worse the errors seem sporadic with no determinable pattern.

Question
How can I find out what is causing the above events?

Version
Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64)
    Jun 17 2011 00:54:03
    Copyright (c) Microsoft Corporation
    Standard Edition (64-bit) on Windows NT 6.1  (Build 7601: Service Pack 1) (Hypervisor)

Troubleshooting this wasn't the simplest of processes, as it turned out to be a third party application killing the user. However there were clear indications in the SQL log that there were problems.

There were around 20 memory errors a day in the SQL Server log along the lines of "A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 7254 seconds. Working set (KB): 12756, committed (KB): 61520, memory utilization: 20%."

Using a product called ExtraHop (You can use your favorite network packet capturing tool) we were able to trace all the traffic going into and out of the server during the time frame of the 17052 error. This showed us that our monitoring software was connecting to the server and that a simple SELECT 1 was taking two seconds to complete.  A Change request was raised to increase the memory and then implemented and the errors went away.

I believe the monitoring software terminates its connection attempt to an SQL server; as the termination was coming from outside of SQL it would indeed be an unknown error.


Optimizing WHERE condition for TIMESTAMP field in MySQL SELECT statement I am working on a schema for an analytics system which tracks usage times, and there is a need to see total usage time in a certain date range.

To give a simple example, this type of query would be run often:

select sum(diff_ms) from writetest_table where time_on > ("2015-07-13 15:11:56");

This query typically takes around 7 seconds on a table that is heavily populated. It has ~35 million rows, MyISAM on MySQL running on Amazon RDS (db.m3.xlarge).

Getting rid of the WHERE clause makes the query take only 4 seconds, and adding a second clause (time_off > XXX) adds an additional 1.5 seconds, bringing the query time to 8.5 seconds.

Since I know these types of queries will be commonly done I would like to optimize things so they are faster, ideally below 5 seconds.

I started by adding an index on time_on, and though that drastically sped up a WHERE "=" query, it had no effect on the ">" query. Is there a way to create an index that would speed up the WHERE ">" or "<" queries?

Or if there are any other suggestions the performance of this type of query, please let me know.

Note: I am using the "diff_ms" field as a denormalization step (it equals time_off - time_on) which improves performance of the aggregation by around 30%-40%.

I am creating the index with this command:

ALTER TABLE writetest_table ADD INDEX time_on (time_on) USING BTREE;

Running "explain" on the original query (with "time_on >") says time_on is a "possible_key" and the select_type is "SIMPLE". The "extra" column says "Using where", and "type" is "ALL". After the index was added, the table says "time_on" is "MUL" key type, which seems correct since the same time can be present twice.

Here is the table schema:

CREATE TABLE `writetest_table` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `sessionID` int(11) DEFAULT NULL,
  `time_on` timestamp NULL DEFAULT NULL,
  `time_off` timestamp NULL DEFAULT NULL,
  `diff_ms` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `time_on` (`time_on`)
) ENGINE=MyISAM AUTO_INCREMENT=50410902 DEFAULT CHARSET=latin1;

UPDATE: I created the following index based on ypercube's response, but this increases the query time for the first query to around 17 seconds!

ALTER TABLE writetest_table  ADD INDEX time_on__diff_ms__ix (time_on, diff_ms) ;

UPDATE 2: EXPLAIN output

mysql> explain select sum(diff_ms) from writetest_table where time_on > '2015-07-13 15:11:56';
+----+-------------+---------------------+-------+----------------------+----------------------+---------+------+----------+--------------------------+
| id | select_type | table               | type  | possible_keys        | key                  | key_len | ref  | rows     | Extra                    |
+----+-------------+---------------------+-------+----------------------+----------------------+---------+------+----------+--------------------------+
|  1 | SIMPLE      | writetest_table_old | index | time_on__diff_ms__ix | time_on__diff_ms__ix | 10      | NULL | 35831102 | Using where; Using index |
+----+-------------+---------------------+-------+----------------------+----------------------+---------+------+----------+--------------------------+
1 row in set (0.00 sec)

Update 3: result of requested query

mysql> SELECT time_on FROM writetest_table ORDER BY time_on LIMIT 1;
+---------------------+
| time_on             |
+---------------------+
| 2015-07-13 15:11:56 |
+---------------------+
1 row in set (0.01 sec)

For the specific query:

select sum(diff_ms)
from writetest_table
where time_on > '2015-07-13 15:11:56' ;     -- use single quotes, not double

an index on (time_on, diff_ms) would be the best option. So, if the query runs often enough or its efficiency is crucial to your application, add this index:

ALTER TABLE writetest_table
  ADD INDEX time_on__diff_ms__ix      -- pick a name for the index
    (time_on, diff_ms) ;

(Not related to the question)
  And really, change the table's engine to InnoDB. It's 2015 and MyISAM's funeral was some years ago.
(/rant)


RMAN restoring datafile out of ASM Why is RMAN restoring the datafiles out of ASM and how to restore a backup into ASM?

Here is one output. The tablespace triscaltb was restored in /u01/app/oracle/product/12.1.0/dbhome_1/dbs/MISSING00013

RMAN> report schema;

Report of database schema for database with db_unique_name TRISCALDB

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    780      SYSTEM               YES     +DATA/TRISCALDB/DATAFILE/system.258.1016190249
3    600      SYSAUX               NO      +DATA/TRISCALDB/DATAFILE/sysaux.257.1016190057
4    135      UNDOTBS1             YES     +DATA/TRISCALDB/DATAFILE/undotbs1.260.1016190491
5    250      PDB$SEED:SYSTEM      NO      +DATA/TRISCALDB/FD9AC20F64D244D7E043B6A9E80A2F2F/DATAFILE/system.267.1016191021
6    5        USERS                NO      +DATA/TRISCALDB/DATAFILE/users.259.1016190489
7    510      PDB$SEED:SYSAUX      NO      +DATA/TRISCALDB/FD9AC20F64D244D7E043B6A9E80A2F2F/DATAFILE/sysaux.266.1016191021
8    260      TRISCALPDB:SYSTEM    NO      +DATA/TRISCALDB/FD9BD2B44413096FE043B6A9E80ABC28/DATAFILE/system.272.1016192445
9    520      TRISCALPDB:SYSAUX    NO      +DATA/TRISCALDB/FD9BD2B44413096FE043B6A9E80ABC28/DATAFILE/sysaux.271.1016192441
10   5        TRISCALPDB:USERS     NO      +DATA/TRISCALDB/FD9BD2B44413096FE043B6A9E80ABC28/DATAFILE/users.273.1016192445
11   1243     TRISCALPDB:EXAMPLE   NO      +DATA/TRISCALDB/FD9BD2B44413096FE043B6A9E80ABC28/DATAFILE/example.270.1016192435
13   0        TRISCALTB            NO      /u01/app/oracle/product/12.1.0/dbhome_1/dbs/MISSING00013

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    60       TEMP                 32767       +DATA/TRISCALDB/TEMPFILE/temp.265.1016190837
2    20       PDB$SEED:TEMP        32767       +DATA/TRISCALDB/FD9AC20F64D244D7E043B6A9E80A2F2F/DATAFILE/pdbseed_temp012019-08-13_11-21-09-am.dbf
3    20       TRISCALPDB:TEMP      32767       +DATA/TRISCALDB/FD9BD2B44413096FE043B6A9E80ABC28/DATAFILE/triscalpdb_temp012019-08-13_11-47-56-am.dbf

Imagine the following.

On 2019-08-19 18:00:00 you started a full online database backup that took 5 minutes. This backup included all the datafiles listed above.

Backuppieces created from this operation:

arch1.bkp - backup of archivelogs that existed before starting the full database backup + the last archivelog that was created as a result of the implicit logswitch
db1.bkp - backup of datafiles
arch2.bkp - backup of archivelogs that were created during the full database backup + the last archivelog that was created as a result of the implicit logswitch
c1.bkp - controlfile autobackup created at the end of the backup process

Then at 2019-08-19 18:10:00 someone dropped the TRISCTALTB tablespace.
At 2019-08-19 18:15:00 a scheduled archivelog backup was started, which took 1 minute, and created the following backuppieces:

arch3.bkp - backup of archivelogs created since the last backup + the last archivelog that was created as a result of the implicit logswitch
c2.bkp - controlfile autobackup created at the end of the backup process

At 2019-08-19 18:20:00 you realize that TRISCALTB tablespace is missing. You check the alert log, and see the tablespace was dropped at 2019-08-19 18:10:00. You decide to do an incomplete database recovery and specify the point of time as 2019-08-19 18:09:59, right before the  tablespace was dropped.

You then stop the instance, delete all files, check the backups, start the instance in nomount mode, and restore the controlfile from the latest controlfile backup: c2.bkp. The problem is, that controlfile backup was taken after the TRISCALTB was dropped, so there is no datafile 13 in the list of datafiles.

You perform the restore and recovery until 2019-08-19 18:09:59. This restores all datafiles except datafile 13 from db1.bkp, restore and applies the archivelogs from arch2.bkp and arch3.bkp. When the recovery is complete, you then open the database with alter database open resetlogs;.

That is the point where such datafile entries, with MISSING in their name are created. Datafile 13 does not exist in the restored controlfile. Datafile 13 exists in the dictionary in SYSTEM tablespace at the point of time it was restored and recovered to. When you open the database, it recognizes the mismatch between the controlfile and the dictionary, and creates an offline datafile entry in the controlfile with the name of MISSING and the datafile number (00013) under the default location ($ORACLE_HOME/dbs). No, RMAN did not restore datafile 13 at all, it is not in the filesystem, datafile 13 is just a controlfile entry at this point.

Even though you had no errors during the restore and recovery, it is not yet complete. If you see these MISSING datafiles, you need to restore and recover them.

At this point you can do the following in RMAN:

Restore the datafile:

run
{
  set newname for datafile 13 to '+DATA';
  restore datafile 13;
}

Switch to the restored copy:

switch datafile 13 to copy;

Recover the restored datafile:

recover datafile 13;

Finally bring it online:

alter database datafile 13 online;


How do I list all schemas in PostgreSQL? When using PostgreSQL v9.1, how do I list all of the schemas using SQL?

I was expecting something along the lines of:

SELECT something FROM pg_blah;

To lists all schemas, use the (ANSI) standard INFORMATION_SCHEMA

select schema_name
from information_schema.schemata;

More details in the manual

alternatively:

select nspname
from pg_catalog.pg_namespace;

More details about pg_catalog in the manual


SQL Server 2016 on Microsoft Failover cluster Do I need SQL Server 2016 Enterprise Edition for installing it on a Microsoft failover cluster?

No. You can setup a two node failover cluster with the standard edition of SQL Server. Any more than that and you need the enterprise edition.

https://www.microsoft.com/en-us/cloud-platform/sql-server-editions


How can Stored Procedures and Triggers be Automatically Loaded When MySQL Starts Is there any straightforward method of doing as the questions asks?

We are using MySQL 5.5 & Linux.

Use a cron task.
Here are some examples for mysql

http://dbperf.wordpress.com/2010/04/05/scheduling-a-mysql-stored-procedure-using-a-linux-cron-job-and-mysql-events/

Add at boot
http://jonathonhill.net/2009-08-18/easy-startup-scripts-using-crontab/


How does cast work with Set Returning Functions (SRF) like generate_series? I got to wondering about how we would best use generate_series if we only want a series of dates. As a note, generate_series returns setof timestamp or setof timestamp with time zone (same as argument type). For example, observe this..

SELECT generate_series(
  '2018-01-01'::date,
  '2019-01-01'::date,
  '1 day'
);
    generate_series
------------------------
 2018-01-01 00:00:00-06
 2018-01-02 00:00:00-06
 2018-01-03 00:00:00-06

In the above you'll notice the time portion. In order to generate dates, I tried,

SELECT cast(
  generate_series(
    '2018-01-01'::date,
    '2019-01-01'::date,
    '1 day'
  )
  AS date
);
 generate_series
-----------------
 2018-01-01
 2018-01-02
 2018-01-03

To my surprise that worked! But, that raises the question, is this supported? If it is, is this documented. Even more awkward, in the above you'll see that cast is returning a setof, but if you move it to the from condition like this,

SELECT *
FROM cast(
  generate_series(
    '2018-01-01'::date,
    '2019-01-01'::date,
    '1 day'
  )
  AS date
);

You'll get back,

  ERROR:  set-valued function called in context that cannot accept a set

As a side note, it works in sub-selects and the dynamic can also be seen with int/reals

SELECT x/5 FROM (SELECT CAST(generate_series(1,10) AS real)) AS t(x);
SELECT x/5 FROM (SELECT CAST(generate_series(1,10) AS int)) AS t(x);

Question inspired from this answer

Cast the actual value, not the set.

SELECT v::date
FROM generate_series('2018-01-01'::date, '2019-01-01'::date, '1 day') as t(v);


How do I conditionally select elements of an array? I have a table (only one row) in my PostgreSQL 9.5 db with two columns i.e., count (bigint) and array (text).

count     array
6         "112,19.3,142,142,19.3,172,172,20.3,202,202,20.3,232,232,19.3,262,262,19.3,292"

The array represents six sets of values i.e., Lower_limit, height and Upper_limit.How? every three elements of array make the set as 1 thus 18 elements / 3 = 6 sets (count). Now, I need to conditionally modify my array i.e., when upper limit and lower limits are coinciding AND if the difference among values is less than or equal to 1 then merge those sets and select the first upper limit and last lower limit and return the most common value (which is 19.3) among the limits. My desired output would be like:

count    array
1        112, 19.3, 292

Could anyone help me to have some pointers towards my desired output?

UPDATE1:

The array is formed from a series of three members/elements i.e., lower limit, value and upper limit. Whenever, these three values appear in array elements it means we have 1 set (count = 1). In the question, there are 18 elements in array >> 18 / 3 = 6 sets (or count = 6). It can be considered as counting the number of elements in array (3 elements make one set i.e., foo, value, bar >> 1 set). I hope things are clear and I may replace "count" by "set" in question if it creates confusion.

Now my requirement was:

Starting from the first set, check if the upper limit of first set coincides with the next AND
if the difference among values (19.3 and 19.3) is less than or equal to 1
If both or any of above condition fulfills, then merge them and get limits of merged ones
Do the same for all sets and get the lower and upper limit of finally merged set. Count the elements  of merged set and update "counter/set"

In the given array, the difference among values is less than or equal to 1 and also starting from 1st set (112, 19.3, 142)to second set (142,19.3, 172), the upper limit of first set coincides with the lower limit of second set means there are next to each other and also the difference between their values is 0 thus they should be merged. In this manner, I needed to have my desired output. Hope things are clear and make sense now.

I figured out your question by brute force. Please give more details next time. I still have no idea what count() is for. You want the min(), max() and mode() from an array. We can do that by splitting out the text value with string_to_array. Then we can unnest the array to get something we can run our aggregate functions on.

Note, mode is a ordered set aggregate. That's why it looks different.

From there the job is simple.

CREATE TABLE foo(v) AS
VALUES ('112,19.3,142,142,19.3,172,172,20.3,202,202,20.3,232,232,19.3,262,262,19.3,292');

SELECT
  min(x),
  mode() WITHIN GROUP (ORDER BY x),
  max(x)
FROM foo
CROSS JOIN LATERAL unnest(string_to_array(v, ',')) AS x;

 min | mode | max
-----+------+-----
 112 | 19.3 | 292
(1 row)

Or, if you want that in an array..

SELECT ARRAY[
  min(x),
  mode() WITHIN GROUP (ORDER BY x),
  max(x)
]
FROM foo
CROSS JOIN LATERAL unnest(string_to_array(v, ',')) AS x;

     array
----------------
 {112,19.3,292}
(1 row)


mongodump download collections only if changed If I get it right if to make mongodump in to directory that already contains previous dumped data it will still download full collection data even if is not different from data containing in the dir Though it could check the metadata. Is there a way to make it not download collection if it has not "changed" in terms I described?

Mongodb version 4.0.4

As at MongoDB 4.0, collection metadata doesn't include a last modified timestamp and there is no record of when mongodump last ran (or what collections were dumped).

You could write a custom script if you have a means of identifying relevant changes in your deployment since your last backup timestamp, but there are some potentially tricky aspects such as documents that have been removed.

One approach would be to use Change Streams to keep track of relevant collections that have changed since the last backup time, but this would require a replica set deployment. It is definitely possible to configure a single member replica set deployment if that suits your requirements.

It would be more ideal to use a supported backup method if your actual goal is incremental backups rather than per-collection mongodump.


mysqldump not generating USE statement I've got a small script which allows me to select a database, and a number of tables, then output them via mysqldump.

Currently this does not include the USE 'db_name' statement in the output file, something which I need to be included.

Here's an example the dump command I'm using:-

mysqldump -u root db_name tbl_1 tbl_2 tbl_3 > output.sql

I found this question here, which describes the same issue I'm having, however the provided answer does not work for me. The USE 'db_name' statement is simply not written to the outputted SQL file.

Here's everything from one of my dumped SQL files, upto where it starts the DROP/CREATE table commands:

-- MySQL dump 10.13  Distrib 5.5.16, for Win64 (x86)
--
-- Host: localhost    Database: db_name
-- ------------------------------------------------------
-- Server version   5.5.16-log

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

If anyone knows a solution to this, or why the solution in the other question isn't working for me, I'd really appreciate it.

Thanks!

Try putting the USE db_name manually

echo "USE db_name" > output.sql
mysqldump -u root db_name tbl_1 tbl_2 tbl_3 >> output.sql

I can see the wisdom of not mixing the USE command with select mysqldumping of tables. You do not want to risk creating database with the name of a production database. You also do not want the developer to become aware of not the database that will be the target. In this way, the mysqldump, in itself, can never be blamed for overwriting target tables.


How do I debug/set up MariaDB SSL connection? [ I originally posted this on ServerFault but then was pointed at this community ]

Since moving from MySQL to a newer MariaDB installation on Debian 9, I a struggling with SSL connections.

I have created a CA certificate and installed it on both server and client. I then have created server and client keys and certificates that I signed with the CA certificate.

What works:

I can connect from the client to the server with the mysql client without SSL connections.

I can verify the client certificate with openssl:

$ openssl verify MariaDB_client.crt

MariaDB_client.crt: OK

I understand that MariaDB uses yaSSL, but this still shows that the certificates and CA are correct, I think.

On the server, I have:

MariaDB> show global variables like '%ssl%';
+---------------------+--------------------------------------+
| Variable_name       | Value                                |
+---------------------+--------------------------------------+
| have_openssl        | NO                                   |
| have_ssl            | YES                                  |
| ssl_ca              | /etc/mysql/conf.d/MariaDB_ca.crt     |
| ssl_capath          |                                      |
| ssl_cert            | /etc/mysql/conf.d/MariaDB_server.crt |
| ssl_cipher          |                                      |
| ssl_crl             |                                      |
| ssl_crlpath         |                                      |
| ssl_key             | /etc/mysql/conf.d/MariaDB_server.key |
| version_ssl_library | YaSSL 2.4.4                          |
+---------------------+--------------------------------------+

What does not work:

If I add a .my.cnf file with the following content to the users $HOME directory...

[client]
ssl-ca=/--path--/MariaDB_ca.crt
ssl-cert=/--path--/MariaDB_client.crt
ssl-key=/--path--/MariaDB_client.key

...the connection fails with:

ERROR 2026 (HY000): SSL connection error: unable to verify peer checksum

In the server log, I find (not sure whether this is related):

[Warning] IP address 'ip.of.connecting.client' could not be resolved: Name or service not known

My questions:

How can I narrow down/debug this connection problem?
I found conflicting information about whether I should use the same or different CN for the server and client certificates. Currently, I use the same CN.
Are there particular access rights required for the certificates?

A good start is to use the openssl s_client command. You will need a fairly new version of openssl (1.1.0) - see https://serverfault.com/a/931652/317638 for more info.

Then you must remember -starttls mysql :

  openssl s_client -connect YOURHOSTNAME:3306  -CAfile /etc/mysql/cacert.pem  -starttls mysql


Worse performance after create empty table I'm working with MySQL 5.7.10 and I have this issue.

I have this table to trace requests:

CREATE TABLE `invoice_requests` (
    `REQUEST_ID` VARCHAR(20) NOT NULL DEFAULT '' COLLATE 'utf8_spanish_ci',
    `INVOICE_ID` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8_spanish_ci',
    `STARTTIME` DATETIME NULL DEFAULT NULL,
    `ENDTIME` DATETIME NULL DEFAULT NULL,
    `STATUS` VARCHAR(10) NOT NULL DEFAULT 'WORKING',
    PRIMARY KEY (`REQUEST_ID`),
    UNIQUE INDEX `UNQ_INVOICE_ID` (`INVOICE_ID`),
    INDEX `IDX_REQ_CODE_END_TIME` (`REQUEST_ID`, `ENDTIME`),
    INDEX `IDX_INV_NUMBER` (`INVOICE_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

This table had 350MB and last year data.
So I did the following steps:

RENAME TABLE invoice_requests TO 201805_invoice_requests;
run the 'create table' above.

The first executions with this new, empty table has worsened performance of some queries (from 1 second to 15).

With MySQL EXPLAIN, we have checked that no index is used in the INNER JOIN with other tables, but the queries are the same and
the JOINs are made with the indexed field INVOICE_ID.

For testing purposes, we quit the invoice_requests JOIN in the query and the requested data returned fast again.

With this scenario, my questions would be:

Are those the right steps to backup a table (and their indexes too)?
Do I need the "old" index data in the new, empty table? Supposedly, no. But I don't understand this behaviour.

Any help would be very appreciated.

It would help to see the slow query, and its EXPLAIN.

Meanwhile, try

ANALYZE TABLE invoice_requests;

There are no useful 'statistics' on an empty table.  InnoDB should have recalculated the stats before you got to this problem, but we may be missing something.

Indexes are a mess:

PRIMARY KEY (`REQUEST_ID`),   -- notes 1,2
UNIQUE INDEX `UNQ_INVOICE_ID` (`INVOICE_ID`),  -- notes 1,3
INDEX `IDX_REQ_CODE_END_TIME` (`REQUEST_ID`, `ENDTIME`),  -- note 2
INDEX `IDX_INV_NUMBER` (`INVOICE_ID`)  -- note 3

Notes:

Why have two unique indexes?  Can't you live with just one?  (Not knowing a "request" from an "invoice", I cannot advise further.)
Don't bother adding an index that starts with the PK's column(s)
A UNIQUE index is an index plus a uniqueness constraint.  So, don't bother adding another index with the same column(s).

Having a bunch of tables like 201805_invoice_requests will be a problem in the long run.  (There are many threads discussing having multiple identical table.)


How to restructure this slow query For the following query:

SELECT l.*
FROM `leads` AS `l`
INNER JOIN `users` AS `u` ON l.client_id = u.id
WHERE (`l`.`lender_id` = 1)
AND (l.claimed_date IS NULL)
AND (`l`.`disabled` = 0)
AND (u.last_activity_date > '2012-01-13 02:42:10')
AND (l.id = (
    SELECT `leads`.`id`
    FROM `leads`
    WHERE (lender_id = l.lender_id)
    AND (client_id = l.client_id)
    AND (disabled = 0)
    ORDER BY `price` DESC
    LIMIT 0, 1
))
GROUP BY `l`.`client_id`
ORDER BY `u`.`last_activity_date` DESC

How could we restructure the query to remove the correlated subquery?

You could avoid the correlated subquery with a filtering join, like:

select  *
from    leads l
join    users u
on      l.client_id = u.id
join    (
        select  lender_id
        ,       client_id
        ,       max(price) as max_price
        from    leads
        where   disabled = 0
        group by
                lender_id
        ,       client_id
        ) tl
on      tl.lender_id = l.lender_id
        and tl.client_id = l.client_id
        and tl.max_price = l.price
WHERE   l.lender_id = 1
        and l.claimed_date is null
        and l.disabled = 0
        and u.last_activity_date > '2012-01-13 02:42:10'
group by
        l.client_id

If there is more than one lead with the highest price, this version will return all of them.


Replication error after server name change I have changed the computer name of the server where SQL Server 2008 R2 is installed.

Old name was WIN-OUJEKRMGXXX
New server name is KASURDYYY

When I try to configure replication, the following error occurs:

  SQL Server Replication requires actual server name to make a connection to the server ... Specify the actual server name, WIN-OUJEKRMGXXX

I updated the server name in SQL Server in order to try to resolve the issue. I ran the following queries:

execute sp_dropserver 'WIN-OUJEKRMGXXX';
execute sp_addserver 'KASURDYYY', 'local';

...but the same error still occurs. I checked the server name :

select @@SERVERNAME -- returns WIN-OUJEKRMGXXX
select SERVERPROPERTY('SERVERNAME') -- returns KASURDYYY

sp_dropserver 'real-server-name' was giving an error about remote logins:

  There are still remote logins or linked logins for the server ...

Therefore, I executed the following query:

execute sp_dropremotelogin old_physical_server_name;

...but this gave the following error because of replication:

  There is no remote user '(null)' mapped to local user '(null)' from the remote server ...

Then I executed:

execute sp_removedbreplication 'database-name-on-which-publication-exist';
execute sp_dropdistributiondb 'distribution-database-name';

...still the same error. Finally I tried:

execute sp_dropdistributor @no_checks = 1, @ignore_distributor = 1;
execute sp_dropserver 'real-server-name';
execute sp_addserver 'nwe-server', 'local';

This resolved the issue.


Current size of a PostgreSQL table (vs disk space used) I have a table that was recently VACUUMed, which means the disk space it takes is higher than its actual size (what pg_total_relation_size might report). Is there a way to tell how much space it actually takes / how much free space I have "in the table"?

There are two extensions that may be helpful

pgstattuple provides various functions to obtain tuple-level statistics,
pg_freespacemap  provides a means for examining the free space map (FSM).


Best What's new book about just changes and new features in SQL Server 2012 I want to read about new changes and new features in SQL Server 2012.I found some E-Book in the internet but they have basic topics (like create Tables,Views,...) I want a book that just cover new features.Is there any book?

thanks

You could always read the what's new sections in the BOL. As for a book, have you looked at Microsoft's free book? A hard copy is available too, if you prefer. The book is not a coursebook but more general an approach. As per introduction:

  Who Should Not Read This Book

  As mentioned earlier, the purpose of this book is to provide the
  reader with a high-level preview of the capabilities and features
  associated with SQL Server 2012. This book is not intended to be a
  step- by-step comprehensive guide.


Is order by necessary after a group by clause? Suppose I have a simple query

SELECT col1, col2, COUNT(col3)
FROM tbl_name
WHERE condition
GROUP BY col1, col2
ORDER BY col1 ;

My understanding is group by clause seems to already have a sorting functionality. DB engine would sort col1 then col2 if adjacent rows of col1 happen to have same values. As least that is my experience with sql server.

If that is the case, order by clause here isn't really necessary?

My understanding is group by clause seems to already have a sorting
  functionality.

There are two basic approaches that SQL Server can use. A stream aggregate requires the data to be sorted by the group by keys. This can be either supplied by an index or might need an explicit sort. A stream aggregate is order preserving in that the rows output from that operator are in the same order as the input. This does not imply any guarantee about the eventual output from the query as a whole however. You only get that if you add an ORDER BY.

For a GROUP BY col1, col2 the input stream can be ordered by either col1, col2 or col2, col1 to be acceptable for the stream aggregate in this case. The addition of an index might change the decision of the optimiser as to which one to use.

The other basic approach is that of a hash aggregate. In which the grouping keys are hashed. This is not at all order preserving and will likely output rows in seemingly random orders.

Additionally for parallel plans there might be hybrid approaches, e.g. Each thread could have a local stream aggregate with the thread results then aggregated at global level with a hash aggregate.

Adding ORDER BY means that SQL Server will ensure that the rows are delivered in the desired order. If you are currently observing an output order of col1, col2 then likely you are getting a stream aggregate ordered by those two columns. The addition of an explicit order by won't change the execution plan to add any additional sort as SQL Server will recognize that the output of this aggregate is already in the desired order.


Should I stop transaction log backups during a full backup? I have two SQL Agent jobs which are scheduled to run at different intervals. The first job runs a full backup once a day. The second job runs transaction log backups every fifteen minutes.

As the database has grown, the full backup is taking longer than originally planned (even with compression). And I've noticed from my logs that the transaction log backups are now running at the same time.

Should I change the schedule of the transaction log backup so that doesn't run when the full backup is running? Does it matter?

As you can see in this table, there is no conflict between Full backup and Log backup. They can occur at same time, and, when log backup completes, log will not be cleaned until full backup (or diff backup) completes.

  Not all administrative tasks are allowed to run concurrently. In the table below, a black circle indicates two operations that cannot run in a database at the same time.

References:

Concurrent Administrative Operations


If replication is 600 seconds behind master will INSERT or UPDATE queries including `NOW()` be 600s different? If slave is behind master by 600 seconds, will queries including NOW() also be 600s different?

Example:

master|mysql> INSERT INTO my_table (hash,my_time) VALUES ('foo',NOW());
### 600s later, does slave get the same query?
 slave|mysql> INSERT INTO my_table (hash,my_time) VALUES ('foo',NOW());

Will the following produce the exact same result if run on master and slave?

master|mysql> SELECT my_time FROM my_table WHERE hash = 'foo'; #2016-04-21 09:49:42
 slave|mysql> SELECT my_time FROM my_table WHERE hash = 'foo'; #????-??-?? ??:??:??

Assume SHOW SLAVE STATUS\G shows Seconds_Behind_Master: 600, that master and slave are in the same datacenter (same timezone) and slave doesn't catch up to master until after the above is completed.

If the two are 600s different, what is the best practice to use instead of NOW()?

tl;dr No.

The binlogs contain a set timestamp command before every transaction exactly to prevent this situation. This will only affect the session scope of the SQL thread so running queries meanwhile won't report date 600 seconds in the past.

To take a look you can run:

$ sudo mysqlbinlog --base64-output=DECODE-ROWS binlog.XXXXX |head -30

#150613  9:06:38 server id 1  end_log_pos 478584 CRC32 0xd86fcd7b   Query   thread_id=88627 exec_time=0 error_code=0
SET TIMESTAMP=1434179198/*!*/;
BEGIN
...


How to correctly implement optimistic locking in MySQL How does one correctly implement optimistic locking in MySQL?

Our team has deduced that we must do #4 below or else there is a risk that another thread can update the same version of the record, but we'd like to validate that this is the best way to do it.

Create a version field on the table you want to use optimistic locking for e.g. column name = "version"
On selects, make sure to include the version column and make note of the version
On a subsequent update to the record, the update statement should issue "where version = X" where X is the version we received in #2 and set the version field during that update statement to X + 1
Perform a SELECT FOR UPDATE on the record we are going to update so that we serialize who can make changes to the record we are trying to update.

To clarify, we are trying to prevent two threads which select the same record in the same time window where they grab the same version of the record from overwriting each others if they were to try and update the record at the same time. We believe that unless we do #4, there is a chance, that if both threads enter their respective transactions at the same time (but have not issued their updates yet), when they go to update, the second thread that will use the UPDATE ... where version = X will be operating on old data.

Are we correct in thinking we must do this pessimistic locking when updating even though we are using version fields/optimistic locking?

Your developer is mistaken. You need either SELECT ... FOR UPDATE or row versioning, not both.

Try it and see. Open three MySQL sessions (A), (B) and (C) to the same database.

In (C) issue:

CREATE TABLE test(
    id integer PRIMARY KEY,
    data varchar(255) not null,
    version integer not null
);
INSERT INTO test(id,data,version) VALUES (1,'fred',0);
BEGIN;
LOCK TABLES test WRITE;

In both (A) and (B) issue an UPDATE that tests and sets the row version, changing the winner text in each so you can see which session is which:

-- In (A):

BEGIN;
UPDATE test SET data = 'winnerA',
            version = version + 1
WHERE id = 1 AND version = 0;

-- in (B):

BEGIN;
UPDATE test SET data = 'winnerB',
            version = version + 1
WHERE id = 1 AND version = 0;

Now in (C), UNLOCK TABLES; to release the lock.

(A) and (B) will race for the row lock. One of them will win and get the lock. The other will block on the lock. The winner who got the lock will proceed to change the row. Assuming (A) is the winner, you can now see the changed row (still uncommitted so not visible to other transactions) with a SELECT * FROM test WHERE id = 1.

Now COMMIT in the winner session, say (A).

(B) will get the lock and proceed with the update. However, the version no longer matches, so it will change no rows, as reported by the row count result. Only one UPDATE had any effect, and the client application can clearly see which UPDATE succeeded and which failed. No further locking is necessary.

See session logs at pastebin here. I used mysql --prompt="A> " etc to make it easy to tell the difference between sessions. I copied and pasted the output interleaved in time sequence, so it's not totally raw output and it's possible I could've made errors copying and pasting it. Test it yourself to see.

If you had not added a row version field, then you would need to SELECT ... FOR UPDATE to be able to reliably ensure ordering.

If you think about it, a SELECT ... FOR UPDATE is completely redundant if you're immediately doing an UPDATE without re-using data from the SELECT, or if you're using row versioning. The UPDATE will take a lock anyway. If someone else updates the row between your read and subsequent write, your version won't match anymore so your update will fail. That's how optimistic locking works.

The purpose of SELECT ... FOR UPDATE is:

To manage lock ordering to avoid deadlocks; and
To extend the span of a row lock for when you want to read data from a row, change it in the application, and write a new row that's based on the original one without having to use SERIALIZABLE isolation or row versioning.

You do not need to use both optimistic locking (row versioning) and SELECT ... FOR UPDATE. Use one or the other.


Visual Studio 2013 and up - Database Project Schema Compare File Is there any reason to save the schema compare file once you are done comparing, creating scripts or just simply updating?

Short answer, maybe. If being able to open a previously saved .scmp file makes life easier through time savings or consistency, I'd say it's worth saving.

With a schema compare window open, there is a cog wheel icon which opens the Schema Compare Options dialog. There are quite a few options under General and Object Types. Some are selected, others are not selected, by default. If you find yourself repeating compares that require changing the same default settings each time, that would be a compare worth saving.

The compare also saves source and target. If one found that when comparing one's local machine.MyDB to Development.MyDB, one didn't want to see differences in ObjectType X, but one did want to see those differences when comparing Development.MyDB to QA.MyDB, one could save two separate compares.

A .scmp file can also be added to source control, if one found value in doing that as well.

There very well could be other advantages I'm not aware of, but that's about the extent of my limited experience with database projects so far.


Is there a way to tell SQLCMD not to output rows to text or a file? I am testing the behavior of a SELECT statement that returns 170,000,000 rows using SQLCMD.  My goal is to cut out the time spent receiving the result-set on the client end (the time the process shows the ASYNC_NETWORK_IO waittype while an output file is being written to disk).  I would like to have SQLCMD run my SELECT statement but not actually save or output the result-set.  Just returning the number of rows affected would be fine.  Is this possible?

sqlcmd -E -t 0 -S server\instance -d databaseName -i hugeselect.sql -o none?

This isn't an answer for SQLCMD specifically (I don't know of an option to discard results), but an option for testing the performance of a query and ignoring the network overhead or client-side rendering is to use the free SentryOne Plan Explorer. By design we drop the results at the server and don't bring them across, allowing you to focus on the actual performance on the query rather than the performance of your network or the power of your desktop.


Can't get this query to work (left outer join) Postgres I've got this query I'd like to get working. I'm trying to count all the instances of a foreign key after a certain date that isn't null, and then left join that to the distinct of the original table, so I can see the variables with no entries. Here's my query:
select fulllist.fk_fc_id
from
(
    select distinct fk_fc_id
    from data_item
) as fulllist
left outer join
(
    select temp.fk_fc_id, count(*) as number
    from
    (
        select *
        from data_item
        where di_item_value not like 'Null'
            and di_timestamp > '2013-11-01 00:00:00'
    )   as temp
    group by fk_fc_id
)   as lj
    on lj.fc_fk_id=fulllist.fk_fc_id;

the error is

ERROR:  column lj.fc_fk_id does not exist
LINE 19:  on lj.fc_fk_id=fulllist.fk_fc_id;

It looks like this is just a typo.  Your subquery is returning fk_fc_id but your join is referencing fc_fk_id.  It seems like you just need to alter the query:

select fulllist.fk_fc_id
from
(
    select distinct fk_fc_id
    from data_item
) as fulllist
left outer join
(
    select temp.fk_fc_id, count(*) as number
    from
    (
        select *
        from data_item
        where di_item_value not like 'Null'
            and di_timestamp > '2013-11-01 00:00:00'
    )   as temp
    group by fk_fc_id
)   as lj
    on lj.fk_fc_id=fulllist.fk_fc_id;

It also seems like you would want to include the number in the final select:

select fulllist.fk_fc_id, coalesce(lj.number, 0) number
from
(
    select distinct fk_fc_id
    from data_item
) as fulllist
left outer join
(
    select temp.fk_fc_id, count(*) as number
    from
    (
        select *
        from data_item
        where di_item_value not like 'Null'
            and di_timestamp > '2013-11-01 00:00:00'
    )   as temp
    group by fk_fc_id
)   as lj
    on lj.fk_fc_id=fulllist.fk_fc_id;


Can't connect to PostgreSQL instance using Python I have installed vagrant and virtual box on my laptop to finish a class I am working on. I have created a database called tournament.  I can connect to it directly, create tables, insert data, etc.

However, I am trying to write a python script to connect to it and having great difficulty.

Here is my greatly simplified code:

import psycopg2

psycopg2.connect('dbname=tournament')

Here is what I am getting:

Any help would be greatly appreciated.  I am really frustrated.

Since you did not specify the host argument of che connect call, you are connecting via a unix named pipe instead of TCP/IP, so the error message might be slightly incorrect. BTW, the database should have opened a pipe named /var/run/postgresql/.s.PGSQL.5432. Can you check if it exists using command ls -la /var/run/postgresql/.s*? You might find there a pipe with a different number, or you might find and empty directory. In the first case, just add port=number (using the right number) to the connect call; in the latter case, find the correct directory for your postgresql and add host=path (using the right path) to the connect call.

In order to find the correct path, connect via psql and give command \conninfo. In will display the correct path, as in:

postgres=# \conninfo
You are connected to database "postgres" as user "postgres" via socket in "/var/run/postgresql" at port "5432".

P.S. It might also be a problem of the syntax you used. It should be psycopg2.connect(dbname="tournament")


Name for SELECT equivalent of UPSERT UPSERT means INSERT and\or UPDATE, is there a standard name for a stored procedure that will INSERT if necessary and then SELECT?

e.g.  I want to get the IS of a row that matches columns and give me the ID, if nothing matched then I want it to INSERT a new row and return me the ID of that row.

In PostgreSQL we have a name for it INSERT ... RETURNING.

You can use this form in a CTE to obtain the effect you want..

CREATE TABLE foo ( foo serial, bar int );

WITH t1 AS (
  SELECT x
  FROM generate_series(1,10) AS t(x)
),
t2 AS (
  INSERT INTO foo (bar)
  SELECT x
  FROM t1
  WHERE NOT EXISTS (
    SELECT *
    FROM foo
    WHERE foo.bar = x
  )
  RETURNING foo, bar
)
SELECT foo, bar
FROM foo
INNER JOIN t1 ON (foo.bar = t1.x)
UNION ALL
  SELECT *
  FROM t2
;

Here we

Generate test data in a cte t1 which we plan to INSERT or SELECT on.
With t2 we INSERT the data we don't already have into our desired table... foo, and return the results (that which was inserted) as t2
We run a SELECT to get all of the elements in our test data (t1) that already exist.
INSERT all of the others.


best way to consolidate data I have three select statements which have the bulk of their columns which are the same except a few. So here is an example

select
*orgid='0'
,'P' as OrgType*
, Lang
,*cast(Null as varchar) as BoardName
,cast(Null as varchar) as SchoolName*

,SUM(CASE WHEN IncludeColumn = 1 AND SecondInclude IN(5) THEN 1 ELSE 0 END) as Column1
,SUM(CASE WHEN IncludeColumn = 1 AND SecondInclude IN(6) THEN 1 ELSE 0 END) as Column2
,SUM(CASE WHEN IncludeColumn = 1 AND SecondInclude IN(4) THEN 1 ELSE 0 END) as Column3

FROM *TABLEA*
WHERE *[Include_All] = 1*
Group By *[Lang]*

select
orgid=boardname
,'B' as OrgType
, Lang
, BoardName
,cast(Null as varchar) as SchoolName

,SUM(CASE WHEN IncludeColumn = 1 AND SecondInclude IN(5) THEN 1 ELSE 0 END) as Column1
,SUM(CASE WHEN IncludeColumn = 1 AND SecondInclude IN(6) THEN 1 ELSE 0 END) as Column2
,SUM(CASE WHEN IncludeColumn = 1 AND SecondInclude IN(4) THEN 1 ELSE 0 END) as Column3
FROM TABLEB
WHERE [Include_Board] = 1
Group By [Lang], BoardName

select
orgid=SchoolName
,'S' as OrgType
, Lang
, BoardName
,SchoolName

,SUM(CASE WHEN IncludeColumn = 1 AND SecondInclude IN(5) THEN 1 ELSE 0 END) as Column1
,SUM(CASE WHEN IncludeColumn = 1 AND SecondInclude IN(6) THEN 1 ELSE 0 END) as Column2
,SUM(CASE WHEN IncludeColumn = 1 AND SecondInclude IN(4) THEN 1 ELSE 0 END) as Column3

FROM TABLEC
WHERE [Include_School] = 1
Group By [Lang], BoardName

So as you can see, the calculated columns are the same in each "grouping". The problem is that I have about 70 calculated fields (which are the same) in all three select statements. This makes reading the script horrible as i have to endlessly scroll.

The main changes are the first couple of columns in the select statement and then the way it groups by at the end.

Right now my current solution is to set strings and execute them. IE, I'll have

header1 = "SELECT FROM all..."
header2 = "SELECT FROM board.."
header3 = "SELECT FROM school.."
footer1 = "GROUP BY lang"
footer2 = "GROUP BY lang, board"
footer3 = "GROUP BY lang, school"
Common = CalculatedFields

EXECUTE (header1 + common + footer1)
EXECUTE (header2 + common + footer2)

but this just makes my script all red because its one large string. Not only that I have to break it up into different strings because I have a lot of text.

Any ideas on how to best do this while maintaining readability and clarity?

Not sure what your problem is... the three views solution sounds best.
If consistency is your problem with that then automate the creation of the views.
Use a function to return the common "logic"

Create function fn_GetStaticViewElement()
   return CONVERT(VARCHAR(MAX),',SUM(CASE WHEN IncludeColumn = 1 AND SecondInclude IN(5) THEN 1 ELSE 0 END) as Column1,SUM(CASE WHEN IncludeColumn = 1 AND SecondInclude IN(6) THEN 1 ELSE 0 END) as Column2,SUM(CASE WHEN IncludeColumn = 1 AND SecondInclude IN(4) THEN 1 ELSE 0 END) as Column3')

Create proc proc_GenerateViews

'ALTER VIEW vwALL
execSQL ('ALTER VIEW vwALL AS Select ... ' + fnGetStaticViewElement() + '...From...Where...')
'ALTER VIEW Board
execSQL ('ALTER VIEW vwBoard AS Select ... ' + fnGetStaticViewElement() + '...From... Where...')
'ALTER VIEW School
execSQL ('ALTER VIEW vwSchool AS Select ... ' + fnGetStaticViewElement() + '...From... Where...')

to have the data combined:
Create 1 huge view, union all the sources, combining all the possible columns.
De-normalised.
Expose sourcename into a column.
use that as part of your filter.
Should act like a partitioned view, I think.


Is it better to add an index and search via the index or add a unique constraint and let an insert fail? I have a table with over 2.5 million records in production that keeps track of if a specific user has already viewed an job:

mysql> DESCRIBE job_views;
+--------------------+--------------+------+-----+---------+----------------+
| Field              | Type         | Null | Key | Default | Extra          |
+--------------------+--------------+------+-----+---------+----------------+
| id                 | int(11)      | NO   | PRI | NULL    | auto_increment |
| job_id             | int(11)      | YES  |     | NULL    |                |
| session_id         | varchar(255) | YES  | MUL | NULL    |                |
| created_at         | datetime     | YES  |     | NULL    |                |
| updated_at         | datetime     | YES  |     | NULL    |                |
+--------------------+--------------+------+-----+---------+----------------+

Before adding a row to the table, I want to ensure that there isn't already a row with the same job_id and session_id. Currently the application queries the table for a row that matches (presently without an index, although an index on the two columns has been added), but I believe a unique constraint makes more sense.

Is there a difference between a SELECT using the index and then an INSERT if a row isn't found versus an INSERT that fails because of a unique constraint? If so, which is preferable (or how can I make that determination)?

Use a unique constraint (unique index), this is exactly what they're for.

The difference will be in your code. If you use the unique constraint method, you need to catch the error that the dbms will throw on duplicate entries, but if you check for the value explicitly first, you won't need to catch an error.

However, if you don't use the unique constraint method, you're not really restricting the duplication of the data at the database level, allowing the data to be corrupted easily by another application or even a maintenance script being run by a careless developer or dba.


Always On - Restore a node from a Veeam backup I have a cluster with 2 nodes, and Veeam is backing up databases every day and logs every 5 minutes.  This works great and I can easily recover a single database to any point in time.

Then I was wondering what will happen if I completely lose a node. Let's say, worst case scenario, OS is gone. The AG will handle the failover and databases will still be accessible.

How can I restore the lost node?

I went into testing. To avoid a full reinstall of the node I took a backup of the VM. But obviously databases contained in the backup are older than those on the primary node of the cluster. So when I tried this I end up being stuck with databases out of sync.

First thing I did after the backup was restored was to restart the machine to kill. Still out of sync. As far as I understand, the backup I used was not a SQL database backup, and databases were in use (not that good) and the second error the databases on the primary and the broken secondary took different paths.

I found a really inefficient way to do it: remove databases from the AG, delete them on the secondary, then re-add them to the AG and let the secondary node sync.

Is it possible to resync databases more efficiently? Particularly, without losing the primary (meaning no restoration on the primary)?

It does not look like WITH NORECOVERY functionality will be included with Veeam until a future update, per this thread found over at the official Veeam forums.  It looks like you'll need to use native features (as you've already identified) until then to get your AG back in sync.