Determining whether column in view can only contain unique values I have a SQL Server 2008 database with several views. I'm looking for a way to definitively determine whether a particular view has a column that can only contain unique values (i.e. can be used as a primary key).

Some of these views have simple definitions, but some are also quite complex, so I find that manually analysing each of these views would be very time-consuming.

As well, some of these views have columns that concatenate values from other columns in the view in order to be "unique", but I can't be certain whether these values are actually unique because in some cases I have found that they are not.

One simple approach I had was to use the following query to determine if any duplicate values exist for a particular column in a view:

SELECT   column_name
FROM     view_name
GROUP BY column_name
HAVING   COUNT(column_name) > 1

The above query works fine when there is enough data in the view to catch any non-unique values, but this still isn't definitive.

Is there a way to have SQL Server perform this analysis to determine whether a column in a view can only contain unique values?

Short answer:  you'll have to do your own dirty work.  Like @mrdenny says, there is no way of automating this task.

Very long answer:  SQL Server doesn't have an easy way of determining unique columns in a view, in part because of how complex (and dynamic) views can be.  There are two methods to make a first approximation, however, and these methods can work in concert.  The first is what you already have:  performing queries against the view to see if you can find anything which already violates uniqueness constraints.  If it does, you can throw that column (or set of columns) out immediately.  If it doesn't, that combination might be a valid unique key, but there is no guarantee.

The second method is to reason from the data model, starting with unique constraints (assuming you have those on your tables!).  There are three nice ways that you can have enforced uniqueness on a table:  a primary key, a unique key constraint, or a unique index.  All three of them show up in the sys.indexes system table and have the is_unique property set to 1.  There are also some not-so-nice ways like using triggers to enforce uniqueness.

You could try messing around with sys.dm_sql_referenced_entities and a query like this might be helpful to give you a starting point:

declare @ViewName sysname = 'MYVIEW';

with viewcolumns as
(
    select * from sys.dm_sql_referenced_entities(@ViewName, 'OBJECT') dsre /* where is_selected = 1   --uncomment if using SQL 2012 */
),
uniquereferences as
(
    select
        i.object_id,
        object_schema_name(i.object_id) as SchemaName,
        object_name(i.object_id) as TableName,
        i.name as IndexName,
        c.name as ColumnName,
        case when vc.referenced_entity_name IS NOT NULL then 1 else 0 end as HasReference
    from
        sys.indexes i
        inner join sys.index_columns ic
            on i.index_id = ic.index_id
            and i.object_id = ic.object_id
        inner join sys.columns c
            on c.column_id = ic.column_id
            and c.object_id = ic.object_id
        left outer join viewcolumns vc
            on vc.referenced_id = c.object_id
            and vc.referenced_minor_id = c.column_id
    where
        i.is_unique = 1
),
sufficientreferences as
(
    select
        object_id,
        IndexName,
        min(HasReference) as HasReference
    from
        uniquereferences
    group by
        object_id,
        IndexName
    having
        min(HasReference) = 1
)
select
    ur.object_id,
    ur.SchemaName,
    ur.TableName,
    ur.IndexName,
    ur.ColumnName
from
    uniquereferences ur
    inner join sufficientreferences sr
        on ur.IndexName = sr.IndexName
        and ur.object_id = sr.object_id
where
    ur.HasReference = 1
    /* Optional:  remove referenced tables; if you have a 1:1 reference, leave this bit out */
    and not exists
    (
        select
            *
        from
            sys.foreign_keys fk
            inner join viewcolumns vc
                on fk.parent_object_id = vc.referenced_id
        where
            vc.referenced_minor_id = 0
            and fk.referenced_object_id = ur.object_id

    );

For SQL 2008, the is_selected flag is not available, so there's no way to tell if the column returned by that function is actually part of the SELECT clause or if it is is used in a join or filter.  With SQL 2012, you could at least limit your query to the columns that actually are part of the SELECT clause.

