speeding up a deletion in postgresql I came up with:

drop table if exists idtemp;
create temp table idtemp as
      select documentid from taskflag where taskid='coref' and state = 2
         order by statechanged asc limit howmany;
create unique index on idtemp(documentid);

-- trim taskflag to the first N docs ordered by coref.
delete from taskflag where documentid not in (select documentid from idtemp) ;

This is very slow when there are 120k records in taskflag and I'm keeping 10k.

Taskflag looks like:

\d taskflag
                Table "public.taskflag"
    Column    |            Type             | Modifiers
--------------+-----------------------------+-----------
 documentid   | character varying(64)       | not null
 taskid       | character varying(64)       | not null
 state        | smallint                    |
 statechanged | timestamp without time zone |
Indexes:
    "taskflag_pkey" PRIMARY KEY, btree (documentid, taskid)
    "task_index2" btree (documentid)
    "task_index4" btree (taskid, state, statechanged)

Explain says:

                                QUERY PLAN
----------------------------------------------------------------------------------
 Delete on taskflag  (cost=0.00..105811822.25 rows=223210 width=6)
   ->  Seq Scan on taskflag  (cost=0.00..105811822.25 rows=223210 width=6)
         Filter: (NOT (SubPlan 1))
         SubPlan 1
           ->  Materialize  (cost=0.00..449.00 rows=10000 width=146)
                 ->  Seq Scan on idtemp  (cost=0.00..184.00 rows=10000 width=146)
(6 rows)

Should I just arrange for the temp table to contain the ones I am keeping?

The easiest optimization would probably be to let the planner use a hash anti join, by rewriting the query as:

 delete from taskflag where not exists
  (select 1 from idtemp where documentid=taskflag.documentid);

also you may need to ANALYZE the temp table immediately after populating it.


How to insert numerous data into temporary table? I'm building a program which generates a T-SQL query in following form:

DECLARE @In TABLE (Col CHAR(20))
INSERT INTO @In VALUES value1, value2... value1000
GO
INSERT INTO @In VALUES value1001, value1002...

but the second INSERT statement throws error:

  Msg 1087, Level 15, State 2, Line 1
  Must declare the table variable "@In".

What am I doing wrong?

You can use VALUES (...), (...):

INSERT INTO table(colA, colN, ...) VALUES
    (col1A, col1B, ...)
    , ...
    , (colnA, colnB, ...)

With @In:

DECLARE @In TABLE (Col CHAR(20))
INSERT INTO @In VALUES
    ('value1')
    , ('value2')
    , ...
    , ('value1000')

It will insert X rows all at once. GO is not needed. Variables declared before GO don't exist anymore after GO.


Transaction rollback hanging from past two months(MSSQL 2016) I executed a query using SQLCMD on 2019-01-30, and the query caused ldf file to bloat exponentially and it became 2TB in size, then my MSSQL started rollback of transaction at 2019-02-01. I used KILL SPID WITH STATUSONLY to monitor the rollback process: progress of rollback was about 2% a day for first two weeks, afterwards the rollback process got stuck at 22% from 2019-02-14, and it's still there today.

I'd appreciate if I can get expert opinion on if there is any way to fix this issue - how to stop or speed up rollback?

Please find below code details about this issue:

SQL

BEGIN TRAN
  DECLARE @m int
  SELECT @m = @@ERROR
  DECLARE @tbname_old varchar(50) = 'OTS_ARCHIVE'
  DECLARE @tbname_new varchar(50) = 'OTS_ARCHIVE2'
  DECLARE @column_old varchar(30) = 'GuID_ID'
  DECLARE @column_new varchar(30) = 'GuID_ID_old'
  DECLARE @sql varchar(50) = '[' + @tbname_new + '].[' + @column_old + ']'
  DECLARE @sqlid varchar(100) = 'CAST(CAST(NEWID() AS BINARY(10)) + CAST(GETDATE() AS BINARY(6)) AS UNIQUEIDENTIFIER)'
  DECLARE @date as datetime
  DECLARE @i int
  DECLARE @f int
  set @date = '2017-01-01'
  set @i = 0
  set @f = 27
  WHILE @i < @f
  BEGIN
  EXEC ('INSERT INTO ' + @tbname_new +
    ' select
    GuID_ID
    ,Box_ID
    ,Start_Time
    ,End_Time
    ,Duration_Time
    ,ots_count
    ,Group_ID
    ,' + @sqlid + ' from ' + @tbname_old
    )
  END
IF @m = 0
  COMMIT TRAN
ELSE
  ROLLBACK TRAN
  SELECT
    ERROR_NUMBER() AS ErrorNumber,
    ERROR_SEVERITY() AS ErrorSeverity,
    ERROR_STATE() AS ErrorState,
    ERROR_PROCEDURE() AS ErrorProcedure,
    ERROR_LINE() AS ErrorLine,
    ERROR_MESSAGE() AS ErrorMessage

Activity monitor

After about 60 days, the rollback process was finally completed. It seems the only way and the only solution you should do is wait, wait, and keep waiting.


What impact does using backup preference on Availability Groups have? I am using Availability Groups (database level) SQL Server 2017:

I have set my availability group to "prefer secondary". I have allowed read access on replica.
I have created a maintenance plan with full backups on all databases on replica using "copy only" and then also on primary.

But now I'm wondering does AG actually make any decisions? Both Mainenance plans will run and from what I can tell won't change "copy only" to primary's maintenance plan if there's a failover.

Besides just preventing me from running backups if I change the setting..... besides enforcing rules for what [another] dba may or may not be able to do: what is the point of this?

Or, did I setup my backups totally wrong?

No, the availability group does not do anything to control where backups occur.  The backup preference settings on an availability group are there so that the backup software can reference it to determine where to run the backup.  If the backup software in use doesn't have any code to reference these settings, they will have no impact on that backup software and it will just run the backups on the server it connects to.

Most backup software that supports availability groups will reference the backup preference configuration and take backups accordingly.  Maintenance Plan backups will adhere to the backup preference configured on the availability group.

One option I would recommend for the copy-only backup setup--create your SQL Agent job to run a copy-only backup.  The first step in the job would be to check if the server is the primary, and if so, end the job.  If it is not the primary, then run the copy-only backup.

The first step of the job can use the fn_hadr_is_primary_replica function to determine if the server is the primary of a given database:

sys.fn_hadr_is_primary_replica ( 'dbname' )

See sys.fn_hadr_is_primary_replica (Transact-SQL).


How to get the relationship between backup set? We use command backup database to perform a full backup, serval backup sets are generated during the backup operation.
Also, we use list backup to show the backup information, the output as followed:

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
7       136M       DISK        00:00:20     04-NOV-06
        BP Key: 7   Status: AVAILABLE  Compressed: NO  Tag: TAG20071104T200759
        Piece Name: /d2/RDBMS/backupset/2007_11_04/o1_mf_annnn_TAG20071104T200759_ztjxx3k8_.bkp

  List of Archived Logs in backup set 7
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    1       173832     21-OCT-06 174750     21-OCT-06
  1    2       174750     21-OCT-06 174755     21-OCT-06
  1    3       174755     21-OCT-06 174758     21-OCT-06

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
8       Full    2M         DISK        00:00:01     04-NOV-06
        BP Key: 8   Status: AVAILABLE  Compressed: NO  Tag: TAG20071104T200829
        Piece Name: /disk1/oracle/dbs/c-774627068-20071104-01
  Controlfile Included: Ckp SCN: 631510       Ckp time: 04-NOV-06
  SPFILE Included: Modification time: 21-OCT-06

From above output, I could not figure out whether such backup sets are generated by the same command, backup database. Is there any available command to satisfy my requirement?

From SQL:

SELECT p.start_time, p.handle, p.tag, s.backup_type,
       s.incremental_level, controlfile_included
FROM   v$backup_piece p, v$backup_set s
WHERE  p.set_stamp = s.set_stamp
AND    p.set_count = s.set_count
ORDER BY tag;

For the backup type:

L: Archived Redo Logs
D: Datafile Full Backup (or control file backup or autobackup)
I: Incremental Backup

You can see which files are in each backup by the TAG column.

Keep in mind that this view may report backups are don't actually exist on disk unless you:

RMAN> crosscheck backup;
RMAN> delete expired backup;

Refer to the Oracle Database 12c Documentation (or whatever version of Oracle you are using) for more information:

v$backup_set

v$backup_piece


Postgres INNER JOIN same table failing on CartoDB I have a table with data which looks like this

+----+------+-------+
| cp | year | count |
+----+------+-------+
| 1  | 2000 | 10000 |
| 1  | 2001 | 9000  |
| 1  | 2002 | 9500  |
| 2  | 2000 | 8000  |
| 2  | 2001 | 7500  |
| 2  | 2002 | 7000  |
+----+------+-------+

Every cp is guaranteed to have the same number of years. What I want is the percentage change between the current year and a reference year. So I'm after this:

+----+------+-------+-----------+--------------+
| cp | year | count | since2000 | since2000pct |
+----+------+-------+-----------+--------------+
| 1  | 2000 | 10000 | 0         | 0            |
| 1  | 2001 | 9000  | -1000     | -0.1         |
| 1  | 2002 | 9500  | -500      | -0.05        |
| 2  | 2000 | 20000 | 0         | 0            |
| 2  | 2001 | 16000 | -4000     | -0.2         |
| 2  | 2002 | 21000 | 1000      | 0.05         |
+----+------+-------+-----------+--------------+

It's been awhile since I've done much with SQL, but this looks like a pretty straightforward inner join. All I should need is the year 2000 count repeated for every row, and the rest is just math. I got it working using SQLFiddler:

http://sqlfiddle.com/#!15/81a94/1/0

SELECT
*
FROM traffic_counts as s1
INNER JOIN
(SELECT cp, count AS count2000 FROM traffic_counts WHERE year = 2000) s2
ON s1.cp = s2.cp;

But when I try to run this on CartoDB (PostgreSQL 9.3.4) I'm getting an error saying that cp is ambiguous. (Fiddler has no problem…) I even tried aliasing the table to s3 inside the subselect and fully qualifying the first "cp", but the error was the same.

Can anyone help?

The problem may be that you have multiple columns of the same name in the result (2x cp). When using SELECT * you would return both.

The simple fix for the query at hand would be USING in the join clause, which only keeps one instance of the joined column in the result (and seems smarter in this particular case anyway):

SELECT *
FROM   traffic_counts s1
JOIN  (SELECT cp, count AS count2000 FROM traffic_counts WHERE year = 2000) s2 USING (cp);

Updated SQL Fiddle.

BTW, SQL Fiddle itself was updated only recently to allow such ambiguity. Until some months ago it also raised an error for queries with duplicate names in the result. Quite a few clients cannot cope with that.


How do I insert a value into table2 from a trigger on table1 I have 2 tables with a column name WHO, and I'm using these 2 tables to create a view where FSwho.who = FStasks.who.

If I enter a new record into FStasks where FStasks.who = LIZ, but there's no record on my FSwho.who table, then the trigger should insert LIZ into my table.

