Using an in-memory table with a regular stored procedure I am working on converting one of the tables in my database to an in-memory table in SQL Server Enterprise 2014.  Let's call the table in question table A.  When I convert table A from a regular table to the in memory table I use the query

CREATE TABLE A(
    id1 SMALLINT NOT NULL PRIMARY KEY NONCLUSTERED,
    id2 BIGINT,
    date DATETIME )
WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_ONLY);

Table A is used by a regular stored procedure in my database, which simply reads id1 and id2 from Table A. Similarly, Table A is called by a regular function in my database which reads id1 from Table A.

The problem I am having is that when I change this table to in-memory, the stored procedure and/or the function which call Table A are no longer working when called through my application - but when I call them through the database, they work just fine.  Is there an issue with using regular functions and stored procedures on in-memory tables?

EDIT: I figured out the issue.  Turns out the application was executing

SET TRANSACTION LEVEL READ UNCOMMITTED

before every query of the in-memory table, and this is incompatible with in-memory tables, so there was an error not being thrown for that within the application.  Changed the database to take snapshots before reading from in-memory tables, solved the issue.

I worked through stored procedures and each type of function ( scalar, in-line TVF, multi-line TVF ) to double-check they worked after conversion to In-memory OLTP tables and it did.  Consider working through this script to see if you can work out what's different about your setup:

USE hekatondb
GO

SET NOCOUNT ON
GO

IF OBJECT_ID('dbo.usp_test') IS NOT NULL DROP PROC dbo.usp_test
IF OBJECT_ID('dbo.usp_test2') IS NOT NULL DROP PROC dbo.usp_test2
IF OBJECT_ID('dbo.usf_test') IS NOT NULL DROP FUNCTION dbo.usf_test
IF OBJECT_ID('dbo.itvf_test') IS NOT NULL DROP FUNCTION dbo.itvf_test
IF OBJECT_ID('dbo.mtvf_test') IS NOT NULL DROP FUNCTION dbo.mtvf_test
IF OBJECT_ID('dbo.A') IS NOT NULL DROP TABLE dbo.A
GO

CREATE TABLE dbo.A (
    id1 SMALLINT NOT NULL PRIMARY KEY NONCLUSTERED,
    id2 BIGINT,
    date DATETIME )
WITH ( MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_ONLY );
GO

IF OBJECT_ID('dbo.B') IS NOT NULL DROP TABLE dbo.B

-- Original table
CREATE TABLE dbo.B (
    id1 SMALLINT NOT NULL PRIMARY KEY,
    id2 BIGINT,
    date DATETIME );
GO

-- Add some dummy data
INSERT INTO dbo.A ( id1, id2, date )
VALUES
    ( 1, 1, '1 Jan 2016' ),
    ( 2, 2, '2 Jan 2016' )

INSERT INTO dbo.B ( id1, id2, date )
VALUES
    ( 1, 1, '1 Jan 2016' ),
    ( 2, 2, '2 Jan 2016' )
GO

-- Show the data
SELECT * FROM dbo.A
SELECT * FROM dbo.B
GO

-- Stored proc and function
CREATE PROC dbo.usp_test

    @id1    SMALLINT,
    @id2    BIGINT      OUT,
    @xdate  DATETIME    OUT

AS

SET NOCOUNT ON

    SELECT
        @id2 = id2,
        @xdate = date
    FROM dbo.B
    WHERE id1 = @id1

RETURN
GO

-- First call, works as expected
DECLARE @id1    SMALLINT = 1,
        @id2    BIGINT,
        @xdate  DATETIME

EXEC dbo.usp_test @id1, @id2 OUT, @xdate OUT

SELECT @id1 id1, @id2 id2, @xdate xdate
GO

-- Alter the proc to point at the in-memory OLTP table
ALTER PROC dbo.usp_test

    @id1    SMALLINT,
    @id2    BIGINT      OUT,
    @xdate  DATETIME    OUT

AS

SET NOCOUNT ON

    SELECT
        @id2 = id2,
        @xdate = date
    FROM dbo.A      --<-- now points at in-memory OLTP table
    WHERE id1 = @id1

RETURN
GO

-- Second call, works as expected
DECLARE @id1    SMALLINT = 1,
        @id2    BIGINT,
        @xdate  DATETIME

EXEC dbo.usp_test @id1, @id2 OUT, @xdate OUT

SELECT @id1 id1, @id2 id2, @xdate xdate
GO

-- Create scalar function
CREATE FUNCTION dbo.usf_test
(
    @id1    SMALLINT
)
RETURNS INT
AS
BEGIN

    DECLARE @Result BIGINT

    SELECT @Result = id2 FROM dbo.B WHERE id1 = @id1

    RETURN @Result

END
GO

-- Test it
SELECT dbo.usf_test(1) id2
GO

-- Create inline TVF
CREATE FUNCTION dbo.itvf_test ( @id1 SMALLINT )
RETURNS TABLE
AS RETURN
(
    SELECT id2, date FROM dbo.B WHERE id1 = @id1
)
GO

-- Test it
SELECT * FROM dbo.itvf_test(1)
GO

-- Create multi-line TVF
CREATE FUNCTION dbo.mtvf_test ( @id1 SMALLINT )
RETURNS @resultset TABLE
(
    id2     BIGINT,
    xdate   DATETIME
)
AS
BEGIN

    INSERT INTO @resultset ( id2, xdate )
    SELECT id2, date
    FROM dbo.B
    WHERE id1 = @id1

    RETURN
END
GO

-- Test it
SELECT * FROM dbo.mtvf_test(1)
GO

-- Create natively compiled stored proc
CREATE PROCEDURE dbo.usp_test2
    @id1    SMALLINT,
    @id2    BIGINT      OUT,
    @xdate  DATETIME    OUT
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER
AS BEGIN ATOMIC WITH
(
 TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english'
)

    SELECT
        @id2 = id2,
        @xdate = date
    FROM dbo.A      --<-- now points at in-memory OLTP table
    WHERE id1 = @id1

END
GO

-- In-memory OLTP call, works as expected
DECLARE @id1    SMALLINT = 1,
        @id2    BIGINT,
        @xdate  DATETIME

EXEC dbo.usp_test2 @id1, @id2 OUT, @xdate OUT

SELECT @id1 id1, @id2 id2, @xdate xdate
GO

As already mentioned, the most likely suspect is the durability, or possibly permissions.  It's also worth mentioning that character columns for In-memory OLTP tables on SQL Server 2014 require a case-sensitive collation, so although your example does not show any char/varchar etc columns, you should bear this in mind.

If your stored proc only references the In-memory OLTP table then you could consider making it a natively compiled stored procedure, which could potentially be faster.
HTH


Untyped functions that return tables I would like to write a PostgreSQL stored function that essentially behaves like the stored procedures I know and love from MSSQL and MySQL where I can just wrap a query that takes no parameters and have it return that resultset without having to specify the format of the output and change that definition every time I update the query. Is this even possible in PostgreSQL ?

I've tried the following using PostgreSQL 9.2:

CREATE OR REPLACE FUNCTION test()
RETURNS SETOF record

Which gives me the following error:

  ERROR:  a column definition list is required for functions returning "record"

I've also tried:

CREATE OR REPLACE FUNCTION test()
RETURNS table ()

but apparently that's invalid syntax.

This is a misunderstanding. A function like this is perfectly valid:

CREATE OR REPLACE FUNCTION test()
 RETURNS SETOF record AS
$func$
VALUES (1, 2), (3, 4);  -- 2 rows with 2 integer columns
$func$ LANGUAGE sql;

However, since it returns anonymous records, you are required to provide a column definition list with every call , just like the error message told you. SQL demands to know what to expect from a function up front. The call would work like this:

SELECT * FROM test() AS f(a int, b int);

Functions returning anonymous records have their uses. I try to avoid them mostly, because the call is rather unwieldy. Depending on your exact requirements, there are a couple of ways around it. I wrote a comprehensive answer for that on SO recently.


Imitate 'dynamic' columns in SQL Server We have a central SQL Server 2012 database.  There is a single table [tbl_Users] that stores all kinds of information pertinent to an employee, for example,

UserID
CustomerID
FirstName
Surname
EmployeeID

All users are stored in the same table regardless of Customer, and segregated using CustomerID.

Some customers are now asking for new columns to be added, which are only relevant to their particular business.  In the past, we've just added the columns into [tbl_Users], but now this table is becoming untidy with too many columns, and for the most part, many row values end up being NULL because of their small relevance to others.

Can anyone offer any suggestions on how to allow us imitate this type of design dynamically (if it is possible).  I've considered the following approach:

Create a table called [tbl_ColumnNames] that contains each unique column name and data type
Remove all non-generic columns from [tbl_Users] move them to [tbl_ColumnNames]
Create a many-to-many table [tbl_CustomersColumns] where we assign columns to any respective Customers
Create a table [tbl_CustomerColumnData] to hold the data moved from [tbl_Users], possibly with SqlVarient type for the actual data field

Is this a decent approach?  Our ASP.NET application would then dynamically create the forms to allow data population.  Has anyone used SqlVarient types for this kind of thing?  Or, is there a better way altogether?

Many thanks.

I used your approach two years ago for a similar problem, it works fairly well, except a bit pain with linq to sql (or entity framework), plus WinForm:

We created a few updatable views for each type of entity (or customer as per your sample) but linq to sql could not cope well with updatable views, or probably it's not the case now.

Now we used a different approach:

In the main table, we will have a column named Descriminator (something like UserType),

then for each type of entity, we will have its delegated table (extended from the main table), which uses same PrimaryKey as the main table - so of course, a foreign constraint refer back to the main table (i.e. Primary Key and foreign key on the same field), and the delegated table will have the specific columns related to that user type.

it works well too, and with this, we can take the advantage of entityframework's inheritance feature.

that's just my experience, so my suggestion is to write some small test apps to test out your approach(es) and evaluate. then get the best that has good performance and also suits your framework for UI.


How can I model and enforce constraints on a categorical item contained in a collection? Scenario:

There is a collection of items where each item belongs to 1 category.
There is a collection of buckets which may contain at most 1 item from each category.  Oh, and an item may belong to at most 1 bucket.

I'm trying to model this with 3 sets at the moment:

categories (category_id),
items (item_id, category_id), and
buckets (bucket_id, category_id, item_id)

With

a foreign key reference between items.category_id and category.category_id,
a foreign key reference between buckets.category_id and category.category_id,
a foreign key reference between buckets.item_id and items.item_id, and
a unique index on (bucket_id, category_id)

But I'm stumped trying to prove that the item_id belongs to the category indicated by the category_id on the buckets set.

Am I going about this the wrong way? Is there a formal name for this problem? Thoughts?

