texts: Why does this tsvector update use so much space?

texts:
This is my table structure:

  Column     |           Type           | Collation | Nullable |               Default                | Storage  | Stats target | Description
-------------+--------------------------+-----------+----------+--------------------------------------+----------+--------------+-------------
 id          | integer                  |           | not null | nextval('tbl_disk_id_seq'::regclass) | plain    |              |
 inserttime  | timestamp with time zone |           | not null | now()                                | plain    |              |
 buildid     | text                     |           | not null |                                      | extended |              |
 app_version | text                     |           | not null |                                      | extended |              |
 device      | text                     |           | not null |                                      | extended |              |
 device_api  | text                     |           | not null |                                      | extended |              |
 userid      | text                     |           | not null |                                      | extended |              |
 path        | text                     |           | not null |                                      | extended |              |
 size        | numeric                  |           | not null |                                      | main     |              |
 type        | integer                  |           | not null |                                      | plain    |              |
 root        | integer                  |           | not null |                                      | plain    |              |
 status      | integer                  |           | not null | 0                                    | plain    |              |

I used this query to check the size of the table:

SELECT  relname AS "relation",
  pg_size_pretty(pg_total_relation_size(C.oid)) AS "total_size"
  FROM pg_class C
  LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
  WHERE nspname NOT IN ('pg_catalog', 'information_schema')
    AND C.relkind <> 'i'
    AND nspname !~ '^pg_toast'
    AND relname like 'tbl_disk%'
  ORDER BY pg_total_relation_size(C.oid) DESC
  LIMIT 20;

The result:

       relation        | total_size
-----------------------+------------
 tbl_disk_20180830     | 90 MB
 tbl_disk_20180831     | 32 MB

Now I add a new column document with type tsvector:

ALTER TABLE tbl_disk ADD "document" tsvector;

and update the data:

UPDATE tbl_disk
SET document = array_to_tsvector(ARRAY[split_part(path,'/',2),split_part(path,'/',6)]);

path data like : /data/user/0/com.abc.abc/database/olala.db

I just need two tokens 'data' and 'database' , that is why I split data.

After successfully updating, the size approximately doubles:

       relation        | total_size
-----------------------+------------
 tbl_disk_20180830     | 175 MB
 tbl_disk_20180831     | 61 MB

The data :

                          path                          |      document
--------------------------------------------------------+--------------------
 /data/user/0/com.abc.abc/lib/libabcqlite.so          | 'data' 'lib'
 /data/user/0/com.abc.abc/files/ddinfo2               | 'data' 'files'
 /data/user/0/com.abc.abc/lib/libabcsqlite.so         | 'data' 'lib'
 /data/user/0/com.abc.abc/lib/libabcsqlite.so         | 'data' 'lib'
 /data/user/0/com.abc.abc/shaders/b32                 | 'data' 'shaders'

You can see the size of tbl_disk_20180830 increases from 90MB to 175MB.

I don't know why it needs such a lot of space to save two tokens?


texts: Compare Timestamp Difference Between Consecutive Records

texts:
How do I compare Timestamp difference between 2 consecutive records?

This is my example:

DECLARE @x TABLE(ProductID INT, SingleRunFailure bit, SampleDate date);
INSERT @x VALUES
(101, 1, '2016-01-01 10:09:19.617'),
(101, 0, '2016-01-05 11:09:19.617'),
(101, 1, '2016-01-07 12:09:19.617'),
(101, 0, '2016-01-09 12:09:19.617'),
(101, 1, '2016-01-15 13:09:19.617'),
(101, 0, '2016-01-17 13:09:19.617'),
(101, 0, '2016-01-29 14:09:19.617'),
(101, 0, '2016-02-02 15:09:19.617');

;WITH cte(ProductID, CCVPassed)
AS
(
   SELECT ProductID, CASE WHEN SUM(LastTwoRunsPass) >= 2 THEN 1 ELSE 0 END
   FROM (SELECT ProductID, LastTwoRunsPass = CASE WHEN SingleRunFailure = 0 THEN 1 ELSE 0 END,
             RowNumber = ROW_NUMBER() OVER(PARTITION BY ProductID ORDER BY SampleDate DESC)
        FROM @x) AS aggregation
   WHERE RowNumber <= 2
   GROUP BY ProductID
)

SELECT * FROM cte;

This returns me a Pass or Fail where SingleRunFailure flag was false on last 2 records.  This part works as expected.

I need to add logic where if the SampleDate difference on the last 2 records was less than 15 minutes return True.

Expected response:

ProductID    CCVPassed
101          0

This is because the difference between last 2 records is 4 days.  If the SampleDate difference between last 2 records was less than 15 minutes CCVPassed would return 1.


texts:
In SQL Server Management Studio, when I run the query

select * from tablename

it shows me the data.

But, that table is not physically present in that database. Is it possible?


texts: Table partitioning in MYSQL Master Slave

texts:
I have to Partition a MYSQL table using Range partitioning. Table is 200M rows big and I have Master/Slave replication.

What I want to do is to stop replication on slave, partition the table on slave and start replication. My question for you guys is would this work despite the fact Master would have no partitioning on that table but slave would have roughly 50 partitions of that table?

Would replication still work ?


texts: How to get Identity Column to jump ahead (by say 1000) on SQL Server 2012

texts:
I am using SQL Server 2012 and I would like to keep my pre-existing data but to cause the ID (identity) column to skip by 2000 rows.

Why is this happening? I have a scenario where I may have transactions happening on a standby server which I will need to copy to current server. I want the IDs to match. Therefore I do not want any new transactions on this server that would have the same ID as my standby server.

Hopefully this makes sense!! Thank you


texts: Why isn't the result of an OpenQuery refreshing?

texts:
I'm trying to get some data from the Active Directory in SQL. I use the code:

