texts: How to remove Alter, Drop, Create SQL from deployment script created in VS 2015 using SSDT 14.06

texts:
I need to remove the below auto-generated deployment script for my SQL project. I am using VS 2015 with SSDT 14.06. Would this be something that can be set in the Advanced Deployment settings under the Drop tab ? This is for an existing Database deployment.

IF (DB_ID(N'$(DatabaseName)') IS NOT NULL)
BEGIN
    ALTER DATABASE [$(DatabaseName)]
    SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE [$(DatabaseName)];
END

GO
PRINT N'Creating $(DatabaseName)...'
GO
CREATE DATABASE [$(DatabaseName)]
    ON
    PRIMARY(NAME = [$(DatabaseName)], FILENAME =
N'$(DefaultDataPath)$(DefaultFilePrefix)_Primary.mdf')
    LOG ON (NAME = [$(DatabaseName)_log], FILENAME =
N'$(DefaultLogPath)$(DefaultFilePrefix)_Primary.ldf') COLLATE
SQL_Latin1_General_CP1_CI_AS
GO
USE [$(DatabaseName)];

I have the following items all unchecked for my deployment options in the properties:

-- Deploy database properties
-- Always re-create database
-- Block incremental deployment if data loss might occur
-- DROP objects in target but not in project
-- Do not use ALTER ASSEMBLY statements to update CLR types


texts: How to query data between two specific months for all years?

texts:
Need to query the data between two months of all years.

For Example:

Table 1 --> contains 100 records for 10 years

Column --> Created date

Need to pull the data between April to May for all years


texts:
The size of the our PostgreSQL data directory is 100 GByte.

Up to now we use traditional hard disk drives and hardware RAID 10.

Which hard disk drives and RAID setup would give us maximum performance?

The budget is at maximum 5000$.

The load is like in most average databases. Maybe 1/5 writing (update or inserts) 4/5 reading (select).


texts: Oracle Jobs: Can I create a job that will not overlap with itself?

texts:
How can I ensure that a job will never have more than one instance running?

For example, if I have a job that is scheduled to run once a minute, but it actually runs for three minutes, I don't want a second and third instance of the job to start and run concurrently.


texts: Force select * in postgres materialized view definition

texts:
I have a Postgres database and a family of materialised views defined as clones of tables, used for testing and analysis of data so the data won't change during the testing/analysis process like:
create materialized view cloneschema.records_table as
select * from dataschema.records_table;

I would like to force the view definition to retain the select * columns, so that if the underlying schema of dataschema.records_table changes to gain additional columns then those columns also appear in cloneschema.records_table when the materialised view is refreshed.
The current state of affairs is that pg_matviews lists the SQL with only the columns present at time of materialised view creation, so if the materialised view is refreshed it holds only data for those columns.
The main goal is to ensure that additional columns are picked up in testing and analysis depending on these materialised views so they remain full copies of the tables when refreshed. A side advantage would be not needing to consciously DROP CASCADE and rebuild as in this answer.
My Postgres version is PostgreSQL 11.6 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.9.3, 64-bit. I have consulted all questions tagged postgresql and materialized-view on Stack Overflow and DBA and haven't found a related question. I don't in fact find much discussion on the wider Internet of select * constructions in materialised view definitions in Postgres.


texts: Too many TIME_WAIT connections in PostgreSQL

texts:
I am using PostgreSQL 9.0 on Centos 5.7 Final.

At any given there are around 1,500-2,200 connections open for PostgreSQL on port 5432 which are in TIME_WAIT state.

I have a feeling this is not good.
Is this wrong? If yes how to get rid of this?

EDIT

There are mainly 3 applications connecting to the database:

A Server which inserts around 5,000 rows every minute.
A web app.
A reporting server


texts: Checking if an Username is already in use

texts:
I am writing some scripts for my website, and I am currently focussing on checking if a username is already in use. I have tried writing a SELECT script where the datafield is selected and checks if the POSTed data matches.

Here is my code:

$username = $_POST["username"];
$password = $_POST["password"];

{ // Check if account already exists

        $Select_Account = "SELECT ";
        $Select_Account .= "Username ";
        $Select_Account .= "FROM UserData ";
        $Select_Account .= "WHERE UserData.Username ";
        $Select_Account .= "= '$username'";

        $Select_Account_Query = mysql_query($Select_Account);

        if ($Select_Account_Query) {
            echo "Account Exists";
        } else {
            echo "Account does not exist";
        }

    }

I cannot figure out the issue that is preventing the code from ECHOing "Account does not exist" when a value that it inputted is not inside of the table.

Please be aware that I am quite new to writing PHP and MySQL, so I am not fully up to scratch with all the queries and the syntax yet. All help will be greatly appreciated.


texts:
I have made a view in which i use function to get a column something like that

dbo.GetFinalLetter(Id, CASE
        WHEN Id IN (
                ---------
                )
            THEN 1
        WHEN Id IN (
                --------
                )
            THEN 0
        END) as LetterText

so i want to put an other condition that only those results been shown that have text in that column for that purpose i used

where LetterText <> null

where LetterText not null

but it shows error


texts: Multiple calculations in a select statement with CASE

texts:
I have the following query:

select
CASE
    WHEN f.nature = 'box'
    THEN 1
    WHEN f.nature = 'bag'
    THEN 3
    ELSE 0
END AS Pnature,
CASE
    WHEN f.type = 'electronics'
    THEN 0
    WHEN f.type = 'cloths'
    THEN 3
    ELSE 0
END AS Ptype
from product f

Now I want somehow to sum Pnature+Ptype in the select statement and do another CASE i.e. if Pnature+Ptype<100 then 20.

Is there a way to do this?


texts:
As I explained in Set a filegroup to readonly, I wanna set some tables readonly, to have better performance.

These tables are only written by an ETL that runs daily, then they are only read. Therefore, I'll move these tables' indexes to a separate filegroup and keep this filegroup as readonly. When my ETL runs, I must set the filegroup to read-write, run the ETL, then set it back to readonly.

The issue is that, for doing so, I must run ALTER DATABASE MYDB MODIFY FILEGROUP Dimensions READ_ONLY, and for this operation to run there can't be any other transaction running on the whole DB.

ALTER DATABASE MyDB SET SINGLE_USER isn't an adequate solution, because I also can't let my ETL kick/rollback other jobs. It must wait them to finish, keep them blocked while it runs, then let them finish transparently. Also, with that operation any transaction could grab a lock on the database while ETL is running, and block my ETL.

I really don't know the best solution for this, I'm open to any suggestion. My current idea is to request an exclusive lock on the DB, so that I can run ALTER DATABASE MYDB MODIFY FILEGROUP Dimensions READ_ONLY without breaking any other transaction. In the worst case scenario, I'd keep a loop asking for the lock until I find the DB free and grab it.

I don't need the lock/single_user during the whole ETL. Only for setting the filegroup to readonly and read-write.

UPDATE: I'm basing myself on the article The Read Uncommitted Isolation Level:

  For scenarios that demand the very highest levels of consistency guarantees, serializable remains the only safe choice. For performance-critical operations on read-only data (for example, large databases that are effectively read-only between ETL windows), explicitly setting the database to READ_ONLY can be a good choice as well (shared locks are not taken when the database is read only, and there is no risk of inconsistency).


texts:
I'm currently thinking about how to store data that the user might (or might not) change each day. Please note, I'm not a rdb expert and I'm still trying to learn everything.
For example, the user may change his weight today, (2021-04-24) to 80kg then tomorrow change it to 80.25kg, and so on (its also possible to go back in time inside the app and change the weight a month ago for example)
What I'm struggling with is how to save the data in a scalable way.
I know that I might want to add different data_types, for example, the option to change the daily waist measurement.
All options are assuming that the app have about 10K daily active users (not a lot but its just for demonstration sake)
Option 1: Save each day as one row, if I'll need to add new data_type, I'll have to do a ALTER TABLE ADD COLUMN `waist` . Which I know will lock the table and its probably not a great idea in production (or am I wrong?)
CREATE TABLE `daily_data` (
  `date` date NOT NULL,
  `user_id` int NOT NULL,
  `weight` float NOT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`date`, `user_id`)
);

Number of rows per year: 3,650,000.
Option 2:
Split the table in to 2 tables, So now I can easily add a new data_type in production by just adding a new row in data_types
CREATE TABLE `data_types` (
  `type_id` int PRIMARY KEY NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL UNIQUE,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
);

CREATE TABLE `daily_data` (
  `date` date NOT NULL,
  `user_id` int NOT NULL,
  `type_id` int NOT NULL,
  `data` float NOT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`date`, `user_id`, `type_id`)
);

The number of rows per year increases significantly (10,000 * number_of_types * 365).
Lets say that there are 3 types: 10,950,000.
This makes me think if its a good solution because 10K daily active users is not a lot of users but the number of rows is very big.
Option 3:
A bit more efficient because now instead of inserting new row per user per day, I'll insert a new row only when the user actually changes the data.
 CREATE TABLE `daily_data` (
   `id` int PRIMARY KEY AUTO_INCREMENT,
   `user_id` int NOT NULL,
   `start_date` date NOT NULL,
   `end_date` date,
   `type_id` int NOT NULL,
   `data` float NOT NULL,
);

With this solution there are a lot less rows because now potential duplicated data will not occur.
Minimum number of rows: (10,000 * 3) - None of the users ever changed their data
Maximum number of rows: 10,000 * 3 * 365 - Each user changed each data_type one day a year.

What's the best solution?

Is it reasonable to have this many rows for 10K daily active users? what if the app grows to 100K users? is it still fine?

Is there a better solution I'm missing?

Note: the third option refers to Option no.2 schema but can obviously be applied to Option no.1 as well.
My main goal is to be able to retrieve data as quickly as possible for my app.
Any kind of help is much appreciated!! if something is unclear, please comment and I'll clarify.


texts: powershell to connect to SQL and display a friendly message when it cannot connect

texts:
We check our SQL servers using powershell. The script goes through all servers in a text file and sends the output to a text file when it can connect. But when it cannot connect, it displays error in the PS window and jumps to the second server in list. Problem with this is,when we schedule this script to run (unattended), we don't get a report of any failure in the text file.
What I really want it to do is....try to connect to a SQL server, if it can, then send output to file and if it can't then write something like "Failed to connect to servername" to the file.
Following is the script I am using as an example