What you get from this is not a set of unique keys for the view.  What you get is a set of columns which make up unique keys on their underlying tables.  The difference is that you could have a reference table with a unique key constraint on the Name column, and that Name column would show up in the above query even if the view joins the reference table to the base table (thereby causing repeated use of the reference table's Name column).  To help alleviate that, I have a NOT EXISTS clause which removes cases in which the object is the referenced table in a foreign key relationship with another table in the view, so our unique index for the reference data table should not show up.

What this does allow you to do is reduce your possible answer space.  But even then, you'll be doing a lot of spadework.  The more complex your views get, the less valuable this is.  For example, if you have a UNION ALL in your query, the statement above might show you a candidate column set which is wrong, because those columns might be duplicated in the other half of the UNION ALL.  Or if you have cross-server queries, sys.dm_sql_referenced_entities might not even show you any column names.  In other words, the query above is a semi-functional aid and certainly not a method of automating the process.

Note that this does depend upon having unique constraints specified.  If your only unique keys are surrogate primary keys, it might not be quite as easy to find a candidate column set because not even SQL Server knows that the column combination is supposed to be unique.


mysql 8 high availability with drbd, corosync and pacemaker I would like to implement High Availability for MySQL8, is this version supported by drbd, heartbeat, corosync, pacemaker? I've looked around online to that regard but there isn't much information about MySQL8. What is the ideal HA cluster for MySQL8?

Those days I won't recommend you to use these technique to MySQL HA. I love pacemaker but it's not suitable for datbases are those are much more complex.

I would recommend you to use MySQL InnoDB Cluster that handles everything, from orchestration to routing. It provides you everything you need built-in, split-brain prevention, quorum, data consistency, automatic data provisioning, ...

Take a look at :

https://www.slideshare.net/Grypyrg/mysql-innodb-cluster-new-features-in-80-releases-best-practices
https://www.slideshare.net/Grypyrg/mysql-database-architectures-innodb-replicaset-cluster-220070786
https://www.youtube.com/watch?v=m7pFwxiglHc

Regards,


Split .mdf datafile methods We have a database with 1,1TB .mdf file in SQL Server 2014. I want to split it into two datafiles (add new .ndf file). As I know, there are two methods:

To rebuild big clustered indexes and then shrink the .mdf file.
Add new .ndf file and run a DBCC SHRINKFILE ('filename', EMPTYFILE). After this, new values will be replaced in both datafiles.

Which method is safer, faster, better? We will do first on TEST and then in PRODUCTION environment.

And maybe there is another way to split datafile?

I did this on an instance that had 20+ databases, some went fairly quick (minutes/hours).  One was heavy OLPT and I ended up using a job that ran off hours to move, it took about 6 months.

I have tried, file balancing with index rebuilds, it is not as effective as you might hope.

If you just add a new .ndf for the most part only new data will go there, and you may have decreased performance compared to well balanced datafiles.

If you have a TB of data, you probably want more then one .ndf file.  There are recommendations based on CPU.  If you have 8 CPU you should have 8 datafiles (one .mdf & 7 .ndf)

It is not possible to move all the data off of the .mdf, there is some that must stay there.  Also optimally you want to balance files evenly across all your datafiles.  Best course IMHO is to move everything you can to a single temp.ndf.  Then create your 7 new .ndf files mydatabase_2.ndf, etc and move everything back to the 8 datafiles.  This will give you a nicely balanced set of datafiles.  It is close to what would have grown naturally if you had all 8 datafiles at the start.

Disk space may be an issue.  If you don't have 2TB of room you can't have a mydatabase.mdf and a temp.ndf that are both 1TB.  You may have to stop the data movement occasionally so you can trim newly freed space.

Create temp.ndf give it a decent autogrow size, probably 500GB (if you have room, make it 1TB from the start)
Move data off DBCC SHRINKFILE (N'mydatabase' , EMPTYFILE)
Trim free space DBCC SHRINKFILE (N'mydatabase' , 1)
When the original .mdf is as small as it will get (about 10MB) create your new files mydatabase_2.ndf, mydatabase_3.ndf, etc
Shrink back DBCC SHRINKFILE (N'temp', EMPTYFILE), watch for blocking and space, trim as needed.

While doing the move you will want to watch for blocking and dataspace issues.  This is the code I use, run it every few minutes while I am watching.

-- Two queires to provide and idea of how things are going durring a file shrink empty modification on the specific database

-- Look for locks and waits that may be impeding the move in specific database.
-- !!!!!Be sure to put your database name in the where clause!!!!!!-----------------
Select session_ID
, Start_time
, [Status]
, command
, user_id
, blocking_session_id as 'blocking ID'
, wait_type
, wait_time
--, estimated_completion_time  as 'est comp time'--Values can fluctuate wildly, When it is smaller the cpu_time and decreasing can suddendly finish.
, cpu_time
, percent_complete as '%conmplete'
, lock_timeout
, deadlock_priority
, last_wait_type
, SDB.name as 'DB_name'
, SDB.state_desc as 'DB_Status'
--, *

From sys.dm_exec_requests

left join sys.databases as SDB
on sys.dm_exec_requests.database_id = SDB.database_id

where [status] not in ('background','sleeping')
and SDB.name = 'DB_name'-- The database I am working on ---------<<<<<<<<<<<<<<< Change this value------------

--Identifies used space on files, how much data has moved.
select file_id
, type_desc
, name
, substring([physical_name],1,3) AS [Drive]
, physical_name
, state_desc
, size / 128 as 'AllocatedSizeMB'
, FILEPROPERTY([name],'SpaceUsed') /128 AS 'SpaceUsedMB'  --Addapted from https://sqlperformance.com/2014/12/io-subsystem/proactive-sql-server-health-checks-1
, (1- (FILEPROPERTY([name],'SpaceUsed') / CAST (size AS MONEY))) *100 AS 'PercentFree'
, growth / 128 as 'GrowthSettingMB'

 from sys.database_files
 order by type_desc Desc, name


Relationship between two tables so their value changes together First of all I'm not sure if this website is for purely experts but sorry if it is.. the main page was a little vague

I was wondering if it'd be possible to have a relationship, for example, in my case it would be Customer to saidProduct..

If Customer has a field 'saidProduct' and the table 'saidProduct' has a field 'active'

(I have no knowledge of stored procedures but if this is how it can be done I will figure out how.. that's the only guess I have though)

Can I make it so that if I update Customer.saidProduct = true (was false) so that saidProduct.active = true (was false) as well?

Not sure if it's relevant but I want to simplify my code in VS but I figured asking dba was a better idea

You could create a trigger on both tables to update the other table when changes are made to the data.

I've create a sample schema to test this, and to show results:

USE tempdb;

GO
CREATE TABLE dbo.Products
(
    ProductID INT NOT NULL CONSTRAINT PK_Products PRIMARY KEY CLUSTERED IDENTITY(1,1)
    , Active BIT NOT NULL CONSTRAINT DF_Products_Active DEFAULT ((0))
);
GO
INSERT INTO dbo.Products(Active)
VALUES (0);
GO
CREATE TABLE dbo.Customers
(
    CustomerID INT NOT NULL CONSTRAINT PK_Customers PRIMARY KEY CLUSTERED IDENTITY(1,1)
    , ProductID INT NOT NULL CONSTRAINT FK_Customers_ProductID FOREIGN KEY REFERENCES dbo.Products(ProductID)
    , SaidProduct BIT NOT NULL CONSTRAINT DF_Customers_SaidProduct DEFAULT ((0))
);
GO
CREATE TRIGGER dbo.CustomerTrigger ON dbo.Customers
AFTER INSERT, UPDATE
AS
BEGIN
    UPDATE dbo.Products
    SET Active = SaidProduct
    FROM dbo.Products p
        INNER JOIN inserted i ON i.ProductID = p.ProductID;
END
GO
INSERT INTO dbo.Customers(ProductID, SaidProduct)
VALUES (1, 1);
GO
SELECT *
FROM dbo.Products;

This inserts a row into the Products table.  The product is inserted with an Active status of False. A row is then inserted into the Customers table, where SaidProduct is set to True, which triggers an update on the Products table.  The result is the row in the Products table is now set to Active = True.

Trigger recursion may be an issue if you create a trigger on both tables, such that updating the Customers table would update the Products table, which might then try to update the Customers table, thereby creating a loop.  There are several ways to avoid recursion, but that is slightly beyond the scope of your question.


Why don't databases create their own indexes automatically? I would have thought that databases would know enough about what they encounter often and be able to respond to the demands they're placed under that they could decide to add indexes to highly requested data.

Update

This is now implemented in SQL Server Azure. It generates recommendations

and index management can be configured to be automatic.

  Enable automatic index management

  You can set the SQL Database Advisor to implement recommendations
  automatically. As recommendations become available they will
  automatically be applied. As with all index operations managed by the
  service if the performance impact is negative the recommendation will
  be reverted.

Original Answer

Some databases do already (kind of) create indexes automatically.

In SQL Server the execution plan can sometimes include an Index Spool operator where the RDBMS dynamically creates an indexed copy of the data. However this spool is not a persistent part of the database kept in synch with the source data and it cannot be shared between query executions, meaning execution of such plans may end up repeatedly creating and dropping temporary indexes on the same data.

Perhaps in the future RDBMSs will have the capacity to dynamically drop and create persistent indexes according to workload.

The process of index optimisation is in the end just a cost benefit analysis. Whilst it is true that humans may have more information about relative importance of queries in a workload in principle there is no reason why this information could not be made available to the optimiser. SQL Server already has a resource governor that allows sessions to be classified into different workload groups with different resource allocations according to priority.

The missing index DMVs mentioned by Kenneth are not intended to be implemented blindly as they only consider the benefits to a specific query and make no attempt to take account of the cost of the potential index to other queries. Nor does it consolidate similar missing indexes. e.g. the output of this DMV may report missing indexes on A,B,C and A,B INCLUDE(C)

Some current issues with the idea are

The quality of any automated analysis that does not actually create the index will be highly dependant upon the accuracy of the costing model.
Even within the field of automated analysis an offline solution will be able to be more thorough than an online solution as it is imperative that an online solution should not add large book keeping overhead to the live server and interfere with its primary purpose of executing queries.
The indexes created automatically in response to workload will necessarily be created in response to  queries that would have found them useful so will lag behind solutions that create the indexes in advance.

It is probably reasonable to expect the accuracy of costing models to improve over time but point 2 looks trickier to solve and point 3 is inherently insoluble.

Nevertheless probably the vast majority of installs are not in this idealised situation with skilled staff who continuously monitor, diagnose, and anticipate (or at least react to)  changes in workloads.

The AutoAdmin project at Microsoft Research has been running since 1996

  The goal of this project is to make databases self-tuning and
  self-administering by exploiting knowledge of the workload

The project home page lists several intriguing projects. One is particularly relevant to the question here

  Another interesting problem arises when there is no DBA available
  (e.g. an embedded database or a small business). In such scenarios, a
  low touch continuous index tuning approach may become important. We
  have explored solutions ...[in]  “An Online Approach to Physical Design Tuning” in ICDE
  2007.

The authors state

  With increasingly common DBMS features like online indexes , it is
  appealing to explore more automatic solutions to the physical design
  problem that advance the state of the art.

The paper introduces an algorithm

  Its main characteristics are:

  As queries are optimized, we identify a relevant set of candidate indexes that would improve performance. This feature allows query
  processing to continue in parallel with indexes that are built in the
  background.
  At execution time, we track the potential beneﬁts that we
  lose by not having such candidate indexes and also the utility of
  existing indexes in the presence of queries, updates, and space
  constraints.
  After we gather enough “evidence” that a physical design change is beneﬁcial, we automatically trigger index creations or deletions.
  The online nature of our problem implies that we will generally lag behind optimal solutions that know the future. However, by carefully
  measuring evidence, we ensure that we do not suffer from “late”
  decisions signiﬁcantly, thus bounding the amount of incurred loss

The implementation of the algorithm allows for throttling in response to changes in server load and also can abort index creation if during creation the workload changes and expected benefit falls below the point that it is deemed worthwhile.

The conclusion of the authors on the topic of Online versus traditional physical tuning.

  The online algorithms in
  this work are useful when DBAs are uncertain about the future behavior
  of the workload, or have no possibility of doing a comprehensive
  analysis or modelling. If a DBA has full information about the
  workload characteristics, then a static analysis and deployment by
  existing tools (e.g., [2, 3]) would be a better alternative.

The conclusions here are similar to those in another paper Autonomous Query-driven Index Tuning

  Our approach cannot beat the index advisor if the whole workload is
  known in advance. However, in dynamic environments with evolving and
  changing workloads the query-driven approach produces better results.


Optimize Subquery with Windowing Function As my performance tuning skills never seem to feel sufficient, I always wonder if there is more optimization I can perform against some queries.  The situation that this question pertains to is a Windowed MAX function nested within a subquery.

The data that I'm digging through is a series of transactions on various groups of larger sets.  I've got 4 fields of importance, the unique ID of a transaction, the Group ID of a batch of transactions, and dates associated with the respective unique transaction or group of transactions.  Most times the Group Date matches the Maximum Unique Transaction Date for a Batch, but there are times where manual adjustments come through our system and a unique date operation occurs after the group transaction date is captured.  This manual edit doesn't adjust the group date by design.

What I identify in this query are those records where the Unique Date falls after the Group Date.  The following sample query builds out a rough equivalent of the my scenario and the SELECT statement returns the records I'm looking for, however, am I approaching this solution in the most efficient manner?  This takes a while to run during my fact table loads as my record counts number in the upper 9 digits, but mostly my disdain for subqueries makes me wonder if there's a better approach here.  I'm not as concerned about any indexes as I'm confident those are already in place; what I'm looking for is an alternative query approach that will achieve the same thing, but even more efficiently.  Any feedback is welcome.

CREATE TABLE #Example
(
    UniqueID INT IDENTITY(1,1)
  , GroupID INT
  , GroupDate DATETIME
  , UniqueDate DATETIME
)

CREATE CLUSTERED INDEX [CX_1] ON [#Example]
(
    [UniqueID] ASC
)

SET NOCOUNT ON

--Populate some test data
DECLARE @i INT = 0, @j INT = 5, @UniqueDate DATETIME, @GroupDate DATETIME

WHILE @i < 10000
BEGIN

    IF((@i + @j)%173 = 0)
    BEGIN
        SET @UniqueDate = GETDATE()+@i+5
    END
    ELSE
    BEGIN
        SET @UniqueDate = GETDATE()+@i
    END

    SET @GroupDate = GETDATE()+(@j-1)

    INSERT INTO #Example (GroupID, GroupDate, UniqueDate)
    VALUES (@j, @GroupDate, @UniqueDate)

    SET @i = @i + 1

    IF (@i % 5 = 0)
    BEGIN
        SET @j = @j+5
    END
END
SET NOCOUNT OFF

CREATE NONCLUSTERED INDEX [IX_2_4_3] ON [#Example]
(
    [GroupID] ASC,
    [UniqueDate] ASC,
    [GroupDate] ASC
)
INCLUDE ([UniqueID])

-- Identify any UniqueDates that are greater than the GroupDate within their GroupID
SELECT UniqueID
     , GroupID
     , GroupDate
     , UniqueDate
FROM (
    SELECT UniqueID
         , GroupID
         , GroupDate
         , UniqueDate
         , MAX(UniqueDate) OVER (PARTITION BY GroupID) AS maxUniqueDate
    FROM #Example
    ) calc_maxUD
WHERE maxUniqueDate > GroupDate
    AND maxUniqueDate = UniqueDate

DROP TABLE #Example

dbfiddle here

When and if you are able to upgrade from SQL Server 2012 to SQL Server 2016, you may be able to take advantage of the much improved performance (especially for frameless window aggregates) provided by the new batch mode Window Aggregate operator.

Almost all large data processing scenarios work better with columnstore storage than rowstore. Even without changing to columnstore for your base tables, you can still gain the benefits of the new 2016 operator and batch mode execution by creating an empty nonclustered columnstore filtered index on one of the base tables, or by redundantly outer joining to a columnstore-organized table.

Using the second option, the query becomes:

-- Just to get batch mode processing and the window aggregate operator
CREATE TABLE #Dummy (a integer NOT NULL, INDEX DummyCC CLUSTERED COLUMNSTORE);

-- Identify any UniqueDates that are greater than the GroupDate within their GroupID
SELECT
    calc_maxUD.UniqueID,
    calc_maxUD.GroupID,
    calc_maxUD.GroupDate,
    calc_maxUD.UniqueDate
FROM
(
    SELECT
        E.UniqueID,
        E.GroupID,
        E.GroupDate,
        E.UniqueDate,
        maxUniqueDate = MAX(UniqueDate) OVER (
            PARTITION BY GroupID)
    FROM #Example AS E
    LEFT JOIN #Dummy AS D -- The only change to the original query
        ON 1 = 0
) AS calc_maxUD
WHERE
    calc_maxUD.maxUniqueDate > calc_maxUD.GroupDate
    AND calc_maxUD.maxUniqueDate = calc_maxUD.UniqueDate;

db<>fiddle

Note the only change to the original query is creating an empty temporary table and adding the left join. The execution plan is:

(58 row(s) affected)
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0
Table '#Example'. Scan count 1, logical reads 40, physical reads 0, read-ahead reads 0

For more information and options, see Itzik Ben-Gan's excellent series, What You Need to Know about the Batch Mode Window Aggregate Operator in SQL Server 2016 (in three parts).


Mongo replication sync to newer version I got one mongo v2.4.10 server with 1.7TB data, I am trying to migrate and upgrade the mongo to mongo v.3.0.15 server

I've setup a new mongo v.3.0.15 and configured replication for v3.0.15 to be secondary to sync with v.2.4.10 primary mongo.

The secondary was in STARTUP2 and the sync was almost finish as I can check with the growth of my storage device for the new machine which running mongo v.3.0.15

However there were some socket exceptions which caused both of my machine to resyn again from the start, just to ask anything I can configure or setup to prevent the error to happen again because I don't want to waste another 7 days to fail to sync up 1.7TB again.

Below are some logs from my mongo:

Primary mongo (v2.4.10):

Wed Jul 3 10:03:59.196 [conn21] SocketException handling request, closing client connection: 9001 socket exception [SEND_ERROR] server [x.x.x.x:32829]

Secondary mongo (v.3.0.15)

...
2019-07-03T09:54:29.169+0800 I NETWORK [ReplExecNetThread-0] Socket recv() timeout 192.168.168.122:27017
2019-07-03T09:54:29.169+0800 I NETWORK [ReplExecNetThread-0] SocketException: remote: 192.168.168.122:27017 error: 9001 socket exception [RECV_TIMEOUT] server [192.168.168.122:27017]
2019-07-03T09:54:29.169+0800 I NETWORK [ReplExecNetThread-0] DBClientCursor::init call() failed
2019-07-03T09:54:29.169+0800 I REPL [ReplicationExecutor] Error in heartbeat request to 192.168.168.122:27017; Location10276 DBClientBase::findN: transport error: 192.168.168.122:27017 ns: admin.$cmd query: { replSetHeartbeat: "ArchiverReplica", pv: 1, v: 1, from: "x.x.x.x:27017", fromId: 1, checkEmpty: false }

2019-07-03T09:54:29.170+0800 W NETWORK [ReplExecNetThread-0] Failed to connect to 192.168.168.122:27017 after 1 milliseconds, giving up.
2019-07-03T09:54:29.170+0800 I REPL [ReplicationExecutor] Error in heartbeat request to 192.168.168.122:27017; Location18915 Failed attempt to connect to 192.168.168.122:27017; couldn't connect to server 192.168.168.122:27017 (192.168.168.122), connection attempt failed
...
2019-07-03T10:07:41.452+0800 W NETWORK [ReplExecNetThread-0] Failed to connect to 192.168.168.122:27017 after 4995 milliseconds, giving up.
2019-07-03T10:07:41.452+0800 I REPL [ReplicationExecutor] Error in heartbeat request to 192.168.168.122:27017; Location18915 Failed attempt to connect to 192.168.168.122:27017; couldn't connect to server 192.168.168.122:27017 (192.168.168.122), connection attempt failed
2019-07-03T10:07:43.602+0800 I REPL [ReplicationExecutor] Member 192.168.168.122:27017 is now in state PRIMARY
...
2019-07-03T10:08:03.845+0800 I NETWORK [rsSync] Socket recv() errno:104 Connection reset by peer 192.168.168.122:27017
2019-07-03T10:08:03.845+0800 I NETWORK [rsSync] SocketException: remote: 192.168.168.122:27017 error: 9001 socket exception [RECV_ERROR] server [192.168.168.122:27017]
2019-07-03T10:08:03.853+0800 I NETWORK [rsSync] trying reconnect to 192.168.168.122:27017 (192.168.168.122) failed
2019-07-03T10:08:03.928+0800 I NETWORK [rsSync] reconnect 192.168.168.122:27017 (192.168.168.122) ok
2019-07-03T10:08:03.939+0800 E REPL [rsSync] 16465 recv failed while exhausting cursor
2019-07-03T10:08:03.939+0800 E REPL [rsSync] initial sync attempt failed, 9 attempts remaining
2019-07-03T10:08:08.939+0800 I REPL [rsSync] initial sync pending
2019-07-03T10:08:08.958+0800 I REPL [ReplicationExecutor] syncing from: 192.168.168.122:27017
2019-07-03T10:08:09.204+0800 I REPL [rsSync] initial sync drop all databases
2019-07-03T10:08:09.205+0800 I STORAGE [rsSync] dropAllDatabasesExceptLocal 3
2019-07-03T10:08:09.221+0800 I JOURNAL [rsSync] journalCleanup...
2019-07-03T10:08:09.221+0800 I JOURNAL [rsSync] removeJournalFiles
2019-07-03T10:08:09.895+0800 I JOURNAL [rsSync] journalCleanup...
2019-07-03T10:08:09.895+0800 I JOURNAL [rsSync] removeJournalFiles
...
resyn from the begining .......

To upgrade to MongoDB 3.0 all members of a replica set must be running the previous major release (MongoDB 2.6). See: Upgrade a replica set to 3.0.

A replica set configuration mixing 2.4 and 3.0 nodes is not supported or tested and will likely lead to unexpected errors. Since you have 1.7TB of data, I would definitely follow the recommended upgrade instructions in the MongoDB documentation and upgrade all members one major release series at a time (2.4 => 2.6, then 2.6 => 3.0).

Since it sounds like you are starting from a single node, it would be more straightforward to:

Review the driver & server upgrade notes and test driver compatibility for your target MongoDB server release. Drivers generally have a broader range of compaibility and should be upgraded first. There may be driver API changes required given the age of your current server version.
Take a backup of your MongoDB instance (perhaps using filesystem snapshots given your data size).
Upgrade your mongod as a standalone (2.4 => 2.6, 2.6 => 3.0) until you reach your destination release.
Convert your standalone to a replica set. When you add secondary members, this would be good timing to switch to the WiredTiger storage engine.

Ideally you should upgrade to a supported version of MongoDB (currently 3.4 or newer, although 3.4 will reach EOL in Jan 2020). Changes in successive releases have improved initial sync and 3.4 would be a much better starting point. MongoDB 3.4 also includes optional network compression for communication between mongod instances (which is enabled by default in 3.6+).


What's best practices for changing a user's email in an application? I'm working on a crud for an email application, and I'd like the ability to change "everything". Right now, however, it matches the JSON string to the Database based on the email address.

Problem: Under this model, a user can never change their email. A new email address = a new user.

    declare @email varchar(255),
    @ID int

    insert into announcement_jsonlog (json_string)
    values (@json)

    select @email = email from
            openjson(@json)
            WITH
            (email varchar(255)         '$.Email')

    insert into announcement_contacthistory (
    ID, Email, Prefix, FirstName, MiddleInitial, LastName, Suffix, Title, Company, Address1, Address2, City, State, Zip, Zip_4, Phone, Extension, Unregistered, AddDateTime, LModifiedDateTime,
    longkey, shortkey, History_addDateTime)

    select *, current_timestamp from announcement_contact
    where email = @email

Question: how would you design a CRUD that lets you change the email address, too?

It sounds like you are using the email address as a key, which is not good practise. Anything that can change is not a good candidate to be a key value which includes email addresses and phone numbers. Other reasons it is not a good idea include:

It could be NULL: maybe there are people in your target audience who don't use email so won't have an address?
Someone could have more than one address/number
People might share an address/number so it is not unique

If you do not have a non-changing, unique, never-NULL value that is therefore a candidate to be the key value, it is usual to have a generated surrogate key: most commonly an integer value or UUID that is decided when the record is created and does not depend on any properties of the record. You appear to have such a key already in your announcement_contacthistory table, the ID column.

This is particularly important if the key value is referenced by foreign key values in other tables as appears to be the case here. While SQL Server and Azure SQL DB have ON UPDATE CASCADE which makes changing such referred values possible and easy from the coders point of view (just update the primary value, the engine will go away and fix up all the references) this can be very inefficient resulting in a lot of related changes being made. If announcement_contacthistory had a reference to announcement_contact.id instead of announcement_contact.email you could change the email of a user without needing to update all the references in announcement_contacthistory and elsewhere.

Of course you may want to keep a history of email addresses so you know what was used at the point in time the messages was sent, either by still recording the address in the message record or by keeping a history of the values in the contact table (perhaps using a system versioned temporal table).

To find info on the relevant theory and practise, search around the following terms:

Key
Candidate Key
Surrogate Key
Primary Key
Unique Key


PostgreSQL 9.3.13, How do I refresh Materialised Views with different users? [I think the root cause of this problem is me not understanding permissions and privileges...]

So, to set the stage, the set up I have is a DB, call it MyDb.

I have two users, spu1 and u1, spu1 is a superuser and u1 a 'regular' user. The owner of MyDb is spu1. I think I should also say that u1 has create databases & create role privileges as inherited from a group role.

I have a schema sch1, which is a user defined schema.

Within this schema I have a table, call it tbl1, and a materialised view, call it mvw1.

The tbl1's owner is the spu1, the mvw1's owner is u1.

The Problem:

In the current set up, as described above, I cannot refresh mvw1 as either u1 or spu1. I simply get the below fun error (which I have googled extensively but not found anything which quite solves for my setup..).

ERROR:  permission denied for relation tbl1
********** Error **********

ERROR: permission denied for relation tbl1
SQL state: 42501

I have found out that

Changing the owner of mvw1 to the spu1, allows me to refresh as spu1.
Running the below allows me to refresh mvw1 as u1.

I'm trying to find out what the missing permission(s) (ideally the minimum required) I need to grant the regular user, u1, so that I can refresh this view when logged in as them.

The first option, whilst nice to know, doesn't solve my problem.
The second option seems like I'm in effect granting superuser permissions to a non-superuser, or rather granting greater privileges than I need to.

If anyone can explain to me what exactly is going on here (or point out what info I've missed out in the description of my problem needed to solve it), and let me know if my second option is in fact the way to go or of a better alternative?

Many Thanks!

You can do this with a function that runs in the security context of its owner.

Function that refreshes the view (create it with the user that owns the MV/table):

CREATE OR REPLACE FUNCTION refresh_mvw1()
RETURNS void
SECURITY DEFINER
AS $$
BEGIN
REFRESH MATERIALIZED VIEW mvw1 with data;
RETURN;
END;
$$ LANGUAGE plpgsql;

Grant execute on the function to any user that you want to be able to refresh the view:

-- Users have 'execute' permissions by default on functions!
revoke all on function refresh_mvw1() from public;
grant execute on function refresh_mvw1() to u1;

To refresh:

select refresh_mvw1();

From the Postgres docs:

  SECURITY DEFINER specifies that the function is to be executed with
  the privileges of the user that created it.

Version that supports a parameter:

CREATE OR REPLACE FUNCTION refresh_mv_xxx(table_name text)
RETURNS void
SECURITY DEFINER
AS $$
DECLARE sql text;
BEGIN
sql := 'REFRESH MATERIALIZED VIEW ' || table_name || ' with data';
EXECUTE sql;
RETURN;
END;
$$ LANGUAGE plpgsql;

... but I'm not sure if dynamic SQL will still execute as the definer.


NuoDB with Amazon S3 I think about developing my new mobile and web app against NuoDB. I believe in their technology. The thing that I care the most is backup and restore in case of data corruption and read / write speed scalability.

From what I've read, you can use NuoDB with Amazon S3 as the archive node for storing the data. That looks very useful because you aren't limited on how much data you can store on a single bucket. Furthermore, It's easy to backup the data because you can detach the archive node and you get to have a backup of the data which you can restore back by attaching the archive node back and let the other node sync with it.

Because I expect a lot of data (terabytes), it seems like a good solution, because I will not need to export the data, compress it and store it on EBS volume(s).

1) How efficient is S3 as archive node with NuoDB for high read/write scenerios?

2) Is this is a good backup option to use with NuoDB (the one that I described above)?

3) Should I be better to use MySQL Load balancers? I've read that their are many solutions that you can use on Amazon RDS to scale the DB horizontally and without any code and DB modification (ScaleBase?).

Barry Morris here from NuoDB:

You are correct that NuoDB can use S3 as it's primary storage.  In NuoDB terms you are configuring a Storage Manager for S3.

You can have as many other Storage Managers as you want, including others on S3, on a file system somewhere else, or on another supported Key/Value store.

All Storage Managers store the full database redundantly, so loss (or switching off) of Storage Managers will not cause data loss as long as one Storage Manager is still running somewhere in the system.

If you add an empty Storage Manager to a running database it will catch up to the current state of the database before taking part in database activity.  For a very large database this will not be the most efficient way to bring on a new Storage Manager, but it will work.

As relates to backup, you can adopt one of a few different strategies:
a) Do a conventional JDBC/ODBC/SQL level backup.
b) Take a Storage Manager offline, and rely on the fact that we can restart directly from that Storage Manager if needed.
c) Some people will feel that redundant storage in multiple storage managers is good enough and they don't need separate backups.

