MySQL replication as both master and slave (replication loop?)

Terminology

What you're describing is a common set up that may be variously described as master-master replication, dual-master, active-active, bidirectional, or a few other terms.

Good reasons not to do it

The team who wrote High Performance MySQL offer this pithy summary of master-master replication - specifically, "active-active" replication:

  In general, allowing writes on both servers causes way more trouble than it’s worth.

Baron Schwartz, one of the authors, offers a slightly more blunt opinion on his blog.

  Save yourself grief, work, and money. Never write to both masters.

There are many things that can go wrong if both servers are updating data at the same time, including (but by no means limited to):

If something goes wrong with replication - even a temporary network glitch, you'll likely end up with a split brain and it may be impossible to determine which data is "correct"
If AUTO_INCREMENT MySQL settings are wrong, it's easy for data collision to take place
Row locking no longer offers any protection - data can be manipulated on one master even while a transaction has it locked on another

Good reasons to do it - carefully

The most common reason for master-master replication is to so that you can very quickly switch the "current" master if something goes wrong with one of your servers.

The common way to implement this is to set up a barrier that ensures that only one of your servers is every written to by your application servers. This is usually a hardware load balancer, or software load balancer like HAProxy.

The authors of High Performance MySQL (mentioned above) describe this as "active-passive" master-master replication.

As long as the barrier is robust, you are fairly well protected from all of the issues described above. This may be supported by setting read_only on the "passive" server for an extra layer of protection.

A well-built master-master setup can offer you some amazing flexibility - you can do some really complex table or server maintenance on your passive server, wait for replication to catch up, then migrate your load balancer over to point at your shiny, reconfigured environment with almost no service downtime. This sort of thing does need to be planned carefully, though, and a lot can go wrong.


select only one element from an priority ordered group with limits?

Without window functions, the query will be complicated. One way to solve the problem in MySQL:

SELECT
    n.*
FROM
    ( SELECT DISTINCT featureName
      FROM i18n
    ) AS d
  JOIN
    i18n AS n
  ON  n.id =
      ( SELECT i.id
        FROM i18n AS i
        WHERE i.featureName = d.featureName
          AND i.locale IN ('en', 'de', 'de_AT')
          AND i.priority <= 3
        ORDER BY i.priority DESC
        LIMIT 1
      )
ORDER BY
    n.featureName ;           -- or whatever you like, eg:  BY n.id

Also note that the above query will fail it is in SQL Server (which uses TOP, not LIMIT) and possibly fail in other DBMS as well.


Postgres value that never goes below zero

You can do this with BEFORE INSERT and BEFORE UPDATE triggers. Here is a tutorial.

These triggers can access the row being inserted (or the updated value) in the NEW variable. Do not use an UPDATE statement to change the row! Simply change the NEW variable, have the trigger function RETURN NEW, and the values will be used in the INSERT/UPDATE that fired the trigger. This only works in BEFORE triggers. AFTER INSERT/UPDATE cannot modify values since the operation is already done.

Code would look like this:

CREATE OR REPLACE FUNCTION mytrigger() RETURNS TRIGGER AS $example_table$
   BEGIN
      IF NEW.yourcolumn < 0 THEN NEW.yourcolumn := 0 END
      RETURN NEW;
   END;
$example_table$ LANGUAGE plpgsql;

Then use the CREATE TRIGGER command as mentioned in the tutorial.


Can a single database be excluded from an AG backup?

Select databases. The keywords SYSTEM_DATABASES, USER_DATABASES,
  ALL_DATABASES, and AVAILABILITY_GROUP_DATABASES are supported. The
  hyphen character (-) is used to exclude databases, and the percent
  character (%) is used for wildcard selection. All of these operations
  can be combined by using the comma (,).

Ola Documentation

So as you list the databases to back up, it will look like this:

AVAILABILITY_GROUP_DATABASES,-%Db%...

All databases like Db will be excluded from the backup while the rest of the databases in the availability group will be backed up. Change that Db to be the one you want to not backup and adjust the % signs as applicable to your database.


How do I open local files using heidiSQL?

(This part lifted from my answer here...)

The first thing to note is that an .sql file is not a MySQL database. It is a script file containing SQL statements. You would run such a script against the database platform for which it was written (using the SQL dialect for a specific SQL database engine) to create the actual database.

From comments to other answer:

  I was wondering if there is a way on Heidi or another program to do this without setting up a server.

No, you need a server somewhere. If there was any stand-alone tool that could "graphically manipulate" an .sql file (containing DDL, I presume) then it would have to have its own MySQL server embedded in it.


We recently added more options to the sp_BlitzIndex output, but the kind of housecleaning mode you're asking about isn't one of them, unfortunately.

The closest it gets to that is to run it with @Mode = 4 (as of v3.0) or @Mode = 0 (prior to v3.0), which I suspect you're already doing. The benefit to using the v3.0 is you can use the @GetAllDatabases = 1 parameter to collect all the unused/dupes for the whole SQL Server instance in one pass.

I suggest storing the output to a table and querying it for only the unused/dupes. Probably not the answer you were hoping for, but sp_BlitzIndex doesn't have that functionality built in.


Two mysql db's load the same data at two different rates

It looks like it must have been the zone (sub-region) that was the issue.  The slow loading happened when the EC2 container with the *.sql file was in us-east-1a and the RDS instance was in us-east-1b.


What are some good resources for learning about document oriented databases?

Here's a good article comparing document oriented databases:

http://www.mikeperham.com/2009/09/01/comparing-document-oriented-databases/

and here's a good article about designing document oriented databases:

http://ayende.com/Blog/archive/2009/03/08/designing-a-document-database.aspx


Custom calendar for calculating workdays per month

I'd start with a table that contains the date and some convenience columns plus a workday indicator.  (date full db date column, day, month, year, day_of_week, work_day Y/N field, isvacation, isweekday).

Populate that with values in the time range you're going to use.  Set appropriate flags (e.g. update date_table set isweekday where day_of_week between 1 and 5) and update all the work_day and isvacation flags by vacation that your company observes.  This is going to be a manual process, but not daunting.  Some holidays are the same day each year, some are not.  A vacation day could fall on the weekend, but be observed the previous Friday, etc.

