Why is an index I created not being used in a query execution?

Thanks everyone for your help. I think i've/we've answered the questions.

  Why isn't the optimizer using my covering index? Is it because it thinks it's cheaper to scan the whole table?

Because the optmizer has to read most rows in table, it's preferring the clustered index.

  I noticed if i change the query to just fetch a.ListingId and not the rest of the columns from a, it uses my index (and the reads/cost seems much lower, due to avoiding the index scan). Should I use this in a subquery then?

No. Optimizer is doing the right thing. Forcing an index hint proves that, when looking at cost difference.

  Why is the query optimizer doing an clustered index scan and not a seek?

See first answer

  This query is the one existing in an index view. When the index view needs to be updated, is this the actual query that runs to update itself? My thinking was yes, which is why I'm trying to optimize it. If the index view update doesn't use this query, then I won't bother?

As requested, I split this out into a different question


I just reviewed the replication on one of my clients systems and I see similar linked servers both on the publisher and the distributor.  In my case the linked servers are all setup using the "Be made using the login's current security context".

If you've got the linked servers setup using a specific security context that's probably because the machines aren't on the same domain.  If they are, and the rights are setup correctly you can try changing things.  The linked servers should only be used when making changes to the replication configuration so if there's a problem you may not see it until later.


An example:

CREATE TABLE test (id INT,
                   val INT);
GO

CREATE TRIGGER tr
ON test
AFTER INSERT, UPDATE
AS
UPDATE test
SET val = 123     -- custom value which must be set instead of NULL
                  -- maybe any expression
                  -- and even an output of the subquery
WHERE EXISTS ( SELECT NULL
               FROM INSERTED
               WHERE id = test.id
                 AND val IS NULL );
GO

INSERT INTO test VALUES (1,1), (2,null), (3,3);
SELECT * FROM test;
GO

id | val
-: | --:
 1 |   1
 2 | 123      -- NULL is replaced with custom value
 3 |   3

UPDATE test SET val = NULL where id = 1;
SELECT * FROM test;
GO

id | val
-: | --:
 1 | 123      -- NULL is replaced with custom value
 2 | 123
 3 |   3

db<>fiddle here


2 B-tree indices OR 1 GiST index on tsrange -- which will perform better?

Timestamps with B-tree index
I suggest a third option: as long as your table holds two timestamp columns (which seem to be defined NOT NULL) I would use a single multicolumn index with opposed sort order (if no other considerations apply):
CREATE INDEX reservations_range_idx ON reservations using gist(starts_at, ends_at DESC);

More in these related answer:

Optimizing queries on a range of timestamps (two columns)
PostgreSQL index not used for query on range

As for the query, have a look at the SQL-standard operator OVERLAPS:
SELECT * FROM reservations
WHERE (starts_at, ends_at) OVERLAPS ('2014-01-01 00:00:00', '2014-01-03 00:00:00');

More in this related question on SO:

Find overlapping date ranges in PostgreSQL

Should be faster than two B-tree indices. Less disk space and cheaper to maintain. The burden on write operations is small
Range type with GiST index
With big tables, a GiST index on a range type is most probably faster, because it scales better. Storage on disk is considerably bigger and index maintenance a bit more expensive, though.
If you go that route, it would be more efficient to store your timestamp as range (tsrange or tstzrange) to begin with. A plain GiST index without the functional aspect is a bit faster.
CREATE TABLE reservation (
  reservation_id serial PRIMARY KEY
 ,span tsrange
 , ...
);

CREATE INDEX reservation_span_gist_idx on reservations USING GiST (span);

With the && "overlap" operator you already displayed in the question:
SELECT *
FROM   reservation
WHERE  span && ('2014-01-01 00:00:00', '2014-01-03 00:00:00');

Also, you might be interested in an exclusion constraint to rule out overlaps by design, which implements a GiST index like the one above automatically. There is a code example in the manual. This related answer on SO has more details:

Preventing adjacent/overlapping entries with EXCLUDE in PostgreSQL


The Instant Client is easier to install and more lightweight. It doesn't even need to be installed, you can just unzip it and use it. You don't need an Oracle Home either.

On the other hand, it does not support deprecated features like Oracle Names. If you don't need any feature the instant client omits, I suggest you use it. It looks like you are covered by the list of what is supported:

  Instant Client can be used to run your OCI, OCCI, Pro*C, JDBC, and ODBC applications without installing a full Oracle Client. In addition, Instant Client supports SQL*Plus. As of Instant Client 10.2, it is also possible to develop applications for OCI and OCCI using the Instant Client SDK download.


InnoDB MySQL table with a trigger that inserts into a MyISAM table

Inserting into a MyISAM table may take a table lock on that table.  Since that INSERT is coming from a Trigger on a InnoDB table, it is inside a transaction.  So, if the table lock takes a long time, the transaction takes a long time, hence "lock wait timeout".

The exception on the table lock is if there have been no updates or deletes from the MyISAM table and the PK is AUTO_INCREMENT.  (Or something like that.  Dig in the docs for the specifics.)

Is it possible to add to audit outside the InnoDB transaction?

You say "full table lock requests" -- which table?  What is the specific text?

Another thing that can mess up inserting a MyISAM table -- a big SELECT.  Perhaps you do a big 'report' twice a day?

Rethink whether to use MyISAM for audit.


Will stopping my query rollback all my queries

Not unless you wrapped those calls in the same transaction, otherwise those previously ran updates are committed already.


Sounds like you need a history table for room changes that should be made most likely through the app layer.  I know it's tempting to use built in triggers to just have it log it, but a simple app layer should do the trick.

If you're really concerned about that and you have the CDC option feel free to use that, but I would go with a transaction that logs the current record elsewhere, updates it, and commits with rollback error handling if it fails and a nice error for the end user to take some other step for the customer in the mean time while this error is looked at.

Edit:
Updating based on question.  To do this, you could write a stored procedure that you call from your app which:

-begins a transaction

-copies the current hotel number and occupant id along with a timestamp to a 'getRoomChangeHistory' type stored procedure

-then issue a 'updateRoomChange' type stored procedure so the new value is updated

-Finally COMMIT the transaction.  Add Add error handling for failures.  If you want a solid auditable way of doing it, this is it.  If you want easy, it won't be dependable.  For that, why not just save it as a document store somewhere else then if you don't care about ACID compliance?

Here's some more on the matter for SQL 2012 and above using TRY CATCH from Books OnLine to achieve this:
https://msdn.microsoft.com/en-us/library/ms175976.aspx


Postgres CREATE DATABASE already has data and tables

You probably created some tables (with data) in the database template1.

Connect to that database (e.g.: psql postgres template1) and drop everything you don't need there.

More details in the manual: http://www.postgresql.org/docs/current/static/manage-ag-templatedbs.html


Error 9002. The transaction log for database is full due to 'NOTHING'

NOTHING is basically a 'Not any of the other reasons' status. Other Log Full status values I've seen are: LOG_BACKUP, REPLICATION, and CHECKPOINT.

As to the cause, check the log file max size & growth properties. Then compare them to the available disk free space. The log file may have reached the Max size, or the disk may not have enough free space to accommodate the next growth interval/amount.


What advantages does in-memory OLAP have over traditional systems with significant memory?

Not my field of expertise but as I understand it the difference in the majority of so-called in-memory OLAP databases (not a term I'm fond of, it's used as marketing pitch more than as a fair comparison of technologies) is column store indexes.

Column-Stores vs Row-Stores (How Different Are They Really) is a good intro to the technology if you're familiar with traditional OLTP and OLAP database structures.


What is the best way to represent an album and song entity in an e-commerce schema?

There really is not a One True Answer, but there are plenty of samples available on the web.  One site is: http://www.databaseanswers.org/data_models/

This is actually a site with many simple data models. I would not call these a complete solution, but it is a good resource for getting started.

For a CD Collection there is a sample model, that focuses on content, not on the commercial side of selling content.

http://www.databaseanswers.org/data_models/cd_collection/index.htm

In addition if you search for something like example database model music you will find other examples.


Blocked sessions waiting with PAGELATCH_* wait types?

I previously assumed that SQL Server would only report a blocking session in the blocking_session_id column, if the blocked sessions were waiting for a logical lock and not anything else such as a PAGELATCH_*.

You are using Adam Machanic's sp_WhoIsActive procedure, not a built-in SQL Server facility. Adam's procedure reports all 'interesting' causes of blocking, not just lock blocking. The underlying information comes from various sources, including sysprocesses, sys.dm_exec_requests, and sys.dm_os_waiting_tasks.

A task can wait for other things besides a lock. It may be able, for example, to acquire an exclusive lock on a row on a page, but then still have to wait to acquire an exclusive page latch (because other tasks have latched the same page in an incompatible mode).

In your example, the PAGELATCH_EX exclusive page latches are on regular data or index pages, not PFS, GAM, SGAM, DCM, or BCM pages, as indicated by the (*) at the end of the wait_info.

You can find the documentation (blog entries) for sp_WhoIsActive here.


How to automatically Reset Query Cache for improve table cache hit in MySQL?

The query cache is only helpful for low write, high read workloads and should be pretty handy on a blog with this access pattern. If you issue any DML then you invalidate the QC for that table and repopulation would be necessary. Have you calculated the QC efficiency based on your local status?

http://www.mysqlperformanceblog.com/2006/07/27/mysql-query-cache/

