PostgreSQL chooses to seq scan huge table instead of index lookup I have a table, account_balance, with primary key (address, timestamp, currency). This table is pretty big, having nearly 1 billion rows and taking ~3T of space. I am doing a simple query on it:
SELECT address, EXISTS(SELECT address FROM account_balance WHERE address = a.address) ex
FROM (VALUES ('ab566dceac0d857dbf05682d1ceb548667cf4580')) AS a(address);

This should be a simple index lookup but instead Postges produces a seq scan. Here's explain result:
Result  (cost=0.37..0.38 rows=1 width=33)
  InitPlan 1 (returns $0)
    ->  Seq Scan on account_balance  (cost=0.00..355882209.06 rows=965801108 width=0)
          Filter: ((address)::text = 'ab566dceac0d857dbf05682d1ceb548667cf4580'::text)

If I set set  enable_seqscan = off then as expected:
Result  (cost=0.99..1.00 rows=1 width=33)
  InitPlan 1 (returns $0)
    ->  Index Only Scan using account_balance_pkey on account_balance  (cost=0.83..153234426.09 rows=965801135 width=0)
          Index Cond: (address = 'ab566dceac0d857dbf05682d1ceb548667cf4580'::text)

So the question is - why it chooses the seq scan? It should never make sense to prefer seq scan to index lookup on such a huge table. What could compel it to choose to go sequentially over a 3T table (which of course takes forever) instead of going for the index (which takes microseconds)? Is some parameter wrong or some system variable I need to check? I tried SET random_page_cost = 1 and for some cases it helps, but for others it still insists on using seq scan. Only setting enable_seqscan to off seems to fix it consistently, but I feel it's a wrong approach.
I am not sure how to interpret the costs numbers - I feel like they may be the key for it, but I can't figure out where they come from and why they are what they are.
This is on PostgreSQL 12.
P.S. Tried to do ANALYZE, didn't change anything.

EXISTS will stop executing as soon as a result is found, so if that happens early on, a sequential scan will be very fast. Obviously PostgreSQL estimates that very many rows in account_balance have that address.
The cost estimate on the sequential scan is misleading, since it is for the complete scan. As you see, the final cost is estimated quite low.
Without EXPLAIN (ANALYZE, BUFFERS) it is impossible to say if PostgreSQL is right, but then you only asked for an explanation why it would choose such a plan.


Why Wikipedia using blob type instead of text type? As I experienced, Blob type is good choice when you want to save binary data such as images or videos etc and Text type is good choice when you want to save large string.

Today I saw Wikipedia database. The interesting thing was that wiki use medium blob type for storing wikitext of the page.

So I want to know why Wiki prefer to use Blob type instead of Text?

Thanks

The 'right' way to store text is to use TEXT with CHARACTER SET ut8fmb4.  However, that requires the you know what encoding exists in the client, so that it can be converted (by MySQL) as it is stored.

By storing into BLOB, the above issue is eliminated.  However, that leads to another issue -- how to display the text.

Another possible excuse for BLOB is that Wikipedia was probably started before utf8mb4 was added to MySQL.

(We can only guess.)


SQL query for all combinations without repetition I need a function for calculating combinations without repetition from a list.

Example:
I have a list of names e.g.

Tom
Bob
Paul
Tina

Now I need a list of 3 names out of the 4 without having a name twice and without having the 3 names twice because of ordering. The correct result would be

Tom,Bob,Paul
Tom,Paul,Tina
Tom,Bob,Tina
Bob,Paul,Tina

Of course this also should work quite fast with 14 out of 19 or similar.

You can use regular joins:

SQL> WITH DATA AS
  2   (SELECT 'Tom' NAME
  3      FROM dual
  4    UNION ALL
  5    SELECT 'Bob' NAME
  6      FROM dual
  7    UNION ALL
  8    SELECT 'Paul' NAME
  9      FROM dual
 10    UNION ALL
 11    SELECT 'Tina' NAME
 12      FROM dual)
 13  SELECT *
 14    FROM DATA d1
 15    JOIN DATA d2 ON d1.name > d2.name
 16    JOIN DATA d3 ON d2.name > d3.name;

NAME NAME NAME
---- ---- ----
Tom  Tina Bob
Tom  Tina Paul
Tom  Paul Bob
Tina Paul Bob


Query CMS From PowerShell Without Loop Currently, I have a script queries my CMS server to get the list of servers in a certain CMS group. With that list, I perform a foreach loop to execute a query against each instance in the CMS group individually.

I am wondering if there is a way (such as in SSMS) where my query can be run against all targets in the CMS group and results from all targets are shown in one table?

Essentially I don't want to end up with my results separated. I would like them all in one table. If this is possible it would be easier than formatting data rows in PowerShell.

Since you are using Powershell anyways, I'm not sure why you wouldn't just want to do the loop and return the results from each server to a data object.

If you want to break out of powershell and do it from SSMS, as long as you know your servers to begin with you can look into using Registered Server groups which allow you to run a query against multiple SQL instances at once.

Basically, from SSMS, go to View > Registered Servers.
It should appear in it's default location on the left.  From there, drill down to Local Server Groups, right click > New Server Group.
Add the group name 'CMS Group'.
Then right click on the new group and select 'New Server Registration'.  Fill in your connection details and add the server.
Repeat that process until all your servers are added to the group.

Once the group is complete you can right click on the group folder and choose 'New Query'.
A new query window will open with a pinkish red color at the bottom where you would normally see your connection details.  This new query window will run against all members of that group.


2008R2 Cannot Disable Non-Clustered Index We have identified a handful of superfluous non-clustered indexes and I would like to disable them for a few days before straight dropping them.  However when using the GUI in SSMS (right click > disable) OR running ALTER INDEX DISABLE it says Successful but the index is still online.  I cannot seem to disable it.  Even in AW2008R2 I cannot disable an index.  Help!

You are relying on the graphical tree icon in SSMS to indicate that an index is disabled. In my experience, this has never been true for indexes (though it is true for certain other things, like jobs and triggers).

To address Paul's comment:

  No, I don't know why they changed this behaviour.

I don't know that they have changed it in the way the asker suggests:

  I swear it used to show a little down arrow on the index icon in the tree.

Unless we're going back to 2005 days (I have 2005 instances, but no 2005 Management Studio handy to test). In one way, they've actually improved the behavior; in another, they've made it worse. Let me explain.

In the following screen shot, 2012/2014 is on the left, 2008/2008 R2 is on the right. There is even less visual cue in the older UI about the index being disabled. Notice that the index name in the tree has the same visual and iconic representation in both versions, even though the index is disabled (click to enlarge), and only 2012/2014 has the warning that Paul mentioned:

So, as I said, I think the visualization has actually improved, even if it is less than perfect - I do tend to agree that there should be a red down arrow or something similar to be consistent with other elements in the UI (have you filed a Connect item yet?).

Now. how has it gotten worse in the newer versions of SSMS? Well, I can't find a way to re-enable the index in the UI. In 2008/2008 R2, there was a "Use index" checkbox on the Options tab, which works - though I'm not sure how intuitive that is. There's also a checkbox for "Rebuild index" which seems to be more appropriate for a button, since it's not an option or a property, but I suppose that is a different discussion (click to enlarge):

To verify the index is really disabled, I would not use the UI at all, but rather the catalog views:

SELECT i.name, i.is_disabled
  FROM sys.indexes AS i
  INNER JOIN sys.objects AS o
  ON i.[object_id] = o.[object_id]
  INNER JOIN sys.schemas AS s
  ON o.[schema_id] = s.[schema_id]
  WHERE s.name = N'dbo' -- or another schema
    AND o.name = N'the table the index belongs to'
    AND i.name = N'the index you disabled';

You can leave out the last AND if you want to see all the indexes for a table. If you want to see all disabled indexes in your database, small change:

SELECT s.name + '.' + o.name, i.name
  FROM sys.indexes AS i
  INNER JOIN sys.objects AS o
  ON i.[object_id] = o.[object_id]
  INNER JOIN sys.schemas AS s
  ON o.[schema_id] = s.[schema_id]
  WHERE i.is_disabled = 1;

You can also just right-click the index in the tree, Script Index As > Create To > New Query Editor Window, and you should see that - for an index that you have disabled - the index includes a DISABLE script at the end, e.g.:

USE [yourdb]
GO

CREATE INDEX ...
GO

ALTER INDEX [x] ON [dbo].[y] DISABLE
GO


innodb fulltext ibdata1 size I am running mariadb 10 and have a large innodb table with a full text index.
I have innodb_file_per_table=ON

I've noticed though that the ibdata1 file grows at the same rate as my table file.

I have tried using mysqldump, removing the files and reloading as well as separating the undo tablespaces. Neither of those made any difference so I assume this is not a transaction overhead of some kind.

Does the ibdata1 file store the actual full text index or is there another explanation for the growth in the file?

First of all, with innodb_file_per_table=ON, all data and index pages are stored in the .ibd file of the table.

The growth of the system tablespace (ibdata1) can be a big headache.

I have mentioned this in earlier posts

Apr 23, 2013 : How can Innodb ibdata1 file grows by 5X even with innodb_file_per_table set?
Mar 31, 2014 : mysql directory grow to 246G after one query, which failed due to table is full
Jun 16, 2014 : MySQL Index creation failing on table is full

In your case, there is transaction overhead. You have big transactions because of the mysqldump. Why ?

The default option called --opt has many options enabled, including --extended-insert. Extended inserts will cause mysqldump to INSERT INTO multiple rows in batches. Each batch of rows is a single transaction. Thus, you should expect ibdata1 to get bloated, especially if the table has TEXT or BLOB data (I suspect that since you have fulltext indexes).

The only way around this would be to run the mysqldump again with --skip-extended-insert. This will do three(3) very nasty things:

Runs INSERT for each row
Makes a much larger mysqldump file
Take a ton longer to reload

This makes each individual INSERT transaction hundreds or thousands of times faster because you are inserting one row instead of hundreds or thousands. Unfortunately, you are doing hundreds or thousands longer to execute because of the number of INSERTs. This is just one way to prevent ibdata1 from getting bloated.

Another thing you could try would be to drop the FULLTEXT index from the table, mysqldump it with skip-extended-insert, reload it, then add the FULLTEXT index back. This could help because the adding the FULLTEXT index is not a transaction.

Give it a Try !!!


Oracle Enterprise Manager 11g dbconsole "Internet Explorer cannot display the webpage" The dbconsole is configured and started, but when I navigate to it, Internet Explorer shows the page stating that "Internet Explorer cannot display the webpage". This is the error that means that IE received no response from the server at all. The firewall is completely disabled.

I've tried -deconfig and -config to no avail. Any thoughts that could possibly lead me down the path to fixing this are appreciated.

OS: Windows Server 2003 32-bit
DB: 11gR2 Enterprise Edition 11.2.0.3.0
EM: Whatever comes with DB 11gR2 11.2.0.3.0

UPDATE:
I'm using the link that is automatically created for you when you run emctl -config dbconsole db. The URL is https://corp-svr-001-xxxxxx.xxxxx:5500/em where x represents an alphanumeric character removed for privacy. The reason it configures at port 5500 is that there is a 10g instance on the same server that already has port 1158. We recently updated it to 11g but haven't uninstalled 10g yet.

