different login credentials based on user

You typically have to weight the cost of doing so vs. the benefits ... but benefit in risk management is difficult to quantify.

Basically, it comes down to what the cost of an exploit would be, and what the likihood of it happening are.

So, having to restore from backup because someone managed to drop a table which creates a denial of service, and being down for a day has a cost to the company in terms of what profit they'd have made in that given time, but there's also an issue of reputation loss (ie, customers/users who stop doing business with you, or potential users who are less likely to do busines in the future) ... but we have to balance this by the likehood of someone successfully attacking the site and causing this.

If you're not storing credit cards, and you're not a big target (the type of site people would brag about taking out), you're less likely to be hacked ... although, if you're running commonly distributed software, you still risk attacks by script kiddies who are just looking for people running software with a known exploit.

...

What our security folks don't seem to understand is that it's a balancing act -- some changes for security will create a burden on your users.  And sometimes, the security itself will cause outages (eg, one of our external partners moves IP ranges ... but the new holes in the firewall weren't made, and due to a "network hold" we can't get any changes made for over a week) or just performance degredation.

Sometimes it's just that it takes longer to code, or more headaches to maintain, etc.

But it's something you have to answer for yourself -- is the cost worth the benefit of having made the change?  (and sometimes, if the cost is just in man-power, was there an opportunity cost; ie, could you have been doing something else that would derive even more benefit with your time?)


This line

set @URLID := (SELECT ifnull(id,0) FROM url_productivity WHERE url = @newShortURL LIMIT 1);

isn't returning any rows at all, therefore @URLID retains the value set in the preceding line i.e. -1.


Do I want an index if all I care to query is field not null

SELECT id FROM accesses WHERE token IS NOT NULL;

The perfect index for this specific query would be a partial index:

CREATE INDEX accesses_foo_idx ON accesses(id) WHERE token IS NOT NULL;

The index condition is the important part.
On top of it, since you only retrieve id which is covered by the index, you can get index-only scans out of this (if the table is vacuumed enough).

Effectiveness grows with the number of rows excluded from the index this way (and also with the number of columns in the table). I.e.: especially beneficial for few non-null values in token (in a table with many more columns).

Related:

Unexpected Seq Scan when doing query against boolean with value NULL
Index optimization with dates


How to drop a DB2 instance when the instance owner was removed

If you dropped the instance from the file system (perhaps while DB2 was down with db2stop), then, DB2 still "knows" about the instance because its directories contain a reference to it.

I would perhaps try to uncatalog the instance first (this would remove it from the directories).

Try the following:

db2 uncatalog node <instance name>;
db2 terminate;

And see if that doens't help. You may also need to remove the instance definition from the DB2 registry:

# make sure there is a space after the equals sign to unassign this variable.
db2set DB2INSTDEF= -g
db2stop
db2start

As with anything, try to take a backup of everything before you mess around so you don't make it worse.


Problem with a partitioning switch script

@wBob got it in one. It's all about those NULLs.

The following script creates the Destination table with a NOT NULL modified column and it alters the same column on the TableName_Partition2 to be NOT NULL after creation.

This allows the SWITCH to run.

CREATE TABLE [dbo].[SourceTable](
    [Col1] [bigint] NULL,
    [Col2] [int] NULL,
    [Col3] [int] NULL,
    [Col4] [int] NULL,
    [Col5] [int] NULL,
    [Col6] [money] NULL,
    [Modified] [datetime] NULL,
    [ModifiedBy] [varchar](50) NULL,
    [ModifiedType] [char](1) NULL
);

CREATE CLUSTERED INDEX [SourceTable_Modified] ON [dbo].[SourceTable]
(   [Modified] ASC   )
GO

CREATE PARTITION FUNCTION [ThirteenMonthPartFunction](datetime) AS
 RANGE RIGHT FOR VALUES
    (   N'2015.08.01',
        N'2015.09.01',
        N'2015.10.01',
        N'2015.11.01',
        N'2015.12.01',
        N'2016.01.01',
        N'2016.02.01',
        N'2016.03.01',
        N'2016.04.01',
        N'2016.05.01',
        N'2016.06.01',
        N'2016.07.01',
        N'2016.08.01'
 );

CREATE PARTITION SCHEME [ThirteenMonthPartScheme]
    AS PARTITION [ThirteenMonthPartFunction] ALL TO ([PRIMARY]);

CREATE TABLE [dbo].[DestinationTable](
    [Col1] [bigint] NULL,
    [Col2] [int] NULL,
    [Col3] [int] NULL,
    [Col4] [int] NULL,
    [Col5] [int] NULL,
    [Col6] [money] NULL,
    [Modified] [datetime] NOT NULL,
    [ModifiedBy] [varchar](50) NULL,
    [ModifiedType] [char](1) NULL
) ON ThirteenMonthPartScheme (Modified);
GO

CREATE CLUSTERED INDEX [DestinationTable_Modified] ON [dbo].[DestinationTable]
(
    [Modified] ASC
) ON ThirteenMonthPartScheme (Modified);
GO

INSERT [dbo].[SourceTable]
VALUES  (1,1,1,1,1,9.99, '2015.08.01', 'James', 'A'),
        (2,1,1,1,1,9.99, '2015.08.02', 'James', 'A'),
        (3,1,1,1,1,9.99, '2015.08.03', 'James', 'A'),
        (4,1,1,1,1,9.99, '2015.09.01', 'James', 'A'),
        (5,1,1,1,1,9.99, '2015.09.02', 'James', 'A'),
        (6,1,1,1,1,9.99, '2015.09.03', 'James', 'A'),
        (7,1,1,1,1,9.99, '2015.10.01', 'James', 'A'),
        (8,1,1,1,1,9.99, '2015.10.02', 'James', 'A')

SELECT *
    INTO [TableName_Partition2]
FROM  SourceTable
WHERE Modified >= N'2015.08.02'
  AND Modified < N'2015.09.01';
GO

ALTER TABLE [TableName_Partition2] ALTER COLUMN Modified datetime NOT NULL
GO

CREATE CLUSTERED INDEX [TableName_Modified]
    ON [dbo].[TableName_Partition2] ([Modified] ASC);
GO

ALTER TABLE [dbo].[TableName_Partition2]
    ADD CONSTRAINT [CK_TableName_Partition2]
    CHECK ([Modified] >= N'2015.08.01'
      AND  [Modified] < N'2015.09.01');
GO

ALTER TABLE [TableName_Partition2]
    SWITCH TO [DestinationTable] PARTITION 2;
GO


What is the Best way to set Disaster recovery setup for percona Xtra DB Cluster?

In the latest Percona XtraDB cluster version it is possible to make WAN segments so that there is more efficient communication between nodes. If you didn't do that, all nodes would try to replicate to all nodes, which is highly inefficient for Galera. With Galera 3/PXC 5.6, you can establish those so that only one node at a time will replicate to the other segments. This was specifically thought for geographical redundancy.

Before 5.6:

  ---------------
 /     /   \     \
A --- B --- D --- E   (A, B, C Local nodes; D,E Remote nodes; all talk to all)
 \   /     /     /
   C ------------

After 5.6 (Galera 3):

[ A --- B ] --- [ D --- E ]   (no redundant communication between segments)
[  \   /  ]
[    C    ]

In any case, think that the round-trip time will limit the minimum latency for each transaction, which means that using geographical replication will make the cluster much slower.

An alternative option that you can setup is combining Galera and standard replication, like this:

 A --- B ~~~ D --- E (--- : Galera replication; ~~~: MySQL replication )
  \   /
    C

Standard MySQL replication is way more efficient, as it does not wait for the "slaves" to confirm the changes (it does not use group communication). The disadvantage is clear: being asynchronous it does not guarantee that absolutely all changes applied to the primary cluster has been transmitted to the slave node or nodes. If the replications is well managed that should only mean less than 1 second of transactions, but this is certainly a no-go for banking applications or other applications where loss of information is impossible even if the whole datacenter fails. Whoever, that is something that most web applications and services can handle. On the negative aspect, standard replication has some disadvantages, specially if it is not well managed, like potential data drifts or extra latency due to it being single-threaded.


How to capture PRINT statements from TSQL called by CLR stored procedure

In order to capture messages (either from PRINT or RAISERROR('', 1, 10) or like it) in .NET (regardless of calling a Stored Procedure or ad hoc SQL), you need to set up a method that will get called by the SqlConnection.InfoMessage event.

The basic implementation is as follows:

[SqlProcedure()]
public static void Print_CLR()
{
    using (SqlConnection conn = new SqlConnection("context connection=true"))
    {
        conn.InfoMessage += new SqlInfoMessageEventHandler(CaptureMessage);
        conn.Open();

        using (SqlCommand c = new SqlCommand("exec dbo.Print_TSQL", conn))
        {
            c.ExecuteNonQuery();
        }
    }
}

private static void CaptureMessage(object sender, SqlInfoMessageEventArgs args)
{
  // do something with args.Message

  return;
}

At this point, you still need to do something with the Message. While there are a few options, some of them have security implications due to the environment. SQL Server's CLR host is highly constrained compared to the main CLR running on the OS that is used for Windows Apps, Console Apps, and ASP.NET apps.

If you use the static method approach as shown above, then what you do with the Message will impact the PERMISSION_SET value for the assembly containing this code:

If you simply want to print the message back to the user, then you can do something like the following which can be done in a SAFE assembly:

SqlContext.Pipe.Send(args.Message);

If you want to write the messages to a file, then you can do something like the following which can be done in an EXTERNAL_ACCESS assembly:

File.AppendAllText(FILE_PATH_CONSTANT, args.Message);

This might cause extra latency and not be worth it. But on the other hand, if the process fails in the middle, whatever was written to the file would still be there, so there is definitely some benefit to this approach.
But if you want to store the messages in a variable to do something with later, then you will need a static class variable since the method to capture the message is static. For example, you can declare private static m_Messages StringBuilder = new StringBuiler("");. Then, in your CaptureMessage method you can do the following:

m_Messages.AppendLine(args.Message);

This seems simple enough. And for most any environment outside of SQLCLR it is. However, using a static variable in SQLCLR requires that the assembly have a PERMISSION_SET of UNSAFE, and it is best to avoid that if at all possible. And the reason that the assembly is required to be marked as UNSAFE is that the AppDomain where this code is running is shared across all Sessions / SPIDs. Hence, a static class variable is also shared across Sessions and that can lead to very odd behaviour.

Thankfully, all hope is not lost if you want to collect messages to an instance variable. To do this, you need to define the handler inline using an anonymous delegate. The anonymous method is within the same scope as the rest of your code so it has access to local variables. This is quite handy as it allows you to easily store the messages in an assembly that has a PERMISSION_SET of SAFE (which is highly preferred).

[SqlProcedure()]
public static void Print_CLR()
{
    StringBuilder _Messages = new StringBuilder("");

    using (SqlConnection conn = new SqlConnection("context connection=true"))
    {
        conn.InfoMessage += delegate(object sender, SqlInfoMessageEventArgs args)
        {
            SqlConnection _Connection = (SqlConnection)sender;
            // _Connection.DataSource is the "Server" of the ConnectionString
            // _Connection.Database is the CURRENT database of the Connection

            _Messages.Append(args.Message);

            return;
        };

        conn.Open();

        using (SqlCommand c = new SqlCommand("exec dbo.Print_TSQL", conn))
        {
            c.ExecuteNonQuery();
        }
    }
}


