Powershell-type SQL Agent Job failing intermittently trying to get Disk Space on Server via WMI-query Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (X64)   Mar 29 2009 10:11:52   Copyright (c) 1988-2008 Microsoft Corporation  Enterprise Edition (64-bit) on Windows NT 6.1  (Build 7600: ) (VM)
Microsoft Server 2008 R2 Standard

ProductVersion 10.0.2531.0
ProductLevel   SP1
Edition        Enterprise Edition (64-bit)
EngineEdition  3

In trying to execute the following powershell-script...

$conn = New-Object System.Data.SqlClient.SqlConnection("Data Source=localhost\myServer; Initial Catalog=SysAdmin; Integrated Security=SSPI")

$conn.Open()

$cmd = $conn.CreateCommand()

gwmi -query "select * from Win32_LogicalDisk where DriveType=3" | select Name, FreeSpace, Size | foreach {
   $Name      = $_.Name.substring(0,1)
   $FreeSpace = $_.FreeSpace
   $Size      = $_.Size

   $cmd.CommandText = "INSERT dbo.DiskSpace (drive, [free(bytes)], [total(bytes)]) VALUES ('"+ $Name + "', " + $FreeSpace + ", " + $Size + ")"
   $cmd.ExecuteNonQuery()

   $cmd.CommandText = "EXEC dbo.sp_diskspace @performAggregation=1"
   $cmd.ExecuteNonQuery()
}

$conn.Close()

...I get the following error

  Message
  Executed as user: Domain\SqlSrvAgentSer.

  A job step received an error at line 7 in a PowerShell script. The corresponding line is 'gwmi -query "select * from Win32_LogicalDisk where DriveType=3" | select Name, FreeSpace, Size | foreach {'. Correct the script and reschedule the job. The error information returned by PowerShell is: 'Exception calling "ExecuteNonQuery" with "0" argument(s): "Incorrect syntax near ','."  Incorrect syntax near ','.  '.  Process Exit Code -1.  The step failed.

Additional Information

CREATE PROCEDURE dbo.sp_diskspace
   @performAggregation BIT = 0
AS

SET NOCOUNT ON;

DECLARE @aggregrateDate DATETIME

BEGIN
  SET @aggregrateDate = DATEADD(month, -1, GETDATE())
  SET @aggregrateDate = DATEADD(dd, DATEDIFF(dd, 0, @aggregrateDate), 0)

  INSERT INTO dbo.diskspace (
     drive,
     MeasurementDate,
     [free(bytes)],
     [total(bytes)],
     isAggregated)
  SELECT
      drive,
      DATEADD(dd, DATEDIFF(dd, 0, MeasurementDate), 0),
      AVG([free(bytes)]),
      AVG([total(bytes)]),
      1
  FROM
      dbo.diskspace
  WHERE
      MeasurementDate < @aggregrateDate
      AND isAggregated <> 1
  GROUP BY
      drive,
      DATEADD(dd, DATEDIFF(dd, 0, MeasurementDate), 0)

  IF @@ERROR = 0 and @@ROWCOUNT > 0
     BEGIN
        DELETE FROM dbo.diskspace
        WHERE MeasurementDate < @aggregrateDate
        AND isAggregated <> 1

        IF @@ERROR = 0
           BEGIN
              RAISERROR('sp_diskspace : aggregation complete', 0, 1)
           END
     END
END

Any help on this would be greatly appreciated!

Don't know if you're still looking for a solution to this but I'd change the Powershell for the insert command just a little.  Add this after the line setting $size = $_.Size

   $sql = "INSERT dbo.DiskSpace (drive, [free(bytes)], [total(bytes)]) VALUES ('$Name', $FreeSpace, $Size)"
   $sql | Out-File "C:\tmp\debug.sql" –Append # to wherever is appropriate for your system
   $cmd.CommandText = $sql

To me using the extra variable but getting an easier to read insert command is a good tradeoff and the debug.sql file makes finding sql errors much easier.
HTH


Insert multiple values in a text field using a "delimiter"? Let's say I have a Text field called "description" inside "products" table.

Inside this field I will store 3 values. I will separate them with two pipes ( || ):

requirements:
- bla bla.. (a text)
||
info:
- bla bla.. (a text)
||
specifications:
- bla bla.. (a text)