I've tried changing the URL to use localhost and the network IPv4 address to no avail.

This is broken due to this patch: http://technet.microsoft.com/en-us/security/advisory/2661254

As per this link http://trinidba.wordpress.com/2012/10/11/internet-explorer-cannot-display-the-webpage-when-connecting-to-oem/ you can fix it with Oracle Doc ID Doc ID 1489557.1


Mysql Alter table with Instant algorithm I just read this MySQL 8 official docs for new features of online DDL operation, where new algorithms added INSTANT. but I am a little bit confused about its working. Can someone please help me to understand the below statement

The benefit of the INSTANT algorithm is that  only metadata changes
are made in the data dictionary.  There is no need to acquire metadata
lock during SE changes and we  don’t touch the data of the table.

The manual goes into a lot of gory details; you can mostly ignore them.  In general, if you do not provide an "algorithm", the ALTER will pick the fastest way.  If you specify INSTANT, but that algorithm is not possible for what you want to change, it will spit at you.  (No harm done.)
At one extreme (the COPY end):  Changing the PRIMARY KEY (on InnoDB) requires rebuilding the entire table, reordering the data, and rebuilding all the indexes (because the PK is part of every secondary index).
At the other extreme (the INSTANT end):  Adding an option to the end of an ENUM list does not require touching the data at all.


SSMS table properties show an exception for Index space on every table When getting table properties from a SQL 2014 server (12.0.4449) using SSMS 2014 (12.0.4100) or 2016 (13.0.15600) on any database or table I get the following text for the "Index space" property on the Storage tab:

  An exception occurred while executing a Transact-SQL statement or
  batch.

This is the error:

Like Haris commented, it's likely an SSMS bug that Microsoft will have to resolve. But you should be able to run a trace or an Extended Event session to capture the TSQL that SSMS is using to get the data that you are after. Capture the TSQL and then run that in a query window. Or use the query from marc_s.


MongoDB deleted logs So I recently realized my /var directory was growing but couldn't find anything using du, then find out about lsof and found the following:

lsof -nP | grep '(deleted)'
mongod    23184 23205          mongodb    5w      REG              202,1 10765518018     284593 /var/log/mongodb.asd/mongod.log (deleted)

Quite a few lines of those, but I understand they won't just dissapear until the mongod process is restarted?

Anything I can do about it?

This is under Ubuntu 16.04 and MongoDB 3.6.13.

Looks like somebody deleted the log file while it was used by the mongodb process. Deleting log files is such a manner is not a good idea as the process will keep it locked and that means the file will keep occupying space on the disk. Such deleted files entry in the lsof will disappear once the process that is locking it is restarted.

The best way to handle logs files is by using proper log rotation by utilities like logrotate which is available on almost all linux systems.

If you ever face a situation where the log file has grown too big and you want to free up space without restarting the service, the best way would be to truncate the file using - cat /dev/null > /var/log/my_big_log_file.log or just echo "" > /var/log/my_big_log_file.log. Just remember that, this is not a proper solution for handling log files and should be used as a temporary fix.


What is the optimal response time for file growth We normally set our file growth in percent (10%,20%) or fixed size (10 MB, 20 MB etc). If the disk response time is too high for the requested size to grow, then users will be affected and have to wait until the file growth completed. We can see the response time in micro seconds at default trace file column named as 'Duration'.

My question is, what is the optimal response time for every growth in milliseconds?

First of all you want to make sure that your database is sized appropriately up front.  Usually I like to plan for 6 months to a years worth of growth.  That being said your data file(s) should be the only ones growing on a regular/irregular basis.  Your log file once it hits a certain point should not grow much if at all, assuming you are taking regular backups.  Given that, you can use a server setting called Instant file initialization that will make your database growth very very quick, regardless of how much space you are adding.

Here is a link you can read to start you on it.  http://msdn.microsoft.com/en-us/library/ms175935(v=sql.105).aspx

If you have this turned on then you can afford to set your auto growth to larger sizes so that you don't have to grow as often.


Replace nested "select with count" with outer join? is this possible? SELECT c.classid , c.classname ,
      (select count(s.id) from students s where s.classid = c.classid and
      status = 'failed') as noOfFailed,
      (select count(s.id) from students s where s.classid = c.classid and
      status = 'passed') as noOfPassed
      FROM class c where c.schedule = 'MWF'

Is there a way to replace/optimize this with JOIN?

select c.classid,
       c.classname,
       count(case when s.status = 'failed' then 1 end) as no_of_failed,
       count(case when s.status = 'passed' then 1 end) as no_of_passed
from class c
  left join students s on c.classid = s.classid
where c.schedule = 'MWF'
group by c.classiid, c.classname;

This exploits the fact that aggregate functions will ignore null values.

But depending on your DBMS and the size of your tables, your original query might actually be faster. But this can only be found out if you show us the execution plan.


SELECT * FROM Not Executing First I executed the following query:

 BEGIN TRANSACTION
        UPDATE MOVIES
        SET Rating = '777'
        WHERE Title IN
        (SELECT TOP 1 Title
           FROM MOVIES
           ORDER BY Title DESC);

Then the simple query to see the change I made to the table:

SELECT * FROM MOVIES;

There are only 18 rows and 3 columns but it's been executing for 15 minutes now. Before that I cancelled and tried executing again but it just won't execute.

Your BEGIN TRANSACTION is holding a lock on the table. You have to either:

Commit it with COMMIT
Roll it back with ROLLBACK
Use an isolation level hint in your query, like add WITH (NOLOCK) after the MOVIES table name
Use a different default isolation level in your database, like Read Committed Snapshot Isolation, where writers don't block readers (although you won't see data that isn't committed yet)
Run the SELECT query from the same session as your transaction (if you're working in Management Studio, that means running the query in the same tab/window)


Multiple system.replset collections in local I have a replicaset that's showing multiple system.replset collection in the local db.

replset1:PRIMARY> show collections;
me
oplog.rs
replset.minvalid
slaves
system.indexes
system.profile
system.replset
system.replset

Running db.system.replset.find()  returns the documents from both with out error.

They are different versions, one showing the current membership, another an older setup.  I haven't seen this before.  Is this expected?

I'm not really seeing any problems from the mongo or application side, this just seems strange.  The duplicates are present on the primary and secondaries.

Should .remove({"version":}) be safe?

I ran into this issue while trying to reconfigure my replicaset. db.system.replset.find().pretty() yielded two replicasets with different versions and different members.
Running db.system.replset.remove({"version":199}) ended up removing the bad replicaset configuration. Reconfiguring my replicaset worked after that.


MongoDB 3.0 : Configure storage engine at collection or database level I know we can configure the storage engine to be wiredTiger at instance level when we start mongod.

But can we also select the storage engine to be different for different collection or database?

You can only use a single storage engine type per mongod instance. There is only a single dbPath setting for a mongod deployment, and you cannot mix & match data files from different storage engines within the same data path.

However, you can use different storage engine configurations within members of a replica set (for example, mmap primary and WiredTiger secondaries). If you do this, you should be wary of differences in performance and be sure to test thoroughly in a QA/staging environment with representative workload.

Another available option is to set storage-engine specific options when creating a collection. For example, you could override the instance-level defaults for WiredTiger and set different options for specific collections (i.e. higher level of compression, or perhaps no compression).


Start job from another SQL server instance I have a job on an SQL 2008 server (Server A) (I know this is not ideal... That issue is being addressed). The agent runs under NT AUTHORITY\NETWORK SERVICE.

I want to add a step that runs a job that sits on another SQL 2014 server (Server B).

I would use:

EXEC [Server B].msdb..sp_start_job N'JobName'

Ran from Server A manually it executes the job on Server B fine as expected.

If I add the task to a job on Server A it fails with message:

  Executed as user: NT AUTHORITY\NETWORK SERVICE. The EXECUTE permission
  was denied on the object 'sp_start_job', database 'msdb', schema
  'dbo'. [SQLSTATE 42000] (Error 229).  The step failed.

I have assigned NT AUTHORITY\NETWORK SERVICE on Server B to the 'TargetServerRole' in msdb and then granted Execute permission to concern user to SP_Start_Job and SP_Stop_Job.

Server A has Server B as a linked server and I tried setting "Local server login to remote server login mappings" Local login NT AUTHORITY\NETWORK SERVICE to impersonate.

The job still fails with the same error.

What do I need to do?

Thanks

You probably don't want to grant direct access to sp_start_job or sp_stop_job, or even grant access to a range of SQL Agent functionality via SQLAgentOperatorRole, to either NT AUTHORITY\NETWORK SERVICE or MyDomain\ServerA$. Doing any of those combinations would, at the very least, allow any process running as NT AUTHORITY\NETWORK SERVICE on ServerA the ability to start and/or stop any job on ServerB.

Using Module Signing, you can set up very granular permissions such that MyDomain\ServerA$ (or any login or role, or any combination of those) can only start and stop that one particular job.

USE [msdb];
GO
CREATE PROCEDURE dbo.StartStopJobX
(
  @Operation VARCHAR(10) = 'start'
)

AS
  IF (@Operation = 'start')
  BEGIN
    EXEC dbo.sp_start_job N'{job_name}';
  END
  ELSE
  BEGIN
    EXEC dbo.sp_stop_job N'{job_name}';
  END;
GO

CREATE CERTIFICATE [SqlAgentPermissions]
 ENCRYPTION BY PASSWORD = 'change_me'
 WITH SUBJECT = 'Allow low-priviledged accounts to start/stop certain jobs',
 EXPIRY_DATE = '2099-12-31';

ADD SIGNATURE
  TO dbo.StartStopJobX
  BY CERTIFICATE [SqlAgentPermissions]
  WITH PASSWORD = 'change_me';

CREATE USER [SqlAgentPermissions] FROM CERTIFICATE [SqlAgentPermissions];

ALTER ROLE [SQLAgentOperatorRole] ADD MEMBER [SqlAgentPermissions];

Now all you need to do is:

GRANT EXECUTE ON dbo.[StartStopJobX] TO [MyDomain\ServerA$];


Can I skip the intermediate table in a join? I've written this query :

SELECT
    "BD - Utilizadores".Utilizador,
    "BD - Utilizadores"."Palavra passe",
    "BD - Áreas".área
FROM "BD - Utilizadores"
INNER JOIN "BD - Permissões"
    on "BD - Utilizadores".id = "BD - Permissões"."user_id"
Join "BD - Áreas"
    on "BD - Permissões".area_id = "BD - Áreas".id

I want to join data from table "bd - utilizadores" with "bd - áreas". Since they have no direct link up I had to use a "man in the middle" i.e. "bd - Permissões". Here is the diagram:

My main question is, is there any other way to do this and get the same result?

tl;dr

The short answer is no. You have to do the multiple join, even if you only want columns from the two tables 'on each side'.

Table design

This seems like a pain, but although it makes your queries longer, it is much more important to get the database structure, including foreign keys right, than it is to make queries short.

There are a couple of ways of arranging foreign keys, sometimes with extra tables which are only there to express relations. For example, if every Cat has a unique Mouse, there could be a foreign key from Cat to Mouse with a unique constraint. It could even be the primary key of Cat. If every Cat has zero or more Mouses, but no Mouse having more than one Cat, you would have a usual foreign key from Mouse to Cat. If every Cat has zero or more Mouses, and every Mouse has zero or more Cats, you would create a third table CatMouseMatching, which has an entry for each 'edge' between a Cat and a Mouse, with foreign keys to the Cat and the Mouse. You will learn that these patterns are very important and the basic cases cover a lot of situations.

Now, suppose that you reverse the foreign keys in your table design, so that Utilizadors and Areas both have a foreign key to Permissoes. This would mean that you could simplify the join - because you could filter on permissoe_id being equal in both tables. However, this would be a quite strange relationship between Utilizador and Area which might not correspond to reality. You might have an Utilizador associated with a Permissoe which did not have any Area linked to it. This Utilizador would just disappear from the inner join.

This might only make sense if there was some real entity which formed the link between the two entities. For example, every Cat lives in a House, and every Mouse lives in a House. You can easily find the Cat-Mouse pairs which live in the same House.

Practical tips

You could always create a VIEW, which makes a common query act like a table. Your query above could be defined as the view, and then other queries could select from it and join to it, without having to understand what joins make up the view internally.

If you don't create a view, you might at least save the snippet of code which does the join, so that you can paste it into other similar queries later.


MySQL select returns no rows while there are many I'm using django to create a database and I manually created a test values in the database by creating objects and saving them to the database.
Now when I run this query:

select TABLE_NAME,
       TABLE_ROWS
from information_schema.tables
where table_schema = 'test_test_db';

It returns the tables with the correct number of rows. For example, a table 'accounts_users' has 4269 rows.

But when I try to select any values using select username from accounts_users;, I get 0 rows back or 'Empty Set' result.

And of course my django app cannot run because there is "no" data in the database.

What is wrong?

I tried manually adding a test user and the TABLE_ROWS was updated "4270" but when I select I get only the test user I added. Anyway, I learned that TABLE_ROWS is only a rough estimate and I guess it is not updated regularly enough.


Join mysql tables to find siblings and get their notifications i have two mysql tabes one for company and one for notifications.
Company

+-----------------------+
| ID |parentID|vaue     |
|1   |0       |22020    |
|2   |1       |23423    |
|3   |2       |22222    |
|4   |2       |23423    |
+-----------------------+

company_notes

+---------------------------------+
|ID   |FromID |  ToID   | NotesID |
|1    |1      |  2      | 33
+---------------------------------+

so far i am able to join the tables to get NotesID of the company who has posted the note. How do i get NoteID of all sibings using company_notes.FromID.

I tried:

Select c1.ID as id,
       ci.Parent as Parent,
       n1.NotesID as noteid
    FROM `company` as c1
    LEFT JOIN comapany as c        on c2.parent=c1.ID
    LEFT JOIN company_notes as n1  on n1.FromID=c1.ID
    WHERE c1.ID=2

but the above code gives me only child notifications and alot of dupilcate values.

how do i find out notifications of all siblings, everyone related to id 2

Sorry if my question was not clear, i have now removed unrequired fields to avoid confusion.

Below is the result that i am expecting:

+-----------------+
|  ID   | NotesID |
|  3    | 33      |
|  4    | 33      |
+-----------------+

Cheers! and Happy New Year's Eve!

I am not sure I really get your purpose, but this is what would gives the answer you're looking for:

SELECT
    company.ID, company_notes.NotesID
FROM
    company
    JOIN company_notes ON company_notes.ToId = company.parentID
WHERE
    company.parentID = 2 ;

If you use a DB that implements WITH, this would show it:

WITH company (ID, parentID, value) AS
(
VALUES
    (1, 0, 22020),
    (2, 1, 23423),
    (3, 2, 22222),
    (4, 2, 23423)
),
company_notes(ID, FromID, ToID, NotesID) AS
(
VALUES
    (1, 1, 2, 33)
)

SELECT
    company.ID, company_notes.NotesID
FROM
    company
    JOIN company_notes ON company_notes.ToId = company.parentID
WHERE
    company.parentID = 2 ;


Is the CPU performance relevant for a database server? This is a purely theoretical question. Let's say I have an application deployed on multiple servers.

A load balancer,
Multiple/scalable applications servers
A (single) database server (for the moment)

On the two first parts, I do know what to look for. But what about the database server? What kind of hardware should I look for?

Is CPU frequency relevant for a database server?
Are multiple core CPUs relevant?
Is RAM more important than CPU?

PS: Supposing the chosen database is MySQL or PostgreSQL.

For PostgreSQL, CPU power can be very relevant, especially if a fairly high percentage of the active working set of your data fits in RAM.  Most of the databases I've worked with have had CPU power as the main bottleneck most of the time.  (I just checked vmstat on a server hosting web sites with millions of hits per day hosting over 5TB of database space, and I never saw more than 2% disk wait time, but saw a peak of 12% user CPU time.)

Since PostgreSQL is process-based, any single process can only run as fast as one core, but in a mix like like we have on the server mentioned above, with a high volume of small requests, total CPU across all cores is most important.  For the same total CPU power, PostgreSQL will generally do better with fewer, faster cores than many, slower cores.

Up to the point where a high percentage of your active data set is cached, adding RAM will typically show more bang for the buck than adding cores.  After you've got sufficient caching, the benefit of additional RAM goes down and you're better off boosting CPU power.

For more details on this topic as it pertains to PostgreSQL, I don't think there is a better source than PostgreSQL 9.0 High Performance by Greg Smith.  (Full disclosure, I was a technical reviewer for the book, but get no financial benefit based on sales.)


How to manage primary key value while multiple secondary server sync with primary server? we have three secondary database server in different cities and one primary server.
my question is i want asynchronous transaction commit to all three database but  primary key data conflict occur when commit to primary database...because same id can generate on all three server.
then which technology is available for this kind of scenario for maintain primary key conflict issue...

Note-all three secondary server work as a 'replication publisher' and primary server work as a subscriber(primary replica of alwayson).

solution from my side- i commit the records on secondary server and i use some tricks on all secondary server like..server 1 (seed value = 1 increment by = 3),server 2 (seed value = 2 increment by = 3), server 3 (seed value = 3 increment by = 3)...according to this setting my primary key value never conflict....so please advice am i going right way or not ???

There are 3 possible solutions that I can think of to what you are asking.

First solution is exactly how you have it, set each server to increment an identity column by the same increment but start them offset by one. (Server 1: start at 1 increment by 2, Server 2: start at 2 increment by 2) This solution works well because it keeps your key small but allows you to generate them without running into conflicts. On the other hand you have to configure each identity column on each server so you have the maintenance overhead.
Another solution is to set your key to a guid, at 16 bytes the chances of running into a conflict are small, but it's a rather wide key and comes with it's own set of troubles.  See here for more info:  Kimberly Tripp GUIDs as Primary Key  Or here:  GUID vs INT Debate
Use a composite key where one column represents the server that created the record and the other column is an identity that increments by 1. This also has a disadvantage of having to configure the column on each server to represent the server it was created on.

In the end there is no right or wrong way to do it, it's all a matter of what trade offs you want to put up with.


SQL SERVER 2016 side by side with previous versions I know that SQL SERVER 2016 can co-exist with previous versions on the same server. I wonder however if there are any gotchas that I should be aware of?
We have a test server that already has  2008r2, 2012 and 2014 instances on it. Is there any chance that there are anything on the existing instances that will behave differently if we install 2016 also on the same server?

Each instance is a separate service with its own set of folders, system databases, agent and binaries so they do not directly impact each other.  All of the instances, however, will share the management tools, and older versions of the management tools cannot manage newer instances.   You can avoid trouble by installing the oldest version instances first and install the newest version last.  This way SSMS, SSCM, Profiler, etc will be the 2016 version and capable of managing all previous versions.

Another thing to consider is your Max Server Memory setting.  You want to make sure that the combined total of all your max server memory settings minus the total memory in the box, will still leave enough memory for the operating system and other applications.

The last thing I would consider is your CPU.  It is possible for instances of SQL to so consume resources that another instance could effectively be 'starved'.  A work around for this is to set affinity mask so that each instance is assigned a set number of CPU.


Get longest strings in jsonb column I have a postgresql table with a jsonb column holding a lot of different data. This data needs to be moved into a DB2 database, but I need to know the max length of each individual "node" within the jsonb data to be sure I don't exceed the limitations of the individual DB2 column they get inserted into.

For example, {'city':'Mesa','county':'Maricopa','mayor':'John Smith'}

Is there a way to get the max length of each value in a jsonb column via SQL, resulting in something like city: 15, county: 22, mayor: 28 short of looping over every record?

If you have a flat JSONB structure, ie. no objects and arrays exist as values, then the solution is simple.

Given the following table and data:

CREATE TABLE jsonbtest (data jsonb);

INSERT INTO jsonbtest (data) VALUES ('{"city":"Mesa","county":"Maricopa","mayor":"John Smith"}');
INSERT INTO jsonbtest (data) VALUES ('{"other city":"Mesa but with a longer name","county":"Maricopa","mayor":"John Smith Jr"}');

you can get the key-value pairs by doing

SELECT (jsonb_each_text(data)).* FROM jsonbtest;

    key     │   value
────────────┼────────────
 city       │ Mesa
 mayor      │ John Smith
 county     │ Maricopa
 mayor      │ John Smith
 county     │ Maricopa
 other city │ Mesa
(6 rows)

Having this, getting the maximal length is a matter of a simple GROUP BY query:

SELECT key, max(char_length(value))
  FROM (SELECT (jsonb_each_text(data)).* FROM jsonbtest) t
 GROUP BY key;

    key     │ max
────────────┼─────
 city       │   4
 other city │  27
 county     │   8
 mayor      │  13
(4 rows)

See all this at work on dbfiddle.  See what else you cana use for processing your JSONB values in the docs.


MySQL Timestamp and NOT NULL We are on MYSQL 5.7 and when we create  a column like so in our DDL

my_update_timestamp timestamp(6) NOT NULL

MySQL complains when we try to create the table with

Error 1067: Invalid default value for my_update_timestamp.

Is it possible to have a not nullable timestamp with no default value?

The usual mistake is to not include (6) on the default:

DEFAULT CURRENT_TIMESTAMP(6)

See about 3/4 of the way down on https://dev.mysql.com/doc/refman/5.7/en/timestamp-initialization.html .

Also, consider that to set this to:  https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_explicit_defaults_for_timestamp


What exactly is the use of the column USER in a pg_hba.conf file? Let's say I have a pg_hba.conf file for my Postgresql server. I know that in order to allow other IP addresses to access my Postgresql server, I need to write their entries in my pg_hba.conf file. Now, what I don't understand is the use of the column USER in the pg_hba.conf file. Lets say my file contains the following entry

host all    user123     192.168.10.27/32    md5

According to my understanding, it means that in order to access my Postgresql server from the machine with IP 192.168.10.27, someone needs to be logged in as the user 'user123'.
But, as far as i understand, in order to access my Postgresql server from 192.168.10.27, i need to create a separate connection to my server from 192.168.10.27. So even if I am logged in as 'user123', I will be anyways creating a separate connection to access my Postgresql server, which won't depend on whether I am logged in as 'user123' or someone else.
Hence, I don't understand the use of USER column.
Can someone please help me?

"someone needs to be logged in as the user 'user123'" - no, that means that someone tries to connect with the Postgres database user user123.

This can be used to allow certain database users to connect only from certain client computers, e.g. the user for a web application is only allowed to connect from the IP of the webserver.

Or this can be used to prevent superuser logins from remote connections by only allowing  local connections for the database user postgres.


Advise On SQL Server Data Replication We have a huge enterprise system, with geographically seperated SQL servers.

We have some requirements related with data replication between these servers.

We have considered the Sync Framework option, but there is a requirement regarding multiple parallel data replication. Thus, Sync framework does not seem to be an option.

Also, we do not have a central database; requirements state that user shall configure the topology and related conflict resolution steps. There are three conflict resolution options; "source wins", "destionation wins" and "timestamp based".

Also, note that, database structure is a bit complicated and we have some deep hierarchies.

Lastly, the only way of communication between these servers is using port 80; using other ports is not an option.

I need to decide how to implement the replication structure, but I do not seem to have any options other than creating a replication structure manually and implementing all features.

Do you know any tools, or have any ideas how to handle replication easily without any central SQL server and with user configurable topologies?

Almost all of your requirements can be provided by merge-replication. There is some information about it in my StairWay series here: http://www.sqlservercentral.com/stairway/72401/

Those articles don't cover replication using port 80 but that is possible.

However, the topology is always a tree. You have one root and the data is distributed through there. A real "distributed agents" topology, where each note can synch with each other node is not possible with the build in tools.


Need both a Readable Secondary and High Availability I am doing research into how to have both a readable secondary database that we can use for offloading heavy queries (such a reports) and also (ideally) use as highly available backup.  We are a VERY small shop, and we do not have the option of hiring a dedicated DBA.  We need a solution that is low maintenance.

We went down the road of Transactional Replication. We have it configured on our staging servers, but we've hit some issues.  (Short version of the issues: the subscriber database is sllloowwww. And, making schema changes in the publisher is a serious PIA.) I am second guessing my decision to use Transactional Replication.  When I began to research the solution to these problems, it became clear that managing Replication is a much larger task than originally anticipated. It has us a bit spooked.

We are already doing nightly off-sight backups in another region of the country.  We're good for pure DR.  It's HA and a readable secondary that we lack.

Here are my questions:

For a small shop with no DBA, what solution(s) should we consider?  Is there one single solution that will provide both a readable secondary and HA? Or should we be looking at two different solutions? Will Always On provide both?  Should we push forward with Replication?

What would you do?

@sp_BlitzErik is correct.  AGs need planning and care.  AGs don't have the particular issues you struggle with in Transactional Replication, but they have others.

If you are comfortable with Windows Server Failover Clusters (WSFC), then you have conquered the biggest hurdle to deploying and running a SQL Server Availability Group.  AGs use WSFC for the quorum model, and for a floating IP that moves between the nodes.

If you are willing to not have automatic failover, you can use the new "Clusterless" AGs in SQL Server 2017.  See Read-scale availability groups.  They are called "Read-scale" AGs to emphasize that they are not intended for HA, but they do support synchronous (zero data loss) replicas, and manual failover.

    For a small shop with no DBA, what solution(s) should we consider?

Also must mention that Azure SQL Database is ideal for a small shop with no DBA.  And enables you to create readable replicas in the standard tier, and premium tier databases get an automatic readable replica.


Select command returning different column values I am just starting SQL and am using version SQL server 2016(few days in to learning)

I am using the WideWorldImporters DB from microsoft.

The basics of my question is I don't understand some results I am getting and why?
I run this command:
use WideWorldImporters
select top (2) OrderID
From Sales.Orders;

and it returns a table with 2 lines as expected but the OrerID column values for the two rows of 1 and 16

However when I run the command:
select top (2) OrderID, CustomerID
From Sales.Orders;

I get the additional column for customerID with 1 and 1, and for the ORDERID column it now returns row values of 2934 and 3482.

I don't understand why OrderID would return the two different results. Am I running the command incorrectly?

I am trying to learn the sum and avg aggregate commands on just the top X rows of the column OrderID with the GROUP BY CUSTOMERID for testing, but ultimately want to do below

This is what I was trying to imagine doing:
If you imagined that OrderID was in fact an total order price, I was trying to sum up each customers order total from all purchases. So sum up every row with customerid =  N and then list all the totals for every customerid. My select top 2 was for testing purposes so it wasn't final results, but led me to discrepancy.

The query you wrote is basically asking the database to give you any two rows of data (any two orders). If you wrote the query with an ORDER BY clause then your results should be the same each time.

SELECT TOP 2 OrderID FROM Sales.Orders ORDER BY OrderID ASC;

This would give you the two rows with the lowest OrderID and you will still get the same two rows even when you add other fields to the SELECT list.

You might be thinking that TOP means that the database will give you the first two, but it actually means that it should stop returning rows after it has returned two rows. And since you didn't have an ORDER BY it could just return whichever two rows are most convenient.


These files are related to the new FULLTEXT index support in InnoDB (starting with MySQL 5.6). Removing them is not a good idea for sure.

Unlike with MyISAM, where FULLTEXT indexes are stored in the tables .MYI file together with all other indexes fulltext indexes in InnoDB are implemented using several internal helper tables which show up as separate .idb table space files in innodb_file_per_table mode:

The "...INDEX_1.ibd" to "...INDEX_6.ibd" table files contain the actual fulltext index, split up into 6 partitions. The "...CONFIG.idb" stores meta information about the fulltext index and the "...DELETED..." files are needed for housekeeping of deleted data.

For more details see

http://dev.mysql.com/doc/refman/5.6/en/innodb-fulltext-index.html#idm140684547617952


Recover registered servers in SQL Server Management Studio 2012 My computer died so it's be reinstalled.

Previously I had SQL Server 2008 R2 installed. On the new system I have SQL Server 2012.

I have the old RegSrvr.xml file (that contains the list of registered SQL Servers).

How do I get the server list from 2008 R2 into 2012?

Update:
When doing  Local Server Groups -> Tasks -> Import and choose the RegSrvr.xml file, click OK it's says "The import was successfull" - but no servers or folders has been added and the RegSrvr.xml file for the 2012 installation has not been modified

Sample from xml file

 <document>
  <docinfo>
    <aliases>
      <alias>/RegisteredServersStore/ServerGroup/DatabaseEngineServerGroup/ServerGroup/Company1/RegisteredServer/stage</alias>
    </aliases>
    <sfc:version DomainVersion="1" />
  </docinfo>
  <data>
    <RegisteredServers:RegisteredServer xmlns:RegisteredServers="http://schemas.microsoft.com/sqlserver/RegisteredServers/2007/08" xmlns:sfc="http://schemas.microsoft.com/sqlserver/sfc/serialization/2007/08" xmlns:sml="http://schemas.serviceml.org/sml/2007/02" xmlns:xs="http://www.w3.org/2001/XMLSchema">
      <RegisteredServers:Parent>
        <sfc:Reference sml:ref="true">
          <sml:Uri>/RegisteredServersStore/ServerGroup/DatabaseEngineServerGroup/ServerGroup/Company1</sml:Uri>
        </sfc:Reference>
      </RegisteredServers:Parent>
      <RegisteredServers:Name type="string">stage.server.dk</RegisteredServers:Name>
      <RegisteredServers:Description type="string" />
      <RegisteredServers:ServerName type="string">stage.server.dk</RegisteredServers:ServerName>
      <RegisteredServers:UseCustomConnectionColor type="boolean">false</RegisteredServers:UseCustomConnectionColor>
      <RegisteredServers:CustomConnectionColorArgb type="int">-986896</RegisteredServers:CustomConnectionColorArgb>
      <RegisteredServers:ServerType type="ServerType">DatabaseEngine</RegisteredServers:ServerType>
      <RegisteredServers:ConnectionStringWithEncryptedPassword type="string">server=stage.server.dk;uid=username;password=AQAAANCMnd8BFdERjHoAwE/Cl+sBAAAAegY4QOvPGEGcK2Xdr+jDjQQAAAACAAAAAAADZgAAwAAAABAAAADesdxAGO4GO879z1yS2DZeAAAAAASAAACgAAAAEAAAAGAvgtRJfPRL/puHnREmKu8YAAAAUxRIQVDZHywb7rOYL9MRqG0B2Jvi8kyfFAAAANs53tVPbVux2j56oRU/2oGtmQc9;pooling=false;packet size=4096;multipleactiveresultsets=false</RegisteredServers:ConnectionStringWithEncryptedPassword>
      <RegisteredServers:CredentialPersistenceType type="CredentialPersistenceType">PersistLoginNameAndPassword</RegisteredServers:CredentialPersistenceType>
    </RegisteredServers:RegisteredServer>
  </data>
</document>

If your RegSrvr.xml contains a  "password=....;" in <RegisteredServers:ConnectionStringWithEncryptedPassword> section, replace ";uid=...;password=...;" with ";trusted_connection=true;"

In the same file, replace:

<RegisteredServers:CredentialPersistenceType type="CredentialPersistenceType">PersistLoginNameAndPassword</RegisteredServers:CredentialPersistenceType>

with:

<RegisteredServers:CredentialPersistenceType type="CredentialPersistenceType">PersistLoginName</RegisteredServers:CredentialPersistenceType>

Now the server registration will try to use Windows Authentication.  Once the Registered Servers are loaded, you can go back and edit the registrations to use your SQL Authentication information.


Collect performance information on high CPU condition in SQL Server I have a SQL server cluster that is often reaching 100% CPU out of working hours. I want to gather some information to help troubleshooting when the actual condition is taking place.

I'm thinking of gathering info from sys.dm_exec_requests for example.

Question: How can i trigger this information gathering to take place based on CPU utilization condition

For Example: Insert the results of sys.dm_exec_requests into a table when CPU > 95%

suggestions for anything else I should gather are very welcome!

It appears that I might be able to do this with alerts, but I need to better understand the counters as my example doesn't fire, despite CPU >5%

USE [msdb]
GO
EXEC msdb.dbo.sp_update_alert @name=N'My Test CPU Alert',
    @message_id=0,
    @severity=0,
    @enabled=1,
    @delay_between_responses=0,
    @include_event_description_in=0,
    @database_name=N'',
    @notification_message=N'',
    @event_description_keyword=N'',
    @performance_condition=N'Resource Pool Stats|CPU usage %|default|=|5',
    @wmi_namespace=N'',
    @wmi_query=N'',
    @job_id=N'00000000-0000-0000-0000-000000000000'
GO

I think we might need to dip into sys.dm_os_ring_buffers and sys.dm_os_sys_info to get the CPU utilization data.

Please find below the script of Glenn Berry

-- Recent CPU Utilization History (SQL 2008/2008 R2 Only)
DECLARE @ts_now bigint = (SELECT cpu_ticks/(cpu_ticks/ms_ticks)FROM sys.dm_os_sys_info);

SELECT TOP(144) SQLProcessUtilization AS [SQLServerProcessCPUUtilization],
               SystemIdle AS [SystemIdleProcess],
               100 - SystemIdle - SQLProcessUtilization AS [OtherProcessCPUUtilization],
               DATEADD(ms, -1 * (@ts_now - [timestamp]), GETDATE()) AS [EventTime]
FROM (
      SELECT record.value('(./Record/@id)[1]', 'int') AS record_id,
            record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int')
            AS [SystemIdle],
            record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]',
            'int')
            AS [SQLProcessUtilization], [timestamp]
      FROM (
            SELECT [timestamp], CONVERT(xml, record) AS [record]
            FROM sys.dm_os_ring_buffers
            WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
            AND record LIKE '%<SystemHealth>%') AS x
      ) AS y
