How to detect MySQL slave writes? In a MySQL master-slave replication architecture, how do you detect if there have been (erroneously, since this should never happen!) direct writes on the slave?

For now, I'm running this command on the slave:

tcpdump tcp port 3306 and not host my_master_IP -A | grep -i -e INSERT -e UPDATE -e DELETE

whose output, if any, means writes on the slave, which is bad.

Is there a better method?

The best way would be to enable binary logging on the Slave. Why ?
With binary logging enabled on the Slave, each INSERT, UPDATE, DELETE recorded in the binary logs comes with the server_id where the SQL statement originated.
What if log-slave-updates was enabled ?
If you have log-slave-updates enabled on the Slave, every INSERT, UPDATE, DELETE from the Master comes with the Master's server_id. If any writes were executed on the Slave directly, you will have to separate the commands.
EXAMPLE: suppose the Master's server_id is 10 and Slave's server_id is 20. Now, let's say you have these binary logs on the Slave:
+------------------+------------+
| Log_name         | File_size  |
+------------------+------------+
| mysql-bin.000001 | 1073741824 |
| mysql-bin.000002 |  987741325 |
+------------------+------------+

You can separate the Master's commands from the Slave's commands like this:
mysqlbinlog --server-id=10 mysql-bin.* > Master_Commands.sql
mysqlbinlog --server-id=20 mysql-bin.* > Slave_Commands.sql

Then, you examine Slave_Commands.sql to see what commands were run and when.
What if log-slave-updates was disabled ?
This would be better because the binary logs on the Slave should never increase in size on a Slave unless someone or something ran INSERTs, UPDATEs, DELETEs locally.
EXAMPLE: Let's say you ran RESET MASTER; SHOW BINARY LOGS; on the Slave. For MySQL 5.6, you should see this:
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       120 |
+------------------+-----------+

From this point forward, if the binary logs ever change in size, this quicky tells you that someone or something ran INSERTs, UPDATEs, DELETEs locally. Next, you run
mysqlbinlog mysql-bin.* > Local_Commands.sql

Then, you examine Local_Commands.sql to see what commands were run and when.


Adding end-of-line markers to a string We have Scenario where we need to modify input string while fetching data from SQL Server.
"TEST RATE CODE DESC TEST RATE CODE DESCTST TEST" and C is 47th character in an "DESCTST" then push delimiter at the beginning of a word. like "\DESCTST"
For Example:

Current => TEST RATE CODE DESC TEST RATE CODE DESCTST TEST\
Required =>
TEST RATE CODE DESC TEST RATE CODE DESCTST \TEST

We have created an SQL function for achieving this and everything working fine except the backward operation as mentioned in the example.
Here is my SQL function:
ALTER  FUNCTION [dbo].[DescFormatter_New]
(
    @InputString nvarchar(max),
    @MaxLength int=0,
    @CharPerLine int=0,
    @NoOfLines int=0
)
RETURNS nvarchar(max)
AS
BEGIN
    DECLARE @ResultText nvarchar(max)
    DECLARE @I INT
    DECLARE @COUNT INT

    IF(LEN(@InputString)<@CharPerLine)
        BEGIN
            SET @ResultText=@InputString
        END
    ELSE
        BEGIN
            set @InputString=LEFT(@InputString,@MaxLength)
            SET @I = LEN(@InputString)/@CharPerLine

            if((@I*@CharPerLine)<LEN(@InputString))
                BEGIN
                    SET @I=@I+1
                END

            SET @COUNT =1
            SET @ResultText= LEFT((@InputString),@CharPerLine)
            if(@I>@NoOfLines)
                Begin
                    set @I=@NoOfLines
                End
            WHILE(@I>1 AND @I<=@NoOfLines)
                BEGIN
                    SET @ResultText=@ResultText+'\'+SUBSTRING(@InputString,(@COUNT*@CharPerLine)+1,@CharPerLine)
                    SET @COUNT=@COUNT+1
                    SET @I=@I-1
                END
        END
    RETURN @ResultText
END

We call this function like here:
[dbo].[DescFormatter_New](LText,94,47,2) AS [Short Description]

Note that we will need multiple line delimiters for the string which contains more than 100 or 200 characters.
here are the few examples :

1)
Input String = Deluxe and spacious 1 King Bed NS in room Whirlpool
Computer Wet Bar Refrigerator Mini Bar Desk Safe WIFI Warm Decor
Expected Output = Deluxe and spacious 1 King Bed NS in room \Whirlpool
Computer Wet Bar Refrigerator Mini \Bar Desk Safe WIFI Warm Decor
2)
Input String = Spacious 1 Queen Bed Non Smoking featuring flat screen TV with cable wifi coffeemaker mini frig microwave hairdryer iron with board test test
Expected Output = Spacious 1 Queen Bed Non Smoking featuring flat\ screen TV with cable wifi coffeemaker mini \frig microwave hairdryer iron with board test \test

In the above expected output string the first deliminator come after 47 charecters i.e between Whirl\pool (diaplayed as \Whirlpool) and then the loop will start from count 1 again after Whirl in the Whirlpool, So the second deliminator should come like here Des\k (Displayed as \Desk).

How do they do it

Fisrt step, select first substring of N characters in reverse mode.

REVERSE(SUBSTRING(@Text, @InitPos, @CharPosition))<

Second one, find first space position in the reversed string, that is the beginning of the selected word:

SELECT @pos = CHARINDEX(' ', @Str);

Then reverse it again, get left part of the string and add a separator '/':

LEFT(REVERSE(@Str), @CharPosition - @Pos + 1) + '/';

Last step by calculating next initial position, according to the beginning of the selected word:

@InitPos + @CharPosition - @Pos + 1;

The whole function:

CREATE FUNCTION [DescFormatter] (@Text nvarchar(max), @CharPosition int)
RETURNS nvarchar(max)
AS
BEGIN
    DECLARE @Pos int = 0;
    DECLARE @Str nvarchar(max) = '';
    DECLARE @Out nvarchar(max) = '';
    DECLARE @InitPos int = 1;

    WHILE @InitPos + @CharPosition < LEN(@Text)
    BEGIN
        SELECT @Str = REVERSE(SUBSTRING(@Text, @InitPos, @CharPosition));
        SELECT @Pos = CHARINDEX(' ', @Str);
        SELECT @Out = @Out + LEFT(REVERSE(@Str), @CharPosition - @Pos + 1) + '/';
        SET @InitPos = @InitPos + @CharPosition - @Pos + 1;
    END

    SELECT @Out = @Out + SUBSTRING(@Text, @InitPos, LEN(@Text) - @InitPos + 1);

    RETURN @Out;
END
GO

How to use it:

SELECT dbo.[DescFormatter]('Deluxe and spacious 1 King Bed NS in room Whirlpool Computer Wet Bar Refrigerator Mini Bar Desk Safe WIFI Warm Decor', 47);

This is the result:

Deluxe and spacious 1 King Bed NS in room /Whirlpool Computer Wet Bar Refrigerator Mini /Bar Desk Safe WIFI Warm Decor

dbfiddle here


Add new shard - always best? In our setup, we currently have 3 shard set sharded cluster, each shard being a replica set of 3. Our writes are about to go up significantly to implement a new feature, and we know the extra data will be necessary. The nature of our writes are basically all upserts(which will likely be updates) and updates where we increment a particular field by 1.

Our updates are always being incremented by 1 and the way our data is distributed, not all documents are treated equally, some get their fields incremented a lot more. An alternative solution that I thought could be effective is to have some type of middle man, like a few Redis databases (or some smaller mongods) where we do the updates to them first and after about 5 minutes (or use some queueing system), we have a bunch of workers consume the data and update the actual live cluster with the documents. This would save our main cluster a ton of writes as it would allow certain update heavy documents to accumulate their updates and could save us a ton of writes (exact numbers I will post shortly in an edit).

So bottom line, when is adding another shard not the right solution?

Nothing here immediately looks like a Sharding issue. Sharding will have a more significant impact on your Reads than your Writes. There are other areas, for Write performance, I would explore prior to spinning up more resources. There is also a larger topic of how your collection is sharded. You could easily, as per your description, have some documents be updated more than others. You could easily have hotspots in an improperly sharded cluster thus sharding will provide you little performance benefit.

MongoDB has changed since your question was posted. Circa 2014; you can now better leverage bulk operations. In other words, one request could carry more operations. That itself may already resolve the presumed issues you are referring to and eliminate the need for a middleman. If it does not, then yes you could use your proposed middleman approach. I work on a project that utilizes such a solution to pump much more data into a 3 shard development environment with an eye towards 17 shards. That is mainly due to C# driver inefficiencies.

Prior to a new shard, I would first explore setting your read preference to SecondaryPreferred. This will allow your primary node in each RS to leverage more resources for Write operations.

One could also argue you should reexamine how your documents are modeled. You may benefit from consolidating some documents if the Read operations tend to query a lot of pseudo-related data across many documents.


Insert And Update Statement With Merge I am attempting to execute the below syntax and I get two errors

  A MERGE statement must be terminated by a semi-colon (;).
    Incorrect syntax near '='.

Now if I comment out the last line of code which is an update statement the syntax executes perfectly.  Is there a way to run both an Insert() and Update() statement in one Merge()

Here is syntax

MERGE INTO EmpSales es
USING (
    SELECT employee, COUNT(sales) as SaleCount
    FROM salesInfo
    WHERE [Sale Date] BETWEEN '01/01/2016' AND '12/31/2016'
    GROUP BY employee
) cs
    ON es.employee = cs.employee
WHEN MATCHED THEN
    UPDATE SET es.TotalSales = cs.SaleCount
WHEN NOT MATCHED BY TARGET THEN
  INSERT (employee, TotalSales)
  VALUES (cs.employee, cs.SaleCount)
  UPDATE SET es.Madrigal = '0';

It's not completely clear what you're trying to accomplish. However, it seems most likely that you're trying to update EmpSales.Madrigal to '0' after your insert.

To set EmpSales.Madrigal to '0' for all newly inserted records:

MERGE INTO EmpSales es
USING (
    SELECT employee, COUNT(sales) as SaleCount
    FROM salesInfo
    WHERE [Sale Date] BETWEEN '01/01/2016' AND '12/31/2016'
    GROUP BY employee
) cs
    ON es.employee = cs.employee
WHEN MATCHED THEN
    UPDATE SET es.TotalSales = cs.SaleCount
WHEN NOT MATCHED BY TARGET THEN
  INSERT (employee, TotalSales, Madrigal)
  VALUES (cs.employee, cs.SaleCount, '0')
;

To set EmpSales.Madrigal to '0' for all inserted or updated records:

MERGE INTO EmpSales es
USING (
    SELECT employee, COUNT(sales) as SaleCount
    FROM salesInfo
    WHERE [Sale Date] BETWEEN '01/01/2016' AND '12/31/2016'
    GROUP BY employee
) cs
    ON es.employee = cs.employee
WHEN MATCHED THEN
    UPDATE SET es.TotalSales = cs.SaleCount, es.Madrigal = '0'
WHEN NOT MATCHED BY TARGET THEN
  INSERT (employee, TotalSales, Madrigal)
  VALUES (cs.employee, cs.SaleCount, '0')
;

If you're looking for something else, please clarify what you need.


max_allowed_packet after mysql restart I want to set max_allowed_packet parameter in runtime. The

SET GLOBAL max_allowed_packet = 16777216

