Postgres deadlock in INSERT VALUES concurrent with ON DELETE CASCADE I create tables like this:

CREATE TABLE tgroups (
    group_id text PRIMARY KEY,
    vacancy_id text
);
CREATE INDEX tgroups_vacancy_id_idx ON tgroups (vacancy_id);

CREATE TABLE tscores (
    application_id text NOT NULL,
    group_id text NOT NULL,
    CONSTRAINT tscores_pkey PRIMARY KEY (application_id, group_id),
    CONSTRAINT tscores_group_id_fkey FOREIGN KEY (group_id) REFERENCES tgroups(group_id) ON DELETE CASCADE
);
CREATE INDEX tscores_group_id_idx ON tscores(group_id);

I then have two processes executing SQL transactions in very rapid succession. Both have the standard READ COMMITTED isolation level. The first process is running this, except the values in the INSERT...VALUES are in a random order (I've done it in Python):

BEGIN;
    DELETE FROM tgroups WHERE vacancy_id = 'vacancy1';

    INSERT INTO tgroups (group_id, vacancy_id) VALUES
    ('group1', 'vacancy1'),
    ('group2', 'vacancy1'),
    ...
    ('group10', 'vacancy1');
COMMIT;

The second process runs the following SQL. Again, the values in INSERT...VALUES are in a random order. The variable :application_id is chosen from the set ["application1"..."application10"], which I've done with Python (I guess you could achieve the same in pgsql):

BEGIN;
    DELETE FROM tscores WHERE application_id = :application_id;

    INSERT INTO tscores (application_id, group_id) VALUES
    (:application_id, 'group1'),
    (:application_id, 'group2'),
    ...
    (:application_id, 'group10');
COMMIT;

This sometimes deadlocks:

sqlalchemy.exc.OperationalError: (psycopg2.extensions.TransactionRollbackError) deadlock detected
DETAIL:  Process 527 waits for ShareLock on transaction 391860; blocked by process 526.
Process 526 waits for ShareLock on transaction 391861; blocked by process 527.
HINT:  See server log for query details.
CONTEXT:  while deleting tuple (2,218) in relation "tgroups"
 [SQL: "DELETE FROM tgroups WHERE vacancy_id = 'vacancy1';"]

I can only reproduce the deadlock if both of the following are true:

The deletes in tscores from the first transaction are carried out behind the scenes by ON DELETE CASCADE. If I remove the constraint and manually delete the rows in question at the beginning of the transaction there is no deadlock
The values inside the insert statement are in a random order

I was under the impression that both DELETE and INSERT...VALUES were atomic, and that ON DELETE CASCADE happened at the same time as the DELETE it is related to. Therefore I'd have thought that if one transaction was already in process, the blocked transaction would block on DELETE until the other transaction finished, and then continue, and indeed this is what seems to happen if I manually run the two transactions line-by-line in separate psql sessions.

Somehow I clearly misunderstand the way that locks, inserts, foreign keys and cascades interact.

NB I am aware sometimes the INSERT into tscores will sometimes fail because the group that is referenced has just been deleted, but this isn't my problem (though I guess it could be related).

I would not worry about the behavior, just retry the transaction if terminated by the deadlock.

The first transaction tries to delete A and B, and acquires sequentially a lock on them, and the second deletes B and A, with the same approach. Upon locking A and B, none of the two transactions can progress, hence one of them is terminated by a deadlock.


'bsondump --pretty --type=debug' use case and output explanation sample output with type debug

bsondump --pretty --type=debug audit.bson.2017-03-10T10-38-23
--- new object ---
        size : 236
                atype
                        type:    2 size: 28
                ts
                        type:    9 size: 12
                local
                        type:    3 size: 37
                        --- new object ---
                                size : 30
                                        ip
                                                type:    2 size: 15
                                        port
                                                type:   16 size: 10
                remote
                        type:    3 size: 38
                        --- new object ---
                                size : 30
                                        ip
                                                type:    2 size: 15
                                        port
                                                type:   16 size: 10
                users
                        type:    4 size: 53
                        --- new object ---
                                size : 46
                                        0
                                                type:    3 size: 41
                                                --- new object ---
                                                        size : 38
                                                                user
                                                                        type:    2 size: 19
                                                                db
                                                                        type:    2 size: 14
                roles
                        type:    4 size: 12
                        --- new object ---
                                size : 5
                param
                        type:    3 size: 39
                        --- new object ---
                                size : 32
                                        ns
                                                type:    2 size: 27
                result
                        type:   16 size: 12
--- new object ---

What's --- new object ---? It seems not to be a JSON document.
What's type and the number? Is size characters or other measurement?

Why this output is useful? What's the usecase for DBA or App developer?

The bsondump utility converts BSON data (which is binary) into human-readable formats. Common usage would be bsondump --pretty to inspect data formatted as MongoDB Extended JSON. This can be useful for previewing data contained in BSON files as created by mongodump, replica set rollbacks, and other features (eg. auditing in MongoDB Enterprise as per your example).

The --type=debug output is generally only relevant for a developer who wants to see an annotated view of the BSON (as per the BSON spec).

  What's --- new object ---? It seems not to be a JSON document.

This indicates the start of a new BSON object, which would be represented as an opening curly brace ( { ) in JSON. You will see this at the top level for each new document and for every embedded document/array.

  What's type and the number? Is size characters or other measurement?

This indicates the BSON type and size (in bytes) of that object including nested fields/objects.

Potential use cases for confirming underlying data types might be checking for consistency (eg. a given field has the expected type) or  confirming underlying data types in order to do a query using the  $type operator in MongoDB.

You can perform a similar size calculation for a document/object in the mongo shell using Object.bsonsize(object).


Installing Temporal Tables extension (contrib extension) on windows I am trying to install this Temporal Tables extension into my localhost installation of PostgreSQL. (pg9.4, windows 7)

I have figured out I needed to put the files into the 9.4/share/contrib folder (currently the files are at 9.4/share/contrib/temporal_tables-1.0.2) but I cannot figure out how to make CREATE EXTENSION work. I know I have to do the windows equivalent of "make install", and I would like to avoid needing to use Visual Studio if I can.

Since you are on Windows, you also have to copy the compiled DLLs to your PostgreSQL lib directory. Step-by-step instructions are in the manual.


What are "sys.extended_index..." indexes? A CHECKDB run reports corruption in sys.extended_index_1066482673_384000. What kind of index are represented by the name pattern sys.extended_index_...? The message is:

  The spatial index, XML index or indexed view 'sys.extended_index_1066482673_384000' (object ID ...) does not contain all rows that the view definition produces.  This does not necessarily represent an integrity issue with the data in this database. For more information about troubleshooting DBCC errors on spatial indexes, XML indexes, and indexed views, see SQL Server Books Online.

Extended Indexes are full text indexes, XML indexes, and Spatial indexes.  Reason, the 3 types of indexes are better handled outside the B+ tree index.

I know you do not care to correct this but, did you recently upgrade?  Try changing the compatibility level if it's not SQL Server 2014 and run CHECKDB again

You can identify the table object for the extended index error by using the below code. You may need to tweak the name in the where clause if I have it wrong.

SELECT
    OBJECT_NAME(PARENT_OBJECT_ID) AS table_object,
    name,
    create_date
FROM sys.internal_tables it
WHERE INTERNAL_TYPE = 207
AND it.name = 'extended_index_1066482673_384000'

Hope this helps!


Query MongoDB Collection Compression Settings? How can i query the settings for collection compression in MongoDB?
I configured WiredTiger as storage engine and as collection compression the zlib algorithm.

Here is a snippet of my configuration YAML file:

storage:
   dbPath: "/data/wiredTiger"
   engine: "wiredTiger"
   wiredTiger:
      collectionConfig:
         blockCompressor: "zlib"

Using mongo version 3.0.5.

Any help please

found the solution by myself.

use db.printCollectionStats()

in the output you will find the mentioned algorithm in the "wiredTiger.creationString" section.

"wiredTiger" : {
            "metadata" : {
                    "formatVersion" : 1
            },
            "creationString" : "allocation_size=4KB,app_metadata=(formatVersion=1),block_allocation=best,block_compressor=zlib,cache_resident=0,checkpoint=(WiredTigerCheckpoint.1=(addr=\"018181e4de542a6a8281e4f2e09da0808080808080e21fc0dfc0\",order=1,time=1440667794,size=8192,write_gen=2)),checkpoint_lsn=(2,25344),checksum=on,collator=,columns=,dictionary=0,format=btree,huffman_key=,huffman_value=,id=15,internal_item_max=0,internal_key_max=0,internal_key_truncate=,internal_page_max=4KB,key_format=q,key_gap=10,leaf_item_max=0,leaf_key_max=0,leaf_page_max=32KB,leaf_value_max=64MB,memory_page_max=10m,os_cache_dirty_max=0,os_cache_max=0,prefix_compression=0,prefix_compression_min=4,split_deepen_min_child=0,split_deepen_per_child=0,split_pct=90,value_format=u,version=(major=1,minor=1)",
            "type" : "file",

just look for block_compressor


SUM (CASE WHEN) AS.. query I'm very new to SQL and I'm trying and failing to get the right syntax on what I presumed would be a pretty easy query.

I have a table (CustomerSales) with four columns:

Period: a combination of year and month, e.g. 201501 is January 2015;
Customer_no;
Sales;
Class, i.e. customer segment.

I currently have the below query which extracts all sales in 2015 for class 'R', by customer number:

select Customer_no, class,
SUM (Sales) as '2015'
from CustomerSales
where left(Period,4) = '2015' and class = 'R'
Group by Customer_no, class

This returns a simple 3-column output of Customer_no, class and 2015 Sales.

I would like to add columns for each year in the table, i.e. add five more columns for each year 2010-2014.

To do this, I tried to move the left(Period,4) = '2015' expression into a case when condition and then my intention was to repeat this for 2014, 2013, etc. But the query fails before I even try to include the other years. The current query (with just 2015) is:

select Customer_no, class,
SUM (case when(left(Period,4) = '2015' then Sales else 0 end)) as '2015'
from CustomerSales
where class = 'R'
Group by Customer_no, class

The error I get is

  Incorrect syntax near the keyword 'then'.

I'd be grateful for any help as to how to fix the above query and add the 2010-2014 sales as extra columns.

You simply need to balance your ().

Either delete the ( between when and left or add a ) after '2015'.


PostgreSQL - SSL connection without providing the root-ca certificate? I set the following parameters to enable SSL on my PostgreSQL server:

ssl = True
ssl_ca_file = 'path-to-root-ca'
ssl_cert_file = 'path-to-server-crt'
ssl_key_file = 'path-to-server-key'

For my tests, I use the PGSSLROOTCERT variable which contains the path to the root ca certificate.

I realized that it is still possible for a client to connect to the database without providing the root CA certificate and that the prompt states that an SSL connection is used:

psql "postgres://user:XXXXXX@hostname:port/postgres"
psql (10.2, server 10.3 (Debian 10.3-1.pgdg90+1))
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.

postgres=# \q

psql "postgres://user:XXXXXX@hostname:port/postgres?sslmode=verify-ca"
psql (10.2, server 10.3 (Debian 10.3-1.pgdg90+1))
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.

postgres=# \q

If the PGSSLROOTCERT variable is set to a wrong value:

psql "postgres://user:XXXXXX@hostname:port/postgres?sslmode=verify-ca"
psql: SSL error: certificate verify failed

The pg_hba.conf file:

local  all  postgres    trust
hostssl  all  all  0.0.0.0/0  md5

In the absence of PGSSLROOTCERT or sslrootcert, the library libpq (which psql uses) looks in a compiled-in default location for the file.  And apparently, it finds it at that location.

For Linux, that location is ~/.postgresql/root.crt.  Although it is possible that "Debian 10.3-1.pgdg90+1" patched it to look someplace else instead.


Migrating SQL Server 2000 to 2012, getting old versions of Views (edit: offender SQL Server 2008 R2) I'm migrating an ancient SQL Server 2000 database to 2012 (with a quick pass through 2008 R2 since 2012 is not compatible with 2000). The DB contains hundreds of views and stored procedures, and in my testing I'm now starting to see that some views are migrated in some old version. I did not even know that there were two (or more) versions of views in 2000, but it seems so.

In the cases I've found, so far, the effected views has been using the CASE-select, and I'm starting to think that it has been edited by using a "double click on the view" rather than "design". The version that is migrated to 2012 (and 2008) is clearly from a few years old, and after resaving the offending view in design-mode in 2000, and migrating again, it gets the correct version.

But I can't do this manually for hundreds of views. Is there any way to force an version-sync of sorts in 2000? Any other ideas on a solution is much welcomed.

[Edit/Additon]

I've kept testing, and I have now narrowed down where the problem occurs. The SQL Server 2000 backup restores fine in SQL Server 2008 R2, and the view (in question) is up to date. But then I run a few checks and updates, and after running sp_refreshview on all views, it reverts back to an old version (that was not even visible in 2000 this time).

In summary: why does sp_refreshview, from SQL Server 2008 R2, revert some of my views back a this-was-the-original-design-of-the-view-in-2001 state?

Having found more info I had an easier time finding similar problems: http://sqlblog.com/blogs/davide_mauri/archive/2010/10/12/bug-with-sp-rename-sp-refreshview.aspx
But I'm still unsure on how to fix this issue since I don't know how many views are effected with this "bug".

So I finally found the error, and I'll just have to cross my fingers that this was a lone occurance.

The view that kept reverting, after a sp_refreshview in SQL2008 r2, was named "qryCreateSnapshot". Then there was another view, a backup of sorts, named "qryCreateSnapshot_Old". The problem was that in the old view, the body was still "CREATE VIEW dbo.qryCreateSnapshot". I have no idea why this even compiled, but there you have it.

So when I refreshed in 2008, I guess the new view got overwritten with the old. This did not happen in 2000.

Now to compare hundreds of view names with their respective body name... ;-)

For anyone in a smiliar situation, here is a small query to find mismatches between header name and body name of a view:

SELECT
  O.name AS HeaderViewName,
  RTRIM(SUBSTRING(C.text, 17, LEN(O.name))) AS BodyViewName,
  CASE
     WHEN RTRIM(SUBSTRING(C.text, 17, LEN(O.name))) = O.name THEN 'Yes'
     ELSE 'Nope, fix body'
  END AS Match,
  C.text AS SourceText
FROM sysobjects O
INNER JOIN syscomments C
   ON O.id = C.id
WHERE O.type = 'V'
AND   LEFT(C.text, 12) = 'CREATE VIEW ';

I know it's not perfect, but it worked for my data and I found another ~15 mismatches.


Column name or number of supplied values does not match table definition error I have a table, with two foreign keys defined on two columns of that table. The problem is that, when I insert the values from SQL Server, I get the following error:

  Column name or number of supplied values does not match table definition

Please see the below image for the definition of the table. Can you please tell me why the value is not getting inserted?

You're not showing us what you're doing - but based on the table structure, this is what you should do:

create an INSERT statement that explicitly lists the columns it will insert into - assuming that ID might be an IDENTITY column that you don't want / can't insert into
define the exact number of values to fill into these columns

So your INSERT statement should be something like:

INSERT INTO dbo.tbl_Post (cat_id, ngo_id, title, description, active)
VALUES (42, 4711, 'Some title', 'Some description', 1)

What you should definitely get in the habits of avoiding is using INSERT INTO dbo.tblPost without explicitly defining the list of column to insert into. This is just a recipe for disaster, as soon as you change your table definition, all your existing INSERTs will break since they don't match the table definition anymore.

Therefore: always explicitly define the list of columns that an INSERT statement should fill data into!

Also see Aaron Bertrand's excellent blog post on the topic:
Bad habits to kick: using SELECT * / omit the column list


How to split table records into aggregate and array in PostgreSQL I have a table max_values in my PostgreSQL 9.5 database containing four columns.

id  values  lower_limit   upper_limit
1   15.3    112           142
2   18.1    142           172
3   17.5    172           202
4   16.8    202           232
5   15.7    232           262
6   19.3    262           292
7   21.4    292           322
8   21.4    322           352
9   21.4    352           382
10  21.4    382           412
11  21.4    412           442
12  21.4    442           472

with table structure as follows:

Create Table max_values as
(
id integer,
values numeric,
lower_limit numeric,
upper_limit numeric
);

Each 'ID' is unique identifier of 'values'. I need to split above table records into an aggregate and an array based on ID (expected output: 1 row and 2 columns). What I need to do is:

get the most prevalent/most repetitive value in 'values'
count IDs of 'values' other than most repetitive value
return the total ID count, lower limit, value and upper limit in an array
if there is no repetitive value then first column could be zero

The desired output could be like:

Most_repetitive    Remaining
21.4               6, 112, 15.3, 142, 142, 18.1, 172, 172, 17.5, 202, 202, 16.8, 232, 232, 15.7, 262, 262, 19.3, 292

Being a newbie, I am aware of some PostgreSQL functions such as mode () etc. but I would highly appreciate if someone could point me how to get the desired output for my table records?

The first part is pretty easy:

select values,count(*) from max_values group by values order by 2 desc limit 1

The second part you could the ARRAY function to transform rows for all values that aren't equal to the one above into one long array, though that is honestly a little tough to do in one SQL statement. Does it really have to be one long array like that? What are you going to do with it?  There is an example of ARRAY() here:

https://blog.lerner.co.il/turning-postgresql-rows-arrays-array/


How do I do a complex GROUP BY in MySQL? I have a table that contains several keys into other tables (where each key is comprised of multiple columns).  I would like to be able to group rows together that have an equal key, but I don't want to group all of them together.  It's not a simple GROUP BY on the key but rather I want to be able to make groups of say 10.  So if a particular key showed up 50 times I would get 5 results when I do this grouping (5 groups of 10).  I also want this grouping to occur randomly within the key.

I didn't know of the direct way to do this, and the roundabout method I came up with isn't working like I think it should.  The roundabout solution I came up with was to create a new column for each key that would be an integer such that value i represents the ith occurrence of that key (but in random order).  I could then do integer division so that every n (say 10) rows within the key have the same value, and I could do a GROUP BY on that value.

Is there a more direct way to accomplish what I just described?  It's quite awkward, and I ran into problems in creating the new index column (as I described in this question).

EDIT: First of all note that this is for MySQL.  I'll add an example in case my goal is not clear.  The MySQL docs show a method to get almost there:

CREATE TABLE animals (
    grp ENUM('fish','mammal','bird') NOT NULL,
    id MEDIUMINT NOT NULL AUTO_INCREMENT,
    name CHAR(30) NOT NULL,
    PRIMARY KEY (grp,id)
) ENGINE=MyISAM;

INSERT INTO animals (grp,name) VALUES
    ('mammal','dog'),('mammal','cat'),
    ('bird','penguin'),('fish','lax'),('mammal','whale'),
    ('bird','ostrich');

SELECT * FROM animals ORDER BY grp,id;

This creates a table which, although not what I want, gets close:

+--------+----+---------+
| grp    | id | name    |
+--------+----+---------+
| fish   |  1 | lax     |
| mammal |  1 | dog     |
| mammal |  2 | cat     |
| mammal |  3 | whale   |
| bird   |  1 | penguin |
| bird   |  2 | ostrich |
+--------+----+---------+

I would essentially like to GROUP BY id, except I would want the records with mammal to have one "group" for IDs 1-10, another "group" for IDs 11-20, etc.  However, I would be doing this with an existing table, and I wouldn't necessarily want "dog" to show up with ID 1.  I would want that initial ordering to be random, but then deterministic from then out.

What about doing a little math against your ID column to dynamically generate the group?

SELECT grp, FLOOR(id/10) AS id_grp
FROM animals
GROUP BY grp, id_grp

This would give you groups of 10 based on the ID of the record.  I used your animals table above to generate the data below.

Sample data

 INSERT INTO animals VALUES
 ('mammal',10,'dog'),('mammal',11,'dog'),('mammal',12,'dog'),
 ('mammal',21,'cat'),('mammal',22,'cat'),('mammal',23,'cat'),
 ('mammal',24,'cat'),('mammal',25,'cat'),('mammal',26,'cat'),
 ('bird',30,'penguin'),('bird',31,'penguin'),('bird',32,'penguin'),
 ('bird',33,'penguin'),('fish',44,'lax'),('fish',45,'lax'),
 ('fish',46,'lax'),('fish',47,'lax'),('fish',48,'lax'),
 ('mammal',31,'whale'),*'fish',51,'lax'),('fish',52,'lax'),
 ('fish',53,'lax'),('fish',54,'lax'),('bird',10,'ostrich');

Query Output

 +--------+--------+
 | grp    | id_grp |
 +--------+--------+
 | fish   |      4 |
 | fish   |      5 |
 | mammal |      1 |
 | mammal |      2 |
 | mammal |      3 |
 | bird   |      1 |
 | bird   |      3 |
 +--------+--------+
 7 rows in set (0.00 sec)


Is having SQL Server express database on client better than querying data on a remote server? I'm planning to create a SQL Server Express database on each client machine that will sync with the remote server's data. What I'm trying to achieve is to have a faster processing and querying of data wherein the client application connects to its local db rather than directly connecting to the remote server.

Do you think that this is a good approach? Or should I just stick to the client-server approach wherein all I need to do is have 1 remote db server where the clients connect to it?

Do you think that this is a good approach?

How does "totally rotten" sound?

Seriously - there MAY be some reasons to do that (distributed systems on slow links for example) but this is the first time I hear someone trying to replicate a database to every client to avoid overloading a server. Totally out of whack unless specific circumstances call for it.

One example - one that we have:
* A trading application on a remote location reporting it's trades to central processing via replication

Reason: the link can go down and the trading app still works. It is isolated from corporate central.

ANother example we had:
* A CRM - installed on laptops and needed to work without permanent internet connectivity.

But normally this is not a decent solution. YOu may have specific circumstances, but you totally avoid naming them.

Go with a decent central database machine and it should be fine.


PostgreSQL GiST for compressed entry type I am using PostgreSQL 9.5 and I'm trying to understand how to implement a GiST index where I have a representation that is a lossily compressed version of the indexed type. For instance, say I have images stored in BYTEA type, and for the index I store the colour ranges (rmin, rmax, gmin, gmax, bmin, bmax), and I want to compare images based upon colour similarity - e.g. with a === operator that returns true when the colours ranges are exactly the same, allowing me to facilitate queries like:

SELECT COUNT(*)
FROM icons, avatars
WHERE icon.image === avatar.image AND avatar.id = 123;

where icons and avatars are both tables with an image field of type BYTEA.

Having looked through the implementation documentation is looks like this should be possible. Using the above example situation, I think I could do as follows:

the union method would generate the bounding range of all entries
picksplit and penalty would just try to minimise the ranges,
similar to a R-Tree
compress would take the BYTEA data and calculate the colour range
decompress would be an identity function
consistent (for the === operator) would return true if the entry's colour range contained the query range for internal nodes, and only if the ranges exactly matched for leaf nodes.

Is this the right approach? I'm not clear about when the compression steps take place. For instance, consistent is presumably called multiple times on different nodes of the tree. So does this mean that the query will have re-calculate the colour range of the query data every time? And in the index, will the leaf nodes contain a copy of the image data or just its colour range?

Note The example given is just for illustrative purposes. My question is about lossy representations in GiST, not indexing images.

Since you already store the values (rmin, rmax, gmin, gmax, bmin, bmax) for the image column, a btree index on those covers equality checks just fine:

CREATE INDEX foo1 ON icons (rmin, rmax, gmin, gmax, bmin, bmax);

This query will use the index:

SELECT COUNT(*)
FROM   avatars a
JOIN   icons   i
WHERE  a.id = 123
AND   (a.rmin, a.rmax, a.gmin, a.gmax, a.bmin, a.bmax)
    = (i.rmin, i.rmax, i.gmin, i.gmax, i.bmin, i.bmax);

Of course, you need another index on avatars.id, or possibly on (id, rmin, rmax, gmin, gmax, bmin, bmax) to allow index-only scans.


Why does a query on a local instance with local database files cause NETWORK_IO waits? I have followed this tutorial to get the wait stats of a particular query while it is 'in flight' using extended events. Whilst the tutorial refers to an index rebuild, I just did a select statement from a single table

The waits (using the query in the tutorial) were

Wait Type            Wait Count  Wait Time  Resource Wait Time  Signal Wait Time
-------------------  ----------  ---------  ------------------  ----------------
NETWORK_IO                  693        778                 776                 2
SOS_SCHEDULER_YIELD           6          0                   0                 0

I ran the query in SSMS on the same box the instance is installed on (used . as my server name when connecting)
the data files for the database are on the C:/ drive of the box so I am confused as to why NETWORK_IO is showing?

As a further test, I disabled the network adapter on the box and found the same result (obviously with slightly different numbers)

Would I be right in thinking that NETWORK_IO relates to SQL server returning the result to SSMS?

Usually this surfaces as ASYNC_NETWORK_IO outside of Extended Events, but yes, it is the time SQL Server spent waiting for the caller (in this case SSMS) to consume the results. If the caller is slow (like SSMS) then SQL Server has to sit there and wait before feeding more rows. If you have a wide result set or large values, you should compare results to grid and results to text. But really, you shouldn't be timing anything based on response times in SSMS, since it does unrealistic things with the data and how it renders that your application won't be doing (hopefully).

I wrote about this wait type (and 9 other common ones) here:

What to do (or not do) about top wait stats


SUSPECT Status Of Databases, but Ola Hallengren backup job completed SUCCESSFULLY We are using the SQL Server Backup solution from Ola Hallengren in our environment for SQL Servers. Today, there were 2 database which were in SUSPECT mode and a backup was not possible for them. A job is scheduled to take backup of these databases (two of which were in Suspect mode) and one another database. The backup of the online DB got completed since it was first in the Alphabetical order and for the other two, it failed. However, the job did not fail and showed the job as successful! The job should have failed if it fails to take the backup of any database.

https://i.stack.imgur.com/n8Ww1.png

Message from the Log:

Date and time: 2018-03-18 03:12:55
Database: [ABC]
Status: SUSPECT
Standby: No
Updateability: READ_WRITE
User access: MULTI_USER
Recovery model: FULL
Differential base LSN: 5334862000009909000054
Last log backup LSN: N/A

Date and time: 2018-03-18 03:12:55
Database: [CDE]
Status: SUSPECT
Standby: No
Updateability: READ_WRITE
User access: MULTI_USER
Recovery model: FULL
Differential base LSN: 4543119000022597000098
Last log backup LSN: N/A

Date and time: 2018-03-18 03:12:55

One more scenario, where the DB backup fails but it does not report it as [FAILED] in the Log unlike to when it logs when the DB backup is successful - Outcome: Succeeded.

How can we get the information of failed backups from the logs? There is no Tag or Keyword which can be searched for when looking for failed backups in the Log.  There are only incomplete command statements which can tell that the backup has failed and for that you have to search the whole log and any kind of reporting is not possible where we can use the concept of keyword searching in log content for failures.

It would be great if you can please let us know what is the solution to these.

Your problem was easily reproduced.

I used the code in Creating a SUSPECT Database to create a suspect database on my local instance and then ran Ola's backup script.  The script executed without error (as you described).  The output did report the database as being suspect, but the script did not error.

Date and time: 2018-03-18 06:20:07
Database: [DemoSuspect]
Status: SUSPECT
Standby: No
Updateability: READ_WRITE
User access: MULTI_USER
Recovery model: SIMPLE
Encrypted: No
Differential base LSN: N/A
Last log backup LSN: N/A

In addition to not backing up suspect databases, there are (at least) Three cases where Ola Hallengren's Maintenance Solution won't backup a database.

A Major takeaway from this post is:

  You should always always double-check either the msdb backup
  history
  or the master.dbo.CommandLog table to make sure any important backup
  was taken.

There are a variety of ways to query successful backup using the samples in Script to retrieve SQL Server database backup history and no backups.  An example would be to look for any databases that haven't been backed up today.

SELECT d.NAME
    ,bck.last_db_backup_date
FROM sys.databases d
LEFT JOIN (
    SELECT CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS SERVER
        ,msdb.dbo.backupset.database_name
        ,MAX(msdb.dbo.backupset.backup_finish_date) AS last_db_backup_date
    FROM msdb.dbo.backupmediafamily
    INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
    WHERE msdb..backupset.type = 'D'
    GROUP BY msdb.dbo.backupset.database_name
    ) bck ON bck.database_name = d.NAME
WHERE bck.last_db_backup_date IS NULL
    OR convert(DATE, bck.last_db_backup_date) <> convert(DATE, sysdatetime())

My suggestion would be to add an extra job step to verify that all databases are being backed up and force the job to abend if a problem situation is detected.


SQL Select where value = to another select I have two tables. Orders, and OrderParts. One Order can have many OrderParts.

I want a list of all the order.customerID where the number of OrderParts ordered = Total_number_parts in the order table.

Below is where i have got to so far, but where do i put the where sum(partqty)=total_number_parts bit!!

Select a.customerID, a.Total_number_parts
From Orders a
Join (Select sum(PartQty) from OrderParts Group by customerID) B
   on B.customerID = a.customerID
Group By a.CustomerID

You can use a CTE for this work:

WITH ctSum AS
(
    SELECT   customerId, SUM(PartQty) as TotalQty
    FROM     OrdersParts
    GROUP BY customerID
)
SELECT a.customerID, a.Total_number_parts
FROM   Orders a
JOIN   ctSum b
ON     b.customerID = a.customerID
WHERE  a.Total_number_parts = b.TotalQty;

Or if you prefer another syntax:

SELECT a.customerID, a.Total_number_parts
FROM   Orders a
JOIN   (SELECT   customerId, SUM(PartQty) as TotalQty
        FROM     OrdersParts
        GROUP BY customerID) b
ON     b.customerID = a.customerID
WHERE  a.Total_number_parts = b.TotalQty;


Unable to open/connect to Oracle 12c Enterprise Manager Express I have a just installed a new single instance installation of Oracle 12c (12.1.0.2.0 on Windows x64) with ASM.  The database itself is working OK (can be accessed locally and remotely via port 1521), however I am unable to open Enterprise Manager Express.  (I have tried different browsers).

The listener is up and seems to be configured for Ent. Mgr. Express, plus I can telnet into the relevant ports (tcp:8080 and tcps:5500), however when connecting via a web browser, no data is returned (verified using Fiddler).

Because ASM is being used, there could be two listeners, however all services are actually exposed via the Grid listener, there is no listener configured against the database home.

Any advice would be appreciated as I am not sure where to look next.

"lsnrctl status", "lsnrctl services" output and listener.ora contents are given below.

Thanks,

Chris

"lsnrctl status" output is as follows:

LSNRCTL for 64-bit Windows: Version 12.1.0.2.0 - Production on 15-JUN-2015 23:07:45

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=cba***.cbailiss.com)(PORT=1521)))
STATUS of the LISTENER
Alias                     LISTENER
Version                   TNSLSNR for 64-bit Windows: Version 12.1.0.2.0 - Produ
ction
Start Date                15-JUN-2015 23:02:00
Uptime                    0 days 0 hr. 5 min. 47 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   C:\app\grid\cbailiss\product\12.1.0\grid\network\admin\listener.ora
Listener Log File         C:\app\grid\cbailiss\diag\tnslsnr\cbaz***\listener\alert\log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=cbaz***.cbailiss.com)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.0.0.5)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=cbaz***.cbailiss.com)(PORT=5500))(Security=(my_wallet_directory=C:\APP\ORACLE\admin\orcl\xdb_wallet))(Pre
sentation=HTTP)(Session=RAW))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=cbaz***.cbailiss.com)(PORT=8080))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "+ASM" has 1 instance(s).
  Instance "+asm", status READY, has 1 handler(s) for this service...
