Postgres SUM over PARTITION BY "tolerate" null values I have a view which calculates rolling sum taking in value form previous row based on PARTITION BY clause, and I need to indicate one field in PARTITION BY clause can have nulls in them.
sum(s."QuantityChange") OVER (PARTITION BY s."LocationId", s."PartId", s."SupplierFacilityId" ORDER BY s."DueDate" ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS "RunningSum"

Sometimes SupplierFacilityId is null and that's fine, it should still be included in the sum() calculation.
Is this possible with Postgres?

Your image from the comment (which should be text in the question) shows that you run the query with this WHERE clause:
WHERE ... AND ("SupplierFacilityId" IS NULL OR "SupplierFacilityId" = 647)

So you can just drop "SupplierFacilityId" from PARTITION BY to achieve what you want: rows with "SupplierFacilityId" IS NULL are treated as equal peers of the partition:
sum(s."QuantityChange") OVER (PARTITION BY s."LocationId", s."PartId"
                              ORDER BY s."DueDate" ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS "RunningSum"

Also, since the default framing option is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, and assuming that (s."LocationId", s."PartId", s."DueDate") is defined unique (which makes RANGE and ROWS equivalent in this context), you can just drop the custom framing options:
sum(s."QuantityChange") OVER (PARTITION BY s."LocationId", s."PartId"
                              ORDER BY s."DueDate") AS "RunningSum"

Without said WHERE clause, your goal would be unachievable with a simple window function as you would have to use the same rows with NULL values in multiple partitions. You would have to multiply those rows first ...


How do you retrieve the identity value of a row inserted from the OUTPUT of an UPDATE statement? How do you retrieve the identity value for an inserted row when that row is inserted from the OUTPUT of an UPDATE statement? Neither @@IDENTITY nor SCOPE_IDENTITY() appears to be set properly.

Consider this code:

DECLARE @UpdateTable table (UpdateTableID int IDENTITY, UpdateTableValue int);
DECLARE @InsertTable table (InsertTableID int IDENTITY, UpdateTableValue1 int, UpdateTableValue2 int);
DECLARE @TestValue int = 5;
INSERT INTO @UpdateTable (UpdateTableValue) VALUES (1),(2),(3);
SELECT [@@IDENTITY] = @@IDENTITY, [SCOPE_IDENTITY()] = SCOPE_IDENTITY();

INSERT INTO @InsertTable (UpdateTableValue1, UpdateTableValue2)
SELECT
    UpdateTableValue1, UpdateTableValue2
FROM        (
                UPDATE      @UpdateTable
                SET         UpdateTableValue = UpdateTableValue + @TestValue
                OUTPUT      deleted.UpdateTableValue, inserted.UpdateTableValue
                WHERE       UpdateTableID = 2
            ) AS UpdateResults (UpdateTableValue1, UpdateTableValue2);

SELECT [@@IDENTITY] = @@IDENTITY, [SCOPE_IDENTITY()] = SCOPE_IDENTITY();

The last-inserted row has an identity value of 1, yet the @@IDENTITY and SCOPE_IDENTITY() functions are returning their original values from the original INSERT prior to the last statement executed.

@@VERSION:

  Microsoft SQL Azure (RTM) - 12.0.2000.8   May  2 2019 20:11:13
    Copyright (C) 2019 Microsoft Corporation

I believe that this is because @@IDENTITY and SCOPE_IDENTITY() do not work when used in combination with an UPDATE statement. While I understand that the natural response to this would be "Well why doesn't it return the ID of the outer INSERT?" the answer is: because of Scope.

The UPDATE statement, and the INSERT statement (using the results of the OUTPUT) are executed as one statement and are inherently within the same scope of one another. When the engine executes the query, it realises this and so it is unable to track the identity generated by the outer INSERT. You can prove this by running the following query which splits the UPDATE and the INSERT into two individual statements:

DECLARE @UpdateTable table
(
    UpdateTableID int IDENTITY,
    UpdateTableValue int
);
DECLARE @InsertTable table
(
    InsertTableID int IDENTITY,
    UpdateTableValue1 int,
    UpdateTableValue2 int
);

DECLARE @TestValue int = 5;

INSERT INTO @UpdateTable (UpdateTableValue)
VALUES (1),(2),(3);

SELECT [@@IDENTITY] = @@IDENTITY, [SCOPE_IDENTITY()] = SCOPE_IDENTITY();

DECLARE @tmp TABLE
(
    UpdateTableValue1 int,
    UpdateTableValue2 int
);

UPDATE      @UpdateTable
SET         UpdateTableValue = UpdateTableValue + @TestValue
OUTPUT      deleted.UpdateTableValue, inserted.UpdateTableValue
INTO        @tmp
WHERE       UpdateTableID = 2;

INSERT INTO @InsertTable (UpdateTableValue1, UpdateTableValue2)
SELECT
    UpdateTableValue1, UpdateTableValue2
FROM        @tmp;

SELECT [@@IDENTITY] = @@IDENTITY, [SCOPE_IDENTITY()] = SCOPE_IDENTITY();

Which produces the correct results:

If you need to get the identity from the table you have 3 options as far as I see:

Use the technique I use above to obtain the value.
Add an OUTPUT clause to the final INSERT to capture the generated ID.
Use the IDENT_CURRENT() function to obtain the current value.

If you are using temp tables (not global temp tables) then IDENT_CURRENT() would be perfectly fine as you can guarantee that it will only return the value generated within your session (as the objects are not available via other sessions). However, if the example you gave is just that then I would go with option 2 and use another OUTPUT clause to capture the identity value that is generated:

INSERT INTO @InsertTable (UpdateTableValue1, UpdateTableValue2)
OUTPUT inserted.InsertTableID
SELECT
    UpdateTableValue1, UpdateTableValue2
FROM
(
    UPDATE      @UpdateTable
    SET         UpdateTableValue = UpdateTableValue + @TestValue
    OUTPUT      deleted.UpdateTableValue, inserted.UpdateTableValue
    WHERE       UpdateTableID = 2
) AS UpdateResults (UpdateTableValue1, UpdateTableValue2);

This then outputs the correct value:


FCI with Alwayson Availability Group (Asymmetric Storage) I am trying to setup a demo environment where I have a windows cluster with 3 nodes.
Node 1 will be standalone sql server and will be the primary replica.
Node 2 and 3 will be in a Failover cluster instance(sqlclu1) and that will be the async secondary replica.

I have installed sql server as a clustered instance (Node 2 and 3) and
has a shared storage(E: drive) which is presented only to node 2 and 3 and which contains sql data files(by which I mean, the possible owner for this disk is only node 2 and 3 and also I have not connected the disk to Node 1).

I am stuck at the next step where I am trying to install a standalone sql server on node 1 for being the primary replica.  The issue is when I try to add a disk with the same name (E:) it is not letting me do that.  E:drive is just not available in the list when I create a new volume.

I am guessing I have stuffed up something and am wondering how Asymmetric Storage needs to be configured so that I can create disk with the same drive letter.

As per https://msdn.microsoft.com/en-us/library/jj215886.aspx

  Asymmetric Storage Two FCIs, one at each site on a single multi-site
  WSFC, introduce considerations around how shared storage is handled.
  Each FCI has its own shared storage. The nodes at the primary site
  share storage among themselves to form a shared-storage FCI, and the
  nodes at the DR site share storage among themselves to form another
  shared-storage FCI. The storage on the primary site is not visible to
  the nodes on the disaster recovery site and vice versa. This
  arrangement of storage, where a cluster disk is shared between a
  subset of nodes within a WSFC, is referred to as asymmetric storage.
  Before the asymmetric storage capability, shared storage needed to be
  visible to all the nodes in the WSFC (symmetric storage).  This
  Windows Server enhancement is the key piece of functionality that
  enables the FCI + AG solution architecture discussed in this white
  paper. By enabling this functionality, you can combine the shared
  storage solution (FCI) with the non-shared storage solution
  (availability groups), in a single HA + DR solution. Consequently,
  this enhancement also enables you to use identical drive letters for
  shared disk resources across data centers.

Using sql server 2014 and windows 2012R2

Can you try adding it under another drive letter, and then changing the drive letter via Disk Management. It's what was suggested by Microsoft during the CTPs.


Does export in schema mode also includes tablespaces? I am executing an expdp command in schema mode. I'm just curious if it also exports tablespaces. I'm total newbie in oracle databases. What if I want to export schemas and tablespaces? What mode do I need to use?

Yes it does.
https://docs.oracle.com/cd/E11882_01/server.112/e22490/dp_export.htm#i1007519

Tablespace Mode
A tablespace export is specified using the TABLESPACES
parameter. In tablespace mode, only the tables contained in a
specified set of tablespaces are unloaded. If a table is unloaded,
then its dependent objects are also unloaded. Both object metadata and
data are unloaded. In tablespace mode, if any part of a table resides
in the specified set, then that table and all of its dependent objects
are exported.

e.g.
expdp hr DIRECTORY=pump DUMPFILE=tbs456.dmp TABLESPACES=tbs_4, tbs_5, tbs_6


Does MySQL do UPDATE in place on NOT NULL column? Assume I have a table like this in MySQL:

CREATE TABLE Foo (
Key INT NOT NULL
, MyCol INT NOT NULL
PRIMARY KEY (Key))

And I execute a statement like this on:

UPDATE Foo
SET MyCol = 13
WHERE Key = 42

Will MySQL do an INSERT followed by DELETE of this row behind the scenes (and thus, potentially cause a page split) or can the UPDATE happen in place on the row?

Under which conditions do each of these situations happen?

I can confirm that the comment by ypercube above is correct :)  Here are some additional details:

Updates occur in place, with the older version of the columns relocated to UNDO space.  The UNDO is used for providing MVCC, but also in the event of a rollback.  As InnoDB pages are initially written they are only 15/16ths filled, so there is some free space to reduce page splits.
Indexes contain all 'versions' of a column.  This is required to be able to perform index-only-scans while still offering MVCC.
Exception to this rule is overflow text/blob pages, which are always freshly written and not updated in place.


Neither DBCC FREEPROCCACHE nor DBCC FREESYSTEMCACHE('SQL Plans') do anything to free CACHESTORE_SQLCP memory CACHESTORE_SQLCP Sql Plans takes up > 38 GB after a few days.

We are already running with "optimize for ad hoc workloads" option on.  (Entity Framework and custom reporting creates a lot of ad hocs!)

  SQL Server 2016 SE 3.00.2164.0.v1 on AWS RDS with multi-AZ mirroring

When I run:

DBCC FREESYSTEMCACHE('SQL Plans');

or

DBCC FREEPROCCACHE

or

DBCC FREESYSTEMCACHE ('SQL Plans') WITH MARK_IN_USE_FOR_REMOVAL

or

DBCC FREESYSTEMCACHE ('ALL') WITH MARK_IN_USE_FOR_REMOVAL;

It doesn't seem to clear it:

SELECT TOP 1 type, name, pages_kb FROM sys.dm_os_memory_clerks ORDER BY pages_kb  desc

type                name        pages_kb
CACHESTORE_SQLCP    SQL Plans   38321048

I was running with Query Store enabled, but I disabled it to see if that was interfering with anything, it didn't seem to help, but I left it off.

What's really weird also is

SELECT COUNT(*) FROM sys.dm_exec_cached_plans

is 1-3 or so (it seems to show only ever show currently-running queries), even though all that memory is reserved, even before I attempted to clear anything.  What am I missing?

CACHESTORE_SQLCP is taking up more than 60% of all available memory, which is a concern because there are memory waits happening occasionally.  In addition, we had to kill a routine DBCC CHECKDB over the weekend that was lasting 4 hours because insufficient memory was stacking up waits (it completed instantly with no errors with PHYSICAL_ONLY on).

Is there any way to reclaim this memory (other than nightly reboots!?)?

Update from comments/answers

When I run

SELECT * FROM sys.fn_my_permissions(NULL,NULL)

I get

entity_name subentity_name  permission_name
server      CONNECT SQL
server      CREATE ANY DATABASE
server      ALTER ANY LOGIN
server      ALTER ANY LINKED SERVER
server      ALTER ANY CONNECTION
server      ALTER TRACE
server      VIEW ANY DATABASE
server      VIEW ANY DEFINITION
server      VIEW SERVER STATE
server      ALTER SERVER STATE
server      CREATE SERVER ROLE
server      ALTER ANY SERVER ROLE

Which includes the required ALTER SERVER STATE permission.

The output of DBCC FREEPROCCACHE is

  DBCC execution completed. If DBCC printed error messages, contact your
  system administrator.

Which is the standard message.  Other DBCC functions that RDS does not support give error messages about permissions.

(A day later and after running that again, SQL Plans is still 38,321,280 kb)

Update from comments/answers

SELECT pool_id, name, cache_memory_kb, compile_memory_kb FROM sys.dm_resource_governor_resource_pools

outputs:

pool_id name    cache_memory_kb  compile_memory_kb
1   internal    38368408         1168080

DBCC FREEPROCCACHE ('internal') doesn't do anything differently

Update

SQL Error Log logs the following each time:

2017-10-19 14:26:47.22 spid85 SQL Server has encountered 1 occurrence(s) of cachestore flush for the 'Object Plans' cachestore (part of plan cache) due to 'DBCC FREEPROCCACHE' or 'DBCC FREESYSTEMCACHE' operations.

2017-10-19 14:26:47.22 spid85 SQL Server has encountered 1 occurrence(s) of cachestore flush for the 'SQL Plans' cachestore (part of plan cache) due to 'DBCC FREEPROCCACHE' or 'DBCC FREESYSTEMCACHE' operations.

2017-10-19 14:26:47.22 spid85 SQL Server has encountered 1 occurrence(s) of cachestore flush for the 'Bound Trees' cachestore (part of plan cache) due to 'DBCC FREEPROCCACHE' or 'DBCC FREESYSTEMCACHE' operations.

Update

There's an engine version upgrade on RDS from 13.00.2164.0.v1 to 13.00.4422.0.v1 available.  Though it is set to auto-minor-version upgrade, it hasn't seemed to keep us updated to the latest.  Will reboot and install that this weekend to see if it helps.

I upgraded to 13.00.4422.0.v1 (SQL Server 2016 SP1) and restarted.

So far, I am able to clear SQL Plan memory using DBCC FREESYSTEMCACHE('SQL Plans'); command!

Time will tell to see if it uses the memory a little wiser now, but at least now I have a way to reset it if its gets too bloated again.


Sharing PostgreSQL database on one dual-boot machine without duplication (cross-platform) I have PostgreSQL 9.4 installed on a dual-boot machine (both on Win 7 and Ubuntu 14.10). The two systems share a drive (NTFS-formated) which includes a PSQL database directory which was created under Windows. Obviously the PSQL installations are system-specific, and they also happen to be on different partitions. The drive is automatically mounted under Linux and given full read/write/execute permissions.

I would like to know if it is possible to share the structure and data between the two systems without duplicating and/or having to copy everything each time I load a new OS. Effectively, I would like all the data to be available to Ubuntu when it is created in Windows, and vice versa.

Is this possible, and if so, how would one set this up from Linux assuming all the data was originally generated under Windows?

EDIT: I tried running:

sudo pg_dropcluster --stop 9.4 main

followed by

sudo pg_createcluster -d /db_from_windows_path 9.4 main

which appeared to work, but then when starting up the server again, I get:

sudo /etc/init.d/postgresql start
 * Starting PostgreSQL 9.4 database server
 * Error: could not exec /usr/lib/postgresql/9.4/bin/pg_ctl /usr/lib/postgresql/9.4/bin/pg_ctl start -D /db_from_windows_path -s -o  -c config_file="/etc/postgresql/9.4/main/postgresql.conf" :
 [fail]

It seems extremely cryptic, especially since /var/log/postgresql/postgresql-9.4-main.log is completely empty, and so is the rest of the /var/log/postgresql/ directory

EDIT 2: After some looking about, I noticed that the chown hadn't taken because NTFS systems can apparently only be set at mount time. After fixing the /etc/fstab to give ownership to postgres, I get a different error: The PostgreSQL server failed to start. Please check the log output. Which, again, resulted in no log, so I ran:

/usr/lib/postgresql/9.4//bin/postgres -d 5 -D /var/lib/postgresql/9.4/main -c config_file=/etc/postgresql/9.4/main/postgresql.conf

which gave this:

LOG:  invalid value for parameter "dynamic_shared_memory_type": "windows"
HINT:  Available values: posix, sysv, mmap, none.
2015-04-17 12:38:43 BST [3653-3] LOG:  invalid value for parameter "lc_messages": "English_United Kingdom.1252"
2015-04-17 12:38:43 BST [3653-4] LOG:  invalid value for parameter "lc_monetary": "English_United Kingdom.1252"
2015-04-17 12:38:43 BST [3653-5] LOG:  invalid value for parameter "lc_numeric": "English_United Kingdom.1252"
2015-04-17 12:38:43 BST [3653-6] LOG:  invalid value for parameter "lc_time": "English_United Kingdom.1252"
2015-04-17 12:38:43 BST [3653-7] FATAL:  configuration file "/etc/postgresql/9.4/main/postgresql.conf" contains errors
2015-04-17 12:38:43 BST [3653-8] DEBUG:  shmem_exit(1): 0 before_shmem_exit callbacks to make
2015-04-17 12:38:43 BST [3653-9] DEBUG:  shmem_exit(1): 0 on_shmem_exit callbacks to make
2015-04-17 12:38:43 BST [3653-10] DEBUG:  proc_exit(1): 0 callbacks to make
2015-04-17 12:38:43 BST [3653-11] DEBUG:  exit(1)

This seems like a different problem altogether, perhaps POSIX-related?

I would like to know if it is possible to share the structure and data between the two systems without duplicating and/or having to copy everything each time I load a new OS. Effectively, I would like all the data to be available to Ubuntu when it is created in Windows, and vice versa.

You can't. Just not supported, not possible. No can do. PostgreSQL does not attempt to make the data directory portable across architectures, operating systems, etc.

The only way you can do this is to run a Windows virtual machine inside Ubuntu when you want to access a Windows data directory, or a Linux virtual machine inside Windows when you want to access a Linux data directory.

You might want to run a VM image that's shared between both platforms, and have it contain the database - but be careful, Linux's NTFS driver isn't great for performance, and might not have all the wrinkles ironed out when it comes to concurrent I/O so I'd be a little leery running something like a VM image off it. FAT32 and exFAT are not crash-safe. So there aren't really any good choices for a file system both systems can share to run "real work" off.

So personally... I'd just have a small separate machine. Or I'd keep a dump of the DB and reload it when I rebooted.

(Additionally, if running PostgreSQL under Linux on Linux's NTFS file system driver works, that's a little surprising, and I certainly wouldn't rely on it for anything I cared about).


Postgresql connection refused I'm trying to remotely connect to my postgresql database. I have two virtual machines set up both running ubuntu 14.04. I am trying to connect to the second vm using the first vm using

psql -h 10.0.1.23 -U postgres -d postgres

But I receive the error:

Could not connect to server: Connection refused
    Is the server running on host "10.0.1.23" and accepting
    TCP/IP connections on port 5432?

I have changed the pg_hba.conf and added

host all all 10.0.1.64/24 md5
host all all * md5
host all all 0.0.0.0/0 md5

And changed the postgresql.conf listen_address=" * "

In an attempt to allow all incoming connections. I have also tried to change the firewall settings, but I am unsure of whether or not the ports are properly listening for the connection.

Apparently the problem was in the postgresql.conf the listen_addresses line had the # still in front of it making it a comment. Removing it solved my problems.


converting mysql columns to bits in a single column I have a mySQL table with multiple columns (tinyint) with 0 or 1 values

I want to convert all these values into bits in a single integer column
So a column named column1 will go to MSB another column will set 2nd bit etc...

How do I do it?

Use logical shifts << plus a bitwise or | operation.

For example:

SELECT (col1<<16) | (col2<<15) | (col3<<14) | (col4<<13) ...

MySQL docs are here.


Implementation of SSL ON SQL SERVER DEFAULT INSTANCE We've purchased an SSL certificate for our Server with a specific CN and implemented the certificate on IIS.

We imported the same certificate on the SQL Server machine.

However, when trying to configure the instance from configuration manager there is certificate showing up in certificate tab.

With reference to link for deploying and troubleshooting certificate: http://support.microsoft.com/kb/316898/

Apparently the certificate was not issued to the machine FQDN/hostname as there is no domain for the SQL Server machine or the Web Server machine.

I tried creating an OpenSSL certificate on a machine using its hostname but when trying SCM to configure the instance after certification deployment there's no certificate in the tab.

Questions:

Does an OpenSSL certificate or a purchased certificate use port 443?  Reference this link: https://stackoverflow.com/questions/9342769/sql-server-cannot-find-certificate.
If yes, what is the GUID for my SQL Server instance or where can I get this info while running "netsh".
Above link also states.

  “The last step was making sure the account running SQL Server had permission to read the certificate. I went into the certificate snap-in and then went to properties under the certificate, then on the Security tab I gave the Network Services account read permission on the certificate. (NOTE: I did not find any tutorials online reference this step. I was just some dumb luck that I found this.”

I couldn't find any security tab; is there something other than the MMC wizard to see this property of certificate?

Server details:

Window Server 2008 R2
SQL Server 2008 Web Edition

What am I missing?

Ok this is what i've referenced thesqldude.com/tag/makecert for creating self signed certificate. JIST :

1) MAKECERT tool used for preparing certificate with command mentioned in link above –

FYR :
     makecert -r -pe -n "CN=cs020.classic.classicinformatics.net" -b 01/01/2000 -e 01/01/2036 -eku 1.3.6.1.5.5.7.3.1 -ss my -sr localMachine -sky exchange -sp "Microsoft RSA SChannel Cryptographic Provider" -sy 12

Where "cs020.classic.classicinformatics.net" is my system FQDN.

2) Then open mmc and added console to view certificate, And it was automatically restored in the certificate store under "personal"
3)Then opened SCM and view properties of SQL SERVER protocal.