As for performance on S3 it will be load dependent.  If you want us to give you some estimates then let us know what you are doing and we can estimate it or run some tests for you.


SQL Server 2012 Query: Invoices with Earliest Date in State for each Vendor I have the tables:

Invoices ( VendorID, InvoiceDate, InvoiceNumber, InvoiceTotal,...),
Vendors (VendorName, VendorID,....)

I am trying to obtain a list of the earliest invoices for each vendor, together with VendorName, InvoiceNumber, InvoiceDate, InvoiceTotal (for that earliest date).

Here is what I have :

SELECT VendorName, InvoiceNumber, InvoiceDate, InvoiceTotal
FROM Vendors V JOIN Invoices I ON V.VendorID=I.VendorID
WHERE  InvoiceDate   <= ( SELECT  Min(InvoiceDate)
                          FROM Invoices
                          JOIN Vendors ON V.VendorID=Vendors.VendorID  )
GROUP BY VendorName, InvoiceNumber, InvoiceDate, InvoiceTotal

The problem is that I am getting only one invoice, which is the earliest of all invoices, not the earliest by state. What is going on?

Looks like you don't have InvoiceState anywhere in your query.

Here it is with a little better formatting (PRE-COLUMN COMMA MASTERRACE!!)

SELECT VendorName
, InvoiceNumber
, InvoiceDate
, InvoiceTotal
FROM Vendors V
JOIN Invoices I
    ON V.VendorID=I.VendorID