With the code below, the error I get is

  The multi-part identifier "fs_tasks.WHO" could not be bound. (I've also tried inserted.who)

I've scoured the interwebs and most of the solutions I've come across are about changing values on the same table. How do I go about inserting values to a completely different table?

ALTER TRIGGER [dbo].[FS_TASKS_TRIG]
   ON  [dbo].[FS_TASKS]
   AFTER INSERT,UPDATE
AS
BEGIN
    SET NOCOUNT ON;

DECLARE @Action as char(1);
    SET @Action = (CASE
        WHEN EXISTS(SELECT * FROM INSERTED) AND EXISTS(SELECT * FROM DELETED) THEN 'U'  -- Set Action to Updated.
        WHEN EXISTS(SELECT * FROM INSERTED) THEN 'I'  -- Set Action to Insert.
        WHEN EXISTS(SELECT * FROM DELETED) THEN 'D'  -- Set Action to Deleted.
        ELSE NULL -- Skip. It may have been a "failed delete".
    END)

    if @Action in ('I','U')
    begin

        IF NOT EXISTS(SELECT * FROM fs_who where who = WHO) BEGIN
            INSERT INTO FS_WHO (WHO)
            VALUES (fs_tasks.WHO)
        END

    end

END

inserted is a table. So try:

INSERT INTO FS_WHO (WHO)
SELECT DISTINCT i.WHO
FROM inserted AS i
WHERE NOT EXISTS (SELECT *
    FROM FS_WHO f
    WHERE f.WHO = i.WHO);

And you don't need to consider @Action for this...


Display single record for attributes values joining 3 tables in SQL Server? I have 3 tables

Table1 (a view) contains the record ID, Person's Name, Attribute ID and a new record for all attributes entered into the database pertaining to the person.

Table2 is a descriptive identifier name for the Attribute Type ID in Table1

Table3 (a view) contains the data entry timestamp for the record ID in Table1, used as selection criteria range

Table1 --SQL Server 2014 View--

ID [char(18)]
Name [nvarchar(50)]
AttributeTypeID [int]
AttributeValue [nvarchar(2000)]

Table2 --SQL Server 2014 Table--

AttrTypeID [int]
AttrName [nvarchar(50)]

Table3 --SQL Server 2014 View--

EntryID [char(18)]
EntryDateTime [datetime2(3)]

My current query brings them together (however I would like a different output):

SELECT DISTINCT
Table1.ID
Table1.Name
Table2.AttrTypeID
Table2.AttrName
Table1.AttributeValue

FROM
Table1 RIGHT JOIN Table2 ON Table1.AttributeTypeID = Table2.AttrTypeID

WHERE
Table1.ID IN (SELECT DISTINCT
             EntryID
             FROM Table3
             WHERE
             EntryDateTime BETWEEN '2018-05-01 00:00:00.000' AND '2018-05-07 00:00:00.000')

ORDER BY
Table1.ID ASC

Returns the following records:

ID     | Name  | AttrTypeID  | AttrName   | AttributeValue
123456 | Bob   | 201         | Hair Color | Blonde
123456 | Bob   | 202         | Eye Color  | Blue
123456 | Bob   | 203         | Height CM  | 180
123456 | Bob   | 204         | Weight KG  | 77.1
123456 | Bob   | 205         | Email      | bob@domain.com
123456 | Bob   | 206         | Profile    | Text...2000characters...
789000 | Sarah | 201         | Hair Color | Brown
789000 | Sarah | 202         | Eye Color  | Brown
789000 | Sarah | 203         | Height CM  | 155
789000 | Sarah | 204         | Weight KG  | 50.5
789000 | Sarah | 206         | Profile    | Text...2000characters...

Note Sarah does not have an entry for email (AttrTypeID = 205) in table 1

My desired output is create a query that returns:

ID     | Name  | Hair Color  | Eye Color  | Height CM  | Weight KG  | Email           | Profile
123456 | Bob   | Blonde      | Blue       | 180        | 77.1       | bob@domain.com  | Text...2000characters...
789000 | Sarah | Brown       | Brown      | 155        | 50.5       | NULL            | Text...2000characters...

Your assistance is very much appreciated!

PS I'm new here so if you need more info or require a different format for asking this question please let me know.

Here is a very simple way to solve this.

--Setup demo data
Declare @Table1 table
    (ID int, Name varchar(5), AttrTypeID int, AttrName varchar(10), AttributeValue varchar(24))
;

INSERT INTO @Table1
    (ID, Name, AttrTypeID, AttrName, AttributeValue)
VALUES
    (123456, 'Bob', 201, 'Hair Color', 'Blonde'),
    (123456, 'Bob', 202, 'Eye Color', 'Blue'),
    (123456, 'Bob', 203, 'Height CM', '180'),
    (123456, 'Bob', 204, 'Weight KG', '77.1'),
    (123456, 'Bob', 205, 'Email', 'bob@domain.com'),
    (123456, 'Bob', 206, 'Profile', 'Text...2000characters...'),
    (789000, 'Sarah', 201, 'Hair Color', 'Brown'),
    (789000, 'Sarah', 202, 'Eye Color', 'Brown'),
    (789000, 'Sarah', 203, 'Height CM', '155'),
    (789000, 'Sarah', 204, 'Weight KG', '50.5'),
    (789000, 'Sarah', 206, 'Profile', 'Text...2000characters...')
;

--The actual query
SELECT ID, max(name) as Name,
  MAX(CASE WHEN AttrName = 'Hair Color' THEN AttributeValue END) AS [Hair Color],
  MAX(CASE WHEN AttrName = 'Eye Color' THEN AttributeValue END) AS [Eye Color],
  MAX(CASE WHEN AttrName = 'Height CM' THEN AttributeValue END) AS [Height CM],
  MAX(CASE WHEN AttrName = 'Weight KG' THEN AttributeValue END) AS [Weight KG],
  MAX(CASE WHEN AttrName = 'Email' THEN AttributeValue END) AS [Email],
  MAX(CASE WHEN AttrName = 'Profile' THEN AttributeValue END) AS [Profile]
FROM @Table1
GROUP BY id, name;

| ID     | Name  | Hair Color | Eye Color | Height CM | Weight KG | Email          | Profile                  |
|--------|-------|------------|-----------|-----------|-----------|----------------|--------------------------|
| 123456 | Bob   | Blonde     | Blue      | 180       | 77.1      | bob@domain.com | Text...2000characters... |
| 789000 | Sarah | Brown      | Brown     | 155       | 50.5      | NULL           | Text...2000characters... |

You just need to wrap your original query inside a common table expression and use that in place of my @Table1.  Something like:

;With Table1 as
(
SELECT DISTINCT Table1.ID Table1.NAME Table2.AttrTypeID Table2.AttrName Table1.AttributeValue
FROM Table1
RIGHT JOIN Table2 ON Table1.AttributeTypeID = Table2.AttrTypeID
WHERE Table1.ID IN (
        SELECT DISTINCT EntryID
        FROM Table3
        WHERE EntryDateTime BETWEEN '2018-05-01 00:00:00.000'
                AND '2018-05-07 00:00:00.000'
        )
)

--The actual query
SELECT ID, max(name) as Name,
  MAX(CASE WHEN AttrName = 'Hair Color' THEN AttributeValue END) AS [Hair Color],
  MAX(CASE WHEN AttrName = 'Eye Color' THEN AttributeValue END) AS [Eye Color],
  MAX(CASE WHEN AttrName = 'Height CM' THEN AttributeValue END) AS [Height CM],
  MAX(CASE WHEN AttrName = 'Weight KG' THEN AttributeValue END) AS [Weight KG],
  MAX(CASE WHEN AttrName = 'Email' THEN AttributeValue END) AS [Email],
  MAX(CASE WHEN AttrName = 'Profile' THEN AttributeValue END) AS [Profile]
FROM Table1
GROUP BY id, name;


What are the main causes of deadlocks and can they be prevented? Recently one of our ASP.NET applications displayed a database deadlock error and I was requested to check and fix the error. I managed to find the cause of the deadlock was a stored procedure that was rigorously updating a table within a cursor.

This is the first time I've seen this error and didn't know how to track and fix it effectively.  I tried all the possible ways I know, and finally found that the table which is being updated doesn't have a primary key! luckily it was an identity column.

I later found the developer who scripted database for deployment messed-up. I added a primary key and the problem was solved.

I felt happy and came back to my project, and did some research to found out the reason for that deadlock...

Apparently, it was a circular wait condition that caused the deadlock. Updates apparently take longer without a primary key than with primary key.

I know it isn't a well defined conclusion, that is why I'm posting here...

Is the missing primary key the problem?
Are there any other conditions which cause deadlock other than (mutual exclusion, hold and wait, no preemption and circular wait)?
How do I prevent and track deadlocks?

tracking deadlocks is the easier of the two:

  By default, deadlocks are not written in the error log. You can cause SQL to write deadlocks to the error log with trace flags 1204 and 3605.

  Write deadlock info to the SQL Server error log:
  DBCC TRACEON(-1, 1204, 3605)

  Turn it off:
  DBCC TRACEOFF(-1, 1204, 3605)

  See "Troubleshooting Deadlocks" for a discussion of trace flag 1204 and the output you will get when it is turned on.
  https://msdn.microsoft.com/en-us/library/ms178104.aspx

Prevention is more difficult, essentially you have to look out for the following:

Code Block 1 locks resource A, then resource B, in that order.

Code Block 2 locks resource B, then resource A, in that order.

This is the classic condition where a deadlock can occur, if the locking of both the resources is not atomic, the Code Block 1 can lock A and be pre-empted, then Code Block 2 locks B before A gets processing time back.  Now you have deadlock.

To prevent this condition, you can do something like the following

Code Block A (psuedo code)

Lock Shared Resource Z
    Lock Resource A
    Lock Resource B
Unlock Shared Resource Z
...

Code Block B (pseudo code)

Lock Shared Resource Z
    Lock Resource B
    Lock Resource A
Unlock Shared Resource Z
...

not forgetting to unlock A and B when done with them

this would prevent the deadlocking between code block A and code block B

From a database perspective, I'm not sure on how to go about preventing this situation, as locks are handled by the database itself, i.e. row/table locks when updating data.  Where I've seen the most issues occur is where you saw yours, inside a cursor.  Cursors are notoriously inefficient, avoid them if at all possible.


When the primary key is also the foreign key, in Postgres In Postgres 12, I am tracking photographs. Each is large, about 6 to 8 megs each. For quick browsing I will store a thumbnail image along with the full image. Both full and thumbnail are BYTEA type columns.

For performance, I want to avoid loading the full image when only the thumbnail is needed. As I understand it, when accessing a row, all the BYTEA field values will be loaded into memory. So displaying a list of thumbnails to the user will necessarily be loading the full photos on the server even when not explicitly requested in the query.

So I will break that photo table into two, storing the thumbnail on the main table, and storing the thumbnail in a separate child table, in a One-To-One.

In such a scenario, the child full-photo table carries the ID of its parent thumbnail row as a foreign key. That foreign key column could logically serve as the primary key as well.

Can I designate that one column of a child to be both a foreign key and also a primary key? If so, are there any caveats to be aware of?

Primary key & Foreign key

  Can I designate that one column of a child to be both a foreign key and also a primary key?

Yes absolutely:

create table photo
(
  id integer primary key,
  ... other columns ...
);

create table thumbnail
(
  id integer primary key references photo,
  ... other columns ...
);

TOAST

bytea columns are stored outside of the normal column data (in so called "toast tables") and are not retrieved unless you include them in the SELECT list.

Quote from the manual

  The big values of TOASTed attributes will only be pulled out (if selected at all) at the time the result set is sent to the client.

This only means that even if a query is forced to do a Seq Scan, the bytea columns are not retrieved until the rows are identified that need to be sent back. So if your query does a seq scan on 1 million rows but only 1 row is returned, only one bytea value is read and sent to the client.

Note that the thumbnails might actually be stored inline (not in the toast table) if they are small enough (TOASTing is only triggered for sizes below approx. 2k)


org.postgresql.util.PSQLException: ERROR: canceling statement due to conflict with recovery I currently have a PostgreSQL slave node that is presenting problems, that don't arise in the master node. Seems to be related with the node sync process.

Complete stacktrace:

org.postgresql.util.PSQLException: ERROR: canceling statement due to conflict with recovery
  Detail: User query might have needed to see row versions that must be removed.
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2103)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1836)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:512)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:388)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:273)
    at org.apache.tomcat.dbcp.dbcp2.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:82)
    at org.apache.tomcat.dbcp.dbcp2.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:82)
    at cl.waypoint.mailer.reportes.BasicReport.getSingleColumn(BasicReport.java:542)
    at cl.waypoint.mailer.reportes.BasicReport.getSingleColumn(BasicReport.java:518)
    at cl.waypoint.mailer.reportes.StatusSemanalClientes.updateIgnicion(StatusSemanalClientes.java:448)
    at cl.waypoint.mailer.reportes.StatusSemanalClientes.access$2(StatusSemanalClientes.java:447)
    at cl.waypoint.mailer.reportes.StatusSemanalClientes$TempAndDoorLocator.call(StatusSemanalClientes.java:414)
    at cl.waypoint.mailer.reportes.StatusSemanalClientes$TempAndDoorLocator.call(StatusSemanalClientes.java:1)
    at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:334)
    at java.util.concurrent.FutureTask.run(FutureTask.java:166)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1110)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:603)
    at java.lang.Thread.run(Thread.java:722)