Then you can calculate work hours pretty easily (e.g. select count(1)*8 from date_table where month = 1 and year = 2014 and work_day = 'Y'

Once the table is created, you can use it for lots of stuff.  Add a quarter field, first work day of the month field, day names, month names, etc..  Pretty handy, especially for reporting, just join on the truncated date column in your source data to the date field in the table.


Why can a partition bound expression not include a casting operation?

You must be using PostgreSQL v11 or older.
Back then, it could only be a literal:

FROM ( { numeric_literal | string_literal | TRUE | FALSE | MINVALUE | MAXVALUE } [, ...] )

See the documentation.


Query Plans are flushed from the cache for several reasons, including being aged out, being flushed due to memory pressure, flushed due to user action (DBCC FREEPROCCACHE etc), flushed due to restart and flushed due to explicit recompilation (OPTION (RECOMPILE or sp_recompile).

If you can see no evidence of forced recompilation or a manual flush, then it is most likely the plan was aged out or it was flushed due to memory pressure. From Docs: Plan Cache Internals:

  Evicting plans from cache is based on their cost. For adhoc plans, the
  cost is considered to be zero, but it is increased by one every time
  the plan is reused. For other types of plans, the cost is a measure of
  the resources required to produce the plan. When one of these plans is
  reused, the cost is reset to the original cost. For non–adhoc queries,
  the cost is measured in units called ticks, with a maximum of 31. The
  cost is based on three factors: I/O, context switches, and memory.
  Each has its own maximum within the 31-tick total.

  I/O: each I/O costs 1 tick, with a maximum of 19.
  Context switches: 1 tick each with a maximum of 8.
  Memory: 1 tick per 16 pages, with a maximum of 4.

  When not under memory pressure, costs are not decreased until the
  total size of all plans cached reaches 50 percent of the buffer pool
  size. At that point, the next plan access will decrement the cost in
  ticks of all plans by 1. Once memory pressure is encountered, then SQL
  Server will start a dedicated resource monitor thread to decrement the
  cost of either plan objects in one particular cache (for local
  pressure) or all plan cache objects (for global pressure).

Take a look at this StackExchange answer which provides heaps of great links and information on plan reuse and caching. You'll have to put in place monitoring to catch the exact cause if this reoccurs, but you should also look at leveraging Query Store which will allow you to force a known good plan for this query and track regressions across your database as this may be affecting other queries as well.


Joining two tables based on two Aggregate columns

Something like this:
with
ta as (select a.customerid, sum(a.items) amount_a
       from orders a
       group by a.customerid
      ),
tb as (select sum(b.items) amount_b
       from tableb b
      )
select ta.customerid, ta.amount_a, tb.amount_b
from ta join tb on ta.amount_a = tb.amount_b


MySQL date range if data not available display 0

You should use a RECURSIVE CTE  available in Mysql8.0 and above.

WITH RECURSIVE abc AS (
  select CAST('2020-01-01' as DATE) as a
  union all
  select ADDDATE(a,interval 1 day)
  from abc where a<'2020-01-31')
SELECT * FROM abc;

Above will output all dates between '2020-01-01' and '2020-01-31'.

If you join this to your query, you should see 0 for dates that have no value in your table.

WITH RECURSIVE abc AS (
  select CAST('2020-01-01' as DATE) as a
  union all
  select ADDDATE(a,interval 1 day)
  from abc where a<'2020-01-31')
select SUM(x.jumlah), x.tanggal  from (
select
 count(id) as jumlah,
 tanggal
from tb_trafik_berita
WHERE (tanggal BETWEEN '2020-01-02 00:00:00' AND '2020-01-07 23:59:00')
group by tanggal
union all
select 0,a from abc
) x
GROUP BY x.tanggal


How can Cumulative Updates to SQL Server 2019 be Slipstreamed into a Single Installer?

You can't do this, unfortunately.
As I wrote in my blog post here, you must:

Download and extract the installation media to a directory;
Add the patches to an updates subdirectory (yep, all of them).

As you've discovered, you use the /UpdateEnabled and /UpdateSource switches when it comes to doing the installation.
The one small mercy is that CUs are cumulative, so you only need the latest one in the updates subdirectory.


Constraining inserts/updates for summary tables with complex validation logic

That sort of validation is not a candidate for a column or table level Constraint. You have two options depending on how you're interacting with this database:

Validation is done via the 'front end' interface, it won't let you make 'bad' picks. You can then validate them again within a SQL query to double check if so desired.
Validation only occurs within a SQL query, allowing the 'front end' to pass along potentially 'bad' data.

If you choose to validate in the SQL query you can either build explicit checks using typical IF THEN iterative logic or build it into your INSERT queries. To do the latter your tables need to contain the data in a relationship that mimics you validation scheme, which luckily it looks like your tables do. Something like the following should do what you're looking for (@ denotes variables, I'm assuming your passing those in or setting them ahead of time):

INSERT INTO Tests (student_id, quarter_id, class_id, test_date, test_grade)
SELECT student_id
       ,quarter_id
       ,class_id
       ,test_date
       ,test_grade
FROM (SELECT @student_id AS student_id
             ,@quarter_id AS quarter_id
             ,@class_id AS class_id
             ,@test_date AS test_date
             ,@test_grade AS test_grade ) AS InsertSet
        INNER JOIN quarters Q
            ON InsertSet.quarter_id = Q.quarter_ID
               AND InsertSet.student_ID = Q.student_ID
        INNER JOIN classes_to_courseloads CTC
            ON Q.courseload_ID = CTC.coarseload_ID
               AND InsertSet.class_id = CTC.class_id

By turning the variable set into an inline table we can join against the other tables to insure the data is valid. We INNER JOIN to the Quarters table to insure the quarter_ID and student_ID match up, thus verifying he is enrolled for the quarter. We then INNER JOIN onto Classes_to_Courseloads to verify that the class_ID the test grade is for matches up with his courseLoad_ID the student_ID was signed up for in the Quarters table. If any of the JOINs in that query fail you'll get no results and thus won't INSERT anything into the Tests table.

As for throwing back a message or error you can run a SELECT query against Tests using the variables to try and return the test_ID, if you get a result the INSERT was a success, if you don't it failed.


Need some help for choosing the replication type

In the simple view, this pretty much looks like Transactional Replication from {Publisher1, Publisher2, Publisher3} to Subscriber.

I assume that Subscriber includes the Staging DB.  Therefore, as the Staging DB is updated it can, in turn, become a publisher using Transactional Replication to {Subscriber1, ..., Subscriber5}.

Of course, depending on the way data is maintained, you might need to use Merge Replication instead.

Paul Ibison has a brief article that discussed the differences in republishing with Transactional or Merge Replication at: http://www.replicationanswers.com/Republishing2005.asp

The big issue is being sure that you clearly understand which sources of data are authoritative, how the data flows (and what happens to it in the Staging DB), et cetera.


How do I list all tables in all schemas owned by the current user in Postgresql?

This will list all tables the current user has access to, not only those that are owned by the current user:

select *
from information_schema.tables
where table_schema not in ('pg_catalog', 'information_schema')
and table_schema not like 'pg_toast%'

(I'm not entirely sure the not like 'pg_toast%' is actually needed though.)

I you really need the owner information, you probably need to use pg_class and related tables.

Edit: this is the query that includes the owner information:

select nsp.nspname as object_schema,
       cls.relname as object_name,
       rol.rolname as owner,
       case cls.relkind
         when 'r' then 'TABLE'
         when 'm' then 'MATERIALIZED_VIEW'
         when 'i' then 'INDEX'
         when 'S' then 'SEQUENCE'
         when 'v' then 'VIEW'
         when 'c' then 'TYPE'
         else cls.relkind::text
       end as object_type
from pg_class cls
  join pg_roles rol on rol.oid = cls.relowner
  join pg_namespace nsp on nsp.oid = cls.relnamespace
where nsp.nspname not in ('information_schema', 'pg_catalog')
  and nsp.nspname not like 'pg_toast%'
  and rol.rolname = current_user  --- remove this if you want to see all objects
order by nsp.nspname, cls.relname;


The problem ended up being some obscure bug in Bash. I was calling psql from a Bash script, which was being called from another Bash script, which was being run from a Cron job, which was redirecting all output to a log file. When I changed it so that Cron directly ran the psql call, and redirected the output, the "hanging" stopped.


How to change MySql Master-Master Replication to Master-Slave Replication

On a basic level, what you have done is enough, but there is a risk it could be re-enabled by mistake as long as the Binary logs exist on master2.
So, if this is just a temporary thing then running RESET SLAVE will remove the master_log_file and read_master_log_pos values, thus preventing replication from starting again by mistake, but maintaining the other values for future use.
If this is going to be permanent, then RESET SLAVE ALL will remove all the replication information (include host, port, user, password etc).


Steps or evaluation order of the SQL statement

Following is the logical order of execution of the SQL clauses:

  FROM clause
  WHERE clause
  GROUP BY clause
  HAVING clause
  SELECT clause
  ORDER BY clause


Right Tool for Data Mapping in Relation to Business Intelligence

As with your other questions, this one could be more specific.  However, I will assume that you want to document mappings arising from analysis work.

An ETL tool will actually load the data, and the mappings tend to take the form of wiring loom diagrams that show the mappings graphically.  If you have relatively simple transformations the internal self-documenting capabilities of the ETL tool may suffice for this.

However, this tends to break down on more complex transformations or in situations where not all of the logic is done within the tool.  The more complex the transformation the more likely it is to happen.  In many cases a decisions is made to do all the transformation work in stored procedure code and the ETL tooling is largely used for extraction, logging and load control tasks.

If you are mapping for documentation or specification purposes, people often use spreadsheets if no other tool is available.  However, this really carries an assumption of an isomorphic 1:1 mapping from source to target and does a poor job of capturing and documenting complex logic.

Some CASE tools have meta models for mapping available.  I've done this with Sparx Enterprise Architect, although we had to build a custom metamodel for this.  Some tools have this feature out of the box.

Finally (and this is my preferred approach) a functional specification for the ETL can be written up as a technical document.  This can describe mappings and detailed specifications for the logic.  In the case of a system with complex logic in the ETL a spec document is by far the best way to do this.


3NF decomposition + database normalization

Every textbook I've ever seen gives you one or more algorithms you can use to determine every possible candidate key for R{ABCDEFG}. (It can be a fairly laborious and time-consuming job.) Based on the FDs you provided, I think R{ABCDEFG} has only one candidate key, {AC}.

Loosely speaking, for a relation to be in 3NF, it must be in 2NF and have no transitive dependencies. For a relation to be in 2NF, it must be in 1NF and have no partial key dependencies.

The only candidate key here is {AC}.
From A->DE we know that DE is dependent on A, which is only part of the candidate key.

Therefore, R{ABCDEFG} is not in 2NF (or 3NF) based on the FDs you provided.


Your client should use the QUOTENAME function, if not, here is an example:

DECLARE @schemaname sysname = 'dbo';
DECLARE @objectname sysname='-mySuperTableName';
DECLARE @indexname sysname = 'ix1';
DECLARE @SQLCommand AS nvarchar(3000)

  SELECT
    @SQLCommand = 'Alter Index ' + @indexname + ' ON ' + @schemaname + '.' + @objectname + ' REBUILD;'
    print  @SQLCommand

Try to execute this code and the command you received is Alter Index ix1 ON dbo.-mySuperTableName REBUILD; and it definitely gives you an error

  Incorrect syntax near '-'.

if you try to execute the command


What is the point of encryption at rest?

The file_key_management is largely a testing plugin. Its there to show the general operation and be there as self tests. Its also there as a simple reference implementation if you want to write your own.
You are right about every weakness described.
There may be container based applications where the storage of keys used by the file_key_management plugin are only brought together when the MariaDB pod is instigated. With this there may be some risk vectors mitigated.
There are a number of other encryption implementations using the same server API that offer greater protection.


Missing FROM-clause in RETURN QUERY EXECUTE FORMAT loop

It turns out that I simply needed to quote the table name passed to the function:
RETURN QUERY EXECUTE FORMAT('SELECT %L as metric_name, hypertable_size(''prom_data.%I'') as raw_size', metric_table, metric_table);


When you're installing MySQL you're asked to enter the root password that you want to set for the database. In the status code you've listed above you're logging in as charles@localhost and not root. That's the reason why it's not asking for the password you set during installation.

If you're logging in to MySQL using the root account then you can do all the things you want.

Simply type:

mysql -p -u root

and it'll ask you for the root password. Now you're "in" the system with full access. You can then create databases using:

CREATE DATABASE MyNewDatabase;

If you have a user that you want to give access to this new database you can type:

GRANT ALL ON MyNewDatabase.* TO 'charles'@'localhost';

When this is done you should be able to login as charles and also have full access to the newly created database.


update/delete records concurrently without impact to other process for handling huge data?

If you can apply partitioning to the table(s) in question you can swap out whole partitions then drop the swapped partitions.  This is extremely fast and resource-light.

It requires minimum versions and editions, however, and your question's tags don't specify this.  It also imposes some prerequesites on your tables' keys.


This line seems to be causing the problem

`date_art` datetime(6) DEFAULT NULL,

This works

`date_art` datetime DEFAULT NULL,

Is the version of the database you exported from the same as you are importing to?

https://stackoverflow.com/questions/2572209/why-doesnt-mysql-support-millisecond-microsecond-precision

http://dev.mysql.com/doc/refman/5.6/en/fractional-seconds.html


Cannot connect to a SQL Server 2014 Express database intermittently

Finally, it seems to have turned the problem: an IP in the secondary DNS configuration caused the problem.
Removing the IP, everything works correctly again.


Why does backup file size become enormous when Agent schedule is changed?

You don't indicate how often your provider issues BACKUP LOG commands (which I assume are not COPY_ONLY), but here is my guess at to what is happening.

Let's assume a 'regular' transaction log backup has occurred at 8am which truncates the log.  Also, let's assume that 1gb of transaction log is being created every 15 minutes.

8:00am - Your BACKUP LOG COPY_ONLY runs and doesn't really backup
anything because the log has just been truncated by the normal log
backup.
8:15am - Your BACKUP LOG COPY_ONLY runs and backs up up everything
since 8:00am  (because that's when the log was last truncated). Let's say 1gb.
8:30am - Your BACKUP LOG COPY_ONLY  runs and backs up everything
since 8:00am (because that's when the log was last truncated) - now 2gb.
8:45am - Your BACKUP LOG COPY_ONLY  runs and backs up everything
since 8:00am (because that's when the log was last truncated) - now 3gb.
9:00am - Your BACKUP LOG COPY_ONLY  runs and backs up everything
since 8:00am (because that's when the log was last truncated) - now 4gb

You are also 'appending' this data to your existing backup because you are using NOINIT.


How do I resolve Postgresql error, 'no collation was derived for column "foo" with collatable type citext'?

For more information on this find our correspondence on this thread:

Thread: http://archives.postgresql.org/pgsql-general/2012-05/msg00262.php

Problem

The problem comes right from the horses mouth, Tom Lane,

  Well, if that's how you're creating citext *, then yeah it's broken.
  As of 9.1 the citext type needs to be created with the attribute
  "COLLATABLE = true".  The suggested UPDATE statements are a means
  of correcting a failure to do that after-the-fact, but they don't
  cover any domains that have already been created on top of citext.

Source: http://archives.postgresql.org/pgsql-general/2012-05/msg00262.php

* I was creating citext however my specific dump had it created, there was nothing special.

So a dump/restore from 8.3 -> 9.1 is not supported if you've got a domain over citext.

Solution

You can get around this problem by first doing:

CREATE EXTENSION citext before you load the dump.
or, by hacking the patch to operate on DOMAINS over citext.


Which Indexes in which situations is more efficient in SQL Server 2012

I think you are already on the right track here - and I encourage you to keep experimenting with different options and see for yourself what the differences to performance are. But, here are my thoughts below:

Columnstore Indexes
These perform really well when you are genuinely interested in all (or most) of the data in a column. If you are keen to see the distribution of data in a column, e.g. number of sales per order or location data, then a column store is a highly performant way to access the whole column.

Writing to columnstore indexes is slower than writing to a row. I would not use a columnstore index if you have a high-velocity OLTP system. Would also avoid columnstore indexes if you are interested in filtering down to small result sets.

Covering Indexes
Use these when you have wide tables and queries that regularly access a small number of the columns in the table. Covering indexes are a superb way to remove clustered index scans, reduce your overall IO and improve concurrency.

Covering indexes over all the columns of a table
Never. I cannot see a good reason to do this. Am happy to be corrected though.
Perhaps... maybe... still not convinced... you might do this if you are implementing something like a filtered index. This would essentially chunk you table into discrete ranges. But I am not convinced this is a good method.


Ubuntu / MySQL / Percona XtraBackup gives error "sh: 1: xtrabackup_55: not found"

Check your server bit if it is 32 bit and if you try to install 64bit package percona's xtrabackup . It would end up throwing such error messages.


How to change PostgreSQL IP address for streaming replication

This is not the question about PostgreSQL - this is about routing. Just route from 192.168.1.17 became as better than from 192.168.2.12


Percentage calculation with case not working as expected

Quick-n-dirty dual/CTE solution:

-- sum up the fico groups

with fico_groups as
(select   fundingyear,
          case  when fico <  620                 then '0-619'
                when fico >= 620 and fico <= 679 then '620-679'
                when fico >= 680 and fico <= 719 then '680-719'
                when fico >= 720                 then '720+'
          end as fico_txt,
          count(fico) as count_fico
 from     fico_scores
 group by fundingyear,
          case  when fico <  620                 then '0-619'
                when fico >= 620 and fico <= 679 then '620-679'
                when fico >= 680 and fico <= 719 then '680-719'
                when fico >= 720                 then '720+'
          end
),

-- get the yearly totals

fico_totals as
(select   fundingyear,
          sum(count_fico) as fico_total
 from     fico_groups
 group by fundingyear
)

-- final result set

select fg.fundingyear,
       fg.fico_txt                           as fico,
       fg.count_fico,
       (fg.count_fico *1.0 / ft.fico_total)  as fico_perc

from   fico_groups fg,
       fico_totals ft

where  fg.fundingyear = ft.fundingyear

order by fg.fundingyear, fg.fico_txt

Result set (based on sample data provided by OP):

fundingyear   fico      count_fico  fico_perc
2017          0-619     1           0,076923076923
2017          620-679   4           0,307692307692
2017          680-719   4           0,307692307692
2017          720+      4           0,307692307692
2018          0-619     1           0,090909090909
2018          620-679   2           0,181818181818
2018          680-719   3           0,272727272727
2018          720+      5           0,454545454545

Here's a db fiddle of the above.


Does the table constraint CHECK gets executed on deletions?

Have you tried ON DELETE RESTRICT ? You can combine this constraint with a function attached to the foreign tables CREATE TRIGGER ON DELETE

CREATE TABLE my_table (
  foreign_key_id uuid REFERENCES my_foreign_table ON DELETE RESTRICT
);

CREATE OR REPLACE FUNCTION foreign_object_delete()
RETURNS TRIGGER AS $$
BEGIN
    DELETE FROM my_table
    WHERE foreign_key_id IS NULL
    AND foreign_key_id = OLD.foreign_key_id;
    RETURN OLD;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER foreign_row_trigger BEFORE DELETE ON my_foreign_table
    FOR EACH ROW EXECUTE PROCEDURE foreign_object_delete();

Update:
Check a condition in several tables before deleting the record

CREATE OR REPLACE FUNCTION foreign_object_delete()
RETURNS TRIGGER AS $$
BEGIN
   IF SELECT exists(SELECT 1 FROM table_x WHERE column_x = 'SAMPLE') THEN
      DELETE FROM my_table
      WHERE foreign_key_id IS NULL
      AND foreign_key_id = OLD.foreign_key_id;
   END IF;
      RETURN OLD;
END;
$$ LANGUAGE plpgsql;


ETL, Production, Development -- Should these be different servers? Instances?

There are probably a hundred reasons if we think about it really hard, but there are really just a few reasons that are so important that we never bother thinking of others:

Resources:  If you haven't had a situation yet were someone did something on a non-production server that sucked up all the memory, CPU, or I/O, you will eventually have that if you continue to work in IT long enough.  You don't want this happening in production.  Yeah, the developers won't do any testing at night when the production ETL processes are running--until they are up against a deadline.  Even if you use resource governing, separate instances--whatever, again, once you've been around long enough (my apologies if you feel that you have), you'll still not want them running on the same box.
Security: Ideally you want to have Active Directory groups for any access that doesn't use a SQL login.  If your development and production are on the same instance, then all of the developers have some access to the production instance.  Ideally you want to allow only as few people as possible the ability to even log in to the production instance
Changes: You are limited to what changes can be tested if all environments use the same system.  Can't test a new service pack or cumulative update if everything's on the same instance.
The Future: If you have separate systems for each environment, you will be much more likely to be able to accommodate future needs.  For example, isolating non-production systems from production systems is becoming A Thing for security purposes.  So workstations can talk to dev and prod, but dev and prod servers are prohibited from communicating.  Can't do this if it's all on the same system.
Reduced Probability of Accidents: If you have "dev" in the name of every server, database, SQL login, etc. that is in the development environment, you are much less likely to have someone accidentally run something in production.  Lots of horror stories out there when credentials were the same name and then it just took a server name typo to bring down production.

Using multiple instances would certainly be a step in the right direction, but you still have risk of resource contention, inability to test impact of OS updates, security changes to OS, different driver versions, etc.

The same principles apply as to whether to run the SSIS jobs on the same server.  Ideally, you would have a separate SSIS server so that you avoid the resource contention--you seem to have a good grasp of that.  Whether it's worth it for you organization to pay for the additional hardware and software licenses--no way for us to tell.  Present it to management, and if they decline then you are covered if things go badly due to too many things running on one system.


Failed to start of service MongoDB community4.2 using Homebrew in Mac OS Mojave 10.14

use code below before mongo

sudo brew services start mongodb-community


SQL Server differential backup LSN values

why would the FirstLSN of the second differential backup
  (22000000041800004) be higher than the FirstLSN of the first
  differential backup (22000000039800037)?

When dealing with Data (Full, file, etc) or differential version of said backups the FirstLSN and LastLSN actually mean something different than is being inferred.

FirstLSN when dealing with the differentials means that's the LSN which is needed in order start recovery with that backup (meaning that's where it's going to start the process, at that lsn).

LastLSN when dealing with the differentials means that's the LSN at which any subsequent restore operations (in this case, your log backups) need to start. If you wanted to apply any extra logs, for example, the log backup that can first be applied needs to hold the LastLSN value in it in order to be applied.

However, this definition changes when dealing with the log backups which would be your original thought on the matter.

Edit: Just found this, which may help.

Edit: Updated question so updating answer.

  So if I am going to recover the database using the second differential
  backup, wouldn't it stand to reason that the second differential
  backup's FirstLSN would start where the full backup left off
  (22000000038800001)?

It's a differential, so it only holds what little log it needs to be transactionally consistent. It wouldn't have to be that, as that's not the firstLSN where it is starting recovery. This differential could be from a full backup base taken 1.5 years ago (please, please don't let it go that long between fulls) and would not have any LSNs in most likely the last 1.5 years in it. It'll have all of the changed extents, but no log transactions (again, it'll have a tiny amount to be consistent). It's just telling you that it's going to start it's recovery process at that LSN.

  Restoring the second differential would apply all the transactions
  that occurred after the full backup was created wouldn't it?

It would restore ALL of the extents that were changed since the last full backup. These may or may not reflect actual changes. For example, a large insert statement that put 1000 rows into an empty table that was subsequently rolled back. The extents allocated to that table, while nothing would be in there since it was rolled back, will still be apart of the differential as they were changed. All the differential does (well, not all but mostly) is look a the DCM and backup those extents, figure out how much log it needs and presto you have a differential backup. So it won't apply the transactions (as in replaying them) but it will hold whatever their consequences are.

  I'm not understanding how the system accounts for the LSN gap between
  the full backup and the second differential.

It's not really a gap, it holds everything that has changed since that time (not as transactions but as what extents were changed) and then a little log. The "gap" is the changed extents part and what "makes up for it" is the little bit of log in the differential backup to make it consistent.

  But why do full and differential backups need to store the LSN values?

They need to know information about themselves as well. For example, assume that a full backup was lost but you have a differential based on the newer full backup and you have an older full backup. You could restore the original old backup, logs, then the differential when you finally got to where it needs to be and continue on... but how would you know where that needed to be if you didn't store the values somewhere? How would we as admins know what order items came in if we didn't have these values?


From what I have seen reaching a High Water Mark means approaching some established limit. There are multiple "high water marks" in DB2 depending on which area you are talking about. Some relate to memory used for sorting/joining. Others relate to maximum agents allowed or differing heap sizes, or even package caching.

I'd recommend taking a look at this article on developerworks. Though it was written for an older version of DB2 (8.1), it still holds valuable nuggets for what parameters to tune when to get what you are looking for. I recommend taking a peek at it. If you search for high water mark on the page you find all the different instances I was referring to and which parameters tune them.

Also, take a look to see if you are running any hash joins and how many. This article is also helpful for understanding which parameters can be tuned that affect hash joins and certain usages of memory.


Designing a Multiple Reflexive Relationship

I'll answer for an RDBMS, like PostgreSQL. Here we use a many-to-many table.

CREATE TYPE device_type
  AS ENUM ( 'SERVER', 'STORAGE', 'SWITCH' );

CREATE TABLE devices (
  id   serial         PRIMARY KEY,
  type device_type
);

CREATE TABLE device_map (
  id_device   int REFERENCES devices,
  id_parent   int REFERENCES devices,
  PRIMARY KEY (id_device, id_parent)
);

INSERT INTO devices(id,type)
VALUES
  (1,'SERVER'),(2,'SERVER'),
  (3,'STORAGE'),(4,'STORAGE'),(5,'STORAGE'),
  (6,'SWITCH');

-- Everything the first and second server is connected to.
INSERT INTO device_map(id_device,id_parent)
VALUES
  (1,2),(1,3),(1,6),
  (2,1),(2,3),(2,6),(2,4),(2,5);

Now I think you want something like this.

SELECT id_device, d1.type AS device, id_parent, d2.type AS parent
FROM device_map JOIN devices AS d1 ON id_device = d1.id
JOIN devices AS d2 ON id_parent = d2.id
ORDER BY id_device, d2.type = 'SERVER' DESC;
 id_device | device | id_parent | parent
-----------+--------+-----------+---------
         1 | SERVER |         2 | SERVER
         1 | SERVER |         3 | STORAGE
         1 | SERVER |         6 | SWITCH
         2 | SERVER |         1 | SERVER
         2 | SERVER |         3 | STORAGE
         2 | SERVER |         6 | SWITCH
         2 | SERVER |         4 | STORAGE
         2 | SERVER |         5 | STORAGE
(8 rows)

That models this schema, assuming simply that non-servers can only be connected to servers.


How to inspect global temp table from outside the transaction it is created in

File this under "just because you can, doesn't mean you should"

If you generate a bind token in the first session, and somehow publish it, you can join its transaction from another session.

Eg from spid 61:

if @@TRANCOUNT > 0 rollback
go
begin transaction

select *
into ##t
from sys.objects

declare @bind_token varchar(255);
exec sp_getbindtoken @bind_token output
declare @bind_token_bin as varbinary(128) = cast(@bind_token as varbinary(128))
set context_info @bind_token_bin

--rollback

then from another session

declare @bind_token varchar(255) = (
    select cast(context_info as varchar(255))
    from sys.dm_exec_sessions
    where session_id = 61
)

exec sp_bindsession @bind_token
go
select * from ##t


From this listing it looks like there is a package named postgresql-9.2-postgis2.  You may have to add a new apt repository to the existing ones.  In this, you can find help here.


Use another CTE for the second INSERT and SELECT DISTINCT user_id FROM that CTE. In the new CTE do an INSERT ... SELECT FROM the old CTE.

WITH inserted_user
AS
(
INSERT INTO "user"
            (username,
             password,
             nickname,
             account_non_expired,
             account_non_locked,
             credentials_non_expired,
             enabled)
            VALUES ('username',
                    'password-hash',
                    'nickanme',
                    true,
                    true,
                    true,
                    true)
            RETURNING id
),
inserted_user_role
AS
(
INSERT INTO user_role
            SELECT iu.id,
                   gs.n
                   FROM inserted_user iu
                        CROSS JOIN generate_series(2, 4) gs(n)
            RETURNING user_id
)
SELECT DISTINCT
       user_id
       FROM inserted_user_role;


Best way to prevent deadlocks and server locking for single row Insert/Update transaction?

I suggest one handle only errors that are expected and unavoidable. It would be better to address the root cause of errors rather than introduce the complexity of a retry work-around.

In this case (singleton update by primary key), the 1205 deadlock error is unlikely because only a single row is involved. The 1222 error will occur only with a non-default LOCK_TIMEOUT setting, which doesn't appear to be the case here. The 1204 error should not happen on a healthy server.

Command timeout errors cannot be caught in T-SQL. Command timeouts occur on the client side when the client API cancels the query, waiting up to the specified CommandTimeout (30 seconds by default with most APIs). No subsequent statements in the T-SQL batch will execute after cancelation, including the CATCH block.
A consideration is that an explict transaction started in the proc will remain open after a timeout (until the application closes or reuses the pooled connection). For this reason, I strongly recommend one specify SET XACT_ABORT ON in stored procedures with explict an BEGIN TRANSACTION so that transactions are rolled back immediately following a client timeout. There is no need for an explict transaction for this singleton row update proc, though, since autocommit will guaranteed atomic all-or-none behavior.

You might want to add an optimistic concurrency check so that users don't accidentally overwrite one another's changes. Whether or not this is worth the effort depends on the business impact of such a scenario and the likelihood of it happening. An optimistic concurrency check can be accomplished by either comparing each column value with the value originally retrieved or by adding a rowversion column to the table to simplify the task. Here's an optimistic concurrency example using a rowversion column. The app code would retrieve the rowversion when presenting data to the user and provide that value when updating the row.

ALTER TABLE dbo.Building
    ADD RowVersion rowversion NOT NULL;
GO

CREATE PROCEDURE [dbo].[UpdateBuilding]
    @Status BIT = NULL,
    @Name VARCHAR(50) = NULL,
    @Code CHAR(2) = NULL,
    @ActionID UNIQUEIDENTIFIER = NULL,
    @OriginalRowVersion ROWVERSION
AS
UPDATE dbo.Building
SET Status = @Status,
    Name = @Name,
    Code = @Code,
    ActionDt = SYSDATETIME(),
    ActionID = @ActionID
WHERE
    Code = @Code
    AND RowVersion = @OriginalRowVersion;
IF @@ROWCOUNT = 0
BEGIN
    RAISERROR('Building with code %s was modified or deleted by another user.', 16, 1, @Code);
END;
GO

Regarding the INSERT proc, it seems primary key violations are unlikely given the 2 character Code primary key and presumably small user base. You could just use a single statement stored procedure (autocommit) and handle PK violations errors on the applciation side with a user-freindly message.


optimize SELECT query with CASE statements and JOINs

Moved from a comment because getting lengthy:
I think if your query is executing in only 4ms currently you're fine. The relationship between data growth and execution time is not linear, so if your data even grew 10x what it currently is and you re-ran your query, it'll probably still run in under 10ms, roughly speaking. I don't see much issues in your query itself as long as your indexing everything properly.
The only recommendation I could make is to avoid sub-queries when not necessary, especially as part of predicates, such as in your WHERE clause. This can lead to slight inefficiencies, and there's a better relational alternative by just doing an INNER JOIN to the followers table from your subquery on the user_id = following_user_id field. You may never run into a performance issue as you currently wrote it, but just a heads up a better alternative exists.
Example:
SELECT
  a.post_id::int,
  a.repost_id::int,
  CASE WHEN a.repost_id IS NULL THEN a.text ELSE b.text END,
  CASE WHEN a.repost_id IS NULL THEN a.media ELSE b.media END,
  CASE WHEN a.repost_id IS NULL THEN a.timestamp ELSE b.timestamp END,
  c.username,
  d.username as original_username
FROM
  posts a
INNER JOIN followers f
    ON a.user_id = f.following_user_id
    AND f.user_id = $1
LEFT JOIN
  posts b
      ON a.repost_id = b.post_id
JOIN
  users c
      ON a.user_id = c.user_id
LEFT JOIN
  users d
      ON b.user_id = d.user_id
WHERE
  post_id < $2
ORDER BY
  post_id DESC
LIMIT
  10


Backup and Restore Oracle database in Azure

As far as I know, Oracle databases in Azure are not provided as DBaaS, you get OS access, so it does not really affect the choice.

RMAN is efficient when working with large databases, while Data Pump is trouble-free and is easier to manage for someone not familiar with DBA tasks. A 260 GB database is not big, I would just export-import the database.

To restore an RMAN backup, you need the same platform + the same or higher software version. If your software version is higher, you need to immediately upgrade the database after restoring it.

With Data Pump you can just import the data directly to a higher version database, even on a different platform. Loading data with Data Pump is usually fast. The slow parts are creating indexes and validating constraints. This is a one-time operation, it should not affect performance in a negative way. In fact, it reorganizes tables and indexes, it may even improve the performance depending on their current state.


Oracle 11G - Index Rebuild VS GATHER_TABLE_STATS

Gathering statistics and rebuilding indexes are two completely separate things.

It is exceedingly rare that an index in Oracle needs to be rebuilt so any process that is regularly rebuilding an index is highly suspect.  I strongly suspect that rebuilding the indexes is unnecessary.  And I would strongly suggest spending some quality time reading through Richard Foote's Index Internals - Rebuilding the Truth to understand why rebuilding indexes is so rarely needed and those few cases where it is necessary.
You do need to gather statistics on tables and indexes when there are substantial changes in data volumes or distributions in order to give the optimizer the information it needs in order to determine the appropriate query plan.  If you create an index on an empty table and subsequently insert millions of rows into the table, you'll need to gather statistics to tell the optimizer that the table is no longer 1 empty extent.  Unless you disabled the automatic job, Oracle will also automatically gather statistics on any objects that have changed substantially since the last time statistics were gathered during the early morning hours every day.


Number of connection running within certain period of time?

Something like this might do the trick, assuming your mysqld is listening on port 3306:

$ netstat -ant | grep ":3306" | grep ESTABLISHED | wc -l
50

You can put this in a script and execute every 10 minutes through cron.


SQL Server 2016 stretch database - namespace error

I had the same thing happening to me and my students during a SQL Server 2016 hands-on lab a couple of months ago. It is a weird error that shouldn't happen (it's definitely a bug that you should report).

However, there's a workaround: create an Azure SQL Database manually, then delete it. From that moment on, you will no longer receive the error message.


Postgres SQL - Compare Count of rows, returning where a < b or b doesn't exist

You can get COUNT of each tag by using next query;

SELECT hash,
       SUM(CASE WHEN val ILIKE 'dht\_%' THEN 1 ELSE 0 END) dht,
       SUM(CASE WHEN val ILIKE 'thumb_%' THEN 1 ELSE 0 END) thumb
FROM   tags
GROUP BY hash;

hash             | dht | thumb
:--------------- | --: | ----:
8dbb1085750696c5 |   3 |     4
b119ceb321058d7c |   0 |     6

Then apply filter conditions:

WITH CTG AS
(
    SELECT hash,
           SUM(CASE WHEN val ILIKE 'dht\_%' THEN 1 ELSE 0 END) dht,
           SUM(CASE WHEN val ILIKE 'thumb_%' THEN 1 ELSE 0 END) thumb
    FROM   tags
    GROUP BY hash
)
SELECT hash, dht, thumb
FROM   CTG
WHERE  (thumb > dht)
OR     (thumb >= 1 AND dht = 0);

hash             | dht | thumb
:--------------- | --: | ----:
8dbb1085750696c5 |   3 |     4
b119ceb321058d7c |   0 |     6

The condition can be further simplified to:

WHERE  (thumb > dht) ;

And it might be more efficient as a subquery (derived table) than a CTE, if the rows (that are produced by the CTE/derived table) are very many:

SELECT hash, dht, thumb
FROM
(
    SELECT hash,
           SUM(CASE WHEN val ILIKE 'dht\_%' THEN 1 ELSE 0 END) dht,
           SUM(CASE WHEN val ILIKE 'thumb_%' THEN 1 ELSE 0 END) thumb
    FROM   tags
    GROUP BY hash
)
    AS CTG
WHERE  (thumb > dht) ;

dbfiddle here


Unable to query SQL server metadata through linked server

The issue is not security related :-).

In your query, you are selecting from a table on a remote system, but the WHERE condition is using the OBJECT_ID() function, which can only see the local instance, and is not running in the scope of the Linked Server connection.

Why don't you try:

select *
from [DBTEST].[AdventureWorks].sys.objects
where [name] = N'DatabaseLog';

And that query makes more sense anyway since you already had the name, so no sense in querying to get the ID from the name just to use the ID in a second query ;-).

If you want / need to also filter on the Schema name, then you will have to add the JOIN to the [DBTEST].[AdventureWorks].sys.schemas table as OBJECT_SCHEMA_NAME is also local scope only.


Join query using OR on two different columns

Putting certain types of OR logic in a join clause will prohibit SQL Server from implementing the join as a merge join or hash join. The query that you provided appears to be such a query.

CREATE TABLE X_UniqueTeams (ID INT);
CREATE TABLE X_Matches (AWAYTEAM INT, HOMETEAM INT);

select *
from X_UniqueTeams uTeam
Left HASH Join X_Matches match
on match.AwayTeam = uTeam.Id or match.HomeTeam = uTeam.id;

select *
from X_UniqueTeams uTeam
Left MERGE Join X_Matches match
on match.AwayTeam = uTeam.Id or match.HomeTeam = uTeam.id;

Both of those queries error out with the following:

  Msg 8622, Level 16, State 1, Line 37
  Query processor could not produce a query plan because of the hints defined in this query.
  Resubmit the query without specifying any hints and without using SET FORCEPLAN.

Only a nested loop join is available to the query optimizer for your query. For some table structures and data structures a nested loop join can be very inefficient. In general, restricting the physical join operators available to the query optimizer is not a good idea. You want other physical join operators to be available so the query optimizer can find a plan with the lowest estimated cost.

There are two common rewrites that I know of to avoid OR logic in a join clause. The first is that sometimes you can just split the LEFT OUTER JOIN into two joins if the cardinality between the tables is 1:1. That doesn't appear to be the case here so that option isn't available to you.

The second option is to use UNION or UNION ALL. You should prefer UNION ALL whenever possible because it avoids a sorting or hashing step.

select *
from stat.UniqueTeams uTeam
Left Join stat.Matches match
on match.AwayTeam = uTeam.Id
UNION --ALL
select *
from stat.UniqueTeams uTeam
Left Join stat.Matches match
on match.HomeTeam = uTeam.id

For this query in particular, first verify that it does what you need it to. Do you truly need to return all columns with SELECT *? Do you want to return teams that haven't played any games? Once your query syntax is correct consider your response time requirements. How fast does your query need to be? Once you know that try testing out the different rewrites available to you. If none of them are fast enough consider adding indexes on the tables.

If you need further help than this you will need to provide the information that the commentators asked for. Even if this answer is sufficient to answer your question, it's good practice to provide that information upfront in future questions.


How to change db owner after the associated AD account is disabled

Run this for each database.

   -- Change the database owner
    ALTER AUTHORIZATION ON DATABASE::databasename TO sa;
    GO

To generate the script to change them all:

SELECT 'ALTER AUTHORIZATION ON DATABASE::' + db.name + ' TO sa;'
FROM sys.databases db
WHERE owner_sid != 0x01


Design SQL Server 2008 new install ... which collation?

The default collation is based on the Windows system locale of your server. SQL_Latin1_General_CP1_CI_AS is usually the default collation for computers based in the North American locale. Collation can be changed if you have a reason for it, like: sorting order, case sensitivity, accent sensitivity, non-English characters, width.

Collation for a DB can be set at the time of the creation of the DB, so you can have a server that has DBs with different collations. The Alter database command can be used to change the collation of all newly created objects in the DB. I have used the default SQL_Latin1_General_CP1_CI_AS all the time and have not had any issues, but then I do more DB administration that development.


Running .sql files in postgres (structured programs)

The equivalent of Oracle's anonymous PL/SQL blocks are anonymous PL/pgSQL blocks in Postgres. These are started using the do statement:

do
$$
declare
  ....
begin
  ....
end;
$$


ORA02063 error while connecting to remote databse

My Oracle Support Doc ID [456320.1] indicates that the IDENTIFIED BY VALUES clause is only intended to be used by internal utilities (export/import) when used in a database link definition, which may be why the link is not working.  Database link passwords are stored in a separate encrypted format starting in Oracle 10.2.

  The only valid value to be passed in the "IDENTIFIED BY VALUES" clause is an encrypted dblink password identifier (note - this is different from the user password identifier stored in the PASSWORD column of USER$).

The document indicates because this is the expected and intended behavior of the "IDENTIFIED BY VALUES" clause, using it in a database link definition and then trying to use that link to execute remote SQL statements isn't classified as a bug if you receive an ORA-600 error.

Once created, the database link DDL in the database already stores the remote schema password using the IDENTIFIED BY VALUES encrypted format (for example, when retrieved with DBMS_METADATA.GET_DDL()).  Once created using the actual remote schema password, it will be encrypted by Oracle, so unless there is a very good security reason not to use the actual password when creating the link, it seems like this is the best strategy if you are using the database link for normal remote DML operations.


Your options are:

Assign the result to a dummy variable:

junk := perform_work;

Select the result into a dummy variable:

select perform_work into junk from dual;

Create an implicit cursor in a loop:

for i in ( select perform_work from dual ) loop
   null;
end loop;

If the function does not need to be called from SQL you could change it into a procedure with an OUT parameter, though this still requires a "junk" variable it might look very slightly cleaner?

perform_work(junk);

If you don't want to use this function though, why not just remove it from your code?

The only time I can think of there being a valid use-case for this sort of structure is when you're running identical code in two different schemas/on two different databases but this function is useless in one of them. If that's the case the function is still useful in the other and so you will want to do something with the result anyway.


In my opinion, a procedure should do one thing and one thing only. The logic should be performed in the application code. Secondly, a procedure should not return a result set. It should do work and then possibly return a status e.g. success, failure or something similar. Also, the name of your procedure is meaningless. Choose a descriptive name.

So, break it up into three procedures (insert, update and delete). Move the select statement into a view rather than a procedure. Let your programmers add the logic in their code to determine which one to call. Make sure you put index on the table.

Never do a select *. Only select the columns you need. It may be that you add columns later on but you will still SELECT *. Your SELECT * will then store all (incl unnecessary columns) columns in memory and, in worst case, run out of memory and be swapping to disk.


Definition of "table variable" and "base table"

Community wiki answer:

A table variable is an instance of a table. Tables are called relations in relational algebra - hence relational variable (or relvar for short).

Base table means that they are not views which are derived from base tables.


From the downloads page:

  It is available as both open source and commercial editions. The Community (OSS) Edition is available under the free software/opensource GNU General Public License (commonly known as the "GPL").

If GPL is ok for you, then you're fine. The Carrier Grade Edition is a commercial offering though. You'll need to contact Oracle to see if they have special pricing or licensing for educational purposes.


Query to fine "Order_num" that has no two equal "product_name"

Classic solution:
SELECT order_num
FROM a_test
GROUP BY order_num
HAVING COUNT(DISTINCT product_name) > 1;

Custom solutions:
SELECT DISTINCT t1.order_num
FROM a_test t1
JOIN a_test t2 ON t1.order_num = t2.order_num
WHERE t1.product_name != t2.product_name;

WITH cte AS ( SELECT order_num,
                     DENSE_RANK() OVER (PARTITION BY order_num ORDER BY product_name) dr
              FROM a_test )
SELECT DISTINCT order_num
FROM cte
WHERE dr = 2;

...
fiddle

The one that you used 'Join' , again does not return the desired result. It returns those records that have equal order_num but their product_name are different and does not return records with same order_num that their product_name s are the same . Just this – Pantea

Do you mean that you need only orders which (1) have at least 2 products, and (2) have none product twice? If so then add .. AND COUNT(DISTINCT product_name) = COUNT(product_name) to the first query.

SELECT order_num
FROM a_test
GROUP BY order_num
HAVING COUNT(DISTINCT product_name) > 1
AND COUNT(DISTINCT product_name) = COUNT(product_name);

fiddle


How present the result table below the last result query table, SSMS 2017

Yes, actually if you run two queries at the same time it should automatically display to separate windows with the result.


It is the number of connections.  It is not the number of users, as one user can have many connections concurrently and each one counts against max_connections.  And you can also have many more users than connections, as long as users are not permanently connected.


Why shouldn't user-defined database roles be members of fixed roles?

The author is recommending that you practice the Principle of Least Privilege.

Basically, the author is saying that if you create a Role, it should do exactly what you define that role to do and nothing else at all. If you then assign it to a built-in role, you're now greatly expanding the role's permissions and losing some control over exactly what those permissions might be.


Database schema for large possible product combinations and associated price

Is creating columns for all the combinations the correct way? Based on the little information you have given us: No, columns for all combinations is probably not the right solution. Imagine if you suddenly get more products, then you will need to add more columns. Or if some products are removed. Designing your schema so you have to alter a table to deal with something as trivial is bad practice. No, you should probably use records instead.

Let's say you have a combination table, something like this:

CREATE TABLE combination (
  combination_id int unsigned auto_increment,
  product_id int unsigned,
  ...
  PRIMARY KEY (combination_id),
  CONSTRAINT combination_fk1 FOREIGN KEY (product_id)
    REFERENCES product(product_id)
);

So you will insert a record for each product in a certain combination.

You can then select from this table, and join with your product table, to get a listing with all the combinations where a certain product is involved:

SELECT c.combination_id, p.product_name, p.width, p.height, p.price
FROM combination c
  JOIN product p USING(product_id)
WHERE p.product_id = 56
ORDER BY c.combination_id, c.product_id;


Assuming the same user exists in both databases:

EXEC otherdb.sys.sp_executesql N'GRANT ...';

Of course you grant SELECT on a column, not EXECUTE.


Is there a symbol I can use to have a where clause match all columns?

I do not agree with the approach you are thinking of using, but if you are dead set on using it, then I would probably structure the query like this:

SELECT path, markdown
  FROM public."Comment"
  where path = '/other/relative/path/' AND (TRUE OR markdown = 'here I am');

Essentially adding a 3rd parameter to your query .. one use case would use the 3rd param and set the 2nd param to FALSE, while another would set the 2nd param to TRUE..


How to Create a PostgreSQL database with different Encoding?

On Linux,
I was able to create a database with encoding LATIN1 by 1st initializing the database using initdb in \usr\pgsql-10\bin as initdb --encoding=en_US.iso88591. Read more about localization and available character set support.

Then restart the service using-

systemctl restart postgresql-10

This will re-initialize the template databases template0 and template1 with the new encoding and then you can create the database using

createdb <dbname>

Hope this helps.


Should PostgreSQL be Installed on a Server Machine for Multi-User Access?

It can be installed to a server or you can buy it as a cloud service.

As your organization has an IT department, you should ask their opinion first.


What is the easiest way to start a mongodb copy server?

If you want to migrate with minimal downtime, a straightforward approach would be to use MongoDB replication:

Convert your current standalone node to a replica set by restarting your current mongod with a --replSet name and then running rs.initiate(). NOTE: The rs.initiate() step is a once off command that only needs to be run on a primary when the replica set is first created.
If all went well, you should now have a single node replica set with your former standalone node as the primary (check rs.status() and rs.conf() in the mongo shell).
Create your new EC2 instance and start up a new mongod process as a member of the same replica set (i.e. including the same --replSet name in the configuration). To avoid any unexpected issues, the new node should be running the same version of MongoDB as your original standalone server.
Connect to your primary via the mongo shell and add the new mongod process as a secondary node:

rs.add("mynewec2:27017")

You should now have a two node replica set with a primary node and secondary node (which may be in a "startup" or "recovering" phase while data is syncing).
NOTE: For a production MongoDB replica set configuration you would normally want to have a third node to maintain a quorum. This can either be a secondary or an arbiter, which is a voting-only node that does not hold any data and only participates in elections. For the purposes of your migration a third node is optional, but recommended in the event there are any problems with the secondary while the data is being synced. With a two node replica set, both nodes must be online in order to maintain a primary. A three node replica set allows for fault tolerance of 1 node failure.
Assuming there were no firewall or communication errors, the node you added as a secondary should begin to sync data over the network from your original (primary node). Note: there are alternative ways to sync a replica set member (for example starting from a file copy backup).
You can follow the sync progress using db.printSlaveReplicationInfo() on the primary.
Once your secondary node has caught up with the current primary (or is within 10s of the oplog time of the primary) you can use rs.stepDown(60) to force the current primary to stepdown. This should cause your secondary (newly provisioned host) to become the primary.
At this point the migration is complete: you can now shutdown your original mongod host, and restart the new mongod in standalone mode.

To minimise the impact to application writes during this period, you can either:

change your application to use a replica set connection so writes will automatically change to the current primary
coordinate changing your application connection string with the stepdown of the (former) standalone primary


Contact table for customers and employees

with this structure you can find every employye that a customer has, only by querying the Customercontacts table, and only if you need more details from the other tables you join them.
edit.
with this revision  Iadded a date so that you can search for a tine frame where the customer has contact with ione of the empoyees

CREATE TABLE customer (cid BIGINT PRIMARY KEY)

CREATE TABLE customerDeatails (
cdid BIGINT PRIMARY KEY
, cid BIGINT
, Details TEXT
,  FOREIGN KEY (cid) REFERENCES customer(cid) )

CREATE TABLE employee (eid BIGINT PRIMARY KEY)

CREATE TABLE CustomerContactss(
cid BIGINT
, eid BIGINT
,contactdate Datetime
, Details TEXT
, PRIMARY KEY (cid,eid,contactdate)
,  FOREIGN KEY (cid) REFERENCES customer(cid)
,FOREIGN KEY (eid) REFERENCES employee(eid))

db<>fiddle here


You could use one of the free SQL benchmarking tools such as Dell's Benchmark Factory for Databases (BFD) or Hammerora. Benchmark Factory has a free trial and Hammerora is opensource.  They both run industry-known TPC benchmarks, and you can do custom workloads with BFD.  I have only used BFD and each test 'run' can be saved as a report which I found really useful for comparisons.  However Hammerora is also well supported ( eg here and here ).

You could also record one of your own known workloads with a Replay trace via Profiler / server-side trace then restore the backup and replay it in each environment.  There's a bit more work to this option but would make for a more familiar test.

I also have a soft-spot for ostress which is part of the RML Utility Suite which had an update in April 2014 and I still use all the time.  More of a learning curve with this free Microsoft tool, but it's really powerful.  For example you can do things like "run all the .sql scripts in this directory 20 times, simulating 50 concurrent users".

In summary, if you're after a free tool and a quick start look at one of the free tools I mentioned above.  If you want to invest a bit more time, have a look at Replay traces, RML and ostress.

HTH


Handle concurrency on sql server for a booking system

However, the performance of this solution is a big concern, and I read that the order of sql commands is not guaranteed, which means if two sqls hit the server with a microsecond of difference, there is no guarantee the first will have precedence.

I don't quite understand how the order of sql commands will affect performance per this sentence.

  Now, what happens is that when more than one user selects the same available hour, I need to be able to guarantee 100% that only the first will be able to schedule an appointment, and the other will be faced with an error.

Well first is relative, I mean really nobody will know if in a nano second if B hit his after A in regards to your previous SQL.  99.999% of the time it won't happen and if it does happen... well someone is going to get kicked, it really doesn't matter who.

  I could not come up with a mechanism that ensures no racing condition will occur.

Here is one way (fairly simple and easy):

Let say you have a table (Appointment) like:

(Int/Guid) (DateTime2)      (DateTime2)      (int/guid/varchar)
ID         AptDatTime      ReservedOn      ReservedBy
-----------------------------------------------------
1          1/1/2016 08:00

The SQL for someone who starts the reservation would be:
(mixing C# and TSQL for brevity)

Update
  Appointment
Set
  ReservedOn = DateAdd('mi', 5, getdate()),
  ReservedBy = <whateverUserSomething>
Where
  (ReservedOn is Null or ReservedOn > DateAdd('mi', 5, getdate())

(There isn't a way that I am aware of to pull this off with entity-framework natively, you'll have to run the command manually through EF)

Then when the user accepts you do another update

Update
  Appointment
Set
  ReservedOn = DateAdd('yyyy', 1000, getdate()), -- or c# DateTime.Max
Where
  (ReservedOn is Null or ReservedOn > DateAdd('mi', 5, getdate())
  AND (ReservedBy == <whateverUserSomething>)

Actual reservations (can be EF-afied):

Select
  (columns)
From
  Appointment
Where
  (ReservedOn > DateAdd('mi', 5, getdate())

Temporary reservations (can be EF-afied):

Select
  (columns)
From
  Appointment
Where
  (ReservedOn > getdate()
    AND ReservedOn < DateAdd('mi', 5, getdate())

  By which I mean if I hit the db with 500 queries at the same time, will it be able to handle it?

SQL Server is extremely robust, 500 queries is very small.  Unless you're using like Compact DB or Local DB maybe or a server with extremely limited cpu/memory/low IO it shouldn't be a problem.


Is raid 5 suitable for a mysql installation?

In a read-heavy, low-write environment on a RAID5, I would just leave that to one's budget, tolerance, and blood pressure.
In a write-heavy, low-read or write-heavy, read-heavy environment, RAID5 is simply out of the question. This is especially true for InnoDB. Think of an InnoDB's table interaction.
InnoDB
If you do not use innodb_file_per_table, OMG all the activity would be centered around just one file, ibdata1. What is contained in ibdata1?

Table Data Pages
Table Index Pages
Table Metadata for Managing TableSpace IDs
MVCC Data (for ACID Compliance and Transaction Isolation)

Even reads in InnoDB tend to shroud rows with MVCC protection to allow repeatable reads and permit transactions to hit the same rows being read. Thus, reads as well as writes produce disk I/O in ibdata1.
Using innodb_file_per_table may relieve some of the disk I/O by separating Table Data and Index pages from ibdata1 into .ibd files. Yet, I would expect a somwehat noticeable  performance improvement only for a limited time in a RAID5 environment. The table interaction is still somewhat the same. Every access to a .ibd file is always preceded by reference checks against ibdata1.
While the separation can bring significant performance changes, RAID5 would be what they call in the chemistry world, a limiting reagent. Any benefits expected from InnoDB layout changes would be neutralized by outside factors, such as RAID5. The presence of extra tablespace files due to innodb_file_per_table buys you nothing over time but just the presence of extra tablespace files.
MyISAM
When it comes to MyISAM, RAID5 is OK in a read-heavy, low-write environment provided you map all temp tables (using tmpdir) to another disk, separate from the RAID5.
Please remember that table data pages live in .MYD files and its corresponding index pages live in .MYI files. A write-heavy environment (INSERTs, UPDATEs, DELETEs) will obligate RAID5 to slow things down. Given MyISAM's locking behavior (full table lock with each INSERT, UPDATE, and DELETE) in a write-heavy environment, a steady stream of DML will keep RAID5 rather busy and have DB users enter a brief-but-annoying time warp waiting for DML to complete.
Conclusion
Under the hood, RAID5 has the following characteristics for writing with parity

Read the old data block
Read the old parity block
Compare the old data block with the write request. For each bit that has flipped (changed from 0 to 1, or from 1 to 0) in the data block, flip the corresponding bit in the parity block
Write the new data block
Write the new parity block

Should any of these steps see the slightest intermittency, the RAID5 set enters a brief-but-annoying time warp. Multiply that by a huge number of writes and you will feel it in the database performance. Each of these steps could be a point of failure. Why?
According to Wikipedia

In the event of a system failure while there are active writes, the
parity of a stripe may become inconsistent with the data. If this is
not detected and repaired before a disk or block fails, data loss may
ensue as incorrect parity will be used to reconstruct the missing
block in that stripe. This potential vulnerability is sometimes known
as the write hole. Battery-backed cache and similar techniques are
commonly used to reduce the window of opportunity for this to occur.

Recommendation
RAID10 not only provides stability but allows some leeway in disk maintenance without taking mysql down in most cases. When data is mirrored, you know where the data is going and you know from where the data is being read.
UPDATE 2012-02-14 17:55 EDT
After reading your question update, I would say go with RAID10. Unless you do not mind long periods of downtime, you cannot afford to do RAID5 disk maintenance in lieu disk syncing. In fact, the smaller the disks you stripe in RAID10, the faster the sync time would be after a RAID 10 disk maintenance.
Other things to consider

Tune your queries
Remove redundant indexes
Cache as much of the data as you can
Use covering indexes wisely

Questions of this nature can be posted in StackOverflow. You may post such questions in the DBA StackExchange as well.


Query is fast but becomes sluggish when created as a view

The essential problem is that filtering on Route = N'0600' before computing the window functions (as in the query) is different from filtering on Route = N'0600' after computing the window functions (as in the view).

This would give different (= incorrect) results for the window functions in general, so the optimizer does not do that. See Of Windowing Functions And Where Clauses by Erik Darling for more information.

If all the window functions in the view were partitioned on Route, the optimizer would consider pushing the predicate down into the view because correct results would still be obtained. Sadly, that is not the case for your view. Adding OPTION (RECOMPILE) would not help in this case.

Consider rewriting (or supplementing) the view as an inline table-valued function, with an explicit parameter for Route. I have an example of the technique in an answer on Stack Overflow.

If you have other filter criteria beyond Route alone, and if you're going to have a stored procedure anyway, you could abstract away the type of filter criteria - make a TVF for route, and when route is passed to the stored procedure, call it. Make another TVF for date range, and when dates are passed, call it. Gets complicated when they pass both route and dates, if that's possible, but separation is likely going to be the safest way to get reliably predictable performance regardless of which parameter is passed. If you have optional filter criteria it may be that dynamic SQL and/or option (recompile) is your best bet.


Is it possible to add a spatial index to a partition with Postgres 10?

As documented in the manual

  Partitions may have their own indexes, constraints and default values, distinct from those of other partitions. Indexes must be created separately for each partition

(emphasis mine)

So as you are using Postgres 10, you need to create that index manually on each partition (the part you quoted from the manual even shows that the indexes are created for each partition, not for the partitioned table).

With Postgres 11, indexes can defined on the master table and will automatically be created on each partition.

If you need partitioning you should consider upgrading to Postgres 11 anyway as that massively improved the partitioning capabilities (including performance)


SQL Change Tracking not tracking multi-record updates

I have worked out what is going on.

The bad assumption I have made is that if a change causes 4 rows to be updated at once, that would result in the SYS_CHANGE_VERSION of the table going up by 4. It doesn't. Although there are four changes in the changetable, the version only goes up by 1 (it's a single change that changed multiple records.)

Anyone interested in testing it for themselves, can use the code below.

-- 1. Create Table
CREATE TABLE dbo.fooTable(
    [ID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
    [Title] [nvarchar](20) NULL,
    [Surname] [nvarchar](128) NULL,
    [MiddleNames] [nvarchar](128) NULL,
    [Firstname] [nvarchar](128) NULL,
    [CreateDate] [datetime2](7) NOT NULL CONSTRAINT         [DF_CustomerProfile_CreateDate_I]  DEFAULT (getdate()),
    [LastModifiedDate] [datetime2](7) NOT NULL CONSTRAINT     [DF_CustomerProfile_LastModifiedDate_I]  DEFAULT (getdate()),
 CONSTRAINT [PK_fooTable_I] PRIMARY KEY CLUSTERED
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,     ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)

GO

-- 2. Insert dummy data
insert into fooTable (Title, Firstname, MiddleNames, Surname, CreateDate,     lastModifiedDate)
values ('Mr','John',null,'Smith',GetDate()-5, getdate())
insert into fooTable (Title, Firstname, MiddleNames, Surname, CreateDate,     lastModifiedDate)
values ('Mrs','Mary','Beth','Jones',GetDate()-5, getdate())
insert into fooTable (Title, Firstname, MiddleNames, Surname, CreateDate,     lastModifiedDate)
values ('Ms','Thanh',null,'Nguyen',GetDate()-5, getdate())
insert into fooTable (Title, Firstname, MiddleNames, Surname, CreateDate,     lastModifiedDate)
values ('Dr','Lee','Evan','Oscars',GetDate()-5, getdate())

-- 3. Turn on Change Tracking
-- Database
ALTER DATABASE fooDB
SET CHANGE_TRACKING = ON
(CHANGE_RETENTION = 10 DAYS, AUTO_CLEANUP = OFF)

-- Tables
ALTER TABLE dbo.fooTable
ENABLE CHANGE_TRACKING
WITH (TRACK_COLUMNS_UPDATED = OFF)

-- 4. Check current version
Select CHANGE_TRACKING_CURRENT_VERSION() -- Should be Zero if you have never     turned change tracking on before.

-- 5. Make a single change
Update fooTable set MiddleNames = 'Arthur' where Surname = 'Smith'

-- 6. Check current version, should have gone up by 1
Select CHANGE_TRACKING_CURRENT_VERSION() -- Should be 1 if you have never turned change tracking on before.

-- 7. See the change, should be Type U on record with ID 1
DECLARE @version bigint
SET @version = CHANGE_TRACKING_CURRENT_VERSION()-1
Select CT.*
    FROM CHANGETABLE(CHANGES dbo.fooTable, @version) CT

-- 8. If everything is OK up to here, that's great. Now we have a problem.
-- Update all the rows in the table
Update fooTable set lastModifiedDate = Getdate() + 5

-- 9. Check current version, I expected it would go up by 4, one update for each record
Select CHANGE_TRACKING_CURRENT_VERSION() -- Should be 5 if you have never turned change tracking on before.

-- 10. View all changes for the table. There should be 4 (the first one will be gone now.)
Select CT.*
    FROM CHANGETABLE(CHANGES dbo.fooTable, 0) CT

-- Observe that each row has the same SYS_CHANGE_VERSION which means I need to deal with it and move on.


How to calculate the space I need for the indexes?

For the data record add:

6 bytes transaction id
7 bytes rollback pointer
5 bytes extra bytes header
2 bytes - for length of TEXT field (however if TEXT values longer than 7k overhead will be bigger)

Index record stores indexed fields + primary key. So its length will be

5 bytes - extra bytes
indexed fields length
primary key length (in your case it's 1+4+1 bytes)

Take into account an InnoDB page may be filled up to 15/16 i.e. 15k of data+headers.

Each page has 120 bytes in headers.

Plus take into account space that is used by non-leaf pages. It's not huge, but still.


Kafka Internal Data structure vs LSM tree

No Kafka , does not use LSM trees or any tree based data structure for data representation. It relies upon Sequential IO so that it is not relying upon JVM to maintain  cache which would then be presenting as an overhead.
Kafka generally implements a queue based structure rather than a tree based structure (memory + merge sort on disk , something like sstables as disk store format).
Hope this helps a bit


Best practise how to sync SQL Server 2014 database with other database

It sounds like what you are looking for is an Always On Availability Group in Synchronous Commit mode with automatic failover.  The transactions sync from the primary to the secondary and in the event of a system failure, will pass ownership of the database to the secondary. Your application would need to be aware of both the primary and secondary and which one is active.  This feature does make use of the cluster service but you don't have to have an actual cluster with shared storage (see this post from Brent Ozar for more details about hot to work around that). This is the more expensive option, since it requires Windows Enterprise.

You can implement mirroring in high safety mode which does essentially the same thing on any edition of windows, even a desktop if you want, since it doesn't require clustering features. You won't get all the extra benefits, but if you just want to have a synchronous hot standby of your database then it will do that. The caveat here is that mirroring is deprecated, so if you implement this now, you will eventually have to migrate to availability groups in the future.


We can derive AB → CDEFG in this way:

1. A → CDE (given)
2. B → FG  (given)
3. AB → BCDE (by augmentation of 1)
4. AB → AFG (by augmentation of 2)
5. AB → ABCDEFG (by union of 3 and 4)
6. AB → CDEFG (by decomposition of 5)

Note that in general to find a minimal cover is not necessary to perform some proof using the Armstrong’s axioms, but is sufficient to use one of the algorithms presented in database books.


How do I an add a DEFAULT on a column if the table already exists?

In PostgreSQL, DEFAULT is not a constraint. PostgreSQL follows the spec and implements the <set column default clause>. See the ALTER TABLE command documentation for more details. Your command should look like this:

ALTER TABLE foo
  ALTER COLUMN column_name
  SET DEFAULT now();


Two databases same server with different time zones

If you just want to store the same data and have it automatically converted to the timezone of the client, that is just a matter of a session level setting with TIMESTAMP WITH LOCAL TIME ZONE Datatype. Example:

SQL> create table t1(c1 timestamp with local time zone);

Table created.

SQL> alter session set time_zone = '+02:00';

Session altered.

SQL> select current_timestamp from dual;

CURRENT_TIMESTAMP
---------------------------------------------------------------------------
03-JUN-19 07.05.01.908465 PM +02:00

SQL> insert into t1 values(current_timestamp);

1 row created.

SQL> commit;

Commit complete.

SQL> alter session set time_zone = '-09:00';

Session altered.

SQL> select * from t1;

C1
---------------------------------------------------------------------------
03-JUN-19 08.05.11.187305 AM

SQL> insert into t1 values(current_timestamp);

1 row created.

SQL> commit;

Commit complete.

SQL> alter session set time_zone = '+02:00';

Session altered.

SQL> select * from t1;

C1
---------------------------------------------------------------------------
03-JUN-19 07.05.11.187305 PM
03-JUN-19 07.05.36.714308 PM

SQL>

If you really want to store both the timestamp and the timezone offset, then you can use TIMESTAMP WITH TIME ZONE Datatype.


How to count between two users unread messages in SQL

to find out which user has which number of unread messages use this.

SELECT to_id, COUNT(*)
FROM pm
WHERE readd = 0
GROUP BY to_id

So you filter by readd to get only unread messages. Then you will group by the recievers id so that you can get the number of those messages per each reciever.

If you need a more detailed view how many unread messages came from the same sender, use sommthing like this.

SELECT to_id, from_id, COUNT(*)
FROM pm
WHERE readd = 0
GROUP BY to_id, from_id


How to set up simple database replication using WAMP server?

Replication setup is very easy, please follow these steps to make proper replication setup:

1) Backup your Master server's data as:

  mysqldump --all-databases --master-data > dbdump.db

2) Import this backup file on Slave server

3) Create a user in Master server to connect to Slave

  GRANT REPLICATION SLAVE ON .  TO 'repl'@'slaveip'
   IDENTIFIED BY 'slavepassword';

4) Edit Slave server and give server-id to something other that Maser server_id in my.ini file as well specify replicate_do_db as database you want to replicate from Master.

This will replication only database which you want to replicate to slave server, other database from Master will be skipped for replication

5) Execute this query on Master and get it's filename and position

  Show Master status;

6) Now execute this query on Slave

  CHANGE MASTER TO
                  MASTER_HOST='masterip',
                  MASTER_USER='slave_user',
                  MASTER_PASSWORD='slavepass',
                  MASTER_LOG_FILE='Master-bin.000001',
                  MASTER_LOG_POS=101;

7) Now execute this on slave

  Start Slave;