WHERE InvoiceDate <= ( SELECT Min(InvoiceDate)
                       FROM Invoices
                       JOIN Vendors
                       ON V.VendorID=Vendors.VendorID )
GROUP BY VendorName
, InvoiceNumber
, InvoiceDate
, InvoiceTotal

Adding it (InvoiceState) to your SELECT and GROUP BY would add that level of detail.  I'd assume you need to add it as your first column (before VendorName).

Edit: looking at it further, seems like you'd also need it in your Subquery.


How can I restart Database Mirroring after intentionally breaking it? I have setup database mirroring using SQL server on two servers. I have Enterprise Edition on the primary server and Standard Edition on the secondary server.

In order to verify the data on the secondary server I manually broke the mirroring, so I could inspect data on the mirror. Now after the verification process, I want to enable mirroring again.

The database is larger than 200 GB so it is not feasible to take a backup of the primary, send it over the network, and restore it on the secondary server because of the long distance between the two nodes, and the network configuration.

What options are available so I can restart mirroring?

Once you have broken the database mirror, you have no other option than to re-initialize it using a full backup and transaction log backup of the principal.

The steps you must take are:

Restore a recent full database backup of the principal database, after making sure that the principal database was already using the full recovery model when the backup was taken. The mirror database must have the same name as the principal database.
If you have taken any differential backups of the database since the restored full backup, restore your most recent differential backup.
Restore all the log backups done since the full or differential database backup.
Complete the remaining setup steps as soon as you can after taking the backup of the principal database. Before you can start mirroring on the partners, you should create a current log backup on the original database and restore it to the future mirror database.
Establish the mirroring session using ALTER DATABASE <database_name> SET PARTNER =<server_network_address> on the secondary server first, then on the principal server.

I would advise you use the native backup compression feature in SQL Server to compress the database and log backups (and perhaps subsequently compress them with 7-Zip); then ship the files using the fastest overnight shipping method you can find.  Have someone at the secondary location put those files onto the secondary server in a mutually agreeable location where you can subsequently restore them using WITH NORECOVERY.

Probably the most important piece of advice is to understand there is no need to validate the data at the secondary side since SQL Server commits the data at the secondary the same way it commits data at the primary; that is it uses ACID-compliant methodologies.  As such you can be assured the data at the secondary is an exact copy of the data at the primary.  The best way to confirm the secondary is a functional copy is to fail-over the mirroring session so the secondary becomes the primary.  You can do this during a maintenance window, with testers on-hand ready to validate the system works as expected.  Clearly, you'd take a complete set of backups prior to the first failover, just in case things don't work as expected.


How to install mysql/mariadb on NIXOS? I installed mariadb via configuration.nix. For the installation I made the following entries in my configurastions.nix.

services.mysql.enable = true;
  services.mysql.package = pkgs.mariadb;
  services.longview.mysqlPasswordFile = "/run/keys/mysql.password";

After I upgraded my system I get this error:

  warning: The option definition services.mysql.rootPassword' in/etc/nixos/configuration.nix’ no longer has any effect; please
  remove it. Use socket authentication or set the password outside of
  the nix store.

Before I always set my password in the configuration.nix with:

services.mysql.rootPassword

I also does a simple systemctl start mysql. However I get the following error message under systemctl status mysql:

● mysql.service - MySQL Server
   Loaded: loaded (/nix/store/2aliiwch37wiis98q6nbplcwyqk987l5-unit-mysql.service/mysql.service; enabled; vendor preset: enabled)
   Active: failed (Result: signal) since Mon 2019-08-12 00:45:56 CEST; 15min ago
  Process: 4784 ExecStartPre=/nix/store/bg6p3jcw23xmag07nzakkqh5hmy0h5yn-unit-script-mysql-pre-start (code=exited, status=0/SUCCESS)
  Process: 4785 ExecStart=/nix/store/r8s4kw7wskyn73ksb5nm9a5ykmyxk5ca-mariadb-10.3.15/bin/mysqld --defaults-file=/etc/my.cnf --user=mysql --datadir=/var/lib/mysql --basedir=/nix/store/r8s4kw7wskyn73ksb5nm9a5ykmyxk5ca-mariadb-10.3.15 $_WS>
  Process: 4820 ExecStartPost=/nix/store/hvfnmysk0hrgfp8pzr19r9sv0np88d1g-mysql-setup (code=exited, status=0/SUCCESS)
 Main PID: 4785 (code=killed, signal=ABRT)
   Status: "Taking your SQL requests now…"

With a mysql or mysql -u root -p I get the following error message:

  ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/run/mysqld/mysqld.sock' (2)

Would be grateful for any help.

Check the .err log file which will often be in your datadir which in your case appears to be /var/lib/mysql/, although sometimes the error log is under /var/log/. The error log file should hopefully have some good clues as to why the MariaDB server won't start up.

You can also have a look in your MariaDB config file which appears to be /etc/my.cnf and see if there's a log_error entry that defines a different location for the file.

See also the MariaDB documentation for the Error Log.

Your installation procedure may or may not have created a password for the root account. With most Linux distros I've encountered it doesn't create one. A password will in any case only be used when connecting over TCP. If you're logged in on the actual server, you can instead connect over the Unix socket with the Linux root user without a password. You can specify the socket file if you you know where it is and you want to be sure it's using the correct one, e.g. mysql -S /tmp/mysql.sock. Obviously, this won't work until you have successfully started the MariaDB server.

See also the MariaDB documentation on the Authentication Plugin - Unix Socket.


Login failure error log when querying Central Management Server group I am querying a group under a Central Management Server, which results in the following being logged to the SQL Server Error Log.

Error: 18456, Severity: 14, State: 38. Login failed for user
'domain\account'. Reason: Failed to open the explicitly specified
database. [CLIENT: xx.xx.xx.xx]

This error is only appearing on this one particular SQL Server in this group. Running a Profiler Trace on this server and reproducing the login failure brings up the following User Error Message:

Cannot open database "<multiple>" requested by the login. The login failed.

My account belongs to an AD group which is apart of the sysadmin server level role. The AD group's default database is the master database. The SQL Server's Connection Properties, Connect to database: is set to <default>. Which is no different from any of the other SQL Servers in this group.

Any reason why this would occur?

The error appears in the trace once I click New Query and the window opens. Before any querying actually occurs.
Removed and re-added the server registration, error still appears.
I can query the server from the window successfully. There's no real issue or error experienced other than the fact that this error appears in the error log.
Server collations are not all the same.
The collation of the problematic server is Latin1_General_CI_AS.
Default user options are the same.
All databases are in the online state.

I have seen this issue when the default database for your login is set to the wrong database.


1055 - Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column My SQL statement returns properly on my laptop's MySQL (Server version: 5.5.62-0ubuntu0.14.04.1 - (Ubuntu)) but on my server (Server version: 5.7.26-0ubuntu0.16.04.1 - (Ubuntu)) it returns an error.

SELECT *
FROM `orders`
WHERE `mail_sent`='No'
  AND `datetime` < DATE_SUB(NOW(), INTERVAL 15 MINUTE)
GROUP BY `contact_id`
ORDER BY `datetime` ASC;

  1055 - Expression #1 of SELECT list is not in GROUP BY clause and
  contains nonaggregated column 'shop.orders.id' which is not
  functionally dependent on columns in GROUP BY clause; this is
  incompatible with sql_mode=only_full_group_by

I think this problem due to strict mode enabled in your MySQL version. Kindly disable strict mode and try again.
To disable follow How to turn on/off MySQL strict mode in localhost (xampp)?
STRICT_TRANS_TABLES is responsible for setting MySQL strict mode.
To check whether strict mode is enabled or not run the below sql:
SHOW VARIABLES LIKE 'sql_mode';

If one of the value is STRICT_TRANS_TABLES, then strict mode is enabled, else not. In my case it gave
+--------------+------------------------------------------+
|Variable_name |Value                                     |
+--------------+------------------------------------------+
|sql_mode      |STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION|
+--------------+------------------------------------------+

Hence strict mode is enabled in my case as one of the value is STRICT_TRANS_TABLES.
To disable strict mode run the below sql:
set global sql_mode='';

[or any mode except STRICT_TRANS_TABLES. Ex: set global sql_mode='NO_ENGINE_SUBSTITUTION';]
To again enable strict mode run the below sql:
set global sql_mode='STRICT_TRANS_TABLES';

If you don't what to disable strict_mode then you have to change your sql query to follow sql standard.
like this..
SELECT
   aggregated_column,
   aggregated_column,
   aggregation_function(column),
   aggregation_function(column),
   aggregation_function(column)
FROM
  TABLE
WHERE [CLAUSE]
GROUP BY [GROUP BY FIELD]
WHILE [CLAUSE]
ORDER BY

for your query..
SELECT
  contact_id,
  min(mail_sent),
  min(datetime),
  max(datetime),
FROM `orders`
WHERE `mail_sent`='No'
  AND `datetime` < DATE_SUB(NOW(), INTERVAL 15 MINUTE)
GROUP BY `contact_id`
ORDER BY `datetime` ASC;

Let me know if that helps.