works for new connections, but after database server restart, it has previous value. Can value of the max_allowed_packet in runtime for new connection and restart of database server will not affect this value?

Thank you.

Setting in the command line are not persisted anywhere. Therefore after a restart it resets to default or picks up the value from the config file.

Put the following line into your my.cnf file under the [mysqld] section:

max_allowed_packet = 16777216

About how to find what file is used you can find some resources here on stack exchange as well:

https://stackoverflow.com/questions/2482234/how-to-know-mysql-my-cnf-location


How to join fixed row table against mapping table and reveal unmapped records I have two tables and I am having trouble figuring out how to join them to meet my projection needs. Here is a scenario that illustrates the problem

1. The first table has a fixed set of values/rows; in this case Room Types

Room Types

Id | RoomType
--------------
1  | Bed Room
2  | Kitchen
3  | Half-Bath

2. The second table maps the items from the first table to an instance of a Property. One Property can have 1 or more Room Types

Property Rooms

Property | RoomTypeId
---------------------
  ABC    |     1
  ABC    |     3
  EFG    |     1
  XYZ    |     1
  XYZ    |     2
  XYZ    |     3

Note: some of the above properties are not mapped to some Room Types

3. I would like to make a projection that joins the rows in such a way that I can easily account for Room Types that are mapped and not mapped to a Property. Here is a sample result:

Property | RoomTypeId | PropertyHasRoom
---------------------------------------
   ABC   |     1      |      Yes
   ABC   |     2      |   No or NULL
   ABC   |     3      |      Yes
   EFG   |     1      |      Yes
   EFG   |     2      |   No or NULL
   EFG   |     3      |   No or NULL
   XYZ   |     1      |      Yes
   XYZ   |     2      |      Yes
   XYZ   |     3      |      Yes

Can anyone explain how this can be accomplished in T-SQL.

You need to create a multiplication first - every combination of Property + RoomType. In SQL Server we use a CROSS JOIN for that. Then you need to outer join to the junction table, and conditionally display Yes/No based on whether the junction table had a matching row. This should provide the output you want:

SELECT
  p.Property,
  RoomTypeID = t.Id, -- why isn't this just called RoomTypeID?
  PropertyHasRoom = CASE
    WHEN pr.Property IS NOT NULL THEN 'Yes'
    ELSE 'No' END -- or just END if you want NULL
FROM dbo.Properties AS p
CROSS JOIN dbo.RoomTypes AS t
LEFT OUTER JOIN dbo.PropertyRooms AS pr
ON p.Property = pr.Property
AND t.Id = pr.RoomTypeID
ORDER BY p.Property, t.Id;


SQL Azure "hot" table query slowdown over time I have an Azure SQL Database table that is acting as a "prioritized queue". As such, it gets heavily written to and deleted from over time. What's happening is that after a week or two, the DB size increases. CPU increases and performance reading from this slows down almost to a halt.

If I check the count, it's zero. If I check the table size, there are a lot of pages assigned to this table.

If I TRUNCATE the table, the count is still zero but the table size is back to minimal levels and performance is acceptable again.

Obviously, to me this indicates that the table has a lot of pages due to the high insert/delete activity and this is why the DB size increases. This is fine as when Azure cleanup occurs (presumably transaction log backups), the DB size drops back to normal levels.

What I don't understand is why the performance drops off so drastically when there are still zero rows in the table.

Can someone provide some suggestions as to what may be going on?

NB: The logic dictates that the overall write is atomic; i.e. update business data and write to the queue. Additional logic dictates that if an item (identified via business key) exists then overwrite queue item.

The Table is defined thus:

CREATE TABLE [dbo].[PrioritizedQueue](
    [Id] [bigint] IDENTITY(1,1) NOT NULL,
    [Region] varchar(10) NOT NULL,
    [ItemId] varchar(10) NOT NULL,
    [Priority] tinyint NOT NULL,
    [ReceivedTimestamp] datetime2(7) NOT NULL,
    [Operation] varchar(7168) NOT NULL,
    [Content] varchar(max) NOT NULL,
 CONSTRAINT [PK_IndexerQueue] PRIMARY KEY NONCLUSTERED
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)
GO
CREATE UNIQUE INDEX [IX_PrioritizedQueue_ItemId] ON [dbo].[PrioritizedQueue]
(
    [ItemId] ASC,
    [Region] ASC
)
INCLUDE ([Priority])
WITH (STATISTICS_NORECOMPUTE = OFF, DROP_EXISTING = OFF, ONLINE = OFF)
GO
CREATE CLUSTERED INDEX [IX_IndexerQueue_Priority] ON [dbo].[PrioritizedQueue]
(
    [Region] ASC,
    [Priority] ASC,
    [ReceivedTimestamp] ASC
)
WITH (STATISTICS_NORECOMPUTE = OFF, DROP_EXISTING = OFF, ONLINE = OFF)
GO

FYI: The "content" field is a varchar(max) due to history, it now only contains a file pointer so AFAIK the row should never need to use data pages off the current page

The Stored proc that reads from the table, and appears to have all the problems is:

CREATE PROCEDURE [dbo].[usp_indexer_dequeue]
    @batchSize AS int,
    @region varchar(10)
AS
BEGIN
    SET NOCOUNT ON;
    SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
    DECLARE @deletedbatch table
    (
        [Operation] varchar(7168) NOT NULL,
        [Content] varchar(max) NOT NULL,
        [ItemId] varchar(10) NOT NULL
    );
    WITH cte AS
        (
            SELECT TOP(@batchSize) [Operation], [Content], [ItemId]
            FROM [PrioritizedQueue] WITH (rowlock, updlock, readpast)
            WHERE [Region] = @region
            ORDER BY [Priority], [ReceivedTimestamp] ASC
        )
    DELETE FROM cte
    OUTPUT deleted.*
    INTO @deletedbatch;
    SELECT * FROM @deletedbatch
END
GO

This proc is designed to fetch the top available N rows (typically this 25 rows) return them to the app and then delete from the table. As it runs as one of many processes calling this at once, it uses (rowlock, updlock, readpast) to bypass anything currently locked.

