How to get the name of the current database from within PostgreSQL? Using \c <database_name> in PostgreSQL will connect to the named database.

How can the name of the current database be determined?

Entering:

my_db> current_database();

produces:

ERROR:  syntax error at or near "current_database"
LINE 1: current_database();

The function current_database() returns the name of the current database:

 SELECT current_database();

It's an SQL function, so you must call it as part of an SQL statement. PostgreSQL doesn't support running functions as standalone queries, and has no CALL statement like some other SQL engines, so you just use SELECT to call a function.


Oracle - how to reference to a field%TYPE using database links? I have this database which includes a couple of schemas. Say I need to access schemaONE from schemaTWO. To do this, I have created a database link named "DB_ONE_LINK".

I am able to access to schemaONE's tables using the following syntax:

SELECT * FROM "Tbl1"@"DB_ONE_LINK";

Now, when creating SPs and functions in schemaONE, I can define variables like this:

tmp_amount "schemaONE"."Tbl1"."Amount"%TYPE;

The question is, how can I reference to a field type in schemaONE using "DB_ONE_LINK"?
something like this:

tmp_amount "Tbl1"."Amount"@"DB_ONE_LINK"%TYPE;

Is it even possible?

You can reference a remote table in a procedure or a package. Here's an example with a loopback link:

SQL> SET SERVEROUTPUT ON
SQL> DECLARE
  2     l dual.dummy@loopback%TYPE;
  3  BEGIN
  4     SELECT * INTO l FROM dual@loopback;
  5     dbms_output.put_line(l);
  6  END;
  7  /

X

PL/SQL procedure successfully completed

I think there are restrictions and you should probably use a workaround such as a local view that references the remote table or a local synonym.

In any case this should remain an exception, obviously the best place to store a procedure is in the database where the data is stored. If you find that a program frequently needs access to several databases, it might be a good idea to merge those databases.


postgres: is not a valid data directory I am trying to bring up a postgres server where the configuration files and the data are in separate places/partitions

postgresql-10 has been installed using the official repository using YUM in Centos 7

And it has been initialised using

/usr/pgsql-10/bin/pg_ctl initdb --pgdata=/var/lib/pgsql/10/data

as user app

What is referenced as PGDATA (configuration) directory is the default one:

# ls -salt /var/lib/pgsql/10/data
total 120
 4 drwxrwxr-x. 19 app app  4096 Nov 10 17:29 .

I would like to use the following directory for data storage:

# ls -salt /usr/ip-spotlight/postgresql/
total 12
4 drwxrwxr-x. 3 postgres  postgres  4096 Nov 10 17:43 .
4 drwx------. 2 postgres  postgres  4096 Nov 10 17:43 data

And it complains about:

2017-11-10 17:51:44.495 CET [81743] FATAL:  "/usr/ip-spotlight/postgresql/data" is not a valid data directory
2017-11-10 17:51:44.495 CET [81743] DETAIL:  File "/usr/ip-spotlight/postgresql/data/PG_VERSION" is missing.

On the other hand, in https://www.postgresql.org/docs/current/static/runtime-config-file-locations.html it says:

  If you wish, you can specify the configuration file names and
  locations individually using the parameters config_file, hba_file
  and/or ident_file. config_file can only be specified on the postgres
  command line, but the others can be set within the main configuration
  file.

My /var/lib/pgsql/10/data/postgresql.conf is configured as:

data_directory = '/usr/ip-spotlight/postgresql/data'
hba_file = '/var/lib/pgsql/10/data/pg_hba.conf'
ident_file = '/var/lib/pgsql/10/data/pg_ident.conf'

Could you please advise how to separate the config files of postgresql and the actual data storage ?

OK, i figured it out:

initialise the db as user postgres

name: "Create a new PostgreSQL database cluster"
become: postgres
command: "/usr/pgsql-{{ postgres_version.major }}/bin/pg_ctl initdb --pgdata={{ postgres_data }}"
ignore_errors: yes

override PGDATA env variable through systemd

name: "Override PGDATA pathname (1/3)"
become: yes
file: path="/etc/systemd/system/postgresql-{{ postgres_version.major }}.service" state=touch owner=root group=root mode=0644
when: dev.platform == "baremetal"

name: "Override PGDATA pathname (2/3)"
become: yes
lineinfile: path="/etc/systemd/system/postgresql-{{ postgres_version.major }}.service" line=".include /lib/systemd/system/postgresql-{{ postgres_version.major }}.service" state=present
when: dev.platform == "baremetal"

name: "Override PGDATA pathname (3/3)"
become: yes
blockinfile:
  path: "/etc/systemd/system/postgresql-{{ postgres_version.major }}.service"
  state: present
  block: |
    [Service]
    Environment=PGDATA={{ postgres_data }}
  when: dev.platform == "baremetal"
  notify:
    reload-systemd


SQL Alert: 020 - SSPI handshake failure. Tracking the cause? I have done my best to find an article that answers this question but I have not had much luck.

I manage the database across three domains. One domain has the ability to access the other two, but without a trusted connection (SQL traffic through firewall using SQL credentials). I have started getting a lot of SSPI handshake failures in my logs (and as such alerts). I know that a percentage of them are my fellow developers forgetting to use SQL Auth and the server legitimately failing to authenticate.

However I also believe that there is an application out there (or an ODBC setting, authenticated excel report, etc) that is causing some of the errors. I have tracked the problem down to our RDP farm, but not to the root application/etc.

My question is: How can I determine the application/remote-user/something that is causing the SSPI error so that I can resolve it once and for-all!

Usually when you get the SSPI notification (or from the error log ), you will see the IP address from which the app\user tried to connect. The IP may be internal or external.

Just launch the command line and type:

NSlookup IP

(using the IP address from the log). This will give you the hostname from which the connection request came.


cannot recover mysql idb file I recently have an hdd failure and I try to restore Griffith (https://github.com/micjahn/Griffith) database from a /var/lib/mysql/griffith file copy. My new computer uses a more recent version of MySQL:

original :  5.7.19-0ubuntu
new :       5.7.20-0ubuntu

I read many a thread about innoDB database recovery and I succeeded to restore many tables but the main table (movies) fails to get restored.

Here is what I do:

create a new database
find table structure with https://recovery.twindb.com/
recreate tables
use for all tables:

ALTER TABLE mytable DISCARD TABLESPACE;

copy backuped files
use for all tables:

ALTER TABLE mytable IMPORT TABLESPACE;

On the last step on the movies table, it fails and mysql server terminates.

After some analysis, I found out that the original database was created with utf8 encoding (twindb script was using latin1). But it does not fix the issue.

I also changed mysql config with:

innodb_force_recovery=6

It helps, doing a select count(*) gives some info, but I cannot dump the table to make a real backup.

In mysql logs I have this error when running the IMPORT TABLESPACE command:

[...many errors and warnings...]
[ERROR] InnoDB: Table `mysql`.`innodb_table_stats` not found.
[ERROR] InnoDB: Fetch of persistent statistics requested for table `griffith`.`movies`
but the required system tables mysql.innodb_table_stats and mysql.innodb_index_stats
are not present or have unexpected structure. Using transient stats instead.
[ERROR] InnoDB: Trying to access page number 1647523377 in space 238, space name
griffith/movies, which is outside the tablespace bounds.
Byte offset 0, len 16384, i/o type read.
If you get this error at mysqld startup, please check that your my.cnf
matches the ibdata files that you have in the MySQL server.
[ERROR] InnoDB: Server exits.

Can someone help me?

Looks like one of the pages in movies.ibd is corrupt, so it can't read it.

I see two options here.

1) There is a script fetch_data.sh that can copy as many records as possible from a corrupt InnoDB table into a MyISAM table. Start MySQL with innodb_force_recovery=6, create a MyISAM table with same structure and use the script to copy records.

2) From the same toolkit use c_parser/stream_parser to fetch records from movies.ibd. You can't import it anyway, because it's corrupt.

Ah, and as a TwinDB author I can suggest one more option :-) - use https://recovery.twindb.com/ to recover movies.ibd. But it's going to cost $$$.


Translating backup size to database size I have a client who gave me a 4 gig datafile from mysqldump that I need to restore somewhere.

Is there a good rule of thumb for translating the size of a backup into the size of the database once it is restored?

I am trying to figure out if I can restore this to an existing MySQL 5.5 server, or if I need to spin up an new one.

You could have asked mysql for all individual table sizes

SELECT table_schema,table_name,data_length,index_length
FROM information_schema.tables
WHERE table_schema NOT IN ('information_schema','mysql');

Summary by Storage Engine

