Postgresql: Refreshing materialized view fails with "No space left on device" and a huge spike in disk usage I have a materialized view that refreshes every five minutes. The SQL aggregates the data among many tables with over 800k rows in each.
However, the when using the "REFRESH MATERIALIZED VIEW CONCURRENTLY tableName", the query runs for about an hour and then complains:
ERROR:  could not write block 39760692 of temporary file: No space left on device
It should be noted that this 39760692 changes every time I execute the query.
The disk size is about 960 GB and the database size is about 30 GB. So the disk has a free space of about 930 GB.
I noticed that when running the refresh query, there is a huge spike in the disk usage of about 12GB per minute and then finally the query errors out with no space error when it hits the 960 GB mark. Immediately, the disk usage is back to 30GB from the abnormal growth.
I even tried the REFRESH MATERIALIZED VIEW tableName (without concurrently) and seeing the same behavior.
I'm not sure what can be done here to diagnose the problem.

Find where postgres writes temp files for you and follow it during the refresh. As you are processing alot of data inside the database, postgres has to write a huge temp file and as this fills your disk, you get an error.
You probably have to find a more efficent way to write your query.


How to Migrate complete Transactional Replication to other servers without Snapshot? We have large databases that are being kept in sync using Transactional Replication. They are all running in VMs and we are going to move to a new provider and reorganize the network structure and application distribution.
Is it possible to migrate the Transactional Replication: Publisher, Subscriber, Distribution Database without having to take a new Snapshot?
Currently we have in Server_1 the Publisher and in Server_2 the Subscriber and Distribution Database.
We are going to move the Publisher to Server_3 and the Subscriber and Distribution Database to Server_4
I don't have much experience with SQL Replication so I have been reading a lot. I found this answer here. Reading it, I understand that if I follow these steps, moving the Replication to the new 2 servers should be possible:

During downtime we wait until all objects are in sync
Then Script out the Replication (does this include, Publisher, Subscriber and Distribution Database?),
Migrate the Publisher and Suscriber Databases to their new Locations
Create new Transactional Replication from Scratch (using the scripts generated from the existing Replication) with replication support only
Run snapshot jobs to ensure the creation of any System Objects missing.

However I don't know If I have all the data/steps/understanding required and if I am  missing anything important.
Also, would it be possible not to wait for all the objects to be in sync? I would like to test the process but I don't know if I will be able to shutdown the databases for that long just to test. So could We migrate both databases without having them perfectly in sync and regenerate the Replication without Creating a Snapshot? Maybe without the "replication support only" option and running the snapshot Jobs. If we run the snapshot jobs in that situation will they create a whole snapshot since the DBs won't be in Sync or will it only look for the differences?
I am sorry if my questions seem rather obvious, I haven't worked with this Technologies before and I want to make sure to do everything right.

I would like to test the process but I don't know if I will be able to shutdown the databases for that long just to test

You should test and automate this process in a non-production environment.


Removing chat history for a particular user from database I'm currently developing a web chat application using SignleR and SQL server this is my database schema so far

I've added an option where the user can Clear Chat history for a particular conversation , but I'm having trouble with the concept of clearing history for a particular user in wish case if user-1 hit clear history all the messages of this conversation will not be displayed for user 1 but will still be seen for user-2

How can I upgrade my database schema so I can make this functionality ?

PS : yesterday i asked the same question on stackoverflow website and someone gave me a solution .but the solution he gave me is complicated and it didn't fill my requirement .
my database is currently supporting only private chat between 2 users

A simple way to do this would be to create a new field in the Messages table called MESSAGE_VISIBLE_TO. This field should be of the same type as the USER_ID field and have a default NULL value. When User1 hits the "Clear History" button, the application would need some logic to update all of the messages between the two people, setting the MESSAGE_VISIBLE_TO to the ID of User2. Something like (passing in USER_ID for both User1 and User2):

UPDATE Messages
SET MESSAGE_VISIBLE_TO = @UserID2
WHERE MESSAGE_SENDER_ID = @UserID1 AND MESSAGE_SENDER_ID = @UserID2

The MESSAGE_VISIBLE_TO field could have 4 possible "states":

NULL = the default, both users can see the message
-1 = neither user can see the message
UserID1 = User1 ONLY can see the message
UserID2 = User2 ONLY can see the message

The application would need to incorporate the logic to UPDATE and SELECT the messages either at the database level (probably preferable as you wouldn't need to send more data than required) or at the application level (simply hiding the messages that the user is not supposed to see).

If you wish to eventually upgrade the application to support more than 2 people chatting, your approach would need to be different. You would need to create another table in between the Users and Messages table. You could name the table something like "MessageStatus", including the following fields:

USER_ID
MESSAGE_ID
VISIBLE (bit)

The table would then identify which users can see which messages by the value of the VISIBLE bit field.


Postgres long running query not blocking table operations We have a 3rd party service that imports data into a local postgres instance, that we then query/copy into our own view-data system. These queries can yield millions of rows, so the import of them to our view-data can take a LONG time (days sometimes). This isn't actually a problem for us, but the 3rd party script occasionally performs table operations (like drop) when it's rebuilding itself. Unfortunately those operations get blocked when we have queries running. Right now, due to the size of the data, we're using a postgres cursor (using the ruby sequel gem to assist with this) so we can fit a fixed chunked of data into memory. It seems that CURSORS though, have a sort of transaction running while iterating through the cursor.

I recognize that the best way to do this is take the results of a query and place them somewhere else as quickly as possible and then scan them to import that data and I know I could do that with temporary tables or other means, however I'm wondering if there's a way that a query can buffer its results (say, to a file on disk) without having to resort to managing temporary tables, such that it no longer locks schema operations?

For instance, I found there's a "single row mode" that streams query results, but I'm not entirely sure how postgres does that under the hood and if that would ultimately have the same effect on concurrently running schema operations.

Can someone enlighten me as to how "streaming" works and if this would solve my problem? Or do I have no other choice but to create a temporary table?

Ruby will close the cursor when it is done iterating over it.  If you already know that that will take a long time, then there is no reason to think it isn't working correctly.  Can you set log_statement ='all' to see when the CLOSE and COMMIT arrive if you want to double check.

Using single row mode won't change the situation.  It will still hold locks until it is done.  It will just change the chunk size fetched from the server from 1000 (or whatever the cursor uses) to 1.

I know that the dblink function does spool its results to disk, so that it can release locks (on the remote end) as quickly as possible. It is the only thing I know of that does that on the server side. Perhaps you can use dblink to loop back to the same database server and run the query through it, then use single row mode to get the results, although I don't know how you get Ruby to make use of single-row-mode.  I've only seen it done from C.  You can probably get Ruby to use a cursor with a dblink query.


Unable to SELECT data that is definitely there This is truly strange.  I'm connecting to an Oracle Docker container (epiclabs/docker-oracle-xe-11g) and performing the following operations:

alter database default tablespace users
create user foo identified by foo
alter user foo quota unlimited on users
create table foo.bar
insert into foo.bar some data 5 times.
Verify that select count(*) from foo.bar returns 5.  So far so good.
Open a second connection to the same database.
select * from foo.bar order by baz asc

This query returns 0 rows.
Things I've ruled out:

The container isn't being reset, because the table foo.bar still exists.  (I tried saying select * from foo.GARBAGE just to make sure it wasn't erroring out on a missing table and swallowing the error.  It errored as expected.  So the table is there.)
The second connection isn't a different user with no permissions.  Verified by select USER from DUAL.  Same username both times, and if it was a permissions issue I'd expect to see an error.

So... what could be the reason why I'm getting no rows returned from a table I've verified is populated with data, on a query with no WHERE clause?

Global temporary table was just a guess, because you had not posted the actual statement for table creation, and already said you used explicit commit. Using the information you gave us, we can only guess.
But here it is, works as expected:
[root@r5 ~]# docker pull epiclabs/docker-oracle-xe-11g
Using default tag: latest
latest: Pulling from epiclabs/docker-oracle-xe-11g
e92ed755c008: Pull complete
b9fd7cb1ff8f: Pull complete
ee690f2d57a1: Pull complete
53e3366ec435: Pull complete
51f7c9099c0d: Pull complete
1b7f09633195: Pull complete
4a8ff9746209: Pull complete
6bae0d820e30: Pull complete
Digest: sha256:

404173b90d09d0619c854dd469c76c3e7256c88a7aac5d47e05f934ec7afae06
Status: Downloaded newer image for epiclabs/docker-oracle-xe-11g:latest
docker.io/epiclabs/docker-oracle-xe-11g:latest
[root@r5 ~]# mkdir /storage/wdred_3tb_raid/mydata
[root@r5 ~]# docker run -d -v /storage/wdred_3tb_raid/mydata:/u01/app/oracle -p 1521:1521 epiclabs/docker-oracle-xe-11g
29f5bcbd1ccbf88d59111d653830098e6a965c602d07adfad5b331c7be8a4b95
[root@r5 ~]#

Test from other machine:
[oracle@o81 ~]$ sqlplus system/oracle@\"r5/xe\"

SQL*Plus: Release 19.0.0.0.0 - Production on Mon May 24 20:31:08 2021
Version 19.11.0.0.0

Copyright (c) 1982, 2020, Oracle.  All rights reserved.

ERROR:
ORA-28002: the password will expire within 7 days

Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

SQL> create user foo identified by foo default tablespace users quota unlimited on users;

User created.

SQL> grant create table to foo;

Grant succeeded.

SQL> create table foo.bar(c1 number);

Table created.

SQL> insert into foo.bar select rownum from dual connect by level <= 5;

5 rows created.

SQL> commit;

Commit complete.

SQL> select count(*) from foo.bar;

  COUNT(*)
----------
         5

SQL> connect system/oracle@r5/xe
ERROR:
ORA-28002: the password will expire within 7 days

Connected.
SQL> select count(*) from foo.bar;

  COUNT(*)
----------
         5

SQL>


How many blocks is my database How can I write a query using Oracle SQL Developer to find how many blocks my database is using? (tables & indexes)

This is what you're looking for, if I've read your question correctly:

SQL> select segment_type, sum(bytes)/1024/1024 as size_in_mb, sum(blocks) as size_in_blocks
  2  from dba_extents
  3  group by segment_type;

SEGMENT_TYPE       SIZE_IN_MB SIZE_IN_BLOCKS
------------------ ---------- --------------
LOBINDEX              43.4375           5560
INDEX PARTITION         61.75           7904
TABLE SUBPARTITION          2            256
TABLE PARTITION       70.6875           9048
NESTED TABLE           1.8125            232
ROLLBACK                .4375             56
LOB PARTITION         21.1875           2712
LOBSEGMENT              331.5          42432
INDEX                338.8125          43368
TABLE                   713.5          91328
CLUSTER                 60.25           7712
TYPE2 UNDO            14.3125           1832

12 rows selected.

SQL>


Fetch large data from Sql Server and process in chunks I am working on an API which would need to process large dataset. The query to select records is complex and takes time to complete. I cannot use the pagination using ROW_NUMBER as not all records returned by query will be marked closed. The same records (potentially all but not necessarily) may get reselected by the query.