The documentation is misleading.  I read it exactly the same way you do, which isn't what the utility does.

Adding --flush-privileges causes mysqldump to include the following in the backup file, after dumping the mysql schema...

--
-- Flush Grant Tables
--

/*! FLUSH PRIVILEGES */;

...which of course causes the server where the dump is being restored to re-read the potentially-changed grant tables.

And, that's all it does.

Confirmation of this can be found in the source code for mysqldump "10.13" (the version bundled with MySQL Server 5.5.30):

if (flush_privileges && using_mysql_db == 0)
{
  fprintf(md_result_file,"\n--\n-- Flush Grant Tables \n--\n");
  fprintf(md_result_file,"\n/*! FLUSH PRIVILEGES */;\n");
}

The using_mysql_db == 0 comparison was confusing at first, but it turns out to be the return value from a string comparison function where "0" means "identical."


update one column based on query containing another (joined) table

Disclaimer, I don't use PostgreSQL but I'm pretty familiar with SQL Server.

From what I understand in the PostgreSQL documentation here, I think the error you are getting is caused by the inclusion of public."section" INNER JOIN in your FROM statement. When using UPDATE you specify the table you are updating so adding public."section" in the FROM is not necessary. Per the documentation "Do not repeat the target table as a from_item unless you intend a self-join (in which case it must appear with an alias in the from_item)."

If you change your code to what I have below, I think it would resolve your issue. Though I'm not sure about the quotation marks you have, I just copied your format. The main difference being, only have the tables you're joining to in the FROM and move the ON to the WHERE.

UPDATE public."section"
SET public."section".column = public."event".oldcolumn
FROM public."event"
WHERE public."section".event = public."event".id AND public."section".id = 1


There are a bunch of ways you can do this, one of them is with DISTINCT ON as @Ypercube has suggested,

SELECT DISTINCT ON (path2, sha1) path2, sha1, path1
FROM table_name
ORDER BY path2, sha1;

You can also use an ordered-set aggregate which should generally be slower.

SELECT percentile_disc(0) WITHIN GROUP (ORDER BY path1) AS path1, path2, sha1
FROM table_name
GROUP BY path2, sha1;


Sql Server Plan - what is a Filter after an Index operation?

Filter operators are often used for non-SARGable predicates, or when the predicates are too complicated to be pushed effectively to the index access operation.

In your case, I'm willing to bet that it's a series of optional predicates. These are not SARGable: Conor vs. Optional Parameters .

  ...plus some other conditions on some other SP parameters...

If your code is doing something like col = @var or @var IS NULL, it would fall into that category, and may result in the filter operator.


Difference between row-based and statement-based replication in MySQL?

Statement-Based Replication (SBR) will replicate SQL Statements. It is easier to read using mysqlbinlog dump program.

Row based replication (RBR) will replicate actual data changes. Binary logs and relay logs will grow much faster than SBR. You also cannot identify the SQL that generated the changes.
(Please read updated information for MySQL 5.6.2. below)

Here is a more comprehensive pros-and-cons list : http://www.databasejournal.com/features/mysql/article.php/3922266/Comparing-MySQL-Statement-Based-and-Row-Based-Replication.htm

MySQL will allow both types of statement/row formats in its binary logs. I recommend STATEMENT based. Default binary log format is MIXED which is OK.

Since you are using replicate-ignore-db and replicate-do-db, I would trust Statement-Based Replication.

Update for MySQL 5.6.2:

Introduced in MySQL 5.6.2 the binlog_rows_query_log_events system variable causes a MySQL 5.6.2 or later server to write informational log events such as row query log events into its binary log. So we can identify the SQL that generated the changes.

Reference: 17.1.4.4 Binary Log Options and Variables


Postgres query plan shows sequential scan instead of a index scan with on the fly data

Your test table is very small:

  rows=129 width=587

Around 10 data pages of 8 kB. Your index occupies 2 data pages most likely, incl. 1 meta page. (That's unless either is bloated with dead tuples or something, which decreases general performance while typically favoring the use of indexes.)

Postgres estimates that it pays to involve the index for a single input value, but not for two or more, for which case more and more data pages have to be read anyway. The overhead of involving the index won't pay any more. That may explain the different query plans. Hardly important, though, for the small table where either way is extremely fast, as was pointed out in comments already.

The important point of interest here: Postgres will use the index once the table gets big enough. Missing statistics for the implicit temporary table from the CTE won't stand in the way. While you are right that Postgres does not have detailed statistics there, like a MCV list (most common values) etc., it does know and factor in the number of rows - as can be seen in the query plan you posted:

  CTE Scan on lcaseid  (cost=0.00..0.04 rows=2 width=32)

And statistics on the table are typically overwhelmingly more important anyway.
If you have a case where detailed statistics on the input data will make a difference, you can create a temporary table instead of the CTE and ANALYZE it manually. (But I don't see that here.) See:

Are regular VACUUM ANALYZE still recommended under 9.1?

Either way, if your input values won't contain duplicates, you can simplify:

SELECT c.*
FROM   products.catalog c
JOIN  (
   VALUES ('Frasier'),
          ('Big brother')
   ) i(icaseid) ON lower(i.icaseid) = lower(id);

A bit faster because it gets rid of both the CTE and the IN which add extra cost. And less noise.
I don't see anything else to improve for either query or index. If you only need a small selection of columns from a much wider row, there might be potential for an index-only scan. But while you SELECT *, there is not.

There is the additional module citext, which might have useful features for you. But I don't generally recommend it, due to side effects and limitations.


Same user cannot 'see' view on remote oracle database athough dblink is valid

Do not quote schema or object names in your SQL. If forces case-sensitivity in the names where none exists by default in Oracle. i.e. TABLE_NAME is the same as table_name, but "TABLE_NAME" is not the same as table_name. Even if you were going to quote the object names to enforce case-sensitivity, you should use double-quotes and not single quotes.
Also, be careful what you're granting: "GRANT ALL" is not appropriate if all the user needs to do is select data.
     SELECT 'GRANT ALL ON "'||owner||'"."'||object_name||'" TO <DB_LINK_NAME>' grantSQL
       FROM all_objects
      WHERE owner = 'OWNER_NAME'
        AND object_type = 'VIEW'
        AND object_type IN
            (
              select view_name
              from sys.dba_views
              where owner = 'OWNER_NAME'
            )
   ORDER BY object_type, object_name

produces a SQL statement like this:
GRANT ALL ON 'owner'.'object_name' TO DB_LINK_USER_NAME

When what you most likely want is this:
GRANT SELECT ON owner.object_name TO DB_LINK_USER_NAME

Your query also seems a little convoluted. DBA_VIEWS gives you everything you need, so why join with ALL_OBJECTS which may not contain the matching view names? The order of the grants are made is also not important, so why include an ORDER BY clause that will force extra I/O to sort the results in the TEMP tablespace?
Try this:
     SELECT 'GRANT SELECT ON "||owner||"."||object_name||" TO <DB_LINK_USER_NAME>' grantSQL
       FROM dba_views
      WHERE owner = 'OWNER_NAME'


If your question is about MS-Sql you can use this syntax:

DECLARE @data xml;

SET @data =
'<Message>
  Hello
  bye
</Message>';

SELECT d.lin.value('.', 'varchar(100)') as [Code]
FROM   @data.nodes('Message/Code') d(lin)
GO

| Code   |
| :----- |
| Hello  |
| bye    |

dbfiddle here


Service Credentials to use in AlwaysOn Availability Groups?

When using AlwaysOn Availability Groups does each SQL Instance need to use the same service credentials?

No, SQL Server service accounts can be different. We have a 3 node cluster running AlwaysON and on all the servers SQL Server is running with a different account.
e.g. If you have one server in NY and other in LD, then

Active Directory Accounts to be created (Below will  be sql server service accounts)
NYDB1_sql
LDDB1_sql
Add both these accounts to the group DB1-SQL (below)
Active Directory Group to be created : DB1-SQL (Both the above accounts to be members of this group).

One important thing to note, from BOL :

If two server instances run as different accounts, the system administrator must use the CREATE LOGIN Transact-SQL statement to create a login for the startup service account of the remote instance in the syslogins table of the master database of each server instance.

Also, grant the CONNECT permission on the endpoint to the service accounts.

Previously we've had the SQL Server Service account locked out which has obviously caused problems.

You can use group managed service account - which are new in Windows Server 2012. The only requirement is you need at least one Windows 2012 domain controller, and use 2012 or 2012 R2 member servers.


Is mongoreplay included in the toolset with mongodb 4.0 community version for windows?

mongoreplay is currently not included in the Windows installation packages (as at MongoDB 4.0). This tool uses the libpcap packet capture library which is available on most UNIX/Linux platforms but is not standard on Windows.

There's a relevant feature request to watch/upvote in the MongoDB Jira issue tracker: TOOLS-1730: Release  mongoreplay for windows.

I also raised DOCS-12355 as this should be more clearly noted in the documentation.


Two active + one passive node for failover clustering

I'd like to know if it is possible to have a single passive node for two SQL Servers in a failover clustering deployment.

Yep that is quite possible. You can have 3 node(let us use node instead of server) cluster with each SQL Server instance on one node A and Node B respectively  and 3rd node is their in case of failover.

You can also have two instances running on same node(suppose Node A) and other node(Node B) acting as passive node. But such configuration is not a best one which you should go with.

  If it is apt to do so, will server C run two instances of SQL Server; one for A and one for B?

In case of failover, and both nodes A and B down, yes node c will act as owner of both instance running on Node A and Node B.

  As a side question; what is the benefit of "dynamic quorum" and "dynamic witness"?

You must understand witness helps in forming quorum. I would suggest you read this blog Understanding Behavior of Dynamic Witness. If you then have a specific question feel free to ask.


PostgreSQL doesn't register the timestamp when a privilege was granted. The semantics would be unclear: what should the time be when you restore a dump? The time the privilege was originally granted or the time of the restore?

Anyway, you can get the rest of the information from the system catalogs:

SELECT roleid::regrole, member::regrole, grantor::regrole
FROM pg_catalog.pg_auth_members;


How to skip OR if AND statement row count is not 0

You want to limit your result to one row anyway, so just write your order by like this:

SELECT *
FROM hwr_story
WHERE question_count = '8'
AND (lower(subcategory) = lower('True') OR subcategory IS NULL)
ORDER BY subcategory IS NULL, RAND() LIMIT 1

subcategory IS NULL returns 1 or 0, true or false. Then just sort ascending and you got it :)


MySQL row-based replication - Delete Fails

In the absence of any obvious, logical reason why this might be happening, I'm going to shamelessly invoke the MyISAM boogey-man.

This question reminds me of one from a few years back.  It's not a duplicate question, but the underlying mechanisms could be similar.  The workaround I provided in that case was specifically based on the premise of duplicate "unique" values existing in a table.

I suspect you have latent, undetected defects in your MyISAM tables, where there are actually -- via an unknown (to me) mechanism -- duplicate primary (or unique) key values hiding below the surface.

When MyISAM selects or deletes a row by primary key, it won't see these duplicates, because as soon as it finds one row, it stops looking, because there "can't" be more to find... yet, if you SELECT * you'll get the duplicates... and deleting one of the rows would then unmask the other.

One way to test this that might work would be this:

mysql> CREATE TABLE test_table LIKE real_table;
mysql> INSERT INTO test_table SELECT * FROM real_table;

If this is indeed at the root of what's going on, you might get "lucky" and get a duplicate key error, which would prove the theory... because a duplicate key error should be impossible if the original table's data is intact.

You could, of course, review the binary logs using mysqlbinlog, to confirm that the deletions were logged... but I suspect you will find that the deletes did get logged correctly... but after the rows were deleted, the phantom rows were then visible, and caused the subsequent replication error.


I found an answer for this!

1) Create a function that returns a table containing the desired dates

