Returning a True or False from an IIF statement I have a query with the following IIF statement

OnlineEnrolment: IIf([qry_ebs_final_CAN-ENROL-ONLINE].[SP_CAN_ENROL]='Y',True,False)

It gives the correct results, but the format that it gives them is what I am struggling with.  It responds back with 0 or -1.   I want it to respond TRUE or FALSE.

How can I change this other than making TRUE and FALSE strings?  I still want them as boolean values.

Thanks

Kieran

This is how TRUE and FALSE work in VBA (and Access): https://stackoverflow.com/questions/4275800/what-are-the-integer-values-of-boolean-false-and-true-in-vb6

If you want a 1 and a 0 like most other programming languages, return 1 and 0 and evaluate that instead of using the keywords TRUE and FALSE. That will make it consistent with other languages, but it won't be internally consistent with Access/VBA.

Something like this:

    IIf([qry_ebs_final_CAN-ENROL-ONLINE].[SP_CAN_ENROL]='Y',1,0)


Consolidated realtime mysql backup with master/slave setup or binlog I have 20+ mysql servers and their have low write requirements.

So, I want to see if any way I can replicate the data/binlog from these servers to a single slave so realtime backup purpose.

If I have only a single master, then replication is easy, however, in this case, I have 20+.

Are there any easy way if I don't want to setup 20+ mysql salve on a single server?

Till MySQL 5.6 released version, Multiple master to single slave replication is not possible. This type of replication is under development in MySQL 5.7 version.

you can refer below link:-

MySQL 5.7 Multi-Source-Replication


What is the Wait Type "QDS_Shutdown_Queue" referring to? I'm gathering wait types on a load test environment, and the second biggest wait type is QDS_SHUTDOWN_QUEUE. The stats are coming from sys.dm_os_wait_stats, but I can't find any documentation that explains what that particular type means.

I'm running SQL Server 2014, SP1 on a physical Windows 2008 R2 instance (no VM involved).

It's Query Data Store related and you can safely ignore it.
Query store is a new feature in SQL 2016.
Refer to http://www.sqlskills.com/blogs/bobb/more-xevents-in-sql-server-2014-ctp1-dont-wait-any-longer/
Edit:
Below confirms that I was correct - You can safely ignore/filter out this wait type.
Paul Randal has commented in his Wait statistics, or please tell me where it hurts

Dave Mc: Any thoughts on QDS_SHUTDOWN_QUEUE? It’s currently the top reported wait from one of our SQL 2014 prod instances.
Paul Randal : It’s for the Query Store in 2016 – you can filter it out.


