Why is DBMS_CRYPTO inaccessible for most users by default?

A fundamental principle of Oracle security is everything is prohibited unless specifically granted.  You see this even in creating a new user.  Unless you specifically grant them CREATE SESSION (or a role that includes it) they can't even log on, even with proper credentials.  There is no reason grant execute on that package by default.  And keep in mind that there is really nothing "special" about SYSTEM.  It is just a default DBA user.  Also consider that DBMS_CRYPTO is of more use (in general) to apps developers, so why should it break the Principle of Least Privilege by making effectively available to everyone?


SELECT highest quantity in related table, exclude if there is a tie

It's a bit unclear what you want, but you can use rank() to collect duplicates, and then another window function that counts those, and filter them out if there is more than one:

SELECT qty.Id
     , qty.Product_Name
     , qty.Product_Type
FROM (
    SELECT qty.Id
     , qty.Product_Name
     , qty.Product_Type
     , COUNT(Rnk) OVER () as cnt
    FROM (
        SELECT td.Id, prd.Product_Name, prd.Product_Type
        ,  RANK() OVER (PARTITION BY prd.Receipt_Number
                        ORDER BY prd.Quantity) AS Rnk
        FROM Transactions td
        JOIN Products prd
            ON prd.Receipt_Number = td.Receipt_Number
        WHERE prd.Quantity > 1
    ) qty
    WHERE qty.Rnk = 1
) WHERE cnt = 1;

If you want the rows, but with null instead of values you can use a case expression:

SELECT CASE WHEN cnt = 1 THEN qty.Id END
     , CASE WHEN ... qty.Product_Name ...
     , ...
FROM (
...
);


Using the GRANT Shema permissions on my made-up ReportUsers domain group, I can assign SELECT permissions as follows:

GRANT SELECT ON SCHEMA :: dbo TO [domain\ReportUsers];

For the Reporting schema, that can be tackled in a few ways. Do you want to give them full control over the schema? Is it just for creating views?


Check for table lock in conditional statement

The simplest solution to this seems like it would be to use a named lock.

http://dev.mysql.com/doc/refman/5.5/en/miscellaneous-functions.html#function_get-lock

IF GET_LOCK('my_arbitrary_lock_name',0) THEN
  ...
  DO RELEASE_LOCK('my_arbitrary_lock_name');
ELSE
  ...
END IF;

This pool of locks is independent of table locks, metadata locks, row locks, or any other kind of "actual" database object locks, and obtaining, releasing, or checking these locks is also independent of transactions.

The only thing that's actually getting "locked" here is the name you passed as the first argument, which is an arbitrary string.  All this does is ensure that no more than one thread can hold a lock of the same name at the same time.  The others have to wait for up to the number of seconds specified in the 2nd argument to GET_LOCK() otherwise they don't get the lock (and GET_LOCK() returns false).  After the timeout (which is 0, in the example, to wait no time at all) if the lock isn't free, the function returns false; if the lock is free then the lock is obtained by that connection and the function returns true.  Other connections can discover which connection holds a lock with the IS_USED_LOCK() function, but they can't interfere (other than kill the connection, given sufficient privileges).

If you "forget" to release a lock, it's automatically released if you disconnect.

Asking for a different lock will also release the current lock you hold in MySQL, and asking for a lock you hold will release it and then try to re-obtain it, potentially handing it over to another thread.  These behaviors vary between MySQL and some versions of MariaDB, it's best to be explicit when using them rather than relying on such side-effects for intentional behavior.


How to skip the current 'Relay_Master_Log_File' in AWS MYSQL RDS?

Replication Lag is usually measured by finding the difference between the following

TIMESTAMP#1 at (Relay_Master_Log_File,Exec_Master_Log_Pos)
TIMESTAMP#2 at (Master_Log_File,Read_Master_Log_Pos)

From what I am seeing in the SHOW SLAVE STATUS\G

you are 98230 seconds (27 hours 17 minutes 10 seconds) behind
you have 329 binlogs (278337 - 278008) to process after mysql-bin-changelog.278337
The sum of all your binlogs is 354792434 (338M)

If the Seconds_Behind_Master is steadily increasing by one every second, you will have to wait it out and let replication catch up. This is a strange indication that you are running some terrible query that is making the number of binlogs waiting to be processed increase. You could see that query by running show processlist; and look for the SQL thread (where the user is system user) with the same number for Time as the Seconds_Behind_Master.

If you cannot wait for the catch up of replication, you just need to spin up another read-only slave and drop this very latent slave.


How to create scheduler for manually refresh Materialized View if the MV get need_compile stallness status

I'm successfully able to create a scheduler for my MV to refresh every minutes if the status stallness is not FRESH. Here's my solutions :

I created a schedules to be used in my scheduler:

BEGIN
    sys.dbms_scheduler.create_schedule(
    repeat_interval => 'FREQ=MINUTELY',
    start_date => to_timestamp_tz('2015-06-09 01:41:55 Asia/Jakarta', 'YYYY-MM-DD HH24:MI:SS TZR'),
    comments => 'Interval refresh 1 menit',
    schedule_name => '"SOME_SCHEMA"."REFRESH_MV"');
END;

After that I create DBMS_SCHEDULER to create a job like this :

BEGIN
sys.dbms_scheduler.create_job(
job_name => '"SOME_SCHEMA"."REFRESH_MV"',
job_type => 'PLSQL_BLOCK',
job_action => 'DECLARE
    mv_name USER_MVIEWS.MVIEW_NAME%type;
    mv_status USER_MVIEWS.STALENESS%type;
    mv_last USER_MVIEWS.LAST_REFRESH_TYPE%type;
    mv_state USER_MVIEWS.COMPILE_STATE%type;

    CURSOR check_mv
    IS SELECT MVIEW_NAME, STALENESS, LAST_REFRESH_TYPE, COMPILE_STATE FROM USER_MVIEWS WHERE MVIEW_NAME = ''MV_TEST_BRO'' ORDER BY MVIEW_NAME;

    BEGIN
        OPEN check_mv;

        LOOP
            FETCH check_mv INTO mv_name, mv_status, mv_last, mv_state;
            IF mv_status != ''FRESH'' THEN
                DBMS_REFRESH.REFRESH(name => ''SOME_SCHEMA.MINUTE_REFRESH'');
            ELSE
                EXIT;
            END IF;
            EXIT WHEN check_mv%NOTFOUND;

        END LOOP;

    CLOSE check_mv;
END;',
schedule_name => '"SOME_SCHEMA"."REFRESH_MV"',
job_class => '"DEFAULT_JOB_CLASS"',
comments => 'Refresh MV every 1 Minutes',
auto_drop => FALSE,
enabled => FALSE);
sys.dbms_scheduler.set_attribute( name => '"SOME_SCHEMA"."REFRESH_MV"', attribute => 'raise_events', value => dbms_scheduler.job_failed);
sys.dbms_scheduler.set_attribute( name => '"SOME_SCHEMA"."REFRESH_MV"', attribute => 'logging_level', value => DBMS_SCHEDULER.LOGGING_FAILED_RUNS);
sys.dbms_scheduler.set_attribute( name => '"SOME_SCHEMA"."REFRESH_MV"', attribute => 'restartable', value => TRUE);
sys.dbms_scheduler.enable( '"SOME_SCHEMA"."REFRESH_MV"' );
END;

In the job action, I checked stallness status for specified MV like MV_TEST_BRO but you can check the status for every MV you had. And I activated some attribut to raise event if the job failed, logging level, and restartable.

This job was running for a week now and no problem at all. My MV always FRESH every time. May be this post will help another who face the same problem like me. :D


ODBC connection for Active Directory user who doesn't log in to Windows

Although you cannot login to SQL Server unless you use either a SQL Server Login or a Windows Account with permissions to connect to the SQL Server, you could create a low-permission user that can EXEC certain stored procedures.  Those stored procedures could then contain code that switches into the context of another, more highly privileged account.

EXECUTE AS Login = 'DOMAIN\User';
SELECT * FROM sys.databases;
REVERT

Read more about EXECUTE AS on MSDN.


Slow running query with inner SELECT in where clause

Try this:

USE database2;
go
CREATE INDEX ix4_23 ON TABLE4(Col23) INCLUDE (Col22, Col25);
go
USE database1;
go
CREATE INDEX ix3_21 ON TABLE3(Col21) INCLUDE (Col20);

And in the sub-queries put "TOP (99999999)" in there. You want to persuade the sub-queries to pull all the rows from the other databases separately. TOP with a number should persuade it... :)


Replicating a View to a Table. Issues with schema binding, view indexing

When you right click on Tasks and use the Import Data/Export Data task, you're building an SSIS package in the background. In the penultimate screen, you're usually greeted with

Instead of Run immediately, check "Save SSIS Package". I generally save to the File System with the Package Protection Level of Do not save sensitive data. Your preference may be different but the important thing is to note where you save this.

You may now use your scheduling tool of choice to invoke the execution of this SSIS package and keep your production environment synchronized with your values in your development environment.


MySQL GTID replication: is it possible to make DDL changes to a slave and then catch it up to a master?

You have described a common way to make DDL changes.  Yes, it should work.  But...  please describe the datatype change further.

If you are ALTERing a COLUMN that is current INT SIGNED to be a SMALLINT SIGNED, and if none of the values are bigger than 32767, there should be no problem.  If you are changing from SIGNED to UNSIGNED, and none of the numbers are negative, then no problem.  Etc.  In general, if the old value will fit in the new datatype, then it is OK.  If it won't fit, then something will be lost.  1 million into a SMALLINT UNSIGNED will probably become 65535.

As for the application, how does mysql know it is getting an 11-digit id?  It sees a number of some number of digits and shoves it into the target size.

Do SHOW WARNINGS; after the DDL.


Postgres: How to insert row with autoincrement id

Apparently you inserted rows into that table without using the sequence and that's why they are out of sync.

You need to set the correct value for the sequence using setval()

select setval('context_context_id_seq', (select max(context_id) from context));

Then the next call to nextval() should return the correct value.

If the column is indeed defined as serial (there is no "auto increment" in Postgres) then you should let Postgres do it's job and never mention it during insers:

insert into context (some_column, some_other_column)
values (42, 'foobar');

will make sure the default value for the context_id column is applied. Alternatively you could use:

insert into context (context_id, some_column, some_other_column)
values (default, 42, 'foobar');


As far as you don't give us more information I'll throw a bet.

Using only uid to join both tables you get 20 records (5 x 4). I suppose you should add symbol.

SELECT     a.symbol,
           a.order_type,
           a.price_open,
           a.time_open,
           a.size,
           b.bid,
           b.point
FROM       trade_log a
INNER JOIN currency_rates b
ON         a.uid = b.uid
AND        left(a.symbol, 6) = b.symbol
WHERE      a.uid = '23'
AND        a.status = 'Open'
AND        a.order_type IN ('0', '1')
ORDER BY   a.time_open ASC;


Optimizing Queries HAVING COUNT and INSERT...ON DUPLICATE KEY UPDATE