Service "orcl" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orclXDB" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
The command completed successfully

listener.ora contents:

listener.ora Network Configuration File: C:\app\grid\cbailiss\product\12.1.0\grid\network\admin\listener.ora
Generated by Oracle configuration tools.

VALID_NODE_CHECKING_REGISTRATION_LISTENER = SUBNET

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = cbaz***.cbailiss.com)(PORT = 1521))
    )
  )

ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER = ON

"lsnrctl services" output:

LSNRCTL for 64-bit Windows: Version 12.1.0.2.0 - Production on 16-JUN-2015 00:15
:04

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=cbaz****.ad.cbailiss.com)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
  Instance "+asm", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER
Service "orcl" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER
Service "orclXDB" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
    Handler(s):
      "D000" established:1 refused:0 current:0 max:1022 state:ready
         DISPATCHER <machine: CBAZ****, pid: 1804>
         (ADDRESS=(PROTOCOL=tcp)(HOST=cbaz****.cbailiss.com)(PORT=51202))
The command completed successfully

I had the same problem after installing Oracle 12.2.0.1 on Windows 2016 without ASM and without RAC.

First I found that TCP/IPv6 was on so I turned it off.

Then tried following Oracle Note Number (Doc ID 1608258.1) - 12c EM Express shows Invalid Certificate Error in web browser.

