texts: How to increase performance in Cross Database operations?

texts:
We currently have a SQL Server 2008R2 being accessed by a legacy ASP application.

Among different processes, one of them essentially SELECTs data from one database and INSERTs (or UPDATEs) them into another database, based on a set of business rules.  This process generally runs at night after business hours, but on occasions it must be run during business hours to avoid task backlog.

We are now facing an I/O contention issue and so far my best guess is that this process is hurting the server.

Facts that support this hypothesis:

When the process is NOT running concurrently with ordinary operation, the server runs smoothly;
We generally experience I/O contention early in the morning; severely increased number of Page Reads/sec (avg 2000 reads/s), decreased Page Life Expectancy, some increase in LazyWrites.  As far as my knowledge goes, this is generally a sign of SQL Server self-tuning to a different workload.  This behaviour usually goes away after 20-something minutes.

I am now considering adding two other partitions (one for datafiles, one for logfiles) and move the database which receives the INSERTs to them, effectively distributing the I/O load.

Is this procedure worthwhile?
Are there any other variables to look into before venturing into disk rearrangement?  While not 100%, indices are fairly well in place, for instance.

Thanks in advance!

QUICK UPDATE

Having reviewed some monitoring graphs, I noticed this contention on some other occasions other than early in the morning.  While I don't profile the server continuously, it's safe to say that the process that copies between database was NOT running during most of these occasions, which means there might be other variables behind the contention.

Again, this only adds up to the question on which other variables I should have a look before playing with disks.


texts:
I just learned from an answer to this question that the .mdf and .ldf files that SQL Server uses are an important pair for the database. I previously thought the .ldf was just a non-critical log for backups or audits.

Anyway, our server right now has the .mdf on a SSD and the .ldf on the main HDD with Windows. I set it up like that because I assumed the .mdf was the most performance critical file location.

But now it seems like the .ldf is actually the first place data is written in the event of an UPDATE, and then the .mdf is written to later on.

Should I put both files on the SSD? Or is it better if they're on different drives, even if one of them is an HDD? (and if so, which file should go where?)

If it makes a difference, SELECT performance is more important to me (and more common) than UPDATE.


texts: how to replace with default value when querying a JSONB column with a key that is not present in a particular row giving empty result

texts:
I have a table that has a jsonb column as 'result' which has an array of objects.
Looks like this for every row:
[{"grade": "A", "subject": "MATH"}, {"grade": "B", "subject": "PHY"}...]

When I am querying this way
select up.id, jsonb_array_elements(to_jsonb(up.result)) ->>'subject' as result
from "UserProfile"

I get correct result but problem arises when I query for a particular row with an id wherein this result column has an empty array data. Then it neither gives any result nor an error even though I am selecting 'id' in query. not able to put any default value also using coalesce
How can I resolve this issue?


texts: Send email only if no backups within 24 hours

texts:
I'm trying to combine the script from Tim Ford (http://www.mssqltips.com/sqlservertip/1601/script-to-retrieve-sql-server-database-backup-history-and-no-backups/)

-------------------------------------------------------------------------------------------
--Databases Missing a Data (aka Full) Back-Up Within Past 24 Hours
-------------------------------------------------------------------------------------------
--Databases with data backup over 24 hours old
SELECT
   CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
   msdb.dbo.backupset.database_name,
   MAX(msdb.dbo.backupset.backup_finish_date) AS last_db_backup_date,
   DATEDIFF(hh, MAX(msdb.dbo.backupset.backup_finish_date), GETDATE()) AS [Backup Age (Hours)]
FROM    msdb.dbo.backupset
WHERE     msdb.dbo.backupset.type = 'D'
GROUP BY msdb.dbo.backupset.database_name
HAVING      (MAX(msdb.dbo.backupset.backup_finish_date) < DATEADD(hh, - 24, GETDATE()))

UNION

--Databases without any backup history
SELECT
   CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
   master.dbo.sysdatabases.NAME AS database_name,
   NULL AS [Last Data Backup Date],
   9999 AS [Backup Age (Hours)]
FROM
   master.dbo.sysdatabases LEFT JOIN msdb.dbo.backupset
       ON master.dbo.sysdatabases.name  = msdb.dbo.backupset.database_name
WHERE msdb.dbo.backupset.database_name IS NULL AND master.dbo.sysdatabases.name <> 'tempdb'
ORDER BY
   msdb.dbo.backupset.database_name

with the sp_send_dbmail proc.  Right now it is sending emails even when 0 rows are returned (i.e. all databases have been backed up within last 24 hrs).

What I would like to have happen is an email sent out ONLY when the recordCount is > 0.  i.e. there is a database that has not been backed up within 24hrs.

Current script which sends an email even when there are 0 results returned.

EXEC msdb.dbo.sp_send_dbmail
    --@profile_name = 'SQLDBEmail',
    @recipients = 'dba@someemailaddress.com',
    @query = 'SELECT
               CONVERT(CHAR(100), SERVERPROPERTY(''Servername'')) AS Server,
               msdb.dbo.backupset.database_name,
               MAX(msdb.dbo.backupset.backup_finish_date) AS last_db_backup_date,
               DATEDIFF(hh, MAX(msdb.dbo.backupset.backup_finish_date), GETDATE()) AS [Backup Age (Hours)]
            FROM    msdb.dbo.backupset
            WHERE     msdb.dbo.backupset.type = ''D''
            GROUP BY msdb.dbo.backupset.database_name
            HAVING      (MAX(msdb.dbo.backupset.backup_finish_date) < DATEADD(hh, - 24, GETDATE()))

            UNION

            --Databases without any backup history
            SELECT
               CONVERT(CHAR(100), SERVERPROPERTY(''Servername'')) AS Server,
               master.dbo.sysdatabases.NAME AS database_name,
               NULL AS [Last Data Backup Date],
               9999 AS [Backup Age (Hours)]
            FROM
               master.dbo.sysdatabases LEFT JOIN msdb.dbo.backupset
                   ON master.dbo.sysdatabases.name  = msdb.dbo.backupset.database_name
            WHERE msdb.dbo.backupset.database_name IS NULL AND master.dbo.sysdatabases.name <> ''tempdb''
            ORDER BY
               msdb.dbo.backupset.database_name',
    @subject = 'Databases not backed up within 24 hrs',
    @attach_query_result_as_file = 1 ;


texts: How could a "SECURITY DEFINER" function in PG be insecure with an improper search_path?

texts:
The PG docs provides this example of a properly written security definer function with a search_path:

CREATE FUNCTION check_password(uname TEXT, pass TEXT)
RETURNS BOOLEAN AS $$
DECLARE passed BOOLEAN;
BEGIN
        SELECT  (pwd = $2) INTO passed
        FROM    pwds
        WHERE   username = $1;

        RETURN passed;
END;
$$  LANGUAGE plpgsql
    SECURITY DEFINER
    -- Set a secure search_path: trusted schema(s), then 'pg_temp'.
    SET search_path = admin, pg_temp;

I don't understand this part:

  But without the SET clause, or with a SET clause mentioning only
  admin, the function could be subverted by creating a temporary table
  named pwds.

If admin.pwds exists and search_path is set to admin only, how is it even possible to create a temporary table, pwds, maliciously?


texts: database connection and sessions in an example of social media

texts:
I am referring to MS SQL connection and sessions. I know that each application should make a connection in order to access the database, and each connection can have multiple sessions. In case of a social media website where there are multiple users each with its own security credentials; does every user have to make a connection to all the databases that the user needs, and for different actions, like deleting a post or uploading a picture, make corresponding sessions? Or is this terminology for an application not a user?
I am new to this terminology and sorry for possible misunderstandings.

Thanks for your time.


texts: How memory limit for backup process is calculated?

texts: How "memory limit" for backup process is actually calculated?


texts:
Let's imagine a stored procedure that retreives data and do some kind of pagination. This procedure has some inputs describing which set of data we want and how we sort it.

Here is a very simple query, but let's take it as an example.

create table Persons(id int, firstName varchar(50), lastName varchar(50))
go
create procedure GetPersons @pageNumber int = 1, @pageSize int = 20, @orderBy varchar(50) = 'id', @orderDir varchar(4) = 'desc'
as

declare @sql varchar(max)
set @sql = 'select id, firstName, lastName
from (
    select id, firstName, LastName, row_number() over(order by '+@orderBy+' '+@orderDir+') as rn
    from Persons
    ) t
where rn > ('+cast(@pageNumber as varchar)+'-1) * '+cast(@pageSize as varchar)+'
        and rn <= '+cast(@pageNumber as varchar)+' * '+cast(@pageSize as varchar)+'
order by '+@orderBy+' '+@orderDir

exec(@sql)

It is supposed to be used like that:

exec GetPersons @pageNumber = 1, @pageSize = 20, @orderBy = 'id', @orderDir = 'desc'

But a smart guy could launch:

exec GetPersons @pageNumber = 1, @pageSize = 20, @orderBy = 'id)a from Persons)t;delete from Persons;print''', @orderDir = ''

... and drop data

That's obviously not a secure situation. And how could we prevent it ?

Note: this question is not about "is it a good way to do pagination ?" nor "is it a good thing to do dynamic sql?". The question is about preventing code injection when building sql queries dynamicaly in order to have some guidelines to make the code a bit cleaner if we have to do similar stored procedures again in the future.

Some basic ideas:

Validate inputs

create procedure GetPersons @pageNumber int = 1, @pageSize int = 20, @orderBy varchar(50) = 'id', @orderDir varchar(4) = 'desc'
as

if @orderDir not in ('asc', 'desc') or @orderBy not in ('id', 'firstName', 'lastName')
begin
    raiserror('Cheater!', 16,1)
    return
end

declare @sql varchar(max)
set @sql = 'select id, firstName, lastName
from (
    select id, firstName, LastName, row_number() over(order by '+@orderBy+' '+@orderDir+') as rn
    from Persons
    ) t
where rn > ('+cast(@pageNumber as varchar)+'-1) * '+cast(@pageSize as varchar)+'
        and rn <= '+cast(@pageNumber as varchar)+' * '+cast(@pageSize as varchar)+'
order by '+@orderBy+' '+@orderDir

exec(@sql)

Pass ids instead of strings as inputs

create procedure GetPersons @pageNumber int = 1, @pageSize int = 20, @orderBy tinyint = 1, @orderDir bit = 0
as

declare @orderByName varchar(50)
set @orderByName =  case @orderBy when 1 then 'id'
                        when 2 then 'firstName'
                        when 3 then 'lastName'
                    end
                +' '+case @orderDir
                        when 0 then 'desc'
                        else 'asc'
                    end

if @orderByName is null
begin
    raiserror('Cheater!', 16,1)
    return
end

declare @sql varchar(max)
set @sql = 'select id, firstName, lastName
from (
    select id, firstName, LastName, row_number() over(order by '+@orderByName+') as rn
    from Persons
    ) t
where rn > ('+cast(@pageNumber as varchar)+'-1) * '+cast(@pageSize as varchar)+'
        and rn <= '+cast(@pageNumber as varchar)+' * '+cast(@pageSize as varchar)+'
order by '+@orderByName

exec(@sql)

Any other suggestions?