Large table size, affecting usage I have the following database table, that saves all outgoing emails to students:

    CREATE TABLE [dbo].[tblEmailsSent](
        [id] [int] IDENTITY(1,1) NOT NULL,
        [Sent] [datetime] NULL,
        [SentByUser] [nvarchar](50) NULL,
        [ToEmail] [nvarchar](150) NULL,
        [StudentID] [int] NULL,
        [SubjectLine] [nvarchar](200) NULL,
        [MessageContent] [ntext] NULL,
        [ReadStatus] [bit] NULL,
        [Folder] [nvarchar](50) NULL,
     CONSTRAINT [PK_tblMessages] PRIMARY KEY CLUSTERED
    (
        [id] ASC
    ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
     IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
    GO

The table currently stores 18,700 records.

I have a number of issues, which I guess might be related:

The table, for some reason, weighs 460 MB - which means I am pretty much hitting the limit of the hosting package. I wouldn't have thought that 18,700 rows would weigh so much. Is there something that can be done about this?
When I load the top X rows from the table, the response is VERY slow. We are talking about a run time of a little more than 2 minutes (in SSMS), for a simple query that looks like this:

SELECT top 500  *
FROM tblEmailsSent
ORDER BY id desc

The following query gives zero records, when I know for a fact (ie, i can see in the table,) that emails were sent during the day of the 18th:

 SELECT id FROM tblEmailsSent
 WHERE  convert(datetime,[sent],103) = convert(datetime,'18/11/2016',103)

I am using Microsoft SQL Server 2005 - 9.00.5000.00.

I am using ntext because i am using Unicode (for Hebrew support).

SET STATISTICS TIME ON for query 2 returns:

(500 row(s) affected).

SQL Server Execution Times:
    CPU time = 141 ms,  elapsed time = 112347 ms.

A SELECT AVG(DATALENGTH(MessageContent)) query returns 23,363.

I changed the MessageContent column to nvarchar(MAX), and ran query 2 again:

SQL Server Execution Times:
    CPU time = 47 ms,  elapsed time = 116726 ms.

...only a few seconds different from before.

The server is remote. It generally responds fairly quickly, but these results for such a simple query are really very very slow.

Using SET STATISTICS IO ON returns:

Table 'tblEmailsSent'.
 Scan count 1, logical reads 17, physical reads 0, read-ahead reads 354,
 lob logical reads 3308, lob physical reads 660, lob read-ahead reads 0.
SQL Server Execution Times:
 CPU time = 62 ms,  elapsed time = 107245 ms.

Try rebuilding all indexes on your table to see if you can reclaim free space due to fragmentation.  If you can move to SQL Server 2016 SP1, you could take advantage of compression.
Are you sure the actual query is taking 2 minutes or is it SSMS that is taking 2 minutes to display the results.  The grid in SSMS is notoriously slow.  Before running your query, run SET STATISTICS TIME ON.  Run your query and look in messages for:

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

That would be the actual amount of execution time for the query.
select convert(datetime,'18/11/2016',103) returns midnight as far as time, so unless those emails were sent at exactly midnight, you won't get a match.  If you want to see emails for that date, use

SELECT id FROM tblEmailsSent
 WHERE  convert(date,[sent],103) = convert(date,'18/11/2016',103)

Posting the xml for the "actual" execution plan "might" provide us with additional information. I wouldn't think the order desc would be causing any real problem - add SET IO STATISTICS ON before you run the query to see how much logical vs physical I/O you're doing.

One thing to do as an experiment would be to change the select to just retrieve ID only - how long does that run.  Add the next column - how long does that run and so on.  Perhaps your LOB reads are really where the slow down is - if that's the case, I'm not sure what you'd be able to do about that.  Starting with a 'minimum number of columns and adding additional columns on each subsequent run may provide some insight as to what is bogging down your query.

Also, check out this Q & A that has some excellent discussion of LOB data - - LOB_DATA, slow table scans, and some I/O questions


SQL beginner trying to calculate total time in minutes; need help with WHERE syntax error I'm trying to calculate the total minutes employees were on a system.
The data, stored as 'Action' looks something like below. Note that employees log on and off multiple times a day.

INSERT INTO Activity (empid, action, log_date) VALUES
('a1', 'ON', '2019-01-01T07:00:00.000Z'),
('a1', 'OFF', '2019-01-01T07:30:00.000Z'),
('a1', 'ON', '2019-01-01T08:30:00.000Z'),
('a1', 'OFF', '2019-01-01T11:30:00.000Z'),
('a1', 'ON', '2019-01-01T12:30:00.000Z'),
('a1', 'OFF', '2019-01-01T12:45:00.000Z'),
('a2', 'ON', '2019-01-01T08:45:00.000Z'),
('a2', 'OFF', '2019-01-01T09:15:00.000Z'),
('a2', 'ON', '2019-01-01T10:00:00.000Z'),
('a2', 'OFF', '2019-01-01T11:45:00.000Z');

Below is the SQL query code that I have been working on so far, but it's returning 'Query Error: error: syntax error at or near "WHERE"'.

SELECT e.empid, CAST(e.log_date AS DATE) AS date,
SUM(DATEPART(MINUTE, CAST(e.log_date - s.log_date AS TIME))) AS work_minutes
FROM (SELECT empid, log_date
      FROM Activity
      WHERE action = 'ON') AS s
JOIN (SELECT empid
      FROM Activity
      WHERE action = 'OFF') AS e
ON e.empid = s.empid
WHERE e.log_date = (SELECT log_date
                    FROM Activity
                    LIMIT 1
                    WHERE empid = e.empid
                    AND log_date > s.log_date)
GROUP BY e.empid, CAST(e.log_date AS DATE);

What seems to be the problem? I want the query result to look something like this:

|---------------------|------------------|------------------------|
|         empid       |        date      |      work_minutes      |
|---------------------|------------------|------------------------|
|          a1         |    2019-01-01    |        100 minutes     |
|---------------------|------------------|------------------------|
|          b2         |    2019-01-01    |        150 minutes     |
|---------------------|------------------|------------------------|

(work_minutes values above are random)

Any help will be greatly appreciated.
Thank you!

You are overcomplicating things.

You only need one derived table (aka "sub-select"). Using a lateral join makes it possible to push the values from the outer query into the sub-select:

select a1.empid, a1.log_date::date, sum(a2.log_date - a1.log_date) as duration
from activity a1
  join lateral (
    select min(off.log_date) as log_date
    from activity off
    where off.action = 'OFF'
      and off.log_date > a1.log_date
      and off.empid = a1.empid
  ) a2 on true
where a1.action = 'ON'
group by a1.empid, a1.log_date::date;

This assumes that an OFF event always comes after an ON event.

a2.log_date - a1.log_date returns an interval. Do turn that into minutes, extract the epoch value from that interval which is the interval in seconds. Then divide that by 60:

extract(epoch from sum(a2.log_date - a1.log_date)) / 60 as minutes


SQL Server error/warning 825 Accidental DBA here (DB developer/jack of all trades), and first-time poster.  So please be gentle.  I have an alert on our SQL Server for error 825, and it fired in February.  Then twice in June, and now three times in the past three weeks.  The databases are for a vendor application.  DBCC CHECKDB ran in production after the errors, and I also restored to a test server and ran again, with no errors.  The errors occurred on two of the databases, one of which holds 99% of the application's data.  No rows in suspect_pages.

I also wrote to the software/database vendor, and they pretty told me to do what I already did, and sent a bunch of cut-n-paste research that I've already found.

Everything I found says hardware or drivers.  Our network/hardware is maintained by a third-party provider, and they say they don't see anything wrong with the disks.  We run VMware on EMC - that's about all I know about hardware. SQL is Standard Edition 2008R2 SP3, running on Server 2008R2 Standard.

Oh, I changed DBCC CHECKDB to run daily.

We've been told we should hire a DBA because our disks are healthy.

Any advice?

Thanks,
Pat

Here you can find the explnation of this error by Paul Randal:
A little-known sign of impending doom: error 825

From SQL Server 2005 onwards, if you ever see an 823 or 824, SQL
Server has actually tried that I/O a total of 4 times before it
finally declares a lost cause and surfaces the high-severity I/O error
to the connection’s console, killing the connection into the
bargain. The idea behind this read-retry logic came from Exchange,
where adding the logic reduced the amount of immediate downtime that
customers experienced. While in concept this was something I agreed
with at the time, I didn’t agree with the way it was implemented.
If the I/O continues to fail, then the 823/824 is surfaced – that’s fine. But what if the I/O succeeds on one of the retries?
No high-severity error is raised, and the query completes, blissfully
unaware that anything untoward happened. However, something did go
badly wrong – the I/O subsystem failed to read 8KB of data correctly
until the read was attempted again. Basically, the I/O subsystem had a
problem, which luckily wasn’t fatal this time. And that’s what I
don’t like – the I/O subsystem went wrong but there are no flashing
lights and alarm bells that fire for the DBA, as with an 823 or 824.
If read-retry is required to get a read to complete, the only notification of this is a severity-10 informational message in the
error log – error 825.
Additional messages in the SQL Server error log and system event log may provide more detail. This error condition threatens
database integrity and must be corrected. Complete a full database
consistency check (DBCC CHECKDB). This error can be caused by many
factors; for more information, see SQL Server Books Online.
What this message is really saying is that your I/O subsystem is going wrong and you must do something about it. And unless you’re
regularly scanning the error log looking for these, you’ll be
none-the-wiser.


Snapshot for transactional replication taking too long for large database I want to establish a transaction replication of a large database (200GB). But when I create the publisher and the snapshot agent starts, it takes very very long time and it sticks on the following message

  The process is running and is waiting for a
  response from the server

but the problem is when I see the size of the snapshot folder, I can see it is growing up! But too slowly.

Do you have any Idea about this? or any different way of doing the replication? how can I find the more detailed warning or errors messages?

P.S. In my database there are two tables for Images that they make 98% of the size of my database. when I don't include them in my publication, everything goes fine. but when I Include them, I will face the problem.

Yes, initializing a large database through a snapshot can be very slow and time consuming.  If it is appropriate to your case, you should start by restoring a copy of the database to the target machine.

In setting up the subscription choose "allow initialization from backup files".

Because of limitations in the user interface for setting up replication, you will need to create a script instead of relying on the UI tools.  There is a blog post on this at:

http://blogs.msdn.com/b/repltalk/archive/2010/03/16/deep-dive-on-initialize-from-backup-for-transactional-replication.aspx

The post from msdn includes the code sample:

exec sp_addsubscription @publication = N'Repl2000', …..
    @sync_type = N'initialize with backup', @backupdevicetype='Disk',
    @backupdevicename='C:\Repl2000_RestoreThis.bak'--the last backup used to restore on the subscriber
go
exec sp_addpushsubscription_agent …….
go

The article also includes some troubleshooting tips.


String manipulation tools to populate a variable for two "functions" Versions: SQL Server 2008 R2 - 2014

I'm using a server audit to find changes to logins on my SQL Server instances, and writing it to the ERRORLOG directory path. Unfortunately, not all the instances use the same path for the ERRORLOG directory. I can get the path using SELECT SERVERPROPERTY('ErrorLogFileName') but I need to "insert" the return value of that statement to two other places. The first is when I'm creating the Server Audit:

CREATE SERVER AUDIT [ServerAuditName]
TO FILE
(   FILEPATH = N'C:\Program Files\Microsoft SQL Server\MSSQL\Log'
    ,MAXSIZE = 0 MB
    ,MAX_ROLLOVER_FILES = 2147483647
    ,RESERVE_DISK_SPACE = OFF
)

So, I need to "insert" the results of the SERVERPROPERTY query into a variable or...something, so I can use it in the FILEPATH value above. Secondarily, I also need to find the ERRORLOG directory path when reading the *.sqlaudit files the Server Audit creates. I can easily use the following to get files, but here too, I need to "insert" the filepath vaule with the result of the SERVERPROPERTY query:

SELECT *
FROM sys.fn_get_audit_file('Filepath\*.sqlaudit',DEFAULT, DEFAULT)

I have tried some "google-fu" and have looked at a couple of string functions, like replace and the "trim-family"...which wasn't very relevant. If anyone can help or point me to a good article or two on how to achieve this, I would greatly appreciate it.

The first thing you need to do is chop the filename off of that full path, else you will just get an error. The following does that using a CTE to apply the expression twice so that I didn't have to copy/paste the CONVERT:

;WITH cte AS (SELECT CONVERT(NVARCHAR(259), SERVERPROPERTY('ErrorLogFileName')) AS [Log])
SELECT LEFT(cte.[Log], CHARINDEX(N'\error', cte.[Log] COLLATE Latin1_General_100_CI_AS))
FROM   cte;

In the query above, I used COLLATE Latin1_General_100_CI_AS to force a case-insensitive comparison as I noticed different casing between "regular" SQL Server (i.e. running via a service) and SQL Server Express LocalDB in terms of the output of the SERVERPROPERTY('ErrorLogFileName') function:

"regular":

C:\Program Files\Microsoft SQL Server\MSSQL14.SQL2017EXPRESS\MSSQL\Log\ERRORLOG
LocalDB:

C:\Users\Solomon\AppData\Local\Microsoft\Microsoft SQL Server Local DB\Instances\LostInTranslation\error.log

You can use that with Dynamic SQL to generate the CREATE SERVER AUDIT statement as follows:

DECLARE @SQL NVARCHAR(MAX);

;WITH cte AS (SELECT CONVERT(NVARCHAR(259), SERVERPROPERTY('ErrorLogFileName')) AS [Log])
SELECT @SQL = N'CREATE SERVER AUDIT [ServerAuditName]
TO FILE
(   FILEPATH = N''' +
  LEFT(cte.[Log], CHARINDEX(N'\error', cte.[Log] COLLATE Latin1_General_100_CI_AS)) + N'''
    ,MAXSIZE = 0 MB
    ,MAX_ROLLOVER_FILES = 2147483647
    ,RESERVE_DISK_SPACE = OFF
) '
FROM   cte;

PRINT @SQL; -- DEBUG (just to see what is being executed)

EXEC(@SQL);

For reading the audit file, you don't need the Dynamic SQL as you can pass the expression to the DMF as follows:

;WITH cte AS (SELECT CONVERT(NVARCHAR(259), SERVERPROPERTY('ErrorLogFileName')) AS [Log])
SELECT *
FROM   cte f
CROSS APPLY sys.fn_get_audit_file(LEFT(f.[Log],
       CHARINDEX(N'\error', f.[Log] COLLATE Latin1_General_100_CI_AS)) + N'\*.sqlaudit',
    DEFAULT, DEFAULT);


Alternative defaults with a different table name Say the original table has DEFAULT nextval('some_sequence'). I'm looking for a way to specify DEFAULT nextval('another_sequence') on a table view/alias so that I can just insert into the alias as usual instead of repeating int_col = nextval('another_sequence') with every insert. In other words, the user/application only needs to be concerned with a table name and not whichever sequence is being used. Is this possible?

Found a clean solution thanks to a_horse_with_no_name for the idea and the syntax example set in this answer

CREATE VIEW tbl_view AS
    SELECT * FROM tbl;

ALTER TABLE tbl_view ALTER int_col SET DEFAULT nextval('another_sequence');


Strategies for rotating backend production MySQL data into a test environment I've been assigned a task where I need to replicate a web application for creating a test environment, and part of the project is to create a test database with sample production data to help testing. I'm no database expert, and I haven't been able to find any solid strategies online for rotating data. All I know for sure is that I would need to spin up another MySQL instance on our DB server and connect the new instance to that, but how do I go about getting actual data out of our production DB without harming production data? Are there any well-known strategies I can deploy?

Plan A:  Have the Production system be a Master; have another machine (or two) be a Slave.  At any moment, you could use the Slave (in a couple of different ways) to provide a snapshot of Production at some point in time.  The impact on the Master (Production) is zero (after a somewhat painful setup).  Master-Slave is the beginnings of any HA (High Availability) solution.

Plan B:  Use LVM (Logical Volume Management, an OS tool) as a way of taking a snapshot of the Production machine.  This involves taking down the server briefly (about a minute, regardless of amount of data).  Them the disk can be copied to elsewhere for whatever use -- such as building a dev box.

Neither of those do what I think of as a "rotate", but perhaps they solve your request?

I suggest you search for either or both of those strategies, then come back for further questions, if needed.


Why won't some DBMS's allow rollback for certain DDL statements? Recently I found out that MySQL doesn't support rollback of DDL such as "alter table"... Being used to PostgreSQL, that struck me as odd, but a friend of mine told me that even Oracle doesn't allow it.. Are there technical reasons for not supporting it? Is it simply an "uninteresting" feature for them?

Edit: just found this comparison. It looks like there are many DBMSes that do support transactional DDL.

The reason why this works in PostgreSQL is that the system catalogs are regular tables.  So creating a new function, for example, just requires inserting a row into the pg_proc table, changing the default value of a column just requires making an update to some row in pg_attrdef, and so on.  Since tables are transactional anyway, you'd almost have to go out of your way not to have it work that way.  (Lots of painful implementation details omitted here. ;-) )

I suppose, not knowing the source code, that other database engines use some custom internal structures to represent their system catalog information.  And so they'd have to put in extra effort, a lot of extra effort likely, to make transactional DDL work, and it's apparently not a priority for them.

The flip side of this is that this is the reason why major version upgrades of PostgreSQL are so painful.  Other products can presumably design their internal metadata structures with changes and updates in mind, and so there are no issues with upgrading to a new major version.  In PostgreSQL, there is no way to change a system catalog table to suddenly look like a newer version of a system catalog table, at least not while keeping the system online, because that would require access to the system catalogs.  Urgh.


Does using a read committed isolation level and a NOLOCK query hint impact performance? Can having both the SET TRANSACTION ISOLATION LEVEL READ COMMITTED and WITH (NOLOCK) on all tables in SP cause conflicts and downgrade performance on the SP?  I just noticed some changes that a dev made in the SP recently.

If you have stored procedure that starts with:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

And then you have a SELECT query like this:

SELECT * FROM dbo.MyTable mt WITH (NOLOCK);

Then the SELECT query will use the READ UNCOMMITED isolation level* - in other words, the NOLOCK will override the SET statement.  This is documented in SET TRANSACTION ISOLATION LEVEL (Transact-SQL):

  All read operations performed within the transaction operate under the rules for the specified isolation level unless a table hint in the FROM clause of a statement specifies different locking or versioning behavior for a table.

As far as performance, using NOLOCK will ignore locks taken by other queries, and take / release locks more quickly, so you will likely experience less blocking.  However, this is at the expense of the correctness of the results.

* Since this is a table-level hint, the hint would need to be applied to each table involved in the SELECT query in order to be functionally equivalent to using SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED


Filtering on a virtual column from a UNION ALL with LEFT OUTER join causes a table scan? I asked this question on SO but havn't got many bites and even a vote to close for being "Off Topic." So, I thought this stack exchange might be better suited.

Consider the following SQL...

SELECT allStuff.id
  FROM    (SELECT id, 1 AS clazz_
             FROM MyStuff
           UNION ALL
           SELECT id, 2 AS clazz_
             FROM MyWarehousedStuff) allStuff
       LEFT OUTER JOIN
          Chilren child
       ON allStuff.id = child.id
 WHERE allStuff.id IN (/*fast sub select */)
       AND allStuff.clazz_= ? /* if I remove this line the query is fast (index fast)
                                 but if it stays in then DB2 does a tablescan
                                 on MyStuff and MyWarehousedStuff (very expensive) */

To make things a bit more confusing, if I remove the LEFT OUTER JOIN or make it an INNER JOIN DB2 does the right thing. I find it highly unusuall that an AND would cause worse perforamnce. I am also surprised by the use of a table scan when the field in question isn't actually on the table and is constant.

Note, this is a cleaned up Hibernate query so I'm not to worried about fixing the SQL in question but rather I want to know why DB2 is acting a particular way.

What would be causing this behavior?

[update] - It appears that even adding allStuff.clazz_ to the column list is enough to trigger the table scanning.

[update] - Also seems that the IN subslect is part of the problem. Switching that to a literal subselect IN (?, ?) is fast but IN (select 1 from SYSIBM.SYSDUMMY1) is not

[update] - Using DB2 v9.1 z/OS

[update] - To be clear, the following is also slow

SELECT allStuff.id, allStuff.clazz_
  FROM    (SELECT id, 1 AS clazz_
             FROM MyStuff
           UNION ALL
           SELECT id, 2 AS clazz_
             FROM MyWarehousedStuff) allStuff
       LEFT OUTER JOIN
          Chilren child
       ON allStuff.id = child.id
 WHERE allStuff.id IN (select 1 from sysibm.sysdummy1)

But this is fast...

SELECT allStuff.id, allStuff.clazz_
  FROM    (SELECT id, 1 AS clazz_
             FROM MyStuff
           UNION ALL
           SELECT id, 2 AS clazz_
             FROM MyWarehousedStuff) allStuff
       LEFT OUTER JOIN
          Chilren child
       ON allStuff.id = child.id
 WHERE allStuff.id IN (?, ?)

Fast...

SELECT allStuff.id, allStuff.clazz_
  FROM    (SELECT id, 1 AS clazz_
             FROM MyStuff
           UNION ALL
           SELECT id, 2 AS clazz_
             FROM MyWarehousedStuff) allStuff
       INNER JOIN
          Chilren child
       ON allStuff.id = child.id
 WHERE allStuff.id IN (?, ?)

Fast...

SELECT allStuff.id, allStuff.clazz_
  FROM    (SELECT id, 1 AS clazz_
             FROM MyStuff
           UNION ALL
           SELECT id, 2 AS clazz_
             FROM MyWarehousedStuff) allStuff
 WHERE allStuff.id IN (select 1 from sysibm.sysdummy1)

Fast...

SELECT allStuff.id
  FROM    (SELECT id, 1 AS clazz_
             FROM MyStuff
           UNION ALL
           SELECT id, 2 AS clazz_
             FROM MyWarehousedStuff) allStuff
       LEFT OUTER JOIN
          Chilren child
       ON allStuff.id = child.id
 WHERE allStuff.id IN (select 1 from sysibm.sysdummy1)

Just off the top of my head, it makes sense that it needs to tablescan those two tables. They are both present in a UNION ALL. This essentially means all records are in consideration. You then also use a LEFT OUTER JOIN meaning everything from the left side (your two union'd) tables even if it doesn't match the right side (your Children table). This is telling DB2 that no matter what every possible record in the two tables of your UNION ALL is under consideration.

Now...to fix that problem. I would ask, do you have any indexes over the clazz column in both tables? Since clazz is a predicate of your where clause you may wish to have a index over that field in both tables (MyStuff and MyWarehouseStuff). You don't need an index over the id fields (as long as they are primary keys), because you gain that by default in DB2.

To better verify what DB2 is doing, you should use the visual explain (of course you may have done that). It should show you why it is picking what it is. A few other things to keep in mind when examining the explain, do you run stats? If you don't, you should. Running stats will keep DB2 better informed as to statistics about its tables, data, indexes, etc, and may cause it to choose a better access path. Also, this may seem a silly question, what version and fix pack of DB2 are you on? Occasionally certain versions/fix packs end up buggy and there are performance issues due to poor access path selection by the optimizer. An example of this, if you are on DB2 9.7 then you want to be on fix pack 4 or 5. There is at least one fix pack below those that had serious performance issues due to a bug in the optimizer.

Anyway, check out your indexes first. Adding one to both the tables I mentioned may solve your problem. Worse case, I suppose you could re-write your query to put the clazz predicate up into the sub select "table". That may cause the optimizer to eliminate rows before it unions and joins. Just another thought.

  EDIT: Here is a thought on your query

SELECT allStuff.id
  FROM    (SELECT id, 1 AS clazz_
             FROM MyStuff
           UNION ALL
           SELECT id, 2 AS clazz_
             FROM MyWarehousedStuff) allStuff
       LEFT OUTER JOIN
          Chilren child
       ON allStuff.id = child.id
 WHERE allStuff.id IN (/*fast sub select */)
       AND allStuff.clazz_= ?

  You are using a constant basically that from what I understand doesn't
  exist in the table. You also are not selecting this, but you have
  applied it to every row, and you use it in the WHERE clause. So I
  ask why do this? It really is just saying select every row. You may as
  well be doing select *. This will contribute to the table scan. Plus
  you are not displaying this value back. So why use it? Why not rather
  do the following:

SELECT allStuff.id
  FROM    (SELECT id FROM MyStuff
           UNION ALL
           SELECT id, FROM MyWarehousedStuff) allStuff
       LEFT OUTER JOIN
          Chilren child
       ON allStuff.id = child.id
 WHERE allStuff.id IN (/*fast sub select */)

  Since you are not selecting the data, unless you oops'd on your query
  you are posting, isn't this what you would rather have? It union
  (all)'s the ids, joins them to the child table and then returns what
  you want?

  Although I would also question the point of your LEFT OUTER JOIN.
  Because if your left table (in this case allStuff) has a NULL for
  the id it shouldn't match your "fast sub select", unless that allows
  NULL's as well. (Does it?), so you would be excluding these. I don't
  know the context of  what you are trying to accomplish so perhaps I am
  not speaking to it appropriately, but it makes me question having a
  LEFT OUTER JOIN as well.


CREATE TABLE AS SELECT creates non-nullable columns I am using the CREATE TABLE AS SELECT statement in Oracle 11g to get data from SQL Server 2012 via a database link.

Oracle creates all these tables with non-nullable columns and that causes me problems later when I try to update them.

How can I prevent this behaviour in Oracle and make resulting columns nullable?

Either create the table manually beforehand, or specify the column names an NULLability in the CTAS statement:

create table blah2
(
  ctascolumn1 not null,
  ctascolumn2 null
)
as
select col1, col2 from blah;


Can I implement clustering with just 2 servers and one NAS? I have the following architecture, it consists in only 2 servers, connected to a NAS.

I'm having trouble in implementing clustering because I can't have a domain controller in any of them (if I do the sql server complains...). Is there a way to implement clustering with these limitations?

Consider using mirroring, not clustering?

MSDN difference overview, a summary:

mirroring is per database vs per instance
mirroring is 2 instances with own files, clustering is one set of files owned by active node
clustering has stricter hardware requirements


How to verify postgresql db restore from pg_dump file How can I verify that my restored database is identical to my original database?

Here's what I did.  Is this a valid way to verify a restore?

# dump mydb_orig:
$ pg_dump  --cluster 8.4/my.8.4.cluster mydb_orig > mydb_orig.dump

# create mydb_restored:
$ createdb --cluster 9.1/my.9.1.cluster mydb_restored

# load mydb_orig into mydb_restored:
$ psql -q  --cluster 9.1/my.9.1.cluster -d mydb_restored -f mydb_orig.dump

# dump mydb_restored:
$ pg_dump  --cluster 9.1/my.9.1.cluster > mydb_restored.dump

# verify mydb_orig and mydb_restored are identical:
$ diff mydb_orig.dump mydb_restored.dump

# The files have the same number of bytes, but some of the rows are in a different order, so sort them:

$ sort mydb_orig.dump > sorted.orig.dump
$ sort mydb_restored.dump > sorted.restored.dump
$ diff sorted.orig.dump sorted.restored.dump
# Now they are identical

What is the exact problem you are trying to solve? pg_dump and pg_restore are official supported PostgreSQL tools, so you should just assume they are correctly implemented. You have a few options on checking databases are identical though, depending on how certain you need to be they are identical.

Your most basic check would be to check that both databases have the same tables. You can find a list of tables via the information schema. For a loose check that the data is identical, you could look at the amount rows in these tables, and the upper and lower bound of primary keys (assuming you have primary keys where ordering makes sense).

If you really need to guarantee the data is identical, then you will need to produce some sort of hash and compare that, and that's essentially what you are doing with sorted dumps. It's worth to remember though that the database is more than just tables, you need to check sequences too.


SSL to individual database Is there a way to setup an SSL connection to an individual database, or does it have to be done at the instance level?

Connection settings, including encryption, are instance level. Since connections can switch databases at any moment (simply issue an USE statement) it makes no sense at all to have such settings at database level.

Security constrains ('all traffic to and from this DB must be encrypted') often result in splinting the DB into its own instance just to satisfy the constraint.


Best approach to mirror All databases of a sql server I see here that we have a lot of questions about best approach to mirror a single database. I got a new job and people there is  under a task to mirror all databases, to a secondary one, in a different city for security (if we lost a server, we have everything on the mirror).

1) to mirror ALL databases, should I repeat the mirroring step for every database?

2)is this the best approach to high availability?

I'm thinking about something with raid, so we have a mirror disk that if a disk fails, there is this secondary ( i just forgot what raid is this, im on the cellphone and will search later).

I think there will be a lot of requests to mirror the data ( it's not going to be synchronous) if we really  chose to mirror ALL databases ( there are more than 200).

What would you  guys do in this situation? I would even prefer log shipping.

I just found this question  but i really need to sleep now. Will study it tomorrow. It says about log shipping too.

I do not see what version of SQL Server you are using and from the tag assuming it is SQL Server.

From Books Online:
This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use Always On Availability Groups instead.

If you are at 2012 or above I highly recommend you to consider Always ON Availability group. Besides High Availability you can also gain performance benefit by offloading certain maintenance operations and read traffic.  It will depend on your setup, application design and requirement. You can read more in details here.
FYI :This technology is built upon mirroring which you mentioned in the question.
Log Shipping is definitely another option but your read only copy cannot be available for read traffic all the time and not in sync as close to real time as Always ON Availability Group.  See details about Log Shipping here.

Few more options that you can consider based on your requirements:

Failover Clustering
Failover Clustering in conjuntion with storage level replication.
AlwaysOn Failover Clustering Instances
Replication


Windows Storage Server, SQL Server, regular files -- means to protect from insider threats? This is my first post here, and I am by no means qualified as a Windows Server or Storage Server 2008 or SQL Server 2008 administrator. I am mostly a Unix guy, however tasked with a job to investigate if our firm (a small software startup), IT infrastructure, which includes Windows Server 2008, Windows Storage Server, and SQL Server used exclusively by Finance, HR, Sales, Legal and Administration depts. Devs are all on Unix.

What I need to get some ideas around is, how to protect the Windows based infrastructure from insider attacks. More precisely, against rogue system-administrators. Our current system administration role is played by a temp. In theory, having access to all (or most) passwords/login credentials, he can do whatever he likes.

Is there then some technology (s.a. audit logs, tamper-proof backups etc.), that can be setup (like onetime), whose credentials are available only with an extremely trusted person (maybe CEO himself), and all other system admin tasks can be performed by this temp chap.

In worst case, if the admin removes, deliberately corrupts/modifies certain files, or alters database content, such acts can be:

Tracked down
Proven
Rectified

I understand that this is probably not a pure DBA question, and pardon me for willfully posting this, but I didn't find a more appropriate SE forum, but found quite a few interesting Windows Server infrastructure discussions here.

What you can do is restrict access to the SQL Server and the Windows server that SQL is on. There are server level roles, with sysadmin giving you the highest privileges. Review Server-Level Roles to see what you will need to give your temp. The temp should never be in the sysadmin server level role, based on what I read from your requirements.

Then there are database level roles. This is set at each database level for each DB. See Database-Level Roles to read more. Your temp may be in the db_backupoperator, or the db_datareader role for each DB. This will prevent him from deleting/modifying the DBs.

Encrypt your confidential data. You can also use passwords for your backups, but this feature is not meant for security, rather is helps maintain related backup sets.

On the Windows server part, you can further restrict access. Querying the SQL Server can be done from a laptop with SSMS installed on it, thereby not having to give your temp much rights on the Windows server.

To track changes on the DB you will need auditing. There is a plethora of information in the Auditing in SQL Server 2008 Microsoft technical article.

Remember, too much auditing comes at the cost of performance. With SQL Server 2008, there are new and much granular auditing controls, like CHANGETABLE and policy-based management. These can help with your Tracked down, Proven, and Rectified requirements.

Above is just a quick summary of the possibilities.


How to set datadir to different values based on the hostname in my.cnf? How to set datadir to different values based on the hostname in my.cnf?  I want to use the same my.cnf file on a Linux machine and a Mac OS X machine where the datadir should have different values.

A simple solution for that would be to keep the my.cnf file identical, and use symlinks on your machine sto point to whatever directory you want the data to be in. For example, assuming you my.cnf contains this:

[mysqld]
datadir=/mysql_data

On you linux host, you would:

cd /
ln -s /var/lib/mysql mysql_data

Add permissions to this folder for mysql user:

chown mysql:mysql /mysql_data


Adding the domain account to a security group on the SQL Server computer that has sufficient privileges to log on as a service After reading this article,

http://www.red-gate.com/supportcenter/content/knowledgebase/SQL_Backup/KB200710000173

I have some problems configuring point 2) and 3):