How to override SSIS 2008 package config file path? I have an SSIS 2008 package with a single configuration named Config. I would like to use the dtexec /SET switch to change the path of the config file that it uses so our developers that build the package can have it point to their local path, but when we deploy it to our production servers we can specify a new path for dtexec to load the config from so it can contain prod settings.

According to MSDN we should be able to do this using the /SET command as it mentions here:

  Using the /Set option to change the location from which the utility
  loads design-time configurations
  Behavior in SQL Server Integration Services 2008

  Succeeds.

  After the utility applies the run-time options, the utility reloads the design-time configurations from the new location.

I've tried:

dtexec /FILE "PackagePath" /SET  "\Package.Configurations[Config].Properties[ConfigurationString]";"ConfigFilePath"

and it fails with:

  The package path referenced an object that cannot be found: "\Package.Configurations[Config].Properties[ConfigurationString]". This occurs when an attempt is made to resolve a package path to an object that cannot be found.

I've also pasted a redacted version of my .dtsx file here in case anyone thinks I'm just mistyping something. And here is the contents of config that I'm trying to change it to.

I realize there are other ways of doing indirect configs using environment variables, etc... but that is not what I'm interested in so please do not offer up those suggestions. I'm specifically trying to use the /SET command since Microsoft has explicitly stated that we should be able to. Due to the way our environment is setup this method would be the best for us.

You dont have to use SET. Instead you can do that using DTExec.exe /CONFIG parameter as below:

SQL Agent Job --> Steps --> General --> Type (Operating System(CmdExec)) ---> command

"Drive\Microsoft SQL Server\110\DTS\Binn\DTExec.exe" /FILE "D:\SSIS\SSIS_PackageName.dtsx" /DECRYPT "STRONG_PASSWORD" /CONFIGFILE "D:\SSIS\CONFIG_FILE_PATH.dtsConfig" /CHECKPOINTING OFF /REPORTING E

EDIT: Below is an excerpt from msdn

/Conf[igFile] filespec
(Optional). Specifies a configuration file to extract values from. Using this option, you can set a run-time configuration that differs from the configuration that was specified at design time for the package. You can store different configuration settings in an XML configuration file and then load the settings before package execution by using the /ConfigFile option.
You can use the /ConfigFile option to load additional configurations at run time that you did not specify at design time. However, you cannot use the /ConfigFile option to replace configured values that you also specified at design time. To understand how package configurations are applied, see SSIS Package Configurations and Behavior Changes to Integration Services Features in SQL Server 2008 R2.

You have asked on How to override SSIS 2008 package config file path?

What you are talking about is SET switch -- Overrides the configuration of a variable, property, container, log provider, Foreach enumerator, or connection within a package.

I have been dealing with SSIS dev's and I use /CONFIG when deploying it to PROD .. provided the paths are same in the package.

EDIT: 2

Agree with OP that the behaviour has changed in 2008 and up :

In SQL Server 2008 Integration Services, events occur in the following order:

The utility first applies the design-time configurations.
The utility then applies the run-time options that you specified on the command line when you started the utility.
Finally, the utility reloads and reapplies the design-time configurations.

In above scenario, you can do as below:

option 1:

DECLARE @ssis_cmd VARCHAR(4000)
DECLARE  @Packagepath VARCHAR(50)
DECLARE  @FileName VARCHAR(50)
SET @Packagepath = 'D:\SSIS\SSIS_PackageName.dtsx' -- SSIS package location
SET @FileName = 'D:\SSIS\CONFIG_FILE_PATH.dtsConfig' --Passing dynamic variable i.e 'file location' into ssis package.
SELECT @ssis_cmd = 'dtexec /F "' + @Packagepath + '"'
SELECT @ssis_cmd =
@ssis_cmd + ' /SET \Package.Configurations[Config].Properties[ConfigurationString];"' + @FileName + '"'
EXEC master..xp_cmdshell @ssis_cmd

Option 2:

As described here, you can create table SSIS_Configurations and then load them during runtime. Good example is given here.

Found a connect item here

HTH


Deleting data from some but not all tables I've got a moderately large sql server db for which some data is imported from an external system, and some is entered through the front end or set up when the app is set up.  I want to delete all the data from all the tables we import to, but leave the data on the other tables.  Everything of course has foreign keys/relationships/constraints set up.

First I tried manually deleting in the proper order...it was a slow process to determine the order, and the delete itself ran very slowly.
- is there a way to determine the delete order that won't run up against constraints?

I tried truncate, but that required dropping all the constraints, and I was concerned about getting them all back on without missing anything.
--is there a way to via script to drop them all then bring them all back?
Last time I ended up just creating an empty one, then bringing data over with visual studio database compare which was also slow and painful.

What's the best way?  Any tips?  I'm a .net dev that does many stored procedures but not good with the admin or DBA type stuff.

Dropping and then recreating all of the foreign keys shouldn't be a problem because you should have them all in your source control system. If that's not the case then you have much bigger problems.

You can generate DROP/CREATE scripts through SSMS. If you right-click on your database then you can go to Tasks->Generate Scripts... and select the tables that you need through the wizard. You'll have to do a lot of cut and paste to get just the FKs. Alternatively, you can dig down to the FKs in the object tree, right-click each one, and generate a script from that menu.

In either case, it sounds like your deploy process for your production system is in some bad shape.


Pioneers in Database Research Who all people have made life changing contributions to database research? I want to know more about them.

This could be viewed as a very speculative question. However, for the database community it should be a no brainer and a slam dunk.

Edgar Frank Codd : Inventor of the Relational Model we all model databases with.

Christopher J Date : Extensive writer of Database Theory. He keeps evolving database theory and expresses such in his current website.

Donald Chamberlin and Raymond Boyce : Authors of SQL

Some people say that Pioneers explore the land and take all the arrows, while settlers get to dwell in the land left by the trailblazing of Pioneers.

Other names can be added to this list, but database theory and research had its roots from the works of these Pioneers. All users of Databases after their inital works are like Settlers in the land.

Anyone can read books or Google names of such people, but nothing beats using the principles of relational theory to appreciate the depth of their research.


Upgrade from SQL server 2012 to 2016 we are going to upgrade from SQL server 2012 Standard edition to SQL Server 2016 Enterprise edition. we can't have a downtime more than 30 mins .The new SQL server will use always on availably group 1 primary and 2 read-only secondaries .   I  am looking for the fastest way to do this upgrade.
I am thinking  of creating mirroring between the old server and new primary server , then shut down the application update the connection string , fail over the DB to the new server, stop mirroring,  start the application. After  the application is up,  start in configuring the Availability group.

IS this a correct way to use

What you've got sounds like it should work and would get you online quickly, however you will have no HA plan if something fails before you've got the 200GB database replicated to at least one of your secondaries.

Also what will you do if your upgrade fails? I don't think there will be a way back to the old server without going back and restoring backups.

When we've done similar migrations (2012 to 2014), we've done the following:

Create the Availability Group
Take a full backup of current production
Restore on all servers in the AAG (leaving in restore mode)
Take a differential backup of current production shortly before your 30 minute window and restore to all servers on the AAG (leaving in restore mode)
Take regular log backups of the current production server and restore (you could automate this with log shipping)
When your 30 minute window starts, restore the last of the log backups, bring the new primary online, make sure everything is attached to the AAG and you're ready to go


Left join for unique pairs, 0 otherwise Just to continue the question , I'm struggling to do left join and to keep equal sized time series generated in the previous step.

So I have customer_date_dimention table with equal time series for each cust_id.

cust_id, date

1, 2018-01-01

1, 2018-01-02

2, 2018-01-01

2, 2018-01-02

And then I have retention_weekly_intensity

cust_id, date, intensity
1, 2018-01-02,6

2, 2018-01-02, 10

and then I want to create the following result:

cust_id, date, intensity

1, 2018-01-01, 0

1, 2018-01-02, 6

2, 2018-01-01, 0

2, 2018-01-02, 7

The query is the following, but the verification gives different sized series

create table retention_weekly_intensity_balanced as
 select
 coalesce(t.cust_id, 'unidentified') as clientidno,
 dtd.d,
 coalesce(t.size_of_increased_intensity) as size_of_increased_intensity
 from customer_date_dimention dtd
 left join retention_weekly_intensity t

ON
 dtd.d = t.date
 and
 dtd.cust_id = t.cust_id
WHERE dtd.cust_id is not NULL
 ;

select cust_dt, count(*) from retention_weekly_intensity_balanced group by 1

How do I properly join the labeled series with other data?

Kind regards

If i understood correctly, then you want to join your two tables and if there is no entry in retention for a given cust_id and date the intensity should return 0.

So you might be looking for something like this:

DROP TABLE IF EXISTS customer_date_dimension;
CREATE TABLE customer_date_dimension(
 cust_id INT,
 cust_date DATE
);

INSERT INTO customer_date_dimension(cust_id, cust_date) VALUES
(1, '2018-01-01'),
(1, '2018-01-02'),
(2, '2018-01-01'),
(2, '2018-01-02');

DROP TABLE IF EXISTS retention;
CREATE TABLE retention(
  cust_id INT,
  cust_date DATE,
  intensity INT
);

INSERT INTO retention(cust_id, cust_date, intensity) VALUES
(1, '2018-01-02', 6),
(2, '2018-01-02', 10);

SELECT c.cust_id AS customer_id, c.cust_date AS "date",
      (CASE WHEN r.intensity IS NULL THEN 0 ELSE r.intensity END) AS intensity
FROM customer_date_dimension AS c
LEFT JOIN retention AS r ON
      c.cust_id = r.cust_id
  AND c.cust_date = r.cust_date;


Are mysql global variables values in bytes? When I run the following in my mysql terminal

mysql> SHOW GLOBAL VARIABLES LIKE 'tmp_table_size';
+----------------+----------+
| Variable_name  | Value    |
+----------------+----------+
| tmp_table_size | 16777216 |
+----------------+----------+
1 row in set (0.01 sec)

This is probably very oblivious but is the value 16777216 in bytes?  I just want to be sure and can't seem to find a definitive answer on the web.

Is this the same for all values? Such as query_cache_size & query_cache_limit

I need to know this to set up a Amazon RDS Param group.

Yes, it is bytes. You also can not set anything other like

set session tmp_table_size = 8k;

This is only possible in configuration files. But you can do something like

set session tmp_table_size = 8 * 1024;


How to stop a job for the rest of the day after the relevant condition has been satisfied once? I have a job that runs every 5 minutes to check an IF condition. If the condition is true, it will send an email, if not, it won’t do anything.

My problem is that once the IF condition is true, it will remain true for the whole day and, as the job is running after every 5 minutes, it will keep sending the email after every 5 minutes.

I need to stop the job for the whole day once the email has been sent once. Is there a way to do so?

I actually wrote a similar tip at mssqltips.com.
For your current case, the general idea would be like this:

you may add an additional step at the end of the job, once your primary job steps succeed, the job will run the final step (i.e. the newly added step)
If your job fails in your primary steps, your job will just exit and no need to run the final added step.
In you final job step, you will do one thing, i.e. updating your job schedule' start date to be next day.

use msdb

declare @active_date int, @sch_name varchar(128);

