Postgresql versus MySQL insert performance with same indexes

For MySQL, I would guess that got bogged down in I/O.  Possible scenario:

you are using InnoDB, and
the total data + index size exceeded innodb_buffer_pool_size, and
the extra index in the slow test was very random (such as a UUID)

As long as everything fits in the buffer_pool, InnoDB can run quite rapidly.  When you run out of cache space, there might be a lot of I/O, thereby slowing things down.

Also..., InnoDB caches and delays non-unique index writes.  But eventually the writes must be done.  It is hard to say whether you got to that point.

Other things that may speed up inserts:

Batching, either through LOAD DATA or multi-row INSERTs.  The latter runs about 10 times as fast when inserting 100 rows at a time.
Even single-row INSERTs run a lot faster if you do not COMMIT after each row (such as with autocommit=ON).
Sorting the data according to some index, especially some UNIQUE index.  (This helps with caching.)

Those are just some of the ways your benchmark may have been biased in favor of or against MySQL.  (I cannot speak for Postgres, other than  to say that the PRIMARY KEY is stored quite differently.)

An argument against the benchmark:  The main use for indexes is during SELECT, so why measure INSERTs?  Also, if you need to insert a zillion rows per millisecond, when will the database find time to run SELECTs?

Here are more tips on MySQL high-speed ingestion.  (I do not know which parts would also help Postgres.)

A common flaw in benchmarking is to accidentally set the number of iterations (or rows or whatever) just above or below some critical point.  Perhaps 10K rows would report nice rows/second for both engines; perhaps 1M rows works terrible for both, but 100K is near the "cliff" --  and MySQL fell off the cliff just before Postgres would.


How can I get the minimum sum value of fields without repeating where clause?

In Sql Server , you can do it with RANK

CREATE TABLE foos
    ([id] int, [num1] int, [num2] int, [deleted_at] datetime)
;

INSERT INTO foos
    ([id], [num1], [num2], [deleted_at])
VALUES
    (1, 1, 1, '2015-01-01'),
    (2, 2, 2, NULL),
    (3, 2, 2, NULL),
    (4, 4, 4, NULL)
;

--the select

SELECT [id], [num1], [num2]
FROM
(
 SELECT [id], [num1], [num2]
  , RANK() OVER(ORDER BY [num1]+ [num2] ASC) as rk
 FROM foos
 WHERE [deleted_at] IS NULL
)as r
WHERE r.rk = 1

output:

id  num1    num2
2      2    2
3      2    2

dbfiddle here


Upgrading SQL Server with Integration Services

As per BOL

  When you upgrade to SQL Server 2014 Integration Services (SSIS) on a
  machine that has one of these earlier versions of Integration Services
  installed, SQL Server 2014 Integration Services (SSIS) is installed
  side-by-side with the earlier version.

It also says that:

You can upgrade by using one of the following methods: Run SQL Server
2014 Setup and select the option to Upgrade from SQL Server 2005, SQL
Server 2008 or SQL Server 2008 R2, or SQL Server 2012.
Run setup.exe at the command prompt and specify the /ACTION=upgrade
option. For more information, see the section, "Installation Scripts
for Integration Services," in Install SQL Server 2014 from the
Command Prompt.

Source

Now, as per my personal experience with In-Place SQL Server upgrade from SQL Server 2008 R2 to SQL Server 2012 I didn't face compatibility issues. Packages ran fine, except for few where I had to open packages and compile script task again.


How to get the row number when using alias in orderby

The error is (from the column used inside the OVER) in SELECT. You can't use a column alias defined in the select list for another column expression in the same select list:

SELECT
      --
      (product_positive + product_negative) AS total_comments,
      ROW_NUMBER() OVER (ORDER BY total_comments) AS rank
                               -- ^^ this is throwing the error

You just have to duplicate the expression inside the OVER (ORDER BY ...):

SELECT
      --
      (product_positive + product_negative) AS total_comments,
      ROW_NUMBER() OVER (ORDER BY product_positive + product_negative) AS rank
---
ORDER BY
      total_comments DESC NULLS LAST ;       -- you can keep it here

Irrelevant to the issue but this condition will stop the optimizer from using any indexes on (created_at):

WHERE DATE(c.created_at) = DATE 'yesterday'

I'd write it like this, so indexes can be used more effectively:

WHERE c.created_at >= DATE 'yesterday'
  AND c.created_at  < DATE 'today'


How to get users how do not have a group (through join table)?

Right now your (uncorrelated) subquery returns all user IDs, which is obviously a non-empty set. You need to correlate the subquery to the particular user from the outer query:

SELECT users.email
FROM users
  WHERE NOT EXISTS (
    SELECT groups_users.user_id
    FROM groups_users
    INNER JOIN groups ON groups_users.group_id = groups.id
    WHERE groups.name = 'everybody'
    AND groups_users.user_id = users.id
);


Using JOIN to find duplicates in another table taking too long

I can't imagine that the first query would work.  The subquery needs count but that is not available until the entire query is finished.  The outer select can't move forward until the subquery provides the existence of each row.
Try to turn the entire query into a JOIN; no subquery.  In the ON you can test for one "people_id" being less than another -- this implies that there are two rows.


I do not see any proof of the listener running.

This does not seem to be correct:

LISTENER1=
  (DESCRIPTION=
    (ADDRESS_LIST=
      (ADDRESS=(PROTOCOL=tcp)(HOST=10.56.184.32)(PORT=1530))
      (ADDRESS=(PROTOCOL=ipc)(KEY=extproc))
    ))
SID_LIST_LISTENER=
  (SID_LIST=
    (SID_DESC=
      (GLOBAL_DBNAME=CSMEC)
      (ORACLE_HOME=/u03/ORCDEV/oracle/app/oracle/product/12.1.0/dbhome_1/)
      (SID_NAME=CSDEV))
   ))

If you want static registration for LISTENER1, then use SID_LIST_LISTENER1. Your tns entry contains SERVICE_NAME=CSDEV, and you try to register the database with SERVICE_NAME=CSMEC (GLOBAL_DBNAME=CSMEC).

Fix the above and start the listener:

lsnrctl start listener1

Then try again.


How to Best Implement nearest neighbour search in mysql?

As far as design patterns, the Yelp question is pretty standard stuff.

For a more complex answer, you will probably need the geospatial distance. Here is a fascinating powerpoint about that topic (and here is a pdf version of that as well).  However, the math involved is quite ugly.

From their slide:

set @orig_lat=122.4058; set @orig_lon=37.7907;
set @dist=10;

SELECT *, 3956 * 2 * ASIN(SQRT(
POWER(SIN((@orig_lat - abs(dest.lat)) * pi()/180 / 2), 2) +  COS(@orig_lat * pi()/180 ) * COS(abs(dest.lat) * pi()/180) *  POWER(SIN((@orig_lon – dest.lon) * pi()/180 / 2), 2) )) as  distance
FROM hotels dest
having distance < @dist
ORDER BY distance limit 10

There's a longer, more in-depth answer about geospatial distance on Stack Overflow.

But you still want to limit the results by latitude and longitude.

Ultimately, I would avoid the POINT datatype and go with latitude/longitude.  There's currently no way to determine the distance between two POINTs, so you're going to have to store latitude/longitude for that calculation anyways.

One last link:  you may also want to check out this SO thread regarding speeding up the queries using spatial indexes.


mssql-service error exit with status 255 ubuntu

Seems like the only solution is downgrading:
On a Ubuntu server do the following to perform a downgrade to the previous (14.0.3022.28-2) version:
systemctl stop mssql-server
sudo apt-get install mssql-server=14.0.3022.28-2
systemctl start mssql-server

For other linux OS use your package manager or download the package manually from here: https://packages.microsoft.com

Update 2018-03-25:
The support team replied and included a solution for this problem in the release notes:

To resolve this error, enable SQL Server Agent and restart SQL Server with the following commands:
sudo /opt/mssql/bin/mssql-conf set sqlagent.enabled true
sudo systemctl start mssql-server

https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-release-notes#known-upgrade-issue


I see from the comments that you've already given Compare-Object a try, but that really should be working.  Below is a working example showing this:

$SqlServer1Name = "SQL1"
$SqlServer2Name = "SQL2"

$SqlServer1 = New-Object Microsoft.SqlServer.Management.Smo.Server($SqlServer1Name)
$SqlServer2 = New-Object Microsoft.SqlServer.Management.Smo.Server($SqlServer2Name)

$SqlServer1JobListing = $SqlServer1.JobServer.Jobs |
    Select-Object -ExpandProperty Name

$SqlServer2JobListing = $SqlServer2.JobServer.Jobs |
    Select-Object -ExpandProperty Name

Compare-Object $SqlServer1JobListing $SqlServer2JobListing |
    Where-Object {$_.SideIndicator -eq "<="} |
    Select-Object -ExpandProperty InputObject

This will output all of the jobs that live on SQL1 and not on SQL2.  This comparison is by name only, not taking into consideration any other SQL Server Agent Job attributes.


Will MongoDB exceed the 64000 ulimit threshold?

Without knowing more about your database and its usage, I can't say for sure, but in general, the high number of open files found in most busy MongoDB instances is caused by connections (one open file per connection) rather than the data files themselves.

What does MMS have to say about your concurrent connection count?

You need to have huge number of databases/collections, and they need to be quite large before you start to hit this purely from data files themselves.

Hence, the usual method to limit how many open files MongoDB will create is to limit the inbound connections. The most common way to do that is to limit/control the number of client connections from the drivers (connection pool size) and in a sharded environment the number of mongos processes is also important. You can usually work out your theoretical maximum for connections to a MongoDB instance if you know the max number of clients, their max connection pool sizes, how many mongos processes you have (if sharded). There are a couple of other minor factors (replication, chunk migrations etc.) but that calculation will get you in the right ballpark.

If fixing the client side turns out to be an issue, then you can look at limiting incoming connections on the mongod too with the net.maxIncomingConnections option. However, that will just mean that rather than ulimit issues you will be rejecting client connections due to a configuration limit.


If you are strictly interesting in

setting up a quick-and-dirty database
free to use and setup
not worrying about a lot of administration
not being tied down to Storage Engine configuration/tuning specifics

then I would recommend the Xeround Cloud Database service.

I just wrote a post about Xeround's limitations in terms of MySQL and its configurability. Since that is not a concern, Xeround gives you a fairly easy Database to setup and tools to manage it. You can literally create a Xeround MySQL Instance in under 5 minutes.

The free service will give you room to create a 10MB DB. If you need slightly more space, there are pricing options.


How to set a NOT NULL column into NULL in PostgreSQL?

You can simply drop NOT NULL

ALTER TABLE app_client_users ALTER COLUMN client_id DROP NOT NULL;


no pg_hba.conf entry for host "10.1.1.91", user "user" database "testing", SSL off

Thanks for the reply @Imarin. I figured it out. I was configuring the wrong pg_hba.conf file. I needed to configure the file in test-qa-db because that was where the database resided.


The job failed. The Job was invoked by Schedule 1012

Since you indicated your 'transaction log' backup maintenance plan had been working, but was now failing, double check to make sure none of the databases involved in the maintenance plan have somehow gotten changed to SIMPLE recovery - transaction logs cannot be backed up in SIMPLE recovery mode.

Run this query to identify the recovery mode of all of your databaes.

select name,recovery_model_desc from sys.databases

To begin taking transaction log backups again, you'll need to re-establish the log chain by taking at least a DIFFERENTIAL backup (although, to keep things nice and clean, I'd recommend a new FULL backup) to bridge the gap between the time the database was changed to SIMPLE and now.