You are done with your replication

8) To check your replication status execute this query on Slave server:

  Show slave status;


Is it possible to add a certificate to a database role?

No, it is not possible to create a Role from a CERTIFICATE or ASYMMETRIC KEY like you can with Logins and Users. It is also not necessary to do this nor would it even make sense to do this. And the reason that is doesn't make sense to associate a Role with a Certificate or Asymmetric Key is that this way of doing security does not work like the standard way of granting permissions.
In the typical / common way of granting permissions, you give permission to a User / Login / Role to do a particular action (Authenticate / Insert / Create / Update / Select / Execute / etc), often on a particular object (i.e. Schema / Table / View / Function / etc). Here you are allowing someone (or some process) to do (or not do) the action that they are requesting to do.
But Certificate- / Asymmetric Key- based security is not concerned with who is making the request, but what code is making the request. This is where ADD SIGNATURE comes in since it makes the association between the code making the request and what permissions that code has. But code (i.e. Stored Procedures, Scalar T-SQL Functions, Multi-Statement T-SQL Functions, Triggers, and SQLCLR objects via the Assembly) cannot be granted (or denied) any permissions directly. Hence you need to create a Login and/or User from the Certificate or Asymmetric Key to receive those permissions and act as a proxy for those permissions to whatever code has the same Certificate or Asymmetric Key that was used to create the Login and/or User. And Logins and Users created from Certificates and Asymmetric Keys cannot log in since they are merely vessels for those proxy permissions; they do not make requests as themselves.
So, if you want a Stored Procedure to access one particular DMV that requires VIEW SERVER STATE, but you don't want to:

grant that permission to a Login because it gives access to a lot of other DMVs, or
use the EXECUTE AS statement since it requires that the User / Login -- the one who should be restricted -- has the IMPERSONATE permission for whichever User / Login they need to "execute as". This leaves the door open to use EXECUTE AS to do other things beyond this desired action, or
use the EXECUTE AS Clause because you either do not want to change the actual execution context (you just want to grant additional permissions), or you need to do something that spans databases, or there is some other nuance of the EXECUTE AS clause that is causing issues,

then you would:

Create the Stored Procedure that SELECTs from that DMV

Create a Certificate or Asymmetric Key

Create a Login from that Certificate or Asymmetric Key

Grant that Login the VIEW SERVER STATE permission

Use ADD SIGNATURE with the same Certificate or Asymmetric Key on that Stored Procedure created in Step 1.
This final step ties the Login (having the correct permission) to that Stored Procedure. Now the Stored Procedure itself, not any Login or User executing that Stored Procedure, has whatever permissions were given to the Login that has the same Certificate or Asymmetric Key.

So, if you have a Role that was used to grant object permissions to for a particular database, then you should just need to add the Certificate-based User to that Role, just like you would add any other User to that Role to grant them the permissions assigned to that Role. This is similar to how you granted EXECUTE on the stored procedure to the Certificate-based User, just like you would for other Users / Roles.