The problem might be cardinality estimation, index fragmentation and stale statistics causing performance issues that increase over time. If you're not rebuilding your indexes and updating your statistics, this can be affecting the optimiser's plan choices and causing performance degradation over time. It might also explain why TRUNCATE seems to correct the issue (see Kendra Little's article, basically the optimiser tracks TRUNCATE to ensure it makes sensible decisions on empty tables even without statistics updates).

You need to do a couple of things to track down the root cause:

Enable Query Store to identify regressions in query performance over time. If a new plan is being selected due to cardinality issues or stale statistics, Query Store will highlight them.
Implement regular index and statistics maintenance tasks if you haven't already. If you have stats or index frag issues, this should help alleviate them.
Track your wait statistics over the one-two week period to see if a particular wait is increasing over time causing the degradation.
Next time it occurs, don't truncate to fix - you need to collect as much data as you can to try and determine the root cause. Get the statistics details for the table, get the index frag details for the table, get the current execution plan details, collect BlitzCache details, these will all help you identify the root cause.


Enable GTID on one node in Percona/Galera Im having 3 node percona cluster. (5.6)

Server ID is enabled on each node but all are different IDs.
Binlog and log slave updates are enabled.

Now I want to enable GTID on the third node alone. I have tried it in a two node test server, and nothing has affected. So I want to implement this on the production which has 2k transaction on sum of all three nodes.
Questions:

If I enabled on only one server, will if affect anything in percona replication (Reference)?
What will happen for my running transactions while restarting the Percona MySQL? (will be kill and roll back the uncommitted transactions)?
I have different Server ID, any problem with that?

The scenario you have described is all OK.

Nevertheless, we recommend that you enable GTID on all nodes, and the recommendation in that reference you provided is also to enable GTID on all nodes. As a general principle, Percona recommends having the same configuration on all nodes of the cluster. If you don't do it at the same time and then later try to enable it then you risk having GTID inconsistent as per that reference you posted.

As long as that recommendation is understood...

Running transactions:

If the node that is being shutdown is replicating trx .. then existing trx will not have any affect
If the node that is being shutdown is processing an active workload then you should stop this workload. If the trx is already in commit state that will processed but no new trx will allow to progress.

There is a discussion on the Percona blog that describes some different scenarios using 5.6 and this might provide some further insight for you. For example, it observes that even if you are using 'mixed' replication "we can have different replication modes on different servers, but only if all servers have gtid_mode set to ON."

However, the post also mentions that the author can't think of a good reason to do this (maybe you now have one). As well as blog posts (do a search on that site) you might also find some useful - free - recorded webinars on PXC that might be good resources for you and provide more information. Here is the link, as always please read the details carefully and test! :)
https://www.percona.com/blog/2013/05/21/replication-in-mysql-5-6-gtids-benefits-and-limitations-part-1/

Here is a webinar for 5.6 https://www.percona.com/resources/mysql-webinars/whats-new-percona-xtradb-cluster-56 ... if you add webinar to your site search you'll turn up some more useful recorded webinars on Percona XtraDB Cluster. These are for techs and presented by techs, normally support or development engineers.

Disclosure: I work for Percona (obviously, but I guess I should say it anyhow).


Recursive CTE to find Total for all children Here is an assembly tree that I want to search using a recursive T-SQL Query (presumably CTE) with the expected results below. I want to know the total amount per assembly given any part.

Meaning if I search for 'Rivet', I want to know the total count at each level within the assembly, not just the direct children count.

Assembly (id:1)
    |
    |-Rivet
    |-Rivet
    |-SubAssembly (id:2)
    |   |
    |   |-Rivet
    |   |-Bolt
    |   |-Bolt
    |   |-SubSubAssembly (id:3)
    |      |
    |      |-Rivet
    |      |-Rivet
    |
    |-SubAssembly (id:4)
       |-Rivet
       |-Bolt

    DESIRED Results
    -------
    ID, Count
    1 , 6
    2 , 3
    3 , 2
    4 , 1

Currently, I can get the direct parents, but want to know how to extend my CTE to allow me to roll this information upward.

With DirectParents AS(
--initialization
Select InstanceID, ParentID
From Instances i
Where i.Part = 'Rivet'

UNION ALL
--recursive execution
Select i.InstanceID, i.ParentID
From PartInstances i  INNER JOIN DirectParents p
on i.ParentID = p.InstanceID

)

select ParentID, Count(instanceid) as Totals
from DirectParents
group by InstanceID, ParentID

Results
-------
ID, Count
1 , 2
2 , 2
3 , 2
4 , 1

Creation script

CREATE TABLE [dbo].[Instances] (
  [InstanceID] NVARCHAR (50) NOT NULL,
  [Part] NVARCHAR (50) NOT NULL,
  [ParentID] NVARCHAR (50) NOT NULL, );

INSERT INTO Instances
Values
  (1, 'Assembly', 0),
  (50, 'Rivet', 1),
  (50, 'Rivet', 1),
  (2, 'SubAssembly', 1),
  (50, 'Rivet', 2),
  (51, 'Bolt', 2),
  (51, 'Bolt', 2),
  (3, 'SubSubAssembly', 2),
  (50, 'Rivet', 3),
  (50, 'Rivet', 3),
  (4, 'SubAssembly2', 1),
  (50, 'Rivet', 4),
  (51, 'Bolt', 4)

This recursive CTE (SQL Fiddle) should work with your sample:

WITH cte(ParentID) AS(
    SELECT ParentID FROM @Instances WHERE [Part] = 'Rivet'
    UNION ALL
    SELECT i.ParentID FROM cte c
    INNER JOIN @Instances i ON c.ParentID = i.InstanceID
    WHERE i.ParentID > 0
)
SELECT ParentID, count(*)
FROM cte
GROUP BY ParentID
ORDER BY ParentID
;

Output

ParentID    Count
1           6
2           3
3           2
4           1

Note: You mentioned in comments that the question only contains a simplified sample table and real data have proper indexes and handle duplicates and data adequately.

Data used (SQL Fiddle):

DECLARE @Instances TABLE(
    [InstanceID] int NOT NULL
    , [Part] NVARCHAR (50) NOT NULL
    , [ParentID] int NOT NULL
);

INSERT INTO @Instances([InstanceID], [Part], [ParentID])
VALUES
    (1, 'Assembly', 0)
    , (50, 'Rivet', 1)
    , (50, 'Rivet', 1)
    , (2, 'SubAssembly', 1)
    , (50, 'Rivet', 2)
    , (51, 'Bolt', 2)
    , (51, 'Bolt', 2)
    , (3, 'SubSubAssembly', 2)
    , (50, 'Rivet', 3)
    , (50, 'Rivet', 3)
    , (4, 'SubAssembly2', 1)
    , (50, 'Rivet', 4)
    , (51, 'Bolt', 4)
;


Dimension or Degenerate Dimension? I am taking a course on data warehousing. Currently, I am reading through Ralph Kimball's Data Warehouse Toolkit.

I understand that a degenerate dimension is a dimension key that is stored in a fact table. It does not join to a corresponding dimension table because all of its attributes have already been placed in other analytical dimensions, thus eliminating the need to join to another dimension table.

What I do not understand is when I should make something into a Degenerate Dimension as opposed to having it as a "normal" dimension and linking it to the fact table via a Foreign Key?

These are the hints:

The dimension consists only of natural key (e.g. invoice's or order's number) and contains no other attributes (because they've been placed in dimensions of their own).
The dimension table grows proportionately with the fact table, e.g. every time you insert new order lines into the fact table, you have to insert new OrderNumber dimension rows.
You never use the dimension to filter data when populating a report and it makes no sense to use its values as a row header.

Read Design Tip #46: Another Look At Degenerate Dimensions from the Kimball Group


Change SQL Server service account without restarting SQL Server For security reasons, we want to change the password that starts the SQL Server service everyday.  Is it possible to do this without restarting the service?

If it is possible will the changes be immediately applied, or will I eventually need to restart the service to get the new password?

We were thinking of doing this change in the services option of the windows OS and not in SQL Server configuration manager.

...for security reasons we want to change the password that starts the sql server service everyday

I realize this is probably outside your control, but it's worth mentioning that this is not a good idea for security.  It's a high process overhead for your organization (even if it's generally automated in some way), and it will likely lead to increased chances of some kind of password breach.

Think about it like this: every time you open the bank vault, there is a risk that some malicious person can get inside.  Would you rather open the vault every single day and change the locks, or open it occasionally and keep a really big lock on it (long, secure password).

Accessing the password every day (to change it, and then apply it to the service account) is like this.  You should very a very secure password over changing the password frequently.

If you are required to do this, though - why don't you want to use SQL Server Configuration Manager?  That is the recommended method per the docs:

SQL Server Configuration Manager - Changing the Accounts Used by the Services

  Always use SQL Server tools such as SQL Server Configuration Manager to change the account used by the SQL Server or SQL Server Agent services, or to change the password for the account. In addition to changing the account name, SQL Server Configuration Manager performs additional configuration such as setting permissions in the Windows Registry so that the new account can read the SQL Server settings. Other tools such as the Windows Services Control Manager can change the account name but do not change associated settings. If the service cannot access the SQL Server portion of the registry the service may not start properly.

Emphasis added by me.  So doing this outside of the service can cause reliability issues with your database engine.

Regarding avoiding a restart, using the configuration manager accomplishes this goal as well:

  As an additional benefit, passwords changed using SQL Server Configuration Manager, SMO, or WMI take affect immediately without restarting the service.

Of course, as other answers have mentioned, you should be allowed to restart the service from time to time (Windows Updates, etc).


User permissions for Microsoft SQL Server Please advice to give permission for a SQL server user. Conditions are, this user must be able to create,delete,modify,drop the tables/view/procedure on user default schema(not dbo) not in any other schema. I have given db_ddladmin privilege but here the user will be able to perform any action on other schema's as well.

GRANT CONTROL ON SCHEMA :: BobsSchema To Bob;

Where Bob is the users name. See MSDN.


casting empty string as null datetime Consider the following code in Microsoft SQL Server 2012:

INSERT INTO [dbo].Production
SELECT [field1]
      ,[field2]
      ,cast([datefield] as datetime)
FROM [RAW].Staging

The staging table is loaded with data from a CSV file. So in some cases, rather than having NULL fields we end-up with empty fields. As a result the datefield gets converted to a 1900-01-01 date when being inserted over the Production table.

I was wondering if there is something that I could do during the insert code above to ensure that datefield is set to NULL if the field is NULL or empty when coming from the Staging table.

Besides Gary's answer, I came up with the following:

cast(NULLIF([expected_discharge_dttm],'') as datetime)

Can anyone foresee an issue with this code (or Gary's use of CASE)?


Transaction log growing out of control on random times We have a problem with one of our databases. At random times something causes our transaction log to swell up to 20x its original size (it basically fills up the whole HD, so 20x is not accurate since it might grow more if there was space left on disk)

We do full backup once a week and differentials every day. Our database is using SIMPLE recovery mode.
I am trying to go through the transaction log of the differential and full backup files using fn_dblog and fn_dump_dblog, yet for whatever reason, in both cases it is showing me [Begin Time] and [End Time] for a very small amount of time. For differential it is showing records for a time span of only around 1/2 a minute. For the full backup, it is showing records within a span of 5-10 minutes.

Here is the query I was running, hoping from here i can find long running transaction logs from the past:

SELECT
    [Current LSN], a.[Transaction ID], [Transaction Name], [Operation], [Begin Time], [End Time], SUSER_SNAME([TRANSACTION SID]) as LoginName
FROM fn_dump_dblog (NULL, NULL, N'DISK', 1, 'DB_backup_path',
    DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
    DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
    DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
    DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
    DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT) as a
WHERE
    Operation in ('LOP_BEGIN_XACT', 'LOP_COMMIT_XACT')
order by
    [Transaction ID]

Here is the query I was running to see long transaction which are still in activate transaction log:

SELECT
    [Current LSN], [Transaction ID], [Transaction Name], [Operation], [Begin Time], [End Time], SUSER_SNAME([TRANSACTION SID]) as LoginName
FROM
    fn_dblog(NULL, NULL)
WHERE [Operation] in ('LOP_BEGIN_XACT', 'LOP_COMMIT_XACT')

In both cases, why am I getting data only for a small span of time?

Also, on more generic level, is this the best way to investigate why on random times the transaction log grows out of control. We looked at all the reoccurring sql jobs we are running and there is nothing extra ordinary there. When we kick them off, they run for at most 5 minutes. Even running multiple jobs at the same time causes no bottlenecks. Our application does not use any transactions when it talks to DB, so we are completely confused.

Any ideas?

If you are running in simple recovery mode, then the only transaction log data the full and differential backups will contain are for the very narrow time while the backup itself is in process, so looking at that is not going to help you here.

To see real-time activity, you can instead run the SQL Profiler.

Before I did that, though, I'd try a few other ways to identify open transactions:

Download and use sp_WhoIsActive, look at the open_tran_count and perhaps the start_time columns.
Run a query to show explicit and implicit open transactions.

Something like:

SELECT st.session_id, DB_NAME(sp.dbid) as dbname, at.name,
    at.transaction_begin_time, sp.last_batch, sp.status,
    RTRIM(sp.hostname) as hostname, RTRIM(sp.program_name) as program,
    RTRIM(sp.cmd) as command, RTRIM(sp.loginame) as loginname
FROM sys.dm_tran_active_transactions at
INNER JOIN sys.dm_tran_session_transactions st ON at.transaction_id = st.transaction_id
INNER JOIN sys.sysprocesses sp ON st.session_id = sp.spid
ORDER BY transaction_begin_time

If you see something that has been running for a very long time, try doing a kill and see if the log clears up.


Combining array_agg and unnest Given a dataset (with a GIN index on values):

key | values
-------------
 1  | {4,2,1}
 1  | {2,5}
 2  | {4,1,3}

I want to aggregate the arrays:

key | values
-------------
 1  | {4,2,1,5}
 2  | {4,1,3}

My first thought didn't work:

SELECT key, array_agg(DISTINCT unnest(values)) AS values FROM data GROUP BY key

  [0A000] ERROR: aggregate function calls cannot contain set-returning function calls
  Hint: You might be able to move the set-returning function into a LATERAL FROM item.

Not being familiar with LATERAL FROM, it's not obvious to me how to achieve the desired output.

You need to do the unnest in a sub-query:

select d."key", array_agg(distinct x.v)
from data d
  cross join lateral unnest(d."values") as x(v)
group by d."key";

Set returning functions (like unnest()) should in general be used in the from part of a query. But to be able to reference a column from the table you need a lateral join.

from data cross join lateral unnest(...) is an explicit way of writing from data, unnest(...) which also generates a cross join. But I prefer the explicit cross join operator to document that I indeed intended to write a cross join, rather than accidentally.

This will however not preserve the order of the elements.

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


Unable to re-install an instance using the original instance name We are trying to uninstall and re-install a 2008R2 instance.  We have uninstalled the database engine but when we tried to re-install it we got the error "Original instance id\name in use".

The instance is on a VM machine (no cluster).  Unfortunately there is another (production) instance on the same machine.

The steps taken were as follows:

Back up the databases
Uninstall the instance
Reboot
Attempt re-install failed
Review registry for any possible references to the database engine and that instance name and remove them.
Attempt re-install - same error.

We did discover that there is a reporting server instance with the old instance name already installed but I wouldn't have thought that the instance name on a reporting server instance would affect the install of a database engine instance.

Any suggestions on what we are missing?

The cleanest solution IMHO will be to remove reporting services, too, as part of cleaning the old instance off the machine. Until you've cleaned off everything service-related, that instance name still "exists" - and it can be a really thorny and frustrating experience trying to remove all the registry entries, GUID by agonizing GUID.

I suspect that SQL Server must keep some things around e.g. for connectivity to SSRS from external sources via the instance name. This is why you can't just pave a new instance over top of it.

The other alternative would be to leave your SSRS instance intact, but install the new instance with a different name, and just update application connection strings, aliases, DNS etc. so that they will connect to the new instance name (hopefully this change will be as transparent as possible).


Oracle: Extract Graphical Database Representation Possible Duplicate:
  Oracle Creating ER Diagrams and Data Dictionaries

How can I extract a graphical (schema) representation of an Oracle database (tables and their relationships; with colums and datatypes....). Are there (free) tools, which can do this?

I think the Oracle SQL Developer has no such feature...

You might try SQL Developer Data Modeller which does do graphical data models and is free - I don't know if it does reverse engineering though. http://www.oracle.com/technetwork/developer-tools/datamodeler/sqldevdm31ea-download-515132.html


Distributed Database Architecture Using MySQL Replication I've been task to move our web servers to the cloud for all countries that use our services for localization.

The issue is, they must share the same data and therefore I need to set up some database architecture that reads/writes locally but is shared amongst other servers.

I found an article that is pretty much what I want to do. Example below.

http://www.compassitesinc.com/blogs/distributed-database-architecture-using-mysql-replication/

However my DBA says this is not possible. So my question is, is this possible? And if so, how?

We are a LAMP shop (Redhat, Apache, PHP5, MySql 5.5).

Thank you.

Bill Karwin has already given the best answer that I can think of... but I think some additional explanation might be in order as to why the link you posted in the original question probably does not provide a sufficient solution.

Your DBA is, at least in one sense, correct.  The problem is that the illustration is deceptive.  The three distributed nodes in the middle look like they are database servers sending updates back to the master, which MySQL can't really offer.  You'll notice that the 3 nodes are actually labeled as "web servers."

It is apparent from the configuration examples that all the MySQL slave instances are doing in this setup is providing the application with read-only access to local replicas of the master database, but the web (application) server is actually sending all of its writes back to the master database -- not through to the local MySQL instances.

That's what MySQL can do -- scale out your ability to read from asynchronous replicas.  It is unlikely that you'd want the ability for applications to write to the database to be dependent on the single point of failure that the document includes.  Yes, you could have multiple masters, but depending on how the application works, it's only safe to direct writes to one such master at a time because there's no provision in multi-master asynchronous (ordinary MySQL) replication to handle the case when conflicting data gets written simultaneously from two (or more) masters.

PXC and MariaDB in cluster configuration don't have that same problem because the cluster nodes don't operate asynchronously... although there's a different problem, since if one node gets isolated from the other two, it can't be written to because in the case of a network split, the server that is left in the minority won't accept writes (to avoid inconsistency).  If each of them is isolated from all of the others, then none of them will work, because they are all in the minority and have no idea what has happened to their peers.  That's not to say it's not a great solution -- it's just not a perfect one.

But there is no perfect solution.

If your data is such that the following is practical, there's a third approach.  This would be a scenario where each site has its own set of uniquely-named schema+table sets (that is, table_schema + table_name is unique to each site, table names could be the same if database names are different) that are written to, but needs to be able to read the data from the other sites.

MariaDB multi-source replication could be configured such that you have a centralized server that is configured as a slave to all of the remote servers, and serves as master to all of the remote servers.  Each replication event received by this central server would be written to the binlog and sent out to all of the remotes, which would execute it locally.  The remotes would log their local changes, sending replication events back to the master for distribution to all of the other remotes... sort of a hub-and-spoke configuration.  The central server would only consolidate the data, it wouldn't be used by an instance of the application... and the remote servers wouldn't necessarily even have to be MariaDB -- only the central one -- I have MySQL 5.5 and MariaDB 10.0 systems interconnected and replicating to each other (5.5 <-> 10.0) without issue in my operation.  The current limit of "spoke" servers would be 64.

But again, this is only practical if each site needs to see, but not change, the data from the other sites, and/or access a common set of read-only data while updating a uniquely-named local set of tables.  In this scenario, a site's isolation would only delay their access to the latest data from outside the local environment but would not impair their ability to write to the things they should be able to write.


Getting one record per Emp_ID I wrote a query whereby I  joined multiple tables to get  one row per employee id and the relevant information for each employee job code, job code etc

SELECT A.EMPLID, A.JOBCODE, A.JOBCODE_DESCR, A.EMPL_STATUS, A.LOCATION, A.LOCATION_DESCR, A.NAME, E.EMAILID, B.EMPLID, E.OPRID
  FROM PS_NPS_VIEW_ALL A, PS_PERSON_NAME B, PSOPRDEFN E
  WHERE ( A.EFFDT =
        (SELECT MAX(A_ED.EFFDT) FROM PS_NPS_VIEW_ALL A_ED
        WHERE A.EMPLID = A_ED.EMPLID
          AND A.EMPL_RCD = A_ED.EMPL_RCD
          AND A_ED.EFFDT <= SYSDATE)
    AND A.EFFSEQ =
        (SELECT MAX(A_ES.EFFSEQ) FROM PS_NPS_VIEW_ALL A_ES
        WHERE A.EMPLID = A_ES.EMPLID
          AND A.EMPL_RCD = A_ES.EMPL_RCD
          AND A.EFFDT = A_ES.EFFDT)
     AND A.EMPLID = B.EMPLID
     AND A.EMPLID = E.EMPLID
     AND A.PER_ORG = 'EMP'
     AND A.PAYGROUP NOT IN ('SUM','CWR'))

I wanted to get another field, JPM_DESCR90 into my query by joining the table PS_NPS_LIC_VW_RPT by EMPLID with one of my other tables in the original. I tried a left outer  join between PS_PERSON_NAME B and PS_NPS_LIC_VW_RPT where by PS_PERSON_NAME B LEFT OUTER JOIN  PS_NPS_LIC_VW_RPT C ON  B.EMPLID = C.EMPLID

SELECT A.EMPLID, A.JOBCODE, A.JOBCODE_DESCR, A.EMPL_STATUS, A.LOCATION, A.LOCATION_DESCR, A.NAME, E.EMAILID, B.EMPLID, E.OPRID, C.JPM_DESCR90
  FROM PS_NPS_VIEW_ALL A, (PS_PERSON_NAME B LEFT OUTER JOIN  PS_NPS_LIC_VW_RPT C ON  B.EMPLID = C.EMPLID ), PSOPRDEFN E
  WHERE ( A.EFFDT =
        (SELECT MAX(A_ED.EFFDT) FROM PS_NPS_VIEW_ALL A_ED
        WHERE A.EMPLID = A_ED.EMPLID
          AND A.EMPL_RCD = A_ED.EMPL_RCD
          AND A_ED.EFFDT <= SYSDATE)
    AND A.EFFSEQ =
        (SELECT MAX(A_ES.EFFSEQ) FROM PS_NPS_VIEW_ALL A_ES
        WHERE A.EMPLID = A_ES.EMPLID
          AND A.EMPL_RCD = A_ES.EMPL_RCD
          AND A.EFFDT = A_ES.EFFDT)
     AND A.EMPLID = B.EMPLID
     AND A.EMPLID = E.EMPLID
     AND A.PER_ORG = 'EMP'
     AND A.PAYGROUP NOT IN ('SUM','CWR')

However I got multiple rows for each  EMPLID since JPM_DESCR90 has multiple values for each EMPLID in PS_NPS_LIC_VW_RPT C .

I also tried a standard join between the two whereby B.EMPLID = C.EMPLID between
PS_PERSON_NAME B, and  PS_NPS_LIC_VW_RPT C

SELECT A.EMPLID, A.JOBCODE, A.JOBCODE_DESCR, A.EMPL_STATUS, A.LOCATION, A.LOCATION_DESCR, A.NAME, E.EMAILID, B.EMPLID, E.OPRID, C.JPM_DESCR90
  FROM PS_NPS_VIEW_ALL A, PS_PERSON_NAME B, PSOPRDEFN E, PS_NPS_LIC_VW_RPT C
  WHERE ( A.EFFDT =
        (SELECT MAX(A_ED.EFFDT) FROM PS_NPS_VIEW_ALL A_ED
        WHERE A.EMPLID = A_ED.EMPLID
          AND A.EMPL_RCD = A_ED.EMPL_RCD
          AND A_ED.EFFDT <= SYSDATE)
    AND A.EFFSEQ =
        (SELECT MAX(A_ES.EFFSEQ) FROM PS_NPS_VIEW_ALL A_ES
        WHERE A.EMPLID = A_ES.EMPLID
          AND A.EMPL_RCD = A_ES.EMPL_RCD
          AND A.EFFDT = A_ES.EFFDT)
     AND A.EMPLID = B.EMPLID
     AND A.EMPLID = E.EMPLID
     AND A.PER_ORG = 'EMP'
     AND A.PAYGROUP NOT IN ('SUM','CWR')
     AND B.EMPLID = C.EMPLID)

I'm tried to get JPM_DESCR90 from PS_NPS_LIC_VW_RPT into my original query so that I can get one value per EMPLID .I know that its probably a simple solution but would appreciate some help

I was able to solve my problem by adding the completion dates for JPM_DESCR90 (which is a field decription/name for certifications) in my query i.e. and then looked for max values of those dates in my query by adding MAX( TO_CHAR(C.JPM_DATE_3,'YYYY-MM-DD')) to the SELECT statement. This gives me the most recently acquired certification for each employee. I have attached a snapshot of my result as a jpeg image above

SELECT A.EMPLID, A.JOBCODE, A.JOBCODE_DESCR, A.EMPL_STATUS, A.LOCATION, A.LOCATION_DESCR, A.NAME, E.EMAILID, B.EMPLID, E.OPRID, MAX( TO_CHAR(C.JPM_DATE_3,'YYYY-MM-DD'))
  FROM PS_NPS_VIEW_ALL A, (PS_PERSON_NAME B LEFT OUTER JOIN  PS_NPS_LIC_VW_RPT C ON  B.EMPLID = C.EMPLID ), PSOPRDEFN E
  WHERE ( A.EFFDT =
        (SELECT MAX(A_ED.EFFDT) FROM PS_NPS_VIEW_ALL A_ED
        WHERE A.EMPLID = A_ED.EMPLID
          AND A.EMPL_RCD = A_ED.EMPL_RCD
          AND A_ED.EFFDT <= SYSDATE)
    AND A.EFFSEQ =
        (SELECT MAX(A_ES.EFFSEQ) FROM PS_NPS_VIEW_ALL A_ES
        WHERE A.EMPLID = A_ES.EMPLID
          AND A.EMPL_RCD = A_ES.EMPL_RCD
          AND A.EFFDT = A_ES.EFFDT)
     AND A.EMPLID = B.EMPLID
     AND A.EMPLID = E.EMPLID
     AND A.PER_ORG = 'EMP'
     AND A.EMPL_STATUS='A'
     AND A.PAYGROUP NOT IN ('SUM','CWR'))
  GROUP BY  A.EMPLID,  A.JOBCODE,  A.JOBCODE_DESCR,  A.EMPL_STATUS,  A.LOCATION,  A.LOCATION_DESCR,  A.NAME,  E.EMAILID,  B.EMPLID,  E.OPRID

However, I have one more question. Is there a way to get all the certifications(JPM_DESCR90 aka Jobcodedescr column( in one row rather than in multiple rows for by getting each value of JPM_DESCR90 separated by ':' character for each employee irrespective of certification completion dates (C.JPM_DATE_3) ?


Reducing the size of a database SQL Server newbie here. I'm a MySQL guy. I'm having a look at something for a client in their 2008 SQL Server, and need some advice. Whoever designed the database chose to log insane amounts of stuff and never flush those log tables.

The largest table stores complete XML documents from transactions between the app and APIs of sites like eBay. I can only assume that the database being about 230 gigabytes hurts performance. I'm guessing these tables are not queried on in the app, but even so, I don't like the idea of such a huge database. After purging the log tables, I would anticipate a total remaining size of about 30GB.

I'd like some advice on how to go about this. From the little I've read on the subject, after deleting a bunch of data, the database file will not automatically shrink in size. I also read that shrinking and re-indexing are bad.

Is this large database hurting performance of other tables?
Should I do something about it?
How can I safely do something about it that will yield a performance increase?

If you are going to continue logging data in this database, the last thing on earth you want to do is shrink the database file (and then perform index maintenance that will require it to grow again). Never mind that these shrink and grow operations will interfere with performance, and that the end result is not going to leave you much better off than when you started.

Since the file is only going to grow again, this is an extremely futile operation - much like drying off while still in the shower. What are you going to do with all that disk space you free up temporarily? Lease it out to another application until the database needs to grow again? Of course not. If the database grew to that size once, it will grow to that size again, but it is going to be much more efficient to reuse the space within the file without all this unnecessary shrink-grow-shrink-grow roller coaster of the file itself.

Even if you move the logging table to another database, you should do what you can to pre-allocate the data file to a size that will accommodate the amount of logging you want to save (be it a week, a month, what have you). Keep this database trim by purging data every day, and stop worrying about shrinking & re-indexing. If you size it right, there should always be some free space, but not excessive free space. And if you need to re-index (you shouldn't really, if your clustered index is datetime or otherwise monotonically-based), do so after the purge (when you have the most amount of free space), not after a shrink (when you have the least).

You can do what Mark suggests without introducing a new database to the application, or changing the application or its interface to the database at all (one important change, of course, would be the removal of any foreign keys or other database-dependent features). You can simply create your table(s) in the new database, then add an INSTEAD OF INSERT trigger to the table in the current database (I assume there are no updates to the logging table, but you may also need an INSTEAD OF DELETE trigger in case you don't directly control the process that performs the purging). That will help with writes, but you'll have to point reads elsewhere, since there are no INSTEAD OF SELECT triggers. Another alternative would be to rename the existing table and create a synonym or even a view that points to the new table.

If you're going to need to clean up the logs table that has grown, I would avoid a single atomic transaction like:

DELETE dbo.logs_table WHERE [datetime] < '20121201';

This will cause massive log growth and will take a long time. Instead you can break the cleanup into chunks, e.g.

BEGIN TRANSACTION;

SELECT 1;

WHILE @@ROWCOUNT > 0
BEGIN
  COMMIT TRANSACTION;

  -- if in simple: CHECKPOINT
  -- otherwise: BACKUP LOG

  BEGIN TRANSACTION;

  DELETE TOP (1000) FROM dbo.logs_table WHERE [datetime] < '20121201';
END

I picked 1000 and Dec. 1st arbitrarily, I don't know what fits your scenario best. The point is that you want to keep transactions short and contained, and prevent any long-term impact while you clean up the table. Another option I've used in the past, instead of deleting 99% of the junk in a table, move the 1% you want to keep to a new table and drop the old one.

BEGIN TRANSACTION;

SELECT *
  INTO dbo.new_logs_table
  FROM dbo.logs_table
  WHERE [datetime] >= '20121201'

COMMIT TRANSACTION;

-- create indexes/constraints/triggers on dbo.new_logs_table

BEGIN TRANSACTION;
  DROP TABLE dbo.logs_table;
  EXEC sp_rename N'dbo.new_logs_table', N'logs_table', N'OBJECT';
COMMIT TRANSACTION;

If you say the logs have never been purged, then you may very well be in a scenario where the database will be at a size that you don't anticipate you will ever need again (say, if you only intend to ever keep a week of logs at a time). In this case I might go along with a shrink operation, but only if truly necessary (e.g. you really do need the space for other purposes). A bunch of empty pages aren't going to affect your backups or other operations, and the pages will eventually be fully deallocated and reused.


Unable to start mysqld service after enabling log_bin Hi I am unable to start mysqld service after I make changes in my.cnf to enable log-bin.
After making changes the file looks like below:

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin

#This is what I added to enable log-bin

log-bin=/var/log/mysql/
log-bin-index=bin-log.index
max_binlog_size=100M
binlog_format=row
socket=mysql.sock

#That was what I added to enable log-bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

I am using:

[ec2-user@pip my.cnf.d]$ cat /etc/redhat-release
Red Hat Enterprise Linux Server release 7.3 (Maipo)

Following is the ownership and permissions:

d]$ ls -ld /var/log/mysql
drwxrwxrwx. 2 mysql mysql 23 Sep  8 09:01 /var/log/mysql

Following is the error that I get:

[ec2-user@i.d]$ sudo service mysqld start
Redirecting to /bin/systemctl start  mysqld.service
Job for mysqld.service failed because the control process exited with error code. See "systemctl status mysqld.service" and "journalctl -xe" for details.

MySQL version is:

Server version: 5.7.19 MySQL Community Server (GPL)

Please let me know if any other detail is required

According to the manual entry for the mysqld option log-bin:

  If you specify this option without also specifying a --server-id, the server is not allowed to start.

So it seems you need something like:

[mysqld]
server-id=1
log-bin


Changing feature on a database that is a member of SQL Server AlwaysOn availability group Our developers need a change on a database that is in the Availability group (they need Service broker in one of the databases to be enabled) but when I try to do it I am getting error saying that I can’t make the change since this database is a member of an Availability group….

Is there a way to change it without taking this database out of availability group or I have to take it out (and how to do it if I have to and not to have to re-install the database again on the secondary node).

I see that source database we copied from on our current Live server has this Service broker enabled but after restoring it's backup on Azure it picked up the False state for Enabled Service Broker feature).

There is no other way to enable service broker except taking that database out of AlwaysON Availability group, enabling service broker and then joining it back.

If you know that your source database has service broker enabled, then when you restore the database, use RESTORE .. with ENABLE_BROKER, so that you dont have to go with such trouble again.

If you try to enable service broker for a database involved in AG, you will get below error :

  Msg 1468, Level 16, State 1, Line 3
  The operation cannot be performed on database "AdventureWorks2012" because it is involved in a database mirroring session or an availability group. Some operations are not allowed on a database that is participating in a database mirroring session or in an availability group.
  Msg 5069, Level 16, State 1, Line 3
  ALTER DATABASE statement failed.


Blocking drop database command I created a MySQL database and created a new user dedicated to that database with all permissions. The new user then proceeded of dropping the database in addition to all tables. I need to block that user from being allowed to drop the database, but allow him to drop anything else.

My question is: is there a server level trigger in MySQL similar to MSSql, or is there another method to do this?

Perhaps you want to remove the DROP privilege at the global user level
UPDATE mysql.user SET drop_priv = 'N' WHERE user='...' AND host='...' and db='...';
FLUSH PRIVILEGES;

Something to keep in mind: The DROP privilege encompasses databases, table, and views. Unfortunately, DROP DATABASE and DROP TABLE fall under the same privilege scope.
For example
I create a user called kumar and a database called kumar
mysql> create database kumar;
Query OK, 1 row affected (0.00 sec)

mysql> grant all on kumar.* to kumar@'%';
Query OK, 0 rows affected (0.06 sec)

mysql> use kumar
Database changed
mysql> create table rolando (a int);
Query OK, 0 rows affected (0.08 sec)

I logged into mysql as kumar and dropped the database kumar
mysql> drop database kumar;
Query OK, 1 row affected (0.07 sec)

So, I recreated it as a SUPER user. I also create a database kumar2
mysql> create database kumar;
Query OK, 1 row affected (0.00 sec)

mysql> use kumar
Database changed
mysql> create table rolando (a int);
Query OK, 0 rows affected (0.07 sec)

mysql> create database kumar2;
Query OK, 1 row affected (0.00 sec)

mysql>

I tried to drop kumar2
mysql> show grants for kumar@'%';
+--------------------------------------------------+
| Grants for kumar@%                               |
+--------------------------------------------------+
| GRANT USAGE ON *.* TO 'kumar'@'%'                |
| GRANT ALL PRIVILEGES ON `kumar`.* TO 'kumar'@'%' |
+--------------------------------------------------+
2 rows in set (0.00 sec)

mysql> select user(),current_user();
+-----------------+----------------+
| user()          | current_user() |
+-----------------+----------------+
| kumar@localhost | kumar@%        |
+-----------------+----------------+
1 row in set (0.00 sec)

mysql> drop database kumar2;
ERROR 1044 (42000): Access denied for user 'kumar'@'%' to database 'kumar2'
mysql>

Thus, kumar@'%' can only drop the kumar database. kumar@'%' cannot drop any other database.
CONCLUSION
The user kumar@'%' can drop the following:

All Views in the kumar database
All Tables in the kumar database
The kumar database itself

You can either allow all three or restrict all three fro this user's access to the kumar database.


Restore a SQL Server bacpac file in Linux I'm working to move an Azure database to an open source DB under Linux.  I've received a .bacpac file and, to start with, I thought I'd try to install MS/SQL in Linux and restore the .bacpac file so I could peruse the database.  But a restore command gives me:

$ /opt/mssql/bin/sqlpackage /a:Import /tsn:tcp:localhost /tdn:dbName /tu:SA /tp:thePassword /sf:/home/user/Downloads/dbname.bacpac
An unexpected failure occurred: Data plan execution failed with message One or more errors occurred..

Unhandled Exception: Microsoft.SqlServer.Dac.Data.DataException: Data plan execution failed with message One or more errors occurred. ---> System.AggregateException: One or more errors occurred. ---> System.AggregateException: One or more errors occurred. ---> System.AggregateException: One or more errors occurred. ---> System.UnauthorizedAccessException: Access to the path 'C:\Users\Client\Temp\tmp4D32.tmp' is denied.
   at System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath)
   at Microsoft.Data.Tools.Schema.Sql.Dac.Data.TemporaryStorageProvider.CreateTemporaryFile(Int64 maxSize)

Obviously the restore is assuming the existence of Windows paths.  Is there a way to override this that I've missed?  I'm using:

1> select @@version
2> go

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Microsoft SQL Server 2017 (CTP2.1) - 14.0.600.250 (X64)
    May 10 2017 12:21:23
    Copyright (C) 2017 Microsoft Corporation. All rights reserved.
    Developer Edition (64-bit) on Linux (Linux Mint 18.1)

(1 rows affected)

Any pointers?  My MS/SQL experience dates to a time not too long after SQL server came from Sybase so right away I'm a bit behind.  Thanks for any help.

Edit

I'll not answer my own question as this solution is incomplete but I did figure out how to limp along with this as much as possible.  Part of what I got from the current maintainer was a schema create script.  Using sqlcmd I was able to create the schema.  The .bacpac file is just a zip file and, when you extract it, you have a "Data" directory with a separate directory per table.  There is also a "model.xml" file that is basically the schema - this must be how the sqlpackage import command can create the database tables but I used a .sql file for mine.

For each directory there is one or more BCP files - ah finally something I remember from my Sybase days.  Because I had very few tables I was able to manually use bcp to load each table in succession.

This isn't the best answer as it requires more than just the .bacpac file to work but it did work for me.  If MSFT really wants people to use MS/SQL server on Linux they've got some bugs to fix.

I'd recommend trying the restore process suggested in the Microsoft Docs - https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-migrate-sqlpackage

It looks like this may help:

RESTORE DATABASE AdventureWorks
FROM DISK = '/var/opt/mssql/backup/AdventureWorks2014.bak'
WITH MOVE 'AdventureWorks2014_Data' TO '/var/opt/mssql/data/AdventureWorks2014_Data.mdf',
MOVE 'AdventureWorks2014_Log' TO '/var/opt/mssql/data/AdventureWorks2014_Log.ldf'
GO

Hope this helps with the path issue you're running into.


Find max date in multiple columns in SSIS I have csv file source and it have 5 date columns . I need to create a new column which will have the latest date after comparing  all the 5 columns .

Expample :

source
![cust id  dt1 dt2 dt3 dt4 dt5
101]1

I can think of a couple of ways that it can be done.

The first is using pure SSIS tasks and data flows. Based on your source data, the easiest approach I can think of would be to use an Unpivot Transformation followed by an Aggregate Transformation to get the values you need. Un-pivot on DT1, DT2, DT3, DT4 and DT5 and then aggregate over the date column and group by the CustID field. This would achieve what you need.

I don't have a copy of SSIS in front of me right now, but you can find the documentation for Unpivot Transformation and Aggregate Transformation on Microsoft Docs.

Alternatively, if the data is in a table (or can be put into a table), then you could just write a SQL script and call that via an Execute SQL Task to do the unpivot and aggregation for you like this:

 SELECT CustID, MAX([Date]) AS [Date]
   FROM (SELECT CustID, DT1, DT2, DT3, DT4, DT5) AS p
UNPIVOT ([Date] FOR [Dates] IN (DT1, DT2, DT3, DT4, DT5)) u;

Then you can just return the row set back into SSIS if you need to. Either method would be fine for such a small data set but test them both and see which one performs better and is easier to maintain.


Sql Query Performance I have one query for the report.

Query

Use RIS

select CİHAZ,sum(sayi) as [Çekim Sayısı], count(*) as [Hasta Sayısı] from
(select CİHAZ,[KABUL NO],[TC NO], count(*) as Sayi from

(SELECT
a.GeneralReceptionNo as [KABUL NO], pa.TRIDNO as [TC NO],
m.EtiketAdi as [CİHAZ]
FROM   Appointments a
INNER JOIN Examinations e    ON a.ObjectID=e.AppointmentID   --aobject 1392334
INNER JOIN Patients pa       ON a.PatientID=pa.ObjectID
INNER JOIN Procedures pr     ON a.ProcedureID=pr.ObjectID
LEFT OUTER JOIN Machines m   ON a.MachineID=m.ObjectID
LEFT OUTER JOIN ExaminationReportAssistanceAssociation ra ON e.ObjectID =ra.ExaminationID
INNER JOIN Hospital..Receptions r       ON r.GeneralReceptionNo =a.GeneralReceptionNo
WHERE
CONVERT (DATE,e.CompletedDate)>='2018-12-01'
AND CONVERT (DATE,e.CompletedDate)<'2019-12-30' AND e.IsCompleted =1) sorgu
GROUP BY CİHAZ,[KABUL NO],[TC NO] ) sorgu2
GROUP BY CİHAZ ORDER BY CİHAZ

My problem is ,when i wrote condition is CONVERT (DATE,e.CompletedDate)>='2018-12-01'
AND CONVERT (DATE,e.CompletedDate)<'2019-11-30' , query not ended in 50 min but

if i wrote condition is CONVERT (DATE,e.CompletedDate)>='2018-11-01'
AND CONVERT (DATE,e.CompletedDate)<'2019-11-30'
 I can get the result in 12 sec.

First example time range wider than the second example. I can't find a solution. Thanks for the help.

Why are you using the condition?

CONVERT (DATE,e.CompletedDate) >= '2018-11-01'
AND CONVERT (DATE,e.CompletedDate) < '2018-11-30'

This query is not SARGable, just rewrite your query as

e.CompletedDate >= '2018-11-01'
AND e.CompletedDate < '2018-11-30'

And ensure you have an index with CompletedData field as the first field. As far as I see the condition, it should work in the same way but much faster and will use minimal resources.

As per your comment, apart from the proposed change in your WHERE condition you need to create a new index as:

CREATE INDEX IX_Examinations_CompletedDate ON Examinations(CompletedDate)


pg_hba error while setting up repmgr Please help me to figure it out, help is much appreciated!

ERROR:

FATAL:  no pg_hba.conf entry for replication connection from host "172.18.0.4", user "repmgr", SSL on

The following is a list of my pg_hba entries:

The following is the query output from pg_hba_file_rules:

line_number | type  |   database    | user_name  |  address   |                 netmask                 | auth_method | options | error
------------+-------+---------------+------------+------------+-----------------------------------------+-------------+---------+-------
      85 | local | {all}         | {postgres} |            |                                         | peer        |         |
      90 | local | {all}         | {all}      |            |                                         | peer        |         |
      92 | host  | {all}         | {all}      | 127.0.0.1  | 255.255.255.255                         | md5         |         |
      94 | host  | {all}         | {all}      | ::1        | ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff | md5         |         |
      97 | local | {replication} | {all}      |            |                                         | peer        |         |
      98 | host  | {replication} | {all}      | 127.0.0.1  | 255.255.255.255                         | md5         |         |
      99 | host  | {replication} | {all}      | ::1        | ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff | md5         |         |
     101 | host  | {all}         | {repmgr}   | 172.18.0.0 | 255.255.255.0                           | trust       |         |
     102 | host  | {replication} | {repmgr}   | 172.18.0.0 | 255.255.255.0                           | trust       |         |

As you can see, the pg_hba.conf did get loaded to the database.
Where did I do wrong?

Finally got it working!!!

  Since the pg_hba.conf records are examined sequentially for each connection attempt, the order of the records is significant. Typically, earlier records will have tight connection match parameters and weaker authentication methods, while later records will have looser match parameters and stronger authentication methods.


Display Top X results for each unique value I am trying to figure out how I can output any machine that has had the latest 3 backups be larger than 10GB. My SQL Table looks similar to the below table:

ComputerID | BackupImageName | WindowsVolumeGUID | FileSize | TimeCreated
1234       | PCBackup.spi    | kjsdfai-dadad-ddd | 10400    | 05-22-20
1234       | PCBackup1.spi   | kjsdfai-dadad-ddd | 10500    | 05-23-20
1234       | PCBackup2.spi   | kjsdfai-dadad-ddd | 10300    | 05-24-20
1234       | PCBackup3.spi   | kjsdfai-dadad-ddd | 10900    | 05-25-20
1225       | PCBackup4.spi   | kjsdfa5-dadad-ddd | 10600    | 05-22-20
1225       | PCBackup5.spi   | kjsdfa5-dadad-ddd | 5000     | 05-23-20
1225       | PCBackup6.spi   | kjsdfa5-dadad-ddd | 10000    | 05-24-20
1225       | PCBackup7.spi   | kjsdfa5-dadad-ddd | 4588     | 05-25-20

I need to output all machines (computerID's) or Volumes (WindowsVolumeGUID) that have their latest 3 files in the list greater than 10000 file size. So from the above table it would output the computerID of 1234 because its latest 3 are greater than 10000 file size and even though the computerID 1225 has 2 files greater than 10000 it would not output the result because there is a break in-between the two large files. The search will need to take into account the WindowsVolumeGUID since some machines have more than one volume and I need to take into account the individual volume backups and count the latest entries based on that.

Alternatively, I can also accomplish the same thing if there is a way to output the top 3 results for each unique WindowsVolumeGUID and not just across the board.

Any help would be greatly appreciated!

Incoming schema and test data

CREATE TABLE `db_info` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `ComputerID` int(10) unsigned NOT NULL,
  `BackupImageName` varchar(32) NOT NULL,
  `WindowsVolumeGUID` varchar(32) NOT NULL,
  `FileSize` int(10) unsigned NOT NULL,
  `TimeCreated` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `ComputerID` (`ComputerID`),
  KEY `WindowsVolumeGUID` (`WindowsVolumeGUID`),
  KEY `FileSize` (`FileSize`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `db_info` (`ComputerID`, `BackupImageName`, `WindowsVolumeGUID`, `FileSize`, `TimeCreated`) VALUES
(1234, 'PCBackup.spi ', 'kjsdfai-dadad-ddd', 10400, '2020-05-22'),
(1234, 'PCBackup1.spi', 'kjsdfai-dadad-ddd', 10500, '2020-05-23'),
(1234, 'PCBackup2.spi', 'kjsdfai-dadad-ddd', 10300, '2020-05-24'),
(1234, 'PCBackup3.spi', 'kjsdfai-dadad-ddd', 10900, '2020-05-25'),
(1225, 'PCBackup4.spi', 'kjsdfa5-dadad-ddd', 10600, '2020-05-22'),
(1225, 'PCBackup5.spi', 'kjsdfa5-dadad-ddd', 5000, '2020-05-23'),
(1225, 'PCBackup6.spi', 'kjsdfa5-dadad-ddd', 10000, '2020-05-24'),
(1225, 'PCBackup7.spi', 'kjsdfa5-dadad-ddd', 4588, '2020-05-25');

Requested Query

SET @n := 0, @lastComputerId := NULL;
SELECT ComputerId, SUM(IF(FileSize >= 10000, 1, 0)) AS large_backups_count
FROM (
    SELECT ComputerId, FileSize
    FROM `db_info`
    WHERE (@n:=(@n * IF(@lastComputerId <=> ComputerID, 1, (@lastComputerId := ComputerID) AND 0) + 1)) < 4
    ORDER BY ComputerID, TimeCreated DESC
) AS tmp
GROUP BY ComputerId
HAVING large_backups_count >= 3;

Result:

+------------+---------------------+
| ComputerId | large_backups_count |
+------------+---------------------+
|       1234 |                   3 |
+------------+---------------------+

Alternative query based on total size of recent three backups :

SET @n := 0, @lastComputerId := NULL;
SELECT ComputerId, SUM(FileSize) AS sum_of_recent_three_backups
FROM (
    SELECT ComputerId, FileSize
    FROM `db_info`
    WHERE (@n:=(@n * IF(@lastComputerId <=> ComputerID, 1, (@lastComputerId := ComputerID) AND 0) + 1)) < 4
    ORDER BY ComputerID, TimeCreated DESC
) AS tmp
GROUP BY ComputerId
HAVING sum_of_recent_three_backups >= 30000;

Result:

+------------+-----------------------------+
| ComputerId | sum_of_recent_three_backups |
+------------+-----------------------------+
|       1234 |                       31200 |
+------------+-----------------------------+


When using an ORM, what are some things to watch out for in your database design What are some database design gotchas to watch out for when you know the database will be accessed using a Object Relational Mapper (ORM) Wikipedia? Also see Entity Framework NHibernate or LLBLGenPro.

As an example, I'll note the 2100 parameter limit on SqlServer's RPC call.  This is a problem when using LLBLgen and joining tables when compound primary keys were utilized see MSDN article for compound keys.

Go through standard normalization techniques, mainly to 3rd normal form. Some ORMs can pick up on the relationships defined at the database level so you won't have to manually.

I would really go the other way and ask what should I know about ORMs. Make sure you know:
 - How do I profile the queries?
 - How can I override the ORM and write my own query?
 - Can I use stored procs instead?

The database should be ORM agnostic since, more than likely, it will outlast the app.


Which row is selected by DISTINCT? I have a table with products with names in different languages:

product-id | lang-no | name

Now I want to generate product lists with names in each language. Unfortunately I don't have all names in all languages and must fall back to other languages if the right one isn't availabe.

Getting a list with a name in any language is easy:

SELECT DISTINCT product-id, name FROM mytable;

Is it defined which row gets picked by the database for the distinct product-id ? (MySQL in my case.)

How can I generate a list for language 1 and one for language 2 ?

Bonus: Can I define which language to fall back on ?

Update:

So my initial query is wrong. (Thanks!)

My list needs to include all products.

SELECT DISTINCT product-id FROM mytable

Now I need to add the names, preferrablx in one language, but no one language covers all products.

Your query will give all unique combinations of product-id and name in the table, so if for example product id 123 has name "Pencil" in English and "Crayon" in French, you will get:

123 | Pencil
123 | Crayon

The results are deterministic in that all unique combinations that exist in your table, will be returned in that query. There isn't an option of which row will be picked, as the list of columns in the query determines what you want the results to be "unique" by. For example if you have product ID 123 with "Pencil" in both British English and American English, the query will return just the single one 123 - Pencil.

To get a list for language 1 (let's say I want it in English and that is language number 567):

select distinct product-id, name from mytable where lang-no = 567

Please clarify how the "fallback" language should work - do you have a specific language in which you know you will always have ALL products? Once you clarify that we can edit the answer accordingly.


Azure database - unable to locate in my Azure account I have an Azure account where a developer that used to work with me has set up an MSSQL database on an Ubuntu VM.

The PHP (CodeIgniter) system I am working on logs in (so database queries work correctly). I have the following information available:

The connection string uses "sqlsrv" driver in CodeIgniter
The hostname in the CI config is "127.0.0.1".
Using "top" command, I can see "sqlservr" using resources (12.8% mem, 0.7% CPU)
I can run SQL scripts with "sqlcmd -S localhost"

But yet, I can't find anywhere where this database is listed in my Azure portal. Very frustrating. There is nothing listed under "SQL databases" and I have been through all the visible options in the Linux VM, and can't find it.

What am I missing?

The connection information suggest the SQL instance is simply a local SQL Server instance installed in the VM like any other software so you don't use the Azure portal to manage it. Instead, ssh into the VM and run systemctl status mssql-server --no-pager to see if mssql-server.service is listed.

The instance could also be a docker database container, in which case you can use docker ps to list the docker containers running on the machine.


Why no rows in sys.dm_os_cluster_* on Availability Group? Running into some issues with failovers on an Availability Group with two nodes and a file share witness.  I read a post  and then I tried to get the HealthCheckTimeout value from sys.dm_os_cluster_properties and no rows are returned.  My google skills are either not working well this morning or no one else has this problem.  I was able to get the values out of the Failover Cluster Manager.  I want to know why I get no rows out of the cluster DMVs when I do have a cluster.  I am assuming I have something set up wrong.

SQL SERVER 2016 SP1 CU2
WINDOWS SERVER 2016

I also don't get any rows on 2012 SQL and Server versions.

Thanks in advance.

This dmv is for Failover Cluster Instance also known as FCI and not for Windows Server Failover Cluster (WSFC).

  These properties are used to set the values that affect failure
  detection, failure response time, and the logging for monitoring the
  health status of the SQL Server failover cluster instance.

Details here:

https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-os-cluster-properties-transact-sql


Postgres evaluates syntax error as boolean in Update statement Recently I came across a bug in code, equivalent to this snippet:

create table testing(num int, dt date, istrue boolean);

insert into testing values (1, '2018-01-01', True);

UPDATE testing SET num = 2, istrue = null AND dt = '2018-01-01';

The UPDATE statement should have actually been:

UPDATE testing SET num = 2, istrue = null WHERE dt = '2018-01-01';

Yet postgresql happily accepted the UPDATE as valid and it evaluated the code:

null AND dt = '2018-01-01'

after the second equal sign as a boolean and therefore updated data based on bad logic.

I have tried this in other SQL flavors and it is not allowed, they want a WHERE.

I'm not a postgres person but it does seem like a very odd syntax quirk, or is it a bug?

Yet postgresql happily accepted the UPDATE as valid and it evaluated the code:
null AND dt = '2018-01-01'

after the second equal sign as a boolean and therefore updated data based on bad logic.

Bad things happen where there is bad code. In this case, the code was not equivalent to the logic you wanted, and had replaced the WHERE with AND. This kind - of bad substitution - often results in errors. The rare times that it doesn't result in error you get this - bad - results where you get different/unexpected results from a query or - even catastrophic - you update the whole table where you wanted to update a few rows.

I have tried this in other SQL flavors and it is not allowed, they want a WHERE.

I don't know of any DBMS that requires WHERE in an UPDATE statement. SQL Server, Oracle, DB2, SQLite, MySQL, neither of them requires it. That is the job of the database developers, code reviewers and testing - to assure that code aligns with business logic and requirements.

I'm not a Postgres person but it does seem like a very odd syntax quirk, or is it a bug?

It's definitely not a bug. The code is parsed and executed - as you correctly assumed - as:
UPDATE
    testing
SET
    num = 2,
    istrue = ( null AND (dt = '2018-01-01') )
;

resulting in updating the whole table. istrue will be updated with a value of NULL for the rows where dt is NULL or equal to '2018-01-01' and with a value of FALSE for the rest of the rows.


Clone a Live MySQL Database We have a staging server and a production server, both running their own MySQL server. Data on the production MySQL server changes all the time.

Occassionally we want to update the data on the staging MySQL database using data ob the production MySQL server. This allows us to play around on the staging server with an updated copy of the production data.

What is the recommended way of performing this clone/sync from a live server to another?

No and yes.

No -- in that you would need to dump the data from your single server; this is invasive and costly.

Yes -- in that there are several ways of setting up Replication to make this task a breeze, possibly near-instantaneous, regardless of dataset size.

With a Master (Prod) + Slave, you could disconnect the Slave to use it as Staging; it would be up to date to the moment when it was grabbed.  But if you modify the Staging server, it could not be put back as the Slave.

Galera -- Have 3 (or more) nodes.  Removing a node from the cluster is easy.  Adding a node to the cluster is easy -- the dump+reload is automatic.  Then the grabbed node becomes Staging; then throw it away.

LVM -- This requires setting up the Prod machine with LVM before building the database.  However, a one-minute downtime is all it takes to grab a snapshot of the prod machine.  You then have hours to copy (perhaps via rsync) to the Staging server.


When creating a many to many relation with an attribute, should I include a non-composite primary key? For example, I have frame and voltage tables, both of which relate to a motor object:

frame(id, motor_attribute);
voltage(id, voltage_attribute);

I can pick a frame and pick a voltage and for each combination of those there is an assigned efficiency attribute.

I can thus create an efficiency table, but how?  I see two choices:

efficiency(frame_id, voltage_id, efficiency_attribute);
//composite key = (frame_id, voltage_id)

OR

efficiency(id, frame_id, voltage_id, efficiency_attribute);
//composite key = (frame_id, voltage_id)
//primary key = (id)

Do I include a non-composite primary key into the table?

What are voltage and efficiency?  Simple numbers (SMALLINT) or FLOATs?  Then do not normalize them and do not use a many:many table.  Simply have the value in the table instead of an id pointing to the value.  There is such a thing as "over-normalization"; it does not scale well.

Use many:many mapping tables for relating things that have lots of attributes.

Back to your question...  A many:many table does not need a surrogate id for the PK.  It is not just OK to have a composite PK, it is actually more efficient to have such.  Save AUTO_INCREMENT for cases where there is no good "natural PK".

Other tips on many:many tables.  Note that it advises having a secondary key for when you need to 'go the other direction'.

(Based on Comment):

Using ENUMs:

voltage ENUM('unknown', "460/60/3", ...)  NOT NULL
efficiency ENUM('unknown', "ie1", ...)  NOT NULL

Using a string:

voltage VARCHAR(15)   CHARACTER SET ascii NOT NULL DEFAULT ''
efficiency VARCHAR(8) CHARACTER SET ascii NOT NULL DEFAULT ''

Normalizing:

voltage_id TINYINT UNSIGNED NOT NULL ... and another table
efficiency_id ...

If there are very few choices, and the list of choices changes only very rarely, ENUM may be optimal.  The 'string' option is not too bad since the strings are rather short.  I feel that the normalizing is "over-normalizing" in this case, namely only a few short strings.


Sum of previous n number of columns based on some category I have following input for which I need to calculate the sum of values for previous x number of weeks for each category.

If the x is 3 the output would look like this:

Note that the last value is 49 because it added only last two week's values to the current week since x=3.

I am looking to write the SQL as a stored procedure and need some help on appropriate ways of doing this.

With the help from @sp_BlitzErik I tried to use LAG but couldn't quite get where I need to. Here is my query:

SELECT category
    ,year
    ,week
    ,value
    ,(
        LAG(value, 1, 0) OVER (
            ORDER BY category
                ,year
                ,week
            ) + LAG(value, 2, 0) OVER (
            ORDER BY category
                ,year
                ,week
            ) + value
        ) AS cumulative_value
FROM valuedata

And the output is not quite correct yet:

On SQL Server 2014 and 2016 1 you can use a WINDOW function (i.e. an OVER clause) to perform what you want:

SELECT
     category, year, week, value,
     sum(value) OVER (PARTITION BY category
                          ORDER BY year, week
                              ROWS 2 PRECEDING) AS retention_value_3_weeks
FROM
     t
ORDER BY
    category, year, week ;

And this is the result you'll get:

category | year | week | value | retention_value_3_weeks
:------- | ---: | ---: | ----: | ----------------------:
a        | 2016 |    1 |     5 |                       5
a        | 2016 |    2 |     7 |                      12
a        | 2016 |    3 |     8 |                      20
b        | 2016 |    3 |     6 |                       6
b        | 2016 |    4 |    15 |                      21
b        | 2016 |    5 |    25 |                      46
c        | 2016 |    3 |    25 |                      25
c        | 2016 |    4 |     2 |                      27
c        | 2016 |    5 |    21 |                      48
c        | 2016 |    6 |    26 |                      49

NOTE that the x = 3 of your example gets translated to (the current row and the 2 preceding ones).

If, for some reason, you can't use the OVER clause, you can still compute the same results using some (quite convoluted) subqueries:

SELECT
    category, year, week, value,
    (SELECT
        sum(value)
     FROM
        (SELECT TOP 3                    /* total number of rows to consider */
             value
        FROM
           t t2
        WHERE
               t2.category = t.category  /* partition by category */
           AND t2.week <= t.week         /* current and preceding rows */
        ORDER BY
           year DESC, week DESC          /* order by criteria */
        ) AS q
     ) AS retention_value_3_weeks
FROM
    t
ORDER BY
    category, year, week ;

Check it all at dbfiddle here

If you want to use @x instead of 3, you can do so:

DECLARE @x AS INTEGER = 3;

SELECT
    category, year, week, value,
    (SELECT
        sum(value)
     FROM
        (SELECT TOP (@x)                  /* total number of rows to consider */
             value
        FROM
           t t2
        WHERE
               t2.category = t.category  /* partition by category */
           AND t2.week <= t.week         /* current and preceding rows */
        ORDER BY
           year DESC, week DESC          /* order by criteria */
        ) AS q
     ) AS retention_value_3_weeks
FROM
    t
ORDER BY
    category, year, week ;;

dbfiddle here

1) Can't test with SQL Server 2012 because I don't have one. The documentation from MS SQL Server indicate it should be available since version 2008.


Backup database by copying the MDF and LDF files I want to backup a SQL Server database by copying its files manually. Is copying the database.MDF and database.LDF files enough or should I also add more files?

It might be not the proper way for backing up a database, but in certain cases it might help for testing purposes for example.

Suppose you are left with a corrupted OS with no any DB backups ,but you can still plug it as HDD and get those files containing the data available in C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQL2008\MSSQL\DATA and run the following commands

 Create database dbname.

 On.

 (
 Filename= 'mymdf.MDF',
 Filename ='mylog.LDF'. ).

 For attach;

Is there a reason for you to do this? Why don't you use:

backup database x to disk...?

Try at least to list all files of your database:

select
   db_name(database_id) as Database_Name,
   physical_name,
   name,
   state_desc
from sys.master_files
where
   db_name(database_id) ='Your_Database'

After this, set your database OFFLINE (this will stop the database):

alter database [Your_Database] set offline

Then, copy your files with ctrv+c, ArcServe or whatever.
Set it Online again:

alter database [Your_Database] set online


DELETE query against a linked server issues error I have a query that is part of a bigger process, and runs against a linked server.

The code is like this:

DELETE FROM [LinkServerIP].[Database].[dbo].[TableName]
WHERE NOT EXISTS (
    SELECT *
    FROM [LinkServerIP].[Database].[dbo].[AnotherTableName] t
    WHERE t.[Code]=[LinkServerIP].[Database].[dbo].[TableName].[Code]
);

Now, when I run this I get:

  Msg 4104, Level 16, State 1, Procedure xxxx, Line 113 The multi-part identifier "LinkServerIP.Database.dbo.TableName.Code" could not be bound.

Any ideas?

Just use the syntax that allows you to define an alias:

DELETE t1 FROM Server.Database.dbo.Table1 AS t1
WHERE NOT EXISTS
(
  SELECT 1 FROM Server.Database.dbo.Table2 AS t2
  WHERE t2.Code = t1.Code
);

Or you can use dynamic SQL to simplify:

DECLARE @sql NVARCHAR(MAX) = N'DELETE t1 FROM dbo.Table1 AS t1
  WHERE NOT EXISTS
  (
    SELECT 1 FROM dbo.Table2 AS t2
    WHERE t2.Code = t1.Code
  );';

EXEC Server.Database..sp_executesql @sql;

This forces the query to be run on the other side, which can provide other benefits too...


What can be done, if anything, to tune a SharePoint database? SharePoint uses a single table (i.e. dbo.AllUserData) with columns like nvarchar1, nvarchar2, ..., nvarchar64 or int1, int2, ..., int16 (193 columns altogether) to store all items from all lists in all site collections.

Can creating new indexes, recomputing statistics, etc. help?
Let's forget for now that it very likely to lose Microsoft support for SharePoint installation modified like this.
Am I right in thinking that such a design makes all classic databases tuning techniques useless?
Has anyone ever tried to solve performance problems of SharePoint in any other way than adding more/faster hardware?

There is not a lot you can do to tune the database it self.  You aren't allowed to create indexes, or statistics.

Keep your content databases small.  If they get to 100 Gigs you'll want to add another content database (there's ways to do this through the SharePoint UI).

As the tables grow you will want to be sure that you are doing index rebuilds and updating stats regularly.

You can look at using the EBS feature if you are using SharePoint 2010 (might be in SharePoint 2007 as well) which will allow you to move the blob data out of the database and store it on the file system using SQL Server's FILESTREAM feature.

Short of that there isn't much you can do besides throwing more hardware at the problem.


Select statement to table with percentage column names I am selecting data from one table that have percentage sign in some column names like:

| name | val% | vale2% |

This table is ofted use with select * from table, but in this case I need only the field names with percentage sing, when I run a select in query tool of pgadmin this notice this error:

ERROR:  syntax error at or near "as"
LINE 1: SELECT val% as value, val2% as value2 from table

How I can run this query and get only val% and val% without the rest of fields.

NOTE: I can change the database structure, only query it data

Moved from comment to answer, as it seemed to resolve the OP's problem:

It sounds like the actual column names are causing your problem. If so, then you need to quote them somehow. Depending on the settings for your database:

SELECT "val%" as value, "val2%" as value2 from table

may work. In some SQL variants, you can use square brackets [] to quote column names that conflict with language keywords and operators:

SELECT [val%] as value, [val2%] as value2 from table


How costly is opening and closing of a DB connection? How CPU intensive is opening and closing of a DB connection (for a web app) in MySQL

... when the DB software is on localhost?
... when the DB software is on another machine?

Think about the amount of memory being allocated per DB Connection. What things must be allocated? According to MySQL 5.0 Certification Study Guide, page 357:

  The server maintains several buffers for each client connection. One
  is used as a communications buffer for exchanging information with the
  client. Other buffers are maintained per client for reading tables
  and performing join and sort operations.

What settings govern the per-connection buffers?

join_buffer_size
sort_buffer_size
read_buffer_size
read_rnd_buffer_size
tmp_table_size / max_heap_table_size
net_buffer_length / max_allowed_packet
thread_stack

It takes time to allocate and deallocate these buffers when a connection comes into being. Don't forget to multiple the sum of those values by max_connections. As a side note, please refrain from using mysql_pconnect as PHP and MySQL's persistent connections do not get along well. Here are two informative links on this topic:

Open bug on PHP Not Cleanly Closing MySQL Connections on Apache Death.
Requesting a Persistent ssh Connection.

In heavy-read, heavy-write environment, such as OLTP, this would be expensive in terms of RAM usage and possible inhibition due to swapping in the OS. On a low-write, low_read website, I would not worry as much.


backing up a database into a new folder I'm writing a script that will create a new folder and backup all databases into that folder. The script will generate the new folder, but will not save the backups into that folder. I'm not sure what I'm missing. Thank you in advance.

DECLARE @name VARCHAR(50) -- database name
DECLARE @path VARCHAR(256) -- path for backup files
DECLARE @fileName VARCHAR(256) -- filename for backup
DECLARE @fileDate VARCHAR(20) -- used for file name
DECLARE @DirTree TABLE (subdirectory nvarchar(255), depth INT)

SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)

SET @path = '\\noc2-storage\IT_Backup\Weekly_SQL\CDADB1\' + @fileDate;

DECLARE db_cursor CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb')  -- exclude these databases

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name

WHILE @@FETCH_STATUS = 0
BEGIN
IF NOT EXISTS (SELECT 1 FROM @DirTree WHERE subdirectory = @path)
EXEC master.dbo.xp_create_subdir @path
       SET @fileName = @path + @name + '_' + @fileDate + '.BAK'
       BACKUP DATABASE @name TO DISK = @filename

       FETCH NEXT FROM db_cursor INTO @name
END

CLOSE db_cursor
DEALLOCATE db_cursor

Thank you in advance

Why are you reinventing the wheel .. as there is a cost to reinvent it !

You are also using deprecated stuff e.g. sysdatabases .. instead use sys.databases.

You should use -

Ola's backup solution
Minionware's backup solution

Both  are free and are well tested by the community.


Network Maintenance and High Availability Group Prep We have had a RAID card on one of our switches die, and the replacement had issues which caused my availability groups to failover multiple times between my 2 nodes like a game of tennis and left a few databases in an unhealthy state.

Obviously, the card needs to still be replaced again and I want to be able to set a maintenance setting to not have the databases failover repeatedly for what will be a brief blip. This will take place at a time of low activity this time, not peak activity.

Are there any best practices relating to this?
What would be the safest way to leave the databases online for most of the time (except the blip) and not have them failover multiple times?

Do I shut down the inactive node? Do I stop data movement?

We have 2 nodes running SQL 2016 Standard and 50 availability groups.

Any tips or ideas would be great.
Thank you very much

If you change the failover mode to "manual" then they should not failover automatically.


Build a Hyper-V template w/ SQL Server 2016 pre-installed The problem that I've encountered is in renaming the "template" to the "production" server name. SQL Server sys.servers, server_id =0 retains "template", not "production".

The only way around this seems to be to re-install SQL Server -- defeating the purpose of a template.

Note: sp_addserver has been discontinued; sp_addlinkedserver cannot create server_id =0 (local).

From https://docs.microsoft.com/en-us/sql/database-engine/install-windows/rename-a-computer-that-hosts-a-stand-alone-instance-of-sql-server :

sp_dropserver <old_name>;
GO
sp_addserver <new_name>, 'local';
GO

Restart the instance of SQL Server.

For a named instance:

sp_dropserver <old_name\instancename>;
GO
sp_addserver <new_name\instancename>, 'local';
GO

Restart the instance of SQL Server.

Note you cannot rename the instance part of the name via this method.


MySQL fulltext query is very slow | What I'm doing wrong? I have a very simple table with 100,000 rows.
CREATE TABLE test_database.products (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `city_id` int  NOT NULL,
  `market_id` int NOT NULL,
  `product_id` int NOT NULL,
  `search` varchar(450) COLLATE utf8mb4_unicode_ci NOT NULL,
  `quantity` int NOT NULL,
  `price` varchar(15) COLLATE utf8mb4_unicode_ci NOT NULL,
   PRIMARY KEY (`id`)
)
ENGINE=InnoDB
DEFAULT CHARSET=utf8
COLLATE=utf8_general_ci;

I added fulltext:
ALTER TABLE products ADD fulltext (search);

And when I search:
    SELECT
        id, market_id, product_id, quantity, price,
        MATCH(pr.search) AGAINST('dolor* qui*' IN BOOLEAN MODE) as srv
    from
        products pr
    where
        pr.city_id = 1 and
        MATCH(pr.search) AGAINST('dolor* qui*' IN BOOLEAN MODE)
    ORDER by
        srv DESC;

It takes ~ 2500 ms.
Explain select ...
id|select_type|table|partitions|type    |possible_keys|key   |key_len|ref  |rows|filtered|Extra                        |
--|-----------|-----|----------|--------|-------------|------|-------|-----|----|--------|-----------------------------|
 1|SIMPLE     |pr   |          |fulltext|search       |search|0      |const|   1|    10.0|Using where; Ft_hints: sorted|

MySQL version:
 Ver 8.0.21 for Win64 on x86_64

What I'm doing wrong?

Give this formulation a try:
SELECT ..., x.relevance
    FROM (
        SELECT  id,
                MATCH(...) AGAINST(...) AS relevance
            FROM products
            WHERE MATCH(...) AGAINST(...)
         ) AS x
    JOIN products AS pr  USING(id)
    WHERE pr.city_id = 1
    ORDER BY x.relevance DESC


Multi-column uniqueness constraint, checking a field has one of two values I'd like to enforce that only one row in a database matches value A in column 1, and either value B or value C in column 2. A small example

create table builders (
    id text serial
);
create table houses (
    id text serial,
    status text,
    builder_id text references builders(id)
);

The house status can be "NOT_STARTED", "PLANNING", "CONSTRUCTING", "BUILT" or integers mapping to these values or whatever you want.

There's a rule in my system where a builder can only plan or construct one house at a time, that is, if there are two house rows with builder Id 1 and one has status "PLANNING" and the other has status "CONSTRUCTING", the system is in an inconsistent state. I'd like to enforce this in the database.

I tried doing

CREATE UNIQUE INDEX CONCURRENTLY builders_unique_house
ON houses ("status", "builder_id")
WHERE "status" IN ('PLANNING', 'CONSTRUCTING');

I was able to add this index, and it can detect two houses in PLANNING or two in CONSTRUCTING, but it allows one to be in PLANNING and one to be in CONSTRUCTING.

Is there an index or constraint I can add to express this rule? Another possibility I had was adding a boolean to represent "planning or constructing", like in_progress or something and then adding a unique key on that.

You need to remove the status column from your index:

CREATE UNIQUE INDEX builders_unique_house
   ON houses (builder_id)
WHERE status IN ('PLANNING', 'CONSTRUCTING');

If you keep the status column, the index will contain e.g. ('PLANNING', 1) and ('CONSTRUCTING', 1) - that combination is unique.

If you remove the status, only the builder_id will be indexed, but only if one of the status values are set. And then trying to index "the other" status will lead to the desired unique index violation.