function checkSQLRestart([string] $Hostname )
{

    $con="server=$Hostname;database=master;Integrated security=sspi"
    $da=New-Object System.Data.SqlClient.SqlDataAdapter ("

            declare @ServerRestartDate datetime
            set @ServerRestartDate = (select crdate from master..sysdatabases where dbid=2)
            if(@ServerRestartDate>GETDATE()-1)
                print @@servername +' was restarted in last 24 hours. Restart date/time = '+cast(@ServerRestartDate as varchar(20))

                                        ",$con)
    $dt=New-Object System.Data.DataTable
    $da.fill($dt)|Out-Null
    $svr
    $dt|Format-table -AutoSize |Out-File $OutputFile -Append

}

I tried using things like

if($da -eq -1)
{
     write-host "failed to connect"
}

But it didn't work.

Any help please?


texts: database setup for posts table with multiple types

texts:
Struggling with how to set this up for speed and scalability.

So basically I have a bunch of posts that depending on what type of post it is, it could have different input values that I would need to store. So what I am thinking of is storing the common values together in a "posts" table and the storing the Type specific values all in separate Type specific tables... Is that scalable? especially when I want to pull say ALL posts?

what I am thinking below:

Tables:

posts:
id, type_id, commonValue1(varChar), commonValue2(int), commonValue3(boolean), commonValue4(boolean)

Type1:
id, post_id, Type1SpecValue1(varChar), Type1SpecValue2(int), Type1SpecValue3(boolean)

Type2:
id, post_id, Type2SpecValue1(int), Type2SpecValue2(varChar)

Type3:
id, post_id, Type3SpecValue1(varChar), Type3SpecValue2(int), Type3SpecValue3(int), Type3SpecValue4(boolean), Type3SpecValue5(varChar)

This will obviously expand as time goes on and I will add more types ... any ideas? The other thought was I just create different Type tables with all the values in them (even the common values) and then just have a posts table for when I need to pull multiple types? thoughts on how to set this up?

---Update---

Just to clarify those specific values could be int, varchar etc. or else I would just do a many to many with items table but I don't think that will work with different field types.


texts: fsync() calls significantly higher on MySQL 5.7 as compared to MySQL 5.6

texts:
We have 3 database servers currently in production running MySQL 5.6. One of these is a master and two are slaves. SHOW ENGINE INNODB STATUS shows the following, with regard to disk IO:

Master (5.6)

132061643 OS file reads, 1193737510 OS file writes, 98583725 OS fsyncs
61.10 reads/s, 16384 avg bytes/read, 555.17 writes/s, 26.38 fsyncs/s

Slave 1 (5.6)

486391505 OS file reads, 1092015482 OS file writes, 79181501 OS fsyncs
275.37 reads/s, 16384 avg bytes/read, 451.09 writes/s, 38.58 fsyncs/s

Slave 2 (5.6)

473261340 OS file reads, 1019795312 OS file writes, 76065920 OS fsyncs
264.04 reads/s, 16384 avg bytes/read, 432.69 writes/s, 32.44 fsyncs/s

As part of a project to upgrade the hardware and also upgrade to MySQL 5.7, there's now two new servers. One of these is a master and the other a slave. The new master replicates from the old master. SHOW ENGINE INNODB STATUS shows the following, with regard to disk IO:

New Master (5.7)

416755 OS file reads, 1630038 OS file writes, 1179258 OS fsyncs
142.42 reads/s, 16384 avg bytes/read, 466.80 writes/s, 278.04 fsyncs/s

New Slave (5.7)

470747 OS file reads, 1424367 OS file writes, 1055024 OS fsyncs
140.95 reads/s, 16384 avg bytes/read, 284.91 writes/s, 216.26 fsyncs/s

As you'll see... fsyncs/s is significantly higher on the 5.7 servers. This is causing significant increases in IOPS and the new servers are barely keeping on top of replication.

We've disabled sync_binlog on 5.7 to give it some parity with the default setting in 5.6 (off). Beyond that, the my.cnf is the same on both servers.

Can anyone speculate on why fsyncs/s is significantly higher in 5.7 vs 5.6?


texts: Displaying Parent Child Information, With Certain Parent Columns Only Shown Once

texts:
For a TSQL report, I have a one-to-many parent-child relationship.

I want to report both parent and child relationships in the same report, like so, with the parent columns on the left and child columns on the right:

ParentName | ParentNum | ParentIncome | ChildName | ChildNum | ChildAllowance
John       | 1         | 50000        | Johnny    | 1        | 5
Jane       | 2         | 55000        | Jackie    | 2        | 10
Jane       | 2         |              | Billy     | 3        | 5
Jane       | 2         |              | Sally     | 4        | 5
Jackie     | 3         | 90000        | Monique   | 5        | 0

I want to avoid duplicating certain columns, like ParentIncome, because users dump the report output into Microsoft Excel and I want to minimize the chances of them summing columns incorrectly.

Therefore, for each parent, I want to only display ParentIncome once (ParentNum and ParentName OK to repeat.). For rows other than the first row, display a blank cell (i.e., no NULL values).

Currently, I produce this report using a wrapper around a subquery. The wrapper looks like this for all the columns:

CASE WHEN DATA.ROW_NUMBER = 1
     THEN DATA.Field1
     ELSE '' END AS 'AColumn'

The subquery orders things using OVER and PARTITION BY and ORDER BY, so when ROW_NUMBER = 1, it's always a new parent.

It seems like this is something others have run into, though...in TSQL, can I get the same results in a simpler way, without the extra outer wrapper query to format things?


texts: What is the difference between terminating an Oracle sql statement with semicolon or with a slash on the next line?

texts:
The following two queries seem to be equivalent when executed in sql*plus:

1.

select * from user_tables;

2.

select * from user_tables
/

Is there any difference between both versions?


texts: Unknown sticky Problem after MySQL upgrade

texts:
few weeks ago we upgraded mysql server from 5.5 to 5.6
on 1st server few days ago (5May) I reboot mysql server , after this customer reported that some database data table not load to their site . (mostly phpfox - also few in other scripts) - also new data not submitted to database
I was reinstall mysql - disable cloudlinux - cagefs ... but problem not resolve. also rebuild php
after above , I move that database to remote-mysql-test-server with same Mysql version (5.6) and problem resolved. So I found this problem mysql related
yesterday in an other server like above (server B - that mysql upgraded from 5.5 - 5.6 few weeks ago) when mysql server restart , same problem occurred . for example when I answer ticket (WHMCS) , this date not submitted to ticket ... some clients can't update their site .

few website error like this :
SQL] [1364] Field 'stat_time' doesn't have a default value
STRING: INSERT INTO pn_statistics (stat_name,stat_value) VALUES ('Day-1394-02-24','0')

also in this server when I move database to remote-mysql-test-server this problem resolved.

I speak with few system admin and they can't find my issue .

Any suggestion ?


texts: Backing up database with missing datafile

texts:
Is there a way to backup a Oracle 12c database which has a missing datafile?

I dont have backups. Database is running in archive log mode. One of the data files is physically missing from the disk.

But querying dba_data_files, it shows up as a datafile and when I try to backup using RMAN or expdp, it throws an error and fails.

What are the options I have got here?

Oracle12c/RHEL 6


texts: Postgres query plan shows sequential scan instead of a index scan with on the fly data

texts:
My use case is I am trying to select rows based on a list of known text values. For example, I have known text values as 'Frasier' and 'Cheers' and I want to select some rows that match the values.

EXPLAIN
    WITH lcaseid AS (
        SELECT lower(icaseid)
        FROM (
                 VALUES ('Frasier'),
                        ('Big brother')) AS i (icaseid)
    )
    SELECT *
    FROM products.catalog
    WHERE lower(id)
              in (
              SELECT *
              FROM lcaseid);

Hash Semi Join  (cost=0.10..12.76 rows=2 width=587)
  Hash Cond: (lower(catalog.id) = lcaseid.lower)
  CTE lcaseid
    ->  Values Scan on ""*VALUES*""  (cost=0.00..0.03 rows=2 width=32)
    ->  Seq Scan on catalog  (cost=0.00..12.29 rows=129 width=587)
            ->  Hash  (cost=0.04..0.04 rows=2 width=32)
            ->  CTE Scan on lcaseid  (cost=0.00..0.04 rows=2 width=32)

Why is Postgres doing Sequential Scan here? There is an index on lower(id):

CREATE UNIQUE INDEX lower_case_id ON products.catalog ((lower(id)));

When I do this instead, Postgres shows an Index Scan:

EXPLAIN
    SELECT
        *
    FROM
        products.catalog
    WHERE
            lower(id)
            in('frasier');

Index Scan using lower_case_id on catalog  (cost=0.14..8.16 rows=1 width=587)
  Index Cond: (lower(id) = 'frasier'::text)

The problem with the second approach is that I have to have the text values to be in lowercase because I can use them in the in clause. The first approach solves this problem by lower-casing when creating the temporary table lcaseid.

My guess is the temporary table is to blame for the sequential scan? Because Postgres does not have statistics on the temporary table, it simply does a sequential scan.

Also how should I go about solving the problem of selecting based on lowercase text values optimally?

Table definition:

CREATE TABLE "products"."catalog" (
    "id" text NOT NULL,
    "is_original" bool NOT NULL,
    "image_url_1" text NOT NULL,
    "image_url_2" text NOT NULL,
    "image_url_3" text NOT NULL,
    "image_url_4" text NOT NULL,
    PRIMARY KEY ("id")
);

CREATE UNIQUE INDEX lower_case_id ON products.catalog ((lower(id)));

Postgres version: 9.6.12


texts: Space concerns for unique constraint on row for time-series db (PostgreSQL / TimescaleDB)

texts:
I was wondering if there is a more space-efficient way of doing unique row constraints on time-series DBs.  Adding a normal unique key for the entire row will result in an index the same size or larger than the table itself.
Since I'm dealing with time-series data, this unique index really doesn't have to contain more than the last hour or days worth of data, since no older data will be
I only need the constraint to ensure duplicate data isn't written.  Are there any useful alternatives?  Note I do not wish to query for every insert as I am continuously writing data.
EDIT: Since this is a TimescaleDB and the table is stored in chunks, is there a way to create the index for the current chunk only, and drop the index for older chunks?


texts: What is the overhead for an array in Postgres?

texts: What is the additional overhead of an array compared to a normal column of that same datatype? In other words, if an array will almost always have one value in it, how much space would I be "wasting" by using an array instead of a normal column?


texts: Database Schema for Blank Database is large. 20MB

texts: Is it normal for blank databases with just schema to be large? Or is there a way that I can reduce the size of the initial database?


texts: Resuming Replication w/ Database Mirroring when Principle is unavailable

texts:
I'm trying to setup a disaster recovery scenario and I'm stuck at this particular point.  Here's what I've done so far:

  A) Created principal database on Server A and and mirror on Server B

  B) Setup the Distribution database on Server C and modified the
  agent profiles with –PublisherFailoverPartner to Server B.

  C) Created two replication publishers on the principal.

  D) Setup a subscriber on Server D to the two subscriptions.

  E) Verified my subscriber was getting updates.

At this point, I shut down Server A hosting my principal database.  I then ran ALTER DATABASE  SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS on Server B to get my mirror database online.  With the old principal Server A still offline, I modified a record on a published table.  The change never made it to Server D, my subscriber.

I checked replication monitor and it shows no errors.  But on the Publisher to Distributor History it says 'Replicated transactions are waiting for next Log backup or for mirroring partner to catch up.'

I understand that when I ran the FORCE_SERVICE_ALLOW_DATA_LOSS that my mirror became my principal and my old principal became the mirror (which will sync back to my new principal when brought online).

What I'd like to have happen is replication function like normal even while the Server A is down.  Then when it comes back, shut down the Server B and re-run the FORCE_SERVICE_ALLOW_DATA_LOSS command on Server A and have my original principal server back without tons of work.