ORDER BY record_id DESC;

Source : Glenn Berry's Script


Weblogic Error: Connection test failed I am trying to create a SQL Server 2014 data source in weblogic through admin console. When I click on test configuration, I get the following error:

  Message icon - Error Connection test failed.
  Message icon - Error [FMWGEN][SQLServer JDBC Driver][SQLServer]Could not find stored procedure 'master..xp_jdbc_open2'.

In WebLogic console, if you are selected XA Driver then it need additional drivers  specific library files like sqljdbc.dll etc.
Better to choose Oracle non XA driver(second item from the dropdown list) while you are configuring your datasource.

HTH


Changing to InnoDB increases iostat I have changed my MyISAM tables to InnoDB and from that day, iostat is increasing continuously. My findings on the server say that mysqld is the one who is increasing iostat.

But how can it increase continuously?

write is heavy. And server has 8GB.am pasting my.cnf

innodb_data_file_path = ibdata1:10M;ibdata2:20M:autoextend
innodb_autoextend_increment=5
innodb_file_per_table
innodb_log_group_home_dir = /var/lib/log/
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
innodb_buffer_pool_size = 2048M
innodb_additional_mem_pool_size = 2M
#innodb_log_file_size = 5M
innodb_log_buffer_size = 8M
#innodb_log_files_in_group=2
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50
innodb_log_file_size = 40M
innodb_log_files_in_group = 2

