Disk sizing considerations for 'tempdb' - Growing substantially two times a year I have an HR software on which an employee of the HR department runs long and complex analyses and calculations about two times in a year. Because of this the tempdb is growing to 500GB and even more.

What is a good disk sizing solution? Because the rest of the year tempdb is not so big.

Details

The SQL Server version and edition of relevance are 2017 Standard.
tempdb and databases are on the same partition D: and Log files are on a different partion E:
The HR database itself has around 78GB.

The "real" answer is: work with HR and/or their software vendor to fix or improve their twice-annual jobs so they don't do that.

Assuming that's not possible, you're left with two three lousy options:

Leave tempdb huge all year long.
Leave enough empty space on disk so that tempdb can grow huge, then shrink it back down immediately afterwards.
Coordinate with systems to add disk space just before processing, then shrink tempdb afterwards and recover the disk space.

1 and 2 are pretty much functionally identical, although I'd tend toward 2 (just in case the vendor got its act together sometime in the last 6 months and fixed their processing job without you knowing it).

Exactly how to undertake #3 would depend on lots of technical details that will vary by your environment. One method I'd consider would be:

Before processing, add a large temporary drive (say z:) to the server
Do an ALTER DATABASE tempdb MODIFY FILE for each tempdb file, specifying both the new location on z: and a larger file size
Restart SQL for that change to take effect (SQL re-creates tempdb every time it restarts)
Let tempdb use up as much space as it needs on z: during processing
After processing is done, re-execute the ALTER DATABASE tempdb MODIFY FILE to redirect tempdb back to the original drive (and the original smaller size)
Restart SQL again for the change to take effect
Drop/remove the now-empty disk

In our environment, this is preferable because (with our particular configuration of SAN/virtualization), it happens to be vastly easier to add and later remove a drive than it is to expand an existing drive and (especially) to shrink an existing drive. (If I remember right, shrinking an existing drive literally requires presenting a whole new disk and migrating data). But your mileage may vary depending on your configuration.


Converting 3 Master + 1 Slave to 4 Master - Any advice? so we have a beautiful 3 master 1 slave cluster running.

we would like to convert this to a 4 master cluster.

here is how the topology currently looks

s1

enslaved by s3

s2

enslaved by s1

s3

enslaved by s2

As it stands now, all servers are fully in sync and used by a live running application.

Question Part 1: What mysql commands need to be executed on each of the servers to reset the current binlog position to 0 and sync this action to whatever server is enslaved by the one where the commands are executed. Just to clean things up prior to making the switch.

Question Part 2: We can easily change the source code running to ignore one of the servers. Each of the nodes can support the whole application running live, so we can take each of the servers down one by one. What commands need to be executed to basically do the following:

Pause updating the node from a master. But make it so when switching to the new master, the syncing of the database will continue as left off. So I am assuming that ultimately 2 servers will need to go down at this point. One of which being the slave that's switching from slave to master/slave. I should also mention, we have the ability, and use it often, to basically pause the inserting / updating of 'the main table'. Most of the other data is non-critical items such as logging, etc. So even if a few rows (or even some full tables) are lost due to the servers being out of sync its OK.

So that pretty much sumarizes it. We want to convert a running and live 3 + 1 combo to a 4 combo. All 4 servers are in sync, we have a few tools available on the code side to make taking 1 to 2 servers down at a time safe.

We should be able to do this without any downtime.

Any and all advice appreciated.

Thank you kindly.

Here is the topology you just described

+-------------+
|             ^
|             |
V             |
M1 --> M2 --> M3
|
|
+----> S1

You would like to slip S1 as a Master into the Replication Ring so that it looks like this:

+---------------------+
|                     ^
|                     |
V                     |
M1 --> S1 --> M2 --> M3

Essentially, you only have to prep S1 to a Master and Point M2 to receive binary log entries from S1.

OK Here we go

STEP 01) Point your application at M3

STEP 02) Prep S1 to be a Master

set the server_id as a different number from M1, M2, M3
set log-slave-updates in my.cnf like you did for M1, M2, M3
enable binary logging on S1 the same way you enabled it on M1, M2, M3
restart mysql on S1

STEP 03) run STOP SLAVE; on M1

STEP 04) run SHOW SLAVE STATUS\G on S1 and M2

Make sure Seconds_Behind_Master is 0 on S1 and M2

STEP 05) run SHOW MASTER STATUS; on S1 (Record the binary log and position)

STEP 06) run this on M2

STOP SLAVE;
CHANGE MASTER TO master_host='IP Address of S1',
master_post=3306,
master_user='repluser',
master_password='replpass',
master_log_file='XXXX'
master_log_pos=YYYY;
START SLAVE;
SHOW SLAVE STATUS\G

where XXXX is the binary log from STEP 05 and YYYY is position from STEP 05

If the SHOW SLAVE STATUS\G says Yes for Slave_IO_Running and Slave_SQL_Running then you have achieved this:

+---------------------+
                      ^
                      |
                      |
M1 --> S1 --> M2 --> M3

STEP 07) run START SLAVE\G on M1

STEP 08) run SHOW SLAVE STATUS\G on M1, S1, M2, M3

Where Seconds_Behind_Master is 0 on all the servers...

STEP 09) Point you application to other servers as desired

Any questions ???

If none

Give it a Try !!!

Your mission, should you decide to accept this, is to practice this is a Dev/Staging Environment and make sure you trust this algorithm before doing this in Production.

In the event your data is caught or killed, the DBA StackExchange and I will disavow any knowledge of your actions.


