texts: Setting up a SQL Server cluster for the first time, some questions!

texts:
I am a SharePoint architect but looking to implement AOAG with SharePoint in an Azure lab environment.

I'm building the first node in a cluster and I'm a little confused:

I have my cluster object in AD, the installation asks me for a network name for SQL Server. What's the difference between this and the cluster object name?
Likewise, also the cluster resource group name.
Lastly, what's the IP address specified for?


texts:
We have queries in procedures in oracle like,

    SELECT  ......
    WHERE   .....
    AND (SomeColumn IS NULL OR SomeColumn = SomeThing)

But our DBA is saying this is not good, and saying use NVL instead. We are using this for long time and haven't found any issue. Should we change IS NULL with NVL? from NVL I mean NVL(SomeColumn, '') = SomeThing


texts: Determine when a PostgreSQL database was last changed

texts:
I'm looking at altering how backups are done and am wondering if there is a way to determine which databases in a postgreql cluster have not been recently changed?

Instead of using pg_dumpall, I'd like to use pg_dump and only dump those databases that have changed since the last backup (some databases don't get updated very often)-- the idea being that if nothing has changed then the current backup should still be good.

Does anyone know of a way to determine when a specific database was last updated/changed?

Thanks...

Update:

I was hoping to not have to write triggers all over tha place as I have no control over
the creation of databases in one particular cluster (let alone creation of db objects within a database).

Digging further, it looks like there is a correlation between the contents of the $PGDATA/global/pg_database file (specifically the second field) and the directory names under $PGDATA/base.

Going out on a limb, I'd guess that the second field of the pg_database file is the database oid and that each database has its own subdirectory under $PGDATA/base (with the oid for the subdirectory name). Is that correct? If so, is it reasonable to use the file timestamps from the files under $PGDATA/base/* as the trigger for needing a backup?

...or is there a better way?

Thanks again...


texts:
Non-sharded database. 2 replicas in repset.
Connected to mongos and ran db.dropDatabase(), got OK but db is still here.
Connected to mongod and ran db.dropDatabase(), got OK but db is still here.
Shut down all mongos instances, connected to mongod and ran db.dropDatabase(), got OK but db is still here.


texts: Best way to configure and maintain a table with many insertions and deletions that's empy on average?

texts:
There exists a "batch query" table that has two columns (BatchID, RecordID) which are both of TSQL type 'uniqueidentifier'. The unique clustering key is on (BatchID, RecordID), and there are no secondary indexes.

We use SqlBulkCopy to quickly insert thousands or millions of ids into the table under a single batch id, so that various queries can join against it to perform filtering using that set of record ids associated with that batch. That approach much faster than attempting multiple queries that all send lists of ids in the query string. As soon as the queries complete, the batch of record ids is deleted. The table is empty on average when no queries are running, but may have hundreds of active batches as we process thousands of API requests per second that may use the table.

My question is, what kind of maintenance steps, if any, are required of a DBA to maintain these tables. For example, would it benefit at all from periodic index rebuilding, or will SQL Server handle cleanup of the deleted rows (freeing page blocks, etc.) well on its own. Also, is there any special configuration of the table or clustered index that would help, especially considering that the identifiers are random Guids (uniqueidentifiers).

Generally we use the latest version of SQL Server,  Enterprise Edition. Some servers are in Azure, some in AWS.

I'm also interested in hearing alternatives. For example, I assume this approach is best with a permanent table, but I could also create a session-local temp table as part of a transaction that gets deleted when finished, rather than inserting and deleting rows from a permanent table. I just don't know how that would perform in comparison.


texts:
I want to create  a table with department name as column name and no. of employess for each department as its row. Here dept_name is coming from Dept table and count  of emp.ID is coming from emp table.

I am getting this error for my code

  Msg 4104, Level 16, State 1, Line 3
  The multi-part identifier "d.dept_name" could not be bound.

This is my query:

SELECT d.dept_name
from dept d
inner join emp e
on e.dept_ID=d.ID

Pivot
(
  count(e.ID)
  for d.dept_Name
  in([pROD],[elect],[inst],[fin],[otherdepartment])

  )
  as pivotab


texts:
when I ran dbca as administrator under windows server 2008R2 is used to delete the database completely ( database folder, control files, datafies, redo logs, then remove all related windows services to the database), but recently I noticed when I use it I found the services still exist as well as the trace (still generated), mail folder inside oradata still exist, as well as I can try to remove it countless time.

DB trace:

2015-02-16 15:31:48.434: [  OCRMSG]prom_waitconnect: CONN NOT ESTABLISHED (0,29,1,2)
2015-02-16 15:31:48.439: [  OCRMSG]GIPC error [29] msg [gipcretConnectionRefused]
2015-02-16 15:31:48.439: [  OCRMSG]prom_connect: error while waiting for connection complete [24]
2015-02-16 15:31:48.440: [ default]ut_read_reg:2:ocr registry key SOFTWARE\Oracle\olr cannot be opened. error 2

*** 2015-02-16 15:31:50.494

2015-02-16 15:31:50.494: [  OCRMSG]prom_waitconnect: CONN NOT ESTABLISHED (0,29,1,2)
2015-02-16 15:31:50.494: [  OCRMSG]GIPC error [29] msg [gipcretConnectionRefused]
2015-02-16 15:31:50.494: [  OCRMSG]prom_connect: error while waiting for connection complete [24]
2015-02-16 15:31:50.494: [ default]ut_read_reg:2:ocr registry key SOFTWARE\Oracle\olr cannot be opened. error 2

Any suggestion to fix this issue?


texts: Auto Increment Primary ID inserting random values

texts:
Today I was asked to look into this problem and I have never seen anything like this before. A table in our MySQL database, with a primary IDs column, which is set to autoincrement int, had some weird IDs. So the sequence goes as follows:

Some more random ids

You can see between 27243 and 27245, and 27324 and 27325 there are some different numbers inserted. This has caused a lot of problem for us since there are FK in another table which are now missing or messed up. Even today, there was one similar incidence.

In my many years of dealing with MySQL, this is the first time ever I am seeing something like this. What could have been the reason and how to correct it?

There are no triggers. Currently table create statement shows:

CREATE TABLE `txn` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`client_id` int(11) DEFAULT NULL,
`timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=27368 DEFAULT CHARSET=latin1;

Thanks.


texts: Proper implementation of child (derived) table PK/FK during single and multiple inheritance hard coded generalization physical data model

texts:
Below is an image of hard coded generalization UML logical model of entities.  I'd like to implement these classes as tables in a physical data model.

Two derived classes/entities (Class2 and Class5) show single inheritance generalization because they each are derived from a single base class/entity.  Class2 IS-A Class1.  Class5 IS-A Class4.

And Class3 shows multiple inheritance generalization because it has two base tables (or two parent entities).  Class3 IS-A (Class1 AND Class4).  Or you could read it Class3 IS-A Class1 OR Class3 IS-A Class4.

Here are my questions.  Choosing one example from each.  I'd like to be consistent.  How should this be implemented?  Or maybe I missed a scenario?

1.) Single inheritance: How would the PK/FK values get implemented in the derived tables (Class2 for example)?

(1a) Have attribute1 be PK of derived table and be FK #1 (pointing to only parent/base table PK).

OR...

(1b) Have attribute1 be PK of derived table.  And attribute2 be the FK to derived table (pointing to only base/parent table PK).

2.) Multiple inheritance: How would the PK/FK values get implemented in the derived table (Class3 for example)?

(2a) Have attribute1 be PK of derived table and be FK #1 (pointing to first parent/base table PK).  Then have attribute2 be FK #2 (pointing to second parent/base table PK).

OR...

(2b) Have attribute1 be PK of derived table.  And have attribute2 be FK #1 (pointing to first parent/base table PK).  And have attribute3 be FK #2 (pointing to second parent/base table PK).


texts: Full Text Search not searching special character

texts:
I am working on SQL Server 2008.
I have created full text index on a Table(Column) and I am trying to find.

SELECT *
FROM TblName
WHERE CONTAINS(ColName,'@#');

This is giving no results.

But I have a row in that column which contains value @# in field, so is this the default behavior of SQL Server Full Text Search, or am I doing something wrong and need to do something for this?


texts: Index is not used when joining two tables in a stored procedure

texts:
In MySQL 5.7, I have two tables:

CREATE TABLE `periodholdings ` (
   `stkcode` varchar(255) NOT NULL,
   `participantid` varchar(255) DEFAULT NULL,
   `participantname` varchar(255) DEFAULT NULL,
   `holdingdate` date DEFAULT NULL,
   `shares` bigint(20) DEFAULT NULL,
   `percentage` decimal(5,4) DEFAULT NULL,
   KEY `sp` (`stkcode`,`participantname`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `pholdings` (
   `stkcode` varchar(255) NOT NULL,
   `participantid` varchar(255) DEFAULT NULL,
   `participantname` varchar(255) DEFAULT NULL,
   `holdingdate` date DEFAULT NULL,
   `shares` bigint(20) DEFAULT NULL,
   `percentage` decimal(5,4) DEFAULT NULL,
   KEY `sp` (`stkcode`,`participantname`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `choldings` (
   `stkcode` varchar(255) NOT NULL,
   `participantid` varchar(255) DEFAULT NULL,
   `participantname` varchar(255) DEFAULT NULL,
   `holdingdate` date DEFAULT NULL,
   `shares` bigint(20) DEFAULT NULL,
   `percentage` decimal(5,4) DEFAULT NULL,
   KEY `sp` (`stkcode`,`participantname`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

In the SP, I called the following:

    drop table if exists pholdings;
    create table pholdings (index sp(stkcode, participantname)) as select * from periodholdings where holdingdate = '2016-09-01';

    drop table if exists choldings;
    create table choldings (index sp(stkcode, participantname)) as select * from periodholdings where holdingdate = '2016-09-21';

   explain
    (select p1.stkcode, p1.participantid, p1.participantname, if(c1.shares is null, -p1.shares/100, (c1.shares - p1.shares)/100) as averagediff from pholdings p1 use index(sp) left join choldings c1 use index(sp) on p1.stkcode = c1.stkcode and p1.participantname = c1.participantname)
    union
    (select c1.stkcode, c1.participantid, c1.participantname, if(p1.shares is null, c1.shares/100, (c1.shares - p1.shares)/100) as averagediff from pholdings p1 use index(sp) right join choldings c1 use index(sp) on c1.stkcode = p1.stkcode and c1.participantname = p1.participantname);

However, the index is not used at all.

1   PRIMARY p1      ALL                 407340  100.00
1   PRIMARY c1      ALL sp              412439  100.00  Using where; Using join buffer (Block Nested Loop)
2   UNION   c1      ALL                 412439  100.00
2   UNION   p1      ref sp  sp  1535    ccass.c1.stkcode,ccass.c1.participantname   70012   100.00
    UNION RESULT    <union1,2>      ALL                         Using temporary

If I just put the last EXPLAIN statement into the query and modify a little (like exchange the c1 and p1 around the =), the index works again.


texts: Advantages of using PgBouncer vs HAProxy?

texts:
Are there any advantages for using one over the other? I'm only looking at options to hide the actual IP address of the master and slave servers from my processes, and not having to restart them if the master fails.

If Nagios detects a failing master, I still want to get involved to do the failover, including telling HAproxy / PgBouncer about the new master.


texts:
We are facing the following error on our production database:

  ORA-00338: log # of thread # is more recent than control file

The database crashed and there was no recovery action. What could I do to resolve the problem?


texts: Does DBCC SHRINKDATABASE free physical disk space during its run?

texts:
I want to run exactly this command:
DBCC SHRINKDATABASE mydb

When I run this just for a while and then kill it, will the physical database file be a little bit smaller?
In other words: Does the command immediatedly free physical disk space during its run, or does it first a full reorganization, and only then free all physical space?
EDIT: We have simple recovery model, so our log file is very small and we only have one big database file.


texts: Restore database from individual datafile backups

texts:
I had a script that backs up all the individual datafiles like:

RMAN> BACKUP AS COMPRESSED BACKUPSET DATAFILE 'datafile<1>.dbf'

So i have backupsets of all the datafiles. I'm confused as to whether it would be sufficient to use RESTORE DATABASE, as can be done after a full database backup using BACKUP DATABASE? Isn't the end result of both BACKUP operations the same?

Thank you in advance.


texts: How to become a DBA...the non-standard route (not beginning as sysadmin or dev)?

texts:
Background:

I'm a recent BS graduate who's found myself in an extraordinary situation. I've fallen into something of an entry-level Oracle DBA role straight out of the college box. At my small company where the only 2 database people around have each been at it for over half the time I've been breathing, it's difficult to see the path between where I am and where they are.

From what I've read, many successful DBAs emerge from systems admin or developer roles, and I feel as though I've cheated myself - I've jumped the gun and now have no base in which to root new knowledge. Furthermore, I'm pulled in too many different directions as far as what to learn first. (Must understand B before learning A; but must have a grasp of A before diving into B!)

I often find myself wondering how to get where I want to go. What plan of attack might be the simplest for a beginner to begin? Width before depth or depth before width (i.e. an overview of the entire gamut while glossing over details OR an in-depth dive into RMAN or another specific topic). Then what about how Oracle plays with others like Linux, ASM/disk arrays, hardware, business applications, etc.?

Endless thoughts! Stressful implications!

Question:

Essentially, what resources (free or paid) would provide the most value up front, considering virtually no experience and only vague textbook education? Something that may provide exercises, lectures, etc. An ideal source would include more advanced or applied topics than basic SQL and db architecture without too much jargon.


texts: Cleaning up specific domain logins on about 50 servers and therefore the users

texts:
There is a specific need to clean up logins from the SQL Servers after migration from one domain say abc to new domain foo.

After migration as we still have those old logins and users under naming of abc\login1, abc\login2... and so on.

How can I perform this clean up to delete all the logins from the server, which has Windows logins say with domain abc\.. and within same script to delete all the related users within the instance for all databases.

I've searched the link SQL Server script to delete accounts no longer in Active Directory but it does not help in deleting users.

Note: These are just Windows logins and not part of any AD group. Just want to drop logins and the db users associated with it. Other domain AD users have been taken care by our AD and wintel team and users are all set. Just doing the clean up here.


texts: How to create and send mail in SQL Server 2008?

texts:
I want to send E-mail from SQL Server 2008. What are the basic steps for that? Can you explain me if someone used this facility.

Any help will appreciated.

this link will helpful as commented by  marc_s (also I have accepted an answer below)
http://blog.sqlauthority.com/2008/08/23/sql-server-2008-configure-database-mail-send-email-from-sql-database/


texts:
Let's say I have the following (contrived) table of monitoring data:

RowID       DateStamp            Success         Error
-----       -----------------    -------         -----------
1001        5/24/2019 11:23am    1               None
1004        5/24/2019 11:24am    1               None
1005        5/24/2019 11:25am    1               None
1009        5/24/2019 11:26am    0               SQL Timeout
1018        5/24/2019 11:27am    0               SQL Timeout
1019        5/24/2019 11:28am    1               None
1026        5/24/2019 11:29am    1               None
1035        5/24/2019 11:30am    0               Planned Maintenance
1100        5/24/2019 11:31am    0               Planned Maintenance
1111        5/24/2019 11:32am    1               None

I'd like to group rows with the same status and error, but only if they fall next to each other in sequence by date or by ID (ID will be ordered correctly, but is not guaranteed to be sequential), as follows:

Starting             Ending               Polls  Success   Error
-----------------    -----------------    -----  -------   -----------
5/24/2019 11:23am    5/24/2019 11:25am    3      1         None
5/24/2019 11:26am    5/24/2019 11:27am    2      0         SQL Timeout
5/24/2019 11:28am    5/24/2019 11:29am    2      1         None
5/24/2019 11:30am    5/24/2019 11:31am    2      0         Planned Maintenance
5/24/2019 11:32am    5/24/2019 11:32am    1      1         None

A simple GROUP BY doesn't work:

SELECT MIN(DateStamp) as Starting, MAX(DateStamp) as Ending,
      Count(*) as Polls, Success, Error
FROM myTable
GROUP BY Success, Error
ORDER BY Starting

Since that combines all rows with the same status/error, regardless of when they occur:

Starting             Ending               Polls  Success   Error
-----------------    -----------------    -----  -------   -----------
5/24/2019 11:23am    5/24/2019 11:32am    6      1         None
5/24/2019 11:26am    5/24/2019 11:27am    2      0         SQL Timeout
5/24/2019 11:30am    5/24/2019 11:31am    2      0         Planned Maintenance

Is there an easy way to do this in SQL Server 2012?


texts: SQL Server script to delete accounts no longer in Active Directory

texts:
We have a SQL Server 2000 that will shortly be migrated to SQL Server 2005. It has years of Windows Authentication accounts created that no longer exist in Active Directory, which prevent the Copy Database Wizard from creating these accounts on the new server.

Is there a script or some automated way of deleting the accounts that no longer exist in our Active Directory?

EDIT: Just to be clear, the logins needing to be deleted are on SQL Server 2000, which does not support the DROP LOGIN command.

Separately, manually deleting the logins in SQL Server 2000 would (I think) be done with exec sp_droplogin 'loginname' but on mine, the login name cannot be found, whether I use 'domain\loginname' or 'loginname'

Just to add to the confusion, exec sp_revokelogin 'domain\loginname' does appear to work.

EDIT 2: Finally resolved the issue. Many of the logons that were problematic were programmatically added to the database, and while they worked in the sense that a user could connect, the username vs NT login name had a mismatch of domain-prefixed logons when SQL Server expected no domain, and vice versa.

To resolve this, I modified the sp_droplogin procedure to take out one of the checks that was erroring.

I'm accepting my own answer as it works in SQL Server 2000.


texts: Does it matter if id (in TABLE ) starts from 0 or from 10000

texts:
Sorry for my incorrect terminology (as I don't know what to call that)..
does it matter (with regard to performance) if unique (index) key starts from 0 and from i.e. 100000 ?

For example, if I have

ID     |  Brand
----------------
1      | BMW
2      | FERARRI

or

ID     |  Brand
----------------
100000 | BMW
100001 | FERARRI

will be there any difference for performance/queries? I ask that because I have a PHP function that deletes everything and inserts new values. Thus, delete/insert/delete/insert causes to increase the ID more and more, and I am afraid, if I will eventually meet a problem? Or that's not a problem?


texts: Delete all data for a Single Vendor in the Dabatase

texts:
I have a Vendor table with vendor_Id as the PK and 10 other tables which have reference to Vendor.vendor_Id.

Now, if Vendor chooses to close their account, we need to delete all the data in the other 10 tables.

Is it a good idea to have ON CASCADE DELETE in 10 tables for the vendor_Id field or shall we delete the vendor data one table at a time?
Shall we write a Stored Procedure for the same ?


texts:
I have some databases on an instance used for reporting. I want to merge them into one. There are many cross-database dependencies in the stored procedures (e.g. an SP can use tables from different databases). Besides there are some SSIS packages that point to the different databases when they load data.
I think people who created the databases thought the a database is a folder and they made thier own system (e.g. "let us put these tables in a new database"). All objects are in the dbo schema and the names do not collude. I don't see any benefit of having multiple DBs. But many benefits of having one. E.g. I cannot create indexed views and objects from different databases are involved. I have to monitor the SPs from different databses, etc.
Is there any smart way of mergin the databases, or does everything have to be done manually? Is there a tool, e.g. on Redgate? So everything is on the same instance... Just some tips... Thanks.


texts: Transaction Log and mirroring - looking for dumbest explanation possible

texts:
First of all I must admit that I struggle with concept of Transaction Log. I mean - I do understand that it is Log of all Transactions that happen on database, but when it comes to properly putting it into context within some task I obviously lack something. So, to anyone who will be answering the question - please feel free to expand on theory behind Transaction Log.

Main question is - I have SQL Server 2008 and 2 GB database that I need mirrored (has 12GB transaction log). If I wasn't mirroring that database I presume I could either switch to Simple mode or truncate the log after backup. But in this case - what should I do if I wish to keep that transaction log under control? As I understand - I need to keep that whole transaction log if I wish to be able to easily mirror database (just do Full backup).

Is there any way around this? Ideally I wish it was possible to do backup that keeps both MDF and LDF in 1 file every time and after backup is done Transaction Log (LDF) on database is reduced to 0. The problem with this scenario are incremental backups - if my first backup truncated log, I presume that second backup would need to reference first one if I want to do mirroring later (i.e. I would be stuck with keeping bunch of files instead of just one).

So - can anyone enlighten me on this subject? I understand that I am trying to fill in lots of holes here and that my proposed "solutions" may not be the best ones, but I would sincerely appreciate if somebody can push me in right direction on Transaction Logs, how they impact mirroring and best practices with those two.


texts: How to decide when is use ERD or UML for data modeling?

texts: A UML diagram can be used both to model database designs and class designs, but an ER diagram should be used exclusively in database problems. How do you pick one graphical representation over the other?


texts: SQL Server 2008 server cannot connect to itself with alias name or “localhost”

texts:
We have a SQL Server 2008 ENT 64bit SP3 server running Server 2008 R2 Standard SP1 64-bit. This is also a windows/sql clustered environment. There is the cluster alias for the server, and then we have 2 AD DNS aliases as well for the server

Suddenly I can't connect to the sql instance in Management Studio, or with a Powershell Invoke-SQLcmd IF I am doing it from that actual server, when using one of the dns alias names, or "localhost". If I use the Cluster alias name it works and if I use the other dns alias name it works.

Using management studio with the non-working dns alias I get "Login failed. The login is from an untrusted domain and cannot be used with Windows authentication."

If I use localhost, I get "A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 2)"

Shared Memory and TCPIP are enabled in SQL Server Config manager but named pipes is not. But we've NEVER had named pipes enabled, and don't have it enabled on any of our other sql servers and they're set up the same (clustering, dns aliases...etc) and are having no issues.

I've Googled around and had a view people suggest listing what other SQL servers it can see (cmd prompt sqlcmd -L) If it do it from a good server I see all other servers on my network including the "bad" one. If I do that command from the "bad" server, I get no results at all.

Pretty much checked everything from this blog and it's all ok except the sqlcmd -L issue I just mentioned above.

http://blog.sqlauthority.com/2009/05/21/sql-server-fix-error-provider-named-pipes-provider-error-40-could-not-open-a-connection-to-sql-server-microsoft-sql-server-error/

Windows firewall is not enabled.

This was working just fine for a couple of years. I'm not sure when this started happening, but I know that at some point, it was not happening.

And the other kicker...it's only happening locally to this server. If I connect with management studio or run a powershell Invoke-Sqlcmd from another machine, or my local machine connecting to the "bad" server, it works fine no matter what alias I use.

I'm stumped. I think it sounds like an issue with the AD alias (there are 2 ad aliases, 1 works locally the other doesn't)...but not being able to connect with localhost makes me question that.

any help is greatly appreciated.


texts: Given two known statements, how can I replicate deadlock?

texts:
I have two statements (an update against every row in one table (call it table A) and a delete on another table that looks up rows in table A) that I know are causing occasional deadlocks. It seems that there is an X lock and a U lock on the same primary key index of table A.

I have been trying, and failing, to replicate the deadlock in SQL Server Management Studio. Should I be able to?

Separately, the delete statement is very inefficient and I think I can fix the issue by creating a covering index that means that the primary key index mentioned above is no longer included in the actual execution plan of the delete statement. Given that ultimately the same rows are required by both statements will this guarantee no deadlocks or simply reduce the chance of it happening by giving SQL Server a different path to the data?

I finally managed to get a deadlock graph as shown below:

If I am reading this correctly, the process node on the right has an exclusive lock on PK_LoanFacility and is requesting another one (maybe this is because there are two updates in the statement associated with this process node. The first sets a field to NULL for all rows and the second then updates a subset with values pulled from another database). The process node on the left has an update lock on PK_LoanFacility and is requesting a shared lock. The statement on the left is deleting a single row from a child table and is finding the parent id via a WHERE clause. i.e. DELETE FROM table where ForeignID = (SELECT ID FROM ParentTable WHERE x = y). I am unsure as to why this would require an update lock on PK_LoanFacility (the PK of the parent table). I am guessing that whenever you delete a row from a table, all the foreign keys are inspected and updates to the primary key indexes of all parent tables are made..?

As previously mentioned, I am fairly sure that I can eliminate the need for the requested shared lock by adding some proper indexes, but I am still interested to understand what is happening.


texts: SYMMETRIC key & certificates role in sql server?

texts:
1)

CREATE SYMMETRIC KEY SecureSymmetricKey
    WITH ALGORITHM = DESX
    ENCRYPTION BY PASSWORD = N'StrongPassword';

I'm trying to figure out about the SQL Server encryption.

once I've executed the code above , is there any way to find out later what is the password value for the SecureSymmetricKey ?

2)

If now i'm doing this with Certificates :
I'm ( the admin)  created

CREATE MASTER KEY ENCRYPTION
BY PASSWORD = 'DB Master key password!'
GO

only I know the password.

later I :

CREATE CERTIFICATE MyCertificate
WITH SUBJECT = 'My Certificate Subject'

CREATE SYMMETRIC KEY MySymetricKey
WITH ALGORITHM = TRIPLE_DES ENCRYPTION
BY CERTIFICATE MyCertificate

until now , Its all ok.

now , When a hacker comes to the computer  , all he have to do is :

OPEN SYMMETRIC KEY MySymetricKey DECRYPTION
BY CERTIFICATE MyCertificate

and then :

SELECT
       convert( NVARCHAR(max), decryptbykey(namePAss))
FROM  tbl1

so where is the protection in certificates ? no one asked him for a password ( like in password encryption (as in my first question)...?he only needed to know the certificate name

OPEN SYMMETRIC KEY MySymetricKey DECRYPTION
    BY CERTIFICATE MyCertificate

and its not a problem to find out what is the certificate name ...So where is the protedtion in decypher data from the Hacker ?

3)

When im using

CREATE SYMMETRIC KEY SecureSymmetricKey
    WITH ALGORITHM = DESX
    ENCRYPTION BY PASSWORD = N'StrongPassword';

DECLARE  @str NVARCHAR(100)
SET @str = 'lala';

OPEN SYMMETRIC KEY SecureSymmetricKey
    DECRYPTION BY PASSWORD = N'StrongPassword';

Whom am I trying to protect the data from ?
the data being sent from client to server ? ( the data is being sent by plain text - I can't activate SQL commands before sending the data...) or people who has access to the SQL server?


texts:
Hi im getting this error:
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.
this is my current SQL query:
DECLARE @UgpEntry VARCHAR(50)

SELECT @UgpEntry = UgpEntry FROM OITM t1 WHERE t1.ItemCode='CID-11418272385'

IF (@UgpEntry = -1)

SELECT
t1.ItemCode as sapitemcode
     , t1.CodeBars as Barcode
     , t1.ItemName as description
     ,LEFT(t1.ItemName,20) as short_description
     ,
           (select max(p.Price)
        from ITM1 p
        where p.ItemCode = t1.ItemCode
          and p.PriceList = 1) as [price_1]
     ,
         (SELECT T0.[ItemCode], T2.[U_NAME] as 'User that created Item', t0.updatedate

FROM AITM T0 inner join OUSR T2 on t0.usersign = t2.internal_K

WHERE T0.[ItemCode] = t1.ItemCode

group by T0.[ItemCode], T2.[U_NAME], t0.loginstanc, t0.updatedate

having t0.loginstanc = (select min(loginstanc) from AITM where itemcode = t0.loginstanc)),
     CASE t1.VatGourpSa when 'V0' THEN 4 when 'V1' THEN 1 WHEN 'V2' THEN 2 WHEN 'V3' THEN 3 END as TaxCode,t1.U_GRUPOA, t1.U_GRUPOB, t1.U_GRUPOC, t1.UgpEntry

FROM OITM t1
WHERE t1.ItemCode='CID-11418272385'

ELSE

SELECT t1.ItemCode as sapitemcode
     , t1.CodeBars as Barcode
     , t1.ItemName as description
     ,LEFT(t1.ItemName,20) as short_description
     , (select max(p.Price)
        from ITM9 p
        where p.ItemCode = t1.ItemCode
          and p.UomEntry = 1 and p.PriceList = 1) as [price_1mananitas],

           (select max(p.Price)
        from ITM1 p
        where p.ItemCode = t1.ItemCode
          and p.PriceList = 1) as [preciocajamananitas],
(select max(p.Price)
        from ITM9 p
        where p.ItemCode = t1.ItemCode
          and p.UomEntry = 1 and p.PriceList = 2) as [price_2cabra],
               (select max(p.Price)
        from ITM1 p
        where p.ItemCode = t1.ItemCode
          and p.PriceList = 2) as [preciocajacabra]

     , CASE t1.VatGourpSa when 'V0' THEN 4 when 'V1' THEN 1 WHEN 'V2' THEN 2 WHEN 'V3' THEN 3 END as TaxCode,t1.U_GRUPOA, t1.U_GRUPOB, t1.U_GRUPOC, t1.UgpEntry

FROM OITM t1
WHERE t1.ItemCode='CID-11418272385'

i just added this subquery:
(SELECT T0.[ItemCode], T2.[U_NAME] as 'User that created Item', t0.updatedate

    FROM AITM T0 inner join OUSR T2 on t0.usersign = t2.internal_K

    WHERE T0.[ItemCode] = t1.ItemCode

    group by T0.[ItemCode], T2.[U_NAME], t0.loginstanc, t0.updatedate

    having t0.loginstanc = (select min(loginstanc) from AITM where itemcode = t1.ItemCode))

when i run this alone i get something like this:

what im trying to do, is to add that select inside my main query so will appear as new columns inside the original query. So it should appear like new columns (T2.[U_NAME] and t0.updatedate)  at the end like this:

what im missing?
thank you


texts:
Question

How can I retrieve the current shift record based on getdate() and which lies between the existing ShiftStartTime and ShiftEndTime as per the defined table and data?

It is possible get shift if the time reaches 12.00 AM?

Table

create table ShiftMaster
(
ShiftID char(4),
ShiftName char(7),
ShiftDescripiton varchar(20),
ShiftStartTime time,
ShiftEndTime time
)

Data

insert into ShiftMaster
values
('SHF1', 'Shift A', 'First Shift', '07:15:01', '15:45:00'),
('SHF2', 'Shift B', 'Second Shift', '15:45:01', '00:15:00'),
('SHF3', 'Shift C', 'Third Shift', '00:15:01', '07:15:00')

Original Data (Screenshot)

What I Have Tried

 SELECT * FROM VHASM2.ShiftMaster
 WHERE FORMAT(getdate(),'HH:mm:ss') between ShiftStartTime and ShiftEndTime;

dbfiddle and Alternate Statement

select *
from ShiftMaster
where
'' + cast(datepart(hh, getdate()) as char(2)) + ':' + cast(datepart(n, getdate()) as char(2)) + ':' + cast(datepart(ss, getdate()) as char(2)) + '' >= ShiftStartTime
and
'' + cast(datepart(hh, getdate()) as char(2)) + ':' + cast(datepart(n, getdate()) as char(2)) + ':' + cast(datepart(ss, getdate()) as char(2)) + '' <= ShiftEndTime

The dbfiddle so far.


texts: MySQL connection timeout on connection spike

texts:
I have a little bit difficult problem, whenever a spike of connection occurs our MySQL server, connecting from all sources to the MySQL is practically impossible, the max_connections setting is not reached, load is ok 2-3 on a total 48 core.

My suspicion is the back_log and OS back_log, and my question is should i decrease or increase the back_log / os back_log ? or what am i doing wrong

Server info:

  OS Debian GNU Linux 7.7
  MySQL 5.6.16-enterprise-commercial-advanced-log
  CPU 2 x Intel(R) Xeon(R) CPU E5-2697 v2 @ 2.70GHz
  256 GB DDR3 @ 1866Mhz ECC
  RAID 10 (6 x 1.2 TB SAS 10k)

MySQL settings

  back_log = 2000 max_connections = 61440 max_connect_errors = 256
  key_buffer_size = 2048M table_open_cache = 32768 sort_buffer_size = 8M
  read_buffer_size = 8M read_rnd_buffer_size = 32M ft_min_word_len = 4
  thread_stack = 256K max_allowed_packet = 1024M bulk_insert_buffer_size
  = 64M thread_cache_size = 512 query_cache_size = 128M thread_concurrency = 48 innodb_read_io_threads=64
  innodb_write_io_threads=64 innodb_checksum_algorithm=INNODB
  binlog_checksum=NONE tmp_table_size = 128M max_tmp_tables=20480
  max_heap_table_size = 64M join_buffer_size = 128M
  innodb_buffer_pool_size = 160G innodb_thread_concurrency = 0
  open-files-limit = 655360 innodb_open_files = 655360
  plugin-load=thread_pool.so thread_pool_size = 16

OS settings:

  net.core.netdev_max_backlog = 20480
  net.ipv4.tcp_max_syn_backlog = 20480

P.S Please do not ask or leave links regarding to tutorials or mysql documentation, for i have done many tests and this is a question suited for Rolando or persons with strong mysql background.

Thanks,


texts:
Problems

Seeing this wait increase and very high, which it never shows up as a wait.
Users don't get back to the database when they make requests - it takes a while before they land on the database and when they do, they get an immediate resolution to query/insert/etc.
It really appears there's an issue with the users getting confirmed before querying - this is just a guess based on some reading.  Microsoft provides nothing on this wait that I can find.
The normal volume for users dropped 99% when this wait took off.
Developers seeing "you are not authorized to access microsoft iis" on their end - but we've checked disk space on all servers, which is fine.

What we've done:

Restarted all servers - SQL and web.
CPU, memory, disk all seem fine on SQL Server.
This wait continues to increase.
Error log doesn't show any abnormal behavior.

Overview of issue: people will run a query on the website, we don't see it on the back-end until much later, when I do see it, it runs immediately and they get their result.  There seems to be a delay from the intermediate server to the database server, possibly related to authentication (a guess based on this wait).  It's possible this wait is completely unrelated, but it continues to increase and I've never seen this.


texts: Using the same table for entities that are almost alike when it breaks foreign key integrity?

texts:
I'm designing a website with a database that should support events that a user can sign up for, fx. a concert. Users can sign up using our internal registration system, or using an external website (which we just redirect to).

So events can be internal or external. Both types of events have the same type of metadata such as date, title and place etc. If the event is internal then some can tables refer to the event ID using a foreign key, fx. the many-to-many table users_events, there's also some tables that handle billing-data related to internal events.

My current setup is a single event table that has the columns I mentioned before, date, title, place etc. It also has an external_signup_url column, which is NULL when an event is internal, and contains an URL for when an event is external. The problem with this design is that the tables that references an event ID by a foreign key can potentially reference an external event, which makes no sense at the application level.

So my question is, even though the two types of events are so similar (attribute-wise), does the small difference, and the potential non-sensical foreign key reference, really warrant separate tables, fx. external_event and internal_event?


texts: What's stored in data_dir/base of postgresql

texts:
I suddenly found found my PostgreSQL out of space. It's a tmp instance running on ram disk. Limited space (around 800M) allowed. Data are not actually stored on it, all table will be dropped after some calculation done.

I checked the folder and found tons of data under base folder:

$ du -h
6.0M    ./pgsql_tmp
706M    ./16384
6.0M    ./12780
6.0M    ./12772
6.0M    ./1
730M    .

My questions are:

What is inside that folder?
Any chance I can clean it out?


texts:
I want to deny query for insert,update,delete,alter in database engine only? is there a way to do that ? Because I have an application that uses the SQL database server and has a lot of users that consists of a lot of levels. I use the connection to the database with ADODB. How do I only can execute insert, update, delete or procedure only on the application, but can not run in SQL server database engine?

example: users A can insert,delete or update data into 'customer' through application, but cannot execute query that contain insert,delete, or update from database engine. because is too risky if users do that and can manipulate data from there.

Thanks Before


texts: convert date to timestamp and store into table using trigger mysql

texts:
Using trigger I convert DateTime into a timestamp and then store into another table. But somehow convert date into timestamp always affected by  timezone so that's why I had use convert_tz of date and then try to store in the database but it's not working for me.

TRIGGER :

DELIMITER $$
CREATE TRIGGER `insert_cdrs_data` AFTER INSERT ON `cdrs` FOR EACH ROW
BEGIN
        insert into cdrs_data1(`id`,`timestamp`,`accountid`,`type`,`calldate`) VALUES (new.id,UNIX_TIMESTAMP(CONVERT_TZ(new.calldate, @@global.time_zone, '+0:00')),new.accountid,new.type,new.calldate);
    insert into cdrs_data2(`id`,`timestamp`,`accountid`,`type`,`calldate`) VALUES (new.id,UNIX_TIMESTAMP(CONVERT_TZ(new.calldate,'+0:00',@@global.time_zone)),new.accountid,new.type,new.calldate);
END;
$$
DELIMITER ;

Here we can see that for table cdrs_data1
 UNIX_TIMESTAMP(CONVERT_TZ(new.calldate,@@global.time_zone,'+0:00')

For the table: cdrs_data2

UNIX_TIMESTAMP(CONVERT_TZ(new.calldate,'+0:00',@@global.time_zone)

OUTPUT :

cdr_data 1 :

mysql> select calldate,timestamp, @@global.time_zone, @@session.time_zone from cdrs_data1 where id ='44';
    +---------------------+------------+--------------------+---------------------+
    | calldate            | timestamp  | @@global.time_zone | @@session.time_zone |
    +---------------------+------------+--------------------+---------------------+
    | 2019-05-25 07:49:53 | 1558763393 | +00:00             | +00:00              |
    +---------------------+------------+--------------------+---------------------+

cdr_data2:

mysql> select calldate,timestamp, @@global.time_zone, @@session.time_zone from cdrs_data1 where id ='44';
+---------------------+------------+--------------------+---------------------+
| calldate            | timestamp  | @@global.time_zone | @@session.time_zone |
+---------------------+------------+--------------------+---------------------+
| 2019-05-25 07:49:53 | 1558763393 | +00:00             | +00:00              |
+---------------------+------------+--------------------+---------------------+
1 row in set (0.01 sec)

Setting global and session timezone into +03+00.

mysql> SET @@session.time_zone='+03:00';SET @@global.time_zone='+03:00';
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

mysql> select calldate,timestamp, @@global.time_zone, @@session.time_zone from cdrs_data1 where id ='45';
+---------------------+------------+--------------------+---------------------+
| calldate            | timestamp  | @@global.time_zone | @@session.time_zone |
+---------------------+------------+--------------------+---------------------+
| 2019-05-25 08:26:04 | 1558761964 | +03:00             | +03:00              |
+---------------------+------------+--------------------+---------------------+
1 row in set (0.01 sec)

mysql> select calldate,timestamp, @@global.time_zone, @@session.time_zone from cdrs_data2 where id ='45';
+---------------------+------------+--------------------+---------------------+
| calldate            | timestamp  | @@global.time_zone | @@session.time_zone |
+---------------------+------------+--------------------+---------------------+
| 2019-05-25 08:26:04 | 1558783564 | +03:00             | +03:00              |
+---------------------+------------+--------------------+---------------------+
1 row in set (0.00 sec)

After change database session and global timezone value timestamp value is not correct showing in above queries.

Question :
How to store date into timestamp using trigger in GMT+0:00 without change timezone or in other terms its should not affect by timezone.

Note :
Right now its not possible to restart mysql so i can't set timezone into the my.cnf file.


texts: Repairing Broken Binary Replication in PostgreSQL 9.0

texts:
I have a binary replication that was stopped for so long that the WALs were removed and as a result, it ended up being old. I'm trying to reestablish the replication and the best way I've found so far is following the steps on the PostgreSQL wiki:

Issue select pg_start_backup('clone',true); on master
rsync everything except for pg_xlog from master to slave
Issue select pg_stop_backup(); on master
rsync pg_xlog

But the database is too big (300 GB), my connection is not really amazing (like 800 kB/s) and the files in base keep changing. So I was wondering if there's a more optimal way to do this.


texts: Convert date yyyy-mm-dd to integer YYYYMM

texts:
How can I convert @dateb:

SET @dateb = dateadd(month, datediff(month, 0, getdate()) - 3, 0)

that returns 2014-04-04 as date to an integer of 201404

Thanks


texts:
According to the MySQL reference manual, the year type is restricted to at most 9999 if I understand it correctly. (See http://dev.mysql.com/doc/refman/5.7/en/year.html).

So how then do you store large future or past dates? For example 6000BC or what got me asking, 21974AD? I have tried to find examples of using larger dates but haven't been able to and wondered if it's at all possible to do so.

(Small background to this, was testing some Java date-time validations, and found trying to insert record for the year 21974 into the database, causes an exception, due to the previously mentioned limitation)


texts: Trigger performance - UPDATE statement joining on INSERTED

texts:
I have a trigger (MS SQL Server) on TABLE_A that fires on an update that looks basically like the following (I've changed the names of the tables/trigger to simplify):

CREATE TRIGGER [dbo].[TABLE_A_B_UPDATE] ON [dbo].[TABLE_A]
FOR UPDATE
AS

IF UPDATE ( [ATTRIBUTES] )
BEGIN
    BEGIN TRAN
    -- update the attributes of the table C records
    UPDATE [TABLE_C]
    SET [ATTRIBUTES] = I.ATTRIBUTES
    FROM TABLE_C C
    INNER JOIN INSERTED I ON
        C.UNIQUE_ID = I.UNIQUE_ID AND C.USER_ID = I.USER_ID
    WHERE E.[ATTRIBUTES] & 4 = 0

    UPDATE  [TABLE_B]
    SET     [ATTRIBUTES] = I.ATTRIBUTES
    FROM TABLE_C C
    INNER JOIN INSERTED I ON
        C.UNIQUE_ID = I.UNIQUE_ID AND C.USER_ID = I.USER_ID
    INNER JOIN [TABLE_B] B ON
        B.UNIQUE_ID = C.UNIQUE_ID AND B.USER_ID = C.USER_ID
    WHERE B.[ATTRIBUTES] & 4 = 0

    COMMIT TRAN
END

Currently by design, there is only ever ONE record in the INSERTED table (we are only updating one record at a time from a UI).

What we have discovered is that, as the number of records increase in TABLE_B, trigger performance degrades rapidly. For example, with around 12000 or so records in TABLE_B, this update statement takes around 40 seconds (we established a timeout of 30 seconds). As I remove records from TABLE_B, performance gradually improves. As this was an unacceptable solution, I had to find ways to improve this update statement.

Through testing/profiling, I found that the problem was with the second update statement (update TABLE_B). The first update statement works without problem; if I change the second update statement to its equivalent SELECT statement, it also runs fast.

The solution that I found was to shove the singular record in the INSERTED table into a #TEMP table and join on that instead. I was also able to do this with a table variable as well, but performance was terrible until I created an index on it. This immediately resolved the problem and the update now runs almost instantaneously.

My question is this - why did this solve the performance problem? Perhaps I am looking at this in the wrong way, but I can't imagine why I would need an index on a one record table. I have read that the INSERTED table isn't created with an index on it, but it still seems odd to me that I should need one.

Thanks in advance!

EDIT: As pointed out, I forgot to mention some other relevant table structure tidbits.

TABLE_B indeed has a compound primary key/index created as follows:

CONSTRAINT [TABLE_B] PRIMARY KEY CLUSTERED
(
    [USER_ID] ASC,
    [UNIQUE_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

TABLE_C has the same index as above. All indexes were rebuilt at the start of testing.

Both tables have additional triggers that are being fired - however, during testing, I disabled these triggers to determine where specifically the performance hit was. Disabling all other triggers did not improve performance.

EXECUTION PLAN:
I'm not super savvy on execution plans for triggers, but as far as I can tell, you can view them from the profiler with the showplan option turned on. I believe this is the relevant plan:

I'm not sure what to think about this plan, other than I noticed that 80% on that clustered index scan. Hopefully this is what was being asked for, if not, I can repost. Thanks again!


texts: Statement Based Replication still giving duplicate Entry Error

texts:
New to the replication. I am configuring multi-master replication. I did the following steps:

made one slave.
configured two masters to it.

Now i am performing following scenario:

disconnect both masters from this slave. (TEST_TABLE having the last row with P_ID 30)
After discoonecting, add an entry (into TEST_TABLE) on both MASTERS. (primary key entry with auto_incremeent went to 31)
reconnect slave to master-1 (It replicate the data with P_ID 31 on it)
reconnect slave to master-2 (Here comes the Duplicate Entry ERROR)

I am unable to figure out what the purpose of replication is if i have to face this issue? or am i missing something too basic?

I have done this with both Row Based Replication as well as Statement Based Replication but same error. So what's the point of SBR? Only to log statement rather than row data? Or if it's replicating the statement, why issuing the same P_ID and not just running the command generally and auto_incrementing the id to 32.

Reference query can be: **INSERT INTO TEST_TABLE ('firstname','lastname') VALUES('f_name_entry','l_name_entry')**

If really i am getting all this wrong and this is the exact behavior replication do, then whats the solution to it?

Banging my head for whole week on this. Kindly ask if anything is missing and Sorry if i am unable to explain something. Thanks in advance to bear me.


texts: Vacation Approvals Multiple requests into one request?

texts:
Not 100% sure if this makes the most sense to try and do in SQL.

What I am trying to achieve, is drive Vacation Approvals through SharePoint Lists using Flow.

Currently I am able to get the data from our scheduling system with the requests as individual rows, however I need to present the request back as a single item.

For Example:

The reason why I need it like this is because when I use Flow to populate the sharepoint list, it seems to be 1:1 mapping from field to field per row.

Not opposed to creating additional tables or request ID system to join all these together.

Thanks in advance,

Craig


texts: Nested table hierarchy: foreign key duplication vs. join

texts:
I have a nested table hierarchy, similar to the following one (this is an example but demonstrates the schema well enough):

+---------+  +---------+  +---------+  +-------------+
| users   |  | blogs   |  | posts   |  | comments    |
+---------+  +---------+  +---------+  +-------------+
| user_id |  | blog_id |  | post_id |  | comment_id  |
| email   |  | user_id |  | blog_id |  | post_id     |
+---------+  | name    |  | content |  | comment     |
             +---------+  +---------+  +-------------+

So, the object hierarchy is clear: a comment belongs to a single post, which belongs to a single blog, which belongs to a single user.

Now, in my app code, I want to verify that comments can only be read by the user who owns the blog which owns the post (assume these are private comments, ok?). E.g., when I SELECT * FROM comments WHERE comment_id = 666, I want to verify that the requesting user "owns" that comment.

My dillema is this: Would you advice that I:

duplicate the user_id to comments, so I always have a quick reference to the owning user, and checking the user id (during the select or later) is straightforward, or
Write my DB access code (a mapper or what have you) to always JOIN and fetch the user_id field as part of the returned object. Mind you that in this case I need two JOINs to do that

Basically I see it as performance vs. the so called bad practice of data duplication - but is it really such a bad practice in this case? It is unlikely that user_id values will ever change or that a blog changes its user_id.

I am using MySQL (MariaDB in fact) if that makes any difference.


texts: IDE Access to Hive without user password?

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

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

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

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


texts: Concurrent Update On Primary Key Column Making Deadlock

texts:
I have simple table T which have Col1, Col2,Col3 and Col4. columns. Col1 is primary key. Now I am running some concurrent code,
Parallel.For(0, list.Count, new ParallelOptions { MaxDegreeOfParallelism = -1 }, j =>
{
   var obj= list[j];
   // Do some work
   // UPDATE T SET Col2, Col3, Col4 WHERE Col1 =@Col1

Each value of col1 is different. But when I run this code I am getting too much deadlocks. Since Col1 is PK(Clustered Index), I am now confuse why this is happening?
In the deadlock vistim. I see Owner Mode: U
Whenever I run,
USE master
GO
DROP TABLE temp_sp_who2
GO
CREATE TABLE temp_sp_who2 ( SPID INT, Status VARCHAR(1000) NULL, Login SYSNAME NULL, HostName SYSNAME NULL, BlkBy SYSNAME NULL,DBName SYSNAME NULL,Command VARCHAR(1000) NULL, CPUTime INT NULL,DiskIO BIGINT NULL,  LastBatch VARCHAR(1000) NULL, ProgramName VARCHAR(1000) NULL,SPID2 INT , RequestId INT NULL )
GO

INSERT  INTO temp_sp_who2
EXEC sp_who2

SELECT  BlkBy ,count(*) FROM
temp_sp_who2 WHERE CAST(BlkBy AS NVARCHAR(MAX)) <> CAST(SPID AS NVARCHAR(MAX))
GROUP By BlkBy
ORDER BY Count(*) DESC
GO

I see BlkBy is different with high numbers. But BlkBy is always high when app is running.


texts:
Looking at performance data I realized sudden drops of Page Life Expectancy on a Sql Server 2016 SP1 running on VMWare consuming 58982 MB of 64 GB RAM. The previous value of PLE was around 133,000 and suddenly it dropped to 7,300 sec.

It looks like there was only one single query a candidate to cause this. I uploadad the execution plan.

That query ran very early in the morning so it looks like there was almost no additional activity on the system. It required 01:27 m:s runtime and caused 600,000 Reads.

Why did this query cause this drop of PLE?

What are the consequences of that drop?


texts: Removing/overwriting sensitive data located in already deleted records in ibdata files

texts:
As far as I know, using InnoDB tables with innodb_file_per_table = off does not prevent deleted data from being visible in the ibdata1 file.

I know this is by-design, and that I could switch the system setup to use innodb_file_per_table = on, which would allow the system to shred the ibdata* files containing the sensitive information, however there is "no server downtime allowed"

Could I simply overwrite the sensitive information in the ibdata1 file somehow, so that some erroneous this is sensitive reads this is xxxxxxxxx afterwards.

I'm not a DBA and neither know whether this would work out at all, or will destroy some checksums, crash the server, or whatever.

So my question is, what can I do to prevent leaking in the meantime without affecting the stability of the running server?


texts: innoDB row locking - rows locked one by one or atomically

texts: When one locks rows for Exclusive access using FOR UPDATE in a SELECT query, are all the rows one-by-one (potentially leading to a hold-and-wait condition for locks) or the lock is acquired atomically on all the rows (which means all rows are locked or none is locked)?


texts: User can create in SSRS, but can't manage objects

texts:
We have a user who can create, but is unable to manage objects in SSRS, even though he has content manager permissions and another user who has same permissions who is able to.

It is SSRS 2008 R2, the folder is nested, but permissions have been broken for only specific users that differ from parent folder.  User 1 is on Win7 computer with IE11 and Chrome, doing run as admin and not - not sure why he's doing that, but I didn't instruct him either way.

We have two users that started with same permissions (Browser, Content Manager, My Reports, Publisher, Report Builder), but user 1 can only create objects and cannot manage them.  We tried the following to no avail:

Remove all but content manager
Remove a security group that only had Browser permission, in case he was nested in there and it was granting lowest permissions only
Grant System User permissions in Site Settings
Granted Content Manager at parent folder level
Connecting through a different machine (RDP'd to a server)
Granted System Administrator permissions in Site Settings
Restarting user 1's computer
Clearing cache has been done several times
Trying from another user's Win7 computer

After System Administrator still didn't work, we asked user 2 to try, and it worked.  He only has Content Manager permissions.  That's where it got weird.  These users are both developers, and now user 1 has higher permissions than user 2, but he still can't change anything.  Yes, I confirmed the username is what he is using.

The error is:  The permissions granted to user 'domain\username' are insufficient for performing this operation

Has anyone experienced this or something similar, and can you share some wisdom?  After scouring the web, we're at a loss and our next step is to reach out to MS since this is to get us off a failing server.

Sorry if this has been raised before - I tried searching the site and only found one question that didn't match.


texts: multiple table inner join didn't get expected values

texts:
Student_register table:

regNo
pro(HNDIT,HNDA,HNDFT)
name
start_day
etc ... more columns

Semester:

regNo
curSemmester(3,4,5)
name

The query I used to get values:

SELECT student_register.pro='HNDIT', student_register.regNo,sem.curSem =4,sem.name
FROM student_register
INNER JOIN sem
ON sem.regNo = student_register.regNo
ORDER BY sem.regNo;

I need to get regNo from student_register that according to pro = hndit or any other (hndft/HNDA) then I want to compare those regNo to current semester which that user define curSem=3 (4/5/6) regNo Numbers

Get student numbers who do HNDIT course and then get regNO from semester table that user define semester(3 or 4 or 5 ) student who do Course hndit:

student_register.pro='HNDIT' -> sem.curSem =4 -> regNo and name


texts: Should I add an arbitrary length limit to VARCHAR columns?

texts:
According to PostgreSQL's docs, there's no performance difference between VARCHAR, VARCHAR(n) and TEXT.

Should I add an arbitrary length limit to a name or address column?

Edit: Not a dupe of:

Would index lookup be noticeably faster with char vs varchar when all values are 36 chars

I know the CHAR type is a relic of the past and I'm interested not only in performance but other pros and cons like Erwin stated in his amazing answer.


texts:
I have just deleted a big part of the data stored in one jsonb inside my users table (Its not needed anymore) and just keep an id inside that column. I expected to free a lot of disk space with this action.

UPDATE users SET my_jsonb = '{id: xxxx }';

The problem is that when i try to see the free space in the disk remains the same

/dev/md125      935G  501G  387G  57%

Also the table

 Table                                            Size        External
 users                                          | 497 GB     | 478 GB

My last option is to try a VACUUM FULL users but since this table is used in production environment it would not be convenient to lock it a unkown ammount of time.

Im running a postgres 10.5 database on a CENTOS RHEL7 machine.


texts:
Is there any frequency at which i should run REORG and RUNSTATS on my master tables?
If i have a daily load of around 1,000,000 records into my table would it be suggestible to run a REORG  and  RUNSTATS everyday after the Load?
Are there any drawbacks to REORG  and RUNSTATS?

I understood what REORG and RUNSTATS does from the links here. But could not conclude  on the Impact it  might have on Overusing or Underusing it.


texts: DELETE from Linked Server table using OPENQUERY and dynamic criteria

texts:
I'm attempting to run a DELETE statement from my local SQL Server to delete appropriate records from a table in my remote linked server using OPENQUERY(). I'm using the following code, but it seems slow and I don't want to be pounding my server with bad SQL. Should I be using OPENQUERY() for this? Should I be deleting from it a different way that's more direct?

Here's my code:

DELETE RemoteTable
FROM OPENQUERY(MyLinkedServer,'SELECT * from RemoteTable_T1') AS RemoteTable
INNER JOIN MyDB.dbo.LocalTable_T1 AS LocalTable
ON LocalTable.LocalTable_T1_EntryID = RemoteTable.RemoteTable_T1_EntryID;

Any improvements I can make on this code since it is going to be set up as a recurring task on my server? I have about 5 different variations of this in order to delete from 5 different tables on my linked server.


texts: SQL Server Extended Events - How to filter statements by Table?

texts:
I am curious, is it possible to filter the SQL Statement by table (table id or something)?

What I can think of, is to naively add another 'Statement' with Operator 'like' '%MyTable%'. Is that any 'Field' to filter the Table?


texts: Assign maximum memory limit for a particular database in sql server

texts:
In my SQL-Server instance, there are many databases. I want to restrict the memory size of each database (for example 8 GB). When I tried using Resource Governor, there are options like max_memory_percent and min_memory_percent and it is given in percentage value (values from 0 to 100). But I want to give the database size explicitly in GB and it should not go beyond that maximum limit.

Is there any way to restrict the database size or to get alerts if the database size exceeds the limit?

Can I implement this using Resource Governor?

ALTER RESOURCE POOL


texts: Viability of a large database without using indexes and some other things

texts:
I'll explain my situation as briefly as possible. My group is working for another company and I'm mostly creating stored procedures for making reports.

The twist is that, last month, they decided to do some major changes, which increased a lot the time required to what before worked nicely. I don't know the details, but one of the big changes is that what used to be tables now are views that get the data from an external source. Those are defined as:

CREATE view [dbo].[eng_contra_env] as
select ce_serial, ce_empresa, ce_ganado, ce_contra, ce_fecha_env, ce_fecha_rec, ce_observa
  from OPENROWSET('MSDASQL', '***', 'SELECT * from eng_contra_env');
GO

Also, since that change, I'm not allowed to do some basic things like indexing some tables, as my boss tells me not to do so due to those changes that the client did.

So, I'm asked to improve the performance of some procedure (which again, before the changes it finished, now it doesn't). One bit of the procedure requires me to do the following:

https://www.brentozar.com/pastetheplan/?id=SkDJAvZQI

How am I supposed to optimize that? Also, with what I said already, how viable do you think it is to work under these conditions.

Update: Apparently they had a meeting at the client and they decided to revert these changes. Thank god!


texts: How to view the current settings of Autovacuum in Postgres?

texts:
I've found a million examples of how to set Autovacuum's configuration settings, but can't seem to find how to list the current configuration.

Postgres 9.1 is the version I'm most interested in.


texts: SQL Server - Show Content of a partition

texts:
I did somes partitions on my sql database with the wizard of microsoft Sql Server Management Studio. But i wanted to know if it's possible to do an request for show just lines contains by one partition.

I tried this :

select * from  MyTable where $PARTITION.LinkTest(ID) = 2;

But it's just show me the result of the partition function LinkTest not the lines contained on one filegroups or file. Someone know how I can do that?


texts: How to control number of columns after pivoting date as column

texts:
Here is the sample query:

DECLARE @cols NVARCHAR(MAX) DECLARE @query NVARCHAR(MAX)

Select @cols = COALESCE(@cols +',[' +  CONVERT(VARCHAR(10), ReportedDate,120)+']',      '[' + CONVERT(VARCHAR(10), reportedDate, 120)
+']')       FROM (SELECT DISTINCT CONVERT(VARCHAR(10),reportedDate,120)         AS reportedDate FROM Activity       ) PV ORDER BY reportedDate desc

SELECT @query = 'SELECT * FROM (    SELECT  b.Description, CONVERT(VARCHAR(10), reportedDate, 120) as reportedDate,Status FROM Activity    left join ActivityType b     on b.activityTypeId = Activity.ActivityTypeId ) AS t PIVOT  (   COUNT(reportedDate)    FOR reportedDate IN( ' + @cols + ' )' + ') AS p ;'

EXEC SP_EXECUTESQL @query

This is my sample image:
I want to control the date columns limit into only 8 columns.
as reference of what I mean please see the second table and its different from the first table.

So in my first table date column from '2020-01-07' up to '2019-11-12'
However my second table only up date '2020-01-07' up to '2019-11-19'

I want to get the result as shown in this second Table
assuming that this date '2020-01-07' is newly added.
so the '2019-11-12' will not be included.

I hope I did will explaining my point.


texts: Backup and Restore SQL server files and filegroups

texts:
I have a database called Admin. It has only one default filegroup called primary.

File group: primary
files in the file group: Admin_data.mdf

Now I have taken a full filegroup backup.

Backup file name: fg_full.bak

Then created a new ndf in the same file group called sec_file.ndf and created few tables, inserted 1000 rows on each table.

After an hour took a file backup of my MDF and NDF files

MDF backup: Admin_Data.bak
NDF backup: sec_file_ndf.bak

File Backup using SSMS

Here somehow my sec_file get deleted so I want to restore latest NDF backup, how can I restore it?

Output of restore headeronly

Restore filelistonly from disk = 'D:\sec_file_ndf.bak'


texts: How to add the aliases to the group by clause

texts:
In the below query how to add the aliases assignedTOiti, assignedTOasstAdm, assignedTOhlp, closedDate to the group by clause.

I am struggling for the past few hours.

SELECT ieor.aper07_req_k                                  AS reqno,
       ford.apeh09_supervisor_cds_d                       AS ll6CdsID,
       'Ford Offboard Req'                                AS reqtype,
       par_val1.aper17_parameter_val_x                    AS requeststatus,
       (SELECT Min(iti2.aper07_req_created_s)
        FROM   iaper07_employee_offbrd_req iti2
        WHERE  ieor.aper07_req_k = iti2.aper07_req_k
               AND iti2.aper06_req_status_k = 4)          AS assignedTOiti,
       (SELECT Min(hlp2.aper07_req_created_s)
        FROM   iaper07_employee_offbrd_req hlp2
        WHERE  ieor.aper07_req_k = hlp2.aper07_req_k
               AND hlp2.aper06_req_status_k = 5)          AS assignedTOasstAdm,
       (SELECT Min(asst2.aper07_req_created_s)
        FROM   iaper07_employee_offbrd_req asst2
        WHERE  ieor.aper07_req_k = asst2.aper07_req_k
               AND asst2.aper06_req_status_k = 12)        AS assignedTOhlp,
       (SELECT Min(clsd2.aper07_req_created_s)
        FROM   iaper07_employee_offbrd_req clsd2
        WHERE  ieor.aper07_req_k = clsd2.aper07_req_k
               AND clsd2.aper06_req_status_k IN ( 3, 9 )) AS closedDate,
       ieor.aper07_assigned_to_cds_d,
       ieor.aper07_comment_x,
       ieor.aper07_last_updt_user_c,
       ieor.aper07_last_updt_s                            AS last,
       ieor.apeh09_employee_k,
       ieor.aper06_req_status_k,
       ipw.apeh04_first_n,
       ipw.apeh04_cds_d,
       ipw.apeh04_last_n,
       ipw.apeh04_person_type_c,
       irs.aper06_req_status_x,
       offr.aper15_offboard_reason_x                      AS offboardReason,
       Min(aper07_req_created_s)                          AS creationDate
FROM   iaper07_employee_offbrd_req ieor,
       iapeh05_person_vw ipw,
       iapeh09_ford_employee ford,
       iaper06_req_status irs,
       iaper15_offbrd_req_reason offr,
       iaper17_parameter_values par_val1
WHERE  ford.apeh09_cds_d = ipw.apeh04_cds_d
       AND ieor.apeh09_employee_k = ipw.apeh04_visitor_k
       AND irs.aper06_req_status_k = ieor.aper06_req_status_k
       AND offr.aper15_offboard_reason_k = ieor.aper15_offboard_reason_k
       AND ieor.aper06_req_status_k = par_val1.aper17_parameter_val_r
       AND ipw.apeh22_region_k = 1
       AND ieor.aper07_last_updt_s = (SELECT Max(aper07_last_updt_s)
                                      FROM   iaper07_employee_offbrd_req b
                                      WHERE  ieor.aper07_req_k = b.aper07_req_k)
       AND ieor.aper07_req_k = 3387
GROUP  BY ieor.aper07_req_k,
          ford.apeh09_supervisor_cds_d,
          ieor.aper07_assigned_to_cds_d,
          ieor.aper07_comment_x,
          ieor.aper07_last_updt_user_c,
          ieor.aper07_last_updt_s,
          ieor.apeh09_employee_k,
          ieor.aper06_req_status_k,
          ipw.apeh04_first_n,
          ipw.apeh04_cds_d,
          ipw.apeh04_last_n,
          ipw.apeh04_person_type_c,
          irs.aper06_req_status_x,
          par_val1.aper17_parameter_val_x,
          offr.aper15_offboard_reason_x
HAVING ipw.apeh04_person_type_c NOT IN( 'c', 'C' )


texts: AWS RDS Performance Insights not showing SQL Server Queries

texts:
I enabled Performance Insights on an existing SQL Server database (Express, if it matters) in AWS RDS. If I look at the database load graph and table I do not see any of the queries I am doing in real time. I see some queries that appear to be done by Amazon itself, such as selecting from rdsadmin.dbo.log_backup_manifest.

If I change the range to the past 24 hours, I see what appears to be a couple non-system queries on our actual data by another user. But I can't get any new queries to appear. I show up as a Host on the Hosts tab and as a User on the Users tab. The dashboard shows "0 sessions" even when I have SSMS open and connected to it, though.

Is there some condition I need to meet in order to have my query get recorded in Performance Insights? What's the criteria? How can I troubleshoot this?


texts:
This question is about teaching:  When I learned databases, we talked about indexes being like the card catalog in the town library.  We had at least an author index, a title index, then the Dewey decimal number was the access path to the book, which is like the whole record in the database.

Books?  What, on paper?  Index cards in drawers?

The youngsters don't really know what I'm talking about, so how do we best explain it nowadays?

(Feel free to enjoy my lawn, just please recognize the difference between the grass and the astroturf at the prep school, ok?)


texts: How to reduce redundant time-series data in MySQL into a function?

texts:
i have a question that actually does not fit here very much, but maybe it fits. So, i have data like this:

How can i reduce this 3 entries with same values? Sure, i can just delete the other 2, but in time-series data i can not just do this, this would hide the information, that in between the real time 15:19:45 and 15:19:55, the value did not change. So i thought about saving the delta-time and value in a separate table and only save the first entry and mark it, but i dont know if this is the best way to do so.


texts: Count number of duplicate rows, sort from most to least (statistic)

texts:
I'm trying to make statistic of top-10 most visited URL. I have one simple table. For each URL visit, one row added with that URL as content. Most visited URL has the most number of duplicate.

I've found many reference to SELECT count(*) FROM tablename WHERE url='tofind' but that only works if I supply the URL data.

Suggestion are welcome.


texts: Users database to store access levels, can I move the non-essential information to another table?

texts:
I would like some advice on the best database structure for my system.

I have different kinds of users with different access levels, and different information of course. I created a users table with the following fields:

id
student_number
password
first_name
last_name
address
email
mothers_name
mothers_occupation
fathers_name
fathers_occupation
religion
civil_status

The problem is that some users just don't need all those fields. The only user type that needs all those are the students users. The student users have a different 'environment' / module that sends a request to an api, that then sends back a json response.

Other fields, for other type of users, are not applicable to them, for example, an admin, he/she mustn't have those other fields (I could use dummy data, but I am looking for a cleaner approach).

I was thinking I can just create tables like this:

Users table

id
username
password

UserInfo table:

user_id (FK)
first_name
last_name
...

What do you think? Any examples/erd of big users database?


texts:
At work we keep receiving the following DataException seemingly at random when one of our processes tries to write/access a table for one of our clients:

com.ibm.db.DataException: A database manager error occurred. :
[IBM][CLI Driver][DB2/NT64] SQL0290N  Table space access is not allowed.  SQLSTATE=55039

Has anyone encountered this? I'm not the person who primarily does administrative tasks on our databases, but even they seem to be having difficulty finding the root of this problem. Any suggestions? Anyone encounter this before? This error comes up for only one of our clients at a time, and it generally seems to rotate. We have Rackspace service but they wont be of much help unless we can provide screenshots, etc at the exact moment this occurs.

Apologies if this post may be too vague, please let me know what information to supply to clarify things more. I'm one of the developers in my office, but I don't primarily handle the administrative tasks on our databases.

edit: We spoke with IBM and this could possibly be caused by some sort of virus scan being run by IBM/Rackspace as a part of maintenance? They said this kind of dubiously though, so I am doubting this is the culprit because tables remained locked for variable amounts of time.


texts: How to aggregate calculated measures involving dimension attributes?

texts:
The business case is as follows -
I have a cube of sales transactions and a dimension of parts. One of the attributes of a part is the current cost, which is non-aggregatable. A user needs to model different scenarios based on costs that vary from the current ones.

My solution-
Add an additional field 'Proposed Cost' to the relational table, mark it non-aggregatable and enable writeback. Create a calculated measure multiplying the proposed cost times the qty measure to get an extended cost. I enable what-if analysis in Excel and we're set

The Problem-
Since the standard cost is non-aggregatable, any measures derived from it are as well. I don't get totals along any group in that dimension. There's no reason that the extended costs could not be summed, so I am guessing that this is a consequence of the design of the product.

Is there any way to accomplish this of is my whole plan fatally flawed?


texts: Selecting two values from the same table but under different conditions

texts:
I want to grab a value from a table into two different columns for different values from the same table. Use this query as an example (notice how the select is on the same table aliased as 2 different tables):

SELECT a.myVal, b.myVal
FROM MyTable a, MyTable b
WHERE
  a.otherVal = 100 AND
  b.otherVal = 200 AND
  a.id = b.id

When I run a relatively simple query like this on my dataset, it works - it just takes a long time. Is there a better/smarter way of writing this query?


texts: Using different servers for front-end and back-end?

texts:
I recently noticed that the load time of my website has slowed significantly.  I'm not a developer, but my programmer mentioned this is due to the large amount of data that is being searched through when a user comes to the homepage (due to recording tracking information).

Would using separate servers for the front-end code and the back-end database help resolve this problem?  Any recommendations I can talk to my developer about would be helpful.

Thank you,

David


texts: How to automatically increment several Integer columns based on alphabetically ordered text columns?

texts:
Hello and thank you very much in advance for taking the time to look at my question. My question is as followed, I have a large dataset in PostgreSQL, recorded in a similar fashion as it is written in the table.
The Province, District and Town column are constantly updated and new rows are added. I would like to have incremental numbers for the Province_id, District_id and Town_id automatically assigned whenever a new entry is made in one of the text columns. Preferablly, the numbers would update dynamically whenever a new Province, District or Town is entered (alphabetically).
Could I use triggers to make this automatically? I have also looked a lot at things like the dense_rank() functions (but is this a good method to follow?) I would be very happy to hear if there are alternative solutions. Thank you.


texts: Postgresql: invalid input syntax for integer: "false"

texts:
Below is my table structure

CREATE TABLE configurations
(
  id serial NOT NULL,
  key text,
  description text,
  value text,
  CONSTRAINT configurations_pkey PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);

I am inserting data with below query

insert into configurations
(id, key, description, value)
select 1,'key1','D1',25
UNION
select 2,'key2','D2',365
UNION
select 3,'key3','D3','2017-01-01'

But i am getting below error

ERROR:  invalid input syntax for integer: "2017-01-01"
LINE 7: select 3,'key3','D3','2017-01-01'

I don't know why i am getting this error. value "2017-01-01" is a text value and column data type is also text then why i am getting integer specific error.


texts: INSERT with ON CONFLICT does not insert rows as expected

texts:
We have two tables:
CREATE TABLE listing_master (
    id SERIAL PRIMARY KEY,
    feed_id character varying(20) NOT NULL,
    feed_listing_id character varying(20) NOT NULL,
    updated_time timestamp(0) without time zone NOT NULL
);

-- Indices -------------------------------------------------------

CREATE UNIQUE INDEX listing_master_pkey ON listing_master(id int4_ops);
CREATE UNIQUE INDEX listing_master_feed_id_feed_listing_id_idx ON listing_master(feed_id text_ops,feed_listing_id text_ops);

And
CREATE TABLE listing (
    id SERIAL PRIMARY KEY,
    mls_num character varying(20),
    feed_listing_id character varying(20) NOT NULL,
    feed_id character varying(255) NOT NULL,
    list_price double precision,
    postal_code character varying(255) DEFAULT NULL::character varying,
    photos_count integer,
    unparsed_address character varying(255) DEFAULT NULL::character varying,
    city character varying(255) DEFAULT NULL::character varying,
    status character varying(20) NOT NULL DEFAULT 'new'::character varying,
    processing_status character varying(20) NOT NULL DEFAULT 'none'::character varying,
    last_update_from_feed timestamp(0) without time zone DEFAULT NULL::timestamp without time zone,
    images_data jsonb,
    coordinates point,
    raw_data jsonb,
    deleted_date timestamp(0) without time zone DEFAULT NULL::timestamp without time zone,
    state_or_province text,
    country text,
    type character varying(40) DEFAULT NULL::character varying,
    ownership_type character varying(40) DEFAULT NULL::character varying,
    bedrooms integer,
    living_area integer,
    lot_size integer,
    year_built integer
);
COMMENT ON COLUMN listing.images_data IS '(DC2Type:json_array)';

-- Indices -------------------------------------------------------

CREATE UNIQUE INDEX listing_pkey ON listing(id int4_ops);
CREATE UNIQUE INDEX listing_feed_id_feed_listing_id_idx ON listing(feed_id text_ops,feed_listing_id text_ops);
CREATE UNIQUE INDEX listing_mls_num_feed_id_state_or_province_idx ON listing(mls_num text_ops,feed_id text_ops,state_or_province text_ops) WHERE state_or_province IS NOT NULL AND status::text = 'live'::text AND mls_num IS NOT NULL AND deleted_date IS NULL;

listing_master is populated from an API call and holds all the records that eventually have to exist in the listing table.
So, let's assume
listing_master has 100k records
listing has somewhat less
In order to verify that I ran this query:
select *
from listing_master
where feed_id = 'ddf'
 and feed_listing_id not in (select feed_listing_id
                             from listing
                             where feed_id = 'ddf'
                               and deleted_date is null );

And, as promised, this gave me 136 records back.
Now, I want to populate the missing listings from listing_master into listing, and trying it this way:
insert into listing(feed_id,feed_listing_id,status,processing_status)
select lm.feed_id, lm.feed_listing_id, 'new' as status,'none' as processing_status
from listing_master lm
on conflict (feed_id,feed_listing_id) do nothing;

For some reason, this inserts 0 records, while it clearly should add 136 of them.
What am I missing?
I am in PostgreSQL 12.


texts: Unable to connect to PostgreSQL database within pgAdminIII

texts: I have two databases within one Postgres server, and I am unable to connect to one of these databases. Upon a connection attempt, pgAdmin gets hung on "retrieving details on database." I'm able to connect to all other databases successfully.


texts: Which databases can meet these replication requirements?

texts:
I am doing research on database architectures, and I am looking for which software can meet my requirements.

To try to explain the requirements simply:

You have a master database server. This may make a one or a few identical replicates for distance purposes - such as one for US and another for Europe. These masters would have identical clones locally for failover.
This master then does one-way slave propagation to many read-only servers via VPN. These servers serve-up all data reads to a LAN at that server's location, serving a few hundred clients at each location. Ideally, the read-only slave DB could mirror locally for failover.
All writes are made to the master server(s) and then make their way back to the slaves. The application this architecture serves is far more read intensive than write.
The slaves require intelligent granularity. The replication must not only be table-specific per site, but also must be row-specific in some tables. For example, the master server would have a monolithic client table with all clients across all sites. The client table should replicate in part to the slaves, containing the same columns however only having those clients that have purchased from that local branch. We do not want the local branch to have access to all clients.

__

Though relatively novice in database administration, I have done a bit of research thus far. It seems Postgresql is quite a positive choice for a database, and the closest replication middleware I can find to fit these requirements is PgPool-II. I do not, however, know if it will meet all of these requirements easily and in a way that will scale.

What, if any, major database such as PostgreSQL or MSSQL, is capable of satisfying the above requirements? Does it do this natively? And if not natively, what middleware for that database is capable of handling it?


texts:
Hi I've stumbled into the administration of a Legacy SQL 2000 server that has had next to no maintenance for the past 8 years (and is very slow).  As I have only been doing DBA / PT for the past 2 years (I'm primarily a Developer) I don't have allot of experience on 2000.  Can anyone point me in the direction of some maintenance, tuning scripts.  Just figured I'd ask first before I reinvent the wheel for something I will rarely use.

Thanks Guys / Girls !


texts: MySQL query to get same column data in two rows


texts: Cleaning up archives in Postgres after using systemd

texts:
I'm using Postgres 9.3 and I have a master database and a hot standby one that is following the master.
The operating system is CentOS 7.

I've changed the way of starting Posgres from SysV to systemd.
I've used the standard /usr/lib/systemd/system/postgresql-9.3.service, and it's much more simplified than the old SysV script.

After the change, I've observed that it continues to work ok.  The only downside is that the archives in the hot standby are never deleted.  It is like the archive_cleanup_command in recovery.conf were ignored.

In the .service, Postgres is started in this way:

pg_ctl start -D ${PGDATA} -s -w -t 300

whereas in SysV in this other way:

postmaster -p $PGPORT -D $PGDATA

I think both of them are quite equivalent.

If I change back to SysV, then archives are not accumulated any more.

Why my archives aren't cleaned up with systemd?


texts:
I have a question concerning performance settings/setup in MySQL DB/InnoDB.
The server has SSD disks so general access to data is fast. Although there is enough of RAM, but enough is never enough and I must make some limitations somewhere.

Does it make sense to reduce innodb_buffer_pool_size and read_buffer_size - and use available RAM to increase sort_buffer_size, join_buffer_size, tmp_table_size, max-heap-table-size - so that joins and temporary tables would not be written to disk at all?

In other words - I want to reduce disk read cache (because SSD can read data really fast) and increase RAM for joins and for temporary tables, to avoid tables written to disk (i.e. make all temporary activity in RAM only). Yes, I know that SSD write is also faster than HDD write, but if I can avoid/minimize disk write - then query execution would be even faster.

Thank you for your opinions.


texts: Does a transaction cover the subroutines?

texts: When I begin transaction and exec a stored procedure, does that transaction cover the changes made by the stored procedure I executed? Would a rollback transaction cancel the changes made by the stored procedure which is executed?


texts: Reorganize Objects in OEM - Host Credentials do not have enough privileges

texts:
When I try to re-organize objects from OEM (11g) and enter my host credentials (not "oracle"), I get the message:

  "An error occurred verifying the host credentials. Make sure the
  credentials are valid and that they specify an account with enough
  privileges to write to the directory where the reorganization script
  will be created. Credential verification error: ERROR: Invalid
  username and/or password"

My host username is a member of the oinstall and dba groups.  I guess my first question is what directory is it that I need write access to?  My second question is what privileges do I need in order to run a reorg (not as the "oracle" user)?

I should say that this is a linux Oracle server.


texts:
When I run exec dbo.subprogram from SQL Server Management Studio, it executes and returns result as many rows affected.

But when I run from cmd
C:\Users\mssadmin>sqlcmd -S servername -d databasename -q "EXIT( exec dbo.subprogram)"
it returns as 0 rows affected.

What could be the problem?


texts: How to check sybase command line history?

texts: How can I check the previous command line history in sybase command line?


texts:
A question that arose in a chat discussion:

I know hash join bailout switches internally to a sort of nested loops thing.

What does SQL Server do for a hash aggregate bailout (if it can happen at all)?


texts:
I’m having a problem with a result set that I am getting from a stored procedure when running a PowerShell script (Using Invoke-SQLCmd) to get the results and send the output to a .csv file.

The date column is bringing back the time as well, see below. I have setup a simple example in Adventure works.

My Command is to execute a simple stored proc to just get the Top 10 From the Sales Order Header in Adventureworks.

Invoke-Sqlcmd -ServerInstance "MY-PC\SQL2016" -Database "AdventureWorks2016" -Query "EXEC[AdventureWorks2016].[dbo].[SOH]" | Select-Object -Property SalesOrderID, OrderDate

That’s fine and I can filter out the columns I want, but I have the time 00:00:00 in the order Date Column. And I just want the date. I have googled and I am still having problems with this one. See the result set below. I need the OrderDate to just be the date. Not append 00:00:00 for the time.

SalesOrderID OrderDate
------------ ---------
43659 31/05/2011 00:00:00
43660 31/05/2011 00:00:00
43661 31/05/2011 00:00:00
43662 31/05/2011 00:00:00
43663 31/05/2011 00:00:00
43664 31/05/2011 00:00:00
43665 31/05/2011 00:00:00
43666 31/05/2011 00:00:00
43667 31/05/2011 00:00:00
43668 31/05/2011 00:00:00


texts:
I am learning database in apex oracle.
Now I am trying to create table.
I Have composite attributes, but I cannot implement on db.

  CREATE TABLE PERSON
(
    FIRSTNAME VARCHAR2(15) NOT NULL,
    LASTNAME VARCHAR2(15)  NOT NULL,
    SSN NUMBER(11) NOT NULL,
    BIRTHDATE DATE,
    AGE NUMBER(3),
    GENDER VARCHAR2(6),
);


texts: Reasons For Slowness in SQL Server 2008 R2 Express Edition

texts:
We are observing intermittent slowness in our SQL Server. We ran sp_who2, sp_who3 stored procedures for checking CPU/IO usage but did not see any spikes/abnormal values. What else could be the possible reasons for slowness. What other reports we can take from SQL Server to analyze the issue?
We are using SQL Server 2008 R2 Express.


texts: Notification when the status on sql server replication is "Not Running"

texts:
We have a lot of replications set up. Sometimes for some reason, the status of the replication changes to "Not Running/Expired Soon or Expired". Could you please let me know if there is a way we can receive alerts when the status changes on a particular replication.

Thanks


texts: PROXYSQL configuration over mysql galera replication

texts: can I have same databases connected to two different proxysql configuration?


texts: How to calculate total memory used by one table?

texts:
I have innodb_buffer_pool_size=100GB;

show table status where name='test_tb'\G

gives

    Data_length: 4686086144
Max_data_length: 0
   Index_length: 5107564544

seems index uses 5GB memory.

ANALYZE table test_tb;
SELECT
sum(stat_value) pages,
  index_name,
  sum(stat_value) * @@innodb_page_size /1024/1024 "size(MB)"
  FROM
  mysql.innodb_index_stats
  WHERE
  table_name = 'test_tb'
  AND database_name = 'test_dbs'
  AND stat_description = 'Number of pages in the index'
  GROUP BY
  index_name;

+--------+-------------------+---------------+
| pages  | index_name        | size(MB)      |
+--------+-------------------+---------------+
| 286016 | PRIMARY           | 4469.00000000 |
| 150014 | test_key1         | 2343.96875000 |
| 161727 | test_key2         | 2526.98437500 |
+--------+-------------------+---------------+

its 8GB.

So which one is accurate?


texts:
I have a table with id,user_id,timestamp and I'm trying to INSERT into the table, the user_id from a DELETE from table where id=some_number and timestamp. So what I'm trying to achieve looks like:
INSERT INTO table (user_id,timestamp)
VALUES (
    (DELETE FROM table WHERE id=some_number returning user_id),
    current_timestamp+'1 day'
)
RETURNING *;

I am getting a syntax error doing this... Can someone help me here plz...?


texts:
My machine (OSX) had a power failure and when restarted, I have one db in MongoDB that is completely unrecoverable. I am using the db per directory with dbpath and logpath specified options and was able to recover all except one of the dbs. This is the log from the unrecoverable one. Any ideas would be helpful - I have run --repair and removed the lock file with zero results.

The Error Log message in terminal:

2015-10-07T11:28:22.102+0530 W STORAGE  [initandlisten] database/Users/JineshJohn/TECSOL/zatsa2/data/db local could not be opened due to DBException 0: assertion src/mongo/db/storage/mmap_v1/extent.h:77
2015-10-07T11:28:22.108+0530 I STORAGE  [initandlisten] exception in initAndListen: 0 assertion src/mongo/db/storage/mmap_v1/extent.h:77, terminating
2015-10-07T11:28:22.108+0530 I CONTROL  [initandlisten] now exiting
2015-10-07T11:28:22.108+0530 I NETWORK  [initandlisten] shutdown: going to close listening sockets...
2015-10-07T11:28:22.108+0530 I NETWORK  [initandlisten] removing socket file: /tmp/mongodb-27017.sock
2015-10-07T11:28:22.108+0530 I NETWORK  [initandlisten] shutdown: going to flush diaglog...
2015-10-07T11:28:22.108+0530 I NETWORK  [initandlisten] shutdown: going to close sockets...
2015-10-07T11:28:22.108+0530 I STORAGE  [initandlisten] shutdown: waiting for fs preallocator...
2015-10-07T11:28:22.108+0530 I STORAGE  [initandlisten] shutdown: final commit...
2015-10-07T11:28:22.108+0530 I JOURNAL  [initandlisten] journalCleanup...
2015-10-07T11:28:22.108+0530 I JOURNAL  [initandlisten] removeJournalFiles
2015-10-07T11:28:22.108+0530 I JOURNAL  [initandlisten] Terminating durability thread ...
2015-10-07T11:28:22.213+0530 I JOURNAL  [journal writer] Journal writer thread stopped
2015-10-07T11:28:22.213+0530 I JOURNAL  [durability] Durability thread stopped
2015-10-07T11:28:22.213+0530 I STORAGE  [initandlisten] shutdown: closing all files...
2015-10-07T11:28:22.214+0530 I STORAGE  [initandlisten] closeAllFiles() finished
2015-10-07T11:28:22.214+0530 I STORAGE  [initandlisten] shutdown: removing fs lock...
2015-10-07T11:28:22.214+0530 I CONTROL  [initandlisten] dbexit:  rc: 100


texts: Cant initialize MySQL. "mysqld_safe mysqld from pid file /var/run/mariadb/mariadb.pid ended"

texts:
I am doing a fresh install of MySQL on Fedora 22.
After I run mysql_install_db I try to start the daemon using mysqld_safe but it does not work. The system says:

mysqld_safe Logging to '/var/log/mariadb/mariadb.log'.
mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
mysqld_safe mysqld from pid file /var/run/mariadb/mariadb.pid ended

How do I get this to work?

Here are the mariadb.log contents:

mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
[Note] /usr/libexec/mysqld (mysqld 10.0.23-MariaDB) starting as process 20443 ...
[Note] InnoDB: Using mutexes to ref count buffer pool pages
[Note] InnoDB: The InnoDB memory heap is disabled
[Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
[Note] InnoDB: Memory barrier is not used
[Note] InnoDB: Compressed tables use zlib 1.2.8
[Note] InnoDB: Using Linux native AIO
[Note] InnoDB: Using CPU crc32 instructions
[Note] InnoDB: Initializing buffer pool, size = 128.0M
[Note] InnoDB: Completed initialization of buffer pool
[ERROR] InnoDB: ./ibdata1 can't be opened in read-write mode
[ERROR] InnoDB: The system tablespace must be writable!
[ERROR] Plugin 'InnoDB' init function returned error.
[ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
[ERROR] mysqld: File '/var/lib/mysql/aria_log_control' not found (Errcode: 13 "Permission denied")
[ERROR] mysqld: Got error 'Can't open file' when trying to use aria control file '/var/lib/mysql/aria_log_control'
[ERROR] Plugin 'Aria' init function returned error.
[ERROR] Plugin 'Aria' registration as a STORAGE ENGINE failed.
[Note] Plugin 'FEEDBACK' is disabled.
[ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
[ERROR] Unknown/unsupported storage engine: InnoDB
[ERROR] Aborting
[Note] /usr/libexec/mysqld: Shutdown complete
mysqld_safe mysqld from pid file /var/run/mariadb/mariadb.pid ended


texts: Oracle categorizing alert log error messages

texts:
We can find oracle alert log error messages by querying in v$diag_alert_ext where message_text LIKE '%ORA-%'. I want to categorize the error messages like

OS errors
DB errors
Listener errors

Local Listener Errors
Scan Listener Errors

Is it possible to do so by querying in the v$diag_alert_ext table? What is the way to do that?


texts: Is it redundant to set your primary key as a unique key?

texts: I'm putting tables together in MySQL workbench and I'm currently ticking my primary keys to also be uniquely indexed. Is this a redundant configuration?


texts: SQL Server 2016 Database Backup and Restore

texts:
Is there any different approach to restore a SQL Database backup having memory optimized tables as we know that there's additional filegroup present for holding In-Memory data.
The restore command showing error for not able to create xtp folder for memory optimized database while performing restore operation.