Backup plan for MySQL NDB cluster databse not innoDB

You should go through some more articles regarding cluster to make better understanding.
I have put here the steps of taking cluster backup and restore with 4 data node scenario.

1.MySQL Cluster Native Backup Tool (ONLINE Backup) and Restore

1.1

ndb_mgm> START BACKUP <Option>

<Option>:NOWAIT, WAIT STARTED, WAIT COMPLETED

NOWAIT - This returns a response to the display immediately and             begins the backup without waiting for responses from the data nodes.

WAIT STARTED - This returns a response to the display only when a response is returned from all the data nodes saying the backup has begun.

WAIT COMPLETED - This returns a response to the display only when a response is returned from all the data nodes saying that the backups have been completed successfully.
In most cases, using the START BACKUP WAIT COMPLETED is the best option:

 ndb_mgm> START BACKUP WAIT COMPLETED

Files that are created on Disk

1.1.1   .ctl - This group of files (labeled backup-..ctl) is          the metadata from the cluster for each data node.

1.1.2   .log - This group of files (labeled backup-..log)             contain the UNDO log files to keep the backup consistent and to ensure that no locks are needed during the backup process.

1.1.3   .Data - This group of files (labeled backup--                    ..Data) contains the data for the entire system.

1.2 To interrupt the backup in progress.

If the abort was successful, a message will be displayed stating this and issuing an error (which is to be expected).If a backup id is entered, but there is no backup occurring that is associated with that id, there is a response but without an error being issued:

ndb_mgm> ABORT BACKUP <backup id>
ndb_mgm> ABORT BACKUP 6

1.3  ndb_mgm> <node_id> REPORT BackupStatus

This command provides a status report on a cluster backup at anytime during the backup for node_id, or for all data nodes using ALL.

1.4  Restoring cluster Backup

1.4.1 Shutdown the cluster and then Restart Management Node

1.4.1.1

 ndb_mgm>SHUTDOWN

1.4.1.2

/usr/local/mysql/bin/ndb_mgmd

1.4.2   Start and empty the data nodes:-

Prior to restoring a backup, it is important to start and empty the data nodes.This is accomplished by starting each data node with the --initial option.

./ndbd  --initial

1.4.3   Restore the Cluster Backup

1.4.3.1 ndb_mgm> ENTER SINGLE USER MODE [Node-Id of First Free Slot]

1.4.3.2 In an operating system console, enter the following                     command from the /var/lib/mysqlcluster/BACKUP/BACKUP-1/ directory:

ndb_restore -m -r -b 1 -n 2   /* -- For First DataNode*/
ndb_restore -r -b 1 -n 3      /*-- For Second DataNode */

1.4.3.3

ndb_mgm> EXIT SINGLE USER MODE

2. MySQL Cluster Backup through MySQLDump Utility

2.1

shell> mysqldump -u root -p --lock-tables Dd_Name > Db_Name-backup.sql

2.2 Restoring mysqldump Files

shell> mysql -u root -p < Db_Name-backup.sql


Why does this say there is no join predicate?

It's happening here because the predicate slard.AreaNum IN ('40') is pushed to both ends of the Nested Loop Join operation.

I have a blog post about this same thing, but what it boils down to is that your WHERE clause matches a JOIN clause ON slard.AreaNum = ac.AreaNum

In these cases, the optimizer can push SARGable predicates to the the index access (seek or scan), because it's assured that whichever values come out will match.

This is called an implied predicate, and you can read more about them here:

Craig Freedman

Some Bozo

You can ignore the warning in this case.

Hope this helps!


That's exactly the benefit the programming manager of one of the companies I work with highlighted, he told me "with serverless we can test with the same performance level of the production database, but paying less. We will pay only for the specific period of times when we test".

Azure SQL Serverless application scenarios:

Examples:

New apps when compute sizing requirements are unknown.
Development/test workloads
Line-of-business applications
Content management systems, e-commerce and more.

Typical serverless usage patters are:

Intermittent, unpredictable usage and lower average compute utilization over time.
Frequently rescaled databases.
New databases without usage history.

Usage patterns when serveless is not recommended:

More regular, predictable usage and higher average compute utilization over time.
Databases sensitive to performance trade-offs from scaling.
Multiple databases that can be consolidated into elastic pools.

Price-performance shape (serverless).

In terms of auto-scaling, min and max vcore range available to database.  Memory and IO limits proportional to vCore range selected.
Database is auto-paused if inactive for long enough. Database is autoresumed when login is received.
In terms of billing, compute billing is per second based on vcores and memory used. Minimum compute billed based on min vcores and min memory configured. Only the storage is billed when the database is paused.

The following image compares provisioned compute vs serverless compute.


You need to add super-key (unique index/constraint) on both (id,topic_id). This gives you the  "target" uniqueness to create your foreign key. This acts like a CHECK constraint in this case.

ALTER TABLE comments ADD
    FOREIGN KEY (parent_comment_id, topic_id) REFERENCES comments (id, topic_id)

Note: id remains as primary key to preserve the model. Even if id is serial, it's would be wrong from a modelling perspective to change the PK to (id,topic_id)


Since recalculating rankings for a large table can take a long time, and doing that frequently can mean a really long time, start with this question:

Will the recalculating during SELECT be "fast enough"?  If the answer to this question is 'yes', then simply do it.
Will the recalculation will be "fast enough" to do during INSERTs.  Also, ask whether the burden on the system is "too much".  If this is OK, then simply do it.
If both of those don't pass muster, periodically recompute the rankings.

Note that MySQL 8.0 and MariaDB 10.2 have ranking and other "windowing functions".  However, you need to test them -- they may not be "fast enough"; after all 'ranking' probably requires reading and sorting the entire table.


There are instructions on the pgAdmin site how to install with pip:

https://www.pgadmin.org/download/pip4.php

But there is no official Debian Package (.deb), yet. A request for packaging is pending:

https://bugs.debian.org/cgi-bin/bugreport.cgi?bug=834129

For now, consider the Postgres Apt repository:

https://wiki.postgresql.org/wiki/Apt/FAQ


What's the structure of a matchmaking server?

Try this:-

SELECT id
      ,title
      ,DESCRIPTION
      ,max_players
      ,host_username
      ,host_ip
      ,(
           SELECT COUNT(*)
           FROM   matches_players
           WHERE  match_id = id
       ) AS player_count
FROM   matches


A possible solution would be to create a #tmpHold table with the same columns as #tmp minus the dbSet column.  Insert the results of the exec into #tmpHold and then insert into #tmp the data from #tmpHold plus the extra column @dbSetc.

create table #tmpA
(
    idx int identity(1,1),
    lpath nvarchar(260)
)

------------ ADD ALL DB Backup Files below ------------
insert into #tmpA (lpath)
    Select
    'C:\temp\db.bak' union select
    'C:\temp\db1'
------------ END EDIT ------------

create table #tmp
(
    LogicalName nvarchar(128)
    ,PhysicalName nvarchar(260)
    ,Type char(1)
    ,FileGroupName nvarchar(128)
    ,Size numeric(20,0)
    ,MaxSize numeric(20,0)
    ,FileId tinyint
    ,CreateLSN numeric(25,0)
    ,DropLSN numeric(25, 0)
    ,UniqueID uniqueidentifier
    ,ReadOnlyLSN numeric(25,0)
    ,ReadWriteLSN numeric(25,0)
    ,BackupSizeInBytes bigint
    ,SourceBlockSize int
    ,FileGroupId int
    ,LogGroupGUID uniqueidentifier
    ,DifferentialBaseLSN numeric(25,0)
    ,DifferentialBaseGUID uniqueidentifier
    ,IsReadOnly bit
    ,IsPresent bit
    ,TDEThumbprint varbinary(32)
    ,SnapshotUrl nvarchar(128)
    ,dbSetC int
)

create table #tmpHold
(
    LogicalName nvarchar(128)
    ,PhysicalName nvarchar(260)
    ,Type char(1)
    ,FileGroupName nvarchar(128)
    ,Size numeric(20,0)
    ,MaxSize numeric(20,0)
    ,FileId tinyint
    ,CreateLSN numeric(25,0)
    ,DropLSN numeric(25, 0)
    ,UniqueID uniqueidentifier
    ,ReadOnlyLSN numeric(25,0)
    ,ReadWriteLSN numeric(25,0)
    ,BackupSizeInBytes bigint
    ,SourceBlockSize int
    ,FileGroupId int
    ,LogGroupGUID uniqueidentifier
    ,DifferentialBaseLSN numeric(25,0)
    ,DifferentialBaseGUID uniqueidentifier
    ,IsReadOnly bit
    ,IsPresent bit
    ,TDEThumbprint varbinary(32)
    ,SnapshotUrl nvarchar(128)
--  ,dbSetC int
)
declare @countr as INT
declare @size as INT
declare @dbSet as INT
declare @tmpPath as nvarchar(260)

