Upgrading postgres to 10 from 9.5 using pg_upgradecluster I am testing the process of upgrading postgres to 10 from 9.5

I am following the steps as mentioned in this link -

dmanna@ubuntu:~$ pg_lsclusters
Ver Cluster Port Status Owner    Data directory               Log file
9.5 main    5432 online postgres /var/lib/postgresql/9.5/main /var/log/postgresql/postgresql-9.5-main.log
10  main    5433 online postgres /var/lib/postgresql/10/main  /var/log/postgresql/postgresql-10-main.log

sudo pg_dropcluster 10 main --stop
sudo pg_upgradecluster 9.5 main -m upgrade
sudo pg_dropcluster 9.5 main

Can someonet let me know the following

If there is a way I can check if the upgrade will be successful
before going for the actual upgrade?
If I am using the link option
in the upgrade can I still use the sudo pg_dropcluster 9.5 main?

To check for compatibility, etc use the native upgrade command with check option.

pg_upgrade —check
Refer to the the pg_upgrade doc for details. And remember (just found out for Ubuntu today), datadir is actually the location of the dir which has the config files (hba, ident, pg.conf).

If the —link option is used

sudo pg_dropcluster 9.5 main

should be run. IIRC pg_upgradecluster starts the db cluster automatically.

If you follow pg_upgrade route, there is defined point where the old cluster can still be used/ rolled back to.


How to search one bible verse from a source that may contain ranges? My WordPress system contains posts with a field containing Bible references and ranges, eg.

John 5,1-10; Matthew 3,2; Mark 1,1-5

Users searching for a Bible references e.g. John 5,3 or Matthew 3,2 or Mark 1,2 would all need to find the above post.

From an SQL point of view is this even possible? If not what would I have to change in this approach?

This is very doable, assuming you setup the structure correctly.

create database dba248824;
use dba248824;

