Move a Postgres 9.3 database to a new server and update to 9.6 with minimum downtime I have a PostgreSQL 9.3 database, which I have to move to a new server and update to 9.6 with minimum downtime.
I thought I could :

on the old Postgres, activate archive_command to copy WAL to a NFS directory mounted on both the old and new server
pg_dumpall the old Postgres
install a Postgres 9.6 on the new server
import the SQL dump
restart the new server in recovery mode, pointing to the NFS directory to get the WAL from the old server
So when the old and new Postgres are in sync, I can stop the old, verify that the new is up-to-date, change applications configuration to the new Postgres, and restart the applications.

I tested this with some test databases, and it does not work: on the new postgres the latest checkpoint's redo wal file has a number which is higher than the WAL number on the old. When I import the sql dump some WAL are generated, so the WAL number get incremented. So when I restart the new in recovery mode it does not read the old Postgres WAL.

I'm not sure this method is possible at all.

Could someone advise me another method if this one is not possible?

You can't do this, because WAL archive can only work with physical copies, while pg_dumpall and restore generates a logical copy.  Also, WAL format is not compatible across major versions, so you couldn't re-play WAL from one version to another anyway.  (You can do something like that with logical replication, but that doesn't exist in version 9.3, and is a rather advanced topic requiring extensive design and testing in the newer versions.)

Is it really necessary to move versions and servers at the same time?  If something goes wrong during such a double migration, you've just created a big diagnostic mess by changing two major things at the same time.

The lowest down-time way to move versions (other than logical replication) is to use pg_upgrade -k.  This requires you to have both old and new versions installed on the same machine simultaneously.

The lowest down-time way to move machines is to set up streaming replication; and wait for it catch up (as much as it can get while the master is still active), then shutdown all user connections to the master, wait for all WAL to replay to the replica, and promote the replica.


PgBouncer log management My pgbouncer.log grows endlessly.

How do I enable filename rollover or limit the logfile size for pgbouncer on Ubuntu?

Until now, pgBouncer doesn't support rotate log. Hence, you have to do it by yourself.

You can refer to sites below:

How to rotate PgBouncer logs in Linux/Windows ?

How To Manage Log Files With Logrotate On Ubuntu 12.10


Drop & re-add subscribers to transactional replication I'm trying to get stored procedures to execute on the subscribers, as seems to be best practice. I already have transactional replication running happily.

I add a new stored procedure, and set properties of this stored procedure article, but 'Replicate' is grayed out.

Ok - so I go to the stored procedure root and set properties for all stored procedure articles. Under 'Replicate' I set it to: "Execution in a serialized transaction of the SP", which is the recommended setting.

Yay! All is good!

But SQL Server lies.

If I go back in and check the properties for all stored procedures, it's changed the 'Replicate' setting back to "stored procedure definition only".   It doesn't actually keep my change.

The only mention of this issue I could find was here: http://www.informit.com/articles/article.aspx?p=343672&seqNum=4  where the solution is:
"To make stored procedure execution options available, you must drop any subscriptions from this publication and drop the article from the publication. After you add the article back to the publication, all options will be available to you again."

So - apologies for the long pre-amble, but here's my question:

If I remove my subscribers, make my necessary changes, and add the subscribers back, is SQL Server going to want to create a full snapshot?

I'm asking because a full snapshot takes a few hours & chews up our server, so it needs to be carefully planned.

Any insight anyone can provide would be awesome - thank you!

When you create a publication, you can choose whether or not you want to create a snapshot. Similarly (and more importantly), when you create a subscription, you can say if you want it to initialize or not. Choosing not to is probably what you need, but I suggest you test it before doing it in production


VACUUM and VACUUM FULL do not return space to OS I'm running Postgres 8.3.3.  I have a postgres database on a Linux filesystem that is quite full.  I've run VACUUM and VACUUM FULL, yet none of the space has been returned to the OS.  When I observe the size of the table in postgres it shows that the table is using considerably less space (down to 80MB form ~800MB).

Is there something else that should be run?

First you should at upgrade to the latest version of the 8.3 branch (which is 8.3.17). There were several fixes to VACUUM and autovacuum after the 8.3.3 (although I don't think any of those would fix your problem)

I think there are situations where even VACUUM FULL will no free all space possible. You could try to run CLUSTER on the table which does a better job of rebuilding it than VACUUM FULL (especially with that old version)

The drawback of CLUSTER is that it requires more intermediate space (because rebuilding isn't done "in-place"), so if your filesystem is really full, than it might not be possible.


creating a trigger based on time after one year in mysql My database has two tables: plans and status. plans has an attibute issue_date.

I want to create a trigger such that when the issue_date crosses one year, the attibute status in the status table should become inactive.

Triggers only occur when an action (INSERT, UPDATE or DELETE) occurs, but that's not the case here.

You have a record which has "aged out". So, basically, it just sits there and does nothing unless you actively "look for it"! The server can only react to what it can perceive as a change - not an arbitrary record age!

You'll have to use events - say (in your case), fired once a day! Think of these as "temporal programmable triggers".

From the documentation, the first thing you do is (one of):

SET GLOBAL event_scheduler = ON;
SET @@global.event_scheduler = ON;
SET GLOBAL event_scheduler = 1;
SET @@global.event_scheduler = 1;

That sets the events daemon (or service on Windows), which are like a cron job or an at) in motion (see), so basically now, events will be fired.

Then, from here do something like:

DELIMITER $$
CREATE
    EVENT `entity_update`
    ON SCHEDULE EVERY 1 DAY STARTS '2017-10-20 03:00:00'  -- do this at three in the morning!
    DO BEGIN

      -- This is where to put the SQL below - I forgot about
      -- the non-standards-compliant dialect of SQL that
      -- MySQL uses (see comparison with PostgreSQL below!).

    END */$$
DELIMITER ;

Be sure and test a couple of times before you "bed" your solution in!

The update SQL would look something like this (made a guess at table structure based on OP - see full DDL and DML below)

UPDATE status
SET status.status_plan = 'inactive'
WHERE status.status_plan_id IN
(
  SELECT del_id
  FROM
  (
    SELECT s.status_plan_id AS del_id
    FROM plan p
    JOIN status s
    ON p.plan_id = s.status_plan_id
    WHERE p.plan_issue_date < (DATE_SUB(NOW(), INTERVAL 1 YEAR)) -- plan_issue_date older than 1 year
    AND s.status_plan = 'active'  -- <-- and only change those which are not already inactive!
  ) AS things_to_delete
);

Incredibly, for reasons outlined here, with MySQL, we programmers have to put in an artificial subSELECT into our UPDATE! Check out the much simpler and more elegant PostgreSQL syntax below:

UPDATE status
SET status_plan = 'inactive'
WHERE status.status_plan_id IN
(
  SELECT s.status_plan_id
  FROM plan p
  JOIN status s
  ON p.plan_id = s.status_plan_id
  WHERE p.plan_issue_date < NOW() - INTERVAL '1 YEAR'
  AND s.status_plan = 'active'  -- <-- only change those which are not already inactive!
);

(Full MySQL solution here, PostgreSQL here)

---------- MySQL DDL ---------

CREATE TABLE plan
(
  plan_id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
  plan_issue_date DATE
);

CREATE TABLE status
(
  status_id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
  status_plan_id INTEGER,
  status_plan VARCHAR (10),  -- no CHECK CONSTRAINTs in MySQL!
  CONSTRAINT status_plan_fk FOREIGN KEY (status_plan_id) REFERENCES plan (plan_id)
);

Incredibly, MySQL does not support CHECK CONSTRAINTs on tables! You can get around this in three (clumsy IMHO) ways:

TRIGGERs - as outlined - here. Very clumsy - these should be for complex business logic and not for relatively simple criteria - Occam's Razor and all that!
A small lookup/reference table - here. Not a bad alternative, but not the most elegant - I prefer, if possible, to keep all definitions/constraints associated with a table in the CREATE TABLE statement!
VIEWs with CHECK OPTION - here. Quite why MySQL can provide VIEWs with such an option but not tables is beyond me!

<rant over...>

------------ DDL for both MySQL and PostgreSQL ------------

INSERT INTO status (status_id, status_plan_id, status_plan) VALUES (35, 1, 'active');
INSERT INTO status (status_id, status_plan_id, status_plan) VALUES (45, 2, 'active');
INSERT INTO status (status_id, status_plan_id, status_plan) VALUES (67, 3, 'active');
INSERT INTO status (status_id, status_plan_id, status_plan) VALUES (78, 4, 'active');
INSERT INTO status (status_id, status_plan_id, status_plan) VALUES (95, 5, 'inactive');

INSERT INTO status (status_id, status_plan_id, status_plan) VALUES (35, 1, 'active');
INSERT INTO status (status_id, status_plan_id, status_plan) VALUES (45, 2, 'active');
INSERT INTO status (status_id, status_plan_id, status_plan) VALUES (67, 3, 'active');
INSERT INTO status (status_id, status_plan_id, status_plan) VALUES (78, 4, 'active');
INSERT INTO status (status_id, status_plan_id, status_plan) VALUES (95, 5, 'inactive');

------------------ PostgreSQL DDL ----------------------------------

CREATE TABLE plan
(
  plan_id SERIAL, CONSTRAINT plan_pk PRIMARY KEY (plan_id),
  plan_issue_date DATE -- , CONSTRAINT plan_date_ct_gt_now CHECK (plan_issue_date > NOW())
                       --   might be an idea for future inserts into plan?
);
ALTER SEQUENCE plan_plan_id_seq RENAME TO plan_seq;

INSERT INTO plan (plan_issue_date) VALUES ('2017-10-15');
INSERT INTO plan (plan_issue_date) VALUES ('2016-10-15');  -- << older than 1 year
INSERT INTO plan (plan_issue_date) VALUES ('2017-07-23');
INSERT INTO plan (plan_issue_date) VALUES ('2016-03-04');  -- << older than 1 year
INSERT INTO plan (plan_issue_date) VALUES ('2016-02-25');  -- <<= older than 1 year and inactive in status table.

CREATE TABLE status
(
  status_id SERIAL,
  CONSTRAINT status_pk PRIMARY KEY (status_id),
  status_plan_id INTEGER,
  status_plan VARCHAR(10) CHECK (status_plan IN ('active', 'inactive')),
  CONSTRAINT status_plan_fk FOREIGN KEY (status_plan_id) REFERENCES plan (plan_id)
);


sql update query for two connected tables UPDATE rooms set r.vacant=true
FROM reservation s,rooms r
WHERE r.room_no = s.room_no AND s.cus_id=1

I created above sql queryto update room status.I want to set vacant as true when customer_id =1.
But this code didn't work on mysql.
Whats wrong with this?

Try using an INNER JOIN statement:

UPDATE rooms r
INNER JOIN reservation s
ON s.room_no = r.room_no
SET r.vacant=true
WHERE s.cus_id=1


AlwaysOn Availability Groups on Secondary Replica We have a Primary Server A with a Database. It has AlwaysOn ReadOnly Asynchronous which moves data to Server B.

Out of curiosity, is it possible to place Server B on AlwaysOn and move it to another Server C Asychronous Read Only?

We want to reduce the workload/performance on our main server A OLTP; however, we want two replicas. We're just curious.

So AlwaysOn from

Server A → ServerB → ServerC

Additional details

We want to make Database B as a primary replica on AG, which then passes to secondary Database C.

Out of curiosity, it is possible to place Server B on AlwaysOn and move it to another Server C Asychronous Read Only.

If it's the same databases in the already existing Availability Group, then the only way you could theoretically do this is by re-architecting your solution so that Server B and C were in their own cluster and utilizing distributed availability groups from Server A.

I would not do this.


IDE Access to Hive without user password? I'm a DB Developer and in my company we're only able to interact with hive through a technical user who owns the specific write permissions for Hive on HDFS.
So in practice this looks like this:

I connect through our remote server by using SSH with my user credentials
I switch to the technical hive user by doing sudo su - hive_user (DBA's wont hand out the password for this, so they prefer only giving us specific sudo permission for switching user like this)
I execute a query using beeline -f QUERY_FILE

So as you can see I'm bound to work with the CLI (beeline) all the time, but I'd like to have the convenience using any SQL IDE from my Desktop.

Is there any IDE for Hive, that allows me to connect to our DB as a technical user that can only be accessed by using sudo su - hive_user? A linked manual for this, also would be nice.

I could not test the below described procedure as I don't have the same infrastructure as you, but I suggest you to try this and come back with further details if any of the steps fails.
DBeaver should be able to do this trick for you:

Install DBeaver, either as an Eclipse plug-in or as a stand-alone application
Create a new connection, for example by clicking on the  icon
Select Apache Hive as connection type, and then click "next":

In Generic JDBC connection setting, go first to "SSH" tab and setup your SSH tunnel:

Still in Generic JDBC connection setting, come back to "General" tab and click on "Connection details (name, type, ... )" at the bottom right of the window
On the new screen, click on "Shell commands":

Then I am less sure, but either in "Before connect" or "After connect", add your command sudo su - hive_user. I would rather expect "Before connect", but it depends in which sequence DBeaver connects to SSH tunnel and execute this command. If the first solution doesn't work then I would test the other one:

Once done, click "Back" and fill in the "General" tab with your Hive user. You click "Test connection ..." to ensure everything works fine. Once it works, you can click on "Finish".


how to get count(*) from table which either has column or not? I have a post table as below:

CREATE TABLE `post` (
  `p_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` smallint(5) unsigned NOT NULL,
  `cat_id` tinyint(3) unsigned NOT NULL,
  `view_count` smallint(5) unsigned NOT NULL DEFAULT '0',
  `status` tinyint(3) unsigned NOT NULL,
  `abstract` text CHARACTER SET ucs2 COLLATE ucs2_persian_ci NOT NULL,
  `content` text CHARACTER SET ucs2 COLLATE ucs2_persian_ci NOT NULL,
  `title` varchar(50) CHARACTER SET ucs2 COLLATE ucs2_persian_ci NOT NULL DEFAULT '',
  `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `meta_description` tinytext CHARACTER SET ucs2 COLLATE ucs2_persian_ci NOT NULL,
  `picture` varchar(100) NOT NULL,
  PRIMARY KEY (`p_id`),
  KEY `user_id` (`user_id`),
  KEY `date` (`date`),
  KEY `pic_id` (`picture`),
  KEY `cat_id` (`cat_id`),
  CONSTRAINT `post_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `karbar` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `post_ibfk_3` FOREIGN KEY (`cat_id`) REFERENCES `category` (`cat_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=latin1

And I have a comment table as below:

CREATE TABLE `comment` (
  `id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `p_id` smallint(5) unsigned NOT NULL DEFAULT '0',
  `name` varchar(50) CHARACTER SET ucs2 COLLATE ucs2_persian_ci NOT NULL DEFAULT '',
  `site` varchar(200) CHARACTER SET ucs2 COLLATE ucs2_persian_ci NOT NULL,
  `content` text CHARACTER SET ucs2 COLLATE ucs2_persian_ci NOT NULL,
  `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `p_id` (`p_id`),
  CONSTRAINT `comment_ibfk_1` FOREIGN KEY (`p_id`) REFERENCES `post` (`p_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1

I want to get one post(e.g p_id=8) and all of its comments. how to do that? How to get count(*) from comment table while your getting your post record?

This will get all posts and their comment counts

SELECT p.*,IFNULL(c.comcount,0) comment_count
FROM post p LEFT JOIN
(SELECT p_id,COUNT(1) comcount FROM comment GROUP BY p_id) c
USING (p_id);

This will get post for p_id=8  and its comment count

SELECT p.*,IFNULL(c.comcount,0) comment_count
FROM (SELECT * FROM post WHERE p_id=8) p LEFT JOIN
(SELECT p_id,COUNT(1) comcount FROM comment WHERE p_id=8 GROUP BY p_id) c
USING (p_id);


PostgreSQL: How does the implicit index work when using "COPY" for loading bulk rows into a newly created table? What I know for fast loading bulk rows into a new table is:

Create the table(without creating the index)
Use "COPY" to load data from a file into the table
Create the index

What if I added a primary key when creating the table, according to PostgreSQL: "Adding a primary key will automatically create a unique btree index on the column or group of columns used in the primary key.", will the rows be indexed during "COPY" since the table has an implicit index? if the answer is yes, the "COPY" will slow down?

will the rows be indexed during "COPY" since the table has an implicit index?

Yes.

  if the answer is yes, the "COPY" will slow down?

Yes.

You can LOCK TABLE ... IN ACCESS EXCLUSIVE MODE then drop the PRIMARY KEY constraint for the duration if you need to.

PostgreSQL doesn't have a way to mark indexes as invalid then revalidate them for use without dropping and recreating them (yet).


How to insert into a table daily automatically in postgresql? I'm trying to create a log of an inventory that runs daily

Basically on my log table i want to sum how many units have status1, how many units have status2, etc for every day

is there a way to fill up my log table without having to do it manually every day?

My log table is id serial, date date, count_of_status1 integer, count_of_status2 integer

My insert query would be

insert into logtable(date, count_of_status1, count_of_status2)
select now(),  count(*) filter (where status =1), count(*) filter (where status = 2)
from mytable

How to make this insert to be done daily in an automated form rather than manually?

PostgreSQL does not have a built in job scheduler.  There are many options to run jobs on a schedule.  If you already have a generic job scheduler in use in your IT environment, I'd recommend making use of that, rather than installing and configuring and learning a new one just for PostgreSQL.  For example, I use "cron" for all my job scheduling outside of PostgreSQL, so I use it for PostgreSQL as well, using something like psql -f job.sql as the command to run.  You will have to arrange for the job to be able to connect to the database without a password--there are several ways to do that depending on your security environment.

For the query itself, you can't expect the job to run at exactly midnight down to the nanosecond. So run the job several minutes after midnight (to make sure clock skew doesn't cause it to accidentally run before midnight), and put WHERE clauses in the query that uses a timestamp in the table to pull out the data exactly aligned to date boundaries.


SQL Server 2012 Express : How do you limit a database's size and retention policy? I don't work with databases very often and I want to make sure I am going about this the right way.

I want to configure a database's file size to not exceed 9 gigs. At the point the database reaches this file size limit I would like it to purge 25% percent of its oldest records to accommodate new records.

Is this something I could/should configure on the database server itself, like a one time maximum setting config, or does this have to be handled by the program that is updating the database (it would check the capacity and tell it when to purge records).

I'm not a DBA so I'm unsure of how this is usually handled.

You can set the database's max file size to 9GB, but you will need to do the purging yourself.  This is not something that SQL Server supports.  You could get notified of the limit being reached using extended events, but there is no way for SQL Server to know what records to delete without you telling it.  SQL Server does not log or otherwise keep track of how old a row in a table is.  You have to build it into the table schema and maintain your own aging logic.

To set the file size in SQL Server Management Studio:

Right-click the database and get properties
Click the Files link in the menu on the left
Click the ... button under Autogrowth / maxsize and set the properties accordingly

T-SQL to set the file size:

ALTER DATABASE MyDb
MODIFY FILE
(
    NAME=N'MyDataFile',
    MAXSIZE=9000MB
)

Note: MyDataFile is the logical name of your data file, not the physical file name on the filesystem.  You can find the logical file names of your database files with the sys.database_files system view:

USE MyDb
SELECT name
FROM sys.database_files
WHERE type_desc = 'ROWS'

The value in the name column is the logical name of the file.


Oracle DBCA utility: "Listener is not up or database service is not registered with it" I'm a software engineer with some experience on SQL Server but a newbie on Oracle.

I'm attempting to run dbca on a remote linux machine to reconfigure a database that was initially configured to idle with zero memory (so I'm told). Enterprise Manager is not configured properly and my listener.ora is as follows:

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS =
        (PROTOCOL = TCP)
        (HOST = Myhost)
        (PORT = 1521)
      )
    )
  )

