texts: Low CPU Utilization but High Signal Waits

texts:
I have a server with 16 CPUs that is configured with a max degree of parallelism of 8 and a max worker threads setting of zero.

For a given hour, my signal waits were 20% yet my OS CPU utilization during that time never went over 25%. Can someone explain why my signal waits were so high?

My vendor has a best in class scoring system that expects us to be at 10% or less signal waits, or we get dinged. How can I go about fixing this (without adding additional CPUs)?

We do not have more than 8 CPUs per NUMA node, so Trace Flag 8048 does not apply.
The largest instance waits are CXPACKET (70%),then PREEMPTIVE_OS_PIPEOPS (20%)
cost threshold for parallelism is set to 50. Should I raise it? To what?
This is a physical machine (not a VM), dedicated to SQL Server.
I am using a monitoring tool to identify the most frequently run queries and procedures. Do I want to look at high CPU, high I/O, or high duration? Normally our app is I/O intensive so I tune high I/O. But since the issue is signal waits, do I need to look at high CPU?
I was hoping to avoid Max Vernon's recommendation to lower MAXDOP to 4, because the app does some warehouse style queries that need the extra threads.


texts:
Goal:
Display detailed information if database A use database B:s table and its column. if yes, the list will display what is the name of the database B:s table and column that is being used in database A.

Purpose:
Database A has about 200 View and 400 tables and database B has about 154 View and 165 tables and I don't wanna review each table and view in order to locate what table and column it use from database B. The main purpose is to reduce the time.

Problem:
I do not know how to do it?

Information:
*I'm using SQL server 2008 R2


texts: Difference between sqlserver.nt_username & sqlserver.username

texts:
I am just learning about SQL Extended Events.

I have a small event and for actions I have selected both sqlserver.nt_username & sqlserver.username

When I test connect with a NT account the event captures the NT account in both fields. When I test connect with a SQL Server Authentication account, only the sqlserver.username field captures the account, I can understand why the NT field does not capture non-NT accounts.

It looks like sqlserver.username captures everything, while sqlserver.nt_username is more selective.

Other then what I have mentioned are there any differences between these two?  Is there any case when sqlserver.nt_username might capture something NOT captured by sqlserver.username?

In this case I am using the event sqlserver.sql_batch_starting but my question is for any event.  With SQL versions 2012 to 2017.

Note: Both of these fields are actions, so grabbing both increases overhead. While it is probably not significant overhead, I am trying to stay as lite as possible.


texts: MySQL replication and master users on slave server

texts:
Do the users initiating inserts on the master server also have to be on the slave with at least as many permissions for the replicated queries to be correctly executed? My understanding is that you only have to have a replication user with enough permissions but I still have a doubt.

Moreover, let's say that the slave server has the same users as the master server, in addition to a replication-dedicated user, but with less permissions, non writes for instance. Would there be any kind of conflicts resulting in the replicated queries not being executed?

The idea is to have a read-only slave with only SELECT perm users to access to data, not necessarily the same as the master's users, plus a replication-dedicated user on the slave with REPLICATION SLAVE perm. Would all replicated queries then supposed to be executed normally? That's my actual wondering.


texts: Index for column with two possible value

texts:
I use PostgreSQL 9.4.

I have a database where a table have index in column which can contains  two possible value only(gender: male/female). When I execute a query with condition on this column index was not use.
It's bad idea use index in such column?

If index useless in this case, will I have update for this index when add new record. In other words: Will I have increase insert speed if I remove this index?

SQL for index:

CREATE INDEX index_participations_on_gender
ON participations
USING btree
(gender COLLATE pg_catalog."default");

My query:

SELECT
  participations.last_name,
  participations.first_name,
  participations.birthday,
  participations."number",
  participations.chip_id,
  participations.gender
FROM
  public.participations
WHERE
  participations.gender = 'male';

Rows in the table: 64982

Thanks.


texts:
I'm using an sharded-cluster(Mongo version 2.4.1) with a java application. After a specific time (10 minutes or more) I got the following exception:

com.mongodb.MongoException$Network: Read operation to server cs-mongo-mongo-west-t:3306 failed on database mdb
    at com.mongodb.DBTCPConnector.innerCall(DBTCPConnector.java:253)
    at com.mongodb.DBTCPConnector.call(DBTCPConnector.java:216)
    at com.mongodb.DBApiLayer$MyCollection.__find(DBApiLayer.java:288)
    at com.mongodb.DBApiLayer$MyCollection.__find(DBApiLayer.java:273)
    at com.mongodb.DBCursor._check(DBCursor.java:368)
    at com.mongodb.DBCursor._hasNext(DBCursor.java:459)
    at com.mongodb.DBCursor._fill(DBCursor.java:518)
    at com.mongodb.DBCursor.toArray(DBCursor.java:553)
    at com.mongodb.DBCursor.toArray(DBCursor.java:542)
    at com.pribas.messagesdb.impl.GetMessagesDBHandlerImpl.handleRequest(GetMessagesDBHandlerImpl.java:86)
    at com.pribas.messagesdb.impl.MessagesDBHandlerImpl.handleRequest(MessagesDBHandlerImpl.java:37)
    at com.pribas.messagesdb.service.MessageServlet.processRequest(MessageServlet.java:161)
    at com.pribas.messagesdb.service.MessageServlet.doPost(MessageServlet.java:86)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:647)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:728)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:305)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:222)
    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:123)
    at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:472)
    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:171)
    at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:99)
    at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:953)
    at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118)
    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:408)
    at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1008)
    at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:589)
    at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:310)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
    at java.lang.Thread.run(Unknown Source)

Is there a way to disable this timeout of the cursor within the sharded-cluster or within the config-file?
At the moment this issue can only be solved by restarting the application so it establishes a new connection.

Thank you very much,
Daniel


texts: Is "CREATE INDEX` in MySQL a Linear Operation?

texts:
What I mean is the following:

If creating an index on a table with n rows takes t time. Will creating an index on the same table with 1000*ntake approximately 1000*t time.

What I'm trying to achieve is to to estimate the time it takes to create the index on the production database by creating the same index on the much smaller test database.


texts: Schema Dump Without User Mapping Passwords

texts:
Is it possible to do a pg_dump --schema-only of the database schema but not include any username/password combinations that are in user mappings?

For example, I would like items such as the following to not be put in the dump:

CREATE USER MAPPING FOR greg SERVER db_name OPTIONS (
    password 'supersecret',
    username 'bobs_uncle'
);


texts:
I'm starting to make heavy use of CTEs for my postgres based application, and am wondering if selecting from a previous CTE statement guarantees any ordering?

E.g. is the order of rows for this query guaranteed?

WITH latest_logs AS (
    SELECT * FROM logs ORDER BY created DESC LIMIT 100
)
SELECT * FROM latest_logs;

What about this one?

WITH latest_logs AS (
    SELECT * FROM logs ORDER BY created DESC LIMIT 100
)
SELECT * FROM latest_logs WHERE my_field = 5;

Or do I always need to re-apply the ORDER BY created DESC every time I select from latest_logs?

Thanks :)


texts: Performance impact of IF @@TRANCOUNT > 0 COMMIT TRAN

texts:
I'm dealing with some performance issues on SQL Server 2012. The client application indicates that the SQL time to load a page is over 2.5 seconds with a total loading time of more than 3 seconds. For this page it executes more than a few 1000 queries. In the trace I can see that it does the following a lot.

IF @@TRANCOUNT > 0 COMMIT TRAN (see screenshot of default tace).

What is the impact of this constant trancount check on the database? Can this be the cause of the issue? The queries itself take a very short time (microseconds) and other indicators show that the SQL server is healthy so I'm looking at overhead caused by these type of things. I've eliminated the network too.


texts: How do you drop a corrupted INNODB database in MariaDB?

texts:
If you try to access a table in a database of type INNODB and MariaDB tells you it isn't here, you have a corrupted database.

(I am posting this question because all of the existing questions have been "closed" by various moderators and NONE of them have a real answer.  They discuss the complexities of INNODB, and ultimately that you have to rebuild the entire database server through a torturous process.)


texts: Is it good practice to have relationship between the tenant table and all other tables in a multi tenant application?

texts:
I am developing a multi tenant SaaS that uses Azure SQL for data storage. I make use of sp_set_session_context to make sure that any query only touches records relevant to the current tenant.

I occationally get requests to delete a tenant from the database. That means the record in the Tenants table and all other records in other tables with the same Tenant_Id. Today I do this with one DELETE statement per table.

Is it a good idea to create a relationship between Tenant and all other tables? This way I could use cascade delete to delete a tenant by simply removing a single record from the tenants table.

I am concerned about the more difficult recovery in the case of a mistakenly deleted tenant. I also want to be confident that there will be no serious performance issues with such a schema.


texts: Does TokuDB need any regular cleanup/maintenance?

texts: PostgreSQL has vacuum and InnoDB has purge. Does TokuDB have any required maintenance/cleanup operations? I couldn't find anything in the online docs: https://www.percona.com/software/mysql-database/percona-tokudb


texts: MongoDB stale replication, odd timestamp after data flush

texts:
Right before my secondary goes stale, it first has this written in the log:

2017-04-25T06:48:02.991+0000 I STORAGE  [DataFileSync] flushing mmaps took 13530ms  for 3150 files

Then it gives me a odd stale message, note the "b" in the oldest available.

2017-04-25T06:50:03.815+0000 I REPL     [ReplicationExecutor] could not find member to sync from
2017-04-25T06:50:03.815+0000 E REPL     [rsBackgroundSync] too stale to catch up -- entering maintenance mode
2017-04-25T06:50:03.815+0000 I REPL     [rsBackgroundSync] our last optime : (term: 6, timestamp: Apr 25 06:32:08:333)
2017-04-25T06:50:03.815+0000 I REPL     [rsBackgroundSync] oldest available is (term: 6, timestamp: Apr 25 06:32:08:3b1)
2017-04-25T06:50:03.815+0000 I REPL     [rsBackgroundSync] See http://dochub.mongodb.org/core/resyncingaverystalereplicasetmember

I am running mongodb 3.2.8. Is that b expected to be in there? Is this expected?


texts: High CPU usage on SQL server - Slow queries

texts:
Our MS SQL Server is using about 95% of the CPU-power.

After a server (hardware) restart, or a SQL-Service restart, the usage is 0% and slowly increases over the course of 1-3 days. Depending on how much it is used.

When it's over 80%, every query is extremely slow.

Our website is dealing with alot of big queries, so some of them takes 45-60 seconds.
After a restart (CPU usage less than 80%), it takes 11-20 seconds for the same Query.

How can I fix this? I've read online that affinity masks can adjust the CPU usage, but the Affinity settings are disabled. I cannot change them. Is this because I only have 1 processor?

There are plenty of tricks to do with the queries themselves, but our websites and services are quite big, and there is simply too much to change.

Most of them are already pretty well optimized anyway.

I cannot keep restarting the SQL-Service, even though it only takes 2 seconds, because we have an alarm service that allows people to call in and record a message, a selected group will then be called and hear the recorded message.

This system is used by hundreds Search and Rescue teams, and if the SQL-Service restarts during an alarm, it will terminate and the person that called it in will not be notified.

I have searched all over the place, but found nothing except for stuff about "Affinity Masks", which I cannot change.

There must be a way to clear out the CPU cache, without terminating current queries... right?

