Table A triggers an insert onto Table B, Table B triggers an insert onto Table A, what happens?

Aside from the obvious "uh, don't do that"... this took about two minutes to test.

USE tempdb;
GO

CREATE TABLE dbo.TableA(ID INT);

CREATE TABLE dbo.TableB(ID INT);

GO
CREATE TRIGGER dbo.TableAToB
ON dbo.TableA
FOR INSERT
AS
  INSERT dbo.TableB SELECT ID FROM inserted;
GO
CREATE TRIGGER dbo.TableBToA
ON dbo.TableB
FOR INSERT
AS
  INSERT dbo.TableA SELECT ID FROM inserted;
GO

INSERT dbo.TableA(ID) VALUES(1);

Error:

  Msg 217, Level 16, State 1, Procedure TableBToA
  Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).

Also, no row is ever inserted into either table.


Stop using REAL and use DECIMAL instead. REAL and FLOAT are approximate data types and can't represent all numbers in the nice, rounded way you are used to. Compare these:

DECLARE @a REAL = 0.23, @b REAL = 0.24;
SELECT AVG(r) FROM (SELECT r = @a UNION SELECT r = @b) AS x;

Result:

0.234999999403954

Now using DECIMAL instead:

DECLARE @a DECIMAL(10,4) = 0.23, @b DECIMAL(10,4) = 0.24;
SELECT AVG(r) FROM (SELECT r = @a UNION SELECT r = @b) AS x;

Result:

0.235000

Some related reading:

http://web.archive.org/web/20130205023610/http://classicasp.aspfaq.com/general/why-does-3-2-1-5-4-7000000000000002.html
http://web.archive.org/web/20120208202619/http://databases.aspfaq.com/database/what-datatype-should-i-use-for-numeric-columns.html
https://sqlblog.org/2009/10/12/bad-habits-to-kick-choosing-the-wrong-data-type

Decimal is not perfect, either; you can lose precision if you need more decimal places than supported, for example. But for most applications this is not an issue.


Find the destination owner (schema) for replication on SQL Server

Instead of using [distribution].[dbo].[MSarticles] use [publicationDatabase].[dbo].[sysarticles]. Use dest_owner column.

Detail here:
https://docs.microsoft.com/en-us/sql/relational-databases/system-tables/sysarticles-transact-sql


If this returns nothing, then your database is fine.

DBCC CHECKDB (DatabaseName) WITH NO_INFOMSGS,
ALL_ERRORMSGS, EXTENDED_LOGICAL_CHECKS, DATA_PURITY


How to migrate Unicode UTF-8 CLOB data from Oracle to SQL Server 2017 UTF-?

You could check out - https://www.attunity.com/products/replicate/attunity-replicate-for-microsoft-migration/

"Attunity Replicate for Microsoft Migrations is a special, no cost offering for Microsoft customers to help migrate data from popular databases to the Microsoft Data Platform."


Creating mongodb database with ansible playbook

- name: Create MongoDB root user siteRootAdmin
  mongodb_user:
    login_port: "27017"
    database: "admin"
    name: "{{ mongodb_admin_user }}"
    password: "{{ mongodb_admin_password }}"
    roles: "root"

- name: Create MongoDB administrative user siteUserAdmin
  mongodb_user:
    login_user: "{{ mongodb_admin_user }}"
    login_password: "{{ mongodb_admin_password }}"
    login_port: "27017"
    database: "e-gov"
    name: "{{ mongodb_user }}"
    password: "{{ mongodb_password }}"
    roles:
      - { db: "admin", role: "readWrite" }
      - { db: "{{ mongodb_database }}", role: "readWrite" }

- name: Create MongoDB backup user siteUserBackup
  mongodb_user:
    login_user: "{{ mongodb_admin_user }}"
    login_password: "{{ mongodb_admin_password }}"
    login_port: "27017"
    database: "e-gov"
    name: "{{ mongodb_backup_user }}"
    password: "{{ mongodb_backup_password }}"
    roles:
      - { db: "admin", role: "backup" }

I did this, and it works with ansible 2.2.0^


For Graph database on Cassandra have a look at TitanDB:

What you need is already implemented in TitanDB. Implementing your own Graph Database is not trivial, and would be very time consuming. In most cases, a proven solution is best. (BTW, I am not involved in TitanDB development or business.) I have no idea about your use case, but I do not see a reason to implement something new, except as a hobby.

Update I found a whitepaper about Titan GraphDB's data model in database: https://github.com/thinkaurelius/titan/wiki/Titan-Data-Model. It gives some hints how to design a datastore for graphs.

Aurelius is now also part of Datastax and they work on a combined solution for storing big graphs in Cassandra.


Permission to view execution report in SSIS Catalog

Here is our solution (believe me it will work perfectly!)

After investigating the procedure about how the execution reports are stored, we found that every time a job runs, the internal.executions table in SSISDB will be updated. And in order to view the execution report of this run, we need to run something like below:

EXEC SSISDB.catalog.grant_permission
    @object_type = 4,
    @object_id = @execution_id,
    @principal_ID =  13,
    @permission_type = 1;

This stored procedure will grant a role/user a certain access to an object in database. @object_type means on which type of object you need permissions on (4 means operation); @object_id means the specific object we want to access to; @principal_ID means who want to get the access; permission_type means what kind of access we want to have (1 means read only). For more information, please refer to catalog.grant_permission (SSISDB Database)

Our target is to create a trigger that every time a job runs -- meaning the internal.executions table gets inserted -- using the above SP to grant a role the permission to that operation information.

Then, let's follow the below steps to setup execution report viewing permissions:

Create a user the trigger will be executed as. This user should be able to execute a trigger in SSISDB and have access to SSIS Catalog. In our case, we give it db_owner role and ssis_admin under SSISDB.

USE [master]
GO
CREATE LOGIN [ssis_job_viewer] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
GO
USE [SSISDB]
GO
CREATE USER [ssis_job_viewer] FOR LOGIN [ssis_job_viewer]
GO
USE [SSISDB]
GO
ALTER ROLE [db_owner] ADD MEMBER [ssis_job_viewer]
GO
USE [SSISDB]
GO
ALTER ROLE [ssis_admin] ADD MEMBER [ssis_job_viewer]
GO

Create a role [package_execution_viewer]. This role will be used in the stored procedure we mentioned above.

USE [SSISDB]
GO
CREATE ROLE [package_execution_viewer]
GO

Add users to [package_execution_viewer]

USE [SSISDB]
GO
ALTER ROLE [package_execution_viewer] ADD MEMBER [user1]
GO
USE [SSISDB]
GO
ALTER ROLE [package_execution_viewer] ADD MEMBER [user2]
GO

Get the principle_id of package_execution_viewer role. This id will be used in above SP also.

SELECT * from sys.database_principals
GO

Create trigger to grant permission for package_execution_viewer

