In Change Data Capture (CDC), how to configure retention with success?

I answered this question in my blog at Sqltimes.wordpress.com
As far as I know, the only way to configure retention period is by updating the retention column in the msdb.dbo.cdc_jobs table. You want to update the retention column of the record with 'cleanup' in job_type column. It could be done in two ways:

EXECUTE sys.sp_cdc_change_job @job_type = N'cleanup', @retention = 349082;
UPDATE msdb.dbo.cdc_jobs SET retention = '666000' WHERE job_type = 'cleanup'

If you have more than one CDC running, then using the appropriate CDC instance name.

A Little more info:

When the CDC cleanup job runs, it purges all data in the CDC tables up to this date. You can stop the CDC cleanup job any time while it is running. When you start it, it will pick up from where it left off. So if you stopped the job while it is running today and start to run it tomorrow it will start from where it left off yesterday and perform purge until the retention period mentioned in the msdn.dbo.cdc_jobs table.

CDC is pretty robust technology.

Hope it help,
_UB


What known bugs still exist in SQL Server 2005 after SP4 CU4?

Microsoft doesn't really keep a public list of open bugs. The ones that are the most important (e.g. anything involving security) are kept private for obvious reasons - they actually aren't published until they are fixed (and even then they often remain private). Not that there have been many security issues with SQL Server in the past 12 years, but by necessity, we don't know what we don't know.

And to be honest keeping such a list out in the open would be like BMW keeping a bulletin board with all the problems that still exist on their 2006 M3. It's kind of like a dirty laundry list, and other than satisfying customers who refuse to upgrade to more modern (and arguably "more fixed") versions, what would it do for them? :-)

Now, you could go out to http://connect.microsoft.com/sql/ and peruse the list of bugs opened against SQL Server 2005 that are still open. But that wouldn't be the whole story, since many bugs were opened against 2005, then closed as fixed when the fix was actually implemented in a more modern version. Never mind the large number that were closed as by design or not reproducible but that still affect you to this day.

You could also go to the knowledge base http://support.microsoft.com/kb and try to find all of the issues listed there that haven't been updated to include the text "this issue has been fixed in [such and such update]." This would also be a Herculean task that I would not wish against my worst enemies.

In the end, if you are not affected by bugs you do not know about, they may as well not exist, right? And if you are continuing active development against SQL Server 2005 and you come across an issue, it is highly, highly unlikely that you will be the first person to come across it. So if you come here for assistance you will stand a very good chance at getting the guidance you need - either a workaround to get around the bug by coding the query a different way, or a trace flag, query hint, or a completely different approach.

I know this isn't the answer you're looking for, but I'm quite afraid you're not going to get the answer you're looking for. This answer (and this site, to be honest) is quite possibly the next best thing. When you have a specific issue, bring it here, and we will help.


Cannot list tables (views, packages etc) from Oracle 11 database using sql explorer, but can see them using PL/SQL Developer

SQLDeveloper  shows tables that are owned by the logged in user. To see other users' tables, right click on connection ("dev" in your case), click "Schema Browser", and then select a user whose object you want to see.

Or directly query system views (e.g. all_tables )


CASE is an expression that returns a single value. You are attempting to use it as control of flow logic to optionally include a filter, and it doesn't work that way. Maybe something like this would work:

AND COALESCE(Project.Contribution, 0) >= CASE @BudgetFilter
  WHEN 0 THEN 25000 ELSE 2000000000 END
-- guessing that 2 billion is enough to always be greater
-- than the highest valid contribution

Expanding to your three conditions, you need to use multiple expressions because you can't use CASE, again, to change >= to <. So something like:

AND COALESCE(Project.Contribution, 0) >= CASE @BudgetFilter
  WHEN 0 THEN 25000 ELSE 2000000000 END
AND COALESCE(Project.Contribution, 0) < CASE
  @BudgetFilter WHEN 1 THEN 25000 WHEN 2 THEN 0 ELSE -2000000000 END)

Or you can use the OR conditionals as mentioned in the comments:

AND ((@BudgetFilter = 0 AND Project.Contribution >= 25000)
  OR (@BudgetFilter IN (1,2) AND Project.Contribution < CASE
    @BudgetFilter WHEN 1 THEN 25000 ELSE 0 END))

Or you can just use dynamic SQL, which may be useful if you have a lot of other criteria and/or you are having difficulty getting consistent behavior from the plans for the above variations:

DECLARE @sql NVARCHAR(MAX) = N'SELECT ...
   WHERE ...
   AND COALESCE(Project.Contribution, 0) '
+ CASE @BudgetFilter
  WHEN 0 THEN ' >= 25000'
  WHEN 1 THEN ' < 25000'
  WHEN 2 THEN ' < 0' END + ';';

EXEC sp_executesql @sql;

(And in case you're wondering why I use COALESCE instead of ISNULL. That said, the COALESCE/ISNULL is really meaningless here, because the COALESCEd value (0) will only potentially meet the < 25000 criteria - and I'm not sure that's what you intended at all.)


Is my Publisher database my actual source database or is it a new database?

Yes, your publication database is your source database.

There are some considerations when backing up the publication database, as well as other replicated databases, such as the distribution and subscription databases.  This is covered in Strategies for Backing Up and Restoring Snapshot and Transactional Replication.


Mariadb/MyISAM: Incompatible key or row definition between the MariaDB .frm file and the information in the storage engine. You have to dump an

Try the following:

First check for all running queries and possible Connections your table might use. You can do this by

SHOW PROCESSLIST;

which gives you all running queries/Connections. You can terminate a query/Connection by

KILL <id>;

After that repair your table

REPAIR TABLE myTable;

Try to run this more than once until your Status Returns as ok. After that

OPTIMIZE TABLE myTable:

Then you can use the table again.


Here is a version for SQL Server 2012 using the new LAG feature:

WITH CTE AS
(
    SELECT
        EventDateTime,
        Host,
        Flag,
        LAG(Flag)
            OVER (ORDER BY EventDateTime) AS PrevFlag
    FROM MyTable
)
SELECT EventDateTime, Host, Flag
FROM CTE
WHERE Flag <> PrevFlag;

Here is a version for SQL Server 2008 using a self join:

WITH CTE AS
(
    SELECT
        EventDateTime,
        Host,
        Flag,
        ROW_NUMBER()
            OVER (ORDER BY EventDateTime) AS RowNum
    FROM MyTable
)
SELECT
    C.EventDateTime,
    C.Host,
    C.Flag
FROM CTE AS C
    INNER JOIN CTE AS D
    ON C.RowNum = D.RowNum +1
WHERE C.Flag <> D.Flag;


What is the best practice for storing image varbinary(max) data in a database table?

You might try changing the id generation so inserts are not contending with each other, or consider setting ALLOW_PAGE_LOCKS = OFF, noting the implications for index maintenance (which are probably only relevant if you are also doing updates)


Does the EXIST clause only return distinct rows by its nature?

EXISTS don't actually return any rows.  They check for the existence and then move on.

Your problem is probably a duplicate caused by the JOINs to the second two tables.  Try running this:

SELECT p.FirstName, p.LastName, e.JobTitle, COUNT(1) AS Cnt
FROM Person.Person AS p
JOIN HumanResources.Employee AS e
   ON e.BusinessEntityID = p.BusinessEntityID
JOIN HumanResources.employeeDepartmentHistory AS edh
    ON P.businessentityID = edh.BusinessEntityID
JOIN HumanResources.Department AS d
    ON edh.DepartmentID = d.DepartmentID
WHERE d.name LIKE 'P%'
AND E.BusinessEntityID = EDH.BusinessEntityID
GROUP BY p.FirstName, p.LastName, e.JobTitle
HAVING COUNT(1) > 1

I'm guessing you will get back one row with a count of 2.

The reason you aren't seeing anything in the EXCEPT is because all of the rows do exist in both places.  You just have a duplicate in the second query.

The reason for the duplicate is because either the joins with HumanResources.employeeDepartmentHistory and HumanResources.Department.  Basically the HumanResources.employeeDepartmentHistory table is used to map one employee to two departments.

This is one of the major differences in using a JOIN vs EXISTS for the type of logic you are trying to use.


The tasks are stored inside project folder of MobiLink-project with mlt-extension. They are XML-files. Therefor there is no special export needed. Just copy the file.


Postgresql size of Slave is bigger than Master size

SOLVED. It was not DB problem. du command showed correct size of DB. Problem was in my BackUP system. It helds descriptors of deleted files. Restart of backup service solved problem

lsof | grep '(deleted)'  shows deleted files process ID in second column.


Compound Primary Key - Index both fields?

A query use the index in the column order (UserId first then RoleId).
Without an index on RoleId, it will scan the clustered index.
Unless there is a where clause with a userId, the engine does not know how to get inside the index whitout scanning all the userId.

Because roleId is a FK, best practice suggest to have an index on it. You definetly need it if you (often) query it.

Index on UserId alone is useless. The clustered index already does it since UserId is the entry point. Using it does not require an extra lookup to the PK in order to get data or associated roleId. Just clustered index seek.


What is the difference between PostgreSQL 9.0 Replication and Slony-I?

This article covers it well: http://scanningpages.wordpress.com/2010/10/09/9-0-streaming-replication-vs-slony/


I don't know how to fix ORA-04091 mutating trigger error

ORA-04091

This is Oracle's way of saying:

  you really don't want to do that.

Here is my short-list of workarounds.

Option 1

Use a VIEW.

This is the preferred method.

Option 2

Use a Materialized View.

Yes, you can Fast Refresh SUM() on commit.

Option 3

Use a TAPI (Table API). (a Package)

All DML operations must go through this Package; never against the actual table.

Option 4

3-Trigger solution.  (This could be a single Complex trigger.)

Without proper locking, you could have "lost updates".

Even with proper locking, Dead Locks could occur.


How can I get Sql Server 2016 on a 32-bit server?

You can't, SQL Server 2016 only comes compiled for 64-bit.

  Is there a way to get around it with compatibility levels?

Thankfully, no.

  I need to upgrade it to SQL Server 2016 in order to log ship to a new server, so we can put this thing out of its misery.

You'll need to have someone install an OS supported by SQL Server 2016, then install SQL server 2016.

  Question: how can I migrate the data to a SQL 2016 server with <1 day of downtime?

Install a supported OS for SQL Server 2016 on the old 2008 server. Install SQL Server 2016, then setup log shipping as you normally would per your above quote.


You can do steps 1 and 2: but use the DATEADD/DATEDIFF technique as per this: How to the get current date without the time part

You will most likely be unable to index the computed column because it won't be deterministic with the varchar method


Trigram similarity (pg_trgm) with German umlauts

For starters: A German poet is still a Poet. 'Poet' <> 'Pöt'; 'oe' <> 'ö'. The convention to replace 'ö' with 'oe' is largely out of use nowadays. See:

https://de.wikipedia.org/wiki/Umlaut#Darstellung_von_Umlauten

I have had related problems many times. The additional module unaccent is instrumental to cover spelling variants and typos on both sides. Install it once per database:
CREATE EXTENSION unaccent;

See:

Does PostgreSQL support “accent insensitive” collations?

The tricky part is to get index support with it. Details may be hard for beginners.
Trigram similarity
Then create the wrapper function as instructed here (same link again):

Does PostgreSQL support “accent insensitive” collations?

Create a new index with it:
CREATE INDEX names_trgm_idx ON names USING gin (f_unaccent(name) gin_trgm_ops);

Query demo:
SELECT name
     , similarity(f_unaccent(name), f_unaccent('Schoenstraße')) AS sim_unaccent
     , similarity(name, 'Schoenstraße') AS sim
FROM   names
WHERE  f_unaccent(name) % f_unaccent('Schoenstraße')
ORDER  BY f_unaccent(name) <-> f_unaccent('Schoenstraße')
        , name <-> 'Schoenstraße';  -- best match first

Sorting by name <-> 'Schoenstraße' additionally serves as tiebreaker to get best matches first (after filtering cheaply with the index).
Consider a GiST index instead of GIN for "nearest neighbor"-type queries with LIMIT. See:

Best index for similarity function

Full text search (FTS or just TS)
Consider full text search to just match complete words (after stemming), or with prefix matching / phrase search ... See:

Pattern matching with LIKE, SIMILAR TO or regular expressions in PostgreSQL

The unaccent module provides a dictionary for integration in a TEXT SEARCH CONFIGURATION:
CREATE TEXT SEARCH CONFIGURATION de (COPY = german);
ALTER TEXT SEARCH CONFIGURATION de
ALTER MAPPING FOR hword, hword_part, word WITH unaccent, german_stem;

Index based on it:
CREATE INDEX names_fts_idx ON names USING GIN (to_tsvector('de', name));

Alternatively, since you are dealing with names, you might base it on the simple text search configuration instead of german. No stop words, no stemming. Names don't necessarily follow language-specific rules.
Query demo:
SELECT name
     , ts_lexize('unaccent', name)
     , to_tsvector('de', name)
     , to_tsquery('de', 'Schoenstraße')
     , to_tsvector('de', name) @@ to_tsquery('de', 'Schoenstraße') AS match
     , similarity(name, 'Schoenstraße')
FROM   names
WHERE  to_tsvector('de', name) @@ to_tsquery('de', 'Schoenstraße')
ORDER  BY name <-> 'Schoenstraße';  -- exact matches first

Again, sorting by name <-> 'Schoenstraße' serves as tiebreaker, maybe additionally to other sort criteria.
Note, however, that looking for 'Schoenstraße' with FTS does not find 'Schönstraße', as unaccent() does not change 'oe', and maps 'ö' to 'o'. ('oe' <> 'ö').
Related:

Creating a case-insensitive and accent/diacritics insensitive search on a field

Finding the most commonly used non-stop words in a column

db<>fiddle here  -- looking for 'Schoenstraße'
db<>fiddle here  -- looking for 'Schönstraße'


Psycopg2 Errors on SQL statement when trying to copy data from CSV file into PostgreSQL database

sqlcopy = "COPY irwin (fire_id,name,type,acres,date_time,state,county,admin_unit,land_cat,commander,perc_cntnd,cont_date,gacc,lat,long,geom,updated,imo)  FROM STDIN WITH FORMAT CSV, HEADER")