SQL: Microsoft SQL Server 11.0.2100.60
OS: Windows Server 2012 x64
Processor: 2.30 GHz
RAM: 4.00 GB


texts: Powershell Restore Azure SQL Database to local with Restore-Database

texts:
I have written a powershell script that uses Start-AzureSqlDatabaseExport & Get-AzureStorageBlob to export my Azure DB to a bacpac and download it locally, the next step is to restore the bacpac to my local SQL DB using Restore-Database, but below fails with "the media family on device is incorrectly formatted".

Restore-Database -dbName "MyDB" -backupFile "C:\MyAzureBackup.bacpac"

Is there a way to get this working with Restore-Database, or is there an alternative command that will do this?


texts: What is cool-down time in Alibaba Cloud Auto Scaling?

texts: I am using Auto scaling on my Alibaba Cloud Elastic Compute Service Instances. I am not aware of what the cool-down time is and the implications?


texts:
I am trying to write a Stored Procedure which updates a quantity data column in one of my tables, and then takes a field set for random notes and concatenates the update information.

This is what I have for the Procedure thus far

CREATE PROCEDURE UpdatePartQuantity(
    @OrderNum VARCHAR(20),
    @PartNum  VARCHAR(100),
    @Quantity INT
) AS
BEGIN
    DECLARE @OrderID int, @PartID int

    IF(EXISTS(SELECT order_id FROM purchase_orders WHERE order_number = @OrderNum))
    BEGIN
        SELECT @OrderID = order_id
          FROM purchase_orders
         WHERE order_number = @OrderNum
    END
    ELSE
        RETURN 1

    IF(EXISTS(SELECT part_id FROM parts WHERE part_number = @PartNum))
    BEGIN
        SELECT @PartID = part_id
          FROM parts
         WHERE part_number = @PartNum
    END
    ELSE
        RETURN 1

    IF(EXISTS(SELECT part_id, order_id FROM order_parts WHERE order_id = @OrderID AND part_id = @PartID))
    BEGIN
        UPDATE order_parts
        SET ordered = @Quantity, notes = CONCAT(notes, 'string goes here')
        WHERE order_id = @OrderID AND part_id = @PartID
        RETURN 0
    END
    ELSE
        EXEC AddPartToOrder @OrderNum, @PartNum, @Quantity
END

So here is how the procedure is suppose to run:

Check if the passed order number exists within the database, if it does get the order id associated with it.
Check if the passed part number exists within the database, if it does get the part id associated with it.
Check if an entry has even been made for this part in this order.  If it hasn't create the entry by called the AddPartToOrder Procedure
If the entry does exists, change the ordered value to the value of @Quantity and concatenate onto the notes column: "Quantity updated: [Current Date]Old-[Old Value],New-@Quantity"
If any If statements return False, exit Procedure returning 1

My issue is I am not sure how to set up my CONCAT statement.  How would I put a GETDATE call inside of it?  How would I reference the value of @Quantity?  I am not exactly sure how to approach this.


texts: Deadlock Priority High Chosen as deadlock victim

texts:
I have SQL Server 2016 SP2 (13.0.5237.0). Here is a deadlock graph I noticed recently in my system. The process with high deadlock priority was chosen as a victim (probably because of high log usage compared to the other process). But that shouldn't have happened. Is this a defect introduced in SQL? Is there a way to prevent a process with high deadlock priority from becoming a victim?
Here is the deadlock xml:

<deadlock>
 <victim-list>
  <victimProcess id="process1d3ea515848" />
 </victim-list>
 <process-list>
  <process id="process1d3ea515848" taskpriority="10" logused="5800" waitresource="OBJECT: 9:290100074:0 " waittime="4711" ownerId="359850034" transactionname="user_transaction" lasttranstarted="2019-07-31T08:12:25.267" XDES="0x1d2f62d1840" lockMode="Sch-M" schedulerid="3" kpid="10320" status="suspended" spid="52" sbid="0" ecid="0" priority="5" trancount="4" lastbatchstarted="2019-07-31T08:13:30.990" lastbatchcompleted="2019-07-31T08:13:29.427" lastattention="1900-01-01T00:00:00.427" clientapp=".Net SqlClient Data Provider" hostname="XXX" hostpid="14944" loginname="NT AUTHORITY\SYSTEM" isolationlevel="read committed (2)" xactid="359850034" currentdb="9" currentdbname="XXX" lockTimeout="4294967295" clientoption1="673415200" clientoption2="128056">
   <executionStack>
    <frame procname="CreatePartition" line="30" stmtstart="2052" stmtend="2218" sqlhandle="0x03000900d670d75323ec7f0094aa000001000000000000000000000000000000000000000000000000000000">
ALTER PARTITION FUNCTION...    </frame>
    <frame procname="CreatePartitions" line="26" stmtstart="1618" stmtend="1788" sqlhandle="0x030009000f95cb5424ec7f0094aa000001000000000000000000000000000000000000000000000000000000">
EXEC CreatePartition     </frame>
   </executionStack>
   <inputbuf>
Proc [Database Id = 9 Object Id = 1422628111]   </inputbuf>
  </process>
  <process id="process1d4593028c8" taskpriority="0" logused="0" waitresource="METADATA: database_id = 9 DATA_SPACE(data_space_id = 65601), lockPartitionId = 0" waittime="21105" ownerId="359870992" transactionname="sqlsource_transform" lasttranstarted="2019-07-31T08:14:29.460" XDES="0x1d4911c8de0" lockMode="Sch-S" schedulerid="4" kpid="5052" status="suspended" spid="93" sbid="0" ecid="0" priority="0" trancount="0" lastbatchstarted="2019-07-31T08:14:29.460" lastbatchcompleted="2019-07-31T08:14:29.447" lastattention="1900-01-01T00:00:00.447" clientapp=".Net SqlClient Data Provider" hostname="XXX" hostpid="14944" loginname="NT AUTHORITY\SYSTEM" isolationlevel="read committed (2)" xactid="359870992" currentdb="9" currentdbname="XXX" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
   <executionStack>
    <frame procname="GetXXX" line="17" stmtstart="-1" sqlhandle="0x03000900abe7e85629eb7f0094aa000001000000000000000000000000000000000000000000000000000000">

CREATE PROC [dbo].[GetXXX]
AS
BEGIN
    SET NOCOUNT ON;
    SELECT ...    </frame>
   </executionStack>
   <inputbuf>
Proc [Database Id = 9 Object Id = 1458104235]   </inputbuf>
  </process>
 </process-list>
 <resource-list>
  <objectlock lockPartition="0" objid="290100074" subresource="FULL" dbid="9" objectname="TableXXX" id="lock1d4eeca4f80" mode="Sch-S" associatedObjectId="290100074">
   <owner-list>
    <owner id="process1d4593028c8" mode="Sch-S" />
   </owner-list>
   <waiter-list>
    <waiter id="process1d3ea515848" mode="Sch-M" requestType="wait" />
   </waiter-list>
  </objectlock>
  <metadatalock subresource="DATA_SPACE" classid="data_space_id = 65601" dbid="9" lockPartition="0" id="lock1d48e40dc00" mode="Sch-M">
   <owner-list>
    <owner id="process1d3ea515848" mode="Sch-M" />
   </owner-list>
   <waiter-list>
    <waiter id="process1d4593028c8" mode="Sch-S" requestType="wait" />
   </waiter-list>
  </metadatalock>
 </resource-list>
</deadlock>


texts: Bag union operation in relational algebra

texts:
I have difficulty in understanding how does the bag union operates in relational algebra?

Here, 1) has a schema (X, Y, A)
2) (Y, A)
3) (X, A)
4) (Y, A)
so how will output look like after union operation on 1, 2, 3, and 4?
In my understanding for union operation schema should be the same but here 2 output of 4 has a different schema.

ANS: C


texts:
I have a "large" EC2 instance with 2 cores running MongoDB as part of a Replicaset.

Does MongoDB use both cores in parallel by default or do I need to make some configuration change?


texts: Transaction log backup - Only full recovery model?

texts:
I am reading this book from Microsoft - it says:
"A log backup cannot be performed unless the database is in full recovery model and an initial full backup has been performed"

Is this correct? I know simple recovery model doesn't support transaction log backup. But think I have read somewhere that full and also bulk recovery model supports transaction log backup.


texts: How do you create a Geographic Point with MariaDB and MySQL?

texts:
PostGIS provides a simple cast to construct a geography type from floating point lat, and long

SELECT ST_Point(1,2)::geography;     -- standard version (MM) less precision
SELECT ST_MakePoint(1,2)::geography; -- PostGIS version makes 2d, 3dz, 4d

However, despite the docs in MySQL clearly telling there is a difference between Cartesian and geodetic points, I can't find out how to construct a geography cord in MySQL or MariaDB?


texts: Database Design - hierarchical structure

texts:
We are trying to redesign a database that currently holds thousands of parameters for different datatypes and currently has a flat data structure.

We generate XML files from the database, which our software application uses to load and display the required data. As the complexity of the data used by the application increases, predominantly becoming more hierarchical and using more arrays, the current database structure is obviously not suitable for purpose.

We have discussed the possibility of the using a parent-child relationship for parameters that have an array of values, using an index to order the data. However, this does not solve the problem of storing data structures and arrays of data structures within a data structure within a database.

A typical data structure used within the application looks like the following:

class A {
    int MyInt;
    List<B> MyBs;
    C MyC; }

class B {
    string MyString;
    double[] MyNumbers; }

class C {
    string MyString;
    double MyDouble;
    B MyB;
    List<B> MyBs; }

Can anyone help advise us on how we might solve this issue?


texts: Return Distinct Records by Category Based Multiple Column Values

texts:
ID    MODEL    MODELSTATUS    CONTROL
11     100      GOOD           XN
24     100      TRENDING       BF
33     101      GOOD           XN
46     102      BAD            BF
50     103      BAD            XN
64     103      BAD            BF
77     104      PENDING        XN
89     104      TRENDING       BF
92     105      TRENDING       BF
93     105      TRENDING       XN

Given the data above, how would I return the results below.  There can either be 1 or 2 (at most) records with the same MODEL.  If there are 2 records for a MODEL the CONTROL will be different.

If MODESTATUS is 'Trending' return that record.  Else if MODELSTATUS is 'Pending' or 'Bad' return that record.  Else if there are 2 records for a MODEL where MODELSTATUS are the same return record with CONTROL is 'XN'

ID    MODEL    MODELSTATUS    CONTROL
24    100      TRENDING       BF
33    101      GOOD           XN
46    102      BAD            BF
50    103      BAD            XN
77    104      PENDING        BF
93    105      TRENDING       XN


texts: How to create Clustered and non clustered index in Oracle?

texts:
I dug couple of hours regarding my question and didn't get satisfactory answer. Still I have doubt. I have found the following about Clustered Index:

Data is stored in the order of the clustered index.
Only one clustered index per table.
When a primary key is created a clustered index is automatically created as well.

I got these points, but my questions are:

Does Clustered index exist in Oracle database? since I read in some blogs "Oracle does not have a concept of a clustered index."
If yes, please let me know the sql statement to create a clustered index.
As said above, clustered index automatically gets created when primary key is defined on a column of a table, how can I check the index type if it is created or not ?

Please find my table architecture :

Let me know if anything else is required to get answers for these questions.


texts: Oracle 11. Updating BLOB field. Db file sequential read inappropriately slow?