This database is not on port 1521, it's on port 15321. I can connect to it using SQL Developer by manually creating a connection with the correct port, etc.

I get the error in the title after I press finish in dbca. There is also a log file that is very long. How do I proceed to fix dbca? Ultimately I need to configure Enterprise Manager.

As far as I understand, there is a database on the machine. This database should register itself on the listener. It can do so when it knows where - on which port - the listener runs. In you case the listener is on port 15321. To enable the auto registration of the instance, you should make sure that the LOCAL_LISTENER parameter is filled with the correct port number in order to be able to contact the listener.
For docu check Oracle® Database Reference 11g Release 1 (11.1)

. oraenv
[YOUR_ORACLE_SID]
sqlplus / as sysdba
-- if the instance appears not to be running:
startup
alter system set local_listener = '(ADDRESS = (PROTOCOL=TCP)(HOST=Myhost)(PORT=15321))';
-- to force immediate registration:
alter system register;

Now your database should be visible in the listener services list
listener.ora should look like this - note the PORT =

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS =
        (PROTOCOL = TCP)
        (HOST = Myhost)
        (PORT = 15321)
      )
    )
  )

check the results:

  lsnrctl services


MySQL - view select rooms not in booking range I'm using a select query within a view to find when a room is available, a booking is identified with a reference and a start/end date.

view:

USE CATTERY2;
CREATE VIEW RoomAvalability
AS
SELECT ROOMINFO.ID,# ROOMINFO.`Type`, ROOMINFO.Max,
    #GROUP_CONCAT(DISTINCT BOOKROOM.Ref) as Bookings,
    GROUP_CONCAT(DISTINCT BOOK.Ref) as BookRef, ##
    GROUP_CONCAT(DISTINCT BOOK.Start_Date) as StartDate,
    GROUP_CONCAT(DISTINCT BOOK.End_Date) as EndDate
FROM ROOMINFO
LEFT JOIN BOOKROOM ON ROOMINFO.ID = BOOKROOM.ID
INNER JOIN BOOK ON ROOMINFO.ID AND BOOK.Ref = BOOKROOM.Ref ##
GROUP BY ROOMINFO.ID, ROOMINFO.`Type`, ROOMINFO.Max;

