Trigger to automatically add values to a table in Oracle

Sounds like you just want a basic AFTER INSERT trigger. Then you can access the inserted data via :new. Something like:

CREATE OR REPLACE TRIGGER insert_data
AFTER INSERT
ON OWNER.TABLE
FOR EACH ROW
BEGIN
INSERT INTO OWNER.OTHER_TABLE
(COL1,
COL2,
COL3)
VALUES
(:NEW.COL1,
:NEW,COL2,
:NEW,COL3)
END;


I think you are looking something like this.
This will list all products in the same topic id..

SELECT
   productClassification.asin
 , productClassification.topic
 , productClassification.weight
FROM (
 SELECT
   productClassification.topic
 FROM
   productClassification
 WHERE
   productClassification.asin = 'B01LYQ7HHQ'
)
 AS
 productClassification_filter
INNER JOIN
 productClassification
ON
 productClassification.topic = productClassification_filter.topic
AND
  productClassification.asin != 'B01LYQ7HHQ'
ORDER BY
   productClassification.topic ASC
 , productClassification.weight ASC

Result

asin         topic         weight
----------  ------  ---------------
B01MATR1ZN       1    99.1049586790
B01M7MPM53       1    99.1240145475
B01KWK92UQ       1    99.5800318809
B01LXSHRCW       1    99.6681413232
B01LZF3T03       1    99.6694739441
B01M11J5C5       1    99.6694741630
B01M11HUG0       1    99.6694747525
B01M1DBCJR       1    99.6747018048
B01M1DAC7T       1    99.6759819038
B01M1DAN1S       1    99.6759829421
B01M1DCIFY       1    99.6759830134

But if you want to limit per topic id you need to use user variables.
This query will show the 3 best matching products per topic id

SELECT
   productClassification_ordered_ranked_limited.ASIN
 , productClassification_ordered_ranked_limited.topic
 , productClassification_ordered_ranked_limited.weight
FROM (
  SELECT
    @topic := NULL
  , @rank := 0
)
 AS
  init_user_vars
CROSS JOIN (

SELECT
   productClassification_ordered_ranked.asin
 , productClassification_ordered_ranked.topic
 , productClassification_ordered_ranked.weight
 , productClassification_ordered_ranked.rank
FROM (
  SELECT
     productClassification_ordered.asin
   , productClassification_ordered.topic
   , productClassification_ordered.weight
   , @rank := (
    CASE
     WHEN @topic := productClassification_ordered.topic
     THEN @rank + 1
     ELSE @rank := 0
    END
     ) AS rank
   , (@topic := productClassification_ordered.topic)
  FROM (
    SELECT
      productClassification.asin
    , productClassification.topic
    , productClassification.weight
    FROM (
      SELECT
        productClassification.topic
      FROM
        productClassification
      WHERE
        productClassification.asin = 'B01LYQ7HHQ'
      )
      AS
        productClassification_filter
      INNER JOIN
        productClassification
      ON
        productClassification.topic = productClassification_filter.topic
      AND
        productClassification.asin != 'B01LYQ7HHQ'
      ORDER BY
        productClassification.topic ASC
      , productClassification.weight ASC

    )
    AS
      productClassification_ordered
  )
   AS
     productClassification_ordered_ranked
  )
   AS
     productClassification_ordered_ranked_limited
 WHERE
   productClassification_ordered_ranked_limited.rank <= 3 -- limit 3 per topic id

Result

ASIN         topic         weight
----------  ------  ---------------
B01MATR1ZN       1    99.1049586790
B01M7MPM53       1    99.1240145475
B01KWK92UQ       1    99.5800318809


mongodb inMemory engine available in the community edition?

As at MongoDB 3.4, no plans have been announced to make the In Memory Storage Engine available in the MongoDB Community Edition. I suspect this is likely to remain as an Enterprise-only feature for the foreseeable future given the more niche use cases for in-memory storage, however the definitive source will be the mongodb.com website and any future major release announcements.

FYI, you can subscribe to driver/server release announcements via the mongodb-announce group.


One thing to note is that xp_sqlmaint is scheduled to removed in the future and is not the recommended method for running the maintenance plans. (Though it is still supported at this time.)

For SQL Server maintenance I would recommend downloading the Ola Hallengren solution at https://ola.hallengren.com/.  This is a freely provided product for managing the integrity checks as well as, if you wish, to automate your database backups. And Ola is quick to respond with personal help.

These are highly respected scripts, which I have been using for several years.

You seem to have a corruption problem if the -CkDBRepair cannot finish the check. If that is the case, then you will need to be careful in how you fix the problem so as to avoid as much loss as possible. So please carefully read:

DBCC CHECKDB (Transact-SQL)

Be sure to read the warnings on DBCC CHECKDB and the impact of allowing data loss when resolving the problem.


Upgrading Cassandra from 1.1.0 to latest

what would be the procedure to update from my 1.1.0 version to the
  latest version without having any downtime ?

You're going to need to do 3 upgrades in-total:

upgrade 1.1 to 1.2
upgrade 1.2 to 2.0
upgrade 2.0 to 2.1

And you'll want to follow these steps for each upgrade:

Stop the node.
Back up your configuration files. Depending on how you install the
product, these files may be overwritten with default values during
the installation.
Install the new version of Cassandra.
Configure the new product. Using the backups you made of your
configuration files, merge any modifications you have previously
made into the new configuration files for the new version.
Configuration options change often, so be sure to double check the
version restrictions for additional steps and changes regarding
configuration.
Start the node.
If you are upgrading from a major version (for example, from 1.2 to
2.0) or a major point release (for example, from Cassandra 2.0 to 2.1), upgrade the sstables on each node.

$ nodetool upgradesstables
Check the logs for warnings, errors and exceptions. Repeat on each
node in the cluster.

For questions on those steps, refer to the DataStax Upgrade Guide.


Automate update columns in a view with trigger function in plpgsql

I was able to solve my problem in a more manual way. It took some time to complete for all columns but at least it is done. Heres an example, of what I did.

-- update col_d
        IF (NEW.col_d != OLD.col_d) THEN
          UPDATE poi
          SET col_d = NEW.col_d
          WHERE poi_id = NEW.poi_id;
          RAISE NOTICE 'col_d was % , changed to %',OLD.col_d,NEW.col_d;
        END IF;
-- in case col_d is NULL
        IF OLD.col_d IS NULL AND NEW.col_d IS NOT NULL THEN
          UPDATE poi
          SET col_d = NEW.col_d
          WHERE poi_id = NEW.poi_id;
          RAISE NOTICE 'col_d updated NULL to %', NEW.col_d;
        END IF;


I'm guessing here based on how I'd implement MIRROR TO:

Probably, SQL Server streams the backup data to two backup devices if you use MIRROR TO. This means that every block they read is written two times. The data stream has a fork.

What they are certainly not doing is write the data once and then copy it over. That would a) incur an additional read pass over the data and b) also open a window for data corruption while only a single copy of the backup exists. From an implementors standpoint it is less work to fork the stream and it has a better result. That's why I think they're doing that way.

This means that MIRROR TO is less resource intensive than copying the data files after the backup has succeeded. It also has less room for data corruption. Data corruption is rare but a practical concern. Memory, disk and network all can have undetected bit flips. (Yes, TCP does not 100% protect from that.)

Advice given the fact that Enterprise Edition will become available to you: Use MIRROR TO. It results in less resource usage and less potential for (undetected) backup corruption. It also automates stuff that you can get wrong when you are doing it manually. SQL Server is certainly tested better than most in-house script development. The only reason not to use it would be if your backup process cannot easily be modified to use it.

One additional point: If the mirror cannot be written the backup will fail. This can be a downside (you probably exceed your RPO objective). It can be an upside because errors are more likely to be detected.

As mentioned by Paul White in the comments: If one of the backup destinations is slow (or all data is flowing over the same saturated network link) the backup might take a lot longer than before. In case of a saturated network link it might be faster to locally duplicate the backup file on the target server.

This theory can be tested: Back up the database to two IO devices that have identical perf characteristics using MIRROR TO. If I'm correct you should observe only a tiny slowdown because the writes are streaming and parallel.

I performed this benchmark myself. Backing up from SSD (very fast) to two (almost) identical magnetic drives. Using data compression (but the CPU was not maxed out).

Mirror: 1:37.
No mirror: 1:10 to drive 1.
No mirror: 1:14 to drive 2.

This slowdown (1:37 vs. 1:14) is well within the tolerance of my theory. There seem to be some overheads but there certainly is no separate mirroring/copy phase. Using Process Explorer I observed IO measured in MB/sec. It was fairly constant. This also hints that there is no separate phase. Writes seem to be parallel to both drives.


Is MongoDB a good fit to track a stock portfolio?

The "Stocks information" domain potentially fits well with a document database, such as MongoDB. The information available for different financial instruments might vary substantially, which means that a loose schema and the ability to store in-depth information in loosely defined sub-document structures could be useful. Also, you may want to extend the information scope gradually over time, in directions that may be hard to anticipate, which could be another argument in favor of a loose schema for the storage.

However, the other four data domains are typical examples of what a relational database is optimized for. While there may perhaps exist other, less widely used document databases that provide sufficient integrity constraint and join functionality to work well in this scenario, MongoDB certainly does not.

If the data really must be stored in MongoDB, the best would probably be to put all portfolio, transaction and holding information of each owner, along with cached price data for relevant instruments, in a single document. The application would then handle all necessary calculations when a stock owner makes a transaction, which would already be more complicated than achieving the same thing using built-in functionality for triggers, constraints and views in relational databases, but could, with some effort, be made to work reliably and with reasonable performance.

Once data for several owners must be aggregated, or when already loaded price information has been determined to contain errors, requiring all ownership documents to be recalculated, you will, however, have to spend even more time doing things that would be trivial with a relational database solution. Performance is also likely to be worse.

If there are strong other reasons to use a document database, and a solution with two concurrent database types is not acceptable, it might be worth the trouble to use MongoDB, but otherwise, a relational database will be the easiest and most robust solution.

Also, there are now relational databases with reasonably good document storage functionality. Microsoft SQL Server, for example, has JSON column support since version 2016, with query support for document content that could be sufficient for fairly complex document-based financial instrument information models.


What is the relationship between ADO.NET and Npgsql?

From Microsoft's docs about ADO.NET

ADO.NET is a set of classes that expose data access services for .NET Framework programmers. ADO.NET provides a rich set of components for creating distributed, data-sharing applications. It is an integral part of the .NET Framework, providing access to relational, XML, and application data. ADO.NET supports a variety of development needs, including the creation of front-end database clients and middle-tier business objects used by applications, tools, languages, or Internet browsers.

From Npgsql's docs

