Easy or Schedulable way to move data from Production to Test

Here is what I would recommend:

If you do not already do this, create a regular backup process for your production database. To make life simple, make it a full backup and include all database objects (tables/schema/data/users). The Microsoft website has a lot of information on how to achieve this.
When you need to update your development environment, restore the production backup into development, then implement the migration (tables, procedures, data) you need to test using the method that is documented for the production change.
Test, knowing that you are not just testing your code changes, but also testing your migration strategy for when you roll out your change to the production environment. You are also testing your database backup and restore (DR) strategy as well.

Note, this may mean you have 2 development databases, one for actual development and one for integration/QA/UAT testing.


Is there any way you can tell MySQL to set value to 0 if value for unsigned column is < 0?

Use the GREATEST() function (this needs a CAST() as well):

UPDATE my_table
SET comments = GREATEST(CAST(comments AS signed)-3, 0)
WHERE something = 123;

or the IF() function:

UPDATE my_table
SET comments = IF(comments>3, comments-3, 0)
WHERE something = 123;

or a CASE expression:

UPDATE my_table
SET comments = CASE WHEN comments>3 THEN comments-3 ELSE 0 END
WHERE something = 123;


Application Server is not able to connect to SQL Server database when TLS 1.2 or 1.1 is enabled

SQL Server 2012 does not support TLS 1.2 in all SPs or versions. See this table for CU and Hotfix minimum versions https://support.microsoft.com/en-us/help/3135244/tls-1-2-support-for-microsoft-sql-server


Back-end with encription and user-control for MS Access2003-VBA

I have talked to them. They consider moving to Access 2007, which provides data protection but no decent per-user control.

It's a bad solution. I will investigate JDBC-ODBC GPL bridges and modern GPL databases.


Setting up an Access DB as a linked server gets pretty ugly, but if you absolutely have to do it, you can split that one table into a separate database file, and create a linked table pointing to it from the original Access DB file. Then set up a linked server in SQL Server pointing to the Access database file with that one table.

Better option: Build an SSIS package that periodically imports/synchronizes data from your Access database.

Best option: Use the upsizing wizard in Access to migrate the data to SQL Server, and continue using Access as the front-end application, but not as the storage engine.


Postgres 9.3 - deletes terribly slow for 10 minutes after dropping indexes/constraints

First, sorry if I have a bad English.

Even if you dropped the constraints from the table you are trying to delete, the constraints of any children tables will affect the performance of the parent table. This occurs because the database needs to do an select on all the foreing keys pointed to the main table each line of delete to guarantee the integrity of the deletion that you're trying to do.

Create indexes on the foreign keys of the child columns that are pointed to the main table you are trying to delete, this will solve the problem and improve performance for inner join also. Remember PostgreSQL will not create indexes for the foreign keys automatically.


MySQL: selecting arbitrarily ordered rows after a specific id

If you are fine about using variables and double sorting:

SELECT
  id,
  value
FROM (
  SELECT
    id,
    value,
    @check AS chk,
    @check := IF(id = 5, 1, @check)
  FROM t, (SELECT @check := 0) x
  ORDER BY
    value DESC,
    id DESC
) s
WHERE
  chk = 1
ORDER BY
  value DESC,
  id DESC
LIMIT 2
;

Here's a SQL Fiddle for it.


If a significant portion of the rows match company_id in (1), then MySQL will choose to do a table-scan instead of using the index. In my experience, "significant portion" is about 20%.

Think of it this way: in the index at the back of a book, why don't they index words like "the"? Because the index entry would just show a list of every single page number. Looking up each page the word occurs on would be more trouble (flipping back and forth in the book) than it would be to simply read the book cover-to-cover.

That's an extreme example, but there's a break-point where it's more efficient to do a table-scan. For better or worse, MySQL tends to choose this option if the value you're searching for occurs somewhere between 16-20%  of the rows.

The other indexes cannot be used because you're using OR.


PostgreSQL 9.1 Hot Backup Error: the database system is starting up

The message "The database system is starting up." does not indicate an error.  The reason it is at the FATAL level is so that it will always make it to the log, regardless of the setting of log_min_messages:

http://www.postgresql.org/docs/9.1/interactive/runtime-config-logging.html#RUNTIME-CONFIG-LOGGING-WHEN

After the rsync, did you really run what you show?:

pgsql -c "select pg_stop_backup();";

Since there is, so far as I know, no pgsql executable, that would leave the backup uncompleted, and the slave would never come out of recovery mode.  On the other hand, maybe you really did run psql, because otherwise I don't see how the slave would have logged such success messages as:

Log: consistent recovery state reached at 0/BF0000B0

and:

Log: streaming replication successfully connected to primary

Did you try connecting to the slave at this point?  What happened?

The "Success. You can now start..." message you mention is generated by initdb, which shouldn't be run as part of setting up a slave; so I think you may be confused about something there.  I'm also concerned about these apparently conflicting statements:

  The only ways I have restarted Postgres is through the service
  postgresql-9.1 restart or /etc/init.d/postgresql-9.1 restart commands.
  After I receive this error, I kill all processes and again try to
  restart the database...

Did you try to stop the service through the service script?  What happened?  It might help in understanding the logs if you prefixed lines with more information.  We use:

log_line_prefix = '[%m] %p %q<%u %d %r> '

The recovery.conf script looks odd.  Are you copying from the master's pg_xlog directory, the slave's active pg_xlog directory, or an archive directory?


Unable to connect to an AWS RDS read replica of MySQL

The size of my MySQL writer instance is db.t3.medium, and that of my read replica was db.t3.small.
After upgrading my read replica to db.t3.medium, I'm able to connect to the read replica.
I don't know how to explain this phenomenon.
This page says

It's a best practice that all instances in a cluster are the same size to avoid any workload overload on the reader instances.

But my read replica's workload is not high.


How can I compare the server impact of transaction replication

Our system must have access to data that is < 1 minute old.
alternatively, establishing transaction replication from the source cluster to our own system for subsequent processing.

Transactional replication will give you the ability of having a near-to real time data at the subscribers.
It comes at the cost, as during the snapshot, the entire table is locked and if the table is huge, then there will be prolonged blocking happening. As an alternative, you can initialize T-Rep from a backup.
Best is to use some stress utility to simulate a workload and then see the latency that is incurred on the T-Rep.
It also makes a big difference whether you are replicating your entire database vs a subset of tables, whether the distribution is on the same server as publisher server, the n/w latency between publisher and subscriber and most obviously the hardware.
It will be tricky when you have to add new table to the publisher or do some schema modification on the publisher - as it will require a snapshot to be generated. Also, be careful with Identity columns when implementing T-Rep.
Below are some links/references that will get you started.

Enhance Transactional Replication Performance
Transaction Replication Latency Diagnosis
How do Identity Columns get Replicated?
Publishing stored proc execution in transactional replication
Transactional replication altering tables and adding stored procedures
Transactional Replication - Snapshot metrics


Innodb buffer pool indexes versus data usage

I have some heartbreaking news for you that will solve your problem.

If you are using MySQL 5.5/5.6, I can assume you are using innodb_file_per_table. There is some disk I/O going on when accessing the INFORMATION_SCHEMA to get information on InnoDB.

What I am about to tell you I have personally eyewitnessed: I worked with a client that had a 2TB MySQL Instance with dozens of database and 10's of thousands of tables. Using MySQL 5.5, I was able to run these queries:

QUERY #1 : Disk Usage by Storage Engine