I am doing this because it looks better than having 3 different Text fields to do the job (which will increase the table's size).

Is this a bad idea? Should I use 3 different fields? Does 3 text fields really affect database performance?

Is this a bad idea?

Almost certainly. If code only ever treats that column as single un-parsed continuous string then it may be OK1. As soon as you want to pull one of the three values it is definitely a bad idea. Even if the parsing happens in the application it is additional complexity that need not be there.

  Should I use 3 different fields?

Yes.

  Does 3 text fields really affect database performance?

Not in any way you will ever notice. Depending on your particular DB software those "text" columns may be treated just like any other data type. Also you must consider how optimized your code to handle this will be compared to that written by a room full of PhD's who built the DBMS and do just this for a living.

1 Even then I'd say have separate columns.


How to get CPU usage of PostgreSQL server? I'm reading about PostgreSQL monitoring tool. I get a lot of information like database size etc. but can't find info about CPU and memory usage.
Are these informations provided by PostgreSQL server or do I have to get it by my native client application (for example, find PID of PostgreSQL and call OS API function).

That's not really a PostgreSQL monitoring tool, that's PostgreSQL's internal facilities for diagnostics, monitoring and database statistics.

For host monitoring you want a tool like Icinga (modernized Nagios) or Zabbix. When monitoring PostgreSQL the check_postgres tool is likely to be of interest.

If you're interested in getting the CPU use of a particular PostgreSQL backend process from your own custom programs, you must SELECT pg_backend_pid() or get the pid of the task of interest from pg_stat_activity then use OS-level tools and APIs to collect the desired information.

If you desired, you could write a PostgreSQL C extension function to collect the information and return it as a row or rowset over the PostgreSQL protocol, so you could SELECT my_system_stats(). See:

C-language functions
Extensions

Essentially, you write a library in C using the PostgreSQL APIs, compile it using the pgxs make helper and have the server dynamically load the resulting shared object. Then you tell it what C functions are callable from SQL using CREATE FUNCTION. You can then call them like any other function. C extensions can call OS-level APIs or do pretty much whatever they want, though it is generally very unwise for them to create threads, write directly to PostgreSQL files/filehandles, or read/write PostgreSQL sockets.


INSERT/UPDATE stored proc deadlocking itself I have a table (Database2.dbo.OrganizerDataDependencyChange) that contains information about when a row in certain other tables was last changed.  On each of these tables I have a trigger that calls a stored procedure (Database2.dbo.SaveOrganizerDataDependencyChange) that simply updates the Database2.dbo.OrganizerDataDependencyChange with the time that the trigger fired.  These triggers are fired from either Database1 or Database2, so they often are cross-database calls.

I'm getting read-write deadlocks on the Database1.dbo.OrganizerDataDependencyChange table when two different rows on the table are being updated and I don't understand why.  There's only one index on the table, and it's the clustered index that completely covers the query so no lookup deadlock is possible, and though there are two statements in the proc, I specifically re-wrote it to what I understand is the best way to avoid concurrency issues, with a WHERE NOT EXISTS instead of using IF/ELSE logic, so it shouldn't be coming at this index from different angles, right?  Since it's fired in triggers and cross-database I'm having some difficulty reproducing the issue, I can of course reproduce blocking but that should be fine and is what I expect.

Can someone help me understand what's going on here?  I  could probably fix it with a NOLOCK hint or maybe an applock but I still wouldn't understand why it was happening.

Here's the table:

CREATE TABLE [dbo].[OrganizerDataDependencyChange](
[TableID] [INT] NOT NULL,
[Database] [VARCHAR](150) NOT NULL,
[Updated] [DATETIME] NULL
)
CREATE CLUSTERED INDEX [CX_OrganizerDataDependencyChange_TableID_DB] ON [dbo].[OrganizerDataDependencyChange]
(
[TableID] ASC,
[Database] ASC
)

And here's the stored procedure:

CREATE PROCEDURE [dbo].[SaveOrganizerDataDependencyChange]
(
@TableID int,
@Database varchar(150)
)
AS
BEGIN

SET NOCOUNT ON;

    DECLARE @rowcount INT;

INSERT INTO dbo.OrganizerDataDependencyChange
        ( TableID, [Database], Updated )
SELECT TOP 1
    TableID = @TableID,
    [Database] = ISNULL(@Database, ''),
    Updated = GETDATE()
FROM dbo.OrganizerDataDependencyChange
WHERE NOT EXISTS
(
    SELECT
        1
    FROM dbo.OrganizerDataDependencyChange
    WHERE TableID = @TableID
    AND [Database] = @Database
)

SET @rowcount = @@ROWCOUNT  --How many rows were inserted?

UPDATE dbo.OrganizerDataDependencyChange
SET Updated = GETDATE()
WHERE @rowcount = 0 --Only run this if no rows were inserted
AND TableID = @TableID
AND [Database] = ISNULL(@Database, '')

END

And finally a couple of the deadlock graphs I'm getting out of XE that shows the ridiculous nested triggers:

Deadlock 1

Deadlock 2

MSSQL2014 Standard, if that makes a difference. Isolation level is the default Read Committed.  If there's anything else I can share to shed more light please let me know!

I think you will find this pattern better:

BEGIN TRANSACTION;

UPDATE dbo.OrganizerDataDependencyChange WITH (HOLDLOCK)
  SET Updated = GETDATE()
  WHERE TableID = @TableID
  AND [Database] = ISNULL(@Database, '');

IF @@ROWCOUNT = 0
BEGIN
  INSERT INTO dbo.OrganizerDataDependencyChange
      ( TableID, [Database], Updated )
  VALUES(@TableID, ISNULL(@Database, ''), GETDATE());
END

COMMIT TRANSACTION;

Stop thinking about having to "check" if there is a row to update, and then updating it, which can lead to two full scans. Just try to update it. If no rows are updated, no harm, no foul - you can simply perform an insert (I talk about this a bit here).


Select records with the same first column value and different second column value? SQL Server Assume we have a SQL Server table as the following:

ContentId |  TagId
--------------------
1               2
1               3
2               1
2               2
2               3
1002            1
1002            1003
1003            4
1003            1002
1004            1
1005            2002
1006            1
1006            1003
1007            1
1007            1003
1008            2
1008            3

How can we select records with the same ContentId and TagId in 1, 1003.
In my sample result is as follows

ContentId |  TagId
----------------------
1002            1
1002            1003
1006            1
1006            1003
1007            1
1007            1003

Or

ContentId
----------
1002
1006
1007

You could use intersect to get the matching records

SELECT ContentId
FROM dbo.Content
WHERE TagId = 1003
INTERSECT
SELECT ContentId
FROM dbo.Content
WHERE TagId = 1;

Result

ContentId
1002
1006
1007

DbFiddle


A change in the 5.6.20 release notes:

Redo log writes for large, externally stored BLOB fields could overwrite the most recent checkpoint. The 5.6.20 patch limits the size of redo log BLOB writes to 10% of the redo log file size. The 5.7.5 patch addresses the bug without imposing a limitation. For MySQL 5.5, the bug remains a known limitation.
As a result of the redo log BLOB write limit introduced for MySQL 5.6, innodb_log_file_size should be set to a value greater than 10 times the largest BLOB data size found in the rows of your tables plus the length of other variable length fields (VARCHAR, VARBINARY, and TEXT type fields). Failing to do so could result in "Row size too large" errors.

(emphasis mine)
The default for innodb_log_file_size is 50331648, which means the largest BLOB/TEXT value you can create, regardless of data type, is close to 5033164, and you discovered the precise value is 5033932. I suppose internally the calculation involves some fudge factor.
So you need to increase innodb_log_file_size if you want to store larger BLOB/TEXT data. Fortunately, changing the log file size is a lot easier in 5.6 than in earlier versions of InnoDB. Just add a line in your my.cnf with the new value, and restart mysqld.


Mapping a shared physical folder to filestream We are working on a project where we have large amounts of images, texts etc which we need to work on in SQL Server. We were thinking of using Filestream and filetable functionality. However, even after trawling through a lot of data on the net, we aren't able to find answers to the below queries:

We are trying to create a shared folder on our system and then map it to the filestream. So we are creating a shared folder (lets say D:\Ftable ) which has access level set to full access for everyone. Now when we go to the SQL Server configuration manager and try to set this folder as shared folder for filstream (the properties tab where we enable the filestream access) it throws us error : The windows share folder name is not a valid windows share. We know that filestream folder has to be a local folder, however even when we just try to put a different local folder path (like D:\test etc) it gives us an error.
We would also like to know if it is possible to create a filestream shared directory on a mapped drive? We have created a shared folder on one system and mapped that folder on a different system (so that it appears as local). However once again, when we try to set a specific folder on the mapped drive as filestream shared folder, it throws us an error.

Would really appreciate any pointers/help on the topic. Our main access is to create a filestream share on a physical location of our choice which is shared so that we can transfer data to it either remotely/ or via a tool.

this isn't exactly how filestream and filetables work, you can't specify the name of an existing share as SQL server will try and create the share that you specify in SQL Server Configuration Manager.  This is because the share is not mapped directly to a folder on the filesystem like a normal share, but an NTFS data container that lives in the filestream filegroup that is created for the database.

There is a misconception with filestream/filetable that because the data is stored in the filesystem, it is just a normal folder, this isn't entirely correct.

When you create your database, you specify that one of the filegroups is a filestream filegroup, this isn't like a normal filegroup and is actually a series of NTFS folders called Data Containers, this is where your files actually get stored, you can't modify this folder directly, but you can look at the contents (not that there's much human readable stuff in here though, you can find your actual files in here though if you look hard enough through the GUID folders and oddly named files :)).

The file share that you use to copy the files into the table is actually a representation of the data inside these data containers that is presented to Windows explorer via a filter driver which uses the streaming APIs to make the changes to the data containers and add the rows to the filetable etc.

So if you want to specify the location that the files are physically stored, you need to specify that location as where you want the filestream filegroup stored, then you can change the share name to whatever you want.


how to write conditional where clause query Hi I want write the mysql query conditionally. I am basically using the Wordpress, please have a look at my same tables.

tbl1

id      name

1   A
2   B
3   C
4   D
5   E

tbl2

id    post_id   key             value

1   1   m_type          1
2   1   m_date          2013-11-22
3   2   m_type          2
4   2   m_date          2013-11-20
5   2   m_routine       2
6   3   m_type          2
7   3   m_date          2013-11-10
8   3   m_routine       null
9   3   m_custom_rountine   5

I have the maintenance date in tbl2. So I want to get the all the post id filtered by following types.

Maintenance

Due today
Week due.
Year due.
over due.

tbl2 has the due date m_date. But My problem when m_type = 1. I need to add the where clause like WHERE CURDATE() = m_date else m_type =2 means m_date will be the starting date of maintenance and I need to increment the date using m_routine or m_custom_routine.

For example,
m_type = 2
m_date = 2013-11-22
m_routine = 2

Then I want check

CURDATE() = 2013-11-22 OR 2013-11-24 OR 2013-11-26 etc.

I was thinking to use CASE .. WHEN... THEN in WHERE clause. But I am not getting it properly.

Desired output

CURDATE() = 2013-11-22

Output post_id = 1,2 (2-routine incremented date from 2013-11-20)

CURDATE() = 2013-11-15

Output post_id = 3  (custom_routine incremented date from 2013-11-10)
Hope I explained well enough. Please someone help me.

SUDO code

if(m_type == 1){
    m_date = CURDATE();
}
else if(m_type == 2){
    increment_date = m_date increment m_rountine (OR) m_date increment m_custom_rountine
    CURDATE() = increment_date
}

Edit:-

I created the sql fiddle for your ref

It can be done using some multiple joins from table1 into table2 and then use those joins  combined with case/calculation logic, or using sub-selects.
But it seems rather complex to want to do that in a single query/single join - that I'd recommend finding an alternative way to do it. It's something which is a good candidate to solve in application logic.

However, I think the route you want to go is something like this:

SELECT
  T1.*,
  CASE
    WHEN T2.value = '1' THEN 'CalculateStuffBasedOnT3OrT4'
    WHEN T2.value = '2' THEN
        CASE
          WHEN t3.value IS NULL THEN 'CalculateOtherStuffBasedOnT3OrT4'
          WHEN T3.value = '2' THEN 'CalculateMoreStuffBasedOnT3OrT4'
          ELSE 'SomethingElse'
        END
     ELSE 'Something'
    END
FROM tbl1 T1
LEFT JOIN tbl2 T2 ON T1.id = T2.id AND T2.key = 'm_type'
LEFT JOIN tbl2 T3 ON T1.id = T3.id AND T3.key = 'm_routine'
LEFT JOIN tbl2 T4 ON T1.id = T4.id AND T4.key = 'm_date'

Such a query can then be used as foundation in a subquery to select the posts you want.

But this will quickly become quite complex and unmaintainable.
So I'd advice you to rethink the strategy and go with designing the query dynamically in you php code, or rethink the data structure, or utilize multiple queries to solve it.


When inserting the varchar value into the datetime field, it throws conversion error FROM_DATE column is DATETIME format in the temptable1stockdetails table.

Gradd19 column is VARCHAR format in the GradedDETAILS table.

I want to insert the Gradd19 value to FROM_DATE but it throws the following error:

  The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
  The statement has been terminated

How can I solve this issue.

Sample data of the Gradd19 in the GradedDETAILS table is:

Sep 16 2016 12:00AM
Sep 16 2016 12:00AM
Sep 16 2016 12:00AM
Sep 19 2016 12:00AM
Sep 19 2016 12:00AM
Sep 16 2016 12:00AM
20
60
20-09-2016
20-09-2016

Since you are not shared the sample data of the Gradd19 value, I suspect the date value is in the yyyy-dd-mm format, so when trying to convert (month value more than 12) it cause the out-of-range value error.

For sample, if the date format in yyyy-mm-dd, it will execute properly:

DECLARE @FromDate DATETIME;
DECLARE @Gradd19 AS VARCHAR (20) = '2016-11-15';
SELECT @FromDate = CONVERT(VARCHAR, @Gradd19, 105);
SELECT @FromDate;

Incase if it is in yyyy-dd-mm it will throw out-of-range value error

DECLARE @FromDate1 DATETIME;
DECLARE @Gradd191 AS VARCHAR (20) = '2016-15-11';
SELECT @FromDate1 = CONVERT(VARCHAR, @Gradd191, 105);
SELECT @FromDate1;

So in that case you need do one more conversion to skip the issue, like the below:

DECLARE @FromDate2 DATETIME;
DECLARE @Gradd192 AS VARCHAR (20) = '2016-15-11';
SELECT @FromDate2 = CONVERT(VARCHAR(19), CONVERT(DATETIME, @Gradd192, 105), 120)
SELECT @FromDate2;

So instead of

CONVERT(VARCHAR, Gradd19, 105) AS ExpiryDate

you need to use

CONVERT(VARCHAR(19), CONVERT(DATETIME, Gradd19, 105), 120) AS ExpiryDate

Update: As you mentioned in the comments the Gradd19 having the values of 20 and 60, it is difficult to filter those non-valid data. But based on the LEN() function you may avoid the non-valid data, then using the CONVERT you can get the VARCAHR field as DATETIME value.

Based on your sample data the valid date's minimum length is 10 characters, so you can apply the condition in the WHERE clause.

Sample execution:

DECLARE @GradedDETAILS  TABLE (Gradd19 VARCHAR (30));

INSERT INTO @GradedDETAILS (Gradd19) VALUES
('Sep 16 2016 12:00AM'),
('Sep 16 2016 12:00AM'),
('Sep 16 2016 12:00AM'),
('Sep 19 2016 12:00AM'),
('Sep 19 2016 12:00AM'),
('Sep 16 2016 12:00AM'),
('20'),
('60'),
('20-09-2016'),
('20-09-2016');

SELECT CONVERT(VARCHAR(19), CONVERT(DATETIME, Gradd19, 105), 120)
FROM @GradedDETAILS
WHERE LEN(Gradd19) >= 10

So in your actual query also you need to add the LEN(Gradd19) >= 10 condition and in the SELECT use this to convert as DATETIME by CONVERT(VARCHAR(19), CONVERT(DATETIME, Gradd19, 105), 120)


How connect to DB PosgreSQL in pgAdmin4 with port 84? Install DB Server PosgreSQL
postgresql-9.6.1-1-windows-x64.

After Install I change number standard port to 84.
In postgresql.conf

port = 84               # 5433 (change requires restart)

I restarted service and new port working.

After I try to connected DB Server by pgAdmin4.

But when I change Connection Port pgAdmin4 wrote ERROR

Can I connected to DB with port less 1024?

There is a very good reason this port restriction should be removed.  When using a Platform As A Service (PAAS) that creates endpoints based on services, it is typical to create the service on port 80 or 443 regardless of what is behind the URL.  Since this is just a host name, there is no reason why this should be blocked.  The landscape is different then the data center level as these services could be spun up at any time.  This requirement is arbitrary.


Error log - What does this mean and what can I do about it? I have the following repeated errors in my error.log and I don't know what it means or what I should/can do about it:

  Access denied for user 'root'@'[some IP]' (using password: YES)

There are many different IPs showing in the [some IP] field.

I've been reading some security posts that recommend changing the root username to something else, is this something I should do here?

I am using MySQL 5.5.22

I believe these are attempts to gain access to my database from hackers. I want to know what I can do to prevent the attempts. Most of the IPs are coming from China.

My first answer is: why is your database even accessible from outside through the Internet? That network traffic really ought to be blocked by router Internet gateway router or firewall.

If you really need to allow some connections from the Internet to your database, then limit it to the valid IP address who should be connecting.

At this point that's not really a dba question but a network admin question.


Is backing a website by a SQL Server 2012 OLAP cube considered reasonable? I have been tasked with architecting a solution for a large retail chain. They want to allow each of its 1.2 million customers to log on to a web site to see the distribution of recent purchases (current month, previous month, year-to-date) over about 50 categories.
Data will be updated once every day.

I am thinking of putting up a SQL Server 2012 based OLAP cube and letting the website query this cube directly, leveraging features like proactive caching. However, being a developer at heart, I have next to no experience with the analysis services parts of SQL Server, so am quite concerned about the performance of this solution.

Does connecting a web site directly to an OLAP cube sound like a feasible solution? Do such systems react to the load from multiple users roughly like a SQL Server, making this a reasonable solution, or do they act completely differently?

I don't expect users to check their status very often and I will of course be using caching on the webserver etc.

You could do this with an OLAP system - some of the benefits of SSAS for this type of application include:

SSAS can readily scale out - especially as this is a read-only application with no requirements for cube writeback.
Aggregations can be tuned to minimise the I/O allowing the cubes to be tuned for efficiency.
OLAP client software and third party controls (web and rich client) are readily available from a number of vendors.
SQL Server 2012 Business Intelligence edition has pretty much all of the scalability features for SSAS, so it can be used as a cost-effective platform to front cubes for a SQL Server enterprise edition (or third party) database.  Note that licensing may be an issue for this as B.I. edition is CAL-only.
SSAS has a data mining function that could be used to do a shopping basket analysis on the data and feed a 'suggested purchases' feature on the website.

On the other hand, the requirement is to show a relatively constrained data set, so the ad-hoc slice-and-dice capability of an OLAP server may be overkill, both in cost of software and cost of the hardware infrastructure to run it (SSAS is quite resource hungry).  You could probably achieve your immediate requirement with a periodically refreshed summary database, and do it with less hardware and licensing costs.

From a first glance, I would suggest OLAP is probably not necessary to fulfil your existing requirement.  However, it could certainly be done this way and you might get some mileage from the data mining features to provide a 'suggested purchases' feature.


How to avoid Recovery Catalog's metadata being purged after 60 days? I'm using Oracle's Recovery Catalog to track my db's backups metadata (RC ver. is 12.01.00.02 )

I need to collect historical backup sizes but then I've realized there's no metadata information about completed backups beyond 60 days. (however, there's for FAILED and incompleted backups)

e.g. I'm trying to collect this data from rc_rman_backup_job_details view. This RC's was used since Q1 2015, but only 60 days of completed backups are shown.

RMAN@SERESCATALOG> SELECT status, MIN(start_time)
      2  FROM rc_rman_backup_job_details
      3  GROUP BY status
      4  ORDER by status;

STATUS      MIN(START_TIME)
----------- --------------------
COMPLETED   03-SEP-2016 01:31:34
FAILED      18-SEP-2015 09:03:50
RUNNING     28-MAY-2015 02:39:36

What controls this retention? There's any way to change it? Any documentation?

Regards.

This is described in the note:

How to Clean up The information in EM Backup Report (Doc ID 430601.1)

Edit recover.bsq, find the procedure cleanupRSR, change the default value 60 as needed:

  DELETE FROM rsr
        WHERE rsr_end < nowTime-60
          AND rsr.dbinc_key IN
              (select dbinc_key from dbinc
               where dbinc.db_key = this_db_key);

When finished:

RMAN> upgrade catalog;


Windows Server 2019 non-owner node cannot see Cluster Name or IP I am trying to build a SQL Server AlwaysOn Availability Group with 2 nodes in Azure. I have created 2 SQL Server VMs and Managed to create a Cluster with both Nodes as members of that cluster.

I have followed the steps in this Article of building the Windows Clusters https://docs.microsoft.com/en-us/azure/azure-sql/virtual-machines/windows/availability-group-manually-configure-tutorial
with 1 significant change, I used powershell to create the cluster.

New-Cluster -Name SQLClusterName -Node ("SQLNode1","SQLNode2") –StaticAddress 172.100.10.100 -NoStorage -ManagementPointNetworkType Singleton

Because, if I used Failover Cluster Manager to build the cluster it did not created Cluster Core Resources and I did not gave me the option to select an IP for the cluster.

Anyway, once the cluster has been configured, I tried to configure AlwaysOn Availability Group, but the wizard fails to create Always on Availability Group, complains about it cannot connect to the Cluster.

I went on both Nodes and what I have realised is that I can ping to the Cluster Name or IP and get a response only from the Owner Node. From the cluster Non-owner node I could not ping the cluster name or IP. If I failover the cluster to the 2nd node the I ping behaviour will be contra versa.

Has everyone else experienced this issue? Is so, how you managed to resolve it? Any help or a pointer in the right direction is much appreciated. Thanks in advance.

I managed to fix this by enabling HA ports in the Azure Load Balancer (standard Load Balancer). That seems to have fixed the issue.
By the way I have SQL Server 2019 Enterprise on Windows Server 2019.
Set the following Load Balancing rule in the Azure Load Balancer for WFCS.

The source of this information for this article : https://docs.microsoft.com/en-us/azure/azure-sql/virtual-machines/windows/availability-group-manually-configure-tutorial#add-the-cluster-core-ip-address-for-the-windows-server-failover-cluster-wsfc
The article does not talk about HA ports because it is a Standard Azure Load Balancer feature and the article was written using a Basic  Azure Load Balancer.


New query slower even if it has less logical reads than the old one I have a query in a stored procedure that must list all dates in a range and JOIN on it date from a table, if it exists for that day.
The procedure was created before I was employed as the DBA, so I thought I might optimize it a bit. However, it seems I've hit a brick wall, and I cannot understand why.
The current implementation of the procedure SELECTs data from two normal tables (let's call them TableA and TableB) as a derived query (in the FROM clause), and then has this as a RIGHT JOIN on it:
RIGHT JOIN (
     SELECT DATEADD(DAY,number,@DateFrom) AS DATE
     FROM (
         SELECT DISTINCT number
         FROM master.dbo.spt_values
         WHERE name IS NULL
     ) n
     WHERE DATEADD(DAY,number,@DateFrom) <= @DateTo
) AS y ON derived.Date = y.Date -- "derived" is an alias of the derived query

Since this same code is used in multiple procedures, I thought I would create a table with dates from 2005-01-01 to 2039-01-01:
CREATE TABLE Dates(
     [Date] DATE NOT NULL PRIMARY KEY
)

The table was filled from a warehouse that had a dimension with dates.
So, instead of that RIGHT JOIN, I've written:
FROM Dates d
LEFT JOIN derived ON derived.Date = d.[Date] -- the "derived" table was moved to CTE, since that's easier to read
WHERE d.[Date] BETWEEN @DateFrom AND @DateTo

Satisfied with the code, I've turned on STATISTICS IO and executed both versions of procedure code and was completely shocked with the result.
The statistics of the old query:

Table 'TableA'. Scan count 0, logical reads 456516, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TableB'. Scan count 367, logical reads 3949, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'spt_values'. Scan count 1, logical reads 14, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

The statistic of the new query:

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TableB'. Scan count 1, logical reads 1767, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TableA'. Scan count 1, logical reads 6, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Dates'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

But, the second query is slower.
When looking at the execution plans, it says that the first query takes only 2% of cost, while the second one takes 98%. Both have the same Index Seek on TableB (the larger one), with the difference:

in the first query, that Index Seek executes 367 times, and costs 5.4% of the full query,
in the second query, that Index Seek executes only 1 time, but costs 47.1% of the full query.

In both cases, the number of rows returned from the Index Seek is 228258.
Here are both execution plans, as well:

And in the XML format:
XML plan of both queries
My questions are:

Why is the second query slower?
Can I make the second query faster?

The percentage costs on an execution plan are from the optimizer estimates, even when an actual execution plan produced. The actual execution plan does use the exact plan and include both the estimated rows and the actual row counts. Discrepancies between the row counts can be useful to determine how accurate the estimate was.

Somewhere between the subquery and comparing it to the column in derived, the optimizer wasn't able to correctly estimate how many rows would match. It guessed that there would be 18 rows from derived when there were actually over 220,000. An additional clue is the warning message Cardnality Estimate: CONVERT(nvarchar(35),[mssqlsystemresource].[sys].[spt_values].[name],0) on the SELECT node.

If you were to check the query run length with something else, such as STATISTICS TIME, I would expect them to be much closer, and likely the second query running faster.

Here's another plan analysis with a somewhat similar situation. (SQL Server Plan Explorer) (with hat tip to Kendra Little on how to fool the optimizer)

The estimate shows a 93%/7% cost split, but by looking at the actual CPU, time, or IO, the difference is not that extreme. IO is about 75%/25% and CPU is roughly 60%/40%. (I tried to come up with something more even, but wasn't able to.)


How can I obtain continuous results with a PostgreSQL query? I am using PostgreSQL version 8.1.  I have a table of dates and tasks.  The tasks have start times and end times in the table.  The query below does not work as expected.

SELECT sites.abbrev,
       (SELECT count(*)
        FROM (
          SELECT DISTINCT measurements.task
          FROM measurements
            JOIN tasks ON tasks.id = measurements.task
          WHERE measurements.ztime >= '2016-10-10'
            AND measurements.ztime <= '2016-10-15'
            AND tasks.site = sites.id
        ) ALIAS
      ) AS num_tasks,
      count(*) AS total_time
FROM sites
  JOIN tasks ON tasks.site = sites.id
  JOIN measurements
    ON (measurements.task = tasks.id
   AND measurements.ztime >= '2016-10-10'
   AND measurements.ztime <= '2016-10-15')
WHERE sites.abbrev = 'AA-10'
GROUP BY sites.id,
         sites.abbrev,
         measurements.ztime::date
ORDER BY measurements.ztime::date;

The results are shown below:

 abbrev | num_tasks | total_time
--------+-----------+------------
 AA-10  |        62 |         36
 AA-10  |        62 |          5
 AA-10  |        62 |         58
 AA-10  |        62 |         28
 AA-10  |        62 |         17
(5 rows)

Each row corresponds to one 24 hour period (Ex. 2016-10-10 to 2016-10-15).  The problem is that I need num_tasks to show me the results for each 24 hour period, not the total number as shown.

PostgreSQL version 8.1 does not allow for dates to be used with generate_series and there are no windowing functions available either.

How can I modify my query so I can achieve the desired results?

I don't think you need the subquery. Would this work?

SELECT sites.abbrev,
       measurements.ztime::date AS period_start,
       count(DISTINCT measurements.task) AS num_tasks,
       count(*) AS total_time
FROM sites
  JOIN tasks ON tasks.site = sites.id
  JOIN measurements
    ON (measurements.task = tasks.id
   AND measurements.ztime >= '2016-10-10'
   AND measurements.ztime < '2016-10-15')
WHERE sites.abbrev = 'AA-10'
GROUP BY sites.id,
         sites.abbrev,
         measurements.ztime::date
ORDER BY measurements.ztime::date ;


Attaching database to the SQL server I have the following error

  The database 'Test' cannot be opened because it is version 661. This
  server supports versions 655 and earlier. A downgrade path is not
  supported.

Usually this kind of error occurs when the database was made with SQL Server 2008 R2 and is opened with for example SQL Server 2008 SP2, but the problem is that I'm trying to open it exactly in SQL Server 2008 R2 which supports version 661. So what's the problem?

You are connected to a SQL Server 2008 instance. The error message clearly indicates that. See How to determine the version and edition of SQL Server and its components, the section on SQL Server Database Engine version lists no less than 4 methods:

  Method 1: Connect to the server by using Object Explorer in SQL Server Management Studio. After Object Explorer is connected, it will
  show the version information in parentheses, together with the user
  name that is used to connect to the specific instance of SQL Server.
  Method 2: Look at the first few lines of the Errorlog file for that instance.
  Method 3: Connect to the instance of SQL Server, and then run the following query:

  Select @@version;
  Method 4: Connect to the instance of SQL Server, and then run the following query:

  SELECT SERVERPROPERTY('productversion'),
       SERVERPROPERTY ('productlevel'),
       SERVERPROPERTY ('edition');


SQL Server transaction log growing without control We have a database growing uncontrolled and filling the hard drive every 2 days. Database maximum log size is set to 6 gb but id doesn't seem to be working because it continues growing beyond 6 gb.

The only way to "solve" it somehow is backing up and truncating after that.

First, wow's that possible database continues growing if maximum log size is set to a specific size?

Second, if transactions are opened and closed properly, we know it's working well, why my log file grows uncontrolled?

The only way to "solve" it somehow is backing up

Because that is the only way to truncate the log. See How to Shrink the SQL Server log for an explanation on what happens. Normally log backups are scheduled very frequently (think 10 minutes) and they truncate the log. You want a frequent log backup not only because it eliminates the growth problem, but primarily because you want to minimize the amount of transactions lost in case of disaster (crash).

If you want a different behavior, change the recovery model to SIMPLE. In case of failure you will loose all the transactions since your last full backup, but there is no free lunch, is there?


SQL Server maintenance plans I use the maintenance plan of SQL Server, but it does not work well.

Transaction log truncation flow in the maintenance plan:

① Database consistency check task
↓
② Database compression task
↓
③ Database backup task
(Transaction log backup)
↓
④ Truncate transaction log area (execute T-SQL)

If run the maintenance plan manually in the manual server, it will successfully terminate the transaction log area and reduce the size.

The size is not reduced while the transaction log area is truncated, even though the schedule is successfully terminated.

Is there any problem with the execution of the maintenance plan?

USE [XXX system _SQL]
GO

DBCC SHRINKFILE (XXX system _SQL_log, TRUNCATEONLY)
GO

If you need to shrink your log file often, it means that the size it has after a shrink is not sufficient for your everyday activity, so it grows again and again.

This means that you do unnecessary work when shrink it, and, that is worse, every time your log needs to expand, it wastes time zeroing out every new piece of file it adds (and your users WAIT for the completion of this zeroing out).

You should stop this double waste of time and eliminate log shrinking from your maintenance plan. If you are not satisfied with the size of your log, shrink it once and then make your log backups more frequently.


Monitoring for and dealing with sudden jump in TOAST bloat I'm trying to diagnose a sudden jump in disk usage in one of my databases (running 11.4). Typically the database grows by ~1 GB per day, but recently in the span of 24h it grew by nearly 100 GB.
Looking at various statistics, I concluded the growth came from a particular TOAST table. The table is used for a BYTEA column which stores an encrypted blob of a couple of MBs. There are ~1000 rows, but the TOAST table takes up 400 GB.
These rows are updated frequently for a few days after they're first created and then aren't touched again.
The DB is a multi-AZ RDS deployment, so my best hypothesis is that replication fell behind, causing a bunch tuples to be kept alive and making the TOAST table suddenly grow.
Is that a sensible hypothesis? How can I confirm if it's the case? What are possible workarounds to avoid this hitting us again?

Additional data:

The pg_toast table has ~900k live tuples
Dead tuples were at 5.2M this morning but at 1.7M now
The pg_table_size hasn't budged in a few hours for the pg_toast table, despite continued activity on the underlying table

If you want to check how bloated the table is, you should install the pgstattuple extension and execute pgstattuple() on the suspected table. If the table is large, as in your case, you can use pgstattuple_approx() to get an approximation.
Regarding your hypothesis, I don't think that Multi-AZ can explain your issue, as PostgreSQL Multi-AZ replication is synchronous. Network issues between master and standby would halt the activity on the master.
Other possible explanation might be:

Autovacuum not being able to catch up with many deletes or updates.
This can happen because of database load that is unrelated to that
query. Look at your logs and CloudWatch metrics for clues
If you have a regular read-replica (not Multi-AZ) then your hypothesis could explain what has happened. There is a CloudWatch metric that shows the replication lag.


Find a row by primary key, when you don't know in which table it is? I'm the (inofficial) DBA responsible for a proprietary OODBMS. Management wants us to move to Postgres, to reduce license costs. The move should be progressive, therefore we should also keep the same data-structure as in the OODBMS. Luckily, with the support for ARRAYs, and table inheritance, we can literally create an identical schema in Postgres. All tables will use bigint as primary key, and all (indirectly) inherit from the same base table.

The biggest issue is this: Our bigint key are (and must be) unique across all tables, and we must be able to quickly load a set of rows, based on their primary key, without knowing in which table they are. The rows will be spread over any and all tables. The primary concern here is speed, rather than disk or memory usage.

Expressed differently, what we need is a unique index across all tables. AFAIK, this is not possible in Postgres. What is the "next best" option? I'm open to any solution, including using, or maybe even coding, some "Postgres extension".

To give some hints about the actual DB, we are talking about 300 tables, 130M rows, and about 300GB in size (OODBMS size).

All tables will use bigint as primary key, and all (indirectly) inherit from the same base table

I am not sure I like this "all tables inherit from the base table", but given that, it sounds doable with Postgres:

To generate the primary key for all tables, create one sequence:

create sequence one_for_all as bigint;

Create the base table using that sequence to generate the values:

create table base (id integer primary key default nextval('one_for_all'));

Note that the primary key will not be enforced for the child tables!

Then create the child tables:

create table t1 (t1_data integer, primary key (id)) inherits (base);
create table t2 (t2_data integer, primary key (id)) inherits (base);

If you now insert into the child tables, the sequence will be used to generate the ID:

insert into t1 (t1_data) values (100);
insert into t2 (t2_data) values (200);

To find in which table a row is located, select from the base table and include the tableoid column, that identifies the actual table in which the row is located:

select b.*, tableoid::regclass as actual_table
from base b
where id = 42;

You will still need another query to return the full row of the child table.

Online example: https://dbfiddle.uk/?rdbms=postgres_11&fiddle=4305e86b996e7a94c24faed733257232

Another approach that does not need inheritance is to generate ID values that encode the table name (shudder).

Something along the lines:

create sequence one_for_all as bigint;
create table lookup (table_name text primary key, code serial unique);
insert into lookup (table_name) values ('base'), ('t1'), ('t2');

create function get_id(p_tablename text)
  returns bigint
as
$$
  select nextval('one_for_all') * 1000 + code
  from lookup
  where table_name = p_tablename;
$$
language sql;

create function get_tablename(p_id bigint)
  returns text
as
$$
  select table_name
  from lookup
  where code = p_id % 1000;
$$
language sql;

By multiplying the value from the sequence with 1000 we essentially have the lower 3 digits available to encode a unique number for each table. In order to be able to lookup those numbers, we need that additional table.

Note that this will fail miserably if the lookup table does not contain all tables!

Then instead of using nextval() use, the get_id() function:

create table base (id integer primary key default get_id('base'));
create table t1 (id integer primary key default get_id('t1'), some_value integer);
create table t2 (id integer primary key default get_id('t2'), some_data text);

Then you can do:

insert into t1 (some_value) values (42);
insert into t2 (some_data) values ('foo');
insert into t1 (some_value) values (117);
insert into t2 (some_data) values ('bar');

So the rows in t2 now go the IDs 2003 and 4003. The function get_tablename() can be used to retrieve the table name to which an ID belongs:

select get_tablename(2003);

get_tablename
-------------
t2

This is certainly faster in terms of looking up the table name and does not carry the baggage of a huge inheritance tree with it. So performance wise it's probably faster. It will however become a maintenance nightmare.

Populating of the lookup table could perhaps be done through event triggers whenever a table is created or dropped.

If you can change your application to support varchar primary keys, instead of bigint (and you can live with the slightly higher storage requirements), you could also generate the ID as a string that simply contains the tablename:

create function get_id(p_tablename text)
  returns text
as
$$
  select concat(nextval('one_for_all'), '_', p_tablename)
$$
language sql;

Things like sorting and range queries will be complicated though.


Index based on another index Assume a mysql table with columns A, B, C, ... .

There are one composite index based on columns A+B+C. Combination of values A, B and C is unique, so this index forms the primary key.

Now, a second index is need. There are two possibilities for this one: a) A+B+D+E; b) D+E.

I wonder which one of the previous options for the second index is better. Any hint is welcome.

Thanks.

As you gave no details of what you want to accomplish to base any hints on, I can tell you only the generic stuff:

If it is InnoDB then each secondary key contains the primary key values to find the right rows, so (a,b,d,e) is extended to (a,b,d,e,c) and (d,e) to (d,e,a,b,c) - as you can see, the columns indexed are the same but the order is not, and order of columns in multicolumn index is as important as the actual columns so those two are quite different in their effects.

If it is MyISAM, then the extension does not apply, each index references the row by a pointer to heap, so the indexes are totally different.

There is no universal notion of "better", it depends on what do you need the indexes for. Query speedup? Or are they supposed to be unique?


PostgreSQL CREATE EXTENSION file not found error on Mac OS On PostgreSQL 9.2.3 when trying this:

CREATE EXTENSION btree_gist;

I get this error:

  ERROR: could not open extension control file "/opt/local/share/postgresql92/extension/btree_gist.control": No such file or directory

Running SELECT pg_available_extensions(); shows that indeed is not available.

The problem is that the needed contrib files are not installed. This was actually the case for several contrib modules.

I found help with the solution to this here:
https://wiki-bsse.ethz.ch/display/ITDOC/Get+PostgreSQL+contrib+modules+on+Mac+OS+X

$ su
# port build postgresql92
# cd `port work postgresql92`/postgresql-<version>/contrib
# for d in *; do test -d $d && ( echo $d; cd $d; make all && make install; cd .. ); done

I suspect reinstalling everything would work too, but this highlights the actual problem.


sys.dm_os_volume_stats return no result Version - SQL Server 2012 SP1 CU3.

I have been using sys.dm_os_volume_stats for a while and it was working. Recently I have not been able to return any result from this DMF on one SQL Server 2012 instance.

e.g.

SELECT * FROM sys.dm_os_volume_stats(1, 1);

I have no issues with the same code on other SQL Server 2012 or SQL Server 2008 R2 instances. Does anyone know the reason behind and has fixed this problem?

After some tests, in order for the DMF to work, it appears that the service account needs to have at least READ access to the root volume of where the database files are located at, in addition to the SQL Server login security VIEW SERVER STATE permission.

The READ permission does not have to be granted explicitly to the service account. It could be granted through other user/groups,

1) SQL Server service account
2) Everyone
3) Users
4) NT Authority\Authenticated Users

