Problem in Fetching Result set in Desired Format I have 2 tables having the following columns

Person
------
person_id,
first_name,
middle_name,
last_name

Items
-----
person_item_id,
person_id,
item_type,
status,
ordered_date

The query is

select
    Person.person_id as PID,
    Group_concat(Items.item_type) AS ITYPE,
    Group_concat(Items.status) AS STATUS,
    Group_concat(Items.orderd_date)AS ODATE,
    Group_concat(Items.person_item_id) AS IID
from
    Persons inner join
    Items ON Person.person_id = Items.person_id
group by person_id;

The resultset returned is as follows

PID ITYPE      STATUS                ODATE                     IID
1   A,A,B,C    Y,N,Y,Y         2/5/2012,5/5/2012,17/5/2012     1,1,2
2   A,B        Y,N             5/5/2012,15/6/2012              1,2

One Person can have many item_type and can order same item many times,I want to show the record set in such a manner that no matter the item is purchased by person or not , if the item is present in the record set i am drawing a column and have to show the result corresponding to that as such if the item is purchase 3 times then i want to show the item and it's corresponding record i.e order_date,status etc in sequential manner.

For eg :-

If there are 3 items A, B,C then output should be shown on the screen as :-

PID           A           B                 C

1             Y           N                 Y
          2/5/2012      5/5/2012         7/5/2012
         -----------
              Y
          17/5/2012

2             Y           N
          12/6/2012    15/6/2012

Now Suppose there is another item so what i am doing is first fetching the result set from above query and then traversing through that result set to check the existence of that item & if that item is present i am showing it & it's corresponding values , so 1st i have to traverse the whole result set to get the item_type and then have to show it .

I think you need a third table, orders,  which connects the items and persons.

Tables

CREATE TABLE IF NOT EXISTS `persons` (
  `person_id` int(4) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  PRIMARY KEY (`person_id`)
);

CREATE TABLE IF NOT EXISTS `items` (
  `item_id` int(4) NOT NULL AUTO_INCREMENT,
  `item_type` varchar(50) NOT NULL,
  PRIMARY KEY (`item_id`)
);

CREATE TABLE IF NOT EXISTS `orders` (
  `order_id` int(12) NOT NULL AUTO_INCREMENT,
  `item_id` int(4) NOT NULL,
  `person_id` int(4) NOT NULL,
  `status` varchar(50) NOT NULL,
  `ordered_date` date NOT NULL,
  PRIMARY KEY (`order_id`)
);

Data

INSERT INTO `persons` (`name`) VALUES
('Walter White'),
('John Locke');

INSERT INTO `items` (`item_type`) VALUES
('A'),
('B'),
('C');

INSERT INTO `orders` (`person_id`, `item_id`, `status`, `ordered_date`) VALUES
(1, 1, 'Y', '2012-5-2'),
(1, 2, 'N', '2012-5-5'),
(1, 3, 'Y', '2012-5-7'),
(1, 1, 'Y', '2012-5-17'),
(2, 1, 'Y', '2012-6-12'),
(2, 2, 'N', '2012-6-15');

Query

SELECT orders.person_id AS PID, GROUP_CONCAT(items.item_type) AS ITYPE, GROUP_CONCAT(status), GROUP_CONCAT(ordered_date)
FROM orders
INNER JOIN items ON
items.item_id = orders.item_id
GROUP BY person_id, orders.item_id

Result

| PID | ITYPE | GROUP_CONCAT(STATUS) | GROUP_CONCAT(ORDERED_DATE) |
-------------------------------------------------------------------
|   1 |   A,A |                  Y,Y |      2012-05-02,2012-05-17 |
|   1 |     B |                    N |                 2012-05-05 |
|   1 |     C |                    Y |                 2012-05-07 |
|   2 |     A |                    Y |                 2012-06-12 |
|   2 |     B |                    N |                 2012-06-15 |

SQL Fiddle


Table statistics on database Is there any way that I could find how many tables has statistics on them,and how many tables which doesn't have statistics on them.

All the information regarding statistics gathered with dbms_stats can be found on dba_tab_stats_history.

Ref.: http://docs.oracle.com/cd/B19306_01/server.102/b14237/statviews_2103.htm#BGBBBGCC


Use array_position() function to get the most frequent value for column from pg_stats I'm trying to execute this simple query in order to check whether some value (1000) belongs to MCV list used by Postgres query optimizer:

SELECT array_position(most_common_vals, 1000)
FROM pg_stats
WHERE tablename = 'tenk1'
AND attname = 'unique1';

But receive the following error message:

ERROR:  function array_position(anyarray, integer) does not exist

How can it be fixed?

array_position() is the standard function as described here and the following statement returns 2 as expected:

SELECT array_position('{1,2,3}', 2);

Solution

Assuming the data type of your column tenk1.unique1 is integer:

SELECT array_position(most_common_vals::text::int[], 1000)
FROM   pg_stats
WHERE  tablename = 'tenk1'
AND    attname = 'unique1';

Use your actual column type and the corresponding array type.
You get the position, or NULL if the value is not in the MCV list.

The solution is short - unlike the ...

Explanation

The function array_position() is defined to take (anyarray, anyelement) (or (anyarray, anyelement, integer) for the second variant).

The column pg_stats.most_common_vals has the polymorphic data type anyarray to be able to hold arrays of any data type - for obvious reasons.

anyarray and anyelement are not allowed as data types for user-created tables. For users, both are polymorphic pseudo-types. (But Postgres can use them in system tables.)

Multiple polymorphic variables in the same function have to resolve to the same (or corresponding) data types. The manual:

  Furthermore, if there are positions declared anyarray and others
  declared anyelement, the actual array type in the anyarray positions
  must be an array whose elements are the same type appearing in the
  anyelement positions.

And:

  Thus, when more than one argument position is declared with a
  polymorphic type, the net effect is that only certain combinations of
  actual argument types are allowed.

Bold emphasis mine.

You have found a corner case where function type resolution fails for the combination of the polymorphic anyarray with an integer - or any non-polymorphic type in second position.

1000 in your expression array_position(most_common_vals, 1000) is a numeric constant resolving to integer. These would fail in similar fashion:

array_position(most_common_vals, '1000')  -- untyped string literal

ERROR:  function array_position(anyarray, unknown) does not exist

array_position(most_common_vals, '1000'::text)

ERROR:  function array_position(anyarray, text) does not exist

Furthermore, there are no casts defined for anyarray, being a pseudo-type in user-land:

SELECT * FROM pg_cast WHERE castsource = 'anyarray'::regtype;  -- nothing found

The workaround is to cast to text as stepping stone, since any type can be cast to text. Then cast to integer[], arriving at the solution above.

In closing, I think this is a shortcoming in function type resolution that could be resolved (easily?). But since the data type anyarray is not supposed to be used like this in user-land to begin with I doubt that any developer will spend time on it ...


MongoDB Reverse SSH Replica Set Config Fails when leader changes We have a MongoDB replica set with 3 machines outside our network and one inside network. The inside one is connected to leader by reverse SSH tunnel such as :

ssh -R 37017:inside_machine:37017 user@leadermachine

Everything works well if the leader doesn't change. When it does, we need to renew the tunnel and hosts entry for the new leader (host entry should resolve the same ip as the leader) but replica set configs don't match anymore,

InvalidReplicaSetConfig: Our replica set configuration is invalid or does not include us

And we need to remove the inside machine, drop local db and readd to replica set. This means the inside machine drops everything and starts to replicate from the very beginning.

While this is not much of a problem for litte db's, ours are large and replication may as well take 2 days.

Are we doing something wrong or is there anything we can do to prevent this?

Thanks a lot.

You should use TLS/SSL for connections between those machines. Then you don't need to play around with ssh. With SSL connections between nodes are encrypted and IP addresses don't change.


Space at the end of string I have a SQL code like shown below:

declare @str nvarchar(max),@i int
set @i=0
set @str='abc '
declare @tbl Table(a  nvarchar(max))
insert @tbl select @str
while (select a from @tbl)<>''
begin
    set @i=@i+1
    set @str = substring(@str,2,len(@str))
    update  @tbl set a=@str
select * from @tbl
end

Here @str has value 'abc ' (there is a space at the end). When above query is executed it will stop when only a space is present in 'a'.
Also output of this query is:

bc
c
<here blank>

For the above query if I give input @str as 'abcd'
then output will be

bcd
cd
d
<here blank>

So in the first case that is @str='abc ' I want to get output like

bc
c
<here blank>
<here blank>

Now the code is checking for space and because of that I am having problem. But I want it to consider the space at the end also.

Same is the problem in SQL for len() also. Both len('a') and len('a ') will return 1.
So if anyone please help on my query so that it will give my desired output.

When checking if a string is empty or not, append a non-space character to it and compare the result to the same character. This

@string + '.' <> '.'

will evaluate to TRUE only when the @string is truly empty. The @string's trailing spaces will count (because they will no longer be trailing after appending the .).

And you can go similarly about finding the length. This

LEN(@string + '.')

will only ever evaluate to 1 if the @string is '', not when it's ' ' or '     ' or anything of the kind.

By the way, when you want the SUBSTRING function to return a substring from a certain position to the end of the string, you can specify any fairly large number as the length parameter, no need to use LEN(). Very often something like

SUBSTRING(@string, 2, 999999999)

would do.


How to get overall rank based on data regardless of scope (all items, user specific, etc.)? Table schemas:

servers
- id
- user_id
- game_id
- slug
  ...

server_votes
- id
- server_id
- ip_address
- created_at
  ...

So, I have a two tables: servers and server_votes, and I want to get the rank of each server based on how many votes each server has received in a certain amount of time. I have created the following query to do just that (apart from the ranking part):

SELECT
    s.id,
    s.user_id,
    s.slug,
    (
        SELECT
            COUNT(sv.id)
        FROM
            server_votes AS sv
        WHERE
            DATE(sv.created_at) > '2016-01-24 19:11:15'
        AND
            sv.server_id = s.id
    ) AS votes
FROM
    servers AS s
LEFT JOIN
    server_votes AS sv
ON
    sv.server_id = s.id
WHERE
    s.game_id = 1
GROUP BY
    s.id
ORDER BY
    votes DESC,
    s.id ASC

The query above will return the following:

*----*---------*---------*-------*
| id | user_id | slug    | votes |
*----*---------*---------*-------*
| 4  | 1       | Server4 | 3     |
| 2  | 1       | Server2 | 2     |
| 1  | 1       | Server1 | 0     |
| 3  | 2       | Server3 | 0     |
| 5  | 2       | Server5 | 0     |
*----*---------*---------*-------*

I'd like to end up with the following:

*----*---------*---------*-------*------*
| id | user_id | slug    | votes | rank |
*----*---------*---------*-------*------*
| 4  | 1       | Server4 | 3     | 1    |
| 2  | 1       | Server2 | 2     | 2    |
| 1  | 1       | Server1 | 0     | 3    |
| 3  | 2       | Server3 | 0     | 4    |
| 5  | 2       | Server5 | 0     | 5    |
*----*---------*---------*-------*------*

Or if I'm querying a specific user's server's, I'd get the rank in context of all servers:

*----*---------*---------*-------*------*
| id | user_id | slug    | votes | rank |
*----*---------*---------*-------*------*
| 3  | 2       | Server3 | 0     | 4    |
| 5  | 2       | Server5 | 0     | 5    |
*----*---------*---------*-------*------*

Any insight would be appreciated.

Also, here's the SQL Fiddle incase that is of any help to you.

This query uses a variable for ranking:

SELECT t.id,
    t.user_id,
    t.slug,
    t.votes,
    @rank := @rank + 1 AS rank
    FROM (
    SELECT
        s.id,
        s.user_id,
        s.slug,
        (
            SELECT
                COUNT(sv.id)
            FROM
                server_votes AS sv
            WHERE
                DATE(sv.created_at) > '2016-01-24 19:11:15'
            AND
                sv.server_id = s.id
        ) AS votes
    FROM
        servers AS s
    LEFT JOIN
        server_votes AS sv
    ON
        sv.server_id = s.id
    WHERE
        s.game_id = 1
    GROUP BY s.id
) t, (SELECT @rank := 0) r
ORDER BY
    t.votes DESC,
    t.id ASC

See SQL Fiddle.

Output:

id  user_id slug    votes   rank
4   1       Server4 3       1
2   1       Server2 2       2
1   1       Server1 0       3
3   2       Server3 0       4
5   2       Server5 0       5

Once the above query is put in a subquery with a WHERE clause, it outputs data for a specific user:

SELECT
    r.id,
    r.user_id,
    r.slug,
    r.votes,
    r.rank
FROM (
   ...
) r
WHERE r.user_id = 2;

It will output id 3 and 5.

See SQL Fiddle


Rebuilding fragmented indexes on SQL Server We've got a SQL Server database powering our SAAS web app. It's a SQL Azure database, hosted on S3 Standard plan (100 DTU units - pretty suficient for what we need).

Our DB indexes fragment really quickly, to the point that after 3-4 days, a lot of our more used tables are usually over 40% fragmented. If left, after 2 weeks a lot would be ~90% fragemented. In our most used tables, we've got about 2 million rows.

So, to sort this, we've got a script that runs every 3 days, looking for indexes that are >20% fragmented, and we rebuild these (with online on).

The problem with that is while it's running, the app becomes very slow and unresponsive for about 2 hours while it does it's thing.

Is there a better strategy to deal with this fragmentation of indexes, or anything else I could look into to try and make the fragmentation less frequent?

Thanks.

If the indexes are fragmenting that quickly due to massive amounts of activity affecting arbitrary user-provided values then they may be little you can do.

If the indexes that are fragmenting badly are over UNIQUEIDENTIFIER columns then these will fragment quickly when they see a lot of activity because the data is effectively random. If this is the case then you can minimize this greatly by using NEWSEQUENTIALID() instead of NEWID(), or the equivalent in your application if you are generating UUIDs there instead of in the database.

If you are generating UUIDs outside SQL Server make sure that they are generated in a way that is sequential in SQL Server's sort order (different generators use different byte orderings) otherwise you lose some or all of the benefit. See this for reference to .Net's UuidCreateSequential not producing UUIDs with the same ordering properties to SQL Server's NEWSEQUENTIALID() and how to juggle the result to work.


How do I select row which has a max value in other column - PostgreSQL? I'm just starting with PostgreSQL and I bet there is a simple answer for my qs.
I have a view like:
 county - candidate - votes
Abbeville - John Kasich - 157
Abbeville - Ben Carson - 305
Abbeville - Ted Cruz - 876
Abbeville - Jeb Bush - 236
Abbeville - Hillary Clinton - 1501
Abbeville - Marco Rubio - 740
Abbeville - Bernie Sanders - 312
Abbeville - Donald Trump - 1353
Abbot - Hillary Clinton - 0
Abbot - Bernie Sanders - 1
Abington - Ben Carson - 53
Abington - John Kasich - 299
Abington - Bernie Sanders - 1352

And I would like to write a query which returns who has most votes per county like:
    county - candidate - votes
    Abbeville - Hillary Clinton - 1501
    Abbot - Bernie Sanders - 1
    Abington - Bernie Sanders - 1352

I tried this query but cannot return right candidate:
select
distinct on (county) county , candidate ,max(votes) over(partition by county)
from primary_results_csv prc
group by county, candidate, votes
order by county

Thanks in advance for help.

You were close, but you don't need the group by or the max(votes) window function when using distinct on ()
select distinct on (county) *
from primary_results_csv
order by county, votes desc;

The equivalent solution using window functions would be:
select county, candidate, votes
from (
   select *, max(votes) over (partition by county) as max_votes
   from primary_results_csv
) t
where votes = max_votes;

Online example


postgresql replace table without losing dependancies i want to replace an existing table with a new one, without losing foreign keys or inheritance relations. But I feel lazy to alter it to make it look like the new one. Is there a way to replace the table without doing all the hard job? Like a CREATE OR REPLACE statement in SQLITE or by tweaking the table name in pg_class table?

CREATE TABLE a (
  id serial NOT NULL,
  -- a bunch of other columns
  CONSTRAINT a_pk PRIMARY KEY(a)
);

CREATE TABLE b (
  id serial NOT NULL,
  a_id bigint refernces a(id)
);

--REPLACE a with a new table without violating b FOREIGN KEY relations(somehow)
CREATE OR REPLACE a(
  id serial NOT NULL,
  -- a bunch of new columns
  CONSTRAINT a_pk PRIMARY KEY(a)
);

Disable any FKs pointing to the table
Create table SAVE as SELECT * FROM A
Truncate table A
Begin to create a script with the following

with xfer as (select cast ('a' as text) as targ_tab, cast ('b' as text) as from_tab)
select xfer.*, tabs.table_name as targ_table_name, cols.table_name as from_table_name,
  'alter table ' || tabs.table_name || ' add (' ||
  coalesce(cols.column_name, '') || ' ' || coalesce (cols.data_type, '')
  ||
      case when data_type = 'numeric' and coalesce (cols.numeric_precision,
          cols.numeric_scale) is not null then
          '(' || coalesce ('' || cols.numeric_precision, '*') || ', ' ||
          coalesce (cols.numeric_scale, 0) || ')'
      else ''
      end
  || ')'
  as synt
from xfer join
  information_schema.tables tabs on (xfer.targ_tab = tabs.table_name)
  join
  information_schema.columns cols on (xfer.from_tab = cols.table_name)
where not exists (
select 1
from information_schema.columns targcols
where targcols.table_name = xfer.targ_tab
and targcols.column_name = cols.column_name
)
order by ordinal_position

Repopulate with INSERT/SELECT
Re-enable foreign keys


How can I make "phraseto_tsquery" as fast as "plainto_tsquery" in PostgreSQL Some background:

I have a table, documents with over 200,000 rows that has a column body, which can be very long. I put an index on this table:

CREATE INDEX documents_body_tsvector_index ON documents USING GIN (to_tsvector('english', body));

With this index, my queries that include WHERE clauses like to_tsvector('english', body) @@ to_tsquery('english', 'awesome') are blazing fast, which is great.

The problem:

While plainto_tsquery('english', 'awesome website') runs fast, phraseto_tsquery('english', 'awesome website') runs extremely slow. (I've also triedto_tsquery('english', 'awesome <-> website') as well, and that is also extremely slow.)

The question:

How can I speed this up? Am I doing something wrong with my index? I really need to take position into account—using phraseto_tsquery seems like the best way to do it. Any help would be much appreciated.

(Edit) The EXPLAIN:

Here's the fast query:

EXPLAIN (ANALYZE, BUFFERS) SELECT COUNT(*)
FROM documents
WHERE to_tsvector('english', body) @@ plainto_tsquery('english', 'termone termtwo');

Aggregate  (cost=57.16..57.17 rows=1 width=8) (actual time=4.386..4.387 rows=1 loops=1)
  Buffers: shared hit=2949
    ->  Bitmap Heap Scan on documents  (cost=36.04..57.15 rows=5 width=0) (actual time=1.609..4.096 rows=3053 loops=1)
      Recheck Cond: (to_tsvector('english'::regconfig, text) @@ '''termone'' & ''termtwo'''::tsquery)
        Heap Blocks: exact=2907
        Buffers: shared hit=2949
          ->  Bitmap Index Scan on documents_text_tsvector_index  (cost=0.00..36.04 rows=5 width=0) (actual time=1.265..1.265 rows=3053 loops=1)
            Index Cond: (to_tsvector('english'::regconfig, body) @@ '''termone'' & ''termtwo'''::tsquery)
            Buffers: shared hit=42
Planning time: 0.094 ms
Execution time: 4.417 ms
(11 rows)

Here's the slow query:

EXPLAIN (ANALYZE, BUFFERS) SELECT COUNT(*)
FROM documents
WHERE to_tsvector('english', body) @@ phraseto_tsquery('english', 'termone termtwo');

Aggregate  (cost=57.16..57.17 rows=1 width=8) (actual time=120252.866..120252.866 rows=1 loops=1)
  Buffers: shared hit=38792
    ->  Bitmap Heap Scan on documents  (cost=36.04..57.15 rows=5 width=0) (actual time=3.065..120250.231 rows=1750 loops=1)
      Recheck Cond: (to_tsvector('english'::regconfig, body) @@ '''termone'' <-> ''termtwo'''::tsquery)
        Rows Removed by Index Recheck: 1303
        Heap Blocks: exact=2907
        Buffers: shared hit=38792
          ->  Bitmap Index Scan on documents_text_tsvector_index  (cost=0.00..36.04 rows=5 width=0) (actual time=1.491..1.491 rows=3053 loops=1)
            Index Cond: (to_tsvector('english'::regconfig, body) @@ '''termone'' <-> ''termtwo'''::tsquery)
            Buffers: shared hit=42
Planning time: 0.540 ms
Execution time: 120252.938 ms
(12 rows)

The phrase search has to inspect every document which contains both 'awesom' & 'websit' to make sure the tokens occur in the correct order and proximity, because the order and proximity data is not present in the index.  Reparsing, tokenizing, stemming, etc. those documents is slow.  Also, since a large document is stored out-of-band, it has to read that large data in from a separate TOAST table before it can do any of that.  Without the the word order and proximity constraint, it doesn't have to do any recheck at all, as in this case the index only returns exact results, with no possible false positives (as long as work_mem is large enough).

Rather than using a functional index, you can store the tsvector in the table and keep that column populated (for example, a trigger on row insert and on update of body which sets tsv = to_tsvector('english', body)). Then you would build the index directly on "tsv" column, and write the query directly against that column:

ALTER TABLE documents add column tsv tsvector;
update documents set tsv = to_tsvector('english', body);
CREATE INDEX documents_tsv_tsvector_index ON documents USING GIN (tsv);

SELECT...WHERE tsv @@ phraseto_tsquery('english', 'awesome website')

Here, it still needs to recheck the "tsv" for order and proximity, which means it needs to read that large column out of disk and back into memory, but it doesn't need to reparse it as it is already stored in tokenized form designed for efficiency.  How much of an improvement this makes will depend on whether your bottleneck is the IO for reading the documents back into memory, or the CPU for doing the parsing.

Of course that does mean you will be storing both "body" and "tsv" in the table, which will expand its size considerably, but that might be a low cost to pay for the improved performance.  If all you want to do is COUNT(*), then you could even store only the tsvector, and don't store the raw "body" at all.


SSAS and Visual Studio Optimization Wizard does not work We have setup and logged data on our Production Server, but when we try to run the Optimizations from within Visual Studio it crashes immediately with

TITLE: Microsoft Visual Studio
------------------------------

The system cannot locate the object specified.

------------------------------
BUTTONS:

OK
------------------------------

The tables all exist, right where they should, with DBO as the owner.  The logged in user has the rights to read the data.

The extra information on the error says:

Program Location:

   at EnvDTE.IVsExtensibility.RunWizardFile(String bstrWizFilename, Int32 hwndOwner, Object[]& vContextParams)
   at Microsoft.DataWarehouse.VsIntegration.Shell.Project.FileProjectHierarchy.RunAddNewItemWizard(String pszItemName, Int32 cFilesToOpen, String[] rgpszFilesToOpen, IntPtr hwndDlg)
   at Microsoft.DataWarehouse.VsIntegration.Shell.Project.FileProjectHierarchy.RunWizard(String wizardName, String newItemName)
   at Microsoft.DataWarehouse.DataWarehouseUtilities.RunWizard(String wizardName, IServiceProvider serviceProvider, Object[] stackParameters)
   at Microsoft.AnalysisServices.Design.AnalysisServicesUtilities.InvokeUsageBasedOptimizationWizard(MeasureGroup measureGroup, Partition[] selectedPartitions)
   at Microsoft.AnalysisServices.Design.Aggregations.AggregationsEditor.OnUBO(MenuCommand menuCommand, CommandHandlingArgs args)

The OLAP tables have the default names of dbo.OlapQueryLog and there is recent data in it.  Im not sure what else I need to do to make this activate, but I never get as far as the wizard window.

Any ideas?

The answer isn't obvious.

You need BIDS Helper installed to your visual studio.

There are four buttons in the PARTITIONS tab one of which is the "Edit Aggregations" button.  If it isn't there, you dont have the latest BIDS helper.

An aggregation Manager window will appear, with a treeview of your measures structure.  Drill down to the Aggregations Design folder that appears in that tree, and right click it, or any of its children.  Click the "Add Aggregations from Query Log" menu item, and you will be given a window where you can even edit the SQL that selects your Query log item.

This will allow you to achieve exactly the same thing that the Aggregations tab currently isn't.

For the record, the Aggregations tab issue remains, but is irrelevant as this method does everything we need.


Conformed Fields or Snowflake? - Shop and Customer Location I have shop dimension and customer dimension which both has location information.

Should I create location dimension to correlate shop location and customer location? I believe this one is called snowflake schema. I heard that it is hard to maintain.

|----------|       |---------|       |----------|
| Dim_Cust | ----- | Dim_Loc | ----- | Dim_Shop |
|----------|       |---------|       |----------|

Or, should I maintain definition that shop and customer dimension is conformed by each location field? For this one, I denormalized location information to each dimension.

|----------|       |----------|
| Dim_Cust | ----- | Dim_Shop |
|----------|       |----------|

This is more of an outrigger dimension. You area allowed to do this with the Kimball methodology (star schema, not snowflake) but it does add complexity to your data model (happens with date dimensions often when you have a date in another dimension).

I would simply add location information to both the shop and customer dimensions. If you want to do complex querying around which shops are visited by which customers, you could create a denormalised customer/shops dimension which has every used combination of customer and shop. This will make it much easier to query - which is what a good data warehouse will provide. Hope this helps.

Here's some stuff on outrigger dimensions.
http://www.kimballgroup.com/data-warehouse-business-intelligence-resources/kimball-techniques/dimensional-modeling-techniques/outrigger-dimension/


How to use MySQL stored function argument to refer to column name? How can I use a function argument s to stand in for a column name in a query?  I'm using mysql 5.5.  I tried

SELECT count_distinct(last_name)

but it returns

ERROR 1054 (42S22): Unknown column 'last_name' in 'field list'

The function was defined as

CREATE FUNCTION count_distinct (s CHAR(20) )
RETURNS INT
READS SQL DATA
RETURN( SELECT COUNT(DISTINCT s) FROM president);

I expected it to return a value of 37 as

SELECT COUNT(DISTINCT last_name) FROM president

returns 37.
The 'president' table is from the sampdb database from MySQL by Paul Dubois.

With thanks to Colin 't Hart this worked:

DELIMITER $
CREATE PROCEDURE showUnique( IN col CHAR(64))
BEGIN
SET @s=CONCAT('SELECT COUNT(DISTINCT ',col,') FROM president);
PREPARE stmt from @s;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END $
delimiter ;
CALL showUnique(last_name)


Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON' for SSIS I have a SSIS package which is perform data migration between MySQL database and SQL server database. When I manually run it from  visual studio or from SQL Server Management Studio, it ran successfully. But when i schedule it using SQL server agent job, an error occur.

  Description: Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.  Source: .Net SqlClient Data Provider

What is this error? Why this happen only when scheduled? How to overcome this problem?

You could be dealing with a delegation/impersonation problem as DimUser suggested.

If your SSIS is just fetching data from one DB server and delivers it to the other the solution is much easier and should be to set up an SQL Server Agent Proxy.

If you are executing the SSIS Package from Visual Studio or SSMS by hand the package will try your Windows credentials to log on to the SQL Server. If your account has the correct rights it will succeed.

If you set up an SQL Server Agent Job to execute that package the service account running SQL Server Agent executes this package. He does not have your Windows credentials so he will try to use anonymous login resulting in this error message.

To enable SQL Server Agent to execute a package using a different account you have to set up three things:

Create a credential
Set up a proxy account
Configure the SQL Server Agent step to use the proxy account

Create a credential
Connect to the SQL Server that should execute your package. Right-click Security and select "New Credential...". Enter a descriptive name (Credential name), select the Windows account you intend to use (Identity) and enter the password. Click OK.

For initial testing, you can use your own Windows account. For production use, I would suggest creating a dedicated AD Service Account (with minimal permissions).

Set up a proxy account
Expand the SQL Server Agent folder. Open Proxies and right click on "SSIS Package Execution" selecting "New Proxy...". Enter a descriptive proxy name and use the credential you created earlier.

Configure the SQL Server Agent step to use the proxy account
Open your Agent Job, select the properties of your step executing the SSIS Package. Now you can select your proxy account in the "Run as:" drop-down list.

Additional setup:
If your package is deployed to the SSIS Catalog you need to grant the Windows login you used for the credential the "SSIS_Admin" role on SSISDB as well.

For that, you need to create the account as a regular Windows login in SQL Server (Public) and map the user to SSISDB using the SSIS_Admin role.


Is there an Oracle function to check for valid Japanese characters (Non-ASCII)? Is there an Oracle function that can recognize if a character is readable or not?

I would like to search a specific column for garbled characters. I know that this can typically be done with a regular expression. However, my database contains Japanese characters, so it is not just a simple matter of an [A-Z][0-9] pattern.

I think the answer given the specifics is "no there is not."  I would think, however, in Oracle you could use a regular expression perhaps to find fields with characters not in a specific set.  See http://docs.oracle.com/cd/B19306_01/appdev.102/b14251/adfns_regexp.htm

I don't know how regex usage works with indexes on Oracle however.


limiting the rows to where the sum a column equals a certain value i m trying to limit the rows to where the sum a column equals a certain value but i m getting unexpected results.

CREATE TABLE buy (
  id int not null,
  volume decimal(9,4) not null,
  price decimal(9,2) not null
 );

insert buy (id, volume, price) values
(1, 1.0000, 14250.00),
(2, 0.2500, 14251.10),
(3, 0.2500, 14250.00),
(4, 0.0050, 14200.00),
(5, 0.5000, 14255.01);

expected result selected ids 5,2,1(because ordering by higest price) but it selects ids 2,1,3

SELECT t.id, t.volume, t.price,
(SELECT SUM(volume) FROM buy WHERE id <= t.id) 'RequiredVolume'
FROM buy t
HAVING RequiredVolume <= '1.5000'
ORDER BY price DESC, id ASC;

| id | volume |   price | RequiredVolume |
|----|--------|---------|----------------|
|  2 |   0.25 | 14251.1 |           1.25 |
|  1 |      1 |   14250 |              1 |
|  3 |   0.25 |   14250 |            1.5 |

http://sqlfiddle.com/#!9/ef024/3/0

I think the problem could be due to the nested query being >= and not =.

This gives the output you described:

SELECT t.id, t.volume, t.price,
(SELECT SUM(volume) FROM buy WHERE id = t.id) 'RequiredVolume'
FROM buy t
HAVING RequiredVolume <= '1.5000'
ORDER BY price DESC, id ASC;

Memo - Can you please post what your expected results would be for all fields and not just the ID ordering.


Is it possible to monitor role and privilege grant/revoke using triggers? Is it possible to monitor role and privilege grant/revoke using triggers? I' aware of doing that using Oracle audit tools however, it's interesting is it possible to do that using triggers.

You could do that with DDL triggers ([before|after] grant or revoke).

The docs are in the Oracle® Database PL/SQL Language Reference. You should look carefully at the event attribute functions table, and the Event Attribute Functions for Client Event Triggers section to know what information is available in those contexts.

Here's an example trigger (probably incomplete, this is just an example) that would log basic information about grant and revoke events, assuming a log table with appropriate columns:

create or replace trigger trigger1 after grant or revoke on database
  declare
    priv  dbms_standard.ora_name_list_t;
    who   dbms_standard.ora_name_list_t;
    npriv pls_integer;
    nwho  pls_integer;
  begin
    npriv := ora_privilege_list(priv);
    if (ora_sysevent = 'GRANT') then
      nwho := ora_grantee(who);
    else
      nwho := ora_revokee(who);
    end if;
    for i in 1..npriv
    loop
      for j in 1..nwho
      loop
        insert into log values
          (
            systimestamp,
            ora_sysevent,
            who(j),
            priv(i),
            ora_dict_obj_owner,
            ora_dict_obj_name
          );
      end loop;
    end loop;
  end;

Example:

SQL> grant select on log to bar;
Grant succeeded.

SQL> revoke select on log from bar;
Revoke succeeded.

SQL> select * from log;
DT                           WHAT   WHO PRIV   OWN OBJ
25-NOV-12 05.29.19.095403 PM GRANT  BAR SELECT MAT LOG
25-NOV-12 05.29.27.004610 PM REVOKE BAR SELECT MAT LOG


Help with optimising a sql query on two partitioned tables I have a SQL query that I am running on two very large tables in SQL Server 2012 Enterprise.

The two tables are ACKs and Logs. Both are partitioned on their time by day column with the appropriate indexes.

The tables are as follows:

and

Here is the query:

DECLARE @day as varchar(max) = N'20141007'
DECLARE @dayStart as varchar(MAX) = CONCAT(@day,N' 00:00:00.000')
DECLARE @dayEnd as varchar(MAX) = CONCAT(@day,N' 23:59:59.999')

SELECT
    messageId, time, direction, hasRouting, deviceType,
    unitId, accountCode, clientId, data
FROM
    Logs AS A
WHERE
    EXISTS (SELECT * FROM Acks AS B
            WHERE (A.messageId = B.messageId)
              AND (B.direction = 0) AND (B.isNack = 0))
    AND (A.unitId LIKE 'TEST')
    AND (A.direction = 1) AND (A.time BETWEEN @dayStart AND @dayEnd)
ORDER BY
    time ASC

I must admit that I have not had a lot of experience with queries from two tables i.e. joins. The way I interpret this query is that it is going to first filter out all of the entries for the log table, and then go and do a "where" query on the entire log table for each row in the log table.

Now, each table is about 50GB and you can imagine if it's not utilising the partitioning scheme correctly this query is going to iterate through the entire table.

My concerns with the query are:

Performance. The query is taking way too long to execute
It is not fully utilising the partition function when searching

I have noticed that it looks in every single file group when executing the query, even when the query only spans 1 day. I'm not sure if this is due to the Exists.

Some advice and tips on how to structure this query would be greatly appreciated. And possibly also explain what is wrong with my current one so I can learn from my mistakes.

{EDIT}

Reworked version:

DECLARE @day as datetime2(2) = N'20141007'
DECLARE @dayStart as datetime2(2) = CONCAT(@day,N' 00:00:00.000')
DECLARE @dayEnd as datetime2(2) = CONCAT(@day,N' 23:59:59.999')

SELECT
   messageId, time, direction, hasRouting, deviceType,
   unitId, accountCode, clientId, data
FROM
   Logs
INNER JOIN
   Acks ON Logs.messageId = Acks.messageId
WHERE
   (Acks.direction = 0)
   AND (Acks.isNack = 0)
   AND (Logs.unitId LIKE 'DEVMB1')
   AND (Logs.direction = 1)
   AND (Logs.time BETWEEN @dayStart AND @dayEnd)
   AND (Acks.time BETWEEN @dayStart AND @dayEnd)
ORDER BY
   Logs.time ASC

Added execution plan for reworked query:

Execution Plan

Without query execution plan, my first thoughts are:

I. Use datetime variables to remove implicit convertion and use index.

DECLARE @day as datetime2(2) = '20141007'
DECLARE @dayStart as datetime2(2) = CONCAT(@day,' 00:00:00.000')
DECLARE @dayEnd as datetime2(2) = CONCAT(@day,' 23:59:59.999')

II. Change EXISTS for INNER JOIN

EXISTS (Select * FROM Acks AS B where (A.messageId = B.messageId)

to

INNER JOIN ON A.messageId = B.messageId

III. Consider limiting table B on date - at the moment it looks through whole table.

UPDATE.
1. Do parts of your join work much faster as single queries?
2. Please include both Execution Plans on your post. Links may stop working in future.


Handling exceptions in stored procedures called using insert-exec blocks I have a stored procedure that is called in an insert-exec block:

insert into @t
    exec('test')

How can I handle exceptions generated in the stored procedure and still continue processing?

The following code illustrates the problem.  What I want to do is return 0 or -1 depending on the success or failure of the internal exec() call:

alter procedure test -- or create
as
begin try
    declare @retval int;
    -- This code assumes that PrintMax exists already so this generates an error
    exec('create procedure PrintMax as begin print ''hello world'' end;')
    set @retval = 0;
    select @retval;
    return(@retval);
end try
begin catch
    -- if @@TRANCOUNT > 0 commit;
    print ERROR_MESSAGE();
    set @retval = -1;
    select @retval;
    return(@retval);
end catch;
go

declare @t table (i int);

insert into @t
    exec('test');

select *
from @t;

My problem is the return(-1).  The success path is fine.

If I leave out the try/catch block in the stored procedure, then the error is raised and the insert fails.  However, what I want to do is to handle the error and return a nice value.

The code as is returns the message:

Msg 3930, Level 16, State 1, Line 6
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.

This is perhaps the worst error message I've encountered.  It seems to really mean "You did not handle an error in a nested transaction."

If I put in the if @@TRANCOUNT > 0, then I get the message:

Msg 3916, Level 16, State 0, Procedure gordontest, Line 7
Cannot use the COMMIT statement within an INSERT-EXEC statement unless BEGIN TRANSACTION is used first.

I've tried playing around with begin/commit transaction statements, but nothing seems to work.

So, how can I have my stored procedure handle errors without aborting the overall transaction?

Edit in response to Martin:

The actual calling code is:

        declare @RetvalTable table (retval int);

        set @retval = -1;

        insert into @RetvalTable
            exec('

declare @retval int;
exec @retval = '+@query+';
select @retval'
                    );

        select @retval = retval from @RetvalTable;

Where @query is the stored procedure call.  The goal is to get the return value from the stored procedure.  If this is possible without an insert (or, more specifically, without starting a transaction), that would be great.

I cannot modify the stored procedures in general to store the value in a table, because there are too many of them.  One of them is failing, and I can modify that.  My current best solution is something like:

if (@StoredProcedure = 'sp_rep__post') -- causing me a problem
begin
    exec @retval = sp_rep__post;
end;
else
begin
    -- the code I'm using now
end;

The error in the EXEC part of the INSERT-EXEC statement is leaving your transaction in a doomed state.

If you PRINT out XACT_STATE() in the CATCH block it is set to -1.

Not all errors will set the state to this. The following check constraint error goes through to the catch block and the INSERT succeeds.

ALTER PROCEDURE test -- or create
AS
  BEGIN try
      DECLARE @retval INT;

      DECLARE @t TABLE(x INT CHECK (x = 0))

      INSERT INTO @t
      VALUES      (1)

      SET @retval = 0;

      SELECT @retval;

      RETURN( @retval );
  END try

  BEGIN catch
      PRINT XACT_STATE()

      PRINT ERROR_MESSAGE();

      SET @retval = -1;

      SELECT @retval;

      RETURN( @retval );
  END catch;

Adding this to the CATCH block

 IF (XACT_STATE()) = -1
BEGIN
    ROLLBACK TRANSACTION;
END;

Doesn't help. It gives the error

  Cannot use the ROLLBACK statement within an INSERT-EXEC statement.

I don't think there is any way of recovering from such an error once it has happened. For your specific use case you don't need INSERT ... EXEC anyway though. You can assign the return value to a scalar variable then insert that in a separate statement.

DECLARE @RC INT;

EXEC sp_executesql
  N'EXEC @RC = test',
  N'@RC INT OUTPUT',
  @RC = @RC OUTPUT;

INSERT INTO @t
VALUES      (@RC)

Or of course you could restructure the called stored procedure so that it doesn't raise that error at all.

DECLARE @RetVal INT = -1

IF OBJECT_ID('PrintMax', 'P') IS NULL
  BEGIN
      EXEC('create procedure PrintMax as begin print ''hello world'' end;')

      SET @RetVal = 0
  END

SELECT @RetVal;

RETURN( @RetVal );


Are PostgreSQL clusters and servers the same thing? From the official documentation (emphasis mine):

Chapter 22. Managing Databases

  Every instance of a running PostgreSQL server manages one or more databases. Databases are therefore the topmost hierarchical level for organizing SQL objects ("database objects").

Chapter 22. Managing Databases -> 22.1 Overview

  More accurately, a database is a collection of schemas and the schemas contain the tables, functions, etc. So the full hierarchy is: server, database, schema, table (or some other kind of object, such as a function).

Chapter 18. Server Setup and Operation -> 18.2. Creating a Database Cluster

  A database cluster is a collection of databases that is managed by a single instance of a running database server.

I don't quite understand the difference between the PostgreSQL concepts of a cluster and a server, but it seems to me that they are the same thing?

tl;dr

Yes.

Confusion arises from the official terms used in the SQL standard versus more commonly used terminology.

Cluster = database server
Catalog = a database
Schema = a namespace, a grouping of tables

Computer > Cluster(s) > Database(s)/Catalog(s) > Schema(s) > Table(s)

See my Answer to a similar question on Stack Overflow for more info.

Briefly here…

An installation of Postgres on a computer is called a cluster, per Section 4.13 of SQL-92 titled "Clusters of catalogs".

That cluster has one or more databases, each database called a catalog per the SQL standard. Each database contains one or more schemas, each schema being a namespace and security boundary for a collection of tables.

You can have more than one cluster on a computer. This is typically done for different versions of Postgres. For example, you could have Postgres versions 9.5, 9.6, and 10 all installed, each listening on a different port for incoming connections. That would be three clusters in that computer, each containing catalogs that contain schema that contain tables that contain your rows of data defined by columns.


Importing a different character set export dump into another DB instance I am trying to import a given dump file using oracle imp utility.
However; I am facing the following issues.

C:\Users\AD>imp DB_USER/*****@dev1 file=C:\test\db42312.dmp log=C:\test\import2.log

Import: Release 11.2.0.1.0 - Production on Thu Apr 10 13:37:47 2014

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

IMP-00038: Could not convert to environment character set's handle
IMP-00000: Import terminated unsuccessfully

C:\Users\AD>

My source DB yields following results for the NLS query

SQL> select * from v$nls_parameters where parameter in ('NLS_CHARACTERSET','NLS_LANGUAGE');

PARAMETER           VALUE
----------          ---------
NLS_LANGUAGE        FRENCH
NLS_CHARACTERSET    AL32UTF8

SQL>

And my destination DB yields the following results:

SQL> select * from v$nls_parameters where parameter in ('NLS_CHARACTERSET','NLS_LANGUAGE');

PARAMETER           VALUE
----------          ---------
NLS_LANGUAGE        AMERICAN
NLS_CHARACTERSET    AL32UTF8

SQL>

The issue that I face while executing the import command seems like a character set issue.

Is it possible to import the database dump onto my new (but blank) schema? Please note that I already have other databases on the same server.

For me it was the imp command that was wrong. The dump I got was taken using expdp and I was trying to import it with imp.

Got imported using impdp command.


Creating index for large table is taking forever I am trying to create Index for a large table with row count of 496,000,000 and the database is on SQL Server 2012  Enterprise edition. I know how to create index but its taking forever and would like know how to make it faster? Here is the script i tried.

create clustered index.....on table.name ([Column.name] desc)
    with (
            PAD_INDEX = off
            ,STATISTICS_NORECOMPUTE = off
            ,SORT_IN_TEMPDB = off
            ,DROP_EXISTING = off
            ,ONLINE = off
            ,ALLOW_ROW_LOCKS = on
            ,ALLOW_PAGE_LOCKS = on
            ) on [PRIMARY]
            go

Create clustered index ix1_table1 on dbo.table1 (col1) with (online = on)

This will take a little longer, the online part, but it will allow users to continues using the table while the index is being created. Every table (with very few exceptions) should have a clustered index.


join a lookup table with a data table I have the following schema in my database

Neighbors: ( central_id, neighbor_id)
Data: (central_id, value)

This schema is for a convolution algorithm which for each central_id I have 6 neighbors(it is hexagonal grid)
for example here is a part of my Neighbors table

Central_id|Neighbor_id
0|1
0|2
0|3
0|4
0|5
0|6

and here is my data

central_id|value
0|20
1|1
2|6
3|1
4|6
5|1
6|0

I want to join these tables in a way that I can sum value for each central_id
my final result is sth like this

central_id|sum
0|(1+6+1+6+1+0=15)

Here is some of my tries

this query gives me the central_id|neighbor_id|value

select nb.c ,ac.c,ac.value
from
 neighbors as nb
inner join data as ac on nb.neighbour_id=ac.central_id

but it's result is wrong, since value is 20 which is value of central_id.
this is my second try

select c as nb,value from data where c in(
      select nb.c
from
 neighbors  as nb
inner join data  as ac on  nb.neighbour_id=ac.central_id
where  and ac.c=0
)

this query result is correct. It returns neighbour_id and it's value, but I can not sum them based on central_id
If I could end up with such result my problems would be solved

central_id|neighbour_id|value(of each neighbour_id not central_id)
0|1|1
0|2|6
0|3|1
0|4|6
0|5|1
0|6|0

Can some one give me some hints about fixing this problem?thanks

This should work:

SELECT a.central_id, sum(b.value)
FROM neighbors AS a
JOIN data AS b
  ON a.neighbor_id = b.central_id
GROUP BY a.central_id;


How to create a table dynamically based on json data? I have a json that I get as a file, I have loaded that in the Postgres database.
Here is the json data:

{
"tablename": "test",
"columns": [
    {
        "name": "field1",
        "datatype": "BigInt"
    },
    {
        "name": "field2",
        "datatype": "String"
    }
]

}

Now I have to create a table dynamically, I am thinking of writing a function in Postgres to do that.
So the table would be named test with 2 fields one as string and another as bigint.

I am able to get the table name by doing a select as below:

select (metadata->'tablename') from public.json_metadata;

However, I am having difficulty getting all the nested column names to form a create table statement.

1- How would you go about doing that, any built in Postgres functions to extract that.

2- Is a Postgres function the best way to approach this problem, or should I write this in python (I will have to learn Python) or shell script.

The number of columns would not be fixed, different json files will have different number of columns.

You need dynamic SQL for that, which carries the hazard of SQL injection.
However, done properly, this is safe against SQLi:

DO
$do$
BEGIN

EXECUTE (
   SELECT format('CREATE TABLE %I(%s)', metadata->>'tablename', c.cols)
   FROM   public.json_metadata m
   CROSS  JOIN LATERAL (
      SELECT string_agg(quote_ident(col->>'name')
                        || ' ' ||  (col->>'datatype')::regtype, ', ') AS cols
      FROM   json_array_elements(metadata->'columns') col
      ) c
   );
END
$do$;

Table and column names are treated as case-sensitive. (You may want lower-case instead.) The type name is treated as case-insensitive, and any valid type name works.

Of course, this would raise an exception for the non-existent data type String you display. Try with text instead.

Note the use of format(), the object identifier type regclass, quote_ident(), the aggregation of columns in the LATERAL subquery and the DO command to execute dynamic SQL.

Related:

How to do this binary appearance analysis
SQL injection in Postgres functions vs prepared queries
Table name as a PostgreSQL function parameter


MySQL Database Replication in Fedora 21 I have a MySQL 5.6 Community Edition server running on a machine with Fedora 21 which was installed via the YUM repository. I want to enable MySQL replication on the Server, but since I am limited to one machine, I have to install two MySQL instances on the same machine. I need a complete step-by-step procedure of installing two or more MySQL server instances side by side, one to act as the Master, the other as the slave. I have tried searching on the Internet for a comprehensive procedure, but I haven't found any yet. The MySQL Official documentation didn't really help as it is OS independent, yet I want a Fedora/Linux specific installation and configuration. Other links I have tried include (not in any specific order) this and this, but to no avail. Anyone with a complete reference/procedure on how to do this?

Here is an old instructions for setting up master-master replication for windows but concept is same.
Considering you already have a 3306 instance by default you can follow below steps to setup as well (if above doesn't help)

  Make datadir and correct permissions:
  mkdir /var/lib/mysql3307
  chown -R mysql:mysql /var/lib/mysql3307
  Create config
  cp /etc/my.cnf /etc/my3307.cnf (Correct relevant parameters)
  Install basic auth system / dbs:
  mysql_install_db --user=mysql --basedir=/usr --datadir=/var/lib/mysql3307 --defaults-file=/etc/my3307.cnf
  Start mysql  mysqld_safe --defaults-file=/etc/my3307.cnf --user=mysql &
  Take fulldump from 3306 instance:  mysqldump -uroot -p --all-databases --master-data=2 | gzip > backup.sql.gz
  Load it to new 3307 instance
  zcat backup.sql.gz  | mysql -uroot -p --port=3307 --socket=/var/run/mysql/mysqld3307.sock
  Grab binary log co-ordinates from  backup:
  zcat backup.sql.gz | head -30 | grep -i change
  Create replication user on master:
  grant replication slave on . to 'repluser'@'%' identified by 'replpass';
  Run change master to on 3307 slave using binlogs take from  step #7:
  CHANGE MASTER TO MASTER_HOST='localhost', MASTER_USER='repluser', MASTER_PASSWORD='replpass', MASTER_PORT=3306, MASTER_LOG_FILE='XXXX',
  MASTER_LOG_POS=XXXX;
  Start  slave on  3307.

You're done.


Checking if 2 tables ( on different servers ) have the same exact data So it seems that the company who is hosting our SQL Servers has been having some trouble with the replication from the production server to the backup server... I believe some of the tables have been replicating correctly though. Replication is done daily (after hours).

Is there a way I can compare 2 of the same tables, 1 from the backup vs 1 from the production server to see if last nights replication worked?

The only way I could find was to run the following query on both servers and seeing if the result matched which "might" mean that to 2 tables contain the same information.

SELECT CHECKSUM_AGG(BINARY_CHECKSUM(*))
        FROM   (

                    SELECT  *
                    FROM    table_to_compare
               ) t1

using the above code, It seems that the table did replicate successfully as the checksum values are the same but I'm not sure how reliable this method is.

Does anyone know of a better method to check this out or if this is a good way?

I'm running SQL Server 2008 on a windows server 2008 computer.

Thanks.

I believe you're looking for the tool tablediff which lets you do exactly that - compare two replicated tables for differences. You might find this article useful, to get  started.

Here is a GUI for tablediff


float to varchar with no trailing zeros I am having a heck of a time trying to convert a FLOAT(53) field to a VARCHAR where there are no trailing zeros or scientific notation.

The field could have a number that is really big or really small. I need to convert it to VARCHAR and show, at most, 5 digits after the decimal but no trailing zeros. So 123.456789 should be 123.45678 and 123456.78 should be 123456.78.

I have an SQL Fiddle showing what I have tried: http://sqlfiddle.com/#!6/bd392/4

SQL Server 2012 and up support FORMAT, which enables .NET-like format strings, making this a rather easy task; just use

FORMAT(Num, '0.#####')

Working SQL Fiddle here.


Is there a developer edition of Microsoft SQL Server 2017 for Linux? I have SQL Server 2016 Developer Edition installed on Ubuntu, whis is free for development purposes:

  SQL Server 2016 SP1 Developer is a fully featured free edition licensed for use as a development and test database in a non-production environment.

I wanted to install SQL Server 2017 on Ubuntu as well. For Windows, on the SQL Server download page, there are three options - a trial evaluation, SQL Express (free but limited), and SQL Developer (free for non-production environments).

However, for Linux, there's just links for installing on Red Hat, Ubuntu, or SUSE.

When installed on Ubuntu, SQL Server 2017 (v.Next) says:

Enter the SQL Server system administrator password:
Confirm the SQL Server system administrator password:
Configuring SQL Server...

This is an evaluation version.  There are [174] days left in the evaluation period.

Is there a SQL Server 2017 Developers Edition for Linux? If so, where so I go to access it?

Note: This isn't a question about licensing, per se; it's about whether a 2017 version of the product I'm alreasdy using is available.

2017, May:

SQL Server 2017 hasn't been released yet (only previews) so no licensing or pricing is available yet (the name v.Next should have been a clue!).

2017, Dec:

SQL Server 2017 for Linux was released in October and the Developer Edition is free for development purposes, as in previous versions.


Table 'performance_schema.threads' doesn't exist. SQL Error: 1146) I'm using MySQL '5.6.29-log' on Windows 2012 and performance_schema is missing
few tables.

How does I re create performance_schema? I'm looking at MySQL :: MySQL 5.6 Reference Manual :: 22.2.1 Performance Schema Build Configuration and  I cannot find anything(

Please advise.

mysql_upgrade — Check and Upgrade MySQL Tables


What happens if a DC goes down in a Postgresql, Patroni, etcd multi DC setup I have a question regarding how Patroni and etcd handle the below scenarios.

Assume there are two sites in this deployment, Site1 and Site2 and it's a 5 node cluster spread across these two sites. This is a primary/replica HA deployment.

Site1 has

S1node1 (Patroni + etcd leader + PostgreSQL master),
S1node2 (Patroni + etcd follower + PostgreSQL replica) and
S1node3 (Patroni + etcd follower + PostgreSQL replica)

Site2 has

S2node1 (Patroni + etcd follower + PostgreSQL replica) and
S2node2 (Patroni + etcd follower + PostgreSQL replica).

Scenario 1:

My understanding is that since the etcd leader is in Site1 and quorum is (5/2)+1 = 3, in case of a network split between the two sites Site1 will continue to accept updates since it has majority and meets quorum. Site2 will stop accepting updates since quorum is not met. Once the network glitch is resolved then the Site2 nodes just join back into the cluster. No action required.

Scenario 2:

If for some reason the entire Site1 went down (disaster scenario), then in Site2 we just have two nodes which don't meet the quorum. There will be no etcd leader which means that Patroni cannot promote the PostgreSQL replica to master at Site2.

Now the question is how to ensure that the Site2 nodes can get promoted to master? Does Patroni take care of this scenario?

Let me just copy the answer and following remarks from the main Patroni developer and maintainer:

You got nearly everything correctly. If the Site1 is down, Site2 will not promote due to the lack of quorum.  There is no way to automatically recover from such a situation.

The only solution would be to run one etcd node somewhere else, say Site3. You don't have to run Postgres there.  Keep the nu

Please note that the Postgres master doesn't necessarily have to run on the same node where the etcd leader does. They are independent from each other.

In any case, with only two data centers it is sometimes better to have two independent etcd clusters on Site1 and Site2, and run a Patroni standby cluster on Site2. If the first site is down, you can manually promote the standby cluster.

Automatic promotion in this case is not possible, because Site2 will never be able to figure out the state of Site1.


SQL Server 2008 R2 memory issue I have read many posts regarding the memory but still its not clear.

I have dedicated server SQL Server 2008 R2 with Windows 2008 R2, 32 GB RAM, 2 6 core processor and a database of around 60 GB on it. Min and Max amount is set to 16 GB and 28 GB.

After few days if I check in task manager, it shows the load of 31 GB Memory but actually maximum is allotted to SQL Server is 28 GB and nothing is running on he server.

Why its increasing to the maximum limit? And where this 31 GB used? If its used in cache then how to clear the cache without restarting the SQL Server?

There are different questions in my mind if someone can help and discuss on this.

Although you limited the memory to 28 GB out of 32 GB, this is not the only memory that SQL Server uses.  The running programs and needs within Windows take some additional space.

If you want to closely tune the memory use, I recommend reading this, which works well for my servers.  See Jonathan Kehayias at:
http://www.sqlskills.com/blogs/jonathan/how-much-memory-does-my-sql-server-actually-need/

It basically says "reserve 1 GB of RAM for the OS, 1 GB for each 4 GB of RAM installed from 4–16 GB, and then 1 GB for every 8 GB RAM installed above 16 GB RAM."  This suggests that your Maximum Memory should be tuned to 25 GB.

You can dynamically alter the min/max by setting a new value for those measures.  The OS will adjust in a relatively short time without a reboot.

EDIT: Of course, as both Jonathan and Shanky mention, you still need to monitor your performance counters, since any general answer will likely need tuning.


Generate INSERT statement from SELECT query I have two databases, in first database I have a table called zoom_city and in the second database I have a table called courrier_city. The SQL for each is below:

CREATE TABLE IF NOT EXISTS `zoom_city` (
  `id_ciudad` int(11) NOT NULL AUTO_INCREMENT,
  `id_estado` int(11) NOT NULL,
  `ciudad` varchar(200) NOT NULL,
  `capital` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id_ciudad`),
  KEY `id_estado` (`id_estado`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=644;

CREATE TABLE IF NOT EXISTS `courrier_city` (
  `country` varchar(4) NOT NULL,
  `state` varchar(10) NOT NULL,
  `id` varchar(100) NOT NULL,
  `name` varchar(255) NOT NULL,
  `capital` varchar(255) NOT NULL,
  `created` timestamp NULL DEFAULT NULL,
  `modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `deletedAt` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`country`,`state`,`id`),
  KEY `fk_courrier_city_state1_idx` (`country`,`state`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

So I need to dump values from first table zoom_city and insert them in courrier_city but taking this in account:

country always be VE string
id_estado are stored in zoom_city as for example 1, 2, 3, 10 and right values for courrier_city and taking same example would be VE.01, VE.02, VE.03 and VE.10 so this value needs a transform before insertion.

Is that possible in raw SQL or I need to do this in a programming language like PHP for example?

I wasn't clear where you want the transformed id_estado inserted, so I chose "state" as a destionation.   You can modify accordingly:

insert into courrier_city (country, state, created, modified) select "VE", if(is_estado<10, concat("VE0.",is_estado),concat("VE.",is_estado)), now(), now() from zoom_city

You will probably want to modify the example further to account for specifying all the destination columns with either a literal value such we did for country, a transform like we did for is_estado, or just the basic column name in the select portion to copy values.

You should specify some value for every destination column without a schema default defined.


Does Sql Server 2008 support Database Sharding Does Microsoft support implementing Database Sharding using Sql Server 2008 R2 (any edition). From my limited understanding of it, I think, we could probably implement it using Sql Server Express. But I was not sure if Microsoft supports it or not.

Database Sharding Resource Link

Other than Distributed Partitioned Views, Data-Dependent Routing, what other options are available for Scale-out? (Peer-to-Peer Transactional Replication, Federated DB?)

Please share any URL that describe any implementation architectures in detail.

Thanks,
_UB

No, SQL Server doesn't support sharding. SQL Server scales up not out.

This applies to RDBMS in general:

CAP theorem says you can have at most 2 of C, A and P.
RDBMS are ACID compliant which is the point of using them

The "C" in both acronyms is "Consistency" and sharding sacrifices "Consistency"
So generally sharding and consistency are mutually exclusive

There are exceptions (more or less) of course. Some examples:

Oracle RAC which is expensive and tricky
MySQL replication which is not Consistent
MySQL cluster sacrifices Availability in case of node failure, and can be set to prioritize either consistency or availability


Materialized View PostgreSQL Is there any possibility of having built-in materialized views in PostgreSQL ?

I don't want Gardner version or somehthing like physical table populated by triggers.
I've read that was/is something being deploy...

A basic version of materialized view are being delivered in 9.3, see depesz's article. However, it's a very limited version as you have to call REFRESH MATERIALIZED VIEW to get the database to update the view. This will hopefully improve to become automatic in later versions soon.


Installation of SSRS I have a doubt regarding SSRS installation. I see two methods on the web.However I am not sure if both lead to same solution.

1)Install SSRS via installer OF SIZE 105 mb.

2)Install Visual Studio Community, then SSDT, which in turn installs SSRS, and post this  install templates via VS extensions.

My question is whether both these methods lead to the same result? If not which is the correct one?

The requirement in m organisation is to develop SSRS reports using TSQL DYNAMIC QUERIES,FUNCTIONS,AND STORED PROCEDURES.

SERVER :SQL SERVER 2019 DEVELOPER EDITION

I think you are possibly confusing two different but related items. The first is the installation, which you absolutely should use the installer for. Full installation instructions can be found here.

The second item is about creating and publishing content. Generally you do this via SSDT or the BI Components of Visual Studio (or just knock up the XML in a text editor of your choice if you feel like a challenge). Once you have created your reports you can then publish them to SSRS via Visual Studio or take the RDL file and publish it manually. There is a brief tutorial on how to do this in MS Docs.

The most important difference between these two items though is that even if you installed SSDT/Visual Studio and have created your reports, unless you have actually got somewhere to host them (i.e. by installing SSRS on a server or within Power BI Service) then you cannot share them or access them other than from within Visual Studio.


Couldn't find sql server agent in Sql Server 2014 Management Studio? Is there any way to add it in SQL Server Express edition?

No you cannot have SQL Server agent feature in express edition of SQL Server. To get that you would have to upgrade to licensed version of SQL Server like Enterprise, Standard, Web and Developer editions. Just so that you know you can see the SQL Server agent services in express edition but that cannot be started.
If you want to use scheduling to schedule jobs you have to rely in windows scheduler

From SQL Server 2014 onwards the developer edition is free to download and use and this version has SQL Server agent feature. The only limitation is you cannot use developer version in production, you can ONLY use it in development and testing environment.


Sql Database server Remote access not working I have configured Remote settings from TCP/IP with 1433 port and
Firewall is also disabled

I have checked settings over intenet and applied still not able to connect remotly

I am using windows server 2008 r2 and server has Private Ip

Can anybody tell me is there any other setting which i need to do?

== Edit ==
The error is:

  "A network-related or instance-specific error occurred while
  establishing a connection to SQL Server. The server was not found or
  was not accessible. Verify that the instance name is correct and that
  SQL Server is configured to allow remote connections."

The first thing you want to check is if Remote Connections are enabled on your SQL Server database.
right click the server… Go to properties  then Connections.

Under the hood of 'remote server connections' . It should be checked box tick of 'Allow remote connections to this server'

For further Ref Here


How to get sum & latest activity in same result? Table_1
Name   |   Activity  | LogTime
A      |    0        | 2018-12-17 10:16:04.877
A      |    1        | 2018-12-15 10:16:04.877
A      |    0        | 2018-12-16 10:16:04.877
A      |    0        | 2018-12-10 10:16:04.877
A      |    0        | 2018-12-10 10:10:04.877
B      |    1        | 2018-12-16 10:16:04.877
B      |    0        | 2018-12-17 10:16:04.877
C      |    1        | 2018-12-14 10:16:04.877
C      |    1        | 2018-12-12 10:16:04.877
C      |    1        | 2018-12-18 10:16:04.877

Desired Result

Name |   TOTALActivity_0  | TOTALActivity_1  |  LatestActivity_0_Logtime  | LatestActivity_1_Logtime
A    |      4             |     1            |  2018-12-17 10:16:04.877   |  2018-12-15 10:16:04.877
B    |      1             |     1            |  2018-12-17 10:16:04.877   |  2018-12-16 10:16:04.877
C    |      0             |     3            |  NULL                      |  2018-12-18 10:16:04.877

I am able to get result as below

Name |   TOTALActivity_0  | TOTALActivity_1
A    |      4             |     1
B    |      1             |     1
C    |      0             |     3

Using below query

SELECT
  NAME,
  SUM(CASE WHEN Activity = 0 THEN 1 ELSE 0 END) TOTALActivity_0,
  SUM(CASE WHEN Activity = 1 THEN 1 ELSE 0 END) TOTALActivity_1
 FROM Table_1
 GROUP BY NAME

If I tried as below

 SELECT
  NAME,
  SUM(CASE WHEN Activity = 0 THEN 1 ELSE 0 END) TOTALActivity_0,
  SUM(CASE WHEN Activity = 1 THEN 1 ELSE 0 END) TOTALActivity_1,
  CASE WHEN Activity = 0 THEN LogTime ELSE NULL END AS LatestActivity_0_Logtime,
  CASE WHEN Activity = 1 THEN LogTime ELSE NULL END AS LatestActivity_1_Logtime
 FROM Table_1
 GROUP BY NAME,Activity,LogTime

Then it is giving Single-single & redundant records.
Then I tried with again with below query

 SELECT
  NAME,
  SUM(CASE WHEN Activity = 0 THEN 1 ELSE 0 END) TOTALActivity_0,
  SUM(CASE WHEN Activity = 1 THEN 1 ELSE 0 END) TOTALActivity_1,
  NULL AS LatestActivity_0_Logtime,
  NULL AS LatestActivity_1_Logtime
 FROM Table_1
 GROUP BY NAME
 UNION
 SELECT NULL,NULL,NULL,CASE WHEN Activity = 0 THEN LogTime ELSE NULL END AS LatestActivity_0_Logtime,
  CASE WHEN Activity = 1 THEN LogTime ELSE NULL END AS LatestActivity_1_Logtime
  FROM Table_1

Then result is again undesirable as below

Name |   TOTALActivity_0  | TOTALActivity_1  |  LatestActivity_0_Logtime  | LatestActivity_1_Logtime
A    |      4             |     1            |  NULL                      |  NULL
B    |      1             |     1            |  NULL                      |  NULL
B    |      1             |     3            |  NULL                      |  NULL
NULL |     NULL           |   NULL           |  2018-12-17 10:16:04.877   |  2018-12-15 10:16:04.877
NULL |     NULL           |   NULL           |  2018-12-16 10:16:04.877   |  2018-12-16 10:16:04.877
NULL |     NULL           |   NULL           |  2018-12-10 10:16:04.877   |  2018-12-18 10:16:04.877
NULL |     NULL           |   NULL           |  2018-12-10 10:10:04.877   |  2018-12-15 10:16:04.877
NULL |     NULL           |   NULL           |  2018-12-17 10:16:04.877   |  2018-12-16 10:16:04.877
.
.
.
.
.
.

Please Help to get distinct & merged result as desired

This should work:

SELECT
    Name,
    COUNT(CASE WHEN Activity = 0 THEN 1 END) AS TOTALActivity_0,
    COUNT(CASE WHEN Activity = 1 THEN 1 END) AS TOTALActivity_1,
    MAX(CASE WHEN Activity = 0 THEN LogTime END) AS LatestActivity_0_Logtime,
    MAX(CASE WHEN Activity = 1 THEN LogTime END) AS LatestActivity_1_Logtime
FROM
    Table_1
GROUP BY
    Name ;

Test in: dbfiddle.uk


Msg 8152, Level 16, State 2 Have anyone experienced this?

  String or binary data would be truncated

Raised during ALTER INDEX; even in CREATE TABLE. I'm not having this problem in other databases. I may have reconfigured something as this is only a test database.

All I am seeing in the internet is regarding length of strings inserted by INSERT statement exceeds in the length of defined data type.

Community wiki answer:

Check for triggers, and DDL triggers on the database.

The error message gives the name of what to look for: procedure DatabaseAudit line 47.


SQL-Server Custom Database Role I have a created custom Database Role called Managers. Added db_owner to this role. I would like to Deny DROP Database to this custom Role. But the DENY Drop DATABASE TO Managers is not working

Any work around ?

The roles db_owner and sysadmin are special cases and can't be denied anything.  My guess is that db_ddladmin, db_datareader, db_datawriter and execute access will give you everything you want instead of using db_owner.


mysql change password in session I changed my password from old hash to a new hash using the following query

SET SESSION old_passwords=FALSE;
SET PASSWORD = PASSWORD('[your password]');

I found this query on https://stackoverflow.com/questions/1892607/mysql-password-hashing-method-old-vs-new. I replaced [your password] to my own password.

Now I can't log into my db again using the password I set. I was wondering is this password change forever or is the session going to expire and how long till it does.

According to MySQL Documentation on SET PASSWORD

The SET PASSWORD statement assigns a password to an existing MySQL
user account.   If the password is specified using the PASSWORD() or
OLD_PASSWORD() function, the literal text of the password should be
given. If the password is specified without using either function, the
password should be the already-encrypted password value as returned by
PASSWORD().
With no FOR clause, this statement sets the password for the current
user. Any client that has connected to the server using a nonanonymous
account can change the password for that account

However, you just been struck by lightning because the same webpage says

Important
SET PASSWORD may be recorded in server logs or in a history file such as ~/.mysql_history, which means that plaintext passwords may be
read by anyone having read access to that information. See Section
6.1.2, “Password Security in MySQL”.
Note
If you are connecting to a MySQL 4.1 or later server using a pre-4.1 client program, do not use the preceding SET PASSWORD or
UPDATE statement without reading Section 6.1.2.3, “Password Hashing in
MySQL”, first. The password format changed in MySQL 4.1, and under
certain circumstances it is possible that if you change your password,
you might not be able to connect to the server afterward.

Your password change is forever, so to speak.
HOW TO FIX IT
Suppose your user is myuser@localhost, you can have someone with ALL PRIVILEGES run this
UPDATE mysql.user SET password=OLD_PASSWORD('[your password]')
WHERE user='myuser' AND host='localhost';
FLUSH PRIVILEGES;

If you have no DBA but can restart mysql, then do this
service mysql restart --skip-networking --skip-grant-tables
mysql -e"UPDATE mysql.user SET password=OLD_PASSWORD('[your password]') WHERE user='myuser' AND host='localhost'"
service mysql restart


What does this error mean when doing a pg_restore on a Postgres 9.6.2 database? So I have a rails 5.0.x application running on Heroku with Postgres 9.6.1. I pull down and do a pg_restore --exit-on-error --verbose --clean --no-acl --no-owner -h localhost -d database_development ~/Documents/Backups/myproduction.dump

I get the following error:

pg_restore: connecting to database for restore
pg_restore: dropping FK CONSTRAINT trial_versions fk_rails_f888baa05c
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 3091; 2606 16768 FK CONSTRAINT trial_versions fk_rails_f888baa05c u7dok58iar41mh
pg_restore: [archiver (db)] could not execute query: ERROR:  constraint "fk_rails_f888baa05c" of relation "trial_versions" does not exist
    Command was: ALTER TABLE ONLY "public"."trial_versions" DROP CONSTRAINT "fk_rails_f888baa05c";

What is this error telling me? I've never had this issue before. This just started happening recently and I need to first understand the error before I can even begin to troubleshoot it.

The database the dump file was taken from has a certain foreign key constraint defined.  But your target database does not.  Probably because someone went and added it to the dumped database since the last dump.  Since you specified --clean, it is trying to drop all the objects that exist in the dump file from your target database before recreating them.  It is failing, because it doesn't exist in your target database to be dropped.

You can add --if-exists to the restore command.  Or you can remove --exit-on-error from the command, and just ignore the error message you get.


Repeating rows of date intervals based on another table in Oracle I have a table of maintenance plans with date intervals between each required maintenance period.

Fiddle: http://sqlfiddle.com/#!4/af742

create table maint_sched (maint_id number, start_date date, day_interval number);
insert into maint_sched values (1, date '2015-01-01', 90);
insert into maint_sched values (2, date '2015-04-01', 200);
insert into maint_sched values (3, date '2015-07-01', 30);

I need to write a query which generates rows for all maintenance schedules up to a finite point in the future (e.g. 12 months).

MAINT_ID   DATE_DUE
--------  ---------
1          1-Jan-15
1          1-Apr-15
2          1-Apr-15
1         30-Jun-15
3          1-Jul-15
3         31-Jul-15
3         30-Aug-15
1         28-Sep-15
3         29-Sep-15
2         18-Oct-15
3         29-Oct-15
3         28-Nov-15
1         27-Dec-15
3         28-Dec-15

I've been able to get as far as generating fixed intervals with CONNECT BY tricks, but it starts doing something recursive when I try joining it to a table other than dual.

-- This generates a list of every five days for 36 months
SELECT (sysdate + (5 * level)) AS duedate
FROM dual
CONNECT BY (sysdate + (5 * level)) < add_months(sysdate, 36)

I have a feeling this is the right approach, but I could use some help making it work!

Returning maintenance dates for the next 365 days:

variable n number;
exec :n := 365;

with generator as
(
  select rownum - 1 as r from dual connect by level <= (select max(ceil(:n / day_interval)) from maint_sched)
)
select ms.maint_id, ms.start_date + g.r * ms.day_interval as date_due
from maint_sched ms join generator g on (ms.start_date + g.r * ms.day_interval between sysdate and sysdate + :n)
order by 2, 1;

  MAINT_ID DATE_DUE
---------- ---------
         1 30-JUN-15
         3 01-JUL-15
         3 31-JUL-15
         3 30-AUG-15
         1 28-SEP-15
         3 29-SEP-15
         2 18-OCT-15
         3 29-OCT-15
         3 28-NOV-15
         1 27-DEC-15
         3 28-DEC-15
         3 27-JAN-16
         3 26-FEB-16
         1 26-MAR-16
         3 27-MAR-16
         3 26-APR-16
         2 05-MAY-16


Dramatic decrease in SQL Azure performance after scaling to new edition Yesterday, we scaled one of our databases in Azure from the old Business edition to the new Standard one (S2).

Since then, it has been behaving rather erratically.

We have a relatively simple table called dbo.Asset.  On it, there is a column called ContentSetId which is of type integer.  This column is also covered by an index:

CREATE NONCLUSTERED INDEX [IX_Asset_ContentSet] ON [dbo].[Asset]
(
    [ContentSetId] ASC
)

The table contains around 2 million records.

The below query took 36 seconds to run the first time, but 0 seconds on subsequent runs.  We are seeing similar behaviour in other queries too, whereby they can take upwards of a minute to return under 1000 rows, but subsequent runs are much, much quicker.

SELECT TOP 100 * FROM dbo.Asset WHERE ContentSetId = 3

As stated above, we have only noticed this behaviour occurring since changing from the business edition to the new standard s2 one.

Any suggestions would be very welcome!

I conducted a series of performance tests against the new service tiers (as mentioned by another poster above).

I tested I/O rates back in July and have now also tested the memory (i.e. max buffer pool size) in each tier:
http://cbailiss.wordpress.com/2014/11/11/azure-sql-database-memory-limits-by-service-tier/

(I was going to add this as a comment on dark_perfect's comment above, but on this site I don't have enough reputation to add comments yet).


Will I have to restore controlfile in RMAN level 1 backupset before recover database? For example, I have a level 0 and level 1 RMAN  backupset files like
inr00_0413_*
inr01_0413_1_*
Is it necessary to do this when recover from RMAN level 1 backup:

restore controlfile from inr01_0413_1_ctl_file_9jr2v0te_1_1_20160413;

Or I just:

catalog start with '/u02/rmanbackup/';
recover database;

If not restore controlfile, after resotre and recover will get error:

  ORA-16004: backup database requires recovery
  ORA-01152: file 1 was not restored from a sufficiently old backup
  ORA-01110: data file 1: '+DATA1/rac/datafile/system1.dbf'

Can't do

SQL> recover database using backup controlfile until cancel

Or

RMAN> sql 'alter database open read only';

Backupset files:

[oracle@dbsrv01] ll /u02/rmanbackup/
Apr 13 20:53 block_change_tracking.ctf
Apr 13 19:44 inr00_0413_arc_8pr2uqq2_1_1_RAC_20160413_42265
Apr 13 19:44 inr00_0413_arc_8qr2uqq2_1_1_RAC_20160413_42266
Apr 13 19:44 inr00_0413_ctl_file_8rr2uqqa_1_1_20160413
Apr 13 19:44 inr00_0413_dbf_8er2uqcc_1_1_RAC_20160413_42254
Apr 13 19:46 inr00_0413_dbf_8fr2uqcc_1_1_RAC_20160413_42255
Apr 13 19:48 inr00_0413_dbf_8gr2uqcc_1_1_RAC_20160413_42256
Apr 13 19:50 inr00_0413_dbf_8hr2uqcc_1_1_RAC_20160413_42257
Apr 13 19:51 inr00_0413_dbf_8ir2uqcc_1_1_RAC_20160413_42258
Apr 13 19:52 inr00_0413_dbf_8jr2uqcc_1_1_RAC_20160413_42259
Apr 13 19:52 inr00_0413_dbf_8kr2uqcc_1_1_RAC_20160413_42260
Apr 13 19:52 inr00_0413_dbf_8lr2uqcc_1_1_RAC_20160413_42261
Apr 13 19:52 inr00_0413_dbf_8mr2uqcc_1_1_RAC_20160413_42262
Apr 13 19:52 inr00_0413_dbf_8nr2uqcc_1_1_RAC_20160413_42263
Apr 13 19:52 inr00_0413_dbf_8or2uqcc_1_1_RAC_20160413_42264
Apr 13 19:52 inr00_0413_spfile_8sr2uqqc_1_1_20160413
Apr 13 20:50 inr01_0413_1_arc_9gr2v0t5_1_1_RAC_20160413_42288
Apr 13 20:50 inr01_0413_1_arc_9hr2v0t5_1_1_RAC_20160413_42289
Apr 13 20:50 inr01_0413_1_arc_9ir2v0tc_1_1_RAC_20160413_42290
Apr 13 20:50 inr01_0413_1_ctl_file_9jr2v0te_1_1_20160413
Apr 13 20:50 inr01_0413_1_dbf_95r2v0sv_1_1_RAC_20160413_42277
Apr 13 20:50 inr01_0413_1_dbf_96r2v0sv_1_1_RAC_20160413_42278
Apr 13 20:50 inr01_0413_1_dbf_97r2v0t0_1_1_RAC_20160413_42279
Apr 13 20:50 inr01_0413_1_dbf_98r2v0t0_1_1_RAC_20160413_42280
Apr 13 20:50 inr01_0413_1_dbf_99r2v0t0_1_1_RAC_20160413_42281
Apr 13 20:50 inr01_0413_1_dbf_9ar2v0t0_1_1_RAC_20160413_42282
Apr 13 20:50 inr01_0413_1_dbf_9br2v0t0_1_1_RAC_20160413_42283
Apr 13 20:50 inr01_0413_1_dbf_9cr2v0t0_1_1_RAC_20160413_42284
Apr 13 20:50 inr01_0413_1_dbf_9dr2v0t0_1_1_RAC_20160413_42285
Apr 13 20:50 inr01_0413_1_dbf_9er2v0t0_1_1_RAC_20160413_42286
Apr 13 21:06 inr01_0413_1_dbf_9fr2v0t0_1_1_RAC_20160413_42287
Apr 13 21:06 inr01_0413_1_spfile_9kr2v0tg_1_1_20160413

The error might be a bit misleading.
It is correct to restore the control file from the latest backups assuming you want to recover to as late in time as possible. If your control_file_record_keep_time is long enough to also contain the backup pieces of the level 0 backup, rman will start restoring the level 0 backup and continue with the level 1 backup. If your level 0 backup is not visible in the restored control file you need to do the catalog so the info gets loaded into the control file.
The problem is with the ending of the recovery because the database keeps requesting archives until it sees the end of redo marker. This means that there was a clean shutdown. If everything is OK, you also have the archive log files, in backup or in the archive destination.
To get this database open after restoring the level 0 backup, you have to prepare the restore by setting until what moment the recovery has to be done. Without this setting the recovery will try to recover almost forever.
set until can be done until time or until sequence or until scn. It is my guess that "

Apr 13 21:06 inr01_0413_1_dbf_9fr2v0t0_1_1_RAC_20160413_42287

" is a backup of log sequence 42287 so your recovery would succeed if you specify
set until sequence 42287 {
restore database;
recover database;
}


Struggling to create an efficient index for this query I am struggling to create an index for a query and unfortunately I cannot change the query at all as it part of an ERP system. The problem is this query has over 1m reads and sometimes a duration of 10s

I Have tried using just the predicate, the seek predicate and a combination of both in different orders to no success.

Here is the query that cannot be changed:

SELECT  TOP (@0) "timestamp","Link ID","Record ID","URL1","URL2","URL3","URL4","Description","Type","Created","User ID","Company","Notify","To User ID"
FROM "Database".dbo."Record Link" WITH(READUNCOMMITTED)
WHERE ("Link ID">@1 AND (("Company"=@2 OR "Company"=@3) AND "Notify"=@4 AND ("To User ID" COLLATE Latin1_General_100_CI_AI LIKE @5 OR "To User ID" COLLATE Latin1_General_100_CI_AI LIKE @6)))
ORDER BY "Link ID" ASC OPTION(OPTIMIZE for UNKNOWN, FAST 50)

Here is the script for the table if it helps:

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Record Link](
    [timestamp] [timestamp] NOT NULL,
    [Link ID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
    [Record ID] [varbinary](448) NOT NULL,
    [URL1] [nvarchar](250) NOT NULL,
    [URL2] [nvarchar](250) NOT NULL,
    [URL3] [nvarchar](250) NOT NULL,
    [URL4] [nvarchar](250) NOT NULL,
    [Description] [nvarchar](250) NOT NULL,
    [Type] [int] NOT NULL,
    [Note] [image] NULL,
    [Created] [datetime] NOT NULL,
    [User ID] [nvarchar](132) NOT NULL,
    [Company] [nvarchar](30) NOT NULL,
    [Notify] [tinyint] NOT NULL,
    [To User ID] [nvarchar](132) NOT NULL,
 CONSTRAINT [Record Link$0] PRIMARY KEY CLUSTERED
(
    [Link ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

Here is the stats for the time and IO

  SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 4 ms.

(0 rows affected)
Table 'Record Link'. Scan count 1, logical reads 1018402, physical reads 3, read-ahead reads 1018391, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row affected)

 SQL Server Execution Times:
   CPU time = 8234 ms,  elapsed time = 12641 ms.

Here is the execution plan:

I would greatly appreciate it if someone could help me create an efficient index for this.

Here is a link to the execution plan:
https://www.brentozar.com/pastetheplan/?id=BkTxCbdN4

If you where to go the route of plan guides as @DenisRubashkin mentioned, you could create an 'empty' plan guide to remove the existing hints: OPTION(OPTIMIZE for UNKNOWN, FAST 50).

An example of a plan guide you could use, you might have to change some datatypes.

EXEC sp_create_plan_guide
    @name =  N'Guide1',
    @stmt = N'SELECT  TOP (@0) "timestamp","Link ID","Record ID","URL1","URL2","URL3","URL4","Description","Type","Created","User ID","Company","Notify","To User ID"
FROM test.dbo."Record Link" WITH(READUNCOMMITTED)
WHERE ("Link ID">@1 AND (("Company"=@2 OR "Company"=@3) AND "Notify"=@4 AND ("To User ID" COLLATE Latin1_General_100_CI_AI LIKE @5 OR "To User ID" COLLATE Latin1_General_100_CI_AI LIKE @6)))
ORDER BY "Link ID" ASC OPTION(OPTIMIZE for UNKNOWN, FAST 50)',
    @type = N'SQL',
    @params = '@0 int, @1 int, @2  [nvarchar](30), @3  [nvarchar](30), @4 tinyint, @5 nvarchar(264),@6 nvarchar(264)',
    @hints = NULL;

@hints will override the existing hints, by not specifying any, OPTION(OPTIMIZE for UNKNOWN, FAST 50) is removed and no other hints are used.

In my tests, while my data is different, this index was used

CREATE INDEX IX_Notify_Company_Link_ID
ON [dbo].[Record Link](Notify,Company,[Link ID])
INCLUDE([To User ID]);

This could not be the most optimal index for your dataset! Used solely as a way to show that the plan guide works. YMMV

A seek predicate on both Notify and Company where used.
They are not very selective in my dataset, resulting in many rows read.

Part of the new plan

PasteThePlan

Comment by @PaulWhite

  You might find an addition benefit in parameter embedding using OPTION
  (RECOMPILE) in the plan guide.

You would have to change the @hints parameter in the plan guide to

    @hints = 'OPTION(RECOMPILE)';

Keep in mind that your query plan will be recompiled each time, but if it is not executed that frequently it should not be a problem.

Plan with option recompile

A better plan was used, with seek predicates on all 3 key colums.

PasteThePlan

Test Query used

SET STATISTICS IO, TIME ON;
EXEC SP_EXECUTESQL N'SELECT  TOP (@0) "timestamp","Link ID","Record ID","URL1","URL2","URL3","URL4","Description","Type","Created","User ID","Company","Notify","To User ID"
FROM test.dbo."Record Link" WITH(READUNCOMMITTED)
WHERE ("Link ID">@1 AND (("Company"=@2 OR "Company"=@3) AND "Notify"=@4 AND ("To User ID" COLLATE Latin1_General_100_CI_AI LIKE @5 OR "To User ID" COLLATE Latin1_General_100_CI_AI LIKE @6)))
ORDER BY "Link ID" ASC OPTION(OPTIMIZE for UNKNOWN, FAST 50)',N'@0 int, @1 int, @2  [nvarchar](30), @3  [nvarchar](30), @4 tinyint, @5 nvarchar(264),@6 nvarchar(264)',@0=100,@1 = 99 ,@2  = 'NNNNNNNV',@3 = 'NNNNNNN',@4=1,@5='NNNNNNN1',@6='NNNNNNN2'

Test Data used

CREATE TABLE [dbo].[Record Link](
    [timestamp] [timestamp] NOT NULL,
    [Link ID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
    [Record ID] [varbinary](448) NOT NULL,
    [URL1] [nvarchar](250) NOT NULL,
    [URL2] [nvarchar](250) NOT NULL,
    [URL3] [nvarchar](250) NOT NULL,
    [URL4] [nvarchar](250) NOT NULL,
    [Description] [nvarchar](250) NOT NULL,
    [Type] [int] NOT NULL,
    [Note] [image] NULL,
    [Created] [datetime] NOT NULL,
    [User ID] [nvarchar](132) NOT NULL,
    [Company] [nvarchar](30) NOT NULL,
    [Notify] [tinyint] NOT NULL,
    [To User ID] [nvarchar](132) NOT NULL,
 CONSTRAINT [Record Link$0] PRIMARY KEY CLUSTERED
(
    [Link ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET NOCOUNT ON;
DECLARE @I INT = 0;
WHILE @I <= 10000
BEGIN
INSERT INTO [dbo].[Record Link]
(

    [Record ID]   ,
    [URL1]    ,
    [URL2]    ,
    [URL3]    ,
    [URL4]    ,
    [Description]    ,
    [Type]  ,
    [Note]  ,
    [Created]   ,
    [User ID]   ,
    [Company]   ,
    [Notify]   ,
    [To User ID]   )
    VALUES(convert(varbinary(448),'NNNNNNN'),'NNNNNNN','NNNNNNN','NNNNNNN','NNNNNNN','NNNNNNN',@I,'NNNNNNN',GETDATE(),'NNNNNNN'+CAST(@i as nvarchar(10)),'NNNNNNN',1,'NNNNNNN'+CAST(@i as nvarchar(10)))
    SET @I += 1
    END
    SET NOCOUNT OFF
INSERT INTO [dbo].[Record Link]
(

    [Record ID]   ,
    [URL1]    ,
    [URL2]    ,
    [URL3]    ,
    [URL4]    ,
    [Description]    ,
    [Type]  ,
    [Note]  ,
    [Created]   ,
    [User ID]   ,
    [Company]   ,
    [Notify]   ,
    [To User ID]   )

    SELECT
     [Record ID]   ,
     [URL1] ,
     [URL2]    ,
     [URL3]    ,
     [URL4]    ,
     [Description] ,
     [Type]  ,
     [Note]  ,
     [Created]   ,
     [User ID]   ,
     [Company]   ,
     [Notify]   ,
     [To User ID]
     FROM   [dbo].[Record Link]
     GO 7


Mysql backup from ibdata1, ib_logfile1, ib_logfile0 I accidentally deleted my word press mysql DB. Below is the list of log files that i see in my directory where i am hoping i can get back some of my data.

I actually converted the first file (mysql-bin.000001) to sql and saw the create statements for wordpress tables. That made me hopeful about the fact that i might be able to recover all my data. However, i ran all the mysql commands in the below mysql binary log files, and could only bring up a very old version of the wordpress blog.

Can i recover my data using the ibdata1, ib_logfile1, ib_logfile0 files?

i re-ran the sql statements in the binary log files on the same DB instance (which gave me a older version of my wordpress tables)

Ouch.  How much hope there was for recovery depended on whether your tables were using the MyISAM or the InnoDB storage engine.  If MyISAM, then... "none," and if InnoDB, then "some."

MyISAM, the older engine, stores its data in files named after the table, with an MYD extension, while InnoDB stores its data either in files named after the table, with an ibd extension, or in the ibdata1 file, which houses the system tablespace, depending on the setting of innodb_file_per_table in configuration.  The definition of each the table (column names, data types, index definitions), for either engine, is stored in files named after the tables, with an frm extension.

The large size of your ibdata1 file suggested that maybe you didn't have innodb_file_per_table enabled, and that some variation of the following steps might have worked, if you were indeed using InnoDB without innodb_file_per_table:

Make a backup copy of everything.
Set up a separate, new MySQL server.
Apply the binlogs you have to the new server, in the interest of recreating the .frm files for the table definitions, and then manually altering any tables to make any changes you recall that aren't accounted for in the older binlogs.
Stop the new server.
Copy directories inside your data directory and their .frm files to the appropriate places on the old server
Start the old server, using innodb_force_recovery if needed, to see how well InnoDB would manage to pair up the .frm files with the table data in ibdata1, which it should, if the table definitions were the same.

Since you have already created new tables, presumably with the same names, on the existing server, any hope for that plan to have succeeded are likely to be eliminated, or significantly diminished to the point that it is unlikely to be worth trying, since your best hope if you tried it would be to see what you are seeing now -- old data that you inserted into tables you created from the binary logs.

So, what's plan B?  To borrow a fitting phrase from the source I'll cite:

  "It is a tedious process that requires an understanding of InnoDB's internal data storage format, C programming, and quite a bit of intuition and experience."

http://www.percona.com/docs/wiki/innodb-data-recovery-tool:start

The Percona InnoDB Data Recovery Tool is the only meaningful approach I can think of that might get you where you need to be... or any comparable tools, assuming there are comparable tools.  I don't see any simple path... you're going to have to go "low level" if you want to rescue your data.

You have deleted important files, and in an effort to recover from the first problem, you have altered the one remaining file that might have been salvageable, and I'm not entirely sure how InnoDB will have handled those abandoned tables in its internal structures.  The data may still be hiding.

There is one reason for optimism, though, and that is in the strings utility.  The link I've provided is not to the genuine GNU "strings" utility, it's Microsoft's analogous semi-equivalent, but the point of this tool is to display strings embedded in files that the internal algorithm thinks might be human-meaningful strings.

There are two uses for this:  one, you can try using it on your ibdata1 file (it only reads, shouldn't modify) to see what your eyeballs tell you about what you find in there.  It offers essentially a brute-force window into the raw content living in the file, and if you find text in there that is newer than anything you put in using the binlog files, that's a good sign that time spent with the Percona tools could be worthwhile because the old data is actually in the file.  If you see nothing at all that you recognize, there may be a problem with the method (though I've verified it works on Linux with genuine GNU "strings"), but if you see old content and not new, then the new content is likely gone.

Of course, the other potential use of "strings" if you do see the content and if it's primarily content that is the most valuable thing you've lost -- not configuration and customization -- then you could just use "strings" to extract the raw content, redirect it into another file, and proceed with a new wordpress installation where you could use that raw text to recreate things.

If the data is "that" important, Percona and SkySQL (I'm not affiliated with either company, but those seem to be the two big players in MySQL space) and other companies offer paid recovery services.


Postgresql server causing a lot of IO activity I have a postgresql server running on a small linode partition. It is running a web app for a site which gets on average 0 users / minute (it has not launched yet). At one point, I loaded up the database with a lot of data, to do a stress test. I have since deleted the data and now have a few hundred megabyte DB just sitting there without anyone asking anything of it.

The linode server is reporting an average IO rate of 2.6k blocks a second. I ran pidstat -d 10 90 and figured out that the culprit was postgres:

Average:          PID   kB_rd/s   kB_wr/s kB_ccwr/s  Command
Average:        28836   1251.31      0.00      0.00  postgres

Further digging shows that approximately once every three minutes postgresql decides to do some heavy lifting (a short burst of reading at 21mb/s).

I know the bare minimum when it comes to DB administration and have an issue. My research unearthed things like VACUUMing and such. However, I am not sure what is happening, why, and how to stop it. There is absolutely no load on this machine. Can anyone point me in the right direction?

If you have added a lot of data and later deleted it, most likely Postgres starts auto-vacuuming process on your tables.

Auto-vacuum is designed to be rather gentle and not put heavy load on running server - otherwise it will significantly reduce system performance if it was serving live data.

Since your database is mostly empty and does not have any clients, you can fix this by performing full vacuum:

VACUUM FULL VERBOSE ANALYZE;

On a few hundred megabytes database it should not take more than a few minutes, and should stop any further activity on your database.

However, it is not recommended to perform full vacuum on production database because it locks tables as it goes - do simple VACUUM instead, or rely on auto-vacuum.


mysqldump stuck at "Connecting to" I'm having a weird problem with mysqldump. I have a docker instance of mysql (5.7) to which I can connect just fine (to any database). I can also use mysqldump to dump the databases except one - after executing mysqldump on that database it simply hangs on "Connecting to " and does nothing.

I use simple:

mysqldump --routines=true -v -h <host> -u root -p<pass> <database>

I also tried adding: --single-transaction or -l without much success.

I can connect to this database just fine (if I switch mysqldump to mysql).

I'm not really sure how to debug this issue further as there are no errors nor log entries.

OK, so show processlist; revealed that some queries got stuck in "Waiting to lock metadata" on that table. show engine innodb status \G helped identify the offending transaction and then simple KILL <id> unlocked the system.