texts: DBMS_DATAPUMP error: The current API cannot be executed because of inconsistencies between the API and the current definition of the job

texts:
I'm trying to do an impdp on a Amazon RDS Oracle instance using DBMS_DATAPUMP.

I'm following the steps described in the Amazon RDS guide (Importing Data into Oracle on Amazon RDS).

I've already copied successfully the dmp to the RDS instances.

This is my impdp job executed with awssys user:

-- IMPORT DATAPUMP
DECLARE
hdnl NUMBER;
BEGIN
hdnl := DBMS_DATAPUMP.OPEN( operation => 'IMPORT', job_mode => 'SCHEMA', job_name=>null);
DBMS_DATAPUMP.ADD_FILE( handle => hdnl, filename => '20180618_my_copied.dmp', directory => 'DATA_PUMP_DIR', filetype => dbms_datapump.ku$_file_type_dump_file);
DBMS_DATAPUMP.METADATA_FILTER(hdnl,'SCHEMA_EXPR','IN (''MYSCHEMA'')');
DBMS_DATAPUMP.START_JOB(hdnl);
END;
/

I can't understand the meaning of the error displayed:

ORA-39002: operazione non valida
ORA-06512: a "SYS.DBMS_DATAPUMP", line 6224
ORA-06512: a line 7
39002. 00000 -  "invalid operation"
*Cause:    The current API cannot be executed because of inconsistencies
           between the API and the current definition of the job.
           Subsequent messages supplied by DBMS_DATAPUMP.GET_STATUS
           will further describe the error.
*Action:   Modify the API call to be consistent with the current job or
           redefine the job in a manner that will support the specified API.

This is the version of the source DB:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
"CORE   11.2.0.1.0  Production"
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

This is the version of Amazon RDS instance:

Oracle Database 12c Standard Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
"CORE   12.1.0.2.0  Production"
TNS for Linux: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production

EDIT: I've added version=COMPATIBLE to both the expdp command and DBMS_DATAPUMP options but error remains.

expdp system@source_db version=COMPATIBLE dumpfile=20180618_my.dmp logfile=20180618_my.log directory=DATA_PUMP_DIR CONSISTENT=Y  SCHEMAS=MYSCHEMA,MYSCHEMA2,MYSCHEMA3,MYSCHEMA4


texts:
I have a MS SQL Server running in a different country (hence VPN, hence long round trip delay) and delveloping a PowerQuery model on a view located on that MS SQL Server.
Everytime I change something in the model, it reloads the view which is consuming a lot of time.
I'd like to:

setup a local PostGreSQL Server on my Mac (done already)
mirror the MS SQL View data into a PostgreSQL Database
connect the powerQuery to the local PostgreSQL Server (easy)

I'm struggling with "2", mirror the content of a view into the PostgreSQL server without using direct SQL (TCP1433) which I cannot use.

How can I mirror the data to the PostgreSQL Server using ODBC only?

BTW: I have no direct access to the MS SQL Server apart from the ODBC connection

EDIT: If required, I can also install a MS SQL Express Edition if that helps in any way. Thanks


texts: Oracle XE 18c connects with sqlplus, but not DB GUI apps

texts:
I installed Oracle XE 18c on RHEL on my laptop, got it configured, created connections with two applications (DBeaver and DbVisualizer) as well as able to connect with sqlplus. It all worked "perfectly".

This morning, I had to "IT Crowd" reboot my laptop (turned it off and back on again), then started Oracle XE using sqlplus, accessed a table to confirm the engine actually came up although the appropriate startup messages were generated.

My problem is neither of the DB apps (above) can connect to the XE database again. I've checked the listener.ora file, it matches the connection configuration in both applications for the hostname and port number. The /etc/hosts file still contains the same configuration.

Note that the server is configured in listener.ora to listen on port 1539.

The message I get from both apps when I "ping the server" is effectively the same: "

Cannot connect to '<redacted>' on port 1539

along with the standard "please check and try again" advice. I can't find any log files with entries for today. It sounds trite, but none of the configuration files changed, the only difference I can see is having rebooted and possibly something did not "stick", perhaps a process did not get started.

Please accept my thanks in advance.


texts: Rollback is not working if the storedproce call another storedproc

texts:
Is it intended behavior that if I call a stored procedure inside a stored procedure will automatically commit?

example:

pseudo procedure proc1

create procedure proc1(in var1 int, out var2 int)
begin
  start transaction;
  -- some code here
  commit;
end

pseudo procedure proc2, the one will call the proc1

create procedure proc2()
lbl_begin:
begin
  start transaction;
    -- modify some data here
    call proc1(0, var2);

    if var != 0 then
      rollback;
      leave lbl_begin;
    end;
  commit;
end;

Even the rollback on proc2 is executed, the data modified before the call of the proc1 is still committed.

Is it a bug or its an intended behavior?


texts: Copy mysql schemas to a new instance of a new version

texts:
I have a few schemas, Which are included in mysql 4 instance, And i want to move them to other mysql instance which running under a newer version - mysql 5.6. This instance is already including some schemas.
I want to move these schemas from the old version to the new version, And so to get rid of the old mysql instance.

What is the best practice to do so? Do i need to upgrade each version (4-->5.0, 5.0-->5.1, etc), And only after the schemas will be updated to version 5.6 i will be able to move them to the new instance?

I don't have any experience with mysql upgrading, So any tip would be great.


texts: Which is more efficient, filtering the SSRS dataset or filter using a query parameter

texts:
People at my organization use SQL Queries within SSRS reports: for instance

SELECT name, age
FROM egTable

Then, this query is run on the database and then they use a FILTER within SSRS to get rid of unwanted rows. For instance, there is a filter in the SSRS called AGE, and this will be something like Age = 11

My proposition was that, this is bad: this way of doing things means that we are querying the ENTIRE table, and then from the GIANT resultset we are just getting rid of unwanted rows. Instead, we should be introducing a parameter: @age, and then write the query as follows:

    SELECT name, age
    FROM egTable
    WHERE age = @age

Am I correct in saying that the first method pulls the entire table, whereas mine is far more efficient because it only returns a small result set?

How can I verify/prove this?


texts:
I have researched this on my own but not really sure of the terms I should use and have not gotten far.  Basically I'm trying to figure out how to implements tags for a table row.  So if my table was musicFile, my tags would be artist, genre, etc....  But lets say some musicFiles would only have artist, or genre so its variable.

I think I would need a separate table for Tags but even then there would have to be foreign keys that are placed in the musicFile table and I end up at the starting point since its 1-1, meaning that its still variable.  I would not know at database creation time how many fields to dedicate to Tag foreign keys.


texts: Get the underlying foreign key constrains based upon a view

texts:
I have the following relations:

CREATE TABLE cities (
    city VARCHAR (80) PRIMARY KEY,
    LOCATION point
);

CREATE TABLE weather (
    city VARCHAR (80) REFERENCES cities (city),
    temp_lo INT,
    temp_hi INT,
    prcp REAL,
    DATE DATE
);

CREATE VIEW test AS SELECT
    *
FROM
    cities;

Now I would like to utilize the test view or any other view in a GUI which simply presents an editable table. This is possible with the recently introduced automatically updatable views. I want the table to contain drop-downs containing all possible values every time there is a column with constrains on it, like the column with a foreign key in this case.

How can this be achieved? I know that I could utilize enums and I do that today, but on Amazon RDS they are unreasonably hard to modify.

I had an idea of (ab)using the "EXPLAIN SELECT" query for each column and check which table and column is being queried.


texts: Meaning of the number of days in an average age?

texts:
I would like to know the average age of my users and did the following to do that:

# SELECT avg(age(birthday)) FROM "user";
                   avg
------------------------------------------
 33 years 10 mons 32 days 08:33:10.577946

What does the number of days mean? How can it be over 31 days?

I have 3746 records and I'm on the UTC time zone.

PS: I'm using Postgres 9.5.3


texts: SQL Server AlwaysOn Availability Groups Cluster File Share Permissions

texts:
Just need to double confirm, when setting up the file share witness to act as a quorum vote in the HA cluster - does each server node within the cluster need access to that file share or is it just the cluster name that needs write access to the file share?

Pretty sure its just the cluster object name that needs access to the file share but want to be doubly sure


texts: Optimization: Increase Innodb_pool_size useful?

texts:
We run an AWS RDS instance with 8vCPUs and 64GB memory.
All tables are using InnoDB engine.

I have kept the default setup of innodb_pool_size to be half the memory.

What I see is that almost 30Gb of memory is used and no read I/O operation (except for some spikes).

Freeable memory:

Read IO:

I've seen some great blog post, answers (like this one How large should be mysql innodb_buffer_pool_size?) about tuning MySQL.

Would it be beneficial to increase the innodb_pool_size to something like 3/4 of the memory? As I don't see read IO.

Also, but I don't know if that is related: what can explain this huge storage drop in a couple of minutes? We don't store large objects, BLOBs, etc.

Free Storage:


texts: Fastest engine for Mass Updates (only updates done on table)

texts: I have a table of 2 columns and 60 million rows. I want to update the table by incrementing one field by 1 on various different rows (This is the only type of operation that will ever be done on the table). The updates that occur happen all at once everytime, so I am usually updating 30-40 million out of the 60 million rows once a day. This table is only used by one user so I am not worried about multiple users trying to connect to the table at the same time or anything like that. Which engine (and parameters) would be fastest to perform just updates and nothing else on (well there will be one select statement once in a while, but I am not concerned about the performance of select statements)? I am currently using MyISAM as I am not worried about ACID, but it currently is quite slow for mass updates. Essentially I am trying to find the fastest way to do MASS Updates.


texts: mySQL for analytics and updating same row

texts:
Looking for the best way to handle using mySQL/PHP for analytics and thus having multiple connections trying to update the same counters in the same row at the same time.

The problem being if I have a row like 'year-month-day-hour','hits' and I want to increase hits for every pageview, that's going to be a lot of connections trying to update that same field in the same row. I'm worried about contention and/or writing the wrong values.

Also this will be packaged for many clients on different kinds of hosting. Thanks for any suggestions.


texts: Why can't "NEXT VALUE FOR" be used in a case statement

texts:
I was writing an INSTEAD OF INSERT trigger and hit an error on this code:

CASE
   WHEN Inserted.KeyId IS NOT NULL THEN Inserted.KeyId
   ELSE NEXT VALUE FOR SomeSchema.NextKeyId
END

The error was:

  NEXT VALUE FOR function cannot be used within CASE, CHOOSE, COALESCE, IIF, ISNULL and NULLIF.

Reading up shows that this is a fairly hard limitation, which I guess I can accept, but I would like to know why this limitation in is place.

Seems odd to me that it can't just go call that method for the part of the set that matches that branch of the case statement.

Can someone explain why a CASE statement does not allow NEXT VALUE FOR?


texts: SQLAnywhere: Mobilink upload_fetch_column_conflict seems not to be executed

texts:
With Sybase Central I've configured a MobiLink test object, where I'm trying to detect a unique constrain violation before uploading. Unfortunately it appears upload_fetch_column_conflict seems to not being executed. My events are configured like this on Sybase Central:

[...]
Product (DBA): upload_fetch_column_conflict
SELECT "DBA"."Product"."name",
    "DBA"."Product"."quantity",
    "DBA"."Product"."quality"
FROM "DBA"."Product"
WHERE "DBA."Product"."name" = {ml r."name} AND NOT (SELECT count(*) from "DBA"."Product" WHERE quality = {ml r.quality})> 0;
[...]

Any idea what I've maybe missed?


texts: How to grant select on a view ... based on IP adress?

texts:
I wonder if it's possible to allow a client A to "see" some data in a view while allowing any other clients, namely B and C, to see ALL data from the same view.

The distinction between A, B and C would rely on their IP address.


texts: only last row is inserting in table when having "instead of insert" trigger on that table

texts:
I have this trigger

CREATE TRIGGER [dbo].[intrigger] ON [dbo].[test]
 instead of insert
AS
begin
declare @name nvarchar (50),@city nvarchar (11),
@tdate datetime , @fid int,@fopd datetime;
select @name =i.name from inserted i;
select @city =i.city from inserted i;
select @tdate =i.date from inserted i;
select @fid =i.fid from inserted i;
select @fopd = Faculty.fopd from Faculty
    where fid= @fid;
if (@fopd > @tdate )
begin
    RAISERROR('Cannot insert ',16,1);
    ROLLBACK TRANSACTION
    RETURN;
    -- stop on error
end
insert into test
values (@name,@city,@tdate,@fid);
end

and the insert :

insert into test
values('a','h','2014-10-13',1),
('s','h','2014-10-14',2),
('y','d','2014-10-15',2),
('s','h','2014-10-18',1),
('m','a','2014-10-20',1),
('a','h','2014-10-28',2)

then I select * from test .. only the last values are inserted ('a','h','2014-10-28',2)

I have to do one insert at a time !

I used for trigger .. all lines were inserted BUT in another test it accepted the wrong values :

insert into test
values('a','h','2014-10-13',1),
('s','h','2014-10-14',2),
('y','d','2014-10-15',2),
('s','h','1990-10-18',1), -- this is wrong must not be inserted or stop all
('m','a','2014-10-20',1),
('a','h','2014-10-28',2)

what went wrong !?


texts:
I have two tables, same structure (filename, filesize, permissions), and possible different or missing values between the two..

I would like to display all the records with the following fields:

filename | filesizeTable1 | permissionsTable1| filesizeTable2 | permissionsTable2 | match

and I would like:

the same filename to be displayed only once
if filename is missing in one of the two tables, the columns filesize and permissions for that table would be empty
if filename exists on both tables, but one of the other fields is different the field "match" would display "1"
if filename exists on both tables, and the other fields are the same, the field "match" would display "0"

The step 1 is achievable with a join between the two tables (select filename from table1 union select filename from table2 order by filename;) but I'm stuck on how to proceed to have empty lines when a filename is missing in one of the two tables..

Solution (thanks Lennart):

select a.filename as filename
     , a.size as filesize1, a.permissions as permissions1
     , b.size as filesize2, b.permissions as permissions2
     , CASE
        WHEN a.filename IS NULL or b.filename IS NULL THEN 1
        WHEN a.size != b.size THEN 2
        WHEN a.permissions != b.permissions THEN 2
        ELSE 0
       END  as Match
from scanTableHost1 as a
LEFT JOIN scanTableHost2 as b
    ON a.filename = b.filename
UNION
select b.filename as filename
     , a.size as filesize1, a.permissions as permissions1
     , b.size as filesize2, b.permissions as permissions2
     , CASE
        WHEN a.filename IS NULL or b.filename IS NULL THEN 1
        WHEN a.size != b.size THEN 2
        WHEN a.permissions != b.permissions THEN 2
        ELSE 0
       END  as Match
from scanTableHost2 as b
LEFT JOIN scanTableHost1 as a
    ON a.filename = b.filename


texts: Why is this very simple UPDATE so much slower than the SELECT it contains?

texts:
In MariaDB 10.3.9:

SELECT COUNT(*) FROM photos

vs

UPDATE counts SET countValue = (SELECT COUNT(*) FROM photos) WHERE countName = 'photos';

The select takes 1.1s vs the update which takes 2.6s.  3million rows in table photos, 3 rows in table counts.

ANALYZE format=JSON output:

{
  "query_block": {
    "select_id": 1,
    "r_loops": 1,
    "r_total_time_ms": 1299.6,
    "table": {
      "table_name": "photos",
      "access_type": "index",
      "key": "PRIMARY",
      "key_length": "4",
      "used_key_parts": ["photoId"],
      "r_loops": 1,
      "rows": 3178665,
      "r_rows": 3.25e6,
      "r_total_time_ms": 1085.7,
      "filtered": 100,
      "r_filtered": 100,
      "using_index": true
    }
  }
}

vs

{
  "query_block": {
    "select_id": 1,
    "r_total_time_ms": 2764.7,
    "table": {
      "update": 1,
      "table_name": "counts",
      "access_type": "range",
      "possible_keys": ["countName"],
      "key": "sCount",
      "key_length": "1023",
      "used_key_parts": ["countName"],
      "rows": 1,
      "r_rows": 1,
      "r_filtered": 100,
      "r_total_time_ms": 0.0756,
      "attached_condition": "counts.countName = 'photos'"
    },
    "subqueries": [
      {
        "query_block": {
          "select_id": 2,
          "r_loops": 1,
          "r_total_time_ms": 2764.6,
          "table": {
            "table_name": "photos",
            "access_type": "index",
            "key": "PRIMARY",
            "key_length": "4",
            "used_key_parts": ["photoId"],
            "r_loops": 1,
            "rows": 3178665,
            "r_rows": 3.25e6,
            "r_total_time_ms": 2541.8,
            "filtered": 100,
            "r_filtered": 100,
            "using_index": true
          }
        }
      }
    ]
  }
}

Why is the subquery in the update query so slow?  I can easily fix this by doing a separate manual update with the result of the select query, but I'd like to know why this is slowing down so much.

On further examination, even THIS takes over twice as long as the simple select:

SET @foo = (SELECT COUNT(*) FROM photos);

What?  Why?!


texts: SQL Server Agent - Report Failure but continue When intermediate step fails

texts:
I have a SQL Server Agent job that has three steps with the following control flow:

Step 1 - on success - Go to next Step. on fail - job fails
Step 2 - on success - Go to next Step. on fail - Go to next Step
Step 3 - on success - report success, on fail - report fail

However, What I want to happen is, if step 2 fails, run step 3 but report that the job has failed (regardless of whether step 3 is successful or not)

The only way I can think to do this is as per the screenshot below which duplicates the final step but the duplicate step reports failure if it succeeds

Is there a better way of doing this?


texts: Java driver dont fire query-start probe in postgres 11

texts:
While using SystemTap to trace postgres session on PostgreSQL 11.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-36), 64-bit.

global livequeries

probe process("/usr/pgsql-11/bin/postgres").mark("query-start"){
  printf("[%s] Query start on PID %d : %s \n", ctime(gettimeofday_s()), pid(), user_string($arg1) )
  livequeries[tid(),$arg1] = gettimeofday_us()
}

psql fires this probe but java driver dont.

REF: docs


texts: Is MongoDB IXSCAN operator a scan or a seek?

texts:
It seems to me that the only operators I see in a MongoDB query plan are COLLSCAN or IXSCAN. The indexes being b-tree, I guess MongoDB can do index seek and index scan ? If so, how do we know that a seek id performed ?

