texts: Will a composite Primary Key use the 15/16 page fill factor or the 1/2?

texts:
I understand that if a PK is a consecutive number, it will fill a page as its maximum which is a factor of 15/16. If it is a random PK the page fill factor will be of 1/2.

I was wondering if my PK was a composite key.

For example, for a blog post table my PK would be (author_id, post_id). What fill factor would that use ?

Making a PK of this form lets me retrieve all the posts of a given author faster. But I/O wise I was wondering if this was a good shot.

Question is for MySQL DB using InnoDB storage engine.


texts: Run a script as a sheduled job using sql developer scheduler GUI

texts:
I have a script

Alter Trigger  TEST.LIVE_DELETE Disable;
Alter Trigger  TEST.LIVE_UPDATE Disable;

----------Running the comparison script----------------

var scanid number;
SET SERVEROUTPUT ON
DECLARE
consistent   BOOLEAN;
scan_info    DBMS_COMPARISON.COMPARISON_TYPE;
BEGIN
consistent := DBMS_COMPARISON.COMPARE(
           comparison_name => 'COMPARE_TEST',
           scan_info       => scan_info,
           perform_row_dif => TRUE);
:scanid:=scan_info.scan_id;
DBMS_OUTPUT.PUT_LINE('Scan ID: '||scan_info.scan_id);
IF consistent=TRUE THEN
  DBMS_OUTPUT.PUT_LINE('No differences were found.');
ELSE
  DBMS_OUTPUT.PUT_LINE('Differences were found.');
END IF;
END;
/
COLUMN OWNER HEADING 'Comparison Owner' FORMAT A16
COLUMN COMPARISON_NAME HEADING 'Comparison Name' FORMAT A20
COLUMN SCHEMA_NAME HEADING 'Schema Name' FORMAT A11
COLUMN OBJECT_NAME HEADING 'Object Name' FORMAT A11
COLUMN CURRENT_DIF_COUNT HEADING 'Differences' FORMAT 9999999
SELECT c.OWNER,
c.COMPARISON_NAME,
c.SCHEMA_NAME,
c.OBJECT_NAME,
s.CURRENT_DIF_COUNT
FROM DBA_COMPARISON c, DBA_COMPARISON_SCAN_SUMMARY s
WHERE c.COMPARISON_NAME = s.COMPARISON_NAME AND
c.OWNER           = s.OWNER AND
s.SCAN_ID         = :scanid;
SET SERVEROUTPUT ON
DECLARE
scan_info    DBMS_COMPARISON.COMPARISON_TYPE;
BEGIN
DBMS_COMPARISON.CONVERGE(
  comparison_name  => 'COMPARE_TEST',
  scan_id          => :scanid,
  scan_info        => scan_info,
  converge_options => DBMS_COMPARISON.CMP_CONVERGE_REMOTE_WINS);
DBMS_OUTPUT.PUT_LINE('Local Rows Merged: '||scan_info.loc_rows_merged);
DBMS_OUTPUT.PUT_LINE('Remote Rows Merged: '||scan_info.rmt_rows_merged);
DBMS_OUTPUT.PUT_LINE('Local Rows Deleted: '||scan_info.loc_rows_deleted);
DBMS_OUTPUT.PUT_LINE('Remote Rows Deleted: '||scan_info.rmt_rows_deleted);
END;
/

--------------End Of Comparison Script-----------------

----------------Enable the Triggers--------------------

Alter Trigger  TEST.LIVE_DELETE enable;
Alter Trigger  TEST.LIVE_UPDATE enable;

----------------END OF SCRIPT--------------------------

I am trying to set this script as a repeated job.
Using SQL Developer>Scheduler>New Job (Wizard)

I get the below error,
"ORA-06550: line 1, column 754:
PLS-00103: Encountered the symbol "ALTER" when expecting one of the following:

( begin case declare exit for goto if loop mod null pragma
   raise return select update while with
     <<
   continue close current delete fetch lock insert open rollback
   savepoint set sql execute commit forall merge pipe purge
"
Is there a better way to do this or fix this!


texts: How to go about testing your network for an AlwaysOn implementation

texts:
So i'm taking my first steps into deploying an AlwaysOn solution at my company. I've set it up in a test environment so know the setup/config steps required, however what we don't know is how our systems are going to perform once AlwaysOn has been switched on in production, as we haven't done any performance testing with production-like load.

We have about 46 databases that need to be synchronized across to DR - totalling around 1 TB in size. This is a data warehousing system and is used for reporting during the day (all reads) and batch loads at night (all writes).

We are not using AGs to provide HA - this is for DR across two data centers using asynch mode, but with added bonus of up to date read-only data on DR for the analytics team to do their ad hoc analysis without interfering with regular business reporting. In a DR scenario failover would be manual.

Reading posts about AG performance, I know there are things we could tweak such as max worker threads and using a separate NIC for mirror traffic to improve performance - so we have options should we run into issues. I haven't really ever looked into network latency/bandwidth performance metrics before so specifics would be helpful.

How should we run an Availability Groups network test, and what network latency or bandwidth numbers should we watch during the test?


texts:
I am trying to build a procedure that i would use to check my data format before processing it to the net stop and i need this procedure to check the format of my file. So what i need is, my database to use a specific folder and then check the data thats loaded into the table.
How can I get it work, any ideas, suggestions?


texts: What is the difference between node types and atomic types in XML

texts:
Im Studying TSQL and I came to the chapter regarding XML, and here a question that I cannot find an answer: "What is the difference between node types and atomic types"?

Thank you!


texts: How should I configure a table to optimize for frequent inserts and deletes?

texts:
I know about making the clustered index a steadily increment field, like a datetime field. So that will give me fast inserts.

However, the deletes will be based on a consumer of the "queue". So the table looks roughly like this.

 CREATE TABLE wk.WorkQueue
    ( TimePosted datetime2    NOT NULL
      CONSTRAINT DF_WorkQueue_TimePosted DEFAULT( sysdatetime())
    , Producer   varchar(64)  NOT NULL
    , Consumer   varchar(64)  NOT NULL
    , WorkData   varchar(800)     NULL -- simple model
    , CONSTRAINT PK_WorkQueue PRIMARY KEY CLUSTERED ( TimePosted ASC ) ON [INDEX]
    , INDEX IX_WorkQueue_by_QueuedForConsumer NONCLUSTERED
        ( Consumer   ASC
        , TimePosted ASC
        )
      ON [INDEX]
     )
     ON [PRIMARY]
     ;

What's the best way to structure the table to frequently delete data from the table.

I'm usually doing this to pull from the table:

DELETE wk.WorkQueue
OUTPUT deleted.TimePosted, deleted.Producer, deleted.WorkData
  INTO @tWork ( TimePosted, Producer, WorkData )
  FROM wk.WorkQueue WITH (INDEX(IX_WorkQueue_by_QueuedForConsumer))
 WHERE Consumer = @WorkerName
;

Is there a good way to set up the second index, to make quick deletes? Should I use WITH(ROWLOCK)?


texts: Restoring tab exports to a mysql database with integrity

texts:
I have a database with a lot of data for a client that's on a shared web host. Doing a plain mysqldump is causing the job to get killed on the server, so we were looking at backing up the tables individually.

I've found that the --tab option will automatically create separate files for each table, but my question comes to restoring them while maintaining integrity. This site shows various ways to write shell scripts that loops through the list of backup files and loads them one-by-one. However I am suspicious that this may not guarantee integrity of data.

Is there a more automated way I can lock the database and load the individual tab files, to ensure that I have the database restored to the state it was backed up in?


texts: Auto increment a value using cursor in SQLServer


texts:
Most database systems can represent missing values, typically as "Null" or something similar.  But what if I want to represent different categories of missing data?

For categorical data, this is not necessarily a big problem.  Just create additional categories for each type of missing.  But this can be a more challenging problem when representing continuous data (e.g., income).  Some people will use codes like 99999, 99998, 99997 to represent missings, leaving it to the user to recognize that these values are missing and recode them as such when performing analysis.  I would like to avoid this.  Other workarounds include having a parallel table that includes a categorical variable for each continuous variable, where the categorical variable indicates missingness and category of missingness.  I would also like to avoid this.

For comparison, Stata (which, of course, is not a database management system) stores data in a single table and encodes missings as ., .a, .b, etc.  Each of these categories of missing can have a different label affixed to it indicating its category of missing.

What I would love is if a database system could accommodate missings the way that stata does.  Basically, different types of Nulls, each with their own label.  Does anything like this exist?  Is there a convenient way to simulate this functionality if it does not exist with any database or DBMS?

For context, I am a 'data scientist' (what does that even mean).  We are collecting a large amount of not-very-well-organized data and normalizing it in a database.  Some data is missing for reasons we don't know (data entry error?).  Other data is missing because it is not applicable to the person.  Sometimes it is missing because the value was implausible and we recoded it as missing.

The appropriate way to handle these missings in subsquent statistical analysis will depend upon why these variables are missing.  It is important not to throw away this information as we take disorganized flat files and convert them into our well-organized, normalized data tables.

I imagine you experts might be familiar with this problem and might have some insights into how to deal with it.  Maybe I am thinking about this problem the wrong way.


texts: SQL server: create local copy of remote table

texts:
I have read access to a remote SQL Server that contains table 'dbo.myTable'. The problem I am facing is that it is slow to read the data remotely, especially for small frequent calls.

I also have a local SQL server. What is the most simple way to have a local copy of 'dbo.myTable', that is synchronised automatically with the main one? (I don't need second precision, maybe a few minutes lag is enough)

The data will only be modified on the remote one, as locally I will only read.

Thank you


texts: Insert and Update with AES_ENCRYPT is mangling character set on double byte script

texts:
DB and fields are UTF8_unicode_ci collation - MySql v 5.6

HTML charset is utf 8

Double byte script was entered successfully prior to this issue (Chinese, Japanese, plus Cyrillic etc) when using plain non encrypted input.

But trying to use AES_ENCRYPT on input mangles all non-english script.
DB field is BLOB.

 UPDATE mytable SET bodytext=AES_ENCRYPT('任車心是，防少劇和的；子反華不就','aeskey') WHERE t_id=123

produces strings of question marks - English is fine though

I'm not sure if I should try to CONVERT the input to UTF8 first - but when I try that I get an error.
Any suggestions of what to review?


texts:
I am running a rather large database on SQL Server 2008 R2 Express. Recently I hit the 10GB limit, so the system would not allow me to create anymore objects, ect..

I have archived off a very large amount of records, around 3 quarters of the data that was there.

The database file is still around 10GB though, I was wondering if there is anything I need to do to release that free space in SQL Server.


texts:
Not sure how to explain this properly but here goes.
I a table with rows that are related to each other and I want to aggregate them based on a sort order until the value in one column has a certain value that would reset the group.
Sample table:
|cust_id|price  |timestamp       |type|
|-------|-------|-------         |----|
|1      |10     |2021-01-29 12:32|1   |
|1      |20     |2021-01-29 12:33|2   |
|1      |15     |2021-01-29 12:34|2   |
|2      |10     |2021-01-29 12:33|1   |
|1      |20     |2021-01-29 12:34|1   |
|2      |10     |2021-01-29 12:35|2   |

Wanted result:
|cust_id|price  |timestamp       |type|
|-------|-------|-------         |----|
|1      |45     |2021-01-29 12:32|1   |
|2      |20     |2021-01-29 12:33|1   |
|1      |20     |2021-01-29 12:34|1   |

So the input table would be sorted by timestamp and then grouped by cust id, the price is aggregated until the next occurrence where type is 1 which would indicate a new group. Type here is just one and two, but can in reality have a lot of different values.
I have not figured out a good way of accomplishing this so any pointers is appreciated.


texts: I see a constraint in my table thats not in INFORMATION SCHEMA?

texts:
When I expand the database "Constraints" object, I see 2 of them named DF_DocumentD_Docum_3F466844 and df_documentdistqty, but when I select to view everything from the "Information Schema.TABLE Constraints", the constraints named above dont show up, any idea why?
I scripted out these constraint and it has the following info:

ALTER TABLE [dbo].[DocumentDistribution] ADD  DEFAULT (getdate()) FOR [DocumentDistDate]
GO

and the other has:

IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[df_documentdistqty]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[DocumentDistribution] DROP CONSTRAINT [df_documentdistqty]
END


texts:
I need to create a process where anything older than six months we would write to an archive table and delete the records from the inventory table. Our inv table is huge btw lets use (x) as the number. So i need query to select anything that has an end date that is six months or older and write the SQL. Does this make sense?
Where End_Date <= dateadd(month, -6, getdate())
Since we want to go back 6 months we add -6 to today's date, which is the last parameter in the formula (getdate() get's today's date from the server).  This will yield an answer of 10/8/2020  So any end date on the table that is less than or equal to 10/8/2020 will be selected to the results .
If this doesn't make sense or you need more info let me know.


texts:
Could someone tell how to give a description on the following relationship?

One (or each?) Operator is managed by [how many?] Supervisors?
And one (or each?) Supervisor manages [how many?] Operators?

Does this relationship indicates that an Operator Employee may be managed by none Supervisor?


texts: What do these pstart and pstop values mean?

texts:
I'm attempting to tune a query and I want to be able to tell if partition pruning is happening. On one part of the query, the I'm getting a Pstart and Pstop value = "KEY":

| Id  | Operation                                 | Name                           | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
|*  5 |      INDEX RANGE SCAN                     | IX_SOMEINDEX                   |     1 |       |     1   (0)| 00:00:01 |   KEY |   KEY |

Another line of the execution plan shows a Pstart and Pstop value = "ROWID":

| Id  | Operation                                 | Name                           | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
|  10 |        TABLE ACCESS BY GLOBAL INDEX ROWID | LOOP                           |     1 |    18 |     2   (0)| 00:00:01 | ROWID | ROWID |

Basically I'm looking for definitions of what ROWID and KEY mean within the context of Pstart and Pstop.


texts: Not getting a result set in SSRS 2008r2 from an iSeries DB2 SQL stored procedure

texts:
After reading 'Defining an SQL procedure' section of DB2 UDB for iSeries SQL Programming Concepts V5R2 and 'Returning result sets from stored procedures' section of i5/OS Information Center, Version 5 Release 4, I created the following stored procedure:

CREATE PROCEDURE TESTDAT.REPORT (IN DATESTART DATE, IN DATEEND DATE)
LANGUAGE SQL READS SQL DATA
BEGIN
    DECLARE CSR1 CURSOR WITH RETURN FOR
    SELECT *
    FROM  SYSIBM.SYSDUMMY1;

    OPEN CSR1;
    RETURN;
END

Next I read 'Example 1: Calling a stored procedure that returns a single result set' section of the same i5/OS Information Center, Version 5 Release 4 and tried to call this procedure from SqlDbx client (via ODBC) or SSRS report (via IBMDASQL provider) using below statement:

CALL TESTDAT.REPORT (DATE('11/01/2013'), DATE('11/03/2013'));

It executed successfully, but did not return the resultset.
Executing this thru iSeries navigator successfully returns 2 statements - select and declare cursor.
Calling

SELECT * FROM  SYSIBM.SYSDUMMY1;

returns IBMREQD column with 1 row of 'Y'.
What am I missing from the documents above?

Update:

Generated SQL for this procedure includes other options besides already discussed:

SPECIFIC TESTDAT.REPORT
NOT DETERMINISTIC
READS SQL DATA
CALLED ON NULL INPUT
SET OPTION  ALWBLK = *ALLREAD ,
ALWCPYDTA = *OPTIMIZE ,
COMMIT = *NONE ,
DECRESULT = (31, 31, 00) ,
DFTRDBCOL = *NONE ,
DYNDFTCOL = *NO ,
DYNUSRPRF = *USER ,
SRTSEQ = *HEX

Can any of them affect returning of resultset to SSRS?


texts: "SELECT TOP N" returns the wrong results

texts:
Supposing I have a table foo similar to:

ident int, primaryKey, auto_increment
someNumber int
statusId int
...

where the someNumber column is, except for a few special cases, equal to ident.  (in those special cases, someNumber is always equal to a previous ident).

I'm seeing problems which give the appearance that a query such as SELECT TOP 10 ident, someNumber FROM foo WHERE statusId=@desiredStatusId ORDER BY someNumber will return the wrong 10 records.  The records all have the correct statusId, but randomly there is one whose someNumber is way off.  For eaxmple:

ident        someNumber
-------      ----------
1234567      1234567
1234700      1234700
1234568      1234568
1234569      1234569
1234570      1234570
...          ...

What could be causing this query to intermittently return the wrong results?


texts: Can inserting new rows make the RDB not use an index?

texts:
Say I have a table with 10000 records. I then delete them and create them again. This is done with "normal" sql, row by row (not some sort of bulk insert (BCP in sqlserver lingua)).

Does the RDB(Oracle in my case) still consider the indices on said table usable, or has it noticed many rows changed and stops using it?

(If the example above seems wierd here is a similar: Say I have a table with 10000 records with nice evenly divided data. I then insert 10000 new records with similarly evenly divided data.)


texts:
I have 1 big table with 25 milions of rows and i have to merge data of another table with 500,000 rows into the big table. the rows of both tables have the same columns and i have to check if the key columns exist then i update the rest of the columns , otherwise i insert the new row.
i would like to know which is the best way to do the update:
1) update the columns according to the key columns
2) delete the old column and insert the new one instead.