2) Create a SQL Server login that has the ability to backup (and restore) databases (y) by adding it to the SYSADMIN server role.

3) Add the domain account (x) to a security group on the SQL Server computer that has sufficient privileges to log on as a service, etc.

Where can I find detailed instructions/videos on how to accomplish them?

Thanks.

To answer your questions directly:

2) In order to add a login to the sysadmin fixed server role you can utilize ALTER SERVER ROLE ... ADD MEMBER (2012) or sp_addsrvrolemember (pre-2012).  Here would be an example:

exec sp_addsrvrolemember 'yourLoginName', 'sysadmin';

3) Without having to reiterate what's found in the following documentation, take a look at this MSDN reference on the service permissions for each SQL Server service.


Your query is using grouping but you do not have any aggregation happening on any of your columns. By default, I believe MySQL will allow you to do this, but it is not supported or generally accepted as safe because MySQL will pick arbitrary values for your non-aggregated columns. (SEE: https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sqlmode_only_full_group_by)

So assuming you want to group properly, I'm guessing you probably want a sum of sales for each product and returned with that aggregate, you want the "first" image.

What version of MySQL are you using? If you are using < v8.0 then there's no "simple" way to effectively do this unless a few assumptions can be made. MySQL < v8.0 lacks windowed functions / groupwise sorting (google "row_number over partition by") and I have not worked with that functionality in 8.0 yet so I cannot speak to that.