innodb_thread_concurrency=16
innodb_thread_sleep_delay=1000

innodb_max_dirty_pages_pct = 80

query_cache_size=512M
query_cache_limit= 1M

max_heap_table_size = 32M
tmp_table_size = 64M

read_buffer_size = 512K

innodb_log_buffer_size=8M
thread_cache_size = 70

table_cache=2048

This iostat started increasing when I changed engine of my tables from MyISAM to InnoDB.

Please help now.

Disclaimer: This is still very broad and could be anything, so this answer is only some potential reasons for increased IO activity

My first inclination to your question But how can it increase continuously? when you mentioned that the load is 'write-heavy' is that you have autocommit enabled. If you are doing a lot of inserts, this might be your issue (emphasis mine):

  When importing data into InnoDB, turn off autocommit mode, because it performs a log flush to disk for every insert. To disable autocommit during your import operation, surround it with SET autocommit and COMMIT statements:

SET autocommit=0;
... SQL import statements ...
COMMIT;

  [src]

If that doesn't help, another major source of IO in a write-heavy environment are your indexes. Make sure you are not over-indexing (have unused indexes).

Also, InnoDB will write the primary key data to the index file for each secondary index as a way to identify them. So if you have a lot of natural or composite primary keys in tables that have secondary indexes, that could be a lot of extra data that is being written.


Whenever you use AND clause and picking exact string/integer type with equals = . Its effective to use composite index.

Try having composite index on hilong and lolong. See if the query picks index, as you have two different columns in their own ranges I'm not sure if it could help.

It mostly depends upon how the app-db architecture is defined. If I were to be you.

See if this is in transactional path or analytics path?
See if you can identify any other AND condition that has PK/higher cardinality would reduce the range scan of the columns.
If the table growth is limited or high, upon which you can keep the logic in Mysql if its limited and Latency with data retention is accepted by End users. If not work on other DB stores to suit its requirement in ElasticSearch/Cassandra or any DistributedSystems etc with retention.


Postgres not smart enough to combine fields in WHERE and ORDER BY for index scan? I have the following table in Postgres 9.6:

                                       Table "public.TagNotifications"
    Column    |            Type             |                            Modifiers
--------------+-----------------------------+-----------------------------------------------------------------
 createdAt    | timestamp with time zone    | not null default now()
 updatedAt    | timestamp with time zone    | not null default now()
 id           | integer                     | not null default nextval('"TagNotifications_id_seq"'::regclass)
 tag          | character varying(255)      | not null
 triggerId    | integer                     |
 userId       | integer                     | not null
 comparison   | "TagNotificationComparison" |
 setpoint     | double precision            |
 severity     | "TagNotificationSeverity"   |
 acknowledged | boolean                     | not null default false
 value        | double precision            |
Indexes:
    "TagNotifications_pkey" PRIMARY KEY, btree (id)
    "TagNotificactions_userId_acknowledged_createdAt_tag_id" btree ("userId", acknowledged, "createdAt" DESC, tag, id)
    "TagNotificactions_userId_acknowledged_tag_createdAt_id" btree ("userId", acknowledged, tag, "createdAt" DESC, id)
Foreign-key constraints:
    "TagNotifications_tag_fkey" FOREIGN KEY (tag) REFERENCES "Metadata"(tag) ON UPDATE CASCADE ON DELETE CASCADE
    "TagNotifications_triggerId_fkey" FOREIGN KEY ("triggerId") REFERENCES "TagNotificationTriggers"(id) ON UPDATE CASCADE ON DELETE SET NULL
    "TagNotifications_userId_fkey" FOREIGN KEY ("userId") REFERENCES "Users"(id) ON UPDATE CASCADE ON DELETE CASCADE

I'm trying to ensure good performance for the following query (and similar queries with cursor-based pagination):

EXPLAIN
SELECT * FROM "TagNotifications"
WHERE ("userId" = 2 AND "acknowledged" = false)
ORDER BY "tag" ASC, "createdAt" DESC, "id" ASC
LIMIT 6;
                                     QUERY PLAN
------------------------------------------------------------------------------------
 Limit  (cost=840.10..840.12 rows=6 width=75)
   ->  Sort  (cost=840.10..856.04 rows=6376 width=75)
         Sort Key: tag, "createdAt" DESC, id
         ->  Seq Scan on "TagNotifications"  (cost=0.00..725.81 rows=6376 width=75)
               Filter: ((NOT acknowledged) AND ("userId" = 2))
(5 rows)

Why isn't Postgres smart enough to use the index in this case?  It uses the index if I include "userId" and acknowledged in ORDER BY, but this shouldn't be necessary because of the WHERE condition:

EXPLAIN
SELECT * FROM "TagNotifications"
WHERE ("userId" = 2 AND "acknowledged" = false)
ORDER BY "userId" ASC, "acknowledged" ASC, "tag" ASC, "createdAt" DESC, "id" ASC
LIMIT 6;
                                                                   QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.41..2.27 rows=6 width=75)
   ->  Index Scan using "TagNotificactions_userId_acknowledged_tag_createdAt_id" on "TagNotifications"  (cost=0.41..1974.27 rows=6376 width=75)
         Index Cond: (("userId" = 2) AND (acknowledged = false))
         Filter: (NOT acknowledged)
(4 rows)

It has grown smarter in v10.

I think the reason it wasn't smart enough before is described here:

https://www.postgresql.org/message-id/1788.1481605684@sss.pgh.pa.us

I don't know how to summarize that, so I'll just say "booleans in indexes are confusing"


How to get the Mongo log information for particular range from Mongod.log file I have around 50GB Mongo log file in my cluster, for support purpose i need toshare last 10 days or range(from : this date to:that date) information to support team .

Can you tell me how to get the particular log information from log file.

Thanks

You should be able to extract this information using the mtools package, specifically using the mlogfilter tool. The syntax would be similar to this:

mlogfilter mongod.log --from "end -10d" > Last10DaysMongoD.log


Logical replication - Manually triggering update of data on destination tables (after schema change) I'm testing out Logical replication on Postgresql (10.4 at the moment) with a quite specific use case.

I have some central DB (publisher) from where I need to replicate few tables to distributed DB's (subscribers) (across different servers).

This is the scenario:

Table in Master DB changes (gets new column). Lets say that this new column is of type "timestamp without timezone", not null, with default value of now().
Because of 1) replication stops working, which is fine. (logical replication target relation <relation> is missing some replicated columns)
After some time, distributed DBs also get the same change (new column).
At this point, replication starts working again and new rows are replicated, including the data from new column (for new rows).
However, for existing rows, the values (timestamps) from new column in master DB table and replicated DB table are different (by time between kicking off the migration on master table and replica tables) and do not get updated up until row changes in Master DB/source table.

My question: is there a way to "force" update new column data replication without truncating table data, updating all rows in source table or similar very time&resource consuming operations?

Also, performance related note - there will be 1-10 million rows in one of tables that is being replicated.

I'm open to all suggestions&ideas.

Thanks.

You would want to add the new column to the subscriber with the appropriate default already in place.  You will have to fish that appropriate value out of the publisher, I don't know of a better way to get it.  After the column is added, you might want to either drop the default, or turn it back to now().  If the only way the table gets inserted/updated is from the publication, then this isn't necessary as the publisher always sends the actual value and so doesn't use the default.  But leaving the unused default in place seems like a recipe for confusion.

begin;
alter table pgbench_accounts add column newcol2 timestamp not null default '2019-03-26 09:44:56.391128';
alter table pgbench_accounts alter newcol2 set default now();
commit;

Note that in version 10, this will still be pretty resource intensive, as adding a column with a non-NULL default requires a complete table re-write the subscriber, just as it did on the publisher. (This was changed in version 11 to use "fast default"). But the intensive work is confined within each server, not the connection between them.


How to count between two users unread messages in SQL I am creating between two users unread message count script in SQL.

My script problem not counting between two users unread messages.
My script problem all users unread messages display as a same results.

Like this

user1 unread message 2
user2 unread message 2
user3 unread message 2

I want to get like this results

user1 unread message 2
user2 unread message 7
user3 unread message 4

Here is my link http://sqlfiddle.com/#!9/299d64/3/0

to find out which user has which number of unread messages use this.

SELECT to_id, COUNT(*)
FROM pm
WHERE readd = 0
GROUP BY to_id

So you filter by readd to get only unread messages. Then you will group by the recievers id so that you can get the number of those messages per each reciever.

If you need a more detailed view how many unread messages came from the same sender, use sommthing like this.

SELECT to_id, from_id, COUNT(*)
FROM pm
WHERE readd = 0
GROUP BY to_id, from_id


SQL Server : parsing with XPath Performance I have a UI that allows a user to select certain data points they want to filter the results by. There are things such as location, department etc. Once they have selected all of the options, I generate an XML string that I pass to my stored procedure.

From there, for locations for example I create a temp table and dump all of the locations that were selected by the user into that table which I then join my data on later.

When running the execution plan, I noticed that each one of the 5 or so queries that are parsing the filter data are costing about 12% which is over 60% of the query just to determine the data we are going to be filtering by.

DECLARE @tmLocations TABLE (
    location VARCHAR (100));
BEGIN
    INSERT INTO @tmLocations
    SELECT ParamValues.x1.value('location[1]', 'VARCHAR(200)')
    FROM   @xml.nodes('data/teammateLocations/locations') AS ParamValues(x1);
END