DB Version:

PostgreSQL 9.4.9 on x86_64-unknown-linux-gnu, compiled by gcc (Debian 4.9.2-10) 4.9.2, 64-bit

OS Version:

Distributor ID: Debian
Description:    Debian GNU/Linux 8.6 (jessie)
Release:    8.6
Codename:   jessie

The question is: how can I debug the actual cause of the problem and/or solve/avoid it? Don't hesitate to ask for more info if needed to pinpoint the problem, right now just don't know what else to provide.

PS: Error does NOT appear if the offending query is repeated, so it's not strictly related to the query itself, but perhaps with a table update/sync transient.

What you see is a common experience for anyone running standbys and bigger queries on the master.  There are a couple of possible causes:

  There are also additional types of conflict that can occur with Hot
  Standby. These conflicts are hard conflicts in the sense that queries
  might need to be canceled and, in some cases, sessions disconnected to
  resolve them. The user is provided with several ways to handle these
  conflicts. Conflict cases include:

  Access Exclusive locks taken on the primary server, including both
  explicit LOCK commands and various DDL actions, conflict with table
  accesses in standby queries.

  Dropping a tablespace on the primary conflicts with standby queries using that tablespace for temporary work files.
  Dropping a database on the primary conflicts with sessions connected to that database on the standby.
  Application of a vacuum cleanup record from WAL conflicts with standby transactions whose snapshots can still "see" any of the rows
  to be removed.
  Application of a vacuum cleanup record from WAL conflicts with queries accessing the target page on the standby, whether or not the
  data to be removed is visible.

When a query will be killed for one of the above reasons is hard to predict, but usually there are some that appear more often.  It is also configurable, for options read the documentation page further.

The most important option is hot_standby_feedback, which helps against the most common cause (VACUUM removing recently-dead rows).  It has its own costs (might cause table bloat), but the outcome is usually still better than running the same queries on the master.


MySQL : Are hash indices useless? In MySQL, hash indices are only supported by the MEMORY storage engine. By using this engine, data is stored in RAM, not on the hard disk, so it will be lost when MySQL is restarted.

Also, according to this link : hash index, it showed that hash indices are ineffective when data is stored on hard disk. But for practical usage for storing in RAM, Redis (other in-memory structures, .... ), may be a better choice than the MEMORY engine with MySQL.

So which use cases are valid for MEMORY storage engine and hash indices?

A BTree index is arguably better than a Hash index.  A BTree can efficiently handle "ranges", whereas Hash is useless for such.  For "point queries" they are about equivalent.

As for MEMORY, the allure is the speed of access.  However, the caching of disk blocks makes any other Engine nearly as good in many production situations.

In a survey of over 1000 tables discussed on forums.mysql.com, only 6 were MEMORY.  I suggest that says that MEMORY is only very rarely the 'engine of choice'.

The lack of persistence makes MEMORY a non-starter.

As a "cache", Redis, memcached, Memory, etc, have appeal.  But InnoDB, for example, has a builtin cache, and it is usually inefficient to put a cache in front of a cache.

Perhaps you are thinking of some niche usage where MEMORY is well suited?


Why does a JSON element return text when it's not text? When I try to access a JSON element using ->>'elementName' I get a texttype.

SELECT pg_typeof(x1->>'a'), jsonb_typeof(x2)
FROM ( VALUES
  ('{"a":5}'::jsonb, '5'::jsonb)
) AS t(x1,x2);

 pg_typeof | jsonb_typeof
-----------+--------------
 text      | number
(1 row)

However, jsonb says it maps numbers to numeric types...

  When converting textual JSON input into jsonb, the primitive types described by RFC 7159 are effectively mapped onto native PostgreSQL types, as shown in Table 8-23.

Here is the table reproduced from the docs,

Table 8-23. JSON primitive types and corresponding PostgreSQL types

JSON primitive type     PostgreSQL type  Notes
string                  text             \u0000 is disallowed, as are non-ASCII Unicode escapes if database encoding is not UTF8
number                  numeric          NaN and infinity values are disallowed
boolean                 boolean          Only lowercase true and false spellings are accepted
null                    (none)           SQL NULL is a different concept

It isn't currently possible to access the internal JSON types. The quoted documents above mention only how they're stored. There is a pseudo-type in PostgreSQL anyelement,  but you can not return that type. Functions are polymorphic in that they accept different types, but they must return a specified type.

The operator could be overloaded for different types, but it isn't currently that way. Currently ->> is defined as

Operator    Right Operand Type  Description
->>         text                Get JSON object field as text

This means no matter how the type is stored, it'll have to go through text to get access to it. All of the jsonb operators return jsonb or text.

Consider the ambiguity even if the type was overloaded, how would this be processed.

SELECT pg_typeof(x1->>'a'), jsonb_typeof(x2)
FROM ( VALUES
  ('{"a":5}'::jsonb, '5'),
  ('{"a":true}'::jsonb, 'true'::jsonb)
) AS t(x1,x2);

If that makes sense.. then what does this do..

SELECT sum(x1->>'a')
FROM ( VALUES
  ('{"a":5}'::jsonb, '5'),
  ('{"a":true}'::jsonb, 'true'::jsonb)
) AS t(x1,x2);

While overloading ->> may make the system more efficient, it would also make it far more complex.


Nested cursors, multiple result sets I'm using a SQL Server stored procedure to generate an Excel report. Basically each "entry" in the report has three parts: Object, Observation, and Points. The observations have a reference to the primary key of an object, and the points have a reference to an observation. That is the relationship between the parts. (Sorry if my terminology is off, I am used to writing in C# or Java but still learning SQL.)

I want the output to be like this:

-Object

  -All Observations linked to that Object

    - Under each observation there should be all the points linked to that observation

And repeat for each object. I have this working using a cursor for generating all observations with each object, but I can't get the points to come up under each observation.

I have read all over the Internet that cursors are Bad, and I assume this means nested cursors are Very Bad. But this is what I have so far. If you know of a better way to do this, please tell me. I am open to totally refactoring this if necessary.

  DECLARE @Object_Cursor cursor
  DECLARE @Observation_Cursor cursor
  DECLARE @i int
  DECLARE @j int
  DECLARE @row int = 1
  SET @Object_Cursor = CURSOR FOR (SELECT PK_ObjectId FROM Objects)
  OPEN @Object_Cursor
  FETCH NEXT FROM @Object_Cursor INTO @i
    While @@FETCH_STATUS = 0
    Begin
    Select
        PK_ObjectID,
        Name,
        Timestamp
    From Objects where PK_ObjectID = @i

  SET @Observation_Cursor = CURSOR FOR (SELECT @PK_ObservationID FROM Observations)
  OPEN @Observation_Cursor
  FETCH NEXT FROM @Observation_Cursor INTO @j
    While @@FETCH_STATUS = 0
    Begin
    Select
        Timestamp,
        Note,
        User
        from Observations
        where FK_ObjectId = @i
        order by FK_ObjectID

    Select Lat, Lng
    From Pts
    Where FK_ObservationID = @j
    Fetch Next From @Observation_Cursor into @j
    END

    FETCH NEXT FROM @Object_Cursor into @i
    END

    Close @Observation_Cursor
    DEALLOCATE @Observation_Cursor
    CLOSE @Object_Cursor
    DEALLOCATE @Object_Cursor

I feel like the order I'm executing these loops is off somehow, but I haven't been able to get it. Current output is:

 -Object
   -Observations for that object
    -empty points result set

These results are repeated several times for the same object before it goes to the next one, and the points are always empty.

Any help or pointers in another direction is much appreciated.

select obj.PK_ObjectID, obj.name, obs.timestamp, obs.note -- etc
from   Objects as obj
join   Pts     as obs
on     obj.PK_ObjectID = FK_ObservationID
order by obj.PK_ObjectID, obs.FK_ObservationID

You're googling the wrong places, if I may say so.  You yourself say you're new to SQL, and yet you've figured out how to implement a cursor instead of writing an elementary JOIN.  You wrote 33 lines where 5 will do, and ensured your query will run 1% as fast as it normally would.

I hearby award you one gold star for asking!  Now get cracking with a good SQL book such as An Introduction to Database Systems (8th Edition), 2003, C.J. Date.


How can I know what hashing algorithm SQL Server used to decrypt the encrypted data when using the function DECRYPTBYPASSPHRASE? My question is related to the following experiment with two instances:

SQL Server 2017 Express instance (Microsoft SQL Server 2017 (RTM-CU16))
SQL Server 2014 Express instance (Microsoft SQL Server 2014 (SP2-CU18))

I used the function ENCRYPTBYPASSPHRASE to encrypt a text and used the result as the @ciphertext for DECRYPTBYPASSPHRASE. The result of my tests were these:

According to this Microsoft fix,

  [...] SQL Server 2017 uses the SHA2 hashing algorithm to hash the
  passphrase. SQL Server 2016 and earlier versions of SQL Server use the
  SHA1 algorithm that's no longer considered secure.