SELECT IFNULL(B.engine,'Total') "Storage Engine",
CONCAT(LPAD(REPLACE(FORMAT(B.DSize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Data Size",
CONCAT(LPAD(REPLACE(FORMAT(B.ISize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Index Size",
CONCAT(LPAD(REPLACE(FORMAT(B.TSize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Table Size" FROM
(SELECT engine,SUM(data_length) DSize,SUM(index_length) ISize,
SUM(data_length+index_length) TSize FROM information_schema.tables
WHERE table_schema NOT IN ('mysql','information_schema','performance_schema')
AND engine IS NOT NULL GROUP BY engine WITH ROLLUP) B,(SELECT 3 pw) A
ORDER BY TSize;

Summary by Database

SELECT DBName,CONCAT(LPAD(FORMAT(SDSize/POWER(1024,pw),3),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Data Size",
CONCAT(LPAD(FORMAT(SXSize/POWER(1024,pw),3),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Index Size",
CONCAT(LPAD(FORMAT(STSize/POWER(1024,pw),3),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Total Size"
FROM (SELECT IFNULL(DB,'All Databases') DBName,SUM(DSize) SDSize,
SUM(XSize) SXSize,SUM(TSize) STSize FROM (SELECT table_schema DB,
data_length DSize,index_length XSize,data_length+index_length TSize
FROM information_schema.tables WHERE table_schema NOT IN
('mysql','information_schema','performance_schema')) AAA
GROUP BY DB WITH ROLLUP) AA,(SELECT 3 pw) BB ORDER BY (SDSize+SXSize);

Summary by Database/Storage Engine

SELECT Statistic,DataSize "Data Size",IndexSize "Index Size",TableSize "Table Size"
FROM (SELECT IF(ISNULL(table_schema)=1,10,0) schema_score,
IF(ISNULL(engine)=1,10,0) engine_score, IF(ISNULL(table_schema)=1,
'ZZZZZZZZZZZZZZZZ',table_schema) schemaname,
IF(ISNULL(B.table_schema)+ISNULL(B.engine)=2,
"Storage for All Databases",IF(ISNULL(B.table_schema)+ISNULL(B.engine)=1,
CONCAT("Storage for ",B.table_schema),CONCAT(B.engine," Tables for ",
B.table_schema))) Statistic,
CONCAT(LPAD(REPLACE(FORMAT(B.DSize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') DataSize,
CONCAT(LPAD(REPLACE(FORMAT(B.ISize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') IndexSize,
CONCAT(LPAD(REPLACE(FORMAT(B.TSize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') TableSize FROM (SELECT table_schema,engine,
SUM(data_length) DSize,SUM(index_length) ISize,SUM(data_length+index_length) TSize
FROM information_schema.tables WHERE table_schema NOT IN
('mysql','information_schema','performance_schema') AND engine IS NOT NULL
GROUP BY table_schema,engine WITH ROLLUP) B,(SELECT 3 pw) A) AA
ORDER BY schemaname,schema_score,engine_score;

Please send these queries to your IT people and have them email the results back

Please keep in mind that mysqldumps do not contain indexes. A mysqldump is simply a logical representation of the data plus the commands and directive to crate the table, load the table, and make indexes. What get generated physically upon restore can be known by these queries before launching the mysqldump.


Mongodb - see when a database was dropped I want to know if there is a way to see when a database was dropped.
Is there an entry in a log file or some other way to see when this occurred

If the drop command ran slowly, then it will be recorded in the logs (by default >100ms), otherwise the only record of it will be in the oplog (assuming you are running a replica set, even a single node replica set) and that is assuming that it did not occur so far in the past that it has "fallen out" of the oplog (which is a capped collection).

NOTE: Before running queries against your oplog, be aware that any such queries will be table scans, and that running such a query will potentially be slow, especially when run on an active replica set with a large oplog.  If you have a secondary available, you may wish to use that for this type of query rather than add load to your primary.

With that said, let's show an example.  We will call the database "foo", drop it and show how you would search the oplog for evidence of the drop.  From the mongo shell your query would look like this:

use local;
db.oplog.rs.find({ns : "foo.$cmd",  "o" : { "dropDatabase" : 1 }})

And the result, if found, would look like this:

{
        "ts" : Timestamp(1412246712, 1),
        "h" : NumberLong("-6606042550253448275"),
        "v" : 2,
        "op" : "c",
        "ns" : "foo.$cmd",
        "o" : {
                "dropDatabase" : 1
        }
}

After that, the only other place to gather such evidence would be the filesystem, since the files are unlinked/deleted when the DB is dropped.  There are plenty of answers on how to do that (and the potential problems) - I've used this one successfully in the past.


What will be the best data model for membership based websites? I am building a paid membership based website where user can signup for different kind of membership. Membership will essentially enable or disable certain pieces of functionality in the website. A user can upgrade his membership.

What I am looking for is a datamodel that will capture the

Payment information
Any upgrades done
Next Due dates

etc...

Thanks

Check out the free data models at databaseanswers.org for inspiration.

Once you've attempted something yourself, we can help fine tune it.


Adding ID in second Table I have two table one is photos and another one is photo_detail, this is how i have created both tables:

/* creating photos table */
CREATE TABLE photos(
->  pid INT(10) NOT NULL AUTO_INCREMENT,
->  purl VARCHAR(200) NOT NULL,
->  uid INT(5) NOT NULL,
->  time TIMESTAMP NOT NULL,
->  title VARCHAR(80) NOT NULL,
->  PRIMARY KEY (pid)
->  )
->  ENGINE = InnoDB DEFAULT CHARSET = utf8;

/* creating photo_detail table */

CREATE TABLE photodetail(
->  pid INT(6) NOT NULL,
->  ptext TEXT NOT NULL,
->  location VARCHAR(40) NOT NULL,
->  FOREIGN KEY (pid) REFERENCES photos(pid)
->  )
->  ENGINE = Innodb DEFAULT CHARSET = utf8;

My question is how i can add the automatically incremented ID from photos table to photo_detail table  while storing the photo? i am using PHP language! any suggestion on this table design?

You can use LAST_INSERT_ID() to get the inserted id for example if you insert a row in your photos table and you need that auto incremented id for the detail table you can do so

INSERT INTO photos (purl, uid,`time`,title)
VALUES ( 'test',1,1111,'test');
INSERT INTO photodetail (pid, ptext, location)
VALUES (LAST_INSERT_ID(), 'test', 'test');

Fiddle Demo


How to make it impossible for a postgres user to delete databases? What yould be the sql statement (that the user postgres will execute) so it will be impossible for the postgres user user1 to delete (drop) databases?

Or can I add a rule into some config file?

In PostgreSQL, only the owner of a database can drop a database. (Superusers can drop databases, but that's a different issue.) So changing the owner is the most direct way to prevent user1 from dropping any databases.

Fix that with ALTER DATABASE.

ALTER DATABASE name OWNER TO new_owner

  To alter the owner, you must own the database and also be a direct or
  indirect member of the new owning role, and you must have the CREATEDB
  privilege. (Note that superusers have all these privileges
  automatically.)

To give specific permissions back to user1, use GRANT.  You're probably looking for something like

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA schema_name TO user1;

But read the documentation for GRANT first. You might need WITH GRANT OPTION, permissions on sequences, something less than ALL PRIVILEGES, and so on.


SSMS Intellisense spamming error log I'm seeing thousands of rows in our error log about failed logins:

  Error: 18456, Severity: 14, State: 38.
  Login failed for user 'Ourcompany\myaccount'.
  Reason: Failed to open the explicitly specified database 'mydatabase'. [CLIENT: 10.nn.nn.nn]

Some of our users do not have access to all databases on the server.
When I switch off Intellisense in SSMS -> Tools -> Options -> Text editor -> Transact-SQL -> IntelliSense, the spamming stops.

Is this a bug in SSMS?
Are there ways around it (apart from granting access to all databases)?

Have users update SSMS. This was a bug in an earlier release of SSMS2016. Earlier this year I had the same issue, but only with some users. Those users had not updated their SSMS. Once they did, issue went away. Unfortunately I do not remember which version had this issue.


If I understood your question right , you'll need something like

select bs.key , coalesce(w.walls,0) + coalesce(f.floor,0) + coalesce(r.roof,0) as final_value
from building_specs bs
left join spec_value w on w.value= bs.wallsId
left join spec_value f on f.value= bs.floorId
left join spec_value r on r.value= bs.roofId

Output matches 701 and 703 from the question , but different for 702
(4.8 +10.1+3.6 != 12.2)


PostgreSQL - Return type and EXECUTE QUERY My function returns a setof composite type, however the RETURN EXECUTE seems to return some other unknown type:

create type core.search_result as (
  user_id uuid,
  country core.country,
  ethnicity core.ethnicity,
  is_smoker core.smoking_status,
  existing_conditions varchar[]
);

create or replace function core.search_samples(
  country core.country,
  ethnicity core.ethnicity,
  is_smoker core.smoking_status,
  existing_conditions varchar[]
) returns setof core.search_result as $$
  declare
   _where text;
   _sql text := '
     select (
       core.user.id,
       core.user.country,
       core.health_wellness.ethnicity,
       core.health_wellness.is_smoker,
       core.health_wellness.existing_conditions
     )::core.search_result
     from
       core.user
     join
       core.health_wellness
     on
       (core.user.id = core.health_wellness.user_id)';
    results core.search_result[];
  begin
    _where := concat_ws(
      ' AND ',
      CASE WHEN country IS NOT NULL THEN 'core.user.country = $1' END,
      CASE WHEN ethnicity IS NOT NULL THEN 'core.health_wellness.ethnicity = $2' END,
      CASE WHEN is_smoker IS NOT NULL THEN 'core.health_wellness.is_smoker = $3' END
    );
    raise notice '_where: %', _where;
    IF _where <> '' THEN
      _sql := _sql || ' WHERE ' || _where;
      return query EXECUTE _sql
      USING $1, $2, $3;
    end if;
    raise notice 'all input params null';
  end;
$$ language plpgsql stable;

When I try to run the function:

select core.search_samples('United Kingdom', null, null, null);

I get the following error:

NOTICE:  _where: core.user.country = $1
ERROR:  structure of query does not match function result type
DETAIL:  Returned type core.search_result does not match expected type uuid in column 1.
CONTEXT:  PL/pgSQL function core.search_samples(core.country,core.ethnicity,core.smoking_status,character varying[]) line 29 at RETURN QUERY

It's a mismatch of nesting levels.

The return type of the function is declared as:

returns setof core.search_result

core.search_result being the composite type you created earlier. This is equivalent to the more explicit declaration:

RETURNS TABLE (
  user_id uuid,
  country core.country,
  ethnicity core.ethnicity,
  is_smoker core.smoking_status,
  existing_conditions varchar[]
)

Then you return values with:

select (
       core.user.id,
       core.user.country,
       core.health_wellness.ethnicity,
       core.health_wellness.is_smoker,
       core.health_wellness.existing_conditions
     )::core.search_result
     from ...

... which would seem to make sense on a first glance. But the expression is exactly 1 column of type core.search_result, which Postgres tries to fit into the first column of the return type (user_id uuid). Hence the DETAIL information in your error message:

Returned type core.search_result does not match expected type uuid in column 1

You need to return 5 separate columns.

Just remove parentheses & cast:

select core.user.id,
       core.user.country,
       core.health_wellness.ethnicity,
       core.health_wellness.is_smoker,
       core.health_wellness.existing_conditions
     from ...

You may have to add casts to individual columns if data types don't match. One way to avoid that would be to cast the whole ROW type and decompose to separate columns again:

select ((
       core.user.id,
       core.user.country,
       core.health_wellness.ethnicity,
       core.health_wellness.is_smoker,
       core.health_wellness.existing_conditions
     )::core.search_result).*
     from ...

But I'd rather avoid that complication.

Aside:
Once you have fixed the exception, raise notice at the very end of your function is reached unconditionally and incorrect. RETURN QUERY ... does not end a function. Only RETURN; does. Rearrange to something like:

IF _where <> '' THEN
  _sql := _sql || ' WHERE ' || _where;
  return query EXECUTE _sql
  USING $1, $2, $3;
ELSE                                     -- !!
  raise notice 'all input params null';  -- !!
END IF;

Details:

PostgreSQL stored procedure to return rows or empty set


The message you include from your db2diag.log shows that there is a potential space problem (i.e. the file system holding your data is filling up).  The monitoring data here shows that you are ranging between 89% and 94% over a 90 minute period with increases and decreases – this is probably associated with system temporary tables in the database.  It may be worthwhile to increase the size of the filesystem(s) holding the data since you're getting very close to running out of space.

This space utilization, however, has nothing to do with how much I/O activity (IOPS) the database is generating.

Generally when you have an I/O problem where you see a much different usage pattern than normal.  You mention that normally there the system is generating 300 IOPS, but lately it's been generating 5000 IOPS.

Assuming that the I/O activity can be traced to the file system(s) holding DB2 data, then you need to monitor the database during these periods of high I/O to find out what queries are active and are causing lots of physical I/O, and then do some investigation to figure out why.  You can use the db2top utility as well as many other monitoring tools to figure this out.

At that point it's a tuning exercise.  Are your bufferpools too small?  Was an index dropped?  Or is the cause a new query that needs to be reviewed / tuned?


Get postgresql exact log file name with sql I get current log file by using SQL without getting in server via ssh. So I use a query like the one below;

SELECT pg_read_binary_file(
  concat_ws('/',
    current_setting('log_directory'),
    'postgresql-Fri.log'
  )
);

Log files are not too big so i am fine with that. The thing is, I cannot use this query in a generic way. Is there a way to get 'postgresql-Fri.log' from my log_filename setting which is currently postgresql-%a.log but the format can differ?

There is now a pg_current_logfile() function returning this information:

  Primary log file name, or log in the requested format, currently in
  use by the logging collector.

but it's a new feature of PostgreSQL 10, which you probably don't run since it hasn't yet got out as a General Availability release (it's still Release Candidate at the moment).

With previous versions, you're pretty much on your own to figure out the current name, either by interpreting log_filename, or by finding the latest modified, for instance with a combination of pg_ls_dir and pg_stat_file:

SELECT file, -- should be the latest logfile
  (pg_stat_file(current_setting('log_directory')||'/'||file)).modification
  FROM  pg_ls_dir(current_setting('log_directory')||'/') as list(file)
  ORDER BY 2 DESC
  LIMIT 1;

This can fail if the server's clock goes backward or if non-log files are dumped into the same directory as the log files.


Query that only uses index takes long time to execute I have a table with ~69,000 rows. I have this very simple query:

-- SET STATISTICS TIME ON
SELECT ID, FirstName, LastName FROM Customers
-- SQL Server Execution Times: CPU time = 31 ms, elapsed time = 901 ms.

I also have this non clustered index:

CREATE NONCLUSTERED INDEX IX1 ON Customers (LastName ASC, FirstName ASC, ID ASC) WITH (
PAD_INDEX = OFF,
STATISTICS_NORECOMPUTE = OFF,
SORT_IN_TEMPDB = OFF,
DROP_EXISTING = OFF,
ONLINE = OFF,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON
) ON PRIMARY

The execution plan indicates that this index is being used. Yet the query takes ~1 second to return 69k rows which seems odd to me. I am wondering if 1 second is an acceptable amount of time or is it possible to improve this query.

Edit 1:

  Microsoft SQL Server 2012 - 11.0.2100.60 (X64)
   Enterprise Edition (64-bit) on Windows NT 6.1  (Build 7601: Service Pack 1)

It runs on a dedicated Dell PowerEdge server with 16GB RAM and two SCSI hard drives.

Edit 2:

I get almost similar timings (~1 second) even if I run the query on the server itself using named pipes/shared memory/TCP connections.

It may just be taking a while to return to the client.  How long does it take if you run as SELECT INTO, eg

SELECT ID, FirstName, LastName
INTO #tmp
FROM Customers


Update table from another database I have two databases database_a & database_b with the exact same structure, but different email data. How do I update all of database_b's email field with the ones from database_a based on the clients id number?

CREATE `clients` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `firstname` text NOT NULL,
  `lastname` text NOT NULL,
  `companyname` text NOT NULL,
  `email` text NOT NULL,
  `address1` text NOT NULL,

Use an UPDATE JOIN

UPDATE database_a.clients A INNER JOIN database_b.clients B
USING (id) SET B.email = A.email;

or

UPDATE database_a.clients A INNER JOIN database_b.clients B
ON A.id = B.id SET B.email = A.email;

Give it a Try !!!


Pre-checking execute privs for SPs We have a number of DBs where EXECUTE privs are required on a SP by SP basic. We have other DBs where we don't need to do this at all.

In the latter case, I understand that this is a blanket GRANT, but is there anyway to tell up front whether the blanket grant has been applied? For the blanket grant I don't see any users set up under the Permissions page in SSMS for any of the SPs in question and yet it just works.

If there are no users here I don't know whether I need to specify users or assume a blanket grant.

Is there anyway to tell up front whether the blanket grant has been
  applied? For the blanket grant I don't see any users set up under the
  Permissions page in SSMS for any of the SPs in question and yet it
  just works.

Below is a TSQL method to find all explicit DB object permissions and members of fixed or custom DB roles per each DB you run it against. Just search the results for the value of the role name you need to confirm the user is a member of beforehand and that should suffice for your need.

/*
Script DB Level Permissions v2.1
Source: http://www.sqlservercentral.com/scripts/Security/71562/
*/
DECLARE @sql VARCHAR(2048)
    ,@sort INT

DECLARE tmp CURSOR
FOR
/*********************************************/
/*********   DB CONTEXT STATEMENT    *********/
/*********************************************/
SELECT '-- [-- DB CONTEXT --] --' AS [-- SQL STATEMENTS --]
    ,1 AS [-- RESULT ORDER HOLDER --]

UNION

SELECT 'USE' + SPACE(1) + QUOTENAME(DB_NAME()) AS [-- SQL STATEMENTS --]
    ,1 AS [-- RESULT ORDER HOLDER --]

UNION

SELECT '' AS [-- SQL STATEMENTS --]
    ,2 AS [-- RESULT ORDER HOLDER --]

UNION

/*********************************************/
/*********     DB USER CREATION      *********/
/*********************************************/
SELECT '-- [-- DB USERS --] --' AS [-- SQL STATEMENTS --]
    ,3 AS [-- RESULT ORDER HOLDER --]

UNION

SELECT 'IF NOT EXISTS (SELECT [name] FROM sys.database_principals WHERE [name] = ' + SPACE(1) + '''' + [name] + '''' + ') BEGIN CREATE USER ' + SPACE(1) + QUOTENAME([name]) + ' FOR LOGIN ' + QUOTENAME([name]) + ' WITH DEFAULT_SCHEMA = ' + QUOTENAME([default_schema_name]) + SPACE(1) + 'END; ' AS [-- SQL STATEMENTS --]
    ,4 AS [-- RESULT ORDER HOLDER --]
FROM sys.database_principals AS rm
WHERE [type] IN (
        'U'
        ,'S'
        ,'G'
        ) -- windows users, sql users, windows groups

UNION

/*********************************************/
/*********    DB ROLE PERMISSIONS    *********/
/*********************************************/
SELECT '-- [-- DB ROLES --] --' AS [-- SQL STATEMENTS --]
    ,5 AS [-- RESULT ORDER HOLDER --]

UNION

SELECT 'EXEC sp_addrolemember @rolename =' + SPACE(1) + QUOTENAME(USER_NAME(rm.role_principal_id), '''') + ', @membername =' + SPACE(1) + QUOTENAME(USER_NAME(rm.member_principal_id), '''') AS [-- SQL STATEMENTS --]
    ,6 AS [-- RESULT ORDER HOLDER --]
FROM sys.database_role_members AS rm
WHERE USER_NAME(rm.member_principal_id) IN (
        --get user names on the database
        SELECT [name]
        FROM sys.database_principals
        WHERE [principal_id] > 4 -- 0 to 4 are system users/schemas
            AND [type] IN (
                'G'
                ,'S'
                ,'U'
                ) -- S = SQL user, U = Windows user, G = Windows group
        )
--ORDER BY rm.role_principal_id ASC

UNION

SELECT '' AS [-- SQL STATEMENTS --]
    ,7 AS [-- RESULT ORDER HOLDER --]

UNION

/*********************************************/
/*********  OBJECT LEVEL PERMISSIONS *********/
/*********************************************/
SELECT '-- [-- OBJECT LEVEL PERMISSIONS --] --' AS [-- SQL STATEMENTS --]
    ,8 AS [-- RESULT ORDER HOLDER --]

UNION

SELECT CASE
        WHEN PERM.STATE <> 'W'
            THEN PERM.state_desc
        ELSE 'GRANT'
        END + SPACE(1) + PERM.permission_name + SPACE(1) + 'ON ' + QUOTENAME(SCHEMA_NAME(obj.schema_id)) + '.' + QUOTENAME(obj.NAME) --select, execute, etc on specific objects
    + CASE
        WHEN cl.column_id IS NULL
            THEN SPACE(0)
        ELSE '(' + QUOTENAME(cl.NAME) + ')'
        END + SPACE(1) + 'TO' + SPACE(1) + QUOTENAME(USER_NAME(usr.principal_id)) COLLATE database_default + CASE
        WHEN PERM.STATE <> 'W'
            THEN SPACE(0)
        ELSE SPACE(1) + 'WITH GRANT OPTION'
        END AS [-- SQL STATEMENTS --]
    ,9 AS [-- RESULT ORDER HOLDER --]
FROM sys.database_permissions AS PERM
INNER JOIN sys.objects AS obj ON PERM.major_id = obj.[object_id]
INNER JOIN sys.database_principals AS usr ON PERM.grantee_principal_id = usr.principal_id
LEFT JOIN sys.columns AS cl ON cl.column_id = PERM.minor_id
    AND cl.[object_id] = PERM.major_id
--WHERE usr.name = @OldUser
--ORDER BY perm.permission_name ASC, perm.state_desc ASC

UNION

SELECT '' AS [-- SQL STATEMENTS --]
    ,10 AS [-- RESULT ORDER HOLDER --]

UNION

/*********************************************/
/*********    DB LEVEL PERMISSIONS   *********/
/*********************************************/
SELECT '-- [--DB LEVEL PERMISSIONS --] --' AS [-- SQL STATEMENTS --]
    ,11 AS [-- RESULT ORDER HOLDER --]

UNION

SELECT CASE
        WHEN PERM.STATE <> 'W'
            THEN PERM.state_desc --W=Grant With Grant Option
        ELSE 'GRANT'
        END + SPACE(1) + PERM.permission_name --CONNECT, etc
    + SPACE(1) + 'TO' + SPACE(1) + '[' + USER_NAME(usr.principal_id) + ']' COLLATE database_default --TO <user name>
    + CASE
        WHEN PERM.STATE <> 'W'
            THEN SPACE(0)
        ELSE SPACE(1) + 'WITH GRANT OPTION'
        END AS [-- SQL STATEMENTS --]
    ,12 AS [-- RESULT ORDER HOLDER --]
FROM sys.database_permissions AS PERM
INNER JOIN sys.database_principals AS usr ON PERM.grantee_principal_id = usr.principal_id
--WHERE usr.name = @OldUser
WHERE [perm].[major_id] = 0
    AND [usr].[principal_id] > 4 -- 0 to 4 are system users/schemas
    AND [usr].[type] IN (
        'G'
        ,'S'
        ,'U'
        ) -- S = SQL user, U = Windows user, G = Windows group

UNION

SELECT '' AS [-- SQL STATEMENTS --]
    ,13 AS [-- RESULT ORDER HOLDER --]

UNION

SELECT '-- [--DB LEVEL SCHEMA PERMISSIONS --] --' AS [-- SQL STATEMENTS --]
    ,14 AS [-- RESULT ORDER HOLDER --]

UNION

SELECT CASE
        WHEN PERM.STATE <> 'W'
            THEN PERM.state_desc --W=Grant With Grant Option
        ELSE 'GRANT'
        END + SPACE(1) + PERM.permission_name --CONNECT, etc
    + SPACE(1) + 'ON' + SPACE(1) + class_desc + '::' COLLATE database_default --TO <user name>
    + QUOTENAME(SCHEMA_NAME(major_id)) + SPACE(1) + 'TO' + SPACE(1) + QUOTENAME(USER_NAME(grantee_principal_id)) COLLATE database_default + CASE
        WHEN PERM.STATE <> 'W'
            THEN SPACE(0)
        ELSE SPACE(1) + 'WITH GRANT OPTION'
        END AS [-- SQL STATEMENTS --]
    ,15 AS [-- RESULT ORDER HOLDER --]
FROM sys.database_permissions AS PERM
INNER JOIN sys.schemas s ON PERM.major_id = s.schema_id
INNER JOIN sys.database_principals dbprin ON PERM.grantee_principal_id = dbprin.principal_id
WHERE class = 3 --class 3 = schema
ORDER BY [-- RESULT ORDER HOLDER --]

OPEN tmp

FETCH NEXT
FROM tmp
INTO @sql
    ,@sort

WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT @sql

    FETCH NEXT
    FROM tmp
    INTO @sql
        ,@sort
END

CLOSE tmp

DEALLOCATE tmp

Source


Can I change CITEXT columns to VARCHAR without encountering any surprise difficulties? I went overboard on CITEXT columns on a particularly large table in my application. I would like to back some of these out as it's confusing how to trigger lookups on the desired indexes.

My question is, can I do this without encountering any major difficulties? And if I change this, do I need to rebuild any indexes on those fields?

Will there be any space gains from moving in this direction?

Case-insensitive queries are not necessary on these columns.

I have counts against this table that are predicated on 2 columns, and these counts are taking over an hour. The table has 60 columns.

I am using Postgres 10.6.

I'm primarily interested in whether indexes need to be rebuilt if they contain a column that has changed from CITEXT to VARCHAR.

Will there be any space gains from moving in this direction?

No. citext and text (or varchar) occupy the same space on disk and in RAM.

  Case-insensitive queries are not necessary on these columns.

Then there is no point to use citext.

  I have counts against this table that are predicated on 2 columns, and these counts are taking over an hour. The table has 60 columns.

count() is not affected by citext vs. text (or varchar) at all. "60 columns" might be something to look into. No problem if you actually need all of them - but do you? And are you using proper data types etc.

  I'm primarily interested in whether indexes need to be rebuilt if they contain a column that has changed from CITEXT to VARCHAR.

All indexes involving any of the affected columns have to be rebuilt. (Drop index, change type, recreate index.)

Personally, I stay away from citext after mixed experiences.


How do I force windows to use a specific version of SQLCMD? I have several versions of SSMS installed on my machine. We have legacy servers running several versions of SQL Server, mainly 2008, 2012, and 2014. I use SQLCMD all the time, but it seems to default to the Version 10 version of the command.

  C:\Program Files\Microsoft SQL Server\100\Tools\Binn

I need to use the one from the newer version for some of the new servers that don't have backwards compatibility enabled.

  C:\Program Files\Microsoft SQL Server\110\Tools\Binn

What settings do I need to change so that I don't have to use the full path every time I want to use SQLCMD?

Have you tried updating the PATH variable?

For Windows 10, do the following:

In the File Explorer, right-click on This PC.
In the context menu, click Properties.
In the System window, click the Advanced system settings page on the left-hand side.
In the Advanced Tab, click on  Environment Variables.
In the System Variables window, highlight Path, and click Edit.
In the Edit System Variables window, insert the cursor at the end of the Variable value field.
If the last character is not a semi-colon (;), add one.
After the final semi-colon, type the full path to the file you want to find.

In your case: C:\Program Files\Microsoft SQL Server\110\Tools\Binn

Find the path with 100 in it, and then remove it.

Click OK in each open window.

Word of caution: After doing these steps, you will not be able to auto-find any other exe's that the system was able to find earlier in the older path you just removed.


Most efficient ordering post database design I have posts table that is has post_order column. I store order of each post in it. when I change the order of a row from 25 to 15, I should update all the row from 15 to end. It's good for few rows, But in thousands rows it's worst.

Is there any better design for ordering posts, that is more efficient?

You can change your post_order column to a float and update the value with a calculation using the post_order values from the posts you want to end up between.

Example:

PostID, PostOrder
1       1
2       2
3       3

If you want to move PostID = 3 to be sorted between 1 and 2 it would be

PostID, PostOrder
1       1
3       1.5
2       2

Here is a SQL Fiddle with some code that moves the posts using a stored procedure where you pass in the PostID of the post to be moved and PostID of the post you want to end up behind.

SQL Fiddle

MySQL 5.5.30 Schema Setup:

create table Post
(
  PostID int primary key,
  PostOrder float unsigned not null unique,
  check (PostOrder > 0)
);

insert into Post(PostID,  PostOrder) values (1, 1);
insert into Post(PostID,  PostOrder) values (2, 2);
insert into Post(PostID,  PostOrder) values (3, 3);
insert into Post(PostID,  PostOrder) values (4, 4);
insert into Post(PostID,  PostOrder) values (5, 5);
insert into Post(PostID,  PostOrder) values (6, 6);
insert into Post(PostID,  PostOrder) values (7, 7);
insert into Post(PostID,  PostOrder) values (8, 8);
insert into Post(PostID,  PostOrder) values (9, 9);
insert into Post(PostID,  PostOrder) values (10, 10);

//

create procedure MovePost(MovePostID int, AfterPostID int)
begin

  declare AfterPostOrder float;
  declare NextPostOrder float;

  set AfterPostOrder = (select PostOrder
                        from Post
                        where PostID = AfterPostID);

  if AfterPostOrder is null then
    -- Move first
    set AfterPostOrder = 0;
  end if;

  set NextPostOrder = (select min(PostOrder)
                       from Post
                       where PostOrder > AfterPostOrder);

  if NextPostOrder is null then
    -- Move last
    set NextPostOrder = (select max(PostOrder) + 2
                         from Post);
  end if;

  update Post
  set PostOrder = (AfterPostOrder + NextPostOrder) / 2
  where PostID = MovePostID;

end

Query 1:

call MovePost (7, 3);    -- Move 7 after 3
call MovePost (8, 3);    -- Move 8 after 3
call MovePost (9, null); -- Move 9 first
call MovePost (2, 10);   -- Move 2 after 10

select *
from Post
order by PostOrder

Results:

| POSTID | POSTORDER |
----------------------
|      9 |       0.5 |
|      1 |         1 |
|      3 |         3 |
|      8 |      3.25 |
|      7 |       3.5 |
|      4 |         4 |
|      5 |         5 |
|      6 |         6 |
|     10 |        10 |
|      2 |        11 |


Return 2 rows at a time from SQL Server How can I slice up my table and return and evenly distribute the data?

Because I wanted to slice this up and put it into my 3 combo boxes in a windows form.

Example :

Table1

  ID          Name
   1           a
   2           b
   3           c
   4           d
   5           e

Windows Form Output :

combobox1   combobox2  combobox3
  a             c          e
  b             d

This code should achieve what you want

SELECT [1] AS [Combobox1], [2] AS [Combobox2], [3] AS [Combobox3]
FROM
(
    SELECT [name],
        NTILE(3) OVER (ORDER BY ID) AS [CB]
    FROM #Temp
    WHERE [ID]%2 <> 0
) src
PIVOT
(
    MAX([Name]) FOR [CB] IN ([1], [2], [3])
) pvt
UNION
SELECT [1] AS [Combobox1], [2] AS [Combobox2], [3] AS [Combobox3]
FROM
(
    SELECT [name],
        NTILE(3) OVER (ORDER BY ID) AS [CB]
    FROM #Temp
    WHERE [ID]%2 = 0
) src
PIVOT
(
    MAX([Name]) FOR [CB] IN ([1], [2], [3])
) pvt

Sample output:

Combobox1 Combobox2 Combobox3
--------- --------- ---------
a         c         e
b         d         NULL


Vertical data set to horizontal data set for many columns I have read about pivot function but I am not sure if If it helps me in this situation. Essentially I have to produce my vertical data set horizontally. For instance the first table shows the vertical data set and ideally i would like to convert that into how the second table looks.

Any help would be appreciated!

create table Vertical(FID int ,[SID]int ,OAID int,FN varchar(10),[Row] int)

insert into Vertical(FID ,[SID],OAID ,FN ,[Row] ) values(3184226,9304,82,'8090',1)
insert into Vertical(FID ,[SID],OAID ,FN ,[Row] ) values(3200394,4140,81,'0016',2)
insert into Vertical(FID ,[SID],OAID ,FN ,[Row] ) values(3203126,10465,81,'0426',3)
insert into Vertical(FID ,[SID],OAID ,FN ,[Row] ) values(3136790,10822,81,'0029',4)

    select
        OAID,
        case when FID_1 is null then FID_2 else FID_1 end  as FID_1,
        case when SID_1 is null then SID_2 else SID_1 end as SID_1,
        case when FN_1 is null then FN_2 else FN_1 end as FN_1,

        case when FID_1 is null then FID_3 else FID_2 end  as FID_2,
        case when SID_1 is null then SID_3 else SID_2 end as SID_2,
        case when FN_1 is null then FN_3 else FN_2 end as FN_2,

        case when FID_1 is null then FID_4 else FID_3 end  as FID_3,
        case when SID_1 is null then SID_4 else SID_3 end as SID_3,
        case when FN_1 is null then FN_4 else FN_3 end as FN_3

    from (
    select OAID,
        min([1]) as FID_1,
        min(SID_1) as SID_1,
        min(FN_1) as FN_1,

        min([2]) as FID_2,
        min(SID_2) as SID_2,
        min(FN_2) as FN_2,

        min([3]) as FID_3,
        min(SID_3)  as SID_3,
        min(FN_3) as FN_3,

        min([4] )as FID_4 ,
        min(SID_4) as SID_4 ,
        min(FN_4) as FN_4

    from
    (
    select FID,OAID, [1] as FN_1,[2] as FN_2,[3] as FN_3,[4] as FN_4 ,SID_1,SID_2,SID_3,SID_4,rn_2 from
    (
    select
            FID,
            OAID,
            FN,
            [1] as SID_1,
            [2] as SID_2,
            [3] as SID_3,
            [4]  as SID_4
            ,rn_1
            ,rn_2
    from (select *,ROW_NUMBER() over(order by OAID desc,SID) as rn_1,ROW_NUMBER() over(order by OAID desc,SID) as rn_2 from Vertical )as v
    pivot(min([SID]) for [Row] in([1],[2],[3],[4])) as p

    )as P
    pivot(min([FN]) for rn_1 in([1],[2],[3],[4])) as p

    )as P
    pivot(min([FID]) for rn_2 in([1],[2],[3],[4])) as p

    group by OAID
    )as A


MariaDB: Created_tmp_disk_tables high after start today i looked in the status of my MySQL server which is based on MariaDB and i noticed that
Created_tmp_disk_tables is very high (over 168k). I think this is the reason for my high I/O usage. I try to optimize this by increasing tmp_table_size and max_heap_table_size and some other config parameters.

Now i restarted the server and took a look at the status variables:

Created_tmp_disk_tables 12
Created_tmp_tables      82

The server is running for 20 seconds, but why it has created 12 tmp tables on the hdd? I set max_heap_table_size  and tmp_table_size to 256MB so it's not possible that this is full after 20 seconds.

After 5 Minutes the Status looks like this:

Created_tmp_disk_tables 40
Created tmp tables 3300

The difference is not as big as it was before increasing the RAM. But i can't understand why tmp tables are created on the hdd when enough RAM is avaliable. I think this is not good because it slow down the querys and create high I/O usage. My server also have enough RAM, so that i want to avoid this and make MariaDB to create all tmp tables in the RAM.

I use MariaDB on Debian 7.3

You may have other limiting factors involved. Here are some suggestions:
Additional Buffers
The per-connections buffers you may need to increase includes

join_buffer_size
sort_buffer_size

Queries will demand temp table based on join and sort operations. These may need to be increased.
Improve Queries
Queries that need to do joins and sorts can be reduced if queries had proper indexing. I would suggest that you profile all your queries using pt-query-digest and create whatever indexes that table need to reduce the incidence of temp table creation.
Create a RAM Disk
You should map a chunk of your RAM to be a dedicated RAM disk. Then configure tmpdir to point to it. See my post Is it bad to create many mysql temporary tables simultaneously? on how to set one up.


Read Committed Shared Lock I wanted to know when you are at read committed isolation level and in one session someone started an explicit transaction which has a select statement, say it takes 20 seconds. Can someone from a different session will be able to update the records from that table while the select statement is still executing.
I was discussing this with someone and according to them it will be able to update those records because that's where update locks come into play and gives the exclusive lock to the update while the query is still executing.
I can't find any documentation on this and also don't know how to test this

I can't find any documentation on this

Docs are here.  An S lock is compatible with a U lock, but the UPDATE will reuquire an X lock to actually complete, which is incompatible with an S lock.

and also don't know how to test this

You can always run a a query like
select count(*)
from sales.SalesOrderDetail with (tablock), master..spt_values v, master..spt_values v2, master..spt_values v3

And see if you can update from another session.  The tablock is to ensure that the whole table is locked with an S lock, as it may or may not be with a long-running SELECT.
You'll discover that unless the database has the READ COMMITTED SNAPSHOT option set, the SELECT will block the UPDATE.


postgres database with multiple database object owners and pg_dump with a postgres 11.2 database with multiple database object owners and trying to use the following command as the database owner (not the postgres superuser):

pg_dump  -h hostname -p 5432 -U dbownername dbname --file= dbname_20200406.pg_dump --no-owner --no-privileges --format=p

it fails due to object ownership not being all the same. (I have tested that this is the case by changing all ownership to dbownername and at that point pg_dump succeeds)

is the only way to get this to work is to run it as the postgres superuser?  or are there postgres roles that I can give dbownername to allow them to back up... with out in effect making them another postgres superuser?

If you grant SELECT privileges to all tables to the database owner you should be able to export at least the tables. If not, please give full error message(s).


Oracle - execute DML query as different user I would like to be able to run DML queries (INSERT,UPDATE,DELETE) by executing a procedure on a given schema. Those queries need to change data on different schemas on the same instance. So, basically, I need to run this procedure with all the privileges of a user that it needs to be ran on. I put the

AUTHID CURRENT_USER

clause in the package header as well as

EXECUTE IMMEDIATE ('ALTER SESSION SET CURRENT_SCHEMA =B')

clause in the body of procedure. This works perfectly fine for non-DML/DDL queries like SELECT, unfortunetely when I run the DML procedure there's an error about insufficient privileges. When I grant privileges to user A (the one which the package is stored on) the queries run with no problems. So clearly the procedure is ran as user A (package user) and not user B (user I want to run DML queries on).

Is there anything I can do to make this happen? I want to keep all the code on user A without manually granting any privileges on all the users.

A bit old question but if anybody is interested, there is a way to do it. Take a look at dbms_sys_sql package. It allows you to execute any SQL as any user on the database. It is how APEX does it. But be careful granting execute privileges to this package since it can be used to impersonate anybody on the database and it is very powerful.


Postgresql 11 SSPI authentication failing On windows 2016 in AD setup when I try to use SSPI with postgresql 11 and following settings :
pg_hba.conf:
host    all all 127.0.0.1/32    sspi    map=newmap #IP4

pg_ident.conf:
newmap  SrvSupportPsgr  srvsupportpsgr

log:

Log: no match in usermap "newmap" for user "SrvSupportPsgr" authenticated as "SrvSupportPsgr@domainname"
FATAL: SSPI authentication failed for user "SrvSupportPsgr"

this account has superuser on postgres and local admin + domain user on AD.
I have tried putting usernames in double quotes as well.
thanks

If you are authenticated as "SrvSupportPsgr@domainname", then that is what needs to be in the 2nd column of pg_ident.conf; including the "@domainname" part.  (Or it could be a regexp which matches to that)

Also, pg_ident.conf is case sensitive.  In the third column, "SrvSupportPsgr" is not the same thing as "srvsupportpsgr".


Postgres sometimes uses inferior index for WHERE a IN (...) ORDER BY b LIMIT N We have a PostgreSQL table with ~5 billion rows that has developed a nasty habit of missing the proper indices and doing a Primary Key scan on certain LIMIT operations.

The problem generally manifests on an ORDER BY .. LIMIT .. clause (a common pattern in Django pagination) where the LIMIT is some relatively small subset of the results matched by the index. An extreme example is this:

SELECT * FROM mcqueen_base_imagemeta2
  WHERE image_id IN ( 123, ... )
  ORDER BY id DESC
  LIMIT 1;

where the items in that IN clause are ~20 and total rows matched by the index on image_id is 16.

The EXPLAIN shows that it misses the image_id index and instead does a PK scan of 5B rows:

Limit  (cost=0.58..4632.03 rows=1 width=28)
   ->  Index Scan Backward using mcqueen_base_imagemeta2_pkey on mcqueen_base_imagemeta2  (cost=0.58..364597074.75 rows=78722 width=28)
         Filter: (image_id = ANY ('{123, ...}'::bigint[]))

If the LIMIT is increased to 2, it works as expected:

Limit  (cost=7585.92..7585.93 rows=2 width=28)
   ->  Sort  (cost=7585.92..7782.73 rows=78722 width=28)
         Sort Key: id DESC
         ->  Index Scan using mcqueen_base_imagemeta2_image_id_616fe89c on mcqueen_base_imagemeta2  (cost=0.58..6798.70 rows=78722 width=28)
               Index Cond: (image_id = ANY ('{123, ...}'::bigint[]))

This also happens on queries where the index matches ~3000 rows and the limit is set to 100, so something that easily happens in real world REST API pagination.

The table definition is:

mcqueen=# \d mcqueen_base_imagemeta2
                                       Table "public.mcqueen_base_imagemeta2"
      Column       |           Type           |                              Modifiers
-------------------+--------------------------+----------------------------------------------------------------------
 id                | bigint                   | not null default nextval('mcqueen_base_imagemeta2_id_seq'::regclass)
 created_at        | timestamp with time zone | not null
 image_id          | bigint                   | not null
 key_id            | smallint                 | not null
 source_version_id | smallint                 | not null
Indexes:
    "mcqueen_base_imagemeta2_pkey" PRIMARY KEY, btree (id)
    "mcqueen_base_imagemeta2_image_id_616fe89c" btree (image_id)
    "mcqueen_base_imagemeta2_key_id_a4854581" btree (key_id)
    "mcqueen_base_imagemeta2_source_version_id_f9b0513e" btree (source_version_id)
Foreign-key constraints:
    "mcqueen_base_imageme_image_id_616fe89c_fk_mcqueen_b" FOREIGN KEY (image_id) REFERENCES mcqueen_base_image(id) DEFERRABLE INITIALLY DEFERRED
    "mcqueen_base_imageme_key_id_a4854581_fk_mcqueen_b" FOREIGN KEY (key_id) REFERENCES mcqueen_base_metakey(id) DEFERRABLE INITIALLY DEFERRED
    "mcqueen_base_imageme_source_version_id_f9b0513e_fk_mcqueen_b" FOREIGN KEY (source_version_id) REFERENCES mcqueen_base_metasourceversion(id) DEFERRABLE INITIALLY DEFERRED

I'm a novice at best when it comes to tuning, but I figure that the defaults for statistics are not up to that table's size and so it naively thinks that a PK scan is faster than an index scan.

Why?
For a LIMIT 1, Postgres may estimate it to be faster to traverse the index supporting the ORDER BY and just keep filtering until the first row is found. This is fast as long as more than a few rows qualify and one of those pops up early according to ORDER BY. But it is (very) slow if no qualifying row pops up early, or even a worst case scenario if no row ends up qualifying at all. Similar for any small LIMIT.
Postgres collects statistics about the most common values (MCV list), but not for the least common ones - for obvious reasons, that would be far too many to be useful. And it has no statistics for correlations between columns by default. (While that can be created manually it won't fit your use case anyway, as ID numbers are typically uncorrelated.)
So Postgres has to base its decision on generic estimates. It's very hard to identify the sweet spot where to switch from one index to the other. This gets harder, yet, for a predicate like image_id IN (123, ... ) with many items, and most are typically rare or very rare or even non-existent. But if you put enough numbers into the list, Postgres will eventually expect that traversing the other index will find the first hit faster.
Solutions?
You may be able to improve the situation somewhat with a larger statistics target:
ALTER TABLE mcqueen_base_imagemeta2 ALTER image_id SET STATISTICS 2000;

That (among other things) increases the size of the MCV list for the column and help identify more (less) common values. But it's not a general solution for the problem, and makes ANALYZE and query planning a bit more expensive. Related:

Check statistics targets in PostgreSQL

Upgrading to the latest version (soon to be Postgres 12) also helps as general performance got better and the planner smarter.
There are various techniques for a workaround, depending on cardinalities, value frequencies, access patterns, ... Completely disabling the ORDER BY index like Laurenz demonstrated is one radical workaround - which can backfire for long lists or very common image_id, where the ORDER BY index would, in fact, be much faster.
Related:

Can spatial index help a "range - order by - limit" query

Workaround for your case
Should work well for the given numbers: 5 billion rows, around 20 image_id in the filter list, small LIMIT. Most efficient for LIMIT 1 and a short list, but good for any small LIMIT and manageable list size:
SELECT m.*
FROM   unnest( '{123, ...}'::bigint[]) i(image_id)
CROSS  JOIN LATERAL (
   SELECT m.id
   FROM   mcqueen_base_imagemeta2 m
   WHERE  m.image_id = i.image_id
   ORDER  BY m.id DESC
   LIMIT  1  -- or N
   ) m
ORDER  BY id DESC
LIMIT  1;  -- or N

Provide your list as array and unnest(). Or use a VALUES expression. Related:

Optimizing a Postgres query with a large IN

It's essential to support this with a multicolumn index on (image_id, id DESC)!
You might then delete the existing index mcqueen_base_imagemeta2_image_id_616fe89c on just (image_id). See:

Is a composite index also good for queries on the first field?

This should result in one very fast index(-only) scan per image_id. And a final, (very) cheap sort step.
Fetching N rows for each image_id guarantees that we have all rows needed in the outer query. If you have meta-knowledge that only fewer rows per single image_id can be in the result, you can decrease the nested LIMIT accordingly.
Aside

(a common pattern in Django pagination)

Pagination with LIMIT and OFFSET? OK for the first page, but after that it's just a bad idea.

Efficient pagination for big tables
What is the recommended way to join junction tables for efficient ordering/pagination?


PL/pgSQL functions and optimizations fences? I am newbie on DB stuff. And my DB knowledge is very old (around MSSQL2000). All what I remember is, the stored procedures are faster than ordinary queries because it avoid query compilation and optimization.

I know PG has two types of query caching options. Prepared statement (temporary cached query) and function (persistent cached query, which is stored procedure in MSSQL).

Anyway, today I heard the concept of optimization fence. Which is the optimizer sees functions as opaque black box, so cannot optimize the inside. Conceptually it could be right.

Is this true even if the functions are fully written only with PL/pgSQL? Or just a limitation only to non-SQL languages? Do I have any chance to get better performance by using PL/pgSQL?

Functions written in language SQL can under certain conditions (e.g., no side effects) be "inlined" (at the discretion of the optimizer), in which case they will take part in the query plan optimization.  Functions written in all other languages (including PL/pgSQL) will not take part in the optimization of the main query, so they are indeed optimization fences, as you call it.

On the other hand, a query run by a PL/pgSQL function will be compiled and prepared the first time it is run, so subsequent calls of that function (even as part of the same top-level query) will be faster.  SQL functions do not implicitly prepare the queries they run, so if inlining of an SQL function fails, repeated calls will probably slower.

In practice, it depends on the specific circumstances which is faster.  Generally, I stick to the basic rule of using LANGUAGE SQL when it's possible and LANGUAGE plpgsql only when I need the additional expressive power.  But it's definitely a useful microoptimization strategy to compare implementations in the two languages.


Process possessing lock crashes I have two machines, M1 and M2, through which I am connecting to MySQL Database on machine M3 using Perl script (DBI.pm).

So, here is the scenario that I am confused about:
M1 places a table level lock, and performs some operations. M2 also does the same. Table level locking mechanism is guarantying me that both of them will not be locking the table at the same time.

But, what if M1 has locked the table, and suddenly after performing half hte updates, the machine crashes or the Perl script process crashes, what happens to the lock then? My updates were performed mid-way, do those get rolled back? And what is the best way to deal with these kind of locks?

http://dev.mysql.com/doc/refman/5.6/en/innodb-autocommit-commit-rollback.html

  If a session that has autocommit disabled ends without explicitly committing the final transaction, MySQL rolls back that transaction.

There is a process that monitors active sessions. If any are found to have disconnected, the sessions are cleaned up. This includes rollback of uncommitted transactions.


Where is authentication information saved in master? When using SQL Server Authentication mode, I know that usernames and hashed passwords are saved in the master database. I wonder is there a way to retrieve that information? If yes, which tables stores it?
(except contained database users)

Thanks for support.

I wonder is there a way to retrieve that information? If yes, which tables stores it? (except contained database users)

For SQL Server authentication you have password stored in hash format you can see it from sys.sql_logins, the column password_hash stores password in hash format. The information about SQl server logins are stored in master database and each login has SID respective to it. Only SA login has same SID no matter what server it is. To get SQL authenticated logins you can use below query

SELECT name
FROM sys.server_principals
WHERE TYPE = 'S'
and name not like '%##%'

You can use sys.database_principals and sys.server_principal to gain more information about logins and users.

Regarding would you be able to recover the hashed password or decrypt it in some somway, the answer is NO you cannot.


What happens when you update a VARCHAR cell with a longer string? Say I have a column of type VARCHAR(32) in a mySQL database.

On Spetmeber 1st I decide to store the string "tea", but on September 2nd I decide to update it to "coffee".

Clearly, if all the records in this table were squished right up against each other, and we tried to lengthen one record by 3 bytes, then all the records that appeared after this one would need to shift down by 3 bytes. Of course, this is ridiculous; there is no way any DBMS would ever resort to shifting thousands of possible entries.

So what exactly does mySQL do in this eventuality? Does it behave the same way for TEXT and BLOB types?

EDIT:
After reading this a day later, I realized that this question was fairly ambiguous. Here is an example that I hope will clear things up:

Say I have a table, fav_drinks with two columns:

user_id, which is an INTEGER
drink, which is a VARCHAR(32)

Pretend that this table is stored like this in memory:

[1,"juice",2,"tea",3,"soda",4,"hot chocolate"]

That is, all records are stored sequentially one after the other. If we need to update user 2's favourite drink from "tea" to "coffee", in theory we would need to shift down the entries for users 3 and 4. Of course, I don't think this is what would happen in a real database.

So, to reiterate the question, how does mySQL manage this specific case where one table entry suddenly requires more memory?

MySQL's InnoDB stores rows in blocks of 16KB each.  Within a block there could be a few big rows or hundreds on narrow rows.  Almost always, there is some spare room in a block.  When a column in a row is changed, the row is rewritten in the same block (if possible) and things are shuffled around within the block to make room.  (The exact details are buried in the code.)

If so much text (or blob or whatever) is added, that there is not room in the block, then there is a "block split".  This is where the rows in the original block are split between two blocks.

Blocks are stored in a B+Tree arrangement of blocks.  (See Wikipedia.)  Therefore, the blocks are not necessarily stored consecutively, but they can be referenced consecutively.  Thus, a block split in a billion-row table is about the same small amount of work as in a hundred-row table.

Another aspect is the "MVCC" that goes on to allow for multiple transactions to touch the same rows at the same time.  This leads to not just replacing a row with the modified value, but actually hanging onto the previous copy of the row until the transaction that needs it is finished.  But again, the spare room in a block, plus block splits, plus the BTree organization handles this.

So, your next question is about deleting?  Well, if the text is shrunken a lot, or a bunch of rows are DELETEd, then it seems like a block might be shrunken a lot, possibly even emptied?  Yes.  In this case, InnoDB checks to see if two adjacent blocks are small enough to combine, and does so, thereby freeing up one of the blocks for future use.  (This freed up block is kept in the tablespace and not given back to the OS, hence ibdata1 never shrinks; etc.)

All of that stuff happens under the covers, and we don't need to worry about it.

Really large records:  For example a row with a MEDIUMTEXT column with 1MB of data won't fit in 16KB.  What to do?  Well, the bulky columns are actually stored in other blocks, separate from the main part of the record.  This leads to some confusing limitations on the maximum size of a row and some unexpected performance issues.  Again, this is handled transparently, and the unit of allocation is 16KB.

While I am at it, note that the structure of an INDEX is essentially identical to that of a table -- a BTree of 16KB blocks that play block split/merge games under the covers as the index entries are added/deleted for row inserts/deletes.


Security Compliance Dashboard We have internal audit team, which works with E&Y and SOX on Monthly, Quarterly and Yearly basis. They would come up with different kind of questions each time when its time for monthly/quarterly/yearly audit needs to be done. This would involve my DBA's to spend almost a day and half (depending upon requirements) to provide them the information (i.e. Screenshot, Query, Result-set) they are looking for.

Here are few examples of what they ask to see:

Who has DBO/SA access?
Who can modify particular object?
Who got access to modify MonthEnd process since last time we did security check?

We have asked our internal audit group to provide us list of things (i.e. most commonly asked question from E&Y and SOX), so this way, we can build them a Security Dashboard, which internal audit group can utilize to see, who has access to what and so on without requiring DBA help. But, each time we asked for requirements from them, we get little run around or just won't respond with requirements. I know there are 3rd Party solution out there like Idera, Apexsql...but we don't have luxury to spend money on 3rd party tools at the moment.

My questions are:

Does anybody have build their Own Solution to provide Security information to internal audit via Dashboard/Report/Some Automated ways so they can export out result-set or take a screenshot from the dashboard or something like that?
Is there a way to get requirements which are approved by E&Y and SOX compliance for SQL database?

We are all Microsoft shop and have various SQL Server i.e. 2000 (please don't ask why still SQL 2000) to 2016.

**Note: If you think this is not the correct place to post this question or is missing anything or wanted me to correct anything, please let me know.

Thank you,

HP

In my experience with audits, some questions repeat every year, and some are tweaked repeatedly, either as the company moves from being less compliant to more compliant (requiring more detail), or as the rules change over time.

Also, some requests require human verification (for example, if you're asked to note all accounts with elevated privileges, and why those accounts have those privileges, then simply check for anything new added or old removed, and then handing them the same thing you gave last year (modulo those changes), doesn't really do the trick - someone actually needs to look at the reasons given, and be sure they still apply. Because, if next year someone responds "Gee, that shouldn't be on there still, we stopped using that software 5 years ago," lots of people will not be happy.

Finally, some things (like screen shots showing the "sa" login is disabled, if it is) aren't easily automated (as far as I know), or require enough effort to automate that it would take years for it to balance out.

For many of the rest, the auditing team wouldn't necessarily want there to be an automated where miscellaneous people can run (for instance) a query to see what logins have sysadmin access. Among other things, if such a tool is available, it's exploitable. They'd probably rather have a sysadmin user run these things as needed, for that reason.

However, that doesn't mean the sysadmin user can't save the script they ran last year, and use it again next year. Upgrades and such can make this less useful (if a command you were using was removed, or if new options make your script obsolete), but a starting point is always good.

When I was responsible for this sort of thing, I had a few scripts saved off that I'd run each year to provide as much of what was wanted as I could. In fact, some of the data was part of jobs that triggered things (like reviewing privileged logins/users at least 4 times a year, or whatever). Early on I would tweak them regularly to make sure I was getting everything needed, but after two or three iterations they generally stayed the same.

Unfortunately, as such code belongs to my former employer, I can't really pass any of it on to anyone else. However, once you have what you need for this year, make sure it's saved, and appropriately documented (as in, what was used to fulfill what requests), and known to all DBAs in the department; put it in your knowledgebase, or wherever your people look to find how to do something they haven't done before. Also keep the requests and responses, so you can refer back to them (as the auditors change, sometimes the terminology they use when making a request also changes - being able to say, "Here's what I think we gave the auditors last year in response to a similar request - is that what you're looking for?" is very helpful.

If your audit team is good about maintaining an email-distribution list, then automated jobs are much more useful; if not, then your job output is likely to be sent to one guy who's not doing audits any more, and 5 addresses that no longer exist.

The best requirements from your auditors (internal or external) tends to be "whatever worked last year". In fact, if you can say "here's what we gave them last year", and it's not good enough this year, it tends to put the onus on the auditors to give you an updated script that covers what else they need. After all, who's to say you know how to get it, or you'll get it right (sometimes, the "DBA" is the one guy who could actually write a SELECT query if he had to, maybe even without a query building tool).

If you're too swamped to do something when they ask, don't be afraid to tell them. Maybe they don't have to have it for a month, and are fine with you not running it for three weeks. They are being paid by your company, and generally don't want to interfere more than they have to.

That said, I always try to be as helpful as possible with the auditors. Yes, they're interfering with your normal work, but they're just doing their job, and it's necessary for your company to continue. And, if you're always honest and up-front with them, they may be far more understanding if they do see something amiss than if you've seemed adversarial in general.


"current security context is not trusted" for job running as a SQL login accessing linked server I'm running into the error "Access to the remote server is denied because the current security context is not trusted. [SQLSTATE 42000] (Error 15274)" while trying to run a stored procedure from a job, and the stored procedure queries against a linked server. The job is configured to run as a SQL login which I'll refer to as user1, and I have given user1 permission and ownership in every possible place.

Here is how the job is set up:

A SQL Server Agent job whose owner is user1
A step in the job is configured to "Run as user" user1 on the Advanced tab (The "Run as" list on the General tab is a blank list -- not sure if that's a factor)
The step calls a stored procedure which user1 has access to execute
The stored procedure is defined with WITH EXECUTE AS 'user1'
The linked server has a local/remote mapping for user1 with the remote user and remote password entered. The username and password are the same on both servers. The "For a login not defined in the list above, connections will" option is set to "Be made using the login's current security context". I've also tried setting this option to "Be made using this security context", with user1 and its password repeated.
user1 has the appropriate permissions to query on the remote server.
I even made user1 a sysadmin on the local server

Despite all of these, I still get the access error. I must be missing something somewhere... Any ideas?

I fixed this by 1) removing the WITH EXECUTE AS 'user1' clause from the stored procedure and 2) removing user1 from the job -- it is no longer the owner or the "Run as user" of the job's step.

My final configuration is:

SQL Server Agent job owner is a sysadmin, not user1
The job's step "Run as user" is left blank
The step calls a stored procedure which user1 has rights to
execute
The linked server has a local/remote mapping for user1 with the
remote user and remote password entered. The username and password
are the same on both servers. The "For a login not defined in the
list above, connections will" option is set to "Be made using the
login's current security context".
user1 has the appropriate permissions to query on
the remote server.
user1 does not need to have the sysadmin role or any such administrative role


SQL Server 2012 Express with Reporting Services Everywhere I search for SQL Server 2012 Express with Advanced Services, I ended up with this link, http://www.microsoft.com/en-us/download/details.aspx?id=35579, and every time I install it, I do not see where I can add reporting services as a feature.

Is there another installation file that includes Reporting Services, or do I actually have it now but cannot tell it's installed? I went to Control Panel -> Administrative Tools -> Services already and do not see it listed, which tells me that it's not installed?

Update: I've followed Kin's instructions, but just cannot advance further.  Here's where I am now. In Reporting Services Configuration Manager, the Connect button is grayed out, and when I click on Find, I get error message of "Unable to connect to the Reporting Services..." See below.

When I go to the SQL Server Configuration Manager, I do not see Reporting Services as a service listed to start of stop. See below.

So, in my mind, perhaps I don't have the Reporting Services installed, so I try to install the features, but there's no Reporting Services listed as an available feature to add. See below. I know I downloaded the .exe file that includes everything available to download, including the Reporting Services. What should my next steps be to troubleshoot further? I'm tempted to uninstall and reinstall, but I'm not confident that I can uninstall properly. There seems to be so many files related to SQL Server 2012, and I'm not sure how that would affect Visual Studio I currently have installed. By the way, I'm on a local desktop installation with Windows 7.

Following up on my comment, you have to select ENU\x64\SQLEXPRADV_x64_ENU.exe for installation.

Download link.

Then run the installation package and follow instruction until you get feature selection page.

Follow rest of the steps and complete the installation.

Now Verify the RS installation :

Note: You must be logged in as a member of the local administrators group on the computer where SQL Server 2012 Express is installed.

Navigate to Start  -> All Programs -> Microsoft SQL Server 2012 -> Configuration Tools -> Reporting Services Configuration Manager.
In the RS Configuration Connection dialog, make sure that your local report server instance (for example SQLExpess) is selected and click Connect.

In the RS Configuration Manager left pane, select Web Service
URL.

Make sure that the Virtual Directory parameter is set to ‘ReportServer_’ (‘ReportServer_SQLEXPRESS’ for the SQLEXPRESS instance), and that the TCP Port is set to 80. Click Apply.
In the Reporting Services Configuration Manager left pane, select Database.

On this page, ensure that the SQL Server Name and Database Name fields contain correct values. If not, click the Change Database button and complete the Report Server Database Configuration wizard.
In the Reporting Services Configuration Manager left pane, select Report Manager URL.
On this page, ensure that the Virtual Directory parameter is set correctly, and that the URL is valid.
Click Exit to close the RS Configuration Manager.


Compare strings from two tables SQL Server 2014 I'm using these two tables in a SQL Server 2014 database:

CREATE TABLE #FORMULAS
(
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [DataSourceAddress] [nvarchar](255) NULL,
    [Description] [nvarchar](255) NULL
)

CREATE INDEX [IX_FORMULAS]
ON #FORMULAS ([DataSourceAddress] ASC)

CREATE TABLE #PARAMETERS
(
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Datetime] DateTime NULL,
    [ParameterName] [nvarchar](255) NULL,
    [Value] float NULL
)

CREATE INDEX [IX_PARAMETERS]
ON #PARAMETERS([ParameterName] ASC)

INSERT INTO #FORMULAS ([DataSourceAddress], [Description])
VALUES ('(parameter1+parameter2+parameter3)/3', 'Formula description 1'),
       ('parameter4', 'Formula description 2'),
       ('(parameter5*1000)', 'Formula description 3');

INSERT INTO #PARAMETERS ([Datetime], [ParameterName], [Value])
VALUES ('2016-01-26 11:40:00.000', 'parameter1', 1),
       ('2016-01-26 11:40:00.000', 'parameter2', 2),
       ('2016-01-26 11:40:00.000', 'parameter3', 3),
       ('2016-01-26 11:40:00.000', 'parameter4', 4),
       ('2016-01-26 11:40:00.000', 'parameter5', 5),
       ('2016-01-26 11:40:00.000', 'parameter6', 6),
       ('2016-01-26 11:44:00.000', 'parameter1', 1),
       ('2016-01-26 11:44:00.000', 'parameter2', 2),
       ('2016-01-26 11:44:00.000', 'parameter3', 3),
       ('2016-01-26 11:44:00.000', 'parameter4', 4),
       ('2016-01-26 11:44:00.000', 'parameter5', 5),
       ('2016-01-26 11:44:00.000', 'parameter6', 6);

I want to select the parameters of the table #FORMULAS that match with the column [ParameterName] of the table #PARAMETERS.

For example, take the value '(parameter1+parameter2+parameter3)/3' of the table #FORMULAS and receive all the rows of the table #PARAMETERS that contains 'parameter1','parameter2','parameter3'.

I'm using the following select:

SELECT
    A.[Datetime],
    A.[ParameterName],
    B.[Description],
    A.[Value]
FROM
    #PARAMETERS A
JOIN
    #FORMULAS B ON CHARINDEX(A.[ParameterName], B.[DataSourceAddress]) > 0

The #PARAMETERS table has more than 160000 rows (with 1174 unique ParameterNames), #FORMULAS has 3283 rows. The query takes a lot of time to finish, more than 10 minutes.

Is there a faster way to do the query?

Wildcard matching strings with good performance will always be tricky, but here's a best-effort attempt.

First off, your indexing can be improved. The index on #PARAMETERS only includes the actual ParameterName column, which is fine if you need a list of parameters, but whenever you need any other column, the index will be useless to SQL Server, and it'll revert to scanning the entire table instead. I would propose fixing this by changing the index to a clustered index:

CREATE UNIQUE CLUSTERED INDEX [IX_PARAMETERS]
    ON #PARAMETERS (ParameterName, [Datetime])
    WITH (DROP_EXISTING=ON);     -- Remove this row if the index doesn't already exist.

Clustered indexes include every column in the table, which automatically makes a clustered index a covering indexes.

From here, I've updated your query to do three things:

Collect each unique parameter in #PARAMETERS.
For each unique parameter, find matching formulas in #FORMULAS. This is the expensive part, and no index will really improve this search because the search condition (LIKE) is not sargable. This means that, index or not, SQL Server will go through each and every row in the table (a so-called scan), for every parameter.
Finally, join #PARAMETERS to get all the dates and values. This is a relatively cheap and efficient operation. This is where the modified index on #PARAMETERS helps.

Here's the resulting query:

SELECT  A.[Datetime],
        p.[ParameterName],
        B.[Description],
        A.[Value]
FROM (SELECT DISTINCT ParameterName FROM #PARAMETERS) p
CROSS APPLY (
    SELECT *
    FROM #FORMULAS B WITH (FORCESCAN)
    WHERE B.DataSourceAddress LIKE N'%'+p.ParameterName+N'%') AS B
INNER JOIN #PARAMETERS AS A ON
    p.ParameterName=A.ParameterName;

I added WITH (FORCESCAN) to eliminate key lookups. You can try the query with or without this hint.

If I had a lot more time to work on this and it was a super-critical query, I would probably (a) remodel the tables and/or (b) consider storing the data in #FORMULAS as XML data and add XML indexes, which would eliminate the LIKE match.

Footnote: Text matching like you do here (LIKE and CHARINDEX are pretty much the same here) will include formulas with the parameters "parameter10" and "parameter11" even when you're just searching for "parameter1".


Is there any AWS RDS Utility to Manage Master Slave mysql db with read write splitting? I am looking for a solution for production which can manage the read and write splitting on master and slave connection as i can't affford to define multiple connection string for R/W in whole application.

AWS does not have a service that provides this.

Transparent, intelligent read/write splitting is a Hard Problem™ because there are so many details that can go wrong, such as queries that use user-defined variables, which aren't deterministic unless the state of the variables is maintained across multiple connections... inconsistent reads and read-after-write due to replication lag... and many others.

The only solutions for now are third-party products like Maxscale and ProxySQL, unless you are using a connector library or ORM that manages such things for you (assuming such things exist).


SQL Server 2016: Bidirectional Transactional Replication using Backup, Do I use/need Snapshot? Overview:

I have been setting up bidirectional transactional replication between 2 SQL Servers. We have another pair of servers, running Inductive Automation's Ignition Gateway server creating and sending SQL data to Server 1 as the primary data source, and Server 2 as a backup data source. There are 3 databases that I need to keep synchronized at all times on each of the servers, so if a failover occurs the backup has the same data the primary has.

I have been setting up and testing this configuration for a long time, and testing the failover functionality of the Gateway software. All seems to be working very well, and the databases seem to be synchronizing.

The instructions I used had me setup network shares for the snapshots on each server, but since I used a backup of the 3 databases, it doesn't appear to ever use the Snapshots.

One of these synchronized databases is over 600GB of data. The deployment took 3 steps over a weekend.

Implementation:

Step 1: was starting the SQL Server Maintenance Plan of the primary server, consolidating\backing up all databases. This took over 6 hours to complete.

Step 2: was copying the files to the Backup Server, and restoring the databases to the Backup server, again taking many hours to finish.

At this point all 3 databases on Server 1 and Server 2 are identical.

Step 3: I ran my scripts that: activate Distribution on each server, Publish the 3 databases on each server, and subscribe each server to the published databases on the other server (ensuring that loopback detection was set to true for each subscription).

Questions:

What are the snapshot folders for, and why were they needed to setup the synchronization? I suspect something is either not working, or the part of the instructions that had me setup the network shares "\Server1\snapshots" and "\Server2\snapshots", was completely erroneous.
What is the Snapshot Agent supposed to be doing for the type of synchronization we have here?
I am seeing options in SQL Server Management Studio under the Replication section to 'validate', 'reinitialize', and 'check subscription status' of the subscriptions.  I've run each and the results don't give me the expected feedback. What is the difference?
In the event that the subscriptions have problems, the only maintenance procedure I feel confident about is repeating the steps we used to deploy this configuration.  The length of time it takes to complete this leaves me very nervous!  Production runs: Mon-Wed 24 hours, Thurs-Sat 4AM-4PM.  Interruptions to production are very expensive.  I'm looking for any guidance you might have about maintaining this setup.
Day 1 after implementation, I spotted that the Snapshot Agents had never started in the Replication Monitor, so I started the Agents manually.  When I did, both servers became unresponsive and had to be rebooted.  Now the Log Reader agents all look happy, performance is all excellent.  However, the Snapshot agents all have errors "Not completed", and I cannot seem to reset them or clear them in the Replication Monitor. How do I fix this?

Snapshot are not required if subscribers are setup using backup\restore or another technique to preload the subscriber data.


Group by Multiple columns and then count different value of same column I want to achieve the result which tells me the number of males and females of each disability types in each district.
each district can have multiple disabilities.
So far i have reached  the following query :
  SELECT
  DistrictId,
  fb.DisabilityTypeId,
  SUM(
    CASE WHEN GenderId = 1 THEN 1 ELSE 0 END
  ) AS Male,
  SUM(
    CASE WHEN GenderId = 2 THEN 1 ELSE 0 END
  ) AS Female
FROM
  Districts d
  LEFT OUTER JOIN FormAddresses a ON d.Id = a.DistrictId
  INNER JOIN PeopleForms pf ON a.PeopleFormId = pf.Id
  INNER JOIN FormBeneficiaries fb ON pf.Id = fb.PeopleFormId
  INNER JOIN FormPersonalInfos fp ON pf.Id = fp.PeopleFormId
where
  a.IsDeleted = 0
  AND pf.FormTypeId = 2
  AND d.CityId = 3
GROUP BY
  DistrictId,
  fp.GenderId,
  fb.DisabilityTypeId

which gives the following result  :
DistrictId  |   DisabilityTypeId    |   Male  | Female
    1       |       2               |   1     |     0
    3       |       2               |   0     |     3
    5       |       16              |   1     |     0
    5       |       20              |   2     |     0
    5       |       20              |   0     |     1

But i want to achieve the following result :
DistrictId  |   DisabilityTypeId    |   Male  | Female
    1       |       2               |   1     |     3
    5       |       16              |   1     |     0
    5       |       20              |   2     |     1

i somehow managed to get the expected result, but that with some complex sub-queries in the select clause  of each gender which i didnt like and was not sure about the performance.
how can i write an efficient query for the desired result that i want?
Thanks.

Remove gender from GROUP BY clause:
SELECT DistrictId
     , fb.DisabilityTypeId
     , SUM( CASE WHEN GenderId = 1 THEN 1 ELSE 0 END ) AS Male
     , SUM( CASE WHEN GenderId = 2 THEN 1 ELSE 0 END ) AS Female
FROM Districts d
LEFT JOIN FormAddresses a
    ON d.Id = a.DistrictId
JOIN PeopleForms pf
    ON a.PeopleFormId = pf.Id
JOIN FormBeneficiaries FB
    ON pf.Id = fb.PeopleFormId
JOIN FormPersonalInfos fp
    ON pf.Id = fp.PeopleFormId
WHERE a.IsDeleted = 0
  AND pf.FormTypeId = 2
  AND d.CityId = 3
GROUP BY DistrictId
       , fb.DisabilityTypeId


Should we periodically 'refresh' the mirrored copy of a SQL Server database? When you have SQL Server mirroring set up, do you trust that your mirror database will stay the same as the primary, even after months and months of mirroring?  I know we all check the status, and of course it says the database is synchronized...  but I worry that somehow the mirror might fall out of sync due to network glitches, software bugs etc. and the fault won't be detected.  Then one day we need the mirror and we're horrified to learn that it's corrupted.

One thing I could do is periodically refresh the mirror from the primary, since our database is not terribly large.  But perhaps I'm being overly paranoid.

I would like to hear from DBAs, either of SQL Server or any other platform, as to whether you share this concern about the integrity of mirror DBs, and if so, what you do to mitigate that concern.  Do you occasionally re-copy your primary DB over to the mirror?  Do you take other measures to ensure consistency between the two?  Thanks for any advice you can provide.

I don't trust mirroring 100%, but I do trust it about 99%, same with everything else. It never hurts to periodically check your mirror database and DR scheme. We test our DR plan across the board several times a year, and it would not hurt your scenario to set aside a DR test time and simulate a failure of your primary database to fully test the mirror if you have concerns.


Declarative partitioning with Postgres for integer column I was reading this article:
https://blog.timescale.com/scaling-partitioning-data-postgresql-10-explained-cd48a712a9a1

the article lifts information directly from here:
https://www.postgresql.org/docs/10/static/ddl-partitioning.html#DDL-PARTITIONING-DECLARATIVE

My question is - if we declaratively create a partitioned table like so:

CREATE TABLE measurement (
    city_id         int not null,
    logdate         date not null,
    peaktemp        int,
    unitsales       int
) PARTITION BY peaktemp;

and then let's say we insert into measurement, but with a peaktemp that is not covered by an existing partition.

Before we insert, do we need to manually create our own partition like so:

CREATE TABLE measurement_55 PARTITION OF measurement FOR VALUE 55;

..or will Postgres create that table for us if we insert into measurement with peaktemp as 55?

Sorry the syntax might be wrong but hopefully the question makes sense.
Basically I want to partition a table by an integer column, one partition for each value of the integer. And I am wondering what is automated and what remains manual in this case?

It should be done manually.  From the doc page (already mentioned in the question)
 - https://www.postgresql.org/docs/10/static/ddl-partitioning.html#DDL-PARTITIONING-DECLARATIVE) -
under

  5.10.2.1 Example  To use declarative partitioning in this case, use the following steps: ....

  Create partitions. Each partition's definition must specify the bounds that correspond to the partitioning method and partition key of
  the parent. Note that specifying bounds such that the new partition's
  values will overlap with those in one or more existing partitions will
  cause an error. Inserting data into the parent table that does not map
  to one of the existing partitions will cause an error; an appropriate
  partition must be added manually."


ORACLE file I/O withouth explicit flushing on 10g and 11g what happens, if I open a file (foo := utl_file.fopen(... ), write multiple times to a file ( utl_file.put_line (foo,bar) ) and close it ( utl_file.fclose(foo) ) without any flushing?

Is there a difference between the versions 10g and 11g?
Is the output deterministic?

Thank you

Edit 1: I am asking because we got some missing characters in a huge output file at some points where the 7FFF (= 32767) byte is full and the 32768th byte would be written. This error exists only since the update from 10g to 11g. The error in the code might be a missing flush, but we don't get any exceptions and it was working before the update. The issue is not with every 32768th byte.

According to the documentation for

10g http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/u_file.htm#i1003326

11g http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/u_file.htm#ARPLS70908

and

12c http://docs.oracle.com/cd/E16655_01/appdev.121/e17602/u_file.htm#i1003326

  If there is buffered data yet to be written when FCLOSE runs, then you may receive a WRITE_ERROR exception when closing a file.

So you should call utl_file.fflush(foo); before closing.


Way to check where the MySQL connection was initialized? Is there a way to identify who created the MySQL connection?

For example:
in file a.php
$conn = mysqli_connect($dbhost, $dbuser, $dbpwd, null, $port, $socket);
I would like to know that this connection was initialized from a.php

I switched on the logger but all it told me that the connection was made but not who initialized it.
Connect    root@localhost on  using Socket

Who has the root password?  If there are multiple admins with root permissions, change them to non "root" user names, but with ALL PRIVILEGES.  Then you can distinguish them in the general log.

"Socket" implies the same machine; nothing provides what program.

I recommend you add comments to every query, perhaps with the filename and line number (__LINE__).


PHYSICAL_READ_BYTES_DELTA and PHYSICAL_READ_BYTES_TOTAL What is the difference between PHYSICAL_READ_BYTES_DELTA column and PHYSICAL_READ_BYTES_TOTAL  column in DBA_HIST_SQLSTAT table? Are they correlate?  Please provide also an example.

The documentation explains it perfectly:

  The total value is the value of the statistics since instance
  startup. The delta value is the value of the statistics from the
  BEGIN_INTERVAL_TIME to the END_INTERVAL_TIME in the DBA_HIST_SNAPSHOT
  view.

So in this case, the PHYSICAL_READ_BYTES_TOTAL is the number of physical reads (in bytes) since the instance was started, and PHYSICAL_READ_BYTES_DELTA is the number of physical reads (in bytes) since the previous snapshot (SNAP_ID).


Shrink log file after configuring backups properly I had a database that was working in FULL recovery mode but never had transaction log backups done.

The consequence of that was the fact that the log file kept growing and now its 10 GB in size.

I configured scheduled backups for this database (full + differential + log) and now log % space used is around 1%.

I want to shrink the 10GB file to a sensible size (maybe 200MB with autogrow 5 MB?).

How can I do it and what are the consequences of shrinking the file?

I want to shrink the 10GB file to a sensible size (maybe 200MB with autogrow 5 MB?).

IMHO, 5MB autogrowth is not sensible. You should monitor your log growth using default trace and find a good value for autogrowth.

Below script will help you monitor your Log (and Data file) autogrowths. Trend it over a week (or whatever suits your need). This will help you get a "near to best number" for your autogrowth setting.

IF OBJECT_ID('tempdb..#autogrowthTotal') IS NOT NULL
    DROP TABLE #autogrowthTotal;
IF OBJECT_ID('tempdb..#autogrowthTotal_Final') IS NOT NULL
    DROP TABLE #autogrowthTotal_Final;

DECLARE @filename NVARCHAR(1000);
DECLARE @bc INT;
DECLARE @ec INT;
DECLARE @bfn VARCHAR(1000);
DECLARE @efn VARCHAR(10);

-- Get the name of the current default trace
SELECT @filename = CAST(value AS NVARCHAR(1000))
FROM ::fn_trace_getinfo(DEFAULT)
WHERE traceid = 1 AND property = 2;

-- rip apart file name into pieces
SET @filename = REVERSE(@filename);
SET @bc = CHARINDEX('.',@filename);
SET @ec = CHARINDEX('_',@filename)+1;
SET @efn = REVERSE(SUBSTRING(@filename,1,@bc));
SET @bfn = REVERSE(SUBSTRING(@filename,@ec,LEN(@filename)));

-- set filename without rollover number
SET @filename = @bfn + @efn

-- process all trace files
SELECT
  ftg.StartTime
  ,te.name AS EventName
,DB_NAME(ftg.databaseid) AS DatabaseName
,ftg.[FileName] as LogicalFileName
,(ftg.IntegerData*8)/1024.0 AS GrowthMB
,(ftg.duration/1000)AS DurMS
,mf.physical_name AS PhysicalFileName
into #autogrowthTotal
FROM ::fn_trace_gettable(@filename, DEFAULT) AS ftg
INNER JOIN sys.trace_events AS te ON ftg.EventClass = te.trace_event_id
join sys.master_files mf on (mf.database_id = ftg.databaseid) and (mf.name = ftg.[FileName])
WHERE (ftg.EventClass = 92  -- Data File Auto-grow
    OR ftg.EventClass = 93) -- Log File Auto-grow
ORDER BY ftg.StartTime

select count(1) as NoOfTimesEventFired
        , CONVERT(VARCHAR(10), StartTime, 120) as StartTime
        , EventName
        , DatabaseName
        , [LogicalFileName]
        , PhysicalFileName
        , SUM(GrowthMB) as TotalGrowthMB
        , SUM(DurMS) as TotalDurationMS
into #autogrowthTotal_Final
from #autogrowthTotal
group by CONVERT(VARCHAR(10), StartTime, 120),EventName,DatabaseName, [LogicalFileName], PhysicalFileName
having count(1) > 5 or SUM(DurMS)/1000 > 60 -- change this for finetuning....
order by CONVERT(VARCHAR(10), StartTime, 120)

-- Report back the results
select * from #autogrowthTotal_Final
order by TotalDurationMS desc
-- optional Filters
-- where DatabaseName = ''

  Why shouldn't I use autogrow if a log file is really being recreated after a differential backup?

This is not true. When you take "Log Backups", SQL Server - for databases in proper FULL or BULK_LOGGED recovery models will truncate the transaction log i.e the portion of transaction log will be marked as "no longer needed" and will be overwritten.

Read up more on : Misconceptions around the log and log backups: how to convince yourself  and 8 Steps to better Transaction Log throughput

  I want to shrink the 10GB file to a sensible size (maybe 200MB with autogrow 5 MB?).
  How can I do it and what are the consequences of shrinking the file?

You can use DBCC SHRINKFILE(yourdb_log, 200);. Remember to use SHRINKFILE and NOT SHRINKDATABASE.

I would suggest you to be PROACTIVE (monitor your log autogrowths and give a sensible value for autogrowth) rather being REACTIVE (frequently shrink log file, because you think that you will need disk space (or for whatever reasons)).

You should read up answers from Mike Walsh and Aaron Bertrand for Why Does the Transaction Log Keep Growing or Run Out of Space? to understand the consequences of shrinking log file.


Its odd that your SHOW PROCESSLIST doesn't include the REPAIR TABLE. This suggests its in some form of hung state. Check with a new connection and SHOW PROCESSLIST again. If it also doesn't show the REPAIR TABLE, I'd restart the mysqld service and try again.