SQL Service Broker and AlwaysOn Issue I'm having an issue setting up my current environment using AlwaysOn and SSB and I'll try to explain as best as I can here:

Current environment:

Server A             Server B
database x(Pub)      database x (Sub)
                     database y

Our app runs on database x. We have transactional replication setup to make database x available on Server B.
Database y is used to receive data from another app. We use it to process this data and just send the necessary information to database x. The replica of database x on Server B helps us to validate all the information and make sure we won't send any invalid data.

To communicate between database y and database x we use SSB. Why? It's faster.

We are moving to AlwaysOn and we'd like to maintain the current setup and add the failover cluster technology along with it.

So we come to the problem: AlwaysOn does not let us choose what we want to have on the replica. So it has everything, including the SSB objects (Services, Queues, etc.)
When we set up the databases and try to communicate database y and x, we get this error on transmission_queue:

  Database x is read-only mode

So SSB is using the local services we have on the replica of database x in Server B instead of looking at our routes that tell him to go to database x in Server A.

One alternative we found is to create a new instance for database y. I don't like the approach and from what I've read we also lose the automated failover from AlwaysOn with two instances and we'd have to do it manually.

My personal guess is that SSB Always uses the local services and I have no way out of this. One thing I have considered is removing the AutoCreatedLocal route from Server B and see if that Works (still have to try) but if it does, I'm not sure how I'll make the setup when it does the failover. I'll probably have to have a script to run and create it back.

Appreciate any help on this.

Editing to add more information:
Followed the link provided by Remus ( Service Broker with AlwaysOn ) to configure SSB with AlwaysOn. Basically just changed the route to use the AG instead of the specific server.

Still having the same error: database in read-only mode.

You need to setup a AG aware SSB route between x and y. See Service Broker with AlwaysOn Availability Groups. This route will deliver the messages over the network even when x and y are collocated on the same host. When failover occurs and x and y are on different hosts, the delivery continues uninterrupted.

  My personal guess is that SSB Always uses the local services and I have no way out of this.

You should read Service Broker Routing (although is a bit dated, as it misses the AG aware endpoints). SSB uses the local services (the "auto" routes) as a last resort, if no explicit route is declared. You always have a way "out" by explicitly creating a route.,


How to find which tables and views a user has access to? We have a SQL Server 2008 database and have restricted all tables and views away from a specific user ID.  Over the years we have granted back tables and views one at a time based on user need.

We had to do it this way because the vendor provides read/write access to the public role out of the box, so we had to create a role for this user, remove all access and grant back only what they needed.

Today we are going to create a replicated database containing only these tables and views so the user can run reporting on without degrading the production system.  The issue is I'm not sure what tables and views this user has access to, because many people have granted access one at a time over the years.

Is there a query to check this users access?

PS Im an apps developer so please feel free to explain this as elementary as needed.

EDIT - How the role and access was originally created

------------------------------
-- CREATE ROLE db_finrep_deny
------------------------------

DECLARE @RoleName sysname
set @RoleName = N'db_finrep_deny'
IF  EXISTS (SELECT * FROM sys.database_principals WHERE name = @RoleName AND type = 'R')
Begin
    DECLARE @RoleMemberName sysname
    DECLARE Member_Cursor CURSOR FOR
    select [name]
    from sys.database_principals
    where principal_id in (
        select member_principal_id
        from sys.database_role_members
        where role_principal_id in (
            select principal_id
            FROM sys.database_principals where [name] = @RoleName  AND type = 'R' ))

    OPEN Member_Cursor;

    FETCH NEXT FROM Member_Cursor
    into @RoleMemberName

    WHILE @@FETCH_STATUS = 0
    BEGIN

        exec sp_droprolemember @rolename=@RoleName, @membername= @RoleMemberName

        FETCH NEXT FROM Member_Cursor
        into @RoleMemberName
    END;

    CLOSE Member_Cursor;
    DEALLOCATE Member_Cursor;
End
GO
-- dropping the role itself
IF  EXISTS (SELECT * FROM sys.database_principals WHERE name = N'db_finrep_deny' AND type = 'R')
DROP ROLE [db_finrep_deny]
GO

------------------------------
-- Grant access (read/write/select/upd) to only select tables
------------------------------
CREATE ROLE [db_finrep_deny]
AUTHORIZATION [dbo]
GO
exec sp_addrolemember 'db_finrep_deny', 'finrep'
GO

-- run the dynamic sql generated by this statement
select  'DENY SELECT, INSERT, UPDATE, DELETE ON '
        + ss.name
        + '.'
        + st.name
        + ' TO db_finrep_deny;'
from sys.tables st
inner join sys.schemas ss on st.schema_id=ss.schema_id
where st.name not in ('TABLE1','TABLE2','TABLE3','TABLE4')
order by ss.name, st.name;

grant select on dbo.vwFINMASTER to db_finrep_deny;

Aaron - this is what I get when I run your query, it shows only views, but no tables

(No column name)    name    permission_name
dbo     dtproperties    REFERENCES
dbo     vwFINMASTER     SELECT
dbo     vwLEDGERVIEW    SELECT

You can use the function fn_my_permissions and impersonate the user to see which objects they have access to. You may want to expand the types to procedures and the various types of functions...

 USE Your_Database;
 GO
 EXECUTE AS USER = N'the_user_name';
 GO
 SELECT
    s.name,
    o.name,
    p.[permission_name]
 FROM sys.objects AS o
 INNER JOIN sys.schemas AS s
 ON o.[schema_id] = s.[schema_id]
 CROSS APPLY sys.fn_my_permissions(QUOTENAME(s.name)
   + N'.' + QUOTENAME(o.name), N'OBJECT') AS p
   WHERE o.[type] IN (N'U', N'V') -- tables and views
   AND p.subentity_name = N''; -- ignore column permissions
 GO
 REVERT;

Only tested on compat level 100+; you may need to structure that slightly differently in older compatibility modes (see the first example here).

Since your actual role has been granted a bunch of implicit access to tables through authorization, perhaps try:

-- all the tables and views in the system
SELECT
  schemaName = s.name,
  objectName = o.name,
  o.[object_id]
FROM sys.objects AS o
INNER JOIN sys.schemas AS s
  ON o.[schema_id] = s.[schema_id]
WHERE type IN (N'U',N'V')
AND NOT EXISTS
(
  -- except those that have been *explicitly* denied
  SELECT 1
  FROM sys.database_permissions AS p
  WHERE p.class = 1
  AND p.state_desc = N'DENY'
  AND p.major_id = o.[object_id]
  AND p.grantee_principal_id IN (USER_ID(N'db_finrep_deny'),USER_ID(N'finrep'))
);


Get last ID of repeated values based on type and most recent sequence As I am learning sql I am stuck on this one:

I have this table:

I am trying to get to this table:

The criteria is pick any ID that has Type A and its grade based on the most recent seq.

In this case you should generate a sub-query with the aggregated values:

SELECT
    ID,
    TYPE,
    MAX(SEQ) SEQ,
    COUNT(*) CNT
FROM
    T
GROUP BY
    ID,
    TYPE;

ID | TYPE | SEQ | CNT
-: | :--- | --: | --:
 3 | A    |   2 |   2
 1 | A    |   4 |   4
 2 | B    |   2 |   2

And then join it with your table ON SEQ = MAX(SEQ):

SELECT
    T1.ID,
    T1.TYPE,
    T1.SEQ,
    T1.GRADE,
    T2.CNT
FROM
    T T1
JOIN
    (SELECT
         ID,
         TYPE,
         MAX(SEQ) MAX_SEQ,
         COUNT(*) CNT
     FROM
         T
     GROUP BY
         ID,
         TYPE) T2
     ON T2.ID = T1.ID
     AND T2.TYPE = T1.TYPE
     AND T1.SEQ = T2.MAX_SEQ
WHERE
    T1.TYPE = 'A';

ID | TYPE | SEQ | GRADE | CNT
-: | :--- | --: | :---- | --:
 3 | A    |   2 | W     |   2
 1 | A    |   4 | C     |   4

db<>fiddle here


How many operations SQL Server support per connection I am making a multi threaded application in which I will use a single SQL Server connection and perform select, insert, update and delete.

So once my application starts, it will open connection with SQL Server and then multiple threads will run each doing select, insert, update and delete (in no particular order) on multiple tables using the same connection.

I want to ask how many operations can SQL Server handle for a single connection?

a multi threaded application in which I will use a single SQL Server connection

Bad idea. A database connection usually is not thread-safe, and as you suspect, at any one time one connection can only execute one statement. You should open a separate connection for each application thread instead, or at least use a connection pool shared between the threads.


Not lowering avg fragmentation I have a script which iterates through all database and underlying tables. With this script i want to rebuild/reorganize the indexes and i am testing this on AdventureWorks2017 database. So after executing the script, lot of indexes have the same results (before 50%, after 50%) and i don't know why? Can someone explain to me why indexes are not fragmented or the script is not good? Thanks

WHILE @@FETCH_STATUS = 0
BEGIN
-- > 30 rebuils
IF @avg_fragmentation_percent > 30
BEGIN
     -- > 30 rebuils
     SET @SQLString = 'USE  ' +'['+ @Database_name +']'+ CHAR (13 ) +
     'ALTER INDEX ' + LTRIM(RTRIM(@IndexName)) + ' ON '+LTRIM(RTRIM(@SchemaName)) +'.' + LTRIM(RTRIM(@TableName)) + ' REBUILD PARTITION = ALL'
     PRINT @SQLString
     EXEC (@SQLString)
END
ELSE
BEGIN
 --<30 reorganize
       SET @SQLString = 'USE  ' +'['+ @Database_name +']'+ CHAR (13 ) +
      'ALTER INDEX ' + LTRIM(RTRIM(@IndexName))+ ' ON '+LTRIM(RTRIM(@SchemaName)) +'.'+ LTRIM(RTRIM(@TableName)) + ' REORGANIZE  PARTITION = ALL'
       PRINT @SQLString
       EXEC (@SQLString)

       SET @SQLString = 'USE  ' +'['+ @Database_name  +']'+ Char (13 ) +
       'UPDATE STATISTICS  ' +LTRIM(RTRIM(@SchemaName)) +'.'+ Ltrim(Rtrim(@TableName)) + ' WITH FULLSCAN'
       PRINT @SQLString
       EXEC (@SQLString)
   END
FETCH NEXT FROM dbmaint INTO @TableName, @Database_name, @IndexName, @IndexID,@avg_fragmentation_percent ,@SchemaName
END

I am not sure from where you have got this script however it seems you are trying to rebuild indexes if the fragmentation level is more than 30 and reorganize if the same is less than 30. Your script is actually command to issue reorg and rebuild statement based on  other part of code which is not copied here.

You may try below piece of code for inquiring the fragmentation level within a given database:

1st Query:

SELECT dbschemas.[name] as 'Schema',
dbtables.[name] as 'Table',
dbindexes.[name] as 'Index',
indexstats.alloc_unit_type_desc,
indexstats.avg_fragmentation_in_percent,
indexstats.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]
INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
AND indexstats.index_id = dbindexes.index_id
WHERE indexstats.database_id = DB_ID()
and indexstats.page_count >=1000 and
dbindexes.name is not null
ORDER BY indexstats.avg_fragmentation_in_percent desc