select @sch_name=s.name
from msdb.dbo.sysschedules s
inner join msdb.dbo.sysjobschedules js
on js.schedule_id = s.schedule_id
and js.job_id = $(ESCAPE_NONE(JOBID));

set @active_date = cast(replace(cast(cast(getdate()+1 as date) as varchar(10)), '-', '') as int);

exec sp_update_schedule @name=@sch_name, @active_start_date= @active_date;

Of course, I assume your job has one dedicated schedule only.
The approach is all self-dependent and you do not need any additional external jobs to manage your current job.


Equivalent Linux commands for Windows ones to create user and database There is a manual for configuring PostgreSQL on Windows with these commands:
C:\"Program Files"\PostgreSQL\12\bin\createuser.exe --username=postgres --superuser --pwprompt saleor

C:\"Program Files"\PostgreSQL\12\bin\createdb.exe --username=postgres --owner=saleor saleor

I'm going to do the same on Linux, but I'm not sure how.
I know that I have to do:
> sudo su postgres
> psql

psql (13.3)
Type "help" for help.

postgres=#

But I'm not sure what to do next. I appreciate any help.

On a 32-bit openSUSE Tumbleweed with PostgreSQL 13 installed. Just run:
> sudo su postgres
> createuser --username=postgres --superuser --pwprompt saleor
> createdb --username=postgres --owner=saleor saleor

If the user already exists, it can be deleted by:
> sudo su postgres
> dropuser --username=postgres saleor

To see a list of users and databases, just run:
> sudo su postgres
> psql
# \du
# \l
# \q


How to find out if an index is aligned I'm struggling to find any confirmation within sql server or sql management studio that my index is in fact aligned with my partition.

I'm creating a primary key constraint on a partitioned table:

CREATE PARTITION FUNCTION [pfDATA](bigint) AS RANGE RIGHT FOR VALUES (0);
CREATE PARTITION SCHEME [psDATA] AS PARTITION pfDATA TO ([P_CURRENT], [P_CURRENT]);
CREATE TABLE [dbo].[DATA](
    [DATAID] [bigint] NOT NULL,
    [VALUE1] [int] NOT NULL,
    [VALUE2] [int] NOT NULL,
    [VALUEetc] [int] NOT NULL
    CONSTRAINT [PK_DATA] PRIMARY KEY CLUSTERED
    (
        [DATAID] ASC
    ) ON psDATA(DATAID)
) ON psDATA(DATAID);

SQL management studio doesn't seem to give me any feedback visually about the characteristics of this index.

What I normally do when the application doesn't tell me what I need to know visually, is let the application generate a CREATE script (for the key/index or table, it doesn't matter in this case). What I expect to see is the partition scheme being mentioned in the constraint clause, but what I get is no mention of any partitioning:

CREATE TABLE [dbo].[DATA](
    [DATAID] [bigint] NOT NULL,
    [VALUE1] [int] NOT NULL,
    [VALUE2] [int] NOT NULL,
    [VALUEetc] [int] NOT NULL
    CONSTRAINT [PK_DATA] PRIMARY KEY CLUSTERED
    (
        [DATAID] ASC
    )
);

Is there any way to check if my indexes are constructed correctly (and are in fact, aligned)?

I guess you want to see partitions in scripted objects.
All you need to do is to go to Tools-Options-Scripting and play with checkboxes there:


Are the columns returned from CROSS APPLY with a Table Valued Function evaluated only once? Considering the following query:
select Student.Id,
       case when SomeTvfResult.Num = 3 then N'foo'
            when SomeTvfResult.Num > 42 then N'bar'
            else N'baz'
       end as SomeStr
from Student
cross apply SomeTableValuedFunction(Student.Id) SomeTvfResult

I wonder if the value of SomeTvfResult.Num gets recalculated each time I reference it in the case statement or it's somehow cached and evaluated only once. If it's relevant, you can assume that SomeTableValuedFunction is an inline TVF.
Since I've learned that coalesce is simply replaced by a case statement that causes the given expressions evaluated more than once I feel concerned each time I use some function in my queries.

The way you have phrased the question indicates you fear that the whole TVF will be evaluated multiple times. This is certainly not the case, as with an inline TVF the inner query is effectively pasted into the outer query as a derived table.
What you can possibly be concerned about is whether the computation of a computed column in the TVF is deferred or not. Unfortunately, the XML query plan has no info on this (you would be looking at the relevant COMPUTE SCALAR).
Paul White has a very good blog post explaining this. Often SQL Server will defer the calculation of a Compute Scalar to later in the plan, and this can become an issue with very complex or computationally difficult calculations.
For example, PATINDEX and SUBSTRING (and even REVERSE) are often used in tandem to do string manipulation. A common trick to avoid lots of copy-pasta is to CROSS APPLY (VALUES) of one calculation, then reference it in another CROSS APPLY, similar to how you might do it procedurally.
What I have noticed from query plans is that the optimizer collapses the whole thing together, no nested loops, no constant scans, which is normally the right idea. As I understand it, the execution engine will afterwards pull out various pieces which it thinks need pre-calculating and storing, and some which it will calculate when needed, but sometimes it can get it wrong, especially when dealing with very large strings.
What you can do in such a case instead is to use OUTER APPLY (VALUES), this appears to create an optimization fence, causing a nested loop with a constant scan. Do not do this blindly. Test it, time it, look at CPU time etc.


MariaDB I/O capacity with innodb_io_capacity parameter I use hdparm to test my SSD hard drive I/O benchmark :

[root@mail ~]# hdparm -t -T /dev/xvda2

/dev/xvda2:
 Timing cached reads:   12906 MB in  1.99 seconds = 6477.79 MB/sec
 Timing buffered disk reads: 1650 MB in  3.00 seconds =  550.21 MB/sec

I saw innodb_io_capacity parameter for MariaDB is :

Storage type The innodb_io_capacity value
HDD 200
RAID 10 HDD 1,000
SSD 5,000
Fusion IO 10,000

Could some body please tell me which value i should use in this case ?

It looks to me that you should increase that parameter - what I would advise is that you set up database benchmarking  and vary the parameter in steps of 1000 until you find a sweet spot. There are any number of MySQL benchmarks - sysbench for example. You could try mysqlslap - it's "quick and dirty" but should be good for a first approximation - 1, 2, 3, 4, 5 and check out the documentation. I've been messing with this recently so I have the links to hand.


optimal table design mysql with primay key and varchar value I have a table:

id (PRIMARY KEY AUTO_INCREMENT)
value (UNIQUE KEY)

which currently holds over 50 millions rows (and growing fast) and is hit hard:

process 1 queries this table (>200/sec) with a value to get it's id and insert it of not there
process 2 queries this table (>400/sec) with an id to get it's value.

So both processes are hitting the table and the queries are slowing down.
I considered partitioning but when on id the problem arises that process 2 queries need to check all partitions and when on value the same problems comes the other way around.

What would be an optimal table design for this?
Currently I use MyISAM since I get a higher SELECT performance than InnoDB but if someone tells me to change it, no problem.

thx,
Martin

You may find what I am about to suggest a little surprising

Add the following indexes

ALTER TABLE mytable ADD INDEX id_value_ndx (id,value);
ALTER TABLE mytable ADD INDEX value_id_ndx (value,id);

This will actually cache id and value together in the MyISAM Key Cache. That way, all queries are in memory only. Queries will not go to the table at all.

I would also propose you make a dedicated 1GB MyISAM Key Cache just for your table

STEP 01: Create a script to load the table into the dedicated key cache

cd /var/lib/mysql
echo "SET GLOBAL mykeycache.key_buffer_size = 1024 * 1024 * 1024;" > init-file.sql
echo "CACHE INDEX mytable IN mykeycache; >> init-file.sql
echo "LOAD INDEX INTO CACHE mykeycache; >> init-file.sql

STEP 02 : Add this to /etc/my.cnf

[mysqld]
init-file=/var/lib/mysql/init-file/sql

STEP 03 : service mysql restart

From here on, every restart of mysql will setup this private key cache for your table

Give it a Try !!!


How to uninstall SQL Server 2014 Standard Edition without install media? I've had a copy of SQL Server 2014 Standard installed on my dev box as the default instance for a few years. I installed standard on my machine because I had a free license that I could use via my MSDN subscription. Now I want to uninstall SQL Server 2014 and make SQL Server 2017 Developer Edition my default instance. I tried to uninstall SQL Server 2014 via the standard Add/Remove Programs workflow but after asking me what features I want to uninstall it prompts me for the directory containing the uninstall media. Unfortunately I didn't save the SQL Server 2014 download package I got from MSDN, and I no longer have access to MSDN. I also checked My Visual Studio but it only goes back to SQL Server 2016. How can I uninstall SQL Server 2014 Standard without the install media?

Further background:

The whole reason I want uninstall SQL Server 2014 is I want to use the STRING_AGG function which is new to Azure SQL Databases and SQL Server 2017. To make setting up development environments easier we use the dot notation for our local environment development connection strings, for example our connection string is:

Data Source=.;Initial Catalog=<Database Name>;Trusted_Connection=True;Connection Timeout=30;

The dot notation connects to the default database, and to my knowledge I can't make SQL Server 2017 the default database without uninstalling SQL Server 2014 first. If I can use the dot notation connection string to connect up to SQL Server 2017 without uninstalling SQL Server 2014 then I'd be open to that solution as well.

I was able to uninstall my SQL Server 2014 instance by downloading the SQL Server 2014 evaluation version*. In order to download the evaluation version I had to give Microsoft some contact details and then download two files. One file had a .box extension and the other had a .exe extension. The executable seemed to just unpack the box file. After the box file was unpacked I reinitiated the SQL Server 2014 uninstall process via the standard Add/Remove Programs workflow. Note that I never attempted to install the evaluation version. I just used the unpacked files to uninstall my existing SQL Server 2014 instance.

I chose to uninstall the database components but leave the shared components. Once the uninstall process started requesting the install media I found it would ask for various .msi files. Most of the time it would ask for a msi file with "loc" in the filename followed by a msi file without "loc" in the filename. Within the folder that I unpacked the box file the "loc" version of the msi files was located:

\1033_ENU_LP\x64\Setup\

and the files without "loc" in the filename were located:

\x64\Setup\

After the uninstall process completed I restarted my computer and was able to install SQL Server 2017 Developer Edition as the default instance. Interestingly during the SQL Server 2017 install process I was prompted for the install media for 2 SQL Server 2014 files, one of which was SQLWRITER.MSI. I don't know if this is because I didn't uninstall the shared components for SQL Server 2014. If this gives me problems in the future due to the time limited nature of the SQL Server 2014 evaluation version I used to uninstall my old SQL Server 2014 Standard Edition I'll update this answer.

Alternative approaches that were mentioned in comments that I didn't try, but seem like they'd be good fall back measures.

The whole reason why I wanted to uninstall SQL Server 2014 was so I could use the dot notation to connect to SQL Server 2017. Scott Hodgin recommended I investigate a technique to make a named instance appear like a default instance. I didn't try this technique but judging by the votes its collected on StackOverflow it has surely solved some people's problems.
Aaron Bertrand it seems has been in a similar situation as me in the past and wrote two different blog posts (first, second) on ways to force the removal without any install/uninstall media. Both blog posts used essentially the same technique of building a batch file to execute the msiexec command which forces an uninstall based on the GUID found in the registry. If you only read one of the blog posts I'd recommend the second one. It uses a little PowerShell script to build the batch file. While I didn't personally need to use this approach it was my next option. Also I'd like to give Aaron a hat tip for fighting through a pain point before a common man like myself encountered the problem, and for documenting a good solution to the problem for posterity.