2) Do a cartesian product of the timeslots with the dates

3) Find the exceptions between the booked dates and the cartesian product


How to select any couple of digits except '00' using mysql regex subset syntax?

Direct solution:

(0[1-9])|([1-9][0-9])

It is simple enough for do not look for shortening or another optimization, I think.


Primary key with "NOT FOR REPLICATION" option

NOT FOR REPLICATION indicates that when a record is replicated to this table, any value that is inserted into the identity column via the replication agent keeps it's original value from the source system, but any records added locally still increment the identity value.

UPDATE: Old Link broken

Here is the link to the table_constraint docs that details what setting NOT FOR REPLICATION will do:

Table Constraint


For this you can use user defined Varaible
Select @res := CASE WHEN A = 1 then a END, myfunction(param1, @res)
FROM table1;

Or in mysql 8
WITH myselectanswer as
(Select  CASE WHEN A = 1 then a END myresult
FROM table1)
Select myselectanswer.myresult, myfunction(param1, myselectanswer.myresult)
FROM myselectanswer


Can I shed permissions during a session?

If you use Roles to control access to your tables, you can set a "read-only" role after opening the database connection:

set role read_only_role;

Where this role has select access on the target tables.

https://dev.mysql.com/doc/refman/8.0/en/set-role.html


how to limit amount of rows in jsonb_agg & jsonb_build_object

LIMIT 3 will work just fine if you add it in the right fashion, that is before the rows are aggregated:
SELECT jsonb_agg(jsonb_build_object('me_id', me_id))
FROM (SELECT me_id FROM message LIMIT 3) AS subquery;


how to build table relationships in a firebird database?

While I'm pretty sure you should redesign your database at first (and probably read something on SQL basics), you can get what you want with triggers :)

This one inserts row into daily collection with appropriate balance:

CREATE trigger masterlist_ai for masterlist
active after insert position 0
AS
begin
  insert into "daily collection" (  "date",     balance)
                        values (new."date", new.balance);
end

And this one updates balance in masterlist after updates of daily collection (assuming date is the primary key):

CREATE trigger "daily_collection_au" for "daily collection"
active after update position 0
AS
begin
  update masterlist
  set balance = new.balance
  where "date" = new."date";
end


Is it possible to do a datapump import with a different user than did the export?

It looks like Oracle can't read the .dmp file, because it's under another user's home directory. Oracle has to be able to read it, not the Linux user you're running the impdp command as. (Contrary to my earlier misleading comments!)

Assuming the two users are in different groups, you may need to make the .dmp file world-readable:

chmod o+r /home/..../tmsmv-20120706.dmp

And usually all directories in the path need to be world-readable and world-executable:

chmod o+rx /home/..../

If you wanted the log file to be created, it would be need to be writable too (chmod o+rwx). If it's in a subdirectory of your home directory, you might need to do this to all intermediate levels.

But relaxing permissions on your home directory can be a problem, so you might be better off using a directory somewhere else. Or leave the file where expdp put it, and grant permissions on that DIRECTORY object to tmsmv instead.


How does one create a notification upon corruption being found during Automatic Page Repair under database mirroring?

Assuming you are on any edition besides express AND that you have SQL Agent enabled/running, the quickest route is to utilize Glenn Berry's alerts setup script, to configure all of the necessary parts for alerting on most of the IO related alerts (823, 824 and 825). 823 and 824 are specific to an actual IO corruption, while 825 is a lesser used warning sign of a potential storage problem (retry).

In addition to Glenn's base alerts, add an alert for error 829 (Page marked RestorePending) and error 1481 (Database mirroring could not repair physical page).  These will likely fire with corresponding 823 or 824 errors), but are mirroring/AG specific in context.

Also note, that you can historically view pages that were marked suspect/corrupt, by querying the suspect_pages table in msdb, as opposed to scanning the ERRORLOG for errors.


Splitting TempDB into multiples files equal to number of CPUs

A ratio of 1/4 to 1/2 times the number of TempDB data files to machine cores has long been the recommendation...

  But there's now even better guidance. At PASS in 2011, my good friend Bob Ward, who’s the top guy in SQL
  Product Support, espoused a new formula: if you have less than 8
  cores, use #files = #cores. If you have more than 8 cores, use 8 files
  and if you’re seeing in-memory contention, add 4 more files at a time. [link]

The last sentence has always been relevant. If you're not seeing contention, why add additional files? To play safe, most will add 2-4 files as a starting point for the majority of builds but beyond that, measure and react.


How to find Oracle is configured for OLTP or OLAP?

OLTP does not require any specific configuration.

Checking the availability of OLAP libraries:

SQL> select * from v$option where parameter = 'OLAP';

PARAMETER  VALUE
---------- -----
OLAP       TRUE

Checking the availability of OLAP components in the database:

SQL> select comp_name, version, status
     from dba_server_registry where comp_name like '%OLAP%';

COMP_NAME                 VERSION    STATUS
------------------------- ---------- ----------
OLAP Analytic Workspace   11.2.0.4.0 VALID
Oracle OLAP API           11.2.0.4.0 VALID
OLAP Catalog              11.2.0.4.0 VALID

Checking if OLAP is in use (note that, this works with sampled data, the sample interval by default is 1 week, it does not represent the real-time situation):

SQL> select name, detected_usages, currently_used from
     dba_feature_usage_statistics where name like 'OLAP%' order by name;

NAME                           DETECTED_USAGES CURRE
------------------------------ --------------- -----
OLAP - Analytic Workspaces                   0 FALSE
OLAP - Cubes                                 0 FALSE


Is it possible to run an arbiter on a machine that also hosts a secondary member?

Is it possible to run an arbiter on system that also hosts a secondary member?

There is no technical restriction on running multiple processes on the same host, as long as they are listening to different port numbers. For example, you could start an arbiter listening to port 27018 (instead of the default 27017). However, you do need to consider the operational implications of colocating processes.

  Even if it is not advisable, but if it is possible, how can I install an arbiter on the secondary server for when the primary go down the secondary become the primary?

This is the standard replica set behaviour. Assuming you have a preferred primary (which will have a higher priority value), your replica set configuration would look similar to:

{
   _id : "myreplset",
   members: [
      { _id: 0, host: "machine1.example.net:27017", priority: 2 },
      { _id: 1, host: "machine2.example.net:27017" },
      { _id: 2, host: "machine2.example.net:27018" }
   ]
}

With three voting members in your replica set, the strict majority required to elect (or maintain) a primary is  two votes. If machine1 is unavailable, the members on machine2 can still elect a primary.

However, because machine2 has the majority of the voting members there are a few operational consequences:

If machine2 is unavailable, machine1 cannot elect or sustain a primary without manual intervention to force reconfigure the replica set.
In the event of a network split, machine2 will become primary.

If the arbiter (or another voting member) was on a third machine, both of these scenarios would be avoided.

You may want to consider colocating the arbiter on machine1 , so in the event of a network split the primary will remain on machine1 (reversing the operational scenarios noted above so that failover to machine2 would be a manual reconfiguration).

For a production deployment I would also recommend using a third secondary (instead of an arbiter). When your Primary-Secondary-Arbiter deployment is down a member (so effectively Primary-Arbiter) there is no longer any active replication and your deployment will not be able to acknowledge majority writes. It is definitely not ideal to have multiple data-bearing members competing for resources on the same host machine, but data redundancy and availability of majority writes is often a more important factor.

  Once installed, how can I configure the replica set for when the original primary returns and make a copy of the secondary?

Assuming you have configured the member on machine1 with a higher priority as per the above example, the expected outcome is that when machine1 is available again it will automatically resume syncing from the current primary on machine2 (as a secondary). Once the member on  machine1 catches up and is eligible to be primary, it will initiate an election based on a higher priority member being available (referred to as a priority takeover election).


You can use DISTINCT ON:

select distinct on (native_name)
    'nil' as problem_type, native_name, event_date, count
from
    point_problem_history
where
    building_fk = 3
and
    problem_type = 'nil'
order by
    native_name, event_date desc ;

which does exactly what you want. Similar to GROUP BY, it will keep one row per distinct value of native_name but - differently that group by - you don't need to use max and min functions. The row selected will be the first according to order by event_date desc, so the maximum event date per native name.


How to restore database from /var/lib/mysql

you need to make sure the ownership and privileges are correct for the directory, you can use the following commands to set it right:

chown -R mysql:mysql /var/lib/mysql/ --your mysql user may have different name, if its different you need to change it [mysql:mysql] as needed
chmod -R 755 /var/lib/mysql/

note: -R: recursive fir the directory and all its contents.


I find that error particularly disturbing because MySQL introduced INFORMATION_SCHEMA with the introduction of MySQL 5.0. @gbn's answer shows that this bug

goes back to MySQL 5.0.16
was reported on ANY operating having this
was based on the error happening in the IBM AIX 5.3 ML2 Operating System
was closed 2006-01-26

MySQL's definition of a database is simply a subfolder under datadir.

Now, here is why I find the error particularly disturbing: The INFORMATION_SCHEMA database is not supposed to be a manifested folder under datadir.

For example

datadir is /var/lib/mysql
You have two databases: db1 and db2

Go to the OS and run the following:

cd /var/lib/mysql
ls -l

You will see several folders:

mysql
db1
db2
test (since MySQL default installs a test database)
. (current dir)
.. (parent dir)

In mysql, when you do SHOW DATABASES;, you should not see . and ... The source code would have made sure of that. Now, where is INFORMATION_SCHEMA? Guess what? All tables in the INFORMATION_SCHEMA are temp tables and use the memory storage engine. Also note that you do not a see a folder called INFORMATION_SCHEMA. Now, check out the definition of INFORMATON_SCHEMA.TABLES:

mysql> use information_schema
Database changed
mysql> show create table tables\G
*************************** 1. row ***************************
       Table: TABLES
