MYSQL: What is the benefit of compound indexes, over separate ones? I've been experimenting with the performance of SELECT queries on a table with about a million records, consisting of these columns(among others..):

id, lastName, firstName,

Wehn i perform this query:

SELECT *
FROM `user`
WHERE lastName = 'someLastName'
AND firstName = 'someFirstName'

I get more or less the same performance(in terms of miliseconds), whether i have two separate indexes on those fields, or have a compound index(lastName,firstName).

I came across this thread:difference between creating index with two columns and creating separate index on two columns

The guy explains the case where a compound index might fall short, bu he didn't give any example where it can actually be beneficial, over setting up separate indexes.

Can someone provide an example/simple explanation of a scenario where a compound index is a good choice?

If you know that you frequently query on a combination of fields, a combined index means "you only have to search one B-tree."  Otherwise, the server would have to search n individual B-trees and then combine the results.

(N.B.:  A "B-tree" is a data structure commonly used by database servers to create indexes ...)

(You can use the EXPLAIN command to quickly see how the server plans to do this ... and to see that it does have to do this.)


Data Model For Summarizing Student Info I work for a school district and one of the many things the admin wants to know is how many tardies a student has for a given time frame.  I could certainly go retrieve the count from the attendance table, but wondered about summarizing the data and if that might be faster/better.  So, I've conisdered a table that looks something like:

create table stu_summary (
id int identity primary key nonclustered not null,
stu_id char(10) not null primary key references student (stu_id),
group_name varchar(50) not null,
item_name varchar(50),
value varchar(100))

which would then allow me to have data that looked like:

1, 'abc112233', 'tardy-count', '2011-F-21-99383', 2

which would signify that this record shows that student abc112233 has 2 tardies for the academic year 2011 in fall for progress period 21 and class id 99383.

Is there a better way to store this kind of info?  I'm hoping to keep the table flexible and not tie it down to the specific data columns.  Am I headed in a bad direction?  What have you done to store this kind of date span specific summaries?

Edit
There has been speculation as to the amount of data, number of tables in question here, so let me illuminate:

There are 70k rows of attendance recorded each day, 1.2m per month
Each student's tardy count will be displayed in an attendance window with a grid.  This is important because a student normally has 6 classes and so their attendance will be loaded frequently throughout the day
The attendance table has a code column in it that determines what kind of attendance it is.  The tardy code is one of those values.

Calculate those counts on the fly.

For most data sets of this nature, I would imagine calculating the count of tardiness events would be relatively cheap.  How many rows do you expect there to be in the long run in your students or attendance tables?  Probably on the order of tens of thousands at most.  Contrast that to calculating an account balance from its transaction history in database for a large bank with billions of transactions. In this case I would consider persisting a summary of these aggregations somewhere.

If you wanted to pre-calculate this data anyway, I would summarize the count of tardiness events per student per year in an indexed view as suggested in the top answer here.

For example:

CREATE VIEW dbo.tardiness_summary
WITH SCHEMABINDING
AS
SELECT
     student_id
   , year
   , COUNT_BIG(*) AS tardy_count
FROM dbo.attendance
GROUP BY
     student_id
   , year
;