*: Thank you to Ali Razeghi for suggesting this approach in the comments.


databasename_indx.ndf file not referenced but can't delete I have a SQL Server 2000 Express database for a legacy app I'm ditching soon. Until then, I'm at my 2 GB limit.

According to sp_helpfile the database has one .mdf (900 MB) and one log file, but the .ndf (1100 MB) is not referenced. But if I stop the SQL Server and delete the .ndf file, then I can't access the database once I start SQL Server back up, and the following error is logged:

  Data File .\CallAccounting_indx.ndf does not exist - unable to activate.
  Device activation error. The physical file name '.\CallAccounting_indx.ndf' may be incorrect.

I'm in the middle of purging old records to free up pages so I can't recreate the other errors just now.

I have no idea how to proceed here. Am I truly hitting the 2 GB limit, or is there something easy I can do here to clean up?

groupid allocpolicy status  groupname
1       0       16  PRIMARY
2       0       0   INDEXGROUP

fileid  groupid size    maxsize growth  status  perf    name                filename
1       1       59360   -1      10      1081346 0       CallAccounting_dat  C:\Program Files\Microsoft SQL Server\MSSQL\Data\CallAccounting_dat.mdf
2       0       96      -1      10      1081410 0       CallAccounting_log  C:\Program Files\Microsoft SQL Server\MSSQL\Log\CallAccounting_log.ldf

status  fileid  name            filename
32770   1   CallAccounting_dat  C:\Program Files\Microsoft SQL Server\MSSQL\Data\CallAccounting_dat.mdf
32834   2   CallAccounting_log  C:\Program Files\Microsoft SQL Server\MSSQL\Log\CallAccounting_log.ldf

fileid  groupid size    maxsize growth  status  perf    dbid    name                filename                                                                    createlsn               droplsn
1       1       59360   -1      10      32770   0       5       CallAccounting_dat  C:\Program Files\Microsoft SQL Server\MSSQL\Data\CallAccounting_dat.mdf     0x00                    0x00
2       0       96          -1  10      32834   0       5       CallAccounting_log  C:\Program Files\Microsoft SQL Server\MSSQL\Log\CallAccounting_log.ldf      0x00000000000000000000  0x00000000000000000000
3       2       50208   -1      10      32770   0       5       CallAccounting_indx C:\Program Files\Microsoft SQL Server\MSSQL\Data\CallAccounting_indx.ndf    0x050000001E0000000100  0x00000000000000000000

The information about files has somehow got out of synch between the master database and the CallAccounting database.

So I suppose there are two possibilities.

The file information was erroneously removed from the system tables in the user database.
The file row should have been removed from the master database but for some reason wasn't (or maybe master was restored from backup at some point which brought back in information about a previously deleted file).

The second one seems much more likely as I'm pretty sure that in the first case you would be encountering a bunch of other errors when queries tried to access data in that file.

First run DBCC CHECKDB and see if that tells you anything useful.

If it doesn't you could use DBCC IND to dump out information about all relevant objects to check that nothing appears to be using that file.

SET NOCOUNT ON;

CREATE TABLE #DBCCIND   (
     PageFID         TINYINT,
     PagePID         INT,
     IAMFID          TINYINT,
     IAMPID          INT,
     ObjectID        INT,
     IndexID         TINYINT,
     PageType        TINYINT,
     IndexLevel      TINYINT,
     NextPageFID     TINYINT,
     NextPagePID     INT,
     PrevPageFID     TINYINT,
     PrevPagePID     INT
  );

DECLARE @id varchar(10)

DECLARE @C1 AS CURSOR;
SET @C1 = CURSOR FAST_FORWARD FOR SELECT DISTINCT id
FROM   sysindexes
OPEN @C1;
FETCH NEXT FROM @C1 INTO @id;
WHILE @@FETCH_STATUS = 0
BEGIN

INSERT INTO #DBCCIND
    EXEC('DBCC IND (0, ' + @id + ', -1) WITH NO_INFOMSGS')
  FETCH NEXT FROM @C1 INTO @id ;
END

SELECT IAMFID AS FileId
FROM #DBCCIND
UNION
SELECT NextPageFID
FROM #DBCCIND
UNION
SELECT PrevPageFID
FROM #DBCCIND

DROP TABLE #DBCCIND

If you do see FileID of 3 returned then something is definitely using it.

If you don't I'd probably try attaching (copies of) just the mdf and ldf to another instance and test to see it all works correctly (with CHECKDB and selecting all rows from all tables).

If all of this works without error then it is possibly OK to backup everything then go ahead and delete that row referencing the file from master..sysaltfiles (will need ad hoc updates to system catalogs enabled).

I, of course, accept no liability if this all goes horribly wrong however!


Backup to the null device on SQL Server for Linux In SQL Server on Windows, it's a common enough thing to backup a database to the NULL device if you need to take a backup but don't actually care about that backup.  This is useful for changing the recovery model in a dev environment, as an example.

The backup statement is something like:

BACKUP DATABASE [xyz] TO DISK = 'NUL:';

In SQL Server for Linux, one might assume /dev/null would be the logical equivalent, however if you attempt that:

  Processed 125224 pages for database 'xyz', file 'xyz01' on file 1.
  Processed 2 pages for database 'xyz', file 'xyzLog' on file 1.
  Msg 3634, Level 16, State 2, Line 16
  The operating system returned the error '31(A device attached to the system is   not functioning.)' while attempting 'DiskChangeFileSize' on '/dev/null'.
  Msg 3013, Level 16, State 1, Line 16
  BACKUP DATABASE is terminating abnormally.

The above command does appear to reset the log_reuse_wait and log_reuse_wait_desc columns in sys.databases, so perhaps /dev/null really does work.

I'm using SQL Server on Linux v14.0.900.75 on RedHat Enterprise Linux 7.2.

The "typical" SQL Server syntax does actually work in SQL Server for Linux; so try:

BACKUP DATABASE [xyz] TO DISK = 'NUL';


Problem with a foreign key referencing a primary key which is also part of an unique key (MariaDB) I created a database using MariaDb. I have the following author table (with id as primary key), which is extended by two other tables,  author_personal and author_corporate, representing two different kinds of author and having different fields:
CREATE TABLE `author` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `author_type` char(1) NOT NULL,
  -- other fields
  PRIMARY KEY (`id`),
  UNIQUE KEY `author_UN` (`id`,`author_type`) USING BTREE,
  CONSTRAINT `author_CHECK_type` CHECK (`author_type` in ('P','C'))
);

CREATE TABLE `author_personal` (
  `id` int(10) unsigned NOT NULL,
  `surname` varchar(30) DEFAULT NULL,
  `name` varchar(30) DEFAULT NULL,
  -- other fields
  `author_type` char(1) GENERATED ALWAYS AS ('P') VIRTUAL,
  PRIMARY KEY (`id`),
  KEY `author_personal_FK` (`id`,`author_type`),
  CONSTRAINT `author_personal_FK` FOREIGN KEY (`id`, `author_type`) REFERENCES `author` (`id`, `author_type`) ON DELETE CASCADE
);

CREATE TABLE `author_corporate` (
  `id` int(10) unsigned NOT NULL,
  `corporate_name` varchar(50) DEFAULT NULL,
  `corporate_acronym` varchar(5) DEFAULT NULL,
  `author_type` char(1) GENERATED ALWAYS AS ('C') VIRTUAL,
  PRIMARY KEY (`id`),
  KEY `author_corporate_FK` (`id`,`author_type`),
  CONSTRAINT `author_corporate_FK` FOREIGN KEY (`id`, `author_type`) REFERENCES `author` (`id`, `author_type`) ON DELETE CASCADE
);

Although author.id would be enough, I decided to create an UNIQUE KEY (id, author_type) to be referenced by the foreign key in the two other tables, so that it would be impossible to reference from author_personal an author which is not flagged as P, and from author_corporate an author which is not flagged as C.
The problem rises when I want to reference author using the primary key, like in this table:
CREATE TABLE `work_authors` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `work_id` int(10) unsigned NOT NULL,
  `author_id` int(10) unsigned NOT NULL,
  -- other fields
  PRIMARY KEY (`id`),
  KEY `work_authors_FK` (`work_id`),
  KEY `work_authors_FK_author` (`author_id`) USING BTREE,
  CONSTRAINT `work_authors_FK` FOREIGN KEY (`work_id`) REFERENCES `work` (`id`) ON UPDATE CASCADE,
  CONSTRAINT `work_authors_FK_author` FOREIGN KEY (`author_id`) REFERENCES `author` (`id`) ON UPDATE CASCADE
);

Both DBeaver and PhpMyAdmin think that work_authors_FK_author references author_UN instead of the actual primary key (author.id).

This means that in DBeaver I am not able to click on the values in work_authors.author_id and open the referenced record in authors because I get the following error:
Entity [davide_library.author] association [work_authors_FK_author] columns differs from referenced constraint [author_UN] (1<>2)
I created the foreign keys using DBeaver. Although I selected PRIMARY as the unique key, in the list of foreign keys it always show author_UN as the referenced object. I don't know if this behavior depends on MariaDB or DBeaver.
My question is:
Is there any way to explicitily reference in the DDL the primary key instead of the unique key?
or, alternatively:
Is there a different way, other than the UNUQUE constraint, to check that author_personal references only authors flagged with P, and the same for author_corporate?

MariadDB seems to handle it correctly. According to Fiddle the information_schema is correct, so the problem seems to be with your tools:
SELECT table_name, constraint_name, referenced_table_name, unique_constraint_name
FROM information_schema.referential_constraints;

table_name          constraint_name         referenced_table_name   unique_constraint_name
-----------------------------------------------------------------------------
author_personal1    author_personal_FK      author1                 author_UN
work_authors1       work_authors_FK_author  author1                 PRIMARY


InnoDB Failure of some kind I have MySQL 5.5 installed. I tried to install Joolma but it failed. I went into their sql and replace EGNINE=InnoDB with MyISAM and the install worked.

InnoDB is listed under SHOW ENGINES;

Any idea what the cause is or how to fix this so other InnoDB sites can be used?

I had these errors:

MySQL ERROR No: 1030
MysqL Error: Got error -1 from storage engine

check your my.cnf file to see if there is anything force innodb to recover

force_innodb_recovery

take a look to this topic that talk about similar problem Got error -1 from storage engine

or make sure the /tmp partition or the partition the MySQL databases are stored on is not full.


MySQL dump with low disk space I have an asterisk PBX that has call recordings stored in a SQL database table.

This table has 9199 rows and is consuming 21GB on my server. I want to dump the data in this table to restore space on the server. I also only have 2GB of space left on my server. So could someone please help me with the following:

Is there an issue if I start to flush the table with only 2GB of space on my disk?
What would be the best command to use to flush this table?

Try cleaning the logs if you don't need them.