texts:
Using DBeaver, I'm trying to connect to my AWS ec2 instance where I've installed a MariaDB database.
I've created a new connection as:

Connection: AWS / Maria DB
Server Host: localhost   (because I'm using ssh to connect)
Database, Username and Password: the right ones
Port: 3306

SSH tunnel on the connection:

Use SSH Tunnel: checked
Host/IP, Port and User: as suggested by the AWS documentation
Auth: Public Key
Private key: file .pem (the one I'm also using to connect via ssh from command line)

The connection works great at the beginning.
After some minutes of inactivity if I open a table the connection stucks and I need to invalidate/reconnect to fetch the data.
This process sometimes is done by DBeaver, after 15 seconds of not fetching anything, but sometimes even if I wait 2 minutes it keeps running but fetching nothing.
Is there something I'm missing in my configuration?
Or some configuration on the server maybe.. I don't know..
Have you ever had this problem?


texts:
Do I have an issue with my syntax?  I created this trigger as sometimes the sales personnel forget to input the sale date (I Know it should be automatically captured which is an update in progress) but we are still seeing a few rows in the table with a null value for saledate  Is this accurate syntax to update the field saledate with GetDate() when saledate is null?  And I want to run this on Insert Or Update

create trigger updatefield
on salerecords
for insert, update
as
begin
  update salerecords
  set saledate = getdate()
  where saledate Is null
end


texts: is there any method should i use for trigger update?

texts:
I try to modify the syntax of SQL trigger to mysql trigger
[check 32:01 for reference] ]1

someone suggested me to use this syntax in mysql, however I got error in mysql.

DELIMITER //
CREATE TRIGGER ComputeTotaluPDATE
BEFORE UPDATE ON tblcart
FOR EACH ROW
BEGIN
    IF NEW.qty <= 0 THEN
        SET NEW.status = 'Cancelled';
    END IF;
END
DELIMITER ;

By the way this function is working to me.

CREATE
    TRIGGER  ComputeTotaliNSERT
 BEFORE INSERT ON tblcart FOR EACH ROW
    SET NEW.total = NEW.price * NEW.qty;

CREATE
    TRIGGER  ComputeTotaluPDATE
 BEFORE UPDATE ON tblcart FOR EACH ROW
    SET NEW.total = (NEW.price * NEW.qty) - NEW.discount;


texts: how to change mysql table auto_increment primary key

texts: Now i have a table with Auto_increment id as Pri key, and exchange_id as key, How could I change the primary key to field [ticker]..?


texts: Best Distkey and Sortkey for the tables in Redshift

texts:
I have the following tables:

orders:

id => primary key
customer_id =>
store_id =>
order_date => datetime

order_items:

id => primary key
order_id => FK

The query I usually run is for customer_id or for a store  (which belongs to a customer)  for a certain datetime range.

There are many customers in the system having one to many stores doing many orders all day.

What's the best candidate for DISTKEY and SORTKEY for the above tables.

order_items are always joined with the orders table and the where usually falls on the orders table.


texts:
Is there a way to export postgres table data as json to a file? I need the output to be line by line, like:

{'id':1,'name':'David'}
{'id':2,'name':'James'}
...

EDIT: postgres version: 9.3.4


texts:
We have a web application wherein nonprofit organizations can host their own auctions, donation campaigns, manage event ticketing, etc.  We also have services for for-profit businesses, but let's ignore them for simplification.  And for nonprofits, I'll focus on donation campaigns.

In order to place a donation, a donor must have a user account with us (username, password, etc).  Credit card tokens, addresses, and donations are associated with a user ID.

We're going to expand our dashboard to provide CRM-like capabilities, including allowing organization admins to add arbitrary people.  Maybe those people have made offline donations, maybe they haven't.  These are not users within our system, although they may become users eventually (and we'll build a de-duplication tool so admins can handle those cases).

We're also going to want to be able to add some basic features to the "CRM", such as associating addresses with people (we already do this for users), adding arbitrary tags and internal notes, etc.

My question: how best to store "people entered into the CRM" vs. "actual users of our application"?

Immediately I think of creating a people table and updating code across our existing products to interface with a user_id or person_id, but it feels like there's some redundancy there.  Example:

USERS
id | username | email | password | first_name | last_name | (etc)

PEOPLE
id | organization_id | first_name | last_name | (etc)

DONATIONS
id | organization_id | user_id | person_id | placed_offline | amount | (etc)

ADDRESSES
id | user_id | person_id | country | city | (etc)

Another idea would be to change our users table so that it only contains authentication-related information, and store all other data in people.  We'd have to change a bunch of queries around the site, but it's doable.  Something like this:

USERS
id | username | password | salt | registered_on | (etc)

PEOPLE
id | organization_id | email | first_name | last_name

USER_PEOPLE
user_id | person_id

DONATIONS
id | organization_id | person_id | placed_offline | amount | stripe_charge_id | (etc)

ADDRESSES
id | person_id | country | city | (etc)

INTERNAL_PERSON_NOTES
id | organization_id | person_id | note | posted_by | (etc)

PERSON_TAGS
id | person_id | tag_id | (etc)

TAGS
id | organization_id | name | (etc)

Note that people need to be organization-specific, since we wouldn't want one organization's custom-added people appearing for another organization.  For people records tied to a user I'd set an organization_id of 0.

I like the second idea, but I think it has some kinks that need working out, likely some that I'm unaware of.  I'm wondering what an actual DBA would think of doing in this situation / if there's a best practice.


texts: Postgres group and aggregate(sum) JSONb arrays and non JSONb properties

texts:
Version
PostgreSQL 11.7 on x86_64-pc-linux-gnu, compiled by gcc, a 224fe214a p 3971489d3e, 64-bit
I am trying to build a query which (distinctly) groups values from JSONb columns, and also sums values from others, and non JSONb columns.
Table Definition (I have removed other columns that are not relevant)
id varchar(255) NOT NULL,
casualties jsonb NOT NULL,
involved_parties jsonb NULL,
tags jsonb NULL,
reported_at int8 NULL,

casualities
Each row has a single object which represents the count of casualties across categories, for the incident.
{"police_deaths": 0, "civilian_deaths": 0, "criminal_deaths": 0, "military_deaths": 0, "police_injuries": 0, "emergency_deaths": 0, "civilian_injuries": 1, "criminal_injuries": 1, "military_injuries": 0, "emergency_injuries": 0}

involved_parties
This is an array of objects.  Each row shows zero or more involved parties (who was involved in the incident).  The data looks a little misleading at first as for each entry within the array the involved party/incident relationship has an ID.  This doesnt really give us anything and I dont need this, but it is in the data currently.
[
  {"id": "2a0fd9dc-40bd-40dc-88ce-bc819fe9cdd8", "type": "group", "group": {"id": "6d342bfc-72c4-4588-ab95-1b3bdfb4881a", "name": "Naxals"}, "involvement": "Actor"},
  {"id": "dafc4726-3d3d-40cb-bbaf-63fa57250b44", "type": "group", "group": {"id": "18c6d3f6-c3eb-45db-9a02-26606f85d7eb", "name": "Indian Security Forces"}, "involvement": "Directly Targeted"}
]

Its the group, and involvement data I am interested in here.
affected sectors
This structure is much like the involved parties.
[
  {"id": "fcb952ef-3139-4fe7-ba15-7d800bdc60ae", "sector": {"id": "668d330e-aee5-4291-be98-df9c32b5b420", "name": "Military"}},
  {"id": "d1b71bae-29ac-48a2-ab41-a6979d720171", "sector": {"id": "550a4aa0-6d6f-4be2-ba33-f35d159ee686", "name": "Police/Law"}}
]

Here it is the sector I am interested in.
reported_at
This is the epoch representation of when the incident was reported by our analysts.
Desired Output
For the records in the query I want a single row.
The single row has the following columns:
incident_count,
casualties,
involved_parties,
tags,
min_reported_at,
max_reported_at

The incident count should be just that, the number of rows formally represented.
The casualties object always has the same properties in the JSON and I want to sum them.  So, there will be a single object with the sum of all police_deaths, civilian_deaths etc.
For the involved parties and affected sectors; each should have a single array containing a unique set of the parties/sectors from the rows
Min/Max reported at should be the min/max over all rows.
I tried from this starting point:
select
    jsonb_agg(incidents.affected_sectors) as affected_sectors,
    jsonb_agg(incidents.involved_parties) as involved_parties
from incidents

But this was painfully slow (9 seconds). So I then tried by expanding each object out to a row, then trying to collapse it back but got lost off and bad results.
I'd appreciate any pointers here
Thanks,
Mark.


texts: MySQL Lock Wait Time Out Exceeded upon Delete

texts:
I have a Delete Statement which fails with Below Exception

com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Lock wait timeout exceeded; try restarting transaction

It waits for 50 Seconds for Lock and gives Up, I wanted to know which all SQL Statements can lock a table.

LOCK WAIT 6017 lock struct(s), heap size 376, 137376 row lock(s), undo log entries 22011
MySQL thread id 8242, OS thread handle 0x2b019e749700, query id 13811010 10.103.89.37 administrator updating
DELETE from CONFIGSTOREQASTAGINGREL.EVENT_DETAILS where UPDATE_DATE < 20191013 AND COMPONENT_NAME = 'health'
------- TRX HAS BEEN WAITING 25 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 194 page no 9414 n bits 17 index `GEN_CLUST_INDEX` of table `CONFIGSTOREQASTAGINGREL`.`EVENT_DETAILS` trx id 38176269 lock_mode X waiting
Record lock, heap no 17 PHYSICAL RECORD: n_fields 14; compact format; info bits 0
 0: len=6; bufptr=0x2b01d5f29030; hex= 0000001ef5fb; asc       ;;
 1: len=6; bufptr=0x2b01d5f29036; hex= 0000024674c5; asc    Ft ;;
 2: len=7; bufptr=0x2b01d5f2903c; hex= af000004900110; asc        ;;
 3: len=30; bufptr=0x2b01d5f29043; hex= 333532613463313736333863613932393935336262653631646135306137; asc 352a4c17638ca929953bbe61da50a7; (total 32 bytes);
 4: len=30; bufptr=0x2b01d5f29063; hex= 316663346166393133353164633731393062363833393134386335326535; asc 1fc4af91351dc7190b6839148c52e5; (total 32 bytes);
 5: len=6; bufptr=0x2b01d5f29083; hex= 6865616c7468; asc health;;
 6: len=30; bufptr=0x2b01d5f29089; hex= 36436c74773144627644656948474472756e774d4f362020202020202020; asc 6Cltw1DbvDeiHGDrunwMO6        ; (total 32 bytes);
 7: len=4; bufptr=0x2b01d5f290a9; hex= 81341726; asc  4 &;;
 8: len=4; bufptr=0x2b01d5f290ad; hex= 800003aa; asc     ;;
 9: len=16; bufptr=0x2b01d5f290b1; hex= 4f494d2d534552564943452020202020; asc OIM-SERVICE     ;;
 10: len=4; bufptr=0x2b01d5f290c1; hex= 80000000; asc     ;;
 11: len=0; bufptr=0x2b01d5f290c5; hex= ; asc ;;
 12: len=30; bufptr=0x2b01d5f290c5; hex= 436f6e6e656374697669747920746f20536563757265204167656e742069; asc Connectivity to Secure Agent i; (total 168 bytes);
 13: len=4; bufptr=0x2b01d5f2916d; hex= 5da441f8; asc ] A ;;

I wanted to know what is Record Lock and when does that gets locked.I would very thankful if someone explains what is the meaning of above statements i got from SHOW INNODB ENGINE STATUS


texts: When exactly does pg_stat_all_tables.last_autovacuum get updated?

texts:
I'm looking into issues with our autovacuum settings and trying to understand last_autoanalyze and last_autovacuum from the pg_stat_all_tables.

I understand autovacuum to work in an incremental way, compacting and cleaning up dead tuples in batches, then sleeping for a moment, etc. So what does last_autovacuum mean? Is it possible that autovacuum could be mostly working but that that timestamp might never get updated (e.g. because a tuple could not be removed)?

Many of the timestamps I see for our tables are old, even though I'm fairly sure I observed a running autovacuum thread on some of the tables.

EDIT:
If I'm unclear, my question is: does last_autovacuum mean...

the time at which an incremental autovacuum process last finished up a chunk of work on this table and slept
the time an autovacuum process worked its way to the very end of a table
the above, but only if there were no tuples that could not be removed
something else


texts: How to check for mutiple rows with same data?

texts:
Can anyone advise how do I check multiple rows with same data?

E.g.

I have 2 rows with following values:

Cola         Colb  Colc
TYPE_APPLE   123   abc
Colb         Colb  Colc
TYPE_APPLE   123   abc

I want to write an SQL query which will not select data of multiple rows which contain the same data as above. How should I write the query?


texts: How can I dynamically back up all SSAS databases on a given instance?

texts:
I want to dynamically back up all the databases on a given SSAS instance using a SQL Agent job (which would most likely involve executing an SSIS package). It is imperative that this is a dynamic process - if users add databases or cubes, I want to set up a job one time that can automatically detect all existing SSAS metadata.

Unfortunately, I don't see anything out there that tells me how I can automatically and dynamically back up all of the databases on an SSAS instance in a clean way. By "clean", I mean:

Using the DatabaseId in the backup command, not the DatabaseName. Sometimes there can be a difference between a DatabaseName and a DatabaseId, and if there is a difference, the backup will fail if the DatabaseName is used in place of the DatabaseId. Just querying the Catalogs schema  alone won't give me the DatabaseId.
Avoiding the need to create a linked server every time I come across a new instance to back up.


texts: Replication slave re-reading WAL on restart

texts:
I have a standard streaming replication setup going with PostgreSQL 9.1. It works fine. However, if I stop the slave cleanly (to move it or perform other maintenance) and restart it a short time later, it insists on re-reading all the WAL files it has generated before connecting to the master, which is a lot of them and takes hours. I would expect it instead to have noted the last WAL position it got from the master and restart from there.

I feel like I'm missing something here. Is this expected behavior?

My slave's config has the usual suspects:

listen_addresses = '*'
wal_level = hot_standby
max_wal_senders = 10
wal_keep_segments = 24
hot_standby = on


texts: Does pg-pool-II support two phase commit?

texts: When pg-pool II is used with sharding, what happens if I want to do a transaction which involves two nodes ? Does pgpool support two phase commit ?


texts: When is it ok to use an Index Hint on a query in production code?

texts:
I've recently been working with decently sized tables (between 100s of million of rows to 10s of billions), and have ran into a few cases where I was forced into a corner to use query hints, which I used to think were bad practice but since learned they have their place (mostly the ForceSeek and ForceScan hints).

I've finally ran into a query where no matter what I do, I can't seem to get it to use the better index naturally (which is the primary key clustered index).

The query is pretty simple:

SELECT A.Field1, B.Field2
FROM TableA AS A WITH (FORCESEEK)
INNER JOIN TableB AS B WITH (FORCESEEK)
    ON A.PrimaryKeyClusteredIndexedField = B.PrimaryKeyClusteredIndexedField
WHERE B.NonClusteredIndexedField = SomeBooleanValue -- SomeBooleanValue = 1 (True)

I run this query across many databases (about 500 - this is just how our schema is designed) and in the relatively smaller databases it does default to using the Primary Key Clustered Index in the query plan. In a couple one-off much larger databases it instead tries to do an Index Seek on the Nonclustered Index field "B.NonClusteredIndexedField" which also then requires a Key Lookup.

Not sure why it would pick the Nonclustered Index over the Primary Key Clustered Index in this case, but the Key Lookup then severely impacts the performance of the query. Using an Index Hint on the TableB so that the code becomes INNER JOIN TableB AS B WITH (FORCESEEK, INDEX(IX_PrimaryKeyClusteredIndex)) fixes this problem but feels dirty to me. Is it ok to use in one-off cases like this, especially when it's an Index Hint to the Primary Key Clustered Index which is unlikely to change?


texts:
I have a very simple requirement. I have an application where users log in, add/remove/view friends, and can send/receive (also view the message you sent and received) a message (message is more like an email, not a real time chat) from a friend. When they receive the message, the application needs to mark the message as 'read'. The messages must also come ordered by newest to oldest.

My thoughts:

KINDS:

USER

username <String> (ID)
password <String>
friends <Comma Separated String of usernames> (will contain a max of 100 friends).

MESSAGE

msg_id <String concating [from+to+timestamp]> (ID)
from <String> (indexed)
to <String> (indexed)
timestamp (indexed)
msg <String>
hasRead <boolean>

1) User would login using their username and password