Is there another method of extracting data from XML or ways to improve the query like above so that it doesn't cost as much to run? Setting up the data is costing more than the actual filtering of the data which is really slowing down performance.

When running the execution plan, I noticed that each one of the 5 or
  so queries that are parsing the filter data are costing about 12%
  which is over 60% of the query just to determine the data we are going
  to be filtering by.

The query costs are based on estimates even in the actual execution plans. They do not tell you how efficient the query actually was.

The estimates are in turn based on statistics and those can be outdated giving you estimates and costs that are wrong.

The estimates for XML queries is always wrong. There is no statistics generated for XML columns and there is certainly no statistics generated for XML parameters or variables.

Have a look at this rather simple XML query.

declare @X xml;

select 1
from @X.nodes('*') as T(X);

Estimated query plan

SQL Server assumes there are 10000 elements in the XML and keeps on guessing from there. Using the nodes() function assumes that 200 of those will be returned. Before that there is a Filter operator that checks if @X is not null limiting the number of estimated rows to 66. Pure guesswork and not influenced at all by what data you actually have in your XML.

To know if a query is good enough you should have a look at things like duration, number of reads and allocated memory. Don't use the estimated cost and please don't use the percentages of individual queries to compare performance.

Your XML query could be improved as Mister Magoo suggests in a comment.

SELECT ParamValues.x1.value('(location/text())[1]', 'VARCHAR(200)')
FROM  @xml.nodes('data/teammateLocations/locations') AS ParamValues(x1);

If you don't specify the text() node, SQL server have to generate a plan that works with mixed content XML, concatenating all the node values from the sub nodes.


impdp - ORA-39000: bad dump file specification While importing a dump file using the command:

impdp manipur/manipur file=D:\ManipurCBSdump\MICB_TABLES.dmp full=yes log = implog.txt

The following error arises:

Connected to: Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production
ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-39088: file name cannot contain a path specification

The syntax for impdp for Oracle 11 is here.  The FILE parameter that you have used should be DUMPFILE. Log should be LOGFILE.  Here is an example from the documentation

impdp hr DIRECTORY=dpump_dir1 DUMPFILE=expfull.dmp LOGFILE=dpump_dir2:expfull.log


Can't alter database roles on a restored database My SSMS Login (using an AD group) is allocated all server roles apart from sysadmin so I have quite a lot of access.

Using this Login, I have restored a database from its .bak file. However, my Login wasn't mapped to a User before this database was backed-up so when it is restored, I am not mapped to any Users. But, if I go into database Properties > Files, the Owner is me (although it is my personal Windows accounts not my AD Group Login I use within SQL).

I'm not sure how this affects things but it means I can still read/write to all the tables in the database, even though my Login is not a User in this database. I therefore then want to create a User on this database for my AD Group Login which works successfully. But then when I try to allocate this new User to any database roles, it says that I do not have permission.

What steps am I missing? What permissions do I need to allocate database roles to a new User on this restored database?

Despite the fact that you're seeing your login as the owner of the database, I don't believe that information is accurate because of the output you got from select user_name() and select is_member('db_owner'). I think SQL Server is misleading you. The permission to read/write every table on the database could be explained by the public database role.

About not being able to add a new member to the roles:

Since you're not a member of db_owner, you won't be able to add a user on a database role. The doc says that:

  to change the membership in a fixed database role you need:

  Membership in the db_owner fixed database role

The problem taking the ownership:

When you tried to take ownership of the database (which is a way of being added to the db_owner role) you got the error

  Cannot find the principal '[domain login]', because it does not exist
  or you do not have permission.

Because by being member of all server roles except sysadmin, as you described, doesn't grant you the TAKE OWNERSHIP or IMPERSONATE permissions and the ALTER AUTHORIZATION for databases doc says those permissions are needed:

  Requirements for the person executing the ALTER AUTHORIZATION statement:

  If you are not a member of the sysadmin fixed server
  role, you must have at least TAKE OWNERSHIP permission on the
  database, and must have IMPERSONATE permission on the new owner login.

You can check if you have those permissions from sys.fn_my_permissions running the following queries while connected to your restored database:

SELECT * FROM fn_my_permissions (NULL, 'DATABASE')
WHERE permission_name = 'TAKE OWNERSHIP';

SELECT * FROM fn_my_permissions (NULL, 'SERVER')
WHERE permission_name = 'IMPERSONATE ANY LOGIN';

If no rows are returned, it means you won't be able to alter the owner of the database and to change that you probably need a sysadmin to execute the ALTER AUTHORIZATION  or grant you enough privileges to do so.

Granting the needed privileges:

GRANT TAKE OWNERSHIP ON DATABASE::YourDatabase TO [YourDomain\YourGroupOrLogin];
GRANT IMPERSONATE ANY LOGIN TO [YourDomain\YourGroupOrLogin];

Note: The login that is going to be the owner of the database cannot be an existent user on that database, or you'll get the following error:

  Msg 15110, Level 16, State 1, Line 1   The proposed new database owner
  is already a user or aliased in the database.

Further reading: the
Chart of SQL Server Permissions contains all the permissions you get from being a member of fixed roles (server and database level).


Best way to perform SQL Server replication over multiple servers? My current setup requires me to use three separate SQL Server 2012 instances (installed on 3 separate machines, separated geographically) and have the data be replicated across all 3. Any changes made in the 7 tables within my database need to be replicated based on where the change was made, for example, if I add a record on Server A, it needs to be added on Server B and Server C. If a record is added on Server B, it needs to be added on Server A and Server C. Same thing with Server C.

From what I've read, Bidirectional Transactional Replication seems to be the best avenue for me to use in SQL Server 2012 Standard. The issue that I'm having is that no one has a very good guide to Bidirectional Transactional Replication that really explains much. I think I've poured over Hilary Cotter and Microsoft's information a million times and I still can't seem to get it to work. The fluff data that they provide in examples just isn't practical and no one explains how to do it between two physical machines; they assume you're using the same machine and just replicating between another instance.

Am I doing something wrong or am I going about this the wrong way?

I had a long answer written on stackoverflow, but then you deleted the question about the same time I was ready to post it. I am not going to put the same effort in again, but in short:

Check out the stairway to replication: http://www.sqlservercentral.com/stairway/72401/
It does not cover bidirectional transactional replication, but it covers the permissions involved in setting up transactional replication. That should allow you to figure out the bidirectional setup as well.
You might want to check out merge replication instead. It was designed for multi-node bidirectional synchronization of data and offers nice conflict resolution handling. However it adds a guid column to each table.


How to change Oracle user password without showing the actual password on screen? Is there a way you can change Oracle user password without showing the actual password on the screen? I understand that you can execute ALTER USER user_name IDENTIFIED BY new_password; but this will show the password on screen.

My organization practices split-password for administrator password and two person would need to key in the password. How do I allow them to key in the password without any of them viewing any portion of the password?

Thank you.

You can do this with the SQL*Plus password command.

To change the current user's password, just type password and you'll get the usual prompt.

$ sqlplus foo/foo123
...
SQL> password
Changing password for FOO
Old password:
New password:
Retype new password:
Password changed

You can also use password username to change another user's password (if you're logged on with sufficient privileges - you need to be dba I believe).

SQL Developer also allows you to do that. Just type password and execute, and you'll get a password change popup. Or expand the "Other users" branch, right-click on the account that needs a change and select the "Edit user" menu.


Calculating Running Average using Over clause in SQL server My table looks like this:

CREATE TABLE [dbo].[TimeSeries](
[LOCID] [int] NOT NULL,
[Date] DateTime,
[YEAR] int,
[MONTH] tinyint,
[RTT] [int]
PRIMARY KEY ( [LOCID] ASC,[Date] ASC)
 )

It contains 12 rows(or MONTHS) per LOCID, per YEAR.

I have the following query:

SELECT
    LOCID,
    [Year],
    [Date],
    AVG(cast(RTT as float)) OVER (
         PARTITION BY LOCID ORDER BY Date
             ROWS BETWEEN CURRENT ROW AND 4 FOLLOWING ) AS avgRTT
FROM TimeSeries

example data:

LOCID   Date    Year    Month   RTT
1   01/01/1982  1982    1   58
1   01/02/1982  1982    2   63
1   01/03/1982  1982    3   34
1   01/04/1982  1982    4   27
1   01/05/1982  1982    5   6
1   01/06/1982  1982    6   4
1   01/07/1982  1982    7   3
1   01/08/1982  1982    8   14
1   01/09/1982  1982    9   22
1   01/10/1982  1982    10  16
1   01/11/1982  1982    11  17
1   01/12/1982  1982    12  44
1   01/01/1983  1983    1   58
1   01/02/1983  1983    2   63
1   01/03/1983  1983    3   33
1   01/04/1983  1983    4   27
1   01/05/1983  1983    5   9
1   01/06/1983  1983    6   0
1   01/07/1983  1983    7   3
1   01/08/1983  1983    8   0
1   01/09/1983  1983    9   6
1   01/10/1983  1983    10  27
1   01/11/1983  1983    11  11
1   01/12/1983  1983    12  48

Now I am trying to execute the above query for a specific MONTH in every year only, calculating the average for the 5 following dates/months/rows. But a WHERE clause to specify the MONTH value will not work because that affects which numbers are averaged.

I need to do this for tens of thousands of LOCID's and millions of rows.

My question is if there is a way to execute the correct moving average for only a specific value for 'MONTH', thus hopefully reducing the processing time a number of times...

example desired output:

LOCID   Date    Year    Month   avgRTT
1   29952   1982    1   37.6
1   30317   1983    1   38

Any pointers will be greatly appreciated.

You can apply your final filter on @dekade after computing the running averages.

In order to reduce the number of rows that need to be processed for the running averages, you can apply an earlier filter on [dekade] IN (@dekade, (@dekade+1)%36, (@dekade+2)%36) to ensure that you are processing the minimal amount of rows but still including all the rows that are necessary to including the following 11 rows in the running average. (The only reason for the % 36 is to handle values at @dekade that fall at the end of the year.)

This will still result in a table scan given your current table structure, but at least the rows can be filtered out earlier on in the query plan.

DECLARE @dekade TINYINT = 1
SELECT *
FROM (
    SELECT
        LOCID,
        [Year],
        [Date],
        [dekade],
        AVG(cast(RTT as float)) OVER
            (PARTITION BY LOCID ORDER BY Date
                ROWS BETWEEN CURRENT ROW AND 11 FOLLOWING) AS avgRTT
    FROM TimeSeries
    -- If you want to limit the rows that are use when computing the running average,
    -- you can make sure that only the desired @dekade plus the following two @dekades
    -- (which may be needed to get the following 11 rows) are used for each year
    WHERE [dekade] (@dekade, (@dekade+1)%36, (@dekade+2)%36)
) x
-- Filter your results be @dekade after computing the running average
WHERE x.[dekade] = @dekade

It would be helpful to post some sample data so that we can actually run the query though.


Can a temporary table be shared across sessions? I have revised a procedure that builds a dynamic SQL statement to create and populate a temporary table. The syntax was something like this:

...'create #temp_' + CAST(GETGUID() AS VARCHAR(36)) ...

I asked a colleague if he knew why a unique identifier was being concatenated to the table name. He told me that in the past there were cases where temporary tables were shared across sessions and the data was messed up.