texts:
I have table which contains about 470mln of rows. I would like to select data based on date. I have two indices created on this table. One is clustered one and other is non clustered on date column (date is stored as INT). I have simple select statement:
select *
from big_table
where [date] BETWEEN 20200820 AND 20200828

The problem is that query plan uses clustered index scan instead of nonclustered seek + keylookup. Plan as follows:

Estimates generated in query plan are fine, statistics are up to date. This date range should deliver about 5mln of rows.
When I provide index hint then this select completes in couple of seconds - without hint it takes couple of minutes to finish.
This is SQL Server 2019 and generaly speaking I noticed that db prefers clustered index scans that using non clustered + keylookups even on bigger tables.
I would rather not using hint because:

sometimes I select wider ranges where clustered index scan should be desirable
table is used in view and I cannot supply index hint to the view

Is there any explanation why db is not using NC index it this case?
Links to query plans:

https://www.brentozar.com/pastetheplan/?id=SkaQFqLmD
https://www.brentozar.com/pastetheplan/?id=Hkh7q587P


texts: Is there a way to used grouped data multiple times in a query

texts:
Suppose a table contains employee id, department name and salary. I want to select the department that gives total salary greater than average salary given by all departments.

The solution that I know, is grouping data by department to find sum of salaries given by that department and then use having clause to compare the sum with the output of a subquery, that again groups by department and finds average department salary.

I want to know if it is possible to group according to departments once, and use this grouped data multiple times so that we don't have to group again and again.


texts: How to stuff together earliest date and or text

texts:
I'm trying to write a query and output to an excel file which outputs all of a members mortgages into a cell, but grabbing their earliest maturity date first.

Table Creation

CREATE TABLE mortgages
(
      PartyID varchar(30)
      , Balance varchar(30)
   ,AccountNum varchar(30)
      ,FullName varchar(30)
      ,MaturityDate date

);

INSERT INTO mortgages (PartyID, Balance, AccountNum, FullName, MaturityDate)
VALUES ('1', '100', '10000', 'Jane Doe', '2020-03-20' )
     , ('1', '1000', '10001', 'Jane Doe',NULL );

For example, my desired output would look like this for a each member:

Jane Doe - Account#: 10000  Maturity Date: 2020-03-20   Balance: 100 Account#: 10001  Maturity Date: No Maturity Date   Balance: 1000

If they have a mortgage with no maturity date, it would just set the date to output text like 'No Maturity Date'.

I have the query to output everything into one line, but for some reason it's setting some of members earliest maturity dates to 'No Maturity Date' when they indeed have a date.

My SQL query currently looks like this for:

 ;WITH Mtg AS(
    SELECT DISTINCT PartyID,
    Balance,
    accountnum,
    fullname,
    COALESCE(NULL,CAST(MIN([MaturityDate]) AS VARCHAR(30)), 'No Maturity Date'+ SPACE(2)) [MaturityDate]
    FROM mortgages
    group by partyid, balance, accountnum, fullname)