If you are able to assume a few things though, you can probably get a working query.

Assuming your images table ID is auto_increment and you will always want the image with the lowest ID to be returned in this query, then the following should work:

mysql> select p.id, p.product, i.image, sum(sale) from products p left join (select p_id, min(id) as minImageId from images group by p_id) as minI on p.id = minI.p_id left join images i on minI.minImageId = i.id left join sales s on p.id = s.p_id group by p.id;
+----+--------------+-------------+-----------+
| id | product      | image       | sum(sale) |
+----+--------------+-------------+-----------+
|  1 | super prod 1 | myimg01.jpg |        45 |
|  2 | super prod 2 | myimg04.jpg |        29 |
|  3 | super prod 3 | myimg06.jpg |        12 |
|  4 | super prod 4 | myimg08.jpg |        55 |
|  5 | super prod 5 | myimg10.jpg |        16 |
+----+--------------+-------------+-----------+
5 rows in set (0.00 sec)

Here is how I built the query up from the products table all the way to the finished query:

SELECT *
FROM products;
+----+--------------+
| id | product      |
+----+--------------+
|  1 | super prod 1 |
|  2 | super prod 2 |
|  3 | super prod 3 |
|  4 | super prod 4 |
|  5 | super prod 5 |
+----+--------------+

-- for each product, get the minimum image ID
SELECT *
FROM products
LEFT JOIN
  (SELECT p_id,
          min(id) AS minImageId
   FROM images
   GROUP BY p_id) AS minI ON products.id = minI.p_id;
+----+--------------+------+------------+
| id | product      | p_id | minImageId |
+----+--------------+------+------------+
|  1 | super prod 1 |    1 |          1 |
|  2 | super prod 2 |    2 |          4 |
|  3 | super prod 3 |    3 |          6 |
|  4 | super prod 4 |    4 |          8 |
|  5 | super prod 5 |    5 |         10 |
+----+--------------+------+------------+

-- clean up query by removing a column we do not care to see
SELECT p.id,
       p.product,
       minI.minImageId
FROM products p
LEFT JOIN
  (SELECT p_id,
          min(id) AS minImageId
   FROM images
   GROUP BY p_id) AS minI ON p.id = minI.p_id;
+----+--------------+------------+
| id | product      | minImageId |
+----+--------------+------------+
|  1 | super prod 1 |          1 |
|  2 | super prod 2 |          4 |
|  3 | super prod 3 |          6 |
|  4 | super prod 4 |          8 |
|  5 | super prod 5 |         10 |
+----+--------------+------------+

-- join in image table using the min image ID we found ealier
SELECT p.id,
       p.product,
       minI.minImageId,
       i.image
FROM products p
LEFT JOIN
  (SELECT p_id,
          min(id) AS minImageId
   FROM images
   GROUP BY p_id) AS minI ON p.id = minI.p_id
LEFT JOIN images i ON minI.minImageId = i.id;
+----+--------------+------------+-------------+
| id | product      | minImageId | image       |
+----+--------------+------------+-------------+
|  1 | super prod 1 |          1 | myimg01.jpg |
|  2 | super prod 2 |          4 | myimg04.jpg |
|  3 | super prod 3 |          6 | myimg06.jpg |
|  4 | super prod 4 |          8 | myimg08.jpg |
|  5 | super prod 5 |         10 | myimg10.jpg |
+----+--------------+------------+-------------+

-- bring in and aggregate our sales data. if you do not put an aggregate function on the sale value, mysql will pick a value for you arbitrarily
SELECT p.id,
       p.product,
       minI.minImageId,
       i.image,
       sum(sale)
FROM products p
LEFT JOIN
  (SELECT p_id,
          min(id) AS minImageId
   FROM images
   GROUP BY p_id) AS minI ON p.id = minI.p_id
LEFT JOIN images i ON minI.minImageId = i.id
LEFT JOIN sales s ON p.id = s.p_id
GROUP BY p.id;
+----+--------------+------------+-------------+-----------+
| id | product      | minImageId | image       | sum(sale) |
+----+--------------+------------+-------------+-----------+
|  1 | super prod 1 |          1 | myimg01.jpg |        45 |
|  2 | super prod 2 |          4 | myimg04.jpg |        29 |
|  3 | super prod 3 |          6 | myimg06.jpg |        12 |
|  4 | super prod 4 |          8 | myimg08.jpg |        55 |
|  5 | super prod 5 |         10 | myimg10.jpg |        16 |
+----+--------------+------------+-------------+-----------+

-- clean up query by removing a column we do not care about
SELECT p.id,
       p.product,
       i.image,
       sum(sale)
FROM products p
LEFT JOIN
  (SELECT p_id,
          min(id) AS minImageId
   FROM images
   GROUP BY p_id) AS minI ON p.id = minI.p_id
LEFT JOIN images i ON minI.minImageId = i.id
LEFT JOIN sales s ON p.id = s.p_id
GROUP BY p.id;
+----+--------------+-------------+-----------+
| id | product      | image       | sum(sale) |
+----+--------------+-------------+-----------+
|  1 | super prod 1 | myimg01.jpg |        45 |
|  2 | super prod 2 | myimg04.jpg |        29 |
|  3 | super prod 3 | myimg06.jpg |        12 |
|  4 | super prod 4 | myimg08.jpg |        55 |
|  5 | super prod 5 | myimg10.jpg |        16 |
+----+--------------+-------------+-----------+


Cannot shutdown old postmaster when upgrading to Postgres 9.2 I'm upgrading to Postgres 9.2.2 (from 9.1.4). When I try to upgrade the DBs using:

pg_upgrade -b /usr/local/Cellar/postgresql/9.1.4/bin -B /usr/local/Cellar/postgresql/9.2.2/bin -d /usr/local/var/postgres91 -D /usr/local/var/postgres

I get the following error message:

Performing Consistency Checks
-----------------------------
Checking current, bin, and data directories                 ok

There seems to be a postmaster servicing the old cluster.
Please shutdown that postmaster and try again.
Failure, exiting

I've trying stopping the server, but cannot get the upgrade command to work. How to I shutdown the old postmaster?

The postmaster.pid should be inside the previous version's usr/local/var folder. Simply renaming this file should solve the problem.


Difference in between MySQL affected num rows and num rows I am just newbie to the MySQL.I want to know what is the main differences in between mysql num rows and mysql affected num rows?Any example or references will be highly appreciable.

Here's mysql_num_rows:

int mysql_num_rows ( resource $result )

  Retrieves the number of rows
  from a result set. This command is only valid for statements like
  SELECT or SHOW that return an actual result set. To retrieve the
  number of rows affected by a INSERT, UPDATE, REPLACE or DELETE query,
  use mysql_affected_rows().