I am thinking to use global temporary table as working set. Below is my approach:

API will first create the global temporary table.
API will execute the query and populate the temp table.
API will take data in chunks and process it.
API will drop the table after processing all records.

The API can be scheduled to run at an interval of 5 or 10 minutes. There will not be concurrent instances running, only one instance will run at one time.

Do you see any issues with the usage of global temporary table? Would a permanent table be more appropriate in this scenario?

Using a permanent table (as the commenter suggests) makes it a lot easier to track dependencies. Instead of recreating the table every time, just TRUNCATE it.

When you implement a pattern like this, you have to make sure you are making progress and not selecting the same records over and over again. One way to do this, is to process records in the order of the primary key on the input table. Here is the pseudocode for doing this:

DECLARE @Progress [Type of key]
/* Initial state, start from the bottom of the table */
SELECT  @Progress = MIN(Key) FROM Input

/* Main event loop to do work */
WHILE 1=1 BEGIN
  INSERT INTO WorkTable
  SELECT TOP (@SizeOfBatch) *
  FROM Input WHERE Key > @Progress
  ORDER BY Key ASC

  /* Do work on WorkTable */

  /* Make sure we start where we left off */
  SELECT @Progress = MAX(Key) FROM WorkTable

  TRUNCATE TABLE WorkTable

  /* Wait for 5-10 min */
END

Of course, the above only works if the Key column is ascending. If it isn't you have to maintain a column in the Input table that track whether work has been done or not (and then SELECT TOP the ones where it hasn't)


Sub-Query group-by count(*) not returning 0 Prefix: I am working on an application that is tracking employee training time and I need to get the total number of employees that have reached 75 total hours of training. After researching for some time I have determined I should probably use a join clause but joins are not my strength.

Question: For the following query I am having a problem getting the 'completed' subquery to return a 0 value.

SELECT
    Completed.Total_Complete "Completed Training",
    Incomplete.Total_Incomplete "Incompleted Training"
FROM

    (
     SELECT
        count(*) AS Total_Complete
     FROM
        EMPLOYEE_TRAINING_RECORD
     HAVING SUM(TRAINING_HOURS) >= 75
    )
    Completed,

    (
     SELECT
        count(*) AS Total_Incomplete
     FROM
        EMPLOYEE_TRAINING_RECORD
     HAVING SUM(TRAINING_HOURS) < 75
    )
    Incomplete
;

Thanks for any help.

You don't need a join. First you need to GROUP BY employee, in order to get the hours per employee. Assuming you have an employee_id that identifies employees:

 SELECT
     employee_id,
     SUM(TRAINING_HOURS) AS Hours_Worked
 FROM
     EMPLOYEE_TRAINING_RECORD
 GROUP BY
     employee_id ;

Then you need to do another GROUP BY, in the whole result set, to count the number of employees that have over and under 75 hours:

SELECT
   COUNT(CASE WHEN Hours_Worked >= 75 THEN 1 END) AS Completed_Training,
   COUNT(CASE WHEN Hours_Worked  < 75 THEN 1 END) AS Incomplete_Training
FROM
   (  SELECT
         -- employee_id,      -- we don't need that in the final results
         SUM(TRAINING_HOURS) AS Hours_Worked
      FROM
         EMPLOYEE_TRAINING_RECORD
      GROUP BY
         employee_id
   ) grp                   -- the first group result set
 ;

You could also use window functions and not use subqueries but the code is rather obfuscated:

SELECT DISTINCT
   COUNT(CASE WHEN SUM(TRAINING_HOURS) >= 75 THEN 1 END)
      OVER () AS Completed_Training,
   COUNT(CASE WHEN SUM(TRAINING_HOURS)  < 75 THEN 1 END)
      OVER () AS Incomplete_Training
FROM
   EMPLOYEE_TRAINING_RECORD
GROUP BY
   employee_id ;


Storing phone number in E.164 TLDR; If I store a phone number in MySQL(or any DB with equivalent constraints), would the correct format be E.164 values into a VARCHAR(32) field?

Have been reading up on format/storage of international phone numbers. In the past for various reasons it was suggested to store as a string due to this data being an identifier not an actual number(along with issues like leading 0's). I'm aware that I should not attempt to parse/validate phone numbers via regex, and that libphonenumber developed by Google is generally recommended for handling this and producing E.164 formatted values.

I'm assuming it's safe to say E.164 is the best suited storage format for phone numbers today. As the leading + does not seem required and that leading 0's would not occur in this format? I've seen BIGINT(15) suggested for storing this in MySQL databases. This would require an additional field(string?) to support phone numbers with extension numbers. It's not stated on the Wikipedia article but I've seen many mentions in blog posts that the E.164 format supports extensions by appending ";ext=12345" so would it be more advisable to store into a VARCHAR(32) field? "+19995556789;ext=12345", 32 should be the maximum length with this format, so the length of 32 is appropriate? <+><;ext=>

EDIT: I'm not sure exactly what the extension value should be, according to this it is 11, however the article linked to has been modified since and archived by Apple, there is no clear information on extension length there. Would it be best just to a higher limit such as VARCHAR(50), or go with BIGINT(15) and a additional field for extensions that is nullable?

BIGINT(15) or even BIGINT(18) might not be long enough to store all possible phone numbers. Depending which country and if a PBX is involved 18 digits are not going to be enough. I have seen quite a number of longer ones already. Even so E.164 recommends to not assign phone numbers with more than 15 digits to phone providers world wide, not all of them adhere to these standards.

I am not sure where exactly you are heading with your database, but if you want to store phone numbers I would strongly suggest to use a character based format.
In most applications that really use phone numbers you might not only want to store them but also use them. For using a phone number you might need some transformations to add, change or replace prefixes, or maybe filter for certain prefixes, since international phone numbers do not have all the same length it is much easier and faster on the database to use a character based format like CHAR(32) or VARCHAR(32). If speed of character based filter operations (number pattern, prefixes, areacodes, etc) is important for you the CHAR typ will usually outperform VARCHAR but you will have to clean up trailing spaces.
Internationally there are two common ways to realize direct in dial lines. I would call them "direct dial" and "post connection dial". Any direct dial number can be put into the "regular" number field since they can be dialed in one succession with the carrier assigned number. The other type usually requires a pause before continuing to dial the extension (like in most smaller US legacy PBX systems) these "post dial digits" should go into a separate field, which for better design should be nullable.
I would not use the E.164 extension format that you mentioned, it is so rarely used that in myself working in the industry for well over 10 Years I have never seen it in production.


SQL query for SQL Server backups I found that SQL query for MS SQL server 2008 to display backups.

SELECT      D.name
           ,ISNULL(CONVERT(VARCHAR,backup_start_date),'No backups') AS last_backup_time
           ,D.recovery_model_desc
           ,state_desc,
            CASE WHEN type ='D' THEN 'Full database'
            WHEN type ='I' THEN 'Differential database'
            WHEN type ='L' THEN 'Log'
            WHEN type ='F' THEN 'File or filegroup'
            WHEN type ='G' THEN 'Differential file'
            WHEN type ='P' THEN 'Partial'
            WHEN type ='Q' THEN 'Differential partial'
            ELSE 'Unknown' END AS backup_type
           ,physical_device_name
FROM        sys.databases D
LEFT JOIN  (
    SELECT  database_name,backup_start_date,type,physical_device_name
           ,Row_Number() OVER(PARTITION BY database_name,BS.type
            ORDER BY backup_start_date DESC) AS RowNum
    FROM    msdb..backupset BS
    JOIN    msdb.dbo.backupmediafamily BMF
      ON    BS.media_set_id=BMF.media_set_id
) AS CTE
   ON       D.name = CTE.database_name
  AND       RowNum = 1
ORDER BY    D.name,type;

Is there any other useful information that I can display? Can you propose some additional information that I can query?

This query will return you all the columns of both tables, you can add your desired column in second query of simple joins without CTE

SELECT *
FROM msdb.dbo.backupset s
     INNER JOIN msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id
     ORDER BY backup_start_date DESC, backup_finish_date

Query2:

Select  s.database_name,
          m.physical_device_name,
          CAST(DATEDIFF(second, s.backup_start_date,s.backup_finish_date)
            AS VARCHAR(100)) + ' ' + 'Seconds' TimeTaken,s.backup_start_date,
          CASE s.[type]
           WHEN 'D' THEN 'Full'
           WHEN 'I' THEN 'Differential'
           WHEN 'L' THEN 'Transaction Log'
          END AS BackupType,
          s.server_name,
          s.recovery_model
     FROM msdb.dbo.backupset s
     INNER JOIN msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id
     ORDER BY backup_start_date DESC, backup_finish_date
     GO

Reference Link


How to set a mongodb node to return as the primary of a replication set? There is this question here:

How to force a mongod to become primary in a replica set?

But it was not the answer I was looking for.

and there is the question below, which could be related but it is not the same.

How to force a delayed member to become primary in case of failure in mongodb

and this question which is nearly a duplicate, but deals with version 2.6.11 and mine is version 3.0 and 3.2.
Plus I don't want to use force : 1 unless I need to.

Force a Member to Become Primary in mongodb

I have been struggling to put in practice the majority described in the answer there, maybe with an example or clarification, therefore this question.

I had my replication working fine, 3 nodes, node1 is the primary.

when I run the following command

//-----------------------------
//check the status of the replication
//-----------------------------
rs.status()

I get this result:

{
    "set" : "Krishna",
    "date" : ISODate("2016-04-26T14:59:40.263Z"),
    "myState" : 1,
    "members" : [
        {
            "_id" : 0,
            "name" : "jpb01275:37001",
            "health" : 1,
            "state" : 1,
            "stateStr" : "PRIMARY",
            "uptime" : 1022,
            "optime" : Timestamp(1461678841, 1),
            "optimeDate" : ISODate("2016-04-26T13:54:01Z"),
            "electionTime" : Timestamp(1461682464, 1),
            "electionDate" : ISODate("2016-04-26T14:54:24Z"),
            "configVersion" : 239068,
            "self" : true
        },
        {
            "_id" : 1,
            "name" : "jpb01275:37002",
            "health" : 1,
            "state" : 2,
            "stateStr" : "SECONDARY",
            "uptime" : 1022,
            "optime" : Timestamp(1461678841, 1),
            "optimeDate" : ISODate("2016-04-26T13:54:01Z"),
            "lastHeartbeat" : ISODate("2016-04-26T14:59:40.206Z"),
            "lastHeartbeatRecv" : ISODate("2016-04-26T14:59:40.179Z"),
            "pingMs" : 0,
            "configVersion" : 239068
        },
        {
            "_id" : 2,
            "name" : "jpb01275:37003",
            "health" : 1,
            "state" : 2,
            "stateStr" : "SECONDARY",
            "uptime" : 1022,
            "optime" : Timestamp(1461678841, 1),
            "optimeDate" : ISODate("2016-04-26T13:54:01Z"),
            "lastHeartbeat" : ISODate("2016-04-26T14:59:40.238Z"),
            "lastHeartbeatRecv" : ISODate("2016-04-26T14:59:39.593Z"),
            "pingMs" : 0,
            "configVersion" : 239068
        }
    ],
    "ok" : 1
}

what happened is that I had to stop node1 (jpb01275:37001) and the node2 became the primary.
When I started node1 again, I wanted it to become the primary again.

what I did is

logged into node2 and node3 run the following command on then, one after another:

rs.stepDown()

and the node1 came back as a primary.

I also tried the following on node1, but it did not work for me.

//-----------------------------
// reconfigure the replica set
// because I stopped this server and now it is secondary
// I want it back to primary
//-----------------------------

cfg = rs.conf()

printjson(cfg)

cfg.members = [cfg.members[0] , cfg.members[1] , cfg.members[2]]

rs.reconfig(cfg, {force : true})

what is the correct, safest way to bring back node1 and set it as a primary for this replication?

Typically, you don't. Thinking of your nodes in terms of primary or secondary is the wrong way to approach it. Since standard data bearing nodes should have the same dimensions, it is better to think of them as replica set members, one of which gets elected to primary. Since the drivers are replica set aware and know which the current primary is and you are supposed to give more than one server in your connection string like

mongodb://jpb01275:37001,jpb01275:37002/?replicaSet=Krishna

there is no advantage in setting a fixed replica set primary.

If you really have to do it, you need to do an

rs.stepDown(90,30)

on the current primary and repeat that until your chosen node becomes primary, within 90 seconds (the first numeric value set). For details, see the documentation of rs.stepDown().


Damned if you do, damned if you don't.  The Optimizer has two choices -- filter on language in one table first, or scan on date in the other table first.

If the recent articles are in English, then using the date is better.  If you ask for some language with few articles, the other index is better.

Meanwhile, the Optimizer does not have the smarts to look in two different directions at once to decide which is better.

I would argue that the schema is not 'correct'.  Can't a publisher provide news in multiple languages?  If so, language should be in news, not in publishers.  If you make than change, then INDEX(language, date_added) would become an better index.

I have addressed the general "news" list problem with the addition and maintenance of a separate table.


Save Connections in SSMS? Can I save some sort of session or profile in SSMS (2008 r2) that will open up connections to a saved list of database servers when I open SSMS?

So for example I frequently will open up SSMS and then connect to SQL Servers NY-DB01, NY-DB02 etc... Can I have some sort of shortcut just to open all of these at once?

Using SSMS, register the servers you're interested in and group them in a folder. Then you can connect to them all at once like this:


join two tables, include one row from the parent table for each primary key I've got a simple join over a primary and foreign key... but I need to include a row from the parent table in the results for each primary key as a sort of header row for the children. For instance, I have two tables,

parents
ID | Name
0  | FOO
1  | BAR
2  | ASD

children
ID | Name | ParentID
10 | baz  | 0
11 | zap  | 0
12 | zam  | 1
13 | maz  | 1

I'd like to make a view that returns

ParentID | ChildID | Name | isChild
0        |         | FOO  | False
0        | 10      | baz  | True
0        | 11      | zap  | True
1        |         | BAR  | False
1        | 12      | zam  | True
1        | 13      | maz  | True
2        |         | ASD  | False

The data structure doesn't make much sense but it's populating 3rd party software and that's the only format option. Right now I'm hitting the database with an additional query for each parent. I imagine pulling it into one query would be more efficient, I've only got a few hundred parents

Thanks!

Perhaps I'm missing something, but I don't understand why this is not a simple UNION.

--Demo setup
Declare @Parent table (ID int, Name varchar(30))
insert into @Parent(id, Name) values(0,'FOO'),(1,'BAR'),(2,'ASD')
Declare @Children table (ID int, Name varchar(30),ParentId int)
insert into @Children(id, Name,ParentId) values(10,'bas',0),(11,'zap',0),(12,'zam',1),(13,'maz',1)

--The solution
;with UnionedData as
(
SELECT ID AS ParentId
    ,' ' AS ChildID
    ,NAME
    ,'False' AS isChild
FROM @Parent

UNION ALL

SELECT p.Id
    ,convert(VARCHAR(5), c.id) AS ChildID
    ,c.NAME
    ,'True' AS IsChild
FROM @Parent p
JOIN @Children c
    ON c.ParentId = p.id
)
SELECT *
FROM UnionedData
ORDER BY ParentId
    ,ChildID

| ParentId | ChildID | NAME | isChild |
|----------|---------|------|---------|
| 0        |         | FOO  | False   |
| 0        | 10      | bas  | True    |
| 0        | 11      | zap  | True    |
| 1        |         | BAR  | False   |
| 1        | 12      | zam  | True    |
| 1        | 13      | maz  | True    |
| 2        |         | ASD  | False   |


Error ORA-12541: TNS:no listener Whenever I try to start the listener (I just have only one Oracle database) I get the following:

LSNRCTL> status
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=admin-41f89f35d)(PORT=1521)))
TNS-12541: TNS:no listener
TNS-12560: TNS:protocol adapter error
TNS-00511: No listener
32-bit Windows Error: 61: Unknown error

