Migration MySQL to Cloud Environment replication I'm having 5 MySQL database server , these  5 server are used for e-business web-server. Now I Would like to move the MySQL database to cloud environment for database continuous running. Recently we face hardware failure (misc), in future we need to avoid any failure the database should continuously up and running that is the main objective

Guys could you suggest best way to proceed this scenario ?

we already done the master & master replication in locally , so can any one recommended to which cloud is best for my query like (AWS , Openstack,SoftlayerCloud ) on it.


Should I add relation id on parent or child table for one to one relation Lets say I have 2 tables product and product_detail they both share one 2 one relation and product_detail can be null so should I add relation id in parent table like this?

CREATE TABLE `product` (
   product_id INT(11) PRIMARY KEY auto_increment,
   ...
   product_detail_id INT(11) DEFAULT NULL,

   FOREIGN KEY (product_detail_id) REFERENCES product_detail(product_detail_id)
);

CREATE TABLE `product_detail` (
   product_detail_id INT(11) PRIMARY KEY auto_increment,
   ...
);

Or should I add it in child table like this

CREATE TABLE `product` (
   product_id INT(11) PRIMARY KEY auto_increment,
   ...
);

CREATE TABLE `product_detail` (
   product_detail_id INT(11) PRIMARY KEY auto_increment,
   ...
   product_id INT(11) NOT NULL,

   FOREIGN KEY (product_id ) REFERENCES product(product_id)
);

When the table is splitted vertically with 1:1 relation where slave sub-table record is optional, the common structure is

CREATE TABLE main ( ... ,
                    slave_id INT NULL,
                    UNIQUE INDEX uidx_slave_id (slave_id),
                    FOREIGN KEY (slave_id) REFERENCES slave (id),
                    ... );

CREATE TABLE slave ( id INT NOT NULL PRIMARY KEY,
                     ... );

This structure gives more simple query when you need main records which have no slave record. In all another cases (when you need data from both tables) this and backward structures are close to equivalent.


Clustered index additional storage Reading some books I found that clustered index not require additional disk space for storing index data, because leaf level of clustered index are data itself.

But, as clustered index is organized as B-tree structure, it require intermediate levels and root level. Where are stored those pages? are they additional index structure? How internal structure on physical level looks like (when speaking about clustered indexes)?

There is some space used for the upper levels of the B-tree (how much depends on how large the table is).  However if you look at a Clustered index and a Non Clustered index of the same data (exact same keys and including all columns of the table in the non clustered index) the upper levels of the B-tree would look very similar (if not the same).  The difference comes when storing the data.  A clustered index is the data in the table where as a non clustered index makes a copy of the data in the table (resulting in double the space taken if you include every column in the table in the index, either in the keys or as included columns).

Because a non clustered index makes a copy of the data it is said to take more space than a clustered index when it comes to the data level.


Easiest way to find the larger version number? I have a table that stores version information in multi-dotted strings, ie '4.1.345.08', '5.0.1.100', etc. I am looking for the quickest way to find out which is larger. Is there a faster, or easier way on the eyes than what I have come up with below? One caveat is that I am 90% sure that we should ONLY find dotted numeric values here, but I can't guarantee that in the future.

DECLARE @str1 nvarchar(max)='15.0.4000.8'
DECLARE @str2 nvarchar(max)='15.6.4000.8'

SELECT
CASE
WHEN PARSENAME(@str1, 4) > PARSENAME(@str2, 4)
   THEN @str1
WHEN PARSENAME(@str1, 4) < PARSENAME(@str2, 4)
   THEN @str2
ELSE CASE
   WHEN PARSENAME(@str1, 3) > PARSENAME(@str2, 3)
      THEN @str1
   WHEN PARSENAME(@str1, 3) < PARSENAME(@str2, 3)
      THEN @str2
   ELSE CASE
      WHEN PARSENAME(@str1, 2) > PARSENAME(@str2, 2)
         THEN @str1
      WHEN PARSENAME(@str1, 2) < PARSENAME(@str2, 2)
         THEN @str2
      ELSE CASE
         WHEN PARSENAME(@str1, 1) > PARSENAME(@str2, 1)
            THEN @str1
         WHEN PARSENAME(@str1, 1) < PARSENAME(@str2, 1)
            THEN @str2
         ELSE NULL
         END
      END
   END
END

A little easier on the eyes perhaps. It relies on the inferences I can make from your sample data (that three of the octets are < 100 and one is > 1000) - you may need to make adjustments to the multipliers to reflect any differences in real data. I doubt performance will be any different, but depending on how often you are running these comparisons, you may consider computed/persisted columns storing the calculations.

DECLARE @str1 nvarchar(max)='15.0.4000.8',
        @str2 nvarchar(max)='15.6.4000.8';

DECLARE @i1 BIGINT = 10000000000 * PARSENAME(@str1, 4)
    + 1000000 * PARSENAME(@str1, 3)
    + 10 * PARSENAME(@str1,2)
    + PARSENAME(@str1, 1),
  @i2 BIGINT = 10000000000 * PARSENAME(@str2, 4)
    + 1000000 * PARSENAME(@str2, 3)
    + 10 * PARSENAME(@str2,2)
    + PARSENAME(@str2, 1);

SELECT @i1, @i2, CASE WHEN @i1 > @i2 THEN @i1 ELSE @i2 END;

Results:

150000040008    150006040008    150006040008


SQL Query for monitoring exceptions between parent/child table enter code hereI need help with a query that I want to create. For instance, I have two tables (Process & Article).

Article is the parent table what has a unique article number and in the Process table there are processes created under an article number.

I want to create a query to monitor instances when an Article is Active and ALL the Process status of that article number is Inactive

Article Table

ArticleTitle                   ArticleNumber   ArticleStatus
-------------------------------------------------------------
 Expense Report                       1          Active
 Inbox Monitor                        2          Active
 Daily finance report                 3          Active
 Order Audit                          4          Active
 Order Tracking                       5          Inactive

Process Table

 ProcessTitle                   ArticleNumber   ProcessStatus
-------------------------------------------------------------
 Expense Report CPA                   1          Active
 Expense Report BG                    1          Active
 Inbox Monitor db                     2          Active
 Weekly finance report CPA            3          Inactive
 Weekly finance report BG             3          Inactive
 Order Audit for xyz                  4          Active
 Order Audit for abc                  4          Inactive

 Order Tracking                       5          Inactive

Test Data

CREATE TABLE dbo.Article([Article Title] NVARCHAR(50),
                         [Article Number] INT,
                         [Article Status] NVARCHAR(20));

INSERT INTO dbo.Article([Article Title],
                        [Article Number],
                        [Article Status])
VALUES
('Expense Report',1,'Active'),
('Inbox Monitor',2,'Active'),
('Daily finance report',3,'Active'),
('Order Audit',4,'Active'),
('Order Tracking',5,'Inactive');

CREATE TABLE dbo.Process([Process Title] nvarchar(50),
                         [Article Number] int,
                         [Process Status] nvarchar(20));

INSERT INTO  dbo.Process([Process Title],
                         [Article Number],
                         [Process Status])
VALUES
('Expense Report CPA',1,'Active'),
('Expense Report BG',1,'Active'),
('Inbox Monitor db',2,'Active'),
('Weekly finance report CPA',3,'Inactive'),
('Weekly finance report BG',3,'Inactive'),
('Order Audit for xyz',4,'Active'),
('Order Audit for abc',4,'Inactive'),
('Order Tracking',5,'Inactive');

Query

SELECT *
FROM dbo.Article A
WHERE [Article Status] = 'Active'
AND NOT EXISTS
(SELECT * FROM dbo.Process P WHERE  P.[Article Number] = A.[Article Number]
                                    AND P.[Process Status] != 'Inactive');

Result

Article Title         Article Number    Article Status
Daily finance report  3                 Active


Do SQL Server Views update their data types automatically based on what tables feed them? Someone mistakenly created a bunch of nchar fields in database tables.
I noticed this, and after examining the issue, we're moving these fields to nvarchar types and trimming the values inside.

In my script, I currently have it limited to pull only the tables that have nchar types, assuming that the views that are fed by the tables will know to update their values. Is this assumption correct, or should I be including views in this list as well?

For reference, the script in its current form is below (mostly taken from this great SO answer):

declare @tn nvarchar(128)
declare @cn nvarchar(128)
declare @ln int

declare @sql as nvarchar(1000)

declare c cursor for
    select cols.table_name,cols.column_name,cols.character_maximum_length
    from information_schema.columns cols
        inner join information_schema.tables tabs
            on (cols.TABLE_SCHEMA = tabs.TABLE_SCHEMA and cols.TABLE_NAME = tabs.TABLE_NAME)
    where cols.data_type ='nchar' and tabs.TABLE_TYPE = 'BASE TABLE'

open c
fetch next from c into @tn, @cn, @ln

while @@FETCH_STATUS = 0
begin

    set @sql = 'alter table ' + @tn + ' alter column '
        + @cn + ' nvarchar(' + convert(nvarchar(50), @ln) + ')'
    exec sp_executesql @sql

    set @sql = 'update ' + @tn + ' set ' + @cn + ' = LTRIM(RTRIM(' + @cn + '))'
    exec sp_executesql @sql

    fetch next from c into @tn, @cn, @ln
end

close c
deallocate c

References

Convert all NCHAR columns to NVARCHAR columns in a database or table

A simple example:

CREATE TABLE dbo.x(a INT, b NCHAR(4));
GO
CREATE VIEW dbo.vx AS
  SELECT a, b FROM dbo.x;
GO

ALTER TABLE dbo.x ALTER COLUMN a TINYINT;
ALTER TABLE dbo.x ALTER COLUMN b NVARCHAR(4);
GO

SELECT a,b INTO #blat FROM dbo.vx;
GO

EXEC tempdb.dbo.sp_columns N'#blat';
GO

DROP VIEW dbo.vx;
DROP TABLE dbo.x, #blat;

Partial output:

COLUMN_NAME    TYPE_NAME    PRECISION
-----------    ---------    ---------
a              tinyint      3
b              nvarchar     4

So, in practical terms, yes, any interaction with the view should yield the new data types.

That said, I would always call sp_refreshview for any views that reference a table that has changed (and in fact I often use WITH SCHEMABINDING so that I can't alter a table without knowing about the views and other objects it affects - this can make cowboy development/deployment painful, but I guess that's kind of the point).