EDIT: To make this more concrete, consider the following question which asks a similar question in terms of people and pet species (https://dba.stackexchange.com/a/16854/35832), but now lift the constraint to genus (genus is a grouping of species).  That is, at most one of each genus, but the item in the bucket is a member of a species.

Categories and Buckets.

create table categories
(
  category_id int primary key
);

create table buckets
(
  bucket_id int primary key
);

Depending on how to interpret "an item may belong to at most 1 bucket" you need to do some different things with Items.

An item has to belong to one bucket.

create table items
(
  item_id int primary key,
  category_id int not null references categories(category_id),
  bucket_id int not null references buckets(bucket_id),
  unique (category_id, bucket_id)
);

If an item may belong to one bucket but does not have to you need to allow for null values in bucket_id and use a filtered index as the unique constraint.

create table items
(
  item_id int primary key,
  category_id int not null references categories(category_id),
  bucket_id int references buckets(bucket_id)
);

create unique index uq_items on items(category_id, bucket_id) where bucket_id is not null;

  There is a collection of items where each item belongs to 1 category.

category_id as a foreign key in items.

  an item may belong to at most 1 bucket.

bucket_id as a foreign key in items.

  There is a collection of buckets which may contain at most 1 item from
  each category.

A unique constraint on bucket_id and category_id in items.


Sync Server Objects when having two or more Availability Groups Lets imagine that i have two servers/nodes with TWO availability groups Group01 and Group02. I will need to ensure that the server objects are all the time recreated over the secondary replica.

If i have the Group01 in one node as primary, and the Group02 in the other node as primary, how will the sync of server objects be processed?

I was thinking of using dbatools with one command line only:

Start-DbaMigration -Source $PrimaryInstance `
                   -Destination $SecondaryInstance `
                   -Exclude Databases

What am I missing here?

The server object are at the server level e.g. logins, jobs, etc. So you can just use Sync-DbaAvailabilityGroup

Syncs depdendent objects for availability groups. Such objects include:

SpConfigure
CustomErrors
Credentials
DatabaseMail
LinkedServers
Logins
LoginPermissions
SystemTriggers
DatabaseOwner
AgentCategory
AgentOperator
AgentAlert
AgentProxy
AgentSchedule
AgentJob

What you are using is a much bigger umbrella that folks use for migration from one server to another.


Is there an Open Source Metadata Management Solution? Is there an Open Source Metadata Management Solution? I'd like to create a Metadata Repository that will hold the details of the metadata of database schemas, tables and data items of hundreds of Enterprise databases.

I'm especially interested in something that can automatically query the schema data of the databases to be able to track changes in the metadata related to tables. i.e. changes to column data sizes, tables and columns added etc.

MIKE?
What is MIKE2.0?
MIKE2.0 which stands for Method for an Integrated Knowledge Environment, is an open source methodology for Enterprise Information Management that provides a framework for information development . The MIKE2.0 Methodology is part of the overall Open Methodology Framework.


Load balancing Availability groups with SQL Server Standard So I have the current scenario, and it looks to be working fantastically, but I just want to get some input on the configuration.
Is it smart? Are there any issues I am not thinking of?

We have SQL Server Standard, and as such, you can only have 1 DB per AG (we have 20 databases), and no Read-Only secondaries.
Basically meaning, the primary server is doing ALL the lifting, with the secondary doing a lot less.
So essentially, you are paying for resources for Node 2, that are sitting at 10% workload, while node 1 is at 70/80% workload.
Both nodes are fully licensed with regards to SQL Server cores.

What I have done, to assist this, is slit the database primaries up.
So about 50% of the databases are Primary on node1, while the other 50% are primary on node2.

The results:

The applications all connect great to either node via their respective listener.
If a failover occurs, just the databases on the failing node are affected, and failover to the other node (we have tested this fairly well).

Each node, can now split the load, essentially load balancing.
It is a manual process to set it up this way and when deploying new DBs and groups they go to the lighter node. but a small price to pay for "more" hardware punch without much cost (licenses which we already have and a bit of admin).

What are your thoughts on this, guys?

I kind of like your idea, although it defeats the idea of a primary and second nodes. You should be sure to evaluate if one server can take the load of both, as if one goes down, then one server will have all the load.
You should also be careful, if the activity on one server gets to high in time, it might affect the "seconday" node activities and cause I/O bottlenecks. (It all depends on how your disks are confugred.)But other than that, it could be a good alternative and when your organization gets bigger, you will be able to afford another server to remove some load from your current setup. Just make sure to review carefully your emergency scenarios and to test them well.


Why SQL Server uses Non-Clustered but not Clustered Index? I have a table with 145 million rows

CREATE TABLE [dbo].[RFTest](
    [SnapshotKey] [int] NOT NULL,
    [SnapshotDt] [datetime] NOT NULL,
    [LoanNum] [int] NOT NULL,
    [GLSourceSystem] [varchar](10) NOT NULL,
    [FlowDescription] [varchar](30) NULL,
    [Account] [varchar](30) NULL,

    --- plus 20 more column
)

The table is partitioned on SnapshotDt.

I added following indexes on my table:

create clustered index ci on RFTest (SnapshotDt, SnapshotKey, LoanNum)

create nonclustered index nci on RFTest (SnapshotDt, SnapshotKey, LoanNum)
    include ([GLSourceSystem],[Account],[FlowDescription])

I ran below query: (I use top 100 for test as the whole table will take long time if I wanted to run it)

select top 100 *
from RFTest with (index(ci)) -- force index
where LoanNum = 2712

select top 100 *
from RFTest
where LoanNum = 2712

LoanNum column exists in both indexes, part of key in clustered and included in non-clustered.

Execution plan shows the engine choose non-clustered "nci" index, NOT the clustered one.

I like to know why.

Clarification:

To me in both case SQL read the same amount of data. and LoanNum is in both index and BTW, the LoanNum is part of key so It seems to me that it makes more sense if it use clustered index.

The indexes are exactly as I posted. There were some comments in queries, when I captured plan. the query you see in the post is correct. I don't want to keep both index, I was trying to see which one performs better and the question came to me.

The optimizer has a choice between two main strategies:

Scan the table (the clustered index) checking every row to see if LoanNum = 2712.
Scan & Lookup

Scan the nonclustered index to find rows where LoanNum = 2712
Look up the column data for the matched rows not covered by the nonclustered index.

The key point is that the nonclustered index is smaller, so scanning it is expected to be cheaper. This might seem counterintuitive because the clustered index definition has the same keys, and the nonclustered index has included columns, but the point is that the clustered index includes all columns stored in-row - the leaf (lowest) level of the clustered index literally is the in-row data.

For a small number of expected matches, the cost saving from scanning the smaller index is more than enough to compensate for the key lookups.

Incidentally, you may find that removing the WHERE 1 = 1 from your query causes the optimizer to choose the clustered index scan. The (redundant) constant-to-constant comparison prevents SQL Server parameterizing the query, so estimates are based on statistical information about LoanNum 2712. If the query is parameterized, SQL Server will use the average distribution of LoanNum values, which might result in a higher number of expected rows, and a change of plan choice.

See also:

Effective Clustered Indexes by Michelle Ufford
How (and why) does TOP impact an execution plan?


PostgreSQL: Log statements which do sequential scans I use this snippet to detect missing indexes:

https://stackoverflow.com/a/12818168/633961

Example:

SELECT
   relname                                               AS TableName,
   to_char(seq_scan, '999,999,999,999')                  AS TotalSeqScan,
   to_char(idx_scan, '999,999,999,999')                  AS TotalIndexScan,
   to_char(n_live_tup, '999,999,999,999')                AS TableRows,
   pg_size_pretty(pg_relation_size(relname :: regclass)) AS TableSize
 FROM pg_stat_all_tables
 WHERE schemaname = 'public'
       AND 50 * seq_scan > idx_scan -- more then 2%
       AND n_live_tup > 10000
       AND pg_relation_size(relname :: regclass) > 5000000
 ORDER BY relname ASC;

Result:

tablename | totalseqscan | totalindexscan | tablerows | tablesize
----------+--------------+----------------+-----------+----------
 mytable  |      112,479 |      2,978,344 | 1,293,536 |   1716 MB

I am curious - I would like to see which SQL statements actually does a seq scan on table mytable.

Is there a way to let PostgreSQL emit a warning if it does a sequential scan on this table?

I think the only way to do this is to use the auto_explain module and enable dumping of execution plans if a statement is slower than e.g. a second.

The plan will be written into the Postgres log file

Then you can have a job that monitors the log file and takes actions if a Seq Scan is part of the plan.

A Seq Scan is not something that should be avoided at all costs. It often the fastest way to get the result.

I would focus on plans with Seq Scans, but simply try to find (and then fix) slow queries.

For analysing the logfile you want to have a log at pgbadger or you can use POWA to monitor your server in real time


MySQL How to calculate Partnership Scores from Cricket ball by ball database [Solved] I'm trying to create a simple cricket application, where i will store all cricket records from a database. I collect a sample database with data from online and i successfully did some query but I'm unable to write the query for finding Partnership Scored for a match. I have no clue how can i do this.

Below is the three main Table which i will used to get the data:

  1. ball_by_ball table, this table holds full information by ball by ball:

  2. batsman_scored, this table holds batsman run scores by ball by ball:

  3. wicket_taken, this table hold wicket fallen information by ball by ball:

I tried below query but I'm unable to group those properly

select P.Player_Name, P1.Player_Name, sum(b.runs_scored) as Runs
from ball_by_ball a
       inner join batsman_scored b using (match_id, over_id, ball_id, innings_no)
       inner join Player P on a.striker = P.Player_Id
       inner join Player P1 on a.non_striker = P1.Player_Id
where a.match_id = 981018
  and a.innings_no = 1 group by P.Player_Name, P1.Player_Name;

So this my output: (this is not what i want)

  If you look above screenshot, there Row 1 and 2 will be same, AJ Finch BB McCullum and BB McCullum AJ Finch will be same row and the
  Runs column will be sum. That's what i want but i'm unable to do it.

UPDATED- This is the finally query which gives the partnership information:

select P.Player_Name, Runs + Extra as Partnership, P1.Player_Name
from (select if(striker > non_striker,
                concat(striker),
                concat(non_striker))      as Bat1,
             sum(b.runs_scored)           as Runs,
             ifnull(sum(e.extra_runs), 0) as Extra,
             if(striker > non_striker,
                concat(non_striker),
                concat(striker))          as Bat2
      from ball_by_ball a
             left join batsman_scored b using (match_id, over_id, ball_id, innings_no)
             left join extra_runs e using (match_id, over_id, ball_id, innings_no)
      where a.match_id = 981020
        and a.innings_no = 1
      group by if(striker > non_striker,
                  concat(striker, '-', non_striker),
                  concat(non_striker, '-', striker)), Bat1, Bat2) as t
       inner join Player P on t.Bat1 = P.Player_Id
       inner join Player P1 on t.Bat2 = P1.Player_Id;

  This is the final output which i wanted:

Your grouping is a player pair so your GROUP BY expression needs to reflect this. The striker / non_striker must combined form a unique value. Start with an IF statement to avoid duplicates. I've used a mathematical expression that won't cause duplicates.

So with your query use the following group by:

GROUP BY IF(striker > non_striker,
            striker * 30 + non_striker,
            non_striker * 30 + striker)

This works because assuming all player ids are < 30. If this isn't the case it can be replaced by a higher value.

Alternately you could use a string based mechanism to get 1-2 as unique identifier. Note a separator - is needed (and can be anything not in the result set), otherwise striker,non_striker value 1,234 would be grouped the same as 12,34

GROUP BY IF(striker > non_striker,
            CONCAT(striker, '-', non_striker),
            CONCAT(non_striker, '-', striker)


what is running VSS on my server Client's SQL Server farm DBs are backed up by third-party tool that does not use VSS. About half of the instances show backups being made by some tool that is using VSS. I can disable SQL Server VSS Writer to stop these errant backups, but how do I track down what is running the VSS backups?

keywords: Volume Shadow Copy Service, volume-shadow-service [I don't have rep to create tags]

I believe that if you run native SQL Server backups (using T-SQL BACKUP) instead of DPM protection agent-based SQL backups, the SQL Server VSS Writer service must not be used on your SQL Server database folders.  (The same problem is true for other third-party backup tools.)

This DPM/VSS service is not used by native SQL backups. If you prevent the DPM protection agent backups from triggering SQL Server VSS snapshots, this will protect your chain of log backups.

If DPM/VSS does continue to backup the SQL Server files, then the log chain is broken, because there are two tools (T-SQL BACKUP and DPM/VSS) backing up the log files.


How to count boolean values in PostgreSQL? How to count, how may true and false for the field public in postgresql user table
 i have tried this query

select
sum(case when false then 1 else 0 end) as false,
sum(case when true then 1 else 0 end) as true
from  public.user;

but am not getting any value and if i remove public from query then i will get correct counts only i have value true

table : name|   DOB     | public
values : bb | 20/2/1991/| true

op : true = 1 and false  = 0

but am getting the same answer when i make public as false

table : name|   DOB     | public
values : bb | 20/2/1991/| false

op : true = 1 and false  = 0

so someone please help me to solve this

Use the filter() clause:

select count(*) filter (where "public") as public_count,
       count(*) filter (where not "public") as not_public_count
from  public."user";

Note that user is a reserved keyword, you have to use double quotes in order to use it as a table name.

The above assumes that the column public is of type boolean


SQL to DjangoORM query I've model,

class ModelA(models.Model):
    device_id = models.IntegerField()
    deal_sent = ArrayField(models.IntegerField)

For given device_id, we can have a list of repeating deal_sent. For eg:- Consider the following table

device_id         deals_sent
    1       [4,5,1]
    2       [1,2,3,4,2,3,4,3,4,5,1]
    3       [1,4,2,3,4,3,4,5,1]
    4       [1,4,3,4,5,1]
    5       [4,5]
    6       [1,3,4,2,3,4,3,4,5,1]

I want to get top N device_id, who have minimum count of 1 in deal_sent ArrayField. For eg:- If I want top 2 device_id who have minimum count of 1 in deals_sent, then device_id 1 and 5 will be returned since the count of 1 in deals_sent was least in device_id 1 and 5.

I've followed this question which seems to be a close solution what I'M expecting.

with elements (element) as (
   select unnest(ARRAY['a','b','c','a','a'])
)
select count(*)
from elements
where element = 'a';

How can I convert the above query to DjangoORM query? OR What can the alternate perfect query at the DB level itself without using a for-loop at the application level?

One way to do the query would be:

with norm (device_id, element) as (
    select device_id, unnest(deals_sent) from modelA
)
select device_id
from norm·
group by device_id
order by count(element = 1)
limit 2;

The same query cannot be produced by Django ORM. You can only use raw. However there are some options to still keep the benefits of the ORM.

1) Using a view

You can create a VIEW and an unmanaged model in your django app pointing to that view.

CREATE VIEW modelA_norm AS (
   select device_id, unnest(deals_sent) as element from modelA
);

Then in Django:

class ModelA_norm(models.Model):
    device_id = models.IntegerField()
    element = models.IntegerField()

     class Meta:
         managed = False

The query would look like this:

ModelA_norm.objects.extra(
    select={'counter': 'COUNT(element=%s)'},
    select_params=(1, )
).order_by('counter')[:2]

2) Using subselect in where

ModelA.objects.extra(
    where=[
        'device_id IN ( ... here comes the select to get the device ids ...)'
    ]
)

Be aware in this case you will get the results back in arbitrary order not necessary the order of ids produced by the subselect.

3) Run the select directly