I know this is not possible since MSDN clearly states:

  You can create local and global temporary tables. Local temporary
  tables are visible only in the current session, and global temporary
  tables are visible to all sessions.

Now (remembering the case) some colleagues think we should use the syntax above as a good practice but I do not like it and need proof there is no such bug or if it was it is already fixed.

Could anyone with experience in SQL Server 2005/2008 tell me if there ever was such case?

Local table tables should never be shared across sessions. As you've pointed out, they are only visible to the session that created them and are destroyed when the creating session is terminated, or you drop them.

Global temporary variables are visible to all sessions, but you'd need to define them with the double hash i.e. ##temp, for them to be defined as global.

As for putting a suffix on the table name when creating it, you're wasting your time, as SQL Server does that anyway.

CREATE TABLE #temp (Name VARCHAR(20));

USE tempdb;
GO

SELECT name FROM sys.tables WHERE name LIKE '#temp%';

For me, this returns

#temp_______________________________________________________________________________________________________________00000000004C

However, as Aaron Bertrand pointed out in this answer:

  if you call everything #temp, or #t, or #x, then it's possible that such a table already exists from an outer scope prior to calling the procedure.

In this case, you could be sharing the temp table between a top-level process and a number of different procedures that it calls within that process. This of course could lead to data corruption, depending on how you defined and constructed your temporary table.


Sql Server 2017 Developers Edition install failure during configuration When installing SQL Server 2017 Developers Addition, the install would get to the point where it needed to connect to the service to configure the users (and whatever else it does), and it would fail to connect.

This is my development machine, and I had uninstalled other versions of SQL Server (2016, 2014, 2012, 2008 R2) previously and rebooted.  I tried removing C++ 5 and 7 shared component.  I uninstalled SSMS.  I completely deleted Sql Server in Program Files and Program Files (x86). I could not figure it out.

I never solved it, but here is how I worked around it.

I don't know if it was necessary, but I started with SSMS and all previous versions of Sql Server uninstalled, as well as the Visual Studio C++ shared components (versions 2015 and 2017), and had deleted the Sql Server directories.

I was installing the database as the user I wanted to be the admin user.  When I ran the install, I right clicked and ran as admin.  When the installer asked for the admin user, I used the "Add Current User" button to add myself.

It still failed when trying to connect to configure the database.  However, the Configuration Manager had already been installed (in the Start Menu).  So I ran that, right clicked on the Service to get to properties, found the command line switches, and added -m to start the service in single user mode.  Because I had ran the installed as admin, that invokes the backdoor to allow a local admin user to connect without checking credentials, so the install script could configure the database and add the users. After installation was complete, I removed the -m switch using the configuration manager, restarted the service, and had a running instance of the database.

I hope nobody ever has to go through that again, but if you do, that's what worked for me.


Restore Database without taking RDP I have a DB server 192.168.X.X, Now due to some Issue with the domain user we are unable to get the RDP of that server. but we can get the DB connected from other server which is there on the network.
Now I want to restore the database on 192.168.X.X, so Is it possible to restore the DB without taking the RDP of the server.
Any help will be appreciated.
I tried this step but with no luck:  Link
I tried to Restore the DB with T-SQL code as well:
Use master

RESTORE DATABASE TEST
FROM DISK = '\\192.168.X.X\New folder.bak'

But it gives me an error.
Msg 3201, Level 16, State 2, Line 3
Cannot open backup device '\192.168.X.X\New folder.bak'. Operating system error 1326(The user name or password is incorrect.).
Msg 3013, Level 16, State 1, Line 3
RESTORE DATABASE is terminating abnormally.

Assuming you have access and sufficient rights to the database on the SQL server via SSMS.
Create a shared folder on a different network server 192.I.Have.Access.  Grant the SQL engine and agent accounts read and write access to the shared folder.
You can take backups to the share (with write access) and you can restore backups from the share (with read access).  Direct access to folders on the server the SQL instance is installed on is not required.
BE-AWARE If someone besides you is responsible for SQL backups on the server.  Taking backups and/or restoring database can and probably will break the backup chain.  This will make disaster recovery for some period impossible, understand the implications to disaster recovery in your organization before making a one off backup or doing a restore.

If the only copy of the backup you want to restore is on the server in a folder you can not access; Congratulations you just learned why it is important copy backups to a safe location on a different server for disaster recovery.  I hope the lesson has not been overly expensive.


Find points within a distance using MySQL I have a MySQL table

Users(State, City, Zip, Lat, Long)

I would like to get a list of user who are inside the circle or square of a given latitude and longitude with given distance. For example my input Lat= 78.3232 and Long = 65.3234 and distance = 5 kilometers. I would like to get the list of users who are inside 5 kilometers distance from the point 78.3232 and 65.3234. Is it possible to solve this with single query?

Using GIS

Assuming MySQL 8+

SELECT u.*,
  ST_Distance(
    ST_SRID(Point(65.3234, 78.3232),4326),
    ST_SRID(Point(u.lon,u.lat),4326)
  )
FROM Users AS u
WHERE ST_Intersects(
  ST_Buffer(ST_SRID(Point(65.3234, 78.3232),4326), 5000),
  ST_SRID(Point(u.lon,u.lat),4326)
);

If you're not using MySQL 8+ you can't use ST_SRID(geom,SRID). Also, if you store GEOMETERY types on the table, rather than silly lat and long columns, this looks a lot cleaner.

SELECT u.*,
  ST_Distance(
    ST_SRID(Point(65.3234, 78.3232),4326),
    u.geom
  )
FROM Users AS u
WHERE ST_Intersects(
  ST_Buffer(ST_SRID(Point(65.3234, 78.3232),4326), 5000),
  u.geom
);


Combine two event tables for multiple timelines into a single result set This question is an extension to a question I've previously asked that was overly simplified. The more accurate example is demonstrated in this SQLFiddle, where I demonstrate a working (but slow) solution, followed by my attempt to adapt the previous answer to the actual problem.

The actual problem comes because the two tables contain events for multiple timelines.

CREATE TABLE foo (ts int, id text, foo text);
INSERT INTO foo (ts, id, foo)
VALUES
    (1, 'A', 'Lorem'),
    (1, 'B', 'ipsum'),
    (4, 'B', 'dolor'),
    (5, 'A', 'sit'),
    (8, 'A', 'amet'),
    (8, 'B', 'consectetur');

CREATE TABLE bar (ts int, id text, bar text);
INSERT INTO bar (ts, id, bar)
VALUES
    (1, 'A', 'adipiscing'),
    (5, 'B', 'elit'),
    (6, 'A', 'sed'),
    (9, 'B', 'do ');

Each table has events for timelines 'A' and 'B'. The goal is to combined the results in to a single result set showing the "state" of each timeline. The two timelines are orthogonal.

ts  id  foo         bar
1   A   Lorem       adipiscing
5   A   sit         adipiscing
6   A   sit         sed
8   A   amet        sed
1   B   ipsum       (null)
4   B   dolor       (null)
5   B   dolor       elit
8   B   consectetur elit
9   B   consectetur do

In addition to the solution of the simple case, add a PARTITION clause to the window functions in the inner query, to get group numbers per partition (per "timeline"). Combine group numbers with the respective timeline (id in your example) keep partitions separate in the second step:

SELECT id, ts
     , min(foo) OVER (PARTITION BY id, foo_grp) AS foo
     , min(bar) OVER (PARTITION BY id, bar_grp) AS bar
FROM (
   SELECT id, ts, f.foo, b.bar
        , count(f.foo) OVER (PARTITION BY id ORDER BY ts) AS foo_grp
        , count(b.bar) OVER (PARTITION BY id ORDER BY ts) AS bar_grp
   FROM   foo f
   FULL   JOIN bar b USING (id, ts)
   ) sub
ORDER  BY 1, 2;

Result as requested (except with id first).
SQL Fiddle

Your attempt to adapt the previous solution was very close. It didn't work because of PARTITION BY f.id / PARTITION BY b.id instead of PARTITION BY id. You really want the combined id to include missing rows in the result - that's where the last non-null value has to be filled in for the missing (NULL) value.

If performance is your paramount requirement consider a server-side function like demonstrated in the previous answer.


Postgres Query not using Index but faster with Index Sorry if this seems to be a duplicate question. I'm using Postgres 11.6 on AWS RDS. I have 2 tables:
CREATE TABLE public.e
(
    id character varying(32) COLLATE pg_catalog."default" NOT NULL,
    p_id character varying(32) COLLATE pg_catalog."default" NOT NULL,
    CONSTRAINT e_pkey PRIMARY KEY (id)
)
WITH (
    OIDS = FALSE
)
TABLESPACE pg_default;

CREATE TABLE public.ed
(
    e_id character varying(32) COLLATE pg_catalog."default" NOT NULL,
    <other columns + primary key>
)
WITH (
    OIDS = FALSE
)
TABLESPACE pg_default;

I have an index on ed.e_id:
CREATE INDEX ix_ed_e_id
    ON public.ed USING btree
    (e_id COLLATE pg_catalog."default" ASC NULLS LAST)
    TABLESPACE pg_default;

When I run this query:
select *
from ed, e
where e.id = ed.e_id
and e.p_id = '5c7cae8df6d10f1064b2eaf5';

(Problem persists when using from ed inner join e on e.id = ed.e_id)
The explain analyze plan is:
Gather  (cost=1136.68..141235.01 rows=28320 width=311) (actual time=0.456..871.155 rows=102709 loops=1)
  Workers Planned: 2
  Workers Launched: 2
  ->  Hash Join  (cost=136.68..137403.01 rows=11800 width=311) (actual time=0.241..688.095 rows=34236 loops=3)
        Hash Cond: (ed.e_id = e.id)
        ->  Parallel Seq Scan on ed ed  (cost=0.00..133210.10 rows=1544610 width=218) (actual time=0.005..314.524 rows=1235269 loops=3)
        ->  Hash  (cost=135.67..135.67 rows=81 width=93) (actual time=0.125..0.126 rows=81 loops=3)
              Buckets: 1024  Batches: 1  Memory Usage: 19kB
              ->  Bitmap Heap Scan on e e  (cost=4.91..135.67 rows=81 width=93) (actual time=0.045..0.097 rows=81 loops=3)
                    Recheck Cond: ((p_id)::text = '5c7cae8df6d10f1064b2eaf5'::text)
                    Heap Blocks: exact=31
                    ->  Bitmap Index Scan on ix_e_p_id  (cost=0.00..4.89 rows=81 width=0) (actual time=0.035..0.035 rows=81 loops=3)
                          Index Cond: ((p_id)::text = '5c7cae8df6d10f1064b2eaf5'::text)
Planning Time: 0.329 ms
Execution Time: 877.804 ms

With a Parallel Seq Scan on ed for the ed.e_id match.
When I SET SESSION enable_seqscan = OFF, the explain plan is:
Nested Loop  (cost=0.72..395895.14 rows=28320 width=311) (actual time=0.037..60.068 rows=102709 loops=1)
  ->  Index Scan using e_pkey on e e  (cost=0.29..917.61 rows=81 width=93) (actual time=0.019..4.995 rows=81 loops=1)
        Filter: ((p_id)::text = '5c7cae8df6d10f1064b2eaf5'::text)
        Rows Removed by Filter: 10522
  ->  Index Scan using ix_ed_e_id on ed ed  (cost=0.43..4757.83 rows=11844 width=218) (actual time=0.013..0.334 rows=1268 loops=81)
        Index Cond: (e_id = e.id)