The table cache is a different animal. It caches tables in memory so that MySQL can avoid the overhead of opening them. There's a recipe for tuning this, which is something along the lines of depth of longest join * max_connections but I doubt this us causing you trouble. It's likely that you would be better off tuning the key_buffer (MyISAM's cache for indexes) or the Innodb_buffer_pool_size (InnoDB cache for data + indexes) to reduce load on the server.

http://www.mysqlperformanceblog.com/2009/11/16/table_cache-negative-scalability/


Database in postgresql with missing rows, how to fill the missing rows with a backup copy?

You can restore all affected tables into a different schema, there are a variety of ways in which you can do that (several good ideas in https://stackoverflow.com/questions/4191653/i-want-to-restore-the-database-with-a-different-schema).

Once the table is in a separate schema, you can compare them by primary key, and insert the missing ones, like so for each affected table:

INSERT INTO production.table
SELECT b.*
FROM backup.table b
LEFT JOIN production.table p ON (p.pkey = b.pkey)
WHERE p.pkey IS NULL;

You will just need to find out the list of affected tables, restore their backup in a separate schema, determine what are their primary keys, create the script with the inserts, and run it.


unless they are not corrupted,You can use the files to restore databases.With out restoring you cant say about usability of those files..

If you are able to restore them with out any issues

1.First take backup of what you restored
2.Do a DBCC CHECKDB to check for any signs of data corruption


Remove some auto created indices from Entity Framework?

Foreign keys should be indexed as a best practice. If you're not using them now, you can disable them or drop them, but you may want to use them later. The impact on performance is negligible in most cases if you keep them.

I'd leave them as is.


How to relate the middle table with One or Many relationship?

If a user can have no more than a single role, and this will ever change, then put role_id directly on the user table, then the relationship between role table and user table is a single role to many users.


create table foo
(
    some_date date not null check (some_date > date '1998-02-01');
);

More details in the manual:

http://www.postgresql.org/docs/current/static/datatype-datetime.html
http://www.postgresql.org/docs/current/static/ddl-constraints.html#DDL-CONSTRAINTS-CHECK-CONSTRAINTS


The default isolation level is controlled through default_transaction_isolation and is read committed in a standard installation.
You can check your current setting using:
show default_transaction_isolation;

As Postgres does not support (or implement) read uncommitted there is no need to change this "away from read uncommitted".
If you want to change the isolation level to "higher" one, even in autocommit mode, you can use:
set default_transaction_isolation = serializable;

The above will change the default for the current session (=connection).


What happens to existing MySQL databases when I upgrade my Ubuntu distro?

Your databases will remain untouched (though of course I can't be liable :) ).

The reason are are being asked for a password could be:

Your new Ubuntu version uses a newer version of MySQL (it does). It needs root password so as to run the mysql_upgrade utility, which upgrades internal mysql schema to fit new version
You new installation includes new packages/dependencies, for which a new schema should be created. Such packages could be phpmyadmin, cacti, bugzilla, wordpress or any others which may require use of a database schema.

You should be in particular comfortable with this upgrade since this is from one LTS (Long Term Support) version to another. Not that I would expect database problems with other upgrades, but still, LTS versions are expected to be more "trusted".

Just to conclude, I am using Ubuntu. Usually installing MySQL from external binaries, but sometimes from package. I've never heard of a "data gone lost" problem due to an upgrade.


Which databases can meet these replication requirements?

Having done this type of project before, here's some of the practical gotchas to look out for:

"2. This master then does one-way slave propagation to up to 200 read-only servers via VPN."

Think carefully about the network traffic required here. In the simplest terms, if the insert/update/delete load on the database takes up 1/200th of a network cable's throughput, and if your master server only has one network connection, it's going to be saturated. (In practical terms, it'd be saturated long before that since you also need to accommodate query loads and backups.)

Next, think about the logging requirements. Every transaction has to be logged, and the logged changes get sent to the read-only servers as they happen. If any one read-only server drops offline, then the master has to retain all of the logs until the read-only server comes back online and consumes the changes. In a high-volume change environment like you're describing, this can easily be a higher size than the original data set itself (since some records can get updated over and over, or get deleted.) You need the ability to recognize when a replica has gotten farther behind than it can easily catch up, and then the ability to reinitialize that replica from backup/restore rather than replication synchronization.

Since you want to be able to fail over from one master to another, then every master would need to be aware of the replication synchronization state of every read-only replica.

With these limitations, you can see why you usually want to separate the work of distribution out onto other servers. You want masters handling writes, but then just batching off the changes to a set of distributor servers that are responsible for tracking replication synchronization states across many read-only servers. (This is how Microsoft SQL Server's transactional replication works, and I would imagine most other DBMS's have similar distributor architectures available as well.)


Can I take backup of those tables which are created with NOLOGGING in Oracle?

You can. From backup (rman) perspective there is no difference between LOGGING and NOLOGGING tables.

What you cannot do is restore through NOLOGGING operation. That means that if recover procedure will encounter such operation in archive log it is currently applying table will be marked as INVALID.


You are doing a cartesian product here:

...
FROM dbo.[DataSource], @ids i

you need to know which address_id corresponds to which user, and for that you must have a clause. Something like this will do it:

INSERT INTO dbo.[Users]
        (
          first_name,
          surname ,
          address_id
        )

SELECT  first_name, surname, Address.address_id,
FROM dbo.[DataSource]
INNER JOIN dbo.Address ON Address.address = DataSource.Address

A further consideration; this will work well as long as you don't have two users with the same address, or if it is desirable that two users with the same address have the same address_id.


Is it necessary to delete a database before restoring the backup in MySQL?

Read the first few lines of the dump -- Does it say DROP DATABASE or anything like that?

(There are many options on mysqldump that control whether drops, creates, etc, are emitted into the dump, so there is no single answer to this question.)


How to get one row out of many rows with same column id

You can return a comma separated list of values by using the string_agg function.

https://docs.microsoft.com/en-us/sql/t-sql/functions/string-agg-transact-sql?view=sql-server-ver15


aggregate column of nvarchar2 and obtain result in nvarchar2 too

As the documentation describes:

  The return data type is RAW if the measure column is RAW; otherwise
  the return value is VARCHAR2.

So unless you pass the column as RAW, the result will be VARCHAR2, and that is not good enough, because that means data loss during conversion.

LISTAGG works with VARCHAR2 or RAW, so to simply put it, you need to convert everything to RAW, then convert the result back to NVARCHAR2.

Below is an example:

SQL> select parameter, value from nls_database_parameters where parameter like '%CHARACTERSET';

PARAMETER                      VALUE
------------------------------ ------------------------------
NLS_CHARACTERSET               EE8ISO8859P2
NLS_NCHAR_CHARACTERSET         AL16UTF16

My default characterset does not support the € sign.

SQL> create table t1 (id number, c1 varchar2(20), c2 nvarchar2(20));

Table created.

SQL> insert into t1 values (1, 'A', 'A');

1 row created.

SQL> insert into t1 values (2,  NCHR(8364),  NCHR(8364));

1 row created.

SQL> commit;

Commit complete.

SQL> select * from t1;

        ID C1 C2
---------- -- --
         1 A  A
         2 ?  €

As you can see, i get a ? instead of it using VARCHAR2.

If I simply try to use LISTAGG on the NVARCHAR2 column, data will be lost during conversion:

select
  listagg(c2, ',') within group (order by id) as list1
from t1;

LIST1
----------
 A, Ź

If I try to cast the result, still not good enough, as data was lost during the conversion to VARCHAR2:

select
  cast(listagg(c2, ',') within group (order by id) as nvarchar2(20)) as list2
from t1;

LIST2
----------
 A, Ź

The remaining option is, to convert to RAW:

select
  utl_raw.cast_to_nvarchar2(listagg(utl_raw.cast_to_raw(c2), utl_raw.cast_to_raw(',')) within group (order by id)) as list3
from t1;

LIST3
----------
AⰠ

Converting a VARCHAR2 delimiter will mess up the data, I need to specify the delimiter also in NVARCHAR2, so instead of ',', I have to use N',':

select
  utl_raw.cast_to_nvarchar2(listagg(utl_raw.cast_to_raw(c2), utl_raw.cast_to_raw(N',')) within group (order by id)) as list4
from t1;

LIST4
----------
A,€


Error when running sp_BlitzFirst from First-Responder-Kit-20170307

You have databases in compatibility level 2005, which isn't supported.


How to execute dependent multiple SQL queries from multiple databases via same PHP web page?

You shouldn't need two connections for this as the databases in your code are on the same MySQL instance and the DB user apparently has grants to use both.

All you have to do is prefix your table with the database name in the SQL statements, as you have already done. You can use the same connection for both. (It doesn't matter which database is specified in the connection parameters, I suspect this parameter can even be empty.)


The PRINT statement will print nothing if the expression evaluates to a null. Apparently @team ends up being null as the result of the preceding statement, causing the PRINT to output nothing. If you want to print something even when the variable is null, use ISNULL or COALESCE:

PRINT 'value is ' + ISNULL(@team, '(null)');

You could also add a filter to the SELECT to ensure that NULLs are skipped (if you want them skipped, that is):

SELECT @team = TEAM
FROM DISTRIB_LINE
WHERE TEAM IS NOT NULL;

However, if the table has no rows or if all TEAM values are null, this statement will not do any assignment. In this case, if PRINT still outputs nothing, it means @team was null even before the SELECT. Therefore, you may still need to apply the previous method.

Besides, it is not a good idea to assign a variable this way when a table has many rows, because you have no control of which value will ultimately be assigned. Therefore. Alexei has a point there as well with regard to using TOP, as well as combining it with ORDER BY and, possibly, additional filters.


Mysql join heavy query on large tables taking hours

It may (or may not) be possible to add some composite indexes to speed up the query.  It may (or may not) be possible to reformulate the query to speed it up -- perhaps by turning subqueries into JOINs.

But it seems like 3M possible updates will lead to a very slow query in any case.  I suggest chunking the UPDATE into no more than 1000 rows at a time.  This will avoid various locks that interfere with other traffic.  And it may actually run faster -- due to not needing to hang onto millions of rows for potential ROLLBACK.

Discussion of chunking:  http://mysql.rjweb.org/doc.php/deletebig#deleting_in_chunks

If you want me to check your indexes, please provide SHOW CREATE TABLE.


There are some tricks to make r1/r2 follow new parent, but generally I see them as tricks, which means I wouldn't really depend on them in production environment.

You might want to check this blogpost: http://www.pateldenish.com/2010/11/possibility-of-graceful-switchover.html


Does changing `wal_keep_segments` require a restart?

For checking the variable's context, you can execute the following query:

SELECT name, context FROM pg_settings WHERE name = '<variable name';

In the case of wal_keep_segments, the context is sighup which means that only requires a server reload. You can use pg_ctl reload from the shell prompt or select pg_reload_conf() from psql or database client.

In the latest version, there are 7 types of context. internal context means that only can be modified at compilation time, postmaster means that a service restart is needed, the others are more session/backend specific.


Handling modifications of fields within a document

I have rarely seen a use case where nested documents were a valid way to go. Sooner or later, any combination of the following the problems occur:

The BSON size limit of 16MB / doc is reached.
Exceeding the padding size of the documents leads to frequent (costly) document migrations.
The more complicated a model gets, the more complicated data manipulation gets - leading to longer maintenance and development times.

My suggestion would be to have independent user and client documents and look them from the client side. This way, a user can even have billions of clients and you still won't run into one of they mentioned problems.

Allowing the user to modify the persisted fields is pretty much a nightmare from the developers point of view. How do you display a decent input view? Or the output? You'd either have to parse the user specific structure and construct both views accordingly or you'd have to store meta information. I'd probably go with the approach to allow them to store additional fields in independent documents like this (which ironically is a use case for embedding documents ;) ):

{
  _id: new ObjectId(),
  user: owningUser,
  // If you want different fields for different customers
  customer: appliedTo,
  fields:[
    {name:"Date of Birth", type:"Date", value:ISODate("2015-05-05T03:46:00-01:00")},
    {name:"discount", type:"Float", value: 7.5}
  ]
}

This way, you'd have the user limited to some 16MB of additional data per customer and you have an idea for how to parse the data with a rather simple model. Setting indices on user and customer should make this efficient, too.


Not directly, no. What you describe is kind of like expecting your e-mail program to know when to open and display new e-mail, even though the program needs to be open already to check for new mail.

You might be able to do this with a trigger on the table itself - however note that triggers can be disabled, and also that some operations (such as BULK INSERT) can intentionally bypass triggers.

CREATE TRIGGER dbo.ShouldIEnableJob
ON dbo.TableName
FOR INSERT
AS
BEGIN
  IF EXISTS (SELECT 1 FROM inserted)
  BEGIN
    UPDATE msdb.dbo.sysjobs
      SET enabled = 1
      WHERE name = N'your job name'
      AND enabled = 0;
  END
END

(This doesn't follow your requirement of checking once an hour, but rather it responds to the first insert. You'll also want to drop the trigger once the job has been enabled, but I don't know that you'll be able to do that from within the trigger.)

You could probably create a different job that wakes up every hour. It would check the data, and if it is time to enable the other job, it would do so (similar to the trigger above) and also disable itself. You could also schedule something similar outside of SQL Server (e.g. using PowerShell, VBScript via Windows Task Scheduler, etc.).

Maybe Event Notifications could assist in a similar way, but I've never used them for this specific purpose.


First, lets assume that (id) is the primary key of the table. In this case, yes, the joins are (can be proved) redundant and could be eliminated.

Now that's just theory - or mathematics. In order for the optimizer to do an actual elimination, the theory has to have been converted into code and added in the optimizer's suite of optimizations/rewritings/eliminations. For that to happen, the (DBMS) developers must think that it will have good benefits to efficiency and that it's a common enough case.

Personally, it doesn't sound like one (common enough). The query - as you admit - looks rather silly and a reviewer shouldn't let it pass review, unless it was improved and the redundant join removed.

That said, there are similar queries where the elimination does happen.
There is a very nice related blog post by Rob Farley: JOIN simplification in SQL Server.

In our case, all we have to do in change the joins to LEFT joins. See dbfiddle.uk. The optimizer in this case knows that the join can be safely removed without possibly changing the results. (The simplification logic is quite general and is not special-cased for self-joins.)

In the original query of course, removing the INNER joins cannot possibly change the results either. But it's not common at all to self-join on the primary key so the optimizer does not have this case implemented. It's common however to join (or left join) where joined column is the primary key of one of the tables (and there is often a foreign key constraint). Which leads to a second option to eliminate the joins: Add a (self referencing!) foreign key constraint:

ALTER TABLE "Table"
    ADD FOREIGN KEY (id) REFERENCES "Table" (id) ;

And voila, the joins are eliminated! (tested in the same fiddle): here

create table docs
(id int identity primary key,
 doc varchar(64)
) ;
GO

✓

insert
into docs (doc)
values ('Enter one batch per field, don''t use ''GO''')
     , ('Fields grow as you type')
     , ('Use the [+] buttons to add more')
     , ('See examples below for advanced usage')
  ;
GO

4 rows affected

--------------------------------------------------------------------------------
-- Or use XML to see the visual representation, thanks to Justin Pealing and
-- his library: https://github.com/JustinPealing/html-query-plan
--------------------------------------------------------------------------------
set statistics xml on;
select d1.* from docs d1
    join docs d2 on d2.id=d1.id
    join docs d3 on d3.id=d1.id
    join docs d4 on d4.id=d1.id;
set statistics xml off;
GO

id | doc
-: | :----------------------------------------
 1 | Enter one batch per field, don't use 'GO'
 2 | Fields grow as you type
 3 | Use the [+] buttons to add more
 4 | See examples below for advanced usage

--------------------------------------------------------------------------------
-- Or use XML to see the visual representation, thanks to Justin Pealing and
-- his library: https://github.com/JustinPealing/html-query-plan
--------------------------------------------------------------------------------
set statistics xml on;
select d1.* from docs d1
    left join docs d2 on d2.id=d1.id
    left join docs d3 on d3.id=d1.id
    left join docs d4 on d4.id=d1.id;
set statistics xml off;
GO

id | doc
-: | :----------------------------------------
 1 | Enter one batch per field, don't use 'GO'
 2 | Fields grow as you type
 3 | Use the [+] buttons to add more
 4 | See examples below for advanced usage

alter table docs
  add foreign key (id) references docs (id) ;
GO

✓

--------------------------------------------------------------------------------
-- Or use XML to see the visual representation, thanks to Justin Pealing and
-- his library: https://github.com/JustinPealing/html-query-plan
--------------------------------------------------------------------------------
set statistics xml on;
select d1.* from docs d1
    join docs d2 on d2.id=d1.id
    join docs d3 on d3.id=d1.id
    join docs d4 on d4.id=d1.id;
set statistics xml off;
GO

id | doc
-: | :----------------------------------------
 1 | Enter one batch per field, don't use 'GO'
 2 | Fields grow as you type
 3 | Use the [+] buttons to add more
 4 | See examples below for advanced usage


Granting permission on a particular job to execute on SQL Server 2005

User need access on that job, he can modify and run too.

You can give the user - SQLAgentUserRole.

From BOL :

  SQLAgentUserRole is the least privileged of the SQL Server Agent fixed database roles. It has permissions on only operators, local jobs, and job schedules. Members of SQLAgentUserRole have permissions on only local jobs and job schedules that they own.

-- lists activity for all jobs that the current user has permission to view.
USE msdb ;
GO

EXEC dbo.sp_help_jobactivity ;
GO


Help with removing duplicate reversed pairs in relational algebra

Do you need to return an existing combination, e.g. if there's only Tom,Mike do you need to return exactly this or is Mike,Tom also ok?

-- order of columns doesn't matter
SELECT DISTINCT
   CASE WHEN name1 > name2 THEN name2 ELSE name1 END as name1,
   CASE WHEN name1 < name2 THEN name2 ELSE name1 END as name2
FROM tab;

-- order of columns is maintained
SELECT DISTINCT name1,name2 -- DISTINCT might not be needed
FROM tab AS t1
WHERE NOT EXISTS(
  SELECT * FROM tab AS t2
  WHERE t1.name1 = t2.name2
    AND t1.name2 = t2.name1
    AND t1.name1 > t2.name1)
;

See fiddle

If you want to delete those rows you might us the 2nd logic, the actual syntax depends on your DBMS:

DELETE --change to SELECT * to see which rows will be deleted
FROM tab AS t1
WHERE EXISTS(
  SELECT * FROM tab AS t2
  WHERE t1.name1 = t2.name2
    AND t1.name2 = t2.name1
    AND t1.name1 > t2.name1)
;


What should I set DATE field as default value when NO_ZERO_DATE is enabled in MySQL?

There is no date 0000-00-00 on any calendar. It's not a valid value for a date.
Don't use 0000-00-00 or 1900-01-01 or 9999-01-01 or any other supposedly "unused" date value to represent a missing value. This just trades one type of problem for some different problem. For example, you can no longer use MIN() or MAX() or AVG() on that column.
If you need some way to represent that the date is unknown or inapplicable on a given row, then use NULL. That's what NULL is for in SQL.


How can I optimize this mySQL table that will need to hold 5+ million rows?

The reason it takes progressively longer when your table has a lot of rows is due to having to write the indexes. Try turning offing indexes for the table before inserting, then re-enable afterwards:

ALTER TABLE my_table DISABLE KEYS;

-- Your insert statement

ALTER TABLE my_table ENABLE KEYS;

Disabling keys only affects the updating of non-unique keys, so ON DUPLICATE UPDATE will still work as intended:

  ALTER TABLE ... DISABLE KEYS tells MySQL to stop updating nonunique indexes. ALTER TABLE ... ENABLE KEYS then should be used to re-create missing indexes. [src]


I've got 60 million sales of 800 different liquid products whose price change daily

Without seeing the execution plan, it's impossible to say exactly what the problem was. However, the root cause seemed to be with the way the optimizer was handling joining the queries within your CTEs (or something along those lines).

In SQL Server CTEs aren't currently materialized or fenced off (unless you use TOP inside one). In many cases, breaking up queries (whether they use CTEs or not) can be helpful to the optimizer.

As of this answer, there are no hints to alter the way CTEs are handled.


How to completely restrict a postgres role to a single schema

You have to revoke the users rights to the schema

Its kinda hard to revoke public schema as its default is well public which is granted automatically to any user granted access to database and all users are added to the public role.

Commands like this have to be run

REVOKE ALL PRIVILEGES ON public FROM public;

There are lots of gotchas with this
here are a couple of articles going over how to do this READ them CAREFULLY and TEST  before doing it on a production machine

https://wiki.postgresql.org/images/d/d1/Managing_rights_in_postgresql.pdf

https://blog.dbi-services.com/avoiding-access-to-the-public-schema-in-postgresql/

EDIT FORGET TO STATE

I have never tried to grant access from one schema that reads a table from another schema that user does not have access to..  This may not work as security should be passed up the chain blocking the read.  I DO NOT KNOW


MS SQL Server 2016 - Database Security - Login Permissions

There is a user "Guest" in each database. This user gives some permission for everyone.
My guess is that for the database where you can "Expend" the DB, your "guest" user  in those database is probably enable and probably have the "Connect" to the database securable (and I hope not more then connect)
To disable that user, run this TSQL:
Use [yourDB]
go
REVOKE CONNECT FROM guest;
GO

Once this is done, your login x should receive the error you are expecting.


Verification that my SSRS subscription was emailed to a valid address

The easiest way to do it is to set up a Reply email: address when you you are configuring database mail.  This will send the bounce back messages to that email account.

UPDATE:

The bounce-back for invalid email addresses will go to the FROM address.  The Reply-To address is used only by an email program when a user tries to reply to the message.  Change the FROM email address and see if you get the bounce-back your inbox.

If you get the bounce-back to the from address you can setup a mailbox to collect these messages.


Intermittent RESOURCE_SEMAPHORE_QUERY_COMPILE Wait Stats

I believe you will see this symptom if you have a LOT of large query plans that are fighting for memory in order to compile (this has very little to do with running the query itself). To hit this, I suspect you are using an ORM or some kind of application that generates many unique but relatively complex queries. SQL Server could be under memory pressure because of things like large query operations, but on further thought it is more likely just that your system is configured with far less memory than it needs (either there is never enough memory to satisfy all of the queries you're trying to compile, or there are other processes on the box that are stealing memory from SQL Server).

You can take a look at what SQL Server is configured with using:

EXEC sp_configure 'max server memory';    -- max configured in MB

SELECT counter_name, cntr_value
  FROM sys.dm_os_performance_counters
  WHERE counter_name IN
  (
    'Total Server Memory (KB)',    -- max currently granted
    'Target Server Memory (KB)'    -- how much SQL Server wished it had
  );

You can identify the cached plans that required the most compile memory with the following Jonathan Kehayias query, adapted slightly:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

;WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT TOP (10) CompileTime_ms, CompileCPU_ms, CompileMemory_KB,
  qs.execution_count,
  qs.total_elapsed_time/1000.0 AS duration_ms,
  qs.total_worker_time/1000.0 as cputime_ms,
  (qs.total_elapsed_time/qs.execution_count)/1000.0 AS avg_duration_ms,
  (qs.total_worker_time/qs.execution_count)/1000.0 AS avg_cputime_ms,
  qs.max_elapsed_time/1000.0 AS max_duration_ms,
  qs.max_worker_time/1000.0 AS max_cputime_ms,
  SUBSTRING(st.text, (qs.statement_start_offset / 2) + 1,
    (CASE qs.statement_end_offset
      WHEN -1 THEN DATALENGTH(st.text) ELSE qs.statement_end_offset
     END - qs.statement_start_offset) / 2 + 1) AS StmtText,
  query_hash, query_plan_hash
FROM
(
  SELECT
    c.value('xs:hexBinary(substring((@QueryHash)[1],3))', 'varbinary(max)') AS QueryHash,
    c.value('xs:hexBinary(substring((@QueryPlanHash)[1],3))', 'varbinary(max)') AS QueryPlanHash,
    c.value('(QueryPlan/@CompileTime)[1]', 'int') AS CompileTime_ms,
    c.value('(QueryPlan/@CompileCPU)[1]', 'int') AS CompileCPU_ms,
    c.value('(QueryPlan/@CompileMemory)[1]', 'int') AS CompileMemory_KB,
    qp.query_plan
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
CROSS APPLY qp.query_plan.nodes('ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple') AS n(c)
) AS tab
JOIN sys.dm_exec_query_stats AS qs ON tab.QueryHash = qs.query_hash
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
ORDER BY CompileMemory_KB DESC
OPTION (RECOMPILE, MAXDOP 1);

You can see how the plan cache is being used with the following:

SELECT objtype, cacheobjtype,
    AVG(size_in_bytes*1.0)/1024.0/1024.0,
    MAX(size_in_bytes)/1024.0/1024.0,
    SUM(size_in_bytes)/1024.0/1024.0,
    COUNT(*)
FROM sys.dm_exec_cached_plans
GROUP BY GROUPING SETS ((),(objtype, cacheobjtype))
ORDER BY objtype, cacheobjtype;

When you are experiencing high semaphore waits, check to see if these query results vary significantly from during "normal" activity:

SELECT resource_semaphore_id, -- 0 = regular, 1 = "small query"
  pool_id,
  available_memory_kb,
  total_memory_kb,
  target_memory_kb
FROM sys.dm_exec_query_resource_semaphores;

SELECT StmtText = SUBSTRING(st.[text], (qs.statement_start_offset / 2) + 1,
        (CASE qs.statement_end_offset
          WHEN -1 THEN DATALENGTH(st.text) ELSE qs.statement_end_offset
         END - qs.statement_start_offset) / 2 + 1),
  r.start_time, r.[status], DB_NAME(r.database_id), r.wait_type,
  r.last_wait_type, r.total_elapsed_time, r.granted_query_memory,
  m.requested_memory_kb, m.granted_memory_kb, m.required_memory_kb,
  m.used_memory_kb
FROM sys.dm_exec_requests AS r
INNER JOIN sys.dm_exec_query_stats AS qs
ON r.plan_handle = qs.plan_handle
INNER JOIN sys.dm_exec_query_memory_grants AS m
ON r.request_id = m.request_id
AND r.plan_handle = m.plan_handle
CROSS APPLY sys.dm_exec_sql_text(r.plan_handle) AS st;

And you may also want to look and see how memory is distributed:

DBCC MEMORYSTATUS;

And there is some good information here about why you might be seeing a high number of compiles/recompiles (which will contribute to that wait):

http://technet.microsoft.com/en-us/library/ee343986(v=sql.100).aspx

http://technet.microsoft.com/en-us/library/cc293620.aspx

You can check for high compile/recompile counts using the following counters:

SELECT counter_name, cntr_value
  FROM sys.dm_os_performance_counters
  WHERE counter_name IN
  (
    'SQL Compilations/sec',
    'SQL Re-Compilations/sec'
  );

And you can check for internal memory pressure leading to evictions - non-zero counters here would indicate that something not good is going on with the plan cache:

SELECT * FROM sys.dm_os_memory_cache_clock_hands
  WHERE [type] IN (N'CACHESTORE_SQLCP', N'CACHESTORE_OBJCP');

NOTE Most of these metrics don't have a magic "oh my gosh I need to panic or do something!" threshold. What you need to do is to take measurements during normal system activity, and determine where these thresholds are for your hardware, configuration and workload. When you panic do something is when two conditions are true:

the metrics vary significantly from normal values; and,
there is actually a performance problem occurring (like your CPU spikes) - but only if they are actually interfering with anything. Other than seeing the CPUs spike, are you seeing any other symptom? In other words, is the spike the symptom, or is the spike causing other symptoms? Would users of the system ever notice? A lot of people always go after their highest wait consumer, simply because it's the highest. Something is always going to be the highest wait consumer - you have to know that it's varying enough from normal activity that it indicates a problem or some significant change.

Optimize for ad hoc workloads is a great setting for 99% of the workloads out there, but it will not be very helpful in reducing compilation costs - it is aimed at reducing plan cache bloat by preventing a single-use plan from storing the whole plan until it's been executed twice. Even when you only store the stub in the plan cache, you still have to compile the full plan for the execution of the query. Perhaps what @Kahn meant to recommend was setting the database level parameterization to forced, which will potentially provide better plan re-use (but it really depends on how unique all of these high-cost queries are).

Also some good information in this white paper about plan caching and compilation.


Return results from SQL JOIN even if criteria not met in all tables

As Akina mentioned, you need to use an OUTER JOIN, specifically LEFT OUTER JOIN or more simply written as LEFT JOIN to take all the records from the left-hand side of the join clause, join where they match the right-hand side of the clause, and just NULL out the fields where they don't match in the right-hand side. This tutorial from W3Schools gives a brief intro with visuals to the different join types. Here's another article with further information on different join types. (Note these articles are database-agnostic and there may be other available join types for your specific system as well, but these are generally all one ever needs.)
What you were previously doing by just specifying JOIN is using the shorthand syntax for the INNER JOIN clause which only returns rows that match on both sides of the join clause.


How can I filter query results based on whether they have ALL values from another table?

Relational division is a less common type of query than the more typical joins and unions. It's a little more awkward in SQL.
You want to know the users who have all the permissions required by POST.
Another way to state this is to return users for whom no permission required for POST is missing from their permissions.
SELECT u.UserID
FROM Users AS u
WHERE NOT EXISTS (
  SELECT *
  FROM RequiredPermissions AS rp
  LEFT OUTER JOIN Permissions AS p
    ON rp.Permission = p.Permission AND u.UserId = p.UserId
  WHERE rp.ActionType = 'POST'
    AND p.UserId IS NULL)

The way it works is that the OUTER JOIN will make p.* NULL if there is no match. So if the outer query wants there to be no case where a required permission has no match for the respective user, then that user must have all the required permissions.
This automatically handles actions with 3 or more permissions. The other solutions for relational division that I've seen require you to make code changes to your query for the number of items you are searching for.


How to determine actual memory occupied by Query Cache in MySQL?

It uses the amount specified by query_cache_size.
If that value is more than about 50M, then it is probably slowing down the server.
The Query cache is going away; it cannot be used in any Clustering setup, and it has been removed from MySQL 8.0.
Every modification to a table triggers a purge of all entries for that table.  Therefore the QC, especially a big one, is costly in busy production servers.
If you must use the QC, follow these tips:

No more than 50M (except on Aurora)
Use SQL_CACHE or SQL_NO_CACHE in each SELECT, based on whether the query is likely to be repeated before it is purged.

(A small percentage of production system do benefit from the QC.)
There are cryptic metrics in SHOW GLOBAL STATUS LIKE 'Qc%'; but I can't explain them.


Redshift: Table columns can be found in information_schema.columns but not in pg_catalog.pg_table_def

Check show search_path; to make sure you are on the current path where the table was created.
As stated on the original AWS redshift documentation.

  PG_TABLE_DEF only returns information about tables that are visible to the user.

to get what you want you should run

set search_path to '$user', '<#your_schema#>';
select * from pg_catalog.pg_table_def where tablename = '<#your_table#>';

Hope that helps.


sh.status() error processing query: ns=config.mongosTree

As I am able to see that you are getting the exit error code : 2

As MongoDB documented here The error code :2  signifies The specified options are in error or are incompatible with other options.

As per MongoDB documentation here The sh.status(), When run on a mongos instance, prints a formatted report of the sharding configuration and the information regarding existing chunks in a sharded cluster. The default behavior suppresses the detailed chunk information if the total number of chunks is greater than or equal to 20.

For further your ref here and here


I am unfamiliar with 5gbfree hosting, but the host = localhost for me is a clear indication that the host may not be accesible from the public internet, only the application server will have access.

Check if you can change the database server and network configuration/firewall, but I would strongly suggest to, asuming you have ssh remote access or similar to tunnelize the mysql connection (e.g. https://stackoverflow.com/questions/22639188/how-can-i-use-ssh-tunneling-to-connect-to-a-remote-mysql-server ) so you don't expose the mysql service to the outside, but still can access from your development machine.

If tunnelizing or opening ports are not options, consider setting up a local test database on your development machine instead rather than connecting directly to the one on your hosted environment or installing web administration tools such as phpmyadmin. Free hosting services are, for a reason, not very flexible. If you want to test mysql but cannot afford to pay for it I would suggest taking advantage of the 1 year free of limited hosting on AWS and the free accounts of RDS and EC2.


Why is DROP DATABASE taking so long? (MySQL)

Rather than killing the process, it would be safer if you did it within MySQL:

$ mysqladmin processlist -u root -p
Enter password:
+-----+------+-----------+-------------------+---------+------+-------+------------------+
| Id  | User | Host      | db                | Command | Time | State | Info             |
+-----+------+-----------+-------------------+---------+------+-------+------------------+
| 174 | root | localhost | example           | Sleep   | 297  |       |                  |
| 407 | root | localhost |                   | Query   | 0    |       | show processlist |
+-----+------+-----------+-------------------+---------+------+-------+------------------+

The query with id 174 is the one blocking deletion of the 'example' database, so before you kill any processes first let MySQL try to terminate the query:

$ mysqladmin kill 174

Run the processlist command above again to confirm that it was killed.

If this doesn't work, then you could perhaps look at killing the errant process, but before that you might try restarting the MySQL server.

You can also run commands like 'SHOW FULL PROCESSLIST' and 'KILL 174' in the MySQL shell, for example if you only have the MySQL client installed. The main point is to avoid killing the process using 'kill' in the shell unless absolutely necessary.

Generally speaking you can use either mysql or mysqladmin. You shouldn't need to be running commands like this that often though; once you start killing queries regularly something is definitely wrong and you'd be better off fixing that problem (killing the query process is just treating the symptom).


Difference between GROUP BY B,A and GROUP BY COALESCE(B,A)

With GROUP BY b,a the tuples (null, 1), (1,1), (2,1) and (17,1) would end up in four different groups.

With GROUP BY coalesce(b,a) the tuples (null,1), (1,1), (2,1) and (17,1) would end up in the same group.

If you want the "conditional" grouping, then yes, the version with coalesce is probably what you want.


I would create tables for:

People
Actions
Items
Actions By People Against Items

In SQL, this would look like:

CREATE TABLE dbo.Actions
(
    ActionID int NOT NULL PRIMARY KEY
    , ActionDescription varchar(100) NOT NULL
);

CREATE TABLE dbo.People
(
    PersonID int NOT NULL PRIMARY KEY
    , PersonName varchar(100) NOT NULL
);

CREATE TABLE dbo.Items
(
    ItemID int NOT NULL PRIMARY KEY
    , ItemDescription varchar(200) NOT NULL
);

CREATE TABLE dbo.ItemActions
(
    ItemID int NOT NULL FOREIGN KEY
        REFERENCES dbo.Items(ItemID)
    , PersonID int NOT NULL FOREIGN KEY
        REFERENCES dbo.People(PersonID)
    , ActionID int NOT NULL FOREIGN KEY
        REFERENCES dbo.Actions(ActionID)
    , ActionDate datetime NOT NULL
);

Sample data:

INSERT INTO dbo.People (PersonID, PersonName)
VALUES (1, 'Ella')
    , (2, 'Mike')
    , (3, 'Sue');

INSERT INTO dbo.Actions (ActionID, ActionDescription)
VALUES (1, 'Lost')
    , (2, 'Found')
    , (3, 'Claimed');

INSERT INTO dbo.Items (ItemID, ItemDescription)
VALUES (1, 'Apple')
    , (2, 'Pear')
    , (3, 'Peach')
    , (4, 'Ameoba');

INSERT INTO dbo.ItemActions (ItemID, PersonID, ActionID, ActionDate)
VALUES (1, 1, 1, '2018-03-04 12:34:56')
    , (1, 2, 2, '2018-03-05 09:17:00')
    , (2, 1, 1, '2018-04-01 10:42:01')
    , (3, 1, 1, '2018-04-01 11:40:05')
    , (3, 2, 2, '2018-04-02 15:40:00')
    , (2, 3, 3, '2018-04-03 17:54:46');

Query:

SELECT i.ItemDescription
    , p.PersonName
    , ia.ActionDate
    , a.ActionDescription
FROM dbo.ItemActions ia
    INNER JOIN dbo.Items i on ia.ItemID = i.ItemID
    INNER JOIN dbo.People p ON ia.PersonID = p.PersonID
    INNER JOIN dbo.Actions a ON ia.ActionID = a.ActionID;

Results:

╔═════════════════╦════════════╦═════════════════════════╦═══════════════════╗
║ ItemDescription ║ PersonName ║       ActionDate        ║ ActionDescription ║
╠═════════════════╬════════════╬═════════════════════════╬═══════════════════╣
║ Apple           ║ Ella       ║ 2018-03-04 12:34:56.000 ║ Lost              ║
║ Apple           ║ Mike       ║ 2018-03-05 09:17:00.000 ║ Found             ║
║ Pear            ║ Ella       ║ 2018-04-01 10:42:01.000 ║ Lost              ║
║ Peach           ║ Ella       ║ 2018-04-01 11:40:05.000 ║ Lost              ║
║ Peach           ║ Mike       ║ 2018-04-02 15:40:00.000 ║ Found             ║
║ Pear            ║ Sue        ║ 2018-04-03 17:54:46.000 ║ Claimed           ║
╚═════════════════╩════════════╩═════════════════════════╩═══════════════════╝

This setup means you could add other actions such as "Thrown Away" to the list of possible actions without disrupting your design.


reducing migration time of a large size postgresql database

Bit of a FAQ.

pg_upgrade with --link (but understand that you can't easily go back);
Logical streaming replication with pglogical
Trigger-based logical replication with Londiste etc

Search for zero or low downtime PostgreSQL major version upgrade.


How to select values consisting of numerals only?

You can use a regular expression for that:

select *
from the_table
where the_column ~ '^[0-9]+$';

Related question on Stackoverflow


sp_update_schedule says @schedule_id does not exist

The @schedule_id parameter on sp_update_schedule does refer to the schedule_id column on msdb.dbo.sysschedules
sp_update_schedule contains a call to sp_verify_schedule_identifiers to validate the schedule, and it's from this verification step that you are getting that error (message 14262).
EXECUTE @retval = msdb.dbo.sp_verify_schedule_identifiers
          @name_of_name_parameter = '@name',
          @name_of_id_parameter   = '@schedule_id',
          @schedule_name          = @name             OUTPUT,
          @schedule_id            = @schedule_id      OUTPUT,
          @owner_sid              = @cur_owner_sid    OUTPUT,
          @orig_server_id         = NULL

If you are passing both a schedule id and a schedule name, there could be a problem in the string you are passing for the name, as that is used for additional filtering within the verification procedure.
I'd suggest doing some testing with calls directly to sp_verify_schedule_identifiers so that you can simplify your troubleshooting, and make sure you're passing valid parameters to sp_update_schedule:
DECLARE
  @name_of_name_parameter   VARCHAR(60) ,--= '@schedule_name',
  @name_of_id_parameter     VARCHAR(60) = '@schedule_id',
  @schedule_name            sysname     ,--= 'syspolicy_purge_history_schedule',
  @schedule_id              INT         = 8,
  @owner_sid                VARBINARY(85),
  @orig_server_id           INT,
  @job_id_filter            UNIQUEIDENTIFIER    = NULL,
  @RetCode                  INT;

EXEC @RetCode = sp_verify_schedule_identifiers
  @name_of_name_parameter    = @name_of_name_parameter,
  @name_of_id_parameter      = @name_of_id_parameter,
  @schedule_name             = @schedule_name,
  @schedule_id               = @schedule_id,
  @owner_sid                 = @owner_sid,
  @orig_server_id            = @orig_server_id,
  @job_id_filter             = @job_id_filter;

SELECT
   RetCode              = @RetCode,
   NameOfNameParam      = @name_of_name_parameter,
   NameOfIdParam        = @name_of_id_parameter,
   ScheduleName         = @schedule_name,
   ScheduleID           = @schedule_id,
   OwnerSid             = @owner_sid,
   OrigServerID         = @orig_server_id,
   JobIDFilter          = @job_id_filter;

Permissions may also be getting in your way. You would need, at minimum, GRANT EXEC ON sp_update_schedule TO <your login> in order to make the call you are trying to make. To do the troubleshooting step I list above, you would also need to be granted EXEC on sp_verify_schedule_identifiers--though you only need this if you execute it directly. Normally, ownership chaining would allow you to only need the top-level permission & you would implicitly have the permission to do what that procedure does.  You could also be added to the SQLAgentUserRole security role in msdb, which includes other permissions as well--if you're rescheduling jobs, you may also need/want some of those other permissions in order to accomplish your work, assuming that this is an acceptable level of access.


How to order by specific column without breaking groups?

Another option, using a window function:
select
    id, "group", date
from
    table1
order by
    max(date) over (partition by "group") desc,
    "group",
    date desc ;

Thnx to @nbk for providing the dbfiddle.uk


Return sample row if result set is empty

You can use either generate_series function or VALUES Lists with a left join. The sample queries are below;

    CREATE TABLE my_table (a INT, b INT);
    INSERT INTO my_table VALUES (1,2), (2,2), (3,4);

    SELECT a, b FROM generate_series(1,1)
    LEFT JOIN my_table ON b = 5;

    |      a |      b |
    |--------|--------|
    | (null) | (null) |

    SELECT a, b FROM (values (1)) as one
    LEFT JOIN my_table ON b = 5;

    |      a |      b |
    |--------|--------|
    | (null) | (null) |

    SELECT a, b FROM generate_series(1,1)
    LEFT JOIN my_table ON b = 2;

    | a | b |
    |---|---|
    | 1 | 2 |
    | 2 | 2 |


As you have 200 index, it would take longer than a vacuum. A reindex always depends on your OI. It seemed that it would take more than an hour.

What I would suggest is:

get rid of some indexes. 200 for 100 columns makes no sense. See how many of the l those you use 90% of the time.

1.1. The other queries would find their path along other indexes

1.2. Your record takes more time recording indexes than rows, I that should be slowing down your night update
You could reindex the indexes the you use the most

2.1. Use REINDEX INDEX index_name;

2.2. If you can run some reindex in parallel, it would make better use of your server cores


Type conversion in expression may affect “CardinalityEstimate” - on a computed column?

I had a look at this question:
Type conversion in expression may affect “CardinalityEstimate” in query plan choice?
But that was related to collation and not with computed columns.

It's the same issue. The warning is informational, to help you understand any issues you might notice with cardinality estimation.

Is there any way I can get rid of this warning?
Is it a persisted column, the conversion has been already done, why exactly is the query optimiser going on about it?

Just because the computed column is persisted, does not mean the optimizer will choose to use the persisted value. SQL Server routinely expands computed columns (persisted or not) into the underlying definition at the start of the compilation process. Later on, it may, or may not, match expressions back to the available persisted computed columns. When the optimizer encounters the expression, it may add a warning to the plan.
The only way to prevent this expansion and match-back is to enable trace flag 176 on suitable versions.

When I remove the MIN and MAX columns from the select, there is no more any warnings

With this change, the optimizer is able to simplify the query to remove the unnecessary join. The result is a TRIVIAL plan, with no cost-based choices, so there is no warning about plan choices.


How to display query result in rockmongo by using execute method

The find() method returns a cursor object. In order to view the first returned record in the cursor you must call the next() method.

function () {
  return db.tablebusiness.find({
    "_id": "the-simmons-paradise__41.85_-87.88"
  }).next();
}


Fetch Records alternatively from database

Without considering performance, it is possible to solve by using one query with 3 subquery inside.

SELECT * FROM
   (SELECT * FROM
       (SELECT id, Date_Time, MOD(@no := @no + 1, 2) AS even
        FROM recorddata  ORDER BY Date_Time ASC) l
    WHERE even = 0) a,
   (SELECT id, Date_Time FROM recorddata ORDER BY Date_Time ASC) b
WHERE a.Date_Time < b.Date_Time
GROUP BY a.id


To be quite honest your requirements sound like a cross between a nightmare and a Version Control System.  I would highly recommend looking at popular solutions like Git instead of reimplementing a DVCS with a relational database.  Your application could effectively be a nice skin around Git.  If you have to do this via a relational database then I think versioning by time stamps is going to be your best chance of success.

The basic strategy is to put a time stamp on every entity.  Then when you need to go to the most recent version you select all the entities through the relational chain with the most recent time stamp.  When you need to get a specific version you grab all the most recent versions of the entities that are at or older than the version time stamp you want.

Versioning by time stamp prevents you from copying all the older entities to a new version id even if those entities haven't changed.  It also saves you the madness of keeping track of those version ids.

This might lead to slow query times but you can play with it to see if it suits your needs.  To reiterate I don't think a relational database is the best solution, but sometimes life forces us to put the square peg in the round hole.  If that is your lot in life I wish you the best.

Edit: Versioning is related to historical pricing

I agree that time stamps are the way to go given that versioning is related to historical pricing.  To handle that you just need an Effective Start Date column.  To find the rate at any point in time you just need to find the most recent date that isn't in the future.  For example:

declare @Date DATE = SYSUTCDATETIME(); -- Or any other date for historical/future data
;with [CurrentPrice_CTE] AS -- You don't really need this semi-colon but I added it as a defensive habit.
(
    select
      [PriceId] -- Price table's PK
      max([EffectiveStartDate]) AS [EffectiveStartDate]
    from [dbo].[Price]
    where
      [EffectiveStartDate] <= @Date
)
select
  [p].* -- Select the columns you need
from [dbo].[prices] [p]
    inner join [CurrentPrice_CTE] [cp] as [cp].[PriceId] = [p].[PriceId] and [cp].[EffectiveStartDate] = [p].[EffectiveStartDate]

Just make sure you make the start date unique for each product or that code will return more results than you want.  To be totally honest this sounds like a much less complicated problem than having to reversion the entire database every update.  :)


Is there a way to achieve Workload Management in MySQL?

Other than outdated techniques like obtaining exclusive locks on tables and tactics like SELECT HIGH_PRIORITY and INSERT DELAYED -- which are either deprecated or otherwise not useful (especially with InnoDB) -- MySQL (including RDS) does not have any mechanisms for prioritization like this.

Depending on the nature of your "longer running background queries" -- if they are reads, not writes, such as for reporting purposes -- the usual approach is to deploy a read replica.  In RDS for MySQL 5.5, the replica server has to be another RDS instance.  This is different than "Multi-A/Z" RDS, where you don't have access to the standby server, and switchover to the standby is automatic.

In addition to offloading heavy read loads, having a read replica is still a viable disaster recovery tool.  If you were to have a catastrophic outage of your production RDS instance, an RDS read replica can be manually converted to a standalone (master) instance, and you can migrate your application to it -- it will have the most up to date version of your production data possible -- nothing that it received up until the original master failed should be lost.

Standard replication in MySQL is asynchronous -- the replica is not precisely in lock-step-sync with the master, but on a system that is sufficient for the workload offered, the delay is usually a matter of milliseconds.  The master sends the changes to the replica almost instantaneously, and the replica -- which started out with an identical data set to the master -- immediately stores a copy of the raw change instructions to disk in the "relay log," and then applies those changes to its own working data set as fast as its disk, memory, and CPU will permit... in a 2-step process.  An overloaded replica server's working data set may lag behind the master, but if the master has a catastrophic failure preceded by a period of normal operating conditions, then the replica will have already received the change events and once it catches up on playing back and applying the stream, its working data set will be identical or as near as possible to identical to what existed on the master at the moment the master failed.

New, in RDS for MySQL 5.6, you can actually deploy a read replica on a MySQL instance that is external to RDS, either in an EC2 instance or at your own data center, and have a live copy of your data that's independent of RDS.  Officially, a non-RDS replica is documented as being intended for short term migration purposes, but it does not appear to be actually limited to this application.  The availability of this feature was the main reason I dropped my resistance to allowing some of my corporate services to use RDS.

Operating in a production environment without at least one live replica as a standby in case of failure of the master is, to me, unthinkable, and the ability to run the reporting queries with zero impact on the master is, at minimum, a bonus.  Every production server I have has at least one and sometimes more than one replica.

I don't know if this addresses your needs directly, but I would recommend having one at any rate.

ETA: A follow-up thought that occurred to me as something worth mentioning is that while a read replica generally has identical table definitions to those on the master, it is possible to declare the indexes on those tables as differently as your needs require.  On one of my systems, I have a multi-million-row table where the master rarely does anything to more than one row at a time, but the replica does extensive sorting and ordering and group-by, so the replica has substantially more indexes on the same table than the master has.  This gives me the luxury and flexibility of what are arguably almost excessive indexes, but without the master having to spend the necessary time maintaining all of those different indexes as it's processing live transactions.  If these same indexes had to be defined on the master server, they could add up to unjustifiable overhead... not to mention that I can add additional indexes to an table on the replica at any time, without impacting the live system.  In most cases, I do the majority of backups from replicas as well.


MySql counting results with left joins doubles the results

You have two joins from the base table (auth_user) and both joins are to tables with 1-to-many relationship with the base table. That creates the multiple rows. If for example, an auth_user has 3 orders and 7 jobs, the join (before any group by) will result in 21 rows.

When you then GROUP BY, the (created) rows are counted and you get incorrect results (in the example, you'd get the job counts multiplied by 3 and the orders count multiplied by 7). Your code would manage to avoid the x7 because it uses DISTINCT when counting orders but not avoid the x3 when counting jobs.

One solution would be to use DISTINCT in all counts. In your case, you could write the "wash" count as:

            Count(DISTINCT CASE
                             WHEN `job`.`service` = 'wash' THEN job.id
                             ELSE NULL
                           END)                   AS `wash_ct`

But this solution will become quite complicated and inefficient with many joins.

Another solution is to group by in each individual table and then join (the derived tables) to auth_user. Also:

GROUP BY and DISTINCT in the same SELECT are rarely needed:
ELSE NULL is the default in CASE expressions. (it's not a problem if you like it there, just not needed.)
You don't need to quote all table and column names. Only reserved words - which should be avoided anyway - and names with special characters.
It would best to avoid reserved words - like order - when naming tables and columns.

The query could be written:

SELECT au.id,
       au.username,
       COALESCE(orders_ct, 0) AS orders_ct,
       COALESCE(clean_ct, 0)  AS clean_ct,
       COALESCE(wash_ct, 0)   AS wash_ct
FROM   auth_user AS au
       LEFT OUTER JOIN
           ( SELECT user_id,
                    Count(*)  AS orders_ct
             FROM   `order`
             GROUP  BY user_id
           ) AS o
                    ON au.id = o.user_id
       LEFT OUTER JOIN
           ( SELECT user_id,
                    Count(CASE WHEN service = 'clean' THEN 1
                          END)  AS clean_ct,
                    Count(CASE WHEN service = 'wash' THEN 1
                          END)  AS wash_ct
             FROM   job
             GROUP  BY user_id
           ) AS j
                    ON au.id = j.user_id
ORDER  BY au.id DESC
LIMIT  100 ;


Ended up not trying the uninstall/reinstall of SSRS.

Here's what got me working:

Stop SSRS service
detach reporting service databases
restart SSRS -- it complains about missing DBs
stop SSRS
reattach
restart SSRS
Using SSRS configuration wizard, re-point SSRS to its databases
Now I was able to delete the encryption keys

This is obviously a hack solution and fails to identify what went wrong in the first place, but it worked


PostgreSQL 12: column proisagg does not exist

The query is hard-coded into pgAdmin III, and you cannot change it.

These columns have been removed in v12 because they are no longer useful.

pgAdmin III has been out of support for years.

Use pgAdmin 4 or another supported client tool.


Find out who ran a query using transaction log backups

Converting my comment as answer :

You have to use undocumented fn_dblog Begin Time, SUSER_SNAME([Transaction SID]) or fn_dump_dblog -- when reading from T-log backups. Be careful and try it on a NON PROD system as every time fn_dump_dblog is called, it creates a new hidden SQLOS scheduler and up to three threads, which will not go away (and will not be reused) until a server restart.

Also, its not that straight forward to be able to read T-log and come on a conclusion - If you have not done it or are new to it. You are better off trying a 3rd party tool (like Apex SQL Log) if it is that critical for you to find out who did the mess.


Large hexadecimal PRIMARY KEY - how to index substrings of the PK field?

<TL;DR>

Regular expressions can be a very powerful tool for various data storage,  manipulation and indexing requirements - regexes' capabilities in conjunction with GENERATED columns can be even more flexible and powerful and provide a decent solution to the OP's issue.
In this particular case (rowing back from my initial answer!), MySQL or its derivatives are the only feasible solution. PostgreSQL doesn't allow GENERATED fields to be FOREGIN KEYs unless they're UNIQUE. Obviously, the requirement to be able to use small substrings as FKs can't be satisfied in this case, despite the fact that MySQL's regex implementation leaves a lot to be desired! PG could use TRIGGERs - but that's another question!

</TL;DR>
A relatively simple solution to your issue exists if (and maybe it's a big if), you have PRIMARY KEY string separated by the same character. A more complex solution is possible using regular expressions - both options are explored below.
You can use GENERATED(*) columns (confusingly, these are also referred to as VIRTUAL in the Wikipedia entry, which is also one of the storage classes for such fields - the other storage classes are [STORED | PERSISTENT]. From the MariaDB documentation:

PERSISTENT (a.k.a. STORED): This type's value [i.e. data] is actually stored in the table.

VIRTUAL: This type's value is not stored at all. Instead, the value [data] is generated dynamically when the table is queried. This type is the default.

(*) There are other terms for these columns, e.g. COMPUTED BY or CALCULATED.
MySQL's syntax is (versions 5.7 and up):
col_name data_type [GENERATED ALWAYS] AS (expr)
  [VIRTUAL | STORED]  -- storage class
  [NOT NULL | NULL]
  [UNIQUE [KEY]] [[PRIMARY] KEY]
  [COMMENT 'string']

In order to use such columns as FOREIGN KEYs, their storage class has to be STORED.
"Simple" solution:
Your PK is of the format (a fiddle with the code and tests for this part is available here):
str:str:str:str:str:str:str:str  (str = 1-4 characters(*) - separated by colons)

(*) Not hex characters in this case - I messed around with MySQL's HEX() and UNHEX() functions to no avail. However, see discussion!
MySQL has an IS_IPV6() function, however, the MySQL 5.7 documentation also contains this gem:

CHECK
The CHECK clause is parsed but ignored by all storage engines.

So, I'm going to use MySQL 8 which has implemented them - if you want valid IP_V6 addresses in a version prior to 8, then you'll have to use a trigger. MariaDB has CHECK constraints from at least 10.3 onwards.
You mentioned that IPv6 was (only) an example - if any set of characters will do, then just remove the CHECK constraint - and see the complex solution below for a discussion on regular expressions.
I created this MySQL 8 table (see fiddle here):
CREATE TABLE test
(
  ip_v6 VARCHAR (39) NOT NULL,
  host VARCHAR (100) NOT NULL,

  ip_v6_token_1 VARCHAR (4) GENERATED ALWAYS AS
    ((SUBSTRING_INDEX(SUBSTRING_INDEX(ip_v6, ':', 1), ':', -1))) STORED,
  ip_v6_token_2 VARCHAR (4) GENERATED ALWAYS AS
    ((SUBSTRING_INDEX(SUBSTRING_INDEX(ip_v6, ':', 2), ':', -1))) STORED,

  INDEX (ip_v6_token_1),  -- required for FOREIGN KEYs
  INDEX (ip_v6_token_2),  --           "

  CONSTRAINT test_ip_v6_pk PRIMARY KEY (ip_v6),
  CONSTRAINT ip_v6_valid_ck CHECK (IS_IPV6(ip_v6))
);

-- do the same for tokens 3 - 7 as per requirements!

I inserted a few records and then:
SELECT
  ip_v6_token_1 AS "Token 1",
  ip_v6_token_2 AS "Token 2",
  ip_v6         AS "IPv6:",
  host          AS "Host:"
FROM test ORDER BY host;

Result:
Token 1 Token 2 IPv6:                                    Host:
   2001    0db8 2001:0db8:0000:FFF0:0000:8a2e:0370:7334 host_1
     10       9 10:9:8:7:6:6:7:6                        host_2
   4001    1111 4001:1111:2222:FFF0:0000:8a2e:0370:7334 host_3

So far, so good - we have our tokens split out as required.
Now, we check the FOREIGN KEY support - create a table:
CREATE TABLE test_fk
(
  t_id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,

  --
  -- Other fields
  --

  token_1 VARCHAR (4) NOT NULL,
  token_2 VARCHAR (4),

  CONSTRAINT test_fk_test_ip_v6_token_1_fk
    FOREIGN KEY (token_1)
      REFERENCES test (ip_v6_token_1) ON DELETE CASCADE ON UPDATE CASCADE,

  CONSTRAINT test_fk_test_ip_v6_token_2_fk
    FOREIGN KEY (token_2)
      REFERENCES test (ip_v6_token_2) ON DELETE CASCADE ON UPDATE CASCADE
);

So, I tested the ON DELETE CASCADE and the ON UPDATE CASCADE functionality and they appear to work. Also, when one tries to INSERT on test_fk it fails if there isn't a corresponding token in the ip_v6 field of the test parent table - see the fiddle for the tests.
Complex solution (modelled on the above):
This part of the answer makes extensive use of regular expressions. I'm taking IPv6 as an exemplar of the power and flexibility of regexes - ultimately, you should be able to write your own in line with your particular requirements.
A fiddle for the code and tests can be found here.
As above, I CREATEd a table:
CREATE TABLE test
(
  ip_v6 VARCHAR (39) NOT NULL,
  host VARCHAR (100) NOT NULL,

  ip_v6_token_1 VARCHAR(39) GENERATED ALWAYS AS
    (SUBSTRING_INDEX(SUBSTRING_INDEX(ip_v6, ':', 1), ':', -1)),
  ip_v6_token_2 VARCHAR(39) GENERATED ALWAYS AS
    (SUBSTRING_INDEX(SUBSTRING_INDEX(ip_v6, ':', 2), ':', -1)),

  -- as in simple solution, same for tokens 3-7 as per requirements!

  INDEX (ip_v6_token_1),  -- required for FOREIGN KEYs
  INDEX (ip_v6_token_2),

  CONSTRAINT test_ip_v6_pk PRIMARY KEY (ip_v6),
  CONSTRAINT ip_v6_regexp_ck
    CHECK(ip_v6 REGEXP '^([[:xdigit:]]{1,4}:){7,7}[[:xdigit:]]{1,4}$')
);

So, as you see, the change is on the CHECK constraint on the ip_v6 field:
  CONSTRAINT ip_v6_regexp_ck
    CHECK(ip_v6 REGEXP '^([[:xdigit:]]{1,4}:){7,7}[[:xdigit:]]{1,4}$')

A brief explanation of the regular expression '^([[:xdigit:]]{1,4}:){7,7}[[:xdigit:]]{1,4}$':

the ^ character is an anchor - it matches the beginning of a line.
This ensures that the format of the ip_v6 string is (from the beginning) of 1-4 hex characters (of any combination) in the ranges of 0-9 OR a-f OR A-F - from here - groups of characters enclosed in [ & ] are called character classes. My regex above is equivalent to '^([0-9a-fA-F]{1,4}:){7,7}[0-9a-fA-F]{1,4}$'.
From here:

When it is necessary to specify the minimum and maximum number of
repeats explicitly, the bounds operator {} may be used. Thus. a{2} is
the letter ‘a’ repeated exactly twice, a{2,4} represents the letter
‘a’ repeated between 2 and 4 times, and a{2,} represents the letter
‘a’ repeated at least twice with no upper limit. Note that there must
be no whitespace inside the {}, and there is no upper limit on the
values of the lower and upper bounds.

the {1,4} [:xdigit:] characters are followed by a (character literal) : - it has no special meaning in regexps.

the enclosing parentheses (()) (important for this discussion, see below) mean that the groups of hex digits concatenated with a single : are to be treated as a group - "captured groups" - see discussion re. MySQL vs. PostgreSQL!

the {7,7} means 7 and no more nor no less than 7 repeats of that group can be present - since it ends with a colon, it can't be the last element. That is taken care of by the final group - [0-9a-fA-F]{1,4}$ - where the usual 1 - 4 digit hex is followed by the $ character.

the $ character is another anchor, this time signifying the end of the line - so the end of our ip_v6 string must be the hex character(s) without a trailing :!

So, we've ensured that our ip_v6 string at least corresponds to some of the basics about IPv6 addresses - now what can we do?
As can be seen, there are INDEXes (or KEYs - synonyms in MySQL) for the three token fields generated - so far so good. I don't have a realistic dataset to test, but if you do, you can see the effect of these indexes.
You can see that I've used SUBSTRING_INDEX (esp. from here) - which is all well and good - but this will become more and more (totally) restrictive as your requirements become more complex! For some reason, the relatively easy SUBSTRING() function is "disallowed" in GENERATED columns.
What you really need is the power of Regular expressions - these are very powerful and come in handy in lots of scenarios - like the one in this question!
You will see in the fiddle that I have run a few tests - some of which work and some of which fail - as with the simple solution!
Actual regexps for IPv6 addresses can be found here, here. These are not for the faint-hearted - first link's example:

(([0-9a-fA-F]{1,4}:){7,7}[0-9a-fA-F]{1,4}|([0-9a-fA-F]{1,4}:){1,7}:|([0-9a-fA-F]{1,4}:){1,6}:[0-9a-fA-F]{1,4}|([0-9a-fA-F]{1,4}:){1,5}(:[0-9a-fA-F]{1,4}){1,2}|([0-9a-fA-F]{1,4}:){1,4}(:[0-9a-fA-F]{1,4}){1,3}|([0-9a-fA-F]{1,4}:){1,3}(:[0-9a-fA-F]{1,4}){1,4}|([0-9a-fA-F]{1,4}:){1,2}(:[0-9a-fA-F]{1,4}){1,5}|[0-9a-fA-F]{1,4}:((:[0-9a-fA-F]{1,4}){1,6})|:((:[0-9a-fA-F]{1,4}){1,7}|:)|fe80:(:[0-9a-fA-F]{0,4}){0,4}%[0-9a-zA-Z]{1,}|::(ffff(:0{1,4}){0,1}:){0,1}((25[0-5]|(2[0-4]|1{0,1}[0-9]){0,1}[0-9]).){3,3}(25[0-5]|(2[0-4]|1{0,1}[0-9]){0,1}[0-9])|([0-9a-fA-F]{1,4}:){1,4}:((25[0-5]|(2[0-4]|1{0,1}[0-9]){0,1}[0-9]).){3,3}(25[0-5]|(2[0-4]|1{0,1}[0-9]){0,1}[0-9]))

That's over 660 characters, and the second link's example is 1080 characters:

^\s*((([0-9A-Fa-f]{1,4}:){7}([0-9A-Fa-f]{1,4}|:))|(([0-9A-Fa-f]{1,4}:){6}(:[0-9A-Fa-f]{1,4}|((25[0-5]|2[0-4]\d|1\d\d|[1-9]?\d)(.(25[0-5]|2[0-4]\d|1\d\d|[1-9]?\d)){3})|:))|(([0-9A-Fa-f]{1,4}:){5}(((:[0-9A-Fa-f]{1,4}){1,2})|:((25[0-5]|2[0-4]\d|1\d\d|[1-9]?\d)(.(25[0-5]|2[0-4]\d|1\d\d|[1-9]?\d)){3})|:))|(([0-9A-Fa-f]{1,4}:){4}(((:[0-9A-Fa-f]{1,4}){1,3})|((:[0-9A-Fa-f]{1,4})?:((25[0-5]|2[0-4]\d|1\d\d|[1-9]?\d)(.(25[0-5]|2[0-4]\d|1\d\d|[1-9]?\d)){3}))|:))|(([0-9A-Fa-f]{1,4}:){3}(((:[0-9A-Fa-f]{1,4}){1,4})|((:[0-9A-Fa-f]{1,4}){0,2}:((25[0-5]|2[0-4]\d|1\d\d|[1-9]?\d)(.(25[0-5]|2[0-4]\d|1\d\d|[1-9]?\d)){3}))|:))|(([0-9A-Fa-f]{1,4}:){2}(((:[0-9A-Fa-f]{1,4}){1,5})|((:[0-9A-Fa-f]{1,4}){0,3}:((25[0-5]|2[0-4]\d|1\d\d|[1-9]?\d)(.(25[0-5]|2[0-4]\d|1\d\d|[1-9]?\d)){3}))|:))|(([0-9A-Fa-f]{1,4}:){1}(((:[0-9A-Fa-f]{1,4}){1,6})|((:[0-9A-Fa-f]{1,4}){0,4}:((25[0-5]|2[0-4]\d|1\d\d|[1-9]?\d)(.(25[0-5]|2[0-4]\d|1\d\d|[1-9]?\d)){3}))|:))|(:(((:[0-9A-Fa-f]{1,4}){1,7})|((:[0-9A-Fa-f]{1,4}){0,5}:((25[0-5]|2[0-4]\d|1\d\d|[1-9]?\d)(.(25[0-5]|2[0-4]\d|1\d\d|[1-9]?\d)){3}))|:)))(%.+)?\s*$

However, the IS_IPV6() function removes the need to use horrors like these!
Whether or which is correct/better is a bit above my pay-grade, but you can see from the discussion under the first link's answer (> 250 upvotes!) that it's a complex task to get right but also that regular expressions can be very powerful!
However, all is not rosy in the garden - if you look at my complex solution fiddle, I've added some regular expression tests and MySQL fails on a few - notably those using captured groups (that is, bracketing using ( and ).
An example is this one (see the MySQL 8 complex fiddle):
It SHOULD work like this (see the last code snippet in the PostgreSQL fiddle here):
SELECT
  REGEXP_REPLACE
  (
    ip_v6, '(^[[:xdigit:]]{1,4}):([[:xdigit:]]{1,4}):([[:xdigit:]]{1,4}):(.*$)', '\4 xx \3   \2   \1'
  )
  AS "Rep text 2",
  ip_v6
FROM test;

Result:
Rep text 2                                      ip_v6
FFF0:0000:8a2e:0370:7334 xx 0000   0db8   2001  2001:0db8:0000:FFF0:0000:8a2e:0370:7334
123:123:123:123:123      xx  321    654    987  987:654:321:123:123:123:123:123

Notice the 5 last tokens of the ip_v6 string followed by xx followed by tokens 3, 2 & 1 in that order - i.e. reversed as per the captured groups (\3 &c.) in the (brackets).
MySQL returns (fiddle - last snippet in the fiddle):
Rep text 2      ip_v6
4 xx 3   2   1  123:123:123:123:123:123:123:123
4 xx 3   2   1  2001:0db8:0000:FFF0:0000:8a2e:0370:7334

MySQL simply treats the replacements for the captured groups (i.e. what would be the tokens) as string literals (4, 3, 2, 1) - this is quite simply appalling - instead of failing, it returns the WRONG DATA. MySQL does the same thing - incorrect data instead of failing for character class shortcuts - \d fails as a replacement for [[:digit:]] which works for PostgreSQL. Again, instead of failing, it returns an incorrect result!
In this PostgreSQL fiddle, I set up the GENERATED columns and my FOREIGN KEYs as per the MySQL complex solution fiddle - however, with PostgreSQL, there is a total showstopper for the OP's requirements.
There has to be a UNIQUE index (not just a simple one) on that column for it to be able to be used as an FK. Just for the sake of completeness, I did this, but it completely puts the kibosh on using PostgreSQL for this question's requirements - but hey, I learnt a lot going through all of this - I hope you have too!


Is a separate execution plan generated for each dynamic SQL query that is executed within a stored procedure?

Yes, the dynamic SQL you submit will be treated as ad-hoc. I.e., a hash is generated from the text, making it sensitive to everything (space, upper/lower, search arguments, etc).

If you happen to submit the exact same string as a regular ad-hoc (not dynamic sql from a proc), or from other procedures as dynamic SQL, then the plans can be re-used.

What is best for you, we can't say. An option for you is to use sp_executesql and parameterize your queries to allow for plan re-use. OTOH, you will now have parameter sniffing. Either "pay" for plan generation for every execution or re-use plans. Your choice.


mysql diff and add diff records into new database

mysqldump --insert-ignore --extended-insert=FALSE  --complete-insert
  --skip-disable-keys --no-create-info -h A --port=3306 -u A
  --password=A database_A table_A | mysql -h B -u B --password=B database_B

--insert-ignore eventually worked as diff and made the rows unique while dumping data across database.


How to create an aggregate to find min and max value of an integer column and return output in text format?

you need to dynamic sql as seen below
as seen in the last query, you need to use the result of the query and also address the return values

CREATE TABLE towns ("id" int)

INSERT INTO towns VALUEs (1),(2),(3)

select format('%s - %s',min(id),max(id) )min_max_vals  from towns;

| min_max_vals |
| :----------- |
| 1 - 3        |

create function min_max_val( cols text,tables text)
returns SETOF RECORD
 LANGUAGE plpgsql
 AS $$

  begin

      RETURN QUERY EXECUTE 'select format(''%s - %s'',min('||$1||E'),max('||$1||E') ) AS min_max_vals from '||$2||E';';

 end
$$ ;

SELECT min_max_vals
FROM
    min_max_val('id','towns')
    AS t( min_max_vals tEXT)

| min_max_vals |
| :----------- |
| 1 - 3        |

db<>fiddle here


Unknown system variable 'binlog_checksum'

That's because your master is running MySQL 5.5 (or earlier), and the slave is running MySQL 5.6. The binlog_checksum global variable was introduced in 5.6, and the slave is trying to find out if that feature is enabled on the master. But the variable doesn't exist on your master. This shouldn't cause any data loss, and the warning will go away once you upgrade your master.

  Client requested master to start replication from impossible position

Sounds like your master crashed before it could write a final "Rotate to next binlog" event at the end of a binlog file. So the slave doesn't know to advance to the next binlog.

You can manually change the slave to read from the next binlog:

mysql> STOP SLAVE;
mysql> CHANGE MASTER TO MASTER_LOG_FILE='mysqliv-bin.000143', MASTER_LOG_POS=4;
mysql> START SLAVE;

But first, double-check that mysqliv-bin.000143 exists on the master with SHOW BINARY LOGS.

The following blog explains in more detail:
http://www.skysql.com/blogs/adam-donnison/client-requested-master-start-replication-impossible-position


These are clearly bugs in particular 4.1 version , refer these bug reports:

http://bugs.mysql.com/bug.php?id=24294

http://bugs.mysql.com/bug.php?id=9852


Change column without interrupting service

There are at least two tools which attempt to mitigate the pain on online shema modifications - both by big hitters in the MySQL world. One is pt-online-schema-change from Percona and the other is oak-online-alter-table from Shlomi Noach. Could depend on whether you're more at ease with perl (percona) or python (Noach).

Needless to say, I urge you to test before implementing a solution on production.

[EDIT1]

It's unclear as to which of the toolkits (or which particular tool, if any) works with Windows - no mention of an install for oak-toolkit - there's one mention of Windows in the 366 page Percona pt-toolkit manual. In any case, for Windows, an install from source appears to be necessary.

There is however a tool by Facebook called OSC (Online Schema Change) which is a PHP script - could be suitable for Windows? Windows isn't really my thing, but there's an article here.

[EDIT2]

There's yet another solution available here - it involves replication, cutting over and cutting back. Appears to have the advantage of working under Windows. Again, you can test (and YMMV).

Found something even better - here. Fromdual are big on the MySQL scene. You're on 5.6 so this should apply.


Should dbatools be installed on all SQL Servers

In general you should be able to use most of the dbatools functionality from your laptop (or whatever host you use to connect to your server via SSMS). If you're the kind of admin who prefers to RDP onto your servers and use SSMS locally, then maybe try to break that bad habit; but in general you should be able to use dbatools alongside the rest of your administrative toolset.

If you've never used powershell at all in your environment before, then you may need to coordinate with your sysadmins or networking team to debug some PS Remoting  growing pains or open up ports here and there. As well, if you want to use dbatools functions as a part of SQL Server Agent Jobs, you will need to install (and upgrade!) dbatools locally (or in a way that the module can be loaded by the agent). I recommend this guidance

And as always - for any specific issues, the SQL Community dbatools slack channel is the bee's knees.


Query to find customers that have deposit in all branches located in a specific city

I have two solutions for your question :
solution 1 , I use Not Exists , Except

Select distinct C.CustomerName
From customers C
where NOT EXISTS ((Select BranchName
                   From Branch
                   Where BranchCity='Bahar')

                      EXCEPT

                   (Select BranchName
                    From Deposit a
                    where A.CustomerName = C.CustomerName))

In solution 2 I use Not Exists ,

Select Distinct C.CustomerName
From Customers C
Where NOT EXISTS  (Select BranchName
                   From Branch b
                   Where BranchCity='Bahar'
                   and NOT EXISTS (select BranchName
                                   from Deposit a
                                   where a.CustomerName=C.CustomerName
                                   and a.BranchName=b.branchName))

Each one of the query above will answer your query and give you the desired result.


How do I use a dynamic command with copy from program?

If you want to use dynamic commands, you need the right context for it.  Plain SQL is not one.

One possibility is using PL/pgSQL, and it's EXECUTE functionality.  Use DO to get into the context:

DO $$
DECLARE page integer;
BEGIN
FOR page IN SELECT i FROM generate_series(1, 10) AS t(i)
LOOP
    EXECUTE format($cmd$ COPY _temp
                         FROM PROGRAM 'curl -uri "https://url?&page=%s"'$cmd$,
                   page::text);
END LOOP;
END; $$;

The other way is to run the loop on the shell side.  The following example is using bash:

for i in $(seq 1 10); do
    psql -c "COPY _temp FROM PROGRAM 'curl -uri https://url?&page=${i}'"
done

Notes:

I am using two levels of dollar-quoting ($$ and $cmd$) to prevent quote multiplication.
I am also using format() instead of the messy concatenation.
If you really have to quote the URI of the curl command, you will have to escape the quotes in there.


How can I prevent MongoDB from logging the contents of queries under any circumstance?

Yes,with 3.4 Enterprise version it is possible. Log Redaction.


Are RANK() and DENSE_RANK() deterministic or non-deterministic?

According to official Microsoft BOL DENSE_RANK is nondeterministic (RANK()). But according to Ranking Functions by Itzik Ben-Gan "... the RANK() and DENSE_RANK() functions are always deterministic". Who is right?

They are both right, because they are using different senses of the word "deterministic".

From the SQL Server optimizer's point of view, "deterministic" has a very precise meaning; a meaning that existed before window and ranking functions were added to the product. To the optimizer, the "deterministic" property defines whether a function can be freely duplicated within its internal tree structures during optimization. This is not legal for a non-deterministic function.

Deterministic here means: the exact instance of the function always returns the same output for the same input, no matter how many times it is called. This is never true for windowing functions, by definition, because as a (single-row) scalar function, they do not return the same result within a row or across rows. To state it simply, using ROW_NUMBER as an example:

  The ROW_NUMBER function returns different values for different rows (by definition!), so for optimization purposes it is nondeterministic

This is the sense BOL is using.

Itzik is making a different point about the determinism of the result as a whole. Over an ordered input set (with suitable tie-breaking) the output is a "deterministic" sequence. That is a valid observation, but it is not the "deterministic" quality that is important during query optimization.


You can try and compare this yourself with DBCA.

When you create a new database with DBCA and choose the Custom Database option, that does not include datafiles, and creates a database from scratch by running scripts to build the dictionary and other objects. This may take 15-60 minutes depending on the hardware and options selected.

When you choose the General Purpose option, that in fact performs an RMAN restore of a database that was already prepared and included in the database software. This should not take more than 5 minutes. It is indeed faster. (I do not like it though, because it contains extra cost options such as Spatial or OLAP, and I prefer to keep the database small and simple, so by default I skip all options, even Java and Text).

With DBCA, you can use an existing database as source for creating a template that includes datafiles as well, so yes, you can build your initial database, create a template from it, and reuse it.


I have multiple ibdata files : What should I do with them?

Disclaimer : Never Used MySQL Enterprise Monitor
In reality, you do not need the first one as it has not been written in years. However, the default datadir for a MSI-installed mysql instance is "C:\Program Files\MySQL\MySQL Server 5.0\data". You should leave it alone in case you want to create another local instance of MySQL or use it as a template datadir for another local instance.
As for the other two ibdata1 files, one may be needed by MySQL Enterprise Monitor and the other for your actual data.
To be sure, you need to check if you are running Multiple Instances of MySQL.
There are two methods
METHOD #1 Check via the Services List

Start
Control Panel
Administrative Tools
Services
Scroll through the List of Services
Look for any user-defined MySQL service names

If you see multiple MySQL services defined in Services, you have multiple MySQL instances running.
METHOD #2 : Check via the Task Manager

Right Click the Taskbar
Click Start Task Manager
Click Processes Tab
Scroll through the Process List
Count how many mysqld.exe entries you see

If you see multiple mysqld.exe entries in the Task Manager, you have multiple MySQL instances running.
NEXT STEP
Judging from the date and time of each ibdata1 file and their respective folders, my guess is that you have MySQL Enterprise running and it has its own MySQL instance. You need to check the MySQL Enterprise Documentation on moving Enterprise Monitoring Information to anther disk location.
As for C:\MySQL Datafiles, you need to locate the my.ini being used. You should be able to have MySQL Enterprise Monitor tell you. In the event MySQL Enterprise Monitor does not any mechanisms for editing the datadir or migrating the MySQL data folder, you could also get it from the Services entry:

Start
Control Panel
Administrative Tools
Services
Scroll through the List of Services
Right click on the MySQL Service
Choose Properties
Scroll left-to-right through the command for the how the Service is started. You should see the location of the my.ini

Once you find the my.ini

open it with Notepad or Wordpad
edit the datadir setting to new folder location on Drive E:\
save the file

Migrate the Data from C: to E: with something like this:
mkdir "E:\MySQL Datafiles"
xcopy "C:\MySQL Datafiles" "E:\MySQL Datafiles" /s

Then startup MySQL. If the MySQL Enterprise Monitor cannot be used to start MySQL then do net start mysql.
While you could move the whole folder C:\Program Files\MySQL\Enterprise\Monitor\mysql\data over to Drive E:\ with
mkdir "E:\MySQL_EMD"
xcopy "C:\Program Files\MySQL\Enterprise\Monitor\mysql\data" "E:\MySQLEMD" /s

you need to make sure MySQL Enterprise Monitor is full shut down. You also need to check for configuration settings for MySQL Enterprise Monitor to make sure it knows where its mysql data is located. Please don't move anything until you find this out. Consult either the Documentation or the Enterprise Forums on this aspect.
Sorry, I am not a user of MySQL Enterprise Monitor, but I hope I gave you a starting point.


sql 2005: Timeout- Error 1222 Severity 16 State 18

I'd start by doing three things:

Make sure of what the user actually means by "the query is timing out". Sometimes you get false positives from someone who doesn't understand that a connection timeout and a query timeout are different things.
If it's not a connection timeout, go ahead and analyze the execution plan on the query the user is running. Especially if by "user" you mean "non-DBA who has ad-hoc query access to the database". 10 to 1 that query has a Cartesian join in it. Or nested cursors on a large dataset. Or some other such tomfoolery.
Check the Activity Monitor for recent long-running queries and also analyze those. Usually the SPID that kicks off a timeout message is not the actual culprit. It times out because another process is consuming resources inefficiently, shutting out subsequent processes that need those resources.

Even though #3 is the most likely solution, go ahead and do #2 because I've found by experience that any query a user complains about is likely to have something in it that can be significantly tuned for better performance, even if the main issue was something else outside the query.

Plus, it's good customer service to give their complaint a reasonable amount of due consideration. Doctors check the vitals even of known hypochondriacs.  :)