The idea is SQL Server service account needs at least READ permission to the root volume. I have listed the test and details here.


Incorrect information in file [ERROR] mysqld: Incorrect information in file: '/mysql/#sql50f_eb5128_1.frm'

Received this error while restoring old snapshot. How to confirm which table which is causing this error message? I'm sure this must be MySQLdatabase as all other databases are INNODB. I am using mariadb 5.5.

after some research and consult with other DBA friends concluded that it is a temporary table as any temporary table is named in this pattern only.


Concurrency / Transaction Concerns with PostgreSQL Copy (bulk insert) We're building a CQRS system backed by Postgres. As part of this, we generate 'readmodels' which are precalculated projections of event sourced data. We regularly need to create new tables, query event sourced data, calculate a  projection and insert thousands of rows (mostly containing jsonb data) into new tables.

These inserts need to be done inside a transaction which also includes reads and writes to other tables.

Our initial code used standard INSERT ... ON CONFLICT UPDATE commands and achieved about 100 rows per second(). When however I changed it to use COPY ... FROM STDIN (FORMAT BINARY) the throughput improved by a factor of about 100. I'm aware that I can't do 'upserts' in the same way with the second method but was just planning to delete records before the COPY insertion (inside a transaction).

I'm thinking to use COPY flow of our application. I've received criticism however along the lines of "I doubt it will work. COPY is designed for bulk import. You'll have concurrency issues".

