texts:
I need a way to get detailed sale information for each employee in the StoreInfo table.  Each employeename in that table has it's own table with data structure mirroring the table information below.  I thought maybe a cursor would be the best bet, but it is taking roughly 25 minutes to aggregate all of this data.  Below is my sample DDL - how can I aggregate this data w/o it taking this extend period of time?

Create Table stores
(
  storeID int NOT NULL IDENTITY (1,1) PRIMARY KEY
  ,storename varchar(500)
)

Insert Into stores Values ('WS'), ('RBG'), ('CY'), ('MF')

Create Table storeinfo
(
  storeinfoID int NOT NULL IDENTITY (1,1) PRIMARY KEY
  ,storeID int FOREIGN KEY REFERENCES stores(storeID)
  ,employeename varchar(500)
  ,employeecity varchar(100)
  ,employeestate varchar(100)
)

INSERT INTO storeinfo(storeID, employeename, employeecity, employeestate) VALUES
('1', 'BobGodfrey', 'Mine', 'WV'), ('1', 'RoyBlue', 'Merk', 'SC'), ('1', 'GreenState', 'Merk', 'SC'),
('2', 'PinkSox', 'Yellow', 'CA'), ('2', 'BlueHoodie', 'Yellow', 'CA'), ('3', 'PurpleCow', 'Ridata', 'NY'),
('3', 'StinkySoles', 'Ridata', 'NY'), ('4', 'MetalJacket', 'Bells', 'MN'),   ('4', 'DimensionalData', 'Bells', 'NY')

Create Table BobGodfrey
(
  bobgodfreyID int NOT NULL IDENTITY (1,1) PRIMARY KEY
  ,storeID int FOREIGN KEY REFERENCES stores(storeID)
  ,itemsold varchar(500)
  ,datesold date
)

Insert Into BobGodfrey (storeID, itemsold, datesold) VALUES
('1', 'pants', '02/01/2015'), ('1', 'pants', '02/01/2015'), ('1', 'pants', '02/01/2015'),
('1', 'shirt', '02/01/2015'), ('1', 'pants', '02/01/2015'), ('1', 'pants', '02/01/2015')

Declare @empname varchar(500), @inlinesql varchar(max)

Create Table holdingtable
(
  employeename varchar(500)
  ,saledate date
  ,itemsold varchar(500)
  ,totalsales int
)

DECLARE db_cursor CURSOR FOR
SELECT employeename
FROM storeinfo

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @empname

WHILE @@FETCH_STATUS = 0
BEGIN

   set @inlinesql = 'Select '+@empname+' As employeename, saledate, itemsold, COUNT(itemsold) As [totalsales] '
                    +'INTO holdingtable '
                    +'FROM '+@empname+' '
   Exec(@inlinesql)

   FETCH NEXT FROM db_cursor INTO @empname
END

CLOSE db_cursor
DEALLOCATE db_cursor

Select * from holdingtable


texts: Oracle 12c: textual search over all PL/SQL code

texts: I want to do a text search over all PL/SQL functions/procedures in my DB to see which code is calling a particular function. Is there a tool in SQL Developer for that, or a meta-table under the system schema storing function code? A textual dump of the schema would help as well.


texts: Error in login in with root user in new mysql server

texts:
I've installed a MySQL server in CentOS 6.2 machine

+-------------------------+---------------------+
| Variable_name           | Value               |
+-------------------------+---------------------+
| protocol_version        | 10                  |
| version                 | 5.1.61-log          |
| version_comment         | Source distribution |
| version_compile_machine | i386                |
| version_compile_os      | redhat-linux-gnu    |
+-------------------------+---------------------+

After the installation I executed this command:

/usr/bin/mysqladmin -u root password Pa$$w0rd

Now I can only access the server using the command:

mysql -uroot -pPa$$w0rd

But I'd like to do it using:

mysql -uroot -p

and after that, inform the password.

Everytime I try that I get an error:

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)


texts:
Suppose I have a table (TABLE_1) with some fields, including ID (number), NAME (varchar2) and DATE_TIME (date). There was a need to create a index on those 3 fields to optimize queries like:

select * from TABLE_1 where ID = [SOME_VALUE] and NAME = [SOME_VALUE] and DATE_TIME is not null;

Because DATE_TIME can have a multitude of different datetime values, I decided to create the index as such:

CREATE INDEX IDX_TABLE_1 ON TABLE_1 (ID,NAME,TRUNC(DATE_TIME));

My question is: will Oracle use this index on the select query or will it ignore it and perform a full table scan on TABLE_1?

EDIT1: I forgot to mention that the ID field is a foreign key.


texts: Data files half empty in SQL Server. Is that a problem?

texts:
I have a multiple TB database and I have been doing some clean-up and dropped many tables. So now the data files are half empty. If I don't care about releasing the space to the operating system, is there any other reason to shrink the files?
I am thinking that now at least I do not need to worry about auto-growth settings, which might slow things down if I add a large table with ETL.


texts: SQL Server Standard edition attach partitioned database

texts:
I have a little problem. I'm using a SQL Server database version 11. I have to change database edition from enterprise to standard.

I overlooked fact that standard edition doesn't have partitioned function. But I moved all .mdf files to new server. It's about 200Gb of data.

I want to ask you question, if it's possible to attach database without partitioned tables. That tables i can moved over internet. It's about 40Gb, it's ok to moved that file over internet.

Thanks for answer.


texts: Azure SQL Data Migration Assistant is Stuck on "Migrate Data"

texts:
I am in the process of using the Data Migration Assistant to move a local DB to Aszure SQL.  The schema transfer went well, and 243 of the 246 tables all were able to transfer their data with no errors.

However, the transfer has been running for 13+ hours, and over the last three hours, three of the tables have not budged on their percent complete.  One is at 92.3%, one is at 93.9%, and the last is at 98.5%.

Some questions:

At this point, can I safely assume that this is a failed transfer, at least of these three tables?
I don't see a "stop" button.  If I wanted to, how would I abort this process?
If I were to abort the process, is there some way to manually finish the transfer of data from just these three tables?


texts: Trigger to UPDATE values of multiple rows after INSERT and DELETE

texts:
I have a database with two tables. You can see the schema here:

The table2 has a foreign key referring to the id of table1.

The user is going to insert/remove an item in table2 with table2.foreignkey_table1 and a value. Inside table2 there is already a set of values with table2.foreignkey_table1 equal to the one set by the user.

The trigger should update all the values when the new.value inserted by the user is lower than the ones in the tables. The action to be done is to increment the values after insertion, keeping the lower values the same.

Example:

Table2.foreignkey_table1 equal to 120 has in total 10 elements.

Each element has the following values: 1,2,3,4,5,6,7,8,9,10.

The user inserts a new value (3) in the Table2 with foreignkey_table1 equal to 120.

The trigger should compare all the values and move all the old.values from 3 to 10 adding +1. So the final list would be 1,2,3 (new value just inserted),4,5,6,7,8,9,10,11. From 4 to 11, they are the old values incremented.

The same thing should be done when the values are cancelled. For example, removing number 7, the trigger should decrement the higher numbers.

If the values is the highest in the list, nothing should be done.

Thanks for your support!


texts: Postgres: Sum from multiple joins resulting in values being more than expected

texts:
I have 3 tables: invoices, invoice_payments, invoice_credit_notes. I want to fetch all those invoices where the total paid amount is less than the grand_total amount in invoices table.

An invoice can have multiple invoice_payments as well as multiple invoice_credit_notes.

invoice_payments has amount and discount field in it.

invoice_credit_notes has amount field in it.

Here's what I came up with:

SELECT invoices.* FROM invoices
LEFT OUTER JOIN invoice_payments ON invoice_payments.invoice_id = invoices.id
LEFT OUTER JOIN invoice_credit_notes ON invoice_credit_notes.invoice_id = invoices.id
GROUP BY invoices.id
HAVING ((COALESCE(SUM(invoice_payments.amount + invoice_payments.discount), 0) + COALESCE(SUM(invoice_credit_notes.amount), 0)) < ROUND(invoices.grand_total, 0))

But there's an issue with my solution as there can be multiple invoice_payments or invoice_credit_notes the (COALESCE(SUM(invoice_payments.amount + invoice_payments.discount), 0) + COALESCE(SUM(invoice_credit_notes.amount), 0) is returning an amount that is higher than the actual paid amout. This is most probably because multiple tables are being joined.

So, how to overcome this issue?


texts: How to change "deployment target version" on SSIS 2019?

texts:
I am experiencing the following errors when using the "Transfer SQL Server Objects" task in SSIS on Visual Studio 2019:

An exception occurred in SMO
Value cannot be null. Parameter name: server

These errors have previously been described in SSIS 2017 and could apparently be solved by changing the deployment target version to 2016 as described in these links:

https://social.msdn.microsoft.com/Forums/SECURITY/en-US/6096339d-766b-45ab-9e97-e8b49812aaf5/object-transfer-error-an-exception-occurred-in-smo-value-cannot-be-null-parameter-name-server?forum=ssdt

https://stackoverflow.com/questions/48145026/why-does-ssis-transfer-object-stage-return-smo-failure

So as directed in the linked pages, I opened the "Deployment Property Pages" window and looked in "Configuration Properties" for the "General" option, but it is not present, all that is present is "Configuration" and it does not have a "Deployment Target Version" option.

Is there any way I can change the Deployment Target Version (or any other ideas about how best to resolve this error) in SSIS 2019?

I've tried Google but can't find anything relevant!


texts: How does SQL Server perfom lock when using CROSS APPLY?

texts:
The best way I think I can ask my question is by starting with an example.

Imagine these two queries :

SELECT CAR.SerialNumber, TIRE.SerialNumber
FROM dbo.tCar AS CAR
INNER JOIN dbo.tTire AS TIRE ON ( TIRE.CarID = CAR.ID)
WHERE CAR.Brand = 'Jaguar'

and

SELECT CAR.SerialNumber, TIRE.SerialNumber
FROM dbo.tCar AS CAR
CROSS APPLY (
    SELECT TIRE.SerialNumber
    FROM dbo.tTire AS TIRE
    WHERE TIRE.CarID = CAR.ID
) AS TIRE
WHERE CAR.Brand = 'Jaguar'

NOTE: tTire.CarID is indexed and tCar.Brand is indexed

These two would return the same output.

I know the second query is just foolish as it stand, but again, it just serve as an example for my question.