Also didn't work.

I fixed it by old school way ... :)

I discovered that the Windows user Oracle_DBA (that logged on the PC and used to install Oracle 12c) don't have any permissions on the 2 files under the folder "xdb_wallet".

So:

1- I changed the ownership for the folder "xdb_wallet" from whatever it was to Oracle_DBA :

2- changed the ownership of the 2 files ( ewallet.p12 , cwallet.sso ) under the folder "xdb_wallet" from whatever it was to Oracle_DBA :

3- I did step 1 again , and checked that ownership changed for the 2 file.

The website   https://hostname:5500/em   worked fine on FireFox , because IE11/Win2016 has funny issues with Flash Player.

I don't know from security point of view what that means (steps 1-3), or what threats might happens due to those changes.

Please advice if you know.


Calendar Event table - best practice setup for range queries and individual retrieval This seems like a generic problem that should have been solved already, but I can't find anything about this. In general this question is - given a table where data is read by a date range, what is the best, most efficient setup?
We have a calendar event table that will quickly grow to millions of records.
The schema is something like:
CREATE TABLE [dbo].[CalendarEvent](
[Id] [uniqueidentifier] NOT NULL,
[DtStart] [datetime] NULL,
[DtEnd] [datetime] NULL,
[Created] [datetime] NULL,
[LastModified] [datetime] NULL,
[CalendarEventType] [nvarchar](255) NULL,
[CalendarId] [uniqueidentifier] NULL
PRIMARY KEY CLUSTERED
(
    [Id] ASC
    )WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

Forget about recurring events, etc. as that doesn't bear on our problem.
Most queries will be of the type:
select * from CalendarEvent where CalendarId = 'b5d6338f-805f-4717-9c0a-4600f95ac515' AND dtStart > '01/01/2020' AND dtStart < '10/22/2020'
Notice no joins, etc.
But we will also have some that select for individual events, and include joins:
select * from CalendarEvent ce join tags t on ce.Id = t.CalendarEventId where Id = '17606330-5486-496a-a91c-f5d0e123bfff'
Questions and ideas:

Should we keep the Id as the PK, but make the start date the clustered index?
Should we just make an index on dtStart?
Should we partition by month?
Should we denormalize a little and break duplicate the dtStart data by include year and month columns that we can index and use in our range queries?

In general, when you do your querying on a table by date range, what is the best setup for this type of table?
Note: If you think this question could be improved to help more people, make it more generic and widely applicable, such as removing references to a Calendar Event table specifically, and making this just about date range querying in any type of table, please help me do that.

So there's probably a few different things you can try but what stands out to me from your schema and examples are the following:

Avoid using UniqueIdentifiers if possible, and try to use Int or BigInt instead. (Int has over 8 billion values, BigInt over 18 quintillion.) It'll definitely make a difference in indexing performance.

Create a unique clustered index on your PK field (which it looks like you're already doing, so you can keep it as is).

Create a nonclustered index on CalendarId and dtStart.

Avoid SELECT * in your queries, unless you're using every column every time, it's faster to only select the exact columns you need (and either way it's best practice to list your columns out instead of using * for reliability reasons).

*5. If you're able to do #4 then when you create the nonclustered index in #3, you can use the INCLUDE keyword to specify names of the columns you'll be normally selecting. This eliminates key lookups and will be a little more performant.

You can look into creating an indexed view on top of:
select * from CalendarEvent ce join tags t on ce.Id = t.CalendarEventId

You just have to replace * with the actual columns you'll be using, and you need a unique value (likely the primary key of one of the tables) that you can create a unique clustered index on. This will basically store the data of this view as if it was a table and can save you some time. You also get the added benefit of being able to create additional nonclustered indexes on it if necessary as well.
This Brent Ozar article is also useful to be aware of for the limitations of Indexed Views: What You Can (and Can’t) Do With Indexed Views

You can also look into Table / Index Compression but this will be dependent on how much data is in your tables / indexes and if the bigger hardware bottleneck is I/O vs CPU.


MySQL shutdown unexpectedly 2013-09-09 10:21:44 5776 [Note] Plugin 'FEDERATED' is disabled.
2013-09-09 10:21:44 1624 InnoDB: Warning: Using innodb_additional_mem_pool_size is DEPRECATED. This option may be removed in future releases, together with the option innodb_use_sys_malloc and with the InnoDB's internal memory allocator.
2013-09-09 10:21:44 5776 [Note] InnoDB: The InnoDB memory heap is disabled
2013-09-09 10:21:44 5776 [Note] InnoDB: Mutexes and rw_locks use Windows interlocked functions
2013-09-09 10:21:44 5776 [Note] InnoDB: Compressed tables use zlib 1.2.3
2013-09-09 10:21:44 5776 [Note] InnoDB: Not using CPU crc32 instructions
2013-09-09 10:21:44 5776 [Note] InnoDB: Initializing buffer pool, size = 16.0M
2013-09-09 10:21:44 5776 [Note] InnoDB: Completed initialization of buffer pool
2013-09-09 10:21:44 5776 [Note] InnoDB: Highest supported file format is Barracuda.
2013-09-09 10:21:44 5776 [Note] InnoDB: The log sequence numbers 0 and 0 in ibdata files do not match the log sequence number 19862295 in the ib_logfiles!
2013-09-09 10:21:44 5776 [Note] InnoDB: Database was not shutdown normally!
2013-09-09 10:21:44 5776 [Note] InnoDB: Starting crash recovery.
2013-09-09 10:21:44 5776 [Note] InnoDB: Reading tablespace information from the .ibd files...
2013-09-09 10:21:44 5776 [ERROR] InnoDB: Attempted to open a previously opened tablespace. Previous tablespace casualme/scraped_user uses space ID: 2 at filepath: .\casualme\scraped_user.ibd. Cannot open tablespace mysql/innodb_index_stats which uses space ID: 2 at filepath: .\mysql\innodb_index_stats.ibd
InnoDB: Error: could not open single-table tablespace file .\mysql\innodb_index_stats.ibd
InnoDB: We do not continue the crash recovery, because the table may become
InnoDB: corrupt if we cannot apply the log records in the InnoDB log to it.
InnoDB: To fix the problem and start mysqld:
InnoDB: 1) If there is a permission problem in the file and mysqld cannot
InnoDB: open the file, you should modify the permissions.
InnoDB: 2) If the table is not needed, or you can restore it from a backup,
InnoDB: then you can remove the .ibd file, and InnoDB will do a normal
InnoDB: crash recovery and ignore that table.
InnoDB: 3) If the file system or the disk is broken, and you cannot remove
InnoDB: the .ibd file, you can set innodb_force_recovery > 0 in my.cnf
InnoDB: and force InnoDB to continue crash recovery here.