My gut feel is that with the MVCC and transactional DDL of Postgres, it's probably robust enough to handle something like this without breaking a sweat. I've had fear, uncertainty and doubt sown in me though!

My questions:

1/ Can I use COPY in normal transactions as I would other statements?

2/ Can mass COPY loads happen while the database is still live with normal production inserts and selectes still running against other tables?

3/ Can multiple connections concurrently perform COPY operations? (against different tables)

4/ Am I crazy for thinking to treat this like any other PG command? Is it designed to be used against live, production databases or is it more of a housekeeping tool.

We're using NPGSQL as the client library to Postgres.

Thanks!

Can I use COPY in normal transactions as I would other statements?

Yes, You can. This will invoke the trigger, check constraint and other
constraints as well. But it will not invoke the rules. It supports
MVCC and ACID.

Can mass COPY loads happen while the database is still live with normal production inserts and selects still running against other tables?

Yes, But its based on your workload. Do a test in Dev infra.

Can multiple connections concurrently perform COPY operations?

Yes it'll. But keep an eye on the resource utilization.

Am I crazy for thinking to treat this like any other PG command? Is it designed to be used against live, production databases or is it more of a housekeeping tool.

You can trust and use it in production. No issues.

References:

Read the Notes section from PG documentation

Someone implemented the same in Production with 10m rows file


