Oracle SQL, how to filter on a set by a rank? I am struggling with the following SQL query right now:

http://sqlfiddle.com/#!4/736a7/12

select uc.*
from usercredential uc
inner join users u on u.userid = uc.userid
inner join credential c on c.credentialid = uc.credentialid
inner join healthsystemcredential hsc on hsc.credentialid = c.credentialid
inner join accesslevel ac on hsc.hscredentialid in (
  -- Trying to figure out how to filter here?
  select 1 from dual
)
where c.fulfillmentmethod = 'Attach'
and c.display = 1
and uc.auditstatus <> 0
and ( (uc.auditstatus is null) or (uc.auditstatus = 1 and uc.lastmodified > uc.auditdate) )

Basically I am trying to get UserCredential that are Many To One with Credential.  Credential is One To Many with HealthSystemCredential.

The HealthSystemCredential has a foreign key to AccessLevel, however this AccessLevel column in HealthSystemCredential is actually the Lowest Allowable Access Level.  AccessLevel records have a Type Rank between 1 at the lowest and 4 at the highest.

  Example: A HealthSystemCredential with an AccessLevel record that has a Type Rank of 2 should be fetched when I filter my query by AccessLevels with Types of 2, 3, or 4.

The problem is I can't figure out how to do this in one query.  Is there some way I can get a DENSE_RANK column in a subquery of AccessLevels, apply my filter in the subquery and join it to the table somehow to give me all the applicable HealthSystemCredentials I want?

There are actually several ranking functions you can use. You can read at the Analytic functions at Oracle documentation. In your case, RANK() and DENSE_RANK() would work, if I have understood you:

select *
from
  ( select uc.*,
           DENSE_RANK() OVER (PARTITION BY ac.HEALTHSYSTEMID
                              ORDER BY ac.ACCESSLEVELTYPE ASC)
             AS drnk
    from usercredential uc
      inner join users u
              on u.userid = uc.userid
      inner join credential c
              on c.credentialid = uc.credentialid
      inner join healthsystemcredential hsc
              on hsc.credentialid = c.credentialid
      inner join accesslevel ac
              on hsc.HEALTHSYSTEMID = ac.HEALTHSYSTEMID
    where c.fulfillmentmethod = 'Attach'
    and c.display = 1
    and uc.auditstatus <> 0
    and ( (uc.auditstatus is null)
       or (uc.auditstatus = 1 and uc.lastmodified > uc.auditdate)
        )
  ) tmp
where drnk = 1 ;

The condition on drnk = 1 (lowest Type Rank) will be applied of course after the other conditions in the internal where are applied. If you want to first filter on the lowest Type Rank (this may give you less rows in the final result), you can either group by in a derived table and then join:

select uc.*
from usercredential uc
  inner join users u
          on u.userid = uc.userid
  inner join credential c
          on c.credentialid = uc.credentialid
  inner join healthsystemcredential hsc
          on hsc.credentialid = c.credentialid
  inner join ( SELECT HEALTHSYSTEMID,
                      MIN(ACCESSLEVELTYPE) AS ACCESSLEVELTYPE
               FROM accesslevel
               GROUP BY HEALTHSYSTEMID
             ) ac
          on hsc.HEALTHSYSTEMID = ac.HEALTHSYSTEMID
where c.fulfillmentmethod = 'Attach'
and c.display = 1
and uc.auditstatus <> 0
and ( (uc.auditstatus is null)
   or (uc.auditstatus = 1 and uc.lastmodified > uc.auditdate)
    ) ;

or use a subquery in the ON clause. This is closer to your try but quite complicated. SQL-Server has CROSS APPLY which makes this kind of joins more easy to write:

select uc.*
from usercredential uc
  inner join users u
          on u.userid = uc.userid
  inner join credential c
          on c.credentialid = uc.credentialid
  inner join healthsystemcredential hsc
          on hsc.credentialid = c.credentialid
  inner join accesslevel ac
          on ac.HEALTHSYSTEMID = hsc.HEALTHSYSTEMID
         and ac.ACCESSLEVELTYPE =
             ( SELECT MIN(aci.ACCESSLEVELTYPE) AS ACCESSLEVELTYPE
               FROM accesslevel aci
               WHERE aci.HEALTHSYSTEMID = hsc.HEALTHSYSTEMID
             )
where c.fulfillmentmethod = 'Attach'
and c.display = 1
and uc.auditstatus <> 0
and ( (uc.auditstatus is null)
   or (uc.auditstatus = 1 and uc.lastmodified > uc.auditdate)
    ) ;

Tried at SQL-Fiddle. You can test with data in the tables, to see which one fits your requirements and whether the joins should be on hsc.HEALTHSYSTEMID = ac.HEALTHSYSTEMID or with some other column.


How to pass a parameter into a function At the moment I have a view created, see it here in the answer. How can I create a function based on that crosstab() query , so I can pass a date, and get data for the specific date?

Also is it good practice to call that function multiple times and pass different dates to fill a chart (for example)?

I suggest an SQL function:

CREATE OR REPLACE FUNCTION foo(_date date)
  RETURNS TABLE (
   name       text  -- types have to match your actual types!
 , keyword_id int
 , project_id int
 , the_date   date
 , today      int
 , yesterday  int
 , week       int
 , month      int) AS
$func$

SELECT k.name, f.keyword_id, f.project_id, _date -- AS the_date -- col alias irrelevant
     , f.t AS today, f.y As yesterday, f.w AS week, f.m AS month
FROM   crosstab(

    -- crosstab function from previous question here
    -- http://dba.stackexchange.com/a/71266/3684

   ) f (rn int, keyword_id int, project_id int
       , t int, y int, w int, m int)
JOIN   keyword k USING (keyword_id);

$func$  LANGUAGE sql;

Call:

SELECT * FROM foo('2014-07-07');

Replace all occurrences of now()::date with your input parameter named _date.
 In Postgres 9.1 or older use the positional parameter $1 in SQL functions (which can be used in any version).

More code examples:

Return rows from a PL/pgSQL function
Pass In “WHERE” parameters to PostgreSQL View?

Tricky detail

The crosstab() function takes query strings as parameters. Function parameters are not visible inside crosstab(). So you need to pass in the date values as string literals!
I suggest the function format() for convenience. For example, the second parameter becomes:

,format('VALUES(%L::date), (%L), (%L), (%L)'
        , $1, $1 - 1, $1 - 7, ($1 - interval '1 month')::date
       )

instead of:

,$$
 VALUES
       (now()::date)
     , (now()::date - 1)
     , (now()::date - 7)
     , ((now() - interval '1 month')::date)
 $$

Complete code

SQL function

CREATE OR REPLACE FUNCTION foo_sql(_date date)
  RETURNS TABLE (
   name       text
 , keyword_id int
 , project_id int
 , the_date   date
 , today      int
 , yesterday  int
 , week       int
 , month      int) AS
$func$
BEGIN

SELECT k.name, f.keyword_id, f.project_id, _date
     , f.t, f.y, f.w, f.m
FROM   crosstab (
   $$
   SELECT rn
        , pk.keyword_id
        , pk.project_id
        , d.created_at
        , COALESCE(pr.pos, 0)
   FROM  (SELECT *, row_number() OVER () AS rn FROM project_keyword) pk
   CROSS  JOIN (
       $$
    || format('VALUES(%L::date), (%L), (%L), (%L)'
              , $1, $1 - 1, $1 - 7, ($1 - interval '1 month')::date)
   || $$
     ) d(created_at)
   LEFT JOIN (
      SELECT keyword_id
           , project_id
           , created_at::date AS created_at
           , min(position) AS pos
      FROM   project_report
      GROUP  BY keyword_id, project_id, created_at::date
      ) pr USING (keyword_id, project_id, created_at)
   ORDER  BY pk.rn, d.created_at
   $$

  ,format('VALUES(%L::date), (%L), (%L), (%L)'
              , $1, $1 - 1, $1 - 7, ($1 - interval '1 month')::date)
   ) f (rn int, keyword_id int, project_id int
       , t int, y int, w int, m int)
JOIN   keyword k USING (keyword_id);

END
$func$  LANGUAGE sql;

PL/pgSQL function

Shorter, reusing the VALUES expression. Probably a bit faster, too.

CREATE OR REPLACE FUNCTION foo_plpgsql(_date date)
  RETURNS TABLE (name text, keyword_id int, project_id int, the_date date
               , today int, yesterday int, week int, month int) AS
$func$
DECLARE
   _dates text := format('VALUES(%L::date), (%L), (%L), (%L)'
                        , $1, $1 - 1, $1 - 7, ($1 - interval '1 month')::date);
BEGIN

SELECT k.name, f.keyword_id, f.project_id, _date, f.t, f.y, f.w, f.m
FROM   crosstab (
  'SELECT rn
        , pk.keyword_id
        , pk.project_id
        , d.created_at
        , COALESCE(pr.pos, 0)
   FROM  (SELECT *, row_number() OVER () AS rn FROM project_keyword) pk
   CROSS  JOIN (
       ' || _dates || '
     ) d(created_at)
   LEFT JOIN (
      SELECT keyword_id
           , project_id
           , created_at::date AS created_at
           , min(position) AS pos
      FROM   project_report
      GROUP  BY keyword_id, project_id, created_at::date
      ) pr USING (keyword_id, project_id, created_at)
   ORDER  BY pk.rn, d.created_at'

  ,_dates
   ) f (rn int, keyword_id int, project_id int, t int, y int, w int, m int)
JOIN   keyword k USING (keyword_id);

END
$func$  LANGUAGE plpgsql;

CTE

For completeness, the "same" without persisting a function, with CTEs:

WITH d(day)   AS (SELECT '2014-07-07'::date)  -- provide your date here
,    v(dates) AS (
   SELECT format('VALUES(%L::date), (%L), (%L), (%L)'
                 , day, day - 1, day - 7
                 ,(day - interval '1 month')::date)
   FROM d
   )
SELECT k.name, f.keyword_id, f.project_id, d.day AS the_date
     , f.t AS today, f.y As yesterday, f.w AS week, f.m AS month
FROM   crosstab (
  'SELECT rn
        , pk.keyword_id
        , pk.project_id
        , d.created_at
        , COALESCE(pr.pos, 0) AS pos
   FROM  (SELECT *, row_number() OVER () AS rn FROM project_keyword) pk
   CROSS  JOIN (
       ' || (SELECT dates FROM v) || '
     ) d(created_at)
   LEFT JOIN (
      SELECT keyword_id
           , project_id
           , created_at::date AS created_at
           , min(position) AS pos
      FROM   project_report
      GROUP  BY keyword_id, project_id, created_at::date
      ) pr USING (keyword_id, project_id, created_at)
   ORDER  BY pk.rn, d.created_at'

  ,(SELECT dates FROM v)
   ) f (rn int, keyword_id int, project_id int
       , t int, y int, w int, m int)
JOIN   keyword k USING (keyword_id);


MySQL, get rows for non-matching reference or reference does not exist in other table MySQL, select non-matching reference or reference does not exist in other table.

For example.

table: category
category_id,    category_name
1               XYZ_1
2               XYZ_2
3               XYZ_3
4               XYZ_4
5               XYZ_5
6               XYZ_6

table: type
type_id,    type_name
1           A
2           B
3           C
4           D

table: type_match
match_Id,   type_id category_id
1           2       1
2           3       1
3           4       1
4           1       1
5           2       2
6           3       2
7           2       3
8           4       3
9           2       4