SELECT IFNULL(B.engine,'Total') "Storage Engine",
CONCAT(LPAD(REPLACE(FORMAT(B.DSize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Data Size", CONCAT(LPAD(REPLACE(
FORMAT(B.ISize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Index Size", CONCAT(LPAD(REPLACE(
FORMAT(B.TSize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Table Size" FROM
(SELECT engine,SUM(data_length) DSize,SUM(index_length) ISize,
SUM(data_length+index_length) TSize FROM
information_schema.tables WHERE table_schema NOT IN
('mysql','information_schema','performance_schema') AND
engine IS NOT NULL GROUP BY engine WITH ROLLUP) B,
(SELECT 3 pw) A ORDER BY TSize;

QUERY #2 : Disk Usage by Storage Engine

SELECT DBName,CONCAT(LPAD(FORMAT(SDSize/POWER(1024,pw),3),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Data Size",CONCAT(LPAD(
FORMAT(SXSize/POWER(1024,pw),3),17,' '),' ',SUBSTR(' KMGTP',pw+1,1),'B') "Index Size",
CONCAT(LPAD(FORMAT(STSize/POWER(1024,pw),3),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Total Size" FROM
(SELECT IFNULL(DB,'All Databases') DBName,SUM(DSize) SDSize,SUM(XSize) SXSize,
SUM(TSize) STSize FROM (SELECT table_schema DB,data_length DSize,
index_length XSize,data_length+index_length TSize FROM information_schema.tables
WHERE table_schema NOT IN ('mysql','information_schema','performance_schema')) AAA
GROUP BY DB WITH ROLLUP) AA,(SELECT 3 pw) BB ORDER BY (SDSize+SXSize);

I got responses 10-20 seconds. How? The client uses innodb_file_per_table=0.

I REPEAT : innodb_file_per_table=0

WHAT DO YOU DO NOW ???

Here is why I said "heartbreaking": You would have put all your InnoDB data back inside ibdata1. Would you like to know all the steps to do this ? Here we go...

STEP #1

Add these to /etc/my.cnf

[mysqld]
innodb_file_per_table = 0
innodb_fast_shutdown = 0

STEP #2

service mysql restart

STEP #3

Create a script that converts InnoDB to InnoDB

MYSQL_USER=root
MYSQL_PASS=password
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
SQL="SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' ENGINE=InnoDB;')"
SQL="${SQL} FROM information_schema.tables WHERE engine='InnoDB'"
mysql ${MYSQL_CONN} -ANe"${SQL}" > RemakeInnoDB.sql
cat RemakeInnoDB.sql

STEP #4

Execute the script

MYSQL_USER=root
MYSQL_PASS=password
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
mysql ${MYSQL_CONN} < RemakeInnoDB.sql

EPILOGUE

When everything is done, all the .ibd files will be gone. All the data and index pages will be inside ibdata1. You will find accessing the INFORMATION_SCHEMA against all InnoDB tables shockingly fast.

Here is the InnoDB Architecture

Note that ibdata1 (a.k.a. System Tablespace will house)

Data Pages
Index Pages
Data Dictionary
Double Write Buffer (Needed for crash recovery)
Insert Buffer (changes to secondary indexes)
Rollback Segments
Undo Space

Don't worry about putting 106G inside ibdata1. MySQL was running with innodb_file_per_table=0 before MySQL 5.5. It can still handle it.

Give it a Try !!!


Will (ROWLOCK,UPDLOCK,READPAST) with ORDER BY non-clustered columns work?

I would opt for marking the row as "in process" (e.g. having a status for the equivalent of "checked out") as opposed to relying solely on row locking:

;WITH x AS
(
  SELECT TOP (1) i_task_id, i_status
    FROM dbo.TAB WITH (ROWLOCK,UPDLOCK,READPAST)
    WHERE i_status = 1
    ORDER BY i_priority DESC,i_task_id ASC
)
UPDATE x SET i_status = 2 -- in process? locked?
  OUTPUT inserted.i_task_id
  WHERE i_status = 1;

No blocking or deadlocking in my tests. The 6th session won't block or deadlock either, it will just return no rows.


Count Assets Barcodes and Group by Rooms

This does Query uses XML string concatenation to get the result in the format you need.

If AssetID or Description can be NULL then you would have to add additional logic

SELECT  '('+reverse(stuff(reverse((
        SELECT A.[AssetID] +','
        FROM [dbo].[AssetRegister] A
        WHERE A.LocationRoomID = AR.LocationRoomID
        FOR XML PATH(''))), 1, 2,''))+')'
        ,
        '('+reverse(stuff(reverse((
        SELECT A.[Description] +','
        FROM [dbo].[AssetRegister] A
        WHERE A.LocationRoomID = AR.LocationRoomID
        FOR XML PATH(''))), 1, 2,''))+')'
      ,LR.[RoomNo]
      ,COUNT(*)
FROM [dbo].[AssetRegister] AR
INNER JOIN [dbo].[LocationRoom] LR
on AR.LocationRoomID = LR.LocationRoomID
GROUP BY LR.[RoomNo],AR.LocationRoomID;

Result

DB<>Fiddle

SQLFiddle

Edit:

  Thank you very much is it possible to get all the 19 rows each one
  with their AssetBarcode 01780, Dell Computer, R001, 4

SELECT  AR.AssetID,
        AR.[Description],
        LR.[RoomNo],
        COUNT(*) OVER(PARTITION BY LR.RoomNo)
FROM [dbo].[AssetRegister] AR
INNER JOIN [dbo].[LocationRoom] LR
on AR.LocationRoomID = LR.LocationRoomID

Result

AssetID Description RoomNo  (No column name)
01780   DELL LAPTOP R0001   4
01781   DELL MONITOR    R0001   4
01782   DELL CPU    R0001   4
01783   DELL KEYBOARD   R0001   4
01784   HP LAPTOP   R0002   4
01785   HP MONITR   R0002   4
01786   HP CPU  R0002   4
01787   HP KEYBOARD R0002   4
01788   APPLE LAPTOP AIR    R0003   8
...*(19 rows)*

DB<>Fiddle


The answer is same as the answer for this question: Inner join using an array column

The main point is installing the btree_gin extension (this may require additional package, like postgresql-contrib)

Then, the extension can be enabled easily with: (requires postgresql >= 9.1)

CREATE EXTENSION btree_gin;

And index can be created with:

CREATE INDEX index_name ON items USING GIN (account_id, keywords);


A clustered index stores all columns from the table, and is effectively the table's data source. Therefore, updating the table in any way will update the clustered index.


Why is the OCR&Vote disk group servicing SCAN name

That is the Oracle Cluster Registry (OCR) where information about clusterware components is stored.

http://docs.oracle.com/cd/E11882_01/rac.112/e41959/intro.htm#CWADD90952

Its size is 256 MB, or 0.26GB, you can check this with ocrcheck:

# ocrcheck
Status of Oracle Cluster Registry is as follows :
         Version                  :          3
         Total space (kbytes)     :     262120
         Used space (kbytes)      :       2948
         Available space (kbytes) :     259172
         ID                       : 1703252243
         Device/File Name         :       +OCR
                                    Device/File integrity check succeeded

                                    Device/File not configured

                                    Device/File not configured

                                    Device/File not configured

                                    Device/File not configured

         Cluster registry integrity check succeeded

         Logical corruption check succeeded


Autocomplete too slow: possible optimizations?

The solution I went with is to build a "half-triangram" table which contains a pre-processsed version of all the model # substrings as suggested by Aaron Bertrand in the two following blog posts of his :

https://sqlperformance.com/2017/02/sql-indexes/seek-leading-wildcard-sql-server

https://sqlperformance.com/2017/02/sql-performance/follow-up-1-leading-wildcard-seeks

The solution for me was to create a new table where I look for models starting with the search string. Each model is listed as such, let's say the product_ID was 51 and the model was 7500 Twin Bed

ID|Model
----------------
51|7500 Twin Bed
51|500 Twin Bed
51|00 Twin Bed
51|0 Twin Bed
51| Twin Bed
51|Twin Bed
51|win Bed
51|in Bed
51|n Bed
51| Bed
51|Bed
51|ed
51|d

This way you never need to do a full wildcard search, a simple select distinct id_product from products_dictionary where model like 'Twin%' will return the required results. The query now takes less than 100ms.

Here is the code I used to create the table and populate it. Again, this is all described properly on Aaron's blog post:

CREATE TABLE [dbo].[products_dictionary](
    [Id_product] [int],
    [model] [nvarchar](75) NOT NULL
)
insert into [products_dictionary]
select p.id_product,f.fragment from products p
cross apply dbo.CreateStringFragments(p.model) AS f;

create clustered index index_idprod_substrmodel on [Products_dictionary]([model],[Id_product])


gtid_executed table is from your former MySQL instance. It isn't used by MariaDB.
The global gtid%pos variables you have listed keep track of the gtid.


How to design a relational DB to capture the same entity as reported by multiple sources?

Is it poor design to have a table (i.e. Instrument) with only a single surrogate column? If this is a problem, how would it be designed differently?

If that is the only constant property of the entity then that is fine, but I would assume that there are other invariant properties such as a name ("Walmart" in your example). Even if the same "instrument" can be referred to by different names by each source and issuer (those names then belong in the relation table) do they not have a canonical name?

  Are the various *Instrument table names confusing? If so, how could it be more clear?

It depends a lot on how well you document them and/or the names and naming conventions in your target industry.

Table names should be as descriptive as possible (you can always shorten them in queries using aliases) avoiding generic words where possible. Where a generic name is needed because nothing more speciifc isn't too specific then I would suggest the simplest generic word that fits (item, object, thing) rather than instrument, but that is personal preference.

For junction tables (relation tables, or whatever you call them as there are several common names for the same concept) I recommend the name represents the row entity types in the relationship - so IssuerInstrument rather than MasterInstrument in your diagram.


Why is my query not using "Missing index tip"? ( SQL Server 2008 R2 )

Table creation :

CREATE TABLE dbo.table1
    (         [ID]              int primary key
            , [Data]            varchar(20)
            , [IDChamada]       varchar(50)
            , [Evento]          varchar(50)
            , [Resposta]        varchar(10)
            , [IDTarifador]     int
            , [ProximoEvento]   varchar(10))
;

You can bloat the table

UPDATE STATISTICS dbo.Table1 WITH ROWCOUNT = 10000000, pagecount = 10000000

With your given index

create nonclustered index nc_idChamada_evento_Rafael on dbo.table1 ( idchamada,evento)

SQL Server is going to do a KEY LOOKUP to find other columns from your SELECT list

select [ID]
    ,[Data]
    ,[IDChamada]
    ,[Evento]
    ,[Resposta]
    ,[IDTarifador]
    ,[ProximoEvento]
from dbo.Table1
where idChamada = '18/09/15 09:08:19 010A 7E09/00'
    and evento = 'EVENTO_DE_COMPLETAMENTO'

Key Lookup is done on below columns since they are part of select query :

The index you need is

create nonclustered index nc_idChamada_evento_KIN on dbo.table1 (
    idchamada
    ,evento
    ) include (
    [Data]
    ,[Resposta]
    ,[IDTarifador]
    ,[ProximoEvento]
    )

Above index will give you a nice seek :


Pause postgres process already in progress

If you have access to the OS account under which postgres runs, you can identify the PID of your process (using top, or ps, or pg_stat_activity), and then do a kill -SIGSTOP <pid>.  Later you would then do a kill -SIGCONT <pid> to get it to run again.

This is not a risk-free thing to do.  If you stop the process while it is holding a spinlock (pretty unlikely, but certainly possible), you could freeze up the entire database, until eventually it would realize something was wrong and then crash itself (terminating your colleague's job).  If you stop the process while it is holding a lightweight lock, it would also freeze the database (or parts of it) and it remain frozen until you continue the process and it releases the lock.

Also, the memory of the stopped process will not be released.  The system will be able to swap that memory out without it constantly fighting to swap back in, and maybe that is good enough.  But it would be better if it were actually released.

All in all, it is probably a better idea to cancel your update and repeat it later.


postgresql : find privileges in meta informations

I find it by myself !

Tables to be parsed to have all metadata on ACL and privileges given by ALTER DEFAULT PRIVILEGES can be found in pg_catalog.pg_default_acl and pg_catalog.pg_namespace with the help of pg_catalog.pg_authid.

Some references :

http://www.postgresql.org/docs/9.4/static/catalog-pg-default-acl.html
http://www.postgresql.org/docs/9.4/static/catalog-pg-namespace.html


SQL Server - Cursors - Missing Afterthought - Safeguard against infinite looping in case of missing FETCH NEXT

To avoid problems like this I'm using different patter for fetch:

OPEN Cursor;

WHILE 1=1
BEGIN

    FETCH NEXT FROM Cursor INTO [...];
    IF @@FETCH_STATUS <> 0 BREAK;

    [...]

END;

CLOSE Cursor;
DEALLOCATE Cursor;

In this pattern you have FETCH exactly once and then you need to check fetch status once too.


Get size of a mongo database in a clustered enviroment

AFAIK, no. You'll have to write a script around db.stats().


'SSL is required but the server doesn't support it (2026)' error while making an SSL MySQL connection

I managed to solve the problem by editing my.ini, step-by-step according to mySQL SLL setup documentation.
SSL settings inside my.ini should be:

[client]
ssl-ca=C:/vca/ca-cert.pem
ssl-cert=C:/vca/client-cert.pem
ssl-key=C:/vca/client-key.pem
[mysqld]
ssl-ca=C:/vca/ca-cert.pem
ssl-cert=C:/vca//server-cert.pem
ssl-key=C:/vca//server-key.pem

I guess the problem was that server understands "\s" (or "/s") as "space". So the last 2 server lines looked like:

ssl-cert=C:/vca/ erver-cert.pem
ssl-key=C:/vca/ erver-key.pem

According to server's logic. Plus configuration path/filename must be the same as windows service initialization path.

Configuration path/filename at mySQL Workbench:

Configuration path/filename at MySQL57 service:

"C:\Program Files\MySQL\MySQL Server 5.7\bin\mysqld.exe" --defaults-file="C:\ProgramData\MySQL\MySQL Server 5.7\my.ini" MySQL57


How Do I Find Who Last Edited a Record or Table in a Database?

You cannot achieve this if you do not have a historical or archive table that store this data. You have to create an AuditDb and create some DML trigger.

OR

You have to use a third-party log reader (if log is not shrinked) like ApexSQL LOG


Started backup via RMAN, any way to nohup it?

Once you start an RMAN operation, the process you started it from is required to stay alive and awake, as that is the session coordinating the other sessions.

You can not exit or put that session in background.

Next time use nohup or even better, my personal favorite: screen.


How to create oracle user for more than one version

For a start you can use oraenv script to set PATH, LD_LIBRARY_PATH, ORACLE_BASE and ORACLE_HOME:

export ORACLE_SID=ORCL

ORAENV_ASK=NO
. /usr/local/bin/oraenv

ORACLE_SID can be parsed from /etc/oratab file. ORACLE_HOSTNAME maybe from HOSTNAME environment variable.


Permissions for sys.database_mirroring in SQL Server

The comment: "To see non-NULL values on a mirror database, you must be a member of the sysadmin fixed server role" is found in the discussion of sys.database_mirroring dynamic management view at:

https://msdn.microsoft.com/en-us/library/ms178655.aspx

Try running the following statement:

select * from sys.database_mirroring

Using my sysadmin account, I get a row for every database. Most rows are all NULL because they are not mirrored databases, but the two mirrors report their status. If you only want to check running mirrors you could use:

select * from sys.database_mirroring
where mirroring_guid is not null

Using my non-sysadmin account, I get nothing. No rows returned, no ROWCOUNT, et cetera, just a delayed 'ding' sound.

So, that note is just trying to tell you not to be surprised when you get no information back. (Except, perhaps, a 'ding' sound.)


Best method to give two Oracle databases on the same server access to common data

You could store the catalogs tables in a separate schema on production.  test could then have a schema with the same name that uses views that select from the production tables using a database link.  This would make production fast because the data is local without requiring extra space.

If you decided that a particular table was too slow to access over the database link or having too great of an impact on production, then you could create a materialized view on test instead of a view.  You could create as many or as few of these as necessary to give the best balance of space and performance.

On the other hand, 1GB isn't that much space, so you might be better off importing into both databases even though it would double the space requirements.  It would have a speed advantage and would make test more like production.


Formal Database Modeling Technologies at Wikipedia

The Wikipedia examples are not the best.

Relationships are between entities. The person <-> weight is a poor examples of a 1-1 relationship because weight is not an entity but an attribute. A better example might be employee <-> cube (assuming one-person cubes). Each employee is assigned one and only one cube and each cube be is assigned to one and only one employee.
In practice, there are few places where you will find 1-1 relationships between tabled entities. You might find them for some PII data that is moved to a separate table for security reasons.

Again a poor example and for the same reason: a date is an attribute, not an entity. Use again the employee <-> Cube number example. The optional quality is imparted by making the Cube number field nullable, meaning the cube may not apply or may not be known, at least at the time the record was created. However, it makes more sense to have the optional component on the cube side as a cube could well be empty, not assigned to any employee.

A better example. A person may be the author of 0, 1 or many books and a book may be authored by 0 (such as a phone book), 1 or many authors. The optional quality on either side could also mean the data is just not known.

Also a fairly good example. A person can only be born in one and only one place -- say, a city. A city, however, can be the birthplace of many people.


How to model dependent categories (parent-child-relation)?

First you have to separate the nodes subordination from the nodes itself. You need a pivot table trees having exactly two columns: nodeID -- parentID. The node having parentID equal to NULL is a root node for certain tree. Pivot table trees can contain more than one tree in it. This table requires a primary key (nodeID) and an unique multicolumn key (nodeID, parentID) - each node should be listed once and each node can have only one parent. An additional restriction - node can not refer to itself as a parent.
CREATE TABLE trees (
  nodeID INT UNSIGNED NOT NULL,
  parentID INT UNSIGNED, -- nullable for root nodes
  PRIMARY KEY ( nodeID ), -- each node is listed once
  UNIQUE KEY  ( nodeID, parentID ), -- no duplicates
  CONSTRAINT treeselfref CHECK ( nodeID <> parentID ) -- can't be parent for itself
)

Then you need a set of procedures for nodes manipulations:
AddNode( nodeID, parentID )
DeleteNode( nodeID ) -- you have to manage orphan childs
DeleteBranch( nodeID ) -- and all its childs/subchilds
GetParent( nodeID )  -- for the given node
GetChilds( parentID ) -- whos parent is parentID
GetBranch( parentID ) -- childs and their childs up to the leaves, ranked
GetLeaves( nodeID ) -- all leaves attached to the node
GetPath( nodeID ) -- full subordination path from the root up to the node, ranked
MergeBranches( nodeIDa, nodeIDb )
MoveBranch( nodeID, newParentID )
. . . . .

An exact set of procedures and their logic depend on the items/categories nature. So here is the sample code for the GetLeaves( nodeID ) procedure, just for illustration of the basic principle:
WITH RECURSIVE cte (nodeID, parentID) AS
( SELECT w.*
    FROM trees AS w
   WHERE w.nodeID = _nodeID  -- seed node for branch
  UNION ALL
  SELECT q.*
    FROM cte   AS z
    JOIN trees AS q ON q.parentID = z.nodeID  -- childs, recurrent
) SELECT s.nodeID
    FROM cte        AS s
    LEFT JOIN trees AS r ON r.parentID = s.nodeID
  WHERE r.nodeID IS NULL; -- nodes not listed as parents for other nodes i.e. leaves

Here is the code for GetPath( nodeID ) procedure that returns a list of all parent nodes from the tree root up to the given node, with rank (distance):
WITH RECURSIVE cte (rank, nodeID, parentID) AS
( SELECT 0 AS rank, w.*
    FROM trees AS w
   WHERE w.nodeID = _nodeID
  UNION ALL
  SELECT z.rank+1, q.*
    FROM cte   AS z
    JOIN trees AS q  ON q.nodeID = z.parentID
) SELECT s.*, f.name
    FROM cte        AS s
    JOIN nodes      AS n ON n.ID = s.nodeID
    JOIN categories AS f ON f.ID = n.categoryID
   ORDER BY s.rank DESC

When invoked for nodeID = 123 it will return something like that:
+----+------+--------+----------+
|rank|nodeID|parentID|name      |
+----+------+--------+----------+
|  5 | 1    | NULL   | root     |
|  4 | 7    | 1      | edible   |
|  3 | 11   | 7      | fruit    |
|  2 | 31   | 11     | domestic |
|  1 | 42   | 31     | apple    |
|  0 | 123  | 42     | mcintosh |
+----+------+--------+----------+


dm_db_missing_index_details - does the order of equality_columns matter

In this case the single index you propose could be used to satisfy all three equality searches.  If any of the columns allows null values, it may matter which order the columns are in.

The optimizer should match the index regardless of the order in the query.  What is important that the equality conditions match the leading columns in the index.  The proposed index would not be good for an equality condition on client_id or stats but not including client_product_id.

In some cases the index may be used if the first field is referenced, but one of the subsequent values is skipped.  Depending on data distribution an optimizer may use a range scan on the proposed index to search for equality on client_product_id and status but not client_id.


Unable to access mysql database remotely

Two possible problems

You didn't specify the -p option. Without -p the client will not prompt for a password and will attempt to connect without one
The database may not be configured to allow remote login for root.  You may have to login on the server and enable remote access (i.e. GRANT ALL ON *.* TO root@'%' IDENTIFIED BY '...';)


Client not reading from Mongo secondary database

Using "ReadPreference.SECONDARY_PREFERRED" does not provide any such guarantee to read from a node in the same region.

I'm not familiar with Mongoose but the first thing I would do is prove this via the Mongo shell first. Second I would remove the redundancy you acknowledged above. If it shouldn't be there, get rid of it. Via the shell; try using a read preference of "secondaryPreferred" but really what I think you want is "nearest" which will read from a node with the least network latency.
Again; I cannot speak towards Mongoose specifically but there are some non Mongoose related to resolve first per what you shared.

The best this will do is confirm for you if you have a Mongoose issue or not.

Also; using a WriteConcern of 0 and reading from Secondary nodes is not a terrific practice if you rely on the data being on the secondary. If you are okay with the eventual consistency, then a write concern of 0 and reading from secondaries is fine.


JOIN between 3 tables to show missing data from 2 tables

SELECT c.*, p.*, cp.InstallDate
FROM Computer c
CROSS JOIN Patch p
LEFT JOIN ComputerPatched cp ON c.ComputerID = cp.ComputerID
                            AND p.PatchID = cp.PatchID


Why MySQL imports only one record for each table?

I'm using Mac OS (10.10.1) and SequelPro (1.0.2).

This is bug in SequelPro, i think: bug began to appear after upgrading OS to 10.10.1.

See issue #2048 on GitHub.


difference between pg_dump command's --column-insert and --attribute-insert

They are the same. Check the documentation (where they are mentioned together):

  --column-inserts
  --attribute-inserts
  Dump data as INSERT commands with explicit column names (INSERT INTO table (column, ...) VALUES ...). This will make restoration very
  slow; it is mainly useful for making dumps that can be loaded into
  non-PostgreSQL databases. However, since this option generates a
  separate command for each row, an error in reloading a row causes only
  that row to be lost rather than the entire table contents.


Do not use the SHRINK option.

It results in serious performance degradation. Also after you shrink the data file, it will later grow even more.

Please watch this video by Klaus Aschenbrenner (Microsoft Certified Master in SQL Server):

SQL Server Quickie - SHRINK

I would recommend to kill the SPID but be ready for the rollback operation. It can take another 3 hours, maybe less.


'I am trying to determine if memory pressure is an issue to some of my problem.'

very useful script:
https://github.com/ktaranov/sqlserver-kit/blob/master/Scripts/SQLServer_Memory_Information.sql

you see verbose memory utilization:

https://www.sqlskills.com/blogs/glenn/sql-server-diagnostic-information-queries-for-november-2017/
SQL Server 2017 Diagnostic Information Queries:
see comment

-- Page Life Expectancy (PLE) value for each NUMA node in current instance  (Query 46) (PLE by NUMA Node)
SELECT @@SERVERNAME AS [Server Name], RTRIM([object_name]) AS [Object Name], instance_name, cntr_value AS [Page Life Expectancy]
FROM sys.dm_os_performance_counters WITH (NOLOCK)
WHERE [object_name] LIKE N'%Buffer Node%' -- Handles named instances
AND counter_name = N'Page life expectancy' OPTION (RECOMPILE);
------

-- PLE is a good measurement of internal memory pressure
-- Higher PLE is better. Watch the trend over time, not the absolute value

(Query 14)

-- Good basic information about OS memory amounts and state  (Query 14) (System Memory)
SELECT total_physical_memory_kb/1024 AS [Physical Memory (MB)],
       available_physical_memory_kb/1024 AS [Available Memory (MB)],
       total_page_file_kb/1024 AS [Total Page File (MB)],
       available_page_file_kb/1024 AS [Available Page File (MB)],
       system_cache_kb/1024 AS [System Cache (MB)],
       system_memory_state_desc AS [System Memory State]
FROM sys.dm_os_sys_memory WITH (NOLOCK) OPTION (RECOMPILE);
------

-- You want to see "Available physical memory is high" for System Memory State
-- This indicates that you are not under external memory pressure

-- Possible System Memory State values:
-- Available physical memory is high
-- Physical memory usage is steady
-- Available physical memory is low
-- Available physical memory is running low
-- Physical memory state is transitioning

(47)

-- Memory Grants Pending value for current instance  (Query 47) (Memory Grants Pending)
SELECT @@SERVERNAME AS [Server Name], RTRIM([object_name]) AS [Object Name], cntr_value AS [Memory Grants Pending]
FROM sys.dm_os_performance_counters WITH (NOLOCK)
WHERE [object_name] LIKE N'%Memory Manager%' -- Handles named instances
AND counter_name = N'Memory Grants Pending' OPTION (RECOMPILE);
------

-- Run multiple times, and run periodically if you suspect you are under memory pressure
-- Memory Grants Pending above zero for a sustained period is a very strong indicator of internal memory pressure

(62)

-- Top Cached SPs By Total Logical Reads. Logical reads relate to memory pressure  (Query 62) (SP Logical Reads)
SELECT TOP(25) p.name AS [SP Name], qs.total_logical_reads AS [TotalLogicalReads],
qs.total_logical_reads/qs.execution_count AS [AvgLogicalReads],qs.execution_count,
ISNULL(qs.execution_count/DATEDIFF(Minute, qs.cached_time, GETDATE()), 0) AS [Calls/Minute],
qs.total_elapsed_time, qs.total_elapsed_time/qs.execution_count AS [avg_elapsed_time],
CASE WHEN CONVERT(nvarchar(max), qp.query_plan) LIKE N'%<MissingIndexes>%' THEN 1 ELSE 0 END AS [Has Missing Index],
FORMAT(qs.last_execution_time, 'yyyy-MM-dd HH:mm:ss', 'en-US') AS [Last Execution Time],
FORMAT(qs.cached_time, 'yyyy-MM-dd HH:mm:ss', 'en-US') AS [Plan Cached Time]
-- ,qp.query_plan AS [Query Plan] -- Uncomment if you want the Query Plan
FROM sys.procedures AS p WITH (NOLOCK)
INNER JOIN sys.dm_exec_procedure_stats AS qs WITH (NOLOCK)
ON p.[object_id] = qs.[object_id]
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
WHERE qs.database_id = DB_ID()
AND DATEDIFF(Minute, qs.cached_time, GETDATE()) > 0
ORDER BY qs.total_logical_reads DESC OPTION (RECOMPILE);
------

-- This helps you find the most expensive cached stored procedures from a memory perspective
-- You should look at this if you see signs of memory pressure

(63)

-- Top Cached SPs By Total Physical Reads. Physical reads relate to disk read I/O pressure  (Query 63) (SP Physical Reads)
SELECT TOP(25) p.name AS [SP Name],qs.total_physical_reads AS [TotalPhysicalReads],
qs.total_physical_reads/qs.execution_count AS [AvgPhysicalReads], qs.execution_count,
qs.total_logical_reads,qs.total_elapsed_time, qs.total_elapsed_time/qs.execution_count AS [avg_elapsed_time],
CASE WHEN CONVERT(nvarchar(max), qp.query_plan) LIKE N'%<MissingIndexes>%' THEN 1 ELSE 0 END AS [Has Missing Index],
FORMAT(qs.last_execution_time, 'yyyy-MM-dd HH:mm:ss', 'en-US') AS [Last Execution Time],
FORMAT(qs.cached_time, 'yyyy-MM-dd HH:mm:ss', 'en-US') AS [Plan Cached Time]
-- ,qp.query_plan AS [Query Plan] -- Uncomment if you want the Query Plan
FROM sys.procedures AS p WITH (NOLOCK)
INNER JOIN sys.dm_exec_procedure_stats AS qs WITH (NOLOCK)
ON p.[object_id] = qs.[object_id]
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
WHERE qs.database_id = DB_ID()
AND qs.total_physical_reads > 0
ORDER BY qs.total_physical_reads DESC, qs.total_logical_reads DESC OPTION (RECOMPILE);
------

-- This helps you find the most expensive cached stored procedures from a read I/O perspective
-- You should look at this if you see signs of I/O pressure or of memory pressure

(64)

-- Top Cached SPs By Total Logical Writes (Query 64) (SP Logical Writes)
-- Logical writes relate to both memory and disk I/O pressure
SELECT TOP(25) p.name AS [SP Name], qs.total_logical_writes AS [TotalLogicalWrites],
qs.total_logical_writes/qs.execution_count AS [AvgLogicalWrites], qs.execution_count,
ISNULL(qs.execution_count/DATEDIFF(Minute, qs.cached_time, GETDATE()), 0) AS [Calls/Minute],
qs.total_elapsed_time, qs.total_elapsed_time/qs.execution_count AS [avg_elapsed_time],
CASE WHEN CONVERT(nvarchar(max), qp.query_plan) LIKE N'%<MissingIndexes>%' THEN 1 ELSE 0 END AS [Has Missing Index],
FORMAT(qs.last_execution_time, 'yyyy-MM-dd HH:mm:ss', 'en-US') AS [Last Execution Time],
FORMAT(qs.cached_time, 'yyyy-MM-dd HH:mm:ss', 'en-US') AS [Plan Cached Time]
-- ,qp.query_plan AS [Query Plan] -- Uncomment if you want the Query Plan
FROM sys.procedures AS p WITH (NOLOCK)
INNER JOIN sys.dm_exec_procedure_stats AS qs WITH (NOLOCK)
ON p.[object_id] = qs.[object_id]
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
WHERE qs.database_id = DB_ID()
AND qs.total_logical_writes > 0
AND DATEDIFF(Minute, qs.cached_time, GETDATE()) > 0
ORDER BY qs.total_logical_writes DESC OPTION (RECOMPILE);
------

-- This helps you find the most expensive cached stored procedures from a write I/O perspective
-- You should look at this if you see signs of I/O pressure or of memory pressure

P.S. this is not an exhaustive answer


PgAdmin 4.14 connection error in cloudSQL : could not send data to server, socket is not connected, could not send ssl negotiation packet

Subash's answer worked for me. Switching from localhost to 127.0.0.1 allowed the connection successfully.


postgres 9.1: role public does not exist

As @Evan Carroll said, there is no "public" role. The manual has this to say:

  The key word PUBLIC indicates that the privileges are to be granted
      to all roles, including those that might be created later. PUBLIC
      can be thought of as an implicitly defined group that always
      includes all roles. Any particular role will have the sum of
      privileges granted directly to it, privileges granted to any role
      it is presently a member of, and privileges granted to PUBLIC.

...

  Unlike the case with privileges, membership in a role cannot be
      granted to PUBLIC.


Filtering a result set on a very large table (using INCLUDE index in SQL Server)

From the details you've provided it seems reasonable that the IX_VeryLarge non-clustered index would support both queries you've shown in your question.  You have the Payload column typed as VARBINARY(MAX) - if you expect large objects to be stored in that column, I'd likely not INCLUDE it in the index since that will cause the index to be much much larger than it would otherwise be.  If the vast majority of your queries result in one or two rows, a simple lookup into the clustered index/heap (the table) is preferable.  The pattern you've shown whereby you have a clustered index/heap and supporting non-clustered indexes with included columns (a so-called covering index) is extremely common, and perhaps the single best way to ensure good performance, along with good statistics.

Having said that, I wouldn't expect the 2nd query to outperform the 1st query since the results returned would be exactly the same.  SQL is not a procedural language, it is a declarative language.  With a procedural language, you tell the computer what to do, whereas with a declarative language you describe the desired results.  Since SQL is declarative, and since both queries return the same result set, it is reasonable to assume SQL Server will likely come up with the same plan for both queries.  Since the 2nd query is more difficult for a human to understand, I'd favor the 1st variant.  Try not to outsmart the SQL Server query optimizer, it cannot be done is very hard to do.

At the end of the day, the only way for you to truly understand the best option in your situation, on your data, with your table definition and output requirements, is to test them.


Is the complex query read-only ?  The answer to that kinda determines the solution.

You could do this with haproxy maybe.   See https://githubengineering.com/context-aware-mysql-pools-via-haproxy/ for inspiration on how to measure the load of your backend (this is mysql but same idea works on pgsql)

You could probably get some good config values from my repo here https://github.com/gplv2/haproxy-postgresql

it's made for master / standby cluster but you can add a standby pgsql backend pool to this config to the example of the guys at github with their mysql machines pool and the backup pool.  sounds like that is what you want


INSERT INTO selected columns creates new column from JOIN

Argh.. Stupidity reigns supreme. In response to Aaron's comment (which deserved an up-vote for stating the obviously overlooked), I traced back through the nested stored procedures. Turns out there was an ALTER TABLE that was adding the columns with NULL values from some old legacy script that was an attempt at trying to do what the INSERT INTO was able to accomplish all along.

@Aaron, thanks for stating the obvious which lead me to check the code path to this point.


Postgres DDL and DML best practices and coding standards

Like all coding standards, every project is going to have preferences.  I am not saying you will like my preferences but I will offer them.  Keep in mind that LedgerSMB is heading towards a model where half of the code will be SQL so we have had to put some thought into these.

The first thing we did was decide that DDL should be documented for autodocumentation tools rather than human reviewers. If you are going to go the human reviewer route (which reduces your documentation), you do something like:

CREATE TABLE asset_item (
    id serial not null unique, -- internal id
    tag text primary key, -- accounting control code
    description text not null,
...
);

That is great and all, but it means that people have to read your DDL files to get a sense of what you want to tell them.  If you go the autodoc documentation tool route, you get something different:

CREATE TABLE asset_item (
    id serial not null unique,
    tag text primary key,
    description text not null,
...
);

COMMENT ON TABLE asset_item IS $$ This table stores the fixed asset items.$$;
COMMENT ON COLUMN  asset_item.id IS $$Internal id, used for joins$$;
COMMENT ON COLUMN asset_item.tag IS $$Accounting control code$$;

This makes the comments available through general documentation tools which is very helpful in a large project.  The big limitation is that comments must follow definitions.

For DML we usually justify the queries, lining up the verbs on one side and conditions or identifiers on the other.  An example might be:

   SELECT ai.id, ai.tag, ai.description, coalesce(ai.start_depreciation, ai.purchase_date),
          adm.short_name, ai.usable_life
           - months_passed(coalesce(ai.start_depreciation, ai.purchase_date),
                                  coalesce(max(r.report_date),
                                           ai.start_depreciation,
                                           ai.purchase_date))/ 12,
          ai.purchase_value - ai.salvage_value, ai.salvage_value, max(r.report_date),
          sum(rl.amount), ai.purchase_value - sum(rl.amount)
     FROM asset_item ai
     JOIN asset_class ac ON (ai.asset_class_id = ac.id)
     JOIN asset_dep_method adm ON (adm.id = ac.method)
LEFT JOIN asset_report_line rl ON (ai.id = rl.asset_id)
LEFT JOIN asset_report r on (rl.report_id = r.id)
    WHERE r.id IS NULL OR r.approved_at IS NOT NULL
 GROUP BY ai.id, ai.tag, ai.description, ai.start_depreciation, ai.purchase_date,
          adm.short_name, ai.usable_life, ai.purchase_value, salvage_value
   HAVING (NOT 2 = ANY(as_array(r.report_class)))
          AND (NOT 4 = ANY(as_array(r.report_class)))
          OR max(r.report_class) IS NULL
 ORDER BY ai.id, ai.tag, ai.description;

Not everyone likes this style but we find it easy to read, follow, and maintain.

A few big points I would make:

Avoid inline views if you can.  Formally define them or use CTE's if you need them.  Inline views make maintenance harder because the simple structure of the query and rigid order is degraded.
Sometimes subqueries are needed but try to avoid them where you can for the same reason.
When deciding whether to use select * or a named column list consider whether you expect a named type back.  If you do, then select * is preferable.  Otherwise typically you want to avoid select *.
If you are going to use stored procedures consider your contracts carefully.  Changes to interfaces in these are often not as easy as you'd like unless you have some sort of service discovery framework in between.


Handling large DB tables that have millions of records

You can try this:

create table Table3 as (select * from Table1 A where
exists(select * from Table2 B where A.key=B.key));

And then delete from Table2 using newly created table Table3

delete from Table2 A where exists(select * from Table3 B where A.key=B.key);


Relocate all files to folder checkbox unavailable sql server 2012

Try to do this with T-SQL:

restore database YourDatabase_NewName
from disk = 'C:\YourDir\YourDatabase.bak'
with
    move 'YourDataFileName' to 'C:\DataDir\DataFile.mdf',
    move 'YourLogFileName' to 'C:\LogDir\LogFile.ldf'
go


You can get invalid views with a procedure like this:

DECLARE

    CURSOR InvaliViews AS
    SELECT OBJECT_NAME
    FROM USER_OBJECTS
    WHERE object_type = 'VIEW'
        AND status = 'INVALID';

BEGIN

    FOR aView IN InvaliViews LOOP
        UTL_MAIL.SEND(
            sender => 'noreply@your_domain.com',
            recipients => 'your-email@your_domain.com',
            subject => 'Notification about invalid views',
            message => 'View '||aView.OBJECT_NAME||' is not valid');
    end loop;

end;

You can run such procedure once a week triggered by Scheduler Job


Calculate start and end dates from a list of only start dates and partitioned

To make sure I've answered correctly, I'll restate the requirement. The result set should show the first date (DATEID) that a SUPPLIER took control of a PRODUCT and the last date the PRODUCT was visible (ENDDATEID) when another SUPPLIER took control.

ENDDATEID should add an additional 28 days to the date the next SUPPLIER took control of a PRODUCT. If no new SUPPLIER has taken control, the ENDDATEID should show the current date + 28 days.

If I've stated this correctly, you can use LEAD and a CTE to return the result set you're looking for. Code below, and working example here:

WITH CTE AS (
SELECT DATEID
  ,LEAD(DATEID, 1, GETDATE()) OVER (PARTITION BY PRODUCTNUMBER ORDER BY DATEID) AS TAKEOVERDATE
  ,PRODUCTNUMBER
  ,SUPPLIERNUMBER
FROM FACT_TBL_PRODUCTSUPPLIER
)

SELECT DATEID
  ,DATEADD(DAY, (DATEDIFF(DAY, DATEID, TAKEOVERDATE))+28, DATEID) AS ENDDATEID
  ,PRODUCTNUMBER
  ,SUPPLIERNUMBER
FROM CTE

Output:

DATEID      ENDDATEID   PRODUCTNUMBER   SUPPLIERNUMBER
----------------------------------------------------
2019-07-06  2020-02-27  343             545
2019-07-06  2019-08-07  555             1234
2019-07-10  2019-08-13  555             8888
2019-07-16  2019-08-29  555             1234
2019-08-01  2019-10-13  555             1234
2019-09-15  2020-02-27  555             6666

In the LEAD syntax, we're partitioning the data by PRODUCTNUMBER to ensure we only get dates relevant to the product that row represents. We then get the next DATEID in order (defined as DATEID ascending in the LEAD syntax). If no next date value exists, we get the current date. This gives us the date a product was taken over (or the current date if it hasn't been taken over).

From here, we can use DATEADD outside the CTE to add 28 days to the 'TAKEOVERDATE' column to produce the desired ENDDATEID value.

NOTE: This syntax is for SQL Server. You haven't stated an RDBMS, but tagged the question T-SQL so I'm assuming SQL Server.


Recommended Database(s) for Selecting Random Rows

I was using one of two ways to get a random row:

SELECT * FROM mytable ORDER BY RANDOM();

and

SELECT * FROM mytable LIMIT 1 OFFSET <random number>;

The offset method was faster for low offset numbers, otherwise super slow. An offset of 1 million took ~600 msec, while 100 million was 60 seconds (average query time for midpoint offset was 2.5 minutes). For order by random, all queries were ~5 minutes.

I ended up finding a good solution, similar to Mat's comment, but I don't need to keep track of how many rows there are.

I added an integer column called "randval" with a btree index. When a record is saved, I generate a random number between 1 and 2 billion. It took a while to migrate existing data (about half a day on slow hardware), but now random selects are super fast, typically around 1 millisecond using this query:

SELECT * FROM mytable WHERE randval >= <random number> ORDER BY randval LIMIT 1;

With 500 million rows, not every random number has a value, so the order by with limit gives us the next closest one and the index makes it quick, with a 150,000x speedup for what was an average 2.5-minute query.

Databases don't like doing random things, but they sure do well at specific things that just happen to have random values.


Unable to connect to SQL Server from one machine; "an existing connection was forcably closed by the remote host"

Found the problem. SQL Server 2008 R2 really wants to use a cert signed with SHA1. If SHA1 certs are disabled, you can't connect.


How to optimize query by preventing repetitive subquery

You can use a lateral cross join, although I don't understand why you want to avoid a CTE:
SELECT dim_date.date AS "date",
       stage_net_subscription_020_classes.client_id AS client_id,
       stage_net_subscription_020_classes.product_id AS product_id
FROM star.dim_date dim_date
  CROSS JOIN stage.stage_net_subscription_020_classes
  CROSS JOIN LATERAL (
    select min(date) as min_date, max(date) as max_date
    from stage.stage_net_subscription_010_filter_and_net_pds
  ) as mm
  where date >= mm.min_date
  and   date <= mm.max_date


You haven't really indicated how this is called.  Do you really have a string full of comma separated ints or is that just an artifact of some other aspect of your system?

If the outer part of the environment around this requirement is changeable, then typically in SQL Server 2008 and up, I pass a table-valued parameter containing a list of IDs (perhaps from ADO.NET or whatever) and then just use it as a table inside the procedure - i.e. WHERE value IN (SELECT id FROM @table) or INNER JOIN @table AS t ON t.id = users.t


Stress Test to Saturate Memory for MySQL InnoDB

From Windows Platform Limitations in the MySQL 5.5 Reference Manual:

  On Windows 32-bit platforms, it is not possible by default to use more than 2GB of RAM within a single process, including MySQL.

What's to investigate?  We already know what happens when you max the memory: Really Bad Things™.

If there's a remote chance that your setup will periodically require this much memory, 32-bit Windows is the wrong platform.

Almost universally, the biggest memory consumer is of course the InnoDB Buffer Pool, but there's no need for any queries to test this... you don't need any activity at all, or any tables, because the entire amount of memory declared for innodb_buffer_pool_size is allocated immediately when the server starts.  The buffer pool never grows, never shrinks, never changes, ever.  The number of free pages changes, but those are not "free" from the operating system's perspective -- they're still just as allocated, merely marked as containing nothing of interest within InnoDB.

If the operating system refuses to allocate the amount of memory provisioned for the buffer pool, MySQL will simply refuse to start.

This is illustrated here, where the OP mistakenly believed that the server was crashing and restarting "because" memory couldn't be allocated for the buffer pool, but was in fact crashing for a Linux-specific reason but then refusing to restart because the system would not allocate the total amount of memory required for the pool, due to overuse of available memory by something else... but this allocate-all-at-startup behavior for the InnoDB buffer pool is not platform-specific.

So, you should be able to set this value near the max and then find that taking the server process over the edge should not require very much additional effort at all.  But I'm still not sure what the point is.

As you realize, MySQL uses memory for a variety of different purposes, several of which are dynamically-sized, definable on a per-connection basis, and allocated on demand, which makes it virtually impossible to provision a server based on limiting memory usage to some worst-case scenario absolute value, yet expecting that server to be able to handle its typical load efficiently.

The simplest illustration of this is the fact that you can obviously reduce the theoretical maximum memory utilization of a given instance by restricting the maximum number of simultaneous client connections... but any given application needs a certain number of available connections to perform efficiently, and if that number is below your target value, then you're not really solving anything -- it just feels like you have.

I say, either your server has enough memory for the workload, or it doesn't.  If it doesn't, then attempting to "tune" your way out of potential trouble is unlikely to offer much in the way of solutions.

Some ideas on how to easily generate demand for more memory...

SELECT * FROM large_table ORDER BY non_indexed_column;

SELECT * FROM large_table WHERE non_indexed_column = some_value;

SELECT * FROM large_table WHERE some_column LIKE '%a_freqent_match%';

Queries like these could trigger the allocation of a sort buffer, a read buffer and/or a random read buffer, which should make a new request to the OS for the memory that buffer requires.

Simple solution:

DELIMITER $$

DROP PROCEDURE IF EXISTS `test`.`eat_memory_until_server_crashes` $$
CREATE PROCEDURE `test`.`eat_memory_until_server_crashes`()
BEGIN

-- this procedure is intended to eat as much memory as it can
-- it creates a series of consecutively-numbered session variables as large
-- as your configuration will allow them to be.

-- do not run this unless you intend to crash your server

-- also, do not run from a gui tool -- use the mysql command line client:

-- mysql> CALL test.eat_memory_until_server_crashes;

-- if you kill the query or thread before the server crashes,
-- the memory consumed will be returned to the OS

  DECLARE counter INT DEFAULT 0;
  LOOP
    SET counter = counter + 1;
    SET @qry = CONCAT('SET @crash_me_',counter,' := REPEAT(\'a\', @@max_allowed_packet)');
    SELECT counter, @qry;
    PREPARE hack FROM @qry;
    EXECUTE hack;
    DEALLOCATE PREPARE hack;
    -- adjust timing or remove this entirely depending on how quickly you want this to happen
    DO SLEEP(0.1);
  END LOOP;

END $$

DELIMITER ;

Inspiration for this: Schwartz, Baron; Zaitsev, Peter; Tkachenko, Vadim (2012-03-05). High Performance MySQL: Optimization, Backups, and Replication (Kindle Location 12194). OReilly Media - A. Kindle Edition.


how to synchronize Oracle data with SQL Server

Set up a database link between Oracle and SQL server, then set up a trigger in the Oracle database to update the SQL Server table whenever the oracle table changes.


Why the query optimizer choose this bad execution plan?

My suggestion would be to change the ORDER BY id and use instead:

ORDER BY updated_at, id

The secondary index on (updated_at) includes the id as well (which I assume is the primary key), so with this change there is no chance that the index won't be used, as it will be a perfect match for both the WHERE and the ORDER BY clause.

Bonus point, the query's plan will get rid of the sort as well.


how to show combined results of two tables?

Test

SELECT t1.InvoiceID, t1.UserID, t2.InvoiceID, t2.UserID
FROM ( SELECT InvoiceID, UserID, @num1 := @num1 + 1 rownumber
       FROM au.invoice, (SELECT @num1 := 0) variable
       WHERE InvoiceID > 1234
       ORDER BY UserId ) t1
JOIN ( SELECT InvoiceID, UserID, @num2 := @num2 + 1 rownumber
       FROM nz.invoice, (SELECT @num2 := 0) variable
       WHERE InvoiceID > 1234
       ORDER BY UserId ) t2 USING (rownumber)

fiddle

If the list of UserID values in both subqueries is the same with guarantee (although it is unclear why) you may use JOIN ... USING (rownumber, UserID). Or even use the enumeration while partitioning by UserID (using 2 variables in each subquery).


Dump output is raw SQL statements, numbers and everything else is represented as text so the size is in no way relevant (one int is 4 bytes, but as text 1 is probably 1 byte and maximal int value (signed) = 2147483647 is 10 bytes and minimal int value = -2147483648 is 11 bytes).
I suppose those are all ibdataX, *.ibd, *.myi, *.myd, *.frm. Both MyISAM and InnoDB tables may contain some empty space (for example after deleting some rows) which will not be automatically reclaimed by the filesystem, InnoDB files contain not only actual row data but old versions of records, redo logs and other information needed for transactions.
The free space mentioned before is not accounted for here, the same applies for redo logs probably.

In the end it depends on your needs for the size - do you need to know how much space to allocate for backups? #1 or #2 may be what you need, depending on your backup strategy and after accounting for compression. Do you need to know how big disks/cloud storage to pay for - #2 is probably the best bet.

I personally do not see much use for #3 in general, but you can use it to get some average storage needed for a record so you can project future grow of specific tables.

I am sure I missed some differences but hopefully nothing big.


Setup AlwaysOn with 2 Secondaries and not adding all the databases for 2nd Secondary

You cannot exclude databases from replicas in an availability group.  There may be a way to accomplish this with a hack, but it would be an unsupported configuration.

In order to accomplish what you are specifying, you need two availability groups.  Availability group A would have all three servers as replicas, and would contain only the databases that you want to be replicated to all three servers.

Availability group B would have two servers as replicas, and would contain the one database that you want to be replicated to only those two servers.


It depends entirely on the breakdown of work at an organization, and what each person's responsible for.  But they're just labels; someone titled 'software engineer' at one company might be 'programmer/analyst' at another company or even 'systems programmer', etc.   There is no hard border between the two.

Where I work, the 'software engineer' tends to be the person doing both the design and the actual implementation, and we don't have anyone titled 'DBA';  The maintenance type tasks that would typically be a DBA fall back to the general 'system administrators', based on the guidance that the 'software engineer' gave them.

In what I personally think is a best case scenario, you'd break the design into multiple parts:

data modeling
requirements for the data storage (record structure, size of tables, number of inserts/deletes/updates per second, how quickly processes need to complete, etc)
requirements for disaster recovery / continuity of operations (needs to be back up within (x) hours, can't have longer than an (y) hour maintenance window each (month) )
implementation of the storage (selection of the database software, selection of the physical storage, how the tables are spread out on the storage, etc.)
implementation of the backup plan

And then the maintenance type tasks:

tuning the database
tuning the queries
debugging when things go wrong
overseeing & verifying the backups
applying software updates

For most of these, they don't have to be done by a DBA; it could be done by a software engineer, or in the case of some of the maintenance tasks, a system administrator.

If you have people in both roles, you might have them confer and collaborate on the design and tuning (what they'd call in construction 'design-build') , or if it's a rush job, you might assign the various tasks between the two.  You might have other people involved, too : a 'software architect', 'data architect', an archivist, various programmers, system administrators, network administrators, security, etc.


Getting the primary key columns off a table

https://stackoverflow.com/questions/1214576/how-do-i-get-the-primary-keys-of-a-table-from-postgres-via-plpgsql ...

if schema is needed please modify slightgly to

SELECT
  pg_attribute.attname,
  format_type(pg_attribute.atttypid, pg_attribute.atttypmod)
FROM pg_index, pg_class, pg_attribute, pg_namespace
WHERE
  pg_class.oid = 'MY TABLE'::regclass AND
  indrelid = pg_class.oid AND
  nspname = 'MY CLASS' AND
  pg_class.relnamespace = pg_namespace.oid AND
  pg_attribute.attrelid = pg_class.oid AND
  pg_attribute.attnum = any(pg_index.indkey)
  AND indisprimary


I don't know XAMPP that well, but a quick search turned this up:

  How to find my.ini and my.cnf

  To find these MySQL config files, do this:

  Go to Folder Options: in any folder, go to the top horizontal main text menu >> Tools >> Folder Options
  Enable 'View Hidden Files and Folders', and 'View Protected System Files', save & exit
  Go to C Drive, locate and rename the my.ini file, which will now be visible. Rename it as my.ini.bak
  Go to /xampp/msql/bin - locate and rename my.cnf, to my.cnf.bak
  If there is no file my.cnf, but there is one called my - then that is the one you want. Rename it to my.cnf.bak

As for whether you can do this with an online site, you can change it online, but unless you update your my.cnf, the change will be lost after mysql restarts the next time.


Why are periodic restarts required to keep my instance performing well?

You say that everything is fine, then after a couple of weeks, performance drops.  (Usually, people claim that performance drops quickly, or at specific times, or at seemingly random intervals. That could mean bad I/O performance or lock storms or cpu-intensive queries running at wierd times, or a heavyweight scheduled job or lack of indexing or bad stats causing cpu-intensive queries or disk reads. Or other stuff.) Weeks is unusual.

My hypothesis is that another application on your server is leaking memory. I have seen this with virus software (every DBA's favorite server software villain) and 3rd party monitoring software. I would double check the memory usage of SQL Server, over time, and I'd grab all of the memory usage of all of the other applications on the box as well. If you have hard limits set on SQL Server's memory usage and have it set to not allow paging, it might be other apps that are getting paged out and eating up I/O capacity.

It's not hard to look for. If you are not already keeping metrics on the server, I would just start up Perfmon and have it grab a sample every 30 or 60 minutes. After a few days, you may see another applications memory usage creep upwards.

Are there error messages in the SQL Server log stating that "significant portions of sql server have been paged out"? That would also be a big clue.


how to disable access to mongo shell to anonymous users?

There is no access control enabled with MongoDB by default, so this is something you'll need to configure separately. Fortunately, it's not too difficult thanks to the well-written MongoDB Manual.
Here is the gist:

Start MongoDB without access control (which I believe you've already done)
Connect to the instance (which you've stated you've already done)
Create an administrator account:
use admin
db.createUser(
  {
    user: "adminny",
    pwd: "superSecretPassword!123",
    roles: [ { role: "dbOwner", db: "admin" } ]
  }
)

Re-start the MongoDB instance with access control.
mongod --auth --port 27017 --dbpath /data/db1

Note the --auth option in the startup.
Authenticate as the admin account:
mongo --port 27017 -u "adminny" -p "superSecretPassword!123" --authenticationDatabase "admin"

Important Note About Roles
There are several roles that are available, and choosing the wrong one can result in unnecessary frustration.

Role
Detail

root
This would be a root-level role, allowing the creation of user accounts, databases, and all administration-level actions one might need to do.

dbAdmin
Provides the ability to perform administrative tasks such as schema-related tasks, indexing, and gathering statistics. This role does not grant privileges for user and role management.

dbOwner
The database owner can perform any administrative action on the database. This role combines the privileges granted by the readWrite, dbAdmin and userAdmin roles.

userAdmin
Provides the ability to create and modify roles and users on the current database. Since the userAdmin role allows users to grant any privilege to any user, including themselves, the role also indirectly provides superuser access to either the database or, if scoped to the admin database, the cluster.

userAdminAnyDatabase
Provides the same access to user administration operations as userAdmin on all databases except local and config.

readWrite
Provides all the privileges of the read role plus ability to modify data on all non-system collections and the system.js collection.

You can read more about each of the Built-In Roles in the MongoDB Manual, which is very approachable.


Linking Products to Users Via Categories

I'd imagine using JOINs rather than subqueries would be the first step to enhancing the speed of this thing.

Very rough example:

SELECT p.*
FROM product p
INNER JOIN products_categories pc ON p.product_id = pc.product_id
INNER JOIN public.users_categories c ON pc.category_id = c.category_id
WHERE p.disabled = False AND c.user_id = 'some-user-id'

Not gone into a lot of detail there - one of more of your JOINs might need to be an OUTER.


Update queries slower after enabling SQL Server Full-Text Index

In my case I had to change an inefficient UPDATE statement:

Before:

UPDATE Customer
SET Rating = 'Not Rated'
WHERE ...

After:

UPDATE Customer
SET Rating = 'Not Rated'
WHERE ...
AND Rating <> 'Not Rated' -- THIS LINE WAS INSERTED

This improved performance from 4 minutes down to 5 seconds. The problem was that I used to update all rows, even if they already had the value that I wanted to assign.


Joining subquery from same table on 1=1 in Oracle

I think it should be this:

SELECT
    zip.zip,
    ROUND(SDO_GEOM.SDO_DISTANCE((MDSYS.SDO_GEOMETRY(2001, 4326, MDSYS.SDO_POINT_TYPE(inp.lng, inp.lat, NULL), NULL, NULL)), (SELECT geom FROM standard_zip WHERE zip = zip.zip), 0.005) / 1609.34, 2) AS dist,
    inp.lat,
    inp.lng
FROM standard_zip zip
   CROSS JOIN (SELECT x as lng, y as lat from standard_zip where zip = :zip) inp
WHERE zip.zip IN (
        SELECT zip
        FROM standard_zip c
        WHERE SDO_FILTER(c.geom, SDO_UTIL.CIRCLE_POLYGON(inp.lng, inp.lat, (:r * 1609.34), 5)) = 'TRUE'
            AND SDO_RELATE(c.geom, SDO_UTIL.CIRCLE_POLYGON(inp.lng, inp.lat, (:r * 1609.34), 5), 'mask=ANYINTERACT querytype = WINDOW') = 'TRUE'
        )
ORDER BY DIST;


Designing a database for a video game business domain with multiple many-to-many relationships

Yes, the identification of many-to-many (M:N for brevity) associations or relationships is a situation that a database practitioner faces quite commonly when laying out a conceptual schema. Associations of said cardinality ratios come about in business environments of very different nature, and when properly represented at the logical level by means of, e.g., a SQL-DDL arrangement, they do not introduce harmful redundancies.
In this way, the objective of a database modeling exercise should be to mirror the relevant characteristics of the business context of interest with high precision; therefore, if you identify correctly that there are numerous M:N associations, then you must express them in (a) the conceptual schema and also in (b) the respective logical-level declarations, no matter how many connections of that —or any other— kind of cardinality ratios have to be addressed.
Business rules
You have supplied a well-contextualized question, and have also clarified that the database you are working on is purely hypothetical, which is an important point since I esteem that a “real world” business scenario like the one under consideration would be much more extensive and, hence, would imply more complex informational requirements.
I decided to (1) make a few modifications and expansions to the business rules you have provided in order to (2) produce a more descriptive conceptual schema —although still rather hypothetical—. Here are some of the formulations that I put together:

A Party1 is either a Person or an Organization
A Party is classified by exactly-one PartyType
A PartyType classifies zero-one-or-many Parties
An Organization develops zero-one-or-many Products
A Product is either a System or a Game
A Product is classified by exactly-one ProductType
A System is catalogued by exactly-one SystemType
A Game can be played via one-to-many Systems
A System is used to play one-to-many Games
A Game is classified by zero-one-or-many Genres
A Genre classifies zero-one-or-many Games
A Product originates one-to-many Jobs
A Job is fulfilled by zero-one-or-many People, who are playing the Role of Collaborators
A Person is a Collaborator in zero-one-or-many Jobs

1 Party is a term used in legal contexts when referring to either an individual or a group of individuals that compose a single entity, so this denomination is suitable to represent People and Organizations.

IDEF1X diagram
Subsequently, I created the IDEF1X2 diagram shown in Figure 1 (make sure to click the link to see it in a higher resolution), consolidating in a single graphical device the business rules presented above (along with some others that seem relevant):

2 Integration Definition for Information Modeling (IDEF1X) is a highly recommendable data modeling technique that was established as a standard in December 1993 by the United States National Institute of Standards and Technology (NIST). It is based on (a) the early theoretical material authored by the sole originator of the relational model, i.e., Dr. E. F. Codd; on (b) the entity-relationship view of data, developed by Dr. P. P. Chen; and also on (c) the Logical Database Design Technique, created by Robert G. Brown.

As you can see, I have depicted only three M:N associations by way of the corresponding associative entity types, i.e.:

Collaborator
SystemGame
GameGenre

Among other aspects, there are two distinct supertype-subtype structures, where:

Person and Organization are mutually exclusive entity subtypes of Party, their entity supertype

Product is the supertype of System and Game, which in turn are mutually exclusive subtypes

In case you are not familiar with supertype-subtype associations, you might find of help, e.g., my answers to the questions entitled:

“Modeling a scenario in which each Music Artist is either a Group or a Solo Performer”
“How to model an entity type that can have different sets of attributes?”
“Developing a database for a funds transfers business where (a) people and organizations can (b) send and receive money”
“Modelling a database structure for multiple user types and their contact information”

Illustrative logical SQL-DDL layout
Successively, we must make sure that, at the logical level:

Each entity type is represented by an individual base table
Each single property of the applicable entity type is denoted by a particular column
An exact data type is fixed for each column in order to ensure that all the values it contains belong to a particular and well defined set, be it INT, DATETIME, CHAR, etc. (of course, when using, e.g., Firebird or PostgreSQL, you might like to employ the more powerful DOMAINs)
Multiple constraints are configured (declaratively) in order to guarantee that the assertions in form of rows retained in all the tables comply with the business rules determined at the conceptual level

So I declared the following DDL arrangement based on the IDEF1X diagram previously shown:
CREATE TABLE PartyType ( -- Stands for an independent entity type.
    PartyTypeCode CHAR(1)  NOT NULL, -- To retain 'P' or 'O'.
    Name          CHAR(30) NOT NULL, -- To keep 'Person' or 'Organization'.
    --
    CONSTRAINT PartyType_PK PRIMARY KEY (PartyTypeCode)
);

CREATE TABLE Party ( -- Represents an entity supertype.
    PartyId         INT       NOT NULL,
    PartyTypeCode   CHAR(1)   NOT NULL, -- To hold the value that indicates the type of the row denoting the complementary subtype occurrence: either 'P' for 'Person' or 'O' for 'Organization'.
    CreatedDateTime TIMESTAMP NOT NULL,
    --
    CONSTRAINT Party_PK            PRIMARY KEY (PartyId),
    CONSTRAINT PartyToPartyType_FK FOREIGN KEY (PartyTypeCode)
        REFERENCES PartyType (PartyTypeCode)
);

CREATE TABLE Person ( -- Denotes an entity subtype.
    PersonId        INT      NOT NULL, -- To be constrained as (a) the PRIMARY KEY and (b) a FOREIGN KEY.
    FirstName       CHAR(30) NOT NULL,
    LastName        CHAR(30) NOT NULL,
    GenderCode      CHAR(3)  NOT NULL,
    BirthDate       DATE     NOT NULL,
    --
    CONSTRAINT Person_PK PRIMARY KEY        (PersonId),
    CONSTRAINT Person_AK UNIQUE             (FirstName, LastName, GenderCode, BirthDate), -- Composite ALTERNATE KEY.
    CONSTRAINT PersonToParty_FK FOREIGN KEY (PersonId)
        REFERENCES Party (PartyId)
);

CREATE TABLE Organization ( -- Stands for an entity subtype.
    OrganizationId  INT      NOT NULL, -- To be constrained as (a) the PRIMARY KEY and (b) a FOREIGN KEY.
    Name            CHAR(30) NOT NULL,
    FoundingDate    DATE     NOT NULL,
    --
    CONSTRAINT Organization_PK        PRIMARY KEY (OrganizationId),
    CONSTRAINT Organization_AK        UNIQUE      (Name), -- Single-column ALTERNATE KEY.
    CONSTRAINT OrganizationToParty_FK FOREIGN KEY (OrganizationId)
        REFERENCES Party (PartyId)
);

CREATE TABLE ProductType ( -- Represents an independent entity type.
    ProductTypeCode CHAR(1)  NOT NULL, -- To enclose the values 'S' and 'G' in the corresponding rows.
    Name            CHAR(30) NOT NULL, -- To comprise the values 'System' and 'Person' in the respective rows.
    --
    CONSTRAINT ProductType_PK PRIMARY KEY (ProductTypeCode)
);

CREATE TABLE Product ( -- Denotes an entity supertype.
    OrganizationId  INT      NOT NULL,
    ProductNumber   INT      NOT NULL,
    ProductTypeCode CHAR(1)  NOT NULL, -- To keep the value that indicates the type of the row denoting the complementary subtype occurrence: either 'S' for 'System' or 'G' for 'Game'.
    CreatedDateTime DATETIME NOT NULL,
    --
    CONSTRAINT Product_PK               PRIMARY KEY (OrganizationId, ProductNumber), -- Composite PRIMARY KEY.
    CONSTRAINT ProductToOrganization_FK FOREIGN KEY (OrganizationId)
        REFERENCES Organization (OrganizationId),
    CONSTRAINT ProductToProductType_FK  FOREIGN KEY (ProductTypeCode)
        REFERENCES ProductType (ProductTypeCode)
);

CREATE TABLE SystemType ( -- Stands for an independent entity type.
    SystemTypeCode CHAR(1)  NOT NULL,
    Name           CHAR(30) NOT NULL,
     --
    CONSTRAINT SystemType_PK PRIMARY KEY (SystemTypeCode)
);

CREATE TABLE MySystem ( -- Represents a dependent entity type.
    OrganizationId   INT      NOT NULL, -- To be constrained as (a) the PRIMARY KEY and (b) a FOREIGN KEY.
    SystemNumber     INT      NOT NULL,
    SystemTypeCode   CHAR(1)  NOT NULL,
    ParticularColumn CHAR(30) NOT NULL,
    --
    CONSTRAINT System_PK              PRIMARY KEY (OrganizationId, SystemNumber),
    CONSTRAINT SystemToProduct_FK     FOREIGN KEY (OrganizationId, SystemNumber)
        REFERENCES Product (OrganizationId, ProductNumber),
    CONSTRAINT SystemToSystemType_FK  FOREIGN KEY (SystemTypeCode)
        REFERENCES SystemType (SystemTypeCode)
);

CREATE TABLE Game ( -- Denotes an entity subtype.
    OrganizationId INT      NOT NULL, -- To be constrained as (a) the PRIMARY KEY and (b) a FOREIGN KEY.
    GameNumber     INT      NOT NULL,
    SpecificColumn CHAR(30) NOT NULL,
    --
    CONSTRAINT Game_PK          PRIMARY KEY (OrganizationId, GameNumber),
    CONSTRAINT GameToProduct_FK FOREIGN KEY (OrganizationId, GameNumber)
         REFERENCES Product (OrganizationId, ProductNumber)
);

CREATE TABLE Genre ( -- Stands for an independent entity type.
    GenreNumber INT      NOT NULL,
    Name        CHAR(30) NOT NULL,
    Description CHAR(90) NOT NULL,
    --
    CONSTRAINT Genre_PK  PRIMARY KEY (GenreNumber),
    CONSTRAINT Genre_AK1 UNIQUE      (Name),
    CONSTRAINT Genre_AK2 UNIQUE      (Description)
);

CREATE TABLE SystemGame ( -- Represents an associative entity type or M:N association.
    SystemOrganizationId INT      NOT NULL,
    SystemNumber         INT      NOT NULL,
    GameOrganizationId   INT      NOT NULL,
    GameNumber           INT      NOT NULL,
    CreatedDateTime      DATETIME NOT NULL,
    --
    CONSTRAINT SystemGame_PK         PRIMARY KEY (SystemOrganizationId, SystemNumber, GameOrganizationId, GameNumber), -- Composite PRIMARY KEY.
    CONSTRAINT SystemGameToSystem_FK FOREIGN KEY (SystemOrganizationId, SystemNumber) -- Multi-column FOREIGN KEY.
        REFERENCES MySystem (OrganizationId, SystemNumber),
    CONSTRAINT SystemGameToGame_FK   FOREIGN KEY (SystemOrganizationId, GameNumber) -- Multi-column FOREIGN KEY.
        REFERENCES Game (OrganizationId, GameNumber)
);

CREATE TABLE GameGenre ( -- Denotes an associative entity type or M:N association.
    GameOrganizationId INT      NOT NULL,
    GameNumber         INT      NOT NULL,
    GenreNumber        INT      NOT NULL,
    CreatedDateTime    DATETIME NOT NULL,
    --
    CONSTRAINT GameGenre_PK        PRIMARY KEY (GameOrganizationId, GameNumber, GenreNumber), -- Composite PRIMARY KEY.
    CONSTRAINT GameGenreToGame_FK  FOREIGN KEY (GameOrganizationId, GameNumber)
        REFERENCES Game (OrganizationId, GameNumber), -- Multi-column FOREIGN KEY.
    CONSTRAINT GameGenreToGenre_FK FOREIGN KEY (GenreNumber)
        REFERENCES Genre (GenreNumber)
);

CREATE TABLE Job ( -- Stands for an associative entity type or M:N association.
    OrganizationId  INT      NOT NULL,
    ProductNumber   INT      NOT NULL,
    JobNumber       INT      NOT NULL,
    Title           CHAR(30) NOT NULL,
    CreatedDateTime DATETIME NOT NULL,
    --
    CONSTRAINT Job_PK          PRIMARY KEY (OrganizationId, ProductNumber, JobNumber), -- Composite PRIMARY KEY.
    CONSTRAINT Job_AK          UNIQUE      (Title), -- Single-column ALTERNATE KEY.
    CONSTRAINT JobToProduct_FK FOREIGN KEY (OrganizationId, ProductNumber) -- Multi-column FOREIGN KEY.
        REFERENCES Product (OrganizationId, ProductNumber)
);

CREATE TABLE Collaborator ( -- Represents an associative entity type or M:N association.
    CollaboratorId   INT      NOT NULL,
    OrganizationId   INT      NOT NULL,
    ProductNumber    INT      NOT NULL,
    JobNumber        INT      NOT NULL,
    AssignedDateTime DATETIME NOT NULL,
    --
    CONSTRAINT Collaborator_PK         PRIMARY KEY (CollaboratorId, OrganizationId, ProductNumber, JobNumber), -- Composite PRIMARY KEY.
    CONSTRAINT CollaboratorToPerson_FK FOREIGN KEY (CollaboratorId)
    REFERENCES Person (PersonId),
    CONSTRAINT CollaboratorToJob_FK    FOREIGN KEY (OrganizationId, ProductNumber, JobNumber) -- Multi-column FOREIGN KEY.
       REFERENCES Job (OrganizationId, ProductNumber, JobNumber)
);

It is opportune to stress that there are declarations of composite PRIMARY KEY constraints across several tables, which stand for the hierarchy of connections that take place among conceptual entity types, arrangement that can be very beneficial with respect to data retrieval when, e.g., expressing SELECT operations that include JOIN clauses to obtain derived tables.
Yes, (i) every M:N association and (ii) every one of the associated entity types are denoted by (iii) the corresponding table in the logical DDL structure, so pay special attention to the PRIMARY and FOREIGN KEY constraints (and the notes I left as comments) of tables representing these conceptual elements, because they assist in ensuring that the connections between the relevant rows meet the applicable cardinality ratios.
The usage of composite keys was introduced by Dr. E. F. Codd from the very origin of the relational paradigm, as demonstrated in the examples he included in his 1970 seminal paper entitled A Relational Model for Large Shared Data Banks (which, precisely, also presents the most elegant method to handle conceptual M:N associations).
I put up  a db<>fiddle and a SQL Fiddle, both running on Microsoft SQL Server 2014, so that the structure can be tested “in action”.
Normalization
Normalization is a logical-level procedure that implies, basically speaking:

Eliminating non-atomic columns via first normal form so that data manipulation and constriction are much easier to cope with by the data sublanguage of use (e.g., SQL).

Getting rid of undesirable dependencies among the columns of a specific table by virtue of the successive normal forms to avoid update anomalies.

Naturally, one has to take into account the meaning carried by the table(s) and column(s) at issue.
I like to think of normalization as a test founded on science that a designer applies to the pertinent elements once he or she has delineated a stable logical-level arrangement in order to determine whether its items comply with every one of the normal forms or not. Then, if needed, the designer takes the appropriate correcting measures.
Redundancy
In the relational model, while duplication of values contained in columns is not only acceptable but expected, duplicate rows are forbidden. To that extent, as far as I can see, duplicate rows and other kinds of harmful redundancies are prevented in all the tables comprised in the logical layout exposed before, perhaps you would like to clarify your concern in this regard.
Anyway, you can certainly (a) evaluate on your own said structure by dint of the normal forms to define if it meets the requirements and (b) modify it if necessary.
Related resources

In this series of posts I present some deliberations about a straightforward M:N association that can interrelate the instances of two different entity types.
In this other one I propose an approach to handle an occurrence of the “Bill of Materials” or “Parts Explosion” construct, in which I describe how to connect distinct instances of the same type of entity.

Ternary associations
There is another important aspect that you brought up via comments (posted in an now-deleted answer):

Every time I try to make a bridge the elements in that bridge also have a Many to Many, I'm under the impression that isn't allowed or at least discouraged.

That circumstance seems to indicate that one of your concerns has to do with conceptual ternary associations. Basically speaking, this sort of associations comes about when there exists (1) a relationship involving (2) two other relationships, in other words “a relationship between relationships” —a typical situation too, since a relationship is an entity in its own right—.
These arrangements, when suitably managed, do not bring harmful redundancies either. And, yes, if there is a certain use case where you identify that such relationships present themselves among “real world” entity types, you have to (i) model and (ii) declare them with accuracy at the logical level.

Here is a question and answer were we analyze a domain of discourse about surveys, which includes an example of a ternary association.
In this very good answer, @Ypercube presents a diagram and the respective DDL structure for an interesting diamond shaped relationship, which is very similar to this class of scenarios.


Oracle: Full database (user) restore requires drop?

I haven't found any impdp commands that would allow you to drop existing schema prior to importing data. But you should know that Data Pump command-line utilities are based around PL/SQL packages DBMS_DATAPUMP and DBMS_METADATA. Thus you may well write your own PL/SQL scripts to automate your data move calling programs in DBMS_DATAPUMP directly. And since this is PL/SQL, you may just invoke DROP USER statement in your scripts prior to calls to programs in DBMS_DATAPUMP and DBMS_METADATA.

I think this approach is more flexible, and to further automate your tasks you can employ Oracle Scheduler.


So finally sorted this out.  There was an orphaned instance within the MDW.  I removed this, which took at least a day to kill off.  Trying to create the indexes failed through the gui due to a time out.  Using activity monitor I could see that there were still huge waits on the server.  I stopped all data collection for the MDW and disabled all the jobs associated, including the purge job.  I then stopped this job as it was still running.  Created the indexes using t-sql.  Re ran the jobs and I am now back with the baseline for this server.  I have gone from over a day to purge data to about 15 minutes so all in all much happier.


How to read and increment a number in text file using TSQL

The simplest way to read and increment a numeric value in a text file would be to not do it in SQL Server, though you can still use SQL Server to cause it to happen.

You can create a CMD script as follows that accepts a single input parameter for the file name and then increments the value inside of that file by 1. If the file name does not exist, it will be created with an initial value of 0.

Just create a new text file in Windows Explorer and replace the entire name, including the extension, with:  IncrementValue.cmd.

Then edit the file and paste in the following:

@ECHO OFF

SETLOCAL ENABLEDELAYEDEXPANSION

IF NOT EXIST %1 ECHO 0 > %1

FOR /F %%A IN ('TYPE %1') DO (
    SET /A NewTempValue=%%A+1
    ECHO !NewTempValue! > %1
)

REM Uncomment the following line to get the value back in xp_cmdshell
REM ECHO !NewTempValue!

Then just call it in SQL Server, either from a Command-step in a SQL Server Agent job, or via xp_cmdshell like so:

-- remove the ", NO_OUTPUT" if you need to get the new value back
EXEC xp_cmdshell N'IncrementValue.cmd MyFile.txt', NO_OUTPUT;

Or, this same operation could be done in a PowerShell script.


How to use aggregate functions on each array in a table, in Postgres?

You need a scalar subquery:
select (select sum(i)
        from unnest(scores) as x(i)) as total
from games;


Remote connection issue from windows machine

Your LOCAL_LISTENER parameter is set to listener_orcl, which is defined in your tnsnames.ora as being on localhost port 1522. But your listener is on RAJHEGDE-LAP2.oradev.oraclecorp.com port 1522, so the database is unable to register itself. You can change your tnsnames.ora entry to match the listener.ora definition. After the database is restarted, or you do alter system register, the lsnrctl status output will show your service name as registered as well.

But your SQL Developer connection isn't getting that far. You can't telnet to the listener port, which means either a firewall is present (which you've assured us is not the case), or you're running a virtual machine you haven't told us about (I'm suspicious about the .oradev.oraclecorp.com domain the listener is reporting), or you're connecting to the wrong IP address.

Check that RAJHEGDE-LAP2 on machine A and RAJHEGDE-LAP2.oradev.oraclecorp.com on both machine A and machine B, and whatever host name you are using for your telnet test and connection, all resolve to the same IP address. You can also use netstat on machine A to check which IP address is listening on port 1522. You may have more than one interface and the listener isn't on the one you expect - perhaps because of a mismatch between a static address in your hosts file and what's assigned by DHCP and/or in DNS, for example.


Is it a good idea to use COLUMN AS (left([Another_Column],(4)) instead of LEFT in the select?

If the computed column isn't persisted or indexed, it's only a virtual column (expression), and it's not taking up any "space".


Deciding EC2 Instance type & Volume IOPS for Mongo DB

You should be able to view your current IOPS on the monitoring page of AWS - at least you can for RDS instances.

If this is EC2 and not RDS (RDS is backed by EC2 instances) you probably need to enable enhanced monitoring for those instances.


"Filter:" instead of "Index cond:" in index production usage

The planner does weigh between the options and chooses the one that it thinks will be cheaper, of course based on the estimates it has at hand at the time.
When having the ANY in the "Index Cond", it needs to re-descend the index one time for each of the 19 members of your list.  Each time it re-descends, it thinks it will land on a different index leaf page costing one random_page_cost (a questionable assumption perhaps, but the alternative assumption would be just as questionable).  This leads to a high estimated cost.  It needs to pay this cost even for the members that don't end up matching to any index entry.
When the ANY is in the Filter, it thinks it will have to scan all rows meeting the provider_id = 38 AND connection_id = 1175966 condition, and apply the ANY to each one.  But the thing is, we have no way of knowing how many rows it thinks that that will be.  The EXPLAIN reports how many rows it thinks will survive the ANY filter, but doesn't report to us how many it thinks will be tested but fail (it has that estimate under the hood somewhere, it just doesn't make it visible to us).  EXPLAIN ANALYZE reports how many actually failed, but the planner can't plan retroactively based on that value of course.
It looks to me like your production server thinks only one row will have to be tested against the filter, which explains why it has such a low cost estimate and gets chosen.  Whereas your dev server has a more accurate estimate of how many rows need to be tested.  The way to test this hypothesis would be to run slightly different query and see what it gives for the row estimates on each machine.
explain SELECT * FROM reports WHERE "provider_id" = 38 AND "connection_id" = 1175966;

There is another possibility that the executor doesn't implement, which means the planner does not consider it.  It could scan the whole section of the index where "provider_id" = 38 AND "connection_id" = 1175966 and test the ANY against the id_on_site value stored in the index, rather than jumping to the table to test the id_on_site stored there.  I think the reason it does not do this is that it does not trust the ANY/IN test not to throw errors, so it is not going to risk testing it in the index and possibly throwing an error when that tuple in the table might be obsolete anyway.  No one would thank the PostgreSQL developers for making it throw errors based on the invisible detritus!  Now in this case it should be safe as it just a combination of = and OR, both of which are safe.  But I guess the system is not clever enough to know that.
So what can you do about it?  One thing would be fix the estimate of how many rows will match the provider_id = 38 AND connection_id = 1175966 condition on production (in which the first step would be finding out what that estimate is).  Another would be to get an index-only scan by adding more columns to the index or removing columns from the select list.  If you can get an index-only scan, it would test the ANY condition against the index value rather than the table value as long as the table page is marked all visible.  Another thing you could do is switch your RDS to a storage class which doesn't suck. It appears to be taking over 20ms per random page read, which as Erwin points out is horrible.


The likely problem is that, by joining to those additional tables, rows in quoteitem become represented more than once in the intermediate result. That is, you have a one-to-many join at some point. SQL Server then obediently sums up those rows multiple times. It's a mistake that's very easy to make.

The simple workaround is to perform your aggregate calculations before doing any one-to-many joins. It might look something like this.

SELECT q.sumQty,
       p.ProdID,
       p.CatID
FROM (
        SELECT ProdID, SUM(qty) AS sumQty, ...other columns...
        FROM quoteitem
        GROUP BY ProdID
        HAVING SUM(qty) > 10
    ) q
       INNER JOIN cds_prod AS p
         ON p.ProdID COLLATE Latin1_General_CI_AS = cnetprodid
       INNER JOIN cds_atr AS a
         ON a.ProdID COLLATE Latin1_General_CI_AS = p.ProdID
       INNER JOIN lan_cat_sections AS c
         ON c.attID = a.atrID
             OR ( LEN(ISNULL(c.attID, '')) = 0
                  AND c.cat_id LIKE p.CatID + '%' )
                AND ( ( c.[include] = 0
                        AND a.valID NOT IN (SELECT Value
                                            FROM   Split(c.valID, ',')) )
                       OR ( c.[include] = 1
                            AND a.valID IN (SELECT Value
                                            FROM   Split(c.valID, ',')) ) )
WHERE  ordered = 1
       AND sageSOPOrderReturnLineID IS NOT NULL
       AND LEN(LTRIM(RTRIM(cnetprodid))) > 0
       AND cnetprodid LIKE 'S%'
       AND price > 10
       AND created > DATEADD(MONTH, -3, GETDATE())
GROUP  BY p.ProdID,
          p.CatID,
          a.prodID
ORDER  BY q.sumQty DESC


Set long_query_time=0 and turn on the slowlog to a FILE (in my.cnf, and restart).  Then the slowlog will contain a variety of info about every command executed, including the query time.

You might also want to run pt-query-digest to summarize the slow log.  This will include min/max/avg/etc for each 'different' query.


Mongodb Bulk find and remove is taking long time

This shouldn't have anything to do with bulk operations. The find operation must read through the collection, which takes time on 1million documents.

Adding an index on a should improve the speed dramatically.


How to SELECT a sibling in an adjacency list type table?

It seems you need

SELECT t1.title taskTitle,
       t2.title projectTitle
FROM tasks t1
JOIN tasks t2 ON t1.parent_id = t2.parent_id
             AND t1.position + 1 = t2.position
WHERE t1.id = 192


This works for me.
You say the ssh command is trying to connect to port 22.  That is what it should be doing, in the absence of -p telling it to do otherwise.  It connects to port 22 on host 123.45.67.89 to set up the tunnel.  If 123.45.67.89 runs its sshd on a nonstandard port, then you need to specify it with -p.
Once that is done, then connections to localhost:42042 on the same machine where you ran the ssh command (your aws node) will get routed through 123.45.67.89 and on to psql.example.com on port 5432.


SQLite3 - remove duplicates by multiple columns

Supposing you have no foreign keys referencing that table, you could do something like

create table some_table as
select min(id), name, age, x
from t
group by name, age, x

then you can drop the old table, rename the new table so that it has the same name as the old one before, and create indexes and other things you need on that table.