Why is DTA recommending non-clustered indexes that match the clustered Primary Key index? I'm only vaguely familiar with SQL Server's Database Engine Tuning Advisor (DTA) but I'm using it to suggest recommendations for indexes on my database.

Most of the tables in the database at present have a single index which is the Primary Key. It is defined during the tables' creation as:

CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED ([ID] ASC)

When the DTA runs, it is advising that I add additional non-clustered indexes on some tables containing only the [ID] column, e.g.:

CREATE NONCLUSTERED INDEX [_dta_index_Employee_18_1170844725__K1] ON [dbo].
[Employee]
(
    [ID] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]

What I'm wondering is why it's recommending I create an index which exactly matches an index that already exists. Is there some benefit to the query optimizer of there being a non-clustered index as well as the clustered one?

For what it's worth, in most instances the [ID] column is also an IDENTITY column.

The index does not match exactly what already exists.  The Clustered index (the primary key) contains all the other data related to the table.  But the nonclustered index (NCI) contains just the primary key. The resulting NCI is much narrower than the clustered index.

Having a non-clustered index on just the primary key has a few benefits that may be relevant here.  But, I also want to caution you to take the DTA and the suggested missing indexes with a grain of salt.

Benefits of having a non-clustered index in addition to the clustered index (on the same key).

Foreign Key Check Constraints - If other tables use this one as a
foreign key reference, then having a narrow NCI will speed this up
dramatically.
COUNT or EXISTS - If you frequently check for the existence of this
value (or count them) without any other conditions, then having an
NCI will also be beneficial.  Having a narrower index to use for
these is extremely efficient, especially if the base table is very
wide.

Basically, even though both would be seek operations, if the NCI can be used it will typically be very narrow and can fit more ID values per page on disk so will reduce memory grants.


Optimizing MYSQL statements Recently, I started working on a project, where I came across many MySQL statements like this:

SELECT s.Important, c.Interesting
FROM table1 s, table2 c
WHERE s.IdForTableC = c.id

or

SELECT q.Important, c.Interesting
FROM table1 q, table2 p, table3 c
WHERE q.IdInTableP = p.id
AND p.IdInTableC = c.id
AND c.Measure = 1

I am completely new to optimizing MySQL and read a few guides that advised me to use EXPLAIN SELECT. I did and get type all for the above query and type index for the second one. I guess this means, that both queries should be optimized, but I don't know how. Is it even possible without changing the whole structure of the database?

EDIT: the EXPLAIN output of the first one is:

'1', 'SIMPLE', 'c', 'ALL', 'PRIMARY', NULL, NULL, NULL, '467', ''
'1', 'SIMPLE', 's', 'ref', 'IdInTableC', 'IdInTableC', '5', 'table2.c.id', '2', 'Using where'

and INDEXES are:

'table2', '0', 'PRIMARY', '1', 'id', 'A', '14184441', NULL, NULL, '', 'BTREE', '', ''
'table2', '1', 'otherID', '1', 'otherID', 'A', '92708', NULL, NULL, '', 'BTREE', '', ''
'table2', '1', 'IdInTableC', '1', 'IdInTableC', 'A', '3048', NULL, NULL, '', 'BTREE', '', ''

Query 1

EXPLAIN is not simple to follow.  First, let me explain how to optimize the two queries.

SELECT s.Important, c.Interesting
FROM table1 s, table2 c
WHERE s.IdForTableC = c.id

That's the old way to write a JOIN; please learn the new way:

SELECT s.Important, c.Interesting
    FROM table1 s
    JOIN table2 c  ON s.IdForTableC = c.id

The ON explains how the two table relate to each other.

Now there is no filtering via a WHERE clause, so all that can be done is

Go through all the rows of one of the tables.  (No index is useful)
Foreach row, reach into the other table.

If s is used for step 1, then an index on id is needed in c for step 2.
If c is used for step 1, then an index on IdForTableC is needed in s for step 2.

The convention is to name the PRIMARY KEY "id".  A table must have a PRIMARY KEY.  I'll guess that c has PRIMARY KEY(id).  This makes starting with s a slam-dunk.  But, instead, the Optimizer decided to start with c -- as seen by it being row 1 in the EXPLAIN.

"ALL" means that the entire table is scanned.  Some of the NULLs go along with that.  "PRIMARY" does not mean much in this context.  c has about 467 rows.

To get into s, it used table2.c.id.  Something is wrong there.  That syntax says "database table2, table c, column id".  Is your database called "table2".  Ugh.

The index named IdInTableC was used.  Presumably, you had

INDEX(IdInTableC)

so that name is the same as the column.  5 probably means it is a NULLable INT.  Use NOT NULL unless you have a purpose for NULL.  The JOIN will find an estimated 2 rows in s.

I guessed wrong as to which order c and s would be used.  But the symmetry of the query implies that there might not be a lot of difference in which to start with.

Query 2

Again, let's use the new JOIN syntax:

SELECT q.Important, c.Interesting
    FROM table1 q
    JOIN table2 p  ON q.IdInTableP = p.id
    JOIN table3 c  ON p.IdInTableC = c.id
    WHERE  c.Measure = 1

It is usually best to "filter" first.  The only filter is c.Measure = 1, so c will probably be the first table, especially if you have

INDEX(Measure)

Then, the chain must be:  c -> p -> q.  So these are needed:

p:  INDEX(IdInTableC)
q:  INDEX(IdInTableP)

With those indexes, all three tables should use INDEX, perhaps with ref or eq_ref.

More Tips

In the Extras column:

"Using index" means that the index is "covering".  That is, all the columns needed are found in the index.  This is a minor performance boost.
"Using index condition" is different and mostly out of your control; it also implies a perf boost.
"Using join buffer" -- the second table is fully loaded into memory; this may be a big perf boost.
"Using temporary, Using filesort" -- People wrongly fear these.  There is some performance loss, but usually one or both are required.  After all, ORDER BY implies a sort.  The absence of "filesort" implies an optimization that is sometimes available.

For some basic index-creation tips, see http://mysql.rjweb.org/doc.php/index_cookbook_mysql

I don't use EXPLAIN that much; I try to go straight from the SELECT to the optimal index.


Troubles connecting to a SQL Server Express instance on AWS I'm trying to remote connect a SQL Server Express instance that is running on AWS on a db.t2.micro. I'm getting the following error:

  A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 0 - The requested name is valid, but no data of the requested type was found.) (Microsoft SQL Server, Error: 11004)

I'm behind a proxy, but I should have route the address so the proxy is bypass for the IP of the SQL Server Express instance. What else can I try?

SQL Server Express does not enable a TCP/IP listener by default. You must enable it using SQL Server Configuration Manager to allow remote connections.


Add New Interface for Oracle DB I believe there are similar question about this. But I need more explanation about my plan.

I have an 11g Oracle database running with one listener on port 1522. And currently using one interface (one IP for the server). Some client using another segment of IP, which always use router to communicate with server. Every morning, my router get heavy load because some different segment user create connection to database's IP.

From this situation, I want to add new interface for the server and assigned same IP segment for those client. From netstat I assume that no need extra configuration from oracle side. Here is the netstat result.

I will only add the IP address from OS side. And change the tnsnames.ora of client to connect to new IP address of server.

Is my scenario will work well? Or will I need to reconfigure the listener and database to work with my situation?

You need to add that new IP in your listener.ora file and modify tnsnames.ora file to load balance the connection requests to two different IPs.

Let's say I have database server and one listener is serving at 192.168.30.133/Port 1522. Next, I want to add new interface with IP 192.168.30.144.

Then I need to add the second IP address to my listener.ora file as shown below.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.30.133)(PORT = 1522))
    )
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.30.144)(PORT = 1524))
    )
  )

In order to balance the load of client connections between these two IP address you need to modify tnsnames.ora file at client site as shown below.

 ORAPDB1 =
  (DESCRIPTION =
   (ADDRESS_LIST =
    (LOAD_BALANCE = on)
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.30.133)(PORT = 1522))
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.30.144)(PORT = 1524))
   )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orapdb1)
    )
  )

