Notification of row entries I have SQL Server 2012 machine housing a database running an off the shelf application with a users table. This users table contains PII enterable fields. Per requirement none of our databases can house PII. I have a job set up to scrub any rows not null that runs daily. I am wondering if there is a way to set up a job or trigger that can alert administrators anytime data is entered into one of these PII rows and perhaps have the scrub job run automatically anytime one of the PII fields is updated with anything other than NULL?

An option would be to create an after insert/update trigger which would join the base users table with the virtual inserted table and force the PII columns to be null (or other scrubbing logic).  Here is a very simple example:

--create test table
drop table if exists [dbo].[UserTable]
CREATE TABLE [dbo].[UserTable] (
    [id] [int] NULL
    ,[BirthDate] DATE NULL
    ,[Street1] VARCHAR(30) NULL
    ,[Street2] VARCHAR(30) NULL
    ,[SSN] VARCHAR(9) NULL
    ,[OtherColumn1] VARCHAR(100)
    ,[OtherColumn2] VARCHAR(100)
    ,[OtherColumn3] VARCHAR(100)
    ,
    ) ON [PRIMARY]
GO

--create after insert/update trigger to force PII fields to null
CREATE TRIGGER [dbo].[UserTable_InsertedOrUpdated] ON [dbo].[UserTable]
AFTER INSERT
    ,UPDATE
AS
BEGIN
    SET NOCOUNT ON

    UPDATE ut
    SET ut.birthdate = NULL
        ,ut.Street1 = NULL
        ,ut.Street2 = NULL
        ,ut.SSN = null
    FROM UserTable ut
    JOIN inserted i ON i.id = ut.id
END
GO

--test an insert
INSERT INTO UserTable (id,Birthdate,Street1,street2,SSN,OtherColumn1,OtherColumn2,OtherColumn3)
VALUES (1,'1960-01-01','Street1','Street2','111111111','OtherColumn1','OtherColumn2','OtherColumn3')
INSERT INTO UserTable (id,Birthdate,Street1,street2,SSN,OtherColumn1,OtherColumn2,OtherColumn3)
VALUES (2,'1960-01-01','Street1','Street2','111111111','OtherColumn1','OtherColumn2','OtherColumn3')

--test an update
UPDATE UserTable
SET SSN = '999' where id = 1

--check the contents of the table
SELECT *
FROM UserTable


SQL Server Impersonation is just NOT working I simply would like a very limited user, in its own database, to call an update procedure on our database to tell us that it has updated a table, which it has access to.

I have allowed the user login ("Doc") the "Impersonate any login" permission.  Doc also has impersonate rights on the "App" login, which exists despite what the error messages seem to say.

And I inserted the following statement near the beginning of the proc

EXECUTE AS LOGIN = 'App';

"App" is in the db_owner of the database that I want to update. I was running into an issue with the WITH EXECUTE AS 'App' permission on the proc I was using, because our canned audit procedure calls procedures in another database, and I had read that such a permission was confined to the current database.

But for some reason when I try the EXECUTE AS LOGIN statement it tells me that this very much used login cannot be used.

Cannot execute as the server principal because the principal "App" does not exist,
this type of principal cannot be impersonated, or you do not have permission.

So, if this is telling me right,

logins aren't the type of principal that can be impersonated, or
despite the check boxes on the UI, "Doc" does not have the right to impersonate the user "App", or
The login that my application uses 700,00 times a day, "does not exist".

Or this is just broken.

I'd prefer to give "sudo" type rights to limited users, but if I have to, I'll start traversing through the tree giving every single right the "limited" user needs for 24-hour access to table modifications which are kind of like the updates I want him to make. Despite that all I want him to do is call a proc that I created, update a particular column in a table in the Application database and log the result if it goes sideways.

UPDATE: Base on what srutzky says below, I tried turning Trustworthy on in a restricted database with a new user and a new login and the rights to impersonate, and it worked. I guess I have to weigh the risk between marking a database as trustworthy and creating users in every database that login will touch.

If your user does not need elevated permissions for ad hoc queries, then you can actually do better with Module Signing than with Impersonation. Impersonation comes with various complexities, especially when dealing with multiple Databases as that then requires setting TRUSTWORTHY to ON, something which is generally ill-advised as it is a security risk. Module Signing allows for more granular control over what permissions are being effectively granted, and works across Databases without needing to enable TRUSTWORTHY. I have explanations and examples of this across various answers here. One of the most recent ones, and one which includes a detailed walk-through of this cross-Database setup, is:

Stored procedure security with execute as, cross database queries, and module signing

The general concept is this:

Create a Certificate in master
Create a Login from that Certificate
Grant any required Server-level permissions to that Login

Create that same Certificate in Database A
Create a User in Database A from the Login
Grant Certificate-based User any necessary Database-level permissions
Sign one or more Stored Procedures / Functions in Database A with that Certificate
Grant real user Execute permission on the Stored Procedure(s) / Function(s)

Create a User in Database B from the Login
Grant Certificate-based User necessary permissions in Database B

In this setup, the real user/person doesn't actually have any elevated permissions. Only the code that has been signed with the Certificate has the permissions, and it can only do what the code inside it says to do. And if someone tries to change that code to do something else or more, then the module loses the signature and no longer has any elevated permissions at all. Which forces someone to resign the module, which is a good indication that the code needs to be reviewed.

UPDATE

  I have to weigh the risk between marking a database as trustworthy and creating users in every database that login will touch.

There is no real difference there. Using Impersonation, if you are creating an App User in each DB for the App Login, then it is the same to create the Certificate-based User in each DB. It is one line of code to CREATE USER [App] FROM Login [App];. BUT, if you don't need to create an App User because the App Login is in the sysadmin fixed server role, then you can just as easily place the Certificate-based Login in the sysadmin fixed server role and most likely not need to create and of the Database-level Users. However, you still end up with an account that is likely more privileged than it needs to be, just to avoid creating those Users.


Change Data Capture SSIS not picking changes I am trying to implement change data capture in SQL Server 2012. I enabled it at database and table level. when i try to update the table, CDC_CT captures the changes. However when i am using SSIS CDC components, it doesnt pick anything.

the version of SQL server is:
Microsoft SQL Server 2012 (SP3-CU10-GDR) (KB4057121) - 11.0.6615.2 (X64)
    Jan  9 2018 21:26:48
    Copyright (c) Microsoft Corporation
    Enterprise Edition (64-bit) on Windows NT 6.3  (Build 9600: ) (Hypervisor)

Can someone please guide?

More information here: https://stackoverflow.com/questions/51959201/change-data-capture-cdc-ssis-not-working

There are some issues with SP3 in sql server 2012. As soon as I installed SP4 this issue went away.


Single server / instance mongodb deployment I need a database for backends (for iOS apps). Those are mostly low-load demand applications and I like mongo for it's schemaless-ness. I'm looking to host both db and app server on a small digital-ocean instances.

I plan to:

run it under systemctl on debian probably
have periodic backups (not sure yet how)
make sure it's only accessible to localhost

Anything else I need to do, like maybe enable journaling? (if yes, how do I do it?). What else do I need to do so that my single-server mongod production deployment is decent?

I understand you want to deploy single instance of MongoDB for production.  This is the the recommendation from MongoDB.

  Use standalone instances for testing and development. Do not use these
  deployments, which lack replication and high availability, for
  production systems. For all production deployments use replica sets.

Details about installing MongoDB community edition here.

Details about backup here.

To enable journaling, start mongod with the --journal command line option.
For 64-bit builds of mongod, journaling is enabled by default.

Details about journaling here and here.


postgres: Converting '' into NULLS I'm trying to run this query to change my latitude coordinates, which is saved as  text into a double precision

ALTER TABLE mapdata ALTER COLUMN lat TYPE double precision USING (lat::double precision);

However, I keep getting this error: invalid input syntax for type double precision: ""

My assumption is that there is a blank entry and that postgres doesn't know to treat it as a double precision. How would I make this out to be a NULL or is there another way I can approach this to turn my lat column into a double precision? Thanks!

You have two options:

you change the empty strings in the original data to NULLs and then change the type
you handle them while changing the type

The first one would be a simple

UPDATE table SET col=NULL where col='';

but this naturally takes time and is a bit unnecessary since you're changing the type after that.

Second way is to handle the empty strings while converting

ALTER TABLE mapdata
  ALTER COLUMN lat TYPE double precision
    USING (NULLIF(lat, '')::double precision);

The NULLIF will return NULL if the lat column is an empty string, otherwise it'll return the lat column value. This will be a lot more efficient than to first change the source data.


inserting a special character into a table I have a few characters that are being replaced by a ?. How do you keep the original character in the table? The character that I am trying to insert is a ɬ; Latin Small Letter L With Belt. Is it possible to add this character into the table?

It is possible to add that character as long as either:

the destination column is NCHAR / NVARCHAR / XML (NTEXT also works, but has been deprecated since the release of SQL Server 2005 so don't use it),
OR

the destination column is CHAR / VARCHAR and the Collation of the column uses a Code Page that supports the character(s) in question.

So, make sure to do the following:

Prefix the string with N (not required if the current Database's default Collation uses a Code Page that supports the character, but helps in that it removes the Database's default Collation from being a factor that could cause this issue -- reason being that if the current DB's default Collation uses a Code Page that doesn't support the character, then it will be converted to either a "best fit" match, or ? if no best fit match can be found, while it is being held in a variable or string literal).
Along those same lines, if this is coming into a stored procedure or parameterized query batch via a parameter and the parameter is of type CHAR or VARCHAR, then the current Database's default Collation does not use a Code Page that supports the character, so switch to using NVARCHAR / NCHAR as the parameter datatype.
If the destination column is VARCHAR / CHAR, then be sure that the Collation of the column uses a Code Page that supports that character.
If the destination column is VARCHAR / CHAR and you cannot find a Collation that uses a Code Page that supports the character(s) in question, then you need to convert the column to NVARCHAR / NCHAR so that it can store any character.   ( This is what worked for the O.P. )

For more info on working with Collations, please visit: Collations.info


SSMS not coloring tables, columns and variables I use SSMS 2012 v11.0.5058.0.

MSDN says "System table, view, or table-valued function" should have green color. I'm used to have green color in variables and a few other token types too. But my SSMS keeps them black as raw values.

I followed Change Font Color, Size, and Style and tried to set default, but it didn't solve. I also tried to set them manually but didn't find them to select.

Any idea what may be the issue?

This happens in both cases of opening an .sql file or having SSMS create a select query for a table. Even when there are [] around the column name. It remains even after restarting Windows.

Colors are controlled via the options dialog box, which you linked to in your question.

Check the following, to see what it shows in the "Sample" box:


Frequency distributions segmented by a field Some background:
I have a sample population data file. Each record in the data file has a frequency weight (FIELD NAME: wgt) indicating how many times the records needs to be replicated to get the true population. The data is set up in Microsoft SQL 2008 R2. It is usually a good practice to generate frequency distribution of the weights to see if one particular type of record is being over-represented in any way - thus help identify trends/ outliers. This task is straightforward in SQL:

SELECT wgt, COUNT(*) FROM tablename
GROUP BY wgt

Challenge:
I want to further segregate these frequencies based on other values. Say a frequency distribution of weights for different household sizes. One way to achieve this is to use different where conditions in the statement above:

SELECT wgt, COUNT(*) FROM tablename
WHERE household_size=x --x being the desired segment
GROUP BY wgt

But is there any way to create a single table with all of the different segments? Something like this:

WGT |   SIZE1       SIZE2       SIZE3       SIZE4
--------------------------------------------------
1   |    2,034      1,025       502         234
2   |    215        253         142         23
3   |    31         25          21          34
4   |    7          1           3           7
5   |    5          NULL        2           5
6   |    1          1           NULL        NULL
7   |    NULL       1           NULL        NULL

A fancier solution I am looking for: A stored procedure where I can just specify the variable (say household size or household income) and the code should be able to generate separate frequency distributions for all distinct values for that variable as a view/table.

Any thoughts folks?

For the first challenge, are you looking for something like this?

SELECT wgt, SUM(Case when household_size=1 then 1 else 0 end) AS SIZE1
    , SUM(Case when household_size=2 then 1 else 0 end) AS SIZE2
    , SUM(Case when household_size=3 then 1 else 0 end) AS SIZE3
    , SUM(Case when household_size=4 then 1 else 0 end) AS SIZE4
FROM tablename
GROUP BY wgt

Now for the fancier challenge, where @column will be your sp parameter:

DECLARE @column VARCHAR(100);
SET @column = 'household_size';
DECLARE @main_SQL VARCHAR(4000);

SET @main_SQL =
    'DECLARE dynamic_cursor CURSOR FOR
    SELECT DISTINCT ' + @column + '
    FROM tablename
    ORDER BY ' + @column + '

    --Now we will be using a cursor to iterate over
    DECLARE @value INT;
    OPEN dynamic_cursor
    FETCH NEXT FROM dynamic_cursor INTO @value

    DECLARE @sql VARCHAR(2000);
    SET @sql = ''SELECT wgt'';
    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @sql = @sql + '', SUM(Case when ' + @column + '='' + CAST(@value AS VARCHAR) + '' then 1 else 0 end) AS SIZE'' + CAST(@value AS VARCHAR)
        FETCH NEXT FROM dynamic_cursor INTO @value
    END
    SET @sql = @sql + '' FROM tablename GROUP BY wgt''

    close dynamic_cursor
    DEALLOCATE dynamic_cursor

    EXEC(@sql) '

EXEC(@main_SQL)

This part selectes the distinct values of the tablename's column received into a cursor

DECLARE dynamic_cursor CURSOR FOR
SELECT DISTINCT ' + @column + '
FROM tablename
ORDER BY ' + @column + '

Next we will iterate over the distinct values to dynamically build up a query that will look just like the one at the top of my post, so here is the basis for it:
start the select:

SET @sql = ''SELECT wgt''

the iterative part which dynamically adds the successive , SUM(Case when household_size=n then 1 else 0 end) AS SIZEn to the select (note that @value will be the distinct values that we got selected into the cursor):

SET @sql = @sql + '', SUM(Case when ' + @column + '='' + CAST(@value AS VARCHAR) + '' then 1 else 0 end) AS SIZE'' + CAST(@value AS VARCHAR)

finish the select:

SET @sql = @sql + '' FROM tablename GROUP BY wgt''


Deploying to an existing database from VS 2010 on TFS I am trying to find a way in VS2010 to add a few objects to an existing database without scripting out all of the existing objects. This is a maintenance project that is adding a table, altering an existing table by adding a column, creating a foreign key constraint between them and altering a stored proc to use a new column in the existing table rather than a hard coded case statement. I tried a database project and then converted it to SSDT in an attempt to find a way to not drop the database.

The scripts generated from this database project generate drop and create statements for the database. If I manually remove the database portion of the scripts, the result is exactly what I need.  Is there a way to force the project to NOT drop and recreate the database, or is there a project type I can use instead?

For SSDT, when you choose Publish to generate a script the advanced options include "Always re-create database". Untick that and you're good to go.

Same option applies if you do a local debug build.

Similar options buried in the old VS2010 Database Project options somewhere.

To be honest, from the description of what you're doing SSDT and/or VS2010 database projects are overkill. Scribble a script in SSMS and be done with it.


Slow Linked Server INSERTs on SQL Server 2014 My local and remote servers were all using SQL Server 2008 R2 but the local ones were recently upgraded to SQL Server 2014 and the performance of Remote Queries has been crippled (literally from minutes to hours).

It appears that the problem is with sending data to the Remote Server so, as long as I am just reading data from the Remote Server, it performs fine except that, if the query is based on data at the local end, it seems to be effectively sending data to the remote server. I cannot setup the Linked Servers on the Remote Server end so I cannot make the remote server pull from the local one.

In most cases I can rewrite the queries to pull the data from the remote server into a local table, filter the local data, and then just send the relatively few updates to the remote server - Still slower than it was, but functional.

However, when doing anything involving lots of records, it is unuseable. The classic example is this query (originally it was doing a more complex query on the local server so I changed it to first populate a local copy of the final table so that I can do the simplest possible query for the INSERT);

INSERT INTO [REMOTE-DB].Databasename.dbo.Test_Table
SELECT Person_Nbr, FullName, Birth
FROM Test_Table WITH (nolock)

Even when I try OPENQUERY it seems no faster;

INSERT OPENQUERY ([REMOTE-DB], 'SELECT Person_Nbr, FullName, Birth FROM Databasename.dbo.Test_Table')
SELECT Person_Nbr, FullName, Birth
FROM Test_Table WITH (nolock)

If it weren't that this worked almost instantly on the older version of SQL Server, I would accept this as a fact of life but there is such a huge difference in performance that it seems that there must be some obvious fix that I am missing. I don't have control of the local servers but the DBA for them claims that the database configuration is consistent with the old setup.

Do you have any ideas what I can do to fix this or what I can point the DBA at?

"My local and remote servers were all using SQL Server 2008 R2 but the
  local ones were recently upgraded to SQL Server 2014 and the
  performance of Remote Queries has been crippled (literally from
  minutes to hours)."

When Sql Server performance degrades substantially, my first response is usually "what has changed?"

Well - you just upgraded the local server to Sql Server 2014 which introduces a new Cardinality Estimator (CE).

As quoted in the blog (Careful Testing the 2014 Cardinality Estimator with Trace Flags):

  When you migrate to SQL Server 2014, rather than turning on the new
  cardinality estimator right away, it's recommended to stick with the old
  cardinality estimator initially by leaving your database in SQL Server
  2012 compatibility mode. Let the database settle down for a few weeks,
  let folks air out their grievances, and give yourself some time to
  make sure the system is working at least as well as the old system.

You say that "the DBA (for the local server) claims that the database configuration is consistent with the old setup".  So, how can you tell if you are being impacted by the new CE?  There are a few ways.

First,
run this query against the local instance.

select name, compatibility_level from sys.databases

If the compatibility level is 120 or higher, you certainly 'might' be using the new CE (assuming it has not been overridden by using session or global trace flags, or QUERYTRACEON hints on the query that force the use of the older CE.)

Another way to see if the new CE is being used is to look at the execution plan XML for a 'local' query and locate the string 'CardinalityEstimationModelVersion'.

If it's 70, you're using the old CE.  If it's 120, you're using the new CE.

My suggestion is to 'first' figure out whether you are being bitten by the new Sql Server 2014 CE.

There is quite a bit of information on the Internet about this topic.

Another helpful link is The SQL 2014 Cardinality Estimator Eats Bad TSQL for Breakfast.


Error "ORA-12726:unmatched bracket in regular expression" while trying to separate a part of a string from other part I have a string like this :

v_sgtring:'sum:[sum(x)>1 and sum(y)>2 and sum(z)>3],where:[n=1 and m=2]'

and I need to separate sum:[] from where:[] and assign them to different variables like this:

v_sum_string:'sum(x)>1 and sum(y)>2 and sum(z)>3';
v_where_string:'n=1 and m=2';

What I've written so far is:

select regexp_substr('sum:[sum(x)>1 and sum(y)>2 and sum(z)>3],where:[n=1 and m=2]',
                 'sum:[]+,',
                 1,
                 1)
from dual;

but it gives me error ORA-12726:unmatched bracket in regular expression and I can not understand where my problem is .
I was wondering if you could help me with this and if you have better opinion about the original string (for example it would  be easier to separate if it was parentheses) please say

thanks very much in advance

The error is because [ and ] are part of regex syntax, which makes handling them in a regex tricky as you have to escape them, e.g. 'sum:\[\]+,'
I found it easier to replace them on the fly with { and } before applying regexp_substr to the result:
with demo (str) as
      ( select 'sum:{sum(x)>1 and sum(y)>2 and sum(z)>3},where:{n=1 and m=2}'
        from dual )
select regexp_substr(translate(str,'[]','{}'),
         '(sum:{)([^{}]+)', 1, 1, 'i', 2) as sum_expr
     , regexp_substr(translate(str,'[]','{}'),
         '(where:{)([^{}]+)', 1, 1, '', 2) as where_expr
from   demo;

SUM_EXPR                            WHERE_EXPR
----------------------------------- -----------
sum(x)>1 and sum(y)>2 and sum(z)>3  n=1 and m=2


How to use mysql to migrate database changes from local databases? I am working on a project with a few people and we have got the point where we all have different local versions of our website database and wish to get these changes to the server, after searching around I found something I liked but have a few questions on my approach to keeping our local databases in sync...

So, as we are all using phpMyAdmin, when we want to push changes we would export our local databases with phpMyAdmin and check our .sql files from phpMyAdmin into our git repo under a folder called schema... I already have some git web hooks setup, so I was basically thinking of running the command before inside of my deploy script as whenever we push changes to our dev branch or master on git hub a deploy script runs on the server...

Now, the idea is it would use the exported .sql file from the schema folder and import the new database schema, is this approach ok? What would happen if a column was removed or a length was changed in the structure of the database, would this cause errors? I followed this way because I did not want anything too complicated...

mysql -u <username> -p<PlainPassword> <databasename> < <filename.sql>

or is there a better way to do this? My tech stack is LAMP and I am using a local LAMP server to develop.

Database Migration tools

There are tools to help you manage the process of updating the structure of your database as your app evolves. ”Database migration" tools seems to be the emerging technical term for this, though imprecise/ambiguous.

Examples include Flyway (my favorite) and Liquibase. By the way, Flyway is Java-based but can be used by non-Java programmers via command-line utilities that wrap the Java classes.

The idea is that you must be disciplined in making no ad-hoc changes at all. Every change to the database structure must be written in a script to be formally managed by the migration tool.

You can run the tool wherever makes sense to you. If you have a single monolithic app, you could run the migration tool every time the app launches. Or you could call the tool from the command-line, or from a hook in your build tool or deployment tool.

The tool automatically detects which scripts have been applied already and which need to be applied. So you are accumulating scripts over time, never deleting them. Great for testing too, as you can recreate the database and take it to any point in the history of your project.

The scripts may be simply SQL code, as is the case with Flyway, plus optional Java classes you can write for complicated cases beyond the capability of SQL. Liquibase uses XML-based scripts.

These tools are best used from the start of a project but you can start with a 'baseline'. Personally, I suggest rebuilding your database from scratch using the tool to recreate the database entirely from your new organized scripts.

Consistent use of a database migration tool eliminates your questions. For example, ”What would happen if a column was removed or a length was changed in the structure of the database, would this cause errors?“, your migration scripts would alter the table to drop or modify the column. You know the column exists because you know the migration tool ran earlier scripts that already added the column.


EMP_DEPT database. Try to SELECT records with smallest salary, NULL column record is missing SQL Server 2017: I have the EMP_DEPT (SCOTT schema) database with a few additional records inserted. Two of them have ZERO base salary (PORTMAN, RENO) and one employee (MCQUEEN) has NULL in the salary column.
It looks like this:

Then I'm learning SQL on these records and I want to SELECT the employees with the highest and lowest salaries.
To do this I write 3 queries:
/*      Return employee record with max/min salary.    */
        SELECT * FROM EMP
        WHERE SAL = (SELECT MAX(SAL) FROM EMP);

        SELECT * FROM EMP
        WHERE SAL = (SELECT MIN(SAL) FROM EMP);

        SELECT * FROM EMP
        WHERE SAL = (SELECT MIN(ISNULL(SAL, 0)) FROM EMP);

        --ISNULL(source column, replacement_value) function transforms NULL value into
        --any other value specified in the second ISNULL attribute.

The results are as follows:

The first query results are not a surprise - I understand them.
The second query results are not a surprise - I understand them.
But in the third query I would have expected MCQUEEN returned as well, but he's not!
The question is why MCQUEEN is not returned in the third query results??
I've treated SAL column with ISNULL function to change this NULL value to ZERO.
So why isn't MCQUEEN among PORTMAN and RENO employees with zero salary??

So why isn't MCQUEEN among PORTMAN and RENO employees with zero
salary?

The issue is this predicate:
WHERE SAL = (SELECT MIN(ISNULL(SAL, 0)) FROM EMP);

At run time, the above comparison for the MCQUEEN row with NULL salary is:
WHERE NULL = 0;

The result of any comparison to NULL is UNKNOWN per the ISO SQL standard. The UNKNOWN result is neither TRUE nor FALSE so the row is eliminated from the result regardless of the value returned by the subquery.
Even though the right side subquery value will never be NULL due the the subquery ISNULL(SAL, 0) expression, the left side SAL value may be NULL so you need to similarly consider NULL values for SAL:
WHERE ISNULL(SAL, 0) = (SELECT MIN(ISNULL(SAL, 0)) FROM EMP);

Rows with NULL or zero SAL values will then be returned unless you have employees with negative salaries. Similarly, one could use COALESCE(SAL, 0), CASE WHEN SAL IS NULL THEN 0 ELSE SAL END), etc. to coerce a non-NULL for NULL values.
A better approach to include NULL may be a UNION ALL query:
SELECT * FROM EMP
WHERE SAL = (SELECT MIN(SAL) FROM EMP)
UNION ALL
SELECT * FROM EMP
WHERE SAL IS NULL;