I need category_id where type_id is not 3 or category_id does not exist in type_match table:
expected result:

category_id
3  // category_id exist in type_match table but not matching type_id = 3
4  // category_id exist in type_match table but not matching type_id = 3
5  // category_id does not exist in type_match table
6  // category_id does not exist in type_match table

Please help.

SELECT c.category_id
FROM category c
LEFT JOIN type_match t ON c.category_id = t.category_id AND t.type_id = 3
WHERE t.category_id IS NULL

Or the same in NOT EXISTS form:

SELECT category_id
FROM category c
WHERE NOT EXISTS ( SELECT NULL
                   FROM type_match t
                   WHERE c.category_id = t.category_id
                     AND t.type_id = 3 )


MySQL client believes they're in a transaction, gets KILLed, wreaks havoc Open up two mysql command-line clients and connect to your database. In client #1, enter

START TRANSACTION;

In client #2, use SHOW PROCESSLIST, then

KILL [n];

where n is the id for client #1's connection. Bam—transaction rolled back. But client #1 doesn't know that. Then from client #1, send some command—say,

UPDATE clients SET important_field = NULL;

You'll get the response:

ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...

Query OK, 10000 rows affected (0.05 sec)
Rows matched: 10000  Changed: 10000  Warnings: 0

Oops, you say, better hit ROLLBACK! Then you realize, to your horror, that you're not in a transaction anymore.

Here's my question: In case I want to KILL a connection at some point, is there any way I can ensure that this "now you're in a transaction, now you're not" scenario doesn't happen, short of setting autocommit to 0 at the system level?

Note that the above is tested under mysql 5.1—if later versions provide a fix, I'd love to hear about it. I'd also love to see tests done with JDBC, ADO.NET, etc. to see whether they're susceptible to this same issue.