SET @countr=1
SET @size=(SELECT count(*) FROM #tmpA)

WHILE @countr <= @size
BEGIN
    SET @tmpPath = (SELECT lpath FROM #tmpA WHERE idx = @countr)
    SET @dbSet = (SELECT idx FROM #tmpA WHERE idx = @countr)

    TRUNCATE TABLE #tmpHold;
    INSERT INTO #tmpHold    --insert results of EXEC into #tmpHold
        EXEC ('RESTORE FILELISTONLY FROM DISK = ''' + @tmpPath + '''') --need to add @dbSet to dbSetC column
    INSERT INTO #tmp    --insert #tempHold + @dbSet into #tmp
        SELECT *,@dbSet FROM #tmpHold
    SET @countr = (@countr + 1)
END


What can I use for the PowerShell notin operator in v2.0?

You can use -NotContains in PowerShell v2.0 and up. The only thing you will need to do is just reverse your check. Using -NotIn you are comparing your current value to the collection, with -NotContains you do the reverse by comparing your collection to your current value.

So just change this:

Where-Object { $_.DatabaseName -notin ($NoBackups.DatabaseName) }

To

Where-Object { $NoBackups.DatabaseName -NotContains $_.DatabaseName }

A rough example:


Form groups computing max - min depending on number of peers

If I understand correctly ...

Use a subquery and compute the value depending on the count of rows.
Then get the final result using the window function lag():

SELECT time_interval
     , CASE WHEN ct > 1 THEN span ELSE span - lag(span) OVER (ORDER BY time_interval) END AS span
FROM  (
   SELECT date_trunc('minute', datetime) - ((EXTRACT(MINUTE FROM datetime)::int) % 15) * interval '1 minute' AS time_interval
        , CASE WHEN count(*) > 1 THEN MAX(value) - MIN(value) ELSE MAX(value) END AS span
        , count(*) AS ct
   FROM   point
   WHERE  datetime BETWEEN '2011-01-01 07:00:00' and '2011-01-01 07:45:00'
   GROUP  BY 1
   ) sub


What is the format of EXTERNAL NAME argument?

The correct format is <JAR name>:<class name>.<method name>, <class name> is fully qualified, i.e. includes the package name. Since you are still getting this error, it's likely that your method signature does not comply with the convention you use. You don't specify the PARAMETER STYLE option, meaning DB2GENERAL is used by default, which is not recommended. Use PARAMETER STYLE JAVA and make sure the method signature is correct (public static void mth(int arg1) in your contrived example).
Note also that

For updating Java™ routines that are built into JAR files, you must issue a CALL SQLJ.REFRESH_CLASSES() statement to force the database manager to load the new classes. If you do not issue the CALL SQLJ.REFRESH_CLASSES() statement after you update Java routine classes, the database manager continues to use the previous versions of the classes. The database manager refreshes the classes when a COMMIT or ROLLBACK occurs. (source)


Unistalled MySQL 5.6 and installed MySQL 5.7 - Restore Database and Tables

The "user" that logged into Workbench did not have permissions to see the rest of the databases.

Also, did you run mysql_upgrade?


Insert data from a source table into a destination table with more columns?

SELECT * selects col1, col2, col3, as defined in table2, and if the selection doesn't match the target, it will fail.

the conclusion is to alter the selection:

INSERT INTO table1
SELECT col1, col2, NULL as col_extra, 'default' as col_extra2, col3, [...]
FROM table2

not tested, but should work


Deleting near duplicates based on one varchar field

So, rephrasing your requirement a little, you want to delete every row whose pos has the value of OF and for which another row in the same table exists with the same plyrID, year and teamID, and with pos of RF, LF or CF.

That sounded almost SQL, you would only need to correct some "grammar":

DELETE FROM
  MyTable
WHERE
  pos = 'OF'
  AND EXISTS
  (
    SELECT
      *
    FROM
      MyTable AS t2
    WHERE
      t2.plyrID = MyTable.plyrID
      AND t2.year = MyTable.year
      AND t2.teamID = MyTable.teamID
      AND t2.pos IN ('RF', 'LF', 'CF')
  )
;

The above is standard SQL but it wouldn't work in MySQL. Needs rewriting with JOIN:

DELETE MyTable
FROM MyTable
  JOIN
      MyTable AS t2
    ON
      t2.plyrID = MyTable.plyrID
      AND t2.year = MyTable.year
      AND t2.teamID = MyTable.teamID
      AND t2.pos IN ('RF', 'LF', 'CF')
WHERE
  MyTable.pos = 'OF'
;


How to return rows with a specific value last?

This seems like a much simpler solution than introducing a bunch of unions:

SELECT code_id as CategoryID, code_desc as CategoryDescription
FROM dbo.codes
WHERE code_id > '000001' AND code_id < '000100'
ORDER BY CASE code_id
  WHEN '000002' THEN 3
  WHEN '000003' THEN 2
  ELSE 1 END, CategoryDescription;


Get results from "combined, either inner join"

Venn diagrams are not ideal to visualize join operations like a_horse commented:

Venn diagrams do not visualize joins, but set operations like union, intersect or except.

And his link to illustrate:

https://blog.jooq.org/2016/07/05/say-no-to-venn-diagrams-when-explaining-joins/

The twist, I think, is this: the Venn diagram is the right tool to visualize your objective, but the SQL JOIN is the wrong approach. You need a UNION (or equivalent) at the core of your query.
Title and the example query are misleading like that. What's more, you  start with:
SELECT * FROM ...

That gets all columns from all joined tables. Also not the expressed objective:

all organizations I'm either moderator or administrator.

UNION
The core query is:
SELECT organization FROM admin_organization     WHERE user = 10
UNION
SELECT organization FROM moderator_organization WHERE user = 10

UNION. Not JOIN. And not UNION ALL - we don't want duplicate organizations in the result.
The column name organization is also misleading (IMHO). Should really be something like organization_id for clarity.
The resulting set of unique IDs may already be all that's needed. To flesh it out with more (or all) attributes of the organization (columns of table organization), now we JOIN to the table:
SELECT *
FROM  (
   SELECT organization AS id FROM admin_organization WHERE user = 10
   UNION
   SELECT organization FROM moderator_organization WHERE user = 10
   ) x
JOIN   organization USING (id);

It's typically (substantially) cheaper to apply UNION on just the ID column, and then join. It may even be a necessity, if some of the columns have types have data types with no equality operator. More common than one might think. See:

How to sum the values of a json column filtered with regex?

Assuming referential integrity, enforced with FK constraints, nothing is lost in the join.
For convenience, it added a column alias in the subquery (organization AS id), so that we can use the simplified join condition with USING (id) which, in turn, allows us to use the simple (and now correct) SELECT * in the outer query to get all columns of the table organization without a duplicate ID column.
Alternative with EXISTS
Shorter equivalent that also avoids undue duplication or elimination of rows:
SELECT *
FROM   organization o
WHERE  EXISTS (SELECT FROM admin_organization     WHERE user = 10 AND organization = o.id)
   OR  EXISTS (SELECT FROM moderator_organization WHERE user = 10 AND organization = o.id);

Naming convention
A naming convention with descriptive names can avoid some of the confusion and noise. Use organization_id (or org_id if you prefer short names and there is no ambiguity) for all three: admin_organization.organization, moderator_organization.organization, and organization.id.


An Index Only Scan (not "query") is a step in an execution plan that retrieves data only from an index and does not require a lookup to the actual table data.

This optimization can only be used if there is an index that contains all columns that are needed by the query. If such an index exists, that index is called a "covering index" because it "covers" all needed columns.

Or to put it the other way round: only if there is a covering index, the query optimizer can use an Index Only Scan.


Parent/Child Relationship Table Design - What's The Best Practice?

The technique you are describing for representing task hierarchy is called 'Adjacency list'. Although it is the most intuitive to humans, it doesn't lend itself to very efficient querying in SQL. Other techniques include path enumeration (aka materialized paths) and nested sets.
To learn about some other techniques, read this post or search the web for numerous articles on these techniques.

SQL Server offers a native hierarchy representation for path enumeration. This is most likely your best bet...


The MSX/TSX is indeed related to the SQL Agent, allowing you to schedule jobs on a master server and have those jobs pushed out to slave servers.

CMS is an extension on the registered servers concept and allows you register servers into the MSDB database of a single instance through the SSMS GUI.  Once the servers are registered in the CMS you can logically group them by business unit, version, etc.  Once you have them registered any DBA/user with permission to the instance can connect to the CMS and see the same grouping.

CMS does not allow you to schedule agent jobs like MSX does, but you are able to run a script through SSMS on servers grouped in the same folder in CMS.  For example, if you highligh a subfolder in CMS and hit New Query it will connect to all the instances registered in that folder and run the script on each one.  Below is an example script you could run; CMS will return the instance name and the user running the script as columns by default.  You can also use CMS for grouping Policy Based Management evaluations and policies.

  select @@VERSION as Verison


pt-query-digest --type binlog (percona-toolkit) gives empty output with MariaDB/Galera 10.1

See Reading RBR binary logs with pt-query-digest and LP #1377887: pt-query-digest fails to parse binary log with RBR events

That bug report is over 2 years old and ranked as "low" priority.  Suggest you add a comment to clue Percona in that there is interest in getting it fixed.


How do DTUs in Standard and Premium performance tiers in SQL Azure compare?

I was similarly confused when looking at the pricing for those two tiers. 100 DTUs on standard costs $150/mo and 125 DTUs on Premium costs $465/mo, I figured something else must explain that disparity. I think this line from https://docs.microsoft.com/en-us/azure/sql-database/sql-database-service-tiers-dtu#choosing-a-service-tier-in-the-dtu-based-purchasing-model must explain the difference:

                            | Standard                   | Premium
IO throughput (approximate) | 2.5 IOPS per DTU           | 48 IOPS per DTU
IO latency (approximate)    | 5 ms (read), 10 ms (write) | 2 ms (read/write)

So it looks like a Premium DTU is actually worth 19x more than a Standard DTU


Always On Setup - OBJECTSTORE_SERVICE_BROKER Memory clerk eating all the memory

You mentioned that you're not explicitly using service broker anymore, but Availability Groups (AGs) use service broker behind the scenes.  This system is called the Usage Control Service (UCS) and is used for queuing and sending groups of log blocks from the primary to the secondaries.

I was able to find at least one bugfix for this system causing memory issues:

FIX: Service Broker UCS task leaks memory in SQL Server 2014 or 2016

  Assume that you have AlwaysOn Availability Groups configured in SQL Server 2014 or 2016. If an application uses the TABLOCK hint, the Service Broker Usage Control Service (UCS) task leaks memory. In this situation, you can see that the number of tasks in the sys.dm_os_tasks function continuously grows. This causes AlwaysOn synchronization to become slow.

Note that this bug was fixed in SQL Server 2016 (RTM) CU2, so unless this is a regression, this isn't the specific bug you're facing.

All that to say, this could be a legitimate bug in the product that's been fixed in a newer version.  If you're having trouble updating to SP2, consider trying to at least updating to the latest CU for SP1 to see if the problem has been resolved there.


run job with uncommitted transaction - how to find the transaction? is it still active?

Non-committed transactions will be automatically rolled-back if the session is terminated.
You can easily test this by:
USE tempdb;

CREATE TABLE dbo.TestTran
(
    ID INT NOT NULL
        CONSTRAINT PK_TestTran
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
);

BEGIN TRAN
INSERT INTO dbo.TestTran DEFAULT VALUES;
SELECT * FROM dbo.TestTran;

Results will show a row inserted into dbo.TestTran.
Now, disconnect and reconnect the session.
--disconnect and reconnect

USE tempdb;

SELECT * FROM dbo.TestTran;

The results from the above SELECT statement will return no results since the non-committed transaction was automatically rolled back when the session was disconnected.
This is a primary tenet of all ACID-compliant database management systems.

Atomicity
A transaction is a unit of work in which a series of operations occur between the BEGIN TRANSACTION and END TRANSACTION statements of an application. A transaction executes exactly once and is atomic — all the work is done or none of it is.

Once you disconnect the session (or it completes without a COMMIT TRAN), the transaction will rollback to ensure none of the work has been done since the BEGIN TRAN statement.


How to monitor performance in application insights of an on prem SQL Server database?

I don't know about application Insight but many things already exists inside of an SQL prem server to allow you to monitor your server. I'll put here what I use in case it can help you.
For the number of queries per hour/day I monitor perfmon counter "Requests batch per second". https://www.brentozar.com/archive/2017/02/what-is-batch-requests-per-second/
You can also turn on the query store which will help you to monitor performances of queries and query plans.
But to get the number of records returned by each select and the performance of individual queries, you would need to run a trace on your server at all time. It seems to me a little too much and seems like it's going to maybe impact performances of your Server.
What I do is, I monitor perfmon counter latches and when it goes higher than my above threshold, I get an alert and go check what queries are running and do performance check on them. It allows me to catch the problems as they happen. https://www.mssqltips.com/sqlservertip/3088/explanation-of-sql-server-io-and-latches
To monitor perfmon we use a monitoring tool, Zabbix.
You can also use Brent Ozar's sp_blitz to help you. https://www.brentozar.com/blitz/
It's a really useful tool that I use also and it's free.


LOAD DATA (400k rows) INFILE takes about 7 minutes, cannot kill the "logging slow query" process?

The query under process ID 180233 looks like it is in distress.
Here is the query itself
SELECT COUNT(DISTINCT A.`campaignid`)  INTO _c
FROM `ox_campaigns` A
INNER JOIN `selfserving_users` B ON B.`user_id` = A.`uid`
INNER JOIN `v3_cam_date` C ON C.`campaignid` = A.`campaignid`
WHERE A.`revenue_type` = 5 AND A.`deleted` = 0
AND A.`expire` = DATE_ADD(CURRENT_DATE, INTERVAL 1 DAY)
AND A.`isExpired` = 0
AND IF( NAME_CONST('_permitid',3) = -1, 1=1,
IF( NAME_CONST('_permitid',3) = 0, A.`uid` IN
(SELECT C.`user_id` FROM `selfserving_users` C
WHERE C.`groupid` =  NAME_CONST('_groupid',12) ) ,
A.`uid` =  NAME_CONST('userid',388)));

The scary part about the query is the self-reference
You have selfserving_users acting in a self serving manner against itself.
Sometimes, the MySQL Query Optimizer will play a bait-and-switch, smoke-and-mirrors games with data, especially with a self reference, in order to formulate the best EXPLAIN plan possible. While mysql is very capable of completing sub-SELECTs, it can be still be expensive.
However, this is just a symptom that manifested because of Process ID 97. What is really the issue here?
LOAD DATA INFILE against an InnoDB table could make mysqld a little punch drunk. I don't believe (or at least I don't exercise full confidence) you can encapsulate it as a normal transaction although this was addressed back in MySQL 5.0.
Just picture it:

You are hammering the InnoDB Buffer
Some memory swapping may be going on
Possible full table locking issues that are affecting data pages outside the v3_zone_date table (such was with the selfserving_users table)

There may be a way to throttle the LOAD DATA INFILE process on an InnoDB table. I cannot give you a solid answer on this one, but try this link from Baron Schwartz.
UPDATE 2012-02-22 12:00 EST
There is open bug report in MySQL 5.5.7 called Deadlock when DDL under LOCK TABLES WRITE, READ + PREPARE. At the bottom of the report, a person complained about a block problem cause by the explicit LOCK TABLES.
Launching a COMMIT on locked rows in a table would hang because of trying to unraveling MVCC data assocaited with the locked rows. Based on the InnoDB Status you have shown, there would exist 6933 row locks on the table you are importing. I know that in Oracle, when introducing new rows to a table, MVCC is still generated because the previous version of the newly inserted row is a nonexistent row. The same must be occurring for InnoDB.
UPDATE 2012-02-22 12:42 EDT
In your question you stated the following about your .NET process

LOCK TABLES;
SET autocommit=0;
SET unique_checks=0;
SET foreign_key_checks=0;
LOAD DATA;
COMMIT;
UNLOCK TABLES;
SET autocommit=1;
SET unique_checks=1;

All of these events are running within the same DB Session. This is also happening within one DB Connection. Thus, this is not a deadlock in the traditional sense. It is just a case of blocking your COMMIT within a given DB Connection/Session because the tables were locked within the same DB Connection/Session.
UPDATE 2012-02-23 19:00 EDT
I would change the sequence to be this:

SET autocommit=0;
SET unique_checks=0;
SET foreign_key_checks=0;
LOCK TABLES;
LOAD DATA;
UNLOCK TABLES;
COMMIT;
SET autocommit=1;
SET unique_checks=1;
SET foreign_key_checks=1;

Please remember, a COMMIT cannot proceed if you have the tables locked in serial fashion. Therefore, UNLOCK TABLES must precede COMMIT.


If you have the space, you can CTAS using minimal undo/redo. If you have any indexes at all, doing it any other way will be very slow and generate logging like crazy.

In the case where you have a single IOT without any secondary indexes, or a single table cluster, you could step through the primary/cluster key updating in chunks without having to rescan the whole table to find the fields that have not yet been updated.

--edit

  I am not able to create a secondary table ... There are a couple of
  indexes, but none of them involve the column I am updating.

Then I suggest break the table up into chunks for processing using something you are indexing on (even if it is a single column, you can split it into ranges of values) This will do a FTS once instead of once for each chunk as in your code. You will have to live with an awful lot of redo and will wipe out your undo space too (so no flashback subsequently)

--edit2

if you can add/rename/drop columns, you can do this very efficiently, but only on 11g


How to design a main list and sublists schema

One issue I have with your current model is that it would be possible for a FavouritesList to contain FavouritesListItems that belong to different Users, which to my understanding of your business logic shouldn't happen. I think a better way would be to store all FavouritesListItems that don't belong to a particular FavouritesList in some entity that is designated as a User's "UngroupedFavouritesList". There are a few ways to create that entity.

Method 1
Make the Title attribute in FavouritesList nullable. If it is null, then it is considered to be the User's "UngroupedFavouritesList".

Method 2

Add an "isUngrouped" flag to the FavouritesList table. If it's set to true, the it's considered the User's "UngroupedFavouritesList". The title attribute of FavouritesList would be null in this case.

Method 3

Make FavouritesList a parent table with UngroupedFavouritesList and NamedFavouritesList as child tables. This is achieved by having the primary key of UngroupedFavouritesList and NamedFavouritesList also be a foreign key to the FavouritesList's primary key.

Method 4

Create an "IFavouritesContainer" interface table, which both FavouritesList and User implement. This is achieved by User and FavouritesList having a foreign key to the IFavouritesContainer table. The FavouritesListItem then has a reference to the IFavouritesContainer that contains it. For ungrouped list items, its IFavouritesContainer would be the User. Note that the idea of implementing interfaces isn't a built-in thing with any RDMS that I know of, I'm just using the terms as an analogy to object oriented programming.

Pros and Cons for each method

        Method 1
        Pros:

            Simple

        Cons:

            It's not obvious that a null title means it's an "UngroupedFavouritesList
            It's possible for a User to have multiple "UngroupedFavouritesLists"

        Method 2
        Pros:

            Simple
            Clear what is an "UngroupedFavouritesList" and what isn't

        Cons:

            Storing a title for the "UngroupedFavouritesList", even though it will always be null. This is very minor.
            It's possible for a User to have multiple "UngroupedFavouritesLists"

        Method 3
        Pros:

            Clear what is an "UngroupedFavouritesList" and what isn't
            Tables don't contain unnecessary fields

        Cons:

            It's possible for a User to have multiple "UngroupedFavouritesLists"
            More complex

        Method 4
        Pros:

            Tables don't contain unnecessary fields
            The business logic of a User containing only one "UngroupedFavouritesList" but multiple "NamedFavouritesLists" is preserved

        Cons:

            More complex
            Not obvious what it means for a FavouritesListItem to be contained by Users vs FavouritesList


In the comments you mention that a UserInput is either a Msg or an Action and not related to many actions or to many messages. The first model does not accurately describe this relationship.

You correctly identify that the problem is how to make Action and Msg "extend" UserInput. This is often called Supertype/Subtype pattern in database design.

I would make (UserInputID) as the PRIMARY KEY in both Action and Msg:

UserInput
  UserInputID   PK

Action
  UserInputID   PK, FK1
  TabID         FK2

Message
  UserInputID   PK, FK1
  GroupID       FK2

The rest of the tables would stay unchanged. This is the easiest thing to do, assuming that you have the freedom to define how the IDs are populated (for every new Action, a new UnserInput is inserted and the ID is used for the Action, too. Similarly for the Msg table.)
It still does not address all potential problems. A malicious or wrong update can have the database into some weird status, where a UserInput is both an Action and a Msg.

An altered design can overcome the issue by adding a Type column and some constraints:

UserInput
  UserInputType  PK
  UserInputID    PK
  CHECK (UserInputType IN ('A', 'M'))

Action
  UserInputType  PK, FK1
  UserInputID    PK, FK1
  TabID          FK2
  CHECK (UserInputType = 'A')

Message
  UserInputType  PK, FK1
  UserInputID    PK, FK1
  GroupID:       FK2
  CHECK (UserInputType = 'M')

CriticalPoint
  UserInputType  PK, FK1
  UserInputID    PK, FK1


How can I prove NOLOCK is the source of deadlocking problems?

The architect-in-charge how insists NOLOCK is the way to go. Recommends with all ad-hoc queries and whenever querying production. I haven't seen a sproc without nolock hints on every table.

Sorry to hear that. This is pretty well universally regarded as an anti-pattern among expert SQL Server practitioners, but there may be nothing you can do to change the facts on the ground if the practice is truly ingrained, and based on one person's sincerely and deeply-held beliefs.

The question says that "sending a link may not be enough", but it's unclear what you want instead. We could write the most compelling argument in the world in an answer here, and you would still be left "sending a link" to it. Ultimately, no one here can know which set of arguments will be successful in your specific situation (if any).

Nevertheless, the following cover most points that some people find persuasive enough to change a previously common practice:

Bad habits : Putting NOLOCK everywhere by Aaron Bertrand
SQL Server NOLOCK Hint & other poor ideas By David Lean (Microsoft)
The Read Uncommitted Isolation Level by me
Justify NOT using the (nolock) hint in every query (related Q & A here)

Even so, you may not be able to 'win' this battle. I have worked in an environment that did this, understood the risks and reasons not to do it, but continued with it anyway. These were bright, logical people, but in the end it was an environment I could not be happy working in.

  Currently some other DBAs are wrestling with some mysterious deadlocking. How does one determine that NOLOCKs are the source.

The more usual pattern (perhaps more prevalent in the past) in that NOLOCK hints are introduced in an attempt to reduce the incidence of deadlocks. This can 'work' in that reducing the number of shared locks taken naturally reduces the chances of incompatible locks being taken, but it is not a good solution to the underlying problem, and comes with all the caveats noted in the previous section.

Nevertheless, NOLOCK hints can introduce new ways to deadlock. Using read uncommitted isolation can remove a transient blocking condition (resolved when the contended resource becomes available) into an unresolvable deadlock (where neither waiter can make progress) simply because the contention now happens at a different point, where e.g. write operations in a different order overlap. Dave Ballantyne has an example here:

Increasing deadlocks with NOLOCK

For more general advice about dealing with deadlocks, I recommend the following as a starting point:

The Difficulty with Deadlocks by Jeremiah Peschka

You should also familiarise yourself with the documentation:

Deadlocking (and all three sub-topics there)

Other useful resources:

SQL Server Deadlocks by Example by Gail Shaw
Deadlock troubleshooting by Bart Duncan


Results of SAMPLE clause using a DBlink between 2 Oracle databases not randomised

According to the Oracle documentation for the SELECT ... SAMPLE clause, the sample is random; it is just a coincidence that you are always getting the same rows on the local machine.  You could try adding the SEED(x) clause, where x is a number from 0 to 4294967295; but that may not help going across a database link.

select * from myTable@myDbLink sample(10) seed(42);

Per the Oracle documentation:

  SEED seed_value Specify this clause to instruct the database to attempt to return the
  same sample from one execution to the next. The seed_value must be an integer
  between 0 and 4294967295. If you omit this clause, then the resulting sample will
  change from one execution to the next.

Notice it is still not guaranteed!


Query optimizer recommends adding index instead of using existing index

Your index is seemingly fine and good (i.e. covering) for the query and it should be used. The real problem is the query itself and specifically this condition which hides an implicit conversion:

WHERE [serialNumber] = 137802

According to SQL Server's datatype precedence, when two values of different datatypes are compared, the value with the datatype of lower precedence is converted to the datatype of the higher precedence. Unfortunately, int is higher in the list than varchar. This blows up any hope of using the index as the column (serialNumber) values are converted to integers. The column being the 1st position of the index, leads the optimizer to not use that index and search for an alternative (and thus the suggestion.)

Solution is not to have any implicit or explicit conversions of columns in WHERE condition. Simply use:

WHERE [serialNumber] = '137802'


You could go with your own idea of an asymmetric schema by removing one of the foreign keys from the current set-up, or, to keep things symmetric, you could remove both foreign keys and introduce a junction table with a unique constraint on each reference.

So, it would be like this:

CREATE TABLE dbo.Hazard
(
  HazardId int IDENTITY(1,1) NOT NULL CONSTRAINT PK_Hazard PRIMARY KEY CLUSTERED,
  Details varchar(max) NULL
);

CREATE TABLE dbo.Task
(
  TaskId int IDENTITY(1,1) NOT NULL CONSTRAINT PK_Task PRIMARY KEY CLUSTERED,
  Details varchar(max) NULL,
);

CREATE TABLE dbo.HazardTask
(
  HazardId int NOT NULL
    CONSTRAINT FK_HazardTask_Hazard FOREIGN KEY REFERENCES dbo.Hazard (HazardId)
    CONSTRAINT UQ_HazardTask_Hazard UNIQUE,
  TaskId int NOT NULL
    CONSTRAINT FK_HazardTask_Task FOREIGN KEY REFERENCES dbo.Task (TaskId)
    CONSTRAINT UQ_HazardTask_Task UNIQUE
);

You could additionally declare (HazardId, TaskId) to be the primary key if you need to reference these combinations from another table. For the purpose of keeping the pairs unique, however, the primary key is unnecessary, it is enough that each ID is unique.


Optimizing an audit table for inserts and deletes

Typically to optimize inserts/deletes for such a table you would cluster on the datetime column. (You are tracking when these events happen, I presume.)

This way, inserts go to the end of the table always, minimizing the "bad" kind of page splits. Deleting or archiving data is easy because the clustered index will support range deletes, and these operations will not typically be locking the pages where you're performing inserts (unless you have escalation for some reason).

You will probably want other indexes like what you're describing to support queries, and maintenance of these of course will interfere somewhat with DML.

I'm not sure I understand why you only want to keep a certain number of rows for any UserId/EventId combination. If one user does a whole bunch of stuff today, do you really want to remove all the stuff they did yesterday, last week, etc., while you keep older data for users that are less active? Wouldn't it make more sense to just keep data based on time (e.g. retain two weeks of history)?

I'm also not sure why the delete absolutely needs to be coupled to the insert. Can't the delete be deferred to a background process instead of holding up the insert transaction?


Get a specific number of rows from parent table?

Here's one way to do what you describe:

SELECT <columns>
FROM (
    SELECT <columns>
    FROM foo
    WHERE <condition>
    ORDER BY <expression-list-1>
    LIMIT 15
) AS T
LEFT JOIN bar
    ON T.a = bar.a
ORDER BY <expression-list-2> ;

You basically get 15 rows from foo according to some criteria and join the result with bar.

The first, internal ORDER BY list affects which 15 rows will be selected. The second, final list affects only the presentation ordering of the results.

The two ORDER BY lists do not need to be identical, although in practice they'll often be the same or the second an extension of the first, to resolve ties (how the children of the same parent are ordered).


A materialized view in Oracle is a combination of a structure to hold the data (a table), a job that refreshes the data (a job), and a process that figures out how to refresh the data based on the specified query.  This process would generally involve the creation and maintenance of materialized view logs on the base table to track changes so that the materialized view can be refreshed incrementally though that is technically optional.  It is also common to organize multiple materialized views into refresh groups so that the materialized views themselves are transactionally consistent with each other.

The Materialized View Concepts and Architecture chapter of the Advanced Replication manual is a good place to start reading up about this sort of thing.


If two entities are engaged in a many-to-many relationship with both entities participating totally, how many tables are needed?

Using a third table to hold links between two independent tables is the correct method when designing a simple many-to-many relationship. You don't specifically need flag the fields as NOT NULL because they form the Primary Key and so cannot be NULL anyway.

One note, however, you've stated in your example that a Student record MUST be associated with at least one Course record, and vice versa. Your table design would not enforce this requirement, it would only allow the possibility that Student and Course records could be linked - not that they must have at least one linked record.

Your example would not enforce total participation on both sides, and I am not sure that you even could enforce it. It raises the question of which comes first, the Student or Course record given that each of those record types requires at least one link to the other record type.

Without a Student record, no Course could be created because it doesn't have a linked record, and vice versa - it's the classic "which came first" chicken or egg scenario!


mongoDB: Limit user to find documents only by ObjectId

Currently, The lower level you can control the access of resources is at the collection level.

One option you can try is to create collections at each user level and give the user access to only that collection.


a SELECT or set-returning version of MERGE/UPSERT?

If you want to find the results of the "UPSERT" without actually performing it, you can run a query with FULL JOIN:

SELECT
    did,
    COALESCE(s.dname, t.dname) AS dname   -- in case of conflict,
                                          -- give precedence to source table
FROM
    distributors AS t
    FULL JOIN distributors_upd AS s
    USING (did) ;

Test at dbfiddle.uk (thnx to McNets for setting it up)

The columns that would be updated on the conflict by the merge will have the source column first in the COALESCE() function and the target column second. If the update is more complex, you'll have to use a more complex COALESCE() or CASE expression. Example:

ON CONFLICT UPDATE
    SET counter = counter + EXCLUDED.counter

will become

SELECT
    COALESCE(t.counter + s.counter, t.counter, s.counter) AS counter


why is this postgresql query returning only zeros?

You know you don't need to use all those subqueries, right?

select g.ad_id, count(1) as total,
count(case when utc_timestamp > '2017-11-01 00:00:00' AND utc_timestamp < '2017-11-01 23:59:59' then 1 end) NOV1,
count(case when utc_timestamp > '2017-11-02 00:00:00' AND utc_timestamp < '2017-11-02 23:59:59' then 1 end) NOV2,
...
FROM geoData g
group by g.ad_id ;


Your structure i commonly known as an Adjacency List (should perhaps be Set) Model. If you don't want to change the structure of your model, you basically have to do something similar to what you are currently doing. You can rewrite your query using joins like:

SELECT q1.* FROM qa_posts q1
WHERE q1.postid=1
UNION
SELECT q2.* FROM qa_posts q1
            JOIN qa_posts q2
                on q1.postid=q2.parentid
WHERE q1.postid=1
UNION
SELECT q3.* FROM qa_posts q1
            JOIN qa_posts q2
                on q1.postid=q2.parentid
            JOIN qa_posts q3
                on q2.postid=q3.parentid
WHERE q1.postid=1;

If you are willing to change the model you can search for Nested Set (example http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/), Materialized Path (example http://bojanz.wordpress.com/2014/04/25/storing-hierarchical-data-materialized-path/) and Transitive Closure Table (at the end of http://www.slideshare.net/billkarwin/models-for-hierarchical-data, at the beginning the other two models are described)

Edit: Adding DFS with different weights according to type:

select postid, parentid, type
from (
    SELECT q1.*
        , lpad(q1.parentid,8,case q1.type when 'Q' then 'a'
                                          when 'C' then 'b'
                                          when 'A' then 'c'
                             end)
        || lpad(q1.postid,8,case q1.type when 'Q' then 'a'
                                         when 'C' then 'b'
                                         when 'A' then 'c'
                             end) as padded_path
    FROM qa_posts q1
    WHERE q1.postid=1
    UNION
    SELECT q2.*
       , lpad(q1.parentid,8,case q1.type when 'Q' then 'a'
                                         when 'C' then 'b'
                                         when 'A' then 'c'
                            end)
        || lpad(q1.postid,8,case q1.type when 'Q' then 'a'
                                         when 'C' then 'b'
                                         when 'A' then 'c'
                            end)
        || lpad(q2.postid,8,case q2.type when 'Q' then 'a'
                                         when 'C' then 'b'
                                         when 'A' then 'c'
                            end) as padded_path
    FROM qa_posts q1
    JOIN qa_posts q2
        on q1.postid=q2.parentid
    WHERE q1.postid=1
    UNION
    SELECT q3.*
         , lpad(q1.parentid,8,case q1.type when 'Q' then 'a'
                                           when 'C' then 'b'
                                           when 'A' then 'c'
                              end)
         || lpad(q1.postid,8,case q1.type when 'Q' then 'a'
                                          when 'C' then 'b'
                                          when 'A' then 'c'
                              end)
         || lpad(q2.postid,8,case q2.type when 'Q' then 'a'
                                          when 'C' then 'b'
                                          when 'A' then 'c'
                              end)
         || lpad(q3.postid,8,case q3.type when 'Q' then 'a'
                                          when 'C' then 'b'
                                          when 'A' then 'c'
                              end) as padded_path
    FROM qa_posts q1
    JOIN qa_posts q2
        on q1.postid=q2.parentid
    JOIN qa_posts q3
        on q2.postid=q3.parentid
    WHERE q1.postid=1
) as x
order by padded_path;

+--------+----------+------+
| postid | parentid | type |
+--------+----------+------+
|      1 |        0 | Q    |
|      8 |        1 | C    |
|      2 |        1 | A    |
|      4 |        2 | C    |
|      5 |        2 | C    |
|      6 |        1 | A    |
+--------+----------+------+
6 rows in set (0.00 sec)

Edit: The other Questions result in (padded_path included):

+--------+----------+------+--------------------------+
| postid | parentid | type | padded_path              |
+--------+----------+------+--------------------------+
|      3 |        0 | Q    | aaaaaaa0aaaaaaa3         |
|      7 |        3 | C    | aaaaaaa0aaaaaaa3bbbbbbb7 |
+--------+----------+------+--------------------------+
2 rows in set (0.00 sec)

+--------+----------+------+----------------------------------+
| postid | parentid | type | padded_path                      |
+--------+----------+------+----------------------------------+
|      9 |        0 | Q    | aaaaaaa0aaaaaaa9                 |
|     10 |        9 | C    | aaaaaaa0aaaaaaa9bbbbbb10         |
|     11 |        9 | A    | aaaaaaa0aaaaaaa9cccccc11         |
|     12 |       11 | C    | aaaaaaa0aaaaaaa9cccccc11bbbbbb12 |
+--------+----------+------+----------------------------------+
4 rows in set (0.00 sec)

Changing the predicate q1.postid=1 to q1.type='Q' results in:

+--------+----------+------+----------------------------------+
| postid | parentid | type | padded_path                      |
+--------+----------+------+----------------------------------+
|      1 |        0 | Q    | aaaaaaa0aaaaaaa1                 |
|      8 |        1 | C    | aaaaaaa0aaaaaaa1bbbbbbb8         |
|      2 |        1 | A    | aaaaaaa0aaaaaaa1ccccccc2         |
|      4 |        2 | C    | aaaaaaa0aaaaaaa1ccccccc2bbbbbbb4 |
|      5 |        2 | C    | aaaaaaa0aaaaaaa1ccccccc2bbbbbbb5 |
|      6 |        1 | A    | aaaaaaa0aaaaaaa1ccccccc6         |
|      3 |        0 | Q    | aaaaaaa0aaaaaaa3                 |
|      7 |        3 | C    | aaaaaaa0aaaaaaa3bbbbbbb7         |
|      9 |        0 | Q    | aaaaaaa0aaaaaaa9                 |
|     10 |        9 | C    | aaaaaaa0aaaaaaa9bbbbbb10         |
|     11 |        9 | A    | aaaaaaa0aaaaaaa9cccccc11         |
|     12 |       11 | C    | aaaaaaa0aaaaaaa9cccccc11bbbbbb12 |
+--------+----------+------+----------------------------------+
12 rows in set (0.00 sec)


Receiving error : ORA-01747 While trying to calculate COUNT(*) of a specific table

That is not valid syntax.

SQL> select count(t.*) as x from dual t;
select count(t.*) as x from dual t
               *
ERROR at line 1:
ORA-01747: invalid user.table.column, table.column, or column specification

SQL> select count(*) as x from dual t;

         X
----------
         1

SQL>


Group values: CASE text, ELSE field value

How about ...

select distinct
   (case
       when road_class in('ARTERIAL A','ARTERIAL B','ARTERIAL C') then n'ARTERIAL'
       else road_class
     end ) as ROAD_CLASS_GROUPED
from user.road;

Due to the fact that the datatype of the column road_class is NVARCHAR, we need to write a  N  or  n  in front of the string literal 'ARTERIAL' in order to prevent the error message. (see also: https://docs.oracle.com/database/121/SQLRF/sql_elements003.htm#SQLRF00218 )


Why does Informatica hang and say "Not Responding" when connecting to ODBC Data Source

There are technically two windows for ODBC Data Sources in Windows.  The 64 bit window pops up when navigating to Control Panel > Administrative Tools > ODBC.  That connection for NETEZZA_I2B2_ETL worked.

However, the 32 bit dialog also had this data source.  But the connection failed there.  So after I fixed the connection, Informatica worked, and it created the source table.  Informatica must use the 32 bit ODBC connections, not the 64 bit ODBC connections.  "SysWOW64" means 32 bit.  Microsoft decided to keep the old folder names in Windows (System32 for example) and make them 64 bit folders in Windows 7.

C:\Windows\SysWOW64\odbcad32.exe

HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\ODBC\ODBC.INI

See this:

https://superuser.com/questions/713258/how-to-remove-odbc-data-source-if-driver-is-removed


What's the most appropriate type of index to use for this function-based query?

The list of expressions for an index must be put between parentheses on table_name (a,b,c). If the index expression is not just a column name, the expression itself must be enclosed with parentheses as well:

CREATE INDEX my_article_idx
   ON mydb_article ( (regexp_replace(url, '\?.*$', '')) );


DECODE or CASE STATEMENT for PARAMETER in WHERE CLAUSE

I don't have a database on hand to test this but can't you do something like this:

and SPC.CHARACTERISTIC in ('ARLFT','MINE')
and SPC.CHARACTERISTIC in ('&industry')

With both of those predicates present you will default to both values if you enter '%' but be restricted to just one if you enter either 'MINE' or 'ARLFT'


A Huge Database: Picking the right schema and data model

I have not experience such scenario but based on my present knowledge I would suggest you have a table which contains union of all the column which any client can need e.g. say client A wants  column 1 and 2 and client 2 wants column 2 and 3 and you have only these two clients then create a table with column 1,2 and 3 and in case of client 1 keep column 3 as null and in case of client 2 keep column 2 as null. Once you are done with this create view for each client e.g.create View A, it should have column 1,2 and will be used in case of client A similarly create view for each client.

Also I would suggest to keep data in table only and not separatly on disc as it create sync issue.


Are there standard SQL functions with side effects?

You have a few different questions in here.

Q: What are ANSI standard SQL functions?

ANSI standard functions are things like AVG, COUNT, MIN, MAX. They're covered in the 1992 ANSI standard, but that's one heck of a dry, boring read.

Q: Do ANSI standard SQL functions change data in the database?

No. You can use them to change data - for example, I can say:

INSERT INTO dbo.MyReport SELECT MAX(SalespersonRevenue) FROM dbo.Sales

But by themselves, just the use of a AVG, COUNT, MIN, MAX, etc shouldn't change data permanently inside your database.

Q: Does the ANSI standard allow me to write my own functions?

Yes, but the exact implementation varies from vendor to vendor. The functions you write may conform to the ANSI language standard, but what you do inside your function can be horrifically awful, like creating side effects.

When discussing intended behavior, it's possible to get a cross-platform answer.
When discussing side effects, it is not.

Q: Can I create my own function to write data?

Why sure, if you're creative. I'm a Microsoft SQL Server guy, so I'm going to focus on that platform. Books Online's function page says:

  User-defined functions cannot be used to perform actions that modify the database state.

To which I say:

  You're not my real dad.

So here's how I'd break the rules. Warning: very bad ideas follow.

In your function, query a new table specially created for this evil purpose, and then create something that watches the table for select statements, and then fires an action (Extended Events, auditing, or a Profiler trace). You can hook together a Rube Goldberg sort of contraption to perform work based on those select statements.
In the function, call CLR code - heck, you can even call a web service. That web service could very well push data back into your own database.
In the function, call xp_cmdshell and do something through the command prompt. (HT @AaronBertrand in the comments.)

All of these examples have huge drawbacks in the form of performance and transactional consistency. You just asked if it could theoretically be done, and the answer there is yes. I wouldn't ever use either of those in my own code - I'd step back and ask, "What's the business goal I'm trying to achieve here, and is there a way I can do it to achieve performance and transactional consistency?" If you'd like specific advice on those, I'd ask a separate Stack question with specifics.


A average Mark overall for all of one student subjects

you can use a subquery to get the result for average and the number of subjects

Create table  a_Student(
StudentId int(5) Not null,
StudentForename varchar (30) Not null,
StudentSurname varchar (45) Not null,
PRIMARY KEY(StudentId)
);

Create table a_Enlistment(
StudentId int(5) Not null,
SubjectId char(6) Not null,
results char (2),
primary key (SubjectId,StudentId),
#foreign key (SubjectId) references a_Subject(SubjectId),
foreign key (StudentId) references a_Student(StudentId)
);

insert into a_Student (StudentId,StudentForename,StudentSurname)
values ("33447","Alan","Burns"),
("33672","Mark","Harrison");

 Insert into a_Enlistment (StudentId,SubjectId,Results)
values
        ("33447","TMP243","78"),
         ("33447","AWD169","45"),
         ("33447","IIA887","48"),
        ("33672","TMP243","57"),
       ("33672","AWD169","67"),
      ("33672","IIA887","48"),
      ("33672","test","52")

✓

SELECT s.*
,avgresults
FROM a_Student s INNER JOIN (SELECT
StudentId,
AVG(Results) avgresults,
COUNT(*) countr FROM a_Enlistment
GROUP BY StudentId) e ON s.StudentId = e.StudentId
WHERE countr > 3

StudentId | StudentForename | StudentSurname | avgresults
--------: | :-------------- | :------------- | ---------:
    33672 | Mark            | Harrison       |         56

db<>fiddle here


You can solve this problem using intersect statements. Do a separate select for each tag_id and join them with intersects and you'll only get the records that match all three tag_ids.

select products.id, products.name from
products join tag_ties
on tag_ties.ref_id = products.id
where tag_ties.tag_id = 10
intersect
select products.id, products.name from
products join tag_ties
on tag_ties.ref_id = products.id
where tag_ties.tag_id = 11
intersect
select products.id, products.name from
products join tag_ties
on tag_ties.ref_id = products.id
where tag_ties.tag_id = 12

Here's a reference article on using intersect

You can also use a temporary view to make this look a little nicer.

create temporary view temp_view as
select name, products.id as id, tag_ties.tag_id as tag_id
from products join tag_ties
on tag_ties.ref_id = products.id

select name, id from temp_view where tag_id = 10
intersect ...


Best way to explain the importance of Raid 10 to SysAdmins

This is just a reference. A great session recording at SQL PASS Summit 2011 by Wesley Brown on this subject. It is an hour and 15 minutes of just storage talk. He goes over RAID configurations and then how they pertain to SQL Server and the pros and cons of each:
Understanding Storage Systems and SQL Server

You will need to register with SQL PASS in order to view it but it is well worth it. To find the recording go to this link and look for course "DBA-103". I don't think the same slide deck he uses is available for download but he posted the deck used at a few SQL Saturday events here.


How to install two mysql instance on same machine

You must have separate data trees and my.cnf files and Ports.  Your client(s) must pick the instance via the Port number.  That's about all you need to know.

You may use different binaries.

You may use VMs or Docker or ...  These may confuse the issues of Ports, binaries, etc, since they chop up your machine is ways that sort of give you separate logical machines.

There are several questions on this forum relating to this, and they discuss details for what to do before 5.7.6.


backing up selected tables in a database

The smallest for of a BACKUP DATABASE can only be a filegroup.  So in other words, if your tables are all confined to a selective filegroup you could achieve your goal through backing up that filegroup.

But I'm guessing that you probably don't have all of your backup-desirable tables in one filegroup isolated from the rest.  In which case you'll have to get a little creative.

What I'd do is just script out your desired tables.  You can do this by right-clicking your database and selecting generate scripts.  Walking through that wizard you can select the specific database objects to script out.  You can also set the option to back up both schema and data.


For this, you can make use of a very useful PostgreSQL feature - that of the partial index.

You have your table:

CREATE TABLE foo
(
  id BIGINT NOT NULL,
  --
  -- other fields
  --
  active BOOLEAN NOT NULL
);

Then, you do:

CREATE UNIQUE INDEX ON foo (id)
WHERE active;  -- if you prefer more clarity, could have "WHERE active = TRUE;"

So, we try it out (see the fiddle here):

--
-- `active` - all false for a given id - works
--
INSERT INTO foo
VALUES (1, FALSE), (1, FALSE), (1, FALSE);

check:

SELECT * FROM foo;

Result:

id  active
1   f
1   f
1   f

Another possibility - the specific one pertaining to the question:

--
-- one and only one `active` = true for a given id - works
--
INSERT INTO foo
VALUES (2, FALSE), (2, FALSE), (2, TRUE);

Check:

SELECT * FROM foo;

Result:

id  active
1   f
1   f
1   f
2   f
2   f
2   t

Final test:

--
--  attempt to INSERT two true `active` values for a given id - fails!
--
INSERT INTO foo
VALUES (3, FALSE), (3, FALSE), (3, TRUE), (3, TRUE);

Result:

ERROR:  duplicate key value violates unique constraint "foo_id_idx"
DETAIL:  Key (id)=(3) already exists.

The foo table is unchanged, which is what we want!


Copy SQL Logins and Permissions From One Server to Another

I created a series of stored procedures that accomplished a similar goal (with some powershell for flavor) based on this article Claire-Hsu - SQL Logins

I don't have a blog of my own (I know, I know) so I can't share my precise solution with you.  I essentially created three stored procedures.  One for the server logins, one for database and another for database object permissions.

Ah, shared on my one drive.  Link to files  It's the stored procedures you need.  I have them in my DBA database.  I then use Powershell to get a list of logins, then query the stored procedure, save the script to a file and repeat.  You could easily change it to run the script on a different server.


Every object in SQL Server has to be owned by someone. Ownership is used to control permissions. For example, any object that you own you have direct access to without it having to be granted to you. An other example is a procedure that has access to any object owned by the same owner. This allows someone with only access to the procedure to execute it even if it is accessing objects that are owned by someone else.

A schema is a grouping mechanism that most other RDBMSs dont have. (If an other RDBMS refers to a "schema" it means something more like what we call a "database".) Schemata allow to group objects of different types together into logical units within a single database. By default, the ownership of the schema is passed through directly to all objects in that schema. If you change the owner of the schema, the owner of all objects within changes too. (this can be changed on an object by object basis however.)

On the other end, you can grant access to a schema which in turn grants access to all objects within. This makes managing permissions a lot simpler.

Be aware, that you should always fully qualify every object with schemaname.objectname. The reason for this is two fold:

Performance: SQL Server tries to figure out the schema of an object that was referenced without schema specification based on different settings and rules. This burns precious cpu cycles unnecessarily.
While rare, it can lead to security and other access problems because the wrong object might be selected by SQL Server if multiple objects with the same name exist in different schemata.


T-SQL query - how to AVG() the SUM() of columns' rows to a new row column?

If you are looking for a different solution, you could always unpivot the data, aggregate and then join back on the source table.  Here is an example.

Also, thank you so much for including sample table and data to work with, so much easier.

;WITH CTE_Unpivot AS
    (
    SELECT StudentID, MarkSubject, Mark
    FROM dbo.Students AS S
        UNPIVOT (Mark FOR MarkSubject IN (Biology, Maths, [Geography])) AS upvt
    )
, CTE_Average AS
    (
    SELECT StudentID, AVG(Mark) AS StudentAverage
    FROM CTE_Unpivot AS U
    GROUP BY StudentID
    )
SELECT S.StudentID
    , S.StudentName
    , S.StudentSurname
    , S.Biology
    , S.Maths
    , S.[Geography]
    , A.StudentAverage
FROM dbo.Students AS S
    LEFT OUTER JOIN CTE_Average AS A ON S.StudentID = A.StudentID

I feel the need to point out that this is less efficient than just adding the fields together and dividing them.  Manipulating the data like this touches it multiple times, which means that it is more likley to spill to tempdb and have a higher cpu cost.  Probably negligible, but still.


Set non-negative floor for rolling sum, in SQL Server

Here's a recursive CTE example I came up with (which seems to work).  Is uses Row_Number() OVER to create a sequence number with no gaps. I don't know how well it would perform with your data, but it's something to try.

--Set up demo data
IF OBJECT_ID('tempdb..#temp') IS NOT NULL
    drop table #temp
go
create table #temp (PKID int, NumValue int, GroupID int)
insert into #temp values
(1,-1,1), (3,-2,1), (5,5,1), (7,-3,1), (9,1,2)

--here is the real code
;
with RowNumberAddedToTemp as
(
SELECT
  ROW_NUMBER() OVER(ORDER BY PKID ASC) AS rn,
  * from #temp
  )
,x
AS (
    SELECT PKID         --Anchor row
        ,NumValue
        ,RunningTotal = CASE
            WHEN NumValue < 0   --if initial value less than zero, make zero
                THEN 0
            ELSE NumValue
            END
        ,GroupID
        ,rn
    FROM RowNumberAddedToTemp
    WHERE rn = 1

    UNION ALL

    SELECT y.PKID
        ,y.NumValue
        ,CASE
            WHEN x.GroupID <> y.groupid     --did GroupId change?
                THEN CASE
                        WHEN y.NumValue < 0     --if value is less than zero, make zero
                            THEN 0
                        ELSE y.numvalue         --start new groupid totals
                        END
            WHEN x.RunningTotal + y.NumValue < 0    --If adding the current row makes the total < 0, make zero
                THEN 0
            ELSE x.RunningTotal + y.NumValue        --Add to the running total for the current groupid
            END
        ,y.Groupid
        ,y.rn
    FROM x
    INNER JOIN RowNumberAddedToTemp AS y ON y.rn = x.rn + 1
    )
SELECT PKID
    ,Numvalue
    ,RunningTotal
    ,GroupID
FROM x
ORDER BY PKID
OPTION (MAXRECURSION 10000);


Does setting DEADLOCK_PRIORITY to LOW cause a stored procedure to run slower?

Going to guess here... transaction log file sizes

In the test system was small and grew when you tested the first time with LOW
When you re-run without LOW, then log file was already sized correctly.

Easy to verify: check the "lastwaittype" in sys.sysprocesses (deprecated but useful) to see if you gave LOG related waits affecting the SPID doing the DELETE.

Otherwise double check that the 2 executions are identical

same data
any triggers enabled
ditto foreign keys, indexes
foreign key cascades
...

Also see Why does an UPDATE take much longer than a SELECT?


Locking on database from SELECT INTO #TempTable

I would expect the same behavior with or without the INTO. Remote queries run with default READ COMMITTED isolation level behavior acquire and release shared locks during execution just like local queries. Those locks can block writers of the remote tables, especially with poorly written queries and if shared table-level locks are held for the duration of the query.

Separately, the local SELECT INTO query consists of two separate transactions when run in autocommit mode. This is done to avoid holding long-term locks on the system catalog objects which could otherwise block object creation by other sessions. Creation of the new table is done in the first transaction and then the table is loaded using the remote query source in the second transaction.


Apply SP3 on SQL Server 2012 AlwaysOn AAGs

Before an upgrade, you should:

Perform a full backup of every database
Try a manual failover. Make sure the AG fails over a Synchronous replicas and that the synchronization state of the replicas is SYNCHRONIZED
Disable automatic backups on Secondary while they are being upgraded
Disable automatic backups on Primary before the upgrad of Primary
Do not upgrade Primary before any or all of the Secondary or .

Then here is the Rolling Upgrade Process:

Remove automatic failover on all synchronous-commit replicas
Upgrade all remote secondary replica instances running asynchronous-commit secondary replicas
Upgrade the all local replica secondary instances that are not currently running the primary replica
Manually fail over the availability group to a local synchronous-commit secondary replica
Upgrade or update the local replica instance that formerly hosted the primary replica
Configure automatic failover partners as desired


Is that a good idea to use one database for 50.000+ shops?

Please note: I'm answering from a SQL Server perspective, so I mention some concepts specific to SQL Server, but I believe all of these concepts have equivalents in other major RDBMS platforms, with similar benefits and limitations.I will also probably continue editing this answer as I think of other potential pros/cons.

Well, it really depends on schema, volume, etc. What exactly is a shop storing? How is it different from storing data about 50,000 cats or 50,000 products or 50,000 wingnuts?

There are several reasons (other than just the size aspect on its own) why you might not want to store data for 50,000 different customers in a single database, if indeed the data can be completely segregated by customer (not including lookup tables like zipcodes or application-specific tables, which could go into a single, central database):

if one customer outgrows the application, there is no easy way to extract just their data and move it to another instance, server, etc. to scale out, unless you plan ahead and partition on something like CustomerID and have 50,000 filegroups (you're limited to 15,000 partitions anyway, or 1,000 if you're on an older version of SQL Server, and having too many filegroups can be disastrous). Also note that partitioning requires Enterprise Edition.
if it turns out that all of your customers are simply too big for this instance, scaling out means getting new hardware and moving the entire database there (and potentially doing that again down the road).
deleting a customer can be equally painful, as you will have to delete some % of rows from very big tables, and that will not be cheap.
you will likely have wide distribution of customer data (one customer with a billion rows, another customer with 5,000). This can lead to things like parameter sniffing and detrimental performance involving cardinality and plan quality (since you will likely be re-using the same plans for the same queries against very different data sets).
all of your customers are subject to the exact same SLAs and HA/DR plans. You either have the entire database in full recovery mode with n-minute log backups, or you are in simple and rely on full+diff backups. If you have to revert because of a customer error, or need to recover the database to a point in time, that affects every single customer.
there is potential for errors in data retrieval - bugs in where clauses, for example, could lead to one customer seeing another customer's data, or all of the other customers' data.
there may be legal implications (some companies will have strict requirements in place that you do not place their data in the same database as any other company, and particularly that of their competitors).
if security of any one customer's data is important, then achieving that is much easier using database separation than separation within a table.

Some advantages to having each customer in a separate database (or at least having multiple databases, each for a group of customers):

in terms of size, it will take about the same size on disk.
scaling out is easier, since you can just move a database (or many) to a different server.
deleting a customer and all of its data roughly equates to DROP DATABASE.
you're using more memory for plans (or you have fewer plans in cache per customer), but at least those plans are relevant to the data in their respective databases and are less prone to statistics / parameter sniffing issues.
you can easily have different SLAs and DR plans, placing some databases in full and others in simple. Also reverting or restoring to a point in time only affects that customer.
you can easily place different databases (say, your high priority customers) on faster I/O. You could do this in a single database with filegroups, but that is much trickier to manage (at least IMHO).

Some drawbacks:

size aside, you probably won't want to have 50,000 databases on a single instance of SQL Server, so this will probably mean scaling out to multiple servers.
startup time goes up because there is some inherent overhead in starting up each database.
the app has to be a bit smarter - instead of just having CustomerID on the where clause, it has to dynamically connect to CustomerID's database. This isn't hard with a proper middle tier but it is a change.
yes, you have many copies of the same tables and procedures, but code and schema are identical across databases, just the data is different. So deploying code/schema changes is now just a loop instead of a single execution.
maintenance is a little bit different when you're managing 50,000 databases - again the overall size is roughly the same but the process has to change - you can't just defrag / reindex / back up all 50,000 databases at once. Having said that, at my previous job I managed instances with 500-1,000 identical databases, and the difference between managing 3 identical databases and 750 identical databases is simply the time it takes.


How to have a one-to-many relationship with a privileged child?

Another way (without Nulls and without cycles in the FOREIGN KEY relationships) is to have a third table to store the "favourite children". In most DBMS, you'll need an additional UNIQUE constraint on TableB.

@Aaron was faster to identify that the naming convention above is rather cumbersome and can lead to errors. It's usually better (and will keep you sane) if you don't have Id columns all over your tables and if the columns (that are joined) have same names in the many tables that appear. So, here's a renaming:

Parent
    ParentID        INT NOT NULL PRIMARY KEY

Child
    ChildID         INT NOT NULL PRIMARY KEY
    ParentID        INT NOT NULL FOREIGN KEY REFERENCES Parent (ParentID)
    UNIQUE (ParentID, ChildID)

FavoriteChild
    ParentID        INT NOT NULL PRIMARY KEY
    ChildID         INT NOT NULL
    FOREIGN KEY (ParentID, ChildID)
        REFERENCES Child (ParentID, ChildID)

In SQL-Server (that you are using), you also have the option of the IsFavorite bit column you mention. The unique favourite child per parent can be accomplished via a filtered Unique Index:

Parent
    ParentID        INT NOT NULL PRIMARY KEY

Child
    ChildID         INT NOT NULL PRIMARY KEY
    ParentID        INT NOT NULL FOREIGN KEY REFERENCES Parent (ParentID)
    IsFavorite      BIT NOT NULL

CREATE UNIQUE INDEX is_FavoriteChild
  ON Child (ParentID)
  WHERE IsFavorite = 1 ;

And the main reason that your option 1 is not recommended, at least not in SQL-Server, is that the pattern of circular paths in the foreign key references has some problems.

Read a quite old article: SQL By Design: The Circular Reference

When inserting or deleting rows from the two table, you'll run into the "chicken-and-egg" problem. Which table should I insert first - without violating any constraint?

In order to solve that, you have to define at least one column nullable. (OK, technically you don't have to, you can have all columns as NOT NULL but only in DBMS, like Postgres and Oracle, that have implemented deferrable constraints. See @Erwin's answer in a similar question: Complex foreign key constraint in SQLAlchemy on how this can be done in Postgres). Still, this setup feels like skating on thin ice.

Check also an almost identical question at SO (but for MySQL) In SQL, is it OK for two tables to refer to each other? where my answer is pretty much the same. MySQL has no partial indexes though, so the only viable options are the nullable FK and the extra table solution.


using pg_prewarm after an ETL load has finished - worth it?

Based on your description, prewarm is unlikely to be harmful.  With the information you provide, there is no way to know how helpful it will be.  It probably takes less time to implement than it does to think about whether it is worth implementing it. So just do it and be done with it.

  Does it make sense to call pg_prewarm on all the reference data tables after an ETL job has finished, so as to ensure that all of them are loaded into memory the next time an ETL load fires?

Well, who would be driving this data out of memory in the first place? If you are going to prewarm, it would make more sense to do it immediately before you start a load, not immediately after you finish one in the hopes it sticks around until the next one starts.


Find Multiple Finished Product code via 1 Raw Material Code

Does something like this help? It's a recursive CTE.

DECLARE @crap TABLE (Resource VARCHAR(6), PMName VARCHAR(6), PMDawn VARCHAR(50))
INSERT @crap ( Resource, PMName, PMDawn )
SELECT *
FROM (
VALUES('M1655' ,      'A02748',    'HONEY RST SALMON FROZEN'),
      ('A02748',      'A02749',    'HONEY RST SALMON DEFROSTED'),
      ('A02749',      'R3770' ,    'LOCHMUIR SALMON PASTA'),
      ('A02749',      'R3856' ,    'CAFE HONEY RST SALMON NICOISE'),
      ('A02749',      'R3857' ,    'BFY HOT SALMON & SUPERGREEN')
) x (Resource, PMName, PMDawn);

WITH r_cte AS (
        SELECT  c.Resource, c.PMName, 0 AS PMLevel, CONVERT(VARCHAR(900), c.Resource) AS FullPath
        FROM @crap AS c
        WHERE c.Resource = 'M1655'

        UNION ALL

        SELECT c2.Resource, c2.PMName, PMLevel + 1, CONVERT(VARCHAR(900), CONCAT(r.FullPath, '/', c2.Resource))
        FROM r_cte r
        JOIN @crap AS c2
        ON r.PMName = c2.Resource
)
SELECT DISTINCT c.PMName, c.PMDawn
FROM r_cte r
JOIN @crap AS c
ON r.Resource = c.Resource
WHERE c.PMName LIKE 'R%'
OPTION(MAXRECURSION 0);


What is wrong with my RESTORE procedure?

I had the same issue. I ended up partitioning a D: drive, then it worked. Funny thing is that after it restored, D: drive was empty. :o


Oracle error ORA-65052: statement involves operations with different container scope

I think this Q & A on the Tridion beta Stack Exchange site may help you:

Oracle Content Delivery CreateTridionSYSUser.sql errors

Basically you need to be in the pluggable database because of the multi-tenancy that is a new feature of 12c.

alter session set container=pdborcl;


Mysql: does full-text cover normal index?

No.

(Is that answer short enough and decisive enough?)

A regular index can be used for these, yet a FULLTEXT index cannot:

WHERE price = 12.34  -- numeric comparison
WHERE quantity > 100  -- not "equality"
WHERE country_code = 'US'  -- possibly to short for FULLTEXT
WHERE col LIKE '% foo %'  -- note explicit check for spaces
WHERE col LIKE '%=%'  -- punctuation

But for searching for a "word" inside a "text" column, FULLTEXT is excellent.  You have to live within its limitations.


How to execute stored procedure with output parameter in Oracle PL/SQL?

There are several mistakes.
1. When specifying parameters of procedure, you don't need to specify size. E.g. it should be CREATE OR REPLACE PROCEDURE insert_toys(toy_id OUT NUMBER,toy_name VARCHAR ), not  .... VARCHAR(30))
2. You created sequence CREATE SEQUENCE toy_seq, but trying to use sequence with different name toy_id := seq_toy.NEXTVAL;  (toy_seq vs seq_toy)
3. Parameter name is the same as field name (Toy_Name). Even though it's not a compilation error, qualifying variables is always much better compared to relying  on resolution rules :

INSERT INTO Toy (Toy_No,Toy_Name)
VALUES (insert_toys.toy_id,insert_toys.toy_name);

The procedure takes 2 parameters, and should be called like that.

set serveroutput on;
declare new_id NUMBER;
BEGIN
  insert_toys(new_id,'name2');
  dbms_output.put_line(new_id);  --print value of new id
END;


Postgresql requested starting point is ahead of the WAL flush position of this server

I ended up deleting the slave and redeploying a new one with the "new" basebackup.

Obviously this is not ideal, but it works. It's a last resort solution. It's literally setting up that part of the environment fresh.


How to get the difference in days between 2 timestamps in PostgreSQL

You could use the function age() to simplify your expression (returns interval). But it's much more efficient to use a sargable expression to begin with.

This operates with the exact time difference (current time is relevant):

SELECT *
FROM   accounts
WHERE  status = 'PENDING_PAYMENT'
AND    status_updated_at < now() - interval '10 days'

To operate with whole calendar days (up to but excluding midnight, local time zone):

...
AND    status_updated_at < CURRENT_DATE - 10

You can just subtract integer from date, the result is still a date which can be compared directly to the data type timestamp of your column.

You seem to be asking for the behavior of the second variant. The difference is more than 10 days.

How does the evaluation work?

In reply to your comment.

CURRENT_DATE is a special function returning a date (implemented internally with ('now'::cstring)::date in pg 9.4). There are many variants of the operator -. I find 43 in the system catalog pg_operator in my current test DB. The right one is picked by means of the operands' data types.

For date - integer, the integer amount in days is subtracted from the date - just because it's defined that way. It makes sense for me, the unit of a date is "days". Modern Postgres stores dates and timestamps as integer numbers internally. It's very cheap to transform one into the other and it's very cheap to add/subtract days to/from a date and it's also very cheap to compare them.

The result of the expression is a date, while status_updated_at is a timestamp column. I was assuming an implicit assignment cast at first, but that step is not even necessary, date and timestamp share a binary compatible format and can be compared directly. There is variant of the < operator registered for timestamp < date. Column values can be tested "as is", the expression is sargable, a btree index can be used.

SELECT oprname, oprleft::regtype, oprright::regtype
FROM   pg_operator
WHERE  oprname = '<'
AND    oprleft = 'timestamp'::regtype;

Related:

Generate series of dates - using date type as input

With timestamptz, the matter of time zones would be added to the equation, which is complex but typically burns down to very simple evaluation:

Ignoring timezones altogether in Rails and PostgreSQL

Volatility of now()

  It would be ideal if now() was not calculated for each record and
  instead be a single timestamp value calculated at the beginning.

Your wish has been granted. The now() family of functions (including CURRENT_DATE id defined STABLE, i.e. now() returns the same value within the same transaction.

now() default values are all showing same timestamp
How do IMMUTABLE, STABLE and VOLATILE keywords effect behavior of function?


You can use the following link to install sql 2014 offline files
MS SQL Server 2014


Return document that has $max value in specific field using mongodb

what I did was this:
exported to a new collection temp and then, grouped by total_winds, sorted by ID and took first elements. It returns not only the very first, but all those being equal.

  db.temp.aggregate([
  {$group: {
      _id: "$total_wins",
      my_winner: {$push: "$$ROOT"}
  }},
  {$sort:{_id:-1}},
  {$limit:1}  ]).pretty()


The process cannot access the file because it is being used by another process

just make sure to close the designer window before hitting F5.


PostgreSQL Division In Query Not Working

Integer division truncates fractional digits. Your expression returns a ratio between 0 and 1, which is always truncated to 0.

To get "percentage", first multiply by 100.
To also get fractional digits, cast to numeric (before you divide) - or multiply by 100.0. The presence of a fractional digit in the numeric literal coerces the result to numeric automatically.

Typically, you would round to two fractional digits or something. Use round() for that (only works for numeric).

Adding some other basic optimizations to the query you get:

SELECT total
      ,tier1, round((tier1 * 100.0) / total, 2) AS tier1_pct
      ,tier2, round((tier2 * 100.0) / total, 2) AS tier2_pct
      ,tier3, round((tier3 * 100.0) / total, 2) AS tier3_pct
FROM  (
   SELECT count(*) AS total
         ,count(fall_september_tier = 1 OR NULL) AS tier1
         ,count(fall_september_tier = 2 OR NULL) AS tier2
         ,count(fall_september_tier = 3 OR NULL) AS tier3
   FROM   national_assessments.aimsweb
   WHERE  general_outcome_measure = 'PSF'
   AND    fall_september_tier IS NOT NULL
   ) sub;

Explanation for the counting technique:

For absolute performance, is SUM faster or COUNT?


(Got an error reading communication packets) but no error on client

Try to increase your connection pool min size 5 instead of 0.

From DB side, verify the timeout variables  and connection pool variables like

wait_timeout & interactive_timeout >=  app connection pool timeouts.  (db timeouts should not be less than app timeouts).    And check to make sure the value of max_allowed_packet is high enough. Lastly increase enough net_read and net_write timeouts from DB side.


Upgrading to SQL Server 2012 from SQL Server 2008

Turning my comments to answer :

SSMS is just a tool to connect to SQL Server.

You did an inplace upgrade, but were connecting to SQL Server 2012 using SSMS 2008R2 and SSMS 2012.

Using T-SQL - will give you the correct version:

SELECT
   SERVERPROPERTY ('MachineName') AS PhysicalMachineName,
   SERVERPROPERTY ('ServerName') AS SQLServerName,
   SERVERPROPERTY ('Edition') AS ServerEdition,
   CAST(SUBSTRING(@@Version,charindex('SQL',@@version, 1),15) AS VARCHAR(255)) +  ' + '
       + CAST (SERVERPROPERTY ('productlevel')AS VARCHAR (50)) + ' + (Build'
       + CAST (SERVERPROPERTY ('ProductVersion') AS VARCHAR (50)) +')' AS ProductVersion,
    RIGHT(@@version, (Len(@@Version)-charindex('Windows',@@version, 1))+1) AS [O.S.],
 SERVERPROPERTY ('Collation') AS Collation

Best practice:

Before upgrade - run the above script to get the version and after upgrade, run it to make sure that you are on correct right version.
Always use the latest version of SSMS (regardless of SQL Server version).


Optimisation Newbie: How much of a 'sin' is redundancy?

Generally, I don't add redundant columns unless I really need too.

Running a COUNT over a set of data is quite efficient in any RDBMS.

Consider this is a read over indexed (hopefully) cached data to get the count will beat the the 2nd write in to maintain the denormlaised column. This write requires more resources/locking/longer transaction etc which impact reads more

If performance becomes an issue over time, then you can pre-calculate the COUNT more efficiently using an indexed (aka materialised) view


In general, the type of storage and current size of database isn't particularly relevant to your auto-grow settings.  Ensuring that instant file initialization is enabled is far more important.

If instant file initialization is not enabled, then everything (more or less) comes to a halt while it is initializing the new part of the file.  With SSDs that time will be shorter, but it will still be a significant hiccup.  So If you don't have it enabled, you would want to have growth size set fairly small, or increase the size during maintenance windows.

If instant file initialization is enabled, then the file growth will only take a few milliseconds regardless of whether the storage is SSD or platters.  How much you grow it at once just depends on whether anything else will be using the disk space.  If it is one database, one file, etc., and nothing else will be using it, you could just grow it once to use almost all of the disk and be done with it.  But if there are multiple databases/files, then you'll just have to take all of those factors into consideration.

As to file fragmentation, the performance impact is all about disk read times, and since random disk seeks are far faster on SSDs, it's not near as much of an issue as it is with platters.  As Aaron mentioned in comments above, reads should mostly be out of the buffer, so if there is adequate memory for SQL Server it won't be reading much from disk anyway.

In general, you want to set it up so that it doesn't have to grow very often so that when there is a performance issue, you won't even be wondering if it's due to file fragmentation, etc.  It doesn't matter what your storage is, optimize the config for whatever resources you have.  Without knowing all of the details of the system, we can only answer questions about concepts, not specifics of what would be best for your particular situation.  Additionally, StackExchange is not really the place for getting custom specs--that's a consulting engagement.


You are counting the toast tables twice.  Once under their owner, as pg_total_relation_size(reltoastrelid), and again under their own entry in pg_class.  You should use relkind, not reltype, to filter what you want.

Also, trying to micromanage your RDBMS to this extent rarely pays off.


Where has STATMAN gone in SQL Server 2014?

I still see StatMan operations captured if I use SQL Sentry Plan Explorer PRO against SQL Server 2014:

   click to enlarge

So, not exactly sure what sp_whoisactive is doing differently in this case (I've never used it to analyze statistics creation), but I can assure you that SQL Server 2014 still uses StatMan.


MySQL #1054 - Unknown column 'masterdata_hyung_event.eventId' in 'on clause' on deep join tables

After full days my noob brain tried to work hard, I've found my solution.
SELECT * FROM hyung, masterdata_hyung_description #this

JOIN masterdata_hyung_event ON masterdata_hyung_description.eventId = masterdata_hyung_event.eventId #and this

WHERE hyung.Taket = '1822171' AND masterdata_hyung_description.hyungDescriptionId = hyung.descriptionId

Not tested in multiple results tho (but it should), but hopefully it works too. Thanks


How to select rate for all selected modules and all selected years no matter they got rate for that year or not

That looks like a homework question that is investigating how well you understand outer joins and maybe the NVL function (rather than the equijoins you currently have).


SQL date and time query not importing all

This looks like a case where the date column isn't a date at all, but rather a string.

You aren't getting Nov 2 through Nov 9 because the sorting on the string and calculating if it's less than 11/1.... in alphanumeric order.

Ideally, dates will be stored as dates, and then your existing query will work. This will also ensure that dates are valid dates, and you don't end up with data for 32 February, or even random strings.

If you can't change the type of the column, you'll have to cast the database values to a date to do the comparison (which will hurt performance, but get you the results you expect).


SQL Server Change Data Capture order of records

Let me quote from the manual:

Track Data Changes (SQL Server)

  Change tracking is based on committed transactions. The order of the changes
  is based on transaction commit time. This allows for reliable results to
  be obtained when there are long-running and overlapping transactions.
  Custom solutions that use timestamp values must be specifically
  designed to handle these scenarios.

and cdc.fn_cdc_get_all_changes_ (Transact-SQL) says:

  Commit LSN associated with the change that preserves the commit order of the
  change. Changes committed in the same transaction share the same commit LSN
  value.

for one of the returned columns (_$start_lsn) in get_all_changes.


Study your queries.  It could be that you accidentally have a "cross join" because you left off the ON clause.  This could lead to a huge temp table that, in certain circumstances, could bloat the .ibd file.

What version of Percona?

How much RAM do you have?? innodb_buffer_pool_size =  650GB implies that you have at least 700GB!  Otherwise, you will swap to death.  (This won't explain the .ibd issue, but it is a serious one.)

These are also rather high unless you have a lot of RAM:

thread_cache_size = 256
table_open_cache = 10000
max_connections = 10000

Please provide SHOW CREATE TABLE for that one table, and SHOW TABLE STATUS for it.  BLOBs and TEXTs are of interest; other things may be of note.

A query would take a long time to bloat the table.  Glance at SHOW FULL PROCESSLIST; you might spot it.  And/or, turn on the slowlog, and check it later.

A query (added from comment)

select  table0_.id as id,
                table0_.created as created,
                table0_.modified as modified,
                table0_.chunk_index as chunk,
                table0_.content as content,
                table0_.other_table_id as export
    from  table table0_
    where  table0_.other_table_id=8305
    order by  table0_.chunk_index ASC

To improve the performance of that query, you need INDEX(other_table_id, chunk_index).


How to find out who deleted some data SQL Server

I've not tried fn_dblog on Express but if it is available the following will give you delete operations:

SELECT
    *
FROM
    fn_dblog(NULL, NULL)
WHERE
    Operation = 'LOP_DELETE_ROWS'

Take the transaction ID for transactions you're interested in and identify the SID that initiated the transaction with:

SELECT
    [Transaction SID]
FROM
    fn_dblog(NULL, NULL)
WHERE
    [Transaction ID] = @TranID
AND
    [Operation] = 'LOP_BEGIN_XACT'

Then identify the user from the SID:

SELECT
    *
FROM
    sysusers
WHERE
    [sid] = @SID

Edit: Bringing that all together to find deletes on a specified table:

DECLARE @TableName sysname
SET @TableName = 'dbo.Table_1'

SELECT
    u.[name] AS UserName
    , l.[Begin Time] AS TransactionStartTime
FROM
    fn_dblog(NULL, NULL) l
INNER JOIN
    (
    SELECT
        [Transaction ID]
    FROM
        fn_dblog(NULL, NULL)
    WHERE
        AllocUnitName LIKE @TableName + '%'
    AND
        Operation = 'LOP_DELETE_ROWS'
    ) deletes
ON  deletes.[Transaction ID] = l.[Transaction ID]
INNER JOIN
    sysusers u
ON  u.[sid] = l.[Transaction SID]


CHECK constraint for array column to verify length > 0

The crucial point you may not be aware of, quoting the manual:

  It should be noted that a check constraint is satisfied if the check
  expression evaluates to true or the null value.

Bold emphasis mine.

Using cardinality() fixes this case like a_horse explains.

Explicitly disallowing empty arrays would do the same, cheaper:

CHECK (words <> '{}')

Both solutions still allow that words itself is NULL. You might want to add a NOT NULL constraint.

Either way, be aware that NULL always passes CHECK constraints. The general solution to disallow NULL for expressions:

CHECK (<expression> IS TRUE)


Check that auditing is enabled:

SQL> SHOW PARAMETER AUDIT

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest                      string      /u01/app/oracle/admin/phil12c1/adump
audit_sys_operations                 boolean     FALSE
audit_syslog_level                   string
audit_trail                          string      DB
unified_audit_sga_queue_size         integer     1048576

If audit_trail is not set (NONE), turn auditing on (as SYSDBA):

ALTER SYSTEM SET audit_trail=db SCOPE=SPFILE;

... then restart the database.

I'll create a test table to use:

create table fgatest
(
  id number primary key,
  value number
);

insert into fgatest values ( 1, 2 );

insert into fgatest values ( 2, 200 );

commit;

Now, I'll create an example of an FGA auditing policy that uses the above table. This will audit any SQL that contains a row with a value greater that 100 in its resultset. The parameters speak for themselves.

BEGIN
  DBMS_FGA.add_policy(
    object_schema   => 'C##PHIL',
    object_name     => 'FGATEST',
    policy_name     => 'FGA_TEST_CHECK_VALUE_GT_100',
    audit_condition => 'VALUE > 100',
    audit_column    => 'VALUE');
END;
/

Test:

[oracle@ora12c1 ~]$ sqlplus c##phil/phil

SQL*Plus: Release 12.1.0.1.0 Production on Thu Jan 30 09:55:57 2014

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

Last Successful login time: Thu Jan 30 2014 09:37:34 +00:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL>
SQL> select * from fgatest where value>150;

        ID      VALUE
---------- ----------
         2        200
SQL>

SQL> conn / as sysdba
Connected.
SQL> select session_id,timestamp,db_user,sql_text
     from dba_fga_audit_trail;

SESSION_ID TIMESTAMP DB_USER    SQL_TEXT
---------- --------- ---------- -------------------------
    280018 30-JAN-14 C##PHIL    select * from fgatest where value>150

SQL>

Note I've done my test on 12c, but this test will work perfectly on 11.2 too.


Error PRVG-11322 while atempting to install ODAC 12c release 3

use the installer in commandline mode and use the option to ignore prerequisite checks


FTS is slow when searched word appears frequently in rows

Your bitmap has grown too large to fit in work_mem, and so it has "gone lossy" where it records just the blocks that contains matches (for some blocks), and then needs to recheck each row in those blocks.

                 Rows Removed by Index Recheck: 1329552
                 Heap Blocks: exact=12673 lossy=22516

Doing this is slow.  Especially so because you are using a functional index, and so it has to re-parse content for each of those rows as the parsed form is not stored in the table.  (That re-parsing is also why you get the NOTICEs).

Increase work_mem so that it can hold the entire bitmap.


Overestimated query resulting in poor performance - TVF with xml processing

I think the base cardinality estimate for XML TVF is 10,000 rows - I read it in a presentation by Michael Rys

To improve estimates, you can either add a schema to your XML or create an XML index.

Other than that I could only direct you to row goal hints and estimates trick I first heard from Adam Machanic here


Retrieving only the last row of a table, in the absence of a primary key

If you want the row with the highest offset value for each file, then:

;WITH x AS (SELECT [Column 1], [Column 2], [Column 3],
  rn = ROW_NUMBER() OVER (PARTITION BY [Column 2] ORDER BY [Column 3] DESC
  FROM dbo.source_table
)
SELECT [Column 1], [Column 2], [Column 3]
  FROM x
  WHERE rn = 1
  ORDER BY [Column 2];

If yu just want the row with the highest offset across all files, you don't want MAX(), but you can use TOP (1):

SELECT TOP (1) [Column 1], [Column 2], [Column 3]
  FROM dbo.source_table
  ORDER BY [Column 3] DESC;


DTC Timeout when trying to update oracle table through link that contains a text index

Turns out (after an Oracle SR) this was an already known problem we needed to patch the Oracle Server -> ORACLE 11G 11.2.0.2 PATCH 27 BUG FOR WINDOWS helps.