In general case once InnoDB tablespace is corrupt you need to re-create tablespaces from scratch. This is the safest way especially if you're not familiar with InnoDB files/format.

In some cases InnoDB writes to the error log file information about corrupted page number and index it belongs to. For example,

InnoDB: Page may be an index page where index id is 2575
InnoDB: (index "idx_actor_last_name" of table "sakila"."actor")
InnoDB: Database page corruption on disk or a failed
InnoDB: file read of page 4.

InnoDB crashed at that page but it doesn't mean other pages are OK. To estimate how badly a tablespace is corrupt innochecksum might help. Note however it's often misleading. innochecksum checks individual pages, but not the tablespace structure. For example, all-zero page is valid in innochecksum's opinion while the page should have contained some data.

To check that all tablespaces are good you can read data from all indexes (PRIMARY and all secondary) of all tables. For example, to check sakila.actor you need two selects:

SELECT * FROM actor FORCE INDEX ('PRIMARY')
SELECT * FROM actor FORCE INDEX ('idx_actor_last_name')

If you suspect that only a secondary index is corrupt you may drop and re-create it. I describe this case in https://twindb.com/repair-corrupted-innodb-table-with-corruption-in-secondary-index/ .

If PRIMARY index is corrupt you may want to dump the table drop it and reload again. I.e. you don't reload whole database, just one table.