But how does it know what was the algorithm used to encrypt data if there's no argument related to that on the function DECRYPTBYPASSPHRASE? Is it part of the encrypted data?

By the results of my tests I'd guess SQL Server always uses the newer version of algorithm available on the instance to encrypt data, but tries all the algorithms to decrypt data until it finds one that fits or returns NULL when no corresponding algorithm is found. It's just a guess though since I couldn't find any way to check what hashing algorithm SQL Server used to decrypt the encrypted data.

But how does it know what was the algorithm used to encrypt data if there's no argument related to that on the function DECRYPTBYPASSPHRASE? Is it part of the encrypted data?

Yes, right on point.

I'm going to use the following for the output:

DECLARE @Data VARBINARY(MAX)
DECLARE @Text NVARCHAR(MAX) = N'I''ll get you, and your little dog too!'
DECLARE @Phrase NVARCHAR(100) = N'Fly My Pretties!'

SELECT @Data = ENCRYPTBYPASSPHRASE(@Phrase, @Text)

SELECT @Data AS [Encrypted_Data]

SELECT CAST(DECRYPTBYPASSPHRASE(@Phrase, @Data) AS NVARCHAR(MAX))

If I run this on my 2014 instance, I'll get the following for Encrypted_Data:
0x01000000E565142762F62...

If I run this on my 2017 instance, I'll get the following for Encrypted_Data:
0x020000004D261C666204F...

What should pop out is the preamble, where you can see the 2014 instance starts with 0x01 and the 2017 instance starts with 0x02. This is the versioning of the encryption type that is used. Note that there is more than just this but there is no need to get into that detail for the purposes of this answer, nor does it need to be public knowledge.

SQL Server 2017 understands 0x01 and 0x02 because it's new and knows the new things. SQL Server 2014 understands only 0x01 because it's older and does not know any of the new things as the new things were not backported.

  [...] SQL Server 2017 uses the SHA2 hashing algorithm to hash the passphrase. SQL Server 2016 and earlier versions of SQL Server use the SHA1 algorithm that's no longer considered secure.

This is not the same thing, but generally has to do with Symmetric keys being created with the same initialization vector across both versions. I blogged about this when 2017 came out and it was fixed a little bit later with the trace flag that must be used whereas in your question there is no trace flag needed for 2017 to read 2014 data as shown.


Sizing a MongoDB shard? I am in the process of planning a MongoDB deployment, this will be my first to production and I have a question regarding the disk size of each shard in a cluster.

This cluster itself will be for storing of time series building sensor data with the potential to accumulate 2TB of data every year quite soon after deployment.

So, I plan to start with a 2 shard cluster (2 x 3 node replica sets) since I then have the query routers and config servers in place from the get go. However I could do with some advice regarding how I go about choosing how much data I should store per shard.

Each 'shard node' will have a minimum of 48GB RAM and obviously the right disk configuration to meet the IOPS requirement.

If I am able to satisfy the IOPS requirement of the applications using this DB, what is to stop me sizing for 2TB per shard? Is there a limit on the volume of data a single shard should hold or guidelines to help my decision making process?

I am reading a lot about performance issues if the data volume exceeds the available RAM per host. But if the disks provide enough IOPS's surely this isn't a problem? I appreciate disks will still be far slower than memory but if MongoDB performs poorly when you exceed the RAM size then how to people deal with large databases? The cost to keep adding small shards to a cluster in order to stay within RAM is huge!

In short, to keep cluster expansion to a minimum, if I can satisfy my IOPS requirement, am I able to safely store any amount of data on a single shard or is there a much lower recommendation and if so, for what reason?

Also, I know i must try and keep my index size below my RAM size to ensure efficient query execution. Here is an example:

  If my data volume is 1TB per shard and I have 48GB RAM on each of the
  shard nodes, is there a way to estimate index size? The working set is
  hard to estimate since this is a data logging system which will update
  anything upto the total point count of data entering the database
  every minute, which might be 30,000 documents, inserted once, then
  updated with minute data for the whole day, then a new 30,000
  documents the following day, then updated etc....

It's not an easy task to plan the dimensions of shards, and there is no fool or errorproof way of doing it.

What I tend to do is to create a certain fraction of the expected data per shard as dummy data. If I have a shard handy, I fill it completely.

In the next step, I generate the same fraction of the expected load on a matching number of front-end servers.

Now, I analyse the memory consumption of the indices and overall. Knowing the index size, you roughly know the minimum RAM you need. In order to have enough warning time and a decent amount of RAM for connections, the  working set and other processes, I round up to the next sensible value for RAM sizes. This way you make sure your RAM does not get filled up easily, you have a decent warning time when you have too scale out because of insufficient RAM and your application won't be throttled by your database.

Let me illustrate that. Given my average object size is slightly under 1kB and I plan to have a 1TB partition, but I only have a 256GB partition for tests, I'd create somewhere in the order of 250 million dummy documents. Now, let's say we plan to have 8 front-end servers for the application, I'd set up two of them.

Next, I'd use a load testing tool (there are many, and they are out of scope of this answer) to generate load on those two front-end servers.

After you have done that, you should have a quite accurate indication of overall memory consumption, index size and working set size.

Let's say we find out that our indices are 20GB in size and 300 connections are made. So, let's just right say 21GB. So our shard should have four times this RAM, rounded up to the next sensible value (keep in mind cost efficiency!) So, let's assume that would be 96GB.

Now, you should find out wether it makes more sense to have two shards instead of one, each with 48GB of RAM and 500GB of disk space for MongoDB, respectively. This may be the case, because bigger SSDs can be disproportionately expensive.

I think you get the picture: There is no easy formula, you simply have to do your homework ;) My shortish might or might not work for you. If it doesn't, you have to work one out yourself.

Oh, and whatever you do when planning a shard: plan with SSDs. It's worth it.


PgPool fails to auth in master_slave mode We have two PostgreSQL servers running in master-slave streaming replication mode, behind pgpool.

When we activate master_slave mode in pgpool.conf, we get following error:

do_md5 failed in slot 1

When we add a host with the md5 method in pool_hba.conf, we're getting this error:

md5 token is invalid

Without master_slave mode, pgpool connects successfully. Where to dig?

Solution, in 2 words: install latest pgpool2 from sources

Step by step solution:

Make sure that you have pgpool2 with version 3+
Create pool_passwd with pg_md5 command
Make sure, that you have md5 in pool_hba.conf. If you have pgpool2 from deb - it will not support md5 token in pool_hba.conf


SELECT gives different results with/without FOR UPDATE in same transaction I'm sorry but I havent't found a better wording for the title.

Given the table a:

CREATE TABLE a (
    id INT NOT NULL auto_increment PRIMARY KEY,
    ref VARCHAR(10) NOT NULL,
    val INT NOT NULL
);

INSERT INTO a(ref, val) VALUES ('ten', 10);

And two transactions Tx1 and Tx2 with default isolation level, whose queries are issued in the following order, is the result shown in step [*] of transaction Tx2expected?

Tx1: START TRANSACTION;

            Tx2: START TRANSACTION;

            [^] Tx2: SELECT * FROM a WHERE id='1';
            (without FOR UPDATE)
            ===>
                "id"    "ref"   "val"
                "1"     "ten"   "10"

Tx1: SELECT * FROM a WHERE id='1' FOR UPDATE;
===>
    "id"    "ref"   "val"
    "1"     "ten"   "10"

            Tx2: SELECT * FROM a WHERE id='1' FOR UPDATE;
            (waits)

Tx1: UPDATE a SET v=20 WHERE id;

Tx1: COMMIT;

            Tx2: (locked on 'SELECT * FROM a WHERE id='1' FOR UPDATE')
            (unlocks)
            ===>
                "id"    "ref"   "val"
                "1"     "ten"   "20"

            [*] Tx2: SELECT * FROM a WHERE id='1';
            (without FOR UPDATE)
            ===>
                "id"    "ref"   "val"
                "1"     "ten"   "10"

[*]: Shouldn't this give the updated row value 20 for column val?

UPDATE: If I remove the query [^], then [*] yields the value 20.

This is on 5.6.39; queries were issued using third party client - HeidiSQL - though I guess that shouldn't matter.

A guess:

"Repeatable Read" means that the identical SELECT will produce identical results throughout the transaction.  But, apparently, the FOR UPDATE clause is enough to make the two selects not 'identical'.

Under the covers...  There is a clock (sequence number) ticking away.  Whenever a row is modified, it is copied over.  Now there are two copies of that row, one with the old clock, one with the new.  (See "History list".)  Your first SELECT is associated with the older clock value; the second SELECT, not being identical to the first, has a newer clock value, hence linked to a different copy of the row.

After the transaction if finished (COMMIT or ROLLBACK), the extra copy of the row is tossed.  (I think this is a background task for COMMIT, but a "do it now" task for ROLLBACK.)  Meanwhile, other connections pick the appropriate row from the history list for each row based on their isolation_mode.  Roughly speaking, READ_COMMITTED goes to the old end of the list; READ_UNCOMMITTED ("dirty read") goes to the new end.

Things are optimized on the "optimistic" assumption that COMMITs are more likely than ROLLBACKs.  Hence, the comments in the previous paragraph, and the slowness of ROLLBACK.


MySQL server has gone away obstructing import of large dumps I am trying to import a large sql dump (2GB) to my local mysql on my mac. I have been able to do this in the past (I was using MAMP), but now I get a
    ERROR 2006 (HY000) at line 7758: MySQL server has gone away
everytime I try to import the dump. The database contains innodb tables.

I tried copying the my-innodb-heavy-4G.cnf file to my my.cnf to see if those settings would help, but no luck.

Any ideas on what to tweak?

I am using the "Mac OS X ver. 10.6 (x86, 64-bit), DMG Archive" from here: http://dev.mysql.com/downloads/mysql/

One of the silent killers of MySQL Connections is the MySQL Packet. Even the I/O Thread of MySQL Replication can be victimized by this.

According to the MySQL Documentation

You can also get these errors if you send a query to the server that is incorrect or too large. If mysqld receives a packet that is too large or out of order, it assumes that something has gone wrong with the client and closes the connection. If you need big queries (for example, if you are working with big BLOB columns), you can increase the query limit by setting the server's max_allowed_packet variable, which has a default value of 1MB. You may also need to increase the maximum packet size on the client end. More information on setting the packet size is given in Section C.5.2.10, “Packet too large”.
An INSERT or REPLACE statement that inserts a great many rows can also cause these sorts of errors. Either one of these statements sends a single request to the server irrespective of the number of rows to be inserted; thus, you can often avoid the error by reducing the number of rows sent per INSERT or REPLACE.

At the very least, you must make sure the packet sizes for both the machine you mysqldump'd from and the machine you are loading are identical.

There may be two(2) approaches you can take:

APPROACH #1 : Perform the mysqldump using --skip-extended-insert

This will make sure the MySQL Packet is not inundated with multiple BLOBs, TEXT fields. That way SQL INSERTs are performed one at a time. The major drawbacks are

the mysqldump is much larger
reloading such a dump takes much longer.

APPROACH #2 : Increase max_allowed_packet

This may be the preferred approach because implementing this is just a mysql restart away. Understanding what the MySQL Packet is may clarify this.