Is this possible?  Does anyone have any advice outside of removing database mirroring after the initial failover (to get replication to work)?  Any alternate solutions?  What happens when Server A comes back online (regardless of whether mirroring was left in tact) with regard to the –PublisherFailoverPartner switch.  Does replication automatically go back to Server A?


texts: Multiple PostgreSQL instances for one PGDATA folder

texts:
Is it possible to have multiple PostgreSQL instances for one PGDATA folder, shared in a storage for example?

And if is not, is there is some kind of RDBMS capable of something like this?


texts: How to understand what Columns Have Changed in Merge Replication

texts:
I am using merge replication in SQL 2012. Some of my merge articles use column level tracking.

I am trying to understand how to use the 'lineage', and 'colv1' fields in the MSmerge_contents table to work out which columns have changed in a particular row in my database.


texts:
One of the table I have looks like this

CREATE TABLE orders_suppliers_contacts
(
  supplier_id bigint NOT NULL,
  name character varying(64) NOT NULL,
  phone character varying(18),
  notes character varying(2000),
  last_modified timestamp without time zone NOT NULL,
  CONSTRAINT "FK_orders_suppliers_contacts" FOREIGN KEY (supplier_id)
      REFERENCES orders_suppliers (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE
)

I need to change the field phone character varying into contacts contactinfo[] whose custom type is defined as

CREATE TYPE contactinfo AS (
    tag VARCHAR(8),
    "value" VARCHAR(64)
)

What would be the insert query for that?

INSERT INTO orders_suppliers_contacts VALUES
(1, 'John Smith', ???, null, NOW());

Also, once retrieved, the data is sent through a web API as JSON, will the column need to pass through a JSON function or is it already compatible? The required output (HTTP response) should be, something like

[{
  "supplier_id": 1,
  "name": "John Smith",
  "contact": [{"tag":..., "value":...}, ...],
  "notes":...,
  "last_modified":...
}, ...]

for a query such as SELECT * FROM orders_suppliers_contacts.


texts: Raise application error in a stored procedure when inputs contain keywords like 'delete' , 'insert','update'

texts:
I have a Stored Procedure which gets 6 input queries and the inputs are the same as where clause of a query. For instance where x=1 and z=5 and e=4. The Application makes these input strings and pass it onto my procedure.This is a part of my procedure in which I'm checking the inputs in order to validate the content of each input query so that they do not contain keywords like "select", "update", "delete" or equivalents.I want to know whether there are better ways to do that.
create or replace procedure app_error_test(query1 nvarchar2,
                                           query2 nvarchar2,
                                           query3 nvarchar2,
                                           query4 nvarchar2,
                                           query5 nvarchar2,
                                           query6 nvarchar2) is
queryconcat nvarchar2(30000);
begin

--**************** validate content of input queries

queryconcat := lower(nvl(query1, '') || nvl(query2, '') ||
                     nvl(query3, '') || nvl(query4, '') ||
                     nvl(query5, '') || nvl(query6, ''));

 if (queryconcat like '%drop%' or
      queryconcat like '%delete%' or
       queryconcat like '%execute%' or
        queryconcat like '%truncate%' or
         queryconcat like '%create%'  or
          queryconcat like '%update%'  or
           queryconcat like '%insert%') then

     RAISE_APPLICATION_ERROR(-20032, 'ILLEGAL CONTENT');
  end if;
end;

Thanks in advance


texts: Improve the performance of ST_Within boundary lookups in PostGIS

texts:
I'm trying to improve the performance of a georeferencing feature in our application. For every address in our system, we need to store which of several boundary types it is located within (local government area, electoral boundaries and so on).

The lookup process I'm using seems very slow for what it is. In this test dataset of 690 rows, and 150 relevant boundaries, the query takes a bit over 5 seconds on my iMac:

UPDATE testdata
SET ELB = (
    SELECT gc.id
    FROM geo_choice gc
    JOIN geo_choice_list gcl ON gc.geo_choice_list_id = gcl.id
    WHERE gcl.name = 'FEDERAL_ELECTORATE'
      AND gc.geo_choice_list_version_id = gcl.current_version
      AND ST_Within(ST_Point(testdata.lon, testdata.lat), gc.geom)
      LIMIT 1
)

This version takes just over 9 seconds:

UPDATE testdata
SET ELB = gc.id
FROM geo_choice gc
JOIN geo_choice_list gcl ON gc.geo_choice_list_id = gcl.id
WHERE gcl.name = 'FEDERAL_ELECTORATE'
      AND gc.geo_choice_list_version_id = gcl.current_version
      AND ST_Within(ST_Point(testdata.lon, testdata.lat), gc.geom)

And this version, using CROSS LATERAL (inspired by this blog post), is about 5.5:

UPDATE testdata t1
SET ELB = gc.id
FROM testdata t2
CROSS JOIN LATERAL (
    SELECT gc.id
    FROM geo_choice gc
    JOIN geo_choice_list gcl ON gc.geo_choice_list_id = gcl.id
    WHERE gcl.name = 'FEDERAL_ELECTORATE'
      AND gc.geo_choice_list_version_id = gcl.current_version
      AND ST_Within(ST_Point(t2.lon, t2.lat), gc.geom)
    LIMIT 1
) AS gc

There is a spatial index on the geo_choice table:

CREATE INDEX geo_choice_gix ON public.geo_choice USING gist (geom) TABLESPACE pg_default;

I can confirm the index is being used: if I used _ST_Within, to negate it, the query takes over 4 minutes.

Is there a faster way to do this kind of boundary lookup, or something else I'm missing?


texts: Restoring Oracle prod backup to test server with new database name

texts:
I need to restore an Oracle production database (aaprod) to a test server that already is home to a test database (aatest) under a new name (bbtest).  I have a cold backup of the production database

Questions:  What is the appropriate approach to restoring this production database to a test server under a new database name using the cold backup?

Easy to do with SQL Server, Oracle is a new gig.


texts: Does the principle of having an "ever increasing index" also apply for NVARCHAR and VARCHAR?

texts:
This might sound like an amateur question, but I just need to be 100% sure and I haven't found anything on it online.

It is considered good practice having an "ever increasing index". Now, does that rule also apply to NVARCHAR and VARCHAR?

Let's say I insert "abc", then "def" and then "ghi". Is this considered "increasing"?

Note: I am well aware that it is bad convention to use an NVARCHAR and VARCHAR as index.


texts: How to properly shutdown Windows while MongoDB is running

texts:
I am using Mongodb version 3.2.6 as my application database.  MongoDB is installed as a SYSTEM service in Windows and my application runs in Kiosk mode.  When shutting the system down by performing a clean Windows shutdown process, I am getting error in event log showing unexpected shutdown of mongo.  I also see in the logs that journaling is running and recovering the database every time.

I first attempted to add a log off script via gpedit using "net stop mongo db"

After learning that this is not a good method, I changed to "mongo admin --eval shutdownServer()".  I am continuing to get mdmp files and seeing journaling performing recovery.

Any advice? A colleague believes this is a bug in MongoDB.  Thoughts?


texts:
Suppose I have a database schema with 3 tables - Area, Work and Author.

Area is a table which contains the list of Areas of interest of an Author / Areas in which a Work has been published, for example Math, Economics, Computer Science.

Author is a list of authors.

Work is a list of published articles.

Work_Area is a many to many relationship describing the area(s) of a Work. For instance Work with ID 1, can be on Math and Economics.

Work_Author is a many to many relationship between Work and Authors. For instance Work with ID 1 can have Authors with ID 1 and 2.

Author_Area is the list of interests of an Author.

Suppose An Author has Math has an interest and he publishes an Article on Math then there is no problem.

Instead an Author who has Math as an interest publishes an Article on Economics. Then my database becomes inconsistent in the following sense:-

When I look up the interests of this Author in the table Author_Area then I get ONE result Math.
When I join the Author with Work_Author and further join this with Work_Area and extract the list of all Areas in which the author has published work then I get TWO results - Math and Economics.

My query is : How can I prevent this inconsistency in my database?

Also, what is this type of inconsistency called ? Does it have a name in the literature?


texts: Writing something then immediately read when using read/write split: how to deal with replication lag

texts:
We have an issue where we write to the master table, then immediately must read using the reader. We are experiencing issues with replication lag where the data is not available to the reader at the time we execute our read statement (our replication lag is around 18 ms, which seems pretty fast so I don't think it's a problem with having too much lag).

What patterns are there to deal with this problem? So far our solutions are:

Just use the writer to read
Put logic in our code to wait for replication to occur

Are there any other ways of dealing with this?


texts: How to benchmark disk performance from within SQL Server?

texts:
I know of tools such as SQLIO and diskspd to benchmark IO/disk performance, but need something that can be run from the SQL Server instance itself via T-SQL, as I'm working on a Cloud-hosted environment (AWS RDS) and do not have access to the server itself.

There is this article on using sys.dm_io_virtual_file_stats to monitor file IO but I need something that can be run to generate some IO load on demand on multiple servers and generate some kind of report I can use to compare.

Heavy analytical SQL queries with identical execution plans are taking x2 (or more) the time in different servers. I'd like to run this benchmark in order to try to to make sure that's the best this instance type can do in terms of I/O.


texts: VACUUM FULL restored very small amount of disk space

texts:
I have a large table with ~130m entries. I recently removed unnecessary entries and it should be left with ~60m.
I ran VACUUM(FULL) [table_name] and after it finally finished it restored only 2GB of disk space, while I expected it to be ~20GB. Also it seems that only 10m entries were removed after vacuuming. The VACUUM(FULL) finished successfuly and it had enough disk space.
What could cause this? Can this occupied space be recovered somehow?


texts:
I'm seeing the following behavior from a shard in mongo:

mongos> db.fs.chunks.getShardDistribution()

Shard SH0
 data : 740.29GiB docs : 5003098 chunks : 32949
 estimated data per chunk : 23MiB
 estimated docs per chunk : 151

Shard SH1
 data : 800.67GiB docs : 5413760 chunks : 32948
 estimated data per chunk : 24.88MiB
 estimated docs per chunk : 164

Shard SH2
 data : -188212B docs : 3083 chunks : 37
 estimated data per chunk : -5087B
 estimated docs per chunk : 83

Totals
 data : 1540.96GiB docs : 10419941 chunks : 65934
 Shard SH0 contains 48.04% data, 48.01% docs in cluster, avg obj size on shard : 155KiB
 Shard SH1 contains 51.95% data, 51.95% docs in cluster, avg obj size on shard : 155KiB
 Shard SH2 contains -0.01% data, 0.02% docs in cluster, avg obj size on shard : -115628438B

I'm attempting to drain SH2, but 37 "jumbo" chunks are left. This is a gridfs collections sharded on files_id, n - it's hard to believe these chunks are actually jumbo, but I haven't been able to do anything to split them (I've tried everything mentioned in the other relevant SO questions about splitting jumbo chunks).

Any ideas, any clue why this is showing up like this?

EDIT