My phpmyadmin is not working. How can I make it work again? I have a very large database.

I have the following files. db.opt , scraped_user.frm , scraped_user.ibd is there a way to recover my database from these files? I just want to get the table's data.

I want to reinstall my xampp but is there a way to back up my db when I can't run it? Like copying this folder 'DBNAME' with files db.opt, scraped_user.frm, scraped_user.ibd.

I tried the first option suggested by InnoDB above. Still not working. In second I cant remove the .ibd file. I don't have a backup of my table. On the third item, I don't know where it is located. Been looking for it.

I have managed to recover my data. (so happy)

After hours of reading forums and solutions I found a way to fix it. Here's what I did:

In xampp I backed up my old copy (after crash) of folder DbScrapes (dbname) from C:\xampp\mysql\data with files db.opt, scraped_user.frm, scraped_user.ibd
In C:\xampp\mysql\data I backed up an old copy (after crash) of files ib_logfile0, ib_logfile1, ibdata1.
Uninstalled my xampp
Restarted my computer to be sure ports that xampp is using will be close
Checked if there is a MySQL service in services.msc
On my computer there was a MySQL service.
I deleted it using cmd command sc delete "service_name"
Restart again to make sure everything is fresh.
Installed a new copy of xampp
Test if apache and MySQL is ok, if it's ok click stop
Copy the files that I have backed up to the location where I get them so it's C:\xampp\mysql\data
Run xampp's apache and MySQL again.

Now I'm running xampp and I have my data.

Note: Don't delete ibdata1 - it will delete all your database tables.

The crashing issue I think was a bug in xampp.


RegEx with update query my sql i need to use a mysql  update query with regex so my example is

update table_name set coulmn_name = replace(coulmn_name, 'regex', '');

i will explain a specified thing i need to do. my column is contained some text & some html tags i need to delete all img tags from this coulmn  what can i do to achieve this ?

this is my ReGex

/</?(?i:img)(.|\n)*?>/g

but i don't know how to make this
any help please

MySQL doesn't support it natively, but this is possible if you install the user-defined functions from the lib_mysqludf_preg library.

You should be able to then do something like this:

UPDATE table_name
   SET column_name = PREG_REPLACE('regex_match', 'replacement_value', column_name);

This is third-party software but it uses the standard MySQL User-defined function interface to enable custom functions written in C/C++ to interact and behave like built-in functions (e.g., REPLACE()) ... but UDFs are essentially plug-ins that can be installed and uninstalled without having to recompile the server or even having to shut it down and restart.

Above, the 'regex_match' and 'replacement_value' are the regular expression and replacement values, which can be from columns, variables, or supplied in the query as string literals.  In the last case, they need to be quoted.


pgAdmin to display multiple result sets With MS SQL Query Browser I can run multiple queries at the same time and each result set will display in it's own window.

Does PostgreSQL/pgAdmin have this functionality? Plugin perhaps?

If started from the same SQL window, then no - you will always get the latest result set (or nothing, if the last command returns no rows, like in the case of COMMIT).  Not sure if this is OK for you, but you can open as many query windows as you want and so have as many result sets.