According to the page 99 of "Understanding MySQL Internals" (ISBN 0-596-00957-7), here are paragraphs 1-3 explaining it:

  MySQL network communication code was
  written under the assumption that
  queries are always reasonably short,
  and therefore can be sent to and
  processed by the server in one chunk,
  which is called a packet in MySQL
  terminology. The server allocates the
  memory for a temporary buffer to store
  the packet, and it requests enough to
  fit it entirely. This architecture
  requires a precaution to avoid having
  the server run out of memory---a cap
  on the size of the packet, which this
  option accomplishes.

  The code of interest in relation to
  this option is found in
  sql/net_serv.cc. Take a look at my_net_read(), then follow the call to my_real_read() and pay
  particular attention to
  net_realloc().

  This variable also limits the length
  of a result of many string functons.
  See sql/field.cc and
  sql/intem_strfunc.cc for details.

Given this explanation, making bulk INSERTs will load/unload a MySQL Packet rather quickly. This is especially true when max_allowed_packet is too small for the given load of data coming at it.

CONCLUSION

In most installs of MySQL, I usually set this to 256M or 512M. You should experiement with larger values when data loads produces "MySQL has gone away" errors.


MariaDB Grants for set session variables On MariaDB, what grants are needed for set session variables?

For set global are the SUPER grants. But can any client without SUPER grants set his own session variables?


Statistics update on Azure SQL DB New to Azure SQL DB.  I was wondering what is the best practices for scheduling jobs like update statistics in Azure SQL DB.
Googling got a couple of blogs that uses Azure automation.

https://blogs.msdn.microsoft.com/azuresqldbsupport/2018/01/15/automating-azure-sql-db-index-and-statistic-maintenance-using-azure-automation/

Is that the best way to do this?  Just looking for some suggestions on the pros and cons of this approach.  I will be testing this soon.

We have been using the approach in your posted link since first reading about it in Sept 2017.  Here is a link with comments from MS SQL Architect Conor Cunningham about the need to re-index in Azure SQL Database  Link to Conor Cunningham comments.


Design a relational database (Access) that identifies breaches in records based on user defined limits I am trying to create a structured database in Microsoft Access that monitors trading risk. Let's say an organisation wants to analyse all of the trades that it has in its books at any given point in time. In this case, a trade would be an exchange of goods for some form of collateral. Collateral can be stocks, equities, bonds cash etc... basically something that has value that can be traded.
Each trade will have its own unique set of limits. For example, the securities (collateral) that make up each trade can only be equities from approved indices (NDX Index, HSI Index , and so on...). Another example may be that a maximum of 10% of the trade amount can be made up by each security.
When the trade data is imported into the database, I want to flag or extract all of the trades where the unique limits for each trade have been breached. I don't necessarily want to delete the trade records, but I want to possibly group them into a separate table for easy viewing.
Question on Database Structure
I have attached a picture of the (simplified) proposed database schema with the tables in question. Let's say I want to set a limit on the acceptable indices that the particular trade can accept. For this limit on index example, is the structure of the database schema correct for enabling the creation of a query that checks for a given trade:  The limit of acceptable indices to be made of of 80% Primary and 20% Secondary and of those indices, I want indices that are restricted to Spain and Japan?

If the securities associated with a given trade can change on a daily basis, I want to ensure that all of these new securities in this particular trade follow each of the limits imposed on the trade. In this case, a limit on acceptable indices, however different trades may have a different set of limits not only for indices, but for concentration etc.. I don't want to have to write unique queries for each trade (for example, I may want securities of a different trade to be 100% in primary indices and that the are all indices from USA).
If the limits for indices, concentration, approved countries etc. for a given trade are breached for the securities that are contained in that trade, then I want these particular trades flagged to the user.
Note: I have been exploring the option of putting all of the limits as attributes for each trade, but I don't know how I can create a generalized query structure that allows me to identify limit breaches without creating massive queries.
Any suggestions would be greatly appreciated.

Initial observations are that Index Type and Index Composition are attributes of Index, not of Acceptable_indices. There will be relationships between a security and an index; whether this is important to your use case or not can't be determined from the current information. Concentration should relate to another entity type saying what it is a concentration of.

More generally you need to distinguish what is an acceptable set of values from what was actually in the transaction executed. The acceptable set is your risk and exposure policies documented in the form of a normalised data model. You have to think carefully of all the conditions, including the obscure, infrequent and exceptional ones. For each executed transaction the model must capture sufficient information to determine if it meets the risk policies or not.

There must be a link between an individual transaction and the set of rules that apply to it. The easiest way is to set the rules by security type e.g. one set for bonds, one for equities, one for futures, one for options etc.

Another complication is the time-dependent nature of the data. I'm guessing this application will be for analysis after the transactions are complete, rather than pre-transaction authorisation. Likely there will be a number of days between transaction and analysis. So you need to know what rules were in place when the transaction occurred. Everything must have start and end dates (or datetime values, depending if things can change intra-day).

For a physical database design you could implement one table per entity type. This is the usual way in the absence of a strong incentive to do otherwise. Queries may be longer from this approach (if there are many limiting factors the query will have to bring in the table with limit's allowable values and the table including the actual executed value for each factor). Queries are very obvious, however, and easy to implement and maintain. The runtime engine should have not problem executing them.

Alternatively each limit could be considered as a feature of the deal. The tables reduce to bags of feature values. To check if an executed trade breaks a limit one compare the "allowed" bag of values to the "executed" bag of values. This would be a variation on the Entity Attribute Value (EAV) design pattern. Often this pattern is considered dangerous. Queries are obscure as everything is a "bag" or a "feature"; it is never obvious what each particular feature represents. Writing queries to ensure the limit's feature is compared to the corresponding feature on the trade can be painstaking work. Maintenance even more so. Performance tends to be worse because this is adding an extra layer of abstraction on top of how the engine expects to work. It is wonderfully flexible, however. If you get things just right adding another feature can reduce to a simple cut-and-paste.

A third option would be to hold each value as an attribute. This make the tables very wide. The DBMS will be able to cope with this (up to a limit!) but makes things difficult to read. The real problem comes when there can be multiple acceptable values. For your example there may be two acceptable indexes in the limits and two indexes involved in the trade. You created columns index1 and index2 on each of Limit and Trade table. To check validity one must compare all possible combinations i.e.

(
    limit.index1 = trade.index1
and limit.index2 = trade.index2
)
or
(
    limit.index2 = trade.index1
and limit.index1 = trade.index2
)

This becomes ugly real quick.

My suggestion is to implement it one table per entity. Write the queries and test them. They can be written incrementally, checking first for, say, country validity, then country and index, then country, index and counterparty and so on. If this approach surfaces a particular problem, pause and re-evaluate in the light of the new circumstances.


InnoDB deadlock with INSERT .. SELECT I am trying to understand why a deadlock occurs when two workers in parallel are running INSERT .. SELECT queries on a table which has both 1) auto incremented primary key, 2) a unique index on multiple columns first of which is a date column. Workers normally work with rows from different dates. The unique index column value ranges should not overlap (they were overlapping before, but I changed the index to have date column as the first one).

SHOW ENGINE INNODB STATUS\G shows that 1st transaction (active for 20secs) is waiting for unique_rows lock (mode S), while the 2nd transaction (active for 11secs) holds unique_rows lock (mode X) and is waiting for auto-inc table lock.

How far I understand this: when 1st transaction starts, it acquires auto-inc table lock (I use the default auto-inc lock mode = 1), then it acquires some gap locks on the unique_rows index. The 2nd transaction could be acquiring gap locks on the unique_rows index, but they should not conflict with the locks acquired by 1st transaction.

Having written the question down, I suspect my assumptions about gap locks are wrong.

Another thing is I always have a DELETE FROM WHERE query in the same transaction just before INSERT .. SELECT, but that DELETE query has never been mentioned in deadlock logs.

The whole process works like this: 1) delete a date range from the destination table, 2) insert a date range in the destination table by selecting data from some other table.

the deadlock message sometimes will not show the full picture. It could be some other query is holding the lock which is not any of those two showed in the log. As you said, you have delete query in the beginning, that properly would be the case.

Here is a way I normally use to troubleshoot the deadlocks when you can not get all the info from the log.

mysql> set global log_output = 'table';
mysql> set global general_log=on;

Then you reproduce the problem, when the deadlock happens, turn off the general log. Find those two queries in the mysql.general_log, then use thread_id to find the start and end of the transaction. You will have all the queries in the transaction, then you can go through the logic, to find out which is the query or logic caused the problem.


DISTINCT on one column and return TOP rows How do you query for three unique customers with the largest Purchase_Cost?

I want to apply the DISTINCT only on Customer_Name, but the query below applies the distinct on all three columns. How should I modify the query to obtain the desired output?

SELECT DISTINCT TOP 3 customer_name, order_no, Purchase_Cost
FROM PurchaseTable
ORDER BY Purchase_Cost

Replace your dbname and schemaName in the following query.

;WITH CTE AS
(
SELECT
       [Order_No]
      ,[Customer_Name]
      ,[Purchase_Cost]
     , ROW_NUMBER() OVER(PARTITION BY [customer Name] ORDER BY [Purchase Cost] DESC) AS "RowNumber"
  FROM [dbname].[schemaName].[PurchaseTable]
  )

  SELECT TOP(3)
       [Order_No]
      ,[Customer_Name]
      ,[Purchase_Cost]
  FROM CTE WHERE RowNumber=1
  ORDER BY [Purchase_Cost] DESC

I am sure there are other ways of doing the same. I suggest you read this.


Optimizer choosing a bad index I have a pretty simple select statement,

select column 4 from table_x where column1=:1 and column2=:2 and column3=:3

Columns 1,2 and 3 make up the primary key on the table.

For some reason, rather then using the primary key index, oracle is using a nonclustered index that only contains 1 of the columns.

The only thing that I can find for why the optimizer is ignoring the primary key index, is that the bind variables used in the query are not defined the same way as the columns.  The bind variables are all defined as varchar(32), the columns are nchar, number, and nchar.  Is this mismatch enough to prevent the optimizer from using the primary key index?

Stats have been updated on the table and all the indexes.

It isn't like there is a rule for the optimizer choosing the index with the very same columns that the query predicates have.

You should always use your bind variables with the proper type. No, in this specific case, those types do not prevent the optimizer from using your index.

Below is just an artificially constructed example. I do not know your table/index structure and data distribution.

drop table t1 purge;
create table t1(c1 nchar(32), c2 number, c3 nchar(32), c4 number);
insert into t1 with g as (select * from dual connect by level <= 1000) select N'HELLO', mod(rownum, 1000), N'WORLD' || rownum, rownum from g,g;
alter table t1 add constraint t1_pk primary key (c1, c2, c3) using index;
create index t1_i1 on t1(c1);
create index t1_i2 on t1(c2);
create index t1_i3 on t1(c3);
exec dbms_stats.gather_table_stats(user, 'T1');

With the above sample data, we have 1 million rows, with C1 having the same value everywhere, C2 having 1000 uniformly distributed different values and C3 being unique (with no constraint on it).