You can build the script to refresh all referenced views dynamically like this (this is for a single table; you'll need to incorporate this into your existing script to make it dynamic for all affected tables):

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql += N'EXEC sp_refreshview '''
  + QUOTENAME(s.name) + '.' + QUOTENAME(v.name) + ''';'
FROM sys.sql_expression_dependencies AS d
INNER JOIN sys.views AS v
ON d.referencing_id = v.[object_id]
INNER JOIN sys.schemas AS s
ON v.[schema_id] = s.[schema_id]
WHERE d.referenced_id = OBJECT_ID('dbo.whatever')
GROUP BY s.name, v.name;

EXEC sp_executesql @sql;

Another reason you should always refresh all views after changing the base table(s), especially if your views use SELECT *:

CREATE TABLE dbo.x(a INT, b NCHAR(4));
GO
CREATE VIEW dbo.vx AS
  SELECT * FROM dbo.x;
GO

SELECT * INTO #b1 FROM dbo.vx;
GO

ALTER TABLE dbo.x ALTER COLUMN a TINYINT;
ALTER TABLE dbo.x ALTER COLUMN b NVARCHAR(4);
GO

SELECT * INTO #b2 FROM dbo.vx;
GO

ALTER TABLE dbo.x ADD d INT;
GO

SELECT * INTO #b3 FROM dbo.vx;
GO

EXEC sp_rename N'dbo.x.b', N'c', N'COLUMN';
EXEC sp_rename N'dbo.x.d', N'b', N'COLUMN';
GO

SELECT * INTO #b4 FROM dbo.vx;
GO

EXEC tempdb.dbo.sp_columns N'#b1';
EXEC tempdb.dbo.sp_columns N'#b2';
EXEC tempdb.dbo.sp_columns N'#b3';
EXEC tempdb.dbo.sp_columns N'#b4';
GO

DROP TABLE #b1, #b2, #b3, #b4;
GO

Partial results:

-- initial:

#b1____    a    int        10
#b1____    b    nchar      4

-- correct:

#b2____    a    tinyint    3
#b2____    b    nvarchar   4

-- missing new column d:

#b3____    a    tinyint    3
#b3____    b    nvarchar   4

-- missing column c, b still points at "old" b:

#b4____    a    tinyint    3
#b4____    b    nvarchar   4

But then if we refresh the view:

EXEC sp_refreshview N'dbo.vx';
GO

SELECT * INTO #b5 FROM dbo.vx;

EXEC tempdb.dbo.sp_columns N'#b5';

DROP VIEW dbo.vx;
DROP TABLE dbo.x, #b5;

Results:

#b5____    a     tinyint     3
#b5____    c     nvarchar    4
#b5____    b     int         10

Note that the data types are correct now, but the columns are not in the order you would expect.


The possibility to define a composite unique constraint for specific cases I have two tables: user and address.
User table:

id
email
name

1
henk@example.com
Henk

2
john@example.com
John

Address table:

id
user_id
street
is_default

1
1
Super cool street
1

2
1
Beautiful avenue
0

3
1
Some street
0

4
2
Damn cool street
0

5
2
Cool street
1

6
2
My street
0

The problem
Image someone setting two default addresses for a user; it would be non-sensical from the Business logic perspective.
The question
I want to create a unique constraint composed by user_id and is_default but only for a specific case, which is that the constraint must be enforced when is_default is 1.
Is this possible with mysql?

Add a foreign key column in the user table that references the primary key of the address table. Call it default_address_id. This enforces uniqueness because it can have only one value at a time for a given user.
Then drop the is_default column in your address table because it risks disagreeing with the foreign key in the user table.


Sql Server 2008 to R2 Restore Backup from R2 I need to restore SQL Server 2008 R2 Backup to SQL Server 2008, but as you know, it's not possible. So, I decided to upgrade my SQL 2008 to R2 but I don't know which instance to select  in Instance to upgrade that contains ("MSSQLSERVER","SQLEXPRESS","<<Upgrade shared features only>>"

Most likely the first one "MSSQLSERVER".  Thats the default name for a new installation.  The express one is the free version and limits your DB size to 10GB.  The 3rd one isn't an instance, just shared components so ignore that one.

You should verify by logging into the server from SSMS and typing in SELECT @@SERVERNAME first to verify.  If it only returns the server name, then the MSSQLSERVER is the right instance.  If it returns ServerName\Express then you're on the express edition.


Practical Aspects of Making Changes to Databases I am setting up my first database and have gone through the standard process of studying the business logic, figuring out the entities, associated fields and dependencies.

This is the theoretical/classroom side, which is useful, but maybe does not give the full picture. I'm trying to get advice from those with practical experience in making changes to existing databases, i.e., adding/deleting fields to a table and adding/updating tables to the database.

How is this done; how are dependency issues managed?
Do we use auxiliary programs like Apex, or is this done in other ways?
How hard is it in actuality to change a database?

EDIT: My apologies for not making this post more specific; I am caught in a sort-of catch-22: Since I don't know enough, I cannot ask good, specific questions, and since I can't ask good questions, I can't learn enough.

A really important thing to remember about SQL databases is that architecture is king, it governs how difficult everything else is, from querying to modifying its structures later. Business requirements will sometimes force you to avoid what is traditionally best practice or break a particular design pattern. It's okay that it happens, but certain patterns can make your life a lot easier.

For the most part we rely on using traditional development tools to manage our database design manually. I personally work almost exclusively in Visual Studio and SQL Server Management Studio for MS SQL Server. We use VS to manage our SQL Server project within our larger product code base, utilize our TFS Source Control, and use the comparison tools to generate patch scripts. I do most of my scratch pad development, testing, and management of our local development DBs via SSMS. For 3rd party tools I prefer Redgate's SQL Prompt and SQL Search, not particularly cheap though. They have some other cool toys, but those are the only two I use every single day; to the point that I feel sluggish without SQL Prompt. Though the latest version of SSMS seems to have improved its intelli-sense to almost the same level of SQL Prompt.

Adding to a database structure (especially in a well designed relational model) is almost always easier than modifying or removing something from said structure. Creating new tables and relationships doesn't pose a risk to any of your existing data or queries since it's all additive. Worst case you'll have to populate some of those new tables with existing data. On the other hand modifying or deleting existing columns and tables may require you to migrate data or modify queries to maintain data integrity. It'll be tempting to be lazy about this sometimes, don't be! You'll thank yourself later.

I personally believe good database design is a matter of keeping things relatively simple and adapting when necessary: using Foreign Keys, naming columns after their FK target field, maintaining constraints, indexing high volume tables, relying heavily on INNER JOIN relations, and avoiding 'negative' logic (NULLs, NOTs, implicit relationships, ORs, etc...) when possible can make your life a lot easier to maintain your database over time and will get you most of the way through most projects. We easily spend 25-50% of our time undoing 'bad' design decisions or lazy workarounds from previous and less experience developers.

Unfortunately this is only a very high level view, we could talk about specific topics for days. I would definitely recommend poking around various blogs and picking up a book or two to get a grasp of some of the things I've mentioned above.


What does 'ibfk' stand for in MySQL? If I create a foreign key constraint for table 'photos' in phpmyadmin, I later see that the constraint is named 'photos_ibfk_1', and the next constraint is called 'photos_ibfk_2', etc. From this I have gathered that [tablename]_ibfk_constraintIndex is the convention for DB constraints in MySQL. Is this correct? What does IBFK stand for?

innodb foreign key.  It's just short hand naming convention.  You could call it asdfqwerty and the name would still work.


Is there a way to make this select retrieve the same results with a single seek? Is it possible to retrieve the same data as the following with a single seek or scan, either by modifying the query or influencing the optimizer's strategy?

Code and schema similar to this are currently on SQL Server 2014.

Repro script. Setup:

USE tempdb;
GO
IF OBJECT_ID('dbo.TestUpload', 'U') IS NOT NULL
  DROP TABLE dbo.TestUpload;

CREATE TABLE dbo.TestUpload(
    JobRunId bigint NOT NULL,
    ThingAName nvarchar(255) NOT NULL,
    ThingAType nvarchar(255) NOT NULL,
    ThingAGranularity nvarchar(255) NOT NULL,
    ThingBName nvarchar(255) NOT NULL,
    ThingBType nvarchar(255) NOT NULL,
    ThingBGranularity nvarchar(255) NOT NULL
);
CREATE CLUSTERED INDEX IX_JobRunId ON dbo.TestUpload (JobRunId);

GO

INSERT INTO dbo.TestUpload (JobRunId, ThingAName, ThingAType, ThingAGranularity, ThingBName, ThingBType, ThingBGranularity)
  VALUES (1, 'A', 'B', 'C', 'D', 'E', 'F');
GO 10

INSERT INTO dbo.TestUpload (JobRunId, ThingAName, ThingAType, ThingAGranularity, ThingBName, ThingBType, ThingBGranularity)
  VALUES (1, 'D', 'E', 'F', 'A', 'B', 'C');
GO 10

Query:

DECLARE @JobRunID bigint = 1;

SELECT JobRunId,
  ThingAName AS Name,
  ThingAType AS [Type],
  ThingAGranularity AS Granularity
FROM dbo.TestUpload
WHERE JobRunId = @JobRunID
UNION
SELECT JobRunId,
  ThingBName AS Name,
  ThingBType AS [Type],
  ThingBGranularity AS Granularity
FROM dbo.TestUpload
WHERE JobRunId = @JobRunID;

Tear down:

IF OBJECT_ID('dbo.TestUpload', 'U') IS NOT NULL
  DROP TABLE dbo.TestUpload;

I think this is probably not modeled ideally. I'm trying to get more information from the developer about how the schema was chosen but am curious if there's a TSQL trick I'm overlooking as it will be easier to change the query than the schema.

I'd try this but I have no idea if it will be more efficient. You need the DISTINCT to remove duplicates, so the UNION ALL might be more appropriate, no need for two distinct operations:

SELECT DISTINCT
    JobRunId = @JobRunID,
    d.*
FROM dbo.TestUpload
  CROSS APPLY
    (   SELECT
          ThingAName AS Name,
          ThingAType AS [Type],
          ThingAGranularity AS Granularity
      UNION                            -- or UNION ALL
        SELECT
          ThingBName,
          ThingBType,
          ThingBGranularity
    ) AS d
WHERE JobRunId = @JobRunID ;

UNION ALL plan:

UNION plan:


Security implications of restoring a backup from an unknown source? Scenario: You're handed a database backup and told to restore it to a server (that's already hosting other databases), but are given no useful information about what the backup contains or whether the source should be trusted.

Question 1: What are the potential implications of restoring a backup that could well be malicious?

Question 2: What can you do to protect your server/the data in other databases from the impact of restoring a potentially-malicious backup? RESTORE VERIFYONLY would seem to be a good first step. The ultimate answer is probably 'restore the database in a sandbox VM with no access to the outside world', but let's assume that option is off the table. What else should be done in this situation?

A database may contain malicious code, possibly a procedure that is going to change a password for the "sa" login or drop every database. However the only way that I can see that causing an issue is for an individual to restore the database, and then manually execute any code within that database. It would not execute in any automated manner.

There is no setting that can be applied within a database to have SQL Server automatically execute some bit of code within the database upon restoring it to a server. If it did I would expect Microsoft would loose the Common Criteria certification for the product. That is to great of a bug to have allowed in a DBMS to me.


Defining a column as nullable in a view (but not in the referenced table) My company is working on implementing a new HR application that will unify our existing HR processes. We've run into a bit of a sticking point, though; according to business rules, the EmployeeID and SupervisorID columns in the Employee table cannot be null. However, the application requires these columns to be nullable, and won't synchronize the columns while they're set to disallow null.

The proposed solution was to create a view where the schema appeared to allow null values in a column, while leaving the table alone to prevent them from being entered into the data. I'm not entirely convinced this is possible, and I haven't been able to find a way to do it. Is this possible? If not, what is the best way to handle this situation?

Yes, it's possible:

select Id, -- Remains as in table
  cast(MandatoryIntCol as int) as [MandatoryIntCol], -- Makes nullable
  isnull(OptionalIntCol, 0) as [OptionalIntCol] -- Makes mandatory
from dbo.MyTable;

It's worth noting that there are some restrictions with this approach:

The changed columns are not writeable in the view, so if the OP's application tries to write to them, it would fail.
The application must not try to distinguish between tables and views. Most apps do not, but annoyingly, some product applications do.


Get the average of the last X records for each foreign key My table is a list of yacht handicaps for yacht racing. After each race a new handicap is calculated based on how the yacht performed which is added to the table. The actual handicap used to modify the finish time of a yacht is the average of the last 3 races. I am having trouble getting my head around the sql to retrieve the race modifier of all yachts.

My table data (I have removed extraneous columns):

id | hcpval | yacht_id
----------------------
 1 | 0.9    |  1
 2 | 0.75   |  2
 3 | 0.84   |  1
 4 | 0.71   |  2
 5 | 0.88   |  1
 6 | 0.73   |  2
 7 | 0.81   |  1
 8 | 0.7    |  2

Obviously, the most recent results have the higher id. After the query has finished I'm hoping to get the following results:

yacht_id | hcpval
-----------------
    1    | 0.843 - avg of ids 7,5 & 3
    2    | 0.713 - avg of ids 8,6 & 4

You need to find the three latest rows. That is typically done using a window function. The result of that can then be aggregated:

select yacht_id,
       avg(hcpval) as hcpval
from (
  select yacht_id,
         hcpval,
         row_number() over (partition by yacht_id order by id desc) as rn
  from handicaps
)  t
where rn <= 3
group by yacht_id
order by yacht_id;

Online example: https://rextester.com/DDTU17483


SQL Server DROP Database which is in a long roll back process I have a DataBase that is taking very long to rollback a transactions.
Which could be normally because it is rolling back a hugh amount of data.

However I have the scripts to create the DB again and just want to drop the DB so that this roll back stops...

How can I achieve this?

thanks already!

kind regards

You're just going to need to wait, unless you want to risk corrupting other databases by, say, yanking the power cord out of the wall. You can set your other user databases offline before attempting such a stunt, but good luck with system databases.

For a rollback you should be able to guesstimate how much longer you'll need to wait by running this query:

SELECT
  percent_complete,
  [est_finish] = DATEADD(MILLISECOND,[estimated_completion_time],CURRENT_TIMESTAMP)
FROM sys.dm_exec_requests
WHERE command LIKE N'%ROLLBACK'; -- could be KILLED/ROLLBACK

I blogged about this here (and it includes a much more exhaustive query):

When will my backup / restore / index reorganize finish?

If you know what operation is rolling back, you may also be able to infer some guesses on progress by looking at the columns reads,writes and logical_reads - but don't spend too much time on that, since I/O is what percent_complete is based on, so you'll probably come to roughly the same conclusion.

Still, this is just information that will help you set expectations about how long you need to wait. There isn't a very safe way to cancel a rollback.


Help writing the following query to relational algebra Can someone help me in writing the following query to relational algebra, I am studying DBMS and  I am stuck on this exercise.

Query:

SELECT a.name, d.name. d.grade
FROM student a, enrolled i, class d
WHERE a.id= i.student_id
AND i.disc_id = d.id

Thanks

This line represents a cartesian product of 3 tables:

FROM student a, enrolled i, class d

After creating a cartesian product you filter out rows that match the predicate.

WHERE a.id= i.student_id
AND i.disc_id = d.id

If you look closer at this query it is equivalent to:

  SELECT a.name, d.name. d.grade
    FROM student a
      JOIN enrolled i ON a.id= i.student_id
      JOIN class d ON i.disc_id = d.id

Maybe you should analyze the second query.


Duplicate UNIQUE KEY error after ROLLBACK in MySQL I have a table like this

mysql> describe seudonimos;
+--------------+------------------+------+-----+---------+----------------+
| Field        | Type             | Null | Key | Default | Extra          |
+--------------+------------------+------+-----+---------+----------------+
| id_seudonimo | int(11) unsigned | NO   | PRI | NULL    | auto_increment |
| seudonimo    | varchar(45)      | NO   | UNI | NULL    |                |
+--------------+------------------+------+-----+---------+----------------+
2 rows in set (0.02 sec)

Let's assume that it is empty so autoincrement is 0. For example:

SET AUTOCOMMIT=0;
BEGIN TRANSACTION;
INSERT INTO seudonimos (seudonimo) VALUES ('Agatha Christie');
ROLLBACK;
SET AUTOCOMMIT=1;

As far as I konw, the rollback does not affect the autoincrement. So if I insert a new value the autoincrement will be 2 instead of 1. But if I try to insert 'Agatha Christie' again, I have the following problem:

INSERT INTO seudonimos (seudonimo) VALUES ('Agatha Christie');

#1062 - Duplicate entry 'Agatha Christie' for key 'seudonimo'

That is not what I expected. I expected this:

+--------------+--------------------+
| id_seudonimo | seudonimo          |
+--------------+--------------------+
|            2 | Agatha Christie    |
+--------------+--------------------+

What's wrong?

In one of your comments to your own question, you're saying you're using MyISAM.

However, MyISAM does not support transactions (see ref. table). Therefore, it will always autocommit, whether you try to turn it off or not.

If you want to use transactions, you need to use an engine that supports it, such as InnoDB.

EDIT (following comments and additional information -- I must admit I hadn't realised you were also using a second table until @RolandoMySQLDBA pointed it out):

As the documentation says: "If you use tables that are not transaction-safe within a transaction, changes to those tables are stored at once, regardless of the status of autocommit mode."

Since a second table (seudonimos_consulta, using MyISAM) is involved in the transaction, via a trigger, what's inserted after the first INSERT in that table isn't rolled back. Hence, #1062 - Duplicate entry 'Agatha Christie' for key 'seudonimo' would refer to the second table, seudonimos_consulta.seudonimo, not seudonimos.seudonimo.


Extended event for lock count does not show locks I recently came across the article Locking and Performance. It has following statements

If we manually monitor locking activity while this query executes, we see that without the page locking granularity hint, SQL Server acquires and releases over half a million row level locks while seeking the clustered index.
The performance of this query can be improved to 320 ms simply by adding a page granularity locking hint to the Sales table

I am using SQL Server 2012. For identifying the locks, I referred MSDN: How to: Find the Objects That Have the Most Locks Taken on Them. And I used a workload query that joins two tables and selects   1001000 rows. Still it is not showing any locks in the output. It is hard to believe that there is no lock happening.

Why is it not showing any locks? How to correct this?
Is there a better way to identify the type and count of locks?

References - Updated:

Which Locks Count Toward Lock Escalation? - Kendra Little

Use Extended Events to capture Deadlocks in SQL Server

Tracking SQL Server Database Usage - Jonathan Kehayias

Locking and Blocking (2) – Shared Locks and Lock Resource

View locks acquired during query execution (SQL Server) and  beta_lockinfo

Using Extended Events to Find Blocks in SQL Server 2012

Determine Which Queries Are Holding Locks

Extended Event with my Workload
IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='LockCounts')
DROP EVENT session LockCounts ON SERVER
GO
DECLARE @dbid int

SELECT @dbid = db_id('My_DW')

DECLARE @sql nvarchar(1024)
SET @sql = '
CREATE event session LockCounts ON SERVER
ADD EVENT sqlserver.lock_acquired (WHERE database_id =' + CAST(@dbid AS nvarchar) +')
ADD TARGET package0.synchronous_bucketizer (
SET filtering_event_name=''sqlserver.lock_acquired'', source_type=0, source=''resource_0'')'

EXEC (@sql)
GO
ALTER EVENT session LockCounts ON SERVER
STATE=start
GO

----------------------------- Create a simple workload that takes locks.------------------------------------------------
-- ---------------------------------------------------------------------------------------------------------------------
USE My_DW
GO

SELECT TOP 1001000 *
FROM Fact_Appointment F
INNER JOIN Dim_AppointmentType D
    ON  F.AppointmentType_Key = D.AppointmentType_Key

GO

-------------------------------------------------------------------------------------------------------------------------
SELECT name, object_id, lock_count FROM
(SELECT objstats.value('.','bigint') AS lobject_id,
objstats.value('@count', 'bigint') AS lock_count
FROM (
SELECT CAST(xest.target_data AS XML)
LockData
FROM sys.dm_xe_session_targets xest
JOIN sys.dm_xe_sessions xes ON xes.address = xest.event_session_address
JOIN sys.server_event_sessions ses ON xes.name = ses.name
WHERE xest.target_name = 'synchronous_bucketizer' AND xes.name = 'LockCounts'
) Locks
CROSS APPLY LockData.nodes('//BucketizerTarget/Slot') AS T(objstats)
 ) LockedObjects
INNER JOIN sys.objects o
ON LockedObjects.lobject_id = o.object_id
--WHERE o.type != 'S' AND o.type = 'U'
ORDER BY lock_count desc
GO
--
-- Stop the event session.
--
ALTER EVENT SESSION LockCounts ON SERVER
state=stop
GO

You mentioned that you are testing in SQL 2012 I suggest you change you query for SQL 2012. You are using the version for 2008R2.

Pay attention to these lines.

WHERE xest.target_name = 'histogram' AND xes.name = 'LockCounts'
) Locks
CROSS APPLY LockData.nodes('//HistogramTarget/Slot') AS T(objstats)

Full test code that is working for me.

-- Find objects in a particular database that have the most
-- lock acquired. This sample uses WideWorldImporters .
-- Create the session and add an event and target.
--
IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='LockCounts')
DROP EVENT session LockCounts ON SERVER
GO
DECLARE @dbid int

SELECT @dbid = db_id('WideWorldImporters')

DECLARE @sql nvarchar(1024)
SET @sql = '
CREATE event session LockCounts ON SERVER
ADD EVENT sqlserver.lock_acquired (WHERE database_id =' + CAST(@dbid AS nvarchar) +')
ADD TARGET package0.histogram(
SET filtering_event_name=''sqlserver.lock_acquired'', source_type=0, source=''resource_0'')'

EXEC (@sql)
GO
ALTER EVENT session LockCounts ON SERVER
STATE=start
GO
--
-- Create a simple workload that takes locks.
--
USE WideWorldImporters
GO
SELECT TOP 1 * FROM sales.Orders
GO
-- The histogram target output is available from the
-- sys.dm_xe_session_targets dynamic management view in
-- XML format.
-- The following query joins the bucketizing target output with
-- sys.objects to obtain the object names.
--
SELECT name, object_id, lock_count FROM
(SELECT objstats.value('.','bigint') AS lobject_id,
objstats.value('@count', 'bigint') AS lock_count
FROM (
SELECT CAST(xest.target_data AS XML)
LockData
FROM sys.dm_xe_session_targets xest
JOIN sys.dm_xe_sessions xes ON xes.address = xest.event_session_address
JOIN sys.server_event_sessions ses ON xes.name = ses.name
WHERE xest.target_name = 'histogram' AND xes.name = 'LockCounts'
) Locks
CROSS APPLY LockData.nodes('//HistogramTarget/Slot') AS T(objstats)
 ) LockedObjects
INNER JOIN sys.objects o
ON LockedObjects.lobject_id = o.object_id
WHERE o.type != 'S' AND o.type = 'U'
ORDER BY lock_count desc
GO
--
-- Stop the event session.
--
ALTER EVENT SESSION LockCounts ON SERVER
state=stop
GO

I also tested this as target to a file.  You can then save it in a database table and query/aggregate to your need.

CREATE EVENT SESSION [CaptureLocks] ON SERVER
ADD EVENT sqlserver.lock_acquired(
    ACTION(sqlserver.database_id,
           sqlserver.database_name,
           sqlserver.server_instance_name,
           sqlserver.session_id)
    WHERE ([package0].[equal_uint64]([database_id],(5))
           AND [object_id]>(0)))
ADD TARGET package0.event_file(SET filename=N'C:\CaptureLocks.xel')
WITH (MAX_MEMORY=4096 KB,
      EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
      MAX_DISPATCH_LATENCY=30 SECONDS,
      MAX_EVENT_SIZE=0 KB,
      MEMORY_PARTITION_MODE=NONE,
      TRACK_CAUSALITY=OFF,
      STARTUP_STATE=OFF)
GO

Sample query to look at the data saved in table.

SELECT OBJECT_NAME([object_id]) AS [objectName],
       resource_type,
       mode,
       Count(0) AS [LockCounts]
FROM [WideWorldImporters].[dbo].[CollectLocks]
GROUP BY [object_id], resource_type, mode

Adding this section based on the question asked in comment section.
Reference: https://docs.microsoft.com/en-us/sql/relational-databases/event-classes/lock-acquired-event-class

Mode column will give you lock type. See above link for details.

I captured another trace with paglock and rowlock hint.  resource_type column will capture the resource type (table/page/row).

Exact query I ran was:

SELECT TOP (1) * FROM sales.orders WITH (PAGLOCK)
GO
SELECT TOP (1) * FROM sales.orders WITH (PAGLOCK)
GO

Sample form live data window.


How to see resumable timeout of another session in oracle? How I see the setting of timeout about resumable of another session? I've found the function dbms_resumable.get_timeout, but there is no way of inform some parameters about a specified session.

DBA_RESUMABLE

  ...

  TIMEOUT | NUMBER | Timeout of the resumable statement

  ...

Session 1:

SQL> select sys_context('userenv', 'sid') from dual;

SYS_CONTEXT('USERENV','SID')
--------------------------------------------------------------------------------
53

SQL> alter session enable resumable timeout 600 name 'hello world';

Session altered.

Session 2:

SQL> select session_id, timeout, name from dba_resumable;

SESSION_ID    TIMEOUT NAME
---------- ---------- ------------------------------
        53        600 hello world


How to copy auto-increment id value from one table to another? I have a database with two tables, A and B.

Table A contains the title of a book and has an auto-increment column, book_id, that I declared as primary key.

In table B, I want to retain the price of the book having the same book_id value. In this case, the book_id column will be constrained as a foreign key and I dont want to auto-increment it.

How do I do it using PHP?

Your schema doesn't even make sense. You need to have an authoritative bookid somewhere.

CREATE TABLE book (
  bookid  serial PRIMARY KEY
  -- stuff
);

CREATE TABLE book_title (
  bookid  int REFERENCES book,
  title   text
);

CREATE TABLE book_price (
  bookid  int REFERENCES book,
  price   float
);

That said, if everything is 1:1 with book, just put it all on the same table. You don't need a separate table for every column:

CREATE TABLE book (
  bookid  int serial,
  title   text,
  price   numeric(10,8)
);


Filtering data that could have more than one category I have a table like the following:

Annotation (
    document,
    term,
    category
)

Where document and term are some ID, while category is an integer.

The couple document - term is not unique, i.e. I could have the same couple with a different category.

document_id_1, term_id_1, category_1
document_id_1, term_id_1, category_2
document_id_1, term_id_1, category_3

I would like to design a query such that it return only the couple document-term for whom there exists a only a row with category = 1.

E.g. in the previous example the couple document_id_1 - term_id_1 is not returned becouse there exist also other two rows, with different values of category.

Can you give me some hints on how to do that?

If I understood you correctly, one way to achieve this is getting those rows where category = 1, then check for the non-existence of non-category1 rows:

SELECT document, term
FROM Annotation ann
WHERE
    category = 1
    AND NOT EXISTS (
        SELECT 1
        FROM Annotation a
        WHERE
            a.document = ann.document
            AND a.term = ann.term
            AND category <> 1
    )
;

(Thanks to DavideChicco.it for showing that this was overly complicated).


MySQL query optimization for legacy application I'm working with a legacy application that started out with little data years ago (developed by a third party custom for this client), and now we're seeing MySQL load skyrocket on this query alone.

The fact that they have actual data now years later is why we're noticing the performance hit at this time. Just not sure what to do with it yet.

I've tried to make sure indexes are correct and what not in the schema, but here's a sample query being generated by the application. Any ideas to help with performance?

SELECT id,
       name,
       phone
FROM   customers
WHERE  ( last_updated >= '2013-03-01'
         AND last_updated <= '2013-03-27 24:00:00'
         AND status = '0'
         AND deadlead = ''
         AND purchased = ''
         AND id != 0 )
        OR ( status = '0'
             AND deadlead = ''
             AND purchased = ''
             AND id IN (SELECT DISTINCT cid
                        FROM   notes
                        WHERE  timestamp >= '2013-03-01'
                               AND timestamp <= '2013-03-27 24:00:00')
             AND id != 0 )

CREATE TABLE IF NOT EXISTS `customers` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(40) NOT NULL,
  `email` varchar(255) NOT NULL,
  `phone` varchar(20) NOT NULL,
  `alt_phone` varchar(255) NOT NULL,
  `tradein` text NOT NULL,
  `purchased` text NOT NULL,
  `deadlead` text NOT NULL,
  `interest` varchar(255) NOT NULL,
  `trailer` varchar(255) NOT NULL,
  `status` binary(1) NOT NULL,
  `options` text NOT NULL,
  `created_on` date NOT NULL,
  `last_updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY `id_2` (`id`),
  KEY `id` (`id`),
  KEY `id_3` (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=8913 ;

CREATE TABLE IF NOT EXISTS `notes` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `cid` int(11) NOT NULL,
  `uid` int(11) NOT NULL,
  `note` text NOT NULL,
  `file` varchar(255) NOT NULL,
  `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `reminder` date NOT NULL,
  `locked` int(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=19308 ;

UPDATED:

This is the output of using EXPLAIN on the query:

SUGGESTION #1

You have no meaningful indexes. That can have a detrimental effect on execution since the MySQL Query Optimizer has no help in searching.

You should all the following indexes

ALTER TABLE customers ADD INDEX bigindex (status,deadlead,purchased,last_updated);
ALTER TABLE notes ADD INDEX timestamp_id_index (timestamp,id);

This may help with some of the searching

SUGGESTION #2

You have duplicate indexes. That can slow down INSERTs and bloat the MYI file.

Please run

ALTER TABLE customers DROP INDEX id,DROP INDEX id3;

SUGGESTION #3

Periodically, perhaps once a week, you should update index statistics

ANALYZE TABLE customers;
ANALYZE TABLE notes;

CAVEAT

There could be other things I could suggest. However, if the amount of data is relatively small, indexing is probably all you need.

Give it a Try !!!


Ola Hallengren for Managed instance How can we configure Ola Hallengren's solution on a managed instance? (We want output file for each run, storing it is an issue)
If we want backup retention longer than 35 days for the database in managed instance, what are the possibilities? (copy_only backups to blob storage?)

Ola Hallengren's maintenance solution has several parts:

Database backups - you don't usually want to use these in Managed Instances because you can't restore the backups to another location, like on-premises. The only use for database backups is to restore them into another managed instance - like if you're doing it for disaster recovery, backing them up to blob storage, and then syncing that blob to a different Azure account (in case you're worried about a malicious actor closing your Azure account, and leaving you with no backups.) If you do want to use backups for that purpose, you can use the @Url parameter for the DatabaseBackup proc, and back up to a URL.

Integrity checks - these work the same way they work for normal SQL Server - no change for Ola's scripts here.

Index and statistics maintenance - these also work the same way they'd work on-premises, but be aware that with the limited transaction log throughput of Azure SQL DB and its relatives, you probably want to use the @Delay parameter when you rebuild/reorg indexes so that you slow down the amount of transaction log throughput you're generating, and leave some DTUs for user transactions.

Parameters common to all procs - don't try to log them to the file system, because you simply don't have access to the file system.


Free universe tool monitoring all database Is there any free tool to monitor most popular databases like Oracle, MySQL, MSSQL, PostgreSQL. I want to monitor the real time performance of all the above mentioned databases with a single tool like DBEAVER. But DBEAVER does not have real time monitoring facility.
Please suggest.
Thanks in advance.

Regards
Mahendra

Not that I'm fond of it, but if you use Nagios, it's rather easy to add some database-related plugins. You could do worse than have a look at:

PostgreSQL: Very useful. The latest versions are available at Bucardo.
MySQL: quite a few are available, make your pick.
Oracle: but you'd better ask the Oracle experts.
MsSQL: I'm not using it yet but will test it soon.


SSIS Merge Join Error I have a simple data flow.  I'm trying to join two sets of data from two different databases.  The TXT database is collation SQL_Latin1_General_CP1_CI_AS. The other dataset (Cost) is coming from Dynamics AX which is Latin1_General_CI_AS.

AX columns are Unicode.  So I have put data conversion on TXT columns to Unicode

However no matter what I do I cannot get rid of the MERGE JOIN ERROR and it's driving me insane.

  TITLE: Microsoft Visual Studio

  Both inputs of the transformation must contain at least one sorted column, and those columns must have matching metadata.

Both datasets are set to sort by ITEMID (and I have set sort keys in both).  Any ideas???

Whoops my bad!  I was sure I had set the sort key on the advanced editor on ole db source but I hadn't!


Postgres server not starting anymore My postgres was working previously, but for some reason does not work anymore. I have tried several different searches in stack overflow and other sites, and after coming up with the same results and reading the same pages over and over again, without any success, it was time to post a new question.

Trying to run my rails project on the local host raises the following error message: could not connect to server: No such file or directory Is the server running locally and accepting connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?

The server is not running at all, as indicated by the following output from ps -fu postgres:

postgres 23969 23968  0 09:54 pts/25   00:00:00 -bash

Error messages that I'm getting:

When running as the "postgres" user, executing psql results in:

psql: could not connect to server: No such file or directory
        Is the server running locally and accepting
        connections on Unix domain socket "/tmp/.s.PGSQL.5432"?

When trying to start postgres:

Other things I have tried:

Executing postgres -D /usr/local/pgsql/data >logfile 2>&1 & (as indicated by the official documentation) outputs: [1] 24670. But the server still doesn't run.
I also tried starting the  server via service postgres start. This prompts me to my password to authenticate me as the user (it says Authentication is required to start 'postgres.service'.) My login succeeds (==== AUTHENTICATION COMPLETE ===)but then it says Failed to start postgres.service: Unit postgres.service not found.
I tried restarting the server (following these instructions using sudo /etc/init.d/postgresql restart), which in the past did fix postgres, but now simply prompts Restarting postgresql (via systemctl): postgresql.service. However, now I try to start the server again, and nothing has changed.
The problem does not seem to be any postmaster.pid file that I need to remove. After going through the same process that this user did, nothing was found when running sudo find / -name .s.PGSQL.5432 -ls
I have tried a clean install, and it still doesn't work. Nothing has changed, and the same error messages are appearing.

After removing postgres (following these instructions), I went through the digital ocean tutorial up to the point where it says  "You can now access a Postgres prompt immediately by typing: psql".

Other possible factors: (I am at my wit's end, so I'm listing any possible factor that comes to mind)

I am running Ubuntu Xenial (16.04), and postgresql 9.5
I recently moved and am using a new Internet provider. I'm not sure why this would be an issue, but it did require me to change other settings (specifically, to deploy my rails project locally, instead of localhost:3000 I was using my inet addr. In other words, "http://:3000" (where  is the number provided for inet by ifconfig). But after moving to a new Internet provider, my inet addr changed.
[Update] Log files:

LOG: could not bind IPv4 socket: Cannot assign requested address
HINT:  Is another postmaster already running on port 5432? If not, wait a few seconds and retry.
WARNING:  could not create listen socket for "localhost"
FATAL:  could not create any TCP/IP sockets

At this point I spent several hours to no avail... Please help!!

The /etc/hosts file was incorrectly configured, due to some changes to that file previously. It did not cause trouble immediately, but for some reason changing the file back to defaults (i.e., 127.0.0.1 for the local host and 127.0.1.1 for my machine) solved the problem.

This was discovered by running netstat -tulpn | grep :5432, which gave the following error output: sudo: unable to resolve host <my_machine>. This pointed to the fact that there was a problem with the /etc/hosts file.

For a helpful resource, see this link.


Update inner join query efficiencies I have two tables, both with ~30,000 rows.  Anywhere where b.comments is not null, I want to overwrite a.comments with b.comments' data.  Seems simple enough - but my query has been running for 45 minutes and I don't think it's working.  There about 3,000 values in b that are not null - so a max of 3,000 updates.

I know that syntactically this is fine, but I'm wondering if there is a different way I should be writing this query as I cannot seem to get it to run unless I also add in something like ...and batch_id = 12345 to get the number of records down to 1.

UPDATE temp3 a
INNER JOIN temp2 b
ON a.batch_id = b.batch_id
and a.batch_li = b.batch_li
SET
a.comments = b.comments
WHERE
b.comments is not null;

(Taken from comment)

Try putting a nonclustered index on both - use batch_id and batch_li as the keys, and make sure to include comments. The order in which you include them depends on the contents of the columns - I'd read up on that before you make a decision.


Why is this datetime index not being used? I have a table like this:

CREATE TABLE TestTable
(
    [TestTableID] [int] IDENTITY(1,1) NOT NULL,
    [IntField1] [int] NOT NULL,
    [IntField2] [int] NOT NULL,
    [IntField3] [int] NOT NULL,
    [IntField4] [int] NOT NULL,
    [IntField5] [int] NOT NULL,
    [DateField1] [datetime] NOT NULL,
    [IntField6] [int] NOT NULL,
    [IntField7] [int] NOT NULL,
    [TextField1] [nvarchar](300) NULL,
    [DateField2] [datetime] NULL,
    [TextField2] [nvarchar](300) NULL,
    [DateField3] [datetime] NULL,
    [BoolField1] [bit] NULL
)

I have created an index like this:

CREATE NONCLUSTERED INDEX IX_TestTable_DateField1
ON TestTable(DateField1);

And now I have this query:

    DECLARE @startDate DATETIME = '20190101'
          , @endDate   DATETIME = '20200101'

    SELECT [TestTableID],
           [IntField1],
           [IntField2],
           [IntField3],
           [IntField4],
           [IntField5],
           [DateField1],
           [IntField6],
           [IntField7],
           [TextField1],
           [DateField2],
           [TextField2],
           [DateField3],
           [BoolField1]
      FROM TestTable
     WHERE DateField1 >= @startDate
       AND DateField1 < @endDate

This table has nearly 10,000,000 records, and this query will return nearly 10,000 records.

Now, I expected the query to at least use my index IX_TestTable_DateField1 (Index Scan + Key Lookup) but it is doing a Clustered Index Scan (on PK field). I think this is because the query is returning all fields for the table.

My previous thinking was:

If the index has INCLUDEd all fields, so the SqlServer will do an Index Seek;
If not included all fields, but if the field is used in WHERE or ORDER, it will use Index Scan + Key Lookup;
If neither 1 or 2, it will do a Clustered Index Scan;

Is this correct? Why an "Index Scan + Key Lookup" is not happening?

SQL server can definitely do an index seek and then a lookup, even when you don't cover the query.

The optimizer has no idea what values you have in your variables (that is how variables work). So it has to guess the selectivity. You can look at the actual execution plan and see how many rows it guesses. Apparently it guesses so many rows so it decides that it is better to do a table scan (cl ix scan).

If you add OPTION(RECOMPILE) at the end of the query, you should see a different estimated selectivity, and potentially usage of the index (all based on what selectivity you have in the end).

Also, if you have literals (values are known) or stored procedure parameters (values are sniffed), you'll see how it will estimate differently.


Aggregate JSON column I have a table with a JSON column in Postgres, I am grouping the rows and I am looking for some aggregate function that could "sum up" values from jsons.

sum aggregator sums the integers, I am looking for equivalent aggregator for JSONs, for example these 2 JSONs:

 {a: 1, b: 4}
 {b: 7, c: 3}

should be aggregated to

{a: 1, b: 11, c: 3}

Note that the values in conflicting keys are summed. JSONs are always 'str->int'

Is it possible to that in Postgres? Alternatively is it possible to write a custom aggregator to do that in C?

You need to first normalize the JSON data into a result that can be aggregated:

with data (doc) as (
  values
    ('{"a": 1, "b": 4}'::jsonb),
    ('{"b": 7, "c": 3}'::jsonb)
)
select k, v::int as val
from data, jsonb_each_text(doc) as t(k,v);

returns

k | v
--+--
a | 1
b | 4
b | 7
c | 3

This can now be aggregated per key:

select k, sum(v::int) s
from data, jsonb_each_text(doc) as t(k,v)
group by k;

returns:

k | s
--+----
c |  3
a |  1
b | 11

This can now be converted back and aggregated into a JSON:

select jsonb_object_agg(k,s) as doc
from (
  select k, sum(v::int) s
  from data, jsonb_each_text(doc) as t(k,v)
  group by k
) t

returns

doc
-------------------------
{"a": 1, "b": 11, "c": 3}


Mysql RDS migration problem I'm trying to migrate my MySQL RDS to a different machine. The reason is that I need smaller disk, and you can't make disks smaller for RDS servers, only bigger.

I'm trying to migrate with this answer:
https://stackoverflow.com/questions/20638012/backup-mysql-amazon-rds#20645291
and this tutorial:
http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/MySQL.Procedural.Importing.NonRDSRepl.html#MySQL.Procedural.Importing.Start.Repl

Briefly :

Created replica and stopped
Migrated from replica to a new server
Created user on Source DB with:

CREATE USER 'repl_user'@'%' IDENTIFIED BY 'slavepass';

GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%'
I've pointed to master from slave with this command:

CALL mysql.rds_set_external_master ('source.mydomain.com', 3306,'repl_user', 'slavepass', 'mysql-bin-changelog.000001', 107, 0);
I've started replication with :

CALL mysql.rds_start_replication;

When checking status with SHOW SLAVE STATUS, it shows that slave can't connect to master.
All servers are in the same security group. I was trying to connect to source with repl_user with MySQL command line and it worked.
I have no idea where to look next. I think it might be something with security in aws, but I was comparing new server with existing replicas and all seems the same.

I'm running MySQL 5.6 on both machines.

I've managed to make it work with pointing slave to master IP instead of url, and putting slave ip to inbound rules of Master's security group


DatabaseMail process is shutting down I Have configured the Database Mail in INSTANCE1 and INSTANCE2. Whenever i try to send a mail using the following code, i get output as "Mail queued" in INSTANCE1.

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Vinesh',
@recipients = 'VineshSenthilvel@gmail.com',
@body = 'The stored procedure finished successfully.',
@subject = 'Automated Success Message - Default' ;

But the mail is not delivered to my mailbox. And i tried executing the above query in INSTANCE2, the mail got delivered. I have verified that Database Mail configuration seems to be perfect in both the instances.

I can able to find the Database mail process is shutting down in the Database Mail Log. Also found an error mentioned below.

1) Exception Information
===================
Exception Type: Microsoft.SqlServer.Management.SqlIMail.Server.Common.BaseException
Message: The Transaction not longer valid.
Data: System.Collections.ListDictionaryInternal
TargetSite: Void ValidateConnectionAndTransaction()
HelpLink: NULL
Source: DatabaseMailEngine

StackTrace Information
===================
   at Microsoft.SqlServer.Management.SqlIMail.Server.DataAccess.ConnectionManager.ValidateConnectionAndTransaction()
   at Microsoft.SqlServer.Management.SqlIMail.Server.DataAccess.ConnectionManager.RollbackTransaction()
   at Microsoft.SqlServer.Management.SqlIMail.IMailProcess.QueueItemProcesser.GetDataFromQueue(DataAccessAdapter da, Int32 lifetimeMinimumSec)
   at Microsoft.SqlServer.Management.SqlIMail.IMailProcess.QueueItemProcesser.ProcessQueueItems(String dbName, String dbServerName, Int32 lifetimeMinimumSec, LogLevel loggingLevel, Byte[] encryptionKey, Int32 connectionTimeout)

Is there is any solution for this? Thanks in Advance.

I have had the same issue and I followed these steps to resolve that.

EXEC msdb.dbo.sysmail_help_queue_sp
and check if length > 0 then DELETE QUEUE items.
Use the following query to delete queue items:

DECLARE @GETDATE datetime
SET @GETDATE = GETDATE()
EXECUTE msdb.dbo.sysmail_delete_mailitems_sp
@sent_before = @GETDATE;

GO

EXEC msdb.dbo.sysmail_start_sp, start the process and check.


What database platform better at scaling? I am planning a for a database that i expect will be very big in terms of number of records rather than tables and batabases.  My Question is what product (i.e, SQL 2008, Cassandra, Azure) handles scaling better?

I expect that there will be 100k or so records will be added everyday, It should easily retrieve a single row and a batch of them.

The app will crawl the web and for each word in a dictionary will find the same word used in different sentences and record them to a database.
The developers are mainly .net oriented but they are fluent in C/C++ as well and because this is a academic project anything is basically affordable.

One platform/product per answer please.

Any of the big RDBMS systems or NoSQL systems can handle that sort of data load.  Back in 2002 I had a database table with 1B records in it that got more than 100k records a day, and that was running SQL Server 2000.  The systems today are much faster (both the hardware and the software) and should be able to handle that load going in with no problem.  The big problem becomes getting the data out, and having the data indexed properly to find it based on how you want to search.  And having enough memory to load the data into cache to find it faster.


Auto Increment Key Pair with Start Digit at 200000 SQL SERVER 2008 I'm currently rewriting a front end for a program that handles interviews. I want to prevent an issue the older program had were during the tiny amount of time it was doing a lookup for the next control number, another client could get the same number and create a conflict. I think it's best to do this in the database during the initial insert rather than the code.

The way I want it to work is with a two part key were one part is a Client's abbreviated name and the other part is an auto increment integer that starts at 200000. I would like it to do the 200000+ part per client. I want to auto fill the columns instead of having the program do it so there isn't any way the program can create a duplicate Client Key. It would be awesome if I could use this as the primary key(s). I have to leave it in two columns due to management.

Client1    200000
Client1    200001
Client2    200000
Client1    200002
Client2    200001
Client1    200003

How would I go about doing this?

Here is one way, note that this effectively limits concurrency, so don't expect stellar performance at scale.

INSERT dbo.TableName(col1, col2)
  SELECT @ClientName, COALESCE(
    (SELECT MAX(col2) + 1
     FROM dbo.TableName WITH (UPDLOCK, HOLDLOCK)
     WHERE col1 = @ClientName), 200000);

Of course if anyone ever deletes any rows from this table, you'll be missing values. And if an insert gets rolled back, that rolled back number will be reused, so be careful if you reveal that assigned value anywhere prior to the rollback.


Why does SQL Server use .bak extension? I've googled and I can't find any information about it. I'm wondering why does SQL Server uses .bak extensions by default.

I know that there are a lot of different software which use .bak extension, not only SQL Server.

And another question, is there any other file formats that can be used for the backup file?

In the case of backup software, you can't use anybody's bak file interchangeably. SQL Server's backup file format is proprietary to SQL Server. It doesn't work for other apps.

You can also name your backups anything you want during the backup process - you could use the .docx extension, for example, but that doesn't mean they're readable in Microsoft Word.

(Some folks default their differential backups to .diff, and log backups to .log - but other folks just name 'em all .bak. There's no real standard out there, and changing the extension doesn't affect the contents.)


SQL Server 2008 : force user to change password at next login How can I force users to change their password at next login? I want all my user change their password at next login to choose new password with new policy.

user must change password at next login is disable and just can be enable for new logins.

If you are creating a new login you have that option of " enforcing users to change the password at next login" from windows policies.
Therefore for To force a user to change password at next login, the sql login should be created with "Enforce Password policy" checked.

If you go back into this newly created login later, and look at the boxes, only 2 options are available to be checked. The “User must change password at next login” is grayed out.

  In order to access the box and force a password change, you need to
  change the password. The reason is that if the account is compromised,
  the hacker should not be the one to set a new password. The security
  model assumes the administrator can contact the legitimate owner
  offline and give them the new password.

You can refer here for same as well.


Monitor number of records returned in a long-running SELECT statement during SSAS processing On a SQL Server 2014, I have an SSAS tabular model which is querying a relational clustered columnstore table (with 800 mio. records), when the model is being processed. The query is just a simple SELECT statement with no joins, filters, etc. I am processing the model through an XMLA command, which is being fired by a SQL Server Agent job. The processing takes a couple of hours.

To get an idea of the progress of the processing, I would like to know the number of records returned so far by the SELECT statement. Is there any way to do this, when I know the SPID of the SELECT statement?

sys.dm_exec_requests does not seem to contain any record count information.

Also, I have tried to look in the DMV's of Analysis Services, to see if any of these objects could give me the information I need, but no luck.

The reason I think it must be possible to obtain this information somehow, is that when I process the tabular model through the graphical IDE of SSMS, it actually shows the row count while processing.

Update: It turns out the sys.dm_exec_requests DMV actually has a row_count column. Unfortunately, the contents of this column does not seem to reflect reality. For the first hour or so of processing, it had the value of "3", but I know from experience that several million records would have been returned within the first hour.

If you connect SQL Server Profiler to SSAS and watch the Progress Report Current events with an EventSubclass=ReadData the IntegerData column will show the cumulative rows read so far for that SSAS partition.

  Integerdata:   Contains the integer data associated with the reported
  event, such as the current count of the number of rows processed for a
  processing event.


Extract all objects from json array using MariaDB 10.3 How can I extract every object from this json array using MariaDB 10.3? I think the JSON_TABLE function in MySQL does what I need, but I'm currently constrained to MariaDB.
SET @json_doc='[
    {
        "DatabaseName":  "master",
        "Schema Name":  "dbo",
        "Object Name":  "CommandLog",
        "column_index_id":  1,
        "Buffer size(MB)":  0.02,
        "BufferCount":  2,
        "Row Count":  3,
        "Compression Type":  "NONE"
    },
    {
        "DatabaseName":  "master",
        "Schema Name":  "dbo",
        "Object Name":  "xevents",
        "column_index_id":  0,
        "Buffer size(MB)":  0.16,
        "BufferCount":  21,
        "Row Count":  0,
        "Compression Type":  "NONE"
    }
]';

With this query I can get the one of the objects in the array, but not all. There won't always be the same number of objects in the array so I need it to be dynamic.
WITH RECURSIVE data AS (
  SELECT
    JSON_VALUE(JSON_KEYS(@json_doc,'$[0]'),'$[0]') AS json_column,
    JSON_VALUE(@json_doc,CONCAT('$[0].',JSON_VALUE(JSON_KEYS(@json_doc,'$[0]'),'$[0]'))) AS json_data,
    0 AS column_index

  UNION
  SELECT
    JSON_VALUE(JSON_KEYS(@json_doc,'$[0]'), CONCAT('$[', d.column_index + 1, ']')) AS json_column,
    JSON_VALUE(@json_doc,CONCAT('$[0].',JSON_VALUE(JSON_KEYS(@json_doc,'$[0]'),CONCAT('$[', d.column_index + 1, ']')))),
    d.column_index + 1 AS column_index
  FROM data AS d
  WHERE d.column_index < JSON_LENGTH(JSON_KEYS(@json_doc,'$[0]')) - 1
)

SELECT json_column, json_data, column_index
FROM data;

This is the result I currently get.
------------------------------------------
|json_column     |json_data |column_index |
|DatabaseName    |master    |0            |
|Schema Name     |dbo       |1            |
|Object Name     |CommandLog|2            |
|column_index_id |1         |3            |
|Buffer size(MB) |0.02      |4            |
|BufferCount     |2         |5            |
|Row Count       |3         |6            |
|Compression Type|NONE      |7            |
-------------------------------------------

This is the result I desire.
-------------------------------------------------------
|json_column     |json_data |column_index |array_index|
|DatabaseName    |master    |0            |0          |
|Schema Name     |dbo       |1            |0          |
|Object Name     |CommandLog|2            |0          |
|column_index_id |1         |3            |0          |
|Buffer size(MB) |0.02      |4            |0          |
|BufferCount     |2         |5            |0          |
|Row Count       |3         |6            |0          |
|Compression Type|NONE      |7            |0          |
|DatabaseName    |master    |0            |1          |
|Schema Name     |dbo       |1            |1          |
|Object Name     |xevents   |2            |1          |
|column_index_id |0         |3            |1          |
|Buffer size(MB) |0.16      |4            |1          |
|BufferCount     |21        |5            |1          |
|Row Count       |0         |6            |1          |
|Compression Type|NONE      |7            |1          |
|...             |...       |...          |n          |
-------------------------------------------------------

You can accomplish this by using a dedicated numbers_table. This is a table with a single column number and can have as many values as you require (I have 0 to 999)
For your question, there are two scenarios: The first is if you do not know the keys, or they change. The second is if you do know the keys and in this case you can get each key of the json objects as a column.
Scenario 1:
SELECT
    JSON_VALUE(JSON_KEYS(@json_doc,CONCAT('$[',n1.number,']')),CONCAT('$[',n2.number,']')) AS json_column,
    JSON_VALUE(JSON_QUERY(@json_doc,CONCAT('$[',n1.number,']')),CONCAT('$.',JSON_VALUE(JSON_KEYS(@json_doc,CONCAT('$[',n1.number,']')),CONCAT('$[',n2.number,']')))) AS json_data,
    n2.number AS column_index,
    n1.number AS array_index
FROM numbers_table AS n1
JOIN numbers_table AS n2
WHERE
    n1.number < json_length(@json_doc) AND
    n2.number < json_length(json_keys(@json_doc,CONCAT('$[',n1.number,']')));

-------------------------------------------------------
|json_column     |json_data |column_index |array_index|
|DatabaseName    |master    |0            |0          |
|Schema Name     |dbo       |1            |0          |
|Object Name     |CommandLog|2            |0          |
|column_index_id |1         |3            |0          |
|Buffer size(MB) |0.02      |4            |0          |
|BufferCount     |2         |5            |0          |
|Row Count       |3         |6            |0          |
|Compression Type|NONE      |7            |0          |
|DatabaseName    |master    |0            |1          |
|Schema Name     |dbo       |1            |1          |
|Object Name     |xevents   |2            |1          |
|column_index_id |0         |3            |1          |
|Buffer size(MB) |0.16      |4            |1          |
|BufferCount     |21        |5            |1          |
|Row Count       |0         |6            |1          |
|Compression Type|NONE      |7            |1          |

Scenario 2:
SELECT
    n1.number AS array_index,
    JSON_VALUE(JSON_QUERY(@json_doc,CONCAT('$[',n1.number,']')),'$.DatabaseName') AS 'DatabaseName',
    JSON_VALUE(JSON_QUERY(@json_doc,CONCAT('$[',n1.number,']')),'$.Schema Name') AS 'Schema Name',
    JSON_VALUE(JSON_QUERY(@json_doc,CONCAT('$[',n1.number,']')),'$.Object Name') AS 'Object Name',
    JSON_VALUE(JSON_QUERY(@json_doc,CONCAT('$[',n1.number,']')),'$.column_index_id') AS 'column_index_id',
    JSON_VALUE(JSON_QUERY(@json_doc,CONCAT('$[',n1.number,']')),'$.Buffer size(MB)') AS 'Buffer size(MB)',
    JSON_VALUE(JSON_QUERY(@json_doc,CONCAT('$[',n1.number,']')),'$.BufferCount') AS 'BufferCount',
    JSON_VALUE(JSON_QUERY(@json_doc,CONCAT('$[',n1.number,']')),'$.Row Count') AS 'Row Count',
    JSON_VALUE(JSON_QUERY(@json_doc,CONCAT('$[',n1.number,']')),'$.Compression Type') AS 'Compression Type'
FROM numbers_table AS n1
WHERE
    n1.number < json_length(@json_doc);

Output:
|array_index|DatabaseName|Schema Name|Object Name|column_index_id|Buffer size(MB)|BufferCount|Row Count|Compression Type|
|0          |master      |dbo        |CommandLog |1              |0.02           |2          |3        |NONE            |
|1          |master      |dbo        |xevents    |0              |0.16           |21         |0        |NONE            |


Backup and Restore with RMAN Oracle 12c I'm trying using RMAN to create and restore backups. I'm getting an error stack trace when trying to restore to one of the backups that I have made. The error is

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 02/01/2016 11:52:10
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 30 and starting SCN of 2586064415

My backup RMAN script is as follows, and is run through an application where the parameters are filled in correctly.:

configure channel device type disk clear;
run {{
 allocate channel ch1 type Disk FORMAT '{0}\\BCKUP_%U' ;
 backup incremental level {1} database plus archivelog tag '{2}' ;
 backup spfile FORMAT '{0}\\SPFILE_%U' tag '{2}';
 backup current controlfile FORMAT '{0}\\CTRL_%U' tag '{2}';
}}

My restore script is:

run{
shutdown immediate
STARTUP FORCE NOMOUNT;
RESTORE CONTROLFILE FROM '...path to control file backup...';
ALTER DATABASE MOUNT;
RESTORE DATABASE;
RECOVER DATABASE;
ALTER DATABASE OPEN RESETLOGS;
}

I can restore to the most recent backup just fine, but when I try to take another step back to one made prior I receive that error. Is there something in my script for either creating the backup or restoring that needs to change?

Files Created by backup:

Side Note: I run ALTER DATABASE OPEN RESETLOGS after I get the error and everything is working fine and dandy. The problem though, is that the application that will eventually do the restore will see the error message stack and classify it as a failed restore.

Parameters:

{0}: \\192.168.xxx.xxx\c$\backups\20160201_104531_INCREMENTAL\ (depending on backup type its INCREMENTAL or ONLINE_FULL)
{1}: 0/1 (depending on level)
{2}: INC_20160201_104531 (depending on backup type it's INC or FULL)
The control file backup loc: \\192.168.xxx.xxx\C$\BACKUPS\20160201_104814_INCREMENTAL\CTRL_0IQSQVKP_1_1

This is absolutely normal when you try to perform a complete recovery from a hot backup, because that is how hot backups work, you will never have the latest changes in your backups.

Your backup includes archivelogs up to sequence 29. The next changes were in log sequence 30, but log sequence 30 became the current redo log at the time your backup was finished, so you do not have any backup of that. This is not a problem, this is how a hot backups work, you will never have backups of the latest log sequence. Obviously, the next hot backup will back up that, but by the end of that backup, another sequence becomes the latest sequence.

If you simply try recover database;, RMAN tries to recover the database to the most current state, and it does not know where to stop (except when you have not only the archivelogs, but the redo logfiles also). Your last log sequence in the backup is 29, RMAN will complain about the next one, because it does not know where to stop.

If you want to avoid the above error, you can explicitly specify RMAN where to stop (perform a point-in-time recovery). For example:

recover database until sequence 30;

This would have had the same result as your recover command, without throwing an error. You can also specify UNTIL SCN and UNTIL TIME.

30 because:

  When specifying log sequences, if the last created archived redo log
  has sequence n, then specify UNTIL SEQUENCE n+1 so that RMAN will
  apply n and then stop.

Or, you can just ignore this error and continue with alter database open resetlogs.


Linked Server from SQL Server to SSAS not working - what is missing? I have a SQL Server 2012 Standard edition sp1 that has ssas also installed.

From this server I create a linked server to a ssas server called BIREPLON1, but when I try to access it, I get the error message shown below.

the funny thing is that when I go through GUI, by clicking Server Objects\linked servers\BIREPLON1 all seem to be ok (as you can see below on picture number 4).

But when I run the simple select:

SELECT CATALOG_NAME
FROM OPENQUERY([BIREPLON1], 'SET FMTONLY OFF;SELECT [CATALOG_NAME] FROM $SYSTEM.DBSCHEMA_CATALOGS') as Radhe

I get this error message:

  Msg 7303, Level 16, State 1, Line 1
  Cannot initialize the data source object of OLE DB provider "MSOLAP" for linked server "BIREPLON1".

Have I missed out on something?

I found this article here.

There it states that:

To resolve that problem you have 3 options:

Run SQL queries from data server (you need to be remotely connected to the database server)
Enable use of Kerberos on the database server
Set proxy account for linked server, so that MDX queries are executed in its context instead of in context of the user that is issuing t-sql query:

Create the linked server:

EXEC master.dbo.sp_addlinkedserver
@server = N'SSASSERVER', -- name of linked server
@srvproduct=N'MSOLAP',
@provider=N'MSOLAP', -- see list of providers available on SQL Server under Linked Server node in SSMS Object Browser
@datasrc=N'ServerName', -- machine or instance name  that host Analysis Services
@catalog=N'SimplifiedCube' -- Analysis Services database (cube)

and adding a linked server login:

EXEC master.dbo.sp_addlinkedsrvlogin
@rmtsrvname=N'SSASSERVER',
@useself=N'False',
@locallogin=NULL,
@rmtuser=N'myDomain\Login',
@rmtpassword='########'

I have basically just done the number 3 and the linked server is working fine for me now.
As you can see on the picture below.

Also I had problems with the timing out so I had to change this setting as well:
(the default is 600)

EXEC sys.sp_configure N'remote query timeout (s)', N'6000'
GO
RECONFIGURE WITH OVERRIDE
GO

This can also be done through the GUI as per the picture below:


Restrictions after you failover to an upgraded server instance Restrictions after you failover to an upgraded server instance.
Can some one please explain this is detail? "After failing over from an earlier server instance to a SQL Server 2016 server instance, the database session is suspended. It cannot be resumed until the other partner has been upgraded".

After failing over from an earlier server instance to a SQL Server 2016 server instance, the database session is suspended.

Once you failover to the 2016 instance, the database is now upgraded fully to 2016. This is a one way trip and will no longer be able to be hosted on anything but SQL Server 2016. Since the partner is not 2016, the data movement will be suspended (stopped). No new data will "flow" back to the older instance.

  It cannot be resumed until the other partner has been upgraded

Until the old principal server (now the mirror server) is upgraded to 2016 you will not be able to resume data movement - no data will flow until you upgrade the older instance to 2016 and manually resume the data movement.


MySQL group by like statement? I need to get some stats out of our MySQL-DB and wonder if there is a faster way in doing this.

Basically I need info about user registration for any given month before including seperation by attributes.

e.G:

SELECT count(*) AS c
FROM `users`
WHERE `date_created` LIKE '2015-10%'
  AND `country` = 'DE'

This would return a number for October 2015. How could I alter this statement to return results for all months within one statement?

I would also be interested in adding something like AND confirmed = 1 as separate result.

Is this possible with just one MySQL-Statement?

Two things are needed to make it run faster:

Get suitable index

Without confirmed = 1, you need

INDEX(country, date_created)

With it, you need

INDEX(country, confirmed, date_created)

Avoid hiding the date in a function

LIKE '2015-10%' cannot use an index on date_created, nor can MONTH(date_created), so let's reformulate the query:

WHERE date_created >= '2015-10-01'
  AND date_created  < '2015-10-01' + INTERVAL 1 MONTH
  AND confirmed = 1   -- add this if you want to limit to confirmed rows

(This assumes that date_created is a DATETIME or TIMESTAMP datatype.)

All Months

This query can still use the two indexes I suggest, however, it won't necessarily use the date_created part.

SELECT LEFT(date_created, 7) AS YYYY_MM,
       COUNT(*) AS ct
    FROM tbl
    WHERE country = 'DE'
      AND confirmed = 1   -- if you want this
    GROUP BY YYYY_MM;

Note about country_code

A common mistake in defining the schema (please provide SHOW CREATE TABLE) is to define it as CHAR(2) and default to utf8.  This leads to using 6 bytes, when 2 would suffice.  Change to CHAR(2) CHARACTER SET ascii.


Getting midnight of today I want to get 00:00:00 of a day.  Should be done without using CONCAT. What options are there?

We can use CURDATE() function in select query but need to display like 2014-05-05 00:00:00. If NOW() will give like 2014-05-05 12:05:37.

I want add default value 00:00:00 with CURDATE() in query.

The documentation surprisigly contains a solution:

SELECT timestamp(current_date);

(which is basically the same as a comment above).


Creating encrypted backups inhouse to be restored at client I am trying to understand the mechanics of database backup encryptions, but there are some issues, which I can't seem to wrap my head around. I have 2 requirements:

I need to be able to create and restore ENCRYPTED database backups "locally", i.e. on servers, which I have full access.
I need to be able to transfer the ENCRYPTED database backup to a client, who is responsible for the administration as well as performing the restore.

On my local server

I have created a Master Key and backed it up to a secure location:

USE MASTER
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'MasterKey_Password';

BACKUP MASTER KEY
TO FILE = '<Secure_Location>\MASTER_KEY.key'
ENCRYPTION BY PASSWORD = 'MasterKey_BACKUP_Password';

Next I have created a certificate and backed it up to a secure location:

CREATE CERTIFICATE MyBackupCertificate
WITH SUBJECT = 'MyBackupCertificate_Subject';

BACKUP CERTIFICATE MyBackupCertificate
TO FILE = '<Secure_Location>\CERTIFICATE_BACKUP.cer'
WITH PRIVATE KEY(
    FILE = '<Secure_Location>\CERTIFICATE_BACKUP_PRIVATE_KEY.key',
    ENCRYPTION BY PASSWORD = 'Certificate_BACKUP_Password');

Finally, I have backup up the database using the certificate to encrypt it:

BACKUP DATABASE MyDatabaseToBackup
TO DISK = '<Backup_Location>\BACKUP_FILE.bak'
WITH ENCRYPTION (ALGORITHM = AES_256, SERVER CERTIFICATE = MyBackupCertificate)

On my client server

I have transferred the database backup file to a location accessible from the SQL Server Instance. Also the .cer file has been transferred to this location.

USE MASTER
CREATE MASTER KEY
ENCRYPTION BY PASSWORD = 'Another_MasterKey_Password';

Created the certificate from the .cer-file:

CREATE CERTIFICATE MyDatabaseToBackup
FROM FILE = '<Client_Secure_Location>\CERTIFICATE_BACKUP.cer'

When trying to restore the file list, I am met with the message

RESTORE FILELISTONLY FROM DISK = '<Client_Backup_Location>\BACKUP_FILE.bak'

  Msg 15507, Level 16, State 30, Line 11
  A key required by this operation appears to be corrupted.

I have been around some other posts, which are trying (almost) the same, but I do not understand, why I have to transfer the certificates private key to my client. Doesn't that defeat the whole purpose of encryption?

Restoring encrypted database on another server (using Backup Encryption)

Created the certificate from the .cer-file […]

You created the certificate but you didn't include the import of the private key, so it makes the use of this certificate to decrypt the data impossible.

  why I have to transfer the certificates private key to my client.

In this case, the certificate (which at its heart is just an asymmetric key) uses the public key to encrypt the data and the private key to decrypt the data. See my other response above.

  Doesn't that defeat the whole purpose of encryption?

No, but it defeats the purpose of backup encryption through SQL Server for your use case. Since you want yourself (1st party) and some random 3rd party to be able to encrypt and decrypt the data, you either need to agree to a set of keys to be used (which would put your other internal items at risk) or use a different method outside of SQL Server backup encryption where you can specify other encryptor options.

The fact that they can't decrypt the data without the required information is the whole point of encryption. In this case, it's doing the job properly it's just your requirements aren't meant to use it in this manner.


Find and Insert Missing data in Mongodb Collection I want to write python3 code to check and insert missing data. My MongoDB collection documents have one field named "height" which is BTC block number. I want to traverse in a range start to the latest block number and check which number is missing from range. The number which is missing I want to insert that. Can somebody help me with the logic?
I have MongoDB version 4.

Assuming your numbers are sequential (which appears to be the case for BTC height values), you can use the $range aggregation expression to generate the expected sequence of values for comparison with the existing values.

Here's an example aggregation function using the mongo shell:

function findMissingValues(start, end) {
   var results = db.blocks.aggregate([

      // This stage would benefit from a relevant index like `height: 1`
      { $match: {
         height: {$gte: start, $lte: end}
      }},

      // Find existing height values in the given range
      { $group: {
          _id: null,
          heights: {$push: "$height"}
      }},

      // Add the expected range (inclusive of start and end values)
      { $addFields: {
          allHeights: { $range: [ start, end + 1]}
      }},

      // Find any expected values that are missing from the existing data
      { $project: {
          _id: 0,
          missing: { $setDifference: [ "$allHeights", "$heights" ] }
      }}

   ]).toArray();

   printjson(results[0]);
}

Sample usage:

> db.blocks.insert([ { height: 550000 }, { height: 550002 } ])

> findMissingValues(550000,550005)
{ "missing" : [ 550001, 550003, 550004, 550005 ] }

With the missing blocks identified, you can now retrieve and insert the relevant BTC data.


How to model a relationship between Sandwiches and Toppings? I want to create a database structure pertaining to my product, which is a sandwich that can have one bread and many toppings.

To illustrate the problem I created a quick diagram:

For the one bread I can easily assign the sandwich_id as seen in the picture, but how would I assign the many toppings in a beautiful way?

My current considerations

I could think of some ideas:

topping_1_id, etc... and a toppings index as in 1 is ‘cheese’, 5 is ‘onion’, so topping_id = 15, but I am not sure how to do that.

Assuming you have your toppings and sandwiches tables, I'd create a linking table consisting of sandwich_id and topping_id, so that you can JOIN from each sandwich record to its many toppings and vice versa.

Each link represents a particular sandwich having a particular topping.

Hope that makes sense.


Load data from flat file into oracle table I created a new database, then created a table "users". Now, I'm trying to load data to this table from a flat file called 'users.tbl', that has some records  with the following format:

1|john|email@email.email|active

I'm using SQL Developer for this, i tried the following commands.

load data infile `"C:\path"
insert into table users
fields terminated by "|"
lines terminated by "\r\n";

But the data is not loaded and no errors are shown. Do you see why its not working? Or How can i load the data in the flat file to Oracle table?

According to your question you have data in the flat file and you want to access it or can load to another table. Basically you are talking about external tables. You want to read more on it. Here is the Oracle Documentation link: External Tables Concepts.

Here is the example how I loaded your data into my external table and created another table from it.

[oracle@testsrv1 Desktop]$ cat user.tbl
 1|john|email@email.com|active
[oracle@testsrv1 Desktop]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Thu Mar 3 06:45:26 2016

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

SQL> create user user1 identified by user1;

User created.

SQL> create directory user_dir as '/home/oracle/Desktop';

Directory created.

SQL> grant resource, connect to user1;

Grant succeeded.

SQL> grant read, write on directory user_dir to user1;

Grant succeeded.

SQL> conn user1/user1
Connected.

SQL> create table user_load(user_id number, name varchar2(20), email varchar2(50), status varchar2(10))
organization external
(default directory user_dir
access parameters
(fields terminated by '|')
location('user.tbl')
);

Table created.

SQL> select * from user_load;

   USER_ID NAME          EMAIL                  STATUS
---------- ----------   -------------------    ----------
     1 john         email@email.com        active

SQL> create table final as select * from user_load;

Table created

SQL> select * from final;

   USER_ID NAME          EMAIL                  STATUS
---------- ----------   -------------------    ----------
     1 john         email@email.com        active


use SQLcl with EZconnect and multiple hostnames (ADDRESS_LIST=) this is our classic tnsnames.ora

test1=
(DESCRIPTION=
    (CONNECT_TIMEOUT=4)
    (TRANSPORT_CONNECT_TIMEOUT=3)
    (ENABLE=BROKEN)
    (ADDRESS_LIST=
        (ADDRESS=(PROTOCOL=TCP)(HOST=example1.example.com)(PORT=1521))
        (ADDRESS=(PROTOCOL=TCP)(HOST=example2.example.com)(PORT=1521))
    )
    (CONNECT_DATA=
        (SERVER=DEDICATED)
        (SERVICE_NAME=EXAMPLE.EXAMPLE.DBS)
    )
)

I use this SQLcl command:

sql -nohistory -noupdates -S $username/$password@$hostname:$port/$servicename @$filename

How to specifiy multiple hostnames? Not only one? It's some kind of active passive cluster (Exadata).

Edit after first answer:

I added in shell script (in this dir tnsnames.ora):

TNS_ADMIN=/example/example

I call $sqlcl -nohistory -noupdates -S $username/$password@"MY-DB" @$filename and get back the error:

./script.sh
  USER          = MY_USER
  URL           = jdbc:oracle:thin:@MY-DB
  Error Message = IO Error: Unknown host specified
  USER          = MY_USER
  URL           = jdbc:oracle:thin:@MY-DB:1521/MY-DB
  Error Message = IO Error: Invalid connection string format, a valid format is: "host:port:sid"

EZconnect was not meant for things like that.

SQLcl can use tnsnames.ora as well. Just create one and use it:

sql -nohistory -noupdates -S $username/$password@test1 ...

Or you can do this, if you really want to provide all that stuff as a commandline argument:

sql -nohistory -noupdates -S $username/$password@"(DESCRIPTION= (CONNECT_TIMEOUT=4) (TRANSPORT_CONNECT_TIMEOUT=3) (ENABLE=BROKEN) (ADDRESS_LIST= (ADDRESS=(PROTOCOL=TCP)(HOST=example1.example.com)(PORT=1521)) (ADDRESS=(PROTOCOL=TCP)(HOST=example2.example.com)(PORT=1521))) (CONNECT_DATA= (SERVER=DEDICATED) (SERVICE_NAME=EXAMPLE.EXAMPLE.DBS)))" ...

Using tnsnames.ora:

[oracle@o71 ~]$ export JAVA_HOME=/u01/app/oracle/product/19.0.0/dbhome_1/jdk
[oracle@o71 ~]$ export PATH=/home/oracle/sqlcl/bin:$JAVA_HOME/bin:$PATH
[oracle@o71 ~]$ export TNS_ADMIN=/u01/app/oracle/product/19.0.0/dbhome_1/network/admin
[oracle@o71 ~]$ cat /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/tnsnames.ora
MIN19=
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.71)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME =  min19_o71)
    )
  )

[oracle@o71 ~]$ sql bp/bp@min19

SQLcl: Release 18.4 Production on Tue Mar 26 09:28:11 2019

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Last Successful login time: Tue Mar 26 2019 09:28:12 +01:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.2.0.0.0

SQL>


DBCC SHRINKFILE performance I have a production SQL Server 2008 with 8 TB spread over several databases. I've deleted about 85% of the data in each database and MUST recover the disk space. The final SUM of all USED_SPACE will be 1.2 TB.

This SHRINKFILE process must fit within a 7 hour maintenance window PER database.

However the DBCC SHRINKFILE on each is taking MANY hours to run. For example shrinking an 800 GB database down to 120 GB takes over 15 hours.

Unfortunately that exceeds my maintenance window and I'm forced to kill the process hoping the database does not become corrupted (subsequent DBCC CHECKDB shows they are fine).

I can see the DBCC SHRINKFILE does NOT fully utilize the available Disk I/O nor CPU resources.

For example, it is possible to copy the full sized database files from one disk to another within only a few hours, but the SHRINKFILE process takes considerably longer.

NOTE: the databases are all set to SIMPLE recovery mode and AUTO_SHRINK is off.

Yes, I see MANY people say to never do this as it destroys indexes - I do plan to rebuild the indexes when the SHRINKFILE completes.

Is there any way to increase the priority of the SHRINKFILE commands or any alternate solutions? Here are some options I'm considering:

SET DEADLOCK_PRIORITY HIGH in the same session before running the
SHRINKFILE.
(But I doubt this will help.)
Creating a new database and copying all the data table-by-table.

The solution is to Leave extra room.

When shrinking the MDF files, if I leave some unused space (do not remove ALL unused space) then it finishes considerably faster.

For example: If a 3TB database has only 200MB used, shrinking it to 200MB would take a very long time. However if I shrink it to 300MB then the shrink process is much faster.

I'm guessing SQL must defragment the data to the extreme in order to remove ALL unused space. However by leaving extra room it must only reorganize larger sections of data or otherwise adjust the file pointers to fit.


Schema Change/Update script for Database deploy I have a need to change the database schema . I'm planning to write Schema change and update scripts for tracking database changes and updating them. I followed

Versioning Databases – Change Scripts

for a start, I got a gist of what he is getting at however since I haven't worked much on SQL scripts before, a tutorial or something to start with would be good. I did some research on the web and came to know that most people use Automatic comparing tools to generate the script which I don't want to do for obvious reason that I won't learn the anything in the process.

I'm looking for some tutorials/links on How to write Change scripts and Update scripts ? Especially update scripts as I couln't find even a single script/pseudo-code on how to do update schema by comparing SchemaChangeLog table, connecting to the table using scripts...

Thanks in advance!

First, I would go with an existing solution, such as Liquibase. If you use an ORM such as Hibernate, it can also do schema migrations for you.

Otherwise, it would go something like this:

Start with an initial database schema script, and give it a version number, such as 0.0.0001.sql. It would contain mostly CREATE statements to create tables, etc, such as:

-- use me to keep track of what version the database schema is using:
create table schema_version (
  name text primary key
);

create table contact (
  id bigserial primary key,
  first_name text not null
);

insert into schema_version values ('0.0.0001');

You would store all of your .sql schema scripts in source control, such as git.

Then, as needed, you would add patch files, such as 0.0.0002.sql. It would normally contain ALTER statements that adjust your database schema:

alter table contact add column last_name text;

insert into schema_version values ('0.0.0002');

You will probably need to read the documentation for your particular database, as they often have slightly different ALTER syntax.

You would then have a non-sql script (bash perhaps), that would check which schema version the database is using and apply any available patches that are greater than that version, or the first sql script if there is no schema_version table.

This book is highly rated on Amazon:

Refactoring Databases: Evolutionary Database Design


Leveraging an index on another column with the same ordering guarantee I have a massive table that is only indexed by the auto-increment column, which is also populated with the current timestamp (which is not indexed).

CREATE TABLE MyTable
(
    ID BIGINT NOT NULL IDENTITY(1, 1) PRIMARY KEY
    ,AuditTimestampUtc DATETIME NOT NULL DEFAULT(GETUTCDATE())
    ,...
)

If I need to query by the creation timestamp of the row, how can I do it efficiently? Adding an index is not feasible because of how gargantuan the table is (hundreds of millions to billions of rows), as we cannot afford the downtime, and I am performing a rare debugging task in a readonly environment, which is essentially

SELECT [...] FROM MyTable WHERE AuditTimestampUtc BETWEEN @Start AND @End

I am trying to debug a novel issue, and have not had to do this task before, so I would have difficulty making the argument for creating a new index. And unfortunately there is quite a process for processing a request to create and sanitize a full database dump (especially given its size), or cloning it to another environment. I have an outdated dump to experiment with, but running the final query will be supervised through a read-only account on production.

Writing a custom binary search seems like overkill, especially in a RDBMS, but alas computers are not mind-readers, even though it is apparent to a person that the identity column can be used as a surrogate ordering* to efficiently search the table by creation time.

*Assuming nobody enables IDENTITY_INSERT to violate this temporal ordering guarantee.

P.S. I do not believe the database platform is very relevant to this question for the first time ever (ignoring the specific syntactical differences of declaring the index/default constraint/etc) but I am using SQL Server.

You could create a temporary table containing just the two columns you're interested in, using the key column as a pointer into the "real" table.  Something like:

CREATE TABLE #t
(
    ID int NOT NULL
    , AuditTimestampUtc datetime NOT NULL
);

Insert rows from MyTable like this:

INSERT INTO #t WITH (TABLOCKX) (ID, AuditTimeStampUtc)
SELECT mt.ID
    , mt.AuditTimeStampUtc
FROM dbo.MyTable mt

Then create an index on the table like this:

CREATE CLUSTERED INDEX t_AuditTimeStampUtc
ON #t (AuditTimeStampUtc);

Now, you should be able to query the "real" table, making use of the index on the #temp table, as in:

SELECT <columns from mt>
FROM dbo.MyTable mt
    INNER JOIN #t t ON mt.ID = t.ID
WHERE t.AuditTimeStampUtc >= '2019-06-01 00:00:00'
    AND t.AuditTimeStampUtc < '2019-07-01 00:00:00'

The query above will, probably, do an index seek on the non-clustered index t_AuditTimeStampUtc, with a nested loops join into MyTable.  This may be faster than just querying the original table.  Especially if you need to do multiple queries like this against MyTable.

Copying data from a large table might seem like a bad idea. If the original table had only the two columns, then yes, I'd agree it's a dumb thing to do.  However, if MyTable has many columns, the temp table will only occupy a small fraction of the space of the main table, and will be much more efficient.