Large dataset of 400+ million rows to compare I am trying to solve this logic problem but my current MySQL schema is giving me a very poor performance with a large dataset.
I have a table which contains almost half a billion telephone records. They are called the Do Not Call list or simply the DNC.
When I start my campaign to dial numbers, I have to check against this list to see if my phone numbers are there in the list or not. If yes, then scrub from the list and do not dial that number.
Here is my dnc table:
CREATE TABLE `dnc_contacts` (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
  `group_id` INT(11) NOT NULL,
  `contact` VARCHAR(45) NOT NULL,
  `status` TINYINT(1) NOT NULL DEFAULT '1',
  `created_date` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  `admin` TINYINT(1) NOT NULL DEFAULT '0',
  `updated_date` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`,`group_id`),
  KEY `fk_dnc_contact_dnc_group1_idx` (`group_id`),
  KEY `idx-group_id-contact` (`group_id`,`contact`),
  CONSTRAINT `dnc_contacts_copy_ibfk_1` FOREIGN KEY (`group_id`) REFERENCES `dnc_groups` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

The query I am using to compare the dnc table is:
UPDATE contacts c LEFT JOIN dnc_contacts d ON d.contact = c.contact AND d.group_id = '9' SET campaign_id = '525', call_status = 1, active_call = 0, dnc_contact = if(d.contact IS NULL, 0, 1) WHERE phonebook_id IN (920)

My contacts table looks like:
CREATE TABLE `contacts` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `phonebook_id` int(11) NOT NULL,
  `campaign_id` int(11) DEFAULT NULL,
  `contact` varchar(45) NOT NULL,
  `status` tinyint(1) unsigned NOT NULL DEFAULT '1',
  `admin` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `call_status` tinyint(1) unsigned NOT NULL DEFAULT '1',
  `active_call` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `test_call` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `dnc_contact` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `v_node_name` varchar(45) DEFAULT NULL,
  `updated_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `created_date` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`,`phonebook_id`),
  KEY `fk_contact_phonebook1_idx` (`phonebook_id`),
  KEY `idx-phonebook_id-contact` (`id`,`contact`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Running this query for small datasets with 100K to 200K contacts works fine, but with large dataset with more than 300 million records kills the performance. Can't think of a better way to achieve the goal.
Appreciate any advice to improve the logic/schema/query.

The contact is not unique. I can have the same contact but under a different group or a phonebook. It gets even messier when there are multiple phonebooks. active_call and call_status keep on changing as the numbers are dialled.

Isn't contact unique in both tables?  If so, get rid if id and have PRIMARY KEY(contact).  There are multiple reasons why this is likely to speed things up.

active_call and call_status sound like things that are changed often?  That interferes with the setting of the dnc_contact, especially when done in large chunks.

I can think of no good reason to even have dnc_contact in contacts.  It is redundant information.  That is, when you are about to make a call, do the JOIN to verify that the phone number is not in dnc_contact.

If you really need to do the UPDATE, here are tips on how to do it is a relatively non-invasive way -- bu chunking.


Causes for pg_class locks except DROP TABLE I do experience the following situation on a PostgreSQL 11:

A transaction with unknown statements creates an ACCESS SHARE lock on pg_class.
Another connection wants to execute DROP TABLE which causes an ACCESS EXCLUSIVE lock on pg_class. This operation is blocked because operation #1 still holds the lock.
Following that any new connection to the DB is waiting forever, because it needs to access pg_class as well.

The only way to proceed is to either kill operation #1 or #2.

While I can reproduce the situation with manual locking the tables, I do not find an explanation what causes the pg_class lock on operation #1. That is, pg_stats_activity only shows a SELECT as current query which is idle in transaction. I suspect this not being the statement creating the lock, however.

Thus, any input on what could cause ACCESS SHARE locks on pg_class except DROP TABLE is highly appreciated.

Update atm it is also unclear why DROP TABLE wants to lock pg_class.

The situation is caused by a long-running transaction which does a SELECT on pg_class first. This causes the ACCESS SHARE lock.

Typically, DROP TABLE does not cause an ACCESS EXCLUSIVE lock, but in that case there is a PG extension loaded which uses more restrictive locking.

Solution for the current situation is to move the SELECT on pg_class out of the long-running transaction.


Delete databases from oracle 12c I want to delete multiple databases and its entries. These instances are not getting started. I want to delete data files also.

EDIT: I am thinking delete all data files. Please help on below concern.

Shall I delete data files manually? Will it cause any problem to my working database instance?

The easiest and safest way to delete a database is using the RMAN DROP DATABASE command.

If on Linux, set up your ORACLE_HOME and ORACLE_SID environment variables appropriately then start the database(s) using startup mount exclusive restrict.  Then drop it using RMAN's DROP DATABASE.  If you want to delete the backups/contents of the FRA as well, then use DROP DATABASE INCLUDING BACKUPS.

You could also use the GUI (DBCA - Database Creation Assistant), but this may be trickier depending on the environment.


Paging through data that is frequently changing When users are browsing pages of data where the data keeps changing, what is the best way to control this so the user experience is not impacted?

If I browse a catalog of 100 records with 10 per page, that is 10 pages; however, as new records are added or removed it would impact the page number the user could be on in the next page load.

I am thinking the first query would be to get a list of all ids, store those in a separate table and then do a join on that.  This way new records or ones marked as deleted would not affect this.  After a few hours I would delete these ids from the new table so they are not hanging around forever.

Is there a better way to achieve this?  Consider this just a standard relational db structure using 3rd normal form.

You could use SQL Server's built-in row-versioning functionality to ensure you only see rows that haven't been touched since the start of your session.  This is test code, so we'll run it in tempdb:

USE tempdb;

IF OBJECT_ID(N'dbo.CatalogList', N'U') IS NOT NULL
DROP TABLE dbo.CatalogList;
CREATE TABLE dbo.CatalogList
(
    ListKey int NOT NULL
        IDENTITY(1,1)
        CONSTRAINT PK_CatalogList
        PRIMARY KEY CLUSTERED
    , rv rowversion NOT NULL /* rowversion is a binary(8) value */
    , ItemName varchar(30) NOT NULL
);

The table above is a very simplified product catalog, with a rowversion column named rv.  rowversion is a data type that exposes automatically generated, unique binary numbers within a database. rowversion is generally used as a mechanism for version-stamping table rows. The storage size is 8 bytes. The rowversion data type is just an incrementing number and does not preserve a date or a time.  See rowversion-transact-sql for more details.

The rv column can be used to eliminate newly added/updated rows from being seen my the client.  To show how that works, we'll add some initial data to the catalog list, and create a stored procedure to give us paginated results:

INSERT INTO dbo.CatalogList (ItemName)
VALUES ('Item1')
    , ('Item2');

GO
IF OBJECT_ID(N'dbo.GetCatalogItems', N'P') IS NOT NULL
DROP PROCEDURE dbo.GetCatalogItems;
GO
CREATE PROCEDURE dbo.GetCatalogItems
(
    @rowversion binary(8) = NULL
    , @PageNumber int = 1
    , @PageSize int = 100
)
AS
BEGIN
    SET NOCOUNT ON;
    IF @rowversion IS NULL SET @rowversion = @@DBTS;
    SELECT cl.*
    FROM dbo.CatalogList cl
    WHERE cl.rv <= @rowversion
    ORDER BY cl.ListKey
    OFFSET @PageSize * (@PageNumber -1) ROWS
    FETCH NEXT @PageSize ROWS ONLY
    RETURN @rowversion;
END
GO

Next, we'll run the stored procedure to show the first result:

DECLARE @rv binary(8);
EXEC @rv = dbo.GetCatalogItems @PageNumber = 1, @PageSize = 1;

@rv is used to save the current rowversion value, for use in subsequent calls.

The results:

╔═════════╦════════════════════╦══════════╗
║ ListKey ║ rv                 ║ ItemName ║
╠═════════╬════════════════════╬══════════╣
║ 1       ║ 0x00000000000007EE ║ Item1    ║
╚═════════╩════════════════════╩══════════╝

Now, we'll add another row to the table, and show the table contents:

INSERT INTO dbo.CatalogList (ItemName)
VALUES ('Item3');

SELECT *
FROM dbo.CatalogList;

Table contents:

╔═════════╦════════════════════╦══════════╗
║ ListKey ║ rv                 ║ ItemName ║
╠═════════╬════════════════════╬══════════╣
║ 1       ║ 0x00000000000007EE ║ Item1    ║
║ 2       ║ 0x00000000000007EF ║ Item2    ║
║ 3       ║ 0x00000000000007F0 ║ Item3    ║
╚═════════╩════════════════════╩══════════╝

Now, we'll re-run the stored procedure, passing in the @rv obtained by the initial call:

EXEC @rv = dbo.GetCatalogItems @rowversion = @rv, @PageNumber = 2, @PageSize = 1;

The results:

╔═════════╦════════════════════╦══════════╗
║ ListKey ║ rv                 ║ ItemName ║
╠═════════╬════════════════════╬══════════╣
║ 2       ║ 0x00000000000007EF ║ Item2    ║
╚═════════╩════════════════════╩══════════╝

And one more time, to see if we can see the third row:

EXEC @rv = dbo.GetCatalogItems @rowversion = @rv, @PageNumber = 3, @PageSize = 1;

The results:

╔═════════╦════════════════════╦══════════╗
║ ListKey ║ rv                 ║ ItemName ║
╠═════════╬════════════════════╬══════════╣
╚═════════╩════════════════════╩══════════╝

As you can see, nothing is returned by the third call, since we're limited to rows that existed at the start of the initial call to the stored procedure.

Using rowversion like this will have the sideffect of removing updated rows from the result set.  This might not be desirable, however if we replace the rowversion column with datetime columns to record creation and modification times, we can easily get around this limitation.

IF OBJECT_ID(N'dbo.CatalogList', N'U') IS NOT NULL
DROP TABLE dbo.CatalogList;
CREATE TABLE dbo.CatalogList
(
    ListKey int NOT NULL
        IDENTITY(1,1)
        CONSTRAINT PK_CatalogList
        PRIMARY KEY CLUSTERED
    , ItemName varchar(30) NOT NULL
    , CreateDate datetime NOT NULL
        CONSTRAINT DF_CatalogList_CreateDate
        DEFAULT (GETDATE())
    , UpdateDate datetime NOT NULL
        CONSTRAINT DF_CatalogList_UpdateDate
        DEFAULT (GETDATE())
);
GO

Here, I'll add a simple trigger to the table to ensure the UpdateDate column is always updated whenever any data in the table is updated:

CREATE TRIGGER dbo.CatalogListTrigger
ON dbo.CatalogList
AFTER UPDATE
AS
BEGIN
    ;WITH src AS (
        SELECT cl.*
        FROM dbo.CatalogList cl
            INNER JOIN inserted i ON cl.ListKey = i.ListKey
    )
    UPDATE src
    SET UpdateDate = GETDATE();
END
GO

Initial test data:

INSERT INTO dbo.CatalogList (ItemName)
VALUES ('Item1')
    , ('Item2');

The new stored procedure:

IF OBJECT_ID(N'dbo.GetCatalogItems', N'P') IS NOT NULL
DROP PROCEDURE dbo.GetCatalogItems;
GO
CREATE PROCEDURE dbo.GetCatalogItems
(
    @InitialDate datetime
    , @PageNumber int = 1
    , @PageSize int = 100
)
AS
BEGIN
    SET NOCOUNT ON;
    SELECT cl.*
    FROM dbo.CatalogList cl
    WHERE cl.CreateDate <= @InitialDate
    ORDER BY cl.ListKey
    OFFSET @PageSize * (@PageNumber -1) ROWS
    FETCH NEXT @PageSize ROWS ONLY
END
GO

The first run of the stored procedure:

DECLARE @d datetime = GETDATE();
EXEC dbo.GetCatalogItems @InitialDate = @d, @PageNumber = 1, @PageSize = 1;

╔═════════╦══════════╦═════════════════════════╦═════════════════════════╗
║ ListKey ║ ItemName ║ CreateDate              ║ UpdateDate              ║
╠═════════╬══════════╬═════════════════════════╬═════════════════════════╣
║ 1       ║ Item1    ║ 2017-07-27 14:31:35.317 ║ 2017-07-27 14:31:35.317 ║
╚═════════╩══════════╩═════════════════════════╩═════════════════════════╝

Inserting an additional row:

INSERT INTO dbo.CatalogList (ItemName)
VALUES ('Item3');

SELECT *
FROM dbo.CatalogList;

╔═════════╦══════════╦═════════════════════════╦═════════════════════════╗
║ ListKey ║ ItemName ║ CreateDate              ║ UpdateDate              ║
╠═════════╬══════════╬═════════════════════════╬═════════════════════════╣
║ 1       ║ Item1    ║ 2017-07-27 14:31:35.317 ║ 2017-07-27 14:31:35.317 ║
║ 2       ║ Item2    ║ 2017-07-27 14:31:35.317 ║ 2017-07-27 14:31:35.317 ║
║ 3       ║ Item3    ║ 2017-07-27 14:31:35.353 ║ 2017-07-27 14:31:35.353 ║
╚═════════╩══════════╩═════════════════════════╩═════════════════════════╝

EXEC dbo.GetCatalogItems @InitialDate = @d, @PageNumber = 2, @PageSize = 1;

╔═════════╦══════════╦═════════════════════════╦═════════════════════════╗
║ ListKey ║ ItemName ║ CreateDate              ║ UpdateDate              ║
╠═════════╬══════════╬═════════════════════════╬═════════════════════════╣
║ 2       ║ Item2    ║ 2017-07-27 14:31:35.317 ║ 2017-07-27 14:31:35.317 ║
╚═════════╩══════════╩═════════════════════════╩═════════════════════════╝

The extra row is not returned by the stored procedure:

EXEC dbo.GetCatalogItems @InitialDate = @d, @PageNumber = 3, @PageSize = 1;

╔═════════╦══════════╦═════════════════════════╦═════════════════════════╗
║ ListKey ║ ItemName ║ CreateDate              ║ UpdateDate              ║
╠═════════╬══════════╬═════════════════════════╬═════════════════════════╣
╚═════════╩══════════╩═════════════════════════╩═════════════════════════╝

We update Item1:

UPDATE dbo.CatalogList
SET ItemName = 'Updated Item1'
WHERE ListKey = 1;

And go back to page 1 in the results, we see the updated item:

EXEC dbo.GetCatalogItems @InitialDate = @d, @PageNumber = 1, @PageSize = 1;

╔═════════╦═══════════════╦═════════════════════════╦═════════════════════════╗
║ ListKey ║ ItemName      ║ CreateDate              ║ UpdateDate              ║
╠═════════╬═══════════════╬═════════════════════════╬═════════════════════════╣
║ 1       ║ Updated Item1 ║ 2017-07-27 14:31:35.317 ║ 2017-07-27 14:31:35.700 ║
╚═════════╩═══════════════╩═════════════════════════╩═════════════════════════╝

For a better fetch/offset solution, see this post by Aaron Bertrand


Mariadb slow_query_log keeps disabling I'm trying to enable slow_query_log on a Mariadb 10.1.32 server.
When slow_query_log is enabled & flushed, logging is successful (slow queries are recorded and written to the file) for a minute or less, before becoming disabled again. By disabled I mean the slow_query_log variable has been set to OFF.
I can't find any indication in logs (mysql/syslogs) as to what might be disabling the slow_query_log.
Any clues appreciated.
options

| log_output          | FILE                                 |
| slow_query_log_file | /var/log/mysql/mariadb-slow_test.log |

Note slow_query_log_file per my configs is set to /var/log/mysql/mariadb-slow.log; and I had changed to this new 'test' file as part of debugging. I see the same behaviour regardless of file.
We're running the database locally; Debian Jessie.
root@xxxxx:/var/log/mysql# ls -l /var/log/mysql/mariadb-slow_test.log

-rw-rw---- 1 mysql adm 449 May 25 09:40 /var/log/mysql/mariadb-slow_test.log

These configs are set in my.cnf since the last restart:
root@xxxx:/var/log/mysql# cat /etc/mysql/conf.d/mygalera.cnf | grep slow
slow_query_log              = 1
slow_query_log_file         = /var/log/mysql/mariadb-slow.log

Long query time:
MariaDB [(none)]> SELECT @@global.long_query_time\G
*************************** 1. row ***************************
@@global.long_query_time: 10.000000

I pointed the file back to the default (/var/log/mysql/mariadb-slow.log), flush & enable, it logged again until 11:50:
 - -rw-r-----  1 mysql adm   861 May 25 11:47 mariadb-slow.log
 - -rw-r-----  1 mysql adm  2.1K May 25 11:50 mariadb-slow.log

This makes me think there is something scheduled that is disabling it, however event_scheduler is off and nothing in cron.

Answering my own question. It turned out to be ClusterControl.

Despite having Performance Schema enabled & configured, ClusterControl's query monitor was starting & stopping slow_query_log as described here.

Disabling the Query Monitor in cluster control, and enabling slow_query_log resolved the issue.


Re-run a specific actual query plan I have captured an actual query plan for a specific query.

After this I've changed a few things around (including updating the statistics) and re-ran that specific query. Now the actual query plan is different (which makes sense).

The query now runs a lot faster. I'm curious if the new execution plan has anything to do with this, because other changes (changes to the IO setup, VM settings, sql instance restart, etc) may also be causing the improvement in performance. To test this I'd like to run the query once more, and try to force SQL Server to use the old execution plan.

Question: is there a way to re-run a query with a user-provided execution plan, or even run a query directly from such a plan?

Here's what I've tried to figure this one out:

I've searched in the books we have available at the office (Professional SQL Server 2012 Internals and Troubleshooting, Querying Microsoft SQL Server 2012);
Google searches, for example "run query based on specific query plan"
DBA.SE searches, for example "execute query plan" and "re run execution plan"
And finally, one that has answered my questions many time before: carefully check "Questions that may already have your answer" before hitting "Post your question" :-)

So bottom line: is this possible? If so: how?

Yes.

You need the USE PLAN hint.

In which you supply the XML from the first plan.

SELECT *
FROM T
OPTION (USE PLAN N'<?xml version="1.0" encoding="utf-16"?> ....')

Whilst it doesn't guarantee that the plan will be exactly the same (e.g. compute scalar operators can move around for example) it will likely be pretty close.


How to load .dat files to oracle database I have .DAT files extracted from telecom switch.

I want to upload this file to an Oracle server using IBM data-stage ETL tool.

Unfortunately these files are not readable format.

This is a picture of the .DAT file when opened in notepad:

Not all .dat files are the same and it's hard to determine from the screenshot what the format is.

If you extracted that from a telecom switch, you will need to contact the telecom vendor to ask if they have a data provider for the file format and use that provider to make a connection to the .dat file from within your ETL tools.

If they don't have a provider, which is entirely plausible, you will need to extract the data in another format such as .csv. Again, contact the vendor to see if that is an option


Optimize query to minimize tmp copying time I have a query when Checking the profile i get this :

I read in order to optimize this i'll have to work on my joins .

I also added index on the fields that i'm grouping by (date_seance , heure_debut) in the main table (seance_pratique)

Is there something i can do to make it faster ?

Update 1 (EXPLAIN QUERY):

Review indexing on cours table as you're getting a full table scan there.

http://mysql.rjweb.org/doc.php/ricksrots

Great indexing tips there ^


How do I use the smallest amount database file space to rebuild an index on Mircosoft SQL Server Express due to the 10GB limit How do I use the smallest amount of database file space to rebuild an index on Mircosoft SQL Server Express due to the 10GB limit

I have a database file size of 7GB but I have two indexes that are on the two largest tables that are very fragmented

If I try to rebuild the index PK_Sale_Dtl the file size balloons to 10GB and the rebuild fails and I get nowhere but if I drop the index and recreate it the file doesn't barely increase at all

Why is this? And which is better rebuild or recreate index?

The reason is that index rebuild means creating a new index under covers and then dropping the old index. I.e., while the operation is running you need twice the amount of storage available. If that isn't doable for you, you have basically two options:

Do it yourself in the opposite order: Drop first and then create.

Or do REORGANIZE instead of REBUILD.


Import a Oracle DMP file into a Fresh install of oracle A client sent us an Oracle database we need to test against. We don't use Oracle or have any in-house Oracle expertise.

We need to setup the database so we can connect to it and debug a problem.

I did a fresh install of Oracle 9 (the version the client is running) and the management tools.

For the life of me, I cannot get it to import the data. It should not be this complicated. I must be getting something wrong.

I've tried:

imp 'sys/password AS SYSDBA' file=c:\database.dmp full=yes log=implog.txt

and got:

Connected to: Personal Oracle9i Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production

Export file created by EXPORT:V09.02.00 via conventional path

Warning: the objects were exported by SYSTEM, not by you

import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
. importing SYSTEM's objects into SYS
. importing ABCDE's objects into ABCDE
IMP-00003: ORACLE error 1435 encountered
ORA-01435: user does not exist
Import terminated successfully with warnings.

But nothing shows up in the manager as far as tables in any schema and I'm at my wits end.

You'll need to create a user (or Schema) first

C:\>sqlplus system/password

SQL> create user CLIENT_TEST identified by client_test_password;
SQL> grant connect, unlimited tablespace, resource to CLIENT_TEST;
SQL> exit

Then you can use the fromuser= and touser=  IMP switches to import the data into the new user/schema:

C:\>imp system/password FROMUSER=ABCDE TOUSER=client_test file=c:\database.dmp full=yes

Hope that helps!


PostgreSQL: Issue with a corrupted GiST index on ll_to_earth I'm facing something that seems like an index corruption issue on a table that stores about 120k entries.
Each entry has a latlon and this latlon is indexed using the ll_to_earth GiST implementation:

CREATE INDEX table_geo ON table USING gist(ll_to_earth(lat, lon));

Now if I query the latlon of a specific entry located at -5.182, -80.622 using earth_box like this:

SELECT id FROM table
WHERE earth_box(ll_to_earth(-5.182, -80.622), 6000) @> ll_to_earth(lat, lon);

I don't have any result, although the same request using the primary key always returns the expected entry:

SELECT id FROM table
WHERE earth_box(ll_to_earth(-5.182, -80.622), 6000) @> ll_to_earth(lat, lon)
  AND id = 999;

> 1 result:
  id     lat     lon
  999    34.01   -117.94

Interestingly, if I increase the radius of the earth_box by a lot, I'm able to see the entry again! I can even find the new location of the entry in the btree by increasing/decreasing the latlon and the radius, but if I reduce the radius too much, I'll lose it because of the Recheck.

I found of that out of 123k rows, 14k (11%) are corrupted.

Query flows

Without the primary key:

EXPLAIN SELECT id FROM table
WHERE earth_box(ll_to_earth(-5.182, -80.622), 6000) @> ll_to_earth(lat, lon);

Bitmap Heap Scan on table  (cost=9.23..495.43 rows=123 width=16)
    Recheck Cond: ('(1029056.37336865277, -6273202.2275760062, -582074.280166476732),(1041056.37292618607, -6261202.22801847383, -570074.280608943431)'::cube @> (ll_to_earth(lat, lon))::cube)
      ->  Bitmap Index Scan on table_geo  (cost=0.00..9.20 rows=123 width=0)
            Index Cond: ('(1029056.37336865277, -6273202.2275760062, -582074.280166476732),(1041056.37292618607, -6261202.22801847383, -570074.280608943431)'::cube @> (ll_to_earth(lat, lon))::cube)

And with the primary key:

EXPLAIN SELECT id FROM table
WHERE earth_box(ll_to_earth(-5.182, -80.622), 6000) @> ll_to_earth(lat, lon)
  AND id = 999;

Index Scan using table_pk on table  (cost=0.42..8.69 rows=1 width=16)
    Index Cond: (id = 999)
    Filter: ('(1029056.37336865277, -6273202.2275760062, -582074.280166476732),(1041056.37292618607, -6261202.22801847383, -570074.280608943431)'::cube @> (ll_to_earth(lat, lon))::cube)

The second query is a simple Index Scan which I think is guaranteed to find the row.

The first query however uses a Bitmap Index Scan on the index + a Bitmap Heap Scan so I can imagine 2 scenarios that would make it fail:

the entry is located in the wrong btree node (= Heap Scan fails)
btree nodes have wrong dimensions (= Index Scan fails)

Things I tried

UPDATE table SET lat = lat + 0.000001 WHERE id = 999;

This is always enough to fix 1 or several specific entries. So I thought I would fix everything by running this query on the whole table but it did not. Some rows were fixed but some others got broken.
The overall corruption rate was still around 10-12%.

Then I read about REINDEX on the postgres manual, so I tried that:

REINDEX INDEX table_geo;

But the same thing happened, some entries got fixed and some others stopped working.

My postgres version on prod:

  PostgreSQL 9.5.10 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.2 20140120 (Red Hat 4.8.2-16), 64-bit

Extensions used:

cube (1.0)
earthdistance (1.0)

Looks like a bug in either the GiST index implementation, the ll_to_earth() function (or maybe the specific operator / operator class). You have pretty much ruled out plain index corruption by trying REINDEX.

First try to upgrade to a current version of Postgres and see if this fixes your problem. 9.5 is getting old (and the current point release is 9.5.15). I did not find any specific entries concerning your case in the release notes since 9.5.10 - on a quick glance! At least upgrade to latest point release, currently 9.5.15 - as is the recommendation of the project. Or to the latest version Postgres 11.1 if that's an option, and see if the problem goes away. (Or recreate your db in pg 11 just to test.)

Both extensions cube and earthdistance have been updated, too. Check curent versions for your installation with:

SELECT * FROM pg_available_extensions where name IN ('cube', 'earthdistance');

For Postgres 11:

cube    1.4
earthdistance   1.1

To see current versions for any given Postgres version try this fiddle.

If upgrading does not fix the problem please file a bug report.


Get last modified date of table in postgresql I want know the last modified date of table in postgresql. In SQL Server  can get using

SELECT modify_date FROM sys.objects

How to get same thing in Postgres? I am using Postgres 9.4

There is a way with Postgres 9.5 or later. Turn on track_commit_timestamp in postgresql.conf and restart the DB cluster. Starts logging commit timestamps.
Then you can get the timestamp of the latest modification (latest commit) for a given table using the function pg_xact_commit_timestamp():
SELECT pg_xact_commit_timestamp(t.xmin) AS modified_ts
FROM   my_table t
ORDER  BY modified_ts DESC NULLS LAST
LIMIT  1;

NULLS LAST is necessary while there still may be rows without recorded commit timestamp.
Related:

PostgreSQL sort by datetime asc, null first?
Row with latest timestamp
Is there way to get transaction commit timestamp in Postgres?

For Postgres 9.4 or older, see:

How to find out when data was inserted to Postgres?


Cannot revoke permissions or drop user in PgSQL AWS RDS I want to drop the user asd from the DB. So, I've got two DBs: foo and bar and the user apparently has dependencies in both. The DBs have been created from the root user postgres, but all tables and sequences inside have been created by users foo_migration and bar_migration respectively.

First I log in as the postgres user to the foo DB. When I try to drop the user, I get:

ERROR:  role "asd" cannot be dropped because some objects depend on it with details about privileges from multiple sequences, tables, the bar DB and a few objects in the postgres DB.

So, I try to clear things one by one (while still logged in as postgres user in the foo DB). I first try to REASSIGN OWNED BY asd TO foo_migration;. This returns ERROR: permission denied, which is weird because I'm logged in as the postgres user. Anyway, then I try

REVOKE ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public FROM asd;
REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA public FROM asd;

and I get a bunch of WARNING: no privileges could be revoked for all the sequences and tables that the user has rights on. I also try DROP OWNED BY asd; which also ends in ERROR: permission denied.

This was all unsuccessful, so I try logging in the postgres DB as the postgres user and perform the same steps. The REVOKE commands execute successfully without warnings, but no permissions actually get changed/affected. The REASSIGN and DROP OWNED BY still result in ERROR: permission denied.

The error messages are bare. There's no hints or details provided alongside with them.

I do not have the password for the asd user, so I cannot try to log in with it and perform any actions.

Do you have any ideas of how to approach this? Do I need to look into some tables that show ownerships and go from there? Isn't the postgres user the admin user that is "almighty" ?

I was running into similar issue on Postgres RDS. This was the workaround I used to reassign objects from one user to another and then drop user. For you it would go like:

-- logged in as the rds_superuser role you defined when you created your RDS instance
CREATE ROLE change_owner LOGIN;
GRANT asd TO change_owner;
GRANT foo_migration TO change_owner;

-- log out of current user and log in as `change_owner`
REASSIGN OWNED BY asd TO foo_migration;

-- log out of current user and log in as rds_superuser role
DROP USER asd;


Order of returned rows with IN statement I know that the order of returned rows is not guaranteed with the IN statement in Postgres. For example if I do this:
SELECT users.id FROM users WHERE users.id IN (13589, 16674, 13588)

I may get this result:
16674
13588
13589

However, I want returned rows to respect the order in the IN list, so I found few solutions online, such as:
SELECT users.id FROM users WHERE users.id IN (13589, 16674, 13588)
ORDER BY POSITION(id::text in '(13589, 16674, 13588)')

or
SELECT users.id FROM users WHERE users.id IN (13589, 16674, 13588)
ORDER BY id = 13589 desc,
         id = 16674 desc,
         id = 13588 desc;

I wonder if there is a nicer way to do this, or better yet more efficient?

WITH ORDINALITY in Postgres 9.4+
Introduced with Postgres 9.4. The manual:

When a function in the FROM clause is suffixed by WITH ORDINALITY, a
bigint column is appended to the output which starts from 1 and
increments by 1 for each row of the function's output. This is most
useful in the case of set returning functions such as unnest().

SELECT u.*
FROM   unnest('{13589, 16674, 13588}'::int[]) WITH ORDINALITY AS x(id, order_nr)
JOIN   users u USING (id)
ORDER  BY x.order_nr;

array or set?
x IN (set) statements are rewritten internally in Postgres to x = ANY (array), which is equivalent:
SELECT users.id FROM users WHERE users.id = ANY ('{13589, 16674, 13588}')

You can see for yourself with EXPLAIN.
Postgres 9.3 or earlier
For now, to preserve the order of elements, you could:
SELECT u.*
FROM  (
   SELECT arr, generate_subscripts(arr, 1) AS order_nr
   FROM  (SELECT '{13589, 16674, 13588}'::int[]) t(arr)
   ) x
JOIN   users u ON u.id = x.arr[x.order_nr]
ORDER  BY x.order_nr;

db<>fiddle here
Further reading:

How to preserve the original order of elements in an unnested array?


Mongodump: Unrecognized field 'snapshot' I am using mongodump from mongo tools to download a backup of my server. However as I run the command, the command returns "Unrecognized field 'snapshot'".

The full error is:

Failed: error reading collection: Failed to parse: { find: "data", skip: 0, snapshot: true, $readPreference: { mode: "secondaryPreferred" }, $db: "xxx" }. Unrecognized field 'snapshot'.

Mongodump --version returns:

mongodump version: built-without-version-string
git version: built-without-git-spec
Go version: go1.7.3
   os: linux
   arch: amd64
   compiler: gc
OpenSSL version: OpenSSL 1.0.2g  1 Mar 2016

I might need to downgrade my version? Otherwise unsure of where to go from here...

Add --forceTableScan
Example:
mongodump --forceTableScan -d database_name -o target_directory

http://aug2uag.blogspot.com/2018/08/mongoexport-unrecognized-field-snapshot.html

The Error comes from the mongo snapshot functionality which was introduced in mongo 4.0 (https://docs.mongodb.com/manual/reference/read-concern-snapshot/)
If you use an older mongo client (<4.0) to dump/export a more recent MongoDB you will get the error

Unrecognized field 'snapshot'

Using "forceTableScan" will prevent the snapshot functionality to become active, this you won't get the error


SSRS 2012 and SharePoint 2010 Standard Is it possible to use Report Builder Model, Report Builder Report, Report Data Sources with a Sharepoint Standard 2010 edition?

The three of them are available with SharePoint standard. Just need to activate the content types (after a restart of SP services).


Unlock user shell script for Oracle We have so many user ID locks from the front-end users. We need to provide a shell script to the user which they can run on OS level and unlock the ID themselves when the DBA is not present.

The sys password needs to be decrypted during runtime for script run & encrypted after script executes successfully. (Ideally they should not see the password)

We have multiple databases so the parameter has to be passed in the below manner only.

Example syntax : ./tmp/unlock_user.sh user instance_name

This is too much to ask. I know. If anyone can help, It will be great.

OS : Generic Linux
Database : 11gR2

We have so many user ID locks from the front-end users.

Good Problem Statement.
More common than you might think.

We need to provide a shell script to the user which they can run on OS level and unlock the ID themselves when the DBA is not present.

No.  You don't.
You need to find out why the users are locking their accounts so often and resolve the Root Cause of that problem, not fiddle about trying to fix the Symptoms.

The sys password needs to be decrypted during runtime for script run

No.
Just ...   No.

... & encrypted after script executes successfully. (Ideally they should not see the password)

There should never be any possibility of Users seeing the SYS password.
EVER.
Remember: your job as DBA is to clean up the mess that other people make.  Always keep the biggest and best Tools for yourself.

We have multiple databases so the parameter has to be passed in the below manner only.
Example syntax : ./tmp/unlock_user.sh user instance_name

This may be part of your Problem.
If Users have different passwords in different databases, then they're going to get themselves muddled up and lock themselves out.
Do your users really have shell access to your database hosts?
Next you'll be telling me that they connect directly to the Database and run SQL ...
Moving forward ...
If there's an Application layer between the Users and your Database, then you should consider introducing an "Application" account, whose credentials are used exclusively by the Application and that users are never aware of.  All database connections will be made using these credentials and Users will not have their own accounts in your databases.  OK, this means that you'll have to re-implement account authentication, because the database won't be doing it for you, but that shouldn't be a huge job.
Using an Application account has other benefits.
If a User "locks themselves out" in this "shared-connection" model, the Application can still support them in resetting their password (or whatever), because the Application has its own credentials and connection to the Database.  If the User has their own credentials and connection, then they cannot get into the database to reset their password in the database, hence your current predicament.
If there is no Application layer between the Users and the Database (and I would seriously ask "why not"?), then you may have to look at something [a lot] more intrusive, like hooking Oracle into Kerberos or [indirectly] Active Directory, to provide you Users with a true, single signon experience.


How do I get more details on a full text catalog error? I have an error that is repeatedly generated on a SQL Server 2005 instance running the databases for MS Team Foundation Server.

 'Severity 16' occurred on INSTANCENAME

Looking in the SQL Server logs gives me

 Errors were encountered during full-text index population for table or indexed view '[TfsWorkItemTracking].[dbo].[WorkItemLongTexts]', database 'TfsWorkItemTracking' (table or indexed view ID '1810105489', database ID '14'). Please see full-text crawl logs for details.

And the actual full-text crawl log (\MSSQL\LOG\SQLFT0001300005.LOG) gives me:

 Error '0x800b0100' occurred during full-text index population for table or indexed view

 The component 'MSFTE.DLL' reported error while indexing. Component path 'D:\Program Files\Microsoft SQL Server\MSSQL.3\MSSQL\Binn\MSFTE.DLL'

If I rebuild the full-text catalog like so...

 ALTER FULLTEXT CATALOG [TeamFoundationServer10FullTextCatalog]
 REBUILD

The error will stop happening for a while, but it will start up again days later. I have searched for hours and cannot figure out what is causing this. Any suggestions?

According to a post in the MSDN forums, SQL Server 2005 (and probably earlier) contained a flaw that results in the error.  This was reported by "A Fellows" in his problem resolution back in 2010.

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/9626c8f8-9171-4625-a9c0-ef72628066b9/fulltext-error-indexing-rows-with-jpgs

In part he says: "Well microsoft looked into it and with SQL2008 it works properly, with SQL2005 it doesn't ... (but) ... SQL2008 full text has been rewritten and works as it should."

If you are still on SQL Server 2005 and still stuck with the problem, then he also offers a workaround of sorts for SQL Server 2005.


Safe to use SQLNET.ALLOWED_LOGON_VERSION_SERVER in 11g sqlnet.ora file? I have a mixed environment running both 11g and 12c databases on the same server.  One of our databases was recently upgraded to 12c, and the older Oracle clients have started throwing ORA-28040 errors on connection attempts due to the use of older authentication protocols.

On the database server, each instance is installed in a separate Oracle Home with it's own Oracle software.  The sqlnet.ora files in each ORACLE_HOME/network/admin directory have been created as a symbolic link to a central sqlnet.ora file on a shared NFS disk.

I would like to use the SQLNET.ALLOWED_LOGON_VERSION_SERVER parameter in the central sqlnet.ora file to allow connections from the older clients until they can be upgraded.  The concern I have is this:  If the parameter is only supported by 12c, I need to be sure that including it won't cause problems for connections to the 11g databases.  Is it safe to include this parameter in the sqlnet.ora file of an 11g database? Will it simply be ignored?

The parameter exists in 11g as well. This is the 11g documentation:

SQLNET.ALLOWED_LOGON_VERSION

  Allowed Values

  12 for the critical patch updates CPUOct2012 and later Oracle Database
  11g authentication protocols (recommended)
  11 for Oracle Database 11g authentication protocols
  10 for Oracle Database 10g authentication protocols
  9 for Oracle9i Database authentication protocols
  8 for Oracle8i Database authentication protocols (default)


ERROR 2003 (HY000): Can't connect to MySQL server on 'xxx.xxx.xx.xx' (10060) I'm trying to connect my local MySQL workbench to a 2nd generation MySQL instance on Google SQL platform using the following command on my windows laptop.

mysql --host=130.xxx.xx.xx --port=3306 --user=root --password

I have also created a user in the MYSQL from the Google Cloud shell and created a 'cwood' user and granted all permissions

 mysql --host=130.xxx.xx.xx --port=3306 --user=cwood --password

should the username be  cwood@something?

I double checked my IP address with whatismyIPaddress it is granted permission in the allowed network section.

I can connect via Google Cloud Shell on the Google Platform itself using root and also ping the IP address from the local cmd prompt and ping my machine from google shell.

As a troubleshooting step I have disabled the local firewalls on my laptop and granted full access to 0.0.0.0/0 to allow all connections on the google platform console so dont think this is the issue.

still no joy from either a cmd line connection on windows or using a putty client, can anyone give me any further pointers on the missing step? something like allow remote connections? being a managed service I dont have the local config files, ip tables as many google searches suggest

Cheers..

I had a similar issue. I believe there are two ways to resolve it (though only one worked for me).

The first way (that did not work for me) would be to create a client certificate via the Cloud Platform -> SQL -> [Instance] -> Access Control tab -> SSL tab -> Create Client Certificate button.

It will give you instructions for downloading the client certificate to your machine and using it to connect via SSL.

The second way (which did work for me) was to allow non-SSL connections. You can find the button in the the "SSL Connections" section under the same SSL tab (under Access Control for the instance). Then try connecting with gcloud sql connect [instance-name] --user=root. This will automatically send a request to allow your IP address to connect directly to the database, and then attempt to connect via the mysql tool.


MongoDB replicaset number of connections too high I'm using a 3 node replicaset with 2 data nodes(version 2.6.1 and 2.6.3) and 1 arbiter node. I get frequent alerts on number of connections >1000, whereas total databases are about 400 and also I'm using connection pooling with nodeJS.

With another standalone instance where number of databases are much more than that in first scenario I get number of connections are mostly less than 400.

How many connections can I expect normaly(so that I can decide a threshold)?

As Antonis said, the number of connections has little relation to the number of databases.

In general, the number of connections aren't something to worry about. The MongoDB drivers keep the connections alive to reuse them and to prevent the overhead of setting up new connections.

However, each connection is provided with about 1MB of stack server side. Unnecessary connections might eat up precious RAM, which is used by MongoDB to for the indices and as much of the working set as possible in order to speed things up.

In case you have enough RAM on your server, you have nothing to worry about – just adjust your alert thresholds to more suitable numbers. If you have a lot of page faults, however, you should investigate a bit further.

Since you are using connection pooling, it is safe to assume that you either more concurrent connections on your application than your MongoDB server (hardware, that is) can handle, or you are opening unnecessary connections.

High number of concurrent connections

As a rule of thumb, your MongoDB should be able to handle as many connections as you have concurrent request. In order to give you a decent amount of time to scale out when you reach the server's limits, your alert should trigger at about 80% utilization. For example, let's assume your server can handle about 1500 connections easily, your alert go off at

  1500 * 0.8 = 1200 connections

If your server gets into problems with the 1000 connections you mentioned or when you hit 80% utilization, you should first scale up , for example by putting more RAM into the machine or – more generally speaking – eliminate the limitation which prevents the server from handling this number of  connections. Which point to scale up to is not easy to determine, but generally speaking, you want to scale up as long as you get more bang than you have to put bucks into it.

There is a point where the bang you get for each buck you put in decreases drastically – of course you want to stop scaling up a bit earlier. Now what can you do in case your server still does not meet your requirements? The answer is to scale out, which in MongoWorld means setting up a sharded cluster. A word of warning: While creating a sharded cluster is no rocket science, there are quite some caveats and pitfalls. Make sure you have read the documentation about sharding thoroughly before implementing a sharded cluster. A good consultant usually is worth the money, too.

That being said: Usually it is the application server which first reaches the limit of concurrent users it can handle, so have a close look there.

Multiple open connections per concurrent user

Usually, you request a connection from the pool by doing your stuff on the reused db object and the connection is returned transparently to the pool after the stuff is done (simplified, but should be sufficient in this context). The pool is handled by the client transparently. Each time you use MongoClient.connect, a connection pool is created. You should only call this method once per application and reuse it. Doublecheck if you follow the described pattern.

Conclusion

Make sure you reuse the db object returned by MongoClient.connect
Find out your number of concurrent users.
Check wether the number of connections made to the replica set is much higher than the number of concurrent users. If not, everything is working as expected.
If the numbers roughly match and you are experiencing problems from the database side (long response times, high latency), either scale up or out after you made sure that it is not your application slowing the responses down.


Deciding between an IsDeleted/DateDeleted column or a separate History table? We have a table that stores nothing but an AccountId, TagId, and DateCreated. Users can update the Tags on accounts, which adds or remove records from this table. No tag can exist more than once on an account, but the same tag can be added and removed multiple times. This table is primarily used in queries users build to filter accounts using either JOIN or EXISTS on the tag table.

I've been asked to start recording tag history, and I'm trying to decide if it would be better to use an IsDeleted and/or DateDeleted column on the existing table, or if I should create a separate history table that stores this data instead.

The primary purpose of tracking the history would be to run reports on a specific point in time. For example, getting data on accounts that had TagA on them during January 2013.

What factors should I consider when trying to decide between the two?

The problem with IsDeleted is its low cadinality: 0 or 1. Which makes it unindexable and since all your queries will add IsDeleted = 0 to the WHERE (or JOIN) conditions this apparent trivial change complicates a lot of things. You covering indexes will all of the sudden require lookups into the base table to decide if the row qualifies or not. There are measures that can improve behavior, but they have to be properly considered and consistently deployed (note that some of these mitigation are mutually exclusive, evaluate them separately):

add IsDeleted as the leftmost key in the clustered index. This effectively 'splits' the clustered table into two regions ('deleted' and 'active'). It also forces de-facto the IsDeleted column in every non-clustered index, thus avoiding the need to lookup the value.
add IsDeleted as a leftmost key in every non-clustered index. May sound extreme, but can be quite effective, provided that all queries specify IsDeleted in the WHERE.
add IsDeleted as an included column to every non-clustered index. Unlike the options above this does not change seek/range/lookup semantics of the exiting indexes, but makes the IsDeleted check filtering available in every access path. Can be quite effective specially if IsDeleted = 1 occurs seldom.
make the non-clustered indexes filtered by IsDeleted = 0. May be effective on large tables, but is a bit dangerous as the filtered indexes become unusable for queries interested in the deleted rows.

A separate table for history makes a much more cleaner cut from the engine point of view: is a different table, no messy/risky plan choices to cope with. But it is much more impact on the application. If looking up the history of a row is the exception, the application changes are reasonable easy then I would consider this option first, as it has the least risk of (severe) performance regression.

I would strongly advise against mixed solutions (eg. tags and tags_history table and a view that unions them), such solutions have all the disadvantages mentioned above without any advantage. In general, anything that claims 'minimal impact' or 'transparent change' is snake oil. This (adding application layer row versioning/row history) is, by definition, a high impact change which will ripple big time in the application.


Get all the users that are managed by a targeted manager I have the following model:

Table user:  (id, name, direct_manager_id)

Table customer: (id, name, managed_by_id)

i.e. users can have another user as their direct manager, Customers can be managed by a user.

My requirement is to create a simple select query that will retrieve all the customers that are managed by a specific user. moreover I would like that query to retrieve all the customers that are managed by users that their direct manager is that same user (1 level deep of direct management). and to retrieve all the customers that their direct manager is manged by a user that the direct manager of him is the targeted user (2nd level deep of direct management).... (to the nth level deep)

Here is a picture of the hierarchy:

           targeted User
            /          \
           user1         user2
           /  \              / \
          c1   c2           c3  user3
                                    /
                                   c4

So the select query with the targeted user id will select the customers c1-c4

No simple select exists because MySQL does not support Recursive CTE queries like SQL Server.

This being the case, I wrote up some Stored Procedures to run iterative techniques for gathering children of a parent out of a hierarchical data design.

Here is my old post from Oct 24, 2011 : Find highest level of a hierarchical field: with vs without CTEs

I have three Stored Procedures

GetParentIDByID
GetAncestry
GetFamilyTree

In your particular case, you need to do two things

Change GetFamilyTree to gather all users from the user table under the target user
LEFT JOIN the result from GetFamilyTree with the customer table (I suggest LEFT JOIN rather than INNER JOIN in the event a user does not have customers, has not been fired, and it still in this hierarchy).


'ORA-00059: maximum number of files exceeded' on Oracle 12c I have a test database.  Every night a script drops and recreates a number of pluggable databases that are used for our automated test runs.  Recently, I have started getting 'ORA-00059: maximum number of files exceeded' errors on the create pluggable database commands.  I must be missing something, because from what I can tell, I am no where near the maximum (and this script was working a month ago).

SQL> select value from v$parameter where name = 'db_files';

VALUE
-----------------------------------------------------------

200

SQL> select count(*) from v$datafile;

  COUNT(*)
----------
        24

SQL> create pluggable database testdb FROM DB_SEED
  2    FILE_NAME_CONVERT = ('/home/ora12c1/app/ora12c1/oradata/ora12c1/testdb_seed/', '/home/ora12c1/app/ora12c1/oradata/ora12c1/testdb/')
  3    PATH_PREFIX = '/home/ora12c1/app/ora12c1/oradata/ora12c1/testdb';
create pluggable database testdb FROM DB_SEED
*
ERROR at line 1:
ORA-00059: maximum number of DB_FILES exceeded

It looks like the following resolves the issue:
Bug 18522516  12.1.0.1.4 (Jul 2014) Database Patch Set Update (PSU)

One note though, I had to bounce the database before I could create a new pluggable database.  As, I preformed all the steps that I saw in the doc:

Apply the patch using opatch apply
Startup/Open all pdbs
Run datapatch -verbose

Then I had to bounce the database to resolve my issue.

The answer was posted as an update to the question:

It looks like the following resolves the issue: Bug 18522516 12.1.0.1.4 (Jul 2014) Database Patch Set Update (PSU)
One note though, I had to bounce the database before I could create a new pluggable database. As, I preformed all the steps that I saw in the doc:
Apply the patch using opatch apply Startup/Open all pdbs Run datapatch -verbose
Then I had to bounce the database to resolve my issue.


Does a whole-server backup guarantee a reliable backup of running databases? Many hosts offer a full-server backup once per day. However, I've been told that a backup of this kind doesn't guarantee a reliable backup of running databases, because even if the database uses transactions and is ACID compliant, what can possibly happen is:

The backup procedure starts reading the database file, but when it's eg. half way through..
A transaction to the database is made that changes something both at the beginning of the database file (which has already been read by the backup), and at the end (which is yet to be read).
The backup procedure continues reading the file, and therefore gets the beginning of the file before the transaction, and the end of the file after the transaction - which spells trouble.

I've been told the only way to reliably backup a database is to use the database's in-built backup procedure. Those backups can be stored on the server, and they in turn can be backed up but the whole-server backup.

Is this all correct?

The reason I ask is the host is offering whole-server backups only, and I need to know if I this is sufficient, or if I need to sort out something separate for the database. The database is MySQL (InnoDB) - though I'm interested if the logic applies to all databases.

For a filesystem backup to be consistent MySQL requires that the DB be stopped http://dev.mysql.com/doc/mysql-backup-excerpt/5.0/en/backup-methods.html but as you said there are ways of using the DB provided tools to create consistent online backups.

Other DB engines such as Oracle have a mode you can put the DB files into prior to a known time of a filesystem copy to ensure that what's on disk at that time is consistent but you have to know the exact time the backup will start and finish.

there are other DB formats that are also consistent by virtue of the fact that their files are 100% append only such as CouchDB.

But for the most part backups of a running DB require some kind of intervention using the DB engine itself.


ON DELETE CASCADE pseudo delete For data integrity ON DELETE CASCADE is necessary but it's really dangerous when a delete affects like a Chain Reaction!

I know one way to prevent this is to make a pseudo delete by putting a "Delete" flag on in all tables where the record is being deleted from. But that's a very old style and clumsy way. How can I optimize this or is there any way to do it from inside database (SQL Code) ?

Perhaps you should look into using tombstone tables.

Here is what I mean: Suppose you had this table

CREATE TABLE mytable
(
    id INT NOT NULL AUTO_INCREMENT,
    ... other columns ....
    PRIMARY KEY (id),
    ... other indexes ...
);

Now, create a table with the IDs that are marked for deletion

CREATE TABLE mytable_deleted
(
    id INT NOT NULL,
    PRIMARY KEY (id)
);

So instead of doing

DELETE FROM mytable WHERE MOD(id,100) = 0;

You would add the ids to mytable_deleted

INSERT IGNORE INTO mytable_deleted
SELECT id FROM mytable WHERE MOD(id,100) = 0;

From here, you simply have to add the mytable_delete to all queries

To see all rows

SELECT * FROM mytable;

To see non-deleted rows

SELECT * FROM mytable A LEFT JOIN mytable_deleted B USING (id) WHERE B.id IS NULL;

To see deleted rows

SELECT * FROM mytable A LEFT JOIN mytable_deleted B USING (id) WHERE B.id IS NOT NULL;

or

SELECT * FROM mytable A INNER JOIN mytable_deleted B USING (id);

To perform the actual deletion

DELETE A.* FROM mytable A INNER JOIN mytable_deleted B USING (id);
TRUNCATE TABLE mytable_deleted;

CAVEAT

This will means that lots of SELECT queries must incorporate the tombstone table.

I have discussed this before : Tombstone Table vs Deleted Flag in database syncronization & soft-delete scenarios. You should read the accepted answer from Leigh Riffel instead of mine for a more honest critique as to which method is better in your case.


Multi-Instance Failover Cluster SQL Cluster Currently planning on building a new SQL Server Cluster enviroment, we currently have 2 node 2 instance Active/Active cluster.

One of the clusters has had heavy hitting security/monitoring apps and also they've put user facing toolsets on there too.

Would it be possible have a Active/Active/Active 3 node 3 Instance Cluster? So each Instance was on its own node?

We had suggested Active/Active/Passive but one of the requirements from the client was each instance is active on its own node

Points to note:

A 3 node cluster requires SQL Server Enterprise Edition. You can deploy a 2 node cluster using Standard Edition.
If you are placing an active instance on each node, then each node will need to be licensed. That is 4 cores per node as a minimum so at least 12 SQL Server Enterprise Edition core licenses, possibly more if your load requires it.
You need to thoroughly test performance with all instances running on one node. If you lose two nodes and all instances fail over to a single node, can your hardware cope with the load?


Create a view for A table with new columns JOIN(ed) on B table queries about the last maching records with certain parameters. How to simplify code? I'm not sure if I can ask this question here. Let me know if I can't. Basically, my code works fine, but I would like to improve the code itself.

I'd like to improve this code to require fewer lines (faster is optional, but not mandatory). I think my code could be optimized in space because it performs two queries about the same table in the two join statements.

Here is the dbfiddle of my query. Note that even when the page said "MySQL 8.0" I'm using MySQL 5.7 (an old version where a lot of new stuff can't be done).

First, we create the (simplified) tables:

CREATE TABLE products (
 code varchar(10) NOT NULL,
 name varchar(100) NOT NULL,
 PRIMARY KEY (code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

CREATE TABLE transactions (
 date datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
 code varchar(10) NOT NULL,
 mode enum('Buy','Sell','Count','Return') NOT NULL,
 stock int(11) NOT NULL,
 average_price decimal(14,4) NOT NULL,
 PRIMARY KEY (date),
 KEY Code (code),
 CONSTRAINT transactions_ibfk_1 FOREIGN KEY (code) REFERENCES products (code) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Then we populate with records:

INSERT INTO products(code, name) VALUES
  ("A", "Alpha"),
  ("B", "Beta")
INSERT INTO transactions(date, code, mode, stock, average_price) VALUES
  ("2000-1-2", "A", "Buy", 5, 1),
  ("2000-1-3", "B", "Buy", 4, 2),
  ("2000-1-4", "B", "Sell", 2, 3),
  ("2000-1-5", "B", "Sell", 1, 4),
  ("2000-1-6", "A", "Buy", 6, 5),
  ("2000-1-7", "A", "Buy", 8, 6)

Finally, we make the view (if you prefer in your answer you can alter the table instead of making a view, but I didn't figure how):

Create VIEW products_view_joinA_and_JoinB
  AS select t1.code, t1.name, t2.stock, t2.average_price as cost_usd, t5.date as last_buy
    from (products t1 left join (
      select t3.stock, t3.code, t3.average_price from transactions t3
      where (t3.date = (
        select t4.date from transactions t4 where (t4.code = t3.code) order by t4.date desc limit 1)
      )) t2 on((t2.code = t1.code))
      left join(
        select t6.code, t6.date from transactions t6
        where (t6.date = (
          select t7.date from transactions t7 where (t7.code = t6.code and t7.mode = "Buy") order by t7.date desc limit 1)
       )) t5 on((t5.code = t1.code)))

Finally, we test it:

SELECT code, name, stock, cost_usd, last_buy FROM products_view_joinA_and_JoinB

code | name  | stock | cost_usd | last_buy
:--- | :---- | ----: | -------: | :------------------
B    | Beta  |     1 |   4.0000 | 2000-01-03 00:00:00
A    | Alpha |     8 |   6.0000 | 2000-01-07 00:00:00

For a better understanding I make these two views with half code each one:

Create VIEW products_view_joinA
  AS select t1.code, t1.name, t2.stock, t2.average_price as cost_usd
    from (products t1 left join (
      select t3.stock, t3.code, t3.average_price from transactions t3
      where (t3.date = (
        select t4.date from transactions t4 where (t4.code = t3.code) order by t4.date desc limit 1)
      )) t2 on((t2.code = t1.code)))

SELECT code, name, stock, cost_usd FROM products_view_joinA

code | name  | stock | cost_usd
:--- | :---- | ----: | -------:
B    | Beta  |     1 |   4.0000
A    | Alpha |     8 |   6.0000

And

Create VIEW products_view_joinB
  AS select t1.code, t1.name, t2.date as last_buy
    from (products t1 left join (
      select t3.code, t3.date from transactions t3
      where (t3.date = (
        select t4.date from transactions t4 where (t4.code = t3.code and t4.mode = "Buy") order by t4.date desc limit 1)
      )) t2 on((t2.code = t1.code)))

SELECT code, name, last_buy FROM products_view_joinB

code | name  | last_buy
:--- | :---- | :------------------
A    | Alpha | 2000-01-07 00:00:00
B    | Beta  | 2000-01-03 00:00:00

The output works fine, but the code itself has some duplicated lines that I would like to remove, I think that would also increase its speed.

(Getting rid of some redundant parens, plus wrapping and indenting:)

Create  VIEW products_view_joinA_and_JoinB AS
select  t1.code, t1.name, t2.stock, t2.average_price as cost_usd,
        t5.date as last_buy
    from  (products t1
    left join  ( SELECT  t3.stock, t3.code, t3.average_price
                    from  transactions t3
                    where  t3.date =
                          ( SELECT  t4.date
                                from  transactions t4
                                where  t4.code = t3.code
                                order by  t4.date desc
                                limit  1
                          )
               ) t2  on t2.code = t1.code
    left join  ( SELECT  t6.code, t6.date
                    from  transactions t6
                    where  t6.date =
                          ( SELECT  t7.date
                                from  transactions t7
                                where  t7.code = t6.code
                                  and  t7.mode = "Buy"
                                order by  t7.date desc
                                limit  1
                          )
               ) t5  on t5.code = t1.code
          )

transactions needs an index on (code, date), but you effectively have such since InnoDB tacks the PK on the end of any secondary key.

Other indexes needed:

transactions:  INDEX(code, mode, -- in either order
                     date)  -- (for t7)

If you are using an old version of MySQL, LEFT JOIN ( SELECT ... ) is problematic since it would repeatedly execute the derived table (that subquery).  5.6 improves on it.  Ditto for having two JOIN ( SELECT ... ) in a query.

Do you need LEFT?

VIEWs are syntactic sugar, and rarely (perhaps never) provide any performance.  Sometimes, they make performance worse.  So, experiment with the query outside a VIEW, then see if it still works as well in a VIEW.

If you are using MySQL 8.0 (or MariaDB 10.2), you could consider CTEs and use WITH to introduce the common code.

Other approaches to redundant code:

SELECT @var := ... FROM ...;  -- to compute a single value that is needed repeatedly.

or...

CREATE TEMPORARY TABLE t  SELECT ...;  -- to build a table that you need to use twice.


SSRS: http://localhost/ReportServer accessible but no report listed I deployed SSRS on Windows 10 in the right way I think, at least, http://localhost/ReportServer is accessible on Internet Explorer.

As you can see in the picture below:

I created a report using SQL Server Data Tools
The report is there, I can also see the preview
The Target Server URL is http://localhost/ReportServer
But if a visit http://localhost/ReportServer there is no report

How is this possible?

The solution was slightly easy: right click on the project > deploy.

Strange there isn't enough info on internet about a so simple glitch. :)


Oracle extents - How can I have so many with this small size? This is a different question than the one with the almost-identical title.

Again, this is more curiosity about how Oracle Database works than anything I need to change (yes I know multiple extents are fine, I read Tom Kyte too).

I have a table with the following characteristics:

Initial Ext: 1.44GB
Next Ext:    1MB
Num Extents: 26
Size:        4.18MB

In this case, I got the size by scanning 10% of the table rows.  With an initial extent size so high, and the data so low, how did further extents get added?  I am certain that the table did not become huge, then shrink drastically back down again.

Thank you.

The data you're getting from Toad appears to be incorrect or, at least, misleading.  If you are using a locally managed tablespace with automatic extent allocation, Oracle will determine your initial and next extent sizes automatically.  In 11.2, the first 16 extents are going to be 64k in size (for a total of 1 MB).  The next 63 extents are going to be 1 MB in size.  So if you have 26 extents, that implies that the table occupies 11 MB of space on disk.  An initial extent of 1.44 GB makes no sense and a size of 4.18 MB seems rather low if you're saying it hasn't shrunk.

What does

SELECT tablespace_name,
       extent_id,
       bytes/1024 kb
  FROM user_extents
 WHERE segment_name = <<name of table>>

show you?  If you have 26 extents, you should see that extents 0-15 are 64 kb in size and extents 16-25 are 1 MB in size.

SELECT SUM(bytes)/1024/1024 size_in_mb
  FROM user_extents
 WHERE segment_name = <<name of table>>

will show you the total size of the table's extents on disk.


Is it better to start with a smaller INT types and grow as needed? I am designing a new SQL Database for my ASP.net web application and I can foresee that some of the ID columns will get very large in the feature need INT and BIGINT but that will not be for a few years. With Entity Framework in ASP.net it is fairly easy to change column data types. So I was wondering, from a design point and performance point, is it better to start off using the smaller column types like TINYINT and SMALLINT and grow to INT and BIGINT when the time finally comes?

I would suggest right-sizing your columns from the get-go.  You need to take into account expected growth of the data versus the life of the application.  If it will take 100 years for your integer data to require an INT datatype, then you can likely stick with a SMALLINT if you think your application likely won't live that long.  If it will take only a few years to get there, then it would be easier to just handle that right now rather than risk an application failure when the upper limit of SMALLINT is reached or to risk forgetting to fix it when it gets close.

If you don't, your application will likely fail at some point, likely in the middle of the night or when you're least expecting it!  Or will fail for someone else who is tasked with maintaining your application.

If the users can live with having your application be down while you determine that your data type has exceeded it's size and take steps to fix it, then you can wait. I would bet that is not an acceptable scenario though and you would like to keep the application up time as close to 100% as possible.

Don't wait for "someday" to arrive, get it right today!


Oracle Database Block Size One of our Production database has a rather peculiar block size of 16KB. This database was created about 2 years ago and at that time the size of the db was approx 1TB only.However over the past 2 years the db size has grown to approx 5TB.

The workload is a mix of OLTP (transaction) as well as OLAP (reporting).

This db block size is causing our DB Buffer Pool to be constantly filled up (95%~100% full).The db uses ASM on Raw Devices.

Is the 16KB db block size sub-optimal?Can this large block size be adversely impacting our storage sub-system performance?If so what can be done to remedy the situation?

Noteworthy here is that we do in fact perform huge full table scan for reporting as well as for our application (business logic).The OLTP nature stems from the fact that our application also has transactional (financial transaction) module.

This db block size is causing our DB Buffer Pool to be constantly
  filled up (95%~100% full)

Your DB Buffer Pool should always be full unless you have recently restarted your server - Oracle will not purge blocks from the cache unless it has to in order to make room for other blocks.

This in itself is not an indication that changing the block size will improve anything. You need to know more about the cache misses.

If the OLTP side of your workload can query any part of your 5TB data, optimizing the buffer pool a little for it will probably not make much difference (but may unnecessarily slow down your big sequential scans that it was presumably optimized for)

On the other hand, if there is a small subset of the data hit regularly, and cache misses for the OLTP workloads can be substantially reduced by reducing the block size (which will depend on whether the reads are really scattered across blocks or not), then perhaps the benefits would be significant.

In the end you will have to test and find out :)


save/export PGAdmin server list configuration settings I have a ton of configured connections listed in the Server Groups, is there a way I can save this? not just save the passwords but the Server Group Settings

Under Windows, pgAdmin4 stores the server list in the users directory in a SQLLite 3 format. Here:

%your-user-home%\AppData\Roaming\pgAdmin\pgadmin4.db


How to understand "NULL NOT IN" when the set is empty? The SQL in this question is for Oracle. But you can simply remove FROM dual to make it work on SQL Server (which returns the same result as Oracle).

I have the following SQL:

SELECT 1
FROM dual
WHERE NULL IN (SELECT 1 FROM dual WHERE 1 = 0);

SELECT 1
FROM dual
WHERE NULL NOT IN (SELECT 1 FROM dual WHERE 1 = 0);

which returns an empty set and 1 respectively. When we substitute some other value for NULL, we got the exact same result.

SELECT 1
FROM dual
WHERE 33 IN (SELECT 1 FROM dual WHERE 1 = 0);

SELECT 1
FROM dual
WHERE 33 NOT IN (SELECT 1 FROM dual WHERE 1 = 0);

So how does the comparison between NULL and the empty result set work here?

Lets see what happens with these two conditions:

WHERE NULL IN (SELECT 1 FROM dual WHERE 1 = 0);

WHERE NULL NOT IN (SELECT 1 FROM dual WHERE 1 = 0);

since the subquery yields an empty table, we can write them in pseudocode:

WHERE NULL IN (<empty_table>);

WHERE NULL NOT IN (<empty_table>);

So the first asks whether some (NULL) value is IN an empty table. The result of such condition is always FALSE, independent on whether the value is null or not, since the table is empty.

Applying the same reasoning in the second condition, it is always TRUE, again independent of the value.

It's easy to confirm in Postgres what the result value of these conditions are, as we have a boolean type. See dbfiddle.uk, where the first shows f (FALSE) and the second shows t (TRUE).

As a result of the above, when you run your two queries, they become:

SELECT 1
FROM dual
WHERE FALSE ;

giving an empty result (correct, since the WHERE condition is FALSE)
and

SELECT 1
FROM dual
WHERE TRUE ;

giving one row (correct again).


Postgres view role permissions I am trying to work out how database roles work.
Here is my user case...
create role user1 login password 'user1';

create schema authorization user1;

CREATE ROLE DEV_ROLE;

grant connect, temporary on database test to DEV_ROLE;
GRANT ALL ON SCHEMA manager TO DEV_ROLE;

Now how do i view the all the grants assigned to 'DEV_ROLE'? This is possible in Oracle but trying to work out the same here.
Do appreciate your reply.
Ta

There is no way to do that in PostgreSQL; you have to look at all ACLs on all objects.
Perhaps an extension like pg_permissions can be useful.
One other crude method I can think of:
BEGIN;
DROP ROLE dev_role;
ROLLBACK;

The DROP ROLE will give you a list of permissions it can find that are granted to the role.


Unlock lock in a db table I want to unlock the locks obtained on a table. I tried killing the session but i dont have the dba privilege. Is there any way to do it? Executing COMMIT or ROLLBACK afterward didn't help, because since I started Oracle SQL Developer I got a new session ID. After a while Oracle removed the inactive sessions and locks, but I would like to have a way to remove the locks as a non-admin user

There is no way for non-dba sessions to kill other sessions, or interfere with their locks in any way. Think about it. That would be a security disaster.

You need to issue your commit or rollback in the same session that launched the query. If you lose the session, you must ask your DBA to kill it for you.


How to solve 'tuple concurrently updated' error? The following query is performed concurrently by two threads logged in with two different users:

WITH raw_stat AS (
    SELECT
       host(client_addr) as client_addr,
       pid ,
       usename
    FROM pg_stat_activity
    WHERE usename = current_user
)
INSERT INTO my_stat(id, client_addr, pid, usename)
    SELECT nextval('mystat_sequence'), t.client_addr, t.pid, t.usename
    FROM (
        SELECT client_addr, pid, usename
        FROM raw_stat s
        WHERE
            NOT EXISTS (
               SELECT NULL
               FROM my_stat u
               WHERE current_date = u.creation
                   AND s.pid = u.pid
                   AND s.client_addr = u.client_addr
                   AND s.usename = u.usename
            )
    ) t;

From time to time, I get the following error:

tuple concurrently updated

I can't figure out what throw  this error and why this error is thrown. Can you shed a light ?

Here is the sql definition of the table my_stat.

CREATE TABLE my_stat
(
  id bigint NOT NULL,
  creation date NOT NULL DEFAULT current_date,

  client_addr text NOT NULL,
  pid integer NOT NULL,
  usename name NOT NULL,
  CONSTRAINT mystat_pkey PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);

I manage to solve my problem by changing my query to this one:

INSERT INTO my_stat(id, client_addr, pid, usename)
    SELECT nextval('mystat_sequence'), client_addr, pid, usename
    FROM (
        SELECT
           host(client_addr) as client_addr,
           pid ,
           usename
        FROM pg_stat_activity
        WHERE usename = current_user
    ) s
    WHERE
        NOT EXISTS (
           SELECT NULL
           FROM my_stat u
           WHERE current_date = u.creation
               AND s.pid = u.pid
               AND s.client_addr = u.client_addr
               AND s.usename = u.usename
        );

I think something happened under the hood right from the Postgresql internals but I can't figure out what ...


Current isolation level in oracle How to get the current (default) transaction isolation level in oracle?

Using the query from the SO answer Vincent Malgrat referenced, here is how you can get the transaction isolation level for the transaction in progress:

SELECT s.sid, s.serial#,
   CASE BITAND(t.flag, POWER(2, 28))
      WHEN 0 THEN 'READ COMMITTED'
      ELSE 'SERIALIZABLE'
   END AS isolation_level
FROM v$transaction t
JOIN v$session s ON t.addr = s.taddr AND s.sid = sys_context('USERENV', 'SID');

If you are not already in a transaction you can start one with the following:

declare
   trans_id Varchar2(100);
begin
   trans_id := dbms_transaction.local_transaction_id( TRUE );
end;
/

It seems like there would be an easier way than this.  I don't know how to get the default isolation level for the session if that is what you are looking for.


Recreate indexes and constraint when redefining table with DBMS_REDEFINITON I have a table A witht the following characteristics:

multiple indexes
constraints (null, unique, check, foreign key)
2 triggers (before update)
primary key

If I were to redefine the table with dbms_redefintion in order to change storage, do I have to recreate the triggers, constraints and trigger pointing to the interim table or does this go automatically with only having to rename them once is it is redefined?

If you are only redefining storage you can do that without redefining the table. You can alter the table definition.  Rearranging column orders requires redefinition.  Most other changes can be done by altering the table.

If you do redefine the table you need to define the constraints and indexes you want on the new table.  These will need new names.  Some objects can be renamed later.

If you want to rebuild the table with the new storage, you can do that with an online move.  If the new storage definition does not include a new tablespace, you will need to move it twice, once to another tablespace, and then back into the original tablespace.


How to get SEEK accessing converted ID via view Assume I have a table:

-- just for test purposes
CREATE TABLE SomeTable (
    ID INT IDENTITY(1,1) NOT NULL CONSTRAINT PK__SomeTable__ID PRIMARY KEY CLUSTERED
    ,SomeColumn1 NVARCHAR(50) NULL
    ,SomeColumn2 DATETIME NULL
    );

-- populate table with some rows
INSERT INTO SomeTable DEFAULT VALUES;
GO 1000

Because a third-party application there is a view converting ID column of a table from INT to NVARCHAR (assume it's a must):

CREATE VIEW ThirdPartyView AS
SELECT
    ID = CAST(ID as NVARCHAR(10))
    ,C1 = SomeColumn1
    ,C2 = SomeColumn2
FROM SomeTable;

Then when I access one row by ID, I get an INDEX SCAN:

SELECT *
FROM ThirdPartyView
WHERE ID = N'1'

I understand why.

What can I do to get INDEX SEEK outside of a query?

EDITORIAL / Circumstances:

base table (SomeTable) definition could NOT be changed (cannot add columns)
view has to have the same columns (cannot add columns)
define any indexes is possible
I can make the view an indexed view but prefer to avoid that option
The other side expects the data type of the ID column is NVARCHAR. I have to meet this conditions, unfortunately. Or I've been told to meet them. Probably their application would fail if it's not.

The issue is that your query through the view is the same as doing

SELECT *
FROM SomeTable
WHERE CAST(SomeTable.ID as NVARCHAR(10)) = N'1'

Pretty much any CAST of a column in a predicate will render that predicate unsargable. The only exceptions that I am aware of are a CAST of datetime column to date and VARCHAR to NVARCHAR under some collations.

There is no such exception for NVARCHAR to INT.

You might hope that it would do something like

WHERE SomeTable.ID = TRY_CAST('Your search string' as int)

but it is not as simple as that. If your search string is '1' the two would return the same results but for the search string '¹' (superscript 1) the casting to int fails but the string comparison compares equal under some collations. Conversely for the search string ' 1' (with leading space) the int cast and comparison would discard the leading space and compare equal but the string comparison would compare unequal (and similarly with empty string which is cast to 0 when converted to int)

Possible solutions

In general you could create either an indexed view or a new index on the base table referencing a computed column but both seem quite suboptimal compared with removing the CAST from the view so the existing index can be seeked.

Computed Column

You could create a computed column on SomeTable with definition CAST(ID as NVARCHAR(10)) and then index that.

ALTER TABLE SomeTable ADD strID AS CAST(ID as NVARCHAR(10));

CREATE INDEX IX ON SomeTable (strID ) INCLUDE (ID, SomeColumn1, SomeColumn2);

SELECT *
FROM ThirdPartyView
WHERE ID = N'1';

Indexed view

Given the restrictions in the question the computed column idea seems ruled out. An alternative will be to create an indexed view but likely you will need to change the query text to get this to work.

In all editions except Enterprise Edition the NOEXPAND hint will be needed to get the indexed view to be matched.
If you are on Enterprise Edition the automatic matching in principle would work whether you indexed the original view or created a copy...
... but index view matching will only be considered at later stages of optimisation. If the query is cheap enough optimisation will end before it gets to that point. In your case in particular you will also need to fight against trivial plan. I added a million rows of data to the table and the indexed view still wasn't hit - as the plan was below the cost threshold for parallelism and it didn't proceed to further optimisation phases beyond trivial.


How to update a table by count from another table? I have two tables as

CREATE TABLE country_statistics
(
id int(11) NOT NULL AUTO_INCREMENT
us int(11),
uk int(11),
ca int(11),
PRIMARY KEY(id)
) ENGINE=InnoDB DEFAULT

CREATE TABLE entries
(
id int(11) NOT NULL AUTO_INCREMENT
list varchar(255),
PRIMARY KEY(id)
) ENGINE=InnoDB DEFAULT

I want to search for the occurrence of each country in the list column. I use multiple subqueries to do so

UPDATE country_statistics
us = (SELECT COUNT(*) FROM entries WHERE list LIKE '%United States%',
uk = (SELECT COUNT(*) FROM entries WHERE list LIKE '%United Kingdom%',
ca = (SELECT COUNT(*) FROM entries WHERE list LIKE '%Canada%'
WHERE id = 'xx'

How can I reduce the number of subqueries, and count all values in one subquery, then updating all columns?

You can use JOIN syntax in UPDATE and conditional count in the subquery

UPDATE country_statistics s CROSS JOIN
(
  SELECT SUM(CASE WHEN list LIKE '%United States%' THEN 1 ELSE 0 END) us,
         SUM(CASE WHEN list LIKE '%United Kingdom%' THEN 1 ELSE 0 END) uk,
         SUM(CASE WHEN list LIKE '%Canada%' THEN 1 ELSE 0 END) ca
    FROM entries
) q
   SET s.us = q.us, s.uk = q.uk, s.ca = q.ca
 WHERE id = 1

Here is SQLFiddle demo


How to identify connections using implicit transactions? I'm investigating a blocking issue that I believe is related to implicit transactions not being committed or rolled back.

How can I identify connections that have turned the implicit transactions setting on?

Well to answer your specific question, you should be able to see that in the existing connections and audit:login events in SQL Server Profiler/Tracing.

I would actually start by trying to investigate who the blockers are and then dig in, however. You may have better luck in actually tracking down the blockers if they actively block and you see the blocking in action.

You should be able to do that by either querying the dynamic management view - sys.dm_os_waiting_tasks (described here) or installing and using Adam Machanic's sp_whoisactive (a procedure I recommend you install anyway)


is it possible to pass the message from sp_add_alert to the job? sp_add_alert stored procedure can react to different system messages and execute a job in response. It also can notify a person with the message text by email, pager of net send.

But how do I pass the sysmessage message (corresponding to the event that caused the alert) not to a person but to the job that is executed in response to the alert?

Let's consider this message:

select [description] from sysmessages where msglangid = 1033 and error = 829

This will yeild:

  Database ID %d, Page %S_PGID is marked RestorePending, which may
  indicate disk corruption. To recover from this state, perform a
  restore.

I'd like to receive this message in the job, so the job knows what %d and %S_PGID caused a problem.

This article gives the answer. Basically the message is accessible by using SQL Server Agent Tokens and Macros.

In particular this Job Step that is calling a stored procedure worked for me nicely:

exec ProcessMyAlert '$(ESCAPE_SQUOTE(A-MSG))'

Note that I had to

  right-click SQL Server Agent in Object Explorer, select Properties,
  and on the Alert System page, select Replace tokens for all job
  responses to alerts to enable these tokens.

As per the linked article. In addition I had to restart SQL Server Agent so that this setting come into effect - it did not work until I restarted it, the article does not mention the restart however.


Merging a Select query into a view I am using DB2. So, I have a view which is working fine. It is :

Select
  Atr.Ssn_Tx
 ,Names.First_Na
 ,Names.Middle_Na
 ,Names.Last_Na
 ,Reff.Ref_Cd_Desc_Tx Suffix_Na
 ,MBR.PRSN_MBRSH_ID

 From Hix.T_Enrt En
 Inner Join Hix.T_Prsn_Enrt Pe   On En.Enrt_Id = Pe.Enrt_Id
                                And En.Enrt_Status_Cd In (428,439, 430 )
                                And Pe.Active_In = 'Y'
                                And (Pe.Retro_In Is Null Or Pe.Retro_In = 'N')
 Left Outer Join Hix.T_Aptc Ap On En.Enrt_Id = Ap.Enrt_Id
 Left Outer Join Hix.T_Prsn_Addl_Attr Atr  On Pe.Prsn_Mbrsh_Id = Atr.Prsn_Mbrsh_Id
 Left Outer Join
    (
                    Select nAc.Prsn_mbrsh_Id,  First_Na , Middle_Na , Last_Na, Suffix_Cd
                     From Hix.T_Prsn_Name Na, Hix.t_prsn_mbrsh_prsn_name_ac nAc
                     where na.prsn_name_id = nAc.prsn_Name_Id
    ) Names      On Pe.prsn_mbrsh_id = Names.prsn_mbrsh_id
Left Outer Join Hix.T_Ref_Data Reff On Names.Suffix_Cd = Reff.Ref_Data_Id
Left Outer Join Hix.T_Ref_Data Re On Atr.GENDER_CD = Reff.Ref_Data_Id
Left Outer Join Hix.T_PRSN_MBRSH MBR on MBR.PRSN_MBRSH_ID =  Pe.PRSN_MBRSH_ID

Also, I have a select query which is also working just fine (a new column is created in this query). The select query is:

SELECT (CASE WHEN FILLING_STATUS_CD IN (2061,2062,2063)
               OR  (FILLING_STATUS_CD=2063 AND PRIM_TX_FILER_IN ='Y')
               THEN 'PRIMARY'
             WHEN FILLING_STATUS_CD=2063
               AND PRIM_TX_FILER_IN ='N'
               THEN 'SPOUSE'
             WHEN FILLING_STATUS_CD IN (2066, 2068)
               THEN 'DEPENDENT'
             ELSE   'Not sent to FDSH'
             END) AS FILTER_TYPE_CD
    FROM HIX.T_PRSN_MBRSH_TAX
    WHERE TAX_FL_YR_NB=YEAR(CURRENT DATE)-2

Now I have to make this select query a part of the view. I want to make a view which does all the work that my view and select query are doing individually.

Just keep adding to your list of LEFT JOINs, like this:

Select
  Atr.Ssn_Tx
 ,Names.First_Na
 ,Names.Middle_Na
 ,Names.Last_Na
 ,Reff.Ref_Cd_Desc_Tx Suffix_Na
 ,MBR.PRSN_MBRSH_ID
 ,(CASE WHEN Tax.FILLING_STATUS_CD IN (2061,2062,2063) OR  (Tax.FILLING_STATUS_CD=2063 AND Tax.PRIM_TX_FILER_IN ='Y') THEN 'PRIMARY'
             WHEN Tax.FILLING_STATUS_CD=2063 AND Tax.PRIM_TX_FILER_IN ='N' THEN 'SPOUSE'
             WHEN Tax.FILLING_STATUS_CD IN (2066, 2068) THEN 'DEPENDENT'
             ELSE 'Not sent to FDSH'
             END) AS FILTER_TYPE_CD
 From Hix.T_Enrt En Inner Join Hix.T_Prsn_Enrt Pe On En.Enrt_Id = Pe.Enrt_Id And En.Enrt_Status_Cd In (428,439, 430 ) And Pe.Active_In = 'Y' And (Pe.Retro_In Is Null Or Pe.Retro_In = 'N')
Left Outer Join Hix.T_Aptc Ap On En.Enrt_Id = Ap.Enrt_Id
Left Outer Join Hix.T_Prsn_Addl_Attr Atr On Pe.Prsn_Mbrsh_Id = Atr.Prsn_Mbrsh_Id
Left Outer Join
    (
                    Select nAc.Prsn_mbrsh_Id,  First_Na , Middle_Na , Last_Na, Suffix_Cd From Hix.T_Prsn_Name Na, Hix.t_prsn_mbrsh_prsn_name_ac nAc
                                                   where na.prsn_name_id = nAc.prsn_Name_Id
    ) Names On Pe.prsn_mbrsh_id = Names.prsn_mbrsh_id
Left Outer Join Hix.T_Ref_Data Reff On Names.Suffix_Cd = Reff.Ref_Data_Id
Left Outer Join Hix.T_Ref_Data Re On Atr.GENDER_CD = Reff.Ref_Data_Id
Left Outer Join Hix.T_PRSN_MBRSH MBR on MBR.PRSN_MBRSH_ID =  Pe.PRSN_MBRSH_ID
LEFT OUTER JOIN HIX.T_PRSN_MBRSH_TAX Tax ON Tax.PRSN_MBRSH_ID = Pe.PRSN_MBRSH_ID AND tax.TAX_FL_YR_NB=YEAR(CURRENT DATE)-2

Assumption: your HIX.T_PRSN_MBRSH_TAX table has a PRSN_MBRSH_ID column (most likely this is the case)


Filter on time difference between current and next row I have a database table with the following structure:

id     | created
-------+---------------
1      | 2018-07-23 00:01:00
2      | 2018-07-23 00:02:00
...

Now I'd like to return all rows where the time difference to the next row is greater than e.g. 10 seconds.

I've tried with the Postgres window function, but couldn't get it to work, e.g.

SELECT
  created,
  created - lag(created, 1) OVER (ORDER BY created) delta
FROM tablename
where created - lag(created, 1)  OVER (ORDER BY created) > interval '10 seconds'
ORDER BY created;

The error I get is that I'm not allowed to use window functions in the WHERE clause. How to get this right?

Consider the sequence of events in a SELECT query, as explained here:

Best way to get result count before LIMIT was applied

Window functions operate on the result set after WHERE clauses have been applied. It would be a logical contradiction to have a window functions in a WHERE clause. Like mustaccio said: use a subquery - or a CTE - to filter on the result of a window function.

But also get the logic right:

  where the time difference to the next row is greater than e.g. 10 seconds.

SELECT *
FROM  (
   SELECT created
        , lead(created) OVER (ORDER BY created) - created AS delta
   FROM   tbl
   ) t
WHERE  delta > interval '10 sec'
ORDER  BY created;


MySql Lock Tables Blocking Logic The MySQL documentation (5.5) for Lock tables states:

Let's say that I'm trying to lock table1 and table2 in a single query, but table2 is already locked. Will MySQL hold onto a lock on table1 and block until it can lock table2 or will it only hold onto a lock on table1 when it can get a lock on both tables?

In case it matters, assume the InnoDB storage engine.

Update
I just found the following statement on the same page:

  When the session has gotten the WRITE lock and is waiting to get the lock for the next table in the lock table list, all other sessions wait for the WRITE lock to be released.

Is this confirming that MySQL will block whilst holding the lock on table1? If that is the case, is there a way to query MySQL such that it will immediately return with a failure rather than block if it fails to grab all of the table locks?

Do not use LOCK TABLES with InnoDB unless you have a very special need.

Instead, learn about "transactions" and use BEGIN...COMMIT to effect most "locking" needed.  See also SELECT ... FOR UPDATE....


inno_db_buffer_pool_size and key efficiency I have the following metrics from mysql workbench

After I have set innodb-buffer-pool-size to 6G, according to this answer's query on how to determine the recommended size.

While the used buffer usage was between 87% and 93% before I did the change in my.cnf (running with default values) it dramatically decreased now, so I'm wondering if 6G was too much. Especially because the key efficiency was between 97% and 99% before the change, which has lost some percents now.

Should I bother more about key efficiency or buffer usage? Should I decrease the buffer usage again?

The server specs are

Intel® Xeon® E5-1650 v3
Hexa-Core Haswell Hyper-Threading-Technologie
128 GB DDR4 ECC RAM
2 x 480 GB SATA 6 Gb/s Data Center Series SSD

If your tables are InnoDB (not MyISAM), innodb_buffer_pool_size should be about 70% of available RAM.  However it does not need to be much bigger than the total Data_length and Index_length for all your tables.

It does not hurt to make that tunable "too big" unless you need the RAM for something else.

If you would like further analysis, please provide SHOW VARIABLES and SHOW GLOBAL STATUS.  From that, we can sort out which engine the vague "key efficiency" refers to, plus check other things.  (I have automated about 200 checks.)

Also, it is unclear whether "InnoDB writes per second" is INSERTs or disk writes.  Since you have SSDs, it could be either.