alter session set statistics_level=all;
variable b1 varchar2(32)
variable b2 varchar2(32)
variable b3 varchar2(32)
exec :b1 := 'HELLO';
exec :b2 := '500';
exec :b3 := 'WORLD500';

select c4 from t1 where c1 = :b1 and c2 = :b2 and c3 = :b3;
select * from table(dbms_xplan.display_cursor(format=>'allstats last cost +peeked_binds'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------
SQL_ID  3r9y8nvz3vdtq, child number 0
-------------------------------------
select c4 from t1 where c1 = :b1 and c2 = :b2 and c3 = :b3

Plan hash value: 3324024074

--------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name  | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |       |      1 |        |     4 (100)|      0 |00:00:00.01 |       3 |
|*  1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T1    |      1 |      1 |     4   (0)|      0 |00:00:00.01 |       3 |
|*  2 |   INDEX RANGE SCAN                  | T1_I3 |      1 |      1 |     3   (0)|      0 |00:00:00.01 |       3 |
--------------------------------------------------------------------------------------------------------------------

Peeked Binds (identified by position):
--------------------------------------

   1 - :1 (VARCHAR2(30), CSID=873): 'HELLO'
   2 - :2 (VARCHAR2(30), CSID=873): '500'
   3 - (VARCHAR2(30), CSID=873): 'WORLD500'

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(("C2"=TO_NUMBER(:B2) AND "C1"=SYS_OP_C2C(:B1)))
   2 - access("C3"=SYS_OP_C2C(:B3))

Notice in Peeked Binds section the type of the variables (VARCHAR2), and in the Predicate Information section how they were automatically converted with the SYS_OP_C2C and TO_NUMBER functions. This implicit conversion in this case does not prevent index usage, but you will not be so lucky with other implicit conversions. (Also notice how I got 0 row as result, while I should have got 1 row as result with those values - but that is a different topic.)

The optimizer chose the index on column C3 instead of the PK index.
The cost of the query is 4. So the cost of the PK index should be higher. Let's see with a hint:

explain plan set statement_id = 'PK_HINT' for select /*+ INDEX(t1 t1_pk) */ c4 from t1 where c1 = :b1 and c2 = :b2 and c3 = :b3;
select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------
Plan hash value: 2347959165

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |   139 |     4   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |     1 |   139 |     4   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | T1_PK |     1 |       |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("C1"=SYS_OP_C2C(:B1) AND "C2"=TO_NUMBER(:B2) AND
              "C3"=SYS_OP_C2C(:B3))

The usage of PK index is possible, but still, the optimizer did not choose it. Now most people would say their cost is the same (4), but they would be wrong. Such execution plans do not show all the details. We can check those from our plan table (or V$SQL_PLAN). I have already placed the plan of the PK index version there with the above EXPLAIN PLAN statement, now do it for the original as well:

explain plan set statement_id = 'NO_HINT' for select c4 from t1 where c1 = :b1 and c2 = :b2 and c3 = :b3;

We have both plans in the plan table. The important factor not being displayed is the CPU cost:

break on statement_id
select STATEMENT_ID, OPERATION, OPTIONS, OBJECT_NAME, COST, IO_COST, CPU_COST from plan_table order by STATEMENT_ID, ID;

STATEMENT_ID OPERATION            OPTIONS                OBJECT_NAME COST IO_COST CPU_COST
------------ -------------------- ---------------------- ----------- ---- ------- --------
NO_HINT      SELECT STATEMENT                                           4       4    29046
             TABLE ACCESS         BY INDEX ROWID BATCHED T1             4       4    29046
             INDEX                RANGE SCAN             T1_I3          3       3    21564
PK_HINT      SELECT STATEMENT                                           4       4    29746
             TABLE ACCESS         BY INDEX ROWID         T1             4       4    29746
             INDEX                UNIQUE SCAN            T1_PK          3       3    22414

Notice how the COST equals to IO_COST.  However, the CPU COST of using the PK index is higher than of the single column index. Simply put, even if the IO cost is the same, processing more columns requires more CPU time.

Also it is a logical decision, because in this example C3 alone is already unique, so using a smaller index with fewer columns, even if not significantly, should be faster.

When in doubt, with so simple statements and low query cost, you should inspect the details as well. If you have a similarly very selective column, this could be the reason for the optimizer choosing the index on it.


SQL db query: how to add a new field and assign values I have 3 tables as shown in the screenshot below. I have created a query using UNION as shown below.

SELECT T1_ID AS ID, T1_NAME AS NAME, T1_ADD AS ADDRESS
FROM Table1
UNION
SELECT T2_ID AS ID, T2_NAME AS NAME, T2_ADD AS ADDRESS
FROM Table2
UNION
SELECT T3_ID AS ID, T3_NAME AS NAME, T3_ADD AS ADDRESS
FROM Table3

And I got the result below.

My question is, how do I add a column (field) called TYPE and assign values to the query results but not updating the existing three tables to make it like the screenshot below?

Not the most elegant of solutions, but to answer your question, you could do this:

SELECT T1_ID AS ID, T1_NAME AS NAME, T1_ADD AS ADDRESS, 'ANIMAL' AS TYPE
FROM Table1
UNION
SELECT T2_ID AS ID, T2_NAME AS NAME, T2_ADD AS ADDRESS, 'HUMAN' AS TYPE
FROM Table2
UNION
SELECT T3_ID AS ID, T3_NAME AS NAME, T3_ADD AS ADDRESS, 'OBJECT' AS TYPE
FROM Table3


Is there a way to make SQL Server's Database Tuning Advisor use more than one core? I'm using SQL Server's Database Engine Tuning Advisor to generate recommendations for performance improvements on my database server.

For the "consuming workload" step, it appears to use all available cores. However, for the much more time consuming "performing analysis" step, only one core appears to be utilized.

Is there a way to get it to use multiple cores for this step?

I expect the analysis is carried out serially, one query at a time, as doing so in parallel would be unreliable.

DTA produces recommendations by creating hypothetical indexes and evaluating the impact on a query's execution plan. If analysis were carried out on multiple queries at a time, the index created for one query could influence the analysis of another.

A hypothetical index is created using the undocumented command CREATE INDEX WITH STATISTICS_ONLY and as the name implies this creates just the statistics for the index, without building the physical structure.


Unknown,AppDomain 66 (master.sys[runtime].65) is Marked for Unload Due to Memory Pressure I have noticed this error occasionally in the SQL error log:

  spid20s,Unknown,AppDomain 79 (master.sys[runtime].78) is marked for unload due to memory pressure.

I am using SQL Server 2016, SP1 CU5 (I am pushing for patching but the company is resistant).

Everything I have read points to non-CLR-specific memory pressure. There are suggestions around changing the MemToLeave setting in the start up parameters. Is this still the case for newer versions of SQL Server, or are there other recommendations?

Memory architecture was changed in SQL Server 2012 such that there was little need to worry about the MemToLeave setting anymore, especially if using 64-bit SQL Server. And, starting with SQL Server 2016 (which you are using), SQL Server is only available in 64-bit (see the "Note" at the top of the "What's new in Database Engine - SQL Server 2016" page). So, no, don't worry about MemToLeave.

Correct, "Memory Pressure" errors are not specific to SQLCLR. Those errors are not telling you the cause of memory pressure, but instead what is being impacted by there being memory pressure (which I doubt there is any possible way to truly have insight into the cause of anyway — I mean, if there are 10 processes taking up memory, which combination is really the cause? it's not necessarily what is taking up the largest chunk as that might be entirely valid). Memory pressure impacts other areas as well that might not show up in the error log, such as flushing the plan cache and/or buffer pool (i.e. data pages loaded into memory).

There are several built-in features that use SQLCLR, a partial list being the following:

Datatypes:

HierarchyID
Geography
Geometry

Functions:

FORMAT
PARSE
TRY_PARSE
AT TIME ZONE (starting in SQL Server 2016)
COMPRESS (but not UNCOMPRESS ; starting in SQL Server 2016)

Features:

Change Data Capture
Dynamic Management Framework
Replication
Policy-Based Management
Master Data Services
SSISDB (the "Fuzzy Lookup" feature)

One or more of those (or perhaps one that I did not list above) is what is being affected in your system. There are two clues, both within the (master.sys[runtime].78) part of that info, that tell us this:

the database being master (assuming you would never, ever load custom assemblies into master ;-))
the "owner" (i.e. AUTHORIZATION) of the assembly being sys (we cannot assign ownership of assemblies to either sys or INFORMATION_SCHEMA as neither of those principals has an SID). If you want to see the owner for each assembly, execute the following:

SELECT asm.[name] AS [Assembly],
       USER_NAME(asm.[principal_id]) AS [Owner],
       USER_SID(asm.[principal_id]) AS [OwnerSID]
FROM   sys.assemblies asm;

What you can do is:

You mention that this error shows up "occasionally", but have not exactly defined how often this really is. Once an hour is different than once a day which is different than once per week. Memory pressure happens, so unless this is more than a few times per day, I wouldn't spend too much time worrying about it.
Add more memory (either physically and/or allowing SQL Server to use more of the system memory if you currently have SQL Server constrained to a lesser amount)
Analyze what is consuming memory outside of SQL Server to see if there are unnecessary processes on the server that can be offloaded to other servers, or perhaps shut-off entirely (i.e. are there other services being used, and/or remote desktop sessions that are then running SSMS, etc that are using lots of memory?)


Microsoft access to Sql Server Data Migration I need to migrate some old MS Access data to a SQL Server Database. Which tool is easier for a newbie to work with: SQL Server Import/Export or Migration assistant for access(SSMA)

I would use the SQL Server import wizard, but make sure to use staging tables to import the data from Access, so you can perform data cleansing before inserting on the definitive tables.

A staging table is a temporary table used to load the data "as is" in the destination system (SQL Server in your case) so you can make sure the datatypes, collation, any custom format is according to your needs. This is made to avoid key/constraint violations, errors due to incompatible datypes, etc...

So for this example, you can import your access tables and ask the wizard to create the new tables during the import, after your are ok with your data, you can insert them in your definitive tables.


Cassandra Table data modeling for a event system monitoring data I have been working in a cassandra database to keep the real time data of an event/monitoring system that has quite a large number of events per hour(100k+) and I have hit a wall in a specific need: I need a way to get the data by event state(open,close) for all the events.
Each event has the following fields: id(this is generated by the event system), state, start_date,end_date,node, description.
My first aproach was to have a table with state as partition key and id as clustering:
CREATE TABLE alarms_by_state (
id,state,start_date,end_date,node,
PRIMARY KEY((state),id))

This that didn't work, as it keeps 2 states for each id.
What I need is that for example we receive event with id = 1 and state=open and the application saves it into the database, later another event with id=1 and state=closed and replaces the previous record.
I know this could all be done in the application side, but I'm trying to keep that control to a minimum due to the number of events I receive per hour.
Thank you for your time

that didn't work, as it keeps 2 states for each id.

I like that you tried this.  For anyone who sees this post in the future, it'll underscore the point that primary keys in Cassandra are unique.
Here's how I would do it.
CREATE TABLE alarms_by_date (
    id UUID,
    state TEXT,
    start_date DATE,
    end_date DATE,
    node TEXT,
    PRIMARY KEY((day,hour,m15),start_date,id))
WITH CLUSTERING INDEX ON (start_date DESC,id ASC);

CREATE INDEX ON alarms_by_date(state);

What this does:

Stores your data partitioned in 15 minute intervals.  With 100k/hour, that's too much to throw into a single partition.  Breaking that into 4 parts should keep it from growing too large.  This could be done with a single partition key, as long as the key was made up of those components (ex: 202102171045).
Applying a clustering index of start_date and id.  Not sure which of the two dates (start/end) are more important to your business case.  But this way, the data will be in descending order by time.  Also, I like using id columns as the last clustering key, to help ensure uniqueness.
Applying a secondary index on status is really the way to go here.  Yes, there's a lot wrong with secondary indexes in Cassandra.  But, the main problem with them, is that they can result in scans of multiple nodes.  By pre-empting queries using that index with the date/partition, queries will be restricted to only a single node.  So I think that'd be the way to go.


What's the overhead of rarely used columns We have a table with a lot of columns that are rarely used (most are NULL).

Can this slow down queries?
More importantly, do the empty columns take a lot of space per row?

Rarely used columns may be a sign of bad normalization, something that, if they are never used could (and this is highly dependent on your structure, engine, table size, data types and access pattern) help improve disk space and consequently performance. If the tables are tiny to small, there may be hardly any difference, although it may result in cleaner/more elegant design.

However in modern versions of MySQL's default Engine, InnoDB, NULLs may take as little overhead as a single bit, as they can be stored as field header flags.

If you really want to know exactly the difference, I would encourage you to test the difference of performance/disk space by separating optional columns on a separate table and joining on query time.

See other related answers: https://stackoverflow.com/a/230923/342196


Managing the Service accounts in large environment I wanted to know if there's a best practice in regards to secure a large environment of SQL Server instances (version 2000-2012).

Currently the SQL services are running with many users and I'm trying to improve the situation.
I know that the best practice is to use a domain account.

But what is the best practice for a large environment ?
Should I create a domain user that will be the service account for each service? For each Server? For the entire list of servers?

I would be happy to hear from your experience,

Thanks,

Roni.

A separate AD service account for each service on each instance is the most secure model and if you have some good password management software it shouldn't be a problem. The work involved in creating all the service accounts and entering them into your password software will be quite time consuming depending on how big your environment is.

Some people sacrifice some security by using the same service account for all the services on a SQL instance. The problem with this is if the account gets locked out then all the services including the SQL service account will be locked out. This problem becomes even worse if you use the same AD account across SQL instances as one instance can be affected by another. Also obviously if an account is compromised, the more services it is used for then the more services that are compromised.


Is maintaining a single database an issue when the underlying project becomes larger? I have seen this related post but it was more focusing on the amount of consumers for the databases, which is not my only problem.

I have a situation, where I am taking part of the design of a new data model implying a lot of relationships across tables and where different users would not be allowed to access all the data in the DB.

At the moment the infrastructure has many databases, even many database servers, with references to each other which makes it chaotic (in my opinion), and I am trying to know whether my idea to make the new data model in a single database is good, or at least, not ridiculous.

These are the reasons why the current architecture was on multiple databases (not servers, I mean databases):

Possibility to easily handle the access rights of the users (with Windows authentication) at a DB level.
It makes it easier to restore databases if somebody makes a mistakes and deletes all the records in a table (the size of a unique DB would make the "rollback" (using backups) too lengthy and too difficult)
The fact that if a single database has a deadlock, then all the architecture is blocked (with multiple databases, at least the independent data is still accessible).
Storing all the data in a single database is less efficient when the amount of records gets very large.

For all these points, I have the following answers:

It is possible to handle security very well using schemas. It comes down to having a good data model where security and compliance issues are also included in the design.
Maybe it's easier, but if somebody is able to affect the database badly enough, then that user simply shouldn't have had the right to perform that action. This comes back to a good security policy design.
Indeed, if there is a server failure, this is an issue. My best answer is that we could have a secondary server replicating the first, ready to take over if the first one was to fail.
I do not think this is true, data storage management should allow the database server to address performance issues.

Moreover, I would say that having a model where all the references between tables is represented by a foreign key is a key element to maintain data consistency. Having reference ID in one DB coming from another DB is inefficient and dangerous.

That's my thoughts, but I'm no expert in  DB management and I would be extremely happy if somebody told me that I am wrong, or could help my add arguments to my cause.

I would like to add that I am discussing an infrastructure in SQL Server (the price of the required hardware is not really an issue, but I could not change the underlying technology).

I would also be extremely happy to know if some academic research has been done on the subject.

I think you are correct in your answers and JNK is correct about locks. I see some reasons to have multiple databases.

Replication. If parts of your database should be replicated to local read servers they benefit from being on different database. I don't know about sql-server specifically but in mysql (and generally) a master can only maintain one binary log per database and if table A and B should replicate to some server and table C and D should replicate to another server than those needs to either be on different databases or the binary log needs to be filtered on the slaves.
Uptime and accessibility. If different parts of your data have different requirements on uptime and accessibility it would make sense to put them on different databases. One could have hot standby with all data replicated and the other could have a nightly dump.

But again, I think you're right :)


Why does JSON_QUERY within CASE statement fail to remove escape characters? It would seem that json_query fails to remove escape characters for the double quote character (") if the function is being applied within a case statement.

Example code below.

declare @data nvarchar(max);
declare @debug int = 0;

set @data = '{"id":10}';

set @debug = 0;
select
    isjson(@data) as 'validateData',
    @data as 'unprocessedSourceData',
    json_query(@data) as 'processedSourceData',
    case when @debug = 1 then json_query(@data) else null end as 'conditionallyProcessedSourceData'
    for json path, without_array_wrapper ;

set @debug = 1;
select
    isjson(@data) as 'validateData',
    @data as 'unprocessedSourceData',
    json_query(@data) as 'processedSourceData',
    case when @debug = 1 then json_query(@data) else null end as 'conditionallyProcessedSourceData'
    for json path, without_array_wrapper ;

Result from the first query:

{"validateData":1,"unprocessedSourceData":"{\"id\":10}","processedSourceData":{"id":10}}

Result from the second query:

{"validateData":1,"unprocessedSourceData":"{\"id\":10}","processedSourceData":{"id":10},"conditionallyProcessedSourceData":"{\"id\":10}"}

Note the escape characters toward the end: "{\"id\":10}"

1) Why would this be so?

2) How do I rework this query so that if @debug = 1 then the conditionallyProcessedSourceData element contains valid JSON, and if @debug = 0 then the element is null?

From JSON_QUERY (Transact-SQL)

  Returns a JSON fragment of type nvarchar(max).

and

  JSON_QUERY returns a valid JSON fragment. As a result, FOR JSON
  doesn't escape special characters in the JSON_QUERY return value.

So for json path escapes nvarchar(max) data unless the nvarchar(max) data comes from json_query().

In your query the data comes from the case statement not directly from json_query.

The workaround you can do is to put json_query outside of the case.

json_query(case when @debug = 1 then @data else null end)

Incorporating this into your full script, so you can compare the different approaches, gives:

declare @data nvarchar(max);
declare @debug int = 0;

set @data = '{"id":10}';

set @debug = 0;
select
    isjson(@data) as 'validateData',
    @data as 'unprocessedSourceData',
    json_query(@data) as 'processedSourceData',
    case when @debug = 1 then json_query(@data) else null end as 'conditionallyProcessedSourceData',
    json_query(case when @debug = 1 then @data else null end) as 'caseResultProcessed'
    for json path, without_array_wrapper ;

set @debug = 1;
select
    isjson(@data) as 'validateData',
    @data as 'unprocessedSourceData',
    json_query(@data) as 'processedSourceData',
    case when @debug = 1 then json_query(@data) else null end as 'conditionallyProcessedSourceData', -- This was the issue
    json_query(case when @debug = 1 then @data else null end) as 'caseResultProcessed' -- This is the solution
    for json path, without_array_wrapper ;


Oracle 12c client installer error I'm trying to install Oracle client 12.1.0.2.0 (32-bit) on a completely clean copy of windows Server 2012, and I'm hitting a problem.

The installer runs fine until it gets to the final part - the net configuration assistant. An error message appears and says "[INS-20802] Oracle Net Configuration Assistant failed.". Here's the relevant part of the log file that is generated (I can post the entire thing if requested, but here's the bit with the actual error occurs):

