I found the normal ULimit setting for mongo db is as follows: I found the normal ULimit setting for mongo db is as follows:

    -f (file size): unlimited
    -t (cpu time): unlimited
    -v (virtual memory): unlimited [1]
    -n (open files): 64000
    -m (memory size): unlimited [1]
    -u (processes/threads): 32000

Just wondering why the "-u", recommendation is such high..

http://docs.mongodb.org/manual/reference/ulimit/

How many process do mongo generally spawn for each CRUD operations?

It's not a matter of the number of operations you will be doing, rather the number of connections and (possibly, though it would be unusual for this to play a big part) the number of server side javascript operations you plan to do (Map Reduce, mainly).

To explain: there will be a thread (and a file descriptor) for each connection made to/from the mongod process (similar for mongos) - therefore it is generally a good idea to have both values set beyond the hard coded 20,000 limit in MongoDB.  You can see this if you run htop or something like this command while you spin up new connections to the mongod or mongos processes:

ps uH p <PID_OF_U_PROCESS> | wc -l

Most users will never get anywhere near these maximum levels, so this is merely a precaution on most systems to avoid problems with low ulimits.  In a large cluster with many mongos processes you may see levels approaching this, but unless you are planning that level of deployment you will not have to worry.

For more information on the Map Reduce side of things, there is an excellent article on it, which includes thread use here.


How can I transpose multiple unrelated columns into rows in SQL Server? I have a table that looks like the following:
PersonId   EyeColor    HairColor    FavoriteTVShow      FavoriteMovie    FavoriteFood    BirthInfo
---------- ----------- ------------ ------------------- ---------------- --------------- ------------------
A123       Brown       Black        The Price Is Right  NULL             Pizza           New York City
A444       NULL        Brown        Friends             Elf              Steak           Seattle

My desired result set is this:
PersonId ColorInfo FavoriteInfo       BirthInfo
-------- --------- ------------------ -------------
A123     Brown     The Price Is Right New York City
A123     Black     Pizza              NULL
A444     Brown     Friends            Seattle
A444     NULL      Elf                NULL
A444     NULL      Steak              NULL

So for each PersonId, each group of columns (2 ColorInfo cols, 3 FavoriteInfo cols, and 1 BirthInfo col) is transposed into rows. I've tried variations of CROSS APPLY and UNPIVOT, but I can't seem to get it just right. I know it's a weird request, but can anyone think of a good way to do this?
Thank you in advance!
Here's the T-SQL:
--Create the table
CREATE TABLE #test (
PersonId VARCHAR(10),
EyeColor VARCHAR(20),
HairColor VARCHAR(20),
FavoriteTVShow VARCHAR(30),
FavoriteMovie VARCHAR(30),
FavoriteFood VARCHAR(30),
BirthInfo VARCHAR(30)
)

--Populate the table
INSERT INTO #test
VALUES
('A123', 'Brown', 'Black', 'The Price Is Right', NULL, 'Pizza', 'New York City'),
('A444', NULL, 'Brown', 'Friends', 'Elf', 'Steak', 'Seattle')

--See the original result set.
SELECT *
FROM #test

--My desired result set
SELECT *
FROM ( VALUES ('A123', 'Brown', 'The Price Is Right', 'New York City'),
              ('A123', 'Black', 'Pizza', NULL),
              ('A444', 'Brown', 'Friends', 'Seattle'),
              ('A444', NULL, 'Elf', NULL),
              ('A444', NULL, 'Steak', NULL) ) t (PersonId, ColorInfo, FavoriteInfo, BirthInfo)

I think that this will give you what you are looking for.  You have to hop through a few CTE's to unpivot the data, then add some attribute information and then pivot it back, but the output does match what you provided...
I had to add a CTE_RawData to convert everything to the same data type since the unpivot doesn't like mismatches...
DROP TABLE IF EXISTS #test

--Create the table
CREATE TABLE #test (
PersonId VARCHAR(10),
EyeColor VARCHAR(20),
HairColor VARCHAR(20),
FavoriteTVShow VARCHAR(30),
FavoriteMovie VARCHAR(30),
FavoriteFood VARCHAR(30),
BirthInfo VARCHAR(30)
)

--Populate the table
INSERT INTO #test
VALUES
('A123', 'Brown', 'Black', 'The Price Is Right', NULL, 'Pizza', 'New York City'),
('A444', NULL, 'Brown', 'Friends', 'Elf', 'Steak', 'Seattle')

;WITH CTE_RawData AS
    (
    SELECT PersonID
        , CONVERT(NVARCHAR(30), EyeColor) AS EyeColor
        , CONVERT(NVARCHAR(30), HairColor) AS HairColor
        , CONVERT(NVARCHAR(30), FavoriteTVShow) AS FavoriteTVShow
        , CONVERT(NVARCHAR(30), FavoriteMovie) AS FavoriteMovie
        , CONVERT(NVARCHAR(30), FavoriteFood) AS FavoriteFood
        , CONVERT(NVARCHAR(30), BirthInfo) AS BirthInfo
    FROM #Test AS T
    )
, CTE_UP AS
    (
    SELECT PersonID
        , Attribute
        , [Value]
    FROM CTE_RawData AS T
        UNPIVOT ([Value] FOR Attribute IN (EyeColor, HairColor, FavoriteTVShow, FavoriteMovie, FavoriteFood, BirthInfo)) AS unpvt
    )
, CTE_UPRN AS
    (
    SELECT PersonID
        --, Attribute
        , [Value]
        , AttributeCategory = CASE  WHEN Attribute IN ('EyeColor', 'HairColor') THEN 'ColorInfo'
                                                                WHEN Attribute IN ('FavoriteTVShow', 'FavoriteMovie', 'FavoriteFood') THEN 'FavoriteInfo'
                                                                WHEN Attribute IN ('BirthInfo') THEN 'BirthInfo'
                                                                ELSE Attribute
                                                                END
        , RN = ROW_NUMBER() OVER (PARTITION BY PersonID, CASE   WHEN Attribute IN ('EyeColor', 'HairColor') THEN 'ColorInfo'
                                                                WHEN Attribute IN ('FavoriteTVShow', 'FavoriteMovie', 'FavoriteFood') THEN 'FavoriteInfo'
                                                                WHEN Attribute IN ('BirthInfo') THEN 'BirthInfo'
                                                                ELSE Attribute
                                                                END
                                    ORDER BY PersonID)
    FROM CTE_UP AS U
    )
SELECT PersonID
    , RN
    , ColorInfo
    , FavoriteInfo
    , BirthInfo
FROM CTE_UPRN AS U
    PIVOT (MAX([Value]) FOR AttributeCategory IN (ColorInfo, FavoriteInfo, BirthInfo)) AS pvt
ORDER BY PersonID
    , RN


If you just run mysql command under root user you will be granted access without asked for password, because socket authentication enabled for root@localhost.

This guide is misleading.

The only way to set password is to switch to native authentication like:

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'test';


why/how does the number of matched columns influences the way of excecuting a query Imagine the following situation:

Table A uses MyISAM and contains 4 fields (text) with a combined FULLTEXT-index.

FULLTEXT fulltext1 | fulltext2 | fulltext3 | fulltext4

Table B uses InnoDB and contains some other fields, where 5 of them are indexed as 'ORD'.

ORD order1 | order2 | order3 | order4 | order5

I like to do a full-text-search on tableA by joining tableB on it's foreign id and ordering the results by the indexed columns of tableB.

Query1 - match all 4 fulltext-columns:

SELECT `tableB`.`id`
FROM `tableA`
INNER JOIN `tableB` ON `tableA`.`tableB_id` = `tableB`.`id`

WHERE MATCH (
`tableA`.`fulltext1`, `tableA`.`fulltext2`, `tableA`.`fulltext3`, `tableA`.`fulltext4`
)
AGAINST (
'+search*'
IN BOOLEAN
MODE
)
ORDER BY
`tableB`.`order1` DESC,
`tableB`.`order2` DESC,
`tableB`.`order3` DESC,
`tableB`.`order4` DESC,
`tableB`.`order5` DESC
LIMIT 0,15

takes 1.6565 seconds.

EXPLAIN Query1:

select_type  table   type     possible_keys     key      key_len  ref   rows            Extra
SIMPLE       tableA  fulltext PRIMARY,FULLTEXT  FULLTEXT    0     1                     Using where; Using temporary; Using filesort
SIMPLE       tableB  eq_ref   PRIMARY           PRIMARY     4     db.tableA.tableB_id   1

no index is used, temporary table was necessary. I don't know what "Extra 1" in second row means.

Query2 - match only 3 columns:

SELECT `tableB`.`id`
FROM `tableA`
INNER JOIN `tableB` ON `tableA`.`tableB_id` = `tableB`.`id`

WHERE MATCH (
`tableA`.`fulltext1`, `tableA`.`fulltext2`, `tableA`.`fulltext3`
)
AGAINST (
'+search*'
IN BOOLEAN
MODE
)
ORDER BY
`tableB`.`order1` DESC,
`tableB`.`order2` DESC,
`tableB`.`order3` DESC,
`tableB`.`order4` DESC,
`tableB`.`order5` DESC
LIMIT 0,15

takes 0.0114 seconds.

EXPLAIN Query2:

select_type table   type    possible_keys  key     key_len  ref           rows  Extra
SIMPLE      tableB  index   PRIMARY        ORD     783      NULL          15    Using index
SIMPLE      tableA  eq_ref  PRIMARY        PRIMARY 4        db.tableB.id  1     Using where

The order of the listed tables has changed. For tableB, the index was used, for tableA no temporary table was necessary.

It doesn't matter which fulltext-column I exclude from the query - as long as I match less than 4 of them, I get the duration and explanation of the showed Query2.
Maybe interesting: the amount of rows is equal in both tables, about 180k.

I'd like to know the reason why this behaviour occurs. I mean, it seems like the whole way of excecution would depend on the number of matched columns.

edit: now I'm completely confused. I deleted the fulltext-index. I matched all 4 (ex-)fulltext-columns (Query1). Now it takes 0.1205 seconds. EXPLAIN shows me that no temporary table is needed; but I wonder how I can match faster in Boolean Mode without having a fulltext-index.

I can provide with a general explanation, but it may not apply specifically to your particular case:

The way decision making works is by evaluation cost of execution plan, then picking up what is hopefully the cheapest plan. This you already know.

When it comes to indexing, though, stuff are getting interesting. The way to evaluate the usefulness or viability of an index is to estimate the selectivity given some value.

For the moment, forget about your FULLTEXT index, and let's assume a simple index on some column col1, and another index on some column col2. Given the following two queries:

SELECT * FROM t WHERE col1 < 10 and col2 = 4;
SELECT * FROM t WHERE col1 BETWEEN 100 AND 110 and col2 = 4;

It may happen that the query is evaluated differently in these two cases. Why? Because it may happen that col2 = 4 returns more rows than col1 < 10, in which case we prefer to use index on col1. But then, it may return less rows than col1 BETWEEN 100 AND 110, in which case we prefer the index on col2.

Your case is not very much different. MySQL estimates the number of rows returned by some index query. When you use more columns, MySQL gets the impression your index is likely to result with few rows. So it chooses to start with TableA, then joins what should be very few rows with TableB.