(On a meta note, this question arose from two questions over at Server Fault. I'm really hoping that the DBA community will prove more helpful...)

Update: See my answers below. This issue appears to be unique to the mysql command-line utility, with its bizarre auto-reconnect "feature." Most likely, any tool or library not built on top of the mysql utility will not exhibit this behavior. However, you may want to test whatever you're using to be sure, or take drachenstern's suggestion and wrap your transactions in stored procedures.

What was wrong with the answers on those two questions? They were 100% accurate, so I'm not sure what more we can do to help you here.

I would suggest that you confirm that you never assume that you're in a transaction. Always check to make sure you are. In TSQL it would be as simple as checking @@TRANCOUNT to be greater than 0. That's rather the same as any threaded situation where you want to check mutexes. What happens if you do it with a stored procedure? It kills the stored procedure, yes? Because SPs are intended to be atomic. What you're demonstrating has nothing to do with atomicity.

To be clear, this behavior is by design! Don't run atomic transactions by hand in the console, put them in a program so if the connection goes away it's gone. This is not something that you can "just hope works right".

If you have something that must be ACID, you must put it in a container that can be made ACID. This means a stored procedure or the like.


JOIN and GROUP BY with additional columns If I run the following query on my database I get the results pasted below:

SELECT t.id as ID,
j.text as Text,
l.location as Location,
FROM [text] t WITH (nolock)
INNER JOIN Text_Location tl WITH (nolock) on tl.textid = t.id
INNER JOIN Location l WITH (nolock) on tl.locationId = l.id

    ID       Text      Location
---------- ---------- ----------
     1         FC      London
     1         FC       Paris
     2         KG      Berlin
     3        TX1        Roma
     3        TX1      Vienna
     3        TX1      Dublin

How could I get the same results grouped by ID and having different columns for location? I would like to get something like this:

    ID       TEXT    LOCATION1   LOCATION2  LOCATION3
---------- ---------- ---------- ---------- ----------
     1         FC     London      Paris
     2         KG     Berlin
     3        TX1     Roma        Vienna     Dublin

Seems you would need to do some CASE statements along with some UNION ALL like this:

SELECT id,
       CASE
              WHEN idcounter <=1 THEN text1
              ELSE ""
       end AS text1,
       CASE
              WHEN idcounter <=1 THEN location1
              ELSE ""
       end AS location1,
       CASE
              WHEN idcounter <=2 THEN text2
              ELSE ""
       end AS text2,
       CASE
              WHEN idcounter <=2 THEN location2
              ELSE ""
       end AS location2,
       CASE
              WHEN idcounter <=3 THEN text3
              ELSE ""
       end AS text3,
       CASE
              WHEN idcounter <=3 THEN location3
              ELSE ""
       end AS location3
FROM  (
                SELECT   t.id        AS id,
                         Count(t.id) AS idcounter
                group BY t.id ) AS results1
UNION ALL
SELECT     t.id       AS id,
           j.text     AS text1,
           l.location AS location1,
FROM       [text] t WITH (nolock)
INNER JOIN text_location tl WITH (nolock)
ON         tl.textid = t.id
INNER JOIN location l WITH (nolock)
ON         tl.locationid = l.id
ORDER BY   l.location
LIMIT      1
UNION ALL
SELECT     t.id       AS id,
           j.text     AS text2,
           l.location AS location3,
FROM       [text] t WITH (nolock)
INNER JOIN text_location tl WITH (nolock)
ON         tl.textid = t.id
INNER JOIN location l WITH (nolock)
ON         tl.locationid = l.id
ORDER BY   l.location
LIMIT      1
offset     1
UNION ALL
SELECT     t.id       AS id,
           j.text     AS text3,
           l.location AS location3,
FROM       [text] t WITH (nolock)
INNER JOIN text_location tl WITH (nolock)
ON         tl.textid = t.id
INNER JOIN location l WITH (nolock)
ON         tl.locationid = l.id
ORDER BY   l.location
LIMIT      1
offset     2

If you provide an SQLFiddle or some such we could play with it.


Unable to call sp_start_job from website only I have an agent job that I'm trying to use to kick off an SSIS package via a web page. I have configured my web site running in an application pool as user X.

When I invoke sp_start_job as use X from SSMS it successfully starts the job.

When I use my web page to invoke sp_start_job permission is denied.

They user is an administrator locally, and a member of the sysadmin role.

What is preventing successfully invoking sp_start_job from the website?

Can you confirm with a Profiler trace which credentials are attempting to execute sp_start_job? I'm curious if it's the credentials of the client using the browser or the underlying application pool. If the account in question has sysadmin, it should already have the permissions to execute jobs.


Select with limit, get total count, optimize query The site mimics the "likes" tooltip from Facebook, the only difference is that you can like or dislike the content.

The tooltip shows the first 12 users that rated the content and displays the number of users not shown (i.e. John, Mike, Kim and 24 more rated this content.). If the user clicks on the tooltip, the full list is retrieved via AJAX and displayed on a modal window.

This is my current query for both actions:

SELECT
   mr.*,
   u.name
FROM
   medias_ratings mr
   LEFT JOIN users u ON u.id = mr.user_id
WHERE
   mr.media_id = XXX
   AND mr.id_client = YYY
ORDER BY
   u.name

How can I optimize the above query? The first thing I can think of is having a different query for the limited result. Something like this:

/* Get the first 12 ratings */
SELECT
   mr.*,
   u.name
FROM
   medias_ratings mr
   LEFT JOIN users u ON u.id = mr.user_id
WHERE
   mr.media_id = XXX
   AND mr.id_client = YYY
LIMIT 12

/* Get the total rating count */
SELECT
   COUNT (*)
FROM
   medias_ratings mr
WHERE
   mr.media_id = XXX
   AND mr.id_client = YYY

I've read that running a second query is usually faster than using SQL_CALC_FOUND_ROWS.

Edit 1: The media table already keeps a field for the total likes, so the query is just to fetch the list of users that liked the content.

Edit 2: Table structure, most of the user fields were omitted for brevity

CREATE TABLE `medias_ratings` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `id_user` int(11) NOT NULL,
 `id_media` int(11) NOT NULL,
 `id_client` int(11) NOT NULL,
 `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
 `rate` int(1) NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=MyISAM

CREATE TABLE `users` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `user` varchar(100) NOT NULL,
 `pass` varchar(100) NOT NULL,
 `category` int(11) NOT NULL DEFAULT '4',
 `name` varchar(100) DEFAULT NULL,
 `company` varchar(100) DEFAULT NULL,
 `area` varchar(100) DEFAULT NULL,
 `position` varchar(100) DEFAULT NULL,
 `country_code` varchar(3) DEFAULT NULL,
 `email` varchar(255) DEFAULT NULL,
 PRIMARY KEY (`id`),
 KEY `category` (`category`),
 FULLTEXT KEY `SEARCH` (`user`,`name`,`email`)
) ENGINE=MyISAM

MyISAM or InnoDB?

Your link does not say anything about SQL_CALC_FOUND_ROWS being slower.  And I would expect it to be faster if for no other reason than having one fewer round trip to the server.

The risk comes when you have thousands of rows.  Then either method may be spending more time than you can afford.  Come back when you encounter that; we can discuss workarounds.

Hmmm... I wonder if the LEFT makes the CALC run faster, since it should not have to reach into users after 12 rows.  But I fear that the optimizer is not that smart.  I'll guess that the following subquery will avoid that issue:

SELECT  SQL_CALC_FOUND_ROWS mr.*,
      ( SELECT  name
            FROM  users
            WHERE  id = mr.user_id
      ) AS name
    FROM  medias_ratings mr
    WHERE  mr.id_media = XXX  -- changed by EDIT
      AND  mr.id_client = YYY
    ORDER BY  mr.`date` DESC
    LIMIT  12;

Now, for indexes.

mr:  INDEX(id_media, id_client, date)  (Put the '=' columns first, then the ORDER BY.) -- changed by EDIT

users: Add a 'covering' index: INDEX(id, name) specifically to make this query faster -- it can run "Using index", as seen in the EXPLAIN.


Need help in incrementing the date in oracle Error thrown as "Invalid number" while trying to increment the date. Given below is the query executed through sqlplus. Please help me on this.

select ''''||to_date(:To_date, 'dd-mon-yyyy')+1||'''' from DUAL;

Expected output (if :To_date variable is '09-DEC-2014'):

'10-DEC-2014'

Note the extra brackets.

SQL> variable d varchar2(100);
SQL> exec :d := '09-DEC-2014';

PL/SQL procedure successfully completed.

SQL> select '''' || (to_date(:d, 'DD-MON-YYYY') + 1) || '''' from dual;

''''||(TO_DATE(:D,'D
--------------------
'10-DEC-14'

Changing date format:

SQL> alter session set nls_date_format='DD-MON-YYYY';

Session altered.

SQL> select '''' || (to_date(:d, 'DD-MON-YYYY') + 1) || '''' from dual;

''''||(TO_DATE(:D,'DD-
----------------------
'10-DEC-2014'


SQL Server Upgrading Compatibility Level from 2005 to 2012 We have old database in SQLServer 2012 Enterprise in Compatibility mode: 90 - SQL Server 2005 . What are the consequence of changing database compatibility mode from 90 into 110-SQL Server 2012? Many legacy applications utilize the database.
Nothing is breaking in QA automated unit and integration tests, however trying to review potential items which could be missing.

I understand backward compitability has issues, changing database from 110- to 90, eg any queries applying newer syntax or tempdb will fail.

However what kind of issues can occur going from 90 to 110? What items can break?

However what kind of issues can occur going from 90 to 110? What items can break?

Well this is basically very subjective, while I cannot tell you what could break from the code side I could definitely point you towards article which list down all breaking changes. Please refer to Breaking Database Engine Changes in SQL Server 2012.

With my past experience changing database compatibility level does not makes database totally dysfunctional. The compatibility level only defines how certain features would work in certain compatibility level. To help you again MS has listed down such changes in ALTER DATABASE (Transact-SQL) Compatibility Level. The article is thorough and includes details of all versions of SQL server

Using above 2 links you need to jot down the what could possibly fail in your scenario and fix it beforehand. After this is done the process is easy you run the command

ALTER DATABASE database_name
SET COMPATIBILITY_LEVEL = 110

Please make sure you run this during off production hours. Now you can ask users to test if something fails and it is really important for it to work run the command again and change compatibility level back to 90. So whole process also includes bit of hit and trial method.


How to find a list of local MS SQL Servers? I looked at How can I determine installed SQL Server instances and their versions? already but CMD returns nothing:

C:\Windows\system32>sqlcmd -L

C:\Windows\system32>

But when using Visual Studio (v2019), I go to View > SQL Server Object Explorer, I see two SQL Server instances with system databases inside. One is called (LocalDB)\MSSQLLocalDB and the other (LocalDB)\ProjectsV13.

Both are using SQL Server 2016 (v13.0.4001). Using that I was able to connect through SSMS 2018 as well.

My question is: why aren't these instances showing up in the command line? And can I delete one of the server instance? Why are there two?

I have tried:

I checked my services and I there is only SQL Server VSS write. everything else is irrelevant to SQL. I looked up in SQL Server configuration manager but SQL Server services is empty there as well. I checked in C:\Program Files (x86)\Microsoft SQL Server\90\Shared and sqlbrowser.exe was not there also.
I just tried setting up inbound and outbound rules on TCP 1433 and UDP 1434 to allow connections. Failed again with nothing returned from cmd.
I turned on firewall logs and checked the logs after running the command a few times but nothing is being recorded (success or failure). I opened powershell and did test-netconnection to my ip and port 1433 and 1434 but the ping failed. Still nothing written to the firewall log files.

-- Powershell

Import-Module sqlserver
'computername'| Foreach-Object {Get-ChildItem -Path "SQLSERVER:\SQL\$_"}

Make sure you don't have linked server (LocalDB)\ProjectsV13 pointing to itself (LocalDB)\MSSQLLocalDB

Check any alias defined


How to "group" based on consecutive occurrences of an element in a column in postgres I have a table that, when ordered, describes a path by point descriptions in each row.
A column tells whether a row is a special (stop) point within that path and is empty otherwise. The path starts and ends with such a row and has many in between. What I want to do is create line segments from the point rows that start at such a row and also end at such a row.

point  stop
  1     yes
  2
  3     yes
  4
  5
  6     yes

I want to call ST_Makeline from PostGIS to turn the ordered points into these line segments. How can I enforce that ST_Makeline, being a "summary" function is only called for the inclusive point groups 1 to 3 and 3 to 6?

I personally think this is not a good design because all intermediate stop points must be included into two segments.

IMHO, you should be able to transform it into something similar to:

id | start | end | stop
---|-------|-----|-----
 1 |   1   |  2  |
 2 |   2   |  3  | yes
 3 |   3   |  4  |
 4 |   4   |  5  |
 5 |   5   |  6  | yes

But using your current schema you could try by using:

WITH x AS
(
  SELECT
      point,
      stop,
      LEAD(point) OVER (ORDER BY point) nxt,
      SUM(CASE WHEN stop = true THEN 1 ELSE 0 END) OVER (ORDER BY point) grp
  FROM
      tbl
  ORDER BY
      point
)
SELECT
    grp, ST_MakeLine(array_agg(ST_MakePoint(point, nxt::int))) lines
FROM
    x
WHERE
    nxt IS NOT NULL
GROUP BY
    grp
ORDER BY
    grp;

That basically returns an array of points like this:

grp | points
--: | :------------------------
  1 | {"(1,2)","(2,3)"}
  2 | {"(3,4)","(4,5)","(5,6)"}

Then using ST_MakePoint and ST_MakeLine you can get:

grp | lines
--: | :-----------------------------------------------------------------------------------------------------------------
  1 | 010200000002000000000000000000F03F000000000000004000000000000000400000000000000840
  2 | 010200000003000000000000000000084000000000000010400000000000001040000000000000144000000000000014400000000000001840

db<>fiddle here


DROP DATABASE Recovery I need to fix a problem. I have dropped a database. I have restored the *.frm and *.ibd files.

I haven't copied them into the mysql folder yet - can I just copy the files back and bring the server back up ?

ibdata1, ib_logfile0 and ib_logfile1 are still intact from when I quit the server with a kill -9 in a panic.

What's the best way to proceed - I haven't tried anything yet as I was worried I might lose the data (if it's not lost already).

If you dropped a table with innodb_file_per_table ON it's better not to undelete the .ibd file, but rather scan the disk partition with page_parser from percona data recovery toolkit.

The ibd files after most certainly corrupt and you won't be able to IMPORT TABLESPACE. Another problem is undelete tools usually (every time I've seen actually) misses chunks of data for sake of reconstructing a file.

To quickly check what's inside the undeleted ibd and see how many records lost/recovered I can suggest https://recovery.twindb.com (DISCLAIMER - I'm the author). But, again, I'd parse the partition.


How to clear the InnoDB redo log before starting mysqld for crash recovery System: Windows Server 2012 R2, MariaDB 10.2.8

We are currently trying to recover from a quasi-crash (it was a normal shutdown, but mysqld won't start up again)
and are having problems with the redo log of InnoDB during start up. If we start with innodb_force_recovery=0,
the process gets to the point where it logs

2019-06-14  9:29:41 14580 [Note] InnoDB: Starting final batch to recover 1150 pages from redo log.

and then gets stuck there. Since there are no further log entries from that point it is not clear if it takes more
time or silently fails. We tried waiting for 22 hours, with no visible progress. The process ran with <1% CPU usage
and unchanging memory usage, so presumably it stops silently.

We then tried increasing numbers of innodb_force_recovery and at 6 the server finally starts up again by
skipping the redo log entirely. Unfortunately this also starts with read-only. Thus clearing the log by using
innodb_fast_shutdown=0 doesn't appear to do anything on shutdown.

What we need now is a way to clear the redo log with mysqld being offline. Simply deleting the log files
ib_logfile0 and ib_logfile1 doesn't seem to work either, since they somehow get recreated during
start up, which runs into the same problems as above. We're assuming the data somehow is saved in ibdata1,
but we can't delete it from there without also deleting the tablespace.

As a last resort we tried deleting the corrupted database by deleting it's directory from disk (since we can't delete
it within MySQL as it only starts in read-only) and then starting with innodb_force_recovery=3, but once again
the process tries to perform the redo log and then stops because the database doesn't exist.

It seems the best course of action is to somehow start mysqld without performing the redo log or clearing it beforehand,
then deleting the corrupted database and restoring it from a backup. But we can't get it to start in write-mode without
trying and failing with the redo log. Is there a way to clear it by directly manipulating the files?

What you should be doing, is

kill mysqld process. Disable the MySQL service (or whatever it is called), if it runs as service.
upgrade to latest 10.2 (it is already on 10.2.24 as of today, you are using a very old version)
Enable the service and start again.

If it does not start again, take the mysqld process minidump while it is hanging, and submit a bug to JIRA https://jira.mariadb.org/browse/MDEV

This is definitely more helpful than trying to "recover" by deleting Innodb redo logs (this is one of the things you should not be doing).


What is the advantage of setting a character column as VARCHAR(32) over VARCHAR(255) What is the advantage of setting a database column VARCHAR(32) over VARCHAR(255) even though the two of them consume 2-byte memory?

You are probably defaulted to CHARACTER SET utf8 (or utf8mb4), in which case (255) would need 2 bytes for the length.

But the real problem is with temp tables (pre-8.0):  If the SELECT is so complex that it needs to generate a temp table (eg, for sorting), it will try first to use a MEMORY table.  In doing so, it will turn VARCHARs into CHARs, thereby taking 765 bytes (if utf8).  If, in doing so, this (and other actions) take too much space, it will switch to the slower MyISAM for the temp table.  See the status value Created_tmp_disk_tables.

For SSN, country_code, zip, etc, explicitly say CHARACTER SET ascii (or latin1).  This will avoid extra bloat for the charset.

For fixed-length columns, use CHAR, not VARCHAR.  So, if this is a US SSN, shouldn't it be

SSN CHAR(11) CHARACTER SET ascii  COMMENT 'example: "123-45-6789"'

In spite of all that, the performance difference will be minor.

More important in the serious legal implications (in the US) if your computer is hacked into, and you lose sensitive info such as SSNs.  The cost of such a breach could bankrupt your fledgling business.  And no, simply encryption won't suffice -- think about how easy the hacker can find the encryption key.


Why is an unindexed range operator (<@) faster than using BETWEEN with an index? NB This is the same setup as this question, where here I'm asking specifically about something I was specifically not asking about over there.
I've got a table with a column utc timestamptz, with a "btree" index on the utc column:
CREATE TABLE foo(utc timestamptz)

CREATE INDEX ix_foo_utc ON foo (utc);

This table contains about 500 million rows of data.
When I filter utc using BETWEEN, the query planner uses the index as expected:
> EXPLAIN ANALYZE
SELECT
   utc
FROM foo
WHERE
    utc BETWEEN '2020-12-01' AND '2031-02-15'
;

Bitmap Heap Scan on foo  (cost=3048368.34..11836322.22 rows=143671392 width=8) (actual time=12447.905..165576.664 rows=150225530 loops=1)
  Recheck Cond: ((utc >= '2020-12-01 00:00:00+00'::timestamp with time zone) AND (utc <= '2031-02-15 00:00:00+00'::timestamp with time zone))
  Rows Removed by Index Recheck: 543231
  Heap Blocks: exact=43537 lossy=1818365
  ->  Bitmap Index Scan on ix_foo_utc  (cost=0.00..3012450.49 rows=143671392 width=0) (actual time=12436.236..12436.236 rows=150225530 loops=1)
     Index Cond: ((utc >= '2020-12-01 00:00:00+00'::timestamp with time zone) AND (utc <= '2031-02-15 00:00:00+00'::timestamp with time zone))
Planning time: 0.127 ms
Execution time: 172335.517 ms

I could write the same query using a range operator without an index at all:
> EXPLAIN ANALYZE
SELECT
   utc
FROM quotation.half_hour_data
WHERE
    utc <@ tstzrange('2020-12-01', '2031-02-15')
;

Gather  (cost=1000.00..9552135.30 rows=2556133 width=8) (actual time=0.179..145303.094 rows=150225530 loops=1)
  Workers Planned: 2
  Workers Launched: 2
  ->  Parallel Seq Scan on foo  (cost=0.00..9295522.00 rows=1065055 width=8) (actual time=5.321..117837.452 rows=50075177 loops=3)
      Filter: (utc <@ '["2020-12-01 00:00:00+00","2031-02-15 00:00:00+00")'::tstzrange)
      Rows Removed by Filter: 120333718
Planning time: 0.069 ms
Execution time: 153384.494 ms

These are doing the same operation (albeit that <@ is right-hand exlusive and BETWEEN is inclusive.)
How can the unindexed query with <@ be faster than the indexed query with BETWEEN?
Surely if ignore an index is faster, the query planner should know that in advance?
Or is this something specific to do with the amount of memory my PG instance has, and the size of the query (big!)

My Postgres version:
"PostgreSQL 10.13 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-11), 64-bit"

First, the index scan is slower than necessary, because your work_mem is not big enough to contain the bitmap. Increase it until you get no more “lossy” heap blocks during the bitmap heap scan.
The second plan is faster than the first because it uses two parallel workers (which the other plan cannot). But it uses way more resources: it keeps three processes busy for 117837 milliseconds (plus an additional 27465 milliseconds to collect the results), while the bitmap index scan is done in 153140 milliseconds.


Trace ODBC commands: What is the registry path for TraceSQLMode and where is SQLOUT.TXT? In the Optimizing Microsoft Office Access documentation, it mentions that we can:

  ...edit a Microsoft Windows registry setting that allows you to see
  the commands that the Office Access database engine is submitting to
  ODBC.

It goes on to give detailed instructions as to how to do this:

  To enable tracing of all ODBC commands from the Jet database engine:

  From the Windows Start menu, select Run.
  Type Regedit to open the Registry Editor.
  If you are using a version of Office Access prior to Office Access 2007, navigate to the following registry key, which
  appears as a folder in the Registry Editor.

  HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\ODBC

  Office Access 2007 uses a customized version of the Jet database
  engine, named the Office Access Connectivity Engine (ACE), which is
  not shared with other Windows applications. If you are using Office
  Access 2007, navigate to the following registry key.

  HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\12.0\Access Connectivity
  Engine\ODBC

  Double-click the TraceSQLMode setting, change the value from 0 to 1,
  and click OK.
  If Office Access is open when you make this change, you
  must close and reopen Office Access for the change to take effect.

  After making this change in the registry, queries submitted to any ODBC data source are logged in a text file named Sqlout.txt.

However, as you can probably tell, this documentation is quite old (it was written about Access 2007 and was published in 2006). Because it is so old, the registry information seems to be out-of-date for newer products such as Office 2016.

Also, it would seem that Access 2016 doesn't use Jet Engine, but instead uses the Access Connectivity Engine.

As such, I'm having trouble finding a couple of things:

Where is the Access Connectivity Engine >>> TraceSQLMode registry key for Access 2016?
Additionally, where does the sqlout.txt file get saved on my computer?

I managed to dig up the path from a comment on this page. On my machine, with Access 2016, the TraceSQLMode registry key is found here:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\ClickToRun\REGISTRY\MACHINE\Software\Microsoft\Office\16.0\Access Connectivity Engine\Engines\ODBC\TraceSQLMode

I found something on this page that mentioned that the sqlout.txt file is stored in the Default database folder as indicated in MS Access Tools-> Options-> General-> Default database folder. In my case, it is located here:
C:\Users\[my user name]\Documents\sqlout.txt


Why is this MySQL SELECT performing so slowly despite indexing? I have a large MyISAM table A (500m entries) that connects two other tables B,C with a n:m relation.

So my table A looks like this:

CREATE TABLE `A` (
  `id_b` int(10) DEFAULT NULL,
  `id_c` int(12) DEFAULT NULL,
  UNIQUE KEY `Uniqueness` (`id_c`,`id_b`),
  KEY `id_b` (`id_b`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Every entity in B is related to 1-12 entities in C with an average of 8 and a mode of 10.

Every entitiy in C is related to 100-500 entities in B (but this is linearly growing over time).

Now for a specific entitiy c I want a list of all other entities in C that it is connected to through B and how many entities in B they are connect through.
Or more directly: I have a specific c, that is connected 100-500 b's which are each connected to a handful of other entities in C. I want to know which C's the b's are connect with, and how often.

My simplified query looks like this:

SELECT COUNT(OtherA.id_b), OtherA.id_c FROM A
INNER JOIN A OtherA
ON OtherA.id_b = A.id_b
WHERE A.id_c=[MY_SPECIFIC_C_ID]
GROUP BY OtherA.id_c

And takes over a second to execute. As described above, the inner join yields at max a few thousand results and the group reduces it to a few hundred. As far as I can tell I indexed correctly, so I am confused why this query is taking so long.

EXPLAIN yields the following result:

row1
id--------------1
select_type-----SIMPLE
table-----------A
type------------ref
possible_keys---Uniqueness,id_b
key-------------Uniqueness
key_len---------5
ref-------------const
rows------------462
Extra-----------Using where; Using index; Using temporary; Using filesort
row2
id--------------1
select_type-----SIMPLE
table-----------OtherA
type------------ref
possible_keys---id_b
key-------------id_b
key_len---------5
ref-------------THISDB.A.id_b
rows------------8
Extra-----------Using where

As suggested I have tried manually extracting the list of id_b's:

[concated_list] = select group_concat(distinct id_b) as id_list from A where id_c = ...;

And then running

SELECT count(id_b), id_c FROM A WHERE id_b IN([concated_list]) GROUP BY id_c

The first query is instant, the second query takes almost as long as the original query, so it was suggested that I need to fine-tune my MySQL Vars. Where do I start?

EXPLAIN SELECT count(id_b), id_c FROM A WHERE id_b IN([concated_list]) GROUP BY id_c

results in:

id--------------1
select_type-----SIMPLE
table-----------A
type------------range
possible_keys---id_b
key-------------id_b
key_len---------5
ref-------------NULL
rows------------802
Extra-----------Using where; Using temporary; Using filesort

MySQL version is 5.5.35-0ubuntu0.12.04.2.

EDIT: Since the first try with a subselect lead into doom, it might be necessary to split the table into a hot range and a "cold" range. That solution only could help if you have some criteria for id_c and id_b, which shows if this id_c will be read often or rarely, for example the age of this. But even deleting from such a big table is not so easy, it would need an optimize which takes ages. It would need some clever ideas to get as little as possible reads which "overlap" between both tables, but since you want to read all rows with id_b from different id_c, there will be cases where you need two queries and adding afterwards.

As already stated in the comments, look into key_reads and key_reads_request with SHOW VARIABLES LIKE 'KEY%';. Check if key_buffer_size is big enough (https://dev.mysql.com/doc/refman/5.0/en/myisam-key-cache.html). You could arrange two different key buffers, one only for that specific table (https://dev.mysql.com/doc/refman/5.0/en/multiple-key-caches.html), one for the rest.

The suggestion with an index id_b, id_c should help a lot, since your query can then be processed completely in index, not from the (not bigger) "real table".

Try doing an analyze table A in low-traffic-times.

It really would help to split the table into many sections, especially for OPTIMIZE TABLE and ANALYZE TABLE, but your kind of query makes that really hard.

Doomed original solution:
I think you create many N x N - block of rows, where N is the number of rows in tableA with identical id_b values, since the join has to combine the id_b - blocks. So when you have many rows for some id_b, time squares up with that number.

It might help to use subselect in that case.

EDIT: EDIT undone.

SELECT COUNT(*), OtherA.id_c
FROM A OtherA where OtherA.id_b in (
    select DISTINCT id_b from A where A.id_c = [MY_SPECIFIC_C_ID]
)
GROUP BY OtherA.id_c

So the id_b can be selected in one scan and then can be sent through the index to find the right rows.

EDIT:
The Explain inserted by the Questioner shows that this subselect is a very bad idea in that case, no wonder it takes forever:

EXPLAIN on the suggested query looks like this:

row1
id--------------1
select_type-----PRIMARY
table-----------OtherA
type------------index
possible_keys---NULL
key-------------Uniqueness
key_len---------10
ref-------------NULL
rows------------496167246
Extra-----------Using where; Using index
row2
id--------------2
select_type-----DEPENDENT SUBQUERY
table-----------A
type------------reg
possible_keys---Uniqueness, id_b
key-------------Uniqueness
key_len---------10
ref-------------const,func
rows------------1
Extra-----------Using where; Using index; Using temporary


Mirroring after restoring the database on a new machine We've had alot of problems with one of our database servers and two days ago the call was made to change a disk and reinstall the OS. Now this server functioned as part of a mirroring setup. Basically after the OS and SQL we're reinstalled i restored the Master database hoping mirroring would continue without causing my headpains.

I should state i'm not a DBA but i try to make due with the knowledge i have.

At the moment the server is giving the following errors;

Please create a master key in the database or open the master key in the session before performing this operation.

Followed by the following Information message;

Database Mirroring login attempt failed with error: 'Connection handshake failed.
Error 15581 occurred while initializing the private key corresponding to the certificate.
The SQL Server errorlog and the Windows event log may contain entries related to this error. State 88.'.  [CLIENT: XXX.XXX.XXX.92]

We've setup mirroring using Certifcates, this is because the servers do not run in the same domain. Now from googling i should have apperently made a backup of the master key (sadly i didn't even know SQL had a master key so i didn't).

What suprises me is that i can actually backup the certificate, i can login to the SQL Server using different credentials so it does seem to be able to encrypt/decrypt somethings (unless these things are not Master Key related).

Basically i'm somewhat at a deadend, how do i resolve these problems? Should i completely reinstall the mirroring? Is this at all recoverable? Any adivce and pointers in the right direction would be very much appreciated.

Answer (ill post as the answer later, need to wait 8 hours :( )

Bit fast for me answering my own question, but basically what i did was the following:

On the "restored" server

Remove the Endpoint
Remove the All Mirror related users, logins & certifactes
Create a new certificate and setup an endpoint
Create inbound access for the Witness and Principal

On the Witness and Principal;

Remove the user, login & certificate of "restored" server
Create a new user, login with the newly generated certificate from the restored server.

After this my mirroring started to Synchronize.

Certificate based authentication of Database Mirroring is explained in How Does Certificate based Authentication Work. After your server rebuild you should had open the master database database master key (hic!) using OPEN MASTER KEY ENCRYPTION BY PASSWORD=... and then add the new server service master key encryption to it using ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY. Then DBM would had access to the endpoint certificate private key and would successfully authenticate with the partner.

If you do not have the original master database master key password (shame!) then you need to force a new one, which may loose the DBM endpoint certificate private key encryption. If you also have encrypted the DBM endpoint certificate with a password then you can open it using that password and then add the new master key encryption to it, afterwards the DBM will successfully connect.

If you cannot recover the original DBM endpoint certificate private key then you need to replace it. You do not have to drop the endpoint, nor the mirroring related users and certificates. You can simply create a new certificate, then associate the new certificate with the existing endpoint using ALTER ENDPOINT ... FOR DATBASE_MIRRORING (CERTIFICATE = ...). On the partner(s) (including the Witness) you need to copy the public key of the certificate, create a new certificate for it in master and have it be owned by the user associated with the previous server certificate/user/login. This is the least disruptive way.

What you did is ultimately correct, but kinda of using the sledgehammer. Given the complexity of all the steps, I don't blame you for redoing everything from scratch. See also Replacing Endpoint Certificates that are near expiration.


How to determine right value for innodb_sync_array_size How do I determine the right value for innodb_sync_array_size in MySQL? We have version 5.7 deployed on AWS RDS with 32 vCPUs, and it sees about 26 concurrent threads on average. When I measure waits using:
 show engine innodb mutex, the total number of waits increases by about 143k / hour.
The MySQL documentation does say that a higher number will help with high concurrency and wait situation, but I cannot find any documentation on what to set this number and how to determine whether this will indeed help me.

This might help in understanding it more in details.


SSIS: To run the package outside of SQL Server Data Tools you must install expression task of integration services or higher Me and my colleague using the same version of .Net framework (4.5) and installed SSDT in visual studio 2013. He prepared a package (not so complex) and can able to execute the same from SSDT without any issue. He imported the package to the integration services under MSDB for testing. If he 'Run package' from integration services he gets an error stating that "To run the package outside of SQL Server Data Tools you must install expression task of integration services or higher".
If I tried to execute by clicking 'Run package' from integration services (MSDB) which he imported; I can able to execute it without any issues.

We have verified all the installation and everything looks similar. Can you guys help me to resolve this issue?

When you run the package from msdb it executes on the machine that you are running it from.

(Unlike packages stored in the 2012+ SSISDB catalog where executing them spawns an execution process on the Server itself).

You must have installed SQL Server Integration Services onto your own machine from the SQL Server installation media, whereas your colleague hasn't. If Integration Services isn't installed you are restricted to running packages in SSDT. Executing packages that run locally with dtexecui will fail.


What is MSrepl_tran_version? (MS SQL Server) So, I'm an "enterprise software developer" (welcome to hell, here's your accordion), meaning I'm not a DBA and I'm inheriting something that's been around for years and very few people have a crisp explanation for much at all.

My question:  what, exactly, is msrepl_tran_version?  We have it in all our tables (a rather large number), but I don't believe we're using replication.  I guess we did at some point in the past, maybe.

However, we seem to be using this field for concurrent-update detection; at update time, we check this field to see if it's changed, and, if so, reject the user's change (error on the screen, force them to refresh, etc., etc.0

Does this field get updated magically, somehow?  Should I be looking for a trigger?  (I doubt it.)  If we're not using replication, what do changes in this field affect?  I can't seem to actually find a good overview explanation of what this column is.

TIA.

John.

what, exactly, is msrepl_tran_version? We have it in all our tables (a rather large number), but I don't believe we're using replication. I guess we did at some point in the past, maybe.

when you have configured Transactional replication with updatable subscriptions, replication adds the column msrepl_tran_version to every table. If the table is dropped from the publication, the msrepl_tran_version column is not removed.

Check with your DBA to see if you still have replication or not.

If you are not using Replication, then to see how this column gets updated, I would suggest you to use profiler.

IF this column is not being used anymore, you should be good to remove/drop it. Refer to my answer with script here.


Index performance on ON versus WHERE I have two tables

@T1 TABLE
(
    Id INT,
    Date DATETIME
)

@T2 TABLE
(
    Id INT,
    Date DATETIME
)

These tables have a non-clustered index on (Id, Date)

And I join these tables

SELECT *
FROM T1 AS t1
INNER JOIN T2 AS t2
ON
    t1.Id = t2.Id
WHERE
    t1.Date <= GETDATE()
    AND
    t2.Date <= GETDATE()

This can also be written as

SELECT *
FROM T1 AS t1
INNER JOIN T2 AS t2
ON
    t1.Id = t2.Id
    AND
    t1.Date <= GETDATE()
    AND
    t2.Date <= GETDATE()

My question is, which of these two queries gives the better performance and why? Or are they equal?

The performance will be the same. The optimizer will recognize this and create the same plan.

On the other hand I wouldn't say they are equal. The first form in the question is far more readable and generally expected.

For an example using some tables I have at hand you can see the execution plan is exactly the same no matter how I write the query.

You should be able to determine the query plans for your own tables and data set so you can see what happens in your situation though.

SELECT * FROM salestable , custtable
WHERE salestable.custaccount = custtable.accountnum
AND salestable.dataareaid = custtable.dataareaid

SELECT * FROM salestable
JOIN  custtable
ON salestable.custaccount = custtable.accountnum
AND salestable.dataareaid = custtable.dataareaid

SELECT * FROM salestable JOIN custtable
ON salestable.custaccount = custtable.accountnum
WHERE salestable.dataareaid = custtable.dataareaid

Gives these execution plans


script breaks my ability to run psql I've successfully installed postgres on my Ubuntu machine.  I am running a script for a website I am working on, here is the relevant code:

apt-get -y install postgresql libpq-dev
su postgres <<EOF
    createuser app
    createdb ultimaterehab
EOF
PG_HBA=$(su postgres -c "psql -t -P format=unaligned -c 'SHOW hba_file'")

When executed this script gives the error:

createuser: could not connect to database postgres: FATAL:  no pg_hba.conf entry for host "[local]", user "postgres", database "postgres", SSL off
createdb: could not connect to database template1: FATAL:  no pg_hba.conf entry for host "[local]", user "postgres", database "template1", SSL off

and when I try running psql I get a similar error:

psql: FATAL:  no pg_hba.conf entry for host "[local]", user "robin", database "robin", SSL off

I have tried editing the pg_hba.conf file to add the hosts but I keep getting the same error.

PG_HBA=$(su postgres -c "psql -t -P format=unaligned -c 'SHOW hba_file'")

That seems potentially problematic.

That line above shows createdb ultimaterehab. That line doesn't however connect to the ultimaterehab database. It connects to the default database.
Debian/Ubuntu already does the setting up for the right pg_hba. You don't have to do anything whatsoever. If you absolutely need the location of the pg_hba file, which you do not need, you'd be better of working with the system utility pg_conftool to get that location: pg_conftool show hba_file.

Moreover, ubuntu doesn't use su, it uses sudo.

sudo -u postgres createuser app
sudo -u postgres createdb ultimaterehab

And you're done.


How to get a sense of how busy is the server I am not in IT infrastructure team. But I have a SQL database server. We are having this intermittent problem with Dynamics SL and everyone in the company sort of pointing the finger that the server is too busy, working too hard, ect2. I disagree with the assessment and would like to get some concrete data from the server to give to my co worker

I was thinking to capture batch request per second and presented the data.
My Question:

Do batch request per second the right approach to see how
busy/overloaded/overworked the server is?
if yes then how do I capture this?
do I have to keep SQL profiler open and save the data daily?
is there an automated way that allow me to capture some sort of metrics on how much request is hitting the server?
If batch request per second is not the right data, then what would be the right info to get? and how to get it?

thanks

I wouldn't buy into an analysis done solely on batch per second. At the very least I would ask for a baseline to compare against (eg. "6 month ago we were handling 50 b/s, now we're doing 500 b/s"). Simply stating "our server handles 342 batches per second" does not say much. At the very least you could capture a timeline and say something like "there are daily spikes of b/s at 9:30 am". But you can do much, much more.

To start with, you need to learn some basics about the performance toolset at your disposal. First and foremost logman.exe. Absolutely read Windows Performance Monitor (all the sub-chapters too, there's a lot to learn there).

Next I would recommend reading How to analyse SQL Server performance. It really answers a lot of your questions.

And now the answers to your questions:

  Do batch request per second the right approach to see how busy/overloaded/overworked the server is?

No.

  if yes then how do I capture this?

Not applicable.

  do I have to keep SQL profiler open and save the data daily?

Never. Even if trace events would be relevant, never SQL Profiler. Read about SQL Trace and server side tracing.

  is there an automated way that allow me to capture some sort of metrics on how much request is hitting the server?

Yes, many. the engine itself cpature a lot of info in wait stats, the OS exposes perf counter and a rich toolset around them. Read the articles linked above.

  If batch request per second is not the right data, then what would be the right info to get? and how to get it?

Ditto, read the articles linked above to get some basic info and narrow down your question.


Can the LOG file fill up from index rebuilds where the indexes are not fragmented? Working on SQL Server 2014.

I have noticed that a log file (60GB) on a database has filled up within the past day. To my knowledge, I am the only developer using this database and I have not been performing any transactions on the database for over a week. I asked the DBA if anyone else is using the database and they did not think so. The DBA mentioned that the automated index rebuild jobs could be filling up the log.

My question is this, can automated index rebuild jobs that run hourly fill up the log file? My assumption here is that the index would not be fragmented and so there would not be any work to rebuild the index. Therefor, the log file should not fill up. Is this assumption false? Am I missing something with how SQL Server index rebuilds work and how that might impact the log file?

Update

We are using Ola's solution: Click Here

EXECUTE [dbo].[DatabaseBackup]
@Databases = 'USER_DATABASES',
@Directory = N'\\DirectoryPath\',
@MirrorDirectory = N'\\DirectoryPath\',
@BackupType = 'LOG',
@Verify = 'Y',
@CleanupTime = 48,
@MirrorCleanupTime=24,
@CheckSum = 'Y',
@LogToTable = 'Y',
@Compress = 'Y'

My question is this, can automated index rebuild jobs that run hourly fill up the log file?

Yes. An index rebuild typically rewrites all of the data contained in the index. I don't know of any exceptions, but there might be some. From the documentation:

  Rebuilding an index drops and re-creates the index. This removes
  fragmentation, reclaims disk space by compacting the pages based on
  the specified or existing fill factor setting, and reorders the index
  rows in contiguous pages.

You also asked:

  My assumption here is that the index would not be fragmented and so there would not be any work to rebuild the index. Therefor, the log file should not fill up. Is this assumption false?

Fragmentation does not matter because a new copy of the data is created. You may be thinking of the REORGANIZE command. The amount of work performed by that command depends on fragmentation of the data, but it's done in small transactions.


How to find SSL root cert that made connection to the database in PostgreSQL? When we connect to postgreSQL via ssl-mode=verify-full how will I make sure if the certificate I passed is used while making the connection?

With ssl_is_used(); shows only true or false. Is there any other extension or pg_catalog views that shows the root cert used in making connection to the DB ?

If you use ssl-mode=verify-full, then the server certificate is always tested against the root certificate you pass to the client. So the mere fact that the connection was established successfully is proof that your root certificate has been used.


Cannot run 'uuid-ossp' functions even when it's activated I want to generate a v4 uuid in PostgreSQL using the extension uuid-ossp, so in psql I ran:

CREATE EXTENSION "uuid-ossp";

Then I ran SELECT uuid_generate_v1(); or SELECT uuid_generate_v4(); but I always get this error:

ERROR:  function uuid_generate_v1() does not exist
LINE 1: select uuid_generate_v1();
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

So how can I solve this error?

There are several possibilities:

you ran CREATE EXTENSION in a different database (postgres?)
you installed the extension in a schema that is not on the user's search_path.

Use

\dx "uuid-ossp"

in psql to see where the extension is installed.


Can we put an equal sign (=) after aggregate functions in Transact-SQL? I have encountered a script like this:

set @sum = sum = (case when somecol1 is null then DATEDIFF(d,[somecol2],somecol3) else 0 end)

I can't understand the meaning of the equal sign (=) after the second keyword sum. When I run the query it is not showing any errors both with the equal sign and without.

I want to know the purpose of putting an equal sign after the keyword sum. Is that a mistake or not?

Thanks

This is documented in UPDATE (Transact-SQL):

  SET @variable = column = expression sets the variable to the same value as the column. This differs from SET @variable = column, column = expression, which sets the variable to the pre-update value of the column.

In your code example, sum is the (unwise) name of a column, not an aggregate.

db<>fiddle demo


SSIS connection used based on passed value I have an SSIS package that populates a reporting warehouse from a live and test environment.

Currently the package has each step duplicated as the source connection changes between live and test. This is a pain to maintain if changes are required.

I know I can send a variable value to the package via the agent job.

I was wondering if I could cut the package down so that the steps only appear once. The agent job could then have two steps that execute the package. E.g First step execute the package and variable set is the name of the live DB and second step executes the package again but the variable set is the name of the test DB.

Can you dynamically select a connection based on a variable received?

Is there a better method?

Thanks

You want to incorporate Environment Variables into your SSIS Packages. Setup the agent job to use different environment variables.

You can set the variables at each step in your agent job. If you create multiple SSISDB Environments in your project folder they can be use or store different variables for executing your SSIS Packages.

Reference:
https://www.mssqltips.com/sqlservertip/4810/setup-environment-variables-in-sql-server-integration-services/


Replication throw an error after failover mirror partner is down in SQL Server Received an error mentioned below after failover when mirror partner is down.

Error :

  to fail over to a database which is not configured for database
  mirroring. code: 22037, text: 'Invalid connection string
  attributeCannot open database "DBName" requested by the login. The login
  failed.Login failed for user 'domain\user'.The connection attempted to
  fail over to a database which is not configured for database
  mirroring.'.

Scenario :

X is a Principal server and Y is a mirror partner of X
X is a publisher too, Z is a distributor server
Z is added as distributor in X and Y Server. X and Y added as publisher in Z server too
Configured Y as a "PublisherFailoverPartner" in replication log reader agent profile setting
Did failover of X
Thereafter Y became a principal and X became  mirror partner
Break mirror from Y to X or X server is down and unavailable

After above steps performed, replication is started to throw an error. When mirror is ON the replication is working, otherwise it raising an error mentioned.

Can you please suggest me steps to resolve it?

This should work as it did for me:

Set the -failoverpartner using the mirror name in the sql agents for replication.
Fail mirror over to secondary

Make sure replication continues to send commands to subscriber.

Remove mirroring so primary is no longer connected.

Go to distributor, add an alias (sql server alias)
The alias should be the old principal name pointing to the secondary server.

Replication will continue without errors, however jobs (and replication monitor) will show everything under the primary mirror server.


mysql: counting number of tickets which are open per day basis I have a table tabName with the following rows:

id(PK) .. Created(datetime)     Resolved(datetime) Status(new/resolved/open)
1          2015-05-10 00:00:00   1970-01-01 ..       open
2          2015-05-10 ..         1970-01-01 ..       new
3          2015-05-10            2015-05-12          resolved
4          2015-05-11 ..         1970-01-01 ..       open
5          2015-05-11 ..         1970-01-01 ..       new
6          2015-05-11            2015-05-11          resolved
7          2015-05-12 ..         1970-01-01 ..       open
8          2015-05-12 ..         1970-01-01 ..       new
9          2015-05-12            2015-05-12          resolved

NOTE:  1970-01-01 .. is default time for all the tickets which are still open.

I have to frame a query to get the count of tickets still open, date wise. Say like on 10 May I have 3 tickets opened. Noting that for id = 3, resolved date is not 10 but it's closed on 12 May.

I framed a query like

SELECT DATE_FORMAT(Created, '%d-%m') AS createdd, COUNT(1)
FROM tabName
WHERE DATE_FORMAT(Resolved, '%d-%m') > DATE_FORMAT(Created, '%d-%m')
      AND YEAR(Created) = '2015'
GROUP BY createdd;

The above query gets all the tickets whose Resolved date is greater than the date which is considered for the count. This may count newer tickets as well. And also if the older tickets which are not resolved yet is not counted.

Is there any other way to frame the required query? I have been juggling with this query from last 3 days. Not able to find out still.

Updated:
As suggested by Abhik, i have tried in sqlfiddle site.

Here is what i created:

CREATE TABLE test
   (idint,createddatetime,resolveddatetime, status varchar(15), PRIMARY KEY (id));
   INSERT INTO test
      (id,'created','resolved',status`)