You can get the ids to a list from the database and use them to query from your model.

from django.db import connection
_c = connection.cursor()
_c.execute('... the query above with placeholders ...', [1])
_ids = [_d[0] for _d in _c.fetchall()]
objects = modalA.objects.in_bulk(_ids)
sorted_objects = [objects[id] for id in _ids]

I tend to use both 1) and 3) depending on the actual situation.

1) for situation where I can either inherit the model from the original or I don't actually need the original model I can just have a new one.

3) when I need the original model. I tend to put the queries into files in SQL directory in the app (just like templates). So I have a clear indication that I'm doing something database dependent and I can easily switch/rewrite them later if necessary.

I hope this helps.


recover postgresql database after removal with --purge I noticed, after a while, that when I got new versions of postgresql (I'm using debian testing) they were being installed while keeping older versions of the DBMS running in parallel (one listening on a different port and so on). So, without thinking much about it, I just ran dpkg remove --purge on the older versions without realizing that the DB that I was using was actually running on the oldest version installed. Is there DB data on the FS that is not deleted if removing the postgresql package with --purge so that I can get the DB back?

Check /var/lib/postgresql/<version>. There are the database files.

If this directory doesn't exist, it was removed. What exactly happens on --purge removal, it depends on the distro (more exactly, on the deinstall scripts were called with a different parameter in /var/lib/dpkg/info). It depends on the scripts (which were provided by the package), what will exactly happen. The logic of the --purge dictates a real, full removal.

If it happened, the data - at least partially - is probably still available on your disk, so a recovery is still not impossible (except the overwritten sectors), but it is very unlikely that you can do that.

To do that, you would need to power off your system (yes, it is one of the rare cases where explicitly power off without any sync, etc is needed), then reboot from without mounting it, and then doing a block-level scan for postgresql database files by its signatures.

Always have a periodic, automatical backup from your databases.


SQL USING CAST in a query inside a stored Procedure I was trying to use a query inside an Stored procedure which has a cast funtion. But when executing the SP I'm getting an error

  Conversion failed when converting date and/or time from character string.

SELECT *
  FROM TEMPTABLE
 WHERE Status = 'A'
   AND technician = 5
   and schid not in (1047)
   and CAST(approxstartdate as datetime)  > =
       ( select CAST(approxstartdate as datetime)
           from trscheduler where Status = 'A'
            AND technician = 5
            and schid = 1059
       )
 order by  CAST(approxstartdate as datetime)

This query is used in a cursor.

Kindly let me know an alternative for this.

You have data in approxstartdate column that cannot be converted to datetime, it’s bad data, chances are if you saw what that data is you wouldn’t know how to convert it to a datetime either.

If you are using SQL Server, take a look at try_convert which returns null if the value cannot be converted. You can use this function to find the values in your table that cannot be converted and then fix them or remove them.


MYSQL workbench & other IDEs not working on fresh OS after reinstall? I've recently had to format my computer and re-install my Win7 OS. Then I reinstalled the MySql workbench I was using last time. However, each time I open it and create a schema, the entire workbench crashes. I've tried rebooting, reinstalling, "check online for help" option, and even tried HeidiSQL which also starts but fails to connect to server. I've attached screenshots.

I think I might have to assume that this is a system fault and my system, since it is new, has some dependencies or some configuration that is missing. But both installations were fine so I'm not sure what those dependencies and configurations might be. Can someone offer some guidance on what can be done here? Thanks, greatly appreciated!!

I'm using 6.3.7 build 1199 CE (64 bit) Community.

The issue was resolved by installing MySQL server. Recommend all to use MySQL installer web version which automatically lets you choose what packages you wish to install (this might even include something you wouldn't even install on your own), and it will download, install, and configure the latest versions of the packages on its own.

Thanks


Restrict Full access to certain User of Database The scenario is the following:

Given SQL Server Instance created and handled not by me
Creation of Database and User on given Instance by me

Question:

To my limited understanding, the sa login will have full access to my Database. Is there a way I can create a user that will be the only user/login that will have full access to my Database, despite the fact I don't own the instance?

If you are trying to prevent the owner of the instance or any Windows or SQL Server administrators from being able to access your database, the answer is no.

Essentially you should assume that any administrator on the box owns and can gain access to everything on the box, including your database.

You may be able to protect the actual data in some cases (check out Always Encrypted in SQL Server 2016, for example), but that's about it. The administrator can access everything you don't encrypt (and much of the stuff you do in current versions), drop your database, lock you out, etc.

If you don't trust the service provider that's hosting your database, you should host elsewhere. After all, any host worth their salt should have agreements in place that legally protect you, even though not physically, from internal data breach.


How to deny a user to access (select, insert, delete, update) to tables but grant (select) a few i am trying to deny a user to access (select, insert, delete, update) to tables on my database but i also need him to grant only select to a few tables,
to do this i created a user with login and gave him datareader permission then by using the code below i Denied user to Select data from a table,

DENY SELECT ON "dbo"."Customer" TO "test"

He can't see the table which is fine but he can still select the data by

select * from Customer

what i want is to deny select data from table, i don't care whether he sees the table.

for further step i will deny users to access tables but few views.

here is the image;

The "secret" here is that your login test is sysadmin and he's mapped to dbo in every database. Test user that is on your picture is NOT the user corresponding to login "test".

If you want to execute under user test you should write the code

exec as login = 'test'
select *
from customer
revert

And this user will have the deny on customer table.

But now all the code you executed you executed it under dbo user (that corresponds to test login), this can be proved by executing

select system_user, user

that will show test as system_user and dbo as user.

This situation can happen if the login (test) was mapped to this database when he was not sysadmin, so test user was created. But then someone made him sysadmin and now he is mapped to dbo and test is just orphaned.

Another situation is when database was restored and that user (still orphaned) has no corresponding login, the new test login is not related to it and is mapped to dbo

  how can i correct it ? it should be a just a user

exec sp_dropsrvrolemember 'test', 'sysadmin'

This way you make him an ordinary login.

Then you should check if your user test is his corresponding login or just re-map it:

alter user test with login = test;

UPDATE

Assuming that the first screenshot is shown "as is" it seems that the code to test "Check if it still works" was executed under the same account that denied the select permission, i.e. under sa login as there is no code impersonating test login. This explains because the select code was executed without errors and also the result of select system_user, user confirms this.


Is there a way to view WAL updates on a Hot Standby server? We are trying to debug some of the problems we are having on our production database server. One thing we would like to enable is pg_stat_statements, but we are holding off on restarting the service. In the meantime, we had the idea that we could enable pg_stat_statements and restart the hot standby. The thinking here was that we could at least see all of the writes that were issued in production, just on a time delay. However, this doesn't seem to be the case.

Is there a way to view the updates that are issued by the WAL logs on the hot standby?

EDIT: or by streaming replication?

pg_stat_statements has nothing to do with WAL. The WAL doesn't record statements and you cannot discover what statements were run from WAL. It only records blocks changed on disk and relevant related metadata.

It's definitely not useful on a standby.

You could use pg_xlogdump (pg_waldump on pg10 and newer) to view the low level WAL stream, but without a lot of experience and time you won't be able to understand it. It doesn't and can't show table names and row info, only table relfilenodes and tuple TIDs, plus binary tuple data.

If you want to avoid installing pg_stat_statements you could make a poor-man's version by capturing pg_stat_activity on a timer and doing your own parsing and merging.


Relation Subtraction wrong result I have 2 queries:

Query1:

SELECT DISTINCT maker FROM Product WHERE type='laptop'

Gives result {A, B, E, F, G}

and

Query2:

SELECT DISTINCT maker FROM Product WHERE type='pc'

Gives result {A, B, C, D, E}

The question is:
How can I get query1 - query2 = {A, B, E, F, G} - {A, B, C, D, E} = {F, G}

I tried:

SELECT DISTINCT maker FROM Product WHERE type='pc' AND NOT EXISTS (
    SELECT DISTINCT maker FROM Product WHERE type='b'
);

But it gave me empty record. Sorry I'm new with MySQL

You might use NOT IN instead:

SELECT DISTINCT maker
FROM Product
WHERE type='pc'
  AND maker  NOT IN
 (
    SELECT maker
    FROM Product
    WHERE type='b'
      AND maker IS NOT NULL -- to prevent empty results
 );

Caution: This will not work correctly with a NULL maker.

The recommended solution is NOT EXISTS, but you need a Correlated Subquery:

SELECT DISTINCT maker
FROM Product AS p1
WHERE type='pc'
  AND NOT EXISTS
 (
    SELECT maker
    FROM Product AS p2
    WHERE type='b'
      AND p1.maker = p2.maker -- correlation to the outer SELECT
 );

If MySQL supported EXCEPT it would be a simple

SELECT maker FROM Product WHERE type='pc'
EXCEPT
SELECT maker FROM Product WHERE type='b';


Are there open source RDBMS that support non-backup snapshots? I'm not looking for point-in-time backups but some kind of "time machine" read-only functionality.

For example when running a SELECT query I would like to tell the database to run it on the data as it was, say, 1 month ago. Snapshots granularity could go from on-demand to taking a snapshot after each transaction.

Ideally it would be possible also to purge data on-demand, something like "stop storing snapshots older than 10 days".

Arkhipov's extension for PostgreSQL enables Temporal Tables.

https://pgxn.org/dist/temporal_tables/

Here's a decent guide for using it: http://clarkdave.net/2015/02/historical-records-with-postgresql-and-temporal-tables-and-sql-2011/

PostgreSQL is uniquely suited to this task due to the way it implements row updates.


Troubleshoot DBMail in Sql Server 2005 On a SQL Server 2005 machine, I have DBMail setup and correctly configured. I am able to use "Notify Operator" and EXEC msdb.dbo.sp_send_dbmail to queue emails.

But the problem is the queued mails never go through. I see 10 emails in the queue when I query

SELECT * FROM msdb.dbo.sysmail_unsentitems

But if I restart the SQL Server service, all mails go through and I receive emails in my inbox.

So far this is the only pattern I could find.

So my question is: is there a way to troubleshoot DBmail? I read some steps on MSDN, but they are not of much help. Any URLs to articles that could help are much appreciated.

Have you start the service broker on MSDB ? You need the service broker on to be able to process mail queue.

Read this post on Stackoverflow.
An other link that may help you is how to troubleshoot a queue in sql.
You can also use this link to get your service broker up and running on MSDB.

Hope that those links may help you.


Query SQL Server from Excel with parameters linked to cells I have a bunch of Excel spreadsheet that have data connections to a SQL Server DB (Hosted by AWS RDS) and I follow exactly this procedure to get those connections up and running with the parameters linked to cells: https://superuser.com/a/758792/164558

My connection string ends up looking like this:

DRIVER=SQL Server Native Client 11.0;SERVER=blabla.yadayada.eu-west-1.rds.amazonaws.com;UID=MyUserID;PWD=MrPasswordMan;APP=Microsoft Office 2013;WSID=COMPUTER-NAME;

And this works perfectly on my PC (I have SQL Server installed), I can link to cells with dates and it accepts them as date parameters. However when I try refresh the data in this spreadsheet from other PCs (that do not have SQL Server installed on them), the data won't refresh throwing ODBC errors about default driver's not being specified.

If I change the driver in the connection string to DRIVER=SQL Server (i.e. drop the native client bit) then it will work on the other PCs. However, it now will only accept text cells for date parameters instead of date cells!

Is there a way to get those SQL Server native client 11.0 drivers installed on the other PCs? Is this something that can be solved via the odbcad32.exe (which I do not know anything about)? Or if not, does anyone know how to get the normal SQL Server driver to accept cells containing dates and not date strings to work as parameters?

I have found a driver that I can install on a PC that does not have SQL Server installed on it and it makes Excel's data connections connect to SQL Server with cell linked parameters that are dates. The install can be found here: http://www.microsoft.com/en-us/download/details.aspx?id=36434

You then need to change the driver in your connection string to look like:

DRIVER=ODBC Driver 11 for SQL Server;SERVER=blabla.yadayada.eu-west-1.rds.amazonaws.com;UID=MyUserID;PWD=MrPasswordMan;APP=Microsoft Office 2013;WSID=COMPUTER-NAME;


Table size reduce with help changing collation charset [MySQL] I have table which is big enough, phpmyadmin shows that it takes more than 40Gb space. I can't remove data from it, but I've figure out one thing: there is one field which is described as

  varchar(3)    utf8_general_ci

There are always latin symbols.

I thought that changing collation to latin1_general_ci would reduce the Table size.

I've tried this out on small version of it with 200 Mb but I did not get expected result :(

Why so ?

MySQL's documentation for utf8 shows that it will use 1 byte for Latin characters, and only use more if the situation requires them.

Therefore, if you're only using normal latin characters, both utf8_general_ci and latin1_general_ci will use between 1 and 4 bytes: one byte to store the length (0-3 characters), and then up to three bytes for the actual text.

If the value of this field will always be exactly three characters, you could save one byte by changing the column from a varchar(3) to a char(3) (as long as you also use latin1_general_ci).

If it's not exactly three characters, it's likely to be more efficient to leave it as a varchar - there's not much more you can do to reduce the size of this field. However, with a 40GB table you can hopefully find something else to optimise!


Should I use SQL JOIN or IN Clause? I have a question about best approach. I am not sure which approach is best when data is considered variable in size.

Consider the following 3 TABLES:

EMPLOYEE

EMPLOYEE_ID, EMP_NAME

PROJECT

PROJECT_ID, PROJ_NAME

EMP_PROJ (many to many of above two tables)

EMPLOYEE_ID, PROJECT_ID

Problem:  Given an EmployeeID,  find ALL the employees of ALL Projects that this Employee is associated with.

I have tried this in two way.. both approaches differ only by few milliseconds no matter what size of data is used.

SELECT EMP_NAME FROM EMPLOYEE
WHERE EMPLOYEE_ID IN (
    SELECT EMPLOYEE_ID FROM EMP_PROJ
    WHERE PROJECT_ID IN (
        SELECT PROJECT_ID FROM EMP_PROJ p, EMPLOYEE e
        WHERE p.EMPLOYEE_ID = E.EMPLOYEE_ID
        AND  E.EMPLOYEE_ID = 123)

go

select c.EMP_NAME FROM
(SELECT PROJECT_ID FROM EMP_PROJ
WHERE EMPLOYEE_ID = 123) a
JOIN
EMP_PROJ b
ON a.PROJECT_ID = b.PROJECT_ID
JOIN
EMPLOYEE c
ON b.EMPLOYEE_ID = c.EMPLOYEE_ID

As of now, I expect around 5000 Employees and Projects each.. but have no idea about what kinda many-many relationship exists.
Which approach would u recommend?
thanks!

EDIT:
Execution Plan of Approach 1

"Hash Join  (cost=86.55..106.11 rows=200 width=98)"
"  Hash Cond: (employee.employee_id = emp_proj.employee_id)"
"  ->  Seq Scan on employee  (cost=0.00..16.10 rows=610 width=102)"
"  ->  Hash  (cost=85.07..85.07 rows=118 width=4)"
"        ->  HashAggregate  (cost=83.89..85.07 rows=118 width=4)"
"              ->  Hash Semi Join  (cost=45.27..83.60 rows=118 width=4)"
"                    Hash Cond: (emp_proj.project_id = p.project_id)"
"                    ->  Seq Scan on emp_proj  (cost=0.00..31.40 rows=2140 width=8)"
"                    ->  Hash  (cost=45.13..45.13 rows=11 width=4)"
"                          ->  Nested Loop  (cost=0.00..45.13 rows=11 width=4)"
"                                ->  Index Scan using employee_pkey on employee e  (cost=0.00..8.27 rows=1 width=4)"
"                                      Index Cond: (employee_id = 123)"
"                                ->  Seq Scan on emp_proj p  (cost=0.00..36.75 rows=11 width=8)"
"                                      Filter: (p.employee_id = 123)"

Execution Plan of Approach 2:

"Nested Loop  (cost=60.61..112.29 rows=118 width=98)"
"  ->  Index Scan using employee_pkey on employee e  (cost=0.00..8.27 rows=1 width=4)"
"        Index Cond: (employee_id = 123)"
"  ->  Hash Join  (cost=60.61..102.84 rows=118 width=102)"
"        Hash Cond: (b.employee_id = c.employee_id)"
"        ->  Hash Join  (cost=36.89..77.49 rows=118 width=8)"
"              Hash Cond: (b.project_id = p.project_id)"
"              ->  Seq Scan on emp_proj b  (cost=0.00..31.40 rows=2140 width=8)"
"              ->  Hash  (cost=36.75..36.75 rows=11 width=8)"
"                    ->  Seq Scan on emp_proj p  (cost=0.00..36.75 rows=11 width=8)"
"                          Filter: (employee_id = 123)"
"        ->  Hash  (cost=16.10..16.10 rows=610 width=102)"
"              ->  Seq Scan on employee c  (cost=0.00..16.10 rows=610 width=102)"

So looks like the Execution plan of Approach 2 is slightly better, because the 'cost' is 60 as opposed to 85 of approach 1. Is that the right way to analyze this?

How does one know it will hold true even for all sorts of many-many combinations?

In SQL Server, with a few assumptions like "those fields can't contain NULLs", those queries should give the almost the same plan.

But also consider the type of join you're doing. An IN clause like this is a Semi Join, not an Inner Join. An Inner Join can project onto multiple rows, thereby giving duplicates (compared to using IN or EXISTS). So you may want to consider this behaviour when choosing how you write your query.


Slow search by LIKE and other values (1 million rows+) I'm currently developing a kind of search website where one can search for business companies in my country. The database table has 1 million rows and quite a few colums (42 columns).

But I find it very slow. Is that because of the query I'm executing, or the database?

I have created indexes on several columns wich are used in the search query too, but often when a query is run, it times out on 30 seconds.

The query run would look something like this:

SELECT *
FROM companies
WHERE ( navn LIKE ?
      OR keywords LIKE ? )
   AND ( ( forretningsadresse_fylke = ?
           AND ( forretningsadresse_kommune = ?
                  OR forretningsadresse_kommune = ?
                  OR forretningsadresse_kommune = ? ) )
          OR ( forretningsadresse_fylke = ?
               AND ( forretningsadresse_kommune = ?
                      OR forretningsadresse_kommune = ?
                      OR forretningsadresse_kommune = ? ) )
          OR ( forretningsadresse_fylke = ? )
          OR ( forretningsadresse_fylke = ?
               AND ( forretningsadresse_kommune = ? ) ) )

Engine: InnoDB.
Collation: utf8 - default collation

If any other info is needed, please say so. What could I do to speed up the search? Thanks in advance.

Edit: extra info.
Indexed columns: PRIMARY (id), organisasjonsnummer, navn, forretningsadresse_fylke, forretningsadresse_kommune, keywords

Not sure about mysql but in mssql like '%val' is a full scan

Multiple keywords in a varchar(200) column is probably not the best design.  Spit the keyswords into separate words into a separate table with a FK back to company.ID.  So each word would be a row. Then you can search on keyword.value = 'xxx' and use an index.


Slave failing to replication in MariaDB 10 I am not able to enable row based replication. I am trying to set up a master slave replication between two nodes running.

Considerations

Server version        10.0.23-MariaDB-log
Protocol version  10
UNIX socket       /var/lib/mysql/mysql.sock.

The scenario I have followed is this:

Shut the prod (mc1), took a cold backup and restored on slave (mc2).
On master ran show master status; prodarchivedlogs-bin.000001 and the pos as 582240
Set these info in slave.
Started the slave, it started throwing up error message:

  160812  3:31:50 [ERROR] Slave SQL: Could not execute Update_rows_v1 event on table radius1.radacct; Can't find record in 'radacct', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log prodarchivedlogs-bin.000001, end_log_pos 587695, Gtid 0-1-79, Internal MariaDB error code: 1032

  160812  3:17:18 [Warning] Slave: Can't find record in 'radacct' Error_code: 1032

  160812  3:17:18 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'prodarchivedlogs-bin.000001' position 587072

I have tried all methods based on my knowledge, but it is just not functioning. I tried using mysqlbinlog tool to extract to a file and then tried inserting, even then it is failing.

The prod my.cnf is as follows

symbolic-links=0
innodb_buffer_pool_size=10G
innodb_data_home_dir= /var/lib/mysql/data2
innodb_data_file_path=ibdata1:10M:autoextend
innodb_log_group_home_dir = /var/lib/mysql/redologs
innodb_log_file_size    = 256M
innodb_buffer_pool_size = 10000M
innodb_flush_method     = O_DIRECT
thread_stack    = 256K
max_connections=100000

expire_logs_days        = 5
max_binlog_size         = 100M

log-bin=/backup/archivedlogs/prodarchivedlogs-bin
binlog_format=row
server-id=1

The slave my.cnf is as follows

skip-name-resolve
skip-slave-start
datadir=/var/lib/mysql/data1
socket=/var/lib/mysql/mysql.sock
user=mysql
symbolic-links=0

innodb_buffer_pool_size=10G
innodb_data_home_dir= /var/lib/mysql/data2
innodb_data_file_path=ibdata1:10M:autoextend
innodb_log_group_home_dir = /var/lib/mysql/redologs
innodb_log_file_size    = 256M
innodb_buffer_pool_size = 10000M
innodb_flush_method     = O_DIRECT
thread_stack    = 256K
max_connections=100000
expire_logs_days        = 5
max_binlog_size         = 100M
log-bin=/backup/archivedlogs/prodarchivedlogs-bin
binlog_format=row
server-id=2

Further details

The master prod machine has 40 GB RAM and has a 16 core CPU.

I followed the process below:

created the rep user and gave the access.
shutdown prod.
scp'ied the files from prod to slave machine.
brought up master instance.
ran the command show master status\G;. took note of bing log and the postion.
brought up the slave instance
configured the slave profile with the bin log file and the position
ran the start slave command.
then ran the command show slave status\G; and I  started seeing the error messages of

  HA_ERR_KEY_NOT_FOUND.

Also:

Tried to run the query mysqlbinlog on the bin log. The report said the file is being used.
Ran the command flush logs; still no results.

Trying all methods to fix this.

Any help will be greatly appreciated.

max_connections=100000 -- Unreasonable, and dangerous.  Even 1000 may be unnecessarily high for the number of simultaneous connections.

10G for the buffer pool -- I assume you have at least 15GB of RAM?

It sounds like the data on the two servers, at least for redacct, is not consistent.  How did you initially populate the Slave?


How to make this nested query :- I've a table called foo which stores the child and parent information of some entity called bar, with level information as well. If there is no parent of bar, then in the parent_id column it will store its own id.

id  parent_id
1   1
2   2
3   3
4   1
5   2
6   4

So in my example, the bar entities with an id of 1 and 2 are at level 0 (no parent) and have children. The bar with an id of 4 has a child but it also has a parent, so its not at level 0.

I am trying to write a mysql query which will return all the bars that have children and are at level 0 (no parent).

I am able to write the query below, which shows the bars that have children:

select * from foo where id!=parent_id;

And output of above query is :

id parent_id
4   1
5   2
6   4

But in above query, parent_id 4 is not at level 0 and I need a query that produces the output below:

id parent_id
1   1
2   2

Let me know if you need any additional information from my side.

Edit :- Thanks Evan for pointing out my mistake, actually I want ids "that have children and are at level 0". Also I've updated the correct O/P.

You just need to add to the where statement to filter for those that are the primary level

SELECT *
FROM foo AS f1
WHERE id = parent_id
AND id IN (
  SELECT f2.parent_id
  FROM foo AS f2
  WHERE f1.id = f2.parent_id
    AND f2.id != f2.parent_id
);


SQL Server 2008 RC2 Edit transaction Log I have been handed an issue with a database where last nights backup will not restore. Having spent all morning on this and getting nowhere, I started to wonder about other things I could do to fix my current issue, then I can go back to the backup problem without having to fend off irate users.

I have isolated the individual transaction that has caused the issue, and have its id by using sys.fn_dblog

How can I edit the records in the transaction file to undelete them?

Okay, there is a reason why tools that can do this are expensive. It's by no way easy to do. However this blog post can help you.

How to create undo update statements

Since you have found the transaction, you can filter the result by using your transaction id.


SELECT query to find rows for specific entry I have a table location. And I want to extract rows from it for the column pattern.

And I did this query SELECT COUNT(DISTINCT username) FROM location;
+--------------------------+
| COUNT(DISTINCT username) |
+--------------------------+
|                    58636 |
+--------------------------+

So I got the whole number of rows.

My question is: how to get rows for each different username entry but not on the whole. The username meaning is not known, and goal to see how many rows each username has.

So for e.g.

username Nick - 10 rows

username Paul - 20 rows

I think you are looking for a simple GROUP BY

SELECT username, COUNT(*)
 FROM location
group by username;


KNN search two arrays with 2000 elements What I did:

installed postgresql 9.3.1
installed postgis 2.1.2
I tried to find the distance by selecting column input_variable_1 with value 50.

I got the following result.

select input_variable_1 <-> 50  as distance  from test_ata limit 10;
 distance
       48
        2
       17
       29
       44
       37
       45
       17
       49
        2
(10 rows)

what i want:

input: column which stores array values say 2000 elements in a single record.

I came across cube extension and postGIS there i can get distance between two points by using tree and distance concepts for lower dimentsions upto 100.

How to find the distance between two arrays having elements atleast 2000 in postgresql?
which tree is best for 2000 dimension indexing in postgresql?
finally i want to know how to use KNN search in postgresql?

You have one option, you can recompile the cube extension to use more than 100 dimensions

KNN Search is done with PostGIS using the <-> operator on two points. You can find out how to do that in the docs.. It doesn't operate in Nd-space. PostGIS isn't an abstract library for math. It's for real world geospatial problems. KNN in 1d, and 4d+ is not a real world problem, or at least not in the domain that PostGIS would be interested in.

I would suggest checking out my other answer here,

Searching in n-dimensional space


SQL Server - TIMESTAMP caused exception I am syncing from PostgreSql to MSSQL using SymmetricDS. Some tables of the source database have timestamp field set too '0001-01-01 00:00:00.0' and it could not be able to insert to destination(SQL Server) database.

Is there anyway to work around this ?

AcknowledgeService - The outgoing batch 001-987 failed: Parameter arg '0001-01-01 00:00:00.0' type: TIMESTAMP caused exception: Only dates between January 1, 1753 and December 31, 9999 are accepted.

Instead of using the (old) datetime datatype in SQL Server, you can use the (newer and better) datetime2. Among other things, it accepts earlier dates, back to year 1.

A tip is to specify how many digits you was for the second fractions. If you don't do it, you get 7 (as in 12:23:45.2314534). If you can get by with less, then specify for instance datetime2(0) and you get zero decimals. Or whatever you want/need.


Filter Data to different data files on SQL server I'm sure this is possible but I have no idea how or where to find the information on how to do it.

Scenario:
I have a reporting database (on SQL2008R2) which is split up into multiple file groups and multiple files. We're reaching the data limit of the current SAN drive and have reclaimed space from another SAN to provide the space we need.

Problem:
The 'new' SAN is on a 1GB connection whereas the current SAN is a 10GB connection, the intention is to keep all current data on the current SAN and put all old data or data for inactive users onto the slower SAN as it will not be used for anything other than very specific reports.

Pseudo Solution 1

ALTER DATABASE Reporting
ADD FILE(
    NAME =DataFile2,
    FILENAME = E:\OldSan\Reporting\Data2.ndf,
    SIZE= 512000MB,
    MAXSIZE = 716800MB,
    FILEGROWTH = 2048MB,
    DATAQUERY ='SELECT ALL OLD/INACTIVEDATA'
)

Thoughts

I know the above is not possible strictly like that and it would need to be done on a table by table basis of the data to move across. but not sure where to start or how to do it.

Theoretically(in my head) this would be done with splitting the clustered index on a filter across the filegroups (and the files just deal with it), there's options on a table properties for storage for filegroup or partition scheme, however on a clustered index this seems to always be greyed out so I'm not sure if I'm just clutching at straws there.

NOTE: it is potentially possible to upgrade from SQL 2008R2 to 2012 if that is absolutely necessary(but not desired)

EDIT: The query for this would ideally be based on data in another table, so DataToOldSAN = where userid in (select id from users where active =0)

Thanks
Ste

Table By Table - You can relatively easily do this.  You need to create a filegroup, then a file in that filegroup and then you can move the tables you want to the new filegroup.  Use the CREATE INDEX syntax with DROP EXISTING=ON and specify the new filegroup.

The clustered index is the table so moving them this way moves the table.

Your alternatives are to use partitioning but that requires enterprise and you won't be able to control it the way you are discussing.

MSDN CREATE INDEX

Example for AdventureWorks2014 and the Person.Person table.

USE [master];

--Creates a new filegroup.
ALTER DATABASE [AdventureWorks2014] ADD FILEGROUP [SLOW]

--Adds a file to the filegroup...
ALTER DATABASE [AdventureWorks2014] ADD FILE ( NAME = N'AdventureWorks2014_SlowSAN'
                                , FILENAME = N'C:\SQLData\JDF2014\Data\AdventureWorks2014_DataSlow.MDF'
                                , SIZE = 524288KB
                                , FILEGROWTH = 131072KB ) TO FILEGROUP [SLOW];

USE AdventureWorks2014

--Move the clustered index (which is the table) to the new filegroup.
CREATE UNIQUE CLUSTERED INDEX PK_Person_BusinessEntityID
    ON Person.Person (BusinessEntityID)
WITH (DROP_EXISTING =  ON )
ON SLOW
GO


How can I switch to sa account from guest session in SQL server 2012? I have a situation where I can log into my server as guest, and see that there is no password on the sa account.  However when I try to login as sa with null password over the network it is rejected.

My question is, since there is no sa password, how can I do the equivalent of a unix sudo or su command, while logged in as guest?  I looked into runas and changing passwords but guest lacks the very privilege I am trying to gain.  ('alter login sa..' doesn't work.)

Also, why does the server refuses login when there is no sa password?

This is SQL Server 2012 SP1 Web edition 64-bit 11.0.3153.0.

For example, there is a command in MySQL called change_user. This command can be used as the name suggests to change a user during a MySQL session. There may be a way to do this in SQL Server but I am not a SQL Server expert so I seek more experienced input.

When signed in under the guest account, you have very few privileges by default. The sa password is displayed as blank because you do not have the necessary rights to view it. Disabling the guest account is a security best practice, where it is not required.

The real sa password is almost certainly not blank. SQL Server does not allow a blank sa password since the 2005 release.

SQL Server does provide a way to impersonate another login or user (assuming you have sufficient rights). See EXECUTE AS (Transact-SQL) for details.

Note that SETUSER is deprecated, provided only for backward compatibility, and has inferior functionality.


What are the consequences of dropping or changing the user name for the postgres DB account? I feel confident I understand what's involved at the db level:

If dropping, I'd need to create users for each sys admin, give them SUPERUSER, and then drop the postgres user.
If changing the name, I'd need to alter the postgres account.

My problem, though, is that I'm not sure what the consequences of this action would be. For example, what would it means from the point of view of the Linux postgres user? When I su - postgres, I can run psql and I'm able to connect to the database. I assume that capability goes away if the postgres DB user is gone.

Also, the database is started using the postgres linux user. Is there some issue if I remove the postgres user that would make it so the database wouldn't even be able to start?

From ps:

/usr/pgsql-9.3/bin/postgres -D /var/lib/pgsql/9.3/data PG_GRANDPARENT_PID=1 USER=postgres PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin PWD=/ PGLOCALEDIR=/usr/pgsql-9.3/share/locale LANG=en_US.UTF-8 PGSYSCONFDIR=/etc/sysconfig/pgsql SHLVL=1 HOME=/var/lib/pgsql LOGNAME=postgres PGDATA=/var/lib/pgsql/9.3/data _=/usr/pgsql-9.3/bin/postgres

I'm running PosrgreSQL 9.3 on Cent OS 7.

The Linux postgres user is entirely different from the postgres database user. It's created via the rpm package. When you're logged in as the system postgres user, it assumes the database user and database name that you're wanting to connect to is the same as the operating system user that you're attempting to connect from. That's why a simple psql works, and logs you into the database as the postgres user and database.

The postgres database is just a copy of template1. You can delete it or recreate it with few problems. You won't be able to drop the postgres user, however, because it's required by the database system.

user1@[local]:5432:user1:=# DROP ROLE postgres;
ERROR:  cannot drop role postgres because it is required by the database system
Time: 50.570 ms
user1@[local]:5432:user1:=#

However, you can block the login of the user by setting all the connections that you use to reject for the postgres, which will keep users from using that particular user for login, instead of their assigned users.


How do I impose a CONSTRAINT on a TEXT column in PostgreSQL 12? I want a column with a single text character as a classifier so it's not worth the overhead creating another whole table and relating to it.

How do I constrain the column to a list of acceptable characters?

E.g. how would I constrain column my_classifier CHAR(1) NOT NULL, to one of [abcd]?

Here is one method to do it:

create table test
(
  dd char NOT NULL,
  constraint dd_c check ( dd in ('a', 'b', 'c', 'd'))
);


Postgres with pgpool architecture Below is an example pgpool architecture:

This implies that you only need to have pgpool on a single server; is this true? When I look at the configuration I also see that you configure backends within pgpool.conf; so it further implies this. But, it doesn't explain why I see pgpool on backend servers also.

When looking over the documentation I also see:

  If you are using PostgreSQL 8.0 or later, installing pgpool_regclass function on all PostgreSQL to be accessed by pgpool-II is strongly recommended, as it is used internally by pgpool-II.

So I'm not sure what to think; if it's best practice to have pgpool on all backends or just a dedicated server?

Generally you would not install Pgpool on the backend servers. What you see in your picture is the most common configuration. Pgpool is a standalone server which essentially sits in front of the databases. The two Postgres servers are often configured with streaming replication; with one being the master and the other the slave.

This allows Pgpool to load balance all read queries between the two (or more) databases. Any queries which involve any writes, will be routed to the master server which in turn replicates to the slave.

As @Neil McGuigan said, you can also have multiple Pgpool servers to achieve better high availability. Technically you could install Pgpool on the database servers in this configuration, but this would be bad practice. Running multiple Pgpool servers is a much more complex configuration. If this is your first time with Pgpool, I would start with one Pgpool server before getting two to work.

In either configuration, your application server thinks it is just connecting to a single Postgres database.

About pgpool_regclass, which should really be a separate question, this is from the Pgpool FAQ:

  If you are using PostgreSQL 8.0 or later, installing pgpool_regclass function on all PostgreSQL to be accessed by pgpool-II is strongly recommended, as it is used internally by pgpool-II. Without this, handling of duplicate table names in different schema might cause trouble (temporary tables aren't a problem).

  If you are using PostgreSQL 9.4.0 or later and pgpool-II 3.3.4 or later, 3.4.0 or later, you don't need to install pgpool_regclass since PostgreSQL 9.4 has built-in pgpool_regclass like function "to_regclass".

If you need this, it is just some SQL code run on your Postgres master server to add a function Pgpool uses.

With regclass, there is an additional step you have to do (I was thinking of insert_lock). If you are compiling from source (generally most distributions have really outdated versions of Pgpool), you will have to compile a Postgres library as well.

If you compiled from source, you will have to go into the .../pgpool-II-3.X.X/src/sql/pgpool-regclass folder and do a ./configure; make.

Copy the pgpool-regclass.so file into the Postgres extension directory. On my Ubuntu 14.04 server (just using the Postgres 9.3 package install), it is located at: /usr/lib/postgresql/9.3/lib. Remember to do this for all Postgres servers.

Once that is complete, then you can run pgpool-regclass.sql on the master. This just maps the pgpool_regclass function to the library you copied over.


Full text search error: Invalid locale ID was specified Query involving full text search is generating the following error message after restoring the database to a new server:

  Invalid locale ID was specified. Please verify that the locale ID is correct and corresponding language resource has been installed.

It was working alright on the previous server.
How can I resolve it? I'm on SQL Server 2008 R2
Thank you.

The answer was to Load Licensed Third-Party Word Breakers (MSDN)

From that link:

  SQL Server 2008 R2 includes licensed third-party word breakers for the following languages:

  Danish
  Polish
  Turkish

  These word breakers are available but are not installed by default, and must be manually registered and then added to the list of LCIDs that are supported for full-text indexing and querying.

  These word breakers are not enabled by default because they are owned by third parties who have not yet provided the level of testing, security, and robustness that is required for them to be enabled by default.

The main steps involved are:

Obtain Instance Name for Each Instance of SQL Server
Obtain the FTData Path for Each Instance
Register a Word Breaker and the Related Linguistic Components

Add the COM ClassID(s) for the Word Breaker and Stemmer Interfaces
Add a Key to the <InstanceRoot>\MSSearch\Language Node
Add Configuration Values That Give the Location of Each Linguistic Component

Update the List of Languages Supported by Full-Text Search

See the linked documentation for the full details.


Why MSDB transaction log file grew while MSDB was in Simple recovery mode? I have Availability Group of 3 servers, and transaction log backups for databases in Availability Group are taken on a secondary replica, each 1 minute

It was working like this for several months

  Now MSDB on this secondary replica is 6 GB data file size (mdf) and is only 4% full and log file size is 14.2 GB (ldf) and is 7% full

I can only remember that I made MSDB 500 MB both data and log files in the beginning when server was setup...

I thought log file grew because of often transaction log backups and FULL recovery mode - but no, MSDB has SIMPLE recovery mode on that server

So it left me wondering, what caused both data and log file growth for MSDB ?

For me, large growths in MSDB are from adding and deleting the table entries within the backupset tables. Do you have a history purge or anything running?


SQL-Server login-error of SQL-Server Agent Account I have to manage a configured SQL-Server version 2005 and have setup Database Mail successfully - test mail works.

Also I've changed the agent service to a domain user with (currently) domain admin and db admin privileges.

Now I'm getting numerous login errors for this user

  [298] SQL-Server error:18456 error logging on user domain\sqlagent, [SQLSTATE 28000]

when running jobs with notification. Any idea how that can happen, when the agent service can be restarted without problem?

I've searched the web extensively but the only answers I can find are related to wrong passwords.

The user account that is running the SQL Server agent might not have permissions to use the database mail profile you can check that with EXECUTE msdb.dbo.sysmail_help_profileaccount_sp;

But you are having some issues with the agent account security, please try the following steps.

Remove all extra privileges from the Agent user account (or create a new domain account) Running the SQL Server agent with domain admin privs is just terrifying.
Change the user for the Agent service using the SQL Server Configuration Manager. You should never change it any other way as it will set all the necessary permissions. Change the account to network service and then back again and make sure beforehand that the agent user is neither a local or domain administrator.
Check the mail profile on the SQLAgent settings and make sure that it's not private and that the agent user account can use it (The user account will be made sysadmin by the Configuration manager and should be able to use all accounts)


How to get minimum and maximum for grouped timestamps I would love to know how to create a view that groups timestamps in 10 minute nearest 10 minute intervals and contains each minimum and maximum timestamp for each.

So a table that looks like this:

| Hero         | timestamp           |

| Batman       | 2016-12-08 12:00:00 |
| Batman       | 2016-12-08 12:07:00 |
| Batman       | 2016-12-08 13:00:00 |
| Batman       | 2016-12-08 14:00:00 |
| Wonder Woman | 2016-12-08 10:15:00 |
| Wonder Woman | 2016-12-08 10:18:00 |
| Wonder Woman | 2016-12-08 10:25:00 |
| Wonder Woman | 2016-12-08 10:30:00 |

would result in a view like this

| Hero         | start_time          | end_time            |

| Batman       | 2016-12-08 12:00:00 | 2016-12-08 12:07:00 |
| Wonder Woman | 2016-12-08 10:15:00 | 2016-12-08 10:30:00 |

or this:

| Hero         | start_time          | end_time            |

| Batman       | 2016-12-08 13:00:00 | NULL                |
| Batman       | 2016-12-08 14:00:00 | NULL                |
| Batman       | 2016-12-08 12:00:00 | 2016-12-08 12:07:00 |
| Wonder Woman | 2016-12-08 10:15:00 | 2016-12-08 10:30:00|

Either solution would be fine.

Raster of 10-minute intervals
I suggest to group by a combination of "hour" and 10-minute interval:
SELECT hero
     , min(timestamp) AS start_time
     , CASE WHEN count(*) > 1 THEN max(timestamp) END AS end_time
FROM   tbl
GROUP  BY hero
     , date_trunc('hour', timestamp)
     , EXTRACT(MINUTE FROM timestamp)::int / 10
ORDER  BY 1, 2;  -- optional

Refer to the chapter Date/Time Functions and Operators in the manual.
EXTRACT(minute FROM timestamp) extracts the minute part of the time The expression. After the cast to integer (::int), integer division (/ 10) effectively rounds to 10-minute intervals (0 - 5).
The CASE expression only adds an end_time if more than one rows fall in the same 10-minute interval.
I advise not to use the "timestamp" as identifier. It's a reserved word in standard SQL and base data type in Postgres.
Groups defined by gaps of 10 or more minutes
If "groups" are defined by gaps of 10 minutes or more between rows of the same hero:
SELECT hero
     , count(*) AS ct  -- optional
     , min(timestamp) AS start_time
     , CASE WHEN count(*) > 1 THEN max(timestamp) END AS end_time
FROM  (
   SELECT hero, timestamp, count(step OR NULL) OVER (ORDER BY hero, timestamp) AS grp
   FROM  (
      SELECT *
           , lag(timestamp) OVER (PARTITION BY hero ORDER BY timestamp)
           < timestamp - interval '10 min' AS step
      FROM   tbl
      ) sub1
   ) sub2
GROUP  BY hero, grp;

Detailed explanation:

Select longest continuous sequence

db<>fiddle here
Old sqlfiddle


Use An Out Variable And Perform Calculation In One Query I am wanting to use an out variable, but I need to perform calculations as well.  Any way I write my query I keep getting this error:

  Msg 141, Level 15, State 1, Line 1
  A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.

Here is my DDL

Declare @SQL nvarchar(MAX), @stat float

Create Table PPerformance
(
  name varchar(500)
  ,attempts int
  ,success int
)

Insert Into PPerformance (name, attempts, success) Values
('Bob', '22', '10')
,('Red', '14', '12')

And this is my query syntax

Set @SQL = 'Select name, @stat = attempts/success FROM PPerformance where name = Bob'

EXECUTE sp_executesql @SQL, N'@stat float OUTPUT', @stat OUTPUT;
Select @stat As Stat

Drop Table PPerformance

What do I need to change so that I can store the value from the equation in the variable @stat

You need to assign the name column to a variable, and return it as well as the @stat variable:

CREATE TABLE dbo.PPerformance
(
  [name] VARCHAR(500)
  ,attempts INT
  ,success INT
)

INSERT INTO dbo.PPerformance ([name], attempts, success)
VALUES ('Bob', '22', '10')
    , ('Red', '14', '12');

DECLARE @SQL NVARCHAR(MAX);
DECLARE @name VARCHAR(500);
DECLARE @stat FLOAT;
DECLARE @vars NVARCHAR(MAX);

SET @vars = N'@stat float OUTPUT, @name varchar(500) OUTPUT';

SET @SQL = N'SELECT @name = pp.name
    , @stat = pp.attempts / pp.success
FROM dbo.PPerformance pp
WHERE pp.name = ''Bob'';';

EXECUTE sys.sp_executesql @SQL, @vars, @stat OUT, @name OUT;

SELECT Name = @name
    , Stat = @stat;

DROP TABLE dbo.PPerformance;

The error is coming from this part of your code:

Select name, @stat = attempts/success

This is attempting to assign a value to the @stat value and show the content of the name column.  You cannot perform both operations in a single statement.

There are a couple of items in your source code that are potentially troublesome:

You should always specify the schema name when referencing objects in T-SQL.  So, select column1, column2 from mytable should be select column1, column2 from schema.mytable.
End each statement with a semi-colon:  select column1, column2 from schema.mytable;
Get in the habit of using an alias for tables referenced in T-SQL statements.  For example:  select mt.column1, mt.column2 from schema.mytable mt;
Don't use reserved names as column or table names.  You've named the first column in your table name, which is a reserved word; I'd change that to PerfName or something.


What does select * from table where column * 'value'; do? select * from table where column * 'value';

I accidentally wrote * for my where statement instead of =, and the query returned all rows in the table.

Made me wonder why an error wasn't thrown instead.

What does the * operator do in a where statement?

The SQL

WHERE column * value

is a numeric operation of multiplication, and when the value of the expression (column * value) isn't 0, that is equivalent to TRUE so will return the row.


MySQL password is changed automatically I'm the lone user of my computer. I haven't changed my MYSQL root user password, but it is somehow changed to empty password. Help me out !

My default sakila db is also gone. I specified engine=innodb for one of the tables.Would there be any effect

The mess was because I was in middle of a crash recovery. It is good now. No ghosts,no hackers.


Increase in temporary tablespace while import When i am importing some tables in my database, the temporary tablespace is getting full. This never happened earlier for other tables.

Please help me know why this is happening and any solution for it.

ParFile:

ParFile:
Directory=IMPDIR
DUMPFILE=expdp.dump
TABLE=scott.table1,scott.table2,scott.table2,scott.table3,scott.table4
TABLE_EXISTS_ACTION=append
CONTENT=DATA_ONLY
REMAP_SCHEMA=scott:hr
REMAP_TABLESPACE=users:testtbs
PARALLEL=10
LOGFILE=impdp.log

The size needed in a temporary tablespace depends on the data volume manipulated, but including potentially other actions also requiring temporary space. What you should be doing is this : monitor temporary tablespace usage, (temporarily) expand the temporary tablespace.

Maybe, you need to recreate your temp tablespace. That is, if there is corruption of some kind. One way - but not the only one - is stopping the database, rename the file(s) of the temporary tablespace, tail the alert.log, and startup the database. Obviously, don't do without backup, and don't test this on production.


How do I create "groups of users" and set permissions in SQL Server? I am new to SQL Server, and I need to do something like the following:

Create a group of users that will allow them to only SELECT from Table 1, but be able to SELECT, UPDATE, DELETE and INSERT INTO Tables 2 and 3;
Create another group of users that will allow them to SELECT, UPDATE, DELETE and INSERT INTO all three of the aforementioned tables

All three of the tables are in the same database.
May someone please provide some sample code so that I can accomplish something like that? Thanks!

What you can do in sql server is to create a group of permission.
They are called Database Roles. You can use pre-build roles or create some new.
https://docs.microsoft.com/en-us/sql/relational-databases/security/authentication-access/database-level-roles?view=sql-server-ver15
About the group of users you can't with the sql authentication but if you use the windows authentication you can relay on local user groups (uased as login in sql server) or active directory user groups (add them as login in sql server)
https://docs.microsoft.com/en-us/sql/relational-databases/security/authentication-access/create-a-login?view=sql-server-ver15


Update new column with value from old columns for all the rows I've got an odd db design in place that was here when I got on my new work space. We have a column named params which contains some string data which appears that it belongs in more than one column, this is the example value of column params :

"---
:body: "Hello\r\nContacting you that I'm ok with your plan.\r\nPlease send me regular updates\r\nThanks"
:emp_id: 4818
"

So I was planning to separate body and emp_id in their own columns so that this table can behave like normal one so we can query the data like normal. Having the data in this format is very hard to query, yet they have silly queries to get/match values.

Anyways I wrote some of my regexes to extract those values and save them to these colums (body) :

select a[1]
from (
    select regexp_matches(params, ':emp_id: ([0-9]+)', 'g') a
    from exchange where id = 483193
) s

for emp_id

select b[1]
from (
    select regexp_matches(params, 'body: (.+):emp', 'g') b
    from exchange where id = 483193
) s

Where id is an id of one exchange. I want to do this for all rows, I tried something but was spinning in the circles, this looked to be most promising but doesn't work :

update exchange
set emp_id = (
  select a[1]
  from (
   select regexp_matches(parameters, ':emp_id: ([0-9]+)', 'g') a
   from exchange where id in (select id from exchange)
  ) s
) where

How can I update all rows? To fill the new columns emp_id and body from existing values in params?

Update:

I've got a massive performance issue here. My db has 500k+ records. And doing update like this :

UPDATE exchange
SET emp_id=cast(subquery.a[1] as int)
FROM (select regexp_matches(params, ':emp_id: ([0-9]+)', 'g') a
    from exchange) AS subquery

Takes really long time. Can this be optimized in some manner?

The solution is very simple.  If you do

UPDATE exchange
SET emp_id = {some expression here on the params column}; -- no WHERE clause!

it will do exactly what you want: pick the params from the row to be updated, calculate the expression and update emp_id with its value.  As there is no WHERE clause, it will go through the whole table.  You don't need the ID of the row or anything else.


Large accounting table, best way to partitioning by two dates I need to create a log table to stores the connections to out network (last 3 years, after that the log will go into backup). The hardware/software used is proprietary, and for accounting it just call our custom script with some arguments like this:

when a user connects (our_script START user mac ip);
when a user disconnects (our_script STOP user mac ip in_bytes out_bytes more)

Sometimes we do not receive the disconnect message. So we need to adapt to this.

So far I came up with this structure for the accounting table:

CREATE TABLE `accounting` (
  `user` varchar(50) NOT NULL DEFAULT '',
  `mac` varchar(20) NOT NULL DEFAULT '',
  `ip` varchar(15) NOT NULL DEFAULT '',
  `ipv6` varchar(39) DEFAULT NULL,
  `start_datetime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `stop_datetime` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `in_bytes` bigint(32) unsigned DEFAULT '0',
  `out_bytes` bigint(32) unsigned DEFAULT '0',
  `more_columns` varchar(255) default NULL,
  PRIMARY KEY (`user`,`mac`,`ip`,`start_datetime`,`stop_datetime`),
  KEY `prim_ipv6` (`user`,`mac`,`ipv6`,`start_datetime`,`stop_datetime`),
  KEY `user` (`user`) USING HASH,
  KEY `mac` (`mac`) USING HASH,
  KEY `ip` (`ip`) USING HASH,
  KEY `ipv6` (`ipv6`) USING HASH,
  KEY `start_datetime` (`start_datetime`),
  KEY `stop_datetime` (`stop_datetime`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

The query used for writing are (these needs to be real fast):

when a user connects a simple insert
when a user disconnects update accounting set stop_datetime=now(), in_bytes=$in_bytes, out_bytes=$out_bytes, more_columns=$more where user="$user" and ip="$ip" and stop_datetime="0000-00-00 00:00:00" order by start_datetime DESC limit 1;, this is a workaround in the case we have more then one row for start, we just update the last start.

A better workaround, is to update the stop_datetime for rows without it, at every start, and use another column for storing that this is not a normal stop, I think I will go with the second workaround.

We have 2 ways to select from this table (this could be slower max 1 - 2 sec):

select the last 50 connection for a user: select ... where user="$user" order by start_datetime DESC limit 50;, for this the user HASH key is handy, beacause AFAIK HASH is better than BTREE for equality.
select witch user was connected with ip=$ip on date=$date select ... where ip="$ip" and $date between start_datetime and stop_datetime;

This table will hold between 0.5 to 1 bilion rows, that's why I thought to partition it.
The best option is to partition it by month, but I have 2 relevant dates, and at the begining/end of the month start will be in one partition, stop will be in another.

The questions:

Witch is the best way to do partitions, by start_datetime, by stop_datetime, by start_datetime with subpartitions for stop_datetime ?
Do I realy need to partitionate ?
Do you have any other suggestion on how to improve this ?

Thank you

Since stop_datetime will not be known at insert time the partition must be done by start_datetime.

I think partitioning by day (date_format(start_datetime, '%Y-%m-%d')) would be adequate since 1 billion rows divided by (3 years * 365 days) ≃ 900,000 rows per partition

Updating indexes is expensive and it happens at inserts, updates and deletes. Keep the indexes to a minimum. If the main queries will be those selects then I would keep the primary key like the following and trash all the other keys:

PRIMARY KEY (`user`,`start_datetime`,`stop_datetime`,`mac`,`ip`)


Can a query write to the binary log BEFORE it has finished executing? I have a Database which is a Slave and a Master. e.g. it replicates data from our main site, and then onwards to a set of slave databases. (this is mainly a test facility so not monitored out of hours).

On Sunday morning at 7:55 it started executing an INSERT query. This is a standard query that executes thousands of times a day. Its a single query, not part of a transaction. There are no triggers on that table. This table (and all others) are INNODB.

But, on this occasion it got stuck. When I came in the office today it had been running for 24 hours (apparently). And replication was a day behind.

However, when I looked in the Binary logs it showed as COMMITed.

#160814  7:55:25 server id 21  end_log_pos 103319397    Query   thread_id=635785334 exec_time=4294967294    error_code=0
SET TIMESTAMP=1471157725/*!*/;
INSERT INTO tablename . . .
/*!*/;
# at 103319397
#160814  7:55:25 server id 21  end_log_pos 103319424    Xid = 284047583
COMMIT/*!*/;
# at 103319424

I checked the Slave databases, and it was on present on there as you'd expect.

I have a monitoring system which records (amongst other things) the processlist, and it shows that for that INSERT query it was perpetually stuck at STATE = UPDATE.

I also checked the slow query log to see if it was in there, and it wasn't.

My understanding was that a query couldn't be written to the binary log until it was commmited on the master, so can anyone explain what has happenned here?

There was a knock on affect in that once out test system tried to connect at 8:38 this morning, the first SELECT query also got stuck, and then it refused all connetions. So I was forced to restart the database.
At which point it ran crash recovery, and carried on as if nothing had happenned.

It is MySQL 5.5.47 and using Statement Based Replication.

well, it doesn't explain why the database locked up but it does answer part of my question:

  When a transaction is sent to a MySQL server running with binary
  logging enabled, the transaction is executed normally, and just prior
  to completing the transaction the server records the change serially
  in the binary log. Once this is done, it tells the storage engine that
  the transaction is ready to be committed.

https://www.packtpub.com/books/content/setting-mysql-replication-high-availability

Although, I've now just read the following:

The SQL statements are not

  The SQL statements are not
  written into the binary log until the transaction commits,

which is from the 'Understanding MySQL Internals' book.


Delete the N oldest entries grouped by multiple columns I´d like to delete the oldest entries of my database, but in each category shall remain at least N words of each char.

Structure of TABLE words

id | word | char | category

For example I have the following entries:

id | word | char | category
1     abc     a       1
2     abc     a       1
3     abc     a       1
4     bcs     b       1
5     bcs     b       1
6     bcs     b       1
7     csd     c       2
8     csd     c       2
9     asd     a       2
10    asc     a       2

For N=2 the following entries should be removed as their id´s are the lowest:

1     abc     a       1
4     bcs     b       1

Can somebody help? I tried various things (GROUP BY, HAVING, INNER AND OUTER JOINS) but it does not work.

If I understand you requirements clearly, you need to delete rows that are grouped on the basis of char and category (leaving minimum latest N rows)

Updated: Temporary table workaround

This can be achieved by

Option 1:

create temporary table test_table_rows_temp SELECT * from test;

DELETE t1
FROM
    test  as t1
WHERE
    t1.id < (
            SELECT
                min( t2.id )
            FROM
                test_table_rows_temp as t2
            WHERE t2.category =  t1.category
                    AND t2.`char` =  t1.`char`
                    AND t2.id >  t1.ID
            HAVING count( t2.id ) >= 2
        )

I have created a temporary table to store the results of the test table, this helps to ease out the query

Option 2:

There's a workaround to avoid manually creating a temporary table by using a nested sub-query instead

DELETE t1
FROM
    test  as t1
WHERE
    t1.id < (
            SELECT
                min( id )
            FROM (
                SELECT
                    id,
                    category,
                    `char`
                FROM test
            ) AS t2
            WHERE t2.category =  t1.category
                    AND t2.`char` =  t1.`char`
                    AND t2.id >  t1.ID
            HAVING count( t2.id ) >= 2
        );

The nested sub-query internally too creates a temporary table :)

The 2 rows as mentioned above (ids 1, 4) are deleted

The query simply gets minimum - N + 1 id in the group of category, word (ordered by the latest id) and then checks if the current row id is less than the one retrieved (within the same group(s))

SQLFiddle

Hope this helps


Vacuum increases bloat size as reported by Heroku's bloat SQL query I am using using the Heroku command as described here to detect bloat in tables. One of our tables reported a bloat size of around 7GB, but running vacuum on it, then running the same bloat command, reports close to 21GB of "waste". I have no idea how this can happen. I understand the various conditions under which a table can bloat, even this dramatically, but not after a vacuum, I have never seen such a case.

Any ideas why this might happen? The table in question does have a few TOAST tuples, perhaps that might be it. I also can't tell if that query from Heroku accounts for TOAST waste, but my understanding is that vacuum would take care of those as well. Local tests show the "waste" column from that bloat query staying pretty stable for small sizes and decreasing for larger values, but never an increase, which makes this rather strange.

All these queries that estimate bloat are not precise, but rely on heuristics.

If you want to find out the truth, install the pgstattuple extension and run the pgstattuple function on the table in question. That will tell you how much space is occupied by user data, dead tuples and empty space, respectively.


SQL Server 2012 Availability Group issue I need some assistance and some bandages for shooting myself in the foot. This is a DEV environment but I need to bring it up.

I am using SQL Server 2012 Enterprise on Windows 2008 R2. Somehow our AG listener group disappeared. I tried to recreate the AG listener and it was giving error regarding computer account not having permissions. The genius inside of me decided to delete AG group from Windows Cluster Admin and now I cannot recreate AG group (I should've done it via SQL Server instead of Windows).

I keep on getting the following error :

  Creating availability group resulted in an error. (Microsoft.SqlServer.Management.HadrTasks)

  ADDITIONAL INFORMATION:

  Create failed for Availability Group 'SQLAG_D'. (Microsoft.SqlServer.Smo)

  An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

  The availability group 'SQLAG_D' already exists. This error could be caused by a previous failed CREATE AVAILABILITY GROUP or DROP AVAILABILITY GROUP operation. If the availability group name you specified is correct, try dropping the availability group and then retry CREATE AVAILABILITY GROUP operation.

  Failed to create availability group 'SQLAG_D'. The operation encountered SQL Server error 41042 and has been rolled back. Check the SQL Server error log for more details. When the cause of the error has been resolved, retry CREATE AVAILABILITY GROUP command. (Microsoft SQL Server, Error: 41042)

Does anyone have any suggestions?

The AG Group is already dropped in Windows Cluster and it is not showing in SQL Availability Group. The sys.availability_groups system catalog view has the "deleted" AG in it. There is no mention of SQLAG_D in it.

You have to do following :

Drop the availability group then
using regedit, (first backup the registry of old availability group that starts with HKEY_LOCAL_MACHINE\Cluster\HadrAgNameToldMap) and then delete that key and
Finally create a new Availability Group with new name.

Note: Extra Caution .. as you are dealing with Windows Registry, Back it up first


How to retrieve missing values of an id from a list? have a data structure that is value/tag inside a table on Oracle sql
Lets say I have this structore
exTable

ID
TAG
VALUE

1
1
x

1
4
y

2
2
x

2
3
y

2
4
z

I want to retrieve the missing tags for a list of values I have, for example [1,2,3,4,5]
In this case Im searching for this as an output if I look for (1,2)

ID
MISSINGTAG

1
2

1
3

1
5

2
1

2
5

Been trying with temp tables and joins but im still missing something because when i try to join I either dont get anything or just get the ones that do exists, I achieved want I want for one id using a minus
select column_value as COL_NAME
from table(sys.dbms_debug_vc2coll('1',
'2',
'3',
'4',
'5'))
MINUS
SELECT TAG FROM exTable WHERE ID='1';
But for doing it for a list of IDS im have not had luck
If im duplicating the question let me know

I don't fully follow your examples, if you could clarify them a little it might help, but in general it sounds like you're trying to find what data exists in one table but not another? If so, then you want to use a LEFT JOIN and a WHERE clause that filters on the missing records like so:
SELECT *
FROM TableWithAllRecords
LEFT JOIN TableMissingRecords
    ON TableWithAllRecords.KeyField = TableMissingRecords.ForeignKeyField
WHERE TableMissingRecords.ForeignKeyField IS NULL


A simple Query causing high CPU in SQL Server 2008 We are noticing below SQL is causing very High CPU and its been executed 10,000 times. Is there a way we can tune this T-sql?

SELECT ISNULL(max(trans_seq),0) +1
FROM inv_inventory_journal
WHERE organization_id = @P0
AND wkstn_id = '600'

From Execution Plan, it is suggesting to create the following index.

/*
Missing Index Details from ExecutionPlan1.sqlplan
The Query Processor estimates that implementing the following index could improve the query cost by 98.8039%.
*/

/*
USE [LKY_Xcenter]
GO
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[inv_inventory_journal] ([organization_id],[wkstn_id])
INCLUDE ([trans_seq])
GO
*/

Table Structure

Based on your schema it looks like trans_seq can't be null so I would remove the isnull from the select as that would have a small amount of CPU overhead in just checking the value that is returned from the max function.

SELECT max(trans_seq) +1
FROM inv_inventory_journal
WHERE organization_id = @P0
AND wkstn_id = '600'

Also if your query is always setting the wkstn_id to a constant 600. You could create a filtered index on that column.

USE [LKY_Xcenter]
GO
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[inv_inventory_journal] ([organization_id],[wkstn_id])
INCLUDE ([trans_seq])
WHERE [trans_seq] = 600;
GO

The nice thing about filtered indexes is that from an IO standpoint they only contain the rows that satisfies the where statement defined in the index so you could get better performance from them.

One thing of note with the filtered indexes is that in order for the query optimizer to use them the value in the where clause must be a constant in the query.

Here is a couple of references to using filtered indexes:
MSDN

Brent Ozar