What's the overhead of updating all columns, even the ones that haven't changed When it comes to updating a row, many ORM tools issue an UPDATE statement that sets every column associated to that particular entity.

The advantage is that you can easily batch the update statements since the UPDATE statement is the same no matter what entity attribute you change. More, you can even use server-side and client-side statement caching as well.

So, if I load an entity and only set a single property:

Post post = entityManager.find(Post.class, 1L);
post.setScore(12);

All columns are going to be changed:

UPDATE post
SET    score = 12,
       title = 'High-Performance Java Persistence'
WHERE  id = 1

Now, assuming that we have an index on the title property as well, shouldn't the DB realize that the value hasn't changed anyway?

In this article, Markus Winand says:

  The update on all columns shows the same pattern we have already
  observed in the previous sections: the response time grows with each
  additional index.

I wonder why is this overhead since the database loads the associated data page from disk into memory and so it can figure out whether a column value needs to be changed or not.

Even for indexes, it does not to re-balance anything since the index values don't change for the columns that haven't changed, yet they were included in the UPDATE.

Is it that the B+ Tree indexes associated to the redundant unchanged columns need to be navigated as well, only for the database to realize that the leaf value is still the same?

Of course, some ORM tools allow you to UPDATE just the changed properties:

UPDATE post
SET    score = 12,
WHERE  id = 1

But this type of UPDATE might not always benefit from batch updates or statement caching when different properties are changed for different rows.

I know you're mostly concerned about UPDATE and mostly about performance, but as a fellow "ORM" maintainer, let me give you another perspective on the problem of distinguishing between "changed", "null", and "default" values, which are three different things in SQL, but possibly only one thing in Java and in most ORMs:

Translating your rationale to INSERT statements

Your arguments in favour of batchability and statement cacheability hold true in the same way for INSERT statements as they do for UPDATE statements. But in the case of INSERT statements, omitting a column from the statement has a different semantics than in UPDATE. It means to apply DEFAULT. The following two are semantically equivalent:

INSERT INTO t (a, b)    VALUES (1, 2);
INSERT INTO t (a, b, c) VALUES (1, 2, DEFAULT);

This isn't true for UPDATE, where the first two are semantically equivalent, and the third one has an entirely different meaning:

-- These are the same
UPDATE t SET a = 1, b = 2;
UPDATE t SET a = 1, b = 2, c = c;

-- This is different!
UPDATE t SET a = 1, b = 2, c = DEFAULT;

Most database client APIs, including JDBC, and by consequence, JPA, don't allow for binding a DEFAULT expression to a bind variable - mostly because the servers don't allow this either. If you want to re-use the same SQL statement for the aforementioned batchability and statement cacheability reasons, you'd use the following statement in both cases (assuming (a, b, c) are all the columns in t):

INSERT INTO t (a, b, c) VALUES (?, ?, ?);

And since c isn't set, you'd probably bind Java null to the third bind variable, because many ORMs also cannot distinguish between NULL and DEFAULT (jOOQ, for example being an exception here). They only see Java null and don't know whether this means NULL (as in the unknown value) or DEFAULT (as in the uninitialised value).

In many cases, this distinction doesn't matter, but in case your column c is using any of the following features, the statement is simply wrong:

It has a DEFAULT clause
It might be generated by a trigger

Back to UPDATE statements

While the above is true for all databases, I can assure you that the trigger issue is true for the Oracle database as well. Consider the following SQL:

CREATE TABLE x (a INT PRIMARY KEY, b INT, c INT, d INT);

INSERT INTO x VALUES (1, 1, 1, 1);

CREATE OR REPLACE TRIGGER t
  BEFORE UPDATE OF c, d
  ON x
BEGIN
  IF updating('c') THEN
    dbms_output.put_line('Updating c');
  END IF;
  IF updating('d') THEN
    dbms_output.put_line('Updating d');
  END IF;
END;
/

SET SERVEROUTPUT ON
UPDATE x SET b = 1 WHERE a = 1;
UPDATE x SET c = 1 WHERE a = 1;
UPDATE x SET d = 1 WHERE a = 1;
UPDATE x SET b = 1, c = 1, d = 1 WHERE a = 1;

When you run the above, you will see the following output:

table X created.
1 rows inserted.
TRIGGER T compiled
1 rows updated.
1 rows updated.
Updating c

1 rows updated.
Updating d

1 rows updated.
Updating c
Updating d

As you can see, the statement that always updates all the columns will always fire the trigger for all the columns, whereas the statements updating only columns that have changed will fire only those triggers who are listening for such specific changes.

In other words:

The current behaviour of Hibernate that you're describing is incomplete and could even be considered wrong in the presence of triggers (and probably other tools).

I personally think that your query cache optimisation argument is overrated in the case of dynamic SQL. Sure, there will be a few more queries in such a cache, and a bit more parsing work to be done, but this is usually not a problem for dynamic UPDATE statements, much less than for SELECT.

Batching is certainly an issue, but in my opinion, a single update shouldn't be normalised to update all the columns just because there is a slight possibility of the statement being batchable. Chances are, the ORM can collect sub-batches of consecutive identical statements and batch those instead of the "whole batch" (in case the ORM is even capable of tracking the difference between "changed", "null", and "default"


Finding Damage Done by FOREIGN_KEY_CHECKS=0 MySQL allows an end user to temporarily disable foreign key checks with the following statement

SET FOREIGN_KEY_CHECKS=0;

This means it's incredibly common (at least in my corner of the world) to find production MySQL databases where some rows have invalid foreign keys.  For example, if you setup two tables with a foreign key relationship

CREATE TABLE `foo` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

CREATE TABLE `bar` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `foo_id` int(11) DEFAULT NULL,
  `name` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `foo_id` (`foo_id`),
  CONSTRAINT `bar_ibfk_1` FOREIGN KEY (`foo_id`) REFERENCES `foo` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;

and insert some legitimate data

insert into foo(id,name) VALUES (1,'Test 1');

insert into foo(id,name) VALUES (2,'Test 2');

insert into bar(name, foo_id) VALUES ("Hello", 1);

and then use the above statements to turn off foreign key checks and set some bogus data

SET FOREIGN_KEY_CHECKS=0;
insert into bar(name, foo_id) VALUES ("Hello", 100);
SET FOREIGN_KEY_CHECKS=1;

You now have a bar table with a foo_id of 100, but no such row in the foo table.

My Question: Does MySQL have any built-in tools for finding this sort of bogus data?  If not, are there common DBA tools for finding this sort of bogus data?  Or am I writing a program to manually scan every table for foreign_keys, and then check each row individually?

The way to check for "incorrect" foreign keys, you have to do something like:

SELECT
   bar.*
FROM
   bar
   LEFT JOIN foo ON bar.foo_id = foo.id
WHERE
   bar.foo_id IS NOT NULL AND foo.id IS NULL ;

or its equivalent

SELECT
    bar.*
FROM
    bar
WHERE
    bar.foo_id IS NOT NULL AND
    bar.foo_id NOT IN (SELECT foo.id FROM foo WHERE foo.id IS NOT NULL) ;

There is a tool, written in Ruby, called mysql-foreign-key-checker, by Michael Irey that can do this kind of checks systematically for you. The program is actually quite simple and easy to understand, and could be translated  quite easyly to the language of your choice.

Check the queries at SQLFiddle.

NOTE: I know that some IS NOT NULL shouldn't be necessary in the queries, because you already have NOT NULL constraints. But... if you're checking for data that actually violates some constraints... you'd better not rely on any constraint.


Assign db_owner to all users at once I need to assign db_owner to all users within an instance. There are a few hundred logins and same number of databases within the instance. I would like each user to get db_owner within the database where that user exists. I know how to do it for individual users, but I do not know how it's done for all of them at once.

I am well aware that it is not a good idea to give db_owner to everyone, but...we are installing a very specific application. Every users who wants to use this application will need db_owner. We already tried walkarounds, but the aplicaton is rather primitive in nature so until application code is changed we will have to tolerate insane number of db_owners. There is no sensitive data on this particular instance anyway so we have management approval.

I would use two cursors. The first runs through all databases - except sys dbs. For each database the logins will be fetched and added to the db_owner role.

If you have hundreds of dbs/ logins, I would write this as a db with some transaction handling and/or logging that if something crashes while executing you can continue on the point of crash.

i couldn't test the code below, this should be an approach how to develop your own proc

Use master
GO

DECLARE @dbname VARCHAR(50)
DECLARE @SQL_statement varchar(max)
DECLARE @chng_role NVARCHAR(MAX)