VALUES
      (1,'2015-05-10 00:00:00','1970-01-01 00:00:00','open'),

  (2,'2015-05-10 00:00:00','1970-01-01 00:00:00','new'),

  (3,'2015-05-10 00:00:00','2015-05-12 00:00:00','resolved'),

  (4,'2015-05-11 00:00:00','1970-01-01 00:00:00','open'),

  (5,'2015-05-11 00:00:00','1970-01-01 00:00:00','new'),

  (6,'2015-05-11 00:00:00','2015-05-11 00:00:00','resolved'),

  (7,'2015-05-12 00:00:00','1970-01-01 00:00:00','open'),

  (8,'2015-05-12 00:00:00','1970-01-01 00:00:00','new'),

  (9,'2015-05-12 00:00:00','1970-01-01 00:00:00','open')

  ;
  `

When i run

`
select
created_date,
@open:= @open+open_ticket-resolved as open
from(
 select
 date_format(created,'%d-%m') as created_date ,

sum( case when status='open' then 1 else 0 end )+

sum( case when status='new' then 1 else 0 end )+

sum( case when status='resolved' and ( date(resolved) > date(created) or

date(resolved) = date(created)) then 1 else 0 end ) as open_ticket,

sum(case when status = 'resolved' and date(resolved) = date(created) then 1

else 0 end) as resolved

from test

where

YEAR(Created) = '2015'

group by created_date
)x,