sh.status() outputs:
mongos> sh.status()
--- Sharding Status ---
  sharding version: {
    "_id" : 1,
    "minCompatibleVersion" : 5,
    "currentVersion" : 6,
    "clusterId" : ObjectId("546bf1a7c0671b92b546fb90")
}
  shards:
    {  "_id" : "sh0",  "host" : [redacted] }
    {  "_id" : "sh1",  "host" : [redacted] }
    {  "_id" : "sh2",  "host" : [redacted],  "draining" : true }
  balancer:
    Currently enabled:  yes
    Currently running:  no
    Failed balancer rounds in last 5 attempts:  0
    Migration Results for the last 24 hours:
        8 : Success
        1 : Failed with error 'migration already in progress', from syn-sh_1 to syn-sh_0
  databases:
   .....
    {  "_id" : "Files",  "partitioned" : true,  "primary" : "sh0" }
        Files.fs.chunks
            shard key: { "files_id" : 1, "n" : 1 }
            chunks:
                syn-sh_0    32967
                syn-sh_1    32968
                syn-sh_4    37
            too many chunks to print, use verbose if you want to force print

EDIT 2
When attempting to move, Mongo thinks the chunk size is approx 1200 Petabytes

{
    "cause" : {
        "chunkTooBig" : true,
        "estimatedChunkSize" : NumberLong("1284608926708044072"),
        "ok" : 0,
        "errmsg" : "chunk too big to move",
        "$gleStats" : {
            "lastOpTime" : Timestamp(0, 0),
            "electionId" : ObjectId("553034c33e622e6efa8c0a6c")
        }
    },
    "ok" : 0,
    "errmsg" : "move failed"
}


texts: Error when running job using SSIS package

texts:
I have  SQL Server 2008R2 installed on my Windows server 2008.
I created a package using the Import and Export Wizard from file system.
After that I created a job (SQL Agent Job ) that used the SSIS package I created in the first step.

After I run this job I would receive the following error:

  Date,Source,Severity,Step ID,Server,Job Name,Step
  Name,Notifications,Message,Duration,Sql Severity,Sql Message
  ID,Operator Emailed,Operator Net sent,Operator Paged,Retries Attempted
  10/10/2017 17:38:12,update job,Error,0,DBServer\ABC,update job,(Job
  outcome),,The job failed.  The Job was invoked by User
  mydomain\myuser.  The last step to run was step 1 (update step
  1).,00:00:10,0,0,,,,0 10/10/2017 17:38:12,update
  job,Error,1,DBServer\ABC,update job,update job,,Executed as user:
  mydomain\myuser$. Microsoft (R) SQL Server Execute Package Utility
  Version 10.0.6000.29 for 32-bit  Copyright (C) Microsoft Corp
  1984-2005. All rights reserved.    Started:  5:38:12 PM  Error:
  2017-10-10 17:38:17.80     Code: 0xC00291EC     Source: Drop table(s)
  SQL Task 1 Execute SQL Task     Description: Failed to acquire
  connection "DestinationConnectionAdoNET". Connection may not be
  configured correctly or you may not have the right permissions on this
  connection.  End Error  Error: 2017-10-10 17:38:17.81     Code:
  0xC00291EC     Source: Preparation SQL Task 1 Execute SQL Task
  Description: Failed to acquire connection
  "DestinationConnectionAdoNET". Connection may not be configured
  correctly or you may not have the right permissions on this
  connection.  End Error  DTExec: The package execution returned
  DTSER_FAILURE (1).  Started:  5:38:12 PM  Finished: 5:38:17 PM
  Elapsed:  5.578 seconds.  The package execution failed.  The step
  failed.,00:00:10,0,0,,,,0

I did some research and I found that the SSIS service was not installed.
I installed SSIS and I can connect to it using SQL Server Management Studio.
After that I created new job using same file but still I received same error as before.
Please advise how to fix this problem


texts: Move live SQL Server database to a new server

texts:
I have a production server running SQL Server 2008 Web Edition, and it has to keep running 24x7 with almost no down time, and it's the time for us to move to a more advanced server hardware, also it's a good chance to move to SQL Server 2012 Enterprise.

Now my question, how can I do this migration without taking the database down at all? Like for example setting something between the two servers as replication or so, so I have exactly identical database up-to-second on both servers, then I can just take the main services down for 1 minute, changing the IP address of the database connection from old server to new server and I be sure that the new server has identical copy of the old one.

I can't go with backup/restore approach as it will take time more than acceptable period, that's why I need something which guarantee identical databases exactly as if I am doing backup/restore. 5 minutes could be the max affordable downtime.

The database size is about 100GB.

New server is running 2012 Enterprise, and old server is running 2008 Web

I can exceptionally archive records from the database and minimize its size down to 20GB like.

I have the option to upgrade my SQL Server 2008 Web edition to Enterprise edition if this will help with more solutions, like replication maybe?


texts: SQL Linked Server Connection Success but simple query returns Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'

texts:
I linked a SQL Server in Management Studio and when I test the connection, I do get "the test connection to the linked server succeeded." However, when I try to run a simple query, I get a message

  Msg 18456, Level 14, State 1, Line 1
  Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

In my query I am using the linked server name.database.schema.table.

Any suggestions will be appreciated. I am using SQL Server Management Studio 2017


texts: MySQL command denied for DELETE with JOIN

texts:
I have a DELETE query that joins three tables. I have explicit GRANTS for the DELETE on the three tables, but not the database. I'm getting a ERROR 1142 (42000): DELETE command denied to user error when the query runs. I suspect this is happening because of the joins (even though the user has grants to delete from all three tables). I can delete from any one of the tables without issue but with the join, it fails. Does anyone know if there is a way to work around this, allowing the query to succeed?

Edit: more information

The query that I'm attempting to run is:

DELETE FROM t1, t2, t3
USING d1.table1 t1
LEFT JOIN d1.table2 t2 ON t1.id = t2.t1_id AND t2.t2_start_date = 1481554800
LEFT JOIN d1.table3 t3 ON t3.t2_id = t2.id
WHERE t1.xyz = 18

The error I'm getting:

SQLSTATE[42000]: Syntax error or access violation: 1142 DELETE command denied to user 'username'@'ip_address' for table 'table1'

The grants I have in place are:

GRANT SELECT, INSERT, UPDATE, DELETE ON `d1`.`table1` TO 'username'@'%'
GRANT SELECT, INSERT, UPDATE, DELETE ON `d1`.`table2` TO 'username'@'%'
GRANT SELECT, INSERT, UPDATE, DELETE ON `d1`.`table3` TO 'username'@'%'


texts:
I have simple table called ips with 2 field ip and address, which schema is following

