texts:
I have a Fox Pro database (one DBC and a bunch of DBF files) that I want to import into SQL via the Import and Export wizard.

I downloaded the FoxPro ODBC driver.  I managed to get to the screen that shows all table name.  I can even preview the data, except if I try to go next I will get

Column information for the source and the destination data could not be retrieved, or the data types of source columns were not mapped correctly to those available on the destination provider.

  "somecolumn" -> [dbo].[somecolumn]:

  Cannot load the column attributes from the schema using the current provider descriptor:

  The column attribute "ORDINAL_POSITION" is not valid.

  Please verify the ProviderDescriptors.xml file contains the data provider descriptor that matches the schema of the used data provider.

I understand I need to fix the ProviderDescriptors.xml file, which has this line

OrdinalPositionColumnName="ORDINAL_POSITION"

But what can I put in there?  I have guessed the following

POSITION
ORDINALPOSITION
ID
INDEX
OFFSET

All of the above doesn't work.


texts: SSAS : are dimensions with only Id and Name attributes useless?

texts:
I wonder if it is worth creating a dimension with only one column (e.g. Name) in addition to its Id Column. This dimension would be referenced by a fact containing a reference to the Id attribute of the above dimension.
Wouldn't be better to include directly the Name in the fact (the dimension is referenced only one time by the fact)?
And my second question is am I expected to create an MDX query manually in the case of the name directly included in the fact ? Won(t be the queries more difficult to write?
thank you.


texts: What happens if a client connection is killed while holding a lock?

texts:
What happens if a client establishes a transaction, locks a row or table, and the client process crashes before it has a chance to rollback or commit the transaction? What, if anything, does SQL Server do with the transaction?

Does SQL Server have a heartbeat mechanism to detect that the socket got killed?
Upon detecting that a socket got killed, does SQL Server rollback the transaction automatically? Or does the row/table lock persist (causing other clients to deadlock)?

Please provide authoritative sources to back up your answer.

UPDATE: The client process is crashing. I am not talking about someone invoking the KILL command.


texts: Issues with GRANTing permissions to AD group

texts:
I have couple of Active directory groups authenticated to access my RDS SQL Server instance. I have granted access to a AD group to perform ALTER ANY connection with GRANT.

When I log in as a user of that AD group and try to GRANT ALTER any connection the SQL stmt fails : GRANTOR does not have GRANT permission

But when I exclusively create a SQL login for the user in the group and give permission to that user and try the GRANT it works.

Is it supposed to work like that? If so, then what's the point of having a group? I'm not able to find any info on this. If you know any Microsoft document that explains this behavior it would be great.

Similar issue : AD User does not inherit the permission from AD Group - for a LOGIN


texts: avoiding unique key collisions across tables

texts:
This might be related to the idea of data warehousing, but i'm not sure, though it is extreme theory.

We have a table where records get written to first, let's call it tbl_recording which looks like:

CREATE TABLE tbl_recording (
  id INT NOT NULL AUTO_INCREMENT,
  name VARCHAR(45) NULL,
  registration VARCHAR(45) NULL,
  legacyid VARCHAR(45) NOT NULL,
  UNIQUE INDEX legacyid_UNIQUE (legacyid ASC),
  PRIMARY KEY (id));

Then, we have yearly tables based off of tbl_recording so like tbl_recording_2012, tbl_recording_2013 etc etc,  their structure is the same.

We also have tbl_recording data off to the yearly table every hour (insert to yearly, delete from recording).  Now, the problem I see is that the uniqueness of legacyid is not remembered across all these tables. So, while we generate a hash for the legacyid, there could be a collision (even though terribly unlikely) at some point:

After data is removed from tbl_recording the same hash could be generated and re-entered into tbl_recording, thus causing a blockage further down the line when trying to insert into the yearly table
Between years, the same hash could be generated for 2013 and 2014 EDIT: This would never happen if i append the year to the hash

The best way I've come up with to mitigate against this is to keep a hashtable,  one that stores all hashes created, and is checked against before a record is inserted, and then that fresh hash inserted into the hashtable.

Are there better solutions to this or is that the only real solution?

edit:  I've created an SQL Fiddle of an example of how my system works

http://sqlfiddle.com/#!2/231fa/1

pretend that: tbl_data_2012 will never be written to again.  tbl_recording is constantly being written to but every hour or so, it's the contents it's being emptied to tbl_data_2013.  tbl_recording has already dumped it's contents to tbl_data_2013 so when the 4th record was written there was nothing in there previously, thus it has created a new legacyid, however that legacyid already exists in tbl_data_2013


texts: show users doesn't show anything, although it has users

texts:
So I'm using MongoDB, and this is my first time dealing with a VPS on my own, I'm trying to view the users i have on the website. I know for a fact that there are signed up users, because I can signup & Login and it all works.
I Also tried to connect to the database from MongoDB Compass but failed.


texts: How does the log reader process bulk updates

texts:
Key Problem

The latency we are experiencing isn't applying the changes from the Distributor to the Subscribers (if we watch Replication Monitor it's latency is usually <1 second), it is the time that a transaction committed to the publisher database takes to get to the distributor.

Part of the issue is that we have several filters applied to one of our most volatile tables, and on every update/insert/delete LogReader.exe needs to check which publication to put that record in based on any filters applied.

From tests we've done it seems that each additional filter that is applied it can exponentially increase the time that the LogReader takes to process each transaction.

(edit 23/06/2011: added more detail about the filters)

Background

In our setup of Replication we have several publication with filters on a highly volatile table (average of 1.5million transactions in a 2hr period).  During heavy periods this can result in the LogReader running at a latency of 20 seconds (usually < 1 second)

We have identified several areas of improvement (reducing number of filters, reducing number of updates, farming out the processing etc..), one potential area of improvement is changing how the updates are applied.

An example table (to aid explanation)

myTable
----------------
myID         int
myGroupID1   int
myGroupID2   int
Suspended    bit
FilterFlag1  int
FilterFlag2  int
FilterFlag3  int

For replication to illustrate this we would have 5 publications of [myTable]:

Publication  Filter
-----------  ------
NoFilter1    [all records]*
NoFilter2    [all records]*
Filter1      FilterFlag1 = 1
Filter2      FilterFlag2 = 1
Filter3      FilterFlag1 = 1 AND FilterFlag2 = 1

Other tables are combined into these publications which is why the same table with identical filters (none) are in more than one publication.

Current update process

The majority of applications that update this table do so by looping through their collection of objects, applying the change to that single object then committing their change to the database before moving onto the next object.

From a DB trace perspective this means we get up to 120 update statements when the change occurs

Example

update [myTable] set Suspended = @Suspended
where myID = @pID

Proposed update process

As the object collections are actually based around the group ID's, one potential improvement is to do a bulk update (rather than individual updates) and then refresh the object collection.  Reducing the number of update statements to 1 or 2 (depending on the business scenarios).

Example

update [myTable] set Suspended = @Suspended
where myGroupID1 = @groupID1
      and myGroupID1 = @groupID2

Impact on LogReader?

From an app processing perspective doing a single update makes sense to me (fewer round trips between the app and the database = quicker), however I'm not sure how the LogReader will treat both scenarios, as it needs to process each record updated by the transaction..

Will the LogReader process those records faster or slower on a bulk update?


texts: What should be the command for following role creation requirement?

texts:
I need to create a role where the assigned user can access only the 'AdventureWorksDB" where he will have execute permission to all tables,views and stored procedures in the database. I wrote the below script.

USE AdventureWorks2012;
GO
CREATE USER test1 FOR LOGIN test1;
GO

CREATE ROLE db_executor

GRANT EXECUTE TO db_executor

EXEC sp_addrolemember 'db_executor', 'test1'

But he also needs SQLAgentOperatorRole permission for SQL Agent. But If I am to grant this I have to give him the access to msdb to be able to do this. But if I give the user , access to msdb user can access other databases as well. What could I do to make the user access only one database ad have full access to SQL Agent?


texts: Index fragmentation while continuously processing

texts:
SQL Server 2005

I need to be able to continuously process about 350M records in a 900M record table. The query I'm using to select the records to process becomes badly fragmented as I process and I have a need to stop the processing to rebuild the index. Pseudo data model & query ...

/**************************************/
CREATE TABLE [Table]
(
    [PrimaryKeyId] [INT] IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
    [ForeignKeyId] [INT] NOT NULL,
    /* more columns ... */
    [DataType] [CHAR](1) NOT NULL,
    [DataStatus] [DATETIME] NULL,
    [ProcessDate] [DATETIME] NOT NULL,
    [ProcessThreadId] VARCHAR (100) NULL
);

CREATE NONCLUSTERED INDEX [Idx] ON [Table]
(
    [DataType],
    [DataStatus],
    [ProcessDate],
    [ProcessThreadId]
);
/**************************************/

/**************************************/
WITH cte AS (
    SELECT TOP (@BatchSize) [PrimaryKeyId], [ProcessThreadId]
    FROM [Table] WITH ( ROWLOCK, UPDLOCK, READPAST )
    WHERE [DataType] = 'X'
    AND [DataStatus] IS NULL
    AND [ProcessDate] < DATEADD(m, -2, GETDATE()) -- older than 2 months
    AND [ProcessThreadId] IS NULL
)
UPDATE cte
SET [ProcessThreadId] = @ProcessThreadId;

SELECT * FROM [Table] WITH ( NOLOCK )
WHERE [ProcessThreadId] = @ProcessThreadId;
/**************************************/

Data content...
While the column [DataType] is typed as a CHAR(1), about 35% of all the records equal 'X' with the remainder equaling 'A'.
Of only the records where [DataType] equals 'X', about 10% will have a NOT NULL [DataStatus] value.

The [ProcessDate] and [ProcessThreadId] columns will be updated for every record processed.The [DataType] column is updated ('X' is changed to 'A') about 10% of the time.The [DataStatus] column is updated less than 1% of the time.

For now my solution is to select the primary key of all the records to process into a separate processing table. I delete the keys as I process them so that as the index fragments I'm dealing with less records.

However, this doesn't fit the workflow I want to have so that these data are processed continuously, without manual intervention and significant downtime. I do anticipate downtime on a quarterly basis for housekeeping chores. But now, without the separate processing table, I can not get through processing even half of the data set without the fragmentation becoming so bad as to necessitate stopping and rebuilding the index.

Any recommendations for indexing or a different data model? Is there a pattern I need to research?
I have full control of the data model and the process software so nothing is off the table.


texts: SQL Server Encrypted Connections - Configuration Manager does not see Certificate