After that, I would format it for output which looks like this:

    )
    , Mtg1 AS(
        SELECT DISTINCT
            outputLogic.DescriptionList
        , SUM(M.Balance) OVER(PARTITION BY M.PartyID ORDER BY M.PartyID) AS TotalMaturingBalance
        , [MaturityDate]
        , M.[FullName]
        , M.PartyID
        FROM Mtg AS M
        CROSS APPLY(
            SELECT (STUFF((SELECT'| ' + [FullName] +'- '+ SPACE(2)
                    + 'Account#: ' + [AccountNum] + SPACE(2)
                    + 'Mat. Date: ' + [MaturityDate] + Space(2)
                    + 'Mat. Bal:'+ '$'+CAST(M.Balance AS NVARCHAR(MAX)) + SPACE(2)
            FROM Mtg AS M1
            WHERE M1.PartyID=M.PartyID
            FOR XML PATH('') ), 1, 5, '')  ) AS DescriptionList
        ) outputLogic

I have my original script, which doesn't account for the 'No Maturity Date' text that's in production now.  When I run my edited script (the one I posted above), the same member which has both types of mortgages (maturity date and no maturity date) in the production script would have only the one correct Account # and Maturity Date, but in my edited script it would output just the Account # and 'No Maturity Date'

My question is, what would I need to change in my script to output the members earliest maturity date, and then 'No Maturity Date' in the same column for the member scenario I explained above?

In my edited version, the SQL I posted above, only the data for 10001 would be outputted, in the production version only the 10000 would be outputted.

My desired output would look like this:

 Jane Doe- Account #: 10000 Mat. Date: 2020-03-20  Mat. Bal:$100
 Account #: 10001 Mat. Date: No Maturity Date  Mat. Bal:$1000

SQL Fiddle


texts:
We have a three node AlwaysOn availability group with Node 3 being used for reporting.  There is a job which runs on Node 3 and queries a bunch of tables and selects data out and it runs every three minutes.  This jobs fails randomly with the below error.

  Snapshot isolation transaction failed in database '%.*ls' because the
  object accessed by the statement has been modified by a DDL statement
  in another concurrent transaction since the start of this transaction.
  It is disallowed because the metadata is not versioned. A concurrent
  update to metadata can lead to inconsistency if mixed with snapshot
  isolation.

Things I have checked

we have SQL Audit that tracks DDL changes, so I have checked that and
could not see any during the job failure.
Look for any update statistics, Index Reorg/Rebuild job during that time

What else could be causing this error?


texts: Programmatically copy index from one table to another, via a stored procedure?

texts:
I use a stored procedure to copy the tables from one database to the other database. Right now it does

insert into copy
   select * from original

I would like to copy the index from the original table as well. What would be the best way to go about this ?


texts: One to at most one relation or multiple null columns?

texts:
I have a table with many attributes that can be grouped in logical groups, and thus the idea of putting these attributes in separate tables seems attractive.  The logical groups don't represent entities themselves.  When one attribute in the group is set, most of the rest of the attributes in that group also must be set (but not all).  The fields's type of the groups generally are VARCHAR(15-20).  There's no BLOB or TEXT fields either.

The logical groups don't represent subtypes, because they aren't mutually exclusive.

The entity is requested more for reading than for writing.  Hence a large table looks appropriate.  Also, this way the joins are avoided in queries.  The part of this approach I don't like is the large number of nullable fields.

Looking for Expert advice.


texts: How to restore 11gR2 fullbackup to 12c database in rman

texts:
I need to transfer my database from Oracle 11gR2 to 12c database. It will be in same computer. I took full backup of Oracle 11gR2 database. Then I remove Oracle 11gR2. I installed Oracle 12c and I tried to restore my backup. I could restored spfile, controlfile, datafiles.. But I couldn't do

RMAN> ALTER DATABASE OPEN RESETLOGS;
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00704: bootstap process failure
ORA-00604: error occurred at recursive SQL level 2
ORA-00904: "I". "UNUSABLEBEGINNING# ": invalid identifier
Process ID: 3377
Session ID: 125 Serial number: 5

What should I do to open database? Should I do Pre-Upgrade script ? I couldn't understand to do pre-upgrade script step..


texts: Postgres multicolumn vs combined single column indexes

texts:
The answer is always "run it and see how it works on your dataset" of course, but looking for general guidelines here. I have three queries:

... WHERE A = '...' AND P = '...' AND Q = '...'
... WHERE B = '...' AND P = '...' AND Q = '...'
... WHERE C = '...' AND P = '...' AND Q = '...'

The first query featuring A will be more common. Any general recommendations on whether to use 5 separate single column indexes, or whether to use 3 combined (A, P, Q), (B, P, Q), (C, P, Q) ?


texts: TM REQUEST Rollback SQL Server 2012 Always on Availability Group

texts:
I have continuous transactions with a TM REQUEST command in sp_who2 active which is at a state of ROLLBACK.

This is on an always on a SQL Server 2012 availability group.

Has anyone had experience of this before?


texts: Are the switches for the SQLCMD command case sensitive?

texts:
I have MS SQL Server 2014 Management Studio installed on two computers, both of which run on Windows 10. The switches for SQLCMD command are case-sensitive on one computer, but it is not on the other one. If I type the command with lower case switches (like the below examples), it works on one but doesn't work on the other.

sqlcmd -s .\<_servername_>
sqlcmd -l

For the second computer, I have to type with upper case as shown below.

sqlcmd -S .\<servername>
sqlcmd -L

What is the issue/setting that decides this?


texts: Is there a way/any functions/DMV's in SQL Server that will provide who executed a stored procedure of a particular database?


texts: Is it possible to enter a missing sequence number into another table, on exception in a Trigger

texts:
Below is the code which I have written in the exception part of my before insert trigger.

CREATE OR REPLACE TRIGGER ORGANIZATIONS_TAB_BI_TRG
    BEFORE INSERT ON ORGANIZATIONS_TAB
    FOR EACH ROW
DECLARE
    -- variables
    lv_CurrentUserID VARCHAR2(30):= NULL;
BEGIN
   -- Getting the current session user id
    SELECT USER
      INTO lv_CurrentUserID
      FROM DUAL;
    IF :NEW.ORG_ID IS NULL THEN
      SELECT TO_NUMBER(TO_CHAR(SYSDATE,'RRRR')||LPAD(ORGANIZATIONS_SEQ.NEXTVAL,6,0) ORG_ID
        INTO :NEW.ORG_ID
        FROM DUAL;
    END IF;
EXCEPTION
  WHEN OTHERS THEN
        SEQ_AUDIT_PKG.InsertMissingSequence('ORGANIZATIONS_SEQ',
                      ORGANIZATIONS_SEQ.CURRVAL,
                          SYSDATE,
                          SYSDATE,
                          lv_CurrentUserID,
                          'Sequence Missed due to :'||SQLERRM);
END;

Can we do this for storing the missing sequence numbers to keep a track?
The SEQ_AUDIT_PKG.InsertMissingSequence is as follows:

PROCEDURE InsertMissingSequence(P_SEQ_NAME IN MISSED_SEQ_LOG.SEQUENCE_NAME%TYPE,
                                             P_SEQ_NUMBER IN MISSED_SEQ_LOG.SEQUENCE_NUM%TYPE,
                                             P_SEQ_DT IN MISSED_SEQ_LOG.SEQUENCE_DT%TYPE,
                                             P_CREATION_DT IN MISSED_SEQ_LOG.CREATION_DT%TYPE,
                                             P_CREATED_UID IN MISSED_SEQ_LOG.CREATED_UID%TYPE,
                                             P_REMARKS IN MISSED_SEQ_LOG.REMARKS%TYPE) IS
  BEGIN
    -- Inserting records into the Missing Sequence Log
    INSERT INTO MISSED_SEQ_LOG (SEQUENCE_NAME,SEQUENCE_NUM,SEQUENCE_DT,CREATION_DT,CREATED_UID,REMARKS)
           VALUES(P_SEQ_NAME,P_SEQ_NUMBER,P_SEQ_DT,P_CREATION_DT,P_CREATED_UID,P_REMARKS);
  EXCEPTION
    WHEN OTHERS THEN
      NULL;
  END InsertMissingSequence;


texts: How is Index rebuild affected by IN_ROW_DATA vs LOB DATA

texts:
I am using Ola Hallengren's scripts to maintain my databases.  On a few of them there are indices that don't seem to be benefiting from the maintenance.  I know that his scripts have a lower threshold (I believe 1000 pages), below which it will not attempt to rebuild or reorganize.

For one index, in dm_db_index_physical_stats the clustered index shows up on 2 lines.  One is PK_DATA, allocation unit type of IN_ROW_DATA, 16 pages, and 93.75% fragmented.  The other PK Data, allocation unit type of LOB_DATA, 57200 pages, and 0% fragmented.

Should I expect this index to be reorganized or is it being skipped due to the differing page counts for the different allocation unit types?  Or might it be getting reorganized unsuccessfully?

Thanks in advance for any advice you might have.


texts:
I have a list of items. Each item is attached to several urls and each url has a score number. So I have something like this :

How should I organise this in dynamoDB? How should I format my Json file to update my data?

Right now I'm using the first item column as a "partition key" and AttributeType as a number. I don't know how should I organise the rest. Maybe something like this : A { stuff : { URL : value, URL: value, URL: value} }. But with this structure I've hard time to update my table and I don't have experiences with this. I don't know the best practises, and I don't find any tutorial...

Thanks!


texts: Merge databases with sql only: firefox places and visits

texts:
I have two tables in each of two dbs:

places: id, url (unique),

visits: id, place_id, timestamp, type.

I need to merge those databases:

There are one main database and one "to-merge". The unique data should be added to the main database (of course with new ids).
Source dbs may contain same places and visits rows but some of them can be with different ids.
Two visits rows are equal when their timestamp, type columns and places.url are equal.
Two places rows are equal when their url columns are equal.
place_id should be corrected for merged visits.

I can attach databases with ATTACH ... AS ....

I've already done this with C# code but that solution seems to be a kind of overcomplicated (I have it on github). I'm wondering if it's possible to merge them with sql only? How would you do that?


texts: Filtering out duplicate domains from URL column using Postgres full-text search parsers

texts:
I have a PostgreSQL database containing pages and links downloaded by a web crawler, with the following tables:

pages
----------
id:          Integer (primary key)
url:         String  (unique)
title:       String
text:        String
html:        String
last_visit:  DateTime
word_pos:    TSVECTOR

links
----------
id         Integer (primary key)
source:    String
target:    String
link_text: String
UNIQUE(source,target)

crawls
---------
id:         Integer (primary key)
query:      String

crawl_results
-------------
id:       Integer (primary key)
score:    Integer (constraint 0<=score<=1)
crawl_id: Integer (foreign key, crawls.id)
page_id:  Integer (foreign key, pages.id)

The source and target fields in the links table contain URLs. I am running the following query to extract scored links from the top-ranking search results, for pages that haven't been fetched yet:

WITH top_results AS
    (SELECT page_id, score FROM crawl_results
     WHERE crawl_id=$1
     ORDER BY score LIMIT 100)
SELECT top_results.score, l.target
FROM top_results
    JOIN pages p ON top_results.page_id=p.id
    JOIN links l on p.url=l.source
WHERE NOT EXISTS (SELECT pp.id FROM pages pp WHERE l.target=pp.url)

However, I would like to filter these results so that only one row is returned for a given domain (the one with the lowest score). So for instance, if I get (0.3, 'http://www.foo.com/bar') and (0.8, 'http://www.foo.com/zor'), I only want the first because it has same domain foo.com and has the lower score.

I was able to find documentation for the builtin full text search parsers, which can parse URLS and extract the hostname. For instance, I can extract the hostname from a URL as follows:

SELECT token FROM ts_parse('default', 'http://www.foo.com') WHERE tokid = 6;

    token
-------------
 www.foo.com
(1 row)

However, I can't figure out how I would integrate this into the above query to filter out duplicate domains from the results. And because this is the docs for "testing and debugging text search", I don't know if this use of ts_parse() is even related to how the URL parser is intended to be used in practice.

How would I use the host parser in my query above to return one row per domain? Also, how would I appropriately index the links table for host and url lookup?


texts:
Good day.

I have a table like this:

CREATE TABLE mytable (
    myname varchar(30),
    mydate date DEFAULT CURRENT_DATE,
    PRIMARY KEY(myname, mydate)
);

EDIT:
Forgot to mention I am using a view and a rule

So I have a view that is basically the same thing as the table

CREATE VIEW mytable_view (name, _date)
AS SELECT (myname, mydate)
FROM mytable;

And the rule is as follows:

CREATE RULE update_mytable
AS ON INSERT TO mytable_view
DO INSTEAD (
    INSERT INTO mytable(myname, mydate)
    VALUES(NEW.name, NEW._date);
);

Now I try to insert into the table using the view by doing this:

INSERT INTO mytable_view(name)
    VALUES("Mozzart");

However, I get an error saying:

  ERROR:  null value in column "mydate" violates not-null constraint

Why is this? I expected the default value to be used if the value being inserted is null or wasn't specified. Is this not how default works?


texts: how to use the same timestamp along several tables?

texts:
Lets say a have 2 tables and both have a datetime column.

On table A, on datetime column, lets say, ts1, is non null, default is CURRENT_TIMESTAMP and on update CURRENT_TIMESTAMP;

The other table, B, the datetime column, lets say, ts2 is non null;

The datetime values on both tables must be the same (A.ts1==B.ts2).

As ts1 is auto-updated on inserts and updates, I don't need to get the current_stamp on advance.

Now, how can I do inserts on table B in such way that B.ts2 has the same timestamp value (A.ts1) from a specific record on table A?

Is there a relation ship between the tables to achieve this or some special insert/select statement ?

sorry if the answer is obvious...I am a new to databases...
PS: Using mySql 5.7 innoDB.

PS2: I found this works, but seems to be awkward.

insert into B (y, ts2) values ('a value for y', (select ts1 from A where x=1));

any other better ideas ?

@Lennart:
Thanks for your comments. Yes I need help in the design. I am open to suggestions.

Developing this a bit more:

On table A, x is a key.
Also, ts1 can not be unique since more than 1 record can have the same ts, because of possible concurrent access to create records on A.

Currently, there is no keys on table B.
The column ts2 can not be unique too, since table B store several y values with the same timestamp ts2;
In fact the excepted usage of table B is something like:

select * from B where ts2=(select ts1 from A where x=somevalue);

I liked how this sounds "add a trigger that does the update". Can you develop this a bit more, please ?

PS3: @Lennart, here we go:

CREATE TABLE `device` (
  `deviceID` varchar(12) NOT NULL,
  `snmpSet` varchar(30) NOT NULL,
  `firstSeen` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `lastSeen` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf16;

ALTER TABLE `device`
  ADD PRIMARY KEY (`deviceID`);
COMMIT;

CREATE TABLE `snmpData` (
  `deviceID` varchar(12)  NOT NULL,
  `ts` datetime NOT NULL,
  `oid` varchar(45)  NOT NULL,
  `value` varchar(30)  NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf16 COMMENT='hold all snmp data from each device';

if it is a new device, insert it into table device and add its data to snmpData

INSERT INTO device (deviceID, snmpSet) VALUES ($id, $snmpSet);
INSERT INTO snmpData (deviceID, ts, oid, value) VALUES ($id, (SELECT lastSeen FROM device WHERE deviceID=$id),$oid,$value);

else, it is a existing device, I need to update the lastSeen on device and then, the same insert on snmpData

UPDATE device SET lastSeen=current_timestamp() WHERE deviceID=$id;
INSERT INTO snmpData (deviceID, ts, oid, value) VALUES ($id, (SELECT lastSeen FROM device WHERE deviceID=$id),$oid,$value);

The general idea:
This DB will support a monitoring tool that store snmp data (oid/value pairs) for several devices (deviceID)
The table device holds basic info about every device and the snmpData holds the data itself for every device.
If I want to check the last values received from a device I plan to do something like this:

select oid, value from snmpData where deviceID=$id and ts=(select lastSeen from device where device=$id);

as result, I will receive a list of 50 or more records, each one for a specific metric and its value.
There is some simplification here. In the real case, both tables have more columns, but you got the idea.

caveats:
The code for writing to tables device and snmpData are not on the same logical block.
It is a PHP program and the operation on table device has its own try/catch block.
If everything it is ok with the INSERT/UPDATE on device table, there is another try/block with the INSERT operation on table snmpData.
Each block has its own prepare/execute staements, so it is not possible to  use the variable trick
"SET @ts := NOW();" that @Rick James  suggested.
BTW, I loved, its new to me, and I will use it on another context. thanks)


texts: Snapshot Azure database for fast revertion to snapshot

texts:
I have spent a considerable amount of effort creating a set of Azure SQL databases and importing data. Importing the data with the DMA tool took the best part of two days. This is on an 'SQL Server (logical server)'.

I now want to take a snapshot of these databases so that I can quickly return them to this initial state. I assume the best way to do this is to create a backup of each one that I can restore later. I'll probably want to do this restore quite often, so speed of restore is very important; 2 days is certainly unacceptable.

But I'm struggling to find out how to do this. There is no 'Backup Now' button in the Azure Portal that I can find. I've waited a day since creating the databases and still nothing is listed in the Available backups page on the Azure Portal. I looked at 'Configure policies' and this doesn't seem to allow creation of a backup on-demand, or to modify the retention period of an existing backup.

I'm probably barking up the wrong tree. What is the accepted way to create a snapshot of an Azure SQL database and then return to that snapshot quickly?


texts: Configuring Oracle 11gR2 RAC on Virtual Machine

texts:
I am working in a small company and interested to understand the RAC configuration.
I have read many documents.. I found that we need three IPs for each node on a RAC. Public IP , Private IP , and Virtual IP..

Now As, I know that sytem Admin Team will provide public IP from  for each node, but what about private and virtiual ip? who will provide private and virtual IP?
OR  dba  can create private and virtual ip from public IP.

Plz clarify!


texts: How to optimize min/max queries for large tables on PostgreSQL

texts:
How do you index a table in PostgreSQL so that min/max queries return as quickly as possible?

I have a large table with a couple hundred million rows. Each row as a source_id and a date when the record was last updated. I'd like to collect some statistics for each source_id, specifically the min and max date range for each source_id.

So I created this index on my table:

 CREATE INDEX CONCURRENTLY mydata_source_last_updated_date ON mydata (source_id, last_updated_date ASC);

However, when I try to query the min dates per source with:

SELECT source_id, MIN(last_updated_date) FROM mydata GROUP BY source_id;

the query takes about an hour to complete.

Is this normal performance for such a large table, even with an index? How can I reduce this query time?


texts: Select all results from two independent tables

texts:
I have two tables:

Comments
--------
id
comment_content
time_stamp

Activities
-----------
id
activity_content
time_stamp

And I would like to combine the results from both, ordered by their timestamp fields.


texts: Why does Oracle APEX Shared_Servers = 0 Break Apex 3.2

texts:
Installed APEX 3.2 and set the shared_servers = 5 and APEX works great, but breaks other processes with our DB. So I set the shared_server = 0 again and now APEX won't show.

I'm using Oracle 11g and APEX 3.2. Does anyone have any ideas why APEX can't work with shared_servers=5?

Any/All help appreciated!

-JFV


texts: Installed mysql 8 on Mac then mysql 5.7 now now they wont work

texts:
I have a brand new Macbook pro with Mojave. I installed mysql 8 using the dmg file but realized that Sequel Pro is not playing nice with it and after researching I decided to install mysql 5.7. This time I installed brew and did it. Sequel pro keeps shutting down and my previous database is showing up even though I thought I removed it. Before I reset the mac and start over, is there a way I can completely remove mysql ( all versions ) from the computer and reinstall using brew just the 5.7 version?

If this is the wrong place to ask kindly let me know where I should ask and I will.


texts: How to handle big data update based on excel spreadsheet

texts:
Scenario

Select-based data is exported from DB to Excel spreadsheets.
Some manual work is done on the excel files (filling missing data).
Changed data from excel files is updated back to DB.

Environment

Oracle DB
Original table is about 7 million rows
Can't use import tools in SQL Developer etc, must be a plain SQL script.
There are several excel files with couple hundred of thousand rows each (400-700 thousand)

Current solution

There is an UPDATE statement generated for each row of the excel file. It checks if data has been changed (the file has separate columns for old and new values).
So from each file we get hundreds of thousands of UPDATE statements.

Question is...

This solution is fairly simple, but I wonder if there is a better way to do that?


texts:
I am a .net developer and not a expert in database. I have a application running with SQL server 2008. Yesterday there was issue with a certain query getting executed in infinite loop and which caused DB size to grow from 500 MB to 380 GB. The log file itself was 300 GB and mdf file 80 GB. Today I stopped the query and truncated the log file to 12 MB. Then I figured out tables which were grown and truncated those tables as well but still my db size is 67 GB! I ran query to determine the size of all tables https://stackoverflow.com/a/7892349/287100

But the total size of all tables was less than 100 MB. I want to know why the db size is huge and how can I reduce it to normal.

Thanks.


texts: Remove Column From Replicated Article without re-snapshoting in 2008r2

texts:
I've inherited an undocumented 2008r2 Transactional Replication set-up.

There are close to 500GB of replicated articles with lots of hidden gotchas such as Indexed views being replicated to Tables.

The Development team need to Drop 1 column from a small lookup table, and Create a new column on another Table. In addition the Triggers on the 2 tables will be modified.

How can I accomplish this without re-taking the snapshot and copying all the data over again? (Am I on a fools errand? should I just start planning for a long weekend? the sooner I have a plan the better.)

The entire Publisher database isn't replicated - only selected articles. (So my snapshot options are limited, can't restore from backup)

I've done some research but can't find anything specific to 2008R2.


texts: Will large transaction log file slow db recovery?

texts: Did a manual failover after windows updates to passive node today and recovery was fast for all but one db. The one slow db had a transaction log size of 203200.00 mb. Log Space percent used = 0.25.  Will the large transaction log file size slow down recovery?


texts: What is the recommended way to backup a MySQL/Amazon RDS database to S3?

texts:
I have two purposes for this:

To have an offsite backup in case of a region wide problem with Amazon Web Services.
To copy production data from a production billing account to a beta billing account.

Currently it does not appear that Amazon supports either of these two use cases out of the box.

I have seen mysqldump and xtrabackup (see form post) mentioned.

I have also seen a more complicated process (documented here)

A new RDS server in the source billing account (prod) is spun up off a recent backup.
A new EC2 instance is spun up that has access to the RDS server in step 1.
mysqldump is used to make a backup of this database.
Backup is copied to an offsite location (S3?).
In a separate account and/or region, a fresh RDS server is spun up.
Database dump is imported.

Tips and suggestions are appreciated.


texts: Sql-Server Unique Constraint but with NON-unique data

texts:
I've inherited this database which has a table defined as such :

CREATE TABLE home_tickets(
    id int IDENTITY(1,1) NOT NULL,
    identifier nvarchar(45) NOT NULL,
    start_date_time datetime NOT NULL,
    revised bit NOT NULL,
    agency_code nvarchar(100) NOT NULL,
    archived tinyint NOT NULL,

    CONSTRAINT PK__home_tic__3213E83F236943A5 PRIMARY KEY CLUSTERED (id ASC) WITH (
        PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON PRIMARY,

    CONSTRAINT UQ__home_tic__969168722645B050 UNIQUE NONCLUSTERED (identifier ASC) WITH (
        PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON PRIMARY
) ON PRIMARY

However when I select from the table given an identifier I am getting multiple rows returned.

select * from home_tickets where identifier = 'TI54815780';

id      identifier  start_date_time         revised archived
415044  TI54815780  2013-10-24 00:00:00.000 0       0
652845  TI54815780  2014-10-24 00:00:00.000 0       0
171642  TI54815780  2012-10-24 00:00:00.000 0       0
945275  TI54815780  2015-10-24 00:00:00.000 1       0
1220327 TI54815780  2016-10-24 00:00:00.000 1       0

Why would this happen with a unique constraint on identifier ?


texts: SQL Server: Importing snapshot of tables from linked server

texts:
I need to import 2 tables from a linked SQL server. So far, the following code has been successful:

SELECT * INTO MyTable1 FROM MyLinkedServer.MyDb.dbo.MyTable1;
SELECT * INTO MyTable2 FROM MyLinkedServer.MyDb.dbo.MyTable2;

In MyLinkedServer, MyTable2 has a foreign key that references MyTable1's PK.

Once in a while a record is added to both tables before the second SELECT INTO completes, so imported data losses referential integrity. In other words, MyTable2 references an entity (row) that doesn't exist in MyTable1.

How do I accomplish this with referential integrity if MyLinkedServer is not running the DTC (Distributed Transaction Coordinator) service?


texts:
One thing that I can't explain is creating secondary index that doesn't trigger fast index creation as it should. I noticed that while creating secondary index on larger table, it gets actually recreated (state: copy to tmp table) instead of just appending the new index. This takes more than 1 hour.

Some data about DB and specific table;

MySQL version: 5.6.23
Engine: InnoDB
Table size: +25 mil rows, 16
columns, +8GB overall size

SQL statement:

CREATE INDEX secindex ON table_name (col3,col4,col5);

If I alter table before creating index, fast index creation is executed (not the whole table is recreated). Create index statement is executed in 2 minutes.

ALTER TABLE table_name FORCE;
OPTIMIZE TABLE table_name;
DUMP table and import back

Interestingly altering with the query mentioned below indexing procedure doesn't change and the whole table is copied again.

ALTER TABLE table_name ENGINE=InnoDB;

I can assume it has to do something with data fragmentation but I couldn't find any straightforward answer to that. I would be very thankful if someone could pinpoint me to what metrics should I check or explanation of how engine decides how this is going to be executed.


texts: How can I get this query to use it's index?

texts:
Queries matching this query below shows up in the slow log (with different contentVersionId &  modifiedDateTime).

As I don't really know what I'm doing I tried adding indexes to all columns in the WHERE clause (on at a time) but it the explain still showed Extra: Using where not Extra: Using index as I was expecting.

Am I wrong in assuming Extra: Using where is bad? If I'm not wrong how should I proceed?

Table is innodb, primarily read from and contains 600000+ rows

The Query:

SELECT cmContentVersion.contentVersionId,
       cmContentVersion.stateId,
       cmContentVersion.modifiedDateTime,
       cmContentVersion.versionComment,
       cmContentVersion.isCheckedOut,
       cmContentVersion.isActive,
       cmContentVersion.contentId,
       cmContentVersion.languageId,
       cmContentVersion.versionModifier
FROM   cmContentVersion
WHERE  cmContentVersion.languageId = 3 AND
       cmContentVersion.isActive   = 1 AND
        (cmContentVersion.contentVersionId > 1207494 OR
         cmContentVersion.modifiedDateTime > '2013-05-05 23:00:00.0' )
ORDER BY cmContentVersion.contentVersionId;

Explain extended gives:

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: cmContentVersion
         type: ref
possible_keys: PRIMARY,ixlanguageId,ixisActive,IX_DateTime
          key: ixisActive
      key_len: 1
          ref: const
         rows: 318270
     filtered: 100.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

And here are the indexes on this table:

+------------------+------------+--------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------
| Table            | Non_unique | Key_name     | Seq_in_index | Column_name      | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment
|------------------+------------+--------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+
| cmContentVersion |          0 | PRIMARY      |            1 | contentVersionId | A         |      636541 |     NULL | NULL   |      | BTREE      |         |
| cmContentVersion |          1 | contentId    |            1 | contentId        | A         |      127308 |     NULL | NULL   |      | BTREE      |         |
| cmContentVersion |          1 | ixlanguageId |            1 | languageId       | A         |           9 |     NULL | NULL   |      | BTREE      |         |
| cmContentVersion |          1 | ixstateId    |            1 | stateId          | A         |           6 |     NULL | NULL   |      | BTREE      |         |
| cmContentVersion |          1 | ixisActive   |            1 | isActive         | A         |           6 |     NULL | NULL   |      | BTREE      |         |
| cmContentVersion |          1 | compositeIDX |            1 | contentId        | A         |       63654 |     NULL | NULL   |      | BTREE      |         |
| cmContentVersion |          1 | compositeIDX |            2 | languageId       | A         |       79567 |     NULL | NULL   |      | BTREE      |         |
| cmContentVersion |          1 | compositeIDX |            3 | stateId          | A         |      159135 |     NULL | NULL   |      | BTREE      |         |
| cmContentVersion |          1 | compositeIDX |            4 | isActive         | A         |      212180 |     NULL | NULL   |      | BTREE      |         |
+------------------+------------+--------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+

EDIT:

new explain with the indexes suggested here

           id: 1
  select_type: SIMPLE
        table: cmContentVersion
         type: ref
possible_keys: PRIMARY,ixlanguageId,ixisActive,IX_foo,active_lang_contentversion_IX,active_lang_modified_IX
          key: ixlanguageId
      key_len: 4
          ref: const
         rows: 356055
     filtered: 100.00
        Extra: Using where


texts: Minimum configurations for mongodb replica set though a java program

texts:
I'm new to morphia. I'm using morphia and mongo-java-driver.jar to talk with replicaset(I need for cluster also) through a java program. I'm new to morphia.
Wrote a sample program code is below
    public static void createDBConnection() {
    try {

        List<ServerAddress> addrs = new ArrayList<ServerAddress>();
         addrs.add( new ServerAddress( "192.168.1.80" , 27017 ) );
         addrs.add( new ServerAddress( "192.168.1.81" , 27017 ) );
         addrs.add( new ServerAddress( "192.168.1.82" , 27017 ) );

        MorphiaObject.mongo = new MongoClient(addrs);

        ReplicaSetStatus status = MorphiaObject.mongo.getReplicaSetStatus();
        List<String> dbs = MorphiaObject.mongo.getDatabaseNames();

        MongoOptions mongOptions = MorphiaObject.mongo.getMongoOptions();
        MorphiaObject.mongo.setWriteConcern(WriteConcern.REPLICAS_SAFE);
        MorphiaObject.mongo.setReadPreference(ReadPreference.secondaryPreferred());
        System.out.println("Read prefrence"+ MorphiaObject.mongo.getReadPreference());
    } catch (UnknownHostException e) {
        e.printStackTrace();

    }
}

If I want to write data safely in all nodes what are steps need to do.
What are the minimum configurations that I need to set for replica?
What are validations need to do like replica set is down or alive, is readable, how can we manage if app or program can't find master?
Thanks in advance.


texts: why would command text not execute at all?

texts:
in my report i have::

<CommandText>SELECT

    column1,
        column2,
        'poop'
from mytable
</CommandText>

i am using sql server profiler to see exactly what statement is being set.

i have set only two filters:

databaseName

yet after running the report, no statement gets intercepted.

i suspect that because i am a beginner at SSRS, i am missing something crucial here.

for what reason would commandtext not be executed at all?

i did follow this question, to make sure that i am using sql profiler correctly, and indeed, i am: https://stackoverflow.com/questions/9107383/sql-server-profiler-capture-calls-to-your-databases-stored-procs-during-ssrs

another bit of important information is although the chart shows no data:

i actually am indeed showing data when i run the commandtext from ssms!


texts: Percona: Backup preparation doesn't work ("cannot open ./xtrabackup_logfile")

texts:
I've created a MySQL backup with PerconaXtrabackup tool via (thanks to this guide):

innobackupex --user=backupmysqluser --password=xyz --compress --stream=xbstream ./ > /var/backups/db/firstBackup.db

It succeeds creating the hot backup. Now I want to test the restoration of it, so then, first, I decompress it:

cd /var/backups/db
mkdir decompressed
xbstream -x -C decompressed

Now I try to prepare it:

innobackupex --apply-log /var/backups/db/decompressed/

And the result is this error:

160719 20:06:28 innobackupex: Starting the apply-log operation

IMPORTANT: Please check that the apply-log run completes successfully.
           At the end of a successful apply-log run innobackupex
           prints "completed OK!".

innobackupex version 2.4.3 based on MySQL server 5.7.11 Linux (x86_64) (revision id: 6a46905)
xtrabackup: cd to /var/backups/db/decompressed
xtrabackup: This target seems to be not prepared yet.
InnoDB: Number of pools: 1
InnoDB: Operating system error number 2 in a file operation.
InnoDB: The error means the system cannot find the path specified.
xtrabackup: Warning: cannot open ./xtrabackup_logfile. will try to find.
InnoDB: Operating system error number 2 in a file operation.
InnoDB: The error means the system cannot find the path specified.
  xtrabackup: Fatal error: cannot find ./xtrabackup_logfile.
xtrabackup: Error: xtrabackup_init_temp_log() failed.

It's strange because in the decompressed folder there's no xtrabackup_logfile, but there is a file named xtrabackup_logfile.qp instead.

What did I do wrong?


texts: Database design for logging varied types of data

texts:
I am currently in the process of moving our logging system from file-based to database-based, and I'm trying to come up with a suitable schema to fit. I'd like my application to be logged extensively, with dozens of possible event types (and some with sub-types) that I'd like logged. Each event I log will have different data logged with it, depending on the event type.

My end goal here is to be able to present the data on a website, and perhaps run some statistics on it later easier than traditional log files would allow. For context, my application is a gameserver and mostly involve logging ingame player actions.

So far, I've thought of a couple of approaches to doing this:

I could create tables for each event I want to log. This would be
fine if I were only logging a few events, but I  would rather not
have dozens of tables - one for logging each specific thing. The
advantage of this is that I don't have to worry about storing the
data associated with each event - each table would have specific
fields for the data I'm storing. I also imagine presenting this data
would be a challenge - probably involving JOINs across many tables.
A reference table of event_types and event_subtypes, and a
generic, single logs table for storing the actual log data. My concern
here is: how do I store the data associated with each event? This
data is different depending on what event is being logged. I thought
of storing this data as JSON inside a generic 'data' field, but that
would severely limit my ability to query the data later, so I'm out
of ideas. Another option is the EAV model which is new to me, but I
hear a lot of criticism of it so I'm wary.

A couple of examples of events I'll be logging, to further illustrate the context here:

A player sends a chat message. I'd like to store the player's account
ID and the message text.
An admin uses an admin action on the player. I'd like to store the
admin action type, the player's account ID, the admin's account
ID, and the reason.

My question is: what would be the best approach here given my requirements? I'm leaning towards the second option, but would value any input regarding alternative approaches or something I haven't considered.


texts: How to get disk statistics in the Postgres' Cloud SQL?

texts: We are having performance issues in our database probably connected to the disk performance (maximum read MB/s). Therefore, I want to see some statistics of the disks. However, there is no such statistic:


texts: ALTER DATABASE... SET COMPATIBILITY_LEVEL -- will this impact log shipping? Can it be done live?

texts:
SQL Server 2017 Standard Ed

I have a database that is at level 2008.

I want to bring it up to current.

This database log ships.

Is it safe to run:

ALTER DATABASE database_name
SET COMPATIBILITY_LEVEL = <whatever>

1- Live , when the app is running (I know the app is compatible with the new level)

2- Will this break or mess with log shipping?


texts: How to use function parameters in dynamic SQL with EXECUTE?

texts:
I have written a PL/pgSQL function in PostgreSQL 9.5. It compiles well but when I call it from pgAdmin3 it gives me an error. It seems like the dynamic query with columns to be replaced with the parameters passed in function is not working.

Below is my function:

CREATE OR REPLACE FUNCTION insertRecordsForNotification(username text, state     text, district text, organizationId text, bloodGroup text, status text,   approveRejectStatus text, emailSubject text, emailBody text, notificationStatus text) RETURNS boolean AS $$
DECLARE
id int;
r moyadev.user%rowtype;
_where text :=
  concat_ws(' AND '
    , CASE WHEN state IS NOT NULL THEN 'state = $2' END
    , CASE WHEN district IS NOT NULL THEN 'district = $3' END
    , CASE WHEN bloodGroup IS NOT NULL THEN 'bloodGroup = $5' END
    , CASE WHEN status IS NOT NULL THEN 'status = $6' END
    , CASE WHEN approveRejectStatus IS NOT NULL THEN 'approve_reject_status  = $7' END);
 _sql text := 'INSERT INTO moyadev.notification_email_details (id,     youth_enrollment_id, youth_email, email_subject, email_body, status, attempt,  sent_date, last_updated_by, last_updated) SELECT uuid_generate_v4(), id, email,  $8, $9, $10, null, null,$1, now() FROM moyadev.youth_enrollment';

 BEGIN

SELECT * into r FROM moyadev.user u where u.user_key=$1;

if (r.level='DISTRICT') then
_where := _where || ' AND ' || 'district=r.district' || ' AND ' ||      'state=r.state' || ' AND ' || 'fk_id=r.fk_id';
elseif (r.level='STATE') then
_where := _where || ' AND ' || 'state=r.state' || ' AND ' ||   'fk_id=r.fk_id';
 elseif (r.level='NATIONAL') then
 _where := _where || ' AND ' || 'fk_id=r.fk_id';
 elseif (r.level='UNIT') then
_ where := _where || ' AND ' || 'district=r.district' || ' AND ' ||    'state=r.state' || ' AND ' || 'fk_id=r.fk_id';
end if;

IF _where <> '' THEN
_sql := _sql || ' WHERE ' || _where;
EXECUTE format(_sql);
END IF;

raise notice 'sql: %', _sql;

RETURN 'TRUE';

END;
$$ LANGUAGE PLPGSQL;

It compiles well but gives below error when I call it using below command:

select insertRecordsForNotification('nssnationalappr@mailinator.com',null,null,null,null,'ACTIVE','APPROVED','test email','test email','PENDING');

ERROR: there is no parameter $8
SQL state: 42P02
Context: PL/pgSQL function insertrecordsfornotification(text,text,text,text,text,text,text,text,text,text) line 39 at EXECUTE

How to use parameter values properly?


texts: SQL Server 2012 AlwaysOn Availability Group active/passive

texts:
I have one Primary and one secondary replica on WSFC and AAG configuration. Both nodes are physical machines and sync with automatic fail over.

The secondary replica is readable and we run maintenance job on it but now we want to keep the "Readable Secondary" setting to "NO" instead of "Readable" and move the maintenance jobs to Primary replica so the secondary replica becomes pure passive node instead of active which saves licensing cost.

My question is - Does changing the readable secondary replica setting to "NO" will make it passive node please?


texts:
I have a table of entities. For example purposes, lets call them Vehicles: Car, Boat, Motercycle, etc.

I'd ideally like to place all entities in a single Vehicles table because a lot of related tables (VehicleRatings, VehicleComments, VehicleHistory, etc) are going to apply to any entity, not just one of them.

But some of the entities have a few individual properties that aren't shared with the other entity types. Right now, there is only 1 extra property for one entity, and 2 for another. I expect there might be a few more that get identified later on, but not many overall.

How do I know when it's better to create a child table for each entity type to store these individual properties, as opposed to just adding an extra column in the parent Vehicles table? Is there any questions I can ask myself to help determine this answer?

I am looking to optimize for query performance primarily, followed by ease of maintenance.


texts: Howto recover SQL 2016 cluster instance with bad tempdb path setting?

texts:
Today I have changed the tempdb's file locations using the ALTER DATABASE MODIFY command and I have accidentaly set it to use the root of the volume. The SQL Server service failed to restart and came up with Access denied errors in eventlog. It is a 2016 Sp1 failover cluster.
I tried to workaround this using

NET START MSSQL$INST1 /f /T3608

but it does not work.

What can I do about this? How can I restart the service to get able to re-define the setting to use a subfolder.


texts:
I need to write an export module which marks exported rows as exported.
We currently run SQL Server Express 2012, but may switch to Oracle, so I need
a client-side approach. We are currently using ASP.NET 4.5.

I have a boolean field named "exported" which is supposed to be set to true.

My idea was to collect all affected rows IDs in an array in the client software and build something like

Metacode:

FOREACH(affectedRowsIDs as affectedRowID)
  $sql = "udpate myRows SET exported = 1 where id = $affectedRowID)
  execute $sql

The problem would be a lot of queries to run.

Different thought:

FOREACH(affectedRowsIDs as affectedRowID)
  $affRows += " , $affectedRowID"

$sql = "udpate myRows SET exported = 1 where id IN ($affRows)"

Could you please give me some feedback how you would do it?


texts: Log shipping restore speed & index rebuild vs reorganize

texts: Does the log shipping server restore a transaction log backup faster if the index on primary server are rebuilt or reorganized? On the log shipping server the restores are fast in phase one but seem to take a long time in the redo & undo phases. Like a tlog backup that took a few minutes on the primary is taking 1-4 hours to restore. I am using Ola Hallogren's routines on the primary server with the default 5% reorg & 30% rebuild levels but I'm wondering if switching to only rebuild and increasing the % will help speed up the restores. I'm not really concerned with how long the defrag takes on the primary but how fast they can be restored on the secondary. Does bulk-logged help vs full recovery?


texts: Geographical replication zones in MySQL Cluster?

texts:
In a default installation, data is shared indiscriminately across all data nodes.

Is it possible to specify that certain tables can only be replicated across a specific group of data nodes?

If so, do the tables need to be created on those nodes, or can they be migrated from other nodes?


texts: PostgreSQL cannot start after replacing contents inside its Data folder

texts:
When recovering the Postgresql database from a hacked Ubuntu 12.04 server, I copied the data directory to another location, reinstalled the OS and PostgreSQL, stopped PostgreSQL service, removed the contents in the data directory and copied back the contents from the previous installation.

Problem: PostgreSQL will not start now, showing the errors below. Why is this happening, and how can we fix the problem?

service postgresql restart
* Restarting PostgreSQL 9.1 database server

* Error: could not exec /usr/lib/postgresql/9.1/bin/pg_ctl /usr/lib/postgresql/9.1/bin/pg_ctl start -D /var/lib/postgresql/9.1/main -l /var/log/postgresql/postgresql-9.1-main.log -s -o  -c config_file="/etc/postgresql/9.1/main/postgresql.conf" :

Update

Restarting PostgreSQL service now gives the error:

* Restarting PostgreSQL 9.1 database server
* The PostgreSQL server failed to start. Please check the log output:
2013-04-16 01:52:10 EDT PANIC:  could not open control file "global/pg_control": Permission denied


texts:
I am having an issue with filestream and accessing the files. From the server itself I can access the files in the filestream folder when browsing direct on that computer. However when I try to browse the filestream UNC path on that server using localhost or from another computer it says incorrect function / don't have permission to view the share.

This was working fine, then Microsoft updates happened. We have since even upgraded to SQL 2017 and it is still not working. I have ensured that the owner of the folder is set to the same account as the service start up account. The permissions are set to allow me full control over that folder so I should be able to access it as well. Everything I have found online I have tried to no avail. Does anyone have any ideas?


texts: How to find the detail cpu high process name which occupying most resource in the SQL Server database?

texts:
I'm running into a problem when using the following SQL to identify CPU usage in the production SQL Server 2005 env.

DECLARE @ts_now bigint;
SELECT @ts_now = cpu_ticks / CONVERT(float, cpu_ticks_in_ms) FROM sys.dm_os_sys_info

SELECT TOP(10) SQLProcessUtilization AS [SQL Server Process CPU Utilization],
               SystemIdle AS [System Idle Process],
               100 - SystemIdle - SQLProcessUtilization AS [Other Process CPU Utilization],
               DATEADD(ms, -1 * (@ts_now - [timestamp]), GETDATE()) AS [Event Time]
FROM (
      SELECT record.value('(./Record/@id)[1]', 'int') AS record_id,
            record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int')
            AS [SystemIdle],
            record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]',
            'int')
            AS [SQLProcessUtilization], [timestamp]
      FROM (
            SELECT [timestamp], CONVERT(xml, record) AS [record]
            FROM sys.dm_os_ring_buffers
            WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
            AND record LIKE '%<SystemHealth>%') AS x
      ) AS y
ORDER BY record_id DESC;

Which shows the other CPU utilization is high in the dedicated SQL Server 2005 env.

SQL_Server_Process_CPU_Utilization  System_Idle_Process Other_Process_CPU_Utilization   Event_Time
1   79  20  11/18/2011 5:07
0   80  20  11/18/2011 5:06
1   77  22  11/18/2011 5:05
1   79  20  11/18/2011 5:04
1   78  21  11/18/2011 5:03
0   79  21  11/18/2011 5:02
0   79  21  11/18/2011 5:01
0   80  20  11/18/2011 5:00
0   80  20  11/18/2011 4:59
0   79  21  11/18/2011 4:58
1   78  21  11/18/2011 4:57
0   79  21  11/18/2011 4:56
0   80  20  11/18/2011 4:55
0   82  18  11/18/2011 4:54
3   77  20  11/18/2011 4:53
2   79  19  11/18/2011 4:52
0   79  21  11/18/2011 4:51
0   79  21  11/18/2011 4:50

I find the Other_Process_CPU_Utilization eat our db resource sometime and how can I get the detail process name for these kind of lists above through DMV? We can't login the SQL Server OS env directly so only DMV could tell us these information.


texts: What is a staging table and how do you protect data loss when transferring the data?

texts:
What is a staging table? What is it used for? Are there best practices that should be applied? I'm working in Oracle right now. Are staging tables platform dependent? I'm performing a bulk copy with Devart's OracleLoader C# class. To my knowledge you can't perform this type of operation on a table with enabled triggers(used to add a sequenced primary key).

Another question related to staging tables. How do you protect data loss when transferring data from a staging table to it's permanent home(permanent table?)

I believe I have a basic understanding of what a staging table is. From my understanding it is just a location to temporarily store data before moving that data to its permanent home.


texts: Curious Database building Problem (ER Diagram)

texts:
I am trying to build an EER Model for a Autostore that has 5 locations and offers a range of auto products. They offer car repairs and roadworthy tests as a service also.

I need to be able to make fortnightly reports on unfinished service jobs, and fortnightly reports on the sales. They have a wide customer database filled with full addresses.

There is a constant inflow of new stock items and restocking of old ones. There should also be a way to know the cost of each item in stock and where its being held.

I swear I've researched it enough to be able to understand it by now but Im really struggling to map this out as I'm constantly running into a wall when dealing with the products that are being restocked, sold and stocked by particular stores in different locations.

I'm a total rookie with this kind of thing but if anyone can help me it would be amazing.


texts: Hinting to Redshift how to distribute a join?

texts:
If I am joining between two tables, A and B, with multiple equality constraints, is there any way that I can hint to Redshift which it should use for distributing the join? For example, in:

FROM A
JOIN B
  ON  B.id = A.b_id
  AND B.x = some_expression_of(A.x)

In my situation, I know that all the data is already distributed by this X expression, so that if Redshift distributes by it, there will not be any actual network activity.

Edit: Here's a more representative version of the join:

FROM order_products --distribution key: user_id
FULL OUTER JOIN order_payments --distribution key: user_id
   ON FALSE
LEFT JOIN orders --distribution key: user_id
   ON  orders.id = COALESCE(order_products.order_id, order_payments.order_id)
   AND orders.user_id = COALESCE(order_products.user_id, order_payments.user_id)

What I'd like is to just be able to tell Redshift to please distribute the last join based on user_id (which Redshift might categorize as a  DS_DIST_BOTH, but should actually result in no network redistribution)


texts: How to use mysql to migrate database changes from local databases?

texts:
I am working on a project with a few people and we have got the point where we all have different local versions of our website database and wish to get these changes to the server, after searching around I found something I liked but have a few questions on my approach to keeping our local databases in sync...

So, as we are all using phpMyAdmin, when we want to push changes we would export our local databases with phpMyAdmin and check our .sql files from phpMyAdmin into our git repo under a folder called schema... I already have some git web hooks setup, so I was basically thinking of running the command before inside of my deploy script as whenever we push changes to our dev branch or master on git hub a deploy script runs on the server...

Now, the idea is it would use the exported .sql file from the schema folder and import the new database schema, is this approach ok? What would happen if a column was removed or a length was changed in the structure of the database, would this cause errors? I followed this way because I did not want anything too complicated...

mysql -u <username> -p<PlainPassword> <databasename> < <filename.sql>

or is there a better way to do this? My tech stack is LAMP and I am using a local LAMP server to develop.


texts:
I have the following structure with a variable number of elements on the 2nd level under root with unknown names:

<root>
   text a
   text b
</root>

I need a query that delivers the text of a specific element in the 2nd level. the name of the required element is the argument passed in by a user.

I already succeeded in finding it for example for element "A", querying the XML document stored in variable @x:

declare @str varchar(1000) = '<root>text atext b</root>'
declare @x xml = convert(xml, @str)
SELECT xLangStruct.x.value('(A/text())[1]', 'varchar(100)') from @x.nodes('//root') as xLangStruct(x)

This works for "A", but now I need to replace "A" with a variable or any kind of filtering... So I need anything like that:

SELECT xLangStruct.x.value('(@SEARCHEDELEMENT/text())[1]', 'varchar(100)') from @x.nodes('//root') as xLangStruct(x)

or

SELECT xLangStruct.x.value('(./text())[1]', 'varchar(100)') from @x.nodes('//root') as xLangStruct(x) WHERE xLangStruct.x.Elementname = @SEARCHEDELEMENT


texts: pgAdmin 4 - Can't Create Script on a database

texts:
When I try to CREATE Script on a database I want to use, there is no CREATE Script item in the context menu.

But CREATE Script looks fine on another database. I can select it and it works fine.

Similarly, CREATE Script in the Object menu looks fine when I'm on the other database, but is disabled when I'm on the one that I want.

Is this just some permissions that I don't have on the main database?

My user role is:

CREATE USER [redacted] WITH
  LOGIN
  NOSUPERUSER
  INHERIT
  NOCREATEDB
  NOCREATEROLE
  NOREPLICATION;

GRANT [redacted], [redacted] TO [redacted];

I'm using pgAdmin 4 version 1.6 on Mac OS X El Capitan v 10.11.6.


texts: Is it good or bad practice to have a fact table relate to the same dimension table multiple times?

texts:
I'm new to DW/BI, and this seems like a pretty basic question, but I can't seem to find a good answer.

I have a fact table that would have more than one date associated with it, so I was thinking that I would have multiple date keys back to the same dimension table, e.g., SubmittedDateKey, ClosedDateKey. Obviously SQL Server can do this - my reservation in this is that when I designed a proof-of-concept schema this way, the client tools (i.e., Tableau, Excel), seemed to get confused about how to map the relationships. (I also found  this forum post saying that Oracle's Analytic Workspace Manager explicitly forbids this, which of course implies nothing about my Microsoft world, but suggests that it's a general no-no.)

Is there a best practice in DW/BI about this? Instead of having one dimension table hooked up to each of these keys, should I have multiple identical dimension tables, e.g., SubmittedDateDimension, ClosedDateDimension?

Thanks!


texts: Date format incompatibility in linked server between SQL Server and Oracle

texts:
When I select data from Oracle to SQL Server via linked server I get exception

  SELECT CreationDate  FROM OPENQUERY (ORCLS, 'Select CreationDate from
  ADMIN.CV_TMP_Subscrs_Stage4_List')

  Msg 8114, Level 16, State 8, Line 7 Error converting data type
  DBTYPE_DBTIMESTAMP to datetime.

If I cast date field in Oracle to varchar, reconvert it to datetime in sql server and fetch data I see

As far as I understand, Oracle formats date as "dd.MM.yyyy".  In order to SQL Server to understand that I need to:

Convert all date/datetime fields in queries to varchar
Reconvert varchar to datetime in sql server side with specific format.

Seems like shooting cannon at sparrows. Maybe there is an easier way?


texts: Paged query against timestamp populated by trigger missing items

texts:
We're having a strange problem with a PostgreSQL 9.2 database that I've been racking my brain trying to figure out.

First some background. We have a "tombstone" table that is populated using a trigger when a row is deleted from a particular table. The trigger does this:

INSERT INTO tombstone (id, delete_timestamp)
VALUES (OLD.id, statement_timestamp() AT TIME ZONE 'UTC');

There is a background service that runs every 30 seconds and checks for items in the tombstone table. The query used by the background service is like this:

SELECT
    id,
    delete_timestamp
FROM
    tombstone
WHERE
    delete_timestamp > @sinceUTC
    OR
    (delete_timestamp = @sinceUTC AND id > @id)
ORDER BY
    delete_timestamp asc, id asc
LIMIT @count;

This query is constructed with two goals: to find new tombstone entries since the last time it was run, and to page through the items if there is a large number. The last timestamp and ID are recorded after each execution of the query to use as the inputs to the next execution of the query. If no results are returned, the last timestamp and ID are not updated.

The problem we are having is this: every once in a while, the background service misses a large number of items in the tombstone table. Recently we had a 10-minute time span where the background service did not pick up any new tombstone items even though there were more than 200 created during that time. The logs for the service show it querying every 30 seconds during that time but finding no items.

I've come up with one scenario that could account for missing items, but it should only miss a few in a very small time window. The scenario I've identified is as follows:

D1 |st1....................|
D2       |st2......|
R                  |.......|

Consider two delete transactions, D1 and D2. D1 starts first. D2 starts and completes while D1 is still in progress. Since the trigger uses statement_timestamp, it will record a the start of D1 as ST1 and the start of D2 as ST2.

If a read (R) starts in the time between the commit of D2 and the commit of D1, then it will see D2 but not D1. It will record the "last timestamp" as ST2, but this is actually after ST1. So the next time the query executes, it will start at a time after ST1 and therefore will miss D1.

As I said, I can see this causing a few occasional misses, but not 10 minutes worth. Also, the missed items were in multiple transactions, not a single large transaction, so the likelihood of this accounting for what we are seeing seems even smaller.

Any ideas on what could cause this issue?


texts: Identifying table causing runaway rollback in MySQL 5.5 (InnoDB / Windows)

texts:
I have a large database (180 GB), running under Windows, with a runaway rollback that starts running as soon as the database is started up.

I have started the database with innodb_force_recovery=3 to prevent the rollback from running while I try to fix the problem.  As I understand it from other questions on StackExchange, the correct thing to do is to drop the table causing the rollback and restore it from a backup.  All that would be no problem. However, I am unable to identify the table triggering the rollback. SHOW ENGINE INNODB STATUS is no help -- it just says that there is a rollback. I am running mysqlcheck over all the tables, but everything is so far showing up "OK".

Is there any way to determine what table(s) are causing the rollback?


texts: How to pass variable to PL/pgSQL code from the command line?

texts:
I am running a psql script from the command line with variables something like:

psql ...... -v now_utc=$NOW_UTC

Then I want to use this in my script like:

$$
DECLARE
   _now_date timestamp := :now_utc;
BEGIN
  -- do something
END
$$

But I got an error like:

syntax error at or near ':'

The script is fine once I change from :now_utc to now() and it works like a charm.

The question is how I can pass a variable from the command line to PL/pgSQL code?


texts: MySQL:How to correct a corrupted MySQL binary log on Master server?

texts:
We are using MySQL DB with one master and one slave. We are using this setup for months now. The slave sync stopped today and the error we got was:

Last_IO_Errno: 1236
            Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'log event entry exceeded max_allowed_packet; Increase max_allowed_packet on master'
           Last_SQL_Errno: 1594
           Last_SQL_Error: Relay log read failure: Could not parse relay log event entry. The possible reasons are: the master's binary log is corrupted (you can check this by running 'mysqlbinlog' on the binary log), the slave's relay log is corrupted (you can check this by running 'mysqlbinlog' on the relay log), a network problem, or a bug in the master's or slave's MySQL code. If you want to check the master's binary log or slave's relay log, you will be able to know their names by issuing 'SHOW SLAVE STATUS' on this slave.

We tried restarting slave's mysql service and start and stop the slave replication with no help. After further investigation we found that there was an issue with particularly big size of query. In the error log of the master server we got error log like below:

[ERROR] Error in Log_event::read_log_event(): 'Event too big', data_len: 1936941420, event_type: 109

We have identified the query that caused this issue. We can ignore the update from that query. This error is logged constantly in the mysql error log.

The issue is, the master is not able to read the from the relay log after that perticular log position. We just want to remove that particular relay log number from the binary log of the master server. How can we remove a particular relay log entry from the binary log file? And yes, we have a critical situation with the application due to this.:(


texts:
I've got a PSA mongodb which I'm restoring starting from a backup made on a standalone installation.
I set the PRIMARY machine in the --host parameter but the replication does not start on the SECONDARY host.
What is the problem? Is there a way to manually start replication? I've checked the rs.status() but everything seems to be ok.


texts: When should we index a key K1 when a composite index already exists with K1 placed first?

texts:
If a table already has an INDEX (K1, K2, ...), when is it a good idea to create an INDEX(K1)?

More generally, when INDEX (K1, K2, K3, ...) is present, under what circumstances is it a good idead to create one or several of  INDEX (K1), INDEX (K1, K2), aso. ?


texts: Restoring a backup of 1 database to another puts both databases into Restoring mode

texts:
We have a 2 databases-

Staging
Test

At times, we refresh out Test environment with a copy of Staging. We do this by taking a full backup of the Staging database and restoring it over the Test database.

When we do this, both databases go into Restoring... mode and only the Test databases comes back online after the restore.. the Staging database shows Restoring... until we issue a RESTORE DATABASE Staging WITH RECOVERY.

Why is this and what do we need to change about our process to prevent this happening?
The backup and restore event log on the Staging database doesn't show any evidence of a restore on the database.

I'm thinking it's something to do with Logical File Names. When we do the restore, we change the .dbf and .ldf files to point to the TEST files, however the logical files are still pointing to STAGING, something we can't change through SSMS during a restore.

The restore command that SSMS is running is:

USE [master]
BACKUP LOG [Chris21_STAGING] TO  DISK = N'F:\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\Chris21_STAGING_LogBackup_2016-06-24_11-12-03.bak' WITH NOFORMAT, NOINIT,  NAME = N'Chris21_STAGING_LogBackup_2016-06-24_11-12-03', NOSKIP, NOREWIND, NOUNLOAD,  NORECOVERY ,  STATS = 5
ALTER DATABASE [Chris21_TEST] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
RESTORE DATABASE [Chris21_TEST] FROM  DISK = N'C:\Chris21_STAGING Backup 20160624.bak' WITH  FILE = 1,  MOVE N'Chris21_STAGING' TO N'F:\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\Chris21_TEST.mdf',  MOVE N'Chris21_STAGING_log' TO N'F:\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\Chris21_TEST_log.ldf',  NOUNLOAD,  REPLACE,  STATS = 5
ALTER DATABASE [Chris21_TEST] SET MULTI_USER


texts: Primary key in resulting relation from union operation in relational algebra

texts:
I have a question regarding the resulting table/relation from a union operation in relational algebra. Does the resulting relation not have a primary key? Or does it return a table with a hidden primary key?

Example: Suppose we take the union of the following two tables:

R1:
id | name
----------
1    a
2    b
3    c

R2:
id | name
----------
1    b
2    c
3    d

Where the attribute "id" is primary key in both relation R1 and R2, now the union of those two will be:

id | name
------------
1     a
1     b
2     b
2     c
3     c
3     d

Now none of the attributes in the resulting relation can be primary key since both id and name contain duplicates. How is this resolved by the DBMS?


texts:
I have created the following as an automated job to run at midnight on every database server I manage:

This job fails intermittently on some servers, but not others and I get the following error:

  SQL Agent - Jobs Failed: The SQL Agent Job
  "ucjSQLerrorLogNightlyCycle" failed at Wednesday, November 22, 2017
  12:00:00 AM with the error "The job failed. The Job was invoked by
  Schedule 9 (NightlySQLError). The last step to run was step 1
  (SQLErrorLogCycle). - Executed as user: MyDomain\MyServiceAccount. SQLServerAgent
  Error: The process cannot access the file because it is being used by
  another process. [SQLSTATE 42000] (Error 22022). NOTE: The step was
  retried the requested number of times (3) without succeeding. The step
  failed."

I receive the following error when I execute EXEC dbo.sp_cycle_agent_errorlog; in the query analyzer (which wasn't happening before):

  Msg 22022, Level 16, State 1, Line 0 SQLServerAgent Error: The process
  cannot access the file because it is being used by another process.

I have performed an intensive search on this error message and have not found much of anything.  The only gem I found was to use Process Explorer from the SysInternals suite by Mark Russinovich.  However, isolating the SQLAgent.exe binary did not show me what other process is using it; the process is lone.  I am either performing the incorrect steps or my process is completely wrong.

Other items to note:

I have verified that another process isn't running at the same time
I have ran the job in the middle of the day on a weekend and it will keep running until it fails
I can copy and paste the code into the SSMS or Toad and the code will execute as expected
I submitted a ticket to my Infrastructure department to make sure there is an exclusion in the antivirus log folder that SQL Server Agent manipulates.
I disabled the antivirus and performed the same aforementioned steps with the same result
Broke the job down into two different steps as Aaron suggested.  The code EXEC sp_cycle_errorlog; works inside of a job and in the query analyzer.  The code EXEC dbo.sp_cycle_agent_errorlog; does NOT work in a job OR in the query analyzer.
Added the path of the SQL Server Agent log file path to the list of exclusions in our antivirus and it did not solve the problem.
Restarting the SQL Server Agent allowed me to run the job successfully, but I don't consider this a "fix"


texts: I want to enable auditing on some column in a table. I have tested the SYS, Schema and Table level auditing. Guide me or refer me some document to enable auditing on column level.


texts: Is it possible than two simple updates on same table interlocks (deadlock)?

texts:
Table A is:
id integer
version varchar
data jsonb (large data 1mb)
fkToBid integer (references B.id constraint)

Table B is:
id integer
other...

Processes are aggressively running the two updates below, in any order and outside any transaction.
Updated records on table A refers sometimes to the same record on table B. Also, sometimes the same A records is updated.
UPDATE A.version WHERE A.id=:id
and
UPDATE A.data WHERE A.id=:id

Why/Does this can interlock because updated records in table A refers to the same row in table B?
Can this interlock for another reason?
Why do I see an AccessShareLock on the B pk index for these update requests?


texts: I'm working on a some Create Table statements for a project I have.  I have two columns (StartDate, EndDate) where the data type is Timestamp.  One of the requirements is to ensure that the date is fixed (January 1, 2000) but leave the time variable.  How would I go about including that in my Create Table statement?


texts: I know I can use the performance counters to track Resource Governor CPU and Memory Usage.  Is it possibly to track the I/O usage for each work load group?  I haven't seen anything specific or any recommended practices.


texts:
I am rewriting Sybase view to postgresql and encountered such problem.
Is there any way to improve performance of view with functions inside?
For example we have complex function

create function func1(pid integer)
returns char(20)
as $$
begin
... do stuf
return 'aaaa';
end; $$ language plpgsql;

And create view with function

create view view_client
as
select code, name, func1(pid) from client;

Such view performs terrible slow, unless "func1" is removed from it. Postgresql probalby executes function for every row even if column is not used?
count(*) from view with 2M rows takes 2+ minutes, without "func1" it takes ~10sec.


texts: Oracle using sub-optimal index on bind variables

texts:
Let's say you have a table, called ORDERS, which contains ORDER_NUMBER, CUSTOMER_ID, and ORDER_DATE (among other fields).  And you have one index on ORDER_NUMBER, another on CUSTOMER_ID, and third index on ORDER_DATE.

The following query is intended to select all orders that are older than the current order, from the same customer:

select ORDER_NUMBER from ORDERS
    where CUSTOMER_ID = :BIND_VAIABLE_1
    and ORDER_DATE <= :BIND_VARIABLE_2
    and ORDER_NUMBER != :BIND_VARIABLE_3

In this case, :BIND_VARIABLE_2 would be a recent date, usually today's date, from a current order.  The problem is, that Oracle seems to want to use the index on ORDER_DATE, which selects almost the entire table, then filters on CUSTOMER_ID,  whereas it is much more efficient to use the index on CUSTOMER_ID instead (since there are only a few orders per customer).

The thing is, if you do a query without bind variables, it will always use CUSTOMER_ID like it should.  But with bind variables, Oracle creates a query plan assuming that ORDER_DATE will be some random value, so ends up using the less efficient index.

How can I get Oracle to use the correct index in the presence of bind variables, without using index hints (since I can't modify the app)?  I've already tried re-computing statistics, using histograms, etc. but without much luck.  Ideally I'd like to turn off bind-variable optimization altogether, and have Oracle re-formulate a query plan based on the actual contents of the bind variables.

Edit: the purpose of this query is to find all previous orders by this particular customer, so BIND_VARIALBE_1 is the current customer, BIND_VARIABLE_2 would be the date of the current order, and BIND_VARIABLE_3 is the current order number.


texts: postgresql set variable not working with command option

texts:
Has anyone a solution for this issue (tested on postgres v12)

I would like to use the psql variable option in conjunction with command like this:

psql -v env=test -c "SELECT :'env';"

but this will fail

on the contrary this one won't echo "SELECT :'env';" |psql -v env=test

Has anyone an idea about how to solve this or do you think this is a bug ?
Thanks for your insights


texts:
I have noticed that using the same SQL*Plus client to connect to different Oracle database result in different outputs for the same SQL query.

Database 1:

C:\Oracle\instantclient_10_2>sqlplus user/passwd@'(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=host1)(PORT=1521))(CONNECT_DATA=(SID=SID1)))'

SQL*Plus: Release 10.2.0.5.0 - Production on Fri Aug 23 11:54:45 2013

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> select 'XXXX ',to_char(UniqueSequenceNumber,'xxxxxxxxxxxxxx') from mytable where rownum < 2;

'XXXX TO_CHAR(UNIQUES
----- ---------------
XXXX          300f483

Here the output is on one line, the headers are truncated.

Same thing on an other database:

C:\Oracle\instantclient_10_2>sqlplus user/passwd@'(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=host2)(PORT=1521))(CONNECT_DATA=(SID=SID2)))'

SQL*Plus: Release 10.2.0.5.0 - Production on Fri Aug 23 11:58:15 2013

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, Data Mining and Real Application Testing options

SQL> select 'XXXX ',to_char(UniqueSequenceNumber,'xxxxxxxxxxxxxx') from mytable where rownum < 2;

'XXXX'
--------------------------------
TO_CHAR(UNIQUESEQUENCENUMBER,'XXXXXXXXXXXXXX')
--------------------------------------------------------------------------------
XXXX
        3a1393b

Here the output is split on two lines.

I am not looking for SQL*Plus formatting options like linesize, column format, etc. that just impact the current session.
What I need to understand is what is making the output different even when I don't specify any options.

Is there some default settings on the server side?
Is it depending on the Oracle server version (10.2.0.1 in one case, 10.2.0.5 in the other)?

Note: column UniqueSequenceNumber is of type NUMBER

Note 2: Tried on a Server version 10.2.0.3, same result as on 10.2.0.1 (one liner)

In reply to miracle173 :

C:\Oracle\instantclient_10_2>sqlplus /nolog

SQL*Plus: Release 10.2.0.5.0 - Production on Mon Aug 26 09:32:02 2013

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

SQL> connect user/passwd@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=host1)(PORT=1521))(CONNECT_DATA=(SID=SID1)))
Connected.
SQL> select 'xxxx' from dual;

'XXX
----
xxxx

SQL> connect user/passwd@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=host2)(PORT=1521))(CONNECT_DATA=(SID=SID2)))
Connected.
SQL> select 'xxxx' from dual;