2nd Query:

DECLARE @DatabaseID int

SET @DatabaseID = DB_ID()

SELECT DB_NAME(@DatabaseID) AS DatabaseName,
       schemas.[name] AS SchemaName,
       objects.[name] AS ObjectName,
       indexes.[name] AS IndexName,
       objects.type_desc AS ObjectType,
       indexes.type_desc AS IndexType,
       dm_db_index_physical_stats.partition_number AS PartitionNumber,
       dm_db_index_physical_stats.page_count AS [PageCount],
       dm_db_index_physical_stats.avg_fragmentation_in_percent AS AvgFragmentationInPercent
FROM sys.dm_db_index_physical_stats (@DatabaseID, NULL, NULL, NULL, 'LIMITED') dm_db_index_physical_stats
INNER JOIN sys.indexes indexes ON dm_db_index_physical_stats.[object_id] = indexes.[object_id] AND dm_db_index_physical_stats.index_id = indexes.index_id
INNER JOIN sys.objects objects ON indexes.[object_id] = objects.[object_id]
INNER JOIN sys.schemas schemas ON objects.[schema_id] = schemas.[schema_id]
WHERE objects.[type] IN('U','V')
AND objects.is_ms_shipped = 0
AND indexes.[type] IN(1,2,3,4)
AND indexes.is_disabled = 0
AND indexes.is_hypothetical = 0
AND dm_db_index_physical_stats.alloc_unit_type_desc = 'IN_ROW_DATA'
AND dm_db_index_physical_stats.index_level = 0
AND dm_db_index_physical_stats.page_count >= 1000

Kindly note that, page count dictates how big is the table and contains how many pages. For performing reorg or rebuild, you may check wonderful script from Mr. Ola Hallengren and use them for your maintenance job, here also you can customize the fragmentation level on which you want to perform reorg or rebuild, it also has option of offline and online, you may read more details at this link.

https://ola.hallengren.com/

You may also check the best practice and possible impacts on website of Mr. Brent Ozar and can be referred as below:

https://www.brentozar.com/archive/2017/12/index-maintenance-madness/

If fragmentation is not showing showing any sign of improvement, that could mean its a very small table having page count much lesser than 1000. Standard practice is to consider only those tables which is at least 1000 pages or more.

Kindly refer below links explaining same:

Why is my database still fragmented after I rebuilt and reindexed everything?

SQL Server 2012, rebuild not lowering avg fragmentation

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/c419f809-c4c6-4c7f-9f36-c9a56d21d37a/index-still-fragmented-after-rebuild?forum=sqldatabaseengine

In case, you have bigger tables however doesn't have clustered index meaning its a heap then you should go for table rebuild with below command:

Alter table SchemaName.TableName rebuild

You can read more details about that on this link.

The most important aspect is to see "How to detect whether index fragmentation affects SQL Server performance". please check more details about that here.

Hope above helps.


Keep the foreign key constraint using soft delete I have a product table and it is foreign key for product ingredients table.
Is there a way to check if it is fk referenced before making isdeleted = true without manually querying for it?

You could require the UPDATE to reference a subquery that will return no results if references exist. Then, if 0 rows are updated, you can raise a message to the UI to report an error.


Postgres slow query with order by, index and limit I am trying to improve the performance of a postgres(9.6) query. Here is my schema and table contains about 60 million rows.

          Column          |            Type             | Modifiers
--------------------------+-----------------------------+-----------
 transaction_id           | text                        | not null
 network_merchant_name    | text                        |
 network_merchant_id      | text                        |
 network_merchant_mcc     | integer                     |
 network_merchant_country | text                        |
 issuer_country           | text                        |
 merchant_id              | text                        |
 remapped_merchant_id     | text                        |
 created_at               | timestamp without time zone |
 updated_at               | timestamp without time zone |
 remapped_at              | timestamp without time zone |
Indexes:
    "mapped_transactions_pkey" PRIMARY KEY, btree (transaction_id)
    "ix_mapped_transactions_remapped_at" btree (remapped_at NULLS FIRST)

Here is the query I am trying to execute.

SELECT *
FROM mapped_transactions
ORDER BY remapped_at ASC NULLS FIRST
LIMIT 10000;

Here is the query plan:

    QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.57..1511.67 rows=10000 width=146) (actual time=327049.374..327345.341 rows=10000 loops=1)
   Buffers: shared hit=574937 read=210425 dirtied=356 written=4457
   I/O Timings: read=146625.381 write=59.637
   ->  Index Scan using ix_mapped_transactions_remapped_at on mapped_transactions  (cost=0.57..16190862.91 rows=107145960 width=146) (actual time=327049.364..327339.402 rows=10000 loops=1)
         Buffers: shared hit=574937 read=210425 dirtied=356 written=4457
         I/O Timings: read=146625.381 write=59.637
 Planning time: 0.125 ms
 Execution time: 327348.322 ms
(8 rows)

I don't understand why it takes so much time when there is an index on the remapped_atcolumn.

On the other hand if I order in reverse it is fast.

SELECT *
FROM mapped_transactions
ORDER BY remapped_at DESC NULLS LAST
LIMIT 10000;

and the plan is:

QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.57..1511.67 rows=10000 width=146) (actual time=0.020..9.268 rows=10000 loops=1)
   Buffers: shared hit=1802
   ->  Index Scan Backward using ix_mapped_transactions_remapped_at on mapped_transactions  (cost=0.57..16190848.04 rows=107145866 width=146) (actual time=0.018..4.759 rows=10000 loops=1)
         Buffers: shared hit=1802
 Planning time: 0.080 ms
 Execution time: 11.561 ms
(6 rows)

Can someone please help me how I can improve the performance of the first query?

Updates

I solved this by rebuilding the table and reindexing the data. VACUUM FULL ANALYZE was not an option because the table was being used and I did not want to lock it.
The performance of the index is deteriorating very fast. I rebuilt the index 7 hours ago and the performance was good. Now the queries answer in about 10s. Please note that that this table is write heavy. How can I make the index fast? Do I have to re-index the table frequently? There are no deletions in this table but a lot of updates.

I can't confirm with PostgreSQL 9.5. So this may be a regression. Sample data,

CREATE TABLE mapped_transactions(remapped_at)
AS
  SELECT CASE WHEN x::int % 42 = 0 THEN null ELSE x::int END
  FROM generate_series( 1, 40e6 )
    AS gs(x);

CREATE INDEX ON mapped_transactions (remapped_at NULLS FIRST);
VACUUM ANALYZE mapped_transactions;

Here we do NULLS FIRST and it works,

EXPLAIN ANALYZE
SELECT *
FROM mapped_transactions
ORDER BY remapped_at ASC NULLS FIRST LIMIT 10000;
                                                                                QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.56..260.24 rows=10000 width=4) (actual time=0.069..4.308 rows=10000 loops=1)
   ->  Index Only Scan using mapped_transactions_remapped_at_idx on mapped_transactions  (cost=0.56..1038716.81 rows=40000016 width=4) (actual time=0.067..2.740 rows=10000 loops=1)
         Heap Fetches: 0
 Planning time: 0.232 ms
 Execution time: 5.017 ms
(5 rows)

Trying DESC NULLS LAST

EXPLAIN ANALYZE
SELECT * FROM mapped_transactions
ORDER BY remapped_at DESC NULLS LAST LIMIT 10000;
                                                                                QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.56..260.24 rows=10000 width=4) (actual time=0.073..4.429 rows=10000 loops=1)
   ->  Index Only Scan Backward using mapped_transactions_remapped_at_idx on mapped_transactions  (cost=0.56..1038716.81 rows=40000016 width=4) (actual time=0.071..2.865 rows=10000 loops=1)
         Heap Fetches: 0
 Planning time: 0.114 ms
 Execution time: 5.137 ms
(5 rows)

You may have to work up a test case. It's not evident what you're experiencing.


Can't create indexed view against table with masked columns I am trying to create an index on a view that references a table with a masked column (SQL Server 2016). The masked column is not the only one in that table, and it's not used in the view.

create unique clustered index [IX_Name]
on dbo.vw_ViewName(SomeUniqueId)

I get this error:

  The index on view 'dbo.vw_ViewName' cannot be created
  because the view is referencing table 'dbo.TableName' with masked
  columns.

In another environment, where masking is not enabled, the index is created successfully.

I looked through about four pages of Google results and haven't found any sensible description of the error. I would appreciate any knowledge transfer on the error and why it is not possible to create such index.

Here is some SQL to reproduce the issue:

drop view if exists dbo.vw_Aggregate
drop table if exists dbo.MainTable, dbo.SecondaryTable
go

create table dbo.MainTable
(
    MainTableId uniqueidentifier primary key,
    SomeExternalId uniqueidentifier,
    SecondaryTableId uniqueidentifier
)
go

create table dbo.SecondaryTable
(
    SecondaryTableId uniqueidentifier primary key,
    CreatedOn datetime,
    Amount decimal(19, 8),
    -- the below column produces error,
    -- if commented out - there is no error
    [Description] nvarchar(max) masked with (function = 'default()'),
    Dummy int
)
go

create view dbo.vw_Aggregate with schemabinding
as
    select AggregateId = m.MainTableId,
           m.SomeExternalId,
           s.CreatedOn,
           s.Amount
    from dbo.MainTable m
    inner join dbo.SecondaryTable s on s.SecondaryTableId = m.SecondaryTableId
go

create unique clustered index [IX_dbo.vw_Aggregate(AggregateId)]
on dbo.vw_Aggregate(AggregateId)
go

As far as I know, this is not supported, and you can only use tables in this case.

As found in following article which is using Row level security which is not the same as Dynamic Data masking, but it might be linked to each other.

https://www.mssqltips.com/sqlservertip/4005/sql-server-2016-row-level-security-limitations-performance-and-troubleshooting/ by Aaron Bertrand:

Indexed Views

Row-Level Security is not compatible with tables that have indexed views. If you try to create an indexed view against a table with a security policy, you will get the following error:

  Msg 33266, Level 16 The index on the view 'view' cannot be created
  because the view is  referencing table 'table' that is referenced by a
  security policy.

And no, you can't chicken-and-egg this to work around it; if a table is referenced by an indexed view, a security policy cannot be applied:

  Msg 33265, Level 16 The security policy 'policy' cannot have a
  predicate on table 'table' because this table is referenced by the
  indexed view 'view'.

Note: You may come across the same types of problems if trying to apply a security policy to a table that is reference by a partitioned view.

As Dynamic Data Masking & Row Level Security were released in the same release maybe the limitations are linked to each other.

The Error

The error also clearly says that it is not allowed to have an index on a view linked to a table with a masked column. In the documentation however nothing can be found about it.

So my guess is that it is just not supported.


Extract from Oracle at given time I have a requirement to retrieve (extract) data from an Oracle database (either 9i or 10g) as at a given point in time.

I will need access to the data at this point of time for several days afterwards (as the database is large enough that the extract process will take several days to complete). The database is also in constant use.