DECLARE db_cursor CURSOR
LOCAL FAST_FORWARD
FOR
  SELECT name
    FROM MASTER.dbo.sysdatabases
    WHERE name NOT IN ('master','model','msdb','tempdb')
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @dbname
WHILE @@FETCH_STATUS = 0
 BEGIN

    SET @SQL_statement = 'Declare  users_cursor CURSOR FOR SELECT name FROM '+ @dbname +'.sys.database_principals where (type='''S''' or type = '''U''')'
    EXEC sp_executesql @sqlstatement
    OPEN users_cursor
    FETCH NEXT FROM users_cursor INTO @UserName
    WHILE @@FETCH_STATUS = 0
        BEGIN
            SET @chng_role = 'use [' + @dbname + ']; exec sp_addrolemember @rolename = ''db_owner'', @membername = ''' + @userName + ''''
            EXECUTE sp_executesql @chng_role
            FETCH NEXT FROM users_cursor INTO @UserName
        END
    CLOSE users_cursor
    DEALLOCATE users_cursor

 FETCH NEXT FROM db_cursor INTO @dbname
END
CLOSE db_cursor
DEALLOCATE db_cursor


Using WITH TIES to retrieve multiple rows with same value I am relatively new to SQLite. I have a database called "Clothes Catalog" that contains info on each data point's item type (shirts, pants, etc), catalog ID, and price.

I want to retrieve the maximum price of an item of each type, the item type, and the catalog ID. If two items within a type have the same price, I want both catalog ID's to be included.

I tried this query but it does not run, any ideas why?

  SELECT TOP (1) WITH TIES itemtype, catalogID, price
  from clothes_catalog
  group by itemtype
  order by catalogID ASC;

Lets say my table looks like this:

 shirt, 20456, $16
 shirt, 23456, $18
 pant, 2222, $20
 pant, 4444, $20
 pant, 5656, $15

I want the following to show, and ALSO have catalogID in ascending order:

 pant, 2222, $20
 pant, 4444, $20
 shirt, 23456, $18

any suggestions?

This query returns the maximum price for each type:

SELECT itemtype,
       max(price)
FROM clothes_catalog
GROUP BY itemtype;

To get all items with that price, join these results back to the original table:

SELECT itemtype,
       catalogID,
       price
FROM clothes_catalog
JOIN (SELECT itemtype,
             max(price) AS price
      FROM clothes_catalog
      GROUP BY itemtype
     )
     USING (itemtype, price)
ORDER BY catalogID;


Is it possible to use SQLite as a client-server database? Are there any techniques or tools to work with SQLite on a medium size/traffic/concurrency DB environment?

SQLite is an embedded database and it is not intended to be used as a client/server DB. If you really want to, you can use SQLitening.

What SQLitening is

  SQLitening is a client/server implementation of the very popular SQLite database.

  SQLitening is a programmer's library in standard Win32 DLL form. It is installed as a standard Windows Service. In addition to client/server mode, the library allows the programmer to also access SQLite databases in local mode. In either mode (local or client/server), the database is extremely fast and robust.
   -- Source: http://www.planetsquires.com/sqlite_client_server.htm


Impact of adjusting Time of Mongodb server? Using Ubuntu 14.04 and mongodb 2.6.10

I have time difference between a replica machine and primary , my secondary isn't configured with NTP what are the implications of adjusting time on the secondary machine ?

The impact of time adjustments depends on how significant the clock drift is between members of your replica set. Generally the most impactful outcome will be some confusing diagnostic information: operations during the affected period may appear to have improbably large (or small) durations.

If you are running any multiple server deployment you should always use a clock sync service (eg. NTP) to avoid potential clock drift. Generally services that make small, incremental adjustments to time (eg. ntpd) are preferable to utilities that make large adjustments (eg. ntpdate).

For more information on the potential outcomes of clock skew with MongoDB, see: MongoDB and Leap Seconds.


Replication from Azure SQL Database table to On-premise SQL Server possible? I have a table on Azure SQL Database that I would like to have replicated/mirrored to our on-premise SQL Server

So the on-prem SQL Server would have a copy of table from Azure that is always up-to-date, available for read-only queries

Is there a technology for this ?

The reason I need this is because I need to join this Azure table to some tables on-premise (on 300K + rows) in a query, and linked server is not working for me very well, despite all the tricks and workarounds I have tried

Regards,

In theory, Azure Data Sync does this, but it's been in "preview" status for years, and there's a lot of gotchas. Read the documentation carefully to see if its limits can work for you, and just know that in a preview program, Microsoft can end up removing support for it later.

A better solution would be something like SSIS jobs to keep the data in sync, but that's obviously not easy, either. As your tables change, you'll need to keep changing the ETL jobs.


Amazon Aurora MySQL5.6 Performance Schema column datatype modified and can't be corrected I'm having trouble getting performance_schema to work in a few of my Aurora instances.  Brand new instances where I enable performance_schema work just fine, but several instances that I've had for some time seem to be completely broken and I don't know why.  The error logs indicate that the problem is that many columns within performance_schema are of the wrong type.

Excerpt of logs for detail:

2017-11-06 18:28:28 28436 [ERROR] Incorrect definition of table performance_schema.events_waits_current: expected column 'NESTING_EVENT_TYPE' at position 15 to have type enum('STATEMENT','STAGE','WAIT', found type varchar(9).
2017-11-06 18:28:28 28436 [ERROR] Incorrect definition of table performance_schema.events_waits_history: expected column 'NESTING_EVENT_TYPE' at position 15 to have type enum('STATEMENT','STAGE','WAIT', found type varchar(9).
2017-11-06 18:28:28 28436 [ERROR] Incorrect definition of table performance_schema.events_waits_history_long: expected column 'NESTING_EVENT_TYPE' at position 15 to have type enum('STATEMENT','STAGE','WAIT', found type varchar(9).

Not all columns are affected in this way but most appear to be.  Is there any reason why this might have occurred?  Is there a way to fix this?  I assume Amazon support should be able to correct it on the back end but they have been less than helpful thus far.

This appears to be the result of the MySQL engine upgrade to version 5.7.3 or later, where the definition of some tables in the performance schema have changed.

If you were running standard MySQL, mysql_upgrade would have fixed that. You'll need to contact Amazon support for ways to upgrade your existing databases to the new engine version.

In the worst case you'd need to create new copies of each affected database and move the data.


How does SQL Server determine the expression type when mixing Unicode and non-Unicode strings? When you say a + b in T-SQL and a and b are of a string type (e.g. varchar(...) or nvarchar(...)): How is the resulting type determined?

I found no clear rules for this when experimenting. It seems to depend on whether literals are involved.

Also, how is the length determined? I found out that the type of 'x' + 'y' is varchar(2). Apparently, the length of the literal is being tracked.

Here's a little experiment that I ran:

USE tempdb

DECLARE @v VARCHAR(400) = 'x'
DECLARE @n NVARCHAR(400) = 'x'

SELECT
    '' AS [varchar]
    , N'' AS [nvarchar]

    , ('x' + N'x') AS ['x' + N'x']
    , (N'x' + 'x') AS [N'x' + 'x']
    , (ISNULL('x', N'x')) AS [ISNULL('x', N'x')]
    , (ISNULL(N'x', 'x')) AS [ISNULL(N'x', 'x')]
    , (COALESCE('x', N'x')) AS [COALESCE('x', N'x')]
    , (COALESCE(N'x', 'x')) AS [COALESCE(N'x', 'x')]

    , (@v + @n) AS [@v + @n]
    , (@n + @v) AS [@n + @v]
    , (ISNULL(@v, @n)) AS [ISNULL(@v, @n)]
    , (ISNULL(@n, @v)) AS [ISNULL(@n, @v)]
    , (COALESCE(@v, @n)) AS [COALESCE(@v, @n)]
    , (COALESCE(@n, @v)) AS [COALESCE(@n, @v)]
INTO StringConversionTest

The documentation calls out the behavior of ISNULL and COALESCE so I understand that part. But the + operator seems to behave under a more complex set of rules.

There are several situations in which two (or more) distinct things are being combined and yet there can only be a single result. These questions are resolved by applying a set of precedence rules. In this case, it would use both Datatype Precedence and Collation Precedence. But there is also logical precedence (when combining AND and OR without parenthesis) and how to deal with decimal computations of varying precision, etc).

Data Type Precedence
Collation Precedence
Precision, Scale, and Length

Related:

Operator Precedence


How to create an index to improve this query I am trying to improve the speed of the following query:

SELECT COUNT(*) AS count,
       SUM(amount) AS total,
       AVG(case when service_years >= 30 then amount end) AS average,
       employer
FROM pensions_pension
WHERE agency_id = 4
GROUP BY employer
ORDER BY count DESC NULLS LAST;

Table structure:

id: integer (primary key)
name: text
slug: varchar(50)
title: text
employer: text
pension: numeric(10,2)
benefits: numeric(10,2)
disability: numeric(10,2)
amount: numeric(10,2)
service_years: numeric(10,2)
retirement_year: smallint
year: smallint
agency_id: integer (foreign key)
rank: smallint
note_text: text

Existing index definitions:

CREATE UNIQUE INDEX pensions_pension_pkey ON public.pensions_pension USING btree (id)
CREATE INDEX employer ON public.pensions_pension USING btree (employer)
CREATE INDEX pensions_pension_agency_id ON public.pensions_pension USING btree (agency_id)
CREATE INDEX pensions_pension_amount ON public.pensions_pension USING btree (amount DESC)
CREATE INDEX pensions_pension_employer ON public.pensions_pension USING btree (employer)
CREATE INDEX pensions_pension_rank_idx ON public.pensions_pension USING btree (rank, amount DESC)
CREATE INDEX pensions_pension_slug ON public.pensions_pension USING btree (slug)
CREATE INDEX pensions_pension_slug_like ON public.pensions_pension USING btree (slug varchar_pattern_ops)
CREATE INDEX pensions_pension_year ON public.pensions_pension USING btree (year)
CREATE INDEX year_amount ON public.pensions_pension USING btree (year DESC, amount DESC)
CREATE INDEX pensions_pension_year_29d1e58f8ad8ed60_uniq ON public.pensions_pension USING btree (year)
CREATE INDEX idx_pensions_pension_year_agency_id ON public.pensions_pension USING btree (year, agency_id) WHERE ((year <= 2016) AND (year IS NULL))
CREATE INDEX pensions_pension_idx_agency_id ON public.pensions_pension USING btree (agency_id)
CREATE INDEX pensions_pension_partial_agency_id__index ON public.pensions_pension USING btree (agency_id, employer) WHERE (agency_id = 4)

This is the query plan when I run ANALYZE EXPLAIN in postgresql:

Sort  (cost=215259.09..215266.48 rows=2956 width=96) (actual time=9976.097..9976.672 rows=3527 loops=1)
  Sort Key: (count(*)) DESC NULLS LAST
  Sort Method: quicksort  Memory: 551kB
  ->  HashAggregate  (cost=215044.35..215088.69 rows=2956 width=96) (actual time=9968.124..9973.413 rows=3527 loops=1)
        Group Key: employer
        ->  Seq Scan on pensions_pension  (cost=0.00..178968.04 rows=2886105 width=38) (actual time=0.054..5236.262 rows=2942951 loops=1)
              Filter: (agency_id = 4)
              Rows Removed by Filter: 2777772
Planning time: 0.443 ms
Execution time: 9983.167 ms

I tried to add an index on agency_id. I also tried creating a partial index on agency_id by setting it to agency_id=4. But the query planner keeps choosing a seq scan instead of an index scan. I'm fairly new to creating indexes and would like to learn about the process and how I can speed up something like this. I am running Postgresql 9.6.

There is no way to test this without your actual data. Theoretically, your optimal index for this query will be on:

agency_id, employer, amount, service_years

This index should allow immediate push-down filtering on agency_id, followed by a stream aggregate instead of the hash aggregate by employer, and the last 2 are just to cover the query, and avoid lookups to the main table to fetch amount and service_years which the query needs for the aggregates.

Generally speaking, when designing indexes, you should consider more than one query, and see if you can find common query patterns that will allow you to create near optimal indexes that will serve multiple types of queries.
The way too common indexing strategy of 'Let's create an optimal index for every painful query' tends to lead to over-indexed databases, which will pose a set of challenges of its own, including modification overhead, and over-sized optimizer search trees.

Let us know if the optimizer uses this index efficiently.


Indexes and windowing functions We have a design in which a table stores the history of changes to an entity, and a view queries the table for the latest row for that entity.
Say, employee_history and employee (the view with latest status).
The view is defined using a rank() function:
create view employee as (
  select * from (
    select
      *
      , rank() over (partition by employee_id order by last_update_ts desc)
    from employee_history
  ) e
  where r = 1
)

There is an index over the employee_id and the update timestamp, both fields used in the view definition.
Most of the queries over this view are for a specific employee, with its ID. So the query engine makes good use of the index.
But now we need a query to find employees in some department, using the department name. This view definition cannot make use of the index.
How may I get a more performant query over the history table? I can create new indices and new views. I cannot create a new table containing only the current data; it has to be over the history table.
This is on Postgres 11.

Your query for a specific department can make use of the index:
create index on employee_history (employee_id , last_update_ts desc);

The index without the desc indeed cannot be used.  I don't think there is a good reason for this, it is just some kind of oversight in the planner.  However, this index usage of the index that can be used is not going to be particularly fast anyway. It will have to read the entire index and table.  It will just avoid the sort of the entire table, not the reading of it.
This is a fundamental limitation of your questionable design.  Without some extra limitations (like employees can never move between departments) I don't see anything you can do about this in general.


I stuck on the network's settings with Griddb on my ubuntu Hostname -i
I got 127.0.1.1
with this command ip -f inet -o addr show eth0 | cut -d' ' -f 7 | cut -d/ -f 1
I got Device "eth0" does not exist
So what do you suggest to fix it? or should I continue to use the ip 127.0.1.1?

Ubuntu generally renames the network devices. You can list them all with ip a. The first device with be the loopback, which is usually 127.0.0.1. The second device is often (but not always) the one that is used for external communications and will look something like enp0s3.


How to massively change string capitalization in a table? I have a project where authors' names have been inserted in all capital letters (ex. JOHN KRAUS, LEONARD MILES, NAOMI DAWSON). Now I need these values in "normal" case (ex. John Kraus, Leonard Miles, Naomi Dawson)
How can I achieve that?

Mysql has a mechanism called cursors that allows to read all the rows from the table one by one. Detailed description can be found here:
https://dev.mysql.com/doc/refman/8.0/en/cursors.html
You have to write a stored routine with a cursor loop inside and to place some case transforming logic in it. Useful functions are LOWER(), UPPER(), SUBSTRING() and other text functions described here:
https://dev.mysql.com/doc/refman/8.0/en/string-functions.html


Microsoft SQL Server Connection Coloring I've been using the Redgate Query Tab Coloring feature (under SQL Prompt's Experimental Features) and I really like it.  Somehow I've never known until today that this feature is built in to SSMS for Status Bar coloring.

My question is, with either Redgate, SSMS, or SSMS Tools Pack, is there some way to set a policy or something on the Microsoft SQL Server instance itself that will cause any SSMS clients to be colorized?  Our server is running SQL 2012 SP2 and SSMS clients are either 2014 or 2012 SP2, also.

Let's just say there are some error-prone co-workers that have been known to execute queries against an instance thinking it's TEST or DEV and it ends up being PROD.  We're all human I guess, but anything that can help would be really great.  And I don't want to rely on the other people to set the coloring settings (because I know some of them won't).  I know, I know, everyone shouldn't have PROD access, but that's another battle I need to fight.

Red Gate implementation:

Built-in SQL Server Management Studio implementation:

Boiling down the question to the basics:

Q: "is there some way to set a policy or something on the Microsoft SQL Server instance itself that will cause any SSMS clients to be colorized?"

No. SSMS query color-coding is part of a client-side connection and that data is stored in the registry. You could theoretically export that registry setting and apply it to anyone in your company with SSMS, but it's not a trivial task for us database people.

I'd take that request to your sysadmins with Active Directory Group Policy administration duties and explain what you need. It's a fairly common type of request for companies that manage software deployments centrally. Unfortunately, it's not point-and-click easy, and you'll need to update the registry keys every time you add or rename a production server. These types of mass client deployments usually require change requests because modifying the Windows registry isn't something you want to be doing often.


configure remote connection for multiple instances sql server 2008 I've installed 2 instances of SQL Server 2008 R2 onto our local windows server.  For the purposes of testing I've turned off the firewall.  I'll get the firewall back up after I manage a successfull remote connection. The server isnt exposed anyway.

TCP\IP is turned on for both instances.  The default port is assigned to the Default instance and that one is working fine.

I've assigned 4143 to the named instance but I'm still unable to connect remotely.

I can connect remotely to the default instance just not the named instance.  What step or steps am I missing?

You need to start the SQL Browser service to enable connections to named instances.

Explanation here:

  Upon startup, SQL Server Browser starts and claims UDP port 1434. SQL
  Server Browser reads the registry, identifies all instances of SQL
  Server on the computer, and notes the ports and named pipes that they
  use. When a server has two or more network cards, SQL Server Browser
  returns the first enabled port it encounters for SQL Server. SQL
  Server Browser support ipv6 and ipv4.


When should a primary key be declared non-clustered? While creating a test database for another question I asked earlier, I remembered about a Primary Key being able to be declared NONCLUSTERED

When would you use a NONCLUSTERED primary key as opposed to a CLUSTERED primary key?

Thanks in advance

The question is not 'when should the PK be NC', but instead you should ask 'what is the proper key for the clustered index'?

And the answer really depends on how do you query the data. The clustered index has an advantage over all other indexes: since it always includes all columns, is always covering. Therefore queries that can leverage the clustered index certainly do not need to use lookups to satisfy some of the projected columns and/or predicates.

Another piece of the puzzle is how can an index be used? There are three typical patterns:

probes, when a single key value is seek-ed in the index
range scans, when a range of key values is retrieved
order by requirements, when an index can satisfy an order by w/o requiring a stop-and-go sort

So if you analyze your expected load (the queries) and discover that a large number of queries would use a particular index because they use a certain pattern of access that benefits from an index, it makes sense to propose that index as the clustered index.

Yet another factor is that the clustered index key is the lookup key used by all non-clustered indices and therefore a wide clustered index key creates a ripple effect and widens all the non-clustered indices and wide indices mean more pages, more I/O, more memory, less goodness.

A good clustered index is stable, it does not change during the lifetime of the entity, because a change in the clustered index key values means the row has to be deleted and inserted back.

And a good clustered index grows in order not randomly (each newly inserted key value is larger than the preceding value) as to avoid page splits and fragmentation (without messing around with FILLFACTORs).

So now that we know what a good clustered index key is, does the primary key (which is a data modelling logical property) match the requirements? If yes, then the PK should be clustered. If no, then the PK should be non-clustered.

To give an example, consider a sales facts table. Each entry has an ID that is the primary key. But the vast majority of queries ask for data between a date and another date, therefore the best clustered index key would be the sales date, not the ID. Another example of having a different clustered index from the primary key is a very low selectivity key, like a 'category', or a 'state', a key with only very few distinct values. Having a clustered index key with this low selectivity key as the leftmost key, e.g. (state, id), often makes sense because of ranges scans that look for all entries in a particular 'state'.

One last note about the possibility of a non-clustered primary key over a heap (i.e. there is no clustered index at all). This may be a valid scenario, the typical reason is when bulk insert performance is critical, since heaps have significantly better bulk insert throughput when compared to clustered indices.


SSIS data flow task failed for no reason An SSIS package keeps failing for no reason. There are only three lookup transform components in the package (besides the source and output component), two running in partial cache mode and the other in full cache mode. Each lookup will need to find records among tens of millions of entries.

When the full-cached component have cached around 10M records the data flow will fail. The only error message is "Data flow task failed" (no error code provided). At that time the server has 15% free memory, and SSIS uses up about 3GB. There is no warning about memory space during execution. Windows Server and SQL Server are 64 bit, but I'm not sure about Visual Studio BIDS.

Think about how the reference and input data sets are configured.
The reference data set (smallest set) should be the only cached data set, while the input rows(i.e. the 10M) pass through that execution tree's buffer.

Also, running parallel lookups on different subsets of the data and aggregating all outputs with a UNION ALL at the end might yield better performance, but test it.

Lasty, look for any block transformations (i.e. Sort, Aggregates, etc.) before or after the lookup.

Getting Optimal Performance with Integration Services Lookups

Integration Services: Performance Tuning Techniques


Issue while restarting - MongoDB I had tried to start the MongoDB service using the following command in linux machine.

service mongod restart

I am getting the following error

Stopping mongod:                                           [FAILED]
Starting mongod: about to fork child process, waiting until server is ready for connections.
forked process: 1428
all output going to: /data/log/mongod.log
child process started successfully, parent exiting

I am having the config file as follows

dbpath = /data/db/
logpath = /data/log/mongod.log
logappend = true
fork = true
port = 27017
pidfilepath = /var/run/mongod.pid
rest = true
maxConns = 12000
oplogSize = 10000
directoryperdb = true
keyFile = /etc/mongod.key
master = true
smallfiles = true

Note

 service mongod status
 mongod dead but subsys locked

Any suggestion will be helpful.

$ rm -f /var/lock/subsys/mongod

$ rm -f /var/lib/mongodb/mongod.lock


Autovacuum in Postgresql database I am trying to set up autovacuum in my postgresql 9.5 database, but table stats shows me no evidence of that.

In postgresql.conf, the autovacuum is already enabled:

  autovacuum = on

In settings, plus, I have (select * from pg_settings where category like 'Autovacuum')

autovacuum  on
autovacuum_analyze_scale_factor 0.1
autovacuum_analyze_threshold    50
autovacuum_freeze_max_age   200000000
autovacuum_max_workers  3
autovacuum_multixact_freeze_max_age 400000000
autovacuum_naptime  60
autovacuum_vacuum_cost_delay    20
autovacuum_vacuum_cost_limit    -1
autovacuum_vacuum_scale_factor  0.2
autovacuum_vacuum_threshold 50

If I execute this query:

SELECT schemaname, relname,
last_vacuum       ,
last_autovacuum   ,
last_analyze      ,
last_autoanalyze
FROM pg_stat_all_tables
where schemaname = 'cdi'

I get this results:

relname last_vacuum         last_autovacuum     last_analyze    last_autoanalyze
aa      11/11/2017 05:56    04/11/2017 03:24    13/11/2017 23:25
bb      09/10/2017 15:01    04/11/2017 03:55    09/10/2017 15:01
cc      13/09/2017 13:23    04/11/2017 03:55    11/11/2017 04:12
dd      14/09/2017 10:27    04/11/2017 03:55    12/11/2017 17:09
ee      14/09/2017 10:32    04/11/2017 03:00
ff      11/10/2017 09:20    04/11/2017 03:48    13/11/2017 22:19
gg      04/11/2017 03:55
hh      04/11/2017 03:55
ii      04/11/2017 03:16    11/11/2017 06:51
ll      04/11/2017 03:16
mm      04/11/2017 03:55
nn      04/11/2017 03:55
oo      04/11/2017 03:55
pp      04/11/2017 03:00

It doesn't appear to me that there is an efficient autovacuum

Can someone tell me a method to make autovacuum happen?

this is my table:

Sequential scans        156
Sequential tuples read  5448762930
Index scans             802121339750
Index tuples fetched    800736733206
Tuples inserted         21907385
Tuples updated          6190434
Tuples deleted          190337
Tuples HOT updated      3958397
Live tuples             59641911
Dead tuples             703334
Heap blocks read        13685226418
Heap blocks hit         786803454925
Index blocks read       5104418623
Index blocks hit        3208275818736
Toast blocks read       0
Toast blocks hit        0
Toast index blocks read 0
Toast index blocks hit  0
Last vacuum             2017-10-11 09:20:22.358935+02
Last autovacuum
Last analyze            2017-11-04 03:48:49.753917+01
Last autoanalyze        2017-11-13 22:19:08.47618+01
Vacuum counter          1
Autovacuum counter      0
Analyze counter         10
Autoanalyze counter     3
Table size              6778 MB
Toast table size        8192 bytes
Indexes size            3969 MB

Autovacuum only runs when necessary:

if too many rows have been changed or deleted sine the last VACUUM run
if there are rows old enough that they should be “frozen” to prevent data loss due to transaction counter wraparound.

See the documentation for details.

If autovacuum never ran on some tables, that could also be because there was no reason for it to run yet.


Sync two MySQL servers data, while changing the Engine in the slave I'm running a MySQL server 5.5.44, and I'd need to sync its data to a new server running MySQL 5.7.13 using master-slave. The idea is to bring both DBs synced until we migrate to the second, newer server, achieving a close to zero downtime.

The issue is not about how to prepare the master-slave. The issue comes when I want to sync the data, but change the engine of many tables in our slave server. Specifically, moving them from MyISAM to InnoDB.

These tables are larger than 1Gb, one being 14Gb, which would lead in a lock for more than 8 hours. My idea was to do any structural optimisation in the slave server, but I'm afraid this might compromise the whole master-slave thing.

Do you know if this is possible?

This should not a problem, provided you do not execute any DDL related to the Storage Engine on the Master.
I have written about this many times

Jun 01, 2011 : What can we do in MySQL 5.0 Replication to address bandwidth concerns?
Nov 16, 2011 : Log MySQL DB changing queries and users
Mar 13, 2012 : https://dba.stackexchange.com/questions/14943/setting-up-mysql-across-a-series-of-production-servers/14945#14945
Aug 14, 2012 : Can I have an InnoDB master and MyISAM slaves with Full-Text for searching?

NOTE ABOUT MySQL 5.5 to 5.7 Replication
DATETIME in MySQL 5.6+ has been expanded to accommodate microseconds. Please change the binlog_format on both Master and Slave to STATEMENT to avert any issue with replication breaking due to DATETIME value (See my old posts mysql replication master 5.5 slave 5.1 error on create database AND enable log_bin_use_v1_row_events in mysql 5.6)


NC Columnstore and delete on batch hope you can give me some pointers on this behavior

I have a NC Columnstore and in the same batch I want to drop this column store and "delete" some information.

But I get this error:
DELETE statement failed because data cannot be updated in a table with a columnstore index. Consider disabling the columnstore index before issuing the DELETE statement, then rebuilding the columnstore index after DELETE is complete.

For what I can image this is due to the compilation throwing the error.

In the end i used the OPTION(RECOMPILE) at the end it worked for the scenario I was facing, but bear in mind that this won't work for parameters.


Foreign Key For Index Column - MYSQL If we have implemented Index on Primary key column in Table1 and on Referenced column in Table2. But we don't have Foreign key on Referenced Column.

Can you please explain is this good to implement foreign key to utilize Index more efficiently

I might be wrong, but I don't think that the MySQL optimizer takes foreign keys into consideration. An index, on the other hand, may be very useful as a support for a foreign key.

However, the main purpose of a foreign key is completely different than what you are asking for. The purpose is to keep the database consistent and maintain referential integrity. When deciding for or against foreign keys, I think this what you should be concidering.


Is it possible to pass in multiple values in the WHEN expression I am trying to update a bunch of rows and their approval status, I have something like this

UPDATE table
SET [Approval] =
CASE
WHEN @approvalStatus = 'Approve' then 'Approved'
WHEN @approvalStatus = 'Reject' then 'Rejected'
END
WHERE [User ID] = @userID

I wanted something like this:

UPDATE table
SET [Approval] =
CASE
WHEN {1} = 'Approve' then 'Approved'
WHEN {1} = 'Reject' then 'Rejected'
END
WHERE [User ID] IN ({0}) AND [Approval] IN ({1})

Is there something like this possible

Edit:

Sample Data:

{ "User ID": "1", "Approval": "Approve", "Name": "John" }
{ "User ID": "2", "Approval: "Reject", "Name": "Bob" }

What I would pass in  {0} would be ('1','2') and for {1} would be ('Approve', 'Reject')

I believe to have written a query that meets your request below:

DECLARE @table TABLE (UserID INT, Approval VARCHAR(10))

INSERT INTO @table
VALUES (1, 'Reject'), (2, 'Approve')

DECLARE @approvalStatus VARCHAR(10)
DECLARE @UserID INT

SET @approvalStatus = 'Approve'
SET @UserID = 2

UPDATE @table
SET Approval = CASE
        WHEN @approvalStatus = 'Approve'
            THEN 'Approved'
        WHEN @approvalStatus = 'Reject'
            THEN 'Rejected'
        END
FROM @table
WHERE Userid = (@UserID)
    AND Approval = (@approvalStatus)

SELECT *
FROM @table

I created a fake variable table and inserted some similar data as your example illustrates and then I have updated the data in the table and I finally do a select from it to show the value has changed.

Here's the MSDN on CASE WHEN, it will have more examples and more information for you to follow.

Notice how I changed your WHERE clause? This is because with a stored procedure it is hard to pass an array as a variable which is what you are attempting. If you want to make it an array you will need to use Table Valued Parameters.

I modified an answer by Nick Chammas on DBA Stack Exchange. They are using TVP's to pass an array. Here is a code example of your situation and how to pass an array into a stored procedure to update your table. Give this a try in a test environment where you can safely create and drop objects without impacting important systems.

-- Using a staging table now, table variables need to be seen when calling the proc.
-- Most of these pieces are one time runs, run the steps 1-4. Afterwards, the code is repeatable to run
-- and you can adjust the parameters you pass to the TVP to see the effects. I have added more sample
-- code to the table so you can see how it interacts with the data.

-- Step (1)
CREATE TABLE Test (UserID INT, Approval VARCHAR(10))

-- Step (2)
-- Creating test records
INSERT INTO Test
VALUES (1, 'Reject'), (2, 'Approve'), (3, 'test'), (4,'test2'), (5,'Approve'),
(6,'Reject')

-- Step (3)
-- This is our TYPE used for the TVP
CREATE TYPE id_list AS TABLE (
    id INT --NOT NULL PRIMARY KEY
    , approvaltwo VARCHAR(10)
    );
GO

-- Step (4)
-- This is a procedure you would use to update data
CREATE PROCEDURE [dbo].[tvp_test] (
    @customer_list id_list READONLY
    )
AS
BEGIN
-- Update the table
    UPDATE test
    SET Approval = CASE
            WHEN approval = 'Approve'
                THEN 'Approved'
            WHEN approval = 'Reject'
                THEN 'Rejected'
            -- This line updates with what was there previously. Without the ELSE, it would null
            -- cells that are not 'Reject' or 'Approve'
            ELSE Approval
            END
    FROM test t
    -- Joining on TVP to filter rows that should be updated
    INNER JOIN @customer_list cl
        ON t.userid = cl.id
            AND t.approval = cl.approvaltwo;
END;
GO

-- The rest of the steps are now repeatable
-- This is where your code would begin now
-- Declare your TVP
DECLARE @customer_list id_list;

-- Insert into the TVP, this is where you pass your array
INSERT INTO @customer_list (id, approvaltwo)
VALUES (1, 'Reject'), (2, 'Approve'), (3,'test');

-- Execute against the TVP and table
EXECUTE [dbo].[tvp_test] @customer_list = @customer_list;
GO

-- View results
SELECT *
FROM Test

One last note is that you may have to adjust your array in code before passing it to the TVP.


How to enable INMEMORY JOIN GROUP in Oracle 12.2 I have read official Oracle documentation on INMEMORY JOIN GROUP and run code from their tutorials. However I unable to successfully create INMEMORY JOIN GROUP.

Installed database is Oracle 12.2
INMEMORY TABLE and INMEMORY COLUMN were created successfully.

Exception message of INMEMORY JOIN GROUP creation below:
Error starting at line : 1 in command -
CREATE INMEMORY JOIN GROUP employees_departments
    (employees(department_id), departments())
Error report -
ORA-00900: invalid SQL statement
00900. 00000 -  "invalid SQL statement"
*Cause:
*Action:

My code is
CREATE INMEMORY JOIN GROUP employees_departments
    (employees(department_id), departments());

Are there any Oracle initialize parameters that should be specified in order to turn on support of INMEMORY JOIN GROUP?
Oracle SQL Developer highlights INMEMORY as incorrect syntax:

No you need also a column for the departments table

CREATE TABLE employees(department_id int)

✓

CREATE TAble departments(department_id int)

✓

CREATE INMEMORY JOIN GROUP employees_departments
    (employees(department_id), departments(department_id));

✓

db<>fiddle here


Connections increasing by 10+ a second with no requests I'm getting no answers from my host, but I suspect their DB servers are compromised.

I was getting some ridiculous page loads for requests to a standard Wordpress installation - upwards of 30 seconds, and always multiples of 30 plus a normal load time, so it might be 32 seconds, or 122 seconds, or 211 seconds for example. This was even for ajax requests for the simplest of tasks.

I then started getting Mysql has gone away errors, sometimes, but not always, in addition to max_connected_users errors.

After querying SHOW STATUS WHERE Variable_name LIKE '%onn%' I noticed that the Connections variable was increasing at the rate of 10 a second.

My question is, is this normal? Would there possibly be hidden host processes that would increase the Connections by this much, constantly, and take up active connections and processing power that limit my access to the DB?

10 Connections per second is rather high.  Still, about 1/4 of production servers I have looked at (WP and non-WP) show a higher value for Connections/Uptime.

Check the web server's log files -- you may find that a hacker is trying to break through your passwords or security flaws in WP.


Does the apply operator use row by row processing? I'm working with 2 of the same datasets. However, one is derived from a temp table and the other is from a derived table (cross apply operator). The derived table takes much longer to process and it is a simple query. I've been told to avoid apply operator because they use row by row processing.

My question(s):

Does the apply operator use row by row processing?
Should I limit my use of apply operators like cursors and trigger statements? (Only use in specific situations)

The APPLY operator is a tabular operator in the T-SQL language. As the language itself, it is declarative and does not indicate in any way what is the physical implementation that the relational engine should be using to retrieve the data. This means that the correlation strategy between the input set and the applied function is not limited to nested loops. This highly depends on the type of tabular function that you are applying to the input set, as some functions support predicate pushdown and/or reordering and others don't.

The APPLY operator per se is not inherently good or bad, so it is not among the things that you should try to avoid in your code.


Long running backups on small databases We normally perform backups across the network to a file share on a different SAN.  This works very well for all but 1 of our database servers. The problem machine is a new server running Windows Server 2012R2 as the OS, and SQL Server 2012 Standard Edition for the database instance.  The network latency between the server and backup share is sub-MS.

The actual backup of the data and log file happen quickly (sub-second), however, it seems to get stuck at 100% completed (when viewed through sp_WhoIsActive) with a BACKUPTHREAD wait type.  If I perform the same backup but to a local drive, it does not experience this problem.

Here is a sample backup command we are running for the MODEL (only 5MBs) database:

USE MASTER
GO

BACKUP DATABASE MODEL
TO  DISK = N'\\<FileShareServer>\SQL_Backup\MODEL.BAK'
WITH  NAME = N'MODEL-Full Database Backup'
    ,NOFORMAT, INIT,  SKIP, NOREWIND, NOUNLOAD,  STATS = 10
    ,CHECKSUM, COPY_ONLY, COMPRESSION;

GO

Here are the stats for the backup, with Trace Flag 3604, 3213 turned on:

Backup/Restore buffer configuration parameters

Memory limit: 36855MB

BufferCount:                7

Sets Of Buffers:            3

MaxTransferSize:            64 KB

Min MaxTransferSize:        64 KB

Total buffer space:         1 MB

Tabular data device count:  1

Fulltext data device count: 0

Filestream device count:    0

TXF device count:           0

Filesystem i/o alignment:   512

Media Buffer count:            7

Media Buffer size:            64KB

Encode Buffer count:           7

10 percent processed.
20 percent processed.
30 percent processed.
40 percent processed.
51 percent processed.
61 percent processed.
71 percent processed.
81 percent processed.
91 percent processed.

Processed 312 pages for database 'MODEL', file 'modeldev' on file 1.
100 percent processed.
Processed 2 pages for database 'MODEL', file 'modellog' on file 1.
-- Up to here happens sub-second as expected
BACKUP DATABASE successfully processed 314 pages in 252.372 seconds (0.009 MB/sec).

I've tried playing around with stripped backups as well as setting the BUFFERCOUNT and MAXTRANSFERSIZE to higher numbers, to no avail.

Thanks in advanced for your input!

Based on Aaron Bertrand's suggestion, I turned on trace flags 3605, 3004, and 3014 and got the following additional information (only pasting in the portion from where it gets to 100% and then sits waiting):

慂正灵慄慴慢敳退邐邐邐邐坖呁啁荈⃬튅襌⑴䱘粉怤ཁ쒔譌䣹솁̘ : Log files done

Backup: Trailing config done

Backup: MBC done

BackupDatabase: Writing history records

Writing backup history records

BACKUP DATABASE successfully processed 314 pages in 251.106 seconds (0.009 MB/sec).
BackupDatabase: Finished

Just to clarify the entries above, starting with, what I'm guessing are Chinese characters, only display once the backup completes.  However, looking in the log file, up to the "Backup: MBC done" entry has the same timestamp as all the previous entries.  "BackupDatabase: Writing history records" has a timestamp 4 minutes later.

Since the additional trace flags allowed you to narrow the delay down to the stage where the process tries to write to msdb, I'm willing to bet one or both of these returns high numbers:

SELECT MAX(row_count), SUM(in_row_reserved_page_count)
  FROM msdb.sys.dm_db_partition_stats
  WHERE [object_id] = OBJECT_ID('msdb.dbo.backupset');

SELECT database_name, [type], c = COUNT(*)
  FROM msdb.dbo.backupset
  GROUP BY database_name, [type]
  ORDER BY c DESC;

You can consider whittling away at some of the data in that table. Of course, only you can know if you have reasons to keep backup history that otherwise should probably be cleaned up in most cases (for example, the first rule I declare to identify junk history data is for databases that no longer exist - but what if you have renamed a database recently?). So please think about each of these rules and only delete backups where the rules make sense to you based on these scenarios. For example:

CREATE TABLE #bs(id INT, ms INT);

-- Databases that no longer exist on this server:
INSERT #bs(id,ms) SELECT backup_set_id, media_set_id
FROM msdb.dbo.backupset
WHERE database_name NOT IN (SELECT name FROM sys.databases);

-- All copy_only backups (because who cares?):
INSERT #bs(id,ms) SELECT backup_set_id, media_set_id
FROM msdb.dbo.backupset
WHERE is_copy_only = 1;

-- All diffs except the most recent:
;WITH x AS
(
  SELECT backup_set_id, media_set_id,
    rn = ROW_NUMBER() OVER (PARTITION BY database_name
      ORDER BY backup_set_id DESC)
  FROM msdb.dbo.backupset
  WHERE [type] = 'I'
  AND backup_finish_date IS NOT NULL
)
INSERT #bs(id,ms) SELECT backup_set_id, media_set_id
FROM x WHERE rn > 1;

...

-- All log backups prior to the most recent full:
INSERT #bs(id,ms) SELECT backup_set_id, media_set_id
FROM msdb.dbo.backupset AS l
WHERE [type] = 'L' AND EXISTS
(
  SELECT 1
    FROM msdb.dbo.backupset AS bs2
    WHERE database_name = l.database_name
    AND [type] = 'D'
    AND backup_finish_date > l.backup_finish_date
);

-- All fulls except the most recent successful non-copy-only full backup:
;WITH x AS
(
  SELECT backup_set_id, media_set_id,
    rn = ROW_NUMBER() OVER (PARTITION BY database_name
      ORDER BY backup_set_id DESC)
  FROM msdb.dbo.backupset
  WHERE [type] = 'D'
  AND is_copy_only = 0
  AND backup_finish_date IS NOT NULL
)
INSERT #bs(id,ms) SELECT backup_set_id, media_set_id
FROM x WHERE rn > 1;

Need to also delete some file and filegroup information:

DELETE msdb.dbo.backupfile
WHERE backup_set_id IN (SELECT id FROM #bs);

DELETE msdb.dbo.backupfilegroup
WHERE backup_set_id IN (SELECT id FROM #bs);

DELETE mf
FROM msdb.dbo.backupmediafamily AS mf
WHERE media_set_id IN (SELECT ms FROM #bs)
AND NOT EXISTS (SELECT 1 FROM msdb.dbo.backupset
  WHERE media_set_id = mf.media_set_id);

DELETE ms
FROM msdb.dbo.backupmediaset AS ms
WHERE media_set_id IN (SELECT ms FROM #bs)
AND NOT EXISTS (SELECT 1 FROM msdb.dbo.backupset
  WHERE media_set_id = ms.media_set_id);

You also need to delete associated restore history:

DELETE msdb.dbo.restorefilegroup WHERE restore_history_id IN
(SELECT restore_history_id FROM msdb.dbo.restorehistory
  WHERE backup_set_id IN (SELECT id FROM #bs));

DELETE msdb.dbo.restorefile WHERE restore_history_id IN
(SELECT restore_history_id FROM msdb.dbo.restorehistory
  WHERE backup_set_id IN (SELECT id FROM #bs));

DELETE msdb.dbo.restorehistory
  WHERE backup_set_id IN (SELECT id FROM #bs);

And then finally the main rows can be deleted without violating any constraints:

DELETE msdb.dbo.backupset
WHERE backup_set_id IN (SELECT id FROM #bs);

DROP TABLE #bs;

This whole process will not likely be fast - I highly recommend doing it during a maintenance window or slow period, and most certainly suspend all backup jobs while doing this.

On Standard Edition there's no real clean way to do this as an online operation, but after you've deleted all those rows, you'll likely benefit most from a full rebuild:

ALTER INDEX ALL ON msdb.dbo.backupset REBUILD;

The above was based partially on the logic included in the system procedure dbo.sp_delete_backuphistory, which leaves out some of the above criteria, has a reputation for locking up the entire set of backup- and restore-related msdb tables, and only allows a singular filter on date (which may not be very helpful depending on a variety of factors, including the percentage of bloat attributed to log backups prior to their most recent fulls, never mind the fact that different databases may be on completely different schedules). You can see where most of my logic came from, though, by looking at the definition for yourself:

EXEC msdb.sys.sp_helptext @objname = N'dbo.sp_delete_backuphistory';


trimming leading characters I'm in oracle and I have a table 'parts' in which have a field 'Description', which contains description for a part.
my query is:

SELECT DESCRIPTION
FROM   PART
WHERE  PAR_ID=123
AND    DESCRIPTION LIKE 'CHARGE BATTERIES%';

it results:

CHARGE BATTERIES
CHARGE BATTERIES_3
CHARGE BATTERIES_4

I want it to only result '3' and remove all leading characters.
I'm familiar with sql trim, substr ans instr functions but don't know hot use these functions.
Any help will be appreciated.

SELECT to_number(replace(DESCRIPTION, rtrim(DESCRIPTION, '0123456789')))
FROM   PART
WHERE  PAR_ID=123
AND    DESCRIPTION LIKE 'CHARGE BATTERIES%';

When there is no number at the end it will result NULL. If you want to have zero then use:

SELECT nvl(to_number(replace(DESCRIPTION, rtrim(DESCRIPTION, '0123456789'))), 0)
FROM   PART
WHERE  PAR_ID=123
AND    DESCRIPTION LIKE 'CHARGE BATTERIES%';


Grant privileges to tables in all (dynamic) schemas We've setup an AWS Database Migration Service which migrates schema and tables from multiple sources.

The rule for migrating from source can be a wildcard on schema, meaning a schema could 'pop up' arbitrarily as it is created in the source.

From all the various granting of privileges I've seen in PostgreSQL, all seem to specify a specific schema, or loops through each existing schema.

Is there not a way to grant certain privileges to every current and future schema in a given database?

Ideally something like this would be great:

ALTER DEFAULT PRIVILEGES FOR ROLE my_role IN SCHEMA "*" GRANT SELECT ON TABLES TO my_role;

I'm aware the super_user role can be granted to a user to make this happen, but that seems awfully dirty for someone who should only have read permissions.

Appreciate the help.

Just omit the IN SCHEMA clause, and it applies to all schemas, so no wildcard capability for schemas is needed.

ALTER DEFAULT PRIVILEGES FOR ROLE my_role GRANT SELECT ON TABLES TO my_role;

Your real problem is likely to be the FOR ROLE clause.  The above is kind of useless, as it applies only to tables (in any schema) created by my_role.  But the creator of tables automatically has select privileges, so this doesn't do anything useful.  Your FOR ROLE clause needs to specify who will be creating the tables (if you omit it, it means the current user).  If you have multiple such creating roles, you have little option but to repeat the ALTER DEFAULT PRIVILEGES for each one.  This is where the wildcard capability is really needed, but sadly does not exist.


Lost DMK encryption password for master database Back story:
My company has TDE encryption enabled for some of our databases. The password that the master key was encrypted by for one of our servers is lost. We do have the backup .key file, however, the password it was encrypted with is lost too.

We also have the certificate backed up, but those passwords are lost as well.

The original backup files (master key, cert) are still in existence and those encryption passwords are available for previous servers these DBs lived on.

Question:

We want to have the actual passwords on file for the certs/keys used in our environment. What is the best solution? - This is a production environment

My company has TDE encryption enabled for some of our databases. The
  password that the master key was encrypted by for one of our servers
  is lost. We do have the backup .key file, however, the password it was
  encrypted with is lost too.

  We also have the certificate backed up, but those passwords are lost
  as well.

So you know the backup cert/DMK files are now useless. I would IMMEDIATELY take new backups and save the passwords. This way you can restore older backup files should anything happen. I would also backup the SMK as that's the only means you have to decrypting the DMK/Cert at this moment.

  The original backup files (master key, cert) are still in existence
  and those encryption passwords are available for previous servers
  these DBs lived on.

I'm assuming the keys haven't been rotated per your comment. If this is the case you are most likely able to restore these to a test server and restore your TDE enabled databases to said test server without issue. If this is the case, you could re-use these keys assuming nothing else is relying on the DMK/Cert in your current instance.

Since TDE works through the SMK (and not by passwords) you're currently "at risk". Sure, it's running but who knows when or if something will happen. I stated before, take new backups.

The most straight forward approach would be to remove TDE from the databases on that instance, remove the cert, and remove the DMK (After backing them up first). Then proceed to create the DMK/Cert/TDE enabled. It's going to be disk and cpu intensive for a bit (depending on the size of database and hardware/disk/etc). Backup the new DMK/Cert with a guarded password.


Recover MySQL ibdata1 file I had mysql running on my VPS. Today it crashed for no good reason!

I tried service mysql start, it gave me start: Job failed to start.

First thing I did was to backup /var/lib/mysql which contained folders corresponding to my databases and these other files:

debian-5.5.flag
ibdata1
ib_logfile0
ib_logfile1
mysql_upgrade_info

I removed /etc/mysql and did sudo apt-get purge mysql-server mysql-common and then installed everything again, the result was the same: start: Job failed to start.

I went to /var/lib/mysql/ and deleted ib* files. I could start mysql then, but some of the tables were problematic: I could see their names in PhpMyAdmin, but when tried to run queries, mysql errored out that the table doesn't exist.

I assume ibdata1 is corrupted! I put innodb_force_recovery = 0 and tried it upto innodb_force_recovery = 6, none of them worked and I got the same error when I tried to start mysql.

I still have the whole /var/lib/mysql backup.

Any ideas why this happened and how I can recover the data and start mysql again?

What You make right - it is backup all files from /var/lib/mysql

all other steps - wrong

Your current error (in PHPMyAdmin with phantom tables) - because You have Your database folder with .frm files for table structure, and empty (new) ibdata.

What You can do now - revert all back (restore files in it original place)
Then You can:
- check error log file, for error message
- start change innodb_force_recovery from 1 to 6


How do I order by NULLS LAST if my database doesn't support it? Select      FullName, Login
FROM        [User]
ORDER BY    FullName desc, login asc

Results

FullName                      Login
----------------------------- ------------------------------
M...... H.........            mh..@...com
A.... H.....                  ad..@...com
NULL                          and..@...com
NULL                          ben..@...com
NULL                          roc..@...com

What I want

FullName                      Login
----------------------------- ------------------------------
A.... H.....                  ad..@...com
M...... H.........            mh..@...com
NULL                          and..@...com
NULL                          ben..@...com
NULL                          roc..@...com

I want the full name in abc order desc and same with login, but I want all nulls to go to the bottom.

There is a (closed) connect item requesting support for NULLS LAST in SQL Server.

A couple of other ways would be

ORDER BY
        CASE WHEN FullName IS NULL THEN 1 ELSE 0 END,
        FullName,
        Login

I prefer this as it doesn't rely on hardcoding a string that it is assumed no legitimate data will sort after. I'd rather not have to consider that on some case sensitive collations z sorts before Z and on others the reverse applies, will that be an issue? Similarly will "Željko Ivanek" sort before "ZZZZZZZ" reliably on all collations? Are there other characters from other languages that might exist in a name and sort after "Z"?

A potentially more efficient solution if there is an index on FullName, Login (as it hopefully will avoid a sort)

CREATE TABLE [User]
  (
     FullName VARCHAR(100),
     Login    VARCHAR(100),
     INDEX IX (FullName,Login) /*Use separate create index if < 2014*/
  );

WITH T
     AS (SELECT 1 AS Grp,
                *
         FROM   [User]
         WHERE  FullName IS NOT NULL
         UNION ALL
         SELECT 2 AS Grp,
                *
         FROM   [User]
         WHERE  FullName IS NULL)
SELECT FullName,
       Login
FROM   T
ORDER  BY Grp,
          FullName,
          Login


psql "invalid client_encoding" error on OS X, PostgreSQL 9.4.5 I'm currently running a OS X Lion Server system which ships with a built-in and not-upgradable PostgreSQL version.
After years of usage I've finnaly decided to leave the built-in version and install an indipendent version. I disabled the built-in installation and downloaded the installer from EDB and followed the wizard. After many issues reguarding encoding and locales, I've finally managed how to setup a DB with no locale and UTF8 encoding. I issued the following command:

initdb -D /path/to/data --no-locale --encoding=UTF8

If I connect using pgAdminIII I get no problems. The command show client_encoding; displays UNICODE as the encoding used by pgAdminIII (the default installation gave me a SQL_ASCII encoding and that's why I run the initdb command).

The problem is that I'm not able to connect to PostgreSQL using psql. Whatever I pass to it, I get the following error:

psql: invalid connection option "client_encoding"

I've searched through the Internet but found nothing that solves my problem (for example issuing env PGCLIENTENCODING=UTF8 and adding client_encoding=UTF8 to postgresql.conf).

otool -L /Library/PostgreSQL/9.4/bin/psql returns:

/Library/PostgreSQL/9.4/bin/psql:
    @loader_path/../lib/libpq.5.dylib (compatibility version 5.0.0, current version 5.7.0)
    @loader_path/../lib/libssl.1.0.0.dylib (compatibility version 1.0.0, current version 1.0.0)
    @loader_path/../lib/libedit.0.dylib (compatibility version 1.0.0, current version 1.48.0)
    /usr/lib/libSystem.B.dylib (compatibility version 1.0.0, current version 169.3.0)

Anyone can help me to figure it out?

Many thanks to all
 Pietro

UPDATE

I forgot to logout and then login after editing the bash profile. The suggestion made by Daniel Vérité was right. I just edited the DYLD_LIBRARY_PATH env variable in /etc/profile in order to make it visible at a global level and not only from the interactive shell.

I added the following line to /etc/profile:

export DYLD_LIBRARY_PATH='/Library/PostgreSQL/9.4/lib'

I hope this helps others.

Daniel, Thank you a lot

client_encoding is a libpq connection option that was added circa 2011 and will not be recognized by older versions.

The error:

  psql: invalid connection option "client_encoding"

comes from dynamically linking a newer psql with an older libpq, for instance the one that ships with the system.

On Mac OS X, this can be worked around by setting DYLD_LIBRARY_PATH (the equivalent of linux's LD_LIBRARY_PATH) to the path of the newer PostgreSQL installation.

Per comments and update, it's confirmed to work when setting this variable globally in /etc/profile


Scope_Identity() returns null after inserting first row in table I have a stored procedure that is used for inserting values in two tables.

The tables have a parent-child relationship i.e. the first table has an identity column, and the second table references the first table.

In the following procedure, I am returning Scope_Identity() value as a column:

ALTER PROCEDURE [dbo].[Insert_Header_Details_Tables]
    (
        @ProcessID [int]=null,
        @FileName [varchar](50)=null,
        @VendorName [varchar](250)=null,
        @LastName [nvarchar](100)=null,
        @FirstName [nvarchar](100)=null,
        @isFirstMSH [bit]
    )
AS
--BEGIN TRANSACTION
-- Insert into Jobs table
IF(@isFirstMSH = 1)
BEGIN
INSERT INTO LabStagingHeader
             ([FileName],
             [VendorName]
            )
    VALUES  (@FileName,
             @VendorName
            )
END
-- Retrieve the automatically @ProcID VALUE from the Header table

SET @ProcessID = SCOPE_IDENTITY()

-- Insert new values into LabStagingDetails table
INSERT INTO LabStagingDetails
            ([ProcessID],
            [LastName],
            [FirstName]
            )
    VALUES (@ProcessID,
            @LastName,
            @FirstName
            )

The issue is when isFirstMSH is false, the ProcessID value is NULL. If isFirstMSH value is false, it should insert the last generated value in table.

If you call the procedure multiple times, those are different scopes, so SCOPE_IDENTITY() is expected to be null. And you need to be careful about concepts - if you call the procedure multiple times, how is the second invocation really going to be sure that "the last generated value" was from the previous invocation from that process, vs. some other concurrent call to the same procedure?

Consider table-valued parameters so that you only have to call the stored procedure once, and you can stop tracking if this call is inserting the "first" row and worrying about multiple users colliding:

CREATE TYPE dbo.TVPLabStagingDetails AS TABLE
(
   FirstName NVARCHAR(100),
   LastName  NVARCHAR(100)
);

Now the procedure becomes:

ALTER PROCEDURE dbo.[Insert_Header_Details_Tables]
    @FileName [varchar](50)    = null,
    @VendorName [varchar](250) = null,
    @Names dbo.TVPLabStagingDetails READONLY
AS
BEGIN
  SET NOCOUNT ON;

  INSERT dbo.LabStagingHeader([FileName], [VendorName])
    SELECT @FileName, @VendorName;

  DECLARE @ProcessID INT = SCOPE_IDENTITY();

  INSERT dbo.LabStagingDetails([ProcessID], [LastName], [FirstName])
    SELECT @ProcessID, LastName, FirstName FROM @Names;
END

If that kind of change is not possible, push back; if that also fails, then you'll need to use an output parameter so that the client side can pass in @ProcessID on subsequent calls. But really that is the least efficient way to do this.

Some other tidbits:

Always specify schema prefix
Use semi-colons to terminate statements
Wrap your procedure body with BEGIN/END and use SET NOCOUNT ON;
Do not use IDENT_CURRENT()


Does SQL Server full restore set the differential base? Differential backups are taken as the difference between the current state, and the last full backup called the "differential base".

Does restoring a full backup reset the differential base? If so, is there a way to avoid that?

The Microsoft docs only say that a backup resets the differential base. My experiments are a little inconclusive, although it seems that restores also reset this value.

I have a list of full backups taken sequentially over time. I'm switching this to a periodic full backup, with interspersed differential backups, eg. one full backup a month, with hourly differentials. However, the existing backup sets are all full backups.

I'm writing a script I want to run on a spare server which will go through the existing sets and sequentially restore them and generate differentials from the backup at the beginning of the month. This won't work if a restore resets the differential base though.

Any pointers or suggestions would be much appreciated. I actually already had something like this working with a binary diff program, but that mysteriously broke this month, so I'm trying to switch to using the built-in differential backups.

Edit: gbn as adequately answered the question of whether the restore resets the differential base, but I'd still like some suggestions on whether it's possible to extract a differential backup if given two full backups.

The differential backup is always associated with the last full backup. It is that simple.

So any differential restore is looking for a certain full restore.

So yes, the base is reset. You can't mix and match.

Personally, I wouldn't have monthly full backups and hourly diffs. The frequencies don't match (example: weekly full, daily diff, hourly log). And I wouldn't use any binary file diff rubbish for my backups because I expect to be able to restore them.


Disable database tables for writes but report writes as successful I manage a database where thousands of GBs of useless log data is created daily.

I can't fix the software that is performing all of this (unnecessary) logging. Is there a way to lock (or disable writes to) this log table so it's never written but at same time it will report the write as successful so it doesn't cause issues with the software?

I know I can just truncate the tables with a cronjob but I'd like to know if there is a better approach?

MySQL, and friends, i.e. MariaDB and Percona, have a rather unusual storage mechanism called the Black Hole engine - it might help with your particular request (see the fiddle here).

For example, if you do this:

CREATE TABLE foo
(
  bar INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  baz VARCHAR (30) NOT NULL
) ENGINE = InnoDB;
Query OK, 0 rows affected (0.07 sec)

The default Engine is InnoDB anyway, but I'm just being explicit for the sake of clarity here.

Then, we input a few records:

mysql> INSERT INTO foo (baz) VALUES ('afadfa'), ('sfasdfa'), ('asdfadf');
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

No problems there! Then, to check we:

mysql> SELECT * FROM foo;
+-----+---------+
| bar | baz     |
+-----+---------+
|   1 | afadfa  |
|   2 | sfasdfa |
|   3 | asdfadf |
+-----+---------+
3 rows in set (0.00 sec)

All's rosy in the garden!

Now, for the interesting part!

mysql> ALTER TABLE foo ENGINE = BLACKHOLE;
Query OK, 3 rows affected (0.10 sec)
Records: 3  Duplicates: 0  Warnings: 0

OK. We continue merrily INSERTing data as follows:

mysql> INSERT INTO foo (baz) VALUES ('xxxxx'), ('yyyyy'), ('zzzzzzz');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

Again, all is fine and dandy. Database is happy, we're happy and, most importantly, third party software is blissfully happy and ignorant of any change in the environment!

BUT!!! Now, when we do this:

mysql> SELECT * FROM foo;
Empty set (0.00 sec)

Nada! Zilch! Rien! Nought...

Our data has met the Black Hole's event horizon from which nothing can escape! You can put as much data in as you like - it's like sending a file to /dev/null in *nix!

You asked:

  Is there a way to lock this log table so it's never written but at
  same time it will report the write as successful so it doesn't cause
  issues with the software?

and now you have (see above) outwardly normal behaviour on the INSERT:

Query OK, 3 rows affected (0.00 sec)
    Records: 3  Duplicates: 0  Warnings: 0

So, we're golden! To all intents and purposes, the data being sent to the server is being faithfully stored, except it's not!

To the software writing these logs, it appears that all is hunky-dory but nothing gets written - it's really quite nifty.

This looks as if it was made for your requirement. In MariaDB, you need to load a plugin as explained here, but it seems pretty straightforward. From here:

  the application will continue to function with no errors. But since the
  data is never actually written, all the extra I/O is gone.

Just a word of warning! I don't know what your software is that is writing all this log data to the server, but somebody, somewhere, sometime thought it was worthy of saving. Just make sure you're not throwing the baby out with the bathwater!

Also, if you have a support contract for this pesky software, you might want to check to see if you're potentially invalidating that contact by doing this?

As a final note, if you try something like this on the Black Hole table:

INSERT INTO foo (baz) VALUES (23, 'asdfds'), (33), (43, 'adsfd');

Result:

Column count doesn't match value count at row 1

So, even the errors are authentic, and your software will behave as expected if there's an error and will continue to (all outward appearances) be functioning normally if something like this happens.

You will notice that the fiddle is for MySQL 8 and not MariaDB. With MariaDB (on this fiddle) I get a permission denied error if I try to run

ALTER TABLE foo ENGINE = BLACKHOLE;

I get message:

Unknown storage engine 'BLACKHOLE'

This is to be expected - from the MariaDB link:

  The BLACKHOLE storage engine was installed by default until MariaDB
  10.0. In MariaDB 10.1 and later, the storage engine's plugin will have to be installed.

Attempts to change the engine type and to install the plugin both failed (hardly suprising on a public-facing server).

INSTALL SONAME 'ha_blackhole';

gives:

INSERT command denied to user
'fiddle_PIIYJABQHVJRTTYOTJLC'@'localhost' for table 'plugin'


MySQL server stops accepting connections partially ("gone away" error) Hope someone can help me debug this issue.
There is a MySQL server 5.6.22 running on OS X 10.2. The load isn't too heavy. All databases are stored in InnoDB. From time to time it stop accepting connections from PHP applications and said applications return with a 2006 "gone away" error. Both PHP and MySQL are on the same machine. External connections are not allowed.

It works for a while after restart and then some arbitrary amount of hours later it starts denying some connections. If I keep it running like that some time later the ratio of denied vs accepted connections grows to the point of the server becoming unusable.

Connecting via the command line works. Nothing in the error logs (warning mode turned on). Restarting the server helps. "FLUSH TABLES" helps equally good. What's strange is if I issue "FLUSH TABLES tb_name" it suddenly starts denying all connections via PHP (not just some of them) until I issue "FLUSH TABLES" without a db name. I tried flushing every table sequentially by name but it doesn't help.

None of the suggestions from MySQL documentation or tinkering with various related variables helped.

This started happening when the OS was 10.9 and I think MySQL server got upgraded to a newer version at some point, although I am not 100% certain if that was the thing that introduced the unstable behavior.

I tried looking at global status, current connections et cetera - nothing out of the ordinary. I.e. the server doesn't get flooded with connections that don't get closed for example and I set a very short timeout for it anyway. It also doesn't have too many open files. The system is also mostly idling, lots of available CPU time and memory.

Any suggestions on where to look to get the root of the problem? It's not nice that the logs don't show anything and basically the only clue I have at the moment is that "FLUSH TABLES" works its magic, for whatever reason. If only I could break that command down to bits and execute them separately to see what part alleviates the problem...

Any suggestions on where to start looking?

So the answer is that really you should only use MariaDB on OSX. Neither Percona or official MySQL distribution will handle the gone away error which arises due to a different mechanism of file locking. All detailed here: https://bugs.mysql.com/bug.php?id=79125

MySQL guys are reluctant to issue a fix but apparently MariaDB had this fixed for a long time. I use this in production and never had a problem since. And just in case, I didn't use ports or brew to install it, simply compiled from source after installing cmake. Hope this helps someone.


sqlplus connect without typing the @local_SID when only have one instance Every time I want to connect with a user in Sqlplus I have to specify the database|sid name too,

SQL>connect scott/tiger@orcl

I have installed another database for other purpose but I removed it.
Before the installation of second database I could connect without using @orcl but after I removed it I have to connect that way I said.
How can I fix this issue?

What OS are you using?

Check what the environment ORACLE_SID is set to.  Change it to the existing one and you won't be needing to specify the connection string anymore.


Oracle: Not able to get value from All_Synonyms in PL/SQL Dabatase: Oracle

User: Not dba

In my oracle database, im able to do the following:

SELECT * FROM ALL_SYNONYMS

But when i do it in pl/sql, it is able to compile and execute, but the dbms.putline prints nothing:

CREATE PROCEDURE PROCEDURE_1 AS
  strName VARCHAR2(50);
  CURSOR C_NAME IS SELECT SYNONYM_NAME FROM ALL_SYNONYMS WHERE TABLE_OWNER = 'USER1';

BEGIN

   FOR R_NAME in C_NAME
   LOOP
     strName := R_NAME.SYNONYM_NAME;
     dbms_output.put_line(strName);        <-----
   END LOOP;

END PROCEDURE_1

Do we need to grant any role to a non-dba user for All_Synonyms table?

You shouldn't need any special privilege to view the ALL_* views.

If the procedure is owned by and is running as USER1 then it could be that USER1 has not been granted access to any synonyms.

If the procedure is owned by a user other than USER1 it could be that that user hasn't been granted access to any synonyms.

It all comes down to who's credentials are actually being used to run the procedure - yours, USER1, procedure owner - and what synonyms or grants to synonyms that user has.


Extract time portion of Datetime2(7) in hhmmssfff format I need to extract the time portion of a datetime2(7) column in hhmmssfff format and I am doing it like this:

DECLARE @mdate AS DATETIME2(7)

SET @mdate = '2012-03-15 10:13:27.5437431'

SELECT LEFT(REPLACE(REPLACE(CONVERT(VARCHAR ,@mdate ,114),':',''),'.',''),9)

Is there a better approach than doing this ugly replaces/convert/left?

I need this to join to a DimTime dimension whose key is in hhmmssfff format.

Please don't use varchar without length.
Please use semi-colons as statement terminators.
I don't think there is a more covert way to do this. Here is how I would do it (14 characters shorter, ignoring the semi-colon):

DECLARE @mdate AS DATETIME2(7);

SET @mdate = '2012-03-15 10:13:27.5437431';

SELECT REPLACE(REPLACE(CONVERT(TIME(3),@mdate),'.',''),':','');


Renaming Sql Server database unusual result This is some behaviour I observed from renaming databases. What is the cause of this? The example is simplified for the purposes of this question.

CREATE DATABASE [Database_A]
GO
CREATE DATABASE [Database_B]
GO
CREATE DATABASE [Database_B_Copy]
GO
-- create a table for testing purposes
USE [Database_B]
GO
CREATE TABLE [dbo].[Table_1](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Column1] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_Table_1] PRIMARY KEY CLUSTERED
(
    [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT INTO [Database_B].[dbo].[Table_1] (Column1) values ('[Database_B]')
GO
-- create an copy of the table with the same schema
USE [Database_B_Copy]
GO
CREATE TABLE [dbo].[Table_1](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Column1] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_Table_1] PRIMARY KEY CLUSTERED
(
    [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT INTO [Database_B_Copy].[dbo].[Table_1] (Column1) values ('[Database_B_Copy]')
GO
-- create a stored procedure to demonstrate the problem
USE [Database_A]
GO
CREATE PROCEDURE [dbo].[TestSP]
AS
BEGIN
    SELECT * FROM Database_B.dbo.Table_1
END
GO

[1]. when I run the stored procedure TestSP the result as expected is the contents of DatabaseB Table_1.

ID      Column1
1       [Database_B]

[2]. Renaming Database_B to Database_B_Original will cause this error as would be expected:

Msg 208, Level 16, State 1, Procedure TestSP, Line 4
Invalid object name 'Database_B.dbo.Table_1'.

[3]. Taking Database_B_Original offline, the error from the stored procedure changes to:

Msg 942, Level 14, State 4, Procedure TestSP, Line 4
Database 'Database_B_Original' cannot be opened because it is offline.

[4]. I then renamed Database_B_Copy to Database_B. I would have expected the stored procedure to work since the a database named Database_B was available. However the error remains the same.

Finally if I restart Sql Server this magically fixes the problem and the output is, as expected:

ID      Column1
1       [Database_B_Copy]

Is SQL Server caching the tables and database name information incorrectly? Is there a way to force Sql Server to refresh the information?

I would like to gain a better understanding of what the underlying cause is for this issue and whether it's by-design or an error with sql server.

What SQL Server is doing is, it is caching the query plan for

 exec [Database_A].[dbo].[TestSP]

This can be found using :

SELECT
    usecounts, cacheobjtype, objtype, text
FROM
    sys.dm_exec_cached_plans
CROSS APPLY
    sys.dm_exec_sql_text(plan_handle)
WHERE
    usecounts > 1 AND text LIKE '%TestSP%'
ORDER BY
    usecounts DESC;
GO

When you execute your stored procedure during step 1, 2 or 3, it gets cached in the procedure cache.

Hence during your 4th step, it throws an error.

If you do a dbcc freeproccache, and then rerun the SP exec [Database_A].[dbo].[TestSP], it will work.

As @Aaron pointed out, it will work using EXEC sys.sp_refreshsqlmodule 'Database_A.dbo.TestSP' or recompiling the SP sp_recompile TestSP  as well.

Ref: http://technet.microsoft.com/en-us/library/bb326754(v=sql.105).aspx

There is no need to restart SQL Server.

Note: Free ProcCache will flush out all the query plans, so its better to use sys.sp_refreshsqlmodule or sp_recompile.


Strange issue with MySql 5.6 replication I have a strange issue with MySql 5.6 Master-Master replication. I'm not sure if its entirely a replication issue or a client issue. We have two MySql databases on two different RHEL servers. We have setup a common schema for them and enabled schema level replication. The replication works fine when issuing any DML using the command line utilities or NaviCat client on either Master. But it does not seem to replicate the data when using SQL Developer or MySql WorkBench clients.

Here is the server information:

Linux "Hostname" 2.6.32-431.3.1.el6.x86_64 #1 SMP Fri Dec 13 06:58:20 EST 2013 x86_64 x86_64 x86_64 GNU/Linux

Here is the my.cnf file

[client]
#password       = your_password
port=3306
socket=./mysql.sock

# Here follows entries for some specific programs

# The MySQL server
[mysqld]
port=3306
socket=./mysql.sock
#skip-locking
key_buffer_size = 16M
max_allowed_packet = 1M
table_open_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
default-storage-engine=InnoDB
lower_case_table_names=1
server-id = 1

replicate-same-server-id = 0
auto-increment-increment = 2
auto-increment-offset = 1
log-bin = <>mysql-bin.log
log-bin-index = <>/mysql-bin.index
log-slave-updates
replicate-do-db=schema_replicate
binlog-do-db=schema_replicate
replicate-do-table=schema_replicate.ref_tbl
relay-log = <>/relay-bin
relay-log-index = <>/relay-bin.index
relay-log-info-file = <>/relay-bin.info
slave-skip-errors = 1062

# binary logging format - mixed recommended
#binlog_format=mixed
# required unique id between 1 and 2^32 - 1
# defaults to 1 if master-host is not set
# but will not function as a master if omitted
server-id       = 1

# The number of seconds the server waits for activity on a noninteractive
# connection before closing it. Range: 1-2147483.  2073600 is 24 days, close
# to maximum
wait_timeout=2073600

# Replication Slave (comment out master section to use this)
#
# To configure this host as a replication slave, you can choose between
# two methods :

innodb_data_file_path = ibdata1:10M:autoextend

innodb_buffer_pool_size = 48M
innodb_additional_mem_pool_size = 2M

innodb_log_file_size = 16M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates

[myisamchk]
key_buffer_size = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout

Any help is appreciated.

Activate the general log on both servers at runtime (beware of doing it when you have a lot of load, as that will make all your queries slower):

SET GLOBAL general_log = 1;

You will find it on datadir/hostname.log, or you can change its location with the general_log_file parameter.

There, all connections and queries sent to the server will be logged. Compare what NaviCat and Workbench are doing to identify a possible problem. You can also check the contents of the binary logs with the utility mysqlbinlog and the changes applied with the commands SHOW MASTER STATUS and SHOW SLAVE STATUS\G on each server, making sure that the replication is not broken (very common in master-master replications).

Remember to disable the general log after you are done:

SET GLOBAL general_log = 0;


Scenarios in which you might need to restore large number of databases at once? I've seen several online sources mentioning that you should always have a restore script automated for the possible need to restore a large number of databases at once and quickly get back up and running to deliver on your SLA.

However, considering I'm running synchronous mirroring in two availability zones, I'm struggling to find a scenario in which I would need to do mass restores on a large number of databases where scripting a restore automation solution would be necessary.

Can anyone point to me to realistic scenario in which you'd need the capability to do a mass number of restores in a scripted manner?

So you've got two zones. Let's say zone one floods or burns... it's completely gone. All the hardware is destroyed, and the old site isn't even usable. It could be weeks putting it back together. Thankfully, you're still online thanks to zone two.

Of course, you might try something like short-term leasing servers to get redundancy back while you put together your real new production facility, but really the time spent getting those online is a distraction from your main task, which should be getting a new primary facility up to snuff as quickly as possible. If you do attempt the temporary facility, having the restore scripts will greatly aid getting the temporary facility online quickly, allowing you to get back to your main task.

If you don't opt for the temporary facility, how capable is your zone two site really? I hear all the time about failover equipment that's really just retired equipment from from the primary site. This often means equipment that is older, which raises concerns about both performance and reliability. How long do you really want to leave that facility running as your only data center? Are you taking good backups while this site runs as primary?

In short, wouldn't you like the setup process for your new site to be as quick, reliable, and well-understood as possible? The longer it takes to get the main facility back up and running, the more likely it is for something to also go wrong at your second facility.

Of course, this is just one scenario. It's the big scary total destruction scenario the seems to unlikely to ever actually happen for you, and maybe even that's true. But this kind of things plays out on a smaller scale all of the time. The ability to do quick, reliable, scripted restores for your entire data center for the big emergencies implies the ability to also do quick, reliable, scripted restores for the small emergencies. And that's a good thing.


separate between rows DECLARE @yourtable TABLE (id int, [name] varchar(100), category_sort varchar(100));
INSERT @yourtable VALUES
(1, 'Campaign1','Beverage'),
(2, 'Campaign2','Beverage'),
(3, 'Campaign3','Food'),
(4, 'Campaign4','Beverage'),
(5, 'Campaign5','Hot Drinks'),
(6, 'Campaign6','Advertizing'),
(7, 'Campaign7','Marketing');

WITH x AS
(
SELECT *, rn = ROW_NUMBER() OVER (PARTITION BY category_sort ORDER BY id)
FROM @yourtable
)
SELECT id, name, category_sort
FROM x
ORDER BY rn, category_sort;

Now when i run the above code it is return :

6 Campaign6 Advertizing
1 Campaign1 Beverage
3 Campaign3 Food
5 Campaign5 Hot Drinks
7 Campaign7 Marketing
2 Campaign2 Beverage
4 Campaign4 Beverage

But actually what i need is :

6 Campaign6 Advertizing
1 Campaign1 Beverage
3 Campaign3 Food
5 Campaign5 Hot Drinks
2 Campaign2 Beverage
7 Campaign7 Marketing
4 Campaign4 Beverage

This will work I think:

WITH x AS
(
    SELECT *, rn = ROW_NUMBER() OVER (ORDER BY category_sort, id) - 1,
              halfsize = (1 + COUNT(*) OVER ()) / 2
    FROM @yourtable
)
SELECT id, name, category_sort
FROM x
ORDER BY rn % halfsize, rn ;

Test at dbfiddle.uk:

id | name      | category_sort
-: | :-------- | :------------
 6 | Campaign6 | Advertizing
 3 | Campaign3 | Food
 1 | Campaign1 | Beverage
 5 | Campaign5 | Hot Drinks
 2 | Campaign2 | Beverage
 7 | Campaign7 | Marketing
 4 | Campaign4 | Beverage


Script to find the list of stored procedures in all databases I need to pull out the list of stored procedures which are available in my instance. I used the following T-SQL statement to get the stored procedures in a given database.

select *
from MyDatabase.information_schema.routines
where routine_type = 'Procedure'

Is there is any script to obtain the all stored procedures or to check the database name of the stored procedure by using the stored procedure name?

You have to iterate through all databases to do that. If you're adventurous enough, you could use the undocumented stored procedure sp_MSForEachDB, which is buggy and unreliable.

Another possibility is using one of its replacements: Aaron Bertrand's or my humble attempt.

Yet another possibility is using a cursor to loop through all databases:

USE master;

DECLARE @name sysname;
DECLARE @sql nvarchar(max) = '
    SELECT
        DB_NAME() AS [database_name],
        OBJECT_SCHEMA_NAME(object_id) AS [schema_name],
        name AS [procedure_name]
    FROM sys.procedures
';
DECLARE @theSQL nvarchar(max);

DECLARE @results TABLE (
    [database_name] sysname,
    [schema_name] sysname,
    [procedure_name] sysname
);

DECLARE dbs CURSOR STATIC LOCAL FORWARD_ONLY READ_ONLY
FOR
SELECT name
FROM sys.databases;
-- you may want to exclude system databases here
-- WHERE name NOT IN ('master', 'model', 'msdb', 'tempdb', 'distribution')

OPEN dbs;
FETCH NEXT FROM dbs INTO @name;

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @theSQL = 'EXEC ' + QUOTENAME(@name) + '.sys.sp_executesql @sql';

    INSERT @results
    EXEC sys.sp_executesql @theSQL, N'@sql nvarchar(max)', @sql

    FETCH NEXT FROM dbs INTO @name;
END

CLOSE dbs;
DEALLOCATE dbs;

SELECT *
FROM @results;


MongoDB terminates when it runs out of memory I have the following configuration:

a host machine that runs three docker containers:

MongoDB
Redis
A program using the previous two containers to store data

Both Redis and MongoDB are used to store huge amounts of data. I know Redis needs to keep all its data in RAM and I am fine with this. Unfortunately, what happens is that mongo starts taking up a lot of RAM and as soon as the host RAM is full (we're talking about 32GB here), either mongo or Redis crashes.

I have read the following previous questions about this:

Limit MongoDB RAM Usage: apparently most RAM is used up by the WiredTiger cache
MongoDB limit memory: here apparently the problem was log data
Limit the RAM memory usage in MongoDB: here they suggest to limit mongo's memory so that it uses a smaller amount of memory for its cache/logs/data
MongoDB using too much memory: here they say it's WiredTiger caching system which tends to use as much RAM as possible to provide faster access. They also state it's completely okay to limit the WiredTiger cache size, since it handles I/O operations pretty efficiently
Is there any option to limit mongodb memory usage?: caching again, they also add MongoDB uses the LRU (Least Recently Used) cache algorithm to determine which "pages" to release, you will find some more information in these two questions
MongoDB index/RAM relationship: quote: MongoDB keeps what it can of the indexes in RAM. They'll be swaped out on an LRU basis. You'll often see documentation that suggests you should keep your "working set" in memory: if the portions of index you're actually accessing fit in memory, you'll be fine.
how to release the caching which is used by MongoDB?: same answer as in 5.

Now what I appear to understand from all these answers is that:

For faster access it would be better for mongo to fit all indices in RAM. However, in my case, I am fine with indices partially residing on disk as I have a quite fast SSD.
RAM is mostly used for caching by mongo.

Considering this, I was expecting mongo to try and use as much RAM space as possible but being able to function also with few RAM space and fetching most things from disk. However, I limited mongo Docker container's memory (to 8GB for instance), by using --memory and --memory-swap, but instead of fetching stuff from disk, mongo just crashed as soon as it ran out of memory.

How can I force mongo to use only the available memory and to fetch from disk everything that does not fit into memory?

As per MongoDB BOL Here Changed in version 3.4: Values can range from 256MB to 10TB and can be a float. In addition, the default value has also changed.

Starting in 3.4, the WiredTiger internal cache, by default, will use the larger of either:

50% of RAM minus 1 GB, or
256 MB.

With WiredTiger, MongoDB utilizes both the WiredTiger internal cache and the filesystem cache.

Via the filesystem cache, MongoDB automatically uses all free memory that is not used by the WiredTiger cache or by other processes.

The storage.wiredTiger.engineConfig.cacheSizeGB limits the size of the WiredTiger internal cache. The operating system will use the available free memory for filesystem cache, which allows the compressed MongoDB data files to stay in memory. In addition, the operating system will use any free RAM to buffer file system blocks and file system cache.

To accommodate the additional consumers of RAM, you may have to decrease WiredTiger internal cache size.

For further your ref WiredTiger Storage Engine and Configuration File Options


mysql replication: replicate all but one table to different database on different server I got database1 on server1 and database2 on server2.

Now I need to replicate a single table from database1 to database2

Now I thought that this would work:

  replicate-ignore-table  = %
  replicate-do-table      = table
  replicate-rewrite-db    = "database1->database2"

but this does not seem to work as mysql does not accept % symbol with replicate-ignore-table directive. any ideas ?

EDIT:

I have tried :

  replicate-ignore-table  = database1.%
  replicate-do-table      = database1.table
  replicate-rewrite-db    = "database1->database2"

Now mysql is starting, but table is not being replicated.

So I had some lunch and figured it out !

  replicate-do-table      = database2.table
  replicate-rewrite-db    = "database1->database2"