Why am I seeing high execution counts for the system stored procedure sys.sp_columns_managed We are seeing high wait times during business hours on one of our databases. Looking at SolarWinds I am finding that the system stored procedure 'sys.sp_columns_managed' is being executed more than 300,000 times per hour (according to SolarWinds). Has anyone else ever experienced this? Does anyone know what can be done to stop this? Or, am I not seeing things correctly in SolarWinds?

This script below shows you the execution count of the stored procedures that are currently in your plan cache.

set transaction isolation level read uncommitted

SELECT DB_NAME(st.dbid) DBName
      ,OBJECT_SCHEMA_NAME(st.objectid,dbid) SchemaName
      ,OBJECT_NAME(st.objectid,dbid) StoredProcedure
      ,max(cp.usecounts) Execution_count
 FROM sys.dm_exec_cached_plans cp
         CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
 where DB_NAME(st.dbid) is not null and cp.objtype = 'proc'
   group by cp.plan_handle, DB_NAME(st.dbid),
            OBJECT_SCHEMA_NAME(objectid,st.dbid),
   OBJECT_NAME(objectid,st.dbid)
 order by max(cp.usecounts) desc

That would be an starting point, if you could see the procedure there and from which database it is fired.

Then have a look who and what is using that particular database\calling that procedure.

here you can find the code of the sys.sp_columns_managed stored procedure.


How to configure SSL mongodb connection in yml file spring boot? I was trying to setup ssl mongodb connection from spring boot but I couldn't able to find a way. How can I add pem and ca file to below connection?

Please note I am using replica set

data:
     mongodb:
         authentication-database: admin
         database: <database>
         host: <host>
         password: <password>
         port: <port>
         username: <username>

You can try a configuration like the one below;

server:
    ssl:
        key-store: classpath:keystore.jks
        key-store-password: secret
        key-password: another-secret
data:
     mongodb:
        authentication-database: admin
        database: <database>
        host: <host>
        password: <password>
        port: <port>
        username: <username>
        ssl-enabled: true


Is there a way to view the body / contents of a system function in sql server 2008 For the sake of leaning and exploring, I'd like to look at how some of the system functions are built. Is there a way to do this in sql server 2008 or R2?

I've tried sp_helptext 'Sum' but I get an error saying that the resource cannot be found.

I don't think Sum is stored as a T-SQL function in your system that you could "peek" into. That's most likely code deep inside the SQL Server core.

As a proof - you can e.g. get the body of other system stored functions using sp_helptext:

sp_helptext 'sys.fn_isrolemember'

That works just fine - so that leads me to believe that Sum isn't a stored function as such.


How can I get all of a few users' roles? I need to see all of a user's roles, including the roles inherited from a role.

Consider for example, this arrangement:

CREATE USER TEMPUSER1 IDENTIFIED BY "TEMPUSER1" ACCOUNT LOCK;
CREATE USER TEMPUSER2 IDENTIFIED BY "TEMPUSER2" ACCOUNT LOCK;

CREATE ROLE TEMP_ROLE1;
CREATE ROLE TEMP_ROLE2;
CREATE ROLE TEMP_ROLE3;
CREATE ROLE TEMP_ROLE4;
CREATE ROLE TEMP_ROLE5;

GRANT TEMP_ROLE1 TO TEMP_ROLE3;
GRANT TEMP_ROLE2 TO TEMP_ROLE3;
GRANT TEMP_ROLE3 TO TEMP_ROLE4;
GRANT TEMP_ROLE3 TO TEMP_ROLE5;
GRANT TEMP_ROLE4 TO TEMP_ROLE6;
GRANT TEMP_ROLE5 TO TEMP_ROLE6;

GRANT TEMP_ROLE2 TO TEMPUSER1;
GRANT TEMP_ROLE4 TO TEMPUSER1;
GRANT TEMP_ROLE6 TO TEMPUSER1;
GRANT TEMP_ROLE2 TO TEMPUSER2;

Then I want the result

GRANTED_USER   | GRANTED_ROLE
---------------+---------------
TEMPUSER1      | TEMP_ROLE1
TEMPUSER1      | TEMP_ROLE2
TEMPUSER1      | TEMP_ROLE3
TEMPUSER1      | TEMP_ROLE4
TEMPUSER1      | TEMP_ROLE5
TEMPUSER2      | TEMP_ROLE1
TEMPUSER2      | TEMP_ROLE2

I'm using Oracle 11.2g, but I also want answers for future versions of Oracle. I'd like for this question to remain relevant if Oracle introduces new features that make this easier (like a view that just gives this to us straight up without writing our own queries).

Explanation