Npgsql is an open source ADO.NET Data Provider for PostgreSQL, it allows programs written in C#, Visual Basic, F# to access the PostgreSQL database server. It is implemented in 100% C# code, is free and is open source.
In addition, providers have been written for Entity Framework Core and for Entity Framework 6.x.

What's a data provider? I think the one of the best answers come from the Wikipedia article about ADO.Net Data Providers:

An ADO.NET data provider is a software component that interacts with a data source. ADO.NET data providers are analogous to ODBC drivers, JDBC drivers, and OLE DB providers.
ADO.NET providers can be created to access such simple data stores as a text file and spreadsheet, through to such complex databases as Oracle Database, Microsoft SQL Server, MySQL, PostgreSQL, SQLite, IBM DB2, Sybase ASE, and many others. They can also provide access to hierarchical data stores such as email systems.

So I wouldn't say that Npgsql is not a subset of ADO.Net. It is, to some extent, a piece of software containing a collection of classes that implement a certain number of standard interfaces; which will be the ones considered "essential to every data provider" and a few of the "optional ones". You should take a look as well to ADO.NET Architecture


Update my local db based on scripts executed in my server database

Rather than trying Opensource or free tools, I would highly recommend using Redgate tools for schema comparison. (note: I am not working for or affiliated to Redgate, but have and is using the schema and data compare and trust me they are life saving !)

For sql server : SQL Compare.

Useful resource for automating using Powershell or command line.

If you want to code the solution by yourself using Powershell, then look at Deploying Database Changes with PowerShell

Note: Just to mention, there is Data compare to sync data as well.