Let's try to connect to the database and see whether the connection requests is distributed to these IPs or not.

[oracle@ora12c trace]$ lsnrctl status listener1

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 22-FEB-2017 10:46:07

Copyright (c) 1991, 2014, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.30.133)(PORT=1522)))
STATUS of the LISTENER
------------------------
Alias                     listener1
Version                   TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date                22-FEB-2017 10:44:36
Uptime                    0 days 0 hr. 1 min. 31 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/12.1.0/db_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/ora12c/listener1/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.30.133)(PORT=1522)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.30.144)(PORT=1524)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=ora12c.dba.com)(PORT=5500))(Security=(my_wallet_directory=/u01/app/oracle/product/12.1.0/db_1/admin/oracdb/xdb_wallet))(Presentation=HTTP)(Session=RAW))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=ora12c.dba.com)(PORT=5501))(Security=(my_wallet_directory=/u01/app/oracle/admin/oracdb/xdb_wallet))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "oracdb" has 1 instance(s).
  Instance "oracdb", status READY, has 1 handler(s) for this service...
Service "oracdbXDB" has 1 instance(s).
  Instance "oracdb", status READY, has 1 handler(s) for this service...
Service "orapdb1" has 1 instance(s).
  Instance "oracdb", status READY, has 1 handler(s) for this service...
The command completed successfully

Lets try to connect to the database.

[oracle@ora12c trace]$ sqlplus jay/jay@orapdb1

SQL*Plus: Release 12.1.0.2.0 Production on Wed Feb 22 10:47:14 2017

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

Last Successful login time: Wed Feb 22 2017 10:25:31 +05:45

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

SQL>

Now lets check which IP address was chosen to server the request.

Go to $ORACLE_BASE/diag/tnslsnr/DB_NAME/listener1/trace.

[oracle@ora12c]$ tail -f listener.log
22-FEB-2017 10:47:14 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=orapdb1)(CID=(PROGRAM=sqlplus)(HOST=ora12c.dba.com)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.30.144)(PORT=21140)) * establish * orapdb1 * 0

We can see that 192.168.30.144 was used to serve the request.

Let's log out and try again to connect to the database.

[oracle@ora12c trace]$ sqlplus jay/jay@orapdb1

SQL*Plus: Release 12.1.0.2.0 Production on Wed Feb 22 10:47:57 2017

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

Last Successful login time: Wed Feb 22 2017 10:47:14 +05:45

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

Listener.log

[oracle@ora12c]$ tail -f listener.log
22-FEB-2017 10:47:57 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=orapdb1)(CID=(PROGRAM=sqlplus)(HOST=ora12c.dba.com)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.30.133)(PORT=37185)) * establish * orapdb1 * 0

Now we can see 192.168.30.133 is used.

Note: As @Colin 'T Hart said. You can use same port number for both IPs.


Having issue with tr_MStran_droptable I'm being prohibited from dropping any table from a db because of this trigger. I can't figure out why it is applying. The table(s) in question are not part of cdc or replication (neither has EVER been enabled on this instance, much less this db)

Nor are the tables in question showing up in...

OBJECT_ID('dbo.sysarticles') IS NULL OR OBJECT_ID('dbo.syspublications') IS NULL OR OBJECT_ID('dbo.sysextendedarticlesview')

any of those tables (from the trigger)

Google is no help, has anyone ran in to this before?

edit - entire trigger text

