Is it possible to get SQL Server 2008 to download a file from a URL I have a set of XML based services running on my network.

The data in these services needs to be mirrored into a Table on an SQL Server 2008 instance.

Getting the XML into a table is no problem I can already do that, but what I'm having to do at the moment is pass a huge long string of XML to a stored proc which then uses the OPENXML command to insert it into the table.

What I'm wanting to know, is instead of using a 3rd party program to get this data from the service then call the stored proc and insert it, is it in anyway possible for me to get SQL Server to just grab the XML directly from the service URL and process it like that.

All I can seem to find no matter how much I try is plenty of articles & posts on reading / writing files to / from the server file system and the several million or so on actually inserting the XML data, but I can't seem to find anything on getting the server to grab data directly from a URL.

Cheers

-----===== Update 25/8/2012 =====-----

After a little bit more research Iv'e found a third way to do this:

declare @xmlObject as int
declare @responseText as varchar(max)
declare @url as varchar(2048)

select @url = 'http://server/feed/data'

exec sp_OACreate 'MSXML2.XMLHTTP', @xmlObject OUT;
exec sp_OAMethod @xmlObject, 'open', NULL, 'get', @url, 'false'
exec sp_OAMethod @xmlObject, 'send'
exec sp_OAMethod @xmlObject, 'responsetext', @responseText OUTPUT
exec sp_OADestroy @xmlObject

select @responseText

exec sp_xml_preparedocument @idoc OUTPUT, @xmlData

Which seems to work, and work quite efficiently, however here's the weird thing.

If I use the full URL, which returns pure XML data with a mime type of 'text/xml' then response text contains nothing, it's null but if I strip it back to say 'http://server/feed/' or 'http://server/' so that the web server is just passing a 404 page or default html page, then I get the actual page content in.

At first I thought it might be the mime type,  'text/xml' vs 'text/html' but testing that made no difference, anything that returns valid XML seems to give null, but anything that returns broken XML seems to work!!

The code above however does work with correctly formatted XML from the internet, for example 'geonames' (which is what the original source of the above code was based on) works fine.

I suspect, that it's something to do with my feed server's config however, so need to do some work to resolve this, I thought I'd add the code here for others.

@Remus thank's for the suggestion, but that's how I currently do the task, I have a CLR binary that I wrote, that runs once a day to sync the feed to the DB, but it takes too long to sync the data.  Using it to feed the XML is faster than doing a pure loop however, but the size of the XML can be quite variable (esp given that a binary would pass it to the stored proc using L2S) and Iv'e already overflowed the input to the SP a couple of times because there's been too much data, hence why I'm looking to get the SP to retrieve the data itself.

@Mr Browstone - SSIS seems to be the way to go, that's what other suggestions have been, I do however admit I don't know an awful lot about using SSIS other than to use the (Import / Export wizard - I'm more of a dev than a DBA :-)  ) so any pointers on how I'd achieve this using SSIS would be helpful.

As for using CLR procs in the DB, I had thought of trying that, but Iv'e been bitten badly using these things before (And crashed a couple of servers too!!) so I'm a little wary of using them.

Use an external process that does the HTTP work and the inserts into the database. I explicitly advise against using SQLCLR for this. Hijacking precious SQL Server workers for the boring job of waiting for HTTP results will one day impact your server severely.

  but the size of the XML can be quite variable (esp given that a binary
  would pass it to the stored proc using L2S) and Iv'e already
  overflowed the input to the SP a couple of times because there's been
  too much data

Use the techniques from DOWNLOAD AND UPLOAD IMAGES FROM SQL SERVER VIA ASP.NET MVC to stream the HTTP response into the database.


How save psql `ERROR` output to the same file as the DDL commands? I'm trying to restore a database from a text file. I'm following the answer on this question. How do I restore a plain text postgres .backup file

But add: > c:\result.txt

psql.exe -U  sde sde < c:\backup\sde_04_06_2018.backup > c:\backup\result.txt

This seem to work but doesn't write the errors.

You get messages like this on the text file:

REVOKE
GRANT
SET
ALTER DEFAULT PRIVILEGES
ALTER DEFAULT PRIVILEGES

But on the command windows you see error like this:

ERROR:  constraint "sde_layer_stats_version_id_fkey" for relation sde_layer_stats" already exists
ERROR:  constraint "xml_columns_fk1" for relation "sde_xml_columns" already exists
ERROR:  constraint "xml_columns_fk2" for relation "sde_xml_columns" already exists
ERROR:  constraint "xml_indextags_fk1" for relation "sde_xml_index_tags" already exists

The idea is save the error on the text file so I can review and decide if are something I need to handle.

I want help with how write the command line so I can save the output of the restore to a file.txt. So the create/insert/alter on the backup aren't relevant. And the image is just as reference, I also include the message as text just in case.

If you're asking how do you put the error to the "result file" too, you need to redirect STDERR as well as STDOUT or direct STDERR to STDOUT with 2>&1

psql.exe -U  sde sde < c:\backup\sde_04_06_2018.backup > c:\backup\result.txt 2>&1

Internally you have three "standard" streams: input, output, and error.

input you're directing that to c:\backup\sde_04_06_2018.backup
output you're directing output to c:\backup\result.txt
You need to also redirect error.


How to use GROUP BY after the Having clause Mysql Here is the query which gets the accounts for a specific user the products are related to accounts in one-to-many relation so for each product there are four permissions

All the accounts which is not Disable and also shows their permissions
I got all accounts according to scenario but the problem is if one account has more than one products then it obviously shows account id more than one time

*What i am looking for to GROUP BY the a.id after the HAVING clause which checks the permissions * but no luck getting syntax error

Error Code: 1064 right syntax to use near 'GROUP BY a.`id` LIMIT 0, 1000' at line 14

Here is my query

SELECT  a.`id` AS aid,ap.`pid`, p.`products_name`, a.accounts_account_number, c.clients_name,a.accounts_product_type AS pptype,
c.`clients_last_name`, a.`accounts_account_name`
,(SELECT `status` FROM `bf_account_permissions` WHERE `permission`='Readonly' AND uid=2 AND aid=a.id AND pid=ap.`pid`) AS Readonly,
(SELECT `status` FROM `bf_account_permissions` WHERE `permission`='Buy' AND uid=2 AND aid=a.id AND pid=ap.`pid`) AS Buy,
(SELECT `status` FROM `bf_account_permissions` WHERE `permission`='Sell' AND uid=2 AND aid=a.id AND pid=ap.`pid`) AS Sell,
(SELECT `status` FROM `bf_account_permissions` WHERE `permission`='Disable' AND uid=2 AND aid=a.id AND pid=ap.`pid`) AS `Disable`
 FROM `bf_clients` c INNER JOIN `bf_user_clients` uc ON (c.`id` = uc.`client_id`)
 INNER JOIN `bf_accounts` a ON (c.`id` = a.`accounts_clients`)
 INNER JOIN `bf_account_products` ap ON (a.`id` = ap.`aid`)
 INNER JOIN `bf_products` p ON (ap.`pid` = p.`id`)
 WHERE uc.`agt_user_id`=2 AND a.deleted=0 AND c.deleted=0
 HAVING (Readonly !='' OR Buy !=''  OR Sell !=''  OR `Disable` !='' )
 AND `Disable` !='1'   ORDER BY a.`id`  GROUP BY a.`id`

Any help would be appreciated

Move the GROUP BY to between the WHERE and the HAVING.

SELECT
    a.`id` AS aid,
    ap.`pid`,
    p.`products_name`,
    a.accounts_account_number,
    c.clients_name,
    a.accounts_product_type AS pptype,
    c.`clients_last_name`,
    a.`accounts_account_name`,
        (SELECT `status` FROM `bf_account_permissions` WHERE `permission`='Readonly' AND uid=2 AND aid=a.id AND pid=ap.`pid`) AS Readonly,
        (SELECT `status` FROM `bf_account_permissions` WHERE `permission`='Buy' AND uid=2 AND aid=a.id AND pid=ap.`pid`) AS Buy,
        (SELECT `status` FROM `bf_account_permissions` WHERE `permission`='Sell' AND uid=2 AND aid=a.id AND pid=ap.`pid`) AS Sell,
        (SELECT `status` FROM `bf_account_permissions` WHERE `permission`='Disable' AND uid=2 AND aid=a.id AND pid=ap.`pid`) AS `Disable`
FROM `bf_clients` c
    INNER JOIN `bf_user_clients` uc ON (c.`id` = uc.`client_id`)
    INNER JOIN `bf_accounts` a ON (c.`id` = a.`accounts_clients`)
    INNER JOIN `bf_account_products` ap ON (a.`id` = ap.`aid`)
    INNER JOIN `bf_products` p ON (ap.`pid` = p.`id`)
WHERE uc.`agt_user_id`=2 AND a.deleted=0 AND c.deleted=0
GROUP BY a.`id`
HAVING (Readonly !='' OR Buy !=''  OR Sell !=''  OR `Disable` !='' )
    AND `Disable` !='1'
ORDER BY a.`id`

Also, move anything that needs to be checked BEFORE the GROUP BY, into the WHERE

Since you are using the HAVING to filter on an aliased subquery, you have should either (option A) add the whole subqueries into the where clause OR (option B?) why don't you just join the table used in the subquery?

Option A Example WHERE clause:

WHERE uc.`agt_user_id`=2 AND a.deleted=0 AND c.deleted=0
    AND (
        (SELECT `status` FROM `bf_account_permissions` WHERE `permission`='Readonly' AND uid=2 AND aid=a.id AND pid=ap.`pid`) != '' OR
        (SELECT `status` FROM `bf_account_permissions` WHERE `permission`='Sell' AND uid=2 AND aid=a.id AND pid=ap.`pid`) != '' OR
        (SELECT `status` FROM `bf_account_permissions` WHERE `permission`='Disable' AND uid=2 AND aid=a.id AND pid=ap.`pid`)  != ''
        )
    and (SELECT `status` FROM `bf_account_permissions` WHERE `permission`='Disable' AND uid=2 AND aid=a.id AND pid=ap.`pid`)  != '1'
GROUP BY a.`id`
-- REMOVED THE HAVING
ORDER BY a.`id`

Option B Revised with a LEFT JOIN:

NOTE: This option below has just been roughly put together without any tables / data, so may need some adjustment.  Further suggestion to improve this are welcome!

SELECT
    a.`id` AS aid,
    ap.`pid`,
    p.`products_name`,
    a.accounts_account_number,
    c.clients_name,
    a.accounts_product_type AS pptype,
    c.`clients_last_name`,
    a.`accounts_account_name`,
    case when perms.`permission` = 'Readonly' then perms.status end as ReadOnly,
    case when perms.`permission` = 'Buy' then perms.status end as Buy,
    case when perms.`permission` = 'Sell' then perms.status end as Sell,
    case when perms.`permission` = 'Disable' then perms.status end as `Disable`
FROM `bf_clients` c
    INNER JOIN `bf_user_clients` uc ON (c.`id` = uc.`client_id`)
    INNER JOIN `bf_accounts` a ON (c.`id` = a.`accounts_clients`)
    INNER JOIN `bf_account_products` ap ON (a.`id` = ap.`aid`)
    INNER JOIN `bf_products` p ON (ap.`pid` = p.`id`)
    LEFT JOIN `bf_account_permissions` perms on (a.id = perms.id)
        and (perms.pid = ap.`pid`)
        and (perms.uid=2)
WHERE uc.`agt_user_id`=2 AND a.deleted=0 AND c.deleted=0
    and (perms.status !='' or (perms.`permission` = 'Disable' and perms.status !='1'))
GROUP BY a.`id`
ORDER BY a.`id`


How do I access Postgres when I get an error about "/var/run/postgresql/.s.PGSQL.5432"? I am running Ubuntu 16.  I have installed Postgresql.  Postgresql used to work, but then I rebooted.

nmap commands show port 5432 is open.  Postgres seems to be working correctly:

service postgresql status

  postgresql.service - PostgreSQL RDBMS    Loaded: loaded
  (/lib/systemd/system/postgresql.service; enabled; vendor preset:
  enabled)    Active: active (exited) since Sat 2017-07-29 18:42:59 EDT;
  1min 4s ago   Process: 201 ExecStart=/bin/true (code=exited,
  status=0/SUCCESS)  Main PID: 201 (code=exited, status=0/SUCCESS)
  Memory: 0B    CGroup: /system.slice/postgresql.service

I ran this: psql

But I got this:

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

The file listed above does not seem to exist.

How do I get into Postgresql?  Normally I'd run psql or sudo -i -u postgres then psql.  But these commands are not working.  I keep getting an error about "could not connect to server."  Several reboots have not helped.

Update:

I ran this command: dpkg -l | grep postgres

rc postgresql-9.5                9.5.6-0ubuntu0.16.04                   amd64        object-relational SQL database, version 9.5 server
ii postgresql-client             9.5+173                                all          front-end programs for PostgreSQL (supported version)
ii postgresql-client-9.5         9.5.7-0ubuntu0.16.04                   amd64        front-end programs for PostgreSQL 9.5
ii postgresql-client-common      173                                    all          manager for multiple PostgreSQL client versions
ii postgresql-common             173                                    all          PostgreSQL database-cluster manager

If your Postgres service is up and running without any error or there is no error in starting the Postgres service and still you are getting the mentioned error, follow these steps

Step1: Running pg_lsclusters will list all the postgres clusters running on your device

eg:

Ver Cluster Port Status Owner    Data directory               Log file
9.6 main    5432 online postgres /var/lib/postgresql/9.6/main /var/log/postgresql/postgresql-9.6-main.log

most probably the status will be down in your case . Try restarting Postgres clusters and service

Step 2: Restart the pg_ctlcluster

#format is pg_ctlcluster <version> <cluster> <action>
sudo pg_ctlcluster 9.6 main start

#restart postgresql service
sudo service postgresql restart

Step 3: Step 2 failed and threw an error

If this process is not successfull it will throw the error.
My error was(You can see the error log on /var/log/postgresql/postgresql-9.6-main.log)

FATAL: could not access private key file "/etc/ssl/private/ssl-cert-snakeoil.key": Permission denied
Try adding `postgres` user to the group `ssl-cert`

Step 4: check ownership of postgres

Make sure that postgres is the owner of /var/lib/postgresql/version_no/main
eg: sudo chown postgres -R /var/lib/postgresql/9.6/main/

Step 5: Check Postgres user belongs to ssl-cert user group

It happened to me and it turned out that I removed erroneously the Postgres user from "ssl-cert" group. Run the below code to fix the user group issue and fixing the permissions

#set user to group back with
sudo gpasswd -a postgres ssl-cert

# Fixed ownership and mode
sudo chown root:ssl-cert  /etc/ssl/private/ssl-cert-snakeoil.key
sudo chmod 740 /etc/ssl/private/ssl-cert-snakeoil.key

sudo service postgresql restart


Selecting the average of the top percent of duration, grouped by minute I am attempting to find the average of (the top 5% of duration/ per minute).

I wrote this query:

select AVG(Q.duration) Avg5Perc, Q.time FROM
(

          select top 5 PERCENT duration, time from table (nolock)
          WHERE time between '2015-06-5 13:00' and '2015-06-5 13:59.99'
) Q
group by datepart(minute, Q.time), Q.table
ORDER BY Q.table desc;

Basically, in the sub-select I am acquiring the top 5 percent of values, and in the outer select I am averaging them. However, when I run the query, I do not actually have it grouped by minute.

Any idea? Thanks

Start by working out the duration per minute:

select sum(duration) as dpm,  datediff(minute, '2015-06-5 13:00', time) as mins_since_start
from table
where time >= '2015-06-5 13:00'
and time < '2015-06-5 14:00'
group by datediff(minute, '2015-06-5 13:00', time)

Now get the top 5 percent of this.

select top (5) percent sum(duration) as dpm,  datediff(minute, '2015-06-5 13:00', time) as mins_since_start
from table
where time >= '2015-06-5 13:00'
and time < '2015-06-5 14:00'
group by datediff(minute, '2015-06-5 13:00', time)
order by dpm desc

Then wrap it up and get the

select AVG(Q.dpm) Avg5Perc
FROM
(select top (5) percent sum(duration) as dpm,  datediff(minute, '2015-06-5 13:00', time) as mins_since_start
from table
where time >= '2015-06-5 13:00'
and time < '2015-06-5 14:00'
group by datediff(minute, '2015-06-5 13:00', time)
order by dpm desc
) Q;

This then gives you the average of the top five percent. I prefer to use datediff like this because it means you're not restricted to a single hour.


MySQL Join is the same as Cross Join? I'm learning mysql.  Under all cases, is Join the same as Cross Join?  For example, this query

SELECT * FROM table t1
JOIN table t2

Will always be the same as

SELECT * FROM table t1
CROSS JOIN table t2

Even if I add WHERE, GROUP BY, HAVING, LIMIT, ORDER etc.... conditions afterwards, JOIN and CROSS JOIN will always yield the same results?

Thanks

INNER, OUTER, and CROSS are ignored by MySQL.  The ON and WHERE control which type of JOIN it is.

In your examples you have nothing tying the tables together, hence operates like a CROSS JOIN.

GROUP BY, etc have no impact.


How to get a unique highscore per user I have the following tables: users, highscores

I would like to get the top ten best results in highscore. The scores should be unique, meaning one score per user. I tried using INNER JOIN and GROUP but this is not returning the correct data. I get the correct uid, score, alias and email but the time is not correct. It seems that it is taken from another highscore post. How do I get the correct data? As soon as we find the "best score" I want everything else (time) from the exact same post.

SELECT UID, ALIAS, EMAIL, SCORE, TIME
FROM users t
INNER JOIN (SELECT user_uid, MAX(score) as SCORE, value1 as TIME
            FROM highscores
            GROUP BY user_uid) x
  ON x.user_uid = t.uid
ORDER BY SCORE DESC
LIMIT 0, 10

Pull the time from the highscores table separately
MySQL doesn't respect ANSI standard aggregations by default: hence your dodgy data

SELECT
    u.UID, u.ALIAS, u.EMAIL, h.SCORE, h.value1 AS TIME
FROM
   (
    SELECT user_uid, MAX(score) as SCORE
    FROM highscores
    GROUP BY user_uid
   ) x
   JOIN
   highscores h ON x.user_uid = h.user_uid AND x.SCORE = h.SCORE
   JOIN
   users u ON h.user_uid = u.uid
ORDER BY h.SCORE DESC
LIMIT 0, 10

This assumes that each user does not have multiple high scores with the same value.
If you do, you need a 2nd derived table with an aggregate to nest the x/h JOIN


In SSAS Parent-Child-Hierarchy how to use MembersWithDataCaption I have a parent-child-hierarchy with MemberWithData=NonLeadDataVisible. I tried different values for MembersWithDataCaption, but I always get name of datamember equal to name of inner node. I am using SSAS 12.0.5538.0.

Questions:

Where to set MemberWithDataCaption, Parent or Key?
MemberWithDataCaption is a formatstring, how does it work? For
simplicity I set the value "parent" without effect (after deploy and
process).
What are the conditions for MemberWithDataCaption to actually work?

My problem was I used a translation (german) and left the translation for MemberWithDataCaption empty. An other pitfall is a formatstring without a '*' does not work.

Answers:

Set MemberWithDataCaption of the Parent and any translations
In the formatstring the first star is replaced with the original
name, succeeding star will not be replaced.

Examples:

formatstring   original name   resulting caption
------------------------------------------------
constant       Charly          Charly
* (dm)         Charly          Charly (dm)
(*) *          Charly          (Charly) *

The conditions are:

Set MemberWithData=NonLeafDataVisible
Use a Formatstring containing a '*'
If you use translations, provide a translated formatstring


How to make variable update for each instance of for-each loop? I have a for-each loop that goes through a file directory. These files are transaction information from different clients. When there is an error with a file (i.e. can't be processed), the Event Handler is to send an email to the client notifying them. There is a SQL task that uses an existing variable (Client ID from the file) that queries a table to get the Client's email address. I created a variable for the Client Email. I want to make this update each time the for-each loop is run, since each file will have a different value for the client email variable.
How can I ensure this?
Inside the for-each loop, there are three data flow tasks that could conceivably "error" and that's when I will be sending these notification emails. Will I need to scope a variable for each of the three executables within the loop for the Client Email? Or can I scope a single variable in the for-each loop?
I understand this may be a bit confusing so please let me know if you have any questions.

You can create the variable either at the foreach loop container or at the global scope, by making sure that the foreach container is selected when you create the variable, or nothing is selected when you create the variable.
Inside the foreach loop container you can then add an Expression Task in order to change the value of the variable at run time.
Depending on what you need exactly, you could also use a Script Task for this purpose.
There's a similar question here with the relevant details:
https://stackoverflow.com/questions/4052864/ssis-set-variable-at-runtime


Change path to log shipping folder We are moving our back-end storage so will need to change the paths for our log shipping. Can we do this without interrupting our log shipping? i.e. Just change the paths in the 'Network path to backup folder' field. Thanks.

Think you can, of course stop all jobs making sure that all logs have been applied (restored) on the secondary to make sure it stays in sync. It sounds like the jobs you will be changing are the backup job on the primary and the copy job on the secondary.


SQL Server doesn't send intermediate SSL certificates I am connecting to SQL Server (2008 R2) over open Internet and properly encrypting the connection is essential. I have COMODO certificate with two intermediate certificates between root CA and server certificate.

Now the problem is that SQL Server responds with truncated certificate chain where only the server certificate is included and intermediate certs are skipped. The client (JDBC in this case) refuses to validate server certificate due to incomplete certificate chain. I checked that the missing chain is the problem by running the program with -Djavax.net.debug=ssl,handshake.

Server (Windows Server 2008 R2) running the SQL Server has server certificate and its complete chain in its certificate database. SQL Server account (Network Service) was granted access to the private key. There's no option to grant access to intermediate certs, so I assume those are already available to all accounts on the computer.

Workaround #1: VPN

VPN is unfortunately out of question. It's very expensive to make VPN secure, highly available, and performant. VPN proved to be an obstacle to cloud service integration. So decision was made some time ago to kill it. The legacy VPN in the company is a performance and reliability nightmare.

Workaround #2: stunnel

I tried to sidestep the issue by wrapping SQL Server with stunnel, but SQL Server apparently uses some wrapped/modified SSL in order to support both encrypted and unencrypted connections on the same port. The JDBC client expects such non-standard SSL and fails to connect through stunnel. I could proxy the connection through stunnel client (essentially introducing point-to-point VPN), but this is unwieldy especially on developer machines.

Workaround #3: Azure SQL Database

I would really love to switch to Azure SQL Database, but other stuff in the legacy datacenter has grown used to the high LAN bandwidth to the SQL Server, so moving to Azure is a long-term prospect at best. I have to find a way to access SQL Server inside the datacenter securely.

Workaround #4: CA override in Java

So the last option I am considering is to embed the intermediate certificates in source code and override Java's default CA database (truststore), which can be done on per-connection basis. But then why did I purchase certificate from public CA? I could have just as well used self-signed certificate this way.

All Microsoft documentation tells me that SQL Server should be able to use certificate issued by public CA. So how do I make it do so?

To configure server side SSL for SQL Server, you will need to place the public CA certificate and private key in the certificate store of the service account running the database engine and place the certificate without the private key in the certificate store of the java VM initiating the connection. Then use SQL Server Configuration Manager to open the connection properties and set force encryption to yes and select the certificate to use, which would be the public CA certificate, for encrypting the connection. When an SSL connection is initiated, the SQL Server will present the certificate to the java VM and the java VM will find it in its store of trusted certificates and validate it directly against the CA without the need for walking the chain of trust for intermediate certificates. Since the server is on the Internet, it wouldn't hurt to configure ipsec so all traffic is encrypted. Also, It's good that you have a certificate from a public CA because if no certificate is configured, then SQL Server will used a self signed certificate and that configuration will leave you vulnerable to a man in the middle attack.


Insert results of spBlitzIndex stored procedure into table I would like to create a weekly to monthly job to execute sp_BlitzIndex during the night and insert the results into a table so that I can consume the results.

Here's what I've tried:

INSERT INTO dbo.[IndexBlitzResults]
           ([PK_IndexBlitzID]
           ,[Priority]
           ,[Finding]
           ,[DatabaseName]
           ,[Details]
           ,[Definition]
           ,[SecretColumns]
           ,[Usage]
           ,[Size]
           ,[MoreInfo]
           ,[URL]
           ,[CreateTSQL])
exec    [ozar].[sp_BlitzIndex]
        @Mode = 0,
        @GetAllDatabases = 1,
        @BringThePain = 1

And here's the error that it throws:

  Starting run. sp_BlitzIndex(TM) v5.4 - June 03, 2017 Create temp
  tables. Adding UQ index on #IndexSanity (database_id, object_id,
  index_id)

  redacted_db Inserting data into #IndexColumns for clustered indexes
  and heaps Failure populating temp tables. Last @dsql: SET TRANSACTION
  ISOLATION LEVEL READ UNCOMMITTED;
                  SELECT 29,
                    s.name,
                      si.object_id,
                      si.index_id,
                      sc.key_ordinal,
                      sc.is_included_column,
                      sc.is_descending_key,
                      sc.partition_ordinal,
                      c.name as column_name,
                      st.name as system_type_name,
                      c.max_length,
                      c.[precision],
                      c.[scale],
                      c.collation_name,
                      c.is_nullable,
                      c.is_identity,
                      c.is_computed,
                      c.is_replicated,
                      c.is_sparse,
                      c.is_filestream,
                      CAST(ic.seed_value AS BIGINT),
                      CAST(ic.increment_value AS INT),
                      CAST(ic.last_value AS BIGINT),
                      ic.is_not_for_replication
                  FROM    [redacted_db].sys.indexes si
                  JOIN    [redacted_db].sys.columns c ON
                      si.object_id=c.object_id
                  LEFT JOIN [redacted_db].sys.index_columns sc ON
                      sc.object_id = si.object_id
                      and sc.index_id=si.index_id
                      AND sc.column_id=c.column_id
                  LEFT JOIN [redacted_db].sys.identity_columns ic ON
                      c.object_id=ic.object_id and
                      c.column_id=ic.column_id
                  JOIN [redacted_db].sys.types st ON
                      c.system_type_id=st.system_type_id
                      AND c.user_type_id=st.user_type_id
                JOIN [redacted_db].sys.objects AS so  ON si.object_id = so.object_id
                                                                          AND so.is_ms_shipped = 0
                JOIN [redacted_db].sys.schemas AS s ON s.schema_id = so.schema_id
                  WHERE si.index_id in (0,1) OPTION (RECOMPILE);

  Msg 50000, Level 16, State 1, Procedure sp_BlitzIndex, Line 1427 [Batch
  Start Line 2] redacted_db database failed to process. An INSERT EXEC
  statement cannot be nested. Msg 3915, Level 16, State 0, Procedure
  sp_BlitzIndex, Line 1431 [Batch Start Line 2] Cannot use the ROLLBACK
  statement within an INSERT-EXEC statement.

I looked into using openrowset, but from what I could tell you can't pass parameters to the stored procedure.

So, how can I insert the results of this stored procedure into the table?

I think there is a open request to have this fixed.

It does appear that they have made some changes in March 2017 as referenced here:

Added Output parameters to sp_BlitzIndex to send Mode 2 results to
permanent repository

I also found a workaround that uses Powershell.

Using PowerShell To Store sp_BlitzIndex Results In A Table.

I'm copying the important information from that post.

  First we need to create a table to store our results in:

USE Master --change this to wherever you want this table to reside
GO
CREATE TABLE [dbo].[BlitzIndexStats](
    [Priority] [tinyint] NULL,
    [Finding] [varchar](255) NULL,
    [DatabaseName] [nvarchar](128) NULL,
    [Details] [varchar](2000) NULL,
    [Definition] [varchar](4000) NULL,
    [SecretColumns] [varchar](512) NULL,
    [Usage] [varchar](255) NULL,
    [Size] [varchar](255) NULL,
    [CaptureDate] [datetime] NOT NULL DEFAULT (sysdatetime())
) ON [PRIMARY]

  I decided to leave off things like the URL and CREATE T-SQL statements
  that are returned, and only store the data I was after.

  Next we use the following PowerShell script to run sp_BlitzIndex and
  store the results in our newly created BlitzIndexStats table.

#call the sp_BlitzIndex procedure and specify the names and data types of the results;
#save the result set in the $BlitzIndex variable
$BlitzIndex = Invoke-Sqlcmd -ServerInstance $SQLInstance -Database master -Query "EXEC ('exec dbo.sp_BlitzIndex @DatabaseName=''--YourDBNameHere--'',@Mode=4')
WITH RESULT SETS
(
    (
    [Priority] smallint
    ,[Finding] varchar(255)
    ,[DatabaseName] nvarchar(128)
    ,[Details] varchar(2000)
    ,[Definition] varchar(4000)
    ,[SecretColumns] varchar(512)
    ,[Usage] varchar(255)
    ,[Size] varchar(255)
    ,[Info] varchar(255)
    ,[URL] varchar(255)
    ,[CreateStatement] varchar(4000)
    )
)"

#Query the data that we want out of our $BlitzIndex result set, and set the $BlitzIndex result set to just contain that data
$BlitzIndex = $BlitzIndex | SELECT Priority,Finding,DatabaseName,Details,Definition,SecretColumns,Usage,Size | Where-Object {$_.Priority -gt 0} | Sort-Object Priority,Finding

#Loop through our desired results
foreach ($i in $BlitzIndex) {
    #we have to set these variables to keep the PowerShell from erroring when encountering sub-expression statements
    $Priority = $i.Priority
    $Finding = $i.Finding
    $DatabaseName = $i.DatabaseName
    $Details = $i.Details
    $Definition = $i.Definition
    $SecretColumns = $i.SecretColumns
    $Usage = $i.Usage
    $Size = $i.Size
    #Insert our results into our table
    Invoke-Sqlcmd -ServerInstance $SQLInstance -Database master -Query "INSERT INTO dbo.BlitzIndexStats (Priority,Finding,DatabaseName,Details,Definition,SecretColumns,Usage,Size) VALUES ($Priority,'$Finding','$DatabaseName','$Details','$Definition','$SecretColumns','$Usage','$Size')"
}

  There are two things I want to point out before running that script:

  Be sure to replace “–YourDBNameHere–“ with your database name
  If you created the BlitzIndexStats table in a different database, modify “-Database master” in the last line of the script


Do you know if this are examples of non-identifying or identifying relationships? Im using MySQLWorkbench to model a diagram. But Im with some doubts about identifying and non-identifying relationships. It seems that all relationships of the project are identifying.

For example, a conference has one user that created the conference.  So the conference table have a column "user_id" to store the id of the user that created the conference. A user can create many conferences so there is a 1 to many relationship between the users and conferences table. But Im not understanding if its a identifying relationship or non identifying relationship. Maybe its an identifying relationship because the conference table, the sabe in the many side, has the column user_id, so because of that is identifying? But so it seems that almost all relationships are identifying.

For example, a conference can have many ticket types so there is a 1 to many relationship between the conferences and ticket types table. A ticket type also have a column "conference_id" so it should be also identifying?

Also a conference can have many registrations so there is a 1 to many between conference and registrations, so there is a column "conference_id" in the registrations table. Its also identifying right?

Also a registration can have many participants associated, so there is 1 to many between registrations and participants tables, so the participants table has a column "registration_id", its also identifying right?

Is the user_id necessary for identifying the conference? Without user_id, would you still be able to unambiguously identify one conference from another?

My guess would be yes because I imagine venue_id (which joins to a table of conference centers) and venue_date uniquely identify conferences. If so, then you have a non-identifying relationship between the conferences table and the users table.

For example: in a baseball DB, is team/player an identifying or non-identifying relationship? It may seem that a team would be a necessary component for identifying a player, but think for a moment: what if a player is a free agent between seasons? Then he may not belong to a team for quite a while.

This takes some time to fully contemplate. Try these explanations:

From - https://stackoverflow.com/questions/762937/whats-the-difference-between-identifying-and-non-identifying-relationships

  An identifying relationship is when the existence of a row in a child
  table depends on a row in a parent table....Formally, the "right" way to do this is to make the foreign key part of the child's primary key. But the logical relationship is that the child cannot exist without the parent.

and

  A non-identifying relationship is when the primary key attributes of
  the parent must not become primary key attributes of the child

Here is another example I like: https://www.datanamic.com/support/relationshiptypes.html


Using RDBMS with pseudo foreign keys VS using NoSQL solutions According to some database administrators for our org, it is generally not advisable to actually enforce foreign-key relations within our MySQL DBs. Instead, it is preferred to simply represent this with a pseudo foreign-key ID column and to instead perform extra application processing for the foreign-key coercion. The reason is that inserts and deletes (especially cascading ones) become prohibitively expensive as the database scales out.

But does this not defeat the purpose of a RBDMS to begin with? From what I understand, it seems that one of the biggest reasons to use a RBDMS to begin with (besides enforcing ACID properties) is to ensure application query processing involving related (i.e. bound by junction tables via FKs) objects are minimized.

Is it in general more practical to utilize pseudo foreign-keys with additional application processing then? And, if so, why would you even want to use an RBDMS? I presume the reason would be that the application processing would still be slightly less (and way more straightforward) than a NoSQL solution?

TL;DR. Unless you have a compelling niche case, then make  full use of the capabilities of your RDBMS!

Hmmm... you say:

some database administrators for our org

What do the others say?
You also write:

it is preferred to simply represent this with a pseudo foreign-key ID column

What, exactly, is a "pseudo foreign-key ID column"?
The whole point (and greatest benefit) you obtain from using FOREIGN KEYs (FKs) is that you have automatic enforcement of Declarative Referential Integrity (DRI) - see below. This functionality of the database system is deliberately inflexible when changing data. This is for your developers' benefit, your DBAs' benefit, your company's benefit and most importantly of all, for your customers' benefit!
The modern RDBMS (most of them anyway) is a complex programming environment in its own right that you can leverage to maintain maximum control of your data as close as possible to your data.
You can effectively have fine-grained control of the data next to the data which is the best place for it. Your database is the last bastion of defence against rogue applications (honest errors for the most part) and/or DBAs/Sysadmins (again, mostly honest) modifying your data in a way that will render it inconsistent.
Inconsistent data can be worse than useless - if you know your data is corrupt, you can restore from backup, but if it's inconsistent, mission-critical decisions could be made on the basis of erroneous premises!
Your DBAs say:

that inserts and deletes (especially cascading ones) become prohibitively expensive as the database scales out

Yet, you appear to be implementing your home-baked RI scheme using pseudo foreign-key ID columns? This adds its own costs - e.g. for an INSERT into a child table, you have to (in the app - and all within one transaction) query the database (presumably checking for the existence of parents?) and then do your (consistent) INSERT and then COMMIT?
For a DELETE and/or an UPDATE on a parent, you have to check for the existence of children, then DELETE or UPDATE the child according to a rule (CASCADing) and then DELETE/UPDATE the parent all within one transaction. Setting DRI in the database removes all that overhead! See here and the rest of that thread.
In my opinion what becomes far more prohibitively costly is lost data, orphaned records, childless parents and late-night calls from irate customers threatening to pull the plug on your company and its software!
Yes, there is a certain price to be paid for enforcing DRI. Your DBAs are suggesting that you enforce this in the app - yet this is also costly and it's far more expensive, far far more error-prone and far far more time-consuming to do it in the app than it is to do it using the RDBMS (see example above).

Apps come and go.
Languages come and go (apart maybe from C).
Frameworks come and go.
It was XML 10 - 20 years ago, now it's all JSON...
Fortran/Cobol -> C -> C++ -> Java -> Scala -> Clojure -> Go -> Rust -> whatever-you're-having-yourself...
Data is permanent (or at least, very long-lived in comparison to the above)!

The youngest of the mainstream RDBMS products, MySQL, was first released 23 years ago and it was only meant to be a lightweight copy of systems such as Oracle (1979), Ingres (mid-eighties or earlier - was important at the time), Sybase/MS SQL Server (1984), PostgreSQL (also mid-eighties or earlier) and Interbase/Firebird (1984 - also important at the time).
So, these systems have been around a lot longer than your app.
What your DBAs are proposing is to reinvet the wheel - a wheel which works very well for millions of users every day in banks, supermarkets, factories, offices and warehouses (non-exhaustive list) all over the world.
There are a few main issues here.

Who knows? Maybe you have an excellent team of hot-shot programmers who can implement mainstream RDBMS capabilities in, say, less than 3 years?
Ehh... why am I not buying that scenario? Well, the "cheapest" (but by far the better IMHO) of the two Open Source systems above - PostgreSQL - cost $15M and 269 work-years of effort and MySQL cost $63.5M and 1155 work-years. Do you think your team can match that?

You have your database software - by trying to circumvent/rewrite its DRI capabilities, you are literally throwing away the years of programming effort and testing by them and (effective) testing by their (literally) millions of end-users.

Suppose, one bright, sunny morning, your boss/customer/tax-authorities/whoever decides that you need to write a new application to work with your database? You'll have to implement all of your DRI all over again! And probably in a different language because your boss has read in some in-flight magazine that X is the language du jour. It'll be like déjà vu meets Groundhog Day all over again!

You then hire a contract whizz-kid programmer who wrote a system in X for his undergrad thesis. He doesn't know that column_A in table_W is meant to be the child of column_B in table_Z, so he happily produces hundreds of lines of code a day, that work swimmingly and quickly on your test data.
Then it lands with the customer and they start to notice that records are going missing - sums/record-counts are incorrect. Your contractor has moved on to a startup (Cowboys 'R Us) and you're left holding the baby! (This scenario could also pan-out in your current set-up).
Check out these three articles

I can't wait for NoSQL to die - an admittedly jaundiced view of NoSQL, but not so much if you consider the following articles,
what happens when the rubber meets the road and (ahem...) advantage was taken of NoSQL's eventual consistency (BASE) model,
Finally, how NoSQL systems are now finding that SQL and consistency weren't really that bad after all - come back ACID/SQL, all is forgiven!

I'm not saying that NoSQL systems don't have a "niche", it's just that a niche is precisely what they do have (much in the same vein as above). Unless you have very specific needs which are clearly aligned with a NoSQL use-case, then they are best avoided  (IMHO).
If the above hasn't convinced you, then consider these final points:

Read this - point no. 2 from Jonathan Lewis. He wrote a book (530 pages on Oracle optimiser fundamentals), so he knows a thing or two about databases!

More war stories, for fans of Chapter 8! "Now prepare yourself to
read all about 'The World's Worst Oracle Project.'" - Jonathan Lewis.
This chapter describes some of the most common mistakes in development
Oracle database applications. You'll certainly recognise some of them,
because so many people stubbornly cling to certain beliefs. I know I
like to bring up several of his points when I get into common
arguments like these:

We want our application to be "Database Independent."
We will check data integrity at the application level instead of taking advantage of Oracle's constraint checking abilities.

NOTE - point 2. - that's exactly what you are proposing to do!

Look at Brian Aker's amusing talk on NoSQL here - he makes the point that NoSQL is OK for ad-hoc queries where you're dealing with stale-ish data, it's just that SQL is great at slicing and dicing data also.

The key advantage though with SQL is that you haven't to write a new Map-Reduce job or whatever to analyse your data, you can just write a query, et voilà, you're done! If you're not using FOREIGN KEYs in your RDBMS, you're effectively in a half-way house between SQL and NoSQL!

There are old-wives' tales about FKs being unnecessary overhead. This notion was kept alive by the fact that (for good, but probably now legacy, notably space, reasons) some RDBMSs did not/don't automatically create indexes on Foreign Key fields (this should be done in the majority of cases). This led to the mistaken view that Foreign Keys were overhead without benefit which is not true for the vast bulk of use-cases!
You say:

if so, why would you even want to use an RBDMS?

This is a very good question. If you're not using the RDBMS as an RDBMS, then why have it at all? Just put the data into Notepad/vi!
You should have a serious sit-down with management and explain why using the RDBMS' capabilities is a good idea. Furthermore, I would ask your (No-FK) people to justify not using FOREIGN KEYs. Unless your company has a real and pressing NoSQL niche, then point them to the works of Micheal Stonebraker and/or Jonathan Lewis on this.
Finally, my advice to you, if your company/management insists on pursuing this madness, is to run very very far, very very fast! You will end up in a situation where you are constantly firefighting, being woken from your bed at 03:30 and worse still, you won't get a chance to improve your skill set or learn anything of value.


How to grant permission to execute stored procedure in master database from another database Our developers have a domain account used by their application that needs to be able to execute stored procedures created in the master database using the "sp_" naming convention from other databases on the server (SQL Server 2012 Enterprise Edition).

I understand this isn't a recommended practice, but the decision to do so was made by someone else with more authority than me.

I have no problem executing such stored procedures myself from any database on the server, but I also have sysadmin privileges and would prefer to grant this user as few permissions as possible.

Here's what I've tried:

--create procedure for purpose of testing
USE [master]
GO
CREATE PROCEDURE dbo.sp_HelloWorld
AS
PRINT 'Hello World!'
GO

--create login for test user
USE [master]
GO
CREATE LOGIN [DOMAIN\user] FROM WINDOWS
GO

--create user in master database
USE [master]
GO
CREATE USER [DOMAIN\user] FOR LOGIN [DOMAIN\user]
GO

--create user in another database on server
USE MyDB
GO
CREATE USER [DOMAIN\user] FOR LOGIN [DOMAIN\user]
GO

--grant execute permission on stored procedure in master database
USE [master]
GO
GRANT EXECUTE ON dbo.sp_HelloWorld TO [DOMAIN\user]
GO

I'm able to run EXEC dbo.sp_HelloWorld myself from any database on the server without issue.

I'm also able to run the following:

USE [master]
GO
EXECUTE AS USER='DOMAIN\user'
EXEC dbo.sp_HelloWorld
REVERT

But trying to execute the stored procedure from within the context of any other database while impersonating the user:

USE MyDB
GO
EXECUTE AS USER='DOMAIN\user'
EXEC dbo.sp_HelloWorld
REVERT

Results in the following error:

  Msg 229, Level 14, State 5, Procedure sp_HelloWorld, Line 1 [Batch Start Line 15]
  The EXECUTE permission was denied on the object 'sp_HelloWorld', database 'master', schema 'dbo'.

What am I missing?

What am I missing?

The DOMAIN\user login should be able to run  dbo.sp_HelloWorld with it's corresponding user in the MyDB context.

The EXECUTE AS USER command is the reason for the error being returned.

Execute as user documentation

  Specifies the context to be impersonated is a user in the current
  database. The scope of impersonation is restricted to the current
  database. A context switch to a database user does not inherit the
  server-level permissions of that user.

Important part: the scope of impersonation is restricted to the current database.

Which is why the permissions from other databases such as master are not applied when using EXECUTE AS USER.

You need to use EXECUTE AS LOGIN  if you want the correct login / user mapping

Execute as login documentation

  Specifies the execution context to be impersonated is a login. The
  scope of impersonation is at the server level.

USE MyDB
GO
EXECUTE AS LOGIN ='DOMAIN\user'
EXEC dbo.sp_HelloWorld
REVERT

Result

Hello World!


Understanding & Resolving Deadlock in SQL Server 2008 R2 I am trying hard to figure out what may be going wrong for below shown deadlock graph:

I am receiving alerts for deadlocks similar to above for almost every 10 minutes, it's just the query changes, action of deadlock remains the same:

**Wait Resource Page:11:11:...2820**
Lock Type:  Page
Own/Wait: Own
Mode: X
SPID: 1568
ECID: 0
Sql:
    update access
    set accessdate=accessdate  where userid= 'ABC.DEF' and companyid= '12'
    and databasename= 'XYZ' and internetaddress= 'KASQL-Tcp#10'
    and sessioncntr=12 and tstamp <= 0x0000000000sw123
Lock Type: Page
Own/Wait: Wait
Mode: U
SPID: 1120
ECID: 0
Sql:
    delete access where userid= 'ADAm.Smith' and companyid= '23'
    and databasename= 'XYZ' and internetaddress= 'KASDER-Tcp#8'
    and sessioncntr=23

**Wait Resource Page:11:11:...2830**
Lock Type: Page
Own/Wait: Own
Mode: X
SPID: 1120
ECID: 0
Sql:
    delete access where userid= 'ADAm.Smith' and companyid= '23'
    and databasename= 'XYZ' and internetaddress= 'KASDER-Tcp#8'
    and sessioncntr=23
Lock Type: Page
Own/Wait: Wait
Mode: U
SPID: 1568
ECID: 0
Sql:
    update access
    set accessdate=accessdate  where userid= 'ABC.DEF' and companyid= '12'
    and databasename= 'XYZ' and internetaddress= 'KASQL-Tcp#10'
    and sessioncntr=12 and tstamp <= 0x0000000000sw123

I've looked at fragmentation & any missing indexes , but that's all good for the database xyz as mentioned in queries above!

Note: DBID mentioned in deadlock graph says dbid 11, which is different than on e in queries that is XYZ, so dbid 11 is actually another database 'DBCapse'

Please provide any inputs that can help me in resolving and understanding this deadlock situation.

Looks like multiple statements in a transaction.  One row is the target of the first statement of the first transaction and the second statement of the second transaction.  And vice-versa: the same row is targeted by the second statement of the first transaction and the first statement of the second transaction.

Is there any reason why you are using multi-statement transactions?

If so, can you "order" the statements within the transaction so that they always affect rows in a predefined order?  In a row with an id identity key, if consecutive statements affect higher id transactions, the chance for conflict is greatly reduced.


Lock pages in memory and Virtual SQL Server VmWare 2008R2 Enterprise SQL server with Lock Pages in Memory ENABLED.... I have read the Jonathan Kehayias blog post referenced from Brent Ozar's blog via sp_Blitz and still not sure if I should have LPIM enabled. I have no ballooning on virtual server in the past month, max memory in set to 52428 MB out of 64GB, min memory is set to 1024, vCPU 8, host CPU% avg for month 24, vMachine CPU% avg for month 17. Currently ~52GB of pages locked in memory...I should note that 3rd party app has lots of ad hoc queries and lots of heaps and I average ~15K fowarded fetches/sec via sp_BlitzFirst. I rebuild the heaps weekly. FINALLY.. LPIM helping or hurting? What else can I look at?

From your description it seems like things are fine for you even when LPIM privilege is their for SQL Server service account. If things are fine why do you want to change it I suggest you keep it like this.

Now LPIM protects paging of SQL Server process, so unless you have mis-configured VM host or some other applications running on OS which can leak or "Pull" memory from OS LPIM is actually not required. I as well believe that normally on VMware SQL Server should not be given LPIM unless required and forced by Ballooning or other application pulling memory unnecessarily. VMware ballooning is feature which should not be disabled but the VM host should be configured such that memory is balanced most of the time.

Quoting From Vmware Online Doc

  When setting the SQL Server Lock Pages in Memory user right, the
  virtual machine’s memory reservation should also be set to match the
  amount of the provision memory. Setting virtual machine memory
  reservations prevent the balloon driver from inflating into the SQL
  Server virtual machine’s memory space. Lock Pages in Memory should
  also be used in conjunction with the Max Server Memory setting to
  avoid SQL Server taking over all memory on the virtual machine.

Moral: If you are not seeing any anomaly after enabling LPIM I suggest you leave it as it is but before doing this speak to your VMware admin and make sure the VMware configuration is correct


How can i use the same query for all codes? I use this query in PHPmyAdmin, i have date that contain(date,hour),supp, price and code_product (it repeated many times) :

I want to return the last price for the last date of product code

   SELECT invent.code_product,products.supp,products.price,products.date
   FROM products

   LEFT JOIN list_product ON products.supp = list_product.supp
   LEFT JOIN invent on list_product.id_product = invent.fk_product

   WHERE invent.code_product= "XXX185"
   ORDER BY products.date  DESC
   LIMIT 1 ;

This is the result for one code :

The query works for one code and limit it the result in 1.

How can i use the same query to return the last date and price for all codes?

It sounds like you want something like this:

SELECT invent.code_product,products.supp,products.price,
       MAX(products.date) AS Date
FROM products
LEFT JOIN list_product ON products.supp = list_product.supp
LEFT JOIN invent on list_product.id_product = invent.fk_product
Group by  invent.code_product,products.supp,products.price
Order by  products.date

To fully understand your comments on Date and Hour, it would be good to see your data definition and some representative data.


database not opening While altering a DB to open getting the below error.

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [kcratr_nab_less_than_odr], [1],
[344], [73], [125], [], [], [], [], [], [], []

Please provide further details about what happened previously. Did a hardware failure occur? Also, refer to the .trc files that generally follows ORA-00600 errors in alert.log. If you're on linux and made a default installation, alert log will probably be located at $ORACLE_BASE/diag/rdbms/sid/sid/trace. It contains the main DB events, and it's the first approach while diagnosing Oracle Failures.


Oracle: Non key-preserved table should be I'm getting "ORA-01779: cannot modify a column which maps to a non key-preserved table" when I try to update a join.  I've searched around the site and found a lot of advice on what key-preserved means and why it is necessary... but as near as I can tell I'm complying with that advice, and still getting the error.

I have two tables:

PG_LABLOCATION has, among other things, the columns:
"LABLOCID" NUMBER,
"DNSNAME" VARCHAR2(200 BYTE)

LABLOCID is the primary key, DNSNAME has a unique constraint

PG_MACHINE has, among other things, the columns:
"MACHINEID" NUMBER,
"LABLOCID" NUMBER,
"IN_USE" NUMBER(1,0) DEFAULT 0,
"UPDATE_TIME" TIMESTAMP (6) DEFAULT '01-JAN-1970'

MACHINEID is a primary key
LABLOCID is a foreign key into LABLOCID in PG_LABLOCATION (its primary key)

The update I'm running is:

update
  (select mac.in_use, mac.update_time
     from pg_machine mac
     inner join pg_lablocation loc
       on mac.lablocid = loc.lablocid
     where loc.dnsname = 'value'
       and '02-JAN-2013' > mac.update_time
  )
set in_use = 1 - MOD( 101, 2 ), update_time = '02-JAN-2013';

I'm only updating values in one table (PG_MACHINE) and the join column in the other table is the primary key, which should make it key-preserved by my reading.  I'm concerned that the where clause is causing the problem, but I tried removing the filter on mac.update_time and got the same error, and loc.dnsname has a unique constraint.

What's even odder is that we have, like many folks, a dev and a prod environment.  We did a complete schema and data migration from prod to dev.  I've looked them both over and they have identical indexes and constraints.  The query works in dev, but generates the above error in prod.

So two questions:

1) Can you see what's wrong with my query?
2) Can you suggest what might be different between my dev and prod environment (e.g. server settings) that could cause this error in one but not the other?

You can update a join in Oracle if the following conditions are met:

Only one base table is updated
All other tables are key-preserved: each of them must have at most one row for each row of the base table.

(additional restrictions on updating views apply)

In your example you update table PG_MACHINE only. Oracle has to make sure that for a single row of this table, only one row of the other can be found. This seems to be the case since you have a PK on PG_LABLOCATION.LABLOCID. Therefore you should be able to update the join. See for example this SQLFiddle with a similar setup.

In your case you should either:

make sure that the primary key is enabled, validated, not deferrable (interestingly, a deferrable constraint prevents Oracle from updating the join!)
use MERGE if PG_LABLOCATION.LABLOCID is unique for the relevant query. MERGE is less strict than update with joins and will only return an error if there is actually a duplicate in the result set (whereas UPDATE will fail if there is the possibility of a duplicate).
review your query, since you don't need values from the parent table in the SELECT clause, you could rewrite it as a semi-join (that guarantees that no duplicate will be generated):

UPDATE (SELECT mac.in_use, mac.update_time
          FROM pg_machine mac
         WHERE mac.lablocid IN (SELECT loc.lablocid
                                  FROM pg_lablocation loc
                                 WHERE loc.dnsname = 'value')
           AND to_date('02-JAN-2013') > mac.update_time)
   SET in_use = 1 - MOD(101, 2),
       update_time = to_date('02-JAN-2013');

This could be rewritten as:

UPDATE pg_machine mac
   SET in_use = 1 - MOD(101, 2),
       update_time = to_date('02-JAN-2013')
 WHERE mac.lablocid IN (SELECT loc.lablocid
                          FROM pg_lablocation loc
                         WHERE loc.dnsname = 'value')
   AND to_date('02-JAN-2013') > mac.update_time;

In this case I would go with the third option: in general you can't update the parent in a parent-child join.


Can a transaction log backup create a new database before any full database backup is applied? SQL Server 2017 We have a large database we restore daily from a single t-log backup from a remote supplier. We are looking to move to 15 minute t-logs, but applying these to the current database on prem it is then very hard to see just what has changed, to update the downstream warehouse tables. A hypothetical solution would be if it were possible to take a t-log as it came in, restore it to a new database, and then be able to compare tables in that with the tables in the full database to then create some sort of UNION view that reflects the current state.
But I am 99.9% sure you cannot restore a t-log if you have not restored a full backup before, hence I am looking at ApexSQL Log to read the t-logs instead.
But if I'm wrong, please let me know! I'd love to be able to restore a single t-log to a new database!

No, in order to restore transaction logs, your database has to be in a restoring state first, and to do that, you'll need to restore a full backup.


SELECT + INSERT or INSERT alone I have an application where several devices send data every second to my server. On the database I have a table with the devices and another table with the data sent every second.

The first time a device sends the data the server script should register (INSERT) that device id in the device table and add the data to the data table.

My question is, what would be faster?

To do a SELECT EXISTS query to determine whether an INSERT is required on the device table. If SELECT EXISTS returns false then execute an INSERT, otherwise do nothing.
To execute always an INSERT statement. Considering the device id is primary key on the table, if the device already exists it would return an error, otherwise it would insert it.

Only the first time a device sends data would require an actual INSERT, after that no INSERT would ever be required.

The PostgreSQL version is 9.4.

The table of devices is defined as follows:

CREATE TABLE common.tag
(
  customer integer,
  hostname character varying(150),
  description text,
  model integer,
  configprofile integer,
  id character varying(150) NOT NULL,
  host integer,
  CONSTRAINT tag_pk PRIMARY KEY (id),
  CONSTRAINT tag_fk_client FOREIGN KEY (customer)
      REFERENCES common.customer (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT tag_fk_configprofile FOREIGN KEY (configprofile)
      REFERENCES common.configprofile (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT tag_fk_tagmodel FOREIGN KEY (model)
      REFERENCES common.tagmodel (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)

In Postgres 9.5, the most elegant, simple, safe and fast solution is the new UPSERT implementation:

INSERT INTO device (device_id) VALUES (1234567)
ON CONFLICT (device_id) DO NOTHING;

-- now we have either inserted the device or it was there already
INSERT INTO data (device_id, data)
VALUES (1234567, 'some data);

Obviously, there must be a some kind of unique index or exclusion constraint on (device_id).
Details in the manual.

For older versions or for auto-generated serial IDs:

Is SELECT or INSERT in a function prone to race conditions?


MariaDB - So many threads? I'm on a test VM based on VMWare with MariaDB + nginx installed; when looking with htop this is the situation:

So we have 26 threads under the main process.

I know that inactive threads doesn't impact so much in memory / performaces, but for my "know how", is there a way (parameters?) to reduce the number of child threads under MariaDB / MySQL?

Googling around I've found something related to max_connections and thread_concurrency parameters that I've set both to 5 but once restarted nothing changed.

Thank you very much for your answers!

Once you have had some activity, I think the number of threads varies between

(misc) + thread_cache_size
(misc) + max_connections

thread_concurrency was specific to Solaris 8, and has been removed from newer versions of MySQL/MariaDB.

Where (misc) is 1 for the main process plus replication slave threads plus innodb read/write/purge threads.  (Thanks danblack)


postgresql count null and blank value from jsonb column In following JSON structure,

[
    ["a", 921],
    ["b", ""],
    ..
    ...
    ["c", null],
]

how to count occurrence of

blank value ""
null value

You can use the function json_array_elements to convert your array into a set of elements. Each element is itself an array, so you can use the -> operator to retrieve the second element. Once you have them, use a standard count(*), with the corresponding FILTER.

You can do it with the following SQL statement (the WITH helps you view the "step-by-step" approach):

WITH original_data(var) AS
(
    VALUES (
    '
    [
        ["a", 921],
        ["b", ""],
        ["c", null]
    ]
    '::json)   -- Note the ::json to make sure PostgreSQL uses the proper type
)
, second_elements AS
(
SELECT
    json_array_elements(var)->1 AS e
FROM
    original_data
)
SELECT
    count(e) AS total_elements,
    count(e) FILTER (WHERE e::text = '""') AS blank_elements,
    count(e) FILTER (WHERE e::text = 'null') AS null_elements
FROM
    second_elements ;

... or the following one:

SELECT
    count(e) AS total_elements,
    count(e) FILTER (WHERE e = '""') AS blank_elements,
    count(e) FILTER (WHERE e = 'null') AS null_elements
FROM
    (
    SELECT
        (json_array_elements(var)->1)::text AS e
    FROM
        (
        VALUES (
        '
        [
            ["a", 921],
            ["b", ""],
            ["c", null]
        ]
        '::json)
        ) AS original_data(var)
) AS second_elements ;

In both cases, you get:

 total_elements | blank_elements | null_elements
 -------------: | -------------: | ------------:
              3 |              1 |             1

You can check everything at dbfiddle.

NOTE: If you want to use jsonb instead of json, just change the ::jsonwith ::jsonb in the first statement, and call the jsonb_array_elements instead of json_array_elements.


Best practice design schema for implementing form approval tracking? I'm in the middle of creating an ASP.NET MVC 5 web app using windows SQL server as my DB setup.

The app has a very simple purpose: Allow a user to submit a form to be signed by multiple people, traveling "up" the chain of command to be approved by each person. After the final approved the form travels back "down" to the requester.

Now I understand of course that within a database context, the form isn't going anywhere at all; variable are changing making the form alternately visible/invisible to different people.

But what I want to know is: what is the best way to implement this? Specifically, the best way to "track" forms that are traveling the chain.

As I have it right now, I have a model of a request form (containing info like date of request, reason for it, etc.), and that's all dandy. But I'm hung up on the BEST WAY to implement the chain. I was thinking of having two extra columns: CurrentApprover and Approved. CurrentApprover is a foreign key representing the primary key of the person that needs to approve your form next, and Approved represents whether the form has been fully approved.

This is simple, and it would work in practice. However it may limit me down the road in manipulating the pipeline or such. Maybe I could have a "manifest" table which contains all active requests and their details? I like this idea better now that I type it out.

Does anyone have a suggestion?

Also, 2 quick questions if someone would be so kind:

1) This app will be processing thousands of forms each year, and though I know tables do fine with millions of entries, I was wondering where there would be any advantage/best practice reason to have different tables for different company divisions (there are 3 distinct divisions, but the forms are the exact same and the process for approval is the exact same).

2) If a form is foreign keyed to its submitter, and the user data table for the submitter contains info that is being passed up with the request form, do I still need to/ should I include those fields explicitly in the request form table, or is it sufficient/better to just query those values when needed. I feel like the latter is better, because a form has a submitter ID (employee ID per-say), and I can just use that to look up the employee's user info when I need it.

Thank you very much!

I am working on a similar process and found this very useful site that proved helpful for my processing issues.  This details a request and approval process that can be changed to fit other applications.


What does it means when you have "sql_exit_invoked" in System Health? I'm having problems with one of my SQL Server 2016 Standard servers. I have 8 production servers and this one is the only one who crashes randomly without having any trace in the log.

I have system_health enabled on it. I noticed that I have a row in system health witch is "sql_exit_invoked".

I'm trying to find more informations on that row. What does it means?
The only info I found over the internet is that it happens when SQLExit() is called and that it's logged only since SQL 2012. (link available on msdn website )

So my question is : Should I worry to see this in my log? I find this only on my problematic server and not on any of the other 7 servers. (All of them are SQL Server 2016 Standard edition)

Can anybody give me more information on this?

Extended Events are pretty poorly documented.

Debug channel extended events are even more poorly documented.

Based on my testing, you'll need to use the "shutdown_option" value from those event entries as a clue for where to look next.  Here's what they mean in my limited testing:

ORDERLY_SHUTDOWN

restart or stop the service cleanly (using Config Manager, Services snap in, etc)
restart Windows cleanly (start menu -> shutdown / restart)

NICE_SHUTDOWN - stop the service using the SHUTDOWN; T-SQL command
FAST_SHUTDOWN - stop the service using the SHUTDOWN WITH NOWAIT; T-SQL command

Note that I also tried the following, and they did not register anything in the Extended Events session:

shutdown Windows by holding down the power button
kill the sqlservr.exe process from the Task Manager

I was unable to get the SHUTDOWN_NOT_SET version of the event to fire, so that one remains a mystery.

In your screenshot, I can see that ORDERLY_SHUTDOWN is the option in your scenario. In that case, since this seems to imply a clean shutdown of the service or Windows PC, I would look into the Windows Event Viewer "System" log and see why the machine is being restarted, or what service / component is issuing a SHUTDOWN command to the SQL Server serevice.

Good luck!


Creating tables and triggers dinamically Can I automatize the creation of tables and triggers to this table ?
I need to create one table every month and this table needs a trigger...
Is It possible ?

The Relational Model of Data advocates storing data in columns of rows in tables. That is, it suggests that storing data--such as year and month--in a table's name might be a Bad Thing. In my experience, it's a Very Bad Thing.
Instead, store the year and month in a column of a row in the table. Use suitable CHECK constraints. (Don't allow storing month 13 or year 32, for example.) Or store a date, from which year and month can easily be derived; columns of type "date" won't allow invalid months, although you might still want to use a CHECK constraint to limit the range of valid dates. Valid dates are application-dependent.
If you're concerned about the number of rows--you probably shouldn't be--read up on indexing, indexing expressions, and partitioning.


MySQL: SLEEP injection I was wondering if someone could help to understand this query, basically I'm aware about SLEEP() function but I've never seen this implementation before.
Why there are no any results returned?

mysql> SELECT fname, lname FROM contacts WHERE fname = 'test' / SLEEP(1) / '';
Empty set, 14 warnings (7.00 sec)

Warnings:

Warning | 1292 | Truncated incorrect DOUBLE value: 'test'
...

No results are returned because, unsurprisingly, there are no records that meet the conditions of the WHERE clause.  SLEEP() will return 0 (if not interrupted), and all operands will be cast as doubles for the division operation (hence the warning about truncated incorrect double).  In essence, the query is the same as

SELECT fname, lname FROM contacts WHERE fname = 0 / 0 / 0;

Division by zero results in null, so you're basically looking for records where fname = NULL, which is never true.


MySQL upgrade from 5.6 to 8.0 When trying to start MySQL 8.0 it complained about partially upgraded DD. Furthermore, it complained about not being to open a table S/AD.ibd which no longer exists there because we moved it to S/AD.ibd. We reverted back to MySQL 5.6 and it seems to be running fine now. It looks like it failed during converting the .frm files into the information_schema, and I suppose this is why we were able to revert back to 5.6.

My questions:

1) Does anyone have any idea what this could be?
2) Are there MySQL diagnostic tools I can run against our 5.6 instance to see if there is inconsistent state stored about our AD.ibd table?

Here is the full log if it's of interest:

  The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
  information that should help you find out what is causing the crash.
  2018-06-22T15:02:56.236394Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.11) starting as process 1831
  2018-06-22T15:02:58.482400Z 1 [ERROR] [MY-011014] [Server] Found partially upgraded DD. Aborting upgrade and deleting all DD tables. Start the upgrade process again.
  2018-06-22T15:02:58.864512Z 0 [ERROR] [MY-010020] [Server] Data Dictionary initialization failed.
  2018-06-22T15:02:58.864621Z 0 [ERROR] [MY-010119] [Server] Aborting
  2018-06-22T15:03:00.755015Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.11)  MySQL Community Server - GPL.
  2018-06-22T15:06:15.015157Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.11) starting as process 2225
  2018-06-22T15:06:17.585241Z 1 [ERROR] [MY-012592] [InnoDB] InnoDB: Operating system error number 2 in a file operation.
  2018-06-22T15:06:17.585309Z 1 [ERROR] [MY-012593] [InnoDB] InnoDB: The error means the system cannot find the path specified.
  2018-06-22T15:06:17.585330Z 1 [ERROR] [MY-012216] [InnoDB] InnoDB: Cannot open datafile for read-only: './S/AD.ibd' OS error: 71
  2018-06-22T15:06:17.585354Z 1 [Warning] [MY-012019] [InnoDB] InnoDB: Ignoring tablespace LD/AD because it could not be opened.
  2018-06-22T15:06:18.511449Z 0 [Warning] [MY-010727] [Server] System table 'plugin' is expected to be transactional.
  2018-06-22T15:06:18.627162Z 2 [Warning] [MY-010772] [Server] db.opt file not found for S database. Using default Character set.
  mysqld: /export/home/pb2/build/sb_0-28122056-1523171044.09/rpm/BUILD/mysql-8.0.11/mysql-8.0.11/sql/dd/upgrade/table.cc:1076: bool dd::upgrade_57::add_triggers_to_table(THD*, TABLE*, const String_type&, const String_type&): Assertion `t->
  get_event() >= t_type && (t->get_event() > t_type || t->get_action_time() >= t_time)' failed.
  15:06:19 UTC - mysqld got signal 6 ;
  This could be because you hit a bug. It is also possible that this binary
  or one of the libraries it was linked against is corrupt, improperly built,
  or misconfigured. This error can also be caused by malfunctioning hardware.
  Attempting to collect some information that could help diagnose the problem.
  As this is a crash and something is definitely wrong, the information
  collection process might fail.

Don't use / (or certain other things that could confuse the file manager) in a table name.

Perhaps you can rename it?

Update

OK, S is the database name, AD is the table name.

No, you cannot move .ibd files around.  There is critical information in ibdata1 that will get confused.

It is possible to do

RENAME TABLE X.AD TO Y.AD

(At least that was possible before 8.0.  The internals of 8.0 are radically different; if such a cross-db RENAME TABLE fails, file a bug report with bugs.mysql.com )


serializability ordering after one transaction finishes in postgres Suppose, I have a table product with integer column price.

Here are two transactions that I begin at the same time (almost):

T1:

BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;

UPDATE "product"
SET price = 13;

T2:

BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;

insert into "product" (id, price) values ('three', 10);

Now suppose I commit T2 first. And then T1.

My result after both transactions finish is:

| id  |  price
---------------
| one | 13   |
| two | 13   |
| three| 10  |

This is perplexing to me as I would have expected the serialized order to be T2 -> T1 (since T2 finished much befor) but the result set implies the serialized order is T1 -> T2.

Also, I might also have been okay with T1 getting aborted because of T2 committing.

The serializable isolation says that the result of concurrent transactions has to be consistent with one serial order, no matter which one.

Here there are two possible serial (sequential) orders, T1 followed by T2 and T2 followed by T1.

The resut of T1 followed by T2 would be:

| id  |  price
---------------
| one | 13   |
| two | 13   |
| three| 10  |

It happens to be exactly the result you got in the concurrent execution of T1 and T2.

This is why there is no reason for the engine to raise a
serialization exception. This particular concurrent execution meets the condition of success of the serializable isolation level.

  I would have expected the serialized order to be T2 -> T1 (since T2
  finished much befor) but the result set implies the serialized order
  is T1 -> T2.

Here you are confused about how it works. The isolation level does not influence the order of execution. It merely detects serialization anomalies to raise an error when they happen (and also in fact, when they might happen, because false positives are possible).


Mysql error "#1030 - Got error -1 from storage engine" when doing insert/update queries I am starting to learn manipulating some long list of data from datalogs in mysql, around 300k entries, and it is being added by about 1k off entries everyday, it was working fine in a week then suddently I'm not able to write in the db. Here are the errors that I'm getting:

I can do select queries but when I do insert and update I am
getting this error:

  #1030 - Got error -1 from storage engine

When I try to change the engine type from InnoDB to MyISAM, I'm
getting this error:

  #1025 - Error on rename of './dbname/tablename'
  to './dbname/#sql2-500-37' (errno: -1

I can create new tables

These are the things that I have checked:

Any of my disk space is still more than 50%, I have checked it using df
Last line in the MySQL error log (/var/log/mysql/error.log):

InnoDB: A new raw disk partition was initialized or
InnoDB: innodb_force_recovery is on: we do not allow
InnoDB: database modifications by the user. Shut down
InnoDB: mysqld and edit my.cnf so that newraw is replaced
InnoDB: with raw, and innodb_force_... is removed.

Tried restarting server & mysql service
I also tried doubling the values in the MySQL config file but it seems it doesn't help, here is my config file (/etc/mysql/my.cnf)

[mysqld_safe]
socket          = /var/run/mysqld/mysqld.sock
nice            = 0

[mysqld]
user            = mysql
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
datadir         = /var/lib/mysql
tmpdir          = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking

bind-address            = 127.0.0.1
key_buffer              = 16M
max_allowed_packet      = 16M
thread_stack            = 192K
thread_cache_size       = 8

myisam-recover         = BACKUP
#max_connections        = 100
#table_cache            = 64
#thread_concurrency     = 10
#
# * Query Cache Configuration
#
query_cache_limit       = 1M
query_cache_size        = 16M

expire_logs_days        = 10 .cnf', otherwise they'll be ignored.
max_binlog_size         = 100M

[mysqldump]
quick
quote-names
max_allowed_packet      = 16M.cnf', otherwise they'll be ignored.

[mysqldump]
quick
quote-names
max_allowed_packet      = 16M

[mysql]
#no-auto-rehash # faster start of mysql but no tab completition

[isamchk]
key_buffer              = 16M

!includedir /etc/mysql/conf.d/
innodb_force_recovery = 0

My server info is:

Server: Localhost via UNIX socket
Server type: MySQL
Server version: 5.5.43-0ubuntu0.14.04.1 - (Ubuntu)
Protocol version: 10
User: sfere01@localhost
Server charset: UTF-8 Unicode (utf8)

Any other tips on how to further troubleshoot & resolve this kind of issue? What other things should I have checked or missed?

You will notice in the errors this message

  InnoDB: innodb_force_recovery is on: we do not allow InnoDB: database
  modifications by the user. Shut down

So please remove the innodb_force_recovery option from my.cnf


What is the Difference between Data Transformation vs Transportation? Is there a difference in meaning between "data transformation" and "data transportation" in ETL process and data warehousing? or both concepts can be used interchangeably in this context?

Thanks

Transportation is just moving data from one place to another - in ETL, from source system to either staging area, data warehouse or data mart.

Transformation is changing data structure so that it meets data warehouse needs - i.e. star schema, denormalization, aggregation, calculations, data cleansing, data enrichment etc.

Both happen while ETL, but the "T" from "ETL" stands for Transformation.


Master/Slave without populating tables So I have some Zabbix instances which are using MySQL for their backends. What I would like to do is, for DR purposes, backup the DBs for each instance using a Master/Slave configuration, with the slave node sitting at the DR site. However, I don't want to capture trends/historical data, all I want is configuration data. Is there a way to replicate Zabbix's database without populating the tables which hold all of the applications historical monitoring metrics?

@RolandoMySQLDBA's answer is accurate, but I would advise against using the replicate-*-table and replicate-*-db options because of the complexity of how MySQL evaluates replication rules.

When these aren't used, everything is replicated, and I would contend that replicating everything the most reliable configuration.  As a rule, I never do anything else.

If you don't want to replicate the historical data, I would start by setting up a full, standard master/slave replication setup...

...then...

...identify the tables you don't care about on the replica and for each such table, on the slave:

ALTER TABLE table_name ENGINE=BLACKHOLE;

This will discard all of the data in those tables on the slave and convert them to the blackhole storage engine, which accepts inserts (but doesn't store them), returns empty result-sets from selects, and has "0 rows affected" on updates and deletes... so your schemata are still fully compatible but you're not actually storing anything in the tables you're not interested in.

If you're using mysqldump to initially set up the replica, you can also manually edit the dump files and change the table engine on those tables' declarations.

All of the data will still be "replicated to" the slave, but not "saved on" the slave... the data written to these tables will not be stored.  Writing rows to a blackhole table is a very resource-friendly operation -- I've seen my servers handle 40,000+ queries per second when replicating "into" blackhole tables.

If you're concerned about the fact that the data is still transferred to the replica... or, really, even if you're not, just turn on slave_compressed_protocol on the slave and don't think about it -- consider it the cost of a cleaner replication setup.  When the slave connects to the master, it will negotiate a connection using the MySQL compressed client protocol, substantially reducing the actual bytes of data transferred on the wire between the machines, but still sending everything.


Statistics: CPU Time vs Elapsed Time. What is more important? When performance tuning, what is more important:

CPU time or elapsed time?
Are there scenarios where one of them is more important than the other?

An example: while performance tuning the CPU time will reduce by ~38% but the Elapsed Time increases by ~22%. Is this an improvement?

CPU Time is the quantity of processor time taken by the process.  This does not indicate duration.  "Elapsed Time" represents the total duration of the task.  If a given task uses a parallelism of 8 (i.e. 8 threads), and each thread is used at a rate of 100% over the entire duration of the task, CPU time could be 8000ms, while Elapsed Time would only be 1000ms.

Therefore, shorter "Elapsed Time" indicates faster response time.

You may want shorter CPU time and longer elapsed time if you are concerned about CPU pressure, and don't care about user experience.

You may accept longer CPU time if that results in a drop in elapsed time, since that might indicate the user is waiting less, at the expense of higher CPU utilization.

Arguably, the more important metric for query tuning would be wait times.


increasing mysql table open cache? I often read that it is best to increase this variable slowly. Can someone explain why? My status indicates that I should increase it... What is best practice / "slowly"?

Thanks!

Since you are using InnoDB with innodb_file_per_table enabled, you need to raise innodb_open_files because this dictates the number of open file handles on .ibd files.

In MySQL 5.6, this setting is treated a little different.

    This variable is relevant only if you use multiple InnoDB tablespaces. It specifies the maximum number of .ibd files that MySQL
    can keep open at one time. The minimum value is 10. As of MySQL 5.6.6,
    the default value is 300 if innodb_file_per_table is not enabled, and
    the higher of 300 and table_open_cache otherwise. Before 5.6.6, the
    default value is 300.

    The file descriptors used for .ibd files are for InnoDB tables only. They are independent of those specified by the --open-files-limit
    server option, and do not affect the operation of the table cache. For
    general I/O tuning advice, see Section 8.5.7, “Optimizing InnoDB Disk
    I/O”.

Here is an additional reference from MySQL Performance Blog about setting innodb_open_files.

Raising innodb_open_files to a big number should not be that all that troublesome, but please be sensible about the number you choose.


Install SQL Server Express r2 2008 using a configuration file I need to deploy my application with SQL Server Express. There is a way of installing sql express using a configuration file so that when deploying my application it appears as if I am only installing one thing and make it easier for the user installing the software. Anyways here are the steps:

Step 1)

Create the configuration file:

Step 2)

Place the Setup.exe (SQL Server 2008 R2 Express)  file next to the configuration file. I place both files at the root of my c drive for simplicity

Step 3)

Execute the setup.exe passing the parameters:

I been getting a lot of errors. Can someone show me how can I have a configuration file in order to install sql server express edition with the administrator as the main user.I don't want the user installing the application to have to go throughout that trouble. I found a nice link that teaches you how to create a configuration file in here but I still am not able to do it. I am using windows xp 32 bit. I managed to installed it on the virtual machine manually. I will like to be able to install sql server express from the command line or with a configuration file.

Unfortunately, you cannot install SQL Express without Administrator rights on the machine and it's not something you can set in the config file or on the command line. If being able to install without Administrator rights is a requirement, you may want to investigate using SQL Server Compact Edition.

See the following MSDN topics for more information:

Using SQL Server Compact 3.5 (Visual Studio)
How to: Deploy a SQL Server Compact 3.5 Database with an Application
SQL Server Compact 3.5 SP2 Books Online
Local Data Overview
Choosing Between SQL Server 2005 Compact Edition and SQL Server 2005 Express Edition -- A little dated now, but much of it still applies.


Missing "public" schema when converting a "regclass" value to text I am writing a script in which I need to parse the name of a table (in regclass). The parsing (with parse_ident()) works so far. However, the script fails when the table is in the public schema because PostgreSQL (10.3) automatically removes the schema name.

For example, if a table tt is in a non-public schema ex, the text value of the regclass is the same as the original:

=> select 'ex.tt'::regclass::text;
 text
-------
 ex.tt

When it's in public, the schema name is lost:

=> select 'public.tt'::regclass::text;
 text
------
 tt

Is there an way to disable this behavior, or to convert to text without losing the schema name?

This is not related to the schema public per se - which is just another schema with no special powers except that it's created by default - and included in the search_path by default.

The current search_path is behind this. The text representation of a regclass value builds on it. The table name is only schema-qualified if the schema is not the first match in the search_path anyway. The manual:

  The regclass input converter handles the table lookup according to the
  schema path setting, and so it does the “right thing” automatically.
  Similarly, casting a table's OID to regclass is handy for symbolic
  display of a numeric OID.

You could force Postgres to print the schema name by setting an empty search_path (locally):

SET LOCAL search_path = '';
SELECT 'public.tt'::regclass::text;

But that forces you to schema-qualify everything for the rest of the transaction or until you set the search_path again. Well, most everything, pg_catalog and pg_temp are special. See:

How does the search_path influence identifier resolution and the “current schema”

Or you avoid the special cast and the search_path and get the schema name from pg_class directly:

SELECT relnamespace::regnamespace::text
FROM   pg_catalog.pg_class
WHERE  oid = 'public.tt'::regclass;


Does the Amazon RDS backup/snapshot service lock tables? We have a database that is 100+GB.  Most tables use the InnoDB engine.

A regular dump takes 1-2 hours.

If we moved to Amazon RDS, would this backup cause table locks?

Does Amazon use a simple mysqldump?

If there are any MyISAM tables present, as your wording implies, the RDS Documentation instructs you on how to handle it and what to expect

According to the RDS Documentation :

  Automated Backups with Unsupported Storage Engines

  Amazon RDS automated backups and DB Snapshots are currently supported
  for only the InnoDB storage engine. Use of these features with other
  MySQL storage engines, including MyISAM, may lead to unreliable
  behavior while restoring from backups. Specifically, since storage
  engines like MyISAM do not support reliable crash recovery, your
  tables can be corrupted in the event of a crash. For this reason, we
  encourage you to use the InnoDB storage engine.

  If you choose to use MyISAM, you can attempt to manually repair tables
  that become damaged after a crash by using the REPAIR command ((see:
  http://dev.mysql.com/doc/refman/5.5/en/repair-table.html). However, as
  noted in the MySQL documentation, there is a good chance that you will
  not be able to recover all your data.

  If you want to take DB snapshots with MyISAM tables, follow these
  steps:

  Launch Process

  1 Stop all activity to your MyISAM tables (that is, close all
  sessions)

  2 Lock and flush each of your MyISAM tables

  3 Issue a
  CreateDBSnapshot API call, or use the RDSCLI rds-create-db-snapshot
  command. When the snapshot has completed, release the locks and resume
  activity on the MyISAM tables. These steps force MyISAM to flush data
  stored in memory to disk thereby ensuring a clean start when you
  restore from a DB snapshot.

  Finally, if you would like to convert existing MyISAM tables to InnoDB
  tables, you can use alter table command (for example, alter table
  TABLE_NAME engine=innodb;).

Believe me, large MyISAM tables have no place in Amazon RDS. InnoDB is far more accepted. Please, either convert them to InnoDB or live with doing your own locking and possible table crashes/repairs.


ORA-28040: No matching authentication protocol I'm trying to connect to my RAC database using odbc drivers. While trying I get

  ORA-28040: No matching authentication protocol

I have added this in both client and the server:

SQLNET.ALLOWED_LOGON_VERSION=8

I have also tried by replacing the above mentioned deprecated parameter with the server and client. I still get the same error.

More details:

Database version 12c
Client version 12c

This may be Oracle client/server version compatibility issue.

  From java.sql.SQLException: ORA-28040: No matching authentication protocol

    This happens because you are using a non compatible version of JDBC driver.

    To resolve this issue, make sure that you are using the latest version of Oracle JDBC driver, if not use JDBC 12c or higher versions.


Unique constraint insert prevention I've created a unique constraint to my table containing employee account details in an attempt to stop duplicated data inserts.

ALTER TABLE emp ADD CONSTRAINT uq_ UNIQUE (USERNAME, EMP_NAME);

However my problem comes when it comes inserting the test data:

----this first insert should work fine
INSERT INTO table_name (id, username, emp_name, regdate)
VALUES (1, 'test', 'test1', sysdate);

What I'm trying to prevent is an instance like this were the data about an employee is inserted twice into the table:

INSERT INTO table_name (id, username, emp_name, regdate)
VALUES (2, 'test', 'test2', sysdate);

As at the moment the insert runs and adds to the table which I'm trying to prevent it from doing.

Is there a way to combat this, would a trigger be a better option to use?

Creating a proper unique constraint on your table will prevent duplicates amongst the columns specified.

Your naming of the constraint should be unique to the table itself.  All constraints for a database are listed under the pg_constraint table.

ALTER TABLE emp ADD CONSTRAINT uq_emp_username_emp_name UNIQUE (USERNAME, EMP_NAME);

There are quite a few docs on the web you can find about standardized SQL naming formats.

To do a check against both the USERNAME and EMP_NAME, you would need to specify constraints individually for each.

ALTER TABLE emp ADD CONSTRAINT uq_emp_username UNIQUE (USERNAME);
ALTER TABLE emp ADD CONSTRAINT uq_emp_emp_name UNIQUE (EMP_NAME);

This would prevent employees from creating multiple usernames under the same employee name.

I find that the PostgreSQL official documentation is super concise and well written.
https://www.postgresql.org/docs/9.5/static/ddl-constraints.html
Just make sure you have your appropriate Postgres version selected.


MySQL FULL JOIN on same table I have a table like this:

And I want to get something like:

fecha       siguiendo    seguidores
2016-01-02    430           1430
2016-01-03    450            NULL
2016-01-04    NULL           5000

I have two types of data seguidores and siguiendo. I want a column for each of them.

I have tried to emulate a FULL JOIN on the same table but I didn't get the expected result. Any ideas?

This is called a pivot

SELECT fetcha,
SUM(CASE WHEN Dato='siguiendo' THEN valor END) as siguiendo,
SUM(CASE WHEN Dato='seguidores' THEN valor END) as seguidores
FROM t
GROUP BY fetcha


The transaction log for database 'tempdb' is full but it should have grown I have a SQL Server 2008 R2 that started showing that the TempDB transaction log was full. I checked the log file settings and there was no growth restriction and there was plenty of disk space.

I had no luck on finding an explanation of what could have caused this.

Has anyone experienced this in the past?

Thank you.

I've seen this occur when the file growth setting is set too high, so the growth event takes too long. Obviously this depends on your storage environment and performance details.

In my case, the 10% default growth rate was fine when the database was small, but at some point when the database got huge enough that the growth event took over 30 seconds, which was long enough that the original query timed out and rolled back. The rollback then cancelled the growth event, until the application re-tried the same query, and we got an endless loop.

So check your growth rates, or just pre-size your tempdb to a larger size.


Adding column to Postgres table fails I have a Postgres (8.4.20) table that I'm supposed to add columns to, but it seems to have some problems that I have difficulties to debug:

I'm attempting what I thought was a simple ALTER TABLE... on it:

db_name=> ALTER TABLE "marking" ADD COLUMN published TIMESTAMP WITHOUT TIME ZONE;
ERROR:  canceling statement due to statement timeout

I'm puzzled, because:

Although the table has (very little) data in it, it's not a NOT NULL column
I've also tried to dump the table, delete it's contents and re-run without data in it

But it still gives the same result. Here's the current schema for the table:

db_name=> \d marking
           Table "public.marking"
 Column |         Type          | Modifiers
--------+-----------------------+-----------
 link   | character varying(32) | not null
 hash   | character varying(40) | not null
 url    | text                  | not null
 blob   | text                  | not null
Indexes:
    "marking_pkey" PRIMARY KEY, btree (hash)
    "ix_marking_link" btree (link)

Sorry for the newbie question, but there's clearly something fundamental I don't understand here and I'm having some trouble getting further.

I've tried to increase statement_timeout and work_mem, but even very large values won't help.

The alter table was probably waiting to get an exclusive lock on the table. If you have transactions touching the table, the ALTER TABLE will wait until all those transactions are committed

You can check if your statement is waiting for such a lock by looking at pg_stat_activity and pg_locks.

If you have sessions that are shown as idle in transaction those are a potential cause for that.

Unrelated, but: Postgres 8.4 is no longer supported (=maintained). You should plan your upgrade to a supported version (9.3 or 9.4) as soon as possible. If you have to stick with 8.4 you should at least be running the latest 8.4 version which is 8.4.22