why is mysql still writing data two hours after all transactions have stopped

InnoDB has the habit of purging about 100 MB every 10 seconds from the InnoDB Buffer pool. That's by design. The idea behind InnoDB transactional model is to delay writes as much as possible.
If you have major spikes in reads, or sustained moderate reads going on, there are still writes occurring because of MVCC. I wrote about this back on May 3, 2012 ( Which is faster, InnoDB or MyISAM? )
You can still see evidence of this in a special way.
Try one of the following two(2) options:
OPTION #1
SET GLOBAL innodb_max_dirty_pages_pct = 0;

The default for innodb_max_dirty_pages_pct is 90 in MySQL 5.0. Set it to zero(0) and flush the Buffer Pool. After it is flushed, set it back to 90. The dirty pages will just pile up again. Delayed writes of changed blocks with just go back to the 100MB/10sec flush pattern all over again.
OPTION #2
SET GLOBAL innodb_max_dirty_pages_pct = 0;

Leave the innodb_max_dirty_pages_pct indefinitely. You will see higher-than-normal writes of the dirty pages.
Regardless which option you choose, if innodb_buffer_pool_pages_dirty conitnues that same pattern and rate of change, without the presence of INSERTs/UPDATEs/DELETES, only SELECTs can be source of this behavior.
You may want to experiment with setting innodb_log_buffer_size (default is 8MB). A larger log buffer saves on disk I/O, but be careful with large COMMITs.


Why is mySQL output not procedurally consistent with source code input?

Databases have no concept of "order".

This is so that they can handle millions, billions, and trillions of rows of data without worrying about "order".

If you want output in a specific order, you MUST specify an ORDER BY clause.

This means you need a column you can ORDER BY.

On such column could be an Identity column.  Check your RDBMS documentation for any potential out-of-order possibilities. (Eg oracle RAC Sequence)

Another method is to use a CREATED_ONcolumn.  This a date+time column with sub-second resolution.


Postgres 9.1.6 Error index contains unexpected zero page at block 0

I just ran into the same issue on Postgres 8.3.11. Although I could not identify the root cause, the fix was simple enough:

REINDEX INDEX tbl_cust_id_idx;

This page contains hints as to what may have caused the error, although they are vague:

  An indicated table index was corrupted (may be a result of recent postgres or system failure, there was a lack of free space on the device, etc).

Ideally it would be helpful if there was something that could be put in place to proactively prevent or correct these errors. On the bright side, this is the first time we have ever seen this problem in several years and across hundreds of machines.


SQL Server (Azure) split comma-separated string into multiple columns?

STRING_SPLIT splits a string into a set consisting of a single column, not into multiple columns in the same row.

If there are always 4 components, you can use this long thing:

DECLARE @x TABLE(ID int,    Name sysname)
INSERT @x VALUES
(1,'     US_Android_100x200_blue'),
(2,'     UK_iOS_100x200_green');

;WITH x AS
(
  SELECT x.ID, y.value, rn = ROW_NUMBER() OVER
    (PARTITION BY x.ID ORDER BY CHARINDEX('_' + value + '_', '_' + x.name + '_'))
  FROM @x AS x CROSS APPLY STRING_SPLIT(x.name, '_') AS y
)
SELECT ID, Country = [1], Device = [2], [Size] = [3], Color = [4]
FROM x PIVOT (MAX(value) FOR rn IN ([1],[2],[3],[4])) AS p
ORDER BY ID;

Or you can hack it:

;WITH x AS
(
  SELECT ID, Name = REPLACE(Name, '_', '.') FROM @x
)
SELECT ID,
  Country = PARSENAME(Name, 4),
  Device  = PARSENAME(Name, 3),
  Size    = PARSENAME(Name, 2),
  Color   = PARSENAME(Name, 1)
FROM x
ORDER BY ID;


Is there a way to make SQL Server's Database Tuning Advisor use more than one core?

I expect the analysis is carried out serially, one query at a time, as doing so in parallel would be unreliable.

DTA produces recommendations by creating hypothetical indexes and evaluating the impact on a query's execution plan. If analysis were carried out on multiple queries at a time, the index created for one query could influence the analysis of another.

A hypothetical index is created using the undocumented command CREATE INDEX WITH STATISTICS_ONLY and as the name implies this creates just the statistics for the index, without building the physical structure.


How to load Excel file into temp table in ORACLE

First go to the menu and choose: database → create  → table

The follow prompt, choose schema, select object type (view, table etc).

Also make sure your column structure does not interfere with importing since the same rules apply with importing data when it comes to column spaces etc.. (Column names with spaces etc...)


Efficient way to store geographical routes in MongoDB

The right way to store a taxiroute in a MongoDB is to use a GeoJSON object and a LINESTRING

{ type: "LineString", coordinates: [ [ 40, 5 ], [ 41, 6 ] ] }

Using this method you can make use of Spatial Queries like $geointersects and $geonear, and Spatial Indexes. All of that said, you're probably better off migrating to a database that focuses more on this kind of stuff, like PostgreSQL.


Query to partially summarize data in a table using SQL

It's indeed a very basic concept: aggregation. You need the GROUP BY clause:

SELECT member_id,
pay_year,
SUM(pay_unit) AS sum_pay_unit
FROM payments
GROUP BY member_id, pay_year;


Plan for managing financial transactions in an easily and retrievable manner, indefinitely

With no growth, you're looking at 1.8 million rows in 10 years. With 100% growth per year, 93 million rows in 10 years. I'd estimate your actual upper bound to be 66 million rows after 10 years.

It's practical to keep that many rows in a transactional table if you partition the table. (You don't have to do that for a while.)

  We've thought of implementing a clever scheme of separating the table into years

Millions of people have thought of this. Don't do it.