Create Table: CREATE TEMPORARY TABLE `TABLES` (
  `TABLE_CATALOG` varchar(512) NOT NULL DEFAULT '',
  `TABLE_SCHEMA` varchar(64) NOT NULL DEFAULT '',
  `TABLE_NAME` varchar(64) NOT NULL DEFAULT '',
  `TABLE_TYPE` varchar(64) NOT NULL DEFAULT '',
  `ENGINE` varchar(64) DEFAULT NULL,
  `VERSION` bigint(21) unsigned DEFAULT NULL,
  `ROW_FORMAT` varchar(10) DEFAULT NULL,
  `TABLE_ROWS` bigint(21) unsigned DEFAULT NULL,
  `AVG_ROW_LENGTH` bigint(21) unsigned DEFAULT NULL,
  `DATA_LENGTH` bigint(21) unsigned DEFAULT NULL,
  `MAX_DATA_LENGTH` bigint(21) unsigned DEFAULT NULL,
  `INDEX_LENGTH` bigint(21) unsigned DEFAULT NULL,
  `DATA_FREE` bigint(21) unsigned DEFAULT NULL,
  `AUTO_INCREMENT` bigint(21) unsigned DEFAULT NULL,
  `CREATE_TIME` datetime DEFAULT NULL,
  `UPDATE_TIME` datetime DEFAULT NULL,
  `CHECK_TIME` datetime DEFAULT NULL,
  `TABLE_COLLATION` varchar(32) DEFAULT NULL,
  `CHECKSUM` bigint(21) unsigned DEFAULT NULL,
  `CREATE_OPTIONS` varchar(255) DEFAULT NULL,
  `TABLE_COMMENT` varchar(2048) NOT NULL DEFAULT ''
) ENGINE=MEMORY DEFAULT CHARSET=utf8
1 row in set (0.05 sec)

I lay full blame for this error on the source code because just as mysql bypasses . and .. as special case folders that are not to be considered databases, INFORMATION_SCHEMA is supposed to be considered a s special case folder as well, a special case folder that does not have a manifested file under the datadir.

I would highly recommend just upgrading MySQL to the latest version because, as @gbn found, there was a bug report but it is marked as closed. This could only happen

if you are still running a very old version of mysql
this situation was not properly handled in the source code for the Ubuntu OS

@gbn gets a +1 from me for finding the bug report which helped me look a little deeper


Deadlock graph: Show SQL statement containing the phrase "password"

No, there is no trace flag or way to override this behavior.

If any statement has a restricted word in it, it will be overwritten with "-------" or "** restricted **". This is the same whether it is a trace, in cache, or using XEvents.


How to get the first or last row in ordered result set, depending by column value?

Doing that is tricky, because you need to check the differences between the data in both orders, ascending to get the first 'IN', descending to get the last 'OUT'.

With I AS (
  SELECT EMPLID, Name, PUNCHDATE, PUNCHTIME, DEVICETYPE
       , ID_Up = Row_Number() OVER (PARTITION BY EMPLID
                                    ORDER BY EMPLID, PUNCHDATE, PUNCHTIME)
       , ID_Down = Row_Number() OVER (PARTITION BY EMPLID
                                      ORDER BY EMPLID, PUNCHDATE DESC, PUNCHTIME DESC)
  FROM   Table1
), S AS (
  SELECT a.EMPLID, a.Name, a.PUNCHDATE, a.PUNCHTIME, a.DEVICETYPE
       , a.ID_Up aUID, b.ID_Up bUID, a.ID_Down aDID, c.ID_Down cDID
       , a.DeviceType aDeviceType, b.DeviceType bDeviceType, c.DeviceType cDeviceType
       , OKValues
       = CASE WHEN a.ID_Up = 1 THEN 1
              WHEN a.DeviceType = 'IN' AND b.DeviceType = 'OUT' THEN 1
              WHEN a.ID_Down = 1 THEN 1
              WHEN a.DeviceType = 'OUT' AND c.DeviceType = 'IN' THEN 1
              ELSE 0
         END
  FROM   I a
         LEFT  JOIN I b ON a.EmplID = b.EmplID And b.ID_Up = a.ID_Up - 1
         LEFT  JOIN I c ON a.EmplID = c.EmplID And c.ID_Down = a.ID_Down - 1
)
SELECT EMPLID, Name, PUNCHDATE, PUNCHTIME, DEVICETYPE
FROM   S
WHERE  OKValues = 1
ORDER BY EMPLID, PUNCHDATE, PUNCHTIME

SQLFiddle

the S CTE is to remove the CASE from the WHERE condition, in the CTE I left a lot of noise data, that can be useful to understand the CASE conditions


What is the underlying representation of BLOB data when I export it?

If you use mysqldump, you can specify the hexadecimal format for BLOB data

mysqldump -u... -p... --hex-blob --all-databases ...

Here is the help on hex-blob in mysqldump

$ mysqldump --help | grep -A 1 "blob" | head -2
  --hex-blob          Dump binary strings (BINARY, VARBINARY, BLOB) in
                      hexadecimal format.


SQL Server - One of the cores always at 100%

could be a few things, start by checking the wait types.
A couple I have seen
RSS Receive Side Scaling is not enabled so all n/w traffic is handled by a single CPU
or
Ghost cleanup running - this is a system SPID responsible for removing deleted records and runs on a single core
https://docs.microsoft.com/en-us/sql/relational-databases/ghost-record-cleanup-process-guide?view=sql-server-ver15


Is MySQL Cluster just for in-memory databases?

In NDB Cluster, data does not have be memory-resident, but I think the indexes do.

This technology is good for high availability and high activity.  It is not for the typical RDBMS application.

NDB's Replication is based on "eventual consistency", where in you can do conflicting inserts/updates, but define the rules of which one wins.

While it is closely related to the rest of MySQL, NDB Cluster is not identical; there will be some coding changes if you migrate.


How to tell from the OS whether Oracle instance is a standby, assuming no DB access?

You can try using:

ps -ef | grep -v grep | grep ora_pr

Or if data guard is in use:

ps -ef | grep -v grep | grep ora_rsm


The statement:

3NF ensures lossless decomposition while BCNF does not.

is incorrect, since both BCNF and 3NF produce decompositions that have the Lossless-join Decomposition property, that says that:

(R1,R2) is a lossless-join decomposition of R with respect to a set of FDs F if for every instance r of R that satisfies F:
πR1(r) ⋈ πR2(r) = r.

This can be seen since there is a theorem that says that a simple criterion for checking whether a decomposition (R1,R2) is lossless-join is that either:

R1 ∩ R2 → R1 ∈ F+, or
R1 ∩ R2 → R2 ∈ F+,

and both the algorithms for BCNF and 3NF produce decompositions that satisfies this property (see the literature on the subject, for instance the demonstration on Chapter 3 of: “Garcia-Molina, Hector. Database Systems: The Complete Book, Pearson Prentice Hall”).
What is true, and that differentiate 3NF from BCNF, is that the synthesis algorithm that produces the 3NF always preserves the dependencies of the original relation, while the analysis algorithm for the BCNF does not.

A decomposition (R1, R2) of R, given
FR1 ={X → Y | X → Y ∈ F+ ∧ XY ⊆ R1}, and
FR2 ={X → Y | X → Y ∈ F+ ∧ XY ⊆ R2}
preserves a dependency f iff f ∈ (FR1 ∪ FR2)+.


Hierarchial, tree-like data model, which DB type?

A graph database would be a good candidate. They specialise at retrieving interconnected data and navigating the relationships between objects. The ones I'm familiar with allow dynamic schema so different objects can have different values. Some allow classes of objects to be constructed so some consistency can be enforced. The links between objects are integral to query processing.

A document database has no explicit concept of a connection between one record / row / object / node and another. Your queries would reduce to step-by-step retrieval of an object, then following the "foreign key" pointers to other objects in the application. At least in an RDBMS the referential integrity could be enforced.


What's faster: two UPDATEs with WHERE (in / not in) or one UPDATE with CASE?

You should perform an UPDATE LEFT JOIN and set enabled based on the right side being NULL

UPDATE table1 A
LEFT JOIN table2 B
ON A.code = B.id
SET A.enabled = 1 - ISNULL(B.id);

Why should this work ?

If ISNULL(B.id) is 0, that means A.enabled is set to 1 (1 - 0) because it is in table2
If ISNULL(B.id) is 1, that means A.enabled is set to 0 (1 - 1) because it is not in table2

@ypercube suggested

UPDATE table1 A
LEFT JOIN table2 B
ON A.code = B.id
SET A.enabled = (B.id IS NOT NULL);

GIVE IT A TRY !!!


SSIS deployment failed - working with files

This isn't a problem with your data - it is a problem with your package. Usually, this is a problem with the size of your query.

It appears that internally, SSIS uses a varchar(8000) value somewhere that could actually use a varchar(max) value. When you run up against this limit, you get this ambiguous error.

There are several ways you can approach this. You can shorten your query (by removing excessive whitespace, replacing spaces with tabs, compacting indents, etc).

You can also split your query out into multiple variables or parameters, then concatenate the pieces back together with an expression task before executing the query.

Depending on the query, you could also convert it into one or more stored procedures or views, and call those within your package.