there is something freak in 9199 rows and consuming 21GB

see Freeing up disk space in 'mysql\data'


How can I tell which data sources are being used in SSRS? We have a bunch of SSRS (2008) reports deployed to our web portal.  We have edited some of the reports to use a shared data source different than the one with which it was originally deployed.

I am looking for a way to query the ReportServer database to show me which reports use which of these shared data sources. I found that you can use the XML data stored in Catalog.Content to show what data source is being used, but this appears to the data source with which the report was originally deployed.

Okay, I got it.  Found this link which helped: http://social.msdn.microsoft.com/Forums/en-US/sqlreportingservices/thread/581c7068-0abe-49f9-a1a5-93e94f472641/

I distilled that information to this query:

select
  Catalog.name,
  cat1.Name datasource
from
  Catalog
  join DataSource
    on Catalog.ItemID = DataSource.ItemID
  join Catalog cat1
    on DataSource.Link = cat1.ItemID
where
  Catalog.Type = 2

Hope this helps someone else in the future!


Pulling a username from table one to update credits on table 2 under the same username I currently have 2 tables, one being a table that stores votes, and the other being the members table.  I want SQL to pull the username from the "votes" table (as this keeps track of who voted), then add +1 "credits" in the table "members".  Along with this, it will have to change the spaces to underscore and remove capital letters if they are used. Does anyone have an idea on how to do this?

Unless you only planning to hold one "Vote" (ever!), I'd suggest you're missing at least one table.

select * from Users ;
+----+----------+------------+
| id | forename | surname    |
+----+----------+------------+
|  1 | Fred     | Flintstone |
|  2 | Wilma    | Flintstone |
|  3 | Barney   | Rubble     |
|  4 | Betty    | Rubble     |
+----+----------+------------+

select * from Votes ;
+----+------------------------------+
| id | Vote Description             |
+----+------------------------------+
| 11 | Mid-Winter Festival Location |
+----+------------------------------+

select * from User_Votes ;
+-----+---------+---------+------------+
| id  | vote_id | user_id | Choice     |
+-----+---------+---------+------------+
| 111 |      11 |       1 | Bedrock    |
| 222 |      11 |       2 | Rock Vegas |
| 333 |      11 |       4 | Rock Vegas |
+-----+---------+---------+------------+

  I want SQL to pull the username from the "votes" table

The Username does not belong in the Votes table.
A User can exist without ever casting a vote, and so needs to exist in its own table.
Try to avoid using anything that might change as a Key.  Going around the database changing duplicated values all over the place is (a) Poor Design and (b) terrible in terms of performance.

  ... it will have to change the spaces to underscore and remove capital letters

You cannot query or join effectively if your data is "noisy".  This is another good reason to use a Surrogate Key (user_id) "inside" the database.  It eliminates all these Data Entry requirements (for this part of the job, anyway).
The User's name should be stored once and only once so that, when it needs changing, you change it in (one and only) one place.

  ... this keeps track of who voted ...

Using the tables I describe above, you get a list of who voted on this particular vote like this:

select u.forename, u.surname, v.choice
from Users u
inner join User_Votes uv
      on   u.id = uv.user_id
where uv.id = 11
order by 2, 1 ;

+----------+------------+------------+
| forename | surname    | choice     |
+----------+------------+------------+
| Fred     | Flintstone | Bedrock    |
| Wilma    | Flintstone | Rock Vegas |
| Betty    | Rubble     | Rock Vegas |
+----------+------------+------------+

(And, if you wanted those who haven't voted yet)

select u.forename, u.surname
from Users u
left join User_Votes uv
      on   u.id = uv.user_id
      and  uv.id = 11
where uv.user_id is null
order by 2, 1 ;

+----------+------------+
| forename | surname    |
+----------+------------+
| Barney   | Rubble     |
+----------+------------+


Always On Availability Groups with Encrypted Connections We have a two-node SQL Server 2016 AOAG, TestA, TestB and listener TestL. We want to begin using encrypted connections between the app server/client and the db server.
I have found the section below in two MS documents about implementing connection encrypting. Just wanted to know if this holds true for AOAGs? I suspect it does since AOAG does require the use of a failover cluster, but would like confirmation.

Encryption on a Cluster
If you want to use encryption with a failover cluster, you must install the server certificate with the fully qualified DNS name of the failover clustered instance on all nodes in the failover cluster. For example, if you have a two-node cluster, with nodes named test1.your company.com and test2. your company.com and a failover clustered instance of SQL Server named fcisql, you must obtain a certificate for fcisql.your company.com and install the certificate on both nodes. To configure the failover cluster for encryption, you can then select the ForceEncryption check box on the Protocols for  property box of SQL Server Network Configuration

First for an AG you need to Force Protocol Encryption on each instance participating in the AG.  With an FCI there is only one instance.

Also with an AG clients can connect to the AG Listener, and they can connect to the instances directly.  So you have to configure the certificates accordingly.  See https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/listeners-client-connectivity-application-failover#SSLcertificates

Note, that this configuration is not required to have protocol encryption.  It's required to authenticate the SQL Server to the clients.  If you merely want protocol encryption, clients may always request it, or you can force it on the server with a self-signed certificate.


mysql combine tables I have three tables which has the same schema. all of them has 10000000+ rows and has unique data as well as duplicate keys but with a datetime column can differentiate them and get the newest row.

I want to create a one combine table with all the data in my three tables updated with the newest records for duplicate values.

Suggest me a method to achieve this.

The UNION command should be your friend: http://dev.mysql.com/doc/refman/5.1/en/union.html

With UNION you can combine several SELECT results into one result. All selects must have the same field structure. In the MySQL docu there is a good example for your problem:

(SELECT a FROM t1 WHERE a=10 AND B=1 ORDER BY a LIMIT 10)
UNION
(SELECT a FROM t2 WHERE a=11 AND B=2 ORDER BY a LIMIT 10);

You create a select for each of your tables with all filter and sorting you need and combine them with UNION to one select. The result of it can be stored by INSERT INTO ... SELECT ... in an archive table. You can also store this query in a MySQL view to have live data of all three tables with one select - Depending on the execution time / indexes etc.


how can I create an where in query that only check on field and ignore another fields? query is something like this:
but I can not figure out how to get phones from inner select and ignore c field.

select * from users where phone in
(
            SELECT phone, COUNT( id ) AS c
            FROM users
            GROUP BY phone
            HAVING c >1
) and verified=true

I know I can do this like this:

select * from users where phone in
    (
        select t1.phone from (
                SELECT phone, COUNT( id ) AS c
                FROM users
                GROUP BY phone
                HAVING c >1) t1
    ) and verified=true

But I think it should be cleaner way.

You could always do an inner join if you are looking for cleaner syntax.  Something like this?  But I wouldn't worry about it too much as long as the query is legible and the performance difference should be negligible.

SELECT U.*
FROM users AS U
    INNER JOIN (
                SELECT T.phone
                    , COUNT(T.id) AS c
                FROM users AS T
                GROUP BY T.phone
                HAVING c > 1
                ) AS PhoneCount ON PhoneCount.phone = U.phone
WHERE U.verified=true;


Would this INSERT statement work? Errata in textbook? I was taking a practice test for Microsoft Certification 70-461 and wanted to confirm if what I saw was an error (this is from the official Microsoft Press book, Ninth printing. I did not see this on existing errata).

  Question:

  You are adding tables to the Hovercraft database. You create
  a table by using the following statement:

  Create Table Hovercraft.Testing
  ( TestIdentifier uniqueidentifier
  DEFAULT NEWSEQUENTIALID() PRIMARY KEY,
  HullId tinyint NOT NULL,
  TestDate date NOT NULL,
  Registration nvarchar(8) NOT NULL,
  Notes nvarchar(max)
  )

  Which of the following statements can you use to insert data into this table? (Each correct answer presents a complete solution. Choose two.)

The choices are:

INSERT INTO Hovercraft.Testing (Notes, HullID, TestDate, Registration,)
 VALUES (N'Hovercraft requires maintenance.', 142, '20120715', N'AAA320')
INSERT INTO Hovercraft.Testing (HullID, TestDate, Registration, Notes)
 VALUES (124, '20120722', N'AAA321', N'Hovercraft in good condition. Next service in six months' time')
INSERT INTO Hovercraft.Testing (HullID, TestDate, Registration, Notes)
 VALUES (247, '20120722', N'ABCD54321', N'Hovercraft in good condition. Next service in three months' time')
INSERT INTO Hovercraft.Testing (Notes, HullID, TestDate, Registration,)
 VALUES (N'Hovercraft requires maintenance.', 842, '20120709', N'AAB121')

The answer key claims the first two are correct. However, shouldn't the additional apostrophe in the word "months" cause an error in the statement?

I apologize in advance if this isn't the right forum to confirm this.

You are correct, the second would fail due to the extra quote. This may be just a misprint caused by an over-aggressive grammar checker, or indeed it could be an apostrophe rather than a quote, and the font delineation is difficult to determine.

The first one will succeed.
The second one would fail due to the single quote (if it's a quote)
The third one would fail due to registration being longer than 8 characters (and the quote, if it's a quote)
The fourth one would fail as the HullId is out of range for a tinyint

Side note: why they would put a Hovercraft schema in the Hovercraft database is beyond me, and a great example of nonsensical design.


creating a trigger after update exclude a column? I want to create a trigger that set a column to to_sync true if the other columns has been changed.

CREATE TRIGGER [dbo].[product_trig]
  ON dbo.product
  AFTER UPDATE
    SET NOCOUNT ON;
    IF NOT UPDATE (to_sync)
      BEGIN
          UPDATE dbo.product
              SET  to_sync  = 'TRUE'
          FROM dbo.product INNER JOIN INSERTED i ON dbo.product.NAME = i.NAME
      END
END

the above code  raise an error:

Msg 156, Level 15, State 1, Procedure mat_trig, Line 4
Incorrect syntax near the keyword 'SET'.
Msg 140, Level 15, State 1, Line 1
Can only use IF UPDATE within a CREATE TRIGGER statement.
Time: 0.504s

I believe your code is missing or is typo'ed

CREATE TRIGGER [dbo].[product_trig]
  ON dbo.product
  AFTER UPDATE
AS -- you miss this 'as'
BEGIN -- you miss this 'begin'
    SET NOCOUNT ON;
    IF NOT UPDATE (to_sync)
      BEGIN
          UPDATE dbo.product
              SET  to_sync  = 'TRUE'
          FROM dbo.product INNER JOIN INSERTED i ON dbo.product.NAME = i.NAME
      END
END


Searching transaction logs for changes I currently have a database with an attached CMS. The CMS has about 50+ people with various permission levels that can log in etc... I would like to know if there is a way I can look at the transaction logs (taken every 30 minutes) and see where a particular value was updated on a bit switch. I can then cross reference it with the person who was logged in at the time so I am fine on that part. If someone can guide me on how to read the transaction log to find the specific update to table x that would be a huge help.

If you're using MS SQL Server you're much better off downloading a tool like this instead of trying to figure out how to go through the entire transaction log and use those functions.  It has a 14 day free trial and should make it a lot more readable for you.

If you end up having to do that check out this thread on DBCC LOG: