ODBC SQL tracer file question marks I have an MS Access query that connects to an Oracle Express database via an ODBC connection.

I've figured out a way to trace the SQL that is sent from MS Access to the ODBC connection. This is what the ODBC SQL looks like (I've formatted it a bit for readability):

SQLExecDirect:
SELECT
    "INSP_ID" ,
    "ROAD_ID" ,
    "INSP_DATE" ,
    "CONDITION"
FROM
    "USER1"."ROAD_INSP" "b"
WHERE
    ("INSP_DATE" = (
            SELECT MAX("a"."INSP_DATE" )
            FROM "USER1"."ROAD_INSP" "a"
            WHERE   (("a"."ROAD_ID" = "b"."ROAD_ID" )
                AND ("a"."INSP_DATE" BETWEEN  ? AND  ? ))))   <-------- Question marks

As you might have noticed, the last line has question marks instead of values.

This is because the MS Access query prompts the user to enter values via parameter prompts.

However, I'm not sure what these question marks are supposed to represent. I know that they originated from the MS Access parameter prompts, but what do they mean to Oracle, and how does Oracle get the parameter values?

This is called a parameterized statement. At the basic level, such a query is executed in two stages: first a query with parameter markers (those question marks) is compiled on the server side, then the actual parameter values are bound to the query and the query is run, returning only results that match the criteria (obviously).


Check Oracle Service is Started from Batch Command Uses: Oracle9i Standard Ed;

I have a schedule which performs a Dump Import and afterwards the System Reboots itself, which is happening without any problem.

After the System had rebooted, I have another task scheduled to run on System Startup; which has yet another PLSQL scripts to be run.

I witnessed after the Schedule task on System Start-up had ran, an error is thrown as

TNS: protocol adapter error

The cause was determined as The oracle Services had not Started.

So if I could check the Oracle Services has started or not prior to the query, I would solve the problem.

Or else I could use SLEEP Command in the batch file, but cant determine the exact time it takes to startup the server.

I welcome any suggestions on doing this with an alternate way as well.

Below is the Script file I run on System Start-up

@echo off
@echo %time%
echo Disable Triggers
sqlplus  "myusername/mypassword" @C:\Factory\participant-inclusion\Auto\step#4.sql<quit.txt
@echo %time%
echo Run Drop Contraints Sql
sqlplus  "myusername/mypassword" @C:\Factory\participant-inclusion\Auto\step#5x.sql<quit.txt

REM File Check
IF EXIST C:\Factory\participant-inclusion\Auto\step#5.sql (
 @echo %time%
 echo Run Drop Contraints
 sqlplus  "myusername/mypassword" @C:\Factory\participant-inclusion\Auto\step#5.sql<quit.txt
 @echo %time%
 echo Modify Type
 sqlplus  "myusername/mypassword" @C:\Factory\participant-inclusion\Auto\step#6.sql<quit.txt
) ELSE (
 @echo File Not Found
)

@echo %time%
echo Default Prefix
sqlplus  "myusername/mypassword" @C:\Factory\participant-inclusion\Auto\Prefix-Updation-ALL-JKB.sql<quit.txt
@echo %time%
echo And Finally Done!
pause

Many Thanks,

You can actually start that service, while you are waiting on it to start in another session. Even if both starts occur at the same time, Windows should be able to handle that correctly, and end with the service being up. Or, you sleep. What time ? Put something, and test. Ain't that hard, no ?


Need to add a Table Level Constraint to prevent bad data - Constraint is not working I have a table that has a flag that can be set to 0 or 1.  I need to make sure for every claim record in that table that the flag is only set 1 once.  So in other words I may have multiple records for one claim and all but one should be set to 0. Below is the table

CREATE TABLE [dbo].[ClaimAccounting_Assignments](
[PracticeID] [int] NULL,
[ClaimID] [int] NULL,
[ClaimTransactionID] [int] NULL,
[InsurancePolicyID] [int] NULL,
[InsuranceCompanyPlanID] [int] NULL,
[PatientID] [int] NULL,
[LastAssignment] [bit] NULL,
[Status] [bit] NULL,
[PostingDate] [datetime] NOT NULL,
[EndPostingDate] [datetime] NULL,
[LastAssignmentOfEndPostingDate] [bit] NULL,
[EndClaimTransactionID] [int] NULL,
[DKPostingDateID] [int] NULL,
[DKEndPostingDateID] [int] NULL,
[RelativePrecedence] [int] NULL
) ON [PRIMARY]

And here is the constraint with the function I created to make this happen

Alter FUNCTION FN_IsLastAssigned(@ClaimId INT)
RETURNS INT
AS

BEGIN
DECLARE @LastAssignment Int

SET @LastAssignment=(
SELECT COUNT(*)
FROM ClaimAccounting_Assignments AS caa
WHERE claimId=@ClaimID AND caa.LastAssignment=1)

RETURN @LastAssignment

END

Here is the Constraint - It should check to see that there are no current records for that claim that have LastAssignment=1 when it inserts into the table:

ALTER TABLE ClaimAccounting_Assignments
WITH NOCHECK
ADD CONSTRAINT  CK_Constraint_CAA_LastAssignments
CHECK ( dbo.FN_IsLastAssigned(ClaimId)=0)

I have a feeling it is something silly I am missing but I don't know.  Any help would be greatly appreciated

Here is some data to do testing with if you need.

INSERT INTO ClaimAccounting_Assignments

VALUES  (1
    , -- PracticeID
     1191
    , -- ClaimID
     12345
    , -- ClaimTransactionID
     0
    , -- InsurancePolicyID
     0
    , -- InsuranceCompanyPlanID
     0
    , -- PatientID
    0
    , -- LastAssignment
     NULL
    , -- Status
     '2012-07-03 16:56:49'
    , -- PostingDate
     '2012-07-03 16:56:49'
    , -- EndPostingDate
     NULL
    , -- LastAssignmentOfEndPostingDate
     0
    , -- EndClaimTransactionID
     0
    , -- DKPostingDateID
     0
    , -- DKEndPostingDateID
     0  -- RelativePrecedence
    ),
    VALUES  (1
    , -- PracticeID
     1191
    , -- ClaimID
     12346
    , -- ClaimTransactionID
     0
    , -- InsurancePolicyID
     0
    , -- InsuranceCompanyPlanID
     0
    , -- PatientID
    0
    , -- LastAssignment
     1
    , -- Status
     '2012-07-04 16:56:49'
    , -- PostingDate
     '2012-07-04 16:56:49'
    , -- EndPostingDate
     NULL
    , -- LastAssignmentOfEndPostingDate
     0
    , -- EndClaimTransactionID
     0
    , -- DKPostingDateID
     0
    , -- DKEndPostingDateID
     0  -- RelativePrecedence
    )
    ,
    VALUES  (1
    , -- PracticeID
     1191
    , -- ClaimID
     12347
    , -- ClaimTransactionID
     0
    , -- InsurancePolicyID
     0
    , -- InsuranceCompanyPlanID
     0
    , -- PatientID
    0
    , -- LastAssignment
     1
    , -- Status
     '2012-07-05 16:56:49'
    , -- PostingDate
     '2012-07-05 16:56:49'
    , -- EndPostingDate
     NULL
    , -- LastAssignmentOfEndPostingDate
     0
    , -- EndClaimTransactionID
     0
    , -- DKPostingDateID
     0
    , -- DKEndPostingDateID
     0  -- RelativePrecedence
    )

You might want to look up in some reliable resource (e.g. the f... manual, or in the SQL standard), when exactly CHECK constraints are verified.  And if it turns out to be "after the INSERT has been processed", then you might try to figure out how the count of existing matching rows can ever be zero.

Apart from that, most engines disallow referencing tables in CHECK constraints.  It's hopelessly annoying, but there is a very good reason for it !!!.  That reason is that the CHECK constraint should also be checked in the event of an update occurring to the referenced table, that somehow impacts the result of this CHECK constraint for some row of the table in which it is declared.

Engines typically do not know how to do this efficiently.  So they impose that limitation upon the users so that at least the integrity of the data is not compromised.  Do not make the mistake of thinking that you are smart when you discover that a UDF with some SELECT can circumvent the limitation.  You're only deluding yourself.  And a couple of months later, you'll just be the next one to ask yet another "CHECK constraint not working" question.


mongodb 2.4.1 instance shutdown without log I am running mongodb in a replica set of 3 nodes (no sharding). But since 5 days the secondary instance shuts down intermittently, once in 2 days.

Log does not indicate any event related to shutdown, but shows error related to secondary running TTL expiry.

Thu Dec 26 16:28:25.404 [conn12625]  authenticate db: local { authenticate: 1, nonce: "**", user: "__system", key: "***" }
Thu Dec 26 16:28:25.404 [conn12626]  authenticate db: local { authenticate: 1, nonce: "**", user: "__system", key: "***" }
        Thu Dec 26 16:28:29.652 [TTLMonitor] Assertion: 13312:replSet error : logOp() but not primary?
0xdc7f71 0xd8963b 0xa63ca3 0xa60f69 0xa72fd4 0xc3d4c1 0xc3e725 0xd8c233 0xd8cce4 0xe10879 0x35040079d1 0x35038e88fd
 /MONGO/dir/product/bin/mongod(_ZN5mongo15printStackTraceERSo+0x21) [0xdc7f71]
 /MONGO/dir/product/bin/mongod(_ZN5mongo11msgassertedEiPKc+0x9b) [0xd8963b]
 /MONGO/dir/product/bin/mongod() [0xa63ca3]
 /MONGO/dir/product/bin/mongod(_ZN5mongo5logOpEPKcS1_RKNS_7BSONObjEPS2_Pbb+0x49) [0xa60f69]
 /MONGO/dir/product/bin/mongod(_ZN5mongo13deleteObjectsEPKcNS_7BSONObjEbbbPNS_11RemoveSaverE+0x10d4) [0xa72fd4]
 /MONGO/dir/product/bin/mongod(_ZN5mongo10TTLMonitor10doTTLForDBERKSs+0xfe1) [0xc3d4c1]
 /MONGO/dir/product/bin/mongod(_ZN5mongo10TTLMonitor3runEv+0x345) [0xc3e725]
 /MONGO/dir/product/bin/mongod(_ZN5mongo13BackgroundJob7jobBodyEN5boost10shared_ptrINS0_9JobStatusEEE+0xc3) [0xd8c233]
 /MONGO/dir/product/bin/mongod(_ZN5boost6detail11thread_dataINS_3_bi6bind_tIvNS_4_mfi3mf1IvN5mongo13BackgroundJobENS_10shared_ptrINS7_9JobStatusEEEEENS2_5list2INS2_5valueIPS7_EENSD_ISA_EEEEEEE3runEv+0x74) [0xd8cce4]
 /MONGO/dir/product/bin/mongod() [0xe10879]
 /lib64/libpthread.so.0() [0x35040079d1]
 /lib64/libc.so.6(clone+0x6d) [0x35038e88fd]
Thu Dec 26 16:28:29.655 [TTLMonitor] ERROR: error processing ttl for db: my_db 13312 replSet error : logOp() but not primary?

There is a mongodb bug reported in ver 2.4/2.4.1 about this error.
https://jira.mongodb.org/browse/SERVER-9261

My question is will this error ultimately cause a replica to crash?

Note: Due to some setting I'm unable to get the core dump of crash.

UPDATE
On Primary node which also shutdown, I studied the /var/log/messages of that date. Found below logs

kernel: INFO: task mongod:11514 blocked for more than 120 seconds.
Dec 28 03:24:33 <hostname> kernel: "echo 0 >
/proc/sys/kernel/hung_task_timeout_secs" disables this message.
Dec 28 03:24:33 <hostname> kernel: mongod        D 0000000000000001
0 11514      1 0x00000080

It seems like the mongod process is being blocked for more than 120 seconds during flush of cache. And during this block the mongod instance is assumed to be shutdown

It looks like the assertion you are encountering is SERVER-9053: TTL index asserts on 2.4 secondary, which was fixed in 2.4.2. This appears to be a race condition where the TTL monitor thread would incorrectly try to apply TTL deletes on a secondary. The assertion does not appear to be fatal; if it was there should be a "Fatal assertion" logged followed by the  mongod process shutting down.

Unexplained shutdowns without MongoDB log messages are commonly the result of something external to mongod, such as the Out-of-Memory (OOM) Killer on Linux. You can usually find more info on activity such as the OOM Killer by searching your system logs.

Note: MongoDB 2.4.1 was released in April, 2013 and the 2.4 release series reached end of life in March, 2016. I'd strongly recommend upgrading to the final 2.4.14 release and then planning an upgrade to a supported version.


Alternative of trigger to insert updated date in a column I am looking for some best practice to insert the update date value in column whenever update query is run without using any trigger. So could anyone suggest me is there any best way except using Trigger or manually entering the update date value with update statement.
Thanks

I would use the DELETED table that is available when you do an update.
this is done through the OUTPUT Clause (Transact-SQL)

there is an example on the link above.

USE tempdb;
GO
CREATE TABLE dbo.table1
(
    id INT,
    employee VARCHAR(32)
);
GO

INSERT INTO dbo.table1 VALUES
      (1, 'Fred')
     ,(2, 'Tom')
     ,(3, 'Sally')
     ,(4, 'Alice');
GO

DECLARE @MyTableVar TABLE
(
    id INT,
    employee VARCHAR(32)
);

PRINT 'table1, before delete'
SELECT * FROM dbo.table1;

DELETE FROM dbo.table1
OUTPUT DELETED.* INTO @MyTableVar
WHERE id = 4 OR id = 2;

PRINT 'table1, after delete'
SELECT * FROM dbo.table1;

PRINT '@MyTableVar, after delete'
SELECT * FROM @MyTableVar;

DROP TABLE dbo.table1;

...

-- Results
-- table1, before delete
-- id          employee
-------------- ------------------------------
-- 1           Fred
-- 2           Tom
-- 3           Sally
-- 4           Alice
--
-- table1, after delete
-- id          employee
-------------- ------------------------------
-- 1           Fred
-- 3           Sally
-- @MyTableVar, after delete
-- id          employee
-------------- ------------------------------
-- 2           Tom
-- 4           Alice

and this is an example of how I use it, on a specific situation.

I use dynamic sql in this case.
Look as the DELETED table is used and the records are saved into a newly created table TableBackups.dbo._KB_20140513_tblStockThreshold in case we need to rollback later on.

USE SAStockLevel

DECLARE @SQL VARCHAR(max)

SET @SQL =
'
CREATE TABLE TableBackups.dbo._KB_'+CONVERT(VARCHAR(8),GETDATE(),112)+'_tblStockThreshold
    (
      [strItemNo] [varchar](20) COLLATE Latin1_General_CI_AS
                                NOT NULL ,
      [sintMarketID] [int] NOT NULL ,
      [sintChannelID] [int] NOT NULL ,
      [lngQtyThreshold] [int] NOT NULL ,
      [dtmRecordAdded] [datetime] NOT NULL ,
      [strAddedBy] [varchar](50) COLLATE Latin1_General_CI_AS
                                 NOT NULL ,
      [dtmLastUpdated] [datetime] NOT NULL ,
      [strUpdatedBy] [varchar](50) COLLATE Latin1_General_CI_AS
                                   NOT NULL ,
      [ActiveRecord] [bit] NOT NULL ,
      [ActiveMarket] [bit] NOT NULL ,
      [ActiveChannel] [bit] NOT NULL
)

-- UK

UPDATE  dbo.tblStockThreshold
SET     lngQtyThreshold = 0
OUTPUT  DELETED.strItemNo, DELETED.sintMarketID, DELETED.sintChannelID, DELETED.lngQtyThreshold, DELETED.dtmRecordAdded, DELETED.strAddedBy, DELETED.dtmLastUpdated, DELETED.strUpdatedBy, DELETED.ActiveRecord, DELETED.ActiveMarket, DELETED.ActiveChannel
        INTO TableBackups.dbo._KB_'+CONVERT(VARCHAR(8),GETDATE(),112)+'_tblStockThreshold
WHERE   strItemNo IN (
        SELECT  strItemNo
        FROM    dbo.tblOrgGrpStockLevel
        WHERE   lngTotal - ( lngAllocated + lngReserved ) BETWEEN 1 AND 4
                AND tintOrgGrpId = 1
                AND lngTotal <> 0 )
        AND sintMarketID = 1
        AND sintChannelID IN ( 1, 2 )
        AND lngQtyThreshold < 6666
        AND lngQtyThreshold <> 0
'
print @sql

BEGIN TRANSACTION T1
EXEC (@SQL)

COMMIT TRANSACTION T1

select * from TableBackups.dbo._KB_20140513_tblStockThreshold


Stored procedure with recompile causing CPU spike There is a heavily queried stored procedure that uses a bad plan once in few days. As the number of execution grows, one of the bad plans causes a 100% CPU spike. In order to mitigate this, we have changed the procedure to do a recompile every time it executes. Now, the CPU usage stayed beyond 90% constant.

What is a better way to deal with this problem?
How would I do a rollback for an immediate solution?

Without more detailed info it is difficult to be more specific regarding what should be done, but I think I could say the following:

An increase in CPU is to be expected when recompiling a frequently executed stored procedure upon each execution.
For the moment, if it were me, I would remove the WITH RECOMPILE because an occasional 100% CPU utilization that can be fixed with a recompile is preferable to a sustained 90% CPU utilization.
If the bad cached plan / parameter-sniffing issue only happens "once every few days", then that implies one or both of the following:

an infrequently used value that has a widely different amount of rows in the table/index than the more frequently used values
statistics getting out of date.

In which case:

Make sure you are updating statistics regularly, at least on the table(s) referenced in this stored procedure that are causing the CPU spike.
If you can figure out which value(s) and which statement(s) in the stored procedure cause the CPU spike, then it might be possible to place one or two statements in an IF block that make use of OPTION (RECOMPILE) (i.e. statement-level recompile, or at least handle in a slightly different way that is more appropriate for the infrequent, problem-causing value(s).

Something you could try is putting the following in a SQL Server Agent job that runs every few minutes. You just need to change the variable values above the dashed line, and then the database name references below that line, both marked by inline comments.

Please keep in mind that the CPU % calculated here is not the same that you would see in Task Manager, but it seems to be close enough (the value in Task Manager is a little higher).

DECLARE @MaxCPU INT = 75; -- only proceed if SQL Server CPU is at least this much

DECLARE @MaxCpuSeconds INT = 60,
        @MaxElapsedSeconds INT = 60,
        @ProcName [sysname] = N'{proc_name}',
        @KillSession BIT = 0;
---  ---  ---  ---  ---  ---  ---  ---  ---  ---  ---  ---  ---
DECLARE @DefaultCPU FLOAT,
        @DefaultCPUbase FLOAT;

SELECT @DefaultCPU = ISNULL(@DefaultCPU,
                            CASE cntr.[counter_name]
                               WHEN N'CPU usage %' THEN cntr.[cntr_value]
                               ELSE NULL END),
       @DefaultCPUbase = ISNULL(@DefaultCPUbase,
                                 CASE cntr.[counter_name]
                                   WHEN N'CPU usage % base' THEN cntr.[cntr_value]
                                   ELSE NULL END)
FROM   sys.dm_os_performance_counters cntr
WHERE  cntr.[object_name] = N'SQLServer:Workload Group Stats'
AND    cntr.[counter_name] LIKE N'CPU usage [%]%'
AND    cntr.[instance_name] = N'default';

SELECT @DefaultCPU, @DefaultCPUbase, 100 * (@DefaultCPU / @DefaultCPUbase) AS [Default];

IF ((100 * (@DefaultCPU / @DefaultCPUbase)) >= @MaxCPU)
BEGIN
  DECLARE @SessionID INT;

  SELECT @SessionID = req.[session_id]
  FROM   sys.dm_exec_requests req
  CROSS APPLY sys.dm_exec_sql_text(req.[sql_handle]) txt
  WHERE  OBJECT_NAME(txt.[objectid], txt.[dbid]) = @ProcName
  AND    (req.[cpu_time] > (@MaxCpuSeconds * 1000)
     OR   req.[total_elapsed_time] > (@MaxElapsedSeconds * 1000))
  AND    txt.[dbid] = DB_ID(N'{db_name_for_proc}'); -- put your DB name here

  IF (@SessionID IS NOT NULL)
  BEGIN
    EXEC {db_name_for_proc}.sys.sp_recompile @ProcName; -- put your DB name here
    PRINT 'Recompiled ' + @ProcName;

    IF (@KillSession = 1)
    BEGIN
      DECLARE @SQL NVARCHAR(4000);
      SET @SQL = N'KILL ' + CONVERT(NVARCHAR(50), @SessionID);
      PRINT @SQL;
      EXEC (@SQL);
    END;
  END; -- IF (@SessionID IS NOT NULL)

END; -- IF ((100 * (@DefaultCPU / @DefaultCPUbase)) >= @MaxCPU)


Converting "string" dates with errors to normal dates or NULL I have an old table containing dates stored as strings like '20191001'.
I have created a new field of type DATE and I try to recover the old dates in the new field :

UPDATE extcg SET newdate=to_date(olddate,'yyyymmdd');

The problem is that some of these dates are invalid (20190231). I would like to skip the conversion for these dates and leave the new field NULL.

Is there a way to exclude these in a WHERE statement ?

For those interested, here is a function that does the job :

CREATE FUNCTION to_date_or_null(ADate TEXT, AFormat TEXT) RETURNS DATE AS
$BODY$
BEGIN
    RETURN to_date(ADate,AFormat);
EXCEPTION
    WHEN others THEN RETURN NULL;
END;
$BODY$ LANGUAGE plpgsql IMMUTABLE STRICT;

I used it this way :

UPDATE extcg SET newdate=to_date_or_null(olddate,'yyyymmdd');

And thanks to @Luuk for mastering Google searches ;)


Chronological list of statements from Oracle trace I'm using a trace in Oracle (11.2) to elucidate the behavior of a client application, and would like to view a list of all of the SQL statements that it submits, in the order in which they were submitted. Toward this end, I enable the trace using:

EXECUTE DBMS_SYSTEM.SET_EV(<sid>, <serial#>, 10046, 12, '')

This produces a trace file with the information I'm seeking, though in a format that is difficult for human consumption. Accordingly, I format the raw trace using:

tkprof raw.trc formatted.out explain=user/password aggregate=no sys=no waits=yes record=formatted.rec

This facilitates readability, but also obscures the real-world sequence of events in both of the output files (formatted.out, formatted.rec).

Specifically, the client appropriately holds open the cursors for popular statements and re-executes them on subsequent invocations. The default (and only?) behavior of tkprof is to:

In the main output file, combine the stats for all executions
In the record file, only list the statement when initially parsed

(The latter of which assumes I'm lucky enough to catch the parse event in the first place, which usually requires flushing the shared pool before enabling the trace, but I digress.)

For example, if the client submits statements like the following and holds the cursors open:

SELECT column1 FROM table1 WHERE column2 = :value;
INSERT INTO table1 SET column1 = :value1, column2 = :value2;
SELECT column1 FROM table1 WHERE column2 = :value;

The output record file will only show two statements:

SELECT column1 FROM table1 WHERE column2 = :value;
INSERT INTO table1 SET column1 = :value1, column2 = :value2;

There is no evidence, as far as I can tell, that the first statement was executed a second time.

Similarly, the main trace file will only show reports for two statements, though the first one will indicate that the SELECT statement was executed/fetched twice (only a partial excerpt follows):

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      2      0.00       0.00          0          4          6           0
Fetch        2      0.00       0.00          0          0          0           2
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        5      0.00       0.00          0          4          6           2

It's not clear from this formatted output which statements were executed in which order - only that one of them was executed twice at some point.

As far as I can see, I need to read the raw trace file and laboriously follow the trail of cursor numbers (some of which get reused for different statements over time) to understand the actual sequence of events, as they occurred in the real world.

My question is:

Is there some tkprof option, or tkprof alternative, that will extract from a raw trace file a list of all SQL statements in the
order that they were executed, even in cases where the same cursor is
held open and executed multiple times?

Ideally, this output would include the bind variable values for each execution, or at least the SQL ID or cursor ID, so I could go back and find them in the raw trace.

Thanks in advance.

While TKProf is generally referred to as something that makes a trace file 'human readable' it is primarily a Profiling tool that aggregates the SQL statements to find the most impactful.

The raw trace file can be used. However if you are primarily interested in what the client sends to the server, you should look at logging options in JDBC (
https://www.oracle.com/technetwork/topics/11-2.pdf ) or in the SQL Net layer (see Oracle Client trace not generating )


How should we give privileges/permissions to group to access different db's I'm new to working on RDS postgresql instance, I've a group and added a couple of users to the group. I need to give permission to users in the group to allow them to access other dbs like x ,y and z

I logged in as admin user to y database and gave permission to the group, but the user is not able to view tables in other dbs. Group name is dev_role. All tables are in public schema.

y=> ALTER DEFAULT PRIVILEGES GRANT SELECT, INSERT, UPDATE, TRUNCATE, REFERENCES, TRIGGER ON TABLES TO dev_role;
ALTER DEFAULT PRIVILEGES

Even Tried

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO dev_role;

The user logged into y db and was trying to run \dt, the user is getting an error:

Did not find any relations.

  psql (10.11, server 11.5)

What could be the issue and can someone point me correct way to give permissions.

Here is one thing, there is no difference between regular PostgreSQL and RDS PostgreSQL when it comes to ROLES,GRANT, PRIVILEGES, and GRANT. In your case, the user must be connecting to other than y DB. Maybe he connection default DB Postgres.  Tested in my rds instance.

GRANT USAGE ON SCHEMA public TO dev_role;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO dev_role;

with this working fine for me, ref.docs:

y=> \dt
         List of relations
 Schema |  Name  | Type  |  Owner
--------+--------+-------+----------
 public | table1 | table | postgres
 public | table2 | table | postgres
(2 rows)

and when I connected to other than y DB,

postgres=> \dt
No relations found.

Since you didn't give permission here.


Select the first row returned from the join query based on a condition I have 2 tables PRODUCT and MANUFACTURER

Table1: PRODUCT, Columns: PRODUCT_ID,PRODUCT_NAME

Table2: MANUFACTURER, Columns: PRODUCT_ID,MANF_ID,ITEM_NO,DEFAULT_MANF

I have to select the default manufacture of a product from the MANUFACTURER table. If no default manufacture is available, i will select a manufacturer by the ITEM_NO from the MANUFACTURER table.

I have written query for this as below:

SELECT p.PRODUCT_ID, p.PRODUCT_NAME, m.MANF_ID, m.ITEM_NO, m.DEFAULT_MANF
FROM PRODUCT p
INNER JOIN MANUFACTURER m on p.PRODUCT_ID = m.PRODUCT_ID
WHERE p.PRODUCT_ID = 'PROD001'
AND m.MANF_ID = (SELECT TOP 1 MANF_ID FROM MANUFACTURER man WHERE man.PRODUCT_ID = p.PRODUCT_ID and coalesce(man.DEFAULT_MANF, 'N') = 'Y' ORDER BY man.DEFAULT_MANF, man.MANF_ID )

This query works as expected in SQL Server, but on Oracle it wont work as I am using 'TOP' in the query.

Is there any way I can modify the query to work in both SQL Server and Oracle?

If your version of Oracle is 12 or above, you can use FETCH FIRST n ROWS ONLY (doc link):

SELECT p.PRODUCT_ID, p.PRODUCT_NAME, m.MANF_ID, m.ITEM_NO, m.DEFAULT_MANF
FROM PRODUCT p
INNER JOIN MANUFACTURER m on p.PRODUCT_ID = m.PRODUCT_ID
WHERE p.PRODUCT_ID = 'PROD001'
AND m.MANF_ID = (SELECT MANF_ID FROM MANUFACTURER man WHERE man.PRODUCT_ID = p.PRODUCT_ID and coalesce(man.DEFAULT_MANF, 'N') = 'Y' ORDER BY man.DEFAULT_MANF, man.MANF_ID FETCH FIRST 1 ROWS ONLY)

If you're on Oracle 11 or below you need to use rownum=1 instead.

I'll add that if your schema was designed properly there would only be one default manufacturer for a product, and the subquery wouldn't be needed.

To actually answer your question though, you can abuse row_number.

SELECT p.PRODUCT_ID, p.PRODUCT_NAME, m.MANF_ID, m.ITEM_NO, m.DEFAULT_MANF
FROM PRODUCT p
INNER JOIN MANUFACTURER m on p.PRODUCT_ID = m.PRODUCT_ID
WHERE p.PRODUCT_ID = 'PROD001'
AND m.MANF_ID = (
    SELECT MANF_ID FROM (
        SELECT MANF_ID, ROW_NUMBER() OVER (ORDER BY man.DEFAULT_MANF, man.MANF_ID) as RN
        FROM MANUFACTURER man
        WHERE man.PRODUCT_ID = p.PRODUCT_ID
        and coalesce(man.DEFAULT_MANF, 'N') = 'Y'
    ) HACK
    WHERE RN=1
);


How to get MIN, MAX and DIFFERENCE among rows with the same value in a certain column I have a query that is returning all RATINGS for STORES and a RATING_DATE. There isn't a fixed amount of ratings, it could be from one to unlimited rows that are returns that have the same STORE_ID value.

I'm trying to find the min, max, and difference for the STORE_ID, like in the example below:

Example data:

STORE_ID RATING_DATE    RATING
-------- -----------    ------
34       2015-01-20     4.0
34       2016-01-01     4.14
34       2017-01-01     4.56
34       2018-01-01     4.78
46       2015-01-01     3.5
46       2017-01-01     3.7
46       2016-01-01     3.95

Desired results:

STORE_ID DIFFERENCE MIN  MAX
-------- ---------- ---  ----
34      .78         4.0  4.78
36      .45         3.5  3.95

In addition to other answers - and to make it clear that subqueries or CTEs are not needed here. We can have the same result without them. Use whatever is more clear to you:

select
     s.store_id,
     max(s.rating) - min(s.rating) as difference,
     min(s.rating) as min_rating,
     max(s.rating) as max_rating
from
     storeRatings as s
group by
     s.store_id
  ;


Good reason to denormalize? I have a database with about 40-50 tables.  All but 5 are part of a giant hierarchy of 1:M relationships that all point back to one solitary parent (call it "Project").  Each table is joined to its direct parent using a foreign key to the parent's primary key, which is an identity field.  Some branches go as many as 6 or 7 levels deep.  Many of the tables have millions of rows.  Nearly all queries require returning all records of a particular table that pertain to a single project.  Queries normally only return fields from the single entity in question.  Thus a typical query (practically all queries other than random ad hoc) looks like this:

SELECT a.* FROM a
JOIN b ON a.id=b.id
JOIN c ON b.id=c.id
JOIN d ON c.id=d.id
JOIN Project p ON d.ProjectID=p.ProjectID
WHERE p.ProjectID = 12345

As you can imagine, the farther you go down the hierarchy the worse the queries perform.  I considered denormalizing on the one field only by redundantly persisting the ProjectID field throughout all levels of the hierarchy, and creating the clustered index on ProjectID in each table.  This would allow me to satisfy all data requests with a single index seek.  Doing so dramatically improves query performance.  However, I have no idea if doing this negatively impacts the fidelity of the schema design.  I'm hoping someone can provide me some insight in case I am missing something.

We do something similar with client id. Yes it can improve performance if you don't need to go through all those intervening tables in every query. However, and it's an important however, this is best done only if you are using a surrogate key that never changes. Otherwise a change of the project id could require a cascade of updates that affect every table and lockup the system. I suspect project_id is as unlikely to change as client Id (client name, now that's another story) and so you might be fine. But please do consider if you will have updates to the field you denormalize, Also it is critical to set up a way to make sure the tables with the denormalized fields cannot get out of synch  with the main table (PK/FK relationships are good for this and you might need cascading updates set (although I personally prefer not to use them if I can help it).)


Oracle service_name vs database name I am reading about Oracle and I am confused with the two names: service_name and database name.  Do they both refer to the database name used while creating a database with the CREATE DATABASE xxxxxx command?

Can the service name be different from the database name? If yes, how do we map the service name to the database name on the server? Because as a client, we use the service name to connect to the database, how does the server map the service name to the database name?

SERVICE_NAMES specifies one or more names by which clients can connect to the instance. The instance registers its service names with the listener. When a client requests a service, the listener determines which instances offer the requested service and routes the client to the appropriate instance.

You can specify multiple service names in order to distinguish among different users of the same database. For example:

SERVICE_NAMES = sales.acme.com, widgetsales.acme.com

You can also use service names to identify a single service that is available from two different databases through the use of replication.

If you do not qualify the names in this parameter with a domain, Oracle qualifies them with the value of the DB_DOMAIN parameter. If DB_DOMAIN is not specified, then no domain will be applied to the non-qualified SERVICE_NAMES values.

DATABASE NAME is the name of the physical database structure. It is stored at the controlfile and datafile header. It is used to identify all physical structures that belong to the same database. It can be defined at install time. It is originally defined by the static instance parameter database_name, and it cannot be changed, and the only way to change it is by means of rebuilding the controlfile and resetting the log sequence.

To conclude, you can have a common name for both of them. However, you might need different service names to distinguish among different users of the same database.


I created a DB schema for a project relating to collecting comments for web pages, is relational the way to go and how are my relationships? I'm curious if relational DB is the way to go, if the current relationships in the diagram make sense, and whether it will bode well for coding.

A couple of cmments:

CommentReplies seems to be implementing many-to-many.  I would expect it to be one-to-many -- as in, each comment has one 'parent'.  One-to-many does not need an extra table; you simply have parent_id as a column in Comments.

Consider using ENUM datatype instead of adding a XxTypes table.


Join Two queries with different declarations I need to join two different queries to get some columns from both. The first one has a CTE.

Query1:

   WITH StatusChanges AS
(
    SELECT
       HistoryID,PartID,Status, DateAdded,
       rnAsset = ROW_NUMBER() OVER (PARTITION BY PartID ORDER BY DateAdded)
    FROM aAssetHistory
) SELECT
    A.isMarketed,A.AssetDescription,A.aPartID,A.SerialNumber,A.PartNumber,
    A.Location,A.Vintage,Ap.Fund,A.ProjectNumber,AP.ProjectName ,
    (
       SELECT Top 1 Disposition
       FROM aAssetDisposition AD
       WHERE (A.aPartid = AD.AssetID)) As Disposition, A.LocStatus ,
          CASE
             WHEN A.LocStatus='In Repair' THEN EstimatedCompletion
             WHEN A.LocStatus like '%lease%' THEN IIF(ProjectedReDeliveryDate='1/1/1900',LeaseExpiry,ProjectedReDeliveryDate)
             WHEN A.LocStatus='Stored' OR A.LocStatus='Marketed' THEN SC.DateAdded
          END 'Available',A.AssetComment,
          Cast(ISNULL((SELECT Top 1 1
                       FROM aAssetMarketingDocuments AMDocs
                       WHERE A.aPartid=AMDocs.aPartid),0) As bit
       ) Attachment
    FROM aAsset A
INNER JOIN aProject AP On A.ProjectNumber=AP.ProjectNumber
LEFT JOIN aAsset ParentA  On A.ParentACPartID=ParentA.aPartid
LEFT JOIN aEngineShop ES ON A.LocStatus='In Repair' AND ES.PartID=A.aPartid
LEFT JOIN vLeasedAsset LA ON A.LocStatus like '%lease%' AND LA.aPartID=A.aPartid
LEFT JOIN (
          SELECT SC1.PartID, SC1.DateAdded,RN= ROW_NUMBER() OVER (PARTITION BY SC1.PartID ORDER BY SC1.DateAdded DESC)
          FROM StatusChanges SC1
          LEFT JOIN StatusChanges SC2 ON SC1.PartID=SC2.PartID AND SC2.rnAsset=SC1.rnAsset-1
          WHERE (SC1.Status<>SC2.Status OR SC2.HistoryID IS NULL)) SC
ON (A.LocStatus='Stored' OR A.LocStatus='Marketed') AND A.aPartid=SC.PartID AND SC.rn = 1
WHERE ((A.isMarketed=1 AND (ParentA.isMarketed IS NULL OR ParentA.isMarketed=0)) )

Query2:

 SELECT
    A.[AssetMarketingID],B.PartID, aCompany.Company 'PotentialCustomer', A.isActive,
    ISNULL(CAST(Lease AS bit),0) 'Lease', ISNULL(CAST(Exchange AS bit),0) 'Exchange',
    ISNULL(CAST(Sale AS bit),0) 'Sale' ,DocumentStage,ContactTB.FirstName + ' ' + ContactTB.LastName 'Contact',
    A.ContactDate,A.Status,B.Amount, A.DateCreated, [Notes]
FROM aAssetMarketing A
LEFT JOIN aAssetMarketingAssets B ON A.AssetMarketingID=B.AssetMarketingID
LEFT JOIN aCompany On [PotentialCustomerID]=COid
LEFT JOIN aCompanyContact ContactTB ON [MarketingContactID]=ContactID
LEFT JOIN (
          select AssetMarketingID,max(case when DealStructure = 'Exchange' then 1 ELSE 0 end) Exchange,
          max(case when DealStructure = 'Lease' then 1 Else 0 end) Lease,
          max(case when DealStructure = 'Sale' then 1 Else 0 end) Sale
FROM [aAssetMarketingDealStructure]
GROUP BY AssetMarketingID) DS
    ON DS.AssetMarketingID=A.AssetMarketingID
ORDER BY A.ContactDate DESC

WITH StatusChanges AS
(
    SELECT
       HistoryID,PartID,Status, DateAdded,
       rnAsset = ROW_NUMBER() OVER (PARTITION BY PartID ORDER BY DateAdded)
    FROM aAssetHistory
) SELECT
    A.isMarketed,A.AssetDescription,A.aPartID,A.SerialNumber,A.PartNumber,
    A.Location,A.Vintage,Ap.Fund,A.ProjectNumber,AP.ProjectName ,
    (
       SELECT Top 1 Disposition
       FROM aAssetDisposition AD
       WHERE (A.aPartid = AD.AssetID)) As Disposition, A.LocStatus ,
          CASE
             WHEN A.LocStatus='In Repair' THEN EstimatedCompletion
             WHEN A.LocStatus like '%lease%' THEN IIF(ProjectedReDeliveryDate='1/1/1900',LeaseExpiry,ProjectedReDeliveryDate)
             WHEN A.LocStatus='Stored' OR A.LocStatus='Marketed' THEN SC.DateAdded
          END 'Available',A.AssetComment,
          Cast(ISNULL((SELECT Top 1 1
                       FROM aAssetMarketingDocuments AMDocs
                       WHERE A.aPartid=AMDocs.aPartid),0) As bit
       ) Attachment
    INTO #tmp
    FROM aAsset A
INNER JOIN aProject AP On A.ProjectNumber=AP.ProjectNumber
LEFT JOIN aAsset ParentA  On A.ParentACPartID=ParentA.aPartid
LEFT JOIN aEngineShop ES ON A.LocStatus='In Repair' AND ES.PartID=A.aPartid
LEFT JOIN vLeasedAsset LA ON A.LocStatus like '%lease%' AND LA.aPartID=A.aPartid
LEFT JOIN (
          SELECT SC1.PartID, SC1.DateAdded,RN= ROW_NUMBER() OVER (PARTITION BY SC1.PartID ORDER BY SC1.DateAdded DESC)
          FROM StatusChanges SC1
          LEFT JOIN StatusChanges SC2 ON SC1.PartID=SC2.PartID AND SC2.rnAsset=SC1.rnAsset-1
          WHERE (SC1.Status<>SC2.Status OR SC2.HistoryID IS NULL)) SC
ON (A.LocStatus='Stored' OR A.LocStatus='Marketed') AND A.aPartid=SC.PartID AND SC.rn = 1
WHERE ((A.isMarketed=1 AND (ParentA.isMarketed IS NULL OR ParentA.isMarketed=0)) )

Query2:

SELECT
    A.[AssetMarketingID],B.PartID, aCompany.Company 'PotentialCustomer', A.isActive,
    ISNULL(CAST(Lease AS bit),0) 'Lease', ISNULL(CAST(Exchange AS bit),0) 'Exchange',
    ISNULL(CAST(Sale AS bit),0) 'Sale' ,DocumentStage,ContactTB.FirstName + ' ' + ContactTB.LastName 'Contact',
    A.ContactDate,A.Status,B.Amount, A.DateCreated, [Notes]
FROM aAssetMarketing A
LEFT JOIN aAssetMarketingAssets B ON A.AssetMarketingID=B.AssetMarketingID
LEFT JOIN aCompany On [PotentialCustomerID]=COid
LEFT JOIN aCompanyContact ContactTB ON [MarketingContactID]=ContactID
LEFT JOIN (
          select AssetMarketingID,max(case when DealStructure = 'Exchange' then 1 ELSE 0 end) Exchange,
          max(case when DealStructure = 'Lease' then 1 Else 0 end) Lease,
          max(case when DealStructure = 'Sale' then 1 Else 0 end) Sale
FROM [aAssetMarketingDealStructure]
JOIN #tmp A ON
A.SerialNumber, A.PartNumber, Location, A.Fund, Disposition, DocumentStage, Lease, Sale
GROUP BY AssetMarketingID) DS
    ON DS.AssetMarketingID=A.AssetMarketingID
ORDER BY A.ContactDate DESC


Page Break is splitting in the middle of a single row in SSRS I have an SSRS report for an Invoice and it generally works perfectly but occasionally it will page break in the middle of a row in the main Tablix. The row will split and leave part of the text on one page and the rest on the next. The Tablix has no inherent page breaking. I was just relying on it to break between rows (regardless of which rows). There are a couple rows that repeat on each page. There is a second Tablix below the Detail one with Summary Totals. This is just some background info about the report. I'm relatively new to SSRS and haven't had the greatest luck with the formatting thus far.

Thanks for any help!

@matt you might want to adjust the tablix or row KeepTogether property. This affects how it tries to have all rows/table on the same page.


How to selectively replicate a PostgreSQL database I would like to keep two PostgreSQL databases with the same schema, but have one replicated from a master that only keeps a small portion of the live data.

Basically the master takes in live data and deletes old data outside of the time window, while the replicated slave should keep all the historical data (at least up to the limits of the disk). So in essence, the slave should replicate the master except its deletes.

Is this possible with any "off-the-shelf" PostgreSQL replication method?

Postgres built-in replication is based on log shipping, and as far as I know does not support this (you're shipping the Postgres transaction log from the master to the slaves, so everything - including deletes - gets passed along. You may be able to approximate it by keeping all the transaction logs (forever) so you can roll back to a specific point in time, but that's not a particularly efficient technique.

You can probably rig up something like this with trigger-based replication mechanisms like Slony, but a better solution might be a standard mirror plus the table auditing (tablelog) project which basically creates a historical log of what happened to a table (in a shadow schema) so you can see what was done when, and by whom.
This is best combined with 9.x-style master/slave replication, where you can read from the slaves to poke around in the audit history.


MySQL with high CPU and memory usage here we have a VPS with 3.2GHz quadcore processor with 2GB of RAM and 4GB of SWAP which need to work with a high intensive legacy system (read: bad coded). The main problem is that the CPU (~80 - 100%) and memory usage (about 50%) are going to the sky, even in a not so intensive usage.

Current /etc/my.cnf

[mysqld]
query_cache_type = ON
slow_query_log = OFF
long_query_time = 1
innodb_buffer_pool_size = 1GB
join_buffer_size = 16MB
key_buffer_size = 128MB
table_open_cache = 100000
innodb_thread_concurrency = 0
innodb_read_io_threads = 64
innodb_write_io_threads = 64
max_user_connections = 800
max_connections = 800
slow_launch_time = 1
thread_cache_size = 4
query_cache_size = 512MB

MySQL version

mysql> SELECT VERSION();
+------------+
| VERSION()  |
+------------+
| 5.5.34-cll |
+------------+
1 row in set (0.00 sec)

MyISAM index length

mysql> SELECT SUM(index_length) FROM information_schema.tables WHERE engine = 'MyISAM';
+-------------------+
| SUM(index_length) |
+-------------------+
|          82627584 |
+-------------------+
1 row in set, 2 warnings (4.55 sec)

InnoDB index length

mysql> SELECT SUM(index_length) FROM information_schema.tables WHERE ENGINE = 'InnoDB';

+-------------------+
| SUM(index_length) |
+-------------------+
|         466092032 |
+-------------------+
1 row in set, 2 warnings (2.62 sec)

Any can guide-me on what is the first step to solve or ease this issue?

There is something you are not taking into account.

MyISAM caches index pages into the Key Cache
InnoDB caches data and index pages in the Buffer Pool
I wrote a post about this : What are the main differences between InnoDB and MyISAM?

The 466092032 you gave is just the number of bytes taken by indexes. The InnoDB Storage Engine is moving data and index pages (each InnoDB page is 16K) in and out of the Buffer Pool due to its small size and the amount of data you are not accounting for.
Please refer back to my post. It will recommend how to size both key_buffer_size (which should be about 84M in you case) and the innodb_buffer_pool_size.
RECOMMENDATIONS

Increase the VPS RAM enough to fully accommodate your data.
Lower the read and write threads since you are in a VPS
Disable the query cache because it slows down InnoDB : See my post Is the overhead of frequent query cache invalidation ever worth it?


Understanding the given correlated subquery I am unable to understand how this correlated subquery works on the relation Student={Sid,Sname,marks}

Select Sname from Student s1
where (select count (*) from Student s2 where s1.marks<=s2.marks)=3

I cannot understand the way in which I should comprehend this query starting from inner query. The count (*) returns the total count of rows in a group but here is it calculating for individual condition satisfied.

Can anyone help in this regard?

zgguy's explanation is very good. It is also illustrated nicely in the execution plan (I have used SSMS2014 to generate one):

the inner query shows on the second "row"
nested loops means that for each row in the outer query, inner input in scanned and matching records are selected
filter (=3) is applied on records from previous step


MS Access replace entire table contents Info: I am setting up a new MS Access database for a small team (~10 person) project. There is a table "A" which is never modified but has relations to another table based on the field "Tag Number". This other table "B" contains all the dynamic information which is modified regularly by the team. Once a week, we receive a new Excel spreadsheet that contains the latest engineering export, which contains the entire contents of the table A. Some new records will be added and some records will be gone every week.

My question is this:

How do I replace the entire contents of the A table? I want to ensure that the relation to B table stays intact. I could delete the entire table and copy/paste in from excel (70000 rows), or I can import the excel spreadsheet to a new table and then use a combination of Update/Append/Delete queries. (Update to modify existing records, Append to add the new non existent records and Delete to remove records no longer present in the latest spreadsheet.)

Or some other, better, method.

Help please?

As Access does not support UPSERT semantics you're stuck with something along the lines:

  insert ... into tbl
  select ... from tmp_tbl
  where not exists (select 1 from tbl where tbl.id = tmp_tbl.id)`

And similar for updates and deletes using outer joins where appropriate.

That means three SQL-statements.

See this question on SO.

Edit:

I would recommend not to delete records, but instead to mark them 'invalid', that way you can decide, what to do with tags in your other table, if necessary.


Select Column Values from all the tables of database ORACLE I am trying to fetch column value from all the tables of database where column value matches.

Table A
CAMPUS_ID
1

Table B
CAMPUS_ID
1

Expected Result

TABLE   VALUE
A       1
TABLE   VALUE
B       1

Query

SELECT CAMPUS_ID FROM (SELECT A.TABLE_NAME FROM
user_tables A JOIN user_tab_columns C ON C.TABLE_NAME =
A.TABLE_NAME WHERE C.COLUMN_NAME = 'CAMPUS_ID')
WHERE CAMPUS_ID = '1'

Error Invalid Identifier

Inner query return the correct tables.

You can do something like this:

select table_name,
       extractvalue(
          xmltype(dbms_xmlgen.getxml('select count(*) c from '||table_name||' where campus_id = 1')),
         '/ROWSET/ROW/C'
       ) as count
from user_tables ut
where exists (select *
              from user_tab_columns c
              where c.table_name = ut.table_name
                and c.column_name = 'CAMPUS_ID');

dbms_xmlgen.getxml runs the passed SQL statement and returns the result as an XML value. extracvalue() is then used to extract the actual rowcount.

This will return the table name and the count of rows in that table where campus_id = 1


Results of using a comparison operator in the ORDER BY clause There is a table x:
select * from x;

+------+------+
| a    | b    |
+------+------+
| aaa  | 999  |
| bbb  | 888  |
| ccc  | 777  |
+------+------+

What do the following queries do?
select * from x order by a < b;
select * from x order by a = b;
select * from x order by a <> b;
select * from x order by a <> b, b;

What is the name for this operator?
I have not found anything in the MySQL documentation on this topic.

This is common comparison operators.
When comparison is performed then some boolean result is produced. It may be TRUE, FALSE or NULL.
These 3 values are related as
NULL < FALSE < TRUE

This relation is used for sorting.
For example:
select * from x order by a<b;

If a and/or b is NULL then the result of ordering expression (comparison result) is NULL, and such records will be at the beginning of the output.
If a less then b then the result of ordering expression is TRUE and such records will be at the end of the output.
The records where a is greater or equal to b will be in the middle.


Cause of High Disk Queue Length in SQL Server I have a fairly high-throughput application that occasionally decides to collapse on me. It's not very often - about once every ~3 weeks or so. When it does, if I check out perfmon, I see 100% "Avg. Disk Queue Length" pegging the server.

During these times, I also see lots of nice connection failed messages from SQL Server.

I'm no SQL Server expert, I can do the basics for indexing, taking backups, etc., but that's it.

What would cause something like this? I was thinking perhaps it was a resize of the database (it was down to ~300MB available [and it's a 30 gig database]), or maybe some reindexing gone nuts?

I do have one table in particular that has tons of inserts. Very few reads, but many inserts per second isn't unusual at all.

The server has only ~4 gig of RAM as well, but we do have a dedicated warehouse box that rolls up data every night where most of the heavy querying is directed.

Anyone got any thoughts on what might cause that huge queue length?

OK, so, from what I can tell, it was related to a bunch of things:

resizing files (data and log both)
large number of inserts happening while 1. was going on
one particularly heavy query running while 1. was going on
a hard drive with some pretty high i/o times in general
lack of memory on the server, so hitting the page file on top of everything else

Here were my resolutions:

change the autogrowth settings so I'd have consistent growth when it happens, manually grew both the temp and primary database files to have more space, added additional vlogs to the temp database, and set up a notification so I can manually grow when the database gets below a certain space level.
[nothing to be done about it]
made the heavy query run less often; it was loading up data the user didn't always need, so changed it to run on-demand
[working on getting a new server, this app is growing fast]
[working on getting a new server, this app is growing fast]

So, anyway, it was a combination of a whole bunch of different things, mostly related to SQL, but not exclusively (so Will was correct there).

I'd love to split the answer between everyone, as they had portions of it right, but what can you do...


Can a Key Preserved Table have a Composite Primary Key, on Oracle db? I'm trying to create an updatable view.

I understand that the db must be able to identify one single row that I'm trying to update.

I believe I satisfied this requirement. There are no aggregates. Each row in the view has a 1-to-1 relationship with rows in the source table which I'm trying to update (MY_CFG_TABLE below).

But my view is not updatable. Why not? Does it have something to do with the composite key?

CFG_VALUE is the column I want to update.

I want a join-view, rather than subquery, so that i can see, in the view, rows in ALL_TAB_COLUMNS for which there's no corresponding row in MY_CFG_TABLE (for that, it will have to be changed to outer join, but for now just trying to make the update work with an inner.)

In the end, I may resort to an INSTEAD OF trigger, but for now I would like to make an updatable join-view-- or understand why I cannot.

Here's the update statement which fails:

update VBA_CFG_JOIN_V
SET CFG_VALUE = 'TRUE'
WHERE TABLE_NAME = 'APN_GR'
AND COLUMN_ID = 1
AND CFG_NAME = 'PART_ID';

Here's the error message:

  cannot modify a column which maps to a non key-preserved table

How can I make my source table, MY_CFG_TABLE, a Key Preserved Table?

View:

CREATE VIEW VBA_CFG_JOIN_V
AS SELECT F.TABLE_NAME, T.COLUMN_NAME, F.COLUMN_ID, F.CFG_NAME, F.CFG_VALUE
FROM ALL_TAB_COLUMNS T, MY_CFG_TABLE F
WHERE F.TABLE_NAME = T.TABLE_NAME AND F.COLUMN_ID = T.COLUMN_ID;

Source tables:

ALL_TAB_COLUMNS is a built-in Oracle view. (Note, I'm not trying to update ALL_TAB_COLUMNS).
https://docs.oracle.com/cd/B19306_01/server.102/b14237/statviews_2094.htm

MY_CFG_TABLE:

CREATE TABLE "GSM_OWNER"."MY_CFG_TABLE"
   (    "TABLE_NAME" VARCHAR2(20 BYTE) NOT NULL ENABLE,
    "COLUMN_ID" VARCHAR2(20 BYTE) NOT NULL ENABLE,
    "CFG_NAME" VARCHAR2(20 BYTE) NOT NULL ENABLE,
    "CFG_VALUE" VARCHAR2(20 BYTE) NOT NULL ENABLE,
     CONSTRAINT "VBA_FIELDS_PK" PRIMARY KEY ("TABLE_NAME", "COLUMN_ID", "CFG_NAME")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "GSM_OWNER_TS"  ENABLE
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "GSM_OWNER_TS" ;

The problem is that we know that the join will produce only 1 row (and no more) in the view for every row in MY_CFG_TABLE but Oracle doesn't. As described in OraFAQ: Updatable Join Views (emphasis mine):

  Clearly the view cannot contain DISTINCT, GROUP BY, CONNECT BY, aggregate functions, UNION, INTERSECT, or MINUS clauses: simple joins are all that is allowed. There is one other restriction: the view must be key preserved. This means that the join must meet the following restrictions:

  The join criteria must be equals (=) joins.
  Tables other than the table that is to be updated must be joined on a primary or unique key.

  If these conditions are violated, then Oracle cannot guarantee that the view will return one row only for each row in the base table. If two or more rows in the secondary table mapped to each row in the base table, then the update would be ambiguous. An attempt to update a non-key-preserved view will result in an Oracle error.

I assume that either the joined table (which is actually a system view) does not have a UNIQUE or PRIMARY KEY constraint or one of the underlying tables (when the whole view is expanded) is not joined on its unique/primary key. This might be causing the errors when we try to update the view.

The problem may be more complicated as we don't know the exact definition of the underlying ALL_TAB_COLUMNS view. It might have some non-equality joins or non-inner joins or unions that make the new view non-updatable.


SQL Server parallelism issue I am rather new to SQL Server administrations, I am running an SSIS that never ends...

A) looking at the processes using

SELECT r.*, t.text
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.status IN (N'Suspended', N'Running', N'Runnable', N'Pending')

I see the session is SUSPENDED

B) looking at the actual execution plan using

SELECT plan_handle
FROM sys.dm_exec_requests
WHERE session_id = 55

SELECT query_plan
FROM sys.dm_exec_query_plan (0x06000A00FB4E0A2240615FAE010000000000000000000000)

I see 95% of time spent on parallelism

How can I solve this issue?

Thanks in advance,
Assaf

Suspended status doesn't tell you much; the wait type tells you the story. I would;

Download and Install sp_whoIsActive execute the below command while the processes are suspended to get the leading blocker, it's wait type along the lock graph. This should show the lock areas. (please upload)

exec sp_WhoIsActive
  @show_own_spid=1,
  @show_sleeping_spids=1,
  @get_task_info =2,
  @get_locks=1,
  @get_additional_info=1,
  @find_block_leaders=1,
  @format_output = 0

Setup a SQL blocking report trace. Here is the link with scripts to set up the blocked report SQL trace. Set the blocked process threshold to 20 secs or higher

Please upload these results for review


Cannot connect to 19c with SQL Developer though database is mounted and opened, error ORA-01034 I have recently had a server crash that is hosting both an 11g and 19c databases.  Upon restart by IT staff, I discovered that one of the two 19c instances was idle. The 11g instances are all mounted, open and accepting SQL Developer connections.I was able to mount and open it using the init.ora file as an argument in the startup. I checked and the database is reporting, through SQL Plus, that it is mounted and open.  The listener has been restarted successfully.  However, trying to connect to it through SQL Developer I get ORA-01034. Any advice on what the problem is would be appreciated.
Error: ORA-01034: Oracle not available
ORA-27101: shared memory realm does not exist
01034, 0000 "Oracle not available"
SQL Developer is local and I have not tried remote sql plus as I am remoting into the Win 2012 R2 host server to run the commands. There is one listener for the database. Stopped and restarted listener and reports STATUS UNKNOWN for both instances.
Have not done anything with the environment variables. I do not have admin rights to the server.

You have to restart your DB instances while listener's are up and don't restart the listener after that. When DB's are started: try to connect to them locally (just with username/password) and locally through tnsnames (username/password@tnsname) If listeners are shown correct status and you are able to connect locally with tnsnames: make sure the sam tnsnames file is on your client and there is no firewall between DB and client.


Schema changes on sharded database I have performance issues with one particulary large table, 500+ Million rows, 300Gb data, Postgres 10.5.
It is already partitioned. I am working on optimising it here and there, but that is not trivial and only provides small improvements.
Table is constanlty growing and we expect our userbase increace significantly so I need a way to scale up.

I want to use multi-tenant sharding approach. X tenants per shard. Shard resolving on app layer.
Most of tenants have relateively small datasets, but few are huge and I want to be able to place them to separate shards . To do that I need lookup tables.
Cross-shard queries are not concern at all, naturaly we have almost all of our queris per tenant, so all the data for the tenant will sit in same shard.

I will be using logical sharding, 4 phisical shards x32 logical (that is twice more shards than partitions currenly).
Each logical shard is separate database. In most tutorials/talks people seem to use schemas instead of databases. Why? Databases are more isolated, and when moving single tenant or virtual shard to other location it does not seem to have any difference. So db looks like a better candidate to me.

Drawbacks look acceptable: update existing code (significantly), app should be shard aware

The question is: How do I handle migrations(schema changes)?

As first step I will have to create 128 databases, ensuring all of them have all tables, indexes, etc. I also want each of dbs have its own sequences to have ids unique accross all shards. Not trivial to me.

But further changes are problem aswell. Do I just iterate all connections and aplly changes? Is there a better (maybe async) way? What do I do if at some point shema in one shard is different from another.

For the side question of sequences, you could do that like this:

For the 42nd shard, use

CREATE SEQUENCE my_seq START WITH 43 INCREMENT BY 1000;

Since all shards have different starting values, the sequence values cannot collide, and you have room for adding more shards.

To consistently change schemas across all shards, use a distributed transaction. Run the following on each shard:

BEGIN;
ALTER TABLE ... ;
/* other schema changes */
PREPARE TRANSACTION 'some_name';

Once this has completed on all shards, execute the following on each shard:

COMMIT PREPARED 'some_name';

If the PREPARE TRANSACTION fails on some database, undo the transaction on each shard with

ROLLBACK PREPARED 'some_name';

This will only work well if your database transactions are all short, because otherwise processing will hang for a long time. It is also beneficial to run the changesbon all shards in parallel, so that processing is not blocked for longer than necessary.

All this assumes that you are not running any other distributed transactions, because otherwise these distributed transactions can easily deadlock with your schema modification in a way that PostgreSQL cannot resolve automatically.


MySql - changing innodb_file_per_table for a live db I have a large MySql DB (150GB) and only now I've noticed that the innodb_file_per_table is set to off which causes the entire DB to be hosted on one single file (ibdata1). I want to activate innodb_file_per_table and have it retroactively split the DB into several files, what's the best way to do this?

There is really only one way to pull this off. You will have to export the data using mysqldumps, drop all databases, shutdown mysqld, delete ib_logfile0, delete ib_logfile1, delete ibdata1, add innodb_file_per_table under the [mysqld] heading, start mysql.

I posted this answer in StackOverflow back in October 2010

Here are the steps listed vertically:

Step 01) MySQLDump all databases into a SQL text file (call it SQLData.sql)

Step 02) Drop all databases (except mysql schema)

Step 03) Shutdown mysql

CAVEAT: To totally clean out uncommitted transactions from the InnoDB Files, run this

mysql -uroot -p... -Ae"SET GLOBAL innodb_fast_shutdown = 0;"
service mysql stop

Step 04) Add the following lines to /etc/my.cnf

[mysqld]
innodb_file_per_table
innodb_flush_method=O_DIRECT
innodb_log_file_size=1G
innodb_buffer_pool_size=4G

Sidenote: Whatever your set for innodb_buffer_pool_size, make sure innodb_log_file_size is 25% of innodb_buffer_pool_size.

Step 05) Delete ibdata1, ib_logfile0 and ib_logfile1

At this point, there should only be the mysql schema in /var/lib/mysql

Step 06) Restart mysql

This will recreate ibdata1 at 10MB, ib_logfile0 and ib_logfile1 at 1G each

Step 07) Reload SQLData.sql into mysql

ibdata1 will grow but only contain table metadata

Each InnoDB table will exist outside of ibdata1

Suppose you have an InnoDB table named mydb.mytable. If you go into /var/lib/mysql/mydb, you will see two files representing the table

mytable.frm (Storage Engine Header)
mytable.ibd (Home of Table Data and Table Indexes for mydb.mytable)

ibdata1 will never contain InnoDB data and Indexes anymore.

With the innodb_file_per_table option in /etc/my.cnf, you can run OPTIMIZE TABLE mydb.mytable and the file /var/lib/mysql/mydb/mytable.ibd will actually shrink.

I have done this many times in my career as a MySQL DBA

In fact, the first time I did this, I collapsed a 50GB ibdata1 file into 500MB.

Give it a try. If you have further questions on this, email me. Trust me. This will work in the short term and over the long haul. !!!

There is an alternative that will extract the InnoDB table without shrinking ibdata1.

Step 01) Add the following lines to /etc/my.cnf

[mysqld]
innodb_file_per_table
innodb_flush_method=O_DIRECT
innodb_log_file_size=1G
innodb_buffer_pool_size=4G

Step 02) service mysql restart

Step 03) To extract a single InnoDB table called mydb.mytable, do this:

ALTER TABLE mydb.mytable ENGINE=InnoDB;

This will create one file pleus keep the original structure file

/var/lib/mysql/mydb/mytable.frm
/var/lib/mysql/mydb/mytable.ibd

You can do this for every InnoDB table. Unfortunately, ibdata1 will stay 150GB.


Any security related reasons to prefer Windows Authentication over Sql Server Authentication? This is a rather short question:

When using Microsoft SQL Server (version 2005 and newer), are there any security related reasons to prefer Windows Authentication over SQL Server Authentication?

Just to point it out, I'm interested in security related concerns, not in administrative or any other differences between the two.

Update: If any difference leads to (or is) a security concern, then I'm surely interested in.

An example, separation of duties

On a web server, the password for the service account is only known by one team. It isn't required to lie around in web.config or source control.

One team sets up the IIS App Pool (password is known here)
One team deploys code
One team manages the SQL Server Instance

If you want to pass an audit where PCI or SoX is involved, then you need to have this


Database under heavier load -- all SQL queries start timing out after a period of hours/days I make a web application which has experienced heavy growth in the past week and it appears my database is seizing up because of it.

You can see that everything seemed to be OK until about 3AM this morning and then all of my requests started timing out. This isn't the first time this has happened, but after I push new changes to the server it starts up OK again. It doesn't seem to happen under low load, but has also ran at high load for hours perfectly fine. I don't think 3AM is an especially high load time.

Things I have done / to note:

Dedicated server, MSSQL and I'm using NHibernate as an ORM to generate my queries.
The server is pretty much just a data store. 99% of the queries are very basic insert/selects. I have one bulk insert for creating a large amount of playlist items.
I have batching enabled. It is set to 50 by default and 200 if >1000 playlist items are about to be  written to the database. This reduced the spikes in my DB's response times.
I have my command timeout set to 5 seconds. It was set at the default, 30 seconds, and I was still experiencing the issue. The problem seems to have stayed away for a longer period of time after setting it to 5 seconds.
I have tried running exec sp_updatestats as described here: https://stackoverflow.com/questions/8602395/timeout-expired-the-timeout-period-elapsed-prior-to-completion-of-the-operation# which did show some indexes being updated, but did not result in any real differences.
I have set my databases connection isolation level to ReadUncommitted: configuration.SetProperty("connection.isolation", "ReadUncommitted");
I specify the default DB for NHibernate performance as described here: configuration.SetProperty("default_schema", "[db896d0fe754cd4f46b3d0a2c301552bd6].[dbo]");

What on earth should I be looking at to try and get a grip on what is causing my database to lockup? It's showing only 1% CPU usage so I don't think it is overly taxed. It shouldn't be a long running query if I have 5 second caps on everything. It's not the indexes. I'm running out of ideas.

In terms of queries -- it's all SELECT user statements. It's just a bunch of people trying to login and access their account. I'm getting about 1000 requests / 30 minutes for SELECT user.

Here's some more info:

@@VERSION:

  Microsoft SQL Server 2008 R2 (SP2) - 10.50.4270.0 (X64)   Nov 30 2012
  17:11:43   Copyright (c) Microsoft Corporation  Web Edition (64-bit)
  on Windows NT 6.1  (Build 7601: Service Pack 1) (Hypervisor)

I was setting indices on my PKs, but I did not have indices on my FKs. Most of my queries were of the form: "Select Playlists p where p.UserId = 'XXX'"

Even though I had an index on User's ID, I did not have one on Playlist's UserID. This was root cause.


SQL Server Transactional Replication With subscriber trigger returning a value I have transaction replication setup on on SQL server 2008 R2. The subscriber has a after trigger on the published article that inserts into another table on the subscriber's database and returns the new value created in the trigger (the column is a persisted computed column). However, it seems we are not getting the new value back from the subscriber's trigger on the publisher after the insert occurs.

Is SQL server able to return the value from the trigger or because replication is transnational, which is just one way, it cannot?

UPDATE (2018-02-09 many many years later)

So hopefully I can better explain this than my past self:

A publisher, on a different SQL Server than the subscriber, publishes a table called dbo.Inventory. The subscriber subscribes to this publication and now has dbo.Inventory on its server. We add an AFTER INSERT trigger on the published table, dbo.Inventory, on the subscriber's SQL Server:

CREATE TRIGGER [dbo].[aiInsertNewPk]
  ON  [dbo].[Inventory] -- remember this trigger is on the subscriber
  AFTER INSERT
AS
BEGIN

SET NOCOUNT ON;

DECLARE @Serial TABLE (ColumnA CHAR(10));

INSERT INTO [Subscriber].[DifferentTable] ( <...columns> )
OUTPUT inserted.[Foobar] INTO @Serial(ColumnA)
SELECT <...columns>
FROM inserted
WHERE <logic>

-- output this value so c# application can grab it
SELECT ColumnA FROM @Serial

END

Our problem some years ago, was that trigger was not outputting the values from ColumnA (the last line of the trigger), so our C# application was always getting a null value back.

Not sure if that helps and honestly I have not revisited this issue to see if the problem was something else entirely. I just thought I would add this update since you were kind enough to comment many years later.

this isn't so much an answer as a workaround. I am saving the value generated in the trigger on a table on the subscriber with the primary key of the transaction from the publisher. Then the application is querying that table on the subscriber immediately after the insert.


dbms_datapump API exclude statistics during import We are using dbms_datapump to copy data between different servers or to setup new schemas quickly.

However importing statistics usually takes ages and has the additional disadvantage that schema statistics are locked after the import.

So I'm trying to find a way to skip importing the statistics.

According to Metalink Doc ID 1392981.1 this should be possible using:

dbms_datapump.metadata_filter(handle => l_job_handle,
      name => 'EXCLUDE_PATH_LIST',
      value => '''STATISTICS''');

However when I try that, I get an "ORA-39001: invalid argument value" error.

I also tried various other formats found in different places:

dbms_datapump.metadata_filter(handle => l_job_handle,
      name => 'EXCLUDE_PATH_LIST',
      value => 'STATISTICS');

dbms_datapump.metadata_filter(handle => l_job_handle,
       name => 'EXCLUDE_PATH_EXPR',
       value => 'like ''%/TABLE/STATISTICS/TABLE_STATISTICS''');

dbms_datapump.metadata_filter(handle => l_job_handle,
       name => 'EXCLUDE_PATH_EXPR',
       value => 'like ''%STATISTICS%''');

But all of them return with an ORA-39001.

The version I'm using is:

  Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

Operating system is a Windows Server, but this also occurs on a Linux installation.

Any ideas how I can skip the statistics during import (or export) through the DBMS_DATAPUMP API?

You need to use:

DBMS_DATAPUMP.METADATA_FILTER(l_job_handle,
                              'EXCLUDE_PATH_EXPR',
                              'IN (''STATISTICS'')');


Cannot create Federated View For the first time ever could not find a solution on internet!
The problem is when I'm trying to (on local server):

CREATE TABLE `DB_1`.`testView`
ENGINE=FEDERATED CONNECTION='remote_server';

I'm getting an error:

  #1939 - Engine FEDERATED failed to discover table `DB_1`.`testView` with 'CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `testView` AS select 'test_line' AS `test_line` CONNECTION='remote_server''

Obviously you can see that VIEW is very simple for testing purposes (executed on remote server):

 CREATE VIEW `DB_1`.`testView` AS SELECT "test_line"

The user in remote_server has all privileges. View was tested with different options and content. Federated tables work fine. Server version on both machines: 10.0.29-MariaDB-0+deb8u1

Am I missing something?

You are using FederatedX engine and since you are not specifying column definitions explicitly, it is trying to perform assisted discovery to retrieve the structure of the table. It cannot do it with the underlying "table" being a view, so you are getting the error.

Instead, try to specify the columns:

-- remote
CREATE VIEW `DB_1`.`testView` AS SELECT "test_line"

-- local
CREATE TABLE `DB_1`.`testView` (test_line varchar(16))
ENGINE=FEDERATED CONNECTION='remote_server';


Unable to get Cassandra in a Docker to talk to cluster Trying to get Cassandra running in a docker using the official docker image. When I execute the below lines of code it starts then stops the docker without connecting to the cluster. I have checked the cassandra.yaml file and it looks just like the other nodes on my cluster. Same for the cassandra-rackdc.properties file. The docker can ping all the other computers.

I run the following and a few derivations:

docker run --name cassandra -i \
-v /media/mcamp/HDD/Docker/CampgroundContainer1:/var/lib/cassandra \
-e CASSANDRA_SEEDS="192.168.0.114, 192.168.0.101, 192.168.0.106" \
-e CASSANDRA_CLUSTER_NAME=CampCluster \
-e CASSANDRA_DC=campground-wireless \
-e CASSANDRA_RACK=Docker1 \
-e CASSANDRA_ENDPOINT_SNITCH=GossipingPropertyFileSnitch \
-e CASSANDRA_START_RPC=true \
-e CASSANDRA_LISTEN_ADDRESS=172.17.0.2 \
-p 7000:7000 \
 cassandra:latest

I get the following error:

INFO  [ScheduledTasks:1] 2017-11-10 03:45:39,428 TokenMetadata.java:498 - Updating topology for all endpoints that have changed
Exception (java.lang.RuntimeException) encountered during startup: Unable to gossip with any seeds
java.lang.RuntimeException: Unable to gossip with any seeds
    at org.apache.cassandra.gms.Gossiper.doShadowRound(Gossiper.java:1415)
    at org.apache.cassandra.service.StorageService.checkForEndpointCollision(StorageService.java:550)
    at org.apache.cassandra.service.StorageService.prepareToJoin(StorageService.java:801)
    at org.apache.cassandra.service.StorageService.initServer(StorageService.java:666)
    at org.apache.cassandra.service.StorageService.initServer(StorageService.java:612)
    at org.apache.cassandra.service.CassandraDaemon.setup(CassandraDaemon.java:393)
    at org.apache.cassandra.service.CassandraDaemon.activate(CassandraDaemon.java:600)
    at org.apache.cassandra.service.CassandraDaemon.main(CassandraDaemon.java:689)
ERROR [main] 2017-11-10 03:46:08,466 CassandraDaemon.java:706 - Exception encountered during startup
java.lang.RuntimeException: Unable to gossip with any seeds
    at org.apache.cassandra.gms.Gossiper.doShadowRound(Gossiper.java:1415) ~[apache-cassandra-3.11.1.jar:3.11.1]
    at org.apache.cassandra.service.StorageService.checkForEndpointCollision(StorageService.java:550) ~[apache-cassandra-3.11.1.jar:3.11.1]
    at org.apache.cassandra.service.StorageService.prepareToJoin(StorageService.java:801) ~[apache-cassandra-3.11.1.jar:3.11.1]
    at org.apache.cassandra.service.StorageService.initServer(StorageService.java:666) ~[apache-cassandra-3.11.1.jar:3.11.1]
    at org.apache.cassandra.service.StorageService.initServer(StorageService.java:612) ~[apache-cassandra-3.11.1.jar:3.11.1]
    at org.apache.cassandra.service.CassandraDaemon.setup(CassandraDaemon.java:393) [apache-cassandra-3.11.1.jar:3.11.1]
    at org.apache.cassandra.service.CassandraDaemon.activate(CassandraDaemon.java:600) [apache-cassandra-3.11.1.jar:3.11.1]
    at org.apache.cassandra.service.CassandraDaemon.main(CassandraDaemon.java:689) [apache-cassandra-3.11.1.jar:3.11.1]
INFO  [StorageServiceShutdownHook] 2017-11-10 03:46:08,469 HintsService.java:220 - Paused hints dispatch
WARN  [StorageServiceShutdownHook] 2017-11-10 03:46:08,469 Gossiper.java:1540 - No local state, state is in silent shutdown, or node hasn't joined, not announcing shutdown
INFO  [StorageServiceShutdownHook] 2017-11-10 03:46:08,469 MessagingService.java:984 - Waiting for messaging service to quiesce
INFO  [ACCEPT-/172.17.0.2] 2017-11-10 03:46:08,470 MessagingService.java:1338 - MessagingService has terminated the accept() thread
INFO  [StorageServiceShutdownHook] 2017-11-10 03:46:09,653 HintsService.java:220 - Paused hints dispatch

I think that the problem could be that you don't set the CASSANDRA_BROADCAST_ADDRESS parameter that is used by Cassandra when it talks with other nodes. By default it sets it to value of CASSANDRA_LISTEN_ADDRESS, but in  your case this address is address of Docker network, so other nodes won't able to reach your Docker instance.

You need to set CASSANDRA_BROADCAST_ADDRESS to IP address of your machine that hosts the Docker.


How do I perform a UNION query on a dynamic list of tables? My database has an unknown amount of tables, named with the same prefix followed by a random string: _ct_<random_string>.

Each of these tables has the same data structure.

I also have another table in which I store the name of each table for future reference.

The following table name is 'content_types' and provides reference for other tables:

+-----------------+
| id | table_name |
+-----------------+
| 1  | _ct_table1 |
| 2  | _ct_table2 |
| 3  | _ct_table3 |
| 4  | _ct_fdfsf  |
| 5  | _ct_535dgf |
...
+-----------------+

The following are example to the referenced tables in 'content_types'.'table_name':

_ct_table1:                              _ct_table2:
+-----------------------------------+    +-----------------------------------+
| id | title | subtitle | date      |    | id | title | subtitle | date      |
+-----------------------------------+    +-----------------------------------+
| 1  | fddfs | ...      | 2012-08-22|    | 4  | fddfs | ...      | 2012-03-18|
| 2  | bdsf  |          | 2012-08-22|    | 5  | b4sf  | dfs      | 2012-07-20|
| 3  | sfdsd |          | 2012-08-22|    | 6  | sfdsd |          | 2012-03-12|
+-----------------------------------+    +-----------------------------------+

Is it possible to create a query with a dynamic UNION statement based on the tables listed in 'content_types'.'table_name'?

Something like this:

select id,title,subtitle,date
from
    (
        select table_name
        from content_types
    );

Yes you can. There are several ways to do this.

1> If you are using the database interactively, then just type in the query.
2> If the database is being accessed via a application or script, write a method in the application/script to create the query required and then run it. You would have a database call to get the list of table names, then create a string with the union query then execute that query.
3> In a similar vein to option 2, most stored procedure languages allow you to dynamically create a query as a string and then execute that string - Check out your DB's documentation to find out how to do this.

A Mysql example https://stackoverflow.com/questions/190776/how-to-have-dynamic-sql-in-mysql-stored-procedure. You may like to use a cursor ( http://dev.mysql.com/doc/refman/5.0/en/cursors.html ) to iterate though the list of tables names to Concat ( http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_concat ) together the query string.


postgres index for "?|" JSONB operator I have a jsonb row with objects/dictionairies : {"a":1, "b":2, "c",3}

I want to query rows that contain a set of keys, ex:

SELECT * from my_table v
WHERE v.my_jsonb_column::jsonb ?| array['b','a']

how can I create an index for this query ?

This index does it :

CREATE INDEX my_idx on my_table USING GIN (my_jsonb_column);


Watch "Alter Table" statement changes in Mysql database I have tried to find any way to track changes in tables where the ALTER TABLE statement was used.

It is known that the trigger event can be INSERT, UPDATE or DELETE  according to ==> Mysql triggers

Anyway I tried to use this query to enable trigger for ALTER TABLE ==> CREATE TRIGGER my_trigger ALTER TABLE ON my table ... but it did not work.

Is there any way to watch changes in a mysql table if it was changed by using ALTER TABLE statement?

Just some ideas:

Set a trigger on INFORMATION_SCHEMA

Not possible, according to docs:

  The INFORMATION_SCHEMA database contains several read-only tables. They are actually views, not base tables, so there are no files associated with them, and you cannot set triggers on them. Also, there is no database directory with that name.

Monitor the DDL log

I wouldn't go down that road (docs):

  The DDL log, or metadata log, records metadata operations generated by data definition statements such as DROP TABLE and ALTER TABLE. [...] This is a binary file; it is not intended to be human-readable...

Use events and track changes yourself

You could keep a copy of the relevant INFORMATION_SCHEMA tables, schedule a recurring event and check for any difference between your copy and the current state.

You would need a very strong use case to justify the effort to develop this, I think.

Track the binary log or general log

If you just want to get a quick heads up, when the schema has changed, you might try to set up a cron job and enable either (a) binary logging to use mysqlbinlog to check for ALTER TABLE statements or (b) to enable the general log and do the same.
You could then send yourself an email, when that happens.

Might be more or less reliable than trusting the developers to notify you - depending on your implementation and on the developers ;)


Creating index for specific values only We have a query that is called very frequently, so I would like to improve performance, but some colleagues are concerned about the index's size and its impact on inserts an updates.

SELECT field1, field2
  FROM table1
  WHERE field3 = '101'
  AND   field2 = @variable

Table table1 has about 5 million rows, but only about 2000 has field3 = 101.

 CREATE TABLE table1 (
   field1 varchar
   field2 int
   field3 int);

Would it be possible to create and index for (field2, field1) only for these 2000 rows in oracle  11g?
How can I do it?

Database SQL Language Reference - CREATE INDEX

  "Oracle does not index table rows in which all key columns are null
  except in the case of bitmap indexes."

Demo

create table t as select level as field3 from dual connect by level <= 1000000;
alter table t add field3_ind as (case when field3 = 101 then field3 end);
create index t_ix_field3_ind on t (field3_ind);

select * from t where field3_ind = 101;

select    segment_name
         ,segment_type
         ,bytes

from      dba_segments

where     owner        =  'DMARKOVITZ'
      and segment_name in ('T','T_IX_FIELD3_IND')

order by  segment_name
;

+-----------------+--------------+----------+
| SEGMENT_NAME    | SEGMENT_TYPE | BYTES    |
+-----------------+--------------+----------+
| T               | TABLE        | 13631488 |
+-----------------+--------------+----------+
| T_IX_FIELD3_IND | INDEX        | 65536    |
+-----------------+--------------+----------+

P.s.
The use of virtual column makes the querying more user friendly.

If the index would have been defined as -

create index t_ix_field3 on t (case when field3 = 101 then field3 end);

then in order to use the index, the query should have looked like -

select * from t where case when field3 = 101 then field3 end = 101;


Backup Compression SQL Server vs PURE We are having a SQL 2016 Standard Edition Server holding 4TB of data.  And the compressed backup using maintenance plans is taking around 1.5 hrs. Our storage team claims that as PURE FlashArray is used, SQL compression is not required as PURE compresses the backup. PURE documentation here - https://support.purestorage.com/Solutions/Microsoft_Platform_Guide/Microsoft_SQL_Server/001_Microsoft_SQL_Server_Quick_Reference  - talks about uncompressed backups and de-dupe. Brent's article about de-dupe - https://www.brentozar.com/archive/2009/11/why-dedupe-is-a-bad-idea-for-sql-server-backups/.

What is the best practice SQL compression or Uncompressed backups on PURE. How do i measure it, as the stats are more powerful than statements.

There is not really a recommended choice for compressed versus uncompressed when the storage subsystem has compression/de-dupe features as there are too many variables, generally though I always enable compression even with storage subsystem compression.

You need to determine which is best for your particular environment and implement that. There are several areas you can check for comparison to validate which is the best choice.

Query speed - Check the overall duration of backups with and without compression. Also, ask your storage team to disable de-dupe and compression at their end for your backup disks (if possible) and run a SQL compressed and non-compressed backup and measure the speed of those backups as well.
Query throughput - Related to the overall duration, record the throughput in MB/s for compressed and uncompressed backups. Again, if you can run these tests with and without storage level compression that gives you more data to validate your choice.
Compression ratio - You want to see who is getting the best compression ratio - if SQL is saving you more space than your storage subsystem for little to no cost in time then it may be the choice, or if it is only marginally larger in size but much quicker, again this may be an acceptable trade-off.
Overall size - This is similar to compression ratio, but requires you to calculate the storage requirements taking into consideration your retention periods. If you need to keep daily FULL backups for 10 years, how much total storage is required, and how much space does the storage layer de-dupe save in that scenario? To answer these questions, you need the compression statistics from both SQL Server and the storage layer.

The below query will get statistics from your backup history to help you collate the data from a SQL Server perspective, and you need to get your storage team to extract their own statistics to show size, compression, throughput etc for comparison.

You should also run these tests on a non-Production SQL Server where you can change your configurations without impacting production.

Script:

WITH BackupDetails AS (
    SELECT
        bs.[database_name] AS [DatabaseName],
        CASE
            WHEN bs.[type] = 'D' THEN 'Full'
            WHEN bs.[type] = 'I' THEN 'Differential'
            WHEN bs.[type] = 'L' THEN 'Log'
            WHEN bs.[type] IS NULL THEN 'No Backup'
        END AS [BackupType],
        COALESCE(CONVERT(VARCHAR(20), bs.[backup_start_date], 120),'-') AS [BackupStartDate],
        CASE
            WHEN DATEDIFF(SECOND, bs.[backup_start_date], bs.[backup_finish_date]) < 1 THEN 1
            ELSE DATEDIFF(SECOND, bs.[backup_start_date], bs.[backup_finish_date])
        END AS Duration_S,
        bs.[user_name] AS [BackupCreator],
        CAST(((bs.[backup_size])* 0.00000095367432) AS DECIMAL(15,2)) AS [BackupSize],
        CAST(((bs.[compressed_backup_size])* 0.00000095367432) AS DECIMAL(15,2)) AS [CompressedBackupSize],
        bmf.physical_device_name AS [File]
    FROM
        msdb..backupset bs
        INNER JOIN msdb..backupmediafamily bmf ON bmf.media_set_id = bs.media_set_id
)

SELECT [DatabaseName],
    [BackupType],
    [BackupStartDate],
    [BackupSize] AS [BackupSize(MB)] ,
    [CompressedBackupSize] AS [CompressedBackupSize(MB)],
    CAST(CAST(ROUND([BackupSize]/[CompressedBackupSize], 2, 1) AS DECIMAL(15,2)) AS VARCHAR(25)) + ' : 1' AS [CompressionRatio],
    [Duration_S],
    CAST([CompressedBackupSize]/[Duration_S] AS DECIMAL(15,2)) AS [Throughput (MB/s)],
    [File]
FROM BackupDetails
WHERE [BackupType] = 'Full'
ORDER BY
    [DatabaseName], [BackupStartDate] DESC


Best way to evenly distribute data over partitioned tables I am working with a database with roughly around 500MM records. We currently use a static partitioning method that partitions the data by an int identifier such at < 150MM goes on partition 1, > 150 && < 350 goes on 2 etc.

What dynamic partitioning schemes are available to better evenly distribute the data over the available partitions? Is there a way to allow for additional partitions to a dynamic scheme with minimal maintenance overhead?

Hashing is a common partitioning mechanism for your use case..  It is NOT available in SQL 2008: http://www.sqlservercentral.com/articles/partition/64740/.

The problem with hash partitioning is that if you add partitions, you need to redistribute the data.  Logically speaking, any partitioning scheme that dynamically determines which partition the data goes into the data would need to redistribute the data after you add partitions (other than perhaps using a hash on the insert time + keeping track of when each partition was added).  This assumes partitioning (distributing data via a predefined scheme to reduce subsequent lookup time) vs sharding (randomly distributing data to multiple boxes reduce processing time of the entire data set).

You can probably simulate a hash partition by generating the hash yourself on insert/update, and using a range based partition over the hashed value.  For instance, lets say you want 10 partitions.  Modulus divide a hash of your key value by 10, and range partition on the remainder (with each range taking one of the 10 possible values).


How do I add extra columns to crosstab()? I am using PostgreSQL 9.6 and have following schema

CREATE TABLE activitylog (
    sessionid text NOT NULL,
    studentid integer NOT NULL,
    activity text NOT NULL,
    created timestamp without time zone DEFAULT now() NOT NULL
);

The schema is used to log visitors activity. Each visit is assigned a session id which is unique for the visit. Sessions ends when user logs out. Every user can log in and log out multiple times.

INSERT INTO activitylog VALUES
  ( 'abc', 1, 'logged in'   , '2017-08-12T02:39:05.753088Z' ),
  ( 'abc', 1, 'page visited', '2017-08-12T02:49:05.753088Z' ),
  ( 'abc', 1, 'logged out'  , '2017-08-12T02:59:05.753088Z' ),
  ( 'def', 1, 'logged in'   , '2017-08-12T03:09:05.753088Z' ),
  ( 'def', 1, 'page visited', '2017-08-12T03:19:05.753088Z' ),
  ( 'def', 1, 'logged out'  , '2017-08-12T03:29:05.753088Z' ),
  ( 'ghi', 2, 'logged in'   , '2017-08-12T03:39:05.753088Z' ),
  ( 'ghi', 2, 'page visited', '2017-08-12T03:59:05.753088Z' ),
  ( 'ghi', 2, 'logged out'  , '2017-08-12T03:59:05.753088Z' );

Required Result

I want to select data from the table such that I get result in following format

|  sessionid  |  studentid  |          loggedin           |          loggedout          |      interval      |
| ----------- | ----------- |       --------------        |         -----------         | ------------------ |
|     abc     |      1      | 2017-08-12T02:39:05.753088Z | 2017-08-12T02:59:05.753088Z |       20min        |
|     def     |      1      | 2017-08-12T03:09:05.753088Z | 2017-08-12T03:29:05.753088Z |       20min        |
|     ghi     |      2      | 2017-08-12T03:39:05.753088Z | 2017-08-12T03:59:05.753088Z |       20min        |

Basically,

Pivot table
Add studentid column
Calculate interval

What I have tried so far

I have successfuly piovoted the table

SELECT * FROM crosstab(
$$
select sessionid, activity, created from activitylog
where activity = 'logged in' OR activity = 'logged out'
ORDER  BY 1, 2
$$
)
AS sessioninfo (sessionid text, loggedin timestamp, loggedout   timestamp);

I tried to add studentid extra column using this query but it didn't give me desired result

SELECT * FROM crosstab(
$$
select sessionid, studentid, activity, created from
activitylog
where activity = 'logged in' OR activity = 'logged out'
ORDER  BY 1, 2
$$,
$$
VALUES (1), (0)
$$
)
AS sessioninfo (sessionid text, studentid INTEGER,
loggedin timestamp, loggedout   timestamp);

But this places null in logged in and logged out timestamps.

To sum it up

What is the purpose of VALUES with crosstab()? To be honest I am not completely sure what purpose second parameter of crosstab() serves and why in almost all examples it is VALUES.
How do I add studentid to result?
How do I calculate interval between two columns that are result of crosstab()?

What you want is something like this,

SELECT *
FROM crosstab(
        $$
                SELECT sessionid, studentid, activity, created
                FROM activitylog
                WHERE activity IN ('logged in', 'logged out')
                ORDER BY 1, 2
        $$,
        $$
                VALUES ('logged in'), ('logged out')
        $$
) AS sessioninfo (
        sessionid text,
        studentid int,
        loggedin timestamp,
        loggedout timestamp
)
CROSS JOIN LATERAL age(loggedout, loggedin);

Output:

 sessionid | studentid |          loggedin          |         loggedout          |   age
-----------+-----------+----------------------------+----------------------------+----------
 abc       |         1 | 2017-08-12 02:39:05.753088 | 2017-08-12 02:59:05.753088 | 00:20:00
 def       |         1 | 2017-08-12 03:09:05.753088 | 2017-08-12 03:29:05.753088 | 00:20:00
 ghi       |         2 | 2017-08-12 03:39:05.753088 | 2017-08-12 03:59:05.753088 | 00:20:00
(3 rows)

  What is the purpose of VALUES with crosstab()? To be honest I am not completely sure what purpose second parameter of crosstab() serves and why in almost all examples it is VALUES.

It specifies the categories. It can also be a query but here there is no need for that and being explicit was even more terse. You're interested in two categories those are loggedin, and loggedout. Age being a function of those two categories is calculated with CROSS JOIN LATERAL.

VALUES itself is a SQL expression. We could have written SELECT * FROM VALUES () AS t(category) but that's just more work.

  How do I add studentid to result?

Just like in my demonstration.

  How do I calculate interval between two columns that are result of crosstab()?

We used age. However one caveat about this we should hit right now..

Your schema is specified as timestamp without time zone. That's certainly a mistake. When that crosses DST, if ever, you're going to be in a world of pain. It'll show the time elapsed in the rollover.


How do I deal with FK constraints when importing data using DTS Import/Export Wizard? I am trying to use the SQL Server Import and Export Wizard to copy data from my production db to my dev db but when I do it fails with the error "The INSERT statment conflicted with the FOREIGN KEY constraint" i have over 40 tables with lots of FK constraints, is there some easy way to deal with this without having to write a drop constraint/add constrat script?

Edit:
 I just found out that in Web edition of SQL Server, which is what I am running, DTS will not let you save packages.

I was given this solution over at SQLTeam.com:

  Use:

 EXEC sp_msforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT all'

  Then import your data

EXEC sp_msforeachtable 'ALTER TABLE ? CHECK CONSTRAINT all'

Using that method I was able to import all the data in with no issues.


Should I nest dependent outer joins in SQL Server? I have heard mixed information on this and am hoping for a canonical or expert opinion.

If I have multiple LEFT OUTER JOINs , each dependent on the last, is it better to nest them?

For a contrived example, the JOIN to MyParent depends on the JOIN to MyChild:
http://sqlfiddle.com/#!3/31022/5

SELECT
    {columns}
FROM
    MyGrandChild AS gc
LEFT OUTER JOIN
    MyChild AS c
        ON c.[Id] = gc.[ParentId]
LEFT OUTER JOIN
    MyParent AS p
        ON p.[id] = c.[ParentId]

Compared to http://sqlfiddle.com/#!3/31022/7

SELECT
    {columns}
FROM
    MyGrandChild AS gc
LEFT OUTER JOIN
    (
    MyChild AS c
    LEFT OUTER JOIN
        MyParent AS p
            ON p.[id] = c.[ParentId]
    )
    ON c.[Id] = gc.[ParentId]

As shown above these produce different query plans in SS2k8

This is absolutely not a canonical answer but I noticed that for the nested loops query plans shown in the SQL Fiddle it was possible to apply the plan from Query 2 to Query 1 with the use of the USE PLAN hint but attempting the reverse operation fails with

  Query processor could not produce query plan because USE PLAN hint
  contains plan that could not be verified to be legal for query. Remove
  or replace USE PLAN hint. For best likelihood of successful plan
  forcing, verify that the plan provided in the USE PLAN hint is one
  generated automatically by SQL Server for the same query.

Disabling the optimizer transformation rule ReorderLOJN prevents the previously successful plan hint from succeeding too.

Experimenting with greater quantities of data shows that SQL Server is certainly capable of transforming (A LOJ B) LOJ C to A LOJ (B LOJ C) naturally as well but I didn't see any evidence that the reverse is true.

A very contrived case where the first query performs better than the second is

DROP TABLE  MyGrandChild , MyChild,  MyParent

CREATE TABLE MyParent
(Id int)

CREATE TABLE MyChild
(Id int PRIMARY KEY
,ParentId int,
Filler char(8000) NULL)

CREATE TABLE MyGrandChild
(Id int
,ParentId int)

INSERT INTO MyChild
                      (Id, ParentId)
SELECT TOP (100000) ROW_NUMBER() OVER (ORDER BY @@SPID),
                     ROW_NUMBER() OVER (ORDER BY @@SPID)
FROM master..spt_values  v1, master..spt_values

INSERT INTO MyGrandChild
                      (Id, ParentId)
OUTPUT INSERTED.Id INTO MyParent
SELECT TOP (3000) Id, Id AS ParentId
FROM MyChild
ORDER BY Id

SET STATISTICS IO ON;
SET STATISTICS TIME ON;

SELECT gc.Id       AS gcId,
       gc.ParentId AS gcpId,
       c.Id        AS cId,
       c.ParentId  AS cpId,
       p.Id        AS pId
FROM   MyGrandChild AS gc
       LEFT OUTER JOIN MyChild AS c
         ON c.[Id] = gc.[ParentId]
       LEFT OUTER JOIN MyParent AS p
         ON p.[Id] = c.[ParentId]

SELECT gc.Id       AS gcId,
       gc.ParentId AS gcpId,
       c.Id        AS cId,
       c.ParentId  AS cpId,
       p.Id        AS pId
FROM   MyGrandChild AS gc
       LEFT OUTER JOIN( MyChild AS c
                        LEFT OUTER JOIN MyParent AS p
                          ON p.[Id] = c.[ParentId])
         ON c.[Id] = gc.[ParentId]

Which gives plans

For me Query 1 had an elapsed time of 108 ms vs 1,163 ms for Query 2.

Query 1

Table 'Worktable'. Scan count 0, logical reads 0
Table 'MyChild'. Scan count 0, logical reads 9196
Table 'MyGrandChild'. Scan count 1, logical reads 7
Table 'MyParent'. Scan count 1, logical reads 5

Query 2

Table 'MyParent'. Scan count 1, logical reads 15000
Table 'MyChild'. Scan count 0, logical reads 9000
Table 'MyGrandChild'. Scan count 1, logical reads 7

So it might be provisionally assumed that the first ("unnested") syntax is potentially beneficial as it allows more potential join orders to be considered but I haven't done exhaustive enough testing to have much confidence in this as a general rule.

It may well be entirely possible to come up with counter examples where Query 2 performs better. Try both and look at the execution plans.