For more information about the transaction log and SIMPLE recovery, please check out these links:

Backup Under the Simple Recovery Model

Transaction Log Truncation


How do I add a where statement at the end of query for a column that does not have a table to reference from?

Use the WITH clause to get the result set and SELECT only the wanted rows.
WITH CTE As (
select
CASE WHEN (to_char(((case when v.trip_order_start_date is null then case when v.manual_start_date is null then t.required_pickup_date else v.trip_order_start_date end else v.trip_order_start_date end  + (interval  '-1 hours' * ofc.offset)) at time zone 'utc' at time zone 'America/Chicago' + (interval  '-1 hours' * ofc.offset)) at time zone 'utc' at time zone 'America/Chicago', 'YYYYMM') < to_char((now() at time zone 'utc' at time zone 'America/Chicago'), 'YYYYMM')
        AND(coalesce(posted_date, paid_date) IS NULL
            OR to_char((coalesce(v.posted_date, v.paid_date) + (interval  '-1 hours' * ofc.offset)) at time zone 'utc' at time zone 'America/Chicago', 'YYYYMM') > to_char((now() at time zone 'utc' at time zone 'America/Chicago' - interval '1' month), 'YYYYMM'))) THEN 'YES' ELSE 'NO' END AS accrual
)
SELECT * FROM CTE WHERE accrual = 'YES'

Or use the SELECT query as SUBSECT in a FROM clause and use that
SELECT * FROM (
    select
    CASE WHEN (to_char(((case when v.trip_order_start_date is null then case when v.manual_start_date is null then t.required_pickup_date else v.trip_order_start_date end else v.trip_order_start_date end  + (interval  '-1 hours' * ofc.offset)) at time zone 'utc' at time zone 'America/Chicago' + (interval  '-1 hours' * ofc.offset)) at time zone 'utc' at time zone 'America/Chicago', 'YYYYMM') < to_char((now() at time zone 'utc' at time zone 'America/Chicago'), 'YYYYMM')
            AND(coalesce(posted_date, paid_date) IS NULL
                OR to_char((coalesce(v.posted_date, v.paid_date) + (interval  '-1 hours' * ofc.offset)) at time zone 'utc' at time zone 'America/Chicago', 'YYYYMM') > to_char((now() at time zone 'utc' at time zone 'America/Chicago' - interval '1' month), 'YYYYMM'))) THEN 'YES' ELSE 'NO' END AS accrual
    ) t1
WHERE accrual = 'YES'

In both cases you need a valid SELECT


Is a manual log file grow procedure working different that autogrowth?

VLF Calculations
The number of the VLFs is not different for a manual or automatic growth of the transaction log file.
SQL Server < 2014
Up to version 2014 of SQL Server the algorithm for the number of VLFs is as follows when you create, grow or auto-grow the TLog file:

Less than 1 MB : Complicated ignore
Up to 64 MB : 4 new VLFs
64 MB to 1 GB : 8 new VLFs
More then 1 GB : 16 new VLFs

SQL Server 2014 and higher
As of SQL Server 2014 and higher the algorithm is as follows:

Growth of TLog less then 1/8 th size of current TLog size?

Yes: Create 1 new VLF
No: Use the above formula (for previous version)

Your calculation for the number of VLFs should be correct.
Is your database set to AUTOSHRINK? Are you executing any other maintenance tasks that could be resizing the TLOG size?
Auto-growth: yes or no?
If you manually grow your TLog file then indeed you could turn off the auto-growth setting. But be aware: What will happen if you are unavailable and the database has to grow? The database will grind to a halt with an error message.
Baseline Size
At some point in time your database will have a TLog size that will have adequate space for the general imports that occur and the everyday workload and the TLog/Diff/Full backups occurring.
Use this baseline size as a starting point when you create a new TLog from scratch. If the database's TLog is really huge then create a TLog with a base size of 8 GB and manually grow in 8 GB steps until you have reached the "baseline" size.
Then leave some additional space on disk to allow for one or two "auto-growths" of 8GB size.
This will leave you with the best settings according to the article Transaction Log VLFs – too many or too few? where they recommend VLFs to be 512 MB in size.
Disk Space
If you don't have infinite disk space, then ensure that the TLog file has a maximum size set, that will not fill you disk to the maximum.
Monitoring
The most important point is to have a monitoring tool in place that monitors the database's "free space", because you never know if a rogue process is going to do things you hadn't anticipated.
Transaction Log Physical Architecture
There is a good article on TechNet that explains the physical architecture of the Transaction Log. This gives you some insight as to why the TLog rolls over or why it might have to grow.
References
Important change to VLF creation algorithm in SQL Server 2014
Initial VLF sequence numbers and default log file size
Transaction Log VLFs – too many or too few?
Transaction Log Physical Architecture


How to get database name in a server-level DDL trigger

DB_NAME() will always return the context of the trigger, not the context of the event. But you're in luck: the EVENTDATA() contains the object name and the database name. Try adding this to your trigger (also you should be very careful about using 50 characters, because both tables and databases can have longer names).

DECLARE @db sysname = @EventData.value(N'(/EVENT_INSTANCE/DatabaseName)[1]', N'sysname');
PRINT @db;


This has worked for me:

Abort the installation an set the following environtment variable: LD_ASSUME_KERNEL=2.4.1, This will get you past the 17% mark.
Later during the installation you will get this error:

This happens because the installer is using the 64 bit bcc library

So solve that you must do this as root:

cd /usr/bin
mv gcc gcc_old
cp gcc32 gcc

Then click on Retry. Remember to revert that change after successfully installing.


For anyone who wants to know how this was resovled...

Bobby Durrett was correct, Oracle does have the ability to pull data from blocks.

I contacted Oracle ACS.  They sent a top consultant and using the Data UnLoader tool (DUL), which by the way is not their product, were able to extract data from the blocks that survived the corruption.

Bascially you configure the tool with a few parameters and identify the datafiles of interest.  It helps a lot if the system data file is present.  You can tell it to create SQL*Loader or dmp files.  It will skip any block that is corrupt and move on to the next block.  The datafiles are read cold so no need to mount or create a new instance or any such thing.

Caveat:
All you get is table DDL and data.  No users, contraints, FK so your are not recovering the database, just the data.

It works beautifully - on any block not corrupted!


Your problem is not the primary key.  It is a misunderstanding of what a clustered index is.  If you look at your structure you will see that the primary key is clustered.  This is the default and fairly normal but is not always the case so it pays to check.

The clustered index is basically the table itself.  The data pages of the clustered index are the data pages of the table.  So your 450mb for the primary key is mostly the data of the table itself.  At 446264K that makes it about 1k per row which is not unreasonable depending on the amount of data in the AuthorURL and Comment columns.


Need a SQL Query for finding OS Version, RAM, etc

Check out Glenn Berry's Diagnostic Scripts. He includes a whole range of queries in there for accessing server information, including a basic health check.

You will need to download the set of scripts appropriate for the version of SQL Server you're diagnosing.


Execute pgScript in pgAdmin 4 while skipping errors

On psql, it's the default, but you can (un)set it with \set ON_ERROR_STOP 0|1.

So I suggest trying \set ON_ERROR_STOP 0 at the beginning of the script.

From documentation:

  ON_ERROR_STOP

  By default, command processing continues after an error. When this variable is set to on, processing will instead stop immediately. In interactive mode, psql will return to the command prompt; otherwise, psql will exit, returning error code 3 to distinguish this case from fatal error conditions, which are reported using error code 1. In either case, any currently running scripts (the top-level script, if any, and any other scripts which it may have in invoked) will be terminated immediately. If the top-level command string contained multiple SQL commands, processing will stop with the current command.


MySQL table partition truncation not to propagate on a slave

You do a TRUNCATE PARTITION on the Master daily, correct?  And you do not want that to be done on the Slave, correct?

If yes, then

SET SQL_LOG_BIN = 0;
TRUNCATE ...;
SET SQL_LOG_BIN = 1;

https://dev.mysql.com/doc/refman/5.7/en/set-sql-log-bin.html

But, beware.  Once you have more than, say, 100 partitions, the table is likely to be slower and slower on the Slave.  And, at some point you will hit the maximum number of partitions.

I can't think of any thing that will "break" because of having more data (and more partitions) on the Slave than on the Master.  I assume you never otherwise modify the data in 'old' partitions.


How do I REPLACE values in column after querying

You can use CASE for what you're using the replace for. Also, I've modified the WHERE to be more streamlined;

SELECT
CASE
    WHEN column = '1' THEN 'ABC'
    WHEN column = '2' THEN 'DEF'
    WHEN column = '3' THEN 'GHI'
    WHEN column = '4' THEN 'JKL'
END AS column
FROM table
WHERE column IN ('1','2','3','4')

As a note, if column is an int then you don't need to wrap the values in ''

SELECT
CASE
    WHEN column = 1 THEN 'ABC'
    WHEN column = 2 THEN 'DEF'
    WHEN column = 3 THEN 'GHI'
    WHEN column = 4 THEN 'JKL'
END AS column
FROM table
WHERE column IN (1,2,3,4)

This won't modify the table at all, it will just change the way that the data is displayed.


If you are designing your system with the intention of supporting thousands of transactions per second, you probably want to design a queuing system that does not rely on inserts.  Inserts are costly, and depending on the design of the queue table may result in the last page of the queue table being a "hot spot" that limits the rate of throughput for the system.

The following design allows for up to 100,000 concurrent transactions through the Rounds table.

CREATE TABLE dbo.OpenRounds
(
    OpenRoundID BIGINT NOT NULL
        CONSTRAINT PK_GamesOpenRounds
        PRIMARY KEY CLUSTERED
    , UserID INT NULL
    , GameActivityID BIGINT NULL
    , VendorID INT NULL
    , RoundDate DATETIME NULL
    , UserBonusID BIGINT NULL
    , VendorRoundID NVARCHAR(50) NULL
    , ReferenceCount INT NOT NULL
        CONSTRAINT DF_OpenRounds_ReferenceCount
        DEFAULT ((0))
);

INSERT INTO dbo.OpenRounds (OpenRoundID)
SELECT TOP(100000) /* top 100,000 -> we're creating 100,000 slots */
    rn = ROW_NUMBER() OVER (ORDER BY o1.object_id)
FROM sys.objects o1
    , sys.objects o2
    , sys.objects o3;

GO
CREATE SEQUENCE dbo.RoundSlotSequence
AS INT
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 100000
CYCLE
CACHE 10000;
GO

The sequence created above is designed to roll-over once it reaches 100,000.  This coincides with the number of rows we created in the dbo.OpenRounds table.

To push messages into the OpenRounds table, you can use the following proc:

CREATE PROCEDURE dbo.PushRound
(
    @UserID INT
    , @GameActivityID BIGINT
    , @VendorID INT
    , @RoundDate DATETIME
    , @UserBonusID BIGINT
    , @VendorRoundID NVARCHAR(50)
)
AS
BEGIN
    DECLARE @SlotID INT;
    DECLARE @SequenceID INT;
    DECLARE @TryCount INT = 0;
    SELECT @SequenceID = NEXT VALUE FOR dbo.RoundSlotSequence;
    WHILE @SlotID IS NULL AND @TryCount < 50
    BEGIN
        UPDATE dbo.OpenRounds WITH (ROWLOCK)
        SET ReferenceCount = ReferenceCount + 1
            , @SlotID = OpenRoundID
            , UserID = @UserID
            , GameActivityID = @GameActivityID
            , VendorID = @VendorID
            , RoundDate = @RoundDate
            , UserBonusID = @UserBonusID
            , VendorRoundID = @VendorRoundID
        WHERE ReferenceCount = 0
            AND OpenRoundID = @SequenceID;
        /* If @SlotID IS NULL the slot was not available
            - wait 5 milliseconds before checking again
            to see if the slot is open
        */
        IF @SlotID IS NULL WAITFOR DELAY '00:00:00.005';
        SET @TryCount += 1;
    END
    IF @SlotID IS NULL
        RETURN 1
    ELSE
        RETURN @SlotID