The advantage of this technique is the queries can leverage an index on the SAL column (if one exists) to execute more efficiently.
I hope this helps you understand the technical reason why the row isn't returned. In the real world, the semantics of a NULL value need to be considered. For example, does the company really have employees with unknown salaries? If not, it's a flaw in the data model. If unknown salaries are allowed, why should a query for minimum salaries return unknown salaries since the actual employee salary could be higher?


SQL Replication using Two Availability Group Is it possible to setup SQL Replication with Two Availability Group Cluster? one is Publisher(or Distributor if not in different server) and the other AG is Subscriber?

Sure. The docs have plenty of detail on how, and specific details that might affect your implementation.
I'd recommend configuring your distributor as a third, dedicated AG (assuming 2017+).


File Share Witness with 4 Node Cluster I am building a cluster of 4 nodes along with a file share witness. Every month due to server updates the  server that holds the file share will need to be rebooted. My question is while the file share becomes unavailable during that time will the cluster go down?

No, the cluster will not go down until a node cannot form quorum.  Assuming all four nodes have a quorum vote, there are five possible votes.  Accordingly a node will have to get three votes to maintain or gain quorum.  When the file share witness goes offline, the node that currently owns quorum will maintain ownership because it can still get four votes.

See Understanding Quorum in a Failover Cluster for the long answer.


Postgres 9.6 export the database structure as pdf Is there any tool/software the can export all the tables structure (column names , its datatype ) in the database as a pdf/excel/word. additional diagram export will be added advantage.

You need a Entity Relation Diagram (ERD) generator. The most popular and advanced one that I know of that is FOSS is SchemaCrawler.