texts:
My goal is to implement encrypted connections on Test SQL Server instance

  First, tried to create self-signed certificate in the Certificates snap in (certlm.msc), Personal > All Tasks > Request New Certificate...

But creation failed, because Test SQL Server machine could not contact (no network connection to) one of the AD servers on which AD Certificate Services are installed.

It can contact some other AD servers, but these do not have AD CS, possibly sysadmin will help to resolve it but not today.

  So I moved on to "New-SelfSignedCertificate" PowerShell cmdlet, which can create self-signed certificates

Tried below ways for creating cert:

New-SelfSignedCertificate -Subject "TEST-SQL-SERVER.domain.com" -DnsName "TEST-SQL-SERVER.domain.com" -CertStoreLocation "cert:\LocalMachine\My"

New-SelfSignedCertificate -Subject "TEST-SQL-SERVER.domain.com" -DnsName "TEST-SQL-SERVER.domain.com","TEST-SQL-SERVER" -CertStoreLocation "cert:\LocalMachine\My"

New-SelfSignedCertificate -Subject "TEST-SQL-SERVER.domain.com" -DnsName "TEST-SQL-SERVER.domain.com","TEST-SQL-SERVER" -CertStoreLocation "cert:\LocalMachine\My" -TextExtension "2.5.29.37={text}1.3.6.1.5.5.7.3.1"

New-SelfSignedCertificate -Subject "TEST-SQL-SERVER.domain.com" -DnsName "TEST-SQL-SERVER.domain.com","TEST-SQL-SERVER" -CertStoreLocation "cert:\LocalMachine\My" -TextExtension "2.5.29.37={text}1.3.6.1.5.5.7.3.1" -KeyAlgorithm RSA -KeyLength 2048

New-SelfSignedCertificate -Subject "CN=TEST-SQL-SERVER.domain.com" -DnsName "TEST-SQL-SERVER.domain.com","TEST-SQL-SERVER" -CertStoreLocation "cert:\LocalMachine\My" -TextExtension "2.5.29.37={text}1.3.6.1.5.5.7.3.1" -KeyAlgorithm RSA -KeyLength 2048

New-SelfSignedCertificate -Subject "CN=TEST-SQL-SERVER.domain.com" -DnsName "TEST-SQL-SERVER.domain.com","TEST-SQL-SERVER" -CertStoreLocation "cert:\LocalMachine\My" -TextExtension "2.5.29.37={text}1.3.6.1.5.5.7.3.1" -KeyAlgorithm RSA -KeyLength 2048 -KeySpec KeyExchange

Each time after generating certificate, right clicked it in Certificates snap in, All Tasks > Manage Private Keys... and granted Read and Full Control permissions to SQL Server's service account

But, in the SQL Server Configuration Manager, each time when I go to SQL Server Network Configuration > Protocols for MSSQLSERVER > Properties, I can not see newly generated certificate on the Certificates tab

P.S. also tried adding "-KeySpec KeyExchange" to my PowerShell command, but Windows Security requests some smart card and I can't proceed further

  Question: what I am missing ?
  How to properly create self-signed certificate that will be visible in SQL Server Confirugation Manager ?


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

Our table design looks like this:

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

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

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

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

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

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

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

We run a query like this:

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

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


texts: Efficiently counting Clicks grouped by Week as well as all sub-totals

texts:
I have a large and slow database 'LargeSlowTable' which records clicks by users. I only have read permission, not write, so I cannot use Global Temporary Tables. I want to count the number of clicks, grouped by week. I also want to count the distinct/unique number of clicks. The following code does this nicely. (There is a reason I have the second Select nested inside the first Select.)

SELECT Week, COUNT(Click), COUNT(DISTINCT Click) FROM
(
SELECT
Week,
Click
FROM LargeSlowTable
WHERE Id = 2
)
GROUP BY Week
ORDER BY Week;

Now, I also want a count of the total number of unique clicks, from the starting date to the present. This is not simply the sum of all unique clicks per week, as those may shows clicks by the same user. Therefore, I need to run another command which ignores the GROUP BY Week part.

Can I do this without calling the LargeSlowTable again?

Finally, I would also like a sub-total per week of the number of unique clicks, from the starting date to that particular week.

Can I do this without calling the LargeSlowTable again?


texts: InnoDB: SELECT COUNT(*) using primary key 25 times slower on Windows

texts:
MySql 5.6, InnoDB. I have a table with 8 million records. It has an integer primary key, and a unique index u1 CHAR(40). It also has a few small fields and a VARCHAR(1000).

SELECT COUNT(*) FROM my_table FORCE INDEX(u1) takes 1.0 s

SELECT COUNT(*) FROM my_table FORCE INDEX(PRIMARY) takes 1.2 s

This is on Linux boxen. But on Windows boxen the times are 2 s and 45 s!

The cnf/ini files are roughly the same. Tweaking buffer sizes etc has been unsuccessful so far. There seems to be a big problem with primary key lookups on Windows. Perhaps a bug in InnoDB on Windows? Or what?


texts: mysql duplicate key error during restoration through mysqldump data

texts:
I have a mysql database which is having one table for geo location and saves latitude and longitude values of users. The fields are as followed