(select @open:=0)r order by created_date ; `

I got following output

created_date   open
10-05   3
11-05   5
12-05   8

Here count of still open tickets for 12-05 must be 7. The above query includes ticket id "3" , which is closed on 12-05. So is there any way to exclude this? If i am able to get this right, then i will have full fledged query.

You can use conditional sum for this calculation something as

select
date_format(created,'%d-%m') as created_date ,
sum( case when status='open' then 1 else 0 end )+
sum( case when status='new' then 1 else 0 end )+
sum( case when status='resolved' and date(resolved) > date(created) then 1 else 0 end ) as open
from table_name
where
YEAR(Created) = '2015'
group by created_date ;

Here is a test case

mysql> select * from test ;
+------+---------------------+---------------------+----------+
| id   | created             | resolved            | status   |
+------+---------------------+---------------------+----------+
|    1 | 2015-05-10 00:00:00 | 1970-01-01 00:00:00 | open     |
|    2 | 2015-05-10 00:00:00 | 1970-01-01 00:00:00 | new      |
|    3 | 2015-05-10 00:00:00 | 2015-05-12 00:00:00 | resolved |
|    4 | 2015-05-11 00:00:00 | 1970-01-01 00:00:00 | open     |
|    5 | 2015-05-11 00:00:00 | 1970-01-01 00:00:00 | new      |
|    6 | 2015-05-11 00:00:00 | 2015-05-11 00:00:00 | resolved |
+------+---------------------+---------------------+----------+
6 rows in set (0.00 sec)

mysql> select
    -> date_format(created,'%d-%m') as created_date ,
    -> sum( case when status='open' then 1 else 0 end )+
    -> sum( case when status='new' then 1 else 0 end )+
    -> sum( case when status='resolved' and date(resolved) > date(created) then 1 else 0 end ) as open
    -> from test
    -> where
    -> YEAR(Created) = '2015'
    -> group by created_date ;
+--------------+------+
| created_date | open |
+--------------+------+
| 10-05        |    3 |
| 11-05        |    2 |
+--------------+------+
2 rows in set (0.00 sec)

UPDATE
The count of open is a incremental as per the comment. So the following should do the trick

select
created_date,
@open:= @open+open_ticket-resolved as open
from(
 select
 date_format(created,'%d-%m') as created_date ,
 sum( case when status='open' then 1 else 0 end )+
 sum( case when status='new' then 1 else 0 end )+
 sum( case when status='resolved' and ( date(resolved) > date(created) or date(resolved) = date(created)) then 1 else 0 end ) as open_ticket,
 sum(case when status = 'resolved' and date(resolved) = date(created) then 1 else 0 end) as resolved
 from test
 where
 YEAR(Created) = '2015'
 group by created_date
)x,(select @open:=0)r order by created_date ;

From the above sample data it will have

 +--------------+------+
| created_date | open |
+--------------+------+
| 10-05        |    3 |
| 11-05        |    5 |
+--------------+------+

UPDATE:

Count the previous tickets which got resolved on a given date and deduct that from the current day open ticket, for that you need a left join something as

select
created_date,
@open:= @open+open_ticket-resolved_ticket as open
from(
 select
 date_format(t.created,'%d-%m') as created_date ,
 sum( case when t.status='open' then 1 else 0 end )+
 sum( case when t.status='new' then 1 else 0 end )+
 sum( case when t.status='resolved' and ( date(t.resolved) > date(created) or date(t.resolved) = date(created)) then 1 else 0 end ) as open_ticket,
 coalesce(tot,0) as resolved_ticket
 from test t left join(
    select count(*) as tot, date(resolved) as resolved from test where status='resolved' group by date(resolved)
 )x on date(x.resolved) = date(t.created)
 where
 YEAR(t.created) = '2015'
 group by created_date
)x,(select @open:=0)r order by created_date ;

OUTPUT :

+--------------+------+
| created_date | open |
+--------------+------+
| 10-05        |    3 |
| 11-05        |    5 |
| 12-05        |    7 |
+--------------+------+


Best way to model a database structure to store multidimensional variables? I am working with some NetCDF data which has multidimensional variables.

As far as I'm aware, the fields are not mandatory.
Each variable will have the same fields/attributes.
Each variable represents a measurement of the atmosphere, in time & space. Level represents a pressure level (like a layer of an onion -- a thick layer).

For example, a variable called SO2 (Sulphur Dioxide) may be 4-D: Time, Level (pressure), latitude, longitude.

I have about 14 variables that I need to store.

Data retrieval tendencies

Now, I'm going to need to query this data and ask questions like:

Give me all the rows for variable SO2 that fall between latitude X and longitude Y at time T and level L.
What is the average temperature for time T at level L?
etc...

Current considerations

I could let each row in my DB be an observation and have the columns: type, lat, long, time, level, value.

Or, perhaps create a table for each kind of variable and have the columns: lat, long, time, level, value.

I'm not sure which will be more appropriate, or whether there is a better option, so I'd appreciate some advice.

So you have some basic data.

SELECT timestamp::timestamp, level, lat, long
FROM ( VALUES
  (now(), 7, 1, 9)
)
  AS t(timestamp, level, lat, long);
         timestamp          | level | lat | long
----------------------------+-------+-----+------
 2017-02-08 15:38:54.903155 |     7 |   1 |    9

What you want to do is create a point with lat, and long...

SELECT timestamp::timestamp, level, ST_AsText(point::geography)
FROM ( VALUES
  (now(), 7, 1, 9)
)
  AS t(timestamp, level, lat, long)
CROSS JOIN LATERAL ST_SetSRID( ST_MakePoint(long,lat), 4326 )
  AS point;
         timestamp          | level | st_astext
----------------------------+-------+------------
 2017-02-08 15:38:00.892956 |     7 | POINT(9 1)
(1 row)

Now, we just store that into a table..

CREATE TEMP TABLE foo
AS
SELECT timestamp::timestamp, level, point::geography
FROM ( VALUES
  (now(), 7, 1, 9)
)
  AS t(timestamp, level, lat, long)
CROSS JOIN LATERAL ST_SetSRID( ST_MakePoint(long,lat), 4326 )
  AS point;

  Give me all the rows for variable SO2 that fall between latitude X and longitude Y at time T and level L.

This is not how GIS works, because between changes on a sphere. So instead, just use distance. So instead what we do

SELECT *
FROM foo
WHERE ST_DWithin(
  (ST_MakePoint(long,lat),4326)::geography,
  point,
  1000 -- distance in meters
)
FROM foo;

  What is the average temperature for time T at level L?

For this we would have had to store the temperature, assuming we did -- which we didn't. It'd look like this.

SELECT avg(temp)
FROM foo
WHERE level = l
  AND time BETWEEN start AND finish;

As a side note ST_DWithin will use an index.. (as will level and timestamp if you create the indexes). However, if you're new to GIS you create GIS indexes with GIST and not btree.

CREATE INDEX ON foo USING gist ( point );

For a similar question see this answer.

UPDATE

This file is a complex proprietary raster,

gdalinfo file.nc
Driver: netCDF/Network Common Data Format
Files: file.nc
Size is 512, 512
Coordinate System is `'
Metadata:
  NC_GLOBAL#Conventions=CF-1.6
  NC_GLOBAL#history=2017-02-08 12:30:09 GMT by grib_to_netcdf-2.0.2: grib_to_netcdf /data/data01/scratch/_mars-atls17-95e2cf679cd58ee9b4db4dd119a05a8d-mEMhIr.grib -o /data/data01/scratch/_grib2netcdf-atls00-95e2cf679cd58ee9b4db4dd119a05a8d-lrYLIt.nc -utime