/****** Object:  DdlTrigger [tr_MStran_droptable]    Script Date: 12/16/2016 12:32:18 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER [tr_MStran_droptable] ON DATABASE FOR DROP_TABLE AS

                            SET ANSI_NULLS ON
                            SET ANSI_PADDING ON
                            SET ANSI_WARNINGS ON
                            SET ARITHABORT ON
                            SET CONCAT_NULL_YIELDS_NULL ON
                            SET NUMERIC_ROUNDABORT OFF
                            SET QUOTED_IDENTIFIER ON

                            DECLARE @EventData XML
                            SET @EventData=EVENTDATA()

                            IF OBJECT_ID('dbo.sysarticles') IS NULL OR OBJECT_ID('dbo.syspublications') IS NULL OR OBJECT_ID('dbo.sysextendedarticlesview') IS NULL OR EXISTS(
                               SELECT 1
                                 FROM @EventData.nodes('/EVENT_INSTANCE') AS R(event_instance)
                                WHERE LOWER(event_instance.value('SchemaName[1]', 'sysname')) IN (N'sys', N'cdc')
                                   OR LOWER(event_instance.value('ObjectName[1]', 'sysname')) IN (N'sysextendedarticlesview', N'sysarticles', N'syspublications')
                                   OR event_instance.value('ObjectName[1]', 'sysname') LIKE N'#%'
                            ) RETURN

                            EXEC sys.sp_MStran_ddlrepl @EventData, 5
    GO

    ENABLE TRIGGER [tr_MStran_droptable] ON DATABASE
    GO

I had this problem after upgrading to SQL Server 2016, and removing all replication was not an option.

For me, the solution was adding SP1 to my SQL Server 2016 instances.

The error had come when the trigger called sp_MStran_ddlrepl (in its last step, above). That stored procedure wasn't expecting "5" as the second parameter on a couple of upgraded servers. Running SP1 altered that stored procedure making "5" an acceptable parameter.


Challenging MySQL query, compare two records to find if they match or not I have a large (88K rows) table that holds data in pairs.

For a specific entity, there are 2 records, with UID's and then a load of additional data.

I need to take each pair (pairs can be found by matching another ID number) and compare the additional data.

So, I'd find a pair, compare the data and if either of the data values is the same (or different) flag that pair (and allow me to potentially run an update on one of the rows)

I can script this out n my PHP application if it requires multiple queries, but if this can be done just in SQL, that'd be great.

Any updates to a specific row would just be change a certain value to 0.

Table:

CREATE TABLE `ad_transitions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `asset_id` int(11) NOT NULL,
  `left` varchar(50) DEFAULT '0',
  `top` varchar(50) DEFAULT '0',
  `width` varchar(50) DEFAULT '0',
  `height` varchar(50) DEFAULT '0',
  `boundingWidth` varchar(50) DEFAULT NULL,
  `boundingHeight` varchar(50) DEFAULT NULL,
  `fill` varchar(50) DEFAULT '0',
  `shadow` varchar(128) DEFAULT NULL,
  `filter` varchar(512) DEFAULT NULL,
  `overlayFill` varchar(50) DEFAULT NULL,
  `opacity` varchar(50) DEFAULT '1',
  `scaleX` varchar(50) DEFAULT '1',
  `scaleY` varchar(50) DEFAULT '1',
  `angle` varchar(50) DEFAULT '0',
  `wait` int(11) DEFAULT '0',
  `duration` int(11) DEFAULT '0',
  `easing` varchar(50) DEFAULT 'Linear.easeNone',
  `originX` varchar(128) DEFAULT 'center',
  `originY` varchar(128) DEFAULT 'center',
  PRIMARY KEY (`id`)
)

Data:

INSERT INTO `database`.`ad_transitions` (`id`, `asset_id`, `left`, `top`, `width`, `height`, `boundingWidth`, `boundingHeight`, `fill`, `shadow`, `filter`, `overlayFill`, `opacity`, `scaleX`, `scaleY`, `angle`, `wait`, `duration`, `easing`, `originX`, `originY`) VALUES ('497', '161', '0', '0', '1280', '720', NULL, NULL, '#000000', NULL, NULL, NULL, '0', '1', '1', '0', '0', '0', 'Linear.easeNone', 'left', 'top');
INSERT INTO `database`.`ad_transitions` (`id`, `asset_id`, `left`, `top`, `width`, `height`, `boundingWidth`, `boundingHeight`, `fill`, `shadow`, `filter`, `overlayFill`, `opacity`, `scaleX`, `scaleY`, `angle`, `wait`, `duration`, `easing`, `originX`, `originY`) VALUES ('498', '161', '0', '0', '1280', '720', NULL, NULL, '#000000', NULL, NULL, NULL, '1', '1', '1', '0', '0', '5000', 'Linear.easeNone', 'left', 'top');
INSERT INTO `database`.`ad_transitions` (`id`, `asset_id`, `left`, `top`, `width`, `height`, `boundingWidth`, `boundingHeight`, `fill`, `shadow`, `filter`, `overlayFill`, `opacity`, `scaleX`, `scaleY`, `angle`, `wait`, `duration`, `easing`, `originX`, `originY`) VALUES ('499', '162', '269.16666666667', '-104', '986', '104', NULL, NULL, '#000000', NULL, NULL, NULL, '1', '1', '1', '0', '0', '0', 'Linear.easeNone', 'left', 'top');
INSERT INTO `database`.`ad_transitions` (`id`, `asset_id`, `left`, `top`, `width`, `height`, `boundingWidth`, `boundingHeight`, `fill`, `shadow`, `filter`, `overlayFill`, `opacity`, `scaleX`, `scaleY`, `angle`, `wait`, `duration`, `easing`, `originX`, `originY`) VALUES ('500', '162', '269.16666666667', '24', '986', '104', NULL, NULL, '#000000', NULL, NULL, NULL, '1', '1', '1', '0', '5000', '1000', 'Bounce.easeOut', 'left', 'top');

You can do this in SQL and make it very efficient at the same time. Essentially what we'll do is join the table to itself on your two Ids. Once you have a full set of all matching IDs you can compare the additional field values. To do this efficiently you can build them out into a single string, and hash it, then compare the hashes. It is exceptionally efficient and effective. Abbreviated example below.

SELECT
     id
    ,asset_id
FROM
(
 SELECT
     id
    ,asset_id
    ,SHA1(left + '|' + top) As DataCompare
    FROM ad_transitions
) As FirstSet
JOIN
(
 SELECT
     id
    ,asset_id
    ,SHA1(left + '|' + top) As DataCompare
    FROM ad_transitions
) As SecondSet
    ON FirstSet.id = SecondSet.id
    AND FirstSet.asset_id = SecondSet.asset_id
WHERE FirstSet.DataCompare = SecondSet.DataCompare

The pipe is added to the end of each field string (convert where necessary) to prevent issues that could result from odd but always possible data patterns such as the following:

Name     RandomText
Joh      Normand
John     Ormand

If just these two items are hashed, as is, they will be identical, despite they clear fact they are very different. The addition of a pipe after the field fixes johnnormand to joh|normand and john|ormand ensuring a unique hash and thus no accidental duplicate detection.


Query to know who paid more than 10k I have a table with payment informations, like this:

ID COSTUMER_ID DATEPAID     AMOUNT
1    1        2012-01-01      50
2    1        2012-02-01      100
3    1        2012-03-01      70
4    2        2012-03-01     6000
4    2        2012-09-01     3000
4    2        2014-04-01     8000
5    3        2013-03-01     9000
6    3        2013-08-01     3000

etc.
I need to know wich costumers paid more than 10k in a 12 months period. (in the exemple, only costumer 3 fits). Don't mind when, the only criteria is SUM(AMOUNT) > 10000 in any 12 consecutive months combination.

But i'm stuck on it. Any ideas?
I'm using SQL Server 2012.

One way to do this is do a select back to the table and sum the rows before the row, and then use that as an inner query to be able to use the sum in the where clause, something like this:

select *
from (
  select
    P1.ID,
    P1.CUSTOMER_ID,
    P1.DATEPAID,
    (select sum(P2.AMOUNT)
      from PAYMENT P2
      where P1.CUSTOMER_ID = P2.CUSTOMER_ID and
      P2.DATEPAID <= P1.DATEPAID and
      P2.DATEPAID >= dateadd(month, -12, P1.DATEPAID)) as PAID12M
  from
    PAYMENT P1
) X
where PAID12M > 10000

Example in SQL Fiddle.

This will return all the rows where the criteria matches, if you need only customer_id or first row or something like that, you'll have to make the outer query a little bit more complex.


ADD and DROP triggers frequently on a database I have a table from which I am deleting the data. Whenever a row is deleted, the row matching the data from other tables must also be deleted. So I am planning to achieve the same using triggers. The trigger will be only on DELETE condition.

The application from which I am achieving the same runs 4-5 times a day. So, at the start of the application, I am adding the triggers and on exit of application, I am dropping the triggers (because triggers slow down the database performance).

This too much frequent adding and dropping triggers is OK for Postgres or is there any better idea to achieve the same?

There are other applications as well which keep on running and they are using the database. Other applications do only INSERT and UPDATE operations. Only my application does the deletion part. It is a utility for daily cleanups.

You may use

ALTER TABLE tblname ENABLE\DISABLE TRIGGER


sqlplus works specifying the parameters but fails when using tnsnames.ora I've been turning google upside down and couldn't find an answer. I can connect to my oracle instance like this:

sqlplus 'myuser@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=msoracle.cnujcn8mkxea.us-east-1.rds.amazonaws.com)(PORT=1521))(CONNECT_DATA=(SID=ORCL)))'

But I can't make it work if I create a TNSNAMES.ORA at /usr/lib/oracle/12.1/client64/network/admin

ORCL=
(DESCRIPTION =
  (ADDRESS_LIST =
    (ADDRESS=(PROTOCOL=TCP)(HOST=msoracle.cnujcn8mkxea.us-east-1.rds.amazonaws.com)(PORT=1521))
  )
  (CONNECT_DATA=
  (SERVICE_NAME=ORCL)  -> I tried SID as well. No luck.
  )
)

I tried to

export ORACLE_SID=ORCL
export PATH=$PATH:$HOME/bin:/usr/lib/oracle/12.1/client64/bin
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/usr/lib/oracle/12.1/client64/lib
export ORACLE_HOME=/usr/lib/oracle/12.1/client64
export TNS_ADMIN=$ORACLE_HOME/network/admin

And nothing. The following message always comes up:

ERROR:
ORA-12162: TNS:net service name is incorrectly specified

EDIT:
This is the output of the tests in the comments:

[franklin@ip-10-148-17-253 client64]$ sqlplus myuser@ORCL

SQL*Plus: Release 12.1.0.2.0 Production on Fri Aug 5 10:13:39 2016

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

Enter password:
ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified

Enter user-name: myuser
Enter password:
ERROR:
ORA-12162: TNS:net service name is incorrectly specified

Enter user-name: ^C

I could fix the problem. I changed my TNS_NAMES.ORA removing ADDRESS_LIST and leaving like this:

ORCL=
(DESCRIPTION =
  (ADDRESS=(PROTOCOL=TCP)(HOST=msoracle.ccccc.us-east-1.rds.amazonaws.com)(PORT=1521))
  (CONNECT_DATA=
  (SID=ORCL)
  )
)

After I created a new file sqlnet.ora and added this:

NAMES.DIRECTORY_PATH = (TNSNAMES)

I also (just in case), renamed both files to lowercase:

[franklin@ip-10-148-17-253 admin]$ ls -ltr
total 8
-rw-r--r-- 1 root root 151 Aug  5 13:08 tnsnames.ora
-rw-r--r-- 1 root root  34 Aug  5 13:08 sqlnet.ora

So now I can connect just doing this:

sqlplus myuser/mypassword@ORCL


How to update array fields in a table in Postgresql? Say I have a table defined as following:

CREATE TABLE my_tbl (
    id  bigint,
    a1  bigint[],
    a2  bigint[]
);

I would like to create a stored procedure to append values to a1 and a2. Is this the right way to proceed or is there a simpler way?

CREATE OR REPLACE FUNCTION append(
        iid  bigint,
        next_a1  bigint,
        next_a2  bigint)
DECLARE
    r       "my_tbl";
    tmp_a1  bigint[];
    tmp_a2  bigint[];
BEGIN
    FOR r IN SELECT * FROM "my_tbl"
              WHERE r."id" = iid
                FOR UPDATE
        LOOP
            tmp_a1 := r.a1 || next_a1;
            tmp_a2 := r.a2 || next_a2;
            UPDATE my_tbl SET ( "a1", "a2" ) = ( tmp_a1, tmp_a2 )
             WHERE "id" = iid;
        END LOOP;
END; $$
LANGUAGE plpgsql;

I am on Postgresql 9.2.

You can simply do

UPDATE my_tbl
   SET (a1, a2) = (a1 || 123465, a2 || 132456789)
 WHERE id = 321;

To obtain a function, you simply have to replace the integer constants with the parameters.  You can safely do it in a query language (aka SQL) function, no need for pl/pgsql at all.


Can I make postgres more verbosely tell me what it's doing? I'm trying to use ALTER TABLE on a live db (not in production, just poking around at a dev box and trying to learn some things)

Here is the command I'm trying:

psql -h $IP -p 5432 -U $DBUSER -d $DBNAME \
    -c "alter table request alter column sources set data type text;"

And what happens is... nothing. postgres just sits there silently for many, many minutes, until i give up and cancel it:

^CCancel request sent
ERROR:  canceling statement due to user request

Some reading I've done suggested that this should be fast because it's not adding any onerous constraints like UNIQUE or foreign keys or anything.

There are less than 100 rows in the table so even if this was a heavy command to run, it should still finish relatively quickly. The existing type is varchar(1000), is it just that it's difficult and slow for pg to convert from varchar to text? I would think that would be a trivial conversion, essentially just taking off the 1000 char limit.

Is it possible that the table is somehow locked and my command is just paused, waiting for something else to finish? How would I check that? If so, how would I say "no pg, this one is the important one, lock out everything else".

Generally it'd just be nice if I could get pg to print out some debugging to say whether or not the long pause is due to heavy processing or just waiting around for a lock to free.

There are 29 rows in pg_locks and some of them do say ExclusiveLock but beyond that I'm not really sure how to interpret this information.

Postgresql version is 9.3.9-0ubuntu0.14.04 (that is, the latest version available for Ubuntu Trusty).

Here's the block report: http://paste.ubuntu.com/12243226/

Potentially relevant: my db consumer is flask/sqlalchemy, perhaps it is the one defining the select statements as blocking the table. The fact that 9 statements are blocking and they all say "where request_id = 1" is quite strange because request_id 1 was deleted some time ago. Is this some kind of dangling ghost query? How can I clear this out?

Here's pg_stat_activity: http://paste.ubuntu.com/12243295/

The idle in transaction state shown in the pg_stat_activity output generally means that there's something that starts a transaction with BEGIN, does some work, and then doesn't issue the COMMIT so that the transaction is finished. This has the effect of leaving the ACCESS SHARE locks acquired by the SELECT statements hanging around, which block the ALTER TABLE statement you're trying to run from acquiring the ACCESS EXCLUSIVE lock it needs to change the type of a column for every row in the table.

They could only be considered "ghost queries", in the sense that they are hanging around waiting for an answer of COMMIT or ROLLBACK that will probably never come. But they are problematic, nonetheless as you figured out.

There might be a bug somewhere in your application that has left all these connections open and waiting for a commit. I'd definitely check that out, and see if perhaps a code block doesn't have a terminating COMMIT.

More commonly (in my experience, YMMV of course), people start database sessions with a BEGIN, do some work, and then go to lunch and never close their session.

It's best to fix the problem at the source, but if you absolutely, positively need to do something right now, pg_cancel_backend and pg_terminate_backend are not bad ways to go.

Commenting on your answer, I'd specifically select the state of idle in transaction, rather than LIKE '%idle%' because those are the ones causing your problems. The idle state backends are just waiting around patiently for new client commands, and had last ran a ROLLBACK command.

You can look at the Explicit Locking documentation for more information about what blocks what.

The description of idle-in-transaction is in the Unix Monitoring Tools section for reference.

pg_cancel_backend and pg_stat_activity issue SIGINT and SIGTERM signals, respectively, and the effects of those signals (and warnings about other signals) are documented in the Server Shutdown section.

Hope that helps explain what you saw and sheds some light on your mystery. =)


Force number of entries My title probably isn't the best, but essentially I have a table that stores a host, vulnerability finding reference, and status.  I am trying to query on those, but where I don't have an entry I want to force it to show me a defaulted value for the status.  Here's an example...

SELECT f.host_id, f.`status` FROM findings f WHERE f.finding_id = 1 AND f.host_id IN (1,2,3)

So if there is an entry for 1 & 2, but not 3, I will want an entry for 3, but with a defaulted status value of 'No Data'.

TIA

EDIT:
I am trying to avoid doing 3 queries because I may have dozens of hosts in that list and that would be a major performance hit!

You can do it with a (derived) table that holds these 3 values and then LEFT joining it to the findings:

SELECT
    d.host_id,
    CASE WHEN f.host_id IS NOT NULL THEN f.status ELSE 'No Data' END AS status
FROM
    ( SELECT 1 AS host_id UNION ALL
      SELECT 2 UNION ALL
      SELECT 3
    ) AS d
  LEFT JOIN
    findings AS f
      ON  f.finding_id = 1
      AND f.host_id = d.host_id ;

If the findings.status column is not nullable, you can replace the CASE ... END with the simpler:

    COALESCE(f.status, 'No data') AS status

If you have a hosts table (and a large number of IDs to check), the derived table can also be simplified:

...
FROM
    ( SELECT host_id
      FROM hosts
      WHERE host_id IN (1,2,3)
    ) AS d
  LEFT JOIN
...


myql performance : extremely slow inserts on a faster box tldr; When migrating a stack to its pre-prod environment, i am confronted with a setup script taking 50x the amount of time to execute compared to my dev box. error logs are dead silent !

dev :
    osx, mysql 5.7, SSD, plenty of memory

pre-prod :
    debian 8.6, mysql 5.6, faster SSD, faster/more memory, faster core.

the PHP script:

    private function _addCityFile($fn)
    {
        $this->log->info(__METHOD__ . " start.");

        $this->log->debug("Adding city file [$fn]");

        try {
            /** @var \PDO $pdo */
            $pdo = $this->pdo;
            $this->pdo->beginTransaction();
            // InnoDB table
            $sql  = "INSERT INTO City SET country=:co,city=:ci,state=:st,zip=:zip";
            $stmt = $pdo->prepare($sql);

            $handle             = fopen($fn , "r");
            $csv_time           = 0.0;
            $pdo_time           = 0.0;
            $index              = 0;
            $intermediate_count = 100;           // echo every 100 records
            $total_count        = 0;

            if ($handle) {
                fgets($handle);                 // skip first line
                while (true) {
                    $index++;
                    $total_count++;
                    $start_csv_once = microtime(true);
                    $ary            = fgetcsv($handle);
                    $end_csv_once   = microtime(true);
                    $csv_time_once  = $end_csv_once - $start_csv_once;
                    $csv_time       = $csv_time + $csv_time_once;

                    if ($ary) {
                        $country = $ary[0];
                        $state   = $ary[1];
                        $city    = $ary[2];
                        if (!$city || $city === '') continue;
                        $zip = strtoupper($ary[3]);
                        if (!$zip || $zip === '') continue;
                        $stmt->bindValue('co' , $country , \PDO::PARAM_STR);
                        $stmt->bindValue('ci' , $city , \PDO::PARAM_STR);
                        $stmt->bindValue('st' , $state , \PDO::PARAM_STR);
                        $stmt->bindValue('zip' , $zip , \PDO::PARAM_STR);
                        $start_pdo_once = microtime(true);
                        $stmt->execute();
                        $end_pdo_once  = microtime(true);
                        $pdo_time_once = $end_pdo_once - $start_pdo_once;
                        $pdo_time += $pdo_time_once;
                        $ct = $csv_time * 1000;
                        $pt = $pdo_time * 1000;
                        if ($index >= $intermediate_count) {
                            $this->log->debug("After $total_count : csv [{$ct} ms.], pdo [{$pt}]");
                            $index = 0;
//                          if ($total_count > 1000) break;
                        }

                    } else {
                        break;
                    }
                }
                $start_commit = microtime(true);
                $this->pdo->commit();
                $end_commit = microtime(true);
                $delayInMs  = ($end_commit - $start_commit) * 1000;
                $this->log->debug("Commit took [$delayInMs] ms.");
                fclose($handle);
            }
        } catch (\Exception $e) {
            $this->pdo->rollBack();
            $this->log->error(prettyStack($e , "While processing [$fn]"));
        }
        $this->log->info(__METHOD__ . " end.");

    }

