Does updating a row with the same value actually update the row?

Due to the MVCC model of Postgres, and according to the rules of SQL, an UPDATE writes a new row version for every row that is not excluded in the WHERE clause.
This does have a more or less substantial impact on performance, directly and indirectly. "Empty updates" have the same cost per row as any other update. They fire triggers (if present) like any other update, they have to be WAL-logged and they produce dead rows bloating the table and causing more work for VACUUM later like any other update.
Index entries and TOASTed columns where none of the involved columns are changed can stay the same, but that is true for any updated row. Related:

PostgreSQL Initial Database Size
Redundant data in update statements

It's almost always a good idea to exclude such empty updates (when there is an actual chance it may happen). You did not provide a table definition in your question. We have to assume first_name can be NULL (which wouldn't be surprising for a "first name"), hence the query has to use NULL-safe comparison:
UPDATE users
SET    first_name = 'Michael'
WHERE  id = 123
AND    first_name IS DISTINCT FROM 'Michael';

If first_name IS NULL before the update, a test with just first_name <> 'Michael' would evaluate to NULL and as such exclude the row from the update. Sneaky error. If the column is defined NOT NULL, use the simple equality check, though, that's a bit cheaper.
Related:

How do I (or can I) SELECT DISTINCT on multiple columns?
Update column with data from another table


structure when storing books in elasticsearch

I found out how to do this with the help of this guide:
The nested type is right and in my case the only option.

curl -XPUT localhost:9200/readgeek/book/1 -d'
{
  "names": [
    {
      "english": "Omero",
      "german": "Homer"
    }
  ],
  "authorid": "1",
  "ratings": "2589",
  "editions": [
    {
      "title": "Odyssey",
      "languageid": "123",
      "isbn10": "1234567890",
      "isbn13": "1234567890123",
      "ratings": "158"
    },
    {
      "title": "Odisea",
      "languageid": "150",
      "isbn10": "1234568890",
      "isbn13": "1234569890123",
      "ratings": "15"
    },
    {
      ...
    }
  ]
}'


You need to add some parameters to your @msgtext so SQL Server knows where you put your data. Something like so:

EXEC sp_addmessage
    @msgnum = 50001,
    @severity = 10,
    @msgtext =
        N'Error 50001, severity 10, state 1 was raised, Please check mdf file. The freespace is %d percent for %s residing on %s' ,
    @lang = 'us_english',
    @replace='REPLACE';

Then, you would raise it....

declare @freespacePct int = 45
        ,@dbname sysname = 'MYDB'
        ,@drive varchar(16) = 'F:/'
RAISERROR(50001, 10,1,@freespacePct, @dbname,@drive) with LOG;

That would return:

  Error 50001, severity 10, state 1 was raised, Please check mdf file.
  The freespace is 45 percent for MYDB residing on F:/

The %s is used for strings, %d is used for signed integers. You can find all of the type specifications in the RAISERROR documentation.

Type specification  Represents
d or i              Signed integer
o                   Unsigned octal
s                   String
u                   Unsigned integer
x or X              Unsigned hexadecimal

  These type specifications are based on the ones originally defined for
  the printf function in the C standard library. The type
  specifications used in RAISERROR message strings map to Transact-SQL
  data types, while the specifications used in printf map to C language
  data types. Type specifications used in printf are not supported by
  RAISERROR when Transact-SQL does not have a data type similar to the
  associated C data type. For example, the %p specification for pointers
  is not supported in RAISERROR because Transact-SQL does not have a
  pointer data type.


How can I get a unique array in PostgreSQL?

While there is no function to accomplish that, you can use

unnest() to convert an array of elements, to a table of rows of one-column,
DISTINCT to remove duplicates
ARRAY(query) to recreate the row.

That idiom looks like,

ARRAY( SELECT DISTINCT ... FROM unnest(arr) )

And in practice is applied like this,

SELECT ARRAY(SELECT DISTINCT e FROM unnest(ARRAY[a,b,c,d]) AS a(e))
FROM ( VALUES
  ('foo', 'bar', 'foo', 'baz' )
) AS t(a,b,c,d);

If you want it sorted you can do,

SELECT ARRAY(SELECT DISTINCT e FROM unnest(ARRAY[a,b,c,d]) AS a(e) ORDER BY e)
FROM ( VALUES
  ('foo', 'bar', 'foo', 'baz' )
) AS t(a,b,c,d);

And that can all can be written with CROSS JOIN LATERAL which is much cleaner,

SELECT ARRAY(
  SELECT DISTINCT e
  FROM ( VALUES
    ('foo', 'bar', 'foo', 'baz' )
  ) AS t(a,b,c,d)
  CROSS JOIN LATERAL unnest(ARRAY[a,b,c,d]) AS a(e)
  -- ORDER BY e; -- if you want it sorted
);

Answer inspired by RhodiumToad on irc.freenode.net/#postgresql


Joining WordPress Comments with Post Titles from MySQL DB

SELECT * FROM wp_posts WHERE id IN (SELECT comment_post_id FROM wp_comments);

or

SELECT A.* FROM wp_posts A INNER JOIN
(SELECT comment_post_id FROM wp_comments) B
ON A.id = B.comment_post_id;


Approaches to prevent database slowdown when another process is hitting it hard

As others have suggested, the problem you've outlined is very open-ended.  However, I can offer one general solution which you may not have considered: break up the intensive operation into smaller batches.  This will keep locks brief, reducing the risk of deadlocks, may reduce the amount of memory and temp space used, and may give the server enough headroom to cope while you do root cause analysis.
For example, if your overnight operation deletes a large number of records, you can instead run DELETE TOP (10000) FROM X WHERE [blah blah] in a loop while @@NUMRECORDS > 0.  You could throttle this by inserting a delay between executions.  Note that the total CPU cycles used will probably be greater with this approach; possibly dramatically greater, depending on the details of the queries involved.  If your server is short on memory, this approach could lead to thrashing your buffer pool (the cache recently-read or -modified records, held in memory when possible).  Test carefully before promoting any change to production, and be ready to halt and revert if it negatively impacts performance.


TRANSACTION ISOLATION LEVEL SNAPSHOT vs. TRUNCATE?

The list of 'DDL' operations listed is not comprehensive (and TRUNCATE TABLE is not the only omission from that list). Whether TRUNCATE TABLE is DML or DDL is a fraught question in SQL Server, with persuasive examples on both sides of the debate, and entries both ways in Books Online.

From the point of view of a snapshot isolation transaction, truncate has the essential quality of taking a Sch-M lock, which explains the blocking ( because RCSI and SI still acquire Sch-S locks); and it also bumps the internal metadata version (for internal reasons*) resulting in error 3961.

So, the behaviour you are seeing is expected, just not documented very well.

* The current implementation of TRUNCATE TABLE does not generate row versions. Bumping the metadata version is the simplest way to ensure correct behaviour.


What percentage of the table is_active normally?

The following should avoid having to do massive updates to flip that flag:

Consider abandoning the is_active flag in the main table.  Instead have a separate table with PRIMARY KEY matching the main table, plus (optionally) a timestamp (I'll get back to that in a minute).

Instead of setting is_active, INSERT a row in the new table.

Instead of turning off is_active, DELETE the row.

To check for [in]active, use LEFT JOIN plus WHERE id IS [NOT] NULL.

The TIMESTAMP is to deal with programming errors that insert a row but somehow forget about it.  Also add a user_id column for debugging, if relevant.


Is there a way to allow a proxy connection to use passed authentication in a public database link?

No, in Oracle 11.2.0.2 proxy connections cannot use passed authentication in a public database link.  Switch to a private database link with specified authentication.


Can an active Postgres serverlog be truncated?

By default PostgreSQL logs to stderr, from PostgreSQL Documentation 18.8. Error Reporting and Logging:

  PostgreSQL supports several methods for logging server messages, including stderr, csvlog and syslog. On Windows, eventlog is also supported. Set this parameter to a list of desired log destinations separated by commas. The default is to log to stderr only. This parameter can only be set in the postgresql.conf file or on the server command line.

If you just direct stderr to file according to the documentation this means you have to stop and restart the server to truncate, 23.3. Log File Maintenance:

  If you simply direct the stderr of postgres into a file, you will have log output, but the only way to truncate the log file is to stop and restart the server. This might be acceptable if you are using PostgreSQL in a development environment, but few production servers would find this behavior acceptable.

About manually deleting the file, take a look at this thread from PostgreSQL mailing lists (from year 2010):

  What platform?  If in Unix/Linux, just:
  cat /dev/null > postgres_logfile
  or
  postgres_logfile
  Most running programs don't like it when you delete a file with an open file handle but you it doesn't mind if you clear it on the fly.  Not sure how to do this in a Windows environment.

And finally here's a good blog post more generally about PostgreSQL logs.


How to grant insert in a table that is in another database on SQL Server 2008 R2?

Indeed what I was looking for (but were unaware of its name) is called cross database ownership chaining.

By reading the article Understanding Cross Database Ownership Chaining in SQL Server  I was able to solve my problem.

As a reference, below is what I did:

Use [DatabaseA]
GO

ALTER DATABASE [DatabaseA]
SET DB_CHAINING ON
GO

Use [DatabaseB]
GO
ALTER DATABASE [DatabaseB]
SET DB_CHAINING ON
GO

EXEC sp_grantdbaccess 'UserB';
GO

GRANT SELECT, UPDATE on [DatabaseB].[dbo].[TableA] TO [UserB]
GO

Thank you all.


How to limit an inner query by outer query attribute

First, if you SELECT only from the teams table and there is a FOREIGN KEY constraint that REFERENCES players, you don't need the join to players at all.

Now, there are various ways to write this type of queries, there is even a tag at SO and DBA.SE, greatest-n-per-group. We need the greatest 1 in this case. The most simple code is to use the DISTINCT ON construct (which is a Postgres, non-standard addition to SQL).
If you need columns from players, you can simply join and add the columns in the select list.:

SELECT DISTINCT ON (t.player_id)
  t.player_id,
  t.team_name,
  t.start_date,
  t.end_date
FROM teams AS t
  -- JOIN players AS p
  --   ON p.id = t.player_id
ORDER BY
    t.player_id,  -- this needs to match the DISTINCT ON ()
    ....      -- pick what order you want, so the 1st is chosen for each player
 ;            -- no LIMIT

Another way that works in most other DBMS is to use window functions. The criteria for which row to be chosen goes inside the OVER clause:

SELECT
    g.player_id,
    g.team_name,
    g.start_date,
    g.end_date
FROM
  ( SELECT
      t.player_id,
      t.team_name,
      t.start_date,
      t.end_date,
      ROW_NUMBER() OVER (PARTITION BY t.player_id
                         ORDER BY ...) AS rn
    FROM teams AS t
      -- JOIN players AS p
      --   ON p.id = t.player_id
  ) AS g
WHERE
    g.rn = 1 ;

If you are not in some ancient version of Postgres, there is also the LATERAL syntax, that is often the most efficient. Especially when there is a "driving" table with the distinct values we want to base the grouping (the players here) and if these values are relatively small in number and have many possible options (i.e. in this case, the biggest the teams table is, in comparison to the players, the better for this query).
Also notice how this query resembles very much your initial idea. It does, literally, exactly what you want: allow the players columns to be referenced inside the lt subquery:

SELECT
    lt.player_id,
    lt.team_name,
    lt.start_date,
    lt.end_date
FROM players AS p
  JOIN LATERAL
    ( SELECT t.*
      FROM teams AS t
      WHERE p.id = t.player_id
      ORDER BY ....     -- pick the order
      LIMIT 1
   ) AS lt ON TRUE ;


Curious About SQL Server Registry Entries

Instead of reinventing the wheel (there is a cost associated to it), use SQL PowerDoc written by  kendalvandyke. Its a powershell based solution.

Documentation can be found here.

  I have noticed that several of my SQL servers don't have the required registration data saved within the registry.

SQL server will have everything in the registry settings. For shared Features, I believe the registry is HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Installer\UserData\S-1-518\Components\0D1F366D0FE0E404F8C15EE4F1C15094

Refer to File Locations for Default and Named Instances of SQL Server


Need to grant select permission on View but not in table which is in a different database

I will give a full demo how you can achieve this.
Create 2 database

USE [MASTER];
GO
CREATE DATABASE [TestA]
GO
CREATE DATABASE [TestB]
GO

Create a login and user in both database.

USE [master];
GO
CREATE LOGIN TestUser WITH PASSWORD = 'Password', CHECK_POLICY = OFF;
GO

Create user in both database using the login above.

USE TestA
GO
CREATE USER TestUser FROM LOGIN TestUser;
GO
USE TestB
GO
CREATE USER TestUser FROM LOGIN TestUser;
GO

Create table is TestA database.

CREATE TABLE TestA.dbo.t1(id INT);
GO

Create view in TestB database.

USE TestB;
GO
CREATE VIEW dbo.ViewA
AS
  SELECT id FROM TestA.dbo.t1;
GO

Enabling Cross-database Ownership Chaining

ALTER DATABASE testA SET DB_CHAINING ON;
ALTER DATABASE testB SET DB_CHAINING ON;

Grant permission to read from the view.

USE [TestB]
GO
GRANT SELECT ON [dbo].[ViewA] TO [TestUser];
GO

Login with TestUser into SSMS and run this:

USE [TestB]
GO
SELECT * FROM [dbo].[ViewA]
USE [TestA]
GO
SELECT * FROM [dbo].[t1]
GO

You should see all the rows from testA.dbo.ViewA table via the view but get following error message for the select on table t1.

  Msg 229, Level 14, State 5, Line 6 The SELECT permission was denied on
  the object 't1', database 'TestA', schema 'dbo'.


Promoting warm standby to master with Postgresql 9.4 using streaming replication

Solved it. The issue was that I had Monit running in the background which was picking up the backup database before I had written recovery.conf (the servers are being provisioned by chef).

The solution was to pass the -R flag to pg_basebackup in order to ensure the database was never started outside of recovery mode which caused it to be incompatible with the WALs and streaming replication from the master.


MySQL subqueries that use range based on values of main queries don't use indices properly

This small data sample doesn't serve to illustrate that the behavior you are attempting to identify exists.  Indeed, I've tested it on a larger data set and it did use the Index (using MySQL 5.5.30).

The problem is that when the optimizer determines that using an index would result in an inordinately large number of matches -- compared to the total number of rows in the table -- it won't use an index, because that could actually perform worse than simply scanning the whole table, and it will exhibit exactly the behavior this example illustrates... it knows the index is a candidate, but it chooses not to use it.

But I would suggest the problem lies in the fact that you're using a subquery in a place where a subquery isn't really necessary or called for.

I rewrote this as a join, because, from what I can tell, this is what you're asking the database to do: join each row in Person to the matching row(s) in CofeeBreaks where that person took their break during that window, and average the ages of the attendees.

I also built this here on SQL Fiddle.  Removed TEMPORARY from the table definitions because the Fiddle doesn't seem to support them properly (because it probably uses a connection pool).

SELECT cb.id,
       cb.cofeeBreakStart,
       cb.cofeeBreakEnd,
       avg(p.age)
  FROM CofeeBreaks cb
  JOIN Person p ON p.lastCofee BETWEEN cb.cofeeBreakStart AND cb.cofeeBreakEnd
 GROUP BY cb.id;

EXPLAIN SELECT on this subquery shows that the index is being used, even on this small data set... although if you change that to a LEFT JOIN (which would show all coffee breaks even if nobody took that particular break, while the JOIN only includes breaks where at least one person did), the index shows up as being a candidate, but doesn't get used... again, likely because of the cost, and this behavior would likely be different with a larger data set.

The LEFT JOIN version of the query would produce identical results to your subquery regardless of the table data, while the JOIN version only produces identical results if every CofeeBreak had at least one person taking that break, which in your sample data, it does.

But using the indexes or not, a correlated subquery will not usually scale as well as a join.

http://dev.mysql.com/doc/refman/5.5/en/rewriting-subqueries.html


Disk Read IO Throttle not showing in perfmon Windows Server 2012 r2

What you are referring is not related to windows. It's for *NIX systems.

For windows, you should monitor

•\LogicalDisk\Avg. Disk Sec/Read
•\LogicalDisk\Avg. Disk Sec/Write
•\LogicalDisk\Disk Bytes/Sec
•\LogicalDisk\Disk Reads/Sec
•\LogicalDisk\Disk Writes/Sec
•\LogicalDisk\Split IO/sec
•\LogicalDisk\Disk Transfers/sec

You can use PAL to analyze the .blg file for any bottlenecks.

Check : http://blogs.technet.com/b/cotw/archive/2009/03/18/analyzing-storage-performance.aspx


Preferred Methods of copying a new database from Development to Production

I'd say the best way (in my opinion, of course) is your 2nd option, SSMS (Generate Scripts).

The reason I say to script out your database is because you will have save-able scripts that you can throw into version control.  If you are only looking for the schema and no data, it's pretty simple to generate.

As for backup and restore, I agree with you.  It's a good way to port databases, but if you need to go through the pain of having a script to remove existing development data, then you are having to go that extra mile (not to mention the possibility of "user error" and not deleting all data, allowing non-prod data in production).

For the third option, writing the T-SQL yourself, that is a very manual process.  Might as well take advantage of SSMS' ability to generate a schema script for your entire database.


In Between causes my query to return "wrong" values

Your query:

SELECT resources.*, chunks.x, chunks.y
FROM resources, chunks
where chunk_ID in (SELECT ID
                   FROM chunks
                   where x between 17185-1 and 17185+1
                     and y between 10846-1 and 10846+1)
group by ID

I see several issues:

The two tables in the FROM clause are not joined (that's the reason for the irrelevant results you get, the two tables are correlated through the subquery* but the FROM clause introduces an additional non-correlated chunks reference which produces all these unrelated results):

FROM resources, chunks

Join them:

FROM resources JOIN chunks ON resources.chunk_id = chunks.id

Since chunks is in the FROM clause, we don't need* the subquery no more, to check x and y:

where chunk_ID in (SELECT ID
                   FROM chunks
                   where x between 17185-1 and 17185+1
                     and y between 10846-1 and 10846+1)

Simplify WHERE:

WHERE chunks.x BETWEEN 17185-1 AND 17185+1
  AND chunks.y BETWEEN 10846-1 AND 10846+1

You have SELECT * combined with GROUP BY. This is a recipe for spurious, wrong results.

Just remove the GROUP BY.

The query becomes after using all the above comments (and some polishing with table aliases):

-- Suggested query
SELECT r.*, c.x, c.y
FROM resources AS r
     JOIN chunks AS c
     ON r.chunk_id = c.id
WHERE c.x BETWEEN 17185-1 AND 17185+1
  AND c.y BETWEEN 10846-1 AND 10846+1 ;

* Just for completeness, we could have kept the IN subquery and remove chunks from the FROM clause.

This would have the restriction that we couldn't have any column from chunks in the SELECT list - and more likely worse performance than the JOIN query:

-- Don't use this one
SELECT r.*
FROM resources AS r
WHERE r.chunk_d IN
      ( SELECT c.id
        FROM chunks AS c
        WHERE c.x BETWEEN 17185-1 AND 17185+1
          AND c.y BETWEEN 10846-1 AND 10846+1
      ) ;


Possibly the --no-constraints option to csvsql may solve that problem simply.

Otherwise COPY has a PROGRAM clause that can pre-process the data. It receives the lines it on its standard input and must output the filtered result on its standard output.

https://www.postgresql.org/docs/current/static/sql-copy.html

  PROGRAM

  A command to execute. In COPY FROM, the input is read from standard output of the command, and in COPY TO, the output is written
  to the standard input of the command.

When called from psql with \copy tablename FROM PROGRAM ... the program refers to a client-side command to launch by psql. When COPY is used, it's a server-side command run by the backend.

But the trimming itself is non-trivial. For instance, see How to manipulate a CSV file with sed or awk? and Trim leading and trailing spaces from a string in awk.


Why can't we write ddl statement directly into the PL/SQL block

As it says in the documentation:

Only dynamic SQL can execute the following types of statements within PL/SQL program units:

Data definition language (DDL) statements such as CREATE, DROP, GRANT, and REVOKE

A TRUNCATE operation is DDL.
When using EXECUTE IMMEDIATE, remember that any DDL operations you execute will implicitly COMMIT the current transaction.


Sort one column , based on that sort another column in Oracle

order by to_char(date_column, 'YYYY') desc, counter desc

order by trunc(date_column, 'YYYY') desc, counter desc

drop table t1 purge;
create table t1 (counter varchar2(2), date_column date);
insert into t1 values ('05', date'2019-07-23');
insert into t1 values ('01', date'2017-06-23');
insert into t1 values ('10', date'2019-06-02');
insert into t1 values ('14', date'2016-12-01');
insert into t1 values ('03', date'2018-11-23');
insert into t1 values ('07', date'2015-11-23');
commit;

select * from t1 order by trunc(date_column, 'YYYY') desc, counter desc;

CO DATE_COLUM
-- ----------
10 02-06-2019
05 23-07-2019
03 23-11-2018
01 23-06-2017
14 01-12-2016
07 23-11-2015


Date pattern regex doesn't match the records in PostgreSQL query?

PostgreSQL does not implement Perl regular expression, it implements POSIX regular expressions.  One difference among many is that \b represents a backspace character, not a word boundary. You can use \y to represent a word boundary, although I don't know if that has the exact same meaning as \b has in Perl.

If you want to use Perl regular expressions exactly, you could use the PL/Perl language


That looks like a snippet from the definition of the sys.sp_spaceused system stored procedure.

  regularly having very high wait stats

What are the waits pertaining to the execution of this stored procedure?

I'd be even more interested to find out why sys.sp_spaceused is running so frequently that it is negatively affecting performance.  That stored procedure isn't typically going to be run regularly, or frequently if regularly.

Depending on what version of SQL Server you are using, there are a few ways to find out who (host name, application name, etc.) is kicking this stored procedure off.  My favorite way to do this would be through the use of Extended Events (if you are only SQL Server 2008 or higher.  You haven't specified version in your question as of now).


I assume that X refers to the same table row everywhere.

In that case, the two transactions in your picture are not serializable, and indeed a lost update would occur. Any serial execution of the transactions would produce a different result from the nested execution you are showing.

To clarify the term "serializable", here is how the SQL standard defines it:

  The execution of concurrent SQL-transactions at isolation level SERIALIZABLE is guaranteed to be serializable.
  A serializable execution is defined to be an execution of the operations of concurrently executing SQL-transactions that produces the same effect as some serial execution of those same SQL-transactions. A serial execution
  is one in which each SQL-transaction executes to completion before the next SQL-transaction begins.

Wikipedia has this definition for view-serializable:

  View-serializability of a schedule is defined by equivalence to a serial schedule (no overlapping transactions) with the same transactions, such that respective transactions in the two schedules read and write the same data values ("view" the same data values).

That seems mostly equivalent, except that the SQL standard only talks about the effects, while Wikipedia talks about reading values as well. But if you read a value and that has no effect, you might as well not read it, so the distinction is mostly philosophical.

And the schedule in your question satisfies neither of these definitions.


Some help with a crosstab query in postgresql

You don't need the GROUP BY in a pivot... And, if you put that WHERE clause in there you're filtering out stuff you need.

SELECT *
FROM crosstab(
  $$
    SELECT game.team_name::text, game.match_id, game.sections_won
    FROM game
    ORDER by 1,2
 $$
  ) as ct("Team" text, "Match 6" int4 ,"Match 7" int4 ,"Match 8" int4 ,"Match 19" int4)
;

  Team  | Match 6 | Match 7 | Match 8 | Match 19
--------+---------+---------+---------+----------
 Team A |       4 |       4 |       4 |        4
 Team B |       1 |       1 |       1 |        1
(2 rows)

As to why you need the ORDER BY, from the docs

  In practice the SQL query should always specify ORDER BY 1,2 to ensure that the input rows are properly ordered, that is, values with the same row_name are brought together and correctly ordered within the row. Notice that crosstab itself does not pay any attention to the second column of the query result; it's just there to be ordered by, to control the order in which the third-column values appear across the page.


Selecting rows conflicting values in WHERE clause

The answer: as ypercube said, the solution is:

adding parentheses around the OR conditions

like so:

SELECT name,url FROM additions WHERE enabled = 1 AND picture = 1 AND (name LIKE '%value%' OR tags LIKE '%value%' OR subtext LIKE '%value%');

With the parentheses, AND will take precedence and the entire section in the LIKE value comparison will have to be true together with the other evaluations, like enabled = 1 and picture = 1. Without the parentheses the OR statements are evaluated individually (and if one of them happens to be true, a row will be selected).


For anyone who stumbles here with the same question. An empty cell is not the same as a NULL cell. Empty cells are '' not NULL!


In a MySQL cursor, will it be possible to skip an iteration or row of the cursor?

Cursors can usually be turned into queries.  Programming SQL with cursors in avoiding the power of SQL.

But first, a lesson on ordering.  A "table" is unordered.  Doing a SELECT gives you no guarantee of what the rows will be delivered in -- unless you have an ORDER BY clause.

Is this what you are trying to do?  "Fetch some rows from a combination of tables, but stop when some condition is met."?

If, instead, you need to "Fetch all the rows from a combination of tables, except the rows that meet some condition", then a single query like this will suffice:

SELECT ...
    FROM a JOIN b ...
    WHERE NOT EXISTS ( SELECT 1 FROM SER_LEGAL.EVALUACION_has_ARTICULO WHERE ... )

No cursor needed.

If you really need to stop at the "first" occurrence of such, then provide the ORDER BY so we can discuss further.


What you are looking for is in fact possible. What you are looking to do (like many businesses) is build an automatic failover which is often called a hot standby. A hot standby is just a backup slave server that can be switched to either automatically or manually when you need to do maintenance or if your master server fails.

MySQL has some great documentation on setting that up here

You may also need to take a look at replication methods for replicating your data to the slave.


How do I set up a Derived Column Transformation to get row number in SSIS 2014?

A derived column won't be able to add a row number. Well, it can add a column called RowNum but the expression language doesn't support the ability to update a value on the fly. Instead, you'll need a Script Component.

You can search for "SSIS generate surrogate key" and find plenty of reference implementations. I'm going to borrow from Joost's post Create a Row Id for this answer

// C# code: surrogate key script
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;

[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{
    // New internal variable to store the rownumber
    private int rowCounter = 0;

    // Method that will be started for each record in you dataflow
    public override void Input0_ProcessInputRow(Input0Buffer Row)
    {
        // Seed counter
        rowCounter++;

        // Fill the new column
        Row.RowNum = rowCounter;
    }
}

For those on 2005, the approach would look something like

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

<Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute> _
<CLSCompliant(False)> _
Public Class ScriptMain
    Inherits UserComponent

    ' New internal variable to store the rownumber
    Private rowCounter As Integer = 0

    ' Method that will be started for each record in you dataflow
    Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
        'Seed counter
        rowCounter = rowCounter + 1

        ' Fill the new column
        Row.RowNum = rowCounter
    End Sub
End Class

And since I love to provide Biml based answers so you can use the following code, also from Joost Creating BIML Script Component Transformation (rownumber)

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
 <Annotations>
  <Annotation>
   File: Script Component Transformation RowNumber.biml
   Description: Example of using the Script Component as
   a transformation to add a rownumber to the destination.
   Note: Example has an OLE DB Destination that supports
   an identity column. Use your own Flat File, Excel or
   PDW destination that doesn't supports an identity.
   VS2012 BIDS Helper 1.6.6.0
   By Joost van Rossum http://microsoft-ssis.blogspot.com
  </Annotation>
 </Annotations>

 <!--Package connection managers-->
    <Connections>
            <OleDbConnection
                Name="Source"
                ConnectionString="Data Source=.;Initial Catalog=ssisjoostS;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;">
            </OleDbConnection>
            <OleDbConnection
                Name="Destination"
                ConnectionString="Data Source=.;Initial Catalog=ssisjoostD;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;">
            </OleDbConnection>
       </Connections>

       <ScriptProjects>
             <ScriptComponentProject ProjectCoreName="sc_c253bef215bf4d6b85dbe3919c35c167.csproj" Name="SCR - Rownumber">
                    <AssemblyReferences>
                           <AssemblyReference AssemblyPath="Microsoft.SqlServer.DTSPipelineWrap" />
                           <AssemblyReference AssemblyPath="Microsoft.SqlServer.DTSRuntimeWrap" />
                           <AssemblyReference AssemblyPath="Microsoft.SqlServer.PipelineHost" />
                           <AssemblyReference AssemblyPath="Microsoft.SqlServer.TxScript" />
                           <AssemblyReference AssemblyPath="System.dll" />
                           <AssemblyReference AssemblyPath="System.AddIn.dll" />
                           <AssemblyReference AssemblyPath="System.Data.dll" />
                           <AssemblyReference AssemblyPath="System.Xml.dll" />
                    </AssemblyReferences>
                    <ReadOnlyVariables>
                           <Variable VariableName="maxrownumber" Namespace="User" DataType="Int32"></Variable>
                    </ReadOnlyVariables>
                    <Files>
       <!-- Left alignment of .Net script to get a neat layout in package-->
                           <File Path="AssemblyInfo.cs">
using System.Reflection;
using System.Runtime.CompilerServices;

//
// General Information about an assembly is controlled through the following
// set of attributes. Change these attribute values to modify the information
// associated with an assembly.
//
[assembly: AssemblyTitle("SC_977e21e288ea4faaaa4e6b2ad2cd125d")]
[assembly: AssemblyDescription("")]
[assembly: AssemblyConfiguration("")]
[assembly: AssemblyCompany("SSISJoost")]
[assembly: AssemblyProduct("SC_977e21e288ea4faaaa4e6b2ad2cd125d")]
[assembly: AssemblyCopyright("Copyright @ SSISJoost 2015")]
[assembly: AssemblyTrademark("")]
[assembly: AssemblyCulture("")]
//
// Version information for an assembly consists of the following four values:
//
//      Major Version
//      Minor Version
//      Build Number
//      Revision
//
// You can specify all the values or you can default the Revision and Build Numbers
// by using the '*' as shown below:

[assembly: AssemblyVersion("1.0.*")]
                           </File>
       <!-- Replaced greater/less than by &gt; and &lt; -->
                           <File Path="main.cs">#region Namespaces
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
#endregion

/// &lt;summary&gt;
/// Rownumber transformation to create an identity column
/// &lt;/summary&gt;
[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{
 int rownumber = 0;

 /// &lt;summary&gt;
 /// Get max rownumber from variable
 /// &lt;/summary&gt;
 public override void PreExecute()
 {
  rownumber = this.Variables.maxrownumber;
 }

 /// &lt;summary&gt;
 /// Increase rownumber and fill rownumber column
 /// &lt;/summary&gt;
 /// &lt;param name="Row"&gt;The row that is currently passing through the component&lt;/param&gt;
 public override void Input0_ProcessInputRow(Input0Buffer Row)
 {
  rownumber++;
  Row.rownumber = rownumber;
 }
}
                           </File>
                    </Files>
                    <InputBuffer Name="Input0">
                           <Columns>
                           </Columns>
                    </InputBuffer>
                    <OutputBuffers>
                           <OutputBuffer Name="Output0">
                                  <Columns>
                                        <Column Name="rownumber" DataType="Int32"></Column>
                                  </Columns>
                           </OutputBuffer>
                    </OutputBuffers>
             </ScriptComponentProject>
       </ScriptProjects>

       <Packages>
             <!--A query to get all tables from a certain database and loop through that collection-->
             <# string sConn = @"Provider=SQLNCLI11.1;Server=.;Initial Catalog=ssisjoostS;Integrated Security=SSPI;";#>
             <# string sSQL  = "SELECT name as TableName FROM dbo.sysobjects where xtype = 'U' and category = 0 ORDER BY name";#>
             <# DataTable tblAllTables = ExternalDataAccess.GetDataTable(sConn,sSQL);#>
             <# foreach (DataRow row in tblAllTables.Rows) { #>

             <!--Create a package for each table and use the tablename in the packagename-->
             <Package ProtectionLevel="DontSaveSensitive" ConstraintMode="Parallel" AutoCreateConfigurationsType="None" Name="ssisjoost_<#=row["TableName"]#>">
                    <Variables>
                           <Variable Name="maxrownumber" DataType="Int32">0</Variable>
                    </Variables>

                    <!--The tasks of my control flow: get max rownumber and a data flow task-->
                    <Tasks>
                    <!--Execute SQL Task to get max rownumber from destination-->
                    <ExecuteSQL
                           Name="SQL - Get max rownumber <#=row["TableName"]#>"
                           ConnectionName="Destination"
                           ResultSet="SingleRow">
                           <DirectInput>SELECT ISNULL(max([rownumber]),0) as maxrownumber FROM  <#=row["TableName"]#></DirectInput>
                           <Results>
                           <Result Name="0" VariableName="User.maxrownumber" />
                           </Results>
                    </ExecuteSQL>

                    <!--Data Flow Task to fill the destination table-->
                    <Dataflow Name="DFT - Process <#=row["TableName"]#>">
                    <!--Connect it to the preceding Execute SQL Task-->
                    <PrecedenceConstraints>
                           <Inputs>
                                  <Input OutputPathName="SQL - Get max rownumber <#=row["TableName"]#>.Output"></Input>
                           </Inputs>
                    </PrecedenceConstraints>

                    <Transformations>
                    <!--My source with dynamic, but ugly * which could be replace by some .NET/SQL code retrieving the columnnames-->
                    <OleDbSource Name="OLE_SRC - <#=row["TableName"]#>" ConnectionName="Source">
                           <DirectInput>SELECT * FROM <#=row["TableName"]#></DirectInput>
                    </OleDbSource>

                    <ScriptComponentTransformation Name="SCR - Rownumber">
                           <ScriptComponentProjectReference ScriptComponentProjectName="SCR - Rownumber" />
                    </ScriptComponentTransformation>

                    <!--My destination with no column mapping because all source columns exist in destination table-->
                    <OleDbDestination Name="OLE_DST - <#=row["TableName"]#>" ConnectionName="Destination">
                           <ExternalTableOutput Table="<#=row["TableName"]#>"></ExternalTableOutput>
                    </OleDbDestination>
                    </Transformations>
                    </Dataflow>
                    </Tasks>
             </Package>
             <# } #>
       </Packages>
       </Biml>

<!--Includes/Imports for C#-->
<#@ template language="C#" hostspecific="true"#>
<#@ import namespace="System.Data"#>
<#@ import namespace="System.Data.SqlClient"#>


MySQL my.cnf performance tuning recommendations

Your innodb_buffer_pool_size is almost absurdly small for a server of that size.
innodb_flush_log_at_trx_commit=2  is dangerous.
innodb_file_per_table=1 you definitely don't want commended out. Rebuild any tables that aren't in separate tablespaces.
query_cache_limit=1G makes no sense with query cache disabled.
Various buffer sizes other than innodb_buffer_pool_size - you should probably leave them at default settings. Comment them out. Especially join_buffer_size and sort_buffer_size.
Do you use MyISAM? If not, reduce key_buffer_size down to something tiny (enough to cover the size of all .MYI files in your database.


Created user can access all databases in PostgreSQL without any grants

At the SQL level, every user can indeed connect to a newly created database, until the following SQL command is issued:

REVOKE connect ON DATABASE database_name FROM PUBLIC;

Once done, each user or role that should be able to connect has to be granted explicitly the connect privilege:

GRANT connect ON DATABASE database_name TO rolename;

Edit:
In a multi-tenant scenario, more than just the connect privilege would be removed. For multi-tenancy tips and best practices, you may want to read on the postgresql public wiki:  Shared Database Hosting and Managing rights in PostgreSQL.


How can I mysqldump all databases except the mysql schema?

You need to collect all the database names into a space delimited list. Use that for mysqldump

MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
#
# Collect all database names except for
# mysql, information_schema, and performance_schema
#
SQL="SELECT schema_name FROM information_schema.schemata WHERE schema_name NOT IN"
SQL="${SQL} ('mysql','information_schema','performance_schema')"

DBLISTFILE=/tmp/DatabasesToDump.txt
mysql ${MYSQL_CONN} -ANe"${SQL}" > ${DBLISTFILE}

DBLIST=""
for DB in `cat ${DBLISTFILE}` ; do DBLIST="${DBLIST} ${DB}" ; done

MYSQLDUMP_OPTIONS="--routines --triggers --single-transaction"
mysqldump ${MYSQL_CONN} ${MYSQLDUMP_OPTIONS} --databases ${DBLIST} > all-dbs.sql

Give it a Try !!!


Process to change collation on a database

Well, it could be very simple, or it could have lots of complications. There are quite a few restrictions in place that will prevent changing a database's default collation

Please see the following post of mine that goes into great detail regarding what each level of collation affects, and what you might run into and need to look out for, and what to do about those things:

Changing the Collation of the Instance, the Databases, and All Columns in All User Databases: What Could Possibly Go Wrong?

Your approach could be completely valid. If it seems to work, then possibly it does. Just go through each of the points in my post to make sure that you have looked in all of the places that need to be checked. For example, are you just changing the collation of a single database? If so, what is the instance-level collation? It is one of the two that you have mentioned? There could be some odd behavior if the database-level and instance-level collations don't match, but not necessarily.

Just out of curiosity: why are you wanting to use SQL_Latin1_General_CP437_CI_AI. Seems like a potentially odd choice. Does the app that uses that DB really need code page 437? Is this, by any chance, for JIRA? I only ask because that is one of the two "supported" collations for JIRA, and it's not all that often that someone is wanting to use that particular collation. If this is the case, then you likely don't need to be making this change. Please see my recommendation to Atlassian here regarding this issue:

Collation error in Jira when the database and server/default collation do not match in SQL Server

Long support ticket short: Atlassian did themselves and their customers a huge disservice by coming up with "supported collations" in the first place. Thus far, no proof has been given for why these two were chosen, and why others are not "supported". I am 99.99999% sure that this is merely an issue of their developers not understanding collations (or SQL Server collations), which is nothing against their developers as Jira is kinda awesome, it's just that most folks don't understand this stuff. My recommendation to them is that they support the following:

  The database in which Jira is installed must have a case-insensitive collation that is the same as the instance-level collation.

whether or not the collation is accent-insensitive is up to you and how you want sorting and comparisons to be done. In the end, just make sure that the instance and DB have the same collation. (if you were about to do all of this work only to change the DB to a collation that they "support" yet is different than the instance-level collation, then you would have broken Jira by trying to comply with their (most likely unnecessary) stated requirement  ;-).

My advice: don't change anything! You even said:

  a database that has been in production for a few years

If it has been in production for years without running into problems, then there are likely no problems :-)


Migrating development and production databases to AWS

One of my customers is running their entire production site up on EC2.  They've had pretty good luck with it, except for when there is some sort of Amazon caused outage (which there have been a couple of over the last couple of years).

The biggest thing that you need to worry about is planning for an EC2 site failure with your application.  If EC2 has a major failure are you ok with the database being offline for a couple of days, or possibly loosing all the data in the database?  If not then you'll need to handle that via traditional SQL Server DR planning by having another server running as a hot standby server in another Amazon data center.  The same goes for the web servers and anything else that hits the database.

The slowest part of using EC2 is the storage.  There are ways around this by getting lots of volumes from Amazon and striping across them within the VM.

As for the dev machine, that's pretty easy.  Just toss it up there and it should be fine as long as you've got backups going somewhere.


How can I get top users in specific tags?

Let's assume these are the settings for the problem. These will be the tables:

CREATE TABLE users
(
    user_id INTEGER NOT NULL PRIMARY KEY
) ;

CREATE TABLE tags
(
    id INTEGER NOT NULL PRIMARY KEY,
    name VARCHAR(100)
) ;

CREATE TABLE qanda_tags
(
    question_id INTEGER,
    tag_id INTEGER NOT NULL REFERENCES tags(id)
) ;

CREATE TABLE reputations
(
    owner_id INTEGER NOT NULL REFERENCES users(user_id),
    question_id INTEGER,
    score INTEGER NOT NULL DEFAULT 0
) ;

And these are the values stored on the different tables:

INSERT INTO users (user_id)
VALUES (1), (2) ;

INSERT INTO tags
    (id, name)
VALUES
    (1, 'MySQL'),
    (2, 'PostgreSQL'),
    (3, 'MS SQL Server'),
    (4, 'Informix'),
    (5, 'MongoDB'),
    (6, 'CouchDB'),
    (7, 'Oracle'),
    (8, 'DB2'),
    (9, 'Sybase') ;

INSERT INTO qanda_tags
    (question_id, tag_id)
VALUES
    (1, 1),
    (1, 2),
    (2, 4),
    (2, 5),
    (3, 1),
    (3, 2),
    (3, 4);

INSERT INTO reputations
    (owner_id, question_id, score)
VALUES
    (1, 1, 3),
    (2, 2, 5),
    (1, 3, 8) ;

At this point, we could run this query...

SELECT
    *
FROM
    reputations r
    JOIN qanda_tags qt
        ON qt.question_id = r.question_id
    JOIN tags t
        ON t.id = qt.tag_id ;

and get

owner_id | question_id | score | question_id | tag_id | id | name
-------: | ----------: | ----: | ----------: | -----: | -: | :---------
       1 |           1 |     3 |           1 |      1 |  1 | MySQL
       1 |           1 |     3 |           1 |      2 |  2 | PostgreSQL
       2 |           2 |     5 |           2 |      4 |  4 | Informix
       2 |           2 |     5 |           2 |      5 |  5 | MongoDB
       1 |           3 |     8 |           3 |      1 |  1 | MySQL
       1 |           3 |     8 |           3 |      2 |  2 | PostgreSQL
       1 |           3 |     8 |           3 |      4 |  4 | Informix

Your original function (slightly modified so it is no actually a function and can be played on at dbfiddle, and with some extra info), would do the following:

(these would be your function parameters)

SET @user_id = 1 ;
SET @tags_num = 4 ;

SELECT
    substring_index(
        group_concat(name_and_score ORDER BY x.tag_score DESC, x.name ASC SEPARATOR ', ')
        , ', '
        , @tags_num
    ) AS top_tags_from_user
FROM
   (SELECT
        t.name,
        sum(r.score) AS tag_score,
        concat(t.name, ' (', sum(r.score), ')') AS name_and_score
    FROM
        reputations r
        JOIN qanda_tags qt
            ON qt.question_id = r.question_id
        JOIN tags t
            ON t.id = qt.tag_id
    WHERE
        r.owner_id = @user_id
    GROUP BY
        t.name
    ) AS x ;

| top_tags_from_user                        |
| :---------------------------------------- |
| MySQL (11), PostgreSQL (11), Informix (8) |

If you want to limit the tags that are checked, you can change the previous code just slightly:

SET @user_id = 1 ;
SET @tags_num = 4 ;
SET @tags_list = 'MySQL,Informix' ;

SELECT
    substring_index(
        group_concat(name_and_score ORDER BY x.tag_score DESC, x.name ASC SEPARATOR ', ')
        , ', '
        , @tags_num
    ) AS top_tags_from_user
FROM
   (SELECT
        t.name,
        sum(r.score) AS tag_score,
        concat(t.name, ' (', sum(r.score), ')') AS name_and_score
    FROM
        reputations r
        JOIN qanda_tags qt
            ON qt.question_id = r.question_id
        JOIN tags t
            ON t.id = qt.tag_id
               /* This is just what you need to add */
               AND (@tags_list IS NULL OR find_in_set(t.name, @tags_list))
    WHERE
        r.owner_id = @user_id
    GROUP BY
        t.name
    ) AS x ;

That will now return:

| top_tags_from_user       |
| :----------------------- |
| MySQL (11), Informix (8) |

The previous code will also work and choose all tags if you set @tags_list to NULL. In that case, the @tags_list IS NULL is true, and the JOIN condition is equivalent to the original case.

You can check everything at dbfiddle here


All four data nodes in MySQL Cluster in same node group

This seems to be a bug [1] in MySQL. It happens because your data nodes are still starting. Node-group IDs should be shown correctly once they are started completely.

[1] http://bugs.mysql.com/bug.php?id=30831


Can sqlplus fetch more than one row at a time?

SET ARRAYSIZE has a default of 15

"Sets the number of rows that SQL*Plus will fetch from the database at one time.

Valid values are 1 to 5000. A large value increases the efficiency of queries and subqueries that fetch many rows, but requires more memory. Values over approximately 100 provide little added performance. ARRAYSIZE has no effect on the results of SQL*Plus operations other than increasing efficiency."

https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sqpug/SET-system-variable-summary.html#GUID-773AEA4E-060E-4C1E-B1A0-52F7DE084AE8

There are additional tweaks that can be done (including the SDU) in either the sqlnet.ora (changing the DEFAULT_SDU_SIZE ) or in the connection string. That can be defined in tnsnames.ora but you can also put it as part of a versbose connection string such as

sqlplus usr/pwd@(DESCRIPTION=(SDU=11280)(ADDRESS=(PROTOCOL=TCP)(HOST=svr)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=svc)))

Put in your appropriate values for usr,pwd,svr and svc
https://docs.oracle.com/cd/E18283_01/network.112/e10836/performance.htm


Why we use low numbers for concurrent in MySQL benchmarks or HTTP_LOAD?

It really depends on what you are modeling. For mysql there are a few different definitions for simultaneous users.

Number of database connections, including idles that only take up memory.
Number of running queries.
Number of queries active in the database engine. This is typically ignored.

With a typical web application, you may have a hundred connections open to the database, but only a handful actually running queries at any single time. You can view this number by running SHOW STATUS LIKE 'Threads_running';. Perhaps run it in a loop and see how it changes. Benchmarking the inactive connections is typically a waste as they just use some memory and not much cpu. So if your application only is running x number of concurrent queries at a time, might as well only benchmark x, even if it's a small number.


Diskbased to in memory tables sql server 2016 ram requirement

Start with the Books Online page on estimating memory requirements for in-memory OLTP. It goes into deep detail, and here's just a snippet:

  When there is an active workload, additional memory is needed to account for row versioning and various operations. How much memory is needed in practice depends on the workload, but to be safe the recommendation is to start with two times the expected size of memory-optimized tables and indexes, and observe what are the memory requirements in practice. The overhead for row versioning always depends on the characteristics of the workload - especially long-running transactions increase the overhead. For most workloads using larger databases (e.g., >100GB), overhead tends to be limited (25% or less).

Keep in mind that that's only for the in-memory OLTP data - not your query workspace, buffer pool for other objects, AG log caching, etc.


PostgresSQL dynamic execute with argument values in array

Either way, that's totally possible, given that all your parameters are of the same data type.
EXECUTE ... USING happily takes an array, which is treated as a single argument. Access elements with array subscripts.
create or replace function test_function(_filter1 text = null
                                       , _filter2 text = null
                                       , OUT retid int) as
$func$
declare
   _args text[] := ARRAY[_filter1, _filter2];
   _wher text[];
begin
   if _filter1 is not null then
      _wher := _wher || 'parameter_name = $1[1]'; -- note array subscript
   end if;

   if _filter2 is not null then
      _wher := _wher || 'parameter_name = $1[2]'; -- assign the result!
   end if;

   IF _args  IS NULL         -- check whether all params are NULL
      RAISE EXCEPTION 'At least one parameter required!';
   END IF;

   execute 'select id from mytable where ' -- cover case with all params NULL
         || array_to_string(_wher, ' or ')
         || ' ORDER BY id LIMIT 1';   -- For a single value (???)
   into  retid
   using _args;
end
$func$  language plpgsql;
This is just a proof of concept and needlessly complicated. It would be an interesting option for actual array input, for instance with a [VARIADIC function][2]. Example:

How to do WHERE x IN (val1, val2,...) in plpgsql

For the case at hand, use instead:
CREATE OR REPLACE FUNCTION test_function(_filter1 text = null
                                       , _filter2 text = null)
  RETURNS SETOF int AS
$func$
DECLARE
   _wher text := concat_ws(' OR '
             , CASE WHEN _filter1 IS NOT NULL THEN 'parameter_name = $1' END
             , CASE WHEN _filter2 IS NOT NULL THEN 'parameter_name = $2' END);
BEGIN
   IF _wher = ''   -- check whether all params are NULL
      RAISE EXCEPTION 'At least one parameter required!';
   END IF;

   RETURN QUERY EXECUTE 'SELECT id FROM mytable WHERE ' || _wher
   USING  $1, $2;
   -- USING  _filter1 , filter2; -- alternatively use func param names
END
$func$  LANGUAGE plpgsql;
Explain

List all values that can possibly be referenced in the dynamic query in the USING clause in their order of appearance. If not all of them will be referenced in the dynamic query, there's no harm in that. But we need to keep ordinal positions intact.

Note in particular that $n inside the dynamic query references given values of the USING clause by ordinal number, while $n in the USING clause references function parameters. Same syntax, different scope!
In my example $2 references $2 for simplicity. But one could reorder values in the USING clause in any fashion, so that (for instance) $2 in the dynamic query references $1 at the 2nd position in the USING clause, which references the 1st function parameter.

This allows for any number of parameters with any (heterogeneous) data types.

Returning a set of integer in this example(RETURNS SETOF int), which better fits the example - using RETURN QUERY EXECUTE accordingly.

concat_ws() is particularly handy to assemble a list of OR'ed or AND'ed predicates conditionally.


How to convert MySQL Keyword to normal column

I can see these choices

don't rename add backticks everywhere
rename the column

Like this

`Maxvalue`


How to prevent PostgreSQL from rewriting OUTER JOIN queries?

You can introduce optimization barriers for your purpose.

Preface

Plain EXPLAIN (without ANALYZE) is enough for the purpose of this question. Parentheses around the ON expression are just noise. Adding table aliases to be unambiguous.

We do see "Full Join" for the full join by itself:

SELECT * FROM tableB b FULL JOIN tableC c ON b.Bcol1 = c.Ccol1;

We could rewrite the full join with a subquery:

SELECT a.Acol1, a.Acol2, d.Bcol1, d.Bcol2, d.Ccol1, d.Ccol2
FROM   tableA a
LEFT   JOIN (
   SELECT *  -- sort out conflicting names with aliases
   FROM   tableB b FULL JOIN tableC c ON b.Bcol1 = c.Ccol1
   ) d ON a.Acol1 = d.Bcol1;

You have to sort out conflicting names with aliases in the subquery - but then again, you would need to do this in any case in the outer SELECT for multiple columns of the same name in the underlying tables.

The query is still optimized as a whole since subqueries do not impose optimization barriers. You'll still see a "Left Join" or "Right Join". However, we can expand on this form to arrive at a solution:

Solution 1. OFFSET 0 hack (undocumented)

EXPLAIN
SELECT a.Acol1, a.Acol2, d.Bcol1, d.Bcol2, d.Ccol1, d.Ccol2
FROM   tableA a
LEFT   JOIN (
   SELECT *  -- you'll have to sort out conflicting names with aliases
   FROM   tableB b FULL JOIN tableC c ON b.Bcol1 = c.Ccol1
   OFFSET 0  -- undocumented hack
   ) d ON a.Acol1 = d.Bcol1;

You'll see a "Full Join".

Why? As soon as a subquery uses an OFFSET clause, the query planner / optimiser plans the subquery separately. OFFSET 0 is logical noise, but Postgres still considers the clause which makes this a query hint to materialize the subquery, effectively. (Even though Postgres does not otherwise support query hints.) It's a much debated issue. Related:

How can I prevent Postgres from inlining a subquery?
http://blog.2ndquadrant.com/postgresql-ctes-are-optimization-fences/

Solution 2. Use a CTE (documented)

EXPLAIN
WITH cte AS (
   SELECT *  -- you'll have to sort out conflicting names with aliases
   FROM   tableB b FULL JOIN tableC c ON b.Bcol1 = c.Ccol1
   )
SELECT a.Acol1, a.Acol2, d.Bcol1, d.Bcol2, d.Ccol1, d.Ccol2
FROM   tableA a
LEFT   JOIN cte d ON a.Acol1 = d.Bcol1;

You'll see a "Full Join" as well.

The manual:

  A useful property of WITH queries is that they are evaluated only once
  per execution of the parent query, even if they are referred to more
  than once by the parent query or sibling WITH queries. Thus, expensive
  calculations that are needed in multiple places can be placed within a
  WITH query to avoid redundant work. Another possible application is to
  prevent unwanted multiple evaluations of functions with side-effects.
  However, the other side of this coin is that the optimizer is less
  able to push restrictions from the parent query down into a WITH query
  than an ordinary subquery. The WITH query will generally be evaluated
  as written, without suppression of rows that the parent query might
  discard afterwards. (But, as mentioned above, evaluation might stop
  early if the reference(s) to the query demand only a limited number of rows.)

Bold emphasis mine.

SQL Fiddle. (For Postgres 9.3, no later version available yet)


You are little modify the article example and modification contain error

@VAR1=(SELECT DISTINCT UserId FROM [IRIS].[dbo].[NDRoaster])

will return more than 1 result, and use it as:

@VAR3=(SELECT Mysingle FROM [IRIS].[dbo].[I_User] WHERE Id =@VAR1)

in You case - WHERE Id =@VAR1
not correct

original article use MIN and MAX

@VAR1=(SELECT MAX(UserId) FROM [IRIS].[dbo].[NDRoaster])

You can use any other filter for warrant single result from subquery

Also - wrong loop,
in this case (for each UserID) define cursor as

(SELECT DISTINCT UserId FROM [IRIS].[dbo].[NDRoaster])

and fetch records in loop

define cursor
tutorial


There are many things that are wrong with your configuration and running status:

With your configuration, before running no query, you need 1.5 GB of static memory assignment- before the buffer pool is even used an no connection is performed. Most of it (1GB) goes to performance_schema, but there is still 500MB that are allocated (vs the 42k for the default configuration).

You also have quite some high number of temporary tables created (180 tables/s), half of them in memory -I would bet that the other 90 are created in memory, then on disk. That can account, in your setup, to up to 1.8 GB of temporary tables per second.

Your queries have a non-negligible amount of full joins and full table scans, reading randomly 26K rows per second and sorting 3K per second. In summary- you end up wasting lots of resources in read buffers, join buffers and sort buffers. And that is for 90 simultaneous connections. 90 is not a high number for MySQL, but you need more resources for the type of queries that you are executing- which examine lots of rows.

You say that you have 100K tables, which usually affects the InnoDB dictionary size (146M) and the open_table_cache. You have increased some of the caches, but I do not agree that that is the right way to proceed.

Aside from that, you have lots of connections using SHOW STATUS commands, typical from an ORM/framework. Not worrying in 5.6, where metadata commands do not update the InnoDB statistics automatically, but from that I can derive that the queries you are executing are not the most optimal ones.

So, my recommendations:

Turn down most of the memory-related, session-only or query-only variables (e.g. tmp_table_size)
Turn down table-cache related variables
Reduce the concurrency by setting up a pool of connections (or reconfigure the current one). This will allow you to turn down max_connections without affecting the application throughput.

These ones will increase latency, but that is needed in order to increase the concurrency. If you want more concurrency with the same memory resources you will have to split your tables on several servers (aka sharding). In general, there are many things that I do not like from your configuration file, but I am not bold enough to tell you to change them without evaluating them myself.

Turn off performance_schema (it will save you 1GB of memory for your configured number of connections)
Fix your queries and start using indexes. Or create the appropriate indexes. This is probably the first step.
Avoid using MySQL- Without seeing your queries, something tells me that most of them could be easily cached at application or middleware layer. 5.6 even comes with a free memcached server!
Watch for the rollbacks you are executing (1 was running at the time of the statistics gathering). Rollbacks are very expensive resource-wise (memory and CPU).

For more detailed information, I would need to perform a query audit.


Temporarily moving a SQL Server 2016 database to SQL Server 2017 and then moving back. Is it possible?

No, this is not possible.SQL Server 2017 backups cannot be restored by any earlier version of SQL Server ref

Also, regarding detatching and reattaching per the docs:

  After being attached to SQL Server 2017, the database is available
  immediately and is automatically upgraded. This prevents the database
  from being used with an older version of the Database Engine

Also, not sure you care about the compatibility level, but FYSA:

  If the compatibility level of a user database is 100 or higher before
  upgrade, it remains the same after upgrade. If the compatibility level
  is 90 before upgrade in the upgraded database, the compatibility level
  is set to 100, which is the lowest supported compatibility level in
  SQL Server 2017.

Additionally, I believe you question is about a user database, but if it isn't:

  Backups of master, model and msdb that were created by using an
  earlier version of SQL Server cannot be restored by SQL Server 2017


There is something you are not taking into account.

MyISAM caches index pages into the Key Cache
InnoDB caches data and index pages in the Buffer Pool
I wrote a post about this : What are the main differences between InnoDB and MyISAM?

The 466092032 you gave is just the number of bytes taken by indexes. The InnoDB Storage Engine is moving data and index pages (each InnoDB page is 16K) in and out of the Buffer Pool due to its small size and the amount of data you are not accounting for.
Please refer back to my post. It will recommend how to size both key_buffer_size (which should be about 84M in you case) and the innodb_buffer_pool_size.
RECOMMENDATIONS

Increase the VPS RAM enough to fully accommodate your data.
Lower the read and write threads since you are in a VPS
Disable the query cache because it slows down InnoDB : See my post Is the overhead of frequent query cache invalidation ever worth it?


100's of 1000's of report server jobs added - how to kill all?

I was wrong in my assumption that Notifications was not the table to be looking in. It was exactly the right one. Deleting all where the ReportID matched the one we were looking at fixed the problem.

DELETE FROM [ReportServer].[dbo].[Notifications] WHERE ReportID = '4837A901-7188-4FCB-B86A-7974D35D9F56'


Oracle 11g http listener configuration for PL/SQL server pages

check tnsnames.ora file

C:\oracle\product\10.2.0\Oracle\NETWORK\ADMIN

ORCL=
   (DESCRIPTION =
       (ADDRESS_LIST =
             (ADDRESS =
                  (PROTOCAL = TCP)
                  (HOST = 123.456.789.123)
                  (PORT = 1521)
             )
       )
       (CONNECT_DATA = (SID = ORCL))
   )


What is really wrong with this query please?

I'm not a MySQL expert, but it looks like:

SELECT CONCAT_WS(parent_title, parent_fname, parent_sname) AS 'Parent'
    CONCAT ('£1.50', sum(activity_price) AS Amount

should be:

SELECT CONCAT_WS(parent_title, parent_fname, parent_sname) AS 'Parent',
    CONCAT ('£1.50', sum(activity_price)) AS Amount

I added a comma after the first result column, and a trailing bracket to the CONCAT function.

While I have no idea if the latter concatenation is a valid operation, what I've suggested should fix the basic syntax errors.


Permission denied with Copy command when using network share

With COPY from a named file, the file is read on the server by the user who owns the database cluster (usually a service account) not on the client.  So the server (both machine and account) is what needs to have access to it. If your file is readable on the client, you can use COPY...FROM STDIN in conjunction with the support functions described here.


SQL Server: how to select rows when changes happen?

This should get you what you want.  You want to look into using the LEAD function.

DECLARE @T TABLE
    (
    CreationDate DATE NOT NULL
    , [Value] VARCHAR(10) NOT NULL
    )

INSERT INTO @T
    (CreationDate, [Value])
VALUES ('6/4/2019', 'apple')
    , ('6/1/2019', 'apple')
    , ('5/30/2019', 'apple')
    , ('4/30/2019', 'orange')
    , ('4/1/2019', 'orange')
    , ('3/1/2019', 'apple')
    , ('2/1/2019', 'orange')

;WITH CTE_Source AS
    (
    SELECT TOP 100 PERCENT CreationDate, [Value], LEAD([Value]) OVER (ORDER BY (SELECT NULL)) AS PriorValue
    FROM @T AS T
    ORDER BY T.CreationDate, T.[Value]
    )
SELECT CreationDate, [Value]
FROM CTE_Source
WHERE PriorValue IS NULL OR PriorValue <> [Value]


It is pretty simple, you just do the following two steps

1st: in sql server, create a new login using the user name in that database, like

use master;

create login [MyUserName] with password='mypassowrd123$', check_policy=off;
go

2nd: map the login with the user name inside that particular database, let's call it [xyz]

use [xyz]
alter user [MyUserName] with login = [MyUserName];
go


The best way to do this effectively I think is to backup all the databases and transfer via Site to Site VPN (setup between your location and East Coast Azure Datacenter).

To backup all databases, setup a SQL agent job with below step. Remember it's a CmdExec type and not a T-SQL statement.

sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d master -Q "EXECUTE [dbo].[DatabaseBackup] @Databases = 'ALL_DATABASES', @Directory = N'C:\SQL Backups', @BackupType = 'FULL', @Verify = 'Y', @CleanupTime =312, @CheckSum = 'Y',@Compress = 'Y'" -b

And simple enough, copy all the .bak files from C:\SQL Backups to the remote Azure location. and use a restore script to restore all databases at once.

I recently found a tool (Teracopy) that does the copy/paste much faster than traditional Windows server copy functionality.

Hope this helps.


Possibly a silly question: Can I install MySQL on an External HDD connected to my Router?

It is not a silly question as these days when databases are growing beyond gigs this is becoming a common practice. You can do that by moving all your files in current data directory to the new location and make changes in (datadir) in your my.cnf / my.ini accordingly

Stop MySQL (service mysql stop)
Make a dir in the HDD (mkdir /mysqldata)
Move your data files to HDD (cp -rp /var/lib/mysql/* /mysqldata)
Search datadir in my.cnf (cat /etc/mysql/my.cnf | grep datadir )
Change path accordinly (datadir = /var/lib/mysql To: datadir = /mysqldata )
Start MySQL (service mysql start)


SET ARITHABORT, .NET, and explanations inconsistency?

The sql engine is generating you a bad plan.  As a starting point make sure your regular maintenance is getting fresh statistics and you don't have fragmented indexes so the engine is trying to work with valid numbers.  If the basics are solid you are more than likely having parameter sniffing issues.  Put simply sometimes the engine is getting a good plan and sometimes it is making a very bad estimate based on a parameter which gives you a different execution plan done in a terrible way resulting in poor performance.

Arithabort setting does not create this performance issue as you would think looking at your fast execution.  .NET using the setting off and SSMS using the setting on just guarantees you won't use the same plan and it will come up with a new plan probably the correct one.  But if you just turn arithabort on for .NET you will probably run into the same bad estimate issue.  Rather what you want to do is leave arithabort off and then try a sample execution of the query with the setting OFF from SSMS to make it mimic running from code and setting ON to compare to help you discover when you are having a problem.  You may or may not be able to figure out based on the table data where the issue lies for instance we had a table with a pseudo-PK that was 0 half the time and had a value the rest of the time so queries had trouble using it as the key is should have been.

The best way to deal with this once you discover this is a problem is the add the I found a parameter sniffing issue specific hint optimize for unknown.  You should be able to search for that and find the syntax that fits your case.


union returns distinct results.
try UNION ALL

select entity_id from table1 exc
UNION ALL
select entity_id from tabl2 inc


MySQL Workbench: Edit hidden relationships in EER diagram

To my surprise I can't find any method to access the hidden relationship. It's listed in the table that defines the foreign key, but you cannot use this to open the relationship editor. However, by (temporarily) changing the foreign key the relationship is made visible again and you can then double click it to open the relationship editor. A context menu entry for the FK in the table editor and/or a small button to open that relationship editor would be nice. Open a feature request at http://bugs.mysql.com if you like.


What is the appropriate index architecture when forced to implement IsDeleted (soft deletes)?

The easiest approach here is to leave your keys and clustered indexes alone, and use filtered indexes for your non-clustered indexes.

Additionally you can migrate some large tables to partitioned heaps or partitioned clustered-columnstores (SQL Server 2016+), leaving the primary key and unique indexes un-partitioned.  This would enable you to push the non-key columns for IsDeleted rows to a seperate data structure, which could additionally be compressed differently or stored on a different filegroup.

And make sure the developers use a literal instead of a parameter to filter the IsDeleted rows out. With a parameter SQL Server has to use the same query plan for both cases.

EG

SELECT ... WHERE ... AND IsDeleted=0

And not:

SELECT ... WHERE ... AND IsDeleted=@IsDeleted

Using a paramter will prevent use of filtered index, and can get you into trouble with parameter sniffing.


You have no need to add a "modules" field to your main/users table.  Just JOIN on the tables -- that's what Relational Databases are for.  INNER or OUTER joins should be used depending on your needs.

For example, this will return a user with their conditions.  If the user doesn't have any conditions, it will return NULL for those fields, but it will still return the user:

SELECT *
FROM User U
   LEFT JOIN Conditions C ON U.Id = C.User_ID

Good visual representation of joins


Where is pt-table-checksum finding this hostname?

Your Question 1

Hostname is from the output of SHOW PROCESSLIST. From that output it tries to detect the slaves. If this is not the right outcome for you, then you can define the dsns table with the list of hosts and use --recursion-method=dsn

Part two of your question will need more information, but you may be able to help yourself using PTDEBUG=1 pt-table-checksum <params>

You are welcome to update your original question with the output from the debug if you would like help with understanding the output.

Disclosure: I work for Percona


Exclude .LOG file while restoring a database

No: the log is an essential part of the database and is used during RESTORE process (redo/undo phases) to ensure consistency.

Use WITH MOVE to restore to a compressed folder to gain "more" disk space temporarily. Or an external USB drive etc

Edit:

By compressed, I mean NTFS compression (for XP but still applies).

WITH MOVE is part of restore exposed as "Restore the database files as" in SSMS

Edit 2:

SQL Server can not use network drives unless you enable trace flag 1807. Now, use at your own risk to get running again only


Does MySql event will start if the previous event doesn't finish yet?

To quote the manual:

  If a repeating event does not terminate within its scheduling interval, the result may be multiple instances of the event executing simultaneously. If this is undesirable, you should institute a mechanism to prevent simultaneous instances. For example, you could use the GET_LOCK() function, or row or table locking.

In other words, the new copy will run.

This could lead to more and more running, so you should worry about it.


Well, perhaps not the best way, however, I managed to do it via this: (I ignored that extra third table, wasn't sure ... not sure how it affects things) ;)
(the w_tab1 and w_tab2 are just your sample data ... )
Tested on Oracle 12.

  with w_tab1 as (
           select 28 idproduct, 18 totalout from dual union all
           select 26 idproduct,  8 totalout from dual union all
           select 27 idproduct,  2 totalout from dual union all
           select 16 idproduct, 17 totalout from dual
        ),
     w_tab2 as (
           select 16 idstorage, 16 idproduct, 20 totalin, 0    purchaseprice, null                                 datainput, 2 idprovider, null status from dual union all
           select 17 idstorage, 17 idproduct, 20 totalin, 0    purchaseprice, null                                 datainput, 2 idprovider, null status from dual union all
           select 22 idstorage, 22 idproduct, 20 totalin, 4500 purchaseprice, null                                 datainput, 2 idprovider, null status from dual union all
           select 23 idstorage, 23 idproduct,  6 totalin, 4300 purchaseprice, to_date('18-may-2015','dd-mon-yyyy') datainput, 2 idprovider, null status from dual union all
           select 24 idstorage, 24 idproduct,  4 totalin, 3700 purchaseprice, to_date('18-may-2015','dd-mon-yyyy') datainput, 2 idprovider, null status from dual union all
           select 25 idstorage, 25 idproduct, 20 totalin, 40   purchaseprice, to_date('18-may-2015','dd-mon-yyyy') datainput, 2 idprovider, null status from dual union all
           select 26 idstorage, 26 idproduct, 20 totalin, 4300 purchaseprice, to_date('18-may-2015','dd-mon-yyyy') datainput, 1 idprovider, null status from dual union all
           select 27 idstorage, 27 idproduct, 10 totalin, 4300 purchaseprice, to_date('18-may-2015','dd-mon-yyyy') datainput, 1 idprovider, null status from dual union all
           select 28 idstorage, 28 idproduct, 10 totalin, 4300 purchaseprice, to_date('12-may-2015','dd-mon-yyyy') datainput, 1 idprovider, null status from dual union all
           select 29 idstorage, 28 idproduct, 10 totalin, 4300 purchaseprice, to_date('18-may-2015','dd-mon-yyyy') datainput, 1 idprovider, null status from dual union all
           select 30 idstorage, 29 idproduct, 10 totalin, 4300 purchaseprice, to_date('18-may-2015','dd-mon-yyyy') datainput, 1 idprovider, null status from dual union all
           select 31 idstorage, 28 idproduct, 40 totalin, 3000 purchaseprice, to_date('18-may-2015','dd-mon-yyyy') datainput, 1 idprovider, null status from dual union all
           select 32 idstorage, 28 idproduct, 30 totalin, 3000 purchaseprice, to_date('22-may-2015','dd-mon-yyyy') datainput, 1 idprovider, null status from dual union all
           select 33 idstorage, 30 idproduct, 10 totalin, 4300 purchaseprice, to_date('22-may-2015','dd-mon-yyyy') datainput, 1 idprovider, null status from dual
        ),
     w_sub as (
           select a.idproduct,
                  a.totalout, sum(b.totalin) over (partition by a.idproduct
                                   order by b.datainput, b.idstorage) sumin,
                  b.datainput, b.totalin, b.idstorage
             from w_tab1  a,
                  w_tab2  b
            where a.idproduct = b.idproduct
        )
  select idproduct,
           least(totalin, nvl(lag(greatest(0,totalout-sumin)) over (partition by idproduct
                                order by datainput, idstorage),greatest(0,least(totalout,sumin))))       totalout,
           datainput, totalin
    from w_sub
   order by idproduct, datainput, idstorage
  /

Result (on Oracle 12):

  IDPRODUCT   TOTALOUT DATAINPUT               TOTALIN
  ---------- ---------- -------------------- ----------
          16         17                              20
          26          8 18-may-2015 00:00:00         20
          27          2 18-may-2015 00:00:00         10
          28         10 12-may-2015 00:00:00         10
          28          8 18-may-2015 00:00:00         10
          28          0 18-may-2015 00:00:00         40
          28          0 22-may-2015 00:00:00         30

  7 rows selected.


Fulltext search limiting users possibilities

Here are three suggestions.

Suggestion 1. Use plainto_tsquery when you convert the user provided search string to PostgreSQL's tsquery, see the PostgreSQL docs. — I think that plainto_tsquery discards any special magical characters + AND, OR etcetera. (But I'm not completely sure. You'd better verify this so you don't do anything dangerous)  Oops I didn't notice that this was about SQL Server not PostgreSQL.

Suggestion 2. Build a HTML form for advanced search queries, with fields like:

Include all these words: ...
Include any of these words: ...
Exclude all these ...

And so on. Next, construct the otherwise dangerous full text search expression yourself in SQL Server syntax.

Suggestion 3. You could provide a single search text field, and let e.g. "-someword" mean that "someword" should be excluded, and "+anotherword" mean that "anotherword" must be included. Then, you build your own parser for this search field, and convert the resulting "abstract search tree" to something SQL Server understands, and give it to SQL Server.


ORA-01652 : unable to extend temp segment by 128 in tablespace HFDORA_TEMP

Error message ORA-01652 means: you don't have enough free temp space (enough free memory inside temp tablespace) or local temp segment not being able to extent space even though there is space in other instances.

To troubleshoot this issue: execute the following commands:

select TEMPORARY_TABLESPACE from dba_users where USERNAME = 'myuser'

then take the result from first SQL command to help you get free space inside that tablespace

select sum(free_blocks)
from gv$sort_segment
where tablespace_name = 'result'

If you get the result 0 thats mean the temp tablespace doesn't have enough free space, otherwise you need to check segment (mostly happened for RAC) by executing the following command:

select inst_id, tablespace_name, total_blocks, used_blocks, free_blocks
from gv$sort_segment;

check total_blocks and used block and adjust the segment as needed.


Looking for another method to count in a single query

From the information provided, it sounds like you don't absolutely have to have the data in the format:

  Grant | Legacy | Gift | Purchase | ...
 -------+--------+------+----------+------
    2   |    5   |   0  |     0    | ...

If you can use the data in the format:

  Status  | Count
----------+-------
 Grant    |     2
 Legacy   |     5
 Gift     |     0
 Purchase |     0
 ...      |   ...

Then the simplest solution is to pull the data back as follows:

SELECT cat.status as `Status`, COUNT(p.id) as `Count`
  FROM cat_provenance cat
         LEFT JOIN provenance p ON (cat.ID = p.provenance_status)
 GROUP BY cat.status WITH ROLLUP

Results will look like the above. By including WITH ROLLUP, we include a row with Status NULL and Count the grand total for all categories.

  Status  | Count
----------+-------
 ...      |   ...
 NULL     |    26

Note: code untested


Is there a routine in MySQL similar to fn_dblog in SQL-server?

Yes, you can use mysqlbinlog. Here are a few tutorials:

15 mysqlbinlog Command Examples for MySQL Binary Log Files
Identifying useful info from MySQL row-based binary logs
mysqlbinlog documentation for your version (going by the mysql-5.6 tag you used)


How are Postgres database files updated?

The linked documentation page says:

  Each table and index is stored in a separate file. […]
  each table and index has a free space map … stored in a file named with the filenode number plus the suffix _fsm. Tables also have a visibility map, stored in a fork with the suffix _vm

So the actual table data ends up in a single file, or in multiple segments all belonging to the same 'virtual' file.

However, a table update can also affect indexes, or statistical information in the system tables.

In any case, the WAL tracks all changes to all tables, and at the end of each transaction, all files are in a coherent state.


Why does Postgres generate an already used PK value?

PostgreSQL will not try to insert duplicate values on its own, it is you (your application, ORM included) who does.

It can be either a sequence feeding the values to the PK set to the wrong position and the table already containing the value equal to its nextval() - or simply that your application does the wrong thing.  The first one is easy to fix:

SELECT setval('your_sequence_name', (SELECT max(id) FROM your_table));

The second one means debugging.

Django (or any other popular framework) doesn't reset sequences on its own - otherwise we would have similar questions every other day.


What is the MAXDOP value for the server which doesn't have NUMA

For SQL Server 2005 and later versions

Server with single NUMA node    Less than 8 logical processors  Keep MAXDOP at or below # of logical processors

Server with single NUMA node    Greater than 8 logical processors   Keep MAXDOP at 8

Server with multiple NUMA nodes Less than 8 logical processors per NUMA node Keep MAXDOP at or below # of logical processors per NUMA node

Server with multiple NUMA nodes Greater than 8 logical processors per NUMA node Keep MAXDOP at 8

See : Recommendations and guidelines for the "max degree of parallelism" configuration option in SQL Server


Can SQL Server database be changed from NoRecovery to Standby?

Yes, you can restore the logs by changing the settings for the Restore Job to be set to STANDBY, instead of NORECOVERY.  This would provide a degree of Read Only access until the next restore is run.

If you are using the log shipping functions that come with SQL Server you use the settings shown by Ali Razeghi for using sp_change_log_shipping_secondary_database.

If you have rolled your own log shipping process (which many people have done) then you are already controlling the restore process yourself.

EDIT: Since you restore the secondary one time a day, the READ ONLY window will be most of a day.  So that eases the scheduling issue.

Since the procedure sp_change_log_shipping_secondary_database is not working, either (1) you ran this on the primary instead of the secondary server, or (2) your organization made their own custom log shipping process.

EDIT: If you are in the (2) state, then please include the details of the secondary server's Log Shipping Restore Job.

However, a database in NORECOVERY can be updated to STANDBY like this:

RESTORE DATABASE [MyDatabase]
WITH STANDBY = N'D:\MSSQL\Backup\MyDatabase_RollbackUndoFile.bak'

Any time the Restore Job recovers a series of LOG files, it will disconnect any STANDBY users that are still connected.

Also see Kendra Little's post: Reporting From a Log Shipping Secondary in STANDBY mode  Contained in here post is a mention of the problem that you have run into with restoring a 2008 database to a 2008 R2 Secondary.

  You can’t use “STANDBY” Mode if your Secondary is a Higher Version

  I was a little sad when I learned this one years ago. I had a SQL
  Server 2005 instance that was the log shipping primary. The log
  shipping secondary was made readable so that developers could check
  out production data if needed without having access to production.

This is because the database pages need to be upgraded to the 2008 R2 level in order to be readable by STANDBY, but that is a violation of keeping the Secondary exactly like the Primary.

Really the only route to getting STANDBY mode on your secondary is to upgrade your SQL Server 2008 to SQL Server 2008 R2 so as to match the secondary. (Or
(if you want to modernize) to upgrade to a higher version for both primary and secondary.)


Does the recovery model affect growth of the .mdf file?

the simple recovery model only affects the log files of your database (.ldf).

If your datafiles are growing, you can check the tables that are occupying more space, you can use the management studio: right click on the database> reports > standard reports > disk usage by top tables

this way you can determine which table is taking all the space and plan a cleanup task for that (or move data to another DB)


SQL to get performance metrics shown in EM Express 12C Resources

STATSPACK comes out of the box with Oracle Database Standard Edition. It works by comparing two snapshots.

https://docs.oracle.com/cd/E13160_01/wli/docs10gr3/dbtuning/statsApdx.html

Install:

cd $ORACLE_HOME/rdbms/admin/
sqlplus "/ as sysdba" @spcreate.sql

Take a snapshot:

sqlplus perfstat/<password>
execute statspack.snap(i_snap_level=>7);

Generate a report:

cd $ORACLE_HOME/rdbms/admin/
sqlplus perfstat/<password> @spreport


If you don 't try to understand the meaning of the request and simply adapt it to MySQL then:

UPDATE userrole ur
LEFT JOIN ( SELECT b.id
            FROM userrole a
            INNER JOIN userrole b ON a.roleid = b.roleid
                                 AND a.region = b.region
            WHERE a.userid = 6
              AND b.userid = 5 ) sq ON ur.id = sq.id
SET ur.userid = 6
WHERE ur.userid = 5
WHERE sq.id IS NULL

If you try to understand the task then it looks like (it seems) "alter userid from 5 to 6 if the record with the same (roleid,region) and userid=6 not exists yet". If so the task may be solved by:

UPDATE userrole t1
LEFT JOIN userrole t2 ON (t1.roleid, t1.region) = (t2.roleid, t2.region)
                     AND t2.userid = 6
SET t1.userid = 6
WHERE t1.useris = 5
  AND t2.userid IS NULL


How to match a version of SQL Server 2008?

Version 10.1.2531.0 is for SQL Server 2008 Service Pack 1.
The following table lists the major releases of SQL Server 2008.

Release                         Product version

SQL Server 2008 Service Pack 3  10.00.5500.00
SQL Server 2008 Service Pack 2  10.00.4000.00
SQL Server 2008 Service Pack 1  10.00.2531.00
SQL Server 2008 RTM 10.00.1600.22

Check more details on SQL Server version

EDIT:

You can query database to get these info from database itself like

SELECT
 SERVERPROPERTY('ProductVersion') AS ProductVersion,
 SERVERPROPERTY('ProductLevel') AS ProductLevel,
 SERVERPROPERTY('Edition') AS Edition,
 SERVERPROPERTY('EngineEdition') AS EngineEdition;

More details on SERVERPROPERTY


Can somebody help me understanding the difference between view,materialized view and snapshot?

snapshot is an old and deprecated term for a materialized view.  They're exactly the same thing, snapshot is just an older term.

A view is a stored query.  It consumes (effectively) no space, just the space required to store the text of the query in the data dictionary.  When you query the view, the stored query is executed.  The results, therefore, will always be up to date.

A materialized view is a both a stored query and a segment.  The stored query is executed and the results are materialized into the segment.  That means that you can do things like index the materialized view.  It also means that if you query the materialized view, you may see stale data (depending on how you have configured the materialized view to refresh itself).  Depending on a variety of factors (whether query rewrite is enabled, whether stale data is tolerated, whether the materialized view is up to date, whether dimensions exist), materialized views can be used by Oracle automatically to speed up queries that do not directly reference the materialized view.  This is particularly useful when you're building a data warehouse and want to be able to pre-aggregate data for various reports.


Is the SQLTRACE_FILE_BUFFER wait type documented? What does it indicate?

I don't believe it is documented anywhere (even in Bob Ward's Wait Type Repository - a useful resource in any case), and I suspect you are quite right about the underlying cause.

If this wait type seems to be causing you significant performance issues (and you've isolated the wait as the cause and not a symptom, by observing relieved pressure when you temporarily disable the trace), probably the best thing would be to investigate the trace and make sure:

you are filtering adequately to prevent the writing of data you'll never look at.
you are capturing only the event types you need.
you aren't writing to a severely under-powered or over-utilized disk, to the same local disks where your data/log files are, or over a network connection that is just not capable of handling the load (if writing remotely the wait may not show up as network - especially if the write is going over iSCSI or other remote-disks-that-look-kind-of-local-to-SQL-Server).

For most things your trace is probably collecting, you may be better off converting your trace to use Extended Events. Not everything there is more lightweight than trace, but most of it is. The EE team at Microsoft blogged about how to do this, but if you're planning a migration to SQL Server 2012, you'll want to watch Jonathan Kehayias' blog.


Simple Mode - Is using checkpoints to truncate the transaction log a bad practice?

For those hitting on that post I actually found a solution to reduce the size of my log by chunking the biggest transactions. That link helped me out :http://sqlperformance.com/2013/03/io-subsystem/chunk-deletes


Changing a column datatype from INT to BIGINT

You have to change all partitions at once, because the column data type of the partitioned table has to be the same as the column data type in the partitions.

One option is to add a new bigint column, fill it with values from the old column and then drop the old column.

This will take a while, but requires only short ACCESS EXCLUSIVE locks on the table.


Troubleshooting "Can't connect ..." from MySQL slave to master

cat <<-EOF | mysql    Line OK
STOP SLAVE; Line OK
CHANGE MASTER 'AnyName4Slave' TO MASTER_HOST = '',MASTER_PORT=3306, MASTER_USER = 'user', MASTER_PASSWORD = 'password' ,MASTER_LOG_FILE='mysql-bin.041117', MASTER_LOG_POS=733093730;
START SLAVE 'AnyName4Slave';
EOF


You need to wrap that code in CREATE PROCEDURE ... syntax, and remove the GO statements after BEGIN TRANSACTION and before COMMIT TRANSACTION.

GO
CREATE PROCEDURE dbo.AssignUserToTicket
(
     @updateAuthor varchar(100)
    , @assignedUser varchar(100)
    , @ticketID bigint
)
AS
BEGIN
    BEGIN TRANSACTION;
    SAVE TRANSACTION MySavePoint;
    SET @updateAuthor = 'user1';
    SET @assignedUser = 'user2';
    SET @ticketID = 123456;

    BEGIN TRY
        UPDATE dbo.tblTicket
        SET ticketAssignedUserSamAccountName = @assignedUser
        WHERE (ticketID = @ticketID);

        INSERT INTO [dbo].[tblTicketUpdate]
            (
            [ticketID]
            ,[updateDetail]
            ,[updateDateTime]
            ,[userSamAccountName]
            ,[activity]
            )
        VALUES (
            @ticketID
            , 'Assigned ticket to ' + @assignedUser
            , GetDate()
            , @updateAuthor
            , 'Assign'
            );
        COMMIT TRANSACTION
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0
        BEGIN
            ROLLBACK TRANSACTION MySavePoint; -- rollback to MySavePoint
        END
    END CATCH
END;
GO

Also note, I have added a TRY...CATCH statement block to allow performing a ROLLBACK TRANSACTION statement in case some error occurs.  You probably need better error handling than that, but without knowledge of your requirements, that is difficult at best.

Some good reading:

Always specify the schema
Stored Procedure Best Practices
Bad habits to avoid


The (not overlapping) constraint between "regular" ranges can be enforced with an EXCLUDE constraint, as you it defined in the question.

The (not overlapping)  constraint between "exception" ranges can be enforced in the same manner and so can the constraint between "day off" ranges.

What is not trivial is the constraint between "regular" and "exception" ranges (and between "regular" and "day off" ranges):

  For an exception there should be a regular range that the exception range is contained inside the regular one.

This is not commutative, so it cannot be done with EXCLUDE constraints. What you really need is a FOREIGN KEY constraint, a slightly more complicated one than a common foreign key constraint.

As an example, a foreign key constraint defined n table B:

CONSTRAINT a_b_fk
    FOREIGN KEY (aid)
    REFERENCES a (aid)

enforces that:

  for any aid value in table B, there exists a row in table A with equal value in column aid

which is quite similar to what you have. The problematic difference is that foreign key constraints can use only the equality (=) and not any other operator. To solve this problem, we'll have to duplicate the columns that need to be checked with the different operator ("is contained by", <@).

As a result, we'll need to split the table into two, "regular" and "exception" ranges (lets forget the "day off" for a bit) and apply our solution:

For the "regular" ranges table, we'll only need an additional UNIQUE constraint, for the foreign key to be defined (and we can skip the is_exception and is_day_off columns):

CREATE TABLE date_regular_ranges (
  id SERIAL PRIMARY KEY,
  user_id INTEGER NOT NULL,
  start_at DATE NOT NULL,
  end_at DATE NOT NULL,
  CONSTRAINT date_regular_ranges_uq
    UNIQUE (user_id, start_at, end_at),
  CONSTRAINT date_regular_ranges_bounds
    EXCLUDE USING gist
      ( user_id WITH =,
        daterange(start_at, end_at, '[]') WITH &&
      )
);

For the "exception" ranges table, we'll need the FOREIGN KEY constraint and a CHECK constraint for the `contained" operator:

CREATE TABLE date_exception_ranges (
  id SERIAL PRIMARY KEY,
  user_id INTEGER NOT NULL,
  start_at DATE NOT NULL,
  end_at DATE NOT NULL,
  regular_start_at DATE NOT NULL,
  regular_end_at DATE NOT NULL,

  -- we don't need the UNIQUE, as the EXCLUDE covers it
  -- CONSTRAINT date_exception_ranges_uq
  --   UNIQUE (user_id, start_at, end_at),

  CONSTRAINT date_exception_ranges_bounds
    EXCLUDE USING gist
      ( user_id WITH =,
        daterange(start_at, end_at, '[]') WITH &&
      ),

  CONSTRAINT date_regular_exception_ranges_fk
    FOREIGN KEY
      (user_id, regular_start_at, regular_end_at)
    REFERENCES date_regular_ranges
      (user_id, start_at, end_at),

  CONSTRAINT date_exception_ranges_ck
    CHECK ( daterange(start_at, end_at, '[]')
         <@ daterange(regular_start_at, regular_end_at, '[]')
          )
);

For the "day off", since we identified that are similar to "exception" ranges, we can either add another table (with identical structure as the date_exception_ranges) or keep only that and add a boolean column to identify whether it is an "exception" or a "day off".

(The following assume that "exceptions" can overlap "day off" ranges. If they cannot, it's actually much simpler):

CREATE TABLE date_exception_ranges (
  id SERIAL PRIMARY KEY,
  user_id INTEGER NOT NULL,
  start_at DATE NOT NULL,
  end_at DATE NOT NULL,
  regular_start_at DATE NOT NULL,
  regular_end_at DATE NOT NULL,
  -- TRUE: is_exception, FALSE: is_day_off
  is_exception BOOLEAN NOT NULL,

  CONSTRAINT date_exception_ranges_bounds
    EXCLUDE USING gist
      ( user_id WITH =,
        (is_exception::int) WITH =,
        daterange(start_at, end_at, '[]') WITH &&
      ),

  CONSTRAINT date_regular_exception_ranges_fk
    FOREIGN KEY
      (user_id, regular_start_at, regular_end_at)
    REFERENCES date_regular_ranges
      (user_id, start_at, end_at),

  CONSTRAINT date_exception_ranges_ck
    CHECK ( daterange(start_at, end_at, '[]')
         <@ daterange(regular_start_at, regular_end_at, '[]')
          )
);

And finally, the above tested at dbfiddle.uk


mongodump failing with invalid json input

Try adjusting the input:
/usr/bin/mongodump -u backup -p backup12345  --host=XX.XX.XX.XX --port XXXX --out=/tmp -d local -c oplog.rs --gzip --authenticationDatabase admin \
--query '{"ts": { "$gt": Timestamp(1619548593, 1) } }'
--ssl --sslAllowInvalidCertificates

I tried multiple versions and they worked fine with me:

--query '{ "ts": { "$gt": Timestamp(1111111, 1) }}'
--query '{ "ts": { "$gt": ISODate("2021-04-05T08:00:00.000Z") }}'
--query "{ 'ts': { \$gt:  ISODate(\"2021-04-05T08:00:00.000Z\") }}"

In older mongo versions you need to use the "extended json":

--query '{ "ts": { "$gt": {"$timestamp":{"t":1565545664,"i":1}} }}'

this is completetly valid json and should work with the old client as well.
also see mongodump command with timestamp query