2) User could get friends by getting their USER entity based on their username

3) User could add/remove friends by getting the entity of user1 and user2 and either adding or removing friend via transaction to make sure they are consistent.

4) User could get all the message they have sent by using indexing the the 'from' attribute (limit of 10 message per request). The same could be done to view all the messages they have received by using the 'to' attribute. When the message has been seen for the first time I would go to the message (1 get) and update the entity (1 write + (4 writes x 3) = 13 writes to update the entity).

My major concern - If a user gets 10 messages, this will require 10 get requests plus if all 10 messages are new I will need to update each entity and if all of them are new that is (10 x 14) 140 writes. Then if I get another 10 message for this user the same process and this could all add up very quickly.

I then thought of creating an entity to store all the sent/received messages in a string for a user inside of a single entity:

MESSAGE
user_entitynum1 <String> (ID)
messages <String looking like this: msg1_fromuser2_unread_timestamp, msg2_touser8_read_timestamp, msg3_touser5_unread_timestamp, etc>

This way I could store all the message (under 1mb) inside of this one entity, but I would have to keep track of which entity each user is at (if the user exceeds the 1mb limit I will have to create a new entity).  This proved to also not be too efficient because if I send a message to perform 2 gets to see which message entity I am currently at and which message entity they are currently at. From there I must now use another 2 reads to get those entities and another 4 writes to update them both (considering I do not need to create another entity if it is full).