A room can have multiple bookings (which don't overlap) within the current bookings logged.

The problem I'm currently having is the select query to specify a date range, I cannot wrap my head around it.

I want to get returned from the query the list of rooms which don't have a booking for the specified date range.

I'm currently trying the following 3 selects for testing:

#1
SELECT * FROM RoomAvalability;
#2
SELECT ID, `Type`, Max FROM RoomAvalability WHERE NOT StartDate <= '2019-11-22' AND EndDate <= '2020-11-28';
#3
SELECT ID, `Type`, Max FROM RoomAvalability WHERE StartDate <= '2019-11-22' AND EndDate <= '2020-11-28';

1 returns all rooms

2 also returns all rooms

3 returns no rooms

1 & 2 results:

ID, type max
1   2   4
2   2   4
3   2   4
4   1   2
5   1   2
6   1   2
7   1   2
8   1   2
9   1   2
10  1   2

Shouldn't number 2 be returning the 8 rooms instead of 10, as it's the negative of number 3?

number 3 provides no results meaning there isn't a booking within this range, but it should provide 2 results?

The other thing I am thinking: should I be starting from the booking and link the room instead?

structure and values

CREATE SCHEMA CATTERY2;
USE CATTERY2;
#BOOK
CREATE TABLE BOOK(
           Ref INT NOT NULL AUTO_INCREMENT,
           Start_Date DATE NOT NULL,
           End_Date DATE NOT NULL,
           PRIMARY KEY(Ref));
#ROOM
CREATE TABLE ROOMINFO(
          ID INT NOT NULL AUTO_INCREMENT,
          `Type` VARCHAR(10) NOT NULL,
          Max TINYINT NOT NULL,
          PRIMARY KEY(ID));
#BOOKROOM
CREATE TABLE BOOKROOM(
           Ref INT NOT NULL,
           ID INT NOT NULL,
           FOREIGN KEY (Ref) REFERENCES BOOK(Ref),
           FOREIGN KEY (ID) REFERENCES ROOMINFO(ID));

INSERT INTO BOOK(Start_Date, End_Date) VALUES
("2019-11-22", "2019-11-25"),
("2019-11-24", "2019-11-28"),
("2019-12-01", "2019-12-02"),
("2019-12-01", "2019-12-06"),
("2019-12-02", "2019-12-03"),
("2019-12-04", "2019-12-10"),
("2019-12-04", "2019-12-10"),
("2019-12-05", "2019-12-13"),
("2019-12-16", "2019-12-19"),
("2019-12-26", "2019-12-28"),
("2019-12-26", "2020-01-01"),
("2019-12-28", "2020-01-02"),
("2019-12-31", "2020-01-05"),
("2020-01-03", "2020-01-08"),
("2020-01-05", "2020-01-11"),
("2020-01-06", "2020-01-09"),
("2020-01-06", "2020-01-11"),
("2020-01-08", "2020-01-18"),
("2020-01-11", "2020-01-15"),
("2020-01-15", "2020-01-17"),
("2020-01-15", "2020-01-18");

INSERT INTO ROOMINFO (ID, `Type`,Max) VALUES
(1, "Family", 4),
(2, "Family", 4),
(3, "Family", 4),
(4, "Dual", 2),
(5, "Dual", 2),
(6, "Dual", 2),
(7, "Dual", 2),
(8, "Dual", 2),
(9, "Dual", 2),
(10, "Dual", 2);

INSERT INTO BOOKROOM( Ref, ID ) VALUES
(1, 4),
(2, 3),
(3, 4),
(4, 5),
(5, 6),
(6, 7),
(7, 3),
(8, 2),
(9, 1),
(10, 8),
(11, 3),
(12, 9),
(13, 2),
(14, 10),
(15, 4),
(16, 5),
(17, 6),
(18, 7),
(19, 2),
(20, 1),
(21, 10);

Try this:

SELECT r.id
FROM ROOMINFO AS r
WHERE NOT EXISTS (SELECT 1
                    FROM BOOKROOM br
                    JOIN BOOK b ON br.ref = b.ref
                    WHERE r.id = br.id
                        AND ('2019-11-22' BETWEEN b.Start_Date AND b.End_Date
                            OR '2019-11-28' BETWEEN b.Start_Date AND b.End_Date
                            OR b.Start_Date BETWEEN '2019-11-22' AND '2019-11-28'
                            OR b.End_Date BETWEEN '2019-11-22' AND '2019-11-28'
                            )
                  );

Or if you add room id to BOOK table you could use such query:

SELECT r.id
FROM ROOMINFO AS r
WHERE NOT EXISTS (SELECT 1
                    FROM BOOK
                    WHERE r.id = id
                        AND ('2019-11-22' BETWEEN Start_Date AND End_Date
                            OR '2019-11-28' BETWEEN Start_Date AND End_Date
                            OR Start_Date BETWEEN '2019-11-22' AND '2019-11-28'
                            OR End_Date BETWEEN '2019-11-22' AND '2019-11-28'
                            )
                );

In this case you can create supporting index ON BOOK(id, Start_Date, End_Date)


Methods of Denying login by specific SQL Authenticated user using Management Studio I am trying to create a way of restricting/complicating access, for a particular user, to login to SQL Server, using Management Studio with SQL Server Authentication.
I would like a way that also gives the user a custom error whenever he tries to connect.
One way I know of is to use Logon triggers. Ex:

CREATE TRIGGER ManagementStudio_Connection_Limit_Trigger
ON ALL SERVER WITH EXECUTE AS 'TestUser'
FOR LOGON
AS
BEGIN
    IF ORIGINAL_LOGIN()= 'TestUser' AND EXISTS (SELECT * FROM sys.dm_exec_sessions WHERE session_id = @@SPID AND program_name LIKE 'Microsoft SQL Server Management Studio%')
        ROLLBACK;
END;

(I know that the Application Name can be spoofed but if they really want to connect, that is OK)

Are there any other ideas?

Edit: The fully story is that the SQL Authenticated user in question, is a user used primarily from some Java applications. I cannot change the permissions without a full test of all applications. Also, unfortunately the Java developers knows these passwords and uses this user willy-nilly. So, if we change the password, the developers need to know. Windows Authentication from Java is a can of worms I'd prefer not to go into right now. And again, if they manage to find a way to connect anyway, it's not the end of the world. I just want to make it complicated enough for them to reconsider. (They have personal Windows Authenticated users which they can use and sometimes do).

Connecting to management studio should not be that threat to you unless and until that user gets full read/write on the database in question which needs to be secured.

You can use DENY VIEW ANY DATABASE Permission herewhich will make the DB sort of invisible for that user when he tries to connect via SSMS, however if they are smart enough and hit a query with USE <database name> they can proceed.

Else, its better you just give them access via windows authentication (with controlled access) and do not share any SQL login credentials which have the permission to execute the task which you do not want the user to.

Also, read this thread


Enable SQL server agent service by T-SQL script or PowerShell Can we enable/disable SQL server agent service by T-SQL script or PowerShell/cmd?

It will depend on what version of PowerShell you are running, but this command can be used from at least 3.0 and up:
Set-Service SQLSERVERAGENT -StartupType Disabled

The alternative would be to use WMI, or you can use SQL Server WMI (microsoft.sqlserver.management.smo.wmi namespace).
In T-SQL there is no mechanism native that lets you interact with the services. Outside of just using xp_cmdshell and calling PowerShell or cmd commands.


Different results from select and from materialized view select distinct on (p) coalesce(native_fcm_token, push_token) p
from session
where user_id = 2779
and ((push_token is not null) or (native_fcm_token is not null))

returns 27 rows

refresh materialized view session_push_token
select * from session_push_token where user_id = 2779

returns 22 rows

session_push_token is defined as

create materialized view session_push_token as
SELECT DISTINCT ON (COALESCE(s.native_fcm_token, s.push_token)) COALESCE(s.native_fcm_token, s.push_token) AS push_token,
                                                                s.os,
                                                                s.user_id
FROM session s
WHERE ((s.push_token IS NOT NULL) OR (s.native_fcm_token IS NOT NULL));

How this can happend?
Postgresql 11 from docker image

They give different results because they do different things.

One does the "distinct on" over only those rows which meet user_id = 2779, the other returns only those those rows where the result of the "distinct on" happens to have user_id = 2779.

So if one group of the values yielded by expression "p" has a row where user_id = 2779 but also other rows, and the row where user_id = 2779 is not the one selected to represent the group, then no row for that group is returned.  Since you did not specify an ORDER BY, it is indeterminate which row is selected to represent each group.

You can reproduce this without a materialized view, it is the difference between:

select * from (<query> where user_id=2779) foobar;

and

select * from (<query>) foobar where user_id=2779;


How to get COUNT of a range between 2 columns Table:

Staff  | StartingTime |  EndingTime  | starthour   | endhour
-------------------------------------------------------------
10     |     0813     |    1610      |    08       |    16
20     |     0910     |    1224      |    09       |    12
30     |     1132     |    1706      |    11       |    17
40     |     1311     |    2110      |    13       |    21
50     |     1533     |    2230      |    15       |    22

The first 2 time columns are nvarchar(4) and I derived the last 2 columns with LEFT(field, 2) to get the hours.

I need to get a count of the number of staff that were working for each hour. It will need to by dynamic as I will never know the time ranges. Right now I'm just trying to get this working for a single day, but eventually I need it broken out by day.

Expected results:

Hour  |  Staff
------------------
8     |    1
9     |    2
10    |    2
11    |    3
12    |    3
13    |    4
14    |    4
15    |    5
..
19    |    2
etc..

This is on Microsoft SQL Server 2014.

This is a way of doing it. However, if you need it broken out by day, you need to add an extra column in your table for indicating the day, and you should add that column to the table variable I created as well.

    DECLARE @Hours TABLE
    (
      hour varchar(2),
      total int
    )
    DECLARE @I INT
    SET @I = 0
    WHILE @I < 24
    begin
        INSERT INTO @Hours VALUES (RIGHT('0' + CAST(@I AS VARCHAR(2)),2), 0)
        SET @I = @I + 1
    end

    DECLARE @starthour VARCHAR(2)
    DECLARE @endhour VARCHAR(2)
    DECLARE cur CURSOR
    FOR SELECT starthour, endhour FROM WorkTime
    OPEN cur
    FETCH NEXT FROM cur
    INTO @starthour, @endhour
    WHILE @@FETCH_STATUS = 0
    begin
        UPDATE @hours
        SET total = total + 1
        WHERE hour BETWEEN @starthour AND @endhour
        FETCH NEXT FROM cur INTO @starthour, @endhour
    end
    CLOSE cur
    DEALLOCATE cur

SELECT * FROM @hours
WHERE total <> 0

OUTPUT

hour    total
08      1
09      2
10      2
11      3
12      3
13      3
14      3
15      4
16      4
17      3
18      2
19      2
20      2
21      2
22      1


select all with other parameters in db2 What is the equivalent query for

SELECT *,SUM(total_request_count) AS total_calls  FROM API_REQUEST_SUMMARY

in DB2. It says * is invalid in db2 data explorer. After running this I got below error

An unexpected token "*" was found following "SELECT ". Expected tokens may include: "NULL".. SQLCODE=-104, SQLSTATE=42601, DRIVER=3.68.61

Any Idea to resolve this is much appreciated.

any not aggregated column must be included in GROUP BY

SELECT COL1, COL2 ,SUM(total_request_count) AS total_calls  FROM API_REQUEST_SUMMARY GROUP BY COL1, COL2

alternative if many columns for GROUP BY

SELECT T1.*, T2.total_calls FROM API_REQUEST_SUMMARY T1 LEFT JOIN
(SELECT GROUP_COLUMN, SUM(total_request_count) AS total_calls  FROM API_REQUEST_SUMMARY GROUP BY GROUP_COLUMN) T2 ON T1.GROUP_COLUMN = T2.GROUP_COLUMN

it is my work solution, may be other present as well


Convert a lookup value to foreign key when a query is run Currently we have a legacy system running queries on a Postgres server to update/insert records. One of the obstacles we are having is that we have to convert text values to the correct foreign key for a lookup table. Please see below for a better explanation:

Query:

INSERT INTO public.people(name, state)
VALUES ('John', 'CA');

Tables:

public.people

CREATE TABLE people
(
  name text,
  state smallint,
  CONSTRAINT state_key FOREIGN KEY (state)
      REFERENCES states (states_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
  OIDS=FALSE
);

CREATE INDEX fki_state_key
  ON people
  USING btree
  (state);

public.states

CREATE TABLE states
(
  states_id smallint,
  name text,
  CONSTRAINT state_unique UNIQUE (states_id)
)
WITH (
  OIDS=FALSE
);

What is the best way of converting 'CA' to the associated ID in the public.states table? The particular interface we are using to talk to Postgres is called Postgrest (https://github.com/begriffs/postgrest) and would require additional SELECT statements instead of a subqueries. We want minimize the amount of calls as much as possible.

Also, on the legacy system that is using that interface it does not use a lookup table, which makes it difficult to determine which fields need to be converted for a lookup table.

Is there a native way in Postgres to handle these types of conversions? If you need additional information please let me know.

"PostgreSQL 9.4.5 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.8.3 20140911 (Red Hat 4.8.3-9), 64-bit"

There are various ways, depending on the use case. For your example:

INSERT INTO public.people(name, states_id)
VALUES ('John', (SELECT states_id from public.states WHERE name = 'CA'));

Or:

INSERT INTO public.people(name, states_id)
SELECT 'John', states_id
FROM   public.states
WHERE  name = 'CA';

Assuming public.states.name to be unique - there should be a constraint in the table definition.
And a column public.people.states_id, obviously.


AlwaysOn Secondary readable - can't connect with applicationintent=readonly With an SQL AlwaysOn, I Set my readable secondary to read intent only.
When I try to connect with parameter applicationintent=readonly my connection is established with the primary server.

Here is my parameters:

Listener:  Lst_Test
Avaibility group: AG-TEST
Primary: PROD-TEST
Secondary: DRP-TEST

Here is my script for create route :

    ALTER AVAILABILITY GROUP [AG-Test]
MODIFY REPLICA ON
'PROD-TEST' WITH
(SECONDARY_ROLE (READ_ONLY_ROUTING_URL='TCP://PROD-test.contoso.com:1433'));

ALTER AVAILABILITY GROUP [AG-Ocean]
MODIFY REPLICA ON
'DRP-TEST' WITH
(SECONDARY_ROLE (READ_ONLY_ROUTING_URL='TCP://DRP-test.contoso.com:1433'));

ALTER AVAILABILITY GROUP [AG-Test]
MODIFY REPLICA ON
'PROD-TEST' WITH
(PRIMARY_ROLE(READ_ONLY_ROUTING_LIST =('DRP-TEST','PROD-TEST')))

ALTER AVAILABILITY GROUP [AG-Test]
MODIFY REPLICA ON
'DRP-TEST' WITH
(PRIMARY_ROLE(READ_ONLY_ROUTING_LIST =('PROD-TEST','DRP-TEST')))

Your connection string likely doesn't specify the database. It has to include the database name so that SQL Server can tell which routing list to use. (Your server could have multiple Availability Groups running on it.)


Backup stored procedure for full, differential and log backup I wrote a simple stored procedure for backing up FULL, Differential and LOG backup. This is my stored procedure:

Alter PROCEDURE BackupDB
(
    @BaseLocation   varchar(1024),
    @BackupType     varchar(32)

)
AS
BEGIN

    Declare @DBName     varchar(255) = db_name()

    Declare @FileName   varchar(256)

    Declare @Date datetime = getdate()
    Set @BackupType = UPPER(@BackupType)

    Set @FileName = @DBName + '_'  + Cast(Year(@Date) as varchar(4)) + '_' +
                    Right('00' + Cast(Month(@Date) as varchar(2)),2) + '_' +
                    Right('00' + Cast(Day(@Date) as varchar(2)),2) + '_' +
                    Right('00' + Cast(DatePart(hh,@Date) as varchar(2)),2) + '_' +
                    Right('00' + Cast(DatePart(mi,@Date) as varchar(2)),2) + '_' +
                    Right('00' + Cast(DatePart(ss,@Date) as varchar(2)),2) + '_' +
                    Right('000' + Cast(DatePart(ms,@Date) as varchar(3)),3) + '_' + UPPER(@BackupType) +
                    (Case When @BackupType = 'FULL' Or @BackupType = 'DIFFERENTIAL' Then '.BAK' Else '.TRN' End)

    Declare @FullPath varchar(1280) = ''

    If Right(@BaseLocation,1) <> '\'
    Begin
        Set @BaseLocation = @BaseLocation + '\'
    End
    Set @FullPath = @BaseLocation + @FileName
    Print @FullPath

    If @BackupType = 'FULL'
    Begin
        Backup database @DBName To Disk = @FullPath
    End
    Else If @BackupType = 'DIFFERENTIAL'
    Begin
        Backup database @DBName To Disk = @FullPath WITH DIFFERENTIAL
    End
    Else If @BackupType = 'LOG'
    Begin
        BACKUP LOG @DBName
             TO Disk = @FullPath
    End

END
GO

I ran my stored procedure and it seems to work perfectly for parameters passed in. Now the next step is to create a SQL JOB for this. However before I do this, I want to know is this correct procedure? I've read many articles and they all seem to point out one line "Test backup strategy."

Now I would be creating a SQL JOB weekly, daily and every 5 minutes i.e Weekly full backups, Daily Differential and Log backups every 5 minutes.

Do you think this stored procedure is incorrectly written or do you think it should be all fine for my scenario?

There are quite a few solutions to have your daily backups (full, diff and logs). You could:

Write your own script: it might be useful if you need a fine control on it, or if you need to respect some naming convention. I did not test it, but your code looks reasonnable.
Use the maintenance plans: very easy to set up, but less control. This might be a good solution in your case.
Use a 3rd party script / tool: Ola Hallengren's scripts are very good, but you can find plenty of others.


non clustered index on same table but the order is different? I have a table with 5 columns col1...col5 .

I need to create a non clustered index on col2,col3,col4. I did, then my boss is asking me to create one more non clustered index on col4,col3,col2. It doesn't make sense to me? Both are same right?

It does matter some times. Colleen Morrow does a good demo on this at
http://colleenmorrow.com/2011/04/07/composite-indexes-does-column-order-matter/

In essence she says that if your WHERE clause contains the first column in the index it will do an index seek. If it contains the third column in the index and not the first it will do an index scan which is not as good. In this case the new index would be better (assuming the optimizer chooses it). If your WHERE clause contains both columns then apparently either index would serve.


How can I disable parallel queries in PostgreSQL? I want to benchmark some things in PostgreSQL. When I do this I normally turn off some of the features the database is currently using, just for my session, to see what the second-best plan is.

Though setting max_parallel_works = 0 , I still get a parallel plan.

                                                         QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
 Gather  (cost=1000.00..120892.01 rows=1000 width=15) (actual time=0.444..4781.539 rows=666667 loops=1)
   Workers Planned: 2
   Workers Launched: 0
   ->  Parallel Seq Scan on foo  (cost=0.00..119792.01 rows=417 width=15) (actual time=0.102..4711.530 rows=666667 loops=1)
         Filter: (lower(x) ~~ '%999.com'::text)
         Rows Removed by Filter: 9333333
 Planning time: 0.124 ms
 Execution time: 4801.491 ms
(8 rows)

I know it says Workers Launched: 0. But, I would like that to look like a pre-Parallel plan where the plan itself is not even planned to be parallel. How can I accomplish this?

From the docs,

  max_parallel_workers_per_gather (integer) Sets the maximum number of workers that can be started by a single Gather or Gather Merge node. Parallel workers are taken from the pool of processes established by max_worker_processes, limited by max_parallel_workers. Note that the requested number of workers may not actually be available at run time. If this occurs, the plan will run with fewer workers than expected, which may be inefficient. The default value is 2. Setting this value to 0 disables parallel query execution.

So to disable the parallel query planning use,

SET max_parallel_workers_per_gather = 0;

You can see that here, same query,

                                                  QUERY PLAN
--------------------------------------------------------------------------------------------------------------
 Seq Scan on foo  (cost=0.00..207292.03 rows=1000 width=15) (actual time=0.082..4715.721 rows=666667 loops=1)
   Filter: (lower(x) ~~ '%999.com'::text)
   Rows Removed by Filter: 9333333
 Planning time: 0.083 ms
 Execution time: 4736.202 ms


Relational Database tables I have a table Player where playerID is the primary key. Then I have a table Game where gameDate is the primary key (of type DATETIME). Each game is played by 2 players and each player can play many games. So I believe there is a many to many relationship between Player and Game. I want to store both players for each game. I thought of adding player1ID and player2ID on the Game table or to have a bridge table PlayerPlaysGame with playerID and gameDate and then try to find who played the same game so that I get the pair. Any advice as to how I should design my database would be really helpful. Thanks

Given that a game can only ever have two players, I suggest the following design:

create table PLAYER
(
  playerid   INTEGER PRIMARY KEY,
  -- add your other columns here
);

create table GAME
(
  gameid     INTEGER PRIMARY KEY,
  gamedate   DATETIME,
  player1id  INTEGER,
  player2id  INTEGER,
  -- add your other columns here
);

If you required a differing number of players per game, then I would have designed it differently - with your requirements there is no need to add an extra table & increase the query complexity.

Also note that I have made gameid the PRIMARY KEY. Using a DATETIME as the PK is generally not a good idea - what would happen if two games were created simultaneously?

You'll also probably want to add foreign keys to/from GAME.player1id/player2id and PLAYER.playerid.


This installation of MySQL is already upgraded to 5.7.25? I used sudo apt upgrade and got this:

E: dpkg was interrupted, you must manually run 'sudo dpkg --configure -a' to correct the problem.

So I did it and I see this for more than an hour.

Setting up mysql-server-5.7 (5.7.25-0ubuntu0.18.04.2) ...
Checking if update is needed.
This installation of MySQL is already upgraded to 5.7.25, use --force if you still need to run mysql_upgrade

Nothing happens. I have not used MySQL for a long time and don't know what should I do. Understand there must be something with force but I am new with Ubuntu and not so familiar with commands. Can you help me? I don't understand nor what the problem is...:(

UPDATE: I can use MySQL Workbench and get access in Terminal too, but still keep seeing that error

Actually here is a solution that worked for me:
https://techierg.blogspot.com/2019/06/ubuntu-1804-mysql-this-installation-of.html
Quote:

Briefly, the solution is
Edit the file /var/lib/dpkg/info/mysql-server-5.7.postinst

sudo nano /var/lib/dpkg/info/mysql-server-5.7.postinst

At line # 370, comment out the echo ALTER statement which would like

#echo "ALTER USER 'root'@'localhost' IDENTIFIED WITH 'auth_socket';"                 >> "$initfile"
Restart the VM, then...

Run dpkg configure again and the problem will be solved.

sudo dpkg --configure -a
That solved it for me and saved me hours of research.
The article refers to this link here:
https://bugs.launchpad.net/ubuntu/+source/mysql-5.7/+bug/1817374/comments/8


Should I join all of my polygons to make a single polygon for point-in-polygon search? I have a set of of polygons. Sometimes they are completely disconnected (like separate patches). Other times, two sides of two polygons may touch each other (when the polygons are adjacent like on a chess board). The polygons never cross each other. In other words, their intersection is always empty.

I need to check if a point is contained in any one of those polygons. Is there a way I can build a single geometry using these polygons and check if the point is in it?

Currently I'm building a long WHERE close with OR conditions and do something like the below:

st_contains(st_GeomFromText('POLYGON(("+polygon+"))'), st_GeomFromText(CONCAT('POINT()')))

How about ST_Contains() or ST_Within()?

https://dev.mysql.com/doc/refman/5.7/en/spatial-relation-functions-object-shapes.html

Perhaps you need AND ST_Distance(...) > 0?

More likely, you want AND NOT ST_Touches(...).


mysql threads running high issue for Percona Mysql 8.0.16 we are experiencing a threads running high issue

mysql> show global status like '%buffer_pool%'
    -> ;
+-----------------------------------------+--------------------------------------------------+
| Variable_name                           | Value                                            |
+-----------------------------------------+--------------------------------------------------+
| Innodb_buffer_pool_dump_status          | Dumping of buffer pool not started               |
| Innodb_buffer_pool_load_status          | Buffer pool(s) load completed at 191011  8:32:57 |
| Innodb_buffer_pool_resize_status        |                                                  |
| Innodb_buffer_pool_pages_data           | 7342440                                          |
| Innodb_buffer_pool_bytes_data           | 120298536960                                     |
| Innodb_buffer_pool_pages_dirty          | 1098108                                          |
| Innodb_buffer_pool_bytes_dirty          | 17991401472                                      |
| Innodb_buffer_pool_pages_flushed        | 32982303                                         |
| Innodb_buffer_pool_pages_free           | 13213728                                         |
| Innodb_buffer_pool_pages_LRU_flushed    | 0                                                |
| Innodb_buffer_pool_pages_made_not_young | 58099577                                         |
| Innodb_buffer_pool_pages_made_young     | 64937403                                         |
| Innodb_buffer_pool_pages_misc           | 415352                                           |
| Innodb_buffer_pool_pages_old            | 2710229                                          |
| Innodb_buffer_pool_pages_total          | 20971520                                         |
| Innodb_buffer_pool_read_ahead_rnd       | 0                                                |
| Innodb_buffer_pool_read_ahead           | 55106                                            |
| Innodb_buffer_pool_read_ahead_evicted   | 0                                                |
| Innodb_buffer_pool_read_requests        | 344963685851                                     |
| Innodb_buffer_pool_reads                | 6418361                                          |
| Innodb_buffer_pool_wait_free            | 0                                                |
| Innodb_buffer_pool_write_requests       | 17527245765                                      |
+-----------------------------------------+--------------------------------------------------+

Could anyone please advise?

Thanks

Lots of threads?  Do SHOW PROCESSLIST.  If most say Sleep, then there is nothing to worry about.

Load Average = 2 and CPU = 15%.  Do you have 16 cores in your CPU?  Of so, that says you are using about 2 of the 16.  Again, no problem.

Are queries taking a long time?  Let's see them, their EXPLAIN, and SHOW CREATE TABLE.

How much RAM do you have?  How big is your dataset?  What is the value of innodb_buffer_pool_size?


Reset mongo cache memory for test perfomance - windows recently i'm working on mongo db and I'm trying to check the performance of mongodb with 2 million record. I need to clear cache every time I start the test. I don't want to restart the computer/server each time I want to run the test. I tried to clear collections cache but the result wasn't as I expected. Is there a way to clear cache like the way exist in linux ?

stop mongod
sh -c "sync; echo 3 > /proc/sys/vm/drop_caches"
start mongod

stop mongod: Stops the database service. sh -c "sync; echo 3 > /proc/sys/vm/drop_caches": write any data stored in memory to disk. start mongod: Start the database again.


SQL Server 2012 Express Report Services report builder button not visible I am trying to run SQL Server 2012 Express Report Services on Windows 7. I finally have it configured, but I'm not sure the missing steps that is preventing the Report Builder button from being visible. My desktop only has one account (administrator) and does not require any password when booting up, so I'd presume that when I launched the Report Services through an IE browser via, localhost/Reports, that I'm logged in as an administrator. Is the button not showing up because of some role is not defined and not tied to my desktop admin account?  Below is the display of the Report Services without the Report Builder button.

You can still install through ClickOnce deployment via (assuming you have configured SSRS with ReportServer as the installation path):

http://<servername>/reportserver/reportbuilder/ReportBuilder_3_0_0_0.application

It's still functional, but it's not full-featured.

Source

For example, Report Models are not supported and neither are ad-hoc queries through Report Models. This is an SSRS Express limitation which will manifest in Report Builder. Additionally, the language of the Supported Features of SSRS Express are somewhat ambiguous (nature of the English language) especially with regards to this line:

  Ad hoc reporting through semantic models and Report Builder is not supported.

What does the "and" connect and how does it connect them? Is it the clause "semantic models and Report Builder" or is Ad hoc reporting not supported through both semantic models and Report Builder. Unfortunately, the answer to this question could be more readily parsed at Linguistics.SE. But I digress. Suffice it to say, Report Builder is available to you in SSRS Express.


Non Buffer Latch - Forefront Identity Manager We are using Forefront Identity Manger and we are doing an initial load to which we are experiencing slow performance.

I always start with wait types for such things and can see %30 CXPACKET, %25 LATCH_EX and %15 ASYNC_NETWORK_IO.

I understand that LATCH_EX is a non buffer wait type so I looked in sys.dm_os_latch_stats and can see that %87 of the latch waits is down to ACCESS_METHODS_DATASET_PARENT.

Is this a problem?  Even on Paul Randals blog I am unclear what my issue is if at all.

Thanks

You mention Paul Randal in your question, so I assume you have seen this post where he talks about this stuff in great detail.   The ACCESS_METHODS_DATASET_PARENT Waits frequently deal with Parallel queries (as do your 30% CXPACKETS) and as Paul says in the blog that I linked, don't knee-jerk and set MaxDOP to 1 to get rid of those.   But I would wager based on the information provided that you do have some parallelism going on within your server, possibly for a bunch of things.   The question for you is do you think this is a problem?   That will depend on your situation, what the server does, how things are set, etc...     Another good link that describes this wait can be found here and in this case a very specific situation is described (and sounds similar to your situation) and the resolution to this one is described in the link.

Depending on what you find to be true on your server, you may want to run some tests with MaxDOP or possible with changing the Cost Threshold For Parallelism (it defaults to 5 and in todays world that is frequently too low, but you need to see for yourself for your particular server).   Cost Threshold For Parallelism is one that Jeremiah Peschka says is one of the 5 settings to change for SQL.   Read through those links run some tests with those properties on your server if you have the ability to do so and hopefully that will help to get you where you want to go.


How to group all linked objects in a undirected cyclic graph I have a table that has customers and its joint customers. e.g Customer 1 has a joint customer 2. Customer 1 is also a joint customer to Customer 3.

I am trying to group all customers that are linked and assign same GroupCustNo to them. In below table 1-2 are linked, 3-1 are linked. So 2-3 are also linked. All customers from 1 to 8 in below table are thus linked to each other and will have same GroupCustNo.

 tbl_GroupCustomers:

CustNo   JtCustNo  GroupCustNo
---      -------     ------
1           2          null
2           null       null
3           1          null
4           1          null
4           5          null
5           6          null
5           7          null
6           null       null
7           null       null
8           5          null

The recursive stored procedure I wrote is below. I am calling this in a while loop for each CustNo:

exec usp_UpdateGroupCustomerNo 1, 1
The stored procedure ran successfully for most of the customers but threw up

Recursion Limit of 32 reached
error for some customers. These are customers that have many joint customers and are also Joint Customers to other customers.

It seems recursion wont work here and I am at a loss on how to proceed. Please let me know if there are any alternate methods to address this problem.

CREATE PROCEDURE [dbo].[usp_UpdateGroupCustomerNo]
     @MainCustNo int, @GrpNo int
AS
    declare @JtCustNo int; declare @MainCustNo2 int;

if exists(select 1 from tbl_GroupCustomer
          where CustNo = @MainCustNo and groupcustomernumber is null)
begin
    update tbl_GroupCustomer
    set groupcustomernumber = @grpno
    where CustNo = @MainCustNo
      and groupcustomernumber is null

    DECLARE db_cursor CURSOR LOCAL FOR
         select JtCustNo
         from tbl_GroupCustomer
         where CustNo = @MainCustNo and JtCustNo is not null

    OPEN db_cursor

    FETCH NEXT FROM db_cursor INTO @JtCustNo

    WHILE @@FETCH_STATUS = 0
    BEGIN
        select @JtCustNo as JtCustNo

        exec usp_UpdateGroupCustomerNo @JtCustNo, @GrpNo

        DECLARE db_cursor2 CURSOR LOCAL FOR
            select CustNo
            from tbl_GroupCustomer
            where JtCustNo = @JtCustNo
              and groupcustomernumber is null

        OPEN db_cursor2

        FETCH NEXT FROM db_cursor2 INTO @MainCustNo2

        WHILE @@FETCH_STATUS = 0
        BEGIN
            if exists(select 1 from tbl_GroupCustomer
                      where CustNo = @MainCustNo2
                        and groupcustomernumber is null)
            begin
                exec usp_UpdateGroupCustomerNo @MainCustNo2, @GrpNo
            end

            FETCH NEXT FROM db_cursor INTO @MainCustNo2
        END

        CLOSE db_cursor2
        DEALLOCATE db_cursor2

        FETCH NEXT FROM db_cursor INTO @JtCustNo
END

CLOSE db_cursor
DEALLOCATE db_cursor

END

The underlying difficulty with this problem is that a table with a self-referencing key, as a data structure, is an implementation of a vertex ( or node )-based digraph.  One way of enabling bidirectional traversals in a relational database is to break out the self-referencing key as a separate table, so that the vertices of the graph can be represented by an entity table ( for instance, a Customer table, which you likely already have ) and the edges can be represented by the new relationship table ( your GroupCustomers table in this case, except the GroupCustomerNo would likely be moved to be an attribute of the Customer table ).

While the collection of edges can be leveraged in as many ways as you can imagine ( a linked list with "next" and "previous" attributes, an n-ary tree with "branch1", "branch2", ..., "branchn"; whatever it is you need your data model to do at this point ), a simple directed edge is often sufficient, as a directed edge contains enough information to represent both directions without the need for storing an additional record for the representation of the other direction - you merely have to swap the vertices.

With that in mind, in order to perform a bidirectional traversal of your tbl_GroupCustomers, simple recursion will be sufficient, though there is some preparation of the data necessary for the traversal.  For this scenario, simulating the graph from the digraph with a CTE seems reasonable, then a relatively run-of-the-mill recursive CTE to perform the traversal on the simulated graph.

USE tempdb;
GO

CREATE TABLE dbo.tbl_GroupCustomer
(
    CustNo                      INTEGER,
    JtCustNo                    INTEGER,
    GroupCustNo                 INTEGER
);

INSERT  INTO dbo.tbl_GroupCustomer ( CustNo, JtCustNo, GroupCustNo )
VALUES  ( 1, 2, NULL ), ( 2, NULL, NULL ), ( 3, 1, NULL ), ( 4, 1, NULL ),
        ( 4, 5, NULL ), ( 5, 6, NULL ), ( 5, 7, NULL ), ( 6, NULL, NULL ),
        ( 7, NULL, NULL ), ( 8, 5, NULL ), ( 9, NULL, NULL ), ( 10, 9, NULL );
GO

CREATE PROCEDURE dbo.usp_UpdateGroupCustomerNo
     @MainCustNo                INTEGER,
     @GrpNo                     INTEGER
AS BEGIN
    SET NOCOUNT ON;

    ;WITH
    cte_Graph AS (
        -- Simulate bidirectional relationships;
        SELECT  CustNo, JtCustNo
        FROM    dbo.tbl_GroupCustomer
        WHERE   JtCustNo IS NOT NULL
        UNION
        SELECT  JtCustNo, CustNo
        FROM    dbo.tbl_GroupCustomer
        WHERE   JtCustNo IS NOT NULL ),
    cte_Traversal AS (
        -- Regular traversal;
        SELECT  DISTINCT CustNo = NULL, JtCustNo = CustNo
        FROM    cte_Graph
        WHERE   CustNo = @MainCustNo
        UNION ALL
        SELECT  g.CustNo, g.JtCustNo
        FROM    cte_Traversal t
        INNER JOIN cte_Graph g
            ON  g.CustNo = t.JtCustNo
        WHERE ( g.JtCustNo <> t.CustNo
            OR  t.CustNo IS NULL ) )
    SELECT  *
    --UPDATE    gc
    --  SET GroupCustNo = @GrpNo
    FROM    cte_Traversal t
    INNER JOIN dbo.tbl_GroupCustomer gc
        ON  gc.CustNo = t.JtCustNo;

    SET NOCOUNT OFF;
END;
GO

EXECUTE dbo.usp_UpdateGroupCustomerNo @MainCustNo = 1, @GrpNo = 1;
EXECUTE dbo.usp_UpdateGroupCustomerNo @MainCustNo = 10, @GrpNo = 2;
GO

DROP PROCEDURE dbo.usp_UpdateGroupCustomerNo;
DROP TABLE dbo.tbl_GroupCustomer;
GO


Why is this column order in my non-clustered index better for my query? I am working on a query for a table that contains movie tickets. The database holds 380k rows. A rows represents a showing of the movie (which cinema, when, how many tickets  and at what price among other things).

I need to compute a few totals for every row: Admissions Paid, Admissions Revenue, Admissions Free and Total Admissions.

For a given row Admissions Paid is the sum of all tickets for that movie up until that point where price>0. The other 3 columns are computed similarly.

I wrote a query and created an index:

 SELECT [ID]
      ,[cinema_name]
      ,[movie_title]
      ,[price]
      ,[quantity]
      ,[start_date_time]
      ,* --I need all the columns for reporting
     ,(select SUM(quantity)
        from [movies] i
        where i.movie_title=o.movie_title
        and i.start_date_time<=o.start_date_time
        and price=0) as [Admissions Free]
        ,(select SUM(quantity)
        from [movies] i
        where i.movie_title=o.movie_title
        and i.start_date_time<=o.start_date_time
        and price>0) as [Admissions Paid]
        ,(select SUM(quantity*price)
        from [movies] i
        where i.movie_title=o.movie_title
        and i.start_date_time<=o.start_date_time
        and price>0) as [Admissions Revenue]
        ,(select SUM(quantity)
        from [movies] i
        where i.movie_title=o.movie_title
        and i.start_date_time<=o.start_date_time) as [Total Admissions]
  FROM [movies] o

I created the following index which brought the query time down to 5 minutes:

CREATE NONCLUSTERED INDEX [startdatetime_movietitle_price] ON [dbo].[movies]
(
    [movie_title] ASC,
    [start_date_time] ASC,
    [price] DESC
)
INCLUDE (   [quantity]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

But this index brought the query time down to 1:30:

CREATE NONCLUSTERED INDEX [startdatetime_movietitle_price] ON [dbo].[movies]
(
    [start_date_time] ASC,
    [movie_title] ASC,
    [price] DESC
)
INCLUDE (   [quantity]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

So my question is: why? From my understanding, it makes more sense to first gather all the movie titles and then look at the start times because there are more start times then there are movies. Distinct movies: 51, distinct start_date_times: 8786

Doesn't the underling B-Tree not cut off more branches if it eliminates the unnecessary start_date_times first?

Here are the execution plans:

The first picture shows the execution plan for the index with movie_title first, the other picture shows start_date_time first.

The first index does look like a better fit for the query. Please provide the actual execution plans.

I would try using window functions instead of the four correlated subqueries. Or a single correlated subquery (with OUTER APPLY) and see which of the two indexes is used.
Both ideas are to coerce the optimizer to use a single index scan to gather the rolling sums instead of 4 (that both your plans do).

It would also be worth checking and comparing the two execution plans, when asking for all the columns and when asking for only the columns in the index:

using window functions:

-- window functions
SELECT
    -- m.*,
    movie_title, start_date_time,
    price, quantity,

    SUM(CASE WHEN price = 0 THEN quantity ELSE 0 END)
        OVER
        (PARTITION BY movie_title
         ORDER BY start_date_time
         RANGE BETWEEN UNBOUNDED PRECEDING
                   AND CURRENT ROW
       ) AS [Admissions Free],
    SUM(CASE WHEN price > 0 THEN quantity ELSE 0 END)
        OVER
        (PARTITION BY movie_title
         ORDER BY start_date_time
         RANGE BETWEEN UNBOUNDED PRECEDING
                   AND CURRENT ROW
       ) AS [Admissions Paid],
    SUM(CASE WHEN price > 0 THEN quantity * price ELSE 0 END)
        OVER
        (PARTITION BY movie_title
         ORDER BY start_date_time
         RANGE BETWEEN UNBOUNDED PRECEDING
                   AND CURRENT ROW
        ) AS [Admissions Revenue],
    SUM(quantity)
        OVER
        (PARTITION BY movie_title
         ORDER BY start_date_time
         RANGE BETWEEN UNBOUNDED PRECEDING
                   AND CURRENT ROW
        ) AS [Total Admissions]
FROM
    [movies] AS m ;

*: If there is a UNIQUE constraint on (movie_title, start_date_time), then you could use ROWS instead of RANGE for the window frames (it's usually more efficient). From the comments, there is no such constraint and there could be many rows with same title and datetime, so RANGE is required above.

using OUTER APPLY:

-- using OUTER APPLY
SELECT
    -- m.*,
    m.movie_title, m.start_date_time,
    m.price, m.quantity,

    c.[Admissions Free],
    c.[Admissions Paid],
    c.[Admissions Revenue],
    c.[Total Admissions]
FROM
    [movies] AS m
    OUTER APPLY
    ( SELECT
          SUM(CASE WHEN i.price = 0 THEN i.quantity ELSE 0 END)
              AS [Admissions Free],
          SUM(CASE WHEN i.price > 0 THEN i.quantity ELSE 0 END)
              AS [Admissions Paid],
          SUM(CASE WHEN i.price > 0 THEN i.quantity * i.price ELSE 0 END)
              AS [Admissions Revenue],
          SUM(i.quantity)
              AS [Total Admissions]
      FROM [movies] AS i
      WHERE i.movie_title = o.movie_title
        AND i.start_date_time <= o.start_date_time
    ) AS c ;

This index may be a little better than the first one:

(
    movie_title ASC,
    start_date_time ASC
)
INCLUDE (price, quantity)


Shrinking SQL Server database I have a question about SQL Server databases.

I have initialized a database (let's say Configuration_DB) of size 120GB following with certain application installation. This application installation has taken up 20GB out of that 120GB DB space and 100 GB is left free.

Now if I run shrink job on that, will it be reduced to 20Gb (plus something) or how much it can be reduced to, although the Configuration_DB.mdf file has already taken 120 GB on disk.

Thanks.

You can shrink you database file using SQL Server Management Studio.

  In Object Explorer, connect to an instance of the SQL Server Database Engine and then expand that instance.
  Expand Databases and then right-click the database that you want to shrink.
  Point to Tasks, point to Shrink, and then click Files.

Source: https://docs.microsoft.com/en-us/sql/relational-databases/databases/shrink-a-file

Please be advised, that the databse needs at least some free space to work. So don't shrink it all the way down to 20 GiB


postgres service suddenly not working after the computer crashed while working I've been working with postgres on a linux server for the last couple of months. Last week, half of our servers crashed and my one with them. I was in the middle of uploading data to the postgres database with osm2pgsql when this happened. After we revived the server, postgres has stopped working.

commands:

service postgresql start
(Alternatively, /etc/rc.d/init.d/postgresql start, same thing happens)
Starting postgresql service:  [  OK  ]

service postgresql status

postmaster dead but pid file exists

Trying to start the postmaster using

su postgres
postmaster -D /var/lib/pgsql/data

yields nothing either.

As for the pid file, /var/run/postmaster.5432.pid exists, and when I remove it then the status changes to "postmaster is stopped", but if I try to start it again:

service postgresql start
Starting postgresql service: [FAILED]

If anyone has any idea what I could do to get it working, where the problem might be. What really gets me is that it was working until last week, I even rebooted a backup and nothing seems to have changed, I'm really at my wit's end with this problem!

When I faced this message, I checked the postgresstartup.log, it showed that the data directory was having permissions as 777,After I changed to 700 , the postgresql started working

sudo su - postgres

chmod 700 -R <postgresql_Data_Directory>

exit

/etc/init.d/postgresql start

/etc/init.d/postgresql status


Backup Compression in SQL Server 2005 I noticed that simply zipping my backup files reduces the space used by 70%

My idea was to, initially, backup the databases to a local server; then, using Bacula or Cobian (I don't have a budget), compress it and then ship it to our remote SAN.

Did you face similar challenge? If so, how did you overcame it?

There are commercial (Redgate for example) and free products that will do this for you as part of the backup.

http://sourceforge.net/projects/mssqlcompressed/


Merge replication gone wrong, and snapshot is timing out I have a merge replication set up between one Publisher and 3 Subscribers, replicating a single database with around a 100 different tables of various sizes. It's "real-time" replication running continually 24x7, and it's worked fine for the last year or more: the volume of changes is small but constant throughout the day.

However, last night it all started to go wrong. Several tables in the database were "locked": applications couldn't access them. I did the usual reboot, and everything seemed fine, and I didn't think much of it. However, this morning the same thing was happening: vital tables were locked, and digging into sp_who2 showed that replmerg was chewing on something.

I tried to reinitialise the subscription; this seems to have made things worse: the replmerge log is telling me that the snapshot is out of date. When I try to run the snapshot agent - which I've done a few times in recent memory and it's never taken more than a couple of minutes - it gets 46% of the way through before telling getting stuck at:

[46%] Setting up the publication for filtered articles

... and eventually timing out. I've dug into the queries that are running when it's stuck, and it appears to be chewing on:

select @changes_in_tombstone = count(*)
  from dbo.MSmerge_tombstone with (rowlock)
  where generation = @g

I've looked at the MSMerge_tombstone table, and it's got 21 million rows in it (a 14 day retention time): I've no idea if this is good or bad.

So I don't know how to proceed: I really don't want to have to delete and recreate the publication - there are changes at the subscribers that I don't want to lose - but it appears I'm stuck in a Catch 22: I need to create a new snapshot in order to get the replication going again, but I can't create a snapshot as it times out.

Should I resign myself to recreating the publication, or is there a way to fix this?

Timing out is probably due to performance.
Reduce the retention period gradually and reinitialize all the subscribers. I've copied and pasted the steps to do this from Claudia Silva's technet blog below:

Evaluate the number of rows that will be deleted:

Use coldate from Msmerge_genshistory to get the list of generations that will be removed
With that list (generation) get the number of rows that will be deleted in other system tables

Example:
Select generation from dbo.MSMerge_genhistory where coldata < ‘[current data – retention period you desire]’

Example:
So, from today, the 5th of April 2016, I would like a retention of 7 days: < 29th of March 2016

With the list of generations generated, we can see how many rows of data will need to be deleted from the other system tables (MSmerge_contents / MSmerge_tombstone) as well. If the number is quite high, then we increase the datetime: (example) < 24th of March 2016

Depending on the amount of data needed to be deleted, one may need “one to many” intermediate steps before reaching the target retention desired.

The key to this being a success is estimating the effect of the deletes that needs to be executed (by default batch of 5000 rows).

http://blogs.technet.com/b/claudia_silva/archive/2009/06/22/replication-infinite-retention-period-causing-performance-issues.aspx
and
http://msdn.microsoft.com/en-us/library/ms178557.aspx


Restore encrypted database to another server I'm using a product which runs on SQLServer 2008. Understandably the company supplying it don't offer SQLServer support. When I installed the product I specified a password to encrypt the database. I'd like to run another copy of the product for testing on another server. I've restored the database to another server and installed the product on that other server. When I installed it I supplied the same password, and then restored a backup from the main server. However I'm getting the error message:

System.Data.SqlClient.SqlException: An error occurred during decryption.

from the product. I can access the tables using SQLServer Management studio.

I have tried this:

on first server:

CREATE CERTIFICATE cert1 WITH SUBJECT = 'Certificate for my stuff'

BACKUP CERTIFICATE  cert1 TO FILE = 'd:\backup\cert1.dat'
WITH PRIVATE KEY
(
ENCRYPTION BY PASSWORD = 'mypassword',
FILE = 'd:\backup\cert1_privatekey.dat'
)

on second server:

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'mypassword'

CREATE CERTIFICATE cert1 FROM FILE = 'd:\cert1.dat'
WITH PRIVATE KEY
(
FILE = 'd:\cert1_privatekey.dat',
DECRYPTION BY PASSWORD = 'mypassword'
)

I also tried this on the second server:

alter MASTER KEY regenerate with enCRYPTION BY PASSWORD='password'

but that gave an error message about an asymmetric key.

How can I restore the backup from the first server onto the second server?

Update:

There is an asymmetric and symmetric key in the database as well. If I open the symmetric key using the asymmetric key I get the same error, so I think that's why it's not working - somehow the keys aren't transferring in such a way that they can be used.

I wrote a blog entry about mirroring and TDE.

It took me a while to figure out that to get the db on the secondary server, I needed the code line:

 OPEN MASTER KEY DECRYPTION BY PASSWORD = 'SomePassword'
 ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
 GO

The password is the same one that I used to encrypt the master key file. After issuing the command on the 2nd SQL server, everything played nicely. Here is the db.stackexchange post I made also.


How large will a MySQL database be relative to the dump file? I'm restoring a ~52GB dump to a MySQL database. The ibdata1 file has already exceeded the size of the dump file and the restore is still incomplete. Is there any way to estimate the final size of the ibdata1 file if the size of the MySQL dump file is known?

Just from the words in your question I suspect the following: You most likely have innodb_file_per_table disabled.

NOTE : The following info is based on innodb_file_per_table being disabled

When insert data into InnoDB tables, everything and its grandmother lands in the system tablespace file, better known as ibdata1. What does ibdata1 actually contain?

Table Data
Table Indexes
Metadata
MVCC Info

Table Data and Indexes while initially bloat ibdata1. The metadata is just simply the data dictionary + the list of tablespace_ids assign on a per-table basis.

What about MVCC (Multiversioning Concurrency Control)? This represents the systrem objects designed to support transaction isolation, rollbacks, undo logs, insert buffers for secondayr indexes, and the double- write buffer.

You need to clean up the InnoDB infrastructure. I already wrote StackExchange posts on how and why to do this:

Why does InnoDB store all databases in one file?
Is innodb_file_per_table advisable?
https://stackoverflow.com/a/4056261/491757
https://serverfault.com/a/231400/69271

Getting back to your original question, the only way to estimate the size of the ibdata1 on reload would have been to run this query before the mysqldump:

SELECT
    data_length/power(1024,3) InnoDBData,
    index_length/power(1024,3) InnoDBIndexes,
    (data_length+index_length)/power(1024,3) InnoDBSize
FROM
    information_schema.tables
WHERE
    engine='InnoDB';

This will report the size of the data in GB. Upon a fresh reload of the ibdata1 (with innodb_file_per_table disabled in your case), this would have been the rule of thumb for the size estimation.

From the dump file size, it is hard to judge because the combined total size of data pages and index pages maybe far less that the size of ibdata1 the dump was created from. That difference would be have leftover space from the bloating of MVCC system objects (rollback segments, undo logs, double-write buffer, secondary index insert buffer). From another perspective, data pages could outnumber index pages, and vice versa. This could be due to too many indexes, bad design, or just a judicious amount of data.


Ordering in subqueries This is my query:

select locid,tlid,locdescription from (

  select locid, tlid, locdescription
  from
  trip_log left join trip_log_det on tlid=tldtripid
  left join locations on tldlocation=locid
  where tlid=50 order by tldid

) as foo group by locid,tlid,locdescription

The point of using a subquery is to get data back ordered as per the internal query, so sorted by tldid. I do not need tldid in the data as it is a serial column and it would nullify the group by statement.

Is there a way to tell the subquery to maintain the ordering of the internal query?

You don't need a subquery at all. But you have to decide what ordering you want.

After the group by there is no tldid values any more. And every group will be made by collapsing many rows (with different tldid values) into one row. Which of them should be used for the ordering? Is it the maximum, the mimimum or some other?

You can write for example:

select locid, tlid, locdescription
from
  trip_log left join trip_log_det on tlid = tldtripid
  left join locations on tldlocation = locid
where tlid = 50
group by locid, tlid, locdescription
order by min(tldid) ;

In Postgres, you could also use the non-standard DISTINCT ON syntax to achieve the same result. This is more flexible, as you can have the tldid and other (non-grouped) columns in the select list as well:

select distinct on (locid, tlid, locdescription)
    locid, tlid, locdescription
from
  trip_log left join trip_log_det on tlid = tldtripid
  left join locations on tldlocation = locid
where tlid = 50
order by locid, tlid, locdescription,
         tldid asc ;


Very Simple SELECT Takes Too Long (300k rows, 60s) Scenario
I have a table that holds aggregated data to support a PowerBI report.  The table is only 8 columns, and uses INT, VARCHAR and DATETIMEOFFSET types.  There's a regular PK int identity column.  There is a clustered index on the PK.
There are 300k rows
I'm on db.t3.small instance on AWS RDS (2 vCPU, 2GB RAM)
My CPU is not spiking, and the DB is not being used for anything else at all -- this server is a clone for testing.
Schema:
id: PK, int, not null
column1: varchar(16), not null
column2: varchar(50), not null
column3: varchar(50), not null
column4: int, not null
column5: int, not null
column6: datetimeoffset(7), not null
column7: int, null

Problem
Just a simple select takes a full minute, and that seems slow since there's no joining or even calculations.  It's literally just selecting those rows.
SELECT * FROM report_MyTableName

Questions

Is that a reasonable amount of time to wait?  It just seems like such a long time to wait for a relatively few/moderate number of rows.
Why does it show that there are 6 selects? there's no view or joining involved so I assumed 1 select.
Why are there 7 round trips to the server?
Is there a practical way to improve performance for this query?
I ran a query on a similar table (with 214k rows) and it took < 2s on my local SQL Server instance on my laptop.  I also had a coworker run a similar query on a different DB server (which admittedly has more horsepower) and it took < 10 seconds.

Data

If I've left out anything important let me know.  I'm happy to update.
Requested Info
Here's the output from COUNT(*)

And here's the output when I ran with STATISTICS IO, TIME
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 0 ms.

(306214 rows affected)
Table 'report_MyTableName'. Scan count 1, logical reads 3098, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(2 rows affected)

(1 row affected)

 SQL Server Execution Times:
   CPU time = 218 ms,  elapsed time = 42203 ms.
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

Completion time: 2021-01-28T17:06:36.4403170-05:00

Actual execution plan

The 6 Selects are due to live statistics. Never mind them.
Based on the fact that Client procesing time takes about 99% of the total time, issue is definitely network/client.
As proposed, try the
select count(*) from report_MyTableName

or
select * into #tmpTable from report_MyTableName

to check performance without network.
In statistics, there is the total amoun received from server. too bad the full info is missing so we can only guess about amount of data vs network speed.
Either provide full info on bytes send or info about columns (types and size) so we can estimate the data size.
EDIT:
So based on the count(*), it is obvious the total amount DB server works on it is about < 0.1s. The CPU time matches but the elapsed time is not normal.
How long did the count(*) take? Live query show ~0.25s, which seems right, elapsed time shows 42 seconds.
My guess is still that the issue is network/client, not server.
What is your ping against the server?


mysql - Right join not retriving correct data I am storing the user activity in the table by start and end time , now i want to get all records from my subject table and matched records from log table.

This is my subject table

This is my log table

I want the output like this

I tried using some code but the records that only matched with log table is return as record, This is what i tried. Any help is appreciable.

SELECT SUM(TIMESTAMPDIFF(MINUTE, A.start_time, A.end_time)) AS prep_time,
                    B.subject_name,
                    A.subject_id
                FROM prep_learn_log A
                RIGHT JOIN prep_subject B ON A.subject_id = B.subject_id
                    AND B.active = 'Y'
                WHERE A.user_id = '1' GROUP BY A.subject_id

Your problem is in the WHERE clause

WHERE A.user_id = '1'

You are creating correctly the RIGHT JOIN but this condition is forzing to showthe row only if the Log table (renamed as A) is equal to 'Y'.
You need to change this condition for example:

SELECT SUM(TIMESTAMPDIFF(MINUTE, A.start_time, A.end_time)) AS prep_time,
                    B.subject_name,
                    A.subject_id
                FROM prep_learn_log A
                RIGHT JOIN prep_subject B ON A.subject_id = B.subject_id
                    AND B.active = 'Y'
                WHERE COALESCE(A.user_id, '1') = '1'
                GROUP BY A.subject_id

I


How to unify two records with same field? I have a table that get records like below:

SELECT cod, name, doc1, cpf, the result is:

cod | name  | doc1 | cpf
   1 | john  |   12 |   0
   1 | john  |    0 | 123
   2 | mary  |   25 |   0
   2 | mary  |    0 | 456
   3 | andy  |   48 |   0
   3 | andy  |   55 | 789

How to unify the registers with some cod like the result below?

cod | name | doc1 | doc2 | cpf
   1 | john |   12 |    0 | 123
   2 | mary |   25 |    0 | 456
   3 | andy |   48 |   55 | 789

Use GROUP BY to get one output row per cod, and use aggregate functions to select one value to output. For the doc values, use CASE to select only one of them (it returns NULL in a row where it does not match):

SELECT cod,
       name,
       max(CASE date_row WHEN 1 THEN doc1 END) AS doc1,
       max(CASE date_row WHEN 2 THEN doc1 END) AS doc2,
       max(cpf) AS cpf
FROM my_table
GROUP BY cod,
         name
ORDER BY cod;


When to use full backup or full backup and then differential backup There is a SQL Server 2012 database that has the following configurations

Recovery mode : Simple
Backup Schedule : Full backup at 01:00 hours daily as well as differential database backup 13:00 hours daily

Once the differential backup of this database failed at 14:00 hours, the database then failed.

I need to restore this database and ensure the data loss is minimal.

Shall I choose

Restore the latest full backup, or
Restore the latest full backup, then restore the latest differential backup

Restore the latest full backup (using the with norecovery option) and the last good differential.


Mysqldump --flush-logs results in small backup files? New to MySQL and just learning how to setup a daily full backup (binary logs enabled). My goal is to run a daily full backup of the databases/schemas and reset the binary log.

I'm currently using an edited script that creates individual backup files for each schema which is great.

Each run through the loop executes this against a database and all looks great except the binary log does not reset.:

%mysqldumpexe% --user=%dbuser% --password=%dbpass% --routines --master-data=2 --databases %%F --log-error=%errorLogPath% > "E:\MySQLBackup\Database\%%F.%backuptime%.sql"

When I add the --flush-logs parameter to the line and run it, it:
1) Creates separate binary logs for each database (currently I have only one)
2) Creates full backup files that are 1-2kb with no data?!

What am I doing wrong here? I want the full backup to be a complete dump of the database at the time and also reset the binary log so it contains the changes just after the full backup.

Thanks in advance

This is related to a bug with new installs of mysql on debian wheezy whereby the error.log file is owned by root.root, as per this:
http://tipstricks.itmatrix.eu/?p=1352

I fixed it with
chown mysql.adm /var/log/mysql/error.log


Moving SQL Server Agent Job Categories and Schedules I want to copy a set of Job Categories and Schedules between MS SQL Servers (2012/2014).

select * FROM msdb.dbo.sysschedules

Returns all schedules.

select * FROM msdb.dbo.syscategories
WHERE category_id IN (SELECT DISTINCT category_id FROM msdb.dbo.sysjobs)

Returns all my job categories.

To add them i could use the stored procedures:

sp_add_category
sp_add_jobschedule

I can get it done with this information, but it would almost be as tedious as re-doing them manually.

How can i do this in a more effective way?

Another cool powershell utility that I use everyday for server migrations is Start-SqlServerMigration.ps1 written by Chrissy LeMaire

It is very adaptive with different options.

Eg. In your scenario for migrating jobs with schedule, you can use

  .PARAMETER MigrateJobServer

  Migrates all job server objects, including proxy accounts, job schedules, shared schedules, alert system, job categories, operator categories, alert categories, alerts, target server groups, target servers, operators, and jobs. Existing objects will not be deleted, and no -force option is available.

e.g. The PS below code will move all the databases (overwrite if the same name databases exists since it uses force parameter) from Source sqlserver\instance to Destination sqlcluster. It will exclude Northwind, pubs databases. It will migrate all the logins except nwuser, pubsuser, "corp\domain admins", will migrate all the sql agent jobs, will export the sp_configure options from source to destination server.

.\Start-SqlServerMigration.ps1 -Source sqlserver\instance -Destination sqlcluster -AllUserDBs -ExcludeDBs Northwind, pubs -IncludeSupportDBs -force -AllLogins -ExcludeLogins nwuser, pubsuser, "corp\domain admins"  -MigrateJobServer -ExportSPconfigure -UseSqlLoginSource -UseSqlLoginDestination


Entries with 2 variables. Do I need to make 2 more tables by entries? In the case of shipment carriers, I have a table with products whose price and delay will vary with 2 variables.

PRODUCT   | SHIPPING DATE | PRICE
Carrier 1 | A             | B
Carrier 2 |               |
...       | ...           | ...

A : Shipping date will vary with the shipment departure date
B : Price will vary with the weight of the shipment

I will need approximately 20 carriers.

I think I will have to make 2 tables by carrier:

TABLE "Carrier 1 WEIGHT-PRICE"
WEIGHT   | PRICE
0-10 kg  | 10 €
10-12 kg | 12 €
12-16 kg | 14 €
...      | ...

TABLE "Carrier 1 DEPARTURE-SHIPPING DATE"
DEPARTURE HOUR (between) | SHIPPING DELAY
00h am : 09h am          | 12h
09h am : 01h pm          | 24h
01H pm : 06h pm          | 36h
06h pm : 12h pm          | 48h

I hope there is a more optimal solution than that. Any ideas?

I will finally make the DB like this:

prices(id [pk][fk], name, min_weight, max_weight, price)

delays(id [pk][fk], name, min_hour, max_hour, delay)

produit(id [pk], id_price [fk], id_delay [fk])

Is it correct?

Colin 't Hart is correct. Do not mix data in your table names.

Instead, create a Carrier table consisting of an identifier and the name of the carrier.

TABLE "Carrier"
ID  | Carrier
22  | Carrier1
31  | Carrier2

Reference the carrier in the weight price and shipping departure tables via the identifier as a FK. So in the case of the weight price table you may have many references to each carrier.

TABLE "WEIGHT-PRICE"
CarrierID | WEIGHT   | PRICE
22        |0-10 kg  | 10 €
22        |10-12 kg | 12 €
22        |12-16 kg | 14 €
31        |0-10 kg  | 9 €


How long does it take for Oracle Flashback Database to restore the data? How long does it take for Oracle Flashback Database to restore the data? Is it an instantaneous operation or dependent on the data volume?

Flashback Database

Flashback Database relies on flashback logs (stored in the flash recovery area) to restore a database to a particular point in time. The time taken to restore a database using Flashback Database would therefore be dependent on the amount of flashback log created between the restoration period and the current point in time. See this link for more information & limitations.

I'll add that it's lots quicker than a point-in-time restore using RMAN (I imagine that's what you were eluding to when you asked the question in the first place), but there is also the extra overhead in writing flashback logs that needs taking into account.


Update XML metadata in SQL Server I'm trying to change the value in the XML metadata using SQL Server.
The XML file looks like this:

<Root xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="MyOldValue">
<SomeTag></SomeTag>

What I want to do is to change xmlns to a new value. I tried casting the xml as nvarchar(max), using replace and then casting back to xml but it didn't work (string truncation).

I tried using XQuery but I kept failing. Can you recommend a solution?

I try this queries is ok.

DECLARE @xml XML='<Root xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="MyOldValue"><SomeTag></SomeTag></Root>';

SELECT @xml = CAST( REPLACE(CAST(@xml AS NVARCHAR(MAX)),'MyOldValue','MyNewValue') AS XML);

Select @xml;


No parallelism in Express Edition of SQL Server I have SQL Server 2019 Express Edition (CU8) on Windows Server 2019 (1809 version) and all of my queries go serial with NoParallelPlansInDesktopOrExpressEdition in NoparallelPlanReason property.
Is it that Express Edition never goes parallel? I cannot find anything about this in Microsoft documentation.

The limitation is mentioned in the Query Processing Architecture Guide under the "Parallel Query Processing" section:

Parallel plans not supported for Desktop and Express edition.

The Editions and supported features of SQL Server 2019 (15.x) documentation says (under "Scale Limits") that Express is:

Limited to lesser of 1 socket or 4 cores

But that is just related to how many cores you can make available to the database engine.
It doesn't say anything about parallel plans (other than a limitation related to batch mode operations being limited to 1 core on Express Edition).
All that to say that it looks like Express really doesn't allow parallel plans at all.  I tried running queries on SQL Server 2019 and 2014 with the same NonParallelPlanReason given, so it doesn't appear to be a new limitation either.


How much data can SQL Server full text indexing handle? I realize that the question is vague and it depends on hardware and our needs.

We currently have 5 million rows of data, a total of 5GB of data which we want to index using full text indexing. Our data increases quite rapidly and it's not unreasonable to assume that in a few years it will be closer to a billion rows and a TB of data.

The index is searchable by web site users, and they expect responses within a second or two.

Is it reasonable to assume that this data set will be indexable using SQL Server 2012 full text indexing? Is it common to do full text indexing of this amount of data? And is there any good reading on the subject, for example from others' experience?

I've not implemented FTS anywhere near that scale personally but there are documented examples:

  In addition to being robust in its support of concurrent users,
  Metalife has found SQL Server 2005 full-text searches to be fast. “SQL
  Server 2005 is at least as fast as our custom code was,” says Pavlova.
  “We created a custom algorithm to return all full-text searches
  against our 1.4 terabyte database in less than a second, and that is
  what we’ve seen with SQL Server 2005.” [Source]

1.4TB is mentioned as the source database here but that doesn't necessarily translate to TB+ of full text indexed data.

This example is quite smart, extracting content from files before shuffling them off to cheap cloud storage:

  There are currently over 50 million files (over 10 Terabytes of data)
  that the customer is migrating into a custom project management
  application (for international customers) that can be accessed via the
  application and can be searched upon.  SQL Server 2008 full text
  indexing is used to index the content of these files which gives them
  rich searching capabilities within their application. [Source]

Newsgator is documented as running a 2.5 billion row, 4TB full text implementation and FileControl as having a 2 billion, 1TB system back in 2005. Given the other options available today, would they do the same now?

Given the licensing cost of SQL Server I'd be inclined to consider alternatives if they fit your use case. Lucene or Solr are the obvious open source choices.


Insufficient disk space in Filegroup One of my jobs is failing and it shows below message.

Error:

Could not allocate a new page for database 'Reporting' because
of insufficient disk space in Filegroup ‘PRIMARY’.
Create the necessary space by dropping objects in the filegroup,
or setting autogrowth on for existing files in the filegroup.
[SQLSTATE 42000] (Error 1101) The step failed.

Any idea how can this be resolved.

Thanks,
Ar

In simple terms you've run out of space in the primary filegroup of the Reporting database.

The data in a database is stored in FileGroups.  The main filegroup (and the one created by default) is Primary.  By default this is where everything is put unless you specify otherwise.  Most databases are just going to have the Primary filegroup.

The error you are getting is caused by running out of space in that Primary filegroup (obviously).  This can be caused by one of two possibilities.

Insufficient disk space.  Your database has expanded to the limit of the drive it's on.  You can easily check this by looking at your server and seeing if the drive the database is on is full.  The solutions here are to either expand the drive, reduce the amount of data in the database, or add additional files to the Primary filegroup on a different drive (or additional filegroups and move data into them).
Your autogrowth settings have limited your growth.  This could be one of a couple of things.

You could have autogrowth turned off entirely.  Basically you have used up the available space in the database and it can't grow by itself.  Typically if you are going to do this then you need to be monitoring the free space in your database and manually grow it when you start running low.
You have a size limit on the files in the filegroup.  In this case you can either increase the size limit (and manually grow the DB) or add additional files as I mentioned above.

To do any of these tasks (except looking at the physical space of the drive) you can use the GUI.  Go to the properties of the database in SSMS.  (Right click on the database name and select properties at the bottom). Then Navigate to the Files tab (you can only control the size of the files within the filegroup)

From here you can see which files are in the Primary filegroup and control their size (Initial Size) & autogrowth settings (hit the ellipsis ...)

The autogrowth settings look like this:

You can see here you can control the max size of the file & if autogrowth is allowed at all.