the puzzle:

the script takes about 3.5 seconds per 1000 records on pre-prod, dev takes .07 seconds/1000 records on dev.

Following some guidance i found on SO, i added the beginTransaction / commit.  With that, i get .04 seconds (!!!) /1000 records on pre-prod vs .06 on dev.

the question

There must be some obvious mysql setting that explains this rather large difference. I compared both configurations, but cant find anything fundamentally different except 5.7/5.6. Any suggestions in what direction to look?

Do not insert one row at a time; batch them.  That is, have 1000 rows being inserted by each INSERT statement.

That will help both servers

To find out why there is a difference, ...  Compare the CREATE TABLE.  Compare SHOW VARIABLES LIKE 'innodb%';.  If those don't point out anything, compare all the VARIABLES.  The likely suspect is innodb_flush_log_at_trx_commit.  Note:  Don't simply look at my.cnf; some defaults are different between versions.


Can we generate SQL Server MDS Model from an existing database schema? I have googled this quite a bit and am surprised to not find an answer.

I'm considering using SQL Server MDS to manage our canonical schema, but I certainly don't want to key in my entire database schema to create the Model.

Does SQL Server MDS support creating a Model by reverse engineering a database?

Does SQL Server MDS support creating a Model by reverse engineering a database?

I wrote a command-line utility to do this, called MdsModelImport.  It's intended to be a one-time import, and simply save you from having to type all the attributes.


is it possible to show the dump history of MySQL 5.7 Now I am using Canal to sync MySQL 5.7 -> MySQL 5.7 in different datacenter. The Canal would send a dump request to MySQL pretent as a MySQL slave node, Now I am not sure the dump is triggered or not, is it possible to show the master dump histroy?

No, there is nothing in MySQL that stores mysqldump story automatically.
From the MySQL point of view, mysqldump is just another connection.


How to use timestamp column for filtering records in where clause in mysql Query :
SELECT event_time, user_host, argument
FROM mysql.general_log
WHERE event_time > ('2016-08-04 10:50:27')
AND user_host LIKE 'vareity_1_1%'
OR user_host LIKE 'db_tvm%'

The query is working but am records which doesn't satisfy the where clause.
Here in the image you can see the results got

AND has higher precedence than OR. Your query, without parentheses, is equivalent to this:

SELECT event_time, user_host, argument
FROM mysql.general_log
WHERE (event_time > '2016-08-04 10:50:27' AND user_host LIKE 'vareity_1_1%')
   OR user_host LIKE 'db_tvm%' ;

Try this instead:

SELECT event_time, user_host, argument
FROM mysql.general_log
WHERE event_time > '2016-08-04 10:50:27'
 AND (user_host LIKE 'vareity_1_1%' OR user_host LIKE 'db_tvm%') ;


Problem with writing a trigger I want to write a trigger on one of the tables in my database so that no one can update the column listprice (the price) by more than 10 percent.

I'm totally confused. I can't write the trigger.

The column would be like this:

listprice
1000
2000
3000

and the calculation would be listprice=listprice*1.1

As long as we don't have sample data, I built simple table with three columns.

Sample solution to achive your problem:

CREATE TABLE TenPercent (Id INT IDENTITY(1,1), Name VARCHAR(256), Listprice NUMERIC(10,2));
GO

INSERT INTO TenPercent (Name,Listprice)
SELECT * FROM (VALUES ('Name',100.00)) T(Name,Listprice);
GO

CREATE TRIGGER TenPercent_Guard ON TenPercent
FOR UPDATE
AS
BEGIN
IF (UPDATE(ListPrice) AND
    EXISTS (SELECT * FROM TenPercent tp
        JOIN DELETED d ON tp.Id=d.Id AND tp.Listprice>d.Listprice*1.1))
    THROW 50000,'You can''t UPDATE Listprice more then 10 percent.',1;
END
GO

UPDATE TenPercent
SET Listprice=150;
GO

SELECT * FROM TenPercent;
GO

UPDATE TenPercent
SET Listprice=110;
GO

SELECT * FROM TenPercent;
GO

I used a JOIN in the trigger body to achive a situation when more then one row will be updated.

Obviously you should fit this solution to your table structure.


Check for table corruption I monitor MySQL databases by querying various things remotely. I recently came across an issue where by a table had become corrupted but I did not know about it until the daily mysqldump failed. Is there a way I can check for corruption (without running a CHECK TABLE on every table)? I thought I had it figured when I noticed information_schema.tables has a table_comment column which says this when it's corrupt:

TABLE_COMMENT: Table './demo/test' is marked as crashed and should be repaired

Turns out, when I try to search on that column, i.e. WHERE TABLE_COMMENT LIKE '%marked as crashed%', nothing is returned!

I need to be able to query this from a stored procedure, any suggestions?

SHOW TABLE STATUS;
and then run
show warnings;

will show you what all tables are corrupted.


How can I merge duplicate rows in a single table? I have a table with duplicate barcodes (barcode) and carts (cart) and (Quantity_counted_IN) and (Quantity_Counted_out).

I want to merge all duplicate records that have the same barcode and cart number to show only one barcode for that cart number but sum(Quantity_counted_IN) and sum(Quantity_Counted_out) at the same time.

Example:

           cart   barcode         Quantity_Counted_In  Quantity_Counted_Out
 Row 1        1   610708542209                     -7                    20
 Row 2        1   610708542209                     -4                    16
 Row 3        2   610708542209                     -3                    17

I want it to read:

 Row 1        1   610708542209                    -11                    36
 Row 2        2   610708542209                     -3                    17

How can I do this?

This is what's known as an aggregate query,

SELECT cart, barcode,
       SUM(counted_in) AS counted_in,
       SUM(counted_out) AS counted_out
FROM tbl
GROUP BY cart, barcode;

Using GROUP BY like this returns one record for every unique occurrence of (cart, barcode) and the totals of counted_in and counted_out for each.


Trigger for updating a count of rows from a table into another table I have these two tables:

mysql> select * from orders;
+-------------+---------+
| Customer_ID | Item    |
+-------------+---------+
| A01         | CPU     |
| A01         | Monitor |
| A02         | Monitor |
| A03         | UPS     |
| A02         | UPS     |
| A03         | CPU     |
+-------------+---------+
6 rows in set (0.00 sec)

mysql> select * from ordered_items;
+---------+-----+
| Item    | Qty |
+---------+-----+
| CPU     |   2 |
| Monitor |   2 |
| UPS     |   2 |
+---------+-----+
3 rows in set (0.00 sec)

I need to count each items in the orders table and update the Qty of the ordered_items table whenever the orders table is updated.

Is it possible to do with a trigger?

Yes, you could do this via a Trigger. But you would need 3 triggers: 1 on insert, 1 on delete, 1 on update (which would do nothing, unless you modify the Item column). This would be harder to mantain, and would slow down operations in orders table.

Another option is using a view:

CREATE OR REPLACE VIEW `ordered_items`
    AS
        SELECT `Item`, COUNT(*) AS `Qty`
            FROM `orders`
            GROUP BY `Item`;

The drawback is that reading ordered_items is slower, because the GROUP BY on orders is executed each time. If this is a problem, you could periodically copy data from ordered_items view into a physical table. This could be done nightly via an Event, if you have MANY data.


Understanding disk requirement to make fail over cluster Please take a look at the following screenshot.

I am trying to make SQL Server 2012 Fail Over Cluster using two nodes. As you know we need to have a shared SAN which will be used by both nodes to store data but I have some confusion regarding it.

I was under the impression that we need one drive for e.g. Z for "Temp DB directory" which will be shared by both nodes to store data.

But someone told me that these will be two drives actually for each node and for these nodes it will appear as one. So I am not sure what is true. May be we are both saying same thing!?

That all depends on which configuration you're seeking. As @EricHiggins noted, a traditional cluster will have shared resources, including disk, network name(s) and IP(s), and the service itself. SQL Server is a cluster-aware application and will interact with the Windows cluster service. When a failure (or manual failover) is detected by the cluster service, the SQL Server service is stopped (taken offline) and the instance resources (name, IP, disk, and other cluster resources) are migrated/transferred to the another node in the cluster. When the resources are fully transferred, the SQL Server service is started and the instance is available.

With SQL Server 2012 Enterprise Edition, the concept of Availability Groups was introduced to the system. Availability Groups are a shared-nothing environment that uses failover clustering for network naming and listening, but uses mirroring to protect at the database level.

There are many more details to go into to draw out the distinction between (and the benefits of) each of these solutions. However, to directly answer your question, if you're going to cluster using failover clustering then the TempDB LUN would be shared. If you're going with Availability Groups, then you'll double your storage, but be able to protect at the database level (instead of the instance level).

I'd need more information about "appears as one" statement - to what, or whom, does it "appear as one?"