Subdatasets:
  SUBDATASET_1_NAME=NETCDF:"file.nc":aermr01
  SUBDATASET_1_DESC=[45x2x451x900] aermr01 (16-bit integer)
  SUBDATASET_2_NAME=NETCDF:"file.nc":aermr02
  SUBDATASET_2_DESC=[45x2x451x900] aermr02 (16-bit integer)
  SUBDATASET_3_NAME=NETCDF:"file.nc":aermr03
  SUBDATASET_3_DESC=[45x2x451x900] aermr03 (16-bit integer)
  SUBDATASET_4_NAME=NETCDF:"file.nc":aermr04
  SUBDATASET_4_DESC=[45x2x451x900] aermr04 (16-bit integer)
  SUBDATASET_5_NAME=NETCDF:"file.nc":aermr05
  SUBDATASET_5_DESC=[45x2x451x900] aermr05 (16-bit integer)
  SUBDATASET_6_NAME=NETCDF:"file.nc":aermr06
  SUBDATASET_6_DESC=[45x2x451x900] aermr06 (16-bit integer)
  SUBDATASET_7_NAME=NETCDF:"file.nc":aermr11
  SUBDATASET_7_DESC=[45x2x451x900] aermr11 (16-bit integer)
  SUBDATASET_8_NAME=NETCDF:"file.nc":so2
  SUBDATASET_8_DESC=[45x2x451x900] so2 (16-bit integer)
  SUBDATASET_9_NAME=NETCDF:"file.nc":z
  SUBDATASET_9_DESC=[45x2x451x900] geopotential (16-bit integer)
  SUBDATASET_10_NAME=NETCDF:"file.nc":t
  SUBDATASET_10_DESC=[45x2x451x900] air_temperature (16-bit integer)
  SUBDATASET_11_NAME=NETCDF:"file.nc":q
  SUBDATASET_11_DESC=[45x2x451x900] specific_humidity (16-bit integer)
  SUBDATASET_12_NAME=NETCDF:"file.nc":r
  SUBDATASET_12_DESC=[45x2x451x900] relative_humidity (16-bit integer)