'XXXX'
--------------------------------
xxxx

SQL> connect user/passwd@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=host1)(PORT=1521))(CONNECT_DATA=(SID=SID1)))
Connected.
SQL> select 'xxxx' from dual;

'XXX
----
xxxx

SQL> connect user/passwd@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=host2)(PORT=1521))(CONNECT_DATA=(SID=SID2)))
Connected.
SQL> select 'xxxx' from dual;

'XXXX'
--------------------------------
xxxx


texts: How to change the firing order of Triggers?

texts:
Realy I rarely use triggers. So I met a problem at first time. I have a lot of tables with triggers (2 or more for every table). I would like to know and change the order of firing triggers for every table. Is it possible to get this information?

ADDED:

Here is a good enoght article on mssqltips I have found.


texts: I have to update all the rows of a Column in a table. Here is my query

texts:
So My tables are

People:

People_name(VARCHAR 100),
People_id (INT, PRIMARY KEY AUTO INCREMENT)

Tracks:

Track_id(INT PRIMARY KEY),
People_id_references (int ,Foreign key references People(people_id)),
Track_title(VARCHAR 100)

top100:

artist varchar(100),
id(int, primary key),
track_title(varchar100)

All the columns in above tables are having values, except in people_id_references(this is in tracks table). top100 has many other columns, but for the sake of clarity I did not mention them, and it is no way connected to people or tracks tables. The data from top100 is present in track and people tables, this is done using some insert queries. I want to import values from people_id (it is in People table).

