Why is my PostgreSQL expression index not being used when I ORDER BY in a subquery?

Query optimizers generally work by figuring out how to limit the number of possible rows for the query as quickly as possible, based on what it knows about the tables and their data, and the options it has on how to get to that data.

One of the factors it has to consider is how many pages it will have to load to get to the data you need. Reading pages from disk is one of the most expensive things a DBMS does, so if it can deal with fewer pages, that's generally going to be a good thing.

Your query doesn't normally include any check on a+b. So, that index will not help with the selection of the data, and there's no reason to load it in.

Once the rows to use have been identified, is it useful to use the (a+b) DESC index to sort them? I can only see two ways to do this:

Use the index backwards: instead of identifying rows based on values for a+b, we look through the index starting at the max value for a+b, and check each associated row to see if it's one of our candidates, stopping when we get ten rows. This requires that we load an unknown number or pages of the index, and then look up rows to see if they exist in the candidate data. Barring situations where we know almost all rows are candidates, I can't see this as being fast.
Calculate the a+b values for the candidate rows, and then use the index to sort: Since one of the ways to match the index against the values of a+b from the table is to sort the calculated values of a+b from the table, it would make more sense to do exactly what it sounds like the engine did: calculate a+b from the candidate rows and sort that directly, without bothering to load the index pages as well.

When you add a search criteria that involves a+b, the picture changes completely. Instead of having to use the index backwards (finding a+b values based on the rows), it actually needs to find rows based on a+b values. If it can keep the data in the sorted order while checking the other criteria, then yes, it would use that sorted order, and not have to perform a separate step to sort the data.

You should also note that, if your primary key is a clustered index, then the data is actually stored based on that value; that has an impact on how easy it is to sort by that.

Also note that the entire set of returned data has to be sorted by a+b to find the top ten a+b values.

If this query is critical, then you may be able to use variation on a covering index. Normally, this is an index that includes all the columns used in the query (whether in the SELECT list, in table joins in the FROM clause, in the WHERE clause, the ORDER BY - all the columns used).

Note that every column in the query does not necessarily have to be used as a part of the index itself. The actual index should cover enough columns to be useful in narrowing down the rows to be returned. Additional columns are generally included at the "leaf" level - they aren't used to sort the rows from the table, but the data is available in the index.

If all the columns in the query that come from this table are part of the index, or included at the leaf level, then there's no need to actually refer to the table; the covering index has all the data needed, and will normally be smaller than the full table, so we can get everything we need from the index, and load fewer pages.

It sounds like you're bringing back all the columns from the table, so a true covering index won't necessarily help much. However, an index that covers the columns except for those only in the SELECT list might work for you. By this, I mean all the columns from this table used in JOIN clauses, the WHERE clause, any GROUP BY or HAVING clauses, and (in this case) even those in the ORDER BY clause.

Using such an index, the engine could find all the rows it needs to return without referring to the actual table. Since we've included the columns from the ORDER BY clause, it should even be able to determine the 10 rows that need to be returned (thanks to your LIMIT clause). With only ten rows involved, the engine might find it faster to identify the ten rows using the index, and then to look those 10 rows up in the actual table to get the full set of values to be returned.

When a number of columns that aren't in any index it's using are involved in the identification of the actual rows to return, the engine is more likely to rely on a table scan for at least part of the query. It has to retrieve the final result set from the table, so if it thinks it will have to load more pages if it uses the index and then looks up the rows in the table, instead of going straight to the table, it might well choose to do that.

Again, please remember this is based on almost no specific information. This will work best if there are many columns (or even a few very large columns) that aren't needed in the query to identify the rows to be returned, but are only part of the ultimate SELECT list.

With respect to this statement:

  However, the index expressions are not recomputed during an indexed search, since they are already stored in the index.

I think you may be generalizing it a bit too much. Without the expression index, to search for all rows where a+b >= 12000000, a+b has to be calculated for each row. By having this value indexed, we can easily identify rows where the value of a+b meets our criteria. However, this does not mean that the value of a+b is also pre-computed at the table level. If there's no way to use the index to identify our rows, then (as discussed early in this answer) the index won't be used at all. And, if the index is not used, then a+b does still have to be calculated. Indexes are designed to find specific rows based on the indexed values, not to find the indexed values based on specific rows.


Actually, your problem comes from temporary tables, that may be created in MyISAM format (in fact, up to 5.5, this was the only format for temporary tables on disk), not from the system tables in the mysql database, that are mostly also in MyISAM format.

This may be caused by a new feature of 5.6, where subquery execution may generate indexes for faster processing.

By experience, do not trust what a simple filesystem inspection may tell you about available free space- in particular with files that are deleted very often, but they have still a file descriptor open.

There are many things that you could check here: temporary files created by your processes, integrity of your /tmp filesystem partition, if you have recently mounted/unmounted that partition, trying to change your /tmpdir, etc. Unfortunately Percona Server 5.5 SHOW TEMPORARY TABLES is not available in 5.6 (yet?).


Finally I got the answer. Below is the fiddle.

sqlfiddle.com/#!9/83f530/6 or sqlfiddle.com/#!9/83f530/7

Thanks to Akina who helped me a lot. I will prefer to use single join SQL over multiple join fiddle.


Which is better MAX function or ORDER BY column_name DESC with LIMIT 1

Add an index on created_at and use your first query:

SELECT MAX(created_at) AS latest FROM sample_table;

The suggestion you got is good in other situations, like if you want a max value of a field that is smaller than certain value. i.e.

SELECT created_at FROM sample_table WHERE created_at<"2014-01-01 00:00:00" ORDER BY created_at DESC LIMIT 1;


Is there any reason to maintain audit columns when the target table already has an audit table?

If you are going to follow the pattern of inserting into the audit table after each insert, no there is no need to retain the creation/alter timestamps as they are present in the audit table.
However, if the pattern is to only insert into the audit table after an update/delete (this is more common), then you will need to retain any relevant timestamps/user info with the data so the original data can be copied into the audit table.
Generally, unless there is a very specific use case, it isn't necessary to store the created at/created by information as this is easily derived from the audit information itself.


centos container, installing mariadb server

It sounds like you're new to Docker. You may want to spend some time reading through documentation and examples to get a better feel for how things work.

First, simply installing software doesn't necessarily make it run, and that is certainly the case with CentOS and other Red Hat variants.  On a bare metal host, you would simply run systemctl start mariadb to start it, but this only works because on a regular system, the /sbin/init component of systemd is started as part of the boot process.

Inside a docker container, nothing is running by default.  For example, if you were to run ps -fe on your host, you would probably see hundreds of running processes.

If you were to run:

docker run centos ps -fe

You would see:

UID        PID  PPID  C STIME TTY          TIME CMD
root         1     0  0 11:12 ?        00:00:00 ps -fe

Because of this, you can't start services using the normal mechanism.  You will instead need to (a) start the service manually and (b) ensure that you have started it such that it will stay in the foreground (because a Docker container exits when the foreground process exits or puts itself into the background).

If you take a look at the official mariadb image, you will see that it ultimately just calls mysqld to start the service.

It sounds like you want to start mariadb inside a container and then try interacting it from within the same container.  While this isn't the normal way of working with docker, you could...

$ docker run -it centos bash
[root@c1d6adf5c8bb /]# yum -y install mariadb-server
[root@c1d6adf5c8bb /]# /usr/libexec/mariadb-prepare-db-dir mariadb.service
[root@c1d6adf5c8bb /]# /usr/bin/mysqld_safe --basedir=/usr &
[root@c1d6adf5c8bb /]# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 1
Server version: 5.5.52-MariaDB MariaDB Server

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]>

More typically, you would use a Dockerfile to build a mariadb image (or just use the official one), start it, and then use mysql on your host or in another container to interact with that mariadb instance.


ORDER BY items must appear in the select list [...]

If an alias is used in an ORDER BY it must be used on its own, not inside an expression.

If inside any kind of expression it tries to resolve it to a column in the base table sources not as an alias.

So for example

SELECT A AS B
FROM   (VALUES (1, 3),
               (2, 2),
               (3, 1)) V(A, B)
ORDER  BY B

Returns (ordered by alias)

+---+
| B |
+---+
| 1 |
| 2 |
| 3 |
+---+

But

SELECT A AS B
FROM   (VALUES (1, 3),
               (2, 2),
               (3, 1)) V(A, B)
ORDER  BY B + 0

Returns (Ordered by base table column B)

+---+
| B |
+---+
| 3 |
| 2 |
| 1 |
+---+

You can of course just wrap the whole thing in a derived table or CTE.

WITH T AS
(
SELECT 1 AS foo, 2 AS bar
UNION ALL
SELECT 10 AS foo, 20 AS bar
)
SELECT *
FROM T
ORDER BY CASE WHEN foo = 1 THEN bar END;


How to ensure backups are successfully persisted to S3 when using Ola Hallengren solution on AWS EC2 Instances

Have you tried using the AWSCLI? I have an Agent Job that runs a .bat after my backups are complete. The bat runs the sync command. I don't delete anything from S3, I simply let the CLI sync new or changed files up to my bucket. I then use bucket policies to manage the lifetime of my backups that are up there.
My bat script is below. You will need to provide your own paths. Don't forget to run AWS Configure to setup the CLI. I suggest creating a specific profile that has least privilege access to upload to S3. This is for Windows.
setlocal

set  tm=%time: =0%
set  tm=%tm:~0,2%_%tm:~3,2%_%tm:~6,2%
set dt=%date:~10,4%_%date:~4,2%_%date:~7,2%

echo "Starting..." >> "L:\Logs\SyncBackups\s3SyncBak_%dt%_%tm%.txt"

aws s3 sync "B:\Database" "s3://MyBucketName/ProductionDatabaseBackups" --exclude "*" --include "*.bak" --profile MyProfileName >> "L:\Logs\SyncBackups\s3SyncBak_%dt%_%tm%.txt"

echo "Synced with no problem ..." >> "L:\Logs\SyncBackups\s3SyncBak_%dt%_%tm%.txt"


With confirmation from Pix(from Chat) the correct way to remove cumulative update is to use
/RemovePatch instead of using /uninstall. The way to remove updates is documented in Installing Updates from Command Prompt.

The script would be

"sqlserver2014-kb4032541-x86_080a5c3a38b721a9587c2072375921161896e19d.exe" /qs /Action=RemovePatch /InstanceName=InstanceName /SkipRules=Cluster_IsOnlineIfClustered


I'll get a answer started for you on the multiple column index questions.

Multiple Column Indexes

CREATE INDEX myindex ON table (clicks, impressions);

  MySQL can use multiple-column indexes for queries that test all the columns in the index, or queries that test just the first column, the first two columns, the first three columns, and so on. If you specify the columns in the right order in the index definition, a single composite index can speed up several kinds of queries on the same table.