CREATE TABLE IF NOT EXISTS `ips` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `address` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `ips_address_unique` (`address`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=2;

in table there are some IP inserted

INSERT INTO `ips` (`id`, `address`) VALUES (1, '123.123.123.123');
INSERT INTO `ips` (`id`, `address`) VALUES (1, '124.124.124.124');
INSERT INTO `ips` (`id`, `address`) VALUES (1, '125.125.125.125');

How to create query that return 123.123.123.123 first time, 124.124.124.124 second time, 125.125.125.125 third time, 123.123.123.123 fourth time and so on...?

The only one solution that comes to my mind is idea of storing id of last returned IP in memory and then query for ip with id greater than stored. Anyway, I'm going to repeat that query multiple times in a second. It will be inefficient.


texts: How to view the latest entries in all tables in an Oracle DB?

texts:
I wanted to examine a running Oracle instance on one of our servers, but nobody knows, if there's still any data of interest stored in the database.

Unfortunately our last Oracle admin has left our small company and so I try to figure out, if there's still any valuable data left.

But I can't really distinguish between system and user content, i.e. what is an example table/database and which tables where created by our ex employee?

I searched for log files but I wasn't really successful, so I thought about using a select about the last 100 (or more) updated that where made in the tables, so that I maybe get a clue what went on in the database.

It's running on Windows2000 and I have the system account.

Thanks for any hints.


texts: Msg 1833, File 'ABC.ndf' cannot be reused until after the next BACKUP LOG operation

texts:
I am having a Database.
The size of my database is near about 4GB(Only .mdf file).
In that database only few tables present which have the maximum data of database. One of them and the biggest table is Table1. This is a huge table means everyday near about 80000 data inserted and same no. of records deleted. There are 30 days * 80000 records present in that table. This table is used very frequently. Hence it takes time to query large data. So my development team decided to create Partition of same table. Table11 has four columns in primary key.
Primary Key Columns :

ChequeDate Date
RunNo Int
SorterNo Int
SequenceNo Int

We decided to partition the table on the basis of SequenceNo column.

I created a partition script and ran it which partitions an existing table. This works perfectly.
Next I run a "rollback" script to remove the partitioning. This also runs perfectly.

When I try to reapply partitioning on the same table it gives me the error mentioned in title. I found a manual solution that worked for me.

My problem is this process should run from a script only. No manual work should be necessary.

I assume that the problem is in the rollback script:

ALTER TABLE [dbo].[Table1] DROP CONSTRAINT [PK_Table1] with (move to [primary]);
DROP PARTITION SCHEME Table1PartSch
DROP PARTITION FUNCTION Table1PartFunc

ALTER DATABASE [MyDataBase] REMOVE FILE Table125LData;
ALTER DATABASE [MyDataBase] REMOVE FILE Table117LData;
ALTER DATABASE [MyDataBase] REMOVE FILE Table17LData;
ALTER DATABASE [MyDataBase] REMOVE FILE Table12LData;
ALTER DATABASE [MyDataBase] REMOVE FILE Table1RestData;
GO
ALTER DATABASE [MyDataBase] REMOVE FILEGROUP Table125LFG;
ALTER DATABASE [MyDataBase] REMOVE FILEGROUP Table117LFG;
ALTER DATABASE [MyDataBase] REMOVE FILEGROUP Table17LFG;
ALTER DATABASE [MyDataBase] REMOVE FILEGROUP Table12LFG;
ALTER DATABASE [MyDataBase] REMOVE FILEGROUP Table1RestFG;
GO

If someone have better solution other than Partitioning, to this situation is Welcome.


texts: Troubleshooting non-working Plan Guide in SQL Server 2008 R2

texts:
I have problems with a Plan Guide I have created in Microsoft SQL Server 2008 R2. The Plan guide is purposed for a performance issue in Microsoft Dynamics AX 2009 and forces a specific question to use a TABLE HINT index. However, when I perform a SQL Profiler Trace, the execution plan still uses two other indexes (one of them is a clustered). The Plan Guide itself works only when the parameters @p1 and @p2 are specified in the question, not for random parameter values. I suspect the problem can be related to how variables are sent from Dynamics AX. I have double checked that the SELECT statement is exactly the same as the one found in Profiler. Do you have any advice how to troubleshoot this problem? In my world, it should really work.

T-SQL USED FOR CREATING NON WORKING PLAN GUIDE

EXEC sp_create_plan_guide
    @name = N'PLAN_GUIDE',
    @stmt = N'SELECT TOP 1 A.COLUMN01,...............A.COLUMN35 FROM TABLE A WHERE ((COLUMN20VALUE=@P1) AND (COLUMN25VALUE=@P2))',
    @type = N'SQL',
    @module_or_batch = NULL,
    @params = N'@P1 nvarchar(4),@P2 nvarchar(20)',
    @hints = N'OPTION (TABLE HINT (A, INDEX(I_708COLUMN20)))'

T-SQL FOR CREATING PLAN GUIDE (THAT WORKS) WITH PARAMETER VALUES IN THE SELECT QUERY (@params commented out)

EXEC sp_create_plan_guide
    @name = N'',
    @stmt = N'SELECT TOP 1 A.COLUMN01,...............A.COLUMN35 FROM TABLE A WHERE ((COLUMN20VALUE='abc') AND (COLUMN25VALUE='L3355'))',
    @type = N'SQL',
    @module_or_batch = NULL,
    --@params = N'@P1 nvarchar(4),@P2 nvarchar(20)',
    @hints = N'OPTION (TABLE HINT (A, INDEX(I_708COLUMN20)))'


texts: Why does msdb.dbo.backupset have entries for each database every 15 minutes?

texts:
I have 134 separate databases on a SQL Server.

Every 15 minutes an entry appears in the msdb.dbo.backupset table for each database on the server.

Why does this happen every 15 minutes?
What is causing this to occur?
How do I stop these entries from happening?
Does anyone else see this type of activity on your MSDB database?


texts: How can I take backup MySQL database from one cloud server to another?

texts:
How can I take backup (by mysqldump) MySQL database from one cloud server to another?

I have a MySQL server that uses innodb and is about 150GB size.

I want to take backup of database to other server, what's the most efficient way to do this?


texts: SQL Server single query takes longer by end of week

texts:
SQL Server 2008 R2.  We are reporting off a DB-Amp backup of our SalesForce instance.
I have a SQL query that takes about 8 seconds to run on Monday.  By Friday the duration is 31 seconds and my app is giving up a second too early.  By the next Monday, the query is performing quickly again.
Here is the SQL
I don't have any Jobs running weekly that do any cleanup.  Rebuilding indexes and sp_syspolicy_purge_history are nightly jobs.
I checked Profiler and nothing else is happening while the query is running slowly.
I don't see any User Temp tables that were created before today (Friday).
I have another similar query that never slows down.
Any ideas?!


texts:
I'm trying to figure out why this query doesn't work, the goal is to insert an item, then return its ID and use that ID to get related data from another table, however it returns an empty record set

WITH inserted as (
    INSERT INTO food_update(diary_id, food_id, value)
    VALUES($1, $3, $4) RETURNING id )
SELECT * FROM food_update INNER JOIN food ON food.id = food_update.food_id where food_update.id=(select id from inserted)

However,

WITH inserted as (
        INSERT INTO food_update(diary_id, food_id, value)
        VALUES($1, $3, $4) RETURNING id )
(select id from inserted)

If I change it to the query above, it returns the newly inserted ID, then when I run SELECT * FROM food_update INNER JOIN food ON food.id = food_update.food_id where food_update.id=(select id from inserted) (second part of the first query) with the newly inserted ID in a separate query it works, but they don't seem to work together. Am i not seeing something obvious, or is there a better way to do this?

I can provide the schema however it's a very general question really i'd just like to know why the insert doesn't seem to return the newly created sequence value. One other way of doing it was to use CURRVAL, though I'm not exactly sure how concurrency works with postgres so i wasn't sure if two queries ran at the same time it may get the second ones ID, though i'm pretty sure the tables meant to be locked while the query is being executed?

As requested here's a schema and a minimal example so that my issue is isolated + anyone that wants to test it can have a look. This implies there's an active database with a public schema

Schema creation + records

CREATE TABLE public.food (
    created timestamptz NULL,
    modified timestamptz NULL,
    id bigserial NOT NULL,
    "name" text NULL,
    CONSTRAINT food_pkey PRIMARY KEY (id)
);

CREATE TABLE public.food_update (
    id bigserial NOT NULL,
    created timestamptz NULL,
    modified timestamptz NULL,
    food_id int8 NULL,
    value numeric(10,2) NULL,
    CONSTRAINT food_update_pkey PRIMARY KEY (id),
    CONSTRAINT food_update_food_id_fkey FOREIGN KEY (food_id) REFERENCES food(id)
);

INSERT INTO public.food
(created, modified, id, "name")
VALUES(NULL, NULL, 1, 'TEST');

This is the query (slightly modified version of mine), and it shows that it still returns nothing.

WITH inserted as (
    INSERT INTO food_update(food_id, value)
    values($1, $2) RETURNING id )
SELECT * FROM food_update INNER JOIN food ON food.id = food_update.food_id where food_update.id=(select id from inserted)


texts: See entire string from a lengthy text field in pgAdmin?

texts:
Using pgAdmin, how can I see the entire string value for a row when the value is very long?

For example, the value may be comma-separated values (CSV) content with hundreds or thousands of characters. Furthermore, the value may contain newline characters.

When doing queries in pgAdmin’s SQL tool, the results pane only shows a few dozen characters, and then suppresses the display of any more. Is there some way to see all of the text value?

Any way to export the results of a query to a text file or a text window perhaps?


texts: MySQL query show times in milliseconds, not seconds?

texts: In psql, with \timing on, query times are displayed in milliseconds + hundredths of milliseconds. In MySQL, they are only displayed in seconds + hundredths of seconds. Is there a way to get MySQL to display them in milliseconds too (I'm trying to compare queries, but the small times just come out as zero in MySQL, which isn't very helpful).


texts: how do I ensure that users in AlwaysOn Availability Group only connect to the secondary, even in a failover

texts: I have an Always On Availability Group with a read-only secondary set up. We're running SQL Server 2016 Enterprise Edition SP1 CU4. Users are supposed to only connect to the secondary using the ReadOnly ApplicationIntent when connecting to the listener. I don't want to remove their logins from the primary since this would prohibit access after a failover. I am looking at perhaps running a SQL Agent job that checks connections and kills sessions if they are running on the primary but it seems like there should be a better way to do that than running a job every minute or so! Do I need to look at creating an audit or is there some other mechanism to accomplish this?


texts: For AlwaysOn AGs, can a secondary replica cause contention on the primary when the resources of the secondary are maxed out?

texts:
Is it possible and in what ways can the secondary replica cause contention against the primary replica when the secondary replica's resources are maxed out (e.g. does the primary "lock" up while waiting on the secondary to catch up on synchronizing)?

Does it make a difference if the Availability Mode of the secondary replica is set to Asynchronous commit vs Synchronous commit?


texts: How can I keep two tables in two different databases with different schemas the same in real-time?

texts:
I have two databases A, and B, which will reside on the same server. A and B have different schemas. Database B will contain different numbers of tables, and sometimes tables with the same name will contain extra columns in database B compared to A.

For some of the tables in Database B, Whenever its corresponding table in Database A is changed, the table in B needs to be updated immediately to reflect that change. In the case of extra columns in the Database B table, I might need to write additional logic to populate them.

If Database B cannot be updated, I want the update to Database A to fail too.

How could I set up such a scheme? Can I set up a trigger to automatically send the same update/insert/delete statement to Database B? Is there another way?

BTW, Database A = Sql server 2000, Database B = Sql server 2008.

Thanks in advance!

Background: There is a huge database of ~500 tables. There are many frontends that use this big database. I'm replacing the frontends one at a time. To prove that it integrates with all the legacy frontends I need to run a second system in parallel with their production system. To keep double data entry to a minimum I want to copy table modifications from the production system for all tables that aren't updated by my replacement frontend.


texts: Postgres deadlock in INSERT VALUES concurrent with ON DELETE CASCADE

texts:
I create tables like this:

CREATE TABLE tgroups (
    group_id text PRIMARY KEY,
    vacancy_id text
);
CREATE INDEX tgroups_vacancy_id_idx ON tgroups (vacancy_id);

CREATE TABLE tscores (
    application_id text NOT NULL,
    group_id text NOT NULL,
    CONSTRAINT tscores_pkey PRIMARY KEY (application_id, group_id),
    CONSTRAINT tscores_group_id_fkey FOREIGN KEY (group_id) REFERENCES tgroups(group_id) ON DELETE CASCADE
);
CREATE INDEX tscores_group_id_idx ON tscores(group_id);

I then have two processes executing SQL transactions in very rapid succession. Both have the standard READ COMMITTED isolation level. The first process is running this, except the values in the INSERT...VALUES are in a random order (I've done it in Python):

BEGIN;
    DELETE FROM tgroups WHERE vacancy_id = 'vacancy1';

    INSERT INTO tgroups (group_id, vacancy_id) VALUES
    ('group1', 'vacancy1'),
    ('group2', 'vacancy1'),
    ...
    ('group10', 'vacancy1');
COMMIT;

The second process runs the following SQL. Again, the values in INSERT...VALUES are in a random order. The variable :application_id is chosen from the set ["application1"..."application10"], which I've done with Python (I guess you could achieve the same in pgsql):

BEGIN;
    DELETE FROM tscores WHERE application_id = :application_id;

    INSERT INTO tscores (application_id, group_id) VALUES
    (:application_id, 'group1'),
    (:application_id, 'group2'),
    ...
    (:application_id, 'group10');
COMMIT;

This sometimes deadlocks:

sqlalchemy.exc.OperationalError: (psycopg2.extensions.TransactionRollbackError) deadlock detected
DETAIL:  Process 527 waits for ShareLock on transaction 391860; blocked by process 526.
Process 526 waits for ShareLock on transaction 391861; blocked by process 527.
HINT:  See server log for query details.
CONTEXT:  while deleting tuple (2,218) in relation "tgroups"
 [SQL: "DELETE FROM tgroups WHERE vacancy_id = 'vacancy1';"]

I can only reproduce the deadlock if both of the following are true:

The deletes in tscores from the first transaction are carried out behind the scenes by ON DELETE CASCADE. If I remove the constraint and manually delete the rows in question at the beginning of the transaction there is no deadlock
The values inside the insert statement are in a random order

I was under the impression that both DELETE and INSERT...VALUES were atomic, and that ON DELETE CASCADE happened at the same time as the DELETE it is related to. Therefore I'd have thought that if one transaction was already in process, the blocked transaction would block on DELETE until the other transaction finished, and then continue, and indeed this is what seems to happen if I manually run the two transactions line-by-line in separate psql sessions.

Somehow I clearly misunderstand the way that locks, inserts, foreign keys and cascades interact.

NB I am aware sometimes the INSERT into tscores will sometimes fail because the group that is referenced has just been deleted, but this isn't my problem (though I guess it could be related).


texts: MySQL Update a table based on the last data from another table while grouping with conditions

texts:
I need a query to UPDATE a series of fields based on another table

Table lp_plates_backup

|       uid | brand    | model    |
|         1 |          |          |
|         a | Old      | Error    |
|       ... | ...      | ...      |

Table lp_pictures_backup

| plate_uid | brand    | model    | date_validated      |
|         1 | Fiat     | Panda    | 2014-10-11 10:03:20 | < Last one
|         1 | BMW      | 7-Series | 2014-08-04 11:21:18 |
|         1 | BMW      | 7-Series | 2014-07-28 19:14:02 |
|         1 | Mercedes | S-Class  | 2014-06-12 08:54:57 |
|         a | Tesla    | Model S  | 2014-12-17 11:00:00 | < Last one
|         a | BMW      | 3-Series | 2014-11-07 14:34:11 |

I need to update columns brand and model of lp_plates_backup based on the last date_validated entry of table lp_plates_backup while lp_plates_backup.uid match lp_pictures_backup.plate_uid for each lp_plates_backup.uid but only if:

lp_pictures_backup.brand is not empty
lp_plates_backup.brand is different from lp_pictures_backup.brand OR lp_plates_backup.model is different from lp_pictures_backup.model

The result should shown (lp_plates_backup) :

|       uid | brand    | model    |
|         1 | Fiat     | Panda    |
|         a | Tesla    | Model S  |

I have read these posts and made lot of tests but without getting the expected result:

https://stackoverflow.com/questions/1973246/how-to-specify-the-parent-query-field-from-within-a-subquery-in-mysql
https://stackoverflow.com/questions/17038193/select-row-with-most-recent-date-per-user
https://stackoverflow.com/questions/11156163/optimize-sub-query-selecting-last-record-of-each-group

Here is the last query I try:

UPDATE
    lp_plates_backup AS t
    LEFT JOIN (
        SELECT
            plate_uid, brand, model, date_validated
        FROM
            lp_pictures_backup as parent
        WHERE
            brand <> '' AND
            date_validated = (SELECT MAX(date_validated)
                 FROM lp_pictures_backup as t2
                 WHERE t2.plate_uid = parent.plate_uid)
        GROUP BY
            plate_uid
    ORDER BY
        date_validated DESC

) AS m ON
    m.plate_uid = t.uid
SET
    t.brand = m.brand,
    t.model = m.model
WHERE
    t.brand <> m.brand
    OR
    t.model <> m.model

I think I had too much brainstormed and getting confused making useless too complicated code.

Any expert in the place?

Thanks for helping.


texts:
I have a query regarding user permission on a stored procedure.

If a user that does not have permissions to create tables in a database, but it does have permissions to create and execute stored procedures

If those stored procedures create tables (as part of the SQL code that is executed) then, will the user get an error when executing the stored procedure when it tries to create a table?


texts:
Apologies if this isn't the norm, but I am seeking advice for the database design for 'dog rescue/adoption'.

I've done my normalization research and this is my first attempt at database design. Any tips/feedbacks/criticism?

The trickiest part was dealing with dogs/users/organisations and representing who owns a dog.

a dog can belong to an organisation.
An organisation can have many organisation users.
Think animal shelter/pound
a dog can belong to an organisation user. think a one man band with a large property housing multiple dogs at a time
a dog can belong to a single user, not attached to any organisation. think someone moving overseas and they need to find their dog a new owner

So, the dog table has to have either an organisation ID or a user ID attached to it, or both. Perhaps a trigger stating that one must be not null.


texts: Environment Varible PATH exceeding recommended length solution?

texts:
I am attempting to install Oracle Database 11g on a Windows 7 64x laptop.

I get all the way to step 5 of 8 and I get a fail on Environment variable path "exceeds recommended length"...

I have created a C:\ODB directory and have set ORACLE_HOME=C:\ODB

I have shortened it as much as possible.

What is the correct way to install Oracle Database 11g on Windows 7?


texts: I have Postgres installed on windows 10. I have master-slave replication ( 1 pc is master and 2 other pc are slaves). I have archive logs saved on a file on master pc and slaves are connected to that file. That file has become quite big and i need to know how to remove log files with pg_archive cleaner. I also have automatic backup jobs on every day.


texts: How to add days to a date in PostgreSQL?

texts:
I have two tables and I want to execute an update similar to this:

UPDATE table1 set table1.date = table2.date + table2.numberOfDays
FROM tabel2
WHERE table1.id = table2.table1_id

Edit: table1.date and table2.date is a date and table2.numberOfDays is a integer


texts: Optimized Way of Scheduling a Differential Backup

texts:
I am working with a data warehouse with SQL Server 2012 and was wondering what would be the most optimized, AUTOMATED procedure for a backup/restore strategy.

Current observations and limitations:

1) Cannot use transaction logs as it would affect my load performance - datasets are potentially huge with large transactions

2) Current plan is to do full backup every week and differential backup every day