SQL Server 2016 Availability Group - 2 Nodes as Primary & Readable Secondary, 1 Node as DR

I would set up your configuration as follows...

Node and File Share Majority quorum model

Node 1, Node 2 and file share witness at primary dc

Node 3 at DR dc

1 vote to each node in the primary data center & fileshare witness

0 vote to node in the DR data center

preferred owners node 1 & node 2

AG synchronous mode between node 1 & 2 automatic failover

AG asynchronous mode to node 3 manual failover

primary AG on node 1 with readable secondary replicas at node 2. Since you plan to use readable secondary replica, you will have to license node 2 as well as node 1. Node 3 will be covered by software assurance. I assume this is a production environment.


MySQL: Why is this UPDATE query so incredibly slow?

Why don't you just build a temp table and use the results to update. This is bad form. It's got to build the Subquery first, then assemble that in the WHERE Clause, then it's going to update every record to find a match.

Instead just INNER JOIN to a temp table that does the first part, or a CTE if you're using SQL Server and can't have a multi-step process.

WITH SkuList AS
(
SELECT DISTINCT sku FROM (
  SELECT name,size,colour,sku,quantity,price FROM ActiveProducts
  UNION ALL
  SELECT name,size,colour,sku,quantity,price FROM XMLImport
) tbl
GROUP BY name,size,colour,sku,quantity,price
HAVING count(*) = 1
ORDER BY name,size,colour,sku,quantity,price
)
UPDATE ActiveProducts ap INNER JOIN SkuList xml ON ap.sku = xml.sku
SET
  ap.name = xml.name,
  ap.size = xml.size,
  ap.colour = xml.colour,
  ap.instock = xml.instock,
  ap.quantity = xml.quantity,
  ap.price = xml.price,
  ap.updated = 1