I'm guessing a bit on what your query is supposed to do. I assume you are looking for all users that share 5 or more items with a certain user:

SELECT a.userid
FROM ratings AS a
WHERE EXISTS (
      SELECT 1
      FROM ratings AS b
      WHERE b.userid = :user
        AND a.userid <> b.userid
        AND a.showid = b.showid
)
GROUP BY a.userid
HAVING COUNT(1) > 4

Is that about right?

For the second part of the question, as I understand it the tables are defined as:

create table shows
(   showid  int not null primary key
,   showname    varchar(10) not null
,   showimage   varchar(10)
,   x int default 0 not null );

create table ratings
(   userid  varchar(200) not null
,   showid  int not null
        references shows(showid)
,   rating  int not null
,       primary key (showid, userid) );

If that's the case I see no particular reason why the insert in ratings is slow, btw what is slow?

Some ideas for trying to pinpoint the problem, make a copy of the ratings table, say ratings2, drop the foreign key and see how that affects the insert performance


Where/When is the use of RBAR buffering related to ASYNC_NETWORK_IO waits negotiated between the Client/SQL Server

With .NET SqlClient (and every other client library) there is no setting that forces the client to fetch and cache the entire resultset. And SSRS will read the query results as it renders reports, and whether it reads the entire resultset at once will depend on the report design and the rendering destination.

You may be able to get SSRS to read the results more quickly if you use Shared Dataset Caching.

But it's not obvious that there's a problem that requires fixing.  You have a report that consumes 800K rows.  Forcing it to fetch all that data at the beginning is going to be expensive on the report server, and may not make anything better on your SQL Server.


SQL Server stored procedure keeps restoring to previous one

A common problem when developers involves the schema that SQL Server assumes when creating or altering an object. This is especially likely in the following circumstances:

The developer has recently been removed from the db_owner role The
developer is working on a server that they normally don't.
They are likely to see the problem on a production server (where their
permissions are tightly controlled) rather than a development server (where they might be a member of db_owner or even sysadmin).

This problem is often described by developers as "SQL Server is using the old version of my proc". What really happens is that the developer is modifying a copy of the procedure and executing a different copy of the procedure.

The quick way out of this is to ensure that you are providing a schema for the stored procedure with your drop, create, alter and execute statements. In SQL Server, the vast majority of the time, that schema is "dbo". In other words, prefer this:

alter procedure dbo.foo
as
select 1
go
exec dbo.foo

and avoid this:

alter procedure foo
as
select 1
go
exec foo


Create a trigger to fill a field according to acondition in sql