SELECT * FROM OpenQuery
(
ADSI,
'SELECT  objectGUID, objectSID, employeeID, lastLogon, mobile, telephoneNumber, mail,  department, givenName, sn, displayname,  sAmAccountName
FROM ''LDAP://DC=mycompany''
where  objectClass = ''user''
and sAmAccountName = ''testuser'''
)

My problem is that the value i receive for lastLogon isn't refreshing. It is possible that the other values aren't also refreshing, however i can't say this for sure, because in the other data there is no change, but the lastLogon value should be updated when i lock my computer and log in.

Does anyone have any idee why is this, and how can i solve this?

Thanks!


texts: Index for finding combinations of attributes in JSON array

texts:
Based on this related question on SO.

This works fine if you only want simple matches. Suppose tracks have both Artist and Title field in the JSON data. So we have something like:

INSERT INTO tracks (id, data)
VALUES (1, '[{"artist": "Simple Plan", "title": "Welcome to My Life"}]');

We create the index like in the original question:

CREATE INDEX tracks_artists_gin_idx ON tracks
USING GIN (json2arr(data, 'artist'));

CREATE INDEX tracks_title_gin_idx ON tracks
USING GIN (json2arr(data, 'title'));

So now we have two fields to match. As you can see, if we perform the original query (with very naive modifications) of:

SELECT *
FROM   tracks
WHERE  '{"ARTIST NAME"}'::text[] <@ (json2arr(data, 'artist'))
AND    '{"TITLE"}'::text[]       <@ (json2arr(data, 'title'))

This will give the wrong answer because the indices of the array of artist and title in the JSON array do not have to match for this query to match something in the JSON. What is the proper way of doing this query so we can get the exact match we need? Does json2arr() need to be changed?

Edit: Why this is wrong

Suppose our table has records like:

INSERT INTO tracks (id, data)  VALUES
  (1, '[{"artist": "Simple Plan",    "title": "Welcome to My Life"}]')
, (2, '[{"artist": "Another Artist", "title": "Welcome to My Life"},
        {"artist": "Simple Plan",    "title": "Perfect"}]');

If you query like:

SELECT *
FROM   tracks
WHERE  '{"Simple Plan"}'::text[]        <@ (json2arr(data, 'artist'))
AND    '{"Welcome to my Life"}'::text[] <@ (json2arr(data, 'title'))

Both records will be matched (both record 1 and 2), even though you really only wanted the first record.


texts: Primary key for link/junction table with time-dependent data

texts:
I want to link two tables in such a way that for any given time period the relationship is one-to-one, but over time the relationship can change.

As an minimal example, suppose I want to list the leaders of various countries in a database that tracks many politicians, including those in positions other than 'leader'. I can create a table of politicians, and a table of countries, like so:

CREATE TABLE politician (
  id integer unsigned NOT NULL,
  name varchar(45) NOT NULL,
  PRIMARY KEY (id)
);

CREATE TABLE country (
  id integer unsigned NOT NULL,
  name varchar(45) NOT NULL,
  PRIMARY KEY (id)
);

Now, if every country had only a single leader forever, I could just link these with a table such as:

CREATE TABLE country_leader (
  countryid integer unsigned NOT NULL,
  leaderid integer unsigned NOT NULL,
  PRIMARY KEY (countryid, leaderid),
  KEY fk_countryleader_country_idx (countryid),
  KEY fk_countryleader_politician_idx (leaderid),
  CONSTRAINT fk_countryleader_country FOREIGN KEY (countryid) REFERENCES country (id) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT fk_countryleader_politician FOREIGN KEY (leaderid) REFERENCES politician (id) ON DELETE NO ACTION ON UPDATE NO ACTION
);

However, this situation obviously isn't the case, because existing leaders are sometimes re-elected (possibly even with some time between two different terms in office), and when they aren't then new leaders take over. So I think that a better link table would look something like this:

CREATE TABLE country_leader (
  countryid integer unsigned NOT NULL,
  leaderid integer unsigned NOT NULL,
  termstart date NOT NULL,
  termend date NOT NULL,
  PRIMARY KEY (???),
  CONSTRAINT ???,
  ....
);

So my question is, what is the best way to fill in the missing PK and constraint information in the junction table country_leader? Or should I be using a different approach to solve this problem?


texts: Can I delete SQLServer2017Media folder from my C drive?

texts:
I have installed SQL server 2017 on my laptop, because am running out of disk space I would like to know is it okay if I delete the SQLServer2017Media folder (takes 1.6 GB) now that set up us complete and application is running fine?
Installation folder is C:\Program Files\Microsoft SQL Server, but SQLServer2017Media folder is also in my C drive C:\SQLServer2017Media and taking up 1.61 GB.


texts:
I am using ssis to generate some .dat files which are then sent to linux server for automated load. It looks as windows add some symbol character in file which is not easily identifiable first but if i open file on linux server i can see that symbol.

For eg. on our side file looks fine but on linux side when opened I see ÿþ is added as first charater in file.  This is UTF-16 (LE) little-endian character represented as Char(255) and Char(254) in decimal. I tried to use replace to remove this symbol but didn't work
For eg. Select replace(replace('ÿþ01CAP   00820130208',char(255),''),char(254),'')

This works fine for string but there is nothing to replace in the file generated on windows side. Once it is uploaded to linux that symbol is sticked into it..
Any ideas ? What could be wrong here ?
I tried to use diff collation in query that generates this file but no luck

Thanks,
NJ


texts: When running `make installcheck` I get can not connect to database

texts:
On Ubuntu when I run make installcheck I'm getting

/usr/lib/postgresql/9.5/lib/pgxs/src/makefiles/../../src/test/regress/pg_regress --inputdir=./ --bindir='/usr/lib/postgresql/9.5/bin'    --inputdir=test --load-language=plpgsql --dbname=contrib_regression base math equality
(using postmaster on Unix socket, default port)
============== dropping database "contrib_regression" ==============
psql: could not connect to server: No such file or directory
    Is the server running locally and accepting
    connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?
command failed: "/usr/lib/postgresql/9.5/bin/psql" -X -c "DROP DATABASE IF EXISTS \"contrib_regression\"" "postgres"
/usr/lib/postgresql/9.5/lib/pgxs/src/makefiles/pgxs.mk:272: recipe for target 'installcheck' failed
make: *** [installcheck] Error 2

What's the reason for this?

This is on the pg-bignum extension.


texts: How can you recursively query across 2 columns?

texts:
Sorry my DBA skills are a bit limited, but here's an example schema (simplified from my real table)
create table test
(
    id          serial primary key,
    person      TEXT,
    start_point TEXT,
    end_point   TEXT
);

and here's some sample data
insert into test(person, start_point, end_point)
values ('Bob', 'a', 'b'),
       ('Bob', 'b', 'c'),
       ('Bob', 'c', 'd'),
       ('Alice', 'a', 'b'),
       ('Alice', 'b', 'c'),
       ('Fred', 'a', 'b'),
       ('Fred', 'c', 'd')
;

Notice how a particular person can go from a->b, then b->c etc.
The Question
How can I SELECT that traverses the start/end and gives me something like this as the output?
| person | place | group_id |
|--------|-------|----------|
| Bob    | a     | 1        |
| Bob    | b     | 1        |
| Bob    | c     | 1        |
| Bob    | d     | 1        |
| Alice  | a     | 2        |
| Alice  | b     | 2        |
| Alice  | c     | 2        |
| Fred   | a     | 3        |
| Fred   | b     | 3        |
| Fred   | c     | 4        |
| Fred   | d     | 4        |

Notice how Bob went thru a-b-c-d, Alice went thru a-b-c, but Fred didn't make it all the way thru so he gets split out into an a-b then a c-d, because he was missing the b-c. group_id can just be from a sequence.
What I have tried so far
What I'm currently doing, is selecting the entire dataset into a CURSOR, then doing a LOOP over it, checking if the start_point matches the end_point of the previous row, and the person matches, whilst this condition is true group them up and update a table.
When we don't find a match, assume it's for a new person and start counting again.
This is incredibly inefficient when the table grows, and I'm in a multithreaded application so I'd much rather be able to do an atomic UPDATE...SELECT rather than opening a cursor and looping through it.
Thanks


texts:
I'm going to install Percona XtraDB Cluster, and I have two questions about it:

1- Is it the last step?  https://www.percona.com/doc/percona-xtradb-cluster/LATEST/verify.html

Or installing ProxySQL is mandatory? I have only 3 nodes and not planning to add an extra node only for ProxySQL! And I think I don't even need it, my round-robin load-balancer is sending the requests to these servers randomly (without health controlling).

So if a node is down it can't even read/write to the DB.
in a nutshell, my application nodes are the same as my DB nodes, and a load-balancer in front of them, So I think I don't need ProxySQL, Am I?

2- What happens if all 3 nodes are down together, I think if this situation happens in a common Group Replication of MySQL, all the cluster collapses and all nodes are being removed from the cluster and we need to reconfigure the cluster manually again, Is the same problem exists in PXC or not?


texts: How do I connect to a database with a blank password using a shell script?

texts:
## connect to mysql and source backup file ##
USER="root"
PASS=""

mysql -u$USER -p$PASS database_name < backup.sql

Above is my shell script which I used to source database but still its asking for password. I just want to know how can I source database in case of blank password. If root password is not blank than it works fine.


texts: If in mysql I have string `xxx yyy` - how can I SELECT this if user input `xxxyyy`?

texts:
My online-shop in PHP (OpenCart). On site we have product-search. For example product have name iSport 220, and if user search input string is iSport220 we must show this product. And vice versa.

First my thought was about LIKE operator, but I see that I need something different. In list of MySQL string functions I do not find any useful. What the best practice for this kind of problem? Version of MySQL is 5.5.


texts: Using all the CPU cores by NDBD and MySQL

texts:
We are running MariaDB which is a fork of MySQL with NDB cluster engine.
The cluster includes one mysql node and two ndb data nodes. While benchmarking the cluster, we are trying to make a bottleneck of CPU and the mysql nodes has 8 cores in total. But mysqld process uses only one to two cores and does not utilizing more than 2 cores.
We tried increasing threads and warehouses but that did not help in utilizing all the CPU cores. Is there a way to make this possible. What system variables should I focus on to increase???

Any thoughts or suggestions are appreciated.


texts: Multiple left joins with common fields are overwritten

texts:
Tables
transactions t

transaction_type
transaction_name

sale
one

sale
two

sales s

name
amount

one
10.0

two
5.0

purchases p

name
amount

Wanted

transaction_type
transaction_name
amount

sale
one
10.0

sale
two
5.0

Tried
select t.transaction_type, t.transaction_name, s.amount, p.amount
from transactions t
left join sales s on t.transaction_type='sale' and t.transaction_name=s.name
left join purchases p on t.transaction_type='purchase' and t.transaction_name=p.name

Got

transaction_type
transaction_name
amount

sale
one
null

sale
two
null

I understand that the outer join of the purchases is overwriting the amounts from the sales because of the common field, but what is the best approach in this case?
Note that there will be more tables to join like this. This is mysql in case it makes a difference, the query has been paraphrased and there is an orm wrapper.
I know I can do it with separate queries, inner joins and unions, is that best? I was hoping to do it in one query.
TIA!


texts: Running members of a job subset at different intervals

texts:
So I'm wanting to batch a number of stored procedures into one job and would like a number of the procedures to run on a schedule different than that of the main job without setting up a new job for each schedule type.

Is this possible?


texts: Improving performance of count where only result > 0 matters

texts:
I have following query:

select count(*) -- rp.tag
from submission
inner join post rp on submission.id = rp.submission_id
where user_name = ?1
and lower(rp.tag) like ('%verified%')
and not lower(rp.tag) like ('%unverified%');

If the result is greater than 0 I do something in my application for this user.

I run this query against a big list of users and it takes close to an hour to finish the whole process. And I only need to know whether the result is greater than 0.

Is there a way to finish the query early if any of the rp.tag match my condition?


texts: Postgresql csv to db translate foreign keys

texts:
I'm trying to import data from a csv to a postgresql database.

the db table looks like this:

CREATE TABLE texts(
text_id serial PRIMARY KEY,
topic_id integer REFERENCES topics,
format_id integer REFERENCES formats,
text text
);

Content:
    1;1;1;sample text

CREATE TABLE topics(
topic_id serial PRIMARY KEY,
topic varchar(50) NOT NULL
);

Content:
    1;comedy

CREATE TABLE formats(
format_id serial PRIMARY KEY,
format varchar(50) NOT NULL
);

Content:
    1;A5

and an csv sheet which looks like this:

topic;format;text
comedy;A5;asdlakjsdlahsdasd
love;A6;laksdasdkhjasf

Now i want to insert the content of the csv.
How can I translate the csv contents like "format" to the format_ids in the formats table ?

Any help is much appreciated.

UPDATE:

Creating a tmp table and inserting the csv is easy.

create table tmp (
    id serial PRIMARY KEY,
    topic_id integer,
    topic varchar(255),
    format_id integer,
    format varchar(255),
    text text
);

copy tmp(topic, format, text) FROM 'test.csv' DELIMITER ';' CSV HEADER;

But how do I get the right ID's now to insert them into the text table?


texts: Is Database Mail worth the risk / a standard practice

texts:
I enabled Database Mail XPs and was testing it on my dev machine.  I was just wondering if the DB Mail is a bad security choice, and if it more secure (or standard practice) to send this kind of message via an application server (ColdFusion or PHP)?  I would be using the Database Mail for DB backup/restore messages, errors or error checking messages (that I can think of).  I am aware that enabling a feature always increases risk.

Using:

behind firewalls, etc..
Windows 2012 r2
MS SQL Server 2012
Exchange is on separate server
Using Windows Authentication
Enabled require secure connection

Thanks in advance.


texts: How to organize the tables of a MySQL database with several statistics data of several years?

texts:
I am developing an app that must provide statistics information about the distribution of the money that a person spends each year in taxes in the city where he lives. The user should select the interested city, a year for which the statistics data will be recovered and provide the amount he spent in taxes.

Then it should see for example that if he spent 4.000 dollars in taxes, the 45% of that amount (1800 dollars) was invested by the city for public education, the 20% (800 dollars) was invested in the improvement of public infrastructure, the 10% was invested in culture and the 25% was invested in administration.

I am using a MySQL database and my database structure is like follows:

I have one table 'Cities' containing the names of all the concerned cities of the statistics;
Another table 'Statistics_Entry' containing all the statistics entry names that will be used (for example, "public education", "administration", "public infrastructure", and so on...;
A lookup table 'Statistics' which binds the two tables explained before and contains the percentage of each Cities<->Statistics_Entry association (the primary key of the lookUp table consists of two INT fields, which corresponds respectively to the INT ID primary key column of the 'Cities' table and the INT ID primary key column of the 'Statistics_Entry' table.

So here is a simple explanaition of the db structure. But here comes the need of having different statistics results for different years.

At a first look I thought of using another column in the lookUp table named "year" which gives the proper year of the statistics data. But I am not sure if this is the best way.

So I am asking some advices on how structurizing this kind of information system.
Should I create another table 'Years' which will contain all the years of the statistics or is it better to store the years in a single field inside the 'Statistics' lookUp table?

Is the db structure I showed good or bad? What could be improved?

Thanks! Hope for some help!


texts: What happens if transaction rollback fails?

texts:
So failure while committing leads to rollback. But what if rollback also fails due to some reasons and the database has changed due to partial commitment. How is the database restored to its original state?

The database in question is postgresql.


texts: How to change default db2 database admin username to something else in Linux?

texts:
I have a DB2 database version 11.1 on Linux - rhel 7.7 in my test environment. I need help on how to change the default db2 admin username to something else, ie. db2inst1 to dbroot. And the new db2 user - dbroot should have the same privileges which old db2 user - db2inst1 had. Please share the steps to do the needful tasks.

Thanks in advance


texts: SELECT first value from 3 tables that is not null

texts:
Having 3 tables with name(varcahr) value(varchar) pairs how do I select the value for a given name from one of the table with respect to the following conditions

select value from tab_1 if it exists for name else select it from tab_2 if it exists in tab_2 else from tab_3
do it in one giant select if possible


texts: I know the differences between clustered and non-clustered indexes. Can someone tell me the various scenarios where we actually prefer non clustered index over a clustered index ?


texts: SSIS - XML with row delimiter to a single column of a SQL table

texts:
I'm trying to upload data from a CSV file into a SQL server table through SSIS package (Flat File source and OLEDB destination). This CSV has comma as column delimiter and {CR}{LF} as row delimiter. This csv file has one column which holds XML data in it. In this XML, they again used {CR}{LF} to make it as a multi-line XML and this XML is placed within a double quotes (""). Ideally, this whole XML needs to be placed in one column however {CR}{LF} makes it as a new row in my table.

Is there any method to have escape sequence and place the whole XML data into a single column?

I'm using Visual studio 2013 and connected to SQL server 2014 database.

Thanks.


texts: In InnoDB, does a Transaction imply any implicit locking of a table?

texts:
The Isolation level is REPEATABLE_READ.

The logic is as under:

Transaction begins
Read data from Table A
If (Table A has Any Data) End Transaction and exit
If Table A has No Data, Proceed further
Delete a record in Table B
Transaction ends

Now, my question is about the following scenario:

The current execution reaches at point #4.
I someone inserts data (Note, the current execution is at point # 4) in the Table A that would have returned some result in #2.
Now, The current execution will execute #5. It will delete a record that should not be deleted.

Is there any implicit locking in transaction or do I need to lock Table A Explicitly so no one can insert any data in Table A before I commit changes?


texts: Making production data accessible to developers via masking

texts:
We want to provide developers in our organization masked data from production to help troubleshoot production issues.
What would be the best way to approach it?
I've read this article https://docs.microsoft.com/en-us/sql/relational-databases/security/dynamic-data-masking?view=sql-server-ver15

But it seems to take a different approach from what I've envisioned. My idea was to have a separate database server that is replicated from the production database, and somehow mask the data during replication so that the real data will never reach the replicated database server. That way we don't have to have special security considerations about how to store and treat the replicated database server.

Is it a reasonable approach?


texts:
I am building this where condition by concatenating the @whereClause variable in my stored procedure. At this point I am using the case to add additional values. if there a better way to do this?
SET @whereClause  =
    ( CASE
        -- APPType Clinical
         WHEN (@ApplicationType = 'Clinical') THEN @whereClause + ' AND vpgl.AppType = Clinical'
         -- APPType Patients
         WHEN (@ApplicationType = 'Patients') THEN @whereClause + ' AND vpgl.AppType = Patients'
          -- APPType Web
         WHEN (@ApplicationType = 'Web') THEN @whereClause + ' AND vpgl.AppType = null'
         ELSE @whereClause
       END
    )


texts: Difference between DataLinks and External tables

texts:
There was a feature called DataLinks that allowed to access data in files from SQL. This was introduced in Db2 V5.2 and removed in V9.7. However, in v11.5 there is a "new" feature called external tables.

What is the difference between these two approaches?

Data Links Manage - http://users.sdsc.edu/~jrowley/db2/Data%20Links%20Manager%20QB.pdf
External tables -  https://www.ibm.com/support/knowledgecenter/en/SSEPGG_11.5.0/com.ibm.db2.luw.sql.ref.doc/doc/r_create_ext_table.html


texts: Is there any way to set stored procedure options like ANSI_NULLS to ON or OFF outside of creation?

texts:
I'd like to set ANSI NULLs and quoted identifier properties on a stored procedure after it has been created.  Is this possible?

For some context, I'm using Visual Studio Database Projects / SSDT, and it appears these values can only be set at the database level, not on the procedure level.  However, we're using an existing database with inconsistently created procedures, so I'd like to preserve the current behavior until we can normalize them.


texts: SQL Server : Merge and update multiple rows across columns

texts:
I am trying to consolidate data in one table and merge it into another.

The data in the source destination table is as follows :

name                |dob                        |city   |occupation
-----------------------------------------------------------------------------------
galileo-galilei     |1900-01-01 00:00:00.000    |rome   |polymath
issac-newton        |1900-01-01 00:00:00.000    |london |mathematician-scientist
leonardo-da-cinci   |1900-01-03 00:00:00.000    |rome   |polymath

The data in the destination source table is :

sl_no   |name               |dob                        |city   |occupation
-----------------------------------------------------------------------------
1       |galileo-galilei    |1900-01-01 00:00:00.000    |       |
2       |galileo-galilei    |1900-01-02 00:00:00.000    |venice |
3       |galileo-galilei    |1900-01-05 00:00:00.000    |       |astronomer

The expected result in the destination table is :

name                |dob                        |city   |occupation
-----------------------------------------------------------------------------------
galileo-galilei     |1900-01-05 00:00:00.000    |venice |astronomer
issac-newton        |1900-01-01 00:00:00.000    |london |mathematician-scientist
leonardo-da-cinci   |1900-01-03 00:00:00.000    |rome   |polymath

My attempts using update-with-join and merge have not been successful.

Update-With-Join :

-- updates data from the first match only
update p
set p.city = s.city,
p.occupation = s.occupation
from person_update_with_join_test_primary p, person_update_with_join_test_secondary s
where p.name = s.name ;

Merge :

-- https://technet.microsoft.com/en-us/library/bb522522(v=sql.105).aspx
/*
The MERGE statement attempted to UPDATE or DELETE the same row more than once.
This happens when a target row matches more than one source row.
A MERGE statement cannot UPDATE/DELETE the same row of the target table multiple times.
Refine the ON clause to ensure a target row matches at most one source row, or use the GROUP BY clause to group the source rows.
*/
begin
merge person_update_with_join_test_primary as p
using person_update_with_join_test_secondary as s
on (p.name = s.name)
when not matched by target
then insert (name, dob, city, occupation)
values (s.name, s.dob, s.city, s.occupation)
when matched
then update set p.dob = s.dob
, p.city=(case when (len(s.city)>0) then  s.city else p.city end)
, p.occupation=(case when (len(s.occupation)>0) then  s.occupation else p.occupation end)
output $action, inserted.*, deleted.*;
end

I believe what I am looking for is similar to what has been posted here and here. However its not quite what I am looking for.

Are there any other ways to accomplish this other than using a cursor and an upsert (assuming that works)?

Update #1 :

Basically as long as the values in the source are not empty the most recent values (having highest id value) in the source are expected to be merged into the destination.

Eg : For #3 row in the source, the city column would not be considered to be merged into destination. Similarly for #2 , the occupation  column would not be considered for being merged into destination.
The the column name is a primary-key in the destination table.

I am trying to achieve the same state in the destination table as would have been expected if I were to iterate over the source data and update only the non-empty values in the destination - by using a query instead of doing it through application.


texts:
There's standalone MySQL server 5.5 with no slaves. Daily dump is created using mydumper tool.

Is there any way to verify created dump (all tables are created, integrity, etc.) without restoring dump somewhere, using slaves, etc..?


texts: Can I artificially limit the Columnstore cache size in SQL Server?

texts:
I am trying to evaluate the potential performance of a data science workload on a large-ish dataset (~200GB).  We have had excellent results using columnstore indexes in SQL Server 2017.

However, the business is interested in cost savings, and I have pointed out to them that columnstore indexes are now available in Standard Edition, albeit with a memory limit of 32GB.  It's possible to determine the current memory use by looking at DMV sys.dm_column_store_object_pool

I would like to know if it's possible to limit the columnstore-dedicated RAM allocation to evaluate how the workload performance changes.

I'm aware that it's possible to limit the total RAM usage in SQL Server, but that will not provide a real indication of the very specific columnstore limits.

I should clarify that since we are currently using Developer Edition, there are no resource limits at all.


texts: Is there a standard approach to rolling out database schema changes?

texts:
A developer said I should simply wrap all my database upgrade scripts in a transaction.  If it fails, just roll back all changes.  All my instincts tell me this is wrong, especially when it comes to dealing with large volumes of data and/or procedures and functions.

I typically hand-hold the upgrade process on single instance databases as follows:

Negotiate a maintenance window
Prepare upgrade scripts
Put database into restricted user mode
Disable scheduled jobs/processes that would normally hit the database
during this window
Perform a full backup
Apply the upgrade scripts
Have developer or test team confirm the app operates as expected
Put database back into multi-user mode
Release the database for normal use

When it comes to rolling out changes to several hundred instances, however, I have changed my process as follows:

I make the upgrade scripts much more robust: they can be run multiple times on the same server without harm, database version numbers are repected, scripts will terminate if they are run against the run version, etc.
spawn a process for each server (using powershell, osql, etc)
run the appropriate upgrade script
report success or failure


texts: How do database operations (write, update, alter) on particular cell in a table are written to disk without overwriting entire file?

texts:
When I want to write to or alter particular cell of an excel file/table using python I'll use pandas read_csv then alter value in particular cell and write back to file with to_csv. But writing back to file seems to be overwriting the entire file with updated version of the file that differs only in single cell. This is a problem when am altering one or two cells in table of trillion rows and trillion columns.

When we are making write/alter database operations (like in SQL) on single cell out of trillion by trillion table, it seems to be making changes in the disk to only the cells that are modified rather than overwriting entire files.

How do databases facilitate writing/updating only particular cell in a table in the disk rather than overwriting entire file?

BTW, am not using SQL database as my table contains numerical column names and SQL doesn't support that. If you know any SQL/NOSQL database that supports numerical values as column names please let me know.


texts:
Hi I tried implementing the below stored procedure which always takes my if condition as true and implements .

CREATE DEFINER=`root`@`localhost` PROCEDURE `InsertOpen`(in uid varchar(11),in S_ID varchar(20))
BEGIN
    if(SELECT COUNT(Slot_ID) FROM smart_scheduler1.work_schedule where Employee_ID=uid AND Schedule_ID=S_ID = 0) THEN

        UPDATE `smart_scheduler1`.`work_schedule` SET `Employee_ID`=uid WHERE `Schedule_ID`=S_ID;
        ELSE
        SELECT COUNT(Slot_ID) FROM smart_scheduler1.work_schedule where Employee_ID=uid AND Schedule_ID=S_ID;

END IF;
END

My question is how do I compare a query with a value or integer in stored procedure if condition?

I am novice in Database , please help me. I would be very pleased if you could get me a good tutorial on stored procedure as I was not able to find much materials on stored procedures online.


texts: What is the correct way to map relationships between structurally similar comments tables and commentable entities?

texts:
I'm working on the usual comments table in postgres that has a one-to-many relationship with a commentable entity. In other words, one comment belongs to a post, and a post can have many comments.

The problem is that I have multiple commentable entities. My initial thought was to create a table for each entity, like posts_comments, and profile_comments, but that just feels wrong as all the comments are structurally similar. If I decide to allow commenting on say, user-posted pictures, then I would need to add another picture_comments table with identical columns. And then if I decide to add or remove any column, I would have to modify all the tables for consistency.

So I'm considering whether I should instead have a single comments table and then maintain referential integrity through intermediary join tables with a unique index on the comment_id foreign key to ensure the one-to-many relationship. Are there any performance or size-related downsides from doing this?

What is the best way to handle this in postgres or SQL in general? I looked around for solutions or clues on stackoverflow and dba exchange but did not find a satisfying answer. If someone can provide an answer or opinion or a link to an already answered question, I would really appreciate it.


texts:
I want to have a script monitoring MySQL Service for running export.

I thought of monitoring linux process list for services like mysqldump or mysqldumper. But i think this is not so reliable since there could be other tools (e.g. phpmyadmin, adminer, MySQL Workbench, etc...) used for export.

But are there any more reliable ways of monitoring database exports, or selecting really huge datasets like in an export scenario?


texts: Order By with duration strings having time units

texts:
I have a SQLite database with a table "Rate" who has a column "timeToMaturity". This column contains strings such as :

7Months
26Days
3Years

I'd like to order a query results by timeToMaturity (i.e. 26Days < 7Months < 3Years). However, because they are strings, I have no idea how I should proceed.

Thank you for your help


texts: How to join multiple tables (SQL) to find the difference between an order and shipped quantities?

texts:
I have a collection of orders that were submitted to an internal ERP, which were processed and partially shipped.

The table hierarchy is as follows:

Order table contains OrderReference(PO), OrderID
OrderItems table contains Product/Lot/Qty requested (OrderID is parentID that joins these tables)
Shipping contains OrderReference(PO), ShippingID
ShippingArchive contains Product/Lot/Qty that was shipped (ShippingID is parent that joins these tables)

I want to create a report that looks as follows:

OrderReference#

Order Requested / Shipped Items / Missing Pieces

PC/Lot/Qty    -   PC/Lot/Qty    - PC/Lot/Qty

I have used a temp table to get some of the data but I realize I'm taking the wrong approach. I should be able to do all of this with a single query, though it will seemingly be a bit complex.

I get held up when I try to think about how to join where pieces don't exist, and where to perform the mathematics in my sorting and filtering. Please help!

Sincere thanks :)


texts: Can we change Log Shipping backup file naming convention

texts:
Seems the Log Shipping backup files naming convention (especially the date-time stamp in the name) is followed by UTC time, i believe that is default and standard.

I would like to clarify on following cases:

I'm wondering if there any possibility to change the naming convention to follow the date-time as per local time-zone from the server where the BACKUP performed.
Is the naming convention (UTC time stamp as suffix) really matter, since anyway the Log Shipping copy/restore job considers the LSN between each backup file to maintain valid log backup chain.

The aim is to avoid issues during compressing process that i'm following to maintain one zip file per day, and as per following screenshot it would combine additional files (20191015xxxxx) which are belongs different date (2019-10-16)


texts:
I was trying to install postgresql by this tutorial http://powa.readthedocs.io/en/latest/quickstart.html to use the tool for bd Powa.

I am use S.O. debian 8 Jessie.

I ran:

 apt-get install postgresql-9.6 postgresql-client-9.6 postgresql-contrib-9.6
 apt-get install postgresql-9.6-powa

So far so good, the DBMS has gone up and I can create tables etc.

But I'm not finding where the postgresql.conf file is.
I tried searching using locate postgresql.conf, but can not find, I suspect it was not created.
I need to add some libraries in the parameter: shared_preload_libraries

Has anyone installed postgresql in this way, and could it tell you where the postgresql.conf file might be, or another one to configure the shared_preload_libraries parameter?


texts:
One of our Production database has a rather peculiar block size of 16KB. This database was created about 2 years ago and at that time the size of the db was approx 1TB only.However over the past 2 years the db size has grown to approx 5TB.

The workload is a mix of OLTP (transaction) as well as OLAP (reporting).

This db block size is causing our DB Buffer Pool to be constantly filled up (95%~100% full).The db uses ASM on Raw Devices.

Is the 16KB db block size sub-optimal?Can this large block size be adversely impacting our storage sub-system performance?If so what can be done to remedy the situation?

Noteworthy here is that we do in fact perform huge full table scan for reporting as well as for our application (business logic).The OLTP nature stems from the fact that our application also has transactional (financial transaction) module.


texts: How to implement a "Command T" (VIM) -like search

texts:
I am thinking of implementing a string search similar to the command-t search in VIM. The context is almost the same: Finding Files in a Webapp.

It's not easy to explain how the command-t search works in a nutshell, so I try to give an example:

The following string:

/documents/companyname/contracts/staff/johndoe.pdf

searched by:

dpanystadoe

matches in that way:

/documents/companyname/contracts/staff/johndoe.pdf

This only makes sense in combination with a live autocomplete search, so it has to be really fast :).

Of course, the most naive approach is to use wildcards and build a search term like this: "%d%p%a%n%y%s%t%a%d%o%e%" but we all know this is a bad idea as long as the data to query grows.

Any ideas how to start? Are there better ways to prepare the data? Which indexes could help?


texts:
What's the best practice approach to storing historical records?

I have an entity whose rows will constantly get updated. I need to keep track of each row's history. There will be about 40K "current" records at any given time and each of them will be updated on a daily basis for about 60 days, then the updates will stop.

I see two options here:

All current and historical records in one table - since each row will have an id that will allow me to identify which records are part of the same group.
Current and historical records in separate tables - each time the current record is updated, a copy of it pre-update is put into the history table. The history table would have a FK.

Is there another option I haven't considered? Which option is the more appropriate approach to what I am trying to do?


texts: If XML child element value contains string return other child element value at same level

texts:
I have a database table named Warehouses that contains rows of data related to specific warehouses. One column in that table is named itemStats and contains XML data in the following format:

<itemStats xmlns="http://www.blahblahblah.com/blah">
  <itemCounts>
    <item>
      <name>Toaster Oven Device</name>
      <count>6</count>
    </item>
    <item>
      <name>Washing Machine</name>
      <count>2</count>
    </item>
    <item>
      <name>Microwave Oven</name>
      <count>4</count>
    </item>
  </itemCounts>

I'd like to query the table to check that column in each row for the existence of some string (example: Oven) and if it finds it, return the count associated with it. So if I had the above data in itemStats  for a given row in Warehouses, I'd like it to return the following along with other matches from other rows:

Name                Count
=========================
Toaster Oven Device     6
Microwave Oven          4

I've attempted several times using SQL value() and nodes() but am having trouble producing the desired results. I know the [1] is wrong in the below query, but I'm not sure how to reference a dynamic location:

;WITH XMLNAMESPACES (N'http://www.blahblahblah.com/blah' as X)
SELECT itemStats.value('(/X:itemStats/X:itemCounts/X:item/X:name)[1]', 'nvarchar(max)') as Name,
       itemStats.value('(/X:itemStats/X:itemCounts/X:item/X:count)[1]', 'int') as Count
FROM Warehouses
WHERE itemStats.exist('(/X:itemStats/X:itemCounts/X:item/X:name[contains(., "Oven")])') = 1


texts: Nonclustered index corruption resulting in minidump - what could be the reason? (SQL Server 2017 CU21)

texts:
So today i received alerts from our AlwaysOn cluster about replica failover. After investigating the case, looking through sql logs, cluster logs, xe sessions i found out that prior to the failover attempt a minidump was generated on the primary replica. The reason for that was apparently
* CPerIndexMetaQS::ErrorAbort - Index corruption

I also received a SQL Agent alert containing info
Unable to find index entry in index ID bla, of table bla, in database 'BLABLA'.

After further investigation I found out that the issue occurred when someone executed a script containing a fairly large block of UPDATE statements in a single transaction across a lot of different databases (which I believe means a distributed transaction).
Then I found this post: https://www.stellarinfo.com/blog/handle-non-clustered-index-corruption-in-sql-database/
Now, the script I've mentioned didn't contain NOLOCK hints, but our databases have RCSI turned on.  So I wonder if maybe the RCSI can induce the same behavior. Could it be the root cause of the index "corruption" effectively causing the minidump generation and in turn the AlwaysOn failover attempt due to temporal "freeze" resulting in lease timeout?
Is my thinking correct or should I look elsewhere?


texts:
In the logs I found slow queries, but when running them manually it is fast or at least taking indexes into account.
The logs tell me the query part is not using an index, which is available.
Running the query takes the index into account.
Running on AWS DocumentDB (which is MongoDB 3.6 mostly compatible).
The logs tell me this (anonymized field names for the internets ;-) ) :

{"op":"update",
"ts":1578041832906,
"ns":"collection",
"command":{
  "q":{"array.field1":{"$binary":"Hzy2Q0TBYEAWadeCtxpXuA==","$type":"3"}},
  "u":{"$set":{"array.$.field2":174}}},
  "nMatched":0,
  "nModified":0,
  "protocol":"op_query",
  "millis":694,
  "planSummary":"IXSCAN",
  "execStats":{
    "stage":"UPDATE",
    "nReturned":"0",
    "executionTimeMillisEstimate":"694.497",
    "inputStages":[{
      "stage":"LIMIT_SKIP",
      "nReturned":"0",
      "executionTimeMillisEstimate":"694.488",
      "inputStage":{
        "stage":"COLLSCAN",
        "nReturned":"0",
        "executionTimeMillisEstimate":"694.486"
      }
    },{
     "stage":"IXSCAN",
     "nReturned":"0",
     "executionTimeMillisEstimate":"694.492",
     "indexName":"_idDocField3",
     "direction":"forward"
    }]
  },
}

When running an explain for this on the update

collection.explain().update(
    {"array.field1": BinData(3, "Hzy2Q0TBYEAWadeCtxpXuA==")},
    {$set: {"array.$.field2":174}}
)

the queryPlanner shows me this

{
  "queryPlanner": {
    "plannerVersion": 1,
    "namespace": "collection",
    "winningPlan": {
      "stage": "UPDATE",
      "inputStages": [
        {
          "stage": "LIMIT_SKIP",
          "inputStage": {
            "stage": "FETCH",
            "inputStage": {
              "stage": "IXSCAN",
              "indexName": "arrayField1"
            }
          }
        },
        {
          "stage": "IXSCAN",
          "indexName": "_idDocField3",
          "direction": "forward"
        }
      ]
    }
  },
  "serverInfo": {
    "host": "xxxxxxx",
    "port": 27017,
    "version": "3.6.0"
  },
  "ok": 1
}

Indexes available are:

_id_ { _id:1 }, ops: ~56,000,
_idDocField3 { _id: 1, docField3: 1 }, ops: ~4,200,000,
arrayField1 { "array.field1": 1 }, ops: ~400,
arrayField1DocField3 { "array.field1": 1,  docField3: 1 }, ops: 0

array is an array containing a document and the ids are UUID.
The collection size is about 20,000,000

Could it be an idea to remove the compound indexes and rely on index intersection?
Or switch the order of the compound indexes?
Anyone who can help me optimizing this?


texts: Postgres: turn off formating for xml export?

texts:
I want to export some table to xml file:

\o output.xml
SELECT table_to_xml('modified_records', TRUE, TRUE, '');

However, I'm struggling with psql formatting. The best I managed to get is:

\t on
\x on
\pset format unaligned
\o modified_records.xml
SELECT table_to_xml('modified_records', TRUE, TRUE, '');
\o

Which results in

table_to_xml|<modified_records xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <tag1>val1</tag1>
  <tag2>val2</tag2>
</modified_records>

<modified_records xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <tag1>val1</tag1>
  <tag2>val2</tag2>
</modified_records>

<modified_records xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <tag1>val1</tag1>
  <tag2>val2</tag2>
</modified_records>

So I still have to remove table_to_xml|. Which is not hard:

cat modified_records.xml | sed '1s/^table_to_xml|//'

# or even in psql:
\o | sed '1s/^table_to_xml|//' > modified_records.xml

but feels like fighting with Postgres.

Is there a way to dump just the value?

(there are plenty of options for \pset, but I can't find its docs)


texts: Fastest way of updating a spider web of data

texts:
I have a large table with over 60 million rows, which has relational data inside of it.  I need to update each of these rows to the newest date, of the newest member, including members that that member is a part of.  The problem I'm having is my queries are too slow, especially when I need to run them in a loop until everything is updated.
Example:

id
type
refid
reftype
timestamp

1
Team
1
Person
2021-04-03

1
Team
2
Person
2021-03-31

2
Team
2
Person
2021-04-01

2
Team
3
Person
2021-04-02

1
Group
1
Team
2021-04-03

2
Group
2
Team
2021-04-05

3
Team
4
Person
2021-03-31

In the above data, all rows except the last one would be updated to "2021-04-05", because Group 2 contains Team 2, and Team 2 contains Person 2, which is also a member of Team 1, which is a member of Group 1.  Same thing would have happened if Person 2 was the newest date. So you can see the confusion and the massive growth of possible connections. In the end I just need to expire anything over a week.
Currently I run a loop of the two queries below until both return 0, but this can take a ton of passes and take over 10+ minutes each time for the second query.  I have combined indexes, but It seems the need to process the sheer 16GB data size takes a while.  Am I missing something, or is there a better way? Appreciate any help!
update table_name
            left outer JOIN
            table_name AS t2 ON t2.refid = table_name.refid AND t2.reftype = table_name.reftype AND t2.TIMESTAMP >= DATE('2021-04-01')
            set table_name.timestamp = t2.timestamp
            WHERE
            t2.timestamp IS NOT NULL
            AND date(t2.timestamp) > date(table_name.timestamp)
            AND table_name.TIMESTAMP < DATE('2021-04-01');

followed by
update table_name
            left outer JOIN
            table_name AS t2 ON t2.id = table_name.id AND t2.type = table_name.type AND t2.TIMESTAMP >= DATE('2021-04-01')
            set table_name.timestamp = t2.timestamp
            WHERE
            t2.timestamp IS NOT NULL
            AND date(t2.timestamp) > date(table_name.timestamp)
            AND table_name.TIMESTAMP < DATE('2021-04-01');

Using 10.5.9-MariaDB, but can switch/upgrade to whatever DB is best for the job.  Analyze statement shows indexes are being used.  timestamp field is datetime, but example shows date only for simplicity.


texts:
Given a table with columns A, B, and C of type CHAR(15) and an unique index

If I have a query with a WHERE clause specifying A and C, oracle's optimizer chooses to do an index range scan. But if I know that the cardinality of B is low, should I hint oracle to use an index skip scan? Will a skip scan be more performant skipping a non-leading column (in this case, B) than a range scan? Will the skip scan skip both A and B in the index or will it just skip B?


texts:
I'm trying to optimize a query (instead of repeating it a lot of time), with this NOT FUNCTIONAL CODE above (since subqueries only return 1 column):

SELECT
    e.pageview_identifier,
    e.created_at,
    e.pageview_current_url,
    e.pageview_mobile,
    (
        SELECT event_type, COUNT(event_identifier)
        FROM events v
        WHERE
            v.company_identifier = e.company_identifier AND
            v.user_identifier = e.user_identifier AND
            v.pageview_identifier = e.pageview_identifier
        GROUP BY v.event_type
    )
FROM events e
WHERE
    company_identifier = 'xyz' AND
    user_identifier = '01CDQZVSJFBDA8W444JS2CS3BA' AND
    event_type = 'page:view';

Basically, I want to retrieve the columns as

pageview_identifier, created_at, ..., event_type_a_count, event_type_b_count, ...

A FUNCTIONAL code that works is:

SELECT
    e.pageview_identifier,
    e.created_at,
    e.pageview_current_url,
    e.pageview_mobile,
    (
        SELECT COUNT(event_identifier)
        FROM events v
        WHERE
            v.company_identifier = e.company_identifier AND
            v.user_identifier = e.user_identifier AND
            v.pageview_identifier = e.pageview_identifier AND
            v.event_type = 'mouse:move'
    ) as mouse_move_count
FROM events e
WHERE
    company_identifier = 'xyz' AND
    user_identifier = '01CDQZVSJFBDA8W444JS2CS3BA' AND
    event_type = 'page:view';

But in this case, I would need to repeat a lot of time this subquery for each kind of event_type.


texts: Is automatic failback on Postgres 8.4 possible via log shipping/PITR?

texts:
I have successfully setup continuous archiving and recovery of a master and slave node with PostgreSQL 8.4 via log shipping. I'm using keepalived to monitor both nodes and I can successfully trigger failover.

If I immediately bring the old master back online after the failover, such that log shipping immediately resumes, the master just sits there waiting for a WAL file that never arrives. It appears to be a timeline issue where the old master is  looking for WAL files from what it knew the last timeline to be, but by design, the new master advanced the timeline.

I've seen some related discussions that seem to indicate that automatic failback isn't possible but they seem to involve later versions of postgres and streaming replication.

Is automatic failback possible, assuming the log shipping resumes as it should, or would one have to redo the process of making a base backup, copying it to the old master, then resuming log shipping?

EDIT: It occurred to me that I didn't mean automatic failback so much as I want the old master to rejoin the cluster as a standby.

Thanks


texts: SQL estimates are way off on DELETE statement with Triggers on huge tables

texts:
I'm working with Microsoft SQL Server 2016 (SP2-CU11) (KB4527378) - 13.0.5598.27 (X64)   Nov 27 2019 18:09:22   Copyright (c) Microsoft Corporation  Standard Edition (64-bit) on Windows Server 2012 R2 Standard 6.3  (Build 9600: )

This server is on SSD drives and has a max memory of 128 gb. CostTheshold for Parallelism is 70, MaxDegree of Parallelism is 3.

I have a "Trips" table which is referenced by 23 foreign keys with the ON DELETE CASCADE option.

This table by itself is not that big (5.3 millions rows, 1.3 gb of data). But of the 23 referenced tables, two of the tables are quite big (more than 1 billions rows, 54 and 69 gb each).

The problem is when we try to delete a small amount of rows in the "Trips" table (let's say 4 rows), SQL estimates so much rows are going to be deleted, it asks for 10gb of RAM, estimates millions of rows will be returned, and locks the table. All goes to a halt and other queries block and the application time outs.

Here are the main tables and the row count for 1 delete statement:

Trips (4 rows)
Segments (27 rows, related to Trips by SegmentId)
Profiles (2012 rows, related to Segments by SegmentId)
ProfileRanges (2337 rows, related to Profiles by ProfileId)
Events (7750 rows, related to Segments by SegmentId)
EventConditions (9230 rows, related to Events by EventId)

Tables EventConditions and ProfileRanges each have more than 1 billion of rows.

Here is the plan cache : https://www.brentozar.com/pastetheplan/?id=HJNg5I0BU

When I look in SentryOne plan explorer, I can see that SQL is reading the whole table even if the "Table spool" then filters and keeps only for 2012 rows ProfileRanges and about the same for EventConditions.

When I look at the memory grant of the query with Brent Ozar's sp_blitzCache procedure, I can see that the query asks for about 10gb of RAM.

After that, the query is either waiting on SOS_SCHEDULER_YIEL (so waiting for it's turn to use the CPU after the 4ms) or MEMORY_ALLOCATION_EXT. The program times out and fails.

What can I do to make this work?

One of the thing I was thinking of, was removing the foreign keys on the two biggest table and delete their rows in an instead of trigger. But I'm not a big fan of enforcing database consistency with triggers instead of foreign keys.

Any advice or help will be appreciated

Primary Key of ProfileRanges is

ProfileId    int
ProfileRangeDefId1   int
ProfileRangeDefId2   int

Primary key of EventConditions is

EventId bigint
EventConditionDefId  int


texts: what are the impacts if I convert one of the column to primary key or add a primary key to an existing table

texts:
One of my MSSQL table don't have any primary key, I would like to add one. Just want to know:

First, if I convert an existing column (or multiple columns) to a primary key, what would be the impact? Will it affect the web applications rely on this table?

Furthermore, what if I add a new column and make it primary key on an existing table?

Last but not least, if none of the above is a good practice, what would be one? Creating a new table, migrating data and deleting old one and renaming the new table to old name?

the applications using this table is nothing but some CRUD operations.

Thank you in advance.


texts: Converted ERD into Dependency Diagram 1NF, 3NF

texts:
Thats the ERD.

I'm studying for an upcoming test and having trouble getting my head around converting thes ERDs into dependency diagrams. This is from a previous exam that the Lecturer told us to study just btw so he said it will be pretty similar to this.

We have to -

-Convert the ERD into a dependency diagram.
-And then, conver that diagram into a 3NF model, with no transitive or partial dependices.

Any help would be much appreciated as I'm struggling to get my head around it and need to pass this test :/

Thanks in advance!


texts: User assignment to various roles of changing events

texts:
We have numerous events throughout the year that are supported by multiple teams.  Team A will support the entire event duration while Team B will support segments (typically per week).  It is common for the event start date and duration (typically 10-30 days) to shift independent of other events creating a problem tracking gaps and overlap in coverage of these events.  We want to create a central schedule so each team can see how event changes creates gaps and/or overlaps in assignments.

Events

Event   StartDate   Duration (Days)
E1      someDate1   25
E2      someDate2   14
E3      someDate3   22

Teams

Team    User
A       1
A       2
A       3
B       4
B       5
B       6

Assignments

Event   User    DayStart    DayEnd
E1      A1      1           NULL
E1      A2      1           NULL
E1      B4      1           7
E1      B6      15          NULL
E2      A2      1           NULL
E2      B7      1           7
E2      B4      1           7
E2      B5      8           14
E3      B5      1           7
E3      B6      8           14

The above example data has a gap for Team B during Event 1 (days 8-14) and Team A has no coverage for Event 3.

The thought is to have a single assignment table where DayEnd being NULL signifies that the individual is covering the remainder of the event.  Is this a reasonable way to structure the tables?  Would it be better to have separate tables for each team (less than five total teams)?  Should NULL be replaced with some sufficiently high number such as 999?  Each team will also have role assignments within events, but believe that will just be an additional table and not relevant to this question.

Disclaimer:  this question was asked on stackoverflow.com without response


texts: Disadvantage of Changing database schema - Insert or delete new columns/tables in middle

texts:
As a read in lot of links , for e-commerce shopping sites, as it may require to add lot of attributes in regular basis, is it okay if we go with Flat tables ?

Whats the disadvantage of changing database schema or inserting/deleting new columns & tables in middle of the project ?

also Is there any problems if we keep lot of NULL values in database ?

As i heard EAV is Evil , I want to create shopping site with flat tables....


texts:
Generally bottleneck of RDBMS (I am a MySQL user) performance is disk access. SSD provides great performance compared with conventional spindle drives.

Question : Is it possible to improve performance by attaching multiple drives of reduced space, because this way more heads will be available to read data?

Like replacing 2TB 7.2k RPM drive with 4 500GB 7.2k RPM drives?


texts: Does firing off an indefinite WAITFOR increase the log file size?

texts:
In the last release of my app, I added a command that tells it to wait when something arrives in the Service Broker queue:

WAITFOR (RECEIVE CONVERT(int, message_body) AS Message FROM MyQueue)

The DBAs tell me that since the addition, the log sizes have gone through the roof. Could this be correct? Or should I be looking elsewhere?


texts:
I've got a one to one relation from users to addresses table.
Where one user can have one search address and one verified address.

I've got two indexes on addresses table :

Index on state field
Index on user_id

I'm trying to fetch addresses for certain users only, and those addresses whose state is other than manual_verification.

Here is my query:

SELECT users.id
FROM "users" INNER JOIN addresses
     ON  addresses.user_id = users.id
     and addresses.type = 'VerifiedAddress'
WHERE ("users".deleted_at IS NULL)
  AND (users.id in (11144,10569,21519,783,15671,21726,17787,11665,
                    19579,12226,1324,9413,5461,20981,12906)
  and addresses.state != 'manual_verification')

Explain for the query above :
http://explain.depesz.com/s/rTj

It takes 37 ms. Sometimes more depending on the number of users.

I think this is a good query, however our team needs to investigate around this, and I'm looking for some optimization tips. I mean I did the one field select, there is a index on user_id (addresses) and state (addresses).

Is there anything else I can do/try?

Update

I found out that this query works much faster:

 SELECT "addresses"."user_id"
    FROM "addresses"
    WHERE "addresses"."type" IN ('VerifiedAddress')
    AND (user_id in (9681,23824,23760,20098,962,14730,12294,9552,534,
                     553,5837,6768,6583,956,24179) and state != 'manual_verification')

Explain for this query:
http://explain.depesz.com/s/nHrr


texts: Does PostgreSQL consider how evenly data is distributed on disk when choosing a seq scan?

texts:
I have a table items with an indexed column type_id.  The table contains about 80 million rows.  At the moment the first 5 million (in order of insertion) and the last 3 million have type_id = 8, and all others have type_id = 12.

I run a query with where type_id = 8 limit 10.  Sometimes it takes a fraction of a second, and sometimes it takes minutes.  Here is explain output:

inventory=# explain select id from items where type_id = 8 limit 10;
                                        QUERY PLAN
------------------------------------------------------------------------------------------
 Limit  (cost=0.00..5.07 rows=10 width=118)
   ->  Seq Scan on items  (cost=0.00..4014534.00 rows=7915149 width=118)
         Filter: (type_id = 8)
(3 rows)

inventory=# explain select id from items where type_id = 8;
                                               QUERY PLAN
---------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on items  (cost=182418.97..3251192.33 rows=7915149 width=118)
   Recheck Cond: (type_id = 8)
   ->  Bitmap Index Scan on items_db97e9bb  (cost=0.00..180440.18 rows=7915149 width=0)
         Index Cond: (type_id = 8)
(4 rows)

Looking at the answers to other questions here, I conclude that the query planner chooses a seq scan because of the low limit, because it expects to find 10 matches from the first ~100 records it scans.  But in my case, the matching records are not evenly spread on disk - they are at the very beginning and at the very end of the table.  Does the query planner take that into account?

Edit: here is my postgresql.conf (only listing changed values):

max_connections = 2000
shared_buffers = 3GB
effective_cache_size = 9GB
min_wal_size = 10GB
max_wal_size = 10GB
checkpoint_completion_target = 0.9
default_statistics_target = 10000
work_mem = 100MB
maintenance_work_mem = 2GB

The server has 16GB memory, uses Amazon EBS "General Purpose SSD" storage, and this database is the only resource-intensive thing it runs.

Edit2: I have just run analyze verbose items;, and the query planner still chooses the same plans.


texts: Database Mirroring setup does not work when the database is above a certain size, logs out of synch

texts:
I have a Microsoft database running in production of about 300gb, I need to transfer and set it up in a mirrored configuration on two different servers.

This has worked fine in my tests using smaller databases, but now I run into the following issues while trying to use the actual production database. It works about 1 out of 3 times, the rest of the times it ends in errors.

When trying to enable mirror on the primary it gives this error:
"The remote copy of database "myDatabase" has not been rolled forward to a point in time that is encompassed in the local copy of the database log."

If I then try and take a new backup of the database log and restore on the mirror:
"Msg 4305, Level 16, State 1, Line 1
The log in this backup set begins at LSN 64612000000218000001, which is too recent to apply to the database. An earlier log backup that includes LSN 64612000000217800001 can be restored.
Msg 3013, Level 16, State 1, Line 1
RESTORE LOG is terminating abnormally"

Here is what I am doing, it is also very time consuming and will require a lot of downtime, so if there is an easier way I would be very happy:

Server A is the current production server where a single instance of the db is running.
Server B is the initial primary database server
Server C is the initial mirror database server

I break the mirror from Server B and delete the databases on Server B and C.

ALTER DATABASE produktion SET PARTNER OFF

I take a backup from Server A and store it in a shared folder on Server B.

BACKUP DATABASE [myDatabase] TO  DISK = N'\\Server B\share\myDatabase.bak' WITH NOFORMAT, INIT,  NAME = N'myDatabase-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

I restore from the backup on Server B.

RESTORE DATABASE [myDatabase]    FROM DISK = '\\Server B\share\myDatabase.bak'    WITH
MOVE 'mdflogicalfilename' TO    'D:\DEFAULT_2012_DATA\mdffile.mdf',
MOVE 'extralogicalfilename_extra' TO    'D:\DEFAULT_2012_DATA\extralogicalfilename_extra.mdf',
MOVE 'logfilename_log' TO    'E:\DEFAULT_2012_LOG\logfilename_log.ldf',
NOUNLOAD, REPLACE, STATS =5;

I take a new backup on Server B (unsure if this can be avoided?)

ALTER DATABASE 'myDatabase' SET RECOVERY FULL;
BACKUP DATABASE 'myDatabase'
TO DISK = '\\Server C\share\myDatabase_full.bak'
WITH INIT, FORMAT, STATS = 10;

BACKUP LOG 'myDatabase'
TO DISK = '\\Server C\share\myDatabase_log.bak'
WITH INIT, FORMAT, STATS = 10;

I restore from the new backup and log on Server C with NORECOVERY

RESTORE DATABASE "myDatabase"    FROM DISK = '\\Server C\share\myDatabase.bak'    WITH
MOVE 'mdflogicalfilename' TO    'D:\DEFAULT_2012_DATA\mdffile.mdf',
MOVE 'extralogicalfilename_extra' TO    'D:\DEFAULT_2012_DATA\extralogicalfilename_extra.mdf',
MOVE 'logfilename_log' TO    'E:\DEFAULT_2012_LOG\logfilename_log.ldf',
NOUNLOAD, REPLACE, NORECOVERY;

RESTORE LOG "myDatabase"    FROM DISK = '\\Server C\share\myDatabase_log.bak'  WITH
NORECOVERY, STATS = 10;

Enable Mirroring on the initial mirror:

ALTER DATABASE myDatabase SET PARTNER = 'TCP://Server_B_FQN:myPort'

Enable Mirroring on the initial primary (this is where it fails):

ALTER DATABASE myDatabase SET PARTNER = 'TCP://Server_C_FQN:myPort'

The whole procedure takes about 7-8 hours, I have created each step as agent jobs to start the next one, but if something can be done faster it would help a lot.

It works about 1 out of 3 times, the other times I get the above errors from which I cannot seem to do anything but start over and hope it works.

Issue doesn't happen with small databases, so the issue is probably that the restore takes so long on Server C, that the DB on Server B some times creates new logs that are then out of synch, this would also explain that it works some times, but there are no changes comitted to the database doing the time.

Is there any way to restore all incremental logs and make sure the databases are in synch for mirror without restoring a full backup of the database?


texts: Postgres: Is migrating old records to separate table and creating new aggregated records a good strategy to improve search speed?

texts: I have a table with 1M+ records. New records are created and updated each day each time an event occurs for each type of event (which there are many). I frequently need to find sums across many records and the time to perform these queries has progressively gotten slower, even though there are multiple indexes in places. Since there are now a couple years of data being stored, I am considering migrating records that are older than 6 months to a separate "archive" table and creating new records for each event type that includes the monthly aggregates (meaning the sum of the rows in the 31 records stored for January 2014 would be stored in 1 record). This will hopefully improve search speed, but is there a better strategy? Is this type of archiving common?


texts: Debugging table permissions for a windows user

texts:
We have a SQL Server 2008 Database.

I created a few Windows users, added them to a specific database, and granted them read write access with the scripts below:

CREATE LOGIN [PRUDENTIAL\X208476] FROM WINDOWS
    WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english];

Use client
GO
CREATE USER [PRUDENTIAL\X208476] WITH DEFAULT_SCHEMA=dbo
GO
exec sp_addrolemember db_datareader, [PRUDENTIAL\X208476]
GO
exec sp_addrolemember db_datawriter, [PRUDENTIAL\X208476]
GO

They run a basic insert command as follows and receive the error shown below:

INSERT INTO [client].[dbo].[admin_user_role]
           ([user_id]
           ,[role_id]
           ,[last_acty_oper_id]
           ,[last_acty_dtm])
     VALUES
           ('X208480'
           ,'ADMIN'
           ,'x163798'
           ,'2017-04-06 11:12:19.597')

Msg 229, Level 14, State 5, Line 1
The insert permission was denied on object ‘admin_user_role’, database ‘client’, schema ‘dbo’.

I tested this with another colleague of mine and he has no issue inserting into the table.

What other check/debugging can I do to resolve this issue?


texts: Transaction log backup after full backup is too big

texts:
Our current production database performs a full backup daily at 7AM. The transaction log backup takes place every 15 minutes (24/7).

Database version: Microsoft SQL Server 2014 - 12.0.4100.1 (X64)
The .mdf file size: 3.9GB
The .ldf file size: 6.5GB
Recovery Model:Full
Full backup size at 7AM: 3.8GB

The transaction log files (.trn) are normally below 50MB but after the full backup (at 7AM) the next transaction Log is around 1500MB. This is an issue for me because transaction log files are being transferred across network and restore time reason.
This is the script of the full backup:
BACKUP DATABASE [DB_PROD] TO
  DISK = N'F:\SQL\DB_PROD_backup_2020_10_12_114830_1849966.bak'
WITH NOFORMAT, NOINIT,  NAME = N'DB_PROD_backup_2020_10_12_114830_1849966',
SKIP, REWIND, NOUNLOAD,  STATS = 10

There is a re-organize index step before the full backup. The command is like this:
ALTER INDEX [UC_LCRecipe_Name]
ON [dbo].[LCRecipe]
REORGANIZE WITH ( LOB_COMPACTION = ON )


texts: How to handle "many columns" in OLAP RDBMS

texts:
I have a fact that has around 1K different numerical attributes (i.e. columns). I would like to store this in to a column-oriented DB and perform cube analysis on it.

I tried to design a star schema, but I'm not sure how to handle this many columns. Normalising it sounds wrong, but I can't just have flat columns either. The combination of attributes are also too diverse to have a simple dimension table for this, even if I'd reduce the numerical values into categories (ranges), which is an option. I thought about storing them as XML or JSON for each row, but that doesn't sound great either.

If it helps, I'm planning to use Amazon's redshift for the DB.

Note: We have strong preference for RedShift as it fits perfectly for at least other few operations we do on this data. Hence I want to avoid other technologies like HBase if possible.


texts:
I'm using this query on SQLite 3:

Select  Message.messageContent, Message.messageDateTime FROM Message INNER JOIN (Contact INNER JOIN contact_m2m_message ON Contact.contactID = contact_m2m_message.contactID) ON Message.msgID = contact_m2m_message.messageID;

it gave me this error: No Such Column: TABLE.ROWID

i'm confused as the very query was working fine when i tested over MS Access DB Engine just for the sake of confirmation.
.
is there something that i'm missing? or is there a difference in syntax in SQLite?
...
I also tried this:

Select  Message.messageContent, Message.messageDateTime FROM Message INNER JOIN (Select Contact.contactNumber, Contact.contactName FROM Contact INNER JOIN contact_m2m_message ON Contact.contactID = contact_m2m_message.contactID) ON Message.msgID = contact_m2m_message.messageID;

But this gives me the error:
no such column: contact_m2m_message.messageID ...  but it does exist in the definition.
.

.
The task i am striving to accomplish is to populate a list similar to Android Inbox view where each list item contains Contact Name, Number and a little Text from the latest message.

moreover, i'm testing queries using SQLite Studio v2.1.5


texts: How to increase performance of UPDATE of ~ 50.000.000 entries

texts:
We have a query that uses a temporary table to update certain columns in the database:

CREATE TABLE #NEW_OLD (
    NEW nvarchar(20),
    OLD nvarchar(20)
);
INSERT INTO #NEW_OLD
VALUES
('DOMAIN\KEY', 'NEW_KEY'),
('DOMAIN\KEY2', 'NEW_KEY2')
(...)
-- (~ 5.000 keys are inserted)

BEGIN TRANSACTION
BEGIN TRY

    DECLARE @TABLES_COLUMNS TABLE(TABLE_NAME varchar(100), COLUMN_NAME varchar(100))

    INSERT INTO @TABLES_COLUMNS (TABLE_NAME, COLUMN_NAME)
    VALUES
    ('[dbo].[MyTable1]', '[MyColumn1]'),
    ('[dbo].[MyTable2]', '[MyColumn2]')
    (...)
    -- ~ 20 columns are included. The tables have ~ 10.000.000 rows.

    -- Looping over table and column name variables with cursor
    DECLARE TABLES_COLUMNS_CURSOR CURSOR LOCAL FAST_FORWARD FOR
        SELECT TABLE_NAME, COLUMN_NAME
        FROM @TABLES_COLUMNS

    -- Variables to hold table and column name
    DECLARE @TABLE_NAME varchar(30), @COLUMN_NAME varchar(30)

    OPEN TABLES_COLUMNS_CURSOR

    -- Instead of fetching twice, we set up no-exit loop
    WHILE 1 = 1
    BEGIN

        -- And then fetch
        FETCH NEXT FROM TABLES_COLUMNS_CURSOR
        INTO @TABLE_NAME, @COLUMN_NAME

        -- And stop if nothing is fetched
        IF @@FETCH_STATUS <> 0
        BEGIN
            BREAK
        END

        -- Now, update each field with the new key that is associated with that key in the temporary table
        EXECUTE
        (
            'UPDATE ' + @TABLE_NAME + ' ' +
            'SET ' + @COLUMN_NAME + ' = new_old.NEW ' +
            'FROM ' + @TABLE_NAME + 'AS t ' +
            'INNER JOIN #NEW_OLD AS new_old ' +
            'ON t.' + @COLUMN_NAME + 'COLLATE DATABASE_DEFAULT = new_old.OLD COLLATE DATABASE_DEFAULT'
        )

    END

    CLOSE TABLES_COLUMNS_CURSOR
    DEALLOCATE TABLES_COLUMNS_CURSOR

END TRY
BEGIN CATCH

    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;
END CATCH;

IF @@TRANCOUNT > 0
    COMMIT TRANSACTION;

This query takes hours to run. The dynamic part (EXECUTE) should not influence performance too much in SQL Server 2008.

How can the performance be improved?


texts: Selecting rows from one table not in another table

texts:
I have two tables

table_a_mod (2 million rows)
table_a (2 million rows + new live data)

table_a_mod is identical to table table_a except for two extra columns

table_a is a "live" table with data being written to it semi-regularly.

To keep table_a_mod up to date I would like to copy data from table_a to table_a_mod

By using a regular join, I am able to select the data:

SELECT *
FROM table_a t1
LEFT JOIN table_a_mod t2
USING(id)
WHERE t2.id IS NULL

However, this query takes about 1 minute to run.

Is there a more efficient query along the lines of:

SELECT  *
FROM table_a t1
LEFT JOIN
(
  SELECT id
  FROM table_a_mod
  ORDER BY id DESC
  LIMIT 1
) t2
USING (id)
WHERE t1.id > t2.id

ie. Get the largest id from table t2. Select all rows from table t1 with id greater than that number.

I've tried different permutations of the above query but I'm falling short.

Edit 1

In response to @danblack

EXPLAIN {slow_query}

"1" "SIMPLE"    "table_a"   "ALL"   \N  \N  \N  \N  "2353123"   ""
"1" "SIMPLE"    "table_a_mod"   "eq_ref"    "PRIMARY"   "PRIMARY"   "4" "db.table_a.id" "1" "Using where; Not exists"

  I'm assuming id is a primary key in both

Yes. id is a primary key in both.

  Are the tables innodb and is there sufficient innodb_buffer_pool_size to hold both tables?

Both tables are innodb. And innodb_buffer_pool_size is 8388608

  Is "new live data" always of an id above the previous table?

Yes, because it's auto incrementing

  Why are you keeping two nearly identical tables

It is a temporary situation. We will drop the older one and use the newer one.


texts: How do you restore a DB to Multi-User mode when deadlocked by a sys process

texts:
I have a database that is stuck in Single User mode after a failed restore process. I have checked the sysprocesses table and none of the processes have spid > 50. They are all sa processes. Using the following code returns a deadlock that I can not seem to clear:

ALTER DATABASE myDatabase SET MULTI_USER with NO_WAIT

Besides restarting the SQL Server and then spamming the Alter statement, is there a way for me to pull this out of single user mode? Thanks in advance for any help you can provide.


texts: Do triggers start to degrade in performance (or sometimes fail) on highly transactional tables?

texts: How many transactions per second can SQL Server table triggers handle? If I had a highly transactional table that was inserted into 1,000 times a second, are insert triggers on that table reliable to fire every single time?


texts: barman + postgresql not start after barman retore

texts:
I use the barman for backup and restore

after some restore , we start the postgresql as the following

systemctl start postgresql.service

but postgresql not startup

from the log:

/var/lib/pgsql/backups/data/pg_log

we seen the following errors

LOG:  database system was interrupted; last known up at 2017-08-06 07:30:49 UTC
LOG:  restored log file "00000008.history" from archive
LOG:  starting point-in-time recovery to 2017-08-06 07:30:49.813508+00
LOG:  restored log file "0000000800000000000000C1" from archive
LOG:  redo starts at 0/C1000080
LOG:  recovery stopping before commit of transaction 96139, time 2017-08-06 07:30:49.855629+00
LOG:  redo done at 0/C1001B90
FATAL:  requested recovery stop point is before consistent recovery point
LOG:  startup process (PID 55858) exited with exit code 1
LOG:  terminating any other active server processes

please advice what is wrong configuration on BARMAN that cause this ?


texts: Database stuck in synchronized mode and another one in 'Initializing / In Recovery' mode

texts:
I have a database which is stuck in Synchronized mode and another database in Initializing / In Recovery mode. Both of them have been removed from their availability groups and I cannot even delete them. Both of them are in secondary server and I cant even do a failover. Any help is much appreciated.
UPDATE
When I try to delete the database in this state: Initializing / In Recovery, it says Lock request time out period exceeded.
When i try to delete the database in this state: Synchronized, it says certain operations cannot be performed while database is in a mirroring session., even though I have removed it from its availability group.


texts: SQL Server LaunchPad "The service did not respond in a timely fashion" + No Log in ExtensibilityLog

texts:
I'm trying to start LaunchPad service but it fails to start with:

Error 1053: "The service did not respond in a timely fashion"

I've been reading this was common in RC versions but I'm running SQL Server 2016 SP1 + CU3.

I've tried to add the SQLRUserGroup to the LogOnLocally user right assignment. The service still not run.

Also, there is no log in folder:
C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Log\ExtensibilityLog

I've tried to change security of log folder including the NT Service\MSSQLLaunchpad and the SQLRUserGroup entries.

Any suggestion is appreciated

UPDATE: It was fixed reinstalling the SP1!

UPDATE2: We reproduced the issue simply by following these steps: install SQL 2016, Update it with SP1, Add R Services using the installation (no SP1).


texts: Accumulated logs - Log shipping SQL Server

texts: If I have a log shipping scenario where a report runs for 30 minutes and the log shipping is set up for not killing the connection, and because of that, the restore will fail,  the question is if all the accumulated transaction logs will be restored the next time the db is available for that (no connections open) ?


texts: Looping through MySQL Query progressively gets slower

texts:
I have a MySQL Query, which as part of a batch job, loops through about 4,000 records - running the same queries against each of the 4,000 records.

As it moves through the records, it gets progressively slower.

By the time it's at the 4,000th, it's taking up to 20 times longer than when it did for the first loop.

There's actually 10 queries which run in each loop (SELECT), but the overall trend is all getting slower as the batch job progresses.

Also interesting is comparing how long each of the 10 individual queries takes in the loop.

At the start of the batch it's uneven (e.g. one SELECT takes 1% of loop time, whilst another takes 35% of loop time for example). This is as expected, as some queries are bigger than others.

But as it progresses, these 10 queries all start to take a much more similar time, all close to 10% of loop time -- suggesting perhaps there is a delay building up and affecting statements before they execute?

On my dev environment, MySQL is running locally - Running MySQL v5.7.24 on Ubuntu 16 on AWS EC2.

On my live environment,  I use AWS RDS. Running Aurora MySQL v5.6.10 on Ubuntu 16 on AWS EC2. I'm currently using Aurora Serverless, so have limited config control, but can move to RDS MySQL instance if there's config I need to access which would help.

The issue occurs in both environments.

It feels like previously executed statements are hanging around in the memory and not full freeing up resources for the next loop. But I don't know where to start looking to rectify.

EDIT:

On my dev environment (with local MySQL server), the load gets pretty high during the process (close to 100%), with RAM and SWAP not really changing (and well within limits).

On my live environment (RDS MySQL), the load and RAM are consistent, with no real change during the process (and well within limits), but the RDS  hits around 60% CPU utilization

So I'm obviously peaking out the CPU - which then, in turn, results in a lag with flows on?

EDIT:

I just doubled my dev environment EC2 (t2.small to t2.medium), which doubles RAM and 1CPU -> 2CPU. It's made no difference, and I notice running htop that only one of the CPUs is maxing out -- the other is almost idle - and I've just read that the MySQL Process won't (can't) be shared across multiple cores... so that doesn't help :-)

Welcome any thoughts and advice,

Thanks


texts:
My collection with a _id and a single field index is run on a single node/on localhost, has 48GB RAM and 16 core CPU available. The hdd is in RAID1, without SSD cache. Mongo version 2.4.

I run a mongoimport with 5Mio documents dump with a total of 5GB with mongoimport.
It starts fast and slows down very quickly.

This starts fast with about 11000/second. After 3 minutes going down to 9000/s, again 1 minute later it is 7000/s and after some more minutes I we are in slow motion on 200/s. The mongoimport takes for hours instead of some minutes.

mongostat shows increasing LOCKs and faults:

insert  query update delete getmore command flushes mapped  vsize    res faults  locked db idx miss %     qr|qw   ar|aw  netIn netOut  conn       time
*0     *0     *0     *0       0     1|0       0   912g  1825g    21g      1 tdb:74.0%          0       0|0     0|1     5m     3k    10   03:20:21
...
79     *0     *0     *0       0     1|0       0   912g  1825g    21g     10 tdb:33.6%          0       0|0     0|0    47k     3k    10   03:22:22
...
104     *0     *0     *0       0     1|0       0   912g  1825g    21g     13  tdb:7.9%          0       0|1     0|1    60k     3k    10   03:22:33
...
86     *0     *0     *0       0     1|0       0   912g  1825g    21g     11  tdb:0.8%          0       0|0     0|1    52k     3k    10   03:23:17
...
128     *0     *0     *0       0     1|0       0   912g  1825g    21g      9 tdb:58.3%          0       0|0     0|1    76k     3k    10   03:25:00
...
95     *0     *0     *0       0     1|0       0   912g  1825g    21g     11 tdb:39.3%          0       0|0     0|0    58k     3k    10   03:26:07
...
86     *0     *0     *0       0     1|0       0   912g  1825g    21g     12 tdb:88.8%          0       0|0     0|1    51k     3k    10   03:27:12

I run mongodb without --upsert and also tried with only the _id index. Furthermore reIndex()did not improve this.

How to improve this and remain the speed of bulk INSERTs constantly?

OS: Debian 6, 64Bit

MongoDB: 2.4.4


texts:
On one of our production server, SQL Server log is continuously showing the following error:

  The time stamp counter of CPU on scheduler id 13 is not synchronized with other CPUs.

What does it mean? Is it serious? If yes, how can I rectify it?


texts:
Create a database for a drone delivery startup that distributes pizzas via drones. The startup has partnerships with several drone manufacturers and pizza shops. The entities of interest to the problem domain are: Drone Manufacturers, Drone Models, Drones, Pizza Shops, Customers, and Deliveries. A drone manufacturer has a name and can produce different drone models. A drone model belongs to a drone manufacturer; it has a name, battery life (in minutes, integer number) and maximum speed. An actual drone is of a particular model and has a serial number. A pizza shop has a name and address. A customer has a name and loyalty score. Pizza shop names and customer names are unique. For each delivery, the system records the corresponding customer and pizza shop, the drone that is used, as well as the date and time. This is how I proceeded:
CREATE TABLE DroneManufacturers(
    id INT PRIMARY KEY IDENTITY(1,1),
    name VARCHAR(30)
);

CREATE TABLE DroneModels(
    id INT PRIMARY KEY IDENTITY(1,1),
    manufacturer_id INT REFERENCES DroneManufacturers(id),
    name VARCHAR(30),
    battery_life INT,
    max_speed INT
);

CREATE TABLE Drones(
    serial_number VARCHAR(10) PRIMARY KEY,
    model_id INT REFERENCES DroneModels(id),
);

CREATE TABLE PizzaShops(
    name VARCHAR(30) PRIMARY KEY,
    address VARCHAR(50)
);

CREATE TABLE Customers(
    name VARCHAR(30) PRIMARY KEY,
    score INT
);

CREATE TABLE Deliveries(
    pizza_shop VARCHAR(30) REFERENCES PizzaShops(name),
    customer VARCHAR(30) REFERENCES Customers(name),
    drone VARCHAR(10) REFERENCES Drones(serial_number),
    delivery_time DATETIME
);

I think the design is ok, but I have some problems in the next query:
Create a view that shows the names of the startup’s favorite drone manufacturers, i.e., those with the largest number of drones used by the startup.
Example: suppose the startup has partnerships with 3 manufacturers: M1, M2, M3; it has 10 drones from M1, 10 drones from M2, and 8 drones from M3. M1 and M2 are the manufacturers with the largest number of drones.
I have tried like this:
SELECT t.name
FROM (
    SELECT dman.id, dman.name, COUNT(*) AS counts
    FROM Drones d
    INNER JOIN DroneModels dm
        ON d.model_id = dm.id
    INNER JOIN DroneManufacturers dman
        ON dm.id = dman.id
    GROUP BY dman.id, dman.name
    ) t
WHERE t.counts = MAX(t.counts)

And I get an error because the last "where" clause is not written properly. Can somebody help me with this query, please?


texts: What are the built-in default configuration values of redis.conf?

texts:
If I omit a configuration option in redis.conf, what value will be used upon running redis?

The self-documented redis.conf states that just rarely.

I have found (but didn't try yet) this command: http://redis.io/commands/config-rewrite which can be possibly used to generate the config file, when running redis without one.


texts: How to use query result as REPLACE parameters

texts:
i'm trying to:

1- Find a string inside a lot of content

2- Replace that specific part of the string with a new one using REPLACE function like this:

REPLACE(column_name, query#1, query#2)

Is this possible?

I already have located the exact part of the content that i want to replace

SELECT `post_title`, SUBSTRING(`post_content`, LOCATE('[flv:',`post_content`), LOCATE(']',`post_content`) - LOCATE('[flv:',`post_content`) + 1) AS amazonlinks FROM wp_posts_duplicatedfortestonly WHERE `post_content` LIKE '%[flv:%'

and have the new string ready in another table column: Png_Link

Finally i'm trying to merge all this using the REPLACE function but it's giving me a lot of errors:

UPDATE wp_posts_duplicatedfortestonly
SET
    post_content = REPLACE(post_content,
                           (SELECT SUBSTRING(`post_content`, LOCATE('[flv:',`post_content`), LOCATE(']',`post_content`) - LOCATE('[flv:',`post_content`) + 1) FROM (SELECT post_content FROM wp_posts_duplicatedfortestonly) AS something WHERE `post_content` LIKE '%[flv:%'),
                           (SELECT `Png_Link`  FROM `VideoPlayerUdpateLinksReady` WHERE `Mp4_Link` != ''));

Something like this (only difference is i'm using 2 tables): https://www.db-fiddle.com/f/91EW2kJDc8aWJYYvw86vG9/0

Also tried with variables without any luck:

SET @tblname := "wp_posts_duplicatedfortestonly";
SET @colname := "post_content";
SELECT @lookfor := SUBSTRING(`post_content`, LOCATE('[flv:',`post_content`), LOCATE(']',`post_content`) - LOCATE('[flv:',`post_content`) + 1) FROM @tblname WHERE `post_content` LIKE '%[flv:%';
SELECT @replacewith := `Png_Link` FROM `VideoPlayerUdpateLinksReady` WHERE `Mp4_Link` != '';

SET @qry = CONCAT('UPDATE ',@tblname, ' SET ', @colname, ' = REPLACE(' ,@colname, ',', @lookfor, ',',@replacewith,')');
PREPARE QUERY FROM @qry;
EXECUTE QUERY;

What am i'm doing wrong? Is it possible to use SELECT expressions as REPLACE function parameters or what other workaround do i have?


texts: Best Practice for Passing and Working With Parameters Security/Performance

texts:
Our senior programmer has been having me write stored procedures in the following format to protect against injection attacks.  He says that the best practice is to take the parameter in, then declare a new variable in the body and assign that var the parameter, that this step is extra protection against injection attacks as it forces any injection attempts to be considered data and not taken literally.  Is that true?  I would think this would slow down the query and use extra memory and not add any extra protection, but I could be wrong.  Example below.

    CREATE PROCEDURE [dbo].[sp_foo_GET]
        @parAcct            NVARCHAR(20)
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE
        @varAcct            NVARCHAR(20) = @parAcct;

    SELECT [userid]
    FROM [tblAccounts]
    WHERE [accountID] = @varAcct;
END

EDIT  After talking with the programmer he seems to think that passing parameters to one variable and assigning them to a declared variable helps with security AND it will cause queries to run faster.


texts: Postgres slow query with order by, index and limit

texts:
I am trying to improve the performance of a postgres(9.6) query. Here is my schema and table contains about 60 million rows.

          Column          |            Type             | Modifiers
--------------------------+-----------------------------+-----------
 transaction_id           | text                        | not null
 network_merchant_name    | text                        |
 network_merchant_id      | text                        |
 network_merchant_mcc     | integer                     |
 network_merchant_country | text                        |
 issuer_country           | text                        |
 merchant_id              | text                        |
 remapped_merchant_id     | text                        |
 created_at               | timestamp without time zone |
 updated_at               | timestamp without time zone |
 remapped_at              | timestamp without time zone |
Indexes:
    "mapped_transactions_pkey" PRIMARY KEY, btree (transaction_id)
    "ix_mapped_transactions_remapped_at" btree (remapped_at NULLS FIRST)

Here is the query I am trying to execute.

SELECT *
FROM mapped_transactions
ORDER BY remapped_at ASC NULLS FIRST
LIMIT 10000;

Here is the query plan:

    QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.57..1511.67 rows=10000 width=146) (actual time=327049.374..327345.341 rows=10000 loops=1)
   Buffers: shared hit=574937 read=210425 dirtied=356 written=4457
   I/O Timings: read=146625.381 write=59.637
   ->  Index Scan using ix_mapped_transactions_remapped_at on mapped_transactions  (cost=0.57..16190862.91 rows=107145960 width=146) (actual time=327049.364..327339.402 rows=10000 loops=1)
         Buffers: shared hit=574937 read=210425 dirtied=356 written=4457
         I/O Timings: read=146625.381 write=59.637
 Planning time: 0.125 ms
 Execution time: 327348.322 ms
(8 rows)

I don't understand why it takes so much time when there is an index on the remapped_atcolumn.

On the other hand if I order in reverse it is fast.

SELECT *
FROM mapped_transactions
ORDER BY remapped_at DESC NULLS LAST
LIMIT 10000;

and the plan is:

QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.57..1511.67 rows=10000 width=146) (actual time=0.020..9.268 rows=10000 loops=1)
   Buffers: shared hit=1802
   ->  Index Scan Backward using ix_mapped_transactions_remapped_at on mapped_transactions  (cost=0.57..16190848.04 rows=107145866 width=146) (actual time=0.018..4.759 rows=10000 loops=1)
         Buffers: shared hit=1802
 Planning time: 0.080 ms
 Execution time: 11.561 ms
(6 rows)

Can someone please help me how I can improve the performance of the first query?

Updates

I solved this by rebuilding the table and reindexing the data. VACUUM FULL ANALYZE was not an option because the table was being used and I did not want to lock it.
The performance of the index is deteriorating very fast. I rebuilt the index 7 hours ago and the performance was good. Now the queries answer in about 10s. Please note that that this table is write heavy. How can I make the index fast? Do I have to re-index the table frequently? There are no deletions in this table but a lot of updates.


texts: Slow MySQL query with Order and Group by