I was looking for any ideas to accomplish what I need in a more efficient way. I have nothing implemented yet so I  am open to ANY ideas. My main concern is for this to be efficient,

Cheers!


texts:
I'm keeping track of teams and their members. Each team has one leader, one contact person, and any number of players, and each of them has a unique email address. The problem is that one physical person can play any number of the roles, and the roles need unique emails. So for example, Alice is a leader (with email alice.leader@game.com), Bob is the contact (bob.contact@game.com), but Bob is also one of the players (bob.player@game.com), along with Charlie (charlie.player@game.com). Each person can be a part of only a single team.

I already have the teams and people in two tables:

+-----+
|teams|
+-----+
|id   |
|name |
|score|
| ... |
+-----+

+-----+
| ppl |
+-----+
|id   |
|name |
| ... |
+-----+

I would like to store the roles in a database. One thing I came up with is three tables: team-leader (1:1), team-contact (1:1), team-player (1:many), with each of them having teamID, personID, and email address. The problem is that you can enter duplicate emails for people appearing in two tables.

Other idea is to have one table with all the roles. This solves the email problem, but I don't know how to restrict, for a single team, one leader/contact, with any number of players.

Does anyone have ideas of constraints or a redesign that would solve this?


texts:
I have installed msyql and mariadb.
When checking for the versions, everything I see is the version of mariadb.
How can I know the version of mysql itself?
mysql -V

mysql  Ver 15.1 Distrib 10.3.23-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2

and
show variables like '%version%';

gives
+-----------------------------------+------------------------------------------+
| Variable_name                     | Value                                    |
+-----------------------------------+------------------------------------------+
| in_predicate_conversion_threshold | 1000                                     |
| innodb_version                    | 10.3.23                                  |
| protocol_version                  | 10                                       |
| slave_type_conversions            |                                          |
| system_versioning_alter_history   | ERROR                                    |
| system_versioning_asof            | DEFAULT                                  |
| version                           | 10.3.23-MariaDB-1:10.3.23+maria~bionic   |
| version_comment                   | mariadb.org binary distribution          |
| version_compile_machine           | x86_64                                   |
| version_compile_os                | debian-linux-gnu                         |
| version_malloc_library            | system                                   |
| version_source_revision           | 607467bd63db2c6ca64610eb9f4e703711f4dfc6 |
| version_ssl_library               | OpenSSL 1.1.1f  31 Mar 2020              |
| wsrep_patch_version               | wsrep_25.24                              |
+-----------------------------------+------------------------------------------+

Maximum version of mysql by now is 8.0.21.
None of them are mysql version. How to find this?


texts: Imposing column level security on a view for different user

texts:
Possible Duplicate:
  Column level security

I have a view which can be viewed by multiple users, and I would like to restrict some columns to specific users.

e.g I have a view with fields A, B... F, and I have users P, Q, R, S. Here's what I am trying to implement:

when user P browses the view, he will see data in A, B, C and F, and columns D and E as NULL
when user Q browses the view, he will see data in all fields
when user R browses the view, he will see data in A, D, E and F, and columns B and C as NULL


texts: Generate report by cross join between two tables

texts:
I wanted to generate report between two tables by using CROSS JOIN.
Below shows the details of table.
Table 1: kod_jawatan

jawatan_id
jawatan_kod
jawatan_nama

Table 2: tpermohonan

mohon_id
pemohon_id
mohon_tarikh
mohon_tarikhluput

Here's the code that I've tried but it didn't work.
SELECT jawatan_nama, MONTHNAME(mohon_tarikh) AS mname,
       count(jawatan_id) AS total
FROM kod_jawatan
CROSS JOIN tpermohonan
WHERE year(mohon_tarikh) = '2019'
GROUP BY MONTH(mohon_tarikh)

UNION ALL

SELECT 'Jumlah',
       count(jawatan_id) AS total
FROM tpermohonan
CROSS JOIN kod_jawatan
WHERE year(mohon_tarikh) = '2019'

This is the output that I want:

Bil
Jawatan
Jan
Feb
March
April
May
June
July
August
September
Oct
Nov
Dec
Total

How do i get the output I want? Much appreciated for your helps.


texts:
I am designing a database structure for a mailing list.

The data I need to record is the mail recipient data:

Since the mail recipient data is not predefined, it is causing a problem.

For example, some of the recipients have name, mail address, phone number, while others have name, birth date, mail address, gender, etc...

So my design is like this:

Table Receiver:
PK: ReceiverID
    AttributeID
    DataID

Table Attribute:
PK: AttributeID
    AttributeName

Table Data:
PK: DataID
    DataContent

An example of one recipient is:

Receiver: 1 1 1
Attribute: 1 mailAddress
Data:1 test@test.com

Receiver: 1 2 2
Attribute: 2 Name
Data:2 Tony

This is causing a problem because I want to check the data field type and length.  What is a better database design?

Sorry about the poor description, I will clarify it if you are confused.


texts:
The default FILLFACTOR is 100 and I have been told at times to reduce it to 80 or lower so that page splits will be reduced due to the empty space on each page.

I don't understand this--if a new index is created on a table with a default FILLFACTOR of 80, 20% space of each page is left empty.  So assume a row needs to use 10% of a page, so when it's full it can hold 10 records.  With a FILLFACTOR of 80, now it can only hold 8 records.  So when you add the 9th record, will there be a  page split?  With 100% FILLFACTOR, a page split will only happen on the 11th record, so it seems that a lower fill factor makes it worse.


texts:
I'm using mariadb and have error

ERROR 1226 (42000): User 'root' has exceeded the 'max_user_connections' resource (current value: -1)

I'm set max_connection to 1000 and not set max_user_connections in Global and user table.

How can I fix that.


texts:
I'm studying about db by doing some modeling exercises. But I've two doubts in a exercise.

In the exercise:

  A Event can have only one organizer, and the organizer is a user, and a user can organize and participate in many events.

So this is a one to many relationship between organizer and event. My doubt is about the need to create a third table for the relation, because it's just necessary to have a third table in a many to many relationship, right?

But for example, in this case the event organizer has some attributes that don't belong to the user like "details", so  we need to create a third table for the event organizer, but this table is not a relationship table it's just a normal entity table right?

But to do the relation, that a user, that is an organizer can organize multiple events and an event is organized by a single organizer, that is a user, how should we do this? Is the below correct? Because the "event_organizer" table seems a relationship table but maybe it should be a entity table.

The other doubt is that an event can have a maximum of 3 categories, and one category can have many events. So it is a many to many relationship? Or because an event can have a maximum of 3 categories is not and it needed a different approach?


texts: Which option is better to search in two tables in varchar columns?

texts:
I have a performance problem with searching in varchar columns in SQL Server 2016. Considering these tables have plenty of rows, for example 60000 rows each one.

Tables

CREATE TABLE Persons (
PersonID int,
LastName varchar(255),
FirstName varchar(255),
CityID int)

CREATE TABLE Cities(
CityID int,
CityName varchar(255))

Code

I have a problem like this:

SELECT p.PersonID, p.FirstName , c.CityID,c.CityName  FROM PERSONS p
JOIN CITIES c ON c.CityID = p.CityID
WHERE
     p.FirstName like '%'+@search+'%'
  OR p.LastName  like '%'+@search+'%'
  OR c.CityName  like '%'+@search+'%'

Possible Solutions in my mind:

Creating a full text index for each table.
Creating Columnstore indexes.
Replicating the columns to another table and searching there.

I've searched in books like: "SQL Server 2012 Query Peformance Tunning" and "Expert Peformance Indexing for SQL Server 2012". And of course, a lot of sites and blogs on the internet.I would appreciate another suggestions to solve this issue.


texts: Update first, if not then insert (mysql)

texts:
I want to do something like insert on duplicate key update but reverse i.e. first it should try to update, and if it doesn't exist, then it should insert.

Why? Because I am running a large number of these queries and most of them are actually update queries and not insert queries. So if I use insert on duplicate key update it takes longer because it's actually 2 queries. So if I can somehow do the reverse it will be ideal.

Any suggestions? If it's not possible, then how do I make this more efficient. Thoughts?


texts: Return ALL rows based on a matching date also matching a specific value in another column

texts:
I'm using SQL Server 2014, exact version: 12.0.5579.0.

I've tried a subquery under a SELECT and WHERE statement and it brings back false results with rows of any row with a 1/1/2019 date. It just takes those dates unattached to its row ID and type and applies it to the outer query.

Example query:

SELECT A.ID, A.date1, A.type
FROM table1 A
WHERE A.ID IN (SELECT B.ID FROM table1 B WHERE B.type='blue')
AND A.date1 IN (SELECT B.date1 FROM table1 B WHERE B.type='blue')
AND type <> 'blue'

Example table:

I just want the other rows that match the same date and ID when there's a blue type.

Correct result would be:

Incorrect result:

It falsely included a row with ID 102 since 102 also has a row with the same date (1/1/2019) as the others, but not blue. If 102 had a blue type on 1/1/2019 then the date could match the green type row returned, but it doesn't.

What sort of subquery should I be doing? Seems simple at first but I've gotten myself into a mess! Can't figure out how to return row values that match values in two different columns together.