Counting NULLs as zeroes and filtering with the WHERE statement

SELECT name, coalesce(socks_bought,0) AS socks_bought
FROM customers
LEFT JOIN
  (SELECT cust_id, sum(num_socks) AS socks_bought
   FROM orders
   GROUP BY cust_id) AS t1
    ON t1.cust_id = customers.cust_id
where coalesce(socks_bought,0) < 3;

+-------+--------------+
| name  | socks_bought |
+-------+--------------+
| Jonny | 0            |
+-------+--------------+


Aurora / MySQL 5.6 process list thousands of threads in state 'cleaned up'

Finally found the answer, its an Aurora specific state that has the same meaning as the blank state in MySQL.

Cleaned Up state means it has completed and cleaned up and waiting for the connection to close.

https://forums.aws.amazon.com/thread.jspa?messageID=708499


Is it possible to restore a database snapshot from a 64 bit system to a 32 bit one?

MyISAM [and] InnoDB data and log files are binary-compatible on all
  platforms having the same floating-point number format.

Source.

You can copy freely between those two platforms if you maintain the same vendor and version.

Obviously, the binary executables for 64 bits will not work on 32-bit.


Firstly, read the Wikipedia article that explains UTC/GMT.

Time stamps with time zones are expressed as a date, a local time (eg: 08:00:00, which is 8am), and an offset (eg: -8, which would be 8 hours behind UTC). To convert a local time into UTC you use the offset.