I am not sure when DML operations will happen as it depends on my application's usage, but is there a way to just track the NUMBER of changes to a database that would trigger a differential backup? A way that would not affect performance? I do not want to be taking unnecessary differential backups.

Would Change tracking be a good solution for my scenario? Or would there be overhead involved? I do not need to know the actual data that was changed, just the fact that it was changed by a certain amount.

Thanks in advance!


texts: DATA BACKUP from SQL Server Express to SQL Server 2012

texts: Can I restore the backup data from SQL Server Express to SQL Server, both 2012 versions?


texts: How to find the nearest lat and lang in postgresql

texts:
I am trying find the nearest lang and lat from the specified lat and lang
i tried the below mentioned query, in my query i have mentioned the place muscat's lat and lang ,so i want to display the nearest cities of muscat with limit 3 along with muscat

CREATE TABLE IF NOT EXISTS tablename(_id SERIAL NOT NULL UNIQUE PRIMARY KEY,city VARCHAR, Lat NUMERIC, Lang NUMERIC);

select * from (
SELECT  *,( 3959 * acos( cos( radians(6.414478) ) * cos( radians( lat ) ) * cos( radians( lang ) - radians(12.466646) ) + sin( radians(6.414478) ) * sin( radians( lat ) ) ) ) AS distance
FROM tablename
) al
where lat >  23.61  OR lang >  58.54
ORDER BY distance
LIMIT 1;

here is my table (Assumed data not exact lat and lang)data

| id | City      |  lat      | lang    |
----------------------------------------
|  1 | muscat    | 23.61     | 58.54   |
|  2 | sur       | 22.566    |59.52    |
|  3 | Muhafazat |23.585     |58.40    |
|  4 | ZZZ       | 5.8       | 7       |
|  5 | AAA       | 9.22      | 5       |
|  6 | Barka     |23.613     |58.592   |

Expected out put:

    | id | City      |  lat      | lang    |
    ----------------------------------------
    |  1 | muscat    | 23.61     | 58.54   |
    |  2 | sur       | 22.566    |59.52    |
    |  3 | Muhafazat |23.585     |58.40    |
    |  6 | Barka     |23.613     |58.592   |


texts: How to resolve TNS error in Oracle 11g when connect with Navicate)

texts:
I want to connect Oracle11g with Navicate, but when I make a connection he display a error

  Ora-12514: TNS: Listener does not currently know of service requested in connect descriptor.


texts: Can't SHOW GRANTS after MySQL re-install

texts:
I had to completely re-install MySQL on one of the slaves and then rebuild it via a sql dump (we have 1 master and 2 slaves). I tried to SHOW GRANTS for one of the users, but keep getting the following error:

  ERROR 1141 (42000): There is no such grant defined for user 'USER' on host '%'

I can SHOW GRANTS for root though. SHOW GRANTS needs a SELECT privilege for the mysql database, which root has:

mysql> show grants;
+---------------------------------------+
| Grants for root@localhost                                                             |
+---------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*dfdfdfdf' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION                                           |
+-----+
2 rows in set (0.01 sec)

Btw, the privileges themselves exist, as I can select them from mysql.user table:

mysql> select update_priv, delete_priv from mysql.user where user like 'USER%';
+-------------+-------------+
| update_priv | delete_priv |
+-------------+-------------+
| Y           | Y           |
+-------------+-------------+
1 row in set (0.00 sec)

What am I missing?

Edit by RolandoMySQLDBA 2013-01-15 21:06 EDT

1) Please run SELECT VERSION(); to tell me what version of MySQL you are using

2) Please run SELECT COUNT(1) from information_schema.columns WHERE table_schema='mysql' AND table_name='user';

3) Please tell me from what version of mysql did the mysqldump come from.


texts:
Following is my query.

Select [A],[C]
      ,CASE WHEN [A]>19999 and [A]<30000 THEN '12' ELSE '21' END AS 'dummy1'
    ,Substring(CAST(C AS varchar(9)),1,4) as YEARDummy
    ,Substring(CAST(C AS varchar(9)),5,2) as MONTHDummy
    ,Substring(CAST(C AS varchar(9)),7,2) as DAYDummy
    ,CASE WHEN MONTHDummy>0 and DAYDummy<31 THEN '30dayas' ELSE 'xyz' END AS Dummy4
From TableYearlyData

I want to do conditional/processing on the dummy data column to create new dummy column. How can I use a dummy column to create a new dummy column?


texts:
I have a SQL query that runs in MS SQL Server and it has grouping(), rank() and rollup() functions / keywords in it. The query is given below.

How can this be translated to PostgreSQL?

select top 100
    sum(ss_net_profit)/sum(ss_ext_sales_price) as gross_margin
   ,i_category
   ,i_class
   ,grouping(i_category)+grouping(i_class) as lochierarchy
   ,rank() over (partition by grouping(i_category)+grouping(i_class)
                             ,case when grouping(i_class) = 0 then i_category end
                 order by sum(ss_net_profit)/sum(ss_ext_sales_price) asc
                ) as rank_within_parent
 from
    ssv_itemv_storev_IJGBAgg
 where
    d_year = 2001
    and s_state in ('TN')
 group by rollup(i_category,i_class)
 order by
    lochierarchy desc
   ,case when (grouping(i_category)+grouping(i_class) = 0) then i_category end
   ,rank_within_parent;


texts: Is there a way to get the max,count, or etc for all columns without naming them individually?

texts:
Normally if you want to find something across all columns you have to

select max(col1),max(col2),max(col3)...max(col99) from blah;

Is there a way to do this without naming every column manually?


texts:
I'm rather new at DW design and am working on a DW to model some IT infrastructure.

The major problem/question at this point is how to model drive information.

We will be collecting aggregate data on files and folders, and separate data on physical drives.  Drive info will include total and free space at a minimum and will be updated several times a week.

One of the business questions that will need to be answered is how drive usage is trending over time.  The drive information will also be used in a hierarchy leading down to the file/folder level as well.

The options that I can see now are:

Implement DRIVE as a Dimension

Simplifies hierarchy design
Will this cause issues with reporting?  It seems counter-intuitive to me to report time-bound data on a dimension only
It also seems problematic to have a dimension that you KNOW will change every time you refresh your data

Implement DRIVE as a Fact Table

Simplifies reporting
Complicates hierarchy(?) - I'll be using Drive to map data back to a specific server or computer as well.  Is it OK to use a fact table as an intermediate level in a hierarchy?  I don't think it is.

Implement DRIVE as both a Fact and Dimension

Fact will contain just the key, date, and facts on space
Dimension will include other non-additive data like what computer it's on, etc.
Seems to resolve both issues, but is this an anti-pattern?


texts: Visualize the effects of a cascading delete or update?

texts:
In MySQL, I'm working with a database that has integrity constraints on foreign keys (nothing overly special here).

Is there a method to somehow visualize the effects of a cascading delete or update?

I would like to somehow see all records that will be effected, the whole chain of it, across all involved tables, preferably (but not a hard requirement at all) before they happen. Is this possible?