This post table is a placeholder to make the queries work. This table already exists in your project, under some name (I don't know what), and holds your posts that have references to Bible verses.

CREATE TABLE post (
`post_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`post_text` MEDIUMTEXT NOT NULL,
PRIMARY KEY (`post_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Next up, we have a table to define all the books of the bible. It is not strictly necessary to have a separate table, and purists will lash me for not making use of natural keys, but I choose to include this table to minimize the size of the indexes in other tables. This only needs to be populated once, with all the books of the Bible.

CREATE TABLE bible_book (
`bible_book_id` TINYINT UNSIGNED NOT NULL,
`bible_book_name` VARCHAR(40) NOT NULL,
PRIMARY KEY (`bible_book_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

The bible_verse table also only needs to be populated once, and would contain every verse in the Bible. Actually, if you only need to reference the verses and not return the text of the verses you can completely skip this table.

If you choose to include this table, you may choose to add a FOREIGN KEY constraint from post_bible_verse to reference this bible_verse table.

CREATE TABLE bible_verse (
`bible_book_id` TINYINT UNSIGNED NOT NULL,
`chapter` TINYINT UNSIGNED NOT NULL,
`verse` TINYINT UNSIGNED NOT NULL,
`text` VARCHAR(255) NOT NULL,
PRIMARY KEY (`bible_book_id`,`chapter`,`verse`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

The post_bible_verse table is the work-horse of this setup. Here, you recover each individual verse that is referenced in a post. You'll have to translate ranges into a list of individual verses using some server side code, but once done you can easily insert into here each time a post is made referencing some verses.

CREATE TABLE post_bible_verse (
`post_id` INT UNSIGNED NOT NULL,
`bible_book_id` TINYINT UNSIGNED NOT NULL,
`chapter` TINYINT UNSIGNED NOT NULL,
`verse` TINYINT UNSIGNED NOT NULL,
PRIMARY KEY (`post_id`,`bible_book_id`,`chapter`,`verse`),
UNIQUE KEY (`bible_book_id`,`chapter`,`verse`,`post_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

To see how this setup works, lets populate some values.

First, the bible_book table. I used the "number" of the book in the Bible as the bible_book_id primary key. That is, John is the 43rd book of the bible, so it gets bible_book_id of 43. Etc.

(Remember, this only has to be done once in your case, to include all books.)

INSERT INTO bible_book
(bible_book_id,bible_book_name)
VALUES
(43,'John'),
(40,'Matthew'),
(41,'Mark');

Then, we populate all the Bible verses. As mentioned before, this is not necessary if you don't need to return the actual text of the verses.

This INSERT statement includes all the verses you mention in your example.

INSERT INTO bible_verse
(bible_book_id,chapter,verse,text)
VALUES
(43,5,1,'actual verse text here'),
(43,5,2,'actual verse text here'),
(43,5,3,'actual verse text here'),
(43,5,4,'actual verse text here'),
(43,5,5,'actual verse text here'),
(43,5,6,'actual verse text here'),
(43,5,7,'actual verse text here'),
(43,5,8,'actual verse text here'),
(43,5,9,'actual verse text here'),
(43,5,10,'actual verse text here'),
(40,3,2,'actual verse text here'),
(41,1,1,'actual verse text here'),
(41,1,2,'actual verse text here'),
(41,1,3,'actual verse text here'),
(41,1,4,'actual verse text here'),
(41,1,5,'actual verse text here');

Create a post to represent what is in your system. (You won't need to do anything in your real setup, as your blog posts already exist and presumably have some id column to use.)

INSERT INTO post
(post_id,post_text)
VALUES
(1,'some blog post here');

And finally, insert all the verses that correspond to this post. You'll have to include code to insert these verses when a post is made or updated, as a text range will not work.

INSERT INTO post_bible_verse
(post_id,bible_book_id,chapter,verse)
VALUES
(1,43,5,1),
(1,43,5,2),
(1,43,5,3),
(1,43,5,4),
(1,43,5,5),
(1,43,5,6),
(1,43,5,7),
(1,43,5,8),
(1,43,5,9),
(1,43,5,10),
(1,40,3,2),
(1,41,1,1),
(1,41,1,2),
(1,41,1,3),
(1,41,1,4),
(1,41,1,5);

Now lets see it in action.

User searches for John 5,3. Server parses that and runs the following query:

SELECT
post_id
FROM post
WHERE EXISTS (
  SELECT 1 FROM post_bible_verse pbv
  WHERE pbv.bible_book_id = 43
  AND pbv.chapter = 5
  AND pbv.verse = 3
  AND pbv.post_id = post.post_id
);

+---------+
| post_id |
+---------+
|       1 |
+---------+

And it returns post_id of 1 (which is the post that referenced those verses.)

User searches for Matthew 3,2:

SELECT
post_id
FROM post
WHERE EXISTS (
  SELECT 1 FROM post_bible_verse pbv
  WHERE pbv.bible_book_id = 40
  AND pbv.chapter = 3
  AND pbv.verse = 2
  AND pbv.post_id = post.post_id
);

+---------+
| post_id |
+---------+
|       1 |
+---------+

Again found our post.

Now Mark 1,2.

SELECT
post_id
FROM post
WHERE EXISTS (
  SELECT 1 FROM post_bible_verse pbv
  WHERE pbv.bible_book_id = 41
  AND pbv.chapter = 1
  AND pbv.verse = 2
  AND pbv.post_id = post.post_id
);

+---------+
| post_id |
+---------+
|       1 |
+---------+

And finally, search for Matthew 3,1, which is NOT referenced by your post.

SELECT
post_id
FROM post
WHERE EXISTS (
  SELECT 1 FROM post_bible_verse pbv
  WHERE pbv.bible_book_id = 40
  AND pbv.chapter = 3
  AND pbv.verse = 1
  AND pbv.post_id = post.post_id
);

Empty set

And we verify that the post is NOT found this time.

So, yes, it is very possible to create a database structure that allows you do search for posts based on Bible verses, but it does require an appropriate setup, and parsing of the ranges into specific queries.


SQLITE table joining with group MESSAGE TABLE                                       USERS
msg_id | msg_to | msg_from | msg_body | msg_date            user_id  | user_name
  1       1          2         hi      22/1/14                 2          jeff
  2       2          1        hello    22/1/14                 3          john
  3       1          2        howdy    22/1/14                 4          james
  4       2          1       nothing   22/1/14
  5       3          1        yes      22/1/14
  6       1          3         maybe   22/1/14
  7       4          1         no      22/1/14
  8       1          4         cool    22/1/14
  9       4          1         hot      22/1/14

*note that user 1 does not have any data in the users table.

I am having an issue getting all the messages uniquely in a stream based on who it was sent from. That is I want the last message that is exchanged between each user who communicates with user 1. I am trying to populate a list that should should the last message exchanged, so the list would be similar to this

          MESSAGE LISTING
     name      |         last_message
     jeff                   nothing
      john                  maybe
     james                   hot

Last messages exchanged between user 1 and the other users at all times, user 1 does not have data saved in the users table but I have access to his user_id if needed in query.
I tried doing a JOIN with the users table ON msg_from = user_id  but this only returns the last message from one user, so the last message exchanged between user 1 and user 2 is howdy instead of nothing based on this sample. I am not quite sure how to tune this query to represent the correct dataset that is needed, any help would be appreciated thanks.

The subquery computes, for each user in USERS, the latest msg_id value:

SELECT *
FROM MessageTable
WHERE msg_id IN (SELECT (SELECT msg_id
                         FROM MessageTable
                         WHERE msg_to   = Users.user_id
                            OR msg_from = Users.user_id
                         ORDER BY msg_id DESC    -- or by a timestamp
                         LIMIT 1)
                 FROM Users)


PostgreSQL utilize data from SELECT in BEGIN or use WITH? I went through the documentation for 9.3 and did not find anything suggesting that I can or can not use data from a SELECT query within a BEGIN query. Is it possible or will I have to resort to a WITH query of some kind?

I'm trying to merge two queries in to a single BEGIN query to figure out how to be more efficient when executing queries to the database. I have another project this rabbit trails from where I've built a tool that quickly duplicates a table (so I can test other database tools I'm building on instead of the originals) however it does not replicate the primary key.

This first query fetches the sequence name of a table:

SELECT s.relname AS sequence
FROM pg_class AS s JOIN pg_depend d ON d.objid = s.oid
INNER JOIN pg_class AS t ON d.objid = s.oid AND d.refobjid = t.oid
INNER JOIN pg_attribute AS a ON (d.refobjid, d.refobjsubid) = (a.attrelid, a.attnum)
INNER JOIN pg_namespace AS n ON n.oid = s.relnamespace
WHERE s.relkind = 'S'
AND n.nspname = 'public'
AND t.relname='parts';

This second query duplicates a table though already contains the sequence name that is fetched from the first query:

BEGIN;
LOCK TABLE parts;
CREATE TABLE parts1 (LIKE parts INCLUDING ALL);
INSERT INTO parts1 SELECT * FROM parts;
SELECT setval('parts_id_seq1', (SELECT max(id) FROM parts)+1);
COMMIT;

You would probably be better suited to do the whole operation inside an anonymous block.. DO ..

DO $$
  DECLARE
    SEQUENCE_NAME VARCHAR;
  BEGIN
    SELECT s.relname INTO SEQUENCE_NAME
    FROM pg_class AS s JOIN pg_depend d ON d.objid = s.oid
    INNER JOIN pg_class AS t ON d.objid = s.oid AND d.refobjid = t.oid
    INNER JOIN pg_attribute AS a ON (d.refobjid, d.refobjsubid) = (a.attrelid, a.attnum)
    INNER JOIN pg_namespace AS n ON n.oid = s.relnamespace
    WHERE s.relkind = 'S'
    AND n.nspname = 'public'
    AND t.relname='parts';

    LOCK TABLE parts;
    CREATE TABLE parts1 (LIKE parts INCLUDING ALL);
    INSERT INTO parts1 SELECT * FROM parts;
    -- change select to perform
    --SELECT setval(SEQUENCE_NAME::regclass, (SELECT max(id) FROM parts)+1);
    PERFORM setval(SEQUENCE_NAME::regclass, (SELECT max(id) FROM parts)+1);
  END;
$$ LANGUAGE plpgsql;

SQL Fiddle


Why am I getting Table scan on a partitioned table query? I have table Inventory_Break partitioned on IR_ID (int).  I also created a non-clustered index for IR_ID on the table.

When I run the following:

SELECT  *
FROM    dbo.INVENTORY_BREAK
WHERE   IR_ID IN ( 100, 101, 102 )

I get the following execution plan:

It states Table Scan (HEAP).  Not quite sure what it means in the context of a partitioned table.  I also don't see that it uses any kind of index.  And yet, it must, because the query comes back fairly fast (e.g. the table has 6 million rows).

So, my questions are:

What does Table Scan (HEAP) mean for a partitioned table?
Does it indeed use an index, perhaps behind the scenes?
Is there anything I need to do to improve efficiency?

The query did do partition elimination.

If you look at the tooltip, the Actual Partition Count says 3, which means it touched 3 partitions. (The Seek Predicate section also confirm this.)

The reason why it appears as a table scan is because it's a table scan with partition elimination. I'm not sure how else to explain that.

No indexes were used for this. Table partitioning is implemented deeper down in the database internal structures than an index, so partition elimination is very efficient by nature (given a suitable predicate, which you provided).

That said, the predicate you've given is suitable only for partition elimination, and (very likely) not much else because it's not very selective. If all the rows from those partitions are required, this is already close to an optimal solution -- creating a useful clustered index is usually recommended.


Update Statement on one table takes locks on other tables Given the following statement on the AdventureWorks database:

UPDATE Sales.SalesOrderDetail SET OrderQty = 100 WHERE ModifiedDate >= '2014-01-01'

If I open another query window and look at the locks the query is using by running

sp_WhoIsActive @get_locks =1

i can see locks as follows:

   <Object name="Person" schema_name="Person">
      <Locks>
        <Lock resource_type="OBJECT" request_mode="X" request_status="GRANT" request_count="1" />
      </Locks>
    </Object>
    <Object name="SalesOrderDetail" schema_name="Sales">
      <Locks>
        <Lock resource_type="OBJECT" request_mode="X" request_status="GRANT" request_count="1" />
      </Locks>
    </Object>
    <Object name="SalesOrderHeader" schema_name="Sales">
      <Locks>
        <Lock resource_type="OBJECT" request_mode="X" request_status="GRANT" request_count="1" />
      </Locks>
    </Object>

I was expecting the table lock on SalesOrderDetail however, I don't understand why the table lock has been taken on the Person Table and the SalesOrderHeader table

I can confirm the locks are present by trying to SELECT * from one of the tables which is then blocked.

Why are tables that are not being updated locked by the update statement?

The tables are being locked due to the trigger: [iduSalesOrderDetail] on [Sales].[SalesOrderDetail]

The trigger launches if any of these actions occur:

IF UPDATE([ProductID]) OR UPDATE([OrderQty]) OR UPDATE([UnitPrice]) OR UPDATE([UnitPriceDiscount])

And UPDATE([OrderQty]) is one of these actions.

This trigger will then update Person.Person two times.

One of these Update statements:

  UPDATE [Person].[Person]
            SET [Demographics].modify('declare default element namespace
                "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey";
                replace value of (/IndividualSurvey/TotalPurchaseYTD)[1]
                with data(/IndividualSurvey/TotalPurchaseYTD)[1] + sql:column ("inserted.LineTotal")')
            FROM inserted
                INNER JOIN [Sales].[SalesOrderHeader] AS SOH
                ON inserted.[SalesOrderID] = SOH.[SalesOrderID]
                INNER JOIN [Sales].[Customer] AS C
                ON SOH.[CustomerID] = C.[CustomerID]
            WHERE C.[PersonID] = [Person].[Person].[BusinessEntityID];

And also Sales.SalesOrderHeader

 UPDATE [Sales].[SalesOrderHeader]
        SET [Sales].[SalesOrderHeader].[SubTotal] =
            (SELECT SUM([Sales].[SalesOrderDetail].[LineTotal])
                FROM [Sales].[SalesOrderDetail]
                WHERE [Sales].[SalesOrderHeader].[SalesOrderID] = [Sales].[SalesOrderDetail].[SalesOrderID])
        WHERE [Sales].[SalesOrderHeader].[SalesOrderID] IN (SELECT inserted.[SalesOrderID] FROM inserted);

Explaining the Exclusive locks on these objects.


How to use simple output parameters? I'm new to Oracle, so I apologize for such a simple question, but I've already wasted several hours on it.

I have the following stored procedure:

create or replace procedure MyTestProc (outparam OUT VARCHAR2) AS
begin
   select 'test' into outparam from dual;
end;

How do I call this stored procedure, and insert the value of outparam  into a variable? I'd like to be able to do this so I can then use the result for other things, e.g., printing it to output.

Using functions, I was able to do this as easy as:

variable tmpString VARCHAR2(100);
call MyTestFunc('test') INTO :tmpString;
print tmpString;

How do I do this with stored procedures?

If you are still using SQL*Plus variables

variable tmpString VARCHAR2(100);
begin
  myTestProc( :tmpString );
end;
/
print tmpString;

If you want to do it in PL/SQL

SET SERVEROUTPUT ON;
DECLARE
  tmpString VARCHAR2(100);
BEGIN
  myTestProc( tmpString );
  dbms_output.put_line( tmpString );
END;


MySQL High I/O. Wrong configuration? Since I switched from MyISAM to InnoDB I have a high I/O.

The writes is a flat line, it seems there is a limit for them. This is my current mysql configuration:

[mysqld]
bind-address = 127.0.0.1
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
symbolic-links=0

# CACHES AND LIMITS #
tmp_table_size                 = 512M
max_heap_table_size            = 512M
query_cache_size               = 16M
query_cache_limit              = 1M
thread_cache_size              = 8
open_files_limit               = 8292
key_buffer                     = 512M
table_cache                    = 512M
sort_buffer_size               = 4M
read_buffer_size               = 4M
read_rnd_buffer_size           = 16M
table_open_cache               = 512M

thread_concurrency=4
query_cache_size = 16M

slow_query_log=1
slow_query_log_file=/var/log/mysql/mysql-slow.log
long_query_time=1

#disable federated by default
skip-federated

innodb_file_per_table=1
innodb_buffer_pool_size = 1G
innodb_additional_mem_pool_size = 20M
innodb_log_file_size = 128M
innodb_log_buffer_size = 16M
innodb_flush_log_at_trx_commit = 0
innodb_lock_wait_timeout = 50

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

I have 2 cpu's and 4GB RAM. My database is around 35GB. What can I do to optimize my configuration? Buy more resources?

Let me know if I need te add more information about my server or configuration.

Definitely more ram. Regardless how you configure it - and your cache limits are way too low configured - on a 4gb machine as dedeicated db server you never will have more than maybe 3.5gb cache. With a 35gb database - unless it is mostly data you dont like - that is a pretty crappy relationship (10:1) which means the cache wont really help a lot. Again - unless your data is mostly stale and rarely used.

With a databbase that small - and 35gb is quite tiny - I would try to keep 100% in cache.

But in your current case.... you only have 512mb cache permission, if I read that right. That is REALLY tiny given the database size.

Disclaimer: I am a SqlServer guy - not MySql, but physics and math is the same regardless of program. I regularly deal with non-small databases (100-1000gb) and up to very large databases - my record size is 21tb.


Eliminating OIDs while upgrading PostgreSQL from 9.4 to 12 I'm currently running PostgreSQL 9.4 in my production environment, but we're in the process of doing an OS upgrade on the server so I thought it might be time to upgrade our PostgreSQL version to the latest (12) so that I could potentially make use of some of the new features available. However, several of the tables in the various databases were built quite a while ago (as in, pre-2008, and quite possibly pre-2000 in at least some cases). These databases contain many tables using OIDs (WITH OIDS=TRUE in the definition).

Additionally, I've run a search through a majority of my code base to look for references to OID columns in any table and found a couple of instances where there are queries that explicitly call on the OID columns. Luckily, there aren't too many of these cases, and a majority of them are referencing system tables (i.e., SELECT oid FROM pg_namespace, ...FROM pg_class).

Running the pg_upgrade on the data dumped from our 9.4 database balks at this point in the process and flatly states that it won't continue until the database is free of OIDs. I understand that they've been slowly phasing these out over the past few releases, and that 12 has made them basically irrelevant, but the suggestion from pg_upgrade is to drop the OID columns entirely before proceeding. I consider that to be "overkill" for a mostly irrelevant column.

At this point - and I readily admit that I haven't thought this all the way through yet - I'm wondering if it's sufficient to simply "flip" the WITH OIDS switch in the affected table definitions to FALSE to be able to proceed with the upgrade? Will the existing OID columns remain a part of the database structure if this is flipped (I would think they would, but automated upgrades can do some funny things)?

I realize that, in an ideal situation, I would want to eventually eliminate OIDs from the database completely. However, being a one-man IT department, that's going to have to go "on the list" for later evaluation. For now, I'd simply like to get the database up-and-running on the latest PostgreSQL version while I have the opportunity to do so without immediately affecting operations.

If the oids of the tables are referenced in the code, you cannot simply get rid of them. Probably the old code relies on oid as an auto-generated identifier. Are there any primary keys or other indexes on these columns?

A way to preserve the oids over an upgrade would be:

Before upgrade, on the old database:

ALTER TABLE has_oids ADD newoid bigint NOT NULL;
UPDATE has_oids SET newoid = oid;
ALTER TABLE has_oids SET WITHOUT OIDS;

After upgrade, on the new database:

ALTER TABLE has_oids RENAME newoid TO oid;
CREATE SEQUENCE has_oids_oid_seq OWNED BY has_oids.oid;
ALTER TABLE has_oids ALTER oid SET DEFAULT nextval('has_oids_oid_seq');
SELECT setval('has_oids_oid_seq', ???);

Here, ??? is a number higher than the highest oid present in the table.

You'd also have to take care of indexes or constraints on the oid column.


How do I optimise XPath Querying over a large dataset? I have a table that has 220 M rows. Also the table has a xml column that has XML data. I have configured single file for data and another ndf for index. Table's primary key is guid column. When am querying the XML contents using xpath query and load the flatten XML data to another table it takes around 30 hrs to load the data. Since am in standard edtition, partition is not an option. Need some advice on same
1. how to improve data load performance.
2. I have a non-clustered index configured on the table which will be rebuilt after load.

Create an XML index on the column, you will have to create a primary index and then a secondary index on the path or value you are querying.
CREATE PRIMARY XML INDEX PXML_NAME ON SCHEMA.TABLE (XMLCOLUMN);
then create a secondary index for the { VALUE | PATH | PROPERTY } you are querying with Xpath

https://msdn.microsoft.com/en-us/library/ms191497.aspx and
https://msdn.microsoft.com/en-us/library/bb934097.aspx


Oracle 11g http listener configuration for PL/SQL server pages Could please someone point me how to configure oracle db to be able to display PL/SQL Server pages. I have successfully created and mapped dad with the DBMS_EPG.

Here is the listener:

# listener.ora Network Configuration File: D:\app\Ja\product\11.2.0\dbhome_1\NETWORK\ADMIN\listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    )
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 8080))
    )
  )

ADR_BASE_LISTENER = D:\app\Ja

When I want to enter

  localhost:8080/my_dad/home

I get error: No data received.

How do I solve this?

check tnsnames.ora file

C:\oracle\product\10.2.0\Oracle\NETWORK\ADMIN

ORCL=
   (DESCRIPTION =
       (ADDRESS_LIST =
             (ADDRESS =
                  (PROTOCAL = TCP)
                  (HOST = 123.456.789.123)
                  (PORT = 1521)
             )
       )
       (CONNECT_DATA = (SID = ORCL))
   )


Snapshot replication - How to detect when a snapshot has arrived on the Subscriber from the Subscriber DB I have snapshot replication set up on a server which publishes to a second server as part of a scheduled SQL Server Agent job. When the snapshot finishes, I would like to detect when each table has been updated or when all tables have been updated.

The best solution I have found so far is to put a flag into a placeholder table before the SQL Server Agent job which is replicated to the second server and then have a job on the second server that runs every minute to check to see whether a new snapshot has arrived.

Does anyone have a better solution or a way to identify when a snapshot has been processed onto a new server?

Change it to a pull subscription, and watch for the SQL Agent job to stop.  When it stops the snapshot has been applied.  Or you could get really creative and setup a linked server to the distributor if you want to keep it as a pull subscription and watch for the job on the distributor to stop.


Restriction trigger to users I'm trying to create a logon trigger that will restrict users views so that one user can select/ view jobs relating to them and only to them within the JOB table.

Additionally these new users must only have the ability to select the job table whereas users who have a REG_DATE > 1 year for instance will be able to edit job details within the JOB TABLE.

I don't know what the trigger should look like hence the reason i'm posting,  also is a trigger ideal for this instance or should look towards using grants?

-----Below are my tables

    CREATE TABLE  tblss_user(
    user_id       INTEGER NOT NULL,
    user_name     VARCHAR(15),
    reg_date      DATE

user_id = pk

    CREATE TABLE job(
    job_id      INTEGER NOT NULL,
    job_name    VARCHAR(25),
    job_salary  INTEGER NOT NULL,
    fkuser_id   INTEGER NOT NULL

jobid = pk

fkuser_id = fk

thanks for your time!

And that is what we have VPD (Virtual Private Database) for, a feature that developers like to reinvent.

Using Oracle Virtual Private Database to Control Data Access

Set up environment:

create user app identified by app default tablespace users
  quota unlimited on users account lock;
grant create table to app;

create user u1 identified by u1;
create user u2 identified by u2;
create user u3 identified by u3;
grant create session to u1, u2, u3;

CREATE TABLE  app.tblss_user(
user_id       INTEGER NOT NULL,
user_name     VARCHAR2(15),
reg_date      DATE
);

CREATE TABLE app.job (
    job_id      INTEGER NOT NULL,
    job_name    VARCHAR(25),
    job_salary  INTEGER NOT NULL,
    fkuser_id   INTEGER NOT NULL);

grant select, update on app.job to u1, u2, u3;

insert into app.tblss_user values (1, 'U1', sysdate - 400);
insert into app.tblss_user values (2, 'U2', sysdate);
commit;

insert into app.job values(1, 'JOB1', 1000, 1);
insert into app.job values(2, 'JOB2', 1200, 1);
insert into app.job values(3, 'JOB3', 800, 2);
commit;

Our data:

SQL> select * from app.tblss_user;

   USER_ID USER_NAME       REG_DATE
---------- --------------- ---------
         1 U1              24-JAN-17
         2 U2              28-FEB-18

SQL> select * from app.job;

    JOB_ID JOB_NAME                  JOB_SALARY  FKUSER_ID
---------- ------------------------- ---------- ----------
         1 JOB1                            1000          1
         2 JOB2                            1200          1
         3 JOB3                             800          2

Now create the security functions providing the predicates:

create or replace function app.job_select_security (p_owner varchar2, p_table_name varchar2)
return varchar2 as
  l_user_id number;
begin
  if sys_context('userenv', 'session_user') = 'APP' then return null; end if;
  begin
    select user_id into l_user_id from tblss_user
      where user_name = sys_context('userenv', 'session_user');
    exception when no_data_found then return '1=2';
  end;
  return 'fkuser_id = ' || l_user_id;
end;
/

grant execute on app.job_select_security to u1, u2, u3;

create or replace function app.job_update_security (p_owner varchar2, p_table_name varchar2)
return varchar2 as
  l_user_id number;
  l_reg_date date;
begin
  if sys_context('userenv', 'session_user') = 'APP' then return null; end if;
  begin
    select user_id, reg_date into l_user_id, l_reg_date from tblss_user
      where user_name = sys_context('userenv', 'session_user');
    exception when no_data_found then return '1=2';
  end;
  if l_reg_date > sysdate - 365 then return '1=2'; end if;
  return 'fkuser_id = ' || l_user_id;
end;
/

grant execute on app.job_update_security to u1, u2, u3;

Create the policies using the above functions:

BEGIN
  DBMS_RLS.add_policy('APP', 'JOB', 'JOB_SELECT_POLICY',
                      'APP', 'job_select_security',
                      'SELECT', TRUE);
end;
/

begin
  DBMS_RLS.add_policy('APP', 'JOB', 'JOB_UPDATE_POLICY',
                      'APP', 'job_update_security',
                      'UPDATE', TRUE);
END;
/

And now test. Selecting:

SQL> conn u1/u1
Connected.
SQL> select * from app.job;

    JOB_ID JOB_NAME                  JOB_SALARY  FKUSER_ID
---------- ------------------------- ---------- ----------
         1 JOB1                            1000          1
         2 JOB2                            1200          1

SQL> conn u2/u2
Connected.
SQL>
SQL> select * from app.job;

    JOB_ID JOB_NAME                  JOB_SALARY  FKUSER_ID
---------- ------------------------- ---------- ----------
         3 JOB3                             800          2

SQL> conn u3/u3
Connected.
SQL> select * from app.job;

no rows selected

Updating:

SQL> conn u1/u1
Connected.
SQL> update app.job set job_salary=1400;

2 rows updated.

SQL> select * from app.job;

    JOB_ID JOB_NAME                  JOB_SALARY  FKUSER_ID
---------- ------------------------- ---------- ----------
         1 JOB1                            1400          1
         2 JOB2                            1400          1

SQL> conn u2/u2
Connected.
SQL> update app.job set job_salary=1400;

0 rows updated.

SQL> select * from app.job;

    JOB_ID JOB_NAME                  JOB_SALARY  FKUSER_ID
---------- ------------------------- ---------- ----------
         3 JOB3                             800          2

SQL> conn u3/u3
Connected.
SQL> update app.job set job_salary=1400;

0 rows updated.


Database design issue, with multiple variables and constraints I'm using PostgreSQL. I have the following entities Categories, Items, Attributes.

Relationships:

M2M between categories and items (an item can be in multiple categories, a category can have multiple items)
FK between Items and Attributes (an item can have multiple attributes)
M2M between Categories and Attributes (a category can have multiple attributes, an attribute can be find in multiple categories)

The design issues:

constraint: A category can have multiple attributes. An item can have multiple attributes, but only the ones linked to the categories in which the item is assigned.

The attributes have values. There are 2 types of attributes, integers or lists. Example:

Item attributes with integer values:

- length: 32

- strength: 44

The values is only dependent on the product.

Item attributes with string list values:

mode: full or mode: half, or mode: half,full

The values for string list attributes, like mode are a predefined list. An item that have the attribute mode can have one or many of the predefined values.

I want to use the attributes as advanced search:

in case of attributes with integer values(for items), check if the values if is between a minim and/or max value
It is similar to:

in case of attributes with values as a string list, check if at least one of the selected values in the search is also found in the attributes values for the product. It is similar to:

In a way similar to search filter on: ebay, but
mostly with min_max filters a less list checkbox filters.

I don't exactly know how to model the attributes connection and values, to keep them dynamic(add/remove attributes without changing the database structure)

I don't know in which category(es) a product will be, so adding attributes as columns I don't think is a viable option. Besides that some of them being lists add to the problem(I though about using json for their product values) but still maintain a sort of constraint regarding values available.

Here is one approach I use this model.  I suggest being careful with attributes,  do not turn everything into an attribute,  I have dealt with DB models that use such an approach several times.  It makes what should be simple query a mess, and means tracking types, and lots of casting to and from data types.

treat the below a psuedo code just to get the ball going on conversation

create table item (
    item_id serial primary key,
    item_descrip text,
    item_uom_id int, --UOM = unit of Measure another table not described
    item_list_price);

create table atts (
    atts_id serial primary key,
    atts_name char(55),
    att_value char(85),
    atts_notes text
);

create table cats (
    cats_id serial primary key,
    cats_name char(55),
    cats_notes text);

create table links_cat_item_atts (
    lcia_id serial primary key,
    lcia_type char(4),
    lcia_link_id  int,
    lcia_cat_id int default NULL,
    lcia_att_id int default NULL,
        CONSTRAINT link_type_con CHECK (lcia_link_id = 'ITEM'
        OR lcia_link_id = 'CATS' OR lcia_link_id = 'ATTS')) ;

--to use this most simple quiers to figure out the what is linked to what

Select * from item
    inner join links_cat_item_atts on item_id = lcia_link_id and lcia_type = 'ITEM'
    inner join cats on lcia_cat_id = cats_id
    inner join atts on lcia_att_id = atts_id

-- to pull cats with attributes this

Select * from cats
    inner join links_cat_item_atts on cat_id = lcia_link_id and lcia_type = 'CATS'
    inner join atts on lcia_att_id = atts_id

-- built a list of attributes that are for cats you can do this

Select * from atts where atts_id in (select lcia_att_id  from links_cat_item_atts
                                    where  lcia_link_id = LIMIT_ID
                                    and  lcia_type = 'CATS' )

--this holds true for items to build a list of attribiutes that are for specific item
Select * from atts where atts_id in (select lcia_att_id  from links_cat_item_atts
                                    where  lcia_link_id = LIMIT_ID
                                     and  lcia_type = 'ITEM' );

-- you can go back and add FK constraints and other things if you choose.

-- I use this model for  several things
-- do not treat price as a attribute this will just muddy the waters
-- if you do treat everything as attribute it means you have to track its data
--type in the database

Select * from item, atts, cats, links_cat_item_atts
    inner join links_cat_item_atts on item_id = lcia_link_id and lcia_type = 'ITEM'
    inner join cats on lcia_cat_id = cats_id
    inner join atts on lcia_att_id = atts_id
    where item_price between 10 and 50
    and cats_name = ' hi there cats '
    and atts_value = 'filter on atts' ;


Excluding Read only Log backups using ola-hallengren script I know that Full backups are excluded from readable secondaries, but how do I exclude log backups from read-only secondaries?

The is_read_only column in sys.databases does not get set on secondaries, so that is not an option.

This link talks about the is_read_only column but is not authoritative enough.

The main restrictions on backups in an Availability Group are:

Full backups on a secondary must be COPY_ONLY.
Differential backups cannot be taken on a secondary.

Your backup application should take into account the backup preference setting of your availability group, but it is not forced to do so. Ola's scripts do respect this setting and only backup on the preferred replica.

If you're seeing log backups on your secondary, check the backup preferences in the AG, as they may have Prefer Secondary set, which means log backups could happen on any replica. Change this to Primary Only and you won't see log backups on your secondary with Ola's scripts.

NOTE: is_read_only in "sys.databases" only indicates if the database is in READ_ONLY or READ_WRITE mode. This is not the same as a readable AG secondary database. See this link for more info.


How to fill gaps with calendar table, count and group by I'm building a bar chart. I would like a query which counts the entries from a particular day and groups them together as one row with a count. I have the following query working and I have a working date dimension table which I would like to try to join with to fill in the gaps. It would also be nice to have min and max variables to easily alter the span (1 week, 1 month, 1 year, or from start of this year etc).

I got a variant working with a CTE query but it was painfully slow (1min+). I can't seem to figure this out, help!

The Date Dimension table was created following this guide: https://www.mssqltips.com/sqlservertip/4054/creating-a-date-dimension-or-calendar-table-in-sql-server/

CREATE TABLE dbo.DateDimension
(
DateKey             INT         NOT NULL PRIMARY KEY,
[Date]              DATE        NOT NULL,
[Day]               TINYINT     NOT NULL,
DaySuffix           CHAR(2)     NOT NULL,
[Weekday]           TINYINT     NOT NULL,
WeekDayName         VARCHAR(10) NOT NULL,
IsWeekend           BIT         NOT NULL,
IsHoliday           BIT         NOT NULL,
HolidayText         VARCHAR(64) SPARSE,
DOWInMonth          TINYINT     NOT NULL,
[DayOfYear]         SMALLINT    NOT NULL,
WeekOfMonth         TINYINT     NOT NULL,
WeekOfYear          TINYINT     NOT NULL,
ISOWeekOfYear       TINYINT     NOT NULL,
[Month]             TINYINT     NOT NULL,
[MonthName]         VARCHAR(10) NOT NULL,
[Quarter]           TINYINT     NOT NULL,
QuarterName         VARCHAR(6)  NOT NULL,
[Year]              INT         NOT NULL,
MMYYYY              CHAR(6)     NOT NULL,
MonthYear           CHAR(7)     NOT NULL,
FirstDayOfMonth     DATE        NOT NULL,
LastDayOfMonth      DATE        NOT NULL,
FirstDayOfQuarter   DATE        NOT NULL,
LastDayOfQuarter    DATE        NOT NULL,
FirstDayOfYear      DATE        NOT NULL,
LastDayOfYear       DATE        NOT NULL,
FirstDayOfNextMonth DATE        NOT NULL,
FirstDayOfNextYear  DATE        NOT NULL
);

Current query:

select count(*) as total, dateadd(DAY,0, datediff(day,0, CreatedAt)) as created
from Table1
group by dateadd(DAY,0, datediff(day,0, CreatedAt))
order by dateadd(DAY,0, datediff(day,0, CreatedAt)) desc

total   created
1       01/11/2017 00:00:00
16      01/03/2017 00:00:00
1       12/27/2016 00:00:00
1       12/20/2016 00:00:00
1       11/30/2016 00:00:00
1       11/29/2016 00:00:00
11      11/28/2016 00:00:00
13      11/25/2016 00:00:00
4       11/24/2016 00:00:00
2       11/22/2016 00:00:00

I want something like this

total   created
1       01/11/2017 00:00:00
0       01/10/2017 00:00:00
0       01/09/2017 00:00:00
0       01/08/2017 00:00:00
0       01/07/2017 00:00:00
0       01/06/2017 00:00:00
0       01/05/2017 00:00:00
0       01/04/2017 00:00:00
16      01/03/2017 00:00:00
etc

First, get your list of dates from the dimension. Just specify the start and end you need - let's say all of January, since you should have at least a gap at the end:

DECLARE @Start date = '20170101',
  @End date = DATEADD(MONTH,1,'20170101');

SELECT [Date] FROM dbo.DateDimension
  WHERE [Date] >= @Start AND [Date] < @End;

You should see 31 rows here. Now, use an outer join to pull in the rows you have aggregates for:

DECLARE @Start date = '20170101',
  @End date = DATEADD(MONTH,1,'20170101');

SELECT created = d.[Date], total = COALESCE(s.total, 0)
  FROM dbo.DateDimension AS d
  LEFT OUTER JOIN
  (
     SELECT [Date] = CONVERT(date, CreatedAt),
            total  = COUNT(*)
     FROM dbo.whatever
     WHERE CreatedAt >= @Start
       AND CreatedAt < @End
     GROUP BY CONVERT(date, CreatedAt)
  ) AS s ON d.[Date] = s.[Date]
  WHERE d.[Date] >= @Start
    AND d.[Date] < @End
  ORDER BY d.[Date];


Work around average behaviour with nulls I have two tables:

Product (PK product_id, brand, model)
Order (PK invoice_id, customer_name, customer_email, FK product_id)

I am trying to find the product(s) whose number of times that product is sold is closest to the average number of times a product is sold.

The problem is as far as I know the AVG function ignores NULL values when it calculates the average. So if a product has never been sold before, it won't be included in the average, but it should be included for my calculation to be correct. A null should be counted as zero to lower the average.

How can I work around this?

To count NULL like 0 when calculating an average, use

avg(coalesce(colname, 0))

coalesce will return the first of its arguments that is not NULL.


Update statement with strange side effects to data The problem:
I am trying to add some logic to a database refresh job that will de-identify PHI-related fields, and have come across a strange side effect. I am attempting to exclude a group of patients, in order to allow that set to see their true information, and mask all of the other sets. When I run my update statement, it also updates a large portion of that set I was trying to exclude. Why is that, and how can I change my update statement to avoid that behavior?

The data model:

Patients table - ID as the key, and GroupID to differentiate groups of patients. Also has an IsDeleted field (bit) to indicate soft deletion.t item
Phone table - ID as the key, and has three business fields: AreaCode, PhoneNumber, and Extension. Also has an IsDeleted field (bit) to indicate soft deletion.
PatientPhone - Has an ID as the key, but also uses the combination (PatientID, PhoneID) to link these tables.

The relationship:
Patient to PatientPhone - 1 to Many
Phone to PatientPhone - 1 to Many

In order to validate before and after, I have been using this query to sample the phone numbers by grouping the first couple of digits. It gives me a wide distribution of values, as I would expect:

SELECT
P.GroupID, left(PH.AreaCode, 2) as ShortAreaCode, left(PH.PhoneNumber, 2) as ShortPhoneNumber,
    left(PH.Extension,2) as ShortExtension, count(*)
FROM dbo.Patient P
JOIN dbo.PatientPhone PP
ON P.ID = PP.PatientID
JOIN dbo.Phone PH
ON PP.PatientID = PH.ID
WHERE P.GroupID = 12345
GROUP BY P.GroupID, left(PH.AreaCode, 2), left(PH.PhoneNumber, 2), left(PH.Extension,2)
ORDER BY count(*) desc, P.GroupID, ShortAreaCode, ShortPhoneNumber, ShortExtension

I then run this update statement against the Phone table to default every other patient's phone number to 555-5555:

   UPDATE
        PH
        SET AreaCode = '555', PhoneNumber = '5555555', Extension = NULL
        FROM dbo.Phone as PH
        INNER JOIN dbo.PatientPhone as PP
        on PH.ID = PP.PhoneID
        INNER JOIN dbo.Patient as P
        on P.ID = PP.PatientID
        WHERE P.GroupID <> 12345 --This is the GroupID where we want to retain phone numbers

However, when I re-run my validation query, I see a couple of problems:

Thousands of records I did NOT want to update were updated.
Many of the other groups of Patients were NOT updated.

(Sample of the output afterwards):

In theory, the data model allows for phones to be related to more than one patient. We did have some issues in the past, and most of the phone numbers have been denormalized. I need help to understand:

How I can explain this side effect of the update statement
How I can adjust my update logic to exclude any 'shared' phone numbers, so that the set of patients not in one particular GroupID will have their phones defaulted

I would tend to believe the data (phones being shared) is likely the cause of this problem, but I am having trouble proving that. Can someone help me with this? Thanks for your time.

In the select, you're using a different table - dbo.phone_Copy. The update is running on dbo.phone.


Count up numbers from a specific column Hi there was wondering if someone could help me out, and can give me a little code example.
i have a table named "projecten", and columns named "pstatus and pafgifte" these columns have numbers only.
I would like to count up all "pafgifte" with "pstatus = 2" only, and echo result.

help would be appreciated.

Like this:

select sum(case when pstatus=2 then pafgifte else 0 end) pafgifte_total
from projecten

Assuming you mean to add the numbers together.


Query Processing & The Buffer Pool When SQL Server Server 2008r2 begins to process a query, I know it needs to parse the SQL Text and create the plan, plus a few other steps.

It is doing all of that in memory OUTSIDE of the buffer pool, correct?

The buffer pool does not come into play until the query is actually being executed to retrieve/manipulate data, correct?

Scenario: (server was not built on configured by me)

SQL Server 2008r2 SP1 - yes it needs to be patched, that is on the radar
96 GB of RAM
MAX Memory set to 92 GB
Buffer Pool sitting just below 80 GB used
PLE over 4 hours
Seeing resource_semaphore WAITS

If my understanding that the memory grant to process the query does not come out of the buffer pool, but the remaining available memory, reducing the MAX Memory should help re-mediate the WAIT type.  My thought is set MAX Memory at 72 GB.  Your thoughts?

Thanks!

When SQL Server 2008r2 begins to process a query, I know it needs to parse the SQL Text and create the plan, plus a few other steps. It is doing all of that in memory OUTSIDE of the buffer pool, correct?

No the memory is from inside the buffer pool. Generally memory required for such process would 'not be much' and this could be easily satisfied by buffer pool. Also note SQL Server might not grant all memory requested by the query in one go. If query requests memory which is of considerable amount SQL Server would grant minimum just to make sure query starts running.

  The buffer pool does not come into play until the query is actually being executed to retrieve/manipulate data, correct?

Buffer pool is always there starting from when plan is created to when query executes. Buffer pool in SQL Server 2008 R2 is sole one allocating memory for any request <= 8KB of memory page.

  If my understanding that the memory grant to process the query does not come out of the buffer pool, but the remaining available memory, reducing the MAX Memory should help re-mediate the WAIT type. My thought is set MAX Memory at 72 GB. Your thoughts?

No, reducing max server memory would be of help here. I would suggest you to look at query and the 'statistics optimizer is looking when it is preparing the query plan'. You have enough amount of RAM. Sometimes when you run a query with outdated stats it requests large amount of memory and ofcourse SQL Server is not going to grant all of it. It would provide minimum amount so that query at least starts running.

When SQL server creates a compiled plan, it calculates two memory grant parameters called "required memory" and "additional memory". The required memory is minimum memory SQL Server can give for sorting and hash operation and additional is what can be required by query to store temporary data which comes with huge sorts or spills. If Query start with minimum memory and requests more but SQL Server is not able to provide it all in on go query waits with wait type 'resource semaphore'. This does not means memory is less it means the statistics was outdated which forced SQL Server to create bad plan thus requesting more memory.

I suggest you read Understanding Query Memory Grants


OPENROWSET returns just one row Im trying to import values from a .csv file which has only one column. I keep getting all the values in just one row.

SELECT BulkColumn
FROM OPENROWSET (BULK 'C:\Temp\myfile.csv', SINGLE_CLOB) MyFile

Here is a sample of the first rows of the files content

Nummer
072XXXXXX63
072XXXXXX76
07XXXXXX66
072XXXXXX4

The OPENROWSET documentation describes the SINGLE_CLOB option as:

  returns the contents as a single-row, single-column rowset of type varchar(max)

So you will either need to split the string into multiple columns using the STRING_SPLIT function (or a similar method), use a format file to specify a row terminator for OPENROWSET, or use BULK INSERT, which allows you to specify a row terminator without the need to create a format file.

You could adapt the following code by customizing the FIELDTERMINATOR and ROWTERMINATOR as necessary for your input file. For example, the following code was tested on your provided sample file:

CREATE TABLE #bulkInsert (col1 NVARCHAR(MAX) NULL)

BULK INSERT #bulkInsert
FROM 'C:\Temp\myfile.csv'
WITH (FIELDTERMINATOR = N','
    , ROWTERMINATOR = N'0x0a' /* See https://stackoverflow.com/a/26758288/1582862 */
    , BATCHSIZE = 50000
    , TABLOCK
);

-- The sample rows were correctly bulk loaded, including the header row,
-- which you can remove from your file or filter out after bulk loading
SELECT *
FROM #bulkInsert
--Nummer
--072XXXXXX63
--072XXXXXX76
--07XXXXXX66
--072XXXXXX4


SQL Server LocalDB Instance; error restoring backup (MasterDBPath not available) I have SQL Server 2012 Local DB Installed, and am connecting to it via (localdb)\v11.0.

When trying to restore a backup file, I get:

  TITLE: Microsoft SQL Server Management Studio

  Property MasterDBPath is not available for Information
  'Microsoft.SqlServer.Management.Smo.Information'. This property may
  not exist for this object, or may not be retrievable due to
  insufficient access rights.  (Microsoft.SqlServer.Smo)

  For help, click:
  http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=11.0.2100.60+((SQL11_RTM).120210-1846+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.PropertyCannotBeRetrievedExceptionText&EvtID=MasterDBPath&LinkId=20476

I have tried setting MasterDBPath string value in:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL11E.LOCALDB\MSSQLServer\CurrentVersion`

but this hasn't helped.

Anyone have any thoughts?

I had the same problem, and solved it.

You need to add 2 strings in the Windows Registry via regedit.exe at
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL11E.LOCALDB\MSSQLServer:

DefaultData
DefaultLog

For me, their values are both C:\Users\neil\AppData\Local\Microsoft\Microsoft SQL Server Local DB\Instances\v11.0.


mysql session logging tables I've been building a session logging system for our sites.  We get around ~15m hits a year and at the moment we're really only interested in stats about people who logon to our sites.

Our table design looks like this:

db_sessions.tbl_sessions_2012 (2012 being current year, 2013 next year).

This is an InnoDB table, and has 9 indexes, 13 columns of generally varchar(varying) and INT, and 2 TEXT columns that record request URL and referrer URL. it looks like this:

CREATE TABLE `tbl_sessions_2012` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `sessionid` varchar(255) NOT NULL,
  `ipaddress` varchar(255) NOT NULL,
  `geocode` varchar(255) NOT NULL DEFAULT '''',
  `journalcode` varchar(255) NOT NULL,
  `logintypeid` int(1) NOT NULL DEFAULT ''0'',
  `accountid` int(11) NOT NULL DEFAULT ''0'',
  `contactid` int(11) NOT NULL DEFAULT ''0'',
  `organisationid` int(11) NOT NULL DEFAULT ''0'',
  `accountcollectionid` int(11) NOT NULL DEFAULT ''0'',
  `request` text,
  `referer` text,
  `isSSL` tinyint(1) NOT NULL DEFAULT ''0'',
  `isLogout` tinyint(1) NOT NULL DEFAULT ''0'',
  `actionTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `contactaccounts` (`contactid`,`accountid`,`journalcode`) USING BTREE,
  KEY `contactaccountcollections` (`contactid`,`accountcollectionid`,`journalcode`) USING BTREE,
  KEY `organisationaccounts` (`organisationid`,`accountid`) USING BTREE,
  KEY `organisationaccountcollections` (`organisationid`,`accountcollectionid`,`journalcode`) USING BTREE,
  KEY `contactaccountloginmonths` (`contactid`,`accountid`,`journalcode`,`logintypeid`,`actionTime`) USING BTREE,
  KEY `contactaccountcollectionloginmonths` (`contactid`,`accountcollectionid`,`journalcode`,`logintypeid`,`actionTime`) USING BTREE,
  KEY `organisationaccountloginmonths` (`organisationid`,`accountid`,`journalcode`,`logintypeid`,`actionTime`) USING BTREE,
  KEY `organisationaccountcollectionsloginmonths` (`organisationid`,`accountcollectionid`,`journalcode`,`logintypeid`,`actionTime`) USING BTREE,
  KEY `accountcollection` (`accountcollectionid`,`journalcode`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=3161119 DEFAULT CHARSET=utf8

We have two MySQL servers, one is running MySQL 5.5.24, and the other running 5.5.11 which stores all our other content.  We are currently writing the session logging to our 5.5.24 which also acts as our dev database.

So at the moment, this single table is getting written to on every hit of the website, ~1.2m a month.  We also want to be able to run queries on it, So have 2 views on the table depending on the login type.  We only ever run queries on the views.

With this, we are getting slowdowns on the admin section of our sites, where we wish to display stats about who is logging in.  None of us are DBAs, but we really need to improve the speed and efficency on this.  Are there any recommended practices for writing to a session logging table and reading from it at the same time.

Could the table design be better.  I went froma  split monthly design to a single yearly design.

We run a query like this:

SELECT count(id) as total, accountid FROM vw_ContactAccounts_2012 s
WHERE 1 AND accountid IN (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
GROUP BY accountid

on our admin pages to show whether someone has session statistics available.

First, from looking at your existing indexes and comparing them to your example query, you are missing an index on just accountid. The way MySQL handles indexes is left-most, meaning you can have a composite index like this:

KEY `contactaccounts` (`contactid`,`accountid`,`journalcode`) USING BTREE,

and run a query that looks for contactid and the contactaccounts index would be a potential index*. However doing a query on accountid will not utilize the index, because accountid is not the left-most column.

If you never search for contactid without an accountid, I would create the index like this:

DROP INDEX `contactaccounts` ON `tbl_sessions_2012`;
CREATE INDEX `accountscontact` ON `tbl_sessions_2012` (`accountid`,`contactid`,`journalcode`);

Now, analyzing your other indexes, the first of each set is redundant using the left-most rule, and can be dropped in favor of the second:

KEY `contactaccounts` (`contactid`,`accountid`,`journalcode`) USING BTREE
KEY `contactaccountloginmonths` (`contactid`,`accountid`,`journalcode`,`logintypeid`,`actionTime`) USING BTREE

KEY `contactaccountcollections` (`contactid`,`accountcollectionid`,`journalcode`) USING BTREE
KEY `contactaccountcollectionloginmonths` (`contactid`,`accountcollectionid`,`journalcode`,`logintypeid`,`actionTime`) USING BTREE

KEY `organisationaccounts` (`organisationid`,`accountid`) USING BTREE
KEY `organisationaccountloginmonths` (`organisationid`,`accountid`,`journalcode`,`logintypeid`,`actionTime`) USING BTREE

KEY `organisationaccountcollection` (`organisationid`,`accountcollectionid`,`journalcode`) USING BTREE
KEY `organisationaccountcollectionsloginmonths` (`organisationid`,`accountcollectionid`,`journalcode`,`logintypeid`,`actionTime`) USING BTREE

By having the duplicates you are using up a lot of space on indexes.

* I say potential because there is still the possibility that your index will not be used due to the amount of rows MySQL has to scan through. As your account_id IN () statement grows, MySQL will determine that it's faster just to do a full table scan regardless of the index.


SQL Server 2014 COUNT(DISTINCT x) ignores statistics density vector for column x For a COUNT(DISTINCT) that has ~1 billion distinct values, I'm getting a query plan with a hash aggregate estimated to have only ~3 million rows.

Why is this happening? SQL Server 2012 produces a good estimate, so is this a bug in SQL Server 2014 that I should report on Connect?

The query and poor estimate

-- Actual rows: 1,011,719,166
-- SQL 2012 estimated rows: 1,079,130,000 (106% of actual)
-- SQL 2014 estimated rows: 2,980,240 (0.29% of actual)
SELECT COUNT(DISTINCT factCol5)
FROM BigFactTable
OPTION (RECOMPILE, QUERYTRACEON 9481) -- Include this line to use SQL 2012 CE

-- Stats for the factCol5 column show that there are ~1 billion distinct values
-- This is a good estimate, and it appears to be what the SQL 2012 CE uses
DBCC SHOW_STATISTICS (BigFactTable, _WA_Sys_00000005_24927208)
--All density   Average Length  Columns
--9.266754E-10  8               factCol5
SELECT 1 / 9.266754E-10
-- 1079126520.46229

The query plan

Full script

Here is a full repro of the situation using a stats only database.

What I've tried so far

I dug into the statistics for the relevant column and found that the density vector shows an estimated ~1.1 billion distinct values. SQL Server 2012 uses this estimate and produces a good plan. SQL Server 2014, surprisingly, appears to ignore the very accurate estimate provided by the statistics and instead uses a much lower estimate. This produces a much slower plan that does not reserve nearly enough memory and spills to tempdb.

I tried trace flag 4199, but that did not fix the situation. Lastly, I tried to dig in to optimizer information via a combination of trace flags (3604, 8606, 8607, 8608, 8612), as demonstrated in the second half of this article. However, I wasn't able to see any information explaining the bad estimate until it appeared in the final output tree.

Connect issue

Based on the answers to this question, I have also filed this as an issue in Connect

The way the cardinality estimation is derived certainly seems counter-intuitive to me. The distinct count calculation (viewable with Extended Events or trace flags 2363 and 3604) is:

Notice the cap. The general logic of this seems very reasonable (there can't be more distinct values), but the cap is applied from sampled multi-column statistics:

DBCC SHOW_STATISTICS
    (BigFactTable, [PK_BigFactTable])
WITH
    STAT_HEADER,
    DENSITY_VECTOR;

That shows 2,980,235 rows sampled out of 3,439,431,721 with a density vector at the Col5 level of 3.35544E-07. The reciprocal of that gives a number of distinct values of 2,980,235 rounded using real math to 2,980,240.

Now the question is, given sampled statistics, what assumptions the model should make about the number of distinct values. I would expect it to extrapolate, but that isn't done, and perhaps deliberately.

More intuitively, I would expect that instead of using the multi-column statistics, it would look at the density on Col5 (but it doesn't):

DBCC SHOW_STATISTICS
    (BigFactTable, [_WA_Sys_00000005_24927208])
WITH
    STAT_HEADER,
    DENSITY_VECTOR;

Here the density is 9.266754E-10, the reciprocal of which is 1,079,126,528.

One obvious workaround in the meantime is to update the multi-column statistics with full scan. The other is to use the original cardinality estimator.

The Connect item you opened, SQL 2014 sampled multi-column statistics override more accurate single-column statistics for non-leading columns, is marked Fixed for SQL Server 2017.


how to using group concat distinct count in MySQL 5.7 Now I am using MySQL 5.7, I have some data like this:
user_id

63763
63763
NULL
16459,16535,16613,16615,42896,42911,62463,62464,63594,63763,63764,63765,63766,63769,63930,63934,63935
NULL
NULL

I want to concat the user_id and distinct , finnally count the result of distinct result(finally I find the result still have duplicate value), is it possible to complete it in one sql? I known distinct like this:
select group_concat(DISTINCT user_id SEPARATOR ',')
from report_summary
where statistic_time >= 1615824000000

how to count the result?

and now I found the result still have duplicate value using this sql:
select group_concat(DISTINCT bet_user_ids SEPARATOR ',')
from report_summary
where statistic_time >= 1616083200000
and statistic_time <= 1616169599999

the result is:
16459,16535,16613,16615,18627,42890,42896,46521,62463,62464,63591,63595,63763,63764,63765,63769,63934,63939,63940,42890,42896,42890,42896,62463,63763

the user 42890 have three times in the result.

There's no direct way to do this with one operator, but you can use the following trick to accomplish it:
select group_concat(DISTINCT user_id SEPARATOR ','),
    LENGTH(group_concat(DISTINCT user_id SEPARATOR ',')) - LENGTH(REPLACE(group_concat(DISTINCT user_id SEPARATOR ','), ',', '')) + 1 AS IdCount
from report_summary
where statistic_time >= 1615824000000

Basically it counts the number of commas (+ 1) in the list by subtracting the length of the string after removing the commas from the original string's length. I thought it was pretty nifty and found it in this StackOverflow answer.


SQLDriverConnect fails when attempting to connect to Failover_Partner SQLDriverConnect fails when attempting to connect to Failover_Partner.

Connection string being used:

Driver={SQL Native Client};Failover_Partner=tcp:dby;Server=tcp:dbx;
    Database=DB;Uid=user;Pwd=Pwd;Connect Timeout=300;Pooling=false

When we failover the database on dbx the next operation we perform SQLError(), which gives us the following information:

SqlState:  08S01
NativeError: 10054
MessageText: “TCP Provider: An existing connection was forcibly closed by
    the remote host."

At this point we disconnect then close off all open handles for that connection.

Then we call SQLDriverConnect using the same connect string.  When we only have a single open connection this call succeeds. When we have multiple open connections we receive the following error:

[Microsoft][SQL Native Client][SQL Server]Login failed for user 'user'.

On the dbx server the error log shows:

Logon   Error: 18456, Severity: 14, State: 38.
Logon   Login failed for user 'XSUser'. Reason: Failed to open the explicitly
    specified database. [CLIENT: 54.164.196.67]

On the dby server there are no new logs.

When we run with only one open connection the failover works fine.  We only have a problem when there are multiple open connections.

When we start up with dbx failed over, we successfully connect to dby on all connection with the connect string supplied. At this point, when we are connected to dby and we can fail over dby and all connections successfully connect to dbx.

We suspected there might be a problem with connection pooling.  However, I believe we have successfully disabled connection pooling:

SQLINTEGER pooling;
DBCRESULT rc = SQLGetEnvAttr(m_henv, SQL_ATTR_CONNECTION_POOLING ,
    &pooling, 0, NULL);

rc is 0, pooling is 0.

Any suggestions on how to proceed would be appreciated.

EDIT:

My testing procedure is to let the application connect and query some data.  Then with the application's connection still open, manually use Management Studio on the primary to Failover the database.  Then on the mirror server use Management Studio to run the same select stmt as the application, which succeeds.  At this point I release the application to execute a SELECT.  The call to SQLExecDirect fails, then I call SQLDriverConnect which also fails.  I would have expected the call to SQLDriverConnect to succeed since the SELECT in Management Studio succeeded.

When database mirroring fails over to the mirror machine, the server runs startup code on the database including recovery steps to ensure the consistency of transactions in the database.  This process can take some time; perhaps on the order of 10 to 20 seconds.

You need to wait for the database on dby to come online.  I would recommend retrying the connection several times, with a wait of a second or two in between.

Don't disable connection pooling, that will only cause you performance problems, and has nothing to do with database mirroring.


How much space is needed to reclaim space from a table in MyISAM MariaDB 10.x | MyISAM

My question is very similar to this one but regarding MyISAM.

I have a DB server that's running very low on disk space (2.5 GB free) and a MyISAM database with individual tables that are 3 GB and 8 GB in size.  I've successfully deleted entries from the tables so I'm confident that some of the space is white space but I'm not sure how much.

I've heard conflicting statements from folks regarding what happens when running OPTIMIZE TABLE.  Some people say it doesn't require any extra disk space... others tell me it will create a new table and copy individual rows into the new table... and only AFTER that's complete will it begin reclaiming disk space.

Can someone please clarify?

As far as I can tell, the documentation is not very clear on the subject.

From my experience, I'd say it always rebuilds fresh table files for data and index.

Intuitively, from what I know about MyISAM storage engine, I'd say it should be possible to run a full data file in place optimisation if and only if the rows have constant length. That is if you don't use columns with dynamic lengths (VARCHAR, TEXT, etc.).

Regarding index file, rebalancing the trees might be possible in place, but at a higher cost (duration).


What are static tables & dynamic tables? I heard the term "static table" when I'am learning about dirty reads in database.

Can anyone explain what is a "static table" and what is a "dynamic table"? Thanks in advance.

Static tables are the master tables that are populated with some
  canned data at the time of creation of the database in a typical
  system setup. Rather they have a pre defined set of data populated in
  them that hardly changes. There is no specific term like Static table
  and Dynamic table, but all tables that are used for inserts, updates
  and deletes with data getting modified can be termed as dynamic tables

Source: https://www.sqlservercentral.com/forums/topic/static-and-dynamic-table#post-663097


How to delete an unknown object? I want to create a new stored procedure but I got this error:

00955. 00000 -  "name is already used by an existing object"

The problem is I don't remember to have created an object with this name and, now, I would like to remove it (don't worry, it's not on my production database).

How can I find the type of this object in order to drop it correctly?

The DBA_OBJECTS view describes all the objects in the database. In it, you can find, by instance, the name of an object, its owner or its type.

Example:

SELECT owner, object_name, object_type FROM dba_objects WHERE object_name = 'MY_OBJECT'; -- Be careful, the name is case sensitive.

OWNER               OBJECT_NAME         OBJECT_TYPE
------------------- ------------------- -------------------
MY_USER             MY_OBJECT           PROCEDURE

Now, you can easily drop it:

DROP PROCEDURE MY_OBJECT;


Permission to create linked server I have a user who has DB_Owner permission on a particular database.
Now, that user needs to create a linked server for himself.
what level of permission do I need to grant to that user at the server level for him to create Linked Server?I don't want to grant him sysadmin access.
I gave following access:
Grant control server to login
grant alter any linked server to login

But didn't worked to create linked server.

There is no requirement for sysadmin to create a SQL Server Linked Server .

Assigning  the permissions ,ALTER ANY LINKED SERVER and ALTER ANY LOGIN to a login account  , allows the actions to complete.

  Create a Linked Server with master.dbo.sp_addlinkedserver.

  ALTER ANY LINKED SERVER permissions are required

Drop a Linked Server with master.dbo.sp_dropserver.
ALTER ANY LINKED SERVER permissions are required

Create the mapping of a local login with a remote server with master.dbo.sp_addlinkedsrvlogin

Code examples: Assigning permissions

USE master;
GRANT ALTER ANY LINKED SERVER TO MYLOGINACCT;
GO
USE master;
GRANT ALTER ANY LOGIN SERVER TO MYLOGINACCT;

--An example of adding a Linked Server
EXEC sp_addlinkedserver
@server='Server1',
@srvproduct='',
@provider='SQLNCLI',
@datasrc='Server1\instance1'

---An example of dropping a Linked Server
EXEC master.dbo.sp_dropserver ‘Server1’

Link: http://www.sqlserver-dba.com/2011/05/sql-server-linked-servers-and-user-permissions.html


Subquery in MS Query doesn't work I'm a beginner in SQL. I hope my question is not too stupid.

I'm trying to write a query in MS Query and want to see the results in Excel.
The problem is that MS Query doesn't accept my code. I get the message:

  table '(select' couldn't be added.

Here's the code:

SELECT
    T1.Jahr,
        T1.Monat,
        T1.Land_ID,
        T1.Kanal,
        T1.Items_per_HC,
        T1.Planstand,
        T1.Zeitraum,
        T2.Zeitraum,
        T2.LAND_ID,
        T2.KANAL,
        T2.IST_ITEMS_PER_HC_EXCL_EASY
FROM
(SELECT tPLAN.Jahr, tPLAN.Monat, tPLAN.Land_ID, tPLAN.Kanal,
        tPLAN.Items_per_HC, tPLAN.Planstand, tZ.Zeitraum
    FROM t_Plan_Items_per_HC tPLAN, t_PLAN_ZEITRAUM tZ
    WHERE tPLAN.Jahr = tZ.Jahr
    AND tPLAN.Monat = tZ.Monat) as T1
INNER JOIN a_ITEMS_PER_CALL_ACT_MONTH_KANAL_EXCL_EASY_HC AS T2
  ON (T1.Zeitraum=T2.Zeitraum
  AND T1.Land_ID=T2.LAND_ID
  AND T1.Kanal=T2.KANAL)

What is wrong?

I have to see the content of table t_Plan_Items_per_HC and table a_ITEMS_PER_CALL_ACT_MONTH_KANAL_EXCL_EASY_HC in only one table. They should be joined by Land_ID, Zeitraum, and KANAL.

UPDATE: Now I tried the same code in SQL Developer and it works. It seems to be an MS Query-specific problem. Unfortunately I have to solve this problem in MS Query so I still need help.

Your query can be simplified (regardless of SQL flavor) not to use an inner query (subselect). This is not tested, but it should be closer to what you need:

SELECT tPLAN.Jahr, tPLAN.Monat, tPLAN.Land_ID, tPLAN.Kanal, T1.tPLAN, tPLAN.Planstand,
    tZ.Zeitraum, T2.IST_ITEMS_PER_HC_EXCL_EASY
FROM t_Plan_Items_per_HC tPLAN
    JOIN t_PLAN_ZEITRAUM tZ ON tZ.Jahr = tPLAN.Jahr AND tZ.Monat = tPLAN.Monat
    JOIN a_ITEMS_PER_CALL_ACT_MONTH_KANAL_EXCL_EASY_HC AS T2
        ON (T2.Zeitraum=tZ.Zeitraum AND T2.Land_ID=tPLAN.LAND_ID AND T2.Kanal=tPLAN.KANAL)

Since all JOINs are INNER, you can just select from one of the tables and JOIN with each of the others using your criteria. Also, WHERE conditions like:

FROM t_Plan_Items_per_HC tPLAN, t_PLAN_ZEITRAUM tZ
    WHERE tPLAN.Jahr = tZ.Jahr
    AND tPLAN.Monat = tZ.Monat)

are not recommended. Use JOINs instead.


Ordering groups/partition windows by other column values I have a sample from a database I'm using in SQL server 2014 you can use here http://sqlfiddle.com/#!6/0d98e/1
It looks like this

+--------------+--------------------------+-----------------------------+------------+----+
| Event_number |          Venue           |            Date             | Attendance | rn |
+--------------+--------------------------+-----------------------------+------------+----+
|          247 | Air Canada Centre        | September, 21 2013 00:00:00 |      15504 |  1 |
|          215 | Air Canada Centre        | September, 22 2012 00:00:00 |      16800 |  2 |
|          296 | Allphones Arena          | November, 08 2014 00:00:00  |       9904 |  1 |
|          200 | Allphones Arena          | March, 03 2012 00:00:00     |      14537 |  2 |
|          311 | American Airlines Center | March, 14 2015 00:00:00     |      17160 |  1 |
|          266 | American Airlines Center | March, 15 2014 00:00:00     |      19324 |  2 |
|          344 | Amway Center             | December, 19 2015 00:00:00  |      14459 |  1 |
|          270 | Amway Center             | April, 19 2014 00:00:00     |      17000 |  2 |
|          322 | Arena Ciudad de México   | June, 13 2015 00:00:00      |      21036 |  1 |
|          298 | Arena Ciudad de México   | November, 15 2014 00:00:00  |      21000 |  2 |
+--------------+--------------------------+-----------------------------+------------+----+

The table is about sporting events that have returned to the same venue. I found the two most recent times an event took place at each Venue from another table and partitioned the results by date desc and then I selected the first two rows to produce this sample data.

What I want to do is order this information by Venue pairs (rn 1 and 2), with the Date desc where rn = 1 without splitting up the pairs.

So the result I am looking for would look like this:

+--------------+--------------------------+-----------------------------+------------+----+
| Event_number |          Venue           |            Date             | Attendance | rn |
+--------------+--------------------------+-----------------------------+------------+----+
|          344 | Amway Center             | December, 19 2015 00:00:00  |      14459 |  1 |
|          270 | Amway Center             | April, 19 2014 00:00:00     |      17000 |  2 |
|          322 | Arena Ciudad de México   | June, 13 2015 00:00:00      |      21036 |  1 |
|          298 | Arena Ciudad de México   | November, 15 2014 00:00:00  |      21000 |  2 |
|          311 | American Airlines Center | March, 14 2015 00:00:00     |      17160 |  1 |
|          266 | American Airlines Center | March, 15 2014 00:00:00     |      19324 |  2 |
|          296 | Allphones Arena          | November, 08 2014 00:00:00  |       9904 |  1 |
|          200 | Allphones Arena          | March, 03 2012 00:00:00     |      14537 |  2 |
|          247 | Air Canada Centre        | September, 21 2013 00:00:00 |      15504 |  1 |
|          215 | Air Canada Centre        | September, 22 2012 00:00:00 |      16800 |  2 |
+--------------+--------------------------+-----------------------------+------------+----+

I came up with a pseudo solution which was to basically concatenate each pair of rows into a single row, which I could then order by one of the chosen date columns. But I want to know if it's possible to do it while retaining pairs and for any future queries where I might want to sort partitioned/windowed results by specific guidelines.

Thanks for any help. Please let me know if I didn't provide enough information or if I was unclear in my explanation.

You can use the the max() with an over() clause partitioned by Venue to get the max date for each venue and then use that as the first column in the order by clause.

select T.Event_number,
       T.Venue,
       T.Date,
       T.Attendance,
       T.rn
from (
     select S.Event_number,
            S.Venue,
            S.Date,
            S.Attendance,
            S.rn,
            max(S.Date) over(partition by S.Venue) as MaxDate
     from SampleTable as S
     ) as T
order by T.MaxDate desc,
         T.Venue,
         T.rn;


Why db2data folder changes its size (increasing and decreasing )? we have DB2 9.7. It has almost 30 GB.
Data are on this /opt/IBM/db2data/ctginst1/NODE0000/DB_NAME/Txxxxxx

I noticed that every day during the day (suppose because of the workload) DB change its size (partition /opt/IBM/db2data)
For example used 26 GB, 27 GB, 28 GB, then it returns to 26GB.

I noticed that on Linux when using
df -h.
Available space grows and then returns to old value.
How is this possible? Is this regular behavior? Also only one TABLESPACE has over 24 GB. Others are smaller. Shouldn't they be splatted equally or not?

Data are not deleted from database so that is not the reason why DB increase/decrease!

Temporary tablespaces grow and shrink as required. They can be used for temporary tables, sorts, or certain types of joins. I still think you should start reading manuals.


why is the name `loop` treated differently in PostgreSQL I issue the following DDL:

CREATE TABLE foo
(
loopa INTEGER,
loop  INTEGER
);

Then, examining the DDL in the server (using a client-side tool like DbVisualizer - sorry don't know the query to produce it directly) I see the following:

CREATE TABLE
    foo
    (
        loopa INTEGER,
        LOOP INTEGER
    );

Observe the change in case for the column named loop but not for the one named loopa. Looking at the list of SQL keywords loop doesn't appear to be a keyword. Why then, this selective change?

This seems to be related to a problem I am having with a trigger trying to access a column loop in a table where I get:

Exception in thread "main" org.postgresql.util.PSQLException: ERROR: record "new" has no field "loop"

(where I get the same error regardless of whether the code in the trigger function access the field as NEW.LOOP or NEW.loop)

UPDATE

The upper-casing seems to be an artifact of DbVisualizer. psql shows:

psql (9.1.13)
Type "help" for help.

foo-test=> \dS+ foo
                    Table "test.foo"
 Column |  Type   | Modifiers | Storage | Description
--------+---------+-----------+---------+-------------
 loopa  | integer |           | plain   |
 loop   | integer |           | plain   |
Has OIDs: no

Yet this still doesn't explain why the PostgreSQL engine apparently doesn't allow my trigger to access NEW.loop. When I rename the column (and the trigger code) to loopi everything appears to work fine. So, there is likely something deeper at work here than just a DbVisualizer glitch. I'll try to create a SSCCE later.

UPDATE 2

A SSCCE for the trigger problem has been created here.

The word LOOP is a reserved word in PL/pgSQL.  If you want to use it as a column name, you need to quote it.


Import data into AWS RDS sql server through SQL Server management studio I have created an RDS instance running SQL Server SE, this does not allow remote desktop access, so I also have an EC2 instance running Windows Server 2012 base, and I have installed SQL Server Management Studio and I'm able to connect and login using the master user and password.

I want to restore a database .bak file. So I used the SQL Server management studio and chose the option Databases ->  ‘Restore Database'. I chose ‘File’ as the backup media and then click ‘Add’. When I click on Add, it throws the below error:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo) The EXECUTE permission was denied on the object 'xp_fixeddrives', database 'mssqlsystemresource', schema 'sys'.

I looked into the internet for answers and some one suggested to grant sysadmin role to the master user. When I try to do that, I get an error saying "The user does not exist or you do not have permissions to do so"

I have run out of ideas on how to resolve the issue. Any help is greatly appreciated.

There are stored procedures that you can do on Rds

EXEC rds_backup_database 'DBName','.bak file path','','0'

EXEC rds_restore_database 'DBName','.bak file path',''

File path use S3.

Just to add more context for the op.

The RDS instance doesn't give you access to the underlying file system like an ec2 would, hence you have to use s3 buckets.

See here for full details:-
http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/SQLServer.Procedural.Importing.html


Sort Totals By Months I need to show the count of all rows grouped by month and then pivot it
Appreciate an easy solution for that

Or alternatively if someone can help me fix this

I've used dynamic pivot query

DECLARE @cols AS NVARCHAR(MAX);
DECLARE @query AS NVARCHAR(MAX);

select @cols = STUFF((SELECT distinct ',' +
                        QUOTENAME(DATENAME(MONTH, CONVERT(DATETIME, createDate)))
                      FROM OSCL
                      WHERE     ([status] = - 3)

                      FOR XML PATH(''), TYPE
                     ).value('.', 'NVARCHAR(MAX)')
                        , 1, 1, '');

SELECT @query =

'SELECT *
FROM
(
  SELECT
    callID,
    DATENAME(MONTH, CONVERT(DATETIME, createDate)) as Months
  FROM OSCL
  WHERE     ([status] = - 3)
) AS t
PIVOT
(
  COUNT(callID)
  FOR Months IN( ' + @cols + ' )' +
' ) AS p ; ';

 execute(@query);

This gives me data like this

April | February | March
44    | 1        | 5

But, I want to show in Sequence

January | February | March | April (Till the current month)
0       | 1        | 5     | 44

Table Example Is This
For Example

callID   |   U_Regn   |   createDate

What I Want To Show Is
A Calendar

             January | February | March | April
Database1 |   0      | 1        | 5     | 44
Database2 |   0      | 6        | 6     | 6

Change

select @cols = STUFF((SELECT distinct ',' +
                    QUOTENAME(DATENAME(MONTH, CONVERT(DATETIME, createDate)))
                  FROM OSCL
                  WHERE     ([status] = - 3)

                  FOR XML PATH(''), TYPE
                 ).value('.', 'NVARCHAR(MAX)')
                    , 1, 1, '');

to

select @cols = STUFF((SELECT ',' +
                    QUOTENAME(DATENAME(MONTH, CONVERT(DATETIME, createDate)))
                  FROM OSCL
                  WHERE     ([status] = - 3)
                  GROUP BY QUOTENAME(DATENAME(MONTH, CONVERT(DATETIME, createDate))), DATEPART(MONTH, CONVERT(DATETIME, createDate))
                  ORDER BY DATEPART(MONTH, CONVERT(DATETIME, createDate))

                  FOR XML PATH(''), TYPE
                 ).value('.', 'NVARCHAR(MAX)')
                    , 1, 1, '');


SQL: Can we make query to compare data for 2 different years in same month, but looking at the same day as priority FYI: I'm using SQL Server 2012.

Because scope of the function is a selling date which is different by the days of week (like comparison for customer visiting percentage).

So the main key to compare between last-this year, is to check the same week or same day that most related to the month and are same day (Monday, Tuesday, ...)

For example:

2018/11/01 should compare to 2017/11/02 (Thu)
2018/11/02 should compare to 2017/11/03 (Fri)
...
2018/11/30 should compare to 2017/12/01 (Fri)

I can't imagine the syntax out of my head.
What is syntax should I write?
What are the keywords, functions should I call to achieve this scope?

Anyone who has experience about some kind of function like this, please help.
All comments would be appreciate.

If I correctly understand your question, you can use two (ore more) ways but both are related with DATEPART function.

DECLARE @Date1 datetime = '20181101';
DECLARE @Date2 datetime = '20170101';

SELECT DATEPART(DAYOFYEAR, @Date1) DayOfYear,
       DATEPART(WEEK, @Date1) WeekNumber,
       DATEPART(WEEKDAY, @Date1) WeekDay,
       @@DATEFIRST FirstDayOfWeek;

DayOfYear | WeekNumber | WeekDay | FirstDayOfWeek
--------: | ---------: | ------: | :-------------
      305 |         44 |       5 | 7

Using DayOfYear

SELECT DATEADD(DAY, DATEPART(DAYOFYEAR, @Date1), @Date2) AS UsingDayOfYear;

| UsingDayOfYear      |
| :------------------ |
| 02/11/2017 00:00:00 |

Using same week & same day of week

This depends on first day of week:

SET DATEFIRST 1;

SELECT DATEADD(DAY, ((DATEPART(WEEK, @Date1) - 1) * 7) + DATEPART(WEEKDAY, @Date1), @Date2) UsingSameMonthSameDayOfWeek;

UsingSameMonthSameDayOfWeek |
| :-------------------------- |
| 02/11/2017 00:00:00         |

You can use that function to add another combination, for example: First Thursday of November.

db<>fiddle here


How to speed up mysql query? I have query :

SELECT
    `school`.`ARCO_name`,
    `student-`.`ClassSize_7`,
    `student-`.`ClassSize_8`,
    `degree_o`.`degree_code`,
    `accredit`.`full_faculty_3`,
    `accredit`.`total_faculty_3`,
    `accredit`.`pc_terminal`,
    `accredit`.`stud_fac_ratio`
FROM school
    INNER JOIN `student-` ON `school`.`scid`   = `student-`.`scid`
    INNER JOIN `degree_o` ON `student-`.`scid` = `degree_o`.`scid`
    INNER JOIN `accredit` ON `degree_o`.`scid` = `accredit`.`scid`
    ORDER BY `school`.`ARCO_name` ASC LIMIT 0, 25
;

It's working and everything but it need about 20 seconds to execute.
Is something wrong in my query?
What can I do to speed up process?

Here is your original query

SELECT
    `school`.`ARCO_name`,
    `student-`.`ClassSize_7`,
    `student-`.`ClassSize_8`,
    `degree_o`.`degree_code`,
    `accredit`.`full_faculty_3`,
    `accredit`.`total_faculty_3`,
    `accredit`.`pc_terminal`,
    `accredit`.`stud_fac_ratio`
FROM school
    INNER JOIN `student-` ON `school`.`scid`   = `student-`.`scid`
    INNER JOIN `degree_o` ON `student-`.`scid` = `degree_o`.`scid`
    INNER JOIN `accredit` ON `degree_o`.`scid` = `accredit`.`scid`
    ORDER BY `school`.`ARCO_name` ASC LIMIT 0, 25
;

Two Suggestions

Perform LIMIT 0, 25 on school table before the INNER JOINs
INNER JOIN school.scid to the other three(3) tables

Here is my propsed query

SELECT
    `school`.`ARCO_name`,
    `student-`.`ClassSize_7`,
    `student-`.`ClassSize_8`,
    `degree_o`.`degree_code`,
    `accredit`.`full_faculty_3`,
    `accredit`.`total_faculty_3`,
    `accredit`.`pc_terminal`,
    `accredit`.`stud_fac_ratio`
FROM
    (SELECT scid,ARCO_name FROM school ORDER BY ARCO_name LIMIT 0,25) school
    INNER JOIN `student-` ON `school`.`scid` = `student-`.`scid`
    INNER JOIN `degree_o` ON `school`.`scid` = `degree_o`.`scid`
    INNER JOIN `accredit` ON `school`.`scid` = `accredit`.`scid`
;

Give it a Try !!!

UPDATE 2012-06-25 12:13 EDT

To speed up the subquery, make sure you have this index:

ALTER TABLE school ADD INDEX ARCO_name_scid_ndx (ARCO_name,scid);

UPDATE 2012-06-25 12:18 EDT

AS pointed out by @yercube in his comments to my answer, you may want to attach the student-id from to degree_o and degree_id over to accedit. Perhaps something like this:

SELECT
    `school`.`ARCO_name`,
    `student-`.`ClassSize_7`,
    `student-`.`ClassSize_8`,
    `degree_o`.`degree_code`,
    `accredit`.`full_faculty_3`,
    `accredit`.`total_faculty_3`,
    `accredit`.`pc_terminal`,
    `accredit`.`stud_fac_ratio`
FROM
    (SELECT scid,ARCO_name FROM school ORDER BY ARCO_name LIMIT 0,25) school
    INNER JOIN `student-` ON `school`.`scid` = `student-`.`scid`
    INNER JOIN `degree_o` ON `student-`.`studend_id` = `degree_o`.`studend_id`
    INNER JOIN `accredit` ON `degree_o`.`degree_id` = `accredit`.`degree_id`
;


What is the overhead for an array in Postgres? What is the additional overhead of an array compared to a normal column of that same datatype? In other words, if an array will almost always have one value in it, how much space would I be "wasting" by using an array instead of a normal column?

You can check that using pg_column_size():

select pg_column_size(1::integer) as int_size,
       pg_column_size(array[1]::integer[]) as array_size

returns:

int_size | array_size
---------+-----------
       4 |         28

So the overhead is substantial for just a single value.


mysql query builder inefficiently builds a query I have a track and a waypoint tables. The track table is also connected to a bunch more tables that are irrelevent. I wish to get all tracks, where at least one of their waypoints is in specified bounds. The tracks should have all their waypoints, even ones outside the bounds. My query looks like this:

select <all columns>
from track inner join waypoint point1 on point1.track_id = track.id
      left outer join other table on track.other_table_id = other_table.id
           <a few more tables outer joined>
where track_id in(
   select distinct track_id
   from waypoint point2
   where MBRContains(<POlYGON HERE>, point2)
)

the efficient way to do this query would be first build a table of all track_ids from the subquery, then connect them to table entries, then left outer join other variables, and then inner join the waypoints from point1 table.

Instead, MySQL first gets a table of all tracks, then outer joins, then does a lookup by both track_id and mbrcontains function, and then joins the inner waypoints.

How can I tell mysql to optimize the query?

Short answer:  You need to rewrite the query.  Or maybe upgrade.

Long answer:

MySQL has long been dumb when it comes to IN ( SELECT ... ).

By using a derived table, the optimizer will do the subquery first, thereby being a lot more efficient:

SELECT ...
    FROM ( SELECT DISTINCT track_id
               FROM ...
               WHERE ... ) x
    JOIN ...
        ON x.track_id = ...

Or...  Upgrade to 5.7 (or 8.0) with its "query rewrite" so that you can rewrite the query after it is sent from whatever is building it.


Finding stored procedures with XACT ABORT ON OR OFF I am trying to troubleshoot blocking problems related to active open transaction with sleeping status and probably have found issues related to XACT ABORT option missing or as OFF read from many blog post online--

Is there a way to find what stored procs are missing those
statements for a database?
As SQL by default has this off, is there a harm enabling at SQL
    instance to ON or is it recommended to have only some SP to turn
    this on as. Why i am asking because as mentioned here it seems
    this should be added to all SP as best practice, is that true? if
    yes why not enable this at SQL instance level?

This query will show you all T-SQL modules (i.e. stored procedures, functions, etc) on a SQL Server instance that have XACT_ABORT in their code:
DECLARE @cmd nvarchar(max) ;
SET @cmd = N'';
SELECT @cmd = @cmd + CASE WHEN (@cmd = N'') THEN N'' ELSE N'UNION ALL
' END + N'SELECT ServerName = @@SERVERNAME COLLATE SQL_Latin1_General_CP1_CI_AS
    , db = ''' + d.name + N''' COLLATE SQL_Latin1_General_CP1_CI_AS
    , ObjectName = s.name + N''.'' + o.name COLLATE SQL_Latin1_General_CP1_CI_AS
    , [definition] = sm.definition COLLATE SQL_Latin1_General_CP1_CI_AS
FROM ' + QUOTENAME(d.name) + N'.sys.sql_modules sm
    INNER JOIN ' + QUOTENAME(d.name) + N'.sys.objects o ON sm.object_id = o.object_id
    INNER JOIN ' + QUOTENAME(d.name) + N'.sys.schemas s ON o.schema_id = s.schema_id
WHERE sm.definition LIKE N''%XACT_ABORT%'' COLLATE SQL_Latin1_General_CP1_CI_AS
'
FROM sys.databases d
WHERE d.database_id > 4
    AND d.state_desc = N'ONLINE'
ORDER BY d.name;
EXEC sys.sp_executesql @cmd;

One could rewrite that to show code without XACT_ABORT present, by simply adding NOT to the WHERE clause, as in:
DECLARE @cmd nvarchar(max) ;
SET @cmd = N'';
SELECT @cmd = @cmd + CASE WHEN (@cmd = N'') THEN N'' ELSE N'UNION ALL
' END + N'SELECT ServerName = @@SERVERNAME COLLATE SQL_Latin1_General_CP1_CI_AS
    , db = ''' + d.name + N''' COLLATE SQL_Latin1_General_CP1_CI_AS
    , ObjectName = s.name + N''.'' + o.name COLLATE SQL_Latin1_General_CP1_CI_AS
    , [definition] = sm.definition COLLATE SQL_Latin1_General_CP1_CI_AS
FROM ' + QUOTENAME(d.name) + N'.sys.sql_modules sm
    INNER JOIN ' + QUOTENAME(d.name) + N'.sys.objects o ON sm.object_id = o.object_id
    INNER JOIN ' + QUOTENAME(d.name) + N'.sys.schemas s ON o.schema_id = s.schema_id
WHERE sm.definition NOT LIKE N''%XACT_ABORT%'' COLLATE SQL_Latin1_General_CP1_CI_AS
'
FROM sys.databases d
WHERE d.database_id > 4
    AND d.state_desc = N'ONLINE'
ORDER BY d.name;
EXEC sys.sp_executesql @cmd;

However, you'll notice there are several possibilities for false positives:

If you have a column named XACT_ABORT in the code.
You have a comment such as /*  must not use XACT_ABORT in this code */

etc.
To be honest, your question sounds like an XY problem.  Are you sure you want to determine which code uses XACT_ABORT?  A stored procedure won't cause an open transaction to "hang around".  The most likely cause is a code window open in SSMS that has BEGIN TRANSACTION where the corresponding ROLLBACK or COMMIT haven't been executed.  Alternately, there could be some legitimate long-running code keeping transactions open.  One common issue that results in long running transaction is code using the RBAR method.
Regarding your second question, I'd be extremely reluctant to ever say a particular set statement should "always" be used, although I do tend to use SET XACT_ABORT = ON a lot.  Erland Sommarskog is very highly regarded in the SQL Server community, but I don't agree with this:

stored procedures should always include this statement in the beginning:
SET XACT_ABORT, NOCOUNT ON


Selecting max(date) from union query I am having a bit of trouble getting accurate results from a query that looks to me as if it should work. RDBMS is MySQL.

I am trying to get the max last login date from a union select on three databases for Atlassian products JIRA, Confluence and Stash. There should only be a single entry in the results for each user (the max(Last_Login) is supposed to get the maximum last login date and show a result if that max(Last_Login) is older than today - 90 days.

I summarize the output of the union query then select from that aggregate so that I can get the max login date that is 90 days ago or prior.

The problem: The query seems to work for the most part, but there are a handful of cases where a user shows activity in Confluence, Stash or JIRA interfaces that is more recent than what I have here.

Here is the query I am attempting to use:

select
    User_Name,
    First_Name,
    Last_Name,
    max(Last_Login) as Last_Login
FROM
(
SELECT
    u.user_name as User_Name,
    u.first_name as First_Name,
    u.last_name Last_Name,
    ifnull(from_unixtime((cast(a.attribute_value AS UNSIGNED)/1000), '%Y-%m-%d'), '1970-01-01') AS Last_Login
FROM jira.cwd_user u
  LEFT JOIN jira.cwd_user_attributes a ON u.id = a.user_id AND a.attribute_name = 'login.lastLoginMillis'
  JOIN jira.cwd_directory d ON u.directory_id = d.id
where u.active = 1
UNION
select
    u.user_name as User_Name,
    u.first_name as First_Name,
    u.last_name Last_Name,
    ifnull(from_unixtime((cast(ca.attribute_value AS UNSIGNED)/1000), '%Y-%m-%d'), '1970-01-01') AS Last_Login
FROM confluence.cwd_user u
  LEFT JOIN confluence.cwd_user_attribute ca ON u.id = ca.user_id AND ca.attribute_name = 'login.lastLoginMillis'
  JOIN confluence.cwd_directory d ON u.directory_id = d.id
where u.active = 'T'
UNION
select
    u.user_name as User_Name,
    u.first_name as First_Name,
    u.last_name Last_Name,
    ifnull(from_unixtime((cast(sa.attribute_value AS UNSIGNED)/1000), '%Y-%m-%d'), '1970-01-01') AS Last_Login
FROM stash.cwd_user u
LEFT JOIN stash.cwd_user_attribute sa ON u.id = sa.user_id AND sa.attribute_name = 'login.lastLoginMillis'
JOIN stash.cwd_directory d ON u.directory_id = d.id
where u.is_active = 'T'
) as z
group by User_Name, First_Name, Last_Name
having (max(Last_Login)) <= date_sub(now(), interval 90 day)
order by User_Name asc, Last_Login desc

So it turns out that confluence uses a different structure to manage this data and now the discrepancies appear to be resolved.

Here is the query:

select
    User_Name,
    First_Name,
    Last_Name,
    max(Last_Login) as Last_Login
FROM
(
SELECT
    u.user_name as User_Name,
    u.first_name as First_Name,
    u.last_name Last_Name,
    ifnull(from_unixtime((cast(a.attribute_value AS UNSIGNED)/1000), '%Y-%m-%d'), '1970-01-01') AS Last_Login
FROM jira.cwd_user u
  LEFT JOIN jira.cwd_user_attributes a ON u.id = a.user_id AND a.attribute_name = 'login.lastLoginMillis'
  JOIN jira.cwd_directory d ON u.directory_id = d.id
where u.active = 1
UNION
 SELECT
    u.user_name,
    u.first_name as First_Name,
    u.last_name as Last_Name,
    date_format(l.successdate, '%Y-%m-%d') as Last_Login
  FROM confluence.logininfo l
  JOIN confluence.user_mapping m ON m.user_key = l.username
  JOIN confluence.cwd_user u ON m.username = u.user_name
  JOIN confluence.cwd_directory d ON u.directory_id = d.id and u.active = 'T'
UNION
select
    u.user_name as User_Name,
    u.first_name as First_Name,
    u.last_name Last_Name,
    ifnull(from_unixtime((cast(sa.attribute_value AS UNSIGNED)/1000), '%Y-%m-%d'), '1970-01-01') AS Last_Login
FROM stash.cwd_user u
LEFT JOIN stash.cwd_user_attribute sa ON u.id = sa.user_id AND sa.attribute_name = 'login.lastLoginMillis'
JOIN stash.cwd_directory d ON u.directory_id = d.id
where u.is_active = 'T'
) as z
group by User_Name, First_Name, Last_Name
having (max(Last_Login)) <= date_sub(now(), interval 90 day)
order by User_Name asc, Last_Login desc


Defaulting a column to the 1st of next month I'm trying to find the most elegant means of defaulting a column's value to the 1st of next month. The best I've been able to come up with is this:

ALTER TABLE Foo ADD
Bar datetimeoffset(0) NOT NULL DEFAULT(DATEFROMPARTS(DATEPART(year, DATEADD(month, 1, GETDATE())), DATEPART(month, DATEADD(month, 1, GETDATE())), 1))

Whilst this works, it feels really clunky because I need to calculate DATEADD(month, 1, GETDATE()) twice and because I need to do the DATEFROMPARTS dance.

Is there a simpler way to achieve my goal?

It think this would be the simplest way to do it.

DATEADD(DD,1,EOMONTH (GETDATE()))

So you query would be like

ALTER TABLE Foo ADD
Bar datetimeoffset(0) NOT NULL DATEADD(DD,1,EOMONTH (GETDATE()));

Thanks!


how to update PostgreSQL array of jsonb I have the structure like this (json pretty):

[{
        "uid": "comment_1",
        "message": "level 1 - comment 1",
        "comments": [{
            "uid": "subcomment_1",
            "message": "level 2 - comment 1",
            "comments": []
        }, {
            "uid": "subcomment_2",
            "message": "level 1 - comment 2",
            "comments": []
        }]
    },
    {
        "uid": "P7D1hbRq4",
        "message": "level 1 - comment 2",
        "comments": []
    }
]

which is part of a table named 't1' with the structure:

id: integer,
... other stuff...,
comments: array of jsonb

I need to update a particular field: comments[1](with uid = comment_1) -> comments[2] (with uid = subcomment_2) -> message = 'comment edited'.

I'm brand new to postgresql so I can't figure it out how to do this, not even close.
I manage to merge objects and change message for level 1 with

UPDATE tasks
    set comments[1] = comments[1]::jsonb || $$
      {
        "message": "something",
      }$$::jsonb
where id = 20;

but that's as far as I could go.

Any hints towards the right direction?

You can use jsonb_set to replace a deep-in part of a jsonb structure.

...jsonb_set(thing,'{0,comments,1,message}','"something"');

To put that together with setting a specific element of a PostgreSQL array, it would be:

update tasks set comments[1]=jsonb_set(comments[1],'{0,comments,1,message}','"something"')
     where id=1;

Figuring out that '0' and '1' are the required subscripts to use in the path, if you didn't already know that, is another matter.

You can use jsonpath (to be new in version 12) to extract elements of JSON arrays based on conditionals (rather than just based on the array index), but I don't see a way to either update the extracted value, nor to get it to return to you an path at which that value was found so that you can sub the path into jsonb_set.