texts: How can I write to replicated environments without impacting production?

texts:
Are there any methods (mirroring, snapshot replication, change tracking, etc...) available in SQL Server 2k8 which would allow me to replicate production data to several individual development databases AND allow separate data to be written to each of those dev db?

In other words: Once a full copy of data is replicated to a dev database, replication would be turned off so new data can then be written to the dev database without impacting the production database.

Thanks in advance


texts:
I want to use "MaxID" as part of my join condition, but it keeps throwing an error:

  ERROR:  syntax error at or near "["
  LINE 29:   AND t1.["MaxID"]=t2.id

This is my query:

with MaxIDs as
(
 select max(id) as "MaxID"
 ,elID
 from valsum
 group by elID
)
select
t1.elID
,es.eNum
,es.des
,es.d1
,case
  when es.st = 1 then 'Pink'
  when es.st = 2 then 'Red'
  when es.st = 3 then 'Blue'
  ELSE es.st::text
END AS st
,t1.price
from MaxIDs t1
INNER JOIN etime es
ON t1.elID=es.elID
join valsum t2
ON t1.elID=t2.elID
AND t1.MaxID=t2.id
WHERE CAST(es.d1 As Date) BETWEEN '01/01/2016' AND '12/31/2016'
AND es.st IN (3)
ORDER BY CAST(es.d1 As Date) DESC

I also tried to use the alias w/o the brackets, but the same error was thrown.  How can I join on this field?

With no brackets and no quotes this is the error:

AND t1.MaxID=t2.id

  ERROR:  column t1.maxid does not exist
  LINE 29:   AND t1.MaxID=t2.id


texts: AWS MySQL RDS instance becomes unresponsive and getting restarted automatically

texts:
We have a AWS MySQL RDS instance which is about 1.7T in size. Sometimes it becomes unresponsive and no operations can be performed.

CPU utilization, Write IOPS, read IOPS, queue depth, write throughput, write latency and read latency drops to zero.
Number of connections get piled up.
"Show engine innodb status" hangs
Lot of queries (around 25 for each) by rdsadmin which are in hang state.

SELECT count(*) from mysql.rds_replication_status WHERE action = 'reset slave' and master_host is NULL and master_port is NULL GROUP BY action_timestamp,called_by_user,action,mysql_version,master_host,master_port ORDER BY action_timestamp LIMIT 1;

SELECT NAME, VALUE FROM mysql.rds_configuration;

After sometime, instance gets rebooted automatically with following error.

MySQL restart initiated to address MySQL induced log backup issues. Note that as part of this resulution, a DB Snapshot will be performed after MySQL completes restarting.


texts: Trying to find unused stored procedures, but dm_exec_procedure_stats seems to be resetting

texts:
I need to identify stored procedures that are no longer used by our application (~2,000 stored procedures, duplicated in ~1,000 databases).

To do this, I'm querying dm_exec_procedure_stats, which initially seemed to give good results, until I saw some stored procedures that I know are being used a lot.

Subsequent investigations show that if I run the query a few times in a row, I get records dropping out.

Is this a simple indication of memory pressure causing the cache to be cleared?
Nobody is running dbcc freeproccache or dbcc freesystemcache.

SELECT database_id, object_id, OBJECT_NAME(object_id, database_id) 'Name',
    d.total_elapsed_time, d.execution_count, d.total_worker_time,
    d.total_logical_reads, d.total_logical_writes
FROM sys.dm_exec_procedure_stats AS d
WHERE p.name LIKE '%myinterestingsproc%'

If, for example, that query returns two rows for 'one' stored procedure  (because it's the same name in two db's, I consider it one stored procedure  for the purposes of utilisation), a subsequent run of the query might lose one of the rows. Numbers of the remaining rows do not reset, so it's not a system-wide stats reset.


texts:
Is IFI (Instant File Initialization) compatible with tempdb growth? Recently I needed to increase my tempdb by 40 Gb and it took 15+ minutes to complete. I verified that the user had already been added to Perform Value Maintenance Task and also verified that other user databases took seconds to complete.

I didn't find any documentation or link that would have in-depth IFI details with regard to TempDB. I really appreciate your suggestions.


texts: Oracle XE 18c DBFS_CLIENT auth failing for SYS without SYSDBA privileges

texts:
I have been following this tutorial to setup an Oracle DBFS environment for testing.

A certain step of the tutorial is pointing to the use of the DBFS_CLIENT:

When I try to authenticate on the dbfs_client using the "sys" credentials, it returns an error: The connection as SYS must have SYSDBA or SYSOPER privileges.

I understand that the privilege is granted by simply adjusting the "sys" credentials I wrote on the command line.

What am I doing wrong there? How can I solve this issue?


texts:
I have a table with an IDENTITY column. While developing I delete the rows from time to time and add them again. But the IDENTITY values always kept increasing and didn't start from 1 when I added them again. Now my id's go from 68 -> 92 and this crashes my code.

How do I reset the IDENTITY value?


texts: Is it possible to make a high availability PostgreSQL database cluster by open source softwares?

texts:
If use these tools can make a great modern database cluster with full features:

pgbouncer (with HAProxy for HA)
repmgr
barman

The main database will run on the repmgr servers. It has primary and standby. If primary down, standby can up. But will take some seconds. At that time, user can't access the database by pending? Isn't there has a good way to make realtime response?


texts:
SELECT
  A.date as timestamp,
  A.driveNumber as 'Drive Number',
  SUM(2 * 6374.612 * asin(sqrt(POW(sin(radians((B.latitude - A.latitude) / 2)),2) + cos(radians(A.latitude)) * cos(radians(B.latitude)) * POW(sin(radians((B.longitude - A.longitude) / 2)),2)))) as KmDriven
FROM teslafiData A
JOIN teslafiData B ON (A.ta_id = B.ta_id + 1)
WHERE A.latitude <> '' and A.longitude <> '' and B.latitude <> '' and B.longitude <> ''
GROUP By A.driveNumber
ORDER BY KmDriven DESC

This gives me KmDriven for each driving event. Each drive has its own driveNumber.

Now I need to get km drive between each charging event. Between charges there might be several driving events. Each charge is listed by chargeNumber and while charging the driveNumberis 0 and chargeNumber is counting up for each new charge.

Data Example in the DB:

ta_id    latitude      longitude  driveNumber   chargeNumber
1        47.xxx        8.xxx      1             0
2        47.xxx        8.xxx      1             0
3        47.xxx        8.xxx      1             0
3        47.xxx        8.xxx      1             0
...
80       47.xxx        8.xxx      2             0
81       47.xxx        8.xxx      2             0
82       47.xxx        8.xxx      2             0
...
151      47.xxx        8.xxx      2             0
152      47.xxx        8.xxx      0             1
153      47.xxx        8.xxx      0             1
154      47.xxx        8.xxx      0             1
...
256      47.xxx        8.xxx      0             1
257      47.xxx        8.xxx      3             0
258      47.xxx        8.xxx      3             0
259      47.xxx        8.xxx      3             0

EDIT:

I now managed to get the ta_id when a charging session ends and when it starts again. Therefore I need now to figure out how to calculate the distance between those two events

SELECT ta_id, chargeNumber
FROM (
SELECT ta_id, chargeNumber,
       IF ( @prev_ver <> chargeNumber,
           IF (@prev_ver := chargeNumber, 1, 1),
           IF (@prev_ver := chargeNumber, 0, 0)) AS IsDifferent
FROM teslafiData
CROSS JOIN (SELECT @prev_ver := '-1') AS var
WHERE battery_range <> 0 and latitude <> '' and longitude <> '' and latitude <> '' and longitude <> ''
ORDER BY ta_id ) A
WHERE A.IsDifferent = 1
ORDER BY ta_id ASC


texts: Unsure how to implement certain constraints and relations in this relational database schema

texts:
I am in the middle of designing the dB for school project- airline ticketing database.(Using MySQL workbench)

So far, I came up with the following design:

Here are couple things I can't figure out:

Is it good idea to have Foreign keys as non-int values (such as Varchar) ??
Database needs to somehow keep track of Number of seats booked & Number of passengers on board for particular flight. Have no idea where to place those attributes.
How to make sure that Arrival City and Departure City (from Flight table) are different for particular flight ?


texts: Message file not found when starting SQLPlus

texts:
I've installed Oracle XE 11.2 on Fedora 18 but have trouble starting SQLPlus. It quits with the following error:

Error 6 initializing SQL*Plus
SP2-0667: Message file sp1<lang>.msb not found
SP2-0750: You may need to set ORACLE_HOME to your Oracle software directory

All of the environment variables are properly set (copied from installation guide)

# ~/.zshrc
ORACLE_HOME=/u01/app/oracle/product/11.2.0/xe # It is in fact installed there
ORACLE_SID=XE
NLS_LANG=`$ORACLE_HOME/bin/nls_lang.sh`
PATH=$ORACLE_HOME/bin:$PATH

NLS_LANG is AMERICAN_AMERICA.AL32UTF8. As far as I understand Oracle ignores system language related variables but here they are anyway:

LC_CTYPE=en_US.UTF-8
LANG=en_US.UTF-8

Any suggestions?


texts: Ideal way to return a scalar from function (RETURN vs. SELECT)?

texts:
There's a blog for SQL Server that says returning a scalar using SELECT is wasteful:

  SELECT should be used for returning resultsets, not scalars.  This procedure uses a SELECT statement to return a single value to the client.  This is inefficient because most applications will have to prepare additional objects (typically referred to as "recordsets") and other support in order to consume the result.  While it is certainly valid syntax to use SELECT to return scalar values, this does not need to be common in production code.  This is the kind of thing that can make it slightly harder for high-end applications to scale.

Does this apply to PostgreSQL as well? Should functions always use RETURN for outputting scalars, or is it ok to use SELECT whenever it's more convenient?


texts: Loyalty reward - record for every day of a week

texts:
I have a table that contains every login a user makes. I am trying to make a loyalty system that rewards users if they've logged in every day for a week.

So, the real question, how do I get every record in a set week by a user (user_id) and know that there was a login on each day? Would MySQL be able to do this or would I have to do a query for every user and then further processing on top of that?

The only thing I can think of doing is:

SELECT * FROM `users` WHERE `users`.`time_they_logged_in` = IN WEEK X

<?php

$result = $pdo->prepare('SELECT....');
$result->bindParam(1, etc etc);

$logged_in_on = array(0, 0, 0, 0, 0, 0, 0);

foreach($result->fetchAll() as $login_record) {
    if(LOGGED IN DAY = MONDAY -> $logged_in[0] = true)
}

Idea: record for each week, with a column for each day and a total - then it's a matter of updating it on viewing a page / login and then looking for items with a total of 7.


texts: Better DB design to have a SUM column, or to constantly add up all relationship items per query?

texts:
I have a database with an invoices table and invoice_items table. For simplicity, let's just say theinvoices table has the following structure:

| id | name | created_at |

The invoice_items has this structure:

| id | invoice_id | tital | created_at |

Pretty simple, a parent invoice table and the line items go into invoice_items with a relationship setup.

What I'm wondering is, how effective it is to has a sum column on the invoices table? Would it be bad to have all invoice_items per invoice added up on the fly to show the invoice total amount? Or would it be better to have a SUM column which is static, and is pulled automatically when invoices are listed - then updated specifically whenever an invoice changes?