Update
I'm trying to estimate the impact of cascading deletes/updates, seeing the result would greatly speed up my understanding.


texts:
Currently I am using ola hallengren  backup and maintenance scripts.

I have a question:

I have 5 big databases in one server. It's taking approximate 10+ hours everyday to complete full backups. Currently it's writing in sequentially into disk.

I want to write all backups parallel to reduce time. Is there any way I can write backups parallel?

I am taking compressed and verify only backups to network location


texts:
I will have a huge PostgreSQL 9.3 database with many tables with more than 100M entries per table. This database will be basically be read-only (once I fill all the necessary tables and build the indexes no more write operations on the DB) and single-user access (run and benchmark multiple queries from localhost), since the DB will be used only for research purposes. Queries will always use JOIN on integer DB fields.

I will probably buy a SSD (256-512GB) for this purpose. I have not used an SSD for a DB before, so is there anything I should be afraid of? Can I put the entire DB on the SSD, or just the indexes? Is there any particular advice / tutorial required for tuning PostgreSQL for SSDs? Note, that I have a good workstation with an i7 and 32Gb of RAM, so perhaps you can offer some advice there too.


texts: Speed difference between Drop table and Truncate table in Postgres

texts:
I currently have a program that inserts into a database by creating temp tables, filling the tables, then merging that data into the main tables. Then dropping the tables and doing it all again.
I'm wondering what the speed difference is if instead of drop and create, if I just truncate.


texts: Setup Master-Slave replication with DML changes in Slave

texts: I have a master database (PostgreSQL 9.0) and I need to setup a streaming replication on a slave. Slave db can read/write and make schema level changes too which shouldn't write back to master. Can you please highlight how can I do this?


texts:
I have looked around at other solutions, but none seems to work quite right.
What I am trying to do is to get the latest result (sorted by date) from a selection, grouped together by sample.

Here is my partial ERD (excuse the ascii):

                  +--------+
                  | sample |
                  +--------+        +--------+
+--------+      ->| ID     |        | batch  |
| result |     /  | text   |        +--------+
+--------+    /   | batch  |------->| ID     |
| ID     |   /    +--------+        | text   |
| text   |  /                       +--------+
| sample |--      +--------+
| test   |---     | test   |
| date   |   \    +--------+
+--------+    --->| ID     |
                  | text   |
                  +--------+

And the DDL:

create table result (resultid int(11), resulttext varchar(20), resultsample int(11), resulttest int(11), resultdate datetime);
create table sample (sampleid int(11), sampletext varchar(20), samplebatch int(11));
create table batch (batchid int(11), batchtext varchar(20));
create table test (testid int(11), testtext varchar(20));

insert into batch(batchid, batchtext) values(12759, 'batch 12759');
insert into sample(sampleid, sampletext, samplebatch) values(99787, 'sample 99787', 12759);
insert into sample(sampleid, sampletext, samplebatch) values(99786, 'sample 99786', 12759);
insert into test(testid, testtext) values(2144, 'test 2144');
insert into result(resultid, resulttext, resultsample, resulttest, resultdate) values(1, "Pass", 99787, 2144, "2018-02-01 11:02:18");
insert into result(resultid, resulttext, resultsample, resulttest, resultdate) values(2, "Fail", 99787, 2144, "2018-02-01 09:01:34");
insert into result(resultid, resulttext, resultsample, resulttest, resultdate) values(3, "Pass", 99786, 2144, "2018-02-01 08:06:12");

Here is an SQL query (just to see whats there):

select
    resulttext,
    resultdate,
    sampleid
from
    batch Right Join(
        sample Right Join(
            test Right Join
            result On testid = resulttest
        ) On sampleid = resultsample
    ) On batchid = samplebatch
where
    batchid = 12759 and
    testid  = 2144
order by
    resultdate desc

Here is an example of my result set (from the above query):

resulttext resultdate          sampleid
---------- ----------          ---------
Pass       2018-02-01 11:02:18 99787
Fail       2018-02-01 09:01:34 99787
Pass       2018-02-01 08:06:12 99786

So I wish to sort on resultdate (get the latest one) before grouping by sampleid. My query is currently:

select
    *
from (
    select
        resulttext,
        resultdate,
        sampleid,
        batchid,
        testid
    from
        batch Right Join(
            sample Right Join(
                test Right Join
                result On testid = resulttest
            ) On sampleid = resultsample
        ) On batchid = samplebatch
    order by
        resultdate desc
) x
where
    batchid = 12759 and
    testid  = 2144
group by
    sampleid

But the result set I get is incorrect:

resulttext resultdate          sampleid
---------- ----------          ---------
Pass       2018-02-01 08:06:12 99786
Fail       2018-02-01 09:01:34 99787

It should have got the 11:02 result (where sampleid=99787), but it gets the wrong one. Can someone help with my query?

I am using MariaDB 15.1


texts:
number of opened tables is increasing rapidly after sometime.

MariaDB [(none)]> show global status like 'open%';
+--------------------------+--------+
| Variable_name            | Value  |
+--------------------------+--------+
| Open_files               | 6      |
| Open_streams             | 0      |
| Open_table_definitions   | 1200   |
| Open_tables              | 1200   |
| Opened_files             | 791203 |
| Opened_plugin_libraries  | 0      |
| Opened_table_definitions | 788523 |
| Opened_tables            | 807692 |
| Opened_views             | 0      |
+--------------------------+--------+
9 rows in set (0.00 sec)

MariaDB [(none)]> show global status like 'up%';
+---------------------------+-------+
| Variable_name             | Value |
+---------------------------+-------+
| Update_scan               | 200   |
| Uptime                    | 2028  |
| Uptime_since_flush_status | 2028  |

how global status like '%conn%';
+-----------------------------------------------+-------+
| Variable_name                                 | Value |
+-----------------------------------------------+-------+
| Aborted_connects                              | 2     |
| Connection_errors_accept                      | 0     |
| Connection_errors_internal                    | 0     |
| Connection_errors_max_connections             | 0     |
| Connection_errors_peer_address                | 0     |
| Connection_errors_select                      | 0     |
| Connection_errors_tcpwrap                     | 0     |
| Connections                                   | 151   |
| Max_used_connections                          | 13    |
| Performance_schema_session_connect_attrs_lost | 0     |
| Slave_connections                             | 0     |
| Slaves_connected                              | 0     |
| Ssl_client_connects                           | 0     |
| Ssl_connect_renegotiates                      | 0     |
| Ssl_finished_connects                         | 0     |
| Threads_connected                             | 12    |
| wsrep_connected                               | OFF   |
+-----------------------------------------------+-------+

 select count(*) from information_schema.tables;
+----------+
| count(*) |
+----------+
|     1986 |
+----------+

Can anyone suggest how to investigate why the opened tables count is increasing so rapidly and how to resolve this issue?


texts:
I have a very important query in my system that is taking too long to execute due to huge amount of data on tables. I'm a junior DBA and i need the best optimization I can get for this. Tables have approximate 80 million rows each.

Tables are:

tb_pd:

   Column            |  Type   | Modifiers | Storage | Stats target | Description
---------------------+---------+-----------+---------+--------------+-------------
 pd_id               | integer | not null  | plain   |              |
 st_id               | integer |           | plain   |              |
 status_id           | integer |           | plain   |              |
 next_execution_date | bigint  |           | plain   |              |
 priority            | integer |           | plain   |              |
 is_active           | integer |           | plain   |              |
Indexes:
    "pk_pd" PRIMARY KEY, btree (pd_id)
    "idx_pd_order" btree (priority, next_execution_date)
    "idx_pd_where" btree (status_id, next_execution_date, is_active)
Foreign-key constraints:
    "fk_st" FOREIGN KEY (st_id) REFERENCES tb_st(st_id)

tb_st:

 Column |          Type          | Modifiers | Storage  | Stats target | Description
--------+------------------------+-----------+----------+--------------+-------------
 st_id  | integer                | not null  | plain    |              |
 st     | character varying(500) |           | extended |              |
Indexes:
    "pk_st" PRIMARY KEY, btree (st_id)
Referenced by:
    TABLE "tb_pd" CONSTRAINT "fk_st" FOREIGN KEY (st_id) REFERENCES tb_st(st_id)

My query is:

select s.st
from tb_pd p inner join
     tb_st s on p.st_id = s.st_id
where p.status_id = 1 and
      p.next_execution_date < 1401402110830 and
      p.is_active = 1
order by priority, next_execution_date
limit 20000;

With the indexes I have, the best I got was:

Limit  (cost=1.14..263388.65 rows=20000 width=45)
   ->  Nested Loop  (cost=1.14..456016201.43 rows=34627017 width=45)
         ->  Index Scan using idx_pd_order on tb_pd p  (cost=0.57..161388942.77 rows=34627017 width=16)
               Index Cond: (next_execution_date < 1401402110830::bigint)
               Filter: ((status_id = 1) AND (is_active = 1))
         ->  Index Scan using pk_st on tb_st s  (cost=0.57..8.50 rows=1 width=37)
               Index Cond: (st_id = p.st_id)

I cannot understand the explain very well but it's not using the idx_pd_where to filter the where clause. The idx_pd_where have all the columns used in where clause.

More info about data:
status_id is 95% = 1
is_active is 90% = 1
next_execution_date is in millis and varies a lot. The value compared is the moment of the execution (current time in millis)

Should I create separate indexes for each filtered column or use any different kind of indexes? Maybe some configuration on DBMS?


texts: Does the order for clustered index have the same constraints as non-clustered index on search?

texts:
I have a table filled with a bunch of data, and I want to use the following clustered index:

[account_id, group_id, run_id, page_id, date]

For a non-clustered index, if I were to to use WHERE filters involving account_id, page_id, and date_time, I know that the index won't work because I'm not also including group_id and run_id. Does the same thing apply when using a clustered index? I often want to make a query in the form:

SELECT *
FROM my_table
WHERE ad_account_id = %d
  AND page_id = %d
  AND date >= DATE(%s)
  AND date <= DATE(%s)

But sometimes I have queries such as:

SELECT *
FROM my_table
WHERE ad_account_id = %d
  AND group_id = %d
  AND date >= DATE(%s)
  AND date <= DATE(%s)

or

SELECT *
FROM my_table
WHERE ad_account_id = %d
  AND group_id = %d
  AND run_id = %d
  AND date >= DATE(%s)
  AND date <= DATE(%s)

It's not obvious whether any of these take advantage of the clustered index past the one given on the account_id (I'm pretty sure this isn't doing a full table scan but even there not sure).


texts: SQL query to return a column per value in value range?


texts: SSRS (2005): conditionally format null/zero count cells based on group

texts:
Okay, so I have an SSRS report column-grouped by weeks wait (and sorted by days wait, because weeks wait is a text category field), and a count of the number of people who've been waiting in each cell.

It's been requested that I format text in the 6+ weeks categories red and the remainder green for a quick visual representation of people who've been waiting an acceptable amount of time. So, the font colour is formatted with:

=Iif(Fields!Days_Wait.Value>42,"Red","Green")

However, this only works where the value has associated rows returned from the query. Where there's a count of 0 in the cell, it doesn't have a "Days_Wait" value to look up and so defaults to green.