(The above means that I'm not aware any such plugins.)


Understanding performance of highly unique WHERE clauses I've been struggling to understand how to deal with a specific type of performance issue that shows up very often in this scenario: when you are wanting to apply multiple filters in a query, but you know that first filter will return a very small number of rows from a very large table.

For example, we have a 3rd-party heterogeneous table with 10M+ rows where the columns mean different things based on the "object type" in TYPEID.  Here is an example query:

SELECT ID, NAME, INT109 FROM DATA WHERE TYPEID = 8301514 AND INT109 = 1

In this query, there are no covering indexes for the two filters, but there is an index on the 'TYPEID' column.  What is perplexing is that even though there are only about 500 rows out of the 10M in the table with TYPEID = 8301514, this query sometimes takes many seconds to run.

If I simply remove the INT109 = 1 filter at the end, the query runs almost instantly:

SELECT ID, NAME, INT109 FROM DATA WHERE TYPEID = 8301514

It makes no sense to me that having fewer filters would make a query run much faster.  Also the behavior seems to be inconsistent - the first query can run really fast too if it has been run multiple times, like something is being cached. It's difficult to do reliable experiments (this is in SQL Azure).  Is this normal behavior?  Is this something that can be caused by a bad execution plan (even though I'm not using parameters) or statistics that are out of date?

What's probably going on here is that at some point, you ran that first query with a value that had a lot more than 500 rows - so many that it figured it was better to scan 10M rows than do hundreds of thousands of lookups to get the Name and INT109 columns. And then that plan got cached, and reused when you provided a different value which would've benefited from a different plan. SQL made an assumption that it would be better to avoid recompiling than to evaluate each different value you could provide.

When you wrote a different query, it evaluated it fresh, and gave the best plan, although that might not be ideal for a different value.

The best way to fix this is to have an index on (TYPEID, INT109) INCLUDE (ID, Name), so that lookups are not needed, and the plan will seek regardless of the stats around TYPEID. Plus, this index is useful even if you leave out INT109.


11gR2 didn't ask me to set username or any password I have installed Oracle 11gR2 (latest version 11.2.0.1.0) on my system. During the installation it didn't ask me to set username or any password. I'm unable to start sql plus because i don't have the username and password.
Can anyone tell me what is the default username and password or what is the default password for SYS or SYSTEM?
Thanks

If the Universal Installer included with 11gR2 did not ask for a password, it is likely that the option to create a database was not selected during installation.

Solution? Create a database with the dbca tool and supply a sys password when prompted.

With older versions, perhaps including Oracle Database XE (11g), you might have luck with one of the default password combinations of sys/change_on_install or system/welcome1 or system/manager. On engineered systems I have also seen sys/welcome1 and sys/changeme


How do you unescape "C-like backslash escape sequence" that come from a function? This will unescape the \n\n sequence.

SELECT E'\n\nFOO';

But if that same sequence comes from a function, what do you do.

CREATE FUNCTION asdf() RETURNS text
AS $$
  SELECT '\n\nFOO'::text
$$ LANGUAGE 'sql';

Pg doesn't appear to expose its string parsing/lexing to SQL. It has quote_literal etc but doesn't seem to have the reverse. So I'm not aware of another way to do this without breaking out into a PL or writing a simple C extension.

e.g.

CREATE OR REPLACE FUNCTION pyunquote(quoted text) RETURNS text AS $$
return quoted.decode("string-escape")
$$ LANGUAGE plpythonu;

then

test=# SELECT pyunquote('\n\nAAA');
 pyunquote
-----------
          +
          +
 AAA
(1 row)

but note that this uses Python's string escape rules, not PostgreSQL's.

To get PostgreSQL's rules, you could use the following horribly insecure approach and trust whatever the function passes you:

DO
LANGUAGE plpgsql
$$
DECLARE
  x text;
BEGIN
  EXECUTE format('SELECT E''%s''', '\n\nFOO') INTO x;
  RAISE NOTICE 'value is "%"', x;
END;
$$;

but of course someone can pass ';DROP TABLE customer;-- which won't be fun.


