How can I get all orphan records in a single query?

This should only be possible if you have a relationship between tables that you know about but SQL Server doesn't.

SELECT fk
  FROM dbo.ChildTable AS c
  WHERE NOT EXISTS
  (
    SELECT pk FROM dbo.ParentTable AS p
    WHERE p.pk = c.fk
  );

Now, in the future, define this relationship explicitly, then people won't be able to delete the parent rows while child rows still exist. To delete the rows that shouldn't be there today:

DELETE c
  FROM dbo.ChildTable AS c
  WHERE NOT EXISTS
  (
    SELECT pk FROM dbo.ParentTable AS p
    WHERE p.pk = c.fk
  );

Next, take away their rights to disable these constraints so that they stop doing this the wrong way.


How can I store least used table files to a slower drive?

What you are asking is not really an automatic feature in MySQL 5.6, I could be wrong.
Notwithstanding, you can set it up manually.
Given the following information

Your datadir is /var/lib/mysql
You data is in the Database mydb
You have this InnoDB table

Table Description
CREATE TABLE mytable
(
   if int not null autop_increment,
   ...
   primary key (id)
) ENGINE=InnoDB;

You have a data folder to hold the least often used tables called /data/least
Make sure /data/least is owned by mysql  by running chown -R mysql:mysql /data/least

Here is what you must do manually
USE mydb
CREATE TABLE mytable_least
(
   if int not null autop_increment,
   ...
   primary key (id)
) ENGINE=InnoDB DATA DIRECTORY='/data/least';
INSERT INTO mytable_least SELECT * FROM mytable;
ALTER TABLE mytable RENAME mytable_trash;
ALTER TABLE mytable_least RENAME mytable;

How was mydb.mytable manifested before the change ?

The .frm file is /var/lib/mysql/mydb/mytable.frm
The .ibd file is /var/lib/mysql/mydb/mytable.ibd

How is mydb.mytable now manifested ?

The .frm file is /var/lib/mysql/mydb/mytable.frm
The .ibd file is /data/least/mytable.ibd

You should test this out with a blank InnoDB table and make sure.
Give it a Try !!!


If you only have a few variables I would consider keeping separate boolean columns.

Indexing is easy. In particular, indexes on expressions are easy.
Conditions for queries and partial indexing are easy to write and read and meaningful.
A boolean column occupies 1 byte. For only a few variables this occupies the least space.
Unlike the other options boolean columns allow NULL values for individual bits if you should need that. You can always define columns NOT NULL if you don't.

Optimizing storage
If you have more than a hand full variables but less than 33, an integer column may serve you best. (Or a bigint for up to 64 variables.)

Occupies 4 bytes on disk.
Very fast indexing for exact matches (= operator).
Handling individual values may be slower / less convenient than with bit string or boolean.

With even more variables, or if you want to manipulate the values a lot, or if you don't have huge tables and disk space / RAM is no issue, or if you are not sure what to pick, I would consider bit(n) or bit varying(n).

Occupies at least 5 bytes (or 8 for very long strings) plus 1 byte for each group of 8 bits (rounded up).
You can use bit string functions and operators directly.

Examples
For just 3 bits of information, individual boolean columns get by with 3 bytes, an integer needs 4 bytes and a bit string 6 bytes (5 + 1).
For 32 bits of information, an integer still needs 4 bytes, a bit string occupies 9 bytes for the same (5 + 4) and boolean columns occupy 32 bytes.
Further reading

Is there any difference between integer and bit(n) data types for a bitmask?
Calculating and saving space in PostgreSQL
Can I convert a bunch of boolean columns to a single bitmap in PostgreSQL?


`SELECT FOR UPDATE` in master-master mysql setup

Generally, it is considered a very bad idea to write to both masters in master/master replication, unless you are writing to different schemas.

MySQL replication is asynchronous (MySQL 5.5 has "semi-synchronous" replication, but it's still asynchronous in practice).  There is no mechanism to synchronize locks or detect conflicts between hosts.

Consider what happens in this situation

master1> BEGIN;
master1> SELECT * FROM table WHERE id = 7 FOR UPDATE;
master1> UPDATE talbe SET col = "Newton" WHERE id = 7;

master2> BEGIN;
master2> SELECT * FROM table WHERE id = 7 FOR UPDATE;
master2> UPDATE table SET col = "Aaron" WHERE id = 7;

master1> COMMIT;
master2> COMMIT;

Depending on timing, replication lag, and whether you have ROW or STATEMENT based replication, you might end up with different data on different hosts.  Certainly, master1's FOR UPDATE will not lock the rows on master2 and vice versa, thus negating the utility of FOR UPDATE (which is to be avoided).

In your situation you could, at best, end up with broken replication and at worst end up with silent data sync issues between the servers.


Postgres - Selectively either null or delete foreign key rows (without cascade)

EDIT: Recursive version. Lots of debug but peace of mind is nice when you're running queries like this. I'm sure this is by no means perfect, it assumes the PKs are ints for one, but it does the job for me:

create or replace function delete_cascade(table_name text, column_name text, column_value int) returns void
language plpgsql
as
$func$
  declare
    sql_temp text := '';

    rec record;
    recur record;
    recur2 record;

  begin
    raise notice 'enter';
    sql_temp := format('' ||
                       'with fkey as ' ||
                       '( ' ||
                       '  select pg_class.oid, pg_attribute.attnum ' ||
                       '  from pg_attribute ' ||
                       '  inner join pg_class on (pg_class.oid = pg_attribute.attrelid) ' ||
                       '  where pg_class.relname = ''%s'' and pg_attribute.attname = ''%s'' ' ||
                       ') ' ||
                       'select pg_class.relname, pg_attribute.attname, pg_attribute.attnotnull ' ||
                       'from pg_constraint ' ||
                       'inner join pg_attribute on (pg_attribute.attrelid = pg_constraint.conrelid) ' ||
                       'inner join pg_class on (pg_class.oid = pg_attribute.attrelid) ' ||
                       'inner join fkey on (fkey.oid = pg_constraint.confrelid and (array_position(pg_constraint.confkey, fkey.attnum) > 0)) ' ||
                       'where array_position(pg_constraint.conkey, pg_attribute.attnum) > 0',
                        table_name, column_name);
    for rec in execute sql_temp
    loop
      sql_temp := format('select pg_attribute.attname from pg_index inner join pg_attribute on ((pg_attribute.attrelid = pg_index.indrelid) and (pg_attribute.attnum = any(pg_index.indkey))) where (pg_index.indrelid = ''%s''::regclass) and pg_index.indisprimary', rec.relname);
      for recur in execute sql_temp
      loop
        sql_temp := format('select %s as col_val from %s where %s = $1', recur.attname, rec.relname, rec.attname);
        raise notice '%', sql_temp;
        for recur2 in execute sql_temp using column_value
        loop
          sql_temp := format('select delete_cascade(''%s'', ''%s'', %s)', rec.relname, recur.attname, recur2.col_val);
          raise notice '%', sql_temp;
          execute sql_temp;
        end loop;
      end loop;

      if rec.attnotnull = true then
        sql_temp := format('delete from %s where %s = %s', rec.relname, rec.attname, column_value);
        raise notice '%', sql_temp;
        execute sql_temp;
      else
        sql_temp := format('update %s set %s = null where %s = %s', rec.relname, rec.attname, rec.attname, column_value);
        raise notice '%', sql_temp;
        execute sql_temp;
      end if;
    end loop;

    sql_temp := format('delete from %s where %s = %s', table_name, column_name, column_value);
    raise notice '%', sql_temp;
    execute sql_temp;
    raise notice 'leave';
  end
$func$;

Original, more readable but "depth = 1" answer:

In the unlikely event anyone wants to follow in my footsteps here, this is what I ended up with. It only "cascades" one level, but as it happens that's good enough for me for the task in hand. I'll leave recursive cascades to the interested reader ;-)

create or replace function delete_cascade(table_name text, column_name text, column_value int) returns void
language plpgsql
as
$func$
  declare
    cur cursor for
      with fkey as
      (
        select pg_class.oid, pg_attribute.attnum
        from pg_attribute
        inner join pg_class on (pg_class.oid = pg_attribute.attrelid)
        where pg_class.relname = table_name and pg_attribute.attname = column_name
      )
      select pg_class.relname, pg_attribute.attname, pg_attribute.attnotnull
      from pg_constraint
      inner join pg_attribute on (pg_attribute.attrelid = pg_constraint.conrelid)
      inner join pg_class on (pg_class.oid = pg_attribute.attrelid)
      inner join fkey on (fkey.oid = pg_constraint.confrelid and (array_position(pg_constraint.confkey, fkey.attnum) > 0))
      where array_position(pg_constraint.conkey, pg_attribute.attnum) > 0;

    rec record;

  begin
    for rec in cur loop
      if rec.attnotnull = true then
        execute format('delete from %s where %s = %s', rec.relname, rec.attname, column_value);
      else
        execute format('update %s set %s = null where %s = %s', rec.relname, rec.attname, rec.attname, column_value);
      end if;
    end loop;

    execute format('delete from %s where %s = %s', table_name, column_name, column_value);
  end
$func$;


What are drawbacks of setting REPLICA IDENTITY FULL in PostgreSQL table

The only disadvantage is that it will produce more data than are necessary to identify the affected row, but since that is what you want, it is not a disadvantage to you.


Reasons for a session got executing the same SQL for hours

This is just too generic. From what you provided us, it is impossible to tell.
A few possible reasons:

network wait (waiting for a dblink connection that is already dead)
blocking wait (another transaction blocks these sessions)
programmed wait (DBMS_LOCK.SLEEP)
or session is not in wait at all but uses the CPU (inefficient PL/SQL code or SQL code/execution plan or session is simply spinning due to bug)


Tuning Clustered Indexes in case of Soft / Logical delete

First, testing is your buddy. Run tests on this.
Let's avoid talking too much opinion. This question could be taken as a purely opinion driven one. Instead, let's talk about some of the mechanics of choosing a clustered index.
You get one clustered index on a table because the clustered index defines data storage. Since the clustered index is where the data gets stored, the best rule I've found is to make the clustered index the most frequently used path to the data. For many tables, this is simple, the primary key. It will absolutely be the most commonly used path to the data. However, that's not always the case.
The question here is, how is your data being accessed? Is it through the primary key? If so, adding the IsDisabled shouldn't be necessary. If most queries have the ID value and you're going directly to one row, that's it. Adding IsDisabled won't help. In fact, having a filter that has the ID and IsDisabled will work fine (although, test it, test it, test it) because the principal filter is on a unique value that can be found first and then additional columns will be used to refine that result.
On the other hand, if the primary key is not the most common access path to the data. Rather, you're seeing filtering on date, some foreign key, a set of columns, and then the IsDisabled column, then changing the clustered index may make some sense. Instead of having it on the default of the primary key, put it on the data, fk, or set of columns, along with IsDisabled. Although, again, test it.
Picking the keys for an index, clustered or not, is all about balancing selectivity, so that the statistics which are always built off the first column look as attractive to the optimizer as possible, with the need to define the key so it functions to properly refine the data. The IsDisabled column, at least early in the databases life, is likely going to have only a single value. However, as the database ages, it's going to have two. But only, ever two. So, regardless of whether or not you put that column in the index, it's unlikely to be useful as the leading edge, or first column, of that index. It's always going to be either a second, or last, column, or an INCLUDE column.
I hope all that helps.
Be sure and test it.