Just trying to weigh up the pro's and con's - because whenever an invoice changes, the SUM column needs to be updated whereas if it was just always calculated based on the line items dynamically, would that be a drastic design flaw or be acceptable?


texts: MongoDB storage of multi-dimensional arrays

texts:
Using MongoDB 3.0.7 as the primary datastore. I connect to it using ReactiveMongo 0.11 on a JVM-based language.

My question is: how well does Mongo store multi-dimensional arrays? For image analysis, I'm storing multiple images in the form of Array[Array[Double]]. There will be 60 images in a single document, each around 250px width and height.

Is this array structure sufficient enough, or do I need to rethink my schema?


texts: How can a raised alert fail to notify an operator via e-mail?

texts:
SQL Server 2008: A message with @msgnum = 75000 and @severity = 16 was added to the SQL instance. An alert was added that is raised when error number 75000 is raised. The alert is enabled and an operator, who is also enabled, is set to be notified via email. The alert history shows that the alert is triggered, but the operator is not notified. The operator history shows "Most recent notification attempts: (Never e-mailed)".

The operator has a correct e-mail address. DBMail is setup correctly since I can sp_send_dbmail from and to that e-mail address.

Where do I begin to troubleshoot?


texts:
I'm using PostgreSQL 8.4 and I have a table that is cleared out and refilled with new data every so often. I want to be able to store the date that the table was last filled, but I'm not sure where to put it. I'm really a novice at database design, and the only two things I can think of are:

Create a table with the sole purpose of storing the date
Timestamp every row in the table with the same timestamp

There's about 1000 to 2000 rows normally. It's just for a hobby project. Any help will be greatly appreciated.


texts: SQLServer: "order by" clause causing excessive locking

texts:
I can't seem to find an explanation as to why this is happening, but while trying to debug a deadlock issue, I found out that SQLServer is locking far more records than it needs to when I include an "order by" clause in my query with the updlock and readpast hints.

For context, I am doing a "select top 1" query on a table that is used for queuing jobs in a multi-node application. I want to grab the oldest job that has not been processed yet, that matches certain criteria that requires joining on a few tables (all of these tables have the nolock hint) If I sort the list by the creation date, all of the records matching the filter are locked, but only one is returned. But if I remove the sorting, only the one record that I want is locked.

Any hints as to why this is happening?


texts: SQL Server : reasons not to use default database settings for ANSI_NULLS?

texts:
I've been given a case where a customer is experiencing this error - once in a while:

  Msg 8624, Level 16, State 21, Line 1
  Internal Query Processor Error:
  The query processor could not produce a query plan. For more
  information, contact Customer Support Services.

They're using our software, and running it on SQL Server 2008 R2 (RTM) and at compatibilty level 100 (SQL Server 2008). The database however was originally created on a 2000 or 2005 machine (can't reproduce anymore) and then moved to 2008 R2 recently.

The stored procedure in question has an ugly looking INSERT statement which grabs data from ten (yes!) "copies" of a given table, all joined with RIGHT OUTER JOIN against one another (same table - ten JOINs against it).

The one solution I found that seems most appropriate has to do with various settings - it's recommended to use:

set ANSI_NULLS ON
set ANSI_PADDING ON
set ANSI_WARNINGS ON
set CONCAT_NULL_YIELDS_NULL ON
set QUOTED_IDENTIFIER ON
set ARITHABORT ON

set NUMERIC_ROUNDABORT OFF

OK - sure - I can set these before every stored procedure I create (or alter).

My question to the database gurus out there would be: any risk when I set these as default settings for my database?

E.g.

ALTER DATABASE MyDB SET ANSI_NULLS ON

and be done with it? Thoughts? Insights? Recommendations?


texts: How can I track down an issue with a locked table, endless query?

texts:
I have a table that is periodically getting locked in a manner that I don't quite understand. I cannot do the following:

select * from thetable
select count(*) from thetable

There are roughly 2,000 records.

I can do the following:

select top 2000 * from thetable
select * from thetable where ID = etc.

Going backwards to find a new record that perhaps was problematic, incrementing the count in chunks until I can finally reproduce it again:

select top 1500 * from thetable order by ID desc
select top 1550.... etc. and eventually it gets locked and never finishes the query.

Query never finishes... have waited 10 minutes. Only resolution is to restart the service.

The related stored procedure that I thought caused the problem I ran manually (it interacts with this table) and the longest time it took was roughly 45 seconds. This particular procedure goes through many phases and is wrapped in a transaction with a try/catch/rollback/commit. There is no explicit locking set on the procedure.

Any direction or guidance to track down the root issue is greatly appreciated.


texts: MYSQL - Timeclock Count Hours In/Out for each day of current week

texts:
Below is my table for my database timeclock that records each record of an employee clocking in/out. I am wanting to create a function that returns hours for each day Monday-Sunday of current week.

id  user    timeinout           type    function
1   1   2015-08-05 20:20:32     in         1
2   1   2015-08-05 20:20:56     out        1
3   1   2015-08-05 20:29:25     in         1
4   1   2015-08-05 20:32:04     out        2
5   1   2015-08-05 20:36:28     in         1
6   1   2015-08-05 20:40:00     out        1
7   1   2015-08-06 09:05:39     in         1

I know this function is way wrong, but here is where I am. I have never done this before so I am at a real loss and all examples I have tried have failed.

function gettimeclockHours($user) {
  include $_SESSION['db'];
  try {
      $sql = "SELECT HOUR(timeinout) as hour, COUNT(*) as num_rows FROM timeclock WHERE user = :user GROUP BY HOUR(timeinout)";
      $s = $pdo->prepare($sql);
      $s->bindValue(':user', $user);
      $s->execute();
  }
  catch (PDOException $e)
  {
      $error = 'Error.' . $e->getMessage();
      moduleError($error);
      exit();
  }
  $result = $s->fetch();
  return $result;
}

The Output example I am looking for is below.

Day of Week                  Total Time
Monday                       1.2 Hours
Tuesday
Wednesday                    3.0 Hours
Thursday
Friday
Saturday
Sunday
Total for current results    4.2 Hours

Thanks for your help in advance.


texts: What role for plpythonu function's in the file system?

texts:
As a preliminary test for a further work I'm trying to use a simple plpythonu function in Postgresql 9.2 to create a folder in my filesystem.  So I have this code :

CREATE OR REPLACE FUNCTION "mkdir_test"()
RETURNS void AS $BODY$

import os

dir = os.path.dirname('/tmp/areas/testdir/')
if not os.path.exists(dir):
    os.makedirs(dir)

$BODY$

LANGUAGE plpythonu
COST 100
CALLED ON NULL INPUT
SECURITY INVOKER
VOLATILE;
ALTER FUNCTION "mkdir_test"() OWNER TO "chewbacca";