The view that tells us about GRANTed roles is DBA_ROLE_PRIVS. (There's also USER_ROLE_PRIVS, but it only shows roles granted to the current user.) However, this view does not give us all the inherited roles. Following through the user's roles recursively requires a "hierarchical" query, or more simply known as a recursive query.

Logically, we want to start with roles granted to the user directly. This means that our START WITH clause should ensure that GRANTEE = 'TEMPUSER1'.
Then we want to look at rows where the GRANTEE is one of the roles granted to the user of interest so far. This means our CONNECT BY clause should ensure that PRIOR GRANTED_ROLE = GRANTEE.
To carry through the actual user through to the rows in our result set, we need to include CONNECT_BY_ROOT GRANTEE, which will give us the GRANTEE from the starting rows (and we started with our user of interest).
Last, it's possible for a user to inherit a role through two different roles, as exemplified with how TEMP_ROLE6 inherits TEMP_ROLE3. This means we'll need some kind of DISTINCT or GROUP BY.

The query

Assembling all that into a query gives us:

SELECT DISTINCT CONNECT_BY_ROOT GRANTEE AS GRANTED_USER, GRANTED_ROLE
FROM DBA_ROLE_PRIVS
START WITH GRANTEE IN ('TEMPUSER1', 'TEMPUSER2')
CONNECT BY GRANTEE = PRIOR GRANTED_ROLE
ORDER BY GRANTED_USER, GRANTED_ROLE
;

This gives the exact result desired.

Bonus info: Source of role

Here's an alternate query that includes some extra info about how the user got the role:

SELECT GRANTED_USER, GRANTED_ROLE, LISTAGG(DIRECT_GRANTEE, ',') WITHIN GROUP (ORDER BY DIRECT_GRANTEE) AS ROLE_SOURCE
FROM (
    SELECT DISTINCT CONNECT_BY_ROOT GRANTEE AS GRANTED_USER, GRANTED_ROLE, REPLACE(GRANTEE, CONNECT_BY_ROOT GRANTEE, 'Direct GRANT') AS DIRECT_GRANTEE
    FROM DBA_ROLE_PRIVS
    START WITH GRANTEE IN ('TEMPUSER1', 'TEMPUSER2')
    CONNECT BY GRANTEE = PRIOR GRANTED_ROLE
) USER_ROLES
GROUP BY GRANTED_USER, GRANTED_ROLE
ORDER BY GRANTED_USER, GRANTED_ROLE
;

Notice that the query shows 'Direct GRANT' when the role is granted to the user directly. This stands out a bit better than just showing the user name in the list.

Notice that we still need the DISTINCT in the subquery. This is necessary because TEMPUSER1 gets TEMP_ROLE4 twice, once from a direct GRANT and once from TEMP_ROLE6. Without the DISTINCT, TEMP_ROLE4 would show up twice as the source of TEMP_ROLE3.

The result looks like this:

GRANTED_USER  | GRANTED_ROLE  | ROLE_SOURCE
--------------+---------------+-----------------------------------
TEMPUSER1     | TEMP_ROLE1    | TEMP_ROLE2
TEMPUSER1     | TEMP_ROLE2    | Direct GRANT
TEMPUSER1     | TEMP_ROLE3    | TEMP_ROLE4,TEMP_ROLE5
TEMPUSER1     | TEMP_ROLE4    | Direct GRANT,TEMP_ROLE6
TEMPUSER1     | TEMP_ROLE5    | TEMP_ROLE6
TEMPUSER1     | TEMP_ROLE6    | Direct GRANT
TEMPUSER2     | TEMP_ROLE1    | TEMP_ROLE2
TEMPUSER2     | TEMP_ROLE2    | Direct GRANT

For all users

These queries can be easily adapter to show roles for all the users in the database. All that's needed is to change the START WITH clause to:

START WITH GRANTEE IN (SELECT USERNAME FROM DBA_USERS)

For roles

The queries work equally well if you want to view the roles granted to a particular role. You need only specify the role name as the starting GRANTEE. For example,

START WITH GRANTEE = 'TEMP_ROLE6'

Or for all roles:

START WITH GRANTEE IN (SELECT ROLE FROM DBA_ROLES)


Can I simply copy mdf and log files into the data folder to add a new database? I have a copy of the mdf and log files of a database. The database is a MSSQL 2012 database. To use it, I did the following steps (a bit by chance actually, because I was trying to copy a mdf and log file out of the data directory first, and it wouldn't let me while the server was using them):

I stopped the MSSQL server process in Windows
Copied them into the DATA directory.
Then I started the MSSQL servcer process again.

In SQL server management studio, the new database appeared. I thought I might have needed to attach it.

Does MSSQL server automatically attach all mdf's in the data directory? I'm worried it is not working as it should, even though it looks like it.

Does MSSQL server automatically attach all mdf's in the data directory?

No it does not attach a new mdf and ldf automatically.

Instead of fiddling around with stopping sql service and copying around mdf and ldf files, I suggest you take a look at backup and restore commands.

backup database foo
to disk = 'd:\backup\foo_full.bak'
with init, stats =10, compression

-- now restore the database with move and recovery

restore database foo
from disk = 'd:\backup\foo_full.bak' -- backup location
with recovery, stats =10,
move 'logicalName' to 'M:\data\foo.mdf', -- mdf location
move 'logicalName_log' to 'L:\log\foo_log.ldf' -- ldf location


Storing a transaction sensitive constant in Firebird I'd like to store a number in my Firebird database that indicates the revision of its structure. The number is supposed to be updated only when the structure has successfully been altered by some migration script. Therefore the storage mechanism needs to be transaction sensitive, which is why I cannot use a generator. The alternative I considered was to define a stored procedure which returns the number, and alter its definition in a transaction, but that as well seems to survive a rollback. What options do I have, not using a table?

I think you can use Database trigger which fires on TRANSACTION COMMIT. However, you need to react only to those commits caused by the "upgrade script" so you need to set up an flag to indicate it. For that you could set an context variable in USER_TRANSACTION namespace. Ie your script would look like this

RDB$SET_CONTEXT('USER_TRANSACTION', 'IncrementVersion', 1);
-- other statements
...
-- in the end of the script commit
COMMIT WORK;

Now the database trigger can check for the IncrementVersion context variable and act accordingly

CREATE TRIGGER IncrementDBVersion
ACTIVE ON TRANSACTION COMMIT
AS
BEGIN
  IF(RDB$GET_CONTEXT('USER_TRANSACTION', 'IncrementVersion') IS NOT NULL)THEN BEGIN
     -- increment the version number
     GEN_ID(GEN_DB_Version, 1);
     -- just in case clear the context variable
     RDB$SET_CONTEXT('USER_TRANSACTION', 'IncrementVersion', NULL);
  END
END

Here trigger uses generator (aka sequence) named "GEN_DB_Version" to "store" the version number.

If you use COMMIT WORK statements in your script you have to make the system more complicated, either set up additional flags to ignore those commits in the middle of the script or set the flag up right before final commit or perhaps some third way - it all depends on what "side effects" you want to use.


What is the best identifier for a UserID?  64 bit integers, UUID V5,  or 64 character SHA256 UID? I wish to future-proof my application.  What is the best type of identifier to use?

I am considering:

64 bit Integer
UUID
64 character SHA256

I am not developing a distributed system, so is using a UUID necessary?

Also, How good are INTEGER joins vs VARCHAR joins?

Finally, is storing the username (unique) and message data in the same MySQL table better than storing the user id?

Use integers. This is the simplest and most efficient way (especially for InnoDB).

That is

don't use hashes or UUIDs or varchars in place of int
don't use the natural key when you have foreign keys to it

Reasons?

varchar comparisons require collation + case tests
index architecture in InnoDB favours integer fields (ideal clustered index is narrow, numeric, monotonically increasing)
hashes and GUIDs and varchar cause fragmentation
wider data + indexes rows when not using integer
64 characters requires 64/66 bytes to store. 64 bit in requires 8 bytes

Other observations:

Do you need 64 bit numbers?


Database design: Which will perform better, auto incremented column or a composite key for appointments The schema of table is :

appointment(date,time,patientID,...)
pateint(patientID,PName,age,...)

PS: two or more patient can came at the same time .
    The number of appointments per day up to 500 .

Which is better for performance, using auto-incremented primary key or a composite primary key (date,time,pateintID)?

From a purely performance standpoint, using the identity key as a clustered primary key will be most efficient. From there I would do a non clustered key with a unique constraint on datetime, patientid.


Retrieve game details that their level's value is higher than 1/10 the sum of guilds levels I've a database which contains tables:

Quest:

PK  Name
PK  qName
    goal

Guild:

PK  Name
PK  gName
    level

Game:

PK  Name
    MaxPlayer
    levels
    uName

GuildQuest:

PK  Name
PK  gName
PK  qName

I need to retrieve  the game details that their level's value is higher than 1/10 the sum of guilds levels

This is what I've build so far:

SELECT
  Name, MaxPlayer, levels, uName
  SUM(level) AS guildsLevels
FROM
  Guild
GROUP BY
  level

I need to add the multiplication by 0.10 of the sum which I don't know how to do, and then check every game that its value is bigger than sum of guild levels multiplied by 10%.

I'm using MySQL (and PhpMyAdmin to run the queries).

Assuming the Guild.gName = Game.Name, I think you are looking for something like this:

select
    ga.Name
  , ga.MaxPlayer
  , ga.levels
  , ga.uName
  , gu.GuildsLevels
from Game ga
  inner join (
    select gName, GuildsLevels=sum(level)
      from Guilds
      group by gName
      ) as gu on ga.Name   = gu.gName
             and ga.levels>=(gu.guildslevels*0.10)


Design a Lab Report tables I need to design tables for a laboratory test.

I will have Lab test name, and its component to be saved. The component can be changed with time. I need to design a UI for update.  Also each component's data with name, min, max value etc. needs to be saved in tables.

I already have Person table for storing PersonID there.

How can I design this more efficiently, Any help. Thanks.

When creating a data model a good place to start is to have an entity type for each noun in the description of your problem.  The attributes of these entity types will be the values you wish to store about those nouns and verbs in the description will become relationships in your data model.

In the database the entity types become tables, the attributes are columns and relationships end up as foreign keys.  Of course there are a lot of subtlties but this should get you going.

Copying your description above into these terms you will have:

Table: LabTests
    Name

Table: Components
    Name
    MinValue
    MaxValue

Since you say "component can be changed with time" you will need a table to link the two together

Table: TestComponents
    TestName
    ComponentName
    FromDate
    ToDate

I've included FromDate and ToDate so you can tell how a test has changed over time.  If you are only interested in what a test looks like right now you can remove these columns.

You mention PersonID, so we'll have a table to put that into.

Table: People
    PersonID

What do you do to that person?  You don't say, but I should think you take a sample from them and apply tests to that sample.  OK, let's have tables for those.

Table: Samples
    SampleID
    TakenFromPersonID
    TakenDateTime

You will need to know from whom the sample was collected.  This is the TakenFromPersonID.  You will need to know when it was collected.  Since it is likely that several samples can be collected in one day this will have to be measured to the minute, or whatever is appropriate.  I've added SampleID so you have a unique, anonymous values which can be used to track sample through the lab, used on bar codes and things like that.  You don't say but I think you may want to have a SampleMaterial column to distinguish between, say, blood and urine samples because some tests will be applicable to one but not the other.

Now we have the sample in the lab we need to know what tests to perform.  I will assume you do not perform every test on every sample.  You need a table to hold this information.

Table: OrderedTest
    SampleID
    TestName

Perhaps you will need to record who ordered the test and when.  You can add to this table as required.

Knowing the TestName we can use TestComponents to find out what is to be measured.

Then we need somewhere to hold the actual measurements.  This will depend on the component and the sample.

Table: Results
    SampleID
    ComponentName
    MeasuredValue
    MeasuredAtDateTime

I'm assuming that a particular component will only be measured once even if it occurs in two tests.  For example, if Test A consists of blood sugar and vitamin A, and Test B consists of blood sugar and iron level, and both Test A and Test B were ordered, then blood sugar would only be measured once from each sample.

A way to check you model is to list the questions you want to ask of it.  Can your model answer those questions fully and unambiguously?  If not you need to talk to your users about how things work in the real world and add this new understanding to the model.

Finally, don't forget normalisation.  As you add more attributes into the model ensure you are adding them to the correct table.  You may have to add new tables and revise the relationships between existing tables.  Don't worry, this is normal in database design.

I hope I've given you some good pointers on how to proceed.  Concentrate on what people do and what they do it to.  This will be a good start.  Lab software is field in itself and can be complicated.  There are a number of commercial packages in existance.


start mongod by service mongod start, attempt to create a lock file on a read-only directory /mgodb I changed /etc/mongod.conf's dbpath to /mgodb and then I execute the follow command:

mkdir /mgodb
chown mongod:mongod /mgodb
chmod 0755 /mgodb

Then I run service mongod start, and get errors like:

  initAndListen: 20 Attempted to create a lock file on a read-only directory: /mgodb

I tried to change /usr/lib/systemd/system/mongod.service's User and Group to root, it also get the same error.

But I cat run mongod --fork --config /etc/mongod.conf through command line.

If you look that /etc/mongod.conf, what are values of processManagement.pidFilePath and storage.dbPath? It sounds like that system tries to create files to some subdirectory, what don't exists or is not owned by user what is used to start mongod process.

You should never start your mongod with root user. And if you have done that, f.ex. when starting mongod to maintenance mode, always remember to chown -R user: <dir> data files back to the right owner.


For MySQL, is there tool to dump a set of rows from one tables and all related (by foreign key) rows from all related tables? I'm using MySQL db.

I want to dump a set of rows from one table and if these rows have foriegn keys that point to rows on some other tables, I want to dump those too.

Then I want to load these dumps into their respective tables on a different database (e.g using LOAD DATA INFILE command). So simply using select...join...into outfile is not enough since the result would be one big joined table.

For example table A has B_id and C_id columns that point to rows on table B and C respectively. So when I dump 3 rows from table A, 3 related rows from B and 3 from C are dumped too.

Does such a tool/script exist?

Writing a script to do that is very doable, but I don't want to reinvent the wheel if such a tool already exist.

I think Jailer is what you are looking for:

http://jailer.sourceforge.net/

(Though I haven't used it so far)


How vulnerable is SQL Server 2000? I know there are still some SQL Server 2000 installs that exist out there.  With Microsoft no longer supporting these versions, how can I check to see what vulnerabilities remain unpatched?

This website lists some of the vulnerabilities but I am unable to determine which have fixes/patches to address the vulnerabilities. http://www.cvedetails.com/vulnerability-list/vendor_id-26/product_id-251/version_id-3147/Microsoft-Sql-Server-2000.html

If there are faults found since it dropped out of the extended support period then unless you have paid Microsoft for extended extended support you can be pretty sure there is not a fix available to you.

Obviously you can mitigate the risk considerably by following standard practise and making sure the only machines that can touch your SQL instance are a select few application servers within your firewall(s) and making sure all of them are fully patched up & so forth.


mysql multiple database prioritize innodb buffer pool size With a mysql instance and multiple databases within that instance is there a way to allocate which database you would like to assign priority to the buffer pool size?

You can configure MyISAM dedicated key buffers. No such buffer scheme exists with InnoDB.

Usually, it is recommended to assign up to 80% of server RAM to the InnoDB Buffer Pool.

Since you cannot prioritize which database or tables can occupy the InnoDB Buffer Pool, all you can really do is create a buffer large enough to hold all InnoDB data.

See my earlier posts on sizing the InnoDB Buffer Pool

Oct 22, 2012 : How large should be mysql innodb_buffer_pool_size?
Oct 22, 2012 : How much memory do I need for innodb buffer pool? (This was my post on ServerFault that was later migrated to the DBA StackExchange the next day)
Apr 14, 2011 : What are the main differences between InnoDB and MyISAM?


How to properly and effective rename a vaue (column) of a foreign key in both tables? I have 2 tables: users and user_statuses. A user status is of type varchar. It's varchar for the purpose of readability and I don't consider using id/int in this case.
CREATE TABLE users(
 --............
 status varchar foreign key references user_statuses(name),
 --.....

The column name in user_statuses is UNIQUE
At some point I may need to rename some the name or names of some statuses in user_statuses table because I haven't decided what to name them and how many of the statuses overall I need.
Question: How would I rename a status name such that it's effective in terms of performance? And in general, how to do it properly in this case?
P.S.
In my real system I have dozens of _*statuses tables and each is subject to have some of its statuses renamed.
Each of the tables that refers to *_statuses ones may have, say, 10M-30M-50M rows. Or up to 100M ones at most.

I would consider using a code for the status. I.e
CREATE TABLE USER_STATUS
( STATUS_CODE CHAR(?) NOT NULL PRIMARY KEY
, STATUS_NAME TEXT NOT NULL UNIQUE
);

The use of char is discouraged by the postgres community, so an alternative would be to use text and an additional constraint:
CREATE TABLE USER_STATUS
( STATUS_CODE TEXT NOT NULL PRIMARY KEY
, STATUS_NAME TEXT NOT NULL UNIQUE
,     CHECK (LENGTH(STATUS_CODE) <= ?)
);

It should be more stable than the name, but more intuitive than an int.
But for your question, I would probably insert the new value:
INSERT INTO USER_STATUS (NAME) VALUES ('Valid');

then update all users:
UPDATE USERS SET STATUS = 'Valid' WHERE STATUS = *Normal';

and finally, remove the old status:
DELETE FROM USER_STATUS WHERE NAME = 'Normal';

For the more general case where there is a hierarchy of tables involved, you traverse them in DFS and insert new rows on the way down. When you reach a leaf you can use update and then delete rows as you climb the tree back up. You can use INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS to obtain the edges in the tree, and then use for example a topological sort to determine the ordering of the tables.
Another advice is to stick with the same name for the attribute throughout the model. I.e:
CREATE TABLE USER_STATUS
( STATUS_CODE ... NOT NULL PRIMARY KEY
, STATUS_NAME TEXT NOT NULL UNIQUE
);

CREATE TABLE USERS
( ...
, STATUS_CODE ... REFERENCES USER_STATUS (STATUS_CODE)
...
);


Unable to start mongod daemon and it looks its crashing DummyD3 is node3 of 3 node replication cluster.
DummyD2 is primary and
DummyD1 is Secondary (which is in sync with primary).
I tried deleting the data under data path and did rsync from other secondary node back to back twice to copy the updates while 1st was running in 2nd rsycn and started the deamon.
rsync -asvh Dummy1:/mongodb/data/dbinstance/* /mongodb/data/dbinstance
rsync -asvh Dummy1:/mongodb/data/dbinstance/* /mongodb/data/dbinstance
rm /mongodb/data/dbinstance/mongod.lock
rm /mongodb/data/dbinstance/mongod.pid
su - mongoinstance -c "/usr/bin/mongod --config /etc/mongo/dbinstance/mongodb.conf &"

Ran above command in screen from problematic node.
~# mongod --version
db version v4.0.4

I'm here looking for solution that if some has solution to help me fixing this issue.
root@DummyD3:~# tail /mongodb/files/errorlogs/dbinstance/mongodb.log

 mongod(_ZN5mongo15printStackTraceERSo+0x41) [0x55c2e748caa1]
 mongod(+0x23D9CB9) [0x55c2e748bcb9]
 mongod(+0x23DA19D) [0x55c2e748c19d]
 libpthread.so.0(+0x11390) [0x7fa295382390]
 libc.so.6(gsignal+0x38) [0x7fa294fdc428]
 libc.so.6(abort+0x16A) [0x7fa294fde02a]
 mongod(_ZN5mongo32fassertFailedNoTraceWithLocationEiPKcj+0x0) [0x55c2e5ac1ea7]
 mongod(_ZN5mongo17wtRCToStatus_slowEiPKc+0x413) [0x55c2e5bb8eb3]
 mongod(_ZN5mongo18WiredTigerKVEngine15_openWiredTigerERKNSt7__cxx1112basic_stringIcSt11char_traitsIcESaIcEEES8_+0x2D1) [0x55c2e5b90161]
 mongod(_ZN5mongo18WiredTigerKVEngineC1ERKNSt7__cxx1112basic_stringIcSt11char_traitsIcESaIcEEES8_PNS_11ClockSourceES8_mbbbb+0x63F) [0x55c2e5b9611f]
 mongod(+0xAC6149) [0x55c2e5b78149]
 mongod(_ZN5mongo23initializeStorageEngineEPNS_14ServiceContextENS_22StorageEngineInitFlagsE+0x552) [0x55c2e62613a2]
 mongod(+0x993251) [0x55c2e5a45251]
 mongod(_ZN5mongo11mongoDbMainEiPPcS1_+0x10DB) [0x55c2e5b2d94b]
 mongod(main+0x9) [0x55c2e5ac3a39]
 libc.so.6(__libc_start_main+0xF0) [0x7fa294fc7830]
 mongod(_start+0x29) [0x55c2e5b2bab9]
-----  END BACKTRACE  -----

Wernfried Domscheit said:

You don't need to copy any data in a replicat set. Just remove entire directory and start the service. The replicat set will trigger an inital sync and copies all data. In case you like to take a copy from dbPath you should stop the service or at least lock it with db.fsyncLock() (and of course unlock afterwards with db.fsyncUnlock())

db.fsyncLock() & db.fsyncUnlock() did work and I fixed the chronic replication.


How to get top 5 rows of given Id? I have the following code:

DECLARE @Latest TABLE
(
    DomainId bigint,
    [date] datetime2
)

DECLARE @Rows int
DECLARE @History int

INSERT INTO @Latest (DomainId, date)
SELECT DomainId, MAX(Date)
FROm DomainDetailDataHistory
GROUP BY DomainId

SELECT @Rows = COUNT(DomainId) FROM @Latest

SET @History = @Rows * 5

SELECT DISTINCT TOP(@History)
  c.DomainId
  , c.[Date]
  , Passed
FROM DomainDetailDataHistory c
JOIN  @Latest h ON h.DomainId = c.DomainId
WHERE c.[Date] != h.[Date]
ORDER BY c.Date DESC

This code gets me the latest inserted row given by Id and the last 5 insertions.

Problem:

This works all good when I have 5 insertions of every unique DomainId.
I tried adding a new one and since the new one did not have any prior insertions the code gave me the top 7 insertions to compensate for the extra domain.

Clarity:

@History gives me how many different domains I have. I multiply this by 5 to get 5 of each.

Problem is when one of the domains do not have 5 insertions, lets say it only has one then I get more than the top 5 of the other domains.

How do I make sure that I always only get 5 of each and NOT MORE?

Have you looked into using a Cross Apply?  This should do the trick for you, but I was unable to test a there is no data in your sample.  For each Domain in the @Latest, it gives you the latest 5 results from the domainDetailDataHistory table.

SELECT  h.DomainId
    , c2.[Date]
    , c2.Passed
FROM    @Latest h
        CROSS APPLY ( SELECT TOP ( 5 )
                                c.DomainI
                              , c.[Date]
                              , c.Passed
                      FROM      DomainDetailDataHistory c
                      WHERE     c.DomainID = h.DomainId
                                AND c.[Date] != h.[date]
                      ORDER BY  c.[Date] DESC
                    ) AS c2;


Database server down: file /dev/vda1 used up all diskspace My database server went down last night because there was no diskspace available. I checked what used up diskspace and it was 28GB by /dev/vda1

I have no clue what this is. The database all together will be a maximum of 1 GB diskspace, what is using up all this space?

When I start mysql, I get:

  error: 'Can't connect to local MySQL server through socket
  '/var/run/mysqld/mysqld.sock' (2)'

This is probably also because of the diskspace issue.

Hopefully someone can help me shortly. Many thanks in advance!

I probably found the reason, I made nightly backups to external server, but those were also stored on our server which had exceeded allowed disk usage. I didn`t understand /dev/vda1 at first, but I now get that is not a file but probably a partition or something like that.


SQL server auto update column I am trying to change the value of  defaults column from Y to N if a new value is inserted. The default column is now only restricted to have only 1 Y based on the different membershipType.

The Table:

 ID  titlePromo   membershipType         defaults
 --  ----------   --------------         ------
  1    Promo 1     Membership Promotion      Y
  2    Promo 2     Membership Renewal        Y
  3    Promo 3     Membership Grad           Y
  4    Promo 4     Membership Promotion      N
  5    Promo 5     Membership Promotion      N
  6    Promo 6     Membership Grad           N

Example:
Promo 1 defaults column value = 'Y'
However, a new 'Y' defaults value is inserted in either existing row with the same membership Type or a newly created row with the same membership Type.
So once a default value is being changed to 'Y', the defaults column value in promo 1 will automatically changed to 'N'.

How do i achieve that?

CREATE TRIGGER trgDefaults ON FYPDB.dbo.Promotions
AFTER INSERT, UPDATE
AS
BEGIN;
UPDATE FYPDB.dbo.Promotions SET defaults = 'N'
WHERE defaults = 'Y'
  AND membershipType IN (SELECT membershipType FROM Inserted);
END

The trigger control that i used:

USE FYPDB
GO
CREATE TRIGGER trgDefaults ON dbo.Promotions AFTER INSERT, UPDATE AS BEGIN; UPDATE dbo.Promotions SET defaults = 'N' WHERE defaults = 'Y' AND membershipType IN (SELECT membershipType FROM Inserted); END

However, it changes my insert defaults value from a 'Y' to a 'N'. It's not what i wanted.

You need to update the defaults column by using an insert trigger on this table. The only problem with it would be that your trigger will fail on multirow-insert.
There is a correction in the WHERE clause you are using. Following is the code you should use:

UPDATE FYPDB.dbo.Promotions
SET defaults = 'N'
WHERE ID NOT IN (SELECT ID FROM Inserted) AND membershipType IN (SELECT membershipType FROM Inserted) AND EXISTS (SELECT * FROM Inserted WHERE defaults = 'Y');
UPDATE FYPDB.dbo.Promotions
SET defaults = 'Y'
WHERE ID IN (SELECT ID FROM Inserted) AND NOT EXISTS (SELECT * FROM FYPDB.dbo.Promotions p WHERE p.defaults = 'Y' and p.membershipType in (SELECT membershipType FROM Inserted));


Return results of two MySQL query in only one I am running two seperate queries to get statistics from 3 different tables:

contacts
campaigns
sent_messages

The first query counts all contacts that are marked as "stop" in the contacts table (results are grouped by month and limited to last 12 months).

SELECT TIMESTAMP(sent_date) AS timestamp, COUNT(contacts.id) AS stopped
FROM contacts
JOIN campaigns
ON campaigns.list_id = contacts.list_id
WHERE STOP = 1 AND campaigns.user_id = 1 AND sent_date != 0
GROUP BY YEAR(sent_date), MONTH(sent_date)
ORDER BY sent_date DESC LIMIT 12;

The other query counts the status codes of sent messages (also grouped by month and limited to last 12):

SELECT
TIMESTAMP(sent_date) AS timestamp,
COUNT(sent_messages.id) AS messages,
SUM(IF(sent_messages.status = 1, 1, 0)) AS received,
SUM(IF(sent_messages.status > 1, 1, 0)) AS errored,
FROM campaigns
JOIN contacts
ON campaigns.list_id = contacts.list_id
WHERE user_id = 1 AND sent_date != 0
GROUP BY YEAR(sent_date), MONTH(sent_date)
ORDER BY sent_date DESC LIMIT 12;

Now I'm having a hard time grouping these two queries in a single one. I have tried the following but it takes ages (full table scan) and returns uncoherent results.

SELECT
TIMESTAMP(sent_date) AS timestamp,
COUNT(sent_messages.id) AS messages,
SUM(IF(sent_messages.status = 1, 1, 0)) AS received,
SUM(IF(sent_messages.status > 1, 1, 0)) AS errored,
SUM(IF(stop = 1, 1, 0)) AS stopped
FROM campaigns
JOIN contacts
ON campaigns.list_id = contacts.list_id
JOIN sent_messages
ON campaigns.id = sent_messages.campaign_id
WHERE user_id = 1 AND sent_date != 0
GROUP BY YEAR(sent_date), MONTH(sent_date)
ORDER BY sent_date DESC LIMIT 12;

So I'd like to know why I get these results (maybe the second JOIN ends up creating duplicates and everything ends up being counted several times?), and how could I fix that (if at all possible).

For reference, the tables involved:

CREATE TABLE contacts (
    id BIGINT UNSIGNED AUTO_INCREMENT,
    list_id INT UNSIGNED NOT NULL,
    telephone CHAR(12) NOT NULL,
    firstname VARCHAR(50),
    lastname VARCHAR(50),
    attributes VARCHAR(10000),
    stop BOOLEAN DEFAULT 0,
    PRIMARY KEY (id),
    INDEX contact_ind_list_id (list_id),
    INDEX contact_ind_stop (stop),
    UNIQUE KEY contact_index_telephone_and_list (telephone, list_id),
    CONSTRAINT contact_fk_list
        FOREIGN KEY (list_id)
        REFERENCES lists (id)
        ON DELETE CASCADE
);

CREATE TABLE campaigns (
    id INT UNSIGNED AUTO_INCREMENT,
    user_id INT UNSIGNED NOT NULL,
    list_id INT UNSIGNED,
    name VARCHAR(50),
    text VARCHAR(500),
    send_date TIMESTAMP,
    sent_date TIMESTAMP,
    sender_name VARCHAR(11),
    status TINYINT UNSIGNED DEFAULT 0, -- 0 draft, 1 to be sent, 2 sent
    batch_number SMALLINT UNSIGNED DEFAULT 0,
    PRIMARY KEY (id),
    INDEX campaign_ind_owner (user_id),
    INDEX campaign_ind_push_id (push_id),
    UNIQUE KEY campaign_ind_list_and_name (list_id, name),
    CONSTRAINT campaign_fk_owner
        FOREIGN KEY (user_id)
        REFERENCES users (id)
        ON DELETE CASCADE,
    CONSTRAINT campaign_fk_list
        FOREIGN KEY (list_id)
        REFERENCES lists (id)
        ON DELETE CASCADE
);

CREATE TABLE sent_messages (
    id BIGINT UNSIGNED AUTO_INCREMENT,
    campaign_id INT UNSIGNED NOT NULL,
    telephone CHAR(12) NOT NULL,
    status TINYINT NOT NULL, -- 0 = waiting / 1 = received / 2 = npai / 3 = net_error (error_credits, unknown_error)
    message TINYINT NOT NULL,
    PRIMARY KEY (id),
    INDEX sent_messages_ind_campaign (campaign_id),
    UNIQUE KEY sent_messages_ind_campaign_and_telephone (campaign_id, telephone),
    CONSTRAINT sent_messages_fk_campaign
        FOREIGN KEY (campaign_id)
        REFERENCES campaigns (id)
        ON DELETE CASCADE
);

First, clean up the GROUP BY:

SELECT  TIMESTAMP(sent_date) AS timestamp, COUNT(contacts.id) AS stopped
    ...
    GROUP BY  YEAR(sent_date), MONTH(sent_date)
    ORDER BY  sent_date DESC

The value of timestamp will be some random value.  Ditto for sorting on sent_date, which is not available after the GROUP BY.  Instead, consider:

SELECT DATE_FORMAT(sent_date, "%Y-%m") AS timestamp, COUNT(contacts.id) AS stopped
    ...
    GROUP BY 1
    ORDER BY 1

(Note:  "1" in these contexts refers to the 1st column of the SELECT.)

Try that; you will see that it is OK.

Then, to fix the original question:

SELECT ...
    FROM ( SELECT ... ((the first query, cleaned up)) ) a
    JOIN ( SELECT ... ((the second query, cleaned up)) ) b ON a.? = b.?
    ORDER BY ?

I'm not sure what to JOIN on; you'll still need an ORDER BY on the outside.


Error Calling Python module function DbMySQLFE.connect Migrating a database from SqlServer to Mysql using "MySQL WorkBench 6.3 CE" using ODBC data source as default methode to use to connect to the RDBMS.

-> Source Selection Test connection is Successful
-> Target Selection Test Connection gives Error Calling Python module function DbMySQLFE.connect
I have researched alot and few folks blaming SSH connection over TCP. If its the case Can I disable SSH over TCP??
I have attached the Screenshot. Please respond its really urgent. thanks

MySQL Workbench version < 8.x cannot login to a MySQL server when a cached sha2 password is used for that user. This auth method has been made the default only recently right before the 8.0.11 server was released. All new accounts will have this auth method unless you switched this off in your server config.

Download the MySQL Workbench release 8.0.11 RC to be able to use such accounts (this is a development release) or use the command line to change the auth method for that user to native, which will allow older Workbench versions to login.


What does reslen and other means in mongodb log? database.tablebusiness query: { LongitudeLatitude: { $nearSphere: [ 106.772835, -6.186753 ], $maxDistance: 0.04498373205078301 }, Prominent: { $gte: 15 }, indexContents: { $all: [ /^aru/, /^op/ ] } } ntoreturn:20 ntoskip:80 nscanned:100 nreturned:20 reslen:1147 1927ms

it seems that ntoreturn means number to return. ntoskip is numbers to skip. nscanned seem to be the sum of the 2.

What is reslen?

I search google with no answer. What site explain what reslen is?

reslen is the query result length in bytes.  From the profiler docs here:

http://docs.mongodb.org/manual/reference/database-profiler/


PostgreSQL pg_ctlcluster won't work today I was reconfiguring some stuff on a PostgreSQL 9.4 install on a Debian 8. After being done I typed

# pg_ctcluster 9.4 main restart

as usual, but this time it wouldn't start back up. The error message directed me towards the log of the cluster, which was empty.

# pg_ctcluster 9.4 main start

Didn't work either.
So to track the error down I started the cluster manually with

$ /usr/lib/postgresql/9.4/bin/postgres -d 3 -D /var/lib/postgresql/9.4/main -c config_file=/etc/postgresql/9.4/main/postgresql.conf

as the user postgres. That worked fine. So the cluster works, the start-up script itself must be the culprit then.
So in hopes to get more detailed error messages I tried to start it like so

# pg_ctcluster --foreground 9.4 main start

which, to my surprise, worked just fine without hiccups.

What can I do about that now? What else is there to check that might throw off the start-up script?
I wouldn't like to modify the pg_ctlcluster script tbh.

Issue solved.
Unknown to me, one member of our team deleted the main log file of the database, because it had grown quite a bit. Apparently it was deleted completely instead of being truncated. Then a new log file was created, but for some reason with the root as owner.
Simply changing the owner back to postgres like so

chown postgres:postgres /var/log/postgresql/postgresql-9.4-main.log

solved the issue.


MongoDB index strategy I'm developing a system to back-up computers and store the files / data with retention for a while. The actual files are stored in an object store, while I'm planning to keep the metadata of the files in MongoDB. I am however rather new to MongoDB.

As there will be many computers containing many many files, there will eventually be billions of (small) records / documents. I'd like to use MongoDB for its sharding features.

For each item I'm storing the following data items: Computer_ID, objectstore_ID, path, filename, size, last_modified, revision_start, revision_end. An example document in the collection would be:

{"computer_ID": 6829, "objectstore_ID": "abcdefghijklmnopqrstuvwxyz", "path": "/home/user/path/to/directory/", "name": "file.txt", "size": 58202, "last_modified": 1395491119, "revision_start": 52, "revision_end": 58 }

Typical queries would be listing a directory on a computer in a specific revision, for example {"computer_ID": 312, "path": "/home/", "revision_start": >=54, "revision_end": <=54 }. Usually those four columns will be used in queries.

My first question is if MongoDB is the right solution for this use case, or would a relational database suit my needs better? In case of the latter, how could I distribute the data over multiple servers like MongoDB has sharding?

Secondly, what would be the best indexing strategy to maintain the best (or at least reasonable) performance when the database grows. Should I add 4 separate indexes on computer_ID, path, revision_start and revision_end? Or create one multikey containing all four? Maybe something else?

Finally, I also wonder whether the storage size of the indexes won't grow to a multiple of the original data size.

Logging is always a good use case for non relational stores (text book example).

You've only given us one query pattern, so going by that I would create a single compound index, equality to inequality.

I would suggest reading up on indexes at:

MongoDB Index Introduction

and

MongoDB FAQ: Indexes


Understanding Table design in database I have 3 years of SQL Server development work experience. Now I am moving to a new company (first jump). I heard in my new company they deal with too many tables.

What is the best way to understand table design in a database? How do you SQL gurus analyse the table design when you move to a new company?

Is ER diagram the best way? Please share your experience on this.

The database is an encoding of the business rules. Without understanding the rules there is no way of making sense of the database. Take it piece-by-piece, focusing on one area until you have a reasonable understanding of it.

Even if documentation exists I like to draw ER diagrams from scratch as it helps me remember the connections. It also allows me to compare how I would have done it to how it is currently implemented. The differences are an opportunity for further learning.

Reading the code will bring out further "virtual" FKs and alternate keys that are not declared explicitly.


SSRS 2008 to 2014 Migration Excel Export error We are working on migrating our SSRS from SQL Server 2008 to SQL Server 2014.  After brining the reports over I have a set of reports that will execute just fine but when I try to perform a standard export to excel from the 2014 Server I get a message box saying:

  We found a problem with some content in ACM Consolidated
  Invoie_2014.xlsx. Do you want us to recover as much as we can?
  If you trust the source of this workbook, click yes.

However, I do not receive the same message when exporting to Excel from the 2008 server.  I believe the problem is related to some Numeric fields that are coming back with a value of 0.0000000000000000000.  Has something changed with the excel export function between these two versions?

This appears to be related to some implicit conversions that are occurring in the background when exporting the report to xlsx.  Numeric columns are being sent to excel as if they are text columns.  One report responded to a change to the query using an Explicit cast to Numeric (Cast as opposed to convert to remove any commas or other formatting that might confuse the conversion).  Several other reports did not respond to the Cast but returned correctly after using an expression conversion of VAL().  I still have several reports that are not responding to either.  If anyone out there has a good explanation for this I'd love to hear it.


MongoDB Replica Set & Data Sync behavior I have a replica set with 3 nodes X, Y & A (where A is an arbiter). Currently X is assigned a higher priority than Y. When X goes down, Y becomes the PRIMARY. When X is back in action, the data sync happens, Y continues to be the PRIMARY and X acts as SECONDARY.

My requirement is as follows:

When X is back in action, I want X to automatically become the PRIMARY (i.e. without any manual intervention), once the X has synced all the data from Y.

I understand that I shouldn't be really worried about which node is primary and which one is secondary, but in my case the performance of Y node is really bad (due to the network latency) and would like to keep X as PRIMARY as much as possible.

Is there any settings / configuration in MongoDB that I can use to achieve this behavior? The version I'm using currently is 3.4.3

Thanks.

If you have modified X nodes priority at rs.conf() higher number than Y node, switch over should automatically happen WHEN X has replicated all information from Y node. Not before that. If switch doesn't happen, check rs.printSlaveReplicationInfo() that your X is really 0 second behind. It sounds like that your Y is so slow that even X cannot read opLog fast enough and keeps lagging that why.


How to change password of user in physical standby database Oracle 11g? Hi I am new to Oracle 11g, I have a physical standby database and a primary database of Oracle 11g on two different servers.

I have changed the passwords of users(schemas)like sys, sysman, system, users,etc. in primary database by using command :

"SQL>alter user username identified by password"

and the passwords have been successfully changed, but when I am trying the same thing with physical standby database , its creating oracle error
ORA-16000: database open for read-only access

And I don't want to change the physical standby database to primary i.e. cannot change it to open read - write mode .

Kindly provide some solution.
Thanks in advance

You do realize the standby is a "physical" copy of the primary (read-only in this case)? If you change the password in the primary, the changes will be propagated to the standby also. You can not change such things in a physical standby independently.


sqldeveloper file open dialog freeze When I try to open a file using the file open dialog, sqldeveloper freezes for almost a minute. After that the dialog opens at the user temp folder.

I think it is trying to access some unavailable network location:

However, I can't figure out how to make it "forget" that location. I tried to select a local file in the dialog, but next time all repeats.

sql developer was taking a long time to open files dialog. I found out that it was because it is opening local temp folder which has a huge amount of files. Guess what, these files are being generated by sql developer. They are name amcXXXX.tmp.
I didnt find how to avoid its generation though.


How to tell if `CLUSTER table USING index` finished? I want to use an AWS scheduled Lambda function to update my PostgreSQL table indexes every month. I am not on psql bash and I am not leaving Lambda function up until CLUSTER finishes.

How can I tell if the CLUSTER finished?

To find out whether or not you have any executing CLUSTER commands, simply run this..

SELECT * FROM pg_stat_activity
WHERE query ~ '^CLUSTER'
  AND state = 'active';

However, that doesn't tell you if it already finished on a table. I don't think you can figure that out. You can only see

whether or not a CLUSTER is running.

SELECT * FROM pg_stat_activity AS psa
WHERE query ~* '^CLUSTER'
  AND state = 'active';

if one is currently running, what table it was running on. This requires more work, we look at the locked tables the transaction is using to figure this out.

SELECT *
FROM pg_stat_activity AS psa
JOIN pg_locks AS pl
  ON (pl.pid = psa.pid)
WHERE query ~* '^CLUSTER'
  AND state = 'active'
  -- replace with table
  AND relation::regclass = 'MyTable'::regclass
  AND mode = 'AccessExclusiveLock';


Converting multiple SQL implicit joins into explicit joins I am trying to convert several queries that use implicit joins into explicit joins, as per the standard ANSI/ISO Entry SQL-92 [1].
But after a lot of googling I could not find any examples or guides on how to proceed with the same. Could someone point me in the right direction..? Thanks!

Sample code:

    SELECT *
    FROM a,b,c,d,e,f,g
    WHERE
01:     e.p_id(+)   = a.p_id
02: AND e.o_id(+)   = a.o_id
03: AND c.c_id      = h.c_id(+)
04: AND c.b_id      = h.d_id(+)
05: AND g.d_id (+)  = h.d_id
06: AND g.o_id (+)  = h.o_id
07: AND a.p_id      = b.p_id
08: AND c.p_id      = a.p_id
09: AND d.c_id      = c.c_id
10: AND a.o_id      = f.e_id
11: AND g.o_id      = a.o_id

UPDATE

Based on my understanding of Ted Elliot's answer, I have tried to reproduce the algorithm:

But not clear on the steps 9, 10.

1.List all tables alphabetically: a,b,c,d,e,f,g

2.Start with first table 'a'. Identify all join conditions: 'a' joins with b/c/e/f/g. Pick any one, say 'e'. Keep 'a' to the right.

e
RJ a ON
e.p_id = a.p_id AND
e.o_id = a.o_id

3.Next is 'b'. Is 'b' joined with a/e above? Yes -> Continue.
Identify all join conditions: 'b' joins with a. Keep 'b' to the right.

...
INNER JOIN b ON
a.p_id = b._pid

4.Next is 'c'. Is c joined with a/b/e from above? Yes -> Continue.
Identify all join conditions: 'c' joins with h/a/d. Selected 'a'. Keep 'c' to the right.

...
INNER JOIN c on
c.p_id = a.p_id

5.Next is 'd'. Is d joined with a/b/c/e from above? Yes -> Continue.
Identify all join conditions: 'd' joins with c. Keep 'd' to the right.

...
INNER JOIN d on
d.c_id = c.c_id

6.Next is 'e'. Is e joined with a/b/c/d/e from above? e already exist in list -> Skip.

7.Next is 'f'. Is f joined with a/b/c/d/e from above? Yes -> Continue.
Identify all join conditions: 'f' joins with a. Keep 'f' to the right.

...
INNER JOIN f on
a.o_id = f.e_id

8.Next is 'g'. Is g joined with a/b/c/d/e/f from above? Yes -> Continue.
Identify all join conditions: 'g' joins with a/h. Selected 'a'. Keep 'g' to the right.

...
INNER JOIN g on
g.o_id = a.o_id

9.Next is 'h'. Is h joined with a/b/c/d/e/f/g from above? Yes -> Continue.
Identify all join conditions: 'h' joins with c/g. Selected 'c'. Keep 'h' to the right.
Note that the conditions require all records of 'c' to be joined with 'h': "c.c_id = h.c_id(+) AND c.b_id = h.d_id(+)". How can we ensure this as 'c' has been joined with a/d above so not all records of 'c' are present in the temp. table of above joins?

...
INNER JOIN h on
c.c_id      = h.c_id AND
c.b_id      = h.d_id

10.Table list completed, But there are two conditions remaining: "g.d_id (+)  = h.d_id AND g.o_id (+)  = h.o_id". Identify all join conditions: 'g' joins with h. Keep 'g' to the right. Note that the conditions require all records of 'h' to be joined with 'g'. But again h is joined above so not all records are available.

...
INNER JOIN g on
g.d_id = h.d_id AND
g.o_id = h.o_id

This is how I would rewrite it:

SELECT *
FROM e
RIGHT OUTER JOIN a ON
  e.p_id = a.p_id AND
  e.o_id = a.o_id
INNER JOIN b ON
a.p_id = b._pid
INNER JOIN c ON
a.p_id = c.p_id
INNER JOIN d ON
c.c_id = d.c_id
INNER JOIN f ON
a.o_id = f.e_id
INNER JOIN g ON
a.o_id = g.o_id
LEFT OUTER JOIN h ON
c.c_id = h.c_id
AND c.b_id = h.d_id
LEFT OUTER JOIN g ON
h.d_id = g.d_id
AND h.o_id = g.o_id

The (+) denote optional tables and end up being converted to either LEFT  or RIGHT OUTER joins.  In "c.c_id = h.c_id(+)" the "h" side is the optional one so this becomes "c LEFT OUTER JOIN h".  If the "c" side had the plus instead it would be a "c RIGHT OUTER JOIN h", although I typically just swap the order and make it a LEFT OUTER JOIN since I think it is easier to read.  I did that in a few cases (LEFT OUTER JOIN g).

If you have multiple conditions for the same set of tables those get combined in the ON clause:

e.p_id(+) = a.p_id
AND e.o_id(+) = a.o_id

becomes

e RIGHT OUTER JOIN a ON
e.p_id = a.p_id
AND e.o_id = a.o_id


SQL squash rows returned when joining several tables I have a database which has a table for the main items, lets call it A with primary key 'id'.
Then there are a number of tables (B, C...), each corresponding to some attribute that each item can have 0 or more of. Each row in the attribute tables have a foreign key ('item_id'), specifing which main item it corresponds to, they also have a primary key 'id' for identification purposes.

I'm attempting to make a query which will get me all items with all attributes from a selection of the attribute tables. For instance if I want information about all attributes B and C, I tried something like

SELECT A.id, B.id, C.id
FROM A
LEFT JOIN B ON A.id=B.item_id
LEFT JOIN C ON A.id=C.item_id

However for an item which has 2 attributes in B and 3 in C, this returns 6 rows for that item:

----------------------------
| A.id  |  B.id  |  C.id   |
----------------------------
|    1  |     1  |     1   |
|    1  |     1  |     2   |
|    1  |     1  |     3   |
|    1  |     2  |     1   |
|    1  |     2  |     2   |
|    1  |     2  |     3   |
|    2  |  NULL  |     1   |
.
.
.

While in the best of worlds it would only require 3 rows for that item:

----------------------------
| A.id  |  B.id  |  C.id   |
----------------------------
|    1  |     1  |     1   |
|    1  |     2  |     2   |
|    1  |  NULL? |     3   |
|    2  |  NULL  |     1   |
.
.
.

Is there anyway to "squash" the rows in this way, so that the number of rows returned for each item is the maximum of number of attributes in the related tables for the item (or at least one row with NULL attributes if it doesn't have any)?
The number of returned rows for each item can grow exponentially with the number of attributes requested...

Or is the best way to gather the attribute information to make one query for each attribute table, with only one JOIN in each query?

Something like

WITH BN AS (SELECT id,
                   item_id,
                   ROW_NUMBER() OVER (PARTITION BY item_id ORDER BY id) rn
            FROM B),
     CN AS (SELECT id,
                   item_id,
                   ROW_NUMBER() OVER (PARTITION BY item_id ORDER BY id) rn
            FROM C)
SELECT A.id, BC.id1, BC.id2
FROM A
LEFT JOIN (SELECT BN.item_id item_id, BN.id id1, CN.id id2
           FROM BN
           FULL OUTER JOIN CN ON BN.item_id = CN.item_id
                             AND BN.rn = CN.rn ) BC ON A.id = BC.item_id


transaction log backup on SQL server I am using log shipping as DR for my organisation.

For the particular time like morning 4 to 5'o clock .wrk files are falling instead of .trn file and that particular file take too much time to copy.

I configured log shipping for 570 databases in 4 instances in DR server.

Please suggest.

.wrk files are just temporary extension names, while log files are being copied. If log is small enough you wont even notice that change, but in your case it is notable.
Basically this is way of sql server ensuring that log that has being copied wont be accidentally restored by LSRestore job or by a manual restore.
As soon as log has been copied successfully it will change into .trn.

I dont know anything about your SQL, hardware and network specifications but having 570 transaction log backups in a span of one hour once in 24h could cause overhead on disk IO.
Check the log file size, perhaps adding to the schedule one more LS during 24h span would be beneficial (lower tran log size).


Sql Server 2012 Change Data Capture weird behaviour In my Sql Server 2012 I have the following table in a database

CREATE TABLE [dbo].[HumanResource](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](50) NOT NULL,
    [IsHired] [bit] NULL,
    [Address] [nvarchar](max) NULL,
    [Score] [bigint] NULL,
    [LastUpdate] [datetime] NULL,
 CONSTRAINT [PK_HumanResource] 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] TEXTIMAGE_ON [PRIMARY]

when I update the Address field (nvarchar, not null), it is captured as below

   NULL       3      2   mahmood True    here    111 2014-09-01 14:42:28.913
   NULL       4      2   mahmood True    there   111 2014-09-01 14:42:28.913

but when I update the Score field (nvarchar, null), this is captured

   NULL       3      2   mahmood True    NULL  111 2014-09-01 14:42:28.913
   NULL       4      2   Mahmood True    there   111 2014-09-01 14:42:28.913

**Edit (the update and select queries) :

update HumanResource set Address = 'blah blah blah' where id = 2
update HumanResource set Score = Score + 1 where id = 2

SELECT        TOP (10000) __$start_lsn, __$end_lsn, __$seqval, __$operation,
__$update_mask, ID, Name, IsHired, Address, Score, LastUpdate
FROM            cdc.dbo_HumanResource_CT

Considering that sql server Update is a chained Delete and Insert, the first row is the delete row capture and the second one is the insert row capture.

But I don't understand why the address is tracked as NULL when I update other fields, I would appreciate any information on this issue.

Here is the MSDN

Large Object Data Types
Columns of data type image, text, and ntext are always assigned a NULL value when __$operation = 1 or __$operation = 3. Columns of data type varbinary(max), varchar(max), or nvarchar(max) are assigned a NULL value when __$operation = 3 unless the column changed during the update. When __$operation = 1, these columns are assigned their value at the time of the delete. Computed columns that are included in a capture instance always have a value of NULL.

By default, the maximum size that can be added to a captured column in a single INSERT, UPDATE, WRITETEXT, or UPDATETEXT statement is 65,536 bytes or 64 KB. To increase this size to support larger LOB data, use the Configure the max text repl size Server Configuration Option to specify a larger maximum size. For more information, see Configure the max text repl size Server Configuration Option.

You are using a nvarchar(max). I've got the same problem.
I fix it by changing my varchar(max) with varchar(255). For an address 255 should be enough.

Hope this help


Improve PostgreSQL SELECT performance in a parent table (inheritance) I have a PostgreSQL 9.2 database where each account has a schema, like below:

My_Database
 |-> Schemas
    |-> AccountA
    |-> AccountB
    |-> AccountC
    |-> AccountD
    |-> AccountE
           .
           .
           .
    |-> AccountZ
    |-> MasterAccount

All schemas have a table called imovel which has a column called id, the structure is:

CREATE SEQUENCE MasterAccount.imovel_id_seq;

CREATE TABLE MasterAccount.imovel (
    id integer DEFAULT nextval('MasterAccount.imovel_id_seq') NOT NULL PRIMARY KEY,
    title varchar(80) NOT NULL
);

CREATE TABLE AccountA.imovel (
    id integer DEFAULT nextval('MasterAccount.imovel_id_seq') PRIMARY KEY
)
INHERITS (MasterAccount.imovel);

.
.
.

CREATE TABLE AccountZ.imovel (
    id integer DEFAULT nextval('MasterAccount.imovel_id_seq') PRIMARY KEY
)
INHERITS (MasterAccount.imovel);

Today I have 127 accounts and when I run a SELECT in a child table, the response is fast. But when I run the same SELECT in the father table, it is slow. I saw the EXPLAIN and it seems that PostgreSQL uses each child's index, instead of use only the father index.

Is it possible to improve this behavior?

I need to use SELECT in the master table for global searches.

SELECT in the child table

SELECT in the father table

But when I run the same SELECT in the father table, it is slow. I saw the EXPLAIN and seems that PostgreSQL uses each child's index, instead of use only the father index.

To search in the father table only, use the ONLY keyword:

 SELECT FROM ONLY MasterAccount.imovel ....

The manual:

  If ONLY is specified before the table name, only that table is scanned.


Recommendations for how I should create my table based on data given? I'm asking this as I'm kinda overwhelmed by the data presented to me (I'm still fairly new in programming and SQL). Basically I need to create a database to store warehouse equipment information, and of course to make it as simple as possible when retrieving these data in .NET

Here are the key attributes of the equipment that perplexed me in creating a SQL table for "Equipment":

Many, but not all equipment can have SSN (this is usually a 10-digit number to uniquely identify the type of equip)
Many, but not all equipment can have serial (a number to uniquely identify the SAME equip which has multiple quantity)
All equipment have description (this is the name of the equip)
Some equip may have both SSN and serial, while some equip have none.
Location: Tells where the equipment is stored in the warehouse. Equipment with different serial may be stored in separate location. As such, need to keep track of location for every serial.
Set: Tells which equipment is part of a set. For example, a machine can be part of a set together with an antenna and a wire, all 3 are different equipment stored in "Equipment" table.

That's about it, but it's too complicating for my current skills. This is so far what I'm able to come up with:

tblEquips:

EqpID - Since an equip can have neither SSN or serial, I have to create an incremental
EqpID
SSN
Description
currentQty
totalQty
location
assemblage - datatype is bit, tells whether this item is a set
Serial - I don't know how to store this, I'm thinking of storing a long list of serials separated by a comma? But it won't work when it comes to identifying which serial is in which location

tblAssemblage:

SetID
Set -  store to identify a set along with its information. Eg. a machine, an antenna and a wire. Maybe XML data type?

As such, anyone can help modify, or even work on new tables to organize this whole chunk of interrelated data? Thanks in advance for the help, and sorry about my English - it's not my native language, I hope it's comprehensible.

Can an equipment be part of multiple set's? ( assume: yes)
Can an item(one piece with one serial) be part of multiple equipments? (assume : no)
I would break the equip in parts at the serial level as smallest item.

Equip:
eqid
ssn
description
currentQty
totalQty

Item:
item_id
eqid
serial
location

Set:
setid
description?

Equip_to_set:
setid
equipid

With this model you have the location per unique serial item.
You have equip with multiple set assignments.
SSN and serial are optional.


Auto-commit UPDATE Transaction On Linked Server I would like to know more about what happens behind the scenes with an auto-commit transaction when performing a cross-server query on a linked server.

I naïvely think that when executing an auto-commit transaction the compiler/SQL Server/something else just prepends all statements with a BEGIN TRANSACTION and appends all statements with a COMMIT TRANSACTION since everything is technically enclosed in a transaction (Is it a bad practice to always create a transaction?). I'm sure this is incorrect and the source of my confusion as to why performing a cross-server UPDATE without explicitly stating BEGIN TRANSACTION works but explicitly stating one does not. According to Microsoft (https://docs.microsoft.com/en-us/sql/t-sql/language-elements/begin-transaction-transact-sql?view=sql-server-2017), an explicit BEGIN TRANSACTION on an UPDATE query that references a table on a linked server gets escalated to a distributed transaction; and since distributed transactions aren't configured on the linked server, I receive an error. How does the auto-commit setting avoid this? How does it not get escalated to a distributed transaction? Does the auto-commit setting send the data to the linked server but doesn't "listen" for a response from the linked server via Microsoft Distributed Transaction Coordinator (MS DTC); thus if an error occurs, it "silently" fails?

Auto-commit doesn't escalate to a distributed transaction:

UPDATE l
SET l.RecordKey = s.RecordKey
FROM LinkedServer.ExampleDatabase.dbo.ExampleTable AS l
INNER JOIN ServerWithActiveConnection.ExampleDatabase.dbo.ExampleTable AS s
    ON l.Value1 = s.Value1;

Explicit transaction does escalate to a distributed transaction (and errors in my case):

BEGIN TRANSACTION
UPDATE l
SET l.RecordKey = s.RecordKey
FROM LinkedServer.ExampleDatabase.dbo.ExampleTable AS l
INNER JOIN ServerWithActiveConnection.ExampleDatabase.dbo.ExampleTable AS s
    ON l.Value1 = s.Value1;
COMMIT TRANSACTION;

I should add that the statements were run in SQL Server Management Studio.

Given that a Linked Server is an external / separate connection, I would expect that it can be handled / managed separately, just like making an external / regular connection to any SQL Server (even back to the calling instance -- i.e. loop back) via SqlClient in SQLCLR. Being a separate connection, it is optional whether or not to attempt joining the current transaction (this is handled via the Enlist= {true | false} connection string keyword). I am guessing that the Linked Server property of is_remote_proc_transaction_promotion_enabled / "Enable Promotion of Distributed Transactions for RPC" is equivalent to the Enlist connection string keyword.

That being said, there is something being overlooked (including being overlooked in my original answer): the name of that option is "Enable Promotion of Distributed Transactions for RPC" (emphasis mine). The highlighted part is rather important here. This option affects stored procedure calls over the linked server, not regular ad hoc query batches. So, that option should not be relevant to the behavior you are describing in the question.

My (educated) guess is that for ad hoc queries, the option to "Enlist" in the current transaction is defaulted to "false" for auto-commit transactions. But, for explicit transactions, you are telling SQL Server that all statements within the BEGIN TRAN ... COMMIT TRAN should be considered an atomic operation, so the remote connection then tries to enlist in the current Transaction.

FYI: this behavior should have nothing to do with executing the statements in SSMS. That is just a client that submits the statements to SQL Server for execution. It should be the same behavior even if executing via SQLCMD.EXE or SqlCient in a .NET app, etc.

UPDATE

With regards to testing this, I suggested to the O.P. using SQL Server Profiler or Extended Events to capture the transaction enlistment / promotion to distributed transaction. The O.P. replied with:

  I followed your suggestion and used Profiler, and there is indeed a row that has its EventClass value with "DTCTransaction" and EventSubClass value as "Enlisting in a DTC transaction" when I use an explicit transaction. No such rows exist when I rely on the auto-commit setting.


Dynamic query in Stored Procedure over table values CREATE TABLE `Slot` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `starttime` datetime NOT NULL,
  `endtime` datetime NOT NULL,
  `shift_Id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1

I have a stored procedure where I have a Temporary Table like :

TempSlot

| starttime              | endtime             |
| 2018-10-25 15:00:00    | 2018-10-25 16:00:00 |
| 2018-10-25 13:00:00    | 2018-10-25 14:00:00 |
| 2018-10-26 12:00:00    | 2018-10-26 13:00:00 |

Now, I need to check if any entry from TempSlot is present in the Slot table such that inserting it will create a conflict.

Am not sure how to write this query in stored procedure :

I want to write something like this :

SELECT COUNT(*)  FROM Slot
WHERE
(((starttime >= '2018-10-25 15:00:00' and starttime < '2018-10-25 16:00:00')
or
(endtime > '2018-10-25 15:00:00' and endtime <= '2018-10-25 16:00:00'))
OR
((starttime >= '2018-10-25 13:00:00' and starttime < '2018-10-25 14:00:00')
or
(endtime > '2018-10-25 13:00:00' and endtime <= '2018-10-25 14:00:00'))
OR((starttime >= '2018-10-26 12:00:00' and starttime < '2018-10-26 13:00:00')
or
(endtime > '2018-10-26 12:00:00' and endtime <= '2018-10-26 13:00:00'))
)

But I want a generic query. What will be a good way to write this ?

SELECT COUNT(*)
FROM Slots s, TempSlots t
WHERE (s.starttime < t.endtime AND t.starttime < s.endtime)
   OR (s.starttime > t.endtime AND t.starttime > s.endtime)

This query counts the overlapping pairs (one record from Slots overlapped one record from TempSlots) only.

To increase the performance you may try to divide this query by two UNIONed queries escaping OR.


Setting DB to Simple Recovery then back to Full Recovery I have recently inherited all of our company's SQL databases without much warning or experience, and was hoping to make a good impression and score a few quick wins by completely reviewing backups across the board.

I've implemented various full and t-log backups but have come across another job on this box that sets the DB to simple recovery mode prior to running, then back to full afterwards, which is unfortunately very necessary.

My question is, if a full backup is taken at 1am, and t-log backups are taken at the top of every hour after that, does this other job setting simple recovery mode at 5:30am ruin the "timeline" as such, between the full backup and the next t-log backup at 6am?

It's SQL Enterprise 2012 for this particular machine.

Appreciate any insight you guys have

Merely setting the recovery back to FULL is not enough - you'd need to take at least a differential after setting back to FULL to bridge the gap between the time you changed to SIMPLE and the time you changed back to FULL - then your T-logs should be ok at 6am

Keep in mind that you will not be able to restore to a point in time after the change to SIMPLE and before the differential is taken.

Also, you say that you are taking T-log backups at the top of every hour.  So, you are basically saying that it's ok to lose up to 1 hour of data should a disaster occur.  Does this actually adhere to your company Recovery Point Objective (RPO)?  Please take a look at The 9 Letters That Get DBAs Fired.

And then take a look at Back Up Transaction Logs Every Minute. Yes, Really


Unexpected result from PostgreSQL information schema I have written a simple query that should show all table sizes of any given schema in human-readable format:
select table_name, pg_relation_size(quote_ident(table_name))
from information_schema.tables
where table_schema = 'my_schema'
order by 2

When I run this query in PgAdmin, I get the following error:
ERROR:  relation "my_table" does not exist
SQL state: 42P01

How can this error even be possible?  I have not changed the information_schema at all and, if a relation doesn't exist in the first place, why would it be in the information_schema ?   Any idea how this could happen?

The only reason I can think of, is if my_schema is not part of your search_path.
As you pass a table name without schema qualifying it to the function pg_relation_size() the table is searched in the default search_path. If it's not found, you'll get that error.
Use a fully qualified name instead:
pg_relation_size(format('%I.%I', table_schema, table_name))


Creating range for special fields in SQL Server I have two columns that look like this:

Start_Post  End_Post
----------  --------
102+20.45   153+19.22
120+21.25   220+25.30
...         ...

And I want to introduce a constraint to each column:

Start_Pos
Min Range: 100+50.30
Max Range: 150+20.65
End_Pos
Min Range: 150+60.30
Max Range: 500+20.75

All values are in feet and follow a special format. It's a construction convention and I am not an expert but to my knowledge, 1+00 would be 100 feet. Essentially, taking the above range of 100+50.30 to 150+20.65 would mean a distance of 4970 ft. What I did was just remove the plus sign and subtract the two numbers. Hope that made sense.

Is there a way to define this constraint whilst maintaining the format?

Assuming the digits to the left of the + are measured in units of 100, you could perhaps create constraints like this:

CREATE TABLE dbo.SurveyData
(
    StartPostStation int NOT NULL
    , StartPostPlus decimal(38,3) NOT NULL
    , EndPostStation int NOT NULL
    , EndPostPlus decimal(38,3) NOT NULL
    , CONSTRAINT StartMin
        CHECK ((StartPostStation * 100 + StartPostPlus) > 100*100+50.3)
    , CONSTRAINT StartMax
        CHECK ((StartPostStation * 100 + StartPostPlus) < 150*100+20.65)
    , CONSTRAINT EndMin
        CHECK ((EndPostStation * 100 + EndPostPlus) > 150*100+60.3)
    , CONSTRAINT EndMax
        CHECK ((EndPostStation * 100 + EndPostPlus) < 500*100+20.75)
);

However, this doesn't allow dynamic constraints, all rows in the table will be constrained to those measurements, which may in fact be exactly what you need.

If you need dynamically adjusting constraints for each independent row, you could do this:

CREATE TABLE dbo.SurveyData
(
    StartPostStation int NOT NULL
    , StartPostPlus decimal(38,3) NOT NULL
    , EndPostStation int NOT NULL
    , EndPostPlus decimal(38,3) NOT NULL
    , CONSTRAINT StartMin
        CHECK ((StartPostStation * PostUnits + StartPostPlus) > MinStartPost * PostUnits + MinStartPostPlus)
    , CONSTRAINT StartMax
        CHECK ((StartPostStation * PostUnits + StartPostPlus) < MaxStartPost * PostUnits + MaxStartPostPlus)
    , CONSTRAINT EndMin
        CHECK ((EndPostStation * PostUnits + EndPostPlus) > MinEndPost * PostUnits + MinEndPostPlus)
    , CONSTRAINT EndMax
        CHECK ((EndPostStation * PostUnits + EndPostPlus) < MaxEndPost * PostUnits + MaxEndPostPlus)
    , MinStartPost int NOT NULL
    , MinStartPostPlus decimal(38,3) NOT NULL
    , MaxStartPost int NOT NULL
    , MaxStartPostPlus decimal(38,3) NOT NULL
    , MinEndPost int NOT NULL
    , MinEndPostPlus decimal(38,3) NOT NULL
    , MaxEndPost int NOT NULL
    , MaxEndPostPlus decimal(38,3) NOT NULL
    , PostUnits int NOT NULL
);

INSERT INTO dbo.SurveyData (StartPostStation, StartPostPlus, EndPostStation, EndPostPlus
    , MinStartPost, MinStartPostPlus, MaxStartPost, MaxStartPostPlus
    , MinEndPost, MinEndPostPlus, MaxEndPost, MaxEndPostPlus
    , PostUnits)
VALUES (120, 49.2, 175, 80.5  --measurements
    , 100, 50.3, 150, 20.65   --valid start post range
    , 150, 60.3, 500, 20.75   --valid end post range
    , 100); --units per post

Selecting data would look like this:

SELECT StartPost = CONVERT(varchar(10), sd.StartPostStation) + '+' + CONVERT(varchar(50), sd.StartPostPlus)
    , EndPost = CONVERT(varchar(10), sd.EndPostStation) + '+' + CONVERT(varchar(50), sd.EndPostPlus)
FROM dbo.SurveyData sd;

Results, like this:

+------------+------------+
| StartPost  |  EndPost   |
+------------+------------+
| 120+49.200 | 175+80.500 |
+------------+------------+

The post columns have been split into two to enable us to use the correct data types to store numeric data.  If we try to store 175+80.50 in a single field, we end up using a varchar(x) column, which allows all kinds of possibilities for bad data, such as tee+27.-1, which are very difficult to comprehensively prevent.  So, we store the station in one column, and the offset from that station in the next column.  When presenting this data on screen or on reports, to humans, we'd use the concatenated version shown above.


Monitoring decimal values on Perfmon using User Settables I am currently using sp_user_counter1 to store database information on Perfmon, which can be recovered by AWS CloudWatch, but the values are Decimal, and the sp_user_counter1 parameter supplied must be an int.

So when I give the stored procedure a value of 12.34567 it report 12.00000 to the perfmon counter....

The sp code is:

    CREATE PROCEDURE sys.sp_user_counter1 @newvalue INT AS
    DBCC setinstance ('SQLServer:User Settable', 'Query', 'User counter 1', @newvalue)

Can I just call DBCC setinstance and give it the decimal value???
I never tried this because I didnt found anyone doing this, I dont know the consequences....

Can this be done?? Is this secure??

Here is the User Setabble documentation:
https://msdn.microsoft.com/en-us/library/ms187480.aspx

Running this:

CREATE PROCEDURE dbo.set_user_counter1
(
    @newvalue DECIMAL(18,2)
)
AS
BEGIN
    DBCC setinstance (
        'SQLServer:User Settable'
        , 'Query'
        , 'User counter 1'
        , @newvalue
    );
END;
GO

EXEC dbo.set_user_counter1 12.5

results in:

  Msg 2560, Level 16, State 9, Procedure set_user_counter1, Line 7
  Parameter 4 is incorrect for this DBCC statement.

Running the following shows you the "help" entry for the DBCC setinstance:

DBCC TRACEON (2588); /* show DBCC Help */
DBCC TRACEON (2520); /* show undocumented command help */
DBCC HELP ('setinstance')

This returns:

  dbcc setinstance (objectname, countername, instancename, value)

  DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Unfortunately, that is no help determining if value can only be an int.

In fact, the MSDN page you linked states the following:

  A counter can monitor any single integer value


Migrate Data and/or Schema from DBv1 to DBv2 I have a production database which has been plugging away for several months. I have been making modifications to a duplicate copy on a test/dev server. I've added some additional fields (null allowed) to several tables, as well as I've added some new tables and indexes to both new and existing tables.

My question is this: I am ready to take my test/dev database and push it into production. The problem is that I need to migrate all the data from my production database to my test/dev database. My first thought was to use SSMS to "IMPORT" data from production to test/dev, the problem I'm having is that even with "enable identity insert" I still get all types of foreign key constraint errors. This is a bit of a two-parter

Is there a better way to migrate schema changes from test/dev to production than the process I just outlined?
If not, is there a way to temporarly disable all insert constraints during the import?

I would be very happy to only migrate my test/dev schema to my production server, but I have not found any way to do this without manually going table by table, index by index, view by view, stored procedure by stored procedure... etc. I've seen some tools that claim to be able to do it, but I'm looking for a free/open source solution and haven't found anything.

note: database is small < 10gb, the test/dev database is fully truncated with zero records in every table.

It sounds like you've got this entirely back to front and upside down. Typically changes are tracked using scripts, which you would then apply to the production database. I've never encountered a case where it made sense to import production data to the next version created from development.

If your database isn't currently in source control, the articles linked to from Jeff Atwood's article Get Your Database Under Version Control are a good introduction. Also, the free Redgate ebook SQL Server Team Based Development includes a chapter on source control for databases.

If you've been making changes via GUI tools and don't have any record of what's changed your best bet is a database comparison tool, like Redgate SQLCompare or Apex SQLDiff. These tools will generate the scripts to upgrade your production database to match the schema of development.

If the boss won't part with the cash for a comparison tool, you could reconcile the changes manually using a diff tool like WinMerge or DiffMerge. Use SSMS Generate Script tools to script objects to individual files, then use the diff tool to identify differences. Finally, hand craft the necessary ALTER statements for any changes to tables.


PostgreSQL 9.2 tstzrange null/infinity CONSTRAINT CHECK I'd like to limit a PostgreSQL 9.2 tstzrange to valid dates at both ends. No NULLs nor 'infinity'.

Various revisions of this SQL isn't constraining '-/+infinity' input:

CREATE TABLE bill
(
  id serial NOT NULL,
  created_at timestamp with time zone NOT NULL DEFAULT now(),
  period tstzrange NOT NULL,
  -- other columns
  CONSTRAINT bill_pk PRIMARY KEY (id),
  CONSTRAINT bill_period_lower_not_null_ck CHECK (lower(period) IS NOT NULL),
  CONSTRAINT bill_period_upper_not_null_ck CHECK (upper(period) IS NOT NULL),
  CONSTRAINT bill_period_lower_not_infinity_ck CHECK (lower_inf(period) IS FALSE),
  CONSTRAINT bill_period_upper_not_infinity_ck CHECK (upper_inf(period) IS FALSE)
);

INSERT INTO bill (period) VALUES (tstzrange (NULL, NULL));
INSERT INTO bill (period) VALUES (tstzrange ('2000-05-10', '2015-02-22');
INSERT INTO bill (period) VALUES (tstzrange ('2000-05-10', now());
INSERT INTO bill (period) VALUES (tstzrange ('infinity', now());
INSERT INTO bill (period) VALUES (tstzrange ('-infinity', now());
INSERT INTO bill (period) VALUES (tstzrange (now(), 'infinity');

psql (9.2.3)
Type "help" for help.

test_1=> SELECT id, period FROM bill;
 id |                           period
----+------------------------------------------------------------
  1 | ["2000-05-10 00:00:00+01","2015-02-22 00:00:00+00")
  2 | ["2000-05-10 00:00:00+01","2013-10-16 13:20:01.793803+01")
  4 | [-infinity,"2013-10-16 13:20:18.993038+01")
  5 | ["2013-10-16 13:21:14.208279+01",infinity)
(4 rows)

Changing the *_inf(period) IS FALSE checks to TRUE rejects valid dates.

Range functions in the manual

Thoughts?

The simple checks work as desired:

CHECK ( lower(period) > '-infinity' )

and

CHECK ( upper(period) < 'infinity' )

Therefore, you can have:

CREATE TABLE bill
(
  id serial NOT NULL,
  created_at timestamp with time zone NOT NULL DEFAULT now(),
  period tstzrange NOT NULL,
  -- other columns
  CONSTRAINT bill_pk PRIMARY KEY (id),
  CONSTRAINT bill_period_lower_not_null_ck CHECK (lower(period) IS NOT NULL),
  CONSTRAINT bill_period_upper_not_null_ck CHECK (upper(period) IS NOT NULL),
  CONSTRAINT bill_period_lower_not_infinity_ck
    CHECK ( lower(period) > '-infinity' ),
  CONSTRAINT bill_period_upper_not_infinity_ck
    CHECK ( upper(period) < 'infinity' )
);


Does INFORMATION_SCHEMA always contain up-to-date metadata? This query prints the size (in terms of data and indexes) of the tables in mydatabase:

SELECT table_name "Table name",
round(((data_length)/1024/1024),2) "Data size",
round(((index_length)/1024/1024),2) "Index size"
FROM information_schema.TABLES
WHERE table_schema="mydatabase" AND data_length>1000000
order by table_name INTO OUTFILE '/tmp/mydatabase_values'
FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';

Sizes are printed in Mb, and only tables larger than 1Mb are considered. Tables are listed in alphabetical order and the output is saved into a CSV file.

Running this query at different points in time showed - obviously - different results as data changed in the database.  However, just in the last few weeks the query produced identical results.  Does it mean that the database hasn't changed much in size (as you can see, rounding is done at the 10Kb boundary) or is there something I am really missing?  The question might sound absurd, but do the views in INFORMATION_SCHEMA always hold up-to-date metadata?

Note: no, I am not reading each time the same CSV file by mistake.

Edit: all tables are InnoDB, and innodb_file_per_table=1.

Yes, the data is up-to-date.  But don't read too much into the numbers.

InnoDB, especially, preallocates space for future rows.  So, it is quite possible to look at the exact size of a table, insert dozens of rows (maybe even thousands), then look at the size again -- and see exactly the same Data_length and Index_length.

When you first create an InnoDB table and insert one small row, the data length will be 16384 bytes (one 16KB block).  As you add more rows, eventually that block will overflow, and another block will be added.  Later (as the table exceeds some threshold), the table will grow in units of (I think) 8MB (an "extent").  This will allow even more rows to be added with no change in disk footprint.

Also, was the table created with innodb_file_per_table ON or OFF?  This controls whether the stuff goes into the communal ibdata1 file or the table's own .ibd file.

And, as @jkavalik points out, DELETE and UPDATE and ALTER can mess with the size, again not necessarily in predictable ways.

Edit

Those are the "disk footprint" size.  Compare to what you can see looking at the filesystem.

A 1-row table with one 4-byte INT takes at least 16384 bytes of disk space.  If you are saying that "4" is the real size, then I have been answering the wrong question.  I say that 16KB is the real size.

There is overhead for each column, for each row, for each block, for BTree structure, for each extent, for indexes, etc.  And InnoDB allocates no less than 16KB at a time.  And room for 'undo' copies of rows.  Hence, "4" turns into "16384".

As a "Rule of Thumb", if you add up the data sizes (4 for INT, etc) of the columns in a row, multiply by the number of rows, then multiply by something between 2 and 3, you are likely to get the "size" of the table.  (The 1-col, 1-row example, of course, is an end-case that exceeds 3x)


SQL Server Agent appears to be treating sysprocesses (and dm_exec_sessions) differently I have a watchdog process that runs via a SQL Agent job to manage some custom tasks. One of the checks is that the process no longer exists:

UPDATE R
SET IsOrphaned = 1, DebugTimestampUtc = GETUTCDATE()
FROM
    RequestTable AS R
    LEFT JOIN sys.sysprocesses AS P ON
        P.spid = R.SPID
WHERE P.spid IS NULL

In SSMS, I run this statement then close the query window before it completes (cancelling the query):

INSERT INTO RequestTable(SPID, CreateTimestampUtc, ...)
SELECT @@SPID, GETUTCDATE(), ...
WAITFOR DELAY '00:00:30.000'

When I manually run the query to flag orphans in SSMS it matches on the row, but my job does not, as is evidenced by the RequestTable not getting the row marked as orphaned. The job history shows it running as scheduled without any errors, taking negligible runtime (< 1 second).

Furthermore, when I augment the orphan detection statement to also find records where P.status = 'sleeping' it immediately detects when I cancel the INSERT ... WAITFOR ... query (and keep the query window open).

Is there some subtle behavior of SQL Server causing this apparently-inconsistent state of the sys.sysprocesses table between SSMS and SQL Agent? I have also tried with and without a NOLOCK query hint on RequestTable.

I'm running on SQL Server 2014 in compatibility mode for 2008R2.

(Note that using sys.dm_exec_sessions instead still behaves the same)

Thanks.

P.S. The purpose of this is to serialize asynchronous processes that have concurrency issues. Perhaps there is also a better manner to do this (without being able to fix the asynchronous processes)?

As pointed out in the comments by Aaron, the current session was being caught, because they are reused in the connection pool.

The solution is to filter it out:

AND session_id <> @@SPID


Aggregate function to count occurrences of specific word I have a table upon which I'm performing a GROUP BY statement. One of the columns in my table can contain one of 5 different words (essentially an enumerated type). Is there a way I can use an aggregate to get the occurrences of each enum, but display each count in a new column?

For example, given the table:

+---------+
|id | enum|
+---------+
| 1 | A   |
| 1 | B   |
| 1 | C   |
| 1 | A   |
+---------+

I'd like to get something like this after grouping by id:

+------------+---------+---------+
|id | a_count| b_count | c_count |
+------------+---------+---------+
| 1 |   2    |    1    |    1    |
+--------------------------------+

Sure, have 3 SUMs of the needed values converted to 1, and others to 0.

select
  id,
  sum(case when enum = 'A' then 1 else 0 end) as a_count,
  sum(case when enum = 'B' then 1 else 0 end) as b_count,
  sum(case when enum = 'C' then 1 else 0 end) as c_count
from
  table
group by
  id
;


Explain doesn't show indexed column as key Consider the following table structure

CREATE TABLE `games` (
 `game_id` bigint(20) NOT NULL AUTO_INCREMENT,
 `players_no` tinyint(4) DEFAULT NULL,
 `startedon` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
 `game_type` enum('R','C') NOT NULL DEFAULT 'R',
 `expirydatetime` datetime NOT NULL,
 `users_info` text NOT NULL,
 `lastupdate` datetime NOT NULL,
 `count_temp_user` int(1) NOT NULL DEFAULT '0',
 `msg_pid` varchar(3) NOT NULL,
 PRIMARY KEY (`game_id`),
 KEY `startedon` (`startedon`),
) ENGINE=InnoDB AUTO_INCREMENT=11009966 DEFAULT CHARSET=utf8

Now,Consider the following query

SELECT * FROM `games` WHERE DATE(games.expirydatetime)<CURDATE() AND `players_no` = 2

When i Explain the above query i have the following result

id|select_type|table|type|possible_keys|key |key_len|ref|rows|filtered|Extra
 1 |SIMPLE     |games|ALL |NULL         |NULL|NULL|NULL|314|100.00|Using where

If i add an index on expirydatetime,i still don't see expirydate in key column after explain.Any Explainations for this behavior?Is it because expirydatetime is used within a mysql in-built function?

Will mysql still consider the index on expirydatetime?

First, the possible_keys shows null because there is no index on any of the two columns used in the WHERE clause. So, there is no useful index to even be considered for this query.

But even when you add an index on (expirydatetime), the situation is still not good. Because when you place a column inside a function in a condition, like the DATE(games.expirydatetime)<CURDATE(), you are basically forbidding the use of indexes. While it might be possible to use the index on the column, it would still had to do a full index scan (because of the function use) and then access the table for the rows that matched. So the optimizer chooses to do a table scan instead.

To make the query use indexes and be more efficient, change the condition to:

WHERE expirydatetime < CURDATE()
  AND players_no = 2

and add a composite index on (players_no, expirydatetime)


sp_help_revlogin for contained database users? I've been using sp_help_revlogin for a long time to transfer server principals from one SQL instance to another.

Is there a sp_help_revlogin equivalent that can output create scripts for partially contained database users? I understand that if I backup/restore the contained user comes along, but I have a need to deploy users from one contained database to another, different, contained database.

While David is right, the contained user will automatically come along with the contained database if you backup/restore to another location (that's kind of the point), I can see some use cases for this, like creating the same contained user across a set of databases, with the same password, without having to know (or change) the password. Or deploying the same user that has been created on dev / QA / test / staging environments to the production copy of the database, without moving the database.

If you know (or can change) the password, of course, scripting out all the contained users in a database is pretty simple, just take this output and replace whatever with the desired password for each contained user:

SELECT N'CREATE USER ' + QUOTENAME(name)
  + N' WITH PASSWORD = N''whatever'';'
FROM sys.database_principals
WHERE authentication_type_desc = N'DATABASE';

If the requirement is that you don't know the password, though, it is a bit more complex. Based mostly on this post by Argenis Fernandez (which is currently not very snappy):

First, make sure there isn't a server-level login with the same name as the contained user.
Connect to the instance using the Dedicated Administrator Connection
(see this post for troubleshooting steps if you can't).
Switch context to the contained database where your existing user lives. I'm going to assume the contained user is named bob.
Run the following:

USE OldContainedDatabase;
GO

DECLARE @sql nvarchar(max);

SELECT @sql = N'CREATE LOGIN ' + QUOTENAME(name)
  + N' WITH PASSWORD = ' + CONVERT(varchar(256), password, 1)
  + N' HASHED;'
FROM sys.sysowners
WHERE name = N'bob';

-- if the target contained database is on the same server:
EXEC sys.sp_executesql @sql;

-- otherwise:
SELECT @sql; -- run this output on the target server

Connect to the target contained database (no longer need DAC), create the user from the server-level login, migrate that user to a contained user, and drop the login:

USE NewContainedDatabase;
GO

CREATE USER [bob] FROM LOGIN [bob];
GO

EXEC sys.sp_migrate_user_to_contained
  @username     = N'bob',
  @rename       = N'keep_name',
  @disablelogin = N'disable_login';
GO

DROP LOGIN [bob];

I just tested this on my machine and was able to log in to both databases explicitly using the same username and password, but users would have been able to do the same even if I didn't happen to know their password throughout this whole process.


Downgrading Enterprise to Standard Edition I'm being asked to downgrade some non critical database environments from Enterprise Edition 2012 to Standard Edition 2012. Due to ongoing licensing costs.

Can I do this? What are the drawbacks?

Is there a process similar to upgrading I can do? Can I just run the standard edition install as an upgrade?

We currently don't have development environment for this databases.

I do not believe there is a "downgrade path" that's officially supported for what you want to do. Nothing that is full proof or baked in, in other words. I would take full backups of all databases, then uninstall and reinstall (also reinstall required SPs). Microsoft's supported upgrade paths: https://msdn.microsoft.com/en-us/library/ms143393.aspx


Change character limit of SQL Agent Operators I want to add more than 100 characters worth of email addresses to the SQL Agent Operators alerting.

For example: emaione@example.com;emailtwo@example.com;emailthree@example.com;....

I tried to get around the 100 character limit by altering sysoperators email_address column as such

ALTER TABLE sysoperators
ALTER column email_address NVARCHAR(1000);

and then creating my operator, but the addresses are still cut off at 100 characters?

Just because you can does not mean you should. Please do not apply the changes below. This answer is only to show you why you should not change system tables or system procedures and what can go wrong.

msdb.dbo.sp_add_operator

When adding the operator with the ssms gui, msdb.dbo.sp_add_operator is called behind the scenes.

The email address parameter used is also nvarchar(100).

In theory, you could adapt the procedure by altering it

And then changing the @email_address parameter to nvarchar(1000)

If I then run it wih 384 characters (non gui)

USE [msdb]
GO
EXEC msdb.dbo.sp_add_operator @name=N'test',
        @enabled=1,
        @pager_days=0,
        @email_address=N'emaione@example.com;emailtwo@example.com;emailthree@example.com;emaione@example.com;emailtwo@example.com;emailthree@example.com;emaione@example.com;emailtwo@example.com;emailthree@example.com;emaione@example.com;emailtwo@example.com;emailthree@example.com;emaione@example.com;emailtwo@example.com;emailthree@example.com;emaione@example.com;emailtwo@example.com;emailthree@example.com;'
GO

Getting the length:

select LEN(email_address)  as email_length
from sysoperators
where name = 'test';

Shows the 384 characters are there.

email_length
384

However, one of the things instantly breaking is the operator list when opening it via ssms:

Due to ssms running this query:

create table #tmp_sp_help_operator
(id int null, name nvarchar(128) null, enabled tinyint null, email_address nvarchar(100) null, last_email_date int null, last_email_time int null, pager_address nvarchar(100) null, last_pager_date int null, last_pager_time int null, weekday_pager_start_time int null, weekday_pager_end_time int null, saturday_pager_start_time int null, saturday_pager_end_time int null, sunday_pager_start_time int null, sunday_pager_end_time int null, pager_days tinyint null, netsend_address nvarchar(100) null, last_netsend_date int null, last_netsend_time int null, category_name nvarchar(128) null)
insert into #tmp_sp_help_operator exec msdb.dbo.sp_help_operator

SELECT
tsho.name AS [Name],
'Server[@Name=' + quotename(CAST(
        serverproperty(N'Servername')
       AS sysname),'''') + ']' + '/JobServer' + '/Operator[@Name=' + quotename(tsho.name,'''') + ']' AS [Urn],
CAST(tsho.enabled AS bit) AS [Enabled]
FROM
#tmp_sp_help_operator AS tsho
ORDER BY
[Name] ASC

drop table #tmp_sp_help_operator

Showing why we should not play around with system tables & procedures.

Who knows what will work and what will break when changing the table & the procedure.

A distribution list as vonPryz mentioned would be a much better solution.


Postgres - How to get Multi-Count Query using a Where Clause for a Join Table I have the following query that gets the counts across multiple tables that are joined:

select
    count(distinct e.planningitemdata->>'receiverDuns') as supplierOrCustomer,
    count(distinct e.planningitemdata->>'shipTo') as shipTo,
    count(distinct e.planningitemdata->>'productId') as product,
    count(distinct eo.orderlineid) as orderline,
    count(distinct e.planningitemid) as planningitem
from
    eventplanningitem e
join
    eventorderline eo
        on e.eventid = eo.eventid
join
    orderline o
        on eo.orderlineid = o.id
        and o.deliveryrequesteddate between '2018-03-06' AND '2018-05-06'
where
    e.eventId = '9f6d3d50-05ca-4441-a4e4-24de2b52de5b'

I want to filter the "orderlineid" counts based on a date filter, but my issue is that when I try to apply it to the join as shown above, it 0's out all the results of all the other counts. Same thing happens if I try to add a where clause to the end. If I make the orderline table a left outer join, I get the count of the orderlines that don't match the date criteria.

Result:

0;0;0;0;0

A similar version produces the same thing:

select
        count(distinct e.planningitemdata->>'receiverDuns') as supplierOrCustomer,
        count(distinct e.planningitemdata->>'shipTo') as shipTo,
        count(distinct e.planningitemdata->>'productId') as product,
        count(distinct eo.orderlineid) as orderline,
        count(distinct e.planningitemid) as planningitem
    from
        eventplanningitem e
    join
        eventorderline eo
            on e.eventid = eo.eventid
    join
        orderline o
            on eo.orderlineid = o.id
            and o.deliveryrequesteddate between '2018-03-06' AND '2018-05-06'
    where
        e.eventId = '9f6d3d50-05ca-4441-a4e4-24de2b52de5b'
        and (o.deliveryrequesteddate >='2018-03-06' and o.deliveryrequesteddate <= '2018-05-06')

I get counts if I make the orderline join a left outer join, but it includes the orderLine records that actually fall outside the date criteria so it's also not correct.

I want to avoid breaking this into multiple queries but I wanted to check if I'm missing something before going that route.

It looks like you can use COUNT(DISTINCT expression), so I'd try replacing

count(distinct eo.orderlineid) as orderline,

with this

COUNT(DISTINCT CASE WHEN     o.deliveryrequesteddate >= '2018-03-06'
                         and o.deliveryrequesteddate <= '2018-05-06'
                 THEN eo.orderlineid
                 ELSE NULL
               END) as orderline,

NULL values are not counted by COUNT, and the DISTINCT should still ensure you're only counting each orderlineid once.