INFO: Read: UnsatisfiedLinkError exception loading native library: oranjni12
INFO: Stderr Read: java.lang.UnsatisfiedLinkError: oracle.net.common.NetGetEnv.jniGetOracleHome()Ljava/lang/String;
WARNING: Skipping line: UnsatisfiedLinkError exception loading native library: oranjni12
INFO: Read: java.lang.UnsatisfiedLinkError: C:\app\ORACLE_USER\product\12.1.0\client_1\BIN\oranjni12.dll: Can't find dependent libraries
INFO: Stderr Read:  at oracle.net.common.NetGetEnv.jniGetOracleHome(Native Method)
INFO: Stderr Read:  at oracle.net.common.NetGetEnv.getOracleHome(Unknown Source)
INFO: Stderr Read:  at oracle.net.ca.NetCALogger.getOracleHome(NetCALogger.java:230)
INFO: Stderr Read:  at oracle.net.ca.NetCALogger.initOracleParameters(NetCALogger.java:215)
INFO: Stderr Read:  at oracle.net.ca.NetCALogger.initLogger(NetCALogger.java:130)
INFO: Stderr Read:  at oracle.net.ca.NetCA.main(NetCA.java:433)
WARNING: Skipping line: java.lang.UnsatisfiedLinkError: C:\app\ORACLE_USER\product\12.1.0\client_1\BIN\oranjni12.dll: Can't find dependent libraries
INFO: Read:
WARNING: Skipping line:
INFO: Read: Error: oracle.net.common.NetGetEnv.jniGetOracleHome()Ljava/lang/String;
WARNING: Skipping line: Error: oracle.net.common.NetGetEnv.jniGetOracleHome()Ljava/lang/String;
INFO: Read: Oracle Net Services configuration failed.  The exit code is 1
WARNING: Skipping line: Oracle Net Services configuration failed.  The exit code is 1
INFO: Completed Plugin named: Oracle Net Configuration Assistant

Here are a few things I have tried:

turning the firewall off
disconnecting from network (clutching at straws!)
running the installer as administrator
re-downloading the client installer from Oracle, in case it had somehow been corrupted

None of these things made any difference. I have successfully installed this exact version of the client on a Windows Server 2012 PC in the past and it worked fine...

Has anyone else encountered this issue?

The install/oraparam.ini contains the following lines (even in a fresh download):

#MSVCREDIST_LOC flag will provide the name of the exe that is being shipped in stage/ext/bin
MSVCREDIST_LOC=vcredist_x64.exe

This is incorrect because you need the 32-bit version (x86).

Now if you check stage/ext/bin, there is a file vcredist_x86.exe, and obviously no x64, since it is a 32-bit installer. Try editing oraparam.init and fixing the above line to:

MSVCREDIST_LOC=vcredist_x86.exe