END;
GO

This procedure can be used to obtain the next message from the OpenRounds table.  Typically this would be ran inside a loop that continuously looks for rows to process:

CREATE PROCEDURE dbo.PopRound
(
    @UserID INT OUTPUT
    , @GameActivityID BIGINT OUTPUT
    , @VendorID INT OUTPUT
    , @RoundDate DATETIME OUTPUT
    , @UserBonusID BIGINT OUTPUT
    , @VendorRoundID NVARCHAR(50) OUTPUT
    , @MaxRetries INT = 2000 /* 10 seconds
                default maximum wait time */
)
AS
BEGIN
    DECLARE @SlotID INT;
    DECLARE @TryCount INT = 0;
    WHILE @SlotID IS NULL AND @TryCount < @MaxRetries
    BEGIN
        UPDATE dbo.OpenRounds WITH (ROWLOCK)
        SET ReferenceCount = ReferenceCount - 1
            , @SlotID = OpenRoundID
            , @UserID = UserID
            , @GameActivityID = GameActivityID
            , @VendorID = VendorID
            , @RoundDate = RoundDate
            , @UserBonusID = UserBonusID
            , @VendorRoundID = VendorRoundID
        WHERE ReferenceCount > 0;
        IF @SlotID IS NULL WAITFOR DELAY '00:00:00.005';
        SET @TryCount += 1;
    END
END;
GO

This design is loosely based upon concepts I saw at the Exadat.co.uk Super-scaling SQL Server site, by Chris Adkin.  His site provides exceptional material and guidance around pushing SQL Server to its limits, including very in-depth details about the hardware and how SQL Server interacts with it.  I am not associated in any way with Chris, or his website.


MongoDB - The connection is not disconnected for a long time

From this state, we restarted the OS of node # 2. rs 01 and rs 02 of
  node # 1 were promoted to primary. rs01, rs02 of node # 2 demoted to
  secondary.I confirmed it from the log.

  However, it happened that more connections would take more than 1 to 2
  hours from OS reboot on node # 2 to expire. And the MongoDB connection
  pool of the server of the web site became MAX and it became not
  connected to MongoDB from the web site. Normally it will be out of
  connection in about 30 minutes at the longest. I thought that node # 1
  works as Primary, so I thought it would work normally, but I do not
  know why the connection is not disconnected for a long time.

As per MongoDB documentation here The priority settings of replica set members affect both the timing and the outcome of elections for primary. Higher-priority members are more likely to call elections, and are more likely to win. Use this setting to ensure that some members are more likely to become primary and that others can never become primary.

The value of the member’s priority setting determines the member’s priority in elections. The higher the number, the higher the priority.

  Note:
  As you said that the version 3.0, this version of the manual is no longer supported. Learn
  more about upgrading your version of MongoDB.

For further your ref here and here


Is there a way to hint to query optimizer to MySQL which constraints should be done first?

I have rather bad news for you in this regard
MySQL Query Optimizer tends to stray away for further optimization once it sees a FULLTEXT index. I have written about this before in the StackExchange

May 23, 2011 : https://stackoverflow.com/a/6092216/491757
Oct 25, 2011: FULLTEXT index ignored in BOOLEAN MODE with 'number of words' conditional
Jan 26, 2012 : Mysql fulltext search my.cnf optimization
May 07, 2012 : MySQL EXPLAIN doesn't show 'use index' for FULLTEXT

You will have refactor the query
My suggestion would be to do the one of the following
SUGGESTION #1

Retrieve Business IDs suing the FULLTEXT Search
Perform GeoSpatial Search on the Retrieved IDs

SUGGESTION #2

Retrieve Business IDs using GeoSpatial Search (Since you said it take 0.9 seconds)
Perform the FULLTEXT Search on the GeoSpatial-Retrieved Businesses IDs

SUGGESTION #3

Retrieve Business IDs using GeoSpatial Search (Since you said it take 0.9 seconds) in Subquery1
Retrieve Business IDs using FULLTEXT Search in Subquery2
INNER JOIN Subquery1 and Subquery2

SUGGESTION #4

Retrieve Business IDs using GeoSpatial Search (Since you said it take 0.9 seconds)
DO NOT USE FullTextSerach. Use REGEXP against GeoSpatial-Retrieved Businesses IDs

Here is your original query:
SELECT BusinessID as ID,
    111151.29341326*SQRT(pow(-6.186751-X(LatLong),2)+
    pow(106.772835-Y(LatLong),2)*0.98838574205337) AS Distance
from tableauxiliary
USE Index (LatLong_2,FullTextSearch)
WHERE
    MBRContains(
    GeomFromText (
        'MULTIPOINT(-6.1934985598076 106.76604791159,-6.1800034401924 106.77962208841)'
        ),
        Latlong)
    AND Prominent >15
    AND MATCH FullTextSearch AGAINST ('a*' IN BOOLEAN MODE)
    ORDER BY
  Distance;