FYI: My system hostname was "cs020" and its in domain "classic.classicinformatics.com".
     If it was in workgroup then creating certifcate with "CN=cs020" would have worked,
     but i check my fullcomputer name and it was stating something else
     i.e. "cs020.classic.classicinformatics.net" , so there was it, i've used the above command and got my results.


What's an example of an exploitation of the security from enabling Ole Automation Procedures Server Configuration option? I have a need to enable to the Ole Automation Procedures Server Configuration Option on my SQL Server, but was wondering what specific kind of security risks do I incur by doing so?

What is an example of how the security can be exploited with this option enabled?

I don't think I would look at it in terms of exploitation. It's very similar to (if not the same security issue as) xp_cmdshell. These are wide open in terms of what you can execute if they are enabled, and for processes that reach outside of SQL Server, they use the security context of the service account running SQL Server (although xp_cmdshell does at least have the option to set up a proxy account for non-sysadmin users).

But, you need to be in the sysadmin instance-level role in order to execute the sp_OA* procs (or be given explicit EXECUTE permission), so you can at least keep all uses of it within the confines of stored procedures that use Module Signing to get the elevated permission (yes, specifying WITH EXECUTE AS N'dbo' also works, if the Login associated with the dbo User is in the sysadmin role, but Module Signing is better).