For example, on January 3rd, I may want to query data as it was on January 1st and I may need access to the data as it was on January 1st for several days (maybe until about January 7th). What is the best way to achieve this?

I am not a DBA, but have looked into a number of solutions, that may form the basis of a solution, but would appreciate any advice on my best course of action.

I am aware of flashback and the ability to query a table as it was at a given point in time. I am, however aware that keeping the amount of history I'd need to (probably at least 7 days worth) is likely to make this infeasible based on the size and high usage of the database. I am also aware that when you set flashback to record a given number of days worth of data, you may end up with significantly less than this based on high usage, so this would seem like a risky approach anyway?

I am also aware of creating a Guaranteed Restore Point, but that this will only be an option if my database is 10g or above(?) If I did this, I believe I would have to turn Flashback off (?) as if I don't, flashback will have to keep logs all the way back to the time of the guaranteed restore point (and so in this example I'd end up with at least 7 days worth of history and risk the database running out of disk space). With a guaranteed restore point in place, is it possible to run a SELECT query on a table as at this restore point using something like the following:

CREATE RESTORE POINT test_restore_point GUARANTEE FLASHBACK DATABASE;

SELECT * FROM tbl1 AS OF test_restore_point;

The only examples I have seen involve actually restoring the database to the restore point (which I don't want to do) rather than querying from it. I am unable to test this for myself as I currently only have access to a 9i database.

I'd be very grateful for any advice on how I can achieve my objectives using this functionality or any other.

Thanks

A query like
SELECT * FROM tbl1 AS OF test_restore_point;
is not possible.
Restorepoints can be used with the command FLASHBACK DATABASE. FLASHBACK DATABASE works similar like a restore.

First the whole database will be brought to a state before the time of the restore point. But this is not accomplished by copying a whole backup over each data file but it is achieved by applying flashback logs. For each data block that was modified after making the   restore point a backup was saved in the flashback logs.

Then the redo files are applied to roll forward the database to the restore point.

So when you flashback to a restore point the whole database is set to the point in time of the restore point. This is not what you want.
You can use flashback queries to queries old data but this data can only be supplied by the database if it is still found in the undo tablespace.
A reliable mechanism to hold the original data for a fixed period of time is Total recall/Flashback Data Archive. As Justin Cave pointed out this Feature is only available for Oracle 11.1 or higher versions.
The following licensing rules hold

Flashback Data Archive (formerly known as Total Recall)
For releases earlier than Oracle Database 11g Release 2 (11.2.0.4): You must license the Oracle Advanced Compression option to use Flashback Data Archive.
Beginning with Oracle Database 11g Release 2 (11.2.0.4): Flashback Data Archive—without history table optimization—is available in all editions.

A Flashback Data Archive holds the undo information of a selected table for a fixed period of time but it used a dedicated tablespace and is therefore not influenced by the cleanup meachanisms of the undo segments.
The table can be queried in the usual way with a flashback query.
You need Oracle Enterprise Edition and an additional license to use this feature.


If a Parallelism Exchange Event deadlock is victim-less, is it a problem? We're seeing a lot of these Intra-Query Parallel Thread Deadlocks in our Production environment (SQL Server 2012 SP2 - yes...I know...), however when looking at the Deadlock XML that has been captured via Extended Events, the victim-list is empty.

<victim-list />

The deadlocking appears to be between 4 threads, two with the WaitType="e_waitPipeNewRow" and two with the WaitType="e_waitPipeGetRow".

 <resource-list>
  <exchangeEvent id="Pipe13904cb620" WaitType="e_waitPipeNewRow" nodeId="19">
   <owner-list>
    <owner id="process4649868" />
   </owner-list>
   <waiter-list>
    <waiter id="process40eb498" />
   </waiter-list>
  </exchangeEvent>
  <exchangeEvent id="Pipe30670d480" WaitType="e_waitPipeNewRow" nodeId="21">
   <owner-list>
    <owner id="process368ecf8" />
   </owner-list>
   <waiter-list>
    <waiter id="process46a0cf8" />
   </waiter-list>
  </exchangeEvent>
  <exchangeEvent id="Pipe13904cb4e0" WaitType="e_waitPipeGetRow" nodeId="19">
   <owner-list>
    <owner id="process40eb498" />
   </owner-list>
   <waiter-list>
    <waiter id="process368ecf8" />
   </waiter-list>
  </exchangeEvent>
  <exchangeEvent id="Pipe4a106e060" WaitType="e_waitPipeGetRow" nodeId="21">
   <owner-list>
    <owner id="process46a0cf8" />
   </owner-list>
   <waiter-list>
    <waiter id="process4649868" />
   </waiter-list>
  </exchangeEvent>
 </resource-list>

So:

The Victim List is empty
The application running the query does not error and completes the query
As far as we can see, there is no obvious issue, other than that the graph is captured

Therefore, is this anything to worry about other than noise?

Edit: Thanks to Paul's answer, I can see where the issue likely occurs and appears to resolve itself with the tempdb spill.

I wouldn't be surprised if this is the way the deadlock graph looks when an intra-query parallel deadlock is resolved by an exchange spill (so there is no victim, except performance).
You could confirm this theory by capturing exchange spills and matching them up (or not) to the deadlock.
Writing exchange buffers to tempdb to resolve a deadlock is not ideal. Look to eliminate sequences of order-preserving operations in the execution plan (e.g. order-preserving exchanges feeding a parallel merge join). Unless it's not causing a noticeable performance problem, and you have other things to worry about.

Out of interest, is this problem likely to be exacerbated by high fragmentation/outdated statistics?

Fragmentation, no. Outdated Statistics: not in any specific sense I can think of, no. Of course unrepresentative stats are rarely a good thing generally.
The fundamental issue here is that parallelism works best when there are as few dependencies between threads as possible; preserved ordering introduces rather nasty dependencies. Things can easily get gummed up, and the only way to clear the logjam is to spill a bunch of rows held at exchanges to tempdb.

Note
Intra-query parallel deadlocks no longer generate xml graphs when the deadlock can be resolved with an exchange spill, following a fix released for Cumulative Update 10 for SQL Server 2017 and Cumulative Update 2 for SQL Server 2016 SP2.


How to do Oracle database cluster / Failover without shared storage I have 2 or 3 RHEL 6 servers and I don't want to have a dedicated storage device. I think maybe you can replicate changes from one node of a cluster to another in real-time.

I want to cluster them so if 1 instance fails it can failover to another instance. Maybe even 2 instances running and a load balancer server.

Is there any solution to this kind of cluster?

In this case, you'll be wanting to use a standby database / data guard, rather than RAC (which uses shared storage).

Read Data Guard Concepts and Administration in the Oracle Documentation.

There's a step-by-step installation guide on Oracle Base that walks through setting up a physical standby database here.


SQL Server 2012 Browser Service doesn't appear I've installed SQL Server 2012 and Management Studio Express. I attached the database that belongs my previous Project in Visual Studio. But I am having SqlException Error , I think the reason of the error is SQL Server. Because when I look at active services, I only see SQL Server VSS Writer. I can't see SQL Server Browser Service and other SQL Server services. How can I solve this problem? Thanks in advance.

Log file:

  Overall summary:
  Final result:                  Passed
  Exit code (Decimal):           0
  Start time:                    2014-07-27 21:42:30
  End time:                      2014-07-27 21:45:33
  Requested action:              Patch

Machine Properties:
  Machine name:                  KEREM
  Machine processor count:       8
  OS version:                    Future Windows Version
  OS service pack:
  OS region:                     Türkiye
  OS language:                   Türkçe (Türkiye)
  OS architecture:               x64
  Process architecture:          64 Bit
  OS clustered:                  No

Product features discovered:
  Product              Instance             Instance ID                    Feature                                  Language             Edition              Version         Clustered
  SQL Server 2012                                                          Management Tools - Basic                 1033                 Express Edition      11.0.2100.60    No
  SQL Server 2012                                                          LocalDB                                  1033                 Express Edition      11.1.3000.0     No

Package properties:
  Description:                   Microsoft SQL Server 2012 Service Pack 1
  ProductName:                   SQL Server 2012
  Type:                          RTM
  Version:                       11
  SPLevel:                       1
  KBArticle:                     KB2674319
  KBArticleHyperlink:            http://support.microsoft.com/?kbid=2674319
  PatchType:                     SP
  AssociatedHotfixBuild:         0
  Platform:                      x64
  PatchLevel:                    11.1.3000.0
  ProductVersion:                11.0.2100.60
  GDRReservedRange:              10.0.1000.0:10.0.1099.0;10.0.3000.0:10.0.3099.0
  PackageName:                   SQLServer2012-KB2674319-x64.exe

The Log you posted seems incomplete, also it is not the log for SQL Server installation but for patch update. You can see in the log

Requested action:   Patch

Anyways log is helpful because it shows below

Product features discovered:
  Product              Instance             Instance ID                    Feature                                  Language             Edition              Version         Clustered
  SQL Server 2012                                                          Management Tools - Basic                 1033                 Express Edition      11.0.2100.60    No
  SQL Server 2012                                                          LocalDB                                  1033                 Express Edition      11.1.3000.0     No

Whenever you run patch SQL Server writes in Log what all instances and features it has found on the current system and as per log you only installed

SQL Server management studio
Local DB not Database engine.

You need to download ENU\x64\SQLEXPR_x64_ENU.exe because your system is 64 bit from This Link

I am not sure error is because of missing Browser or missing Express Database Engine service. Browser service is installed with SQL Express database engine so if you are looking to connect to database engine not Local DB you need to to install Express DB engine.


About backing up Transaction logs I have understood the importance of transaction logs but still unclear on a few things:

I've taken back-up of a T-log using this statement:

BACKUP LOG myDB
TO DISK = 'I:\Log_Backup\myDB.TRN'
GO

Then i used the following statment to monitor the space used:

DBCC SQLPERF(LOGSPACE);
GO

Log spaced used which was previously large went down to 0.859 %

Now on the issue of backing up i have two questions:

Our TSM guy is backing up the folders: I:/Database and I:/Logs thrice a day. I:/Database is the folder with all the MDF-files and I:/Logs is the folder with all the LDF-files.
Shoud he instead be backing up I:/Log_Backup/ and I:/Logs?
Or should he be backing up only I:/Logs where all the actual logs reside.
In case of a disaster when i need to retore a DB, should i use the last back-up from I:/Log_Backup/ or from I:/Logs

What TSM guy is doing is taking File level backup of mdf and ldf file which does not guarantee point in time recovery. You are correct to say that he should also be backing up folder(s) which holds transaction log backup or other similar backups i.e I:/Log_Backup.
In case of disaster you would always want zero data loss or as much less data loss as possible. If you follow what TSM guy is doing you would be only able to attach database to point when it was Snapshot backed up. But with full backup and log backup you could do point in time restore(depending on level of disaster/corruption) and that would give you no or minimum data loss.

What you can do is you can ask TSM guys to also backup the folder holding full and transaction log backups.


Why/how did the behavior of extract(epoch...) from "timestamp without time zone" fields change between PostgreSQL 8.4 and 9.2? Context:

We currently run PostgreSQL 8.4 in production, and are testing 9.2 internally for a future upgrade. Some issues have emerged regarding dates. The servers that run 8.4 versus 9.2 are identical in every way other than the PostgreSQL versions and configs. The same exact data is stored on both sets of servers; it is transferred using pg_dump and pg_restore.

In one part of our database, we store dates in a field of type timestamp without time zone.

Problem:

When accessing dates from that field, an extract(epoch..) request returns very different results depending on the Postgres version, like so:

#On Postgres 8.4. entered_timestamp is a "timestamp without time zone" type column:
SELECT entered_timestamp, extract(epoch from entered_timestamp) AS entered_timestamp from <table row>

 entered_timestamp      | entered_timestamp
 ----------------------------+-------------------
 2012-11-01 06:01:39.699612 |  1351774899.69961

But...

#On Postgres 9.2. All tables, schema, and data are identical, the SELECT statement is identical to the previous one:
SELECT entered_timestamp, extract(epoch from entered_timestamp) AS entered_timestamp from <table row>

 entered_timestamp      | entered_timestamp
 ----------------------------+-------------------
 2012-11-01 06:01:39.699612 |  1351749699.69961

As you can see, the returned timestamps are identical, but the returned epoch numbers are not; in fact, they are off by several hours. The server dates and Linux time zones on both servers are identical (within a second of each other). The Postgres time zones are also identical: running SELECT current_setting('timezone') on both servers returns the same data as well. Running SELECT now() on both servers returns near-identical values.

Question:

How has the behavior of extract(epoch...) on fields of type
timestamp without time zone changed between PostgreSQL 8.4 and
PostgreSQL 9.2?
Why did this change occur?
Is there any way to prevent this change without altering my schema
or changing past data (i.e. a configuration-based fix)?

Testing this on my own machine, I get 1351749699.69961 as the result on both PostgreSQL 8.4.10 and 9.2.1.

With PostgreSQL 8.4.10, the result changes according to the session timezone. With 9.2, it does not.

Likely this is the effect of this change: Measure epoch of timestamp-without-time-zone from local not UTC midnight. which is noted as a 9.2 compatibility change.

The suggested workaround in the change notes is to cast the timestamp to a timestamptz first: not just a configuration change.

Personally, I find the 9.2 behaviour more comfortable. The Unix epoch is defined as UTC, so this effectively means that plain timestamp values are interpreted as UTC: which is how I use them anyway.


troubleshooting missing entries using log files Something somewhere (most likely the app) is causing rows to disappear.
I'd like to find if there is a query that would point me in the right direction as to what's going on.

It looks like my bin log was not turned on so I've updated my.conf to include the log

log_bin                 = ON
general_log_file        = /tmp/mysql.log
general_log             = 1

but this is only going to update it from this point forward.

Is there any other way of being able to go back to see older queries?

A DELETE (and other writes) will be captured by both logs (assuming you don't have binlog-do/ignore setting in my.cnf).  It would be more efficient to use the binlog, but it needs mysqlbinlog to read it.  The general_log is plain text, but it gets huge fast, since it is capturing everything (including reads).

I think both logs have the query time and the login name.  (I hope you are not doing everything as root.)

Neither can capture anything that happened when they were turned off.  So turn one or both on now, and hope that the naughty query comes along soon.


Session hanging with TM REQUEST and plan handle 0x0000 We have a periodic event where lots of connections hang on TM REQUEST (from sys.dm_os_waiting_tasks) and make no progress. KILL on the affected session_id does nothing

The server runs database mirroring (2008R2). Pausing and resuming mirroring fixes the issue when it happens.

The session stuck in this state are unkill'able. The load on the mirror is the usual, not bursty. Once a high number of connections are stuck in this state, the system freezes up and the mirror stops replicating (all queries get blocked).

Anyone seen this before and know what is causing it? There is nothing in the error logs.

TM stands for Transaction Manager. The current command being set to TM
Request means that the thread is currently servicing a DTC (Distributed Transaction Coordinator) request, either to enlist in a DTC transaction, or defect from it, or commit it, or so on. Distributed Transactions are not supported for Database Mirroring because it can't guarantee transaction integrity.  Suppress your DTC transactions and this should go away.


MS SQL 2017 cluster: impact of disabling distributed transactions for an availability group A company is having a Microsoft SQL Server 2017 Enterprise cluster (RTM-GDR, 64-bit,  14.0.2027.2, KB4505224) consisting of one Availability Group (AG) with two servers, a primary and a secondary.
A closed source application used by this company seems to work fine with the exception of one piece of functionality. When this functionality is used, the following error can be found in its log file:
Cannot use SAVE TRANSACTION within a distributed transaction

A possible workaround, according to an unrelated website, is to disable distributed transactions by:
ALTER AVAILABILITY GROUP MyaAG
   SET ( DTC_SUPPORT = NONE );

After testing, this seems to solve the issue. However, I am uncertain about the impact of this change.

What impact does this change have?
Is the cluster still active?
Is the data still replicated to the secondary server?
Does it have a positive or negative impact on the performance?
Has a safety feature just been disabled?

So unfortunate news for you is it sounds like this closed source application uses a potentially deprecated feature of SQL Server if it's using SAVE TRANSACTION, and was not designed to run for a database that's part of an Availability Group (or any kind of clustering for that matter). Even more unfortunate is your only three options are:

Thoroughly verify it isn't breaking anything when you use that functionality of the application. That should involve testing that functionality and measuring the results on your primary, and then failing over to your secondary and verifying the same results did in fact commit to that node as well, and the application is still working as expected on the secondary. If all's well, then just live with the error.

Reach out to the vendor and communicate to them that you're trying to run their application on a clustered server with an Availability Group and are receiving an error for a specific function of their application that appears to use a deprecated feature of SQL Server, and that you need their confirmation if this is ok, and/or does their application support Availability Groups. You'll probably want do this anyway, if you pick option 1.

Disable distributed transactions with SET ( DTC_SUPPORT = NONE ); as you've seen this removes the error, but it does make your Availability Group less reliable which I'll answer directly to your questions below.

Out of these options, your best bet is #2, so that you can work with the vendor and let them know the full situation in which you're trying to use their application. It sounds like Availability Groups aren't directly supported by it and they'd be able to confirm that. Unfortunately for you, that's just a constraint from the vendor, and there's not much else you can do other than the options I provided above, and/or don't use Availability Groups / clustering, rather have an alternative high availability plan in place such as maintaining a completely decoupled, second server, which you regularly restore backups to. (Obviously this is less than ideal.)
To directly answer your questions on disabling distributed transactions:

What impact does this change have?

There is a risk of data loss from unhardened (uncommitted) transactions when a failover event occurs from the primary to the secondary. While this risk is potentially small, and I believe only affects your distributed transactions themselves (such as this application functionality with the SAVE TRANSACTION code) it's still a risk. This is discussed in the Microsoft Books Online for Configure distributed transactions for an Always On availability group, specifically when they say:

SQL Server does not prevent distributed transactions for databases in an availability group - even when the availability group is not configured for distributed transactions. However when an availability group is not configured for distributed transactions, failover may not succeed in some situations. Specifically the new primary replica SQL Server instance may not be able to get the transaction outcome from DTC. To enable the SQL Server instance to get the outcome of in-doubt transactions from the DTC after failover, configure the availability group for distributed transactions.

Is the cluster still active?

Yes the cluster is still active and you should be able to easily test that by making a minor data change on the primary and watching it update on the secondary.

Is the data still replicated to the secondary server?

Yes, same answer as above.

Does it have a positive or negative impact on the performance?

Not relevant to the change, and probably measurably no difference.

Has a safety feature just been disabled?

Yes, your distributed transactions are at risk for data loss now if a failover were to occur when they were still active transactions (i.e. the secondary now becoming the primary wouldn't know if it's correct to rollback those transactions, rollforward or commit them), as discussed in my first answer to your questions. How much risk this is to you will depend on how important that data is for the feature that uses the SAVE TRANSACTION code, and anywhere else you may be explicitly using distributed transactions.


Accent marks/symbols not displaying correctly in MySQL database Anytime there's an accent mark (é or ') or symbol (&) in my database it displays as HTML code. The table CHARACTER SET is UTF-8 Unicode and the COLLATION is utf8mb4_general_ci, which should display those characters correctly. Any ideas why this is happening?

(MySQL version 5.7)

Current data

`company`| `firstname`| `lastname`
----------------------------------
A&amp;T  | Patrick    | O&#039;Brien

Expected

`company`| `firstname`| `lastname`
----------------------------------
A&T      | Patrick    | O'Brien

Thank you in advance!

Fix the data source to store actual characters into MySQL, not "HTML entities".

Where necessary, use output routines such as PHP's html_entities() and/or urlencode()


What is the best way to get all data for a date range, plus the last event just before the range? For this question assume the following:

There is a table of events [Event]
There is a table of employees [Employee]
There is a table that relates the two [EventEmployee] (EventID + EmployeeID)
Each event is assigned to n employees
These events can occur at random dates/times

I need to query for the events that start and/or end within a specified time frame. I also need to include the first event prior to the specified date range PER EMPLOYEE assigned to the event.

For example, if I query from 03/01/2014 to 04/01/2014 and I return 15 events with 5 employees assigned to each. I also need to return the most recent event prior to 03/01/2014 for each of those employees.

I am currently accomplishing this by:

Getting all of my events for the date range into a temp table
Selecting everything out of the temp table
UNION with a query that gets the DISTINCT [EmployeeID]s based on the temp table and does a CROSS APPLY on [Event] joined with [EventEmployee] to get the most recent event prior to the start date of the range.

Is there a more efficient way to accomplish this task?

Please tell me if some sample code would help the explanation.

EDIT:
My Query:

Assuming @StartDate and @EndDate are DATETIME parameters,

-- Get all events that start or end within a given time frame
SELECT
    e.[EventID],
    e.[EventStart],
    e.[EventEnd],
    e.[EventTypeID]
INTO #EventTemp
FROM
    [Events] AS e
WHERE
    ( e.[EventStart] >= @StartDate AND e.[EventStart] <= @EndDate )
    OR ( e.[EventEnd] >= @StartDate AND e.[EventEnd] <= @EndDate )

SELECT
    #EventTemp.[EventID],
    #EventTemp.[EventStart],
    #EventTemp.[EventEnd],
    #EventTemp.[EventTypeID]
FROM
    #EventTemp
UNION
SELECT
    PrevEvent.[EventID],
    PrevEvent.[EventStart],
    PrevEvent.[EventEnd],
    PrevEvent.[EventTypeID]
FROM
    -- Get a DISTINCT list of employeeIDs from those events
    (
        SELECT DISTINCT
            ee.[EmployeeID]
        FROM
            #EventTemp
            INNER JOIN [EventEmployee] AS ee ON
                #EventTemp.[EventID] = ee.[EventID]
    ) AS Emp
    -- Get the most recent prior event for each distinct employeeID
    CROSS APPLY (
        SELECT TOP 1
            e.[EventID],
            e.[EventStart],
            e.[EventEnd],
            e.[EventTypeID]
        FROM
            [Events] AS e
            INNER JOIN [EventEmployee] AS ee ON
                e.[EventID] = ee.[EventID]
        WHERE
            ee.[EmployeeID] = Emp.[EmployeeID]
            AND e.[EventEnd] < @StartDate
        ORDER BY
            e.[EventEnd] DESC
    ) AS PrevEvent

Lets say we end up with 4 events in the temp table #EventsTemp. Each of those events could be tied to any number of employees. For this example, lets assume between all the events in #EventsTemp we are dealing with 3 employees.

For the final select I would want to get the list of events from #TempEvents, in addition I would also want the most recent event, prior to @StartDate, that each of those 3 employees were at. If 2 of the employees were at the same event I would expect to get a single extra row for that event, plus an extra row for the 3rd employee:

EventID,EventStart,EventEnd,EventType
5,'2014-04-01 13:00','2014-04-01 15:00',1
6,'2014-04-01 16:00','2014-04-01 19:00',1
7,'2014-04-02 10:00','2014-04-02 14:00',1
8,'2014-04-02 16:00','2014-04-02 18:00',1
2,'2014-03-26 13:00','2014-03-26 15:00',1
1,'2014-03-25 10:00','2014-03-25 14:00',1

In this example, the first four rows were straight from #EventsTemp, and the last two rows are the distinct prior events attended by any combination of the employees that attended the original 4 events from #EventsTemp

I am going to assume that there isn't an index on the date columns, otherwise I think that the query would have been structured differently. If there is, you can probably find a better performing one than this.

The advantage of this query is that it can get all the data in one scan. The disadvantage is that it has to sort the data and join EventEmployee on the entire table. So as always, test with your own situation. This query also assumes that the MAX date is either unique or that equivalent rows would be acceptable.

USE AdventureWorks2012
GO
;
WITH Base AS (
   SELECT
      TransactionHistory.*
      ,ProductVendor.BusinessEntityID
      ,MAX(CASE WHEN TransactionDate < '2008-08-01' THEN TransactionDate END)
           OVER (PARTITION BY ProductVendor.BusinessEntityID) AS PreviousVendorTransaction
      ,COUNT(CASE WHEN TransactionDate >= '2008-08-01' THEN 1 END )
           OVER (PARTITION BY ProductVendor.BusinessEntityID) AS VendorAfterCutoff
   FROM
      Production.TransactionHistory
      -- Doesn't make the most sense, but I need a repeating relation
      INNER JOIN Purchasing.ProductVendor
         ON TransactionHistory.ProductID = ProductVendor.ProductID
),
Filtered AS (
   SELECT
      *
   FROM
      Base
   WHERE
      Base.TransactionDate >= '2008-08-01'
      OR (TransactionDate = PreviousVendorTransaction AND VendorAfterCutoff > 0)
)
SELECT DISTINCT
   TransactionID
   ,ProductID
   ,ReferenceOrderID
   ,ReferenceOrderLineID
   ,TransactionDate
   ,TransactionType
   ,Quantity
   ,ActualCost
   ,ModifiedDate
FROM
   Filtered

Edit:

Hmm, I think I may have to take back my comment on structuring it differently if there are indexes. The other suggestions that I have are probably fairly minor.

Make sure the query is using the indexes you're expecting it to. Start and End date to build temp table, end date to drive the previous event loop.
If the query to build the temp table is doing a lookup on the clustered index, it may be better to hold off and do that as part of the main query.
Try using a cte instead of a temp table. I think that a cte might be more competitive with the way that the query is structured below.
If you are returning a lot of events, it might be better to pull out the event table lookup to the main query to give the optimizer the option of doing a merge join.
I don't see a way of optimizing the previous event lookup short of an indexed view.

Here's a query that combines a few of those ideas.

SELECT
    e.[EventID]
INTO #EventTemp
FROM
    [Events] AS e
WHERE
    ( e.[EventStart] >= @StartDate AND e.[EventStart] <= @EndDate )
    OR ( e.[EventEnd] >= @StartDate AND e.[EventEnd] <= @EndDate )

;
WITH PrevEvent AS (
    SELECT
        EmpPrevEvent.[EventID]
    FROM
    (
        SELECT DISTINCT
            ee.[EmployeeID]
        FROM
            #EventTemp
            INNER JOIN [EventEmployee] AS ee ON
                #EventTemp.[EventID] = ee.[EventID]
    ) AS Emp
    CROSS APPLY (
        SELECT TOP 1
            e.[EventID]
        FROM
            [Events] AS e
            INNER JOIN [EventEmployee] AS ee ON
                e.[EventID] = ee.[EventID]
        WHERE
            ee.[EmployeeID] = Emp.[EmployeeID]
            AND e.[EventEnd] < @StartDate
        ORDER BY
            e.[EventEnd] DESC
    ) AS EmpPrevEvent
)
SELECT
    e.[EventID],
    e.[EventStart],
    e.[EventEnd],
    e.[EventTypeID]
FROM
    [Events] AS e
WHERE
    e.EventID IN (
        SELECT EventID
        FROM #EventTemp
        UNION
        SELECT EventID
        FROM PrevEvent
    )


In what order does PostgreSQL check object permissions? Given a database role, user1, a function something() defined as a stored procedure and a view created like:

CREATE VIEW view1 AS select * from something()

And, given this permissions:

REVOKE ALL ON FUNCTION something FROM user1
REVOKE SELECT ON view1 FROM user1

When I run SELECT * FROM view1, I get an error permission denied for function something().

My question is, if I revoke the select permissions on the view, why the function is called? I was expecting to receive something like:

permission denied for relation view1

Thank you!

The problem in that case is not exactly about permission order, but execution order.

In resume, for PostgreSQL :

1- Views that are acessing tables will override the tables permission

2- Views acessing functions, will need to evaluate all the functions, before being checked - so the functions must be executed before acessing the view, even if the view does not has select permissions...

How can we prove that?

In postgresql, views are able to give you permissions for doing a select in a table, even if the user don't have this permissions.

For example:

create view view2 as select * from table1;
revoke all on table1 from user1;
grant select on view2 to user1;

Log in as user 1:

select * from table1 (permission denied)
select * from view2 (sucess - the query executes)

In the case, the user will be able to select view2 even don't having permission to select the table.

But what if we do the same thing with a function? The behavior are NOT the same. Lets create a function that wait 5 seconds before returning 1 (so we can debug if postgresql is running the function everytime that we call the view)

CREATE OR REPLACE FUNCTION something() RETURNS integer
AS 'select 1 from pg_sleep(5);'
LANGUAGE SQL
IMMUTABLE
RETURNS NULL ON NULL INPUT; --this function will delay 5 seconds

create view view1 as select * from something();
revoke all on function something() from user1;
grant select on view1 to user1;

Log in as user 1:

select * from something(); (permission denied for something)
select * from view1 (permission denied for something )

The permission to do select on the view do not overrides the function permission, and even worst if we revoke the permissions from view1, the message still shows that postgresql stopped our query because of the function, no matter what the permission of the view.. (that is exactly what is happening in the question)

But is the function really being checked first? If we give the 'all' permissions to the function, but revoke the view permission...

grant all on function something to user1;
revoke all on view1 from user1;
select * from view1;
Delayed 5 seconds... (the function executed!)
Permission denied for select on view1

As you see postgresql WAITED 5 SECONDS before saying that we dont have permission to output the view, showing that the "something()" function are executed. So the function data return must exist before the check of the view.

So now with this tests, we now know that PostgreSQL needed to first evaluate all the functions before continuing our query, is just like the query still does not exist until all the functions envolved are fully completed, so the view cant be solved for postgresql to know if we have or not permission to select it.

I think this answer your question in the terms of "permission order", but why does postgresql needs to evaluate all the functions before continuing, thats is another question...


With most designs, there is some kind of trade-off. One down side with what you suggested is that it is not possible to find a candidate key in ASSIGNED. Consider what it would mean if someone added:

INSERT INTO ASSIGNED (ID, TICKET_ID, TASK_ID, ASSIGNED_TO)
VALUES (5, 1, NULL, 25);

From my perspective, you would be better off with two tables:

CREATE TABLE ASSIGNED_TICKETS
( USER_ID ... NOT NULL REFERENCES USERS (USER_ID)
, TICKET_ID ... NOT NULL REFERENCES TICKETS (TICKED_ID)
,    PRIMARY KEY (USER_ID, TICKET_ID) );

If a ticket cannot be assigned to several users, make TICKET_ID the primary key.

A similar table can be used for ASSIGNED_TASKS.

An alternative design is to use a super table, I'll call that WORK_UNIT for lack of a better word.

CREATE TABLE WORK_UNIT
( WORK_UNIT_ID ... NOT NULL PRIMARY KEY
, WORK_UNIT_TYPE CHAR(4) NOT NULL
,   CHECK (WORK_UNIT_TYPE IN ('TICK', 'TASK'))
, ... );

The relationship between TICKET and TASK can be represented by a separate table:

CREATE TABLE TICKES_FOR_TASKS
( TASK_ID ... NOT NULL
      REFERENCES WORK_UNIT (WORK_UNIT_ID)
, TICKET_ID ... NOT NULL
      REFERENCES WORK_UNIT (WORK_UNIT_ID)
,   PRIMARY KEY (TASK_ID, TICKET_ID) );

If you want to enforce the types in that relation (for example so that a TICKET can't consist of other TICKETS), you can add a UNIQUE constraint to WORK_UNIT:

ALTER TABLE WORK_UNIT ADD CONSTRAINT ...
    UNIQUE (WORK_UNIT_ID, WORK_UNIT_TYPE)

and "inherit" WORK_UNIT_TYPE in TICKES_FOR_TASKS:

CREATE TABLE TICKES_FOR_TASKS
( TASK_ID ... NOT NULL
, TASK_TYPE CHAR(4) NOT NULL
,     CHECK ( TASK_TYPE = 'TASK' )
,     FOREIGN KEY ... (TASK_ID, TASK_TYPE)
      REFERENCES WORK_UNIT (WORK_UNIT_ID, WORK_UNIT_TYPE)
, TICKET_ID ... NOT NULL
, TICKET_TYPE CHR(4) NOT NULL
,     CHECK ( TICKET_TYPE = 'TICK' )
,     FOREIGN KEY ... (TICKET_ID, TICKET_TYPE)
      REFERENCES WORK_UNIT (WORK_UNIT_ID, WORK_UNIT_TYPE)
,   PRIMARY KEY (TASK_ID, TICKET_ID) );

I've heard of (but never used myself) DBMS that support CHECK constraints with selects. You could then avoid "inheriting" the type attributes. Another way to avoid this is to use triggers that throws an exception if a ticket or task is of the wrong type.

In this scenario you only need one table for ASSIGNMENTS:

CREATE TABLE ASSIGNED_WORK_UNITS
( USER_ID ... NOT NULL REFERENCES USERS (USER_ID)
, WORK_UNIT_ID ... NOT NULL REFERENCES WORK_UNIT (WORK_UNIT_ID)
, ... -- additional attributes
,    PRIMARY KEY (USER_ID, WORK_UNIT_ID) );

As, before. If a WORK_UNIT_ID can't be shared between USERS, make WORK_UNIT_ID the primary key.

No design is without flaws so you have to carefully consider for and against each possible one. Hopefully, his will give you some ideas on what to consider.


Does database mirroring replicate the time stamp for database backups? We use Quest Spotlight for database server monitoring.  Depending on when SQL server decides to fail over automatically, we will get an alert stating that a database has not been backed up in over three days (the factory default) if that server has not been primary in over 72 hours.

My suspicion is that SQL Server does NOT replicate this data, but I wanted to hear from the community since a quick Google search didn't provide anything.  Plus, I am travelling and do not have time to test and I need to provide a fairly immediate response.

Backup data is stored in msdb, which is a system database and cannot be part of mirroring or AlwaysON.

So the software should be smart enough to check or implement a custom solution using PowerShell or linked server that will tell if a database is lacking a recent backup.


SSRS Standalone install license For performance reasons we are thinking about moving our SSRS installation to a separate server.
Since you can have a standalone installation of SSRS.
Question being, if we install SSRS (standalone) on a new server with X cores.
Do we need a separate enterprise license for this server?
Is there a licensing cost for installing standalone SSRS?
Or is the license used from the instance where the ReportServerDB is located ?

You will need to buy a separate license for the server running SSRS. Unless you have a virtual host where you have bought an enterprise license for every core in the server (see the chapter on Licensing for Maximum Virtualization)


Two Oracle clients on the same server Is it okay to have two oracle clients (both 11g and 12c) in the same db server? What are the potential issues I could face? The reason im doing this is to connect the app server where I'm deploying the clients to two databases each running on 11g and another on 12c.

You should not install more than one Oracle Client (per architecture) on one machine. For sure they do not work in parallel.

By heart I know several issues:

You would have to change PATH settings in order to determine which Client you want to use. TNS_ADMIN environment variable is used by both versions and must be set accordingly. Oracle OleDB provider can be installed only once (per architecture). Version of Oracle Data Provider for .NET (ODP.NET) must match the client version. Using ODBC can be also a challenge.


SELECT LATEST ENTRY ON GROUP BY This query doesn't return the "latest" entry, rather it returns the "older" rows:

 SELECT MAX(ID), ID, NEW_BALANCE, DATE, NAME, ACC_NO
 FROM
 (
  SELECT ID, NEW_BALANCE, DATE, NAME, ACC_NO
  FROM account_logs
  WHERE customer_id = 1061
  ORDER BY DATE DESC, ID DESC
) AS sub
GROUP BY YEAR(date), MONTH(date) ORDER BY DATE ASC ;

The following inner query:

SELECT ID, NEW_BALANCE, DATE, NAME, ACC_NO
FROM account_logs
WHERE customer_id = 1061
ORDER BY DATE DESC, ID DESC

...perfectly arranges the rows by the latest entries:

I would like to GROUP BY the latest entries, rather I get this:

The rows I need:

SELECT *
FROM account_logs
WHERE ID IN (
  SELECT MAX(ID)
  FROM account_logs
  WHERE customer_id = 1061
  GROUP BY DATE )
ORDER BY DATE ASC ;

The sub-query should return all the ID's that you are interested in.


Analyze logs to tell if database was moved or copied In SSMS when you right click on any database, under Tasks, there is option to Copy Database. One of the things the wizard asks you is whether you are copying or moving the database. It basically saves a package and runs it.

I'm on the server where the job was created, and which is also the destination server. I need to find out whether the database was copied, or moved, from the source server. How can I find that out (other than of course checking the source server)?

I think you can keep an eye on below kind of alerts to find out any change in the database movement:

EXEC sys.sp_readerrorlog @p1 = 0, @p2 = 1, @p3 = N'OFFLINE';
EXEC sys.sp_readerrorlog @p1 = 0, @p2 = 1, @p3 = N'ONLINE';

Message ID of 5084 if you're monitoring database option changes

You would indeed see a logged message in the SQL Server error log:

  Setting database option OFFLINE to ON for database 'TargetDBName'.

  Setting database option ONLINE to ON for database 'TargetDBName'.

Why I say is the process includes :

Issue an ALTER DATABASE command using the SET OFFLINE option to take the database off-line.

2.Move the files to their new locations.

3.Issue the appropriate ALTER DATABASE commands using the MODIFY FILE option to tell SQL Server where the new locations are.

4.Issue an ALTER DATABASE command using the SET ONLINE option to bring the database back on-line.

Finally you can cross check the below command and match the timings of creations date and the log dates:

SELECT Name,create_date, state_desc FROM sys.databases

You can even rely on Profiler traces to find out the changed locations of the existing Database files to another location and you can keep a point on who changed it too.

Hope this helps a bit...


Restore A Live Database to Test Database containing defferent Names using SQL Server Management Studio I have a test database with a name say TestDB and  A production/Live Database say LiveDB running on the same server.
What I want is to restore the production/Live database to a TestDB.
Below is the error I am getting;

Thanks for your help in advance.

Just so you know, this is so much more easily done with T-SQL. Using the GUI for backups and restores is a pain in the bottom.
However, what you need to do to in order to restore a backup from one database on top of a different database is ensure that you're moving the data files from the old databases definitions to the new database. On the screen above, you'll need to change the database name, obviously, for the restore.
Then, you have to click on the Files tab. It's right there in the upper left. That will list out all the files in the current database. It will show their current location. It also has a column called "Restore As" that is defaulted to the current location. You want to change that to be the location of your TestDB, wherever that is.
On the screen above, you also have to click on the Options tab (again, upper left). There, you want to enable "Overwrite existing database (WITH REPLACE)".
A giant word of caution here. Make darned sure you've got the database named changed in the first screen. If you mess that up, you'll be forcing a restore on top of your production database (which will probably fail because connections will block you, but if it doesn't, oh boy).
Or, you could:
RESTORE DATABASE NewDatbaseName
FROM DISK = 'x:\bu\olddatabase.bak'
WITH REPLACE,
MOVE 'OldDataFile' TO 'd:\data\newdbname.mdf',
MOVE 'OldLogFile' TO 'd:\data\newdbname_log.ldf';

So much easier and more clear. However, to each their own.


I need help using hierarchyid datatype and requiring a unique constraint on a varchar field I want to resolve a bunch of tables that had parent->child->grandchild relationships into a single table using hierarchyid identifiers. Here is what I came up with based on what I had.

    CREATE TABLE [dbo].[tbl_server_group]
    (
        [refid]     [int] IDENTITY(1,1) NOT NULL,
        [refhid]    [hierarchyid] NOT NULL,
        [reflvl]    AS refhid.GetLevel() PERSISTED,
        [refdate]   [datetime] NOT NULL DEFAULT GETDATE(),
        [refname]   [varchar](50) NOT NULL
    )

My issue is that I need to be able to require that the refname is unique but only within the container that it is in. Being that I am using this to represent servers, I wanted to emulate a DNS tree of sorts but add other layers to it as well. My example being the following:

    ROOT
    | something.com
    || www
    ||| index.html
    || staging
    ||| testbed.php
    | someotherthing.com
    || test
    ||| index.html

I want to make sure that something.com only has one "www" and that www only has one "index.html".

Is there anyway to do this with constraints? If not, is there an efficient way to force uniqueness?

Thanks

If you add another column for the ancestor, e.g.

CREATE TABLE [dbo].[tbl_server_group]
(
    [refid]     [int] IDENTITY(1,1) NOT NULL,
    [refhid]    [hierarchyid] NOT NULL,
    [reflvl]    AS refhid.GetLevel() PERSISTED,
    [refdate]   [datetime] NOT NULL DEFAULT GETDATE(),
    [refname]   [varchar](50) NOT NULL,
    [reflvl1id] AS refhid.GetAncestor(1) PERSISTED
);

You can ensure that there is only ever one 'www' per parent.  You can create a similar index for 'index.html'.

create unique index ux_tbl_server_group_1 on tbl_server_group(reflvl1id, refname)
where refname = 'www';

Obviously this is carrying it further than just the first level (www) and for children of www (index.html) but I don't see why the expanded restrictions won't work.
If you had to implement it exactly as specified, then you will have to resort to using a trigger.

Note: This makes use of SQL Server's filtered index, so you have to get the SET settings correct for any process that touches this table.


IIS access log to SQL normalization I am looking for insert IIS 6.0 access log ( 5 servers, and over 400MB daily ) to SQL database. What scares me is the size. There is a lot of information you are duplicating (i.e. site name, url, referrer, browser) and could be normalized by index and look-up table.

Reason why I am looking for own database instead using other tools is that is 5 servers and I need very custom statistics and reports on each, few or all. Also installing any (specially open source) software is massacre ( need have 125% functionality and take months ).

I wounder what would be the most efficient way to do it? Is someone saw examples or articles about it ?

I haven't done it myself, but there are several articles on using SSIS to transform the data. The principle will apply to other ETL tools I guess

http://www.databasejournal.com/features/mssql/article.php/3650326/Using-SQL-for-IIS-Logs-Part-2.htm
MS Log Parser
How-To: SQL query IIS Logs with Log Parser

and

maybe for reporting only?


Find which session is holding which temporary table We have a SQL Server 2005 database the temp database has become full. By going into SQL Server Management Studio I can see all the temporary tables in the tempdb. Is it possible to tell which session is holding which temp table? Ideally a query which would list temp tables used by each session.

Thanks,

I asked for something to be built in back in 2007, on Connect. This was rejected for the 2008 release, and subsequently ignored, until Connect died a few years ago. I tried to find it on the new feedback site for SQL Server, but that search is an absolute dumpster fire. The title of my request was "dmv to map temp table to session_id" - since the search can only do OR, "map temp table" returns 118 pages of results. Google seems to suggest the item didn't make the cut when they killed Connect.

In the meantime, for SQL Server 2005 and 2008, you should be able to pull this information from the default trace:

DECLARE @FileName VARCHAR(MAX)

SELECT @FileName = SUBSTRING(path, 0,
   LEN(path)-CHARINDEX('\', REVERSE(path))+1) + '\Log.trc'
FROM sys.traces
WHERE is_default = 1;

SELECT
     o.name,
     o.OBJECT_ID,
     o.create_date,
     gt.NTUserName,
     gt.HostName,
     gt.SPID,
     gt.DatabaseName,
     gt.TEXTData
FROM sys.fn_trace_gettable( @FileName, DEFAULT ) AS gt
JOIN tempdb.sys.objects AS o
     ON gt.ObjectID = o.OBJECT_ID
WHERE gt.DatabaseID = 2
  AND gt.EventClass = 46 -- (Object:Created Event from sys.trace_events)
  AND o.create_date >= DATEADD(ms, -100, gt.StartTime)
  AND o.create_date <= DATEADD(ms, 100, gt.StartTime)

Shamelessly lifted from this Jonathan Kehayias blog post.

To determine space usage you could further enhance this to join in data from views like sys.db_db_partition_stats - e.g.:

DECLARE @FileName VARCHAR(MAX)

SELECT @FileName = SUBSTRING(path, 0,
   LEN(path)-CHARINDEX('\', REVERSE(path))+1) + '\Log.trc'
FROM sys.traces
WHERE is_default = 1;

SELECT
     o.name,
     o.OBJECT_ID,
     o.create_date,
     gt.NTUserName,
     gt.HostName,
     gt.SPID,
     gt.DatabaseName,
     gt.TEXTData,
     row_count = x.rc,
     used_page_count = x.upc
FROM sys.fn_trace_gettable( @FileName, DEFAULT ) AS gt
JOIN tempdb.sys.objects AS o
     ON gt.ObjectID = o.OBJECT_ID
INNER JOIN
(
 SELECT [object_id], SUM(row_count), SUM(used_page_count)
   FROM tempdb.sys.dm_db_partition_stats
   WHERE index_id IN (0,1)
   GROUP BY [object_id]
) AS x(id, rc, upc)
ON x.id = o.[object_id]
WHERE gt.DatabaseID = 2
  AND gt.EventClass = 46 -- (Object:Created Event from sys.trace_events)
  AND o.create_date >= DATEADD(ms, -100, gt.StartTime)
  AND o.create_date <= DATEADD(ms, 100, gt.StartTime)

The problem here is trying to correlate a table name by query text; this just isn't practical, since most of the time, the user isn't still executing a query against that table (never mind still running the one that created / populated it).

However, and this is for other readers (or for you when you upgrade), the default trace in 2012+ no longer tracks temp table object creation, if the #temp table is a heap. Not sure if that is a coincidence or directly related to the fact that starting in 2012 all temp tables now have a negative object_id. You could of course move to Extended Events to help you collect and track this information, but that is possibly a lot of manual work (and I've only verified that this is no longer tracked in trace - you may not be able to pick it up in Extended Events either). The default trace will pick up #temp tables created with a PK or other constraint, or with constraints or indexes added after the creation event, but then you'll have to loosen up the time-based restrictions above (an index can be created much later than 100ms after creation).

Some other answers on this site that may be useful:

How to identify which query is filling up the tempdb transaction log?
Issues with TempDB mdf file ever increasing
Find transactions that are filling up the version store

I've also blogged about this, with a custom Extended Events session to track this information in SQL Server 2012 and up:

Dude, who owns that #temp table?

And Paul White has blogged about reading pages directly (not exactly for the faint of heart, nor easy to automate in any way):

Viewing Another Session’s Temporary Table


Why is an empty XML node xml datatype not being recognized in this query? My query seems to not recognize self-closing or empty nodes at all. Shouldn't the empty  node return 'null'? Or an empty string. Even if I place an empty space in the node, it still is ignored.

declare @x xml;

    set @x='<root>

                    sdfg
                    dfgh

                    foo
                    bar
                    poo

            </root>'
select
    x.c.value('(td//text())[1]', 'varchar(1024)') as col1
    ,x.c.value('(td//text())[2]', 'varchar(1024)') as col2
    ,x.c.value('(td//text())[3]', 'varchar(1024)') as col3
from @x.nodes('/root/tr') x(c)

I want the result to be this:

col1 col2 col3
null sdfg dfgh
foo  bar  poo

..but instead I get this:

col1 col2 col3
sdfg dfgh null
foo  bar  poo

In (td//text())[1] the predicate [1] will return the first node returned by the XQuery (td//text()).

You are saying that you want the first text() node. But in the first td element there are no text nodes present so you get the text node for the second td element which is the first text node returned.

You need to specify the position predicate on the td node instead.

This will give you null in col1

select
     x.c.value('td[1]/text()[1]', 'varchar(1024)') as col1
    ,x.c.value('td[2]/text()[1]', 'varchar(1024)') as col2
    ,x.c.value('td[3]/text()[1]', 'varchar(1024)') as col3
from @x.nodes('/root/tr') x(c)

And not specifying the text() node will give you an empty string in col1.

select
     x.c.value('td[1]', 'varchar(1024)') as col1
    ,x.c.value('td[2]', 'varchar(1024)') as col2
    ,x.c.value('td[3]', 'varchar(1024)') as col3
from @x.nodes('/root/tr') x(c)


SSRS report does not work with multiple parameters My data set has the following where clause:

WHERE a11.PDPOID LIKE '%' + @POID + '%'

My parameter @POID properties are set to single value.  The report works, but if I change the parameter properties to allow multiple values then I get an error:

  Query execution failed for dataset 'DataSetDtRcvd'. incorrect syntax near ','.

You could achieve this by changing your dataset query to an expression and use the Join operator on the multi-valued parameter, for example, your expression would look like

= "Select * " &
  "from table t " &
  "where a11.PDPOID LIKE '%" & Join(Parameters!TestParm.Label, "%' OR a11.PDPOID LIKE
  '%") & "%'"

Replace the Select * from table t with your query.


SSIS Package Validation Error when Executed From Windows Task Scheduler I have deployed my SSIS package using the project deployment model to the SSIS catalog and experience a strange validation error when I try to execute the package. I believe this is due to a privileges issue or I may have configured our data import process wrong.

Let me give you an overview on the process:

Windows Task Scheduler is being used
Scheduled Task executes a long-running file download process (using Powershell)
Scheduled Task triggers execution of the SSIS package (using Powershell and SMO)
Scheduled Task is configured to run as a domain account (see privileges below)

Note: all these steps, including account creation, are automated using guidance from here.

Scheduled Task Account Privileges:

Windows: Users Group
SQL Catalog: Read on Catalog Folder, Read on Environment, Read and Execute on Project
Destination Data Database: db_datareader, db_datawriter
Source Data Database: db_datareader, db_datawriter

Steps To Reproduce The Error:

Right-click scheduled task, choose 'Run'
Package execution is triggered successfully
Open SSMS, view Execution logs. Package execution immediately failed with a validation error "Cannot create task from XML for task "[taskname]", type "SSIS.ReplacementTask" due to error 0x80070057 "The parameter is incorrect."

While diagnosing this issue, I wanted to rule out privileges and ensure the account could see the SSIS project in the catalog, so I executed SQL Server Management Studio as my Scheduled Task account. The Package validated and ran to completion successfully.

Here's the bit that is very confusing.! After performing the above step (login to SSMS as Task Scheduler account),  I could successfully trigger, validate and execute the scheduled task from the Task Scheduler!

I am now able to reliably replicate these steps:

Execution Fails
Login to SSMS As Task Scheduler Account
Execution Succeeds

Theory: the 'first time configuration' that occurs when launching SSMS as the Task Scheduler account is configuring user specific registry keys. I have confirmed that when I do not log in as my custom account, when the package tried to execute it complains that user specific registry keys are not found via Process Monitor.

My Questions:

Should we be using the Task Scheduler instead of SQL Server Agent? I orignally chose Task Scheduler because the powershell download task was long running and needs access to network shares
Can I trigger a package to execute from a Scheduled Task, but have it execute as a different account?

Thanks for any help you can offer - I'm quite confused!

I could not resolve this issue and changed my approach to utilize a SQL Job instead of running the package directly from a powershell script.

Summary of solution:

SQL Job with one Job Step
The Job Step executes the SSIS package

To run a SQL Job with a custom you must also create:

an instance-level Credential
a Proxy Account which should be mapped to the Credential

I still wonder what's different about this approach versus the 'interactive' approach listed above. Both approaches run with the exact same identity and privileges!

For an in-depth description of the steps required to configure a SQL Job, look at this example code for Automated Deployment of a SQL Job and Job Step.

Hope this helps someone else in the same situation -


Select after an Uncommited Update in Python-MySQLdb May be this is a newbie question but want to confirm as this is important for my app:

I'm using MySQLdb on Python and my code looks like:

db = MySQLdb.connect("localhost","root","",test)
cursor = db.cursor()

cursor.execute("UPDATE users SET address = 'Something' WHERE name = 'Someone'")
db.query("SELECT * FROM users WHERE address = 'Something'")
sample=db.store_result().fetch_row(maxrows=0)

So the question is, will sample contain the row that was changed by the uncommitted update?

In the real app, I am making some complex queries with subclauses like GROUP_CONCAT, etc. Will all these work with uncommitted updates? (In the sense that can these work on the rows updated by uncommitted updates)

tl;dr Yes, sample will contain the rows that were just updated.

Please note that you're not in a transaction context. So every query is an implicit transaction in this specific case.

Assuming you did have a begin somewhere sample would still see the rows because it's the same connection and the same transaction.

If you started the transaction and opened a separate connection or you had a concurrent process/request it would only see the rows without commit if your mysql is running with READ-UNCOMMITTED transaction level.

You can check the global settings by:

mysql> show global variables like 'tx_isolation';

It can also be set for individual connection:

mysql> set tx_isolation='READ-UNCOMMITTED';

I hope this answers your question.


mongodb service not running after changing path I have changed the dbpath of mongodb.conf.
And after that trying to start mongodb service,when I run

sudo service mongodb start

I get back

mongodb start/running, process 7320

After that I run:

sudo service mongodb status

And getting back

mongodb stop/waiting

When I want to access mongo shell by :

 mongo
MongoDB shell version: 2.0.4
connecting to: test
Sun Oct 28 13:01:36 Error: couldn't connect to server 127.0.0.1 shell/mongo.js:84
exception: connect failed

But when I start mongod as manually by mongod --dbpath=/mnt/data/db it runs fine.
And I can access the mongo shell by mongo command.

I have correct permission for my data directory.

Any clue where I am doing wrong?

It is possible that mongodb.conf is not getting loaded because of wrong location (typically this is /etc/mongodb.conf) or a typo when you launch through service start

Try launching manually

mongod --config /etc/mongodb.conf

to rule out any issues with the conf file.


SQL query to count number of payments made in a time period with data from two different table Could someone please tell me how to write sql query for the following question? I am not able to get how to write the condition where it selects those user_id which is present on the said date and also made a payment in the last 90 days. The question is as follows:

Every day a player comes in the game, his/her user_id and date when he/she entered the game is   recorded in a table say Table DAU. It has two columns. 1. user_id 2. date

On a particular date, for a player there can be only one entry (No multiple entries on same day). However, the same player can come on different dates.

This table has data from 1/1/2015 (i.e January 1st) onwards. Below are sample entries from this table.

user_id date
1001    6/1/2015
1002    6/1/2015
1003    6/1/2015
1001    6/2/2015
1002    6/2/2015
1004    6/2/2015
1002    6/3/2015
1003    6/3/2015
1005    6/3/2015

Table Payment

Also, every time a player makes a payment in the game, his/her user_id and timestamp when he/she made the payment is recorded in a table, say Table Payment. It has two columns.       1. user_id 2. payment_timestamp

A player can make multiple payments on the same day and across days also.

Below are sample entries from this table.

user_id payment_timestamp
1001    6/1/2015 07:00
1001    6/1/2015 07:30
1002    6/1/2015 10:00
1001    6/2/2015 13:50
1002    6/2/2015 08:00
1004    6/2/2015 09:00
1003    6/3/2015 10:00
1005    6/3/2015  08:30

We define 90-Day Payer DAU for a particular date, say 6/1/2015 as follow:

Count (Player who came on 6/1/2015 and has made at least one payment in the game in last 90 days i.e from 3/4/2015 to 6/1/2015)

Similarly, 90-Day Payer DAU for 6/2/2015 will be:

Count (Players who came on 6/2/2015 and have made at least one payment in the game in last 90 days i.e from 3/5/2015 to 6/2/2015)

Question:

Using the DAU Table and Payment table, please write a SQL query to produce the below table.

Date    90_Day_Payer DAU
6/1/2015
6/2/2015
6/3/2015
6/4/2015
6/5/2015
6/6/2015
6/7/2015

Here is a query that should give you what you are looking for.  This is Oracle SQL syntax, but may work in other databases as well.

SELECT dau.datetime, count(*) FROM DAU
WHERE EXISTS (select 1 from Payer
   WHERE Payer.user_id=DAU.user_id
   AND Payer.payment_timestamp BETWEEN trunc(dau.datetime)-90 AND trunc(DAU.datetime))
GROUP BY dau.datetime
ORDER BY DateTime;

Data to demonstrate:

create table Payer as (select 1001 user_id, to_date('06/01/2015 07:00','MM/DD/YYYY HH24:MI') payment_timestamp from dual);
insert into Payer values (1001, to_date('06/01/2015 07:30','MM/DD/YYYY HH24:MI'));
insert into Payer values (1002, to_date('06/01/2015 10:00','MM/DD/YYYY HH24:MI'));
insert into Payer values (1001, to_date('06/02/2015 13:50','MM/DD/YYYY HH24:MI'));
insert into Payer values (1002, to_date('06/02/2015 08:00','MM/DD/YYYY HH24:MI'));
insert into Payer values (1004, to_date('06/02/2015 09:00','MM/DD/YYYY HH24:MI'));
insert into Payer values (1003, to_date('06/03/2015 10:00','MM/DD/YYYY HH24:MI'));
insert into Payer values (1005, to_date('06/03/2015 08:30','MM/DD/YYYY HH24:MI'));

create table DAU as (select 1001 user_id, to_date('06/01/2015','MM/DD/YYYY') datetime from dual);
insert into DAU values (1002, to_date('06/01/2015','MM/DD/YYYY'));
insert into DAU values (1003, to_date('06/01/2015','MM/DD/YYYY'));
insert into DAU values (1001, to_date('06/02/2015','MM/DD/YYYY'));
insert into DAU values (1002, to_date('06/02/2015','MM/DD/YYYY'));
insert into DAU values (1004, to_date('06/02/2015','MM/DD/YYYY'));
insert into DAU values (1002, to_date('06/03/2015','MM/DD/YYYY'));
insert into DAU values (1003, to_date('06/03/2015','MM/DD/YYYY'));
insert into DAU values (1005, to_date('06/03/2015','MM/DD/YYYY'));


Why can't I create a MySQL user even if i have 'all priviliges'? I am trying to resolve issues created when my ISP moved my database from a shared server to dedicated cloud storage. I am reaching it remotely via SQLyog. They say they have given all privileges to all users (I'm the only user)

Issuing the sql

SHOW GRANTS

returns

GRANT ALL PRIVILEGES ON `my_database_name`.* TO 'myusername'@'my ip address'

but if I try to create a user for my database using command such as

CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';

or

CREATE USER 'newuser'@'%' IDENTIFIED BY 'password';

I get the error

Access denied; you need (at least one of) the CREATE USER privilege(s) for this operation

and if I try to see exactly what privileges I have using

SELECT USER FROM mysql.db WHERE db = 'my_database_name';

I get the error

SELECT command denied to user 'myusername'@'my ip address' for table 'db'

I can't select from several other system tables either, eg user

Should I be able to do these things if I really have 'ALL privileges'?

How can I list exactly which privileges I have instead of just getting 'ALL' ?

Can anyone suggest a way I can try to track down what is wrong?

BTW I don't know if they executed flush privileges after doing the grant or not. would this have made a difference given that SHOW GRANTS appears to show that I have all privileges?

You need to grant CREATE USER using similar syntax:

grant create user on *.* to 'admin'@'%';


Select columns dynamically from sql server table Lets say I have a table that actually store record which looks like

Create Table dbo.Info
(
 SN int primary key identity(1,1),
 FirstName nvarchar(50) not null,
 LastName nvarchar(50) not null,
 Gender char(1) default 'M',
 Age int check(Age>0)
)

Now for reporting purpose I only need this table column name and save in another table

Create Table Report.InfoColumnOrder
(
 SN int primary key identity(1,1),
 UserId int,
 ColumnName nvarchar(100)
)

thus record looks like

1, 1, FirstName
2, 1, LastName
3, 1, Gender
4, 2, LastName
5, 2, FirstName
6, 2, Age
7, 2, Gender

Now when user with id 1 login and wants report from table Info
select command should look like

Select FirstName, LastName, Gender from dbo.Info

whereas for a user with id 2 select command will be

Select LastName, FirstName, Age, Gender from dbo.Info

Since, user can order the column from client side select statement should be dynamic. Is it possible?

Use dynamic SQL. But join to sys.columns to avoid SQL Injection attacks.

DECLARE @qry NVARCHAR(MAX) =
'SELECT ' + STUFF((
    SELECT ', ' + QUOTENAME(c.name)
    FROM Report.InfoColumnOrder r
    JOIN sys.columns c ON c.name = r.ColumnName AND c.object_id = object_id('dbo.Info')
    WHERE u.UserId = @user
    ORDER BY r.SN
    FOR XML PATH(''), TYPE).value('.','nvarchar(max)'),1,2,'') +
 ' FROM dbo.Info'
;
EXEC sp_executesql @qry, N'@user int', @user = @userid;

Or something like that - I'm writing this on my phone...


SQL Cluster instance does not see all drives As DBA on SQL 2014 FCI, I need to do a local backup. However, I do not see all drives available on the VM . I only see the drives added as storage disks in the FCI UI.
in order to see the other drives in the backup menu, do I need to add it to storage disks in FCI?
Thank you,

I believe that is the whole purpose of FCI. You will only see the disk part of shared storage in that cluster.
I am not sure what you are trying to exactly do when you say local backup. Backup of database ? If yes then I guess that should be simple enough to backup in the drive part of shared storage so that after failover to other node backup continues to work.
There are few drives which we might not care local installation directories to be part of same shared storage or software drives or dba script drives what we have in our env.
So totally depends what drives are you talking about here


MongoDB Sharded Cluster Upgrade from 2.4 to 3.2 version I need to upgrade MongoDB Sharded Cluster from 2.4 to 3.2 .

Based on documentation, I need to upgrade from 2.4->2..6->3.0->3.2

So it looks like lots of upgrade from 2.4 to go to 3.2 version.

We have 3 Shards with 3 replica sets.

Each Shard also runs 1 config server and router service

Upgrade Document :
https://docs.mongodb.com/manual/release-notes/2.6-upgrade/#upgrade-a-sharded-cluster-to-2-6.

Question :
1. Should all cluster should be upgraded to 2.4 to 2.6 first and then follow same process from 2.6 to 3.0 and 3.0 to 3.2 ?

Upgrade Sharded Clusters:
           Upgrade the cluster’s meta data

It even did not mention that I need to shutdown existing config DB process. Do I need to shutdown 1 config server on shard and follow upgrade process ? Do I need to upgrade all config servers like this or since Config Server data is synched automatically , I need to upgrade only one config server and then replace binary for 2.6 on other servers.

Please clarify following points :
Upgrade Sharded Clusters

Optional but Recommended. As a precaution, take a backup of the config database before upgrading the sharded cluster.

1.
Disable the Balancer.
Turn off the balancer in the sharded cluster, as described in Disable the Balancer.

    This looks good.

2.
Upgrade the cluster’s meta data.
Start a single 2.6 mongos instance with the configDB pointing to the cluster’s config servers and with the --upgrade option.

    It says Start a single 2.6 mongos. It is not clear to me, at this point should my whole existing cluster should be down or I need to just just down one config server and do upgrade. It even did not mention, I need to stop/shutdown config database on one server. It just says "Start with 2.6 binary with upgrade option" so it is confusing to me. Can someone clarify ?

To run a mongos with the --upgrade option, you can upgrade an existing mongos instance to 2.6, or if you need to avoid reconfiguring a production mongos instance, you can use a new 2.6 mongos that can reach all the config servers.

mongos --configdb  --upgrade
You can include the --logpath option to output the log messages to a file instead of the standard output. Also include any other options required to start mongos instances in your cluster, such as --sslOnNormalPorts or --sslPEMKeyFile.

The mongos will exit upon completion of the --upgrade process.

The upgrade will prevent any chunk moves or splits from occurring during the upgrade process. If the data files have many sharded collections or if failed processes hold stale locks, acquiring the locks for all collections can take seconds or minutes. Watch the log for progress updates.

    Good with instructions ...

3.
Ensure mongos --upgrade process completes successfully.
The mongos will exit upon completion of the meta data upgrade process. If successful, the process will log the following messages:

upgrade of config server to v5 successful
Config database is at version v5
After a successful upgrade, restart the mongos instance. If mongos fails to start, check the log for more information.

If the mongos instance loses its connection to the config servers during the upgrade or if the upgrade is otherwise unsuccessful, you may always safely retry the upgrade.

    Good with instructions ...

4.
Upgrade the remaining mongos instances to v2.6.
Upgrade and restart without the --upgrade option the other mongos instances in the sharded cluster. After upgrading all the mongos, see Complete Sharded Cluster Upgrade for information on upgrading the other cluster components.

    Upgrade and restart without the --upgrade option so there is no need to use --upgrade option. I am not not sure why step is called Upgrade and restart, it should be simple restart. Am I missing any thing here ?
    When we upgrade our first config db with upgrade option, other config db should automatically sync with first one, the only thing which I need to do is to start with 2.6 binary. Is my understanding correct ?

Yes. Period. No "Ok, but..."
You should make a backup. Start now, continue reading later. It is presumably going to take a while.
Config servers in 2.4, 2.6 and 3.0 do *not* form a replica set. Those were basically 3 standalone instances.
Read and understand the migration guide to 2.6 for sharded clusters. Rinse, repeat.
Read and understand the migration guide to 3.0 for sharded clusters. Rinse, repeat.
Read and understand the migration guide to 3.2 for sharded clusters Rinse, repeat.
Read the guide Upgrade Config Servers to Replica Set. Rinse, repeat.
Wait until your backup is done.
Upgrade to 2.6 according to guide mentioned above. Make sure (aka "test thoroughly") your cluster works as expected. Continue at the next appropriate maintenance window.
Upgrade to 3.0 according to the guide mentioned above. Migrate to WiredTiger in the process, unless you have very strange requirements. That is: shutdown data secondary data bearing node, delete its datadir, adjust config to utilize WT, resync, proceed. Have primary step down Before shut down. Make sure (aka "test thoroughly") your cluster works as expected. Continue at the next appropriate maintenance window.
Upgrade to 3.2 according to the guide mentioned above. Make sure (aka "test thoroughly") your cluster works as expected. Continue at the next appropriate maintenance window.
Convert your config servers to a replica set according to the guide mentioned above. Make sure (aka "test thoroughly") your cluster works as expected. Continue at the next appropriate maintenance window.
Take a few notes: "Good resolution 1: read release notes and docs of my chosen persistence technology. Good resolution  2: I will only ask questions to my peers after thoroughly researching the topic myself."