For instance, in this plan (just a fragment) :

 "executionStats" : {
    "executionSuccess" : true,
    "nReturned" : 17,
    "executionTimeMillis" : 42,
    "totalKeysExamined" : 25359,
    "totalDocsExamined" : 1553,
    "executionStages" : {
        "stage" : "FETCH",
        "filter" : {
            "address.zipcode" : {
                "$eq" : "10075"
            }
        },
        "nReturned" : 17,
        "executionTimeMillisEstimate" : 30,
        "works" : 25360,
        "advanced" : 17,
        "needTime" : 25342,
        "needYield" : 0,
        "saveState" : 198,
        "restoreState" : 198,
        "isEOF" : 1,
        "invalidates" : 0,
        "docsExamined" : 1553,
        "alreadyHasObj" : 0,
        "inputStage" : {
            "stage" : "IXSCAN",
            "filter" : {
                "cuisine" : {
                    "$regex" : ".*alian",
                    "$options" : "i"
                }
            },
            "nReturned" : 1553,
            "executionTimeMillisEstimate" : 30,
            "works" : 25360,
            "advanced" : 1553,
            "needTime" : 23806,
            "needYield" : 0,
            "saveState" : 198,
            "restoreState" : 198,
            "isEOF" : 1,
            "invalidates" : 0,
            "keyPattern" : {
                "cuisine" : 1.0
            },
            "indexName" : "cuisine_1",
            "isMultiKey" : false,
            "multiKeyPaths" : {
                "cuisine" : []
            },
            "isUnique" : false,
            "isSparse" : false,
            "isPartial" : false,
            "indexVersion" : 2,
            "direction" : "forward",
            "indexBounds" : {
                "cuisine" : [
                    "[\"\", {})",
                    "[/.*alian/i, /.*alian/i]"
                ]
            },
            "keysExamined" : 25359,
            "seeks" : 1,
            "dupsTested" : 0,
            "dupsDropped" : 0,
            "seenInvalidated" : 0
        }

I can guess it is a scan (I did a regexp search on purpose) :

"totalKeysExamined" : 25359,
"totalDocsExamined" : 1553,

but I see

"seeks" : 1,

Is there any rule as how to interpret the plan, and why there is no IXSEEK operator ?


texts: How do I get the DDL used to create function based index?

texts:
I am reverse-engineering the schema of an Oracle database. I have a table and I created a function-based index on it.

DROP TABLE Fbi_tab;
CREATE TABLE Fbi_tab (
  a INTEGER,
  b INTEGER,
  c INTEGER
);

CREATE INDEX Idx ON Fbi_tab (a+b*(c-1), a, b);

How can I generate the DDL used to create this index?


texts: Why does TSQL return the wrong value for POWER(2.,64.)?

texts:
select POWER(2.,64.) returns 18446744073709552000 instead of 18446744073709551616.  It seems to have only 16 digits of precision (rounding the 17th).

Even making the precision explicit select power(cast(2 as numeric(38,0)),cast(64 as numeric(38,0))) it still returns the rounded result.

This seems like a pretty basic operation for it to be flaking out arbitrarily at 16 digits of precision like this.  The highest it can calculate correctly is only POWER(2.,56.), failing for POWER(2.,57.).  What is going on here?

What's really terrible is that select 2.*2.*2.*2.*2.*2.*2.*2.*2.*2.*2.*2.*2.*2.*2.*2.*2.*2.*2.*2.*2.*2.*2.*2.*2.*2.*2.*2.*2.*2.*2.*2.*2.*2.*2.*2.*2.*2.*2.*2.*2.*2.*2.*2.*2.*2.*2.*2.*2.*2.*2.*2.*2.*2.*2.*2.*2.*2.*2.*2.*2.*2.*2.*2.; actually returns the right value.  So much for terseness.


texts: Substitute DISTINCT ON for query transformation (Postgresql to Redshift)

texts:
I need to transform my Postgresql query to Redshift and I working with the following data, here is an excerpt:

In my final output, I would like only to keep rows with the highest rank (in green). And I was using the following query, which works to get the output, but I have to transform it into Redshift because the visualization tool is only using Redshift and Redshift doesn't recognize DISTINCT ON.
select distinct on customer_id
id_created,
customer_id,
call_start,
status_prior,
status_after,
rank() over (partition by customer_id order by call_start) as rank
order by customer_id, rank desc;


texts: SQL query that concatenates values from duplicate rows in a single table

texts:
Let's say I have a table of employees, like this:

Name     Sex     Role

Bob      M       Developer
Joe      M       QA

Now, I have a problem with duplicated rows in this table. I will fix it sometime but it's not the issue. What I currently need is something that queries such a table:

Name     Sex     Role

Bob      M       Developer
Bob      M       Janitor
Joe      M       QA
Joe      M       CEO

And will output the following rows:

Name     Role

Bob      Developer, Janitor
Joe      QA, CEO

It doesn't matter to me if the "Role" will be split to separate columns or be a single column with multiple values.

Using SQL Server 2008 if it matters.


texts: PostgreSQL isn't listen on 5432 in Windows 7

texts:
I have a PostgreSQL 9.3.1 DB Server running in Windows 7 Ultimate. I have checked the file postgresql.conf and this lines are uncommented:

listen_addresses="*"
port=5432

And this is the configuration at pg_hba.conf file:

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
host    all             all             127.168.1.0/24          md5

# IPv6 local connections:
host    all             all             ::1/128                 md5

# Allow replication connections from localhost, by a user with the
# replication privilege.
#host    replication     postgres        127.0.0.1/32           md5
#host    replication     postgres        ::1/128                md5

Apparently all is fine but if I run the command netstat -an I don't see the port 5432 open to any host unless this line:

0.0.0.0:5432

means that. The problem is I can't connect from any PC in subnet 192.168.1.x and the message says PostgreSQL isn't installed on the PC or it doesn't listen. I have disable any firewall in all PC looking for a solution but can't. Can any help me to find the problem here?

UPDATE

This is the error I'm getting:


texts:
I am unable to add a replica member back into the cluster.
Setup: Haproxy, patroni, consul, postgresql11
Cluster: Leader, standby, 3 replica(all 3 failed in adding back to cluster)
I could see the follow messages in logs:
2021-01-05 14:51:40,315 INFO: Lock owner: db2.host.net; I am db1.host.net
2021-01-05 14:51:40,318 INFO: trying to bootstrap from leader 'db2.host.net'
pg_basebackup: COPY stream ended before last file was finished
pg_basebackup: removing data directory "/app/db/pgsql/data"
2021-01-05 14:51:40,766 ERROR: Error when fetching backup: pg_basebackup exited with code=1
2021-01-05 14:51:40,766 WARNING: Trying again in 5 seconds
I could see the below files in the master node
# find /app/ -name *.init
/app/postgres/data/global/pg_internal.init
/app/postgres/data/base/13881/pg_internal.init
/app/postgres/data/base/16387/pg_internal.init

Please suggest how to proceed with the restore process. So far i have tried removing the init files, restarting the master, cleaning data directory in replica and trying to sync with master again. But i am facing the same errors in the logs.
Also, what could be the reason for the extra init files remained in the system and are these the reason for the failure?
Appreciate your help. Thank you.


texts: Merge Replication - set up new subscriber based on backup of publisher and last snapshot (object definitions only)

texts:
I have a replication setup with 1 publisher and 3 push subscribers. I need to add a new push subscriber. I cannot afford downtime for the publisher and the existing subscribers.

My plan is to do the following:

take a backup of the publisher. Say that this backup finished at 6 pm.
restore the backup to the new subscriber server
use the latest snapshot which was taken 5 days ago as the snapshot to be used to set up the new subscriber
set up the new subscriber and do not use the snapshot to initialize it.  This means that the tables will not be dropped, recreated, nor populated from the snapshot. The snapshot is only used for the metadata definitions contained in it.
start replication to the new subscriber. Say that it was 6:10 pm by the time the new subscriber started synchronizing.

How will any differences between the publisher and the new subscriber be resolved for data that changed during the time the subscriber was restored from the backup of the publisher taken at 6 pm and the time when synchronization of the new subscriber started at 6:10 pm?

a. Will changes that occurred on the publisher between 6 pm and 6:10 pm be pushed to the new subscriber?

b. Since this is merge replication, will any changes made on the publisher between 6 pm and 6:10 pm be affected by the new subscriber itself? I want to make sure I avoid any legitimate changes that occurred on the publisher between 6 pm and 6:10 pm being changed back on the publisher due to the fact that the new subscriber is not aware of the change that occurred on the publisher.

Am I overthinking this?


texts: A flexible and robust schema for storing precomputed values/metrics

texts:
Background

I'm dealing with a table that contains a lot of historical data. What I am doing is generating a bunch of metrics based on different combinations of column values and date periods (e.g. total number of widgets used by each person in the last six months).

Rather than doing these calculations on the fly, I have a query which is precalculating them. However I am not sure of the best way to actually store these values.

Ideas I've come up with

1. A table for every combination of columns

For example PersonWidget, PersonWidgetDepartment etc. These would have a composite primary key consisting of the the IDs of Person, Widget etc.

The problem here is that there would be a lot of tables, there are restrictions on max length for table names and creating new combinations means creating a new table every time (migrations would have to be created and so on).

2. One table for metrics with nulls for columns which aren't relevant

This would look something like:

PersonId | WidgetId | DepartmentId | TotalUsed

1          2          3              50
1          2          4              30
1          2          null           80
1          null       null           100

So when querying, if you wanted to know how many widgets a particular person used, you'd query for their ID + WidgetId IS NULL + DepartmentId IS NULL. This is obviously a bit cumbersome and doesn't quite feel right to me. The benefit is that there is only one table to deal with.

3. One table for metrics with every combination of historical columns having a metric value column

This would look something like:

PersonId | WidgetId | DepartmentId | TotalPerson | TotalPersonWidget | TotalPersonWidgetDepartment

1          2          3              100           80                  50
1          2          4              100           80                  30

There's a lot of redundant data being stored here and it means the table ends up having a huge amount of columns.

If anyone can provide input/ideas around a decent way to go about this, would be much appreciated.


texts:
I'm trying to merge two workspaces that contain version-enabled tables. I can't figure out how to resolve conflicts, because I have a lot of rows with the same ID value. They differ only in time interval. If I execute something like this:

DBMS_WM.ResolveConflicts('W_CHILD', 'SOME_TABLE', 'ID=1', 'CHILD');

Which row will be used? How can I specify concrete row?


texts:
I have two tables with same structures in a MS Sql server : T1 and T2, T1 has around 4 Billion rows and T2 is an empty table. Need to insert T1 data in to T2 in batches, After each Batch Insert in to T2 from T1, I have to Delete that data(batch Delete) from T1.
FYI: Tables contains 1 Primary Key and 4 non-clustered indexes

Please suggest me the best options/best query.


texts: Making a new database onto existing AWS RDS instance

texts: I know that we can create multiple databases on a single AWS RDS instance. I have an existing MySQL instance and was wondering what steps are required to create an additional database on that server? Does it involve creation of a new user and so forth?


texts: Oracle Audit Vault Create Custom Interactive Report

texts:
I have installed and configure Oracle Audit Vault server and i deploy my production database environment to Audit Vault which is sys.aud$ table.
I can get predefined reports which is like entitlement reports.
Now i want to get notification when somebody will occur any drop-create table operations.
When i research in Oracle Docs shows interactive report. But not any explanation unfortunatelly. Somebody says that i need any bi tool for customize report.
Anybody tried or did this case ? Can you please help to me ?
Thanks,


texts:
I migrated a large website and database from an older server (Windows 2008 / SQL Server 2008 / 16 GB RAM / 2 x 2.5 GHz Quad Core / SAS disks) to a newer, much better server (Windows 2008 R2 / SQL Server 2012 SP1 / 64 GB RAM / 2 x 2.1 GHz 16 Core processors / SSD disks).

I detached the database files on the old server, copied and attached them on the new server. Everything went very well.

After that, I changed to compatibility level to 110, updated statistics, rebuild indexes.

To my huge disappointment, I noticed that most sql queries are much slower (2-3-4 times slower) on the new SQL 2012 server than on the old SQL 2008 server.

For example, on a table with around 700k records, on the old server a query on index took around 100ms. On the new server, the same query takes around 350 ms.

Same happens for all queries.

I would appreciate some help here. Let me know what to check/verify. Because I find it very hard to believe that on a better server with a newer SQL Server, the performance is worse.

More details:

Memory is set to max.

I have this table and index:

CREATE TABLE [dbo].[Answer_Details_23](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [UserID] [int] NOT NULL,
    [SurveyID] [int] NOT NULL,
    [CustomerID] [int] NOT NULL default 0,
    [SummaryID] [int] NOT NULL,
    [QuestionID] [int] NOT NULL,
    [RowID] [int] NOT NULL default 0,
    [OptionID] [int] NOT NULL default 0,
    [EnteredText] [ntext] NULL,
 CONSTRAINT [Answer_Details_23_PK] PRIMARY KEY NONCLUSTERED
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

CREATE NONCLUSTERED INDEX [IDX_Answer_Details_23_SummaryID_QuestionID] ON [dbo].[Answer_Details_23]
(
    [SummaryID] ASC,
    [QuestionID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

I executed this query:

set statistics time on;
select summaryid, count(summaryid) from Answer_Details_23 group by summaryid order by count(summaryid) desc;
set statistics time off;

OLD SERVER - SQL Server Execution Times:
   CPU time = 419 ms,  elapsed time = 695 ms.

NEW SERVER - SQL Server Execution Times:
   CPU time = 1340 ms,  elapsed time = 1636 ms.

EXECUTION PLANS uploaded here:
http://we.tl/ARbPuvf9t8

Later update:

AMD 2.1GHz Opteron 16 core processors look much worse than Intel 2.5GHz quad core processors
Great improvement changing windows power options from ballanced to high power
Further improvement changing max degree of parallelism to 8 and cost threshold to 4

Now, SQL Server Execution Times: CPU time = 550 ms, elapsed time = 828 ms.

It's still worse than the old server, but not that bad. If you have any other suggestions (other than local query optimizations) please feel free to comment.


texts: Create a new instance on a SQL server, but I need to use the default DB instance

texts:
I need to be able to connect to mydb\wrk when I already have everything in mydb and I do not want to change anything other than being able to connect to mydb\wrk.

I can connect to my database with the following connection string

Provider = SQLOLEDB.1; Password = ****; Persist Security Info = True;
User ID = sa; Initial Catalog = Master; Data Source = MyDb

I want to use this:

Provider = SQLOLEDB.1; Password = ****; Persist Security Info = True;
User ID = sa; Initial Catalog = Master; Data Source = MyDb\ wrk

I want to change the least possible on the server


texts: SQL Server 2012: Monitoring SPID on unpredictable app

texts:
Techies,

There's an application @my shop that gets executed through sql agent which works 95% of the time. The job completes within 10 minutes. The other 5% of the time, this same job stays active forever until one of us in the DBA team stop it.  The developers are working on a fix, however, in the meantime--the job will need to run as is with the current flaw.

Without a budget for tools, I need to find some way to monitor this job so that if it spins for more over 15 minutes, the DBA team is at least alerted.  Any suggestions?


texts: Redshift: Return rows if value exists in cell array

texts:
How do I return the rows that include a particular value in a column's cell array?

Imagine we have a table like so:

id    name    phone           values
1     Taylor  xxx.xxx.xxxx    [4,6,5]
2     John    yyy.yyy.yyyy    [1,5,2]
3     Peter   zzz.zzz.zzzz    [6,2,6]

I need to create an SQL query that will return the rows where the value '6' exists in the values array.  So the expected output would be:

id    name    phone           values
1     Taylor  xxx.xxx.xxxx    [4,6,5]
3     Peter   zzz.zzz.zzzz    [6,2,6]

We are working in Redshift.  So, if it is easier, the possibility of using the json_extract_array_element_text('json_string', pos) function could be used.  Please note that the length of the values arrays could be different from each other.


texts: Speed up slave replication on large database / quickly spawn slave

texts:
We have a large MySQL database (about 100GB on disk) that we would like to spawn a slave for.

Our typical process for creating a slave is:

Create a slave server
Load in a mysql dump of the master
Start the slave
Wait for slave replication to sync w/ master
Place slave server in production

The problem we are having is with the size of the database.  By the time step two above is completed, the slave is so far behind the master that it is unable to catch up.  In fact, the slave replication grows further and further behind.  I'm not sure why this happens.

Any ideas on how to either seed the slave server faster, or fix the replication issue?  Note that the master server has multiple databases, and a mixture of InnoDB/MyISAM tables.  The server is running MySQL 5.1


texts: Getting Available Date/Times For Attendees And Avoid Conflict

texts:
+------------+  +---------------+  +--------------+
| attendees  |  | attendee_times|  | meeting_times|
+------------+  +---------------+  +--------------+
| attendee_id|  | at_id         |  |              |
| name       |  | attendee_id   |  | meeting_id   |
+------------+  | date_1        |  | recipient_id |
                | time_1        |  | sender_id    |
                | date_2        |  | meeting_date |
                | time_2        |  | meeting_time |
                +---------------+  +--------------+

I have to generate a semi-automated meeting planner. Users enter their two optimal dates/times (attendee_times) and the system will look at all other attendees who picked similar dates/times and create a pending meeting between them.

However, the system has to make sure that a matching attendee does not have an existing meeting (meeting_times) already with the same date/time. An attendee can either be a recipient or sender so attendee_id can be either foreign key.


texts:
I'm taking over a project that involves removing and limiting permissions of all database users across our server farm. (fun times)

One of the permissions currently being limited is db_owner permissions.
This permission is being reviewed on a case-by-case basis, but a common change is to replace the db_owner permissions with the following:

db_datareader
db_datawriter
db_ddladmin
db_executor

I would like to define the exact difference between the two (to inform clients).
However, as far as I can tell, the difference between the two should be:

db_accessadmin permissions
db_backupoperator permissions
db_securityadmin permissions

So in effect they would lose:
[ALTER ANY USER]
[CREATE SCHEMA]
[BACKUP DATABASE], [BACKUP LOG], [CHECKPOINT]
[ALTER ANY APPLICATION ROLE], [ALTER ANY ROLE]
[DROP DATABASE]

Is there anything else that a user would loose once db_owner is replaced by the four roles above?
Does this actually serve much of a purpose security wise?


texts: What is faster Asynchronous SAN Replication or Asynchronous AAG?

texts:
If copying data from one datacenter to another, what would generally be the faster approach to propagate that data to the target datacenter, SAN Replication or AAG, i.e. block-level or transaction-level, assuming the network, bandwidth and all other factors are excellent and the same for either method? Both would be asynchronous.

Thanks!


texts:
The log.trc file is missing on the server, but there are archived trace files like (log_15.trc, log_16.trc, log_17.trc, log_18.trc, and log_19.trc).  Is this something to be concerned about?

Is there a way to create the log.trc file?


texts: Percona XtraDB Cluster - do I add arbiter IP in gcomm://?

texts:
I'm configuring Percona XtraDB Cluster 8 on two nodes, and use a 3rd node to act as an arbiter.
I cannot make up out of the docs whether to add the IP of the arbiter in the galera nodes addresses of the arbiter’s configuration.
# A comma-separated list of node addresses (address[:port]) in the cluster
# GALERA_NODES=""

Also, do I add the IP of the arbiter in the wsrep_clsuter_address of the Galera configuration? Again, it's not described in the docs.
wsrep_cluster_address=gcomm://...


texts:
I have a local install of postgresql 11 on my Windows 10 desktop (for my machine learning PhD). The data folder is 76gb (~15 tables, the largest has 100m rows). The  database sits on a Samsung 970 EVO Plus and when inserting new rows, I can see write speed reaching >500mb/s in task manager. However, when I run vacuum or analyze, task manager reports disk I/O of 3-5mb/s and it takes hours (before I manually terminated the process). What am I doing wrong here?

Here are some changes I've made to postgresql.conf.

#------------------------------------------------------------------------------
# RESOURCE USAGE (except WAL)
#------------------------------------------------------------------------------

# - Memory -

shared_buffers = 512MB          # min 128kB
                    # (change requires restart)
#huge_pages = try           # on, off, or try
                    # (change requires restart)
temp_buffers = 1024MB           # min 800kB
#max_prepared_transactions = 0      # zero disables the feature
                    # (change requires restart)
# Caution: it is not advisable to set max_prepared_transactions nonzero unless
# you actively intend to use prepared transactions.
work_mem = 128MB                # min 64kB
maintenance_work_mem = 512MB        # min 1MB
autovacuum_work_mem = -1        # min 1MB, or -1 to use maintenance_work_mem
#max_stack_depth = 2MB          # min 100kB
dynamic_shared_memory_type = windows

# - Asynchronous Behavior -

#effective_io_concurrency = 0       # 1-1000; 0 disables prefetching
max_worker_processes = 8        # (change requires restart)
#max_parallel_maintenance_workers = 2   # taken from max_parallel_workers
max_parallel_workers_per_gather = 4 # taken from max_parallel_workers
#parallel_leader_participation = on
max_parallel_workers = 8


texts: In [Percona] MySQL, is the Adaptive Hash Index included in the Total memory allocated count?

texts:
I'm debugging a memory problem with MySQL 5.6 (I'm using the Percona distribution), but I can't figure out, searching around the web, if the Adaptive Hash Index amount is part of the Total memory allocated, or if it's separated.

Current output:

BUFFER POOL AND MEMORY
----------------------
Total memory allocated 109890764800; in additional pool allocated 0
Total memory allocated by read views 15776
Internal hash tables (constant factor + variable factor)
    Adaptive hash index 10365924496   (1699980328 + 8665944168)
    Page hash           13281976 (buffer pool 0 only)
    Dictionary cache    427147426   (424996592 + 2150834)
    File system         84040   (82672 + 1368)
    Lock system         295705056   (295674296 + 30760)
    Recovery system     0   (0 + 0)


texts: I've backupset created with RMAN, and I want to see what this set contains. How to do that?


texts: Maintenance Plan for all Online databases doesn't execute for all databases

texts:
Using SQL Server 2008 R2, I have a maintenance plan that has a basic Backup All Databases (ignore offline) task, and clean up after 7 days.

However, I note that sometimes I lack backups for (seemingly) random databases.

Even though the script says all databases, bar offline, is there some parameter set where databases won't be backed up? E.g. No changes to database since last backup, or some other conditions?

The particular database in question at this time has been updated in the last 24 hours, but hasn't been backed up in the past week.

edit: the recovery model is set to Simple, which is the same for most other databases on the server, so it's probably not that.
I've tried to look for other documentation, but I can't seem to find any technical details, only high level business/sales white papers.


texts: SQL Server Rebuilding Indexes Not Responding

texts:
Just a quick question.

I am currently rebuilding indexes of my hotel front office system.
I have 1 table with huge amount of data.
I clicked Rebuild All on indexes. It was running fine up to the 3rd index.
I accidentally clicked on the Title Bar of the SQL where it states "Rebuild Indexes" , now it states "Rebuild Indexes ( Not Responding )".

Is it ok if i just leave it?
OR
Do i need to end task? If i end task/process by Task Manager , will i lose data?

Urgent Help.


texts: Efficiencies and pitfalls of a dynamically updated "Uber" View to streamline future code

texts:
We have a number of tables (about 40) with approximately 2500 columns between them that all have matching primary keys, but different unique columns.  For various procedures and views, we'll draw on any number of them - sometimes hundreds of them - to build the necessary results.

I've been considering creating a view that ties all these tables and columns together primarily for the sake of simplicity in referring to the tables and columns in code for subsequent procedures and views, but I wanted to check that I'm not making a major mistake in doing so.  Unfortunately, I don't directly have the ability to change the tables, although there are circumstances where the tables will have columns added or removed, so I need to account for that.

My first question is, I would expect for any subsequent query against the view that the SQL Server optimizer would exclude any tables or columns from the actual execution that aren't actually used in the result set, correct?

I would also need to create a stored procedure to update the definition of the view so that it adjusts the columns included in the view as they change, correct?  Are there any features or possible hang-ups I'm not considering here?


texts:
I have configured "Always On" on SQL server 2017, and also created a backup maintenance job using the wizard, but when I executed the backup job I got following error:

Could not process the operation. Always On Availability Groups does not have permissions to access the Windows Server Failover Clustering (WSFC) cluster.
Disable and re-enable Always On Availability Groups by using the SQL Server Configuration Manager. Then, restart the SQL Server service, and retry current operation.

I disabled/enabled always on but still get the same error.

Any thought?


texts: Test Driven Design for postgres procedures

texts:
I am looking to introduce a test driven design style to writing my stored procedures for a postgresql database implementation.

I have seen that pgTap is a popular unit testing tool for postgres and correctly allows for the tests to be written in SQL and not built and run externally.

In order to go the full distance I would also like to be able to use test doubles (stub, mock, fake, dummy) and maybe even some IDE support for running and refactoring (something like tsql)

Are there currently tools and resources available specifically for supporting TDD in postgres or do people roll their own by building fake tables and data and wrapping everything in transactions that roll back on completion?

What are currently the best practices for approaching TDD in postgres?


texts:
Looks my question is unusual because I didn't find the answer at all.
Let's imagine, I have table A with columns: language and uri.

language | uri
---------|-----------------
ru       | some-uri
en       | some-another-uri
...

My question is: How can I return JSON object instead of many rows.
E.g.:

{
  "ru": "some-uri",
  "en": "some-another-uri",
  ...
}


texts: Is there a better way to exclude all "not actively created by a human" indexes than what I'm doing?

texts:
My goal is to list the indexes which I, a human, have actually added into my database. I don't want to see ones that were created by PG when creating PKs and whatnot. I didn't even know it did that, but it sure seems to do. A lot. It's not obvious how to sort out all those, since there is no column such as "was_created_automatically_by_pg". I started with:
SELECT schemaname, tablename, indexname, indexdef FROM pg_indexes;

That causes tons of "junk" records to be returned with the schemaname "pg_catalog". So, ignoring those:
SELECT schemaname, tablename, indexname, indexdef FROM pg_indexes WHERE schemaname <> 'pg_catalog';

I still get a lot of indexes which I never created.
SELECT schemaname, tablename, indexname, indexdef FROM pg_indexes WHERE schemaname <> 'pg_catalog' AND indexdef NOT LIKE 'CREATE UNIQUE INDEX %';

Now, all the resulting records seem to correspond to indexes which I remember having created, some with names such as "djhhcdhxchhhsxhsddf" and some with names that were auto-created, based on the columns it involves. Not sure how I made those since pgAdmin 4 refuses to let me create an index without manually naming it. (Which is quite annoying, might I add.)
However, what if I added a "UNIQE INDEX" myself? It feels like my own "final" query is "hiding" some records this way. It doesn't seem reliable.
How come they didn't have a column as I described above? Or is this actually pgAdmin 4 creating the indexes automatically? Is it actually possible to create a PRIMARY KEY without an internal index on it? I don't understand why anyone would ever want to create a PK which is not unique and indexed. That's the whole point of the field... to be unique and enable fast FKing from other tables.
It makes me anxious to see how many indexes there are which I never thought of. I assume that I shouldn't touch them, ever, but I always assumed that the PKs and whatnot had "internal" indexes which are never mentioned/displayed/returned.


texts:
I have simple query

WHILE ( @counter < 50 )
    BEGIN
        BEGIN TRANSACTION
        PRINT 'Counter = ' + CONVERT(VARCHAR(4), @counter);
        WITH    CTE
                  AS (
                       SELECT TOP 50000 * FROM MyTable
                     )
            DELETE FROM CTE OPTION ( MAXDOP 1 )
        SET @counter += 1
        COMMIT TRAN
    END

When I look at messages, the PRINT command does not return row every loop but shows up as multiple rows at ones. I have added one line and now it shows up 1 line at the time and the the whole process is much faster.

WHILE ( @counter < 50 )
    BEGIN
        WAITFOR DELAY '00:00:00.5' --< This line
        BEGIN TRANSACTION
        PRINT 'Counter = ' + CONVERT(VARCHAR(4), @counter);
        WITH    CTE
                  AS (
                       SELECT TOP 50000 * FROM MyTable
                     )
            DELETE FROM CTE OPTION ( MAXDOP 1 )
        SET @counter += 1
        COMMIT TRAN
    END

so why is it when I have added .5 second wait time to each loop iteration it actually runs faster and allows the print message to come out one at the time?


texts: Charindex, Getting specific data from a string

texts:
Hello I am very new to charindex and have tried to create a query that would extract my needed information but am running into an error.  I searched for similar problems on stackexchange as well as a few other sites and it seems I am still unable to fully grasp the concept to get what I need.

My biggest issue is the data that I have in the column is not in any set pattern.  I found a stackexchange question that answered this but my setup is a bit different.  Here are a few examples from column descr and table facility:

ID | Descr |
21 | Playhouse, Virginia Series, 98 Stage, PSDD, House|
35 | Playhouse, Virginia Series, 111 Stage, Inconel|
53 | Playhouse, FX Series, 125 Stage, House F31|
76 | FX Series, 134 Stage, F31, Onconel|

A bit more information.  There should always be a comma before the number I am looking for.  I have found one case out of thousands that this isn't the case, but to make this easier we will assume it always has a leading comma and space.  The number I am looking for can be either two or three digits.  It is also always followed by a space and the word stage.

Here is what I attemped but received and error:
select

with cterecords(descr, position) as
(
    select
        descr, charindex(',', descr) position
        from facility
)
    select
        substring(descr, position +1,
        charindex('stage', descr,position) - position -1)
    from cterecords;

I am receiving this error:
Msg 537, Level 16, State 3, Line 1
Invalid length parameter passed to the LEFT or SUBSTRING function.

But if I get through the error, I am not sure if I have the query written to get exactly what I want.

Here is SQL Fiddle with test data: SQL Fiddle Example Data


texts: Is it possible to disable `execute` in lieu of `exec`?

texts: We've got an issue at the moment where we've got multiple query plans because some JDBC code uses call resulting in an exec statement, and other code uses execute. Is it possible to disable execute to nudge developers into only using call/exec (particularly since call is faster)?


texts: Will creating reports on R using MySQL Database update my database on MySQL?

texts:
I use MySQL to store my data. To analyze the data I want to be able to generate monthly reports on R. So far I was able to connect to MySQL using dbConnect but I want to make sure that none of the tweaking I do affects the database I have in MySQL. Below is the code I used to import the database/table to R.

install.packages("RODBC")
library(RODBC)
Data<-dbConnect(drv, username="<insert username here>", password="<insert password here>", dbname ="<insert database name here>")
Table1 <- fetch(dbSendQuery(Data, "select * from Table"),-1)

Using the fetch function I am able to load the table into R but I want to make sure that I am not changing it on MySQL at the same time. Would applying any operations, adding columns etc affect the original database in MySQL?


texts:
Right now when I install SQL 2014 (via a PowerShell script) it automatically sends errors to:

  c:\Program Files\Microsoft SQL Server\120\Setup
  Bootstrap\Log\blahblahlog.txt

I'm hoping to capture those errors. I have a custom error log that captures other things throughout my PowerShell script. I'm trying to find a starting point, or if this is even possible. The errors logged there are very specific and useful to me.

An obvious solution is to just grab the contents of that log file, but I'm hoping to cut out the middle man.


texts: After upgrade from 9.1 to 9.3, commands still point to 9.1

texts:
Upgraded from 9.1 to 9.3 on my Ubuntu 12.04 server. Everything worked great but running commands like 'createdb' still runs them from the 9.1 path.

/usr/lib/postgresql/9.1/bin/createdb: unrecognized option....

Edit, additional info:
I used this gist https://gist.github.com/ibussieres/11262268
I already uninstalled 9.1 with the regular 'sudo apt-get remove postgresql-9.1'

This is the output of requested commands

which createdb
  /usr/bin/createdb

createdb --version
  createdb (PostgreSQL) 9.1.14

which psql
  /usr/bin/psql

psql --version
  psql (PostgreSQL) 9.3.5


texts: Finding the median of column values per row

texts:
I've tried the below query, but the median value shown is wrong.

Query

select
(select cast(Avg(TotAvg * 1.0 )as decimal(6,2))
 from (values (convert(decimal(6,2), a)),(convert(decimal(6,2), b))
             ,(convert(decimal(6,2), c)),(convert(decimal(6,2), d))
             ,(convert(decimal(6,2), e)),(convert(decimal(6,2), f))
             ,(convert(decimal(6,2), g)),(convert(decimal(6,2), h))
             ,(convert(decimal(6,2), i)),(convert(decimal(6,2), j))
             ,(convert(decimal(6,2), k)),(convert(decimal(6,2), l))
             ,(convert(decimal(6,2), m)),(convert(decimal(6,2), n))
             ,(convert(decimal(6,2), o)),(convert(decimal(6,2), p))
             ,(convert(decimal(6,2), q)),(convert(decimal(6,2), r))
             ,(convert(decimal(6,2), s)),(convert(decimal(6,2), t))
             ,(convert(decimal(6,2), u))
    ) as Totalavg(TotAvg)
) as Median
from temp

Column values are a to u in a row.

Table Values

First row - 1,5,6,7,8,2,6,3,4,5,2,1,6,5,7,8,2,7,6,2,8
Second row - 1,5,6,7,8,2,2,3,2,4,2,1,4,5,9,8,2,7,6,2,8

Create table script

create table temp
(
    id int identity(1,1),
    a decimal(6,2),
    b decimal(6,2),
    c decimal(6,2),
    d decimal(6,2),
    e decimal(6,2),
    f decimal(6,2),
    g decimal(6,2),
    h decimal(6,2),
    i decimal(6,2),
    j decimal(6,2),
    k decimal(6,2),
    l decimal(6,2),
    m decimal(6,2),
    n decimal(6,2),
    o decimal(6,2),
    p decimal(6,2),
    q decimal(6,2),
    r decimal(6,2),
    s decimal(6,2),
    t decimal(6,2),
    u decimal(6,2)
)

Insert Table Script

insert into temp
    (a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u)
values
    (1,5,6,7,8,2,6,3,4,5,2,1,6,5,7,8,2,7,6,2,8)

insert into temp
    (a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u)
values
    (1,5,6,7,8,2,2,3,2,4,2,1,4,5,9,8,2,7,6,2,8)


texts: Multi tenancy by schema - Deny access to another tenants

texts:
In a multi tenant environment, where the multiple tenants are in the same database and a schema is used to separate each one of them, how can I create a database user (MS SQL SERVER) for each schema, where the user has only access to his schema?

I know that I can do something like the code bellow, but I would like that by default a user has no access to any other tenant.

DENY SELECT ON schema::[dbo] TO [user_name]
DENY SELECT ON schema::[other_schema] TO [user_name]
GRANT SELECT ON schema::[safe_schema] TO [user_name]

Thanks


texts: DBCC checkdb showing failure after a restore

texts:
We restored a backup into a new database.  After running a few delete queries we received the following message:

  Msg 824, Level 24, State 2, Line 1 SQL Server detected a logical
  consistency-based I/O error:...

I ran DBCC checkdb and saw numerous lines such as:

Msg 2576, Level 16, State 1, Line 1
The Index Allocation Map (IAM) page (0:0) is pointed to by the previous pointer of IAM page (1:783) in object ID 0, index ID -1, partition ID 0, alloc unit ID 72057599497994240 (type Unknown), but it was not detected in the scan.

and

Msg 8939, Level 16, State 98, Line 1
Table error: Object ID 2083043448, index ID 1, partition ID 72057608168079360, alloc unit ID 72057608304721920 (type LOB data), page (1:307605). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 12716041 and -4.
Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 2083043448, index ID 1, partition ID 72057608168079360, alloc unit ID 72057608304721920 (type LOB data). The off-row data node at page (1:307605), slot 0, text ID 1368915968 is referenced by page (1:307603), slot 0, but was not seen in the scan.
Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 2083043448, index ID 1, partition ID 72057608168079360, alloc unit ID 72057608304721920 (type LOB data). The off-row data node at page (1:307605), slot 1, text ID 903086080 is referenced by page (1:307648), slot 0, but was not seen in the scan.
Msg 8929, Level 16, State 1, Line 1
Object ID 2083043448, index ID 1, partition ID 72057608168079360, alloc unit ID 72057609480241152 (type In-row data): Errors found in off-row data with ID 1368915968 owned by data record identified by RID = (1:43506:13)
Msg 8929, Level 16, State 1, Line 1
Object ID 2083043448, index ID 1, partition ID 72057608168079360, alloc unit ID 72057609480241152 (type In-row data): Errors found in off-row data with ID 903086080 owned by data record identified by RID = (1:102876:38)

The final section of output was:

CHECKDB found 1 allocation errors and 12 consistency errors in database 'Clients'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (Clients).
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

My question is: Should I attempt to restore the database again or proceed with attempting to repair?  I only hesitate because it is a rather large DB and restoring takes quite a while.

UPDATE: a bit more info.
All of the table errors are related to a single table I could just drop and recreate.  However I don't understand what the IAM error really means or its impact.


texts: BULK INSERT permission on a single database

texts:
I work on a company that has straight IT security manners. I was hired as a Database Analyst in order to optimize some processes in our team. The problem is that I only have read and write permissions and I'm trying to work out in getting advanced permissions on the databases. Currently we have a lot of Databases in a single server and we can only work on a single Database, I was wondering if the BULK INSERT permission could be given to a single database or would that be permission to the whole server?

I've searched through the web about this but I got a little confused. This is what I found out. In order to use BULK INSERT I should have INSERT permission to the desired table or database and the ADMINISTER BULK OPERATIONS which is a server wide permission.
If I was given ADMINISTER BULK OPERATIONS permission on the whole database and the INSERT permission on a single database, wouldn't I be able to only BULK INSERT in that single database, since I don't have INSERT permission on other databases?

Is it possible to restrict the use of BULK INSERT to a single database on a server that has multiple databases?

Sorry about the text wall, and thanks!


texts: Oracle random number as default column value

texts:
I'm trying to create a table in OracleDB and assign random number to one of the columns as a default. I have this setup working in postgresql

create table table_name (
    column integer NOT NULL DEFAULT (random())
)

how can I do something similar in oracle (preferably without triggers)?

I've tried this

create table table_name (
    column integer generated always as (dbms_random.random) virtual
)

but oracle doesn't like it since the function is non deterministic.


texts: Can I enable SQLCMD mode from inside a script?

texts: Is there something like a directive which I can use in a script to force SSMS to enable/disable SQLCMD mode?


texts:
ORA-31114: XDB configuration has been deleted or is corrupted

How to recreate the XDB configuration? Do I need to remove the existing component first?

By the way I can't run UTLRP scritp for the same reason.
I also cannot perform a datapump export.


texts: How can i automate the process of log shipping monitoring

texts:
I have log shipping configured between my Prod SQL server 2005 and QA SQL server 2005.        Per the requirement from Business we need to provide them with a report at the end of the day for the LOG shipping configured for multiple databases on their respective instances.

If i go manually and use standard reports, i am able to achieve this. But is there a way i can get such a report send via e-mail confirming the status of Log shipping configured amongst various DB;s in an instance.

Thanks
Kapil


texts: SQL Server Cluster Failover Making DB Inconsistency Error

texts:
I encountered the following DB tables inconsistency error whenever I do a cluster 'move' over from Server-A to Server-B and then do a DBCC CHECKDB on the database in Server-B:

  SQL Server detected a logical consistency-based I/O error:incorrect
  checksum. It occurs during read of page(1:142) in dtabase ID 4 at
  offset 0x0000000011c))) in file
  'X:\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\MSDB.mdf.

Sometimes, the above DB error may vary and occurred to other DB tables :(

The two servers (A & B) are using Window Server 2008 R2 Enterprise Edition (SP1). Each server has its own NAS (SUN Storage 7410), and using Double-Take Availability to create a single copy of the database across the servers. Also, each server has 2 iSCSI connectors (redundancy purpose) connected to the NAS.

The peculiar thing is the DB inconsistency error did NOT happen when I change either one of the following configuration:

Move from Server-B to Server-A (no DB error on Server-A using DBCC)
Software disable one of the 2 iSCSI connectors on Server-B
Use internal local harddisk, instead of NAS in Server-B (Server-A still using NAS)

Anyone can help to advise me what else can I do to diagnose the problem to Server-B.


texts: Absolute beginner needing some help with PostgreSQL and Python

texts:
I am primarily a developer, with very little experience around databases, and this is my first time using PostgreSQL. I am using Python 3.6 and PostgreSQL with Psycopg2.

After a user enters a membership_number, 3 things should happen:

Save the membership_number with the time and date, and the location of the store, in a table, let's say the search
table.
Retrieve the customer_name associated with that membership_number, if it is already in the users' table.
If customer_name is not present, ask the user for it and save it on the users' table.

Both tables have the customer_id so I can cross check all the times that someone has searched for a particular membership_number, get their name and  other records with a LEFT JOIN.

I plan to have several devices linked to it, so my main concern is race conditions if I write a query that does something like this:

check if membership number is present, if not, ask the user for the customer_name, then save it and then retrieve the fields needed and make some left join to get aditional info.

Even if this is the right way to do it, I don't get how that query would be constructed.

Also, I've read something about temp tables, but should I:

send the membership_number
save to a temp table
update search table
return results if present in users' table
if not, ask store for customer_name
save to temp table
update users' table
return results (from left join)

This doesn't seem right.

Also read something about IF NOT EXISTS, but again, never used it and don't know how to.

Could it possibly be done with just one table? Am I overthinking this? What should be the best way to achieve this?


texts: Temboard: when monitoring a server modifying postgresql.conf on the server is not effective?

texts: I've installed the Temboard tool (http://temboard.io/) to start monitoring my postgresql server. I've noticed that when I modify the postgresql.conf on the server and I restart it, the change is not taken into consideration. If I do it in the Temboard terminal it is. Is it normal? I was supposing that temboard should follow what is done in the postgresql.conf if I modify it on the server. Where temboard agent manages the server config?


texts: how to capture the data using DMV's in sql server 2008 r2?

texts:
Hi i am working on sql server 2008 r2,
i have a situation where i wanted to capture the DMV's data into a table, i would like to know when DMV's tables get refreshed or data flushed out. because as soon as the run going on at the production environment the DMV's data changes. i wanted to capture each data that comes in the DMV's can somebody please help me to capture this data i need this in production to look on CPU,Reads,writes for the data or the query that are running by the testing team.
Thanks! in advanced


texts:
I've been seeing some very basic updates timing out lately and have not been able to determine the cause. An example:

//# Query_time: 51 Lock_time: 0 Rows_sent: 0 Rows_examined: 0

UPDATE photos SET position = position + 1 WHERE (photo_album_id = 40470);

The same log has no entries with a Lock_time > 0. Running show innodb status does not reveal any related locks either. This problem appears to be affecting at least 5 different tables based on my app server logs (which show a Mysql::Error: Lock wait timeout exceeded error related to each corresponding entry in the mysql-slow log).

Any idea on where to go from here? I'm hitting dead-ends in all directions. Thanks.

EDIT:

CREATE TABLE `photos` (
  `id` int(11) NOT NULL auto_increment,
  `type` varchar(255) NOT NULL,
  `photo_album_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `title` varchar(255) default 'Untitled',
  `description` text,
  `credit` varchar(255) default NULL,
  `photo_file_name` varchar(255) default NULL,
  `photo_content_type` varchar(255) default NULL,
  `photo_file_size` int(11) default NULL,
  `photo_updated_at` datetime default NULL,
  `position` int(11) default '0',
  `views` int(11) default '0',
  `folder` varchar(255) default NULL,
  `published` tinyint(1) default '0',
  `published_at` datetime default NULL,
  `created_at` datetime default NULL,
  `updated_at` datetime default NULL,
  `album_published` tinyint(1) default '0',
  `comment_count` int(11) default '0',
  `audio_file_name` varchar(255) default NULL,
  `audio_content_type` varchar(255) default NULL,
  `audio_file_size` int(11) default NULL,
  `audio_updated_at` datetime default NULL,
  `cover` tinyint(1) default '0',
  `slug` varchar(255) default NULL,
  `comments_count` int(11) default '0',
  `delete_from_s3` tinyint(1) default '0',
  `batch` int(11) default NULL,
  `audio` varchar(255) default NULL,
  PRIMARY KEY  (`id`),
  KEY `index_photos_on_album_published` (`album_published`),
  KEY `index_photos_on_batch` (`batch`),
  KEY `index_photos_on_comment_count` (`comment_count`),
  KEY `index_photos_on_created_at` (`created_at`),
  KEY `index_photos_on_delete_from_s3` (`delete_from_s3`),
  KEY `index_photos_on_photo_album_id` (`photo_album_id`),
  KEY `index_photos_on_published` (`published`),
  KEY `index_photos_on_published_at` (`published_at`),
  KEY `index_photos_on_type` (`type`),
  KEY `index_photos_on_user_id` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=42830 DEFAULT CHARSET=utf8


texts: Does changing a parameter value in a stored procedure before the query affect the cardinality estimate?

texts:
I routinely "scrub" the parameters of my stored procedures at the top before I run the query like this:
-- Scrub params
SET @SearchText = NULLIF(@SearchText, '')
SET @ThreadId = NULLIF(@ThreadId, 0)
SET @Hashtag = NULLIF(@Hashtag, '')

But then from this article I read the following:

If the query predicate uses a local variable, consider rewriting the
query to use a parameter instead of a local variable. The value of a
local variable is not known when the Query Optimizer creates the query
execution plan. When a query uses a parameter, the Query Optimizer
uses the cardinality estimate for the first actual parameter value
that is passed to the stored procedure.

Does it count as using a local variable if the value originated from a parameter? I'm wondering if my parameter scrubbing could affect the creation of the query execution plan.


texts: SQL Server 2016 Enterprise version: Requires Windows 2012 Datacenter version?

texts:
We will be installing SQL Server 2016 Enterprise on Windows 2012-R2

Looking at the "Verify Your Operating System" section of:
https://msdn.microsoft.com/en-us/library/ms189910.aspx

It discusses Windows 2008 versions, but not Windows 2012 versions. (I suppose it should also discuss Windows 2016 versions.)

The fundamental questions are:

a) Can I install SQL Server 2016 Enterprise on Win2012-R2 Standard Edition?

b) Can I install SQL Server 2016 Enterprise on Win2016 Standard Edition?

c) In the above setups, can SQL Server access 256GB or more RAM?

d) In the above setups, can I still set up SQL Server Failover Clustering, with one master and two or three read-only failover instances?


texts: where does MySQL store data which is inserted into tables?

texts:
I have a small table named animals in database named test:

mysql> select * from animals;
+----+------------+-----------+
| ID | species    | location  |
+----+------------+-----------+
|  1 | elephant   | India     |
|  2 | polar bear | Greenland |
+----+------------+-----------+
2 rows in set (0.00 sec)

mysql>

Table itself is in animals.frm file:

root@debian:/var/lib/mysql/test# strings animals.frm
PRIMARY
InnoDB
)
species
        location
species
location
root@debian:/var/lib/mysql/test#

However, where is the data in rows stored? I mean 1, elephant, India, 2, polar bear and Greenland. I use InnoDB as a storage engine and MySQL runs under Debian Linux.


texts: How do I attach a database in SQL Server?

texts:
Some common questions relating to attaching databases in SQL Server:

  What does it mean to attach or detach a database?
  How do I detach a database?
  How do I attach a database?

  What does it mean to attach and rebuild the log?

  How do I do it in SQL Server Express?
  When might I consider detaching and attaching?
  Are there any risks or warnings?
  What about attaching between versions and editions of SQL Server? (Standard to Enterprise? 2000 to 2008? 2012 to 2008?)


texts: Using Office 365 as mail server with SSRS 2019 on Server 2019 Azure VM - error message 'Failure sending mail:'

texts:
We have a Windows Server 2019 with SSMA 2019 installed. We also use Office 365.
in the mail settings I have smtp.office365.com, and a the username and password for an account that is set up in our Office 365 instance.
Whenever we try and send reports via email we get the error message 'Failure sending mail: One or more errors occurred.'
The config file shows that it is using port 587.
Are there any extra steps which should be used to get this to work with a Azure VM?


texts: Why does "CREATE TABLE IF NOT EXISTS" lock a table?

texts:
We recently run into "waiting for table metadata lock" issues, after  "CREATE TABLE IF NOT EXISTS" has been fired several hundred times per second.

This is not a request for help (obviously we refactored that code), but I try to understand why that command locks table metadata. It was my understanding that this would be a read-only operation if the table indeed does exist.

Server version: 10.1.37-MariaDB-0+deb9u1 Debian 9.6

Thanks


texts: Convert Postgres TIMESTAMP to TIMESTAMPTZ

texts:
I have a decently-sized (~50k rows) time-series database running on Postgres, with some other structured data (in another database instance) which is much smaller.

Stupidly, when I initially designed the thing I had all the fields as TIMESTAMP WITHOUT TIME ZONE, and now I'm paying for it with annoying time-zone related bugs. I want everything to be explicit, so want to convert the field to TIMESTAMP WITH TIME ZONE. I realise that this doesn't store extra information, and all my timestamps are already in UTC, so the migration should be trivial, but I was wondering if there are any things complications / potential tripping blocks that will prove problematic (this is a production database with customers relying on it)?


texts: mysqld_pre_systemd fails when starting MySQL on Fedora

texts:
I installed MySQL using dnf (I'm on fedora) and from the officials rpm on the website. Both had the same problem : I have an error when I do systemctl start mysqld.service.
So I checked systemctl status mysqld.service and got this :
● mysqld.service - MySQL Server
     Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
     Active: failed (Result: exit-code) since Wed 2021-04-07 17:07:54 CEST; 6s ago
       Docs: man:mysqld(8)
             http://dev.mysql.com/doc/refman/en/using-systemd.html
    Process: 39884 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
    Process: 39913 ExecStart=/usr/sbin/mysqld $MYSQLD_OPTS (code=exited, status=1/FAILURE)
   Main PID: 39913 (code=exited, status=1/FAILURE)
     Status: "Data Dictionary upgrade from MySQL 5.7 in progress"
        CPU: 734ms

avril 07 17:07:53 laptop-de-mathis systemd[1]: Starting MySQL Server...
avril 07 17:07:53 laptop-de-mathis mysqld_pre_systemd[39905]: No such file or directory
avril 07 17:07:53 laptop-de-mathis mysqld_pre_systemd[39906]: No such file or directory
avril 07 17:07:53 laptop-de-mathis python3[39907]: detected unhandled Python exception in '/usr/sbin/semanage'
avril 07 17:07:53 laptop-de-mathis mysqld_pre_systemd[39909]: No such file or directory
avril 07 17:07:53 laptop-de-mathis python3[39910]: detected unhandled Python exception in '/usr/sbin/semanage'
avril 07 17:07:53 laptop-de-mathis mysqld_pre_systemd[39912]: No such file or directory
avril 07 17:07:54 laptop-de-mathis systemd[1]: mysqld.service: Main process exited, code=exited, status=1/FAILURE
avril 07 17:07:54 laptop-de-mathis systemd[1]: mysqld.service: Failed with result 'exit-code'.
avril 07 17:07:54 laptop-de-mathis systemd[1]: Failed to start MySQL Server.

I don't really know what to do with this information. I also checked journalctl, but StackExchange considers it as spam, so I can't post it.
Does anyone have an idea how to get me out of this situation?


texts: Workaround the snapshot isolation in a writable CTE

texts:
The postgres docs say:

  The sub-statements in WITH are executed concurrently with each other and with the main query. Therefore, when using data-modifying statements in WITH, the order in which the specified updates actually happen is unpredictable. All the statements are executed with the same snapshot (see Chapter 13), so they cannot "see" each others' effects on the target tables. This … means that RETURNING data is the only way to communicate changes between different WITH sub-statements and the main query.

I'm looking for a way to work around this effect in a situation like this:

Schema Setup:

create table t1(id serial);

create table t2(id integer);

create function f1() returns integer language sql as $$
  insert into t1 default values returning id; --
$$;

create function f2() returns integer language sql as $$
  with w as (insert into t1 default values returning id)
  insert into t2(id) select id from w join t1 using(id) returning id; --
$$;

Example:

select f2()
/*
|     F2 |
----------
| (null) |
*/

select * from t1
/*
| ID |
------
|  1 |
*/

select * from t2
/*
| ID |
------
*/

SQLFiddle

I'd like the insert in f2 to see the data in t1 and so end up with 1 row in t2.


texts: Is there a standard for naming foreign keys when two columns reference the same table?

texts:
I've always used the name FK_CurrentTableName_ReferencedTableName. I just ran into a situation where I've got a table with CreatedById and ModifiedById both pointing to the same (users) table, so this naming convention won't work.

Is it actually better to change all my reference constraints to FK_CurrentTableName_CurrentColumnName? Or should I keep it the same unless there is a conflict and then do FK_CurrentTableName_CurrentColumnName_ReferencedTableName or FK_CurrentTableName_ReferencedTableName_CurrentColumnName?

I've searched and read contradictory opinions. For SQL Server, is there a standard?


texts: Convert a numeric number to a letter PostgreSQL

texts:
i have this function : https://dbfiddle.uk/?rdbms=postgres_12&fiddle=22a67dedd54291d7ffb527e2f9d482ca
this function retrieve only integer part, i want to retrieve also decimal part.
i tried :  select concat(en_function(2053), ' point',  en_function(21))
but it is not the good approch.
i want to make : select en_function(2053.21) and function return : two thousand fifty-four point twenty-one
how can i do that?


texts:
I have this below query which is behaving bit weird . well Weird in the sense that I couldn't find complete explanation for this.

Version : Sql Server 2008 R2 Enterprise

No fragmentation . Statistics updated with fullscan and for all indexes.

DECLARE @t TABLE (
  subid INT )

INSERT INTO @t
VALUES     (7)--,(3)

SELECT TOP 1 t.QueueItemID
FROM   QueueTable t
WHERE  t.IsProcessed = 0
       AND t.QCode = 'USA'
       AND SubID IN (SELECT SubID
                     FROM   @t)
ORDER  BY t.QueueItemID

Little bit about schema :

Table variable (@t ) is just one column subid .

QueueTable schema is :

CREATE TABLE [dbo].[QueueTable](
    [QueueItemID] [int] IDENTITY(1,1) NOT NULL,
    [SubID] [int] NOT NULL,
    [IsProcessed] [bit] NOT NULL,
    [Qload] [varchar](max) NOT NULL,
    [QCode] [varchar](5) NOT NULL,
    [QDesc] [varchar](max) NULL,
 CONSTRAINT [PK_QueueTable] PRIMARY KEY CLUSTERED
(
    [QueueItemID] ASC
)

This table is big as obvious from varchar(max) type columns in above schema..

There are 2 NC index:

NC index NCx_1 on (isprocessed,qcode) include(queueitemid,subid)
NC index NCx_2 on (subid,isprocessed,qcode)  include(queueitemid)

Total rowcount is around 9 million rows. Group by subid is below :

SubID   RowCount
------  --------
1   68
2   8255571
3   378584
7   5350
11  5318

Rows satisfying condition  (t.IsProcessed = 0 and t.QCode = 'USA' ) are around 350k .

When I run above query it takes 1.5 sec to complete with seek on NC NCx_1 and then scan on table variable. Here is plan.

Above plan is for subid = 11 or 7 in @t table variable
Not sure why it isn't using index NCx_2 (subid,isprocessed,qcode) include(queueitemid) which matches the criteria . It is using index NCX_1 instead .

It appears as it is seeking around 350k rows to satisfy (t.IsProcessed = 0 and t.QCode = 'USA' ) and then filtering out data based on subid column.

I would expect it to first filter out data based on subid column (which would be very less) and then apply other filters which is exactly what is NCX_2 is for.

I tried couple of optimization here that improved performance but want to understand this strange behavior at least to me.

When I add merge join hint in the query then query runs very fast ( 100 ms)
When I add index hint (NCX_2) in the query then also query runs very fast (60 ms)
When I modify query to do MIN(t.QueueItemID) and remove order by query again runs very fast( 60 ms)

Not sure why optimizer not choosing it by default.


texts: Inner Join on Multiple Tables with Table Format

texts:
These are related tables and foreign key is guaranteed to exists. the tables are dynamic but they all starts with name and is appended with a creation number.

So I can have tables like data1 up to data7, data1 up to data51

And while I can format my query in C#. I just feel there is a much better way than adding an Inner Join per table, you may imagine this gets ugly after 10 tables.

All tables have different columns.

SELECT *
FROM data1 as t1
INNER JOIN data2 as t2 ON t1.id = t2.id
INNER JOIN data3 as t3 ON t1.id = t3.id


texts:
I have tried to set up a database role that the users are able to execute or view stored procedures or functions as well as SELECT, INSERT, UPDATE, DELETE data on tables via stored procs. This is what I have done is;

CREATE ROLE  db_execute_procedure_only
GRANT EXECUTE ON SCHEMA::dbo TO db_execute_procedure_only
GRANT  VIEW DEFINITION ON SCHEMA::dbo TO db_execute_procedure_only
DENY CONTROL  ON SCHEMA::dbo TO db_execute_procedure_only

Then set the users database membership to db_execute_procedure_only

Problem is it does not work in SSMS. I have tracked down the problem. If I explicitly state execute as user = 'XXXXXX', I get the desired result. Issue is that SSMS uses CURRENT_USER which still points to dbo which has db_owner rights.

Question is - how can I get CURRENT_USER pointing to a user which has say read/write only access?


texts: Save SSMS tabs with timestamp on a daily basis?

texts:
Is there a way to automate SSMS to automatically save down all current open tabs into a folder with a timestamp?

Whilst I'd like to save every single useful ad-hoc query ever written with a nice understandable name, I often don't have time and this would be rather useful

Ideas?


texts:
In an effort to be more efficient when creating a new index, if there is already an index with the same index keys, but it simply needs additional include columns, is it better to create a second index (again, with the same keys) with different include columns? Or would it be more efficient to the query engine (not to recreate the index) to recreate the index and add the include columns to the original include column set?

If simply adding the include columns to the existing index, does it matter what order the include columns are appended?

Is there a scenario where it would make more sense to create a second index, duplicating the index keys, but with a different set of include columns?

I've attempted to simplify this question in code below.

    CREATE NONCLUSTERED INDEX [IX_Index_A] ON [dbo].[Table]
    ([Col1] ASC)
    INCLUDE ([Col2])

    CREATE NONCLUSTERED INDEX [IX_Index_B] ON [dbo].[Table]
    ([Col1] ASC)
    INCLUDE ([Col3],[Col4])

OR

    DROP INDEX [IX_Index_A] ON [dbo].[Table]

    CREATE NONCLUSTERED INDEX [IX_Index_A] ON [dbo].[Table]
    ([Col1] ASC)
    INCLUDE ([Col2],[Col3],[Col4])


texts: Copying Db permission between same instance

texts:
I have to copy the entire database permissions from database A to database B which has  same objects in database B in same instance.

What are the things that I need to copy from database A to database B to get the same permission as in database A.

Environment: SQL Server 2012 , in same server with same logins.


texts: Running pg_dump on a hot standby server?

texts:
Disclaimer: I admittedly haven't tried this yet, but I'm not sure I would know if it wasn't working correctly, so I wanted to ask.

I would like to run a nightly backup job (via pg_dumpall) from a hot standby server running streaming replication, to avoid putting that load on the primary. I've only seen mention of some gotchas people have run into, e.g. here and here, but very little guidance. It's okay if the backup lags behind the primary slightly, as long as it's consistent (which it should be).

My questions are:

Do I really want to do this, or should the backup be done on the primary server? Why?
When doing a dump on the standby, what settings do I need and procedure should I use to do that correctly? e.g. must I stop replication for the duration of the backup?


texts: unable to add index to column, duplicate entry

texts:
I have a table that collects virtual machines usage metrics, I'm trying to add an index to timestamp column, to speed up queries, when selecting time, this is a sample of the table I have, it has other columns, I have only listed 3 here.

    id  timestamp            vm_name
------  -------------------  ---------
     1  2015-05-05 18:21:50  i-2-20-VM
     2  2015-05-05 18:21:50  i-2-21-VM
     3  2015-05-05 18:21:50  i-2-22-VM
     4  2015-05-05 18:21:50  i-2-23-VM
     5  2015-05-05 18:21:50  i-2-24-VM
     6  2015-05-05 18:21:50  i-2-25-VM
     7  2015-05-05 18:21:50  i-2-26-VM
     8  2015-05-05 18:21:50  i-2-27-VM
     9  2015-05-05 18:21:50  i-2-28-VM
    10  2015-05-05 18:21:50  i-2-29-VM

As you can see, my application is inserting duplicate timestamp entry.

When attempting to add an index to timestamp column, I get the following error message.

Duplicate entry '2015-05-05 18:21:50' for key 'PRIMARY'

I'm not sure what do I need to change in my DB design to resolve this issue.

Your suggestions are highly appreciate it.


texts: When you drop or truncate tables in SQL Server do old data pages get overwritten with new data?

texts:
When you drop tables, does the empty space get automatically reused, or do you need to do some sort of maintenance to allow that space to get reused?

Consider a situation where you drop and recreate a large lookup table routinely. For instance, every day. Is this going to use more and more space until you perform some maintenance like shrinking the data file, or something like that? Or will the old space get overwritten with new data as if it were a hard drive?

SQL 2014 if it makes a difference.


texts:
We using Nagios for our servers monitoring.

Each web-environment have 1 cluster as backend - Cassanra 2.0. Each cluster have 3-5 nodes.

Question is: I want write a plugin for Nagios, for Cassandra's nodes monitoring.
Unfortunately - I'm not so good familiar with Cassandra, and don't sure - what parameters need exactly to be checked?

I'm planing use Nodetool utility to grab data from nodes - but it have a lot of commands, and each provides lot of information (e.g. - cfstats, info, status etc).

So - for monitoring I need get some data about memory each node's usage, used disk space, may be - something else?


texts: How to delete foreign key with lots of references

texts:
Table B have several thousand records that references table A with a foreign key.

The application sends a request to delete the row from table A. It's important that the synchronous operation would be immediate and won't be at risk for timeouts.

If a cascade delete is used, and thus several thousands of records will be deleted as well, could it cause the deletion to take a long time? (not immediate).

Assuming that it could lead to a long deletion operation, what could be done as an alternative?

I've though perhaps to delete the record as deleted and to perform the actual deletion in some background process (where the referencing rows would be deleted before the parent), but this approach feels rather error prone (since all of the existing queries will need to reference this new markedAsDeleted column.

Any ideas?


texts: How make convincing case to DBA to use Oracle external tables for 500 MB to 5 GB files?

texts:
In our data feeds, we have a stack of XML files as well as numerous flat files to put into an Oracle11g database.  We flatten the XML into delimited files, and load the whole set using SQLLoader.

I want to try a proof-of-concept loading via TABLE ORGANIZED EXTERNALLY but I need to make a convincing case to DBA's that it won't do something evil to the server.  The only plausible test files I have are 400-600 MB, in production we'd add some multi-gigabyte files.

What are the risks and how should I approach them, any ideas?

UPDATE: Thanks for all of the helpful comments, folks.  More discussions with have yielded "we can't give you shell access on the database server to load files, we can't mount remote files via NFS" - these are basically security concerns.  We handle PII so DBA's are touchy.  Also, some concerns about who's providing the storage.

Any further suggested 'slam dunk' arguments on why 1) external tables are so much better than sqlloader or 2) why a testbed for external tables is low-risk?

Thanks again,

Andrew Wolfe


texts: What is the best and easiest way to turn off SQL Server when not in use?

texts: Situation: developer workstations that typically just use the client tools to connect to other non-local sql servers. However, there are rare times that having the full version of sql server to do local development will be beneficial. In an effort to not waste system resources running sql server all the time I would like to know the best way to "turn it off" while still allowing usage of client tools (management studio, etc).


texts: How to know why my query times out sometimes, but gets executed other times (random behavior)?

texts:
I look at my code's logs, and see that a query to delete something which occurs like 200 times a day, sometimes times-out. I have no clue to how to debug this. Because it's a very random behavior. Nothing is changed accept that the size of database is growing under operation. This is a pseudo-log:

8/20/2016 9:31:13 AM-Error:
The wait operation timed out
at Repository.UserHistoryRepository.Delete(15);

As you can see, this is the only information that I have. That a delete operation is timed-out at time X. I'm stuck at how to debug this and find out the cause. I don't know even from where to start.

I'm using SQL Server 2014.


texts: The sys.system_internals_partition_columns view on Azure SQL doesn't seem to exist. Is there another view I can use to examine index modifications on a per column basis against an Azure SQL DB?


texts: Is tlog backup on replica clearing VLFs on the primary?

texts:
Is it true that a transaction log backup made on the secondary database is clearing the inactive VLFs on the tlog file on the primary database (in case of Availability Groups)?

I was not able to find an official documentation/statement/article that confirms this. This is the most detailed article I have found, but the next parts that are concerning this aspect are missing.


texts:
What is the best way to monitor activity on each database of my server ?

I tried mtop/mytop which display activity in real time but aren't very practical to see all the queries executed on it.

I activated MySQL logs but there's no option to split them per database.

What tools are you using to monitor MySQL servers ?


texts: Unable to connect to SQL Server from one machine; "an existing connection was forcably closed by the remote host"

texts:
Connecting to the SQL Server (2008 R2 SP3 - KB 4057113) with SSMS version 18 yields:

A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 0 - An existing connection was forcibly closed by the remote host.) (.Net SqlClient Data Provider)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&EvtSrc=MSSQLServer&EvtID=10054&LinkId=20476

Error Number: 10054
Severity: 20
State: 0

Connection failure logging is enabled on the server but nothing gets logged. Please note the for help link is dead. I've tried various database applications. Nothing gets through. The problem appears to be on the server, not the workstation, but other workstations can connect to the server. This workstation can also connect to other servers.

I've got a good guess this is somehow TLS related, because that's what it was last time (server didn't support TLS 1.2 and needed a patch). This time I can't find the problem though.


texts: After Insert Trigger never fires using Import Wizard

texts:
Using Import Wizard I tried importing tables tcc_Block and PROJECT_IDENTITY.

These 2 table structures already exist in SQL Server and they are related with ProjectID columns.  PROJECT_IDENTITY has ProjectID as PK and tcc_Block as FK key.

Any time I import tables, ProjectID in parent table is created and incremented, but the one in child table is always Null.

The trigger never fires!?

-- On tcc_Block table I need to do the update (below) AFTER INSERT
-- Tried Trigger, but none would work.

ALTER TRIGGER [dbo].[InsertTest]
ON [dbo].[tcc_Block]
AFTER INSERT
NOT FOR REPLICATION
AS
BEGIN

    DECLARE @proj int;

    SELECT @proj =max(ProjectID)
    FROM PROJECT_IDENTITY ;

    UPDATE tcc_Block
    SET ProjectID = @proj
    WHERE ProjectID is null ;

END;
GO


texts: Trusted Constraints and NOT FOR REPLICATION

texts:
Version and Build:  SQL Server 2005 SP4 (9.0.5000)

As a disclaimer of sorts, I am asking this question to generate some discussion and/or debate on the topic.  I don't know if there is really a correct answer, or if the correct answer is a big fat "It Depends".  As such, I will be pretty slow to accept an answer, unless definitive evidence is presented that cannot be countered by the opposing view.

The issue deals with the OBJECTPROPERTY CnstIsNotTrusted and the OBJECTPROPERTY CnstIsNotRepl, and how they relate.  The CnstIsNotTrusted property is controlled with the WITH CHECK|NOCHECK while adding or checking a constraint.  The CnstIsNotTrusted property means that the constraint was enabled without checking existing rows; therefore, the constraint may not be true for all rows.  When true, the constraint cannot be used for query optimization.  For example, I have a Person table with a Check Constraint on LastName ensuring LastName LIKE 'Mc%'.  If CnstIsNotTrusted = 1 then if I search for LastName = 'Smith', the query plan will still need to scan/seek the index.  If CnstIsNotTrusted = 0 then the execution plan won't even touch the table.  This has been proven here:

https://sqlserverfast.com/blog/hugo/2007/03/can-you-trust-your-constraints/

The OBJECTPROPERTY CnstIsNotRepl (NOT FOR REPLICATION in DDL) enforces that the constraint will not be checked during replication synchronizations.  More accurately stated, the publisher side will be checked, but the subscriber side will not be checked.  This ensures that the same constraint isn't checked more than once.  However, using NOT FOR REPLICATION will render your constraint untrusted.

My question is, is it worth the extra CPU cycles to not use NOT FOR REPLICATION, in order to sometimes receive a more efficient execution plan?


texts:
A multi-tenancy approach for many webapps is to use a separate PostgreSQL schema for each tenant.
In addition to permissions and access controls, is it possible to encrypt a tenant's whole schema using something like PGP?
In my scenario, the webapp connects to PostgreSQL using a single webapp user account. In other words, tenants won't connect to the database with their own database user credentials.