If the MySQL Query Optimizer detects a FULLTEXT search along with the conjunction AND followed by an expression within the same WHERE clause (thus, the same level of evaluation within an EXPLAIN plan), the MySQL Query Optimizer will take a vacation after working with the FULLTEXT search.
Perhaps you could try something like this (from SUGGESTION #1):
SELECT BusinessID as ID,
    111151.29341326*SQRT(pow(-6.186751-X(LatLong),2)+
    pow(106.772835-Y(LatLong),2)*0.98838574205337) AS Distance
FROM
(
    SELECT BusinessID,LatLong
    FROM
    (
        SELECT BusinessID,Prominent,LatLong
        From tableauxiliary WHERE
        MATCH FullTextSearch AGAINST ('a*' IN BOOLEAN MODE)
    ) AA
    WHERE Prominent > 15
) A
WHERE
    MBRContains(
    GeomFromText (
        'MULTIPOINT(-6.1934985598076 106.76604791159,-6.1800034401924 106.77962208841)'
        ),
        Latlong)
ORDER BY
    Distance
;

Notice you do not see the conjunction AND. Please check the EXPLAIN plan for this. The query should do the following:

Gather BusinessID,Prominent,LatLong That Matched the FULLTEXT search
Remove BusinessID,LatLong Whose Prominent value is < 15
Check Distance of remaining BusinessID values
sort the Check Distance results by Distance

UPDATE 2012-08-08 11:30 EDT
As stated earlier, the FULLTEXT index seems to abandon all hope of using other indexes. Perhaps two subqueries that are JOINed (SUGGESTION #3)
SELECT BusinessID as ID,
    111151.29341326*SQRT(pow(-6.186751-X(LatLong),2)+
    pow(106.772835-Y(LatLong),2)*0.98838574205337) AS Distance
FROM
(
    SELECT BusinessID,Prominent From tableauxiliary WHERE
    MATCH FullTextSearch AGAINST ('a*' IN BOOLEAN MODE)
) A
INNER JOIN
(
    SELECT BusinessID From tableauxiliary WHERE
    MBRContains(
    GeomFromText (
        'MULTIPOINT(-6.1934985598076 106.76604791159,-6.1800034401924 106.77962208841)'
        ),
        Latlong)
) B
USING (BusinessID)
WHERE Prominent > 15
ORDER BY
    Distance
;

You may want to abandon using FULLTEXT searching altogether and try regular expressions (SUGGESTION #4)
SELECT B.BusinessID as ID,
    111151.29341326*SQRT(pow(-6.186751-X(B.LatLong),2)+
    pow(106.772835-Y(B.LatLong),2)*0.98838574205337) AS Distance
FROM
(
    SELECT BusinessID From tableauxiliary WHERE
    MBRContains(
    GeomFromText (
        'MULTIPOINT(-6.1934985598076 106.76604791159,-6.1800034401924 106.77962208841)'
        ),
        Latlong)
) A INNER JOIN tableauxiliary B USING (BusinessID)
WHERE B.Prominent > 15 AND
((B.FullTextSearch REGEXP ' a*') OR (B.FullTextSearch REGEXP '^a*'))
ORDER BY
    Distance
;


Installing time zone data on MariaDB 10.0

I'm concerned that installing one of these (probably would choose mariadb-server-5.5) would conflict with our currently-installed version of MariaDB (10.0.17-MariaDB-1~precise-log).

Ubuntu Precise was released 12.04. It's super ridiculous old, and it was when you asked the question. That's why the version of MariaDB was so old too. MariaDB dropped support for precise in 10.0.31. Because you're running on an unofficial in Debian/Ubuntu's eyes version of MariaDB, it's not guaranteed to work with the system.

All of that said, the question is essentially who packaged your MariaDB. It seems likely you got it from MariaDB upstream. So while you have a workaround, the question is better phrased like this

Where does MariaDB install mysql_tzinfo_to_sql?

Clearly it's not in your path, you can find it by

running the package you downloaded through dpkg -c.
using apt-file
Running: sudo updatedb; locate mysql_tzinfo_to_sql;


Large rolling update on huge table: Getting slower and slower

It probably would have been better to create the index as filter (i.e. with a WHERE clause) as that would get smaller as you updated more records that would then get filtered out of it:

CREATE NONCLUSTERED INDEX RECORD_DELETED_INDEX
  ON [dbo].[huge-table] ( [deleted] ASC, [deletedDate] ASC )
  WHERE deleted is null
  OR    deletedDate is null;

However, you are still going to run into the issue of scanning that index every time that loop runs. With 100 million rows in the table and updating 4000 per iteration, that is 25,000 times you are scanning the table (or index) looking for rows to update. It would be best if you reduced the number of times you queried the table.

Please see my answer to one of your other questions regarding this project for a setup that should make this process go much faster:

sql server: updating fields on huge table in small chunks: how to get progress/status?

In that answer, I show how to query the large table only 100 times. Each time it grabs the Clustered Index Key Field(s) and uses them for all of the UPDATE queries so that the updates are fast. And that setup also allows for getting the current progress of the operation quickly (and without having to query the table!) and cleanly cancelling the process.

It should be noted that this question relates to the following questions (listed in chronological order):

sql server: updating fields on huge table in small chunks: how to get progress/status? (noted above)
Are explicit transactions needed in this while loop?
SQL Server lock_escalation flag on ALTER TABLE -- can I really restructure without a table lock?


Can CDC or Change Tracking be against a read only secondary?

I have been able to find the documentation that I was looking for. CDC is supported off of the readable secondary but Change Tracking is not unfortunately.

The link is specified below but here are the relevant parts for my needs.

  Redirecting the Query Load to a Readable Secondary

While in many cases a client application will always want to connect to the current primary replica that is not the only way to leverage AlwaysOn availability groups. If an availability group is configured to support readable secondary replicas, change data can also be gathered from secondary nodes.

When an availability group is configured, the ALLOW_CONNECTIONS attribute associated with the SECONDARY_ROLE is used to specify the type of secondary access supported. If configured as ALL, all connections to the secondary will be allowed, but only those requiring read only access will succeed. If configured as READ_ONLY, it is necessary to specify read only intent when making the connection to the secondary database in order for the connection to succeed.

  Change Tracking Note

Change tracking data must always be obtained from the primary replica. An attempt to access change data from a secondary replica will result in the following error:

Msg 22117, Level 16, State 1, Line1

For databases that are members of a secondary replica, change tracking is not supported. Run change tracking queries on the databases in the primary replica.

Replication, Change Tracking, Change Data Capture, and AlwaysOn Availability Groups (SQL Server)


Let's see the slowest query in the system.  Quite likely, fixing it (adding an index, reformulating the SELECT, etc) is the best solution.  You mentioned SHOW FULL PROCESSLIST -- That is likely to show the naughty queries; let's see them, plus the associated SHOW CREATE TABLEs.

query_cache_size        = 128M -- too big; no more than 50M.  A big value slows down the system with pruning.

innodb_buffer_pool_size = 256M and key_buffer        = 800M -- What ENGINE are you using?  Neither of these values is likely to be 'good'.  See http://mysql.rjweb.org/doc.php/memory for further advice.  A poorly-sized cache could lead to extra I/O, which would be seen as slow queries.

table_cache            = 9000 -- Yikes!  How many tables do you have?  (Thousands of tables is an OS problem.)


How to capture data from a PowerShell script that runs as part of a SQL Agent Job?

Thanks to @Tony Hinkle for giving an answer as a comment. Here it is promoted to an actual answer, so hopefully the next person with this question finds it:

I created a table in my "Automation" database named "ImportRowCount" with one column named "RowCount" and inserted a single row in there with the number 0

Next, I added this line to my Powershell script:

invoke-sqlcmd -ServerInstance SQLServerHostName -Query "UPDATE Automation.dbo.ImportRowCount SET RowCount = $NumRows"

(Replace "SQLServerHostName" with your actual server's hostname)

So now my Powershell script updates that single row with the value of the $NumRows variable each time it runs.

This is perfect, since I can read the contents of that table before I run the remaining process, and I can use it as a decision point to branch my code into various different paths depending on how many rows are in the file.


In terms of databases, what does 'roach motel' mean?

Generally, I understand (could be wrong of course) it to be either

opaque vendor lock-in
difficulty in re-using data how you want

The second point is seen every day

You buy an app that runs on a mainstream RDBMS: you can report on data, extract it, mine it whatever. Or you buy an app that locks the data into some internal, opaque, proprietary format (not a well known one like PDF or DOC or XLS) where you are forced to use the vendor's (perhaps limited) API.

A couple of other examples

Larry Ellison on Salesforce
Facebook vs Twitter


How would this work. Am i missing something here? Am I doing something
wrong?

You don't need another IP for Server 2 because it is in the same subnet as Server 1. Your listener requires 1 valid IP address for each subnet that a cluster node is in. In your case, you have 3 servers, but only 2 subnets so you only need 2 IP addresses for the listener.
Only one IP is ever 'active' for the listener so even if the AG fails over from Server 1 to Server 2, the IP address for the listener will stay the same, but Server 2 will begin listening on that IP.


How important is drive speed for a MySql server

How many GB in the tables?  It sounds like the RAM might be big enough to cache everything all the time?  If that is the case, the disk is irrelevant.
Even if RAM is not big enough, FULLTEXT does not need to hit the disk that much.  Think of a query as being controlled by how many disk hits are needed, minus how many were avoided by caching.
A Rule of Thumb is that a disk hit on HDD is 10ms; on SSD is 1ms.  If the query needs only a "few" hits, the response time on even HDD will be adequate.
I'm saying that FT 5K searches per minute is probably feasible with your proposed configuration!  Even if there were 20 updates per second.
Every action involving the disk actually occurs in the "buffer_pool" in RAM.  So, if the disk must be hit, the time taken for the query is necessarily slower regardless of disk speed.
On the other hand, NVMe is getting cheap, so why not!


Moving to Galera would be an option. Then your application has to be aware of 'deadlock detected try restarting transaction' errors.
Another possibility is to set up a read slave and configure your application to use this slave for read_only queries. If one slave isn't enough you can add more and use them with a loadbalancer of any kind. Examples are maxScale, ha-proxy on application layer or ipvs on transport layer.


Using SQL Server 2016 R Services over a standalone installation of Microsoft R Server

Possible a slightly subjective question but sometimes it's helpful to think of these things in scenarios, for example:

A predominantly SQL Server project, run by DBAs/devs (who may or may not know some R).  A data scientist or R professional writes some R for them or they do it themselves and test on their local machines, eg using R Studio or the Microsoft R IDE.  When they are happy with the R, they plug it into their data workflow using sp_execute_external_script.  They can easily incorporate this into other stored procedures, SSIS etc.  This might be a good candidate for SQL Server 2016 with R Services.
A secure project, the data is in your secured SQL Server and you want to keep the data close to the analytics and not use the cloud.  This might be a good candidate for an on-premise installation of SQL Server 2016 with R Services.
A predominantly data science project which requires data but does not require SQL Server access.  This might be a good candidate for Microsoft R Server only.
High performance is required for a big data project.  You do not want to share the box with the traditionally memory-hungry SQL Server.  This might be a good candidate for Microsoft R Server only.

Just a few examples there.  What examples can you think of?

HTH


Make a Copy of a Microsoft SQL Server 2005 database from a Mirror Copy

I agree this should be moved to the dba stack exchange, though since I answer there as well I will post it here should it be migrated.

The only way to access a mirrored database from the mirror server is through a database snapshot, which will require enterprise edition of SQL Server. It will also be READ ONLY, so this may not work for what you want.

The other alternative is to take the backups of the primary (these, I would hope, should be available) and restore them for your use somewhere else up to the time you need.

If using already taken backups is not possible, ask for a copy_only backup to be taken on the primary and given to you.


How to remote access Oracle 12c using SQL Developer?

You need to check the following in-order to connect to remote database:

Make sure server is accessible from client side, easiest way to do it by using ping command to verify.
There is a connection between host and server using a specific port, you can use telnet command to verify.
Enable the instance listener to be listening on an IP address not localhost.
Database is running


Based on the plan you posted: no.  Grabbing the info from the tables is 98% of your query time and actually concatenating them together is less than 1% so the UNION ALL is not actually taking up much of the query time.


Mysql multi-master active-active replication : replication doesn't happen for all slaves

Looking at you config, it seems you are missing log-slave-updates in your my.cnf files.

You will need to add this in to both masters my.cnf files and restart them.

This tells the slave database to take anything it receives from it's master, and copy from the relay log to the binary log so it can be replicated onwards.

e.g.

Master 1 Updates a table. It gets written to the binary log on Master1

This is replicated to Master 1's slaves (which are slave 1 and Master2)

In order for it to proceed to slave 2, master 2 has to be told to add it to it's binary log. This is what log-slave-updates does.


MySQL lock on MEMORY storage engine, when does it apply?

You said in your question

  It makes me think that when you insert/update/replace the table locks itself automatically and entirely so you will not be able to select anything while the write is being done. Is this correct?

You are correct.

The MEMORY storage engine performs a full table lock for every DML statement. That is why the link you mentioned shows Lock granularity : Table just like MyISAM. You cannot disable this.

In addition to this, please keep in mind that the MEMORY storage engine still imposes some mild disk I/O for both reads and writes. Why ? An open file handle is maintained on a MEMORY table to at least check on the table's existence and accessibility (See my old most I am using the MEMORY storage engine but MySQL still writes to my disk...Why?)

Please see my other old post Is it feasible to have MySQL in-memory storage engine utilize 512 GB of RAM? on the pros and cons of using MEMORY tables.

You may have to resort to using InnoDB for such session data.


How can I connect to an Oracle Autonomous Data Warehouse with a 3rd party IDE (DataGrip)?

DataGrip 2021.1 provides Oracle 21.1 JDBC driver with all required jar files.
Read DataGrip help about Oracle connection using wallets.
There are some additional Oracle references.


Does Postgresql support different transaction isolation per connection?

Transaction isolation level is set up per transaction basis. Even one connection can have different isolation level on each transaction.

Of course different connection can have different isolation level.

Here's some testing :

postgres=# begin;
BEGIN
postgres=# show transaction_isolation ;
 transaction_isolation
-----------------------
 read committed
(1 row)

postgres=# set transaction isolation level serializable ;
SET
postgres=# show transaction_isolation ;
 transaction_isolation
-----------------------
 serializable
(1 row)

At the same time on another session :

postgres=# begin;
BEGIN
postgres=# show transaction_isolation
;
 transaction_isolation
-----------------------
 read committed
(1 row)

postgres=# set transaction isolation level read uncommitted ;
SET
postgres=# show transaction_isolation
;
 transaction_isolation
-----------------------
 read uncommitted
(1 row)

Once You commit a transaction and begin another new transaction, default isolation level will be used which set on postgresql.conf :

#default_transaction_isolation = 'read committed'

I try this on Postgres 9.1.

Hope this help, cheers.


If you only want to look at the execution plan, run EXPLAIN without ANALYZE.

ANALYZE will cause the query to be executed, which provides much more useful data, but takes as long as the query runs.


Convert SQL Server UPDATE Statement for Use in Oracle

Just remove the FROM clause and replace the reference to the alias with the table name:

UPDATE
    SOB
SET
    COA =   CASE
                WHEN
                    (
                        SELECT COUNT(*)
                        FROM SOB s2
                        WHERE
                            s2.ORD = SOB.ORD AND
                            s2.SHP_KEY <= SOB.SHP_KEY AND
                            s2.SHP_DTTM <= SOB.SHP_DTTM AND
                            s2.SOB_KEY <= SOB.SOB_KEY
                    ) > 1 THEN 0
                ELSE 1
            END
WHERE
    COA IS NULL


A subquery with UNION ALL and the aggregate function avg() used as window aggregate function should do the trick:

SELECT tbl::text, ct, round(ct - avg(ct) OVER (), 2) AS deviation
FROM  (
   SELECT tableoid::regclass AS tbl, count(*) AS ct FROM t GROUP BY 1
   UNION ALL
   SELECT tableoid, count(*) FROM tbl GROUP BY 1
   -- UNION ALL ...
   ) sub

SQL Fiddle.


Try here for a basic intro to PostgreSQL and JSON.

Also, PostgreSQL documentation is pretty good, so try it here. Check out the pretty_bool option.

Your original question was "Is there a way to export postgres table data as JSON". You wanted it in this format

{'id':1,'name':'David'}
{'id':2,'name':'James'}
...

I didn't have a running instance of PostgreSQL so I downloaded, compiled and installed 9.4.

To answer this, I first CREATEed a table (fred)

CREATE TABLE fred (mary INT, jimmy INT, paulie VARCHAR(20));

INSERT INTO fred VALUES (2,    43, 'asfasfasfd'      );
INSERT INTO fred VALUES (3,   435, 'ererere'         );
INSERT INTO fred VALUES (6, 43343, 'eresdfssfsfasfae');

Then, to check:

test=# select * from fred;

 mary | jimmy |      paulie
------+-------+------------------
    2 |    43 | asfasfasfd
    3 |   435 | ererere
    6 | 43343 | eresdfssfsfasfae

Then I issued this command

test=# COPY (SELECT ROW_TO_JSON(t)
test(# FROM (SELECT * FROM fred) t)
test-# TO '/paulstuff/sware/db/postgres/inst/myfile';
COPY 3
test=#

I then quit psql and listed the file myfile.

test=# \q
[pol@polhost inst]$ more myfile
{"mary":2,"jimmy":43,"paulie":"asfasfasfd"}
{"mary":3,"jimmy":435,"paulie":"ererere"}
{"mary":6,"jimmy":43343,"paulie":"eresdfssfsfasfae"}
[pol@polhost inst]$

(you can experiment with the output from

COPY (SELECT ROW_TO_JSON(t, TRUE)  -- <-- Note addition of "TRUE" here!

at your leisure).

It was pointed out by @offby1 that the output (while corresponding to the OP's question) is not correct JSON. @EvanCarroll pointed out that \o is also a way of outputting to a file, so I combined the solutions to these two niggles in this statement (with help from here):

test=# \o out.json
test=# SELECT array_to_json(array_agg(fred), FALSE) AS ok_json FROM fred;
                                     -- <-- "TRUE" here will produce plus
                                        ("+) signs in the output. "FALSE"
                                        is the default anyway.
test=# \o

gives:

[pol@polhost inst]$ more out.json
                                                                   ok_json
----------------------------------------------------------------------------------------------------------------------------------------------
 [{"mary":2,"jimmy":43,"paulie":"asfasfasfd"},{"mary":3,"jimmy":435,"paulie":"ererere"},{"mary":6,"jimmy":43343,"paulie":"eresdfssfsfasfae"}]
(1 row)
[pol@polhost inst]$

FINALLY, there is the backslash (\) problem alluded to by @AdamGent in his post. This was a bit tricky, but it is possible without resorting to post-query processing. Voilà:

INSERT INTO fred VALUES (35, 5, 'wrew\sdfsd');
INSERT INTO fred VALUES (3, 44545, '\sdfs\\\sfs\\gf');

And using REGEXP_REPLACE thus (note the cast ::TEXT) removes the excess blackslashes.

test=# \o slash.json
test=# SELECT REGEXP_REPLACE(ROW_TO_JSON(t)::TEXT, '\\\\', '\\', 'g')
test=# FROM (SELECT * FROM fred) AS t;  -- I found that using a CTE was helpful for legibility
test=# \o
test=# \q

gives:

[pol@polhost inst]$ more slash.json
                    regexp_replace
------------------------------------------------------
 {"mary":2,"jimmy":43,"paulie":"asfasfasfd"}
 {"mary":3,"jimmy":435,"paulie":"ererere"}
 {"mary":6,"jimmy":43343,"paulie":"eresdfssfsfasfae"}
 {"mary":35,"jimmy":5,"paulie":"wrew\sdfsd"}
 {"mary":3,"jimmy":44545,"paulie":"\sdfs\\\sfs\\gf"}
(5 rows)
[pol@polhost inst]$

(p.s. As for @Zoltán 's comment - this may be a version thing - unable to reproduce!).


Restriction on self referencing on insert in MYSQL?

This would be easily solved with a check constraint but these are not yet implemented in MySQL (they are just parsed for "compatibility reasons" during the CREATE TABLE definition and completely ignored after that.)

So, solution 1 is rather obvious. Move to a DBMS that has CHECK constraint implemented, like SQL-Server, Oracle, Postgres, etc. (even MS-Access has them!):

ALTER TABLE Employee
  ADD CONSTRAINT Employee_cannot_be_Boss_of_himself
    CHECK (mssn <> ssn) ;

Solution 2 is to drop the designation column altogether. From your description, all 'Owners' have null mssn and all others have their "manager's" ssn stored in the mssn column.

After dropping that column, you can always calculate it in a view:

CREATE VIEW EmployeeDesignated AS
  SELECT ssn
       , name
       , CASE WHEN (mssn IS NULL OR mssn = ssn)
                THEN 'OWNER'
              WHEN EXISTS (SELECT * FROM Employee ew WHERE ew.mssn = e.ssn)
                THEN 'BOSS'
              ELSE 'WORKER'
         END AS designation
       , mssn
  FROM Employee AS e ;

This just enforces that when a new employee is inserted, if the mssn is same as the ssn, the employee is assigned as 'Owner'.

You may have performance issues though, depending on how you use the designation column. Views inside views or complex queries are not the best deal for MySQL's optimizer.

Solution 3 is to emulate the CHECK constraint using the method described in this answer: Check constraint does not work?

This requires that you upgrade to either MySQL version 5.7 or to MariaDB 5.5.

Solution 4 is to wait for them to be implemented. There are rumors (it's 2016 now) that they will be in the next version.


The word "interval" is a reserved word. You can still use it if you quote it correctly.

See the MySQL Manual for a list of the reserved words and how to use them.
In fact, "interval" is the first example in the link provided:

mysql> CREATE TABLE interval (begin INT, end INT);
ERROR 1064 (42000): You have an error in your SQL syntax ...
near 'interval (begin INT, end INT)'

mysql> CREATE TABLE `interval` (begin INT, end INT);
Query OK, 0 rows affected (0.01 sec)


I found the user folders was in the windows user path, but for some reason these are not normal windows users, so it is not visible to the account management. Thank you.


InnoDB Data Dictionary Cleanup for a Non-Existent Database Named "tmp"?

I was able to fix this using the brute force method of backing up my databases, removing and reinstalling MariaDB, and restoring my databases.


Does the SQL Server backup process use a buffer cache?

Does the backup process use only the memory that has been assigned to the SQL Server (instance)?

The memory that you assign with MIN and MAX memory is just for Buffer Pool and for SQL Server 2012 and up, the memory manager has changed.
When a backup starts it creates a series of buffers, allocated from the memory outside the buffer pool. The target is commonly 4 MB for each buffer resulting in approximately four to eight buffers.
Don't do it on PROD, just for your own learning benefit:
You can find out how much memory your backup is taking:
TRACE FLAG 3605: This trace flag will send the trace to SQL Server error logs.
TRACE FLAG 3213: This trace flag gives information about backup and restore throughput and other configurations.
Below is from my test server:

Does the backup process "flush out" the page from the memory (buffer pool), the dirty buffer?

Yes, as it forces a checkpoint to occur. A full backup will force a database checkpoint which flushes all updated-in-memory pages to disk before anything is read by the backup.
If you want to estimate the amount of total buffer memory that a particular full database backup to a physical disk would use:
-- Estimate the amount of total buffer memory that a particular full database backup to a physical disk would use
-- Reference: http://blogs.msdn.com/b/sqlserverfaq/archive/2010/05/06/incorrect-buffercount-data-transfer-option-can-lead-to-oom-condition.aspx
declare @MaxTransferSize float,
@BufferCount bigint,
@DBName varchar(255),
@BackupDevices bigint
-- Default value is zero. Value to be provided in MB.
set @MaxTransferSize = 0
-- Default value is zero
set @BufferCount = 0
-- Provide the name of the database to be backed up
set @DBName = 'entities' --<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<----- CHANGE HERE YOUR DB NAME !!!
-- Number of disk devices that you are writing the backup to
set @BackupDevices = 1
declare @DatabaseDeviceCount int
select @DatabaseDeviceCount=count(distinct(substring(physical_name,1,charindex(physical_name,':')+1)))
from sys.master_files
where database_id = db_id(@DBName)
and type_desc <> 'LOG'

if @BufferCount = 0
                set @BufferCount = (@BackupDevices*3) + @BackupDevices + (2 * @DatabaseDeviceCount)

if @MaxTransferSize = 0
                set @MaxTransferSize = 1

select 'Total buffer space (MB): ' + cast ((@Buffercount * @MaxTransferSize) as varchar(10))

Refer to:

How It Works: What is Restore/Backup Doing?
How It Works: SQL Server Backup Buffer Exchange


Why does Oracle use a different byte length than java for the supplementary unicode character chipmunk?

What follows is my speculation.

Java Strings are internally represented using the UTF-16 encoding. When you getBytes("UTF-8") Java converts between the two encodings, and you probably use an up-to-date Java platform.

When you attempt to store a Java String in the database, Oracle also performs conversion between the Java native UTF-16 and the database character set as determined by NLS_CHARACTERSET.

The chipmunk character was approved as part of the Unicode standard in 2014 (according to the page you linked), while the latest release of Oracle 11g rel.2 was published in 2013.

One might assume that Oracle uses a different or outdated character conversion algorithm so the byte representation of ) on the server (9 bytes long) is different than what getBytes() returns on the client (7 bytes).

I guess to resolve this issue you could upgrade your Oracle server or use UTF-16 as the database character set.


Vertical data set to horizontal data set for many columns

create table Vertical(FID int ,[SID]int ,OAID int,FN varchar(10),[Row] int)

insert into Vertical(FID ,[SID],OAID ,FN ,[Row] ) values(3184226,9304,82,'8090',1)
insert into Vertical(FID ,[SID],OAID ,FN ,[Row] ) values(3200394,4140,81,'0016',2)
insert into Vertical(FID ,[SID],OAID ,FN ,[Row] ) values(3203126,10465,81,'0426',3)
insert into Vertical(FID ,[SID],OAID ,FN ,[Row] ) values(3136790,10822,81,'0029',4)

    select
        OAID,
        case when FID_1 is null then FID_2 else FID_1 end  as FID_1,
        case when SID_1 is null then SID_2 else SID_1 end as SID_1,
        case when FN_1 is null then FN_2 else FN_1 end as FN_1,

        case when FID_1 is null then FID_3 else FID_2 end  as FID_2,
        case when SID_1 is null then SID_3 else SID_2 end as SID_2,
        case when FN_1 is null then FN_3 else FN_2 end as FN_2,

        case when FID_1 is null then FID_4 else FID_3 end  as FID_3,
        case when SID_1 is null then SID_4 else SID_3 end as SID_3,
        case when FN_1 is null then FN_4 else FN_3 end as FN_3

    from (
    select OAID,
        min([1]) as FID_1,
        min(SID_1) as SID_1,
        min(FN_1) as FN_1,

        min([2]) as FID_2,
        min(SID_2) as SID_2,
        min(FN_2) as FN_2,

        min([3]) as FID_3,
        min(SID_3)  as SID_3,
        min(FN_3) as FN_3,

        min([4] )as FID_4 ,
        min(SID_4) as SID_4 ,
        min(FN_4) as FN_4

    from
    (
    select FID,OAID, [1] as FN_1,[2] as FN_2,[3] as FN_3,[4] as FN_4 ,SID_1,SID_2,SID_3,SID_4,rn_2 from
    (
    select
            FID,
            OAID,
            FN,
            [1] as SID_1,
            [2] as SID_2,
            [3] as SID_3,
            [4]  as SID_4
            ,rn_1
            ,rn_2
    from (select *,ROW_NUMBER() over(order by OAID desc,SID) as rn_1,ROW_NUMBER() over(order by OAID desc,SID) as rn_2 from Vertical )as v
    pivot(min([SID]) for [Row] in([1],[2],[3],[4])) as p

    )as P
    pivot(min([FN]) for rn_1 in([1],[2],[3],[4])) as p

    )as P
    pivot(min([FID]) for rn_2 in([1],[2],[3],[4])) as p

    group by OAID
    )as A


How to create a SQL trigger to execute a stored procedure when a table is updated

I am assuming you are trying to implement some basic replication using a trigger. As long as your process remains fairly simple, this should work OK.

However, I would advise you to read up on (i.e. google) why executing stored procedures from triggers is not a good idea, and also about SQL's built in transactional replication feature. Here is a practical guide to the 2nd topic: http://www.sql-server-performance.com/2010/transactional-replication-2008-r2/

Having said that, here is what I think should work:

USE [database_name]
IF EXISTS (SELECT 1 FROM sys.triggers WHERE name = N'trigger_name')
BEGIN
  THROW 51000, 'The trigger [trigger_name] already exists.', 1;
END
ELSE
  CREATE TRIGGER [trigger_name] ON qf.customer_working_hours
  AFTER INSERT, UPDATE, DELETE AS
  BEGIN
    SET NOCOUNT ON
    EXEC [procedure_database].qf.ServiveRefreshCustomer_WH
  END
BEGIN
END

EDIT: Updated trigger if exists check from Aaron's suggestion below.


Advice on transaction log based reporting server

Is there a better solution for what I am attempting to accomplish where the database wouldn't need to refuse connections, or could queue connections until the backup has been applied?

You need to write some manual code and change backups .Please see below article which we are also applying from Aaron bertrand

http://sqlperformance.com/2014/10/sql-performance/readable-secondaries-on-a-budget


How can I catch the output of DBMS_OUTPUT.PUT_LINE when executing a block using [Oracle.DataAccess.Client]?

DBMS_OUTPUT.GET_LINES is the way to get them back programmatically. Call it the same way as any other procedure. Or for a single line (which is probably easier to code) DBMS_OUTPUT.GET_LINE


Mongodb sharded cluster backup user issue

Once you add a user to the individual shards, which you indicate you have done for MMS, you must then have valid credentials to connect for any purpose, including mongodump.  Up until you added that user for MMS, the shards were running with authentication enabled but with no users populated (this only happens if all your users are in the admin database ans using delegated auth for other databases, otherwise with 2.4 and below you would have at least one shard with users for each database - 2.6+ changes this behavior) and so you were able to connect without credentials.

Essentially this is a loophole left open so that you don't accidentally lock yourself out of your instances when you turn on auth with no users (and one that would probably have stopped working at some point as default security is tightened anyway).

The bottom line is that you will need to add a user for use with mongodump, and it's a good idea to do so anyway rather than allowing non-authenticated users free access to your instances.  If you are running 2.6 or later, then the built in backup role exists precisely for this purpose, if you are on 2.4 or earlier, then the description for that role gives you a great outline of what is needed to backup successfully (and in particular if you want to backup the users themselves).


Open files in MySQL increasing over time, eventually causing it to stop accepting connections

We've followed the advice at What value of thread_cache_size should I use? and increased thread_cache_size, which was one of the suggested changes at MySQL crashing at about 70k open files as well. This seems to have stopped Open_files from increasing as it was before (it was increasing by 1 every few seconds).

I believe this makes sense: if the bug is that threads aren't correctly releasing certain files, then creating less threads (reusing more cached threads) could result in less files being left open. This is of course just guesswork of mine.


Does mariadb `NO_TABLE_OPTIONS`, `NO_FIELD_OPTIONS`, `NO_KEY_OPTIONS SQL_MODE` change database content?

mysqldump reads the database, but does not write to it.  It writes a text file you can look at.

You will find that those options remove certain settings relating character set, engine, auto_increment, etc.  These may make the generated SQL more compatible with some other database vendor or MySQL/MariaDB version.

The syntax I found was more like

mysqldump --compatible=no_table_options,no_field_options,no_key_options ...


Typically when using UNION and you need to know what table a specific row comes from, you'd use a hard-coded value stored in a column similar to:

SELECT Id, productName, Largeimagepath, Discount, Price, Image, 'Tablename1' as Source
FROM tablename1
where Active =1
union
SELECT Id, productName, Largeimagepath, Discount, Price, Image, 'Tablename2' as Source
FROM tablename2
where Active =1
union
SELECT Id, productName, Largeimagepath, Discount, Price, Image, 'Tablename3' as Source
FROM tablename3
where Active =1;

This will return a new column called Source with an identifier that shows which table the row came from.

As @ypercube suggested in the comments you may also want to consider altering this to a UNION ALL - this will include duplicate but you'll also have an identifier on which table it came from. Using a UNION ALL will eliminate the performance impact of removing dups.

SELECT Id, productName, Largeimagepath, Discount, Price, Image, 'Tablename1' as Source
FROM tablename1
where Active =1
union all
SELECT Id, productName, Largeimagepath, Discount, Price, Image, 'Tablename2' as Source
FROM tablename2
where Active =1
union all
SELECT Id, productName, Largeimagepath, Discount, Price, Image, 'Tablename3' as Source
FROM tablename3
where Active =1;


Execution plan flips Filter and Execute Scalar when using the PK, causes cast to fail

I'm sure there are dba.se and StackOverflow duplicates, but it was far faster for me to post this link:

https://feedback.azure.com/forums/908035-sql-server/suggestions/32912431-sql-server-should-not-raise-illogical-errors

In short, the Query Optimizer is really free to rewrite the plan as it sees best.  Sometimes, it means a column is transformed (CAST -> int) close to retrieval so that it doesn't have to be carried many steps forward before performing the CAST.

The only sure-fire way of preventing the error is to look at the CAST expression itself, e.g.:

cast(CASE WHEN ISNUMERIC(ENTRY_CODE)=1 THEN ENTRY_CODE END as int)


How to select rows after a certain row using auto increment temporary columns

Use HAVING for checking rownames
SELECT
   (@cnt := @cnt + 1) AS `rowNumber`, ID
  from table1
  CROSS JOIN (SELECT @cnt := 0) AS n
where
  (
    (1 = 1
    )
  )
  and date > '2020-08-06 08:51:23.08'
  HAVING `rowNumber` > 4
  ORDER BY ID
  LIMIT 10
;


Is there a better way to summarize a table containing events of different types by identifier?

There is no way where you don't have to touch your query when new types are introduced, because the number and type of the columns of a query must be known to the database before the query is actually run (they are evaluated at parse time).
But you can simplify your query by using filtered aggregation to get the different created columns in the output. You also don't need a full join as you make sen_sensor the driving table. A left join is enough in this case (assuming you never have sensors in the even table that don't exist in the sensor table).
SELECT x.belongs_to,
       x.sensor_id,
       x.name,
       y.event_count,
       y.temperature[1] as temperature,
       y.temperature_created,
       y.human_motion_created,
       y.presence_created,
       y.heartbeat_created,
       y.startup_created
FROM sen_sensor x
  left join (
      select sensor_id,
             count(*) as event_count,
             array_agg(temperature order by created desc) filter (where type = 'TEMPERATURE') temperature,
             max(created) filter (where type = 'TEMPERATURE') as temperature_created,
             max(created) filter (where type = 'HUMAN_MOTION') as human_motion_created,
             max(created) filter (where type = 'PRESENCE') as presence_created,
             max(created) filter (where type = 'HEARTBEAT') as heartbeat_created,
             max(created) filter (where type = 'STARTUP') as startup_created
      from sen_sensor_event
      group by sensor_id
  ) as y on y.sensor_id = x.sensor_id

The array_agg(temperature ...) is necessary to get the "latest temperature" as Postgres has no "last_value" as an ordered-set aggregate function (only as a window function). Aggregating the values into a sorted array and then picking the first one serves essentially the same purpose.
When a new type is added, you only add a new max(...) filter (...) expression, which still needs a manual change, but is a lot less to type. In fact, you could have a function (or procedure) that generates the necessary SQL dynamically based on the available types.
This should also be substantially faster than your solution.


Unable to create replica set for MongoDB in Linux

Your hostname must be an alias for 127.0.0.1 in your /etc/hosts file to make it work.

aldwin@myhostname~$ cat /etc/hostname
myhostname
aldwin@myhostname:~$ hostname
myhostname
aldwin@myhostname:~$ cat /etc/hosts
127.0.0.1   localhost
127.0.1.1   myhostname


Database structure for a system with multisite

I would recommend keeping everything in one database. It will be easier to manage and administer. If you need to add more domains/clients, you can continue to use the existing instance of the database (no need for new installations). The records that are specific to certain domains/clients will need some kind of indicator. It could be as simple as an client_id field. Installed instances of the application should have their client_id (or multiple values, if they're allowed to search more than one dataset) in their configuration so that every query where it matters contains the proper client_id.

A possible alternative to having applications configured so the know what to query would be to move that configuration into the database, maybe in a table like this:

portals_hotels
--------------
   portal_id
   client_id

sample data:

portal_id   | client_id
------------|----------
www.x.com   | www.a.com
www.y.com   | www.b.com
www.y.com   | www.a.com


Would you still manually limit max RAM for MSSQL eventhough the sole purpose of that server is to serve MSSQL only?

That is bit debatable topic considering you have SQL Server 2012 here. If you would have asked same question about SQL Server 2005/2008, which was on Windows Server 2003/2008, I would strongly suggest to set a max server memory limit due to bugs which were there in Windows operating system(2003/2008). But memory configuration and OS performance changed significantly from SQL Server 2012 onwards and Window Server 2008/2012 r2 onward respectively, if you ask my opinion, You can leave max server memory configuration to default if you have enough RAM on system and you are just using database engine features. If you ask MS engineers about this they would also say that SQL Server is coded well to manage memory by itself and if you have system dedicated just to SQL database engine you can leave SQL Server max server memory to default SQL Server will manage it efficiently.

Anyways if you still want to configure you can read Similar question and answers given On this SE Thread

There are situation where max server memory is required I have outlined that in This SE thread

  Secondly, how do you check if this Windows server is NUMA enabled?

A little search would lead you to this Blogs.msdn article. It has various pictorial description when SQL Server is NUMA aware and is using it.


Find reason long running stored proc SQL Server

This is probably caused by a blocking process. 3-5 times a day this is blocked by some update or other write that takes ~40 seconds. Your SELECT blocks until it can continue. If this is indeed the case, the problem can be identified using the Blocked Process Report Event. You will have to enable this event and set the threshold to ~35 seconds. See blocked process threshold Server Configuration Option. Obviously the event may fire for other queries too, but arguable those issues also needs to be addressed.

A simple workaround for blocking is using a row versioned isolation level. Read Understanding Row Versioning-Based Isolation Levels.

Otherwise, you will need to investigate this as a performance problem. Read How to analyse SQL Server performance.


SQL Server on Linux, an unmount error is output to the screen upon reboot

The systemd unit file for the SQL server should be locally extended by a dependency on the /datadir this will make sure the start is done after the mount and the unmount waits for the stop. The most generic option to do this would be

RequiresMountsFor=/datadir

I think it automatically depend on the installation path of the scripts already.

Note that you do not need to modify the systemd unit files in the library directory but you can amend them in the /etc/systemd/system/<sqlserver>.service


You can roughly calculate current disk space requirements by using guidelines from the Oracle documentation, once you have defined your schema, including the necessary indexes and materialized views, if any. Do not forget to add space for backups and archived logs.

As for the CPU and memory requirements, they would mostly depend on the exact characteristics of your workload, given the schema defined earlier. In this day and age you should be able to spin up a virtual server with one of the cloud providers and run your load tests (you do have a load test suite, right?) against it much faster than trying to find some magical generic formula on the internets.


Delete comment - actually delete it, or create `deleted` column and update it?

I'd say this depends on the set requirements - many projects demand that the database never forgets "things" - to be able to generate reports and so on. In these cases you usually have this deleted column already. On delete you update said column - and for the report-functions you have an additional table that keeps record of who "ordered the delete" (just in case "he" deleted something that wasn't meant to be forgotten).


Here some thoughts on your situation:

1) if 4 indexes are not used at all, and you are not planning using them in future, consider dropping them completely, instead of just disabling it

2) to disable an index, no need to rebuild it before disabling it. To enable it back, you have to "rebuild"
https://blog.sqlauthority.com/2007/05/17/sql-server-disable-index-enable-index-alter-index/

3) if you are willing to archive/delete previous 2 years of data, better drop unused indexes before archiving data -> this way archiving data will be less resource-intensive as those indexes will not need to be maintained during archive/delete

4) as already mentioned in comments, archive/delete data in small batches (1000,500,100 etc.) -

"delete top (n) from [YourTable] where [Date] < 'some date' "

to prevent escalating row/page level locks to table-level locks
There must be 1 to few seconds break between each batch deletion
You can write a t-sql code that does all this for you

5) You can also setup Extended Events session and monitor for "lock_escalation" events, by doing this you can find out what batch size is not causing escalation of locks to table-level when you run your archive/delete process,
and use this batch size going forward - this will make archiving/deleting painless - there will be no need for outage/downtime for your applications/users


PostgreSQL: difference between collations 'C' and 'C.UTF-8'

The PostgreSQL documentation leaves a lot to be desired (just sayin' 😼 ).

To start with, there is only one encoding for a particular database, so C and C.UTF-8 in your UTF-8 database are both using the UTF-8 encoding.

For libc collations: typically collation names, by convention, are truly two-part names of the following structure:

{locale_name}.{encoding_name}

A "locale" (i.e. "culture") is the set of language-specific rules for sorting (LC_COLLATE) and capitalization (LC_CTYPE). Even though there is sometimes overlap, this really doesn't have anything to do with how this data is stored.

An "encoding" is how the data is stored (i.e. what byte sequence equates to which character). Even though there is sometimes overlap, this really doesn't have anything to do with the sorting and capitalization rules of any particular language that uses the encoding (some encodings can be used by multiple languages that can have quite different rules in one or both of those areas).

To illustrate, consider storing Korean data:

ko_KR is the locale.
Possible encodings that work with this locale are:

EUC_KR (Extended UNIX Code-KR)
JOHAB
UHC (Unified Hangul Code / Windows949)
UTF8 (Unicode's 8-bit encoding)

Also consider the following, taken from the "Collation Support: libc collations" documentation (emphasis added):

  For example, the operating system might provide a locale named de_DE.utf8. initdb would then create a collation named de_DE.utf8 for encoding UTF8 ... It will also create a collation with the .utf8 tag stripped off the name. So you could also use the collation under the name de_DE, which is less cumbersome to write and makes the name less encoding-dependent...

  ...

  Within any particular database, only collations that use that database's encoding are of interest. Other entries in pg_collation are ignored. Thus, a stripped collation name such as de_DE can be considered unique within a given database even though it would not be unique globally. Use of the stripped collation names is recommended, since it will make one less thing you need to change if you decide to change to another database encoding. Note however that the default, C, and POSIX collations can be used regardless of the database encoding.

Meaning, in a database that uses the UTF-8 encoding, en_US and en_US.UTF8 are equivalent. BUT, between that database and a database that uses the LATIN1 encoding, the en_US collations are not equivalent.

So, does this mean that C and C.UTF-8 are the same?

NO, that would be too easy!!! The C collation is an exception to the above-stated behavior. The C collation is a simple set of rules that is available regardless of the database's encoding, and the behavior should be consistent across encodings (which is made possible by only recognizing the US English alphabet — "a-z" and "A-Z" — as "letters", and sorting by byte value, which should be the same for the encodings available to you).

The C.UTF-8 collation is actually a slightly enhanced set of rules, as compared to the base C rules. This difference can actually be seen in pg_collation since the values for the collcollate and collctype columns are different between the rows for C and C.UTF-8.

I put together a set of test queries to illustrate some of the similarities and differences between these two collations, as well as compared to en_GB (and implicitly en_GB.utf8). I started with the queries provided in Daniel Vérité's answer, enhanced them to hopefully be clearer about what is and is not being shown, and added a few queries. The results show us that:

C and C.UTF-8 are actually different sets of rules, even if only slightly different, based on their respective values in the collcollate and collctype columns in pg_collation (final query)
C.UTF-8 expands the characters that are considered "letters"
C.UTF-8, unlike C (but like en_GB), recognizes invalid Unicode code points (i.e. U+0378) and sorts them towards the top
C.UTF-8, like C (but unlike en_GB), sorts non-US-English-letter characters by code point
ucs_basic appears to be equivalent to C (which is stated in the documentation)

You can find, and execute, the queries on: db<>fiddle


Select records in one table but not in another, when the second table is not matching on primary key

MySQL, in fact, all SQL products really barf at
c.id=NULL

It causes confusion. You really mean
c.id IS NULL

You need to change it to
SELECT count(*) FROM list l LEFT OUTER JOIN cardinal c ON l.sku=c.sku where c.id is null;

Give it a Try !!!
ABOUT YOUR QUESTION
Your first query
SELECT count(*) FROM list l LEFT JOIN cardinal c ON c.id=null;

resembles a natural join where nothing matches on the cardinal table, so the count 2677513 is correct.
Your second query
SELECT count(*) FROM list l LEFT OUTER JOIN cardinal c ON l.sku=c.sku where c.id=null;

is little more explicit, but the c.id=null makes it fail, thus getting 0 as a count.


Why doesn't SQL Optimizer use my constraint?

Query 2

The issue is Auto parameterization.

In your case the constant 2 gets replaced with a tinyint parameter @1 rather than the literal 2 - as this parameter could have the value 0 or 1 it wouldn't be valid for the query optimiser to assume the check constraint contradicts this.

You can use the following query to get a plan that does use the contradiction detection (the 1=1 prevents the Auto parameterization) The contradiction detection then happens as part of simplification (see the Optimization Pipeline diagram here).

SELECT *
FROM dbo.RuleTest rt
WHERE rt.oldSchoolBool >=2 and 1=1

the resulting plan is simplified to a constant scan

Query 3

Rules have been discouraged/deprecated for about 20 years. 2000 BOL describes them as

  a backward-compatibility feature ... CHECK constraints are the
  preferred, standard way

The CREATE RULE topic states

  Rules do not apply to data already existing in the database at the
  time the rules are created

So I imagine these are never trusted by the query optimiser as it is possible to do the following and have data that does not adhere to the rule

CREATE TYPE dbo.myBool FROM [INT] NOT NULL

GO

CREATE TABLE dbo.RuleTest
(
  customBool dbo.myBool NOT NULL
)

INSERT INTO  dbo.RuleTest VALUES (10)

go

CREATE RULE dbo.R_Bool AS @value BETWEEN 0 AND 1
GO

EXEC sys.sp_bindrule @rulename = N'R_Bool'
                   , @objname = N'myBool'

Whilst technically it might be possible to maintain a trusted rule concept analogous to trusted constraints I don't believe this exists.

Query 4

You need to add a redundant check constraint on CHECK (myBit BETWEEN 0 AND 1) or equivalent if you want it to do this contradiction detection. Even though a not nullable bit can only hold those two values you don't get this contradiction detected without that


You cannot do it there.  But you can set up a server audit and exclude specific login.  I have not tested the code in 2008 R2.  Change you login value with LoginName. You can also use server_principal_id instead of server_principal_name.

USE [master]

GO

CREATE SERVER AUDIT [FailedLogin]
TO FILE
(   FILEPATH = N'C:\'
    ,MAXSIZE = 64 MB
    ,MAX_FILES = 50
    ,RESERVE_DISK_SPACE = OFF
)
WITH
(   QUEUE_DELAY = 1000
    ,ON_FAILURE = CONTINUE
)
WHERE server_principal_name <> 'LoginName'

GO

USE [master]

GO

CREATE SERVER AUDIT SPECIFICATION [ServerAuditSpecificationFailedLogin]
FOR SERVER AUDIT [FailedLogin]
ADD (FAILED_LOGIN_GROUP)

GO


Measure time duration between events, partitioned by hours

This is more sophisticated than I first understood. One way to handle it is with generate-series(), range types and associated functions and operators:

SELECT user_id, hour, sum(upper(min_on) - lower(min_on)) AS minutes_on
FROM  (
   SELECT user_id, hour, u.range * h.range AS min_on
   FROM  (
      SELECT hour, tsrange(hour, hour + interval '1h') AS range
      FROM   generate_series('2015-10-07 00:00'::timestamp  -- defines range of interest
                           , '2015-10-08 03:00'::timestamp  -- cut off the rest
                           , interval '1h') hour
      ) h
   JOIN  (
      SELECT user_id, event_type
           , tsrange(created_at
                   , lead(created_at) OVER (PARTITION BY user_id ORDER BY created_at)
                    ) AS range
      FROM   user_online_offline_events
      -- add WHERE conditions to limit selection
      -- careful with cutting off leading "on" / trailing "off" events
      ) u ON u.event_type = 1 AND u.range && h.range
   ) sub
GROUP  BY  user_id, hour
ORDER  BY  user_id, hour;

SQL Fiddle with extended data.

Major points

Define the outer time frame in generate_series() once. The function generates one row per hour.
Minutes per hours of the day in multiple days are summed up - if the outer time frame spans multiple days.
Hours without any online time are not in the result. If you need that use LEFT JOIN instead of JOIN
Using the overlap operator && for ranges to identify matching hours and the intersection operator * to calculate actual overlaps.
Resulting times are given as interval. Since multiple days might be summed, the result can be more than one hour and a simple EXTRACT() would not capture that. You can extract minutes  as integer number this way:

EXTRACT('epoch' FROM sum(upper(min_on) - lower(min_on))) / 60 AS minutes_on

Basic timestamp ranges are computed naively with the window function lead(). This assumes that every log-on is followed by a log-off, which typically misses special cases:

Multiple log-on / log-off events for the same user in a row
No matching log-on / log-off

You need to define exactly which corner cases can exist and how you want to handle them. There is a solution either way.


Query execution time in Oracle SQL Developer

On recent versions you can see a timer in the "Task Progress" view (View->Task Progress). Annoyingly it resets to zero the moment the task finishes! :


The flags you need to activate the general log and the slow on in the MySQL Documentation

log (to activate the general log)
general log (to name the log file)
slow query log (to activate the slow log)
log output (specify either FILE or TABLE for where logging info is written)

I would recommend using log-output=TABLE

It will save the logging of general log and slow log info to MySQL tables

mysql.general_log
mysql.slow_log

You should convert them to MyISAM. That way, you can index the tables by the timestamp and perform SELECT queries from them.

By default, these files use the CSV storage engine.

mysql> show create table mysql.general_log\G
*************************** 1. row ***************************
       Table: general_log
Create Table: CREATE TABLE `general_log` (
  `event_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `user_host` mediumtext NOT NULL,
  `thread_id` int(11) NOT NULL,
  `server_id` int(10) unsigned NOT NULL,
  `command_type` varchar(64) NOT NULL,
  `argument` mediumtext NOT NULL
) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='General log'
1 row in set (0.09 sec)

mysql> show create table mysql.slow_log\G
*************************** 1. row ***************************
       Table: slow_log
Create Table: CREATE TABLE `slow_log` (
  `start_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `user_host` mediumtext NOT NULL,
  `query_time` time NOT NULL,
  `lock_time` time NOT NULL,
  `rows_sent` int(11) NOT NULL,
  `rows_examined` int(11) NOT NULL,
  `db` varchar(512) NOT NULL,
  `last_insert_id` int(11) NOT NULL,
  `insert_id` int(11) NOT NULL,
  `server_id` int(10) unsigned NOT NULL,
  `sql_text` mediumtext NOT NULL
) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='Slow log'
1 row in set (0.05 sec)

mysql>

Convert the general log and index it by event_time:

SET @old_log_state = @@global.general_log;
SET GLOBAL general_log = 'OFF';
ALTER TABLE mysql.general_log ENGINE = MyISAM;
ALTER TABLE mysql.general_log ADD INDEX (event_time);
SET GLOBAL general_log = @old_log_state;

Convert the slow log and index it by start_time:

SET @old_log_state = @@global.slow_query_log;
SET GLOBAL slow_query_log = 'OFF';
ALTER TABLE mysql.slow_log ENGINE = MyISAM;
ALTER TABLE mysql.slow_log ADD INDEX (start_time);
SET GLOBAL slow_query_log = @old_log_state;

Give it a Try !!!


Rebuilding system and tables, should I change primary key to int?

No, it is complete overkill for the size of this system and an unnecessary complication for an established database. As for "all the reasons that using an int PK is a good idea", those reasons only apply in either VERY large systems (the performance argument), in systems without CASCADE UPDATE on FKs (the immutable argument), or where a overly-simplistic ORM (or similar style) is in use (the "object identifier" argument).

Keep it simple and work within the existing design.  You're just creating a ton of extra work and risk of errors if you try and retro-fit a new set of keys onto the existing data.


Database Design - different objects with shared tagging

The problem with your first example is the tri-link table.  Is that going to require one of the foreign keys on either report or recommendations to always be NULL so that keywords link only one way or the other?

In the case of your second example, the joining from the base to the derived tables now may require use of the type selector or LEFT JOINs depending on how you do it.

Given that, why not just make it explicit and eliminate all the NULLs and LEFT JOINs?

Reports
----------
ReportID
ReportName

Recommendations
----------
RecommendationID
RecommendationName
ReportID (foreign key)

Keywords
----------
KeywordID
KeywordName

ReportKeywords
----------
KeywordID (foreign key)
ReportID (foreign key)

RecommendationKeywords
----------
KeywordID (foreign key)
RecommendationID (foreign key)

In this scenario when you add something else which needs to be tagged, you just add the entity table and the linkage table.

Then your search results look like this (see there is still type selection going on and turning them into generics at the object results level if you want a single results list):

SELECT CAST('REPORT' AS VARCHAR(15)) AS ResultType
    ,Reports.ReportID AS ObjectID
    ,Reports.ReportName AS ObjectName
FROM Keywords
INNER JOIN ReportKeywords
    ON ReportKeywords.KeywordID = Keywords.KeywordID
INNER JOIN Reports
    ON Reports.ReportID = ReportKeywords.ReportID
WHERE Keywords.KeywordName LIKE '%' + @SearchCriteria + '%'
UNION ALL
SELECT 'RECOMMENDATION' AS ResultType
    ,Recommendations.RecommendationID AS ObjectID
    ,Recommendations.RecommendationName AS ObjectName
FROM Keywords
INNER JOIN RecommendationKeywords
    ON RecommendationKeywords.KeywordID = Keywords.KeywordID
INNER JOIN Recommendations
    ON Recommendations.RecommendationID = RecommendationKeywords.ReportID
WHERE Keywords.KeywordName LIKE '%' + @SearchCriteria + '%'

No matter what, somewhere there is going to be type selection and some kind of branching going on.

If you look at how you would do this in your option 1, it's similar but with either a CASE statement or LEFT JOINs and a COALESCE.  As you expand your option 2 with more things being linked, you have to keep adding more LEFT JOINs where things are typically NOT being found (an object that is linked can only have one derived table which is valid).

I don't think there is anything fundamentally wrong with your option 2, and you could actually make it look like this proposal with a use of views.

In your option 1, I have some difficulty seeing why you opted for the tri-link table.


You need to query DBA_DEPENDENCIES.   But be aware of references that may exist outside of the database itself - references in sql scripts on some user's workstation, or in application code that is not a stored procedure.


Prevent SSMS from seeing the server's file system

Tracing the queries

When tracing the queries executed, below query is found that lists the folders on the drives one by one.

declare @Path nvarchar(255)
declare @Name nvarchar(255)

select @Path = N'D:\'

select @Name = null;

        create table #filetmpfin (Name nvarchar(255) NOT NULL, IsFile bit NULL, FullName nvarchar(300) not NULL)
        declare @FullName nvarchar(300)
        if exists (select 1 from sys.all_objects where name = 'dm_os_enumerate_filesystem' and type = 'IF' and is_ms_shipped = 1)
        begin
          if (@Name is null)
          begin
              insert #filetmpfin select file_or_directory_name, 1 - is_directory, full_filesystem_path from sys.dm_os_enumerate_filesystem(@Path, '*') where [level] = 0
          end
          if (NOT @Name is null)
          begin
            if(@Path is null)
              select @FullName = @Name
            else
              select @FullName = @Path  + convert(nvarchar(1), serverproperty('PathSeparator')) + @Name
              create table #filetmp3 ( Exist bit NOT NULL, IsDir bit NOT NULL, DirExist bit NULL )
              insert #filetmp3 select file_exists, file_is_a_directory, parent_directory_exists from sys.dm_os_file_exists(@FullName)
              insert #filetmpfin select @Name, 1-IsDir, @FullName from #filetmp3 where Exist = 1 or IsDir = 1
              drop table #filetmp3
          end
        end
        else
        begin
          if(@Name is null)
          begin
            if (right(@Path, 1) = '\')
              select @Path= substring(@Path, 1, len(@Path) - charindex('\', reverse(@Path)))
            create table #filetmp (Name nvarchar(255) NOT NULL, depth int NOT NULL, IsFile bit NULL )
            insert #filetmp EXECUTE master.dbo.xp_dirtree @Path, 1, 1
            insert #filetmpfin select Name, IsFile, @Path + '\' + Name from #filetmp f
            drop table #filetmp
          end
          if(NOT @Name is null)
          begin
            if(@Path is null)
              select @FullName = @Name
            else
              select @FullName = @Path +  '\' + @Name
            if (right(@FullName, 1) = '\')
              select @Path= substring(@Path, 1, len(@FullName) - charindex('\', reverse(@FullName)))
            create table #filetmp2 ( Exist bit NOT NULL, IsDir bit NOT NULL, DirExist bit NULL )
            insert #filetmp2 EXECUTE master.dbo.xp_fileexist @FullName
            insert #filetmpfin select @Name, 1-IsDir, @FullName from #filetmp2 where Exist = 1 or IsDir = 1
            drop table #filetmp2
          end
        end

SELECT
Name AS [Name],
IsFile AS [IsFile],
FullName AS [FullName]
FROM
#filetmpfin
ORDER BY
[IsFile] ASC,[Name] ASC
drop table #filetmpfin

The main function used is sys.dm_os_enumerate_filesystem, for each folder that is opened, it goes a level deeper, an example of a second level:

select @Path = N'D:\Data\'

For regular logins

For regular logins it is as easy as denying the select permissions on this TVF to make the user not able to list the folders.

DENY SELECT ON master.sys.dm_os_enumerate_filesystem TO [Domain\LoginName]

When trying to select a backup, the user should see this message:

The user will then be able to only see the drive letters.

For contained users

For the contained user, denying the select on the TVF directly does not work

The contained user can succesfully run the next query example

declare @Path nvarchar(255)
declare @Name nvarchar(255)

select @Path = N'D:\'
select file_or_directory_name, 1 - is_directory, full_filesystem_path from sys.dm_os_enumerate_filesystem(@Path, '*') where [level] = 0

And.... this does not work:

use [PartialDb]
GO
DENY SELECT ON [sys].[dm_os_enumerate_filesystem] TO [PartialUser];
GO

  Msg 4629, Level 16, State 10, Line 34 Permissions on server scoped
  catalog views or system stored procedures or extended stored
  procedures can be granted only when the current database is master.

Below statements work but they do not restrict the user, even if it is not part of the dbrole role

DENY VIEW DATABASE STATE TO [PartialUser];

DENY VIEW DEFINITION ON SCHEMA :: information_schema TO [PartialUser];

DENY VIEW DEFINITION ON SCHEMA :: sys TO [PartialUser];

DENY SELECT ON SCHEMA :: information_schema TO [PartialUser];

DENY SELECT ON SCHEMA :: sys TO [PartialUser];

What does work? In theory

Since the contained user uses the guest account / public role to connect and select from dmv's, (the public role has access to certain objects by default) we could try restricting the public role.

This is not ideal due to a number of reasons. For example, deny > grant and as a result only members in the sysadmin role will be able to select from this TVF.

Another important thing to note is that changing the guest user / public role can have unknown side-effects on the instance or certain functionalities.

USE MASTER
GO
DENY SELECT ON [sys].[dm_os_enumerate_filesystem] TO public;
GO

Changing public / guest permissions is not an ideal scenario.

For example, disabling the guest user can break the msdb database.

Rerunning the select in the context of the contained user:

  Msg 229, Level 14, State 5, Line 7 The SELECT permission was denied on
  the object 'dm_os_enumerate_filesystem', database
  'mssqlsystemresource', schema 'sys'.

There may or may not be a way around this far from ideal approach, I have not found it.

An example of the public role's permissions:

These are granted for a reason, as such certain functionalities might break when denying / revoking these objects. Proceed with caution.

Some more information on the guest user / public role here


INSERT/SELECT slow after TRUNCATE TABLE on table with 6 over mllion rows


Is GROUP BY select-list-ref less optimized than GROUP BY colname?

It demonstrably can be in the event x is not a simple column name. If it is a simple column name it doesn't matter. Take for instance this sample set.

CREATE TABLE foo
AS
  SELECT now()::date AS x
  FROM generate_series(1,1e6);

That creates a sample set of 1,000,000 rows. Now look at the following.

SELECT to_char(x,'YYYY-MM-DD'), count(*)
FROM foo
GROUP BY 1;

The above takes 820.066 ms, however..

SELECT x, count(*)
FROM foo
GROUP BY x;

The above takes only 190ms, need to get it back to the preferred format? Wrap it another SELECT

SELECT to_char(x,'YYYY-MM-DD') AS x, count
FROM (
  SELECT x, count(*)
  FROM foo
  GROUP BY x
) AS t;

And, it still just takes 190ms. Things get even more hairy if you have an index, and useful data. Then you could perhaps be visiting a table when you wouldn't otherwise need to, or you could be sorting in seq scan when an index walk would otherwise work.

It's important to note that this caveat on GROUP BY # only applies if the column of the resultset is an injective function. It's proof that it can matter, not proof that it does matter.


Count(*) as rolecount, filter by rolecount = 1

Filters after the GROUP BY is executed can be applied with the HAVING clause, which is equivalent to a WHERE condition, but for the latest stage of the execution. So, assuming the rest of the query is right:

SELECT
  u.uid AS uid,
  u.name AS name,
  u.picture AS picture,
  n.field_full_name_value AS field_full_name_value,
  f.field_function_value AS field_function_value,
  pi.field_phone_intern__value AS field_phone_intern__value,
  ur.rid AS rid,
  COUNT(*) AS rolecount
FROM `dev_drupal_wurth-intranet`.users u
LEFT OUTER JOIN `dev_drupal_project`.users_roles ur
  ON u.uid = ur.uid
LEFT OUTER JOIN `dev_drupal_project`.field_data_field_full_name n
  ON u.uid = n.entity_id
LEFT OUTER JOIN `dev_drupal_project`.field_data_field_function f
  ON u.uid = f.entity_id
LEFT OUTER JOIN `dev_drupal_project`.field_data_field_phone_intern_ pi
  ON u.uid = pi.entity_id
GROUP BY u.uid
HAVING rolecount = 1;

You can use the alias (I recommend it) or the count(*) expression for filtering, but those cannot be on a WHERE clause.