Example

CREATE TABLE test (
    id         INT NOT NULL,
    last_name  CHAR(30) NOT NULL,
    first_name CHAR(30) NOT NULL,
    PRIMARY KEY (id),
    INDEX name (last_name,first_name)
);

  The name index is an index over the last_name and first_name columns. The index can be used for lookups in queries that specify values in a known range for combinations of last_name and first_name values. It can also be used for queries that specify just a last_name value because that column is a leftmost prefix of the index (as described later in this section). Therefore, the name index is used for lookups in the following queries:

SELECT * FROM test WHERE last_name='Widenius';

SELECT * FROM test
  WHERE last_name='Widenius' AND first_name='Michael';

SELECT * FROM test
  WHERE last_name='Widenius'
  AND (first_name='Michael' OR first_name='Monty');

SELECT * FROM test
  WHERE last_name='Widenius'
  AND first_name >='M' AND first_name < 'N';

  However, the name index is not used for lookups in the following queries:

SELECT * FROM test WHERE first_name='Michael';

SELECT * FROM test
  WHERE last_name='Widenius' OR first_name='Michael';

Your Query

This query would not be used in lookups on the index.

SELECT (fields) FROM adverts WHERE clicks > 0 OR impressions > 0;

But this one would.

SELECT (fields) FROM adverts WHERE clicks > 0;

This is why I suggested campaign flags because you can combine the flags in your index to indicate if clicks, impressions OR both are present.

CREATE INDEX myindex ON table (campaign_flag);

To query

SELECT (fields) FROM adverts WHERE campaign_flag = 'Search';  --Clicks Exist
SELECT (fields) FROM adverts WHERE campaign_flag = 'Display'; --Clicks/Impressions Exist

Campaign Flag / Campaign Type Suggestion

If this is for digital marketing, campaign flags or campaign types could be used to further filter down to the clicks and impressions you are really needing.

Let's say there are 1,000 records in the table. If you query looking for both clicks and impressions > 0, then you will search all 1,000 records for a result.

SELECT
   (fields)
FROM adverts
WHERE campaign_flag = 'Display' --Searching for Clicks and Impressions
AND (clicks > 0 OR impressions > 0);

Now let's add campaign_flag field to the table and divide our data up into segments. 500 records are marked as 'Display' and 500 are marked 'Search'. This is a much better approach to index on as opposed to the full 1,000.

SELECT
   (fields)
FROM adverts
WHERE campaign_flag = 'Search' --Searching for just Clicks
AND clicks > 0;

Compensation Models

Compensation models go hand-and-hand with campaign flags or types. Both Search and Display could have a range of different models now and in time. Knowing you have 500 Display records in the previous example, you could also have 250 that are CPC and the other CPM

SELECT
   (fields)
FROM adverts
WHERE campaign_flag = 'Display' --Searching for Clicks and Impressions
AND cmodel = 'CPM' --Further filtering based on type of model
AND clicks > 0;

http://www.basimos.com/web-marketing/internet-marketing-glossary-c.htm


Merge 2 columns and replace with specific output

Create a translation table, let's call it sub1_dict:

CREATE TABLE sub1_dict (
   first int NOT NULL
 , sub   varchar NOT NULL
 , name  text NOT NULL -- find a better name than "name"
 , CONSTRAINT t_pkey PRIMARY KEY (first, sub) -- INCLUDE (name) -- see below
   );

Enter all defined translations in this table.

Since you specified:

  I only need the output where I have a defined "replacement name"

... use an [INNER] JOIN in the query, excluding rows without translation:

SELECT s.name, r.value
FROM   rawdata r
JOIN   sub1_dict d USING (first, sub)
WHERE  r.first < 3;

The added PK should serve your purpose. The INCLUDE clause requires Postgres 11. The intention is to allow fast index-only scans - not important with small tables.

Same for your table rawdata: if it's big, add an index on (first, sub). Or a PK or UNIQUE constraint. Column first first to also serve your WHERE clause optimally. See:

Is a composite index also good for queries on the first field?

If you get index-only scans out of it, consider another covering index like above, with INCLUDE (name) in Postgres 11 or later.

Can Postgres use an index-only scan for this query with joined tables?


Faster alternative to scalar UDF with recursion to walk tree hierarchy?

Let's start with this. Let us know if it looks something like this. Please edit as necessary. I am adding data into the table:

INSERT INTO Customer (CustomerID, ParentCustomerID, IsBillToCustomer)
select  1   ,   0   ,   0   union all
select  2   ,   0   ,   0   union all
select  3   ,   0   ,   0       union all
select  4   ,   1   ,   0   union all
select  5   ,   2   ,   0   union all
select  6   ,   3   ,   1   union all
select  7   ,   1   ,   1   union all
select  8   ,   2   ,   1   union all
select  9   ,   3   ,   1   union all
select  10  ,   1   ,   0   union all
select  11  ,   2   ,   0   union all
select  12  ,   3   ,   0   union all
select  13  ,   1   ,   0   union all
select  14  ,   2   ,   0   union all
select  15  ,   3   ,   1   union all
select  16  ,   1   ,   1   union all
select  17  ,   2   ,   1   union all
select  18  ,   3   ,   1   union all
select  19  ,   1   ,   0   union all
select  20  ,   2   ,   1   union all
select  21  ,   3   ,   1   union all
select  22  ,   1   ,   1   union all
select  23  ,   2   ,   0   union all
select  24  ,   3   ,   0   union all
select  25  ,   1   ,   1   union all
select  26  ,   2   ,   1   union all
select  27  ,   3   ,   1   union all
select  28  ,   1   ,   1   union all
select  29  ,   2   ,   0   union all
select  30  ,   3   ,   0   union all
select  31  ,   1   ,   0   union all
select  32  ,   2   ,   0   union all
select  33  ,   3   ,   0   union all
select  34  ,   1   ,   1   union all
select  35  ,   2   ,   1   union all
select  36  ,   3   ,   1   union all
select  37  ,   1   ,   1   union all
select  38  ,   2   ,   0   union all
select  39  ,   3   ,   1   union all
select  40  ,   1   ,   1   union all
select  41  ,   2   ,   1   union all
select  42  ,   3   ,   0   union all
select  43  ,   1   ,   0   union all
select  44  ,   2   ,   1   union all
select  45  ,   3   ,   1   union all
select  46  ,   1   ,   1   union all
select  47  ,   2   ,   1   union all
select  48  ,   3   ,   0   union all
select  49  ,   1   ,   0   union all
select  50  ,   2   ,   0   union all
select  51  ,   3   ,   0   union all
select  52  ,   1   ,   0   union all
select  53  ,   2   ,   1   union all
select  54  ,   3   ,   1   union all
select  55  ,   1   ,   1   union all
select  56  ,   2   ,   1   union all
select  57  ,   3   ,   0   union all
select  58  ,   1   ,   1   union all
select  59  ,   2   ,   1   union all
select  60  ,   3   ,   1   union all
select  61  ,   1   ,   0   union all
select  62  ,   2   ,   0   union all
select  63  ,   3   ,   1   union all
select  64  ,   1   ,   1   union all
select  65  ,   2   ,   1   union all
select  66  ,   3   ,   1   union all
select  67  ,   1   ,   0   union all
select  68  ,   2   ,   0   union all
select  69  ,   3   ,   0   union all
select  70  ,   1   ,   0   union all
select  71  ,   2   ,   0   union all
select  72  ,   3   ,   1   union all
select  73  ,   1   ,   1   union all
select  74  ,   2   ,   1   union all
select  75  ,   3   ,   1   union all
select  76  ,   1   ,   0   union all
select  77  ,   2   ,   1   union all
select  78  ,   3   ,   1   union all
select  79  ,   1   ,   1   union all
select  80  ,   2   ,   0   union all
select  81  ,   3   ,   0   union all
select  82  ,   1   ,   1   union all
select  83  ,   2   ,   1   union all
select  84  ,   3   ,   1   union all
select  85  ,   1   ,   1   union all
select  86  ,   2   ,   0   union all
select  87  ,   3   ,   0   union all
select  88  ,   1   ,   0   union all
select  89  ,   2   ,   0   union all
select  90  ,   3   ,   0   union all
select  91  ,   1   ,   1   union all
select  92  ,   2   ,   1   union all
select  93  ,   3   ,   1   union all
select  94  ,   1   ,   1   union all
select  95  ,   2   ,   0   union all
select  96  ,   3   ,   1   union all
select  97  ,   1   ,   1   union all
select  98  ,   2   ,   1   union all
select  99  ,   3   ,   0   union all
select  100 ,   1   ,   0   union all
select  101 ,   2   ,   1   union all
select  102 ,   3   ,   1   union all
select  103 ,   1   ,   1

So, let's run a simple CTE with the data we have. Then from here show us where we are and what we're trying to achieve:

;With Parent(CustomerID, ParentCustomerID, IsBillToCustomer)
As
(
   Select c.CustomerID, c.ParentCustomerID, c.IsBillToCustomer
    from Customer c
    WHERE c.ParentCustomerID = 0
    UNION ALL
  Select c.CustomerID, c.ParentCustomerID, c.IsBillToCustomer
    from Customer c
    inner join Customer p on p.CustomerID=c.ParentCustomerID
)

Select *
  from Parent p

Help us understand the problem. Thanks.


How to pass Oracle Wallet auth to sh script?

Create an auto-login wallet.

Creating an Auto-Login Wallet

Then connect to the database using the below syntax:

sqlplus /@connect_string @release.sql

connect_strings must be the same connect_string that is stored in the wallet. Otherwise the credentials in the wallet will not be used.


Modelling a Database for Recording Taxes

You've specified four types of taxes, but

one of them is user defined unfortunately that probably work. You'll have to define the types of taxes users can apply. How will they design the logic for a tax? Unless your providing a logic system, it seems unlikely.
a compound tax is really just two simple taxes paid to different taxing authorities, so we'll drop the designation of simple and complex and normalize that away.

  Problem with this design can be best described in one word - Complicated. It will create a complicated hierarchy of taxes with even more complicated queries.

The purpose of taxes is quite clear. They're supposed to be complicated. They're puzzles to confuse working class folks so rich capitalists can force their surfs to foot the bill for their own private thugs, armies, and corporate bailouts.

As far as a schema critique. I don't think the self-referencing design makes much sense here, and the constraints are impossible without triggers. I'd ditch it.

CREATE SCHEMA tax;