Use a single, unpartitioned table until you have a performance problem. Then partition the table. (You have a lot of time between now and then to test and practice.) Finally, after you tune the server for the new workload, consider upgrading hardware. (Banks don't run on shared web servers that cost $2.95 a month.)

Also consider switching now to PostgreSQL. It has a better query optimizer than MySQL; it has more indexing options, including function-based indexes and partial indexes; and in my experience seems to handle 100-million-row tables better than MySQL.


Using intersect(user_id,deleted) --> Provide INDEX(user_id, deleted) (in either order).

See the tag [greatest-n-per-group]; there are more efficient ways to do this type of query.  Also, see [groupwise-max] in stackoverflow.com

Also INDEX(topic_id, picture_id, news_id) (in any order)

(There may be more improvements to make; see how far those tips get you.)


Is to possible to forbid access to tables in own schema - Oracle?

While there is no privilege you can revoke, and I'd wonder why you can't just create another user, the answer to your question is....

Yes

If you have the Enterprise Edition of Oracle you can use the Virtual Private Database feature to prevent tables from being selected.  Here is an overview from the Oracle Database Security Guide 11g Release 2:

  Oracle Virtual Private Database (VPD) enables you to create security
  policies to control database access at the row and column level.
  Essentially, Oracle Virtual Private Database adds a dynamic WHERE
  clause to a SQL statement that is issued against the table, view, or
  synonym to which an Oracle Virtual Private Database security policy
  was applied.

  Oracle Virtual Private Database enforces security, to a fine level of
  granularity, directly on database tables, views, or synonyms. Because
  you attach security policies directly to these database objects, and
  the policies are automatically applied whenever a user accesses data,
  there is no way to bypass security.

  When a user directly or indirectly accesses a table, view, or synonym
  that is protected with an Oracle Virtual Private Database policy,
  Oracle Database dynamically modifies the SQL statement of the user.
  This modification creates a WHERE condition (called a predicate)
  returned by a function implementing the security policy. Oracle
  Database modifies the statement dynamically, transparently to the
  user, using any condition that can be expressed in or returned by a
  function. You can apply Oracle Virtual Private Database policies to
  SELECT, INSERT, UPDATE, INDEX, and DELETE statements.

To prevent SELECT on a table you would need to do the following:

Create a function that accepts a schema/object name and returns a WHERE clause style Varchar2.  In this case it should return something like '1=2' when the user is the user you don't want to access its own objects.
Call the Add_Policy function of the DBMS_RLS package for each table the user owns.  This will create a policy attaching the function to the table it will protect.


There are a few problems here.

The id column in your schema is not declared as unique. What would you do if you had two of the same ids? @a_horse_with_no_name reads this as greatest-n-per-group, but why the greatest? What's the criteria for?

INSERT INTO telemetry ( id, lat, lon ) VALUES (1,1,1);
INSERT INTO telemetry ( id, lat, lon ) VALUES (1,1,2);

You're using point(). Point is a native-type function. If you're using PostGIS do not use point(). Instead, create your points with ST_MakePoint. (saves the casting).
You should consider not storing your types as lat/log points, but instead storing them as geometry, or geography. This will be faster, though it'll cost a bit more in space. Further, it'll work with indexing better.

The first reading of this problem is addressed by @a_horse_with_no_name  but I'm thinking you want a multipoint for every id in the table. For that you need a PostGIS aggregate, like ST_Union

SELECT id, ST_Union(ST_point(lon,lat)) AS cords
FROM telemetry
GROUP BY id;

That should work.

SELECT id,
  ST_AsText(ST_Union(ST_MakePoint(lon,lat))) AS cords_with_union,
  ST_AsText(ST_Collect(ST_MakePoint(lon,lat))) AS cords_with_collect
FROM ( VALUES
    (1, 1::real, 2::real),
    (1, 2::real, 2::real),
    (1, 2::real, 2::real), -- a duplicate (for id:1)
    (2, 6::real, 5::real)
) AS t(id,lon,lat)
GROUP BY id;

 id |  cords_with_union   |   cords_with_collect
----+---------------------+-------------------------
  1 | MULTIPOINT(1 2,2 2) | MULTIPOINT(1 2,2 2,2 2)
  2 | POINT(6 5)          | MULTIPOINT(6 5)

You can force a MULTIPOINT if you really want that by wrapping ST_Union like ST_Multi(ST_Union()).


SQL Server Client Tools: How can you tell what SQL edition the client tools are from?

Go to Management Studio's Help > About... menu. For example, I am running the latest build of SQL Server 2016 (CTP 2.1), and this is what my Help > About shows:

If I click Copy Info, I get this in my clipboard:

Microsoft SQL Server Management Studio          13.0.300.44
Microsoft Analysis Services Client Tools        13.0.300.44
Microsoft Data Access Components (MDAC)         10.0.10166.0
Microsoft MSXML                                 3.0 6.0
Microsoft Internet Explorer                     9.11.10166.0
Microsoft .NET Framework                        4.0.30319.42000
Operating System                                6.3.10166

There are no longer any "editions" of client tools, starting with SQL Server 2012 Service Pack 1. Before that, there were only two editions: licensed and Management Studio Express. Help > About would yield that information as well, and the title bar would say so too.


What does "volume" mean in MAX_IOPS_PER_VOLUME?

A "volume" is either a drive letter, or a raw partition in SQL Server.

For a resource pool configured for X maximum IOps per volume where a query uses tempdb, splitting that tempdb across multiple volumes on a single physical disk, SSD or not, with the data on a single volume may well give you the granularity you desire.  As always, I'd test it.


MongoDB - Can I create TTL index with rolling method

Can I create TTL index with rolling method

Yes, this is a supported approach for building indexes on replica sets. However, if your goal is to efficiently remove a large quantity of existing documents there are some caveats to be aware of as noted below.

  I tried to remove using bulk.remove(), but that is also slow.(50 records are removing per second).

A TTL index will not speed up removal of documents if you already have an index that supports finding expired documents: the TTL thread still needs to find & remove matching documents so will be doing similar work to a bulk remove.

I would investigate why your current bulk remove operations are slow. For example, make sure you have an optimal index in place to find documents to remove and monitor your system resources (memory, I/O, network, ...) to ensure there aren't any obvious bottlenecks.

If you have a large number of documents that are ready to be removed when the TTL index is created, this could have a significant performance impact. Bulk remove queries with a supporting index would allow more control over the impact since you can add query criteria to restrict the range of documents matching each bulk deletion.

  But somewhere I read, TTL index and do scan interval every 1hour. So it'll remove in faster way.

That timing is incorrect: the TTL deletion task runs every 60 seconds. Based on an indexed date field the TTL monitor can either expire documents after a specified number of seconds has passed or expire documents at a specific clock time.

Assuming your documents have a range of expiry dates, once the initial removal of expired documents is complete a TTL index will be able to delete documents in smaller batches which will be less impactful than an infrequent bulk delete.

  But if I create this index in foreground, it'll lock the collection. So im thinking to do with the rolling index creation method.

Prior to MongoDB 4.2, a foreground index build on a populated collection will block all other operations on the database that holds that collection. For a populated collection in a production environment you will definitely want to use either a rolling index build or a background index build. The rolling index build ensures that only one of your replica set members is building an index and allows a foreground index build to complete faster, however this approach does include some risk of that member becoming stale while running in standalone mode.

MongoDB 4.2+ uses an optimised index build process that limits the lock scope to the affected collection and only holds an exclusive lock at the beginning and end of the index build. You can still use the rolling index build approach but there is no longer a foreground vs background index build distinction.

  If do like that, lets say on a 3 node replica set, detach the node3 and then create index. Once its created it'll start automatically remove the data.

The TTL index thread on replica set members only deletes documents when a member is in the primary state. Document deletes are replicated via the oplog so secondaries always have a consistent point of time with the current primary.

If you restart a replica set member in standalone mode, the TTL collection monitor will not be started (again, to keep the secondary state consistent).


Postgres to_tsvector is case sensitive on mac os

The ability to identify the case of characters depend on the LC_CTYPE of your database, which by default, depends on the environment in which the PostgreSQL instance has been created (with initdb).

For instance, on Ubuntu with PostgreSQL11:

tstc=# show lc_ctype;
 lc_ctype
----------
 C

tstc=# select to_tsvector('simple', 'БОЛЬШИЕ БУКВЫ');
      to_tsvector
-----------------------
 'БОЛЬШИЕ':1 'БУКВЫ':2
(1 row)

That's the result you got on your db on MacOS.

But when I'm logged to a different database, with an UTF-8 locale this time:

postgres=# show lc_ctype;
  lc_ctype
-------------
 fr_FR.UTF-8
(1 row)

postgres=# select to_tsvector('simple', 'БОЛЬШИЕ БУКВЫ');
      to_tsvector
-----------------------
 'большие':1 'буквы':2
(1 row)

Now letters are put in lower case.

The fix is to create the database with the correct LC_CTYPE. It cannot be changed afterwards. By default, this setting comes from template1, but it can be overriden by choosing template0, if template1 does not suit you, for instance:

CREATE DATABASE newDB lc_ctype='C.UTF-8' template template0;

The locale specified with lc_ctype must also be supported by the system. Check with locale -a or some equivalent if that doesn't work on MacOS.


Breakout Query Results By Year And Month

This is very klugey and I've quite a few gymnastics here give your initial code, but the final data set still needs to be ported using a PIVOT if you want ONLY one row. I would suggest you look at the PIVOT operator to understand to use this.

    Declare @Store1 Table (cardname varchar(200), saleamt float, saledate date)
Declare @TF Table (cardname varchar(200), vramt float ,saledate date)

Insert Into @TF (cardname, vramt, saledate) Values
('Red', 200.00, '20160101'), ('Pink', 300.00, '20160201'), ('Orange', 400.00, '20160301')

Insert Into @Store1 (cardname, saleamt, saledate) Values
('Red', 400.00, '20160201'), ('Pink', 800.00, '20160204'), ('Orange', 900.00, '20160301')

;with datavr as (
SELECT
T1.cardname,
SUM(ISNULL(T1.vramt,0)) AS vr,
0 As sa,
--saledate As saledate,
rtrim(cast(saledate as varchar(7))) as monyr
FROM @TF AS T1
GROUP BY T1.cardname, T1.saledate
),
datasa as (
Select
t2.cardname,
0 As vr,
SUM(ISNULL(t2.saleamt,0)) As sa,
--saledate as saledate,
rtrim(cast(saledate as varchar(7))) as monyr
FROM @Store1 t2
Group By t2.cardname, t2.saledate
)

select
    isnull(v.cardname, s.cardname) as cardname,
    isnull(v.monyr, s.monyr) as monyr,
    sum(v.vr) as vrsum,
    sum(s.sa) as sasum,
    (sum(isnull(v.vr,0)) - sum(isnull(s.sa,0)) / sum(v.vr)) as margin
from datavr v
full outer join datasa s
    on v.cardname = s.cardname
    and v.monyr = s.monyr
group by
    isnull(v.cardname, s.cardname),
    isnull(v.monyr, s.monyr)

More kluge, but if you want to use the unpretty PIVOT to really get that one row:

;with datavr as (
SELECT
T1.cardname,
SUM(ISNULL(T1.vramt,0)) AS vr,
0 As sa,
--saledate As saledate,
cast(datename(month,saledate)as varchar(3)) + '-' + right(year(saledate),2) as monyr
FROM @TF AS T1
GROUP BY T1.cardname, T1.saledate
),
datasa as (
Select
t2.cardname,
0 As vr,
SUM(ISNULL(t2.saleamt,0)) As sa,
--saledate as saledate,
cast(datename(month,saledate)as varchar(3)) + '-' + right(year(saledate),2) as monyr
FROM @Store1 t2
Group By t2.cardname, t2.saledate
)
, penult as (
select
    --isnull(v.cardname, s.cardname) as cardname,
    isnull(v.monyr, s.monyr) as monyr,
    --sum(v.vr) as vrsum,
    --sum(s.sa) as sasum,
(sum(isnull(v.vr,0)) - sum(isnull(s.sa,0))) / sum(isnull(v.vr,0)) as margin
from datavr v
full outer join datasa s
    on v.cardname = s.cardname
    and v.monyr = s.monyr
group by
    isnull(v.cardname, s.cardname),
    isnull(v.monyr, s.monyr)
)
select p.*
from penult d
pivot (
    max(margin)
    for monyr in ([Jan-16], [Feb-16], [Mar-16])
) p


Converting a 50+ column table into 1 much smaller table

You need N INSERT ... SELECT ... statements.  A one-time tedious task.


MariaDB is Forgetting Previous Auto Increment IDs

No, that is not the guarantee of AUTO_INCREMENT.  It only assures uniqueness.  It does not assure deleted number won't recur.

Old numbers will be resurrected in this scenario (and perhaps others):

DELETE the highest sequence number(s).  (Or TRUNCATE or ROLLBACK, ...)
Restart mysqld (or power failure, ...)
InnoDB, as it opens the table, computes the next id to use as MAX(id)

You need another mechanism, such as

CREATE TABLE seq (
    id INT UNSIGNED AUTO_INCREMENT NOT NULL,
    code CHAR(1) NOT NULL,
    PRIMARY KEY(id),
    UNIQUE(code)
) ENGINE=InnoDB;

INSERT INTO seq (code) VALUE ('a');   -- initialize only

To get next number:
INSERT INTO seq (code) VALUE ('a')
    ON DUPLICATE KEY UPDATE id = LAST_INSERT_ID(id+1);
then use LAST_INSERT_ID() to retrieve the new number: SELECT LAST_INSERT_ID();

Notes:

This allows multiple sequences, based on code.
The nums will be monotonically increasing (never dup, never decrease).
It will provide consecutive numbers (no gaps).
It will not back-fill.
It will survive crashes; deletes from your table; etc.
If you get a new number inside a transaction, replication could generate the ids not in chronological order.
If you get a new number outside a transaction (autocommit), you could 'burn' the number (create a gap) if certain failures occur.

(There may be something simpler.)


Rows already DELETEd on MySQL slave; How to DELETE on master?

Here is something quick and dirty you can do

Run the DELETE like this on the Master:

SET SQL_LOG_BIN=0;
DELETE FROM ... ;

The first line tells the DB Session not to record the SQL that follows. Thus, when you run the DELETE on the Master, the SQL will not be written in the Master's binary logs. Consequently, the Slave will never receive the DELETE in its relay logs.

This will only affect the session you use to run these two lines. All other DB Connections will replicate as usual. Once you disconnect, any new session will replicate properly.

Give it a Try !!!


Faster way of upgrading postgres to 10 from 9.5

I used a script for loading junk data in postgres. It created a lot of databases (about 500). So this was causing pg_dump to take so much time.

After populating only the relevant database (about 100 GB) I am seeing good performance (about 5 second with -k option).


How do I pass or fail a job in SQL Server agent based on a return value from a stored procedure?

Evaluate the result from the proc (with an IF statement) and then if you want it to fail the job execute a THROW command. See this article.


I would like to get rid of "Using temporary; Using filesort"

One of the problems I see is that you're using different GROUP BY and ORDER BY clauses. From the manual on how MySQL uses temporary tables:

  If there is an ORDER BY clause and a different GROUP BY clause, or if the ORDER BY or GROUP BY contains columns from tables other than the first table in the join queue, a temporary table is created.

As soon as you create a temporary table, it will need to be sorted according to your ORDER BY clause, indicated by 'using filesort'.

This execution plan at leasts uses the indexes to appropriately limit the number of rows found.

I would also look through the docs on ORDER BY optimization.


Is using SUM on multiple columns allowed in general SQL?

NOTE: I have no idea what 'GATE Mock Test Paper' is, or if that's supposed to have a bearing on the answer, so fwiw ...

As others have pointed out, technically all of the answers could be true ... though this will depend on the RDBMS (ie, flavor of SQL) as well as any configs/settings.

If the intention is to pick answers that are ANSI compliant, then I'd say 'III' is the correct answer since all non-aggregates should be part of the group by.  Then again, if you start looking at some of the nitty-gritty ANSI details you'll find this requirement can sometimes be relaxed.

As you'll find out, most RDBMS products don't enforce such a strict standard: all non-aggregates being a member of the group by clause

As Michael Kutz has mentioned, without 'A' and/or 'B' in the select/projection list, answers 'I' and 'II' will provide some confusing answers. [And if you ever find yourself working in a real world RDBMS environment you'll find developers writing these types of queries all the time, but then not being able to describe what it is they're asking for let alone being able to explain the results they end up with.]

Again, answer 'III' is the only one I'd say can be clearly explained ... what I'm looking for ... the results I end up with.


Why does my table hold duplicate primary keys?

It was all caused by the bug described and fixed in this release: http://www.postgresql.org/about/news/1506/

It caused us a lot of trouble!


Can error 1231 after importing a MySQL database be a compatibility issue?

It has to do with the max_allowed_packet on the MySQL server. It was set to 16M and when I upped it to 64M in my.cnf located in /private/etc/ (on Mac El Capitan) which was a copy of the my-huge.cnf.
After stopping the MySQL server and Apache and restarting them made my site work locally again and the error messages went away.


Can't create a backup of data of a single table

Just taking a look at postgres documentation and samples I've found this specific case:

  To specify an upper-case or mixed-case name in -t and related
  switches, you need to double-quote the name; else it will be folded to
  lower case (see Patterns). But double quotes are special to the shell,
  so in turn they must be quoted. Thus, to dump a single table with a
  mixed-case name, you need something like:

$ pg_dump -t "\"MixedCaseName\"" mydb > mytab.sql

You can read about it here.


User called mysql in fresh mariadb install on debian

MariaDB is a fork of MySQL, and as such it uses the latter name for many important things, including its command line tool mysql, daemon binarymysqld, and the user IDmysql` under whose authority the daemon runs. As mentioned in the comments, most Linux daemons run as their own dedicated user IDs for improved security.

Do not delete the user mysql.


Please have a look at IF statement at MySql Docs

  IF search_condition THEN statement_list
      [ELSEIF search_condition THEN statement_list] ...
      [ELSE statement_list] END IF

Or at IF() function:

IF(expr1,expr2,expr3)

If expr1 is TRUE (expr1 <> 0 and expr1 <> NULL),
IF() returns expr2. Otherwise, it returns expr3.

Your sentence should be something similar to:

SELECT
    CONCAT(user1.name, ' ', user2.name)
    IF(user1.salary < user2.salary, user1.salary, user2.salary) as userSalary
FROM `users` AS user1
LEFT JOIN users AS user2 ON user1.id = user2.id
ORDER BY user1.salary ASC


Undesirable Nest Loop vs. Hash Join in PostgreSQL 9.6

What's happening here is the Nested Loop is way off on one side. Nested Loops work really well when one side is very small, such as returning one row. In your query, the planner fumbles here and estimates that a Hash Join will return just one row. Instead, that Hash Join (property_id = id) returns 1,338 rows. This forces 1,338 loops to run on the other side of the Nested Loop which already has 3,444 rows. That's a hella-lot when you're only expecting one (which isn't even much of a "loop"). Anywayy..

Further examination as we move down shows that the Hash Join is really borked by the estimations arising from this,

Filter: (((properties."COMPOSITION")::text ~~ 'Mo%'::text) AND (((properties."NAME")::text ~~ '%VASP-ase-preopt%'::text) OR (properties."CALCULATOR_ID" = ANY ('{7,22,25}'::integer[]))))

PostgreSQL expects that to return one row. But it doesn't. And, that's really your problem. So some options here, that don't involve taking out a sledge hammer and disabling nested_loop

You can add an index or two to properties to help it potentially skip the seq scan entirely, or better estimate the return.

CREATE INDEX ON properties USING ( "TYPE_ID", "CALCULATOR_ID" );
-- the gist_trgm_ops may or may not be needed depending on selectivity of above.
CREATE INDEX ON properties USING GIST (
  "COMPOSITION" gist_trgm_ops,
  "NAME"        gist_trgm_ops
);
ANALYZE properties;

Alternatively, you can move the properties stuff to a CTE or subselect with OFFSET 0 which creates a fence.

WITH t AS (
  SELECT *
  FROM properties.
  WHERE "COMPOSITION" LIKE 'Mo%'
  AND (
    "NAME" LIKE '%VASP-ase-preopt%'
    OR "CALCULATOR_ID" IN (7,22,25)
  )
  AND "TYPE_ID" IN (6)
)
SELECT * FROM structures
JOIN t ON (
  structures.id = entries_properties.entry_id
)


What you want is SELECT ... FOR UPDATE from within the context of a transaction.  SELECT FOR UPDATE puts an exclusive lock on the rows selected, just as if you were executing UPDATE.  It also implicitly runs in READ COMMITTED isolation level regardless of what the isolation level is explicitly set to.  Just be aware that SELECT ... FOR UPDATE is very bad for concurrency and should only be used when absolutely necessary.  It also has a tendency to multiply in a codebase as people cut and paste.

Here's an example session from the Sakila database which demonstrates some of the behaviors of FOR UPDATE queries.

First, just so we're crystal clear, set the transaction isolation level to REPEATABLE READ.  This is normally unnecessary, as it is the default isolation level for InnoDB:

session1> SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
session1> BEGIN;
session1> SELECT first_name, last_name FROM customer WHERE customer_id = 3;
+------------+-----------+
| first_name | last_name |
+------------+-----------+
| LINDA      | WILLIAMS  |
+------------+-----------+
1 row in set (0.00 sec)

In the other session, update this row.  Linda got married and changed her name:

session2> UPDATE customer SET last_name = 'BROWN' WHERE customer_id = 3;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Back in session1, because we were in REPEATABLE READ, Linda is still LINDA WILLIAMS:

session1> SELECT first_name, last_name FROM customer WHERE customer_id = 3;
+------------+-----------+
| first_name | last_name |
+------------+-----------+
| LINDA      | WILLIAMS  |
+------------+-----------+
1 row in set (0.00 sec)

But now, we want exclusive access to this row, so we call FOR UPDATE on the row.  Notice that we now get the most recent version of the row back, that was updated in session2 outside of this transaction.  That's not REPEATABLE READ, that's READ COMMITTED

session1> SELECT first_name, last_name FROM customer WHERE customer_id = 3 FOR UPDATE;
+------------+-----------+
| first_name | last_name |
+------------+-----------+
| LINDA      | BROWN     |
+------------+-----------+
1 row in set (0.00 sec)

Let's test out the lock set in session1.  Note that session2 cannot update the row.

session2> UPDATE customer SET last_name = 'SMITH' WHERE customer_id = 3;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

But we can still select from it

session2> SELECT c.customer_id, c.first_name, c.last_name, a.address_id, a.address FROM customer c JOIN address a USING (address_id) WHERE c.customer_id = 3;
+-------------+------------+-----------+------------+-------------------+
| customer_id | first_name | last_name | address_id | address           |
+-------------+------------+-----------+------------+-------------------+
|           3 | LINDA      | BROWN     |          7 | 692 Joliet Street |
+-------------+------------+-----------+------------+-------------------+
1 row in set (0.00 sec)

And we can still update a child table with a foreign key relationship

session2> UPDATE address SET address = '5 Main Street' WHERE address_id = 7;
Query OK, 1 row affected (0.05 sec)
Rows matched: 1  Changed: 1  Warnings: 0

session1> COMMIT;

Another side effect is that you greatly increase your probability of causing a deadlock.

In your specific case, you probably want:

BEGIN;
SELECT id FROM `items` WHERE `status`='pending' LIMIT 1 FOR UPDATE;
-- do some other stuff
UPDATE `items` SET `status`='working', `updated`=NOW() WHERE `id`=<selected id>;
COMMIT;

If the "do some other stuff" piece is unnecessary and you don't actually need to keep information about the row around, then the SELECT FOR UPDATE is unnecessary and wasteful and you can instead just run an update:

UPDATE `items` SET `status`='working', `updated`=NOW() WHERE `status`='pending' LIMIT 1;

Hope this makes some sense.


How MySQL Table cache hitrate should be calculated?

I could be that the plugin is looking at deltas, and the dump had to open all the tables at least once, thereby running through the cache and getting a burst of "misses".

The "hits" are quite high relative to the other values, so I don't see a real 'problem'.  But you could increase table_open_cache and table_open_cache_instances.

What was Max_used_connections?  I guess that number was rather high, and the many connections were opening lots of tables, thereby reaching up to (and past) 2000.  Later, when the dump came along, many of the little-used tables had been bumped out of cache, hence a low hit rate.

Suggest you grab those GLOBAL STATUS readings before and after a dump.  This may (or may not) confirm my guesses.


Restore MySQL tables with data folder only?

You cannot move a single table or database around via the filesystem.

If you have the entire folder, then there is a pretty good chance of being able to move (or restore) all databases.

ibdata1 must be included
Same version of MySQL.
directory and file permissions need to be maintained
and probably other things.


Migrating MySQL 5.0 to 5.5 - simple databases

Make full back (preferably mysqldump)
Follow official guide : http://dev.mysql.com/doc/refman/5.5/en/upgrading.html

and also this one http://dev.mysql.com/doc/refman/5.0/en/upgrading-from-early-5-0.html


DB2: What's the fast way to drop a big partitioned table?

You could issue a TRUNCATE against the table. Or detach the partitions and do the same.

There are also some pointers from DB2 veteran Ember Crooks about deleting data in one of her blog entries. I find her tips and tricks quite helpful.


InnoDB: Is a covering index in the form of (PK, col1, col2, col3) redundant?

PRIMARY index in InnoDB is a tree where the primary key field (property) is a key and remaining columns are the value. In a secondary index the key are the index fields and the value is a primary key. In your case the secondary index has fields (property, value, and property). (property, value) is a key and property is a value.
The secondary index is larger and redundant, so MySQL make a right decision to use the PRIMARY index.

Check out a presentation I gave on InnoDB indexes structure for more details.
https://www.slideshare.net/akuzminsky/efficient-indexes-in-mysql


Any gotchas with changing IP address of Postgres Master?

There are several places you'd want to change the IP address for a PostgreSQL primary server. Most of them are actually outside of PostgreSQL itself or its configuration, though.
You already mentioned the pg_hba.conf so that doesn't need to be mentioned.

Tables that you have inet types for, or other columns you store IP addresses in. If you found IP addresses in $PGDATA/base via a grep, then that probably means that you have IP address entries in your tables, and looking at the information_schema.columns table for those would be most helpful.
Really, needing to touch anything inside of a PostgreSQL data directory outside of .conf files is not recommended at all.

postgresql.conf listen_addresses entries.

Any recovery.conf files that have the primary's IP address referenced for streaming replication.

IP addresses listed in pgbouncer or pgpool-II configurations.

IPTables or other firewall rules on your servers.

Application configurations.

Remote backup scripts.

These are just highlights.
A comprehensive list will require you going patiently through everything in your setup. Creating a good checklist of what needs to be changed in the event of a failover (and keeping it up to date!) will help you immensely in the future, and improve your disaster recovery and high-availability readiness.
Hope that helps. =)


SQL Server Database Table Partitioning Consideration

The real use case for table partitioning is fast load and unload of data.

If your warehouse tables and staging tables are in the same database, and have the same schema, you will be able to swap a partition from staging to warehouse very quickly.  Similarly, when data has reached its retention expiry date and must be purged, it is very quick to delete a whole partition of data.  This page and its ilk will give some pointers.

As each partition can be directed to a different filegroup your backup & recovery cycles could be shortened at the cost of increased complexity.  For example, load one company's data, process it, then take a backup of the file(s) that hold that company.  Perhaps several companies could be processed in parallel, knowing that file contention will be reduced through partitioning?

Indexes can be partitioned as well as the tables.  Index maintenance can be performed one partition at a time.  This could reduce contention and overall load on your system.

Using partitions solely for performance enhancement can be problematic. Every query will have to have the partition key in the predicate, as a minimum.  Sometimes the optimiser chooses not to perform partition elimination even then.

Partitioning is no free lunch.  It has costs as well as benefits.  You must consider both sides and test well.  As so often in DB design "it depends."


Effect of changing data types with row compression enabled

As the documentation you alredy have linked states, ROW compression uses only the bytes that are needed. Once ROW compression is in use, the CPU cycles used to convert from/to int or bigint are the same: I would not worry about that.

BTW, if you're not sure whether int/bigint has an impact on the database size or not (it hasn't), you can see for yourself with a quick and dirty repro:

USE tempdb;
GO

CREATE TABLE SomeTable (
    SomeColumn bigint
)
GO

ALTER TABLE [dbo].[SomeTable] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = NONE);

INSERT INTO SomeTable
SELECT TOP 10000000 ROW_NUMBER() OVER (ORDER BY(SELECT NULL))
FROM sys.all_columns AS A1
CROSS JOIN sys.all_columns AS A2;
GO

-- Rebuild the heap, so that pages compact nicely
ALTER TABLE dbo.SomeTable
REBUILD
WITH
(
    MAXDOP = 1,
    ONLINE = OFF,
    FILLFACTOR = 100,
    PAD_INDEX = OFF
);
GO

SELECT SUM(page_count)
FROM sys.dm_db_index_physical_stats(
        DB_ID(),
        OBJECT_ID('SomeTable'),
        DEFAULT,
        DEFAULT,
        'detailed'
    ) AS ips;

-- 21009 pages used

ALTER TABLE [dbo].[SomeTable] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = ROW);
GO

SELECT SUM(page_count)
FROM sys.dm_db_index_physical_stats(
        DB_ID(),
        OBJECT_ID('SomeTable'),
        DEFAULT,
        DEFAULT,
        'detailed'
    ) AS ips;

-- 13587 pages used

ALTER TABLE SomeTable ALTER COLUMN SomeColumn int;

-- Rebuild the heap, so that pages compact nicely
ALTER TABLE dbo.SomeTable
REBUILD
WITH
(
    MAXDOP = 1,
    ONLINE = OFF,
    FILLFACTOR = 100,
    PAD_INDEX = OFF
);
GO

SELECT SUM(page_count)
FROM sys.dm_db_index_physical_stats(
        DB_ID(),
        OBJECT_ID('SomeTable'),
        DEFAULT,
        DEFAULT,
        'detailed'
    ) AS ips;

-- 13587 pages used (same as bigint)


giving users select-only permissions of database objects

It sounds like you simply want to give those users db_datareader and nothing else. This is pretty common.

  Members of the db_datareader fixed database role can read all data
  from all user tables.

See the role explained in the MS DOCS


How to check what type of lock an UPDATE statement uses?

You can see the locks from DMV called sys.dm_tran_locks. The type is of lock can be seen from resource_type column (f. ex. OBJECT, PAGE etc).

More information is available in MSDN

Also, maybe this query by Jonathan Kehayias is useful:

SELECT dm_tran_locks.request_session_id,
       dm_tran_locks.resource_database_id,
       DB_NAME(dm_tran_locks.resource_database_id) AS dbname,
       CASE
           WHEN resource_type = 'object'
               THEN OBJECT_NAME(dm_tran_locks.resource_associated_entity_id)
           ELSE OBJECT_NAME(partitions.OBJECT_ID)
       END AS ObjectName,
       partitions.index_id,
       indexes.name AS index_name,
       dm_tran_locks.resource_type,
       dm_tran_locks.resource_description,
       dm_tran_locks.resource_associated_entity_id,
       dm_tran_locks.request_mode,
       dm_tran_locks.request_status
FROM sys.dm_tran_locks
LEFT JOIN sys.partitions ON partitions.hobt_id = dm_tran_locks.resource_associated_entity_id
JOIN sys.indexes ON indexes.OBJECT_ID = partitions.OBJECT_ID AND indexes.index_id = partitions.index_id
WHERE resource_associated_entity_id > 0
  AND resource_database_id = DB_ID()
ORDER BY request_session_id, resource_associated_entity_id


The first thought that came to mind was "Wow !!! You must have rather big transactions."
According to mysqlperformanceblog.com's Reasons for run-away main Innodb Tablespace, these are the main issues that cause ibdata1 to grow:

Lots of Transactional Changes
Very Long Transactions
Lagging Purge Thread

The undo logs inside ibdata1 will hold lots of info to support transaction isolation levels. Since you are using READ-COMMITTED, this growth can be more significant.
I have discussed this  before in the DBA StackExchange

Aug 21, 2015 : Transactional DDL workflow for MySQL
Jun 16, 2014 : MySQL Index creation failing on table is full
Apr 23, 2013 : How can Innodb ibdata1 file grows by 5X even with innodb_file_per_table set?
... and more

SUGGESTION #1
Since you are using MariaDB, please look into configuring the undo logs outside of the system tablespace. Please read the MariaDB docs on innodb_undo_directory and innodb_undo_tablespaces and go from there.
SUGGESTION #2
You may have to mysqldump are your data and load it into a new installation of MariaDB. See my posts in How do I shrink the innodb file ibdata1 without dumping all databases? for other ideas. Either way, this will take some work !!!


Inventory database maintaining remaining qty of an item

I'm not a sqlite expert, but I definitely think your database should be normalized further and you should store Quantity for an item on a separate table than the Sales table.

Even though you've tagged your RDBMS as sqlite, I'd recommend having a look at the AdventureWorks table definitions to get an idea of how you might split your data attributes out to reduce data redundancy.  In the AdventureWorks database, the Quantity is stored in ProductInventory table.

At a minimum, you could start with:

Customer Table - Contains current customer information. Customers are categorized by type: individual consumer or retail store.
SalesOrderHeader Table - Contains the general, or parent, sales order information. The specific products associated with the sales order are stored in SalesOrderDetail Table.
SalesOrderDetail Table - Contains individual products associated with a specific sales order. A sales order may include orders for multiple products. The general, or parent, information for each sales order is stored in SalesOrderHeader. Each product that is ordered, or child, is stored in SalesOrderDetail.
Product Table - Contains the products sold or used in the manufacturing of sold products.
ProductInventory Table - Contains the product inventory information.

Be sure to check out the other AdventureWorks table definitions as they may give you additional ideas about splitting out your data.


Query where there are only two rows with specific data

You don't need self-joining or subqueries at all. This query does the trick:

select
id
from
your_table t
group by id
having sum(name = 'John') + sum(name = 'Jill') = 2 and count(*) = 2;

see it working live in an sqlfiddle

name = 'whatever' in the sum() function returns 1 or 0, true or false.


Distributing a single table across multiple disks of different performance?

How to put data on a different disk: (using tablespaces)

CREATE TABLESPACE fastspace LOCATION '/srv/fast_disk/postgresql/data';

https://www.postgresql.org/docs/10/static/manage-ag-tablespaces.html

.

How to make two tables behave like one: (SQL inheritance)

CREATE TABLE TABLE_FAST () inherits TABLE_SLOW TABLESPACE fastspace;

You will also need to add constraints and indices as apropriate to this new table.

https://www.postgresql.org/docs/10/static/tutorial-inheritance.html

Now selects from TABLE_SLOW will also see the data in TABLE_FAST

you may also benefit from implementing constraint exclusion (also described on that page), but the index on Loaded_Time (which I am assuming you have) should give a good speed increase itself

.

How to move data data: (CTE delete returning)

eg: 3 days old

with deleted as (
     delete from only TABLE_FAST
     where Loaded_Time < 'now()' - '3 days'::interval
     returning * )
insert into slow_table select * from deleted;


The problem however is, that it doesn't allow updating such row. The updated row seems to be considered a doublet.

That's one of the problems.

  Is there a way to fix this or is it a conceptual problem CHECK constraints just don't cover?

Yes, use "on insert" and "on update" triggers,
and yes "check" constraints are unsuited.

you'll also need an "on update" trigger on "attribute" so that changes
of singular are verified against the existing data.

A different approach would be to have partial unique indices generated by a trigger that tracks inserts, deletes, and updates on attribute.

Eg. if you have a attribute day_of_birth which is singular you would do

CREATE UNIQUE INDEX "object_day_of_birth_unique" ON objects(object) WHERE attribute='day_of_birth';

This leverages something that the database engine is good at, and so will give a slight performance boost, but as you're using EAV I suspect perfromance is not important.

when writing such a trigger be sure to use quote_literal and quote_ident where apropriate in execute expressions.


How to categorize rows by SUM of a column in mysql?

One option might be using user-variables:

SET @folder = 1;
SET @items = 0;
SELECT id, num_items,
  (SELECT IF(((@items:=@items+num_items)>100), @folder:=@folder+1,   @folder)) as folder,
  IF(@items>100,@items:=0,@items) as checkItems
FROM foo;

Here's an sql fiddle of it, though your example id=4 does not match. In the first block, it's 44, in the expected output block it is 41, so the folders are off.

 Adding a caveat here. According to the documentation on user variables:

  As a general rule, you should never assign a value to a user variable and read the value within the same statement. You might get the results you expect, but this is not guaranteed.

Just keep that in mind.

Edit by RolandoMySQLDBA (2012-12-17 14:30 EDT)

So as to be sure of what the user variables look like, perhaps have each of the user variables printed along the way : ( See Example )

SET @folder = 1;
SET @items = 0;
SELECT id, num_items,
  @folder as CurrentFolder_Before,
  @items as CurrentItems_Before,
  (SELECT IF(((@items:=@items+num_items)>100), @folder:=@folder+1,   @folder)) as folder,
  IF(@items>100,@items:=0,@items) as checkItems,
  @folder as CurrentFolder_After,
  @items as CurrentItems_After
FROM foo;

DTest gets a +1 for his application of user variables.


Service Broker - Communication not happening between servers despite ssbdiagnose saying all is well

Is the database master key encrypted by the service master key? If not, the db engine has no way to automatically open it. To check, take a look in sys.asymmetric_keys, specifically the pvt_key_encryption_type_desc column. If you find that it's not, you can make it so by doing

alter master key add encryption by service master key


One way Synched Dev Database from Production Database

You've got yourself one heck of a requirement, none of which I would ever support.  Regardless, one solution I can think of is to enable transactional replication on the production side, which can replicate DML and DDL statements asynchronously.  In our production environment, we are at 5 src latency.  I would also double check documentation to ensure all DDL statements you need are supported.

To capture DDL statements on the development side, you can create DDL triggers to log the schema changes. As long as you can distinguish between a replicated change and development change, you can then extract the changes from the log table and apply then to production.


exporting some table table from oracle DB to Mysql DB

Oracle export tools use their own proprietary format that MySQL can not process.

Just unload your data to CSV or insert statements with SQL Developer, then load the result in MySQL

https://stackoverflow.com/questions/4168398/how-to-export-query-result-to-csv-in-oracle-sql-developer


Can I disable journalling to improve performance

It should be noted that on any MongoDB system with significant write volume, most of the continuous/ongoing disk usage will come from the journal - after all it syncs to disk every 100ms - so this observation is not unusual.

If you truly can wipe out 16 shards and restore in 30 seconds (which is impressive), then you can eliminate the journal with minimal risk, yes, but you could also be less drastic.  But, before you do, consider that getting rid of the journal means that a non-clean restart of a mongod leaves your data in an unknown state (it might be OK, it might not).  You will want to be sure you have ways to detect any discrepancies (that might not present as an outage) if you run in this mode.

It's also worth noting that this would essentially be an unsupported mode of operation for production, so getting any help with a problem will likely mean having to turn journaling back on.

Finally, before you turn off the journal, consider these somewhat less drastic steps first:

Move the journal to its own disk/volume - it's a standard write ahead journal meaning sequential writes - very different to the usual data writes, and so having it on its own disk/volume can be significantly more efficient, especially if your other IO is more randomly distributed (common with MongoDB).  Finally, as of writing this, you should never have the journal on an NFS mount - it does not play well (may not apply here, but good advice in general).
Change the commit interval of the journal - the default is 100ms, but you can increase to 300ms (see the docs here) - this is a minor tweak but may help
Although it is not directly related to journaling, the other competing writes will come from the fsync process for data (every 60 seconds by default).  If you alter this you may improve that performance also and remove stress from the disk also.  Again, see the docs on syncdelay for details.
Take a look at how you are writing data - sometimes large array updates, large or un-needed indexes etc. can translate into more writes than they should.


sql server service broker queue pass parameter to stored procedure

No, activation stored procedures are called "in a vacuum" so they don't allow passing parameters (there's "nothing" calling it) nor is there even a user context in which to execute, which is why the EXECUTE AS syntax exists and is required for activation.

If you need to abstract the logic, it can be separated into two procedures. The activation procedure is parameterless, but it can call another with parameters. Something like this:

CREATE PROCEDURE [dbo].[CentralizedLogic]
(
    @p1 varchar(MAX) = NULL
)
AS
BEGIN
    ...
END

CREATE PROCEDURE [dbo].[QueueActivator]
AS
BEGIN
    EXEC [dbo].[CentralizedLogic] @p1 = 'blah';
END

CREATE QUEUE [dbo].[MyQueue]
    WITH
        ...
        ACTIVATION
        (
            ...
            PROCEDURE_NAME = [dbo].[QueueActivator]
            ...
        );


How do I guarantee inventory numbers are unique across all tables?

First insert a row in a table of "objects" that includes an AUTO_INCREMENT PRIMARY KEY.  Get the id by using LAST_INSERT_ID().

Then insert whatever rows you need into other tables.  Use the id from the first step as the unique id.

This works for MySQL and MariaDB.  Other databases have the concept of a SEQUENCE.  Such can be simulated in MySQL/MariaDB, essentially by using the 'first' step I gave you.  So, if you really need to be somewhat db-independent, start with a SEQUENCE emulation.

(My opinion:  There are so many differences between RDBMS vendors that it is folly to try to have one code for all.)


Distribute values equally in count and sum to a set of users

Do we have any idea of the distribution of the values ?

A super basic way of handling that, assuming the distribution of the different values of amount are more or less uniform could be split values into N / number_of_values parts (using ntile() function and ordering by amount)

So something like :

select  amount,
        row_number() over(partition by id order by amount) as id
from (
    select amount, ntile(4) over(order by amount) as id
    from test
    )t
order by 2,1

With the sample data you gave, we get sum 394665.71, 403385.26, 416126.01, 435642.01, 474667.67

Not exactly what you expected but not so far.

A less super basic way could be trying to split in a different way : after the ntile statement, we have 4 buckets of 5 values, the highest values in bucket #1, the lowest in #2.

Let add the highest value of bucket #1 with the lowest of #2, with
the highest of #3, with the lowest of #4.
And the second highest value of bucket #1 with the second lowest of #2, with the second highest of #3, with the second lowest of #4.
Etc...

So :

select  amount,
        case
            when id % 2 != 0 then row_number() over(partition by id order by amount)
            else row_number() over(partition by id order by amount desc)
        end as uid
from (
    select amount, ntile(4) over(order by amount) as id
    from test
    ) t
order by 2,1

With sum 427702.27, 409754.4, 416126.01, 429272.87, 441631.11

For something more accurate, this is more for statistician experts I think.


mysqldump

will dump the CREATE VIEW.  However, it shows it twice.  See the second version for the correct output.

Including the option --no-data is optional.

You do need SHOW VIEW permissions to when using mysqldump or SHOW CREATE VIEW.


What methodology is being used to poll SQL instances when using sqlcmd with the -L switch?

SQL Server discovery is attempted using UDP port 1434.

You'd need to get UDP traffic destined for 255.255.255.255 on port 1434 opened in your local router to see servers from other subnets.

See http://www.mssqltips.com/sqlservertip/2057/why-listing-all-of-your-sql-servers-does-not-always-work/ for more info.


Can I have an InnoDB master and MyISAM slaves with Full-Text for searching?

Absolutely !!!
Just run ALTER TABLE tblname ENGINE=MyISAM; against all tables on the Slave that you want to have the FULLTEXT index. Afterwards, you can run ALTER TABLE tblname ADD FULLTEXT (column[,column]);.
Please be very careful not to run DDL against those tables in the Master that are unique to InnoDB that will replicate to the Slave.
I have suggested using a different Storage Engine on a Slave in my past posts:

Jun 01, 2011 : What can we do in MySQL 5.0 Replication to address bandwidth concerns?
Nov 16, 2011 : Log MySQL DB changing queries and users
Mar 13, 2012 : https://dba.stackexchange.com/questions/14943/setting-up-mysql-across-a-series-of-production-servers/14945#14945


Yes, of course it's possible. Microsoft's done it in their datacentres, you can do it in yours. It may take some time and effort however. :-)

There is not yet an Azure on-prem product. I've read rumours of one due in the next year or so. Either way, it will take a while to be fully functional and licensing not going to be cheap.

Best bet would be to roll out the DevOps toolkit. Infrastructure as code; desired state configuration; pre-speced VM images produced from a pick list; automated on-demand app deployment. All the bits are around and open source. Linking them will take a few dedicated months. Getting management approval, and educating users to take responsibility for their usage may take longer.

In July 2017 Microsoft announced Azure Stack, which makes their cloud management software available for on-premises deployment. Included in this is SQL databases.


Restoring with replace going incredibly quickly

The RESTORE DATABASE ... WITH (REPLACE) does not have to create the 2 TB .mdf file.  This is why it is so much quicker.   You may want to look into instant file initialization.


Get name and definition of all table types

You can see the list of names:

SELECT name FROM sys.table_types;

To see the definition, you can go to Object Explorer:

If you want to automate this, at least for the columns / data types (this leaves out indexes, keys, etc.) you can build the following:

DECLARE @sql nvarchar(max) = N'',
  @stub nvarchar(max) = N'SELECT N''$--obj--$'',
   name, system_type_name
   FROM sys.dm_exec_describe_first_result_set(''DECLARE
   @tvp $--obj--$; SELECT * FROM @tvp;'',null,null)
   ORDER BY column_ordinal;';

SELECT @sql += REPLACE(@stub, N'$--obj--$',
  QUOTENAME(s.name) + N'.' + QUOTENAME(t.name))
FROM sys.table_types AS t
INNER JOIN sys.schemas AS s
ON t.[schema_id] = s.[schema_id];

EXEC sys.sp_executesql @sql;

This produces output like the following (one resultset for each table type):

Of course, it will error if you have named a table type with an apostrophe - don't do that).


How can I find the longest record (in characters) for each column in a table in PostgreSQL

The below worked for what I needed:

SELECT
        MAX(char_length(column1)) as column1,
        MAX(char_length(column2)) as column2
FROM
        schema.table


How to use SQL CASE result to compare a column and get another case result

Your code looks more like postgre, than MySQL
Also for your information, you should not use spaces in column names and leave the desired output to the gui, that save a lot of time
I used the inner SubSELECT to gather the data and put the second CASE in the outer
SELECT
SELECT
    `user email`,
    `user join date`,
    location,
    iso_country,
    iso_country_id,
    users.country,
    users.event_type,
    desirecountry,
    CASE
        WHEN desirecountry = iso_country THEN desirecountry
        ELSE NULL
    END AS Final_Country_Map_Name
FROM
    (SELECT
        subs.`user email`,
            subs.`user join date`,
            subs.location,
            iso.country_name AS iso_country,
            iso.country_id AS iso_country_id,
            users.country,
            users.event_type,
            CASE
                WHEN subs.location = iso.country_name THEN iso.country_name
                ELSE users.country
            END AS desirecountry
    FROM
        subs
    LEFT JOIN iso ON subs.location = iso.country_name
    LEFT JOIN users ON users.user_id = subs.`user email`)


Sorry, to tell you: Check Constraints are not implemented in MySQL.

You are probably better off writing a BEFORE INSERT,BEFORE UPDATE trigger that would check the count using something like

SELECT COUNT(1) into UnitSold
FROM sold WHERE partId = OLD.partId

SCENARIO : Implement a check constraint by means of a trigger

We have a simple sold table that look like this:

use test
DROP TABLE IF EXISTS `sold`;
CREATE  TABLE `sold` (
`part_ID` INT(11) NOT NULL ,
`date` DATE NOT NULL ,
`name` VARCHAR(20),
 PRIMARY KEY (`part_ID`, `date`)
);

Let create it:

mysql> use test
Database changed
mysql> DROP TABLE IF EXISTS `sold`;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE  TABLE `sold` (
    -> `part_ID` INT(11) NOT NULL ,
    -> `date` DATE NOT NULL ,
    -> `name` VARCHAR(20),
    ->  PRIMARY KEY (`part_ID`, `date`)
    -> );
Query OK, 0 rows affected (0.06 sec)

mysql>

Here is the trigger to only allow 3 sales per part_ID

DELIMITER $$
CREATE TRIGGER sold_beforeinsert_trigger
BEFORE INSERT ON sold FOR EACH ROW
BEGIN
    DECLARE UnitsSold INT;

    SELECT COUNT(1) INTO UnitsSold FROM sold WHERE part_ID = NEW.part_ID;
    IF UnitsSold >= 3 THEN
        SELECT COUNT(gummybears) INTO UnitsSold
        FROM information_schema.schemata;
    END IF;

END; $$
DELIMITER ;

Let's create it:

mysql> DELIMITER $$
mysql> CREATE TRIGGER sold_beforeinsert_trigger
    -> BEFORE INSERT ON sold FOR EACH ROW
    -> BEGIN
    ->     DECLARE UnitsSold INT;
    ->
    ->     SELECT COUNT(1) INTO UnitsSold FROM sold WHERE part_ID = NEW.part_ID;
    ->     IF UnitsSold >= 3 THEN
    ->         SELECT COUNT(gummybears) INTO UnitsSold
    ->         FROM information_schema.schemata;
    ->     END IF;
    ->
    -> END; $$
Query OK, 0 rows affected (0.08 sec)

mysql> DELIMITER ;
mysql>

Here is some sample data to load and display:

SET @DaysBack = 0;
SET @DaysBack = @DaysBack + 1; SET @GivenDate = DATE(NOW() - INTERVAL @DaysBack DAY);
INSERT INTO `sold` VALUES ( 1, DATE(@GivenDate), 'rolando');
INSERT INTO `sold` VALUES ( 2, DATE(@GivenDate), 'pamela');
INSERT INTO `sold` VALUES ( 3, DATE(@GivenDate), 'dominique');
INSERT INTO `sold` VALUES ( 4, DATE(@GivenDate), 'diamond');
SELECT * FROM sold;

Let's run this:

mysql> SET @DaysBack = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @DaysBack = @DaysBack + 1; SET @GivenDate = DATE(NOW() - INTERVAL @DaysBack DAY);
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO `sold` VALUES ( 1, DATE(@GivenDate), 'rolando');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO `sold` VALUES ( 2, DATE(@GivenDate), 'pamela');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO `sold` VALUES ( 3, DATE(@GivenDate), 'dominique');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO `sold` VALUES ( 4, DATE(@GivenDate), 'diamond');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM sold;
+---------+------------+-----------+
| part_ID | date       | name      |
+---------+------------+-----------+
|       1 | 2013-03-10 | rolando   |
|       2 | 2013-03-10 | pamela    |
|       3 | 2013-03-10 | dominique |
|       4 | 2013-03-10 | diamond   |
+---------+------------+-----------+
4 rows in set (0.00 sec)

mysql>

Let run it again without the first line

mysql> SET @DaysBack = @DaysBack + 1; SET @GivenDate = DATE(NOW() - INTERVAL @DaysBack DAY);
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO `sold` VALUES ( 1, DATE(@GivenDate), 'rolando');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO `sold` VALUES ( 2, DATE(@GivenDate), 'pamela');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO `sold` VALUES ( 3, DATE(@GivenDate), 'dominique');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO `sold` VALUES ( 4, DATE(@GivenDate), 'diamond');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM sold;
+---------+------------+-----------+
| part_ID | date       | name      |
+---------+------------+-----------+
|       1 | 2013-03-10 | rolando   |
|       2 | 2013-03-10 | pamela    |
|       3 | 2013-03-10 | dominique |
|       4 | 2013-03-10 | diamond   |
|       1 | 2013-03-09 | rolando   |
|       2 | 2013-03-09 | pamela    |
|       3 | 2013-03-09 | dominique |
|       4 | 2013-03-09 | diamond   |
+---------+------------+-----------+
8 rows in set (0.00 sec)

mysql>

and again

mysql> SET @DaysBack = @DaysBack + 1; SET @GivenDate = DATE(NOW() - INTERVAL @DaysBack DAY);
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO `sold` VALUES ( 1, DATE(@GivenDate), 'rolando');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO `sold` VALUES ( 2, DATE(@GivenDate), 'pamela');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO `sold` VALUES ( 3, DATE(@GivenDate), 'dominique');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO `sold` VALUES ( 4, DATE(@GivenDate), 'diamond');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM sold;
+---------+------------+-----------+
| part_ID | date       | name      |
+---------+------------+-----------+
|       1 | 2013-03-10 | rolando   |
|       2 | 2013-03-10 | pamela    |
|       3 | 2013-03-10 | dominique |
|       4 | 2013-03-10 | diamond   |
|       1 | 2013-03-09 | rolando   |
|       2 | 2013-03-09 | pamela    |
|       3 | 2013-03-09 | dominique |
|       4 | 2013-03-09 | diamond   |
|       1 | 2013-03-08 | rolando   |
|       2 | 2013-03-08 | pamela    |
|       3 | 2013-03-08 | dominique |
|       4 | 2013-03-08 | diamond   |
+---------+------------+-----------+
12 rows in set (0.00 sec)

mysql>

and again

mysql> SET @DaysBack = @DaysBack + 1; SET @GivenDate = DATE(NOW() - INTERVAL @DaysBack DAY);
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO `sold` VALUES ( 1, DATE(@GivenDate), 'rolando');
ERROR 1054 (42S22): Unknown column 'gummybears' in 'field list'
mysql> INSERT INTO `sold` VALUES ( 2, DATE(@GivenDate), 'pamela');
ERROR 1054 (42S22): Unknown column 'gummybears' in 'field list'
mysql> INSERT INTO `sold` VALUES ( 3, DATE(@GivenDate), 'dominique');
ERROR 1054 (42S22): Unknown column 'gummybears' in 'field list'
mysql> INSERT INTO `sold` VALUES ( 4, DATE(@GivenDate), 'diamond');
ERROR 1054 (42S22): Unknown column 'gummybears' in 'field list'
mysql> SELECT * FROM sold;
+---------+------------+-----------+
| part_ID | date       | name      |
+---------+------------+-----------+
|       1 | 2013-03-10 | rolando   |
|       2 | 2013-03-10 | pamela    |
|       3 | 2013-03-10 | dominique |
|       4 | 2013-03-10 | diamond   |
|       1 | 2013-03-09 | rolando   |
|       2 | 2013-03-09 | pamela    |
|       3 | 2013-03-09 | dominique |
|       4 | 2013-03-09 | diamond   |
|       1 | 2013-03-08 | rolando   |
|       2 | 2013-03-08 | pamela    |
|       3 | 2013-03-08 | dominique |
|       4 | 2013-03-08 | diamond   |
+---------+------------+-----------+
12 rows in set (0.00 sec)

mysql>

AH HA !!! The trigger stopped the INSERT cold in its tracks where a part_ID had three rows in the sold table. MySQL Triggers are not the greatest. Notice I had to SELECT the count of a column that does not exist in information_schema.schemata to cause the trigger to abort.


What you could do is a design like this:

Product(id,name,description,price,productType)
ProductAttribute(productId,attributeName,value)

Then you would have the possibility to filter for all attributes that are currently used in your database.

If you want to be able to filter for all attributes, even if they are not used in any product at the moment, you'll have to add a table

AttributesForProductType(productType,attributeName)

which contains all possible attributes, and then set up referential integrity (foreign keys) between AttributesForProductType and ProductAttribute. This way you lose the risk of misspelling an attribute name in the ProductAttribute table.

edit: If you take this design this also solves your second question: How to display a details page for every possible product type?

All possible attributes for this product are

select attributeName
from AttributesForProductType
where productType=?

all real attributes for a product with their values are

select attributeName, value
from ProductAttribute
where id=?

and those combined are (off of my head, untested)

select apt.attributeName, pa.value
from Product as p
left outer join AttributesForProductType as apt on p.productType=apt.ProductType
left outer join ProductAttribute as pa on apt.attributeName=pa.attributeName
where p.id=?


Did PostgreSQL write amplification reduction WARM efforts get released in 10 / 11?

No, nothing major along these lines has been released.


There is no need for the instance name, as you can see in the documentation

  This procedure will enable SQL tracing for a given combination of
  Service Name, MODULE and ACTION globally unless an instance_name is
  specified.

instance_name has a default value null implicating it is not a required parameter. In a RAC database this would just enable tracing for your service in all open instances.


Merge Replication between two Embedded Databases?

My understanding (I haven't worked with this in over a year) is that "merge replication" requires a sql server instance to act as a publisher. If you want to only have an SQL CE database at each end, you need to write your own code with sync framework (if you see articles on "sync services" they are using the sync framework).


How does cast work with Set Returning Functions (SRF) like generate_series?

Cast the actual value, not the set.

SELECT v::date
FROM generate_series('2018-01-01'::date, '2019-01-01'::date, '1 day') as t(v);


Based on Silberschatz's Database system concepts book, log file for a transaction has 3 records

That's the minimum you'd need for replay and recovery of individual transactions without any optimizations. There may be many more than 3 for a single CRUD item in SQL Server.

  How can I see these log records in SQL Server?

The one I use to see the active log is sys.fn_dblog (Link to Remus's log post). Note that this function isn't documented and can/will change and doesn't show things such as Hekaton logs which need to be cracked using sys.fn_dblog_xtp which again isn't documented.

  how can I see recovery phase (analyses, redo and undo) in SQL Server?

Through many hours of painstaking work. You can use sys.fn_dump_dblog() to look at transaction log backups that are taken after redo and undo has run.

Since the SQL Server Log records and files aren't documented, except for physical and logical architecture, you'll just have to search the internet and make your own best guesses.

If you're asking because you'd like to make software to deal with the log, then you'll need to get in touch with Microsoft Partner Resources. Otherwise, you're on your own, armed with the collective knowledge of the internet and volumes of your favorite beverage.

  For example T_1,A,100,150 means transaction T_1 has changed the value of A from 100 to 150.

The SQL Server log isn't really human readable, though the functions help. I stated before, it's not always that easy as change 100 to 150.

Let's say you changed 'Hi' to 'Sean'. This might cause a forwarding record, or a page split. It might be the first record in a table, or a heap, or be changed because of multiple indexes.

I'm not trying to put you off, just that academia and the real world and generally two very different things.


YOUR ORIGINAL QUERY

select event_id, count(*) as num_bookmarks from event_connections
where connectiontype = 3 join event_id on events.id=event_connections.event_id group by event_id;

PROPER SYNTAX

select event_id, count(*) as num_bookmarks
from event_connections join events on events.id=event_connections.event_id
where event_connections.connectiontype = 3
group by event_id;

You just had the WHERE in the wrong place

SUGGESTIONS

If all you care about is the event_id and the counts, do this

select event_id, count(*) as num_bookmarks
from event_connections
where connectiontype = 3
group by event_id;

No need to join.

If you want the event title, then you must join

select events.title, count(*) as num_bookmarks
from event_connections join events on events.id=event_connections.event_id
where event_connections.connectiontype = 3
group by events.title;

GIVE IT A TRY !!!


PAGELATCH_EX occures in update query with UPDLOCK, READPAST

This is because you use a Status field for messages and leave unreceiveable messages in the queue. Retaining messages (keeping them after process, with a different status) leads to queue bloat, contention, poor plan choices and general slow processing.

Follow Using tables as Queues and do destructive message processing:

DELETE TOP (1)
FROM [queue].[Messages] m WITH (ROWLOCK, READPAST)
OUTPUT deleted.*;

Also drop all indexes on the queue. The linked article has details how to achieve fancier behavior like FIFO or Pending.

Use queues only for events, not for state.


You'll need to configure SSIS to run in 32-bit runtime as Excel does not support 64-bit

Go to the Property page for the Solution, select Debugging and change Run64BitRuntime to False

Running 32-bit SSIS in a 64-bit Environment


Bulk-Insert Select performance over individual Inserts

If we're talking performance, first approach is better. Here is why:

Whenever new data is inserted into a table, the indexes will also be updated, and physically reordered. In the case on InnoDB, not only are the index files updated, but the data file itself could be reordered, it is clustered based on the primary key. So, re-ordering/updating is an expensive operation, and you want to minimize it.

When you do bulk insert, the indexes are updated at when the statement is finished. i.e. after inserting all rows.
On the other hand, when you do individual inserting, physical files are updated after each insert.

The overhead of the last select statement in the first approach is negligible.


exporting oracle tables on command line as sql files

You can generate DDL statements using imp/impdp utilities:

imp user/passwd file=export.dmp show=y log=export.sql 2> /dev/null
impdp user/passwd file=DIR:export.expdp sqlfile=DIR:export2.sql

But this method will generate just schema DDL. INSERT statements will not be generated.


Can PostgreSQL create a case sensitive database?

I ended up writing some code that transforms the SQL generated by the application into PostgreSQL-compatible SQL. It's pretty straightforward:

Split the statement into sensible tokens, skipping single-quoted string literals
Double-quote anything that is not a keyword or number

I also took advantage of this layer to transform calls to isnull to coalesce. So far it's working pretty well.


According to https://community.oracle.com/thread/959980 and https://community.oracle.com/thread/838380 the only way to do this is to use the SQLFILE parameter to impdp to create a script. Then replace CREATE PACKAGE with CREATE OR REPLACE PACKAGE and then execute the script.


pg_upgrade and tablespaces (postgres 9.2 to 9.3)

PostgreSQL creates subdirectories named for the PostgreSQL version within each tablespace.

So AFAIK pg_upgrade doesn't need to be told to take special action for the tablespaces. It will use the same tablespaces as the original database, but won't conflict because of the use of different subdirectories within the tablespace root.

The caveat is that if you have tablespaces inside your 9.2 data directory, this won't work. You shouldn't do that in the first place. Move the tablespaces to a location outside the 9.2 datadir before doing the upgrade.


You can also do SELECT...INTO, Use the table in a join or use APPLY, eg

SELECT *
INTO #tmp
FROM [dbo].[ufn_MyFunction] ( 1 )

SELECT *
FROM [dbo].[ufn_MyFunction] ( 1 ) f
    INNER JOIN someOtherTable t ON f.RowId = t.rowId

SELECT *
FROM someOtherTable t
    CROSS APPLY [dbo].[ufn_MyFunction] ( t.rowId ) f


Is there any benefit to this additional table?

If I need to check if a user has voted in a poll already, I have to look up the current poll ID and user ID
   SELECT COUNT(*) FROM votes WHERE user_id = 16 AND poll_id = 7 ;

This would be efficient with an index on (user_id, poll_id). But if you don't need the count but just whether a user has taken a poll, you only need an EXISTS subquery. Either SELECT EXISTS (...) or WHERE EXISTS (...), depending on what you want to do with this check:
... EXISTS (SELECT * FROM votes WHERE user_id = 16 AND poll_id = 7)

It will be a bit more efficient than the COUNT() query - assuming that the above index has been added.

Is it bad design that it has to go through thousands of rows to find that?

No, if you have the index, it won't go through thousands or millions of rows. It will do a single index seek.

Would it be better to have a smaller table between the polls table and the votes, for checking if a user has voted in a poll, all the polls the user has voted in, etc.?

It might be better, yes. The smaller (in number of rows) table means that the indexes will be smaller, too. So you would use an EXISTS subquery on a smaller index. For the specific query, the difference in efficiency would be very small though. For different queries, say "How nay users have taken this poll?" or *"How many users have taken each poll?", you'd get larger benefit as they require a scan of the whole index.

Or would this have no performance benefit over my current design?

So, it depends on what kind of queries you have.

Would look something like this I guess

    +------+---------+---------+
    | id   | poll_id | user_id |
    +------+---------+---------+
    | 1    | 7       | 16      |
    | 2    | 7       | 20      |
    | 3    | 8       | 16      |
    | 4    | 8       | 2       |

Then I'd add the id from this new vote_item table to the votes table as a FK (?) so I can grab the vote_item id via the user_id and poll_id, and return all the rows in the votes table with that vote_item_id.

Not exactly. The table only needs user_id and poll_id and a UNIQUE constraint on (poll_id, user_id). The id is useless for this many-to-many table. In most many-to-many tables, it's common to have two unique indexes, on (a,b) and (b,a). So, I suggest you have that (poll_id, user_id) as the primary key and a unique index on (user_id, poll_id) in the new poll_users table, if you decide to add this table.
You are right about the FOREIGN KEY though. You would add a foreign key from votes (poll_id, user_id) that REFERENCES poll_users (poll_id, user_id) and remove the individual foreign keys from votes to polls (poll_id) and users (user_id).
By the way the id in the votes looks useless, too. I'd have a UNIQUE constraint on (poll_id, user_id, poll_option_id) (meaning: no user can answer the same poll option in a poll twice) and throw away that id.


If you will have access to server's filesystem, or you can ask for this, you can use crontab utility. To verify whether you have access to cron, type in the following

crontab -e

If you will have an OS user,and if you can use the crontab utility to schedule a job, you can try something like this:

*/30 * * * * ~/bin/dump.bsh 1>~/bin/log/dump.log 2>&1

This simple entry make a logical backup every half hour.
The dump.bsh file:

#!/bin/bash
PATH=/usr/kerberos/bin:/usr/local/bin:/bin:/usr/bin:$ORACLE_HOME/bin
. $HOME/.bash_profile

now=`date +"%Y-%m-%d_%H-%M"`
expdp user/passwd directory=data_pump_dir dumpfile=lbackup$now.dmp schemas=schema_to_backup

You need to set Oracle env variables (here in the .bash_profile) since cron passes only a minimal set of env variables to your jobs.

Otherwise, go and have a look at this SQL Developer page. In the exports (expdp) section there is also a reference to automatic jobs.


Is outputting to both a result set and a temporary table possible?

you can alter the column in select or insert list as you need.

    alter procedure GetFoos as
    BEGIN
        CREATE TABLE #t (FooId INT, BarId INT, FooData SYSNAME)

        select FooId, BarId, FooData
        OUTPUT INSERTED.FooId, INSERTED.BarId,INSERTED.FooData INTO #t(FooId, BarId, FooData )
        from Foo
        where FooId in (select FooId from FooQueue where IsQueued = 1)

        update BarQueue
        set IsQueued = 1
        where BarId in (select BarId from #t)

        exec GetBars

        delete FooQueue where IsQueued = 1
    end
    GO


Not all logical attributes have to be present in the physical model, as you can easily produce them while querying data:

SELECT firstname || ' ' || lastname AS fullname
FROM person

If you insist on implementing it as a column, you could use a generated (computed) column:

CREATE TABLE PERSON
(
FIRSTNAME VARCHAR2(15) NOT NULL,
LASTNAME VARCHAR2(15)  NOT NULL,
FULLNAME VARCHAR2(31) GENERATED ALWAYS AS (FIRSTNAME || ' ' || LASTNAME) VIRTUAL,
...

For completeness I should mention that you could also create a user-defined object type with FIRSTNAME and LASTNAME as its attributes, but I personally think that you shouldn't go that route for portability and maintainability reasons.


How to keep the sync active with Bucardo

I solved this problem by made separate syncs i.e.

host1->host2
host2->host3
host3->host1

And, for resurect from "stalled" status, made cron's script that check db status and sync status and activate it.


How the get the number bigger than the average number in Postgresql?

First, I've reduced your tables by deleting FOREIGN KEYS just for the sake of the question.

create table task
(
id serial primary key,
title varchar (50) not null,
created_date date default current_date,
empl_id integer,
cat_id integer
);

create table employee
(
id serial primary key,
name varchar (20) not null,
surname varchar(30) not null,
registration_date date default current_date
);

insert into employee values
(1, 'Emp1', 'SEmp1', now()),
(2, 'Emp2', 'SEmp2', now()),
(3, 'Emp3', 'SEmp3', now());

insert into task values
(1, 'Task1', now(), 1, 0),
(2, 'Task2', now(), 1, 0),
(3, 'Task3', now(), 1, 0),
(4, 'Task4', now(), 2, 0),
(5, 'Task5', now(), 2, 0),
(6, 'Task6', now(), 3, 0);

I've used this query to calculate AVG():

select avg(tasks) as average
from   (select empl_id, count(*) as tasks from task group by empl_id) t1;

|            average |
| -----------------: |
| 2.0000000000000000 |

Then I've used it to filter employees > AVG().

select   empl_id, count(*) as tasks
from     task
group by empl_id
having   count(*) > (select avg(tasks) as average
                     from   (select   empl_id, count(*) as tasks
                             from     task
                             group by empl_id) t1);

empl_id | tasks
------: | ----:
      1 |     3

dbfiddle here


How to insert SP results in a multiple tables

SQL Server has no built-in mechanism for capturing multiple result sets returned from Stored Procedures where the result sets have differing numbers of columns and/or datatypes in the same column position. INSERT...EXEC can output multiple result sets, but they need to have the same number of columns with the same datatypes in each position. And both OPENQUERY and OPENROWSET will only return the first result set, no matter how many are returned by the stored procedure (or query).

However, this can be accomplished using SQLCLR. So, if you can't update the Stored Procedure, then you can do something like the following in .NET / C#:

using (SqlConnection _Connection = new SqlConnection(_ConnectionString))
{
  using (SqlCommand _Command = _Connection.CreateCommand())
  {
    _Command.CommandType = CommandType.StoredProcedure;
    _Command.CommandText = "SchemaName.StoredProcedureName";
    // parameters, if any

    _Connection.Open();

    using (SqlDataReader _Reader = _Command.ExecuteReader())
    {
      // Stored Procedure has been executed. Now save the results.
      // Using the Context Connection allows access to local temp tables.

      using (SqlConnection _Connection2 = new SqlConnection("Context Connection = true;"))
      {
        using (SqlCommand _Command2 = _Connection2.CreateCommand())
        {
          _Connection2.Open();

          _Command2.CommandText = "INSERT INTO #Temp1 (column_list)
                 VALUES (@Param1, @Param2, ...);";

          SqlParameter _Param1 = new SqlParameter("Param1", SqlDbType.Int);
          _Command2.Parameters.Add(_Param1);

          // define addition parameters

          while (_Reader.Read())
          {
            _Param1.Value = _Reader.GetValue(0);
            _Param2.Value = _Reader.GetValue(1);
            // set additional parameters

            _Command2.ExecuteNonQuery();
          }

          _Reader.NextResult(); // move to result set #2

          _Command2.Parameters.Clear(); // reset input parameters
          _Command2.CommandText = "INSERT INTO #Temp2 (column_list2)
                 VALUES (@Param1, @Param2, ...);";

          SqlParameter _Param1 = new SqlParameter("Param1", SqlDbType.Int);
          _Command2.Parameters.Add(_Param1);

          // define addition parameters

          while (_Reader.Read())
          {
            _Param1.Value = _Reader.GetValue(0);
            _Param2.Value = _Reader.GetValue(1);
            // set additional parameters

            _Command2.ExecuteNonQuery();
          }

          _Reader.NextResult(); // move to result set #3

          _Command2.Parameters.Clear(); // reset input parameters
          _Command2.CommandText = "INSERT INTO #Temp3 (column_list3)
                 VALUES (@Param1, @Param2, ...);";

          SqlParameter _Param1 = new SqlParameter("Param1", SqlDbType.Int);
          _Command2.Parameters.Add(_Param1);

          // define addition parameters

          while (_Reader.Read())
          {
            _Param1.Value = _Reader.GetValue(0);
            _Param2.Value = _Reader.GetValue(1);
            // set additional parameters

            _Command2.ExecuteNonQuery();
          }
      }
    }
  }
}

Now, since you want to store the results into local temporary tables, those need to be created before you execute this SQLCLR stored procedure. If you create them inside the SQLCLR stored procedure, they won't exist once the stored procedure completes.

CREATE TABLE #Temp1 (column1 DATATYPE_1, column2 DATATYPE_2, ...);
CREATE TABLE #Temp2 (column1 DATATYPE_1, column2 DATATYPE_2, ...);
CREATE TABLE #Temp3 (column1 DATATYPE_1, column2 DATATYPE_2, ...);

EXEC dbo.SQLCLR_StoredProcedure;

SELECT * FROM #Temp1;
SELECT * FROM #Temp2;
SELECT * FROM #Temp3;

BUT, as @AndriyM pointed out in a comment on this answer, if you are able to modify the Stored Procedure, then given that the local temporary tables need to be created first anyway**, it would be better / simpler to modify your Stored Procedure to insert directly into the temp tables, as opposed to adding logic to control which single result set gets returned. In this case, you can detect the existence of the local temp tables and if they do exist, then insert into them, else return the result sets as the Stored Procedure currently does, and this will ensure that any existing code that calls this Stored Procedure will continue to get the three expected result sets. And you don't even need to add any optional input parameters. Just use the following approach per each of the three queries that returns a result set:

IF (OBJECT_ID(N'tempdb..#Temp1') IS NULL)
BEGIN  -- Temp Table does NOT exist, so return result set
   SELECT col_1, col_2, col_3, ...
   FROM   SchemaName.TableName
   WHERE  ...
END;
ELSE
BEGIN  -- Temp Table DOES exist, so insert results into it
   INSERT INTO #Temp1 (column1, column2, column3, ...)
      SELECT col_1, col_2, col_3, ...
      FROM   SchemaName.TableName
      WHERE  ...
END;

** Creating the local temporary tables first wasn't necessarily specific to my SQLCLR suggestion. A single local temp table also needs to be created first in situations where one is able to use INSERT...EXEC. However, in situations where OPENQUERY is an option, then doing SELECT...INTO #tmp would allow for not creating the local temp table first.


Use that query as a 'derived table' to finish the task:

SELECT ...
    FROM ( ... your query ... ) AS x
    JOIN calendar AS prev_row  ON prev_row.calendar_id = x.previous
    ...

(If you are using MySQL 8.0, there is a "windowing" way to get the previous id.)


How to exceute view without DBLink in oracle server

You must have a server (say X) with V1 (pointing at server Y) and V2 (pointing at server Z). Can't you use create GTTs (Global Temporary Tables) with V1 and V2 (this will happen on X) and then perform your join operation on the GTT tables on X using the data from GTT_V1 and GTT_V2? There's a good link here about using GTTs. Check out the guidelines section.


I haven't worked much with geospatial types but the fact that it's CPU-intensive isn't all that surprising. If you want to avoid the CPU pegging out at 100% for the server I'd suggest taking a look at your MAXDOP settings at the instance level. The default value is 0 meaning SQL Server will use up all available processors (logical) when processing. If your query is pegging out all your CPUs and causing issue you may want to experiment tweaking your settings. Take a read of @paulrandal's post on MAXDOP: http://www.sqlskills.com/BLOGS/PAUL/post/MAXDOP-configuration-survey-results.aspx

Word of caution: Don't go changing this willy-nilly in production as it WILL affect performance and the last thing you want is to get yelled at/fired because "some guy on internet said it'd work!". Test your settings in development environment first and see if this works for you.


SQL Server - query optimization for highly skewed data distributions

Are there any other approaches to help optimizer deal with highly skewed data distributions?

Filtered statistics and breaking the query up using intermediate temporary tables (correctly!) are the main options, but you could also consider how an indexed view might be used to help. Properly implemented, an indexed view should have approximately the same impact as an extra nonclustered index on the base table(s).

Using WITH (NOEXPAND) instead of relying on automatic matching (Enterprise Edition only) will allow the optimizer to create and use statistics on the indexed view as well.

More generally, if you are able to identify the 'safe' or 'unsafe' values ahead of time, you could consider a hybrid approach (including dynamic SQL) as detailed in Building High Performance Stored Procedures by Kimberly L. Tripp.

You could also consider multiple separate procedures optimized for different cases, using the appropriate approach in each, including hints like OPTIMIZE FOR.

Finally, you have plan guides and/or forced plans via Query Store (where available).

  What are the drawbacks of manually created and handled filtered statistics in the 2nd approach?

Mainly issues around the filtered statistics not being updated as frequently as one might hope. You can workaround this by refreshing these statistics manually.

You're already recompiling, so that should address the use of filtered statistics with parameterized queries.


SQL Server manages its own security. Just because you have local Windows administrator rights doesn't automatically grant you access.

That said, there are two approaches to get back in, using local administrative rights:

Impersonate the NT AUTHORITY\SYSTEM account. There are two methods to do this, as I blogged about here. These methods allow you to leave the server running while recovering access. If there is no login for that system account, you have to use the second method (which is easier, and if you don't care about downtime, the one you should use anyway because it's safer).
Restart the database engine service in single user mode, which does grant local administrators sysadmin rights over the instance. Do what you need to do, then restart the instance in multi-user mode. This is the backdoor which was created precisely for the situation you're in; however, you do have to interrupt service to do it.


As Igor Pagliai(MSFT) Blog Here of July 3, 2014 has as very well mention about
Deep Dive: SQL Server AlwaysOn Availability Groups and Cross-Region Virtual Networks in Azure.

SQL Server AlwaysOn Availability Groups Supported between Microsoft
Azure Regions Here
VNet-to-VNet: Connecting Virtual Networks in Azure across Different
    Regions Here
Configure AlwaysOn Availability Groups in Azure VM (GUI) Here
Configure an ILB listener for AlwaysOn Availability Groups in Azure Here

As per Igor Pagliai , Azure Virtual Network Bandwidth and Latency is main factor in failover scenario.

This is always my primary concern, even on-premise, When I have to build a multi-site AlwaysOn consideration: the connectivity between different datacenters will provide enough network bandwidth and low latency? Remember that we are in Azure and the connectivity between datacenters is provided by connecting two (at least) Virtual Networks using a VPN tunnel that comes with capacity limits, then let me recap some basic considerations:

Today, for each Virtual Network, you can have only one VPN Gateway;
Each VPN Gateway is able to provide, at the very maximum, a
theoretical limit of 100Mbits/sec;
If you connect a VPN Gateway to    multiple Virtual Networks,
including on-premise local networks, each    channel will share the
same theoretical bandwidth limit of    100Mbits/sec;
VPN Gateway is backed up by two “hidden” Azure VMs of a    certain
size in active-passive configuration, able to provide 99,90%
availability SLA:

Cloud Services, Virtual Machines and Virtual Network SLA
Here

ExpressRoute or Virtual Network VPN – What’s right for me?
Here

NOTE: In this blog post, I will not cover ExpressRoute, I didn’t test for AlwaysOn AG scenarios.


SQL Server - SQL Agent - Allow a Group to own a job

Since SQL Server Agent does not support using a Windows Group as the owner of a SQL Server Agent job. You can do it, but it means an end run around the built in restrictions.

You can use some code that is posted at the link below.

Allow non-sysadmin, non-owner of a SQL Server Agent job to execute it

This approach does an 'end run' around the "owner" of the SQL Agent job for all practical purposes of execution.  This is because the dbo.StartAgentJob stored procedure uses the Logins and Groups in the dbo.msdbJobMap table to decide who can run a SQL Agent Job.  As written, it will include any member of a group assigned to the job, has the rights to start the job.

This means that anyone with rights to create a SQL Server Agent Job (a member of SQLAgentReaderRole or higher) can create and modify their own josb.

If they also have rights to insert the proper GroupName and JobName in the dbo.msdbJobMap table, anybody in those groups assigned to the job can execute the job as well.

Although a GROUP does not own the job directly, the dbo.StartAgentJobstored procedure will use its elevated permissions to determine whether to allow the Login and/or members of a Group to execute this job.

What still remains as a problem?

The owner of the SQL Agent Job is the only one who can edit the job contents.  (Of course, a sysadmin can do almost anything.)

If the SQL Agent job needs to be rewritten and the owner no longer exists in your system, the sysadmin can change the job owner from the defunct account to the new designated owner. (Or script the job out and give the script to the developer who will do the rewrite and give the new job a new name.)

So, it is not a 100% solution to make the developers completely independent from the administrators, but it should Greatly Reduce the interactions needed.

Footnote: If the job is to run on distributed servers this is not enough.


Why Won't a Database Backup if the Log File is Full?

BACKUP DATABASE is terminating abnormally

  Question: Why does the Database backup process require space in the
  log file?

Every data backup (full/differential) as the first step does a checkpoint. This is done for be able to grab as many as possible fresh data from disk.

Every checkpoint operation writes to the log about itself (at least 2 log records)

If your log is full even impossibility of writing checkpoint operation into log can fail your backup.

At the end of backup every full backup resets differential base, and this is also logged.

And there are other log writes in between of this two, you can see them using sys.fn_dblog after doing successful backup.

Here you can see the explanation of checkpoint and differential base:

Database Checkpoints (SQL Server)

How do checkpoints work and what gets logged

Base of a Differential Backup


Since you mentioned your client wanted a comma separated file as a report, you could always do something like:

psql -U myuser -h my.rds.external.address -c "\copy (SELECT * FROM mytable WHERE foo = 'bar') TO 'my_report.csv' WITH CSV HEADER NULL AS ''"

If you run that from a command line or a cron job from another machine (once you've allowed that IP address in your RDS instance configuration), you can just compress it and make it available to your client based on their needs.

A view would also work if you wanted to give them realtime access to the data instead of a comma separated report.


It is not temporary, if you want to access it from multiple requests, because there is no way for mysql to know when to delete it. So you must create it manually and delete it manually. You can use a MEMORY engine, which will have similar characteristics to temp table.


List primary replicas and non-HA databases - excluding forwarder replicas

To list all databases in an forwarder replica on the current server...

select
     dag.group_id
    ,dag.[name]
    ,dag.is_distributed
    ,fwd.replica_id
    ,fwd.replica_server_name
    ,adc.[database_name]
from sys.availability_groups dag
join sys.availability_replicas fwd on fwd.group_id = dag.group_id
join sys.availability_groups ag on ag.name = fwd.replica_server_name
join sys.availability_databases_cluster adc on adc.group_id = ag.group_id
where dag.is_distributed = 1;

To list all databases that are PRIMARY or local only and exclude forwarder databases...

select
    ars.role_desc
   ,ag_name = ag.[name]
   ,adc.[database_name]
from sys.availability_groups ag
join sys.dm_hadr_availability_replica_states ars on ars.group_id = ag.group_id
join sys.availability_databases_cluster adc on adc.group_id = ag.group_id
where ars.is_local = 1
    and ars.role_desc = 'PRIMARY'
    and not exists (
        select 1
        from sys.availability_groups dag
        join sys.availability_replicas fwd on fwd.group_id = dag.group_id
        join sys.availability_groups ag2 on ag2.name = fwd.replica_server_name
        join sys.availability_databases_cluster db on db.group_id = ag2.group_id
        where dag.is_distributed = 1
            and db.[database_name] = adc.[database_name]
)
union all
select
    'LOCAL_ONLY'
   ,@@servername
   ,[name]
from sys.databases d
where not exists (
    select 1
    from sys.availability_databases_cluster adc
    where adc.[database_name] = d.[name]
);


When you need only information about number of inserted rows and not about record's data - you could use the approach, which I present below.

Of course, you could mark rows as deleted but it will require changes in data querying. Also it will have impact on resources (storage, maybe views, probably additional indexes, CPU when querying data).

Solution

Create a table which store counters for all of the tables you are interested in. In your case you should store counters per year for those tables.

Update those counters when inserting new records and read from counters table when need to generate new unique number.

Example of a counter table:

CREATE TABLE Counters (TableName NVARCHAR(256), [Year] INT, [Value] BIGINT)

Remarks

Counter updates could be achieved by triggers in easy way but you should be aware of performance and concurrency issues. Consider updating counters table through application layer or stored procedures, which should be a better way.


MySQL5.7 (Windows) how to install from zip file without the Data Directory?