The following solution assumes rows will be added to the inspections table first (with a default value of NO in the Shop column. It is also assumed that only one attachment can exists for a given inspectionid
Two triggers have been defined on the attachments table.
The insert trigger on the attachments table will update the inspections table with YES in the Shop column to reflect the added attachment.
The delete trigger on the attachments table will update the inspections table with NO in the Shop column to reflect the deleted attachment.
--demo set up
DROP TABLE IF EXISTS Inspections;
DROP TABLE IF EXISTS Attachments;
go
    CREATE TABLE Inspections (
    InspectionId INT
    ,Shop CHAR(5)
    );
GO

CREATE TABLE Attachments (
    InspectionId INT
    ,Attachment CHAR(5)
    );
GO

--create insert trigger
CREATE TRIGGER Attachments_Insert ON Attachments
AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;

    UPDATE i    --attachment found for inspection
    SET i.Shop = 'YES'
    FROM Inspections i
    JOIN inserted ins ON ins.InspectionId = i.InspectionId
END;
GO
--create delete trigger
CREATE TRIGGER Attachments_Delete ON Attachments
AFTER DELETE
AS
BEGIN
    SET NOCOUNT ON;

    UPDATE i    --attachment deleted for inspection
    SET i.Shop = 'NO'
    FROM Inspections i
    JOIN deleted del ON del.InspectionId = i.InspectionId

END;
GO

--test trigger
--insert test rows into inspections table with default of NO in Shop column
INSERT INTO Inspections (InspectionId,Shop)
VALUES (1,'NO');

INSERT INTO Inspections (InspectionId,Shop)
VALUES (2,'NO');

--insert row into attachement
INSERT INTO Attachments (
    InspectionId
    ,Attachment
    )
VALUES (
    1
    ,'xyz'
    );

--verify results - one inspection row should have YES for Shop column
SELECT *
FROM Inspections;

--testing delete of attachment - should update existing inspection to NO
delete from Attachments where InspectionId=1

--verify results
SELECT *
FROM Inspections;


Cannot log in using SA with SQL Authentication

Did you look anywhere? Right-click the server and choose Properties, then select the Security screen.

Then, log in using your Windows auth admin account, and set the password for the sa SQL auth account.

ALTER LOGIN sa WITH PASSWORD = 'strong password here';


Copy PostgreSQL data from one PC to another

You could dump the database using pg_dump and then restore it on the new server using psql. Here's a couple of commands from the above link:

Create the backup:

pg_dump mydb > db.sql

Copy db.sql to the new server (specific command depends on OS)

Go to the new server

createdb mydb -E UTF8 (you don't have to specify UTF8 encoding, but I always do)

Then:

psql -d mydb -f db.sql

As I was answering, JohnP just answered with a fine answer but assumes that pg_hba.conf has been edited to allow remote connections and postgres.conf has been edited to listen on the network.


creating an inventory management system app

and the product sold as a negative value

I personally wouldn't do this - it does not reflect reality. You can't have -25 widgets. You can have 10 in an inventory table and 15 in an orders (active) and/or sold (completed orders) table. You can have negative numbers - your bank account can have -25€ - this makes sense.

Take a look at http://www.databaseanswers.org/data_models/ for example inventory designs. -  Vérace

Your scheme has joining rings, it is excessive, and data may be inconsistent. It is possible to have a joined record pair with purshases.purshaseid = inventory.purshaseid and purshases.productid <> inventory.productid.

I think purshases.productid is excess and must be removed. Its value must be selected from joined record in inventory table. And the same with inventory-product-orders ring. - Akina


Strategy for backup for databases with little use (almost archived databases)

This (the full backup) generates a large backup set that, for various reasons I am finding it hard to deal with.

As you said you are not using compression I believe you can get good relief when you use backup compression. Just forget about the CPU utilization I have used backup compression many a times and the CPU utilization is just 5-10 % more in extreme cases. I suggest you to use it but before that read The Documentation

  I am looking for a back strategy that would not result in unnecessary backup size.

The backup strategy is to make sure you have valid backup when needed and using that you can loose as less data as possible, there is hardly a strategy for making backup size less unless you use data compression or backup compression.

  Is there a way to take advantage of the fact that inactives are simple recovery to threat them differently?

Backup size as such is not influenced by much by recovery model it determines the amount of data loss you can have in case disaster strikes.

Moral: Use backup compression it might solve all your queries.


Security or performance risks using SQL CLR

The question, as Remus pointed out, is too generic to get an answer as the answer depends on the context of what functionality is to be used and how will it be used.

Regarding "Security":

If you are asking about anything that can be done in an assembly marked with PERMISSION_SET = SAFE, then there aren't any issues that I have ever been able to find. And SQLCLR is "safer" than using xp_cmdshell or the wonderful (that was sarcasm) sp_OA* procs (or even Extended Stored Procedures, but hopefully nobody is creating any of those anymore).

If you would like a walk-through of what "SAFE" means in practical terms, please see this article: Stairway to SQLCLR Level 3: Security (General and SAFE Assemblies) (free registration required).

If you are asking about anything that can be done in an assembly marked with PERMISSION_SET = EXTERNAL_ACCESS, then there are certainly risks, again, depending on what functionality is being used. If you write a routine to read directories and file names (i.e. read-only), then it is just a matter of what should be seen and not seen. If you are writing code that allows for deleting a file, the risk increases. But what can be done with those external resources is controlled by:

whether or not you are using Impersonation:

no Impersonation means accessing external resources is done via the "Log on as" account of the SQL Server service. Whatever that account has access to, your SQLCLR function will be able to do.
using Impersonation means that the Login in SQL Server that is running the function, if that Login corresponds to a Windows Login, can do whatever that particular Windows Login is allowed to do. If the Login in SQL Server is a SQL Server Login, then attempting to use Impersonation will get an error.

What permissions are set up on the external resource. For filesystem access, this is controlled through ACLs on NTFS drives.

If you are asking about anything that can be done in an assembly marked with PERMISSION_SET = UNSAFE, that is fairly open-ended. A lot of functionality is deemed only usable in UNSAFE assemblies because they are issues of stability and/or consistent behavior more than security or performance. For example, in an UNSAFE assembly it is possible to have a writable static variable. This is typically not a good thing to do since the SQLCLR classes are shared across all sessions. If your intention is to share data in memory across all sessions and have planned for race conditions (and done plenty of testing), then you should be fine as you are expecting this behavior. But if you simply wanted a writable static variable to cache a value for a particular session to not have to look it up again or calculate it again, and were not aware that other sessions are reading that value and possibly over-writing it, well, that would be an issue.

But if you are worried about someone writing to the Registry, but yet don't have any code that actually writes to the Registry, then you probably don't need to worry about this ;-).

If you would like a walk-through of how EXTERNAL_ACCESS and UNSAFE work in practical terms, and the difference between setting TRUSTWORTHY ON (not preferred) vs using an Asymmetric Key- or Certificate- based Login, please see this article: Stairway to SQLCLR Level 4: Security (EXTERNAL and UNSAFE Assemblies) (free registration required).

Regarding "Performance":

This is all a matter of what you are trying to do and how you go about doing it. There are some things that are much faster in SQLCLR, and some things that are slower. But just like with T-SQL, it is possible to take a somewhat simple and/or efficient task and make it complicated and/or inefficient by doing things incorrectly. But using SQL CLR is not inherently, by its very nature, slower.


Replication error from Mariadb 10.1 to Mysql 5.1/5.0/5/5 when master's logging format is set to row based

You have encountered one of the reasons for saying that replication must not go from a newer Master to an older Slave.

Would you care to explain the ultimate goal; maybe we can discuss a workaround.


The CREATE TABLE documentation says much the same as what your error message says:

  There can be only one AUTO_INCREMENT column per table, it must be
  indexed, and it cannot have a DEFAULT value.

You need to index the JOB_ID column.

ALTER TABLE JOB_ONPREM
ADD INDEX `dba269102` (`JOB_ID`)

Try that and see if afterwards you can alter the primary key. You could also make it a unique index instead, to help avoid duplicates.

ALTER TABLE JOB_ONPREM
ADD UNIQUE INDEX `dba269102` (`JOB_ID`)


Hard to suggest a more specific fix or suggest a cause given that we have no background on how your system got into this state, what you did prior to trying to create the first diagram, if you have successfully created diagrams in the past, or even what version of SQL Server you're using. It sounds like your sysdiagrams table somehow lost the IDENTITY property on the diagram_id column. Do this:

DROP TABLE dbo.sysdiagrams;
GO
CREATE TABLE [dbo].[sysdiagrams]
(
    [name] [sysname] NOT NULL,
    [principal_id] [int] NOT NULL,
    [diagram_id] [int] IDENTITY(1,1) PRIMARY KEY,
    [version] [int] NULL,
    [definition] [varbinary](max) NULL,
    CONSTRAINT [UK_principal_name] UNIQUE ([principal_id],[name])
);

GO
EXEC sys.sp_addextendedproperty
  @name=N'microsoft_database_tools_support',
  @value=1 ,
  @level0type=N'SCHEMA',
  @level0name=N'dbo',
  @level1type=N'TABLE',
  @level1name=N'sysdiagrams';
GO

This will of course lose any diagrams you were previously able to save.

(After performing a search it saddened me to see that many people suggest as a fix to make the default for this column 0. This is great after you create one diagram but falls apart for multiple  reasons once you try to create a second one.)


OBSERVATION #1
It is not recognized because of a typo
You have
  if [ "${database}" == "information_schema" ] || [ "${database}" == "performance_schema" ]; then
        additional_mysqldump_params1="--single-transaction"
  else
        additional_mysqldump_params=""
  fi

It should be
  if [ "${database}" == "information_schema" ] || [ "${database}" == "performance_schema" ]; then
        additional_mysqldump_params="--single-transaction"
  else
        additional_mysqldump_params=""
  fi

Please change the additional_mysqldump_params1 to additional_mysqldump_params
OBSERVATION #2
You have
chmod 600 "${backup_dir}/${database}.gz" ${additional_mysqldump_params1}

It should read
chmod 600 "${backup_dir}/${database}.gz"

Please remove additional_mysqldump_params1 from that line of code
OBSERVATION #3
There is no need to mysqldump information_schema or performance_schema databases

The INFORMATION_SCHEMA database is made of up in-memory temporary tables. They are dynamically generated and populated on mysqld startyo.
The PERFORMANCE_SCHEMA database is for the instrumentation of measuring performance. Each individual MySQL instance is responsible for populating it with instrumentation data when it is in use.


Use InnoDB because it supports Fast Index Creation.*

If you have plenty of RAM, configure a tmpfs partition and point your MySQL tmpdir to that location.

* There are many other reasons besides that to stop using MyISAM. MyISAM is known for its table-level locking, tendency to corrupt data, and lack of support for ACID and referential integrity. It's also slower than InnoDB under most workloads.


Likely the majority of the performance issue you're seeing is just the normal constraints of the data limitations of SQLite. Your query is of  sound mind, and I don't believe there's much you can do to optimize it other than re-write the WHERE predicate to something more efficiently relational with an INNER JOIN like this:
WITH file_id_counts AS (
    SELECT hashband, COUNT(DISTINCT(file_id)) as count
    FROM hashbands
    GROUP BY hashband
    HAVING COUNT > 1
  ) SELECT hashband, file_id, window_id
    FROM hashbands
    INNER JOIN file_id_counts
        ON hashbands.hashband = file_id_counts.hashband
    ORDER BY hashband

This is logically equivalent and the reasoning it could be faster is because the IN clause is syntactical sugar for a bunch of OR clauses which can be less efficient than a direct single equality operator like the INNER JOIN is doing above.
Additionally, ensuring your hashbands table has an index on at least (hashband) or possibly on (hashband, file_id) should help (if one doesn't exist already).
Finally, if possible, removing your ORDER BY clause, and instead doing the sorting in your consuming application would probably help a small amount as well. While this mostly just moves the onus of the sort to a different part of the call stack, generally sorting in the database can add a little extra complexity that sometimes can be resolved and slightly more efficient to do in the consuming application. Plus, sorting is really presentation logic in my opinion (at least when not used for a functional purpose of the query).
Where does this SQLite database live?...a mobile application, or on its own server?


LogShipping frequency for backup/copy/restore

This doesn't really solve the above problem, but I ended up using Transaction Replication and I didn't need to worry about the above complicated timing issues.


How can I bring a database offline safely?

Recently I took the database offline with the option Drop all active connections checked.

When you take a database OFFLINE, SQL Server is no longer protecting the database files from modification by other programs.  SQL Server tries to set file ACLs to prevent other users/programs from modifying the files, but having the database ONLINE provides an important additional protection.
Whenever possible perform actions in a way that doesn't take databases OFFLINE or stop the SQL Server service.  EG take the database to SINGLE USER mode instead of OFFLINE, and use Backup/Restore instead of moving database and databse files.
And if you do take the database OFFLINE, ensure that you have a backup first.


No, check constraints are supported by MariaDB. Domains are user defined datatypes. The domain mechanism allows you to put constraints on the datatype instead of having to put constraints on every single column where the specific datatype is being used.


Are there any third party tools that can read and/or analyze SQL Profiler trace (.trc) files

I would probably import the trace to a table, for example:

USE MyDB
GO
SELECT * INTO MyTraceTable FROM ::fn_trace_gettable('C:\Path\To\My\Trace\MyTrace.trc',
DEFAULT)

If you don't have permissions to create a table, consider using a temporary table or installing SQL Server Express locally and importing the trace there.

Kin's answer (entered concurrently with mine) might be a better option, but I haven't tried ClearTrace yet.


A few things that have to be corrected in your configuration function:

The parameter serviceConfig has to be the type [object[]] in order to accept an hashtable. Using [string[]] PowerShell does not properly handle iterating over that object, since it is a literal object and not just an array of strings.
When you are using a resource in DSC it has to be unique name. Your declaration of the ServiceSet resource is simply passing in $service; which in PowerShell terms is simply passing [System.Collections.Hashtable] each time and is therefore not unique.

Corrected version of the configuration that test and runs successfully on my system:

Configuration SQLConfig
{
    param(
        [Parameter(Mandatory =$true)][object[]]$serviceConfig,
        [Parameter(Mandatory =$true)][string]$DataDrive,
        [Parameter(Mandatory =$true)][string]$LogDrive

    )
    Import-DscResource -ModuleName SqlServerDsc
    Import-DscResource -ModuleName PSDesiredStateConfiguration

    Node localhost
    {
        WindowsFeature Net35 {
            Name   = 'NET-Framework-Core'
            Ensure = 'Present'
        }
        WindowsFeature Net45 {
            Name   = 'NET-Framework-45-Core'
            Ensure = 'Present'
        }
        WindowsFeature Cluster {
            Name   = 'RSAT-Clustering'
            Ensure = 'Present'
        }

        File datadrive {
            Type            = 'Directory'
            DestinationPath = $DataDrive
            Ensure          ='Present'

        }
        File logdrive {
            Type            = 'Directory'
            DestinationPath = $LogDrive
            Ensure          ='Present'
        }
        SqlDatabaseDefaultLocation dataPath {
            InstanceName = 'MSSQLSERVER'
            Path         = $DataDrive
            ServerName   = 'localhost'
            Type         = 'Data'
            DependsOn    = '[File]datadrive'
        }
        SqlDatabaseDefaultLocation logPath {
            InstanceName = 'MSSQLSERVER'
            Path         = $LogDrive
            ServerName   = 'localhost'
            Type         = 'Log'
            DependsOn    = '[File]logdrive'
        }
        foreach ($service in $serviceConfig) {
            ServiceSet $service.ServiceName {
                Name        = $service.ServiceName
                State       = $service.State
                StartupType = $service.StartupType
                Ensure      = $service.Ensure
            }
        }

    }
}

$serviceConfig= @(
    @{ServiceName='MSSQLSERVER';State='Running';StartupType='Automatic';Ensure='Present'},
    @{ServiceName='SQLSERVERAGENT';State='Running';StartupType='Automatic';Ensure='Present'},
    @{ServiceName='SQLBrowser';State='Stopped';StartupType='Disabled';Ensure='Present'}
)
SQLConfig -serviceConfig $serviceConfig -DataDrive "C:\temp" -LogDrive "C:\temp" -OutputPath "C:\temp" -Verbose


How to just keep latest records in SQL server?

You can use row_number() and a CTE to delete from the source table.

;with cte as(
   select
        *,
       RN = row_number() over (partition by [Name], [Status] order by [ChangeDate] desc, ID desc)
   from EMP_Detail)

delete from cte
where RN != 1;


Get count estimates from pg_class.reltuples for given conditions

Not out of the box. But you can achieve it with a ...

Partial index

CREATE INDEX tbl_name_hello_idx ON tbl(tbl_id) WHERE name LIKE 'hello%';

SELECT reltuples
FROM   pg_class
WHERE  oid = 'tbl_name_hello_idx'::regclass;  -- or schema-qualify table name

The actual index column (tbl_id in the example) is irrelevant (unless you have additional use for the index). Best pick a small column that is never changed: a serial PK column would be a perfect candidate. Or you could use a constant (which defeats additional purposes of that index):

CREATE INDEX tbl_name_hello_idx ON tbl((1)) WHERE name LIKE 'hello%';

Every index has its own entry in pg_class and its own reltuples count. The documentation:

  reltuples ...
  Number of rows in the table. This is only an
  estimate used by the planner. It is updated by VACUUM, ANALYZE, and a
  few DDL commands such as CREATE INDEX.

Hence, a partial index can be (ab-)used to get count estimates for any set of predicates, updated by autovacuum automatically. Or maybe you already have the index because you need it anyway?

This might actually be a very clever idea. But you have to weigh cost and benefits: the partial index is small for a rare condition, but the cost gets bigger for a common condition. And while keeping the statistics up to date is comparatively cheap, it's not free of cost.

Related:

Index that is not used, yet influences query
Fast way to discover the row count of a table

TABLESAMPLE SYSTEM (n) in Postgres 9.5+

You will love the new feature in the upcoming Postgres 9.5, which only looks at a random sample of n % of blocks in the table to get a quick estimate. Example for 1 %:

SELECT 100 * count(*) AS estimate
FROM   tbl TABLESAMPLE SYSTEM (1)
WHERE  name LIKE 'hello%';

Details in the answer already linked above:

Fast way to discover the row count of a table

Alternative for given example

For the given example name LIKE 'hello%' you could get very fast exact results using the right index anyway:

CREATE INDEX tbl_name_text_pattern_idx ON tbl(name text_pattern_ops);

Details:

How can one optimize a query involving a sort and a LIKE?
Pattern matching with LIKE, SIMILAR TO or regular expressions in PostgreSQL


Unable to start mongod daemon after upgrading OS from Ubuntu 16 to 18

Support for Ubuntu 18.04 was added in Mongo 4.0.6, see https://jira.mongodb.org/browse/SERVER-37778

You have version 4.0.4


SQL Server Mirroring and automatic failover - why don't all DBs failover to the mirror?

The issue is that there is a separate ping for each database, not one for all. So some are successful and some fail. If you need all of the databases to fail over, I outlined a process (using service broker and event notifications) to detect a failover and make sure all mirrored databases fail over in my book Pro SQL Server 2008 Mirroring.

A lot of this information is also located on my blog here Database Mirroring Automation and here: How do I automatically respond to mirrored database failovers?


From the comments added to the original question, I gather that the central problem with the legacy design centers around types and subtypes (often called classes and subclasses) of claims.  This is a classic problem in relational design, Often, new database designers will have had some formal learning concerning many-to-many relationships, data normalization, and similar topics, but no formal learning regarding classes and subclasses.

The Info tab under the Subtypes tag has a brief summary of the problem.  It mentions inheritance and object modeling in passing.  I want to expand on this a little.  The relational data model, in its original form, has no support for inheritance, although some DBMS products have extended the model with inheritance features.

Fortunately, the problem is well understood, and you can gain valuable insights from people who have studied the problem in depth.  I particularly like Martin Fowler's treatment.  He bridges the gap between how programmers think and how data architects think.

In your case, it appears from your description as though there are a bunch of claim subtype tables, but there is no generic claim table.  I'm going to recommend one particular design for your evaluation.  It's called "class table inheritance".  And I'm going to also recommend a technique called "Shared Primary Key".

In class table inheritance, there will be one table for the generic data that applies to all claims, no matter which subclass they belong to.  This table will have a claim ID, used to point to a row, like the usual table.  The other columns in the table will contain the attributes that are common to claims regardless of what class they belong to.

The other tables contain data that pertain to particular classes (types) of claims.  The ID column of these subclass tables deserves particular attention.  It's not populated in the usual way, by having the DBMS assign a unique number to it.  Instead it is an "inherited" copy of the ID column of the Class table.  This column is both the primary key of the subclass table and a foreign key that references the appropriate row of the claims table.

When you want to deal with a particular class of claim, just join the claims table with the appropriate subclass table.  The irrelevant claims will drop out of the join.

The reason I put "inherited" in quotes is that it requires some programming on your part to cause this inheritance to take place when a new claim is added.   You have to add the row to the claim table, then obtain the automatically generated ID, then propagate it to the subclass table, before making the insert into the subclass table.

That's my recommendation, but it's your call.


Restoring a backup to an older version of SQL Server

You'd have use the Import/Export wizards in SSMS to migrate everything

There is no "downgrade" possible using backup/restore or detach/attach


sql server 2012 developers edition license and 3rd party applications

No, the Developer Edition is for developers only. If it contains production data, you are not licensed and it doesn't matter that only the ERP system accesses the database, the users of your ERP system count as users of SQL server.


LEFT JOIN performance for WHERE on secondary table

Remember how SQL works: construct a set of tuples (rows) based on the given tables and joins, then filter those based on any where clause.
Because you have a where condition on the "right" table, your outer join is effectively converted into an inner join.

To retain the "outer-ness" of the join, put the filter condition into the join construct, rather than in the where clause:

select *
from      user u
left join gender g
     on  u.genderId = g.id
     and g.show = 1
[where ...]


Optimize the DB select query JSON B type extract

You painted yourself into a corner by modeling this with jsonb. The whole large thing has to be read to extract the attribute.
The best I can think of is to break the desired value out of the large object into its own column:
ALTER TABLE mytab
   ADD results jsonb GENERATED ALWAYS AS (message -> 'results') STORED;

This will work on recent PostgreSQL versions; for older versions you can use a trigger that does the same thing.
Then you only have to read the results column instead of the whole message.


In which context does a procedure run when called from another context?

It executes in the context of the database the object exists in. Example code:

CREATE DATABASE Temp1
GO
USE [temp1]
GO
CREATE TABLE Data1 (Val VARCHAR(255))
GO
INSERT INTO Temp1.dbo.Data1 (Val)
VALUES ('Test1')
GO
CREATE PROCEDURE spTemp1
AS
BEGIN
    SELECT * FROM dbo.Data1
END
GO

CREATE DATABASE Temp2
GO
USE [temp2]
GO
CREATE TABLE Data1 (Val VARCHAR(255))
GO
INSERT INTO Temp2.dbo.Data1 (Val)
VALUES ('Test2')
GO
CREATE PROCEDURE spTemp2
AS
BEGIN
    SELECT * FROM dbo.Data1
END
GO

USE [Temp1]
GO
EXEC Temp2.dbo.spTemp2
GO

USE [master]
GO
DROP DATABASE Temp1
DROP DATABASE Temp2
GO

This highlights that even though we query "dbo.Data1" which exists in both databases, the value returned is from that table in the Temp2 database because that is the context of the stored procedure.

The example is a SELECT, but the same holds true for an INSERT.


After trying to restart moved database: Unable to open the physical file "D:\Data\MYDBNAME.MDF"

While the database is offline, you need to copy the files to the new location. This is not something that SQL Server does automatically when you alter the file location. My preferred method is to use the following steps:

Set the database offline
Copy (don't move) the database files
After copy completes successfully, ALTER DATABASE...MODIFY FILE to change metadata for file locations
Set the database online
Confirm the database restarts successfully

If there's a problem bringin the database online, the files are still in the original location, so you can rollback.

Delete the old files

When performing the copy, you'll need to ensure that the permissions on the files are the same in the new location as in the old location. Otherwise, you'll run into different problems.

If you are using ROBOCOPY to move the files, you can use the /COPYALL flag to ensure that security & file info is copied along with the file.

You can also manually verify the permissions, and verify that the folder permissions are inherited to all contents of the directory.


OLTP - Stretch database vs linked server

Limitations for Stretch Database

Not all tables will be eligible for being Stretch-enabled. Certain table properties, data and column types, constraints, and indexes are not supported, such as:

Memory-optimized and replicated tables
Tables that contain FILESTREAM data, use Change Tracking or Change Data
Data types such as timestamp, sql_variant, XML, geography or columns that are Always Encrypted
Check and default constraints or foreign key constraints that reference the table
XML, full-text, spatial, clustered columnstore and indexed views that reference the Stretch-enabled table
You cannot run UPDATE or DELETE statements, or run CREATE INDEX or ALTER INDEX operations on a Stretch-enabled table


Is MariaDB a suitable drop in replacement for MySQL on a standard LAMP stack?

One valid reason to move to another MySQL flavour is performance. The latest MariaDB versions 5.3 and 5.5 (which are drop in replacemants of MySQL 5.1 and 5.5 respectively) included major improvements in query optimization. You can read more on their site: What is MariaDB 5.3

Examples on improvements/features are "Index Condition Pushdown", "Cost-based choice of range vs. index_merge", "Subquery Materialization", "Subquery Cache" and new joins methods, like "Hash Join". Several of the features affect performance of (statements with) subqueries, derived tables and views.

Details can be found at the Optimizer Feature Comparison Matrix page.

Other additional features like microsecond support for time datatypes and improvements on the replication and binary logging.

The previous versions (5.1 and 5.2) also have some features that are not found in (mainstream) MySQL, like [PERSISTENT] VIRTUAL columns.

Of course, any migration of your server from MySQL to MariaDB should be examined and tested by you as whether these features are useful in your databases/applications and whether the difference in performance (if any) is big enough for the troubles you may have (as with any migration).


Oracle Patching & Database Upgrade - Which Order

3<4, I opt for option 2 approach.

This is also what Oracle instructs us to do in for example this document:

http://docs.oracle.com/cd/E11882_01/server.112/e23633/upgrade.htm#UPGRD12394

  After you have installed the software for Oracle Database 11g Release
  2 (11.2) and any required patches, Oracle recommends that you analyze
  your database before upgrading it to the new release


It depends on the scope of the variable.

I once wrote a post Disable trigger for just one table which uses a variable called @TRIGGER_DISABLED.

In order for the variable to disable the trigger, your session has to instantiate the value SET @TRIGGER_DISABLED=0; or SET @TRIGGER_DISABLED=1; before any DML again the table. If you disconnect and reconnect, you have to instantiate the variable each time you reconnect.

In your case, when the DB session starts, @amountPaid and @totalPayments would have to set to 0 before doing anything. You cannot disconnect and reconnect if you are keeping running totals.

If you always disconnect and reconnect, the running totals could be stored in a global table that uses the MEMORY storage engine with the fields and a session ID of your choosing..


The MongoDB Extended JSON format allows representation of additional BSON data types that are not part of standard JSON.

There is a subtle distinction between:

being able to parse the extended JSON into a JavaScript representation
the parser then taking the additional step of interpreting the extended key/value pairs into an object with associated JavaScript wrappers such as NumberLong() or Date()

As per the MongoDB Extended JSON page you linked to, the mongo shell can parse the strict mode Extended JSON (which conforms to the JSON standard) but without recognition of type information:

  Other JSON parsers, including mongo shell and db.eval(), can parse strict mode representations as key/value pairs, but without recognition of the type information.

The strict mode is only only intended as a data interchange format (for example, transferring a collection via mongoexport and mongoimport).

For queries in the mongo shell you should use the mongo Shell Mode representations as noted in the MongoDB Extended JSON documentation.


In Oracle, why would I not always delete all old archive log files with RMAN?

Yes, archive log can duplicated in multiple locations, and in many cases delete all is what you normally do.

But sometimes you may want to control one or more location without rman. For instance, Data Guard is not available in Standard Edition.   However, couple simple shell scripts can replace it to some extent . Surely, it's not a full substitution, but it's enough for most small-to-medium systems that use Oracle DB .  All you need is to reliably copy archive logs from one host to another, and restore them.  It's much better to give full control over one of archive log destinations to these scripts (or maybe homebrew applications or third party products) , so they can decide when it's safe to delete them.


I'm not super familiar with full text searches but I think this StackOverflow answer gives us the answer which is also supported by this StackOverflow answer.  The solution was to create a computed column containing what is essentially a concatenation of all the columns you want to apply your search terms to and do your full text search on that column.

Alternatively you can use the AND, OR, NOT logic in separate expressions like the OP in the StackOverflow answer I linked to.

That being said someone here might have a better solution.


How should I configure my postgresql.conf file to backup the database once per hour?

For hourly backups,  you can achieve this by scheduling Cronjob to take Pg_dump.  Alternatively,   you can configure Bart and schedule the backups.


Importing a schema in Oracle with a different name

REMAP_SCHEMA

  The Data Pump Import command-line mode REMAP_SCHEMA parameter loads
  all objects from the source schema into a target schema.

  Default: There is no default

  Purpose

  Loads all objects from the source schema into a target schema.

Example:

remap_schema=USER1:USER2


Use a subquery, something like:

SELECT * FROM (
  SELECT
    t.*,
    key REGEXP 'some long regular expression' AS a,
    key REGEXP 'some other long regular expression' AS b
  FROM table t
  WHERE a OR b
)
ORDER BY a DESC, b DESC;


I ran into the same issue you did. I would go on to discover that Event Notifications can be used to handle the AUDIT_CHANGE_DATABASE_OWNER event. (Note that this event can't be used with a DDL trigger.) I wrote an Event Notifications blog article that just happens to use the AUDIT_CHANGE_DATABASE_OWNER event as an example: SQL Server Event Handling: Event Notifications

Below is a script that can get you started.

USE SomeDatabase
GO

--Create a queue just for change db owner events.
CREATE QUEUE queChangeDBOwnerNotification

--Create a service just for change db owner events.
CREATE SERVICE svcChangeDBOwnerNotification
ON QUEUE queChangeDBOwnerNotification ([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification])

-- Create the event notification for change db owner events on the service.
CREATE EVENT NOTIFICATION enChangeDBOwner
ON SERVER
WITH FAN_IN
FOR AUDIT_CHANGE_DATABASE_OWNER
TO SERVICE 'svcChangeDBOwnerNotification', 'current database';
GO

CREATE PROCEDURE dbo.ReceiveChangeDBOwner
AS
BEGIN
    SET NOCOUNT ON
    DECLARE @MsgBody XML

    WHILE (1 = 1)
    BEGIN
        BEGIN TRANSACTION

        -- Receive the next available message FROM the queue
        WAITFOR (
            RECEIVE TOP(1) -- just handle one message at a time
                @MsgBody = CAST(message_body AS XML)
                FROM queChangeDBOwnerNotification
        ), TIMEOUT 1000  -- if the queue is empty for one second, give UPDATE and go away
        -- If we didn't get anything, bail out
        IF (@@ROWCOUNT = 0)
        BEGIN
            ROLLBACK TRANSACTION
            BREAK
        END
        ELSE
        BEGIN
            --Interrogate the event data for relevant properties/values.
            DECLARE @Cmd VARCHAR(1024)
            DECLARE @MailBody NVARCHAR(MAX)
            DECLARE @Subject NVARCHAR(255)

            SET @Cmd = @MsgBody.value('(/EVENT_INSTANCE/TextData)[1]', 'VARCHAR(1024)')
            SET @Subject = @@SERVERNAME + ' -- ' + @MsgBody.value('(/EVENT_INSTANCE/EventType)[1]', 'VARCHAR(128)' )

            --Build an html table for use with an html-formatted email message.
            SET @MailBody =
                '
' +
                '
Server Name	' + @MsgBody.value('(/EVENT_INSTANCE/ServerName)[1]', 'VARCHAR(128)' ) + '
' +
                '
Start Time	' + @MsgBody.value('(/EVENT_INSTANCE/StartTime)[1]', 'VARCHAR(128)' ) + '
' +
                '
Session Login Name	' + @MsgBody.value('(/EVENT_INSTANCE/SessionLoginName)[1]', 'VARCHAR(128)' ) + '
' +
                '
Login Name	' + @MsgBody.value('(/EVENT_INSTANCE/LoginName)[1]', 'VARCHAR(128)') + '
' +
                '
Windows Domain\User Name	' + @MsgBody.value('(/EVENT_INSTANCE/NTDomainName)[1]', 'VARCHAR(256)') + '\' +
                    @MsgBody.value('(/EVENT_INSTANCE/NTUserName)[1]', 'VARCHAR(256)') + '
' +
                '
DB User Name	' + @MsgBody.value('(/EVENT_INSTANCE/DBUserName)[1]', 'VARCHAR(128)' ) + '
' +
                '
Host Name	' + @MsgBody.value('(/EVENT_INSTANCE/HostName)[1]', 'VARCHAR(128)' ) + '
' +
                '
Application Name	' + @MsgBody.value('(/EVENT_INSTANCE/ApplicationName)[1]', 'VARCHAR(128)' ) + '
' +
                '
Command Succeeded	' + @MsgBody.value('(/EVENT_INSTANCE/Success)[1]', 'VARCHAR(8)' ) + '
' +
                '

' +
                '
Text Data:
' + REPLACE(@Cmd, CHAR(13) + CHAR(10), '
') +'

'
            --PRINT @Subject
            --PRINT @MailBody

            --Note: you may need to set [msdb] to trustworthy for this to work.
            --Another option is to sign a stored proc with a certificate.
            --See: https://docs.microsoft.com/en-us/sql/relational-databases/tutorial-signing-stored-procedures-with-a-certificate
            EXEC msdb.dbo.sp_send_dbmail
                @recipients = 'You@YourDomain.com',
                @subject = @Subject,
                @body = @MailBody,
                @body_format = 'HTML',
                @exclude_query_output = 1
            /*
                Commit the transaction.  At any point before this, we
                could roll back -- the received message would be back
                on the queue AND the response wouldn't be sent.
            */
            COMMIT TRANSACTION
        END
    END
END
GO

ALTER QUEUE dbo.queChangeDBOwnerNotification
WITH
    STATUS = ON,
    ACTIVATION (
        PROCEDURE_NAME = dbo.ReceiveChangeDBOwner,
        STATUS = ON,
        MAX_QUEUE_READERS = 1,
        EXECUTE AS OWNER)
GO


Delete rows in parent table based on criteria in child table - Oracle

Try like this,

DECLARE
     A_ID_TO_DELETE DBMS_SQL.NUMBER_TABLE;
BEGIN
     SELECT A_ID BULK COLLECT INTO A_ID_TO_DELETE
     FROM (SELECT A_ID
           FROM   B
           WHERE  LENGTH(B_DATA) > 4
          );

     DELETE FROM B
     WHERE LENGTH(B_DATA) > 4;

     FORALL i IN A_ID_TO_DELETE.first..A_ID_TO_DELETE.last
          DELETE FROM A
          WHERE A_ID = A_ID_TO_DELETE(i);
END;
/


You can write the query simpler without CTE which probably would perform better.

select (jsonb_array_elements(transaction->'transactions')->'id')::text as trx_id
from transactions;

SQL alchemy returns a list of tuples. This is the standard behaviour. A row in PostgreSQL is represented as a tuple. Even if you query a single columns it's going to be a tuple with one element. Hence the (u'ABC123',) representation.

If you need a list of the first column you can easily transform your resultset in python:

[r[0] for r in rowset.fetchall()]

This should return

['ABC123', 'DEF456', 'GHI789']


Postgresql tries to connect to fe80::1, permission denied

So this is almost certainly a bad configuration in ip address resolution.  As you mention, you have a line resolving localhost to your external ipv6 address.  Yes, this should be removed, or changed from fe80::1 to ::1


Index is not used when joining two tables in a stored procedure

Using where; Using join buffer (Block Nested Loop) is perhaps better than using an index.  It loaded the entire table in RAM so it can quickly probe it.

You seem to be simulating a FULL OUTER JOIN?  That can be made more efficient...

Create only one tmp table; then use a combination of the original and the tmp table.
Don't include the "intersection" in both parts of the UNION, thereby necessitating UNION DISTINCT (which is what you have), but rather the faster UNION ALL.
periodholdings LEFT JOIN tmp gives you the intersection and NULLs on one side
tmp LEFT JOIN periodholdings ... IS NULL gives you the other lobe of the Venn diagram.

Other issues...

If (stkcode,participantname) is unique, make it the PRIMARY KEY; that will speed things up.
Why have a name and id in the same table?  Shouldn't this be "normalized" into another table?
Don't blindly use (255); pick something smaller, but sufficiently big.  This will speed up any tmp tables needed.
decimal(5,4) - You are not going over 9.9999?

Please provide EXPLAIN FORMAT=JSON SELECT ...; it may provide some more details.


postgres fdw create foreign table without specifiy columns

This is what IMPORT FOREIGN SCHEMA is for.  You can run it for a single table at a time if you want to.

oracle_fdw says it supports IMPORT FOREIGN SCHEMA, but be sure to read that section of the docs for more info.


Unable to backup after recovery model change

In SQL Server you have 3 forms of backup: Full, Differential and Log:

The Full backup is the most important because it has all the information of the database.
The differential is the diference of the last full backup until now.
The Log is the diference of the last full or differential backup until now.

So you can have just the Full Backup or Full and Diferrential or Full and Log or Full, Differential and Log backups. You choose the best combination for your need.

Also look for the recovery/nonrecovery option in the restore clause


There are many ways.  All "work":

Add a deleted column.  (This may have performance problems in the long run.)
Have the application move the row to another table.  (Poor man's Trigger; error prone.)
Trigger that copies the row to another table.  (Clumsy, slow, needs transaction.)  And, as you ask, what about the deleting the row.
Stored Procedure to "delete" a row.  (Users must call it; need to deny DELETE PRIVILEGE to everyone except the Procedure.)
Application procedure.
My preference for big, multi-user, applications:

I like the "database layer" in the application.  Instead of letting the users perform actual SQL statements, require them to call APIs in application code (PHP/Java/whatever).  This way, changes to the underlying schema can be changed with little or no retraining of the users.

If the API is "Delete(key)", then the "layer" could deal with a new deleted column, or moving the row to new table or calling a stored proc instead of issuing DELETE FROM tbl ....  Etc.

And if I decide that the solution picked is less than perfect, I can change it without even informing the users.


Changes made by TransactionA not seen by TransactionB eventhough TransactionA commits them. How is that possible?

The problem was related to the fact that there was a query being executed before MasterRecordX got locked. Here is what happened:

ConnectionA starts TransactionA
RecordW is queried.
MasterRecordX gets locked for update.
...
        ConnectionB starts TransactionB
        RecordW is queried.
        MasterRecordX gets locked for update.
        ...
DetailRecordY is queried
DetailRecordY is changed
...
ConnectionA commits TransactionA
        DetailRecordY is queried
        DetailRecordY is changed
        ...
        ConnectionB commits TransactionB

Because RecordW on TransactionB was queried before DetailRecordY was changed and commited in TransactionA, the changes were not seen by TransactionB. Because of the isolation level (REPEATABLE READ), once a query is executed within a transaction, no later data changes are seen within that transaction.


SQL 2008: agent job error in opening excel files using DTS package

Have you granted the account Domain\SQL2008S1_service admin rights to the server SQL2000S2?  You will need to do so in order to use the C$ network share.


To elaborate on my comment, I would use a set based approach for better performance with error handling, here is how I would do it. I mixed the two common methods, using JOIN and NOT EXISTS / EXISTS.

Note, I don't use a temp table or table variable here. I've seen performance gains when the databases are on different servers but pulling the data into a temp table before doing an upsert across a linked server or whatever, but I'm not sure if your databases are on the same instance or not.

set xact_abort on;
begin try
    begin tran

    update d
    set d.column = s.column
    from destinationTable d
    inner join sourceTable s on
    s.id = d.id

    insert into destinationTable (...column list...)
    select s.1, s.2, ...
    from sourceTable s
    where not exists(select id from destinationTable d where d.id = s.id)

    commit
end try
begin catch
    if @@trancount > 0 rollback tran
    declare @msg nvarchar(2048) = error_message()
    raiserror (@msg, 16, 1)
    return 55555
end catch

Some good reads

Erlan Sommarskog's Error and Transaction Handling
Aaron Bertrand's Use Caution w/ SQL Server MERGE
Martin Smith's break down of Table Variable vs Temp Tables and his suggestions on when you should use one over the other
Using LEFT JOIN or NOT EXISTS

In the case that the data is in on a different server a temp table could increase the performance of the insert (or may be the only option if it's not SQL Server). The above code would be the same, except the sourceTable would be replaced with #sourceTable.

If the table is large, then you may want to consider staging tables instead of #tempTables so that you don't beat up tempdb too bad. BULK INSERT into the staging tables (sourceTable) and then do your upsert from here.


Alter table and update in sql server transactions

You have to break it up into batches because the [Column] does not exist since the statements are being parsed as one batch so it cannot see the new column, hence you receive the error.

Once you break it up into batches with GO statement the column will be added to the table and then your next batch updating the new column will be successful.

Full example:

DROP TABLE #A;
GO
CREATE TABLE #A (ID int IDENTITY(1,1), column1 varchar(1))
GO
INSERT INTO #A (column1)
VALUES('a'),('b'),('c')
GO

begin transaction
alter table #A add [Column] int;
go

begin transaction
update #A set [Column] = 1 where ID=1;
select * from #A;

rollback transaction;
GO

SELECT *
FROM #A


MySql Replicate different tables to different database

You could make server2 as slave of server1. Server2 contains a replica of db1. Into db2 create a view that point to db1.t1, and int db3 create a view that point to db1.t2. I have attached an image to clarify.


Select the latest changed row in a table

If you don't need the id (or any other column, not included in the question) but only the name and the newest changedate, then you only need a simple GROUP BY query - and an index on (name, changedate) for efficiency:

SELECT name, MAX(changedate) AS changedate
FROM test
GROUP BY name ;

If you need more columns, one way to solve is to put the above query in a derived table and JOIN to the original table:

-- Query 1
SELECT t.*
FROM test AS t
  JOIN
  ( SELECT name, MAX(changedate) AS changedate
    FROM test
    GROUP BY name
  ) AS g
    ON  g.name = t.name
    AND g.changedate = t.changedate ;

You could also use a LEFT JOIN or a NOT EXISTS solution:

-- Query 2a
SELECT t1.*
FROM test AS t1
  LEFT JOIN test AS t2
    ON  t1.name = t2.name
    AND t1.g.changedate < t2.changedate
WHERE t2.name IS NULL ;

-- Query 2b
SELECT t1.*
FROM test AS t1
WHERE NOT EXISTS
      ( SELECT *
        FROM test AS t2
        WHERE t1.name = t2.name
          AND t1.changedate < t2.changedate
      ) ;


Recover database from uninstalled SQL Server

The database files are probably still there - in the directory for data and log files.

Th default one is c:\ProgramFiles\MSSQL\MSSQL\MSSQL10_50\MSSQL\Data.
If not default then you need to locate your files and attach them to SQL Server instance of the same or higher edition.


Partitioning a table will help you in maintaining.

Here are some useful articles on maintaining a large table:

Handling Large SQL Server Tables with Data Partitioning

The only downside is that it only exists in the Enterprise and Developer editions.

Simplify Database Maintenance with Table Partitions

And more info on hardware, horizontal, and vertical partitioning:

Partitioning

  Partitioning a database improves performance and simplifies
  maintenance. By splitting a large table into smaller, individual
  tables, queries that access only a fraction of the data can run faster
  because there is less data to scan. Maintenance tasks, such as
  rebuilding indexes or backing up a table, can run more quickly.

  Partitioning can be achieved without splitting tables by physically
  putting tables on individual disk drives. Putting a table on one
  physical drive and related tables on a separate drive can improve
  query performance because, when queries that involve joins between the
  tables are run, multiple disk heads read data at the same time. SQL
  Server filegroups can be used to specify on which disks to put the
  tables.

Also, there are a lot of useful answers in this thread

Hope this helps


Short answer: you can't. Not with the file only, at least.

Long answer: what you can do is query msdb.dbo.backupset on the source instance to extract this information. The backup start/end date of the previous T-LOG backup is approximately the beginning timestamp of the log records conteined in each .TRN file. The end of the log chain is the backup begin/end date of that .TRN file.


Can replication be set up when already mirroring?

You can absolutely setup transactional replication on mirrored publishing databases. It is fully supported, and Microsoft has some good content on getting it up and running.

The only caveats are the ones that already exist for transactional replication, and the recommendation to have the distributor database on another server outside of the mirrored pair.


SQL server AlwaysOn alternate option for readintent=true

I(DBA) want to block this application's read only connections to primary and want the readonly requests of this application always routes to a AlwaysOn secondary replica then how this can achieved without "applicationIntent= readonly" option?

The simple solution is to configure the application to connect directly to the host hosting the secondary replica.  The downside is that if the secondary is offline, the application won't be able to connect.
Alternatively, if your SQL Instances are standalone instances configured to listen on all IPs (the default), you can add an additional Client Access Point (ie a Network Name and an IP Address), to the cluster in a new Application (Cluster Resource Group), and configure the preferred owners of the group, and/or the possible owners of the Client Access Point to control which node the the new Client Access Point run on.  The application would be configured to connect to the Network Name, which could fail over to some node if the preferred owner node is offline.


WinSCP extract won't connect when run by SQL Server Agent Job

Turns out the issue was the firewall.  I figured out that with this setup, SQL server runs remotely and the server it runs off of had a firewall that was blocking the FTP site's IP address.  The network admins were able to whitelist the IP address, now everything runs correctly. Thanks to those that helped me work through the problem.


Post Mortem Analysis: PostgreSQL replication failed

Based on this I'd say you didn't have enough wal_keep_segments on the master, weren't using a replication slot, and either had hot_standby_feedback off or had the connection drop for long enough for the master to remove needed WAL.

And you are presumably not using WAL archiving (archive_command on master, restore_command on replica) as fallback.

So the master removed transaction logs the standby needed.

You'll need to re-create the standby. Then either:

Set the standby to use a replication slot and enable hot_standby_feedback; or
enable archive_command and restore_command


Is there any difference with specifying the primary key as an include column in a nonclustered index?

Nonclustered indexes contain a row locator back to the base table.

This is a clustered index key for rowstore tables with a clustered index or a physical RID (file/page/slot) for heaps.

So the parts in your question assuming that it uses the primary key (in the event that these are different) are moot.

The row locator is added to the key for non unique nonclustered indexes and as an included column to non clustered indexes declared as unique.

There is no performance advantage of specifying this explicitly for the included case. For the case that it is implicitly added to the key you may need to do this explicitly if your queries would benefit from a different asc/desc direction or multi column ordering than the implicit would give you.

You may prefer to be explicit about it for readability of your code for developers unfamiliar with the implicit behaviour or if your queries would still need those columns in the index even if the clustered index definition was to change in the future.

Regarding the followup question in the comments

  Does that mean it's possible a key lookup might be performed from a
  nonclustered index for the primary key (when the primary key is not
  the clustered index) if that primary key is in the select list?

Yes, you can see this with the below.

CREATE TABLE #T
(
PK INT PRIMARY KEY NONCLUSTERED,
CI INT INDEX CIX CLUSTERED,
OtherCol INT INDEX NCIX NONCLUSTERED
)

SELECT *
FROM #T WITH (FORCESEEK)
WHERE OtherCol = 10

The output list of the seek on NCIX is Uniq1001, CI, OtherCol
The values of CI, Uniq1001 are then used by the lookup to seek into the clustered index...
... and the value for PK is returned from that lookup.

If you were wondering about Uniq1001 this is a part of the clustered index key silently added by the system to act as a uniqueifier as the clustered index was not declared as unique. It is empty except for rows where duplicate exist/existed for the value of CI.


How to support multiple updates in this trigger?

If dbo.Generarslug is a scalar function, then you could use the following:

INSERT[dbo].[UrlRecord] ([EntityId],[EntityName],[Slug],[IsActive],[LanguageId])
SELECT  i.id,
        'Category',
        dbo.GenerarSlug(i.Name),
        1,
        0
FROM    inserted AS i
WHERE   NOT EXISTS
        (   SELECT  1
            FROM    [dbo].[UrlRecord] AS r
            WHERE   r.EntityID = i.ID
            AND     r.EntityName = 'Category'
        );

There is still potentially scope for improvement depending on what dbo.GenerarSlug actually does.


How to optimize Mysql database of 250 million rows for bulk inserts and selects

Your should increase the innodb_write_io_threads to 8.
This setting configures how many threads are dedicated to writing to .ibd files from the Buffer Pool.


Tablespace utilization in db2 (percentage of tablespaces)

You can use list tablespaces show detail (you need to be connected to the database for the show detail clause). This will show you the amount of disk (in bytes if I'm not mistaken) available to the tablespace, the amount used, and the amount "claimed" (known as the "high water mark"). Claimed vs used can differ at times. Sometime after massive deletes and even a REORG the amount of the tablespace used is significantly reduced, but if the high water mark is high, then DB2 still has laid claim to this disk and not released it back to the OS.


Stored procedure working when run manually, not running in SQL Agent Job

I had this same issue when google geocoding and parsing the XML result.  Seems that when run via an SQL job the XML result was being truncated.

Found the solution here: http://www.progtown.com/topic376715-a-problem-with-spoagetproperty-p2.html

Had to put:

SET TEXTSIZE 2147483647;

at the start of my stored proc.


In my opinion, these restrictions are best maintained within the UI for said survey/questionnaire because a survey is designed to be situation-specific and highly volatile whereas a data model should be built to be robust and flexible, but not so rigid it become grossly limited in functionality.  Regarding the rest of the answer though, these features primarily pertain to Microsoft SQL Server.  Most of this functionality can be found in other RDBMS platforms, but the links I'll include are specific to MSSQL.

If you must push this sort of logic down to the database, you have a few options.  The first one you've already touched on is the Unique Constraint.  This is a quick check to execute, but it is applied to the entire table, so in your example if another "Chuck" comes and takes the survey his selection of Jogging on Monday (while a valid decision because it's a different Chuck) will throw a constraint error.  Obviously the table would need to be designed so it could track unique users, etc. but hopefully you get where I'm going with the limitations of Unique Constraints.

The sibling of the Unique Constraint is the Check Constraint.  These can be a little more fleshed out as you can create User Defined Functions wherein nested logic can be coded.  So for scenarios B and C above, you could conceptually code up some logic to check for those conditions.  Check Constraints with Scalar UDFs are often horribly slow and will cause performance issues, so this isn't an approach I'd ever recommend... but you can do it.

Another option within the database to handle these situations are Triggers, and in this situation specifically, DML Triggers.  These can be designed to be even more complex than Check Constraints and given the right situation can perform relatively well.  Again, these can also fall prey to poor performance if they're not written well, but this is another way to force logic down to the data model layer.

Hopefully this helps, but forcing this much business logic into the data layer is often frowned upon unless there is some sort of performance benefit, which in this case is unlikely.


SQL Server Troubleshooting Replication and Long-Running fn_dblog query

If fn_dblog is hanging you may try some third party tools from ApexSQL, RedGate or Idera. Or you can try to backup the transaction log file and use fn_dump_dblog just to exclude problems with an online transaction log. You can find more info on this DBA post


Oracle: ORA-00942: "table or view does not exist" in SQL Developer

SQL Developer runs the below query (which you can check by clicking the Run Report in SQL Worksheet button) for its session browser:
with vs as (select rownum rnum,
                            inst_id,
                            sid,
                            serial#,
                            status,
                            username,
                            last_call_et,
                            command,
                            machine,
                            osuser,
                            module,
                            action,
                            resource_consumer_group,
                            client_info,
                            client_identifier,
                            type,
                            terminal,
                            sql_id,
                            sql_child_number
                       from gv$session)
           select vs.inst_id, vs.sid ,serial# serial, vs.sql_id, vs.sql_child_number,
                  vs.username "Username",
                  case when vs.status = 'ACTIVE'
                            then last_call_et
                       else null end "Seconds in Wait",
                  (select command_name from v$sqlcommand where command_type = vs.command ) "Command",
                  vs.machine "Machine",
                  vs.osuser "OS User",
                  lower(vs.status) "Status",
                  vs.module "Module",
                  vs.action "Action",
                  vs.resource_consumer_group,
                  vs.client_info,
                  vs.client_identifier
             from vs
            where vs.USERNAME is not null
              and nvl(vs.osuser,'x') <> 'SYSTEM'
              and vs.type <> 'BACKGROUND'
              order by 1,2,3

Based on that, grants needed:
grant select on sys.gv_$session to your_user;
grant select on sys.v_$sqlcommand to your_user;

You may need additional privileges for other options.


How to join two result sets from same table with different conditions on same column?

If you want number and kilograms to appear as a single column (comma separated), do this:

SELECT GROUP_CONCAT(uom_name) UnitsOfMeasure
FROM unit_of_measure WHERE uom_id IN (1,2);

If you want number and kilograms to appear as a single column (space separated), do this:

SELECT GROUP_CONCAT(uom_name SEPARATOR ' ') UnitsOfMeasure
FROM unit_of_measure WHERE uom_id IN (1,2);

If you want number and kilograms to appear as separate columns, do this:

SELECT * FROM
(SELECT uom_name AS uomname1 FROM unit_of_measure WHERE uom_id=1) A,
(SELECT uom_name AS uomname2 FROM unit_of_measure WHERE uom_id=2) B;

Give it a Try !!!


PostgreSQL for high volume transactions and for Data warehousing

Infrastructure/Database Services

You should probably read this for an overview of a high-volume site that runs on AWS with EBS. They've moved to Ephemeral storage but have had to create some redundancy in being able to (re)store the data.

http://blog.reddit.com/2012/01/january-2012-state-of-servers.html

Data Warehouse/ETL

I've used Pentaho in the past. Not directly with postgres, but I've found it to be a good solution for both OLAP (Mondrian) and ETL (Kettle)

http://www.pentaho.com/

edit: "Community Editions" can be found here

http://mondrian.pentaho.com/

http://kettle.pentaho.com/

Connection

These folks seem to really like pgbouncer. https://stackoverflow.com/questions/1125504/django-persistent-database-connection

I have no experience with it, though. Apparently, Disqus uses it.


Check for overlaps for several arrays in one query

I suppose you want something like this:

with param (p) as
  ( values ('{cat}'),
           ('{bird}'),
           ('{dog}')
  )
select *
from param left join tbl
     on param.p::text[] && tbl.arr ;

Result:

   p    | id |      arr
--------+----+----------------
 {cat}  |  2 | {fish,cat,rat}
 {bird} |    |
 {dog}  |  1 | {dog,mouse}
(3 rows)


IS_MEMBER() in Azure SQLDB not working for AD groups?

This is quite old tread but I was facing similar issue, so if someone encountered it, hope this might help.
I had RLS implemented on SQL Server on IaaS and it was based on Active Directory Groups checks by IS_MEMBER() function. (I have sync with AAD, but here used config with local AD). It worked well until I migrated to PaaS (Azure SQL DB Single instance).
First thing was that while on IaaS I used to refer to AD groups from local AD domain controler:domain\groupname
CREATE LOGIN [domain\groupname] FROM WINDOWS
CREATE USER [domain\groupname] FOR LOGIN [domain\groupname]
select is_member('domain\groupname')
In PaaS I needed to create users for groups and reference to them without domain, as I had to switch to use integrated Azure Active Directory:
groupname
CREATE USER [groupname] FROM  EXTERNAL PROVIDER
select is_member('groupname')
Second thing was something that looks like a bug (currently have open ticket with MS)
While IS_MEMBER worked correctly for test users in PaaS, I had problem with Admin Accounts. Taking for example user1 who was in [adminGroup] on DataDB IS_MEMBER always returned 0, no matter which AAD group I was checking. What was more strange, when changing to Master db IS_MEMBER worked fine there for any AAD group!(offtopic: on master db users where also created to avoid problem with SSMS default option to reach to master DB first, and without addition there non-admin users would get connection error (so either users needed to specify in SSMS connection options destination DB, or be added with connect rights to master DB))
 It turned out that having [adminGroup] being set up as Azure Active Directory Admin on Azure SQL Server was reason for this strange behavior. I changed in our code to use [adminGroup2] for RLS, and removed admins (users) in AAD from [adminGroup] which was kept as SQL Server Azure Active Directory Admin. And with just this change IS_MEMEBER started to work just as expected!
 Other workaround I have found here, would be to use IS_MEMBER('db_owner') for admin group (as those default roles checks are working fine, but this needs additional changes to security config and would not solve problems for other cases then admins, but actually looks like this is the only problem here.
 Waiting to hear response from MS about this bug, as I doubt that that would be implemented by design...


How do i subtract a number of days from a prepared statement parameter in db2?

Just the cross join bit:

SELECT CALC.WEEK, COUNT(CALC.ID) AS TRANSACTIONS ,
    SUM(CALC.SUBTOTAL) AS REVENUE
FROM (
        SELECT
            CASE
                WHEN O.DATE < the_param - 21 DAYS THEN 1
                WHEN O.DATE >= the_param - 21 DAYS AND O.DATE < the_param - 14 DAYS THEN 2
                WHEN O.DATE >= the_param - 14 DAYS AND O.DATE < the_param - 7 DAYS THEN 3
                WHEN O.DATE >= the_param - 7 DAYS THEN 4
            END AS WEEK,
            O.ID, O.SUBTOTAL
        FROM COMPANY.ORDERS AS O INNER JOIN
            COMPANY.LOCATIONS AS L ON O.LOCATION_ID = L.ID INNER JOIN
            COMPANY.STORES AS S ON O.STORE_ID = S.ID CROSS JOIN
            (SELECT ? as the_param FROM SYSIBM.SYSDUMMY1)
        WHERE O.DATE >= the_param - 28 DAYS
            AND O.DATE <= the_param
            AND S.NUMBER = ?
    ) AS CALC
GROUP BY CALC.WEEK

you might need to give the subquery an alias—not sure if that is mandatory on DB2


Delete in big table data all data, where foreign_id in other table didn't exist

This should do it:

delete from table_a where article_id not in (select id from article)

A variant that maybe faster in older versions

delete a.* from table_a a
left join article on a.article_id = article.id
where article.id is null

Oh and regarding the second part of your question...  You may want you put another column on table_a,  like checked that you can set.  And of course you need to put an index on article.id.

It occurs to me that if you are going to be deleting a good fraction of the records then it will be faster to simply make a new table which is a subset of the original.

create table table_b as
select * from table_a where article_id in (select id from article)

Mmm... actually given the problem with old version IN optimization this is probably the best query to go with

create table table_b as
select  a.id, a.article_id, a.text
from table_a a
inner join article on a.article_id = article.id

And just make sure you have an index on article.id (an index on table_a.article_id won't hurt either)

(just an addendum regarding the changing status of IN clause optimisation - I cannot find recent specific benchmarks on googleland but relevant discussion is here for Mysql and Maria


What would be a better many-to-many solution in MongoDB of the relationship would number 1000000?

You can add a third collection, named person_group, being described in that blog picture.

db.person_groups.insert({
  "_id": ObjectId("5e54ed9f48dc5922c0094a42"),
  "groupId": ObjectId("4e54ed9f48dc5922c0094a43"),
  "personId": ObjectId("4e54ed9f48dc5922c0094a44")
});


Your question hits a 'gray area'; there is not an overwhelmingly 'right' way to design the schema.

When the question is asked about city + state + country, I say:  Have one Locations table that contains all 3 columns.  And use country_code CHAR(2) CHARACTER SET ascii.

I also say "normalize, but don't over-normalize".

There are 'religious wars' fought over your question.  There are valid points on either side.  Flip a coin, implement it, then learn from the decision.

Better yet, implement both ways - writing the CREATE TABLEs, the INSERTs, the SELECTs, etc.

Still better is to implement both, but have a "database abstraction layer" in your code that hides the choice from your application code.  Coming up with a single abstraction for your app that works with either backend will be the best lesson here.


When you create an object in Oracle with double quotes, Oracle creates that objects with the same case as you entered it. Try dropping the "insert_family"   procedure and creating it without the double quotes.

This should help.

SQL> CREATE TABLE family
  2       ( NAME          VARCHAR2(25),
  3         NICK_NAME     VARCHAR2(15),
  4         GENDER        CHAR(1),
  5         AGE           NUMBER(3),
  6         MARITL_STATUS VARCHAR2(15),
  7         QUALIFICTN    VARCHAR2(20),
  8         NO_OF_CHLDRN  NUMBER(2) );

Table created.

SQL>
SQL> create or replace procedure "insert_family"
  2     ( NAME varchar2 ,
  3       NICK_NAME varchar2 ,
  4       GENDER VARCHAR2,
  5       AGE number,
  6       MARITL_STAUS varchar2,
  7       QUALIFICTN varchar2,
  8       NO_OF_CHLDRN number)
  9  is
 10  begin
 11  insert into family values(NAME,NICK_NAME,GENDER,AGE,MARITL_STAUS,QUALIFICTN,NO_OF_CHLDRN);
 12  end;
 13  /

Procedure created.

SQL>
SQL> create or replace procedure "Insert_family"
  2     ( NAME varchar2 ,
  3       NICK_NAME varchar2 ,
  4       GENDER VARCHAR2,
  5       AGE number,
  6       MARITL_STAUS varchar2,
  7       QUALIFICTN varchar2,
  8       NO_OF_CHLDRN number)
  9  is
 10  begin
 11  insert into family values(NAME,NICK_NAME,GENDER,AGE,MARITL_STAUS,QUALIFICTN,NO_OF_CHLDRN);
 12  end;
 13  /

Procedure created.

SQL>
SQL> create or replace procedure "iNsert_family"
  2     ( NAME varchar2 ,
  3       NICK_NAME varchar2 ,
  4       GENDER VARCHAR2,
  5       AGE number,
  6       MARITL_STAUS varchar2,
  7       QUALIFICTN varchar2,
  8       NO_OF_CHLDRN number)
  9  is
 10  begin
 11  insert into family values(NAME,NICK_NAME,GENDER,AGE,MARITL_STAUS,QUALIFICTN,NO_OF_CHLDRN);
 12  end;
 13  /

Procedure created.

SQL>
SQL> create or replace procedure "inSert_family"
  2     ( NAME varchar2 ,
  3       NICK_NAME varchar2 ,
  4       GENDER VARCHAR2,
  5       AGE number,
  6       MARITL_STAUS varchar2,
  7       QUALIFICTN varchar2,
  8       NO_OF_CHLDRN number)
  9  is
 10  begin
 11  insert into family values(NAME,NICK_NAME,GENDER,AGE,MARITL_STAUS,QUALIFICTN,NO_OF_CHLDRN);
 12  end;
 13  /

Procedure created.

SQL>
SQL> exec insert_family('AYUSH','SHUKLA','MALE',12,'UNMARRIED','SCHOOL',0);
BEGIN insert_family('AYUSH','SHUKLA','MALE',12,'UNMARRIED','SCHOOL',0); END;

      *
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'INSERT_FAMILY' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

SQL> select object_name, object_type, status from user_objects where upper(object_name)=upper('inSert_family') order by 1;

OBJECT_NAME                    OBJECT_TYPE          STATUS
------------------------------ -------------------- ------------
Insert_family                  PROCEDURE            VALID
iNsert_family                  PROCEDURE            VALID
inSert_family                  PROCEDURE            VALID
insert_family                  PROCEDURE            VALID

4 rows selected.


PostgreSQL on Windows fails to start - 'could not create shared memory segment'

Community Wiki answer generated from question comments by a_horse_with_no_name

Try setting dynamic_shared_memory_type = none in postgresql.conf.

"Permission denied" seems to indicate that the permissions for the user under which the service runs have been changed.


how to query multiple junction tables to create 1 record

This answer assumes you can only have one entry per tournament in each of the Winner1Test, Winner2Test, RunnerUp1 and RunnerUp2 tables. You should contrain this with a unique constraint.

SELECT r.TourneyDate,
r.TourneyTime,
r.TournamentID,
w1.Winner1,
w2.Winner2,
r1.RunnerUp1,
r2.RunnerUp2
FROM TournamentResultsTest AS r
LEFT OUTER JOIN Winner1Test AS w1 ON w1.TourneyID = r.TournamentID
LEFT OUTER JOIN Winner2Test AS w2 ON w2.TourneyID = r.TournamentID
LEFT OUTER JOIN RunnerUp1 AS r1 ON r1.TourneyID = r.TournamentID
LEFT OUTER JOIN RunnerUp2 AS r2 ON r2.TourneyID = r.TournamentID

If my assumption is incorrect and each table can have multiple entries per tournament, then you would need to use outer apply (top 1) rather than outer join.


MySQL - Select when last entry before timestamp has specific value

First, always post with create table statements and insert statements. You will get much more attention if people dont have to do a lot of work to be able to help you. After all, your time is not more valuable than everyone else time:

create table people
( id char(1) not null
, name char(4) not null
, value varchar(20) not null
, ts timestamp not null);

insert into people (id, name, value, ts)
values ('a',    'last',   'jones',   '2018-04-03 01:00:00')
     , ('a',    'last',   'smith',   '2018-04-03 03:03:03')
     , ('a',    'last',   'johns',   '2018-04-04 02:02:02')
     , ('b',    'last',   'johns',   '2018-04-03 12:12:12')
     , ('b',    'last',   'smith',   '2018-04-04 03:03:03')
     , ('c',    'last',   'smith',   '2018-04-03 11:11:11')
     , ('c',    'last',   'johns',   '2018-04-03 17:17:17')
     , ('c',    'last',   'jones',   '2018-04-05 01:01:01');

It might be easier to grasp your query if you instead of a left join uses a NOT EXISTS predicate (rows where it does not exists a row after ..., i.e. last row):

select id
from people a
where not exists (
    select 1
    from people b
    where a.id = b.id
      and a.name=b.name
      and a.ts < b.ts
)
and a.name = 'last'
and a.value = 'johns';

If you are interested in the situation before a certain point in time, add that predicate to both inner and outer select:

select id
from people a
where not exists (
    select 1
    from people b
    where a.id = b.id
      and a.name=b.name
      and a.ts < b.ts
      and b.ts < '2018-04-04 00:00:00'
)
and a.name = 'last'
and a.value = 'johns'
and a.ts < '2018-04-04 00:00:00';


At first glance, I would look over

innodb_buffer_pool_size
innodb_log_file_size
innodb_log_buffer_size

To properly judge the first two values, you should run this query:
SELECT CONCAT(ROUND(KBS/POWER(1024,
IF(PowerOf1024<0,0,IF(PowerOf1024>3,0,PowerOf1024)))+0.49999),
SUBSTR(' KMG',IF(PowerOf1024<0,0,
IF(PowerOf1024>3,0,PowerOf1024))+1,1)) recommended_innodb_buffer_pool_size
FROM (SELECT SUM(data_length+index_length) KBS FROM information_schema.tables
WHERE engine='InnoDB') A,
(SELECT 2 PowerOf1024) B;

This will tell you the ideal buffer pool size for the give dataset in MB. The innodb_buffer_pool_size should never be larger that 75% of installed RAM.
As for the log file size, I have two suggestions (pick one)
Suggestion 1: Set the innodb_log_file_size to be 25% of innodb_buffer_pool_size
Suggestion 2: If you want to set the log file size based on that actual amount of log file writes per hour, see my Aug 27, 2012 post Proper tuning for 30GB InnoDB table on server with 48GB RAM and my Feb 16, 2011 post How to safely change MySQL innodb variable 'innodb_log_file_size'?
As for innodb_log_buffer_size, you should max it out to 8M. The larger the innodb_log_buffer_size, the less flushing of transactions will occur.
EPILOGUE
Your original question was What's the most unsafe MySQL configuration?. The real answer to that is : a MySQL instance with no config file whatsoever !!! ;-)


SQL Server 2012 on a Windows 2008 R2 cluster, would like to upgrade Windows to 2012 R2

Here are the steps that I followed to get a Windows 2008 R2 cluster hosting SQL Server to Windows 2012 R2.

In this example we have a 2 node Active / Passive cluster running Windows Server 2008 R2 Enterprise hosting SQL Server 2012 SP2 with CU6, this will work with any version of SQL Server.

Step 1: Document the following:

Drive letter mapping to shared storage on each node, NTFS Shared folders, IP Addresses for LAN and Heartbeat on each node, Name and IP address of the Windows Virtual Cluster Node, Name and IP address of the SQL Virtual Cluster Node, SQL version, patch level, and features installed, The location of the System Databases

Step 2: In Failover Cluster Manager – Evict the passive node, in this example Node 2 is the passive node.

Step 3: Rebuild Node 2 with Windows Server 2012 R2, using the same server name and IP address. Verify that Node 2 can see the shared storage, but do not make them online at this point.

Step 4: Stop the SQL Services on Node 1.

Step 5: Locate the folder containing the system databases and move them to a secure location. Make sure you get all the system database including TempDB, MSDB, report server, and distribution if you have replication.

Step 6: Shutdown Node 1.

Step 7: In Active Directory Users and Computers disable the accounts for the Windows Cluster Node and SQL Cluster Node.

Step 8: On Node 2, Create the Windows Failover Cluster using the previous Windows Cluster Name and IP. At this point the shared drives will be present, verify they have the correct drive letters.

Step 9: Install SQL Server as a cluster using the same name, IP address, include all features required.

Step 10: Patch SQL Server to the correct SP and CU.

Step 11: Stop the SQL Services using SQL Server Configuration Manager.

Step 12: Copy the system database files that you saved from Step 5 over top the system database files from this new installation.

Step 13: Start the SQL Services using SQL Server Configuration Manager, verify all databases and logins are available.

Step 14: Install Windows Server 2012 R2 on Node 1 and join the Windows Failover Cluster.

Step 15: Install SQL Server as a new node to the SQL Cluster, patch to correct level.


SSRS appears to be ignoring Permissions set using Report Manager

To give a user rights to work with SSRS they need to have permissions at the root folder (which is what it sounds like you have done), but you also have to go into the "site settings" link into  the "security" tab and add the user as (at minimum) a system user.
It is easy to do one, but not the other. Both are required to allow full access to the system.


As suggested by Aaron Bertrand, renaming the files was just enough to solve this problem:

Save your backup files (or mdf/ldf files if you don't bother taking backups), and then obliterate that instance. Install a new instance (preferably of a newer version) and then restore/attach your databases to the new instance.
Try renaming that mdf file it's complaining about (and probably the corresponding ldf file, too). I haven't seen this issue before but it may take some trial and error for such an ancient, out-of-support version. Does your pen testing thing mention SQL Server 2008 (which could just indicate when it was written), or explicitly require it? Seems odd to require a version that is FIVE major versions ago, no longer supported, and quickly declining in use. I would double check that you even need to be dealing with this.


Instead of C:\SQLDBA\ServerName\Instance rather C:\SQLDBA\ServerName_Instance

Your script seems unduly complicated. All you need to do to format the folder name according to the title of your question is replace the backslash in @@SERVERNAME with an underscore. No need for the table, script file, and XML PATH for this task. PowerShell also includes a New-Item command to avoid the need for the legacy COM FileSystemObject.

DECLARE @PowershellCommand varchar(8000) = 'powershell -Command "New-Item -Path \"C:\SQLDBA\'
    + REPLACE(@@SERVERNAME, '\', '_')
    + '\" -ItemType \"directory\""';
EXEC sp_configure 'xp_cmdshell', 1;  --assumes 'show advanced' config option is already enabled
RECONFIGURE;
EXEC xp_cmdshell @PowershellCommand;
EXEC sp_configure 'xp_cmdshell', 0;
RECONFIGURE;

You could also avoid xp_cmdshell entirely and just run a stand-alone PowerShell script on the server:

New-Item -Path "C:\SQLDBA\ServerName_InstanceName" -ItemType "directory"


Adding windows domain group to ORA_DBA not working in Oracle 12c

Most likely you can't.

As part that oracle documentation is clear, you have to be part of the ORA_DBA group to have the sysdba privilege with your Windows account. Being in a subgroup means that you have all the privileges of the parent group without being part of it As the same logic of oracle privileges and roles.


The error message means that repmgr account can't login onto master server with provided password (probably empty one).

You need to allow connections on master, in pg_hba.conf:

host    all     repmgr  192.168.1.1/32       md5
host    replication     all     192.168.1.1/32       md5

then trigger a master server reload:

/usr/lib/postgresql/9.1/bin/pg_ctl reload -D /var/lib/postgresql/9.1/main

(where -D is the data directory of PostgreSQL).

Assumsing you've created a repmgr account, on master with some secret_passw0rd

createuser --login --superuser -P repmgr -W
createdb -O repmgr repmgr

then on standby server create a .pgpass file in postgres home:

su - postgres
cat > .pgpass << EOF
*:*:*:repmgr:secret_passw0rd
EOF
chmod 0600 .pgpass

That's all under assumption that you're using md5 password authentication for replication, if you switch to trust you could skip setting the password:

host    all     repmgr  192.168.1.1/32       trust
host    replication     all     192.168.1.1/32       trust

Now you can check connection to your master server:

psql -h master.hostname -U repmgr


Can you restore purged MySQL binary logs?

The binlogs contain a chronological list of the changes made to all the tables.  There is no "snapshot" in them, only insert, deleted, etc statements.

You can look at any one of the files with the mysqlbinlog program.

"get back from this bin single bin log file" -- What do you imagine you might want to get?