Custom sp_who/sp_whoUsers I need to allow a client in a dev DW SQL 2K8R2 environment, to view and kill processes, but I do not want to grant VIEW SERVER STATE to this person (he's a former sql dba and is considered a potential internal threat).

When I run the following, it returns one row as if the user ran the sp themselves with their current permissions.

USE [master]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[usp_who] with execute as owner
AS
BEGIN
    SET NOCOUNT ON;
    exec master.dbo.sp_who;
END

Changing the "with execute as" to "self" (I'm a sysadmin) returns the same results. I've also tried the below instead of calling sp_who, and it only returns one row.

select * from sysprocesses

It seems that the context isn't switching, or persisting, throughout the execution of the procedure. And this is to say nothing of how I'm going to allow this person to "kill" processes.

Does anyone have a solution or some suggestions to this seemly unique problem?

First of all I question hiring someone you consider an possible internal threat, but that aside your problem is probably a login vs user problem.  When you do an EXECUTE AS you are executing as a user.  VIEW SERVER STATE is an instance or login level permission.  You can set your database to trustworthy and then it will act as if you are using the login rather than the user.  However if you are using a DBA you don't trust then you would be creating a much larger potential problem than just granting him VIEW SERVER STATE.


What is the difference between setting the directive default-character-set under [client] as opposed to [mysqld]? Reference: https://stackoverflow.com/a/8690422/784637

Correct me if I'm wrong, but the best answer suggests a way to force everything being inputted into the database to be encoded in utf-8 and everything going out of the database to be encoded in utf-8.

What's the difference between the following two:

[client]
default-character-set=utf8

[mysqld]
default-character-set = utf8

Does the directive under [mysqld] force all the writes to the database to be encoded under a certain format and the directive under [client] force all of the output to be encoded in a certain format?

For example if the following two directives are set:

[client]
default-character-set=utf8

[mysqld]
default-character-set = utf8

and a there's a php string $var that is encoded in latin1, if that string is inserted into the database, it will be stored in the database as utf-8.  But if a select statement is performed to retrieve that value, it would be encoded in latin1.

[client]
default-character-set=utf8

This option can force client programs to use specific character set.
This is normally unnecessary. However, when character_set_system differs from character_set_server or character_set_client, and you input characters manually (as database object identifiers, column values, or both), these may be displayed incorrectly in output from the client or the output itself may be formatted incorrectly. In such cases, starting the mysql client with --default-character-set=system_character_set—that is, setting the client character set to match the system character set—should fix the problem.

[mysqld]
default-character-set = utf8

MySQL Server has a server character set and a server collation. These can be set at server startup on the command line or in an option file and changed at runtime.
The server character set and collation are used as default values if the database character set and collation are not specified in CREATE DATABASE statements. They have no other purpose.

Conclusion: Client setting is at session level whenever client connects to server, server forces clients to use specific charset however this is not necessary.
Mysqld section charset indicates that server is configured to use particular charset by default however you can change charset at any level Database,Table and Column level you want.


How do I specify a linked server to a remote database over tcp/ip? I have a remote database running on an IAAS provider and I need to be able to execute joined queries from my local workstation. I am having trouble because when I attempt to create the link it is attempting to use named pipes for the connection.

I created a system DSN using the SQL Server Native Client 10 and fed it the IP, Database, User, and Password. Tested it and it was fine.

Went to Linked Servers and Create New.
Specified a name "MAIN"
Selected SQL Server Native Client 10 in the combo box
Entered SQL Server in Product Name Field
Entered my DSN name in Data Source Field
Entered SQLNCLI10 in Provider Name Field
Entered my database name in the Catalog Field
Went to the security options and specified my local to remote login pairing.

After clicking OK, I get an error that it can't find the server and it shows that it is attempting to use named pipes.

What should I do to correct this?

I don't see a reason why you should be using a DSN. Create the linked server using the SQL Server Native Client directly:

EXEC master.dbo.sp_addlinkedserver
    @server     = N'MAIN',
    @srvproduct = N'SQLServ', -- it’s not a typo: it can’t be “SQLServer”
    @provider   = N'SQLNCLI',
    @datasrc    = N'I .P_Address';

Then you can create the local/remote login pairs:

-- Pair local and remote logins
EXEC master.dbo.sp_addlinkedsrvlogin
    @rmtsrvname  = N'MAIN',
    @useself     = N'False',
    @locallogin  = N'Local_user_name',
    @rmtuser     = N'Remote_user_name',
    @rmtpassword = N'Remote_password';v


Bad practice to return more columns than required I unfortunately have quite a broad question regarding queries that return more columns than they need.

Simply put, is this bad practice and should it be avoided at all costs? We continuously have debates about this in our development team. Some argue that all queries should only return the exact data they need as this would reduce I/O etc, others argue that it is OK to have a query that returns a few extra columns such as int or nvarchar(50) as this reuses execution plans and makes development faster. We are not using any SELECT * statements anywhere so this is not an issue - it is purely where a few extra columns are explicitly specified.

I know this may trigger a debate rather than an answer but any insight would be helpful.

Some argue that all queries should only return the exact data they need as this would reduce I/O etc...

And they are correct!

...others argue that it is OK to have a query that returns a few extra columns such as int or nvarchar(50) as this reuses execution plans and makes development faster

These folks are also correct!
There are always tradeoffs with these things.
Imagine a web page where users can edit their profile, populated by "query 1" here:
SELECT Id, FullName, UserName, Phone
FROM dbo.Users
WHERE Id = @Id;

And then there's an admin page that uses "query 2" here:
SELECT Id, FullName, UserName, Phone, IsActive, AdminComments
FROM dbo.Users
WHERE Id = @Id;

Performance
For the situation where we're using both queries, we have (non-exhaustive list of pros and cons):

Con: two different plans taking up space in the plan cache
Con: developers had to write two different queries / stored procedures
Pro: the first query will only be doing the work it needs to (no extra I/O)
Pro: the two queries can be optimized differently
Pro: the two queries can be indexed for separately

If we just have query 2, the situation is basically inverted (only one plan in the cache, only one query / proc had to be written, but the query uses more I/O, etc).
Security
There's also an aspect of security: you might not want those "admin only" columns getting queried for non-admin pages, even if they are never presented to the user.
Maintainability
Sharing the same query for multiple different pages that don't need all that data can also lead to maintainability problems down the road.  What if query 2 suddenly needs to join to other tables, in order to pull in additional information about user activity that's not available to regular users?
Now the normal user page is being impacted by the admin query.  And developers at that time might not realize they need to go back and check on the regular user page.
What if some enterprising developer removes columns from query 2, because it's decided admins don't care about them?  Now the regular user page is actually broken.
Takeaway
Re-using queries can be a valid time-saving strategy, but it requires good change management and testing later to make sure that you don't break anything when these re-used queries are modified.  It's also possible various factors will cause the queries to be split up later (performance, feature changes).  You'll have to keep that in mind to determine if the up front time saving is worth the cost later.
Generally speaking, I think it's better to have queries that are specific to each use-case.  You'll notice that this results in lots of stored procedures / queries, which has it's own maintenance overhead.  There are some things you can do to help with that though (keep the procedures small where possible, include a comment in the procedure that indicates what app function / page uses that query, name the procedures very clearly).


Shouldn't SQL Server Support RANGE? As someone who develops websites and does a bit of SQL Server, it seems a no brainer to me that SQL Server should support a simple clause that indicates the results should only include a specific range of rows. For example, rows 30 through 39.

As it stands, implementing paging in SQL Server is actually a little beyond many web developers' capability when their main focus is not SQL.

In addition, it would greatly simplify queries that may already be complex due to joins and other conditions.

On top of that, I'm of the opinion that such a clause may stand a better chance of getting optimized internally over time because the logic would be entirely under Microsoft's control.

I brought this up some time ago in another forum and many of the replies seemed to suggest this would not be desirable.

Given that any existing techniques for paging would be left intact, what possible reason could there be for not adding such a clause? And it would be so much better for people like me.

Can anyone thing of a good reason why Microsoft hasn't done this?

Microsoft has done this with the next version of SQL Server, codename "Denali", as well as SQL CE 4. Check out the OFFSET & FETCH parts of the ORDER BY clause here: http://msdn.microsoft.com/en-us/library/ms188385(v=sql.110).aspx


Which file is PostgreSQL log file 0? In my PostgreSQL logs, I find this message:

LOG:  invalid magic number 0000 in log file 0, segment 1, offset 16121856

Which file is log file 0?  Here is my pg_xlog directory:

-bash-4.1$ ll pg_xlog/
total 49160
-rw------- 1 postgres postgres 16777216 Dec  6 06:27 000000010000000000000001
-rw------- 1 postgres postgres 16777216 Jan 21 09:10 000000020000000000000001
-rw------- 1 postgres postgres 16777216 Dec 21 05:29 000000020000000000000002
-rw------- 1 postgres postgres       56 Dec  6 06:30 00000002.history
drwx------ 2 postgres postgres     4096 Dec  6 06:30 archive_status
-bash-4.1$ ll pg_xlog/archive_status/
total 0
-rw------- 1 postgres postgres 0 Dec  6 06:30 00000002.history.ready

That would be 000000010000000000000001 or 000000020000000000000001. That's timeline 1 or 2, log 0, segment 1. Which one is being accessed depends on which timeline your system in (you can use pg_controldata to find that out, but basically the system switches timeline when you do a PITR recovery or a replication failover)

The error message basically says that file is corrupt, which is really bad. Another reason this could happen is if that file is from a different version of postgresql - any chance you accidentally mixed two different versions?


Can Unicode columns include non-Unicode values in SQL Server? I need to convert Unicode column values from UTF16-LE to UTF-8 to then import into MySQL.

Is it safe to assume columns of NVARCHAR, NTEXT, NCHAR, BIT, INT, DECIMAL, FLOAT, and DATETIME all must be Unicode and therefore will not have any characters unable to be converted from UTF-16LE to UTF-8 simply by exporting the values to a .txt file and resaving them with the UTF-8 Encoding prior to importing them to MySQL?

Is it safe to assume any unicode columns in SQL Server will not include any characters incapable of being converted from UTF-16LE to UTF-8 after they've been exported to CSV files?

My import fails, regardless if I re-save the CSV with UTF-8 encoding or not. So, I assume either:

SQL Server allows non-unicode character in unicode columns that cannot convert correctly to UTF-8 (which I doubt, hence my question to check my assumption); or
It's failing elsewhere in the process - e.g. re-saving the CSV files adds something MySQL doesn't like.

I'm using bcp to export the values to a CSV. Then, I resave it with the UTF-8 encoding because MS removed the ability to export directly as UTF-8. Finally, I use MySQL's LOAD DATA INFILE to import where it fails.

Is it safe to assume columns of NVARCHAR, NTEXT, NCHAR, BIT, INT, DECIMAL, FLOAT, and DATETIME all MUST be UNICODE...

Only the XML and N-prefixed types (NCHAR, NVARCHAR, and NTEXT [which has been deprecated since SQL Server 2005 was released so please do not use it]) are Unicode. Those other types you mentioned are not strings and are not stored as strings, hence they are not relevant to this question.

  ... and therefore WILL NOT have any characters unable to be converted from UTF-16LE to UTF-8...

This is not exactly a valid question. Unicode characters are Unicode characters regardless of their encoding, whether it is UTF-8, UTF-16LE, UTF-16BE, UTF-32LE, or UTF-32BE. Now, it is possible for the data itself to contain invalid sequences, such as invalid Surrogate Pairs. But then those aren't valid characters in the UTF-8 or UTF-32 encodings either.

  ... simply by exporting the values to a .txt file and resaving them with the UTF-8 Encoding prior to importing them to MySQL?

Well, you need to be sure to save the initial export file with a Unicode-encoding. So you would use either the -N or -w options with BCP.

Also, make sure that you are doing more than just changing the Byte Order Mark (BOM) of the file and are actually converting the Unicode / UT-16LE characters to UTF-8.


Same SSRS report, two different subscription names I would like to know how to change same SSRS reports from one subscription name to two different “from” names?

Need to update XML code in report database tables(after report deployed in report server then need to create subscription, its data will be stored in sql table by xml format) with required format.


Restore of db's on local locks me out of accessing other sites Running my local dev websites on my Mac locally and when I am restoring a database via terminal, which the restore is running I cannot access anything that uses mysql, I get a broken pipe notice.

Is there anyway to allow a restore without it giving me this issue when trying to work on a second, seperate site that uses a direct db but same server?

The error I get when trying to access any development site that uses mysql during a restore

Notice: PDO::__construct(): send of 130 bytes failed with errno=32 Broken pipe in /Library/WebServer/Documents/Sites/site_name/includes/database/database.inc on line 307

Not normally an issue but currently restoring a particular database that is so large it would take 40 minutes to restore.

Server version: 5.6.22 MySQL Community Server (GPL)

Command used

$ mysql -u root -p itrs < clientx_web_live_200315.sql

My.cnf

# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M

# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin

# These are commonly set, remove the # and set as required.
# basedir = .....
# datadir = .....
# port = .....
# server_id = .....
# socket = .....

# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

wait_timeout=3600

If you are using innodb tables, try to raise innodb_buffer_pool_size as much as you can. You must balance the ram from mysql and other tools you use for development. The idea is to don't have a swapping machine and to use memory resource as best as possible.


MongoDB dump oplog based on timestamp I'm trying to dump oplog.rs from local database, based on timestamp.
Query directly from mongo shell works but not from mongodump

Query from mongo shell

RS1:PRIMARY> db.oplog.rs.find({"ts" : {$gt : Timestamp(1591199252, 0)}})
{ "ts" : Timestamp(1591199252, 1), "t" : NumberLong(2), "h" : NumberLong(0), "v" : 2, "op" : "n", "ns" : "", "wall" : ISODate("2020-06-03T15:47:32.975Z"), "o" : { "msg" : "periodic noop" } }
...

Mongodump ends either with an error or with 0 dumped documents (depending on single quotes for timestamp value) :
1. Output with error

$ mongodump $credentials -d local -c oplog.rs --query '{"ts": {"$gt": Timestamp(1591199252, 0)}}' --out ~/test/
2020-06-11T16:54:36.317+0200    Failed: error parsing query as Extended JSON: invalid JSON input. Position: 15. Character: T

2. Output with 0 dumped documents

$ mongodump $credentials -d local -c oplog.rs --query '{"ts": {"$gt": "Timestamp(1591199252, 0)"}}' --out ~/test/
2020-06-11T16:59:28.264+0200    writing local.oplog.rs to
2020-06-11T16:59:30.232+0200    done dumping local.oplog.rs (0 documents)

Mongodump don't understand Timestamp-function, so...

mongodump $credentials -d local -c oplog.rs --query '{"ts":{"$timestamp":{"t":1591199252,"i":0}}}' --out /test/


How to remove the red squiggly line from IntelliSense for the newly created table or procedure without restarting the SSMS A newly created table or procedure will always have the red squiggly underline until the SQL Server Management Studio is restarted. Is there a way to get rid of it without re-start the SSMS? Or it's a feature that SSMS needs to update? Thanks!

Try pressing ctrl + Shift + r to refresh intellisense and remove the red squiggly underline.


Is it bad practice to automate the updating of statistics and re-making of query plans on a relatively small SQL Azure database? I have a SQL Azure database that's about 500MB in size. Every 2-3 weeks the database will suddenly start to run slow and some queries (generated by LINQ) will timeout. There is no gradual increase in execution time, it just suddenly spikes.

The only way to fix it is to update the statistics; I usually delete the query plans at the same time. It then goes back to normal.

Some of the indexes were wrongly created with STATISTICS_NORECOMPUTE=ON at the beginning. Since changing them to OFF, the issue happens less frequently. However, when I look at the indexes and table-creation SQL, nothing is unusual and nothing is unusual about the system. There's new rows being added daily but nothing unusually large or small. I read that statistics only update the 20%+500 of the table has been changed, but I have other similar databases running without issue.

So, if I scheduled the update of statistics every few days at midnight, am I treating the symptom whilst ignoring the elusive cause? Is it bad practice or is this standard database maintenance?

If auto update statistics is not enough for your DB, try a couple of things:

Manually update statistics - recommend Ola Hallengren's scripts that can be configured to maintain indexes and/ or statistics as required. Test different parameters (all/ indexes only/ sample rate etc) and test running daily/ weekly/ at start of proc and the like, and see what works best for you with least overhead.
Scripts are here: https://ola.hallengren.com/
Brent Ozar article about updating stats only with those scripts:https://www.brentozar.com/archive/2016/04/updating-statistics-ola-hallengrens-scripts/

Enable Trace Flag 2371 (if SQL Server version older than 2016)
For large tables, this reduces the threshold for automatically updating statistics dynamically. This algorithm is used as standard since SQL Server 2016.Good further reading:
https://support.microsoft.com/en-gb/help/2754171/controlling-autostat-auto-update-statistics-behavior-in-sql-server
https://littlekendra.com/2016/04/18/updating-statistics-in-sql-server-maintenance-answers/#trace-flag-2371


Changed server name, now maintenance plan fails I am using SQL Server 2008 R2.  When I try to execute a maintenance plan I get the following error:

  Message [298] SQLServer Error: 15404, Could not obtain information
  about Windows NT group/user 'WIN-6QFI9JAK804\sqladmin', error code
  0x534. [SQLSTATE 42000] (ConnIsLoginSysAdmin)

I ran the following queries:

sp_dropserver 'WIN-OldName'
GO
sp_addserver 'VM-MSSQL-2008R2', local
GO

I then restarted the MSSQLSERVER service and the agent service.

When I run select @@servername I do get VM-MSSQL-2008R2 returned however the original error persists when I try to execute the maintenance plan again.

Is there another step that I haven't performed yet?

UPDATED:

I ran:

select * from msdb.dbo.sysssispackages

and noticed that the only plan is called Backups.  So I instead ran the query:

UPDATE msdb.dbo.sysssispackages
  SET ownersid = SUSER_SID('VM-MSSQL-2008R2\sqladmin')
  WHERE name = 'Backups'

I ran the following two selects to make sure that the are the same:

select SUSER_SID('VM-MSSQL-2008R2\sqladmin')
select ownersid from msdb.dbo.sysssispackages WHERE name = 'Backups'

And they both return the same value.

When I run the plan, I get the same error message as before.

(I have created a new Backup Plan, but I'd still like to figure out why this one is working just for knowledge's sake)

You need to change the owner of the maintenance plan. It was set up to be owned by an account that no longer exists. Not sure if it is better in your scenario to use sa or the new local admin account. This update assumes you still have the sqladmin account after the rename and that it has been added as a login to SQL Server with admin privileges.

UPDATE msdb.dbo.sysssispackages
  SET ownersid = SUSER_SID('VM-MSSQL-2008R2\sqladmin')
  WHERE ownersid = SUSER_SID('WIN-6QFI9JAK804\sqladmin');

You may also need to reset the owner of the job associated with the maintenance plan, which you can do through the UI or via the following:

EXEC msdb..sp_update_job
  @job_name = N'MaintenancePlan.Subplan_1', -- or whatever it's called
  @owner_login_name = N'VM-MSSQL-2008R2\sqladmin';

If the login hasn't been added, then execute this first:

CREATE LOGIN [VM-MSSQL-2008R2\sqladmin] FROM WINDOWS;
GO
EXEC sp_addsrvrolemember N'VM-MSSQL-2008R2\sqladmin', N'sysadmin';

In the future, be sure to have the machine properly configured / named etc. before installing SQL Server. :-)


How can dm_db_index_usage_stats show 0 for user_seeks and user scans but a large number for user_lookups? I'm probably not understanding something but here is the question.

I have done a query on dm_db_index_usage_stats. I've noticed that a couple of my indexes have large numbers (6.5 million and 20 thousand) in the user_lookups column but these also have a zero in both the user_seeks and user_scans columns.

My understanding (from books online) is that the user_lookups column counts bookmark lookups. As I understand it that is when the database can't find all the columns it needs in an index and has to visit the main table or clustered index for the remaining columns. However why would SQL server have done this if it has never done an seek or scan on the index?

The user_lookups are lookups in to the index. The original scan or seek will have been on another non covering index.

For example

CREATE TABLE T
(
X INT IDENTITY PRIMARY KEY,
Y INT CONSTRAINT UQ UNIQUE NONCLUSTERED,
Z CHAR(8000) NULL
)

INSERT INTO T(Y)
SELECT DISTINCT number
FROM master..spt_values

GO

DECLARE @Z INT
SELECT @Z = Z
FROM T
WHERE Y = 1

GO 1000

Gives the following plan

SELECT index_id, user_seeks,user_scans, user_lookups
FROM sys.dm_db_index_usage_stats
WHERE object_id = object_id('T')
ORDER BY index_id

Shows 1,000 seeks on the NCI and 1,000 lookups on the CI.

index_id    user_seeks           user_scans           user_lookups
----------- -------------------- -------------------- --------------------
1           0                    0                    1000
2           1000                 0                    0

BTW: Just in case you were not aware the user_lookups shown here refers to the number of times a plan was executed containing the lookup operator not the number of lookups that actually occurred. e.g. Both of the following increment the counter by 1 despite performing 0 and 2,161 lookups in reality

SELECT  Z
FROM T
WHERE Y = -999

SELECT Z
FROM T WITH (FORCESEEK)
WHERE Y >= 0


Select results from table ONLY if rows meet 2 conditions **OrdersTable**
  OrderID
  Product

SELECT OrderId, Product
FROM OrdersTable
WHERE Product = 'ProductA'
AND Product = 'ProductB' --doesn't work as AND looks for the same data in one row

OrderID   Product
-----------------
628       ProductB
628       ProductA

I am trying to query OrderID's that contain BOTH Product 'A' AND Product 'B'.
I've tried using EXISTS, AND/OR, etc but they always return an either or situation (i.e. the query returns orders that have a Product 'A', but not a Product 'B' or vice versa). How can I query only OrderID's that contain both Products A and B?

You can use an aggregate for that:

SELECT OrderId
FROM OrdersTable
WHERE Product in ('ProductA', 'ProductB')
group by orderid
having count(distinct Product) = 2;

This will give you those orders that include at least those two products. The orders might include other products as well.


oracle merge operation hangs (or loops); query tuner claims it's a cartesian product operation I have a Merge query (open to better suggestions) that seems to cause the server to hang.  (I'm not sure what it's doing, but it pegs the CPU and never completes)  When I run it through the query tuner, I'm told it's caused by "an expensive cartesian product operation was found at line ID 7 of the execution plan", and that I should "consider removing the disconnected table or view from this statement or add a join condition which refers to it."  The problem is, there is no disconnected table.

Here's the explain plan:

---------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------
|   0 | MERGE STATEMENT                   |               |     1 |    18 |    75  (10)| 00:00:01 |
|   1 |  MERGE                            | CM_SSS_DETAIL |       |       |            |          |
|   2 |   VIEW                            |               |       |       |            |          |
|   3 |    SORT GROUP BY                  |               |     1 |  1352 |    75  (10)| 00:00:01 |
|   4 |     NESTED LOOPS                  |               |       |       |            |          |
|   5 |      NESTED LOOPS                 |               |     1 |  1352 |    74   (9)| 00:00:01 |
|   6 |       NESTED LOOPS                |               |     1 |  1328 |    73   (9)| 00:00:01 |
|   7 |        MERGE JOIN CARTESIAN       |               |     1 |  1306 |    72   (9)| 00:00:01 |
|   8 |         TABLE ACCESS FULL         | CM_SSS_DETAIL |     1 |  1274 |    18   (0)| 00:00:01 |
|   9 |         BUFFER SORT               |               | 12815 |   400K|    54  (12)| 00:00:01 |
|* 10 |          TABLE ACCESS FULL        | CI_CC         | 12815 |   400K|    54  (12)| 00:00:01 |
|  11 |        TABLE ACCESS BY INDEX ROWID| CI_CASE       |     4 |    88 |     1   (0)| 00:00:01 |
|* 12 |         INDEX RANGE SCAN          | XT220S1       |     6 |       |     1   (0)| 00:00:01 |
|* 13 |       INDEX RANGE SCAN            | XT222P0       |     1 |       |     1   (0)| 00:00:01 |
|* 14 |      TABLE ACCESS BY INDEX ROWID  | CI_CASE_CHAR  |     1 |    24 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

10 - filter("CC"."CC_TYPE_CD"='1NAA-LTR' OR "CC"."CC_TYPE_CD"='1NEA-LTR' OR
"CC"."CC_TYPE_CD"='1NIP-LTR' OR "CC"."CC_TYPE_CD"='CBS-LTR' OR
"CC"."CC_TYPE_CD"='CBS1-LTR' OR "CC"."CC_TYPE_CD"='CBS2-LTR' OR
"CC"."CC_TYPE_CD"='DNAR-LTR' OR "CC"."CC_TYPE_CD"='INR-LTR' OR "CC"."CC_TYPE_CD"='IPL-LTR'
OR "CC"."CC_TYPE_CD"='SOFB-LTR')
12 - access("CC"."PER_ID"="C"."PER_ID")
13 - access("CHR"."CASE_ID"="C"."CASE_ID" AND "CHR"."CHAR_TYPE_CD"='OBLGID')
14 - filter("D"."SA_ID"="CHR"."CHAR_VAL_FK1")

-------------------------------------------------------------------------------

Here's my query:

MERGE INTO cm_sss_detail d
USING (
    SELECT chr.char_val_fk1, MIN(cc.cc_dttm) first_date
    FROM ci_case c
    JOIN ci_cc cc
        ON (cc.per_id = c.per_id)
    JOIN ci_case_char chr
        ON (chr.case_id = c.case_id)
    WHERE chr.char_type_cd = 'OBLGID'
        AND cc.cc_type_cd IN (  '1NAA-LTR','CBS1-LTR','CBS2-LTR','CBS-LTR',
                                'DNAR-LTR','IPL-LTR','INR-LTR','SOFB-LTR',
                                '1NEA-LTR','1NIP-LTR')
    GROUP BY chr.char_val_fk1
) b
ON (d.sa_id = b.char_val_fk1)
WHEN MATCHED THEN UPDATE
    SET d.fst_bill_date = b.first_date;

The cm_sss_detail table has an index on the sa_id column (it's also the PK).

According to the query plan, the optimizer expects that the MERGE statement is going to modify 1 row.  It appears to expect the Cartesian join to return a single row as well.  Based on the fact that the query isn't returning in a few milliseconds, that would generally imply that the statistics on one or more of the objects (tables or indexes) are substantially incorrect.  When was the last time statistics were gathered on these tables and their dependent indexes?  Has the actual data in the tables changed substantially since that time?


Slow queries on production Server I am a developer who has been asked to look at a database slowness issue.
I have had a look at the waits on the server and retrieved the following using a query from Red Gate's wait stats white paper.
The results are:

Wait type         wait_S            Resource_S     Signal_S     WaitCount      Percentage

PAGEIOLATCH_SH      393375.51       391289.91      2085.59      76733247       32.60

CXPACKET            367110.76       334340.19      32770.57     34663540       30.42

OLEDB               84136.77        84136.77       0.00         78106          6.97

SOS_SCHEDULER_YIELD 77896.38        15.97          77880.41     254840824      6.46

PAGEIOLATCH_EX      65944.98        65550.98       394.00       16298376       5.46

ASYNC_NETWORK_IO    45579.23        32512.01       13067.22     125659899      3.78

WRITELOG            37415.61        35457.02       1958.59      18559390       3.10

LCK_M_IX            36602.81        36587.52       15.29        67118          3.03

LCK_M_X             35503.99        35494.55       9.44         32114          2.94

LCK_M_U             30464.81        30452.64       12.17        28824          2.52

Does anyone have any advice on what to do next?

Does CXPacket suggest a maxdop change?  The server is a virtual server showing 4 CPU's in windows task manager.

While by no means a dead cert, PAGEIOLATCH_SH and CXPACKET in equal weight can be indicative of parallel table scans. Without a baseline to compare with I'd suggest looking at the top CPU and IO consumers and looking for those which appear in both.


Package state not re-intialized on raise_application_error When a package has state and the header is changed, the first call gets an error stack something like this:

ORA-04068: existing state of packages has been discarded
ORA-04061: existing state of package "LRIFFEL.PKG1" has been invalidated
ORA-04065: not executed, altered or dropped package "LRIFFEL.PKG1"
ORA-06508: PL/SQL: could not find program unit being called: "LRIFFEL.PKG1"
ORA-06512: at "LRIFFEL.PKG2", line 7
ORA-06512: at line 1

A subsequent call from the same session re-initializes the package state and therefore runs successfully.  The interesting thing is that this behavior seems to change when the caller catches the exception and does a raise_application_error.  As expected the raise_application_error is included in the stack, but contrary to expectations, the ORA-04068 is not.  Here is an example error stack:

ERROR at line 1:
ORA-20001: Failed
ORA-06512: at "LRIFFEL.PKG2", line 7
ORA-04061: existing state of package "LRIFFEL.PKG1" has been invalidated
ORA-04065: not executed, altered or dropped package "LRIFFEL.PKG1"
ORA-06508: PL/SQL: could not find program unit being called: "LRIFFEL.PKG1"
ORA-06512: at line 1

This doesn't just seem to be a display issue as repeated calls to the procedure continue to get the same error.  Only when converting the raise_application_error back to a simple raise (or eliminating the exception block entirely) does the next execution include the ORA-04068 and the following execution succeed.

Can anyone confirm and/or explain this behavior?  Here is a recipe for reproducing the behavior:

--Session 1 - Create Objects
create or replace package pkg1 as
   vInteger Integer := 7;
   procedure procA;
end;
/

create or replace
package body pkg1 as
   procedure procA is
   begin
      DBMS_Output.Put_Line('ProcA');
   end;
end;
/

create or replace
package pkg2 as
   procedure procB;
end;
/

create or replace
package body pkg2 as
   procedure procB is
   begin
      pkg1.procA;
   exception
      when others then
         raise;
   end;
end;
/

--Session 2 - Test execution.
execute pkg2.procB;

--Session 1 - Change package header and thus invalidate package state.
create or replace package pkg1 as
   vInteger Integer := 8;
   procedure procA;
end;
/

--Session 2 - Observe failure due to changed package state.
execute pkg2.procB;

--Session 2 - Observe success due to re-initialized package state.
execute pkg2.procB;

--Session 1 - Change Raise to Raise_Application_Error.
create or replace
package body pkg2 as
   procedure procB is
   begin
      pkg1.procA;
   exception
      when others then
         raise_application_error(-20001,'Failed',True);
   end;
end;
/

--Session 1 - Change package header and thus invalidate package state.
create or replace package pkg1 as
   vInteger Integer := 9;
   procedure procA;
end;
/

--Session 2 - Observe failure due to changed package state.
execute pkg2.procB;

--Session 2 - Observe unexpected continued failures.
execute pkg2.procB;
execute pkg2.procB;
execute pkg2.procB;
execute pkg2.procB;
execute pkg2.procB;
execute pkg2.procB;
execute pkg2.procB;

--Session 1 - Change back to raise.
create or replace
package body pkg2 as
   procedure procB is
   begin
      pkg1.procA;
   exception
      when others then
         raise;
   end;
end;
/

--Session 2 - Observe failure on the first execution.
execute pkg2.procB;

--Session 2 - Observe success.
execute pkg2.procB;

Bug 229349 looks similar except that a trigger is involved.

The response from Oracle is that this is not really a bug because the application is catching the 4068 and handling it, thereby not allowing the normal resolution of the error to occur. Their explanation doesn't seem adequate to me because if the package throws an ORA-01476-Divide By Zero (for example) doing a raise_application_error does not effect whether the ORA-01476 is included in the stack or not.


Enforcing maximum number of relations for a record I am trying to figure out the way to enforce a maximum number of relations in a highly concurrent system.  The following is an example use case.
// Job table
job_id   | ... | num_positions
xx-xx-xx | ... | 3

// Positions table - only 1 spot left
position_id | job_id   | user_id
aa-aa-aa    | xx-xx-xx | ee-ee-ee
bb-bb-bb    | xx-xx-xx | ff-ff-ff

Now let's say two people try to fill the last spot at the same time. What is the best/right way to enforce that there will only ever be a maximum of 3 records? Ideally, the transaction that fires first fills the spot. What is the best way to implement this sort of functionality? Isolation levels etc?
Thanks in advance

Think about
// Positions table - 3 spots for `xx-xx-xx` job, only 1 spot is free
position_id | job_id   | user_id
------------+----------+---------
aa-aa-aa    | xx-xx-xx | ee-ee-ee    -- used spot
bb-bb-bb    | xx-xx-xx | ff-ff-ff    -- used spot
cc-cc-cc    | xx-xx-xx | NULL        -- free spot

The amount of spot rows per a job is equal to jobs.num_positions for according job_id value. They're created with according amount when the row is inserted into jobs table or when jobs table is updated and amount value for some job(s) is increased.
While user filling a spot users's id inserts to the user_id column. When user frees a spot its user_id value is updated to NULL back.
All operations are performed only by UPDATE queries. Spot filling query includes WHERE job_id = @job AND user_id IS NULL LIMIT 1.
If two concurrent users try to fill a spot the user which do this earlier will fill whereas the user who tries to do this later will fail because there is no record which matches the conditions WHERE job_id = @job AND user_id IS NULL.


MySQL bin log missing data? I'm trying to make heads and tails of my binary logs and am coming up short. I have many entries similar to the following from mysqlbinlog but I'm missing log data that I know should be there.

# at 1050548
#130617 18:40:03 server id 1  end_log_pos 1050619   Query   thread_id=844988    exec_time=0 error_code=0
SET TIMESTAMP=1371519603/*!*/;
BEGIN
/*!*/;
# at 1050619
# at 1050782
#130617 18:40:03 server id 1  end_log_pos 1050809   Xid = 33121028
COMMIT/*!*/;

It's puzzling because I get expected SQL in the mysqlbinlog output for statements executed in phpmyadmin but those coming from other PHP-based remote web servers appear to not be recorded.

My settings bin logging are:

bin_log = /path_to_sql_incrementals
binlog_ignore_db = mysql

Am I missing a logging option?

Mysql 5.0.95 / CentOS 5.9

You need to use the --base64-output=decode-rows and --verbose options with mysqlbinlog or you will not always see everything that's in the log.

You should also consider removing binlog_ignore_db from your configuration, because this not only prevents logging changes to the mysql schema, it also stops logging of events from your session that reference other schemas any time you USE mysql until you USE another database because of the way replication rules are interpreted.


exporting oracle tables on command line as sql files I use exp and expdp to export database tables on command line but it generates binary .dmp files. I want to export/import tables as .sql* files on command line like pl/sql developer app can do. Is there a specific command for this or extra parameters for exp/expdp?

*sql files must contain table creation scripts and insert statements.

Thanks.

You can generate DDL statements using imp/impdp utilities:

imp user/passwd file=export.dmp show=y log=export.sql 2> /dev/null
impdp user/passwd file=DIR:export.expdp sqlfile=DIR:export2.sql

But this method will generate just schema DDL. INSERT statements will not be generated.


GeoNear with Mongoose This is my mongoose schema -

var userDestinationSchema = mongoose.Schema({

    uid: String,
    update_time: Date,

    location:{                  //<INDEXED as 2d>
        lon: Number,
        lat: Number
    }
});

var userDestinationModel = mongoose.model('userDestinationModel', userDestinationSchema);

How do I query the model for geoNear?

I tried doing this and it ain't working.

userDestinationModel.geoNear([parseInt(req.params.lon,10),parseInt(req.params.lat,10)],
              { maxDistance : 50, spherical : true },
              function(err, results, stats) {
                   console.log(results);
                });

maxDistance : 50

What is the unit? Km?

I can answer the distance part, but I've never used geoNear with Mongoose I'm afraid - a good way to tell if the issue lies in how you are calling things in Mongoose versus the query itself would be to try it on the mongo shell first to verify that it is returning the results you expect there.

In terms of the distance, for legacy 2d points (which you appear to be using), the $maxdistance is specified in radians - there is a page here which outlines how to use radians with spherical geometry calculations.  If you use GeoJSON instead (which I would recommend), then it is specified in meters (reference).


Find and Replace text in all table using mysql query I have a database with name test having two tables

sample1(name, quote)
sample2(name, quote)

I need a replace function or procedure that does the following

Find columns with String data type (Text,varchar,char,etc) in all tables within test database
Look for a value in that column and replace it with a new value

I have found an stored procedure in this post but I don't understand how it works.

is there anyway to achieve this or adopt the stored procedure in the linked post for my use?

MySQL's replace function should come handy to replace the text you need:

update $table set column = replace(column, 'old_text', new_text') where column='old_text' and <other_conditions>;

Now, as you talk of replace in all tables, that's something not possible in single query. Though you can create a routine to do so!
For the sample you can refer a procedure which is finding a string in all tables of all databases.

(Though been already mentioned in comments) table_schema is a column name of information_schema.tables table which represents the database name.


High Disk I/O from sql server or is High disk I/O slowing sql server? I've been arguing with a DBA and a couple hardware guys about performance issues on our SQL server.  Normally everything is fine, however over the past few weeks we have been having huge lag spikes in sql server.  Its clear that SQL Server is waiting on disk I/O.  But I keep getting told that it is beacuse SQL Server is asking for abnormally high I/O.  Which isn't the case.  I can see from what is running that there is nothing out of the normal, and all the DBA cares to look at is what is causing the blocking and so on, which is useless.  For instance the major thing we see backing up is operation on the ASPState database, which we are using to manage the ASP Session State on the web servers.  These operations are normally never seen on Sp_who2 active results because they occur so quickly.  The database is in simple recovery mode and logging is miminal.  However during these lag spikes we can see alot select and update operations on the database being blocked or waiting.   I'm sure what is going on is that someone or some job is running something that is causing heavey disk usage on the raid arrays used for that databases log and data files.  The problem is proving it, since no one wants to admit they are doing something that is killing our website.

My question is what performance counters or whatever can I log that will help show that SQL server is waiting on I/O, but not because its asking for more than normaly, instead beacuse the disk is to busy to respond to the requests from sql server as quickly as it normally would?

Have a look at the following perfmon counters:

SQL Server, Buffer Manager Object:
Page lookups/sec
Page reads/sec
Readahead pages/sec
SQL Server, Access Methods Object
Full Scans/sec
Range Scans/sec
Skipped Ghosted Records/sec
SQL Server, Wait Statistics Object
Page IO latch waits

SQL Server driving a high number of IO requests would be corroborated with a high number scans, increase in page lookups and page reads and high page IO latch waits. Is worth trying a look at sys.dm_exec_query_stats for entries with high physical reads counts. They could quickly pinpoint the culprit.

In general approaching the problem as a performance troubleshooting problem, following a methodlogy like Waits and Queues is the right approach. You DBA seems to be doing the right thing so you should listen to him.