copy doesn't do values..., the columns in the input must be correctly formatted for the columns you list, also format CSV is usually a better choice than delimiter ','

what copy expert actually does is open a channel to the postgresql copy command. and then squirt the file contents through that channel.
so you need to format the query for what postgres wants.

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


MSSQL - Should I mention column max length?

This is 100% an education problem for your developers and that is where this needs to be addressed.
Not specifying variable column length is a bad idea that will probably break almost immediately for you. Spoiler: it gives severely small lengths to the column.
Similarly, using MAX as the length by default is also a bad idea and will set you up for performance and architectural challenges down the line.
You should stick to using the smallest variable length fields that fit your business requirements and do some sessions with your developers to ensure they understand how SQL Server handles variable length data types, or check their work more closely in pull requests.
In the case where you have already tried this (which I hope is not the case) I have written a script sp_sizeoptimiser that flags occurrences of this problem to help you root out and remove them.


Why query plan changes by number of rows


Does RMAN in Oracle only copy the archive logs? What happens to the active redo log in case of failure?

The first thing RMAN does it automatically perform an ALTER SYSTEM ARCHIVE LOG CURRENT to switch logfiles so that the active log at the point the backup was started will be included.

It doesn't do this in all backup situations, but this answers your question.


join syntax / style performance consideration

In a world where the query optimizer considered all possible join orders, and contained all possible logical transformations, the syntax we use for our queries would not matter at all.

As it is, the optimizer generally uses heuristics to pick an initial join order and explores a number of join order rewrites from there. It does this to avoid excessive compilation time and resource usage. It doesn't take all that many joins for the number of possible combinations to become unreasonable to explore exhaustively.

To take an extreme example, 42 joins are enough to generate more alternatives than there are atoms in the observable universe. More realistically, even 7 tables are enough to produce 665,280 alternatives. Although this is not a mind-boggling number, it would still take very significant time (and memory) to explore those alternatives completely.

Although the heuristics are largely based on the type of join (inner, outer, cross...) and cardinality estimates, the textual order of the query can also have an impact. Sometimes, this is an optimizer limitation - NOT EXISTS clauses are not reordered, and outer join reordering is very limited. Even with simple inner joins, the interaction between textual order, initial join order heuristics, and optimizer internals can be difficult to predict with certainty.

To take an example using the AdventureWorks sample database, I can write a query using the a common syntax form as:

SELECT
    P.Name,
    PS.Name,
    SUM(TH.Quantity),
    SUM(INV.Quantity)
FROM Production.Product AS P
JOIN Production.ProductSubcategory AS PS
    ON PS.ProductSubcategoryID = P.ProductSubcategoryID
JOIN Production.TransactionHistory AS TH
    ON TH.ProductID = P.ProductID
JOIN Production.ProductInventory AS INV
    ON INV.ProductID = P.ProductID
GROUP BY
    P.ProductID,
    P.Name,
    PS.ProductSubcategoryID,
    PS.Name;

Before cost-based optimization, the logical query tree looks like this (note the join order is not the same as the written order):

I can (carefully) rewrite the query to use 'nested' syntax:

SELECT
    P.Name,
    PS.Name,
    SUM(TH.Quantity),
    SUM(INV.Quantity)
FROM Production.ProductSubcategory AS PS
JOIN Production.Product AS P
JOIN Production.TransactionHistory AS TH
JOIN Production.ProductInventory AS INV
    ON INV.ProductID = TH.ProductID
    ON TH.ProductID = P.ProductID
    ON P.ProductSubcategoryID = PS.ProductSubcategoryID
GROUP BY
    P.ProductID,
    P.Name,
    PS.ProductSubcategoryID,
    PS.Name;

In which case the logical tree at the same point is:

The two different syntaxes produce a different initial join order in this case. After cost-based optimization, both produce the same output plan shape:

There are detailed differences between the two plans, with the 'nested' syntax producing a plan with a somewhat lower estimated cost:

The two inputs took a slightly different path through the optimizer, so it isn't all that surprising there are slight differences.

In general, using different syntax will sometimes (definitely not always!) produce different plan results. There is no broad correlation between one syntax and better plans. Most people write and maintain queries using something like the non-nested join syntax, so it often makes practical sense to use that.

To summarize, my advice is to write queries using whichever syntax seems most natural (and maintainable!) to you and your peers. If you get a better plan for a specific query using a particular syntax, by all means use it - but be sure to test that you still get the better plan whenever you patch or upgrade SQL Server :)


How is an Index Seek satisfying this query?

The AK_ContactType_Name index is a unique nonclustered index on the Name field:

CREATE UNIQUE NONCLUSTERED INDEX [AK_ContactType_Name] ON [Person].[ContactType]
(
    [Name] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

There are two indexes on this table by default - the primary clustered key, plus the above one.


Does Log-Shipping need Transaction Log Backup from Primary Server?

If you don't include the transaction log, then it's not really log shipping, is it?

Your full backup is restored to that point in time, and that's where it will stay until you either (a) apply a log or (b) apply a differential or another full backup. Yes, the restore does also bring over the transaction log, but only the state of the transaction log at that same point in time. I guess technically you could argue that you shipped the log once, but the point of log shipping is to keep the secondary server up to date more often than the full backups (which are typically very large in comparison to periodic log backups).

So if your intention is to do this backup / restore once a day, and you don't need to keep the copy up to date throughout the day, just do a simple backup and restore, and stop calling it log shipping.

If you want the copy to stay relatively up to date throughout the day, then yes, it will be mandatory to take periodic log backups on the primary server and restore them on the secondary server. Note that you have to weigh what's more important: keeping a copy of the data up to date, or letting users query stale data uninterrupted. If you restore a log to the secondary server every 15 minutes, you have to kick all the read-only users out every 15 minutes, because you need exclusive access to the database in order to restore the log.

You can read more information about log shipping here:

http://msdn.microsoft.com/en-us/library/ms187103(v=sql.90).aspx


If there's no duplicate data to worry about, adding another 50 GB of data to a 250 GB database is not going to matter much, especially since the business requirements need the archived data in the live database.

Make sure you have good covering indexes. Perhaps you can import the historic data into a separate filegroup and then mark it as read-only. Perhaps you could use a table partition.

There are lots of options to think of. @AMG's answer covers some more business-related questions for your ongoing SLA as well.


Grant permissions on a stored procedure to tables in another schema

No, you do not want (or need) WITH EXECUTE AS OWNER.

The problem is simply that your two schemas — Schema1 wit the stored procedure, and Schema2 with the tables — have different owners. If you execute the following query, you will see who owns which schemas:

SELECT *, USER_NAME([principal_id]) AS [UserName]
FROM   sys.schemas;

Ownership chaining, the default security mechanism, assumes DML and EXECUTE permissions on objects referenced by the object being accessed, IF the owner is the same. If no owner has been explicitly assigned to an object (default = NULL), then the "owner" is considered to be the owner of the schema in which the object exists.

So, technically your schemas could have the same owner and it's just that the tables have a different owner due to being given one explicitly (via ALTER AUTHORIZATION), but it's much more likely that the schemas have different owners.

Fortunately, this situation is easy to solve using Module Signing.

The concept is to create a certificate in the database containing these schemas and objects, which will then be used to sign the stored procedure that exists in Schema1 but selects from tables in Schema2.

A user is created from the certificate, and is then assigned whatever permission(s) are needed in order for the stored procedure to complete successfully.

No permissions are being granted to anyone that will be executing the stored procedure. The permissions are granted only to the certificate-based user. The connection between the stored procedure and the certificate-based user is (effectively) the public key of the certificate. And that public key is only loaded into the session's security context when a module that has been signed with that certificate is being executed. And that signature requires the certificate's password (in the ADD SIGNATURE statement), and is dropped if anyone changes even a single byte of the stored procedure (or the owner of it). Hence, very secure.

The following illustrates this concept:

Main Setup

CREATE SCHEMA [Schema1];
GO
CREATE SCHEMA [Schema2];
GO

CREATE USER [TestUser] WITHOUT LOGIN;

CREATE TABLE [Schema2].[Stuff]
(
  [StuffID] INT NOT NULL IDENTITY(1, 1)
    CONSTRAINT [PK_Stuff] PRIMARY KEY,
  [Stuff] NVARCHAR(50) NOT NULL
);

INSERT INTO [Schema2].[Stuff] ([Stuff]) VALUES (N'Success!!');

GO
CREATE PROCEDURE [Schema1].[GetStuff]
AS
SET NOCOUNT ON;

SELECT [StuffID], [Stuff]
FROM   [Schema2].[Stuff];
GO

GRANT EXECUTE ON [Schema1].[GetStuff] TO [TestUser];

Test Same Schema Owners

EXECUTE AS USER = N'TestUser';
SELECT SUSER_NAME() AS [Login], USER_NAME() AS [User];

EXEC [Schema1].[GetStuff];
-- Success due to both Schemas having same owner

REVERT;
SELECT SUSER_NAME() AS [Login], USER_NAME() AS [User];

Reproduce Current Situation

SELECT *, USER_NAME([principal_id]) AS [UserName]
FROM   sys.schemas
WHERE  [name] IN (N'Schema1', N'Schema2');
/*
name       schema_id    principal_id    UserName
Schema1    5            1               dbo
Schema2    6            1               dbo
*/

ALTER AUTHORIZATION ON SCHEMA::[Schema2] TO [guest];

SELECT *, USER_NAME([principal_id]) AS [UserName]
FROM   sys.schemas
WHERE  [name] IN (N'Schema1', N'Schema2');
/*
name       schema_id    principal_id    UserName
Schema1    5            1               dbo
Schema2    6            2               guest
*/

Test Current Situation (no extra permissions)

EXECUTE AS USER = N'TestUser';
SELECT SUSER_NAME() AS [Login], USER_NAME() AS [User];

EXEC [Schema1].[GetStuff];
-- ERROR:
/*
Msg 229, Level 14, State 5, Procedure Schema1.GetStuff, Line XXXXX [Batch Start Line YYYYY]
The SELECT permission was denied on the object 'Stuff', database '......', schema 'Schema2'.
*/

REVERT;
SELECT SUSER_NAME() AS [Login], USER_NAME() AS [User];

Module Signing Setup

-- Create Certificate and sign Stored Procedure with it:
CREATE CERTIFICATE [Permission$Schema2]
    ENCRYPTION BY PASSWORD = 'SomePassword'
    WITH SUBJECT = 'Used for selecting from objects in Schema2',
    EXPIRY_DATE = '2099-12-31';

ADD SIGNATURE
    TO [Schema1].[GetStuff]
    BY CERTIFICATE [Permission$Schema2]
    WITH PASSWORD = 'SomePassword';

-- Create Certificate-based User to hold permissions
CREATE USER [Permission$Schema2]
  FROM CERTIFICATE [Permission$Schema2];

-- Grant Certificate-based User ONLY the permissions needed
GRANT SELECT ON SCHEMA::[Schema2] TO [Permission$Schema2];

Test with Module Signing

EXECUTE AS USER = N'TestUser';
SELECT SUSER_NAME() AS [Login], USER_NAME() AS [User];

EXEC [Schema1].[GetStuff];
-- 1    Success!!!

REVERT;
SELECT SUSER_NAME() AS [Login], USER_NAME() AS [User];


Stored Procedure which accept xml file as input

You can use X-Query for that:

SQL Fiddle

MS SQL Server 2008 Schema Setup:

CREATE PROCEDURE dbo.ParseXML
   @XML XML
AS
BEGIN

  SELECT
    Book.value('@id','NVARCHAR(100)') AS id,
    Book.value('author[1]','NVARCHAR(100)') AS author,
    Book.value('title[1]','NVARCHAR(100)') AS title,
    Book.value('price[1]','DECIMAL(10,4)') AS price,
    Book.value('publish_date[1]','DATE') AS publish_date

    FROM @XML.nodes('catalog/book')Catalog(Book)

RETURN;
END;

Query 1:

DECLARE @FileXML XML = '<?xml version="1.0"?>
<catalog>
   <book id="bk101">
      <author>Gambardella, Matthew</author>
      <title>XML Developer''s Guide</title>
      <genre>Computer</genre>
      <price>44.95</price>
      <publish_date>2000-10-01</publish_date>
      <description>An in-depth look at creating applications
      with XML.</description>
   </book>
   <book id="bk102">
      <author>Ralls, Kim</author>
      <title>Midnight Rain</title>
      <genre>Fantasy</genre>
      <price>5.95</price>
      <publish_date>2000-12-16</publish_date>
      <description>A former architect battles corporate zombies,
      an evil sorceress, and her own childhood to become queen
      of the world.</description>
   </book>
</catalog>';

EXEC dbo.ParseXML @FileXML;

Results:

|    ID |               AUTHOR |                 TITLE | PRICE | PUBLISH_DATE |
|-------|----------------------|-----------------------|-------|--------------|
| bk101 | Gambardella, Matthew | XML Developer's Guide | 44.95 |   2000-10-01 |
| bk102 |           Ralls, Kim |         Midnight Rain |  5.95 |   2000-12-16 |

This shows you how to get to the values. From there you can easily insert them into your table.


Can I permanently lock a MySQL table to prevent inserts

I would use a before insert trigger:

CREATE TRIGGER trigger_name
BEFORE INSERT
   ON table_name FOR EACH ROW
BEGIN
    SIGNAL SQLSTATE VALUE '99999'
      SET MESSAGE_TEXT = 'You cannot insert rows to this table.';
END;


How to check/know the highest run queries

As someone said in the comments, pg_stat_statements is the way to get the statistics. Put this into your postgresql.conf:

shared_preload_libraries = 'pg_stat_statements'

pg_stat_statements.max = 10000
pg_stat_statements.track = all

Then run this query:

CREATE EXTENSION pg_stat_statements;

After that, this sample query (copied from the docs linked above) will give you the stats for 5 top queries from all the databases:

SELECT query, calls, total_time, rows,
100.0 * shared_blks_hit / nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent
FROM pg_stat_statements ORDER BY total_time DESC LIMIT 5;

If you want results for a single database, you need to filter by dbid which you can get from pg_database by db name. Add this WHERE clause to the query above:

WHERE dbid = (select oid from pg_database where datname = 'YOUR_DB_NAME')

You could also do a join.

When you're testing this it may be a good idea to exclude the queries to the stats/schema tables themselves, for example:

AND query not similar to '%( pg_|information_schema)%'

There's a bunch of free and commercial tools that can help you visualize the data.


Clobbered default database in postgreSQL

Your question is very unclear (see comment) but I'm going to make a guess at it anyway.

I think you DROPped the postgres database. You're used to always connecting to PostgreSQL as the postgres superuser using the postgres database, probably via sudo and peer authentication with something like:

sudo -u postgres psql

where you don't specify a database name to connect to. So with no postgres database, you don't know how to connect anymore. You get an error like:

psql: FATAL:  database "postgres" does not exist

If so, you just need to connect to a different database and re-create the postgres database. It only exists as a convenient scratch area - the system doesn't actually care if the postgres database exists or not, and doesn't use it for anything.

Something like:

sudo -u postgres psql template1 -c 'CREATE DATABASE postgres;'

will do the trick.

If you dropped the template1 database as well, you could still recover by connecting to any other database on the server and re-creating template1 from the locked, protected template0 database; see the documentation on template databases.

Key things to understand:

You don't have to connect to the postgres database. It doesn't get used for anything except a default place to connect to when using the postgres user. If you DROP it the system doesn't care.
psql connects by default to the database with the same name as the current user. You can just pick another one with psql databasename.
psql connects by default with the username of the current unix user. You can just pick another one with (eg) psql -U someuser. This will work fine if pg_hba.conf is configured to permit the connection.
While the postgres user is the default superuser, you can create another one, and you don't have to rely on peer authentication to use it either; you can use md5 password auth or whatever you want. So while you're probably used to just using sudo -u postgres psql you can actually set things up in a variety of flexible ways. I frequently create a database superuser for my regular unix user account (CREATE USER myuser WITH SUPERUSER ENCRYPTED PASSWORD 'blah';) when I'm on a scratch/development box, so I'm not messing about with sudo all the time.


My goal is to execute a command that requires the sysadmin role (DBCC TRACEON(1224))

You are punching a hole in your security by allowing an unprivileged user run as sysadmin role.

If you are trying to set 1224 traceflag, which disables lock escalation based on the number of locks, you can do it on table level using ALTER TABLE

e.g. Below enables lock escalation to the partition level on a partitioned table. If the table is not partitioned, lock escalation is set at the TABLE level.

ALTER TABLE dbo.T1 SET (LOCK_ESCALATION = AUTO); -- valid options are AUTO, TABLE and DISABLE

Now you can just give an unpreviledge user alter table rights.

HTH


Oracle Select with xmlquery() error - How to ignore errors?

I don't think you can directly. I don't know of a way of putting the XML parser into a sort of "lax" mode where it would ignore validation errors, and you can't catch exceptions inside plain SQL.

One approach to deal with this (except harassing your outside source so that they produce valid XML, which is the solution) would be to validate the XML as you store it, and only process data that has been validated. You can do that simple PL/SQL code. Here's a minimal example of one way to approach this.

Sample table and data:

create table foo(id int, xml clob, valid int default null);
insert into foo(id,xml) values (1, '<?xml version="1.0"?><root></root>');
insert into foo(id,xml) values (2, '<?xml version="1.0"?><root><a foo="<"></root>');
commit;

Validation function:

create function is_valid_xml(xml_param clob)
return int
as
  scratch xmltype;
begin
  select xmltype(xml_param) into scratch from dual;
  return 1;
exception
  when others then
    return 0;
end;
/

Applying the validation function

SQL> update foo set valid = is_valid_xml(xml) where valid is null;
2 rows updated.

SQL> select id, valid from foo;
ID | VALID
----------
 1 |     1
 2 |     0

You can then query/report based your usual criteria plus the valid column.


I want to update mysql vertion 5.5.59 to 5.7.21

First, never try to uninstall or purge your mysql packages. Your db be drop from your system for ever…

Secondly, you should try to use apt-cache policy mysql* to check which versions are available on your system.

Thirdly, you should do a backup of ALL you DB.

If it is a simple update, you should try sudo apt-get upgrade mysql-client mysql-server mysql-common.

Or more aggressive sudo apt-get dist-upgrade mysql-client mysql-server mysql-common

If not, use the version available for update from apt-cache policy mysql* output.

And, from what I can see, your source.list seems not to contain any repo that contains mysql-server-core-5.7. So use apt-cache policy mysql*.


Answering my own question, I found this link, which contains the answer:

  ...by having this as a function on your update, you are essentially
  calling the function as many number of times as there are rows in the
  table. This is not the best way to do this, and is known as row-by-row
  processing, rather than set-based.

  Try and take the processing that the function is doing and apply it to
  the table in one go rather than this one-at-a-time approach.


How to find the current Log Sequence Number (LSN) of a database that is in Restoring state?

If you restore the log backup with "HeaderOnly", you will have the first and last LSN include in this backup.

You want to find the log backup that includes the LSN mentioned in your error message (where the DB is at).

Now if you want to find this LSN, you will have to query the sys.master_files DMV and look at the redo_start_lsn column.

Select redo_start_lsn from sys.master_files where database_id=DB_ID('yourDB') and type_desc = 'LOG'


you could add an empty field at the end of each line, and remove the last column of your table.

Otherwise check for all the line terminations (\r\n vs. \n), or the presence of \n in your contents...

It should work at the end, good luck !


Oracle PL/SQL keyword AUTONOMOUS_TRANSACTION will cause a procedure to create another session, do a transaction, commit/rollback just that private transaction, and return flow control back to the parent.

Oh..never send email on uncommitted data.

EDIT: (due to edit of original post)

  Roughly the same problem arises if you want to import files from a
  directory. If you delete the file inside the transaction, then the
  transaction might fail and the file was deleted but never imported. Or
  you delete the file after the transaction. Then the delete of the file
  might fail and the file gets imported a second time.

This type of problem is called a bug.

The solution is:

Define each step as its own TRANSACTION

You'll want to create them in a way that you can rerun (or skip) the step(s) as needed

Run each step in the appropriate order.

do not send email prior to COMMIT.
do not delete a file prior to successful loading of the data

You'll need to keep track of "where you are at" and if that step has passed/failed.

EMAIL example

You should have a procedure to sendEmail that should be called after commit.

If you want to call the procedure prior to commit, you'll need to add a row to a queue that will rollback with the main transaction.  For Oracle, this will be either Advance Queuing or the package APEX_MAIL

By putting it in a separate procedure, you can sendEmail a 2nd time upon [end-user's] request.

Process File

You have an algorithm that contains a few steps where each step can fail.  this is actually different than your sendEmail problem.

You need to record what you are processing, where you are at within your algorithm, and if that step have succeeded or failed.

In order to recover from an error at any step, each step of the process needs to be defined as a discrete TRANSACTION.

In Oracle, I would have these procedures (1 procedure per TRANSACTION):

create or replace
package file_processing_package
as
  procedure update_file_processing_status(
                                p_id       IN files_to_process.id%TYPE
                              , p_status   IN process_states.id%TYPE);

  function add_a_file_to_be_processed( p_filename IN files_to_process.file_name%TYPE )
                               return files_to_process.id%TYPE;

  procedure load_data_from_file( p_id in files_to_process.id%TYPE );

  procedure process_already_loaded_data( p_id in files_to_process.id%TYPE );

  procedure delete_file_from_os( p_id in files_to_process.id%TYPE );
end;
/

This is based on the following tables:

CREATE TABLE PROCESS_STATES (
  id   int generate by default on null as identity, -- 12c+
  state_desc  varchar2(25) not null,
  constraint process_states_pk primary key (id),
  constraint process_states_uq1 unique (state_desc)
);

insert into process_states( state_desc ) values ( 'file to be processed' );
insert into process_states( state_desc ) values ( 'file loaded' );
insert into process_states( state_desc ) values ( 'processing' );
insert into process_states( state_desc ) values ( 'processing failed' );
insert into process_states( state_desc ) values ( 'processing succeeded' );
insert into process_states( state_desc ) values ( 'delete failed' );
insert into process_states( state_desc ) values ( 'OK' ); -- delete succeeded
commit;

CREATE TABLE FILES_TO_PROCESS (
  id               int generate by default on null as identity, -- 12c+
  file_name        varchar2(50) not null,
  process_state_id int not null,
  constraint file_to_process_pk  primary key (id),
  constraint file_to_process_uq1 unique (file_name),
  constraint file_to_process_fk1 foreign key (process_state_id)
                            references (process_states.id)
);

The UNIQUE constraint on FILE_NAME prevents the same file being processed twice.


SQL Server tail log backup fails where it shouldn't

Paul Randal did a good piece on backing up the tail of the log that includes just this case.  In it he says that the method to use when there is no data file is using NO_TRUNCATE.  Based on that your command should be:

BACKUP LOG [AdventureWorks2012] TO
DISK = N'C:\sqldata\MSSQL11.MSSQLSERVER\MSSQL\Backup\AdventureWorks2012.bak'
WITH NAME = N'AW Tail Log Backup', NO_TRUNCATE

BOL does say

  To perform a best-effort log backup that skips log truncation and then take the database into the RESTORING state atomically, use the NO_TRUNCATE and NORECOVERY options together.

So you could try using both of them together but I'm not sure if NORECOVERY can be used if the data file is missing or not, even with NO_RECOVERY.

BACKUP LOG [AdventureWorks2012] TO
DISK = N'C:\sqldata\MSSQL11.MSSQLSERVER\MSSQL\Backup\AdventureWorks2012.bak'
WITH NAME = N'AW Tail Log Backup', NO_TRUNCATE, NORECOVERY

It's certainly worth testing anyway.


can I give permission to insert into view, but not to referred table?

Yes you can. Per documentation:

  Note that the user performing the insert, update or delete on the view
  must have the corresponding insert, update or delete privilege on the
  view. In addition the view's owner must have the relevant privileges
  on the underlying base relations, but the user performing the update
  does not need any permissions on the underlying base relations.


It's as @RDFozz identified in their comment.

(DT_STR,20,1252)((IPUNSPSC) =="no_UNSPSC_available" ? NULL(DT_WSTR,20) : TRIM(IPUNSPSC))

The ternary operator ? : doesn't behave as one might expect. You casted the two resulting values as DT_STR but that's not where SSIS picks up the metadata. Instead, you need to cast the entire result of the ternary operator to a given type.

The following Biml generates a sample SSIS package with an OLE DB Query to present the IPUNSPSC column and then a Derived Column component to manipulate the data as expected.

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Connections>
        <OleDbConnection Name="SRC" ConnectionString="Provider=SQLNCLI11;Server=localhost\dev2016;Initial Catalog=tempdb;Integrated Security=SSPI;" />
    </Connections>
    <Packages>
        <Package Name="dba_178090">
            <Tasks>
                <Dataflow Name="DFT Demo">
                    <Transformations>
                        <OleDbSource Name="OLESRC Query" ConnectionName="SRC">
                            <DirectInput>SELECT '**no_UNSPSC_available**' AS IPUNSPSC UNION ALL  SELECT '***********************';</DirectInput>
                        </OleDbSource>
                        <DerivedColumns Name="DER Demo">
                            <Columns>
                                <Column Name="Derived" DataType="AnsiString" Length="20" CodePage="1252">(DT_STR,20,1252)((IPUNSPSC) =="no_UNSPSC_available" ? NULL(DT_WSTR,20) : TRIM(IPUNSPSC))</Column>
                            </Columns>
                        </DerivedColumns>
                        <!-- Placeholder transformation -->
                        <DerivedColumns Name="DER Bit Bucket" />
                    </Transformations>
                </Dataflow>
            </Tasks>
        </Package>
    </Packages>
</Biml>


MongoDB: Can I have a delayed secondary that doesn't vote?

A configuration with three voting nodes in different data centres plus an additional non-voting delayed secondary is definitely supported. This is actually a reasonable configuration in terms of satisying core availability requirements using the distributed voting nodes with additional hidden/non-voting nodes for special use cases. A delayed secondary should be always configured as hidden and priority 0, and may have 0 or 1 vote.

However, there is one important caveat with your deployment: you have three data-bearing nodes but one of those is delayed. This will be problematic if you want to use any write concern higher than the usual default (w:1 aka acknowledgement by the primary). In the event one of your non-delayed secondaries is unavailable, a higher write concern (eg w:2 or w:majority) could still be satisfied but the writes would have to wait for acknowledgement from a second data-bearing node which would have to be the delayed secondary.

A more robust configuration would be to replace the arbiter with a data-bearing secondary so that unavailability of any of the three voting nodes would still allow for acknowledgement of majority writes.


At one point I answered this question over at StackOverflow, but it seems like it would be useful to have that information at DBA.SE as well, revised and updated.

Just to be totally explicit: TSQL does not (by itself) have the ability to launch other TSQL operations asynchronously.

That doesn't mean you don't still have a lot of options (some of them mentioned in other answers):

SQL Agent jobs: Create multiple SQL jobs, and either schedule them to run at the time desired, or start them asynchronously from a "master control" stored proc using sp_start_job. If you need to monitor their progress programatically, just make sure the jobs each update a custom JOB_PROGRESS table (or you can check to see if they have finished yet using the undocumented function xp_sqlagent_enum_jobs as described in this excellent article by Gregory A. Larsen).  You have to create as many separate jobs as you want parallel processes running, even if they are running the same stored proc with different parameters.
SSIS Package: Create an SSIS package with a simple branching task flow. SSIS will launch those tasks in individual spids, which SQL will execute in parallel.
Custom application: Write a simple custom app in the language of your choice (C#, Powershell, etc), using the asynchronous methods provided by that language. Call a SQL stored proc on each application thread.
OLE Automation: In SQL, use sp_oacreate and sp_oamethod to launch a new process calling each other stored proc as described in this article, also by Gregory A. Larsen.
Service Broker: Look into using Service Broker, a good example of asynchronous execution in this article.
CLR Parallel Execution: Use the CLR commands Parallel_AddSql and Parallel_Execute as described in this article by Alan Kaplan (SQL2005+ only).
Scheduled Windows Tasks: Listed for completeness, but I'm not a fan of this option.

If it were me, I'd probably use multiple SQL Agent Jobs in simpler scenarios, and an SSIS package in more complex scenarios.

In your case, unless you're trying to launch 200 separate threads, multiple scheduled Agent jobs sounds like a simple and manageable choice.

One final comment: SQL already attempts to parallelize individual operations whenever it can*. This means that running 2 tasks at the same time instead of after each other is no guarantee that it will finish sooner. Test carefully to see whether it actually improves anything or not.

We had a developer that created a DTS package to run 8 tasks at the same time. Unfortunately, it was only a 4-CPU server :)

*Assuming default settings. This can be modified by altering the server's Maximum Degree of Parallelism or Affinity Mask, or by using the MAXDOP query hint.


How to create date ranges of events coming from an API where open state represents the date range and close state the gaps between the ranges?

I figured it out how to insert/update this kind of data into PostgreSQL date ranges from an algorithmic perspective
that this may be helpful to you too.

As this data is a time series, ultimately when we know the full dataset, there can't be any overlaps between the open,
close states of a single event over time.

Keeping the algorithm flexible enough, for when we don't know the full range of events and open/close states there are a
couple of rules that needs to be taken into consideration:

If there's a validity column containing the event's date and:

if the event state is open then:

1.1. if the containing validity has a negative infinity = update the lower bound of the containing validity to the event's date.

1.2. if there is another validity row strictly before the containing validity, meaning that the containing validity lower bound equals to another validity upper bound = update the lower bound of the containing validity to the event's date.

1.3. if the above points are false = split the containing validity into two separate rows:

1.3.1. update the containing validity lower bound to the event's date.
1.3.2. insert a new row where the lower bound is the containing validity's original lower bound and the upper bound is the event's date.

if the event state is close then (reverse of the open state rules):

2.1. if the containing validity has a positive infinity = update the upper bound of the containing validity to the event's date.

2.2. if there is another validity row strictly after the containing validity, meaning that the containing validity upper bound equals to another validity lower bound = update the upper bound of the containing validity to the event's date.

2.3. if the above points are false = split the containing validity into two separate rows:

2.3.1.  update the containing validity upper bound to the event's date.

2.3.2. insert a new row where the upper bound is the containing validity's original upper bound and the lower is the event's date.

If there's no validity range containing the event's date then:

if the event state is open then insert a new validity row with the lower bound set to the event's date and the upper bound set to positive infinity.
if the event state is close then insert a new validity row with the upper bound set to the event's date and the lower bound set to negative infinity.

These rules gives you the best possible picture of the event states where the open state are captured in the validity
columns itself and the close state is the gaps between the validity rows.

Once there are more and more data coming in, either from historical data or current ones, the validity columns will
give you a clearer picture of the event states over time.


Execute stored procedure remotely using linked server

Assuming the procedure lives on the remote server: have you tried using the EXECUTE AT command?

DECLARE @RunStoredProcSQL VARCHAR(1000);
SET @RunStoredProcSQL = 'EXEC [Database].[dbo].[StoredProcName]';
--SELECT @RunStoredProcSQL --Debug
EXEC (@RunStoredProcSQL) AT [LinkedServerName];
Print 'Procedure Executed';

That's what I have used successfully in the past.  I usually surround the declare/exec in a try catch just in case, but it's currently working on my systems every hour.


How are joins Commutative and Associative?

You've made an incorrect assumption.

If three's a relationship between A and B, and between B and C but no relationship between A and C, joining A and C doesn't give you no rows. It actually gives you m * n rows, where m is the number of rows in A, and n is the number of rows in B.

A JOIN B on (a.id = b.A_id)
  JOIN C on (b.id = c.B_id)

becomes, if we join A and C first:

A JOIN C on (1 = 1)
  JOIN B on (a.id = b.A_id AND b.id = c.B_id)

NOTE: College was over 30 years ago - hopefully you understand conceptually, and can rewrite into the correct syntax for actual relational algebra.

If you have 20 rows in A, 30 in B, and 40 in C, and every row in B matches one and only one row in A, and every row in C matches one and only one row in B. When you Join A to B, you'll get 30 rows back; when you join those 30 rows to C, you'll get 40 rows back.

If you first join A to C, with no relationship to define which pairs of rows are valid, you'll get back every possible pair of rows - 800 rows. When we join B to the 800 rows, the connection between B and A means that a row in B can only match the AC rows that include one specific A row - there'll be 40 of those (one for each C row). Out of those 40 the only actual matches are the ones where the connection between B and C is also valid. And, we know that the C rows that match the current B row cannot match any other B row. So, for one B row we might have 2 AC rows that match, for another 4, for yet another just 1.

That said, we know every C row does match one (and only one) B row, so the total number of matches will come out to 40 again - the same 40 matches as if we had first matches A and B, or B and C.

So - the condition on a join doesn't tell you which rows do match - it really tells you which rows don't match. Take it away, and you've got a CROSS JOIN - the cross product of the rows in the two tables.


If you're using Linux/Unix, there's a way of making SQL*Plus loads friendlier on the command line - use rlwrap. It adds readline support to SQLPlus, allowing you to use the cursor keys to navigate to previous queries (it retains a history), and edit them when you've made a mistake.

Launch SQL*Plus with rlwrap -i sqlplus or add  alias sqlplus="rlwrap -i sqlplus" to your shell dot file (.bashrc/.cshrc/etc).

To install rlwrap on RHEL 5.0 use the following commands:

wget http://download.fedoraproject.org/pub/epel/5/i386/epel-release-5-4.noarch.rpm
rpm -Uvh epel-release-5-4.noarch.rpm
yum install rlwrap
rlwrap –i sqlplus


How to enforce the structural constraints of rectangularly arrayed data?

All current RDBMS' tables can have CONSTRAINTS on columns. These constraints are checked every time data is inserted into the table. It can also check data against other tables.

We know that each Plate Type has certain number of Rows and Columns. We can enumerate all Rows and Columns for each Plate Type. So, when data is inserted, the DB can check if a certain row/column combination exists for a given Plate Type.

Lets create a set of tables:

create table Plate_Types (
Plate_Type_id int,
Plate_Size int,
Plate_row int,
Plate_col int)

This table holds description of every Plate size like this:

Id     Size   Row     Col
1       6       1       1    -- 2x3
1       6       1       2
...
1       6       2       3
5       1536    1       1    -- 32x48
...
5       1536    32      32

Then, in your main table from Alternative 1 we introduce a Foreign Key - a "link" to another table to check if row and column are valid for this Plate size.

create table MyTable (
well_id int,
plate_id int,
plate_size int,
row_id int,
col_id int,
value real);

ALTER TABLE MyTable
ADD CONSTRAINT FK_Plate_SizeCheck
   FOREIGN KEY (Plate_size_id, Row_id, Column_id)
    REFERENCES Plate_Types (Plate_type_id, Plate_row, Plate_col);

This Constraint here does the following: for every inserted row DB goes to table Plate_Types and looks for combination of Plate_size_id, Plate_row and Plate_col. In other words, it checks if this Plate Size can have row I and column J. If there is no match, then the DB fires an error.

Please note that this is one of several possible solutions for data integrity enforcement for your example. Medical data often comes in huge volumes and performance of this particular design is different question.

PS. This is a shortened explanation for non-developers. Code as well as table design is for concept illustration only.


"Login failed for user..." with cross-instance access on same server in SSIS package

Check your Sever Authentication mode:

Should be "SQL Server and Windows Authentication mode"


mysql greatest n per group: latest message content and time for each conversation

SELECT t.*,
       m4.count,
       m4.time,
       m4.content
FROM topics t
LEFT JOIN ( SELECT m1.topic_id,
                   m3.count,
                   m3.time,
                   m1.content
            FROM messages m1
            INNER JOIN ( SELECT m2.topic_id,
                                COUNT(m2.topic_id) count,
                                MAX(m2.time) time
                         FROM messages m2
                         GROUP BY m2.topic_id ) m3 ON m1.topic_id = m3.topic_id
                                                  AND m1.time = m3.time ) m4 ON t.id = m4.topic_id

I assume that messages (topic_id, time) is unique.


PostgreSQL Partitioning - Constraint exclusion not working

On closer inspection... PostgreSQL is actually functioning as expected:

my_table_master is scanned because there is no constraint on it. The master table can actually have rows, there's nothing preventing it in PostgreSQL inheritance.
my_table_3 is scanned because its check constraint (t='2013-07-01 01:00:00') doesn't have anything to do (in principle) with the condition in the WHERE clause. Even if the definition of the function my_extract makes my_extract(t) = 0 incompatible with t='2013-07-01 01:00:00', the database is clearly not "intelligent enough" to infer it. You could add, if appropriate, even if redundant, NOT (my_extract(t) = 0) in your where clause. That would prevent my_table_3 from being scanned.
my_table_2 is scanned as expected.
my_table_4 is not actually scanned.

So, the constraints prevent one table from being scanned out of the 4.

Alternate query, adding the (my_extract(t) = 0) clause:

EXPLAIN
    SELECT *
    FROM my_table_master
    WHERE t='2013-07-01 01:00:00'
          AND NOT (my_extract(t) = 0)  /* This is the new addition */ ;

returns

Append  (cost=0.00..309.45 rows=7 width=44)
  ->  Seq Scan on my_table_master  (cost=0.00..0.00 rows=1 width=44)
        Filter: ((t = '2013-07-01 01:00:00'::timestamp without time zone) AND (my_extract(t) <> 0))
  ->  Seq Scan on my_table_2  (cost=0.00..309.45 rows=6 width=44)
        Filter: ((t = '2013-07-01 01:00:00'::timestamp without time zone) AND (my_extract(t) <> 0))

By adding the extra condition, the scan of my_table_3 is prevented.

NOTE: To make the trials, I've made up my_extract like this:

CREATE FUNCTION my_extract(_t timestamp without time zone)
RETURNS integer AS
$$
begin
    return extract(day from _t)::integer ;
end
$$
LANGUAGE plpgsql IMMUTABLE /* attention! */ ;

I've used plpgsql to ensure it is not inlined (even if it could be trivially done). Notice that it is necessary that the function be labeled as IMMUTABLE (that is, its result only depends on its parameters), or it won't work as expected.

ADDENDUM

EXPLAIN
    SELECT * FROM my_table_master
    WHERE my_extract(t) = 0 ;

  Append  (cost=0.00..613.25 rows=13 width=44)
    ->  Seq Scan on my_table_master  (cost=0.00..0.00 rows=1 width=44)
          Filter: (my_extract(t) = 0)
    ->  Seq Scan on my_table_2  (cost=0.00..306.62 rows=6 width=44)
          Filter: (my_extract(t) = 0)
    ->  Seq Scan on my_table_3  (cost=0.00..306.62 rows=6 width=44)
          Filter: (my_extract(t) = 0)

Works as well as expected:

my_table_master -> as before
my_table_2 -> is scanned because its constraint doesn't have any immediate relation to
my_table_3 -> is scanned because its constraint actually match the WHERE clause.

What PostgreSQL could do (and it doesn't) is eliminate the FILTER my_extract(t)=0 when scanning table 3, because the condition is already known to be true. Let's hope this optimization is implemented at some point in the future.


Ports to use for mongodb sharding + replication

You can use any ports you like. Particularly on a toy system, it makes no practical difference what number port you choose; it works just as well with port 29589 as port 27017.

Some things to keep in mind:

on a single machine, two different processes must listen on different ports. So no, you can't run two shard servers on the same port.
you need to administer all these processes; if you pick a system where different sets of ports are used for different purposes, that will make it easier to keep track. For example, you might choose to run configsvr processes on ports 21001-21003, shard mongod processes on ports 22001-22999, and mongos processes on ports 23001 upwards.
the default port is 27017, and that is public knowledge; so if your system is open to the internet, be aware that malicious ransomers using port scanners will be looking for an exposed mongod system on port 27017 they can access and exploit. So you might prefer to avoid using that port, to add an extra layer to your security.


This query is choking the server. How can I improve it?

First add the following indices:

ALTER TABLE pet
  ADD INDEX catid_date_index
      (catid, date) ;

ALTER TABLE petfb
  ADD INDEX petid_feedback_index
      (petid, feedback) ;

Then try this query:

SELECT pet.*
     , ownrs.id AS ownrunique
     , ownrs.super

     , ( SELECT COUNT(*)
         FROM comments
         WHERE pet.id = comments.petid
       ) AS noOfCom

     , ( SELECT MAX(comments.comDate)     --- Unfortunately, you need
         FROM comments                    --- 2 subqueries if you want
         WHERE pet.id = comments.petid    --- 2 results from a table
       ) AS lastCom                       --- (comments)

     , ( SELECT COUNT(*)
         FROM petfb
         WHERE pet.id = petfb.petid
           AND feedback IN (1, 100)
       ) AS newLikes

     , ( SELECT COUNT(*)
         FROM petfb
         WHERE pet.id = petfb.petid
           AND feedback IN (5,10)
       ) AS newDisLikes

     , ownrs.name
FROM
        ( SELECT pet.*
          FROM pet
          WHERE catid = 21
          ORDER BY `date` DESC
          LIMIT 0, 10
        ) AS pet
    INNER JOIN
        ownrs
            ON ownrs.uniqueID = pet.ownerID
ORDER BY p.date ;


Can't help the engine to choose the correct execution plan

From your plans I see the problem in this statement:

INSERT INTO #Analysis_ClientAccess_SurveyInstances_SurveyInstances ([SurveyInstanceID])
select SI.[SurveyInstanceID]
from [dbo].[SurveyInstances] SI
    inner JOIN #Analysis_ClientAccess_SurveyInstances_ProtoSurveysFiltered PSF
        on SI.[ProtoSurveyID] = PSF.[ProtoSurveyID]
    inner JOIN #Analysis_ClientAccess_SurveyInstances_ClientAccessStatusIDs CAS
        on SI.[ClientAccessStatusID] = CAS.[ClientAccessStatusID]
    inner JOIN #Analysis_ClientAccess_SurveyInstances_CampaignsFiltered CF
        on SI.[CampaignID] = CF.[CampaignID]
where SI.[IsOkForExport] = 1 AND SI.[HoldExport] = 0 AND ( PSF.[AllowedBy] = 'C' )
option(RECOMPILE)

There is no need in recompile here since there is no parameters at all, you have only constants here.
The difference in two plans is in the JOIN ORDER:

The correct join order is first

[dbo].[SurveyInstances] SI
    inner JOIN #Analysis_ClientAccess_SurveyInstances_ProtoSurveysFiltered PSF
        on SI.[ProtoSurveyID] = PSF.[ProtoSurveyID]

and then everything else, this join applies all the filters and produces only 104 rows that go to final resultset.

Your "slow" plan shows different join order:

[dbo].[SurveyInstances] SI
    inner JOIN #Analysis_ClientAccess_SurveyInstances_CampaignsFiltered CF
        on SI.[CampaignID] = CF.[CampaignID]

that produces 17 mln of rows.

You don't want this join order so your choices are to use FORCE ORDER hint or to simply save the result of the correct join to another temp table and then to join it with the rest of your query:

select SI.[SurveyInstanceID]
into #si_psf
from [dbo].[SurveyInstances] SI
    inner JOIN #Analysis_ClientAccess_SurveyInstances_ProtoSurveysFiltered PSF
        on SI.[ProtoSurveyID] = PSF.[ProtoSurveyID]
where SI.[IsOkForExport] = 1 AND SI.[HoldExport] = 0 AND ( PSF.[AllowedBy] = 'C' );

INSERT INTO #Analysis_ClientAccess_SurveyInstances_SurveyInstances ([SurveyInstanceID])
select SI.[SurveyInstanceID]
from #si_psf SI
    inner JOIN #Analysis_ClientAccess_SurveyInstances_ClientAccessStatusIDs CAS
        on SI.[ClientAccessStatusID] = CAS.[ClientAccessStatusID]
    inner JOIN #Analysis_ClientAccess_SurveyInstances_CampaignsFiltered CF
        on SI.[CampaignID] = CF.[CampaignID];

I suggest you to use the second method.


Emulating Oracle flashback functionality

You could try with replicating you changes to change tables. Dbvisit replicate could be of good value here. See Real-time Oracle data and database replication It is not a free tool but it saves a lot compared to running EE.


Replicate MySQL 5.0 Master to 5.5 Slave?

I have done that dozens of times for my employer's clients. In fact, I just did this a week ago. The client was using MySQL 5.0.81 and had degraded performance in InnoDB.

I create a new DB Cluster

running MySQL 5.5.27
Two DB Servers (ServerA and ServerB)
Configure InnoDB for Multicore Engagement

Master/Slave (ServerA Master and ServerB Slave)
I enable binary logging and log_slave_updates on Production's Slave
mysqldump all databases (except mysql schema) from Production's Slave to /root/MySQLData.sql
run pt-show-grants on  Production's Master to /root/MySQLGrants.sql
load ServerA with /root/MySQLGrants.sql
load ServerA with /root/MySQLData.sql
I replicate from Production's Slave (MySQL 5.0.81) to ServerA (MySQL 5.5.27)

All that was left to do was

Have the client shutdown the app
Remove DBVIP from Master running MySQL 5.0.81
Bring up DBVIP on Master running MySQL 5.5.27
Have the client start up the app

CONCLUSION

MySQL 5.5 can handle replication from previous major releases, and not the other way around.

I have discussed this before

Feb 04, 2011 : MySQL master binlog corruption
Jun 09, 2011 :  Yum update mysql from 5.1 to 5.5
Feb 08, 2012 : will replication from 5.5.20 to 5.0.XX server work?

GOOD NEWS !!!

I already answered a post back on Feb 06, 2012 ( How to setup replication(Master/slave) in MySQL 5.5.20? ) with all the steps you need.

BTW with regards to the step about dumping the MYSQL grants as SQL commands, here is my personal emulation of pt-show-grants:

mysql -hhostaddr -umyuserid -pmypassword --skip-column-names -A -e"SELECT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';') FROM mysql.user WHERE user<>''" | mysql -hhostaddr -umyuserid -pmypassword --skip-column-names -A | sed 's/$/;/g' > MySQLUserGrants.sql

Give it a Try !!!

UPDATE 2012-11-27 11:07 EDT

I wanted to add additional posts I made for setting up Circular Replication should you decide to setup the two DB servers as Master/Master

Sep 24, 2012 : Setting up MySQL circular replication in existing replication topology?
May 07, 2012 : Setting Circular Replication in mysql


I want to import tables from one sql server to another server using SSIS with loop

Maybe you just don't even need a foreach loop and can use the Rows per batch setting of the destination :

But, well, I don't know how is your package so I'm not sure it fits...


Assuming your queries are against regular Access tables, you should be able to replace the Access tables with Linked Tables to your SQL Server.  The Linked SQL tables have to have the same names and structure to in order for this to work.  I don't have the 2008 version of Access so I can't give exact instructions but look in the menus for something like External Data or Link Tables, then look for ODBC and create a "Link to the data source..." and follow the instructions to setup an ODBC data source to your SQL server, you can Google how to setup an ODBC connection if you need to.

You may need to setup the ODBC connection outside of Access beforehand depending on your security settings.  Keep in mind that if your OS is 64-bit you need to use the 32-bid ODBC administrator console to setup an ODBC data source that Access can use. (Wasted a day figuring out that one.)


Is this query safe from SQLi, when user_input_identifier and user_input_value are replaced by actual user input, which might be an SQLi attempt?

The whole sql injection vulnerability is about sanitized input, depending on how you're getting the inputs to the sanitizing function it may not even be safe to use them. In this case, I'll assume you're calling the SELECT statement from a client-library. In such a case, it's

no safer than if you just called the functions directly.
less efficient
still vulnerable to sql injection

The important thing to be safe is that you're binding the values to the input of the function with placeholders.

If your code looks like this,

k = sprintf("
  SELECT *
  FROM some_table
  WHERE validate_identifier(%s) = validate_value(%s)
");

You're not safe. If it looks like this,

k = sprintf("
  SELECT *
  FROM some_table
  WHERE quote_ident(%s) = quote_value(%s)
");

You're not safe. If it looks like this,

k = sprintf("
  SELECT *
  FROM some_table
  WHERE \"%s\" = '%s'
");

You're not safe. And, for all the same reasons. If it looks like this,

k = "
  SELECT *
  FROM some_table
  WHERE col = ?
";
k.exec($foo);

You're safe. If you don't know col at compile time, for instance if you want to safely do ? = ?, the library will either prefetch the escaped version by running something like this,

k = "SELECT quote_ident(?);"

Or it'll simply use a client-side version (which usually binds to libpq) thus saving a trip to the server.

So in answer to your question

  Is it efficient to use plpgsql functions only to validate identifiers and values?

It's certainly less efficient, and depending on how you're providing those functions their values, potentially not safe either.


Set "Encryption Enabled" to FALSE...what can go wrong?

Do not disable TDE. Besides being a lengthy size-of-data operation, it was established initially for a reason and you may be breaking whatever compliance/operations reason exists for TDE.

Rather set up log shipping properly in the presence of TDE. Follow the steps described in Move a TDE Protected Database to Another SQL Server. You are going to export the TDE certificate from the original server and then import it on the standby server, making sure it is encrypted with the master database master key and the database master master key is in turn encrypted with the service master key. After that, if done correctly, restore operations should succeed as the required TDE certificate is present.

To copy the certificate: The certificate is in the master database of the primary instance. You need to run:

BACKUP CERTIFICATE TDE_CERT
 TO FILE <where to save cert>
 WITH PRIVATE KEY (FILE = <where to save private key>,
  ENCRYPTION BY PASSWORD = <password for the private key file>);

Then copy both cert and private key files to the secondary and restore it in master using:

 CREATE CERTIFICATE TDE_CERT
  FROM FILE = <your copied cert file>
  WITH PRIVATE KEY (FILE = <your copied private key file>,
   DECRYPTION BY PASSWORD = <password that protects private key file>);

TDE_CERT in master on the secondary needs to be encrypted with master database master key, which will likely need to be created.


Or is there a way to access a subquery result?

Sure there are ways, but only in the same query at the next higher level.
You can extend the scope with a CTE, but still limited to the same query (anywhere).
To preserve a derived table across queries in the same session, materialize the result in a temporary table.
Or a regular table (maybe UNLOGGED?) to extend the scope to all sessions.

What's reasonable depends on the details of your installation.

Ideally, you do it all in a single query, in a single query level, even. The difficulty is the return type. You have to combine columns, possibly creating some redundancy in the result. You can either reduce query time or reduce redundant data in the result, but not both. Combining everything into a single JSON column might be an alternative.

To get everything in a single query:

SELECT n.id, n.title, n.description, id_channel
FROM   community.user_follows uf
JOIN   community.news         n USING (id_channel)
WHERE  uf.id_user ='3d4c788e0e34657457febba2040297c7';


Windows MySQL 5.5 Cannot connect to localhost: ERROR 1130 (HY000): Host 'localhost' is not allowed to connect to this MySQL server

Short answer: I recovered from backup

During my work day, I do perform several mysqldumps of my work database and mysql. But I could not perform an import since it would fail with the same Error 1130.

I stopped the db, I moved my data/mysql directory out of the way, and then dropped in my March 1st data/mysql. I restarted the database and found I could connect and perform selects from within MySQL Workbench and the DOS command line.

I performed a reboot, restarted the database, and all good.

My backup batch file (copied below) performs a dump of my work database (wipdb) to wipdb.sql and mysql database to mysql.sql

Note the WinRAR command contains the word "PASSWORD" which is the plaintext password used to encrypt my backups. If you use the script, you might want to change it to something else...

The WinRAR files are saved to C:\Backups\Daily\BackupYYYYMMDD-XX.rar where YYYYMMDD is the date and XX is numeric incremented during ever iteration of the script during the day

PATH=%PATH%;C:\Progra~1\WinRar

call mysqldump.exe wipdb --single-transaction --default-character-set=utf8 --max_allowed_packet=1G --user=root > C:\Backups\Daily\wipdb.sql

call mysqldump.exe mysql --single-transaction --default-character-set=utf8 --max_allowed_packet=1G --user=root > C:\Backups\Daily\mysql.sql

cd C:\backups

rar.exe a C:\Backups\Daily\Backup.rar -pPASSWORD -ai -mt2 -m4 -t -agYYYYMMDD-NN -dh -ed -idcdp -r C:\Tools\ C:\Data\websites\  C:\Progra~2\Zend\*.conf C:\Progra~2\Zend\MySQL55\data\mysql C:\Progra~2\Zend\MySQL55\data\wipdb C:\Progra~2\Zend\*.ini C:\Windows\System32\drivers\etc\* C:\Backups\Keep\* C:\Backup\Daily\*.sql


Why is Excel File Opened Exclusively By Another?

Ensure you have the right extension and path to the directory set in your variables within each object including the whereloop and connection file.

*.xls versus *.xls*


How do I specify a timeout in sqlite3 from the command-line?

You can do this by using an init file.

init.sql (note that the timeout value is in milliseconds - 1 is rather short):

.timeout 1000

At the prompt:

$ sqlite3 -init init.sql outgoing.db "select * from edges where worker is not null;"
Loading resources from init.sql
# 1 second pause
Error: database is locked

With some shells (on Linux at least, not very portable), you can avoid the need for a proper file with process substitution if that's a problem:

$ sqlite3 -init <(echo .timeout 1000) your.db "your sql;"

The extra output line ("Loading resources from ...") doesn't get printed if the output is redirected to a file or pipe, or to the .output file if you specified one in your init file.


connection pooling, transactions, nested transaction and rollback

There is no such thing as nested transactions in any released version of SQL Server. You can say BEGIN TRANSACTION as many times as you want, but a ROLLBACK affects all of them (never mind what @@TRANCOUNT says - it only reflects nesting level).

From the documentation:

  It is not legal for the transaction_name parameter of a ROLLBACK TRANSACTION statement to refer to the inner transactions of a set of named nested transactions. transaction_name can refer only to the transaction name of the outermost transaction. If a ROLLBACK TRANSACTION transaction_name statement using the name of the outer transaction is executed at any level of a set of nested transactions, all of the nested transactions are rolled back. If a ROLLBACK WORK or ROLLBACK TRANSACTION statement without a transaction_name parameter is executed at any level of a set of nested transaction, it rolls back all of the nested transactions, including the outermost transaction.

A demonstration:

CREATE TABLE dbo.foo(a INT);

BEGIN TRANSACTION;
  INSERT dbo.foo(a) SELECT 1;
  BEGIN TRANSACTION;
    INSERT dbo.foo(a) SELECT 2;
    BEGIN TRANSACTION;
    ROLLBACK TRANSACTION;

SELECT tc = @@TRANCOUNT;

SELECT a FROM dbo.foo;

DROP TABLE dbo.foo;

Results:

tc
----
0

1 row(s) affected.

a
----

0 row(s) affected.

So, there is no way to only roll back part of a "nested" transaction - it's all or nothing. If you want previous parts of this transaction to commit even if later parts fail, then stop nesting, and commit the first transaction before starting the next one.


Is there a "best-practices" type process for developers to follow for database changes?

In a VS environment, I've always used database projects to implement the update scripts. I tend to use unimaginative names like "DatabaseUpdate17.sql" or "PriceUpdateFebruary2010.sql" for my scripts. Having them as database projects lets me tie them to Team Server tasks, bugs (and if we did code reviews, to them as well). I also include in each database (that I have authority over) a table specifically for the collection of changes to the schema.

CREATE TABLE [dbo].[AuditDDL](
    [EventID] [int] IDENTITY(1,1) PRIMARY KEY NOT NULL,
    [EventData] [xml] NULL,                    -- what did they do
    [EventUser] varchar(100) NOT NULL,         -- who did it
    [EventTime] [datetime] DEFAULT (getdate()) -- when did they do it
    )
GO

Well, that takes care of 3 of the 6 Ws.

CREATE TRIGGER [trgAuditDDL]
ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS
AS
INSERT INTO AuditDDL(EventData, EventUser)
SELECT EVENTDATA(), original_login()
GO

I include an insert statement to log the beginning of a patch as well as the end of a patch. Events happening outside of patches are things to look into.

For example, a "begin patch" insert for "patch 17" would look like:

INSERT INTO [dbo].[AuditDDL]
           ([EventData]
           ,[EventUser])
     VALUES
           ('<EVENT_INSTANCE><EventType>BEGIN PATCH 17</EventType></EVENT_INSTANCE>'
           ,ORIGINAL_LOGIN())
GO

Since it also catches when indices are rebuilt, you'll need to run the following every month or so to clear out those events:

DELETE FROM AuditDDL
WHERE [EventData].exist('/EVENT_INSTANCE/EventType/text()[fn:contains(.,"ALTER_INDEX")]') =1
GO

DELETE FROM AuditDDL
WHERE [EventData].exist('/EVENT_INSTANCE/EventType/text()[fn:contains(.,"UPDATE_STATISTICS")]') =1
GO

Earlier version previously posted on Server Fault.

In a SOX and PCI-DSS compliant environment, you will never have access to the production servers. Therefore the scripts need to be clear and exercised beforehand. The comments at the top of the update scripts include lists of new tables, stored procs, functions, etc as well as lists of modified tables, stored procs, functions, etc. If data gets modified, explain what is being modified and why.

  A secondary problem is that you sometimes end up needing to manually coordinate changes if two separate tasks change the same database object. This may just be the way it is but it still seems like there should be some automated way of "flagging" these issues or something.

I've never come across a tool that lets us track this automatically. Previous employers used a principle of "database owner" - one and only one person who is personally in charge of the database. This person won't be the only developer working against that database, but rather all changes have to go through them. This has worked reasonably well to keep changes from colliding and damaging each other.


Postgres inplace PITR (point in time recovery) or rollback

There is no such facility in PostgreSQL but maybe a template database could help.
Create the template database as a copy of the production system. Then, whenever you need a test database, clone it with
CREATE DATABASE testdb TEMPLATE templatedb;


Transfer all read request to Slave in Master-Slave MySQL

this is called  Read/Write Splitting

MySQL itselft have no ablity to meed your demand,

but MySQL officially release a simple program called MySQL Proxy,
which will help you to archive your goal and more.
however it has lots of limitations, such as doesn't support procedure, etc.
and are offically NOT recommended to use in production environment.

Many companies create their own middleware and some are open sourced,
but as those middlewares are customized by their own scenario, it's actually not easy to use
in other enviroment. so my advice is ,according to your business, write your own middleware.

hope that useful!

added 2014-6-26 7:31 GMT

you probably could consider some other high availability solution such as :

Galera Cluster

Multi-Master Replication Manager for MySQL(a.k.a MMM)

they will partially meet your request, which means write/read on master node, read on several slave nodes

note that if you use MySQL Replication and read from the slave side, you probably read data slower than master side particularly there are heaviy wirte on master side, because hundreds of thousands of threads are writen simultaneously, while only one slave I/O thread propagate data to slave node.


Why does a Columnstore index build often wait on SLEEP_TASK?

SLEEP_TASK Idle Generic Sleep.
SLEEP_TASK is a general wait, used for all sorts of things where a more specific wait type doesn't exist, but it does crop up with Hashing operations (join/aggregate) that spill to tempdb, and occasionally at an exchange (Parallelism operator) where a consumer is waiting on something that isn't a normal CXPACKET or EXECSYNC wait. I would check for Hash spills first
SLEEP_TASK Wait Type in SQL Server - What does it indicate?


Is there a way to enumerate all locks that are currently being held via get_lock()?

You should create your own lock repository

CREATE DATABASE IF NOT EXISTS getlock;
CREATE TABLE IF NOT EXISTS getlock.lockbox
(
    conn_id BIGINT NOT NULL,
    name VARCHAR(64),
    PRIMARY KEY (name,conn_id)
    UNIQUE KEY (name)
) ENGINE=MEMORY;

Then, whenever you need to create a lock, you perform this sequence

SET @lockname = 'whateverlocknameihavechosen';
SELECT GET_LOCK(@lockname) INTO @x;
REPLACE INTO getlock.lockbox VALUES (CONNECTION_ID(),@lockname);
DELETE FROM getlock.lockbox WHERE conn_id NOT IN
(SELECT id FROM information_schema.processlist);

This will acquire the lock, insert or overwrite the previous lock owner, and cleanup the lockbox by locating all entries whose connection had terminated.

Since GET_LOCK() is unsafe for statement-based replication, you can locate the warnings written in the error log and should see the names of the locks.


How to remove multiple records from one table based on the non-existence of a record in another table?

Two ways to accomplish that:

DELETE FROM details WHERE product_id NOT IN (SELECT id FROM products)

However, it is recommended to use left join:

DELETE details FROM details LEFT JOIN products ON details.product_id=products.id WHERE products.id IS NULL


Migrating from phsyical Mysql to virtual instance

Copying data directory would work if you are using the same version of MySQL. As per you question, you are copying from 5.6 to 5.7, so this way wouldn't work.

Now: you mentioned you are "migrating", and the steps you are following are for "creating new slave"!

If you want to "migrate", a better way is the following:

Make a full backup using mysqldump, or other "logical" backup tools (as opposed to physical copy of the files)
restore this backup on your destination server

HTH


How to monitor Mongo databases and Cloud servers?

As per MongoDB documentation here Starting in version 4.0, MongoDB (Community Edition) offers free Cloud monitoring for standalones and replica sets.

Free monitoring provides information about your deployment, including:

Operation Execution Times
Memory Usage
CPU Usage
Operation Counts

By default, you can enable/disable free monitoring during runtime using db.enableFreeMonitoring() and db.disableFreeMonitoring().

You can also enable or disable free monitoring during mongod startup using either:

the configuration file setting cloud.monitoring.free.state
or
the command-line option --enableFreeMonitoring

Once you enable free monitoring, it remains enabled until you disable it.


Query with conditional JOIN running slow

The reason is that AND and OR has the same precedence and the long condition x AND y OR z AND w OR v AND... without round brackets has a bit different meaning than you think.

Your query can be rewritten that way:

FROM tableA AS A
JOIN tableB AS B ON ( B.col3 = 'X' AND B.col4 = A.col3 )
                 OR ( B.col3 = 'Y' AND B.col4 = A.col4 )
                 OR (. . . . .)


How to get sum of 3 sub count queries and group them using an attribute

SELECT '2019-12-02' AS `Date`,
       SUM(cg_response_code = 0)
     + SUM(     topup_id IN (10 , 11)
            AND topup_cg_response_code LIKE '0'
          ) AS `Sum`
FROM XAXBXC_charge_info_ABC_tbl
WHERE `date` = '2019-12-02'

PS. LIKE '0' condition is too strange. I'd recommend to replace it with = 0 or = '0', depending on the topup_cg_response_code datatype.

  This will work for a particular day, but I want records for each day – sadeee nadeee 2 mins ago

SELECT `date`,
       SUM(cg_response_code = 0)
     + SUM(     topup_id IN (10 , 11)
            AND topup_cg_response_code LIKE '0'
          ) AS `Sum`
FROM XAXBXC_charge_info_ABC_tbl
/* WHERE `date` BETWEEN @date1 AND @date2 */
GROUP BY `date`


How to open Oracle listener for three database instances?

Ok, I would like to answer my question. As i have understood what wrong I have done.

I created 3 database instances. So I should start 3 database instance one by one.

So how do we start 3 database instances?

every database instance has its own SID, so before you start the database, you need to put the
correct SID for that.

this is how i did it

ora112@localhost ~]$ ORACLE_SID=JIP02T
[ora112@localhost ~]$ ls
db.rsp  Desktop  summary
[ora112@localhost ~]$ sqlplus '/as sysdba'

SQL*Plus: Release 11.2.0.3.0 Production on Thu Dec 27 01:23:35 2012

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

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 5511979008 bytes
Fixed Size                  2238056 bytes
Variable Size            1107298712 bytes
Database Buffers         4378853376 bytes
Redo Buffers               23588864 bytes
Database mounted.
Database opened.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64                                                                             bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Then i need to set the SID for other database instance.

ora112@localhost ~]$ ORACLE_SID=JIP04K
[ora112@localhost ~]$ ls
db.rsp  Desktop  summary
[ora112@localhost ~]$ sqlplus '/as sysdba'

SQL*Plus: Release 11.2.0.3.0 Production on Thu Dec 27 01:23:35 2012

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

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 5511979008 bytes
Fixed Size                  2238056 bytes
Variable Size            1107298712 bytes
Database Buffers         4378853376 bytes
Redo Buffers               23588864 bytes
Database mounted.
Database opened.
SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64                                                                             bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

There after i just started the oracle listener, oracle listener is opened on port 1521.

So, using sql developer, i created 2 database connections to the two database instances using same port number 1521.


Help improving SQL Server 2005 query execution time

If you post schema and query etc, that would be useful, in addition to the other 3 answers.

Some links anyway, from Simple-Talk. Good stuff.

Execution Plan Basics
Graphical Execution Plans for Simple SQL Queries
Understanding More Complex Query Plans


How to Prevent Partitioned Columnstore Deadlocks on SELECT

Since you're on SQL Server 2016, it's worth mentioning that there is at least one public bug fix for parallel deadlocks involving columnstore indexes:

FIX: A deadlock occurs when you run a parallel query on a clustered columnstore index in SQL Server 2016 and 2017

(thanks to Denis Rubashkin for providing the link initially)

This was released as part of SP1 CU7.  If you're not up to that CU, you should give that a shot.  This fix would also be included in SP2 (any of the CUs).

In general, the two approaches for fixing intra-query parallelism deadlocks:

avoid parallelism (by tuning the query so that it doesn't go parallel, using a MAXDOP hint, etc.) - this is covered in the other answer by Thomas Costers
apply the latest service pack / cumulative updates to SQL Server


Is there a way to accept database access if and only if the SQL command is coming from my app, blocking any other access to the database?

No.

Postgres doesn't care which application the query came from.

It does, however, care a great deal about the machine from which the query was issued and under which account.

The first is managed by Host-Based Authentication (HBA), by adding entries into pg_hba.conf.  As long as your application server is configured in there, it stands a chance of getting in.  For example:

#     Database Account   Address         Auth  (Comment)
host  db1      app_user  11.22.33.44/32  md5   # server44

Then, your application will need a database account with which to connect.

Create a Postgres "User" (a.k.a. "login Role") for your application to use and only use those credentials within your application.  Use a separate Role to grant privileges on database objects (e.g. tables) and grant that Role to your application User:

create table table1 ... ;

# Application permissions
create app_role ;
grant select, insert, update, delete on table1 to app_role ;

# Developer permissions
create dev_role ;
grant select on table1 to dev_role ;

# Application account
create user app_user with password '...' valid until 'infinity' ;
grant app_role to app_user ;

# A Developer account
create user fred with password '...' ;
grant dev_role to fred ;

Of course, fred's Development machine will also have to appear in pg_hba.conf!


What is Report ID used for in SSRS reports?

I assume that is a local .rdlc file.

IMHO it has not practical use, in fact you can copy & paste as a new report using the same ReportID and it works.

Is it safe to create new .RDLC report as a copy of existing .RDLC report?

If you have a look at: RDL XML Schema for Version 2016/01 you won't find any reference to the <ReportID> tag, or if you get the RDL definition of any report stored in SSRS database, (Where does a published RDL file sit?), you won't be able to find this tag into the file definition.


Running Triggers on Slave with Row-based Events in MySQL >=5.6

Perhaps instead, you require a Stored Procedure on your slave that you can run from a cron job mysql  -e "call sp_udfs" to do the processing across the tables to alter that data. Events disable by default in a slave You could do a lot of work with that approach. This way you can control its behaviour much better than triggers. Other benefit, you can run any form of replication you wish, row, statement or mixed


Why am I getting a "login failed" when creating this linked server?

Log in to the serverA using "mydomain\myuser" credentials.
Run below TSQL :

EXEC master.dbo.sp_addlinkedserver
                            @server = N'serverB',
                            @srvproduct=N'',
                            @provider=N'SQLNCLI',
                            @datasrc=N'serverB',
                            @catalog=N'MyDatabase',
                            @provstr='Integrated Security=SSPI;'
GO
EXEC sp_addlinkedsrvlogin 'serverB', true
go

Check if the "serverB" exists in sys.servers :

select * from sys.servers

Check if the "serverB" works from ServerA

select * from serverB.master.dbo.sysdatabases


Coalesce per row - get all Detail IDs grouped per HeaderRow e.g. Invoice1 | 1,2,3 Invoice 2 | 4,5,6

This is standard sql server emulation of GROUP_CONCAT in mysql

select  SalesOrderNumber,Ids=Stuff((SELECT ',' + CAST(SalesOrderDetailID as VARCHAR(255)) FROM t t1 WHERE t1.SalesOrderNumber=t.SalesOrderNumber
 FOR XML PATH (''))
             , 1, 1, '' )
from t
GROUP BY SalesOrderNumber

Fiddle

Also,you might be intrested in http://groupconcat.codeplex.com/


How in Oracle can I export package that will have package and body?

Use SYS.DBMS_METADATA.GET_DDL to get a CLOB with the code:

declare
  myPackageSpec clob;
  myPackageBody clob;
BEGIN
    select dbms_metadata.get_ddl('PACKAGE_BODY', 'PACKAGE_NAME', 'SCHEMA')
       into myPackageBody
      FROM DUAL;
    select dbms_metadata.get_ddl('PACKAGE_SPEC', 'PACKAGE_NAME', 'SCHEMA')
      into myPackageSpec
    FROM DUAL;

  dbms_output.put_line(myPackageSpec);
  dbms_output.put_line(myPackageBody);
end;

You could also use expdp from command line and then impdpwith sqlfile

expdp scott/tiger@db10g schemas=SCOTT INCLUDE=PACKAGE SQLFILE=scott.sql directory=TEST_DIR dumpfile=SCOTT.dmp logfile=expdpSCOTT.log
impdp scott/tiger@db10g SQLFILE=scott.sql directory=TEST_DIR dumpfile=SCOTT.dmp logfile=impdpSCOTT.log


How to swap postgresql rows physical locations?

That being said, I came up with the idea of sorting table's rows in place, swapping their physical location in incremental steps, to gradually improve table's rows physical organization. Is this possible/safe with plain SQL? (ex: issuing updates based on the ctid column)

You can't swap CTIDs in plain sql. You'll get an error.

ERROR:  cannot assign to system column "ctid"

Further, you can't modify a row at all without totally rewriting the row. It's how MVCC works.


How to handle a table schema with a new relationship subtype in an interviews scenario?

My gut says that the schema is rapidly getting too complex.

Let's back up and think about "Entities" and "Relationships".

There are 2 main Entities:  People and Reviews.  See if it is not too clumsy to put Interviewers and Interviewees into the same People table.  If the columns are too different, then make 2 tables.  Ditto for Reviews.

Then provide the minimum number of Relationship tables -- but only when there are many:many relationships.  (Many:1 does not need an extra table.)  The "Interview" is likely to be a Relationship table, but have a few extra columns for when/where/how-long/type/etc.

Do not "over normalize".  For example, an Interview Type is probably one of a few keywords.  These could be implemented in an ENUM without needing an extra table.


If you don't know then you're not ready to write a tool of this nature. To echo the comments of others this is why people buy a tool to do it better than we can, and the prices are super reasonable for very small shops of a few servers.

The only people writing decent tools for themselves are highly skilled professionals sometimes working in larger shops where they can't get management to swallow a 6 figure monitoring cost.

If you want to monitor query performance you need to read up on query fingerprints, and fully read though some Grant Fritchey books on how plans are cached. It's hard going and even he makes mistakes; it's MCM level.

After that you'll understand a little but even the paid tools won't point out when something has gone wrong. What they do point out are the top queries (in CPU, disk, memory), or weird memory grants, or missing indexes. You can do and store these easily from the internet but they aren't alertable thresholds. They never are.

On your own you might also be able to pinpoint excessively different plan recompilations based on fingerprints and execution details - but likely this isn't worth it otherwise commercial tools would do it. It's probably too noisy.

There's another book called Healthy SQL which covers a lot of ideas and thresholds but it gets hazy.

Another great place is to download the Performance Analysis of Logs tool (PAL) which has a list of SQL counters (along with disk IO counters and similar things) plus defines in XML exactly how it calculates thresholds and why. These are useful overall but again I'm not sure I'd alert on them except as a daily report.

I think some company has also published a semi marketing white paper on why thresholds are pointless and what you really need are baselines (and doing statistical analysis on standard deviations but I'm pretty sure they don't use it for much themselves). But IMHO nobody is sharing easy to understand ways to analyse and flag disparate raw baseline data between snapshots. R looks like a promising spot to do it but there's no "how".


Is it possible to take same copy of mysql database in another instance(periodically) in the same server?

Yes , what you need is a replication environment !
Where your primary server (Master) will receive all updates and inserts, basiclly all DML's and the secondary server (slave) will receive only select statements !
 Here is a link to an article showing you how to do this.
 As for the load-balancing , in my case i use F5 to do this for me ! Here is link of the article that shows how this is done ! i have to worn you is quite hard to setup this puppy !

To run multiple instances using MySQL you need to have a couple of things separate from the initial install on MySQL like data directory, init script and config file.
 Ok i will place the steps here :
-all you need is for you complete them:

Create a new data directory
Create/copy existing mysql configuration file
Create/copy existing mysql init file to start/stop/reload etc on
this new instance

Edit the init file and make some minor changes to make it this instance specific - like port number ,instance name, take care at this step  !!

Install default tables for this new database instance

mysql_install_db --datadir=/var/lib/mysql_new --defaults-file=/etc/my_new.cnf --user=mysql
Start the new instance
Set password for this instance and connect to this new instance
Finally add it to server start-up list
And now setup your replication.


Greater than operator ignoring a nonclustered index?

Ensure your statistics are updated for your table and indexes.

Current statistics are crucial for determining the correct plan.

In your plan if the "Actual Number of Rows" and the "Estimated Number of Rows" are fairly close then your statistics are ok.

If they need updated try using a FULLSCAN - sql server uses it's own sample size but this can sometimes be too small.


"INSERT INTO res_partner () VALUES ()" failure (autoincrement in postgres)

Your linked question is for MySQL which doesn't really have a track record for valid standard SQL - and having an empty column list or empty values list is far from being "legal" syntax in standard SQL or Postgres.

If you want to insert the default values you have two choices

insert into public.res_partner (id) values (default);

Or if you do not want to specify the column name, use:

insert into public.res_partner
default values;