Another possibility is that you need to install the Microsoft C++ 2010 Redistributable manually: http://www.microsoft.com/en-us/download/confirmation.aspx?id=5555


Modify linked server definition (Oracle) in SQL Server 2012 I have created a linked Oracle server in SQL Server 2012 using Oracle Provider for OLE DB and TNSNAMES.ORA (the Oracle database is clustered and, as far as I know, no other connection method works with clustered servers). This works fine.

Now the Oracle database has been moved to another cluster, so I edited TNSNAMES.ORA. Oracle SQL Developer works fine.

It seems that SQL Server doesn't re-read TNSNAMES.ORA but stores the connection details internally, because even after a server reboot all queries time out.

Does anyone know of a solution other than deleting and recreating the linked server ?

(That's what I did to resolve my issue, but it shouldn't be that hard !)

How you solved the issue is the way to solve it because we can't modify a Linked Server. And SQL Server doesnt read the tnsnames.ora. Though the solution is drop and recreate we can use SQL Management Studio to edit the Drop and Create script as follows:
1. Right click on the linked server.
2. Choose Script Linked Server as.
3. Choose New Query Editor Window.
4. Now we can modify the script as our requirements and run. This simply drops and recreates the Linked Server.

Thanks


Purge old binlogs in mysql 4 I need to purge binary logs from mysql version 4. I have checked the manual found that the syntax is like an advanced versions:

PURGE MASTER LOGS TO mysql-bin.xxx

However, when i use this command, i get the following error:

ERROR 1064 (00000): You have an error in your SQL syntax.  Check the manual that corresponds to your MySQL server version for the right syntax to use near 'BINARY LOGS TO 'mysql-bin.xxx'' at line 1

Please avdise.

The error message says right syntax to use near 'BINARY LOGS TO 'mysql-bin.xxx' but you mentioned use of PURGE MASTER LOGS TO ...?

Try using PURGE MASTER LOGS TO mysql-bin.xxx instead of using 'PURGE BINARY ....' as the latter was added to MySQL v4.1.1 and if your version is any lower, PURGE BINARY ... won't work.


How to reformat Oracle SQL quadratic-equation solver script? I was wondering how I might be able to tweak this SQL script:

ALTER SESSION
SET NLS_NUMERIC_CHARACTERS='. ';

SELECT 'Not a quadratic equation.' ans
FROM dual
WHERE &&A = 0
UNION
SELECT 'x = ' || to_char(-&&B/2/&A)
FROM dual
WHERE &A != 0
  AND &B*&B-4*&A*&&C = 0
UNION
SELECT 'x1 = ' || to_char((-&B+sqrt(&B*&B-4*&A*&C))/2/&A) || ', x2 = ' || to_char(-&B-sqrt(&B*&B-4*&A*&C))/2/&A
FROM dual
WHERE &A != 0
  AND &B*&B-4*&A*&C > 0
UNION
SELECT 'x1 = (' || to_char(-&B/2/&A) || ',' || to_char(sqrt(-&B*&B+4*&A*&C)/2/&A) || '), ' || 'x2 = (' || to_char(-&B/2/&A) || ',' || to_char(-sqrt(-&B*&B+4*&A*&C)/2/&A) || ')'
FROM dual
WHERE &A != 0
  AND &B*&B-4*&A*&C < 0;

to treat the quadratic equation solver-output from the inputs (a=1, b=5, c=1) displayed in the below image:

as separate values contained within separate columns denoted "Answer 1" and "Answer 2" respectively; so "Answer 1" column contains -2.0871... and "Answer 2" column contains -4.791...

I took a few hacks at it but I couldn't quite format according to the aforementioned.

Well i got the following to get the output in two columns:

SELECT 'Not a quadratic equation.' as "ans1", '0' as "ans2"
FROM dual
WHERE &&A = 0
UNION
SELECT 'x = ' || to_char(-&&B/2/&A) as "ans1", '0' as "ans2"
FROM dual
WHERE &A != 0
  AND &B*&B-4*&A*&&C = 0
UNION
SELECT 'x1 = ' || to_char((-&B+sqrt(&B*&B-4*&A*&C))/2/&A) as "ans1", 'x2 = ' || to_char(-&B-sqrt(&B*&B-4*&A*&C))/2/&A as "ans2"
FROM dual
WHERE &A != 0
  AND &B*&B-4*&A*&C > 0
UNION
SELECT 'x1 = (' || to_char(-&B/2/&A) || ',' || to_char(sqrt(-&B*&B+4*&A*&C)/2/&A) || '), ' as "ans1", 'x2 = (' || to_char(-&B/2/&A) || ',' || to_char(-sqrt(-&B*&B+4*&A*&C)/2/&A) || ')' as "ans2"
FROM dual
WHERE &A != 0
  AND &B*&B-4*&A*&C < 0;


Postgres heap table performance on SSD I've read Performance difference between MySQL and PostgreSQL for the same schema/queries.. Is it still relevant with appearance of Solid State Drive?

That article said that Postgres is better for complex queries and subquering, and worse for simple queries by ids and sequential scans.

Postgres is worse/slower for some queries due each table is a heap, means hasn't clustered index, means rows are not physically sorted on hard drive by their primary key. So if you want to read a significant portion of records (by id as I understand) from Postgres - that will lead to many random I/O and poorer performance if compare with MySQL.

Questions:

But how about SSD? Have sequential reads got same performance for Postgres and MySQL?
Also does it make any sense to use SSD instead of HHD with RDBMs?

Update: related to Rick James' answer

MySQL use Plan A and Postgres use Plan B, right?
Plan A means clustered index, right? The table is physically sorted by lastName + firstName.
Plan A on HDD could perform better that Plan B on SSD, so Postgres + SSD is not an silver bullet. You need to choose MySQL for quering clustered index, right?
Pls more details about which queries do you mean by I/O bound? In my understanding any call to database == I/O bound operation.

"Count the disk hits."

Let's construct a simplistic table and query against it.  Then let's discuss how two database engines will perform that query.

We have a table with lastname and firstname, plus other stuff.  The table is quite big; so big that it cannot be cached in RAM.  The query needs to look up all the people with a given last name and some first initial:

SELECT * FROM tbl
    WHERE lastname = 'James'
      AND firstname LIKE 'R%';

Plan A:  The table is ordered by lastname+firstname using a B+Tree.  The operation work like this:

Locate the first (James,R) row.  This requires drilling down the layers of a B+Tree of depth perhaps 4.  It is very likely that all non-leaf nodes in such a tree will stay cached.  But the leaf node won't be (because of the size of the atable).  Cost (so far):  0 disk hits for the non-leaf nodes, plus 1 disk hit for the leaf node.
Scan forward through the B+Tree.  Let's say the typical block in the tree holds 100 records.  Let's say there are 300 names that match.  Cost:  About 3 more disk hits.

Total cost:  ~4 disk hits.

Plan B:  There is an INDEX(lastname, firstname) to hash-pointer, which then leads to the data.

Traverse that index to find all the 300 matching names.  If the INDEX is something like a BTree, that is, again, ~4 disk hits.
For each of the 300 names, do a hash lookup in the data to get SELECT *.  Since the look up involves a hash, the records will be scattered around.  And, since I hypoticated that the table was too big to be cached, that is upwards of 300 disk hits to find the records.

Total cost:  Somewhere between 4 and 304; let's be generous and say 100.

Further, let's say there is 1ms of CPU for the entire query.  And let's say 10ms for HDD disk hit and 0.5ms for SDD disk hit.

Plan             HDD (ms)   SDD (ms)   RAM (ms, if the table were fully cached)
Plan A: B+Tree    41          3          1
Plan B: Hash    1001         51          1

(Side note: Indexing on UUID in any database engine is like throwing out BTree and using only Hash.)

Until SSDs become as fast as RAM, there will be a performance gap.

As your you Question 2 (Does it make sense to use SSDs):  If you are I/O-bound, then yes.  Otherwise, it may not be "cost-effective".  An RDBMS tries to cache everything, thereby eliminating I/O (for reads); if the data is small enough, the I/O speed does not matter.

On the other hand, writes are much more likely to be dependent on I/O speed.  But that is another discussion (with a similar conclusion).

Responding to question update:

Disclosure:  I am very familiar with MyISAM; Plan A is very close to MySQL's InnoDB.  I am very unfamiliar with Postgres; I do not know if Plan B relates to it.
Yes.
Yes.
It depends. A SELECT where everything it needs is cached in ram: no I/O.  A write in a transaction with setting at maximum security: always I/O.  And there is lots of "in between".


SSPI handshake failed with error code 0x8009030c, state 14 DESCRIPTION:    SSPI handshake failed with error code 0x8009030c, state 14 while establishing a connection with integrated security; the connection has been closed. Reason: AcceptSecurityContext failed. The Windows error code indicates the cause of failure.  [CLIENT: 10.XXX.XX.XX} ***The client address is different on different occasion

Can i refer this problem to my network admin? is this a network issue or a sql server issue. Is there any other way other then changing the registry ? Can i safely ignore this error ?

WORKAROUND/SOLUTION
Loopback check can be removed by adding a registry entry as follows:

Edit the registry using regedit. (Start –> Run > Regedit )
Navigate to: HKLM\System\CurrentControlSet\Control\LSA
Add a DWORD value called “DisableLoopbackCheck”
Set this value to 1


Skip specific field name I am trying to execute a table from a MySQL database using a for loop statement, in order to get the field names as the column header.

Here is my code:

$result = mysql_query("SELECT * FROM bom_commonmaterials where description like '%CAP%'");
        if (!$result) {
            die("Query to show fields from table failed");
        }

        $fields_num = mysql_num_fields($result);

        echo "

";
        // printing table headers
        for($i=0; $i<$fields_num; $i++)
        {
            $field = mysql_fetch_field($result);
            echo "{$field->name}	";
            if ($field ="WATTAGE")
            {
                CONTINUE;
            }
        }
        echo "

\n";
        // printing table rows
        while($row = mysql_fetch_row($result))
        {
            echo "
";

            // $row is array... foreach( .. ) puts every element
            // of $row to $cell variable
            foreach($row as $cell)
                echo "$cell	";

            echo "
\n";

        }
        mysql_free_result($result);
        echo "
";

As you can see, I have an if statement $field ="WATTAGE" inside the for loop statement. I am trying to execute this code so that the field name WATTAGE will be not included as a table header, but I think my code is wrong.

In this case I need help on how to skip a specific field name, and also the field rows of this field name.

Note: I used loop statement in this because the table field name from the database is not fixed. For example the user wants to add new columns. So I can't just code it manually because the table doesn't have a fixed data.

You should have an IF ELSE in your for loop:

for($i=0; $i<$fields_num; $i++)

You are assigning $field to the string WATTAGE here:

$field = mysql_fetch_field($result);
echo "{$field->name}	";

before you get to the code

echo "{$field->name}	";
if ($field ="WATTAGE")
{
  CONTINUE;
}

WATTAGE has has already been assigned to $field before you get to if($field = "WATTAGE") code.

Put an IF/ELSE in your for loop and then put the CONTINUE; bit in the IF and the assignment in the ELSE part. That way you'll never get to the $field assignment if the fieldname is WATTAGE.