CREATE TABLE `USER_LOCATION` (
  `LATITUDE` float NOT NULL DEFAULT '0',
  `LONGITUDE` float NOT NULL DEFAULT '0',
  `STATE` varchar(50) NOT NULL,
  `COUNTRY` varchar(255) DEFAULT NULL,
  `ADDRESS` varchar(255) DEFAULT NULL,
  `CITY` varchar(45) DEFAULT NULL,
  `DISTRICT` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`LATITUDE`,`LONGITUDE`),
  KEY `latlong` (`LATITUDE`,`LONGITUDE`),
  KEY `longlat` (`LONGITUDE`,`LATITUDE`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Problem 1:- Some time fields get duplicate value and the insertion happens successfully.

Problem 2:- If I have taken mysqldump of data and try to restore with the same duplicate rows , then I receive duplicate PRIMARY KEY error like this.

  ERROR 1062 (23000): Duplicate entry '17.4273-78.3316' for key 'PRIMARY'

Now I am not able to understand that why the first problem happens and why this is detected in problem 2.


texts:
i have simple table and need to group by duplicate items , also i need to get min with holding row i read this tutorial work , but not sum grouped item level
http://dev.mysql.com/doc/refman/5.7/en/example-maximum-column-group-row.html

+------------+------+-------+----------+----+
| descendant | deep | level | ancestor | id |
+------------+------+-------+----------+----+
|          2 |    0 |     1 |        2 |  1 |
|          4 |    1 |     1 |        2 |  2 |
|          4 |    2 |     1 |       45 |  3 |
|          6 |    1 |     1 |     1368 |  4 |
|          6 |    2 |     1 |        6 |  5 |
|          6 |    3 |     1 |   123456 |  6 |
+------------+------+-------+----------+----+

i need  group  descendant and just show min deep row , bellow code work and not add level

SELECT  descendant,deep,ancestor, count(level)  FROM test as s1

         where deep = (SELECT min(s2.deep)
              FROM test s2
              WHERE s1.descendant = s2.descendant  )

 group by descendant

my Considered  result

descendant | deep | ancestor |count(level)

2          |  0   |    2     |   1
4          |  1   |    2     |   2
6          |  1   |    1368  |   3

thanks


texts:
I have 3 table linked by an "inheritance" with Entity Framework.
Sadly, I have poor performance with those query because I can't make any good index.
Here is the tables :

CREATE TABLE [dbo].[PlannedElements](
    [IdPlannedElement] [int] IDENTITY(1,1) NOT NULL,
    [ActualDate] [datetime] NULL,
    [Order] [int] NOT NULL,
    [PlannedDate] [datetime] NULL,
    [TimeSpan] [time](7) NULL,
    [IsCompleted] [bit] NOT NULL,
    [PlanningLineIdPlanningLine] [int] NULL,
    [AddressIdAddress] [int] NULL,
    [CrewIdCrew] [int] NULL,
    [PlannedElementIdPlannedElement] [int] NULL,
    [SuppressionDate] [datetime] NULL,
    [LastUpdateDate] [datetime] NULL,
    [IsActualDateEstimated] [bit] NOT NULL,
    [TrackerIdTracker] [int] NULL,
    [HospitalRoom] [nvarchar](4000) NULL,
 CONSTRAINT [PK_PlannedElements] PRIMARY KEY CLUSTERED
(
    [IdPlannedElement] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[PlannedElements_PlannedFleetElement](
    [IdPlannedElement] [int] NOT NULL,
    [TypeOfFleetElement] [int] NOT NULL,
 CONSTRAINT [PK_PlannedElements_PlannedFleetEl] PRIMARY KEY CLUSTERED
(
    [IdPlannedElement] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[PlannedElements_PlannedBusinessFleetElement](
    [IdPlannedElement] [int] NOT NULL,
    [TypeOfBusinessFleetElement] [int] NOT NULL,
    [JourneyIdJourney] [int] NULL,
    [IsCertified] [bit] NOT NULL,
 CONSTRAINT [PK_PlannedElements_PlannedBusines] PRIMARY KEY CLUSTERED
(
    [IdPlannedElement] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

I would need an index on  JourneyIdJourney, AddressIdAddress but when I use a materialized view SQL Server is never using it;
Do you have any recommendations ?

Thanks !


texts:
I have two indexes on a table that will contain roughly 10-20 million records at a given moment.

Index 1

CREATE NONCLUSTERED INDEX [IX_NC_AccountNumber_UTC] ON [dbo].[LogWindow]
(
    [AccountNumber] ASC,
    [UTC] ASC
)
INCLUDE (   [Site],
    [Application],
    [Version],
    [Machine],
    [Context],
    [Severity],
    [Message],
    [Details]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

Index 2

CREATE NONCLUSTERED INDEX [IX_NC_AccountNumber_UTC_Site_Context_Machine] ON [dbo].[LogWindow]
(
    [AccountNumber] ASC,
    [UTC] ASC,
    [Site] ASC,
    [Context] ASC,
    [Machine] ASC
)
INCLUDE (   [Application],
    [Version],
    [Severity],
    [Message],
    [Details],
    [Type]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

The reason why I am currently using two indexes is because I am issuing two types of queries.  The first query only filters records based upon AccountNumber and UTC.  The second query filters records based upon those same columns, plus the additional Site, Context and Machine columns.

The second index is very similar to the first; it's just covering less columns because now they are part of the key indexed columns.

Does it make sense for me to have the first index when the second one is so similar with additional key columns?  Will SQL Server determine there is a benefit to using the first index over the second?


texts: How to select number of affected rows to variable in function?

texts:
create or replace function test()
returns void as $$
begin
  update tbl set col1 = true where col2 = false;
  -- now I want to raise exception if update query affected more than 2 rows to rollback the update
end;
$$ language plpgsql;

How I can select number of affected rows to a variable in function?


texts:
I have my system working with drinks, and now I'm ready to add burgers and other types of items.

Right now I have 1 orders table with separate item and itemorder tables for each type of item.

This is not sufficient because it is difficult to combine the items into 1 order.

Can you suggest any better way to do it? I'm confused about how to combine them because drinks and burgers have different options, and when I will add more and more items I expect a larger variety of options.

What's the most efficient way to do it?

EDIT 1:

I combined the item tables, combined the itemorder tables, and added a table with the price and surcharge information so that it won't be stored as often.

I feel it can still be improved.

EDIT 2:

I decided to remove all enums (thanks to Vérace and sibert), and I've improved my price structure (thanks to Lennart), but I'm  still trying to determine whether the price table and its relationship are as good as they can be. The itemorder table now refers to priceid so I think it will allow me to correctly retrieve historical itemorders with accurate prices.

I still feel it can be improved.


texts: Add article to transactional publication without generating new snapshot

texts:
Using SQL 2008 R2 transactional replication with pull subscribers, when we add an article, I'd like to avoid having to create an entire snapshot (the db is ~80 GB, so this takes hours).

From this article, I've seen how to do this with a partial snapshot by setting immediate_sync off, but that didn't work for us.

Ideally I'd like to just run this as part of our db script to create the table, so if we want it replicated we do:

Create Table ...
sp_addArticle ...
sp_PushThisToOurSubscribersNow


texts:
My MariaDB doesn't support IN and I cannot seem to write a proper query that uses JOIN and gives the same result. Here's the query with IN

SELECT * FROM topics WHERE topic_id IN
( SELECT DISTINCT(topic) FROM answers
ORDER BY date LIMIT 500 )

How would that query translate to something that doesn't use IN but JOIN (prefrably)?


texts:
I wanted to get some opinions on the index maintenance we currently do at the company where I work. One of our production SQL 2012 clusters that consists of 4 nodes, with two instances on each, has multiple AGs which service some very heavy workloads. Some of the databases in these AGs are 2TB+.
We have a standard daily index maintenance routine that does the usual rebuild vs reorg depending on the fragmentation level, but we also only do reorgs in indexes that are over a certain size because we have seen issues with SYNC latency if these larger indexes were to be rebuilt. Once index maintenance has been performed we then update statistics etc.
This job can sometimes run up to 12 hours+ so it impacts on our critical business hours where we see peak traffic, so we really need to do something to alleviate this.
I've seen quite a bit of commentary recently where it has been suggested that index maintenance isn't always required at all, and I suspect that may be the case for us where we are doing a reorg on large indexes that are only 5% fragmented.
I guess I'd like some ideas on how I could identify indexes where the level of maintenance we do daily isn't necessarily required, other than disabling it and observing the impact, if any.


texts:
I am training in MySQL.
I have database called ships. My goal is => For each class, define the number of ships of this class that were sunk in battles.
Result set: class, number of sunked ships.
Here are the tables from which I wanna get the result:

I tried to resolve my problem in this way:

SELECT classes.class, COUNT(outcomes.ship) as sunks
FROM classes LEFT JOIN outcomes ON classes.class=outcomes.ship
WHERE outcomes.result='sunk'
UNION
SELECT ships.class, COUNT(outcomes.ship) as sunks
                FROM ships
                LEFT JOIN outcomes ON ships.name=outcomes.ship
                WHERE outcomes.result='sunk'

The result set of my query is just: Bismark and Kogo classes but other classes were gone.

But it was not exactly what I wanted to get.
I want to get all classes - even with zero outcomes.result.

What am I going to do to make it works properly?
Here is the table script just in case: ships


texts: How to exporting a databse structure (or many tables) for import

texts:
In TSQL: When right click a table you have the option to Script table as > Create

which creates the table script including the constraints
If you want to port the database or  large portion of the table:

is it possible to have a script create the tables in 1 file and the  FK constraints in another file. The 2 files can then be used to recreate the tables and relationships in another DB


texts: Creating two sub queries using WITH clause

texts:
I am struggling with really complex queries involving two sub-queries using WITH clause as described here.

But it appears to me I cannot use one sub-query inside my main query. I here is my query statement:

WITH trip_labelled AS
(
    SELECT DISTINCT ON (trips_1.trip_id) trips_1.trip_id,
            sum(segments.length::double precision) AS segments_length,
            sum(travelmode_profile.foot * segments.length)::real / GREATEST(sum(segments.length), 1::real) AS foot,
            sum(travelmode_profile.bike * segments.length)::real / GREATEST(sum(segments.length), 1::real) AS bike,
            sum(travelmode_profile.car * segments.length)::real / GREATEST(sum(segments.length), 1::real) AS car,
            sum(travelmode_profile.bus * segments.length)::real / GREATEST(sum(segments.length), 1::real) AS bus,
            sum(travelmode_profile.metro * segments.length)::real / GREATEST(sum(segments.length), 1::real) AS metro
           FROM trips trips_1
             JOIN segments ON segments.session_id = ANY (trips_1.session_ids)
             JOIN travelmode_profile USING (session_id, segment_id)
             LEFT JOIN session USING (session_id)
             LEFT JOIN ( SELECT DISTINCT ON (session_1.user_id) session_1.user_id,
                    response.response AS role
                   FROM session session_1
                     LEFT JOIN response USING (session_id)
                  WHERE response.question_id = 24) demography USING (user_id)
          WHERE segments.movement = true
          GROUP BY trips_1.trip_id
),
   trip_mode AS
(   SELECT trips.trip_id AS trip_id,
       trips.seconds_start,
       trips.seconds_end,
       trips.distance,
         CASE
            WHEN GREATEST(trip_labelled.car, trip_labelled.bike, trip_labelled.bus, trip_labelled.metro, trip_labelled.foot) = trip_labelled.car THEN 'car'::text
            WHEN GREATEST(trip_labelled.car, trip_labelled.bike, trip_labelled.bus, trip_labelled.metro, trip_labelled.foot) = trip_labelled.bike THEN 'bike'::text
            WHEN GREATEST(trip_labelled.car, trip_labelled.bike, trip_labelled.bus, trip_labelled.metro, trip_labelled.foot) = trip_labelled.bus THEN 'bus'::text
            WHEN GREATEST(trip_labelled.car, trip_labelled.bike, trip_labelled.bus, trip_labelled.metro, trip_labelled.foot) = trip_labelled.metro THEN 'metro'::text
            WHEN GREATEST(trip_labelled.car, trip_labelled.bike, trip_labelled.bus, trip_labelled.metro, trip_labelled.foot) = trip_labelled.foot THEN 'foot'::text
            ELSE NULL::text
        END AS travel_mode
   FROM trips
     JOIN trip_labelled USING (trip_id)
  )
  SELECT  session_id,
         COALESCE(gps.gpstime, gslocation.gpstime) AS gpstime,
         COALESCE(gps.lat, gslocation.lat) AS lat,
        COALESCE(gps.lon, gslocation.lon) AS lon,
        COALESCE(gps.alt, gslocation.alt) AS alt,
        COALESCE(gps.speed, gslocation.speed) AS speed,
        COALESCE(gps.acc, gslocation.acc) AS acc
        trip_mode.travel_mode
FROM raw_data.gslocation
FULL JOIN raw_data.gps
    USING (session_id, seconds, millis);

Error:

ERROR:  syntax error at or near "trip_mode"
LINE 53:   trip_mode.travel_mode

I cannot use travel_mode or trip_mode.travel_mode from second sub-query trip_mode in main query. But works on commenting out travel_mode/trip_mode.travel_mode. Where do I go wrong?


texts: Verify which MDS.stg table is related to which MDS model for SSIS data load

texts:
I need to load the data into the MDS staging tables by using SSIS.

The problem is that there are many MDS models, and every one of them has its own staging tables, but they are not named properly (by using model_name.stgTable scheme). They just have numbers at the end.

Could you tell me how to verify which MDS.stg table is related to which MDS model?

Example (staging Status tables for different MDS models):


texts: MongoDB - The connection is not disconnected for a long time

texts:
I am building a system that outputs information such as visit information and page transition of the user who visited the website to MongoDB, The MongoDB connection pool of the server of the web site became MAX and it ceased to MongoDB from the web site.

The MongoDB service is configured with the following configuration.

mongos server#1 service：mongos、config mongos server#2 service：mongos、config、rs01arbiter mongos server#3 service：mongos、config、rs02arbiter node#1 service：rs01(secondary)、rs02(secondary) node#2 service：rs01(primary)、rs02(primary)

※Originally the following state is set, but OS is restarted, primary has dropped to node # 2, but it is left as it is node#1 service：rs01(primary)、rs02(secondary) node#2 service：rs01(secondary)、rs02(primary)

From this state, we restarted the OS of node # 2. rs 01 and rs 02 of node # 1 were promoted to primary. rs01, rs02 of node # 2 demoted to secondary.I confirmed it from the log.

However, it happened that more connections would take more than 1 to 2 hours from OS reboot on node # 2 to expire. And the MongoDB connection pool of the server of the web site became MAX and it became not connected to MongoDB from the web site. Normally it will be out of connection in about 30 minutes at the longest. I thought that node # 1 works as Primary, so I thought it would work normally, but I do not know why the connection is not disconnected for a long time.

If you encounter similar events, please let me know if anyone knows the solution or workaround.

appendix
The version of MongoDB is "MongoDB 3.0.12". The application connected to MongoDB is built with .net c # and is running on Windows Server 2012 R2. TCP Keepalive remains the default setting. Also when connecting from application socketOptions.keepAlive has not changed. The connectionString of the application also does not set the option as follows.


texts: Setting a SQL Server instance back to multi user mode

texts:
I had to restore a database, but some of the users were still connected, and had left for the day. Previously, I had set the database to single user, now I do not see that option.

So, I made a change at instance level, setting Properties/Connections/Maximum number of concurrent users to 1, disconnecting all other users from the server.

I restarted the SQL Server service, but now I am unable to access the database at all. The database is up and running, but I can't open a query window or right click on Properties to revert the change.

How can I fix this?


texts: newbie question - how to list people who has not email address related to him/her?

texts:
Sorry for my newbie question: I would like to list all people even those who has no email address in case of using many-to-many relation with superclass-sublclass inheritance.
Please see SQL Fiddle: http://sqlfiddle.com/#!17/8f556/8
Postgresql lists only the people who has email...
I tried this query:
select people.c_id as "ID",
    people.c_family_name as "Family Name",
    people.c_last_name as "Last Name",
    email.c_email as "Email"
from
    t_email email,
    t_people people
    left join t_ref_email ref_email on ref_email.c_party = people.c_id
where ref_email.c_party = people.c_id
  and ref_email.c_email = email.c_id;

Result:
ID  Family Name Last Name   Email
1000    Test    Joe test_email@email.com

Test Bill was not listed because he has no email address...


texts: SSIS Create New Folders On The Fly When Writing To A Flat File Connection

texts:
I have an SSIS package that is meant to write to multiple files, which are stored like

\\server\MainFolder\DynamicField1\DynamicField2\File.csv

where the subfolders DynamicField1 and DynamicField2 are not necessarily in existence, but are coming from the variable i store for the ConnectionString.

When I run the package from my desktop, I get the message:

[File [2]] Warning: The system cannot find the path specified.

The error goes on to say:

[File [2]] Error: Cannot open the datafile "\\server\folder\abcd\efgh\filename_20190606115901.csv".

That looks like a good file name.

I tried replacing the dynamic folders with a dash so that the file name would look like:

\\server\folder\abcd - efgh - filename_20190606115901.csv

and that actually seemed to work. The problem is, there will be hundreds of files and it would be best if they could be organized in two levels of sub folders.

Does this mean that I cannot create folders on the fly?


texts: Does it makes sense to partition on a foreign key?

texts:
I have a MySQL table which looks a bit like this:

ID (BIGINT, PK), OtherID (INT, FK, indexed), NegativeTimestamp (BIGINT, indexed)

Selects should go very fast, updates almost never happens, inserts are not so frequent.
Queries will always query data within the same OtherID. The other filter will always be a range of NegativeTimestamp (which is a negative timestamp, so I can use ASC index while I always need the timestamps to be DESC), or the top (for example top 1000 rows with lowest timestamps). Recent data (lowest NegativeTimestamps) are requested most frequently, but there is no clear line which timestamps are more frequently requested than others.

I'm not sure if I should partition on OtherID (as queries will then never involve two partitions), or on Timestamp, or on none of them.

Any other optimizations are always welcome of course. If possible, also explain why so I can use this information in the future.

Extra info:

Table:

CREATE TABLE `myTable` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `MarketID` int(11) NOT NULL,
  `Close_Datetime` datetime NOT NULL,
  `Data` decimal(30,15) DEFAULT NULL,
  `Close_NegativeTimestamp` bigint(20) NOT NULL DEFAULT '0',
  PRIMARY KEY (`ID`),
  UNIQUE KEY `ID` (`ID`),
  KEY `IX_FK_MarketPriceCandle` (`MarketID`),
  KEY `OpenTimestamp` (`Open_Datetime`),
  KEY `CloseTimestamp` (`Close_Datetime`),
  KEY `CloseNegativeTimestamp` (`Close_NegativeTimestamp`),
  KEY `MarketIDIndex` (`MarketID`),
  KEY `CloseNegativeTimestampMarketID` (`MarketID`,`Close_NegativeTimestamp`),
  CONSTRAINT `FK_MarketMyTable` FOREIGN KEY (`MarketID`) REFERENCES `markets` (`ID`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=110011638 DEFAULT CHARSET=utf8;

This table has currently more than 100 million rows and will continue to grow.

Most executed query:

SELECT * FROM myTable p
 WHERE p.MarketID = @marketID AND p.Close_NegativeTimestamp <= @start AND p.Close_NegativeTimestamp >= @end
 ORDER BY p.Close_NegativeTimestamp ASC;


texts: Overwrite event - data from SQL Server to MySQL

texts:
In MySQL is it possible to execute an update query with values selected from external SQL Server database?

I'd like to execute every night a timed query, which overwrites some data in MySQL with data on SQL Server.


texts: MS SQL Server sys.indexes vs sys.sysindexes system table difference

texts:
For indexes there are 2 system tables: sys.indexes and sys.sysindexes.
I'd like to know what are their differences. Both contain information about indexes then why they are separate?

Thanks in advance.


texts: Search a string across all columns of a query result

texts:
I have a query, which has around 50 columns and it is subject to change.

I need to modify my SQL-DML to search a specific string across all 50 columns.

How is this possible?

select
  *
from
  messages a
  FULL OUTER JOIN  customers b ON a.customer_id = b.customer_id
  FULL OUTER JOIN orders c ON c.customer_id = b.customer_id
  FULL OUTER JOIN delivery_templates d ON c.billing_template_id = d.delivery_template_id

where
  product_id != '0'
  and a.hide_question = '0'
  and a.seller_id in ('2', '3', '1', '6')
  AND a.market_place_id IN (
    SELECT
      MAX(market_place_id)
    FROM
      messages
    GROUP BY
      product_id
  )
order by
  a.market_place_id desc

So far the best alternative seem to be

SELECT *
FROM MyTable
WHERE Col1 LIKE '%foo%' OR
      Col2 LIKE '%foo%' OR
      Col3 LIKE '%foo%' OR
      Col4 LIKE '%foo%' OR
      Col5 LIKE '%foo%' OR
      Col6 LIKE '%foo%'


texts:
I have a HP ProLiant DL380 G5 server and I would like to install mysql on it .

My questions are:

Which MySQL version should I install from this link for better performance, 32 bit or 64 bit?
Which version of Windows should I install on server for better performance: 2003, 2008, XP, or 7?


texts: How do I find out the "redo log file max size" in Oracle 11R2

texts:
I'm working with Oracle 11R2 and I want to see the max size of my redo log files (I didn't install Oracle so I'm not aware of the configuration).

I have searched the Internet but all I have found concerns the way to erase and replace it.

Is there anywhere in the data dictionary I can find this information?

I’ve already tried  v$log and v$parameter but I haven't found what I'm looking for.


texts: Limiting the number of CPUs used in SQL Server 2012 Enterprise

texts:
The platform under consideration is SQL Server 2012 Ent. SP3.

We are in the process of migrating a SQL Server environment to VM but highly feel the server was over spec'd to start.

We want the customer to feel good about going to a VM with less resources as they don't appear to be needed.

To start we want limit the number of CPUs that are currently being allocated.  Is setting the Processor affinity a safe approach? Any gotchas?

The server was a dedicated physical SQL Server with 32 cores moving to VMware. The thought was to step down the number of CPUs on the old server prior to going to VM to show the customer.  According to the VM team, having a server with that many CPUs is not necessary due to VM.   Also, the server is basically using less than 10% of the available CPUs.


texts: Partial backup and restore using filegroups

texts:
I have a single database that I put all tables into with multiple schemas for the different products I have. I know I can't backup and restore just one schema, but if I move all tables for a schema into a separate filegroup can I then back and restore just that schema without affecting other tables in other schemas? I don't have foreign keys between schemas, so there would be no conflicts.

Also, can I restore into a different database just one of the filegroups without restoring the others?

A while back I decided to stick with just one database, thinking there was a way to accomplish this. If there is no way to do this then it is better to create multiple databases.


texts: Alternative to CLUSTER without table lock

texts:
I'm facing performance degradation and increasing storage usage as a result of frequent new and updated records causing index and storage fragmentation.

VACUUM doesn't help much.

Unfortunetely CLUSTER isn't an option as it causes downtime and pg_repack isn't available for AWS RDS.

I'm looking for hacky alternatives to CLUSTER. One that seems to work fine in my local tests is:

begin;

create temp table tmp_target as select * from target;

delete from target;
insert into target select * from tmp_target order by field1 asc, field2 desc;

drop table tmp_target;

commit;

The ordering of ctid looks correct with:

select ctid, field1, field2 from target order by ctid;

The question is: does this look fine? Is it going to lock the target table for SELECT queries causing downtime in apps?
Is there a way to list the locks involved in the transaction?

Related to Cluster command locking read only queries on replica


texts:
When the instance crashes due to unexpected disaster in SQL Server, how does the SQL Server recover the databases?

SQL Server goes through which phases to recover the data and which are the steps that are performed in the instance recovery by SQL Server?


texts:
The situation is like this:

I have a table that has one row for each copy of a letter, which also contains the letter ID.  I want a single query that outputs each letter copy row with the letter copy ID, letter ID and count of all copies for the letter ID ...

Table Rows

Letter Copy ID      Letter ID
1                   1
2                   1
3                   1
4                   2
5                   2
6                   3

Expected Output

Letter Copy ID      Letter ID      Letter Copy Count
1                   1              3
2                   1              3
3                   1              3
4                   2              2
5                   2              2
6                   3              1


texts: How to calculate the max number of worker threads in SQL Server?

texts:
I use 64-bit OS with two CPUs (each of them makes 7 logical processors) and with total number of logical processors equal to 14.

When I run this code I get 2048

SELECT max_workers_count FROM sys.dm_os_sys_info

On the other way, when I use the formula below I get totally different number which is 672

For a 64-bit operating system:
Total available logical CPUs <= 4
Max Worker Threads = 512
Total available logical CPUs > 4
Max Worker Threads = 512 + ((logical CPUs - 4)*16)

Can you explain to me why I get different numbers? I tested it on another server and got exact same numbers for both cases.

EDIT

P.S Thanks to Dan Guzman for his suggestions to check server settings for max worker threads. That is what EXEC sp_configure 'max worker threads' returns:


texts:
I want to be able to store multiple xml objects in a database.  But I don't want to just serialize the object and dump it out.  Rather I would like to be able to access the database representation of the xml object in the same way as I would the xml itself.

Multiple modules need to access the same data but it would be efficient if they could be told which node to start reading from.

In other words traverse the database as if it were actually a binary tree.  CouchDB says it uses a binary tree but at first glance I see no way to store and retrieve data in a tree-like fashion.  Is this the domain of graph style databases?


texts: Create an AD user from a SQL Server trigger

texts:
Running SQL Server 2008 (10.0.5520) - I have a trigger that is executed when a new record is added to the security table. I would like to extend this trigger, and create an entry in the AD system with a couple of the parameters from the insert.

Question is: can this be accomplished? If so, how?

It looks like a command line tool does exist:

Technet reference

To create a contact named Jeff Hay in a top-level OU named Service Dept in the fabrikam.com domain, at a command prompt, type the following command, and then press ENTER:

dsadd contact "cn=Jeff Hay,ou=service dept,dc=fabrikam,dc=com"

Can this command line be executed from the trigger?


texts: MySQL DELETE becomes exceptionaly slow with a large number of rows

texts:
When performing a DELETE against a large number of rows in the Prices table, the DELETE becomes progressively slower.  If deleting 15,000 rows it runs in about 15 seconds.  20K rows takes 3 or 4 minutes.  40,000 rows takes 15 minutes, 100,000 rows runs for well over an hour.

The below After DELETE trigger updates the Items table with a count of the available price types.  This is used when calculating prices to speed up that process in production.

Both tables are InnoDB, and I have updated innodb_buffer_pool_size to 4G, with no effect.

I have verified that all SQL statements are using indexes. There is a PriceType + ItemID index on the Prices table, and ItemID is the primary key for the Items table.

Unfortunately this schema is determined by another application and I can't modify the table structure. I can modify the triggers, indexes, etc.  That application cannot be changed to update the counts in the Items table directly.

BEGIN

    DECLARE iPriceTypeA INT;
    DECLARE iPriceTypeB INT;
    DECLARE iPriceTypeC INT;

    SET iPriceTypeA = (SELECT COUNT(*) FROM Prices WHERE PriceType='A' AND ItemID=OLD.ItemID),
        iPriceTypeB = (SELECT COUNT(*) FROM Prices WHERE PriceType='B' AND ItemID=OLD.ItemID),
        iPriceTypeC = (SELECT COUNT(*) FROM Prices WHERE PriceType='C' AND ItemID=OLD.ItemID);

    UPDATE Items
        SET PriceTypeA = iPriceTypeA,
            PriceTypeB = iPriceTypeB,
            PriceTypeC = iPriceTypeC,
        WHERE ItemID = OLD.ItemID;

END

This doesn't seem like it ought to create a problem, but when deleting 120K rows the server becomes unusable for a couple of hours. Why would this query become essentially exponentially slower with more rows?

Edit: updating with Prices table schema

ItemCode varchar(30)
PriceType char(1)
Method char(1)
Factor decimal(7,2)

Edit:

I am still mystified why this trigger causes the delete to become slower the greater the number of records.  As I said if I remove the trigger call the delete is very quick.  I would really like to get to the bottom of the performance issue.

TO get things working for now my solution was to switch to MyISAM since this table is only used for selects in our application.


texts:
I'm using Oracle 11g database in archivelog mode. I'm getting the following error in my database logs for the last two days.

*** 2017-05-19 22:01:04.164 4515 kcrr.c
ORA-19815: WARNING: db_recovery_file_dest_size of 53687091200 bytes is 100.00% used, and has 2340864 remaining bytes available.
************************************************************************
You have following choices to free up space from recovery area:
1. Consider changing RMAN RETENTION POLICY. If you are using Data Guard,
   then consider changing RMAN ARCHIVELOG DELETION POLICY.
2. Back up files to tertiary device such as tape using RMAN
   BACKUP RECOVERY AREA command.
3. Add disk space and increase db_recovery_file_dest_size parameter to
   reflect the new space.
4. Delete unnecessary files using RMAN DELETE command. If an operating
   system command was used to delete files, then use RMAN CROSSCHECK and
   DELETE EXPIRED commands.

When I went through some forums, one solution that I found was to increase the db_recovery space. So I checked the space and got this result.

Then, I checked the current rman retention policy which is as follows:

RMAN> SHOW ALL;

RMAN configuration parameters for database with db_unique_name PROD are:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 4 DAYS;
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOA
D TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'C:\APP\PRODADMIN\PRODUCT\11.2.0\DBHOM
E_1\DATABASE\SNCFPROD.ORA'; # default

Also, the fra space :

Note : I've scheduled to delete archive log files every two days via Windows Task scheduler.
The content of the batch file for deleting the archivelogs is as follows:

connect target /;
crosscheck archivelog all;
delete archivelog all completed before 'SYSDATE-1';

As far as I understood, FRA has enough space, but still getting the same error. I've no idea how to resolve this. Urgent help is needed.


texts: Copy SQL table to other server identity becomes int

texts:
I just built an Umbraco website. This uses a not-that-small SQL Server db. I copied this DB using the export data function built in in SQL Server 2008.

This copies all the tables and their content but it sets (for the one table i know) the Identity(1,1) column to "int". This way my umbraco installation fails over this.

As I tried to change the column type to identity,  obviously I can't edit the columns.
Even if there was a way to do this manually, I'd still have to change most of the tables in the new database.

Is there some option or so I missed when copying? I'd like to make it copy with the included identity.

I tried backup, and restore but my SQL server edition are not exactly the same. The one is x86 and the other is x64, which is giving me compatibility issues.

Thanks in advance!


texts: Why is it faster to create index and run query than run query without it?

texts:
I have table with few million rows. It contains logs from external service, so I've decided not to index it (lot of inserts, sparse reads).
When I run query that reads from the table without index, it takes (unsurprisingly) very long time.
However, when I create index and run the query and then drop the index, it is considerably faster (even with creating and dropping index).
Why is it faster to create index ad hoc, instead of letting SQL Server do it's thing? It seems unintuitive (Why wouldn't SQL Server create the index itself?).
Are there any downsides to this approach?

The query in question looks something like this, but I do not think it is necessarily relevant, as I've seen similar behavior elsewhere as well.
    UPDATE Device
    SET Col1 = l.Col1
        ,Col2 = l.Col2
        ,Col3 = l.Col3
    FROM dbo.Device
        OUTER APPLY (
            SELECT MAX(Id) AS [Id]
            FROM dbo.Logs
            WHERE Logs.Device_FK = Device.Id
            GROUP BY Logs.Device_FK
        ) lastLog
        OUTER APPLY (
            SELECT Col1, Col2, FORMAT(Col3) AS "Col3"
            FROM dbo.Logs
            WHERE Logs.Id = lastLog.Id
        ) l


texts:
I have a user that is able to drop, alter, create tables and indexes. But when I list the privileges for my Oracle user it does not show any CREATE TABLE or other such privileges. How am I able to do these operations without the privileges or why doesn't Oracle show them?

Running following queries returns nothing:

SELECT * FROM USER_TAB_PRIVS;
SELECT * FROM all_tab_privs_recd WHERE grantee = 'user_kshitiz';

Running SELECT * FROM USER_SYS_PRIVS returns:

USERNAME                       PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
user_kshitiz                   UNLIMITED TABLESPACE                     NO
user_kshitiz                   CREATE SEQUENCE                          NO

Running SELECT * FROM USER_ROLE_PRIVS returns:

USERNAME                       GRANTED_ROLE                   ADM DEF OS_
------------------------------ ------------------------------ --- --- ---
user_kshitiz                   CONNECT                        NO  YES NO
user_kshitiz                   RESOURCE                       NO  YES NO
user_kshitiz                   SCHEMA_1_RW_ROLE               NO  YES NO

Checking privileges on each of those roles returns nothing.

SELECT * FROM ROLE_TAB_PRIVS WHERE ROLE = 'CONNECT';
SELECT * FROM ROLE_TAB_PRIVS WHERE ROLE = 'RESOURCE';
SELECT * FROM ROLE_TAB_PRIVS WHERE ROLE = 'SCHEMA_1_RW_ROLE';

So where does the power come from?


texts: Based on what criteria voting take place in MongoDB?

texts:
Suppose a replication set R1 contains one primary, 2 secondary and one arbiter node. Voting will happen if the primery node goes down. Based on what criteria secondary members will vote to select the next primary node? Is the voting random process ? Whether the members will check the health / efficiency of the other members before voting?

Consider all the nodes have same priority and no salve delay


texts: How Can i Update only one Column of SQL Table via store procedure with passing rest column value null

texts:
Here is What i Have tried.
I Have created a Table [tblUser] which allow null value and a Store procedure [spUpdateUser] with All Parameters which can be passed null except ID parameter

Whenever this procedure executes it will only update the passed (Not Null) Parameters

CREATE TABLE [dbo].[tblUser]
(
[ID] [int] IDENTITY(1,1) NOT NULL,
[UserID] [varchar](50) NULL,
[Name] [varchar](50) NULL,
[FatherName] [varchar](50) NULL,
[DOB] [date] NULL
)

CREATE PROCEDURE [dbo].[spUpdateUser]
@ID int,
@UserID varchar(50) = null,
@Name varchar(50) = null,
@FatherName varchar(50) = null,
@DOB date = null
AS
BEGIN
update tblUser set
    UserID = @UserID,
    Name = @Name,
    FatherName = @FatherName,
    DOB = @DOB
    where ID = @ID
END

How can i update table [tblUser] with only passing one parameter

exec [spUpdateUser] @ID = 1, @DOB = '27 Mar 2017'


texts: Why can I drop table when logged in as not owner?

texts:
I read that only owner can drop table and no other user may be granted to do so.

I created a table with owner postgres and then drop it as another user. What privileges should I revoke to disallow it?

The user has all the privileges granted by default in db properties but I don't see any "create table" or "drop table" privilege.


texts: Is there a problem creating only differential backups every hour?

texts:
I have three databases which are not big. Every differential backup is roughly 50MB, then we have a full backup at midnight.

Is there any problem doing this?

I could take the full backup then logs, then diffs like 4 or 5 times a day, but storage is not a problem with this instance. I can afford to lose 1 hour of data, that's no problem, that's why I'm creating all of the diffs (I'm using full recovery mode). Then I can restore only the full + diff , instead of full + log + log + log + log + log + log + log
.


texts: Is it acceptable to have circular foreign key references\How to avoid them?

texts:
Is it acceptable to have a circular reference between two tables on the foreign key field?

If not, how can these situations be avoided?

If so, how can data be inserted?

Below is an example of where (in my opinion) a circular reference would be acceptable:

CREATE TABLE Account
(
    ID INT PRIMARY KEY IDENTITY,
    Name VARCHAR(50)
)

CREATE TABLE Contact
(
    ID INT PRIMARY KEY IDENTITY,
    Name VARCHAR(50),
    AccountID INT FOREIGN KEY REFERENCES Account(ID)
)

ALTER TABLE Account ADD PrimaryContactID INT FOREIGN KEY REFERENCES Contact(ID)


texts:
I am currently learning SQL on my own and I am looking for a review of one of my query.

I have the following problem.

"Given a table of players and a table of played match output the ranking of the tournament: (id, player_name, win, number_of_games)"

To that I have created to table

CREATE TABLE players (
id SERIAL PRIMARY KEY,
name char(50)
);
-- results => id of the player who won
CREATE TABLE game (
id SERIAL PRIMARY KEY,
player1 integer,
player2 integer,
results integer
);

I have come up with a query that do the job. But I find it dificult to understand.

SELECT S2.playersid as id, S2.name, COALESCE(S1.win, 0) as win, COALESCE(S2.games, 0) as games
FROM
  (
   SELECT results as id, COUNT(1) as win
   FROM game
   GROUP BY game.results
  ) AS S1
RIGHT JOIN
  (
   SELECT players.id as playersid, SUM(games) as games, players.name as name
   FROM
   (
       SELECT game.player1 as id, COUNT(game.player1) as games FROM game GROUP BY game.player1
       UNION ALL
       SELECT game.player2 as id, COUNT(game.player2) as games FROM game GROUP BY game.player2
   ) as computeGames
   RIGHT JOIN players
   ON computeGames.id=players.id
   GROUP BY players.id, players.name
  ) AS S2
ON S1.id=S2.playersid
ORDER BY win DESC, games DESC;

Is there any features that I might have missed that can help me simplify this query?

You can test it over there http://rextester.com/EYPW43210


texts: How to get old and new values on Redis notification?

texts:
I need to implement a feature like: once any specific key in Redis is updated, call a C function with the old and new values as arguments.

Currently, I know Redis notification  and I could use hiredis to subscribe a keyspace event. While Redis notification doesn't tell me the old or new value. My current solution is getting old and new value in another session rathen than the one subscrbing the event. Once event occurs, get the new value then.

But I believe this solution may have potential bug. Say if the value is updated twice in a very short interval, I could get notified, but when I call get value, I may only get the second value, since the first value has been flushed very quickly.

Is my worry necessary? Is there any solution to implement Redis notification with old and new values?

Thanks in advance.


texts: Script Contents of SQL Server Database From a Batch File

texts:
I want to be able to script the schema of a given database into a .sql file from the Windows command prompt. Basically, I want to execute the "Generate Scripts" feature of Management Studio programmatically.

I know this is possible using .NET and SMO, but is there something built-in to do this?

Background to this question: This is meant as a simple auditing instrument. We want to capture the schema every night. Any primitive, low-tech solution works for us.


texts: Reporting Services in SharePoint Integrated Mode with SSPI/Windows Authentication Architecture

texts:
I have a confusion about the place of Reporting Services' installation in my current architecture. I have two servers, one where SharePoint is on, and another where my data resides. Both servers have SQL Server 2016 installed. I need to install Reporting Services in one of the places, I don't know where. Here are my requirements:

The users MUST be represented in the database. This means that if
a user, who has access to a report, does not have access to its
underlying tables, then the user must not be able to see the data.
This means, I'll be managing the security using the DB Roles, and
thus I need pass through authentication. I'll also be using
Row-Level Security feature, which again, mandates the reporting
services to tell the database what is the user that's querying the
data.
I need to use all of the features SharePoint has to offer (and so
does the Reporting Services). This means that I need to install
Reporting Services in Integrated mode.

My Questions:

1- where should the reporting services be installed Server 1 or Server 2 (where SharePoint is located).

2- More importantly: how could I get Reporting Services to communicate the users who are accessing the data. I guess how could I see pass through authentication, to ensure security is not managed by just who has access to the reports and who doesn't. Assume everyone has access to all reports, and DB has to be smart enough NOT to provide unauthorized users anything.


texts: mysql Relay log file not found error while changing mysql data directory

texts:
I want to change data directory of my mysql server running 5.6.28 on centos 5.11. Its a slave to some master server. i have edited the config file ,moved the data contents from old directory to new required directory. When i tried to start the server it failed to start with below error in error log.

/usr/sbin/mysqld: File '/oldpath/to/log/mysql_bin.000220' not found
[ERROR] Failed to open log (file '/oldpath/to/log/mysql_bin.000220', errno 2)

so its still referring old binary log file path. I am not sure why its not referring new binary log file path where the mysql_bin.000220 present and getting failed to start. Any idea why server throwing this error and not starting?


texts: How to check if a PostgreSQL table is temporary?

texts:
How can I write a query to check if a PostgreSQL (10.x) table is a temporary table?

I looked at the pg_class system table, but couldn't find any column about whether the relation is temporary.

SELECT * FROM pg_class WHERE False;
 relname | relnamespace | reltype | reloftype | relowner | relam | relfilenode | reltablespace | relpages | reltuples | relallvisible | reltoastrelid | relhasindex | relisshared | relpersistence | relkind | relnatts | relchecks | relhasoids | relhaspkey | relhasrules | relhastriggers | relhassubclass | relrowsecurity | relforcerowsecurity | relispopulated | relreplident | relispartition | relfrozenxid | relminmxid | relacl | reloptions | relpartbound
---------+--------------+---------+-----------+----------+-------+-------------+---------------+----------+-----------+---------------+---------------+-------------+-------------+----------------+---------+----------+-----------+------------+------------+-------------+----------------+----------------+----------------+---------------------+----------------+--------------+----------------+--------------+------------+--------+------------+--------------
(0 rows)


texts: Does Oracle ADO.NET varchar parameter size influence query plan reuse?

texts:
I am considering to apply a fix which will generate constant parameter sizes for (n)varchar parameters. The application is based on NHibernate with the old OracleClientDriver (using Microsoft Oracle driver, ODP.NET can't be used in the near future), and NHibernate runs on top of ADO.NET.

This driver creates parameterized SQL with the size of (n)varchar parameters set to the actual string size, which, of course, varies:

select * from People where Name=:p0; :p0 = 'John' [Type: String (4)]
select * from People where Name=:p0; :p0 = 'James' [Type: String (5)]

Doing this with SQL Server would create a huge number of query plans, one for each combination of string sizes (when multiple string parameters), which is of course highly inefficient. I always thought it's the same in Oracle, but now I've got doubt.

I applied a fix to the NHibernate driver, which would send always the max parameter size, e.g. if the above Name column was 32 chars wide, it would always send [Type: String (32)]. My code does not use dedicated Prepare statements, but sends parameterized SQL together with the values (similar to calling EXECUTE IMMEDIATE).

I then looked at the statement (actually an INSERT) in Oracle Enterprise Manager, and the old version did not appear in the duplicate queries list. The statement itself showed a parse for each call (total of more than 1000 after some testing), but few hard parses. Thus, I could see no difference in performance between the fixed and the variable varchar length. Does this mean the fix is futile, and different query plans for different parameter sizes occur only in SQL Server (and maybe other DBMS)? Does Oracle check only for SQL string equality, but not for equality in parameter size settings?

I also noticed that duplicate SQL was almost only found where parameter values were concatenated into the SQL string, instead of using bind parameters.

EDIT: cursor_sharing in Oracle is set to EXACT.


texts:
getting error:

"The target database ('MyDB') is in an availability group and is currently accessible for connections when the application intent is set to read only. For more information about application intent, see SQL Server Books Online."

in the SQL error logs with no information who is trying to log in.

This is on SQL 2017 version 14.0.3026.27 with Always On High Availability


texts: List of IDs that have an assignement to ALL other Ids in another table

texts:
I need help with the following task (giving up after some hours)

CREATE TABLE #cities (cityId INT, city VARCHAR(100));
INSERT INTO #cities (cityId, city)
VALUES (1, 'Metropolis A'),
       (2, 'Town B'),
       (3, 'Village C');

CREATE TABLE #assignements (assignmentID INT, cityId INT, desciption VARCHAR(100));
INSERT INTO #assignements (assignmentID, cityId, desciption)
VALUES (1, 1, 'Test Alpha Metropol'),
       (1, 2, 'Test Alpha Town'),
       (1, 3, 'Test Alpha Village'),
       (2, 1, 'Test Beta Metropol');

INSERT INTO #assignements (assignmentID, cityId, desciption)
VALUES (2, 3, 'Test Beta Village');

I need a query, that returns me all assignmentIDs joined to the #cities (easy :-)), but list only those cities, that are used in ALL assignmentID.

In the example above it should me return after the first INSERT only

assignmentID  city
1             'Metropolis A'
2             'Metropolis A'

since the Town and the village are only used once (but there exists two different assignmentIDs)

After the second INSERT it should return

assignmentID  city
1             'Metropolis A'
2             'Metropolis A'
1             'Village C'
2             'Village C'

because Village C is now assigned to both assignmentIDs too.

When I would

INSERT INTO #assignements (assignmentID, cityId, desciption)
VALUES (3, 2, 'Test Gamma Town');

the query should return no row, because there is no city, which is assigned to all three assignmentIDs (Alpha, Beta and Gamma).

Of course this example is simplified, in reality there could be any number of cities which are assigned to (currently) up to 11 assignmentIDs


texts: launch database mirroring monitor grayed out in SQL server 2014

texts: I have 2 servers configured with mirroring each other. Both the servers are principal & Mirror servers each other. From the 2nd Server launch database mirroring monitor is working fine but from 1st server launch database mirroring monitor is showing grayed out. Both server services are running with the same administrator account. The below screenshot is for 1st server. Can you please help me how to enable this launch database mirroring option in SQL server 2014 ?


texts: SSIS 2008 - Getting Execute SQL Task to update data with parameters

texts:
I feel this is s simple task but I'm tripping over how to do it.

What I have is a very simple Data Flow Task that calls a stored procedure.

SELECT [Id]
      ,[Error]
  FROM [CEGSalesforce].[dbo].[OpportunityLineItem_OrderTracking]

I want to update data in another location based on the Id above. I have a "Execute SQL Task" flow that calls another stored procedure with this code in it

UPDATE [Integration].[Staging].[OrderTracking]
   SET TriggerIntegration = CASE
                              WHEN @Error = 'Operation Successful.'
                              THEN 0
                              ELSE 1
                            END
     , Error = @Error
 WHERE SalesforceOpportunityLineItemId = @Id

Here is how my variables are setup

Here is how my SQL Task is setup. I've tried Single row, full result set, anything to get it to run my update as I expect it

And my parameter mapping

And finally, my Result Set

Not sure what I'm doing wrong. Hoping I can get some direction


texts: What is the difference between XtraBackup and rsync?

texts:
Yes, I'm going to run strace on them both... However, I would like to know ...

What is XtraBackup is doing over and above rsync -ac src dest ?
What's so special about XtraBbackup ?
How is XtraBackup interacting with mysqld ?


texts: Specific issue on data pump API in oracle

texts:
I have a client/server architecture. Using an Oracle dbms on the database server side.
I need to perform a user-triggered (from client side) backup of the database, where the best way to perform that is using a stored procedure on the server side which the client may call, as the client has no oracle tools to perform the backup. I've searched thorough inside available solutions and have found that using a stored procedure is the best way.
Well, then I found that using oracle data pump API is the best way to use inside a PL/SQl stored procedure.

My specific questions about the API are...

I would like to ask about two issues ...

---- The first -----

the detach function to detach the handler, is it necessary to be used at the end of the procedure? and what if I don't use it? I read the Oracle documentation but I didn't get their point, they say it doesn't terminate the job but indicates that the user is not interested in it, an when I use detach at the end of my procedure the exported .dmp file disappears.

---- The second -----

to perform a user (client side) triggered back up as the modification are only to the data, I used TABLE parameter for the export operation. But the version parameter... what should it be? I also read the documentation but couldn't determine what I need (LATEST or COMPATIBLE) ?

Thanks


texts: Make SQL Server SSDT Database Project Reference Different in Publish Profile

texts:
In a SSDT Database project, I know how to publish the Self-Database to different locations. I can change eg, the Customer database name in the Publish profile.

Example
In:

Dev Environment: will publish profile to CustomerDevDB
QA Environment: will publish profile to CustomerQADB
Prod Environment: will publish profile to CustomerProdDB

(This is legacy system, I did not design it like this)

Now in our Customer Database, I have stored procedures which point to a Product database reference.
How do I change my Project reference to be variable for each Publish Profile?

Dev Environment: will publish to CustomerDevDB and will point to ProductDev

QA Environment: will publish to CustomerQADB and will point to ProductQA

Prod Environment: will publish to CustomerProd and will point to ProductProd

I am trying to test around with Project DB Reference, and SQL Command variables.
* I can point the Database Reference to different databases names,  however I cannot change the reference in the publish profile yet (which is what I am really looking for).

If someone can print screen images of how to do this, it would be helpful. Thanks


texts: Find groups with exactly one value: COUNT(DISTINCT x) = 1 vs MIN(x) = MAX(x)

texts:
Given this data:

gid | val
1   | a
1   | a
1   | a
2   | b
3   | x
3   | y
3   | z

the following queries return groups (gid) that contain exactly one distinct value (val):

SELECT gid FROM t GROUP BY gid HAVING MIN(val) = MAX(val)
SELECT gid FROM t GROUP BY gid HAVING COUNT(DISTINCT val) = 1

People seem to suggest that the first variant would be faster (if assuming appropriate indexes exist then looking up MIN and MAX would be faster than counting all values). Is that a fact or a myth.


texts: Statistics Update Time is missing in Actual execution plan

texts: I remember that I have seen somewhere in the past that when I saw the XML of the actual execution plan, it had the details about when the stats were updated on the indexes on a table and what was the number of rows sampled. I am not able to find that info now. Is there any knob that needs to be turned on or actual plan never had that kind of information in actual execution plan?


texts:
I am attempting to create a procedure using cursors to list the average salary of all employees on a per department basis. I am using three tables, defined as

emp (eid, ename, age, salary)
works (eid, did, pct_time)
dept (did, dname, budget, managerid)

The procedure is compiling with errors. What am I doing wrong? Please see the procedure definition below.

create or replace procedure get_avgSalary(ddept_id in dept.did%type)
is
  cursor c1 is
    select avg(e.salary)
    from emp e, works w, dept d
    where e.eid=w.eid and d.did = w.did
    group by w.did;
  avg_sal c1%rowtype;
begin
  for avg_sal in c1 loop
    dbms_output.put_line('Average salary of the employees in department ' || ddept_id ||' is '|| avg_sal.salary);
  end loop;
end;
/

I have to use a cursor. It is part of my assignment.


texts:
I was wondering performance wise what would be faster a join or a from and where query.
I am trying merge and update query but performance is terrible?


texts: 'ERROR: could not access file "$libdir/postgis-2.5": No such file or directory'

texts:
Dealing with PostGIS has been a nightmare from day one. Every single time I have to touch it in any way, or update PostgreSQL, it turns into a broken mess. The only reason I use it is because I need to be able to use "geography" columns and a few "st_" functions.

Until a few days ago, I had EnterpriseDB's PostgreSQL 11.5 installed on Windows 10. It had PostGIS installed.
Then I installed EnterpriseDB's PostgreSQL 11.7 to update it. Now it somehow lost PostGIS, so I re-installed that as well. Everything has to be done manually for some reason.

Now, every query related to PostGIS gives this error:

ERROR:  could not access file "$libdir/postgis-2.5": No such file or directory

Even when I try to run:

ALTER EXTENSION postgis UPDATE;

I just get this nonsense:

WARNING:  unpackaging raster
WARNING:  PostGIS Raster functionality has been unpackaged
HINT:  type `SELECT postgis_extensions_upgrade(); to finish the upgrade. After upgrading, if you want to drop raster, run: DROP EXTENSION postgis_raster;

ERROR:  could not access file "$libdir/postgis-2.5": No such file or directory
CONTEXT:  PL/pgSQL function _postgis_drop_function_if_needed(text,text) line 6 at FOR over SELECT rows
SQL state: 58P01

I don't know why it says 2.5 when I installed postgis-bundle-pg11x64-setup-3.0.1-2.exe.

This doesn't work at all (doesn't exist):

SELECT postgis_extensions_upgrade();

What am I supposed to do?

I've seriously spent so much of my life just fighting with PostGIS that I truly hate it, and wish I could get rid of it forever. Or that they at least could get their act together and integrate it correctly into PostgreSQL so it doesn't lead to all these stupid problems all the time. I don't understand why it has to require so much manual work and fiddling. Why can't it just do these steps automatically?


texts: Error on slave2 after promoting slave1 to master in cascaded streaming replication

texts:
I've a setup like MASTER -> SLAVE1 -> SLAVE2. MASTER and SLAVE2 are running on different ports on single server. SLAVE1 in on another server.

For testing purpose I've powered off my first server which holds MASTER and SLAVE1 databases and then promote SLAVE1 to master. When the server was turned on and slave2 was started, I am getting following errors in slave2 logs:

LOG:  restarted WAL streaming at 0/13000000 on timeline 4
LOG:  replication terminated by primary server
DETAIL:  End of WAL reached on timeline 4 at 0/13008B38.

Any idea how to address this issue and continue slave2 replicating from slave1 which is now promoted to MASTER?

Thanks


texts: How to set MySQL binlog retention in hours?

texts:
I'm in a situation where the MySQL log partition is being filled in less than a day. I can't disable binlogs, since I need them for replication.

I know I can set log retention but that accepts days, and a search only showed me this https://serverfault.com/questions/179165/can-expire-logs-days-be-less-than-1-day-in-mysql question, which suggests doing it yourself via scripting.

Since that question is almost 8 years old, I was wondering whether it is possible now.

If it matters, I'm using Percona MySQL 5.7


texts: Deny insert/delete for all users in SQL Server 2005

texts:
In our database we have about 20 users, that is used be some program (and users are added with this program). Now there was new requirement to use 2 database and do not allow delete data from one database.

What would be easiest way to deny insert/delete access for some tables (not all)? All users have db_datareader, db_datawriter.


texts:
i have one transaction table, each transaction has 5-6 contacts involved. each contact has the same fields like name, address, cell, email etc.

So the best thing I guess is to use the same contact table and relate it 5 times to the transaction table.. So I don't need to make the same kind of table 5 times.

The problem starts when I connect with the entity-framework this only takes one relation.

My question is should I make 6 copies of contact for each contact type or I would be better with 6 join tables (A table with just the 2 id's so I could do a join)?

I think its a very common problem but I couldn't find clear information what the best way is.

EDIT:


texts:
Had an issue with one of our production servers earlier today. Many transactions were suspended with RESOURCE_SEMAPHORE_QUERY_COMPILE wait.
Have sp_whoisactive data logged to table every minute. Below is a snippet from it. It is ordered by USED_MEMORY.

Can someone help me understand the unit of measure for Used_Memory please or should i be looking at other measure to find the query that caused the memory pressure.

Updated Pic of the same session :


texts: Invalid use of a side-effecting operator 'INSERT' within a function - Multiple Inserts in Function

texts:
If this is not the right forum please let me know and move it for me.

I've just got a bunch of inserts like this toward the end of my scalar function:

   INSERT INTO [Raptor].[dbo].[UserRole]
           ([RoleId]
           ,[UserId])
     VALUES
           (3
           ,@NewUserID)

    INSERT INTO [Raptor].[dbo].[UserRole]
               ([RoleId]
               ,[UserId])
         VALUES
               (5
               ,@NewUserID)


texts: Problem installing mssql-tools "ODBC Driver 17"

texts:
Whenever I try to apt-get install mssql-tools, I get,

Preconfiguring packages ...
(Reading database ... 302749 files and directories currently installed.)
Preparing to unpack .../msodbcsql17_17.0.1.1-1_amd64.deb ...
Installation failed, ODBC Driver 17 for SQL Server detected!
dpkg: error processing archive /var/cache/apt/archives/msodbcsql17_17.0.1.1-1_amd64.deb (--unpack):
 subprocess new pre-installation script returned error exit status 1
Selecting previously unselected package mssql-tools.
Preparing to unpack .../mssql-tools_17.0.1.1-1_amd64.deb ...
Unpacking mssql-tools (17.0.1.1-1) ...
Errors were encountered while processing:
 /var/cache/apt/archives/msodbcsql17_17.0.1.1-1_amd64.deb
E: Sub-process /usr/bin/dpkg returned an error code (1)

I've tried removing /usr/lib/libmsodbcsql-17.so and also removing odbcinst.ini,

dpkg: error processing archive /var/cache/apt/archives/msodbcsql17_17.0.1.1-1_amd64.deb (--unpack):
 trying to overwrite '/usr/lib/libmsodbcsql-17.so', which is also in package msodbcsql 17.0.1.1-1


texts: How open oracle DB Linux (After installation)

texts:
I have just installed Oracle 11gR2 on my Linux Mint Distro (xfce). All worked fine, and I was able to acces to

https://localhost:1158/em [Erreur 102 (net::ERR_CONNECTION_REFUSED) ]

I rebooted my PC and tried to reopen the page but this time I am unable to open it.

I am not so expert in Linux but could that be a problem of acces permission ?

How could I resolve this issue?

Thank you.


texts:
I have a question regarding max_connections in postgresql.conf.

In the Postgresql documentation, it says it determines the maximum number of concurrent connections to the database server. Does it literally mean users connected to it? or am I missing something?


texts: Cannot revoke default privileges from postgresql user

texts:
I am using the psql tool:

Command:

my_db=# \ddp

Result:

                         Default access privileges
     Owner      | Schema |   Type   |          Access privileges
----------------+--------+----------+--------------------------------------
 postgres       | kpi    | function | kpi_updater=X/postgres              +
                |        |          | intranet2=X/postgres
 postgres       | kpi    | sequence | kpi_updater=rwU/postgres            +
                |        |          | intranet2=rwU/postgres
 postgres       | kpi    | table    | kpi_updater=arwdDxt/postgres        +
                |        |          | intranet2=arwdDxt/postgres
 postgres       | kpi    | type     | kpi_updater=U/postgres              +
                |        |          | intranet2=U/postgres

Next I use the following command, that returns without error:

ALTER DEFAULT PRIVILEGES IN SCHEMA kpi REVOKE EXECUTE ON FUNCTIONS FROM intranet2;

I run \ddp one more time and... I get exactly the same result: default privileges were not modified.

What am I missing here ?

Thanks !


texts: MongoDb: sharded replica set: data distribution

texts:
How does the data get distributed among the shards?
How to backup and restore an infrastructure with 2 shards (3 config servers and 1 mongos)
When should I choose MongoDb Shard with Replica Set vs MongoDb Replica set only?
I have 2 shards. I use mongodump and mongorestore for backup and restore, is it a good practice?
Sometimes, the 3 config server are out of sync. What does it mean? How can I quickly get rid of these issues?


texts: LEFT JOIN based on content of a field in matched rows

texts:
Generically, I'm attempting to take a table and join it with another table but only on rows that have a specific value in one of the fields.

Specifically, I have a users table and a contact table. The contact table ties a user to a piece of contact information (such as phone, email, or a myriad others). But I only want to pull in rows from the contact table that match a specific contact type (i.e. only pull phone records), but I still want to pull all of the records from the users table regardless of whether they have a phone record or not. This is in MariaDB.

Users table (simplified):

mysql> select * from users;
+----+-----------+----------+---------------------------------+
| id | firstname | lastname | title                           |
+----+-----------+----------+---------------------------------+
|  1 | Chris     | Topher   | Ordinary Citizen Extraordinaire |
|  2 | Howard    | Roark    | Solutions Architect             |
|  3 | John      | Snow     | Knows Nothing                   |
+----+-----------+----------+---------------------------------+

Contact table:

mysql> select * from contact;
+----+--------+-------------+-------------------+
| id | userid | contacttype | contactdetails    |
+----+--------+-------------+-------------------+
|  1 |      1 | email       | one@two.Three     |
|  2 |      1 | phone       | 1-800-OHNOES1     |
|  3 |      2 | email       | hroark@amazon.com |
+----+--------+-------------+-------------------+

What I'm trying to select is something like this:

    +-----------+----------+---------------------------------+-------------------+
    | firstname | lastname | title                           | contactdetails    |
    +-----------+----------+---------------------------------+-------------------+
    | Chris     | Topher   | Ordinary Citizen Extraordinaire | one@two.Three     |
    | Howard    | Roark    | Solutions Architect             | hroark@amazon.com |
    | John      | Snow     | Knows Nothing                   | NULL              |
    +-----------+----------+---------------------------------+-------------------+

But my attempts with a left join result in too many or too few records:

mysql> select u.firstname,u.lastname,u.title,c.contactdetails from users u left join contact c on u.id=c.userid;
+-----------+----------+---------------------------------+-------------------+
| firstname | lastname | title                           | contactdetails    |
+-----------+----------+---------------------------------+-------------------+
| Chris     | Topher   | Ordinary Citizen Extraordinaire | one@two.Three     |
| Chris     | Topher   | Ordinary Citizen Extraordinaire | 1-800-OHNOES1     |
| Howard    | Roark    | Solutions Architect             | hroark@amazon.com |
| John      | Snow     | Knows Nothing                   | NULL              |
+-----------+----------+---------------------------------+-------------------+
4 rows in set (0.00 sec)

mysql> select u.firstname,u.lastname,u.title,c.contactdetails from users u left
join contact c on u.id=c.userid WHERE c.contacttype='email';
+-----------+----------+---------------------------------+-------------------+
| firstname | lastname | title                           | contactdetails    |
+-----------+----------+---------------------------------+-------------------+
| Chris     | Topher   | Ordinary Citizen Extraordinaire | one@two.Three     |
| Howard    | Roark    | Solutions Architect             | hroark@amazon.com |
+-----------+----------+---------------------------------+-------------------+

Is this possible in a single query? I'm mostly only familiar with the simple syntax of SQL, but I've tried EXCEPT clauses and subqueries to no avail. Redesigning the schema isn't out of the question, but it does have to account for the fact that users will have multiple forms of contact information (most have 4-5 emails/phones) to include contact types that we don't know about yet.

Thoughts?


texts:
Lets say I have the following table

-----------------------------
| user_id   | comment       |
-----------------------------
| 2         | thats cool    |
| 2         | awesome       |
| 3         | i hate this   |
| 3         | okay          |
| 6         | this is weird |
| 6         | hello?        |
| 6         | what is it    |
| 9         | how are you   |
| 16        | too slow      |
| 16        | yes           |
| 17        | alrighty      |
-----------------------------

How can you select one row per user_id? So my results would be:

-----------------------------
| user_id   | comment       |
-----------------------------
| 2         | thats cool    |
| 3         | i hate this   |
| 6         | this is weird |
| 9         | how are you   |
| 16        | too slow      |
| 17        | alrighty      |
-----------------------------

Is this possible with a single efficient query? Or are sub-selected necessary?  Is it possible to somehow use DISTINCT on a single column?


texts: Creating Pandas DataFrame on Python From a MongoDB Document containing Embedded documents

texts:
I am working on a machine learning algorithm on python's scikit.learn, but this time the data are in Mongodb documents format. I would like to pull my data into a dataframe. Here is an exemple of the documents:

{
    "_id" : ObjectId("552b9525359c6a09f061cb53"),
    "Interrupt" : true,
    "Url" : "Coco_mademoiselle.jpg",
    "Target" : {
        "FemaleInPercent" : 100,
        "MaleInPercent" : 0,
        "AgeProperties" : 6
    },
    "MaxDisplayTime" : 7,
    "MinDisplayTime" : 2,
    "MediaType" : 0,
    "IsLocked" : false,
    "FaceTagged" : [
        {
            "FaceId" : 36,
            "GenderConfidence" : -0.1731295609721586,
            "Age" : 23,
            "TotalAttention" : 14.92099999999997,
            "AttentionInsideThisContent" : 2.273999999999992,
            "Gender" : "Unknown",
            "AngleYaw" : [
                0
            ],
            "XPos" : [
                0.07704142996903575
            ],
            "YPos" : [
                0.7182761555157026
            ],
            "Distance" : [
                0.7223960254002589
            ]
        },
        {
            "FaceId" : 37,
            "GenderConfidence" : 0.3932732620245187,
            "Age" : 51,
            "TotalAttention" : 14.92099999999997,
            "AttentionInsideThisContent" : 2.273999999999992,
            "Gender" : "Female",
            "AngleYaw" : [
                0
            ],
            "XPos" : [
                0.9852976840852283
            ],
            "YPos" : [
                -0.9149562017596122
            ],
            "Distance" : [
                1.344602683844596
            ]
        }
    ],
    "PanelId" : "PANEL_1",
    "ScenarioId" : "Scenario-1",
    "StartTime" : ISODate("2015-04-13T10:06:22.622Z"),
    "EndTime" : ISODate("2015-04-13T10:06:29.640Z")
}

I used this function to put my data into a pandas dataframe but I have some issues with my embedded documents and array of documents:

def read_mongo(db, collection, query={}, host='localhost', port=27017, username=None, password=None, no_id=True):
    """ Read from Mongo and Store into DataFrame """

    # Make a query to the specific DB and Collection
    cursor = collection.find(query)

    # Expand the cursor and construct the DataFrame
    df =  pd.DataFrame(list(cursor))

    # Delete the _id
    if no_id:
        del df['_id']

    return df

Finally, i get a Dataframe with one column containing the FaceTagged informations gathered all together :

data.FaceTagged.to_frame()
                                           FaceTagged
0   [{u'Distance': [0.871754460354], u'XPos': [0.7...
1   [{u'Distance': [0.845591660012], u'XPos': [0.6...
2   [{u'Distance': [1.01813052012], u'XPos': [-0.7...

each line contain all the fields from only one document besides the fact FaceTagged is an array of documents, and each document contains severals fields.

Anyone can relate to this?


texts: Sort operator positioning in an execution plan

texts:
I am troubleshooting a performance issue and trying to get a stable execution plan for a reporting query. Links to the actual plans are below:

Good vs Bad

If you look at the Good plan, the sort is after the table joins. For the bad plan, the sort is just before the last join. In the case of bad plan since the sort is before the last join, and since the sort's estimated number of rows is 5000, SQL Server has decided to go with a nested loop join, which is slowing the query.

I do not want to use table join hint or change the variable on the TOP.

What are the strategies I can use in this scenario to get a consistent plan?

This query runs approximately every 5 minutes.


texts: Oracle 10gR2 - fix for SSL version following Poodle attack

texts:
We use Oracle utl_http to send https requests from within our database (10gR2).
As SSLv3.0 is now considered insecure (POODLE) we need to upgrade to TLS.
How can we ensure TLS usage when sending requests from PL/SQL?
Is a patch to the DB the only solution or is there another way?
Thanks
Terry


texts:
I having the following issues, anyone has faced this ‘msdb110_upgrade.sql’ encountered error 2627, state 1, severity:

  Cannot recover the master database. SQL Server is unable to run.
  Restore master from a full backup, repair it, or rebuild it. For more
  information about how to rebuild the master database, see SQL Server
  Books Online.

  Script level upgrade for database ‘master’ failed because upgrade step
  ‘msdb110_upgrade.sql’ encountered error 2627, state 1, severity 14.
  This is a serious error condition which might interfere with regular
  operation and the database will be taken offline. If the error
  happened during upgrade of the ‘master’ database, it will prevent the
  entire SQL Server instance from starting. Examine the previous
  errorlog entries for errors, take the appropriate corrective actions
  and re-start the database so that the script upgrade steps run to
  completion.


texts:
I am in the process of rolling out DBCC CHECKDB across our instances.

Each database will eventually either have a DBCC CHECKDB or DBCC CHECKDB PHYSICAL_ONLY.

The way I have been gauging whether a FULL CHECKDB should be done, or a PHYSICAL_ONLY is by examining the results from DBCC CHECKDB WITH ESTIMATEONLY.

However I ran this yesterday on SQL Server 2008 (SP1) and the tempdb estimation for a certain database was 510MB.

However in practice it filled the entire 19GB drive so the estimation was way off!

I was under the impression the ESTIMATEONLY bug was in SQL 2008 R2, not the standard 2008 ?

Is there another way to gauge tempdb usage? Some sort of database size to tempdb usage ratio: (I.e if I have a 500GB database how much tempdb consumption should I be expecting?)

I also need to implement test restores because we don't do that here. Once I have that server in place I will switch to PHYSICAL_ONLY for everything then do a full CHECKDB on the restored database.

Thank you