So my query is:

UPDATE tracks
SET People_id_Reference = (SELECT People_id
                           FROM People
                           RIGHT JOIN top100
                           ON
                           People_name=top100.artist LIMIT 1)
                           WHERE People_id_Reference IS NULL;

But that inserts same people_id into all rows of People_id_Reference. Is there any other way?


texts:
I am developing a simple course suggestion application. which suggest a degree course to students based on their diploma result.

here are my tables in my database.

+-----------------------------+
| Tables_in_available_courses |
+-----------------------------+
| course                      |
| course_requirements         |
| qualification               |
| result                      |
| student                     |
| student_result              |
+-----------------------------+

A student have multiple qualifications and a course also have multiple qualifications requirements.

This system should print all suggested courses to every student in the database according to their result.

eg: my results are
Diploma in computing - A
Diploma in management - B
Diploma in software engineering - C

and Computer science degree course's pre-requests are
Diploma in computing - A
Diploma in software engineering - C

So the system should suggest to me Computer Science

    mysql> show columns from student_result;
+------------------+---------+------+-----+---------+----------------+
| Field            | Type    | Null | Key | Default | Extra          |
+------------------+---------+------+-----+---------+----------------+
| id               | int(11) | NO   | PRI | NULL    | auto_increment |
| qualification_id | int(11) | YES  | MUL | NULL    |                |
| result_id        | int(11) | YES  | MUL | NULL    |                |
| student_id       | int(11) | YES  | MUL | NULL    |                |
+------------------+---------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