It works, but then the directory created 'testdir' belongs to _postgres and has privileges '700', meaning that it is forbidden to anyone but postgres.  How can I change this so that the user triggering this function is the owner of the file/folder created ?  (Currently I'm doing this on Mac os x 10.11 but the objective is to have this working on any OS.)


texts: Oracle 12cR1 - RMAN-11003, ORA-00354, ORA-00353 - Corrupt Redo Log

texts:
Oracle 12cR1, disaster database.

ORA-00283: recovery session canceled due to errors
ORA-00354: corrupt redo log block header
ORA-00353: log corruption near block 8 change 80357235718 time
11/01/2020 01:57:01
ORA-00334: archived log: '/FRA/.../o1_mf_2_61264_hsvvjoyb_.arc'

Database in mount mode and can't recover.
How can I resolve this?
Best regards,


texts:
I'm getting an SQL error when trying to mix a match against in a SQL statement with a specific character which is not covered by MySQL escape functions in PHP.

This is an example:

The name to search is: Tommy -

The generated (error) query is:

Select *
From users
Where match(name,username)
            Against('+tommy -*' in boolean
                          mode)

What is the best way to fix it, and why the mysql_escape functions don't cover it?


texts: Setting DB to Simple Recovery then back to Full Recovery

texts:
I have recently inherited all of our company's SQL databases without much warning or experience, and was hoping to make a good impression and score a few quick wins by completely reviewing backups across the board.

I've implemented various full and t-log backups but have come across another job on this box that sets the DB to simple recovery mode prior to running, then back to full afterwards, which is unfortunately very necessary.

My question is, if a full backup is taken at 1am, and t-log backups are taken at the top of every hour after that, does this other job setting simple recovery mode at 5:30am ruin the "timeline" as such, between the full backup and the next t-log backup at 6am?

It's SQL Enterprise 2012 for this particular machine.

Appreciate any insight you guys have


texts: Is (and if how) it possible in Oracle to "insert into  values " if  has virtual columns

texts:
Here's a table

create table tq84_virtual_test_without (
  col_1 number,
  col_2 number,
  col_3 number,
  col_4 number,
  col_5 number
);

with the rule that col_5's value is the sum of the other four columns.

So the table is filled accordingly:

insert into tq84_virtual_test_without values( 1, 2, 3, 4, 10);
insert into tq84_virtual_test_without values( 3, 8, 7, 5, 23);

commit;

Now, say, that there is a need to copy one row and change just one column's value. This can of course be done quite elegantly (imho, that is) with a rowtype-variable, like so

declare
  r tq84_virtual_test_without%rowtype;
begin

  select * into r from tq84_virtual_test_without where col_2 = 8;

  r.col_4 := r.col_4 - 2;
  r.col_5 := r.col_5 - 2;

  insert into tq84_virtual_test_without values r;

end;
/

This is elegant because it doesn't clutter the source code with insert into ... (col_1, col_2...) values (.., ..) statements and I'd like to keep this feature, if possible.

On the other hand, col_5 is a perfact candidate for a virtual column. So, here's almost the same thing, but with col_5 being a virtual column:

create table tq84_virtual_test_with (
  col_1 number,
  col_2 number,
  col_3 number,
  col_4 number,
  col_5 as (col_1 + col_2 + col_3 + col_4) virtual
);

insert into tq84_virtual_test_with values( 1, 2, 3, 4, default);
insert into tq84_virtual_test_with values( 3, 8, 7, 5, default);

commit;

Now, and this is unfortunate, the following construct doesn't work anymore:

declare
  r tq84_virtual_test_with%rowtype;
begin

  select * into r from tq84_virtual_test_with where col_2 = 8;

  r.col_4 := r.col_4 - 2;

--
--  ORA-54013: INSERT operation disallowed on virtual columns
--
  insert into tq84_virtual_test_with values r;

end;
/

So, is this still somehow possible (and if so, how) to use this rowtype-variable along with virtual columns?


texts: MySql replication failed with error 1236 data position > file size with relay-bin file not increasing

texts:
My slave machine reset and now on SLAVE SHOW STATUS\G I have the error:

Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Client requested master to start replication from position > file size'

The slave settings are showing as:

Master_Log_File: bin.003767
Read_Master_Log_Pos: 751854814
Relay_Log_File: mysql-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: bin.003767
Slave_IO_Running: No
Slave_SQL_Running: Yes

However the relay-bin files on the slave are: mysql-relay-bin.000001 with 1KB in size. The Master on the other hand has the files: bin.003767 at over 700 megs. But no relay files are being updated or copied to the slave.

Is there any way to resolve this without a full data synchronization from beginning? It takes days to achieve this.

Does anyone know what the issue is here?

Much appreciated!


texts: why is the 'Dynamic Ownership Chain' mode of a stored procedure mutually exclusive to "chained" / "unchained"?

texts:
How is the "chained" / "unchained" / "any" transaction mode for a stored procedure related to the "[no] dynamic ownership chain" mode?

Reading the Sybase ASE documentation on the sp_procxmode system stored procedure it appears that these modes are mutually exclusive in the sense that a stored procedure can only have only one of the following five execution modes:

chained
unchained
any
dynamic ownership chain
no dynamic ownership chain

This is corroborated by the fact that sp_procxmode reports only one of the above five values for a given stored procedure.

… yet, according to the documentation [no] dynamic ownership chain is about permissions only and, thus, unrelated to chaining:

  To change a stored procedure’s transaction mode, enter:

  sp_procxmode procname, tranmode

  When you create a stored procedure,
  Adaptive Server tags it with the current session’s transaction mode.
  This means:

  You can execute “chained” stored procedures only in sessions using
  chained transaction mode.
  You can execute “unchained” stored procedures only in sessions using
  unchained transaction mode.

  To execute a particular stored procedure in either chained or
  unchained sessions, set its transaction mode to “anymode”.

  If you attempt to run a stored procedure under the wrong transaction
  mode, Adaptive Server returns a warning message, but the current
  transaction, if any, is not affected.

  Executing sp_procxmode procname, 'Dynamic Ownership Chain' makes sure
  that any Dynamic SQL (execute immediate) statements within the stored
  procedure get their permissions checked against the procedure creator.

  Executing sp_procxmode procname, 'No Dynamic Ownership Chain' (the default behaviour if omitted) makes sure that any Dynamic SQL (execute immediate) statements within the stored procedure get their permissions checked against the procedure executor.

The above description leads me to believe that the "dynamic ownership chain" mode of a stored procedure is really unrelated / orthogonal to the "chained" / "unchained" status so I don't understand why they are treated as mutually exclusive.


texts: Find and remove gaps in sequence across two different columns

texts:
I've tried to solve this problem using a set-based approach.  However, since I need to look at each row I think I must use a cursor; please correct me if I'm wrong.

The table:

Project, item, method, start, end

The table contains multiple projects, multiple items, but I've reduced it here to a single project, one item, for simplicity:

The data looks like this:

ABC, widget1, XY, 1000, 1033
ABC, widget1, XY, 1033, 1062
ABC, widget1, XY, 1062, 1112
ABC, widget1, XY, 1112, 1163
ABC, widget1, WW, 1163, 1223
ABC, widget1, WW, 1223, 1288
ABC, widget1, WW, 1288, 1334
ABC, widget1, XY, 1334, 1383
ABC, widget1, XY, 1383, 1425

I'd like to write a query that returns this result:

ABC, widget1, XY, 1000, 1163
ABC, widget1, WW, 1163, 1334
ABC, widget1, XY, 1334, 1425

What is the best way to do this?


texts: MySQL - Error code 2013. Lost connection to MySQL server during query

texts:
I did check other posts. Change timeout value...does not help. its something with the server. It seems each time if I trying to insert sth out range, the server just collapse. Its my localhost, Server on my own computer.
Tried to drop a table or other things. None of the code would work.
Restart the computer would work again, but I'd rather not to to that each time I run into problem.
None of the code in Workbench would work, all come back with Error code 2013...
mysql.connector.errors.DataError: 1264 (22003): Out of range value for column 'total_revenue' at row 1
23:02:40    DROP TABLE `a_share`.`income_statement` Error Code: 2013. Lost connection to MySQL server during query  30.000 sec
23:03:51    DROP TABLE `a_share`.`income_statement` Error Code: 2013. Lost connection to MySQL server during query  30.000 sec


texts: Innodb cluster instance is changed during creation. The instance reports it to another one which I did not provided. What is the problem here?

texts:
MySQL  db1:3306 ssl  JS > cluster = dba.createCluster('prod_cluster');
A new InnoDB cluster will be created on instance 'db1:3306'.

Validating instance configuration at db1:3306...

This instance reports its own address as zserver:3306

Instance configuration is suitable.
NOTE: Group Replication will communicate with other members using 'zserver:33061'. Use the localAddress option to override.

This is my host file
192.168.xx.xxx  db1 db1.local
192.168.xx.xxx  db2 db2.local
192.168.xx.xxx  db3 db3.local
127.0.0.1       localhost localhost.localdomain
# The following lines are desirable for IPv6 capable hosts
::1     ip6-localhost ip6-loopback
fe00::0 ip6-localnet
ff00::0 ip6-mcastprefix
ff02::1 ip6-allnodes
ff02::2 ip6-allrouters

I have given bind-address as 0.0.0.0 in /etc/mysql/mysql.conf.d/mysqld.cnf


texts:
I wonder when to use auto_increment. It is said that it can be used for a unique identifier. But why the unique identifier is needed?

Suppose the data of a student has entered in a student table. If I  enter that student data again, it will be ok since it create another id automatically. But I have storing the same data unnecessarily.

What is the meaning of using that?

If I can identify a primary key, I do not need to use that?
For example, For a Hotel table, I can use the combination of Hotel name and address as a primary key. Then, we can avoid storing the same data several times.

Is it better to use to generate a key with auto_increment?


texts: Hive metrastore Database in Postgresql External

texts: We recently migrated hive metastore database from embedded postgresql(coming with cloudera hadoop) to external postgresql(standalone in vm server). After that we notified that there is increase of 5 to 10 seconds in query execution. May i know why it happens?. Postgresql config setting almost same except archive_mode which is on external postgresql, while in embedded postgresql it is off.


texts: What are some consequences of putting quotes in a WHERE clause for an integer column?

texts:
I have a table with a column some_column of data type integer.
I've noticed that my PostgreSQL 10.6 instance is smart enough to interpret both of the queries below as same:

select * from my_table where some_column = 5; -- no quotes

select * from my_table where some_column = '5'; -- quotes added

What are some repercussions of adding quotes in a where clause for an integer column?
Would it have performance effects on a large table of, say, 20 million rows?
(There is an index for some_column.)


texts: Hallengren @DatabasesInParallel sql_message_id 50000 in sysjobhistory table

texts: Just implemented Ola's Parallel execution for backups, integrity checks and index maintenance. Everything appears to be running great but I am seeing sql_message_id 50000 in the sysjobhistory table for the new executions. I am also seeing [SQLSTATE 01000] on each line of the job output files. Is this normal behavior? I can't seem to find any in-depth documentation online.


texts: PostgreSQL - If I run multiple queries concurrently, under what circumstances would I see a speedup? Under what circumstances would I see a slowdown?

texts:
I approach you all humbly as one who is NOT a DBA, and I'm sure that my question is fraught with conceptual shortcomings and "it depends on" land mines.  I'm also pretty sure that all of you who choose to answer are going to want a lot more in the way of specifics than I can currently deliver.

That said, I'm curious about the following scenario in general:

Say that I have two non-trivial queries.
Query 1 requires 2 minutes to complete on average.
Query 2 requires 5 minutes to complete on average.

If I run them serially, one right after the other, I'm expecting it will require 7 minutes to complete on average.  Is this reasonable?

More than that, however, what if I run the two queries concurrently?  Two separate connections at the same time.

Under what conditions would I expect to see a speedup?  (Total time < 7 minutes)
Under what conditions would I expect to see a slowdown?  (Total time > 7 minutes)

Now, if I had 1,000 non-trivial queries running concurrently, I have a hunch that it would result in an overall slowdown.  In that case, where would the bottleneck likely be?  Processor? RAM? Drives?

Again, I know it's probably impossible to answer the question precisely without knowing specifics (which I don't have.)  I'm looking for some general guidelines to think about when asking the following questions:

Under what circumstances do concurrent queries result in an overall speedup?
Under what circumstances do concurrent queries result in an overall slowdown?


texts: Why my transaction has not been replicated? What was the transaction sequence number?

texts:
I have been troubleshooting a replication problem, according to the picture below:

with reference to the picture above,

the bit that says
Sessions of the distribution agent
I can get using the following query:

--================================================================================
-- finally the contents
-- sessions of the distribution agent:
--================================================================================
use [master]
exec [distribution]..sp_MSenum_distribution_s
@name = N'MYSERVER-Product-Product-MYSUBSCRIPTION-184',
@hours = -1,
@session_type = 1

Actions in the selected session

--========================================================
-- get the actions in the selected session:
-- for each row on the above data, we can get the details:
--========================================================
-- example of the last session - without errors
use [master]
exec [distribution]..sp_MSenum_distribution_sd
@name = N'MYSERVER-Product-Product-MYSUBSCRIPTION-184',
@time = N'20170411 14:16:03.920'

-- example of the second session - with errors
use [master]
exec [distribution]..sp_MSenum_distribution_sd
@name = N'MYSERVER-Product-Product-MYSUBSCRIPTION-184',
@time = N'20170410 15:50:27.650'

Now this bit of code below gives me the errors I am looking for:

Error details or message of the solected session:

And here goes the Question too:

How can I get the number 5468694 from the result above and use it in the query below?

--========================================================================
-- Error details or message of the solected session:
--=========================================================================
    use [master]
    exec [distribution]..sp_MSenum_distribution_sd
    @name = N'SQLAPPLON1-Product-Product-REPLON1-184',
    @time = N'20170410 15:50:27.650'

    use [master]
    exec [distribution]..sp_MSget_repl_error
    @id = 5468694


texts: Oracle 17410(connection reset) when using count(*) on cross join cte with json_table

texts:
Met strange behavior using query with json_table in cte at oracle - connection reset from server, without details.
Illustrating artificial query is:
with data as (
    select 5 as some_value
    from dual
),
ps as (
    select
       dt.some_value,
       json_cnt
    from data dt
    cross join json_table(
            to_clob('{"positions":[{"name":"aaa","cnt":5}]}'),
            '$'
            columns (
                nested path '$.positions[*]'
                    columns (
                        json_cnt varchar2 path '$.cnt'
                        )
                )
        )
)
-- select * -- its ok
-- select count(json_cnt) -- also ok
select count(*) -- fails with connection reset
from ps

It behaves such way:

count(*) - leads to "No more data read from socket" and 17410 error
* - leads to correct fields representation
count(json_cnt) - leads to correct count (1)

In real scenario query is more complicated and uses data from real tables, but for illustration purpose cut it to be reproducible(at least in my environment) in simplified format.
More I can say, that adding some kind of additional use of intermediate cte, "corrects" expected behavior. following query works without a problem :
with data as (
    select 5 as some_value
    from dual
),
ps as (
    select
       dt.some_value,
       json_cnt
    from data dt
    cross join json_table(
            to_clob('{"positions":[{"name":"aaa","cnt":5}]}'),
            '$'
            columns (
                nested path '$.positions[*]'
                    columns (
                        json_cnt varchar2 path '$.cnt'
                        )
                )
        )
),
int_cte as (
    select 5 as json_cnt from dual
)
select count(*)
from ps
left join int_cte on ps.json_cnt = int_cte.json_cnt;