Planning Time: 0.273 ms
Execution Time: 64.675 ms

A whole order of magnitude faster (877ms vs 64ms)! I tried VACUUM ANALYZE ed, but that didn't help. I even tried changing e.id & ed.e_id to UUID type, but that didn't help either.
How can I convince Postgres to use the ix_ed_e_id index without setting enable_seqscan to off?

It seems that PostgreSQL is overestimating the cost of an index scan, which leads it to prefer a hash join over a nested loop join.
There are two parameters that tell PostgreSQL about the hardware and influence its estimate of the cost of an index scan:

random_page_cost: the greater that is compared to seq_page_cost, the more expensive PostgreSQL will estimate an index scan's random I/O compared to sequential I/O. So you can lower that parameter to encourage index scans.

effective_cache_size: this tells the optimizer how much memory is available for caching data. If the value is high, it will assume that indexes are cached and price index scans lower.

Perhaps adjusting these parameters will change PostgreSQL's mind here, although the cost estimates are suspiciously far apart.


How to Create Snapshot Backups in MySQL I've seen a lot of people talking about snapshots backup, even I'm reading a book about mysql but nobody explain the code how to make a snapshots backup.  This is what the book says:

flush tables with read lock
take a snapshot
unlock tables;

THAT's SO NULL!....

Can someone give me MORE information about the snapshots in MySQL?

I have written posts of this nature before

How can I optimize a mysqldump of a large database?
recover a single mysql database on a busy master-slave replicated system
Set up a MySQL slave using mysqldump to get the initial data

You will find code samples and additional advice on its usage

I shy away from Snapshots as done by LVM if

90%+ Data is InnoDB
innodb_file_per_table is disabled
All Data Exceeds 200GB
Heavy Write, Heavy Read

I wrote about this before : Ibdata usage and Recommendations?

While my personal preference is mysqldumps, if you have a small data volume, LVM snapshots would be just fine. If you are running MySQL 5.0/5.1, I would recommend running

SET GLOBAL innodb_max_dirty_pages_pct = 0;

to flush up to 99% of the InnoDB Buffer Pool before performing a file-system snapshot. Then, set it back to

SET GLOBAL innodb_max_dirty_pages_pct = 90;

This is not required for MySQL 5.5

An in-between solution would be Percona's XtraBackup. Percona XtraBackup is free, open-source, and GPL


Unable to Generate Scripts for Publications Version is SQL 2008 R2 Enterprise
I am attempting to document our replication configuration.

Replication is running and working fine:

In SSMS I right click on a publication and select Generate Scripts.
I receive the error

SQL Server cannot generate the SQL script.
Additional Information:
An exception occurred while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.ConnectionInfo)
Could not find stored procedure ''. (Microsoft SQL Server. Error: 2812)

The T-SQL statement EXEC sp_helpsubscription 'SubscriptionName'
returns the following:
Msg 2812, Level 16, State 62, Procedure sp_MSrepl_helpsubscription, Line 537 [Batch Start Line 268]
Could not find stored procedure ''.

Any ideas on what I could check?

DECLARE @distributor sysname

DECLARE @distributiondb sysname
DECLARE @distproc NVARCHAR (255)
DECLARE @publisher sysname

SELECT @publisher = @@SERVERNAME

EXEC sys.sp_MSrepl_getdistributorinfo @publisher = @publisher,
@rpcsrvname = @distributor OUTPUT,
@distribdb = @distributiondb OUTPUT

select @distributor, @distributiondb

SELECT @distproc = QUOTENAME (@distributor) + '. ' + QUOTENAME (@distributiondb) + '.dbo.sp_MSenumdistributionagentproperties'

select @distproc ---what do you get here?

---Line 537
EXEC @distproc @publisher = @publisher,
@publisher_db = @dbname,
@publication = @orig_publication,
@show_security = 1

The exec @distproc section is roughly at Line 537 of sp_MSrepl_helpsubscription. It's building the proc call and should be something like [SeverName].distribution.dbo.sp_MSenumdistributionagentproperties

From the error,  it appears that @distproc variable is not getting set properly, or at least getting set to an empty string.

Why? That's hard to know without more information or being able to test on your system. Some unusual setup with the distributor, perhaps? But this will hopefully point you in the right direction so you can walk through it. (Run that code on the Database that is being published).


Is there any trick to Resize LVM online that raw by UDEV rule without filesystem? Here is the Logic Volume for example:

   lvcreate   -L2G -n Lv_OraD1 vg_db
   lvcreate -L180G -n Lv_OraD2 vg_db

   lvdisplay /dev/mapper/Lv_OraD*

UDEV rule to raw the LV:

   vi /etc/udev/rules.d/60-raw.rules

   ACTION=="add", ENV{MAJOR}=="253",ENV{MINOR}=="3", RUN+="/bin/raw /dev/raw/raw1 %N"
   ACTION=="add", ENV{MAJOR}=="253",ENV{MINOR}=="4", RUN+="/bin/raw /dev/raw/raw2 %N"
   ACTION=="add",KERNEL=="raw[1-9]",OWNER="grid",GROUP="dba",MODE="660"

   start_udev
   raw -qa

ASM Diskgroup maybe like this:

   CREATE DISKGROUP DATA1 NORMAL REDUNDANCY
   FAILGROUP Fg1 DISK '/dev/raw/raw1' SIZE 2048M ,'/dev/raw/raw2' SIZE 184320M;

The effect: the size up 2G of DISKGROUP DATA1 cannot be used.
so any tricks to achieve this result online?

   lvreduce -L90G /dev/mapper/Lv_OraD2
   lvextend -L90G /dev/mapper/Lv_OraD1

Thanks.

First find the disk names and paths:

select name, failgroup, path, os_mb, total_mb, free_mb from v$asm_disk;

After you found the ASM generated name for the disk you want to shrink, reduce it in ASM to the desired size (I will assume it is called DATA1_0002:

alter diskgroup data1 resize disk DATA1_0002 size 90G;

This starts a rebalance, wait until the rebalance completes, you can view its progress as:

select * from v$asm_operation;

After the rebalance, run this query again:

select name, failgroup, path, os_mb, total_mb, free_mb from v$asm_disk;

There you should see the original 184320 OS_MB, and 92160 TOTAL_MB for the disks you have just shrinked. Next use lvreduce to shrink the LV:

lvreduce -L 90G vg_db/Lv_OraD2

Make sure you do not reduce the size of LV below the ASM disk size TOTAL_MB.

Once it is done, increase the other LV:

lvextend -L 90G g_db/Lv_OraD1

Go back to ASM, run this again:

select name, failgroup, path, os_mb, total_mb, free_mb from v$asm_disk;

Here the OS_MB for the first disk should increase to 92160, but TOTAL_MB still remain 2048.

The final step is to increase the first disk size in ASM:

alter diskgroup data1 resize all;

The above will resize all ASM disks in the diskgroup up to OS_MB. This triggers another short rebalance, and when that finished, you should see 92160 for OS_MB and TOTAL_MB for both disks.


Can this be accomplished with Oracles Fine Grained Auditing? We need to audit when a table is accessed (either select, update or delete) and I know Oracles Fine Grained Auditing can handle that. The issue we are having is that all of our users use the same oracle account to log in and we need to audit WHO, using the users username on the application, did the action on this table.  What I was thinking is that we can just pass the users id in as part of the select statement ( select 'bob', col1, col2...) and that could possibly be accessed with Oracles Fine Grained Auditing.

Does anyone have any experience in doing something like this or if it is even possible.  If not, any ideas on how we can accomplish this?

Thanks!

Forgot to mention that this is in Oracle 10G

Get the client to call dbms_session.set_identifier('PHILTEST');. This will then be set in the CLIENT_ID audit field. Obviously you'l need to call it first from the client whenever a new connection is pulled from the pool.

For example:

PHIL@PHILL11G2 > conn / as sysdba
Connected.
Loading glogin.sql

Session altered.

SYS@PHILL11G2 AS SYSDBA> audit all by phil by access;

Audit succeeded.

SYS@PHILL11G2 AS SYSDBA> AUDIT SELECT TABLE, UPDATE TABLE, INSERT TABLE, DELETE TABLE BY ACCESS;

Audit succeeded.

SYS@PHILL11G2 AS SYSDBA> truncate table aud$;

Table truncated.

SYS@PHILL11G2 AS SYSDBA> TRUNCATE TABLE fga_log$;

Table truncated.

SYS@PHILL11G2 AS SYSDBA> conn phil/phil

PHIL@PHILL11G2 > exec dbms_session.set_identifier('PHILTEST');

PL/SQL procedure successfully completed.

PHIL@PHILL11G2 > select count(*) from fgatest;

  COUNT(*)
----------
     1

PHIL@PHILL11G2 > conn / as sysdba

SYS@PHILL11G2 AS SYSDBA> select CLIENT_ID from dba_audit_trail;

CLIENT_ID
----------------------------------------------------------------
PHILTEST
PHILTEST

2 rows selected.

SYS@PHILL11G2 AS SYSDBA>


Extract ISP name from hostname I want to get the high-level domain name (not sub-domain) from the hostname.
The idea is to "guess" the ISP name from the user's hostname. Ideas?

// no ISP when it's an IP
115.20.106.216 => null

// in these cases the domain name should be the ISP name
108-81-122-61.lightspeed.frokca.sbcglobal.net => sbcglobal.net
61-22-86-252.static.trlk.ca.charter.com => charter.com
c-28-8-222-102.hsd1.ca.comcast.net => comcast.net
CPE-128-122-18-181.hdbq1.win.bigpond.net.au => bigpond.net.au
dhcp-108-168-82-106.cable.user.start.ca => start.ca
ip68-100-26-186.dc.dc.cox.net => cox.net
ip-108-85-1-22.web.vodafone.de => vodafone.de
pool-68-86-186-222.washdc.fios.verizon.net => verizon.net
s51865a4f.adsl.online.nl => online.nl

// in these cases it's not very obvious
c-208-168-15-86.wifi.ties.k12.mn.us => k12.mn.us ?
cpe-66-181-81-222.tx.res.rr.com => res.rr.com ?

Note: All IP addresses are random.
Something like bigpond.net.au is 3-level. The main domain is sometimes 2-level and sometimes 3-level.

I found this tool which is doing exactly what I asked for
https://github.com/rvflash/publicsuffix-sql
It uses publicsuffix.org which maintains a list of public suffixes such as .com, .co.uk and pvt.k12.ma.us.
Using the function provided by the tool, we can simply do something like this:
select DOMAIN('115.20.106.216'); -- null
select DOMAIN('cpe-45-37-121-56.nc.res.rr.com'); -- rr.com
select DOMAIN('CPE00fc8d22x-CM023d22f4f0.cpe.net.cable.rogers.com'); -- rogers.com
select DOMAIN('c-208-168-15-86.wifi.ties.k12.mn.us'); -- k12.mn.us
select DOMAIN('CPE-128-122-18-181.hdbq1.win.bigpond.net.au'); -- bigpond.net.au