mysql> show columns from course_requirements;
+------------------+---------+------+-----+---------+----------------+
| Field            | Type    | Null | Key | Default | Extra          |
+------------------+---------+------+-----+---------+----------------+
| id               | int(11) | NO   | PRI | NULL    | auto_increment |
| course_id        | int(11) | NO   | MUL | NULL    |                |
| qualification_id | int(11) | YES  | MUL | NULL    |                |
| result_id        | int(11) | YES  | MUL | NULL    |                |
+------------------+---------+------+-----+---------+----------------+

How to implement it?


texts: Make two different updates with two different where conditions

texts:
Although this question is very similar, I don't think that it is also the best approach for my case, though it seemed to work. Consider the following example:
CREATE TABLE messages (
  writer VARCHAR(5),
  recipient VARCHAR(5),
  show_for_writer TINYINT(1), -- 0 for hidden, 1 for display
  show_for_recipient TINYINT(1) -- idem
);

I now want to create ONE update query which hides all messages of user x for him; i.e. which sets show_for_writer equal to 0 where the writer is x, AND which sets show_for_recipient equal to 0 where the recipient is x. Note that I want to make this as efficiently as possible, as this table may contain millions of records (one per message) for countless writer-recipient combinations (composite, non-unique index).
What I've tried to make, according to the linked post, is:
UPDATE messages
SET show_for_writer =
    CASE
    WHEN writer = 'x'
    THEN 0
    ELSE show_for_writer -- or even better, do nothing; only update if necessary
    END
    ,
    show_for_recipient =
    CASE WHEN recipient = 'x'
    THEN 0
    ELSE display_receiver -- or even better, do nothing; only update if necessary
    END