tnsnames.ora

 # TNSNAMES.ORA Network Configuration File: F:\Oracle\network\admin\tnsnames.ora
    # Generated by Oracle configuration tools.

    PROJECT =
      (DESCRIPTION =
        (ADDRESS_LIST =
          (ADDRESS = (PROTOCOL = TCP)(HOST = admin-xxxxxx)(PORT = 1521))
        )
        (CONNECT_DATA =
          (SERVICE_NAME = PROJECT)
        )
      )

    INST1_HTTP =
      (DESCRIPTION =
        (ADDRESS_LIST =
          (ADDRESS = (PROTOCOL = TCP)(HOST = admin-xxxxxx)(PORT = 1521))
        )
        (CONNECT_DATA =
          (SERVER = SHARED)
          (SERVICE_NAME = MODOSE)
          (PRESENTATION = http://HRService)
        )
      )

    EXTPROC_CONNECTION_DATA =
      (DESCRIPTION =
        (ADDRESS_LIST =
          (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
        )
        (CONNECT_DATA =
          (SID = PLSExtProc)
          (PRESENTATION = RO)
        )
      )

listerner.ora

# LISTENER.ORA Network Configuration File: F:\Oracle\network\admin\listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = admin-xxxxxx)(PORT = 1521))
      )
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = F:\Oracle)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = PROJECT)
      (ORACLE_HOME = F:\Oracle)
      (SID_NAME = PROJECT)
    )
  )

I really don't know what the problem is. My tnsnames.ora and listener.ora files looks fine to me.

As a quick fix, you can just remove the entries (with respect to your database)from both the tnsnames.ora and listerner.ora.
In your scenario, remove this from tnsnames.ora

 PROJECT =
      (DESCRIPTION =
        (ADDRESS_LIST =
          (ADDRESS = (PROTOCOL = TCP)(HOST = admin-xxxxxx)(PORT = 1521))
        )
        (CONNECT_DATA =
          (SERVICE_NAME = PROJECT)
        )
      )

and this from listerner.ora

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = F:\Oracle)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = PROJECT)
      (ORACLE_HOME = F:\Oracle)
      (SID_NAME = PROJECT)
    )
  )

and then you can try to open up the database.
These entries would be created automatically.
And we hope the listener listens to us this time.


PostgreSql 12 - Can't see table details \dt (table) but can see all tables \dt I have a custom schema and one table in it. Following answers about searching through custom schemas I have modified by search_path to include new schema.
I can see now all objects in the new schema, so this one works:
xekata_dev=> \dt
                List of relations
 Schema |     Name     | Type  |      Owner
--------+--------------+-------+-----------------
 xekata | TestNodeBase | table | xekata_user_web
(1 row)

Now when I want to see table details I do:
xekata_dev=> \d TestNodeBase
Did not find any relation named "TestNodeBase".