How to check a database for a specific page

To get information about a particular page in SQL server, you'll need to use the undocumented DBCC PAGE command. Or if you are using SQL Server 2012 and higher, you can also query the sys.dm_db_database_page_allocations DMV.

Regarding problems with page restores, unless you are on Enterprise Edition, page restores have to be performed OFFLINE. (i.e. you must not RECOVER the database and perform the log restore or full restore with the NORECOVERY option which will leave the database in a state that allows page restores in either edition)

Here's a quick blog post I found that introduces the sys.dm_db_database_page_allocations DMV: SQL SERVER 2012 – sys.dm_db_database_page_allocations – dynamic management function

For a quick overview of the DBCC PAGE command, you can read Paul Randal's blog post about it: How to use DBCC PAGE

And an introduction for how to do page restores in Enterprise and Standard edition: How to Restore a Page in SQL Server Standard and Enterprise Edition


Multiple self joins where joining condition not always exists

You use INNER JOIN (in comma-delimited tablenames form) whereas you must use LEFT JOIN.
WITH SALES_TODAY AS( /* daily selection logic */ ),

     sale_id_list AS ( SELECT DISTINCT sale_id FROM SALES_TODAY )

SELECT sale_id_list.sale_id, ...
FROM sale_id_list
LEFT JOIN SALES_TODAY AS SALE_PROPOSED
    ON SALE_PROPOSED.sale_id=sale_id_list.sale_id
    AND SALE_PROPOSED.sale_type='SALE_PROPOSED'
LEFT JOIN SALES_TODAY AS SALE_ACCEPTED
    ON SALE_ACCEPTED.sale_id=sale_id_list.sale_id
    AND SALE_ACCEPTED.sale_type='SALE_ACCEPTED'
LEFT JOIN ...


The effect of a Primary Key on a blocking situation

Community wiki answer - please edit to improve:

The reason is when you create an index, either specifically or by declaring it as primary key (which internally creates an index to enforce the constraint), it makes both update statements fast, and they finish quickly without blocking.

With no index, each query has to scan all the records to locate what you have specified in the where clause, and thus there is blocking, which I guess would eventually go. Or it's lock escalation: without the index, it has to escalate to a table lock, and that's what blocks. You really need to look at what resource is being held by the block/deadlock to see what is happening.


Log shipping restores (like all restores) are based on LSN. Created .trn files should not be affected.   Also, is this for Daylight savings in US, as that is next weekend, not yesterday.  If files have gone missing, there is a different reason for it.  Missing from Primary or Secondary?


How do I Order By on multiple rows in Postgresql

Simply order them  with the right orde4r with DESC for descending
Schema (PostgreSQL v9.6)
CREATE TABLE test (
  "name" VARCHAR(7),
  "critical" INTEGER,
  "major" INTEGER,
  "trivial" INTEGER
);

INSERT INTO test
  ("name", "critical", "major", "trivial")
VALUES
  ('Apache', '2', '3', '2'),
  ('Eclipse', '5', '0', '4'),
  ('TestNg', '0', '0', '5'),
  ('JUnit', '0', '2', '7');

Query #1
SELECT * FROM test
ORDER BY "critical" DESC, "major" DESC, "trivial" DESC;

| name    | critical | major | trivial |
| ------- | -------- | ----- | ------- |
| Eclipse | 5        | 0     | 4       |
| Apache  | 2        | 3     | 2       |
| JUnit   | 0        | 2     | 7       |
| TestNg  | 0        | 0     | 5       |

View on DB Fiddle


changing server roles in streaming replication

Unforutnatley, you cannot do this without taking a new base backup. This is something being worked on, but at this point repointing the slave is not supported.

There exists a tool called pg_rewind, but there are definite cornercases where that one does not work properly, so it's quite a risky way.

For now, doing a basebackup/rsync is the supported method for this.


What is the distinction between ALL_PROCEDURES and DBA_PROCEDURES, and other schema tables?

From here

USER_TABLES describes the relational tables owned by the current user.
ALL_TABLES describes the relational tables accessible to the current user.
DBA_TABLES describes all relational tables in the database.

Accessible != all!


Validate many SQL statements for correctness

There's the table sys.sysdepends which will help with your stored objects.

For embedded SQL you could SET ROWCOUNT for the batch.  This will terminate the query after compilation (and hence name resolution and object binding) but before large quantities of data are processed.


How to fetch the last updated time of a record?

You task has only meaning if the "id" is not a primary key, but a foreign key of your table. It means, that your table has multiple rows with the same id.

Thus, your table is essentially a log.

To get the first row of all ids, you can use the MIN() aggregate function, grouped the tables with ids:

SELECT MIN(last_update) FROM yourTable GROUP BY id;

However, if it is possible that all the values remain the same if a new log entry arrives into your table, then the situation is a little bit harder.

SQL is not very effective is we want to compare consecuting rows, although it is possible.

A possible solution would be using windowing functions. Essentially, we count the timeline of all the records, and then compare all of them to their consecuting one.

WITH Raw AS (
  SELECT *, RANK() OVER (PARTITION BY Id ORDER BY last_update) AS EventNo
  FROM yourTable
)
SELECT Prev.Id, MIN(Next.LastUpdate) AS FirstChangeTime
FROM Raw AS Prev, Raw AS Next
WHERE
  Prev.Id = Next.Id
  AND
  Prev.EventNo + 1 = Next.EventNo
  AND
  ( Prev.Value1 <> Next.Value1
    OR
    Prev.Value2 <> Next.Value2
    OR
    Prev.Value3 <> Next.Value3
  )
GROUP BY Prev.Id;

Note, this solution is the simplest what came in my mind now. It has a terrible performance and a resource waste. Use it only for data mining related tasks.

To do the same effectively requires more advanced technics over the limit of the current answer.


SQL Server Isnull Returning 1900-01-01 when field is null

You can't get an empty string because you're returning the DATE value type from ISNULL.
Per the docs, ISNULL

Returns the same type as check_expression. If a literal NULL is
provided as check_expression, returns the datatype of the
replacement_value. If a literal NULL is provided as check_expression
and no replacement_value is provided, returns an int.

If you're checking whether or not the value is NULL, there is no need to convert it to a date, unless you wanted to return a date value (which you don't seem to).
Instead, use:
SELECT ISNULL( DOB , '')

Which will return
''

if the value is NULL.
A NULL date is NULL (no value). An empty string, on the other hand, evaluates to 0, which in SQL Server is implicitly an integer representing the number of days since 1900-01-01.


DB model strategy to override field value

You can create a view like:

create view all_config as
select y.id, x.accountId, y.config
from accounts x
cross join defaultconfig y
where not exists (
    select 1 from accountconfig z
    where x.accountid = z.accountid
      and y.id = z.defaultconfigid
)
union all
select a.id, a.accountid, a.defaultconfig
from accountconfig a

and select from there to get the current config for an account. Note that you should only store information that is changed in AccountConfig. Don't use null as a merker. In your example, id 1 in AccountConfig should be removed. The view will provide this info from the default table.


Load Balancer for MySQL MASTER MASTER Replication

As you already have a running Master-Master replication, here a tip for the load balancer: I use HAProxy

It is a free software balancer, needs very less system resources and is quite easy to configure.

A tip: Install 2 loadbalancers with keepalived or keep in mind that you just created a critical bottleneck.

I use the setup keepalived + 2x haproxy + 2x master-mysql for 9 months now and I only noticed one problem with pdo_mysql - you can see my problem here: https://stackoverflow.com/questions/26871221/slow-pdo-mysql-mysqli-with-haproxy

There is also a small example config (in the example it is a hot standby master)


How to decide the parameter processes count?

Yes, it depends on your user count. But not only that, it also depends on which way your users connect to the database and the number of background processes your instance requires.

There are basically two types of server processes that services user processes (client processes): dedicated server process, where there is an one-to-one mapping between user process and server process, and shared server process, where, like the name suggests, a server process can be shared between two or more user processes.

If your application (client) connects directly to the Oracle database, it will spawn a dedicated server process to handle its requests. If your application connects to a dispatcher, then you are using one of many shared processes to handle your requests.

Basically, if you use shared server connections you can service many users with fewer processes, but you may have a performance penalty. More information about dedicated and shared server processes can be found at: https://docs.oracle.com/cd/E11882_01/server.112/e25494/manproc.htm#ADMIN11166

Also, please beware that what prevents you from connecting to an instance is reaching the SESSION limit, which is a derived metric from the number of processes:

sessions =  (1.5 * PROCESSES) + 22

Since every login requires a session, it's the sessions parameter that actually determines the number of concurrent users on the instance. Please refer to the following url for more details: https://docs.oracle.com/cd/E18283_01/server.112/e17110/initparams229.htm


How to setup continuous DB replication from on-prem MS SQL server to PostgreSQL on AWS RDS without reconfiguring source machine?