Short of making the query return a whole load of superfluous empty lines so that every row category/column category combination has a row against it, is there any way to do this? I'm currently stuck with Visual Studio 2005 because the universe is a cold and unforgiving place.


texts:
Our application is made from asp.net webforms using framework 4.5. And since migrating to Amazon AWS from an inhouse server. We've been flooded by this error through out our system.

On the software side, I've already increased the command timeouts to 180 seconds (error happens even on simple split second queries), but still no avail. I've also made sure all connections are Disposed after usage.

On the network side, our application has two web servers + a database server. Traffic is controlled by a load balancer. We've already tried turning off one web server just to see if it happens when the user's session jumps from one server to the other, but still no success, error still occurs.

Hope someone has encountered and resolved this issue.

     Level 1  Exception Type: SqlException  ClientConnectionId = 91d69d77-d17e-46c0-b643-7187a194c5ee  Class = 20  LineNumber = 0  Number = 64  Server =
 [IPADDRESS]  State = 0  Source = .Net SqlClient Data Provider  ErrorCode = -2146232060  Message = A transport-level error has occurred when receiving
 results from the server. (provider: TCP Provider, error: 0 - The specified network name is no longer available.)  StackTrace =    at
 System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)     at
 System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)     at
 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)     at
 System.Data.SqlClient.TdsParserStateObject.ReadSniError(TdsParserStateObject stateObj, UInt32 error)     at
 System.Data.SqlClient.TdsParserStateObject.ReadSniSyncOverAsync()     at System.Data.SqlClient.TdsParserStateObject.TryReadNetworkPacket()     at
 System.Data.SqlClient.TdsParserStateObject.TryPrepareBuffer()     at System.Data.SqlClient.TdsParserStateObject.TryReadByteArray(Byte[] buff, Int32
 offset, Int32 len, Int32& totalRead)     at System.Data.SqlClient.TdsParserStateObject.TryReadString(Int32 length, String& value)     at
 System.Data.SqlClient.TdsParser.TryReadSqlStringValue(SqlBuffer value, Byte type, Int32 length, Encoding encoding, Boolean isPlp, TdsParserStateObject
 stateObj)     at System.Data.SqlClient.TdsParser.TryReadSqlValue(SqlBuffer value, SqlMetaDataPriv md, Int32 length, TdsParserStateObject stateObj,
 SqlCommandColumnEncryptionSetting columnEncryptionOverride, String columnName)     at System.Data.SqlClient.SqlDataReader.TryReadColumnInternal(Int32 i,
 Boolean readHeaderOnly)     at System.Data.SqlClient.SqlDataReader.TryReadColumn(Int32 i, Boolean setTimeout, Boolean allowPartiallyReadColumn)     at
 System.Data.SqlClient.SqlDataReader.GetValues(Object[] values)     at
 System.Data.ProviderBase.DataReaderContainer.CommonLanguageSubsetDataReader.GetValues(Object[] values)     at
 System.Data.ProviderBase.SchemaMapping.LoadDataRow()     at System.Data.Common.DataAdapter.FillLoadDataRow(SchemaMapping mapping)     at
 System.Data.Common.DataAdapter.FillFromReader(DataSet dataset, DataTable datatable, String srcTable, DataReaderContainer dataReader, Int32 startRecord,
 Int32 maxRecords, DataColumn parentChapterColumn, Object parentChapterValue)     at System.Data.Common.DataAdapter.Fill(DataSet dataSet, String srcTable,
 IDataReader dataReader, Int32 startRecord, Int32 maxRecords)     at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables,
 Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)     at System.Data.Common.DbDataAdapter.Fill(DataSet
 dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)     at
 System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)


texts: How can I increase the length of the string returned by object_definition in sqlcmd?

texts:
I have an sql script that uses select object_defintion(object_id(...)) to extract the definition from a view.

This script is run with sqlcmd: sqlcmd -i extract.sql -o %oudir%\extracted.dat.

Unfortunately, the length of the returned string seems to be truncated so that not the entire view definition is extracted.

Is there a possibility to extend the length of the returned string?


texts: Performance of a self join in a temporal table with a single day's difference between end_date and start_date

texts:
Title says it all really. A pretty standard temporal table design has a start_date and an end_date where the end_date is the day before the next record's start_date. In this case, there is an employee_id as well. And there are gaps in the sequence. The interval can vary.

The clustered index is on employee_id, start_date.
There is an unclustered index on employee_id, end_date that isn't used by the query below.

The current join from the current record to the previous contiguous record is (simplified):

SELECT *
FROM self AS current
LEFT OUTER JOIN self AS previous
ON previous.employee_id = current.employee_id
AND DATEADD(d, 1, previous.end_date) = current.start_date

There is a Seek Predicate on the employee_id. The DATEADD() is nonSARGable and stops the end_date index being used, so the join from start_date to end_date is a non-indexed Predicate.

Is there a way to get the start_date to end_date join to use an index? Either by clever indexing, a view or by rewriting the query?

I'm still on SQL Server 2008 R2, but please feel free to use the latest version in the answers.


texts: I need an SSIS package that loops back to the initial execute task after the wait for delay block is executed. here is the logic for the below package: execute SQL task executes a set of commands and after that if a condition is met, the second process(preparation sql task 1 and so on) will be executed. if the condition is not met, it will wait for some time and re-run the first task(execute sql task). I am stuck here and cannot go any further. please help


texts: Writing Select statement to join multiple tables

texts:
I have a database that is designed like so:

And I would like to write a SELECT statement that returns all rows from all tables. I have tried the following but always get 0 results returned:

SELECT
*
FROM TABLEB
INNER JOIN TABLEA on TABLEB.TableAId = TableA.ID
INNER JOIN TABLEC on TABLEB.TableCId = TableC.ID
INNER JOIN TABLED on TABLEC.TableDId = TableD.ID
INNER JOIN TABLEE on TABLEC.TableEId = TableE.ID


texts:
We're using Oracle 12c, and have two separate servers running Oracle, with a CDB with 2 PDBs.

What is the easiest way of getting the data from server A to server B, as a one-time job (not replication)?

So far I can imagine the following alternatives:

Do a TRANSPORTABLE RMAN backup, copy it to the second host, do a restore there
Do a datapump export, move it to the second host, restore there
Unplug the PDB, copy its xml-file and data to the second host, plug it in there

About alternative 3, I'm not even sure if it's a real option.
Which of these alternatives would be easiest to accomplish?

I've tried to read up on the Oracle documentation, but it's very verbose and not at all easy to comprehend (which I suppose is why they're selling so much support services).


texts: Oracle 11G Can't create database from template

texts:
I am attempting to create a new database from an existing template in Oracle 11G.
I have done this before using dbca and haven't had a problem, but now I am hitting the following error:

TNS-04404: no error caused by oracle.net.ConfigException:
TNS-04414: File error caused by:
TNS-04610: No literals left, reached end of NV pair

After some research I found out that the TNS-04610 is likely a problem with the tnsnames.ora file. I found this file in the $ORACLE_HOME/network/admin/ directory. The files contains the following:

# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_3/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

BASE_901_NEW =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = BASE_901_new)
    )
  )

ORCL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))

    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl.domain.com)
    )
  )

BASE_901 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = Base_901)
    )
  )

TEST =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = Test)
    )
med =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = med)
    )

As far as I can tell that file looks correct.  Is there a syntax error that I'm not aware of?
This file was not created by me, and as far as I know it has not been manually edited by anyone.

Any leads are appreciated as I have not been able to find anything.


texts: Store logging in separate or existing database?

texts:
In a .NET WebForms project we want to log certain actions in a database for a year. The estimate is that the log will have a maximum size of 1GB (20.000 records per day of 10 fields) that will probably be queried a maximum times of 2 per day.

We are discussing if it is advisable to put the logging table in a new database (on perhaps even a separate server), or use a new table in our existing database.

The advantages of using our existing database are that we do not need to make a new connection in the code nor administrate a new database. The disadvantage might be that the database cannot handle it.

What are your insights? Do you have any advice?


texts: Add Check Constraint On Varchar Column to enforce no duplicates

texts: I have a varchar(max) column where users will populate a pre-given key from another system. I want to enable that the users are forced to enter a unique key which means no duplicate values on that specific column. Should I change the collation or is there some sort of check constraint that I can add?


texts:
Defined an ENUM

CREATE TYPE currency AS ENUM('GBP', 'EUR', 'USD');

Using this as a type in a table

CREATE TABLE if not exists transaction(
  id BIGSERIAL NOT NULL PRIMARY KEY ,
  amount NUMERIC(35,4) DEFAULT 0.0,
  transaction_currency currency NOT NULL
);

Created an index on transaction_currency

CREATE INDEX transaction_currency ON transaction(transaction_currency);

Inserted records

INSERT INTO transaction(transaction_currency) VALUES ('EUR'), ('USD'), ('USD');

Tried searching in different ways, one by typecasting and other plain string

SELECT *
FROM transaction WHERE transaction_currency= 'USD'

This did a sequential scan on the table instead of index

QUERY PLAN
Seq Scan on transaction  (cost=0.00..1.61 rows=24 width=15) (actual time=0.721..0.916 rows=24 loops=1)
  Filter: (transaction_currency = 'USD')
  Rows Removed by Filter: 25
Planning time: 2.634 ms
Execution time: 1.164 ms

SELECT *
FROM transaction WHERE transaction_currency= 'USD'::currency

This did an index scan

QUERY PLAN
Index Scan using transaction_currency on transaction  (cost=0.14..8.56 rows=24 width=15) (actual time=0.062..0.296 rows=25 loops=1)
  Index Cond: (transaction_currency = 'USD'::currency)
Planning time: 2.581 ms
Execution time: 0.614 ms

Question:

Do we need to explicitly typecast enumerated columns in order to benefit from the index?
If yes, can someone help me understanding how this query work?
Can we instruct postgres to automatically typecast the filter to enumerated type?
If not what alternatives we have to this selective performance?


texts: How to further optimize database schema about IT asset/inventory management

texts:
I am currently working on a project to create an IT Asset/Inventory Management system. My approach was to take their mess of spreadsheets and convert it to a relational database. It will be used to track specific system information and eventually relate financial information to the specific systems and hardware.

I have gone through a few iterations now and believe that this is in 2NF. I would love to get this into 3NF, but am not sure where to make the changes at this point. Any guidance would be greatly appreciated!

Side question: I believe that my schema resembles a Star Schema, is this a correct assumption, and can/should a star schema be normalized?

Description of entities/relationships:

The System entity is used to define distinct systems. A System can be either physical (Server) or virtual (VM), and is uniquely identified by a systemKey.
Systems are composed of the related hardware components/Network components
Systems exist in both a physical (Site) location and a virtual (Lab) location.
Location is unqiely identified by the locationKey and each entry requires both physical and virtual attributes to exist.
Systems may have related Software.
Systems may have related Tickets.
Systems may have one or more Warranties.
Hardware is related to vendors and is a specific type of hardware, identified with a number/name.
CPU/DIMM/Drives are all related to their respective types for definition. They are mandatory on all systems (both virtual and physical).