Now, I guess (even if I don't know the details) SQL Server will perform a clever and limited lock on the data read by the first query (JOIN).

But I'm wondering how it will perform lock for the data read by the second query (APPLY), mainly (and obviously imo) on the "tTire" data read.

In case of APPLY how do SQL Server perfom lock?

EDIT: This example is so simple it seems SQL Server manage to create the same execution plan, but my "APPLY" question should be though in terms where a RBAR effect occurs.


texts: Simplify Month Over Month % Change, 4 Months Moving Average %

texts:
invoice

 created_at | amount | status |
------------+--------+---------
 2016-06-01 |   50   |   2    |
 2016-05-30 |   150  |   2    |
 2016-04-29 |   32   |   2    |
 2016-03-28 |   24   |   2    |
 2016-01-27 |   11   |   2    |

In the results I want month, the SUM of the amount with status as 2 Grouped by Month, % Month Over Month change in Sum of amount, % Change in Current Sum of Amount over the Past 4 Months.

The result should include month even when the sum amount is 0, that's why I had to use generate_series and do a left join.

Here is the query I wrote to get the desired result.

SELECT
  month,
  amount,
  Round(100.00 * (amount - last_amount) / NullIf(last_amount, 0), 2) || ' %' AS monthly_delta,
  Round(100.00 * (amount - (Avg(amount) OVER (
    ORDER BY month DESC
    ROWS BETWEEN 1 FOLLOWING AND 4 FOLLOWING
  ))) / NullIf(Avg(amount) OVER (
    ORDER BY month DESC
    ROWS BETWEEN 1 FOLLOWING AND 4 FOLLOWING
  ), 0), 2) || ' %' AS "past 4 months moving avg delta"
FROM (
  SELECT
    Date_Trunc('month', m) AS month,
    Coalesce(Sum(invoice.amount), 0.00) AS amount,
    Lag(Sum(invoice.amount), 1) OVER (ORDER BY Date_Trunc('month', m)) AS last_amount
  FROM Generate_Series(
    Date_Trunc('month', current_date) - interval '2 year',
    Date_Trunc('month', current_date),
    '1 month'
  ) m
  LEFT JOIN invoice
    ON Date_Trunc('month', invoice.created_at) = m
    AND invoice.status = 2
  GROUP BY month
  ORDER BY month DESC
) tt;

How can I simplify this query so it's easy to understand ( mainly the 4 month average )


texts: Oracle: How to get a list of CTXSYS.CONTEXT indices?

texts:
I can get a list of indices by querying USER_OBJECTS.

How can I distinguish which of these indices are of type CXTSYS.CONTEXT?


texts:
What should be the ports for group replication if we are implementing group replication on three different MySQL servers? The my.cnf for the first server is

port=3306
loose-group_replication_local_address= "x.x.x.x:3307"
loose-group_replication_group_seeds= "x.x.x.x:3307,x.x.x.x:3307,x.x.x.x:3307"

For the second server the settings are same and I have changed the IP's in loose-group_replication_local_address accordingly, but I am facing this error when running start GROUP_REPLICATION:

  [ERROR] Plugin group_replication reported: '[GCS] The member was unable to join the group. Local port: 3307'

Does any one know the solution?


texts:
I need export all of a MySQL database users (including permissions) for a migration. There is over 5,000 users, what is the best way to do this?

Should I just dump the mysql.user table?


texts:
My Database is having huge data > 5TB, at some point I am doing vacuuming few tables on daily basis which bit hustle for the server. And I can't do purging to get some room for new tuples due to some dependencies. Is there anyway to increase the XIDWraparound limit?

I have checked in the source code related to the XID limits, Is it possible to set the value, compile and build it. Will that even work?
For example, Increasing MaxTransactionId (2^32) to something bigger than this?

Or my assumptions/expectations above are completely insane?


texts: Uninstalling Oracle Components on Production

texts:
So according to the standard I am tasked with bringing my databases in line with, no unused Oracle components should be installed on our databases. The Oracle versions are varied. From reading the Oracle documentation, it seems that components cannot be uninstalled after database creation, but rather have to not be installed in the first place during database creation. Whoever created these databases before me didn't do that.

What is the most efficient way to do this that results in the least disruption to production? My (inexperienced) gut instinct is that a parallel database would have to be created with only the used components installed, have the datafiles from the currently active production database restored on the new database, then change over to the newly populated database on production.


texts: Database design for payment feature system

texts:
I am building a system where visitors can buy modules from my website. For now I only have 3 available, but of course this number will keep growing.

In order to facilitate this in my MySQL database I am doubting between one of these two architectures, mainly concerning about performance and scalability.

I have a user table in my solution which consists of the user_id, email, password and other fields.

Approach one:
One table called "user_modules" where a column represents each module, and a boolean (1 or 0) corresponds to either they have the module or not.

+---------+----------+----------+----------+
| user_id | module_1 | Module_2 | Module_3 |
+---------+----------+----------+----------+
|       1 |        1 |        0 |        1 |
|       2 |        1 |        1 |        0 |
|       3 |        1 |        1 |        1 |
+---------+----------+----------+----------+

Approach two:
Two tables:
a modules table where all the different modules are in

+-----------+-------------+--------------------+-------+
| module_id | module_name | module_description | price |
+-----------+-------------+--------------------+-------+
|         1 | test        | test2              |     5 |
|         2 | demo        | demo               |    10 |
+-----------+-------------+--------------------+-------+

user_modules table where every row is a user_id coupled with a module_id.

+---------+-----------+
| user_id | module_id |
+---------+-----------+
|       1 |         1 |
|       1 |         2 |
|       2 |         3 |
|       2 |         1 |
|       2 |         5 |
|       3 |         1 |
+---------+-----------+

Now that I typed everything out, it seems clear to me that approach two is the better option. However, are there other suggestions or other possible improvements here?


texts: How does GRANT privileges function when recursive privileges?

texts:
I'm trying to understand how does granting privileges work on SQL.

According to what I understand, one user has privilege as long as said user has been granted privilege by someone or owns the database. And the second condition I though was true, is that a user may have a certain privilege only if someone the privilege chain connects to the owner/admin of the table.

Now, I'm using PostgreSQL to test how this works in an actual DBMS.

For example, let's say we have a table t1 and the users u1, u2, u3 and u4. Also let's assume u1 is the owner.

Now, u1 grants SELECT ON t1 TO u2 WITH GRANT OPTION
Then, u1 grants SELECT ON t1 TO u2 WITH GRANT OPTION
Then, u2 grants SELECT ON t1 TO u3 WITH GRANT OPTION
Then, u3 grants SELECT ON t1 TO u4 WITH GRANT OPTION
At last, u4 grants SELECT ON t1 TO u2 WITH GRANT OPTION

This should leave a cycle of recursion of grants between u2, u3 and u4. This means I can safely REVOKE the link between u1 and u2, and the link between u1 and u3. According to SQL the privileges from u2, u3 and u4 should be removed because they have no connection to the owner/admin of the table, but instead PostgreSQL allows this to happens and you can actually still issue SELECT on t1 even after the privilege was revoked.

So the question is, is this the expected behaviour on PostgreSQL/any RDBMS, or am I understanding wrong how SQL privileges should work. If someone could clarify this a bit for me, I would be thankful.

I was using these slides to understand. The last slide is the case I'm talking about.

EDIT:

Updated the example to recreate how does the database ends up on the state shown on the picture below.


texts:
I had a heated discussion today with some colleagues about high cpu.

We db guys are conditioned to believe that high cpu is a bad thing. I think we spend too much time worrying about cpu and not enough worrying about our real nemesis, those d#mn SANS. When did SANs become more important than a companies data? That's another rant for another day. Back to my cpu question.

I disagree. If the instance is properly tuned and the big three are properly satiated then I don't see anything wrong with a box running at 70%.

Now, PR has weighed in on our discussion.

"up to DBA to know what's right for the system, and constant high cpu is probably a bad sign. I'd be concerned with your scenario but better than constant"

My scenario was a box properly tuned running at 65% with spikes to 90%. You won't hear me say this often but I disagree with Paul on this one.

So, I'd love to hear from some of the more experienced players here.


texts: Does the order of columns in a non-clustered ColumnStore index matter?

texts: I'm reading about creating a non-clustered ColumnStore index. I noticed that when I modify an already created non-clustered ColumnStore in SQL Server Management Studio I have an option to change the order of the columns. Does the order of the columns matter?


texts:
We've finished setup of a 4-node SQL Server 2005 cluster. We are using Windows 2008 R2 as the underlying OS.

We are looking for suggestions on a set of tests we could perform to test failover of the SQL Instances?


texts: Cannot connect after Availability Group automatic failover

texts:
We have a SQL Server setup with Availability Groups. In the cluster we have four nodes, two in one datacenter and two in another datacenter.
There are four AGs on these cluster nodes, each primarily on one of the four nodes. One other node is in the same datacenter for synchronous HA and the other two for asynchronous in the other datacenter for DR.
We have one listener for each AG.
One AG seems to automatically failover at certain times due to various Network issues; however, the AG seems to failover fine. The HA secondary becomes primary, and all databases come online. However, applications seem unable to connect and we see various connection error type messages such as:

Cannot connect to SQL Server instance 'HH-SQL-D11' :
A transport-level error has occurred when receiving results from the
server. (provider: TCP Provider, error: 0 -
The semaphore timeout period has expired.) : The semaphore timeout
period has expired [121] (requires acknowledgement)

and

named pipes provider error 40 the network path was not found

Yet when we failover manually the connection is fine and works as expected. Other AGs on the cluster have automatically failed over and have worked fine. I have checked various things like the SQL Instance is up, TCP/IP and Named pipes enabled, remote connections enabled. I have asked the networks team to check things like firewalls and they say they are all ok.
I have tried to read the cluster logs but I am not sure what to look for in here and I am unsure of how to fix this issue. It's a problem as it means that HA is not operating. And if it does failover automatically, applications connecting to it will not work.


texts: MongoDB replication throttled due to Mongo using all system memory

texts:
We have a small three member replicaset running MongoDB 3.4:

Primary. Physical local server, Windows Server 2012, 64 GB RAM, 6 cores. Hosted in Scandinavia.
Secondary. Amazon EC2, Windows Server 2016, r4.2xlarge, 61 GB RAM, 8 vCPUs. Hosted in Germany.
Arbiter. Tiny cloud based Linux instance.

We have noticed that the replication speed from the primary to the secondary is throttled (capped) at just over 20 MBit/s, i.e. it doesn't go above that speed. If there is more data to replicate, it will queue up and we will have replication lag.

This is not a pure bandwidth issue; for example while throttling is taking place, we can transfer data between the two servers over FTP at far more than 20 MBit/s. Also, sometimes, notably after MongoDB restarts, the throttling stops for a couple of days, then comes back. There is nothing in the logs that describes why.

After a lot of experimentation and debugging, we have found the issue for the throttling: Over time MongoDB will consume all available memory on the primary member. Once this state is reached (typically a few days after a restart), throttling sets in. About half of the memory is the MongoDB process itself (WiredTiger cache), and the remainder is consumed by memory mapped collections. This seems to be by design, according to the MongoDB website: "Via the filesystem cache, MongoDB automatically uses all free memory that is not used by the WiredTiger cache or by other processes."

Now on to the questions:

Can anyone think of the reason why low available memory leads to replication throttling?
Is there a way in MongoDB to limit the amount of memory used for memory mapping? (We haven't found any.)
Is there a recommended way in Windows to do the same? (Feels like the wrong end to solve the problem.)

For now, we have an extremely hacky workaround: We have a small .NET script that continuously monitors the available memory, and whenever it's low, it allocates 10 GB memory (which will be taken from MongoDB), then immediately releases it. The result is that the server has 10 GB available memory, which takes around 1 day for MongoDB to fill up, during which there is no throttling. Yes, this script actually fixes the problem :)


texts:
I have a php page where user can select multiple locations like USA, UK, Finland etc

At the moment i am storing all the data in mysql table comma separated.
It is working fine but when i try to get count for individual locations i am having problem to write sql query.

I want to know what is the best way to save multiple locations in db table.

Edit:
Sample data

Location
India
UK,Finland, India
USA, NZ, AUS, Spain

Please advise.

select l.location, count(*)
from posting l join
     posting p
     on find_in_set(l.location, p.location) > 0;


texts: Store VarChar(Max) [Notes] Column on Same or Separate Table

texts:
Disclaimer

I had and idea for a table design a little while ago that made sense to me at the time.  In recent history I have realized I have a habit of "over engineering" or spending too much time trying to "over optimize" solutions.  I am assuming that this is one of those instances where I am over thinking the solution and will create extra overhead with little to no actual gain.

Question

Assume for an object (a Shipment table for example) there is some kind of NOTE varchar(MAX) data element we want to keep track of.  The NOTE column has the opportunity to have data pushed into an overflow and dramatically increase the size of the row (thus limiting the number of rows that can be saved on the page).  As I understand it, this then negatively impacts the execution time of various operations on the table as a whole.

Is there ever an instance where we should push that column into a separate stand alone ShipmentNote table instead of keeping it as a column on the Shipment table?  The theory being, if we push the NOTE column into a separate table it saves the pages on the Shipment table which allows all operations on the Shipment table to perform better.  Because the row size is smaller and now you can fit more rows onto the same page.

(See schema examples below):

The main use case where this might be a good idea is if:

The Note column is regularly over 8000 characters (which I think is when we start using the extra paging)
The Note column only returned in SELECT operations and is rarely or never used as part of a JOIN or WHERE
The remaining columns on Shipment are going to be queried independent of Note on a regular basis (IE: most of our operations, will not utilize Note, be that JOIN or WHERE conditions happening on other columns in `Shipment)

The disadvantages I am seeing (outside of this may not actual make noticeable improvements in working with the Shipment table outside of the Note column):

It now becomes impossible (or at least requires a trigger or something else) to ensure that there is always some kind of value for NOTE (IE: because it is now on a separate child table we can't ensure that NOTE is NOT NULL for every row in Shipment
Any operation utilizing NOTE will now take extra effort, because of the need to do a JOIN to make sure we are working with the correct record


texts: Trigger with multiple inserts based on JSON data

texts:
I am using sqlite and have its json1 extension available. I have the following tables (reduced to a minimal example):

-- table filled from external source,
-- with only a little possible impact on data
create table "source" (
    "id" Integer primary key,
    "json_data" Text not NULL
);

-- table to be filled using a trigger on "source"
create table "target" (
    "node" Text primary key,
    "parent" Text, -- NULL when root of a tree
    "kind" Text  -- NULL when "parent" is NULL
        check ("kind" in ('clone', 'child', 'offspring'))
) without rowid;

The "json_data" is of the following form:

{
    'id': [n0]  // single-component list
    'clone_ids': ['c2', 'c1'],  // multi-component list
    'parent_id': ['p0'],  // single-component list
    'ancestor_ids': ['a1, a2, p0']
        // single-component list, but multiple semantic components
}

This instance of "json_data" should give rise to the following tuples being inserted into "target" ( "node", "parent", "kind"):

'c2', 'c1', 'clone'
'c1', 'n0', 'clone'
'n0', 'p0', 'child'
'p0', 'a2', 'offspring'
'a2', 'a1', 'offspring'
'a1', NULL, NULL

Usually, 'clone_ids' will be absent; often 'parent_id' and 'ancestor_ids' will be absent; even 'id' could be absent. So the approach should be robust in view of this.

The 'child' kind is an ‘upgrade’ from the 'offspring' one; my feeling was that this upgrade could be done as an update at the end.

So my idea was that I should first try and get a list (as a JSON string or perhaps a temporary table) of the form

[
    ["c2", "clone"],
    ["c1", "clone"],
    ["n0", "offspring"],
    ["p0", "offspring"],
    ["a2", "offspring"],
    ["a1", null]
]

and then create the variable number of inserts from this list.

However, I'm quite inexperienced with SQL and sqlite's json1 extension, so I do not know (i) whether this is possible, and if yes (ii) how. So I would like to get responses to both questions. In case getting the latter list of pairs needs to be done using an external function (which I would do in Python), I still appreciate guidance on how to go from the list to the inserts.

EDIT:

In a comment it has been indicated that what I asked is not possible. So I would like to amend my question in the following way: Assume I have a user-defined function that produces the following JSON:

[
    ["c2", "c1", "clone"],
    ["c1", "n0", "clone"],
    ["n0", "p0", "offspring"],
    ["p0", "a2", "offspring"],
    ["a2", "a1", "offspring"],
    ["a1", null, null]
]

So, essentially the tuples I want to insert.

How would I go about inserting them?


texts: Find "Mystery processes" on a MS SQL Sever environment? (SSIS, databases, etc)

texts:
I inherited a collection of Instances/ MS SQL Databases/ SSIS scripts on a server. I am now the interim "accidental DBA."

I thought I was familiar with everything there, but apparently not. It's all completely undocumented.

I myself am not even currently the 'master admin' of the -- well ... I'm not even sure how that works. Can you group all instances on a server together to have the same master admin account? It's possible one of IT guys who knows nothing of the contents of these dbs has root access of some sort.

Let's just say -- I'm familiar with probably 80% of all processes that go on in this server. It's a collection of databases and SSIS scripts.

HOWEVER, I have noticed that some tables --- well, WHATEVER is writing to them, I have no idea. I don't have visibility to the (presumably SSIS since everything else is SSIS) scripts that are writing to them.

The few SSIS scripts I do have visibility to --- well there's a different "collection" for every Instance on this server.

Well - anyway -- there may be an IT contact that has root access/ admin access to something. The old DBA is gone.

Assuming this IT contact has top-level access to every Instance/ Database on this server, should I just access him for a high-level mapping of every instance-database and potentially access? (I'm literally the only one working with these databases). A lot of stuff I do not have access to, I'm either unaware of its existence, or its hidden.
What happens if this IT contact does not have admin access to some "pieces" or databases here? Are we screwed?
Should I request remote access to the actual Server (probably Windows Server 2012) that this database is hosted on? I'm wondering if that would help me investigate/ find where these Mystery SSIS scripts are.
How do I find out what robot-script is writing to a bunch of tables? Should I set up SQL Server Profiler? I guess it's called Extended Events now? What access/ permissions are required for this (I assume I just need trace permissions on the database where the tables are being updated)? Again, I'm well aware of the database where the updated tables are located --- there are SSIS scripts in that Instance, but none that write to these tables. Some "Mystery Process/ Script" is doing so daily.


texts:
I run a weekly load from csv files on one of my Postgressql tables (27 GB) using /copy command. During this time, the response time of all other tables in the database increases ( from 4-8 ms to 300 ms). Is there any way to run this load without affecting the DB performance?

Regards,


texts:
I am newbie in MySql, thus i have very naive question.
I have some database (for example, about hotels). All users may read information about these hotels. Admins are allowed to change this information.
My question is the following: How to block some hotel entry during its modification? In other words if some admin send a query for modification data for hotel1 (it takes time 1-10 secs) all other admins should wait with their modifications of this hotel. Users should see only old information until modification will be complete.

Million Thanks in advance!


texts: Error running Powershell Script in SQL Server Agent

texts:
I have a SQL job that runs via SQL Server Agent (Running SQL Server 2012 Enterprise). The last step of the job is to run an application located on a network share. Unfortunately I don't know the name of the folder that the application is in (the folder is the version number), so I'm using PowerShell to find it:

set-location "\\server\companydocuments\MyApp\Application Files\"
$name = Get-ChildItem | sort name -desc | select -f 1 | select name
cd $name.name
& ".\Application.exe"

If I open a PowerShell window on the SQL Server then it works fine. When I run this in SQL Server Agent I get the following error:

A job step received an error at line 1 in a PowerShell script.
The corresponding line is 'set-location "\\server\companydocuments\MyApp\Application Files\'"

I thought it might be a permissions issue so I've tried SQL Server Agent running under my credentials (it's a test server, not production) but I still get the same error. I've also tried mapping a network drive to the shared folder instead of UNC paths, but the same error.

Can anyone suggest how I can connect to this folder?


texts: unenforced constraints/possible duplication in my database design?

texts:
I am trying to store information about a school and the class timetable in a database. The outline is as follows:

'A subject has ~20 lessons, each of which needs to have it's description stored in the database. The topic of each lesson is also kept.

Each topic is also taught by a different teacher(s) - some topics are taught by multiple teachers. Each teacher only teaches a single subject and topic.'

So far I have this:

topics:
(id (PK), topic_name)

subjects:
(id (PK), subject_name)

lessons:
(id (PK), lesson_desc, subject_id (FK), topic_id (FK))

teachers:
(id (PK), teacher_name, subject_id (FK), topic_id (FK))

teachers_and_lessons:
(teacher_id (PK), lessons_id (PK))

However, my concern is that it does not enforce certain constraints, and information is perhaps being unnecessarily duplicated.

For example, there is no enforced constraint against me adding a (teacher, lesson) pair to teachers_and_lessons where the teacher's subject_id is 4 but the lesson's subject_id is 5.

Assuming the data is valid, you can also infer the the subject_id and topic_id of the lesson or the teacher by using teachers_and_lessons and either the lessons or subjects table. For example, a teacher with record (1, roger, 1, 2). If the record (1,3) exists in teachers_and_lessons, then the lesson with id = 3 must have subject_id = 1 and topic_id = 2. Note also: a lesson or teacher should be able to exist without having been assigned anything.

Does anyone know a  better way of structuring my database? Thanks


texts: Querying 500 million records in Oracle view takes forever

texts:
I really need professionals help now after wasting a week of time to solve my problem and searching on Stack Overflow. I have a view created on top of two different tables. This view has 500 million records.

view:-

CREATE VIEW VZ.SERVICE_UTILIZATION
AS (
select T0base.SNO as SNO,
    T0base.SERVICE_CALL_DATE as SERVICE_CALL_DATE,
    T1.LOOKUP_VALUE as ORIG_SYS,
    T2.LOOKUP_VALUE as DEST_SYS,
    T0base.MSG_ID as MSG_ID,
    T7.LOOKUP_VALUE as SERVICE_NAMESPACE,
    T4.LOOKUP_VALUE as OPERATION_ACCESSED,
    T0base.RESPONSE_TIME as SERVICE_PROVIDER_TIME,
    T5.LOOKUP_VALUE as SERVICE_END_POINT,
    T0base.SLA_TIME as SLA_TIME,
    T0base.SUCCESS as SUCCESS,
    T8.LOOKUP_VALUE as PERSPECTIVE,
    T0base.CREATION_DTM as CREATION_DTM,
    T3.LOOKUP_VALUE as SERVICE_TYPE,
    T0base.PROCESS_ID as PROCESS_ID,
    T0base.ACTOR as ACTOR,
    T6.LOOKUP_VALUE as CLIENT_END_POINT,
    T0base.SERVICE_PROVIDER_TIME_TYPE as SERVICE_PROVIDER_TIME_TYPE,
    T0base.PARENT_PROCESS_ID as PARENT_PROCESS_ID,
    T0base.ORIG_SYS_NO as ORIG_SYS_NO,
    T0base.DEST_SYS_NO as DEST_SYS_NO,
    T0base.SERVICE_TYPE_NO as SERVICE_TYPE_NO,
    T0base.OPERATION_NO as OPERATION_ACCESSED_NO,
    T0base.SERVICE_END_POINT_NO as SERVICE_END_POINT_NO,
    T0base.CLIENT_END_POINT_NO as CLIENT_END_POINT_NO,
    T0base.NAMESPACE_NO as NAMESPACE_NO,
    T0base.SERVICEBUSPROCESSID as SERVICEBUSPROCESSID,
    T0base.TRANSACTIONID as TRANSACTIONID,
    T0base.PARTNERMACHINE as PARTNERMACHINE,
    T0base.PARTNERCONNECTIVITYTIME as PARTNERCONNECTIVITYTIME,
    T0base.DATASIZE as DATASIZE
from t_vz_service_utilization T0base
  left join t_vz_lookup T1
    on (T0base.ORIG_SYS_NO = T1.SNO )
  left join t_vz_lookup T2
    on (T0base.DEST_SYS_NO = T2.SNO )
  left join t_vz_lookup T3
    on (T0base.SERVICE_TYPE_NO = T3.SNO )
  left join t_vz_lookup T4
    on (T0base.OPERATION_NO = T4.SNO )
  left join t_vz_lookup T5
    on (T0base.SERVICE_END_POINT_NO = T5.SNO )
  left join t_vz_lookup T6
    on (T0base.CLIENT_END_POINT_NO = T6.SNO )
  left join t_vz_lookup T7
    on (T0base.NAMESPACE_NO = T7.SNO )
  left join t_vz_lookup T8
    on (T0base.PERSPECTIVE_NO = T8.SNO )
)

Now, I'm querying for the distinct values from this view

SELECT /*+ FULL(su1) PARALLEL(a, 8) */
               DISTINCT su1.orig_sys AS orig_sys,
                        su1.dest_sys AS dest_sys,
                        su1.operation_accessed AS operation,
                        su1.service_namespace AS namespace
          FROM service_utilization su1;

This query operation takes forever to run. Also, I'm assuming that the indexes on the underlying tables doesn't work when querying on the views. I'm looking for other alternative to speed up my query performance when dealing with views having 500+ million records.

Note:- Materialized view is not an option for me.

Edit:-

Indexes on table t_vz_service_utilization are SNO,MSG_ID,ORIG_SYS_NO, compound index(ORIG_SYS_NO, DEST_SYS_NO, OPERATION_NO, NAMESPACE_NO)

Indexe on table t_vz_lookup is SNO

Explain Plan:-


texts: MySQL: Is there any possible way to get notified about mysql-server FOREIGN KEY CONSTRAINT violation during INSERT, if FOREIGN_KEY_CHECKS=0 ?

texts:
Plese don't bite me, i'm a newbie here ...
So here is the question:

Is there any possible way to get notified about mysql-server FOREIGN KEY CONSTRAINT violation during INSERT, if FOREIGN_KEY_CHECKS=0 ? Is it possible to recive this info at my php code? Something like mysql warnig ? Any info would be useful.

Thank you


texts:
I have a query that returns several rows (one to many relationship), I need to be able to obtain as a result a single row that has concatenated these values.
This is my query:

SELECT I.[ID]
      ,D.[CODEID]
      ,D.[DESCRIPTION]
      ,D.[PARENT]
      ,D.[LEVEL]
  FROM [wde].[INTERACCION] AS I
  LEFT JOIN [wde].[TIPIFICACION] AS t
  ON T.ID = I.ID
  INNER JOIN  [wde].[DICCIONARIO] AS D
  ON D.CODIGOID = T.CODIGO

Result:

And I nedd this:

Note: The number of rows returned by the query are variables per record


texts: Why does dm_os_memory_clerks have MEMORYCLERK_SQLCLR values on one NUMA node?

texts:
We are using a SQLCLR stored procedure for service broker activation, and I want to monitor the memory used by the CLR code. Looking at sys.dm_os_memory_clerks, I see that only NUMA node 1 has any pages associated with the MEMORYCLERK_SQLCLR type. The server has two 8-core CPUs and is running SQL 2014 CU6.

Is this expected? Or should I see memory used on both nodes like I do with MEMORYCLERK_SQLBUFFERPOOL?

Query:

SELECT DOMC.memory_node_id
    , DOMC.pages_kb
    , DOMC.virtual_memory_reserved_kb
    , DOMC.virtual_memory_committed_kb
FROM sys.dm_os_memory_clerks DOMC where type = 'MEMORYCLERK_SQLCLR'

Results:

memory_node_id pages_kb             virtual_memory_reserved_kb virtual_memory_committed_kb
-------------- -------------------- -------------------------- ---------------------------
0              88232                12607744                   1408652
1              0                    0                          0
64             0                    0                          0


texts: How to implement if (t1.ID=t2.ID) returned nothing do the stuff

texts:
I have two tables one table1 containing ID, NAME another table2 is only ID.

I want to have all IDs in table2 and return ID in table2 and NAME in table1 but I also want IDs in table2 which is not exist in table1 and just print ID, - - -.

How do I implement if (table1.ID=table2.ID) returned nothing do the stuff as well and print ID with null NAME column.


texts: How can I check if the keys are enabled on a MyISAM table?

texts:
I have a script, which populates 4 tables. It goes like this:

Disable the keys on all the tables
Populate the tables
Enabled the keys back.

The problem is that windows decided to reboot the machine at night while it was at the step 3.
I did repair on all the tables, but I have no idea whether the keys are enabled or not.

I do not want to enable it blindly, because it takes hours.

How do I check it?


texts: Is there a list what datatype corresponds to the system_type_id in Sys.columns. I am looking for an easy way to examine the columns we have in the database and i would like to avoid the INFORMATION_SCHEMA.COLUMNS.


texts:
I am creating a procedure in a new database. There is no trigger on any table and no procedures exist. I am getting this error:

  ERROR 1303 (2F003): Can't create a PROCEDURE from within another stored routine

What does this mean? How do I solve it?


texts: How to back up an entire database to text files

texts:
If it’s stupid and it works, it’s still stupid.

The hullabaloo about GDPR got me thinking - if you had to go into your backups and modify/delete data, how would you do it? Cracking open a .bak file sounds hard to me. But it’s pretty easy to open up a .txt file.

Could I back up my database to a text file instead, thereby making it easier to delete history out of the backups?


texts: How to merge multiple query results into one

texts:
1.

SELECT COUNT( * )
FROM tbl_shipment
WHERE  `awdno` !=  '' AND  `prtner_name` !=  ''
GROUP BY prtner_name

2.

SELECT SUM( inscan )
FROM tbl_shipment
WHERE  `awdno` !=  '' AND  `prtner_name` !=  ''
GROUP BY prtner_name

3.

SELECT SUM( is_deliver )
FROM tbl_shipment
WHERE  `awdno` !=  '' AND  `prtner_name` !=  ''
GROUP BY prtner_name

4.

SELECT SUM( is_rto )
FROM tbl_shipment
WHERE  `awdno` !=  '' AND  `prtner_name` !=  ''
GROUP BY prtner_name

How To merge above 4 query into one?

I tried below query but it gives Error
"Commands out of sync; you can't run this command now"

SELECT prtner_name, (

SELECT COUNT( * )
FROM tbl_shipment
WHERE  `awdno` !=  '' AND  `prtner_name` !=  ''
GROUP BY prtner_name
) AS total, (

SELECT SUM( inscan )
FROM tbl_shipment
WHERE  `awdno` !=  '' AND  `prtner_name` !=  ''
GROUP BY prtner_name
) AS inscan, (

SELECT SUM( is_deliver )
FROM tbl_shipment
WHERE  `awdno` !=  '' AND  `prtner_name` !=  ''
GROUP BY prtner_name
) AS deliver, (

SELECT SUM( is_rto )
FROM tbl_shipment
WHERE  `awdno` !=  '' AND  `prtner_name` !=  ''
GROUP BY prtner_name
) AS rto

FROM tbl_shipment
WHERE  `awdno` !=  '' AND  `prtner_name` !=  ''
GROUP BY prtner_name


texts: Import local data to remote server for SQL Server

texts:
I currently have a machine A with SQL Server Management Studio (SSMS), and a data file on it.
A connects to machine B's SQL Server database. I have no control of machine B.
The problem is:
I can't transfer data using neither DB restore, DB attach, nor bulk import. Because for any query like
BULK INSERT [MY_TABLE_NAME]
FROM 'F:\Data\MY_TABLE_NAME.txt' WITH (
FIRSTROW=2,
FORMATFILE='F:\Data\MY_TABLE_NAME.txt.fmt')

RESTORE DATABASE MY_TABLE_NAME
FROM DISK = 'F:\Data\MY_TABLE_NAME.bak'
WITH REPLACE

SQL Server Management Studio will try to open the file in B machine, not my machine A. (I can't transfer the file to machine B.)
Is there a way to open the local file in this situation? (Technically, I can use a Python script to insert table by table, row by row, but that would be way too inconvenient.)


texts:
My MariaDB replication stuck at 'preparing'.
This happend after an update and restart of the server yesterday. Since then, replication is not working.

Setup: 2 MariaDB servers with master-master replication. Version: 10.4 (Upgraded few weeks ago from 10.1 - replication has worked after upgrade)

Since the output of both servers for SHOW SLAVE STATUS is similar, i am posting only from my first server:

MariaDB [(none)]> show slave status\G;
*************************** 1. row ***************************
                Slave_IO_State: NULL
                   Master_Host: data2
                   Master_User: replusr
                   Master_Port: 3306
                 Connect_Retry: 60
               Master_Log_File: mariadb-bin.018865
           Read_Master_Log_Pos: 14137515
                Relay_Log_File: relay-bin.017274
                 Relay_Log_Pos: 4
         Relay_Master_Log_File: mariadb-bin.018865
              Slave_IO_Running: Preparing
             Slave_SQL_Running: Yes
               Replicate_Do_DB:
           Replicate_Ignore_DB:
            Replicate_Do_Table:
        Replicate_Ignore_Table:
       Replicate_Wild_Do_Table:
   Replicate_Wild_Ignore_Table:
                    Last_Errno: 0
                    Last_Error:
                  Skip_Counter: 0
           Exec_Master_Log_Pos: 14137515
               Relay_Log_Space: 768
               Until_Condition: None
                Until_Log_File:
                 Until_Log_Pos: 0
            Master_SSL_Allowed: No
            Master_SSL_CA_File:
            Master_SSL_CA_Path:
               Master_SSL_Cert:
             Master_SSL_Cipher:
                Master_SSL_Key:
         Seconds_Behind_Master: NULL
 Master_SSL_Verify_Server_Cert: No
                 Last_IO_Errno: 0
                 Last_IO_Error:
                Last_SQL_Errno: 0
                Last_SQL_Error:
   Replicate_Ignore_Server_Ids:
              Master_Server_Id: 2
                Master_SSL_Crl:
            Master_SSL_Crlpath:
                    Using_Gtid: No
                   Gtid_IO_Pos:
       Replicate_Do_Domain_Ids:
   Replicate_Ignore_Domain_Ids:
                 Parallel_Mode: conservative
                     SQL_Delay: 0
           SQL_Remaining_Delay: NULL
       Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
              Slave_DDL_Groups: 0
Slave_Non_Transactional_Groups: 0
    Slave_Transactional_Groups: 0
1 row in set (0.000 sec)

ERROR: No query specified

Logs aren't showing anything and binaries are exploding since they cannot be rotated because the replication won't continue.

I've never seen something like that. Any ideas?

EDIT - How to downgrade to previous version:
This seems to be a bigger problem. See: https://downloads.mariadb.org/mariadb/10.4.6/

For all the others who have the same problem, follow these steps to downgrade (I'll give an example for 10.4.6, since this was my case):
Find the version you have used before and the related source: https://downloads.mariadb.org/mariadb/10.4.6/

After that, add it to your /etc/apt/sources.list:

deb [arch=amd64,ppc64el,arm64] http://mirrors.n-ix.net/mariadb/mariadb-10.4.6/repo/ubuntu/ xenial main

Downgrade:

apt install mariadb-server=1:10.4.6+maria~xenial mariadb-server-10.4=1:10.4.6+maria~xenial mariadb-client=1:10.4.6+maria~xenial mariadb-client-10.4=1:10.4.6+maria~xenial mariadb-server-core-10.4=1:10.4.6+maria~xenial mariadb-client-core-10.4=1:10.4.6+maria~xenial mysql-common=1:10.4.6+maria~xenial mariadb-client=1:10.4.6+maria~xenial libmariadb3=1:10.4.6+maria~xenial

Hold packages from upgrade, until fix is published:

apt-mark hold mariadb-server*

The steps are also explained here: https://jira.mariadb.org/browse/MDEV-20247?focusedCommentId=132114&page=com.atlassian.jira.plugin.system.issuetabpanels%3Acomment-tabpanel#comment-132114


texts: Rotating MongoDB Replica Set Internal Auth Certificate Authority

texts:
I noticed that for replica set internal authentication all MongoDB instances must have certificates signed by the same CA. (Source)
Rotating individual certificates is sensible, but if I wanted to migrate the CA of all certificates I'm not quite sure how one would go about that.


texts: Can I connect to a SQL Server 2000 instance from SQL Server Management Studio 2012?

texts:
I would like to update my development machine to SQL Server 2012 but I still manage some (very old) SQL Server 2000 machines. Wil my 2012 SSMS be able to connect to those SQL Server 2000 machines?

SQL Server 2008 R2 works fine (what I have now).


texts: Logins aren't syncing across Availability Groups

texts:
We have 2 servers in an AlwaysOn group.

While the user accounts within each synchronized database exist on both servers, the database instance level logins only exist on one of the servers. Ie DBINSTANCE->Security->Logins are missing on one server.

Therefore when there is a failover, I get login failures on the second server (which doesn't have the corresponding instance level logins).

How do I overcome this issue? Was I supposed to set up the user account in a special way?


texts:
While taking a backup from my PostgrSQL database, it is showing this:

pg_dump: Dumping the contents of table "gtab17" failed: PQgetResult() failed.
pg_dump: Error message from server: ERROR:  invalid page header in block 9576 of relation     base/17779/758869
pg_dump: The command was: COPY public.gtab17 (jrdetid, jrmid, acid, dr, cr, narr, ageamt) TO stdout;

I think my table gtab17 is corrupt. I tried:

Vaccum Full error on this table

  INFO:  vacuuming "public.gtab17" ; ERROR:  row is too big: size 3256104, maximum size 8160

Analyze error

  INFO:  analyzing "public.gtab17" ;
  ERROR:  invalid page header in block 9576 of relation base/17779/758869

Database: PostgreSQL 9.2
OS: Windows XP SP3
Filesystem: NTFS

I have googled but dint get any solution to solve this.


texts: Enabling FileStream in existing clustered servers in SQL Server 2012

texts: I have heard that enabling FileStream in an already existing clustered environment is tough. I'm working on a consolidation project and currently analyzing the impact it will have if we migrated DBs into an existing server. FileStream is one of the factors and I wanted to get some more info or key elements to know on why it would be hard to enable FileStream in an existing clustered environment that has no FileStream enabled. I'm currently new at this and would truly appreciate any answers!


texts: Query to obtain indexes information - why does it produce duplicated values?

texts:
SELECT  OBJECT_NAME(S.OBJECT_ID)                                  AS 'TABLE NAME'
       ,I.NAME                                                    AS 'INDEX NAME'
       ,I.INDEX_ID                                                AS 'INDEX_ID'
       ,USER_UPDATES                                              AS 'TOTAL WRITES'
       ,USER_SEEKS + USER_SCANS + USER_LOOKUPS                    AS 'TOTAL READS'
       ,USER_UPDATES - ( USER_SEEKS + USER_SCANS + USER_LOOKUPS ) AS 'DIFFERENCE'
       ,SUM(PS.[USED_PAGE_COUNT]) * 8                             AS 'INDEXSIZEKB'
       ,IXS.PAGE_COUNT                                            AS 'PAGECOUNT'
       ,IXS.AVG_FRAGMENTATION_IN_PERCENT                          AS 'FRAG'

FROM       SYS.DM_DB_INDEX_USAGE_STATS     AS S
INNER JOIN SYS.INDEXES                     AS I ON S.OBJECT_ID = I.OBJECT_ID AND I.INDEX_ID = S.INDEX_ID
INNER JOIN SYS.DM_DB_PARTITION_STATS       AS PS ON PS.[OBJECT_ID] = I.[OBJECT_ID]  AND PS.[INDEX_ID] = I.[INDEX_ID]
INNER JOIN SYS.TABLES                      AS T ON T.OBJECT_ID=S.OBJECT_ID
INNER JOIN SYS.DM_DB_INDEX_PHYSICAL_STATS  (DB_ID(), NULL, NULL, NULL, NULL) AS IXS ON IXS.OBJECT_ID=T.OBJECT_ID
WHERE OBJECTPROPERTY(S.OBJECT_ID, 'ISUSERTABLE') = 1
      AND S.DATABASE_ID = DB_ID()
      AND USER_UPDATES > ( USER_SEEKS + USER_SCANS + USER_LOOKUPS )
   -- AND I.INDEX_ID > 1
GROUP BY S.OBJECT_ID ,I.NAME ,I.INDEX_ID ,S.USER_UPDATES, S.USER_SEEKS, S.USER_UPDATES, S.USER_SCANS, S.USER_LOOKUPS, IXS.PAGE_COUNT, IXS.AVG_FRAGMENTATION_IN_PERCENT
ORDER BY 'DIFFERENCE' DESC, 'TOTAL WRITES' DESC, 'TOTAL READS' ASC

I made this query to list index id, writes, reads and etc, but all values are duplicated only with pagecount and fragmentation different. what am I missing?


texts: SQL Server 2005 Full Text Catalog files got deleted

texts:
I have SQL Server 2005 database. One day we noticed the production backup is not running, and the cause was that the full text catalog was offline. Further analysis showed that the full text catalog file is missing in the folder. We checked the error logs, but could not find any reason for this deletion.

Is there any possibility in SQL Server 2005 for catalog files getting deleted automatically?

Can anyone remove this file manually via Management Studio?


texts: Person-to-Address Relationship Cardinality — One-to-One or Many-to-One (In this context)?

texts:
I have four tables:

Province (province_id, name)
City (city_id, name, province_fk)
Another Smaller subset under city say... City_sub (sub_id, name, city_fk)
Address table (address_1, address_2, sub_fk, person_fk)

Had an argument that it should be N-1, i.e. many-to-one, (actually he said N-N [many-to-many] but I limited the address for a user to be 1 only so let's change it) because there might be 2 persons living on the same house

But how would you query it to ensure that there will be no repeated data in the Address table? Address lines itself is already vulnerable. There are some people who type "street", "st.", and misspelled "stret".

Well what do you guys say? I think the saying "base your model on the real world" has limitations.

I am currently siding with my opinion which is 1-1, i.e. one-to-one, (1-N [one-to-many] if I returned to allowing persons to have multiple addresses).

The person I argued with tells me that this would have "data quality issues".


texts: Data impact on analyzing mysql table stats

texts:
I have a MySQL database that appears to be getting slower over time. After some googling, I determine that the database probably needs to be analyzed. This is because innodb_stats_on_metadata is set as OFF, and I am not aware of any regularly scheduled analysis job running.

Here are a few of my problems:

How do I know for sure this db needs to be analyzed? It seems that MySQL doesn't store when the last time "stat" was updated, and this DB didn't have logging turned on.
This DB constantly have inserts/updates ongoing. I know during the analysis, nothing can write to the DB. What will happen to those write requests? will they get queued, or they will be thrown away?
Will mysqlcheck have any chance of breaking the DB? if so, how do I recover the data? This is a production database that I can't risk breaking...

Any help would be appreciated.


texts:
Simple example: there is a table of customers.

create table Customers (
  id integer,
  constraint CustomersPK primary key (id)
)

All the other data in the database should link to a Customer, so e.g. Orders looks like this:

create table Orders (
  id integer,
  customer integer,
  constraint OrdersPK primary key (customer, id),
  constraint OrdersFKCustomers foreign key (customer) references Customers (id)
)

Suppose now there is a table linking to Orders:

create table Items (
  id integer,
  customer integer,
  order integer,
  constraint ItemsPK primary key (customer, id),
  constraint ItemsFKOrders foreign key (customer, order) references Orders (customer, id)
)

Should I add a separate foreign key from Items to Customers?

...
constraint ItemsFKCustomers foreign key (customer) references Customers (id)

A picture instead: should I add the dashed line/FK?

Edit: I have added primary key definitions to the tables. I'd like to re-iterate on the point I made above: the database is basically siloed by customers, as a correctness / security measure. Therefore, all primary keys contain the customer ID.


texts: query_cache_type on mysql galera cluster

texts:
I was wondering could I turn on the query_cache_type for mysql galera cluster of two nodes.

The scenario is :
1. Proxysql infront of two master-master galera(mysql) nodes
2. Inserts are coming through the proxysql
3. On the node2 I have a local PHP application, which is used for generating reports (many selects). There I would like to use the query cache.
As far as I know query_cache should be disabled for galera cluster if you are using loadbalancing. At this particular case the selects are made directly on one of the nodes.

What is your opinion on the matter?

P.S. Or should I look for memcached instead....


texts: DBMS_RESOURCE_MANAGER limit IOPS or MBPS

texts:
Does DBMS_RESOURCE_MANAGER have limit io bandwidth?

I want reporting group to be limited to some mbps. During peak hours our database disks are 100% loaded, because reporting groups are selecting many rows.

I want  somehow to limit io resource without canceling their selects.


texts: How to quantify or locate command timeouts in Azure SQL

texts:
I am trying to if confirm query timeout in Azure.

Our frontdoor is reporting a 503 response at 30 Seconds response, as its the SQL server default value, I am  trying to confirm if these are indeed query command timeouts.

I've searched for "Timeout" in the Logs section of the portal monitoring (not sure this how they would be represented)

Is there an method to locate query timeouts in Azure?

Answers or links with a decent resource would be highly appreciated as my searches are not proving fruitful at the moment


texts:
Does somebody here know the latest state of the development of MySQL Proxy?
Most materials I found on web are outdated.


texts: Avoiding Multiple Queries when Searching for Records Associated with a Set of Records

texts:
So, I am sure I have done something really stupid while designing this, and I'm open to schema changes if they'll really help me out.

On to the problem:

I have a custom shopping cart system (backed by a MySQL database) that includes a products table and a price_rules table that's used for computing discounts and applying promo code discounts. Some price rules don't have promo codes attached to them; some are simply "10% off of product X from March 1st through April 1st" or similar. Because a single price rule can apply to many individual products, I also have a join table called price_rule_product.

When showing a set of products (for example, on the main shop page or listing all products in a category) I'm currently running a separate query for each product to look for price rules that apply to that product. Here's what one of those queries looks like:

SELECT DISTINCT
    price_rule.*
FROM
    price_rule
    INNER JOIN price_rule_product ON
        price_rule.id = price_rule_product.price_rule_id
    INNER JOIN product ON
        product.id = price_rule_product.product_id
    WHERE product.id = 10
        AND price_rule.apply_to = 'line_items'
        AND (  price_rule.start_date IS NULL
            OR price_rule.start_date = 0
            OR price_rule.start_date <= 1366063902 )
        AND (  price_rule.end_date IS NULL
            OR price_rule.end_date = 0
            OR price_rule.end_date >= 1366063902 )
        AND (  price_rule.promo_code IS NULL
            OR price_rule.promo_code = '' )
    ORDER BY
         sort ASC

Oh SQL Gods, I pray you have some suggestions/solutions for this. It is causing some significant performance issues, and I'm just not experienced enough with SQL to figure out where to go from here.

EDIT: Here's the output of EXPLAIN SELECT both with and without DISTINCT:

WITH DISTINCT

id  select_type table               type    possible_keys   key key_len ref                         rows    Extra
1   SIMPLE          product             const   PRIMARY         PRIMARY 3   const                           1   Using index; Using temporary; Using filesort
1   SIMPLE          price_rule      ALL         PRIMARY         NULL    NULL    NULL                            14  Using where
1   SIMPLE          price_rule_product  eq_ref  PRIMARY         PRIMARY 8   craft_art.price_rule.id,const   1   Using where; Using index; Distinct

WITHOUT DISTINCT

1   SIMPLE          product             const   PRIMARY         PRIMARY 3   const                           1   Using index; Using filesort
1   SIMPLE          price_rule      ALL         PRIMARY         NULL    NULL    NULL                            14  Using where
1   SIMPLE          price_rule_product  eq_ref  PRIMARY         PRIMARY 8   craft_art.price_rule.id,const   1   Using where; Using index


texts: 1205 Lock wait timeout exceeded after switching to multi-primary topology in MySQL InnoDB Cluster

texts:
I have set up an InnoDB cluster using MySQL (version 8.0.23) Shell. I added three nodes to my cluster. At first, my topology was a single-primary topology where I had one master node and two slave nodes. The problem with this topology was that whenever a failure happened (I voluntarily switched off one of the servers), the master node was not switched to another available node. And when I switched off two nodes, the third node remained in read-only mode which means that my application was down.
So, I decided to switch to a multi-primary topology to avoid the read-only problem which could make me lose purchases and money. But now I get SQLSTATE[HY000]: General error: 1205 Lock wait timeout exceeded; almost all the time.
I have no idea what the problem is. I increased innodb_lock_wait_timeout to 900 seconds and nothing changed. I thought maybe my MySQL router is not working properly? Could it be a firewall problem? I have already allowed 6603,60330 and 66031 through my firewall. I am not sure. Any help is appreciated.


texts: What is a source table, and a target table?

texts: I've seen alot of talk about source tables, and target tables on the internet, but no definitions of what these things are...from what I can infer it seems to mean something about copying data from an old table with an old schema into a new table with a new schema...is this what it means?


texts: Troubleshooting "Can't connect ..." from MySQL slave to master

texts:
Although I can connect from slave to master via a command-line mysql client, my slave can't connect to the master as part of its replication task. SHOW SLAVE STATUS cites a "Permission denied" error:

Last_IO_Error: error connecting to master 'replicant@dbmaster:5000' - retry-time: 60  maximum-retries: 86400  message: Can't connect to MySQL server on 'dbmaster' (13 "Permission denied")

I've checked the user permissions as well as a command-line client connection. What else can I check?
The user has SELECT and REPLICATION SLAVE permissions on the master:
mysql -h dbmaster -P 5000 -u replicant "-p$PASS" --ssl -e 'SHOW GRANTS'
# +--------------------------------------------------------------------------------------------------------------------------------------------------------+
# | Grants for replicant@192.168.187.101                                                                                |
# +--------------------------------------------------------------------------------------------------------------------------------------------------------+
# | GRANT SELECT, REPLICATION SLAVE ON *.* TO `replicant`@`192.168.187.101` IDENTIFIED BY PASSWORD '[HASH-REDACTED]' REQUIRE SSL |
# +--------------------------------------------------------------------------------------------------------------------------------------------------------+

And I've set up the slave to use the same username, password, host, port, ssl:
cat <<-EOF | mysql
STOP SLAVE;
CHANGE MASTER TO MASTER_SSL=1, MASTER_DELAY=259200,
MASTER_HOST='dbmaster', MASTER_PORT=5000,
MASTER_USER='replicant', MASTER_PASSWORD='$PASS',
MASTER_LOG_FILE='mysql-bin.041117', MASTER_LOG_POS=733093730;
START SLAVE;
EOF

The server is running Mariadb 10.3.


texts: What are the disadvantages of using surrogate keys?

texts:
I am using MS SQL Server but in general database design I want to know what problems can arise when every row in a database has its auto generated surrogate key value.

I know some advantages, for example that there is no need to identify unique columns without NULLs for primary keys, and there is no need to manage composite primary keys, normal forms are easier to manage, and uniqueness is guaranteed.

I want to know, is there any good reason regarding to performance, or index structures, etc. that should make us use real world factful keys instead of surrogate ones?

Thanks.


texts: .LDF File Size will not stop growing despite Transaction Log Backup schedule

texts:
Preface this with the following: I am not a DBA. I see this is a fairly common issue, but everything points back to the transaction log backups. I've been tasked with something well outside my wheelhouse.
I set up our SQL DB (Roughly 20gb in size,) in FULL recovery mode with a nightly FULL backup that occurs every weekday at 4:30AM. This task checks integrity, reorganizes index, updates statistics, history cleanup, backup the full db, shrinks the db, and then maintenance clean up.
I then set another subplan for Transaction Logs. This backs up the Transaction Log every 3 hours during business hours (8am, 11am, 2pm, 5pm), also during weekdays. There are not that many transactions that occur really; in the years I've been here the actual DB has increased less than a gb.
The .LDF file for the server exploded over night on Saturday and with no monitoring or warning, the server is now at capacity. This had been running fine from the looks of the data usage report (and random eyeballing) for months. It looks like shortly after the backup started, at 4:36AM, the transaction log file kept growing to the point where it is sitting at 90gb, with a whopping 5MB free on the drive.
My initial thought was to move one of the older db backups off the server to alleviate space constraints; This worked for a few minutes before the .LDF file gobbled that up also.
Is the only real option I had to switch the DB to SIMPLE, shrink the transaction log, and then swap to FULL, take a backup, and resume where I left off? I attempted to do a compressed transaction log backup to the other drive on the machine and it still would not allow me to do a DBCC SHRINKFILE. It errored out saying the transaction log for the DB is full due to 'log_backup'. I attempted three transaction log backups and trying the DBCC SHRINKFILE each time.
In my testing environment the FULL->SIMPLE->FULL method seemed to work, but I am unsure why, or how this happened to begin with. Am I missing something? I realize that this method is not ideal since it can break the log chain..
Thanks!


texts: Equality query on NVARCHAR column yields multiple results in SQL Server 2012

texts:
I am in the process of moving a pet project from PostgreSQL (9.2.2) to SQL Server (2012 Standard).

I've noticed an interesting phenomenon when querying unicode words. Given the definition:

CREATE TABLE [word](
    [id] [int] IDENTITY(0,1) NOT NULL,
    [value] [nvarchar](255) NULL
 );

and the data:

insert into word (value) values (N'ῥύπῳ');
insert into word  (value) values (N'ἀπὸ');
insert into word  (value) values (N'ἀπό');
insert into word (value) values  (N'ἐπὶ');
insert into word (value) values  (N'ἐπί');
insert into word (value) values  (N'ὑπὸ');
insert into word (value) values  (N'ὑπό');
insert into word (value) values  (N'πίῃ');

insert into word  (value) values (N'λόγους');
insert into word  (value) values (N'λόγχῃ');
insert into word (value) values  (N'λόγων');
insert into word  (value) values (N'ἀλόης');

a query for a particular word will return near matches. For example:

select * from word where value = N'ἀπὸ'

returns:

id  value
102137  ῥύπῳ
102141  ἀπὸ
102142  ἀπό
102143  ἐπὶ
102144  ἐπί
102145  ὑπὸ
102146  ὑπό
102147  πίῃ

http://sqlfiddle.com/#!6/1ab66/1

However, the same pattern in PostgreSQL only returns the exact match. How can I get SQL Server to do the same?

(PostgreSQL fiddle link): http://sqlfiddle.com/#!12/c57a6/1

I have the distinct feeling that I'm missing something, but I'm not quite able to figure out what it is.

The database collation is SQL_Latin1_General_CP1_CI_AS (which is also the server's collation) on a local installation.


texts: How to make more dynamic Postgres configuration?

texts:
I am running PostgreSQL 11 on EC2 instances that can be scaled up and down. Everytime an instance is scaled his memory specs can change. If in my configuration I have for example something like this:

effective_cache_size = 400GB

in case of big instances, if I scale the machine down of course that configuration memory won't work any more blocking the db instance from starting. So a better solution would be something like:

effective_cache_size = 80%

but that won't work. So apart writing lots of different file configurations, is there a way to have a sort of "dynamic" Postgres configuration?


texts:
The select permission was denied on the object 'sysjobs', database 'msdb', schema 'dbo'.

I have already tried to grant sqlagentoperatorrole and it didn't work. How do I resolve this error?


texts: HOW to "SUM" and "JOIN" for single query of a poll results?

texts:
I have a poll database as

CREATE TABLE poll_answer
answer_id,
answer varchar(255),
poll_id int(11),
FOREIGN KEY(poll_id) REFERENCES polls(poll_id) ON DELETE CASCADE,
PRIMARY KEY(answer_id)
) ENGINE=InnoDB

CREATE TABLE poll_results
vote_id int(11),
user_id int(11),
answer_id int(11),
poll_id int(11),
FOREIGN KEY(answer_id) REFERENCES poll_answers(answer_id) ON DELETE CASCADE,
FOREIGN KEY(poll_id) REFERENCES polls(poll_id) ON DELETE CASCADE,
PRIMARY KEY(vote_id)
) ENGINE=InnoDB

How can I run a query to sum the votes for each answer to get results for the list of answers in the poll?

SELECT ... FROM poll_results ... WHERE poll_results.poll_id='x'

answer_id  |  answer  |  SUM of Votes  |  % of votes


texts: In DB2, what are the alternative ways of evaluating an index's (or several indexes) benefit?

texts: I'm trying to evaluate different approaches to analyzing the benefit of changing the set of indexes that are available. If I pick some representative queries from my workload, I can run EXPLAIN and check if the plan of each changes when I add/drop an index. However, this may be too tedious if I have, say 100 queries. I was wondering what available alternatives (tools, scripts, etc.) exist and what are their pros/cons.


texts: MySQL query to create a Matrix view using pivot

texts:
I have a table with the following fields:

p1
p2
s1
s2

where p1 -> player1, p2 -> player2, s1 -> game1, s2 -> game2
The number of players can vary from 3 - 10.
The table have the results between them.
For example let's suppose that we have 4 players:
TIM, ROB, NICK, GEORGE
and the matches between them:

    p1     p2       s1  s2
    TIM    ROB      4   0
    NICK   GEORGE   4   0
    TIM    NICK     0   4
    ROB    GEORGE   4   0
    TIM    GEORGE   4   0
    ROB    NICK     4   0

What I want to produce with the query is something like this:

           TIM   ROB   NICK   GEORGE
    TIM     X    4-0   0-4     4-0
    ROB    0-4    X    4-0     4-0
    NICK   0-4   4-0    X      4-0
    GEORGE 0-4   0-4   0-4      X

Is it possible somehow in MySQL?
Thanks in advance.


texts: mySQL 5.1 changed innodb_log_file_size getting error on restart

texts:
In order to optimise my MySQL 5.1 (running on Window 2008 WebServer). I changed a few InnoDB options in my.ini namely the innodb_buffer_pool_size and innodb_log_file_size. But now when I am trying to restart the service it won't start. In the event viewer I can see the following errors:

Unknown/unsupported table type: INNODB

I have read about deleting log files and then starting the service but I have no clue where those log files are on windows machine. Also what is the best way to recover from this trauma without loosing any table data ?


texts: How to know exactly when my Database / Log files grew?

texts:
Is there a way to see when (history / log records) of .mdf / .ldf files grew?

Ultimately, is it possible to setup alerting and know immediately when my database / log files grow?


texts: How to log value that cause unique key violation in SQL Server

texts:
I am inserting data into a table from temp table as below:

INSERT INTO TBL_PERSON (PropertyID, PERSONID, FULLNAME)
SELECT DISTINCT PropertyID, PERSONID, FULLNAME FROM #SalesContacts

The unique key constraint is defined in Tbl_Person using combination of PropertyId, PersonId and FullName columns.

When this query is executed, how can I find out which combination of values are causing unique key constraint exceptions?

I want to log these values with the exception message.


texts:
What happens to a Trigger if a table is dropped and rename and recreated with same name?

For e.g:

We have a Trigger X which is define for Table A.
We have a Trigger Y which is define for Table B.

Trigger X is owned by SchemaA
Trigger Y is owned by SchemaB

Now everyday below steps a done during non-business hrs :

Drop Table B.
Rename Table A to Table B.
Create Table A.
Drop Trigger X & Y and Re-create same Trigger X & Y.

For some reasons above Step 4 was next executed means Trigger X & Y was not dropped and recreated. And everything seemed to be fine till the next day non-business activity started

Actual problem started when we were unable to drop Table B next day.

I wanted to know does the problem occurred because Trigger was not dropped and re-created.
Here I understand, When a table is dropped corresponding Triggers are dropped.

What exactly would have happened?

As per my understanding below things have happened
1. Drop Table B. (Table B is renamed as $BIN$B) (Now what will happen to its trigger)
2. Rename Table A to Table B.(Which trigger (X or Y) is Pointing to Table B NOW)
3. Create Table A. (Does Trigger X is still pointing to Table A)

As Trigger is owned by two different Schema what would have stopped from dropping Table B?


texts: GCP Cloud SQL not supporting sysadmin access?

texts:
I am trying to migrate data from GCP cloud SQL to AWS aurora using DMS task, I used 'sqlserver' role for source database and it failed with error. When I looked at the DMS documentation it mentioned that it needs sysadmin access but google cloud sql is not supporting sysadmin access. So how to execute below commands without sysadmin? which are mandatory for setting up source in DMS.
GRANT SELECT ON FN_DBLOG TO dmstest;
GRANT VIEW SERVER STATE TO dmstest;
use msdb;
GRANT EXECUTE ON MSDB.DBO.SP_STOP_JOB TO dmstest;
GRANT EXECUTE ON MSDB.DBO.SP_START_JOB TO dmstest;
GRANT SELECT ON MSDB.DBO.BACKUPSET TO dmstest;
GRANT SELECT ON MSDB.DBO.BACKUPMEDIAFAMILY TO dmstest;
GRANT SELECT ON MSDB.DBO.BACKUPFILE TO dmstest;


texts: Error Message Saying "Invalid Object Name"

texts: I have been following a SQL course for the past few weeks and I never have had this problem: my database is connected but I get the error message saying "Invalid Object Name". Please see picture.


texts:
We have an ADP Access 2010 project, linked to a MS SQL 2012 server.

I am trying to set up a table that has a combo box to select appropriate values, which are supplied by another table.

A table under the default dbo schema reads the extended properties, and shows the appropriate combo box

When I try to add the same extended properties to a table that is under another schema, the combo box does not appear.

What do I need to do to solve for this, or am I resigned to giving up with the task, and deal with it later when we drop the ADP?

(I tried asking this on SuperUser, but got no response)

Access code that opens table that works:

Private Sub cmdAvayaQ_Click()
DoCmd.OpenTable "dbo.tbl_o_etc_Avaya_Skills", acViewNormal, acEdit
End Sub

Access code that opens table that doesn't work

Private Sub cmdNonPhone_Click()
DoCmd.OpenTable "forecasting.tbl_nonphone_queues", acViewNormal, acEdit
End Sub


texts: How to stop a PostgreSQL database with pg_ctl?

texts:
I am trying to stop the currently running database, because apparently EnterpriseDB on Mac OSX does not support Postgis. And the Postgis site tells me to use Postgres.app. But I cannot stop the currently running database with EnterpriseDB. I tried pg_ctl:

$ pg_ctl stop -D /Library/PostgreSQL/9.6/data
pg_ctl: could not open PID file "/Library/PostgreSQL/9.6/data/postmaster.pid": Permission denied
$ sudo pg_ctl stop -D /Library/PostgreSQL/9.6/data
pg_ctl: cannot be run as root
$ cd /Library/PostgreSQL/9.6
$ sudo chown -R $(whoami) .
$  pg_ctl stop -D /Library/PostgreSQL/9.6/data
pg_ctl: could not send stop signal (PID: 105): Operation not permitted

How am I supposed to do this?


texts: How Columns_update() Function Works in Trigger To Find Updated Columns Name

texts:
I am in confuse about trigger function Columns_Update().
 i Have Basic Idea about this function that it returns a varbinary and it is use to get updated Columns in Trigger
 even i have Find The code to get updated Columns Name But  have still confusion in Bit Representation.I would be very Thank Full if Some one Explain Me this Function or Give Reference

I got Following Function

CREATE FUNCTION dbo.GenColUpdated
(@Col INT, @ColTotal INT)
RETURNS INT
AS
BEGIN;

DECLARE
@ColByte INT,
@ColTotalByte INT,
@ColBit INT;
-- Calculate Byte Positions
SET @ColTotalByte = 1 + ((@ColTotal-1) /8);
SET @ColByte = 1 + ((@Col-1)/8);
SET @ColBit = @Col - ((@ColByte-1) * 8);
RETURN Power(2, @ColBit + ((@ColTotalByte-@ColByte) * 8)-1);
END;

and Use same Function in trigger Like this

set @ColUpdatedTemp=dbo.GenColUpdated(@ColCounter,@ColTotal) ;

If COLUMNS_UPDATED()& dbo.GenColUpdated(@ColCounter,@ColTotal) =@ColUpdatedTemp

I didn't understand this Condition

dbo.GenColUpdated(@ColCounter,@ColTotal) =
        @ColUpdatedTemp

it doesn't make Any sense to me .First you are assign the value and than Check it


texts: Group together similar queries in sys.dm_exec_sql_text

texts:
I'm analyzing a 2008 Instance that supports a 3rd party app.

The app will generate the SQL Code then send it to the Database as an ad-hoc query.

I'm using this query (based on a Glenn Berry script):

SELECT
    qs.creation_time
    ,qs.last_execution_time
    ,qs.execution_count
    ,qs.total_worker_time
    ,qs.total_physical_reads
    ,qs.total_logical_writes
    ,qs.total_logical_reads
    ,qs.plan_handle
    ,qt.text
    ,qt.dbid
FROM
        sys.dm_exec_query_stats AS qs WITH (NOLOCK)
        CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
WHERE
    qt.dbid >= 7
OPTION (RECOMPILE)

My problem is that I'm getting thousands of plans for very similar queries i.e.

SELECT * FROM customers WHERE name = 'bob'
SELECT * FROM customers WHERE name = 'bill'

(in reality the queries are very large and up to 3000 characters)

It's making it almost impossible to get the data into a format that's ideal for high level analysis.

Is it possible to quickly compare 2 SQL queries and see if they are pretty much the same query? I'd then pick one of the queries at random and group all the activity against that one query. (I've tried DIFFERENCE but it's very slow)

Does SQL already store a value similar to the MD5 Hash sql_handle that allows it to see if two queries are similar and consequently reuse the same plan? (If such a value exists then I'd group on that)

I don't have this problem with Stored Procedures because the same plan is being reused. It's just all the similar ad-hocs that I want to group together.


texts:
I am doing a query like this:

select
   *
from
   table1 join table2 on table1.fieldjoin = table2.fieldjoin
where
   table1.field = 'y'

I would ask is this query equivalent in terms of performance to this:

select
    *
from
  (select * from table1 where field = 'y') as t1 join table2
  on table1.fieldjoin = table2.fieldjoin

That is by explicting doing a where inside the joined table


texts: PostgreSQL 11 Windows 10 Truncates All Tables on Shutdown

texts:
i have a new Windows 10 laptop with beefed up processors, 16GB RAM and 2TB SSD storage. I have installed PostgreSQL 11.2 on this laptop, and use it to compile large datasets from various sources and run various analyses and reports against this data.

I had been running PostgreSQL 9.x on my older Windows 7 laptop for years with no real issues. But on my new laptop, it seems that every time I shut down the laptop and go back into the database, every one of the tables have been truncated. All of the tables are still there, but just plumb empty.

Is there a configuration setting that needs to be changed somewhere?


texts: Attach AdventureWorks Data to Existing Database (No Admin Role)

texts:
I've been struggling to find a solution to this.

I have been provided with a database within a server run by our IT department.  I have full write access to that dB but no other rights on the server.

I'd like to use the AdventureWorks dB to learn SSIS, but obviously can't create the database from the .mdf file.

Is there another method I can use to create the relevant table and copy the AW data directly into my existing dB?


texts: Find the Sharepoint application server based on Sharepoint Databases

texts:
I'm doing some cleanup in my SQL Server farm and I found on one of our SQL servers (2012) several databases that I believe belong to a Sharepoint server.

No one knows in the company what is the SP that is linked to it.

I'm don't have much experience with Sharepoint.

Can you please help me with some direct queries in the Sharepoint database - Where can I find a URL for the SP ? Or Where is the Sharepoint Application server name (It is not on the same box as the database server).

I want to gather information before I'll recommend to delete those databases.

Thanks in advance,

Roni.


texts: SQL Server System Versioned Table Execution Timing

texts:
I am trying to learn more about system versioned tables in SQL Server so I am running a script to perform simple DML statements on a system versioned table and capturing different times stamps that are used for inspecting the tables between each statement.
Setup
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[EXAMPLE_TABLE]') AND type in (N'U'))
ALTER TABLE [dbo].[EXAMPLE_TABLE] SET ( SYSTEM_VERSIONING = OFF  )
GO
DROP TABLE IF EXISTS [dbo].[EXAMPLE_TABLE]
GO
DROP TABLE IF EXISTS [dbo].[EXAMPLE_TABLE_HISTORY]
GO

CREATE TABLE dbo.EXAMPLE_TABLE_HISTORY (
    ID INT NOT NULL,
    -- 'data'
    ColA VARCHAR(255) NULL,
    ColB VARCHAR(255) NULL,
    ColC VARCHAR(255) NULL,
    SysStartTime DATETIME2 NOT NULL,
    SysEndTime DATETIME2 NOT NULL
);
CREATE CLUSTERED INDEX IX_EXAMPLE_TABLE_HISTORY__SysEndTime_SysStartTime
    ON dbo.EXAMPLE_TABLE_HISTORY (SysEndTime ASC, SysStartTime ASC);

CREATE TABLE dbo.EXAMPLE_TABLE (
    ID INT NOT NULL IDENTITY(104729, 1),
    CONSTRAINT PK_EXAMPLE_TABLE
        PRIMARY KEY CLUSTERED (ID),
    -- 'data'
    ColA VARCHAR(255) NULL,
    ColB VARCHAR(255) NULL,
    ColC VARCHAR(255) NULL,
    -- system versioning
    SysStartTime DATETIME2 GENERATED ALWAYS AS ROW START HIDDEN NOT NULL,
    SysEndTime DATETIME2 GENERATED ALWAYS AS ROW END HIDDEN NOT NULL,
    PERIOD FOR SYSTEM_TIME(SysStartTime, SysEndTime)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.EXAMPLE_TABLE_HISTORY));
GO

Problem
I am running into a strange situation where the results don't appear to be correct unless I add delays into the code. For example, when running the below script I would expect the table to be empty at T0, however it returns data from the first insert.
-- perform simple dml statements
DECLARE @t0 DATETIME2 = SYSUTCDATETIME();
INSERT INTO dbo.EXAMPLE_TABLE (ColA, ColB, ColC) VALUES ('a', NULL, NULL);

DECLARE @t1 DATETIME2 = SYSUTCDATETIME();
UPDATE dbo.EXAMPLE_TABLE SET ColB = 'b' WHERE ColA = 'a';
INSERT INTO dbo.EXAMPLE_TABLE (ColA, ColB, ColC) VALUES ('d', NULL, 'f');

DECLARE @t2 DATETIME2 = SYSUTCDATETIME();
DELETE dbo.EXAMPLE_TABLE WHERE ColA = 'a';

DECLARE @t3 DATETIME2 = SYSUTCDATETIME();

-- inspect tables at specific points in time
SELECT * FROM dbo.EXAMPLE_TABLE FOR SYSTEM_TIME AS OF @t0;
SELECT * FROM dbo.EXAMPLE_TABLE FOR SYSTEM_TIME AS OF @t1;
SELECT * FROM dbo.EXAMPLE_TABLE FOR SYSTEM_TIME AS OF @t2;
SELECT * FROM dbo.EXAMPLE_TABLE FOR SYSTEM_TIME AS OF @t3;
SELECT * FROM dbo.EXAMPLE_TABLE;

'FIX?'
If I add in delays everything seems to work, but I am want to understand if the delays are needed or if they are 'working' accidentaly.
-- perform simple dml statements
DECLARE @t0 DATETIME2 = SYSUTCDATETIME();
WAITFOR DELAY '00:00:00.100';
INSERT INTO dbo.EXAMPLE_TABLE (ColA, ColB, ColC) VALUES ('a', NULL, NULL);

DECLARE @t1 DATETIME2 = SYSUTCDATETIME();
WAITFOR DELAY '00:00:00.100';
UPDATE dbo.EXAMPLE_TABLE SET ColB = 'b' WHERE ColA = 'a';
INSERT INTO dbo.EXAMPLE_TABLE (ColA, ColB, ColC) VALUES ('d', NULL, 'f');

DECLARE @t2 DATETIME2 = SYSUTCDATETIME();
WAITFOR DELAY '00:00:00.100';
DELETE dbo.EXAMPLE_TABLE WHERE ColA = 'a';

DECLARE @t3 DATETIME2 = SYSUTCDATETIME();
WAITFOR DELAY '00:00:00.100';

-- inspect tables at specific points in time
SELECT * FROM dbo.EXAMPLE_TABLE FOR SYSTEM_TIME AS OF @t0;
SELECT * FROM dbo.EXAMPLE_TABLE FOR SYSTEM_TIME AS OF @t1;
SELECT * FROM dbo.EXAMPLE_TABLE FOR SYSTEM_TIME AS OF @t2;
SELECT * FROM dbo.EXAMPLE_TABLE FOR SYSTEM_TIME AS OF @t3;
SELECT * FROM dbo.EXAMPLE_TABLE;

At this point my best guess is SQL Server captures the time at the beginning of the DML statment and there is simply not a long enough delay at the precision of a datetime2 to distinguish between the time captured and the next statement.
Is this assumption correct? Any explanation or documentation would be helpful.
All testing was performed with SQL Server 2017.


texts: SQL Server options that influence log buffer flushing or commits-per-second performance?

texts:
I am investigating two SQL Server 2014 instances, both running identical software, both running identical hardware (VMs), both loaded with the same test database, but one with hugely greater performance on a microbenchmark.
The benchmark essentially runs the following in a loop:
BEGIN TRANSACTION
    INSERT INTO [tablename] ([data])
    VALUES (CONVERT(NVARCHAR(2000), NEWID()))
COMMIT

One server can achieve approximately 200 transactions per second. The other, approximately 2000 transactions per second.
Since this transaction is doing so little, I hypothesized that the bottleneck could be disk I/O in flushing the log buffer. I thought the DELAYED_DURABILITY option was sure to explain the difference. But alas, it was set to Disabled on both servers. An audit of other options has turned up no other apparent configuration differences.
So my questions are:

Is there any other way that transaction durability could be delayed when that option is set to Disabled, e.g. a master override config file somewhere?
Is there any other way that log buffer flushing could be influenced?
Are there any other settings I should look into that might explain the wide variance in performance that I am seeing?

N.B. I'm aware that this is a microbenchmark and is not representative of the application workflow - however, real-world testing has shown a performance difference too, and this transaction loop seems to be the minimum reproducible example that shows a difference.
Additional details
Looking at the wait stats, WRITELOG is top of the list.


texts: Cannot execute job that runs a shell script

texts:
I have the following pl/sql script that should execute the shell script script.sh. When I runt the code below In SQLDeveloper I get:

    Error report -
ORA-27370: job slave failed to launch a job of type EXECUTABLE
ORA-27300: OS system dependent operation:accessing login executable failed with status: 2
ORA-27301: OS failure message: No such file or directory
ORA-27302: failure occurred at: sjseccel 1
ORA-06512: at "SYS.DBMS_ISCHED", line 185
ORA-06512: at "SYS.DBMS_SCHEDULER", line 486
ORA-06512: at line 20
27370. 00000 -  "job slave failed to launch a job of type EXECUTABLE"
*Cause:    The scheduler ran into an error when the job slave tried to start
           a job of type EXECUTABLE. The rest of the error stack will provide
           more detailed information on what the exact problem was.
*Action:   Correct the problem specified in the error stack and reschedule
           the job.

The file exists and has the correct permissions on my system. Is something that I am doing wrong? Also, the provided credentials seem to be the correct ones.

An here is the code:

set serveroutput on;

BEGIN
    DBMS_SCHEDULER.create_credential(
        credential_name => 'user_cred',
        username        => 'user',
        password        => 'pass');
END;
/

begin
      DBMS_SCHEDULER.CREATE_JOB (
        job_name           => 'test_prog',
         job_type           => 'EXECUTABLE',
        job_action         => '/home/user/Desktop/foo/script.sh',
         enabled            => TRUE,
         credential_name => 'user_cred'
      );
    dbms_scheduler.run_job('test_prog');
end;
/


texts: should we keep the log file or the data file on the fastest drive?

texts:
SSD = solid state drive

We have a specific question about tempDB performance on server CRM01 (Marketing CRM server - running large queries):

On CRM01,  tempDB data and log files reside on the solid state drive. The SSD total size is 400Gb.
Tempdb data files take up 300 Gb and the log around 100 Gb and may need increasing but there is no space left.

The question is theoretical. We do understand that:
-    if tempdb log files grow too much we should split our processes so that data should be processed in smaller chunks, or we should look at using possibly temporary physical tables instead of tempdb.

both data and log tempdb files should remain on the SSD to optimise performance

However, if we really had to take the decision of moving some of the tempdb files out of the SSD due to space constraint, which ones would you choose to move to a standard fast drive: the  data or the log files?

what is it was not the tempDB, it was a normal DB.
of course then I could partition tables, and create different filegroups, but still would I keep the data or the log in the fastest disk?

lets say the utilization is 50% read and 50% updates and inserts.

@Moderator, I agree the second part of the question is not objective, but it provides insights and ideas.


texts:
My table:

CREATE TABLE items (
    id BIGINT PRIMARY KEY NOT NULL,
    name VARCHAR,
    images json
);

images format:

[
  {
    "id": "owner",
    "full": "<url>",
    "thumb": "<url>"
  },
  {
    "id": "note_0",
    "full": "<url>",
    "thumb": "<url>"
  },
  {
    "id": "note_1",
    "full": "<url>",
    "thumb": "<url>"
  },
  {
    "id": "note_2",
    "full": "<url>",
    "thumb": "<url>"
  }
]

I need something like this:

UPDATE items SET images = delete(images, 'note_1');


texts: Partitioning a table with LOB columns in Oracle 11g

texts:
we have a fairly small table (~25k rows) that is being heavily written to and most of the rows go away quickly (it's part of a scheduling application which stores information about the jobs in that table).

It's a third party application which does a huge amount of small transactions which result in a high contention on that table.

This is all running on a two node RAC cluser using Oracle 11gR1

We can see in the AWR reports that most of the time is spent waiting for the cluster synchronization.

We would like to (hash) partition that table in order to distribute the contention on the blocks being modified.

However the table also contains a BLOB column which is used by the application to store the state of the job. We also see a high volume of reads and writes to the LOB segment for that column.

My question is: when we partition the table, is there a way to create multiple LOB segments as well? So that there is a one-to-one relation between table partitions (i.e. segments) and LOB segments?

I didn't find anything in the manual, but as the documentation for CREATE TABLE is sooo huge I wouldn't be surprised if I overlooked something.


texts: Oracle Wallet Error in Red Hat High Availability Resource

texts:
Environment: Running Red Hat 7.2, using Pacemaker 1.1.13-10.el7_2.2 and Corosync 2.3.4-7.el7_2.1 to implement cluster failover of an Oracle 12c Enterprise Edition Release 12.1.0.2.0 Database.

Background: When I start the database outside the cluster, everything works correctly and outside queries from other hosts can execute.  The database has an encryption wallet which is set to auto-open, and which worked seamlessly before this problem cropped up.

Problem: When I add the oracle instance to the High Availability resource group, the instance starts up without any errors (that I can see).  However, when outside connections are attempted from other hosts, the database responds

ERROR at line 1:
ORA-28365: wallet is not open

If I connect to the database on the host server and execute any queries, no errors are reported at the console, and the queries from remote hosts suddenly start to succeed again.  The cluster then works fine until the resource fails over to another node, then the problem reappears with the same symptoms / temporary solution.  The rejected queries cause trace files to be generated for the database SID containing:

kcbtse_get_tbskey: decrypting encrypted key for pdb 0 tablespace 6 without opening the wallet
kcbtse_get_tbskey: wallet is not opened (ts 0/6)
kcbtse_encdec_tbsblk: DIAG DUMP tsn 0/6 rdba 25165987, afn 6, mode 4

Has anyone seen this sort of issue before?  What is missing from the HA environment that is present outside of HA?


texts:
I need to insert multiple records into a table. The number of records depend on the result of another query. For example:I want to insert booking detail depend on number of room customer choose. If customer choose 2 room, inserting should run two times. How can I do that?

$fetch = mysql_query("SELECT r.room_id FROM room AS r WHERE
(r.room_name  LIKE '$roomtype') AND r.room_id NOT IN (SELECT b.room_id
FROM bookingdetail AS b WHERE b.datearr =  '$datearrive' OR b.datedep =
'$datedepart LIMIT '$roomno')")or die(mysql_error());

$sql="INSERT INTO bookingdetail (booking_id, room_id, datearr, datedep)
VALUES ('$bookingid', '$room_id', '$datearrive', '$datedepart')";


texts: How to reverse-engineer SQL statements issued by a legacy application

texts:
I am re-writing the question below to provide more details.

We have a legacy application and are now developing it using a new technology. The database is SQL Server 2014. We know 90% of the tables that get updated when an operation is performed on the UI. However, we are not sure if we have covered all the tables.

The customer wants us to run a scan of the database on the old system when an operation is performed on the front end and then do the same with the new application. HE would like to make sure that we are touching the same tables in the old and new application.

So, I would like to understand if there is some query I can run that will give the list of tables that were updated when I perform an operation on the front end. All the tables have last update date and user who performed the update.


texts:
I have varchar column, its name is column1 and has records like 111232435-0 and 1123443530. The last digit is not always 0; it can be 0-9.

Some of them are digits with (-), other are digits. I want to change digits to digits with(-).

For example: 1123443530 -> change to -> 112344353-0

Pseudo-code:

if record just be digit just update in column3
else
the last digit separate by (-)
and then record update in column3

In SQL Server 2012 and put this new record and records with (-) in new column.

What should I do?


texts: SQL Server Agent cannot log on to "MyServer"

texts:
I'm struggling to get our SQL Server Agent to run a maintenance plan, which used to work in the past. (This is on SQL Server 2008 R2.)

The original problem was that the SQL Server Agent would not start, because it tried to connect to, let's call it "MyServer," which resulted in what appeared to be a network name resolution error. We then changed the alias from "MyServer" to "." (SSMS > SQL Server Agent > right-click Properties > Connection > Alias local host server). This fixed it and we can now start the agent.

The second and current problem is that when I execute a maintenance plan, then the log for the SQL Agent (not the log for the maintenance plan) shows a similar error, that it cannot log on to the server "MyServer." (See below for the exact error.) I updated the maintenance plan's connection to use the IP directly (in a desperate attempt), and I checked the job's user password, but it did not change anything. I think it fails before it gets to this point.

Why does it still want to use "MyServer?" Is there another place where I should set the alias, or clear a wrong alias?

Side Notes:

This is a test server, so there is not much control over who changed what, which is probably how things broke in the first place.

Current error: [382] Logon to server 'MyServer' failed (ConnLogJobHistory)

Error for the original problem (now fixed): [000] Unable to connect to server 'MyServer'; SQLServerAgent cannot start

I already tried a server rename from "MyServer" to "MyServer.abc.def" (where "abc.def" is the DNS suffix), but the error message still shows a failure to log on to "MyServer" (without the suffix). The plan's data source is ".", so no server name mix-up there. I think the problem occurs before this point.


texts:
We have MySQL 5.7 cluster with 4 nodes. We get a problem with one node in this cluster often.
Our applications write data to node1 and node1 replicates data to other 3 nodes.
In node1, I keep getting this in /etc/log/mysqld.log
2020-08-17T04:03:07.709440Z 21269681 [Note] Aborted connection 21269681 to db: 'pro_sample' user: 'user_pro' host: 'node2.example.com' (Got an error reading communication packets)
2020-08-17T04:06:19.152707Z 21271865 [Note] Aborted connection 21271865 to db: 'pro_sample' user: 'user_pro' host: 'node2.example.com' (Got an error reading communication packets)

in my node1 MySQL database, I get many sleep connections.
select * from INFORMATION_SCHEMA.PROCESSLIST where db ='pro_sample';
| 21270100 | user_pro | node2.example.com:45134 | pro_sample | Sleep   |  651 |              | NULL                                                                                |
| 21270089 | user_pro | node2.example.com:45102 | pro_sample | Sleep   |  668 |              | NULL

and node2 is the server often goes offline and causing replica issue.
What do we missing here?