This is a very rough thought process on one thing you could do (and I'm sure there's ways to fine tune this idea, which I'll continue to update as I think of them) but you can leverage a couple of the things MS SQL Server and AWS offers to meet most of your needs.
In order of events, this is the steps you can take:

Scale up an additional Microsoft SQL Server possibly using SQL
Server Express Edition: This server will house a job that will
handle pulling the data from your primary MS SQL Server and
comparing it to your AWS PostgreSQL instance, to generate the changes.
There are a lot of limitations with SQL Server Express Edition, so
you'll have to read up on it, but it's a free edition of Microsoft
SQL Server so it checks off the cheap checkbox if you are able to
leverage it. If you can't, then a somewhat cheap alternative would
be to use SQL Server Standard Edition on a two core server. Make sure to install the  Integration Services component during the installation of SQL Server (regardless of which edition you choose).
You can choose to scale this up in AWS, a VM, or however you see
fit. (In AWS you can create a job that turns this server on and off
as needed.)

Create a SQL Server Integration Services project: This will be compiled into an SSIS package that will live on the server job server above. In the SSIS project you'll be able to connect to your primary Microsoft SQL Server, pull in the data from the source table, and then pull in the destination copy of the table on your AWS PostgreSQL instance, and then compare the two datasets for changes. How you implement your comparator is up to you, but one thing in Microsoft SQL Server that can be useful is a function called HASHBYTES(). You can hash the whole row and compare it's hash to your AWS PostgreSQL instance's hashed rows. (I'm not sure if there's an equivalent function in PostgreSQL, but if there's not then you can import your PostgreSQL's table data into your MS SQL job server first and then compare both datasets with HASHBYTES.) Then you'll be able to output the resultant rows that've changed to a destination. Unfortunately I don't think you'll be able to directly output them to your AWS PostgreSQL instance, so the next step will be an intermediary step.

Add a step to your SSIS project to output the rows that changed to either another staging table on your job server or to a raw file like a CSV. (This will be up to you to decide how to implement, as a staging table means you'll need to leverage a more complex process in AWS to pull in the data. I think a CSV is the simpler route, you just need to ensure you setup a file share for the file to be saved to.)

Deploy the SSIS project as a package to your Microsoft SQL job server. Then use the SQL Agent to schedule a job that runs daily to execute the SSIS package.

Create an S3 bucket (or alternative AWS storage solution) in your AWS cloud for where the raw file from step 3 will be copied to.

Create a scheduled job that will migrate your raw file from step 3 to your S3 bucket in step 5. This again will be up to you and can be accomplished many ways such as the following examples:

AWS SFTP Managed Service
AWS REST API
AWS SDK using your programming language of choice
AWS CLI or any other alternative they offer
An alternative non-AWS solution (programmatically, etc)

Leverage the AWS SDK, or alternative services they provide to create an AWS job that uploads the data from the file you imported into your S3 bucket into your PostgreSQL instance. I'd recommend importing it to a separate staging table in PostgreSQL first (but this up to you how you end up implementing it). (You might be able to roll step 6 into the same job you create for this step.) See this AWS doc for more info: Importing data into PostgreSQL on Amazon RDS

After importing the data above into a staging table in PostgreSQL, you can (either in the same job or a new job), delete from your destination table the records with the same primary keys as the records in your staging table. Then insert the records from your staging table into the destination table. (Although this is technically doing a DELETE / INSERT on the records that were updated, it's still better than truncating the entire table and inserting everything, so I think it should suffice your requirement of not doing a full load.)

Implementing this process is going to be a lot of work (more than it took to read :), so you might consider just paying an AWS partnered company to implement a solution for you if it's too much time to implement, but that's for you to decide.
One other thing that might be useful to be aware of in Microsoft SQL Server is Change Data Capture (CDC). This is a process in SQL Server that logs changes to tables (essentially maintains a transaction log table of every table in your database). It might be useful in conjunction or in place of SSIS and is the main component of Microsoft SQL Server that AWS's out of box DMS uses to migrate data from SQL Server.
Another similar component in Microsoft SQL Server is called Temporal Tables which have similar functionality as Change Data Capture (but are probably less used).
If you have any questions, I'm happy to elaborate on any of the steps in my rough solution above.


The first thing I do is create an Entity-Relationship Diagram (ERD).  Sometimes you can simply describe the metadata with command-line tools but to save time there are some tools that can generate a diagram automatically.
Second, examine each table and column make sure I learn the meaning of what it stores.
Third, examine each relationship and make sure I understand how the tables relate to one another.
Fourth, read any views or triggers to understand custom data integrity enforcement or cascading operations.
Fifth, read any stored procedures.  Also read SQL access privileges if there are such.
Sixth, read through parts of the application code that use the database.  That's where some additional business rules and data integrity rules are enforced.

update:  I just read an interesting article "9 Things to Do When You Inherit a Database" with a good checklist.

Summary:

Backups
Research (the schema documentation steps I mention above)
Talk to the former developers
A bug database
Source code control
Talk to the users and/or business owners
Establish credibility with the users by fixing a few things or making some enhancements
Create a development environment
Drop obsolete objects


Two hints for you:

The KEY uid is redundant, because it is covered by the PRIMARY KEY
40,000 rows at a time might make for too large a transaction. Although these are very small rows (two INTs) this may cause the transaction to go to disk, depending on your settings. I usually go with around 1,000 rows at a time (I go as low as 100 and as high as 10,000).
Please try doing 40 * 1,000 and see if this works better for you than 1 * 40,000


They are all minimal keys.

Minimal means there is no proper subset with the same property.


oracle - does this have the potential to cause a lock?

In Oracle, writers don't block readers, and it won't lock the table, just the affected rows.

Until you commit, all other sessions will continue to see the data as it was before your delete, and can continue to query.

The only blocking you'd see in another writer session is if there is a primary key or unique index on the table and it tries to insert a row which clashes; or it tries to update/delete a row that you deleted. That second session will wait until your first session issues a commit or rollback; the second session will then either get an error (if you committed) or complete the action (if you rolled back).


UPDATE id table on the columns with the same name

To fulfil the requirements, we have to first find the ID that we need - this can be found using the min() function, not surprisingly.  Then we do the update - the quickest to write is possibly the following:

UPDATE minupdate m
   SET id = (SELECT min(id)
               FROM minupdate
              WHERE name = m.name);

The backside of this is it can be very slow, if you have a bigger number of rows.  I have populated the table with some 50,000 random rows, using the following statement:

INSERT INTO minupdate (name, id)
SELECT translate(substr(i::text, 1, 1), '1234567890', 'ABCDEFGHIJ'),
       random() * 100000
FROM generate_series(1, 50000) t(i);

On my test box, without an index, this took looong minutes - I did not wait for it to produce an actual execution plan (using EXPLAIN (ANAYLZE, BUFFERS), but cancelled and run it with EXPLAIN to see what's wrong:

 Update on minupdate m  (cost=0.00..136044281.50 rows=87720 width=12)
   ->  Seq Scan on minupdate m  (cost=0.00..136044281.50 rows=87720 width=12)
         SubPlan 1
           ->  Aggregate  (cost=1550.87..1550.88 rows=1 width=4)
                 ->  Seq Scan on minupdate  (cost=0.00..1526.50 rows=9747 width=4)
                       Filter: (name = m.name)

So I've added an index in (name, id) to get some faster results:

 Update on minupdate m  (cost=0.00..417486.00 rows=50000 width=15) (actual time=1771.008..1771.008 rows=0 loops=1)
   Buffers: shared hit=394845 read=653 dirtied=654
   ->  Seq Scan on minupdate m  (cost=0.00..417486.00 rows=50000 width=15) (actual time=0.062..1317.366 rows=50000 loops=1)
         Buffers: shared hit=172641 read=192
         SubPlan 2
           ->  Result  (cost=8.31..8.32 rows=1 width=0) (actual time=0.018..0.020 rows=1 loops=50000)
                 Buffers: shared hit=171655 read=192
                 InitPlan 1 (returns $1)
                   ->  Limit  (cost=0.29..8.31 rows=1 width=4) (actual time=0.011..0.012 rows=1 loops=50000)
                         Buffers: shared hit=171655 read=192
                         ->  Index Only Scan using minupdate_name_id_idx on minupdate  (cost=0.29..8.31 rows=1 width=4) (actual time=0.006..0.006 rows=1 loops=50000)
                               Index Cond: ((name = m.name) AND (id IS NOT NULL))
                               Heap Fetches: 50000
                               Buffers: shared hit=171655 read=192

Now the problem is this: the inner part of the plan is executed 50,000 times - the number of rows to be updated.  In the index-less version, it would scan the whole table this may times - no wonder it would take very long.

But there is more to it.  Those 50,000 loops are ugly - let's find something nicer:

EXPLAIN (ANALYZE, BUFFERS)
UPDATE minupdate m
   SET id = mins.id
  FROM (SELECT name, min(id) AS id
          FROM minupdate
         GROUP BY name
       ) mins
WHERE m.name = mins.name;

This gives a more streamlined plan:

 Update on minupdate m  (cost=1861.76..4525.59 rows=91698 width=42) (actual time=995.845..995.845 rows=0 loops=1)
   Buffers: shared hit=241809 read=394 dirtied=843
   ->  Hash Join  (cost=1861.76..4525.59 rows=91698 width=42) (actual time=218.866..542.437 rows=50000 loops=1)
         Hash Cond: (m.name = mins.name)
         Buffers: shared hit=972 dirtied=427
         ->  Seq Scan on minupdate m  (cost=0.00..1402.98 rows=91698 width=8) (actual time=0.027..106.868 rows=50000 loops=1)
               Buffers: shared hit=486 dirtied=1
         ->  Hash  (cost=1861.65..1861.65 rows=9 width=36) (actual time=218.816..218.816 rows=9 loops=1)
               Buckets: 1024  Batches: 1  Memory Usage: 1kB
               Buffers: shared hit=486 dirtied=426
               ->  Subquery Scan on mins  (cost=1861.47..1861.65 rows=9 width=36) (actual time=218.738..218.790 rows=9 loops=1)
                     Buffers: shared hit=486 dirtied=426
                     ->  HashAggregate  (cost=1861.47..1861.56 rows=9 width=6) (actual time=218.722..218.740 rows=9 loops=1)
                           Group Key: minupdate.name
                           Buffers: shared hit=486 dirtied=426
                           ->  Seq Scan on minupdate  (cost=0.00..1402.98 rows=91698 width=6) (actual time=0.007..111.580 rows=50000 loops=1)
                                 Buffers: shared hit=486 dirtied=426
 Planning time: 0.164 ms
 Execution time: 995.908 ms

There are now two sequential scan nodes there (somewhat surprisingly), but both are executed only once (loops=1).

What I've done here is removing a correlated subquery, replacing it by a temporary view (the query after FROM), joining it to the table to be updated.  This approach often gives far better plans than the easy way.  On big tables the performance difference can be quite significant.


SqlPackager.exe what is SqlPermissionStatement?

I identify one reason of such entry in my project(SSDT).
I have following entry in code:

GRANT EXECUTE ON OBJECT::[dbo].[up_Foo] TO [$(Domain)\$(DBUserName)] AS [dbo];

Then in Deployment script I can find this:

GRANT EXECUTE ON OBJECT::[dbo].[up_Foo] TO NULL AS [dbo];

And related:

<Operations>
    <Operation Name="Create">
        <Item Value="Permission" Type="SqlPermissionStatement" />
    </Operation>
</Operations>

Therefore I believe I have some problem with CMDVARs substitution. What generates grant execute to null. And grant execute to null is always interpreted as new grant.


Strange sort behavior on a partitioned table with a prefix index

Do not use index prefixing; it is often more harmful than useful.  Especially for short string.

In a composite index, the Optimizer won't go beyond the prefix index.
Prefixing is somewhat useful when you can't make a full sized index.

Do not use PARTITION unless there is a good reason for it; your schema an queries do not show any use for partitioning.

This lists the only use cases I know of.

Do have INDEX(data, id).

Even without partitioning, this would be the best index for that SELECT.

Do have an explicit PRIMARY KEY on every InnoDB table.

(If this was a watered down version of the real schema, start a new Question without the simplifications.  There may be something useful to discuss.)


It is possible for you to do this yourself rather than using commercial products like dbShards or ScaleBase but you will end up building the same thing and it isn't trivial.

To answer your question though, it makes sense to replicate A and B across all shards so you either need your application to duplicate the writes across all shards or you will need some replication technology to do this. I don't think you can easily configure MySQL replication to do this.

To load balance you will need your client to randomly choose a shard when running queries against these tables, just like you'll need your client to determine which shard to use when accessing table C.

For HA you will want to have master/slave setup for each shard so that you can failover in the case of losing a master shard.

Regards,

Andy Grove
Chief Architect
http://www.dbshards.com


You can't use USE to change server context. You can use four-part naming for your objects, like Shawn suggested, if srvB is a linked server.

Another option is using SQLCMD mode, then you can say:

-- do stuff on server A
:CONNECT srvB
-- do stuff on server B

But that doesn't work well if you are trying to share data etc. between the two batches. For variables you can use SQLCMD variables instead.

More info here.


Updating log table in an effective manner

Do you need the count as you go along? If not,  drop the count column. Insert a row for each event as it happens. Aggregate the rows afterwards in some async batch process. Maybe have a monotonic system-generated number if you only want to touch each event once. If you use a datetime instead of an integer you can get aggregates across pages over time as well as over pages.
Up front I'm going to say I'm no expert on MySQL's internals. Take the following as unproven until you've researched the documentation and tested it in your own environment for yourself.
The type of workload you describe can be slow because each write requires the previous thread's write to complete so it knows what new value to write. If the previous thread wrote value 6 my thread has to read that 6 in order to increment it to 7, and the thread after me has to wait on my write so it can read 7 and increment it to 8. In the worst case, with a single page being served by all clients, the system becomes, essentially, single-threaded at this count.
By performing inserts instead of updates the inter-thread dependency is removed. Each thread can write its event when it's ready to. Throughput is limited only by the ability of the storage to persist bytes.
This approach treats the receiving table as a log. You want to choose an implementation which facilitates this. Likely there will be no indexes on this table as they increase the number of IOs required per event and are a further source of possible contention. Different storage engines have different support and performance characteristics for un-sequenced, store-it-anywhere-you-can writes.


I had exactly the same errors after installing a 32-Bit client (19.9.0.0.201020). Windows application event log stated an error concerning "C:\Windows\system32\ntdll.dll". This is a 64-Bit DLL with the 32-Bit counterpart in "C:\Windows\SysWow64".
This is the solution
You need to add C:\Windows\SysWow64 to the PATH environment variable. This can be done temporarily using:
SET PATH=%PATH%;C:\Windows\SysWow64

Furthermore you need to have 32-Bit Microsoft Visual C++ Runtime 2015-2019 installed. The latest Visual C++ Redistributable can be downloaded from:

The latest supported Visual C++ downloads (Microsoft | Support)

Hope this helps.


How to add 100 to all strings starting with a digit in an attribute

It assumes that value field length is always 5 and s is located always at end of the string.

update doc_properties
set value = concat(lpad(cast(left(value, 4) as signed) + 100, 4, '0'), 's')
where substr(value, 1, 1) between '0' and '9';

✓

select * from doc_properties;

id | doc_property_category | prop_id | value
-: | --------------------: | ------: | :----
 1 |                     1 |       1 | 0300s
 2 |                     1 |       1 | 1300s
 3 |                     1 |       1 | 0600s
 4 |                     1 |       1 | 2000s

db<>fiddle here


How to Load XML child nodes in to multiple tables

As I pointed out in my comment there is a missing </Data> tag in your example. But if the file has it, you can use the next command to extract XML values:

SELECT
    t2.lin.value('(./ACCT_NUM)[1]', 'int') as ACCT_NUM
    ,t3.lin.value('(./@CONTACT_TYPE)[1]', 'varchar(100)') as CONTACT_TYPE
    ,t3.lin.value('(./CONTACT_TOLL_FREE)[1]', 'varchar(100)') as CONTACT_TOLL_FREE
    ,t3.lin.value('(./CONTACT_ADDRESS_LINE_1)[1]', 'varchar(100)') as CONTACT_ADDRESS_LINE_1
    INTO #tmp
FROM
    txml
CROSS APPLY
    data.nodes('Extract/Request') as t2(lin)
CROSS APPLY
    t2.lin.nodes('Data/Contacts/Contact') as t3(lin);

NOTE: I've used a temporary table #tmp to store the returned values.

Then you can use this values to build/insert that values into the two tables:

SELECT
    ACCT_NUM
INTO
    #table1
FROM #tmp;

SELECT
    ACCT_NUM,
    CONTACT_TYPE,
    CONTACT_TOLL_FREE,
    CONTACT_ADDRESS_LINE_1
INTO
    #table2
FROM #tmp;

And this is the final result:

SELECT * FROM #table1;

SELECT * FROM #table2;

| ACCT_NUM |
| -------: |
|   111111 |
|   111111 |
|   222222 |
|   222222 |

ACCT_NUM | CONTACT_TYPE | CONTACT_TOLL_FREE | CONTACT_ADDRESS_LINE_1
-------: | :----------- | :---------------- | :---------------------
  111111 | CRM          | 800.123.4444      | PO Box 11111
  111111 | Collections  | 800.123.5555      | PO Box 22222
  222222 | CRM          | 888.123.6666      | PO Box 33333
  222222 | Collections  | 888.123.7777      | PO Box 44444

db<>fiddle here


Why does this execution plan have Compute Scalars?

The Compute Scalars are related to the computed field LineTotal. Script out the table, and you'll see that field defined as:

[LineTotal]  AS (isnull(([UnitPrice]*((1.0)-[UnitPriceDiscount]))*[OrderQty],(0.0))),

SQL Server has two operations to perform. It has to first run the calculation of:

[UnitPrice]*((1.0)-[UnitPriceDiscount]))*[OrderQty]

Then it has to check to see if that value is null, and if so, substitute 0.0.


Can MySQL Enterprise Backup perform partial backup/restore of views?

As per MySQL documentation here you can "partial" Restore of particular tables or tablespaces.

For your further ref here and here


SQL Trace (what Profiler is built on) will capture all instances of any events that you decide to collect. So if you collect RPC:Completed events, you'll get an event for every RPC completed, trace will be capturing that event. If you don't select that event, trace won't be grabbing it. So it will only capture what it is told to.

You can (and should) then further filter the data returned by adding filters on various columns (say a database name or a duration for a completed event to capture only events with a duration of greater than a certain value, for example). In that case, all of the instances of that event are still captured, but the filter is then applied before the information is dumped to the mechanism you are using to trace.

See the difference? If you don't select the event, that event isn't captured or sent (Emitted as you are saying, if I comprehend you correctly), but if you do select the event - all instances are captured but only those that pass the filter criteria are sent (emitted).

I would also argue against using profiler but consider doing a Server Side trace to a file. Look at this blog post from Jonathan Kehayias to show the difference in performance impact between the methods.


Attempting to kill the offending process failed with the same error.  In the end I had to reboot the server to get by the issue.


Mapping logins to linked servers for migration

You'll find the linked servers login mappings in sys.linked_logins view.
This, in turn accesses the master.sys.syslnklgns table (in case this gives something useful), but you can only access the real systems tables when you connect using the Dedicated Admin Connection (/A with sqlcmd.exe).


How to avoid redundant case/when in Postgres?

You should add a condition for every counter:

select
    sum(case when x = 1 and y = 1 then 1 else 0 end) as counter_x,
    sum(case when x = 1 and y = 2 then 2 else 0 end) as counter_y,
    sum(case when x = 1 and y > 2 then 7 else 0 end) as counter_z,
    sum(case when x = 1 then 1 else 0 end) as counter_a,
    sum(case when x = 2 and y = 1 then 10 else 0 end) as counter_b,
    sum(case when x = 3 and y = 30 then 400 else 0 end) as counter_c
from
    some_data;

counter_x | counter_y | counter_z | counter_a | counter_b | counter_c
--------: | --------: | --------: | --------: | --------: | --------:
        1 |         4 |         7 |         4 |        20 |         0

dbfiddle here


Limiting Domain Admins permissions on a SQL Server

By default domain admins don't have access to a SQL 2012 instance, because by default builtin\administrator (which are the local admins on the machine) are not a login in SQL Server itself.

The default has changed starting 2008

  By default, the local Windows Group BUILTIN\Administrator is no longer
  included in the SQL Server sysadmin fixed server role on new SQL
  Server 2008 installations.

So unless someone added them in SQL Server the local admins/domain admins shouldn't have permissions on the instance.

That doesn't mean they can't give themselves permissions, as some case of emergency solution they can start sql in single user mode and connect but that requires a server restart.

On the other hand, on 2008 R2 this trick would still work: Think Your Windows Administrators Don’t Have Access to SQL Server 2008 by Default? Think Again


A little late, but here we go...

If you can't add more disk space and can't promote a replica I think your only option (short of adding more disk space) is upgrading to MySQL 5.6 and using the online alter feature. The third-party tools that I know of all require a second copy of the table to be created before performing the swap. As you mentioned, the good news is that as per the Summary of Online Status for DDL Operations dropping a FK constraint should not require a full copy to be made.

I've had moderate success using the online alter feature but there are a couple caveats to be aware of:

There is no throttling of the operation and this can heavily load the server
Due to the above this can cause large replication spikes to replicas
Adding/dropping a column can cause primary key errors

The last caveat doesn't apply in this situation but it's a big gotcha.

I'd also be sure to carefully read the known limitations of the online alter feature too.


Poor performance of JOIN, WHERE, ORDER BY combination (MySQL)

With your datasets, MySQL has to obtain those 450,000 records from posts (in 1000 little chunks from each matching source_id), sort it, and then return the top 10. It is a costly exercise.

You could resort to using a stored procedure, and accumulate results going back in time, say daily or weekly, looping until obtaining at least 10 records, and then returning the 10 most recent ones. You'll need an index on posts by (date, source_id). It would return quickly for the most recently active users, but take much longer for users without recent posts. Something like the following:

DELIMITER ;;
CREATE DEFINER=CURRENT_USER PROCEDURE stored_procedure_name(u_id int)
BEGIN
  DECLARE fd DATE;
  DECLARE d DATE;

  SELECT MIN(date), MAX(date) INTO fd, d FROM posts;

  CREATE TEMPORARY TABLE last_posts (id int);

  WHILE d > fd AND (SELECT COUNT(*) FROM last_posts) < 10 DO
    INSERT INTO last_posts (id)
    SELECT p.id
    FROM relations AS r
    JOIN posts AS p ON (p.source_id = r.source_id AND
                        p.date > date_sub(d, interval 7 day) AND p.date <= d)
    WHERE r.user_id = u_id
    ORDER BY p.date DESC
    LIMIT 10;

    SET d = date_sub(d, interval 7 day);
  END WHILE;

  SELECT p.id, p.title, p.content, r.id AS rid
  FROM posts p
  JOIN relations AS r ON (r.source_id = p.source_id)
  WHERE p.id IN (SELECT * FROM last_posts)
  ORDER BY p.date DESC
  LIMIT 10;

  DROP TABLE last_posts;
END;;
DELIMITER ;

You could play with the intervals, reducing them to 1 day (more cycles with faster queries). Ensure you have an index on posts (date).


Disable log ERROR: duplicate key value violates unique constraint

You could use

INSERT ... ON CONFLICT DO NOTHING

and check if a row was inserted or not.


You can use user-defined variables for this.

In your first example this would be as simple as this:

SELECT
name, @rn := @rn + 1 AS ordinal
FROM
your_table
CROSS JOIN (SELECT @rn := -1) AS var_init_subquery
ORDER BY whatever_column;

Here we initialize the variable in another query. This is often more comfortable than doing this:

SET @rn := -1;
SELECT
name, @rn := @rn + 1 AS ordinal
FROM
your_table
ORDER BY whatever_column;

when you're not allowed to execute multiple statements in PHP or whatever.
The variables are session bound!
Further we initialize it here with -1 so that the first row will have value 0, because @rn + 1 is evaluated first.
Also note, that I added an ORDER BY clause. If you don't add it, your result is kind of meaningless, cause there's no order in a relational database unless you specify it. And the calculations done with any variables in the SELECT clause rely on the order of the rows.

Regarding your second example, here's just important, to take care of the order in the SELECT clause.

SELECT
name, @rn := @rn + 1 AS ordinal,
IF(@rn < 2, 'first', IF(@rn < 7, 'second', 'third')) AS prize
FROM
your_table
CROSS JOIN (SELECT @rn := -1) AS var_init_subquery
ORDER BY points DESC; /*or whatever_column*/


SQL Server Always On AG – Is a Passive Secondary Required for Automatic Failover?

You can fail over to a readable secondary.  The author is either wrong, or just using some confusing terminology.  Reading the exact quote you included, I'd lean towards the former explanation.  But I don't know all the context around that statement.
Here I have an AG - all replicas are configured to be readable when they are in the secondary role, and NODE2 is set up for automatic failover:

Here's the current state of things on the AG dashboard:

I'll go ahead and connect directly to NODE2 (sync secondary) and start some queries running:
SELECT COUNT_BIG(*)
FROM dbo.A;

WAITFOR DELAY '00:00:01';

GO 100

And then do something to cause an automatic failover to NODE2.  In this case, I will kill the SQL Server service on the current primary (NODE1).
Now I'll check the AG dashboard, and can see that NODE2 automatically became the new primary.

Note that during the failover, my SSMS window running queries on NODE2 got this message:

Msg 596, Level 21, State 1, Line 0
Cannot continue the execution because the session is in the kill state.
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command.  The results, if any, should be discarded.

Which is normal (clients being disconnected while the database undergoes recovery as part of becoming the primary).  I retried the query and it continued running.  I wouldn't have to do that if I were connecting to the listener via read-only intent.


Option 1: You can create a reference table (Month, Quarter) and you can extract month from your date column and join with it. (Joining this table won't affect performance as PI will be month)

Mth| Quarter
1  | 1
2  | 1
3  | 1
4  | 2
5  | 2
6  | 2
7  | 3
8  | 3
9  | 3
10 | 4
11 | 4
12 | 4

Option 2: Use a logic as (2+Month)/3 as in

SELECT FLOOR((2+EXTRACT(MONTH FROM DATE_CLM))/3)


Recently, we set up log shipping to an off-site server and it seemed to be working great. The LSBackup job runs every 15 minutes.

You should change the maintenance plan and just take full and diff (if required). Since logshipping is taking log backups every 15 mins, you dont need hourly log backup.

  but my question is do I need to remove all log shipping, restore the secondary database from the most recent full backup and then set up log shipping again?

No since your log shipping is working fine. Just remove additional log backups from maintenance plans since it can break log shipping.

  Or, can I pause the LS jobs, to the restore and then restart the LS jobs?

As a caution, you can pause all the jobs, take a diff backup and then start your log backup, copy and restore jobs one by one.

Just remove the maintenance plan log backup job.

Much better to use Ola's backup solution rather than using maintenance plans.


Is db_executor a standard role in SQL Server 2012 R2?

db_executor is not a default role (MS has a list of default roles), it would have been created by somebody.

There's also no SQL Server 2012 R2. Potentially you are getting confused with Windows Server 2012 R2.


How can I get SQL Server restore start and end time information?

1.The restore history information is readily available inside the msdb i.e. msdb.dbo.restorehistory

You can use below T-SQL code to find the start time and restore information of a database over a required period.

DECLARE @dbname sysname, @days int
SET @dbname = NULL --substitute for whatever database name you want
SET @days = -1 --previous number of days, script will default to 30
SELECT
 rsh.destination_database_name AS [Database],
 rsh.user_name AS [Restored By],
 CASE WHEN rsh.restore_type = 'D' THEN 'Database'
 WHEN rsh.restore_type = 'F' THEN 'File'
 WHEN rsh.restore_type = 'G' THEN 'Filegroup'
 WHEN rsh.restore_type = 'I' THEN 'Differential'
 WHEN rsh.restore_type = 'L' THEN 'Log'
 WHEN rsh.restore_type = 'V' THEN 'Verifyonly'
 WHEN rsh.restore_type = 'R' THEN 'Revert'
 ELSE rsh.restore_type
 END AS [Restore Type],
 rsh.restore_date AS [Restore Started],
 bmf.physical_device_name AS [Restored From],
 rf.destination_phys_name AS [Restored To]
 FROM msdb.dbo.restorehistory rsh
 INNER JOIN msdb.dbo.backupset bs ON rsh.backup_set_id = bs.backup_set_id
 INNER JOIN msdb.dbo.restorefile rf ON rsh.restore_history_id = rf.restore_history_id
 INNER JOIN msdb.dbo.backupmediafamily bmf ON bmf.media_set_id = bs.media_set_id
 WHERE rsh.restore_date >= DATEADD(dd, ISNULL(@days, -30), GETDATE()) --want to search for previous days
 AND destination_database_name = ISNULL(@dbname, destination_database_name) --if no dbname, then return all
 ORDER BY rsh.restore_history_id DESC

Alternatively you can use the approach of default trace as mentioned by Chris as well.


Are CREATE INDEX statements replicated in row-based Mysql Replication?

DDL can still replicate with row-based replication

From the MySQL Documentation for binlog-do-db

  For example, DDL statements such as CREATE TABLE and ALTER TABLE are always logged as statements, without regard to the logging format in effect

Since CREATE INDEX is mapped to ALTER TABLE, CREATE INDEX will work just fine.


AWS RDS MariaDB replication to GCP Cloud SQL or Azure SQL DB

I don't think it can be done solely in RDS, at least not directly between 2 DBaaS platforms.  Both vendors would need interoperability in their APIs.  I think your stuck standing up a Linux/Windows server in AWS/GCP/Azure to be a "on-prem" host to work in between the DBaaS platforms.

This may be helpful: http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/MySQL.Procedural.Exporting.NonRDSRepl.html

Of course...

  Replication to an instance of MySQL running external to Amazon RDS is only supported during the time it takes to export a database from a MySQL DB instance. The replication should be terminated when the data has been exported and applications can start accessing the external instance.

Hopefully you need to do this for a migration, and not ongoing replication, or you don't care about being in a "supported" configuration.


One of the problems with using collections in SQL is that the optimizer isn't able to guess how many elements the collection has.  It defaults to assuming that the collection has a few thousand elements (I want to say 4k elements but I wouldn't wager on that).  If that is roughly 1,000 times more elements than you have in your actual collection, that's will certainly tend to cause the optimizer to make poor choices about the query plan.

The best way to alleviate that problem is to use the CARDINALITY hint.

SELECT /*+ cardinality(a 4) */ a.col1, b.col2, b.col3
  FROM
  TABLE(FOO.my_table) a
  INNER JOIN existing_table b ON (b.existing_table_id = a.existing_table_id)
  --b.existing_table_id - primary key supported by unique index
  ORDER BY a.existing_table_id

tells the optimizer to assume that the collection aliased to a has only 4 elements which should cause it to pick a more appropriate query plan.


SQL Server FILELISTONLY command providing incorrect logical database name

So what it sounds like is happening is:

You create a database called foo with a logical data file name of foo.
You back up that database.
You restore that database as a different database (say, bar).
You backup the bar database.
You try to restore the bar backup as yet another database name, and your code assumes that the logical file name for the data file for the database bar should be bar. But it's not, it's still foo!

You could also interject some random ALTER DATABASE x MODIFY FILE (name = 'y', newname = 'z'); in there to make it even more confusing for you (never mind your code). Particularly if - like it turns out is true in your case - you are storing multiple backups in a single backup file. This can be bad regardless of whether it is the same database but the logical file names change over time, or different databases being backed up to the same file. You need to determine which file number you're actually looking for, and specify that in FILELISTONLY's WITH FILE = option. Better yet, stop reusing the same file for multiple backups.

You should be sure that when you restore a new database based on the backup of another, you use WITH MOVE to give the logical file names meaningful physical paths, and then use MODIFY FILE immediately afterward to make the logical names themselves meaningful.

To help figure out where the actual discrepancy is coming for a specific case you are experiencing (I couldn't reproduce a case where RESTORE FILELISTONLY showed different values than the restore UI in SSMS), you could provide a .bak file on some file sharing service, and we could look ourselves. Your description of what the file names are and what they should be is extremely hard to follow.


SQL Server Express Freezes for a couple minutes

There is couple of important point missed

Please check whether Auto Close option is set to true for database. I have seen lots of similar issue where Auto close option was culprit.

Is OS resifing on Virtual machine or Physical ?. If its is virtual look for any VM snapshot backup happeing this might also cause freezing.

Other thing can be, do you have backup taken for  database using third party tool/windows native backup  if so these tools freeze data files for couple of sec to sync metadata and take consistent backup.And you can see VIRTUAL_DEVICE in errorlog when you specifically look ta backup information in it.

Hope this helps


Your CTE:

(
  SELECT a.gid,
         ... AS the_geom
  FROM ...
)

produces a table with 2 columns, named gid and the_geom. But the "wrap" has an obvious glitch, it only has one column:

WITH data (the_geom) AS

So it says that the table will be named data and the first column (re)named to the_geom. Now you have 2 columns named the_geom! You could have the same effect - but more clear to spot - if you had written:

WITH data (the_geom, the_geom) AS

The solution is simple. Either remove the column list from that part:

WITH data AS
  ( ...
  )

or (re)name both columns explicitly:

WITH data (gid, the_geom) AS           -- explicit
(
  SELECT a.gid,
         ... AS the_geom
  FROM ...
)
INSERT INTO public.g100_wby_lakes_r (gid,geom)
SELECT gid, the_geom
FROM data
WHERE st_GeometryType(the_geom) = 'MULTIPOLYGON' ;


Is hosting the back end in the cloud supposed to significantly slow performance?

Well, if the backend is sitting on an external server, your network speed now plays a major part in the connection. Accessing local files off harddisk is fast, but accessing it of an internet connection is slower.

Try testing your app using a faster internet connection and see if there are differences. If there are minimal differences, then it could be the server hosting your app that is slowing it down. But my main bet will be the internet.


Dealing with Oracle ARCHIVELOG during huge updates

Of course. Do not perform an UPDATE at all. Just add it as new column with the default value of HELLO.

It took 0.12 seconds and generated an insignificant amount of redo in my sandbox:

SQL> select force_logging from v$database;

FORCE_LOGGING
---------------------------------------
YES

SQL> set timing on
SQL> select count(*) from t1;

  COUNT(*)
----------
1000000000

Elapsed: 00:00:14.34

SQL> select sn.name, ss.value from v$statname sn
     join v$sesstat ss on (sn.statistic# = ss.statistic#)
     where ss.sid = sys_context('userenv', 'sid') and sn.name = 'redo size';

NAME            VALUE
---------- ----------
redo size        5964

Elapsed: 00:00:00.00

SQL> alter table t1 add my_meta varchar2(20 char) default 'HELLO' not null;

Table altered.

Elapsed: 00:00:00.12
SQL> select sn.name, ss.value from v$statname sn
     join v$sesstat ss on (sn.statistic# = ss.statistic#)
     where ss.sid = sys_context('userenv', 'sid') and sn.name = 'redo size';

NAME            VALUE
---------- ----------
redo size       20408

SQL> select count(*) from t1 where my_meta = 'HELLO';

  COUNT(*)
----------
1000000000

Elapsed: 00:01:00.96


CROSS APPLY query with CASE and running totals

The following will work on sql-server 2012+. Note that there were a couple of inconsistencies between your supplied SQL and the sample schema that you provided and I think I have followed the names in your SQL.

With CTE_CalcTotalDisc As (
    Select d.SaleID, s.Amount, d.Discount, d.Tipo, d.Seq,
        T_Disc = iif(d.Tipo = 'V', d.Discount, (s.Amount * d.Discount)),
        CurrentValue = iif(d.Tipo = 'V', s.Amount - d.Discount, iif(d.Tipo = 'P', s.Amount - (s.Amount * d.Discount), 0))
      From #disc As d
      Join #sales As s On d.SaleID = s.SaleID
      Where d.Seq = 1
    Union All
    Select d.SaleID, Amount = cte.CurrentValue, d.Discount, d.Tipo, d.Seq,
        T_Disc = iif(d.Tipo = 'V', d.Discount, (cte.CurrentValue * d.Discount)),
        CurrentValue = iif(d.Tipo = 'V', cte.CurrentValue - d.Discount, iif(d.Tipo = 'P', cte.CurrentValue - (cte.CurrentValue * d.Discount), 0))
      From #disc As d
      Join CTE_CalcTotalDisc As cte
        On d.SaleID = cte.SaleID
        And d.Seq = cte.Seq + 1),
CTE_Ordered As (
    Select SaleID, CurrentValue,
        Discount = Sum(T_Disc) Over (Partition By SaleID Order By Seq Asc),
        RowNo = Row_Number() Over (Partition By SaleID Order By Seq Desc)
      From CTE_CalcTotalDisc)
--Select * From CTE_CalcTotalDisc
Select * From CTE_Ordered Where RowNo = 1;

Note the use of Row_Number(), partitioned by SaleID and sorted descending by Seq to get the final value, and the use of Sum() to get the running total of the discount.

You can uncomment the commented select statement to view all rows, not just the final row for each SaleID.


For absolute performance, is SUM faster or COUNT?

You mostly answered the question yourself already. I have a few morsels to add:
In PostgreSQL (and other RDBMS that support the boolean type) you can use the boolean result of the test directly. Cast it to integer and SUM():
SUM((amount > 100)::int))

Or use it in a NULLIF() expression and COUNT():
COUNT(NULLIF(amount > 100, FALSE))

Or with a simple OR NULL:
COUNT(amount > 100 OR NULL)

Or various other expressions. Performance is almost identical. COUNT() is typically very slightly faster than SUM(). Unlike SUM() and like Paul already commented, COUNT() never returns NULL, which may be convenient. Related:

Query optimization or missing indexes?

Since Postgres 9.4 there's also the aggregate FILTER clause. See:

Return counts for multiple ranges in a single SELECT statement

It's faster than all of the above by around 5 - 10 %:
COUNT(*) FILTER (WHERE amount > 100)

If the query is as simple as your test case, with only a single count and nothing else, you can rewrite:
SELECT count(*) FROM tbl WHERE amount > 100;

... which is the true king of performance, even without index.
With an applicable index it can be faster by orders of magnitude, especially with index-only scans.
Benchmarks
Postgres 13
db<>fiddle here
Basically the same results as for Postgres 10 below.
(I added a test without the new parallelism.)
Postgres 10
I ran a new series of tests for Postgres 10, including the aggregate FILTER clause and demonstrating the role of an index for small and big counts.
Simple setup:
CREATE TABLE tbl (
   tbl_id int
 , amount int NOT NULL
);

INSERT INTO tbl
SELECT g, (random() * 150)::int
FROM   generate_series (1, 1000000) g;

-- only relevant for the last test
CREATE INDEX ON tbl (amount);

Actual times vary quite a bit due to background noise and specifics of the test bed. Showing typical best times from a bigger set of tests. These two cases should capture the essence:
Test 1 counting ~ 1 % of all rows
SELECT COUNT(NULLIF(amount > 148, FALSE))            FROM tbl; -- 140 ms
SELECT SUM((amount > 148)::int)                      FROM tbl; -- 136 ms
SELECT SUM(CASE WHEN amount > 148 THEN 1 ELSE 0 END) FROM tbl; -- 133 ms
SELECT COUNT(CASE WHEN amount > 148 THEN 1 END)      FROM tbl; -- 130 ms
SELECT COUNT((amount > 148) OR NULL)                 FROM tbl; -- 130 ms
SELECT COUNT(*) FILTER (WHERE amount > 148)          FROM tbl; -- 118 ms -- !

SELECT count(*) FROM tbl WHERE amount > 148; -- without index  --  75 ms -- !!
SELECT count(*) FROM tbl WHERE amount > 148; -- with index     --   1.4 ms -- !!!

db<>fiddle here
Test 2 counting ~ 33 % of all rows
SELECT COUNT(NULLIF(amount > 100, FALSE))            FROM tbl; -- 140 ms
SELECT SUM((amount > 100)::int)                      FROM tbl; -- 138 ms
SELECT SUM(CASE WHEN amount > 100 THEN 1 ELSE 0 END) FROM tbl; -- 139 ms
SELECT COUNT(CASE WHEN amount > 100 THEN 1 END)      FROM tbl; -- 138 ms
SELECT COUNT(amount > 100 OR NULL)                   FROM tbl; -- 137 ms
SELECT COUNT(*) FILTER (WHERE amount > 100)          FROM tbl; -- 132 ms -- !

SELECT count(*) FROM tbl WHERE amount > 100; -- without index  -- 102 ms -- !!
SELECT count(*) FROM tbl WHERE amount > 100; -- with index     --  55 ms -- !!!

db<>fiddle here
The last test in each set used an index-only scan, which is why it helped for counting one third of all rows. Plain index or bitmap index scans cannot compete with a sequential scan when involving roughly 5 % or more of all rows.
Old test for Postgres 9.1
To verify I ran a quick test with EXPLAIN ANALYZE on a real life table in PostgreSQL 9.1.6.
74208 of 184568 rows qualified with the condition kat_id > 50. All queries return the same result. I ran each like 10 times in turns to exclude caching effects and appended the best result as note:
SELECT SUM((kat_id > 50)::int)                      FROM log_kat; -- 438 ms
SELECT COUNT(NULLIF(kat_id > 50, FALSE))            FROM log_kat; -- 437 ms
SELECT COUNT(CASE WHEN kat_id > 50 THEN 1 END)      FROM log_kat; -- 437 ms
SELECT COUNT((kat_id > 50) OR NULL)                 FROM log_kat; -- 436 ms
SELECT SUM(CASE WHEN kat_id > 50 THEN 1 ELSE 0 END) FROM log_kat; -- 432 ms

Hardly any real difference in performance.


How to match column names and values from joining and related tables?

With the information you gave, I think what you're trying to achieve is a Pivot table. I'd use CASE to make the pivot with your same query:

SELECT
    cars.id as car_id,
    cars.make as car_make,
    IFNULL(GROUP_CONCAT(CASE WHEN features.id=1 THEN car_feature.value END),'') as color,
    IFNULL(GROUP_CONCAT(CASE WHEN features.id=2 THEN car_feature.value END),'') as Wheels,
    IFNULL(GROUP_CONCAT(CASE WHEN features.id=3 THEN car_feature.value END),'') as Doors
FROM cars
LEFT JOIN car_feature ON (cars.id = car_feature.car_id)
LEFT JOIN features ON (car_feature.feature_id = features.id)
GROUP BY cars.id;

Test:

mysql> SELECT
    -> cars.id as car_id,
    ->     cars.make as car_make,
    ->     IFNULL(GROUP_CONCAT(CASE WHEN features.id=1 THEN car_feature.value END),'') as color,
    ->     IFNULL(GROUP_CONCAT(CASE WHEN features.id=2 THEN car_feature.value END),'') as Wheels,
    ->     IFNULL(GROUP_CONCAT(CASE WHEN features.id=3 THEN car_feature.value END),'') as Doors
    -> FROM cars
    -> LEFT JOIN car_feature ON (cars.id = car_feature.car_id)
    -> LEFT JOIN features ON (car_feature.feature_id = features.id)
    -> GROUP BY cars.id;
+--------+----------+--------+--------+-------+
| car_id | car_make | color  | Wheels | Doors |
+--------+----------+--------+--------+-------+
|      1 | Ford     | Blue   | Alloy  |       |
|      2 | Audi     | Yellow |        | 4     |
+--------+----------+--------+--------+-------+
2 rows in set (0.00 sec)

mysql>

Try it on this Fiddle


if so how much time is needed for the application to connect to the lsitener

Since you said HostRecordTTL is set to 1200 seconds, the application will take up to 20 minutes to reconnect after a failover that crosses subnets.
Ideally you should set RegisterAllProvidersIP = 1, and update your application's connection string to include MultiSubnetFailover = True.  This will provide the best failover experience.
If your application uses a driver that doesn't support this setting, or you can't change the connection string for some other reason, and you need to reduce the failover timeout, then you'll need to lower the HostRecordTTL setting.


Why can't SQL Server find a seek plan? Is this a bug?

For the original query in question optimizer considers(*) two main alternatives normally (when FORCESEEK hints aren't used on any of the tables).

First one is simple join

when indexes from both tables are scanned entirely (without predicates), and predicate a.Id = b.Id AND (a.H = 'A' OR b.H = 'C') is tested at join node.

Second one is apply form (more about it here)

when index on one of the tables is scanned on the outer side of the Nested Loops join, and then index data is used on the inner side to seek into clustered index on the other table using b.Id = a.Id seek predicate and additional a.H = 'A' OR b.H = 'C' predicate. It can be expressed in T-SQL as

SELECT *
FROM Test1 a
    CROSS APPLY (
        SELECT *
        FROM Test2 b
        WHERE b.Id = a.Id AND (a.H = 'A' OR b.H = 'C')
    ) appl

When FORCESEEK is used on one of the tables, simple join alternative falls out from consideration, but optimizer additionally considers modified form of apply

which unions two seeks into clustered index on the other table. One with b.Id = a.Id seek predicate and additional b.H = 'C' predicate. And the other with b.Id = a.Id seek predicate beyond Filter with a.H = 'A' startup predicate. It can be expressed in T-SQL as

SELECT *
FROM Test1 a
    CROSS APPLY (
        SELECT DISTINCT u.Id, u.H
        FROM (
            SELECT b.Id, b.H
            FROM Test2 b
            WHERE b.Id = a.Id AND a.H = 'A'
            UNION ALL
            SELECT b.Id, b.H
            FROM Test2 b
            WHERE b.Id = a.Id AND b.H = 'C'
        ) u
    ) appl

There are more alternatives actually (that use spooling on the inner side of the apply, for example, or different physical join implementation for simple join, or non-clustered index scan instead of clustered index scan, or vice versa, etc.), but above execution plans shapes are quite representative.

When FORCESEEK is used on both tables, no new alternatives appear. Moreover, apply alternatives become rejected after consideration due to seek requirement on both tables.

So, I think we can say, that possible implementations of the original written form of the query require FORCESEEK requirement to be relaxed for at least one of the tables.

You have another equivalent query, but discovering such alternative is not implemented in the current version of query optimizer unfortunately. This is not a bug though, just imperfection.

Notice also that you add FORCESEEK to convince optimizer to seek in the non-clustered index, but in the above cases optimizer understands it in its own way and performs seek over clustered index instead. Rewriting query, when its performance is not satisfactory, is one of the first (and right) things to try often.

(*) One may find it out by analyzing final memo structure and applied transformations (using undocumented trace flags 8615, 8619 and 8621).


Safe method to rename a crashed myisam table

This might work; do it with caution:

CREATE TABLE new LIKE real;   -- copy schema
RENAME TABLE real TO bad, new TO real;  -- replace broken table with empty
REPAIR TABLE bad;     -- slowly repair
RENAME TABLE real TO old, bad TO real;  -- replace empty table with fixed table
DROP TABLE old;   -- clean up

I don't know if the CREATE will work with a broken real.

The RENAMEs are 'instantaneous'.

Then schedule moving to InnoDB.


@spthorn's answer is correct but IN gives sometimes unexpected results when the columns involved are nullable.

If you want to avoid such surprises, it's better to use NOT EXISTS or the LEFT JOIN / IS NULL structure you already have. The change you need is minimal. Just add the s.status_message = 'Delivered' condition in the joining ON clause:

SELECT
    m.id,
    m.date_completed
    -- ,s.status_message      -- no need for this either
FROM
   main m
   LEFT JOIN status_history s ON  s.main_id on m.id
                              AND s.status_message = 'Delivered'
WHERE
    m.date_completed IS NOT NULL
    AND s.main_id IS NULL ;

The NOT EXISTS version would be:

SELECT
    m.id,
    m.date_completed
FROM
   main m
WHERE
    m.date_completed IS NOT NULL
    AND NOT EXISTS
        ( SELECT *
          FROM status_history s
          WHERE s.main_id = m.id
            AND s.status_message = 'Delivered'
        ) ;


Update:

So the package is not encrypted. As Billinkc said, it is zipped as a .ispac file.

These .ispac files first have to be exported from the database, then extracted into their components, which can be one or more .dtsx packages.

Microsoft provide a powershell script that will export every .ispac from your database:

https://gallery.technet.microsoft.com/scriptcenter/ImportExport-projects-bca5f29f

<#
.SYNOPSIS
    Import folders and projects from local file system to SSIS catalog.
.DESCRIPTION
    Import SSIS projects from $ProjectFilePath. The folders under $ProjectFilePath
    will be imported as folders in the SSIS catalog, with all ispac files under that
    folder imported accordingly.

    The script will connect to the local SQL Server instance. It will first
    drop the SSISDB catalog if exists and create a new catalog with a fixed secret.
.EXAMPLE
    .\CatalogImport
#>

# Variables
$ProjectFilePath = "C:\SSIS"

# Load the IntegrationServices Assembly
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Management.IntegrationServices") | Out-Null;

# Store the IntegrationServices Assembly namespace to avoid typing it every time
$ISNamespace = "Microsoft.SqlServer.Management.IntegrationServices"

Write-Host "Connecting to server ..."

# Create a connection to the server
$sqlConnectionString = "Data Source=localhost;Initial Catalog=master;Integrated Security=SSPI;"
$sqlConnection = New-Object System.Data.SqlClient.SqlConnection $sqlConnectionString

# Create the Integration Services object
$integrationServices = New-Object $ISNamespace".IntegrationServices" $sqlConnection

Write-Host "Removing previous catalog ..."

# Drop the existing catalog if it exists
if ($integrationServices.Catalogs.Count -gt 0) { $integrationServices.Catalogs["SSISDB"].Drop() }

Write-Host "Creating new SSISDB Catalog ..."

# Provision a new SSIS Catalog
$catalog = New-Object $ISNamespace".Catalog" ($integrationServices, "SSISDB", "SUPER#secret1")
$catalog.Create()

write-host "Enumerating all folders..."

$folders = ls -Path $ProjectFilePath -Directory

if ($folders.Count -gt 0)
{
    foreach ($filefolder in $folders)
    {
        Write-Host "Creating Folder " $filefolder.Name " ..."

        # Create a new folder
        $folder = New-Object $ISNamespace".CatalogFolder" ($catalog, $filefolder.Name, "Folder description")
        $folder.Create()

        $projects = ls -Path $filefolder.FullName -File -Filter *.ispac
        if ($projects.Count -gt 0)
        {
            foreach($projectfile in $projects)
            {
                $projectfilename = $projectfile.Name.Replace(".ispac", "")
                Write-Host "Deploying " $projectfilename " project ..."

                # Read the project file, and deploy it to the folder
                [byte[]] $projectFileContent = [System.IO.File]::ReadAllBytes($projectfile.FullName)
                $folder.DeployProject($projectfilename, $projectFileContent)
            }
        }
    }
}

Write-Host "All done."

Fill out the connection string, and all your packages will get saved to C:\SSIS

From here, the packages need to be extracted, which I did using python:

import os
import zipfile
import pandas as pd
from sqlalchemy import create_engine
import urllib

conn_str = (
r'Driver=ODBC Driver 13 for SQL Server;'
r'Server=NynyaBeeswax;'
r'Database=Really_Cool_DB;'
r'Trusted_Connection=Yes;')

quoted_conn_str = urllib.parse.quote_plus(conn_str)
engine = create_engine('mssql+pyodbc:///?odbc_connect={}'.format(quoted_conn_str))
cnxn  = engine = create_engine('mssql+pyodbc:///?`odbc_connect{}'.format(quoted_conn_str)).execution_options(autocommit=True)`

directory = 'C:\\SSIS\\'
directory_to_extract_to = 'D:\\Defeintely_not_local\\SSIS_Packages\\'

for subdir, dirs, files in os.walk(directory):
    for filename in files:
        if filename.endswith(".ispac"):
            #print(filename)
            #print (subdir)
            #print(dirs)
            folder = subdir.split('\\')[2]

            target_directory = directory_to_extract_to + folder

+ '\\'

            with zipfile.ZipFile(subdir + '\\' + filename, 'r')

as zip_ref:
                zip_ref.extractall(target_directory)
                #print(target_directory)
        else:
            continue

for subdir, dirs, files in os.walk(directory_to_extract_to):
    for file in files:

        if file.endswith('.dtsx'):

            folder = subdir.split('\\')[5]

            sql = """insert into pkg_xml_data (subdir,

package_name, bulkcolumn, loaddate)
            SELECT '{}','{}', CONVERT(XML, BulkColumn) AS

BulkColumn, GETDATE()
            FROM OPENROWSET(BULK '{}\\{}', SINGLE_BLOB) AS

x;""".format(folder,file, subdir, file)

        #print(filename)
            print(sql)
            #engine.execute(sql)


Upgrade scenario: Oracle 8.0.5.0.0 to Oracle 12c?

There are multiple solutions to this problem but the driving issues are cost, allowable downtime and complexity.

Golden Gate could be purchased with a one year license and will do a zero downtime upgrade from Oracle 8i to 12. You still have to upgrade from 8.05 to 8i.
Your life could be simplified by using VmWare P2V to convert the machine to a virtual machine without any downtime.  This method allows you to:

get your production database on better hardware which should improve the speed of most operations
allows you to make a copy of the machine, put it on a different subnet and start testing various strategies

I have imported schemas from our Oracle 8i database directly into Oracle 11.2 without issues.  Your mileage may vary depending on character sets, licensed features and any unorthodox strategies your programmers have in the PL/SQL code.

One method that could be easily tested:

buy a new server with some serious hardware like 32GB RAM, install VMWare Esxi
convert your old machine to a virtual
create a development copy of the Oracle 8.05
install another virtual machine with Oracle 12
install another virtual machine with Oracle 10XE or 11XE
take snapshots of all the machines with no changes
take an export of the 8.05 development, import into Oracle 10XE or 11XE, test and verify
data pump to the 12 database
verify the data on 12 and offline the 8.05 and oracle 10XE or 11XE

This method needs downtime. A weekend should be more than adequate and only costs you a new server, Oracle 12 licence and some VMWare software licenses.  Keep in mind that testing and verification can take more time than importing the data.


Looking for a scalable relational database

I suggest you take a very close look at this :

http://codahale.com/you-cant-sacrifice-partition-tolerance/

It explains fairly well why "stall all reads and writes" is nothing more than an inevitable logical consequence if you want partitions and the data they contain to be consistent.


Do I gain read performance improvement by using zlib rather than snappy compression in MongoDB?

The documentation at https://docs.mongodb.com/manual/core/wiredtiger/#memory-use says that collection data is only compressed on disk, so uncompressed in memory. Indexes keep their prefix compression in memory, but not the block-level compression.

So changing compression algorithms is not going to directly reduce the amount of memory needed to keep a read-heavy workload efficient (i.e. to make sure its working set stays in RAM) unless you are already memory-starved in which case better compression may help by reducing IO as the storage system is thrashed because any data for the DB in the OS's buffers & cache will be compressed. The only way to tell for sure is to benchmark a realistic workload on production-like data in a test environment with each combination of options you are considering.

The "unless you are already memory-starved" is significant here though: once you are in that state the best the compression is likely to do is improve your performance from very very slow to just very slow.

One exception to the above would be any query that needs to read a data-set that is too large to fit into any practical amount of memory, in which case you may see significant improvements: all the data the query needs will need to be read from the IO subsystem anyway, and the compression is likely to help this. We'd need to know a lot more about your application's data and workloads to give specific hints as to whether this would have a noticeable effect in your case, and even then the only way to be sure is, again, to run benchmarks.

NOTE: this is not the case for all databases. For example with MS SQL Server's compression options the data in pages in RAM are compressed just as they are on disk. This reduces RAM use by the buffer pool at the expense of CPU time on every read of each page. When data is uncompressed in RAM the decompression CPU hit is only experienced as the data is loaded from disk so won't affect subsequent reads until that page/block/document is evicted because it hasn't been referred to recently.

  Can I blindly...

The short answer is: No.

A longer answer is: Nnnnnoooooooooooooooooooooooooooooooooooooooooooooooooo...

Less facetiously: blindly doing anything on a production database and/or application is dangerous so it is never a recommended course of action. Never do this if you value your users and your own sanity. Always test in a dev/test environment first no matter how much you trust any source of advice that suggests otherwise. It may feel like wasting time when you test and find no ill effects, but you will at some point in your career be very glad of having applied due diligence and saved yourself from a nasty and embarrassing event on a application in production! If you don't follow due diligence in this way, you may experience unplanned CV update events.


CONCURRENT SELECT's SLOW .... LOCK IN SHARE MODE

INDEX(type, stamp) should speed up "Select 1" significantly, even without LOCK IN SHARE MODE.

(Please provide SHOW CREATE TABLE for further discussion.)

Edit

Even though you have type and stamp in indexes, having these two fields in this order will be optimal for "Select 1":  INDEX(type, stamp).  This is because the optimizer can drill into the BTree to the end of the "type='string'" values and find the MAX.  Instantly.  Only one rows is fetched.  And it is only fetched in the index.

With PRIMARY KEY(type, other stuff) it needs to scan 6M rows to find the answer.  COM_KEY is of no help (for this SELECT).

After this query is sped up, the other queries may be less of a problem.


How can I get the MySQL super_read_only setting to work?

This seems to be a consequence of MySQL Bug#79328.  I'm assuming you started MySQL Server with either command-line or configuration file options to enable --super_read_only mode.  Because of the referenced bug, the --super_read_only option is not properly processed at server startup.  Until this is fixed (hopefully soon!), --super_read_only must to be set dynamically.


Does it mean that former primary replica (A) will become primary replica automatically?

No, all that means is when your replica comes back into the picture, and when the Availability Group database gets back in a SYNCHRONIZED state that it would be failover ready.  That operation will not happen automatically.  You indeed would have to either do this "failback" manually, or engineer a way to automate this (rather simple, let me know if you want to explore those options).

From a high-level view, your listing of steps is complete after step #3.


The role comments are stored in the "pg_catalog.pg_shdescription" table.


Why does MariaDB still use the "mysql" name everywhere within the file system?

MariaDB is a forked version on MySQL that attempts to be a drop-in replacement.  A good drop-in replacement should work without requiring a lot of configuration and data file movement.  By using the same directories and filenames, it is far simpler to install as a replacement for MySQL.

I haven't tried MariaDB, but I would expect to be able to install it test it, and decide if I want to revert back to MySQL.  While I would backup my databases, I don't expect to have to restore my data whichever option I pick.


SQL Server 2012/2014 insufficient memory available in the buffer pool

I don't think there's much mystery to the the errors occurring with SQL 2012 Standard as that edition only supports 64GB of memory.  Set your max server memory to 64GB and this error should disappear for your SQL 2012 instances.

As for SQL 2014 Standard Edition, the max server memory limit increased to 128GB, so I'll echo Sean Gallardy's question in the comments and ask what else you're running on that VM or if you've over-provisioned the VM host by accident?


That's because by not specifying the FILE parameter of RESTORE DATABASE, it is defaulting to 1 which will be the 1st backup set on that media.  See the section on this page of Specifying a Backup Set.

Instead of:

restore database YourDb
from disk = 'C:\yourpath\backupfile.bak';
go

You will want to do:

restore database YourDb
from disk = 'C:\yourpath\backupfile.bak'
with file = <n>;
go

Where N is the backup set number in the media.  N can be obtained from running RESTORE HEADERONLY on the media.  The column you will be concerned with is Position to use with the FILE option.


Best solution to fixing database design with GUID as primary key

You certainly need NOT to cluster on the GUID.  If you have something which would allow you to uniquely identify records other than that GUID, I'd suggest you look at building an unique index on that other field & making that index clustered.  If not, you are free to cluster on other fields, even using nonunique indexes.  The approach there would be to cluster however best facilitates splitting your data up and querying - so, if you've got a "region" field, or something, that might be a candidate for your clustering scheme.

The problem with changing to a BIGINT would be additions to the data from other databases & integrating their database into the central store.  If this is not a consideration - and will never be a consideration - then, yes, the BIGINT would solve the index rebalancing issue nicely.

Behind the scenes, if you don't specify a clustered index, SQL Server does much the same thing: it creates a row ID field & maps all other indexes into that.  So, by doing it yourself, you're solving it just as SQL would solve it.


pg_restore: error: could not read from input file: end of file

You really have two questions here.  First you have a corrupted tar file.  I'd blame that on your usage of docker.  If you run tar -tf ... on the docker side before moving the file, was it corrupted there?

Second, you don't want to restore data, you want to merge it.  pg_dump/pg_restore doesn't do that.  You will have to figure out what you want to do (overwrite, aggregate, assign new PKs and just insert them as new rows) and then implement that. You likely want to use COPY or \copy to get the data out of the first system so the second one can see it.  After that, it is really up to you what you do with it.  You can implement the merging in python or Perl or SQL (after \copy into staging tables) or whatever your favorite tool is.


1.Why the initial size is 3MB for PRIMARY file and 1MB for LOG If the documentation clearly says that It should be 8MB for versions above 2016 and 1MB for anything lower (I'm on this category as I'm using 2014)

The initial size is going to be whatever Model is set to, in this case your model is 3 MB. Who knows why, could be a host of reasons which in the end aren't very important to be honest. Set Model to the starting size you'd like for all new databases or specify the size in the create database command/window.

  2.I understand that a log file could grow to 2TB maximum but why the Model database says unlimited and the STACK database says limited to 2TB?

They are equivalent, I wouldn't get hung up on this. Most likely the way the GUI is coded, honestly not even worth making a connect item about.

  3.Assuming that the actual default size is indeed 3MB and 1MB, why on the disk, I see 2240 KB and 560 KB?

Model is set to autogrow by 1 MB, so it's 2.2 MB right now... I'm going to guess at 2 MB it grew by 1 MB. The size on disk and the metadata sizes aren't going to match up unless you have a database that is completely and utterly devoid of free space. The log has a size it can't be smaller than, which you're at right now.


Table1 ID is auto increment and i also want to store the information from table 2 against that auto generated ID

You want to update field ID and set to 10?

Some sample:

UPDATE test.personal_info
SET ID = '10'
WHERE db1.table1 = db2.table2

OR

 UPDATE test.personal_info
 SET ID = '10'
 ON <DB.TABLE>
 JOIN TABLE1.FIELD = DB.TABLE2.FIELD


HAVING STATEMENT error SQL command not properly ended

The HAVING clause is the cause of the error as it not syntactically correct. You have (added parentheses to show how it is parsed):

HAVING (SUM(..) BETWEEN x AND y) = 0

and the expression inside the parentheses returns a boolean result.

Additionally, the SUM(INV.INVDATE) doesn't make much sense. What result should one expect if we add dates?

And as @mustaccio correctly noticed, the HAVING clause goes after GROUP BY, not before.

It seems you want something like this in the HAVING clause:

HAVING SUM(CASE WHEN TO_DATE(INV.INVDATE) BETWEEN TO_DATE('01-Oct-13')
                                              AND TO_DATE('30-Sep-15')
           THEN INV.SALES ELSE 0
           END) = 0

which I would prefer rewritten as:

HAVING SUM(CASE WHEN INV.INVDATE >= DATE '2013-10-01'
                 AND INV.INVDATE < DATE '2015-10-01'
             THEN INV.SALES ELSE 0
           END) = 0

The INV.INVDATE should be removed from the GROUP BY clause as well and the TWO_YEARS and PROGRESS expressions should also be corrected as the  SUM(...) expression suggested SUM(CASE WHEN ... THEN x ELSE y END) instead of CASE WHEN ... THEN SUM(x) ELSE y END.


Can't figure out how to update a column in one table based on multiple boolean values in another table in postgresql

If a milestone can have multiple tasks (which is highly likely), then your statement will only update the taskcompleted for the "last" task processed in the query (the "last" is undefined here though).

If you want to check all tasks for a milestone to be completed you need consider all taskcompleted combined with an AND condition. This can be done using the boolean aggregate function bool_and().

So get evaluate the "all completed", you would need something like this:

select md.milestoneid,
       bool_and(t.taskcompleted) as all_completed
FROM task t
  JOIN milestonedependency md ON t.taskid = md.taskid
GROUP by m.milestoneid

This will return a flag for each milestoneid if all tasks have been completed. null values will be ignored (as with all aggregate functions).

To use this in an update statement, you can use something like this:

UPDATE milestone
   SET milestonecompleted = tmp.all_completed
FROM (
  select md.milestoneid,
         bool_and(t.taskcompleted) as all_completed
  FROM task t
    JOIN milestonedependency md ON t.taskid = md.taskid
  GROUP by md.milestoneid
) tmp
WHERE tmp.milestoneid = milestone.milestoneid
  AND milestone.milestoneid = 1;

You can make this more efficient by pushing the condition on the milestoneid into the derived table so that only rows with that milestoneid are aggregated:

UPDATE milestone
   SET milestonecompleted = tmp.all_completed
FROM (
  select md.milestoneid,
         bool_and(t.taskcompleted) as all_completed
  FROM task t
    JOIN milestonedependency md ON t.taskid = md.taskid
  GROUP by md.milestoneid
  WHERE md.milestoneid = 1
) tmp
WHERE tmp.milestoneid = milestone.milestoneid;


How to join all combinations from two tables with a common column

Using SQL Server, because I do not have Access installed, hopefully this is generic enough to be useful to you:

Sample tables and data

CREATE TABLE SupplierCountry
(
    SupplierName varchar(50) NOT NULL,
    CountryName varchar(50) NOT NULL
);

INSERT SupplierCountry
    (SupplierName, CountryName)
VALUES
    ('Supplier A', 'USA'),
    ('Supplier A', 'France'),
    ('Supplier A', 'China');

SupplierCountry

╔══════════════╦═════════════╗
║ SupplierName ║ CountryName ║
╠══════════════╬═════════════╣
║ Supplier A   ║ USA         ║
║ Supplier A   ║ France      ║
║ Supplier A   ║ China       ║
╚══════════════╩═════════════╝

SupplierFactory

CREATE TABLE SupplierFactory
(
    SupplierName varchar(50) NOT NULL,
    CountryName varchar(50) NOT NULL
);

INSERT SupplierFactory
    (SupplierName, CountryName)
VALUES
    ('Supplier A', 'UK'),
    ('Supplier A', 'Germany');

╔══════════════╦═════════════╗
║ SupplierName ║ CountryName ║
╠══════════════╬═════════════╣
║ Supplier A   ║ UK          ║
║ Supplier A   ║ Germany     ║
╚══════════════╩═════════════╝

Query:

SELECT
    SC.SupplierName AS Supplier,
    SC.CountryName AS SupplyCountry,
    SF.CountryName AS FactoryCountry
FROM SupplierCountry AS SC
JOIN SupplierFactory AS SF
    ON SF.SupplierName = SC.SupplierName;

Output:

╔════════════╦═══════════════╦════════════════╗
║  Supplier  ║ SupplyCountry ║ FactoryCountry ║
╠════════════╬═══════════════╬════════════════╣
║ Supplier A ║ USA           ║ UK             ║
║ Supplier A ║ France        ║ UK             ║
║ Supplier A ║ China         ║ UK             ║
║ Supplier A ║ USA           ║ Germany        ║
║ Supplier A ║ France        ║ Germany        ║
║ Supplier A ║ China         ║ Germany        ║
╚════════════╩═══════════════╩════════════════╝


Postgres 11 - Fetching results after a transactiom

From what I understand, in order to use the cursor after your procedure ends, you need to be on the same transaction you was when calling the procedure.
Your code use two different transactions: the first one is started with BEGIN just before calling the procedure, and it ends at the COMMIT. The second transaction starts automatically after the COMMIT in your procedure and continue until the COMMIT after your FETCH.


Is Oracle Configuration Manager HIPAA compliant?

Yes, as long as the data being transmitted is monitored in a "reasonable" way.
If the database hosts PHI and oracle is assisting in the management of the database you must have a written contract with the vendor.

Standard: Business associate contracts and other arrangements. A covered entity, in accordance with §164.306, may permit a business associate to create, receive, maintain, or transmit electronic protected health information on the covered entity's behalf only if the covered entity obtains satisfactory assurances, in accordance with §164.314(a) that the business associate will appropriately safeguard the information.
Document the satisfactory assurances required by paragraph (b)(1) of this section through a written contract or other arrangement with the business associate that meets the applicable requirements of §164.314(a).

You have to log the vendors access into the database and ensure they cannot access PHI.

(A) Authorization and/or supervision (Addressable). Implement procedures for the authorization and/or supervision of workforce members who work with electronic protected health information or in locations where it might be accessed.
(C) Log-in monitoring (Addressable). Procedures for monitoring log-in attempts and reporting discrepancies.
(B) Access authorization (Addressable). Implement policies and procedures for granting access to electronic protected health information, for example, through access to a workstation, transaction, program, process, or other mechanism.
(a)(1) Standard: Access control. Implement technical policies and procedures for electronic information systems that maintain electronic protected health information to allow access only to those persons or software programs that have been granted access rights as specified in §164.308(a)(4).
(b) Standard: Audit controls. Implement hardware, software, and/or procedural mechanisms that record and examine activity in information systems that contain or use electronic protected health information.

If they access PHI you will have to log the incident and report it.
Please see http://www.hhs.gov/ocr/privacy/hipaa/administrative/privacyrule/adminsimpregtext.pdf page 38 for more information on HIPAA IT policy's.
The thing about HIPPA is that most of the requirements are vague and it asks you to make "reasonable" steps to prevent a PHI information breach (It marks these items as addressable). I've been through a couple of HIPAA audits myself.