Perform mysql limited select that wraps when it reaches the end of table

I've set a rextester example whith this sample data:

drop table if exists promotions;
create table if not exists promotions (promo_id int, expiry_date timestamp);
insert into promotions values
(1, '20170101'),(2, '20170101'),(3, '20170101'),(4, '20170101'),(5, '20170101');

IMHO you should add two additional records and then limit final result to two rows. Use a UNION with your query and an addition query that simply returns the two first rows of promotions table.

Note: Add parentheses to both queries or you'll get an error.

(select  promo_id
from     promotions
where    promo_id > 4
order by promo_id
limit    2)
union
(select  promo_id
from     promotions
order by promo_id
limit    2)
limit 2
;

The final result:

promo_id  |expiry_date
----------|--------------------
   5      | 01.01.2017 00:00:00
   1      | 01.01.2017 00:00:00

Using you query

(SELECT `promo_id`
 FROM `promotions`
 WHERE (`expiry_date` >= '2017-03-21' AND `promo_id` > (SELECT `promo_id`  FROM `promoted_questions` WHERE `promo_id` = 8))
 ORDER BY `promo_id` ASC
 LIMIT 2
)
UNION ALL
(SELECT `promo_id`
 FROM `promoted_questions`
 ORDER BY `promo_id` ASC
 LIMIT 2
)
LIMIT 2;


SQL Server Threads and Degree Of Parallelism

My original understanding is (likely incorrect) a scheduler may (when there is a need) use multiple workers/threads from the same NUMA node to process different tasks of the same execution request when it is a serial execution.

A serial plan will not use multiple threads it will only use single thread to perform the task. When the serial query would start it would be bound to a scheduler and that scheduler will not perform any other task unless it completes this query. The scheduler may wait for a process to complete thus forcing query to wait.

It is also worthy noting that MAXDOP setting controls the amount of thread used for parallel processing and MAXDOP is always specified per operator in the execution plan and not per execution plan

I strongly suggest you read below article by Paul White

Understanding and Using Parallelism in SQL Server


How do you count the occurrences of an anchored string using PostgreSQL?

You can solve this with a

FASTEST was the pl/perl method which I placed last on this list because it requires pl/perl, and is likely not needed for most workloads.
FAST A string function, such as one the pattern explained below

length(str) - length(*replace(str, replaceStr))
  / length(replaceStr)

Something that converts from a string to an array.
SLOW Something that converts from a string to a table.

Possible solutions

String

Using length and regexp_replace

Most RDBMS's provide some method to calculate substring occurrences like this,

SELECT length(data) - length(replace(data, '2', ''))
  / length('2')
FROM foo;

This method isn't applicable here because without an anchor, we can't be sure if we're replacing the substring inside of something-space-delimited. As an example, the above replaces the 2 in 329. We can remedy that by using regexp_replace to anchor the substring.

SELECT length(data) - length(regexp_replace(data, '\m2\M', '', 'g'))
  / length('2')
FROM foo;

Because we're not splitting on simple spaces (' ') though we could with more complexity, we may also want to accommodate sub-strings of different lengths like in this question. This is why we explicitly include / length('2'). That reduces to a no-op, but if we we're search for something that was longer than one character, it'd be required.

SELECT length(data) - length(regexp_replace(data, '\m42\M', '', 'g'))
  / length('42')
FROM foo;

Using ARRAY[]

Splitting into an ARRAY[]

Here we have to subtract one match splits a string into two fragments, and thus the occurrences is one less than the fragment counts: this xyx split on y, produces {'x', 'x'} and we want the length to be 1 corresponding to the occurrences of y.

SELECT array_length(x, 1) - 1
FROM foo
CROSS JOIN LATERAL regexp_split_to_array(data, '\m2\M') AS t(x);
-- un-anchored version for reference.
-- CROSS JOIN LATERAL string_to_array(data, '2') AS t(x);

Alternatively, we can use string_to_array to separate something that's space-delimited and then count the matches,

SELECT id, array_length(array_positions(x, '2'), 1)
FROM foo
CROSS JOIN LATERAL string_to_array(data, ' ') AS t(x);

Using a TABLE

Splitting into a table with regexp_split_to_table

Here we split the regex into a table.. In this method you're using GROUP BY and count().

SELECT id, x
FROM foo
CROSS JOIN LATERAL regexp_split_to_table(data, ' ')
  AS t(x);

 id | x
----+---
  1 | 1
  1 | 2
  1 | 2
  1 | 2
  1 | 2
  1 | 2
  1 | 2
(7 rows)

And, from there you can run regular SQL on it.

SELECT id, x, count(*)
FROM (
    SELECT id, x
    FROM foo
    CROSS JOIN LATERAL regexp_split_to_table(data, ' ')
      AS t(x)
) AS t(id,x)
GROUP BY id, x;

 id | x | count
----+---+-------
  1 | 1 |     1
  1 | 2 |     6

Using regex_matches

Here we get away from splitting and instead use the \m, and \M anchors for word-boundaries.

SELECT count(*)
FROM foo
CROSS JOIN LATERAL regexp_matches(data, '\m2\M', 'g');

Procedural Languages

Perl

This method turned out to the be the fastest overall,

CREATE LANGUAGE plperl

CREATE FUNCTION count_occurances(inputStr text, regex text)
RETURNS smallint
AS $BODY$
  scalar @{[ $_[0] =~ m/$_[1]/g ]}
$BODY$
LANGUAGE plperl
IMMUTABLE;

Summary and Performance Implications

Following the same format of data, performance implications can be obtained with

CREATE TABLE foo
AS
  SELECT
    1 AS id,
    array_to_string(
      ARRAY(SELECT trunc(random()*100+1)::int % 100 FROM generate_series(1,5000) AS t(x)),
      ' '
    ) AS data
;

Under these constraints, I found that the procedural method with plperl was the fastest. Next I found the following as being the fastest native method,

length(str) - regexp_replace(str, replacement, g)
  / length(replacement)

Keep in mind the tried and true method of string replacement outside of needing to anchor the string is still the fastest and most efficient native method however clunky it may be,

length(str) - replace(str, replacement)
  / length(replacement)

That said, the ARRAY[] method is massively faster than splitting to a table.


You don't strictly need a newer version of SQL Server; however as a general thing I always prefer to be using the most recent version (especially in this case, because in SQL Server 2016 SP1, you get all kinds of new features in Express Edition).

You do need to apply one of the following updates, though (I go through more details in this blog post). They are listed in my order of preference:

Service Pack 2 (+ SP2 CU#3, ideally)
Service Pack 1 + SP1 GDR (minimum) (+ SP1 CU#10, ideally)
RTM CU#14
RTM GDR update - technically this is all that should be required, but you are missing out on so many other fixes from over two years of updates, this is "the absolute, I don't recommend it, bare minimum" IMHO

That's the gist of it; if you want to get into all the nitty gritty, here is a KB article I'm stealing from Max's deleted post.


Postgres: how to limit the number of query results by aggregate type

WITH
cte AS ( SELECT resource_id,
                target_id,
                relation_id,
                ROW_NUMBER() OVER ( PARTITION BY resource_id, relation_id
                                    ORDER BY id ) rn
         FROM associations
         WHERE relation_id IN (4, 5, 8) )
SELECT resource_id, STRING_AGG(target_id::TEXT, ','::TEXT), relation_id
FROM cte
WHERE rn <= 3
GROUP BY resource_id, relation_id

fiddle


Justin Cave's answer here and Tom Kyte's pointed me to a solution using a function based index. I think this can be made even simpler with some more thought but this works now:
CREATE OR REPLACE FUNCTION UNIQUE_START_STAGE (
   phase_id_in   IN NUMBER,
   stage_id_in   IN NUMBER)
   RETURN NUMBER
   DETERMINISTIC
IS
 -- PURPOSE:enforce business logic that a phase can have only one stage where
 -- the disabled field has a value of 0 and IS_START_STAGE has a value of 1

   v_count   NUMBER (9);
BEGIN
   SELECT COUNT (s.id)
     INTO v_count
     FROM STAGE s
    WHERE     S.IS_START_STAGE = 1
          AND s.disabled = 0
          AND S.PHASE_ID = phase_id_in;

 IF v_count = 1
 THEN
     --return the primary key if there is only one
     v_count := stage_id_in;
  ELSIF v_count < 1
  THEN
     v_count := NULL;
  END IF;

   RETURN v_count;
END UNIQUE_START_STAGE;

and then we create an index based the idea that there can only be one child stage that is enabled for a phase that is the start stage
CREATE UNIQUE INDEX unique_start_stage_idx
   ON stage (
      CASE
         WHEN disabled = 1 THEN NULL
         WHEN is_start_stage = 0 THEN NULL
         ELSE UNIQUE_START_STAGE (phase_id, id)
      END);

--and add the same constraint to the other table

CREATE UNIQUE INDEX unique_start_stage_idx2
ON PHASE_FIRST_STAGE (
UNIQUE_START_STAGE (phase_id, stage_id));
This solution partially solves the problem:

it enforces that there is only one entry in STAGE for each value of PHASE_ID where IS_START_STAGE =1 and DISABLED = 0
it enforces this same uniqueness in PHASE_FIRST_STAGE
it does not enforce that an entry in STAGE is also in PHASE_FIRST_STAGE
you could replace the PHASE_FIRST_STAGE table with a view of STAGE that cleans up the last issue


Neither. Try:
SELECT I.ItemName
FROM Items I
WHERE I.LocationID = 1

...and add a covering index on LocationID with INCLUDE column ItemName  .


Aggregate a JSON doing an average on values without defining keys

I don't know how to do this, so I'll learn myself as we go!

Test table and data:

postgres=# CREATE TABLE testtable (
postgres(#     dt date,
postgres(#     data jsonb
postgres(# );
CREATE TABLE
postgres=#
postgres=# INSERT INTO testtable
postgres-# VALUES
postgres-# ('2018-05-06 18:17:00','{"speed":23.3,"orientation":1.3,"o2":75.2,"pm25":12.1}'),
postgres-# ('2018-05-06 19:17:00','{"speed":20.3,"pm25":13.1}'),
postgres-# ('2018-05-07 15:02:00','{"speed":21.3,"orientation":1.3,"pm10":72.2}');
INSERT 0 3
postgres=#

First, we don't need the time component of the data, so we cast:

postgres=# select dt::date from testtable ;
     dt
------------
 2018-05-06
 2018-05-06
 2018-05-07
(3 rows)

postgres=#

Next, we want to decompose the JSON object to its individual elements:

postgres=# SELECT dt::date, jsonb_each_text(data)
FROM testtable;
     dt     |  jsonb_each_text
------------+-------------------
 2018-05-06 | (o2,75.2)
 2018-05-06 | (pm25,12.1)
 2018-05-06 | (speed,23.3)
 2018-05-06 | (orientation,1.3)
 2018-05-06 | (pm25,13.1)
 2018-05-06 | (speed,20.3)
 2018-05-07 | (pm10,72.2)
 2018-05-07 | (speed,21.3)
 2018-05-07 | (orientation,1.3)
(9 rows)

postgres=#

Now split out the key<>value pairs from the above:

postgres=# SELECT dt::date, jsondata.key, jsondata.value
postgres-# FROM testtable, jsonb_each_text(data) as jsondata;
     dt     |     key     | value
------------+-------------+-------
 2018-05-06 | o2          | 75.2
 2018-05-06 | pm25        | 12.1
 2018-05-06 | speed       | 23.3
 2018-05-06 | orientation | 1.3
 2018-05-06 | pm25        | 13.1
 2018-05-06 | speed       | 20.3
 2018-05-07 | pm10        | 72.2
 2018-05-07 | speed       | 21.3
 2018-05-07 | orientation | 1.3
(9 rows)

postgres=#

Aggregate the above data:

postgres=# SELECT dt::date, jsondata.key, sum(jsondata.value::float) as total
postgres-# FROM testtable, jsonb_each_text(data) as jsondata
postgres-# Group by dt::date, jsondata.key;
     dt     |     key     | total
------------+-------------+-------
 2018-05-06 | o2          |  75.2
 2018-05-06 | speed       |  43.6
 2018-05-07 | orientation |   1.3
 2018-05-07 | speed       |  21.3
 2018-05-06 | orientation |   1.3
 2018-05-07 | pm10        |  72.2
 2018-05-06 | pm25        |  25.2
(7 rows)

postgres=#

Now turn the above resultset into JSON:

postgres=# With sourcedata as (
postgres(# SELECT dt::date as dt, jsondata.key as key, sum(jsondata.value::float) as total
postgres(# FROM testtable, jsonb_each_text(data) as jsondata
postgres(# Group by dt::date, jsondata.key
postgres(# )
postgres-# Select dt, jsonb_object_agg(key, total)
postgres-# From sourcedata
postgres-# Group by dt;
     dt     |                       jsonb_object_agg
------------+---------------------------------------------------------------
 2018-05-07 | {"pm10": 72.2, "speed": 21.3, "orientation": 1.3}
 2018-05-06 | {"o2": 75.2, "pm25": 25.2, "speed": 43.6, "orientation": 1.3}
(2 rows)

postgres=#


ERROR 1146 (42S02): Table 'db.tablename' doesn't exist

I think you might be able to find the answer in the following post:
https://stackoverflow.com/questions/7759170/mysql-table-doesnt-exist-but-it-does-or-it-should

Seems to have the same issue as you.


Columns names are case sensitive on postgres

When migrating, did the migration script double-quote the column names when creating tables? That would preserve the case.

You could either fix the migration script and re-import, or rename the columns e.g.

ALTER TABLE my_user RENAME COLUMN "USER_ID" TO user_id;

(note: user was used in the original question but that's a reserved keyword in Postgres so that couldn't have been one of the tables involved)


Problem with Script to Synchronize Login on AlwaysON

I would suggest using dbatools - Sync-DbaAvailabilityGroup with -Exclude <-- exclude the settings that you dont need to sync.

Honestly, you should sync all e.g. sp_configure, logins, jobs, etc


Example of query execution plan where the cardinality estimation makes a difference

At 180 rows, you're not likely to see any difference whatsoever.

In order to see different plans get chosen at different predicate distributions, you'd need quite  bit more data, and likely competing data sources (indexes) to aid them.

Let's say you had 1.8mm rows in the table. If you've got

A clustered index on a column unrelated to price or country (Like Id or something)
A nonclustered index on price and country
A single column nonclustered index on country

You may start to see a difference in the method of aggregation, serial vs. parallel plans chosen, and index choice, depending on which country you search for and its selectivity.

For an example of a recent Q&A:

Large variances between two similar queries when doing WHERE EXISTS on related table

For a more complex reasons:

Why is my query suddenly slower than it was yesterday?


MySQL select * from table without conditions does not return rows right away

What language are you using in your client?  What connector?  There may be a setting there.

Guessing that it is python, I see this:

https://dev.mysql.com/doc/connector-python/en/connector-python-api-cext-buffered.html

and guess that this would do the trick:

    cnx.buffered(True)

before the lengthy SELECT *.  It is not likely to help SELECT COUNT(*) since the is nothing to 'buffer' until it is finished.

Since you added details about using the Windows cli mysql.exe utility, See if mysql --quick achieves your goal.  If that doesn't provide the speed you require, perhaps consider using a loop, with a LIMIT clause, and "remember where you left off".  This would make each call to mysql.exe relatively quick, at the expense of having to call it many times, once for each window of rows.


What is Oracle equivalent for Linked Server and can you join with SQL Server?

Yes, you can.
In Oracle, this is called "Heterogeneous Connectivity" This Oracle admin manual has details. The basic outline is

Install ODBC drivers on Oracle server
Configure ODBC to talk to the SQL Server DB (create a system DSN)
Test the ODBC
Configure Global_Names parameter to false.
Create initodbc.ora file to configure Heterogeneous services
Modify listener.ora to connect to both Oracle and ODBC drivers
Modify tnsNames.ora file
Reload the listener to see the above changes.
Create a DB Link from Oracle to the ODBC connection.
Test via SQL Select statement.

This article walks through an example case of doing the above steps with details.


Determine sql server installation (server or client tools only)

take a look here :

  HKEY_LOCAL_MACHINE\System\CurrentControlSet\Services\MSSQLServer
  HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server

SQL Server 2008 R2

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\SQLNCLI10\CurrentVersionSQL

SQL Server 2008

SQL Server Native Client 10 HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\SQLNCLI10\CurrentVersion

SQL Server 2005

SQL Server Native Client 9 HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Native Client\CurrentVersion


Here are some suggestions on comparing different queries, since that is what you will most likely end up doing. I have made these types of remarks in the past. These recommendations are somewhat based on those comments from the community.

Create the Table TISSET

CREATE TABLE TISSET(    COL1 int ,COL2 int,COL3 int)

Create Random Data

As @peer mentionned in his comments, you should run your query on actual data. One important step on top of that, is to query against data that closely matches the actual production data you will be using. In the step below, I create random data but that is more evenly distributed. In other words, every N insertions, the values repeat themselves.

-- create random data
DECLARE @ROWNUM as bigint = 1
WHILE(1=1)
BEGIN
    set @rownum  = @ROWNUM + 1
    insert into TISSET values(1 + (CHECKSUM(NEWID())) % (30*60),
    1 + (CHECKSUM(NEWID())) % (24*60), 1 + (CHECKSUM(NEWID())) % (60*60))

    if @ROWNUM > 50000
        BREAK;
END

The amount of data and how its distributed affects the way in which the Query Optimizer chooses which execution plan to use. Having a lot of records is not sufficent, you must also consider how its distributed: number of unique values, one column having more duplicates than another.

After adding so much data, you should also consider page splits and fragmentation.
This query comes from Paul White.

 SELECT
    DDIPS.index_type_desc,
    DDIPS.alloc_unit_type_desc,
    DDIPS.index_level,
    DDIPS.fragment_count,
    DDIPS.avg_fragment_size_in_pages,
    DDIPS.page_count,
    DDIPS.avg_page_space_used_in_percent,
    DDIPS.record_count,
    DDIPS.avg_record_size_in_bytes
FROM sys.dm_db_index_physical_stats
    (
        DB_ID(),
        OBJECT_ID(N'TISSET', N'U'),
        NULL,
        NULL,
        'DETAILED'
    ) AS DDIPS;

There are no indexes in TISSET, but if you did create a clustered index and/or non-clustered index, you would get back the level of fragmentation. Having heavingly fragmented data can skew results and dramatically slow things down.

Clear out the cache

Here we are cleaning out the cache and running a checkpoint. When comparing queries, it can be helpful to know the elapsed time when a query is in cache and when it is being read from disk into cache.

-- Cold cache
DBCC DROPCLEANBUFFERS
CHECKPOINT
CHECKPOINT

DBCC DROPCLEANBUFFERS cleans all buffers. Do not use this command on a production server as you will seriously slow your server down as it reads data back into memory from the disk.

CHECKPOINT - All dirty data file pages for the database are written to disk (all pages that have changed in memory since they were read from disk or since the last checkpoint), regardless of the state of the transaction that made the change.

STATISTICS IO and TIME

The statistics IO statement will send back interesting information

Logical pages read, was tempdb used.

Here is the BOL page on STATISTICS IO

The statistics TIME statement will send back Execution times, both CPU and User.

Here is the BOL page on STATISTICS TIME

Output from your query

STATISTICS IO

Table 'TISSET'. Scan count 49898, logical reads 6834930, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

I tend to look at logical reads (Number of pages read from the data cache.) and Worktable (tempdb may have been used). A closer examination of the execution plan would be necessary. Looking at yours, the use of the Sort operator indicates that tempdb was probably used, if not enough memory was available to perform the sort.

STATISTICS TIME

SQL Server Execution Times:
   CPU time = 819333 ms,  elapsed time = 113509 ms.
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 0 ms.

-- compare queries
set statistics io on
set statistics time on

-- first query

   SELECT TTOuter.Col1,(
        SELECT CASE WHEN SUM(TTInner.Col3 - TTInner.COL2 + TTInner.COL1 ) > 0 THEN 'BIG' ELSE 'SMALL' END D
        FROM (
            SELECT TTInner.Col3, TTOuter.COL2, TTOuter.COL1
            FROM TISSET TTInner
        ) TTInner)
    FROM TISSET TTOuter

-- second query goes here

set statistics io on
set statistics time on

Then of course there is the Execution plan which you can review by simultaneously pressing CTRL+M or under the Query Menu in SSMS, choose Display Acutal Execution Plan

If you were analyzing multiple queries, each one would get a batch cost. This is not an exact metric, but I find it useful when the cost difference between two queries is significant.

Although this information won't answer every question, it can be used as a starting point for comparing queries and deciding which one is best.


MySQL schema design - how to decide between an `enum` vs. a table join

(Yes, there have been many heated discussions of ENUM vs TINYINT vs VARCHAR, both here and on stackoverflow.com.  I have put my 2-cents in on some of them.  I am addressing this Question to discuss nuances of the examples brought up.)

Currency is a tricky one.  When, for example, a devaluation occurs, does the currency_code change?

If so, then is the question is about how often the list of values is likely to change.

If it does not change, then you may have a mess for the data -- Is "XYZ", the old xyz currency, or the 'new'?

With things like country_code (with well-established 2-letter codes) and currency_code (3 letter standard), etc, I recommend using things like

country_code  CHAR(2) CHARACTER SET ascii
currency_code CHAR(3) CHARACTER SET ascii

Rationale:

Only 2 or 3 bytes, which is not much bigger than a 1-byte ENUM or TINYINT.
Avoids the extra bulk and overhead of utf8mb4, which might be the default for the table.
You can still build a secondary table to, for example, spell out the full name of the country or currency.
You won't need a JOIN except when you want the extra info.  (JOINing is most costly when you need to start from multiple Dimension tables in an attempt to look up rows in the Fact table.  A JOIN is reasonably cheap going the other direction.)


Use PostgreSQL's built-in uuid data type, and create a regular b-tree index on it.

There is no need to do anything special. This will result in an optimal index, and will also store the uuid field in as compact a form as is currently practical.

(Hash indexes in PostgreSQL prior to version 10 were not crash-safe and were really a historical relic that tended to perform no better than a b-tree anyway. Avoid them. On PostgreSQL 10 they've been made crash-safe and had some performance improvements made so you may wish to consider them.)

If for some reason you could not use the uuid type, you would generally create a b-tree on the text representation or, preferably, a bytea representation of the uuid.


Query Simplification for better execution

Other than changing your query so that you are using a JOIN when referencing your tables there is not much that can be done to simplify the query itself as it is pretty basic. Here is your query rewritten to use a JOIN:

Select a.item_no,
       a.item_type,
       a.item_group,
       a.uom_group,
       a.unit_price,
       c.service_category,
       c.item_no,
       b.manufacturer,
       b.issue_method,
       b.item_no,
       d.preferred_vender,
       d.mfr_catlog_no,
       d.purchasing_uom,
       d.items_per_purchase_unit,
       d.quantity_per_package,
       d.item_no,
       e.sales_uom_name,
       e.item_per_sales_unit,
       e.package_uom_name,
       e.quanti_per_package,
       e.item_no
  from Item_Master_Details a
  join Item_Master_General_Material b
    on a.item_no = b.item_no
  join Item_Master_General_Service c
    on b.item_no = c.item_no
  join Item_Master_Purchasing_Data d
    on c.item_no = d.item_no
  join Item_Master_Sales_Data e
    on d.item_no = e.item_no;

If it is still running slow then you might want to consider an indexing strategy, materialized views or even creating a de-normalized table to contain the data that you require.


What do I need to restore an encrypted MSSQL database?

We can't restore a backup on a different instance without Certificates. We have to restore the Cert from the source server, then we should be able to restore the database from the backup.

Obviously your certificate & master key are required for restore.

As MSSQLTREK Blog here To restore the Certificate from source (and you have to create a DMK (Database Master Key) on destination if you don’t have one), the rest is just performing your normal DB restore process what you do on any other non encrypted user database(s).

Create a Master Key (you don’t have to do this if you already have DMK) and create a certificate from the cert backup and provide private Key file and password to decrypt it.

  Backup files of databases that have TDE enabled are also encrypted by
  using the database encryption key. As a result, when you restore these
  backups, the certificate protecting the database encryption key must
  be available. This means that in addition to backing up the database,
  you have to make sure that you maintain backups of the server
  certificates to prevent data loss. Data loss will result if the
  certificate is no longer available.

For your further ref Here and Here

Note : If you enable TDE on any Database on your Instance, Your TEMPDB is automatically Encrypted as well. So there’s a high possibility impacting other databases which are actually not using TDE feature while you are in the process of enabling TDE!


How to perform count(), count(distinct) in the same output using POSTGRESQL

WITH cte1 AS ( SELECT t1.keyword, t1.count(keyword) cnt
               FROM keyword t1, sent t2, tweets t3, users t4
               WHERE t4.user_id='18839785'
                 AND t3.id=t4.user_id
                 AND t2.id=t3.id
                 AND t2.id=t1.id
                 AND t2.sent='Pos'
               GROUP BY t1.keyword ),
     cte2 AS ( SELECT COUNT(DISTINCT keyword) cnt
               FROM cte1 )
SELECT cte2.cnt distinct_drivers, cte1.keyword positive_drivers, cte1.cnt countt
FROM cte1, cte2
ORDER BY cte1.cnt;


"This table has the appropriate indecies but is becoming a major hangup when running queries"

Partitioning alone doesn't help query performance unless SQL Server is able to eliminate partitions when running a query.  Your WHERE clause needs to line up with the way you partition.  We only get one field to use as a partitioning field, so if that field isn't included in your WHERE clause, you're still likely to scan the entire table despite having partitions.

"and just because of its size."

Partitioning can make certain maintenance operations easier, but there's still things we can't do on a partition-by-partition basis.  If index maintenance and stats updates are causing you problems, you're better off splitting the design into an archive table and a live-updated table.  When you need to periodically move data from the live table to the archive table, you do that, rebuild the indexes with 100% fill factor, update stats with full scan, and then set its filegroup to read-only.  Partitioning can help with archive table loads - but partitioning the live table may not.  (I'm tossing out several advanced concepts here as if it's quick and simple, but I'm just sketching out some background here.)

"It appears that using the partitioning would require less code changes."

Sorta kinda - it looks that way at first glance, but the more you get into it, you've got options like partitioned views.  You can rename the existing table, put in a view in its place, and then you can make your own changes to the underlying tables (and add multiple tables) without changing your app.

I've written more about the pitfalls of partitioning here:

http://www.brentozar.com/archive/2008/06/sql-server-partitioning-not-the-answer-to-everything/


Do all nodes of a SQL Always-on Availability group need to be the same version of SQL?

Yes, you can absolutely do this. I've done this with several 2012 to 2016 migrations and not encountered any issues along the way.

Just bear in mind, you will not be able to fail back to the 2012 instance once you fail to that 2016 replica, and until you have more 2016 replicas you will not have any HA in place.


curious case of two log files in sql database

Standard warning about not using multiple log files etc etc.

You have two log files and are wondering why the first one didn't grow to fill all of the available disk space (or limit on the file size) before moving on to the second file. Correct?

A little bit of pre-information.  Log files are broken up into multiple virtual log files (VLFs).  If you search for VLF you can find a lot of information on them and when you have too many or too few, but that's a different subject.

If it helps you can think of the multiple log files as a single big circle.  The log information will be written round and round in that circle.  As each VLF is filled up the next is checked until it hits a VLF that is already in use.  Only at that point will there be an autogrowth.  I believe the currently used file will be the one autogrown.  If it can't be (file size limit or out of disk space) then the next file is checked and so on.  Only if none of the files can be grown will you get an out of space error.


You can use ROW_NUMBER window function to set an order, then simply use it as a subquery with a proper predicate.
SELECT FROM
(
  Subquery
)
WHERE
    post_num <= 2;

SELECT
    title,
    author,
    created_at,
    ROW_NUMBER() OVER (PARTITION BY author ORDER BY created_at) as post_num
FROM
    posts

title             | author | created_at | post_num
:---------------- | :----- | :--------- | -------:
Johns first post  | John   | 2021-01-01 |        1
Johns second post | John   | 2021-01-02 |        2
Johns third post  | John   | 2021-01-03 |        3
Mikes first post  | Mike   | 2021-01-01 |        1
Mikes second post | Mike   | 2021-01-02 |        2
Mikes third post  | Mike   | 2021-01-03 |        3

db<>fiddle here


How can I detect the use of SQL Server replication on a server (all versions) preferably using PS or CMD

You can use Replication Management Objects. You can interrogate them locally or remotely (the better option). The DLLs should be on some systems. If you still have known SQL 2000 instances please test them as it's unclear if modern versions of RMO will still talk down to SQL 2000 instances; 2005 and above should be more reliable.

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Replication")
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Rmo")

$rmo = New-Object Microsoft.SqlServer.Replication.ReplicationServer(".")
if ($rmo.DistributorInstalled -or $rmo.IsDistributor -or $rmo.IsPublisher -or $smo.RegisteredSubscribers) {
    "Has Replication"
}

There are other flags you can check out. I should tell you that RMO is deprecated (much to my chagrin) but it still works for now.

Another option was to

Import-Module SQLPS -DisableNameChecking
Get-ChildItem SQLSERVER:\SQL\...(server)...\...(instance or DEFAULT)...\Databases | Where { $_.ReplicationOptions -ne "None" }

But SQLPS is part of the 2008 R2 Feature Pack or 2012+. So if you have a server like that you can run it remotely. However also this replication flag can be enabled for other reasons like CDC and so is usually a little misleading but in your case maybe it's just what you're after.


Did a SQL Server instance ever participate in replication?

I'm not sure if you'll be able to reliable tell if the server has ever used replication or not.  For example, if someone drops replication properly, there will be no trace left behind.


Need help importing SSIS packages in SSMS

Quirk or Perk?

Try this. Close SQL Server Management Studio. Then, right-click it's icon and Run as Administrator. Then, try viewing those packages once more.

That being said...

Use DTUTIL

I find DTUTIL the easiest way to import/export packages to and from SQL Servers.

https://technet.microsoft.com/en-us/library/ms162820(v=sql.110).aspx

Example:

Update, the code from this author looks promising for mass-import of packages using DTUTIL though I have not tried it.

https://canserhat.wordpress.com/2012/08/23/export-and-import-all-ssis-packages-in-sql-server-2008/


Query optimization in case of TIMESTAMPDIFF

You should move all the calculations to one side of the comparison:

WHERE created_on < (unix_timestamp(now()) - 5*60*60) * 1000

5*60*60 equals 5 hours


db2 removal of Generate always Expression on Timestamp data type

This is not valid syntax.  You need to use,

 alter table xxxx alter column update_time drop generated

See the documentation on alter table for more details.


Single table or multiple tables for optional keys

Based on your model, a single table with nullable keys is superior. To find all of the values in the value column, you have to join three different tables and maintain that join in any query operations -- including updates (if you add a key3 to ValueB, now you have to move the entire record to a new table: an insert plus a delete instead of an update).

Better to have null fields so your record -- and any additional columns associated with that record out beyond the Value column -- don't have to be migrated between tables by your business logic tier. Instead, direct update statements can manage these changes.


Data truncated for column - return fatal error

Probably you need to upgrade to 5.7 or MariaDB to get STRICT_TRANS_TABLES to give you an error (and abort the transaction) for that situation.

https://mariadb.com/kb/en/mariadb/sql_mode/
http://dev.mysql.com/doc/refman/5.7/en/sql-mode.html


A basic version of materialized view are being delivered in 9.3, see depesz's article. However, it's a very limited version as you have to call REFRESH MATERIALIZED VIEW to get the database to update the view. This will hopefully improve to become automatic in later versions soon.


For that many-to-many 'relation' table, don't bother to have an id.  Simply have

PRIMARY KEY(case_id, tag_id),
INDEX(tag_id, case_id)

Note that one of those works perfectly for one of your SELECTs, the other works perfectly for the other.  (Use ENGINE=InnoDB.)

To get (tag-1, tag-2, tag-3), use GROUP_CONCAT().

FOREIGN KEYs are optional; I would not bother.


How mysqldump replaces special characters during dumping records?

There are two ways: string and hexadecimal notation, e.g.:

'abc\' as string will be like this -> 'abc\\', all special characters are escaped
'abc\' as hexadecimal will be like this -> 0x6162635C, you can test it using HEX function - SELECT HEX('abc\\');

You can choose one of those methods. Also, try to play with --hex-blob option and check results.


Rather than focusing on the hardware first, or the availability group even, it would be good to get a handle on what this "query" (test case) is waiting on.

To do that, you could run Paul Randal's script for Capturing wait statistics for a period of time, or sp_BlitzFirst from the open source First Responder's Kit.  Or you could just poll sp_WhoIsActive periodically and see what the test query is waiting on in each sample.

Since I'm on 2016, I'm going to use sys.dm_exec_session_wait_stats because it's super easy to do, and it's 11:30 pm and I'm tired.

I ran your test query in SSMS against one of my AG boxes (finished in less than 2 seconds), grabbed the session ID, and then ran this:

SELECT *
FROM sys.dm_exec_session_wait_stats
WHERE session_id = @spid;

The results came out like this:

I ran this from a jump box, so that explains the small amount of ASYNC_NETWORK_IO.  The other main wait there is SOS_SCHEDULER_YIELD, which makes sense - this was a CPU-bound task getting switched off the scheduler periodically (or maybe missing its quantum, this is a VM and there's other work going on as well including SSAS, AG stuff, production workload, scheduled jobs, etc).  And it was only 141 ms during a 2000-ish ms query.

I ran the same test on my local instance, which is barely doing anything other than writing this answer (query finished in 1 second):

There were no significant waits at all.

The wait stats for your benchmark should tell the story in your situation.

Any answer we could give would likely just be speculation without the wait info (maybe there's other CPU load you're not aware of on the "slow server," or the server is experiencing thread exhaustion due to the AG or other queries, or there are "poison waits" like THREADPOOL or RESOURCE_SEMAPHORE, etc).


Really slow DISTINCT ON query with multiple joins

Because the WHERE condition of the query involves only equality checks:
WHERE "songs"."processed" = 't'
  AND "songs"."working" = 't'

and then you have:
SELECT  DISTINCT ON (songs.rank, songs.shared_id) ...

which is similar to GROUP BY songs.rank, songs.shared_id
I would first try adding a compound index on (first the columns in WHERE, then the columns in DISTINCT ON):
(processed, working, rank, shared_id)

The ordering: ORDER BY rank DESC may be better optimized if you have the index as:
(processed, working, rank DESC, shared_id)

Not really sure if this would contribute to efficiency but you can test.

Addition by @Erwin
As per request in comment
In principal (default) b-tree indexes can be scanned forward and backward at the same speed. But sorting can make a difference in multi-column indexes where you combine the sort order of multiple columns. The query starts with:
SELECT  DISTINCT ON (songs.rank, songs.shared_id)

In combination with ORDER BY rank DESC this dictates that the result be ordered by rank DESC, shared_id effectively. After the (simplified) WHERE clause WHERE processed AND working has been applied and before LIMIT can be applied.
I have my doubts if the DISTINCT clause is actually useful. But while it is there, the optimal index for the query should be (just as @ypercube suspected):
CREATE INDEX songs_special_idx
ON songs (processed, working, rank DESC, shared_id);

Looks like one of the rare cases where explicit ordering of index columns would benefit the query. There is an excellent explanation in the chapter Indexes and ORDER BY of the manual.
If the WHERE condition is stable (always WHERE processed AND working), a partial multi-column index would be smaller and faster, yet:
CREATE INDEX songs_special_idx
ON songs (rank DESC, shared_id)
WHERE processed AND working;


I found my answer and I want to laugh on myself... What a silly mistake I did!
Sharing this solution so that others can save their time.
I just prefix sudo command in the below statement and mysql started.

$ sudo /etc/rc.d/init.d/mysql start

Input password:
Starting MySQL.
I checked and found that MySQL is running by issuing below command:

$ mysqladmin -u root -pjoomla1 ping

mysqld is alive


This looks like the bug/feature request MDEV-16546.

This pertains to the system versioned tables columns like row_start explicitly as normal invisible columns are currently still updateable.


How do I locate my Postgres data directory while the server is down?

Check  /usr/local/var/postgres

See also answers for Mac in How do I find PostgreSQL's data directory?.

A Linux PGDATA directory looks like:

bash-4.2$ ls -al $PGDATA
total 72
drwx------. 20 postgres postgres  4096 Apr  8 16:44 .
drwx------.  4 postgres postgres    31 Feb 11 23:07 ..
drwx------.  7 postgres postgres    62 Apr  7 21:27 base
-rw-------.  1 postgres postgres    30 Apr  8 16:44 current_logfiles
drwx------.  2 postgres postgres  4096 Apr  8 17:47 global
drwx------.  2 postgres postgres  4096 Apr  7 08:07 log
drwx------.  2 postgres postgres     6 Feb 13 09:21 pg_commit_ts
drwx------.  2 postgres postgres     6 Feb 13 09:21 pg_dynshmem
-rw-------.  1 postgres postgres  4513 Feb 13 09:21 pg_hba.conf
-rw-------.  1 postgres postgres  1636 Feb 13 09:21 pg_ident.conf
drwx------.  4 postgres postgres    65 Apr  8 17:49 pg_logical
drwx------.  4 postgres postgres    34 Feb 13 09:21 pg_multixact
drwx------.  2 postgres postgres    17 Apr  8 16:44 pg_notify
drwx------.  2 postgres postgres     6 Feb 13 09:21 pg_replslot
drwx------.  2 postgres postgres     6 Feb 13 09:21 pg_serial
drwx------.  2 postgres postgres     6 Feb 13 09:21 pg_snapshots
drwx------.  2 postgres postgres     6 Apr  8 16:44 pg_stat
drwx------.  2 postgres postgres  4096 Apr  8 17:49 pg_stat_tmp
drwx------.  2 postgres postgres    17 Feb 13 09:21 pg_subtrans
drwx------.  2 postgres postgres     6 Feb 13 09:21 pg_tblspc
drwx------.  2 postgres postgres     6 Feb 13 09:21 pg_twophase
-rw-------.  1 postgres postgres     3 Feb 13 09:21 PG_VERSION
drwx------.  3 postgres postgres    89 Mar 21 15:02 pg_wal
drwx------.  2 postgres postgres    17 Feb 13 09:21 pg_xact
-rw-------.  1 postgres postgres    88 Feb 13 09:21 postgresql.auto.conf
-rw-------.  1 postgres postgres 24005 Feb 13 09:21 postgresql.conf
-rw-------.  1 postgres postgres    27 Apr  8 16:44 postmaster.opts
-rw-------.  1 postgres postgres   104 Apr  8 16:44 postmaster.pid
-bash-4.2$


The way I would initially think of this (as an accountant in a previous life) is not as a transfer, but as what is going on in MY bank's account? One stored procedure would deduct funds from one of my member's accounts, and a second stored procedure (initiated by the application) would send something to the other bank system to deposit funds. The record of the transfer would go into the individual account's records as just another transaction, like a deposit or withdrawal. There would be no lasting connection between the two accounts.
Even if the transfer was between a member's checking and savings accounts, they would still be treated as essentially two transactions going on at nearly the same time. One deposit, one withdrawal.


If you don't need real "pairs" you can get the list of employees sharing a desk using this:

select e1.*
from employees e1
where exists (select *
              from employees e2
              where e1.desk_number = e2.desk_number
                and e1.id <> e2.id);

If you want that as a single "list of IDs" you can use string_agg()

select string_agg(e1.id::text, ',') as id_list
from employees e1
where exists (select *
              from employees e2
              where e1.desk_number = e2.desk_number
                and e1.id <> e2.id);

Or use array_agg() to get those IDs as an array

Online example: http://rextester.com/AXVGA22292


VLF count suddenly growing and unable to reduce it

The VLFs grow when the log grows
When VLF’s are created, either by autogrow or a manual grow; a certain number of VLF’s are allocated according to the size.
< 64MB will create 4 VLF’s
= 64MB < 1GB will create 8 VLF’s
and > than 1GB will create 16 VLF’s.

This was prior to 2014. From 2014 SQL Server onwards this has changed and can read in more details here : Understanding increasing number of VLFs

A few hundred of VLF is actually not that a big issue and you can re-configure your threshold. To fix this you can shrink the log file to the lowest possible and make it grow to a bigger size at once and then allow autogrowth if required. More on this can be read here :

Fixing vlf issue

VLFs will always increase and only when huge in number will cause performance issues (thousands ).


How to get connection details for another connection open to database

What you are asking for is only available in MySQL 5.7. It's a new thing I have not played with yet.

It is in the performance_schema database (a.k.a. Performance Schema System Variable Tables)

session_variables : System variables for the current session. An application that wants all system variable values for its own session should use this table. It includes the session variables for its session, as well as the values of global variables that have no session counterpart.
variables_by_thread : Session system variables for each active session. An application that wants to know the session variable values for specific sessions should use this table. It includes session variables only, identified by thread ID.

It looks like this

mysql> desc performance_schema.variables_by_thread;
+----------------+---------------------+------+-----+---------+-------+
| Field          | Type                | Null | Key | Default | Extra |
+----------------+---------------------+------+-----+---------+-------+
| THREAD_ID      | bigint(20) unsigned | NO   |     | NULL    |       |
| VARIABLE_NAME  | varchar(64)         | NO   |     | NULL    |       |
| VARIABLE_VALUE | varchar(1024)       | YES  |     | NULL    |       |
+----------------+---------------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

To address the question, you would need to run somthing like

SELECT * FROM performance_schema.variables_by_thread WHERE thread_id = 6649;

If you want that kind of access to other connections, you must upgrade to MySQL 5.7.

Sorry, there is nothing else available in MySQL 5.6 and prior.


Newly created user has rights on MySQL database

If I am not mistaken, the test Database is created by default.

  In addition, the mysql.db table contains rows that permit all accounts to access the test database and other databases with names that start with test_. This is true even for accounts that otherwise have no special privileges such as the default anonymous accounts. This is convenient for testing but inadvisable on production servers. Administrators who want database access restricted only to accounts that have permissions granted explicitly for that purpose should remove these mysql.db table rows.

http://dev.mysql.com/doc/refman/5.0/en/default-privileges.html

Try to create a new DB with a different name and repeat your steps.

Hope this will help you.


Oracle OS Authentication only works on certain databases/clients

Fixed the problem:

The source of the problem was that the broken clients and server were referencing a central TNSNames.ora file on a different file server. This was intended; however, I did not know that this also makes those machines search the sqlnet.ora there.
Someone - or some installer or something - placed an empty sqlnet.ora file in that central location. I haven't tried if the clients only ignore their local files if there is a central one, but in this case any changes to the local sqlnet.ora files had no effect.

Adding the line

SQLNET.AUTHENTICATION_SERVICES = (NTS)

to that central sqlnet.ora file obviously fixed it.


PostgreSQL full-text search retrival from id gap

Found my stupid mistake...
instead of running the query as my_table.objects.raw(sql_query) I was creating a connection to another db with

conn = psycopg2.connect("dbname='wrong_db' user='my_user' password='my_password'")
cur = conn.cursor()
cur.execute(sql_query)

so lesson learnt... use the Django built-in capabilities to eliminate the possibility of a connection to a wrong db.

The coincidence that I was getting ids from my gap range was caused by the fact that at some point I had tried to import (unsuccessfully) the entries from wrong_db to my db and this created the gap in ids.


UUID's make poor primary keys, but there's nothing wrong with them when used as a regular table attribute.  Instead of exposing the AUTO_INCREMENT column to the client connection, adding a UUID column and exposing that should have an insignificant impact overall, and nothing that can't be addressed with a non-clustered index on the new UUID column, if necessary.

I would likely only bother adding the UUID column to the main user table and altering my update procedures to always require the user UUID as a parameter ( and might even, at that point, leave the other parameters as the current AUTO_INCREMENT value ), reinforcing the intent that a particular user can only update their own data, but there's nothing wrong with adding the UUID column to every table which may receive updates and replacing any exposed AUTO_INCREMENT values with the new UUID either.


Is .NET Framework 3.5 still needed with a SQL Server 2017 installation to utilize Database Mail?

No, it is not required for SQL Server 2017.  The issue with Database Mail not working was fixed in 2016 SP1 CU2, and 2016 RTM CU2:
FIX: SQL Server 2016 Database Mail does not work on a computer that does not have the .NET Framework 3.5 installed or stops working after applying SQL Server update

I know of several SQL Server 2016 and 2017 systems that are installed without .NET 3.51, and Database Mail is working fine.

Note that .NET Framework 3.5 was not included in Hardware and Software Requirements for Installing SQL Server, and that the SQL Server 2016 and SQL Server 2017 view of this page is the same.  The Database Mail issue was considered a defect in SQL Server 2016 and was fixed accordingly.


Why do I get this error "Statement failed, SQLSTATE = 08001"?

You need to add yourself in the Firebird group

sudo adduser `id -un` firebird

or always use localhost address in front of db if Server is Classic or SuperClassic

See the long thread here
http://firebird.1100200.n4.nabble.com/Fwd-Have-you-tried-firebird-2-5-SuperClassic-from-the-packages-td3053790.html


You need to select from something. Another option is:

db2 => values next value for schema_name.sequence_name;

As you noted you can also use SYSIBM.SYSDUMMY1 or create a virtual table:

db2 => select next value for schema_name.sequence_name from ( values 1 );


Yes, you can import old history into history tables. Here is a quick example that shows how to do this - you need to turn off system versioning temporarily.

CREATE TABLE dbo.TemporalExampleHistory
(
  TemporalID int NOT NULL,
  UserName   sysname,
  ValidFrom  datetime2 NOT NULL,
  ValidTo    datetime2 NOT NULL
);
GO

CREATE TABLE dbo.TemporalExample
(
  TemporalID int PRIMARY KEY,
  UserName   sysname,
  ValidFrom  datetime2 GENERATED ALWAYS AS ROW START NOT NULL,
  ValidTo    datetime2 GENERATED ALWAYS AS ROW END   NOT NULL,
  PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
)
WITH
(
  SYSTEM_VERSIONING = ON
  (
    HISTORY_TABLE = dbo.TemporalExampleHistory
  )
);

Now, insert some data:

INSERT dbo.TemporalExample(TemporalID, UserName)
  VALUES(1,N'Bob'),(2,N'Frank');

-- update a row to make some history:

UPDATE dbo.TemporalExample
  SET UserName = N'Frankie'
  WHERE TemporalID = 2;

Validate there is a system-versioned row in history table:

SELECT * FROM dbo.TemporalExampleHistory;

Now, to insert rows into history. Execute these steps one at a time; the parser will prevent the insert if these are all in a single batch.

BEGIN TRANSACTION; -- may want SERIALIZABLE here

ALTER TABLE dbo.TemporalExample SET (SYSTEM_VERSIONING = OFF);

INSERT dbo.TemporalExampleHistory(TemporalID, UserName, ValidFrom, ValidTo)
  SELECT TOP (1) TemporalID, N'Little Frankie', '19000101', ValidFrom
    FROM dbo.TemporalExampleHistory
    WHERE TemporalID = 2
    ORDER BY ValidFrom;

ALTER TABLE dbo.TemporalExample SET
(
  SYSTEM_VERSIONING = ON
  (
    HISTORY_TABLE = dbo.TemporalExampleHistory,
    DATA_CONSISTENCY_CHECK = ON
  )
);

COMMIT TRANSACTION;

Now validate there are now two versioned rows of id = 2 in history table:

SELECT * FROM dbo.TemporalExampleHistory;


Sql Anywhere 11: Restoring incremental backup failure

Summarizing the information linked to in the post above....

The command you have used is for a live backup but what you want is an incremental backup.  The approach you want to use (per this blog) uses somewhat different switches.  You want to use the -n -t -x switches, and the -o to specify a log file.  So the command ends up something like:

dbbackup -n -t -x -c "eng=ServerName.DbName;uid=dba;pwd=sql;links=tcpip(host=ServerName)" -o "c:\backup\backup_log.txt"


Can I select the rows that have the greatest Column A values compared to all other rows which have identical columns B in MySQL?

SELECT id, MAX(`time`) AS `time`
FROM sourcetable
GROUP BY id;

PS. Stop thinking in iterations, think in datasets.


Attacks on Postgresql listening for requests on 'localhost'

Starting by securing PostgreSQL according to Erwins answer is a good start. However, there are other ways to attack and gain access to data. Even if you are after data as an attacker, one wouldn't assume that the database is directly accessible from the internet.

But data might be accessible indirectly due to flaws in the application server, in this case JBoss, and its configuration or in the application code (sql injection).

JBoss had a seriuos security vulnerability about a year ago that allowed you to gain root access - and as a consequence full access to any PostgreSQL server on localhost.
http://www.darknet.org.uk/2011/02/jboss-autopwn-jsp-hacking-tool-for-jboss-as-server/

UPDATE:

Yes, Ashwin, using prepared statements or stored procedures/functions is a good way to minimize the risks of sql injections attacks. OWASP has a check list for prevention of SQL injections.
https://www.owasp.org/index.php/SQL_Injection_Prevention_Cheat_Sheet#Defense_Option_1:_Prepared_Statements_.28Parameterized_Queries.29

One advantage of stored procedures/functions is the granular control of permissions. For example you can grant access for the application user to only be able to run specific procedures/functions, whilst access is denied to the underlying tables and other objects. The procedure/function is instead run by the permission of the owner of the procedure/function (a user also with restrictive permissions, just able to operate on the specific tables). This setting can be controlled with SECURITY DEFINER, as in this example function.

CREATE OR REPLACE FUNCTION myschema.myfunction() RETURNS void AS
$BODY$
BEGIN
    INSERT INTO myschema.mytable(msg) VALUES('value goes here');
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE SECURITY DEFINER;
ALTER FUNCTION myschema.myfunction() OWNER TO myinsertupdateuser;
GRANT EXECUTE ON FUNCTION myschema.myfunction() TO myappuser;

This function can be executed by myappuser, but myappuser can not access myschema.mytable directly. In other words myappuser can only run the code you specify in the function. The function is run as myinsertupdateuser, which is assumed to have insert permission on the myschema.mytable table.


Postgres: Getting the "partition number" instead of row_number

Use count as a window function:

SELECT id, data
FROM (SELECT id, data,
             count(*) OVER (PARTITION BY data) AS c
      FROM t) AS q
WHERE c > 1;


Disable nestloops into the Postgresql JDBC connection

You can use the options connection parameter, as explained here:

  options = String

  Specify 'options' connection initialization parameter.

  The value of this property may contain spaces or other special characters, and it should be properly encoded if provided in the connection URL. Spaces are considered to separate command-line arguments, unless escaped with a backslash (\); \\ represents a literal backslash.

So that should look like

options=-c%20enable_nestloop%3Doff

But I share the horse's concern that this may be a bad idea, and you should rather tune your queries.


I don't think you are going to get anything better than the composite index. However, the key distribution for the composite index could hamper query performance depending on the (hasPhoto,userStatus) combination you choose. Here is how:

Run this Query

SELECT COUNT(*) rowCount,hasPhoto,userStatus FROM Users GROUP BY hasPhoto,userStatus;

This will show you how sparse and how dense each composite index combination is.

Sparse combinations (low rowCount) should use an index scan.

Dense combinations (high rowCount) should use a full table if the Query Optimizer believes it must read too much of the composite index.

You can try this out by running

EXPLAIN SELECT COUNT(*) FROM Users WHERE hasPhoto=1 AND userStatus='a';

This will reveal whether the EXPLAIN plan favors an index scan (Extra column Using where; Using index), or a full index (or table) scan (select_type of ALL or SIMPLE).


Restart of rownumber based on count in other table in oracle sql

Continuing on Lennart's answer, here is how you might do it in a single query.

  with table1 as (
    select cus_id, rownum rn from cus_table
  ),
  table1_count as (
    select count(*) cnt from cus_table
  ),
  table2 as (
    select nv.name_val, mod(rownum, t1c.cnt) + 1 t1_rn
    from name_values nv, table1_count t1c
  )
  select t1.cus_id, t2.name_val
  from table1 t1
    inner join table2 t2 on t1.rn = t2.t1_rn;

First, since you don't have any windowing in your query, you don't have to use row_number() analytic function, you can just use the rownum pseudocolumn. Secondly, note the + 1 in mod(rownum, t1c.cnt) + 1 - mod(x, y) goes from 0 to y - 1, but rownum goes from 1 to y, hence we need to increase the modulo by 1.

The complete script with which I tested is here. http://pastebin.com/DTGir8XK The query runs in well under a second on my home computer.

However, note that the query is non-deterministic due to lack of any ORDER BY! You might get different results each time, so perhaps you should reconsider what exactly you are trying to do.

(In response to a now deleted self-answer) If table1 contains 40k rows and table2 80k, then you get only 40k rows as a result because there are "ids" in table1 which are not present in table2, so inner join eliminates such rows from the result.

You could modify the query to reverse the roles of table1 and table2 to get all 80k rows, however, it might be better for you to try to explain your situation and what exactly your ultimate goal is and we might help you better.


How to select rows that have multiple relations in common

But, the trouble I am having is how to select places that have a
  combination of tags such as tag-z and tag-y

You are close with your query. You can use GROUP BY and SUM to find those relations.

Please note mine query is valid with sql-mode enabled only_full_group_by because places.id is a PRIMARY key.

Query

SELECT
   places.id
 , places.name
FROM
 places

INNER JOIN
 place_taxonomy
ON
 places.id = place_taxonomy.place_id

INNER JOIN
 tags
ON
 place_taxonomy.tag_id = tags.id

GROUP BY
 places.id

HAVING
    SUM(tags.slug = 'tag-z')
  AND
    SUM(tags.slug = 'tag-y')

Result

    id  name
------  ---------
     3  Place C

demo http://sqlfiddle.com/#!9/ea5723/15

Or use a delivered table to find the places with the tags 'tag-z' and 'tag-y'.

Query

SELECT
    places_taged.id
  , places_taged.name
FROM (

  SELECT
     places.id
   , places.name
  FROM
   places

  INNER JOIN
   place_taxonomy
  ON
   places.id = place_taxonomy.place_id

  INNER JOIN
   tags
  ON
   place_taxonomy.tag_id = tags.id

  WHERE
   tags.slug IN (
      'tag-z'
    , 'tag-y'
   )
)
 AS places_taged
GROUP BY
    places_taged.id
  , places_taged.name
HAVING COUNT(*) >= 2

Result

| id |    name |
|----|---------|
|  3 | Place C |

demo http://sqlfiddle.com/#!9/ea5723/27


Is it better to delete then insert, or to update then insert in SQL server?

UPDATE then INSERT usually. Simply, it's less work.

In this case, you have an ID columns (IDENTITY): I'll assume this is the clustered index

You delete rows, you leave gaps in pages = fragmentation. You add rows, you probably need more pages allocated. Other processes are doing this too.

An UPDATE will update in-situ and you'll have a less expensive INSERT because there is less rows.

Saying that...

If your new:update is 100:1 then it doesn't really matter of course. And the EXISTS is required too.

However, from a raw "shifting data" perspective UPDATE..INSERT would be my choice


Chance of skipping extended events when using an offset for the file target

I couldn't find a definitive answer about how the offset is generated, only one unreliable reference that they're a block number. A little disappointing, I guess the engineers don't use stack exchange.

However I was listening to the SQL Down Under podcast this week and they were discussing the issues with doing ETL based on timestamps which are a little unreliable and result in skipped records.

The advice was that it's a good practice to read 30s back from where you left off and then eliminate duplicates.

So unless someone comes up with something better you can at least address your missed record concerns by starting your reads one block prior to the last block you received, and then eliminating duplicates.


Uniqueness constraint for elements of arrays in the entire table

Yes, as you said, I'd create an emails table that refer to the users.
CREATE TABLE users (
    id_user         ... ,
    username        varchar(255),
    ... ,
    PRIMARY KEY(id_user)
);

CREATE TABLE emails (
    id_email        ... ,
    id_user         ... ,
    email           varchar(255),
    ...,
    PRIMARY KEY(id_email),
    CONSTRAINT fk_user
      FOREIGN KEY(id_user)
      REFERENCES users(id_user)
);

Then in the emails table you can add a unique constraint on id_user, id_email.
CREATE UNIQUE INDEX CONCURRENTLY xak1_unique_id_user_and_email_ix
ON emails (id_user, id_email);

ALTER TABLE emails ADD CONSTRAINT xak1_unique_id_user_and_email UNIQUE
                      USING INDEX xak1_unique_id_user_and_email_ix;

P.S. I don't work usually on PostgreSQL, for the syntax I referred to link


setting up a local database to be updated from a master database

Personally, I would go with MySQL Replication. I would go about using it as follows:

First, I would make should every table is InnoDB in all databases (except mysql and information_schema).

Here is a two commands to create a script to convert all MyISAM tables into InnoDB (with the exception of MyISAM tables that have FULLTEXT indexes), and run that script:

mysql -h... -u... -p... -A --skip-column-names -e"SELECT CONCAT('ALTER TABLE ',db,'.',tb,' ENGINE=InnoDB;') FROM (SELECT A.db,A.tb,A.tbsize FROM (SELECT table_schema db,table_name tb,(data_length+index_length) tbsize FROM information_schema.tables WHERE engine='MyISAM' AND table_schema NOT IN ('information_schema','mysql')) A LEFT JOIN (SELECT table_schema db,table_name tb FROM information_schema.statistics WHERE index_type='FULLTEXT') B USING (db,tb) WHERE B.db IS NULL) AA ORDER BY tbsize" > /root/ConvertMyISAM2InnoDB.sql
mysql -h... -u... -p... -A < /root/ConvertMyISAM2InnoDB.sql

Second, I would use periodic mysqldumps. Since the database is very small, you could run the following script every 24 hours:

echo "STOP SLAVE;" > /root/MySQLData.sql
mysqldump -hmasterhostaddr -u... -p... --single-transaction --master-data=1 --routines --triggers --all-databases >> /root/MySQLData.sql
echo "START SLAVE;" >> /root/MySQLData.sql
mysql -hslavehostaddr -u... -p...  -A < /root/MySQLData.sql

You can also run the script on demand to repair any slave.

You can also run this script against a live master since all user tables are InnoDB and you perform the mysqldump as a single transaction. Thus, a slave is loaded with a point-in-time snapshot.


Cant unlock file (Errcode: 11) on my new tables

My suspicion would be that you are either using external_locking or you have character set issues.

It might center around the following bugs

Error 11 - can't unlock file when using external_locking
Inconsistent use of column alias In ORDER BY clause that specified collation

I would suggest upgrading to MySQL 5.6 as this situation is consistent with MySQL 5.1.


Maintenance Plan on Replication Subscriber

Do I treat the subscribers just like a normal database and perform maintenance via a maintenance plan on them - or should they be left alone / treated any differently?

Yes, subscriber databases should be treated as regular user databases & you should intelligently perform Index maintenance (and do backups as well) . Infact, replication does not replicate index maintenance. Also, from BOL :

One suggestion - please do not use maintenance plan !


Viable strategies for rebuilding indexes online in SQL Server 2008 R2 Standard edition?

Assuming these are non clustered indexes then the CREATE INDEX topic on BOL discussing offline operations indicates this approach should only partially work in that it will allow read only access to the table but will still block updates. The relevant section is below (emphasis mine)

  Table locks are applied for the duration of the index operation. An
  offline index operation that creates, rebuilds, or drops a clustered
  index, or rebuilds or drops a nonclustered index, acquires a Schema
  modification (Sch-M) lock on the table. This prevents all user access
  to the underlying table for the duration of the operation. An offline
  index operation that creates a nonclustered index acquires a Shared
  (S) lock on the table. This prevents updates to the underlying table
  but allows read operations, such as SELECT statements.


Does a MySQL dump that contains triggers double data on import?

If you take time to run mysqldump, even with all default arguments, you will see that triggers in the dump file are created after all insert statements that populate your tables. So no, there will be no duplication, your data after restoring the dump will be the same as in the source database.


Finding and fixing InnoDB index corruption

Thanks Rolando and Michael for your responses.

To close the loop, here's the answer I came up with for my original questions:

Q: How can I determine if other tables have similar index corruption?
A: Use CHECK TABLE. I ran mysqlcheck -c on all of the relevant InnoDB tables to find out which ones had index corruption
Q: What's the most efficient way to fix the corrupt indexes?
A: Use OPTIMIZE TABLE to rebuid the InnoDB table that have corrupt indexes. This causes a complete table rebuild which fixes the corruption.


According to official advice from Microsoft there is no need to remove the subscriber.

How do I rebuild indexes or tables in replicated databases?
There are a variety of mechanisms for rebuilding indexes. They can all be used with no special considerations for replication, with the following exception: primary keys are required on tables in transactional publications, so you cannot drop and recreate primary keys on these tables.
How do I add or change indexes on publication and subscription databases?
Indexes can be added at the Publisher or Subscribers with no special considerations for replication (be aware that indexes can affect performance). CREATE INDEX and ALTER INDEX are not replicated, so if you add or change an index at, for example, the Publisher, you must make the same addition or change at the Subscriber if you want it reflected there.

There is a potential, AFAIK, if you're using transactional replication that you will get a big queue of transactions that can't be applied to your subscriber (assuming that your publisher is quite busy otherwise you wouldn't be using replication). This may need to be managed, possibly by doing a REORGANIZE instead of a REBUILD or some such, but you can gauge that best for your situation.


Can you please clarify Users vs Programmers? Typically this is how it is done:

1) Create three identical schemas and name them PROD, QA and DEV. Their names are self explanatory.

2) Create a dbuser for the application (PHP) use on each of them. If your app is MYAPP, name the dbuser myapp. This is the login the application uses on behalf of every application user.

3) Create a dbuser called dbadmin on each of them and give it to your DBA.

4) Create a dbuser called myapp_dev on each of them and give it to your developers.


Although the definitions of the schemas or levels of representation of the ANSI/SPARC architecture did not reach the desired precision, they are only three, and they are named external, conceptual and internal, respectively.

As far as I know, there is no fourth level, because the physical structures employed by a database management system (DBMS for brevity) compose, in fact, the internal schema, as I will explain below.

Note: Even if imprecise, the ANSI/SPARC architecture is definitely a very useful reference when building a system, so it looks like the literature you found contains inaccurate information or perhaps it makes a reference to the (also very useful) notion where the layers of abstraction of a database are called conceptual, logical and physical, which has different implications.

Brief descriptions of the three ANSI/SPARC levels

I deem opportune to describe each level (ordered from the highest to the lowest degree of abstraction) so as to expose its scope:

The external level is the way in which the information is presented to end-users of a computerized information system; e.g., the specific organization of the pieces of information as displayed or requested in the screens or windows (via, e.g, lists, grids, labels, text boxes, check boxes, drop-down menus, etc.) of an application program.
The conceptual level involves the representation of the structure of the information as (a) perceived by the business experts and (b) modeled by the database analysts, commonly in terms of entity types, properties and associations, so it is comprised of ideas or conceptualizations, it is not “concrete” yet and its representation requires constructs that are in lower levels of abstraction. The conceptual level is informally referred to as the business rules of a certain organization regarding its informational requirements. As an side, schemas of this kind are usually —though not necessarily— expressed via graphical devices like, e.g., entity-relationship diagrams to facilitate communication between the interested parties.
The internal level has to do with how the DBMS of relevance stores the information in a concrete form; e.g., arrays, pages, extents, files, records, fields, pointers, etc. This is the lower level  of abstraction since, to put it some way, the DBMS “saves” the information in “real” structures, so this may also be conceived as the physical level of the architecture but naming it in this way does not imply that there is a fourth level. It is worth to mention that there can be different sublevels of abstraction within the internal level itself, but that factor depends on the particular mechanisms used by the DBMS in question.

Relational databases created on SQL DBMSs with respect to the ANSI/SPARC architecture

When a practitioner uses a SQL platform (Microsoft SQL Server, IBM DB2, Sybase ASE, PostgreSQL, etc.) to build a database using the mechanisms proposed by the relational model by Dr. E. F. Codd:

The base tables, columns, types (and domains when available) and constraints must reflect the characteristics of the conceptual schema (entity types, properties, associations, cardinalities) so that the data (in the shape of rows) remains consistent with it. The declarations made by means of a data sublanguage (e.g., SQL DDL) of the base tables, columns, types and constraints are instruments that formalize the conceptual schema, so they are considered logical constructs and, as such, they do not yet “tocuch” the physical level, they are still free from DBMS-specific concrete storage structures. Normalization, being a logical procedure, applies here, not at the conceptual schema.
The views (i.e., derived tables that “combine” columns from one or more base tables or from other derived tables as well, typically by virtue of SQL DML operations involving JOINs) maintain a close correspondence to the external level, because they gather and provide the information requested or sent by the screens or windows of one or more application programs, just like the end-users see it.
The indexes, pages, extents, files1, records2, fields3, partitions, allocation maps, and many other arrangements match the internal schema, since they are the “physical” structures utilized by a DBMS to store the information.

The SQL DBMS functions as a mediator (1) between the internal and the logical representations of the conceptual schemas, and (2) between the internal and external representations through connections via views.

Notes

1, 2, 3 Note how important it is to distinguish between (a) files, records and fields and (b) tables, rows and columns —or relations, attributes and tuples—, as they are elements that belong to distinct levels of abstraction and, hence, are susceptible of very different operations and treatments, having therefore very dissimilar impacts.


Select from sys.server_file_audits returns wrong filename

Reviewing the documentation for sys.server_file_audits, the log_file_name is (highlighting mine)

  Base name for the log file supplied in the CREATE AUDIT DDL. An
  incremental number is added to the base_log_name file as a suffix to
  create the log file name.

Depending on how quickly your sqlaudit files are being created, it can be extremely difficult to determine what the 'current' audit file is.

If you want a quick one-off select of audit information, you can use wildcards of '*.sqlaudit' to read ALL files in the directory.

SELECT * FROM sys.fn_get_audit_file ('O:\auditfiles\logins*.sqlaudit',default,default);

Reading sqlaudit files that are currently being written to presents challenges in the fact that rows can be written to that active file immediately AFTER you have just read it, so you might miss future audit rows if you don't continually select them.  Also, be advised that audit files will continue to be written to your directory path and the wildcard select will run longer and longer unless you implement some kind of pruning process of the sqlaudit files.

If your intention is to track and save this audit information, I'd suggest implementing a solution similar to an answer I gave for 'How to load data to a table from SQL Server Audit File?'

It uses a Sql Agent job that executes a stored procedure to process the sqlaudit files.  In summary, the stored procedure:

Uses Powershell to move any audit files (that are not currently being
written to) to a staging fileshare. The powershell command uses the
-ErrorAction SilentlyContinue parameter which allows the command to 'skip' over files that are currently being written to when copying to
the staging fileshare. Eventually, the 'locked' files will become
available to be copied on a future interval.
Now that I have available SQLAudit files to read from the staging
fileshare, we're able to use sys.fn_get_audit_file to process all
SQLAudit files from the staging fileshare by using wildcards.
After consuming the files from the staging fileshare, I use
Powershell to delete the files from the staging fileshare.


The mapped logins are removed when database is restored

Logins reside in master database and users reside in non system databases. Logins does not come with user database backup.  There is an exception for contained database, in which case there is no login mapped to user. Everything you need is contained in the user database as mentioned by @Nic and @Sepupic in comment section.

Read this for more details:

Basic SQL Server Security concepts – logins, users, and principals by Laurentiu Cristofor

Follow the process described here to copy logins to destination server.

How to transfer logins and passwords between instances of SQL Server

You can also use PowerShell function to copy logins.

COPY-DBALOGIN


Postgres 10.3 could not bind IPv6 address, database system was not properly shut down

Those messages are unrelated.  The first one will show up every time you start postgresql if your machine is not configured to use IPv6.  You can just ignore it if you don't care about IPv6.

The other one is a bit more complex.  'system was not properly shut down' means it was interrupted without being given a chance to do a checkpoint and shut down. 'database system was interrupted while in recovery' means that it had been interrupted before, and was then interrupted again before it was able to clean up from the previous interruption, and is now trying to clean up from both of those previous interruptions.

The 'stopped waiting' means the pg_ctl call in your startup script is not patient enough to for the recovery to happen (which seems to me to be a poor decision to make in a startup script).  But then the issue is, what then?  Sometimes when pg_ctl runs out of patience, it just exits nicely and lets the recovery proceed in the background.  Sometimes it clubs the recovery process over the head.  Which of these it does seems to be pretty unpredictable (to me, anyway) it depends on your OS and OS version, and how your start-up script is written, and maybe what logging options you have configured.

Based on the log file show posted, it is likely that on your system pg_ctl clubs the recovery process over the head once it runs out of patience.  So you shutdown the system uncleanly when it was in the middle of something hectic.  Restarting it will take a lot of time, because it has to clean up whatever it was in the middle of.  pg_ctl ran out of patience and killed it (this part was not in your posted logfile snippet).  Then pg_ctl ran out of patience again and killed (this part is in your snipped).

Your start-up script should probably pass a large timeout value to the -t flag of pg_ctl, to make it patient.


MS SQL : Use Computed value to Compute other values

It's simple, computed value can be used by applying is value with cross apply

like this
select cpk.coupon, p.Price, p.Price-cpk.coupon as FinalPrice -- etc

from Product -- etc
CROSS APPLY (
   select coupon = CASE
  WHEN c.CouponPercent = 0 THEN p.Price - (c.CouponPercent/100)
  ELSE c.CouponValue
END as Coupon ) as cpk

where --etc


Service broker internal spid hogging tempdb

As you can see on this answer, have a look at which databases have service broker enabled (on both target and source servers)

you can use the following query to check which databases have the broker enabled:

--=====================================================================
-- checking what we have and where we point to
--=====================================================================
SELECT @@SERVERNAME
-- my_target_server

USE [master]
GO
SELECT   [name]
 ,[is_broker_enabled]
 ,[service_broker_guid]
FROM [sys].[databases]
WHERE 1=1
  AND is_broker_enabled = 1
ORDER BY  NAME
GO

SELECT name,is_broker_enabled,service_broker_guid,
is_db_chaining_on,  is_trustworthy_on FROM sys.databases
    order by 2 desc, 3 desc,  1

I see you mention the security update and patching - well done for applying it!

Depending on the environment we sometimes use db chaining ownership on, and some times trustworthy on, instead of module signing which is the best way in most cases.

anyway, if you are not using the broker, please switch of all of these features for each database, i.e. disable the broker, and switch it off trustworthy and db ownership chain.

--one of my examples of enabling some of these features and renewing the broker id:

  ALTER DATABASE ORCASTG SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE
  ALTER DATABASE ORCASTG SET NEW_BROKER with rollback immediate
  ALTER DATABASE ORCASTG SET trustworthy on with rollback immediate

You want something like these (provided you are not using any of these features):

  USE MASTER
  ALTER DATABASE [APCore] SET DISABLE_BROKER WITH ROLLBACK  IMMEDIATE
  ALTER DATABASE [APCore] SET TRUSTWORTHY OFF WITH ROLLBACK IMMEDIATE
  ALTER DATABASE [APCore] SET DB_CHAINING OFF WITH ROLLBACK IMMEDIATE

and keep monitoring the tempdb usage, I don't think you need neither to restart the service, nor do a failover.

In any case if you are on alwaysOn - Availability Group you may need to remove the database from the availability group before you can disable the broker.


How to limit rows in PostgreSQL update statement

You need to search for the desired rows with a subquery, and use the primary key of the table to relate those rows to the table in the UPDATE statement.

In general, rownum can be replaced with the row_number() window function (see, e.g., Using window functions in an update statement), but for this case, it is easier to just use limit:

UPDATE employees
SET commission_pct = 0
WHERE id IN (SELECT id
             FROM employees
             WHERE commission_pct IS NULL
             LIMIT 10000);


How to simplify schema for multi database / hundreds of millions of rows data set

When it comes to databases, less is more.

You are doing the right thing by archiving off old data as this will speed up the performance and reduce the maintenance time of the active database.

The archived data needs to be considered. Keeping it in a separate database instance will be beneficial because backups/restoration and other maintenance activities will be completed separately - reducing the effect on the live data.

You should consider keeping the archived data on a physically different disk to the live data - this will avoid conflicts if both are being used at the same time. Also consider the type of disk this the archive is on as this has cost implications - does it really need to be on a raid type array or is a single disk and backup tape/dvd all that is needed? Storage may be relatively cheap, but it is still a cost in $, IO, time and network.

Next take a look at the archived data - do you really need to keep all the records? do you really need to keep all the columns of data? Would changing the structure of the data result in a smaller disk footprint/faster read query time? Can you summarize the data? Archived data for data mining does not always need to have the same structure as live data.

Keeping separate months data in separate databases may be a good way to go, but there other options that may make the analysis easier/quicker. Your options here include keeping the data in one database instance, but with table per month. Another is to have one set of archive tables and use table partitioning (read the manuals).


Does a Levenshtein distance involve some computation for each single row?

PostgreSQL

Using PostgreSQL, you can create a trigram index on a column with the pgtrgm module and do a similarity search on it with the <-> operator,

  text <-> text Returns the “distance” between the arguments, that is one minus the similarity() value.

note there is also word_similarity. I would highly suggest checking out the module, as it explicitly addresses your concerns,

  Trigram matching is a very useful tool when used in conjunction with a full text index. In particular it can help to recognize misspelled input words that will not be matched directly by the full text search mechanism.


For your concern, i would suggest, you make the generated log files read only so that nobody other than the owner can change its contents. Anyway if contents of any file is changed, you can check its last modification time by using command stat filename.

Till now postgresql doesn't have any reliable auditing tools, and by looking at the pgaudit link, it just gives you some extra information from what is present in database logs, and will create a logfile possibly bigger than the database log. It could result in space constraints on your server.


Need help optimizing mysql on a busy server

If you have frequent updates, then you should be using InnoDB as with MyISAM the whole table gets locked.

If you have so much RAM, you could set up large buffers to keep your tables completely in memory, which should give you a big improvement in performance.

Try turning on the slow query log to capture slow queries and queries not using indexes. You could use Percona's pt-query-digest tool to analyze the slow query log. Try tuning the slowest queries first and adding any indexes if necessary.


You are basically looking for filtered replication, and as of writing this answer (August 2016) it is not available as a feature in MongoDB.  The relevant feature request is SERVER-1559 for tracking/voting purposes (it is currently unscheduled).

In terms of options, you can get close to what you describe with the mongo-connector but that is not an officially supported tool.


Should a table have a clustered index even if it doesn't have appropriate fields for it?

1) IF PlayerId is assigned with NEWSEQUENTIALID, you could consider that as the clustered index.

2) Otherwise, you can add an IDENTITY and make that clustered (questionable benefit, since all access will be through the PK you have already established).

3) Or you can leave it as a heap - with appropriate non-clustered indexes.

My order of preference would be 1, 3, 2 assuming you can't change the uniqueidentifier to an IDENTITY instead.

Can you explain why you are using uniqueidentifier in the first place? - that may have some bearing on this.


Renaming a column is a simple dictionary update. Yes, it will be done for all partitions.

SQL> CREATE TABLE t1 (
       c1   NUMBER,
       c2   VARCHAR2(30),
       c3   NUMBER,
       c4   NUMBER, constraint t1_pk primary key (c1, c4) using index local)
  PARTITION BY LIST (c4) (
       PARTITION p10 VALUES (10),
       PARTITION p20 VALUES (20),
       PARTITION p30 VALUES (30,40));

Table created.

SQL> alter table t1 rename column c2 to c5;

Table altered.

SQL> alter table t1 rename column c4 to c6;

Table altered.

SQL> alter table t1 rename column c1 to c7;

Table altered.

SQL> desc t1
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 C7                                        NOT NULL NUMBER
 C5                                                 VARCHAR2(30)
 C3                                                 NUMBER
 C6                                        NOT NULL NUMBER


Do ORMs provide interfaces for working with window functions?

Not really.

While some ORMs may very well be able to utilize windowing functions you yourself write none of them will be able to generate window functions. ORMs map data to data. They are an Object Oriented to relational data-translation layer. Window functions are a data-processing feature. It's a declarative way to process a result set, they run in the last-step of result-set generation, docs:

  If the query contains any window functions, these functions are evaluated after any grouping, aggregation, and HAVING filtering is performed. That is, if the query uses any aggregates, GROUP BY, or HAVING, then the rows seen by the window functions are the group rows instead of the original table rows from FROM/WHERE.

At the point a Window Function works, the authors of an ORM may as well map the syntax into their own result-set processing features.


"Folder" structure of Postgres database missing in DBeaver

In the database navigator on the left, right-click on your database connection, and select "Advanced" rather than "Simple":


Why am i getting "missing right parenthesis" error when i try to LOG ERRORS when loading from an external table?

Because the DML error logging (LOG ERRORS INTO ...) part belongs to the outer insert, not the inline external table. Move it out from the inner part.
External tables have a different kind of error logging, another file can be specified with the BADFILE clause where the rows with errors are logged.
Update:
Something like this:
insert /*+ ignore_row_on_dupkey_index ( enzyme ( enz_name ) ) */
into enzyme
SELECT enz_name FROM EXTERNAL ((
  construct_id NUMBER(10),
  n_term VARCHAR2 (50),
  enz_name VARCHAR2 (3),
  c_term VARCHAR2 (50),
  cpp VARCHAR2 (50),
  mutations VARCHAR2 (50),
  mw_kda NUMBER (7, 3))

    TYPE ORACLE_LOADER
    DEFAULT DIRECTORY data_to_input
    ACCESS PARAMETERS (
        RECORDS DELIMITED BY NEWLINE
        skip 1
        FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
        MISSING FIELD VALUES ARE NULL
        )
    LOCATION ('CONSTRUCT.CSV')
    ) ext
    where not exists (
        select * from enzyme e
        where e.enz_name = ext.enz_name
    )
LOG ERRORS INTO ERR$_ENZYME ('INSERT') REJECT LIMIT UNLIMITED;


Group by multiple columns with different members of the same hierarchy?

Try this ( replace tbl with your table name):

WITH Unpivoted
AS (
    SELECT customerID
        ,ProductLineSlot
        ,ProductLine
    FROM (
        SELECT customerID
            ,productLine1
            ,productLine2
            ,productLine3
        FROM tbl
        ) p
    unpivot(ProductLine FOR ProductLineSlot IN (
                productLine1
                ,productLine2
                ,productLine3
                )) AS unpvt
    )
SELECT ProductLine
    ,count(*) AS [Number Of Customers]
FROM Unpivoted
GROUP BY ProductLine;

Or you can rewrite that without CTEs or derived tables:

SELECT
    ProductLine,
    COUNT(*) AS [Number Of Customers]
FROM
    tbl AS t
    UNPIVOT
    (
      ProductLine
      FOR ProductLineSlot IN (productLine1, productLine2, productLine3)
    ) AS u
GROUP BY
    ProductLine
;

To elaborate a little on how the query works, the UNPIVOT clause turns your table into a row set like this:

customerID  ProductLineSlot  ProductLine
----------  ---------------  -----------
1           productLine1     1
2           productLine1     2
2           productLine2     1
3           productLine1     4
3           productLine2     2
3           productLine3     1

Rows where ProductLine is null are automatically omitted by UNPIVOT. You can see that the above row set can be grouped by ProductLine to get row counts per group for the final result:

ProductLine  Number Of Customers
-----------  -------------------
1            3
2            2
4            1

And that is what the query does.


Put those values in /etc/sysctl.conf and they will be read on boot

Mine is:

[codyc:~]$ cat /etc/sysctl.conf
kern.sysv.shmmax=1073741824
kern.sysv.shmall=1073741824


RMAN Backup to Disk and to Cloud Parallel

Your local backup uses a disk channel. Cloud backup uses and SBT channel.
The same backup can not be duplexed to mixed kind of channels, see below.

Duplexing Backup Sets

  RMAN can duplex backups to either disk or tape, but cannot duplex
  backups to tape and disk simultaneously.

Even if you used an SBT channel for the local backup, that would require a media manager library that is different from the cloud SBT media manager library, which you again can not mix.

So the answer is no.

Just create some local disk backups, then backup the backupsets to the cloud.

About Multiple Copies of RMAN Backups

  Back up your files as backup sets or image copies, and then back up
  the backup sets or image copies with the RMAN BACKUP BACKUPSET or
  BACKUP COPY OF commands


What is the impact of DROP SCHEMA on concurrent read only workloads in PostgreSQL?

DROP SCHEMA would attempt obtaining exclusive use of the schema first, so it would only actually manage to drop the schema when PostgreSQL is done retrieving data from ongoing queries. Further queries would likely be locked until the end of the transaction in your question.

Queries accessing tables in the prod schema may fail after this transaction is finished if they (queries or functions procedures) were compiled prior to the dropping and renaming of the schemas, but should be safe for queries from newly established connections.


Here is a modified query that should be faster and produce the same results:

SELECT r.batch_id, r.unique_doc_id AS DOCUMENT_ID, r.brand,
   (SELECT h.message_date_time FROM doc_recon_header_w h WHERE r.fileid = h.fileid)
      AS UPLOAD_DATE,
   decode(r.recordstatus, '5', 'SUCCESS', 'FAILED') AS UPLOAD_STATUS,
   decode(r.recordstatus, '5',  null,
      (decode(d.vendor_unique_doc_id, r.unique_doc_id, d.comments, r.comments)))
      AS FAILURE_REASON
FROM doc_recon_record_w r
LEFT JOIN doc_duplicate_b d ON d.vendor_unique_doc_id = r.unique_doc_id
ORDER BY r.batch_id;

Reasoning:

Doc_recon_header_w is only joined to get the message_date_time, so that is moved to the select list so it will occur only on the results that will be displayed.  Note that this won't work if there is more than one row per fileid in the table or a header record does not exist for a record, but I wouldn't expect either of these to be the case.
The join on doc_recon_record with doc_duplicate_b is done only to check for the existence of a record in the former, which means the join doesn't need to be a FULL JOIN and can be a LEFT JOIN if the table order is re-arranged.


I'm with you: better to have a more flexible system than having to change your table structure every year. 8M rows is not excessive, just make sure you index it properly based on how you search/retrieve the information, and you'll be fine.

There's another way to go that would probably fit your use case better: document store databases. Switch to MongoDB or RethinkDB and flexibility issues disappear.


294 : Failed with error 'aborted', from rs4 to rs0

Answer is NO.

At 3.4 mongodb we got multi threaded chunk balancing and it is "little bit" stupid that way that it can try to move chunk (from or to) shard what is currently already participating in some other chunk move operation. Of course, that is not possible and then that new move operation is aborted.

If you start seeing huge aborted values, like thousands, check log files of those shards whose name is most listed on error list. Like rs4 there. From log files you see what was "reason" why move was aborted. There can be be cursor with no timeout and previous move operations remove is hanging there.

But, aborted chunk move does not lose data, ever. Data is never removed until it is copied to the new location and then mark to be there.

To find a possible reason for those aborts, all (primary) mongod.log files must be checked. Without them, it's impossible. There is always from node and to node, both log files have information about "that" movechunk operation what was aborted.


Fixed in 5.7.4:  A page allocation for an undo log due failed with a “table is full” error message instead of an “undo log is full” error message.

5.6.3 is pre-GA.  Recommend you upgrade at least to a later 5.6.xx.

See https://dev.mysql.com/doc/refman/5.6/en/innodb-undo-logs.html as for a discussion of what can be done about "undo tablespaces" starting with 5.6.3.


Copy a row from a table to another with the same columns but different order in a trigger

Apparently we don't consider it "simple" to obtain column names of the tables from sys.columns or from Information_Schema.Columns (which handily has a table name column), and then.... hmm, yes, if you construct a "dynamic SQL" statement to call with EXEC() then it won't have access to the [inserted] and [deleted], so you need to copy the row key column to a temporary table... I suppose that isn't simple.  Unless you are doing it one row at a time - then the row key column, is just one variable, and by calling sp_executesql with the appropriate parameter... hmm, again, today may be a headache day.

However... if that's a Microsoft SQL Server error message, then you can again use dynamic SQL to create view objects on one or both of the tables that simply have the column order changed - and you can write in your trigger (I think) if you want,

INSERT DocumentVersionView SELECT * FROM inserted;

INSERT DocumentVersionView SELECT dv.* FROM DocumentView dv JOIN inserted di ON blah blah blah

Whichever is required.  The INSERT will write to the table in the view.

Update: "Of course" DocumentVersionView doesn't have column DocumentVersion_id; the table column gets assigned automatically by your auto-increment.


MySQL: change column type using expression

The correct syntax would be

ALTER TABLE my_table1 MODIFY COLUMN my_uuid_column VARCHAR(36) GENERATED ALWAYS AS (1 +1)  STORED;

But you can't add many functions to a generated column, for example uuid() doesn't work, so you must do this in your code. where you generate the insert

Stored functions and user-defined functions are not permitted.
Stored procedure and function parameters are not permitted.
Variables (system variables, user-defined variables, and stored program local variables) are not permitted.
Subqueries are not permitted.

See official Documentation


Best practice for copying tables from server to server

This looks like a good working solution. Keep in mind however, that copying large amounts of data within a transaction will potentially grow your logfiles significantly. That costs a lot of time and can cause unexpected drive space shortages. Also, during that time the target tables are completely unaccessible for outside connection. The source tabes might also be blocked, depending on you transaction isolation level.

It might be better to break the actual data movement down into small batches. But then you would loose transactional consistency if concurrent writes happen.

As Aaron mentioned, you might also want to look into existing tools like DataCompare (would also not guarantee transactional consistency). If you have to execute this copy process regularly, you could also look into snapshot replication. (This would guarantee transactional consistency, but it would need to be setup by a sysadmin. However, after that initial setup you could start the synchronization yourself.)

Finally, if you go with your solution, make sure that in the error condition you first rollback the transaction and then record to the error log table, otherwise you would rollback that new entry too. You should also check that there is actually a transaction at the time of the error: IF XACT_STATE() != 0 ROLLBACK


65G SQL Server database has 5.5G of data in tempdb when not in use

Non-temporary user objects

Maybe there are non-temporary user tables in tempdb.  These wouldn't get cleaned up automatically (unless the server is restarted).  You can check for them like this:

USE tempdb;
GO
SELECT * FROM sys.objects WHERE [type] = 'U';

To release this space, you'll need to manually drop these tables using DROP TABLE statements.  Make sure no one is still using them first

Sleeping sessions keeping temp objects alive

Another possibility is that someone created a temp table, and their session is still active.  I created a temp table using the Stack Overflow sample database and left the session open:

SELECT * INTO #Users FROM dbo.Users;

Checked space usage:

All 4 tempdb files are using about 15 MB of space.

You can see that the session is still open by checking sp_WhoIsActive with the option to show sleeping spids:

EXEC master.dbo.sp_WhoIsActive @show_sleeping_spids = 2;

Look for sessions that have tempdb allocations, or otherwise suspicious sessions that have been open for a long time.

If I close the window for session 53 in SSMS, and then re-check sp_WhoIsActive, the query is gone.  And now I check tempdb space usage again:

You can see that "Space Used" dropped in all 4 files.

To release space that's being held like this, you'll need to either:

track down the person in the login_name field and ask them to end their session, or
kill the session using the KILL command