I can guess, that there is some strange combo of cross join leading to this, but don't understand the reason. And it took a time to figure out the source of a problem in real query.
Can somebody give a hint what is wrong with first query and what leads to error without explicit check but connection reset.
Version: 12.2.0.1.0


texts: Why is SQL Server 2012 Recommending This Index Be Created?

texts:
I have the following query:

Select SubId, Max(ReadTimeLocal)
From dbo.PanelWorkflow
Where ProcessNumber IN (802,1190,1605,1620,1645,1660,1695,1790,1990,2690,2795,2990,3090,3290,3590,3790,4190,4390,4590,5000,5200,5400)
Group By SubId

I have the following index on the dbo.PanelWorkflow table:

CREATE NONCLUSTERED INDEX [IX_PanelWorkflow_ProcessNumber_Lineage] ON [dbo].[PanelWorkflow]
(
    [ProcessNumber] ASC,
    [Lineage] ASC
)
INCLUDE ( [SubId],[ReadTimeLocal]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

The query plan from SSMS looks as follows:

SSMS is recommending  I create the following index (with an impact of 54.3)

CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[PanelWorkflow] ([ProcessNumber])
INCLUDE ([SubId],[ReadTimeLocal])

Does anyone know why it's recommending I create an index that essentially exists already, and is not using the one that does exist?

There are 830 million rows in the table. Using SQL Server 2012.


texts: Error while querying 'Always encrypted' column

texts:
I have set up 'Always encrypted' on one of the column in Azure SQL DB. Everything worked well except when a user is trying to access he is getting below error. '
I have tried some of the rememdies as below.'Failed to decrypt a column encryption key using key store provider: 'AZURE_KEY_VAULT'. Verify the properties of the column encryption key and its column master key in your database'

User is mapped in Azure AD group which is also mapped in Azure SQL server. Keys are stored in Azure key vault and the AD group is given 'Contributor' access and 'decrypt', 'encrypt', 'get' , unwrap' access is given through access policy.
'GRANT VIEW ANY COLUMN MASTER KEY DEFINITION'  granted to AD group in SQL DB.


texts: How to design a persistent table with dependencies which are allowed to be deleted in the future?

texts:
I am designing a database that archives all class schedules and student attendances based on these schedules.
So here are the tables.

Teacher, Subject, Classroom, Class

Schedule table has many-to-many relationship to the above tables.

Schedule:
  date (date),
  start_time (time),
  end_time (time),
  teachers (m2m to Teacher),
  subject (foreign key to Subject),
  classrooms (m2m to Classroom),
  student_classes (m2m to Class)

My issues is that the schedules must be persistent. However, the teachers may be deleted from the system. How do I design my dependencies so that removing them will not affect the schedules. Currently all relationships use integer primary key.


texts: Are there issues with using SELECT * in a view definition?

texts:
I am creating a number of proxy views for views in a separate database. To avoid defining the view in two places, I want to use SELECT * to create the proxy view.

I've searched for reasons not to do this and didn't find any. Anyone have an opinion on using SELECT * for a view definition?


texts: Where are Statistics physically stored in SQL Server?

texts:
Where are the Statistics used by the Query Optimizer physically stored inside a SQL Server database file and the Buffer Pool?

More specifically, is there a way to figure out the pages used by statistics using DMVs and/or DBCC?

I own both SQL Server 2008 Internals and SQL Server Internals and Troubleshooting books and none of them talks about the physical structure of the statistics; if they do I haven't be able to find this information.


texts: What is the best storing device for DB transaction log?

texts:
I'm using MySQL with InnoDB storing engine. Transactional log is placed to dedicated HDD. Still as it is HDD atopsar shows big load (>50%) to this HDD because of big amount of small IOs (about 50 writes per second 6Kb each).
Setting innodb_flush_log_at_trx_commit to 2 solved problem and now load in average 5%. However, MySQL documentation says:

When the value is 2, the log buffer is written out to the file at each commit, but the flush to disk operation is not performed on it. However, the flushing on the log file takes place once per second also when the value is 2.
With a value of 2, only an operating system crash or a power outage can erase the last second of transactions.

Therefore, it is possible to lose transactional log for last second.
I'm not experienced in configuring hard for DB servers and therefore want to know what modern solutions exists and are most suitable for DB's transactional log giving reliability and high IO capable.
By the best I mean different variants, but personally would prefer optimal combination of performance/price. Any way I think it will be great to collect here different variants.
I know that usual answer will be "use SSD", but as I'm aware of its storing principles it's not reliable solution for transactional log and it should relatively fast fail. From first look, it may seem that rewrite count of modern SSD is enough to operate for years. However, as I know SSD rewrite data by blocks and for example if you want to write 1b of data SSD will rewrite entire block, which may be 4Kb. So for 1 transaction log rewrite circuit SSD might rewrite its blocks many times.
From my perspective, ideal storing device will be a combination of Battary+RAM+HDD with relatively small size (around 5Gb or less). Which in normal operation uses only RAM and on electricity black out flushes all data to HDD. It should be all in one device, not a software solution. However, I do not know if they exist.


texts: Automatic change of "initial size" of a database?

texts:
I just had a problem : days ago, on one of my client's databases, the LDF file suddenly grew up to 100% of the disk capacity.

.TRN backups are correctly done every 15min (I don't think reducing this time will help, because this database is rarely used).

I created this database from a backup, two months ago. Everything was OK.

What I didn't understood, is why the "initial size" of the LDF file was... 512GB, which is the size of the disk...
If the initial size had been like that since the first restore, i would have noticed it. So I'm certain that it was not the case.

What I did : backup, simple mode, reducing initial size (it reduced the file size without shrink), full mode, backup.

It's OK now, but i'd like to understand if the "initial size" can change automatically, and if yes, how to prevent it.

Thanks!


texts: Oracle Enterprise Manager - stats split by user (schema)

texts:
Is there any way to split all the stats by user (schema) when looking at the Performance tab in the Oracle 11g Enterprise Manager?

or effectively some other tool to retrieve that information - e.g. a running chart of #active sessions for user (that's probably easy by probing the v$session or similar). Or a running chart of I/O Megabytes per Second broken by user/schema.


texts: Does document "depth" affect CouchDB performance?

texts: I have a project where it is very tempting to use a deeply nested collection of data. But how does that affect performance? Are there any reasons why I should avoid doing this? I'm talking about lists in lists in lists, etc.


texts: Domain User lockout after SQL Cluster Failover

texts:
We have this one user who uses SSMS to connect to a 3 node 2 instance fail over cluster. He also uses SSIS, Excel, SharePoint, SSAS etc.. to connect to this database instance I'll call TEST. There are a couple hundred other users of this instance.

His domain account gets locked out about 1-2 days after we have an instance failover. This has been happening for about a year. We generally failover about once a month but sometimes not for a couple months. He gets his password reset and everything is fine until the next failover.

I'm assuming he has entered his password in somewhere and it has since expired.  He changed it but never updated the spot he saved it. I can't find that spot for the life of me and the user is getting upset because I keep asking the same question.

We have verified using our syslog server that the lockout is coming from that SQL node that runs the TEST instance. It changes as the instance floats around the cluster so we are sure it is something in the TEST instance. The database engine and SSAS run as part of the TEST cluster service. SSISDB is fairly heavily used on that instance.

I checked all the agent job owners, he doesn't own any of those. I checked the SSISDB parameters list but didn't find is account in there. I'm not really sure if the constrained delegation we have setup for reporting services could trigger the lockout to come from the database server but maybe...

Any other spots I could check for saved credentials in SQL?


texts:
Our Postgres database is currently at about 68G of size and growing at a rate of 6G/month. I am investigating ways how to scale the database.

Would be nice if someone can give some advices as I am new about this?

One suggestion I got is to use Amazon RDS. But it has some limitations as we replicate some particular tables between the database instances using Londiste.

Thanks


texts: Select data from one table with id from another

texts:
so as text is saying I need to grab user id by name from one table and then select count from another table by that ID. is it possible?

users table

ID | User

online table

ID


texts:
I'm storing data in minute, hour, day, month and year buckets. All data is stored in UTC. Let's assume a client is in PDT time (-07:00 UTC)

If the client wants to query for the hour sum of 4/23/2016 7:00pm in their timezone they would convert the time to UTC - 4/24/2016 2:00am and make the query. Image for reference.

This works perfectly fine for hour and minute buckets. However, let's look at the case where the client wants the sum for a day bucket. If the client wants the local day dum for 4/24/2016 they would convert the time to UTC which resolves in 4/24/2016 also. The 4/24/2016 UTC day bucket contains data for 7 hours from the local day 4/23/2016 and misses the last 7 hours of the local day 4/24/2016. This seems like a problem since the query will not return the correct sum. It returns the sum for the UTC day.

Am I missing something with this example? Or is storing data buckets in time intervals > hour a bad idea?


texts: Getting MAX of AVERAGE measure (Power BI/DAX)

texts:
I am stumped. I am calculating an average of products sold per location. Then, I am using AVERAGEX to calculate that average for each sales associate.

But how do I find the MAX of that average and use that as a benchmark for each associate? I want to know for each associate the difference between the MAX AVERAGE products/location and the associates AVERAGE products/location.

I've wanted to use some sort of summary table, but this is a problem because then I lose the contexts I need (dates, locations with sales in that date period). Below are the measures I have so far:

ProductsPerLocation = DIVIDE([TotalSolutions],[TotalLocations])

AveProductsPerLocationByAssociate = AVERAGEX(VALUES(Employees[EmpID]),[ProductsPerLocation])

Hopefully this makes sense. Thanks!


texts: Does Google CloudSQL Export function lock tables?

texts:
In order to create more 'data recovery' points than the standard automatic backup policy allows, I'm going to run a cron which will export sql dumps out of my CloudSQL instances at intervals throughout the day. I know I can do this using the gcloud command as follows:

cloud sql export sql [INSTANCE_NAME] gs://[BUCKET_NAME]/sqldumpfile.gz \
    --database=[DATABASE_NAME] --table=[TABLE_NAME1,TABLE_NAME2, ...]

There is not much info on Google support docs as to what is actually happening here. What's important to me is whether this process causes tables to be locked on export like a mysqldump command would (without the appropriate flags)

I know that the gcloud export function includes the --skip-triggers flag, but there is no information about table locking. Can anybody help?


texts: What is the cardinality of entity relationship between Employee and Department?

texts:
Recently, a friend of mine asked me this question about the cardinality between  a Department and Employee entities, I replied many to many, then he, my friend, showed me drawings from the internet which says otherwise, one to one. I objected the idea and told him don’t trust everything on the internet. However, after I examined books on Database Concepts I found the one to one relationship that my friend showed to me. As an example I took the following snapshot from “The Morgan Kaufmann Series in Database Management System: Database Modeling and Design 4th Edition”

Fortunately, these guys mapped the entities depicting the foreign key relationship. Suppose after a year another employee is assigned to manage a department previously being managed by another employee. At this point are we going to update the mgr_id column and loose the history of the department and employee relationship?