But if MySQL believes the index to return many rows, it may prefer starting with TableB. Why is that? Because you are sorting on indexed columns of TableB. Sorting is a lot of work, too. So MySQL may choose to first sort the rows, then join to TableA and filter by fulltext index. It may not be a bad idea if the fulltext search yields with many rows anyhow.


See if XML element exists at any level in document with a specific value Is it possible to query XML to find if a particular element has a certain value? For example, if I wanted to see if the below XML has the value "Brandt" in <ContactFName>.

But note, the location of the element may change. In some cases it may be in /root/MCTLocations/MCTLocation, or it may jump to under the root, or appear somewhere else...

And, is it possible to parameterize the element name?

DECLARE @table TABLE (XmlCol XML)

INSERT INTO @table (XmlCol) VALUES ('
<root>
<MCTClientName>John</MCTClientName>
<MCTClientCity>Palm Beach</MCTClientCity>
<MCTLocations>
    <MCTLocation>
        <Address>1234 Main Street</Address>
        <ContactFName>Chris</ContactFName>
        <ContactLName>Brandt</ContactLName>
    </MCTLocation>
</MCTLocations>
</root>')

SELECT * FROM @table WHERE ??

For this you want to use the .exist() XML function as it will return a BIT (i.e. boolean) value indicating whether or not the XQuery find anything.

To handle the non-static location of an element, you would use either * (indicating that it should check all nodes of a particular level, but not other levels), or // (indicating that it should check all nodes at that level and below).

The following examples use the example query from the question as a base, and adds a few test cases to place the element at different levels, and adds a test case that changes the name to show that the XQuery isn't just selecting everything.

Test Setup (run once)

SET NOCOUNT ON;
CREATE TABLE #Table (ID INT NOT NULL, XmlCol XML);

INSERT INTO #Table (ID, XmlCol) VALUES (1, N'
<root>
<MCTClientName>John</MCTClientName>
<MCTClientCity>Palm Beach</MCTClientCity>
<MCTLocations>
    <MCTLocation>
        <Address>1234 Main Street</Address>
        <ContactFName>Chris</ContactFName>
        <ContactLName>Brandt</ContactLName>
    </MCTLocation>
</MCTLocations>
</root>');

INSERT INTO #Table (ID, XmlCol) VALUES (2, N'
<root>
<MCTClientName>John</MCTClientName>
<MCTClientCity>Palm Beach</MCTClientCity>
<MCTLocations>
    <MCTLocation>
        <Address>1234 Main Street</Address>
        <ContactFName>Chris</ContactFName>
        <ContactLName>Grandt</ContactLName>
    </MCTLocation>
</MCTLocations>
</root>');

INSERT INTO #Table (ID, XmlCol) VALUES (3, N'
<root>
<MCTClientName>John</MCTClientName>
<MCTClientCity>Palm Beach</MCTClientCity>
<MCTLocations>
    <MCTLocation>
        <Address>1234 Main Street</Address>
        <ContactFName>Chris</ContactFName>
    </MCTLocation>
</MCTLocations>
<ContactLName>Brandt</ContactLName>
</root>');

INSERT INTO #Table (ID, XmlCol) VALUES (4, N'
<root>
<MCTClientName>John</MCTClientName>
<MCTClientCity>Palm Beach</MCTClientCity>
<MCTLocations>
    <MCTLocation>
        <Address>1234 Main Street</Address>
        <ContactFName>Chris</ContactFName>
    </MCTLocation>
    <NewElement>
       <SubElement>
          <ContactLName>Brandt</ContactLName>
       </SubElement>
    </NewElement>
</MCTLocations>
</root>');

INSERT INTO #Table (ID, XmlCol) VALUES (5, N'
<root>
<MCTClientName>John</MCTClientName>
<MCTClientCity>Palm Beach</MCTClientCity>
<MCTLocations>
    <MCTLocation>
        <Address>1234 Main Street</Address>
        <ContactFName>Chris</ContactFName>
    </MCTLocation>
    <NewerElement>
       <ContactLName>Brandt</ContactLName>
    </NewerElement>
</MCTLocations>
</root>');

INSERT INTO #Table (ID, XmlCol) VALUES (6, N'
<root>
<MCTClientName>John</MCTClientName>
<MCTClientCity>Palm Beach</MCTClientCity>
<MCTLocations>
    <MCTLocation>
        <Address>1234 Main Street</Address>
        <ContactFName>Chris</ContactFName>
    </MCTLocation>
    <NewerElement>
    </NewerElement>
</MCTLocations>
</root>
<ContactLName>Brandt</ContactLName>
');

Test 1 (* in place of a node name)

This will check all nodes at the specified level, which in this case is just under <root>. But it won't check other levels.

SELECT *
FROM   #Table tmp
WHERE  tmp.[XmlCol].exist(N'/*/ContactLName[text()="Brandt"]') = 1;

Returns row with ID value of 3.

Test 2 (* in place of a node name)

This will check all nodes at the specified level, which in this case is just under <root><MCTLocations>. But it won't check other levels.

SELECT *
FROM   #Table tmp
WHERE  tmp.[XmlCol].exist(N'/root/MCTLocations/*/ContactLName[text()="Brandt"]') = 1;

Returns rows with ID values of 1 and 5.

Test 3 (// in place of a node name)

This will check all nodes starting at the specified level, which in this case is just under <root><MCTLocations>, and below.

SELECT *
FROM   #Table tmp
WHERE  tmp.[XmlCol].exist(N'/root/MCTLocations//ContactLName[text()="Brandt"]') = 1;

Returns rows with ID values of 1, 4, and 5.

Test 4 (/* or */ in place of a node name)

SELECT *
FROM   #Table tmp
WHERE  tmp.[XmlCol].exist(N'*//ContactLName[text()="Brandt"]') = 1;

-- and:

SELECT *
FROM   #Table tmp
WHERE  tmp.[XmlCol].exist(N'//*/ContactLName[text()="Brandt"]') = 1;

Both return rows with ID values of 1, 3, 4, and 5.

These do not return row ID of 6 due to the * being a placeholder for a single node, hence the highest level allowed would be under <root> (or any top-level node).

Test 5 (// at the top level)

This will check all nodes starting at the top level.

SELECT *
FROM   #Table tmp
WHERE  tmp.[XmlCol].exist(N'//ContactLName[text()="Brandt"]') = 1;

Returns rows with ID values of 1, 3, 4, 5, and 6.

Test 6 (use local variable value for element text in XQuery)

DECLARE @Name NVARCHAR(50) = N'Brandt';

SELECT *
FROM   #Table tmp
WHERE  tmp.[XmlCol].exist(N'//ContactLName[text()=sql:variable("@Name")]') = 1;

SET @Name = N'Grandt';

-- exact same query, just different value in the variable
SELECT *
FROM   #Table tmp
WHERE  tmp.[XmlCol].exist(N'//ContactLName[text()=sql:variable("@Name")]') = 1;

First query returns rows with ID values of 1, 3, 4, 5, and 6.

Second query returns row with ID value of 2.

Test 7 (use function and string literal for element name in XQuery)

SELECT *
FROM   #Table tmp
WHERE  tmp.[XmlCol].exist(N'//.[local-name()="NewerElement"]') = 1;

Returns rows with ID values of 5 and 6.

Test 8 (use function with local variable value for element name in XQuery)

DECLARE @Node NVARCHAR(50) = N'SubElement';

SELECT *
FROM   #Table tmp
WHERE  tmp.[XmlCol].exist(N'//.[local-name()=sql:variable("@Node")]') = 1;

Returns row with ID value of 4.

Test 9 (put all of the pieces together)

DECLARE @NodeName NVARCHAR(50) = N'ContactLName',
        @NodeText NVARCHAR(500) = N'Brandt';

SELECT *
FROM   #Table tmp
WHERE  tmp.[XmlCol].exist(N'//.[local-name()=sql:variable("@NodeName")]
   [text()=sql:variable("@NodeText")]') = 1;

Returns rows with ID values of 1, 3, 4, 5, and 6.

General XML Note:

XML data (in SQL Server) is encoded as UTF-16 Little Endian, same as NVARCHAR / NCHAR. Hence it is best to prefix sting literals with a capital-N when the value is really XML.


TABLESPACE creation error I have an error "ORA-01537" when I create a tablespace.

create tablespace "VOLUMT" datafile 'VOLUMT.dbf' size 250M autoextend on;

Then, it shows like following.

  ERROR at line 1: ORA-01537: cannot add file 'VOLUMT.dbf' - file
  already part of database

I just change the tablespace name of VOLUMT to TEST01, and it works..

  Tablespace created.

So, I get into the directory of db and searched the related tablespace..

${HOME}/oracle/db/oradata/ORAC12

However, I can't find any.. Coudl you help me to solve this problem? Also I need to use "VOLUMT" for tablespace creation.. not TEST01..

-- Additional --

select TABLESPACE_NAME from DBA_DATA_FILES;

TABLESPACE
----------
SYSTEM
SYSAUX
USERS
TEST01

I can see TEST01 Tablespace, but I can't find any datafiles in the directory.
Also I can't find any VOLLMT tablespace in it..

Thank you.

all the guys..

First of all, really appreciate to you all for replying...

When I input the follwing

select   file_name,
         file_id,
         tablespace_name,
         status,online_status
from     dba_data_files
order by tablespace_name;

The output is

FILE_NAME
--------------------------------------------------------------------------------
   FILE_ID TABLESPACE STATUS    ONLINE_
---------- ---------- --------- -------
/home/oracle/db/oradata/ORAC12/ID/sysaux01.dbf
    28 SYSAUX     AVAILABLE ONLINE

/home/oracle/db/oradata/ORAC12/ID/system01.dbf
    27 SYSTEM     AVAILABLE SYSTEM

/home/oracle/db/oradata/ORAC12/ID/ID_user01.dbf
    29 USERS      AVAILABLE ONLINE

There is no such as "VOLUMT" tablespace. When I re-try to create a "TEST01" tablespace, eventhough it's already deleted, it shows 'the file exists already'..

When I get into the directory, there are other SID's as well. And, one of them has "VOLUMT". However, they are different "SID" so they can have same name of tablespace, isn't it?


Applying a rule dynamically to all columns in different tables I am trying to apply a rule to remove double quotes from any value in my DB.
I declared the table name as an Input parameter (This is required). Now I want the rule to run dynamically against all columns & all rows in the table. I have this so far. The rule runs successfully when executed but it only runs against the first column that appears in the information schema, How to make it run against the rest of columns ?

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_tf_RemoveDoubleQuotes]') AND type in (N'P', N'PC'))
    DROP PROCEDURE [dbo].usp_tf_RemoveDoubleQuotes
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[usp_tf_RemoveDoubleQuotes]
(
     @tablename VARCHAR(100)
)
AS
SET NOCOUNT ON;

-- Declare variables

DECLARE  @FieldName varchar(100)
        ,@sql varchar(1000)

BEGIN
    -- Get the table and field for validating

    SELECT   @FieldName = COLUMN_NAME
    FROM     INFORMATION_SCHEMA.COLUMNS
    WHERE    TABLE_NAME = @tablename

    -- Update field

    SET @sql = '
    UPDATE  ' + @tablename + '
    SET     ' + @FieldName + ' = (SELECT REPLACE(' + @FieldName + ',''"'',''''))'

    EXECUTE(@sql)

END
GO

You can retrieve only one name the way you are doing it. One relatively easy change to your current script to account for all columns would be to define a cursor on INFORMATION_SCHEMA.COLUMNS to go through all columns in a loop and apply your current method, as Justin Cave suggested in a comment:

DECLARE CURSOR ColumnsCursor LOCAL FAST_FORWARD
FOR
SELECT
  COLUMN_NAME
FROM
  INFORMATION_SCHEMA.COLUMNS
WHERE
  TABLE_NAME = @tablename
;

OPEN ColumnsCursor;

FETCH NEXT FROM ColumnsCursor INTO @FieldName;

WHILE @@FETCH_STATUS = 0
BEGIN
  SET @sql = '
  UPDATE  ' + @tablename + '
  SET     ' + @FieldName + ' = (SELECT REPLACE(' + @FieldName + ',''"'',''''))';

  EXECUTE(@sql);

  FETCH NEXT FROM ColumnsCursor INTO @FieldName;
END;

CLOSE ColumnsCursor;

DEALLOCATE ColumnsCursor;

Another good point by Justin is that you may want to introduce an additional filter to your COLUMNS query to process only columns that are either varchar or nvarchar:

...
WHERE
  TABLE_NAME = @tablename
  AND DATA_TYPE IN ('varchar', 'nvarchar')

One other minor point is that (SELECT REPLACE(...)) is redundant, just REPLACE(...) is enough, so the @sql assignment would look like this:

  SET @sql = '
  UPDATE  ' + @tablename + '
  SET     ' + @FieldName + ' = REPLACE(' + @FieldName + ',''"'','''')';

However, while these changes are simple, the resulting script would be rather inefficient against tables with multiple string columns. That might not matter much if yours is a one-off task, but you might still be interested in avoiding multiple passes over a table where one is enough to complete the job.

And you can do that if you change the basic logic slightly – instead of these steps:

take one column name,
build an UPDATE statement,
execute,
repeat for the next column until done,

you implement it like this:

take all columns and build an assignment list for the SET clause of the UPDATE,
build the UPDATE statement around the list,
execute.

Here is what it can look like in Transact-SQL:

DECLARE @SetList nvarchar(max), @sql nvarchar(max);

SELECT
  @SetList = ISNULL(@SetList + N', ', N'')
           + QUOTENAME(COLUMN_NAME) + N' = REPLACE('
           + QUOTENAME(COLUMN_NAME) + N',''"'','''')'
FROM
  INFORMATION_SCHEMA.COLUMNS
WHERE
  TABLE_NAME = @tablename
  AND DATA_TYPE IN ('varchar', 'nvarchar')
;

SET @sql = N'UPDATE ' + @tablename + ' SET ' + @SetList + ';';

EXECUTE(@sql);


How do I make PostgreSQL log the file path and line number of the errors? (If it's even possible at all.) My postgresql.conf contains this:
log_destination = 'csvlog'
logging_collector = on
log_directory = 'C:\\Users\\John Doe\\Documents\\PostgreSQL logs'
log_filename = 'PG_%Y-%m-%d_%H;%M;%S'
log_rotation_age = 1min
log_rotation_size = 0
log_truncate_on_rotation = on
log_min_error_statement = 'info'

This makes PG log its errors to CSV files in my custom dir.
Then, I have a constantly running script which looks for new files in that dir, and if it finds any, COPYies it into a custom database table (as described/recommended in the manual) and deletes the file that was successfully added. It doesn't touch the currently active log file.
My table only has one custom column, a boolean called "unimportant", which I set depending on whether I think the error is important or not, so that I can hide the noise in various views and statistics.
Since PostgreSQL does not provide any fields such as "file path" or "line number", I'm at a total loss as to what caused the various logged errors. The only thing I have to go by is the application_name, which is uselessly limited due to being 64 characters maximum and no Unicode, even preventing me from abusing this field to, for example, set it to the relevant file path. But even if it were possible, I wouldn't want to do that, since application_name is supposed to be the application name and nothing else. (But again, it doesn't matter since the path would be too long and contain non-ANSI characters anyway.)
I've thought long and hard about this, but I just can't figure out a way to make me able to know where exactly the error occurred.
Application (PHP) errors will include the relevant location, but these errors are not always logged (I'm unsure why). Here are some examples of errors which don't trigger PHP errors but are logged by PostgreSQL:

there is no transaction in progress for the query COMMIT in application_name whatever123. Okay? Which script did it? And which line?
there is already a transaction in progress for the query BEGIN in application_name whatever123. Okay? Which script did it? And which line?
current transaction is aborted, commands ignored until end of transaction block and deadlock detected ones at least specify what query caused it, but again, I'd have to hunt it down manually by searching for parts of the query. Usually, I don't bother, or am unable to because the query was constructed in code in an unsearchable way.

Is there really no way to solve this? Any idea why the above errors aren't considered "real" errors to PHP (or rather, the pg_*/pglib functions)? And can I control that somehow?

Database logging is the wrong end to handle the problem, since the database does not have the required information.
You will have to write your client application so that it logs the information you need whenever it receives a database error.


MYSQL DISTINCT on one column PLUS oher values I wish to retrieve ordno and invno where ordno is unique from two different tables (archived and none archived invoices). The closest I have come is using the following using UNION DISTINCT but I also need the invno - any help is welcome?

SELECT INInvoiceHeaders.ordno
FROM INInvoiceHeaders
WHERE (MONTH(INInvoiceHeaders.invdate)=11
  AND YEAR(INInvoiceHeaders.invdate)=2016)
UNION DISTINCT
SELECT INArchivedInvoiceHeaders.ordno
FROM INArchivedInvoiceHeaders
WHERE (MONTH(INArchivedInvoiceHeaders.invdate)=11
  AND YEAR(INArchivedInvoiceHeaders.invdate)=2016)
GROUP BY ordno

SELECT DISTINCT(table1.ordno), table1.invno
FROM ...

You can't use DISTINCT like that, it is not a function. It's a modifier (of the SELECT or UNION clause) and it applies to all the row, not a single column.

You can use GROUP BY instead and MIN() function to find the smallest inventory number for each order, in each table.
Then use UNION (ALL, not DISTINCT) to get these results from both tables.
Then GROUP BY again in case some inventory numbers appear in both tables:

SELECT ordno, MIN(invno) AS invno
FROM
  ( SELECT ordno, MIN(invno) AS invno
    FROM INInvoiceHeaders
    WHERE invdate >= DATE '2016-11-01'
      AND invdate  < DATE '2016-11-01' + INTERVAL 1 MONTH

    UNION ALL

    SELECT ordno, MIN(invno)
    FROM INArchivedInvoiceHeaders
    WHERE invdate >= DATE '2016-11-01'
      AND invdate  < DATE '2016-11-01' + INTERVAL 1 MONTH
    GROUP BY ordno
  ) AS t
GROUP BY ordno ;


Transaction Replication on AlwaysOn In our AlwayOn environment I have two SQL servers 2014 Ent.edition (SERVER A, and SERVER B). I setup a transaction replication for [database A ], pointing to listener which shouldn't cause any issue if a failover happened. Tested, worked just fine after a failover, but when I shut off one of the servers in AOAG ,in test environment, replication stops working. There is no error on the replication monitor. For testing purpose,I have inserted few records on the database it works fine on AOAG, but on the Subscriber the data modification is not replicated until I bring up the other server. Once the server is up, then the replication start to work. I'm pretty sure, I might be missing something, this can't be this way by design.

Any Idea/recommendation ?

Here is what I have for Replication on AOAG.

Distribution Server A ( contains Distribution database ) - SQLSERVER

This on a remote server.

two subscribers - Sv1 and sv2 - SQLSERVER 2008R2 STANDARD
Pbulishers ( SERVER A and B - from AOAG ) - SQLSERVER 2014

The Log Reader Agent will only read as far as the last transaction committed to all other Availability Group replicas. This means that if one AG replica is offline, Replication will stop reading transactions until that AG replica is back online. This is done to be ultra-safe and ensure that only fully committed transactions are replicated.

In some cases, this may be too conservative of a rule for your setup. In that case, you can add Trace Flag 1448 to your startup parameters, which will disable this behavior for async replicas. If a synchronous replica is offline, it will still prevent the log reader agent from reading unsent transactions for Replication.

Why does Microsoft impose this restriction on synchronous replicas? If the LSN has not been hardened on the synchronous replica, then the transaction isn't fully committed. Replication is very conservative in what it considers a fully-committed transaction that is safe for replication. Say for example, that the AG isn't in sync due to a network issue, or some other problem (ie, it is online, but the Primary node just doesn't know that). If you force a failover of the AG, and allow data loss, then your replication secondary would have data that was lost during failover-- potentially breaking replication.

This TF will affect the server that is currently hosting the primary database replica, and thus must be set on every server that might host the primary replica for the AG.

From the Microsoft documentation on TF 1448:

  Enables the replication log reader to move forward even if the async
  secondaries have not acknowledged the reception of a change. Even with
  this trace flag enabled the log reader always waits for the sync
  secondaries. The log reader will not go beyond the min ack of the sync
  secondaries. This trace flag applies to the instance of SQL Server,
  not just an availability group, an availability database, or a log
  reader instance. Takes effect immediately without a restart. This
  trace flag can be activated ahead of time or when an async secondary
  fails.


SQL Server Auditing is not available in Standard editions? If the Auditing option is not available in SQL Server 2008 R2 Standard edition, then why I still see it under the Security object in SSMS?

After reading online, it seems that my option is to use the Trace Profiler that it seems will be deprecated by the time I master it! Can I use extended events in my SQL version?

I only want to capture events like what stored procedures are used more frequently, who are the login names and staff that runs them through a front end application which is built in Visual Basics... Thanks for any advice. Thanks.

The auditing option, along with the resource governor and other enterprise only features  are always shown in SSMS no matter which edition you are running. Part of it is probably for familiarity in UX design and part marketing. ;)

Yes, you can use extended events to capture some of this information. Setting up a server side trace to capture this is also very simple and well documented.


Using dynamic column names in PostgreSQL I am using PostgreSSQL 8.3.

I want to execute this query:

select COALESCE(col_fr,col,'Unknown') from my_table where id = 'abc';

Twist in the story is column name colum_fr should be generated dynamically. my_table has different columns for different languages. Something like:

id   col   col_ja    col_fr

I am using this query in crystal reports where I can pass string parameter for language:

select COALESCE(col_||{?parameter},col,'Unknown') from my_table where id = 'abc';

which will internally be converted to something like the following, if {?language} value is fr:

select COALESCE(col_||'fr',col,'Unknown') from my_table where id = 'abc';

which will never work.

I don't want to use select case, to make it dynamic.
As an alternative solution I also tried to create a stored procedure:

CREATE OR REPLACE FUNCTION get_policy_name (
  id text,
  lang   text,
  def_value text
)
RETURNS SETOF record AS
$body$
DECLARE
   sql text;
BEGIN
    sql := 'SELECT   COALESCE(col_'||quote_ident(lang)||',col,'||quote_literal(def_value)||')FROM my_table WHERE id ='||quote_literal(id);

   RETURN QUERY EXECUTE sql
END;
$body$ LANGUAGE plpgsql;

Which should return single record.
It is not working. What am I missing? Does PostgreSSQL 8.3 support RETURN QUERY EXECUTE?

RETURN QUERY EXECUTE was introduced with Postgres 8.4.
Your version is just too old and unsupported by now. Upgrade to a more recent version.

Also, dynamic column names in the result are very hard to come by. It's a principle of SQL that it wants to know the return type - including the names - up front.

Returning anonymous records without a column definition list only works for a single record. Else you have to provide a column definition list in the call. Details under his question on SO:
Return multiple fields as a record in PostgreSQL with PL/pgSQL

There are limited ways around this with with polymorphic types. Advanced stuff:
Refactor a PL/pgSQL function to return the output of various SELECT queries

BTW, your function would look like this in modern PL/pgSQL:

CREATE OR REPLACE FUNCTION get_policy_name (
  _id        int,
  _lang      text,
  _def_value text
) RETURNS TABLE (col text) AS
$func$
BEGIN
   RETURN QUERY EXECUTE
   format('SELECT COALESCE(%I, col, $1)
           FROM my_table WHERE id = $2'
         , 'col_' || _lang)
   USING _def_value, _id;
END
$func$ LANGUAGE plpgsql;

Call:

SELECT col AS col_fr FROM get_policy_name (1, 'fr', 'foo');

Here, I am simply using a column alias in the call to achieve what you want. Much easier than dynamic column names ...


Why shouldn't a pre-hashed password be used with CREATE LOGIN all the time? (For the purpose of this question, assume that I only need to support SQL Server 2008 and higher. Or to future-proof this question, let's say recent, current, and near-future versions.)

The documentation for the CREATE LOGIN statement says (emphasis mine):

  HASHED
  Applies to SQL Server logins only. Specifies that the password entered after the PASSWORD argument is already hashed. If this option is not selected, the string entered as password is hashed before it is stored in the database. This option should only be used for migrating databases from one server to another. Do not use the HASHED option to create new logins. The HASHED option cannot be used with hashes created by SQL Server 7 or earlier.

There's no explanation as to why this option shouldn't be used to create new logins, and if there is a reason, it's not obvious (to me, at least).

The documentation references KB918992, which while a little unclear, describes steps that indicate legacy (2000-2008 R2) password hashes are upgraded automagically. So I can take a password hash generated on 2000-2008 R2 and CREATE LOGIN ... WITH PASSWORD ... HASHED on 2012(+?) and have the hash converted to 2012 format when the principal logs in for the first time. I tested this does indeed work as described.

The password hash itself appears to have a 6-byte signature/metadata header, consisting of what I think is a 2-byte tag/version and a 4-byte salt. When a hash is upgraded, the tag/version is incremented while the salt remains the same.

The question is then: if Microsoft has already baked in this kind of future compatibility (which I assume will need to be maintained going forward), is there any reason why this method shouldn't be used for creating new logins?

The hashing method is presumably an implementation detail which may or may not change in future releases (as it has at least once already). They're telling you not to do it in order to absolve themselves of breaking your scripts/automation if you try to run them on newer or older releases. It's partially supported purely to allow for migrating logins.

At some point in the process, you would have to use the clear-text password to generate the hashed password, so I'm not sure you'd really gain much by supplying pre-hashed passwords.


PostgreSQL : Restrict (another) superuser from accessing database I'm new in PostgreSQL and looking to find if this is possible.

There are two superusers, 'postgres' (which is default superuser from PostgreSQL) and 'super2'.

Superuser 'super2' creates a new database named 'Movies' and being the owner of it.

Is it possible to restrict superuser 'postgres' to access the database 'Movies' so that only superuser 'super2' could access it, because he is the owner of the database.

If it is possible, then how to make it happen?

francs is entirely correct. superuser means just that. They're all-powerful. They can do anything, including load additional code into the database, modify tables on disk directly, etc. See CREATE ROLE and the documentation on client authentication for more information.

If you don't trust them, don't give them superuser rights. In this case, it sounds like you should've just done a CREATE DATABASE movies WITH OWNER the_other_user and given them a normal, non-superuser login. Or if they need to create their own databases, you could give them CREATEDB rights.

The only way to restrict a superuser is by changing the C code inside PostgreSQL directly. Even then you'd probably be wasting your time, as a determined user could get around restrictions like a ProcessUtility_hook filter if they have superuser access.

Remove their superuser access. Unless they've had the foresight to backdoor your system in a way that'll let them regain access (unlikely, and not trivial) you should be OK.

ALTER USER the_user WITH NOSUPERUSER;

You can add CREATEDB rights if you want them to have the ability to create databases.


Why don't HOST comands appear in the spool? spooltest.sql:

SPOOL test.log
CLEAR SCREEN
SET VERIFY OFF
PROMPT Queries will appear in spool
select sys_context('USERENV', 'INSTANCE_NAME') from dual;
PROMPT Output from the HOST command will not
HOST ECHO %COMPUTERNAME%
SPOOL OFF

sqlplus output:

Queries will appear in spool

SYS_CONTEXT('USERENV','INSTANCE_NAME')
---------------------------------------------------

wibble

Output from the HOST command will not
AWESOMESERVERNAME

SQL>

test.log:

Queries will appear in spool

SYS_CONTEXT('USERENV','INSTANCE_NAME')
--------------------------------------------------------------------------------
wibble

Output from the HOST command will not

If it's not possible to capture HOST output, can someone please suggest a workaround.

Yeah spool only captures output from SQLPlus itself. Try this - Before invoking your OS command, turn spooling off. Then pipe the output to the end of the file. Then you can use spool append to start spooling from where you left off again:

spool test.log
select blah from blah;
spool off
!echo %COMPUTERNAME% >> test.log
spool test.log append
select * from argh;


SQL Server Management Studio .bak files I'm new to SQL Server SMS and need help.  I have been asked to check that .BAK files restored be checked using SELECT queries in SQL SMS.  I have tried to create a new  database but get the error "CREATE DATABASE permission denied in database 'master'.  (Microsoft SQL Server, Error: 262)".  It does not give much information so I can find out and Googling it didn't help either.  Also if I drag and drop the .BAK file into the SQL SMS window, I am told that there is no editor available?

First, you need CREATE DATABASE permissions to create a database. The db_creator role may be best for you in this case. Second, you need to script the restore.

For example:

RESTORE DATABASE someDatabase
FROM DISK = N'D:\sql\backups\myDatabase.bak'
WITH STATS = 10 ;
GO


`Invoke-SqlCmd` does not display RAISERROR messages where `sqlcmd` does I switched from sqlcmd to Invoke-SqlCmd, and suddenly my RAISERROR messages disappeared. When I run it with sqlcmd, they appear. Is there any way to make them visible with Invoke-SqlCmd?
RAISERROR (N'Hello World',0,1) WITH NOWAIT;

Invoke-SqlCmd -Server $env:MSSQL_SERVERNAME -Username $env:MSSQL_USERNAME -Password $env:MSSQL_PASSWORD -Database "mydatabase" -Query "EXEC dbo.myprocedure" -OutputSqlErrors $true;

/opt/mssql-tools/bin/sqlcmd -S $env:MSSQL_SERVERNAME -U $env:MSSQL_USERNAME -P $env:MSSQL_PASSWORD -d mydatabase -Q "EXEC dbo.myprocedure";

According to the documentation for Invoke-Sqlcmd, you can use -verbose to display the output messages.  From that post:
Example 5: Run a query and display verbose output
PS C:\> Set-Location "SQLSERVER:\SQL\MyComputer\MainInstance"
PS C:\> Invoke-SqlCmd -Query "PRINT N'abc'" -Verbose
VERBOSE: abc

I have bat file processes that use this technique and pipe the output of the messages to a file.  Example:
-Verbose 4>&1> 'C:\AperioSync\SyncOutput.txt'"


Mongodb cluster issue I would like to put in place a cluster with 2 instances of Graylog. Ideally, one of them will receive logs from servers in the cloud and the other one on premise will receive logs from servers that are running on the premise itself. After reading the documentation, I've understood that I have to put in place a replication of Mongodb instances.
Mongodb recommended to have an odd number of servers in the cluster but I would like to ask whether or not it's mandatory for the cluster to function properly or would it be ok to make a cluster only with 2 servers?

At this point, it seems like my Graylog instance located in the cloud will get logs only from servers in the cloud. Same is applicable for the on premise Graylog instance which will get logs only from on premises servers. According to the documentation, the data from the primary will be replicated on secondary servers. However, I wonder whether or not primary servers will add the data over the data which is already existing on the secondary instance. Will it replace them instead?
Could you please let me know how can I get 1 instance in the cloud and one on premises yet reach all the of my data from one point/location?

Thank you for your help in advance

Mongodb recommended to have an odd number of servers in the cluster
  but I would like to ask whether or not it's mandatory for the cluster
  to function properly or would it be ok to make a cluster only with 2
  servers?

3 members (or a higher odd number) is really best, though. Replica sets go read only if a majority of the set isn't available, so if you lose a member in a two-member set the remaining member becomes read only.

You can run two full members plus an "voting-only" member (called an arbiter) as well. This is the smallest reasonably safe option for a replica set.

As per graylog documentation here We recommend to  deploy a MongoDB replica set.

MongoDB doesn’t have to run on dedicated servers for the workload generated by Graylog, but you should follow the recommendations given in the MongoDB documentation about architecture. Most important is that you have an odd number of MongoDB servers in the replica set.

In most setups, each Graylog server will also host an instance of MongoDB which is part of the same replica set and shares the data with all other nodes in the cluster.

As MongoDB Documentation here A secondary maintains a copy of the primary’s data set. To replicate data, a secondary applies operations from the primary’s oplog to its own data set in an asynchronous process. A replica set can have one or more secondaries.

The clients cannot write data to secondaries, clients can read data from secondary members

For your further ref here, here


Oracle 12c - Slow query to tables ALL_CONS_COLUMNS and ALL_CONSTRAINTS We have successfully upgraded Oracle 11g to Oracle 12c. So far it's been going well. But We noticed one problem, queries made for Oracle's constraint tables (ALL_CONS_COLUMNS and ALL_CONSTRAINTS) are really slow compared to 11g.

When We used 11g, there was no problem at all. The query finished in less than one second but in 12c it is at least 4 times slower. It is affecting all applications We have and even though there's a workaround (application side), ideally, You'd expect the same performance between 11g and 12c.

We have also tried to measure the query cost using Toad. On 12c, the cost is much smaller than 11g. Could this be affecting the query time? Is there anything I can try to solve this problem?

PS : Any other queries made for another tables are okay. Performance wise, there's little to no difference between 11g and 12c.

Any feedback is welcomed. Thank you very much.

Sounds like statistics on those tables are out of date.  I would invoke the following statements:

purge dba_recyclebin;
exec dbms_stats.gather_dictionary_stats
exec dbms_stats.gather_fixed_objects_stats;

If your application creates and or drops tables frequently, you may need to put the above statements in a scheduled job.

And as @MarkDPowell suggested, you should also consider gathering system stats via DBMS_STATS.GATHER_SYSTEM_STATS and DBMS_STATS.GATHER_PROCESSING_RATE (new in 12c) -- that is important in 11g and more important in 12c.


What does sp_restore_filelistonly do? In SQL Server 2017, after applying Cumulative Update 7, there's a new system stored procedure in master called sp_restore_filelistonly. What's it for?

sp_restore_filelistonly is a (currently) undocumented stored procedure with two parameters:

@device_type NVARCHAR(10) - like 'DISK'
@backup_path NVARCHAR(360) - which needs to point to the exact backup file you want to example, not just the folder path where it lives

When you point it at a backup file, like this:

EXEC sp_restore_filelistonly
    @device_type = 'DISK',
    @backup_path = 'E:\MSSQL2017\Backup\StackOverflow2010.bak';

It functions like RESTORE FILELISTONLY:

RESTORE FILELISTONLY FROM DISK = 'E:\MSSQL2017\Backup\StackOverflow2010.bak' WITH FILE = 1;

You get an output set with the files included in the backup:

LogicalName
PhysicalName
Type (D for full, L for log, etc)
FileGroupName
Size (in bytes)
MaxSize
FileId
CreateLSN
DropLSN
UniqueID
ReadOnlyLSN
ReadWriteLSN
BackupSizeInBytes - note that this is the uncompressed size, so if your backup used compression, you won't see the real file size here
SourceBlockSize
FileGroupId
LogGroupGUID
DifferentialBaseLSN
DifferentialBaseGUID
IsReadOnly
IsPresent
TDEThumbprint
SnapshotUrl

Why would Microsoft build it? Well, if they want to let someone get a file list without running a RESTORE command. Could be a separation-of-duties thing, or could be making the automation plumbing easier for Azure Managed Instances.


MySQL lower_case_table_names - Tables vs Views I've set lower_case_table_names=2 in my.ini.

When I create tables, the case is respected. However, when creating views, everything is changed to lower case.

Also, when I generate my scripts using dbForge, the tables keep their case and the views are in lower case.

Is this normal/expected? How can this be fixed?

Update #1

Tried generating scripts for my functions and there too the case is respected.

Update #2

Creating the following table: TableCamelCase, will show up in both dbForge and Workbench respecting the case specified.

Creating the following view: ViewCamelCase, will show up in lower case in both dbForge and Workbench.

The generate scripts from dbForge will respect the case for the table and match the lower case of the view.

I guess this is correct behaviour, don't you think?

0 : Table names are stored as specified and comparisons are case sensitive.
1 : Table names are stored in lowercase on disk and comparisons are not case sensitive.
2 : Table names are stored as given but compared in lowercase.

Last line explains the case.
Referred: Dealing with MySQL case-sensitivity
Update: For views and case-sensitivity, refer this bug report.


How can I create a trigger that prevents column delete, data deletion and table truncate in the same time? Having written this trigger create:

CREATE OR REPLACE TRIGGER PROTECT_DB BEFORE DROP OR TRUNCATE ON X.SCHEMA

How can I "add" a DELETE in the trigger definition ? (the DELETE should prevent data deleting from tables and column erasing).

You can't. Look at the docs. In the syntax diagrams note, that there is no way from the ddl_event/OR loop to get to dml_event_clause.

And it wouldn't make any sense either. On what table should that trigger fire? DDL trigger don't have tables as targets.

Use two separate triggers.


Conversion from varchar2 to timestamp In a oracle table column, there is a varchar2 column which contains value '2017-02-14T18:04:34.988+00:00' I need to convert it to timestamp. I am using TO_TIMESTAMP() to convert but I am not able to find out what date mask I need to provide. I have tried with

sql> select to_timestamp('2017-02-14T18:04:34.988+00:00','YYYY-MM-DD HH24:MI:SS.FF') from dual ;

But it does not work.

Kindly suggest what datetime mask I have to specify as 2nd argument.

According to ISO 8601 T is the time designator.
You need to use a constant "T" in your date format mask.

And your time stamp contains timezone so you need to use TO_TIMESTAMP_TZ function to achieve this.

SQL> select to_timestamp_tz('2017-02-14T18:04:34.988+00:00',' YYYY-MM-DD"T"HH24:MI:SS.FF TZH:TZM') from dual;

TO_TIMESTAMP_TZ('2017-02-14T18:04:34.988+00:00','YYYY-MM-DD"T"HH24:MI:SS.FF
---------------------------------------------------------------------------
14-FEB-17 06.04.34.988000000 PM +00:00

Documentation:TO_TIMESTAMP_TZ


Adding a geographically dispersed third node to an AAG cluster We have a 2 node Always setup in our data center at DC and I have been tasked to setup a third node in this cluster in San Jose. What are the considerations I need to take to make this happen? I read the below given document which provides some insights. Is this document sufficient to consider as a reference?

http://download.microsoft.com/download/D/2/0/D20E1C5F-72EA-4505-9F26-FEF9550EFD44/SQLServer2012_MultisiteFailoverCluster%20(2).docx

My major questions would be where do I place the quorum (probably a disk and file share witness) . And if Asynchronous commit is the only option available because of the geographical distance is it viable to just a have a manual failover option?

My major questions would be where do I place the quorum (probably a disk and file share witness)

Quorum is a majority agreement, it is not a resource. It used to be a resource until 2003 where the only option was a disk, but this hasn't been the case since Server 2008.

There can only be a single witness, you can't have both a disk and a fileshare. The choice of which to have is going to be based on your favorable scenario during a failure and your internal implementation limits. For example, a company I work with has said absolutely no shared disk to any server, so that rules out a disk as a potential witness.

Most organizations want the whatever is considered the optimal  side for their business (though, proper planning and implementation there wouldn't be an "optimal" side) to stay running. In most cases we call this the "primary" side. Generally speaking the witness should be closest to the location that should stay up in case of a true split situation when using windows server 2012R2 or lower. If you're using Windows Server 2016 you will want to look into sites.

  And if Asynchronous commit is the only option available because of the geographical distance is it viable to just a have a manual failover option?

Again, depends. Will this ever be failed to? Maybe just in the instance of a true regional DR event where RTO trumps RPO? Will there be any reporting on this new replica? If so, does the reporting require a specific data freshness SLA?

Synchronous will obviously have an overhead and your round trip time (rtt) latency will play the largest factor for long distance AGs, followed next by disk. Asynchronous will obviously not have those problems in terms of reducing the throughput of the workload but will still feel it in terms of data freshness if it is used for reporting.

Remember, if it is a planned downtime you can change the availability mode before performing any activities.


Find and Replace performance issue Table 1: linked native access table
Table 2: linked pgsql table

Both tables have same records but searching for a record using built-in Find and Replace dialog box in selected field is a lot lot slower on Table 2 (linked) with 30K+ records.

Q: Is there any settings or tricks that can be done to improve performance using native Find and Replace functionality? Thanks.

Unfortunately, no. The basic Find (Ctrl+F) and Replace (Ctrl+H) functions in the Access UI search the resultset of the form row-by-row.

For an ODBC linked table (any ODBC linked table, not just PostgreSQL) once Access has gone past the end of the rows that it pre-fetched when it displayed the form it retrieves one row at a time until it finds a match or reaches the end.

(Find and Replace is often considerably faster for Access linked tables - as opposed to ODBC linked tables - because the Access Database Engine works directly with the database file, retrieving 4KB pages of data at a time. If consecutive rows happen to be on the same page, or if the 4KB page for a given row has recently been cached, then Access can "get" that row without another round-trip to the database file.)

However, there is one workaround that may be helpful: If a Filter has been applied to a Form or Datasheet view then Find and Replace operations are restricted to the filtered rows. Access can take advantage of indexes and (at least some) server-side processing when applying a Filter to a form bound to an ODBC linked table, so you might be able to get the job done faster by Filtering the records first (even a non-sargable filter such as LIKE "*foo*"), and then performing the Find and Replace on the filtered set.


ORA-16000 when trying to perform select on read only Oracle database My application's SQL encounters an ORA-16000 error when trying to access a read only Oracle database.

ORA-16000: database open for read-only access ORA-06512: at "SYS.XMLTYPE",
line 272 ORA-06512: at line 1 ### The error may exist in com/xyz/report/dao/Transaction001Mapper.xml

This is the query that involves the XMLTYPE, the INTERFACE_CONTENT is a CLOB column:

SELECT CONTENTS FROM ERRCODES WHERE
       CODE=(SELECT xmltype(INTERFACE_CONTENT).extract('/Message/PaymentStatus/ps:FIToFIPmtStsRpt/ps:TxInfAndSts/ps:StsRsnInf/ps:Rsn/ps:Prtry/text()','xmlns="urn:abcde" xmlns:head="urn:iso:std:iso:20022:tech:xsd:head.001.001.01" xmlns:ps="urn:iso:std:iso:20022:tech:xsd:pacs.002.001.03"').getstringval() APP_CODE
FROM MESSAGE_EXTERNAL_INTERACTION MEI WHERE MEI.MSG_TYPE='Pacs_002'
    AND MEI.MID='MY_ID')

I also did a lot of EXTRACTVALUE() method on an XML field type.

The SQL is working perfectly if the database is not read only (read write).

What is the issue here? Is this related to some missing privileges/grants?

I am not aware of any specific privileges related to XMLType. There will be execute privs related to PL/SQL that works with it but your message is clear and sadly definitive. If you had a privs issue you wouldn't be getting this message...

XMLType can be funny to work with. Have you thought about retrieving it as a CLOB and then doing the XML parsing and extraction at the client end?


How do I sync instance A to instance B without losing any of the data already on instance B? Like I said in a comment below, I'm no DBA and I'm still in a learning process.

Here's my scenario :

I have an Oracle server running a couple schemas (let' call it Source). What I would like to do is send a 1:1 copy of every objects to another Oracle server (let's call it Target) where there's already some objects that I need to preserve.

Ideally, I don't want to shutdown the Source server.

Also, I need to keep the already existing schemas of the Target server. So the new objects from Source need to co-exist on the Target server with the already existing objects. Maybe by using 2 instances that could be accomplish? I don't know, I'm just suggesting.

What could be the solutions to achieve that?

Golden Gate is answer to these types of problem. Oracle bought it. Therefore it is a Oracle Golden Gate right now.

Look below image which is taken from Oracle Golden Gate Admin Guide
 http://download.oracle.com/docs/cd/E18101_01/doc.1111/e17341.pdf

Also you can look to following article:

http://www.dbasupport.com/oracle/ora11g/Oracle-Replication-Streams-vs-GoldenGate.shtml

Thanks Tevo D for pointing out licensing issue. Actually golden gate may be a expensive solution depending on your circumstances, i.e your company's standing with Oracle, what is your cost of downtime etc.


SQL group by multiple columns Say I have the following table, where a group of 3 people from 3 locations work together on a project that is scored.

|   | Score | Possible_Score | Loc_A | Loc_B | Loc_C |
|---|-------|----------------|-------|-------|-------|
| 1 | 80    | 100            | LA    | LA    | Bos   |
| 2 | 90    | 100            | LA    | Bos   | NYC   |
| 3 | 70    | 100            | NYC   | NYC   | LA    |

I want to get overall scores by location, where each project that has at least 1 person from that location contributes to the location's score. So, in this case, I'd want an output like:

|   | Location | Score | Poss_Score |
|---|----------|-------|------------|
| 1 | Bos      | 170   | 200        |
| 2 | LA       | 240   | 300        |
| 3 | NYC      | 160   | 200        |

How can I do this in SQL? I know how to group by location, but I don't know how to group by location when it can be from any of 3 columns. Thank you for the help!

A solution that work in most DBMS is to use a union:

 select loc_a as location, Score, Possible_Score from T
 union all
 select loc_b as location, Score, Possible_Score from T
 union all
 select loc_c as location, Score, Possible_Score from T

Now you can sum that up as:

select location, sum(score), sum(possible_score)
from (
     select loc_a as location, Score, Possible_Score from T
     union all
     select loc_b as location, Score, Possible_Score from T
     union all
     select loc_c as location, Score, Possible_Score from T
) as TT
group by location;

If your DBMS supports LATERAL JOIN you can do something like:

select x.location, sum(x.score), sum(x.possible_score)
from T
cross join lateral (
    values (T.loc_a, T.score, T.possible_score)
         , (T.loc_b, T.score, T.possible_score)
         , (T.loc_c, T.score, T.possible_score)
) X (location, score, possible_score)
group by x.location;

LATERAL makes it possible to reference T in X, so we can use X to normalize location.


Do I need a Primary Key if it's already an Index I have a bunch of tables with no Primary Key. Some do already have a non clustered index comprised of what would be the Primary Key (ClaimID, LineNBR). The columns used are currently set to accept NULLs so i would need to alter that. Is there value in creating a Primary Key for this and would that make having the non clustered index on the same columns superfluous?
Is it best to leave them with just the non clustered index, delete the non clustered index and make it a Primary Key, or add the Primary Key AND keep the non clustered index?

Table usage patterns are the main driver of if you need them or not. In OLTP workloads, you usually do want them. If you're just dumping data into these tables and reporting off them in big ol' scans, they're likely less valuable.

And while you may not need a Primary Key at all, adding a clustered index can help you avoid problems that arise when tables are Heaps (tables without clustered indexes).

Again, usage patterns will matter most: if you never update or delete data stored in heaps, you won't hit these specific problems:

Forwarded Fetches

How To Fix Forwarded Records
Identify and Correct SQL Server Forwarded Records
How Forwarded Records are Read and Processed in a SQL Server Heap
Forwarding and forwarded records, and the back-pointer size

Captive Pages

DELETE on a heap table
Heaps, Deletes, and Optimistic Isolation Levels
SQL Server: Inside a DELETE operation in Heaps
SQLskills SQL101: Why does my heap have a bunch of empty pages?


I need to run VACUUM FULL with no available disk space I have one table that is taking up close to 90% of hd space on our server.  I have decided to drop a few columns to free up space.  But I need to return the space to the OS.  The problem, though, is that I'm not sure what will happen if I run VACUUM FULL and there is not enough free space to make a copy of the table.

I understand that VACUUM FULL should not be used but I figured it was the best option in this scenario.

Any ideas would be appreciated.

I'm using PostgreSQL 9.0.6

Since you don't have enough space to run a vacumm or rebuild, you can always rebuild your postgresql databases by restoring them.  Restoring the databases, tables, indexes will free up space and defragment.  Afterwards, you can setup automated maintenance to vacumm your databases on a regular basis.

1 Backup all of the databases on your postgresql server

You will want to backup all of your databases to a partition that has enough space. If you were on Linux, you can use gzip to further compress the backup to save space

su - postgres
pg_dumpall | gzip -9 > /some/partition/all.dbs.out.gz

2 Backup your configuration files

cp /path/to/postgresql/data_directory/*.conf /some/partition/

3 Stop Postgresql

pg_ctl -D /path/to/postgresql/data_directory stop

4 erase the contents of the data directory

rm -Rf /path/to/postgresql/data_directory/*

5 Run initdb to reinitalize your data directory

initdb -D /path/to/postgresql/data_directory

6 Restore configuration files

cp /some/partition/*.conf /path/to/postgresql/data_directory/*.conf

7 Start Postgresql

pg_ctl -D /path/to/postgresql/data_directory start

8 Restore the dump of all the databases you made

gunzip /some/partition/all.dbs.out.gz
psql -f /some/partition/all.dbs.out


Insert-heavy InnoDB table won't use all my CPU I have a packet log database, which is almost never queried. It just needs to be fast on inserts. I'm using InnoDB because I'd like to maintain ACID compliance, since even losing a single packet could be damaging to our customers.  In a performance tuning scenario, I send 1,000,000 packets to the server over multiple DB connections. But no matter what settings I use in my.cnf, I can't get the mysqld process to use more than 900% CPU on a system with 12 cores. (Nothing else is running on the box.)

I've set the following

innodb_file_per_table = 1
innodb_write_io_threads = 64
innodb_read_io_threads = 64
innodb_thread_concurrency = 0

If I use MyISAM, I can get all the packets written in about 6 seconds. But InnoDB takes about 25. Can I make MySQL use the rest of the system resources and insert faster?

Edit: Here's the schema for the table:

+-------+----------------------+------+-----+---------+-------+
| Field | Type                 | Null | Key | Default | Extra |
+-------+----------------------+------+-----+---------+-------+
| t     | bigint(20) unsigned  | YES  |     | NULL    |       |
| a     | char(1)              | YES  |     | NULL    |       |
| sa    | int(10) unsigned     | YES  |     | NULL    |       |
| sb    | int(10) unsigned     | YES  |     | NULL    |       |
| sc    | int(10) unsigned     | YES  |     | NULL    |       |
| sd    | int(10) unsigned     | YES  |     | NULL    |       |
| sp    | smallint(5) unsigned | YES  |     | NULL    |       |
| da    | int(10) unsigned     | YES  |     | NULL    |       |
| db    | int(10) unsigned     | YES  |     | NULL    |       |
| dc    | int(10) unsigned     | YES  |     | NULL    |       |
| dd    | int(10) unsigned     | YES  |     | NULL    |       |
| dp    | smallint(5) unsigned | YES  |     | NULL    |       |
+-------+----------------------+------+-----+---------+-------+

edit2:
I've batched more inserts together so that a single query is near the maximum length (about 16,000,000 chars). The database now spikes to 1100% for two seconds, then goes down to 100% for the rest of the time. Total time is now 21 seconds, or about 16% faster than when I started.

You have to crank up innodb_io_capacity as well.

The default is 200. Raise it to 5000 for starters. I would go to 20000.

You may also want to make sure ib_logfile0 and ib_logfile1 are sufficiently large. The default value for innodb_log_file_size is 5M. I would raise that to 1G for starters.

A larger InnoDB Buffer Pool would also help, perhaps 4G.

To recap, use these additional settings:

[mysqld]
innodb_io_capacity=5000
innodb_buffer_pool_size=4G
innodb_log_file_size=1G

After adding these settings to my.cnf, to resize ib_logfile0/ib_logfile1 do the following

service mysql stop
rm -f /var/log/mysql/ib_logfile[01]
service mysql start

The files ib_logfile0 and ib_logfile1 are recreated. Don't worry, I have done this many times.

You may have to do something out of the ordinary for InnoDB

Try the following:

Full Table Lock on the InnoDB table
Perform the Bulk Load
Release the Lock


Upgrade SQL Server 2008 Express to SQL Server Workgroup 2008 Volume License I'm in need of some help with SQL Server. I’m fairly new to SQL Server so bear with me.

My situation:

We currently have SQL Server 2008 Express installed and running on Windows Server 2003. We are reaching the max size for SQL Server 2008 Express, so we purchased SQL Server Workgroup 2008 volume license version. Now because the volume license product key is built in, I can’t do a standalone upgrade from the SQL Server 2008 Express version.

How do I go about installing the new workgroup version? Do I uninstall the express version and install the workgroup version? Do I install the workgroup side by side of the express? How do I transfer the data over from the express version to the workgroup?

As you can see, I need help on how to tackle this project.  Please! Any help would be well appreciated

You can use the copy database wizard to move databases from one database instance to another.

Right-click on an existing database, choose TASKS and then at very bottom, is 'Copy Database'.

I would recommend the detach / re-attach database option, I don't like the other method, it's not reliable enough.

You'll probably want to test a couple of database detach / attach operations.

Detach takes the database offline, and makes it portable so you can change the volume, before re-attaching the database in a new location.


Oracle result cache behaviour Oracle by default caching the query results and function results(Buffer cache).

I have noticed this with AutoTrace utility, where the physical reads are huge on first execution, but from next execution onward it reduced dramatically.

Then what is the importance of query result cache, function result cache?

I have also noticed that the buffer cache does not gets invalidated even when I try to update physical table. But still I noticed physical reads are 0.

Could some one help on this to understand better?

Oracle by default does not cache query and function result, but both cache do exist.

http://www.oracle.com/technetwork/articles/sql/11g-caching-pooling-088320.html

The importance of these: there is more than caching blocks. Even if you process everything in memory, why process the same requests again and again, if you already know the answer?

What you are referring to when mentioning autotrace and physical reads, is the buffer cache.

  "I have also noticed that the buffer cache does not gets invalidated
  even when I try to restart the server."

This is a false conclusion. When you restart the database, the buffer cache will be empty again. Post your test that confirms the opposite.


Delete from table where not in another table using two columns I am trying to get my head around this delete statement I am trying to accomplish.  I understand how to delete from table where column not in ( subquery to table2)

I have two tables with multiple columns, and each table has two columns making up the primary keys. I think you have to do a join maybe?  I would like something in the lines of the following:

Delete From tbl1 left join tbl2
on tbl1.PK1 = tbl2.PK1 and tbl1.PK2 = tbl2.PK2
where tbl1_PK1 Not IN(
Select Distinct tbl2.PK1, tbl2.PK2 where DateStr >= GetDate()-365)

I am trying remove rows from tbl1.  Does this look close to correct?

Edit to add:

tbl1
PK1, PK2, Col1

tbl2
PK1,PK2, DateStr

Delete From tbl1 left join tbl2
on tbl1.PK1 = tbl2.PK1 and tbl1.PK2 = tbl2.PK2
where tbl1.PK1,tbl1.PK2 Not IN(
Select Distinct tbl2.PK1, tbl2.PK2 where DateStr >= GetDate()-365)

What is left after running sqlfiddle should be:

tbl1
('T', '222','asdf')

tbl2
('T', '222','9/26/2014')

I suspect you're after something like this:

DELETE FROM tbl1
FROM tbl1
    LEFT OUTER JOIN tbl2
        ON tbl1.PK1 = tbl2.PK1
        AND tbl1.PK2 = tbl2.PK2
        AND tbl2.DateStr >= GETDATE() - 365
WHERE tbl2.PK1 IS NULL
    AND tbl2.PK2 IS NULL

Yes, you have two FROM clauses with a multi-table DELETE statement. This is essentially just an anti-join turned into a deletion.


Why is the secondary selective index not used when the where clause filters on `value()`? Setup:

create table dbo.T
(
  ID int identity primary key,
  XMLDoc xml not null
);

insert into dbo.T(XMLDoc)
select (
       select N.Number
       for xml path(''), type
       )
from (
     select top(10000) row_number() over(order by (select null)) as Number
     from sys.columns as c1, sys.columns as c2
     ) as N;

Sample XML for each row:

<Number>314</Number>

The job for the query is to count the number of rows in T with a specified value of <Number>.

There are two obvious ways to do this:

select count(*)
from dbo.T as T
where T.XMLDoc.value('/Number[1]', 'int') = 314;

select count(*)
from dbo.T as T
where T.XMLDoc.exist('/Number[. eq 314]') = 1;

It turns out that value() and exists() requires two different path definitions for the selective XML index to work.

create selective xml index SIX_T on dbo.T(XMLDoc) for
(
  pathSQL = '/Number' as sql int singleton,
  pathXQUERY = '/Number' as xquery 'xs:double' singleton
);

The sql version is for value() and the xquery version is for exist().

You might think that an index like that would give you a plan with a nice seek but selective XML indexes are implemented as a system table with the primary key of T as the lead key of the clustered key of the system table. The paths specified are sparse columns in that table. If you want an index of the actual values of the defined paths you need to create a secondary selective indexes, one for each path expression.

create xml index SIX_T_pathSQL on dbo.T(XMLDoc)
  using xml index SIX_T for (pathSQL);

create xml index SIX_T_pathXQUERY on dbo.T(XMLDoc)
  using xml index SIX_T for (pathXQUERY);

The query plan for the exist() does a seek in the secondary XML index followed by a key lookup in the system table for the selective XML index (don't know why that is needed) and finaly it does a lookup in T to make sure there actually are rows in there. The last part is necessary because there is no foreign key constraint between the system table and T.

The plan for the value() query is not so nice. It does a clustered index scan of T with a nested loops join against a seek on the internal table to get the value from the sparse column and finally filters on the value.

If a selective index should be used or not is decided before optimization but if a secondary selective index should be used or not is a cost based decision by the optimizer.

Why is the secondary selective index not used when the where clause filters onvalue()?

Update:

The queries are semantically different. If you add a row with the value

<Number>313</Number>
<Number>314</Number>`

the exist() version would count 2 rows and the values() query would count 1 row. But with the index definitions as they are specified here using the singleton directive SQL Server will prevent you from adding a row with multiple <Number> elements.

That does however not let us use the values() function without specifying [1] to guarantee the compiler that we will only get a single value. That [1] is the reason we have a Top N Sort in the value() plan.

Looks like I am closing in on an answer here...

The declaration of singleton in the path expression of the index enforces that you can not add multiple <Number> elements but the XQuery compiler does not take that into consideration when interpreting the expression in the value() function. You have to specify [1] to make SQL Server happy. Using typed XML with a schema does not help with that either. And because of that SQL Server builds a query that uses something that could be called an "apply" pattern.

Easiest to demonstrate is to use regular tables instead of XML simulating the query we are actually executing against T and the internal table.

Here is the setup for the internal table as a real table.

create table dbo.xml_sxi_table
(
  pk1 int not null,
  row_id int,
  path_1_id varbinary(900),
  pathSQL_1_sql_value int,
  pathXQUERY_2_value float
);

go

create clustered index SIX_T on xml_sxi_table(pk1, row_id);
create nonclustered index SIX_pathSQL on xml_sxi_table(pathSQL_1_sql_value) where path_1_id is not null;
create nonclustered index SIX_T_pathXQUERY on xml_sxi_table(pathXQUERY_2_value) where path_1_id is not null;

go

insert into dbo.xml_sxi_table(pk1, row_id, path_1_id, pathSQL_1_sql_value, pathXQUERY_2_value)
select T.ID, 1, T.ID, T.ID, T.ID
from dbo.T;

With both tables in place you can execute the equivalent of  the exist() query.

select count(*)
from dbo.T
where exists (
             select *
             from dbo.xml_sxi_table as S
             where S.pk1 = T.ID and
                   S.pathXQUERY_2_value = 314 and
                   S.path_1_id is not null
             );

The equivalent of the value() query would look like this.

select count(*)
from dbo.T
where (
      select top(1) S.pathSQL_1_sql_value
      from dbo.xml_sxi_table as S
      where S.pk1 = T.ID and
            S.path_1_id is not null
      order by S.path_1_id
      ) = 314;

The top(1) and order by S.path_1_id is the culprit and it is [1] in the Xpath expression that is to blame.

I don't think it is possible for Microsoft to fix this with the current structure of the internal table even if you were allowed to leave out the [1] from the values() function. They would probably have to create multiple internal tables for each path expression with unique constraints in place to guarantee for the optimizer that there can only be one <number> element for each row. Not sure that would actually be enough for the optimizer to "break out of the apply pattern".

For you who think this fun and interesting and since you are still reading this you probably are.

Some queries to look at the structure of the internal table.

select T.name,
       T.internal_type_desc,
       object_name(T.parent_id) as parent_table_name
from sys.internal_tables as T
where T.parent_id = object_id('T');

select C.name as column_name,
       C.column_id,
       T.name as type_name,
       C.max_length,
       C.is_sparse,
       C.is_nullable
from sys.columns as C
  inner join sys.types as T
    on C.user_type_id = T.user_type_id
where C.object_id in (
                     select T.object_id
                     from sys.internal_tables as T
                     where T.parent_id = object_id('T')
                     )
order by C.column_id;

select I.name as index_name,
       I.type_desc,
       I.is_unique,
       I.filter_definition,
       IC.key_ordinal,
       C.name as column_name,
       C.column_id,
       T.name as type_name,
       C.max_length,
       I.is_unique,
       I.is_unique_constraint
from sys.indexes as I
  inner join sys.index_columns as IC
    on I.object_id = IC.object_id and
       I.index_id = IC.index_id
  inner join sys.columns as C
    on IC.column_id = C.column_id and
       IC.object_id = C.object_id
  inner join sys.types as T
    on C.user_type_id = T.user_type_id
where I.object_id in (
                     select T.object_id
                     from sys.internal_tables as T
                     where T.parent_id = object_id('T')
                     );


Purpose of Try/Catch without Transactions I have come across the following code (SQL Server 2016, 2008-compatible database):

CREATE PROCEDURE myProc
AS BEGIN
    DECLARE @ERRORMESSAGE As VARCHAR(1000)

    BEGIN TRY
        DELETE FROM Foo -- (statement #1)
        DELETE FROM Bar -- (statement #2)
    END TRY
    BEGIN CATCH
        SET @ERRORMESSAGE = ERROR_MESSAGE()
        RaisError(@ERRORMESSAGE,16,1)
    END CATCH
END

What is the purpose of the try/catch block?

For the sake of discussion, lets assume there are no triggers called and the client code that executes this procedure does not wrap the call in a transaction.

I had to fix a bug in code similar to this where statement #2 raised an error. Looking at the data it appeared statement #1 completed successfully and statement #2 did not apply (obviously).

One thing this does is not give a clear line number when trying to debug the crash, but is there anything legitimate this does?

IMHO Using a TRY/CATCH block you don't need to check every single operation.

DELETE FROM Foo;
IF @@ERROR <> 0
BEGIN
    RAISERROR('Error deleting table Foo', 16, 1);
    RETURN -1;
END

OR

DELETE FROM Bar;
IF @@ERROR <> 0
BEGIN
    SET @ErrMsg = 'Error';
    GOTO ERROR;
END


How to debug a db memory-leak causing mysql to go before it's own limits? We are having a problem with one of the database server of one application, possibly caused by some code that is creating a problem in the way Mysql manage it's memory.

Until the second week of April, our db server has a stable consumption of memory of about 5 gigs (with a maximum of 7 gigs). But then, it started to increase limitless, even surpassing it's theorically maximum possible allocation.

This is our yearly munin graph showing the increase in the last 2 months:

(source: postimg.org)

This is another view from the last seven days after a restart in mysql:

(source: postimg.org)

This is the report created by mysqltuner.pl:

-------- Performance Metrics -------------------------------------------------

[--] Up for: 4d 1h 56m 28s (152M q [431.585 qps], 383K conn, TX: 593B, RX: 29B)
[--] Reads / Writes: 90% / 10%
[--] Total buffers: 5.3G global + 10.2M per thread (200 max threads)

[OK] Maximum possible memory usage: 7.3G (46% of installed RAM)
[OK] Slow queries: 0% (2K/152M)
[OK] Highest usage of available connections: 13% (26/200)
[OK] Key buffer size / total MyISAM indexes: 16.0M/300.0K
[OK] Key buffer hit rate: 100.0% (61M cached / 9 reads)
[OK] Query cache efficiency: 70.8% (103M cached / 146M selects)
[!!] Query cache prunes per day: 501819
[OK] Sorts requiring temporary tables: 0% (926 temp sorts / 3M sorts)
[!!] Joins performed without indexes: 39128
[OK] Temporary tables created on disk: 16% (821K on disk / 5M total)
[OK] Thread cache hit rate: 99% (26 created / 383K connections)
[!!] Table cache hit rate: 10% (845 open / 7K opened)
[OK] Open file limit used: 3% (148/4K)
[OK] Table locks acquired immediately: 99% (65M immediate / 65M locks)
[!!] InnoDB data size / buffer pool: 5.5G/5.0G

We are in unknown territory here. Any help will be appreciated!

Edit: Adding my.cnf

# The MySQL database server configuration file.

[client]
port            = 3306
socket          = /var/run/mysqld/mysqld.sock

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

[mysqld]
character_set_server = utf8
collation_server = utf8_general_ci

user            = mysql
socket          = /var/run/mysqld/mysqld.sock
pid-file        = /var/run/mysqld/mysqld.pid
port            = 3306
basedir         = /usr
datadir         = /var/lib/mysql
tmpdir          = /tmp
skip-external-locking
bind-address            = 0.0.0.0

# Fine Tuning
max_connections         = 200
key_buffer              = 16M
max_allowed_packet      = 16M
thread_stack            = 192K
join_buffer_size        = 2M
sort_buffer_size        = 2M
read_buffer_size        = 2M
read_rnd_buffer_size    = 4M
thread_cache_size       = 128
thread_concurrency      = 24
table_cache             = 2K
table_open_cache        = 2K
table_definition_cache  = 4K

# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover         = BACKUP

# innodb
innodb_buffer_pool_size         = 5G
innodb_flush_log_at_trx_commit  = 1
innodb_support_xa               = 1
innodb_additional_mem_pool_size = 32M
innodb_log_buffer_size          = 8M
innodb_flush_method             = O_DIRECT

# Query Cache Configuration
query_cache_limit               = 32M
query_cache_size                = 256M
query_cache_min_res_unit        = 256

# Logging and Replication
log_error                       = /var/log/mysql/error.log
log-slow-queries                = /var/log/mysql/slow.log
long_query_time                 = 1

# REPLICATION CONFIGURATION
log_bin                 = /var/log/mysql/mysql-bin.log
log-bin                 = mysql-bin
expire_logs_days        = 15
sync_binlog             = 1
server-id               = 1

ssl-ca   =/etc/ssl/private/repl/cacert.pem
ssl-cert =/etc/ssl/private/repl/master-cert.pem
ssl-key  =/etc/ssl/private/repl/master-key.pem

[mysqldump]
quick
quote-names
max_allowed_packet      = 16M

[isamchk]
key_buffer              = 16M

...even surpassing it's theorically maximum possible allocation.

[OK] Maximum possible memory usage: 7.3G (46% of installed RAM)

There is not actually a way to calculate maximum possible memory usage for MySQL, because there is no cap on the memory it can request from the system.

The calculation done by mysqltuner.pl is only an estimate, based on a formula that doesn't take into account all possible variables, because if all possible variables were taken into account, the answer would always be "infinite."  It's unfortunate that it's labeled this way.

Here is my theory on what's contributing to your excessive memory usage:

thread_cache_size       = 128

Given that your max_connections is set to 200, the value of 128 for thread_cache_size seems far too high.  Here's what makes me think this might be contributing to your problem:

  When a thread is no longer needed, the memory allocated to it is released and returned to the system unless the thread goes back into the thread cache. In that case, the memory remains allocated.

  http://dev.mysql.com/doc/refman/5.6/en/memory-use.html

If your workload causes even an occasional client thread to require a large amount of memory, those threads may be holding onto that memory, then going back to the pool and sitting around, continuing to hold on to memory they don't technically "need" any more, on the premise that holding on to the memory is less costly than releasing it if you're likely to need it again.

I think it's worth a try to do the following, after first making a note of how much memory MySQL is using at the moment.

Note how many threads are currently cached:

mysql> show status like 'Threads_cached';
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| Threads_cached | 9     |
+----------------+-------+
1 row in set (0.00 sec)

Next, disable the thread cache.

mysql> SET GLOBAL thread_cache_size = 0;

This disables the thread cache, but the cached threads will stay in the pool until they're used one more time.  Disconnect from the server, then reconnect and repeat.

mysql> show status like 'Threads_cached';

Continue disconnecting, reconnecting, and checking until the counter reaches 0.

Then, see how much memory MySQL is holding.

You may see a decrease, possibly significant, and then again you may not.  I tested this on one of my systems, which had 9 threads in the cache.  Once those threads had all been cleared out of the cache, the total memory held by MySQL did decrease... not by much, but it does illustrate that threads in the cache do release at least some memory when they are destroyed.

If you see a significant decrease, you may have found your problem.  If you don't, then there's one more thing that needs to happen, and how quickly it can happen depends on your environment.

If the theory holds that the other threads -- the ones currently servicing active client connections -- have significant memory allocated to them, either because of recent work in their current client session or because of work requiring a lot of memory that was done by another connection prior to them languishing in the pool, then you won't see all of the potential reduction in memory consumption until those threads are allowed to die and be destroyed.  Presumably your application doesn't hold them forever, but how long it will take to know for sure whether there's a difference will depend on whether you have the option of cycling your application (dropping and reconnecting the client threads) or if you'll have to just wait for them to be dropped and reconnected over time on their own.

But... it seems like a worthwhile test.  You should not see a substantial performance penalty by setting thread_cache_size to 0.  Fortunately, thread_cache_size is a dynamic variable, so you can freely change it with the server running.


SQL Server Space Reclaim We have a sizeable SQL Server 2016 database (running on Windows 2016 Datacenter). A table was growing exponentially due to a bad SQL process.  The bad SQL process has been resolved, but the specific table is still large, and the database instance grabbed a lot of space on the server.  That is, the database instance's physical size is very large.

We need to reduce the physical size and "shrink database" is not recommended by many because of the fragmentation it causes.  To process this while keeping everything on the same server, is the best thing to:

Deal with specific table

a) Create a script that will build a new table and copy records from original table.  But not copy excessive records created by bad SQL process.
b) Then delete the original table and rename the new table.
Deal with general database

c) Create a script to clone the database schema and use this to create a new database.
d) Then script the data in chunks from the current database to the new database (because data is extensive).
e) Drop the current database and rename the new one.

However, if space is an issue on the server to achieve this, could I:

f) Script the entire database WITH SCHEMA and DATA
g) Drop the current database
h) Use script to rebuild the entire database

Delete the extra data out of regular business hours, then shrink the database. Don't overcomplicate things.

Andy Mallon explains how in the post How to shrink a database in 4 easy steps:

First you'll get the file sizes for the databases on your instance, using the sys.database_files system view.
Then using DBCC SHRINKFILE, you'll shrink each file in question.
Finally, you'll rebuild all your indexes to ensure any fragmentation is handled.


Which reporting method has better performance, using linked server or using lookup in SSRS? I've been using the new lookup function in Reporting Services 2008 R2 when using different servers where I don't have the option of a linked server. However, the DBA just created the a link to that server and I'm wondering if I can get better performance changing my query/report to use it instead of the lookup function.

I definitely have a lot more flexibility going linked server route but I'm not sure about performance.

If you are really executing the exact same query, then there shouldn't be much of a difference in executing and returning results. I'd expect SQL server with the linked server to have an edge in the actual lookups into the results.

But this won't mean a thing if the queries you are executing against the sources are different. The differences could go either way depending on the specifics of your queries. With the linked server you could be retrieving exactly the data you need from the linked server, so things would probably be faster. But if your query is too general in its join, or retrieves an excessive number of rows from the linked server, that will cost you.

So I vote to go with whichever is more convenient for you. If you need the subquery dataset in SSRS for some other summaries, keep the separate dataset. If you comfortably think in SQL terms, go with the linked server. Then optimize if things are slow.

(It would be easy to make two situations where I could get either answer to be faster, depending on my lookups and queries even when ultimately returning the same results. The query optimizations would easily more than make up for any performance difference between the two methods. For example, with an SSRS lookup, you could use dataset caching, which would avoid waiting for other server entirely.)


Setting model title in MySQL Workbench for Forward Engineering In MySQL Workbench, when viewing a model, you can select File->Export->Forward Engineer SQL Create Script.

The first four lines of the generated file are a comment, something like this:

  -- MySQL Script generated by MySQL Workbench
  -- Mon Dec 17 11:21:48 2018
  -- Model: New Model    Version: 1.0
  -- MySQL Workbench Forward Engineering

How do you change the model title and version?  Is there any way to have an autoincrement on version, especially with semver options autoincrementing the patch number?

I don't see it in any tab of Model->Model Options.

The dialog box with these controls is under File->Document Properties.
There is no way to autoincrement the version number in this dialog.


Standby logshipping fails when user is doing read operations on the database I have a SQLSERVER 2008 instance with a standby configuration using log shipping.

The production server runs a LSBACKUP job every 30 minutes, ships it to the standby server and the standby will then LSRESTORE the shipped log.

The issue I'm having is the LSRESTORE job will fail if any user sessions are accessing the database, even though they don't have active transactions containing any write operations.

The databases are in (Standby / Read-Only) mode so there's no write operations that could be pending on it, simply developing the database tables in SSMS will block the LSRESTORE job with the following error log :

2017-02-14 09:29:22.87  *** Error: Exclusive access could not be obtained because the database is in use.
RESTORE LOG is terminating abnormally.(.Net SqlClient Data Provider) ***

The only solution I have found is when this happens, I can set the database to single_user and back to multi_user to clear the error. I could also put the SQL commands in the SQLAGENT job in a step before the restore but it looks more like a hack than a fix !

ALTER DATABASE [db_name_goes_here] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE [db_name_goes_here] SET MULTI_USER

Can my standby be protected from this issue in any way ?

When you use secondary database for query processing, you have to check

  database users disconnected when transaction log backups are being restored.


tSQL Stored Procedure with CTE; CASE statement in WHERE slowing run I have a user-defined Stored Procedure
When calling without passing explicit values, I want to just pass all Locations (nvarchar(50)), which is the primary key'd field of a table: Monitor_Locations (with ~850 entries)

One part of the SP is defined as follows (clipped).

ALTER PROCEDURE [dbo].[dev_Tech@Locs2b] ( --CREATE or ALTER
     @Locations as nvarchar(MAX) = NULL -- = 'GG1,BenBr14,BenBr00,YB_ToeDrain_Base'
    ,@rangeStart as DateTime = '1970-01-01'
    ,@rangeEnd as DateTime = '2099-12-31'
) AS BEGIN
SET NOCOUNT ON; --otherwise concrete5 chokes for multi-table returns.
DECLARE @loclist as TABLE (
    Location nvarchar(50) PRIMARY KEY
)
IF @Locations is NULL
    INSERT INTO @loclist(Location)
        SELECT Location from Monitor_Locations order by Location
ELSE --irrelevant for this question
    INSERT INTO @loclist(Location)
        SELECT
            ML.Location
        FROM Monitor_Locations as ML join
            tvf_splitstring(@Locations) as ss ON
                ML.Location=ss.Item OR
                ML.Location like ss.Item+'[_]%'
        ORDER BY ML.Location;
With Deploys as (
    SELECT
        D.Location,
        MIN(D.Start) as Start,
        MAX(D.[Stop]) as Stop
    FROM
        Deployments as D
    WHERE
        D.Stop is not NULL
)

...do a bunch of other stuff...

in order to improve the speed of the stored procedure when a restricted list of sites is sent into the SP, I wanted to replacing the WHERE clause with

WHERE
    CASE
        WHEN D.Stop IS NULL THEN 0
        WHEN @Locations IS NULL THEN 1 -- full list, so binding to another list doesn't do us any good.
        WHEN EXISTS (SELECT 1 from (SELECT Location from @loclist as l where l.Location=D.Location) as ll) THEN 1 --ELSE NULL which is not 1
    END=1

but where the SP once took 6-8 seconds to execute, now it takes 2.5 mins (for calling without a restrictive list).  I thought it would take roughly the same amount of time each way for the full list, as the second clause of the CASE should be fired very quickly and the third clause should never be examined.

So what's going on? This code:

WHERE
    CASE
        WHEN D.Stop IS NULL THEN NULL
        WHEN @Locations IS NULL THEN 1 -- full list, so binding to another list doesn't do us any good.
        WHEN EXISTS (SELECT 1 from (SELECT Location from @loclist as l where l.Location=D.Location) as ll) THEN 1 --else null
    END is not null

Takes a ~10 minute run time with this plan:

To contrast here's the WHERE D.Stop is not NULL plan (6s):

At one point, this SP was taking 1 second with this version, but by changing the SP and then back again, it took 6 seconds again. As mentioned in the answers, this was likely due to parameter sniffing.

Run times

My goal time for execution is less than 2 seconds, as this will be a frequently executed SP on a web application that uses this to populate and restrict other user selections.  Basically, I don't want this to be a noticeable bottleneck. Initial run times were on the order of 3 minutes, but after adding or altering some indexes, this dropped to the 6-8 second range.

On Monday (2016-08-29), prior to major alterations
Simple WHERE without input parameters: 5s
Simple WHERE with rangeStart and rangeEnd: 4s
Simple WHERE with @Locations set to a 7 element CSV variable
CASEd WHERE: up to 10 minutes

After re-working the CLR function (see my answer below)
Tuesday (2016-08-30)
Simple or CASEd WHERE without input parameters OR
Simple or CASEd WHERE with rangeStart and rangeEnd: 3s
Simple or CASEd WHERE with 7 element @Locations: 0-1s

After migrating table variable @loclist to temp table #loclist
All tested WHEREs/parameters: 0-1s

Two big performance problems:

Your CSV splitter function is a major performance killer. Swap it out for Jeff Moden's DelimitedSplit8k function. You can read all about it here. Or better yet, swap it out for the CLR function or a table-valued parameter if you are on 2008+. Check out Aaron Bertrand's performance tests for the various CSV splitter functions. CLR is the winner overall.
The table variable can be a big performance killer even if it only has 1 row. Switch it to a temporary table and add a clustered index to it.

In your now provided execution plans, the function shows a 0% cost, but that is not the case. The function cost is higher, but you won't see the actual cost in the execution plan unless it's an inline table valued function.

  Unfortunately, although I once had a runtime of 1 sec, by changing the
  SP and then back again, it takes 6 seconds again.

That smells of parameter sniffing.