WHERE writer = 'x' OR recipient = 'x';

I however feel that this is not ideal AT ALL, because it goes through all the records of the table, and this query may not be done at a regular scale, but not rarely either. What's the best solution for this? I'm open for any solution, even structural column modification propositions; I'm using MariaDB 10.3.
An Idea I had was maybe to edit the structure as follows:
CREATE TABLE messages (
 participants VARCHAR(10) FULLTEXT,
 writer VARCHAR(5) NOT NULL,
 recipient VARCHAR(5) NOT NULL,
 show JSON NOT NULL
);

And do something like:
UPDATE messages
SET show = JSON_REPLACE(messages,'$.x',0)
WHERE MATCH(participants) AGAINST('x');

The problem here is that I query messages if they shall be shown for 'x' upon init. Hence, setting show to a JSON data type won't make it possible to index this column, hence to query in f(show) efficiently. That's why I thought this idea may not be ideal, but just to show you that I'm basically open even to modifications at the structural level.
Otherwise I could simply fire two subsequent UPDATE statements, like so:
UPDATE messages
SET show_for_writer = 0
WHERE writer = 'x';

UPDATE messages
SET show_for_recipient = 0
WHERE recipient = 'x';

But I really prefer to stick to using one query per action. Help please!
Check the example here, with the update query to be otpimized to avoid going over all records of the table:
http://www.sqlfiddle.com/#!9/f6830d/1


texts: Restoring Database when system can't find filepath specified

texts:
I have a database backup file stored in a hard drive V: named someFile.bak. The file path is simply V:\someFile.bak. I have been trying to load this into SQL Server Management Studio via the command

RESTORE DATABASE someDatabase
FROM DISK = 'V:\someFile.bak'

but I keep getting the error:

Msg 3201, Level 16, State 2, Line 4
Cannot open backup device 'V:\someFile.bak'. Operating system error 3(The system cannot find the path specified.).
Msg 3013, Level 16, State 1, Line 4
RESTORE DATABASE is terminating abnormally.

This suggests to me that SSMS is having issues finding the file path, despite it being specified correctly. Does this have anything to do with the fact that this is from a hard drive rather than from the internal drive (like C:)? If so, is there a better way to restore a database from the hard drive? I am asking this because someFile.bak is too large to store on my internal drive.