And here's mysql_affected_rows:

int mysql_affected_rows ([ resource $link_identifier = NULL ] )

  Get the number of affected rows by the last INSERT, UPDATE, REPLACE or
  DELETE query associated with link_identifier.

Notice that the input types are different:  the first wants a result set, from which it counts the number of rows returned; the second wants a db handle.  That makes sense, though, because when you INSERT (or UPDATE, DELETE, REPLACE) a row, you typically don't want a result set -- you just want to know if it succeeded or failed, or how many rows it updated/replaced/deleted.


Can we create SQL Server Aliases in Linux clients? Can we create "Server Aliases" in Linux clients (webservers etc.) so that you can change connections without touching the application's DB URL string?
The official documentation seems to talk about a GUI way of doing this in Windows, so I was wondering if something like this exists for Linux. This has lots of benefits, especially during containerization of apps.

My high level objective is to see if I can create some level of indirection so that the application always thinks of DB as DSN=myDsn;Uid=myUsername;Pwd=myPwd; while the real driver redirects it to DSN=realDsn;Uid=realUsername;Pwd=realPwd;. Initially I was thinking of something like socat, but I am not sure how password changes can be handled! "Server Aliases for client" seems to do something like this, hence the question.

Aliases on Windows are set using the SQL Server Configuration Manager GUI. The Linux equivalent is mssql-conf, but there isn't an equivalent to aliases on Linux.
(I don't think this is a case of "isn't supported yet" related to Linux support being relatively new. My guess on the future is just a hunch & guessing rather than fact.)
If you need server name redirection, I'd recommend using a CNAME to redirect that name for all clients centrally, rather than a client by client setting. Alternatively, I'd use the hosts file to redirect from one server name to another.
If you need to also do credential impersonation (your example implies this), allowing the user to supply one credential, and using something different to connect to the database, that sounds like something to be handled by your application, rather than expecting the drivers to do it for you.


Automatically generate ALTER TABLE to syncronize schema on PostgreSQL i Have 2 different database schema in PostgreSQL sql file from pg_dump, is there automated way to generate alter table commands so it would be in sync, without inspecting one by one?

this is the example dump data:

database #1 (the correct one)

CREATE TABLE active_students (
    id integer NOT NULL,
    created_at timestamp without time zone,
    updated_at timestamp without time zone,
    deleted_at timestamp without time zone,
    logs text DEFAULT ''::text,
    log_count integer DEFAULT 0,
    krs_confirmed_at timestamp without time zone,
    krs_delivered_at timestamp without time zone,
    krs_generated_at timestamp without time zone,
    krs_timestamp character varying(250) DEFAULT ''::character varying,
    krs_revision integer DEFAULT 1,
    khs_delivered_at timestamp without time zone,
    khs_generated_at timestamp without time zone,
    khs_timestamp character varying(250) DEFAULT ''::character varying,
    khs_revision integer DEFAULT 1,
    ips double precision DEFAULT 0.0,
    ipk double precision DEFAULT 0.0,
    sks integer DEFAULT 0,
    sks_total integer DEFAULT 0,
    user_id integer NOT NULL,
    semester_id integer NOT NULL,
    prodi_id integer
);

CREATE INDEX index_active_students_prodi ON active_students USING btree (prodi_id);

ALTER TABLE ONLY student_invoices
    ADD CONSTRAINT student_invoices_active_student_fk FOREIGN KEY (active_student_id) REFERENCES active_students(id);

database #2 (the current database for production)

CREATE TABLE active_students (
    id integer NOT NULL,
    created_at timestamp without time zone,
    updated_at timestamp without time zone,
    deleted_at timestamp without time zone,
    logs text DEFAULT ''::text,
    log_count integer DEFAULT 0,
    krs_confirmed_at timestamp without time zone,
    khs_delivered_at timestamp without time zone,
    khs_generated_at timestamp without time zone,
    ips double precision DEFAULT 0,
    ipk double precision DEFAULT 0,
    sks integer DEFAULT 0,
    user_id integer NOT NULL,
    semester_id integer NOT NULL,
    sks_total integer DEFAULT 0,
    khs_timestamp character varying(250) DEFAULT ''::character varying,
    krs_delivered_at timestamp without time zone,
    krs_generated_at timestamp without time zone,
    krs_revision integer DEFAULT 1,
    khs_revision integer DEFAULT 1,
    prodi_id integer,
    krs_timestamp character varying(250) DEFAULT ''::character varying
);

You should use Liquibase to manage your database schema changes. You can write the schema in SQL (it was all xml back in the day). It's pretty darn easy once you get the hang of it. You can then keep all schema changes in source control. Bitbucket is free and private for small groups.

In the meantime though, you can use Liquibase to see what the differences are, and generate change scripts, in an automated fashion.

http://www.liquibase.org/documentation/diff.html


What storage engines work with Oracle? http://en.wikipedia.org/wiki/Database_engine

mentions about database engines aka storage engines. What all storage engines can be used with the Oracle database?

Oracle is not like MySQL in this. Like most other RDBMSs it comes with it's own built-in storage engine that cannot be exchanged for another.


Normalization of Time Series Data I have a table containing various a/b test experiment performance on a daily basis. The table structure looks something like this:

date     | experiment | user_count | user_attr | sales
---------------------------------------------------------
6/1/2014 |      a     |     21     |      x    |   1
6/1/2014 |      a     |     15     |      y    |   2
6/1/2014 |      b     |     29     |      y    |   0
6/2/2014 |      a     |     20     |      x    |   0
6/2/2014 |      b     |     18     |      x    |   3
6/2/2014 |      b     |     31     |      y    |   5

While this table structure seemed to be the most logical one when I created it, I'm learning more about database normalization and it seems like this structure may be inefficient (e.g. repetition of experiments and attributes on each day).

I was wondering if there was a preferred structure for this type of data while preserving the daily performance for each experiment (use case involves monitoring daily trends). Is there a better way to model this?

The table itself does not look like it contains redundant data. But, you explain "repetition of experiments and attributes on each day."

If the experiment had other attributes, splitting experiment definition into one table and results into another would be a standard normalization.

Experiment(Id, Name, Description)
Result(Experiment (FK), Participants, Participants_Attribute, Sales)

However, with the naming of the experiments "a" and "b" that seems to be covered already.

Another point of concern is the aggregate data, e.g. user_count. Normally, each result is stored separately and user_count would be a query on a child table. However, if the result is a group datum (individual data not stored), the current method is appropriate.


Report Server ( [162] Internal request to deactivate schedule ) error ( SQL Server 2008 R2 ) This is the error:

  [162] Internal request (from SetJobNextRunDate [reason: schedule will
  not run again]) to deactivate schedule N

The Image:

Has anyone seen this? I have no idea what is this error.

When we Schedule a task to send an e-mail with report server, it shows me this error in error log.

There are a lot of websites saying it is a SQL 2005 BUG. But I can't find nothing to help me with SQL Server 2008 R2.

EDIT 1

Digging a little more...I could find this:

Yes, that's correct, there was bug in earlier version saying

  "If you set up a SQLAgent job to have a recurring weekly schedule, the
  job executes successfully the first day, but may later become disabled
  and the job may not run the next day and thereafter."

But later also, quite a few have reported and it worked for them once they upgraded to SP2+( Sql Server 2008R2), so depends what SP are you running as of now:

Also,

This error may occur if you have a duplicate schedule. Say for the job in question there are various steps and some steps may have the schedule with similar name , even though different timings, but still same name on schedule step also has witnessed the above error. See for any such miss.

Moreover, see here if this helps.


Possibly a silly question: Can I install MySQL on an External HDD connected to my Router? I have a 1TB Western Digital External HDD and I was wondering if I can turn it into a MySQL Database? The HDD itself is connected to my router via USB (BT Home Hub 3) and I would like to experiment with some database ideas at home. Is there any special steps I can take to do what I seek, or is it a hopeless endeavour?

It is not a silly question as these days when databases are growing beyond gigs this is becoming a common practice. You can do that by moving all your files in current data directory to the new location and make changes in (datadir) in your my.cnf / my.ini accordingly

Stop MySQL (service mysql stop)
Make a dir in the HDD (mkdir /mysqldata)
Move your data files to HDD (cp -rp /var/lib/mysql/* /mysqldata)
Search datadir in my.cnf (cat /etc/mysql/my.cnf | grep datadir )
Change path accordinly (datadir = /var/lib/mysql To: datadir = /mysqldata )
Start MySQL (service mysql start)


Windows 2008 R2: Unable to upgrade MySQL 5.5.58 We have MySQL 5.5.58 sitting on a Windows 2008 R2 box.
I have downloaded the Installer from https://dev.mysql.com/downloads/mysql/ but nothing happens when I try and upgrade.
At the installer screen, I click on Upgrade and it says one of my products have upgrades available.
I click on catalog and it lists a bunch of Samples and Exercises.
Any suggestions?
Thanks

You have to follow the upgrade path and follow the upgrade insructions
From 5.5 to 5.6
https://dev.mysql.com/doc/refman/5.6/en/upgrade-paths.html
rom  5.6 to 5.7
https://dev.mysql.com/doc/refman/5.7/en/upgrade-paths.html
And finally from 5.7 to 8.0
https://dev.mysql.com/doc/refman/8.0/en/upgrade-paths.html
Ther you will find also the upgrade instructions


How to move from 32bit SQL Server install to 64bit? A production server has accidentally been installed with SQL Server 2012 32bit version, while it needed a 64bit version.

How best to fix this issue?

Is it possible to simply UPGRADE from 32 to 64 bit version?

Should I just install a new instance of 64 bit version of SQL Server 2012 and then simply restore the backups?

As stated in BOL here:

  Cross-platform upgrade is not supported. You cannot upgrade a 32-bit instance of SQL Server to native 64-bit using SQL Server Setup. However, you can back up or detach databases from a 32-bit instance of SQL Server, and then restore or attach them to a new instance of SQL Server (64-bit) if the databases are not published in replication. You must re-create any logins and other user objects in master, msdb, and model system databases.

If you have installed a default instance of SQL Server and wish to keep it that way your only option is to remove the current install and install using the 64-bit media. Otherwise you can install a 64-bit instance as a named instance on the same server.

Cindy Gross has some additional useful information here.


Using both DISTINCT and ORDER BY in json_agg I'm trying to get a sorted aggregate of unique values in postgresql. SQL Fiddle

The result should be a json array of unique objects, sorted by index field. The value field can be used as unique key in this case if needed.

The structure and data is similar to this:

CREATE TABLE test (
  index INTEGER,
  value INTEGER,
  tag VARCHAR
);

-- In the real query, this table is the result of a join.
-- That is the explanation for duplicated rows.
INSERT INTO test VALUES
  (1, 1, 'a'),
  (1, 1, 'a'),
  (1, 1, 'a'),
  (2, 1, 'a'),
  (3, 2, 'a')
;

Getting an aggregate of distinct rows works:

SELECT json_agg(DISTINCT test.*) FROM test GROUP BY tag;

[{"index":1,"value":1,"tag":"a"}, {"index":2,"value":1,"tag":"a"}, {"index":3,"value":2,"tag":"a"}]

Order by without distinct also works:

SELECT json_agg(test.* ORDER BY index) FROM test GROUP BY tag;

[{"index":1,"value":1,"tag":"a"}, {"index":1,"value":1,"tag":"a"}, {"index":1,"value":1,"tag":"a"}, {"index":2,"value":1,"tag":"a"}, {"index":3,"value":2,"tag":"a"}]

The problem happens if using both:

SELECT json_agg(DISTINCT test.* ORDER BY index) FROM test GROUP BY tag;

ERROR: in an aggregate with DISTINCT, ORDER BY expressions must appear in argument list Position: 42

If I try to add the ORDER BY column I get an error, since json_agg expects only one value:

SELECT json_agg(DISTINCT (SELECT test.index, test.*) ORDER BY index) FROM test GROUP BY tag;

ERROR: subquery must return only one column Position: 28

WITH cte AS (SELECT DISTINCT * FROM test)
SELECT json_agg(cte.* ORDER BY index) FROM cte GROUP BY tag;


good strategy for big tables and archiving in sql server 2012 I have a table with roughly ~500,000 rows a day getting added into it. The database supports an OLTP system. The make up of it is a bit like this:

create table Stuff
(StuffID int identity not null primary key,
ValueA decimal(18,4) not null,
ValueB decimal(18,4) not null,
ValueC decimal(18,4) not null,
ValueD decimal(18,4) not null,
StuffName varchar(10) not null,
Created datetime not null default(getdate())  )

I have a covering index on it because of the type of queries that run on it.

Generally, I'm only working with the current day's data. Very few of my queries are targetting the primary key. I've been considering moving the clustered index off of the primary key and onto the Created (datetime) column because so much of my querying is based on that, and I usually want the resultsets in chronological order.

I'd like to keep everything in one table because from a functional standpoint I have no reason to move it, but I'm worried that by not having an archive strategy of some type that eventually I'll have "too many" rows in there. In five years I should have one billion records, which maybe isn't a big deal considering the relatively small data size of my table.

Boiled down: for a small data sized table with 500K rows getting added per day, is there any particular strategy I should look towards? Will I encounter a general slowdown because of size, or is there really nothing to worry about?

Hopefully with that size you are on Enterprise edition? If not, good luck :) (just kidding see bottom of answer)
If you are on Enterprise, I would suggest looking into partitioning. This has saved my butt a time or two when dealing with this same kind of scenario. Create your partitions on the date field and you will have to best determine how big you want those partitions. I have done one  partition per day and I've also done one partition per month. It all depends on how much history you need (remember you only get a set limit on the number of partitions per table).
If you are limiting the partition to a day AND your queries are only for a specific day, the optimizer should be able to use partition elimination to pick just the partitions it needs for your query. Also, when the time comes to purge old data after several years, partition sliding makes it really easy/quick to purge data (rather than delete statements). Same with just archiving that old data to another table completely.

Non-Enterprise ideas:
Speaking of archiving to another table, that would help as well and not require Enterprise. If this table is only used for reads for anything older than a month or a year, then you can:
1) Create a second table named Stuff_Archive
2) Move everything older than a month or a year (your preference)
3) Rename your current Stuff table to Stuff_Current
4) Create a view named Stuff that unions Stuff_Current and Stuff_Archive. This way any application that uses Stuff will still be able to read from both. And you can change your queries to just go off Stuff.
One thing I think that may help even further is to add a constraint that you will need to change each time you move records to Stuff_Archive (I have not tested this, but I plan on trying it on a DB I currently need to maintain like this). This constraint would be on the date field so it helps the optimizer know "Ok, even though I'm unioning these two tables, you only actually even need to look at table A if I'm asking for dates between X and Y and table B if I'm asking for dates between Y and Z". Theoretically, I would assume the stats of the column/index would already tell the optimizer that, but I have always wondered (again, not had time to test) if a constraint would help?

And the other non-Enterprise idea would be to create a non-clustered index on the date field and even better (if you can fit it into a maintenance window) a filtered index where dt>=somedate in the past and dt<= somedate in the future. That way any queries you write will be able to use that filtered index and it will only be as big as the data within that date range (rather than on all that history you have in your ever growing table). In otherwords: If you create a filtered index for a week, you are only dealing with 500k*7=3.5m records rather than 5billion records. You just have to make sure you rebuild that index often enough to keep that window described in the filtered where criteria within the timeframe for your queries. So if you can rebuild it daily and you only need yesterday's data, then great. But if you can only rebuild it on the weekend, then you may need to make sure stats are kept up to date throughout the week.


If a record does not exist set a value I am struggling with my statement in PostgreSQL. I have 3 tables:

tbl - flags (labeled as a)
a.email

tbl - accounts (labeled as b)
b.id
b.email

tbl - foo (labeled as c)
c.account_id
c.total

My sql statement looks like:

select a.email, sum(c.total)
from flags a, accounts b, foo c
where a.email = b.email and b.id = c.account_id

This statement works but there could be some accounts who does not have an account or total amount either. Well, this statement does not make sense, because there are several a.emails in flags a, which don’t have any c.total record. And also there are several b.emails in accounts b, who doesn’t have any record in c.total.

My desired output:
email - sum(i.total)

foo@foo.com      5666
kjkj@kjaksd.com  882
hello@world.com  23122
mac@toast.com    0 -- mac@toast.com doesn’t have account or any total

So is it possible to check if a record exist in accounts compare to flags? And if so, let’s prompt total amount and if not, let’s prompt 0.

First, the query you have is not valid. I assume you omitted the GROUP BY a.email when posting the question. You also alias foo as c but then you reference to i.total.
Now for the missing results, it's because you are using (implicit) inner joins. That they are implicit, it doesn't affect results. But when using explicit joins, it's easy to change them from INNER to LEFT join.
So by doing a LEFT JOIN b we keep results from a that have no matching row in b and by doing b LEFT JOIN c we keep results from b that have no matching row in c.
The query finally looks like this (adding COALESCE() so we get zeros and not nulls for the emails that have no account or total):

select a.email,
       coalesce(sum(c.total), 0) as sum_total
from flags as a
  left join accounts as b
    on a.email = b.email              -- join condition
  left join foo as c
    on b.id = c.account_id            -- join condition
group by a.email ;


When did sqlmag.com cease to exist, please? It obviously has been a while since I visited the sqlmag.com website.  I found it no longer exists but it appears that ITProToday owns it.  Can anyone tell me when this happened, please?

Penton Media filed for bankruptcy in 2010, tried reinventing themselves as SQL Server Pro in 2011, and then gradually phased out that SQL Server Pro brand into a general IT Pro Today site.


Wraparound Transaction ID Once the txid has passed the the xmin/xmax 32 bit limit, and assuming you've not suffered any issues with wraparound because you've been correctly maintaining your tables (plenty of vacuums, vacuum freeze, etc.), what happens to the txid?  Does it just continue to increment? If so, how do xmin and xmax store it?

It wraps around to FirstNormalTransactionId (which is 3), rather than to zero.  And then it continues to increment and be stored into xmin and xmax like normal.


Is it possible to add index to mysql with multiple column to optimize order by performance? # I have a simple table
  create table t1 (c1 int, c2 int);
  # I want to do the following sorting
  select * from t1 order by c2 desc, c1 desc;
  select * from t1 order by c2 asc, c1 desc;
  # add indexs
  CREATE INDEX index_1 ON t1 (c1 DESC, c2 DESC);
  CREATE INDEX index_2 ON t1 (c1 ASC, c2 DESC);
  # then explain
  explain select * from t1 order by c2 desc, c1 desc;
  # the result is
  mysql> explain select * from t1 order by c2 desc, c1 asc;
+----+-------------+-------+-------+---------------+---------------+---------+------+------+-----------------------------+
| id | select_type | table | type  | possible_keys | key           | key_len | ref  | rows | Extra                       |
+----+-------------+-------+-------+---------------+---------------+---------+------+------+-----------------------------+
|  1 | SIMPLE      | t1    | index | NULL          | index_2 | 10      | NULL |    6 | Using index; Using filesort |
+----+-------------+-------+-------+---------------+---------------+---------+------+------+-----------------------------+
1 row in set (0.00 sec)

My questions are

Are the indexs optimized the performance?
Why Using index; Using filesort show at the same time?

You need an index on (c2, c1) for this query, not an index on (c1, c2).

Of course it would be best if there was an index (c2 DESC, c1 DESC) for the first and an index (c2 DESC, c1 ASC) for the second query. Unfortunately MySQL cannot create such indexes. The ASC and DESC are ignored.

Which means your two indexes:

CREATE INDEX index_1 ON t1 (c1 DESC, c2 DESC);
CREATE INDEX index_2 ON t1 (c1 ASC, c2 DESC);

are actually identical and same as if you had defined them as:

 CREATE INDEX c1_c2_index ON t1 (c1 ASC, c2 ASC);

If the performance - with the (c2, c1) index - is not good enough, you have a few options:

Since the columns are of integer type, add two more columns and store the negative values. You'll need triggers to polulate those columns.

.

CREATE TABLE
( c1 INT
, c2 INT
, c1_neg INT
, c2_neg INT
);

Then you can an indexes on (c2_neg, c1) and (c2_neg, c1_neg)

If you can move to MariaDB, you can use VIRTUAL PERSISENT columns and appropriate indexes for a similar solution (a bit better as you won't have to add triggers, the columns will be populated automatically).


Optimise select distinct from large table I have a table that contains 320,071,712 number of records, the report that I am currently working on can filter the records by date, after which the number of records comes down to 145,878,852. The number of distinct records are 107,311,357.

select count(*)
from [BroadcastOpens] with (nolock)
where [OpenTimeUtc] >= @StartDate
and  [OpenTimeUtc] <@EndDate

The most time consuming process is SELECT DISTINCT..., which took about 40 minutes to run.

SELECT DISTINCT [SubscriberId],[OpenTimeUtc]
FROM [BroadcastOpens] WITH (nolock)
WHERE [OpenTimeUtc] >= @StartDate
AND [OpenTimeUtc] <@EndDate

I have created two indexes on this table for both columns BroadcastId and OpenTimeUtc. They helped to speed up the process, but seem to be less significant.

CREATE NONCLUSTERED INDEX [IX_BroadcastOpens_BroadcastId_Temp]
ON [dbo].[BroadcastOpens]([BroadcastId])

CREATE NONCLUSTERED INDEX [IX_BroadcastOpens_OpenTimeUtc_Temp]
ON [dbo].[BroadcastOpens]([OpenTimeUtc])

As suggested by Martin Smith, I also have created an indexed view on Table BroadcastOpens grouped by [SubscriberId],[OpenTimeUtc],[BroadcastId].

CREATE VIEW dbo.vwBroadcastOpensRecords
WITH SCHEMABINDING
AS

SELECT [SubscriberId],[OpenTimeUtc],[BroadcastId], COUNT_BIG(*) as tmp
from [dbo].[BroadcastOpens] group by [SubscriberId],[OpenTimeUtc],   [BroadcastId]

CREATE UNIQUE CLUSTERED INDEX CIX_vwBroadcastOpensRecords_Temp ON
vwBroadcastOpensRecords(SubscriberId, OpenTimeUtc,BroadcastId);

This is the step in the query that causes problem:

I am now testing both approaches to see which one yields better performances.

No changes to the existing tsql query, no non clustered indexed created. Running on production server.

No changes to the existing tsql query, created two non clustered indexes on the table. Running on staging server.

Create an indexed view, and modified existing tsql query to use this new indexed view to replace table ... testing... (I stopped the execution of the query as it was running for 28 minutes already... I suspect there is something wrong with my indexed view...)

Just wondering: The table BroadcastOpens gets constantly updated, does this have an impact on the indexed view that I created?

Any suggestions on how to improve the performance of this query are appreciated.

Thank you HABO and Martin Smith for your invaluable help!

If you want this query gangsta fast create an indexed view that groups on [OpenTimeUtc], [SubscriberId]. That view will serve the date filter perfectly and it will pre-calculate the distinct as well. The query will result in a plan that does an index range scan on the view. Not possible to do any better than that.


MongoDb Join VS Nested Query I'm trying to build a game server and we are using MongoDb as the database.

We have a collection Games that has models like this:

{
    "_id" : ObjectId("5d1b345b8ea742034db76431"),
    "Users" : [
        {
            "_id" : "0e76bd95-a7c2-4e15-b2bc-4cdf741fe9aa",
            "UserName" : "l98lNhLHPh",
            "Cards" : [
                249
            ]
        },
        {
            "_id" : "6ffec61d-45cc-46fa-a1f0-2a3e0a03fef6",
            "UserName" : "Vun12sWp4W",
            "Cards" : [
                234
            ]
        }
    ],
    "CreatedAt" : ISODate("2019-07-02T15:09:23.303+04:30"),
    "UpdatedAt" : ISODate("2019-07-02T15:09:23.674+04:30"),
    "IsFinished" : false
}

We can easily query users that participated in a game, but what if we need to know the games that a special user had played before?

We reached two solutions but we don't know which one is better.

First, query using nested field with an index on Users.UserName:
db.games.find({"Users.UserName":"Amin"})

Second, create a new collection that holds the User and Games data.

// collection: UserGames
{
    "_id" : ObjectId("5d1b35c58ea742034db79fea"),
    "UserId" : "6ffec61d-45cc-46fa-a1f0-2a3e0a03fef6",
    "GameId" : ObjectId("5d1b35c58ea742034db79fe9")
}

and join Games and UserGames collections to find the which user played which games.

I came from a Relational database mindset and I don't know which approach should I take?

The canonical answer is to create sample data using both schemas, and profile the queries you'll be doing to see which schema runs faster :-)

But I'm guessing you'll actually see equivalent performance. Mongo is pretty fast at querying subdocuments as long as you index the subfields (which you're doing in the first option).

So the real way to decide between the two approaches is to really understand your data and your query / access patterns. For example, one advantage of creating a join table is that you could move all the additional data (like 'UserName' and 'cards') into the join table. That shrinks your game records. This might be useful if you'll be having lots of game servers creating tons of game data in realtime, and where different users are hosted on different servers, which means updating user data as the game happens can lead to locking and contention issues if all of the user data is stored in one big game record. In that scenario, a join table makes better sense, because each user-game combination is a separate record which can be updated separately (and can even be sharded onto separate servers if your game becomes really popular :-) without needing to lock other records.

On the other hand, if you don't foresee needing to do a ton of writes and updates to user-game data in realtime, then keeping all of that data as subdocuments can make queries easier to write.

At the end of the day, it really does depend on what your access patterns will be like. The raw read performance will likely be equivalent, but that's only one factor in determining what's optimal in your scenario.


How to get info if any Monitoring tool (DB performance Analyzer) stops working? What is the Reason behind if SQL monitor for Production stopped working? what is the reason for this? I checked on Licensing, SQL server permissions, No Updates/Patching and Remote admin connections everything is alright. It might be cause of over load or OS issues effect SQL monitor? Any specific reason?

You are encountering THREADPOOL waits, which occurs when the instance has run out of available worker threads. New logins will be denied, and lots of queries will be just waiting.

To troubelshoot THREADPOOL waits, connect with the Remote DAC and check current activity. You'll likely see huge blocking chains. You'll need to resolve or reduce the blocking to resolve the THREADPOOL waits.

Some Microsoft engineers will say to increase worker threads on the instance, but that doesn't help in my experience. It just means it'll take longer before THREADPOOL is encountered.

Things that might resolve THREADPOOL waits:

Adjusting Max Degree of Parallelism and Cost Threshold for Parallelism so that less queries go parallel and thus fewer worker threads are needed
Adding indexes
Refactoring queries
RCSI
etc

For more information on THREADPOOL waits, check out these links (disclaimer: I work for Brent Ozar Unlimited):

https://www.brentozar.com/archive/2014/05/connections-slow-sql-server-threadpool/
https://www.sqlskills.com/help/waits/threadpool/
http://www.sqlpassion.at/archive/2011/10/25/troubleshooting-threadpool-waits/


Is there a way to show index_advantage more friendly? Is there a way to show index_advantage more friendly like 99.54%?

SELECT TOP 25
user_seeks * avg_total_user_cost * (avg_user_impact * 0.01) AS [index_advantage]
FROM sys.dm_db_missing_index_group_stats
ORDER BY index_advantage DESC;

It shows a number like 41554133.7 so I want to show in percentage e.g. 99.54.

As Michael Green noted, this doesn't trivially convert into a percentage. This query looks at the top 25 "scores", based on this formula; but those scores aren't based some known maximum.

If you looked at all the scores, they'd be based on query activity where SQL Server thinks an index that might help, not all SQL activity. And, of course, the numbers are much lower when you're server's been up for 1 day vs. 1 month.

Ultimately, I believe these scores are intended to give you a basis for comparison; a query scoring 2000000 is more likely worthwhile to look at than one scoring 200. - RDFozz


mysqld got signal 11 I have MariaDB in Galera cluster of 3 nodes. Periodically, each node in the cluster restarts by sending yourself a signal 11. I scoured the Internet in search of a solution to the problem, but to no avail. The only thing that could be similar to my problem, it - https://serverfault.com/questions/678128/percona-cluster-signal-11. But I have not found confirmation of this.
I duplicated the problem to the developers MariaDB, but they are in no hurry to solve it: https://jira.mariadb.org/browse/MDEV-14584

error.log:

2017-12-05  9:27:13 140599126464256 [Note] WSREP: (8f3cb7d9, 'tcp://0.0.0.0:4567') turning message relay requesting off
171205 11:38:01 [ERROR] mysqld got signal 11 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.

To report this bug, see https://mariadb.com/kb/en/reporting-bugs

We will try our best to scrape up some info that will hopefully help
diagnose the problem, but since we have already crashed,
something is definitely wrong and this may fail.

Server version: 10.2.10-MariaDB-log
key_buffer_size=16777216
read_buffer_size=131072
max_used_connections=15
max_threads=102
thread_count=23
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 240475 K  bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

Thread pointer: 0x7fddf40009a8
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...

server.cnf:

# this is read by the standalone daemon and embedded servers
[server]

# this is only for the mysqld standalone daemon
[mysqld]
bind-address = 0.0.0.0
transaction-isolation = READ-COMMITTED
max_allowed_packet = 16M
myisam_recover_options = BACKUP
max_connections = 100
sql_mode =

# Logs
log_error = /var/lib/mysql/logs/mysql_error.log
slow_query_log = 1
slow_query_log_file = /var/lib/mysql/logs/mysql_slow.log
long_query_time = 2

# Cache parameters
query_cache_size = 64M
query_cache_limit = 2M
table_open_cache = 4096
thread_cache_size = 200
key_buffer_size = 16M
thread_stack = 128K
join_buffer_size = 2M
sort_buffer_size = 2M

# Parameters for temporary tables
tmpdir = /tmp
max_heap_table_size = 512M
tmp_table_size = 512M
query_cache_type=1

# InnoDB parameters
innodb_file_per_table
innodb_buffer_pool_size = 4G
innodb_flush_log_at_trx_commit = 2
innodb_log_file_size = 128M
innodb_flush_method = O_DIRECT

# Database charset parameters
character-set-server = utf8
collation-server = utf8_unicode_ci
init-connect = "SET NAMES utf8 COLLATE utf8_unicode_ci"
skip-character-set-client-handshake
#skip-name-resolve

#
# * Galera-related settings
#
[galera]
# Mandatory settings
wsrep_on = ON
wsrep_provider = /usr/lib64/galera/libgalera_smm.so
wsrep_cluster_address = "gcomm://192.168.59.78,172.16.140.29"
wsrep_cluster_name = "site-appdb"
wsrep_node_address = "192.168.59.78"
wsrep_node_name = "site-appdb01"
wsrep_sst_method = rsync
wsrep_sst_auth=repl_user:+vQvI9Fe8Nr6
binlog_format = row
default_storage_engine = InnoDB
innodb_autoinc_lock_mode = 2
#innodb_locks_unsafe_for_binlog = 1
#
# Allow server to accept connections on all interfaces.
#
#bind-address=0.0.0.0
#
# Optional setting
wsrep_slave_threads=1
innodb_file_per_table
innodb_buffer_pool_size = 4G
innodb_flush_log_at_trx_commit = 2
innodb_log_file_size = 128M
innodb_flush_method = O_DIRECT

# this is only for embedded server
[embedded]

# This group is only read by MariaDB servers, not by MySQL.
# If you use the same .cnf file for MySQL and MariaDB,
# you can put MariaDB-only options here
[mariadb]

# This group is only read by MariaDB-10.1 servers.
# If you use the same .cnf file for MariaDB of different versions,
# use this group for options that older servers don't understand
[mariadb-10.1]

It's not easy to say anything for certain, but one thing I noticed is that you're also using an old version of the galera RPM (25.3.12). Version 25.3.20 is the one distributed with the version of MariaDB you're using (10.2.10).


mongorestore only one field of a document i'm trying to restore my users :

{
  _id: ObjectId,
  created_at: Date,
  preferences: String
}

I don't know why but created_at field from my users all reseted. So i need to use my .bson backup file to restore this field but i need to only edit created_at field.

Any idea ?

Import your user data (.bson) to other temporary collection and then use forEach to update documents created_at keys.


Set retention for SQL backup to URL I am using Ola Hallengrens DatabaseBackup script. I am storing my backups in Azure Blob storage using the @url parameter. Everything is working fine, however I am looking for a way to set a retention policy on those backups - currently I assume they live there forever until I manually delete them. I tried using the @CleanupTime parameter but got the following error

The value for the parameter @CleanupTime is not supported

Is there any way I am able to set this?

Edit: SQL being run for the backup

sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d DBATools -Q \
"EXECUTE [dbo].[DatabaseBackup] \
@Databases = 'USER_DATABASES', \
@BackupType = 'LOG', \
@Verify = 'Y',  \
@Compress = 'Y', \
@CheckSum = 'Y', \
@LogToTable = 'Y', \
@ChangeBackupType='Y', \
@url='https://example.blob.core.windows.net/backups', \
Credential='AzureBlobBackups'" -b

In order to clean up files in Azure blob storage requires PowerShell or going through some app like Azure Explorer. So it is likely that Ola's backup script does not support it since there is no native T-SQL command or procedure to access blob storage in that manner.

When you are deleting files you will need to account for any that may still have an active lease. BOL has code to handle dropping the active leases.


Some users can have a fixed amount of additional fields, EAV or not? Say I have to store data about two different types of person roles or occupations, i.e., Consumers and Merchants, and both types of roles share a single base table called User.

In this respect, Merchants need to have a few additional fields, e.g., CompanyName, CompanyDescription.

What is the most efficient way to design a database for these specifications?

Have extra columns on the User table.
Have an extra table called MerchantData which has a UserId FOREIGN KEY.
Have a UserMeta EAV table which allows any amount of additional values.

I am currently leaning towards option 2, as the additional fields are fixed. Are there any down sides that I am not seeing?

Since Consumers and Merchants share some common data as Users they should be in a single base table, as you have already mentioned.

An EAV implementation can be successful under some well planned and controlled use.  But I see nothing in your question that would imply that an EAV solution is the right answer.

In fact, as you move further in your development you might find that not only do Merchants have some additional attributes, but so do Consumers.  If so, you might want a simple Supertype-Subtype model.

That would give you 3 tables that are related as shown below:

               USERS (contains common information)
                 |
                 |
     ____________|_____________
     |                        |
     | (details specific to   |
     |       a subtype)       |
Consumers                Merchants

If this works for you, much of the time you would only access the Users table, but when you needed additional information for a Consumer or a Merchant you would like to those details as well.

This is a pretty simple approach and avoids unneeded duplication of data.

For a perspective on EAV implementations, you might look at Aaron Bertrand's post, which explains the advantages and the limitations.

https://sqlblog.org/2009/11/19/what-is-so-bad-about-eav-anyway

Note that in this case it was rapidly changing requirements which made the EAV decision a good choice.


Unsure of Rank / Dense_Rank - Mulitple Columns - change rank when unique combination changes I need to get the unique combinations of 2 fields, with a RankID for each unique combination

In this example I need to create new IDs for each Customer/Value combination.

Source table:

CustID     Value
1   20.00
1   50.00
1   50.00
2   30.00
2   50.00
2   50.00

Should return this please ( change when either of the columns change)

Nr  CustID     Value
1   1   20.00
2   1   50.00
2   1   50.00
3   2   30.00
4   2   50.00
4   2   50.00

Thus there are 4 unique combinations of Customer/Value
and thus 4 nrs.

SQL:

declare @table table(
CustomerID int,
Value       decimal(18,2)
)

insert Into @table(CustomerID,Value)values(2,50)
insert Into @table(CustomerID,Value)values(1,50)
insert Into @table(CustomerID,Value)values(1,50)
insert Into @table(CustomerID,Value)values(2,50)
insert Into @table(CustomerID,Value)values(1,20)
insert Into @table(CustomerID,Value)values(2,30)

select * from @table

select  RANK()   OVER(PARTITION BY CustomerID,Value ORDER BY  Value  DESC) as Rank ,
* from @table

http://www.sqlfiddle.com/#!3/7e60e/3

You need to use:

DENSE_RANK() OVER (ORDER BY CustomerID, Value)

Test at SQL-Fiddle


Protecting Virtualized SSIS and SSRS servers I have a stand alone instance of SSIS and SSRS running on 2 seperate VM's in production. And I want to know if the Servers can be protected with some kind of Snapshot method like Veeam to restore them back in case they blow up. Please let me know if something like this is possible at all.

We are using Veeam to backup our sql db, ssis and ssrs servers.  We are using native storage snapshot integration, which makes them happen faster.  Still we have some issues when running a snapshot when an SSIS package is running.  I would advise avoiding running snapshots during your SSIS package runs.

SSIS and SSRS encryption keys are stored on the machine and don't rely on any externals, so a restore of an entire machine will not require an additional key restore, although it is a good idea to back up your keys in case you have to rebuild and manually restore a machine for some reason.

We still run sql backups of all of our databases including ssis catalog to a network share.  We are planning to use Veaam for those 'application level' backups in the future.


sql server maintence plans. What account to use? When setting up a maintenance plan specifically for backups, what account should you use as the owner.  Currently we use the domain account that sql server runs as.  Is this best practice or should we create a local account / domain account just to run jobs?

Since the jobs will run as your SQL Server Agent account, that's the way to go: http://msdn.microsoft.com/en-us/library/ms187658.aspx

It is a best practice to run your SQL Server engine and your Agent under different accounts however, the logic being if one account is locked or otherwise compromised, you aren't hurting the other. To change your Agent account: http://msdn.microsoft.com/en-us/library/ms191543.aspx


Will an update in my trigger trigger itself? Oracle PLSQL Here's my PLSQL trigger:

CREATE OR REPLACE TRIGGER ROOT_CHANGING_TRG
BEFORE UPDATE OF ROOT_ID,PARENT_PHYS_ID ON UNIQUE_PHYSICIAN
FOR EACH ROW
DECLARE
var_root number := :new.ROOT_ID;
var_par number := :new.UNIQUE_ID;

BEGIN

UPDATE UNIQUE_PHYSICIAN
SET ROOT_ID = var_root
WHERE PARENT_PHYS_ID = var_par;

END;

Will this trigger propagate? Like, if it updates a ROOT_ID for another record, will that trigger its own trigger? Further, if it does trigger that, will it use the new ROOT_ID? I want the ROOT_ID to propagate down the tree I've built.

Edit:

How this works is that each record has a unique ID, a parent ID, and a root ID. I basically have a tree, each member of that tree has a root_ID pointing at the unique ID of the root and a parent ID pointing at the one above it. The root's root and parent IDs are its own unique ID.

in the case that a user manually changes a record to point at a new root and parent, I want all the children of that node to have the new root ID. Is there a better way to do this?

My proposal would be this one:

CREATE TABLE UNIQUE_PHYSICIAN (
    PARENT_PHYS_ID NUMBER,
    PHYS_ID        NUMBER CONSTRAINT PHYSICIAN_PK PRIMARY KEY);

ALTER TABLE DPISYS.UNIQUE_PHYSICIAN ADD CONSTRAINT UNIQUE_PHYSICIAN_PARENT_FK
   FOREIGN KEY (PARENT_PHYS_ID) REFERENCES DPISYS.UNIQUE_PHYSICIAN (PHYS_ID);

CREATE OR REPLACE TRIGGER ROOT_CHANGING_TRG
    BEFORE UPDATE OF PHYS_ID ON UNIQUE_PHYSICIAN
    FOR EACH ROW

BEGIN
    :NEW.PHYS_ID := :OLD.PHYS_ID;
END;

Then the root element(s) is/are the record(s) where PARENT_PHYS_ID IS NULL.

For PHYS_ID, resp. PARENT_PHYS_ID you should use a surrogate key, preferable generated from a sequence. In this case there is no reason (and no possibility) to change them ever.

The root Id you could get with this query:

SELECT p.*, CONNECT_BY_ROOT PHYS_ID AS ROOT_ID
FROM UNIQUE_PHYSICIAN p
START WITH PARENT_PHYS_ID IS NULL
CONNECT BY PRIOR PHYS_ID = PARENT_PHYS_ID;


How do I retrieve columns inserted in subquery 1, and return them with columns inserted in subquery 2? Stack:

React Native
Node 10.x
pg-promise 8.x
PostgreSQL 10.x

A location row is upserted before creating a user row:

const insertUserSql = `
  with l as (
  insert into locations (
    data,
    placeid,
    user_count
  ) values (
    $1,
    $2,
    1
  )
  on conflict
  on constraint placeid
  do update set
    user_count = locations.user_count + 1
  returning
    placeid, data, uuid
)
insert into users as u (
  password,
  email,
  first_name,
  last_name,
  location_uuid
) VALUES (
  crypt(
    $3,
    gen_salt('bf')
  ),
  $4,
  $5,
  $6,
  (select uuid from l)
)
RETURNING
  u.uuid,
  u.email,
  u.first_name,
  u.last_name,
  u.created,
  u.active,
  u.last_login,
  u.location_uuid
`;

In the insert user subquery I want to return the location table columns
placeid, data

However, if I append:

...
RETURNING
  u.uuid,
  u.email,
  u.first_name,
  u.last_name,
  u.created,
  u.active,
  u.last_login,
  u.location_uuid,
  l.placeid,       <------ this
  l.data           <------ this

I receive the following error:

 name:  error
 message:  missing FROM-clause entry for table "l"
 code:  42P01
 stack error: missing FROM-clause entry for table "l"
    at Connection.parseE (/home/imsov/git/d4d/node_modules/pg/lib/connection.js:553:11)
    at Connection.parseMessage (/home/imsov/git/d4d/node_modules/pg/lib/connection.js:378:19)
    at Socket.<anonymous> (/home/imsov/git/d4d/node_modules/pg/lib/connection.js:119:22)
    at Socket.emit (events.js:182:13)
    at addChunk (_stream_readable.js:283:12)
    at readableAddChunk (_stream_readable.js:264:11)
    at Socket.Readable.push (_stream_readable.js:219:10)
    at TCP.onread (net.js:639:20)

I don't want to make two "trips" to the DB (which would be effective but inefficient).

I figured it out:

You can only return fields affected by the insert (thus the error in subquery 2 return statement)

I needed to move the subquery into a CTE and then select the data I want to return

const insertUserSql = `
  with l as (
  insert into locations (
    data,
    placeid,
    user_count
  ) values (
    $1,
    $2,
    1
  )
  on conflict
  on constraint placeid
  do update set
    user_count = locations.user_count + 1
  returning
    placeid, data, uuid
), cu as (
  insert into users as u (
    password,
    email,
    first_name,
    last_name,
    location_uuid
  ) VALUES (
    crypt(
      $3,
      gen_salt('bf')
    ),
    $4,
    $5,
    $6,
    (select uuid from l)
  )
  RETURNING
    u.uuid,
    u.email,
    u.first_name,
    u.last_name,
    u.created,
    u.active,
    u.last_login,
    u.location_uuid
)
select
  l.data,
  l.placeid,
  cu.email,
  cu.uuid,
  cu.first_name,
  cu.last_name,
  cu.created,
  cu.active,
  cu.last_login,
  cu.location_uuid
  from l, cu
`;

However if there is a more efficient way to do this, let me know!