Corner Coordinates:
Upper Left  (    0.0,    0.0)
Lower Left  (    0.0,  512.0)
Upper Right (  512.0,    0.0)
Lower Right (  512.0,  512.0)
Center      (  256.0,  256.0)

You can import it by generating PAM files.

gdal_translate -sds file.nc file

Them remove the .aux.xml files with rm *.aux.xml; And, run this to import them all into the database

raster2pgsql file_* mySchema.mytable | psql -d database

This gets some kind of raster into the database. Now you have to start learning the PostGIS GDAL raster functions to see what and how you can work with it.. Using ST_MetaData we can see you have a 12 rasters with 90 bands (with ST_BandMetaData we can see they're 16bit unsigned).

SELECT rid, (metadata).* FROM foo.foo1 CROSS JOIN LATERAL st_metadata(rast) AS metadata;
 rid |     upperleftx     | upperlefty | width | height |      scalex       | scaley | skewx | skewy | srid | numbands
-----+--------------------+------------+-------+--------+-------------------+--------+-------+-------+------+----------
   1 | -0.200000003394614 |       90.2 |   900 |    451 | 0.400000006789228 |   -0.4 |     0 |     0 |    0 |       90
   2 | -0.200000003394614 |       90.2 |   900 |    451 | 0.400000006789228 |   -0.4 |     0 |     0 |    0 |       90
   3 | -0.200000003394614 |       90.2 |   900 |    451 | 0.400000006789228 |   -0.4 |     0 |     0 |    0 |       90
   4 | -0.200000003394614 |       90.2 |   900 |    451 | 0.400000006789228 |   -0.4 |     0 |     0 |    0 |       90
   5 | -0.200000003394614 |       90.2 |   900 |    451 | 0.400000006789228 |   -0.4 |     0 |     0 |    0 |       90
   6 | -0.200000003394614 |       90.2 |   900 |    451 | 0.400000006789228 |   -0.4 |     0 |     0 |    0 |       90
   7 | -0.200000003394614 |       90.2 |   900 |    451 | 0.400000006789228 |   -0.4 |     0 |     0 |    0 |       90
   8 | -0.200000003394614 |       90.2 |   900 |    451 | 0.400000006789228 |   -0.4 |     0 |     0 |    0 |       90
   9 | -0.200000003394614 |       90.2 |   900 |    451 | 0.400000006789228 |   -0.4 |     0 |     0 |    0 |       90
  10 | -0.200000003394614 |       90.2 |   900 |    451 | 0.400000006789228 |   -0.4 |     0 |     0 |    0 |       90
  11 | -0.200000003394614 |       90.2 |   900 |    451 | 0.400000006789228 |   -0.4 |     0 |     0 |    0 |       90
  12 | -0.200000003394614 |       90.2 |   900 |    451 | 0.400000006789228 |   -0.4 |     0 |     0 |    0 |       90


Oracle: Save PDB state for all instances in RAC environment I found out the following two contradicting statements in the Oracle DBA guide.

  For an Oracle RAC CDB, you can use the instances clause in the pdb_save_or_discard_state clause to specify the instances on which a PDB's open mode is preserved in the following ways: ...

Then it goes on to say this :

  For a PDB in an Oracle RAC CDB, SAVE STATE and DISCARD STATE only affect the mode of the current instance. They do not affect the mode of other instances, even if more than one instance is specified in the instances clause.

How do I save the PDB state in all instances in a RAC environment without connecting to all of them seperately?

Does the srvctl utlity does this automatically when I am using it to bounce the CDB as below:

srvctl stop db -d cdb_name
srvctl start db -d cdb_name

Depends on where you use the command.

It works on all instances from the root container:

srvctl start database -db wagon

SQL> select inst_id, con_id, name, recovery_status, open_mode from gv$pdbs where name = 'PDB1';

   INST_ID     CON_ID NAME       RECOVERY OPEN_MODE
---------- ---------- ---------- -------- ----------
         2          3 PDB1       ENABLED  MOUNTED
         1          3 PDB1       ENABLED  MOUNTED

SQL> alter pluggable database pdb1 open instances=all;

Pluggable database altered.

SQL> select inst_id, con_id, name, recovery_status, open_mode from gv$pdbs where name = 'PDB1';

   INST_ID     CON_ID NAME       RECOVERY OPEN_MODE
---------- ---------- ---------- -------- ----------
         1          3 PDB1       ENABLED  READ WRITE
         2          3 PDB1       ENABLED  READ WRITE

SQL> alter pluggable database pdb1 save state instances=all;

Pluggable database altered.

srvctl stop database -db wagon; srvctl start database -db wagon

SQL> select inst_id, con_id, name, recovery_status, open_mode from gv$pdbs where name = 'PDB1';

   INST_ID     CON_ID NAME       RECOVERY OPEN_MODE
---------- ---------- ---------- -------- ----------
         1          3 PDB1       ENABLED  READ WRITE
         2          3 PDB1       ENABLED  READ WRITE

But it does not work on all instances when issued from the PDB itself:

SQL> alter session set container=pdb1;

Session altered.

SQL> alter pluggable database pdb1 close instances=all;

Pluggable database altered.

SQL> alter pluggable database pdb1 save state instances=all;

Pluggable database altered.

srvctl stop database -db wagon; srvctl start database -db wagon

SQL> select inst_id, con_id, name, recovery_status, open_mode from gv$pdbs where name = 'PDB1';

   INST_ID     CON_ID NAME       RECOVERY OPEN_MODE
---------- ---------- ---------- -------- ----------
         2          3 PDB1       ENABLED  READ WRITE
         1          3 PDB1       ENABLED  MOUNTED


How to add setParameter in mongod config? When I am using setParameter in mongod config, I start mongod with error:

Unrecognized option: setParameter = enableLocalhostAuthBypass=1

How can I use setParameter from config file ?

P/S: I am using YAML config file format.

Here's a working example that I just tested using 2.6.6 - I specified two parameters just to show how it is done for reference:

storage:
    dbPath: "/data/db/test"
systemLog:
    destination: file
    path: "/data/db/test/mongodb.log"
processManagement:
    fork: true
setParameter:
    enableLocalhostAuthBypass: 1
    enableTestCommands: 1


index not being used I have an exact same myisam table on master and slave. The indexes are not being used on Master server.

mysql> explain select count(date_time) from ox_data_summary_ad_hourly where  date_time = '2012-03-08 00:00:00' \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: ox_data_summary_ad_hourly
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 32212292
        Extra: Using where
1 row in set (0.00 sec)

The explain plan on the slave:

mysql> explain select count(date_time) from ox_data_summary_ad_hourly where  date_time = '2012-03-08 00:00:00' \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: ox_data_summary_ad_hourly
         type: ref
possible_keys: ox_data_summary_ad_hourly_date_time
          key: ox_data_summary_ad_hourly_date_time
      key_len: 8
          ref: const
         rows: 156769
        Extra: Using index
1 row in set (0.00 sec)

The master is 5.0 while slave is on 5.5 version if that matters.

Does it mean the index file on master is corrupt?

The usual rule of thumb for MySQL Query Optimizer is this:

If the EXPLAIN plan has to read more than 5% of the table rows via the index, the index is dismissed (ruled out) and a full table scan is performed.

In the case of the Slave, 156769 out of 32212292 rows are to be read. That's 0.4867%. Hence, the index is used.

With regard to the Master, it is very disturbing that the MySQL Query Optimizer ruled out indexes. As @ypercube commented, perhaps there is no index.

You should make sure by running SHOW CREATE TABLE ox_data_summary_ad_hourly\G on both Master and Slave. If they are different, please post both outputs in the question.

Also, please run SELECT COUNT(1) FROM ox_data_summary_ad_hourly; on both Master and Slave and post these counts


MongoDB Disk Usage with Capped Collections I am using MongoDB using C#.

I am  currently using only a single server.

Since I have to create Table events for MongoDB collections I have created a  single replica set(primary). And using oplog.rs table.

I have created only one Database :  MyDatabase
I also have a capped collection in this Database, and some 5 or 6 tables with very less data.

The Issue here MongoDB size become so huge. There is not much data present , the  data wont be more than 500MB but my MongoDb took 20GB data of my Harddisk.

When I checked inside data\db folder

there is 6 copies of my database MyDatabase has been created automatically. Each of which consuming 2GB

MyDatabase.0
MyDatabase.1
MyDatabase.2
MyDatabase.3
MyDatabase.4
MyDatabase.5

and

local.0
local.1
local.2
local.3
local.4
local.5

In the Overview it shows understorage
the oplog allocated 8.5GB
and the capped collection 4.7 GB

The confusion here is No data more than 500MB is present in the Database but still mongoDB took 20GB.

Am I doing something wrong here with the settings. Please help me in this

Thanks

You have basically allocated space by defining two capped collections, the oplog and the second capped collection in MyDatabase.

Unless you specify the oplogSize, the oplog will be allocated at 5% of free space on the volume containing the MongoDB data (so I am guessing you had ~170GB free).

The second capped collection size would have been defined by you, and then the _id index would be created and grown in addition to the initial allocation as you added data.  The files also contain the data from the other collections, as you mentioned.

The key thing to remember here, is that capped collections pre-allocate the entire amount specified in advance and never grow or shrink.  Besides the indexes that will be added on user defined capped collections (the oplog has no indexes), the capped collection will remain the same size regardless of how much data you put into it, whether that is 500MB or 500TB - think of it as a fixed-size circular buffer.

If you want to see this in action, try creating another capped collection of, say, 500MB in a different database called foo, you will end up with foo.0 through foo.4 (possibly foo.5 depending on the version) and have 960MB (or 1984MB, again depending on version) of files on disk. That represents the minimum needed to contain a capped collection of that size.


Is it possible to modify postgresql.conf using pg_ctl? I am programmatically starting PostgreSQL instances using pg_ctl init and pg_ctl start. I need to modify the configuration in postgresql.conf (specifically listen_addresses, and maybe some other values) and am trying to find a way to do this using pg_ctl, if possible. The documentation for pg_ctl and initdb make no mention of this. The section on Setting Parameters states that I can pass parameters to the postgres command, but in my case I am using pg_ctl to start the server process. Is this possible with pg_ctl or other command line tool included with PostgreSQL 9.5?

Reading the documentation page for pg_ctl carefully shows you what happens with the stuff you pass to it using -o:

  -o options

  Specifies options to be passed directly to the postgres command; multiple option invocations are appended.

So you can, for example, do the following:

pg_ctl -D {your data directory} -o "-c listen_addresses='*'" start

where the whole double-quoted expression is passed to postgres (for details, see its own documentation).

As Craig Ringer pointed out in a comment, it might be easier and, more importantly, has a lasting effect if you change these parameters using ALTER SYSTEM.


Actual Query Plan Estimated Cost and dm_exec_query_stats worker time do not gel I cleared my stats and ran my query. The actual execution plan has a total estimated cost of 0.61. I used the total_worker_time column from the dm_exec_query_stats dmv to calculate an average of 20858 microseconds of CPU time:

(SUM(query_stats.total_worker_time) / SUM(query_stats.execution_count))

The plan recommended an index and I created that index. I cleared my stats and I ran the query again. This time the plan has a total estimated cost of 0.37 at the top level. I checked the dm_exec_query_state dmv again and now the average CPU time is 51536 microseconds.

I was expecting the worker time to be around half, not over double! Am I missing something here?  Why is the improvement to the query plan not reflected in the exec query stats?  The two plans are uploaded here:

Plan1

Plan2

The estimated cost is a unit-less measurement for the query cost as estimated by the SQL Server query optimizer. Just because query A has a lower estimated cost than query B does not mean that query A will be more efficient than query B. The query optimizer makes assumptions about your hardware, current workload, and data that may not exactly match your system. There may be model limitations or SQL Server may not have access to perfect information. Even on a well-configured server with well-written queries you will not almost certainly not see the estimated query cost correlate exactly with CPU time. Keep in mind that the query cost includes more factors than just CPU time as well.

The estimated cost that you see in your actual query plan is actually still an estimate. It will have the same value as the estimated cost from an estimated plan if the plan shape does not change due to a recompile. Consider the following simple query:

SELECT t1.high AS high, t2.high AS high2
FROM master..spt_values t1
CROSS JOIN master..spt_values t2
OPTION (FAST 1);

For the estimated plan I get a total estimated cost of 0.0065704. For the actual plan I still have an estimated cost of 0.0065704 even 6431296 rows were returned instead of the single estimated row. The actual plan was uploaded to Paste the Plan if you want to take a look.

If I had to guess why your query was slower I would start with the index spool at node 42:

It's odd that your query now has an index spool after you added an index. You may have created an index with the key columns in the wrong order. Look at the code in fn_get_samples and evaluate if your index is truly the best one. Sometimes the indexes suggested to you by SSMS may suggest a poor ordering for the key columns. You could also consider adding the NO_PERFORMANCE_SPOOL hint to your query but that's probably not the right corrective action to take.


Table order in Join Query If I say:

Table1 left join Table2

is it the same as saying?:

Table2 right join Table1

In other words, should I expect to get the same results from 2 identical queries where the only thing that is changed is which table is written first and whether a left or right join is used (following the same pattern I have described above?)

Yes, the results will be the same.  Take a look at this as an example:

if exists(select * from sys.tables where name = 'T1')
    drop table T1
go
if exists(select * from sys.tables where name = 'T2')
    drop table T2
go

create table T1
(
    id int not null,
    someText varchar(100) not null
)
go

insert into T1
values(1, 'hello'),
(3, 'bye'),
(6, 'what')
go

create table T2
(
    id int not null,
    someText varchar (100) not null
)
go

insert into T2
values(2, 'hi'),
(3, 'ciao'),
(4, 'no')
go

select *
from T1
left join T2
on T1.id = T2.id

select *
from T2
right join T1
on T2.id = T1.id

The output will have the same exact fields (mind you, if you use SELECT * the column order will be different between the queries) with the same exact data.

As a reference, here are the two execution plans:


Best backup strategy for MySQL (MariaDB) replication set My main concern is getting a proper backup, even more so than a temporary outage.  The data is golden.  I've been doing reading through the documentation and a lot of the backup strategies seem to be.

Setup master-slave replica set.
When you want to back up, stop replication on the slave to basically "freeze" it.
Run your mysqldump or w\e you choose.
Restart replication and the slave will eventually catch up.

That's all well and good, but is there a theoretical off-chance that the slave could become corrupt (perhaps replication didn't happen right, or the slave disconnected from the replica set)?  If so, I will unknowingly be taking corrupted or stale backups.  What is the best strategy to avoid this?  The only thing I could think of is:

Stop the database service completely on master and slave.  Pull them out of the replica set.
Edit PHP config files to point to slave server.
Start the slave (essentially the new master) as to avoid downtime during backup.
Start the old master isolated and run your mysqldump
Stop both servers and somehow sync them back up to a consistent state (in case any writes happened on the old slave while the old master was backing up)
Fix your conf files, start the old master as the master and your old slave as the slave.

This seems very convoluted, is there a better solution?  I don't need multi-master.  The reads & writes will happen on one server.  The slave is just for failover purposes.

Replication status and lag are vital monitors you should take care of.  Before start a backup you must know if your slave goes well.

A simple show slave status will show you all needed infos:

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.30.40.61
                  Master_User: replica
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.021934
          Read_Master_Log_Pos: 205924047
               Relay_Log_File: relay-bin.004199
                Relay_Log_Pos: 205924192
        Relay_Master_Log_File: mysql-bin.021934
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 205924047
              Relay_Log_Space: 205924384
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
1 row in set (0.00 sec)

The mains "counters" are Slave_IO_Running and Slave_SQL_Running for replication status and Seconds_Behind_Master for lag (ideally at 0 second).

If you have a slave dedicated to backups (that is a good practice), I recommend you to make a binary copy of  your datadir instead (or in addition) of your mysqldump. The restore will be much more easier and quick. However mysqldump is good if you want to restore a partial backup (especially InnoDB tables) or restore a clean shrinked dataset.

If your are afraid by corrumption or delta between Master and Slaves you can use the Percona tool pt-table-checksum (available in the Percona Toolkit) that "Verify MySQL replication integrity" easily.

Max.


How do I join a parent table to two child tables without duplication? parent table

id
city_name
zip_code

1
new york city
10001

2
los angeles
90001

3
chicago
60007

Customer, child table

id
customer_name
city_id
address

1
alice
1
428 Hartford Lane

2
betty
1
249 George Street

3
cath
3
12112 S Front Ave

4
daph
3
8619 S Wolcott Ave

Restaurant, child table

id
address
city_id

1
256 Roehampton St.
1

2
920  Cherry Camp Road
3

3
7609 Mckinley Ave
2

4
5723 Morgan Ave
2

When using the following query,
    select c.id, c.city_name, c.zip_code, r.id, r.address, r.city_id, cus.id, cus.customer_name, cus.city_id, cus.address
    from restaurant r
    left join city c
        on r.city_id = c.id
     right join customer cus
        on c.id = cus.city_id where c.id = 1;

There will be a duplicate restaurant address entry. How do I ensure that the restaurants and customers are distinct?

id
city_name
zip_code
id
address
city_id
id
customer_name
city_id
address

1
new york city
10001
1
256 Roehampton St.
1
1
alice
1
428 Hartford Lane

1
new york city
10001
1
256 Roehampton St.
1
2
betty
1
249 George Street

***Edit: To add some context, I would like to return a json object of a city which contains its fields as well as a list of customers and a list of restaurants belonging to that particular city.
Is it more efficient to make three separate queries to populate that object, i.e.
select * from city where id = 1;
select * from customer where city_id = 1;
select * from restaurant where city_id = 1;

or
perform one query with two joins, with the expected result as the following, where there are no duplicate restaurants or customers

id
city_name
zip_code
id
address
city_id
id
customer_name
city_id
address

1
new york city
10001
1
256 Roehampton St.
1
1
alice
1
428 Hartford Lane

1
new york city
10001

2
betty
1
249 George Street

Or can I just use a distinct keyword to remove duplicates?

What you're looking to do is denormalize your data into a JSON object. This is usually not a standard thing to do from a relational database, but PostgreSQL has a cool function that makes it possible called row_to_json(). You should be able to distinctify your JSON object using that function accordingly on top of your query's result set.


Create log file with SQL statements for only one database I have two Postgres databases. Can I write in a log file SQL statements only for one of them?

Carefully reading the Error Reporting and Logging documentation, and specially, the section When to Log, you will find the following configuration parameters, that allows you not to log anything:

log_min_messages = 'PANIC'

You should add this parameter to your postgresql.conf file; and (most probably) restart the service.

For the single database whose info you want to log, connect to that database and execute:

ALTER DATABASE database_where_you_want_to_log
    SET log_min_messages = 'WARNING';  /* or whichever level you need */

This allows you to have one default (not to log anything) for most of your databases and one exception for a single one.


Rename all databases when doing a mongorestore? I have a datadump but I need to change the name of every database while restoring them,
if a database is named "customers" it needs to be "customer2", "products" to "products2"
I can rename an individual database but that will take a lot of time to rename each database separately
mongorestore --nsFrom='PetHotel.pets' --nsTo='PetHouse.pets' --nsInclude="PetHouse.*" dump/
how to I rename all database in a dump?

Should be this one:
mongorestore --nsInclude="PetHotel.*" --nsFrom='PetHotel.*' --nsTo='PetHouse.*' dump/

mongorestore --nsInclude='*.*' --nsFrom='$database$.*' --nsTo='$database$2.*' dump/

First test it with option --dryRun
See Change Collections' Namespaces during Restore