CREATE UNIQUE CLUSTERED INDEX IX_tardiness_summary
ON dbo.tardiness_summary (
     student_id
   , year
;

This is not a flexible approach, however, as this view is now schema-bound to the base tables and thus any modifications to either the view or the table will require rebuilding the view. Indexed views also have many restrictions on how they may be created or queried. Their advantage is that they guarantee the summary table will stay in sync with its sources because the database engine is now doing this work for you.


asp.net caused mysql 99% usage I have an asp.net application which use Mysql. I test it on testing server running MySQL. When the application is running, it consumes 40% of cpu. I run the same code in production server which also running MySQL, now it consumes 99% of cpu usage. What setting in MySQL config will affect the CPU consuming level? Because I am running the same code but the result is totally different.

Here is the config of high cpu usage mysql:

[mysqld]
interactive_timeout=28800
wait_timeout=28800

port=3306

datadir=C:/ProgramData/MySQL/MySQL Server 5.7\Data

character-set-server=utf8

default-storage-engine=INNODB

sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

log-output=FILE
general-log=1
general_log_file="EC2AMAZ-SSAVRCC.log"
slow-query-log=1
slow_query_log_file="EC2AMAZ-SSAVRCC-slow.log"
long_query_time=10

log-error="EC2AMAZ-SSAVRCC.err"

server-id=1

secure-file-priv="C:/ProgramData/MySQL/MySQL Server 5.7/Uploads"

max_connections=151

query_cache_size=0

table_open_cache=2000

tmp_table_size=2G

thread_cache_size=10

myisam_max_sort_file_size=100G

myisam_sort_buffer_size=4G

key_buffer_size = 10M

read_buffer_size=64K
read_rnd_buffer_size=256K

innodb_flush_log_at_trx_commit=1

innodb_log_buffer_size = 8M

innodb_buffer_pool_size = 6144M

innodb_log_file_size=48M

innodb_thread_concurrency=33

innodb_autoextend_increment=64

innodb_buffer_pool_instances=8

innodb_concurrency_tickets=5000

innodb_old_blocks_time=1000

innodb_open_files=300

innodb_stats_on_metadata=0

innodb_file_per_table=1

innodb_checksum_algorithm=0

back_log=80

flush_time=0

 join_buffer_size=256K

max_allowed_packet=10M

max_connect_errors=100

open_files_limit=4161

query_cache_type=0

sort_buffer_size=256K

table_definition_cache=1400

binlog_row_event_max_size=8K

sync_master_info=10000

sync_relay_log=10000

sync_relay_log_info=10000

Here is the code of less cpu usage mysql:

[mysqld]

port=3306

datadir=C:/ProgramData/MySQL/MySQL Server 5.7/Data

default-storage-engine=INNODB

sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

log-output=FILE

general-log=0

general_log_file="c:\MySQL_LOG\WIN-9BPM7TSIO9C.log"

slow-query-log=0

slow_query_log_file="WIN-9BPM7TSIO9C-slow.log"

long_query_time=1

log-error="WIN-9BPM7TSIO9C.err"

server-id=1

secure-file-priv="C:/ProgramData/MySQL/MySQL Server 5.7/Uploads"

max_connections=151

table_open_cache=2000

tmp_table_size=16M

thread_cache_size=10

myisam_max_sort_file_size=100G

myisam_sort_buffer_size=8M

key_buffer_size=8M

read_buffer_size=0

read_rnd_buffer_size=0

innodb_flush_log_at_trx_commit=1

innodb_log_buffer_size=1M

innodb_buffer_pool_size=8M

innodb_log_file_size=48M

innodb_thread_concurrency=17

innodb_autoextend_increment=64

innodb_buffer_pool_instances=8

innodb_concurrency_tickets=5000

innodb_old_blocks_time=1000

innodb_open_files=300

innodb_stats_on_metadata=0

innodb_file_per_table=1

innodb_checksum_algorithm=0

back_log=80

flush_time=0

join_buffer_size=256K

max_allowed_packet=100M

max_connect_errors=100

open_files_limit=4161

sort_buffer_size=256K

table_definition_cache=1400

binlog_row_event_max_size=8K

sync_master_info=10000

sync_relay_log=10000

sync_relay_log_info=10000

net_read_timeout = 999999
net_write_timeout = 999999

There may be various causes for this difference, but the first thing that comes to mind is that the second configuration is tuned to use less memory (e.g. innodb_buffer_pool_size, tmp_table_size). Therefore, it may need to use more disk IO to process the same amount of data. CPU has to wait for disk operations to complete before it may proceed to the next chunk of data and hence it becomes underutilized if there are lots of them.

You may try to confirm this conjecture by establishing disk performance monitoring approximately like described in https://blogs.technet.microsoft.com/askcore/2012/03/16/windows-performance-monitor-disk-counters-explained/ .

And I would say 100% utilization of one CPU core is a normal thing with bulk operations in MySQL. The engine doesn’t have much parallelization so it generally can’t use more than one core per session. Lower utilization during bulk operations (e.g. mass insert) means there’s non-CPU bottleneck that needs addressing (because it makes the same operation take longer). You may try to figure out if that’s the case by comparing the time it takes the test script to complete in the two environments.


Will changing server_id on master break replication? I have few MySql absolutely isolated trees, each one of them has one master and few slaves.

For purposes of automation I need to set the same server_id to all masters (this will simplify the usage of pt-heartbeat which needs server_id). This should be perfectly fine since all trees are isolated.

I am wondering, if I change server_id on master, restart master, do I damage the replication? Of course there are will be a short brake of replication, but will this break replication completely?

The question is does changing server_id break replication completely, does it change positions in bin-log?

UPDATE: actually I don't have to restart mysql, I can just change it in console with SET GLOBAL server_id=xxx, in this case I don't need restart.

It should not break replication provided you do not choose a bad server_id. Here is why:

Given this server_id setup

Master with server_id 1
Slave with server_id 2

Here is what happens with Replication

Master completes and SQL command
Master records its server_id along with SQL
Slave reads Binary Logs of Master and stores them in Relay Logs
Slave reads reads Relay Logs and sees server_id 1
Since the Master server_id and Slave server_id do not match, Slaves run the SQL

What happens if you set the Master's server_id to 2 ?

Master completes and SQL command
Master records its server_id along with SQL
Slave reads Binary Logs of Master and stores them in Relay Logs
Slave reads reads Relay Logs and sees server_id 1
Since the Master server_id and Slave server_id match, Slaves does not run the SQL

The only bad server_id to assign is the same server_id as the Slave. Other than that, Replication should not break.


What is the advantage of pluggable database over normal databases? Why creating pluggable database over normal database is good for system performance.

Not a performance gain, but multi-tenant architecture reduces hardware costs.

http://docs.oracle.com/database/121/CNCPT/cdbovrvw.htm#CEGJGEJH


Infinite scrolling with full text search in PostgreSQL I am building a service that allows full-text searches and I would like to paginate the results.

I don't want to use OFFSET, so I opted for infinite scrolling with the keyset pagination, as outlined here.

My search query looks like this:

SELECT *, ts_rank_cd(t.tsv, plainto_tsquery($1)) AS rank
  FROM profiles AS t, plainto_tsquery($1) AS q
  WHERE (tsv @@ q) AND
    (rank, n, id) < ($2, $3, $4)
  ORDER BY rank DESC, n DESC, id DESC
  LIMIT 50

The tsv column contains tsvector objects and it is indexed with GIN. The search results are first ordered by rank, which is computed by PostgreSQL, and then by another column which is not relevant here and finally by id.

It seems correct to me, but I get this error: column "rank" does not exist. It works if I replace rank with its expression in the WHERE clause. Will that impact performance?

A second question: how can I make keyset pagination work if I wanted to sort id ASC instead of id DESC? I cannot split the expression in the WHERE clause like this (rank, n) < ($2, $3) AND id > $4 because then I lose the ordering priority (id must be used only to break ties).

It seems correct to me, but I get this error: column "rank" does not exist.  It works if I replace rank with its expression in the WHERE clause. Will that impact performance?

Yes it will in as much as without that change the query does not work. So that's a positive effect, however rank will not be computed twice so there is no penalty for that.

Nevertheless the whole table needs to be scanned to generate the rank column before the order-by can be done, so efficiency is about the same as offset.

  A second question: how can I make keyset pagination work if I wanted to sort id ASC instead of id DESC

If it's a number type, use -id instead. else you'd have to expand the tuple comparison out into a big messy expression.


Update all foreign keys to a different primary key In a MySQL database, I have a table full of business information.  Lots of businesses have been inserted twice due to human error, so the same information exists with two different primary keys.  Let's say the same delivery service is represented by primary key 3 and 7.

Businesses:
| id | name           |
| 3  | Planet Express |
| 7  | Planet Express |

Lots of other tables foreign key to this business table. (Employees, etc)

Employees:
| id  | name   | business |
| 999 | Fry    | 3        |
| 666 | Bender | 7        |

Fry and Bender really do work for the same business. I'd like to delete the business row with primary key 7, and tell all the foreign key tables that instead of cascade delete, or cascade null, they should update to point to primary key 3.

Obviously I could do this by hand, but I'd rather make the database do it on my behalf since it has a better memory for this kind of thing.  Is there a way to do this?  Like DELETE FROM business WHERE id = 7 ON CASCADE set 3; or something?

Probably not interesting for the OP but I'll give it a shot anyhow. Given the current tables:

create table Businesses
( id int not null primary key
, name varchar(20) not null) engine=innodb;

create table Employees
( id int not null primary key
, name varchar(20) not null
, business int not null) engine=innodb;

insert into Businesses (id, name)
values (3,'Planet Express'),(7,'Planet Express'),(11,'Whatever');

insert into Employees (id, name, business)
values (999,'Fry',3),(666,'Bender',7),(333,'Bob',11);

To fix the immediate problem we can update a join against a derived table like:

update Employees e
join (
    select min(id) as min_id, max(id) as max_id, name
    from Businesses
    group by name having count(1) = 2
) as b on e.business = b.max_id
    set e.business = b.min_id;

select * from Employees;
+-----+--------+----------+
| id  | name   | business |
+-----+--------+----------+
| 333 | Bob    |       11 |
| 666 | Bender |        3 |
| 999 | Fry    |        3 |
+-----+--------+----------+

and then delete the duplicate business. Note that I assume that there are exactly two rows involved in each duplicate.

For the long run I would suggest several things. Names like id, name, etc are to vague, name them something like business_id and use that name throughout the model.

If the name of a Businesses is supposed to be unique, add a constraint that ensures this. Something like:

create table Businesses
( business_id int not null primary key
, business_name varchar(20) not null unique
) engine=innodb;

create table Employees
( employee_id int not null primary key
, employee_name varchar(20) not null
, business_id int not null
,    constraint fk_business foreign key (business_id)
                            references Businesses (business_id)
) engine=innodb;


SQL: pivot non numeric data I have a set of data which basically looks like the following:
+---------+--------+
| EventId | Field  |
+---------+--------+
|       1 | apple  |
|       1 | paper  |
|       1 | boy    |
|       2 | banana |
|       2 | cat    |
|       3 | girl   |
+---------+--------+

I would like to pivot this to look like this:
+-------+--------+------+
|   1   |   2    |  3   |
+-------+--------+------+
| apple | banana | girl |
| paper | cat    |      |
| boy   |        |      |
+-------+--------+------+

I am trying to fiddle around PIVOT, but I can't get the result I want.
Am I looking at or using the wrong command? Is PIVOT the correct relational operator for this?
This is the code I have been working on so far:
DECLARE @columns NVARCHAR(MAX), @sql NVARCHAR(MAX);
SET @columns = N'';
SELECT @columns += N', ' + QUOTENAME(EventID)
  FROM (
            select distinct
                EventId
            from Events
        ) AS x;

SET @sql =
N'select  ' +
STUFF(@columns, 1, 2, '') +  '

into ##tempMap from
(
    select
        ev.EventID,ev.FieldName
    from Events ev
) as a
pivot
(
      max(FieldName) for EventId in ('
  + STUFF(REPLACE(@columns, ', p.[', ',['), 1, 1, '')
  + ')
) as pivottable;';

EXEC sp_executesql @sql

select * from ##tempMap
drop table ##tempMap

The code above returns the following results because of the MAX aggregate function.
+-------+-----+------+
|   1   |  2  |  3   |
+-------+-----+------+
| paper | cat | girl |
+-------+-----+------+

This answer isn't dynamic, but shows you how to achieve the goal in pure TSQL, making it dynamic should be straightforward.
Essentially, you just need to add another column to spread the data out so that the MAX(fieldname) isn't the max for the event.  I've chosen to use a ROW_NUMBER partitioned by the EventID so that each entry for each event is also numbered.
I did also look at https://stackoverflow.com/questions/1343145/tsql-pivot-without-aggregate-function
DROP TABLE IF EXISTS ##Events

CREATE TABLE ##Events
    (
    EventID INT NOT NULL
    , FieldName VARCHAR(20) NOT NULL
    )

INSERT INTO ##Events
    (EventID, FieldName)
VALUES (1, 'apple')
    , (1, 'paper')
    , (1, 'boy')
    , (2, 'banana')
    , (2, 'cat')
    , (3, 'girl')

;WITH CTE_RN AS
    (
    SELECT EventID
        , FieldName
        , RN = ROW_NUMBER() OVER (PARTITION BY EventID ORDER BY FieldName)
    FROM ##Events AS E
    )
SELECT [1]
    , [2]
    , [3]
FROM CTE_RN
    PIVOT (MAX(FieldName) FOR EventID IN ([1], [2], [3])) AS pvt


Oracle 10g Administration Redo log files are not multiplexed in our database. Redo log blocks are corrupted in group 2, and archiving has stopped. All the redo logs are filled and database activity is halted. Database writer has written everything to disk. Which command would execute to proceed further?

If you are sure that you did not lose any data and the problem is only at redolog level you can issue (as sysdba):

alter database open resetlogs;

backup all the datafiles, control files and online redo log before issuing the command  so you can "rollback" if something goes wrong.


Get rows where lastLoginTimestamp is in last 7 days I'm trying to get the rows from a table where lastLoginTimestamp is in last 7 days and I'm doing as follow:

SELECT * FROM representative WHERE DATE_SUB(lastLoginTimestamp, INTERVAL 7 DAY)

lastLoginTimestamp is stored as a VARCHAR so this is where the problem is I though. How do get that rows in this case? Any help?

This are some of the values on that column to play with:

1427162434
1422557283
1395854997

Update

Based on the first answer I've changed my query to this:

SELECT
    *, from_unixtime(lastLoginTimestamp) AS lastLogin
FROM
    representative
WHERE
    DATE_SUB(
        from_unixtime(lastLoginTimestamp),
        INTERVAL 7 DAY
    )

But I'm getting the wrong values, for example I'm getting a row from this lastLogin = 2015-03-23 21:30:34 which is wrong

The values seem to be Unix timestamps, stored as strings. You can use the special datetime functions that MySQL has for conversions:

SELECT *
FROM representative
WHERE DATE_ADD(FROM_UNIXTIME(lastLoginTimestamp), INTERVAL 7 DAY) >= NOW() ;

but it won't be very good for efficiency. Much more efficient, if there is an index on the column, to do it the other way around:

(we assume that the values in the columns are CHAR(10). If some values have smaller length, you'll get erroneous results.)

SELECT *
FROM representative
WHERE lastLoginTimestamp
      >= CAST(UNIX_TIMESTAMP(NOW() - INTERVAL 7 DAY) AS CHAR(10));

The above condition if you want all rows, starting from exactly 7 days ago, same time as now. If you want rows starting from 6 days ago at midnight, so previous 6 days + today (whatever that is, a few minutes or 23 hours, 59 mins):

SELECT *
FROM representative
WHERE lastLoginTimestamp
      >= CAST(UNIX_TIMESTAMP(CURRENT_DATE - INTERVAL 6 DAY) AS CHAR(10));


max_connections set to 500, Max_used_connections never go over 151 I have this set in the mysql config file, rebooted, seems to be correct.
SHOW VARIABLES LIKE "max_connections";
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 500   |
+-----------------+-------+

I have a load test against my server. This never get's above 151, server just gets real slow until the testing is done.
mysql> SHOW STATUS LIKE 'max_used_connections';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| Max_used_connections | 151   |
+----------------------+-------+

I have already put in every config option that supposedly will fix this. Will Max_used_connections ever go above 151?
ubuntu 16
mysql  Ver 14.14 Distrib 5.7.33, for Linux (x86_64) using  EditLine wrapper
been dinking around with this all day, thanks for any advice.

Tell us more about the load test.  What probably happened...
The test succeeded in launching 151 threads at the 'same' time.  Those threads then fought with each other for CPU, disk, network, etc.  They were each given "equal" access to everything.
What does that lead to?  Well, imagine letting 151 customers into a small grocery store.  Each would be pushing there cart but bumping into other carts.  It would take 'forever' to finish one aisle and get to the next.
The net effect is everyone takes an abnormally long time to finish the task.  Have you learned anything about the load?  No.  The grocery store was overloaded.
Run the load test again, but with the "max threads" set to something like twice the number of CPUs.
Is it possible to have 500 connections?  Yes.  But only if they are not doing what the load test is designed to do:  pound on the database and do nothing else.
That is not realistic.  A web page, for example, will grab some data, build some HTML, wait for the user to input something, etc.  That is, a realistic load involves more than just hitting the database.
What was the goal of your load test?  Maybe we can help you get some real news instead of this fake news.


halt on error in a sqlplus script I am deploying pl/sql code using several sql files that are called with @@file.
If a package got a compilation error the script continues to the end.

Is there a way to stop on every compilation error?

I tried WHENEVER SQLERROR EXIT SQL.SQLCODE but the script still continues.

This can't be done without some kind of workaround, so here's one for you.

You can recompile the PL/SQL after creation & raise an exception if the recompilation fails. This will cause SQL*Plus to exit on failure.

For example:

test.sql:

create or replace procedure foo
as
begin
  this is an error;
end;
/

exec execute immediate 'alter procedure foo compile'; exception when others then raise_application_error(-20000,'compilation error')

Example:

[oracle@node1 ~]$ sqlplus phil/phil

SQL*Plus: Release 11.2.0.2.0 Production on Fri Dec 14 20:45:47 2012

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

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

SQL> WHENEVER SQLERROR EXIT SQL.SQLCODE
SQL> @test.sql

Warning: Procedure created with compilation errors.

BEGIN execute immediate 'alter procedure foo compile'; exception when others then raise_application_error(-20000,'compilation error'); END;

*
ERROR at line 1:
ORA-20000: compilation error
ORA-06512: at line 1

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@node1 ~]$


Error after applying SQL Server 2008 R2 Upgrade 10.50.6542 (Latest Build) First the problem: Since I applied the latest patch to SQL Server 2008 R2  (released on March 3, 2016) that should have taken my server to build 10.50.6542 I can't connect anymore. This patch is to provide support TLS 1.2.

The error I'm getting (when attempting to connect using a client tool directly in the server) is

"A connection was succesfully established with the server, but then an error occurred during the pre-login handshake"

The server is a Windows Server 2012 R2 and is running NET Framework 4.6.1 (some posts point to older version of .NET framework causing this problem, but this is not my case).

I tried to run the following packages to see if this would fix...

SQL Server Native Client (x86 and x64)
Hotfix rollup 3106993 for the .NET Framework 2.0 SP2 in Windows Server 2012 R2 and Windows 8.1
Hotfix rollup 3106994 for the .NET Framework 4.0 in Windows
Hotfix rollup 3099845 for the .NET Framework 4.5.2, 4.5.1, and 4.5

... None of these were necessary, and nothing helped...

Has anybody experienced this? Any ideas or suggestions?

Thanks!

One Update 24 hours later
I inspected the SQL Server error log looking for entries that include only a Start, 5 failed login attempts and a stop. From all the messages in that time-frame (107 entries in total) the only message that includes anything like an error is

 The SQL Server Network Interface library could not register the Service Principal Name (SPN) for the SQL Server service. Error: 0x2098, state: 15. Failure to register an SPN may cause integrated authentication to fall back to NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies.

This message though… has been reported on server startup even before I applied the patch so I am 100% confident that it is a red herring. No logs are inserted in the SQL Server error log as a result of the failed login attempts.

Another Update

Based on suggestions I got from people here, I downloaded and installed the latest version of SSMS (17.3) on the server. When I tried connecting this time, I got a different error so it seems I'm moving further, but I'm not there yet.

So it seems that it is true that old versions of SSMS do target .net 3.5 and even if you have newer versions of the .net framework the client connections will not work, the question is now why is that I still get this "No process is on the other end of the pipe", even when most of the client components have been updated.

Some more things I have investigated (and discarded)

The “client protocol” order in SQL Server Configuration Manager seems to be the correct one, as TCP/IP is at the top. This was suggested as a possible cause of trouble, but in my case it is all appropriately configured (apparently)

Tried to install “Microsoft ODBC Driver for SQL Server” as suggested in the TLS 1.2 support for Microsoft SQL Server but that didn’t make a difference either. After installing, the error message is the same
Tried adding the registry keys at HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.2 (also as suggested in the same article). Same error…

Finally! after 3 days of trying everything under the sun I found the root cause of my problem.

The aha! moment was when I got together with a group trying to understand why this patch was deploying just fine in other servers while not in this particular one.

We noticed that something different (among other things... that we listed in a neatly organized notepad) was that this SQL Server 2008 R2 was configured for encryption in transit (a.k.a. SSL encryption) via certificates and some settings on SQL Server Configuration manager namely:

On the Protocols for [Instance_Name] and then Properties (certificates tab)
we had select the certificate we have created for the purpose of encrypted communications.
On that same window (flags tab) we set ForceEncryption option for the Database Engine to Yes

So we started to think... what if the problem is the certificate itself?. Then we went ahead and remove the encryption configuration (the patch for SQL Server Build 10.50.6542 still applied) and the connection started working, confirming that the combination of the patching plus our SSL configuration was bonkers.

After more investigation and via a process of elimination, we narrowed down the problem to the certificates we were using. The certificate was created back then with makecert (to create a self signed certificate) and was NOT using the -a parameter. This parameter tells makecert what Hash algorithm to use. If not provided, makecert will use the default MD5 signature hash algorithm which is not compatible with TLS 1.2.

Also on the TLS 1.2 RFC you can see that explicitly…

MD5
  MD5 [MD5] is a hashing function that converts an arbitrarily long
  data stream into a hash of fixed size (16 bytes).  Due to
  significant progress in cryptanalysis, at the time of publication
  of this document, MD5 no longer can be considered a 'secure'
  hashing function.

So, in order to fix this problem, the solution is to add the -a SHA256 parameter to makecert (make sure you are using makecert version 6.3.9600.17298 and not the old one 5.1313790.0 because that one does not support SHA256 as a parameter).

I'm writing all these since the whole conundrum took me 3 days to figure out and I hope some day someone is going to be benefited from this. If only Microsoft would have included in BIG BOLD letters that MD5 is no longer supported right in the article where the patch is described!

Thanks to @Mr.Brownstone and @sepupic for all their help!


MySQL After Update Trigger apparently not running I have the following After Update trigger on a MySQL table:

CREATE
    DEFINER = 'root'@'localhost'
TRIGGER phpbb_rathermenacing.phpbb_bbdkp_memberlist_AUPD
    AFTER UPDATE
    ON phpbb_rathermenacing.phpbb_bbdkp_memberlist
    FOR EACH ROW
BEGIN
    IF NEW.member_confirmed = 1 AND
        NEW.member_rank_id <> OLD.member_rank_id AND
        ((select min(m.member_rank_id)
            from phpbb_bbdkp_memberlist m
            WHERE m.phpbb_user_id = NEW.phpbb_user_id AND
                  m.member_guild_id = NEW.member_guild_id AND
                  m.member_id != NEW.member_id) >= new.member_rank_id OR
        (select count(m.member_rank_id)
           from phpbb_bbdkp_memberlist m
          WHERE m.phpbb_user_id = NEW.phpbb_user_id AND
                m.member_guild_id = NEW.member_guild_id AND
                m.member_id != NEW.member_id) = 0)
        THEN
            BEGIN
                IF (SELECT count(*)
                      FROM phpbb_user_group ug
                     WHERE ug.group_id =
                                    (SELECT mr.phpbb_group_id
                                       FROM phpbb_bbdkp_member_ranks mr
                                      WHERE mr.guild_id = OLD.member_guild_id AND
                                            mr.rank_id = OLD.member_rank_id) AND
                    ug.group_leader = 0 ) > 0
                THEN
                    UPDATE phpbb_user_group ug
                        SET ug.group_id = (SELECT mr.phpbb_group_id
                                             FROM phpbb_bbdkp_member_ranks mr
                                            WHERE mr.guild_id = NEW.member_guild_id AND
                                                  mr.rank_id = NEW.member_rank_id),
                            ug.user_pending = 0
                        WHERE ug.user_id = NEW.phpbb_user_id AND
                              ug.group_id = (SELECT mr.phpbb_group_id
                                               FROM phpbb_bbdkp_member_ranks mr
                                              WHERE mr.guild_id = OLD.member_guild_id AND
                                                    mr.rank_id = OLD.member_rank_id);
                ELSE
                    IF(SELECT count(*)
                         FROM phpbb_user_group ug
                        WHERE ug.group_id = (SELECT mr.phpbb_group_id
                                               FROM phpbb_bbdkp_member_ranks mr
                                              WHERE mr.guild_id = NEW.member_guild_id AND
                                                    mr.rank_id = NEW.member_rank_id)) = 0
                    THEN
                        INSERT INTO phpbb_user_group (group_id, user_id, group_leader, user_pending)
                               VALUES ((SELECT mr.phpbb_group_id
                                          FROM phpbb_bbdkp_member_ranks mr
                                         WHERE mr.guild_id = NEW.member_guild_id AND
                                               mr.rank_id = NEW.member_rank_id),
                                      m.phpbb_user_id, 0, 0);
                    END IF;
                END IF;
            END;
    END IF;
END

Basically, the trigger is checking to see if one type of group that the user is associated with has been changed, and in the event that it has been changed, it makes an alteration to set a reference for another type of group.

The problem I'm having is that while the trigger saves perfectly fine, nothing seems to happen when I try doing updates.  I can change the rank_id for a memberlist row, but it just updates the row.  I've even tried debugging with dbForge Studio and while it will stop on the UPDATE query I use to actually make the update, stepping in to the UPDATE method does not appear to hit the trigger.

What possibilities could be causing the debugging break points to never be hit and no apparent behavior from the trigger to be seen?

My initial guess would be based on the trigger's flow. Why ?
According to the Book

Chapter 11 Page 251 Paragraph 3 says the following:

The most significant difference between BEFORE and AFTER triggers is that in an AFTER you are not able to modify the values  about to be inserted into or updated with the table in question -- the DML has executed, and it is too late to try to change what the DML is going to do.

From the this statement, this is what I understand: Since the DML is executed and the DML is still spoken of in the future imperfect tense (I am not an English teacher), the new values are really not available to be read in a SELECT since the trigger is not done.
In your case, you are doing an AFTER UPDATE on phpbb_rathermenacing.phpbb_bbdkp_memberlist. With the trigger being 99.99999999% done, you are attempting to do the following in the third clause of the IF statement:
        ((select min(m.member_rank_id)
            from phpbb_bbdkp_memberlist m
            WHERE m.phpbb_user_id = NEW.phpbb_user_id AND
                  m.member_guild_id = NEW.member_guild_id AND
                  m.member_id != NEW.member_id) >= new.member_rank_id OR
        (select count(m.member_rank_id)
           from phpbb_bbdkp_memberlist m
          WHERE m.phpbb_user_id = NEW.phpbb_user_id AND
                m.member_guild_id = NEW.member_guild_id AND
                m.member_id != NEW.member_id) = 0)

You are asking for the count and state of the table phpbb_bbdkp_memberlist that has not yet been changed. Regardless of the Storage Engine, I am very sure you will never get a response because the trigger has to finish in order for the Storage Engine to even think about posting changes to disk.
I have two old posts where I discuss why a trigger is no place for business intelligence:

Aug 11, 2011 : Policies RE database triggers in well-designed applications?
Aug 15, 2011 : Performance of a Trigger vs Stored Procedure in MySQL

SUGGESTIONS

Make that trigger a stored procedure
Remove the AFTER UPDATE trigger
Run the stored procedure after the UPDATE in your application.


Query To Sum Order Totals I have two tables that I need to pull data from.  I need to produce an order total in the query.  Should be straight forward, but I get inaccurate results for anyone who has multiple orders.  This is my DDL

Create Table #first
(
  userid varchar(100)
  ,orderqty int
  ,orderprice float
  ,orderdesc varchar(500)
  ,orderdate datetime
)
Create Table #second
(
  userid varchar(100)
  ,orderstatus varchar(100)
  ,orderreviewstatus varchar(100)
)

Insert Into #first VALUES
('emp12', '14', '10', 'firstorder', '2016-08-11 13:59:23.363')
,('bla', '1', '20', 'red socks', '2016-08-11 13:59:23.363')
,('gra23', '2', '4', 'black coat', '2016-08-11 13:59:23.363')
,('meh33', '5', '2', 'orange socks', '2016-08-11 13:59:23.363')
,('meh33', '3', '1', 'blue trousers', '2016-08-13 13:59:23.363')

Insert Into #second VALUES
('emp12', 'Shipped', 'Reviewed')
,('bla', 'Shipped', 'Reviewed')
,('gra23', 'Shipped', 'Reviewed')
,('meh33', 'Shipped', 'Reviewed')
,('meh33', NULL, NULL)

In this example only userid meh33 has two orders, and this is my query I am using

Select
a.userid
,a.orderqty
,a.orderprice
,SUM(a.orderqty*a.orderprice) As OrderTotal
,a.orderdesc
,a.orderdate
,b.orderstatus
,b.orderreviewstatus
FROM #first a
INNER JOIN #second b
ON a.userid = b.userid
GROUP BY a.userid, a.orderqty, a.orderprice, a.orderdesc, a.orderdate,     b.orderstatus, b.orderreviewstatus

This is the output that this query produces (notice the multiple entries for meh33:

userid  orderqty    orderprice  OrderTotal  orderdesc   orderdate   orderstatus orderreviewstatus
bla 1   20  20  red socks   2016-08-11 13:59:23.363 Shipped Reviewed
emp12   14  10  140 firstorder  2016-08-11 13:59:23.363 Shipped Reviewed
gra23   2   4   8   black coat  2016-08-11 13:59:23.363 Shipped Reviewed
meh33   3   1   3   blue trousers   2016-08-13 13:59:23.363 NULL    NULL
meh33   3   1   3   blue trousers   2016-08-13 13:59:23.363 Shipped Reviewed
meh33   5   2   10  orange socks    2016-08-11 13:59:23.363 NULL    NULL
meh33   5   2   10  orange socks    2016-08-11 13:59:23.363 Shipped Reviewed

And this is my desired output: (one row for each order)

userid  orderqty    orderprice  OrderTotal  orderdesc   orderdate   orderstatus orderreviewstatus
bla 1 20 20 red socks 2016-08-11 13:59:23.363 Shipped Reviewed
emp12 14 10 140 firstorder 2016-08-11 13:59:23.363 Shipped Reviewed
gra23 2 4 8 black coat 2016-08-11 13:59:23.363 Shipped Reviewed
meh33 5 2 10 orange socks 2016-08-11 13:59:23.363 Shipped Reviewed
meh33 3 1 3 blue trousers 2016-08-13 13:59:23.363

What must I alter in my query in order to get my desired result set returned?

You need to change you GROUP BY and SELECT clauses. Also, I'd use Order_ID instead of description to sort.

Select a.userid ,
       SUM(a.orderqty) AS OrderQtyTotal ,
       SUM(a.orderprice) AS OrderPriceTotal ,
       SUM(a.orderqty*a.orderprice) AS OrderTotal ,
       a.orderdesc ,
       MAX(a.orderdate) AS LatestOrderDate ,
       MAX(b.orderstatus) AS LatestOrderStatus ,
       MAX(b.orderreviewstatus) AS LatestOrderViewStatus
FROM #first a INNER JOIN #second b ON a.userid = b.userid
GROUP BY a.userid, a.orderdesc


What's the difference between xp_fixeddrives and this query when i run xp_fixeddrives command to the drive that contains my tempdb, I obtain 10mb of free space, but the following query return other thing (45000 mb free).

other query :

USE tempdb;
GO
SELECT SUM(unallocated_extent_page_count) AS [free pages],
(SUM(unallocated_extent_page_count)*1.0/128) AS [free space in MB]
FROM sys.dm_db_file_space_usage;

this drive only contain my tempdb db.

what is my error?

You're querying two different things here.

xp_fixeddrives will show you the amount of free space on the disk.

Using sys.dm views will tell you how much free space there is in the tempdb data file. If the data file is 50GB, then you're only using 5GB of it and the rest is empty. Even though it's empty, the file exists and is taking up 50GB of space on your drive.


PostgreSQL: COUNT(*) uses a sequential scan, not index Why does PostgreSQL sequentially scans the table for COUNT(*) query, while there is a very small and indexed primary key?

The official wiki pages give an answer to that:

  [...] The reason why this is slow is related
  to the MVCC implementation in
  PostgreSQL. The fact that multiple
  transactions can see different states
  of the data means that there can be no
  straightforward way for "COUNT(*)" to
  summarize data across the whole table;
  PostgreSQL must walk through all rows,
  in some sense. This normally results
  in a sequential scan reading
  information about every row in the
  table. [...]

Furthermore, you could try an ANALYZE to rebuild the info for the query planer.

You should get a better performance using COUNT(an uniquly indexed field) but if this is very big, a seq scan is the only way to do it.

If you need very quick numbers and are not afraid of querying the schema, you can do the following

SELECT reltuples FROM pg_class WHERE oid = 'your_table'::regclass

But don't rely on this values as it is only an "estimated" (although often the exact) number of tuples in the table.


Booleans, CONSTANTS or mapping table for 'status'-like fields? I am modelling a User table which needs to have the following information about the users:

is_active?
is_detained?
has_voluntarily_deactivated?
is_temporarily_suspended?

and so on...

Basically, these are boolean flags with true or false. So, I am considering few approaches other than boolean flags which are as follows:

Create a single varchar field with values like 'active', 'detained', 'deactivated', 'suspended', etc.
Create a tinyint field and map the integers to another table containing status strings
Create a tinyint field and map the integers in code itself using constants, such as ACTIVE = 1, DETAINED = 2, etc. Is Python's enum type the best solution to this?
Create a tinyint field and map the integers to status strings in an XML or JSON file

Which of the above 4 or the original boolean style approach is preferable, or if there could be a completely different approach or a modified version of the above approaches, please let me know?

Also, in my code, how should I call these fields, like:

if (user.status == 1), or something like
if (user.status == STATUS.ACTIVE), or
if (user.status == 'active')

(I think this will depend on which approach I follow)

These status values are not limited and may be added, edited or removed in future. Request you to answer in a database agnostic way and the programming language that I am using is Python.

Thank you for your answers

In MySQL, you could use ENUM or SET, depending on whether the states are mutually exclusive.  That is, can it be both 'active' and 'detained'?

ENUM('active', 'detained', 'deactivated', 'suspended')

or

SET('active', 'detained', 'deactivated', 'suspended')


mongorestore to --oplogReplay or not to --oplogReplay? The MongoDB documentation on --oplogReplay is a bit vague. It just keeps saying a "point in time" snapshot. How is this different from dropping the sink database and restoring from the source?

What are the pros and cons of using --oplogReplay?

An OpLog is like a transaction log, thus if you had a 'full' backup of the system but wanted to get incremental data after the full backup was taken you can replay the opslog to get a 'point in time' restore of the database.  It let's you have more control on being able to address at exactly what point you want to restore everything.  Let's say someone dropped a critical collection at 12:05:23.  You could do a opslog replay up to 12:05:22 for example.


How can I have a gap in the SQL Server Job Agent history for a job? I have a job (Update Tickets) scheduled in Job Agent with a single schedule of "Occurs every day every 30 minute(s) between 12:00:00 AM and 11:59:59 PM. Schedule will be used starting on 11/4/2013." When I use Log File Viewer to see the history for this job, I see no entries for a 2.5 hour period yesterday. Other jobs on the same server ran during the time this one job had the gap in executions.

The job does an INSERT from SELECT from an OpenQuery from a Linked Server. If there was a network or other issue that prevented the connection to the data source from succeeding, I would still expect to see a job failure message in the log, but I see no entries for it at all. Neither the SQL Server log, SQL Server Agent log, nor the Windows System or Application logs show anything relevant during the time of the gap.

What are the possible causes of this?

Thanks,

Mark

Check the duration of the job prior to the first slot it "missed." Typically job history won't have gaps unless:

The previous instance of the job is still running - SQL Server knows not to start two copies of the same job concurrently, so it just skips that slot.
The job was disabled during that time.
Someone deleted the history from msdb.dbo.sysjobhistory.


What is the preferred method to roll out updates to hundreds of databases I am trying to determine the best way to roll out SQL scripts to hundreds of distributed servers.  I have a slow SSIS package and a very fast OSQL/BATCH process.

I am on a project where we have several hundred database servers deployed in the field.  I need to be able to routinely query the servers and/or apply database upgrade scripts.

When we only had 20 servers, I built an SSIS package to grab a list of servers and process them one at a time.  The SSIS package works great, has tons of error handling and logging, etc.  It is able to capture reult sets from each server and store them in a central location for further analysis.  Due to the way I built it, however, it processes each server serially.  20 Servers takes about 10 minutes to process.  I am at a point where this method is simply too slow.

Before I invest more time into re-writing this package to spawn other packages (one for each server), I was hoping to get some advice from others out there who faced a similar situation.

Would you use SSIS in this manner, or switch to powershell, or even OSQL?

Almost as a joke, I dug through my archives and pulled out a batch process I wrote 12  years ago to apply scripts on remote servers.  I made a few tweaks (changed to OSQL) and it runs incredibly fast.  I can process 100 servers in under 20 seconds.  I launch it from a windows 7 machine, which does not choke on that many command windows being opened.

The downside to the OSQL/BATCH solution is that the batch file is hard-coded with servernames and passwords.  But it is so fast I will continue to use it until I find a better solution.

SSIS Package - The current SSIS package does the following:
 - connect to a central server
 - retrieve a list of servers that need to be queried
 - using a forloop container, connects to each server (in series) and executes the query
 - if the query returns a result set, this information is captured and stored at central server
 - the central server is updated to reflect success/fail of each server
 - all errors are logged at the central server

I would adapt your fast solution to something more easily extensible, like PowerShell.

It would be trivial to have PS read a text file for a list of servers to run against, and another file or files for what commands to execute.

You could also add error handling and such like in your SSIS batch, and have it fire asynchronously so it would work on all your servers simultaneously.


SQLCMDMODE from SSMS. Can I set -b mode when using :r to include files? As far as I read msdn there is no variable that corresponds to the -b flag of the command (on error batch abort).
I found no to enable this mode inside a script when running in sqlcmd mode.
Is there any to force
:r filename
to run in this mode, when called from SSMS?

No, you can't.

Not in the supported subset of options: http://msdn.microsoft.com/en-us/library/ms174187.aspx


Is there a sql database engine that support "specific value duplicate restriction"? For example, in the brand column of a product database, the brand value "unknown" should only exist once while other brand values can exist many times.

Is there a sql database engine that supports it? or should I do it in application layer?

Some DBMSes support indexes with a WHERE-condition

CREATE unique INDEX OnlyOneUnkownBrand
ON product (brand)
WHERE brand = 'unknown';


SQL Server Storage sql_variant USE tempdb ;
GO

DROP TABLE tbl ;
GO

CREATE TABLE tbl
(
    i SQL_VARIANT NOT NULL
) ;
GO

INSERT INTO tbl (i)
    VALUES (1) ;
GO

SELECT i FROM tbl ;
GO

DBCC IND ('tempdb','tbl',-1) ;
GO

DBCC TRACEON (3604) ; -- Page dump will go the console
GO

DBCC PAGE ('tempdb',1,157,3) ;
GO

Record Size = 17B
30000400 01000001 00110038 01010000 00
TagA = 0x30       = 1B
TagB = 0x00       = 1B
Null Bitmap Offset    = 0x0004    = 2B
Column Count      = 0x0001    = 2B
Null Bitmap       = 0x00      = 1B
Variable-Length Columns Count = 0x0001    = 2B
Variable-Length Column Offset Array  = 0x0011     = 2B
This is the sql_variant encoding for integers...I guess = 0x0138 = 2B
Our integer column    = 0x00000001 = 4B

    SELECT
      SQL_VARIANT_PROPERTY(i , 'BaseType') AS BaseType      -- SYSNAME NVARCHAR(128)
    , SQL_VARIANT_PROPERTY(i , 'Precision') AS [Precision]  -- INT
    , SQL_VARIANT_PROPERTY(i , 'Scale') AS Scale            -- INT
    , SQL_VARIANT_PROPERTY(i , 'TotalBytes') AS TotalBytes  -- INT
    , SQL_VARIANT_PROPERTY(i , 'Collation') AS [Collation]  -- SYSNAME NVARCHAR(128)
    , SQL_VARIANT_PROPERTY(i , 'MaxLength') AS MaxLength    -- INT
FROM
      tbl ;
GO

My questions:

0x3801...what is that
i = 328792402 Huh?  Where did this come from?
SQL_VARIANT_PROPERTY() says I have an integer. Why does it not choose tinyint?
Does anyone know where SQL_VARIANT_PROPERTY() can be found. Must I use the DAC to get at it?

The first 2 answers are from the SQL Server Internals Book p.278

0x38 is 56 in decimal. This indicates int in sys.types (system_type_id column)
0x01 is the version number of the sql_variant format (always 1 in SQL Server 2008)
This is the way the literal 1 is always interpreted in SQL Server. e.g. SELECT 1 AS foo INTO NewTable will create a new column of integer datatype. Use an explicit cast if you want it to be treated as a different datatype.
This is part of the product source code. You cannot view the definition.

BTW: If you are looking at other datatypes as well you may encounter some additional bytes between the version number and the column value as follows.

numeric/decimal have 1 byte each for precision and scale.
[n][var]char have 2 bytes for max length and 4 bytes for collation id.
[var]binary have 2 bytes for max length.


Downgrading SQL Server 2008 R2 Standard to Web Edition I have to downgrade a SQL Server running SQL Server 2008 R2 Standard Edition SP1 to 2008 R2 Web Edition SP1.

I discovered this question on Server Fault:

  Can I downgrade SQL Server 2008 R2 Standard To Web Edition?

Which links to a similar scenario:

  Downgrading from SQL Server 2008 R2 Enterprise Edition (trial) to R2 Web Edition (licensed)

This server doesn't have Analysis Services installed and I tried to run the Edition "Upgrade" tool and got this failure:

I checked the detailed report and all that it says is:

Rule Name: EditionUpgradeMatrixCheck
Rule Description: Checks whether the selected instance of SQL Server meets upgrade matrix requirements.
Result: Failed
Message/Corrective Action: The selected SQL Server instance does not meet upgrade matrix requirements.

Strangely the Edition Downgrade rule has passed.

I ran this from the SQL 2008 R2 Web Edition DVD.

Can anyone guide me through downgrading this SQL server to Web Edition, I am now at a loss.

Unfortunately looking at the MS matrix you cannot do this.  You were able to earlier because evaluation versions don't have that lock, but once you go full edition it's locked out

http://msdn.microsoft.com/en-us/library/ms143393%28v=sql.105%29.aspx

You could try an approach taken here but proceed with extreme caution.
http://skreebydba.com/2012/11/16/downgrading-sql-server-enterprise-to-standard-with-scripts/

[edit]:
This URL has the scripts to recreate logins, etc. that Aaron pointed out
Just a headsup though, this doesn't script out jobs.  If you restore MSDB to recreate jobs, make sure to

UPDATE MSDB..SysJobs
SET Originating_Server = 'SQLServerName'

It will also work for downgrades to web.


Can you use "RENAME" instead of "FLASHBACK TABLE" to restore a table in Oracle? Just a general question about flashback and the recycle bin in Oracle.  I tried doing this on a demo oracle database I have but the recycle bin does not seem to want to be populated; and I want a quick answer.

One can do the following:

flashback table table_name to before drop;
alter index "BIN$U/9fvJKaASzgQKjAYAIWhw==$0" rename to sys_c1113050;

Can you do this then, to restore a table:

RENAME TABLE BIN$U/9fvJKaASzgQKjAYAIWhw==$0 TO TABLE_NAME

No you cannot. To restore a table from the recycle bin use flashback table <table_name> to before drop;

<table_name> is the original table name and not the object_name from the recycle bin.

Do not rename the table to restore it from the recycle bin!


How to find MYSQL Logs for Drop Delete Record? I am using SQlyog Ultimate for Mysql GUI. I want to know who is deleting, Dropping table and record. I want to track the User Ip and Name.

If there is any method to track please share with me.

I found some Link but not able to understand
How to show the last queries executed on MySQL?

Thanks
Sitansu

You must enable query logging on server as described on the official page:

https://dev.mysql.com/doc/refman/5.7/en/server-logs.html

Here general logging is the easiest approach that can be used to resolve your objective. Link shared by you shows two different methods of achieving the same. You can use following queries as root user to get what you want

SET GLOBAL log_output = 'TABLE';
SET GLOBAL general_log = 'ON';

select event_time,user_host,argument
from mysql.general_log
where command_type='Query' and
upper(argument) rlike '(DROP|DELETE|TRUNCATE)+';

Beware that it will fill server disk space very faster.


Query to extracting the 14th date from a date after excluding the sunday and saturday in Postgresql I need a query to extract the 14th day or any other day before a day after excluding Saturday and Sunday in Postgresql.

For Example, I need 14th day before 2020-01-30 after excluding Saturday and Sunday.

Date Dimension  is " YYYY-MM-DD"

I am using 'ISODOW' < 6 condition to exclude Sunday and Saturday. But from the count,  i am not sure how to fetch the date

 extract('ISODOW' FROM the_day) < 6

WITH cand AS (
   SELECT DATE '2020-01-30' - i AS d
   FROM generate_series(1, 2*14+3) AS i
)
SELECT d
FROM cand
WHERE extract(isodow FROM d::timestamp) < 6
ORDER BY d DESC
OFFSET 13
LIMIT 1;

The 2*14+3 in the CTE is a safe upper limit and can be improved.


DISTINCT COUNT with GROUP BY query is too slow despite indexes I have the following query that counts the number of vessels in each zone for each week:

SELECT zone, DATE_FORMAT(creation_date, '%Y%u') AS date, COUNT(DISTINCT vessel_imo) AS vessel_count FROM vessel_position
WHERE zone IS NOT NULL
AND creation_date >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH)
GROUP BY zone, date;

The table has about 40 million rows. The execution plan for this is:

+----+-------------+-----------------+------------+-------+--------------------+------+---------+------+----------+----------+------------------------------------------+
| id | select_type | table           | partitions | type  | possible_keys      | key  | key_len | ref  | rows     | filtered | Extra                                    |
+----+-------------+-----------------+------------+-------+--------------------+------+---------+------+----------+----------+------------------------------------------+
|  1 | SIMPLE      | vessel_position | NULL       | range | creation_date,zone | zone | 5       | NULL | 21190904 |    50.00 | Using where; Using index; Using filesort |
+----+-------------+-----------------+------------+-------+--------------------+------+---------+------+----------+----------+------------------------------------------+

Columns vessel_imo, zone and creation_date each indexed. Primary key is the composite key (vessel_imo, creation_date).

When I look at the query profile, I can see that a large amount of time i spent doing Creating sort index.

Is there anything I can do to improve this query further?

You'll need no three separate indexes, but one combined (covering) index on  creation_date, zone, vessel_imo (in this column order)

I don't know, how exactly MySQL works on this point, but it could help, when you would group by creation_date instead of the (calculated) date. On the other hand, when creation date contains the time and you do not output the time, then you have to group by by the (calculated) date.


SQL on a VM. Backup to Network location or just backup to VM I'm used to working with physical servers so interested in your thoughts/experience on this.

I'm now working in a VM environment and wondering how you guys handle SQL backups?  I understand best practice is to backup to a network share when using physical devices, but does this still apply to VM's?  VM's obviously use shared storage so not sure of the point of backing up to a network share.

Anyone any thoughts on this?

Thanks in advance!

You should still backup away from the VM, or at least copy the backups off, preferably to a redundant location (either another SAN, or tape, whatever).

VMs can come back from a lot of things, but if you suffer a catastrophic failure, you may not be able to access the VM storage easily.

C: drive fills up
Windows dies a horrible death
A patch goes wrong at some level (VM, Windows, etc.)

Backing up to a VM-agnostic location gives you some breathing room. While the SAN may still be a single point of failure (remember the redundant location thing?), if you need to stand up a new VM in an emergency, you want the backups to be as easy to restore as possible.


How do indices impact query performance? Obviously keeping several different indices has a negative impact on insert and delete performance. How about query performance: Does it make sense at all keeping too many indices on a table? Will the query performance improve in any case with an index added (of course for queries using the index at all) or is it even possible that the query performance will degrade with too many indices because it becomes necessary to consult all the indices to get the result?

In case there are different indices on a table: will they all be considered, or only the best from the optimizer point of view? Does Oracle implement multi-dimensional indices?

It will take marginally longer to generate the plan when there are more indexes to consider, but I doubt the difference would be measurably significant.  The reasons for dropping an index does not list query performance.  On the other hand, in general you shouldn't create indexes unless you know they will be used to make a query more efficient.

From the Oracle Concepts Guide, here are the criteria for creating an index.

  In general, consider creating an index
  on a column in any of the following
  situations:

  The indexed columns are queried frequently and return a small
  percentage of the total number of rows
  in the table.
  A referential integrity constraint exists on the indexed column or
  columns. The index is a means to avoid
  a full table lock that would otherwise
  be required if you update the parent
  table primary key, merge into the
  parent table, or delete from the
  parent table.
  A unique key constraint will be placed on the table and you want to
  manually specify the index and all
  index options.

All indexes will be considered in the sense that all indexes on the tables in the query are examined to determine whether they could be used.  Those that could be are further examined to determine usefulness.


Is there any MongoDB handler for find event? I want to know is there any handler or trigger kind of functionality exist in MongoDB which can help me to run my mongo command on find query?

Basically, I want to run a specific set of Mongo queries on find query. You can say a listener on find query to run other mongo commands.

Other than that is there anything like create a view from a remote database, I know MongoDB provides creating a view from the same database but I want from the remote database?

I do not want any application level solution like any third party module or watcher script, I want something which is within the MongoDB itself like inbuilt function or any event handling.

I have tried auditing but, it only records the operation and all, does not provide any handler through which I can run my mongo commands like Oracle providing in Fine-grained auditing.

Any help related to this is appreciable, Thanks in advance.

I want to know is there any handler or trigger kind of functionality exist in MongoDB which can help me to run my mongo command on find query?

  Basically, I want to run a specific set of Mongo queries on find query. You can say a listener on find query to run other mongo commands.

As at MongoDB 3.4, there is no server-side functionality for query listeners or triggers. There are some open feature suggestions for triggers in the MongoDB Jira issue tracker, but these all appear to be associated with operations that update data (insert, update, delete) rather than find queries.

  Other than that is there anything like create a view from a remote database, I know MongoDB provides creating a view from the same database but I want from the remote database?

As at MongoDB 3.4, there is no in-built feature for creating views from remote databases or deployments.

  I do not want any application level solution like any third party module or watcher script, I want something which is within the MongoDB itself like inbuilt function or any event handling.

Although you've excluded third party solutions, there are currently no built-in features or feature requests matching your description. You could certainly raise one or more feature requests with a description of the use cases, but I think both of these approaches sound more suitable for implementing in an application layer with your business logic rather than embedding that into a distributed database.

As a more general solution to enable the use cases you've described, I suggest putting an API layer between your applications and your MongoDB deployment. The API layer could intercept queries and perform the desired actions based on your use case, and is an option available to you without any dependencies on MongoDB server features.

  I have tried auditing but, it only records the operation and all, does not provide any handler through which I can run my mongo commands like Oracle providing in Fine-grained auditing.

The auditing feature (which is part of MongoDB Enterprise) is only intended to record configured operations of interest. If you want to do special processing based on queries you will have to implement this in your own application code.


SQL Server : KEY lock prevents FK on nearby row, but not SELECT I have the following table structure (simplified, pseudocode):

TABLE dbo.Users (Id int NOT NULL IDENTITY PRIMARY KEY)
TABLE dbo.Transfers (
    Id int NOT NULL IDENTITY PRIMARY KEY,
    CreatedByUserId NOT NULL FOREIGN KEY REFERENCES dbo.Users (Id)
    ... -- no other user-related fields
)

I have the following situation in application code:

-- Given a User with Id 5 that already exists
BeginTransaction
1. INSERT INTO dbo.Users (...) VALUES (...) -- Creates user with Id 6
    NotInTransaction.Start
    2. INSERT dbo.Transfers (CreatedByUserId, ...)  VALUES (5, ...)
    NotInTransaction.End
...
CommitTransaction

In this specific case (when existing user and newly created user have nearby Ids), I get a timeout from step 2.

Now the interesting part is that I paused the application on step 2, and tried the following things outside the (paused) transaction:

SELECT * FROM dbo.Users WHERE UserId = @UserId
works (not locked) for all values of @UserId except 6, which makes total sense
INSERT dbo.Transfers (CreatedByUserId, ...)  VALUES (@UserId, ...)
works for 1,2,3,4, locked for 6 and 5

I did a quick look on sys.dm_tran_locks (which I am not a master of) with some hobt joins, and when INSERT is locked, it shows

ObjectName | resource_type | resource_description | resource_associated_entity_id | request_mode | request_status
Users      | KEY           | (b9b173bbe8d5)       | 72057594047299584             | X            | GRANT
Users      | KEY           | (b9b173bbe8d5)       | 72057594047299584             | S            | WAIT

Now, the question is: why can I select user 5 from outside the transaction, but not insert into a table that references it through FK?

Your simplified repro simplified the problem beyond the point it repro...

create TABLE dbo.Users (Id int NOT NULL IDENTITY PRIMARY KEY)

create TABLE dbo.Transfers (
    Id int NOT NULL IDENTITY PRIMARY KEY,
    CreatedByUserId int NOT NULL FOREIGN KEY REFERENCES dbo.Users (Id))

insert into Users default values;
go 5

insert into transfers (CreatedByUserId) values (1), (2), (3), (4);
go

begin tran
insert into Users default values;
insert into transfers (CreatedByUserId) values (6);

-- from separate connection
insert into transfers (CreatedByUserId) values (5);

Since the problem no longer reproes it means your simplified out a critical piece of information. Your symptoms look suspiciously similar to serializable isolation level issues. Perhaps you're using the harmful default TransactionScope constructor?


Incomplete installation with unsupported locale I was installing postgresql on Ubuntu 12.04 (Digital Ocean droplet). The locale variables in my environment gets transported to the DO box.

When postgres is installed via apt-get install postgresql, it appears to succeed. However no default cluster is created, and postgresql is not added as a service. This happens silently. I realised what was going on when attempting to run pg_createcluster manually failed.

Some questions arising from this:

how can I be alerted that the install hooks aren't being run?
after fixing the locale, I removed (and purged) the postgresql package; however the default cluster still wasn't created. What was preventing this? How can I ensure that install hooks are being run?

Install the package locales-all and your postgresql will start up.


memory errors on startup in postgresql 9.6 log - MAP_HUGETLB failed On postgresql startup, I see this in the log files

  2017-07-17 05:21:01.743 UTC  > LOCATION:  CreateAnonymousSegment, pg_shmem.c:484
  2017-07-17 05:21:01.868 UTC  > LOG:  00000: redirecting log output to logging collector process
  2017-07-17 05:21:01.868 UTC  > HINT:  Future log output will appear in directory "pg_log".
  2017-07-17 05:21:01.868 UTC  > LOCATION:  SysLogger_Start, syslogger.c:622
  2017-07-21 05:46:01.680 UTC  > DEBUG:  00000: logger shutting down
  2017-07-21 05:46:01.680 UTC  > LOCATION:  SysLoggerMain, syslogger.c:490
  2017-07-21 05:46:01.973 UTC  > DEBUG:  00000: mmap(8264876032) with MAP_HUGETLB failed, huge pages disabled: Cannot allocate memory

I have never used huge pages - turning on huge_pages in postgres.conf does not help, I get different errors and the server will not start. I am unfamiliar with these settings.

Please help point me in the right direction to explore why these errors are occurring and what I should do about them - adjustments to memory settings, logging (it is set very high) vacuuming, or what?

What parts of the PostgreSQL manual are relevant?

Stack:  postgresql 9.6 on aws ec2 generic linux

UPDATE:  This post was originally written about things I noticed on installing a backup utility, and how that appeared to be causing errors, then I noticed a server without the utility is logging the same messages.

Own experience suggests that this is due to PostgreSQL running out-of-memory.

For instance, I ran into this error when attempting to bump max_connections up to a very high value (say, 2000) on a "small" server.


Choice of authentication approach for financial app on PostgreSQL First some background.

The LedgerSMB project is an open source financial accounting software project that runs on PostgreSQL.  We implement a very large amount of business logic in user defined functions, which act as the main mapping tool between program object methods and database behavior.  Currently we use database users as authentication users, partly by choice (this allows centralized security logic, so that other tools can be written and re-use permissions given to users), and partly by necessity (after we forked from SQL-Ledger, there weren't many options for retrofitting security onto that codebase).

This buys us access to a reasonable number of single signon options that PostgreSQL has access to, from LDAP to Kerberos 5.  We can even use PAM where passwords are concerned.  It also allows us to re-use permissions when integrating with other applications, or allowing other client interfaces.  For a financial accounting application this seems like a net win.

There are obvious costs involved.  For the web application, we are very limited to the types of http auth that can be supported.  DIGEST for example is entirely out.  BASIC works, and we could implement KRB5 easily enough (I plan on having this supported and working out of the box for 1.4).  Very strong authentication measures cannot be properly managed on this directly although we could probably shim them in if necessary (for example BASIC + client-side SSL cert with a cn matching the user name and a specific root ca).

At the same time we've run into a fair amount of criticism mostly from the development crowd and more occasionally from dba's who tell me that the application should be the security barrier, not the database.  My view is still that a smaller security perimeter is generally better, that the re-use of business logic and security logic go together, and that it strikes me as dangerous to re-use business logic without re-using security logic on the same level of the program.

Am I missing any major tradeoffs here?  Are there gotchas that I am not considering?

I think you're conflating authentication and authorization.

I completely agree that keeping the security model in the DB is wise, especially as LedgerSMB is designed with access from multiple clients in mind. Unless you plan on going 3-tier with a middleware layer it makes perfect sense to have users as database roles, especially for something like an accounting app.

This does not mean you have to authenticate users against the database using a PostgreSQL-supported authentication method. Your database users, roles and grants can be used for authorization only if you like.

Here's how it works for a web ui for example:

jane connects to web ui server and authenticates using whatever method is desired, say HTTPS X.509 client certificate handshake and DIGEST auth. The server now has a connection from a user it accepts is really jane.
The server connects to PostgreSQL using a fixed username/password (or Kerberos or whatever you like), authenticating its self to the db server as the user webui. The db server trusts webui to authenticate its users so webui has been given appropriate GRANTs (see below).
On that connection the server uses SET ROLE jane; to assume the authorization level of the user jane. Until RESET ROLE; or another SET ROLE is run, the connection is operating with the same access rights as jane and SELECT current_user() etc will report jane.
The server maintains the association between the database connection on which it has SET ROLE to jane and the web session for user jane, not allowing that PostgreSQL connection to be used by other connections with other users without a new SET ROLE inbetween.

You are now authenticating outside the server, but maintaining authorisation in the server. Pg needs to know what users exist, but doesn't need passwords or authentication methods for them.

See:

SET SESSION AUTHORIZATION
SET ROLE
GRANT

Details

The webui server controls the queries run, and it's not going to let jane run raw SQL (I hope!) so jane can't RESET ROLE; SET ROLE special_admin_user; via the web ui. For added safety I'd add a statement filter to the server that rejected SET ROLE and RESET ROLE unless the connection was in or entering a pool of unassigned connections.

You're still free to use direct authentication to Pg in other clients; you can mix and match freely. You just have to GRANT the webui user the rights to SET ROLE to users who can log in via the web and then give those users any normal CONNECT rights, passwords, etc you want. If you want to make them web-only, REVOKE their CONNECT rights on the database (and from public).

To make such an authentication/authorisation split easy I have a special role assume_any_user that I GRANT every newly created user to. I then GRANT assume_any_user to the real username used by things like a trusted web front-end, giving them the rights to become any user they like.

It's important to make assume_any_user a NOINHERIT role, so the webui user or whatever has no privilges by its self and can only act on the database once it's SET ROLE to a real user. Under no circumstances should webui be a superuser or DB owner.

If you're connection pooling, you can use SET LOCAL ROLE to set role only within a transaction, so you can return connections to the pool after COMMIT or ROLLBACK. Beware that RESET ROLE still works, so it still isn't safe to let the client run whatever SQL they want.

SET SESSION AUTHORIZATION is the related but stronger version of this command. It doesn't require role membership, but it's a superuser only command. You don't want your web ui connecting as a superuser. It can be reversed with RESET SESSION AUTHORIZATION, SET SESSION AUTHORIZATION DEFAULT or SET SESSION AUTHORIZATION theusername to regain superuser rights so it isn't a privilege-dropping security barrier either.

A command that worked like SET SESSION AUTHORIZATION but was irreversible and would work if you were a role member but not superuser would be great. At this point there isn't one, but you can still separate authentication and authorisation pretty well if you're careful.

Example and explanation

CREATE ROLE dbowner NOLOGIN;
CREATE TABLE test_table(x text);
INSERT INTO test_table(x) VALUES ('bork');
ALTER TABLE test_table OWNER TO dbowner;

CREATE ROLE assume_any_user NOINHERIT NOLOGIN;
CREATE ROLE webui LOGIN PASSWORD 'somepw' IN ROLE assume_any_user;

CREATE ROLE jane LOGIN PASSWORD 'somepw';
GRANT jane TO assume_any_user;
GRANT ALL ON TABLE test_table TO jane;

CREATE ROLE jim LOGIN PASSWORD 'somepw';
GRANT jim TO assume_any_user;

Now connect as webui. Note that you can't do anything to test_table but you can SET ROLE to jane and then you can access test_table:

$ psql -h 127.0.0.1 -U webui regress
Password for user webui:

regress=> SELECT session_user, current_user;
 session_user | current_user
--------------+--------------
 webui        | webui
(1 row)

regress=> SELECT * FROM test_table;
ERROR:  permission denied for relation test_table

regress=> SET ROLE jane;
SET

regress=> SELECT session_user, current_user;
 session_user | current_user
--------------+--------------
 webui        | jane
(1 row)

regress=> SELECT * FROM test_table;
  x
------
 bork
(1 row)

Note that webui can SET ROLE to jim, even when already SET ROLEd to jane and even though jane hasn't been GRANTed the right to assume the role jim. SET ROLE sets your effective user ID, but it doesn't remove your ability to SET ROLE to other roles, that's a property of the role you connected as, not your current effective role. Consequently you must carefully control access to the SET ROLE and RESET ROLE commands. There is, AFAIK, no way to permanently SET ROLE for a connection, truly becoming the target user, though it'd certainly be nice to have.

Compare:

$ psql -h 127.0.0.1 -U webui regress
Password for user webui:

regress=> SET ROLE jane;
SET

regress=> SET ROLE jim;
SET
regress=> SELECT session_user, current_user;
 session_user | current_user
--------------+--------------
 webui        | jim
(1 row)

to:

$ psql -h 127.0.0.1 -U jane regress
Password for user jane:

regress=> SET ROLE webui;
ERROR:  permission denied to set role "webui"
regress=> SET ROLE jim;
ERROR:  permission denied to set role "jim"

This means that SET ROLE isn't exactly the same as logging in as a given role, something you must keep in mind.

webui can't SET ROLE to dbowner since it hasn't been GRANTed that right:

regress=> SET ROLE dbowner;
ERROR:  permission denied to set role "dbowner"

so by its self it's pretty powerless, it can only assume the rights of other users and only when those users have web access enabled.


Unique constraint on different columns I'm trying to make a table to store the edges of a directed graph, but want to avoid duplicate edges in both directions.
I can of course make it one direction :

CREATE TABLE edge (parent integer, child integer, UNIQUE(parent, child));

But how can I make sure that I also don't allow a new (parent, child) that already exists as (child, parent) ?

Any ideas would be greatly appreciated !

Using GREATEST and LEAST

Essentially, if we can sort of the columns in the index, we can ensure that the duplicate entry causes a collision and a constraint violation this is because sort(5,2) = sort(2,5). Because there is no sort, we use greatest and least.

CREATE TABLE edge (
  parent int,
  child  int
);

CREATE UNIQUE INDEX
  ON edge ( greatest(parent,child), least(parent,child) );

INSERT INTO edge(parent,child)
VALUES (42,7), (42,9), (5,7);

INSERT INTO edge(parent,child)
VALUES (7,42);
ERROR:  duplicate key value violates unique constraint "edge_greatest_least_idx"
DETAIL:  Key ((GREATEST(parent, child)), (LEAST(parent, child)))=(42, 7) already exists.


Sybase to SQL Server Migration What is the fastest/best approach to load data from SYBASE to SQL server DB. I have tables with 500 million records. I tried using SSMA and BCP, both are taking over 24hrs. I tried using datastage (etl) methods but not of much help.
Thanks.

Create Linked server which fetches data from Sybase to SQL. Source Creating Linked server to SYBASE from SQL Server
  Use ODBC connections to create an SSIS package which will fetch the data from SYBASE database onto SQL server. Check this Extracting data
  from Sybase SQL Anywhere using SSIS through ODBC. Then Schedule
  the above created package via SQL agent job to update data on SQL
  server as per schedule which meets the requirement.


How to get percentage after grouping? I have a table like this:
person_id food   data
1         bread   xxx
1         bread   xxx
1         fruite  xxx
2         bread   xxx
2         fruite xxx

food can have only three values (bread, fruits), I want to get the percentage of bread each person has eaten, and there percentage of fruits each person has eaten.
I tried to do:
select count(*) from table
group by person_id, food

and that gives me:
player_id, food, number

but how can I continue from now?
I am on hadoop

select person, t1.food, COUNT() total, COUNT() 100 / fcnt "Perc %"
from food_table t1, (select food, COUNT() fcnt from food_table group by food) testsum
where t1.food = testsum.food
group by t1.person, t1.food, fcnt
order by 1
Output
person  food    total   Perc % of total
1       bread   2       66
1      fruit    1       50
2      bread    1       33
2      fruit    1       50


Would installing 32 bit packages on the existing 64-bit VM be an option to run Oracle 9.2? I inherited an old old 9i DB that now needs recovery.  It is unstable (disk corruption and performance problems).

I have a backup.

In the interest of time - I have 64-bit VMs all over the place, but 9i was only made for 32-bit according to Oracle support.

Would installing 32 bit packages on the existing 64-bit VM be an option to run Oracle 9.2?

It is possible, I had to do it myself once.
BUT: It is not enough to just to install 64 bit packages on any new linux distribution. You need a supported version (that is supported by 9.2).
just hacking the config file did not work out, due to many issues e.g. Kernel versions.

IIRC I ended using an Oracle Linux 4 install.

If you need a more modern and supported OS you might want to give Solaris x86 a try. Usually old software runs with much less issues. Known good installation: Solaris 10 with 9.2


How to prevent RID lookup? I have a table that stores time-series data and has about 8 million rows. The table structure is as follows:
Timestamp | ComponenentID | Parameter1 | Parameter2 | Parameter3
I have a clustered index on the Timestamp and ComponentID column. (This is also my primary key)
The query I am trying to run is:-
SELECT * FROM table
WHERE Timestamp BETWEEN '2020-01-01'
AND '2020-01-02'
AND ComponentId = 5

When I run this query an RID lookup seems to be taking place. I have read that this only happens when the index does not cover everything but since I have a clustered index I thought it should itself cover everything and prevent the lookup. How do I prevent this lookup from happening?
Query 1 (Timestamp+ComponentId):-

Query 2 (Timestamp):-

RID Lookups occur on a heap data structure in SQL Server (as opposed to a B-Tree). This occurs when a non-covering nonclustered index is used to fetch the data and it needs to lookup the remaining fields it's missing. Your table data is stored in a heap when there is no clustered index on that table (as the clustered index defines the ordering the records are sorted into a B-Tree normally).
If your table had a clustered index and this query wanted to use the same nonclustered index it's currently using, you'd see a Key Lookup operation in the execution plan instead.
If you created a clustered index on your table for the two fields Timestamp and ComponenentID then that'll be covering for your query and you should see the clustered index used in the execution plan instead which will eliminate any kind of additional lookup operation.

Regarding your second question in the comments, based on your recent comment updates, it sounds like the difference in runtime you're seeing is due to the first run pulling the data into memory from disk (which is generally the most bottlenecking part of the process, from a hardware perspective) and the second run leveraging the existing data in memory.
Depending on how big your Table and Page Sizes are, this normally shouldn't be too much of a concern (based on the number of rows I see your query is returning from its execution plan). All subsequent runs of the query (while the data is still in memory) will have optimal performance.
If the initial run to pull the data off the disk becomes a problem then you can either look into Compression or analyze if you can upgrade your disks to something faster (not sure if you're still on a mechanical hard drive currently, and can switch to an SSD or even better is an NVMe).
I'll also add a final note that was made in the comments that is true and related but not the root issue itself, that your query which does additional filtering on ComponentId results in more data being read. This is evident in your IO statistics screenshots if you compare the grand totals for Logical Reads between the two queries. The one query filtering on Timestamp and ComponentId results in 396 Logical Reads whereas the other query filtering on just Timestamp only results in 90 Logical Reads. Logical Reads are the number of 8 KB Pages that are read from memory. Physical Reads are the number of 8 KB Pages that were read from the disk. It's not a huge difference, but would account for a small difference in time between running the first query vs the second query, on their first runs. This becomes a moot point on subsequent runs of both queries when the data is already in memory from disk (as discussed in my previous few paragraphs).


AWS RDS Postgres pg_hba.conf auth-method I'm starting to use Amazon Web Service's Relational Database Server product (AWS RDS) and want to confirm I have not made a security blunder.

My configuration:

Postgres
Publicly accessible
Inbound rule on security group added for "PostgreSQL / My IP"

These options enable me to use pgAdmin to connect and populate data.  I've tried to determine the auth-method used in AWS RDS Postgres without success.

Am I transmitting my password in clear text when I connect with pgAdmin?

Edit:

I'm starting to think my fears are warranted.  This is what I've learned:

Directions for Using SSL with PostgreSQL DB Instance suggests it's not the default
The 'SSL' option in my pgAdmin connection properties has no options (prob'ly because I haven't setup certificates)

From what I can gather, SSL is usually being used.  What varies is the level to which server certificates are verified.

Reading the guide from Amazon is helpful albeit lengthy.

Things to remember...

To determine if SSL is on (apparently this requires a plugin but works by default on AWS RDS):

  show ssl;

There is a ~/.pgpass file that automatically supplies passwords when using psql (this confused me for a while)
Full server certificate verification (requires the root cert from AWS):

  $ psql -h testpg.cdhmuqifdpib.us-east-1.rds.amazonaws.com -p 5432 "dbname=testpg user=testuser sslrootcert=rds-ca-2015-root.pem sslmode=verify-full"

SSL is still on with this command however (no sslmode,sslrootcert):

  $ psql -h testpg.cdhmuqifdpib.us-east-1.rds.amazonaws.com -p 5432 "dbname=testpg user=testuser"

This will not connect because root cert is not supplied:

  $ psql -h testpg.cdhmuqifdpib.us-east-1.rds.amazonaws.com -p 5432 "dbname=testpg user=testuser sslmode=verify-full"

I never established a non-SSL connection however it is possible to prevent non-SSL connections to a DB instance with a parameter on the server: rds.force_ssl


Stored procedure deadlocking itself, how do I resolve it? MSSQL 2012.  Stored procedure fires twice with the same parameters within 10ms of each other, and we get deadlocks.

Stored procedure basically looks like this (can't share actual code but here's the pertinent pseudocode):

BEGIN TRAN
UPDATE t
SET
    t.Column1 = NewValue1,
    t.Column2 = NewValue2,
    t.Column3 = NewValue3,
    etc for 20 columns...
FROM Table t
    INNER JOIN Results r
        ON r.ID = t.ResultID
WHERE
    UserID = @userid
    AND Deleted = 0

UPDATE t
    SET Deleted = 1
FROM Table t
    LEFT JOIN Results r
        ON r.ID = t.ResultID
WHERE
    t.UserID = @userid
    AND t.Deleted = 0
    AND r.ID IS NULL
COMMIT TRAN

Deadlocks are occurring on Table, indexes involved are UserID (non-clustered covering index) and the Primary Key.  When I run the procedure in my test environment, the execution plans show that both UPDATEs are using the covering index to get the rows (seek, no lookups), then having to update the PK and the covering index.

Possible solutions I've thought of are:

Removing the explicit transaction control.  Since the two updates don't really rely on each other, that seems viable.  Would that resolve the deadlock?
Removing the PK and making UserID the (non-unique) clustered index.  This, however, may impact other things that use the PK elsewhere.
Implementing some boolean logic in the second UPDATE to avoid running it unnecessarily.  Doesn't actually fix the problem though, just makes it happen less often.

I'm about 2 months into my career as a dba, and deadlocks have been difficult to get my head around.  Any advice would be greatly appreciated!

Each of your 2 update statements may cause deadlock when executing within different transactions, no matter explicit or implicit.  They both change the same set of rows  , and nothing guaranties in which order the rows are locked.
One option is just to catch error and re-run failed transactions. Another approach is  to somehow "serialize" updates.

In my opinion, the easiest and fastest way is to modify procedure so it waits until the other instance of the same procedure completes updates.  It can be implemented, for instance, by using sp_getapplock.

Another option is to ensure that rows are locked in the same order (e.g. -open cursor that reads all required rows in particular (unique) order, iterate through it, and for each row issue SELECT WITH ROWLOCK), and issue update after all rows are locked.  Then in case of simultaneous execution the second transaction will wait for the first one. I think that for SQLServer a very little chance of deadlocks might exist because of potential lock escalation though.


Count even deleted rows Is it possible to count even deleted rows in a SQL Server table?

Let's say we have a table Contacts.
I'll add 5 new contacts, and hit count on it. It should return value of 5.
Now, when I delete a contact and run the count it will return 4.

Since I'm working on an app who generates a unique number (Count-year-NP) from the count, it's crucial for me that it's universal.

I don't just count the database, my query is:

SELECT COUNT(*) FROM some_table WHERE YEAR(now) = YEAR(GETDATE())

It counts data where year is current.

Is there any way using stored procedure or simillar, just to keep the count universal for deleted and inserted rows?

I was thinking of marking rows as deleted instead of deleting them, but  I want to be able to delete the values.

When you need only information about number of inserted rows and not about record's data - you could use the approach, which I present below.

Of course, you could mark rows as deleted but it will require changes in data querying. Also it will have impact on resources (storage, maybe views, probably additional indexes, CPU when querying data).

Solution

Create a table which store counters for all of the tables you are interested in. In your case you should store counters per year for those tables.

Update those counters when inserting new records and read from counters table when need to generate new unique number.

Example of a counter table:

CREATE TABLE Counters (TableName NVARCHAR(256), [Year] INT, [Value] BIGINT)

Remarks

Counter updates could be achieved by triggers in easy way but you should be aware of performance and concurrency issues. Consider updating counters table through application layer or stored procedures, which should be a better way.


List all sessions from current user I connect to SQL Server with a given user that is not an administrator. I want to get a list of all my open connections. I've found endless commands to do so but all fall in one of those categories:

I'm not allowed to run that command:

  Msg 297, Level 16, State 1, Line 1
  The user does not have permission to perform this action.

I only see my current session!

Is it possible to list all sessions from current user?

You need to have VIEW SERVER STATE permission on the user account.

GRANT VIEW SERVER STATE TO <username>;

This will need to be granted by someone with sufficient privileges and will allow you to view all sessions when you run commands like sp_who or sp_whoisactive.

If you are worried about security implications of this permission, this post may help Security and Performance implications of "View Server State"

Once you have the permissions to view server state, you can then restrict you just your user by passing that in as a parameter

EXEC sp_who [Domain\mark.sinkinson];
EXEC sp_whoisactive @filter_type = 'login', @filter = [Domain\mark.sinkinson];


Database relationship inheritance I have a vehicle sales store. In my store I sale many kind of vehicles for example  cars and boats. And I would like to keep track of the sales.

My vehicles has some attributes as:

Arrive on store at (a date)
Brand
Model
Color
Type (is a car or is a boat)

A Boat has some other special attributes as:

Registry
Capacity
Footspace

A car has other attributes as:

Registry
Seats

My orders has the following attributes

Vehicle (should be related with a some vehicle that is related
Date of contract
Value
Buyer ID number (yes could add a data structure for buyer data).

I never have two of the same vehicle, since each vehicle is completely        different of another one (even if it was of same brand/color/model), I'm not selling cookies.

How can I represent that in a SQL relational database?

How can a order be expressed?

In special Im trying to undertand how this work inside SQLAlchemy.

Your question is typically too broad to fit in a post here, but I'll draw out some lines that might help you to get a start.

A vehicle can be either a boat or a car. A good place to start is to think of what identifies a vehicle? Since I don't know your business I'll assume that there exists some kind of vehicle_id, note that this can be a combination of attributes:

CREATE TABLE VEHICLE
( VEHICLE_ID ... NOT NULL
, other attributes such as brand
, VEHICLE_TYPE CHAR(4) NOT NULL
,     PRIMARY KEY (VEHICLE_ID)
,     CHECK (VEHICLE_TYPE IN ('CAR','BOAT')
);

If you want type safe sub-types (to prevent someone to add a boat in the car table), you can add a super-key in the vehicle table:

CREATE TABLE VEHICLE
( VEHICLE_ID ... NOT NULL
, other attributes such as brand
, VEHICLE_TYPE CHAR(4) NOT NULL
,     PRIMARY KEY (VEHICLE_ID)
,     CHECK (VEHICLE_TYPE IN ('CAR','BOAT')
      UNIQUE (VEHICLE_TYPE, VEHICLE_ID)
);

A boat can be represented by:

CREATE TABLE BOAT
( VEHICLE_ID ... NOT NULL
, other attributes such as registry
, VEHICLE_TYPE CHAR(4) NOT NULL
,     PRIMARY KEY (VEHICLE_ID)
,     CHECK (VEHICLE_TYPE = 'BOAT')
      FOREIGN KEY (VEHICLE_TYPE, VEHICLE_ID)
          REFERENCES VEHICLE (VEHICLE_TYPE, VEHICLE_ID)
);

and a similar one for car:

CREATE TABLE CAR
( VEHICLE_ID ... NOT NULL
, other attributes such as seats
, VEHICLE_TYPE CHAR(4) NOT NULL
,     PRIMARY KEY (VEHICLE_ID)
,     CHECK (VEHICLE_TYPE = 'CAR')
      FOREIGN KEY (VEHICLE_TYPE, VEHICLE_ID)
          REFERENCES VEHICLE (VEHICLE_TYPE, VEHICLE_ID)
);

Since registry is an attribute of both car and boat you might concider adding that attribute to vehicle.

An order could be represented as ( I assume the is an order_id that can be used to identify an order):

CREATE TABLE ORDER
( ORDER_ID ... NOT NULL PRIMARY KEY
, VEHICLE_ID ... NOT NULL
      REFERENCES VEHICLE (VEHICLE_ID)
, BUYER_ID ... NOT NULL
      REFERENCES BUYERS (BUYER_ID)
, other attributes such as date_of_contract
);

This is just a rough sketch and probably full of false assumptions. I would start with identifying what tables that are involved. Once that is done, start thinking about what identifies a row in each table.


MySQL help do I use Where not exist or Join? - Laravel I have two tables as follows:

1) urls

    id    domain             created_at
------------------------------------------------
    1     google.com           2016-11-30 00:00:00
    2     test.com             2016-11-29 00:00:00
    3     example.com      2016-11-26 00:00:00

etc..
2) links

     id  urls_id   end_date              created_at            status
---------------------------------------------------------------------------
     1     2       2016-11-30 00:00:00   2016-11-30 00:00:00     Approved
     2     2       2016-12-01 00:00:00   2016-11-30 00:00:00     Approved
     3     2       NULL                  2016-11-30 00:00:00     Approved
     4     2       2017-01-01 00:00:00   2016-12-01 00:00:00     Approved
     5     2       2016-01-01 00:00:00   2016-12-01 00:00:00     Pending
     6     3       2016-11-24 00:00:00   2016-12-01 00:00:00     Pending
     7     3       2016-01-01 00:00:00   2016-12-01 00:00:00     Approved
     8     3       NUll                  2016-12-02 00:00:00     Pending
     9     3       2016-01-08 00:00:00   2016-12-01 00:00:00     Approved
     10    3       NUll                  2016-12-02 00:00:00     Pending

I would like to write a MySQL Query to return with these conditions:
one row per domain that follows these conditions:

1). end_date NOT NULL  AND
2). end_date > Carbon::now()   (I am using carbon) not expired AND
3). status is "Approved" AND
4). return latest end_date closer to present or now.

So, for example, it will look and find all "urls_id"= 2 and select ALL status "Approved" and look for end_date that is NOT NULL and not expired, but since there is one NULL then it won't return it, then move on to "urls_id"= 3 and we have 2 approved and both expired and there is no end_date NULL or not expired so we only will return that row of the latest end_date.

Here is an output

  id     domain          end_date_max
  ------------------------------
  9      example.com     2016-01-08 00:00:00

Here is what I have tried but it's selecting all domains (using Where not exists)

select * from urls
where not exists (
    select *
    from links
    where urls.id = links.urls_id
    and (links.status = "Approved"
        and links.end_date is not null)
    and (links.status = "Approved"
        and links.end_date > 2016-12-08 23:46:16)
)
group by urls.domain

How we can do this in one SQL query? In Laravel or just plain Raw query.

Thanks

SELECT
    u1.id,
    u1.domain,
    t1.end_date

FROM urls u1
INNER JOIN
        (SELECT
            l1.urls_id,
            MIN(end_date) as end_date
         FROM urls u2
         INNER JOIN links l1
             ON u2.id = l1.urls_id
            AND l1.status = "Approved"
            AND (l1.end_date > now() OR l1.end_date IS NOT NULL)
         GROUP BY l1.urls_id) t1
ON u1.id = t1.urls_id

Source requirements (not 100% sure about p4, so this could be changed):

1). end_date NOT NULL  AND
2). end_date > now()
3). status is "Approved" AND
4). return latest end_date closer to present or now.