MySQL Utility mysqldbcompare (https://dev.mysql.com/doc/mysql-utilities/1.5/en/mysqldbcompare.html) may be of help. It does provide details on any mismatches it finds when comparing objects (not just tables) on two MySQL Servers.


Is it possible you had just an empty space highlighted in SSMS when you ran the query? If so, it'll still say "query executed successfully" even though nothing actually executed. If you're able to create the index now, then it definitely didn't create the first time.


Is it possible to get the information of which user changed what in a database?

The right direction for this is to use an audit logging plugin. There are some choices out there. Please check out the following post for more information

http://www.mysqlperformanceblog.com/2014/05/20/database-auditing-alternatives-mysql/

My personal preference is the Percona Audit Plugin but you should be using Percona Server (MySQL Alternative) for this.


I would handle it like a normal many-to-many relationship, creating a table that holds question_id and option_id. Then, since you can have sub options, i would add another field containing the primary key of this table:

CREATE TABLE [question_option_rel] (
    [id] int IDENTITY (0,1) PRIMARY KEY,
    [id_question] INT FOREIGN KEY REFERENCES [question]([id]),
    [id_option] INT FOREIGN KEY REFERENCES [option]([id]),
    [parent_option] INT FOREIGN KEY REFERENCES [question_option_rel]([id])
)


MySQL - Simple update is very slow and causing high load average with large amount of server

Get rid of indexes on flags (etat_promo).  The Optimizer almost never uses such; it will prefer to do a table scan.  If you have some query that you think might be using that index, run the EXPLAIN and show us the query for further discussion.


When is the modify_date of an object updated?

What could be altering, or appearing to alter, functions during a database restore?

You can find it using default trace or use SQL Server DDL Triggers to Track All Database Changes. You can even use XEvents depending on your comfort level.

I would say, there has to be some process that does the alter. SQL Server just do not alter selective objects after a db restore.


Always On/SQL Server Replication for Dummies

First on AGs.  To have a readable AG replica, both the primary and secondary SQL instance must be Enterprise Edition.  For SQL 2016 and before you also must have a Windows Server Failover Cluster (although it doesn't need any shared storage).  So there's no extra hardware required; the Windows Servers just need to be able to talk to each other, and be in the same Domain.

For SQL 2017 and after you can create an AG replica without a Failover Cluster (and therefore without automatic failover).  See Read-scale Availability Groups.

If your secondary SQL instance is not Enterprise Edition, you can use Backup and Restore (eg Log Shipping), or Transactional Replication to maintain a second, readable copy of your database.  With any backup-based solution the replica will not be near-real-time, and you will have to briefly disconnect the readers to restore the latest backup.

With SQL 2016 and later, there are additional features to enable you to run reporting workloads directly on your production database.  This uses features including, READ COMMITTED SNAPSHOT isolation, Resource Governor, Updatable Columnstore Indexes with Compression Delay, and optionally In-Memory OLTP, called collectively Real-Time Operational Analytics.


Is MongoDB Connections getting too high?

RAM, mainly. Every connection gets a stack allocated, at the size of 1MB. The more connections you have, the more RAM is needed for them and the less RAM is available for keeping indices or the working set of data in RAM.

So with your 19314 connections, roughly 19GB of RAM is used for connections. That's roughly a third of your available RAM – which is too much, from my point of view. What is acceptable has very much to do with your use cases, performance needs and whatnot. Finding out an acceptable RAM utilization is out of scope of an answer and can take many hours of analysis and optimization.


Should I use backup compression for databases where all data is already compressed?

Yes, in my opinion you should still use compression when backing up, even if the data is compressed. (Exceptions noted below.)

The compression used during backup is able to work across all of the data, unlike page and row compression, which are constrained to only considering the data in each individual page, one page at a time. Think about a phone book in a large metro are: imagine the difference between being able to use dictionary compression for all instances of the word Smith on one page, and then for all instances of the word Smith across all pages. That's similar to the improvement backup compression can add on top of already compressed data.

In my testing, using backup compression on top of page/row compression always yielded smaller backups and completed faster. There is also a lot of interesting commentary where others have confirmed the same. I don 't want to copy all of that here but you can read it at the following URL:

https://sqlblog.org/2010/03/15/yes-you-can-benefit-from-both-data-and-backup-compression

There are exceptions, of course: if your system is already heavily CPU-bound, compression isn't free, and it may make that problem worse. Also if your databases are encrypted with TDE, you'll find that the compression has little to no effect, because the output of that encryption is not a good candidate for further compression.


When should startup parameter -E be used?

The SQL Server Customer Advisory Team wrote a blog post that has some information on this setting here.

The –E startup flag
The SQL Server startup flag –E forces SQL Server to allocate 4 extents at a time to each file, essentially quadrupling the stripe size. In heavy insert scenarios, this drives larger block sizes to the disk. Also, your pages allocation will be more sequential with the same data file, allowing better sequential I/O for range and table scan operations (which are common in OLAP workloads).
This startup flag provides most, but not all, of the above mentioned benefits to I/O system – without the overhead of managing multiple filegroups. Be aware that this flag is supported only in 64-bit environments. You can find information about the –E startup flag in File allocation extension in SQL Server 2000 (64-bit) and SQL Server 2005 (KB329526).


A view can't have multiple statements within it, you have to express the entire thing as a SELECT. A subquery with the SELECT part of your INSERT can be used in place of the temp table reference. Below should give you a rough idea, although there are errors in the original SQL that I can't fix based on the information I have (eg, the first join condition references i.iex_id, which doesn't exist in the temp table or the subquery).
select i.dte, idm.empl_id, 1 as entity_id, sum(i.hrs_total) AS myhrs
FROM (
select id, dte, name, hrs_total
FROM dttbl.dts
group by id,dte,name,hrs_total
) AS i
JOIN schema1.mytable idm ON idm.src_id = i.iex_id::text AND idm.src_type_id = 123 AND idm.end_dte IS NULL
   JOIN schema1.mytable idm2 ON idm2.empl_id = idm.empl_id AND idm2.src_type_id = 345 AND idm2.end_dte IS NULL
  GROUP BY i.dte, idm.empl_id limit 100;


Database Inner join For fetching Records

This code will work in tsql- it doesn't even actually require a join between the tables:

    SELECT question_id,
       MAX(ans_obj_1) AS ans1_obj,
       MAX(ans_obj_2) AS ans2_obj,
       MAX(ans_obj_3) AS ans3_obj,
       MAX(ans_obj_4) AS ans4_obj,
       MAX(ans_sub) AS answer_subj
FROM table_2
PIVOT
(MAX(answer_value)
 FOR answer_value IN ([ans_obj_1], [ans_obj_2], [ans_obj_3], [ans_obj_4], [ans_sub])
 ) A
 GROUP BY question_id


Copy related records from 3 tables to another 3 tables and maintain the relationship between them

Probably the easiest way would be to maintain the old OrderID. You can do this even if the new table uses an IDENTITY column; you can say SET IDENTITY_INSERT dbo.NewOrders ON; and override. The next OrderID generated by the app will be roughly the highest "old" OrderID + 1 (keeping in mind that IDENTITY does not guarantee a contiguous sequence for a variety of reasons).

However if the goal is to renumber the orders (not sure why you would want to do that), the easiest way would be to temporarily add a column mapping to the old OrderID, using that to populate the related tables, then dropping it. Something like:

ALTER TABLE dbo.NewOrders ADD OldOrderID INT;

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

INSERT dbo.NewOrders(OrderDate, CustomerID, etc., OldOrderID)
  SELECT OrderDate, CustomerID, etc., OrderID
  FROM dbo.OldOrders;

INSERT dbo.NewOrderItems(OrderID, ProductID, Quantity, etc.)
  SELECT n.OrderID, o.ProductID, o.Quantity, etc.
    FROM dbo.NewOrders AS n
    INNER JOIN dbo.OldOrderItems AS o
    ON n.OldOrderID = o.OrderID;

INSERT dbo.NewOrderComments(OrderID, CustomerID, etc.)
  SELECT n.OrderID, o.CustomerID, etc.
    FROM dbo.NewOrders AS n
    INNER JOIN dbo.OldOrderComments AS o
    ON n.OldOrderID = o.OrderID;

COMMIT TRANSACTION;

Once you're happy that the data is all mapped correctly:

ALTER TABLE dbo.NewOrders DROP COLUMN OldOrderID;

There isn't a way to do all of this in one statement. You may be able to do it in two with a really complicated and over-engineered MERGE statement and the OUTPUT clause, but I highly recommend against that for other reasons.


You can do it by increasing column size, then drop .
(Because drop column statement try to validate the data, then drop )
like ..

SQL> ALTER TABLE OWNER.CONFIG
     MODIFY(DTE VARCHAR2(500 BYTE));

SQL> ALTER TABLE OWNER.CONFIG DROP COLUMN DTE ;

example:-

SQL> select * from v$version ;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE    11.2.0.4.0      Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

SQL> ALTER TABLE ORCL.FORM DROP COLUMN CURRENT_STEP;
ALTER TABLE ORCL.FORM DROP COLUMN CURRENT_STEP
*

ERROR at line 1:
ORA-12899: value too large for column "ORCL"."FORM"."CURRENT_STEP" (actual:
6, maximum: 2)

SQL> ALTER TABLE ORCL.FORM
  2  MODIFY(CURRENT_STEP VARCHAR2(500 BYTE));

Table altered.

SQL> ALTER TABLE ORCL.FORM DROP COLUMN CURRENT_STEP;

Table altered.


How to set selinux permission for non-standard mysql bin log path?

To set mysql selinux on a directory you can:

semanage fcontext -a -t mysqld_db_t "/yourdirectory(/.*)?"

then

restorecon -Rv /yourdirectory

Also check the mysql user has permissions on your directory:

ls -l

Set permissions if needed:

chown -R mysql:mysql /yourdirectory


MySQL when a = 0, b = 0, but a  b (binary)

BINARY is a misnomer.  It really refers to a string of bytes; it does not refer to binary numbers, and it isn't a unique datatype.

Also, = and <> adapt to the datatype(s) involved:

SELECT a=0, b=0,    -- numeric comparisons
       a=b, a<>b    -- BLOB comparisons; length matters
    FROM f;

It is a combination of the Operator and the Datatypes that controls what string vs numeric operations.

Some info on datatypes:

BINARY / VARBINARY / BLOB can be manipulated with "bit" operators | & ~ ^ -- not to be confused with logical operators AND (&&) and OR (||).  BLOB-like columns are used for non-character strings, such as images.  MySQL 8.0 stretches these operators beyond a 64-bit limit.  INT can also be manipulated with bit operators.

The BIT(m) datatype is prefixed with leading zeros if needed to fill out to full byte(s).  (Limit 64 bits.)  This datatype is almost never used.

BINARY(1) and BINARY(22) are 1- and 22-byte datatypes.  CAST(1 AS BINARY(1) converts the first 1 to a string "1", then stuffs in hex 31 (ascii code for "1").  Try SELECT HEX(CAST(1 AS binary(1))), HEX(CAST("1" AS BINARY(1))); -- both yield 31.


FDW Permissions error using MD5 encrypted password

Can you show us where you got the idea that this should work?

There is no provision for supplying passwords as md5 hashes to postgres_fdw.  Maybe there ought to be, but someone will have to implement it for it to exist.


MySQL: parallel restores at same time from backup files?

This should not be a problem as long as the data in the two dump files involves different tables... however, it may or may not be as helpful as you might hope, because if the single restore is only using 10% cpu, that suggests that your disk, as opposed to cpu, may be the more significant determining factor in limiting the speed of the restore.


The table mysql.proc is missing, corrupt, or contains bad data

I had a similar problem a while back, and mysql_upgrade etc refused to fix it.

In the end I simply took the table structure from a 'good' database and compared it to the 'broken' one. Then made the necessary adjustments. In my case I found that one column had changed slightly.


Can I see the status of a "repair by sorting" in mysql?

Repair by sorting is a phase that creates a temp table, populates a temp index file, and  prepares all indexes for the target. This would require being in the operating system to view the temp file's size as well as the data and time stamp.

If you ever need to see such progress, there are two things you can do

Migrate the DB to MySQL in Amazon EC2 where you can have OS level access
Migrate the DB to MariaDB in Amazon EC2 since MariaDB gives metered progress on DDL : See my post Is there a progress indicator for OPTIMIZE TABLE progress?

Other than these suggestions, there is nothing else you can do.


First of all: Be concistent within your columnnaming! In Type you use ID, in Customer you use CustID and in Orders CusomerID. Same with ToppingID vs ToppingCode. And you dont have to use the Prefix Pizza in the Pizzas (imho, plural is also not that nice) table.

The relation Type is not clear to me. I guess you can put the OrderType directly into Orders.

You also can think about to remove the PizzaCode Column from Pizzas&Orders&Toppings. You can get this information from Orders join Order&Pizzas.

Edit: I guess Time is for the planned delivery time. Therefore you should name it like that. Otherwise you get confused with Date


A create script for tableA including all indexes and the execution plans for the two UPDATE statements involved are required for a definitive solution.  That being said, we can still combine the deadlock graph provided with knowledge of how SQL Server performs updates and have a very good chance of resolving this issue.

This deadlock involves two spids performing UPDATES on tableA, 58 & 59.

Spid 58's Query:

Update b set  syd_id=d.syd_id
        from tableA b
        inner join tableC d with (nolock) on b.syd_pers_id=d.syd_pers_id
        --and b.game='es_lotto'
        and b.game=d.game  and isnull(b.syd_id,0)=0

Spid 59's Query:

Update b set Draw_Date=d.draw_date
        from tableA b
        inner join tableB d with (nolock) on b.draw_no=d.draw_no
        and left(b.game,2)='UK' and b.Draw_Date is null

In this deadlock, there are two resources involved:

a "Key Lock" on the index IX_tableA_Draw_Date.

Owned by spid 58, mode = 'X' (Exclusive)
Requested by spid 59, mode = 'U' (Update)

a "Page" lock for a different, unspecified index on tableA

Owned by spid 59, mode = 'IX' (Intent Exclusive)
Requsted by spid 58, mode = 'U' (Update)

There are also 3 different lock ownership modes in use (U, X, IX):

"U" or Update Lock.  Conceptually, the sql plan for an UPDATE statement has two parts: A "select" part in which the data to modify is identified, and a "modification" part where the modification actually happens.  Update locks are taken during the "select" part to protect the rows from modification until we get around to actually modifying them (upon which time an X lock is acquired).  Update locks are compatible with "S" (Shared) locks, so readers don't get blocked, however, they are incompatible with X, IX and other U locks.
"X" or Exclusive Lock. Exclusive locks are used to protect data being modified during inserts, updates and deletes.  They are incompatible with all other lock types, and ensure that only one writer can access a resource at the same time.
"IX" or Intent Exclusive Lock.  Intent Exclusive locks are taken on higher level resources before lower level resources get X locks.  For example, before an X lock on a row is taken, an IX lock is taken on both the associated page and the entire table. IX locks indicate that X locks exist at a lower level.  They can also be changed into X locks as part of lock escalation. IX locks are incompatible with X locks, so if two transactions have IX locks on a table, then neither transaction can escalate their page or row locks to the table level.  Intent locks (including IX) are an optimization that allow SQL to check one higher level lock instead of checking thousands of lower level locks.

Now, let's examine spid 58's UPDATE query in detail.  We know from the deadlock graph that spid 58 has an exclusive lock on a row in IX_tableA_Draw_Date.  Therefore, we know that syd_id is either in the index key, in the clustered index key, or is an included column (I'm going to ignore the possibility that spid 58 already owned the lock from a previous statement in the trigger).

Spid 58 is also attempting to acquire an update lock on a page in a different index.  This tells two things:

We know that the select portion of the update is still reading and locking data after the modification portion has started.  This implies that there are no blocking operators  affecting the rows from tableA in the plan. For example, if rows were read from tableA, sorted, and used in a MERGE join; then all the rows would be read (and U-locked) before any exclusive locks are taken.  That isn't the case.
We're attempting to take an Update lock on a PAGE, not a ROW, so it's likely that we're doing an index scan.

We can also assume that a relatively small number of rows are expected to be updated (Due to the multi-column join combined with updating nulls or 0's in an insert trigger, this doesn't feel like a query that updates a large percentage of rows).  Taking all this into consideration, it is likely that the UPDATE is reading rows from tableA using a scan on our mystery index (don't know if it's clustered or non-clustered ) and performing a LOOP JOIN against tableC to get the  tableC.syd_id.

Performing a similiar analysis for spid 59, it is likely that we're seeking into IX_tableA_Draw_Date where "Draw_Date is null", reading (and taking update locks on ROWS), LOOP JOIN against tableB, then taking a X ROW lock and updating our "mystery" index (the IX lock on the page implies that there are X locks on rows).

Where are we? And what do we do next?
We know we've got two update statements.  Both are reading and writing to the same pair of indexes (with the reading and writing swapped).  Both are waiting for an update lock after they've already updated some rows.  Spid 58 is reading pages, 59 is reading rows.  Both are writing and locking rows.  Lock escalation isn't a factor, as row locks -> table locks ( not page locks).

If Spid 58 was taking row locks instead of page locks on the mystery index, then you would only have deadlocks if both queries updated the same row.  You could use a ROWLOCK query hint to make that happen, however you run the risk of increased lock escalation to table locks, which could be worse.

Another approach is to separate the update into two parts.

select id, --Not sure what the PK is...
    syd_id
into #tmp
from tableA inner join tableB (nolock) on b.syd_pers_id=d.syd_pers_id
    --and b.game='es_lotto'
    and b.game=d.game  and isnull(b.syd_id,0)=0

UPDATE tableA SET syd_id = t.syd_id from tableA d inner join #tmp t on t.id = d.id

This would change the order of locks taken as the non-clustered indexes will only be locked for writes, not reads.

Recommendation

Deadlocks are a natural by-product of RDBMS's. Even if you do everything "right" you can't always eliminate them.  At 3-4 Deadlocks per day, the correct approach is probably to just wrap the statement in some error handling so that it is re-tried if a deadlock occurs.

http://msdn.microsoft.com/en-us/library/aa175791(v=sql.80).aspx has an example for SQL 2005.  However, since this is in a trigger, you may need to add the error handling outside of the INSERT statement that fires the trigger.


What exactly you are trying to achieve by shrinking the database?
You should plan for the database growth and have reserved space for this growth.

So why don't you leave it as it is to have allocated space for new data coming to your DB.

It has been discussed so many times that you do not want to shrink your databases. Check this out and think twice before shrinking:

Stop Shrinking Your Database Files. Seriously. Now. By Brent Ozar


Can a weak entity type be involved in an "is-a" relationship?

When I've modeled databases, I would use a isType description where you have isA.  You appear to have two types of Specs; labelSpecs and itemSpecs.  Common data belongs in Specs entity, with type specific data in the appropriate subtype record.  Normally subtypes are exclusive, so a Spec would be either a labelSpec or an itemSpec.


The right thing to do is to fix the data as it goes in.  Then you have only digit strings in a 'canonical' format.  This includes prefixing with the country code.  (However, you may want to keep the leading '+' just so users looking at the database will know that the country code is there.)

You will not get reasonable performance if you must repeatedly fix the numbers on the SELECT side.


You have two options I can think of:

Index for a specific set of user searches

or
Full Text Search

For option 1, you need to convert your search string "Smit*, Bob" into something you are indexing on, eg: where upper(last_name) like 'SMIT%' and upper(first_name) like 'BOB%' with function based indexes on upper(last_name) and perhaps upper(first_name)


Need help with difficult table modeling situation

You probably need a linking table called ContainerFlavors (or IceCreamCombinations for something more sensible) for the many-to-many relationship between Containers and Flavors.
That linking table would have it's own unique ID column, and purely store ContainerID and FlavorID. The primary key on that table would be it's ID column and there would be a foreign key on ContainerID and foreign key on FlavorID (and basically would store every possibly combination of Containers and Flavors).
You can store this linking table's ID column in the your Order_Details table.
You can expand the linking table out to store the ToppingID as well (with a foreign key to Toppings) for every combination of Containers, Flavors, and Toppings.

So after some sitting down and modeling out your example, I realized your example is very similar to a manufacturing schema, which is why it's a litte more complex. Please see the schema I came up with below (please ignore any minor semantical differences such as casing, compared to yours). In this example schema the term "Combination" can be thought of as synonymous to "FinalProduct" (feel free to rename things on your end, as needed).

In this schema you have an Order which can contain multiple OrderDetails, where each OrderDetail has one Combination (final product), but the same Combination can be used on multiple OrderDetails (e.g. if two people order "a cone of chocolate ice cream with hot fudge" combination).
The Combinations table is a distinct list of final products. It has a one-to-many relationship with the CombinationsBom table. (BOM stands for Bill of Materials in the manufacturing industry, and is a type of mapping table that contains all the components that make up a final product. In this case all the container + flavors + topping combinations to make up the final product Combination.) Every row in the CombinationsBom table is the Container with it's Flavor or Topping selection. Multiple rows can exist for the same Combination (hence the one-to-many relationship). Therefor a Combination that is made up of a two scoop Container and has no Toppings should have two rows in the CombinationsBom table, one for each Flavor. (Note since BOM tables contain every possible combination of the final products, they are usually large tables.)
*This design actually allows for multiple Toppings in one Combination as well.
There are a few other ways to model this, or minor normalizations that can be added to my example schema, but I think this is a good start for what you're trying to achieve.
Using the above schema we can write an example query like such:
SELECT O.OrderNumber, O.OrderDate, O.CustomerId, C.CombinationName,
    CB.BomId, CB.CombinationID,
    CO.ContainerName, CB.ContainerID, CO.ContainerPrice, CO.NumberOfScoops,
    CB.IsTopping, CB.FlavorOrToppingID,
    CASE WHEN CB.IsTopping = 0 THEN F.FlavorName ELSE T.ToppingName END AS FlavorOrToppingName,
    CASE WHEN CB.IsTopping = 0 THEN F.PricePerScoop ELSE T.ToppingPrice END AS Price
FROM IceCream.Orders AS O
INNER JOIN IceCream.OrderDetails AS OD
    ON O.OrderNumber = OD.OrderNumber
INNER JOIN IceCream.Combinations AS C
    ON OD.CombinationID = C.CombinationID
INNER JOIN IceCream.CombinationsBom AS CB
    ON C.CombinationID = CB.CombinationID
INNER JOIN IceCream.Containers AS CO
    ON CB.ContainerID = CO.ContainerID
LEFT JOIN IceCream.Flavors AS F
    ON CB.FlavorOrToppingID = F.FlavorID
    AND CB.IsTopping = 0
LEFT JOIN IceCream.Toppings AS T
    ON CB.FlavorOrToppingID = T.ToppingID
    AND CB.IsTopping = 1

To produce these kind of results:

(Note I'm finding minor things that need to be improved in the schema design, and I'll continue to update my answer accordingly, but this should hopefully give you an idea of what's involved for modeling this kind of schema.)


Can a poor database layout cause performance issues for simple queries?

Yes, slow queries will cause other queries to be slow.

Server has only finite amount of IO it can do, if it busy doing heavy LIKE operation, like in your case, it can NOT use index it will make other queries slow.


Properly determine SQL Server version and update level

as a partial solution for this interesting problem, I would consider powershell:

first you need to Install the SQL Server PowerShell module on every server you need.

Get-SqlAgent -ServerInstance "MY_SERVER"

On the picture above you can see the ServerVersion that you are looking for.


Change the row position of a record without messing anything else MySQL?

A database is NOT Excel. The position of a column in a table is - in regard to normal USE of the table - completely arbitrary.

To receive results from a table, you query it - and in that query, you can modify the (selected) column order to get your desired results.

In the same vein, storing computed values (your columns 2-5, 11) primarily makes sense in a time-critical context (i.e. when storage (space and speed) is no issue, but data retrieval has to be extremely quick). I'm not really seeing that in your scenario.

It would be better to simply store the age and to compute which category it belongs to in your select query.


I will suggest to have a table valued function. This function will accept your string as an input parameter and then return a table by splitting the inputs separated by ";" in different rows with an index ID. You can use this function internally with you logic and determine the MAX(Us) from mapping table. Below is the sample example for the function.

CREATE FUNCTION [dbo].[funcSplit] ( @string VARCHAR(MAX) )
RETURNS
 @List TABLE ([Name] [nvarchar] (500))
AS
BEGIN

 DECLARE @name NVARCHAR(255)
 DECLARE @pos INT

 WHILE CHARINDEX(';', @string ) > 0
 BEGIN
  SELECT @pos  = CHARINDEX(';', @string )
  SELECT @name = SUBSTRING(@string , 1, @pos-1)

  INSERT INTO @List
  SELECT @name

  SELECT @string = SUBSTRING(@string , @pos+1, LEN(@string )-@pos)
 END

 INSERT INTO @List
 SELECT @string

 RETURN
END

Once implemented, use this function as below in your own logic to determine the roles.

SELECT MAX(us)
FROM YourMappingTable AS A
     INNER JOIN funcSplit('MA;LA') AS B
     ON A.Them = B.Name

Only thing is remaining is to include an exception for the NA or "Input Does not exists" case which can be easily handled.


Unfortunately the only way to recover your data is to recover them from a backup, you can import just that single table and then insert the particular row.

I will edit my answer to get more precise here, first of all you write that you use dynamic row length. This makes the recover operation way harder if not impossible compared to fixed row length. If you are seriously want to recover, I would suggest that you first of all get known to MyISAM dynmic data file layout, a good entry point would be here: MyISAM Dynamic Data File Layout. Therefore you have a chance that you can recover, but not with a tool which is written for fixed length files. Unfortunately I am not aware of such a tool (the ones i tested didn´t satisfied my requirements).

And btw, I think you will have heard or read that more than enough already, but i want to stress this out again: always backup, test the backups, rinse and repeat.


Can I get OSQL if I install SQL Server Client Tools?

The main recommendations appear to be:

If you have the install discs / files, then you should be able to install just the Client Tools.
Download SQL Server 2012 (SP1) Express (I would try the "SQL Server Management Studio Express (Tools only)" version first)
Check out: http://www.mssqltips.com/sqlservertip/1807/sql-server-2008-client-tools-installation/
If you already have a machine with OSQL installed, you might be able to copy just the following two files to the new server (place them in the same directory) and have it work:

C:\Program Files\Microsoft SQL Server\110\Tools\Binn\OSQL.EXE
C:\Program Files\Microsoft SQL Server\110\Tools\Binn\Resources\1033\osql.rll

As far as licensing goes, I found the following file:

C:\Program Files\Microsoft SQL Server\110\Tools\Binn\Resources\1033\license_SQLCMD.txt

That has the following snippet:

  MICROSOFT SOFTWARE LICENSE TERMS MICROSOFT SQL SERVER 2012 COMMAND
  LINE UTILITIES
   ...
  1.    INSTALLATION AND USE RIGHTS. You may install and use any number of copies of the software on your devices to design, develop and test
  your programs.

Note:
OSQL has been superseded by SQLCMD. When you run OSQL it will display the following message:

  Note: osql does not support all features of SQL Server 2012.
  Use sqlcmd instead. See SQL Server Books Online for details.

However, if your existing code expects OSQL then you would need to test SQLCMD with your code first to make sure that any behavior changes between OSQL and SQLCMD do not break your code.


Making production data accessible to developers via masking

Is it a reasonable approach?

Yes.  That's what the (removed) "Static Data Masking" component in SSMS did.  A simple way to implement this is to copy data from production using an identity for which Dynamic Data Masking is enforced on the production database, using the Copy Database Wizard or a custom script.


Audit all insert, delete, update and select statements on a schema in Oracle 12.2

You described traditional auditing. For unified auditing the parameter audit_trail has no meaning and is actually not considered by Oracle. As mentioned earlier, with unified auditing you have to create an audit policy.
Oracle does not write to SYS.AUD$ with unified auditing. It writes to audsys.aud$unified or spillover files on the OS (if the DB is not accessible). Your data is visible through the view unified_audit_trail, which selects from audsys.aud$unified and v$unified_audit_trail (spillover files).
To audit all insert,update,delete,select on a table scott.t1 you would use something like
create audit policy T1_AUDIT
       ACTIONS select on scott.t1,
               insert on scott.t1,
               update on scott.t1,
               delete on scott.t1;

audit policy T1_AUDIT;


Why is PostgreSQL's auto-explain not working in AWS RDS?

auto_explain only logs a statement upon successful completion. If it gets cancelled, it does not get logged by auto_explain.


Insert dummy data based on data time found in column

WITH RECURSIVE
cte AS ( SELECT MIN(datetimefield) datetimevalue -- or some literal value
         FROM datatable
       UNION ALL
         SELECT datetimevalue + INTERVAL 30 MINUTE
         FROM cte
         WHERE datetimevalue < ( SELECT MAX(datetimefield) -- or some literal value
                                  FROM datatable )
       )
INSERT INTO datatable (datetimefield, valuefield)
SELECT datetimevalue, 'dummy value'
FROM cte
WHERE NOT EXISTS ( SELECT NULL
                   FROM datatable
                   WHERE cte.datetimevalue = datatable.datetimefield )

Of course you must adjust the syntax to your DBMS dialect.


Is there a way to get the contnents of an RPG program using SQL?

Depends on your definition of "contents"...

At v7.3 and more at v7.4 IBM has added some services for program info, module info, ect...
IBM i Services (SQL)

But if you're thinking "contents" as in a source list...

Then you'd have needed to have the program compiled with DBGVIEW(*LIST|*COPY|*ALL) and then use the debugger APIs to retrieve the source...


Drop database from remote that has connected users

First, you can issue DROP DATABASE; from a multiline script, but it has to be the only statement of its transaction:

CREATE DATABASE droptest;

\c droptest

CREATE TABLE a (id integer);

Then run the script:

SELECT id
FROM a;

\c postgres

DROP DATABASE droptest;

It results in:

 id
----
(0 rows)

psql (9.1.9, server 9.0.6)
WARNING: psql version 9.1, server version 9.0.
         Some psql features might not work.
You are now connected to database "postgres" as user "postgres".
DROP DATABASE

And the documentation says:

  DROP DATABASE cannot be executed inside a transaction block.

This means it can't be run like psql -1 -f the_above_script.

So, now my solution (hopefully):

First disable connections on the DB:

REVOKE CONNECT ON database_name FROM ALL;

Then do what you do now:

SELECT pg_terminate_backend(procpid)
FROM pg_stat_activity
WHERE
    datname = '$DB_NAME' and
    current_query NOT LIKE '%SELECT pg_terminate_backend(procpid)%'
;

DROP DATABASE database_name;


Invalid default value for DateTime when changing to utf8_general_ci

This looks like more of a SQL mode issue than the char set. Strict mode (and more specifically  NO_ZERO_DATE which is part of strict mode) usually sets off that error if the table was created with an all zero default date before turning on strict.

What we use (for similar modified date columns), is the '1970-01-01 00:00:01'.

and on a slightly related note, we use timestamp for these columns (and for created time too). Takes half the storage space, and is faster access.


How to find out the activity which increasing the response time of the Sql Server?

I highly recommend using Adam Machanic's excellent diagnostic stored procedure sp_WhoIsActive.  I've got an sp_WhoIsActive tutorial on my blog.  Run it during the slow time window as I describe in the video, and it'll show the active queries, what they're waiting on, and their execution plans.

Keep in mind, though, that this is more of a DBA tool than a "here's-your-problem" tool.  This is where a database administrator usually has to step in and do diagnostic work.  If you're confused by the output of sp_WhoIsActive, check out the book Troubleshooting SQL Server.


SQL Server Clustering - Active/Active or Active/Passive?

Active/Active refers, typically, to the idea of having multiple copies of a database available concurrently.  However, in SQL Server, there is no way to accomplish that for read-write databases, other than the somewhat edge case of sharding data across mutliple instances.  In SQL Server, Active/Active is normally used to denote a multi-node Windows Server Failover Cluster that has multiple SQL Server instances installed, where each node has a separate instance.  However, for this to work successfully, each node needs to be configured with enough memory and CPU resources to successfully serve all instances in case that node is the only node left running at some point.

Active/Passive refers to having a multi-node cluster where one node serves the database(s), while the other node is available to take the load if the primary node fails.

As an example of Active/Active, consider the following:

2 computers configured in a Windows Server Failover Cluster, Node "A" and Node "B".  The cluster has two instances installed, "X" and "Y".  Typically, "X" runs on node "A", and "Y" runs on node "B".  This means the horsepower of both machines can be combined to serve the needs of both instances.  If, however, node "A" goes down, then node "B" will host both "X" and "Y" instances.

MSSQLTips.com has a great article comparing the two types of clustering.

MSDN has a good blog post on how to create an Active/Active cluster using Hyper-V.

From the description in your question it sounds to me like you want a typical 2-node cluster with a single instance serving a single database.  The database resides on shared storage so that if the active node (the one currently serving the database) goes down, the passive node will automatically start up the instance and begin serving the database from there.  Once the original node is brought back online, the instance could be moved back over to it, although that is not strictly necessary.


How to set Postgresql database to see date as "MDY" permanently

I used to have a very similar problem years ago, and then discovered that I can do

ALTER DATABASE database_name SET datestyle TO "ISO, MDY";

Try that.  (This works on a per database basis, for a solution for all of your databases, set this parameter in you postgresql.conf - thanks to @a_horse_with_no_name.)

Please be aware that the default setting is not locale independent.  However, after initdb sets it to whatever it wants, you can change it yourself in the postgresql.conf.

@swasheck's comment made me note that with my settings:

test=# SHOW lc_ctype;
      lc_ctype
--------------------
 Hungarian, Hungary
(1 row)

test=# SHOW lc_time;
      lc_time
--------------------
 Hungarian, Hungary
(1 row)

setting datestyle has the following effect:

SET datestyle TO "ISO, MDY";

SELECT current_date;
    date
------------
 2012-06-21
(1 row)

Now this is not really expected - and it's the same on an other server with en_US.UTF8, too.  Trying "ISO, DMY", too suggests me that the "ISO" part is more or less overriding the other part when producing an output.  For input values, it has the expected effect, as summarized in the table below:

                          input values
           '2016/01/21'   '01/21/2016'   '21/01/2016'         output
──────────────────────────────────────────────────────────────────────
ISO, YMD        OK             --             --            2016-01-21
ISO, DMY        --             OK             --            2016-01-21
ISO, MDY        --             --             OK            2016-01-21

-- means above that the given input style results in an error for the given datestyle setting.

Upon careful reading of the documentation, one can see that datestyle is a pair of partially conflicting settings:

  For historical reasons, this variable contains two independent components: the output format specification (ISO, Postgres, SQL, or German) and the input/output specification for year/month/day ordering (DMY, MDY, or YMD).

For me it means that one has to find the one that fits their needs by a bit of experimenting - but the best way is to leave it at the default and always use an unambiguous input format.  Two of these are 'YYYY-MM-DD' and 'YYYYMMDD'.  I prefer the former - even using this IRL ;)

Note: the datestyle setting (and other runtime settings) from postgresql.conf can be overridden by an ALTER DATABASE bla SET datestyle ..., setting it permanently for a single database, or by SET datestyle TO ... setting it for the current session.  This latter might be useful when importing some third party data with a different date format.


mongodb version difference between config and mongos

As Per MongoDB BOL Starting in 3.4, the use of the deprecated mirrored mongod instances as config servers (SCCC) is no longer supported. Before you can upgrade your sharded clusters to 3.4, you must convert your config servers from SCCC to CSRS.

To convert your config servers from SCCC to CSRS, see Upgrade Config Servers to Replica Set.

Where Config servers store the metadata for a sharded cluster. The metadata reflects state and organization for all data and components within the sharded cluster. The metadata includes the list of chunks on every shard and the ranges that define the chunks.

The mongos instances cache this data and use it to route read and write operations to the correct shards. mongosupdates the cache when there are metadata changes for the cluster, such as Chunk Splits or adding a shard. Shards also read chunk metadata from the config servers.

MongoDB also uses the config servers to manage distributed locks.

Each sharded cluster must have its own config servers. Do not use the same config servers for different sharded clusters.

WARNING:
Administrative operations conducted on config servers may have significant impact on sharded cluster performance and availability. Depending on the number of config servers impacted, the cluster may be read-only or offline for a period of time.

For your further Ref Here and Here


Just my 2 cents:

You could set up a MySQL master-slave replication, the master being inside the internal network and the slave on the external site.

You could also do a nightly mysqldump of the wiki, sftp it to the external site via a cron job and restore it there, but it looks like an ugly and clunky solution to me.

Perhaps the simplest thing would be to give up mirroring and have the wiki on the external site only, so it would be accessible in case of internal network downtime.


In the world of RDBMS's, temp tables are a fact of life. It only rears its ugly head in JOINs.

Even the worst case of a JOIN is the degenerate JOIN, a query of one table.

Since temp tables are always making its way into our queries (into our lives), the best thing you can do is starve temp tables. Making them as compact as possible. What do I mean ???

Here is your query :

SELECT SUM(votes.value),
       comments.text,
       comments.comment_id
FROM comments, votes
WHERE comments.comment_id = votes.comment_id AND comments.page_id = $page_id
GROUP BY comment_id;

You query will actually create a table resulting from a join of comments and votes whose rowcount is the number of comments times 2,000,000. Since temp table have no indexes, there are always cartesians joins occuring with the temp tables. The WHERE clause is applied along the way, then the GROUP BY summation. Don't forget, the temp table will also contain the text column. That's a lot of text data to drag around in the JOIN phase.

Let's refactor your query

You can head off the WHERE clause at the pass, so to speak. Here is how:

SELECT comment_id FROM comments WHERE page_id = $page_id;

This query only has the necessary keys from the comments table.

Next, collect comment_ids from votes

SELECT comment_id,SUM(value) sumofvalues FROM votes;

This is actually the worst part. With 2 million rows and 4 bytes per comment_id and 4 bytes for the sum, that's an 16MB table in the absolute worst case.

Next, combine the keys of comments with the matching keys in votes.

SELECT BB.* FROM
(SELECT comment_id FROM comments WHERE page_id = $page_id) AA
INNER JOIN
(SELECT comment_id,SUM(value) sumofvalues FROM votes GROUP BY comment_id) BB
USING (comment_id);

Now that keys from comments and the sum of the values from votes are retrieved, the final part is to connect the comment-ids back to the original comments table and get the text fields.

SELECT
    B.sumofvalues,A.text,A.comment_id
FROM
    comments A INNER JOIN
    (
        SELECT BB.*
        FROM
            (SELECT comment_id FROM comments WHERE page_id = $page_id) AA
        INNER JOIN
            (SELECT comment_id,SUM(value) sumofvalues
            FROM votes GROUP BY comment_id) BB
        USING (comment_id)
    ) B
USING (comment_id);

Before this refactored query can work as fast as possible you will need proper indexing.

Here are the indexes you need:

ALTER TABLE comments ADD INDEX pageid_commentid_ndx (page_id,comment_id);
ALTER TABLE votes ADD INDEX commentid_value_ndx (comment_id,value);

You want the first index because it will group rows by page_id. You want the second index because It will group rows by comment_id. In fact, both these indexes are called covering indexes. Why is that important ??? It is important because the subqueries will retrieve needed data from the index only, never touching the main table. The comments table is only accessed once when all needed keys are compiled together in the subqueries.

Give it a Try !!!

If any syntax is not working, please comment on the question and let me know !!!


Unable to add new instance on MySQL InnoDB cluster

I found solution the issue:

1.It's neccessary to configure and validate every instance with dba.checkInstanceConfiguration() and dba.configureInstance().
2.Specify port number of instance which we'll add to the cluster.
Example in my script:
cluster.addInstance({user: "admin", host: "srv-mysql-02", port:3306, password: dbPass})


Progress of mongodump operation jams at a random point

Seems that some other folks had this issue (here or here) without any clear resolution. Someone suggests adding --quiet to the mongodump command.

But maybe mongodump is not the correct way of doing it. From the documentation:

  mongodump and mongorestore are simple and efficient tools for backing
  up and restoring small MongoDB deployments, but are not ideal for
  capturing backups of larger systems

Also:

  When connected to a MongoDB instance, mongodump can adversely affect
  mongod performance. If your data is larger than system memory, the
  queries will push the working set out of memory, causing page faults

Some other methods of creating backups are detailed in the documentation. Maybe „Filesystem Snapshots” or „cp or rsync” are more suitable to your use case.


Slow Trigger performance when big batches

I'm putting this here since it's a bit long, but I don't think it should qualify as an answer.  There are no answers here, just observations and advice.

Short version, there isn't anything that can be done to make the query go faster and achieve the same results.  You need to change the process that is feeding data into the main table if you want batching to solve the issue.  Otherwise, you have to change the history process.

First, it's not the table insert that's slowing you down, it's the query that pulls the INSERTED and DELETED tables together.

Why Not?

The INSERTED and DELETED tables are heaps with no indexes.  Joining them together as you are doing requires two table scans and a sort.  The larger the operation, the more expensive this gets.

Batching Inside the Trigger

This won't help you here because the source tables are heaps.  You can't walk them without creating some sort of key to work with, and adding anything will just increase (at worst) or just increase complexity (best case) without improving anything.

Batching Outside the Trigger

If you can rearrange so that you are doing smaller update statements at a shot outside the trigger then the INSERTED/DELETED tables will be smaller, making the operation faster and less blocking, although total cost will be the same.

Solution(s)?

Any solution that addresses this will require a change in some fashion.  You don't mention your version of SQL, but if you are on 2016 or better, you could look into temporal tables. https://docs.microsoft.com/en-us/sql/relational-databases/tables/temporal-tables?view=sql-server-ver15

Alternatively, for this type of history table, where you only want to catch the UPDATES, I would do a straight insert of the DELETED table contents.  No additional comparisons or joins with the INSERTED table.  Your cost should be roughly the same as the insert, so minimal increase (I mean, double the I/O, but that's as minimal as you can get).

Then for looking at it, you just grab all the history records + the live record and you can see what changed and when.  It won't have that "ChangedColumns" list that your current version has, but you could put something like that together if you want.

Good luck.


Multiple aggregations in select slow on postgres

Let's create some test data. Looks like your query has about 1% of rows per antenna_id so let's replicate this.
CREATE UNLOGGED TABLE foo( lat FLOAT NOT NULL, lon FLOAT NOT NULL, aid INTEGER NOT NULL );
INSERT INTO foo SELECT random(), random(), random()*100
    FROM generate_series(1,10000000) s;
CREATE INDEX foo_aid_lat ON foo( aid, lat );
CREATE INDEX foo_aid_lon ON foo( aid, lon );
VACUUM ANALYZE foo;

SELECT min(lat),max(lat),min(lon),max(lon) FROM foo WHERE aid IN (1,2);

 Finalize Aggregate  (cost=71572.35..71572.36 rows=1 width=32) (actual time=119.907..125.118 rows=1 loops=1)
   ->  Gather  (cost=71572.12..71572.33 rows=2 width=32) (actual time=119.648..125.110 rows=3 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         ->  Partial Aggregate  (cost=70572.12..70572.13 rows=1 width=32) (actual time=95.595..95.596 rows=1 loops=3)
               ->  Parallel Bitmap Heap Scan on foo  (cost=4886.47..69687.39 rows=88473 width=16) (actual time=9.532..90.336 rows=66524 loops=3)
                     Recheck Cond: (aid = ANY ('{1,2}'::integer[]))
                     Heap Blocks: exact=26477
                     ->  Bitmap Index Scan on foo_aid_lon  (cost=0.00..4833.39 rows=212336 width=0) (actual time=20.022..20.023 rows=199572 loops=1)
                           Index Cond: (aid = ANY ('{1,2}'::integer[]))
 Planning Time: 0.499 ms
 Execution Time: 125.202 ms

This is really slow. Let's try for one antenna_id.
SELECT min(lat),max(lat),min(lon),max(lon) FROM foo WHERE aid=1;
 Result  (cost=1.88..1.89 rows=1 width=32) (actual time=0.192..0.196 rows=1 loops=1)
   InitPlan 1 (returns $0)
     ->  Limit  (cost=0.43..0.47 rows=1 width=8) (actual time=0.059..0.060 rows=1 loops=1)
           ->  Index Only Scan using foo_aid_lat on foo  (cost=0.43..3777.80 rows=106668 width=8) (actual time=0.057..0.057 rows=1 loops=1)
                 Index Cond: ((aid = 1) AND (lat IS NOT NULL))
                 Heap Fetches: 0
   InitPlan 2 (returns $1)
     ->  Limit  (cost=0.43..0.47 rows=1 width=8) (actual time=0.044..0.045 rows=1 loops=1)
           ->  Index Only Scan Backward using foo_aid_lat on foo foo_1  (cost=0.43..3777.80 rows=106668 width=8) (actual time=0.043..0.044 rows=1 loops=1)
                 Index Cond: ((aid = 1) AND (lat IS NOT NULL))
                 Heap Fetches: 0
   InitPlan 3 (returns $2)
     ->  Limit  (cost=0.43..0.47 rows=1 width=8) (actual time=0.038..0.038 rows=1 loops=1)
           ->  Index Only Scan using foo_aid_lon on foo foo_2  (cost=0.43..3777.80 rows=106668 width=8) (actual time=0.037..0.037 rows=1 loops=1)
                 Index Cond: ((aid = 1) AND (lon IS NOT NULL))
                 Heap Fetches: 0
   InitPlan 4 (returns $3)
     ->  Limit  (cost=0.43..0.47 rows=1 width=8) (actual time=0.042..0.042 rows=1 loops=1)
           ->  Index Only Scan Backward using foo_aid_lon on foo foo_3  (cost=0.43..3777.80 rows=106668 width=8) (actual time=0.041..0.041 rows=1 loops=1)
                 Index Cond: ((aid = 1) AND (lon IS NOT NULL))
                 Heap Fetches: 0
 Planning Time: 0.504 ms
 Execution Time: 0.277 ms

That's the correct plan, that uses the multicolumn index to compute the max and min. This requires only 1 index lookup per min() or max(), because
SELECT max(lat) where aid=...

is equivalent to
SELECT lat WHERE aid=... ORDER BY lat DESC LIMIT 1

...which is optimizable using an index which contains the rows in pre-sorted order.
The above optimization of max() and min() is basically syntactic sugar, it turns the query into an ORDER BY+LIMIT and puts that into an InitPlan in order to use the index.
But, apparently, it doesn't do this when querying several antenna_ids using "WHERE IN()". Adding a "GROUP BY aid" at the end of the first query doesn't help.
So... Let's query the antenna_ids one at a time then.
SELECT * FROM
(VALUES (1),(2)) AS v
CROSS JOIN LATERAL (SELECT min(lat),max(lat),min(lon),max(lon) FROM foo WHERE aid=v.column1) x;

It makes a nested loop over the VALUES, and inside the nested loop is the above fast query. It returns the max() and min() for each antenna_id, so to get the global max() and min() you have to wrap that in a subquery and apply max() and min() over the result.
This shouldn't take more than a millisecond unless there is another problem.
Replacing the VALUES above with a generate_series(1,100) that gets the max for the 100 aid's in the table takes about 5ms. Doing it the old fashioned way:
select aid,min(lat),max(lat),min(lon),max(lon) FROM foo group by aid;

takes about 100x longer.


SQL server compare a field with another that is composed of a list separated by commas of the 1st field

Maybe a simple like will work for you:

','+tContactoTerritorio.territorios+',' like '%,'+tTerritorio.terrNome+',%'

I'm adding commas at the beginning and end of territorios assuming the first and last occurrences don't have them. Then you look for '%,terrnome,%'.
(Obviously this is a terrible way to search for a string but it's also a terrible way to store data)

SELECT terrNome             AS Territorio,
  espDescricao              AS Especialidade,
  COUNT (DISTINCT contNome) AS 'Nº Médicos'
FROM tContacto,
  tContactoTerritorio,
  tTerritorio,
  tEspecialidade
WHERE tContacto.contID   =tContactoTerritorio.contID
AND tContacto.tpcontID   =120
AND ','+tContactoTerritorio.territorios+',' like '%,'+tTerritorio.terrNome+',%'
AND (tEspecialidade.espID=tContacto.esp1ID
OR tEspecialidade.espID  =tContacto.esp2ID
OR tEspecialidade.espID  =tContacto.esp3ID
OR tEspecialidade.espID  =tContacto.esp4ID
OR tEspecialidade.espID  =tContacto.esp5ID)
GROUP BY terrnome,
  espdescricao


SQL Database Design: Balance between table scans and row duplication (Address example)

Ensuring that addresses are unique is not a trivial problem. You have to assume that people enter their addresses in slightly different ways, abbreviating some parts sometimes, or omitting optional information. Just checking for exact duplicates would probably fail pretty often.

You should consider whether trying to keep addresses unique is worth the effort and complexity.

Addresses of users and addresses of orders are pretty different concepts. A user can change their current address. An order should never be retroactively changed in that way, the address there should always be the delivery address.

You could consider making addresses immutable. Changing an address would always mean creating a new address. This means you can safely point orders to the common address table, as the addresses will never change.

If a user changes their address, you simply add a new address and link it to the user. The old address can be hidden in the UI, soft-deleted or just shown as an address history. But all orders could still reference the old address because it never changes.

  Is it practical, at scale, to scan an entire table to see if an Address exists before adding it?

That depends entirely on what you consider "at scale", it's not an issue with a few tens of thousands of rows but completely impractical with a billion rows.

  Is an index supporting a combination of eight columns reasonable (as in Option 1)?

B-Tree indexes are limited to something around 2700 characters. Indexing very large items is typically not a good idea. In Postgres 10, which isn't released yet, you could consider a hash index for this which has a fixed size as far as I understand and should have an advantage for large columns.

But I really think you're asking the wrong question here. In your application, you should know when an address is already in the DB because the user didn't enter it from scratch, but e.g. selected it from the list of addresses already associated with their account.

You don't actually need to enforce uniqueness in your DB, if I understand your problem correctly. And if you'd need to do that, you should always use a unique constraint, which automatically uses an index.


Cannot drop column : needed in a foreign key constraint

Having a look at MySql docs I've found a warning about foreign_key_keys:

  Warning
  With foreign_key_checks=0, dropping an index required by a foreign key constraint places the table in an inconsistent state and causes the foreign key check that occurs at table load to fail. To avoid this problem, remove the foreign key constraint before dropping the index (Bug #70260).

IMHO you should drop FOREIGN KEY before DROP the COLUMN.

ALTER TABLE `user` DROP FOREIGN KEY `FK_G38T6P7EKUXYWH1`;
ALTER TABLE `user` DROP COLUMN `region_id`;

I've set up a rextester example, check it here.


MySQL eats up all the memory from the RHEL OS

9 times out of 10 this is due to a hungry query. Set long-query-time to something < 2 and once you have a good sample size analyse the log file using the Percona tool pt-query-digest. This should give you a good place to start.

Try splitting your transactional and analytical workloads. Running them on different hardware is common place.


Looking at your server times: 23 seconds to restore a table with 1,6 million rows from a 1.8 GB dump file. That does not seem too bad. Might be faster, but doesn't seem very slow either.

To speed up index creation (as well as UNIQUE and PK constraints) set maintenance_work_mem high enough temporarily (for the local session or transaction only). The manual:

  maintenance_work_mem (integer)

  ... Specifies the maximum amount of memory to be used by maintenance operations, such as VACUUM, CREATE INDEX, and ALTER TABLE ADD FOREIGN KEY.
  It defaults to 64 megabytes (64MB).

Set it just high enough for Postgres to process everything in RAM (If you have enough RAM to spare (temporarily). For instance:

BEGIN;
SET LOCAL maintenance_work_mem = 1GB;  -- adapt to your needs
ALTER TABLE testtable ADD CONSTRAINT testtable_lang_q_key UNIQUE (lang, q);
COMMIT;

The manual warns:

  Note that when autovacuum runs, up to autovacuum_max_workers times
  this memory may be allocated, so be careful not to set the default
  value too high.

Setting it high for your transaction only avoids this problem.

Related:

Is a clustered index on column A the same as creating a table ordered by A?


How to use aes-encryption in PostgreSQL?

\df *crypt in psql reveals the argument types of the pgcrypto encrypt and decrypt functions (as do the PgCrypto docs):

                                List of functions
 Schema |      Name       | Result data type |   Argument data types    |  Type
--------+-----------------+------------------+--------------------------+--------
 ...
 public | decrypt         | bytea            | bytea, bytea, text       | normal
 public | encrypt         | bytea            | bytea, bytea, text       | normal
 ...

so both the encrypt and decrypt functions expect the key to be bytea. As per the error message, "you might need to add explicit type casts".

However, it works fine here on Pg 9.1, so I suspect there's more to it than you've shown. Perhaps you have another function also named encrypt with three arguments?

Here's how it works on a clean Pg 9.1:

regress=# create table demo(pw bytea);
CREATE TABLE
regress=# insert into demo(pw) values ( encrypt( 'data', 'key', 'aes') );
INSERT 0 1
regress=# select decrypt(pw, 'key', 'aes') FROM demo;
  decrypt
------------
 \x64617461
(1 row)

regress=# select convert_from(decrypt(pw, 'key', 'aes'), 'utf-8') FROM demo;
 convert_from
--------------
 data
(1 row)

Awooga! Awooga! Key exposure risk, extreme admin caution required!

BTW, please think carefully about whether PgCrypto is really the right choice. Keys in your queries can be revealed in pg_stat_activity and the system logs via log_statement or via crypto statements that fail with an error. IMO it's frequently better to do crypto in the application.

Witness this session, with client_min_messages enabled so you can see what'd appear in the logs:

regress# SET client_min_messages = 'DEBUG'; SET log_statement = 'all';
regress=# select decrypt(pw, 'key', 'aes') from demo;
LOG:  statement: select decrypt(pw, 'key', 'aes') from demo;
LOG:  duration: 0.710 ms
  decrypt
------------
 \x64617461
(1 row)

Whoops, key possibly exposed in the logs if log_min_messages is low enough. It's now on the server's storage, along with the encrypted data. Fail. Same issue without log_statement if an error occurs to cause the statement to get logged, or possibly if auto_explain is enabled.

Exposure via pg_stat_activity is also possible.. Open two sessions, and:

S1: BEGIN;
S1: LOCK TABLE demo;
S2: select decrypt(pw, 'key', 'aes') from demo;
S1: select * from pg_stat_activity where current_query ILIKE '%decrypt%' AND procpid <> pg_backend_pid();

Whoops! There goes the key again. It can be reproduced without the LOCK TABLE by an unprivileged attacker, it's just harder to time it right. The attack via pg_stat_activity can be avoided by revoking access to pg_stat_activity from public, but it just goes to show that it might not be best to send your key to the DB unless you know your app is the only thing ever accessing it. Even then, I don't like to.

If it's passwords, should you store them at all?

Furthermore, if you're storing passwords, don't two-way encrypt them; if at all possible salt passwords then hash them and store the result. You usually don't need to be able to recover the password cleartext, only confirm that the stored hash matches the password the user sends you to log in when it's hashed with the same salt.

If it's auth, let someone else do it for you

Even better, don't store the password at all, authenticate against LDAP, SASL, Active Directory, an OAuth or OpenID provider, or some other external system that's already designed and working.

Resources

Secure Password authentication explained simply - CodeProject
Wikipedia: Challenge-response authentication
Wikipedia: Cryptographic hash function
Wikipedia: Salt (cryptography)
Wikipedia: Password

and lots more.


Help with Replication from an Always On Availability Group

The root cause of this was connection timeouts because the availability group was in a Multi-subnet Availability Group.
This was resolved by following this document from Microsoft.


DBA_TAB_PRIVS is what you're looking for. A demo is the best way to illustrate.

Create the edition.

SQL> create edition wibble;

Edition created.

SQL>

No grants yet:

SQL> select table_name as edition_name, grantor, grantee
  2  from dba_tab_privs
  3  where privilege='USE'
  4  and table_name='WIBBLE';

no rows selected

SQL>

Edition created, shouldn't be able to use it.

SQL> conn phil/phil
Connected.
SQL> alter session set edition = wibble;
ERROR:
ORA-38802: edition does not exist

Grant:

SQL> conn / as sysdba
Connected.
SQL> grant use on edition wibble to phil;

Grant succeeded.

SQL>

Should be able to use it now:

SQL> conn phil/phil
Connected.
SQL> alter session set edition=wibble;

Session altered.

SQL>

Grants are now visible:

SQL> select table_name as edition_name, grantor, grantee
  from dba_tab_privs
  where privilege='USE';

EDITION_NAME                   GRANTOR GRANTEE
------------------------------ ------- -------
ORA$BASE                       SYS     PUBLIC
WIBBLE                         SYS     PHIL

SQL>


How to force MySQL Workbench to use constraints in my file?

I don't really understand the trouble you have. These settings are just defaults. They are used when you create a relationship. But you can then change the actions to anything you like. MySQL Workbench won't change them automatically to something else.


Prevent record creation with F_ID and H_ID having NULL values (needs 5.5 or newer server version):

DELIMITER @@;

CREATE TRIGGER cancel_insert_book BEFORE INSERT ON booking
FOR EACH ROW
BEGIN
    IF ((NEW.F_ID IS NULL) AND (NEW.H_ID IS NULL)) THEN
        SIGNAL SQLSTATE '23000'
        SET MESSAGE_TEXT = 'Both F_ID and H_ID cannot be NULL at the same time',
        MYSQL_ERRNO = 1048;
    END IF;
END;
@@;

CREATE TRIGGER cancel_update_book BEFORE UPDATE ON booking
FOR EACH ROW
BEGIN
    IF ((NEW.F_ID IS NULL) AND (NEW.H_ID IS NULL)) THEN
        SIGNAL SQLSTATE '23000'
        SET MESSAGE_TEXT = 'Both F_ID and H_ID cannot be NULL at the same time',
        MYSQL_ERRNO = 1048;
    END IF;
END;
@@;

DELIMITER ;

Remove record which have F_ID and H_ID having NULL values:

DELIMITER @@;

CREATE TRIGGER cancel_book AFTER UPDATE ON booking
FOR EACH ROW
BEGIN
    IF ((NEW.F_ID IS NULL) AND (NEW.H_ID IS NULL)) THEN
        CREATE EVENT remove_record
        ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 SECOND
        DO
          DELETE FROM booking
          WHERE ((f_id IS NULL) AND (h_id IS NULL));
    END IF;
END;
@@;

DELIMITER ;


How to solve multidimensional data relationship

Products

ProdId
ProdName

Properties

(PropId) (primary key, if required)
ProdId (composite key)
PropCode (composite key)
PropValue (composite key) (if storing a specific value, otherwise you can remove this to simply store the possible properties for any given product)

Basically you store multiple values for a given property that all point back to a single product.  The composite key would need to be unique and you would need to ensure data integrity by either linking PropCode to another table or restricting the values on entry.


Stuck task in management studio, how do you handle them?

I would say don't ever use the "Take Offline" thing in the GUI unless you know for a fact that the database is not in use. By anything. That's hard to know without doing some legwork, so why not save this script somewhere and always use this?
USE [master];
GO
ALTER DATABASE $dbname$ SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
ALTER DATABASE $dbname$ SET OFFLINE;

And then the reciprocal of course:
ALTER DATABASE $dbname$ SET ONLINE;
GO
ALTER DATABASE $dbname$ SET MULTI_USER;

The reason you need to set it to SINGLE_USER first is to kick out any existing users (there is an option to do so on the detach dialog, but not the take offline dialog), since SQL Server needs exclusive access to the database in order to take it offline. Now, you may want to do some extra legwork anyway to see who is currently using the database, as if you do this in the middle of a large backup operation or ETL job or what have you, that might be problematic.
EDIT : I have filed a suggestion on Connect about this (see Connect #2687832) and have also posted it to Trello (filed under "Object Explorer").
This was fixed in SSMS 16.3; there is now a checkbox that asks if you want to forcefully terminate any active connections.


Calculating interval in days based on average velocity

First count orders grouped by day of last 7 days, and then calculate averages

--= Count orders grouping by date
WITH cn AS
(
    SELECT created_at::date, COUNT(*) num_orders
    FROM   orders
    WHERE  created_at <  (now() - interval '1 days')
    AND    created_at >= (now() - interval '8 days')
    GROUP BY created_at::date
)
SELECT to_char(AVG(num_orders), '9.99') AS "AvgOrderVelocity",
       to_char(SUM(num_orders) / 7::decimal(18,2), '0.99')  AS "AvgLast7Days",
       (SELECT COUNT(*)
        FROM   orders
        WHERE  created_at > (now() - interval '1 days')) "Last24h"
FROM   cn;

Using first sample data:

Orders grouped by date:

created_at | num_orders
:--------- | ---------:
2017-05-05 |          1
2017-05-07 |          1
2017-05-09 |          1

AvgOrderVelocity | AvgLast7Days | Last24h
:--------------- | :----------- | ------:
 1.00            |  0.43        |       3

dbfiddle here

Using second sample data:

Orders grouped by date:

created_at | num_orders
:--------- | ---------:
2017-05-04 |          3
2017-05-05 |          3
2017-05-06 |          3
2017-05-07 |          3
2017-05-08 |          3
2017-05-09 |          3

AvgOrderVelocity | AvgLast7Days | Last24h
:--------------- | :----------- | ------:
 3.00            |  2.57        |       1

dbfiddle here


Postgis reporting a point is 1216m away from itself using ST_Distance

The two points use different ellipsoids.
EPSG:4326 is WGS 84.
EPSG:4236 is Hu Tzu Shan 1950.


The order of the index matters.
Your update query uses seven and eight. There must be at the left most begining of the index. Because both are with = value based where criteria the order doesn't matter.
create index udx_78 on A (seven, eigth)

Putting the updated columns into the index doesn't help an update query. If it was aselect query, then the query would return those columns from the index.
See how mysql uses indexes.
Welcome to DBA stack exchange. Please include the explain output next time along with show create table {tablename} as it conveys the message faster.


combining two different record into single result

You should use GROUP_CONCAT on emp_first_name. Maybe something like:

$this->datatables
   ->select('o.order_id,
        concat_ws(" " , `l.location_street`, `l.location_city` ) as location_address,
        group_concat(e.emp_first_name SEPARATOR ','), o.order_status, o.from_date')
   ->from('orders o')
   ->join('location l', 'o.location_id = l.location_id')
   ->join('order_details od', 'od.order_id = o.order_id')
   ->join('employee e', 'e.emp_id = od.emp_id')
   ->group_by('o.order_id');


Parsing a manifest.xml of a table schema to script?

Here's some code to get you started:

declare @doc xml = '
<DocumentElement>
  <SchemaTable>
    <ColumnName>somecolumn1</ColumnName>
    <ColumnOrdinal>0</ColumnOrdinal>
    <ColumnSize>4</ColumnSize>
    <NumericPrecision>10</NumericPrecision>
    <NumericScale>255</NumericScale>
    <IsUnique>false</IsUnique>
    <BaseColumnName>somecolumn1</BaseColumnName>
    <DataType>System.Int32, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089</DataType>
    <AllowDBNull>false</AllowDBNull>
    <ProviderType>8</ProviderType>
    <IsIdentity>false</IsIdentity>
    <IsAutoIncrement>false</IsAutoIncrement>
    <IsRowVersion>false</IsRowVersion>
    <IsLong>false</IsLong>
    <ProviderSpecificDataType>System.Data.SqlTypes.SqlInt32, System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089</ProviderSpecificDataType>
    <DataTypeName>int</DataTypeName>
    <NonVersionedProviderType>8</NonVersionedProviderType>
  </SchemaTable>
  <SchemaTable>
    <ColumnName>somecolumn2</ColumnName>
    <ColumnOrdinal>1</ColumnOrdinal>
    <ColumnSize>4</ColumnSize>
    <NumericPrecision>10</NumericPrecision>
    <NumericScale>255</NumericScale>
    <IsUnique>false</IsUnique>
    <BaseColumnName>somecolumn2</BaseColumnName>
    <DataType>System.Int32, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089</DataType>
    <AllowDBNull>false</AllowDBNull>
    <ProviderType>8</ProviderType>
    <IsIdentity>false</IsIdentity>
    <IsAutoIncrement>false</IsAutoIncrement>
    <IsRowVersion>false</IsRowVersion>
    <IsLong>false</IsLong>
    <ProviderSpecificDataType>System.Data.SqlTypes.SqlInt32, System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089</ProviderSpecificDataType>
    <DataTypeName>int</DataTypeName>
    <NonVersionedProviderType>8</NonVersionedProviderType>
  </SchemaTable>

</DocumentElement>';

with q as
(
    select t.c.value('ColumnName[1]','nvarchar(200)') ColumnName,
           t.c.value('ColumnOrdinal[1]','int') ColumnOrdinal,
           t.c.value('DataTypeName[1]','varchar(50)') DataTypeName
    from @doc.nodes('/DocumentElement/SchemaTable') t(c)
)
select concat(case when ColumnOrdinal = 0 then '    ' else '   ,' end, quotename(columnname), ' ', DataTypeName, ' ') columnDeclaration
from q
order by ColumnOrdinal


Query runs quicker when column calculation is done after initial query

In query 4, the one-row from the table variable convinces the optimizer to choose an index seek and key lookup on the EventLog table. The true number of rows returned is 49:

For query 6, the optimizer produces an over-estimate (22,659 vs 49):

The higher number of estimated executions causes the optimizer to choose to build a temporary index (Eager Index Spool) on the EventLog table instead of performing 22,659 key lookups. This temporary index is built and dropped on each execution of the query. The result is a lower estimated cost plan, but it runs much longer in practice.

Adjusting the indexing of the EventLog table is one way to resolve this issue.

For a more detailed answer, please provide the actual (post-execution) plans in xml form e.g. via https://www.brentozar.com/pastetheplan/


Nesting inner to sum inner query's result

You need to alias your inner query and use SUM instead of COUNT.

select SUM(b) from (select a,b from xyz) temp_table;

Instead of that you can do like

SELECT SUM(b) FROM xyz;

Example

SELECT userid,score FROM user_score;
+--------+-------+
| userid | score |
+--------+-------+
|      1 |     4 |
|      1 |     5 |
|      1 |     6 |
|      1 |     7 |
|      1 |     8 |
|      1 |     9 |
|      1 |    10 |
|      1 |     2 |
|      1 |     2 |
|      2 |     2 |
|      2 |     3 |
|      2 |     4 |
|      2 |     5 |
|      2 |     6 |
|      2 |     7 |
|      2 |     8 |
+--------+-------+
16 rows in set (0.00 sec)

SELECT SUM(score) FROM (SELECT userid,score FROM user_score ) A;
+------------+
| SUM(score) |
+------------+
|         88 |
+------------+
1 row in set (0.00 sec)

OR

SELECT SUM(score) FROM user_score;
+------------+
| SUM(score) |
+------------+
|         88 |
+------------+
1 row in set (0.00 sec)


Try this

mysql> create database mydb;
mysql> use mydb;
mysql> source db_backup.dump;


Providing access to the SQL server database for outside developers

Not quite sure what exactly your question is. However, let me try an answer...

In general it is a good idea to have all application access go exclusively through a layer of views and procedures. That allows to run multi step migrations under the covers without impacting the running application. Through that you can implement changes like moving a column of a 2 TB table to another table without downtime.

It is also a good practice to restrict application access to only the minimum required level. So giving the app read/write access to just the views and procedures is a good idea.

Finally, any web developer should know how to use a connection string. So if that is your question, you are good there too.

If you were going for something else, leave a comment below.


Insert sum of the row data of each record into separate column in postgreSQL?

Use an UPDATE statement.

update student
   set total = social + math + science;

But that column is totally useless. In general you should not store data in a database that can easily be derived from existing values.

In your, adding the values of the three columns adds no (noticeable) overhead to a select statement, so there is no point in storing the total.

You should rather remove the column completely and then create a view doing this for you. Otherwise you need to update that column each time one of the other three columns are changed.

create view total_score
as
select id, name, class, social, science, math,
       social + math + science as total
from student;

Plus: your name column should be defined as NOT NULL.


Does disconnecting the network stop a query?

As mentioned by Nick and Martin, the eventual status of your query depends on whether SQL Server knows about your network cable pull before the query completed. From Books Online (though I find it interesting that there are equivalent topics for this in 2000, 2005, 2008, and 2008 R2, but not 2012 or 2014):

  If an error prevents the successful completion of a transaction, SQL Server automatically rolls back the transaction and frees all resources held by the transaction. If the client's network connection to an instance of the Database Engine is broken, any outstanding transactions for the connection are rolled back when the network notifies the instance of the break. If the client application fails or if the client computer goes down or is restarted, this also breaks the connection, and the instance of the Database Engine rolls back any outstanding connections when the network notifies it of the break. If the client logs off the application, any outstanding transactions are rolled back.

(As an aside, that word connections in the 2nd last sentence was probably meant to be transactions. I don't know how one rolls back a connection.)

In a similar way, SQL Server may undo or redo transactions during recovery after the server is shut down unexpectedly, and this will depend on the state of the transaction at the moment of shutdown. I have seen people use this tactic to achieve what you were trying to do (cancel the transaction(s)) and when the server started up again much of the work was simply redone (so net effect of their knee-jerk reaction was much closer to zero than they expected).

So rather than be subject to this, instead of doing drastic things in a panic, like yanking a network cable or shutting off the machine, I suggest in the future you have better discipline about running ad hoc queries against important systems. For example, instead of:

UPDATE dbo.sometable
-- where *oops* I forgot this part

Have this:

BEGIN TRANSACTION;

UPDATE dbo.sometable
-- where *oops* I forgot this part

-- COMMIT TRANSACTION;
-- ROLLBACK TRANSACTION;

Then, if the update was indeed correct, you can highlight the COMMIT part and run it. If it wasn't, you can calmly highlight the ROLLBACK part and run that. You can even use add-ins like SSMS Tools Pack to edit your New Query template to include that boilerplate.

Now it could still get you in trouble in the event that you run the query and then don't either commit or rollback, because now your transaction is blocking other users. But this is better then irrevocably modifying data.

And of course, as always, have a backup you can rely on.


A classical case for a lateral join:
SELECT thd.id as _id,
       thd.txt as text,
       thd.delete_password,
       thd.reported,
       thd.created_on,
       thd.bumped_on,
       r.*
FROM (SELECT *
      FROM threads
      WHERE board_id = 1
      ORDER BY created_on DESC
      LIMIT 10) AS thd
   LEFT JOIN LATERAL
      (SELECT * FROM replies
       WHERE thread_id = thd.id
       ORDER BY created_on DESC
       LIMIT 3) AS r ON TRUE;


How can I do the following task in Oracle

You should probably consider creating the Global Temp table once ... outside of this entire process ...
creating it "on the fly" like this doesn't really make any sense.

Note also that the data is by default transaction based ... so those records you're inserting will tend to "dissappear" quite quickly (ie once you hit a commit).

  SQL> CREATE GLOBAL TEMPORARY TABLE temp(CID NUMBER);

  Table created.

  SQL> insert into temp ( select level from dual connect by level <= 10);

  10 rows created.

  SQL> Select * from temp;

         CID
  ----------
           1
           2
           3
           4
           5
           6
           7
           8
           9
          10

  10 rows selected.

  SQL> commit;

  Commit complete.

  SQL> Select * from temp;

  no rows selected

  SQL>

You don't need the PL/SQL block for this, and in fact creates more overhead than it's worth.


No-SQL Database Design for restaurant app

As NoSQL databases typically do not support joins, they expect you to store the data in de-normalized form. (So, throw the codd normalization rule book away. Just kidding). In this case, the thrid table better have the values duplicated something like (orderid, ordertime, [itemnames], waitername, orderserviced yes/no, servicetime, ...). itemnames can be a list type which is supported by many NoSQL databases. Not all but some databases support secondary index queries. You can query for all the orders where orderserviced=no to get all the open orders.

You may still do what is similar to joins at application level if one of the table is small and can be cached by the application. You can do a hash look up of the id and get the actual value.

In general, you should be conscious that NoSQL databases come with a limited functionality. But they give more flexibility in-terms of the schema and generally tuned for high-volume applications and clustering/HA in mind.


recover postgresql database after removal with --purge

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

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

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

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

Always have a periodic, automatical backup from your databases.