Looking at the CIS quote in @kevinsky's answer (i.e. "These are extended stored procedures that allow SQL Server users to execute functions external to SQL Server."), unless I am missing something, that seems to be an overblown, or possibly uninformed, reaction. It would certainly be a huge issue if all users had access to it if it were enabled. But really, it's only users that already have the ability to enable "OLE Automation procedures" that are allowed to use the sp_OA* stored procs. I'm not saying that it should be enabled if it isn't being used, but this doesn't seem to be an inherent security risk.

I think the security "issue" is that it allows for code to be created by developers that either does things that you might not approve of on the network or file system or at the OS level, or perhaps there are unintended consequences.

That being said, if you are asking with regards to legacy code that you need to support, then while not ideal, it will probably work. But, if this is in regards to new development then you shouldn't be using OLE Automation; you should instead be using SQLCLR. SQLCLR has many benefits over OLE Automation, including (but not limited to):

Better security: you can restrict code to being confined to SQL Server internally (SAFE) or able to access external resources but not alter the system or do asynchronous work, etc (EXTERNAL_ACCESS)
Ability to impersonate caller's Window's login when accessing external resources (not confined to service account)
Better datatype support: OLE Automation should be COM interface, and it doesn't support MAX types, XML, any of the more recent types.
Better memory management


How to fetch data that is not similar I have this table:

| id | user_id | status |
|  a |       1 |      p |
|  a |       1 |      p |
|  a |       1 |     np |
|  a |       1 |     pp |
|  b |       2 |     np |
|  b |       2 |     np |
|  c |       3 |     pp |
|  c |       3 |      p |
|  d |       4 |      p |
|  d |       4 |      p |

I want to fetch those records who don't have the same status records with group by user_id:

| id | user_id |
|  a |       1 |
|  c |       3 |

You can GROUP BY both id and user_id and only keep records HAVNG more than 1 distinct status.

Query:

SELECT id
    , user_id
    , count(distinct status)
FROM data
GROUP BY Id, user_id
HAVING count(distinct status) > 1;

SQL Fiddle

Output:

| id | user_id | count(distinct status) |
|----|---------|------------------------|
|  a |       1 |                      3 |
|  c |       3 |                      2 |


Recover Postgres Streaming Replication Slave from Archived Wal Logs I have set up a Postgres Hot Standby server by Streaming Replication. But My Standby server is asking for an old wal archive log which is currently not in Master's pg_xlog directory. But the file exists in the wal archive backup directory.

How can I configure Standby to read this file from backup directory? Or any way to manually copy this file to Standby Server ?

Any help will be appreciated.

You set the restore_command, in recovery.conf, to copy the requested file (%f) from the archive directory to the server's directory (%p).


Optimal way to ignore duplicate inserts? Background

This problem relates to ignoring duplicate inserts using PostgreSQL 9.2 or greater. The reason I ask is because of this code:

  -- Ignores duplicates.
  INSERT INTO
    db_table (tbl_column_1, tbl_column_2)
  VALUES (
    SELECT
      unnseted_column,
      param_association
    FROM
      unnest( param_array_ids ) AS unnested_column
  );

The code is unencumbered by checks for existing values. (In this particular situation, the user does not care about errors from inserting duplicates -- the insertion should "just work".) Adding code in this situation to explicitly test for duplicates imparts complications.

Problem

In PostgreSQL, I have found a few ways to ignore duplicate inserts.

Ignore Duplicates #1

Create a transaction that catches unique constraint violations, taking no action:

  BEGIN
    INSERT INTO db_table (tbl_column) VALUES (v_tbl_column);
  EXCEPTION WHEN unique_violation THEN
    -- Ignore duplicate inserts.
  END;

Ignore Duplicates #2

Create a rule to ignore duplicates on a given table:

CREATE OR REPLACE RULE db_table_ignore_duplicate_inserts AS
    ON INSERT TO db_table
   WHERE (EXISTS ( SELECT 1
           FROM db_table
          WHERE db_table.tbl_column = NEW.tbl_column)) DO INSTEAD NOTHING;

Questions

My questions are mostly academic:

What method is most efficient?
What method is most maintainable, and why?
What is the standard way to ignore insert duplication errors with PostgreSQL?
Is there a technically more efficient way to ignore duplicate inserts; if so, what is it?

Thank you!

As the answers on the other question (of which this one is considered a duplicate) mention, there is (since version 9.5) a native UPSERT functionality.  For older versions, keep reading :)

I have set up a test for checking the options.  I'll include the code below, which can be run in psql on a linux/Unix box (simply because for the sake of clarity in the results, I piped the output of the setup commands to /dev/null - on a Windows box one could choose a log file instead).

I tried to make different results comparable by using more than one (that is, 100) INSERT per type, run from a loop inside a plpgsql stored procedure.  Additionally, before each run, the table is reset by truncating and reinserting the original data.

Checking a few test runs, it looks like that using the rule and explicitly adding WHERE NOT EXISTS ot the INSERT statement spend similar time, while cathcing an exception takes significantlly more time to complete.

The latter isn't that surprising:

  Tip: A block containing an EXCEPTION clause is significantly more
  expensive to enter and exit than a block without one. Therefore, don't
  use EXCEPTION without need.

Personally, because of readability and maintainability, I prefer adding the WHERE NOT EXISTS bit to the INSERTs themselves.  Just like with triggers (which could be tested here as well), debugging (or simply tracing INSERT behaviour) is more complicated with rules present.

And the code I used (feel free to point misconceptions or other problems):

\o /dev/null
\timing off

-- set up data
DROP TABLE IF EXISTS insert_test;

CREATE TABLE insert_test_base_data (
    id integer PRIMARY KEY,
    col1 double precision,
    col2 text
);

CREATE TABLE insert_test (
    id integer PRIMARY KEY,
    col1 double precision,
    col2 text
);

INSERT INTO insert_test_base_data
SELECT i, (SELECT random() AS r WHERE s.i = s.i)
FROM
    generate_series(2, 200, 2) s(i)
;

UPDATE insert_test_base_data
SET col2 = md5(col1::text)
;

INSERT INTO insert_test
SELECT *
FROM insert_test_base_data
;

-- function with exception block to be called later
CREATE OR REPLACE FUNCTION f_insert_test_insert(
    id integer,
    col1 double precision,
    col2 text
)
RETURNS void AS
$body$
BEGIN
    INSERT INTO insert_test
    VALUES ($1, $2, $3)
    ;
EXCEPTION
    WHEN unique_violation
    THEN NULL;
END;
$body$
LANGUAGE plpgsql;

-- function running plain SQL ... WHERE NOT EXISTS ...
CREATE OR REPLACE FUNCTION insert_test_where_not_exists()
RETURNS void AS
$body$
BEGIN
    FOR i IN 1 .. 100
    LOOP
        INSERT INTO insert_test
        SELECT i, rnd, md5(rnd::text)
        FROM (SELECT random() AS rnd) r
        WHERE NOT EXISTS (
            SELECT 1
            FROM insert_test
            WHERE id = i
        )
        ;
    END LOOP;
END;
$body$
LANGUAGE plpgsql;

-- call a function with exception block
CREATE OR REPLACE FUNCTION insert_test_function_with_exception_block()
RETURNS void AS
$body$
BEGIN
    FOR i IN 1 .. 100
    LOOP
        PERFORM f_insert_test_insert(i, rnd, md5(rnd::text))
        FROM (SELECT random() AS rnd) r
        ;
    END LOOP;
END;
$body$
LANGUAGE plpgsql;

-- leave checking existence to a rule
CREATE OR REPLACE FUNCTION insert_test_rule()
RETURNS void AS
$body$
BEGIN
    FOR i IN 1 .. 100
    LOOP
        INSERT INTO insert_test
        SELECT i, rnd, md5(rnd::text)
        FROM (SELECT random() AS rnd) r
        ;
    END LOOP;
END;
$body$
LANGUAGE plpgsql;

\o
\timing on

\echo
\echo 'check before INSERT'

SELECT insert_test_where_not_exists();

\echo

\o /dev/null

\timing off

TRUNCATE insert_test;

INSERT INTO insert_test
SELECT *
FROM insert_test_base_data
;

\timing on

\o

\echo 'catch unique-violation'

SELECT insert_test_function_with_exception_block();

\echo
\echo 'implementing a RULE'

\o /dev/null
\timing off

TRUNCATE insert_test;

INSERT INTO insert_test
SELECT *
FROM insert_test_base_data
;

CREATE OR REPLACE RULE db_table_ignore_duplicate_inserts AS
    ON INSERT TO insert_test
    WHERE EXISTS (
        SELECT 1
        FROM insert_test
        WHERE id = NEW.id
    )
    DO INSTEAD NOTHING;

\o
\timing on

SELECT insert_test_rule();


How to plan bring down the space used by databases to as low as possible Per our security breaks and guidelines when a database grow too big that it cant meet the requirements where it cannot be restored under a given RTO we need to plan on achieving same:-
Based on your experiences i would need some guidance and help here:-
Some insights on the database in question -

Total space --> Right click database-properties -> size = 10TB
What seems free in above -->  Right click database-properties -> space
Available = 5 TB

This database is an OLTP and critical one as it goes through lot of DUI operations throughout with heavy usage of TempDB

I have listed out TOP 10 tables with space and index usage:-
All top 5 as pulled are

partitioned table with billion and million rows over 700 partitions
in single table

Above tables have PAGE compression enabled

Above tables have less than 3 indexes in most of them but compression
at index level shows NONE

For backup we do use compression with third party tool like idera to reduce size yet it crosses the threshold. Backup size is approx 1.9 TB for full backups. We do have diff and tran log backups as well.
I am little hesitant to go that shrinking route as it may impact performance but please help me with any ideas but else can be done to bring the space down?
Can we split few large table to their own separate database? If yes what is the catch?
Much appreciate all your help

I'll echo SqlWorldWide on the point about data types. The article they linked has a few good examples, but there are a lot more less-obvious areas to check too.
I've written a script sp_sizeoptimiser to help automatically identify a lot of them. Even if you don't use it, the areas it covers are worth looking into:
Data Types

Time based data types
Unspecified VARCHAR length
Mad VARCHAR Max
NVARCHAR data type
FLOAT and REAL data types
BIGINT as IDENTITY
NUMERIC or DECIMAL with 0 scale
Enum columns not implemented as foreign key

Architecture

Default fill factor
Number of indexes
Inefficient indexes
Sparse columns
Heap tables

Details for each of these is in the documentation linked above.
Preferably, I'd run this on a non-production copy of the database if possible, however it doesn't do any heavy lifting under the hood, so should be OK to run on production as well.


Why are there Victimless Entries on the Deadlock Graph? I am trying to learn how to analyze SQL Server 2008's deadlock graph, and I'm finding alot of entries with an empty <victim-list> node.  I don't understand what these entries represent: if there is no victim, how can I identify the waitresource that is causing the deadlock?  What do these entries mean?

Here's a quick example of the entries i'm seeing:

<deadlock-list>
 <deadlock>
  <victim-list />
  <process-list>
   <process id="processd2b6508" taskpriority="0" logused="10000" waittime="31" schedulerid="63" kpid="9104" status="suspended" spid="69" sbid="0" ecid="184" priority="0" trancount="0" lastbatchstarted="2012-07-30T01:10:45.550" lastbatchcompleted="2012-07-30T01:10:45.550" clientapp=".Net SqlClient Data Provider" hostname="XXXXXXX" hostpid="3648" isolationlevel="read committed (2)" xactid="30461033" currentdb="5" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
    <executionStack>
     <frame procname="" line="1" sqlhandle="0x020000002340c50225c17d0eec9bf7c51129348edffd1c70" />
     <!--About 2 more frame tags... -->
    </executionStack>
    <inputbuf />
   </process>
   <!-- 3 or so more process tags... -->
  </process-list>
  <resource-list>
   <exchangeEvent id="Pipeb005eeba0" WaitType="e_waitPipeNewRow" nodeId="7">
    <owner-list>
     <owner id="processd23fdc8" />
    </owner-list>
    <waiter-list>
     <waiter id="processd2b6508" />
    </waiter-list>
   </exchangeEvent>
   <!-- 2 more exchangeEvents -->
  </resource-list>
 </deadlock>
</deadlock-list>

** edit **
As requested, here is the query used to identify a query by it's sqlhandle:

select sql_handle as Handle,
    SUBSTRING(st.text, (qs.statement_start_offset/2)+1,
        ((CASE qs.statement_end_offset
          WHEN -1 THEN DATALENGTH(st.text)
         ELSE qs.statement_end_offset
         END - qs.statement_start_offset)/2) + 1) AS Text

from sys.dm_exec_query_stats as qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
where sql_handle = --0x04000D00E3572A56542E4601CE9E00010100001000000000

from RyanBoyer.net

ExchangeEvent & e_waitPipeNewRow suggests you've run into what Bart Duncan refers too as Annoyingly-Unwieldy Term: "Intra-Query Parallel Thread Deadlocks".

Most intra-query parallelism deadlocks are considered bugs, although
some of them can be risky bugs to fix so a fix may not be possible.
If you run into one and you're already on the latest SQL service pack,
your best bet may be to investigate workarounds.

So, not much you can do other than:

Ensure you're on the latest service pack and cumulative update.
Try to identify indexes and/or other optimisations to improve the performance of the query. You mention that inputbuf isn't populated but you may be able to identify the query in play via the sqlhandle in the graph XML. If you get nothing from that try running a trace and correlating with the times these deadlocks occur.
Reduce MAXDOP for this query or try MAXDOP(1) to force single-threaded execution. Be aware that you might fix the deadlocks but introduce a different set of performance issues by restricting parallelism.
Open a support call with Microsoft. Possible that a) they have a non-public hotfix for this scenario or b) as these intra-query deadlocks are deemed to be bugs they may want to work with you to find a fix.


How to change SQL Server in a domain with a new server machine and latest SQL database? We have an SQL Server in our domain.
The SQL Server version is MS SQL 2000 and the Operating System is Windows Server 2003.

Now I want to change the machine itself with new computer running windows server 2012 and SQL Server 2008.

How to do this ?

What you are planning is called side-by-side upgrade. It means that wholly new system is installed. Its alternative is in-place upgrade. It means that only the Sql Server is upgraded on existing computer. As you are going to upgrade the operating system too, side-by-side is the only option.

Microsoft has released a white paper about how to upgrade Sql Server. The pdf has 400+ pages, but not all parts are likely to be relevant to your case.

Read the documentation and ask for clarifications about things you didn't quite catch.

As a side note: though migrating from Sql Server 2000 to 2008 is certainly an improvement, you really should migrate to more recent version. That is, Sql Server 2014 or 2016 should be your migration target. Be aware that Sql Server 2000 is so old that it cannot be migrated directly to Sql Server 2012. An intermediate step into Sql Server 2008 is needed.


Is Azure Managed Disks enough to ensure high-durability for a database? I want to set up a database in a high durability set-up on Azure. I've previously relied on DB-as-a-service offerings, but can't do that in this case, so I'd like your feedback on the plan below. Is this enough to ensure reliable storage of data?

An Azure Web App takes in metric data from the web, does some minor processing and sampling, and sends the data in batches to VM2.
VM2 runs the Clickhouse database, and stores data on an Azure Managed Disk
Some periodical job takes snapshots of the disk using Clickhouse built-in backup functionality and stores them to cold storage

The periodical backup is meant to mitigate human error, i.e. accidentally running "DROP TABLE xx" on the wrong data.
The big question is if managed disks are an acceptable substitute for database replication, to ensure data durability. Azure Managed Disks are advertised as being very durable forms of storage, with built in triple-redundant replication. They are advertised as good for database use. It seems that this should be enough to take away any concerns of data loss due to hardware failure. Is this correct? Do you see any potential problems with this?
The recovery plan is that if VM2 fails, some monitoring process catches this and spins up a new VM2 instance attached to the same managed disk. The Web App similarly restarts if it fails.
I understand that this setup isn't high-availability, if a VM fails there will be some window of time before it is able to store new data. This is acceptable to me. But I want to ensure that data that gets stored will not be lost, i.e. is durably stored with very high probability. Is this enough to ensure that? Do you see any problems?

But I want to ensure that data that gets stored will not be lost, i.e. is durably stored with very high probability.

Yes.  It could happen, but it's an extremely low-probability event, and there's many other low-probability events that are more likely.  It's vastly more likely that your data will be lost due to a guest OS driver problems, human error, or malware than Azure would lose data on a disk.
See:
LRS provides at least 99.999999999% (11 nines) durability of objects over a given year.

Azure Storage redundancy
And whatever that means, you can bet that a lot of things would have to go very wrong to lose a disk.
If you are happy recovering from backups for those other scenarios, you should be fine.`


postgresql : **ERROR: path element at position 1 is not an integer:** I am trying to update a field value in jsonb column with text value present in another column but getting some syntax errors; not getting any solution.
i am trying to swap values of OutgoingVertexid & IncomingVertexId in below JSONB
'[
      {
         "Edgeid":10,
         "Weight":100,
         "Active":1,
         "OutgoingVertexid":"",
         "IncomingVertexid":"G",
         "EdgeColor":"Black"
      }
   ]
'

so used below code by putting all values OutgoingVertexid & IncomingVertexid value in temp table.
UPDATE temp_table
        SET
        owner_property = CASE
                            WHEN owner_outgoing_edge IS NOT NULL
                                THEN jsonb_set(owner_property, '{OutgoingVertexid}', '""')
                            ELSE
                                jsonb_set(owner_property, '{OutgoingVertexid}', ''||owner_incoming_edge::jsonb||'')
                                END;

but getting below error:
ERROR: path element at position 1 is not an integer:
Thanks in Advance

Your json value is an array, not a single value. So you need to pick the array element you want to change by including the index in the array you pass to jsonb_set()
UPDATE temp_table
  SET owner_property = CASE
                        WHEN owner_outgoing_edge IS NOT NULL
                          THEN jsonb_set(owner_property, '{0, OutgoingVertexid}', '""')
                        ELSE
                          jsonb_set(owner_property, '{0, OutgoingVertexid}', to_jsonb(owner_incoming_edge))
                       END


SQL Server 2008r2 replication from multiple sources I am currently rearchitecting an application so that each of seven businesses will have there own "live" SQL Server 2008 database and at scheduled times during the data the data from all seven databases will be replicated to a master reporting database.

The question I have is do I need to do anything within the replication to maintain the Foriegn Key relationships from the source database tables or will replication take care of it for me?

SQL Replication isn't going to handle that for you.  You'll have to setup a very custom replication type system, possibly using SQL Service Broker to handle the merging of data into a single system for reporting.


Having problems with concurrency while trying to lock a record in custom messaging system (Queue) We are building our own custom messaging system and are having concurrency issues. Here are the rules:

A process (EXE) console application locks 3 records and returns them
No other process running (we have 5 EXEs running) can pick any record that the other processes have already taken.

That simple, but yet, I'm puzzled.

Summary of the SQL Server stored procedure doing a "Lock And Peek":

The idea behind this that we reserve three "NEW" records and change their status to "IN PROGRESS" with a ROWLOCK on the SELECT and UPDATE statements. So in theory these records should be locked for one process so that other processes can't update or even select them. Can someone tell me what I'm doing wrong please?

ALTER PROCEDURE [dbo].[LockAndPeek]
    @Count INT,
    @QueueTypeId INT
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @ListofIDs TABLE(ID INT);
    DECLARE @StatusIDInProgress INT

    SELECT @StatusIDInProgress = ID
    FROM QueueStatuses (NOLOCK)
    WHERE Name = 'In Progress'

    INSERT INTO @ListofIDs (ID)
        SELECT TOP (@Count) Q.ID
        FROM Queues Q (ROWLOCK)
        INNER JOIN QueueStatuses QS (ROWLOCK) ON Q.StatusID = QS.ID
        WHERE QS.Name IN ('New', 'Errored')
          AND Q.TypeID = @QueueTypeID
          AND Q.AvailableTime IS NOT NULL
          AND Q.AvailableTime <= GETUTCDATE()
        ORDER BY Q.ID

    UPDATE Q WITH (ROWLOCK)
    SET STATUSID = @StatusIDInProgress,
        PROCESSED = GETUTCDATE()
    FROM Queues Q (ROWLOCK)
    INNER JOIN QueueStatuses QS (ROWLOCK) ON Q.StatusID = QS.ID
    INNER JOIN @ListofIDs LI ON Q.ID = LI.ID
    WHERE QS.Name IN ('New', 'Errored')

    SELECT
        Q.ID, Q.AvailableTime, Q.NumberOfTries,
        Q.Created, Q.Processed, Q.ErrorData,
        QT.ID QueueTypeID, QT.Name QueueTypeName,
        QS.ID QueueStatusID, QS.Name QueueStatusName,
        Q.Message
    FROM
        Queues Q (NOLOCK)
    INNER JOIN
        QueueStatuses QS (NOLOCK) ON Q.StatusID = QS.ID
    INNER JOIN
        QueueTypes QT (NOLOCK) ON Q.TypeId = QT.ID
    INNER JOIN
        @ListofIDs LI ON Q.ID = LI.ID
END

The query looks so much simpler. There's an output keyword in SQL Server that works perfectly!

Here we go:

ALTER PROCEDURE [dbo].[LockAndPeek]
    @Count INT,
    @QueueTypeId INT
AS
BEGIN

SET XACT_ABORT ON; -- blow up the whole tran on any errors
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN TRAN

UPDATE Q
SET
    StatusID = 2, -- In Progress
    Processed = GETUTCDATE()
OUTPUT Inserted.*
FROM (
    SELECT TOP (@Count) *
    FROM
        Queues WITH (READPAST, ROWLOCK)
    WHERE
        StatusID = 1 AND -- New
        TypeID = @QueueTypeID AND
        AvailableTime IS NOT NULL AND
        AvailableTime <= GETUTCDATE()
   ORDER BY ID
) Q;

COMMIT TRAN;

END


Setting default CHARSET and DB Engine I recently updated my server's database system from MySQL to MariaDB.
After update, everything works well.

But, when I went to phpMyAdmin, the default charset and types are changed:

At the end of this image, the default type was set to 'InnoDB' and charset was set to 'latin1_swedish_ci'.
They were 'MyISAM' and 'utf8_general_ci' before.
How can I revert my character set and type settings back, and how can I customize settings when I make new database?

When I looked at /etc/my.cnf and /etc/my.cnf.rpmnew, I couldn't find any settings related to DB type/charset.

"How can I revert my character set" :

ALTER TABLE myTable CONVERT TO CHARACTER SET "utf8";

"How can I revert my type": (MySQL Engine):

ALTER TABLE myTbale ENGINE=MyISAM;

"how can I customize settings when I make new database?"

In your my.cnf

default-storage-engine=MyISAM

Best Regards.

Max.


Database Design: What is the best structure for storing site offices and locations? Background

I am creating a database driven web application for the management team at the place I work. Part of this application requires fairly specific locations to be linked with where equipment resides. The RDBMS that will be used is MySQL.

The site has 5 major zones labelled Black, Blue, Gold, Green and Red. Within each zone are several offices, generally referred to as Blue 57, Gold 155 etc. Along with the offices are rooms such as cleaner's cupboards, toilets and kitchens which have no specific number. There are also a handful of larger spaces which do not technically come under a specific zone.

The Problem

My concern here is how best to store all these locations within my database.

I could of course just have a flat table with an ID and Name columns:

LocationID        Name

45                Gold 1
46                Gold 2
47                Gold 3
48                Red Kitchen
49                Blue Toilet First Floor
...

However, I recognise that there is a huge amount of repetition. Plus if the site changes zone layout, names or office numbering (has happened at least once) it would be a nightmare to fix. So my next thought was to put the major zones and spaces in their own table, so I would have:

LocationID        Name                   ZoneID

45                1                      102
46                2                      102
47                3                      102
48                Kitchen                104
49                Toilet First Floor     101

ZoneID     Zone

100        Black
101        Blue
102        Gold
103        Green
104        Red

This is definitely better, but with certain types of room - toilets, kitchens et al - there is still repetition (no two numbered offices have the same number, i.e. there is no Red 14 and Blue 14).

Is having a small amount of repetition acceptable to keep the tables simple? If I had a third table with stuff like "Kitchen", "Toilet", "Lockers" etc I would need a RoomType foreign key in the Location table. I would then have a situation where some locations have a Name and a null value for the RoomType, or the opposite and have a RoomType value and empty Name value. This doesn't strike me as being ideal.

Is there a way of doing this that avoids too much redundancy but doesn't increase the complexity massively? These tables will mostly be read with updates and inserts rare once the DB is populated.

Here is some vanilla SQL:

CREATE TABLE RoomTypes
(
 RoomType VARCHAR(12) NOT NULL,
 UNIQUE (RoomType)
);

CREATE TABLE Zones
(
 Zone VARCHAR(10) NOT NULL,
 UNIQUE (Zone)
);

CREATE TABLE Rooms
(
 RoomType VARCHAR(12) NOT NULL,
 Zone VARCHAR(10) NOT NULL,
 Name VARCHAR(30) NOT NULL,
 UNIQUE (RoomType, Zone, Name),
 FOREIGN KEY (RoomType) REFERENCES RoomTypes (RoomType),
 FOREIGN KEY (Zone) REFERENCES Zones (Zone)
);

CREATE TABLE NumberedRooms
(
 RoomType VARCHAR(12) NOT NULL,
 Zone VARCHAR(10) NOT NULL,
 Name VARCHAR(30) NOT NULL,
 CHECK (RoomType = 'Numbered'),
 UNIQUE (Name),
 UNIQUE (RoomType, Zone, Name),
 FOREIGN KEY (RoomType, Zone, Name)
    REFERENCES Rooms (RoomType, Zone, Name)
);

CREATE TABLE AncillaryRooms
(
 RoomType VARCHAR(12) NOT NULL,
 Zone VARCHAR(10) NOT NULL,
 Name VARCHAR(30) NOT NULL,
 CHECK (RoomType = 'Ancillary'),
 UNIQUE (Zone, Name),
 UNIQUE (RoomType, Zone, Name),
 FOREIGN KEY (RoomType, Zone, Name)
    REFERENCES Rooms (RoomType, Zone, Name)
);

The CHECK constraints will not be tested by mySQL e.g. do the tests yourself using triggers. Consider adding other tests e.g. that attribute Name in table NumberedRooms represents an integer.

The idea that every row in the supertype table Rooms will have exactly one row in the union of AncillaryRooms and NumberedRooms. This is merely implied e.g. have 'helper' procs to add rows to both super- and subtype tables as a single operation and use triggers to ensure it is done.

Note NumberedRooms has a simple key Name alone, whereas AncillaryRooms has a compound key on (Zone, Name). All three (non-lookup) tables have a key on (RoomType, Zone, Name) throughout, allowing further subtype tables to referencing them and maintain integrity by further testing for valid RoomType values.


Isssue with Database Links converting OEM Job to DBMS_Scheduler Job We had an OEM job that would run a PL/SQL block using a particular database users credentials.  The credentials are the same for two different databases, so there was no problem for the block to use a private database link without credentials to pull data from the second database into the first.

We migrated the job to DBMS_SCHEDULER, but the job fails with the following error:

ORA-04052: error occurred when looking up remote object PATIENT.PTTRANS@BILL10G
ORA-00604: error occurred at recursive SQL level 2 ORA-01017: invalid username/password; logon denied
ORA-02063: preceding line from BILL10G

I haven't found definitive information, but the things I have read seem to indicate that since scheduler jobs do not have a login, they cannot use database links that use passed credentials.  That would make sense since I get the same error whether the job is owned by SYSTEM or by the user that normally runs the block (and still can when logged in).

Can anyone provide further explanation of this situation or more importantly explain ways to work around this issue?  I can think of several, but I'm not sure I like any of them.

The only way to get around this was to use credentials in the database links definition. Not nice if you ask me but it is what I found. This was in 10g and 11, did not test with 12c.


What can be the downside of always having a single integer column as primary key? Within one Web application I am working on, all database operations are abstracted using some generic repositories defined over Entity Framework ORM.

However, in order to have a simple design for the generic repositories, all involved tables must define an unique integer (Int32 in C#, int in SQL). Until now, this has been always the PK of the table and also the IDENTITY.

Foreign keys are heavily used and they reference these integer columns. They are required for both consistency and for generating navigational properties by the ORM.

The application layer typically does the following operations:

initial data load from table (*) - SELECT * FROM table
Update - UPDATE table SET Col1 = Val1 WHERE Id = IdVal
Delete - DELETE FROM table WHERE Id = IdVal
Insert - INSERT INTO table (cols) VALUES (...)

Less frequent operations:

Bulk insert - BULK INSERT ... into table followed (*) by all data load (to retrieve generated identifiers)
Bulk delete - this is a normal delete operation, but "bulky" from ORM's perspective: DELETE FROM table where OtherThanIdCol = SomeValue
Bulk update - this is a normal update operation, but "bulky" from ORM's perspective: UPDATE table SET SomeCol = SomeVal WHERE OtherThanIdCol = OtherValue

*all small tables are cached at application level and almost all SELECTs will not reach database. A typical pattern is initial load and lots of INSERTs, UPDATEs and DELETEs.

Based on current application usage, there is very small chance of ever reaching 100M records in any of the tables.

Question: From a DBA's perspective, are there significant problems I can run into by having this table design limitation?

[EDIT]

After reading the answers (thanks for the great feedback) and referenced articles, I feel like I have to add more details:

Current application specifics - I did not mention about current web application, because I want to understand if the model can be reused for other applications as well. However, my particular case is an application that extracts lots of metadata from a DWH. Source data is quite messy (denormalized in a weird way, having some inconsistencies, no natural identifier in many cases etc.) and my app is generating clear separated entities. Also, many of the generated identifiers (IDENTITY) are displayed, so that the user can use them as business keys. This, besides a massive code refactoring, excludes usage of GUIDs.
"they should not be the only way to uniquely identify a row" (Aaron Bertrand♦) - that is a very good advice. All my tables also define an UNIQUE CONSTRAINT to ensure that business duplicates are not allowed.
Front-end app driven design vs. database driven design - design choice is caused by these factors

Entity Framework limitations - multiple columns PKs are allowed, but their values cannot be updated
Custom limitations - having a single integer key greatly simplifies data structures and non-SQL code. E.g.: all lists of values have an integer key and a displayed values. More important, it guarantees that any table marked for caching will be able to put into a Unique int key -> value map.

Complex select queries - this will almost never happen because all small (< 20-30K records) tables data is cached at application level. This makes life a little harder when writing application code (harder to write LINQ), but the database is hit much nicer:

List views - will generate no SELECT queries on load (everything is cached) or queries that look like this:

SELECT allcolumns FROM BigTable WHERE filter1 IN (val1, val2) AND filter2 IN (val11, val12)

All other required values are fetched through cache lookups (O(1)), so no complex queries will be generated.
Edit views - will generate SELECT statements like this:

SELECT allcolumns FROM BigTable WHERE PKId = value1

(all filters and values are ints)

Other than additional disk space (and in turn memory usage and I/O), there's not really any harm in adding an IDENTITY column even to tables that don't need one (an example of a table that doesn't need an IDENTITY column is a simple junction table, like mapping a user to his/her permissions).

I rail against blindly adding them to every single table in a blog post from 2010:

Bad habits to kick : putting an IDENTITY column on every table

But surrogate keys do have valid use cases - just be careful not to assume that they guarantee uniqueness (which is sometimes why they get added - they should not be the only way to uniquely identify a row). If you need to use an ORM framework, and your ORM framework requires single-column integer keys even in cases when your real key is either not an integer, or not a single column, or neither, make sure that you define unique constraints/indexes for your real keys, too.


Oracle Hierarchical query: how to follow max on each level I'm trying to get a query to "follow" the max values in each level excluding the max values in other branches.
Here some examples to illustrate what I'm trying to achieve:

Given this hierarchical structure:

    38
      15
        10
        5
          2
          3
      17
        9
        8
      6
    26
      13
      1
      12
    18
      11
      7

I would like to get:

    38
      17
        9

and not:

    38
      17
        10
          3

Like I would get by using the sql query I've come up with until now:

select lpad(' ',2*(level-1))|| max(child)
from test_connect_by
start with parent is null
connect by prior child = parent
group by level

That get the absolute max from each level without excluding the other branches on each iteration over the levels.

The example data comes from http://www.adp-gmbh.ch/ora/sql/connect_by.html

Thanks in advance for any hint
Vlax

This seems to solve the question asked. Not sure if there is a solution without aggregating first:

select lpad(' ',2*(level-1)) || child as result
from
  ( select parent, max(child) as child
    from test_connect_by
    group by parent
  ) t
start with parent is null
connect by prior child = parent
order by level ;


How to separate name string by the spaces in the name in T-SQL I have a list of names, such as: Doe John James, Doe Jane and Doe Henry Albert Sr

I need to split those out into the four component parts of the name (if the name has four parts) to put each of them in their own column (last name, first name, middle name, suffix), while allowing for names that may or may not have a middle name or suffix.

I've got it figured out for everything but the third instance, where there is a suffix.

For first name I have:

case
    when LEN(LTRIM(rtrim(@name))) - LEN(replace(ltrim(rtrim(@name)),' ','')) = 2
        then LEFT(SUBSTRING(LTRIM(RTRIM(@name)), CHARINDEX(' ', LTRIM(RTRIM(@name))) + 1, LEN(LTRIM(RTRIM(@name)))), CHARINDEX(' ', SUBSTRING(LTRIM(RTRIM(@name)), CHARINDEX(' ', LTRIM(RTRIM(@name))) + 2, LEN(LTRIM(RTRIM(@name))))))
    else
        SUBSTRING(@name, CHARINDEX(' ', @name) + 1, LEN(@name))
end as firstName

It's pretty crazy, but it works...

For middle name I have:

case
    when LEN(LTRIM(rtrim(@name))) - LEN(replace(ltrim(rtrim(@name)),' ','')) = 2
        then
            REVERSE(SUBSTRING(REVERSE(LTRIM(RTRIM(@name))),1,CHARINDEX(' ',REVERSE(LTRIM(RTRIM(@name))))))
    else ''
end as middleName

Not quite as complex as the first name.

Since the last name is at the beginning that's the easiest:

SUBSTRING(LTRIM(RTRIM(@name)),1,CHARINDEX(' ',LTRIM(RTRIM(@name)))-1) as lastName

But I can't figure out how to allow for names with a suffix (Jr, Sr, III, etc).

Does something like this help you:

With split(id, n, start, pos) as(
    SELECT id, 1, 0, CHARINDEX(' ', fullname) FROM @names
    UNION ALL
    SELECT n.id, n+1, pos+1, CHARINDEX(' ', fullname, pos+1)
    FROM @names n
    INNER JOIN split s ON n.id = s.id
    WHERE CHARINDEX(' ', fullname, start+1) > 0
)
SELECT id
    , lastName = MAX(CASE WHEN v.n = 1 THEN v.val END)
    , firstname = MAX(CASE WHEN v.n = 2 THEN v.val END)
    , middleName = MAX(CASE WHEN v.n = 3 THEN v.val END)
    , title = MAX(CASE WHEN v.n = 4 THEN v.val END)
FROM (
    SELECT n.id, s.n
        , SUBSTRING(n.fullname
                , s.start
                , CASE WHEN s.pos = 0 THEN LEN(n.fullname)+1 ELSE s.pos END -s.start
            )
    FROM @names n
    INNER JOIN split s ON n.id = s.id
) as v(id, n, val)
GROUP BY id
--OPTION(MAXRECURSION  4)
;

The recursive CTE get the start and end position of each name along with a value n from 1 to up to 4.
Based on the value of n the GROUP BY put them in their respective columns.

On a big table, it may not be very efficient though.

Output:

id  lastName    firstname   middleName  title
0   Doe         John        James       NULL
1   Doe         Jane        NULL        NULL
2   Doe         Henry       Albert      Sr
3   Bart        Simpson     NULL        NULL

Data:

declare @names table(id int identity(0, 1), fullname varchar(200), first varchar(50), middle varchar(50), last varchar(50), title varchar(50));
INSERT INTO @names(fullname) values
    ('Doe John James')
    , ('Doe Jane')
    , ('Doe Henry Albert Sr')
    , ('Bart Simpson')
;


Allow non-sysadmin, non-owner of a SQL Server Agent job to execute it I have a job that runs an SSIS package.

Currently the owner is a proxy account. I can run the job manually from a sys-admin account.

Our web service logs in using a limited account. It needs to execute the job.
Currently it is unable to see the job at all(when I try to execute by name, it says it doesn't exist).

I tried changing to owner of the job to the limited account. Now it could see the job, but the job failed execution because it can't run the SSIS package anymore.

There has to be a way to allow the limited account to run a job owned by another account right?

It is possible to set up a method to grant rights to run a job that a user does not have enough authority to run on its own.

EDIT: For clarity on the three options presented by explicitly mentioning the SQLAgentOperatorRole as an option and by adding some explanation on the third solution.

(1) If the user is allowed to manage the execution of all jobs, then make that user member of SQLAgentOperatorRole.  The user will be able to start (as well as stop, enable, and disable) any SQL Agent job on that server.   (This solution turned out to satisfy the original asker.)

(2) Erland Sommarskog has written a lot on how to grant permissions through stored procedures using counter-signatures.  He has a solution at:

http://www.sommarskog.se/grantperm.html#countersignatures

The key point is: "To be able to start a job owned by someone else, you need to be member of the fixed role SQLAgentOperatorRole in msdb. A start is to write a stored procedure that calls sp_start_job for this specific job, sign that procedure with a certificate, and then create a user from the certificate and make that user a member of SQLAgentOperatorRole."

(3) My general resolution was to create a StartAgentJob stored procedure in the msdb database allowing a user to start jobs owned by someone else.

This requires a table to maintain the configuration of who can run which job. Since the following dbo.msdbJobMap table is SQL Server Agent Job specific, I would create the table in msdb.  But it could be created in some other service database if desired.

USE msdb;

/* Create a table to hold configuration of who can start jobs. */
CREATE TABLE dbo.msdbJobMap
 (job_name NVARCHAR(128),
  group_name NVARCHAR(256));

/* Populate the table of allowed groups for a job
   A group may be a single user or a Windows group. */
INSERT INTO dbo.msdbJobMap Values (N'Test it out',N'Domain\Group');
INSERT INTO dbo.msdbJobMap Values (N'Another job',N'Domain\OtherGroup');
INSERT INTO dbo.msdbJobMap Values (N'Special job',N'Domain\Joe');
INSERT INTO dbo.msdbJobMap Values (N'Special job',N'Domain\Andre');

The stored procedure also allows any member of a specified group to start a job since it uses IS_MEMBER to check group membership.

CREATE PROCEDURE dbo.StartAgentJob
@Job_Name NVARCHAR(128)
WITH EXECUTE AS OWNER
AS
SET NOCOUNT ON;

DECLARE @Allowed INT;
SET @Allowed = 0;

/* Since this runs as sysadmin need to check group membership of original login*/
EXECUTE AS LOGIN = ORIGINAL_LOGIN();
IF EXISTS (SELECT * FROM dbo.msdbJobMap
           WHERE job_name = @Job_Name
           AND IS_MEMBER(group_name) = 1 )
   SET @Allowed = 1;
REVERT;

/* Back to sysadmin so that we can start the job. */

IF @Allowed = 1
    EXEC sp_start_job @job_name = @Job_Name;
ELSE
    PRINT 'Invalid attempt to start ''' + QUOTENAME(@Job_Name)+'''';
RETURN;

As you can see, the procedure depends on running as sysadmin in msdb. By switching to the context of the ORIGINAL_LOGIN it is able to use IS_MEMBER to check that the ORIGINAL_LOGIN has indeed been granted rights through the dbo.msdbJobMap table. Then it goes back to being sysadmin so that it can start the job.


Performance effects of database file size and available space I have set the autogrowth and maxsize properties to:

By 10 MB, Unlimited

When I look at the Properties -> General Tab by right-clicking the database in SSMS, I see the file size is 2249MB with 6.1 MB of space available. What does this mean?

I have 700 GB free space remaining on the drive where the log file and database file sits. The log file does have a limit of 2097152 MB. It looks like that space available means log file space?

In terms of database performance, how does the size of the database and log files affect reads and writes?

The size in and of itself is what you will need to appropriately determine when setting and growing. This is all part of properly sizing your database.

But you need to be careful with growing your files at such a small increment. If you are growing your files often, you are causing SQL Server to have to do this relatively expensive operation. Take, for instance, growing of the transaction log. When this happens all write activity will be impeded. Another thing about that is if you grow your transaction log at a small interval and it grows often, you will end up with a large amount of VLFs that are small in size.

Instant File Initialization is a great approach, as it bypasses the zero-initialization of the additional file (or file allocation). This shaves off a lot of the overhead and performance impact, but this is only possible with data files, not log files.

As you can see, there are a handful of things to consider depending on your environment and how much your database grows. Size your database appropriately and find the right growth increment.

Note, autogrowth should be reserved for emergency situations and it is recommended to manually grow your files if at all possible. This gives you full control over all of the points above, as well as allowing you to determine when this operation occurs.

As for your numbers, "space available" is just that:  The free space that you have for additional data until growth will need to happen. One strategy is to get notified when you reach a certain threshold of used space so that you can schedule and kick off growth of the appropriate file(s).


How to get syntax and logical ORA in a trace file? there is an application with tons of queries and one of them cause ORA-1722
To check: I turn trace on session like:
alter session set events '10046 trace name context forever, level 12'

and say to database
SELECT TO_NUMBER('asdf') FROM DUAL;

I actually receive ORA-1722 on the client side, but can't see that query finished with error code:
PARSING IN CURSOR #140193170131176 len=34 dep=0 uid=9 oct=3 lid=9 tim=24633865068083 hv=3356444932 ad='d25d6b00' sqlid='cmpv4d740yk84'
SELECT TO_NUMBER('asdf') FROM DUAL
END OF STMT
PARSE #140193170131176:c=2315,e=2207,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=1388734953,tim=24633865068082
EXEC #140193170131176:c=27,e=26,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1388734953,tim=24633865068204
WAIT #140193170131176: nam='SQL*Net message to client' ela= 2 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=24633865068275
FETCH #140193170131176:c=19,e=20,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1388734953,tim=24633865068331
STAT #140193170131176 id=1 cnt=1 pid=0 pos=1 obj=0 op='FAST DUAL  (cr=0 pr=0 pw=0 str=1 time=1 us cost=2 size=0 card=1)'
WAIT #140193170131176: nam='SQL*Net break/reset to client' ela= 3 driver id=1413697536 break?=1 p3=0 obj#=-1 tim=24633865068482
WAIT #140193170131176: nam='SQL*Net break/reset to client' ela= 27988 driver id=1413697536 break?=0 p3=0 obj#=-1 tim=24633865096496
WAIT #140193170131176: nam='SQL*Net message from client' ela= 537077 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=24633865633643
CLOSE #140193170131176:c=8,e=9,dep=0,type=0,tim=24633865633723
PARSE #140193170129200:c=32,e=32,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=0,tim=24633865633836
BINDS #140193170129200:

 Bind#0
  oacdty=01 mxl=32767(32767) mxlc=00 mal=00 scl=00 pre=00
  oacflg=03 fl2=1400010 frm=01 csi=873 siz=32767 off=0
  kxsbbbfp=7f8143f31000  bln=32767  avl=00  flg=05
 Bind#1
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=03 fl2=1400000 frm=01 csi=873 siz=24 off=0
  kxsbbbfp=7f814424b060  bln=22  avl=00  flg=05
WAIT #140193170129200: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=24633865634181
EXEC #140193170129200:c=336,e=336,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=1,plh=0,tim=24633865634210
XCTEND rlbk=0, rd_only=1, tim=24633865634250

How to detect query that cause ORA-1722 (or any other logical/syntax issue)?

You can trace specific errors like below:
SQL> alter session set events '1722 trace name errorstack level 1';

Session altered.

SQL> SELECT TO_NUMBER('asdf') from dual;
SELECT TO_NUMBER('asdf') from dual
                 *
ERROR at line 1:
ORA-01722: invalid number

In the trace file:
*** 2020-10-15T18:56:23.811818+02:00
*** SESSION ID:(18.12475) 2020-10-15T18:56:23.811837+02:00
*** CLIENT ID:() 2020-10-15T18:56:23.811841+02:00
*** SERVICE NAME:(SYS$USERS) 2020-10-15T18:56:23.811845+02:00
*** MODULE NAME:(SQL*Plus) 2020-10-15T18:56:23.811849+02:00
*** ACTION NAME:() 2020-10-15T18:56:23.811853+02:00
*** CLIENT DRIVER:(SQL*PLUS) 2020-10-15T18:56:23.811856+02:00

dbkedDefDump(): Starting a non-incident diagnostic dump (flags=0x0, level=1, mask=0x0)
----- Error Stack Dump -----
<error barrier> at 0x7ffdfe31ce40 placed dbkda.c@296
ORA-01722: invalid number
----- Current SQL Statement for this session (sql_id=bkusfcu38gbjr) -----
SELECT TO_NUMBER('asdf') from dual

To log them, you can use a servererror trigger as shown here:
Is it possible in Oracle to trace SQL statements that result in errors?


postgresql - declaring variables to be used for start_time - end_time range I'm relatively new to postgresql, so forgive me if this is a simple problem.
I'm trying to implement a query with start_time and end_time arguments, which the user can input their own dates into for customized results.
From taking bits and pieces from other examples online, I've come up with something that more or less runs, however I'm getting an error "query has no destination for result data".
I'm simply trying to return the data from the select statement.
Is there something trivial I'm missing here?
do $$
DECLARE
    start_date timestamp := '2020-10-1';
    end_date timestamp := '2020-10-5';
begin

select distinct on (account.id, menu.name, kitchen_item.name)
...
query stuff
....
where orders.placed BETWEEN start_date AND end_date
order by account.id asc, menu.name, kitchen_item.name asc, order_item.created desc;

end$$

EDIT -
This is the final result I've worked out which appears to work for this purpose utilizing temp tables.
DO $$
DECLARE

--Specify start date - end date in YYYY-MM-DD format.
start_date timestamp := '2020-10-1';
end_date timestamp := '2020-10-5';

BEGIN
    CREATE TEMP TABLE temp_output ON COMMIT DROP AS
    select distinct on (account.id, menu.name, kitchen_item.name)
    account.id as "Account ID",
    account.firstname as "Seller First Name",
    account.lastname as "Seller Last Name",
    account.email as "Seller Email",
    account.phone as "Seller Phone",
    address.address as "Seller Address (Street)",
    address.address_2 as "Seller Address 2",
    account.zip_code as "Seller Zip",
    address.neighborhood as "Seller Neighborhood",
    menu.name as "Name of active menu",
    kitchen_item.name as "Dishes",
    kitchen_item.price as "Price",
    kitchen_item.daily_max_orders as "Quantity",
    menu.pickup_start_time as "Start time",
    menu.pickup_end_time as "End time",
    menu.repeat_mon as "Monday",
    menu.repeat_tues as "Tuesday",
    menu.repeat_wed as "Wednesday",
    menu.repeat_thurs as "Thursday",
    menu.repeat_fri as "Friday",
    menu.repeat_sat as "Saturday",
    menu.repeat_sun as "Sunday",
    order_item.created as "Date of last sale"
    from account
    left join store on account.id = store.account_id
    left join menu on store.id = menu.store_id
    left join menu_item on menu.id = menu_item.menu_id
    left join kitchen_item on (menu_item.kitchen_item_id = kitchen_item.id and store.id = kitchen_item.store_id)
    left join orders on (orders.store_id = store.id)
    left join order_item on (order_item.order_id = orders.id)
    join store_address on store.id = store_address.store_id
    join address on store_address.address_id = address.id
    where orders.placed >= start_date AND orders.placed <= end_date
    order by account.id asc, menu.name, kitchen_item.name asc, order_item.created desc;
END $$;

SELECT * FROM temp_output;

This is what my final solution looked like, and worked for me.
DO $$
DECLARE

--Specify start date - end date in YYYY-MM-DD format.
start_date timestamp := '2020-10-1';
end_date timestamp := '2020-10-5';

BEGIN
    CREATE TEMP TABLE temp_output ON COMMIT DROP AS
    select distinct on (account.id, menu.name, kitchen_item.name)
    account.id as "Account ID",
    account.firstname as "Seller First Name",
    account.lastname as "Seller Last Name",
    account.email as "Seller Email",
    account.phone as "Seller Phone",
    address.address as "Seller Address (Street)",
    address.address_2 as "Seller Address 2",
    account.zip_code as "Seller Zip",
    address.neighborhood as "Seller Neighborhood",
    menu.name as "Name of active menu",
    kitchen_item.name as "Dishes",
    kitchen_item.price as "Price",
    kitchen_item.daily_max_orders as "Quantity",
    menu.pickup_start_time as "Start time",
    menu.pickup_end_time as "End time",
    menu.repeat_mon as "Monday",
    menu.repeat_tues as "Tuesday",
    menu.repeat_wed as "Wednesday",
    menu.repeat_thurs as "Thursday",
    menu.repeat_fri as "Friday",
    menu.repeat_sat as "Saturday",
    menu.repeat_sun as "Sunday",
    order_item.created as "Date of last sale"
    from account
    left join store on account.id = store.account_id
    left join menu on store.id = menu.store_id
    left join menu_item on menu.id = menu_item.menu_id
    left join kitchen_item on (menu_item.kitchen_item_id = kitchen_item.id and store.id = kitchen_item.store_id)
    left join orders on (orders.store_id = store.id)
    left join order_item on (order_item.order_id = orders.id)
    join store_address on store.id = store_address.store_id
    join address on store_address.address_id = address.id
    where orders.placed >= start_date AND orders.placed <= end_date
    order by account.id asc, menu.name, kitchen_item.name asc, order_item.created desc;
END $$;

SELECT * FROM temp_output;


Combining multiple rows into a single row with multiple columns I am trying to do a manual query for WordPress postmeta table which would combine two types of postmeta into one with multiple columns by post_id.

This is the structure of table:

--------------------------------------------------------
meta_id | post_id | meta_key                | meta_value
1       | 100     | _wp_attachment_metadata | a:6:{s:5:"width"}
2       | 100     | _thumbnail_id           | 23
`

This is the result I am trying to achieve:

--------------------------------------------------------
post_id | _wp_attachment_metadata | _thumbnail_id
100     | a:6:{s:5:"width"}       | 23

SELECT t1.post_id, t1.meta_value, t2.meta_value
FROM postmeta t1, postmeta t2
WHERE t1.post_id = t2.post_id
  AND t1.meta_key = '_wp_attachment_metadata'
  AND t2.meta_key = '_thumbnail_id'

or

SELECT post_id,
       MAX(CASE WHEN meta_key = '_wp_attachment_metadata'
                THEN meta_value END),
       MAX(CASE WHEN meta_key = '_thumbnail_id'
                THEN meta_value END)
FROM postmeta
GROUP BY post_id

PS. First query skips post_id, if it have no one (any) of meta-keys, second query gives NULL for according output field in such case.


MySQL on MACOS Sierra secure-file-priv setting I cannot find where to set secure-file-priv. I get this message:

  1290 - The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

when running this statement:

SELECT COLUMN_NAME, data_type FROM tabledefs INTO  OUTFILE  'xpeter'

If I remove OUTFILE 'xpeter'  it works.  OUTFILE '/tmp/xpeter does not work either.

I tried to add --secure-file-priv="/tmp" , no change. Variable stays as NULL:

 $bindir/mysqld_safe --datadir="$datadir" --pid-file="$mysqld_pid_file_path" --secure-file-priv="/tmp"    $other_args >/dev/null &

/usr/local/mysql/bin/mysqld --verbose --help | grep secure-file
  --secure-file-priv=name
secure-file-priv                                             NULL

There is no my.cnf file.  I run through whole filesystem, looking for any *.cnf file.  Could not find any other than: ./mysql-5.7.17-macos10.12-x86_64/data/auto.cnf and ./mysql-5.7.17-macos10.12-x86_64/support-files/my-default.cnf.  The line $bindir/mysqld...  is in support-files/my-default.cnf.

I rerun the query without OUTFILE 'xpeter',  query OK, no messages in log files except apache2 log.  Then I run

SELECT COLUMN_NAME, data_type INTO  OUTFILE  'xpeter' FROM tabledefs

no errors in  logfiles, only in phpmyadmin (where I run the query statement from, logged in as root): same message as before.

I tried the etc/my.cnf and also:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE plist PUBLIC "-//Apple Computer//DTD PLIST 1.0//EN" "http://www.apple.com/DTDs/PropertyList-1.0.dtd">
<plist version="1.0">
<dict>
      <key>ProgramArguments</key>
        <array>
   <string>--secure-file-priv=/mysql_exp</string>
        </array>
    <key>WorkingDirectory</key>  <string>/usr/local/mysql</string>
</dict>
</plist>

In both cases after reboot I was still not allowed to write a file.

I did not change the folder owner. If I add --secure-file-priv=/mysql_exp to Daemons and reboot MySQL does not run at all.

on mac os x startup parameters of mysql You can change by edit .plist file

location:

/Library/LaunchDaemons
com.oracle.oss.mysql.mysqld.plist

Use Xcode or for example BBEdit, and add line in section ProgramArguments

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE plist PUBLIC "-//Apple//DTD PLIST 1.0//EN" "http://www.apple.com/DTDs/PropertyList-1.0.dtd">
<plist version="1.0">
<dict>
    <key>Disabled</key>
    <false/>
    <key>ExitTimeOut</key>
    <integer>600</integer>
    <key>GroupName</key>
    <string>_mysql</string>
    <key>KeepAlive</key>
    <false/>
    <key>Label</key>
    <string>com.oracle.oss.mysql.mysqld</string>
    <key>LaunchOnlyOnce</key>
    <false/>
    <key>ProcessType</key>
    <string>Interactive</string>
    <key>Program</key>
    <string>/usr/local/mysql/bin/mysqld</string>
    <key>ProgramArguments</key>
    <array>
        <string>/usr/local/mysql/bin/mysqld</string>
        <string>--user=_mysql</string>
        <string>--basedir=/usr/local/mysql</string>
        <string>--datadir=/usr/local/mysql/data</string>
        <string>--plugin-dir=/usr/local/mysql/lib/plugin</string>
        <string>--log-error=/usr/local/mysql/data/mysqld.local.err</string>
        <string>--pid-file=/usr/local/mysql/data/mysqld.local.pid</string>
        <string>--secure-file-priv=/mysql_exp</string>
    </array>
    <key>RunAtLoad</key>
    <false/>
    <key>SessionCreate</key>
    <true/>
    <key>UserName</key>
    <string>_mysql</string>
    <key>WorkingDirectory</key>
    <string>/usr/local/mysql</string>
</dict>
</plist>

restart MySQL and check:

mysql> show variables like 'secure%';
+------------------+-------------+
| Variable_name    | Value       |
+------------------+-------------+
| secure_auth      | ON          |
| secure_file_priv | /mysql_exp/ |
+------------------+-------------+
2 rows in set (0.00 sec)

Add:

You can achieve same result if will create my.cnf file straight in /etc/

sudo nano /etc/my.cnf

You can copy example file from

/usr/local/mysql/support-files/

just notes about comments - "copy/past" it's not a way, even on Your Mac You really do not need file import/export at all
just use normal tools like:

MySQL Workbench
Navicat for MySQL
JetBrain IDE (most of them have database module)
DBVisualizer

any of them can help You:

export result of query into different format - csv, delimited file, json, xls
Import popular format of files direct into table, with mapping columns to datatypes

manual file level import/export could be useful in production environment where You need automate processes, but also for now it is more backup way, primary and proper - ETL scripts and tools.


Identify Duplicates in MySQL across columns I have a table with name, contact1, contact2 and contact3 columns. Is there a way to find out of a given number exists in either of the 3 contact columns?

I am looking identify if a number exists more than once across contact1, contact2 and contact3 columns.

Please help.

You want a query like this one:

SELECT contact_id, contact1, contact2, contact3
FROM contact
WHERE
(
  (contact1 = contact2)
  OR
  (contact2 = contact3)
  OR
  (contact1 = contact3)
);

See the db-fiddle here (see DDL and DML below).

Result:

contact_id  contact1    contact2    contact3
____________________________________________
         3   phone31     phone33     phone33

Of course, if MySQL had CHECK CONSTRAINTs, this problem could be avoided at the DDL level! From here, you have this gem:

  CHECK

  The CHECK clause is parsed but ignored by all storage engines.

============= DDL and DML ============

CREATE TABLE contact
(
  contact_id INTEGER,
  contact1 VARCHAR(20),
  contact2 VARCHAR(20),
  contact3 VARCHAR(20)
);

INSERT INTO contact
VALUES
(1, 'phone11', 'phone12', 'phone13'),
(2, 'phone21', 'phone22', 'phone23'),
(3, 'phone31', 'phone33', 'phone33'), -- << Duplicate number!
(4, 'phone41', 'phone42', 'phone43'),
(5, 'phone51', 'phone52', 'phone53');


How to speed up mysql export and import I need to perform a DB migration from Server A to Server B.

Server A: MySQL version installed 5.0.6
Server B: MySQL version installed 5.6.20
OS: Both are running RedHat Linux 64bit. 16 RAM memory.

There are MyISAM, InnoDB, Archive databases.

Total size about 4TB.
Largest MyISAM database = 3.2TB
Largest InnoDB database = 85GB

key_buffer_size=2GB.
I took a mysqldump on the largest InnoDB table (85GB)
 time /usr/local/mysql/bin/mysqldump -uroot -p --extended-insert --single-transaction ABCEx > ABCEx.sql
After running for 5 hours, the dumpfile has grown to 145GB and yet to finish.
-rw-rw-r--  1 mysql mysql 145019313099 Mar  2 05:12 ABCEx.sql
I am concern on the export time and also import. Any help to expedite this?
As for MyISAM tables migration, I read that I migrate them by coping the .myd .frm .myi files from Server A to B. Can provide some guide or link if any one has done before? We are talking of 3.2TB of MyISAM database.

Please help....much appreciated :)

The best way to migrate tables between servers is to do it in binary "native" format. Doing a serial logical dump (as mysqldump does) not only may take days on a very large database, but it will take even more for recovery.

If you need to maintain availability on the source server for InnoDB tables, the best way is using an utility like MySQL Enterprise Backup (part of Oracle subscription) or the open source similar-tool Percona XtraBackup. Please note that the last one will only work for Linux hosts.

If most of your data is in MyISAM/Archive format, I am afraid that you cannot guarantee availability and consistency at the same time for the performing of the backup. In that case, the best way is to perform a snapshot (your filesystem or environment -VM, LVM- must allow it). Tools like mylvmbackup simplify the process.
Worst possible scenario, you can LOCK those tables -FLUSH TABLES WITH READ LOCK- (your are already doing that for the mysqldump), or even just shutdown the whole server, and just copy the .frm, MYI and MYD files away (for MyISAM). Please note that copying InnoDB (.ibd) files away does not simply work; you need MySQL 5.6 and the LOCK TABLES ... FOR EXPORT + ALTER TABLE...DISABLE/ENABLE TABLESPACE construction. Rsync may help lowering the time MySQL is unavailable.

Please note that binary files of MySQL are almost 100% compatible between different nodes, architectures and OSs.

Last option, if you do not have access to the filesystem, is to perform a logical backup in a more optimized way and do it in a parallel fashion. Tools like mydumper simplifies that greatly, and it is very used where XtraBackup or Enterprise backup do not work. It will be slower, but 5-10 times faster that mysqldump in a good scenario.


How do i store hash indices on my database I'm storing (references to) images in a database. I want to have reverse image search, so I wish to store a multidimensional hash of the image - for example width to nearest 10px, height to nearest 10px, and perhaps a downsampled version of the image.
Originally I was going to store these hashes on disk, but then I realised I may be able to store in database in a much more organised (and perhaps performant) manner.

There are several considerations:

Consistency: if you store everything in the database, consistency between database entries and the hash are automatically guaranteed by the transaction system. That simplifies architecture and development.

Read/Write Performance: reading bigger data from the database performs much worse than reading files from the file system.

Backup/Maintenance: large databases are more difficult to back up than large file systems.

Search performance: unless you build special GiST index support for operators on these hashes (which requires C programming and dealing with the guts of PostgreSQL), you won't be able to efficiently search for the hashes in the database.


Not able to access stored procedure from my application I have written few stored procedures and executed in my server that is having  php myadmin  now while calling this stored procedure from my application  it is showing
following exception

SQLException1 java.sql.SQLException: User does not have access to metadata required to
determine stored procedure parameter types. If rights can not be granted, configure
connection with "noAccessToProcedureBodies=true" to have driver generate parameters that
represent INOUT strings irregardless of actual parameter types.

While searching i find following query to resolve this exception:

GRANT SELECT,INSERT,UPDATE ON mysql.proc TO 'user'@'localhost';

my problem is that while running this query i am getting following error

#1142 - SELECT,INSERT,UP command denied to user 'qqq'@'localhost' for table 'proc'

but i have given all permission to user

 GRANT ALL ON *.* TO 'qqq'@'localhost';

So how to access stored procedure from my application
I am using shared hosting server .

The error you provided is generated by your driver. See this question, which points you to a blog entry giving an answer. To sum it up, you have two options:

The GRANT statement you tried
adding noAccessToProcedureBodies=true to your database connection string (which is also suggested by the error message)

Since you are using a shared hosting server it is likely that you don't have privileges on mysql.proc and you don't have root access to the server, so you can't GRANT them. Therefore you should try the second approach.


How to differentiate between SQL and PL/SQL? I know the question might sound too stupid, but I never understood this part.

  SQL*Plus works with both SQL and
  PL/SQL. How do I know whether some
  code is SQL or PL/SQL? If my code has
  a for loop, is it not SQL anymore?

  PL/SQL is an extension for SQL to have
  loops, conditionals etc. Then any SQL
  code is by default PL/SQL code? Isn't
  it so?

Is there a demarcation between SQL and PL/SQL?

Two examples of differentiating b/w SQL and PL/SQL that triggerred this question:

What is the difference between these two create table statements?

https://stackoverflow.com/questions/2267386/oracle-11g-varray-of-objects/2267813#2267813

If it is wrapped in

BEGIN ... END
DECLARE ... END
CREATE OR REPLACE ... END
Is a one-liner prefixed EXECUTE

Then it is PL/SQL. What does this mean under the hood? SQL gets "compiled" to a query plan and executed, immediately returning a result set in the event of SELECT or the number of rows affected in other cases, or an error. PL/SQL however is more involved. Every object referenced in it is checked for existence and necessary grants, then the PL/SQL is compiled down to near-native code, which executes at full speed. The dependencies are tracked and if a table changes, then any PL/SQL referencing it requires recompilation. The reason for this is speed; since everything is done up front at compile time, there is no need to have any runtime checks in PL/SQL, whereas SQL statements must be checked every time (even tho' query plans are cached, the so-called soft parse, privileges must still be checked, and even a soft parse has a cost associated with it).

This is one of the "killer advantages" of stored procedures as Oracle does them; actually executing a PL/SQL stored proc or trigger does the absolute minimum amount of computation to get the result. Application code running outside of the Oracle kernel where it is untrusted has to jump through more hoops in order to get at the data. It's the same argument for strongly-typed, referentially transparent high-level languages such as OCaml or Haskell - do more work once, at compile time, and reap the benefits on the millions of time the code is executed.


How to create a secondary read-only reporting database with < 15 minute latency We have a 2012 standard edition instance that is doing log-shipping of 2 related databases every 15 minutes to a secondary 2012 standard edition instance for DR purposes. Although it provides read-only access, users are automatically disconnected every 15 minutes when the restore job runs (it takes up to 2 minutes to complete the restores).

Without interfering with that process, we would like to set up a secondary instance for reporting and developer troubleshooting purposes that would also be kept up to date at least every 15 minutes (the reports need near real-time data), but would not involve periodic disconnections, enable different security than the primary (so that devs can read from the secondary but not the primary), and preferably allow  indexes to be created that do not exist on the primary (to improve reporting performance).

1) How can we achieve this?
2) How can we achieve this without having to upgrade to Enterprise Edition?

Transactional Replication is typically used to off-load reporting to another server/instance and can be near real-time in a best case scenario. The benefit of Transactional Replication is that you can place different indexes on the subscriber(s) to optimize reporting. You can also choose to replicate only a portion of the data if only a subset is needed for reporting.

With Transactional Replication you will need Standard Edition or higher for the Publisher and Subscribers can be Express Edition or higher.

Have a look at Transactional Replication to get started.

If you have any questions, let me know. I hope this helps.


Need to backup remote database using mysqldump I tried to backup the database of a remote server using automysqlbackup.

From this platform, I learnt it was not possible without ssh and I need to back it up on a remote machine and then rsync or scp to my local machine, so I am now working with mysqldump.

The size of the database is almost 12GB.

mysqldump -h 192.168.10.209 -u user -ppassword testdb   > dump.sql

I tried backing it up and due to internet issues I have been disconnected twice already., and got this error once:

mysqldump: Couldn't execute 'SELECT /*!40001 SQL_NO_CACHE */ * FROM `helmet`': Lost connection to MySQL server during query (2013)

Is there way to continue from where it got disconnected using msyqldump?

I also read that mysqldump is not good for such a big database backup but I need to test it from here only at this moment.

Are there any options that can be used to make it work smoothly and backup my database?

Unfortunately, you cant continue your failed backup. When mysqldump returns a failed status you have to start over again.

My first recommendation is to compress your script before transmitting it

mysqldump -h 192.168.10.209 -u user -ppassword testdb | gzip > dump.sql.gz

Another recommendation is to connect to the remote server via ssh, then make the backup in the remote server (never will fail for network issues because is a local backup) and, in the last step you could transmit the file (after compress of course).

Other than that, the other 2 options more used are PERCONA XTRA BACKUP and MYSQL ENTERPRISE BACKUP but there are also high prices involved.


Renaming the Data Sets in SSRS What is the easier way to rename the Data Sets in completed SSRS report?
I have created a report in BIDS 2008 and now for some reasons I have to rename the Data Set names, but it seems that renaming the Data Sets in the Report Data pane, does not update the references wherever the old DataSet name has been used.

For example I have DataSet1 and want rename it to Abc. Do I have to go through all text boxes and expressions that have used the old name and rename them to the new Data set name manually? Or there is a short cut to keep them in sync.

Any idea?

Thanks.

The correct answer would be to say yes - however, a quick win is to close your solution, then open the .rdl file up with notepad and do a find-and-replace. Then save your .rdl file and re-open your solution - you should find all of your references within the report have been updated. Remember that the .rdl file is essentially an xml file, open it up and have a look around - it will help you understand the inner workings better. You will of course need to update the data source yourself though.

I hope this helps you.


How to know my DB user? I use the PL-SQL developer tool. I  log in by using Prodcollops as user name to access the database. How can I find out if  my DB user is Prodcollops, too,  or if  it is just a login name?

As  @a_horse_with_no_name stated in a comment, there is a function USER that returns the user of a session, so

select USER from DUAL

is a way to find out the user of a session.