It does everything you want anyway. Though the diagrams are generated with Graphviz (so don't expect Visio quality).

You may want to go scavenger hunting through different graphviz viewers. This one using d3 seems to be pretty decent.


Seeking Help Optimizing Self-Joins This query takes ~5x longer than it should due to the WHERE IN() statement at the end. I am a junior DBA in over my head trying to figure out a way to optimize it. Is there a way to check IN() the resultset of the outer query? Can any of the self-joins be combined? Are there any query optimization resources you would recommend?

Apologies for the lack of minimalism, completeness, and verifiyibility - including the DDL seemed like overkill. Below are the query and the execution plan bottleneck.

SELECT SC.ROW_ID      C1_CASE_ID,
       SC2.ROW_ID     C2_NEW_CASE_ID,
       SC2.CASE_NUM   C3_NEW_CASE_NUM,
       MC.ROW_ID      C4_MSTR_CASE_ID,
       MC2.ROW_ID     C5_NEW_MSTR_CASE_ID,
       MC2.CASE_NUM   C6_NEW_MSTR_CASE_NUM
 FROM  siebel.ODS_S_CONTACT          MCON2,
       siebel.ODS_S_CONTACT          MCON,
       siebel.ODS_S_CASE             SC,
       siebel.ODS_S_CASE             MC,
       siebel.ODS_S_CASE_BNFTPLAN    CBP,
       siebel.ODS_S_CASE             MC2,
       siebel.ODS_S_CASE             SC2
WHERE (SC.STATUS_CD = 'Withdrawn')
  AND (CBP.STATUS_CD IN ('Active', 'Approved','Inactive')
  AND EXISTS (
               SELECT 1
                 FROM (SELECT cbp2.row_id cbp2_row_id,
                              cbp2.case_id cbp2_case_id,
                              DENSE_RANK() OVER (PARTITION BY cbp2.case_id ORDER BY CASE WHEN cbp2.status_cd = 'Active' THEN 1
                                                                                WHEN cbp2.status_cd = 'Approved' THEN 2
                                                                                WHEN cbp2.status_cd = 'Inactive' THEN 3
                                                                                ELSE 4
                                                                                END,cbp2.created DESC
                                                ) cbp2_order
                         FROM siebel.ods_s_case_bnftplan cbp2
                      --where cbp2.case_id = CBP.CASE_ID
                      ) sq
                WHERE cbp2_order = 1
                  AND CBP.CASE_ID = cbp2_case_id
                  AND CBP.ROW_ID = cbp2_row_id
             )
      )
WHERE (SC.MSTR_CASE_ID=MC.ROW_ID)
  AND (MC.APPLICANT_ID=MCON.ROW_ID)
  AND (MCON.SOC_SECURITY_NUM=MCON2.SOC_SECURITY_NUM AND MCON.ROW_ID <> MCON2.ROW_ID)
  AND (MCON2.ROW_ID=MC2.APPLICANT_ID)
  AND (MC2.ROW_ID=SC2.MSTR_CASE_ID AND SC2.TYPE_CD = SC.TYPE_CD AND SC2.STATUS_CD = 'Active')
  AND (SC.ROW_ID=CBP.CASE_ID)
  AND SC2.CREATED IN (
                        -- default: 46 rows  ~30seconds
                        SELECT MAX(SCSUB.CREATED)
                          FROM siebel.ODS_S_CASE          SC2SUB
                         INNER JOIN siebel.ODS_S_CASE    MC2SUB        ON MC2SUB.ROW_ID = SC2SUB.MSTR_CASE_ID
                         INNER JOIN siebel.ODS_S_CONTACT MCON2SUB      ON MC2SUB.APPLICANT_ID = MCON2SUB.ROW_ID
                         INNER JOIN siebel.ODS_S_CONTACT MCONSUB       ON MCON2SUB.SOC_SECURITY_NUM = MCONSUB.SOC_SECURITY_NUM
                         INNER JOIN siebel.ODS_S_CASE    MCSUB         ON MCONSUB.ROW_ID = MCSUB.APPLICANT_ID
                         INNER JOIN siebel.ODS_S_CASE    SCSUB         ON MCSUB.ROW_ID = SCSUB.MSTR_CASE_ID
                         INNER JOIN siebel.ODS_S_CASE_BNFTPLAN CBPSUB  ON CBPSUB.CASE_ID = SCSUB.ROW_ID
                         WHERE SC2SUB.ROW_ID = SC2.ROW_ID
                           AND  CBPSUB.STATUS_CD IN ('Active', 'Approved')
                           AND SC2SUB.TYPE_CD = SCSUB.TYPE_CD
                           AND SCSUB.STATUS_CD = 'Active'
                      )

Execution Plan here.

It looks like you have very few indexes in there.  If this isn't a third-party app then here are some indexes I'd test out.  I'm making some assumptions, such as Row_ID is a unique column.

CREATE UNIQUE CLUSTERED INDEX CIX_OdsSCase_Sc2Sub_RowId ON ODS_S_Case.Sc2Sub
(
    RowId
)

CREATE INDEX IX_OdsSContact_Mcon2Sub_SocSecurityNum ON ODS_S_Contact.MCON2SUB
(
    Soc_Security_Num
)
INCLUDE
(
    Row_Id
)

CREATE INDEX IX_OdsSCaseBnftplan_Cbpsub_CaseId ON ODS_S_CASE_BNFTPLAN
(
    CASE_ID
)
INCLUDE
(
    STATUS_CD
)

CREATE INDEX IX_OdsSContact_Mconsub_SocSecurityNum ON ODS_S_CONTACT.MCONSUB
(
    Soc_Security_Num
)
INCLUDE
(
    Row_Id
)

CREATE INDEX IX_OdsSCase_Scsub_StatusCd_MstrCaseId ON ODS_S_CASE.SCSUB
(
    Status_Cd
    , Mstr_Case_Id
)
INCLUDE
(
    Row_Id
    , Created
    , Type_Cd
)

Also, I can't see this without saying something.  You appear to have a social security number unencrypted, used as a foreign key, assuming it's unique, etc..  This is a risk on many levels.  If what I'm seeing is correct and you're not convinced it's a bad idea  you should read https://www.computerworld.com/article/2552992/not-so-unique.html and https://helifromfinland.blog/2014/04/18/is-social-security-number-a-good-primary-key/

I've spent too much time on this for now.  Try out these indexes in your test environment and see how much of a difference it makes.  If you're able to make these types of changes and didn't know you should then start looking for blog posts introducing people to indexing.


How to avoid implicit type casts in PostgreSQL? I just discovered that I can insert values of any type into a PostgreSQL (9.6) column of type text:

drop table if exists d cascade;
create table d ( a text );
insert into d values ( 42 );
insert into d values ( true );
select a, pg_typeof( a ) from d;

  a   | pg_typeof
------+-----------
 42   | text
 true | text
(2 rows)

Is this an intentional feature? Have I done something wrong? Is there a setting to avoid this? Doesn't this violate
the assumption that RDBMSs should be typesafe?

I know that text acts like a catch-all in PostgreSQL which is often convenient because you can then write
string representations of arbitrary types. But surely sometimes you want to make sure that only strings get
inserted into a given column, to the exclusion of implicitly cast values.

Is there anything I can do to avoid 'casual' type casts?

Is this an intentional feature?

Yes, implicit type conversion is an intentional feature. There are pros and cons either way but this is how postgres is designed to work:

  In many cases a user does not need to understand the details of the type conversion mechanism. However, implicit conversions done by PostgreSQL can affect the results of a query. When necessary, these results can be tailored by using explicit type conversion

  Doesn't this violate the assumption that RDBMSs should be typesafe?

No, everything is still typesafe and "SQL is a strongly typed language". Implicit conversions don't change that.

  Is there anything I can do to avoid 'casual' type casts?

Short of messing around with the system catalog, which is usually a very bad idea (and impossible in some cases), there isn't a lot you can do to avoid implicit casting entirely. Even if you include explicit casts everywhere implicit casts might still occur by mistake:

create table t(foo text);
insert into t(foo) values(11::integer);

select * from t;

| foo |
| :-- |
| 11  |

dbfiddle here


Newbie question on database efficiency I am currently building an online game where data is stored in MySQL.
I have three tables:

Characters
Items
Inventory

They are built in this manner:

Characters

| ID(key)   | Name  | Other...  |
| 1         | Adam  | content   |
| 2         | Ben   | content   |
| 3         | Con   | content   |
| 4         | Dun   | content   |
| ...       | Eab   | content   |

Items

| ID(key)   | Name      | Other...  |
| 1         | Potion    | content   |
| 2         | Sword     | content   |
| 3         | Shield    | content   |
| 4         | Card      | content   |
| ...       | Food      | content   |

Inventory

| ID(key)   | CharacterID   | ItemID    |
| 1         | 1             | 2         |
| 2         | 4             | 2         |
| 3         | 4             | 1         |
| 4         | 2             | 3         |
| ...       | 4             | 3         |

What I am looking for is if this is efficient and an OK method to use for storing this type of data? Instead of having to store the name of the item over and over I just reference it's ID from the items table.

The PHP code creates a JOIN to find the character ID and the item ID then searches for it on the inventory table. Then I can pull in any data I want.

If this is not efficient or if I have created a problem, please point me in the right direction so i can resolve this before it becomes a large issue at launch.

Knowing everything you have specified, I suggest you to create this model:

/********Characters********
this is the table where all the players exist
*/
CREATE TABLE characters (
    id      <id_type> NOT NULL,
    name    <column_type> ,
    other   <column_type>
);
ALTER TABLE characters ADD CONSTRAINT c_pk PRIMARY KEY ( id );

/********Item Type********
this is the type of item; i.e. poison, sword,...
*/
CREATE TABLE item_type (
    id      <id_type> NOT NULL,
    name    <column_type>,
    other   <column_type>
);
ALTER TABLE item_type ADD CONSTRAINT t_pk PRIMARY KEY ( id );

/********Items********
this is every single and unique existence of any item in the game...
*/
CREATE TABLE items (
    id       <id_type> NOT NULL,
    typeid   <id_type> NOT NULL,
    other    <column_type>
);
ALTER TABLE items ADD CONSTRAINT i_pk PRIMARY KEY ( id );
ALTER TABLE items ADD CONSTRAINT i_t_fk FOREIGN KEY ( typeid ) REFERENCES item_type ( id );

/********Inventory********
this relates all players with all of their items
*/
CREATE TABLE inventory (
    characterid   <id_type> NOT NULL,
    itemid        <id_type> NOT NULL
);
ALTER TABLE inventory ADD CONSTRAINT in_pk PRIMARY KEY ( characterid, itemid );
ALTER TABLE inventory ADD CONSTRAINT in_c_fk FOREIGN KEY ( characterid ) REFERENCES characters ( id );
ALTER TABLE inventory ADD CONSTRAINT in_i_fk FOREIGN KEY ( itemid ) REFERENCES items ( id );

I hope this solution helps you to better approach what you are looking for your game.


Updating unindexed values with postgresql question The inexact wording of an interview question has been as follows.

  A PostgreSQL database contains a table called “apps”, having
  millions of rows, numerous indexes, and is heavily used. There are at
  least two columns in this table:

"id": an integer serving as the primary key
"type": a string containing unindexed values such as "SlackApp", "GithubApp", etc.

  You need to update this table so all occurrences of a particular
  string in the “type” column are replaced with another string. This
  process should take place in a migration that will be executed
  from one of the fleet hosts while the other hosts in the fleet are
  still running, therefore this change should happen without requiring
  downtime.

  How would you go about solving this problem? What is the estimated
  time spent in the migration process? What are the tradeoffs of your
  solution compared to other solutions?

My answer to the above question would be to issue a transactional SQL statement to keep atomicity of operations and that would be linearly dependent on number of rows. Is there much more detail that I could add to the answer?

PostgreSQL doesn't table-lock on update, it row-locks.

  You need to update this table so all occurrences of a particular string in the “type” column are replaced with another string.

That's simple,

UPDATE apps
SET type = regexp_replace(type, 'SearchRegex', 'Replacement', 'g')
WHERE type ~ 'SearchRegex';


Cost of Increasing max pool size in SQL Server We have a public front website that run fine most of the time. But we sometimes experience peaks of connections (like after a mailing campaign) and we were getting error like :

Timeout expired. The timeout period elapsed prior to obtaining a
connection from the pool. This may have occurred because all pooled
connections were in use and max pool size was reached

We are pretty sure that all our connections are closed properly (no connection leak).
So we decided to increase the number of connection allowed in the pool (which is 100 by default).
We increased it to 1000 (max pool size=1000; in our connection string), and now our site can handle most peaks of connections. (I precise the site run on a dedicated server)
My question is : What may be the negative effects of the increase of the max connections pool ?
EDIT : Here's whats I have when I use sp_who2 during those peaks :
If max pool is set to 100, I have more than 100 lines of those 'awaiting command' lines :

SPID
Status
Login
HostName
BlkBy
DBName
Command
CPUTime
DiskIO
LastBatch
ProgramName
SPID
REQUESTID

number
sleeping
db_user
IIS pool name of my website
.
MyDatabaseName
AWAITING COMMAND
0
0
05/10 14:48:59
.Net SqlClient Data Provider
137
0

And I can see that all 'LastBatch' were executed just a few second before.

What may be the negative effects of the increase of the max
connections pool?

If connections are indeed properly closed and disposed when not actively used (i.e. immediately returned to the pool) and you have capacity headroom on both the app server and database server, I would not expect negative effects with a larger pool size. However, increasing the max pool size to avoid errors at peak load might not be the best approach depending on the root cause. Consider the implications on both application and database server resources.
Are the queries mostly in runable status on the database server? This may indicate query/index tuning is needed or the database server could use more cores to handle peak demand within SLAs.
Are the queries on the server side mostly suspended due to async network waits? This may be a symptom the web server is slow consuming query results under load, indicating the app server might need more cores, instances, or network bandwidth. You mentioned "a dedicated server" so perhaps additional app servers are in order. Note that 100 active queries also requires 100 threads on both app server and database server.
Are the queries on the server side mostly suspended due to other resource waits, such as IO, locks, or latches? This can be mitigated with query/index tuning or db server configuration (e.g. more tempdb files in the case of tempdb page latch contention).


Is raid 5 suitable for a mysql installation? Is raid 5 suitable for a mysql installation?

Let me explain my application further. My application is a socket programming which will connect with gps device to receive gps string and there after do a further processing. The socket programming will be in another server and db in another server. So during the further processing is where it will query from the db. So here I guess there will lots of i/o rite. Minimally during the further processing there will be minimum of 5 select and the insert will be minimum 1 but at times it can be even minimum 4 or more and also a number of updates. Hope I am clearer now.

In a read-heavy, low-write environment on a RAID5, I would just leave that to one's budget, tolerance, and blood pressure.
In a write-heavy, low-read or write-heavy, read-heavy environment, RAID5 is simply out of the question. This is especially true for InnoDB. Think of an InnoDB's table interaction.
InnoDB
If you do not use innodb_file_per_table, OMG all the activity would be centered around just one file, ibdata1. What is contained in ibdata1?

Table Data Pages
Table Index Pages
Table Metadata for Managing TableSpace IDs
MVCC Data (for ACID Compliance and Transaction Isolation)

Even reads in InnoDB tend to shroud rows with MVCC protection to allow repeatable reads and permit transactions to hit the same rows being read. Thus, reads as well as writes produce disk I/O in ibdata1.
Using innodb_file_per_table may relieve some of the disk I/O by separating Table Data and Index pages from ibdata1 into .ibd files. Yet, I would expect a somwehat noticeable  performance improvement only for a limited time in a RAID5 environment. The table interaction is still somewhat the same. Every access to a .ibd file is always preceded by reference checks against ibdata1.
While the separation can bring significant performance changes, RAID5 would be what they call in the chemistry world, a limiting reagent. Any benefits expected from InnoDB layout changes would be neutralized by outside factors, such as RAID5. The presence of extra tablespace files due to innodb_file_per_table buys you nothing over time but just the presence of extra tablespace files.
MyISAM
When it comes to MyISAM, RAID5 is OK in a read-heavy, low-write environment provided you map all temp tables (using tmpdir) to another disk, separate from the RAID5.
Please remember that table data pages live in .MYD files and its corresponding index pages live in .MYI files. A write-heavy environment (INSERTs, UPDATEs, DELETEs) will obligate RAID5 to slow things down. Given MyISAM's locking behavior (full table lock with each INSERT, UPDATE, and DELETE) in a write-heavy environment, a steady stream of DML will keep RAID5 rather busy and have DB users enter a brief-but-annoying time warp waiting for DML to complete.
Conclusion
Under the hood, RAID5 has the following characteristics for writing with parity

Read the old data block
Read the old parity block
Compare the old data block with the write request. For each bit that has flipped (changed from 0 to 1, or from 1 to 0) in the data block, flip the corresponding bit in the parity block
Write the new data block
Write the new parity block

Should any of these steps see the slightest intermittency, the RAID5 set enters a brief-but-annoying time warp. Multiply that by a huge number of writes and you will feel it in the database performance. Each of these steps could be a point of failure. Why?
According to Wikipedia

In the event of a system failure while there are active writes, the
parity of a stripe may become inconsistent with the data. If this is
not detected and repaired before a disk or block fails, data loss may
ensue as incorrect parity will be used to reconstruct the missing
block in that stripe. This potential vulnerability is sometimes known
as the write hole. Battery-backed cache and similar techniques are
commonly used to reduce the window of opportunity for this to occur.

Recommendation
RAID10 not only provides stability but allows some leeway in disk maintenance without taking mysql down in most cases. When data is mirrored, you know where the data is going and you know from where the data is being read.
UPDATE 2012-02-14 17:55 EDT
After reading your question update, I would say go with RAID10. Unless you do not mind long periods of downtime, you cannot afford to do RAID5 disk maintenance in lieu disk syncing. In fact, the smaller the disks you stripe in RAID10, the faster the sync time would be after a RAID 10 disk maintenance.
Other things to consider

Tune your queries
Remove redundant indexes
Cache as much of the data as you can
Use covering indexes wisely

Questions of this nature can be posted in StackOverflow. You may post such questions in the DBA StackExchange as well.


MongoDB replication throttled due to Mongo using all system memory We have a small three member replicaset running MongoDB 3.4:

Primary. Physical local server, Windows Server 2012, 64 GB RAM, 6 cores. Hosted in Scandinavia.
Secondary. Amazon EC2, Windows Server 2016, r4.2xlarge, 61 GB RAM, 8 vCPUs. Hosted in Germany.
Arbiter. Tiny cloud based Linux instance.

We have noticed that the replication speed from the primary to the secondary is throttled (capped) at just over 20 MBit/s, i.e. it doesn't go above that speed. If there is more data to replicate, it will queue up and we will have replication lag.

This is not a pure bandwidth issue; for example while throttling is taking place, we can transfer data between the two servers over FTP at far more than 20 MBit/s. Also, sometimes, notably after MongoDB restarts, the throttling stops for a couple of days, then comes back. There is nothing in the logs that describes why.

After a lot of experimentation and debugging, we have found the issue for the throttling: Over time MongoDB will consume all available memory on the primary member. Once this state is reached (typically a few days after a restart), throttling sets in. About half of the memory is the MongoDB process itself (WiredTiger cache), and the remainder is consumed by memory mapped collections. This seems to be by design, according to the MongoDB website: "Via the filesystem cache, MongoDB automatically uses all free memory that is not used by the WiredTiger cache or by other processes."

Now on to the questions:

Can anyone think of the reason why low available memory leads to replication throttling?
Is there a way in MongoDB to limit the amount of memory used for memory mapping? (We haven't found any.)
Is there a recommended way in Windows to do the same? (Feels like the wrong end to solve the problem.)

For now, we have an extremely hacky workaround: We have a small .NET script that continuously monitors the available memory, and whenever it's low, it allocates 10 GB memory (which will be taken from MongoDB), then immediately releases it. The result is that the server has 10 GB available memory, which takes around 1 day for MongoDB to fill up, during which there is no throttling. Yes, this script actually fixes the problem :)

I have never seen throttling of replication speed, but I'm working with Linux mongodb machines. I guess that at windows world it can happen, windows OS is throttling when program eats all memory. I remember reading that there are "problems" at windows OS when mongod uses all free memory.

To control how much memory mongod can uses, in Linux, you can use cgroups, so if you use "containers" (docker) in windows, you could control how much resources mongod gets.


Oracle: Quick way to list all database links The title says it all, is there a way to quickly list all the current database links in oracle?

Something along the same lines as this, which lists the current user's tables:

select * from user_tables;

Thanks,
GC.

These views are helpful for working with DB links in Oracle:

DBA_DB_LINKS  - All DB links defined in the database
ALL_DB_LINKS  - All DB links the current user has access to
USER_DB_LINKS - All DB links owned by current user

See more in Oracle documentation


Added a unique index, but cannot find it in TABLE_CONSTRAINTS I'm running the following command in my DB:

CREATE UNIQUE INDEX [UK_NAME1] ON [TABLE1] ([COLUMN1] ASC);

Now, I'm running the following query:

SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS

And yet I can't find my unique index listed in there.

Am I using the wrong query? What other issue could there be?

Use this syntax, which also allows you to name the constraint:

ALTER TABLE Persons ADD CONSTRAINT UQ_PersonID UNIQUE (P_Id,LastName)


How to import SQL file which has no N prefix I have a large .sql file in Persian Language. The file is exported from sqlite db browser and has no N prefix for unicode fields. How can I reuse it in Sql Server?

This is sample of insert query in original file:

insert into `words` (id,word) values ('1','2','فارسی');

But I need this

insert into `words` (id,word) values ('1','2',N'فارسی');

I can not use find/replace to replace ,' with ,N' because there are  some ,' which represent number fields and has no need to N prefix.

I think I will need some conversion between multiple software (perhaps Microsoft access?) but I have no idea where to start?

Just adding an answer based on the below layout.

CREATE TABLE dbo.words(id NVARCHAR(1000),id2 NVARCHAR(1000),word NVARCHAR(max))

INSERT into dbo.words (id,word) values ('1','2','فارسی');
INSERT into dbo.words (id,word) values ('1','2','فارسی');
INSERT into dbo.words (id,word) values ('1','2','فارسی');
INSERT into dbo.words (id,word) values ('1','2','فارسی');
INSERT into dbo.words (id,word) values ('1','2','فارسی');
INSERT into dbo.words (id,word) values ('1','2','فارسی');
INSERT into dbo.words (id,word) values ('1','2','فارسی');
INSERT into dbo.words (id,word) values ('1','2','فارسی');
INSERT into dbo.words (id,word) values ('1','2','فارسی');
INSERT into dbo.words (id,word) values ('1','2','فارسی');
INSERT into dbo.words (id,word) values ('1','2','فارسی');
INSERT into dbo.words (id,word) values ('1','2','فارسی');

**Do replace all for ,' --> ,N'**

will become

CREATE TABLE dbo.words(id NVARCHAR(1000),id2 NVARCHAR(1000),word NVARCHAR(max))

INSERT into dbo.words (id,word) values ('1',N'2',N'فارسی');
INSERT into dbo.words (id,word) values ('1',N'2',N'فارسی');
INSERT into dbo.words (id,word) values ('1',N'2',N'فارسی');
INSERT into dbo.words (id,word) values ('1',N'2',N'فارسی');
INSERT into dbo.words (id,word) values ('1',N'2',N'فارسی');
INSERT into dbo.words (id,word) values ('1',N'2',N'فارسی');
INSERT into dbo.words (id,word) values ('1',N'2',N'فارسی');
INSERT into dbo.words (id,word) values ('1',N'2',N'فارسی');
INSERT into dbo.words (id,word) values ('1',N'2',N'فارسی');
INSERT into dbo.words (id,word) values ('1',N'2',N'فارسی');
INSERT into dbo.words (id,word) values ('1',N'2',N'فارسی');
INSERT into dbo.words (id,word) values ('1',N'2',N'فارسی');

The only downside is you have to change all column definition to NVARCHAR(). But worth a try if there are not much tables. Also try to separate the tables and insert code to a seperate file to work out.

NB:Also regarding the alignment. If the insert layout is aligned we can press
Alt+shift+down arrow will enable a vertical select which is quite useful.


Error: 'PRIMARY' filegroup is full We have increased disk space on the SQL Server, and there is definitely enough space on the server, yet below error is raised:

  com.softmar.fileserver.WebFileException: com.microsoft.sqlserver.jdbc.SQLServerException: Could not allocate space for object 'dbo.WebFileImage'.'pkWebFileImage' in database 'sslFileServer' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.

The properties of the database shows that there is only 1.1 MB available, yet other databases on the same SQL server shows much more space available on the properties.  So why is only this database not accommodating the disk size increase?  Looking at the file settings it is set to unrestricted growth.

This is rather critical so will appreciate your swift response.

add a file to your database and set it's filegroup to primary.


Diagnosing slow Always On commits Two node Always On availability group, one synchronous replica.

My synchronous replica falls out of sync very regularly. I see a pattern in which, when a log backup occurs on the secondary replica a short period of lag will occur during which the redo_queue_size fills up quickly, like so:

Looking at the guidance in the following link, it seems that my problem is mostly due to contention the redo thread experiences when trying to harden the transactions:

https://technet.microsoft.com/en-us/library/dn135335(v=sql.110).aspx

The replica falls further out of sync when a transaction log backup runs and this problem is exacerbated by reports running on the secondary replica also.

All the while, my transaction log backups are huge - on average 1.2GB but can be larger.

As far as I am aware, my log backups will be large because I have TDE enabled on the database, but I really didn't expect them to be this large. I suspect this is what is contributing the most to the slow commits on the secondary replica.

Are there any performance counters recommended to diagnose slow commits on a synchronous replica? What else can I do to validate my theory?

My problem seems to be the same as the one described here:
https://www.sqlservercentral.com/Forums/1871286/AlwaysOn-Missing-Redo-Thread

Can I just enable this trace flag on the secondary replica or does it need applying to both nodes?

EDIT: I checked the redo queue at 6am and found a huge number, with a recovery time of 15-20 minutes and increasing slightly all the time. I then applied the traceflag with DBCC TRACEON (3459, -1) and found after a few minutes, the number of redo commands dropped extremely quickly. So far the problem seems to have been mitigated by this traceflag but presumably this will put all transactions hardened to the secondary replica's log in single-threaded mode, like SQL Server 2014 and therefore, there is still the potential for the secondary replica to fall behind as a result of the non-parallel threading, when the primary is under heavy write load.

The problems I was experiencing:

Synchronous secondary replica falling behind semi-permanently
Huge log backups

These were solved by enabling trace flag 3459. In my case it was extremely easy to see that the flag immediately fixed the wait type of parallel_redo_flow_control
dirty_page_table_lock
parallel_drain_redo_worker and showed a rapid decrease in size of the redo queue.

I wonder why, in the bug report, Microsoft calls this "assertion": https://support.microsoft.com/en-us/help/3200975/fix-assertion-occurs-when-you-use-parallel-redo-in-a-secondary-replica

Credit to Jason AKA CirqueDeSQLeil from SQLServerCentral.com
https://www.sqlservercentral.com/Forums/1871286/AlwaysOn-Missing-Redo-Thread


"parameter name used more than once" error when declaring function - why this is happening? create function test(a integer, b integer)
returns table (a integer, b integer) as $$
  begin
    return query select 1, 2;
  end;
$$ language plpgsql;

throws exception:

ERROR:  parameter name "a" used more than once
CONTEXT:  compilation of PL/pgSQL function "test" near line 1

Why this is happening? I thought that function argument name has nothing to do with returned table column name. Is there a workaround to have function argument named "a" and also have column named "a" in a table returned from function?

I know that I can return setof record but then I have to explicitly "define" columns returned from function in select query:

create function test(a integer, b integer)
returns setof record as $$
  begin
    return query select 1, 2;
  end;
$$ language plpgsql;

select * from test(1, 2) as (a integer, b integer)

You've defined an input variable and a return variable with the same name, so it can't be disambiguated by PL/PGSQL, as the error message is telling you.

You can change it to returns table (c integer, d integer) as well, or you can create a type for this, like CREATE TYPE mytype (a integer, b integer) and then use it in your function as returns setof mytype, which should give you the behavior you're looking for, without colliding variables.


Is it possible to limit concurrent aggregations in Mongo? When clients makes certain requests, our service sends aggregation queries to Mongo. Some of these aggregations take longer than others.

Usually this is fine, but occasionally multiple heavy aggregations will be requested at the same time. In the worst case, this causes Mongo to become unresponsive, and other requests (such as inserting new documents) fail with a timeout.

Is there any way I can limit the number of aggregations that Mongo will perform in parallel?

Ideally if 8 aggregations are running and a new aggregation request is made, Mongo would queue the new aggregation until one of the others has completed.

Answer is simple, NO. Not in mongodb. But you can always do it in your own application. Just create "lock" collection where applications mark they will to do aggregation. Ticket system for queuing.


Differential backups are 2/3 the size of the full backup I was investigating why my differential backups were taking almost as long as my full backups and I noticed that the size of the first differential backup after the daily full backup was 2/3 the size of the full backup.  However the subsequent differential backups for the remainder of the day only grew in size by about 1%.

Nothing has significantly changed in the usage pattern on the database and historically the size of the first differential backup after the daily full backup was about .5% of the size of the full backup.  I've looked at the day over day size of the full backups and they only grow by about 2%.

The daily backup schedule is defined as one full backup at 1AM followed by 5 differential backups between the hours of 7AM and 11:59PM.  Typically the full backup takes about an hour and a half to complete and that includes the verify step.  The differentials were taking about 20 mins to complete including the verify step, however now they are taking about an hour and ten mins to complete.

I've confirmed the database usage pattern hasn't changed, there are no new jobs that are writing/changing a significant amount of data between the window of the full backup and the first differential backup and I don't seen any file subsystem errors.

With all of what I've confirmed above my question is what would cause the first differential backup of the day to have become so large when the subsequent differentials on the same day only grow by 1%?

Server Details:

SQL Server 2008r2 Enterprise
LiteSpeed for SQL Server
Database is in Simple Recovery mode
Backup target is a file share on the network

After digging into the backup logs between LiteSpeed and SQL Server I noticed that the first LSN number between the daily full backup and the full backup LSN number in differential backups didn't match up.

After doing some reading on how the LSN values are used in the backups of the database I did some digging on what would cause the full backup's LSN number in the differentials to be different then what the first LSN value was in the full backup on the same day.  This post on the Backup Chain in SQL Server pointed me to look at the configuration of the backup jobs and I found that the COPY_ONLY option was enabled for the full backups. (Still trying to determine how it was turned on to begin with and when)

What I learned from the post is that the COPY_ONLY option won't reset the LSN value for the subsequent differential backups which is a big problem if you don't retain the last full backup that was done without the COPY_ONLY option.

From the blog post:

  So, stated differently, IF a FULL backup is executed in your environment WITHOUT the COPY_ONLY clause, it can/will jeopardize your backup chain as it will reset the differential base LSN of all subsequent DIFFERENTIAL backups. Meaning, in turn, that if you don’t have (or retain) access to that FULL backup made without the COPY_ONLY option, you won’t be able to recover your databases using subsequent DIFFERENTIAL backups.

I've since turned the COPY_ONLY option off in the backups and the size of my differential backups have dropped back down to what they historically have been.  I've also verified that the Full Back LSN number in the logs for the differential backups is matching what the first LSN number value is in the logs for the daily backups.


Restrict access to my PostgresSQL/ Post GIS database used through QGIS We have a small network of users accessing our QGIS data stored on a PostGIS database (in 3 schemas) which sits on my local drive in my computer. This drive is then shared with the others. Due to the other users being beginners in the GIS world I created a number of layer files (.qlr) for them which point back to the data in my PostGIS database. They can only see these layer files.

But I've just realised when they use these layer files they can still edit, add and remove the data.

How do I restrict this?

Is there a simple way in QGIS or even Windows? (setting permissions on the sharing folders does not seem to work)

The database was created through something called 'pgAdmin 4' and I've tried adding another user/role with different privileges but I can't seem to get that to work its seems to deny access completely or its all still wide open.

The privileges I can alter seem to be too high up the hierarchy all I need to restrict is the ability to write and delete to the schema's and tables, leaving myself with editing rights.

I have no idea how to write in SQL, I'm a geographer not IT. There is no Database Administrator here only me and I made this database just by following the steps on the pgAdmin help pages and some luck. So Im seeking idiot proof help.

That is best done through the PostgreSQL permission system.

Create database users for the people to access the database. Then grant them SELECT on the tables that contain the layer data and USAGE on the schemas containing these tables. For example:

GRANT USAGE ON SCHEMA myschema TO auser;
GRANT SELECT ON myschema.mytable TO auser;


Is there a Postgres admin GUI that can execute a master file containing multiple sql files I'm developing a really long script. I'd like to break the script into smaller, more manageable scripts and include each sql file in a master file, then just execute the master file.

example: master.sql

contents (I don't know the syntax to use):

file1.sql
file2.sql
file3.sql

I've found several tutorials about using psql -f in the command-line and \i to include these files, but the preference here is to avoid the terminal and use a GUI to manage my database and execute the master.sql script.

Is this possible in any postgres admin GUI? Presumably it's not in pgAdmin.

A psql script

A psql script can index multiple files, let's assume 01_mydb.psql, is in the current working directory, and you have a directory 01 that has the files 01_schema.sql and 02_types.sql.

01_mydb.psql may look like this.

\i 01/01_schema.sql
\i 01/02_types.sql

Or however you want to structure it. If things get more complex and need more order, add more subdirectories

\i 01/10_tables/01_foo.sql
\i 01/10_tables/02_bar.sql

Then you would just either..

Add an index file in 01, something like ./01/10_tables.psql
Or, just add them all to 01_mydb.sql

I'll show the second method using find,

Using find to generate a master-load script.

Let's go ahead and create that structure.

01/
├── 01_schema.psql
├── 02_types.psql
└── 10_tables
    ├── 01_foo.psql
    └── 02_bar.psql

Here is the commands we use to create it.,

mkdir 01
touch 01/01_schema.sql
touch 01/02_types.sql
mkdir 01/10_tables
touch 01/10_tables/01_foo.sql
touch 01/10_tables/02_bar.sql

Now you can use find to generate a load script

find ./01/ -type f -printf '\\i %p\n' | sort | tee master.psql
\i ./01/01_schema.sql
\i ./01/02_types.sql
\i ./01/10_tables/01_foo.sql
\i ./01/10_tables/02_bar.sql

Now just run master.psql;

psql -d database -f master.psql


How can I determine size of database prior to importing from SQL dump? I had to manually duplicate a server for development, and the only way I could duplicate the database was via SQL dump.  I was importing into MySQL 14.14 on a virtual machine running Ubuntu 14.  (Yeah, really old stuff, but the client isn't ready to allow an upgrade.)
The SQL dump came to about 6GB of non-compressed, plain ANSI text, so assuming that the import queries bloated the data with all the query instructions, character-escape sequences, and textual representations of binary data, the database ought to be smaller, right?  I figured 6GB for the temporary SQL dump, plus 6GB for the database plus another 8GB for the rest of the system (20GB total) ought to be more than enough.  It wasn't.
I rebuilt my virtual machine with a 30GB-disk thinking surely that would be enough, but it still wasn't.  So I rebuilt my virtual machine with 50GB, and ended up with 16GB leftover when all was said and done.  It turns out the database blew up to 20.5GB!
Somewhere along the way I figured out how to check the size of an existing database in MySQL, but what I would like to know, when the original database is not available to query, is there an application or MySQL command that can passively process a SQL dump without building the database, for the purpose of estimating the size of the resulting database?

I don't know of any tool that can do what you ask.
The space required can vary due to many factors:

Storage engines
Number of indexes per table
Data types
Character sets
Table options (for example row format and compression)
Numerous MySQL Server configuration options
Table encryption

If you are trying to optimize the storage, try launching a cloud instance with a modest instance type but over-provisioned with plenty of storage space. Then restore your database dump file, and measure the actual size of the restored database. Use that information to provision another cloud instance with exactly the storage you think is appropriate. Then drop the first one, since you only used it to get the size estimate.
Another consideration: Do you really want to provision only enough space for the current database? You don't think it will keep growing (most databases keep growing, in my experience). Does your storage volume have enough space for binary logs? For temporary tables needed during ALTER TABLE? For temporary tables needed during queries?
It's usually a good idea to make sure you have at least double the storage space of your current database size.


Add new admin user to MySQL I have inherited a MySQL instance currently being used by an enterprise software suite. I have no idea what the credentials are for any account, but have full admin rights to the server that the instance is installed on.

I am not very knowledgeable about MySQL. Is there any way I can either add a new admin user or find out the details of an existing user of some kind?

I have looked in what seems to be the my.conf file I think is being used, but there are only various directories and server ports being set there.

You should look into resetting the root password.
Normally the root user shouldn't be used for any enterprise package, however "shouldn't" isn't much of a guarantee.

Your first action SHOULD be to ask around for the root password or look in whatever password storage your company might be using.

If all else fails and you have to reset the passwored, you can find out how here.
Alternatively you could use --skip-grant-tables, and create a new admin user, however this is very insecure, and would be best done during a maintenance window with restriction of the network access.


how to load a file into a mysql table for a particular column? I need to load a file into mysql table for a particular column I have a table cc with 2 columns name and points, I have to update only the points column, I have a txt called point.txt which has only the points,now I need to load the point.txt into the table cc for the particular column(pionts) this command is for a whole table.

load data local infile '/path/point.txt' into table tablename;`

I need to load the data for a particulate column

Manual states:
You can also discard an input value by assigning it to a user variable and not assigning the variable to a table column:

LOAD DATA INFILE 'file.txt'
  INTO TABLE t1
  (column1, @dummy, column2, @dummy, column3);

So in your case, it may look something like:

LOAD DATA INFILE '/path/point.txt' INTO TABLE tablename (@dummy, points);


Distribute values equally in count and sum to a set of users I have a table with 100 float values (say AMOUNT) that need to be equally divided among 5 users (say UID 1 to 5). The distribution must be such that each user gets approximately the same total count and value of AMOUNT.

Say, I have a sample table with 20 Amounts:

164548.65, 148410.72, 131395.33, 130219.97, 128593.28,
124539.92, 103958.45, 103671.87, 100210.36,  99645.42,
 98848.25,  97764.84,  97577.03,  90067.98,  87838.22,
 86730.85,  85508.00,  83481.78,  82886.95,  78588.79

This needs to be divided among 5 codes (numbered 1-5) in such a manner that each code gets 4 Amounts with a net worth of approx 424,000.

I am using SQL Server.

The Amounts are Linked to Invoice numbers. So the table has Invoice_Number,Amount. I need to add a column where the USER_ID (1-4) can be plugged in. So if there are 20 Invoices and 4 users, each user gets assigned with 5 Invoices and the sum of Invoice Amounts for each user must approximately be the same.

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

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

So something like :

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

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

Not exactly what you expected but not so far.

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

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

So :

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

With sum 427702.27, 409754.4, 416126.01, 429272.87, 441631.11

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


If value is null then take the next higher value I have a function which requires 3 values and then uses these values to return a value from a table.
FUNCTION: odwh_data.annual_rental(connection_type,data_rate,date)

CREATE OR REPLACE FUNCTION odwh_data.ead_charge_annual_rental(
    "connection_type$" character,
    "data_rate$" integer,
    "start_datetime$" timestamp without time zone)
  RETURNS numeric AS

SELECT
    annual_rental
FROM
    odwh_system.ead_credit_charge
WHERE
    connection_type = connection_type$
AND data_rate = data_rate$
AND start_datetime$::date BETWEEN COALESCE(valid_from,start_datetime$::date) AND COALESCE(valid_to,start_datetime$::date);

Below is an example of the odwh_system.ead_credit_charge table:

Currently is a data_rate is passed that is not in the list is will not return an annual_rental value. I would like to update this so if the value for data_rate is 30 it will take the next higher value e.g. 100 so annual_rental of £185.00 would be returned.

Currently is a data_rate is passed that is not in the list is will not return an annual_rental value. I would like to update this so if the value for data_rate is 30 it will take the next higher value e.g. 100 so annual_rental of £185.00 would be returned.

Maybe you need
SELECT
    annual_rental
FROM
    odwh_system.ead_credit_charge
WHERE
    connection_type = connection_type$
AND data_rate >= data_rate$
AND start_datetime$::date BETWEEN COALESCE(valid_from,start_datetime$::date) AND COALESCE(valid_to,start_datetime$::date)
ORDER BY data_rate LIMIT 1;

?


Upgrade PostgreSQL 9.4 to 9.5 : LC_ALL unset I'm trying to upgrade my PostgreSQL 9.4 database to PostgreSQL 9.5 but everytime while running sudo pg_upgradecluster 9.4 main I get this error:

Stopping old cluster...
Disabling connections to the old cluster during upgrade...
Restarting old cluster with restricted connections...
perl: warning: Setting locale failed.
perl: warning: Please check that your locale settings:
    LANGUAGE = "en_US.UTF-8",
    LC_ALL = (unset),
    LC_CTYPE = "UTF-8",
    LANG = "en_US.UTF-8"
    are supported and installed on your system.
perl: warning: Falling back to the standard locale ("C").
Error: The locale requested by the environment is invalid.
Error: Could not create target cluster

But when I run locale I see the LC_ALL is actually set:

LANG=en_US.UTF-8
LANGUAGE=en_US.UTF-8
LC_CTYPE="en_US.UTF-8"
LC_NUMERIC="en_US.UTF-8"
LC_TIME="en_US.UTF-8"
LC_COLLATE="en_US.UTF-8"
LC_MONETARY="en_US.UTF-8"
LC_MESSAGES="en_US.UTF-8"
LC_PAPER="en_US.UTF-8"
LC_NAME="en_US.UTF-8"
LC_ADDRESS="en_US.UTF-8"
LC_TELEPHONE="en_US.UTF-8"
LC_MEASUREMENT="en_US.UTF-8"
LC_IDENTIFICATION="en_US.UTF-8"
LC_ALL=en_US.UTF-8

I can remember having the same issue while upgrading from PostgreSQL 9.3 to PostgreSQL 9.4 but that time I fixed this by adding this line to /etc/environment:

LC_ALL=en_US.UTF-8

But this doesn't work anymore.

After many attempts to fix this problem, this command finally worked for me:

export LC_CTYPE=en_US.UTF-8 export LC_ALL=en_US.UTF-8


How to pivot a column showing its content as new columns I'm working with MySQL 5.6 and I'm having headaches with this issue. I've been looking in several groups the result, but I think this isn't very common.

I have a table like the following one:

direc       pro_id  program                                       project
----------  ------  ---------------------------------------       -------
31111-0201  191     Empresa Exportadora                           K0002
31111-0201  192     Apertura de Empresas                          K0002
31111-0201  223     Promoción y Atracción de Inversiones...       K0002
31111-0201  191     Empresa Exportadora                           K0003
31111-0201  192     Apertura de Empresas                          K0003
31111-0201  223     Promoción y Atracción de Inversiones...       K0003

31111-0501  190     Ingreso de detenidos en centro de detención   E0045
31111-0501  190     Ingreso de detenidos en centro de detención   E0159
31111-0501  190     Ingreso de detenidos en centro de detención   O0059
31111-0501  190     Ingreso de detenidos en centro de detención   P0042
31111-0501  190     Ingreso de detenidos en centro de detención   R0043

31111-0601  224     Administración de servicios básicos           O0060
31111-0601  228     Análisis Jurídico en las liquidaciones        O0060
31111-0601  232     Administración de seguros por siniestralidad  O0060
31111-0601  224     Administración de servicios básicos           U0135
31111-0601  228     Análisis Jurídico en las liquidaciones        U0135
31111-0601  232     Administración de seguros por siniestralidad  U0135

I need a kind of pivot, but every project would be a new column and the result could be 1 or n columns (projects), the content of those columns would be a zero (that will be captured with percentages).

I've separated with a line the data cause the result I'm looking for is by direc, in this case I need to show as columns the projects K0002 and K0003 for the 31111-0201 direc, the 31111-0501 direc have the result of five and that would be columns and so on.

Following this sample the result I need is like this (I did a dynamic table in Excel for the sample):

Thanks in advance for your help.

Found the answer in the next url:
https://vitobotta.com/2015/12/10/mysql-dynamic-column-name/
And I post the example for those who have a similar problem

The final query is:

DROP TABLE IF EXISTS tmp_proyecto;

CREATE TEMPORARY TABLE tmp_proyecto AS
SELECT DISTINCTROW a.pro_id, a.programa, b.proyecto, CAST(0 AS DECIMAL(8,2)) as total
FROM programas as a
INNER JOIN presupuesto_aprobado as b ON a.direc = b.direc
WHERE a.direc='31111-0601' AND a.pro_ano=2018
ORDER BY proyecto, pro_id;

SELECT CONCAT('
SELECT pro_id, programa, ',proyectos,'
FROM tmp_proyecto
GROUP BY pro_id
ORDER BY pro_id'
)
INTO @query
FROM
(
SELECT GROUP_CONCAT(CONCAT('IFNULL(MAX(CASE WHEN proyecto=''',proy_actual,''' THEN total END), ''-'') AS "',col_name,'"')) proyectos
FROM (
SELECT proy_actual, CONCAT('P_',proy_actual) AS col_name
FROM (SELECT DISTINCT proyecto AS proy_actual FROM tmp_proyecto) AS unicos
) proy_como_campos
) result;

PREPARE statement FROM @query;
EXECUTE statement;
DEALLOCATE PREPARE statement;

As a result:

pro_id  programa                              P_K0002   P_K0003
191     Empresa Exportadora                   0.00      0.00
192     Apertura de Empresas                  0.00      0.00
223     Promoción y Atracción de Inversiones… 0.00      0.00

Thanks anyway


MongoDB doesn't start after changing the data directory I have installed a mongodb instance using yum.. Now everything works fine. I started the service using service mongod start. It works well. Then I changed the data directory and log path in the configuration file. I restarted the server again and I started the service. But I get the below error:

Restarting mongod (via systemctl):  Job for mongod.service failed. See 'systemctl status mongod.service' and 'journalctl -xn' for details.
                                                           [FAILED]

When I give systemctl status mongod.service I get the below:

 Loaded: loaded (/etc/rc.d/init.d/mongod)
   Active: failed (Result: exit-code) since Wed 2015-03-18 11:35:56 IST; 22s ago
  Process: 10672 ExecStop=/etc/rc.d/init.d/mongod stop (code=exited, status=0/SUCCESS)
  Process: 10841 ExecStart=/etc/rc.d/init.d/mongod start (code=exited, status=1/FAILURE)
 Main PID: 10509 (code=exited, status=0/SUCCESS)

Mar 18 11:35:56 localhost systemd[1]: Starting SYSV: Mongo is a scalable, document-oriented database....
Mar 18 11:35:56 localhost runuser[10850]: pam_unix(runuser:session): session opened for user mongod by (uid=0)
Mar 18 11:35:56 localhost runuser[10850]: pam_unix(runuser:session): session closed for user mongod
Mar 18 11:35:56 localhost mongod[10841]: Starting mongod: [FAILED]
Mar 18 11:35:56 localhost systemd[1]: mongod.service: control process exited, code=exited status=1
Mar 18 11:35:56 localhost systemd[1]: Failed to start SYSV: Mongo is a scalable, document-oriented database..
Mar 18 11:35:56 localhost systemd[1]: Unit mongod.service entered failed state.

When I give journalctl -xn I get the below:

-- Logs begin at Wed 2015-03-18 08:56:56 IST, end at Wed 2015-03-18 11:35:56 IST. --
Mar 18 11:30:01 localhost systemd[1]: Starting Session 20 of user root.
-- Subject: Unit session-20.scope has begun with start-up
-- Defined-By: systemd
-- Support: http://lists.freedesktop.org/mailman/listinfo/systemd-devel
--
-- Unit session-20.scope has begun starting up.
Mar 18 11:30:01 localhost systemd[1]: Started Session 20 of user root.
-- Subject: Unit session-20.scope has finished start-up
-- Defined-By: systemd
-- Support: http://lists.freedesktop.org/mailman/listinfo/systemd-devel
--
-- Unit session-20.scope has finished starting up.
--
-- The start-up result is done.
Mar 18 11:30:01 localhost CROND[10712]: (root) CMD (/usr/lib64/sa/sa1 1 1)
Mar 18 11:35:56 localhost systemd[1]: Starting SYSV: Mongo is a scalable, document-oriented database....
-- Subject: Unit mongod.service has begun with start-up
-- Defined-By: systemd
-- Support: http://lists.freedesktop.org/mailman/listinfo/systemd-devel
--
-- Unit mongod.service has begun starting up.
Mar 18 11:35:56 localhost runuser[10850]: pam_unix(runuser:session): session opened for user mongod by (uid=0)
Mar 18 11:35:56 localhost runuser[10850]: pam_unix(runuser:session): session closed for user mongod
Mar 18 11:35:56 localhost mongod[10841]: Starting mongod: [FAILED]
Mar 18 11:35:56 localhost systemd[1]: mongod.service: control process exited, code=exited status=1
Mar 18 11:35:56 localhost systemd[1]: Failed to start SYSV: Mongo is a scalable, document-oriented database..
-- Subject: Unit mongod.service has failed
-- Defined-By: systemd
-- Support: http://lists.freedesktop.org/mailman/listinfo/systemd-devel

Can anyone help me to fix this? Thanks!!!

P.S.:
The data directory I have created has the all permissions for the user. But again if I change the data directory to default (/var/lib/mongodb) it works fine.

I ran into a similar issue and found it to be a misconfigured mongod.conf file in my case.  It could also be the permissions on the new directory are not set properly.  chown -R mongod:mongod <directory name> was how I ensured access (and of course the chmod 600 <dir> as well).  Lastly, run an ls -Z to ensure the context is correct.  I just compared to the default directory which worked for me.

If this is not resolved already, please show the contents of your log file as well.  There may be some clues there.


Can replicated commands be forced to follow each other in transactional replication? We have transactional replication setup between an SQL Server 2012 box and a SQL Server 2016 box.

Every half an hour the source table articles are altered by DELETE then INSERT sequence. This sequence comes from a stored procedure executed by an SSIS packgage. Sometimes the DELETE has been replicated to the subscriber, but the INSERT hasn't made it there yet.

The result is interim missing data at the subscriber. This interim state can last several minutes as the commands get replicated out to all the articles under replication. These table articles are used as the basis for SSRS reports at the subscriber, which become inaccurate due to this issue.

Inside the stored procedures that house the DELETE - INSERT statements we tried wrapping both commands in BEGIN TRAN - COMMIT TRAN, but this didn't appear to help.

Ideally the delete - insert would be combined to happen at once (kind of like MERGE) using a different SQL command, or wrapping the commands in something akin to BEGIN TRAN - COMMIT TRAN

I've trended a row count on the replicated tables at the subscriber and there can be a span of 4 - 10 minutes where the deletes have occurred, but the inserts have not.

It sounds like you'd benefit from using procedural replication for those stored procedures.  That way, SQL Server will just run the procedures on the replicated instance rather than replicating all the DELETE and INSERT operations.  I'll wager that will be much faster and will reduce latency in addition to fixing the issue you're seeing where the destination tables are empty for a period of time.


What is the log backup schedule? I need to see the log backup schedule for a database. Where is this information located in SQL Server Management Studio?

This depends on how your log backups are set up.  If you are using Maintenance Plans, then look up the specific plan under Management->Maintenance Plans in your SSMS Object Browser.  If you are using a SQL Agent Job, then you'll need to look at the schedule attached to that specific job by opening the job and looking at the schedule on the schedule page.

You can also query the msdb.dbo.backuphistory table to see when your log backups have been taken with the following query:

select
    database_name
    ,backup_start_date
    ,backup_finish_date
from msdb.dbo.backupset
where type = 'L'
order by database_name,backup_start_date desc


Detecting Orphaned Users We have exported Microsoft SQL 2014 server and rebuilt master DB. Users are missing now, so we need to detect orphaned users.
We have found article:
https://docs.microsoft.com/en-us/sql/sql-server/failover-clusters/troubleshoot-orphaned-users-sql-server?view=sql-server-ver15#:%7E:text=%20Identify%20orphaned%20users%20in%20those%20environments%20with,are%20user%20SID%27s%20in%20the%20user...%20More%20
It says to use code in user database:
SELECT dp.type_desc, dp.SID, dp.name AS user_name
FROM sys.database_principals AS dp
LEFT JOIN sys.server_principals AS sp
    ON dp.SID = sp.SID
WHERE sp.SID IS NULL
    AND authentication_type_desc = 'INSTANCE';

What is this user database? Where the code should be run to detect orphaned users? Now the server logins are missing. How to get same users as in original database?

That might've been a little bit of poor wording on Microsoft's part but basically what they meant is run that query in the context of the database which you want to check if there are Orphaned Users for. So which ever database you run it under, it'll tell you only for that database of there are Orphaned Users.
They clarify that slightly, just below the example query they provide in the article you linked:

The output lists the SQL Server authentication users and corresponding
security identifiers (SID) in the current database that are not linked
to any SQL Server login.

So if you have 3 databases on your server, for example, then you'd need to run that query 3 times (once in the context of each database) to verify for all of them.


How to drop a column named 'column'? I need to delete a column named "column" form a table but the standard:

mysql> ALTER TABLE table_name DROP COLUMN 'column';

returns:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''column'' at line 1

I am thinking "column" is reserved word and shouldn't be used. But how do I get around to deleting it ?

You are correct to conclude that "column" is a keyword. Fortunately, you can escape the name so that, keyword or not, it is accepted:

mysql> ALTER TABLE table_name DROP COLUMN `column`;

Notice the backticks around the column name. This is always the safest way to qualify MySQL object names (for example, did you realize it is possible to use a space in a column name? Not that I encourage this; but it is possible).

If you're curious to look at more craziness, take a look at my post `;`.`*`.`.` is a valid column name, which discusses the over-flexibility in object names in MySQL.


Find columns referenced by virtual column expression Does anyone know of any Oracle dictionary views that hold references of 'real' columns to virtual columns?

For example lets say I have a table like this:

create table t (
 c1 varchar2(5)
 ,c2 as (c1 || '*')
 )

I'm after a way of determining that c1 has a dependency of c2 (without trying to parse user_tab_cols.data_default).  I'm writing a schema synchronising tool and I'm trying to work out what I need to do to synchronise a table with a 'model' performing the minimal amount of work.  If a model showed that c1 from the above table changed from varchar2(5) to varchar2(6), I'm hoping to be able to determine that I need to do something like:

alter table t modify( c2 as ( null ) );
alter table t modify( c1 varchar2(6) );
alter table t modify( c2 as ( c1 || '*' ) );

And avoid an ORA-54031.

[EDIT]

It seems I wasn't clear enough in explaining my problem so here goes.

When our developers start working on a bug they create a git branch of the application along with a database schema that is populated with the contents of the master git branch. While working on the bug the developer may make DML changes or may occasionally make DDL changes (as well as code changes of course). Once their bug has been completed and reviewed, their git branch is merged with master. The sync tool allows a complete 'diff' to be done on the database schema - both from a content and a structure perspective. This forms part of the review. The developer uses the sync tool to generate an export of their schema and this export is part of the branch. After the bug branch is merged to master, the master database schema is sync'd from the export done by the developer.

Yes, scripts would be one solution to the problem but it requires a level of discipline that is not present in all developers. The sync tool currently handles the scenario I outlined but imo somewhat clumsily. The reason I asked my original question was that if I was able to determine via a dictionary view the relationship between real and virtual columns, the sync sql my tool generates would be more 'surgical'.

Thanks.

User_tab_cols shows that there is a dependancy when virtual_column contains 'YES', which answers your need to determine if there is any dependency at all.

In order to determine what the actual virtual column consists of, one still needs to parse data_default.

SELECT column_name
  ,      virtual_column
  ,      segment_column_id
  ,      internal_column_id
  FROM   user_tab_cols
  WHERE  table_name = 'T'
  ORDER  BY column_id;


Maintenance Plan fails but the query it generates Runs I have an SQL Server 2005 Enterprise Edition whose Maintenance plan fails constantly with the error:

backup MYSERVER (MYSERVER)
Backup Database on MYSERVER
Databases that have a compatibility level of 70 (SQL Server version 7.0) will be skipped.
Databases: All databases
Type: Differential
Append existing
Task start: 2011-10-18T00:10:09.
Task end: 2011-10-18T00:10:09.
Failed:(-1073548784) Executing the query "BACKUP DATABASE [model] TO  DISK = N'\\myNetworkDrive\\opovo\\BackupSQL\\MYSERVER\\model\\model_backup_201110180010.bkp' WITH  DIFFERENTIAL ,  RETAINDAYS = 13, NOFORMAT, NOINIT,  NAME = N'model_backup_20111018001008', SKIP, REWIND, NOUNLOAD,  STATS = 10
" failed with the following error: "Cannot open backup device 'C:\\Program Files\\Microsoft SQL Server\\MSSQL.1\\MSSQL\\Backup\\Arca\\opovo\\BackupSQL\\MYSERVER\\model\\model_backup_201110180010.bkp'. Operating system error 3(The system cannot find the path specified.).
BACKUP DATABASE is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

But the query that this maintenance plan generates:

BACKUP DATABASE [model] TO  DISK = N'\\myNetworkDrive\\opovo\\BackupSQL\\MYSERVER\\model\\model_backup_201110180010.bkp' WITH  DIFFERENTIAL ,  RETAINDAYS = 13, NOFORMAT, NOINIT,  NAME = N'model_backup_20111018001008', SKIP, REWIND, NOUNLOAD,  STATS = 10

runs normally.

even stranger is the error message: "Cannot open backup device 'C:\Program Files(...)" maybe there's a difference between how SSIS and SSMS/SS Agent handles the backslash?

The user for SQL Agent and the user with which I ran this query successfully in all these cases was a domain user "ABC.MyDomainUser" that has permissions to access the network mapping \myNetworkDrive. I even used MSTSC to log in the server that runs SQL Server and ran the query locally, it runs fine, only fails when in the maintenance plan.
Is this a bug? What am I missing here? What is the elegant way to backup to a network location?
Thanks in advance,
Lynx Kepler

The SQL Server Agent account has both share and local security permissions right?

I would also ensure that you have given the SQL Server Agent appropriate network permissions -- it really sounds like a permission issue.