which shows nothing... :(
I tried different ways to but nothing works:
xekata_dev-> \d xekata.TestNodeBase
Did not find any relation named "xekata.TestNodeBase".

I am logged as xekata_user_web and my search_path is:
xekata_dev=> show search_path;
  search_path
----------------
 xekata, public

What's going on? Why I can't see table details?

You created a case sensitive table name (which is strongly discouraged) so now you need to use double quotes every time you access it:
\d xekata."TestNodeBase"

I highly recommend to stop using those dreaded quoted identifies. Forget that double quotes exists when dealing with SQL.


New fact table or new dimension I have a fact table with 2 dimensions:

cohort_id
date_id

Each record contains the amount of active users from a specific cohort at a particular date - typical structure for the cohort analysis.

Currently, a cohort is defined as a group of users signed up at the same date. I would like to start additionally storing the same data, but with a bit different definition of the cohort - a group of users with the first order at the same date.

I have a conceptual dilemma: is it better to create a new fact table with the same structure or to add a new dimension (cohort_type_id) to the existing fact table?

You haven't given a lot of details, but on the face I'd say make it a new fact table.  It sounds like your cohorts will be entirely distinct, so it wouldn't be meaningful to total or average across both sets.  Your records won't have any overlap in dimensions (except Date, which is almost universal), so they're really distinct things, they just happen to have similar fields.


Sybase SQL - Truncation error occurred. Command has been aborted and find CACHE QUALITY Need help with below code, as it fails with truncation error

Truncation error occurred.
Command has been aborted.

create table monCacheQuality (
        ServerName sysname
        ,CollectionDateTime smalldatetime not null
        ,PhysicalWrites decimal(15, 0) not null
        ,PhysicalReads decimal(15, 0) not null
        ,LogicalReads decimal(15, 0) not null
        ,CacheQuality decimal(15, 0) not null
        ,CacheHitPct decimal(15,4) not null
        )

-- Main code starts here
declare @physical_read1 decimal(15, 0)
    ,@logical_read1 decimal(15, 0)
    ,@physical_write1 decimal(15, 0)
    ,@cache_search1 decimal (15,4)

declare @physical_read2 decimal(15, 0)
    ,@logical_read2 decimal(15, 0)
    ,@physical_write2 decimal(15, 0)
    ,@cache_search2 decimal (15,4)

while (1=1)
begin
    select @physical_write1 = PhysicalWrites
        ,@physical_read1 = PhysicalReads
        ,@logical_read1 = LogicalReads
        ,@cache_search1 = CacheSearches
    from master..monDataCache

    waitfor delay '00:00:20' -- Log every 20 sec

    select @physical_write2 = PhysicalWrites
        ,@physical_read2 = PhysicalReads
        ,@logical_read2 = LogicalReads
        ,@cache_search2 = CacheSearches
    from master..monDataCache

    insert monCacheQuality
    select @@servername as ServerName
        ,getUTCdate()
        ,@physical_write2 - @physical_write1
        ,@physical_read2 - @physical_read1
        ,@logical_read2 - @logical_read1
        ,case
            when @physical_read2 - @physical_read1 = 0
                then - 1
            else (@logical_read2 - @logical_read1) / (@physical_read2 - @physical_read1)
            end as CacheQuality
        ,100-(((@physical_read2-@physical_read1)/(@cache_search2-@cache_search1))*100) as CacheHitPct
end

Just in case if any one wants to set up for CACHE QUALITY for Sybase ASE 15 and up, below script will help:

Changes for the script to work fast without overhead of querying MDA tables and the truncation issue is resolved by switching to int datatype.:

The where clause now makes the script work when Sybase ASE is under heavy loads.
where CacheID = 0
The last change is just a logic change for when physical reads = 0, this way we save the logical reads. then @logical_read2 - @logical_read1

    -- CREATE A TABLE FOR DURATION SIMULATION
    if not exists(select 1 from sysobjects where name = 'TimeControl')
    begin
      create table TimeControl (counter int)
    end
    go

    -- CREATE TABLE FOR SQL PIPE RESULTS
    if not exists(select 1 from sysobjects where name = 'monCacheQuality')
    begin
      create table monCacheQuality (
      ServerName sysname
      ,CollectionDateTime smalldatetime not null
      ,PhysicalWrites decimal(15, 0) not null
      ,PhysicalReads decimal(15, 0) not null
      ,LogicalReads decimal(15, 0) not null
      ,CacheQuality decimal(15, 0) not null
      )
    end
    go
    create table cache_Begin (
  CacheID int not null,
  InstanceID tinyint not null,
  RelaxedReplacement int not null,
  BufferPools int not null,
  CacheSearches int not null,
  PhysicalReads int not null,
  LogicalReads int not null,
  PhysicalWrites int not null,
  Stalls int not null,
  CachePartitions smallint not null,
  CacheName varchar(30) null
)
go
create table cache_End (
  CacheID int not null,
  InstanceID tinyint not null,
  RelaxedReplacement int not null,
  BufferPools int not null,
  CacheSearches int not null,
  PhysicalReads int not null,
  LogicalReads int not null,
  PhysicalWrites int not null,
  Stalls int not null,
  CachePartitions smallint not null,
  CacheName varchar(30) null
)
go

use tempdb --- change this to be other than the db being monitored !!
go

declare @physical_read1 int
      ,@logical_read1 int
      ,@physical_write1 int
declare @physical_read2 int
      ,@logical_read2 int
      ,@physical_write2 int

while (select counter from TimeControl) =1
begin
      select @physical_write1 = PhysicalWrites
            ,@physical_read1 = PhysicalReads
            ,@logical_read1 = LogicalReads
      from master..monDataCache
      where CacheID = 0

      waitfor delay '00:01:00'

      select @physical_write2 = PhysicalWrites
            ,@physical_read2 = PhysicalReads
            ,@logical_read2 = LogicalReads
      from master..monDataCache
      where CacheID = 0

      insert monCacheQuality
      select @@servername as ServerName
            ,getUTCdate()
            ,@physical_write2 - @physical_write1
            ,@physical_read2 - @physical_read1
            ,@logical_read2 - @logical_read1
            ,case
                  when @physical_read2 - @physical_read1 = 0
                        then @logical_read2 - @logical_read1
                  else (@logical_read2 - @logical_read1) / (@physical_read2 - @physical_read1)
                  end as CacheQuality
end

-- Report the data that is collected.........
select *
from monCacheQuality

---- reset it back ... (in another Query window)
update TimeControl
set counter = 0
go

--- cleanup ... start and end cache table for next reuse
drop table cache_Begin
go

drop table cache_End
go


Designing SQL database with two types of users So I'm trying to design a Postgresql database with two types of users.

Customers and Brands

A Customer cannot be a brand and a brand cannot be a customer.

Is this the right approach? I feel I may have some challenges when querying data.

If that is a good model or not depends mostly on how you use the tables:

If you expect that many queries or foreign key constraints will refer to “brand and/or customer”, your proposed model may be the way to go.
If the tables are mostly used separately, using only two tables might be better.

If your concern is mostly that the tables should share certain columns, you could also use PostgreSQL table inheritance:

CREATE TABLE users (
   id bigint PRIMARY KEY,
   username text NOT NULL,
   email text,
   password text NOT NULL
);

CREATE TABLE brands (
   storename text NOT NULL,
   ...
) INHERITS (users);

CREATE TABLE customers (
   stripeuserid text NOT NULL,
   ...
) INHERITS (users);

Then brands and customers are separate tables, but they are guaranteed to have the same columns as users (and others in addition).

Then you can query users to see rows from both customers and brands, but you cannot use users as the destination of foreign keys to either customers or brands.


How to force Redshift/Postgres to aggregate nth_value? I'm trying to retrieve the 2nd, 3rd, 4th order/transaction from an orders table using a query that looks like this:

select
    customer_email,
    nth_value(created_at, 1) over (partition by customer_email),
    nth_value(created_at, 2) over (partition by customer_email),
    nth_value(created_at, 3) over (partition by customer_email),
    nth_value(created_at, 4) over (partition by customer_email)
from
    fact_orders
where
group by
    customer_email,
    created_at
limit 100

My goal is to get the 1st, 2nd, 3rd, and 4th order for each customer.
I'm forced to do a group by on customer_email and created_at, which results in a row for each order and date by a customer.

The nth_value yields the correct results, but I would prefer a result set that looks as so:

customer_email, first_order, second_order, third_order, fourth_order

With only 1 record per customer.

How can I achieve this?

I imagine something like this should work:

select
    customer_email,
    nth_value(created_at, 1) over (partition by customer_email
                                   order by created_at),
    nth_value(created_at, 2) over (partition by customer_email
                                   order by created_at),
    nth_value(created_at, 3) over (partition by customer_email
                                   order by created_at),
    nth_value(created_at, 4) over (partition by customer_email
                                   order by created_at)
from fact_orders
where ...
limit 100


Oracle tnsping.exe - where to get/download it I have recently downloaded latest version of Oracle SQL Developer (4.0.0.13), and my idea was that tnsping.exe should go along with it - but this is not the case. Also I was surprised that SQL Developer is provided as archive of files without any kind of installer.
Can somebody advice where to download tnsping.exe?

Just install the Oracle client, the tnsping.exe tool comes with it ! it should be in the bin folder.


Is it possible to display this restriction in an ER diagram? I have a question about modeling a particular kind of restriction, but I don't know the technical term for it, so I will illustrate it with an example.

I wish to make an ER diagram for a hotel room booking. Is it possible in an ER diagram to show that no two bookings of the same room, in the same hotel, in an overlapping time interval can occur? In my initial diagram, I have three entity types: Hotel, Room and Booking. But I don't know if I should use a ternary relationship or just three binary ones, or even if I should have three entity types. Any help would be greatly appreciated.

Edit: One booking can have many rooms in the same hotel. Also, I need to be able to book the same room, in the same hotel on distinct bookings, as long as the start date and end dates of booking X doesn't overlap with the start date and end date of booking Y

My problem is that (if I understand correctly) there is no way to tell from this diagram that two distinct bookings can't book the same room, in the same hotel in an overlapping time interval. Can I somehow modify this picture to show that constraint?

The short answer is "no."

Entity relationship diagrams can be used to show constraints based on the relationships between entities.  However, the particular constraint you want to show (that bookings do not overlap in time) is a constraint between rows in the same table, not between two or more tables.

The only single table constraint that can be illustrated in most common entity relationship diagrams is uniqueness, since ER diagrams can illustrate a primary key.


Insert Into Select multiple columns mysql This query doesn't work, but individual queries inserting and selecting only one column do work:

INSERT INTO subdata (reggeduser,completed)
SELECT COUNT(u.email) FROM user AS u,COUNT(a.email) FROM application AS a

My end goal is this:

CREATE EVENT
ON SCHEDULE EVERY 1 DAY
STARTS '2016-12-12 00:00:00'
DO
INSERT INTO subdata (reggeduser,completed)
SELECT COUNT(u.email) FROM user AS u,COUNT(a.email) FROM application AS a

MySQL version used is 5.6.19-67.0-log.

The error thrown is a simple #1064 Invalid Syntax.

Here is a way, using variables:

SET @count1=0;
SET @count2=0;
SELECT COUNT(email) INTO @count1 FROM user;
SELECT COUNT(email) INTO @count2 FROM application;
INSERT INTO subdata (reggeduser,completed) VALUES (@count1, @count2);


MySQL root login email notification Is there a script that I can run to get an email when someone else other than me logs in as root?

I'm new to MySQL. These are my contents of my.cnf:

[mysqld]
*Remove leading # and set to the amount of RAM for the most important data
cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.

*innodb_buffer_pool_size = 128M

*Remove leading # to turn on a very important data integrity option: logging

*changes to the binary log between backups.

*log_bin

*Remove leading # to set options mainly useful for reporting servers.

*The server defaults are faster for transactions and fast SELECTs.

*Adjust sizes as needed, experiment to find the optimal values.

*join_buffer_size = 128M

*sort_buffer_size = 2M

*read_rnd_buffer_size = 2M

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

*Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

log-error=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

port=1433

Please suggest a way to get an email alert if someone logs in as root into MySQL.

There are multiple ways to go with this but using logs is the simplest way as there is no need of creating table inside mysql to log the root or user login with timestamp as mentioned in : http://www.fromdual.com/sites/default/files/logon_trigger_wp.pdf

I installed logtail and logcheck using yum, then wrote the following script and then created a cron job which runs this script:

#!/bin/sh

if [ -f foundadmin.txt ]
then
    rm foundadmin.txt
fi
if [ -f send.txt ]
then
    rm send.txt
fi

/usr/sbin/logtail2 -f /var/log/mysqld.log -o mysqld.log.offset | grep "root@localhost on  using Socket" > foundadmin.txt

if [ -s foundadmin.txt ]
then
    echo "Subject: MYSQLDB Admin Login on $(hostname)" > send.txt
    cat foundadmin.txt >> send.txt
    /bin/mailx -s "Subject: MySQLDB Admin Login on $(hostname)" myemail@com  < send.txt
    rm foundadmin.txt
    rm send.txt
fi
exit 0

#

this is the cron job to make it run every minute:

This is run a cron job in a time interval and mail the admins to notify

cd /opt/mysql-check ; ./mysql-notify-script.sh


How does autocommit=off affects bulk inserts performance in mysql using innodb? I know that turning off autocommit can improve bulk insert performance a lot according to:

Is it better to use AUTOCOMMIT = 0

And I have made experiments to confirm that conclusion. But what I want to know is, why turning off autocommit can improve bulk inserts? And what happens internally in inno db when a transaction is commited?

If autocommit is turned off, you can always do a ROLLBACK to get back to the state the database was in before you started doing something.
COMMIT makes sure all changes to the database are written permanently, doing this for every INSERT takes more time than doing this for a  lot of INSERTs, because not only the records needs to be inserted, also the indexes needs to be updated. Doing this last piece for a lot of records is more efficient than doing this for 1 record at a time.


Storing companies' working hours in Postgres I have table with millions of companies data (name, address, phones etc) in my Postgres database. I want to store working hours (example: mon-fri: 9:00-12:00, 13:00-18:00; sat: 9:00-13:00). How can I store it to be able to find all companies opened in requested time?

I would store the opening hours per weekday/per company. For storing the actual hours, a timerange seems suitable.

create table opening_hours
(
  company_id int not null references company,
  day_of_week int not null, -- 1 = Monday
  hours timerange not null
);

I would use 1 for Monday because that's the ISO definition and most of Postgres' date functions work like that, so you don't need to do conversion from and to a different base number for Monday.

If there is a noon break, it would mean that the day_of_week gets two entries, e.g.

insert into opening_hours (company_id, day_of_week, hours)
values
(1, 1, timerange('09:00', '12:00')),
(1, 1, timerange('13:00', '18:00'));

To find the companies that are opened at a specific day and time you can use something like this:

select *
from company c
where exists (select *
              from opening_hours oh
              where oh.company_id = c.id
                and oh.hours @> time '17:35'
                and oh.day_of_week = 2)

An exclusion constraint can be used to avoid overlapping opening hours for the same day and company.


Temp DB is growing continuously in SQL server I have used temp tables and drop the same in the every stored procedure in my database. When I try to check my tempdb data using Select * from tempdb.sys.tables, I am getting many tables with names like #A002FF4B,#A08A53F2...etc (name with some hex characters).

These tables are continuously getting added in my tempdb and it is getting full. So sometimes my application stops due to it is not able to access SQL server once tempdb is full.

How can I resolve this issue?

If you do a quick search on this same site, you will find lot of similar questions. Basically, some process is taking to long and is making your temdb grow until you run out of space. According to Brent Ozar "Tempdb is like a public toilette", everyone (all kind of processes) use it and usually to do filthy things.

What you should do is locate what is making your tempdb grow, search that root cause and solve it. If you don't do that, you will still deal with continues issues like this one. You can, as a temporal solution, run a shrink on the tempdb to free some space. Please please avoid having an automated job to do shrinks.

You have several options to find the cause, running a SQL Server Profiler trace to track down Tempdb activity. Also you can use sp_whoisactive to log activity on the server while whatever causes the issue is running, so you can check that log afterward and see possible queries or processes that cause it. Kendra Little has a full blog entry on how to do this.

For some specific code samples, look here and here. Those are just some samples, as commented on first line, this a common issue, so you should find tons of useful answers here on Stackexchange site.


Combining data from 3 tables depending values stored in 1 table I'm trying to make a SQL query to return values potentially from 3 tables. This will combine all booking information for a booking which can span over multiple days with multiple guests and a single member.

I have a bookings, guests and members table:

Bookings:
id, day, booking_id, guest_id, member_id, start_date, end_date

Guests:
id, first_name, last_name

Members:
id, first_name, last_name

The Bookings table contains a new booking row per day booked per person. Each of these bookings can relate to another by booking_id (These are group bookings).
A booking must have a single member related to it but can have multiple guests.
However a single booking does not have a column showing it is a group booking other than if the booking_id is else where in the Bookings table.

From what I understand is I want to self-join multiple times (can potentially have ~30 of the same booking_id's) on booking_id

Then left-join on the Members where Bookings.member_id = Members.id WHERE Bookings.member_id != 0

And left-join on the Guests where Bookings.guest_id = Guests.id WHERE Bookings.guest_id != 0

Sorry if this is confusing and thank you for reading it

For example:

Bookings:
+----+-----+------------+-----------+----------+------------+----------+
| id | day | booking_id | member_id | guest_id | start_date | end_date |
+----+-----+------------+-----------+----------+------------+----------+
| 1  | 01  | 476        | 91        | 0        | 01-06-15   | 01-06-15 |
| 2  | 01  | 476        | 0         | 64       | 01-06-15   | 01-06-15 |
| 3  | 01  | 519        | 32        | 0        | 01-06-15   | 01-06-15 |
| 4  | 02  | 634        | 84        | 0        | 02-06-15   | 04-06-15 |
| 5  | 02  | 634        | 0         | 29       | 02-06-15   | 04-06-15 |
| 6  | 02  | 634        | 0         | 15       | 02-06-15   | 04-06-15 |
| 7  | 03  | 634        | 84        | 0        | 02-06-15   | 04-06-15 |
| 8  | 03  | 634        | 0         | 29       | 02-06-15   | 04-06-15 |
| 9  | 03  | 634        | 0         | 15       | 02-06-15   | 04-06-15 |
| 10 | 04  | 634        | 84        | 0        | 02-06-15   | 04-06-15 |
| 11 | 04  | 634        | 0         | 29       | 02-06-15   | 04-06-15 |
| 12 | 04  | 634        | 0         | 15       | 02-06-15   | 04-06-15 |
+----+-----+------------+-----------+----------+------------+----------+

Members:
+----+------------+-----------+
| id | first_name | last_name |
+----+------------+-----------+
| 32 | Travis     | Pilsner   |
| 84 | George     | Warrok    |
| 91 | Rob        | Williams  |
+----+------------+-----------+

Guests:
+----+------------+-----------+
| id | first_name | last_name |
+----+------------+-----------+
| 15 | Sarah      | Phillips  |
| 29 | Katie      | Hills     |
| 64 | Laura      | Time      |
+----+------------+-----------+

To Produce:

+-----+------------+-----------+----------+------------+----------+--------------------------------------------+
| day | booking_id | member_id | guest_id | start_date | end_date | names                                      |
+-----+------------+-----------+----------+------------+----------+--------------------------------------------+
| 01  | 476        | 91        | 64       | 01-06-15   | 01-06-15 | Rob Williams, Laura Time                   |
| 01  | 519        | 32        | 0        | 01-06-15   | 01-06-15 | Travis Pilsner                             |
| 02  | 634        | 84        | 29, 15   | 02-06-15   | 04-06-15 | Geroge Warrok, Katie Hills, Sarah Phillips |
+-----+------------+-----------+----------+------------+----------+--------------------------------------------+

I splitted the problem into two basic SELECTs, one for members and the another one for guests and I join both later.
MySql's GROUP_CONCAT aggregate funtion is very useful here too.

http://sqlfiddle.com/#!9/58786/1

SELECT
TT1.day as day
,TT1.booking_id as booking_id
,TT1.member_id as member_id
,CASE
  WHEN TT2.guest_ids IS NULL
  THEN 0
  ELSE TT2.guest_ids
  END as guest_id
,TT1.start_date as start_date
,TT1.end_date as end_date
,CASE
  WHEN TT2.guest_ids IS NULL
  THEN TT1.member_name
  ELSE CONCAT(TT1.member_name, ', ', TT2.guests_name)
  END as names
FROM
(
SELECT
Min(day) as day
, booking_id
, member_id
, Members.id
, CONCAT(first_name,' ',last_name) as member_name
, start_date
, end_date
FROM Bookings
INNER JOIN Members
ON Bookings.member_id = Members.id
WHERE member_id <> 0
GROUP BY booking_id, member_id, guest_id, start_date, end_date
) AS TT1
LEFT JOIN
(
SELECT
booking_id
, GROUP_CONCAT(guest_id SEPARATOR ', ') as guest_ids
, GROUP_CONCAT(CONCAT(first_name, ' ', last_name) SEPARATOR ', ') as guests_name
FROM
(
SELECT
booking_id
, guest_id
FROM Bookings
WHERE guest_id <> 0
GROUP BY booking_id, guest_id
) AS T1
INNER JOIN Guests
ON T1.guest_id = Guests.id
GROUP BY booking_id
) AS TT2
ON TT1.booking_id = TT2.booking_id
ORDER BY TT1.day, TT1.booking_id


Speed-- Restoring from Azure Blob to local database We're being encouraged to move our database backups to Azure blobs, which we access through the Microsoft Azure Storage Explorer. We're running SQL Server 2017.

Our backup times are fine-- 20 minutes for a ~15GB database that is compressed, encrypted, and checksum'd.

However, we're getting killed on restore times. That 15GB database takes over 2 hours to restore!

I can download the full backup file from Azure to my local PC in about a minute or two, so it seems like network performance is fine.

It only takes about 12 minutes to restore the backup locally (same file, but on another VM rather than in Azure).

Any ideas or recommendations about what could cause restoring a database from Azure (via URL) to take hours and hours, and what we can do about it? Glad to provide more information if it'll help. I appreciate all of your help!

You can also turn on Trace Flag 3051 when doing the restore to
  generate a detailed log. This log file is placed in the log directory,
  and is named using the format:
  BackupToUrl---action-.log. The log file
  includes information about each round trip to Azure Storage including
  timing that can be helpful in diagnosing the issue.

https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/restoring-from-backups-stored-in-microsoft-azure?view=sql-server-ver15#optimizing-restores

And you can also use AzCopy to download the backup to a local disk first.

Or overwrite a daily backup on a local disk in addition to the Azure backups.


Retrieve list of matched words in PostgreSQL I'm new to PostgreSQL and really more than your more basic queries/inserts.

I've created a TSVector column to my table that I'm searching and have set the column to look at the specific column in the table.

Now what I'm trying to do is find out what words matched in a query.

If I had the query

SELECT ts_headline('the big red ball' , 'big & ball');

It would obviously return:

  "the big red ball"

Is there a way I can get it to return a list instead so I just see big and red in seperate rows?

What about using the regexp_split_to_table function?

SELECT regexp_split_to_table(ts_headline('the big red ball'::text , 'big & ball'),' ');
 regexp_split_to_table
-----------------------
 the
 big
 red
 ball
(4 rows)


How to retrieve all data from table if column value providing null value? How to write a query:

If country = '    ' I need to display all data in table.
If country='algeria', it will display the particular data.
I need both queries in a single query.

I am getting result, but without providing variable how to write query...

declare @country1 varchar(30)
set @country1 = 'asd'

SELECT country_id, country
FROM   country
WHERE
(
    (@country1  = '')
    OR
    (@country1 != '' AND country.country= @country1)
);

with out  taking any  extra variable i need to  write query.
there@country1

This should do the job.

DECLARE @country1 varchar(30)
SET @country1 = 'asd'

SELECT country_id, country
FROM   country
WHERE country.country LIKE IF(@country = '', '%', @country);

The IF statement works like this : IF(condition, ifverifiedcondition, else);


Query that checks for subset to mysql query SELECT PA.PATIENT_ID, PA.FIRSTNAME, PA.LASTNAME, PH.*
FROM pharmacies as PH, patients as PA
WHERE
(SELECT D.DRUG_ID
FROM drugs as D, sells as S
WHERE D.DRUG_ID = S.DRUG_ID AND PH.PHARMACY_ID = S.PHARMACY_ID and PA.TOWN = PH.TOWN )
contains
(SELECT D.DRUG_ID
FROM prescriptions as PR, drugs as D
WHERE PR.PATIENT_ID = PA.PATIENT_ID AND PR.DRUG_ID = D.DRUG_ID)

I have the above query and I would like to write it for Mysql. I want to find for all the patients, the pharmacies that are in the same town, and contain all their drugs.

That is a challenge!

By comparing now many drugs a patient needs to how many a pharmacy can provide of what he needs, I think this gives a list of patient:pharmacy matches.

SELECT  have.patient_id,  have.pharmacy_id
    FROM ( SELECT s.pharmacy_id, pr.patient_id, COUNT(*) AS ct
            FROM sells AS s
            JOIN prescriptions AS pr  USING(drug_id)
            GROUP BY  s.pharmacy_id, pr.patient_id ) AS have
    JOIN ( SELECT patient_id, COUNT(*) AS ct
            FROM prescriptions
            GROUP BY patient_id ) AS need
        ON need.patient_id = have.patient_id
    WHERE need.ct = have.ct

That can then be used as a derived table to get the other specifics:

SELECT PA.PATIENT_ID, PA.FIRSTNAME, PA.LASTNAME, PH.*
    FROM ( the-above-query ) AS x
    JOIN pharmacies as PH  USING(pharmacy_id)
    JOIN patients as PA    USING(patient_id)

Another approach might involve using LEFT JOIN to discover which pharmacies cannot supply a needed drug for a given patient, then use another LEFT JOIN to discover which other pharmacies there are for that patient.


Frequent MySQL table corruptions and instability The database I'm working on is having frequent issues.

Almost every day there is a problem.

Things like tables crashing or becoming corrupt.

So far, they've been repairable in one way or another, however I'm curious what the cause is exactly.

Bad hardware is always a possibility, but because I'm using Amazon's Web Services, I'm doubtful that is the case.

I've been monitoring the CPU usage on the machine and I've noticed that it frequently maxes out. On average, it hovers around 40%, but has frequent spikes.

Would constant high usage be a cause for my issues? If so, what is a good way of handling the high usage apart from optimizing queries; I'm currently working on that and the high usage is likely to stay.  Would replication be beneficial?

EDIT -- I went ahead and bit the bullet and decided to upgrade the resources. So far, I've been able to run all my queries without any issues. It looks like I was just working it too hard.

High CPU usually means inefficient queries.  Let's see the slow queries, together with SHOW CREATE TABLE and EXPLAIN.

The solution may be as simple as adding an index.  Or perhaps reformulating the query.  A common inefficiency is using a subquery instead of a JOIN.

You can't tune your way out of CPU troubles.  Throwing hardware at it is, at best, a temporary solution.


Is it possible to export postgres database without views? I check pg_dump doc, and there seems to be an option that allows to export only views. But is there a way to dump a database without all the views?

There's no option that excludes all views. But there are workarounds.

If you isolate all your views in a single schema (as in create schema views_only...), you can use

either the -n or --schema=public argument to select only the public schema (might not do what you want),
or the -N or --exclude-schema=views_only.

If you follow a strict(ish) naming convention, you can use

either the -t or --table=something to select only the tables,
or the -T or --exclude-table=something to exclude the views.

Be careful with this. In the relational model, base tables and views share a namespace for a reason--they're supposed to be interchangable. SQL generally honors that part of the relational model. Some people use prefixes for database objects, so "we know whether an object is a view, a table, or a stored procedure that returns a table". If you build views with names like "view_wibble", and later decide to replace that view with a table, you have the distasteful options of naming your table "view_wibble", or rewriting application code, stored procedures, other views, etc to use "tbl_wibble" instead of "view_wibble".

But you can use these command-line options more than once. So you can name each of the views you want to exclude. The is probably the best idea.

pg_dump ... --exclude-table=view1 --exclude-table=view2 --exclude-table=view3 ...


Limiting the CPU/Memory utilization for a Query in SQL server 2008R2 We have an AD-HOC process from the application team which is fetching data from one of our heavily used OLTP databases in SQLserver to migrate the data into MongoDB.

During the performance measure we can see that part of their run while fetching data from this sql server there is a spike in CPU for almost an hour, spiking to almost 60-80%.

Therefore to reduce this impact we are thinking to use Resource Governor to limit the CPU/Memory resources. I have never used RG, but reading the blogs seems it can be ideal.

Please suggest if this can be achieved or any better suggestions per your experience.

The activity above is monthly once fetching around 80 millions of data on 5 TB database in SQL server 2008R2

The short answer is 'Yes'.
You can use RG to limit the CPU consumption of that process.
Note that RG is only available in Enterprise and Data Center editions of SQL Server 2008R2. Use the Microsoft documentation excellent walk-through to learn about using RG: Resource Governor How-to Topics

Longer answer - RG does not address the root cause, it's a band aid to help with the symptoms, and make the disease less disturbing to the other users.
If I were in your place, before resorting to RG solutions, I would invest some time trying to optimize the process itself.
Although 'accessing 80 million rows' doesn't mean much, and just accessing data rows should not consume a lot of CPU.
My experience has proven again and again that many processes that take hours, can be reduced to minutes (and in some cases, to seconds), and be made to consume orders of magnitude less resources than the original.
Optimization effort should include not only the technicalities, such as proper indexing, schema tweaks, query syntax, and plan investigation, but also higher level evaluation of the process architecture and flow, does it do a full refresh or incremental update? how many times do you access the same tables? are there many temp tables involved? too many steps? Perhaps some redundant work being done?
If you provide some more details about the process, and why it takes so much CPU, people here will be happy to help you.

Of course, there is no guarantee that you will succeed, and eventually you may end up resorting to use RG, but IMHO the effort is very worth while.


Creating index on foreign key column? I have below table structure where one department can have many employees.

Department
    id
    dept_name
    ...

Employee
   id
   emp_name
   dept_id(foreign key to department table)
   ....

Consider  I have 30 departments and each department has 10k records.

Now I fire below query

select * from department dept, Employee emp where emp.dept_id=dept.id and dept.id=10;

My question is there any benefit on creating index on foreign key column dept_id of table Employee ?

Per my understanding, there should be benefit of creating index on column dept_id as without index DBMS has to scan all records of Employee
table to find dept_id=10. If there is index then it can quickly find the range of record having dept_id=10 with the help of non clustered index.

It is highly recommended to create an index on Employee.dept_id.

It should make a significant improvement for such SELECT statements. Even more important the index becomes on updates. You may lock entire table (forcing other sessions to wait) until you finish your transaction.

See more information here: https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:292016138754


Scheduling backup jobs for an Availability Group In a MS SQL Server 2014 AlwaysOn AG setup, I would like to schedule backup jobs for a given availability group. The ultimate goal is to have the regular backups run on the synchronized secondary and, most importantly, not bound to the availability of a particular secondary node.

The approach I have seen so far was to use the SQL Server scheduler, set up identical jobs on all running instances and introduce conditional logic into the scheduler steps in order to determine if the role is primary or secondary. This won't work for my use case for several reasons:

I want to run the scheduled action on a secondary just once, but I have multiple secondaries
I want to ensure that it is ultimately run, regardless if there are any secondaries left at all - if no secondaries are left, it should run on the primary

The backup job consists of running BACKUP LOG [...] WITH COMPRESSION, NOINIT, NOFORMAT every 15 minutes.

Right now, I am thinking about creating a clustered scheduled task bound to the respective AG's failover cluster role but wondered if there is a more easy and streamlined way to implement this.

In a MS SQL Server 2014 AlwaysOn AG setup, I would like to schedule
  backup jobs for a given availability group. The ultimate goal is to
  have the regular backups run on the synchronized secondary and, most
  importantly, not bound to the availability of a particular secondary
  node.

This can be accomplished without issue. Just keep in mind that your systems should be spec'd to be able to take the full load of the synchronization mechanics for AlwaysOn Availability Groups, plus the redo needed, plus the backups. The last thing that you want is to overwhelm your IO system and cause outages or a large redo queue. This is assuming it's not actively being used as a readable secondary - if it is, you'll want to factor that in and watch for blocked redo threads.

  1.I want to run the scheduled action on a secondary just once, but I have multiple secondaries
  2.I want to ensure that it is ultimately run, regardless if there are any secondaries left at all - if no secondaries are left, it should run on the primary

There is an Availability Group setting and a system function that can be used in order to accomplish this within your expected guidelines.

The first is the setting, per AG, called AUTOMATED_BACKUP_PREFERENCE which has four different options. The one you're describing is called SECONDARY which will prefer any secondary node over the primary node. The secondary node chosen is by the BACKUP_PRIORITY which is set per replica ranging from 0-100, where the higher the number the more weight it has. If there is a tie in weights for secondary replicas, the replica that is sorted first given the system collation will be chosen. If all secondary replicas should fail, the primary replica will be chosen.

The second part of the equation is the system function used to check if the replica that is running the job is the preferred replica based on the values in the previous paragraph. This system function is called sys.fn_hadr_backup_is_preferred_replica(). Given a database name (any database in the AG) a value of 0 will be returned if it is not the preferred replica and a value of 1 if it is preferred.

When creating the agent job to accomplish this, you'll want to wrap the backup logic in an IF conditional to check for the preferred replica. That's it. Put the identical agent job on all replicas.

Please be sure to test that this truly is what you want and expect.


how to install PL/SQL Developer on centos Can we install and run PL/SQL Developer on CentOS 7 server? I couldn't find installation documents anywhere. If the installation is possible can someone guide me on this.

If you were referring to PL/SQL Developer it is only supported on windows

  PL/SQL Developer will run on Windows XP, 2003, Vista, Windows 7,
  Windows 8, Windows 8.1 and Windows 10.

If you were talking about Oracle SQL developer it is supported on

  Oracle SQL Developer 4.1 is available for Windows 7,8,10 and Windows
  Server 2008/2012, Linux or Mac OS X

You need to install Java version 1.8 and the SQL Developer executable. From here

Ensure you have a JDK installed, if not, download here
Download SQL Developer
rpm -Uhv sqldeveloper--1.noarch.rpm
cd sqldeveloper - sqldeveloper


Can’t connect to local PostgresSQL server I have the Postres app installed an running on my Mac. And it has worked beautifully. I don’t know what changed, but now I cannot connect to it from Rails, PG Commander, PG Admin or the command psql -h localhost. However, I can connect with just psql. I get the following error in the log as soon as the connection times out:

LOG:  incomplete startup packet

Rails database.yml

development:
  adapter: postgresql
  database: my_db
  host: localhost

PG Commander settings:

pg_hba.conf:

# "local" is for Unix domain socket connections only
local   all             all                                     trust
# IPv4 local connections:
host    all             all             127.0.0.1/32            trust
# IPv6 local connections:
host    all             all             ::1/128                 trust

postgresql.conf: https://gist.github.com/davbeck/49a23a48fa161b3e06fc#file-gistfile1-txt

Since your local psql session just works, your database is OK. Your other sessions try to connect using network. Things to check here are the listen_addresses and port parameters to be found in your postgres.conf They must match with what the clients specify.
Next to that, your firewalls must allow the access. For your local firewall check your settings Security and Privacy settings. There is a good chance that there is a question asking to allow your postmaster to connect to the network.


Why would I use the MySQL "system" command? What can I do with the MySQL client's system command?  Why does it exist?

The most productive use I've seen is to look around the file system (e.g., to remember the file name you want to SOURCE or LOAD FILE).

mysql> system ls /tmp
backup.sql
mysql> source /tmp/backup.sql
Query OK, 1 row affected (0.02 sec)

mysql>

As near as I can tell, you can't pass a query result to the STDIN of a command, you can't pass STDOUT of a command into a MySQL query.. it just doesn't seem widely useful.

There is actually a far more useful application for the SYSTEM command than simply executing ls to see the current directory contents:

As mentioned in the docs (and elaborated on by a comment), this is way to make backups:

echo "FLUSH TABLES WITH READ LOCK; SYSTEM snapshot.sh; UNLOCK TABLES;" | mysql

snapshot.sh contains code to make an atomic snapshot of the mysql tables, using LVM, ZFS or btrfs capabilites.
You can't split this up into multiple commands from the shell script, because as soon as the mysql session is closed, the table lock is released.


postgresql - granting schema to users I have (2) postgresql databases. I basically used the same pg_dump export file to create the (2) db's on different machines. The DDL is the same, however, I am getting the no relations message on one of them. The search_path's are set correctly.  I believe I'm missing a grant for (2) of my users to see this schema. The settings are correct on Host2. I just don't which grant to use.

Message:

mra_prd_99999=> \dt
No relations found.

HOST1:

mra_prd_99999=> \z
                                                   Access privileges
      Schema       |           Name            | Type  |          Access privileges          | Column access privileges
-------------------+---------------------------+-------+-------------------------------------+--------------------------
 mra_dev_schm_99999 | abc_bounce_category       | table | mra_prd_99999=arwdDxt/mra_prd_99999 |

HOST2:

mra_prd_99999=> \z
                                                        Access privileges
      Schema       |           Name            | Type  |              Access privileges               | Column access privileges
-------------------+---------------------------+-------+----------------------------------------------+--------------------------
 mra_dev_schm_99999 | abc_bounce_category       | table | mra_prd_99999=arwdDxt/mra_prd_99999         +|
                   |                           |       | mra_prd_99999_user1=arwdDxt/mra_prd_99999+|
                   |                           |       | mra_prd_99999_user2=r/mra_prd_99999        |

My rolenames and settings are identical on both (1,2):

 mra_prd_99999          | Create DB                                      | {}
 mra_prd_99999_user1    |                                                | {}
 mra_prd_99999_user2    |                                                | {}

All the users have the search_path set to the same schema:

mra_prd_99999=> show search_path;
    search_path
-------------------
 mra_dev_schm_99999
(1 row)

mra_prd_99999=> \dn
          List of schemas
       Name        |     Owner
-------------------+---------------
 public            | mra
 mra_dev_schm_99999 | mra_prd_99999
(2 rows)

What grants do I need to run in order for HOST1's users to have the same access privileges than HOST2?

You get the message

  No relations found.

because the user does not have the USAGE privilege on the schema (neither granted directly, nor through the public pseudorole).  You can check this by comparing \dn+ mra_dev_schm_99999 on the two databases.

This is what the documentation says about this:

  For schemas, allows access to objects contained in the specified
  schema (assuming that the objects' own privilege requirements are also
  met). Essentially this allows the grantee to "look up" objects within
  the schema. Without this permission, it is still possible to see the
  object names, e.g. by querying the system tables. Also, after revoking
  this permission, existing backends might have statements that have
  previously performed this lookup, so this is not a completely secure
  way to prevent object access.

Furthermore, it appears that the two mra_prd_99999_users don't have the table level privileges on abc_bounce_category - you have to do

GRANT ALL ON TABLE mra_dev_schm_99999.abc_bounce_category
      TO mra_prd_99999_user1;
GRANT SELECT ON TABLE mra_dev_schm_99999.abc_bounce_category
      TO mra_prd_99999_user2;

If you have more tables with the same problem, do

GRANT ALL ON ALL TABLES IN SCHEMA mra_dev_schm_99999
      TO mra_prd_99999_user1;

-- then for future tables:
ALTER DEFAULT PRIVILEGES FOR ROLE mra_prd_99999
    IN SCHEMA mra_dev_schm_99999 GRANT ALL ON TABLES
    TO mra_prd_99999_user1;

-- and similarly to the other user


What should be innodb_buffer_pool_chunk_size for pool_size=2G, pool_instances=2? I have 4G VPS with following config

innodb_buffer_pool_size=2048M
innodb_buffer_pool_instances=2

SELECT @@innodb_buffer_pool_chunk_size/1024/1024
128.00000000

Should I change innodb_buffer_pool_chunk_size or let it be default?

You should leave the innodb_buffer_pool_chunk_size "as is".

Last year, Percona published some nice formulas to figure out the best chunk size

determine_best_chunk_size{
  if innodb_buffer_pool_size / innodb_buffer_pool_instances < innodb_buffer_pool_chunk_size
  then
    innodb_buffer_pool_chunk_size = roundDownMB(innodb_buffer_pool_size / innodb_buffer_pool_instances)
  fi
}
determine_amount_of_chunks{
  innodb_buffer_amount_chunks_per_instance = roundDown(innodb_buffer_pool_size / innodb_buffer_pool_instances / innodb_buffer_pool_chunk_size)

  if innodb_buffer_amount_chunks_per_instance * innodb_buffer_pool_instances * innodb_buffer_pool_chunk_size - innodb_buffer_pool_size > 1024*1024
  then
    innodb_buffer_amount_chunks_per_instance++
  fi
}

determine_best_chunk_size
determine_amount_of_chunks

innodb_buffer_pool_size = innodb_buffer_pool_instances * innodb_buffer_pool_chunk_size * innodb_buffer_amount_chunks_per_instance

Since you have only 2GB for the Buffer Pool, it is definitely not worth the effort to do any tuning,


Cursor Based Pagination for Timestamp field with non-unique values We are trying to create a query for the following table :

 CREATE TABLE `Action` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `vendor_Id` int(10) unsigned DEFAULT NULL,
  `name` varchar(60) NOT NULL,
  `assigned_To` varchar(40) DEFAULT NULL,
  `updated_At` datetime(3) NOT NULL,
  `completed_At` datetime(3) DEFAULT NULL,
  `deleted_At` datetime(3) DEFAULT NULL,
  `created_At` datetime(3) NOT NULL,
  `deadline` datetime(3) NOT NULL,
  `notes` varchar(400) DEFAULT NULL,
  `completed` tinyint(1) DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `action_vendor_id_foreign` (`vendor_Id`),
  CONSTRAINT `action_vendor_id_foreign` FOREIGN KEY (`vendor_Id`) REFERENCES `Vendor` (`vendor_Id`)
) ENGINE=InnoDB AUTO_INCREMENT=42 DEFAULT CHARSET=latin1

We want to show the Actions using pagination based on deadline field in Ascending order. But deadline can have same values and is creating problem for us to implement cursor based pagination on deadline field.

Action can be created for any future date. We thought of using created_At and deadline but it fails as we can  create Action on 22 Nov with deadline 25 Nov and another action on 23 Nov with deadline 24 Nov. I mean we there is no relationship between two of them. and exactly similar thing happens with the id and deadine column.

How can I tackle this problem of showing Actions using pagination with deadline in ASC order.

ORDER BY deadline ASC, id ASC will be "deterministic" (assuming id is the PRIMARY KEY for the table).  And include INDEX(deadline, id).

More discussion of pagination and the evils of OFFSET:  http://mysql.rjweb.org/doc.php/pagination

If there is any chance of adding or deleting rows while the user is paging through the list, items can be skipped or duplicated in the paginated list.  "Remembering where you left off" (see link) solves the problem.  However, it is a little tricky to say > when two columns are involved.


master-master replication or master slave replication? We have right now one server with a database.
A device and a website are accessing that database causing load

What I want is to create two servers master-slave where master databases are replicated to slave but slave have some databases that must not be in master database.

Is it possible to do it using master-slave replication or using master-master replication?

You do not want writes to be replicated from A to B then A better be slave of B, master.

again, to complicate things you can use master-master with replication filters but let's not get there if you do-not wish to.

That said, writing on slave is sort of risky business where you're going to "accidentally" write on databases being replicated from master and that's not good!
As long as you're making sure you're only writing to slave-only databases it should be fine. You should choose to create dedicated users for such activity.
You might also want to wonder:
- What about backups of master data?
- What about backups of slave data?

just saying... but yea to answer your question go with master-slave.


How to add FK on a column of composite type in PostgreSQL? I have a table which contains a composite type and I am trying to connect a column of it to another table's PK.

CREATE TABLE aaa (col1 SERIAL PRIMARY KEY);

CREATE TYPE bbb AS (col2 INTEGER);
CREATE TABLE ccc (col3 bbb);

I tried to add FK but it was syntax error.

CREATE TABLE ccc (col3 bbb, FOREIGN KEY col3.col2 REFERENCES aaa col1);

ERROR:  syntax error at or near "col3"
LINE 1: CREATE TABLE ccc (col3 bbb, FOREIGN KEY col3.col2 REFERENCES…

This is also a syntax error.

CREATE TABLE ccc (col3 bbb, FOREIGN KEY (col3.col2) REFERENCES aaa (col1));

ERROR:  syntax error at or near "."
LINE 1: CREATE TABLE ccc (col3 bbb, FOREIGN KEY (col3.col2) REFERENC…

Also this…

CREATE TABLE ccc (col3 bbb, FOREIGN KEY ((col3).col2) REFERENCES aaa (col1));

ERROR:  syntax error at or near "("
LINE 1: CREATE TABLE ccc (col3 bbb, FOREIGN KEY ((col3).col2) REFERE…

How can I add FK to a sub-column which is a column of a composite type?
I am not trying to make multi-column FK. I wan to link a single INTEGER column to another INTEGER column.

I am using PostgreSQL 9.3.

Update

This is simplified version of my current schema. I am essentially abstracting tables into hierarchical key-value set.

CREATE DOMAIN           "AlwaysSUID" AS BIGINT NOT NULL;
CREATE DOMAIN           "OptionalSUID" AS BIGINT DEFAULT NULL;

CREATE DOMAIN           "AlwaysString" AS TEXT NOT NULL;
CREATE DOMAIN           "OptionalString" AS TEXT DEFAULT NULL;

-- More similar domains...

CREATE TYPE             "Account" AS
(
    "socialName"            "AlwaysString",
    "mailAddress"           "AlwaysString",
    "passcodeHash"          "AlwaysString"
);

CREATE TYPE             "Session" AS
(
    "accountID"             "AlwaysSUID",  -- This needs to be linked to `AccountMap.ID` as FK.
    "chracterID"            "AlwaysSUID",
    "initiationTime"        "AlwaysTimestamp",
    "deviceNameID"          "AlwaysString"
);

CREATE TABLE            "AccountMap"
(
    "ID"                    BIGSERIAL PRIMARY KEY,
    "content"               "Account"
);

CREATE TABLE            "SessionMap"
(
    "ID"                    BIGSERIAL PRIMARY KEY,
    "content"               "Session"
);

This avoids the syntax error:

create type bbb as (col2 int);

create table aaa (
  col1 serial primary key
);

create table ccc (
  id serial primary key,
  col3 bbb references aaa --will reference the primary key of aaa
);

Even without the syntax error, you still get:

"Schema Creation Failed: ERROR: foreign key constraint "ccc_col3_fkey" cannot be implemented
  Detail: Key columns "col3" and "col1" are of incompatible types: bbb and integer.: "

This works though:

create type bbb as (col2 int);

create table aaa (
  col1 bbb primary key
);

create table ccc (
  id serial primary key,
  col3 bbb references aaa
);

But now you don't have a sequence. Also, you can't create sequence as...

So you are going to have to live without sequences on those tables or just use an int instead of a bbb.


Execute sp_WhoIsActive as a query I am at a client where they don't allow Adam Machanic's sp_WhoIsActive stored procedure to be installed anywhere on the SQL server. Does anyone have any suggestions for running it as a query instead?

In general you can run any SP as a query by getting rid of the "CREATE PROCEDURE" part and defining all of the parameters as variables.

For example

CREATE PROCEDURE test (@var1 int, @var2 int) AS
PRINT @var1
PRINT @var2

Would become

DECLARE @var1 int
DECLARE @var2 int
PRINT @var1
PRINT @var2

You might also ask them if it is ok if you create a "DBA" database for code like this and put the SP there.  That way the SP is on the instance and will run correctly, but is still segregated from the rest of their databases.


SQL 2008 Server - performance loss possibly connected with a very large table We have SQL Server 2008 with 3 active databases running on it.

DB1 - cca 400 MB size
DB2 - cca 8 GB size
DB3 - cca 42 GB size - but the majority of records are not used at all

In DB2 we have a this table

CREATE TABLE [dbo].[PenData](
    [IndicatorID] [smallint] NOT NULL,
    [Time] [datetime2](0) NOT NULL,
    [Value] [real] NULL,
    [ValueMax] [real] NULL,
    [ValueMin] [real] NULL,
 CONSTRAINT [PK_Data] PRIMARY KEY CLUSTERED
(
    [IndicatorID] ASC,
    [Time] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

This table by itsel occupies cca 8 GB and has 283 029 812 records.
Vast majority of records in this table are historical records and are accessed very seldom or never. But a small part of recent records is used quite a lot and every hour many new records are getting inserted in this table.

The problem is that we recently observe performance problems in DB3. Though performance of DB2 and PenData is OK.

My question is:

1.could the size of the table PenData be important factor for the overall server performance? How do these many unused table records affect memory allocated by the server?

2.Could I get significant performance gain on the server (in DB3) if I delete half of the records from the very large table PenData?

3.And are there any tools to monitor performance when I do not have permissions to access Activity monitor?

EDIT

I was quite terrified to see (using scripts provided in the answers) that the PenData table took 60-70% of whole SQL Server memory (which is relatively low cca 6 GB). I am not sure why, since this is the application I have programmed myself and I do not see any reason, why should so many rows from this table remain cached in memory. It had also been my mistake to run SELECT COUNT(*) FROM PenData before I had tried to see, how much of PenData remained cached in memory.

I have omitted one foreign key I have in this table, so I present it here:

ALTER TABLE [dbo].[PenData]  WITH NOCHECK ADD  CONSTRAINT [FK_Data_Indicator] FOREIGN    KEY([IndicatorID])
REFERENCES [dbo].[Indicator] ([IndicatorID])

I have deleted milions of records in batches of 100 000 records using SET RECORDCOUNT 100000 from PenData. Now it has 211 120 425 records. I have run DBCC SHRINKDATABASE (PenData, 20) - only after this has the memory consumption by PenData decreased significantly.

The performance and memory consumption by other databases got better.

But after one day the table PenData occupies again almost all the memory...

EDIT

I have changed one single SQL command in one single stored procedure and now everything is perfect, database SupervisionP takes only 184 MB in cache! See detail here

Index seek much slower with OR condition compared with separate SELECTs

Thanks for your help.

You can use the following query to determine how much RAM is being used by each database:

USE master;

SELECT d.name, CAST(COUNT(1) AS BIGINT) * 8192 / 1048576 AS MBinMemory
FROM sys.dm_os_buffer_descriptors bd
    INNER JOIN sys.databases d ON bd.database_id = d.database_id
GROUP BY d.name
ORDER BY d.name;

To see memory used by objects in a specific database, do:

USE <DB_NAME_IN_QUESTION>

SELECT
    obj.name AS ObjectName
    , index_id
    , CAST(count(*) AS BIGINT) * 8192E0 / 1048576 AS CachedMB
FROM sys.dm_os_buffer_descriptors AS bd
    INNER JOIN
    (
        SELECT object_name(object_id) AS name
            , index_id
            , allocation_unit_id
        FROM sys.allocation_units AS au
            INNER JOIN sys.partitions AS p
                ON au.container_id = p.hobt_id
                    AND (au.type = 1 OR au.type = 3)
        UNION ALL
        SELECT object_name(object_id) AS name
            , index_id
            , allocation_unit_id
        FROM sys.allocation_units AS au
            INNER JOIN sys.partitions AS p
                ON au.container_id = p.partition_id
                    AND au.type = 2
    ) AS obj
        ON bd.allocation_unit_id = obj.allocation_unit_id
WHERE database_id = db_id()
GROUP BY name, index_id
ORDER BY count(*) DESC;

The last column in the above query shows the amount of RAM being used by the given database object.

You can see query stats by looking at:  (among a great deal of other things)

SELECT qs.execution_count
    , qs.total_worker_time
    , qs.total_elapsed_time
    , qs.total_logical_reads
    , qs.total_physical_reads
    , qs.total_logical_writes
    , qt.text /* This is the actual query text */
FROM sys.dm_exec_query_stats qs
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
ORDER BY qs.execution_count DESC;


MySQL not responding, Writing to net status I just had to reboot MySQL to regain access to some of my website. It had under 300MB of RAM used. Another product was working fine though, accessing it's own tables which are InnoDB.

Here's the show processlist before I rebooted it:

3629079 user    ip:4815 web Query   2324    Writing to net  SELECT * FROM web.files order by file_name_eng desc
3632228 user    ip:4817 null    Sleep   2323        null
3632453 user    ip:4818 null    Sleep   2323        null
3649490 user2   127.0.0.1:28050 null    Query   1967    Locked  UPDATE web.files SET date_publish_test = NOW(), uuser_id = 2 WHERE file_id = 20454

That is a simple select, here's the explain of it (35K records in total, type MyISAM):

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   SIMPLE  files   ALL null    null    null    null    30593   Using filesort

Version: 5.0.54a-enterprise-gpl-nt on Windows 2003.

Am I going at this properly?

Your problem is quite straightforward.

You mentioned that the table is using the MyISAM storage engine.
The processlist shows

Process ID 3629079 is running SELECT * FROM web.files order by file_name_eng desc
SELECT has been running 2324 seconds (39 min 44 sec)
Process ID 3649490 is running UPDATE query
SELECT has blocking the UPDATE for 1967 sec (32 min 47 sec)

NOTE : Any INSERT, UPDATE, DELETE on a MyISAM table requires a full table lock each time.

NOTE : If a series of 50 UPDATEs is issued on one table, each UPDATE requires a full table lock.

SUGGESTION #1

If web.files has no FULLTEXT indexes, you should convert web.files to InnoDB with

ALTER TABLE web.files ENGINE=InnoDB;

I suggested this because InnoDB will allow UPDATEs, DELETEs, and INSERTs to harmoniously coexist with SELECTs via MVCC and Transaction Isolation.

If you are not the Project Manager/Lead Developer, please consult with your Project Manager/Lead Developer before doing this.

SUGGESTION #2

SELECT * FROM web.files order by file_name_eng desc

From the EXPLAIN plan, I can see that the query

Query must do a full table scan because there is no WHERE clause
There is no index on file_name_eng column, so there is no assistance in order the table (a filesort would happen, but happen fast)

Therefore, you should run

ALTER TABLE web.files ADD INDEX (file_name_eng);

Rhetorial Questions

Do you need SELECT * ?
Could you list only the columns you really need ?


mysql 5.7 general_log table has user_host but the file does not I'm trying to resolve an issue where one particular user hammers our mysql 5.7 database every 2 hours, for a few minutes. Not evil, but I'm suspicious it may be inefficient (or it may not be and it's just the way it is).

The mysql.general_log table gives me everything I want, but I prefer the ease of a file. The output to the file specified with general_log_file works fine, but doesn't include the user_host data that the database table has.

I've read 5.4.3 The General Query Log, googled, but can't seem to find any hint of this.

Is there a format I can set that will include user_host into the file system log file? I must of missed something obvious, why wouldn't the output be exactly the same, file or table?

example of what I see in the file:

2018-03-01T15:41:45.450491Z     137300 Query    SHOW COLLATION
2018-03-01T15:41:45.451280Z     137299 Query    SET NAMES latin1
2018-03-01T15:41:45.451425Z     137299 Query    SET character_set_results = NULL
2018-03-01T15:41:45.451554Z     137299 Query    SET autocommit=1

PS

My workaround is

mysql --user etc... -e "select user_host,argument from general_log where user_host like 'billybob%'" > junk

which isn't as nice as (IMO)

grep billybob log_file

edit:

2018-03-01T15:41:45.450317Z     137300 Query    /* mysql-connector-java-5.1.16 ( Revision: ${bzr.revision-id} ) */SELECT
 @@session.auto_increment_increment

IDEA #1 : USE THE SLOW LOG !!!

The Slow Log ??? Yea, I know what I said : USE THE SLOW LOG !!!

How can you ???

With the slow log being written to a text file, the header info for each slow log entry has the user@host right on the first line of each entry (ignore the # Time: line since one or more slow log entries can be saves within a single time window)

For example, the post Mysql slow query log always include "# Time:" has this example slow log:

# Time: 150419  6:00:43
# User@Host: web[web] @ localhost []
# Query_time: 7.730519  Lock_time: 0.000070 Rows_sent: 167620  Rows_examined: 167620
SET timestamp=1429416043;
SELECT /*!40001 SQL_NO_CACHE */ * FROM `messages`;
# Time: 150419  6:00:45
# User@Host: web[web] @ localhost []
# Query_time: 3.480173  Lock_time: 0.000101 Rows_sent: 0  Rows_examined: 0
SET timestamp=1429416045;
INSERT INTO user (lastupdated, fk_id, user_id) ..
# User@Host: web[web] @ localhost []
# Query_time: 3.388204  Lock_time: 0.000133 Rows_sent: 0  Rows_examined: 0
SET timestamp=1429416045;
INSERT INTO user (lastupdated, fk_id, user_id) ..

In your case, you could probably run

grep -A 3 billybob log_file

which shows for each occurrence of billybob

The line matching billybob
The Query Time
Actual timestamp
Line 1 of the actual SQL

In order to make the slow log behave like a general log you must run

mysql> SET GLOBAL long_query_time = 0.01;

Then, everything and its grandmother will be written to the slow log.

When you are done with your analysis, set the long_query_time back to its old value:

mysql> SET GLOBAL long_query_time = 10;

Don't forget to truncate the slow log after the analysis by doing

echo -n > log_file

IDEA #2 : Poll the Processlist

The problem with using either the slow log or the general log is that entry will not entry the log until the action is done. In other words, if a query takes 10 min to run, the entry will not land in the log file until the command is done executing 10 minutes after it started.

Wouldn't it be nice to catch the bad queries in the act ???

You should use pt-query-digest. Even with queries that are not done, a histogram will be generated with the pattern of queries that have executed or are still in progress.

Rather than write up an example, please see my posts where I discuss using pt-query-digest

Dec 28, 2011 : Is there a tool like Microsoft's "SQL Server Profiler" for MySQL?
Nov 24, 2011 ": MySQL general query log performance effects

GIVE IT A TRY !!!