USE [SSISDB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [internal].[update_viewer_perms]
ON [internal].[executions]
WITH EXECUTE AS 'ssis_job_viewer'
AFTER INSERT
AS
declare @execution_id bigint
BEGIN
select @execution_id = execution_id from inserted
EXEC SSISDB.catalog.grant_permission
    @object_type = 4,
    @object_id = @execution_id,
    @principal_ID =  13,
    @permission_type = 1   **--Note the principal_id needs to be changed**
END
GO

All set. This way we can let people access to the execution reports without making them as ssis_admin. Try it out and share any thoughts to this post!


SQL Server: Do Statistics Updates cause query plans to be flushed? Should they?

The answer is close to your "stats updates automatically cause dependent query plans to be flushed". They don't "stick around"

Flushing a plan from cache is determined by memory pressure.
Statistics updates cause plan recompilations if AUTO_UPDATE_STATISTICS is on.
Updated statistics only mean that individual statements in a batch plan need an optimality based recompile, it does not flush the whole thing.

The analogy might be separation of concerns: the stats thingy does stats, the plan cache widget does plan cache stuff. Statistics says "I've changed", and the consumer of this status decides what action to take: not the generator of this status.

See Execution Plan Caching and Reuse on MSDN.


Aggregate data from multiple rows into single row

Itzik Ben-Gan's blog post (link provided by Daniel Hutmacher) has some nice solutions to the problem for SQL Server 2012 and later:

The Last non NULL Puzzle.

Here is one more that will work in older versions (even 2005) and can be easily adapted for more columns. I haven't tested for efficiency or compared to the other solutions but I'd expect it to work better if the table had a clustered index on (id, priority). But performance may depend on many other things, like the distribution, the number of distinct id values, the number of versions (rows per id values), the number and types of columns, etc.:

SELECT
    d.id,
    d1.col1,
    d2.col2,
    d3.col3
FROM
    ( SELECT id, @point_in_time AS priority
      FROM tablename
      GROUP BY id
    ) AS d
  OUTER APPLY
    ( SELECT TOP (1) col1
      FROM tablename
      WHERE id = d.id
        AND priority <= d.priority          -- optional
        AND col1 IS NOT NULL
      ORDER BY priority DESC
    ) AS d1
  OUTER APPLY
    ( SELECT TOP (1) col2
      FROM tablename
      WHERE id = d.id
        AND priority <= d.priority          -- optional
        AND col2 IS NOT NULL
      ORDER BY priority DESC
    ) AS d2
  OUTER APPLY
    ( SELECT TOP (1) col3
      FROM tablename
      WHERE id = d.id
        AND priority <= d.priority          -- optional
        AND col3 IS NOT NULL
      ORDER BY priority DESC
    ) AS d3 ;

Test in SQLfiddle.

The @point_in_time above is a parameter, that can be removed if we want the latest non-null values across all the table.

If however, we want the latest non-null values for a specific point in time (say for when priority=55), we can replace that @point_in_time with 55 and have the latest non-null values up to that point in time (55).


comparing left join and outer apply doing the same thing

try this - you might get the best of both

select manyfield
from  ( select manyfield
             , row_number() over (partition by t1.id order by t1.timestamp desc) as rownum
          from inserted
          left join sometable t1
                 on inserted.id = t1.id
      ) tt
where rownum = 1 or t1.id is null


Always On AG cluster with 2 servers that fail over to each other

Yes, this is absolutely possible, and a great use of resources.

You would get your OLTP databases added to an Availability Group, and then add the second machine as your secondary replica. For both replicas be sure to allow reads from secondary.

Then do the same with your DW databases.

You will end up with two AGs, and will probably want to run with each as the primary on different servers, so be sure that you include monitoring to let you know when the AGs failover, and what AG is running as the primary on each server.


How can I solve java lang unsatisfiedlink error when runing jdeveloper?

Take a look here - the answer appears to correspond to your needs. It appears to be more of a progamming question than a DBA one, but we're here to help! :-).

Furthermore, a quick Google of sudo yum install libXext got me to this link. Please take the statckexchange site tour and in particular, check out the "help us to help you" blog - both available at the bottom of the page on the left.


how to add shard into current cluster without sharing any chunks

There is other solution, if you want to migrate you data to new machine "on-the-fly" and have only short interruption when you transfer your clients to use that new machine.  With this you can replicate data to new location

If you want to add new shard to cluster and still keep balancer running... you can always use TAGs.. Tag your current shards and define that $MinKey - $MaxKey chunk area belongs to that TAG. Check here what I'm talking about


Remove the (11) from the DOUBLE(11):

`f_sells` DOUBLE  NULL DEFAULT NULL ,

Approximate numeric dataypes (Float and Double) need no parameter. There is also a non-standard syntax with 2 parameters. From the MySQL docs, Floating-Point Types (Approximate Value):

  MySQL allows a nonstandard syntax: FLOAT(M,D) or REAL(M,D) or DOUBLE PRECISION(M,D). Here, “(M,D)” means that values can be stored with up to M digits in total, of which D digits may be after the decimal point. For example, a column defined as FLOAT(7,4) will look like -999.9999 when displayed. MySQL performs rounding when storing values, so if you insert 999.00009 into a FLOAT(7,4) column, the approximate result is 999.0001.


Request suggestion for a database between MySQL and Postgresql

PostgreSQL has a lot more modern SQL features compared to MySQL.

Some of those featurese you might not find necessary right now but you'll deeply miss them once your application grows and your needs get more complex. Here are just some:

No check constraints
No deferrable constraints
No windowing functions
No common table expressions
No recursive queries (think hierarchical data)
Non transactional FK evaluation
No table functions
No function based index
No partial index
No full text search on transactional tables
No GIS features on transactional tables
Incomplete trigger implementation

Performance wise both will be pretty much the same (especially with such a tiny dataset). I wouldn't expect to see any difference between the two with your data.

If the database size grows, PostgreSQL seems to cope better with a lot of concurrent read and write access, while MySQL has some read optimizations that are not (yet) available in PostgreSQL (like index-only scans) which tends to make MySQL perform better in heavy read-only situations. But then every workload is different and it's basically impossible to generally say "one is faster than the other".

If you go for MySQL make sure you run it in ANSI and strict mode right from the beginning to get rid of some of gotchas that the default installation keeps for surprises (e.g. silently truncating data or accepting February 31st as a valid date).

(You might also want to look at the comment of ypercube here: https://stackoverflow.com/questions/9963012/what-does-the-sql-standard-say-about-dependent-conditionals-in-update/9963473#9963473 to get a feeling on what I mean with "gotchas")

This blog post also lists some of the really nice things about PostgreSQL (compared to SQL Server):

http://facility9.com/2011/12/ten-reasons-postgresql-is-better-than-sql-server/


The queries give different estimates because the first one does not benefit from the Parameter Embedding Optimization. From the point of view of the optimizer the query might as well be:

SELECT * FROM t1 WHERE b>???;

To get an idea of how cardinality estimates might work for simple queries, sometimes I think about what I would do if I had to program the query optimizer to produce an estimate. Often those thoughts don't exactly match what SQL Server does but I find it helpful to keep in mind that the optimizer is almost always working with imperfect or limited information. Let's go back to your first query:

DECLARE @c INT=300;
SELECT * FROM t1 WHERE b>@c;

Without a RECOMPILE hint the optimizer doesn't know the value of the @c local variable. There isn't enough information here to do a proper estimate, but the optimizer needs to create an estimate for all queries. So what can a programmer of the optimizer do? I suppose that a sophisticated algorithm that makes a guess based on the distribution of values in the column would be possible to implement, but Microsoft uses a hardcoded guess of 30% for an unknown inequality. You can observe this by changing the value of the local variable:

DECLARE @c INT=999999999;
SELECT * FROM t1 WHERE b>@c;

The estimate is still 300 rows. Note that the estimates do not need to be consistent between queries. The following query also has an estimate of 300 rows as opposed to 1000 - 300 = 700:

DECLARE @c INT=300;
SELECT * FROM t1 WHERE NOT (b>@c);

Conceptually, it can be rewritten as

DECLARE @c INT=300;
SELECT * FROM t1 WHERE b<=@c;

Which will have the same hardcoded 30% estimate.

Your second query does not use a local variable, so the optimizer can directly use the statistics object that you created on the column to aid with estimation. Histograms can only have up to 201 steps and your table has 1000 distinct values so there are some filter values in which the optimizer does not have complete information. In those cases it needs to make more of a guess. Here's how you can view the histogram for the b column:

DBCC SHOW_STATISTICS ('t1', 's_b') WITH HISTOGRAM;

On my machine I got lucky in that 300 is the high value for one of the steps of the histogram:

You can find an explanation of the different columns in the documentation. Going forward I'm going to assume that you know what they all mean. The query requests rows with b > 300, so the histogram with a RANGE_HI_KEY of 300 is irrelevant, as are all of the steps with a lower RANGE_HI_KEY. For this query, I would program the optimizer to simply sum all of the RANGE_ROWS and EQ_ROWS values for the remaining 133 steps of the histogram. Those columns add up to 700 and that is SQL Server's estimate.

Other filter values may not give exact results. For example, the following two queries both have cardinality estimates of 704 rows:

SELECT * FROM t1 WHERE b>293; -- returns 707 rows
SELECT * FROM t1 WHERE b>299; -- returns 701 rows

Both estimates are very close but not exactly right. The histogram does not contain enough information for those values to provide an exact estimate.


As 3manuek mentioned, there are functions and operators to deal with IP addresses - be it IPv4 or IPv6.

I am wondering a bit where your number comes from.  The address range you gave translates to

2001:0dd8:0000:0000:0000:0000:0000:0000

which is an address range of length 1.

If you meant all possible addresses starting with '2001:dd8:', then the correct designation of this range would be 2001:dd8::/32, as it is the first 32 bits that are given.  According to a calculator tool, the resulting range is the following:

Start Range: 2001:dd8:0:0:0:0:0:0
End Range: 2001:dd8:ffff:ffff:ffff:ffff:ffff:ffff
No. of host: 79228162514264337593543950336

Meanwhile, if you omit the block size (/32), you get a single address (the one I showed above).

So, there is a way to get the number provided by the calculator, using builtin PostgreSQL functionality.  Our friend here is masklen():

SELECT round(2 ^ (128 - masklen('2001:dd8::/32'::cidr))::numeric);

             round
───────────────────────────────
 79228162514264337593543950336

The result is the same as form the calculator.  The rounding is there for removing the decimal point and the zeros after it, which are the result of casting to numeric.


Restoring the database dump of a newer version to an older version of mongodb

Assuming you aren't using any newer features or index/collection options from MongoDB 3.2+ that aren't supported in MongoDB 3.0, you should be able to restore the backup.

Before doing so, I would review the downgrade instructions in the MongoDB documentation to check for potential issues:

Downgrade MongoDB 3.4 to 3.2
Downgrade MongoDB from 3.2

The downgrade instructions assume a change of binaries rather than a mongodump backup, but the caveats on backward-incompatible features such as newer index and collection options still apply.

I suggest using the 3.4 versions of mongodump and mongorestore  to test your outcome in a development or staging environment before applying to a production deployment.


Background
In the worst case, a query containing a lookup has to go to physical storage for rows that require column data not covered by the nonclustered index.  In the very worst of worst cases, each lookup will require a separate I/O, and execution will have to wait for that single row's worth of data to come back before proceeding.  This scenario usually has severe performance implications if the lookup has to process a significant number of rows.
That is why lookups get such a bad press.  On the other hand, consider that the ability to do lookups was introduced in SQL Server 2000.  In SQL Server 7.0 the query processor could only use a nonclustered index if it contained all the information necessary to satisfy the query; in all other cases, it had to access data via a clustered index (if present, or a heap scan otherwise).  If lookups were always so very bad, SQL Server would surely never have introduced them.
In SQL Server 2000+ then, where we have a nonclustered index that provides useful ordering and/or (most of) the columns required by a query, and where the number of lookups is likely to be relatively small, using the nonclustered index and performing a limited number of lookups on the base table is likely to be the cheapest available access method (though a fully-covering nonclustered index might be cheaper still, of course).
In many cases, it is just not practical to create as many nonclustered indexes as would be needed to avoid scanning the base table for all common queries.  One reason might be that INSERT/UPDATE/DELETE/MERGE performance is more important that querying speed (remember that data modification operations also have to maintain all affected nonclustered indexes).  Another reason might be space; each nonclustered index represents a copy of a subset of the columns of the base table (or expressions thereon) just sorted differently.  More copies of the data means more storage space, and more things competing for space in SQL Server's in-memory data cache.
Other times, we can create just a few extra indexes (perhaps filtered in SQL Server 2008+) with just enough INCLUDE columns to satisfy the vast majority of performance-critical queries, without compromising data modification performance too much, and without using too much extra disk space.  Balancing the competing considerations is what makes index tuning more art than science.
Cost
You ask what the 99% cost for the lookup operator really means in the query plan.  The costing component of the query optimizer produces an estimated cost for that operation that is 99% of the total estimated for the query.  The number itself (0.29) does not mean very much at all; for all practical purposes, you should consider it as a unit-less number used internally by the optimizer when comparing alternative strategies for that specific query.
The estimated cost takes no account of your hardware, configuration, application needs, or very much of anything else.  The cost model used by the optimizer includes a significant number of heuristics and simplifying assumptions that happen to produce reasonable plans most of the time, for most queries, on most hardware.  That's not to say that there is no correlation between high-cost operators in plans and performance; rather the link is often much weaker than commonly expected.  By all means check the reasons for high-estimated-cost plan operators first, but don't treat the information as anything other than a very possibly flawed estimate.
Impact
I also want to mention a couple of factors that can ameliorate the impact of lookups.  First, I mentioned right at the start that the worst case involves row-by-row physical I/O.  This will obviously be avoided if the data pages (clustered index or heap) needed to satisfy the lookups are already in memory (data cache). Where this is the case, the execution time difference between a plan with a lookup versus a covering index may well be immeasurable.  Even where physical I/O is required, if the number of reads is small, you still may not care.  (How likely data pages for a table are to be in the data cache depends on many factors, and will be specific to your hardware and circumstances).
Where more that a little physical I/O is needed, the impact of the lookups may still be reduced by optimizations present in the query plan.  If SQL Server expects the number of lookups to be significant, it may choose to explicitly sort the rows entering the nested loops join driving the lookup in order of the non-clustered keys.  This reordering promotes sequential reading of the nonclustered index, which may be very much faster than random I/O on your hardware.
With or without an explicit sort, the nested loops join driving the lookup may have the WithOrderedPrefetch or WithUnorderedPrefetch attributes present.  In either case, the query execution engine 'looks ahead' in the index key stream driving the lookups and issues read-ahead reads.  The idea is to issue asynchronous read requests to the I/O system for data pages that will be needed soon, so that by the time the lookup needs a data page, it is already present in memory.
Under ideal conditions (low fragmentation, good query plan, high-performance I/O system) the read-ahead mechanism may well be fast enough to prevent even large parallel query plans from ever waiting on I/O to complete.  This is especially true in Enterprise Edition, which can issue very large single I/O requests (up to 2MB per request if memory serves).  On the other hand, under less than ideal (more normal!) conditions, your query may suffer horribly as it waits on long I/O queues, or fails to drive the I/O system hard enough.  The worst case performance of key lookups can be very poor indeed.
Summary
In summary, you will generally want to avoid lookups where it makes sense to do so.  For small queries (that are going to remain small) you may decide that the overhead of extra indexes (space & maintenance) is not justified, given due weight to the wider needs of the system and its users.
Ultimately this is all part of the art & science that is database development and administration.


You have to build two replication setups:

from masters 1, 2 and 3 to the slave 4 in the multimaster config
from master 4 to slaves 1, 2 and 3 in the plain replication config

Caveats:

each instance of mysql should have an unique server_id to tell events originators
multimaster masters 1, 2 and 3 should use binlog_do_db directive to restrict the set of databases being replicated (until you want to populate ALL the DB's like mysql.* across the whole setup):
binlog_do_db = x_?  # on each server 1, 2 and 3
mysql 4 as a master needs a restriction for replicated databases too:
binlog_do_db = x_1,x_2,x_3
plain replication from the single master to three slaves means the shared master  binlog will contain the events from all the databases mentioned in the binlog_do_db directive. So be ready that relay files will be about three times bigger than required for a single DB.
also you have to restrict the replication on each slave to the proper DB:
replicate_do_db = x_? # on 1, 2 and 3
you have to turn on the binloging on all the servers
you have to CHANGE MASTER 's1'..., CHANGE MASTER 's2'... and CHANGE MASTER 's3'... on the server 4
you have to CHANGE MASTER 's4'... on each server 1, 2 and 3

Replication events are represented in the common object naming manner: originator.database.table

When event is replicated from, say, S1 to S4 it will be applied to the replica of x_1 on S4 as well as written to the binlog. Then that event will be replicated back to the S1. Each replication event is marked by its originator. S4 will see that certain event's originator is S1 and applies it to the corresponding database. When that event has been replicated back to the S1 it will see that he was an originator of the event and discard it. If certain event will be originated from S4 it will be applied by S1.

The whole setup isn't easy to maintain as far as each crash recovery will require the total reinitialization from the scratch. If you really want something like that you'd better look at the mariadb/galera.


In the MySQL General Query Log, the fields are these:

yymmdd hh:mm:ss thread_id command_type query_body

The timestamp only appears each time it changes.

In your example "133" would be the thread id of the connected client, as shown in SHOW PROCESSLIST;.

Each line represents a query issued, but not necessarily a round-trip from the client to the database, because queries issued within stored procedures and stored functions are also logged, and a proc or function, while running, could issue many queries in response to a single query from a client.

Also, the MySQL C-API, which is the library underlying some languages' connectors to MySQL, supports multiple statement execution, which would be another case where multiple queries don't necessarily represent multiple round-trips since multiple queries can be send to the database as a "batch"... but that's only a factor if this capability is used by the calling application.

The general log can also be directed to write to the mysql.general_log table, which is easier to parse and analyze than the file-based log, but the same general observations, above, apply when using that format.

The slow query log includes more information and so provides a more informative log for understanding how the server is performing, and can be used as a log of all queries by configuring MySQL to interpret all queries as meeting the "slow" criteria.  This is done by setting long_query_time to '0' after enabling the slow query log.


Log shipping secondaries in STANDBY stuck in 'Restoring...' - SQL Server 2012 SP3

Please do restore verifyonly of your log backups just to make sure they are restorable. Your issue is similar to the one defined in  Log Shipping - RESTORE WITH STANDBY - on SQL Server 2012 keeps breaking. Try those suggestions as well.

Few more things I would like to suggest is, if your log backups are valid try doing restores with NORECOVERY option to make sure the issue is with only STANDBY option. Also, I am assuming this is not an corruption issue. If it is then it is better to rebuild logshipping rather than fixing the corruption. This link should also help you out for some more suggestions: https://social.technet.microsoft.com/Forums/windowsserver/en-US/9c39fc61-0a59-402b-91fb-17ac1f03da7b/sql-2012-log-shipping-to-report-server?forum=sqldisasterrecovery


Carry forward non-null value with a windowing function?

You can use a recursive CTE and COALESCE to return the most recent non-NULL value for Happiness. See this fiddle for a working example.

In the example, I am first using a CTE to create a pseudo ID column for use in the recursive CTE. Then in the recursive CTE, we select the ID = 1 row (the earliest row) as the anchor and recursively select the next row by ID for each name group. The COALESCE function picks the previous value if the current value is NULL for happiness.

Query:

;WITH DataC AS(
  SELECT ROW_NUMBER() OVER (PARTITION BY name ORDER BY date) AS ID,
    [name],
    [date],
    [happiness]
  FROM Data
), CTE AS (
  SELECT ID,
    [name],
    [date],
    [happiness]
  FROM DataC
  WHERE ID = 1
  UNION ALL
  SELECT d.ID,
    d.[name],
    d.[date],
    COALESCE(d.[happiness], c.happiness) AS happiness
  FROM DataC d
  INNER JOIN CTE c ON c.Name = d.Name AND c.ID = d.ID - 1
)

SELECT *
FROM CTE
ORDER BY name, date

Result:

ID  name    date                        happiness
-------------------------------------------------
1   John    2020-01-03 00:00:00.000     20
2   John    2020-01-04 00:00:00.000     20
3   John    2020-01-05 00:00:00.000     20
4   John    2020-01-06 00:00:00.000     20
5   John    2020-01-07 00:00:00.000     18
1   Mary    2020-01-01 00:00:00.000     35
2   Mary    2020-01-02 00:00:00.000     35
3   Mary    2020-01-03 00:00:00.000     -15
4   Mary    2020-01-04 00:00:00.000     -15
5   Mary    2020-01-05 00:00:00.000     0


When to use sort_in_tempdb when rebuilding indexes?

SORT_IN_TEMPDB means that SQL server will use tempdb to allocate the temporary space as opposed to allocating space in the user database whose index is being rebuild. This means you will need less free space in your user database during an index rebuild operation and more free space in tempdb.

It gives you better advantage when tempdb is on a different set of disks (LUNs) from the user database.

From SORT_IN_TEMPDB Option - BOL :

  If the SORT_IN_TEMPDB option is set to ON and tempdb is on a separate set of disks from the destination filegroup, during the first phase, the reads of the data pages occur on a different disk from the writes to the sort work area in tempdb. This means the disk reads of the data keys generally continue more serially across the disk, and the writes to the tempdb disk also are generally serial, as do the writes to build the final index. Even if other users are using the database and accessing separate disk addresses, the overall pattern of reads and writes are more efficient when SORT_IN_TEMPDB is specified than when it is not.

Make sure you read the disk space requirements when SORT_IN_TEMPDB is ON.

  slow/possibly misconfigured SAN

You know the pain point. Why dont you work with your SAN admin to fix it ? Misconfigured and or slow SAN will cause all sort of problems like slowness.

Some important points to note :

Use MAXDOP=1 when doing an online index rebuild - else it will cause increase fragmentation.
Intelligently reorg/rebuild your indexs based on the fragmentation level as well as page fragments.
Read SQLFool's experience with Creating a 60 GB Index
Make sure you have configured tempdb properly - TF 1117 (GROW ALL FILES IN A FILEGROUP EQUALLY) and 1118 (FULL EXTENTS ONLY) and have more tempdb datafiles.

  What would be the best way to test this?

Yes, you have to test it by analyzing the waitstats when you rebuild the index with and without SORT_IN_TEMPDB. Measure run time as well and when doing in PROD, make sure you do it during a maintenance window or less server activity. Also check your read/write data and log latency.

I am not sure you have Instant file initialization, but it will benefit when restoring, during autogrowth of data files and when creating a new database (just mentioning for completeness).


Howto remind and restore recovery mode during SSDT deployment?

I doubt seriously that the entire deployment happens in one batch and therefore variables wouldn't persist throughout the deploy (but you could test it).  What I think makes more sense is to just store any "pre" values in a temp table (maybe a global temp table) and then retrieve them during the post deploy.


Let me show you what Oracle says about your errors.

  ORA-01157: cannot identify/lock data file string - see DBWR trace file

  Cause: The background process was either unable to find one of the data files or failed to lock it because the file was already in use.
  The database will prohibit access to this file but other files will be unaffected. However the first instance to open the database will need to access all online data files. Accompanying error from the operating system describes why the file could not be identified.

  Action: Have operating system make file available to database. Then either open the database or do ALTER SYSTEM CHECK DATAFILES.

  ORA-01516: nonexistent log file, data file, or temporary file "string"

  Cause: An attempt was made to use ALTER DATABASE to rename a log file, data file, or temporary file; or to change attributes of a data file or temporary file (for example, resize, autoextend, online or offline); or to re-create or move a data file. The attempt failed because the specified file is not known to the database's control file or is not of a type supported by the request.

  Action: Specify the name or number of an existing file of the correct type, as appropriate. Check the relevant V$ table for a list of possible files.

In this case, you can recreate the tablespace that the datafile belongs to:

If the database is down, mount it.

STARTUP MOUNT;

Offline drop the datafile.

ALTER DATABASE DATAFILE 'full_path_file_name' OFFLINE DROP;

If the database is at mount, open it.

ALTER DATABASE OPEN;

Drop the user tablespace.

DROP TABLESPACE tablespace_name INCLUDING CONTENTS;

Recreate the tablespace.

I have tested the above steps to simulate your problem, created new tablespace with the datafile in /tmp directory, and it worked perfectly for me.


Stored Procedure: table-Value parameters

Check out this post from Erik Darling:

Table Valued Parameters: Unexpected Parameter Sniffing

The gist of it is that table-valued parameters are susceptible to parameter sniffing just like other parameters.  If the execution plan happened to get cached when 2,000 rows were passed in via the TVP, then that's the plan you're stuck with (until a recompile).

All of the usual solutions to parameter sniffing apply.  It would be helpful if you could provide an actual execution plan and query text to get a better idea of what's going on.

Probably the "simplest" solution is to add a RECOMPILE hint to the query that's performing the join to this big table.  This will incur higher CPU (due to more plan recompiles) and you lose the "predictability" of the query plan, but it could solve the immediate plan quality issue.

This should also help with the problem of fixed join estimates, mentioned in Erik's post:

  Non-join cardinality estimates behave like local variables (fixed estimates)

  ...we get 10% for equality, 30% for inequality, and 9% for two inequalities.


DDL trigger which restricts ALTER Objects. You can add additional Alter objects which you wish to restrict.

SET ANSI_NULLS, QUOTED_IDENTIFIER ON;
GO
CREATE TRIGGER Tr_DDL_AlterObjects ON DATABASE
  WITH ENCRYPTION
  FOR ALTER_TRIGGER, ALTER_TABLE, ALTER_PROCEDURE, ALTER_SCHEMA,
    ALTER_CERTIFICATE, ALTER_FUNCTION, ALTER_APPLICATION_ROLE,
    ALTER_DATABASE, ALTER_INDEX, ALTER_ROLE, ALTER_VIEW,
    ALTER_XML_SCHEMA_COLLECTION
AS
BEGIN
    PRINT 'You do not have sufficient ALTER Privilege !';
    ROLLBACK;
END;

GO
ENABLE TRIGGER Tr_DDL_AlterObjects ON DATABASE;
GO


SQL Server 2012 IO Latency and Wait Types

The waits that are seen in sys.dm_io_virtual_stats are aggregates instead of point values. If you are doing a lot of transactions in a certain period, it will show you an higher aggregate --> higher average latency. More information on how this dmv works can be found on https://sqlperformance.com/2013/10/t-sql-queries/io-latency

  Virtual file stats are a great starting point when you want to
  understand I/O performance for a SQL Server instance. If you see
  I/O-related waits when looking at wait statistics, looking at
  sys.dm_io_virtual_file_stats is a logical next step. However,
  understand that the data you're viewing is an aggregate since the
  stats last cleared by one of the associated events (instance restart,
  offline of database, etc).  If you see low latencies, then the I/O
  subsystem is keeping up with the performance load. However, if you see
  high latencies, it's not a foregone conclusion that storage is a
  problem. To really know what's going on you can start to snapshot file
  stats, as shown here, or you can simply use Performance Monitor to
  look at latency in real time. It's very easy to create a Data
  Collector Set in PerfMon that captures the Physical Disk counters Avg.
  Disk Sec/Read and Avg. Disk Sec/Read for all disks that host database
  files.

The LCK_M will not contribute to the IO_STALL_READ_MS as these counters are only IO related


Indices for a join query with datetime range

For this specific query, I would create indexes on patients(practice_id, species) and transactions(patient_data_id, practice_id, transaction_date) and then rewrite the query as follows:

select count(distinct p.id)
from patients p
where p.practice_id = 6 and p.species = 'canine'
    and exists (select 1
                from transactions
                where patient_data_id = p.patient_data_id
                and practice_id = 6
                and transaction_date BETWEEN '2015-01-01' AND '2015-12-31')

The database won't go through all the transactions but will stop once it finds the first transaction that meets the conditions, as that's all you need to find out the final result (basically, patients that have at least one transaction). I also removed the first distinct as you don't need it - count will return only one row.


How to correctly interpret index usages from EXPLAIN of mysql?

Using index means that the columns used for that table are all in the chosen index.  This is called a "covering index".

Note the terminology confusion:  the Key column tells you whether it is "using an index"; Using index says whether that index is "covering" in this usage.

Using index condition is something else; don't get confused.

Using temporary; Using filesort does not necessarily apply to the table in question.  See EXPLAIN FORMAT=JSON SELECT ... for specifically which GROUP BY and/or ORDER BY are creating a temp table and/or sorting.

ref=const probably means something like:  INDEX(foo) with WHERE foo=123.  key_len=8 usually refers to a BIGINT NOT NULL.

ref=Production1_ may be a truncation of Production1_db.my_table.my_column

The second row does not say Using index because the index being used in the second table (FKB8...) is not "covering".

EXPLAIN mostly ignores GROUP BY, ORDER BY, and LIMIT.  Instead, it focuses on what index(es), if any, is used for WHERE or ON.


Finding the nearest geo points across two tables?

Don't use ST_Distance. It will never use an index. Instead use KNN distance with <->, and use ST_DWithin when possible.

  To find the nearest points from the table with 80M respect to 50M within 0.1 miles I run something like:

To find the nearest point, you can do..

SELECT A.latitude, A.longitude, B.latitude, B.longitude,
FROM my_table_50 AS A, my_table_80 AS B
ORDER BY A.geom <-> B.geom
LIMIT 1

To find the nearest point within one mile,

SELECT A.latitude, A.longitude, B.latitude, B.longitude,
FROM my_table_50 AS A, my_table_80 AS B
WHERE ST_DWithin(A.geom, B.geom, 1609.34)
ORDER BY A.geom <-> B.geom
LIMIT 1

Also, unless your postgis is old don't ever do

SELECT AddGeometryColumn('my_table_50/80', 'geom', 4326, 'POINT', 2);

From the docs,

  Changed: 2.0.0 This function no longer updates geometry_columns since geometry_columns is a view that reads from system catalogs. It by default also does not create constraints, but instead uses the built in type modifier behavior of PostgreSQL. So for example building a wgs84 POINT column with this function is now equivalent to: ALTER TABLE some_table ADD COLUMN geom geometry(Point,4326);

Make sure in the above you have a spatial/gist index on A.geom and B.geom. And, consider clustering both of them on those indexes.


get table and field names from union in execution plan

The answer lies in the system table/views.

  Always refer to the official documentation from MSDN first, and
  search Dave Pinal second, as you will likely find these types of
  questions answered. ~ Helpful Hint #3 ;)

The system table you want is through sys.dm_exec_query_plan

This function stores the execution plans of the caches plans

And you can acquire the needed sql_plan varbinary value from one of three places:

sys.dm_exec_cached_plans
sys.dm_exec_query_stats
sys.dm_exec_requests

An example is like the following:

SELECT qt.text, qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
WHERE qt.text LIKE 'SELECT %'

I left the predicate in this example, but you can find different methods to achieve the same goal.

Anyways, the XML column query_plan will have all of the tables, columns, and even the values of any variables available in that plan. If you click on it, you get shown the visual query plan, but copy and pasting the field will have the actual XML plan used in the query.

Cheers.


Separating system databases from MDW SQL Server 2008 R2

Join via snapshots_internal to source_info_internal which has the server instance name, eg

SELECT du.*, sii.instance_name
FROM [snapshots].[disk_usage] du
    INNER JOIN core.[snapshots_internal] si ON du.snapshot_id = si.snapshot_id
        INNER JOIN [core].[source_info_internal] sii ON si.source_id = sii.source_id
WHERE du.database_name = 'tempdb'


Postgres function return table with extra column

Just add the variable to the SELECT list:

CREATE OR REPLACE FUNCTION public.sp_user_get_credentials_by_email(email_address varchar)
  RETURNS TABLE (credential_id integer
               , user_id integer
               , password_hash varchar
               , password_salt varchar
               , created_at timestamp
               , last_updated_at timestamp
               , logical_value_return smallint) AS
$func$
DECLARE
   sproc_logical_value_return SMALLINT := 2;  -- you can assign at declaration time
BEGIN
   RETURN QUERY
      SELECT uc.credential_id,
             uc.user_id,
             uc.password_hash,
             uc.password_salt,
             uc.created_at,
             uc.last_updated_at
             sproc_logical_value_return  -- just put it in the SELECT list
      FROM   myapp_users_credentials uc
      JOIN   myapp_contacts_assoc    ca ON ca.user_id = uc.user_id      AND uc.expired_at IS NULL
      JOIN   myapp_contacts          c  ON c.contact_id = ca.contact_id AND uc.expired_at IS NULL
      WHERE  c.value = $1
      AND    c.type  = 1
      AND    c.is_primary = 1
      LIMIT  1;

   IF NOT FOUND THEN
      RAISE EXCEPTION 'Credentials not found';
   END IF;
END
$func$  LANGUAGE plpgsql ROWS 1;

I simplified with table aliases.

Since your function returns a single row by definition, the value is also returned only once.

You might use RETURNS RECORD combined with OUT parameters instead of RETURNS TABLE since you only return exactly 1 row anyway.

CREATE OR REPLACE FUNCTION public.sp_user_get_credentials_by_email(
                 IN  email_address varchar
               , OUT credential_id integer
               , OUT user_id integer
               , OUT password_hash varchar
               , OUT password_salt varchar
               , OUT created_at timestamp
               , OUT last_updated_at timestamp
               , OUT logical_value_return smallint) AS
$func$
BEGIN

   logical_value_return := 2;  -- assign separately or with SELECT list

   SELECT uc.credential_id, uc.user_id, uc.password_hash, uc.password_salt, uc.created_at, uc.last_updated_at
   INTO      credential_id,    user_id,    password_hash,    password_salt,    created_at,    last_updated_at
   FROM   myapp_users_credentials uc
   JOIN   myapp_contacts_assoc    ca ON ca.user_id = uc.user_id      AND uc.expired_at IS NULL
   JOIN   myapp_contacts          c  ON c.contact_id = ca.contact_id AND uc.expired_at IS NULL
   WHERE  c.value = $1
   AND    c.type = 1
   AND    c.is_primary = 1
   LIMIT  1;

   IF NOT FOUND THEN
      RAISE EXCEPTION 'Credentials not found';
   END IF;

END
$func$  LANGUAGE plpgsql;

RETURNS RECORD is redundant noise which can be omitted in this case.

The subtle difference: You could still return the logical constant, even when no row is found. Other columns would be NULL in this case. Details:

Return multiple fields as a record in PostgreSQL with PL/pgSQL
Declaring the tuple structure of a record in PL/pgSQL


How to implement superkey sub key method on cascading tables

Normally, you'd create a view for each subtype. Do the join between the "super" table and the "sub" table in the view.

If you make the views updatable, client code can just use the views.


Without the execution plans, it's impossible to say, but I've seen a similar scenario pop up.  Had a client once whose database was teetering on the edge of being able to fit in memory.  Someone added an index on a large table, queries started using it (which kept big chunks of it in memory.)  Things all used to fit in memory perfectly, but they hit a tipping point where they started to hammer the disk much more frequently.  We caught it via the sudden spike in disk access and a sudden drop in Page Life Expectancy.

Ideally, you should capture these Perfmon counters continuously on database servers, and alert when they drop dangerously.  That'd help correlate things so you'd know if the PLE drop also happened, and that'd indicate you might be running out of memory to cache things.


unable to copy binary string from table to notepad

in SQL Server Management Studio, go to the "Tools" menu, click on "Options", then navigate to "Query Results", "SQL Server", "Results to Grid", and modify the "Non XML data" option to whatever setting you desire.

Aaron has a great post about how to chunk output so you can see it all, regardless of how large a piece of text you're dealing with at MSSQLTips
However, in SSMS 18, I am able to get substantially larger amounts of text to copy-and-paste from the results window using the option I detailed above.  There are at least a couple of caveats you might need to know about before it starts working.

After you change the "Maximum Characters Retrieved" for Non-XML Data, it only takes effect for new query windows.  i.e. you must open a new query window before you can get more than 65535 characters to copy-and-paste.

If you are testing this by generating a series of characters with replicate, ensure you use an explicit "max" data type, as in varchar(max) or varbinary(max).  If you run SELECT REPLICATE('X', 131072) for example, the resulting string will only be 8,000 characters in length because the implicit conversion is to a varchar type, not a varchar(max) type.  Running this, however, will generate a string that is 131072 characters in length:
SELECT [string] = REPLICATE(CONVERT(varchar(max), 'X'), 131072)
    , [length] = LEN(REPLICATE(CONVERT(varchar(max), 'X'), 131072))

The results:

I modified the "Maximum Characters Retrieved" value to 131072, then opened a new query window and pasted in the code above, hit F5, and got the results you see above.  I then cvopied the value shown in the "string" column to the clipboard, then pasted it into another new query window, where I confirmed the length of the string with the following code:
SELECT LEN(CONVERT(varchar(max), 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX(due to the 30,000 character limitation on questions,  not all the "X"s are shown here) XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX'))

╔══════════════════╗
║ (No column name) ║
╠══════════════════╣
║           131072 ║
╚══════════════════╝


Change expiry date of TDE certificate of User Database without changing Thumbprint

Referencing the information in Updating an expired SQL Server TDE certificate, the post explains that it is easy to create a new certificate with an updated expiration date for use in TDE.

USE [master]
GO

CREATE CERTIFICATE NewTDECert
WITH SUBJECT = 'New TDE DEK Certificate',
EXPIRY_DATE = '20181231';
GO

USE [YourDatabase]
GO
ALTER DATABASE ENCRYPTION KEY
ENCRYPTION BY SERVER CERTIFICATE NewTDECert;
GO

After executing the above commands, your database now uses the new certificate with regards to TDE.

However - the post makes it very clear that you still need to keep the old certificate in case you want to restore the database from a backup created when the old certificate was being used.

From that post (highlighting mine):

  It is up to you whether you want to drop the old certificate from the
  SQL Server instance. You should always keep a backup of the old
  certificate in case you need to restore a TDE enabled database using
  an older backup that used the old key.

Additonally (from that post),

  It is paramount to backup the TDE certificate after any certificate
  changes as this is required to restore the database to another SQL
  Server instance. We can issue a backup certificate command for the new
  certificate as shown below.

USE [master]
GO

BACKUP CERTIFICATE NewTDECert
TO FILE = '\\SQLP2\temp\NewTDECert.cer'
WITH PRIVATE KEY (FILE = '\\SQLP2\temp\NewTDECert.pvk',
ENCRYPTION BY PASSWORD = 'str0ngPa$$w0rd');
GO

Additional information can be found in Replacing an expiring SQL Server encryption key.  (Highlighting mine).

  To rotate the certificate for TDE, add the new certificate as above,
  then execute the command ALTER DATABASE ENCRYPTION KEY with the
  ENCRYPTION BY SERVER CERTIFICATE clause:

ALTER DATABASE ENCRYPTION KEY
    ENCRYPTION BY SERVER CERTIFICATE newCertificate;

  SQL Server re-encrypts the database encryption key with the new
  certificate, and drops the encryption by the old certificate when it’s
  finished. And as before, the data itself isn’t re-encrypted, so the
  process finishes almost immediately.

  One caution: Always keep at least one backup copy of every certificate
  you use. If you ever need to restore a database that used encryption,
  you’ll need the certificate that was in effect at the time the backup
  was created. Make a habit of creating a certificate backup immediately
  after creating it in SQL Server. Store the backup in a safe place;
  also keep a copy of the passphrase you use to encrypt the certificate
  backup, preferably in a different safe place for security. Retain
  these forever, or until the last database backup that may possibly use
  them has been purged.


Error while creating CMK(column master key) in always encrypted

These kind of errors occur when there is something corrupt in windows, so please try repair sql server by running setup and also please do check if there is any pending reboot or an updates pending.
I have face same kind of problem and it was due to pending update so its something to deal with the registry


Using an index for both asc and desc on a string column

Although the syntax for CREATE INDEX supports an option for defining the index as ascending or descending, in the most common two storage engines (InnoDB and MyISAM), this index option is a no-op. There is no such thing as an "ascending" or "descending" index, they are just indexes and can be used for sorting in either direction.

But in the case you describe, where you have a multi-column index and you want to order by one column ascending and the other column descending, that's a different story. The sort order must go in the same direction for both columns if it uses an index.

(Not that every sort has to use an index. You can certainly run the query and let it sort matching rows with a filesort.)

To reverse the sort order of strings, you could add your own custom collation. https://dev.mysql.com/doc/refman/5.6/en/adding-collation.html

But you'd have to store your strings using that new collation, and rebuild your index. The index would only be useful for the reverse-sorting.

Re comment from @Cratylus:

A collation is what defines "A" as being before "B" and all the other ordering of letters. So if you define a collation that says that "B" is before "A", and use that collation in your column definition, then sorting on that column, or creating an index on the column, would be in the reverse order.

Note: the following doesn't create a proper collation, it just demonstrates the steps to create one.

Edit /usr/share/mysql/charsets/Index.xml and make this edit:

<charset name="latin1">

  . . . leave other collations alone . . .

  <collation name="latin1_reverse_ci" id="251">
    <order>Dutch</order>
    <order>English</order>
    <order>French</order>
    <order>German Duden</order>
    <order>Italian</order>
    <order>Latin</order>
    <order>Portuguese</order>
    <order>Spanish</order>
  </collation>

</charset>

Edit /usr/share/mysql/charsets/latin1.xml and make this edit:

<charset name="latin1">

. . . leave other collations alone . . .

<collation name="latin1_reverse_ci">
<map>
 AE B1 AC A2 A0 9E 9C 8D BE 8B 89 87 85 83 7F 59
 73 71 6F 6D 63 61 5F 5D 55 4F 4D 4B 49 47 45 43
 97 B1 AC A2 A0 9E 9C 8D BD 8B 89 87 85 83 7F 59
 73 71 6F 6D 63 61 5F 5D 55 4F 4D 4B 49 47 45 43
 FF FE FD FC FB FA F9 F8 F7 F6 F5 F4 F3 F2 F1 F0
 EF EE ED EC EB EA E9 E8 E7 E6 E5 E4 E3 E2 E1 E0
 DF DE DD DC DB DA D9 D8 D7 D6 D5 D4 D3 D2 D1 D0
 CF CE CD CC CB CA C9 C8 C7 C6 C5 C4 C3 C2 C1 C0
 BF BC BB BA B9 AF AA A8 A6 A4 9A 98 95 93 91 8F
 81 7D 7B 79 77 75 6B 69 67 65 5B 57 53 51 41 B8
 B7 B6 B5 B4 B3 AF AA A8 A6 A4 9A 98 95 93 91 8F
 81 7D 7B 79 77 75 6B 69 67 65 5B 57 53 51 41 40
 3F 3E 3D 3C 3B 3A 39 38 37 36 35 34 33 32 31 30
 2F 2E 2D 2C 2B 2A 29 28 27 26 25 24 23 22 21 20
 1F 1E 1D 1C 1B 1A 19 18 17 16 15 14 13 12 11 10
 0F 0E 0D 0C 0B 0A 09 08 07 06 05 04 03 02 01 00
</map>
</collation>

</charset>
. . .

Restart mysqld.
Confirm you have the new collation:

mysql> SHOW COLLATION LIKE 'latin1%';
+---------------------+---------+-----+---------+----------+---------+
| Collation           | Charset | Id  | Default | Compiled | Sortlen |
+---------------------+---------+-----+---------+----------+---------+
. . .
| latin1_general_ci   | latin1  |  48 |         | Yes      |       1 |
| latin1_general_cs   | latin1  |  49 |         | Yes      |       1 |
| latin1_spanish_ci   | latin1  |  94 |         | Yes      |       1 |
| latin1_reverse_ci   | latin1  | 251 |         |          |       0 |
. . .

Create a table and fill it with some text:

mysql> CREATE TABLE foo (
    id INT AUTO_INCREMENT PRIMARY KEY,
    t VARCHAR(60) COLLATE latin1_reverse_ci,
    x DATE,
    KEY(t)
);

mysql> INSERT INTO foo (t) VALUES ('Harry'), ('Ron'), ('Hermione');

Demonstrate that the sort order is the reverse of what you'd think it should be:

mysql> SELECT * FROM foo ORDER BY t ASC;
+----+----------+------+
| id | t        | x    |
+----+----------+------+
|  2 | Ron      | NULL |
|  3 | Hermione | NULL |
|  1 | Harry    | NULL |
+----+----------+------+

Confirm that it doesn't use a filesort:

mysql> EXPLAIN SELECT * FROM foo WHERE t LIKE 'H%' ORDER BY t\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: foo
   partitions: NULL
         type: range
possible_keys: t
          key: t
      key_len: 63
          ref: NULL
         rows: 2
     filtered: 100.00
        Extra: Using index condition

I had to put a condition on the rows, because otherwise it thought it was going to read all the rows in the table, and it did a table-scan and filesort.

Re comment from @ypercube:

It doesn't reverse the order of characters in a given string like REVERSE(), it reverses which characters are considered greater than or less than each other when comparing two strings. So no, the CHAR padding spaces don't matter, they're still at the end of both strings.

Example (no index this time, just using order by):

mysql> create table t (c char(20) collate latin1_reverse_ci);
mysql> insert into t values ('A'), ('B'), ('C');
mysql> select * from t order by c;
+------+
| c    |
+------+
| C    |
| B    |
| A    |
+------+

Edit: no, @ypercube is correct, this doesn't work with CHAR because the space character is in the wrong place when I naively reorder the collation simply by putting all the bytes in the reverse order. That's not the proper way to design a collation.

So this answer should serve as a demo of the mechanics of creating a collation, not as a proper collation for reversing alphabetical order.


Create a view in a user database that can access selected system database tables

Can this be done by granting certain privileges to the the
role(Checklist_role) without giving privileges to any objects in the
"msdb" database?

Rather than a view, you could instead use a multi-statement table-valued function or stored procedure containing the query and sign the module with a certificate to confer additional permissions. This allows you to follow the principle of least privilege since only permissions on the function or stored procedure are needed.
Below is an example script that creates the certificate in both databases along with the needed user and permissions.
USE ChecklistDB;
GO
CREATE FUNCTION MaintenanceJobs_function()
RETURNS @table TABLE(
     name sysname
    ,description nvarchar(1024)
    ,enabled tinyint
    ,category_id int
)
AS
BEGIN
    INSERT @table
    SELECT [name],
           [description],
           [enabled],
           [category_id]
    FROM [msdb].[dbo].[sysjobs];
    RETURN;
END
GO
CREATE ROLE Checklist_role;
GRANT SELECT ON dbo.MaintenanceJobs_function TO Checklist_role;
GO

--Create self-signed cert in ChecklistDB and copy to msdb
USE ChecklistDB;
CREATE CERTIFICATE CrossDatabasePermissionsCertificate
    ENCRYPTION BY PASSWORD = 'temporary password'
    WITH SUBJECT = 'Allow cross-database access';

--Copy cert to another database.
DECLARE @cert_id int = CERT_ID('CrossDatabasePermissionsCertificate');
DECLARE @public_key  varbinary(8000) = CERTENCODED(@cert_id),
        @private_key varbinary(8000) = CERTPRIVATEKEY(@cert_id , 'temporary password', 'temporary password');

--these values should not be NULL
IF @cert_id IS NULL THROW 50000, 'Assertion failed: @cert_id is NULL', 0;
IF @public_key IS NULL THROW 50000, 'Assertion failed: @public_key is NULL', 0;
IF @private_key IS NULL THROW 50000, 'Assertion failed: @private_key is NULL', 0;

--copy certificate in target database
DECLARE @sql nvarchar(MAX) =
      'CREATE CERTIFICATE CrossDatabasePermissionsCertificate
       FROM  BINARY = ' + CONVERT(varchar(MAX), @public_key, 1) + '
       WITH PRIVATE KEY (BINARY = ' +
          CONVERT(varchar(MAX), @private_key, 1)
        + ', DECRYPTION BY PASSWORD = ''temporary password'''
        + ', ENCRYPTION BY PASSWORD = ''temporary password'');'
PRINT @sql; --show CREATE CERTIFICATE DDL

EXEC msdb.sys.sp_executesql @sql;
GO

--create certificate user in msdb and grant permissions needed by the module
USE msdb;
CREATE USER CrossDatabasePermissionsCertificateUser FROM CERTIFICATE CrossDatabasePermissionsCertificate;
GRANT SELECT ON dbo.sysjobs TO CrossDatabasePermissionsCertificateUser;
GO

--add signature to module by the same certificate that exists in both databases
USE ChecklistDB;
ADD SIGNATURE TO dbo.MaintenanceJobs_function BY CERTIFICATE CrossDatabasePermissionsCertificate WITH PASSWORD = 'temporary password';
ALTER CERTIFICATE CrossDatabasePermissionsCertificate REMOVE PRIVATE KEY;
GRANT SELECT ON dbo.MaintenanceJobs_function TO Checklist_role;
GO

--test with minimally priviliged login/user
CREATE LOGIN TestUser1 WITH PASSWORD = 'Test-Permissi0ns';
CREATE USER TestUser1;
ALTER ROLE Checklist_role
    ADD MEMBER TestUser1;
EXECUTE AS LOGIN = 'TestUser1';
SELECT * FROM dbo.MaintenanceJobs_function();
GO
REVERT;
GO


How does the correlation statistic parameter affect the cost of index scan?

The details you want will not be found in the documentation, but rather in the source code.  In particular, src/backend/optimizer/path/costsize.c in the function cost_index.

It computes min_IO_cost based on the assumption the table is correlated, max_IO_cost based on the assumption it is uncorrelated, and then interpolates between them based on the correlation.


MYSQL 5.7 Partition a table based on partition key of another related table

NOTE:  FOREIGN KEYS are not allowed in partitioned tables.  Simply drop the FKs but keep the indexes that they generated.  Presumably, the FK checks are redundant now that you have debugged your app.

adding an additional column t_execute_on_date is just not possible.

See pt-online-schema-change or gh-ost.
Using PARTITIONs is the optimal way to delete "old" data from the history table, but there are other approaches:  http://mysql.rjweb.org/doc.php/deletebig
The one that comes to mind is to have a continually-running job that keeps looking at the "next 1000" rows to see if any of them should be deleted.  (It must walk through the table using the PRIMARY KEY.)  It might do a LEFT JOIN to see which ones are gone, as hinted at in this pseudo-code:
$a = 0;
Loop...
    $z = SELECT th_id FROM transaction_history order by th_id LIMIT 1000,1;
    exit loop if $z IS NULL
    SELECT th_id
        FROM      transaction_history AS the
        LEFT JOIN transaction AS t  ON th.t_id = t.t_id
        WHERE t.t_id IS NULL
          AND th.th_id >= $a
          AND th.t_id   < $z
    DELETE those rows -- probably better to do this as a single query
$a = $z;

When you exit the Loop, simply start over.
The hope is that this approach to deleting "old stuff" will be fast enough to keep up, though there will be some lag.
Still, I would hope that pt-online-schema-change or gh-ost can turn a non-partitioned table into a partitioned table with minimal downtime, regardless of table size.  (No FKs.)
Caution:  The optimal indexes for a partitioned table are usually different than the equivalent non-partitioned table.  Toss any unused tables when adding partitioning; there is some overhead when building a table with the secondary indexes already in place.


Index that is not used, yet influences query

When you create an expression index, it causes PostgreSQL to gather statistics on the that expression.  With those statistics on hand, it now has an accurate estimate for the number of aggregated rows that the query will return, which leads it to make a better plan choice.

Specifically in this case, without those extra statistics it thought the hash table would be too large to fit in work_mem, so it didn't choose that method.


Error updating sql server geography type in trigger

Try this one. I have moved the conditional update into a single statement because the action you were taking was the same for both conditions. Also I have altered the way that you join to the INSERTED table so that it performs the filter pre-join:

CREATE TRIGGER dbo.triggerGeocodedAddressUpdate ON dbo.Party AFTER UPDATE AS
IF UPDATE(Latitude) OR UPDATE(Longitude)
    UPDATE pt
    SET pt.GeocodedAddress = geography::Point(i.Latitude, i.Longitude, 4326)
    FROM dbo.Party AS pt
    JOIN
    (
        SELECT PartyId, Latitude, Longitude
        FROM inserted
        WHERE (Latitude IS NOT NULL AND Longitude IS NOT NULL)
    ) AS i ON i.PartyId = pt.PartyId;
    RETURN
GO

I hope this helps you.


Should every table in my database include store id?

This problem is called multi-tenancy. You want to offer the database service to multiple 'tenants' (in your case, stores). Microsoft published some time ago a paper on the topic, Multi-Tenant Data Architecture which highlights some approaches (separate database, shared database separate schemas, shared tables) with pros and cons of each. Your approach is shared tables, and yes, you do need a store_id in table that contain per-store data (there are usually some shared tables that do no require a store_id). Not only that, but all indexes will require store_id as a leftmost key. All your JOIN must also add AND left.store_id = right.store_id as a join condition. And all your queries must have a WHERE store_id=@store_id filter.


Restore Oracle database from cold backup files

Cold backups, like hot backups can be taken in different ways. One is by using rman - recommended - the other is using OS tools like copy/dd. Next to that, storage based snapshots could be used. So in order to know how to restore, it is important to know how the backup has been made.

If the backup is properly made using RMAN, there is not much more to do than starting your instance, connect to the catalog, or find a snapshot controlfile that contains the backup administration, connect to the instance and issue a restore database command. If it is an OS tools backup, use the same tool[s] to copy the files to their original locations and start the database, once done.

There are many potential pitfalls using cold OS tool backups. I have seen situations in which the admin told me ....

  'eventually we switched to xcopy because we got file locking problems
  ...'

instead of looking further. He missed the point that the database was started during the cold backup. This is one of my reasons not to be a big fan of COLD backups. They make me shiver.

Be smart and carefully read Oracle® Database Backup and Recovery User's Guide
11g Release 2 (11.2) It is worth every minute you spend studying it. Oracle backup and recovery is not hard, there are just a lot of options.


RMAN deletes all conrtolfile autobackups

Hopefully this has been fixed in more recent versions but I had a very nasty experience with this on an 11gR2 training course, many moons ago.

"... REDUNDANCY 1 ... "

... tells Oracle to keep a single backup copy of each file.
It does not tell Oracle to keep a single, consistent (i.e. recoverable) set of backup files!
An entire class of trainees happily built shiny, new databases and then took a backup of them.
So far, so good.
Much later in the day, then got to the exercise of taking another backup, trashing their database and restoring it.
Problem was, the backup ran out of disk space part way through, leaving an inconsistent and unrecoverable set of files - a mish mash of the earlier backup and the new one.
"REDUNDANCY 2" would have prevented this, retaining the previous, recoverable set of backup pieces.
As would more disk space, obviously.
I'd recommend using a RECOVERY WINDOW instead.  It will fall more naturally in line with your [company's] Recovery Strategy.


Consistent backup of large DB --single-transaction

If you want the binlog file and position, just include --master-data.

According to the MySQL Documentation on mysqldump's --master-data option

  Use this option to dump a master replication server to produce a dump
  file that can be used to set up another server as a slave of the
  master. It causes the dump output to include a CHANGE MASTER TO
  statement that indicates the binary log coordinates (file name and
  position) of the dumped server. These are the master server
  coordinates from which the slave should start replicating after you
  load the dump file into the slave.

  If the option value is 2, the CHANGE MASTER TO statement is written as
  an SQL comment, and thus is informative only; it has no effect when
  the dump file is reloaded. If the option value is 1, the statement is
  not written as a comment and takes effect when the dump file is
  reloaded. If no option value is specified, the default value is 1.

  This option requires the RELOAD privilege and the binary log must be
  enabled.

  The --master-data option automatically turns off --lock-tables. It
  also turns on --lock-all-tables, unless --single-transaction also is
  specified, in which case, a global read lock is acquired only for a
  short time at the beginning of the dump (see the description for
  --single-transaction). In all cases, any action on logs happens at the exact moment of the dump.

  It is also possible to set up a slave by dumping an existing slave of
  the master. In MySQL 5.5.3 and later, you can create such a dump using
  the --dump-slave option, which overrides --master-data and causes it
  to be ignored if both options are used.

Here is a sample script

MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
MYSQLDUMP_OPTIONS="--single-transaction --master-data=2 --all-databases"
mysqldump --all-databases  | gzip > dumpfile.sql.gz

You said the the data is 330GB, so the gzip may be 30-40GB. No worries. If you run

less dumpfile.sql.gz

The CHANGE MASTER TO command with the log file and log position will be located on line 23.

Please keep one thing in mind: You cannot run any DDL against InnoDB tables during the mysqldump or else the tables being dumped after that DDL will no longer be point-in-time copies.

BTW there will be no downtime. Just make sure the binary log in the dump is still in existence on the Master when you load the Slave and setup replication.


What is the point of a consistent and partition tolerant multi-master DB Architecture as opposed to Master-Slave

The two arguments you mentioned are independent of weather the system is CP or AP. They are advantages in general for any distributed system with replication. Let's stick to CP as that is the main topic. In a CP distributed system, with replication factor 2, the write should finish on 2 nodes before the client is acknowledged of the write. So, yes that overhead of writing on multiple nodes and the synchronisation is present. But that distributed system may have 50 nodes. The writes will be load balanced across those 50 nodes. And because the replication factor is 2, even if 1 node is down, the reads and writes can continue without any down time.

why CP then ? Putting in a very very naïve way, CP will never loose acknowledge write but AP can loose acknowledged write in a split brain situation. Also, CP will guarantee that if an application updates a value from A to B, a subsequent read by it will always read B. But in an AP system, it may read either A or B. A CP system will deny a write or read if it cannot guarantee its consistency. An AP is system is more relaxed about it. This can be a pretty big deal depending on the type of application. For e.g. if the application is tracking the number of likes, its probably ok to loose a few in a worst case scenario. If the application is dealing with user's money, it should be accurate in all scenarios. Such applications are typically ready to pay the extra overhead.


What are the differences between the first and subsequent rds_superusers in an AWS RDS Postgres Instance?

rds_superuser, unlike a real superuser, doesn't inherently have permission to create roles.

You will have to have the other rds_superuser (or anyone else who already has createrole) do:

alter user new_user createrole;


User based payment system table designing

Hi, i think this model can fit you needs.


MySQL binary log reporting execution time as UINT_MAX

The unsigned value 4294967295 has the same binary representation as signed -1.  So I suggest that it is reporting "-1 seconds".

Either your machines are not in sync on time, or it is a bug in MySQL.

I have several seen other cases where "the clock ran backwards", even on machines that are synced with the time service.

Consider finding or filing a bug with bugs.mysql.com


Can I execute an external process via MySQL on table change?

It is possible, and it also makes for a security threat.

Anyway, to make that happen you would need to create (program, compile and install plugin) you own UDF - User Defined Function. It's a C/C++ code, so you can do anything you like in there.


When you do top 1 the query optimizer will build a plan that is built to fetch 1 row as fast as possible.

When you use a local variable the value of the variable is unknown to the optimizer and instead builds a plan that is optimized to fetch 100 rows as fast as possible.

In your case the query plan generated with a row goal of 100 is the better plan to use even when you only want one row.

To verify you can try to add option (recompile) to your query with the variable. In that case SQL Server will use the current value of @topCount as a row goal and since that is 1 you should get the slow plan.


PL/SQL - Drop User inside BeginEnd block

Data Definition Language (DDL) Statements

  The DDL statements are:

  ... DROP ... (All statements beginning with DROP) ...

PL/SQL does not support DDL as static SQL:

PL/SQL Static SQL

To run a DDL statement in PL/SQL, use dynamic sql:

PL/SQL Dynamic SQL

Example:

begin
  ...
  execute immediate 'DROP USER &OWNER CASCADE';
  ...
end;
/

Or just use:

BEGIN
  FOR r IN (select sid,serial# from v$session where username=&OWNER)
  LOOP
      EXECUTE IMMEDIATE 'alter system kill session ''' || r.sid  || ',' || r.serial# || '''';
  END LOOP;
END;
/

DROP USER &OWNER CASCADE;


Should I invest the time to change the column type from CHAR(36) to UUID?

I would consider changing to the UUID type.  char(36) takes 40 bytes, uuid takes 16, so you'll save 24 bytes per row, which for you will equate to 12 MB a day, 4 GB after a year.  Plus indexes.  Depending on what hardware you have, that isn't much, but it could be.  And it adds up if you have more improvement opportunities like this.

Also, I see no constraint in you schema that ensures that interaction_id is actually in the right format.  Using the right type will give you that as well.

If you like this, however, than using bigint would save even more and have even better performance.  It's very unlikely that your application is so large that a bigint for an ID column won't work.


MySQL 5.5 Innodb_buffer_pool_bytes_data status variable missing

Run show global status like 'Innodb_buffer_pool_bytes_data';


How can I determine which permissions needs to be assigned to the newly created login?

The link by @Ross will definitely help you determine syntax and what all of your options are, but for a little more guidance:

There are two database roles that will probably be useful to you, they are db_datawriter and db_datareader.  The two of them will probably handle the majority of your CRUD (Create, Read, Update, Delete) operations.

ALTER ROLE db_datareader ADD MEMBER <user>
ALTER ROLE db_datawriter ADD MEMBER <user>

You can get more granular than this if you want/need.  For example, allowing inserts, but not updates or deletes to tables.

You mentioned stored procedures in the tags, so those can be a bit trickier.  One of the easier methods is to grant execute on the schema.  But you can also grant them one at a time.

--all stored procedures in schema dbo
GRANT EXECUTE ON SCHEMA::dbo TO <user>

--specific stored procedure
GRANT EXECUTE ON dbo.uspDoMyThing TO <user>

Finally, if you are allowing the application to do TRUNCATE then they will actually need the ALTER TABLE permission in addition to db_datawriter.

GRANT ALTER TABLE ON dbo.MyTable TO <user>

Congratulations on taking control of the security of your server properly.  Even if you have to end up granting db_owner in the database, that's far better than sysadmin.

Some final notes:

Explicit deny permissions take precedence over grants.
Assigning permissions to schemas will grant permissions to all objects in that
schema (even if they get created later).
I would start with db_datareader, db_datawriter and EXECUTE on all stored procedures and that will get you going the fastest with the least maintenance
required later.  It may be broader than necessary but the damage that
can be done is minimized.


Do we need to keep extra memory for some kind of memory mapped files for MongoDB data files

The mem.mapped and mem.mappedWithJournal metrics in serverStatus output are only applicable to the MMAPv1 storage engine, so are expected to be 0 with WiredTiger. These metrics predate the storage engine API (and WiredTiger) so will likely be moved to a storage-engine specific section of serverStatus at some point.

  Does the WT cache contains the working set in terms of compressed data and index ?

The WiredTiger internal cache is used for uncompressed data which is a different representation from the compressed data format on disk. Memory outside the WiredTiger cache is available for filesystem cache (which caches the on-disk representation, including compression) as well as other temporary RAM requirements such as per-connection overhead (1MB per connection), in-memory sorts, aggregations, and JavaScript contexts.

Your working set is the total set of data and indexes that your application frequently accesses. For best performance this should ideally fit into RAM. A larger WiredTiger cache will allow for more uncompressed data in RAM at the expense of memory available for other purposes. If your data compresses significantly, more free memory for the filesystem cache will allow for a larger total working set in RAM (uncompressed + compressed data).

  So I would guess I've no memory mapped bytes, then I can increase de WT cache ?

As per the MongoDB documentation, you should generally leave the WiredTiger cache at the default size (or possibly even decrease this). You can always try to experiment with tuning the cache size for your workload and deployment, but setting this too large may adversely affect performance.


How to create a Hash Computed Column for Many Columns?

Just concat your columns together rather than trying to convert them into xml and then a hash.

CREATE TABLE [dbo].[CustomerTransactiont]
(
    CustomerTransactionId int primary key identity(1,1),
    CustomerName varchar(255),
    Price decimal(10,2),
    Quantity int,

    RowHash as hashbytes('SHA2_512', CONCAT(    UPPER(COALESCE(CustomerName, '')), '|'
                                                , COALESCE(CONVERT(VARCHAR(50), Price), ''), '|'
                                                , COALESCE(CONVERT(VARCHAR(20), Quantity), ''), '|'
                                                )) persisted
)

Note: The coalesce and the pipe characters were just for examples. The Upper is optional if you either want hash to be case sensitive or not.


Encountering "Snapshot too old" error during execution of expdp command

The cause of the ORA-01555 error is undo_retention parameter has the lower value as compared to the time taken to complete the export with the parameter consistent=y(which is deprecated in 11g and should use flashback_time or flashback_scn).

Your undo retention should able to retain the undo information for the time period of export job.

For example, if you export starts at 00:00 AM and it takes 2 hours to complete then the undo_retention should be set at least 2 hours if you wish to get consistent data.

I have simulated the problem in my test server.

[oracle@orcl expdp_dump]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Wed Feb 8 09:41:40 2017

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

SQL> show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     2400
undo_tablespace                      string      UNDOTBS1

My undo retention is set to 40 minutes.

SQL> select bytes/1024/1024 from v$datafile where ts#=2;

BYTES/1024/1024
---------------
          32764

And my undo tablespace is 32GB large.

I tried to export one of my schemas.

expdp system/password directory=DP_DIR schemas=MYSCHEMA dumpfile=expdp_orcl_MYSCHEMA.dmp logfile=expdp_orcl_MYSCHEMA.log FLASHBACK_TIME=SYSTIMESTAMP

  Job "SYSTEM"."SYS_EXPORT_SCHEMA_07" completed with 1 error(s) at Tue
  Feb 7 22:14:46 2017 elapsed 0 01:14:39

It took 1 hour and 14 minutes. And there were moderate DML operations in the database.

ORA-31693: Table data object "MYSCHEMA"."MS_TABLE":"MS_TABLE."MS_TABLE_B_2005" failed to load/unload and is being skipped due to error:
ORA-02354: error in exporting/importing data
ORA-01555: snapshot too old: rollback segment number 84 with name "_SYSSMU84_1745672109$" too small
. . exported "MYSCHEMA"."PRORATE_EVENT"              8.789 KB       4 rows
. . exported "MYSCHEMA"."PRORATE_RULE_GROUP"         5.945 KB       1 rows
. . exported "MYSCHEMA"."ORACLE_JOB_METADATA"            0 KB       0 rows

And I got the ORA-0155 error.

Then I increased the undo retention to 1 hour and 20 minutes.

SQL> alter system set undo_retention=4800;

System altered.

And again I tried to export the schema.

expdp system/password directory=DP_DIR schemas=MYSCHEMA dumpfile=expdp_orcl_MYSCHEMA.dmp logfile=expdp_orcl_MYSCHEMA.log FLASHBACK_TIME=SYSTIMESTAMP

  Job "SYSTEM"."SYS_EXPORT_SCHEMA_07" successfully completed at Wed Feb
  8 22:15:52 2017 elapsed 0 01:15:45.

And there was no ORA-01555 anymore.

Total estimation using BLOCKS method: 182.9 GB

and the actual file size is 141 GB.

Why the difference in estimation and the actual size of the dump file? Look at this post for the answer.

Regarding the time taken to complete the export. Try to use the following parameters.

 Parallel
 DIRECT=y

Also, try to gather data dictionary statistics before starting the export job-

SQL> connect / as sysdba
SQL> exec dbms_stats.gather_dictionary_stats;
SQL> exec dbms_stats.lock_table_stats (null,'X$KCCLH');
SQL> exec dbms_stats.gather_fixed_objects_stats;

Futher Readings:
Poor Performance With DataPump Export On Large Databases (Doc ID 473423.1)
Data Pump Export


Gap resolution of Logical Standby Database via Archive Log Backups

If you can see the backup file in the directory, try cataloging it:

RMAN> catalog starts with '/path/to/archive/directory';

It will list the backup and ask you if you want to catalog it.
Following which you should be able to continue with your gap recovery:

alter database recover managed standby database cancel;
recover standby database;
#after all existing logs are applied put standby database to recover managed
mode
recover managed standby database

If you don't have the archived logs anymore, you can do an incremental backup with:

"backup incremental <from scn> until scn"


So The question has been answered in great detail in chat by ypercubetm https://dba.stackexchange.com/users/993/ypercube%e1%b5%80%e1%b4%b9

The short anwer is yes the solution above is one possible correct solution

Many thanks to ypercubetm for taking the time breaking it down for me!


Off the top of my head I might think it to be related having 4 different DB files and large ones at that (not sure why that would be). Can you easily scope the query to hit 1 vs 4 DB files to see if the preformance is increased?


PostgreSQL COPY TO doesn’t honor Linux group privileges

Server reboot helped. Privileges are treated as expected
(Not sure if only DB engine is enough to fix that thought - I had to reboot entire machine)
Thanks @Colin for tip


Query cache can be combined with memory storage but it'll only make sense if don't have many updates (as your cached results will be no longer actual after your change your data).

Basically query cache stores results of your SELECT queries (if run again, data is retrieved faster), key buffer size stores indices (helps to use them more efficiently, i.e. to retrieve data quicker) and you said you know what memory storage is.

All three options you mentioned can be used together, but the real efficiency depends on your usage pattern. But hey won't do any harm when combined (by that I mean assigning bigger values to cache and buffer while using memory storage) as long as you have enough memory.

P.S. As you probably reckon yourself, your setup screams you're using a wrong tool. I can imagine situation where it is still necessary, e.g. using legacy / unchangeable code that depends on MySQL but otherwise consider redesigning and using other data storages besides MySQL, permanent or temporary like memcache.


Kevin's answer describes what events to capture in SQL Trace/SQL Profiler. To expand on that answer a bit - SP:StmtCompleted will show you each statement within a stored procedure being completed, as it sounds.

Also if you are on a busy system and trying to diagnose a performance issue you should be careful with SQL Profiler. SQL Profiler is much slower than tracing to a file or using Extended Events. This blog post by Jonathan Kehayias shows about a 90% overhead on a system's performance from using SQL Profiler and about a 10% overhead from tracing to file. Less for Extended Events. That is why it is typically recommended to not run SQL Profiler itself whil

While this information is available through Extended Events, I would suggest still using SQL Trace (the technology behind SQL Profiler) but tracing to a file instead (if you wanted to invest in learning and using Extended Events this would be the way to go, in a future version of SQL Server SQL Trace will be gone and all we'll have is Extended Events) . I would also suggest you filter through the Column Filters button out as much background noise as possible to make sure you are only capturing what is necessary. You can setup your trace with the Profiler tool using the steps Kevin describes in his good answer and then add a filter from that same GUI. Then you can export the trace as a script and run that script on SQL Server tracing to a file on a folder that doesn't contain database or transaction log files. To export, you would simply setup your trace, run it for a few seconds to just make sure you are capturing what you want, stop it and then go to the menu bar and File -> Export -> Script Trace Definition and save the file. Then open that file in a new query window on the server you intend to trace. You can see more about the options and definitions of this script you created by looking at the help articles for the various stored procedures used in that script you just created by starting here.

If you have time and want to learn, you can also read some articles on Extended Events and see how to capture the information as well. Jonathan Kehayias is a great resource for blog posts when you are ready to start there.


Feature of database-level audit in SQL Server 2012 Express edition

Old question, but since it got bumped I will answer with some updated information.

While Express 2012 was limited to server level auditing (referred to as "basic auditing" in feature comparison documentation) and its RTM SSMS version was limited in GUI functionality for auditing, newer versions have removed both of those limits:

The newest 17.x versions of Management Studio are 100% compatible with SQL 2012 (and every subsequent version) and has no GUI-based limitation on auditing features (assuming they're supported by the SQL Server version it is connecting to).
As of SQL Server 2016 SP1, "fine grained auditing", i.e. database level auditing, is available across all SQL Server editions.

The term "fine grained auditing" is strange in context of SQL Server, as it seems to only be used in feature comparison docs by Microsoft, but not in any other documentation.

Additionally, I have written a distilled comparison of key features across different editions and versions of SQL Server, focusing on the perspective of Express.


they would somehow only needed root credentials to somehow get user
  and password from "mysql db" and then use them to create the
  connection using customer db credentials. Is this possible?

No, this is not possible, passwords are stored hashed. Hashes are one-way functions where if you get the password, you can get the hash, but it is virtually impossible to do the opposite.

Your API must store temporarily the password, somewhere when it is created (probably on memory), and then send it securely to the client application, then discarded. If that is not desired, you should try a different authentication method that doesn't require passwords or shared secrets.

MySQL authentication should be separate from an application authentication. You could store user credentials (properly hashed and salted) in a database table, but you should keep the users of your application and the users of a database separated (or use an external authentication mechanism, as suggested above).


INSERT INTO a large amount of data with many clients running SELECT

In source_table, are there often duplicate values?  I assume a is the PRIMARY KEY of dest_table?  If there are often dups, change to

INSERT INTO target_table
    SELECT DISTINCT a FROM source_table
ON DUPLICATE KEY a=a;

DISTINCT moves work into the SELECT, thereby lessening the conflicts over the INSERT.

Better yet, do this:

INSERT IGNORE INTO target_table
    SELECT DISTINCT a FROM source_table;

IGNORE is probably less work than IODKU.

Please provide SHOW CREATE TABLE for both tables; there could be subtle issues, especially with the PRIMARY KEY, timestamps, and secondary indexes.


Database Design question, calculate or save financial positions

You want to store the details of the transaction at the time it happens and not calculate at the time you select the record. This is because the information the calculation is based on (such as tax rate ) is subject to change. So yes, store the tax calculation. This is not an issue of performance, it is an issue of temporal data that must be shown as of the date of the action not as it is today. The alternative design is to put dates on everything you use to calulate and then calculate on the fly joining not only on the id but on the date, but this is far easier to make a mistake on and far more likely to be a performance problem.


One MySQL user from 2 hosts not on same subnet

Look at the mysql:user table  The user is likely enabled only from one host (by name or IP).  You can do one of two things:

Add a second occurrence of the user with access from the second host. (Most secure.)
Change the host criteria to be less restrictive. (Less secure.)

See the MySQL Access Restrictions documentation for possible less restrictive host specifications.


Automatically generate ALTER TABLE to syncronize schema on PostgreSQL

You should use Liquibase to manage your database schema changes. You can write the schema in SQL (it was all xml back in the day). It's pretty darn easy once you get the hang of it. You can then keep all schema changes in source control. Bitbucket is free and private for small groups.

In the meantime though, you can use Liquibase to see what the differences are, and generate change scripts, in an automated fashion.

http://www.liquibase.org/documentation/diff.html


This might work for your scenario:

Create a table "COMMANDS" to bulk the data from the select statement , and add a field to let you know if that row has been proccesed.

Once you have data on "COMMANDS" , you can create a procedure that exec all the statements
that still un-proccesed.

You can make it with a loop or a cursor or CTE ...

Good luck.


Can one database affect the performance of another in MongoDB?

I can add, that WiredTiger and mongo 3.2.11 will not help you. Theoretically it hasn't global locks, but in practice I have a bit more beefier server (12 core, 96GB RAM) and two databases. DB1 is in production (it is only 300GB in size, so mostly in RAM) and DB2 which I prepare for next release.

Surprisingly, found that mass updates and deletes on DB2 (also 300GB), even in one thread (one console or java application) make regular indexed queries on DB1 run for 5-10 SECONDS.

When I stop all activity on DB2 it returns to 3-400ms.

In other topics I've read that deleting by unindexed field might lead to such behaviour, but unfortunately, not in my case. I update/delete by "_id" and still performance is terrible.


Effect of TRUNCATE TABLE different from DELETE on subsequent queries

OTHERDB..oldtable --> the data could be in buffers from the first run.

Try running

DBCC DROPCLEANBUFFERS --Not in production though !!

and then your insert query.


With spinning-platter disks you want to have the logs and data on separate drives as random access data disrupts the sequential log write operations, making the logs a performance bottleneck.  SSDs do not have this issue as they lack the performance constraints imposed by the mechanical action of conventional hard disks.

If you're getting SSDs for a DB server, get ones designed for a long life span, such as Intel S3700s.  You're probably better off getting two of those than four cheaper ones, and 2x100GB units + a hot spare (which is probably enough to keep you going for a few years at your current rate of growth) should cost around £200 each according to Google Shopping.

Mixing logs and data on the same disks isn't such a big deal any more.  However, you may get some resiliency out of having separate log drives.  If you want to do this then 5 disks (enough for two pairs + a hot spare) should still see you with change out of £1,000.

Try two good quality drives in a RAID-1, with a hot spare for a starter.  Your growth suggests you probably won't have transaction volumes so high that this setup can't keep up with your application workload.

Back up your database onto spinning disks - a couple of enterprise-grade SATA disks and a hot spare should cost you a few hundred dollars.

If (and only if) you have performance problems with that lot then you might look into adding more SSDs, but I suspect just one pair will be fast enough to keep up with your transaction volumes quite comfortably.


UNTESTED - You did not provide any DDL or sample data scripts...
IF is a flow control construct that determines which statement block will be executed.
What you need are Conditional Expressions. In your case, the COALESCE function should do the trick, also look at CASE for non null condition predicates.

SELECT COALESCE(accounts.email, professionals.email) AS Email
FROM professionals
LEFT JOIN accounts ON accounts.account_id = professionals.account_id
LEFT JOIN users  ON users.user_id = professionals.user_id;


You need a one to many relationship between Book and Writers to Images.

image_id should be a Primary Key just like writer_id and book_id providing a unique id of that specific image within your data.

Than you can have book_id and writer_id fields in Images table which will hold which book or writer this image belongs to. Or another way would be to add an image_type field as enum for book or writer, and add a parent_id field where you will hold the book_id or writer_id depending on the type.

Yes, you can keep the relationship logic as part of your application away from the database, or you can add foreign constraints and relationships after adding some data given that you don't have anything that break those constraints.


If the queries that are accessing the table that need to work while you're making this change are read-only, you could create a view that has the original column and uses the function in place of the computed column, then in a transaction rename the table and create a synonym with the original name that points at the view, then make your changes, start another transaction where you drop the synonym and rename the table, then finally drop the view. If the table needs to be read-write in this time, this may not be sufficient.

This isn't strictly doing what you asked (changing main without affecting computed), but I still think it has the desired effect - minimal impact on users.


Better way to update table with value from another table

You could also remove any doubt about the impact of the subqueries using:

DECLARE @CoupeID INT, @SedanID INT;

SELECT @CoupeID = MAX(CASE carType WHEN 'Coupe' THEN Id END),
       @SedanID = MAX(CASE carType WHEN 'Sedan' THEN Id END)
FROM dbo.carTypes WHERE carType IN ('Coupe','Sedan');

UPDATE c SET c.carType = @CoupeID
FROM dbo.cars AS c
WHERE c.carType = @SedanID
AND EXISTS (SELECT 1 FROM dbo.doorcount AS dc
  WHERE dc.Value = 2 AND dc.carId = c.Id);

Of course appropriate indexes on specific columns mentioned here would be useful for performance (again, if you post the plans somewhere, we can probably help identify opportunities). But in general, an update is an update, and unless these tables are massive, even the slowest way to find the rows to update will pale in comparison to the cost of the update itself.

Please also always use schema prefix and be very careful about CaSe SenSiTivIty.