But if you keep the query inside the package, you need to reduce its length (or its piece's lengths) to less than 8000 characters.  Have fun.

Have fun - nothing is more fun than refactoring a query.


Does document "depth" affect CouchDB performance?

Per this StackOverflow thread, it sounds like lists are created by serializing and then deserializing the data.

If you were deeply nesting your documents with list inside of list, that would be a serious performance hit purely from the serialization and deserialization.

If you have a hierarchy like this, I would consider using MySQL, since it's designed for relational data.  It would be simple to implement a hierarchy using a relational DBMS compared to CouchDB.


Copy large table to archive table SQL Server 2012

You can use the following Stored Procedure, scheduled via a SQL Server Agent job, to archive records on a continual basis. You don't need to worry about the initial move of "everything older than 6 months" as it will be taken care of naturally by this process since it will be able to archive records faster than you are inserting them. Of course, if you want to get a large chunk of those rows archived immediately, you can run this Stored Procedure manually (while the job is not running), and just pass in parameter values that are slightly higher for @BatchSize and @SecondsToRun.

Important Notes:

Scheduled this to run via SQL Server Agent, probably once per hour
Set defaults for @BatchSize and @SecondsToRun to match how fast data is coming into the table
Create a NonClustered Index on dbo.AudLog ([XDATE] ASC)
Assuming that XLOGNO is in the main AudLog table, remove the IDENTITY specification from the XLOGNO column here (in AudLog_Backup) as that value comes from the main AudLog table
Change the datatype of the DETAILS column to be VARCHAR(MAX): text has been deprecated since the release of SQL Server 2005 and is more difficult to work with.

Minor notes:

Running this Stored Procedure, at least via the SQL Server Agent job if not also manually at first to get a large chuck of rows moved over, should be less work than swapping the table out, and there is no period of time when the AudLog table is empty.
The Stored Procedure will simply exit if there is nothing to archive, so it doesn't hurt to run once per hour, or maybe every 2 - 3 hours (depending on how fast new rows come in).
The Stored Procedure is throttled to exit after a certain number of seconds so that if it is causing contention on the main AudLog table, it will only be for short periods of time.
The OUTPUT clause binds the DELETE from the main AudLog table and the INSERT into the AudLog_Backup table in a Transaction, so you don't need to worry about the process failing, or system shutting down / crashing, between those two operations.

CREATE PROCEDURE dbo.AudLog_Archive
(
  @BatchSize INT = 2000, -- don't go above 3000 to avoid lock escalation
  @SecondsToRun INT = 180, -- run for 3 minutes
  @DaysToKeep INT = 185 -- archive rows older than 6 months
)
AS
SET NOCOUNT ON;

DECLARE @EndTime DATETIME = DATEADD(SECOND, @SecondsToRun, GETDATE()),
        @ArchiveDate DATETIME = DATEADD(DAY, @DaysToKeep * -1, GETDATE()),
        @RowsArchived INT = @BatchSize; -- initialize to be able to enter the loop

WHILE (@EndTime > GETDATE() AND @RowsArchived = @BatchSize)
BEGIN

    IF (EXISTS(
                 SELECT 1
                 FROM   dbo.AudLog al
                 WHERE  al.[XDATE] < @ArchiveDate
              )
        )
    BEGIN
        ;WITH batch AS
        (
            -- Keep this as SELECT * as it will alert you, via job failure, if
            -- you add columns to AudLog but forget to add them to AudLog_Backup
            SELECT  TOP (@BatchSize) al.*
            FROM    dbo.AudLog al
            WHERE   al.[XDATE] < @ArchiveDate
            ORDER BY al.[XDATE] ASC
        )
        DELETE b
        OUTPUT DELETED.* -- keep as * for same reason as noted above
        INTO   dbo.AudLog_Backup ([PVKEY], [DKEY], ...) -- specify all columns
        FROM   batch b

        SET @RowsArchived = @@ROWCOUNT;

        WAITFOR DELAY '00:00:01.000'; -- one second delay for breathing room
    END;
    ELSE
    BEGIN
        BREAK;
    END;
END;


There are many ways to export data from Oracle and automate the functionality. Be sure to understand exactly what the data export is being used for, though. If it is for interop between systems, then export in a format your receiving system can understand. If it is for backup purposes, go for the exp/expdp (data pump) method because a database backup needs to store much more than simply data. (Better yet, just use RMAN. But I know many DBAs who also like to do full db exports on a regular basis as well.)

You can use a number of tools to accomplish this, TOAD being the one that springs to mind. It has a powerful data export tool that supports scheduling. There is a free version available, but I am uncertain if it has the scheduling functionality. Worth a try, though.

Alternatively, use the tools already at your disposal: SQL*PLUS, PL/SQL, and cron (or the Windows Scheduler if you run Windows). For a good example of how to write a PL/SQL routine that exports a table to a CSV file, see http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::p11_question_id:88212348059 . If you use a procedure, you can schedule it from Oracle's own job scheduler.

You can do similar things purely with SQL*PLUS as well (without getting into writing a procedure), but it isn't quite as flexible, but for what you want, it might be just what you need. SQL*PLUS is also easily scriptable so that you can then call it whenever you want via cron/Windows Scheduler.

SQL*PLUS works really well on its own to create good fixed-width reports, but it is possible to do HTML and CSV as well. XML will probably require a procedure of some sort, but I'm not an export here, so SQL*PLUS may not be perfect here (it will output to HTML, though, so that might be good enough). If exporting to Excel, remember that the current versions use XML as their file format, which makes things easy (in one way) and painful in other ways (like needing to know beforehand how many rows you're going to have in the output file).

Regardless, with a little bit of work and the combination of two or three tools, you should be able to export your data in any format you wish on any schedule you desire.

Hope that helps.


db.dropDatabase() will drop the database, which will also drop all of the collections within a database.

If you need to see what databases you have, you can do show dbs.

Update: Here's a script to delete everything, make sure you really want to do this:

var dbs = db.getSisterDB('admin').adminCommand("listDatabases").databases
for(d in dbs) db.getSisterDB(dbs[d].name).dropDatabase();


Add a DB2 catalog to a new remote server and make it work

Node directory and database directory are different things. The node directory lists DB2 instances. The database directory lists databases in those instances.

Once you have added a node to the node directory, you will need to catalog a database on that node:

catalog db yourdb at node db03


Dynamically choosing column for WHERE clause

Use DATEPART DAY to retrieve today's day, then build a simple dynamic SQL concatenating that day's value to the column name.

IF OBJECT_ID('tempdb..#PivotedInfo') IS NOT NULL
    DROP TABLE #PivotedInfo

CREATE TABLE #PivotedInfo (
    Day1 INT,
    Day2 INT,
    Day3 INT,
    Day4 INT)

DECLARE @DayToday INT = DATEPART(DAY, GETDATE())

DECLARE @DynamicSQL VARCHAR(MAX) = '
    SELECT
        P.[Day' + CONVERT(VARCHAR(2), @DayToday) + ']
    FROM
        #PivotedInfo AS P '

PRINT (@DynamicSQL)

/*
    SELECT
        P.[Day3]
    FROM
        #PivotedInfo AS P
*/

EXEC (@DynamicSQL)

If you need a general column name as result, you can add an alias:

DECLARE @DynamicSQL VARCHAR(MAX) = '
    SELECT
        DailyValue = P.[Day' + CONVERT(VARCHAR(2), @DayToday) + ']
    FROM
        #PivotedInfo AS P '

PRINT (@DynamicSQL)

/*
    SELECT
        DailyValue = P.[Day3]
    FROM
        #PivotedInfo AS P
*/

You can avoid using dynamic SQL if you unpivot your columns and then filter where the unpivot column name matches your wanted day. It would be more readable but more expensive operation.

INSERT INTO #PivotedInfo (Day1, Day2, Day3, Day4)
VALUES (5, 10, 15, 20)

SELECT
    P.*
FROM
    #PivotedInfo AS I
    UNPIVOT (
        DailyValue FOR Day IN  ([Day1], [Day2], [Day3], [Day4]) -- All day list here
    ) AS P
WHERE
    P.Day = 'Day' + CONVERT(VARCHAR(2), DAY(GETDATE()))


Restoring multiple snapshots with SQL Server 2008

DB snapshots work on the basis of copy-on-write. Read operations of data that is unchanged since the snapshot was taken are fulfilled by pages from the original database. If the data has been changed, the original version of the data is copied to the snapshot so future queries will use pages in the snapshot. When you have multiple snapshots, there is a potentially intricate collection of pages in each snapshot depending on how much write activity took place and how much of that was on the same data. You could end up with many generations of data where each generation resides in one of the snapshots.

This means all snapshots are individually, tightly-coupled with the original database. If you restore the original database using one of the snapshots, that effectively breaks the coupling for all other snapshots hence it isn't allowed.

Third party solutions from storage vendors keep copies of the data around by retaining and tracking blocks that each snapshot is dependent on. That means you could end up consuming storage space many times the size of the original database depending on how many snapshots you have and restores you make. It gets expensive and very complicated quickly.

If you're using multiple snapshots to get different point-in-time data, suggest you consider temporal tables in SQL Server 2016.


I assume you are connected by ssh or you are sitting right at the computer.
Open a separate window and run this query in mysql:

SHOW PROCESSLIST;

I was just doing an import of a 3GB (uncompressed) db the other day and I believe that was the query that showed me exactly where the import was in its progress.

In my case, the tables were being imported in alphabetical order, so I knew exactly where it was in its progress.

Also, check out this stackoverflow question's answers for killing process vs killing query.


mysqldump on slave (without stopping replication)

Performing a mysqldump creates a consistent and logical dump of the data which can be point-in-time recoverable.
There are two ways you can stop replication partway
METHOD #1 : mysqldump option --dump-slave
When you perform a mysqldump with --dump-slave, it will stop only the SQL thread. The I/O thread continues collecting binary log events from the Master. Note how this is explained in the MySQL Documentation:

This option is similar to --master-data except that it is used to dump a replication slave server to produce a dump file that can be used to set up another server as a slave that has the same master as the dumped server. It causes the dump output to include a CHANGE MASTER TO statement that indicates the binary log coordinates (file name and position) of the dumped slave's master. These are the master server coordinates from which the slave should start replicating.
--dump-slave causes the coordinates from the master to be used rather than those of the dumped server, as is done by the --master-data option. In addition, specfiying this option causes the --master-data option to be overridden, if used, and effectively ignored.
The option value is handled the same way as for --master-data (setting no value or 1 causes a CHANGE MASTER TO statement to be written to the dump, setting 2 causes the statement to be written but encased in SQL comments) and has the same effect as --master-data in terms of enabling or disabling other options and in how locking is handled.
This option causes mysqldump to stop the slave SQL thread before the dump and restart it again after.
In conjunction with --dump-slave, the --apply-slave-statements and --include-master-host-port options can also be used.
This option was added in MySQL 5.5.3.

METHOD 2 : Stop the SQL Thread Yourself
Did you know you can manually stop the SQL thread ? According to the MySQL Documentation  on STOP SLAVE;, you can run the following:
mysql> STOP SLAVE SQL_THREAD;

The I/O thread continues receiving binlog events from the Master.
After running STOP SLAVE SQL_THREAD;, you can then run your mysqldump. You could probably do an LVM snapshot or anything else to the data files on the Slave.
When done with your backup, then run
mysql> START SLAVE SQL_THREAD;

or
mysql> START SLAVE;

This will reactivate the SQL thread.
GIVE IT A TRY !!!
Warning: Stopping the SQL thread, which terminates a local DB Connection, could make tables created with CREATE TEMPORARY TABLE disappear. If you are using statement based replication, please do additional research on your App to make sure transactions don't rollback when the SQL thread stops.
WHAT ABOUT --single-transaction ???
You can mysqldump using  --single-transaction ??? without  --dump-slave. However, there are many commands that break running transactions. See the list of those commands in my old post Transactional DDL workflow for MySQL.
UPDATE 2018-02-13 13:07 EST
You just asked me : These backups are for restoring the master DB in case of a storage disaster. Not for slave. So is --dump-slave still valid?
If you use the dump as is (without creating another slave with it and without attempting point-in-time recovery with replay of binary logs), then yes  --dump-slave is OK..
Please keep in mind that if you launch a mysqldump at midnight, that's the dump's point-in-time.
Here is an additional benefit to using  --dump-slave : the mysqldump will include the Master's log file and position between lines 23 and 25. That way, if you wish to create another slave, you have the Master's replication coordinates. If you wish to attempt point-in-time recovery and you can still retrieve the binary logs from the old Master, you can do so.


Most common way to express cardinality in ascii

The UML way (unified modeling language) is to depict the cardinality with <lower bound> .. <upper bound>, being each bound a non-negative integer and * to depict potentially infinite. You can find this on the point 7.5.4 Notation inside the documentation linked before.

However this applies generally to many different modeling diagrams and not exclusively to ERDs. When writing relationships between tables on a relational manner, the most widely used notation I found was N:M (or N to M, N,M). The way you describe the relationships is important too, since there are actually 2 cardinality/multiplicity parts that you need to specify. Check the following examples:

Order with line items. An order must have at least 1 line item and can potentially have infinite. A line item can't exist without an order and only belongs to 1 order. This would be implemented on relational databases with Order and LineItem table, with LineItem having a not null foreign key against Order.