texts:
I've and Oracle (11 Enterprise) schema with a table

 CREATE TABLE USER.WSP_BUNDLE (
    NODE_ID     RAW(16) NOT NULL,
    BUNDLE_DATA BLOB NOT NULL
    );
CREATE UNIQUE INDEX USER.WSP_BUNDLE_IDX ON USER.WSP_BUNDLE(NODE_ID);

and 3'rd party library (Java 6, JDBC - latest jdbc driver) that manipulate it.

Oracle profiler (tkprof) shows that about 50% of overall time the lib execute such statement:

update WSP_BUNDLE set BUNDLE_DATA = :1 where NODE_ID = :2

TKProf data

Plan Hash: 4085453680
update WSP_BUNDLE set BUNDLE_DATA = :1  where NODE_ID = :2

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse      264      0.00       0.00          0          0          0           0
Execute    400     30.59     382.88     141451    1623163    3233827         400
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      664     30.59     382.88     141451    1623163    3233827         400

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 87

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  UPDATE  WSP_BUNDLE (cr=8753 pr=707 pw=706 time=0 us)
      1   INDEX UNIQUE SCAN WSP_BUNDLE_IDX (cr=3 pr=0 pw=0 time=0 us cost=2 size=104 card=1)(object id 75730)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  db file sequential read                    141251        5.53        328.04
  direct path write                             402        0.09          0.43
  SQL*Net more data from client              142158        1.04         11.89
  direct path read                              200        0.03          0.07
  Disk file operations I/O                        1        0.00          0.00
  SQL*Net message to client                     400        0.00          0.00
  SQL*Net message from client                   400        0.29          0.50
  log file switch (private strand flush incomplete)
                                                  5        0.05          0.23
  asynch descriptor resize                   139723        7.46          8.57
  buffer busy waits                               2        0.00          0.00
  log file switch (checkpoint incomplete)         3        0.18          0.27
  log file sync                                   2        0.00          0.00

Could anybody explain/hint me what's going on? Why the update is so slow?

The table WSP_BUNDLE contains about 200+k rows. At the same time I've other tables in the same schema with blobs (CLOB to be more concrete) that contains 600+k rows where similar updates work correctly.


texts: Need help to construct query/pl-sql block to get DB table column name and query result as key-value pairs

texts:
I have a DB table has more than 50 columns.

I am querying this table, it should return only one row any time. as below image from sqldeveloper.

here I need to construct query/pl-sql block, to find out table column as a key and query result as values.

Eg:     Key                         -  Value

      TASK_EVENT_ID   - 1765

      EVENT_TYPE        - ASR_UPDATE

       ...... etc upto all columns in my table.

In my procedure I am constructing as like below.

 Message_handle RAW(64);
Enqueue_options DBMS_AQ.enqueue_options_t;
Message_properties DBMS_AQ.message_properties_t;  msg
SYS.AQ$_JMS_BYTES_MESSAGE;
msg.set_string_property('TASK_EVENT_ID', v_TASK_EVENT_ID);
msg.set_string_property('EVENT_TYPE', v_EVENT_TYPE);

------------------------------------------------------------------------


texts:
In a Sql note "you should never put applications in each other's way if you do not have to.. Applications sharing memory, CPUs, internal tempdb  can cause an issue. One application submitting a runaway query can cause server to go to single halt." (SQL Server 2012 Unleashed) (Rankins)

Our company has 5 different applications. None of the multiple applications touch each other or utilize cross database joins. If company budget is not limited, resources are unlimited, and we have small overhead/ample time to administer different servers, should we place all applications on 5 servers boxes?


texts: How can I identify queries that are producing cache misses in Postgres?

texts:
I have a Postgres database with a total data size of 115GB. The server has ~60GB of memory.  The index cache hit rate is holding at 99%+ but the table cache hit rate has fallen to ~97%.

I am trying to identify if there are particular queries or access patterns we are making that are contributing to the drop.  We may be able to optimize the app if so.

I have used the below query to identify tables that have a low hit rate...

SELECT relname,
  CASE (sum(heap_blks_hit) + sum(heap_blks_read))
    WHEN 0 THEN 1
    ELSE sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read))
  END as hitrate,
  pg_size_pretty(sum(heap_blks_hit) + sum(heap_blks_read)) AS total_read,
  pg_size_pretty(sum(heap_blks_read)) AS total_miss
  FROM pg_statio_user_tables
  GROUP BY relname
  ORDER BY hitrate

I am not sure where to go from here though.  Is there a way to track if certain queries are commonly producing misses for the tables I know are low?


texts: Mariadb running but mysql.sock is on different directory

texts:
I was trying to move my database to a larger partition according to this guide.

My partition exists on /mdb/mysql-data, but when I edit the my.cnf file, it loads the old configuration on my.cnf.d/mariadb-server.cnf.

Here's what I did:

  I tried to comment the old ones but it won't run.
  Run MariaDB with both configs: mysql.sock is on /var/lib/mysql
  Moved my old directory by mv /var/lib/mysql var/lib/mysql.bak

Here's my current .cnf

My error after 3:

Why wont my socket generate on /mdb/mysql-data/?


texts: SQL Server Replication changing a tables ID numbers

texts:
I am using a merge replication setup for our ERP system and I encountered this problem:

While creating a new entry on a table and with the last ID number given was 25, then new entry got the ID number 40000+ (I don't quite remember the exact number). I found a way to solve it with this query:

DBCC CHECKIDENT('tableName', RESEED, NEW_RESEED_VALUE)

but I wasn't sure if using this would be ok with the replication. I know that replication uses an ID pool range for each system connected to the publication. Anyway after restarting the System the ID seed was fixed.

What I want to ask is if there is a safe way to change the ID range for a whole table if needed while using replication.


texts: Consistent Database Export of Oracle Database

texts:
I'm quite confused and I need some clarification!

I do full export of database with Export Pump (for replication/duplication).

What I usually do is:

SQL>SHUTDOWN IMMEDIATE
SQL>STARTUP RESTRICT

Then export, so I can have a consistent export (DMP-file).

Now can I export only with the parameter CONSISTENT=Y and/or FLASHBACK_SCN=SOME_NUMBER?
What is the differences (if any), and when to use them?

I hope some one explain it to me in simple language!

What are the best practices in this regard?


texts:
Given two columns, t1 with distinct values, but a superset of t2, how is it possible to operate over t1 to subtract the elements of t2 of t1? I utilize MySQL 5.7
CREATE TABLE IF NOT EXISTS t1 (
   id_ INTEGER  NOT NULL,
   value1 INT NOT NULL
INSERT INTO t1 (id_, value1) VALUES
(1),
(2),
(3),
(4),
(5),
(6),
(7),
(8)

CREATE TABLE IF NOT EXISTS t1 (
   id_ INTEGER  NOT NULL,
   value1 INT NOT NULL
INSERT INTO t2 (id_, value2) VALUES
(1),
(2),
(3),
(4)


texts: What is the storage size of a BIT (8) NULL column, and is it any different than BIT (7) or BIT (8) NOT NULL?

texts:
According to this question and the docs, the storage requirements for a BIT (M) field in MySQL are "approximately (M+7)/8 bytes".  This seems awfully vague, and the answers given there don't specify whether this is rounding up or down on the remainder. Which is it?

Specifically, (BIT(7)+7)/8 = 1.75. Is this one or two bytes? -- And is the bit used to determine NULL count towards an extra bit as in the case of SIGNED integers, making BIT (8) (one byte) effectively BIT (9) (two bytes)?

Or is every NULL bit stored in a separate byte at the head of the record, as suggested elsewhere?

Is it true that BIT (M) limits the value of the field, padding it physically with zeros only, but INT (N) doesn't? That seems inconsistent. Isn't the (N) dangerous if, even on the command line, it just tells it to calculate the same but hide the missing digits? -> Is the same true for BIT (M), e.g. can BIT (3) store up to 8 bits = minimum byte length?

Finally, why does the MySQL documentation say on the exact same page that:

  Each BIT(M) column takes M bits of storage space. Although an
  individual BIT column is not 4-byte aligned, NDB reserves 4 bytes (32
  bits) per row for the first 1-32 bits needed for BIT columns, then
  another 4 bytes for bits 33-64, and so on.

How does this jibe with the above, or are the storage requirements different because it is for NDB databases?

Note: I'm aware that MySQL no longer treats BIT as an alias for TINYINT (converting each bit into a byte)...

To clarify -- This is a programming question: I am trying to determine how many bits I should set aside in the limit for a bunch of NOT NULL bitmask fields in my SQL. (to allow for additional flags; I only use four or five flags.) What is the recommended per-byte limit? The additional flags will probably never be used, and I'd prefer to limit the number of possible values in the bitmask.


texts:
We have Slot table which tells us about freetime for an employee between starttime and endtime.

We need to find Slot which have freetime > 'x' mins. This could be either be a complete slot or it can be consecutive slot for same employee(in this case we show the first slot to user).

We are using MySQL 5.7

  `CREATE TABLE `Slot` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `starttime` datetime NOT NULL,
  `endtime` datetime NOT NULL,
  `freetime` time NOT NULL,
  `assigned_To_Id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`),
) ENGINE=InnoDB AUTO_INCREMENT=61 DEFAULT CHARSET=latin1

We tried following query :

Select        distinct S1.starttime,
               S1.endtime
from
    (Select * from `Slot`
     where     `assigned_To_Id` in (2, 3, 4)
     and       `freetime`  > 0
     and       `starttime` >= '2018-08-08 00:00'
     and       `endtime`   <= '2018-08-08 23:59'
     order by `Slot`.`starttime` ASC) as S1

inner join S1 as S2
 on        (S1.freetime >'00:10:00')
 or        (S2.starttime = S1.starttime + INTERVAL 60 MINUTE
 and       ADDTIME(S1.freetime, S2.freetime) > '00:10:00'
 and       S1.assigned_To = S2.assigned_To)

We are getting stuck as to how  to run self join on output of S1- its gives error saying that S1 table does not exist.

We read online and found about Common Table Expressions but we are using 5.7 version.

We are not sure how to go about doing inner join on same query.

Example :

|id| startime            | endtime             | assigned_To_Id | freetime
|1 | 2018-07-07 11:00:00 | 2018-07-07 12:00:00 |   2            | 00:00:05
|2 | 2018-07-07 12:01:00 | 2018-07-07 13:00:00 |   2            | 00:00:15
|3 | 2018-07-07 13:01:00 | 2018-07-07 14:00:00 |   2            | 00:00:05
|4 | 2018-07-07 15:01:00 | 2018-07-07 16:00:00 |   2            | 00:00:15

Output Should be :

|1 | 2018-07-07 11:00:00 | 2018-07-07 12:00:00 |   2            | 00:00:05
|2 | 2018-07-07 12:01:00 | 2018-07-07 13:00:00 |   2            | 00:00:15
|4 | 2018-07-07 15:01:00 | 2018-07-07 16:00:00 |   2            | 00:00:15


texts: InfluxDB batch insert only inserts the last line

texts:
I'm trying to insert some lines into a InfluxDB instance (running on Windows 7 x64) from both a .txt and a .csv file, but it's only inserting the last line on the file.

I'm currently using the Insomnia interface to send the file through Influx's HTTP API.
Example:

I have the following content inside a data.txt file:

any,mytag=ab value=59
any,mytag=ab value=78
any,mytag=ab value=102

All lines are ending with LF only (verifying it through N++).
Then I send this file via an HTTP request to my running InfluxDB instance:

POST http://localhost:8086/write?db=mydb&precision=ns

Yet, when I do a select * from "any" query, it shows that only the last line (value=102) was inserted. I've also tried inserting through text/plain content but to no success as well.

Any idea on this?

Thanks.


texts: How to disable data type check while pushing data to Linked Server

texts:
I have two SQL serves one is SQL server 2005 and other SQL server 2008.I push data from SQL server 2005 to SQL server 2008 using Linked Server.I want to improve performance of data transfer. Few days back I cam across one article (I don't remember exact site name) which mentioned  while inserting data in remote server you can disable data type check which can improve performance.He had mentioned one Set Command. I don't remember that command .Guys can you please help me to find out this command.I check with MSDN site but no help.

Also if you have any other performance improvement tricks please let me know.

Thanks in advance...


texts: Error while trying to process dimension in SSAS

texts:
I am getting the below error when I try to process my dimension in Analysis services from SQL Server Management Studio.

Errors in the OLAP storage engine: The attribute key cannot be found when processing: Table: 'AB_MATERIAL', Column: 'MAT_DESC', Value: 'SD DF44002R-N010 2.4L (Q)　'. The attribute is 'MAT_DESC_Attribute'.

I checked the dimension table AB_MATERIAL and am able to see the said missing value. However, whenever I browse the dimension is AS, I cannot see this value.

Can anyone help please.

Thanks.


texts: Is there a way to speed up JOIN ON LIKE?

texts:
I have millions of things in a table. I'd like to assign groups to the things based on theirs prefixes.

I tried to add all the indexes but the planner still does nested loop left join.

A demo case SQL:

create table things as
select
    i as id,
    left(md5(random()::text), 8) as name
from generate_series(1, 100000) as i;

create table match_group_rules as
select
    i as id,
    trunc(random() * 5 + 1) as group_id,
    left(md5(random()::text), 2) as rule
from generate_series(1, 100) as i;

create extension if not exists pg_trgm;
create index match_group_rules_rule on match_group_rules (rule);
create index match_group_rules_rule_pattern on match_group_rules (rule text_pattern_ops);
create index things_name_idx on things (name);
create index things_name_pattern_idx on things (name text_pattern_ops);
create index things_name_gin_trgm_idx on things using gin (name gin_trgm_ops);
create index things_name_gist_trgm_idx on things using gist (name gist_trgm_ops);

explain
select *
from things t
left join match_group_rules r
    on t.name like r.rule || '%';

The demo case planner output:

Nested Loop Left Join  (cost=0.00..176543.25 rows=100000 width=57)
  Join Filter: (t.name ~~ (r.rule || '%'::text))
  ->  Seq Scan on things t  (cost=0.00..1541.00 rows=100000 width=13)
  ->  Materialize  (cost=0.00..2.50 rows=100 width=44)
        ->  Seq Scan on match_group_rules r  (cost=0.00..2.00 rows=100 width=44)

Questions:

What makes the planner ignore the indexes?
Is there a quick way to compute the groups for the things?


texts:
Application server uses single login to connect to a database . There are multiple users connected to the database through the login in that case the information shows only the application login and the host name .

How do i track the individual user that is connected to database through the app server?

Since the dmv only shows the application login and the host name which is same for all the users that are connected.


texts: Is it possible to have indexes for like '%*%' type queries

texts:
Now for my thesis project i need to make a lot of queries like the below

select * from tblMyTable where ItemName like '%item%'

Now my question : is that possible to create indexes for such queries ? If so what is the way of doing it ? Thank you.


texts: What's the difference between MySQL Fabric and MySQL Cluster

texts:
I'm new to the MySQL world and was confused by the two. Don't they both provide high availability and sharding?

Also, how does WebScaleSQL compare?


texts: How to select top 3 vendors without duplicate entries?

texts:
Here is my tables

I want to select Top 3 lowest quoted rate vendors without duplicate Entry. Expected output will be V1, V4, V3 without using while loop. is this possible?

Table details

CREATE TABLE #Header(id int identity,HNo varchar(10),vendor varchar(50))
insert into #Header values('H1','V1')
insert into #Header values('H2','V2')
insert into #Header values('H3','V3')
insert into #Header values('H4','V4')
insert into #Header values('H5','V1')
insert into #Header values('H6','V6')
insert into #Header values('H7','V4')

CREATE TABLE #Detail(id int identity,DNo varchar(10),RATE MONEY )
insert into #Detail values('H1', 10)
insert into #Detail values('H2', 40)
insert into #Detail values('H3', 30)
insert into #Detail values('H4', 20)
insert into #Detail values('H5', 20)
insert into #Detail values('H6', 60)
insert into #Detail values('H7', 70)

SELECT * FROM #Header
SELECT * FROM #Detail

I have the result like below but vendors are duplicated, i can achieve using while loop and check eveytime vendor exist or not. is this any other way?

SELECT top 3 RATE,vendor FROM #Header,#Detail
where DNo = HNo
order by RATE


texts: Is there any way to add a witness without restarting the witness afterwards?

texts:
I want to add a third instance of SQL server to my mirroring pairs to act as a Witness. When I set the witness, the mirror remains disconnected from the witness and the mirror's log accumulates errors. There are two distinct errors that occur with the same timestamp repeating every ~24 seconds.

Mirror Error Message 1:

  Error: 1438, Severity: 16, State: 2.

Mirror Error Message 2:

  The server instance Witness rejected configure request; read its error log file for more information. The reason 1451, and state 3, can be of use for diagnostics by Microsoft. This is a transient error hence retrying the request is likely to succeed. Correct the cause if any and retry.

There are no corresponding errors on the witness server that I could find.

Database Mirroring Monitor will show the witness connection as disconnected on the mirror

In this scenario, I have three instances of SQL server. SqlServer01 is the principle. SqlServer02 is the mirror. The operating mode is high safety. I am attempting to transition to high safety with automatic fail-over by adding SqlServer03 as a witness.

All instances are running the same version SQL Server 2005, 9.00.5292.

Authentication is via SQL Server users with certificate.

The only solution I've found thus far is to restart SQL Server on the witness. Is there a better way? Why does the witness need to be restarted?

I would really like to avoid restarting witnesses because when I set this up in my production environment, witnesses are also part of separate mirroring pairs with their own separate witnesses. Therefore, restarting SQL server on one server will cause a fail-over of all the principal databases on that server.


texts:
Have a look at the following XML data:

<data>
    <test color="red">Red text</test>
    <test color="green">green</test>
</data>

Let's say I have several xml-documents with this structure in my database:

CREATE TABLE xmldata (
    id bigserial not null,
    documents xml)

Now I want to select all possible colors:

SELECT id, xpath('//test', xml) FROM xmldata;

But this returns a table with the id of each document and a text-array of the test-nodes. Furthermore, documents without any "test" node exist in the result as well - with an empty array {}

What I really want is a table like this:

| id | node                              |
|  1 | <test color="red">Red text</test> |
|  1 | <test color="green">green</test>  |

What is the syntax I have to use?

I heard that xpath_table may be the function to use - but this function is marked as deprecated...

(The returned table has to have one line for each occurence of the node I searched for. The node itself maybe an xml-snippet, text or something else - isn't really important)


texts: Change constraint on column based on value of another

texts:
Is it possible to change a constraint on a column in postgres based on the value of another column?
E.g. (pseudocode):

CREATE TABLE transactions(
    id SERIAL PRIMARY KEY,
    type TXN_TYPE NOT NULL,
    amount BIGINT,
    . . . .,
    refunded boolean DEFAULT FALSE,
    refund_id DEFAULT NULL if (CONSTRAINT link_refund CHECK (refunded=TRUE))=TRUE REFERENCES transactions(id)
);


texts: Unable to telnet to port 1433 in SQL Server clustered instance?

texts:
Situation: I believe that multiple port 1433 can be implemented as long as the IP address are all unique.
I’ve created SQL Server 2008 R2 SP1 clustered virtual instance on top of my Windows Server 2008 R2 SP1 Enterprise MSCS, one instance is working fine and I can perform:
C:\telnet SQLClustername-instance1 1433
successfully
However with the other instance I cannot perform the above command and it is not accessible at all ? pinging the SQLClustername-instance2 is fine and reply back, when I do the following command:
C:\netstat –ano | find /I “1433”
I cannot see the SQLClustername-instance2 IP address with port 1433 listening ?
Any assistance would be greatly appreciated.
Thanks.


texts: MySQL server on Microsoft Surface Pro X (ARM-64) work around?

texts:
I've been given a surface pro X laptop/tablet(?) which has been purchased for one of the hands-on finance directors who like to browse the database for the company I'm working for. On the looks of it when I've had a quick check of the device it has a Microsoft SQ1 processor which looks to be an ARM64 model.
The first thing I would usually do is install MySQL, with it being windows I've used the installer which straightaway flags up it's unsupported, quick check on the official website and I can see ARM64 is supported on Linux 7/8 but not windows.
Wondering if anyone has managed to achieve getting it working on ARM64 CPU's as my only 2 possible solutions that have been suggested by friends are either a docker container or to use MariaDB.
Appreciate all the suggestions and help, apologies if this is the incorrect place to ask.


texts:
I am designing database for a school app. Student is the main entity in the database. Student has relation with Parent & Guardian.

I am confused as where to store references. As will student table have foreign keys from parent & guardian table or vice versa.

UPDATE: Need to store student current data like course / batch / roll no etc which keeps on changing. For this purpose I am creating a new table student_current to store the current data with one to many relation from student to student_current table. At any time, i need to create a join with student_current table to fetch latest student_current record. To improve performance, I am storing primary key of student_current table as foreign key in student table and then making a simple join on this to fetch data. Is this approach right??

Also would appreciate your help in suggesting how to store revisions for student data.

Current schema is like:

student { id, student_current_id, name .... }
student_current { id, student_id, course .... }

Please help.


texts: Cannot Start mySQL Daemon with Error "mysqld from pid file ... ended"

texts:
I cannot get MySQL to run. It was working just fine hours ago, but upon starting, repeatedly ends without telling me very much. Here are my logs

[myusername@location ~/mysql]$ ./bin/mysqld_safe --log-error=my-errors &
[2] 14747
[1]    Done                          ./bin/mysqld_safe
[myusername@location ~/mysql]$ 150708 20:55:25 mysqld_safe Logging to '/rc00/d84/myusername/mysql/data/my-errors.err'.
150708 20:55:25 mysqld_safe Starting mysqld daemon with databases from /rc00/d84/myusername/mysql/data
150708 20:57:06 mysqld_safe mysqld from pid file /rc00/d84/myusername/mysql/data/ovid03.u.washington.edu.pid ended

Attempting to start the regular way leads to the following:

[myusername@location bin]$ mysqld
mysqld: Command not found.
[myusername@location bin]$ cd ..
[myusername@location mysqlver]$ ./bin/mysqld
150708 21:04:09 [Note] Plugin 'FEDERATED' is disabled.
150708 21:04:09 InnoDB: The InnoDB memory heap is disabled
150708 21:04:09 InnoDB: Mutexes and rw_locks use GCC atomic builtins
150708 21:04:09 InnoDB: Compressed tables use zlib 1.2.3
150708 21:04:09 InnoDB: Initializing buffer pool, size = 128.0M
150708 21:04:09 InnoDB: Completed initialization of buffer pool
InnoDB: Unable to lock ./ibdata1, error: 11

The last few lines repeat over and over again, as it tries to start.

I was reading around that there could be problems with ownership of files and folders, could this be it?

[myusername@location ~]$ ls -l mysql
total 0
lrwxrwxrwx 1 myusername nousers 12 Jul  1  2013 bin -> mysqlver/bin
lrwxrwxrwx 1 myusername nousers 29 Jul  1  2013 data -> /rc00/d84/myusername/mysql-data
lrwxrwxrwx 1 myusername nousers 13 Jul  1  2013 docs -> mysqlver/docs
lrwxrwxrwx 1 myusername nousers 16 Jul  1  2013 include -> mysqlver/include
lrwxrwxrwx 1 myusername nousers 12 Jul  1  2013 lib -> mysqlver/lib
lrwxrwxrwx 1 myusername nousers 12 Jul  1  2013 man -> mysqlver/man
lrwxrwxrwx 1 myusername users   40 Jul  1  2013 mysqlver -> /usr/local/mysql-5.5.18-linux2.6-x86_64/
lrwxrwxrwx 1 myusername nousers 16 Jul  1  2013 scripts -> mysqlver/scripts
lrwxrwxrwx 1 myusername nousers 14 Jul  1  2013 share -> mysqlver/share
lrwxrwxrwx 1 myusername nousers 18 Jul  1  2013 sql-bench -> mysqlver/sql-bench
lrwxrwxrwx 1 myusername nousers 22 Jul  1  2013 support-files -> mysqlver/support-files
lrwxrwxrwx 1 myusername nousers 14 Jul  1  2013 tests -> mysqlver/tests
lrwxrwxrwx 1 myusername nousers 12 Jul  1  2013 usr -> mysqlver/usr

I feel really lost right now and thank you in advance for your help. I do have a backup of the sql folder from a week ago, but am not yet ready to try putting that on to the server just yet.


texts: How do you migrate a database from SQL Server 2008 to MySQL?

texts:
I'm just getting into MySQL, so forgive me if this is a basic question. For my application I need to migrate my database from SQL Server 2008 R2 to MySQL.

Is there any mechanism or technique or suggestion or tips or magic tricks   for migration?


texts: What is the date function used to display month name from Date column in Teradata

texts:
I have a table with Date column and I need to display Month name.

I found EXTRACT(MONTH) function in teradata to display the month_number but it does not serve my purpose. I can still use case statement on month_number to display the month name.

Can someone help me with a better approach?


texts:
I am Using SQL SERVER 2008 R2

I just came across APPLY in SQL and loved how it solves query problems for so many cases,

Many of the tables I was using 2 left join to get the result, I was able to get in 1 outer apply.

I have small amount of data in my local DB tables and after deployment the code is supposed to run on data atleast 20 times big.

I am concerned that outer apply might take longer than the 2 left join  conditions for large amount of data,

Can anyone tell how exactly apply works and how will it effect the performance in very large data,
If possible some proportional relations with size of each table like proportional to n1^1 or n1^2 ... where n1 is number of rows in table 1.

Here is the query with 2 left join

select EC.*,DPD.* from Table1 eC left join
  (
   select member_id,parent_gid,child_gid,LOB,group_gid,MAX(table2_sid) mdsid from Table2
   group by member_id,parent_gid,child_gid,LOB,group_gid

  ) DPD2 on DPD2.parent_gid = Ec.parent_gid
        AND DPD2.child_gid = EC.child_gid
        AND DPD2.member_id = EC.member_id
        AND DPD2.LOB = EC.default_lob
        AND DPD2.group_gid = EC.group_gid
  left join
  Table2 dpd on dpd.parent_gid = dpd2.parent_gid
            and dpd.child_gid = dpd2.child_gid
            and dpd.member_id = dpd2.member_id
            and dpd.group_gid = dpd2.group_gid
            and dpd.LOB = dpd2.LOB
            and dpd.table2_sid = dpd2.mdsid

Here is the query with outer apply

select * from Table1 ec
OUTER APPLY (
      select top 1 grace_begin_date,retroactive_begin_date,Isretroactive
                    from Table2 DPD
                    where DPD.parent_gid = Ec.parent_gid
                    AND DPD.child_gid = EC.child_gid
                    AND DPD.member_id = EC.member_id
                    AND DPD.LOB = EC.default_lob
                    AND DPD.group_gid = EC.group_gid
                    order by DPD.table2_sid desc
     ) DPD


texts: How to remove files and filesgroup from dis that have data in it?

texts: I have created files and filegroup under partitioning process. These secondary files have data in them. I want to remove these files as I have deleted the table from the database but I am finding it difficult to remove them. how can I remove them?


texts: Query agent jobs for a given notification operator name

texts:
Is there a good way to query sql agent jobs for success / failure notification to a given operator name?
One of the operators is no longer with the company and need to fix up all agent jobs sending failure notifications to that person.


texts: what's `/etc/mysql/mariadb.cnf` used for? (i.e. `my.cnf` vs `mariadb.cnf`)

texts:
I'm trying to better understand how MariaDB is configured.

From mysqld --help --verbose I can see that my mariadb server daemon uses /etc/mysql/my.cnf.

But I'm curious about the /etc/mysql/mariadb.cnf file, and what it's intended for?  It has almost nothing in it compared to my.cnf.

The package mysql-common installs my.cnf
The package mariadb-common installs mariadb.cnf

On my Debian Buster system /etc/mysql/my.cnf and /etc/mysql/mariadb.cnf are similar in what they 'include'.

One minor difference is that because /etc/mysql/conf.d/mariadb.cnf already includes /etc/mysql/mariadb.conf.d, /etc/mysql/mariadb.cnf seems to include /etc/mysql/mariadb.conf.d twice.

my.cnf is full of options, and
mariadb.cnf has little to no content, so doesn't appear to be a replacement to my.cnf, at least not yet.

/etc/mysql/my.cnf               --!includedir--> conf.d
  /etc/mysql/conf.d/mariadb.cnf --!includedir--> mariadb.conf.d
    /etc/mysql/mariadb.conf.d/
    ...

/etc/mysql/mariadb.cnf           --!includedir-->  conf.d  AND  mariadb.conf.d
  /etc/mysql/conf.d/mariadb.cnf  --!includedir--> mariadb.conf.d
    /etc/mysql/mariadb.conf.d/

  /etc/mysql/mariadb.conf.d/         (duplicate load)
    ...


texts: MSSQL Express: linked server from Access database on network path

texts:
I run Microsoft SQL Server Express 64-bit version 14.0.2027.2 (X64) on Windows 10 Pro 10.

I have trouble to set the appropriate network UNC path and other settings for the linked server.

My connection:

EXEC master.dbo.sp_addlinkedserver
@server = N'linkedServerName',
@srvproduct=N'Access',
@provider=N'Microsoft.ACE.OLEDB.12.0',
@datasrc=N'\\unc\to\db.mdb'
GO

returns an 7303 Error:

    The test connection to the linked server failed.
    An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
    Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "linkedServerName".
    OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "xPohoda_UNC_nopass_Acc" returned message "The Microsoft Access database engine cannot open or write to the file '\\unc\to\db.mdb'.
 It is already opened exclusively by another user, or you need permission to view and write its data.".
 (Microsoft SQL Server, Error: 7303)

What is the workaround to make Linked Server to mdb file specified by UNC path functioning?


texts: SSIS column cannot convert between unicode and non-unicode string data types

texts:
I am transferring a table from SQL Server to PDA Server. This is my data flow:

ADO NET Source ---> OLE DB Destination

All of the columns in my source table are varchars and so when I created my destination table, I made sure that they had the same data types. So both tables have varchar data types for all columns.

However, I am still getting the error: Column "col1" cannot convert between unicode and non-unicode string data types. The weird thing is that if I replace my OLE DB Destination with a ADO Net Destination, it seems to work however, I want to use OLE DB so that I can specifying things such as rows per batch.

Can anyone help?


texts: Remote connection showing up as from localhost on MariaDB 10.2

texts:
I have a MariaDB 10.2.14 server running on a CentOS server (10.0.0.201) and a jobs server with various Java processes running on another CentOS server (10.0.0.193).  MariaDB is configured such that the root user can only connect from localhost - the various job apps have their own users to connect with.

One of the jobs is currently running and takes quite a while - it moves a lot of data using an insert-select statement.  If I do a show processlist in the database it tells me the connection has come from localhost and is running as root:

| 58684 | root             | localhost:57486  | db_name              | Query       |  23420 | Sending data ...

A netstat on the db server shows the connection with that client port is actually established from the jobs server:

netstat -n | grep 57486
tcp        0      0 10.0.0.201:3306         10.0.0.193:57486        ESTABLISHED

There is no job running on the DB server (it doesn't even have Java installed).  Other connections from the job server show as I'd expect - with the remote IP and the application user name.  Is anyone able to explain why this might be happening?  Is the MariaDB server somehow proxying the remote request?  Why would it be running as root?


texts: Table Design for Student Attendance hourly for each day

texts:
I have created a table (attendance_details) for storing every day attendance of Students in MySQL. Need to capture attendance for hourly (1-8hrs) every day. I have the student details in separate table (student_details).

As of now, the fields I have created in attendance_details are: attendance_no (Foreign Key which is Primary Key in the student_details table), date, hour1, hour2, hour3, hour4, hour5, hour6, hour7, hour8.

A User will only mark the absentees, and the remaining students should be marked as present.

Is this a good design, since I want the report for each day attendance based on hours of a all students in a class for every month? The Front-end is being developed C#.

Also, I need to keep track of every hour attendance.

Can you help me with the SELECT query which should generate report as below?

MySQL Database fields are shown in the pic below:


texts: Permissions Needed To Attach AdventureWorks Database

texts:
I am trying to add the AdventureWorks database in MS SQL Server Management Studio (SSMS) 2008 R2. As a new person to this area, or noob, this seemingly simple task has caused me much hardship and grief.

Initially I have tried: Right clicking on Databases, 'attach'... which immediately gets me the error:

Cannot show requested dialog.
Additional information:
Parameter name: nColIndex Actual value was -1. (Microsoft.SqlServer.GridControl)

So I've looked up other ways to add this database. I realise that there are many questions similar to the one I will ask below. I have tried their solutions to no avail, including:

Running SSMS as administrator (right clicking on program, and 'Run as Administrator'...). I do the same process as above, and get the same error as above.
Running an SQL script (by writing the script after clicking 'New Query'),

EXEC sp_attach_db    @dbname = 'DBName',
             @filename1='F:\AdventureWorks2008_Data.mdf',
             @filename2='F:\AdventureWorks2008_Log.ldf'

With (2), I get the error:

Msg 262, Level 14, State 1, Line 1
CREATE DATABASE permission denied in database 'master'.

Based on some websites (like this), the error is caused when there's a Permissions issue. But as I understand, I am an administrator.

The server I log onto is the local one, so the Server name is the same as my User name (through Windows Authentication), plus "\SQLEXPRESS".

Why can't I add the AdventureWorks database? What am i doing wrong? How can I get it working?


texts:
How can I turn this into an update query?

SELECT p.Pallet_code, Count(ps.SOHP_ID) as "Roll Count"
FROM PALLET p, PSOH ps
WHERE p.pallet_code = ps.SOHP_bin(+)
     and p.Plant_Code = 36
     and p.Pallet_Status = 1
     and p.pallet_CREA_TIME <= DATE '2017-4-11'
GROUP BY p.pallet_code
HAVING count(ps.sohp_id) = 0;

I tried this but I get a missing expression error.

I want to update pallet_status to 2:

UPDATE pallet
SET    pallet_status = 2
WHERE in (SELECT p.Pallet_code,
                 COUNT(ps.SOHP_ID) AS "Roll Count"
          FROM   PALLET p,
                 PSOH ps
          WHERE  p.pallet_code = ps.SOHP_bin (+)
          AND    p.Plant_Code = 36
          AND    p.Pallet_Status = 1
          AND    p.pallet_CREA_TIME <= DATE '2017-4-11'
          GROUP BY p.pallet_code
          HAVING COUNT (ps.sohp_id) = 0);


texts: Postgres transactions with persistent connection

texts:
So I have a program written in NodeJS, that has a pool of persistent connections, all works as expected except when I execute queries similar to the following:

BEGIN;
  INSERT INTO user(id, name) VALUES ('ce1b346cf35493d30', 'John Doe');
  INSERT INTO login(auth_id, user_id) VALUES ('123456', 'ce1b346cf35493d30') RETURNING id;
COMMIT;

If the transaction succeeds then all is good, but if it fails e.g. due to unique constraint on (auth_id, user_id) then the first time with throw the error I would expect due to the unique constraint, however if I attempt to run any other query (whether I expect it to succeed or fail) I get the following error:

ERROR: current transaction is aborted, commands ignored until end of transaction block
SQL state: 25P02

And the only way to fix it is to close and reopen the database connection. It would seem that the transaction is still somewhat left hanging in some way. The connection state gets stuck as idle in transaction (aborted). Is it normal that only one begin/commit transaction can be used per connection or is there some way to clear the transaction without having to re-establish a connection?


texts: How set balancing as main task for sharder MongoDB cluster?

texts:
I have a big collection (over 400 millions records) which I want to divided between 4 shards. I created infrastructure (4 shards, config server, mongos) and all work well (balancer is working and data is being moved). It has being worked greater than 15 hours and working now. My shards have 2662, 77, 77, 77 chunks. It is too slow!
As I know balancer try to keep mongo available. But now nobody use db and my main task is to finish balancing asap.
Could anybody know any command which set balancing as the most task?


texts: Dropped the Distibution database before disabling publishing and distribution, how to proceed?

texts:
I'm setting up my first Distributor database on SQL Server 2012.  I successfully created one and then wanted to blow it away and perform the task again just for practice.

This was a brand new Distributor and nothing had been done with it yet.

In my naivete I ran the the following commands before disabling the publishing and distribution:

alter database distribution set offline;
drop database distribution;

Now when I try to disable it, I get the following error:

SQL Server could not disable publishing and distribution on '<server>'.
------------------------------
Database 'distribution' does not exist. Make sure that the name is entered correctly.
Changed database context to 'master'. (Microsoft SQL Server, Error: 911)

Is there a way I can fix this?


texts: Moving SQL Server database to a new disk while online

texts:
I have a 1.4TB SQL Server Database that's struggling massively with disk I/O. We've installed a new SSD array into the server that will solve all of our problems, we're just debating the best way to move the database across. Ideally if we can do it without downtime, that's best. But where the choice is between two days of poor performance (e.g. while copying data) versus two hours of downtime, the latter might be preferable.

So far, the solutions we've come up with are:

Simple copy. Take the DB offline, copy the files across, change the locations in SQL Server and bring it back online. Rough figures estimate this will take up to five hours, which is not really acceptable, but it is the easiest solution.
Block-level copy. Using an rsync-like utility, we copy the files across in the background while the DB is up. When we're ready to migrate, we take the DB offline, do a differential copy across using this utility, then point SQL server at the new files and bring it online. Timing here is unknown. We don't know how long it will take to do a differential analysis of 1.4TB and copy that across. Our other concern is that the block-level copy will leave the files in some state unreadable by SQL Server and we'll have waste our time.
SQL migration. Create a new 1.4TB SQL data file on the new disk and disable autogrowth on all of the other files. Then run DBBC SHRINKFILE(-file_name-, EMPTYFILE) on all of the other data files in turn. Once all of the data is across, I'll take a scheduled window at some point to move the MDF file over to the SSD and remove the other unused files. I like this because it minimises downtime. But I have no idea how long this will take and whether it will cause degradation in performance while it's happening.

We don't have any kind of load & performance environment to test this. I can verify that the strategies will work on our staging environment, but not the impact and not the performance.


texts: Creating Windows login/user for Mixed Always on Environment+ Instance without Always ON

texts:
How can i use by making changes to below  script to run in the environments with Always ON enabled/and not enbaled. Below seems to be working after some really helpful suggestions from Kin. However this code would certainly not be at its best when i need to present for deployment across 300-400 SQL servers with some having always on others not.

Please help me in modifying /check if instance has AG enabled and execute with success

UPDATE As requested by @Kin

SET NOCOUNT ON

 DECLARE @user_to_be_added sysname
 SET @user_to_be_added = 'Domain\user'

 DECLARE @sqltext nvarchar(max) = N''

     if (select serverproperty('IsHadrEnabled')) = 0

  BEGIN

           -- change  to your user to be added .. make sure that the login is already existing

       SELECT  @sqltext += char(10)+ 'use '+quotename(name)+';'+char(10)+ 'CREATE USER '+quotename(@user_to_be_added)+' FOR LOGIN '+quotename(@user_to_be_added)

       FROM sys.databases where state_desc ='ONLINE' AND databaseproperty(name,'isReadOnly') = 0

       SELECT  @sqltext

 END

    Else

 BEGIN

      SELECT  @sqltext += char(10)+ 'use '+quotename(name)+';'+char(10)+ 'CREATE USER '+quotename(@user_to_be_added)+' FOR LOGIN '+quotename(@user_to_be_added)

      FROM sys.dm_hadr_availability_replica_states ars
      inner join sys.databases dbs ON ars.replica_id = dbs.replica_id
      where dbs.state_desc ='ONLINE' AND databaseproperty(dbs.name,'isReadOnly') = 0
      and ars.role_desc = 'PRIMARY' -- we want to filter only Primary AG where dbs are writable.

      if  (@sqltext <> '')
       begin
           select @sqltext
       end
      else
        begin
           print 'There is nothing to print - Probably its Secondary Replica of AG'
        end

 END


texts: Is there an execution difference between a JOIN condition and a WHERE condition?

texts:
Is there a performance difference between these two example queries?

Query 1:

select count(*)
from   table1 a
join   table2 b
on     b.key_col=a.key_col
where  b.tag = 'Y'

Query 2;

select count(*)
from   table1 a
join   table2 b
on     b.key_col=a.key_col
   and b.tag = 'Y'

Notice the only difference is the placement of the supplemental condition; the first uses a WHERE clause and the second adds the condition to the ON clause.

When I run these queries on my Teradata system, the explain plans are identical and the JOIN step shows the additional condition in each case.  However, on this SO question regarding MySQL, one of the answers suggested that the second style is preferred because WHERE processing occurs after the joins are made.

Is there a general rule to follow when coding queries like this?  I'm guessing it must be platform dependent since it obviously makes no difference on my database, but perhaps that is just a feature of Teradata.  And if it is platform dependent, I'd like very much to get a few documentation references; I really don't know what to look for.


texts: Add SQL Server Level Login in master Database using Visual Studio 2019

texts:
I'm trying to add sever login in Azure SQL Server using Visual Studio 2019 SQL Project with SSDT and deploy it using Azure Devops by building CICD pipeline. The rest database object (e.g. tables, sprocs, external data sources, external tables, etc) created successfully except database logins.

My database project already had security folder that include login scripts (since i'm importing from existing database). The problem is, in order to add a server login, you must to add it to master databases and my CICD pipeline line set to non master database.

How do I add sever login to Azure SQL database using VS2019 and CICD via Azure Devops?


texts: How to store a query result into a variable? SQL Server 2008

texts:
I have two tables and I want to store the result query of my stored procedure
into a variable.

UserTable:

UserID int primary key identity
CredentialsID int
AccountType nvarchar(15)
ModifiedDate date

CredentialsTable:

Credentials ID int primary key identity
Password

Stored procedure:

Create procedure addFaculty
    @ID int
as
begin
    IF NOT EXISTS (Select AccountType from UserTable where AccountType = 'Faculty')
    begin
        Insert into CredentialsTable values ('iamafaculty');

        Select CredentialsTable.CredentialsID
        from CredentialsTable
        where CredentialsTable.Password = 'iamafaculty';
    end
end

Executing the select statement will return a credentialsID which I want to
store in the @ID parameter. So I can use it on my other statements to be made.


texts: Does MySql event will start if the previous event doesn't finish yet?

texts:
Example I have an event that will run every 5 seconds, then the first instance of the event is triggered but more than 5 seconds later the instances still not done executing. What will happen to the event would it execute again after 5 seconds even the first event is not finished yet or it will wait for the first instance of the event to finish before executing?

Thanks


texts: updating n number of records mysql database

texts:
I want to update n number of records in table using a query like below, but it will return an error since in mysql, it is not allowed to use value after the limit key
update producedItem pi
inner join productionOrderLine pol on pol.id = pi.productionOrderLineId
set pi.isReserved = 1
limit floor(pol.quantity - pol.availableQuantity);

what would be the better alternative?


texts: Join 3 tables, aggregate over date field and subtract sum of two columns

texts:
I have 3 tables and the they have the following columns.

shipments: shipment_id (PK), shipment_date (date)

invoices: invoice_id (PK), shipment_id (FK), amount (float)

bills: bill_id (PK), shipment_id (FK), amount (float)

Each shipment may have multiple invoices and bills. The net revenue of a month is defined as the sum of all bill amounts subtracted from the sum of invoice amounts in that month (derived from shipment_date). While a whole shipment may span across multiple months, we're only concerned about net revenue of the month as an aggregate.

Is there a way to calculate net revenues of all the months (depending on the data) with a single SELECT query?

Ignore the database design. And I'm trying the following query.

SELECT
    u.`month`  AS `month`,
    FORMAT(SUM(u.`amount`),2) AS `net_revenue`
FROM
    (SELECT
        i.`amount` AS `amount`,
        DATE_FORMAT(s.`shipment_date`, "%m/%Y") AS `month`
    FROM
        `invoices` i
        LEFT OUTER JOIN `shipments` s
            ON s.`shipment_id` = i.`shipment_id`
    UNION
    SELECT
        (-1 * b.`amount`) AS `amount`,
        DATE_FORMAT(s.`shipment_date`, "%m/%Y") AS `month`
    FROM
        `bills` b
        LEFT OUTER JOIN `shipments` s
            ON s.`shipment_id` = b.`shipment_id`) u
GROUP BY u.`month`
ORDER BY u.`month` ASC;


texts: Diff in idx_tup_read and idx_tup_fetch in postgres pg_stat_all_indexes

texts:
I have gone through documentation and previous stackoverflow questions but not findings those helpful.
Postgres version 10.6
Even for index only scans values of idx_tup_fetch is increasing. So I am not able to understand difference between idx_tup_read and idx_tup_fetch.
Initially understanding was:
idx_tup_read: matching records read from index.
idx_tup_fetch: live rows fetched from table after records are matched on index. So my understanding is that for index only scan since no data is fetched from table so this should not increment.
But when I ran the tests then this understanding is not true.
postgres=> select * from pg_stat_all_indexes where indexrelname like 'test_monika_rank';
 relid | indexrelid | schemaname |   relname   |   indexrelname   | idx_scan | idx_tup_read | idx_tup_fetch
-------+------------+------------+-------------+------------------+----------+--------------+---------------
 16413 |      16418 | public     | test_monika | test_monika_rank |        4 |            2 |             2
(1 row)

postgres=> select * from test_monika where rank=100;
 id | name | rank
----+------+------
 95 |      |  100
(1 row)

postgres=> select * from pg_stat_all_indexes where indexrelname like 'test_monika_rank';
 relid | indexrelid | schemaname |   relname   |   indexrelname   | idx_scan | idx_tup_read | idx_tup_fetch
-------+------------+------------+-------------+------------------+----------+--------------+---------------
 16413 |      16418 | public     | test_monika | test_monika_rank |        5 |            3 |             3
(1 row)

postgres=> select rank from test_monika where rank=100;
 rank
------
  100
(1 row)

postgres=> select * from pg_stat_all_indexes where indexrelname like 'test_monika_rank';
 relid | indexrelid | schemaname |   relname   |   indexrelname   | idx_scan | idx_tup_read | idx_tup_fetch
-------+------------+------------+-------------+------------------+----------+--------------+---------------
 16413 |      16418 | public     | test_monika | test_monika_rank |        6 |            4 |             4
(1 row)

postgres=> select id from test_monika where rank=100;
 id
----
 95
(1 row)

postgres=> select * from pg_stat_all_indexes where indexrelname like 'test_monika_rank';
 relid | indexrelid | schemaname |   relname   |   indexrelname   | idx_scan | idx_tup_read | idx_tup_fetch
-------+------------+------------+-------------+------------------+----------+--------------+---------------
 16413 |      16418 | public     | test_monika | test_monika_rank |        7 |            5 |             5
(1 row)

postgres=> select 1 from test_monika where rank=100;
 ?column?
----------
        1
(1 row)

postgres=> select * from pg_stat_all_indexes where indexrelname like 'test_monika_rank';
 relid | indexrelid | schemaname |   relname   |   indexrelname   | idx_scan | idx_tup_read | idx_tup_fetch
-------+------------+------------+-------------+------------------+----------+--------------+---------------
 16413 |      16418 | public     | test_monika | test_monika_rank |        8 |            6 |             6
(1 row)


texts: How do you structure a database that allows for groups to be formed/added by users and also be a part of like facebook?

texts:
I'm new to databases and web development so a laymen explanation is best for the time being. I'm only in the data modeling phase so no need for specific code. I'm just trying to wrap my head around how the data would be managed and I'll get around to figuring out code when the time comes. I already understand the concept of OneToMany and ManyToMany, so no need to explain that to me.

Basically, what I'd like to do is for users to be able to create a group and name it whatever, and also have a list of already existing groups for a user to add themselves to, similar to the way Facebook's "interests" works. Users can type in whatever interest they want and if it already exists, they get added to a group of people who have that interest.

Which tables need to be created? Obviously I'll need a UserTable, InterestTable... anything else? How would the tables relate to one another? What keys would each table need?


texts: Alternative of trigger to insert updated date in a column

texts:
I am looking for some best practice to insert the update date value in column whenever update query is run without using any trigger. So could anyone suggest me is there any best way except using Trigger or manually entering the update date value with update statement.
Thanks


texts: Date variable to filter result for all previous years

texts: I am querying a sales order table and need to add a variable so that the query always returns a customer list for all customers that have not purchased this year.  I don't want to hard code this years' date into WHERE or HAVING, but instead am interested to somehow have this dynamically pass the correct variable so that it also works in the future without changing the code. I appreciate any suggestions. Hope this is clear enough.


texts: Error PLS-00436: implementation restriction: cannot reference fields of BULK In-BIND table of records

texts:
i've problem here. i wanna create a Stored procedure like below, but i encountered error PLS-00436: implementation restriction: cannot reference fields of BULK In-BIND table of records.

 create or replace
    PROCEDURE IB_ARCHIVE_FDS_TEST(TABLE_NAME varchar2, COLUMN_NAME varchar2, criteria varchar2, ACTIVE_PERIOD number)
    IS
      TYPE subset_fds IS RECORD(
        message_id            ib_fds_log.message_id%TYPE,
        fds_activity_type     ib_fds_log.fds_activity_type%TYPE,
        ref_no                ib_fds_log.ref_no%TYPE,
        direction             ib_fds_log.direction%TYPE,
        type                  ib_fds_log.type%TYPE,
        ref_ref_no            ib_fds_log.ref_ref_no%TYPE,
        time_request          ib_fds_log.time_request%TYPE,
        time_response         ib_fds_log.time_response%TYPE,
        request_soapid        ib_fds_log.request_soapid%TYPE,
        response_soapid       ib_fds_log.response_soapid%TYPE,
        status                ib_fds_log.status%TYPE,
        is_feedback           ib_fds_log.is_feedback%TYPE,
        session_id            ib_fds_log.session_id%TYPE,
        status_fds_message    ib_fds_log.status_fds_message%TYPE);

      TYPE fds_tabs IS TABLE OF subset_fds INDEX BY PLS_INTEGER;
      TYPE fds_message_id IS TABLE OF IB_FDS_LOG.message_id%TYPE INDEX BY PLS_INTEGER;
      TYPE fds_request IS TABLE OF IB_FDS_LOG.request%TYPE INDEX BY PLS_INTEGER;
      TYPE fds_response IS TABLE OF IB_FDS_LOG.response%TYPE INDEX BY PLS_INTEGER;

      v_fds_log fds_tabs;
      v_message_id fds_message_id;
      v_request fds_request;
      v_response fds_response;
    BEGIN

        SELECT message_id, fds_activity_type, ref_no, direction, type, ref_ref_no, time_request, time_response, request_soapid, response_soapid, status, is_feedback, session_id, status_fds_message BULK COLLECT INTO v_fds_log FROM IB_FDS_LOG2 where direction = 0 and status_fds_message = 0 and time_request < trunc(sysdate - ' || ACTIVE_PERIOD || '+ 1);
      SELECT request BULK COLLECT INTO v_request FROM IB_FDS_LOG2 where direction = 0 and status_fds_message = 0 and time_request < trunc(sysdate - ' || ACTIVE_PERIOD || '+ 1);
      SELECT response BULK COLLECT INTO v_response FROM IB_FDS_LOG2 where direction = 0 and status_fds_message = 0 and time_request < trunc(sysdate - ' || ACTIVE_PERIOD || '+ 1);

        FOR i IN v_fds_log.FIRST .. v_fds_log.LAST LOOP
            v_message_id(i) := v_fds_log(i).message_id;
        END LOOP;

        FORALL indx IN 1 .. v_fds_log.COUNT
            INSERT INTO IB_FDS_LOG3 (message_id,fds_activity_type,ref_no,direction,type,ref_ref_no,time_request,time_response,request_soapid,response_soapid,status,is_feedback,session_id,status_fds_message)VALUES (v_fds_log(indx).message_id,v_fds_log(indx).fds_activity_type,v_fds_log(indx).ref_no,v_fds_log(indx).direction,v_fds_log(indx).type,v_fds_log(indx).ref_ref_no,v_fds_log(indx).time_request,v_fds_log(indx).time_response,v_fds_log(indx).request_soapid,v_fds_log(indx).response_soapid,v_fds_log(indx).status,v_fds_log(indx).is_feedback,v_fds_log(indx).session_id,v_fds_log(indx).status_fds_message);
            COMMIT;

      FORALL indx IN 1 .. v_fds_log.COUNT
            INSERT INTO IB_FDS_LOG3 (RESPONSE) VALUES (v_fds_log(indx).response);
            COMMIT;

      FORALL indx IN 1 .. v_fds_log.COUNT
            INSERT INTO IB_FDS_LOG3 (REQUEST) VALUES (v_fds_log(indx).request);
            COMMIT;

        FORALL indx IN 1 .. v_fds_log.COUNT
            DELETE FROM IB_FDS_LOG2 WHERE message_id = v_message_id(indx);
            COMMIT;
    END;

please help me to solve my problem,
thanks


texts: Are Clustered Index Usage Stats skewed since they are referenced in Non-Clustered Keys?

texts:
When using tools like SP_BlitzIndex and looking at usage stats for indexes. Would clustered index usage be skewed in any way since they are secret keys in all non-clustered indexes and would be read more often than not?

Trying to determine if a table has the correct clustered index, it's read usage is high but looking at how data is being accessed, I don't see a lot of direct joins utilizing the clustered index column that would lead me to believe the usage numbers.


texts: Listing the existing SQL Server Logins and Users

texts:
I know we can check the logins and the users that are defined, using GUI in SQL Server, but am wondering how we can do this check using script.

I ran the query below but it shows Principal_id which I'm not sure how to map to get the permission level.

SELECT * FROM Sys.login_token

So is there any built-in stored proc that can list the logins and the users with their permission level?

Thank you.


texts: How to enable missing Tempspc column in Oracle explain plan

texts:
I am trying to debug "ORA-01652: unable to extend temp segment" from a data warehouse ETL batch job, but my plan_table_output view from explain plan does not show the "Tempspc" column. But I do not seem to have the "'PLAN_TABLE' is old version" problem.

Version: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0

Query: select * from table(dbms_xplan.display_awr(sql_id, null, null, 'ALL'));

Columns returned: Id, Operation, Name, Rows, Bytes, Cost (%CPU), Time

Notes returned:
- dynamic statistics used: dynamic sampling (level=2)

The following query shows that all columns in the global temp plan_table match the @catplan.sql admin script:

select * from all_tab_columns
where owner = 'SYS' and table_name = 'PLAN_TABLE$'
order by column_name;

And this query shows the public synonyms looks ok:

select owner, object_type, object_name from all_objects
where object_name like 'PLAN_TABLE%';

OWNER   OBJECT_TYPE  OBJECT_NAME
------  -----------  -----------
SYS     TABLE        PLAN_TABLE$
PUBLIC  SYNONYM      PLAN_TABLE

Does anyone have advice on how to enable the missing Tempspc column in my explain plan?


texts:
Whenever I try to try to create a GIST index on text[], I get the above error.

CREATE TABLE bar(f)
AS VALUES
  (ARRAY['foo','bar','baz']);

CREATE INDEX ON bar USING gist(f);

ERROR:  data type text[] has no default operator class for access method "gist"
HINT:  You must specify an operator class for the index or define a default operator class for the data type.

I'm trying to create an Exclusion Constraint on bar, so I need a GIST index.


texts:
I'm testing transactional replication between 4 databases, 2 in one machine(Virtual) and 2 in other(also virtual). None of my virtual machines is running an anti-virus.

It's all working as expected, unless I try to insert a blob of considerable size (65mb in this case) if I insert a blob of 3kb works just fine.

I'm having the following error "The distribution agent failed to create temporary files in 'C:\Program Files\Microsoft SQL Server\100\COM' directory. System returned errorcode 5".

This problem is documented here.And I already changed the settings of that folder so that is not read-only anymore (right click on the folder->properties and changed the read only-attribute) I'm not sure if this is the right way to grant permissions to do it.

The reason I say I have no anti-virus running is because I also read that was one source for this problem, as the antivirus might block the folder while inspecting it.

Also tried to change Distribution Profile for OLEDB streaming, that also didn't work..

What else can I do to solve this problem?


texts:
I'm running update-or-insert query on a table of approximatelly 3mln rows, 4 columns.
Query is part of every day process, and for same days, the query stucks forever.
It's just blocked. It's deterministic, for a particular date, the query always stucks.

Query looks more or less like this:
[..] update part, not important, works fine
-- insert part:

insert into t (select * from tmp where col1, col2, col3 not in (select col1, col2, col3 from t));

There is only one potentially interesting row in pg_stat_activity. It's the above query, with column waiting equals to 'f'.

Any ideas what might be the cause? What is actually the semantics of that query? Is it first executing the whole subquery and remembers results or more like lazy evaluation? It is possible that subquery acquires a shared lock on a table level, and insert can't acquire its own lock? Why for some days query works fine?

Thanks!


texts: Which one for MySql Bind addresss 127.0.0.1 (in apache & ubuntu)

texts:
Should I set the bind address 127.0.0.1 in

etc/mysql/my.cnf

or

/etc/mysql/mysql.conf.d/mysqld.cnf

I am using

mysql Ver 14.14 Distrib 5.7.20, for Linux (x86_64)

mysqladmin Ver 8.42 Distrib 5.7.20, for Linux on x86_64


texts: How to convert query results into groups of common results

texts:
I have a query that returns results like this:

     MyValue        MyGroup
_______________|_______________
       56      |      I
_______________|_______________
       12      |      I
_______________|_______________
       56      |      II
_______________|_______________
       12      |      II
_______________|_______________
       56      |      III
_______________|_______________
       56      |      IV
_______________|_______________
       56      |      V
_______________|_______________
       12      |      V
_______________|_______________

I need to convert that into something like this:

      ColA           ColB             ColC            ColD           Count
_______________|_______________|_______________|_______________|_______________
       12      |      56              null            null             3
_______________|_______________|_______________|_______________|_______________
       56      |      null            null            null             2
_______________|_______________|_______________|_______________|_______________

I know I could setup an iterator and go over all the rows of my table and construct this result.

But I think there is another, "set based" way.  Maybe a "Cross Apply" or "Pivot".

But it does not matter how many times I read the docs or look at examples for those, I can't seem to wrap my head around them enough to write my own.

Does anyone have a suggestion?

NOTE: In case it matters, assume that my query to get the first results looks like this:

select MyValue, MyGroup
from SomeTable

(It is actually a complex set of CTE's but I can just add another layer if need be.)


texts: Restore a single database backup (.bak) file into multiple filegroups

texts:
I have a SQL Server 2014 database DB01 (single filegroup - PRIMARY) and I took a backup of it into a DB01.bak file in SSMS.

I have another database DB02 with 1 primary filegroup (1 file) and one secondary filegroup (2 files). I am trying to restore the DB01.bak file into DB02 with multiple filegroups. Is it possible to do this in SSMS?


texts: Find all tables that are related to a view recursively sql server

texts:
I have a view-A that is comprised of other views (View-B View-C) and I would like to list all the tables that used by View-A (which would include the tables used in View-B and View-C).  When I use this code...

-- Get base tables for 'View-A' in Database-A
USE Database-A
GO
SELECT view_name, Table_Name
FROM INFORMATION_SCHEMA.VIEW_TABLE_USAGE
WHERE View_Name = 'View-A'
ORDER BY view_name, table_name
GO

...I only get the tables that are immediately used by View-A plus the name of the other views (View-B, View-C).  Is there a way to get recursively the names of the tables that are also used by View-B and View-C?


texts: I can't get MySQL server to stop running

texts:
The output from MySQL Workbench when I try to stop it is:

Executing '/usr/local/mysql/support-files/mysql.server stop' 2013-12-23 21:08:29 - Stop server:
ERROR! MySQL server PID file could not  2013-12-23 21:08:29 - Stop server: be found!


texts:
I have the following setup:

One Windows cluster consisting of four cluster nodes: A, B, C, D. Cluster nodes A and B in one data centre, cluster nodes C and D in a second data centre.

SQL failover cluster instance SQLFCI1 defined over nodes A and B, SQL failover cluster instance SQLFCI2 defined over nodes C and D.

SQL availability group SQLAG1 defined on SQLFCI1 with asynchronous replica being SQLFCI2. SQLAG1 associated with  listener SQLListener1.

All client apps use SQLListener1 in their application connection strings.

End result

High availability because of FCI between A and B in data centre 1 as well as disaster recovery to FCI between C and D via async availability group. That DR site has high availability as well because the availability group sits on an FCI as well.

Minimal impact to applications because they all use the AG listener name that in effect can move across all four nodes.

Quorum partner in a third data centre.

Is this a valid setup?


texts: SQL Server replication - set PollingInterval in sp_addmergepushsubscription_agent

texts:
I am trying to create replication subscriptions to and SQL Server 2008 R2 via a script. There are 3 subscribers so this script creates the subscriptions by calling

sp_addmergesubscription

and then

sp_addmergepushsubscription_agent

with the required parameters for each subscriber. The only thing I have not accomplished so far is to tell the sp_addmergepushsubscription_agent that I wish to set the PollingInterval to 30 seconds instead of the default 60 seconds. What parameters need to be changed in order to do the trick?


texts: How to install PLV8 in PostgreSQL 11 in Ubuntu 16?

texts:
I am trying to install PLV8 extension (JavaScript Procedural Language for PostgreSQL) in my PostgreSQL 11 Ubuntu Server 16 and having a hard time. The PostgreSQL 11 was installed using PostgreSQL's http://apt.postgresql.org/pub/repos/apt/ xenial-pgdg main repository through apt install command.

My attempts to compile from source code while being guided by the official documantation failed. The PLV8 source code I am targeting is this one https://github.com/plv8/plv8/archive/v2.3.11.tar.gz.

While running make, the machine throws the following error

root@ubuntuLocal:/home/ubuntu/plv8/plv8-2.3.11# make
cat: '/usr/include/postgresql/11/server/pg_config*.h': No such file or directory
/bin/sh: 1: test: -ge: unexpected operator
/bin/sh: 1: test: -lt: unexpected operator
/bin/sh: 1: test: -lt: unexpected operator
/bin/sh: 1: test: -lt: unexpected operator
/bin/sh: 1: test: -lt: unexpected operator
Makefile.shared:183: /usr/lib/postgresql/11/lib/pgxs/src/makefiles/pgxs.mk: No such file or directory
make: *** No rule to make target '/usr/lib/postgresql/11/lib/pgxs/src/makefiles/pgxs.mk'.  Stop.

And while using make PG_CONFIG=/usr/lib/postgresql/11/pg_config it take ages to compile then throws the following:

Updating Clang to 318667-1...
Downloading prebuilt clang
Downloading https://commondatastorage.googleapis.com/chromium-browser-clang/Linux_x64/clang-318667-1.tgz .......... Done.
Creating directory /home/ubuntu/plv8/plv8-2.3.11/build/v8/third_party/llvm-build/Release+Asserts
clang 318667-1 unpacked
Hook '/usr/bin/python v8/tools/clang/scripts/update.py' took 71.09 secs
Running hooks:  95% (22/23) regyp_if_needed
________ running '/usr/bin/python v8/gypfiles/gyp_v8 --running-as-hook' in '/home/ubuntu/plv8/plv8-2.3.11/build'
GYP is now disabled by default in runhooks.

If you really want to run this, either run
`python gypfiles/gyp_v8` explicitly by hand
or set the environment variable GYP_CHROMIUM_NO_ACTION=0.
Running hooks: 100% (23/23), done.
[detached HEAD 3d27d55] [ios] Various build fixes for Xcode 10.
 Author: Justin Cohen <justincohen@google.com>
 Date: Tue Jun 5 15:30:25 2018 +0000
 2 files changed, 36 insertions(+), 11 deletions(-)

Hint: You can raise verbosity (-vv) to see the output of failed commands.

Traceback (most recent call last):
  File "tools/dev/v8gen.py", line 304, in <module>
    sys.exit(gen.main())
  File "tools/dev/v8gen.py", line 298, in main
    return self._options.func()
  File "tools/dev/v8gen.py", line 166, in cmd_gen
    gn_outdir,
  File "tools/dev/v8gen.py", line 208, in _call_cmd
    stderr=subprocess.STDOUT,
  File "/usr/lib/python2.7/subprocess.py", line 574, in check_output
    raise CalledProcessError(retcode, cmd, output=output)
subprocess.CalledProcessError: Command '['/usr/bin/python', '-u', 'tools/mb/mb.py', 'gen', '-f', 'infra/mb/mb_config.pyl', '-m', 'developer_default', '-b', 'x64.release', 'out.gn/x64.release']' returned non-zero exit status 1
Makefile:35: recipe for target 'build/v8' failed
make: *** [build/v8] Error 1

Then my attempts to install from PGXN have failed too, this is the error:

ubuntu@ubuntuLocal:/etc/apt/sources.list.d$ sudo pgxn install plv8
INFO: best version: plv8 2.3.11
INFO: saving /tmp/tmp43TbRw/plv8-2.3.11.zip
INFO: unpacking: /tmp/tmp43TbRw/plv8-2.3.11.zip
INFO: building extension
cat: '/usr/include/postgresql/11/server/pg_config*.h': No such file or directory
/bin/sh: 1: test: -ge: unexpected operator
/bin/sh: 1: test: -lt: unexpected operator
/bin/sh: 1: test: -lt: unexpected operator
/bin/sh: 1: test: -lt: unexpected operator
/bin/sh: 1: test: -lt: unexpected operator
Makefile.shared:183: /usr/lib/postgresql/11/lib/pgxs/src/makefiles/pgxs.mk: No s                                                                uch file or directory
make: *** No rule to make target '/usr/lib/postgresql/11/lib/pgxs/src/makefiles/                                                                pgxs.mk'.  Stop.
ERROR: command returned 2: make PG_CONFIG=/usr/bin/pg_config all


texts: Master Database Occasionally Gets Corrupted

texts:
I've been experiencing an issue with our Windows Update server where the master database appears to occasionally get corrupted at random times. We're running a Windows Internal Database (which seems to be equivalent to SQL Server 2012).

It's happened at least three times since migrating to a Windows 2012 R2 Server from a Windows 2008 Server (both are VMs). Every time it's happened it results in me restoring the master database by swapping out the data and log files with the master template files, then re-attaching the SUSDB.

What typically happens is that we'll see that the Windows Internal Database service is not running, which typically sparks the following troubleshooting steps:

Try to restart the service, which doesn't work.
Try to open the SQL Server instance via SSMS, which doesn't work (it sits and spins for a long time).
Look in the SQL Server logs, where I'll see some or all of the following messages:

  Error: 50000, Severity: 16, State: 1.

  Detected an unauthorized object in the schema

  Database verification script failed for database 'master'.

  Error: 3605, Severity: 16, State: 1.

  Schema verification failed for database 'master'.

I've searched for these error codes and troubleshot, but the only thing that seems to work is completely replacing the master database. I hesitate to say I've tried everything, but I'm pretty sure I've tried everything.

Some of the things that I've tried/pondered:

I'm the only DBA on the server, so files are not being moved around.
I've made sure that the "compress contents to save disk space" has not be checked in the advanced attributes in the data folder.
Because of the size of the SUSDB, we can't switch to using SQL Server Express instead of WID.

Questions

So my questions are:

Does anyone have any insight into how I can prevent this from happening again?
Or possibly someone has a better solution for when it does occur?

As I said, it appears to happen at random and my fix is not exactly perfect. Please let me know if I should clarify anything.

Thanks!