CREATE TYPE tax.cat AS ENUM ('NET', 'GROSS');
CREATE TABLE tax.authority ( authority_id, authority_name, cat, rate, subject )
AS VALUES
  ( 1, 'CA',      'NET',   CAST(0.025 AS real) ),
  ( 2, 'NATIONAL, 'GROSS', CAST(0.025 AS real) );

Now you link them together in a linking table. You can easily calculate the 0.050 using sum() in a subselect. To change the modes of application just do something like,

SELECT
  p.price,
  p.price * t.gross AS gross_tax,
  (p.price - p.cost) * t.net AS net_tax
FROM product AS p
-- should be an inner join if you have a method of going from
-- products to tax authorities
CROSS JOIN (
  SELECT
    sum(rate) FILTER (WHERE cat='NET') AS net,
    sum(rate) FILTER (WHERE cat='GROSS') AS gross
  FROM tax
) AS t;

Taxes are usually a function of locality, so I would use GIS or allow people to pick their own taxing authorities and what should be collected.


kfed is only used to access asm disk headers and metadata - it won't help you in case of a full disk corruption.

You should take the bad disk offline and recover the disk group using the remaining good disk.

Regards,
Jony


Which privileges for MySQL extended status?

Taken from the MySql documentation:

  This statement does not require any privilege. It requires only the
  ability to connect to the server.


Is adding rows better then adding columns in case their value is not consistent?

First, it's about normalization. Today you have max 30 special products, tomorrow (or after 10 years for that matter) you might need another extra 5. You will need to write more code, but you will have greater flexibility after all. At least you won't need to code (or design) 30 columns!
You can have a schema like this:

CREATE TABLE Product (ProductId...)
CREATE TABLE Customer (CustomerId...)
CREATE TABLE CustomerProductPrice (ProductId, CustomerId, Price...)

In the third table you can put price for product/customer, but remember to create a unique constraint on foreign key columns like ProductId, CustomerId.


How to modify a stored procedure using SQLCMD for MS SQL?

Assuming that you have the permission to do so then there should be no reason why you could not. The following example assumes that you are using Windows Authentication:

sqlcmd -E -S computer\instance -d database

alter procedure testtable_pricesmaller
@pricelimit money
as
select * from testtable where price = @pricelimit;
go

Alternatively if you have an existing script file that has the command you want to run you could use the -i option to run that file:

sqlcmd -E -S computer\instance -d database -i file.sql

Here is some more information on the sqlcmd utility that you should find helpful:

http://msdn.microsoft.com/en-us/library/ms180944.aspx

I hope this helps you.


Speedup DROP huge MySQL replicated table

In the end we realized that the process was so slow because we did try to do the DROP while the mysqldump backup was running and the table was blocked by the backup process.

After the backup process had finished the DROP was quite fast and took only some minutes and did not have a big inpact on the server performance.


Increased server load after InnoDB deadlock

may be you need to clear or reload various internal caches
use these commands
flush tables ;
flush logs ;
you can find more details about flush command in this link
http://dev.mysql.com/doc/refman/5.0/en/flush.html


How to select number of affected rows to variable in function?

create or replace function test()
returns void as $$
declare
    v_cnt numeric;
begin
    v_cnt := 0;

        update tbl set col1 = true where col2 = false;
        GET DIAGNOSTICS v_cnt = ROW_COUNT;

        if v_cnt > 1 then
           RAISE EXCEPTION 'more than one row affected --> %', v_cnt;
        end if;
end;
$$ language plpgsql;


Grant permissions on database except specific tables

Broadly speaking you can do this in two ways.

Grant read privileges on only the tables you want them to have access to. Or,
Grant them access to all tables and then Deny access on the tables you dont want them to have access to.

  The DBA giveth... and the DBA taketh away

There are lots of articles and references available that explain how they work together such as this one.

The keys words to search for are "sql server grant & deny". Permissions can be granted on the whole database, a schema or a specific object. (Table, view, stored proc etc)

This can be applied per user or via a Role. Rather than modify the existing roles, you could create a new role and grant the permissions you want to the role, and the give users that.

The most suitable method depends on how many tables they should/shouldnt have access to and how many users you would have to repeat this for.

If there is 100 tables in the db, and the user should read 90 of them, id grant + deny.

If you had to do the same thing for 5/10/50 users i'd create a role.


Mongodb performance: compound query vs moving to other collection

Since 2.6 MongoDB supports index intersection.

https://docs.mongodb.com/manual/core/index-intersection/

To my general experience your queries are not complex enough to be worried.

You should of course create indices for status (especially since it is a string), customer.id, and timestamp but leave the rest to the MongoDB internals. You can of course create a compound index too, and make some benchmarks to see for yourself using .hint() to force MongoDB to use a specific index.

https://docs.mongodb.com/manual/reference/method/cursor.hint/#cursor.hint

You should also know, that the ObjectId contains a timestamp corresponding to the document creation time already which comes in handy as there is always an index on _id.

https://docs.mongodb.com/manual/reference/method/ObjectId/#ObjectId


Suitable way to record users requests for usage invoicing/billing in database?

For a simple solution, you can add a nullable foreign key to an INVOICE table to your USER_REQUEST.  If you want to know what needs invoicing, just select everything for a given user that has a NULL foreign key.

When you create the invoice, set the foreign key to point to the new invoice.

For a more complex, but more sophisticated solution, you may want to consider an intersection table between the USER_REQUEST and an INVOICE_ITEM table.  This solution will recognize and handle the situation where an item is charged on one invoice, then returned on a credit note, and possibly even re-invoice again, and so on...


InnoDB issue after power outage a few weeks ago - no backup

The InnoDB dictionary contains information about all tables in your database(s).
The dictionary is a set of InnoDB tables hidden from a user.

Looks like a records about example_example/table1 is missing in the dictionary.

I'd explore content of SYS_INDEXES and SYS_TABLES to see if the table is in it. https://twindb.com/how-to-recover-innodb-dictionary/


Turns out the issue was with an assembly created with 'Unrestricted' permissions, when trying the ODBC connection under Excel I received a more verbose error related to a .NET error and this assembly, changing the 'Permission set' to 'Safe' seemed to solve the problem.


What is different between fixed-size disk pages and disk-based?

What Stonebraker is talking about here is essentially his latest baby, VoltDB. This is what Stonebraker (a huge hitter in the database world) calls "NewSQL".

He says that OldSQL (The Oracles, MySQLs, SQL Servers &c. of this world) waste too much of their resources Logging, Latching, Locking and on Buffer Management. These result (largely) from the fact that the data moves back and forth between disk and memory as transactions are processed.

He also says that NoSQL throws the baby out with the bathwater in that it gets rid of something he regards as critical for any database system worthy of the name, i.e. ACID transactions.

He sees VoltDB (and similar NewSQL) as the future of OLTP database systems - this is an in-memory shared-nothing system where transactions are in the form of app-specific customised stored procedures which always run to completion. This architecture eliminates all the logging, latching, locking and buffer management that Stonebraker says is essentially useless work. He famously said that OldSQL should go to the home for "tired software"! You might want to check out this paper. He sees the future of OLAP systems as columnar stores - and was a founder of Vertica (subsequently sold to HP).

Finally, now the background is out of the way, to address your specific question. He is saying that because many of the NoSQL architectures are disk-based (i.e. they don't run in memory), these much-vaunted systems will in fact suffer from the same disadvantages as OldSQL (which they were meant to replace), and so not only will they not be faster, but they won't be consistent either - a double-whammy loss as far as Stonebraker is concerned.

Google such terms as "VoltDB architecture", "NewSQL" and "columnar store". That should give you a few leads in this (very interesting) area.


How to find out those users which have two different access right in postgresql

You can aggregate and check if the distinct count per user is equal to the count of distinct groups of the whole table.

Assuming that the user is identified by a column user_id and the group by a column group_name.

SELECT user_id
       FROM users_group
       GROUP BY user_id
       HAVING count(DISTINCT group_name) = (SELECT count(DISTINCT group_name)
                                                   FROM user_group);


No, no, no, you should definitely not do this. It is the responsibility of the object creator to grant the necessary privileges. (Although this is a common request, and it would be nice to finally have built-in support for this.)

Since you are on 12c, forget SELECT, and grant READ. SELECT is not a read-only privilege. With SELECT granted, the grantee can run SELECT ... FOR UPDATE, lock rows, and interfere. READ does not allow that.

What you asked is however still possible with some dirty tricks, using a DDL trigger, for example:

How do I create a Oracle trigger that grants permissions


SQL Server performance baseline benchmark

There's a few different questions inside your post:

Q: Am I gathering the right counters?

I think you copied that Perfmon counter list from an older version of my Perfmon counter blog post. I've updated the counter list over time, so you may want to refresh it - or check with the folks where you got it, if someone in your team is keeping an offline copy.

Q: How should I gather them?

As I write in that post, I'd recommend capturing them every 15 seconds. (Every minute is fine too.) If you go any longer between captures, you can miss peak bursts of activity.

Q: Should I use the averages to build a baseline?

No - averages miss peak load, and include hours (or even days) of time when your SQL Server doesn't have any activity at all. You'll want to focus on periods of time where your end users are complaining about performance.


Data integration name of best practices to avoid corruption, caused by becoming out of sync?

It's an issue of data integrity. You have two systems that should reflect the same data, and they don't.

Conceptually, a transaction in one system isn't truly complete until it has been applied to both systems. There are several ways to do this: replication is probably the most comprehensive, but comes with a certain amount of both system and human overhead.

The method you're using involves asynchronously applying the transactions. You finish in one system, and catch up the other system periodically. The problem you've identified is that a certain type of transaction is not being caught, and thus is never being applied to the remote system.

This means that, as it stands, the database system as a whole (taking both systems involved as one system, for this table at least), fails to be ACID compliant.

It's not Atomic; a delete transaction in the primary system fails to complete ever, thus the transaction is never completed, and never will be.

It's not Consistent (covered adequately above).

You can probably make an argument for not being Isolated or Durable, if you want to.

And, note that the individual systems may be fine; it's the fact that you've created a hybrid system, and the problem lies there.

There are a number of fixes. I know one's already in the works, but I will mention one that worked for my company: we used a delete trigger to record the deleted records, and made removing deleted records a part of the sync process.

The key is in the fact that you've got a hybrid, home-grown database system. There's nothing wrong with that (for small amounts of data, replication or log shipping entire DBs to get one table are overkill), but it is possible to miss a facet when building such a system, and your system is going to have fewer eyes on it than Oracle, MS SQL Server, or MySQL.

Hope that's of some assistance.


Why is the First Transaction Log backup of the day huge

That depends on what is happening in the night.

If you have additional maintenance jobs (index rebuilds, index reorganizations, statistics updates) then you possibly have a lot of changes occurring.
If you are also importing large amounts of data, then that is another possible source of changes.
Deleting data is also a modification and will be logged.

A database that is truly dormant will normally not have huge transaction log backups.

You may also want to consider either taking a second full backup and a transaction log backup after the nightly maintenance, and use that backup as your base for the day's log backups solely to avoid the massive post-maintenance log file, or to just postpone the full backup and subsequent transaction log backups till after the maintenance.


uniqueness in array column type throughout all records

An exclusion constraint would seem like the weapon of choice:

ALTER TABLE tbl ADD CONSTRAINT tbl_names_overlap EXCLUDE USING gist (names WITH &&);

Alas, as of Postgres 11, still no GiST indexes for arrays. And GIN indexes are not (yet) allowed to implement exclusion constraints. There is a TODO item "Allow GIN indexes to be used for exclusion constraints", but I wouldn't know of any recent progress.

There would be a way with integer numbers after installing the additional module intarray - which provides GiST operator classes. So if your "numbers" are actual numbers ...

Else, here is a workaround I came up with some years ago:

Can PostgreSQL have a uniqueness constraint on array elements?


SQL Server 2008 - Adding a column and a filtered index within a single transaction?

You can do it within the same transaction it just needs to be pushed down into a child scope so compiled separately.

BEGIN TRY
    BEGIN TRAN

        IF NOT EXISTS (SELECT * FROM sys.columns WHERE name = 'NEWCOL_ID'
            AND OBJECT_NAME(object_id) = 'TEST_TABLE')
        BEGIN
            ALTER TABLE TEST_TABLE
            ADD NEWCOL_ID INT NULL

            EXEC('CREATE NONCLUSTERED INDEX NEWCOL_ID_IDX
            ON TEST_TABLE (NEWCOL_ID ASC) WHERE NEWCOL_ID IS NOT NULL')
        END

    COMMIT TRAN
END TRY
BEGIN CATCH
    BEGIN
        ROLLBACK TRANSACTION
        PRINT(ERROR_MESSAGE())
    END
END CATCH;

You can also do it within the same scope and in the same transaction if the table does not yet exist.

DROP TABLE TEST_TABLE

GO

BEGIN TRY
    BEGIN TRAN

        CREATE TABLE TEST_TABLE (ID INT IDENTITY(1,1) PRIMARY KEY)

        IF NOT EXISTS (SELECT * FROM sys.columns WHERE name = 'NEWCOL_ID'
            AND OBJECT_NAME(object_id) = 'TEST_TABLE')
        BEGIN
            ALTER TABLE TEST_TABLE
            ADD NEWCOL_ID INT NULL

            CREATE NONCLUSTERED INDEX NEWCOL_ID_IDX
            ON TEST_TABLE (NEWCOL_ID ASC) WHERE NEWCOL_ID IS NOT NULL
        END

    COMMIT TRAN
END TRY
BEGIN CATCH
    BEGIN
        ROLLBACK TRANSACTION
        PRINT(ERROR_MESSAGE())
    END
END CATCH;

In this second case the reference to the column is subject to deferred compilation as the table does not yet exist when SQL Server compiles the batch. See Deferred Name Resolution and Compilation in BOL

  Deferred name resolution can only be used when you reference
  nonexistent table objects. All other objects must exist at the time
  the stored procedure is created. For example, when you reference an
  existing table in a stored procedure you cannot list nonexistent
  columns for that table.


Unique insert performance: Primary Key vs Unique Index

From your question, I'm not completely sure if you're inserting multiple values per query, but you definitely should be.  With MyISAM as the engine, there should not be a performance difference between the index being UNIQUE or PRIMARY; MyISAM doesn't treat them differently in this case.  If you were using InnoDB, however, there would be a difference since it stores the data in primary key order.  If you don't need the id column, removing it and making domain the primary key would help performance.

Changing the collation should help since ascii is much simpler than utf8, but you might want to use ascii_general_ci instead of ascii_bin since domain names are case-insensitive.

One other way to do the queries would be to get the number of rows, INSERT DELAYED, flush the delayed writes, and then get the new row count.  The difference in the counts would be the same as the affected rows.  However, I don't think this would be significantly faster, but it would make the process more complex.


Does a table with an index ever slow down?

Tables do not slow down or speed up, they just sit there. Statements, on the other hand, can slow down. You don't provide sufficient details about your data, but if your primary key is monotonically increasing, like a timestamp or a sequence value, you'll end up rebalancing your tree quite frequently upon inserts. At the same time with such a primary key you can create a concurrency hot spot at the tail end, where all inserts will be happening.

Meanwhile, growing from 106 to 109 entries in the index takes you from 20 comparisons to 30 for the worst case lookup, which is a 50% increase, so depending on your PK value size it's totally feasible that you exceed your noticeability threshold of 2 seconds with some "billions" of rows in the table.


What is the risk of doing a rebuild to my indexes?

Without knowing details about exactly what type of queries are accessing that data and the indexes you are dealing with a precise answer is not possible.

However, in general, an index rebuild drops and then re-adds that index and locking needs to occur for that to happen. If you have queries that are attempting to access this table while the rebuild is going on, they will likely need to wait for it to finish before they can complete.

I found a very good rundown of the locking in a blog post by Kendra Little; and under the "Online" section of the ALTER INDEX MSDN page you can see Microsoft's description of the timing of these locks.

If you are using Enterprise Edition, you can run the index rebuild (or even create) in online mode (with hint Online = on) and it will not cause blocking.


Usually for this purpose, I add a column in the table saying end user who updated it, and each client populates it when it it inserted/updated.

But if want to track each changes, usually I also add 2 datetime(6) columns in the table called StartDate and EndDate (use DateTime for mysql version before 5.6 not supporting microseconds)

EndDate is added to primary index (preferably at the end).

When row I insert a row into it, I put Sysdate(6) in StartDate and '9999-12-31' in EndDate (year 9999, yes it is hardcoded but if your application is still active at this time, it won't be your problem)

When I want to update an existing row, I close it first with a query like update MyTable set EndDate=sysdate(6), UpdatedBy='currentuser' where key=somekeyvalue and EndDate='9999-12-31' then I insert row with new values as explained earlier

When I look to current values, I always add " and EndDate='9999-12-31') in query

When I look to values applicable on a certain date, ie 2017-02-01
I perform query on a range of dates like this
select * from MyTable where key=somekeyvalue and StartDate<='2017-02-01' and EndDate>'2017-02-01'


Reasons to Backup (or not backup) QA and Dev Databases

QA and dev databases (I will refer to these as non-prod[uction] to encompass all life cycles) are still databases.  So the business (in this case, might be the app dev team) needs to determine what type of disaster recovery they require.

In my opinion, SLAs don't just apply to production databases.  They apply to any data store that requires a certain RPO/RTO.  In the case of development databases, DML might not be a huge concern, but the loss of DDL could be application-breaking.

The point I'm trying to get at, is we here can't answers whether or not you'll want to back up your non-prod databases.  You will need to find out what type of disaster recovery is required of these databases.  There's a chance that database creation scripts generated weekly is enough.  But there's also a possibility they may require native database backups.

Personally, I always resort to some form of backups.  Even if it's simple recovery and just daily full backups.  These non-prod databases are typically not gigantic (although there are definitely exceptions to that rule), and end-user impact isn't usually a big concern in lower lifecycle environments (again, there are exceptions here as well).  I typically err on the side of more DR than less DR.  But I'd still recommend talking to the business group to get clearly defined and documented guidance for disaster recovery.  You don't want to be the reason that a week of development was just lost because you haven't been taking backups.


That's needed because the target database for the rest of that script is created just in the line above that statement. However creating a new database doesn't automatically switch to that database. In order for all following statements to be executed against that new database, the connection needs to be switched to that database.


Can you prevent more than X number of rows being updated in SQL Server?

A trigger would be how I would handle this.  This will slow down all your update statements as SQL now has a bunch of extra work to do, but it'll handle your requirement.

CREATE TRIGGER Something on dbo.SomeTable
FOR UPDATE
AS
BEGIN
IF (select count(*) from inserted) > 30000
BEGIN
   RAISERROR('To many rows being processed at one.  Reduce and try again.', 16, 1)
   ROLLBACK
END
END

Personally I wouldn't like the idea of the instead of trigger and reapplying the update with a smaller row set as this gets into tricky areas of half completed operations now which could get very messy, quickly.


Is it possible to load data into Postgres using COPY, where the data includes a SELECT statement?

You do it like bellow:

psql -c '\copy (SELECT * FROM mytable where col1=something) to mytable.dat'

Then load dat file using COPY

psql -c '\copy mytable from mytable.dat'


Please note this Pictorial Representation of ibdata1 (from Vadim Tkachenko)

You have many moving parts inside ibdata1

Data Dictionary
Double Write Buffer
Insert Buffer
Rollback Segments
Undo Space

Almost 3 years ago, I wrote a post addressing why ibdata1 can grow too fast : How can Innodb ibdata1 file grows by 5X even with innodb_file_per_table set?
If the database experienced lots of INSERTs, UPDATEs, DELETEs, you will likely have all kinds of fragmentation in ibdata1. This would be due to lots of transactions of varying sizes.
Another aspect would be variable length columns in a table. If the rows in your tables have TEXT fields, data could be bloat due to fragmentation from UPDATEs of such columns. I wrote about this 4.5 years ago : Different MySQL Datafile Sizes After Restoration
What you are seeing does not surprise me.
SUGGESTION
You may want to run CHECKSUM TABLE against source and target tables to verify the content is the same. If checksums do not match after reloading a mysqldump and you have TEXT or any variable columns, then you will have to mysqldump source and target and compare the data from that aspect.


I installed developer edition on centos 7(Although, it was for RHEL), by referring : https://docs.microsoft.com/en-us/sql/linux/quickstart-install-connect-red-hat?view=sql-server-2017. Didn't get any difficulty in installation. I can use it now. documentation is good.


Grouping/sorting performance choice between bigint and nvarchar

You want to store the hash using the densest datatype possible.
A bigint is 8 bytes, or 64-bits of data.
An nvarchar storing 8 bytes of data would take 10 bytes on disk.
But how long are your hashes? SHA-1 hashes are 20 bytes long (160 bits) so won't fit in a bigint. Storing it as an nvarchar will take 42 bytes of storage if stored naively.
According to https://stackoverflow.com/a/16680423/391445 you best option is to use binary(20) so you are storing exactly 20 bytes (160 bits) of data.
Are you intending to do the hashing in the database or in client code? According to the documentation, as of SQL Server 2016 all hash algorithms other than SHA2_256 and SHA2_512 are deprecated.
But a cryptographic hash function is probably not the best choice of hash function for you if all you want is a random distribution of rows. There are many hash functions available, producing whatever length key you desire!


Accessing a network share via a SQL Server 2012 Availability Group

CLS is not an alias for either CLS1 or CLS2. CLS is a cluster, not a real machine. You cannot access \\CLS\c$ simply because no such thing exists. There is only \\CLS1\C$ and \\CLS2\C$ and you can access either of them. You should figure out which one is the one you're interested and access that.


Is it possible to make an Update query right after Insert returned new Id's

OUTPUT Clause

DECLARE @MyTableVar table( NewScrapReasonID smallint,
                           Name varchar(50),
                           ModifiedDate datetime);
INSERT Production.ScrapReason (Name, ModifiedDate)
    OUTPUT INSERTED.ScrapReasonID, INSERTED.Name, INSERTED.ModifiedDate
        INTO @MyTableVar
VALUES (N'Operator error', GETDATE());

you can then use table for other inserts / updates


How can we decrypt data knowing the symmetric key used for the encryption?

I am wondering, does this mean the data is not protected at all?

No, it's protected but as shown in the article you need to have access to all of the keys in order to decrypt the values. The article takes a bunch of liberties and glosses over quite a few things when it comes to the protection of the keys. Try doing this without being a sysadmin, knowing the DMK password, or using transparent decryption hierarchy. Could you still do it - most likely not.

The secrets in SQL Server are generally protected by levels of encryption - where one key encrypts the other which encrypts the other. If you can unwind all of those levels (which again the article doesn't even really talk about and just kind of glosses over) then absolutely you can decrypt the data. SQL Server doesn't use any homemade encryption, it's all industry standard algorithms which are publically documented. There is nothing stopping anyone from implementing something to reverse it if they had all the keys. This is why over provisioning permissions is a bad thing.

  the data must be encrypted in rest and I want to check if the above behavior can give a person who has access to the database, but do not know the database master key password, the ability to encrypt the data.

In this case, no they generally wouldn't be able to get to the decrypted data. There are, however, edge cases that won't stop someone with administrative access and a decent working knowledge of encryption from possibly getting the data.

It's all about "reasonable protection" wherein you've given a reasonable enough protection to the data. If you wanted it to be extremely secure then you'd need to use an HSM, which another team owns and audits on an extremely frequent basis. No one would have administrative access to the server, and no one would have sysadmin access to SQL Server. That's probably not going to fly - so "reasonable" protection should be sufficed.


the cache hit rate using pg_statio_user_tables

The formula given only tells you what the hit rate was for hits within the PostgreSQL buffer cache (shared_buffers).

But maybe all of the misses were pulled from the OS's file cache, not actually from disk.  PostgreSQL provides no direct way for you to know that, as it keeps out of the kernels business as much as possible.  (Some other DBMS by-pass the OS's file cache, but PostgreSQL doesn't, it runs on top of it.)

One way to get a feel for it on recent versions if you run with track_io_timing turned on would be to look at

select sum(blks_read)/sum(blk_read_time) from pg_stat_database

For example, I get 110 blocks read per millisecond of read time.  Since my disk are not nearly that fast, I know that almost all of that was read from the OS file cache, not actually from disk.

If you have shared_buffers set to a small fraction of the system RAM, as is usually recommended for large systems, then you can get better-looking hit ratios by increasing shared_buffers to be a large fraction of the RAM.  Note that this is unlikely to give you better performance, it will just make some arbitrary numbers look spiffy.

In any case, hit ratios are part of "mythology-based administration".  Maybe 20% of my data is responsible for 80% of my reads.  Maybe 20% of the bloggers' data is responsible for 80% of his reads.  Neither me nor the blogger know whether that applies to your database or not.  Maybe it does, maybe is doesn't.

And even if it does, what if 20% of my data is still far larger than my available RAM?  No amount of tinkering is going to make that fit in the cache.  I would have to buy more RAM, which is expensive and involves down time, all to solve a "problem" that was not actually a problem.

If you have a performance problem, you should investigate what the problem is. If you don't have a performance problem, your time would be better spent practicing disaster recovery, or getting a test system and making a realistic load generator for it (so that you can predict what problems are likely to arise on your real system using data from the test system, rather than based on myths).  Also, if you are running a version older than 9.2, you should spend time upgrading to at least that version if not newer.  The tools available to diagnose problems once they do occur are getting better all the time, but if you are running an old version you can't use them.


Normally when I see: The error is printed in terse mode because there was error during formatting it means there isn't enough memory.

This is further correlated by:

Error 0xE which is...
14 in decimal which is...
net helpmsg 14 which is...
ERROR_OUTOFMEMORY = Not enough memory resources are available to complete this operation. which is...

  Not enough storage is available to complete this operation.

Which is what is stated in the error:

  The SQL Server (MSSQLSERVER) service terminated with the following service-specific error: Not enough storage is available to complete this operation.

Make sure enough memory exists, the page file has space, and/or the max memory setting is set high enough.

To change the memory setting, start SQL Server from the command line or edit the startup flags using SQL Server Configuration Manager. Add -f for minimal configuration startup.


I have accidently deleted ibdata1 on an ubuntu server

As you probably know, you've deleted your InnoDB data file. Your options are basically to restore from backup if you have one, or hire a data recovery company with MySQL knowledge to see what they can salvage off the disks. Some of the MySQL consulting firms like Percona and Pythian might be able to help you there, but it won't be cheap, and they might not be able to recover much. If you want to try recovering the deleted file, stopping all write activity on the filesystem that held ibdata1 or remounting it read-only as soon as possible will increase the likelihood that someone will be able to retrieve anything.


Retrieving executed query list in BigQuery via SQL

Is there a table similar to v$sql in oracle where I can retrieve data associated with a particular query that was run in BigQuery using SQL?

Yes, In BigQuery it's called Jobs and you have few ways to retrieve it:

Via the UI from the menu on the left-hand side, Query History

Via API by calling the Jobs API
Via BigQuery bq command-line using the show option as follow

bq --location=[LOCATION] show -j [JOB_ID]


Using table variable instead of temp table makes query execution slow

The key is in this part of your question:

  @temp table has 40320 records

In the execution plan, hover your mouse over the @temp table's scan. Compare the estimated number of rows versus the actual number of rows. (If you'd like to post the plan at http://PasteThePlan.com, we can give you more specific details. Disclaimer: that's my company's site.)

You're going to see that the estimated number of rows is really low.

SQL Server estimates that 1-3 rows will come back from a table variable (depending on your version of SQL Server, cardinality estimator, trace flags, etc.) This in turn gives you a really bad execution plan because SQL Server underestimates how much work it'll need from other tables, how much memory to set aside, etc.

Here are the two most popular ways to get a more accurate estimate:

Try a temp table instead (and look at estimated vs actual rows in the plan)
Use OPTION (RECOMPILE) on your query - which will get you a much more precise estimate, but with some very big drawbacks around plan cache visibility and CPU usage

To see me doing it live, watch the 1-hour Watch Brent Tune Queries (disclaimer: that's me, linking to a video of me) where I take a Stack Overflow query that uses a table variable, and tune it live in front of an audience at SQL Rally Norway.


I think the problem might be VALUES and I would write the statement as the following:

INSERT INTO `users`
    (`nUserId`, `vLoginName`, `vFirstName`, `vLastName`, `vEmail`)
SELECT
    '$entity_id', '$name', '$firstname', '$lastname', '$email'
FROM dual
WHERE NOT EXISTS (SELECT *
                    FROM `users`
                    WHERE `nUserId`='$entity_id'
                    AND   `vEmail` = '$email')


Programatically scan a stored procedures parameters

Ok, after much digging into this I have determined this is not often possible. I will detail the following scenarios that break down the ability to get the original parameters from the execution plan.

If the parameter is not used in a where clause. Scenarios:

The parameter is placed into a local variable as a way to work around parameter sniffing
The parameter is used in a calculation in a local variable

If the parameter is used in a where clause but a function is used upon it
If the parameter is used in multiple sql statements in the stored proc it will appear in multiple parameter lists.

I just wanted a list of all of the original parameters passed into the stored proc and their values from the original plan creation so that I could recreate the call if need be. I know that I could obtain this information using extended events or profiler but I was trying to extract it from the query plans.


Selection of entries that have only two words with SQL wildcards

Assuming you consider the space character as the delimiter to separate words (as per your examples) this query should do what you're looking for:
SELECT title
FROM Place
WHERE CHAR_LENGTH(SUBSTRING_INDEX(TRIM(title), ' ', 2)) = 0 -- Titles that don't have more than 1 space
    AND CHAR_LENGTH(SUBSTRING_INDEX(TRIM(title), ' ', 1)) > 0 -- And Titles that have at least 1 space (effectively this conjoined predicate results in Title's with exactly 1 space, that isn't at the start or end, aka two words)

There's probably a simpler solution, but this will remove trailing and leading spaces from the title column then filter out anything that has more than 1 space (the first predicate) and filter out anything doesn't have at least 1 space (the second predicate). Also this syntax is only for MySQL, as you requested, but you also tagged your question as PostgreSQL (so you should either remove that tag or update the question with the information you need on PostgreSQL).


Workaround to implement Minimum Degree of Parallelism

There is no such concept like minimum degree of parallelism.

SQL Server has Maximum degree of parallelism (max DOP) and cost threshold of parallelism. These 2 settings are used by sql server's cost based optimizer on the number of threads to use to run queries.

  if a machine is highly loaded such that only a single thread is free, a query that normally runs using multiple threads on an unloaded machine could be executed as single threaded.

If a machine is heavily loaded or a run away query monopolizes the server, then you will run into thread starvation where in you will exhaust the available worker threads. You should adjust the max dop setting to a sensible value and test it out.

  Is there a way to dynamically query the number of free threads available so that a particular critical query (that runs unacceptably as MaxDop < X) could be delayed (while/waitfor) for execution until a minimum free thread count is available?

Below query will help you will provide information about the system tasks that have spawned the additional threads

SELECT  s.session_id, r.command, r.status,
r.wait_type, r.scheduler_id, w.worker_address,
w.is_preemptive, w.state, t.task_state,
t.session_id, t.exec_context_id, t.request_id
FROM sys.dm_exec_sessions AS s
INNER JOIN sys.dm_exec_requests AS r
ON s.session_id = r.session_id
INNER JOIN sys.dm_os_tasks AS t
ON r.task_address = t.task_address
INNER JOIN sys.dm_os_workers AS w
ON t.worker_address = w.worker_address
WHERE s.is_user_process = 0;

Alternatively, you can check sys.dm_os_waiting_tasks for any THREADPOOL related waits. Also, sys.dm_os_scheduler will tell you the current_workers_count for each scheduler.

You can have some complex logic build in to check for current worker count and then just have a waitfor delay x where x is the delay that you want. In my opinion, it will be an overkill to unnecessarily complicate things.

The best is to tune maxdop, cost threashold of parallelism along with making sure that the stats are up-to-date and gathering the wait stats for your instance. In short, baseline your server and see if you spot any anomalies and then start your tuning exercise.


In which folder / directory does SQL 2008 store files?

Via the GUI, right click the main server node:

Programmatically, it is stored in the registry. You can see this here: http://blogs.technet.com/b/sqlman/archive/2009/07/20/tsql-script-determining-default-database-file-log-path.aspx

However, you'd typically specify paths, names, sizes etc in your CREATE DATABASE


How do I select row which has a max value in other column - PostgreSQL?

You were close, but you don't need the group by or the max(votes) window function when using distinct on ()
select distinct on (county) *
from primary_results_csv
order by county, votes desc;

The equivalent solution using window functions would be:
select county, candidate, votes
from (
   select *, max(votes) over (partition by county) as max_votes
   from primary_results_csv
) t
where votes = max_votes;

Online example


What allows SQL Server to trade an object name for a string passed to a system procedure

The first argument to the system stored procedure sp_helptext is:

  [@objname= ] 'name'
  Is the qualified or nonqualified name of a user-defined, schema-scoped object. Quotation marks are required only if a qualified object is specified. If a fully qualified name, including a database name, is provided, the database name must be the name of the current database. The object must be in the current database. name is nvarchar(776), with no default.

In addition, the documentation for Delimited Identifiers (Database Engine) states:

  Using Identifiers As Parameters in SQL Server
  Many system stored procedures, functions, and DBCC statements take object names as parameters. Some of these parameters accept multipart object names, while others accept only single-part names. Whether a single-part or multipart name is expected determines how a parameter is parsed and used internally by SQL Server.

  Single-part Parameter Names
  If the parameter is a single-part identifier, the name can be specified in the following ways:

  Without quotation marks or delimiters
  Enclosed in single quotation marks
  Enclosed in double quotation marks
  Enclosed in brackets

  Multipart Parameter Names
  Multipart names are qualified names that include the database or schema name and also the object name. When a multipart name is used as a parameter, SQL Server requires that the complete string that makes up the multipart name be enclosed in a set of single quotation marks.

The first argument to sp_helptext accepts both single-part (nonqualified) and multipart (qualified) object names.

If the T-SQL parser interprets the item after sp_helptext as a single-part name (in accordance with the four bullet points above), the resulting name is passed as the (string type) argument value expected by the procedure.

When the parser sees it as a multipart name, the text is required to be surrounded with single quotation marks as stated.

The key feature of a multipart name is a . separator (outside any delimiters).

These examples from the question are successfully interpreted as single-part names:

myproc       - single-part (without quotation marks or delimiters - bullet #1)
[myproc]     - single-part (in brackets - bullet #4)
'myproc'     - single-part (in single quotation marks - bullet #2)
'dbo.myproc' - multipart with the required single quotation marks
[dbo.myproc] - single-part (in brackets - bullet #4)

The last two examples from the question are both parsed as multipart parameter names (due to the exposed . separator). They produce an error because they lack the required enclosing single quotation marks:

dbo.myproc     - multipart without the required single quotation marks
[dbo].[myproc] - multipart without the required single quotation marks

This extra example using double quotation marks is successful:

"dbo.myproc" - single-part (in double quotation marks - bullet point #3)

Note that it is successfully interpreted (for the procedure parameter value) as being a valid single-part name, but the procedure code is able to interpret the (multipart) string it receives flexibly (using PARSENAME and OBJECTID).

As a final point of interest, note that using double quotation marks here does not depend on the setting of QUOTED_IDENTIFIER.


InfluxDB: selecting last() values from different tags

I found the solution. All you need to do is add an ORDER BY with the TAG field. So the end query becomes:
SELECT last(Temperature)
FROM raw_measure
where ID =~ /4372502|4399699|4406512|4407840/
ORDER BY ID


How to check history of primary node in an Availability Group?

Here's my go-to query to pull the events that Aaron mentioned (availability_replica_state_change).  This script also takes the liberty of converting the UTC time to your local time:

declare @xel_path varchar(1024);
declare @utc_adjustment int = datediff(hour, getutcdate(), getdate());

-------------------------------------------------------------------------------
------------------- target event_file path retrieval --------------------------
-------------------------------------------------------------------------------
;with target_data_cte as
(
    select
        target_data =
            convert(xml, target_data)
    from sys.dm_xe_sessions s
    inner join sys.dm_xe_session_targets st
    on s.address = st.event_session_address
    where s.name = 'alwayson_health'
    and st.target_name = 'event_file'
),
full_path_cte as
(
    select
        full_path =
            target_data.value('(EventFileTarget/File/@name)[1]', 'varchar(1024)')
    from target_data_cte
)
select
    @xel_path =
        left(full_path, len(full_path) - charindex('\', reverse(full_path))) +
        '\AlwaysOn_health*.xel'
from full_path_cte;

-------------------------------------------------------------------------------
------------------- replica state change events -------------------------------
-------------------------------------------------------------------------------
;with state_change_data as
(
    select
        object_name,
        event_data =
            convert(xml, event_data)
    from sys.fn_xe_file_target_read_file(@xel_path, null, null, null)
)
select
    object_name,
    event_timestamp =
        dateadd(hour, @utc_adjustment, event_data.value('(event/@timestamp)[1]', 'datetime')),
    ag_name =
        event_data.value('(event/data[@name = "availability_group_name"]/value)[1]', 'varchar(64)'),
    previous_state =
        event_data.value('(event/data[@name = "previous_state"]/text)[1]', 'varchar(64)'),
    current_state =
        event_data.value('(event/data[@name = "current_state"]/text)[1]', 'varchar(64)')
from state_change_data
where object_name = 'availability_replica_state_change'
order by event_timestamp desc;


Can you be more specific on "quite large"?  In general you're right, you shouldn't just 'do it live' in production with any change.  Do you have any kind of QA or test environment you can do a run on first?

This isn't the most sexy ninja one line approach but you could

Add a new bit column
Issue updates setting the new column = if( length(old_column)=16,1,0)
Alter table to drop old column and rename the new column


Most efficient way to cancel or avoid select in union query?

What is more efficient, using one scalar variable as in the previous example or using IF sentences and create specific queries as in the example below?

In SQL Server a query plan may not vary based on the parameters passed (unless you disable plan caching with OPTION RECOMPILE).  So whenever you want different plans, you need seperate queries.


Data guard broker cascaded standby delay parameter is ignored

If you want delay apply on Cascaded Standby you need to set the DELAY of LOG_ARCHIVE_DEST_n on Primary.

  The DELAY value that a cascaded standby uses is the value that was set for the LOG_ARCHIVE_DEST_n parameter on the primary that shipped the redo to the cascading standby.

Reference:DELAY


backup/replication over inconsistent network

Generally this is not a dba question as much as it turns into a networking issue. You need to decide what you want / what is more important to you: Having backups or having them centrally.

I generally would go for the first - make a backup locally, then move it offline for serious problems coming. This is particularly important given that you have no reliable internet - so unless you do this, you may end up not having backup done at all because internet is bad.

Now, for packet loss - there is not a lot you can do and it kills data transfers. It also generally is a sign of a very bad internet connection. Wimax generally is not exactly an excuse here as much as it is a symptom - a not really widely used technology with better alternatives existing. You can use a relatively cheap point to point wlan link over many km with much higher speed and - very little packet loss. I am writing this on the receiving end of a 4km WLAN based internet connection and my packet loss on most days (when it is not storming or we have lightning) is neglegible. TO fix the packet loss you must address that from a business point - talking with the ISP and make him maintain his equipment properly. High packet loss WILL kill large transfers with not a lot you can do. It also is the main issue here and - totally not a dba related question, as it affects any kind of data transfer.

And again: Given extended network outages that seem to be normal, I would go for a local backup that then gets copied out. Because this means if your internet is down for 3 days, your backups still happen.


Designing User & its Sub User Database Diagram Problem

Why not just get rid of the main_user and sub_user tables and have a nullable field on the users table called parent_user_id. Then you can use a self-join on users.parent_user_id = users.user_id to get all the sub-users for a given Travel Agency. (When the parent_user_id field is null, that's a Travel Agency.)


What does the vertical pipe slash operator do?

The |/ operator calculates the square root

postgres>  select |/16,  sqrt(16);
 ?column? | sqrt
----------+------
        4 |    4
(1 row)

So |/125.555/pi() is equivalent to sqrt(125.555/pi())


I had to re-read your question a few times to get the sense of what you are trying to do.  What I suggest is something I call a class-based multi-multi join.  In some db's like PostgreSQL, you can then do some advanced referential integrity checks on the joins, but that's outside my answer.

suppose you have a table:

CREATE TABLE correspondence_class (
     id int not null unique, -- hand-assigned
     label text primary key, -- hand-assigned, don't want duplicate labels
);

Then we can:

INSERT INTO correspondence_class (id, label)
VALUES ('1', 'person'), (2, 'color'), (3, 'season'), -- and so forth

From there we can then have two more tables:

CREATE TABLE trigram_assoc (
   trigram_id int references trigram(id),
   c_class_id int references correspondence_class(id),
   correspondence text not null,
   PRIMARY KEY (trigram_id, c_class_id)
 );

you would then do the same for hexagrams, lines, etc.  In PostgreSQL, you could change your primary key to include correspondence too, and uniquely index only those correspondences attached to some correspondence classes.  You could also have deferrable constraint triggers that could be used to ensure that all relevant associations were populated, but how exactly to do this on other dbs is a good question.


TempDB size issue after applying Indexes

Even though you have 22 GB of free space in tempdb now, that doesn't mean this space wasn't used before.  Something caused autogrowth to kick in, and once that process ends, there isn't anything to shrink tempdb back to its current size (and I'm not even going to talk about autoshrink...).

It sounds like the thing complaining about running out of space was the drive (or maybe it hit a hard limit that was set in SQL Server--I can't quite tell from your wording, but I'm guessing the drive).  It recognizes that you have a 38 GB tempdb file, but the Windows API has no clue about whether disk space allocated to an MDF or LDF file is "free space" or not; it's all considered in use by Windows, so it all counts.

The best way to prevent this problem happening is to figure out what caused your tempdb to grow and size appropriately.  If it's a one-time event (e.g., creating indexes with sort_in_tempdb on), tempdb will go back to its default size the next time you have to restart SQL Server.  But if it's as a result of a regular job or a change in queries that have caused significantly more tempdb usage (e.g., a marked increase in hash join spillovers), you would need to take this as your new high water mark and try to get some more disk space.

To help figure out which one it is, you can start with Mark Storey-Smith's answer to a previous question of similar nature.


Finding the median of column values per row

First step is to unpivot columns into rows. Then you can enumerate the values using row_number and pick the middlest value from there:

select id, avg(val)
from (
    select id, val
         , count(*) over (partition by id) as c
         , row_number() over (partition by id order by val) as rn
    from temp unpivot (
             val for col in (a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u)
         ) as x
) as y
where rn IN ((c + 1)/2, (c + 2)/2)
group by id;


Restoring from pg_dump with foreign key constraints

You can put SET session_replication_role = replica;at the top of your SQL file. This will ignore constraints during data insertion As the setting is session-based, constraints will continue to work outside this script.

But beware: if you create inconsistent data while this setting is active, postgres will keep them. Constraints are only ever checked at insert/update time.

See https://www.endpoint.com/blog/2015/01/28/postgres-sessionreplication-role for a deeper discussion of the pros and cons of this approach.


Postgresql Union: Do not repeat based on 1 column only

full outer join is very handy for this as follows:
select id, case when users.id is null then 2 else 1 end as category
from users
full outer join users_2 using (id);

How this works is that the id column will be null if the row comes from users while it won't be null when it's coming from users_2.
Alternatively you can do it with union (and use union all because we're already using not exists to discard duplicates):
select id, 1 as category
from users
union all
select id, 2
from users_2 where not exists (select id from users where id = users_2.id);

but as you can see the query becomes much longer.
A third way that is even longer and probably even less efficient is to use distinct on:
select distinct on (id) id, category
from (
  select id, 1 as category
  from users
  union all
  select id, 2
  from users_2
) x
order by id, category;

NB This last query does guarantee the order in the output; the first two queries don't!
See https://dbfiddle.uk/?rdbms=postgres_13&fiddle=dd8bdbc82edc26cce9e8ba9fdde84517


How can I generate the code-containing comments like mysqldump?

Well you can review what mysqldump does on git and try working on it!! Though I'd not go this way...

Few things to suggest:

You want to hide column names

--> use --no-create-info to dump only data. (onlydata.sql)

--> Have standard table definitions ready and intact for dev server. (definition.sql)

--> Refreshing dev => load definition.sql and then onlydata.sql

Just to note that you can also restrict datadump with --where clauses and even limit that to dump only a few rows.

mysqldump --no-create-info --where " true limit 10000" | gzip > dump.sql.gz

.

You want to remove few values from the dump? Well writing tool for searching strings will again cause long time to parse!

--> You might consider setting up a special slave on which you make your changes and then do a full datadump.

--> You can prepare SQL commands to run after dump load finishes as follows:
update secrettable set secretcolumn='xxxxxx' where secretcolumn='reallysecretthing';


How to generate current Oracle timestamp in perl?

Something like this will do:

my ($second, $minute, $hour, $day, $month, $year, $weekday, $dayofyear, $dst) = localtime();

my $oracledatestring = sprintf "TO_DATE('%04d/%02d/%02d %02d:%02d:%02d', 'yyyymmdd HH24:MI:SS')", $year+1900, $month+1, $day, $hour, $minute, $second;

.. giving something like:

TO_DATE('2014/04/28 16:34:34', 'yyyymmdd HH24:MI:SS')

... which can then be inserted directly into an Oracle DATE column.

All a bit of a long way round, to be honest. Use SYSDATE in your INSERT statements instead.


Db2 REORGCHK returns the SQL2310N (Error "-668") message

I'm not sure REORGCHK UPDATE STATISTICS is the way to go after a destructive operation (drop column) against a table. I suspect that the drop statement put a Z-lock on some catalog tables which prevents STATISTICS from being updated. If you are only interested in whether you need to reorg a table due to the drop you can:

SELECT NUM_REORG_REC_ALTERS, REORG_PENDING
FROM SYSIBMADM.ADMINTABINFO
WHERE (TABSCHEMA, TABNAME) = (..., ...)

For some reason, it's really slow (don't ask for a table that does not exist), but it's faster than reorgchk.

Unless the table is really big, I usually add a REORG command to migration scripts just in case:

REORG TABLE ...;

Do note that REORG will commit any ongoing transactions, so you should end the current uow, before REORG's and put them in a separate transaction.


Mysql Master-Slave replication catchup and bin_logs

1) Any INSERT, UPDATE, ALTER etc type Query executed on the Master will be written to the Binary log, causing the MASTER_LOG_POS to increase

Furthermore, if the Master is receiving updates from another Master (in a Master to Master setup), AND if you have LOG_SLAVE_UPDATES enabled these will be written to the Binary Log as well.

Each time you restart the service, the Binary Log will be flushed, incrementing it by 1.

2) On the Slave, Run SHOW SLAVE STATUS, and make a note of the MASTER_LOG_FILE. Now check, does this file exist on the Master still? If it doesn't then you have a problem, and will need to rebuild the slave from a copy of the master (e.g. MYSQLDUMP)

Assuming it does exist you should be able to just issue a START SLAVE command, and it will start catching up (hopefully)

Next run SHOW SLAVE STATUS again and check the SLAVE_IO_RUNNING and SLAVE_SQL_RUNNING. Are they both YES then all is good. Look for the SECONDS_BEHIND_MASTER to give you an idea of how far behind it is.

If either of them is NO look at the Error (LAST_IO_ERROR or LAST_SQL_ERROR) to get an idea of what the problem is.


When I do transactional replication does SQL truncate T-Logs on the Publisher?

Can I still capture T-Logs from Production and restore them (with a full backup of course) if I have a catastrophic failure on Prod?

Yes. Just make sure that the log chain is not broken by adhoc log backups. You can use sp_restoregene (tsql based) or ps_restoregene (PowerShell based) to automate when disaster happens.

  Do the T-Logs get truncated each time the subscriber pulls the transaction from the Publisher (Prod) and applies it to the Subscriber?

Once the transaction is replicated to subscriber, the log file can be truncated.

You can review the log_reuse_wait and log_reuse_wait_desc columns of the sys.databases catalog view to determine why the transaction log space is not reused and why the transaction log cannot be truncated.  See this KB 317375 for more details.

Check my answer for - Replicated Database Log File Maintenance. Also, Kendra Little wrote an excellent blog post on : You Can’t Kill Transactional Replication


Making a few assumptions, here is a query that would give you the desired results in Oracle:

SELECT EmpCode, Name
   , Max(UG_Qualification) UG_Qualification
   , Max(UG_CompletedYear) UG_CompletedYear
   , Max(PG_Qualification) PG_Qualification
   , Max(PG_CompletedYear) PG_CompletedYear
FROM
(
   SELECT EmpCode, Name
      , Case When Qualification='B.E' Then 'B.E'
         Else Null End UG_Qualification
      , Case When Qualification='M.E' Then 'M.E'
         When Qualification='MS' Then 'MS'
         Else Null End PG_Qualification
      , Case When Qualification='B.E' Then CompletedYear
         Else Null End UG_CompletedYear
      , Case When Qualification='M.E' Then CompletedYear
         When Qualification='MS' Then CompletedYear
         Else Null End PG_CompletedYear
   FROM e1
)
GROUP BY EmpCode, Name;

Results:

   EMPCODE NAME       UG_ UG_C PG_ PG_C
---------- ---------- --- ---- --- ----
  10000001 Ram        B.E 2005 M.E 2007
  10000002 Sita       B.E 2003 MS  2005

Setup:

drop table e1;
create table e1 (EmpCode Number(8), Name Varchar2(10)
   , Qualification Varchar2(5), CompletedYear Varchar2(4));
insert all
   into e1 values (10000001,'Ram','B.E','2005')
   into e1 values (10000001,'Ram','M.E','2007')
   into e1 values (10000002,'Sita','B.E','2003')
   into e1 values (10000002,'Sita','MS','2005')
   into e1 values (10000002,'Sita','Ph.D','2010')
   select * from dual;


How to detect level in nested MySQL table?

in my opinion structure not normalised, but if need resolve it as is

SELECT
t1.id, t1.name,
CASE WHEN parent_id = 0 THEN
       1
ELSE
    (SELECT count(id)+1 from Table t2 WHERE t2.parent_id = t1.parent_id and t2.id < t1.id)
END AS seq_no
    FROM Table t1

there are many modifications possible, depending from what finally You need, such as if need select seq_no for single name


As mentioned in the comment under the question, an answer to this question is included this post: http://blogs.lobsterpot.com.au/2011/02/17/the-ssis-tuning-tip-that-everyone-misses/

That is, that batching is done by default (10k rows per batch).


PostgreSQL - Grant privileges as superuser

ALTER DEFAULT PRIVILEGES without a FOR ROLE clause alters the default privileges for objects created by only the current role (the role executing the ALTER DEFAULT PRIVILEGES).  So in the first case, you altered the superuser's default privileges, which has no effect on testuser's default privileges.


Slow query performance due to temporary file?

work_mem
Obviously, the sort operation spills to disk:

Sort Method: external merge  Disk: 36224kB

More work_mem can help the query, like @Kassandry already suggested. Increase the setting until you see Memory instead of Disk in the EXPLAIN output. But it's probably a bad idea to increase the general setting based on one query. Proper setting depends on available RAM and your complete situation. Start by reading in the Postgres Wiki.
To just fix your query, set work_mem high enough for your transaction only with SET LOCAL in the same transaction.
BEGIN;

SET LOCAL work_mem = '45MB';

SELECT ...

COMMIT;   -- or ROLLBACK

You probably need a bit more than 40 MB. In-memory representation is a bit bigger than on-disk representation. Related:

Configuration parameter work_mem in PostgreSQL on Linux

Query
Your query (after trimming some noise):
SELECT *
FROM   products
WHERE  status > 100
AND    above_revenue_average  -- boolean can be used directly
AND    category_id <> 5
ORDER  BY start_date DESC;

Your rows are half a kilobyte wide (width=524). Do you need to return all columns? (Typically, you don't.) Only list columns in the SELECT list which you need from your query to improve overall performance, especially since you have work_mem issues already.
Can any of the involved columns be NULL? Particularly important for category_id and start_date. You might want to adapt in that case ...
Index
A multicolumn index can certainly help performance. (Like @Paul outlined). You have to weigh cost and gain. If performance for this query is important or it is very common, go for it. Don't create a special index for every query. As few as possible, as many as necessary. Indexes are more powerful when shared, that increases chances that more of them stay in cache.
A boolean column like above_revenue_average is a typical candidate for a condition in a partial index rather than for an index column.

Unexpected Seq Scan when doing query against boolean with value NULL

My wild guess based on incomplete information:
CREATE INDEX prod_special_idx ON products (start_date DESC)
WHERE  above_revenue_average
AND    status > 100
AND    category_id <> 5;

Use DESC NULLS LAST in index and query if start_date can be NULL.

PostgreSQL sort by datetime asc, null first?


SSMA for Access - Migration Assistant Wizard fails at link tables

I was able to work around the problem by doing the following:

Allow the migration assistant to complete (with link converted tables failing)
Manually add MySQL linked tables to Access

Open Access, select External Data->New Data Source->From Other Sources->ODBC Database
Select Link to the data source by creating a linked table
Select your ODBC driver file or source
Enter credentials
Select tables you want to link

Delete Access tables and rename MySQL tables

From Tables list, right click first Access table and delete
Right-click corresponding MySQL table and rename to exactly match deleted Access table
Repeat for every table you want to re-map

Provided that you rename each MySQL table to match the deleted Access tables, all queries should work.


How to stop MySQL recreating SSL files on restart

From the documentation page you linked to:

  This variable is available if the server was compiled using OpenSSL

It sounds like you're using the standard MySQL community edition binary distribution, which is not compiled with OpenSSL, and this is why you're getting the error message when you try to start up with auto_generate_certs=OFF.

See OpenSSL vs yaSSL for details.

See also documentation for the ssl_capath variable, which is what I assume you looked at to deduce that MySQL was looking for your certificate files in /etc/mysql/ssl:

  MySQL distributions compiled using OpenSSL support the --ssl-capath option (see Section 6.4.4, “OpenSSL Versus yaSSL”). Distributions compiled using yaSSL do not because yaSSL does not look in any directory and does not follow a chained certificate tree. yaSSL requires that all components of the CA certificate tree be contained within a single CA certificate tree and that each certificate in the file has a unique SubjectName value. To work around this yaSSL limitation, concatenate the individual certificate files comprising the certificate tree into a new file and specify that file as the value of the --ssl-ca option.


Trigger function error: Record "old" not yet assigned

In the case of an INSERT, there is no previous version of the row, so the OLD variable is unassigned.

See the documentation:

  OLD

     Data type RECORD; variable holding the old database row for UPDATE/DELETE operations in row-level triggers. This variable is null in statement-level triggers and for INSERT operations.


query regarding combining an update and an insert query into a single query in mysql

To do this without the risk of blocking another user trying to update the same profile at the same time, you need to lock the row in t1 first, then use a transaction (as Rolando points out in the comments to your question) :

start transaction;
select id from t1 where id=10 for update;
insert into t2 select * from t1 where id=10;
update t1 set id = 11 where id=10;
commit;


Design for m-to-n relation with unidirectional attribute(s)

I could e.g. add a column to the m-to-n mapping table. Is this a valid solution?

Yes, this solution desrcibes best your scenario. A table with (StackBox, Item, Position) and a unique constraint on (StackBox, Position) (meaning only one item in each place/position) and probably another unique constraint on (StackBox, Item) (unless you can have an item twice or more in a stackbox).

The table would be like (I assume the other two tables have primary keys StackBoxID and ItemID and are INT, adjust accordingly to your design):

CREATE TABLE StackBoxItem
( StackBoxID INT NOT NULL,
  ItemID INT NOT NULL,
  Position INT NOT NULL,
  PRIMARY KEY (StackBoxID, Position),
  UNIQUE (StackBoxID, ItemID),
  FOREIGN KEY (StackBoxID)
    REFERENCES StackBox (StackBoxID),
  FOREIGN KEY (ItemID)
    REFERENCES Item (ItemID)
) ;


How I create backup with backup by dump and WAL files in PostgreSQL?

You cannot restore a pg_dump (via psql or pg_restore) then update it with WAL archives. It just isn't possible, sorry.

WAL archives are block-level records of changes to individual relation files. They only make sense when applied to an identical filesystem-level copy of the original database as created from a pg_basebackup, via rsync, etc.

If you try to apply WAL archives to a DB that's restored from a pg_dump they'll be rejected because as far as PostgreSQL is concerned they're different, unrelated databases. At the block/filesystem level they are; the blocks are different, the relation IDs are different, etc. The WAL archives make no sense when applied to this database, even though at the logical level the row data looks the same.

You must use a pg_basebackup copy to restore WAL, and it only works if you have a complete and uninterrupted sequence of WAL from the moment the base backup was taken until the final segment.


Postgres: foreign key constraint for components of a composite type?

As I pointed out in comments, you could add a new composite column in main table:

CREATE TYPE abtype AS (
    a INTEGER,
    b INTEGER
);

CREATE TABLE main (
    -- a and b are separate fields
    a INTEGER,
    b INTEGER,
    ab abtype generated always as ((a,b)) stored,
    PRIMARY KEY (a,b)
);

CREATE TABLE sub (
    -- a and b are components of a composite type
    ab abtype,
    PRIMARY KEY (ab)
);

And set a unique index:

create unique index on main (ab);

Now you can reference your FK to this column:

ALTER TABLE sub ADD CONSTRAINT sub_ab_fkey FOREIGN KEY (ab) references main(ab);

db<>fiddle here


SQL 2016/SQL 2017 CE Performance on Information_Schema inferior to SQL 2014 out of the box

Here is an excellent article that discusses all the aspects of the new cardinality estimator.
Bottom line - as with all changes, you win some, you lose some, and hopefully overall you win more than you lose.
With the new cardinality estimator, it is a huge win overall, in spite of the occasional hickups like you are experiencing.
You have several work arounds other than the TF, and they are discussed in the article. Easiest should be either using a query hint, not to lose the benefits of the new estimator.

USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION')


What is a Compressed Prefix B+-Tree (CPBTree) in SAP HANA?

I'll assume you know what a B-Tree is. In a B+-Tree the leaf nodes contain all the column of the rows. A compressed B-Tree uses some form of compression to fit more information on each page. Compression is desirable because it maximizes the amount of information passed for each data movement. In a disk-based system the movements are between disk and RAM. For HANA the transfers are between RAM and CPU cache; memory stalls and cache flushes are significant in this architecture.
One possible compression is prefix compression. Here index key values are compared. When a common set of leading characters (a prefix) are identified they are removed from the key and stored separately. At run-time the prefix is implicitly re-attached to the remainder.
For example, say we have an index on the names of people from the Doomsday Book.
William of Braose
William of Ecouis
William of Falaise
William of Keynes
William of Poilley

To store these as-is requires 88 bytes at least. We note that the first 11 characters of each key is identical. This can be extracted as the prefix meaning only the unique part consumes space:
"William of "..
Braose
Ecouis
Falaise
Keynes
Poilley

This needs 44 characters - a 50% reduction in IO, cache miss etc.
This is likely to be effective because a B-Tree is an ordered list of keys. Keys which sort together are likely to be physically adjacent. Further, for large datasets the keys must be long since they have to distinguish between many rows. This means there will be many keys where the most significant (i.e. left-most) bytes are identical. (For small datasets without shared prefixes who cares! They're small and will be processed quickly whatever.)
The same applies to numeric values. Imagine a table whose primary key is a system-assigned incremental integer. After one hundred million rows have been added, the next rows will have values
100,000,001
100,000,002
100,000,003
...

The prefix can be the 4-byte integer "100,000,000" and the unique part held in a single byte each. For numbers this is also known as delta encoding.

I haven't watched it but one would imagine this would be relevant since it's presented by Dr. Plattner, The "P" of SAP.
https://open.hpi.de/courses/imdb2017/items/3S9yMQPPAjYHzgYwFd2DMF


Enforce functional dependencies by using triggers in SQL99

An instead of trigger is exactly that; it won't perform an insert even if data satisfy the constraint, so it won't be appropriate.

I understand that yours is a contrived example and that in real life you'll use a unique constraint to enforce such a rule, but the exists predicate nevertheless might be more efficient:

BEGIN
  IF EXISTS (
    SELECT 1 FROM R
    WHERE R.A = N.A AND R.B = N.B AND R. C <> N.C;
  )
    THEN raise_exception ('AB->C on R was violated');
END;


Data Factory keeps getting "untrusted connection" error when logging into on prem SQL Server via IR on VM

OK, I fixed this by completely uninstalling then re-installing the Integration Runtime on the VM we are using as a gateway box.  This indicates that it can become corrupt, but the corruption is not visible to the user. We could still use the gateway for testing connections, but not for data transfer.  A careful look through of the logs didn't give any clue to the error that had caused the corruption.
The version I reinstalled was exactly the same as what was there previously, so it didn't appear to be an issue with the version (5.2.7695.1).


SQL Server xp_cmdshell: account's access changed - how to avoid restarting the service?

Depending on how secure you want to make it (Not dwelling on it as you've requested), a proxy account could be setup for xp_cmdshell operations so that it does has local admin.
xp_cmdshell_proxy
Since you're interacting with the OS at this point, you could also create scripts to switch execution context to another user either through powershell or through the normal command line.
Obviously this all depends on who has access to SQL Server and Agent, and if it would be an issue. Only you can answer that.


You can't say "UNION ALL ONLY IF SOME CONDITION" but you could do:

IF EXISTS (SELECT 1 FROM dbo.FOO)
BEGIN
  SELECT Column1, Column2, ColumnN FROM dbo.FOO;
END
ELSE
BEGIN
  SELECT Column1, Column2, Column3 FROM dbo.FOO_ARCHIVE;
END


SQLQueryStress is a great tool for small scale concurrency testing but it isn't really up to the job of load testing. A surprisingly little known toolset is available for free, from Microsoft, that can cope with most SQL Server stress testing scenarios, the RML Utilities.

A very brief outline of the tools:

ReadTrace converts Profiler traces from .trc files to .rml (Replay Markup Language).
OStress is used to replay .rml and .sql files against a server.
ORCA (OStress Replay Control Agent) co-ordinates replay across distributed instances of OStress.
Reporter provides performance analysis reports.

There is an excellent fast start article from the SQLCat team which includes a sample database and workloads, Precision Performance for Microsoft SQL Server using RML Utilities 9.0

You have the option of either Profiling activity for replay or, possibly more appropriate in your scenario, hand cranking a set of .sql scripts that you then replay via OStress and ORCA.

Great tools for both load testing and investigating performance issues.


Does anyone have a good recommendation for a book which teaches sharding?

A great place to start is the book High Performance MySQL : Optimizations, Backups, Replication and More. Chapter 9 (pages 409 - 456) of the book is entitled "Scaling and High Availability"

There are the subheadings

Scaling MySQL
Planning for Scability
Buying Time Before Scaling
Scaling Up
Scaling Out

Functional partitioning
Data Sharding
Choosing a partitioning key
Multiple partitioning keys
Querying across shards
Allocating data, shards and node
Arranging shards on node
Fixed allocation
Mixing Dynamic and Fixed Allocation
Explicit Allocation
Rebalancing Shards
Generating globally unique IDS
Tools for sharding

Scaling Back
Scaling by Cluster
Load Balancing
Connecting Directly

and two dozen other subheadings