So, in your example of:

TIMESTAMP '1999-04-15 8:00:00 -8:00'

... the local time is 08:00:00, which is 16:00:00 UTC (08:00:00 + 8 hours, because it is 8 hours behind UTC).

TIMESTAMP '1999-04-15 11:00:00 -5:00'

... the local time is 11:00:00, which is 16:00:00 UTC (11:00:00 + 5 hours, because it is 5 hours behind UTC).

The two times and dates in your example are identical when converted to UTC - that is what the sentence in your question means.


Optimizing PostgreSQL for transient data

Depending on how many different data sets there are, one option would be to partition the tables per-dataset.

When a dataset is updated, BEGIN a new transaction, TRUNCATE the table, COPY the new data into it, and COMMIT. PostgreSQL has an optimisation where COPYing into a table that's been TRUNCATEd in the same transaction does much less I/O if you're using wal_level = minimal (the default).

If you cannot partition and truncate (say, if you're dealing with tens or hundreds of thousands of data sets, where there'd just be too many tables) you'll instead want to crank autovacuum up to run as much as it can, make sure you have good indexes on anything you delete based on, and be prepared for somewhat ordinary performance.

If you don't need crash safety - you don't mind your tables being empty after a system crash - you can also create your tables as UNLOGGED, which will save you a huge amount of I/O cost.

If you don't mind having to restore the whole setup from a backup after a system crash you can go a step further and also setfsync=off, which basically says to PostgreSQL "don't bother with crash safety, I have good backups and I don't care if my data is permanently and totally unrecoverable after a crash, and I'm happy to re-initdb before I can use my database again".

I wrote some more about this in a similar thread on Stack Overflow about optimising PostgreSQL for fast testing; that mentions host OS tuning, separating WAL onto a different disk if you're not using unlogged tables, checkpointer adjustments, etc.

There's also some info in the Pg docs for fast data loading and non-durable settings.


As seen in the availability group prerequisites the database must be in full recovery mode.

To change the recovery model, the database must be removed from the Availability Group, and it must be changed back to FULL before adding back into the Availability Group.

Obviously be very careful here - changing the database to single mode will break your log backup chains so you will be without that recovery mechanism if it goes wrong.


As often with such questions, the \set ECHO_HIDDEN on command of psql helps.  \dT+ will show the possible values of the enum, if the type in question is an enum.  The query behind the output is rather complex, but one can simplify it to fit your needs like

SELECT format_type(t.oid, NULL) AS name,
       array_agg(e.enumlabel ORDER BY e.enumsortorder) AS elements
  FROM pg_type AS t
  LEFT JOIN pg_enum AS e ON e.enumtypid = t.oid
 WHERE t.typname = 'foo'
 GROUP BY t.oid;

This will return {NULL} (an array with a NULL in it) if the type is not an enum, and the actual elements as an array otherwise.  You can tweak it further if necessary.


Error 3203 restoring SQL Server backup: The data is invalid

Try running a RESTORE FILELISTONLY or RESTORE HEADERONLY. If these run successfully, that would prove that SQL Server is successfully accessing the .bak file. (And rule out some sort of permission issue.)

If that is successful, try running your RESTORE VERIFYONLY and RESTORE DATABASE with STATS=1. If it continues to fail in the same spot each time, it would probably indicate that your backup is corrupt, and you're out of luck.

If it fails in a different spot each time, it could be an issue with reliability of the location where your .bak is saved.


At first I didn't want to mess around with the admin database but it seemed it was the only thing to do.

Gather information

sh.status() on a mongos will give the shards name needed and the shard cluster id.
Something like that:

"clusterId" : ObjectId("3b743bf3134df1f277980ard")
...
shards:
        {  "_id" : "shardname1",  "host" : "[host:port]",  "state" : 1 }
        {  "_id" : "shardname2",  "host" : "[host:port]",  "state" : 1 }

For each replica set

Get on the primary, select the admin database

Then:

db.system.version.insert({
    "_id" : "shardIdentity",
    "clusterId" : ObjectId("3b743bf3134df1f277980ard"),
    "shardName" : "REPLICA_SHARD_NAME",
    "configsvrConnectionString" : "conf/[host]"
})

See Shard Aware.

Reboot all replica set members


Is it safe to remove an entry in a "config.mongos" MongoDB collection?

You can remove these entries, but there is really no reason to do so.  The config.mongos collection just records all mongos processes that have connected to the config server in question.  Hence any that reconnect at any point will just show up again - it's a book keeping thing, and only takes up a few KB at most, there's no need to delete them.