Order to LineItem: 1 to N
LineItem to Order: 1 to 1

Employee with itself (chain of command relationship). A particular employee might have no boss (the top chief) or only 1. A boss can have no employees below, or an infinite amount. This would be implemented on relational databases with one table Employee, which has a foreign key with itself which can be null.

Boss to Employee: 0 to N
Employee to Boss: 0 to 1

Person with itself (father and mother relationships). Every person has one father and one mother. Each parent might not have children, or have many (let's say for example fathers can have infinite amount while mothers can have up to 10). The way to implement this on relational databases is through 2 fields on the same table Person, that links with itself through nullable foreign key (partial enforcement).

Person to father: 1 to 1
Father to person: 0 to N

Person to mother: 1 to 1
Mother to person: 0 to 10

Most of these relationships are usually simplified by just mentioning the upper bound of each side. So for the boss to employee it would be 1 to N and it's read as "a boss can have up to N employees, an employee can have up to 1 boss".

There are some caveats with these expressions on the implementation, depending on each database you use.

On the order example, if an order must have an existing line item when creating the record and a line item must have an existing order, then how can you load them without failing? On relational databases, first you load the Order and then it's line items, so at one point there is an order that doesn't have a line item, but this notation expresses not how it should be implemented but how these entities are related on a business level.

On the parenthood example, at some point there will be no info about a parent, otherwise you would have infinite amount of parenthood levels. So the actual implementation of the top level would be Person to father: 0 to 1.

As for the graphical example, I find the crow's foot notation the simplest to use, but there is no universal standard for this.


Storing linked server in stored procedure variable

You could use dynamic sql to check the existence of databases in linked servers when using parameters.

An example:

DECLARE @SourceHost nvarchar(50) = '[CGENMONOSAMP02]';
DECLARE @SourceDB nvarchar(50) = 'Sample_DB';
--@SourceHost is CGENMONOSAMP02

--@SourceDB is Sample_DB
CREATE TABLE #temp ([name] nvarchar(50));
IF NOT EXISTS (SELECT [name] FROM master.sys.databases WHERE [name] = @SourceDB)
BEGIN
    DECLARE @SQL NVARCHAR(MAX);
    SET @SQL = 'SELECT [name] FROM '+@SourceHost+'.[master].[sys].[databases] WHERE [name] = @SourceDB ';

    INSERT INTO #temp([name])
    EXEC SP_EXECUTESQL @SQL,N'@SourceDB nvarchar(50)',@SourceDB;

    IF NOT EXISTS(SELECT [name] FROM #temp)
    BEGIN
        RAISERROR ('Error: Source Database[%s] is not existing.', 10, 1,@SourceDB);
    END
END

DROP TABLE #temp;


no pg_hba.conf entry for host "[local]", user "pgadmin", database "postgres", SSL off on OSX

First, the meanings of the fields  are:

# local       DATABASE  USER  METHOD  [OPTIONS]
# host        DATABASE  USER  ADDRESS  METHOD  [OPTIONS]
# host[no]ssl DATABASE  USER  ADDRESS  METHOD  [OPTIONS]

The attempt is:

host = “[local]” (meaning through a Unix domain socket)

user = “pgadmin”

database = “postgres”

SSL = off

Now your lines:

local  all  postgres    trust

does not match because the user pgadmin is not postgres

host  all  all  0.0.0.0/0  trust

does not match because host only matches TCP/IP connections and [local] is not a TCP/IP connection.

hostnossl    all          all            0.0.0.0/0  trust

does not match for the same reason than the previous line.

If you want to "trust" everyone locally through the Unix domain socket, you may change the first line to:

local  all  all    trust


Why is the page life expectancy 0 (zero) on SQL Server 2005

I would start with doing the steps outlined here:

How to rebuild the performance counters of a SQL Server 2005 or 2008 instance

Update August 2015: Above link appears broken. Alternative reference:

Troubleshooting: SQL Server (2005, 2008) Performance Counter Collection Problems (CSS)

Essential steps from the linked resource

Using an elevated administrator command prompt perform the following steps.

Change the path to the BINN directory of the SQL Server instance you desire to correct.
(Ex: C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\Binn)
Execute unlodctr <<REGISTERED SERVER NAME>>
For example: unlodctr MSSQL$SQL2008 or SQLAgent$SQL2008 ...
Execute lodctr /T:<<perf-sql* matching the counters you desire to load>>
For example: perf-MSSQL$SQL2008sqlctr.ini or perf-SQLAgent$SQL2008sqlagtctr.ini for SQLAgent. The /T is important to load the SQL Server performance counter provider as a trusted provider.
Cycle the remote registry service:
net stop "Remote Registry" then net start "Remote Registry"
Force a WMI synchronization using winmgmt /resyncperfctr "<<PID>>"
where PID is the process id of the WinPriv.exe (you can get this from Task Manager)

Additionally, from this resource, the following may also be required:

Make sure that the correct security permissions have been granted to the HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Perflib\009
registry key:

Grant Full Control permissions to the Creator Owner account.
Grant Full Control permissions to the Administrators account.
Grant Read permissions to the SQL Administrators on that box/Node.
Grant Full Control permissions to the System account


Composite key as foreign key of multiple tables

It depends on the data model.

If it is possible and correct for a row in table 1 to have a relationship with rows in tables 2 & 3 with different values of a then you must keep the two values separate as you potentially need to different values.

If this should not be possible (i.e. makes no sense for the situation you are modelling) then not only could you use the same column, but you should as keeping two copies of data that should be identical can lead, via bugs or other human error, to consistency errors. The same column can participate in any number of keys and indexes so that will not block you from doing this.

Without more information on what you are modelling, it is not possible to give a more definite answer.


For reasons of accuracy, it is indispensable to know the informational characteristics of the business environment under consideration thoroughly so as to supply a proper database design answer but, in order to answer the main inquiry and the other subquestions, I will work on some points of this post in terms of a several assumptions based on the information you did disclose.

Note: An important omission is that the question does not made known the property or properties whose values uniquely identify the entities of type Video in your particular business domain —provided that (i) Videos.video_id is an additional column set up to contain system-controlled surrogates and (ii) there is one or more conceptual properties whose values uniquely individuate each Video instance, which would entail a natural KEY in the respective logical-level table—.

Of course, supplying a detailed description of the entity types User, Video and Comment would have been of immense assistance in understanding the case at hand in a fitting manner and elaborating a more rounded answer.

  Does adding a surrogate key get rid of an identifying relationship between two entities?

First of all, identifying relationship types, entity types, properties, entities (i.e. instances of an entity type), etc., are aspects that are part of the conceptual level of abstraction of a given database. In order to create a suitable conceptual schema (i.e., one that mirrors the real world faithfully), the database modeler/designer must work very closely with business experts to unveil and define these constructs with high precision, due to the fact that it must be the foundation of the database structure and constraints.

Once the modeler/designer has developed a stable conceptual schema, it has to be represented with exactitude in the respective logical SQL-DDL design. As for your example, you have added an extra column, Comments.comment_id, for system-controlled surrogates to the Comments table, which I assume stands for an entity type called Comment. That kind of column is an artifact that is not part of the significant conceptual schema, you added it and declared it as the PRIMARY KEY of said table, but having proceeded so does not mean that the conceptual schema disappears or that it loses importance.

So, assuming that

(a) the addition —and the declaration as the PRIMARY KEY— of the Comments.comment_id column is actually beneficial;
(b) in the real world, an instance of the Comment entity type is in fact uniquely identified by the values of the combination of the properties VideoId, UserId and CommentDateTime; and
(c) the remaining columns [and constraints] of your Comments table mirror the properties [and business rules] of the Comment entity type correctly…

…then you also have to

(d) declare the combination of (Comments.video_id, Comments.user_id, Comments.comment_dt) as a composite ALTERNATE KEY, by means of one multi-column UNIQUE constraint accompanied by three NOT NULL counterparts.

In this way, you would not be “getting rid of” the identifying relationship type, and one should not get rid of any conceptual representation at the logical level unless the conceptual schema has undergone a modification produced by a real-world informational change.

  Logically, it still seems to be there, so does this really come down to the physical model and whether or not you want to include a composite key?

Conceptually speaking, an identifying relationship type —if and only if discovered and delimited properly— is there, it does not matter if you represented it or not in a logical-level design. The problem with not representing it properly is that (1) the logical design does not reflect (2) the conceptual schema with (3) accuracy.

On the other hand, if a SQL table is carrying the meaning of a certain entity type whose instances are uniquely identified by the values of two or more properties, then the database modeler/designer must declare the applicable multi-column key (be it PRIMARY or ALTERNATE), this is not a matter of wanting or not to include such a constraint. If the multi-column key is not declared, then the rows of the table will eventually become inconsistent with the entity type instances of the real world that they are supposed to represent (a row in a relational table is a fact asserted about a certain entity of an exact type).

In addition, composite keys help, e.g., in:

Ensuring that the connections between the rows values remain consistent with the cardinality ratios that come about between the entity types of significance.
Making the queries meaningful (in contrast with columns enclosing system-controlled surrogate values that are meaningless).
Easing data browsing since they allow, e.g., going from TableA to TableC without passing first through TableC, which speeds up data retrieval.

In the same way, attaching extra columns for system-controlled surrogates should not depend on what the modeler/designer wants or not; to express it differently, it is not a matter of personal preference. Appending this kind of column to a table ought to be well-justified (because, e.g., it will very likely require another supplementary index), and should be decided in terms of the overall characteristics of the database at hand, although, yes, that is a different —yet relevant— topic.

For its part, the physical level of abstraction has to do with indexes, pages, extents, records, partitions, the hard drive itself, other hardware devices, or whatever physical structure the database management system of choice employs to support the logical-level declarations of a database, so the conceptual and logical level definitions must remain independent with respect to physical constructs. The tools of the physical tier serve as well to optimize the performance of a database.

  Are identifying relationships even necessary?

As pointed out above, an identifying relationship type is a conceptual construct, and it comes about in terms of the informational characteristics of the organization of interest. If a certain business environment presents identifying relationship types, then it involves several requirements: (i) to discover them, (ii) to capture them in the applicable conceptual schema and (iii) to convey all their aspects in the respective logical design.

With regards to identifying relationship types —just like with other conceptual factors—, a database modeler/designer should not

(a) invent them;
(b) overlook them;
(c) omit them; or
(d) misrepresent them.

It is the responsibility of a database/modeler designer to (1) find and (2) define each identifying relationship type of interest in order to be able to (3) represent it accurately in the corresponding logical layout. It is carrying out this set of tasks properly what is, in actual fact, necessary.


Transaction log for standalone DB vs AlwaysON availability Group

For a high transaction active DB what is the ratio of log generation if it is a standalone DB and when it is part of SQL Alwayson availability group(synchronous commit).

Assuming Full Recovery mode, which AGs require, 1:1.  AGs don't require any additional logging beyond what a Full Recovery database already does.

In Simple recovery mode you might have some minimally logged operations, but for a "high transaction active" database, it's unlikely.  See eg

SQL Server 2016, Minimal logging and Impact of the Batchsize in bulk load operations


Sql Server Maintenance Plan - Simple Recovery Mode - Adding Shrink Log

The first thing you have to remember is that all transactions are logged to some degree.  There are cases where an action can be minimally logged but there is still some entry in the log.

The recovery modes just change how often the space in the log is freed for re-use.  In all cases the log space is freed up once it is no longer needed.  In the case of SIMPLE recovery there is no backup required for the space to be freed.  However as long as a transaction is active that space won't be freed.  In your particular case you probably have some long running transactions (probably batch jobs) that are holding the space open and causing additional data to be added to the end of the log forcing it to grow.

If the logs are taking more space than you are comfortable with your best bet is going to be to examine any batch processes and try to shorten any transactions they are using.


User acceptance testing (UAT) usually involves clicking around in an application, which usually means making changes and testing them.

This is especially true if you think you're going to "replicate those issues/bugs to our development environment" - it sounds like you're thinking about synchronizing in both directions.

That requires a writable database, and unfortunately, that rules out transaction log shipping, database mirroring, and Always On Availability Groups. All of those scenarios are designed for read-only secondary databases.

If both sides are going to be changing the data, then in theory, you could use SQL Server's replication techniques (merge, peer-to-peer, bidirectional transactional replication) to push data in both directions. In practice, your developers will be making table schema changes to the development database, and that's likely going to break replication.


In the Linux command prompt, I ran this:

sh-4.1# perror 122
OS error code 122:  Disk quota exceeded
MySQL error code 122: Internal (unspecified) error in handler
sh-4.1#

Evidently, the first or next binlog cannot be created

Check your diskspace by running

df -h /var/lib/mysql

Clear up the disk so binlogs can be made


Is there any approach for encrypting mysql column by key?

Have a look at the encryption/decryption functions. For example, using AES (see aes_encrypt):

SET @key_str = SHA2('My secret passphrase',512);
SET @crypt_str = AES_ENCRYPT('text',@key_str);
SELECT AES_DECRYPT(@crypt_str,@key_str);

Which gives, as expected:

+----------------------------------+
| AES_DECRYPT(@crypt_str,@key_str) |
+----------------------------------+
| text                             |
+----------------------------------+
1 row in set (0.00 sec)

So you can store the result from AES_ENCRYPT in the column, and then retrieve it using AES_DECRYPT.

The above code is also compatible with MariaDB (at least 10.2). MySQL additionally supports an init string to the AES_ENCRYPT and AES_DECRYPT functions, see the example at the link to AES_ENCRYPT above.

MySQL also offers InnoDB tablespace encryption, but that will encrypt the whole table, not just individual columns.


I need help with a SQL Query. SQL beginner here!

The columns in common, do not have to have the same name, they just need to have the same data.

  I've done a little research into join clauses, but don't there need to be at least one column in common between the tables? I've been analyzing the output from Queries that show the column names from both tables. There are NO common column names. There is, however, a column in each table that has the same data but different column names.

The easiest way to get started in SSMS (SQL Server Management Studio) is to use the it to get the basics of your query. Then fine tune it, be editing the code, it makes for you.

Go to the table, right click on the table name, select option "Select top 1000 rows. this will create the beginning of your query, and you can edit it until it gets you what you want.  If this is a production system you might want to limit to the top 25 until you get your query working well.

SELECT TOP (25) [ARInvoiceDetails].[ArDetPriceLevelID]
, [ARInvoiceHdr].[ArInvPriceLevelID]
  FROM [ARInvoiceDetails]
  LEFT JOIN [ARInvoiceHdr]
  ON [ARInvoiceDetails].[ArDetPriceLevelID] = [ARInvoiceHdr].[ArInvPriceLevelID]


Updating table basing on composite primary keys from derived table

Use correlated subquery

UPDATE `TableA`
SET `TableA`.`enable` = 1
WHERE EXISTS ( SELECT 1
               FROM `TableB`
               WHERE `TableA`.`code` = `TableB`.`code`
                 AND `TableB`.`loc` = 'foo' )


This isn't directly related to ORDER BY, but be careful using the LIMIT N,M pattern.  This is often used for paginating results, e.g.

... limit 0,10;
... limit 10,10;
... limit 20,10;

Each time through mysql will "select" the first N rows and just return the last M to the client. As you go deeper and deeper you're selecting out more and more of rows, only to be thrown away.

In short each "page" takes longer to load.  If your datasets are small and queries are using indexes well this may seem inconsequential, but the effect will become evident as you grow.  I feel it's generally better to put these constraints in the where clause.  e.g.

... where id between 0 and 10;
... where id between 11 and 20;
... where id between 21 and 30;

Finally, if you're worried about the ordering to the limiting effectively creates the correct range restrictions for you then this concern is completely eliminated to begin with.


Incorrect Syntax Near `=` - SQL Expression

One way to do this , if I understood it right :
WHERE APCCL.data = @coolval
          AND CCMF.Status_ID = 1
          AND APCCL.Status_ID = 1
          AND
          (
             (CLM.myval = 3 and APEL.numval = 1)
           or
             (APEL.numval <> 1 )
          )

or try to split in 2 :
SELECT ...
WHERE APCCL.data = @coolval
          AND CCMF.Status_ID = 1
          AND APCCL.Status_ID = 1
          AND CLM.myval = 3
          AND APEL.numval = 1

UNION  ALL  /* or UNION */

SELECT ...
WHERE APCCL.data = @coolval
          AND CCMF.Status_ID = 1
          AND APCCL.Status_ID = 1
          AND APEL.numval <> 1

Here are some sample testing:
declare @coolVal int = 1;

SELECT *
FROM
(
SELECT 1 as Data, 1 as Status_ID , 3 as myVal, 1 as numVal UNION ALL
SELECT 1 as Data, 1 as Status_ID , 3 as myVal, 2 as numVal UNION ALL
SELECT 1 as Data, 1 as Status_ID , 4 as myVal, 1 as numVal UNION ALL
SELECT 1 as Data, 1 as Status_ID , 4 as myVal, 2 as numVal
)a
WHERE
      data = @coolVal
          AND Status_ID = 1
          AND Status_ID = 1
          AND
    (
        (myval = 3 and numval = 1)
        or
        (numval <> 1)
    )

or with UNION
declare @coolVal int = 1;

SELECT *
FROM
(
SELECT 1 as Data, 1 as Status_ID , 3 as myVal, 1 as numVal UNION ALL
SELECT 1 as Data, 1 as Status_ID , 3 as myVal, 2 as numVal UNION ALL
SELECT 1 as Data, 1 as Status_ID , 4 as myVal, 1 as numVal UNION ALL
SELECT 1 as Data, 1 as Status_ID , 4 as myVal, 2 as numVal
)a
WHERE
          data = @coolVal
          AND Status_ID = 1
          AND Status_ID = 1
          AND myval = 3
          and numval = 1

UNION ALL

SELECT *
FROM
(
SELECT 1 as Data, 1 as Status_ID , 3 as myVal, 1 as numVal UNION ALL
SELECT 1 as Data, 1 as Status_ID , 3 as myVal, 2 as numVal UNION ALL
SELECT 1 as Data, 1 as Status_ID , 4 as myVal, 1 as numVal UNION ALL
SELECT 1 as Data, 1 as Status_ID , 4 as myVal, 2 as numVal
)a
WHERE
          data = @coolVal
          AND Status_ID = 1
          AND Status_ID = 1
          AND numval <> 1

and the result set :
Data    Status_ID   myVal   numVal
1       1          3        1
1       1          3        2
1       1          4        2

dbfiddle
some interesting info here


Does anyone knows how the filestream folder structure works?

I found it.

SELECT
    [o].[name] AS [Table],
    [cp].[name] AS [Column],
    [p].[partition_number] AS [Partition],
    [r].[rsguid] AS [Rowset GUID],
    [rs].[colguid] AS [Column GUID]
FROM sys.sysrowsets [r] CROSS APPLY sys.sysrscols [rs]
JOIN sys.partitions [p]
    ON [rs].[rsid] = [p].[partition_id]
JOIN sys.objects [o]
    ON [o].[object_id] = [p].[object_id]
JOIN sys.syscolpars [cp]
    ON [cp].[colid] = [rs].[rscolid]
WHERE [rs].[colguid] IS NOT NULL
    AND [o].[object_id] = [cp].[id]
    AND [r].[rsguid] IS NOT NULL
    AND [r].[rowsetid] = [rs].[rsid];
GO


I'd suggest you have a think about this system's use cases.  Work out in your own mind how the queries will look for the given design and for your alternative.  Include scenarios where people move between slots.  What do updates look like?  Can business constraints be enforced in DRI?  Can the desired values still be found?  Having actual examples to talk through will highlight your concerns and allow your boss to explain the model more fully.

Writing horror queries against a ghastly schema is only one part of the problem.  Someone has to fix them, at 3am, with no documentation, when they break.  More intelligible queries at this stage will give you a better system, sooner.

If it calms your nerves any, I've had success with EAV systems.  That said, we were a team of very experienced DBAs, it really was a blue-sky, "what if" project and, no, we didn't bet the company on it.  Any dev project that will be heading to production we discourage most strongly from using an EAV approach.

If you go the 'flexible' route do not try to write generic queries just because you have a generic schema.  Each query should only deal with one of Company, Location, Region or whatever your slot types are.

My two cents' worth.


Execute Job With Stored Proc Signed with Certificate

I should have kept researching just a little bit longer.  I found this article and discovered I needed to counter-sign a few more stored procedures within msdb.  Once I did that my user was able to execute the proc, which kicked off the job.

ADD COUNTER SIGNATURE TO sp_start_job BY CERTIFICATE ExecuteRestoreJobsCert
    WITH PASSWORD = 'password'
ADD COUNTER SIGNATURE TO sp_verify_job_identifiers BY CERTIFICATE ExecuteRestoreJobsCert
    WITH PASSWORD = 'password'
ADD COUNTER SIGNATURE TO sp_sqlagent_notify BY CERTIFICATE ExecuteRestoreJobsCert
    WITH PASSWORD = 'password'
go


What is the format of raw geometry column in PostGIS?

It is the hexadecimal rendition (two characters per byte) of the EWKB format.
Per the documentation:

The "canonical forms" of a PostgreSQL type are the representations you get with a simple query (without any function call) and the one which is guaranteed to be accepted with a simple insert, update or copy. For the PostGIS 'geometry' type these are:
- Output
  - binary: EWKB
        ascii: HEXEWKB (EWKB in hex form)
- Input
  - binary: EWKB
        ascii: HEXEWKB|EWKT

For example this statement reads EWKT and returns HEXEWKB in the process of canonical ascii input/output:
=# SELECT 'SRID=4;POINT(0 0)'::geometry;

geometry
----------------------------------------------------
01010000200400000000000000000000000000000000000000
(1 row)


SQL: Select all items and add info from left join where condition is met

SELECT NEWS.news_uid, NEWSREAD.news_read
FROM NEWS
LEFT JOIN NEWSREAD ON NEWS.uid = NEWSREAD.news_uid
                  AND NEWSREAD.user_uid = 'A'


Is it possible to connect to Oracle 9i data source in SSIS?

You can connect to Oracle from SSIS. You have to install Oracle client on your development and also on your production environments. After doing proper tnsnames configurations you can use odbc source or ado.net source.


execute() vs executeQuery() in MySQL Connector C++

Each one of the execute calls is optimized in a different way depending based on their precise usage - for instance the executeUpdate method is optimized the way it is because it expects you to not return a result set from your call. You should choose the one that best suits your needs. The below is from the Connector C++ reference:

  If you don't know ahead of time whether the SQL statement will be a SELECT or an INSERT, UPDATE, or DELETE, use the execute method. execute() returns true if the SQL query was a SELECT, and returns false if the statement was an INSERT, UPDATE, or DELETE. If the statement was a SELECT query, you can retrieve the results by calling the getResultSet method on the Statement instance. If the statement was an INSERT, UPDATE, or DELETE statement, you can retrieve the count of affected rows by calling getUpdateCount().

The article I mentioned can be found here:

http://dev.mysql.com/tech-resources/articles/mysql-connector-cpp.html

I hope this helps you.


MySQL is my area of expertise and SQL Server isn't... but it looks like the answer is you definitely can manage the migration with SSMA on a client PC, but it also appears that both your client machine and the SQL Server need connectivity to the MySQL server.  Reading between the lines, it sounds as if the client machine might use its connection to MySQL to discover the schemata and then something from the server-side components installed on the SQL Server would subsequently connect directly to MySQL to fetch the actual data rather than the data being actually piped through the client machine where you're using SSMA.

To use SSMA, you first must install the SSMA client program on a computer that can access both the source MySQL database and the target instance of SQL Server or SQL Azure. Then, install the MySQL providers (MySQL ODBC 5.1 Driver (trusted)) on the computer that is running SSMA Client Program.
— http://technet.microsoft.com/en-us/library/hh313129.aspx
In addition to installing SSMA, you must also install components on the computer that is running SQL Server. These components include the SSMA extension pack, which supports data migration, and MySQL providers to enable server-to-server connectivity.
— http://technet.microsoft.com/en-us/library/hh313121.aspx


There are a number of ...timeout... parameters that can be configured for a MySQL instance. Here are a few selected parameters (Ver. 8.x) for your convenience:

Reference: 5.1.7 Server System Variables (MySQL | Documentation)

interactive_timeout

  The number of seconds the server waits for activity on an interactive connection before closing it. An interactive client is defined as a client that uses the CLIENT_INTERACTIVE option to mysql_real_connect(). See also wait_timeout.

wait_timeout

  The number of seconds the server waits for activity on a noninteractive connection before closing it.

  On thread startup, the session wait_timeout value is initialized from the global wait_timeout value or from the global interactive_timeout value, depending on the type of client (as defined by the CLIENT_INTERACTIVE connect option to mysql_real_connect()). See also interactive_timeout.

Answering Your Question

  Is there a way to configure the timeout for inactive clients with mysql?

It depends. Globally you can define a timeout with the wait_timeout parameter of your MySQL instance. If you are using the specific option CLIENT_INTERACTIVE of the mysql_real_connect() function, then you can use the interactive_timeout parameter.


Log shipping and log file: Can the log on the master or slave every be truncated/shrunk?

The log file on the replica follows exactly the file on the master. They can't be different. You can shrink the master and the replica will automatically change to match.


sp_BlitzFirst fails when outputting values to tables

When we renamed it sp_BlitzFirst and went open source, we made several breaking changes, one of which was changing the history table's DATETIME field to be DATETIMEOFFSET instead:

https://github.com/BrentOzarULTD/SQL-Server-First-Responder-Kit/issues/288

I would use a new history table name, and then it'll get automatically created with the right datatype.


Relating ExecutionInstanceGUID to the SSISDB

I created a SSIS project using the 2012 Deployment Model consisting of a single package. In that package, I added an OLE DB Connection Manager, pointed it to tempdb and dropped a Script Task onto the canvas. I also turned on explicit logging using that OLE DB Connection Manager and captured the OnInformation event.

SCR Fire info

I configured my script task to grab two parameters: System::ExecutionInstanceGUID and System::ServerExecutionID I will admit at this point, I had not noticed the second variable until Marian's answer. Inside the task, I raise 2 Information events so I can get the values recorded. This should be logged to both the explicit table (dbo.sysssislog) and the "free" logging (catalog.operation_messages).

    public void Main()
    {
        bool fireAgain = true;
        string description = string.Empty;
        string variable = string.Empty;
        string value = string.Empty;

        variable = "System::ServerExecutionID";
        value = Dts.Variables[variable].Value.ToString();
        description = string.Format("{0}: {1}", variable, value);
        Dts.Events.FireInformation(0, "Reporting", description, string.Empty, 0, ref fireAgain);

        variable = "System::ExecutionInstanceGUID";
        value = Dts.Variables[variable].Value.ToString();
        description = string.Format("{0}: {1}", variable, value);
        Dts.Events.FireInformation(0, "Reporting", description, string.Empty, 0, ref fireAgain);

        Dts.TaskResult = (int)ScriptResults.Success;
    }

Deploy and execute

I then deployed my project to a server and executed it.

I opened the Operations Report and and clicked into the SCR Fire info task details.

The red circled item shows that we are viewing details for Operation 8, as expected. The highlighted lines are the OnInformation events which bubbled up the values of those two system variables. Also as expected, the value of System::ServerExecutionID matched what was in the report. The value of System::ExecutionInstanceGUID was meaningless as always but it was present {3F515780-8062-40AA-B9EC-C320CBAC5EFD}.

Tying it all together

I now had a two different logs I wanted to tie together.

sysssislog query

Running this query pulled back relevant rows from the old-school logging table.

SELECT
    L.event
,   L.source
,   L.message
FROM
    dbo.sysssislog AS L
WHERE
    L.executionid = '{3F515780-8062-40AA-B9EC-C320CBAC5EFD}'
ORDER BY
    L.id ASC;

The results looked like

event   source  message
PackageStart    ParameterTest   Beginning of package execution.

OnInformation   SCR Fire info   System::ServerExecutionID: 8
OnInformation   ParameterTest   System::ServerExecutionID: 8
OnInformation   SCR Fire info   System::ExecutionInstanceGUID: {3F515780-8062-40AA-B9EC-C320CBAC5EFD}
OnInformation   ParameterTest   System::ExecutionInstanceGUID: {3F515780-8062-40AA-B9EC-C320CBAC5EFD}
PackageEnd  ParameterTest   End of package execution.

catalog.operation_messages query

Running this query against the SSISDB catalog showed all the messages that were in the above report and also confirmed I could link the value in message to operation_id as well as over to dbo.sysssislog.executionid

SELECT
    OM.*
FROM
    catalog.operation_messages AS OM
WHERE
    OM.operation_id = 8;

Those results were

operation_message_id    operation_id    message_time    message_type    message_source_type message extended_info_id
30  8   2013-04-02 21:02:34.1418917 -05:00  10  30  ParameterTest:Validation has started.   NULL
31  8   2013-04-02 21:02:34.1738922 -05:00  10  40  SCR Fire info:Validation has started.   NULL
32  8   2013-04-02 21:02:34.1768872 -05:00  20  40  SCR Fire info:Validation is complete.   NULL
33  8   2013-04-02 21:02:34.1788903 -05:00  20  30  ParameterTest:Validation is complete.   NULL
34  8   2013-04-02 21:02:34.3349188 -05:00  30  30  ParameterTest:Start, 9:02:34 PM.    NULL
35  8   2013-04-02 21:02:34.4009253 -05:00  30  40  SCR Fire info:Start, 9:02:34 PM.    NULL
36  8   2013-04-02 21:02:34.4009253 -05:00  10  40  SCR Fire info:Validation has started.   NULL
37  8   2013-04-02 21:02:34.4019251 -05:00  20  40  SCR Fire info:Validation is complete.   NULL
38  8   2013-04-02 21:02:34.4219283 -05:00  70  40  SCR Fire info:Information: System::ServerExecutionID: 8 NULL
39  8   2013-04-02 21:02:34.4259295 -05:00  70  40  SCR Fire info:Information: System::ExecutionInstanceGUID: {3F515780-8062-40AA-B9EC-C320CBAC5EFD}    NULL
40  8   2013-04-02 21:02:34.4409316 -05:00  40  40  SCR Fire info:Finished, 9:02:34 PM, Elapsed time: 00:00:00.031. NULL
41  8   2013-04-02 21:02:34.4419388 -05:00  40  30  ParameterTest:Finished, 9:02:34 PM, Elapsed time: 00:00:00.125. NULL

Wrap up

When the package is executed outside of the context of the SSISDB catalog (aka via SSDT-BI or the command line to an .ispac) the value of the the System::ServerExecutionID will be 0. It makes sense, but future readers either use a LEFT OUTER JOIN when linking sysssislog to catalog.operation_messages if you want to catch all executions of the package.

Hat tip, my hearty thanks and the answer credit go to Marian for putting me on the correct track. Given the choice between storing a GUID (16 bytes) and a bigint (8 bytes) in my summarized logging table, it's a no brainer for me: monotonically increasing big integer please.


specify delimiter in openrowset bulk insert

but if one of the fields in the csv files contains a comma( , )

If all lines contains a comma, the solution can be

<ROW>
  <COLUMN SOURCE="1" NAME="username" xsi:type="SQLSMALLINT"/>
  <COLUMN SOURCE="2" NAME="name1" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="3" NAME="name2" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="4" NAME="IP" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="5" NAME="title" xsi:type="SQLNVARCHAR"/>
</ROW>

INSERT INTO [player table]
SELECT 20190331,
       username,
       name1+','+name2,
       IP,
       title
FROM  OPENROWSET( BULK 'D:\folder\2019\03-31\Player_statistics.csv',
                  FIRSTROW = 2,
                  FORMATFILE='D:\folder\test\xml\Player_statistics.xml') t1

If only some lines contains commas, and none title is null/empty, then try

INSERT INTO [player table]
SELECT 20190331,
       `username`,
       CASE WHEN title IS NULL
            THEN name1
            ELSE name1+','+name2 END,
       CASE WHEN title IS NULL
            THEN name2
            ELSE IP END,
       CASE WHEN title IS NULL
            THEN IP
            ELSE title END
       IP,
       title
FROM  OPENROWSET( BULK 'D:\folder\2019\03-31\Player_statistics.csv',
                  FIRSTROW = 2,
                  FORMATFILE='D:\folder\test\xml\Player_statistics.xml') t1

If some title is null/empty, I cannot find simple way to distinguish.

UPDATE

The solution can be: divide your CSV on two fields only - username and the whole slack, the slack then can be divided to separate fields using common string functions in the SELECT part of importing query (maybe with using intermediate variables in chain calculations).


It looks like based on your error that the user you are trying to create a login for already has a login in that database with another name, so you are effectively "double mapping" them.

If you have access to SSMS you can see what their mapping is by using the Object Explorer and navigating the following path:
Security -> Logins -> Double click on the username

This should pop up a GUI, you can see the "User Mapping" in the top left hand corner, this should show you to whom the user is mapped.