texts: Buffer Cache Size when Page Life Expectancy Drops

texts:
I'm troubleshooting an issue with one of our SharePoint databases, essentially huge drops in PLE which I think are caused by increased I/O activity (I see increased reads, writes and lazy writes at the same time)

Buffer Cache Hit Ratio at this time is typically 96%..

  Microsoft SQL Server 2012 (SP1) - 11.0.3000.0 (X64) Oct 19 2012 13:38:57 Copyright (c) Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.2  (Build 9200: ) (Hypervisor)

I wanted the check the size of the buffer cache at the exact time of PLE drop, so I'm running the following query which I think gives me the size in MB:

DECLARE @total_buffer INT;

SELECT @total_buffer = cntr_value
FROM sys.dm_os_performance_counters
WHERE object_name LIKE '%Buffer Manager%'
AND counter_name = 'Database Pages'

SELECT ((@total_buffer * 8) / 1024) AS CacheSizeMB

The size I'm seeing does not make sense (to me, i mean), typically 250-350MB. I was hoping I would be seeing a large size, then I could check which tables have the most pages in the cache, which would hopefully lead me to which LOB objects were being retrieved/uploaded thus causing the problems.

Do I also need to be looking at the plan cache? i.e. could the PLE drop be caused by some heavy table scans?

SQL Server is allocated a MAX memory of 12GB, from an available 16GB. How does that correlate with Buffer Cache Size?

If PLE is dropping to values of 0-5 then surely the Buffer Size should be larger...?


texts: Database Link from Oracle 10g on Windows over to MySQL database

texts:
Didn't see any answers on this subject so hoping for a miracle! :-)

I've got an Oracle 10g database running on Windows Server 2003 and I'd like to create a database link to an external MySQL database which is running in the Amazon Web Service RDS cloud.

We may have the option of upgrading the Oracle version to 11g if that would help?

Has anyone done anything like this before?

Thanks for your help! Josh


texts: Displaying SSIS variabe values in Integration Services Dashboard

texts:
I have an SSIS package that executes stored procedures which return output parameters into ssis variables. How can I easily display these variables content in the Integration Services Dashboard ?
Basically after a SSIS Package is run I would like to know if the return code of the SPs and some performance counters (records transferred, etc.)


texts:
I'm going to start my Master of Science degree work.
My goal is to implement (and test in various scenarios) hybrid database environment: my application will communicate with two databases. One will be in-memory database and other will be standard (on hard disk) database. I want to research if this hybrid can be faster than standard database only (in certain situations).

Have you ever heard or read of someone, who tried something like this? Because if there are works like that I would like compare my results to them.

Edit:
I'm on beginning of my work, so exact topic is not cleared yet (I have something about 18 months to write it).
My main idea is to compare standard database (eg MySQL) vs hybrid (eg MySQL+VoltDB) with the same set of data, where on hybrid some tables (or maybe some columns from some tables) will be in in-memory database.
Mainly I want to compare time performance, but maybe other aspects (like administration issues of that hybrid) as well.


texts: Default Constraints with Merge Replication

texts:
I am using merge replication in SQL 2012.

Why can't you mark default constraints as NOT FOR REPLICATION? You can disable all default constraints for a merge article, but it is all or nothing so it doesn't seem to offer enough control.

How about this scenario,

The user inserts a record 'Shape' at a client
The 'Shape' has a default constraint to set the NumberOfSides field to 4
The client user edits the value of NumberOfSides and sets it to 5
They sync with the server

When they sync with the server won't it repeat the default constraint and set the value of 'NumberOfSides' back to 4?

Am I missing something here?

I don't want to disable the default constraints being replicated to the client either because there are valid default values that need to be set. The client side user needs to have these values set to use the software.

UPDATE:

The explanation about the default constraint being applied only once makes sense.

So my only remaining question is if I have default contraints which use sequences I am in trouble because sequences cannot be replicated. Are these my only two options?

Turn off all default constraints for a particular table (not ideal if some of them are not for sequences)
Create the sequences manually at the client side, and then create a custom handler to apply the correct sequence values at the server when syncing


texts: How do I restore a SQL backup, while excluding a specific table?

texts:
How do I exclude a specific table during a SQL backup RESTORE operation?

My question is specific to restoring a backup sql file.

I am using Putty and bash server.


texts: Command wbcopytables --table-range not working

texts:
I am migrating sql server tables in MySQL using wbcopytables command. I want to copy a specific range(id 1-10000) of data from MSSQL to MySQL, so I am using below syntax.

wbcopytables --odbc-source="DRIVER={FreeTDS};SERVER=**.**.**.**;PORT=1433;DATABASE=TESTDB;UID=***;PWD=***;TDS_VERSION=7.1" --target="root@127.0.0.1:3306" --force-utf8-for-source --truncate-target  --table-range [TESTDB] [dbo].[DeletedUnits]  TESTDB DeletedUnits Unit_Id Unit_Id 1 10000

unfortunately I am getting below error message this syntax.

wbcopytables: Invalid option 10000

If we remove Unit_Id from select expression from command, the it creates wrong sql for migration like below.

ERROR:TESTDB.DeletedUnits:SQLExecDirect(SELECT  FROM [TESTDB].[dbo].[DeletedUnits] WHERE Unit_Id >= 1 AND Unit_Id <= 10000): 42000:1156:[FreeTDS][SQL Server]Incorrect syntax near the keyword 'FROM'.


texts: Where is MySQL template directory for pristine Data?

texts:
I'm using MySQL 8.0 in Windows.
I'm trying to set up multiple servers.  And I need to copy the Data directory for it. The MySQL documentation says

Windows MSI package installers create and set up the data directory
that the installed server will use, but also create a pristine
“template” data directory named data under the installation directory.
After an installation has been performed using an MSI package, the
template data directory can be copied to set up additional MySQL
instances.

It says there is a pristine data directory that is not touched. But my Data directories are already modified because I used it quite a long time. I need to copy the Data directory to make new server.
Where do I find the pristine Data directory?


texts: Rolling sum / count / average over date interval

texts:
In a database of transactions spanning 1,000s of entities over 18 months, I would like to run a query to group every possible 30-day period by entity_id with a SUM of their transaction amounts and COUNT of their transactions in that 30-day period, and return the data in a way that I can then query against. After a lot of testing, this code accomplishes much of what I want:
SELECT id, trans_ref_no, amount, trans_date, entity_id,
    SUM(amount) OVER(PARTITION BY entity_id, date_trunc('month',trans_date) ORDER BY entity_id, trans_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS trans_total,
    COUNT(id)   OVER(PARTITION BY entity_id, date_trunc('month',trans_date) ORDER BY entity_id, trans_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS trans_count
  FROM transactiondb;

And I will use in a larger query structured something like:
SELECT * FROM (
  SELECT id, trans_ref_no, amount, trans_date, entity_id,
      SUM(amount) OVER(PARTITION BY entity_id, date_trunc('month',trans_date) ORDER BY entity_id, trans_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS trans_total,
      COUNT(id)   OVER(PARTITION BY entity_id, date_trunc('month',trans_date) ORDER BY entity_id, trans_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS trans_count
    FROM transactiondb ) q
WHERE trans_count >= 4
AND trans_total >= 50000;

The case that this query doesn't cover is when the transaction counts would span multiple months, but still be within 30 days of each other. Is this type of query possible with Postgres? If so, I welcome any input. Many of the other topics discuss "running" aggregates, not rolling.
Update
The CREATE TABLE script:
CREATE TABLE transactiondb (
    id integer NOT NULL,
    trans_ref_no character varying(255),
    amount numeric(18,2),
    trans_date date,
    entity_id integer
);

Sample data can be found here. I'm running PostgreSQL 9.1.16.
Ideal output would include SUM(amount) and COUNT() of all transactions over a rolling 30-day period. See this image, for example:

The green date highlighting indicates what's being included by my query. The yellow row highlighting indicates records what I would like to become part of the set.
Previous reading:

Postgresql Window Function Partition by Comparison
Group by
Data Intervals
Best way to count records by arbitary time
intervals...
Moving Average in Postgresql


texts: MySQL Cluster work well but ndb_desc doesn't work

texts:
I have deployed and configured MySQL cluster. It works fine.

ndb_mgm> show

Connected to Management Server at: localhost:1186

Cluster Configuration

[ndbd(NDB)]2 node(s)

id=2@192.168.56.2  (mysql-5.6.19 ndb-7.3.6, Nodegroup: 0, *)

id=3@192.168.56.3  (mysql-5.6.19 ndb-7.3.6, Nodegroup: 0)

[ndb_mgmd(MGM)]1 node(s)

id=1@192.168.56.1  (mysql-5.6.19 ndb-7.3.6)

[mysqld(API)]1 node(s)

id=4@192.168.56.4  (mysql-5.6.19 ndb-7.3.6)

I am reading the manual section about how to use MySQL cluster program http://dev.mysql.com/doc/refman/5.6/en/mysql-cluster-programs.html

On SQL Node - 192.168.56.4, I am running:

shell> ndb_desc -c 192.168.56.1

I receive message:

Unable to connect to management server.

NDBT_ProgramExit: 1 - Failed

I did ping and telnet successful to management node - 192.168.56.1:1186 from sql node - 192.168.56.4 There is no firewall between them.

What is cause of the problem ?

Thank you in advance.


texts:
I have following scenario:

Dump Mongo database (cmd mongodump)
Change password from one of admin users
Restore Mongo database (cmd mongorestore)

EDIT:

I use following command for db dump:

mongodump -h $HOST:$PORT --ssl --sslAllowInvalidCertificates --authenticationDatabase $authdb -u $user -p $pass --gzip --archive=$destination

As a result password was not restored. Is there any defect plan that can be followed in mongodb for this specific case?


texts: Concurrent MySQL updates hang with InnoDB (on Amazon RDS)

texts:
I'm running into a problem where multiple MySQL updates being performed at the same time will lock up and takes several minutes to finish. I'm using InnoDB, so I'm confused as to why this could be happening since each update is updating only 1 row. I'm also using a m2.4xlarge RDS instance (the largest they come).

Here's what I'm doing: I have a table with about 100M rows in it, with "views" being a column (which is indexed), and I want to update the views on about 1M rows. On several different servers I have a loop like this where each server has it's own set of rows to be updated (pseudo code):

mysql("set autocommit=0");
mysql("start transaction");

foreach($rows as $row) {
    mysql("update table set views=views+1 where id=$row[id]");
}

mysql("commit");

This loops through all the rows that need to be updated. It works perfectly when the number of servers is small, like around 4, but when it grows to 10+ the updates start to hang in the "Updating" state all at once. Nothing says that it's waiting on a lock, it's just "Updating". This happens for about 5 minutes, where it will finally make the updates and continue through the loop and eventually happen again.

I'm not looking for alternative ways to do the updates. Having things like a tmp table and

update table,tmp_table set table.views = table.views+tmp_table.views where
  table.id = tmp_table.id

lock all the rows that are being updated until they all finish (which could be hours), which won't work for me. They MUST be in these awful loops.

I'm wondering why they could be getting stuck in the "Updating" state, and what I can do to prevent it.

tldr; Having 10+ "update" loops will eventually lock up all the updates being done, at the same time, for an unknown reason until they decide to finally make updates and continue through the loops, only for it to happen again seconds later.

SHOW VARIABLES: http://pastebin.com/NdmAeJrz

SHOW ENGINE INNODB STATUS: http://pastebin.com/Ubwu4F1h


texts: How to find out who deleted some data SQL Server

texts:
My boss had a query from a customer yesterday asking how they could find out who deleted some data in their SQL Server database (it is the express edition if that matters).

I thought this could be found from the transaction log (providing it hadn't been truncated) - is this correct? And if so how do you actually go about finding this information out?


texts: MySQL: Show full processlist has empty Host name. Why?

texts:
I am executing SHOW FULL PROCESSLIST and from the response I get the "Host" field is empty.

Quoting from the docs here

  Host

  The host name of the client issuing the statement (except for system
  user where there is no host). SHOW PROCESSLIST reports the host name
  for TCP/IP connections in host_name:client_port format to make it
  easier to determine which client is doing what.

Who is a system user? The root user?

I have permitted remote access to the root user.

The same case on my another system where there a root user with the remote access and executing SHOW FULL PROCESSLIST shows localhost:<thread-id> in the host field.

How to know in which case MySQL shows the host name in SHOW FULL PROCESSLIST`?


texts: Copying a subscribed database to the publisher server

texts:
I have two servers.
I have installed a merge replication in the publisher (Server A) and I have added two subscriptions with the same database name.
- One on the publisher itself and the seconde one on the subscriber (Server B).So far so good. The replication is working well.
I wanted to delete the subscibed database on the publisher (Server A) and replace it by a copy of subscribed database from Server B.

I thought the publisher will continue synchronizing on the newly attached database, but unfortunately, it didn't work as expected, it started aplying snapshot etc.. instead.
Is there anything to modify on the the copied database to make the publisher reconise it as the deleted one and continues synchronizing  ?


texts: Measuring the need of Capacity or scaling SQL server

texts:
This would be more of General approach question rather than too technical:

I have one of my application reach out to me wandering if we need to scale up SQL server in terms of CPU memory disk or CPU as they are planning to increase the volume by double the current one.

From the current volume, i have captures which shows CPU/memory and disk running without issues.

But how can i measure with confidence what all will change if there is increased in volume which might need a bump in capacity at my end?

Need your expertise how can i be pro-active and what all can i measure to make up a decision , atleast close to what can be expected with the growth in volume?

Yes i do have SQL server monitoring in place which stores performance data for last 15 days. Most of that data comes from performance counters and DMV's.

Update- Adding more data as i get to update- Avg 800 transactions/sec i day and 400 batch req/sec in a day followed by steady 40% cpu usage over the day, includes peak day. Disk response are well under 5 ms for both read/write. PLE throughout the day is over 10K.

Any help on how to approach this is greatly appreciated.I know answer is broad to this question, but some great ideas can really help me not only for this server but something similar in future also comes up , thanks!


texts: multiple similar rows, where only one field has changed

texts:
I have a basic database set up for a school spelling bee (the scenario may seem slightly silly, but I'm using it as an excuse to learn a little more about database design). The main tables contains the words, definition, and associated attributes as shown, partOfSpeech and level being linked to two other tables.

The way that the spelling bee is structured is that each level contains a subset of the words from levels below it, and at the moment the words are entered in the database as they appear on paper, so certain words are occurring two or three times - the only thing that has changed for that row is the value of 'level'. For example, above, the word 'impostor' occurs three times, once for level 3, once for level 6 and once for level 7.

My question is how better structure my table to remove this duplication? Effectively I want the word, definition, and other fixed attributes in the table once, with the levels 3, 6 and 7 associated with it. In my head at least, it would also make it easier for me to not pull the same word twice from the database, if I choose to code the front-end not to.

Any help would be appreciated.


texts: Postgres to_tsvector is case sensitive on mac os

texts:
On PostgreSQL 10.4 on Ubuntu 10.4-2.pgdg16.04+1

select to_tsvector('simple', 'БОЛЬШИЕ БУКВЫ');

returns 'большие':1 'буквы':2

But on postgres 10.3 (installed by brew) on Mac Os High Sierra version 10.13.3 it returns 'БОЛЬШИЕ':1 'БУКВЫ':2 for not english letters

How can I fix it on Mac os?


texts: Disk I/O increased when changing RBR to MIXED

texts:
I'm experiencing some replication issues. I have a Master-Slave replication with mysql; the slave had binlog-format = ROW, Last sunday (3 days ago) I changed the format to MIXED (this is master too; so I expected better performance for his slave). The thing is, it appeared to work fine sunday and monday; but starting tuesday this slave began to lag and kept falling behind for some hours... It catched up around midnight, and today again from nowhere started to lag and kept behind many hours again; trying to understand what's happening I've been asking around and it seems like the workload is the same like past weeks, nothing new that could be causing this behavior. I noticed that the Disk Usage has incremented considerably lately, which is strange, I thought that this change would cause less disk writes...

I'm attaching the graphic, you can see a lot of reads december 18, that was some Cold Backup, so it's not that.... The question is: Could the change of binlog-format be the cause of this behavior? Is it normal that it is writing a lot more to disk? And is there a way to improve this?

Thanks

Server1:

Master
binlog_format=MIXED

Server2:

Server1's slave
binlog_format=MIXED (This is the one changed, and where the I/O is up).

Server3:

Server2's slave


texts: How can I find the user who performed a database transaction?

texts:
I am using MySQL database. Suppose a database "X". It is used by multiple users. I want to find those users who performed any transaction of data into this database. Is it possible?

Can I find it from MySQL binary log? How?


texts:
I would like to make some timing tests in my PostgreSQL database. I would like to clear cache every time I run a query to disable influence of caching on my timing results. I've read some topics about clearing that cache in Linux, but not found how to do that in Windows. Could you help me please?

OS: Windows 10.
PostgreSQL: 9.6.0.


texts: Invoke --data-checksums using pg_createcluster on Ubuntu 14.04

texts:
How do you pass the initdb argument for data checksums to pg_createcluster when creating the initial cluster?

Passing the argument as per initdb fails for me:

pg_createcluster --data-checksums --datadir=foo/cluster_name 9.5 cluster_name


texts:
I got the following tables:

  +-----------------+
  | PRODUCTS        |
  +-----------------+
  | #product_id     |
  | #company_id     |
  +-----------------+
        |     |
+---------------------+
| PRODUCT_BUNDLES     |
+---------------------+
| #parent_product_id  |
| #child_product_id   |
| #company_id         |
+---------------------+

And here is a result in a tree like of the relationships among ids in the PRODUCT_BUNDLES table for some product:

        p1
       /   \
      p2   p3
          / | \
        p4  p5 p6

A user give me the following ids p2, p4, p5 and p6 (the leafs of the tree) and I want return the p1 id (the root of the tree).

How I build select query in mysql to do that?

For now I only have the inverse query. From a root ID give the descendent's IDs.

SELECT bundles.product_id FROM products_bundles bom INNER JOIN products products ON products.product_id = bom.parent_product_id INNER JOIN products bundles ON bundles.product_id = bom.child_product_id WHERE products.product_id = SOMEID;

To clarify what I search is a query that return the parent who have all product_id given as descendant not the parent who have some product_id given as descendant. Thanks


texts:
I have this query in PostgreSQL and I am not familiar with PostgreSQL. I need to convert it to SQL Server.

SELECT (applicant_majors.*)::applicant_majors
FROM applicant_majors
WHERE applicant_majors.applicant_degree_id = (applicant_degree_1).id
ORDER BY id
LIMIT 1 OFFSET 1

This is the whole query

           FROM (SELECT college_attendeds,
                    colleges,
                    applicant_degree_1,
                    applicant_degree_2,
                    (SELECT (applicant_majors.*)::applicant_majors FROM applicant_majors WHERE applicant_majors.applicant_degree_id = (applicant_degree_1).id ORDER BY id           LIMIT 1         ) AS applicant_degree_1_applicant_major_1,
                    (SELECT (applicant_majors.*)::applicant_majors FROM applicant_majors WHERE applicant_majors.applicant_degree_id = (applicant_degree_1).id ORDER BY id           LIMIT 1 OFFSET 1) AS applicant_degree_1_applicant_major_2,
                    (SELECT (applicant_minors.*)::applicant_minors FROM applicant_minors WHERE applicant_minors.applicant_degree_id = (applicant_degree_1).id ORDER BY minor_number LIMIT 1         ) AS applicant_degree_1_applicant_minor_1,
                    (SELECT (applicant_minors.*)::applicant_minors FROM applicant_minors WHERE applicant_minors.applicant_degree_id = (applicant_degree_1).id ORDER BY minor_number LIMIT 1 OFFSET 1) AS applicant_degree_1_applicant_minor_2,
                    (SELECT (applicant_majors.*)::applicant_majors FROM applicant_majors WHERE applicant_majors.applicant_degree_id = (applicant_degree_2).id ORDER BY id           LIMIT 1         ) AS applicant_degree_2_applicant_major_1,
                    (SELECT (applicant_majors.*)::applicant_majors FROM applicant_majors WHERE applicant_majors.applicant_degree_id = (applicant_degree_2).id ORDER BY id           LIMIT 1 OFFSET 1) AS applicant_degree_2_applicant_major_2,
                    (SELECT (applicant_minors.*)::applicant_minors FROM applicant_minors WHERE applicant_minors.applicant_degree_id = (applicant_degree_2).id ORDER BY minor_number LIMIT 1         ) AS applicant_degree_2_applicant_minor_1,
                    (SELECT (applicant_minors.*)::applicant_minors FROM applicant_minors WHERE applicant_minors.applicant_degree_id = (applicant_degree_2).id ORDER BY minor_number LIMIT 1 OFFSET 1) AS applicant_degree_2_applicant_minor_2
               FROM (SELECT (college_attendeds.*)::college_attendeds AS college_attendeds,(colleges.*)::college_bases AS colleges,
                            (SELECT (applicant_degrees.*)::applicant_degrees FROM applicant_degrees WHERE applicant_degrees.college_attended_id = college_attendeds.id ORDER BY id LIMIT 1         ) AS applicant_degree_1,
                            (SELECT (applicant_degrees.*)::applicant_degrees FROM applicant_degrees WHERE applicant_degrees.college_attended_id = college_attendeds.id ORDER BY id LIMIT 1 OFFSET 1) AS applicant_degree_2
                       FROM college_attendeds
            LEFT OUTER JOIN college_bases AS colleges ON (college_attendeds).college_id = colleges.id AND colleges.type = 'College') AS applicant_degree_data) AS college_attended_data
LEFT OUTER JOIN degree_bases AS applicant_degree_1_degree ON (applicant_degree_1).degree_id = applicant_degree_1_degree.id AND applicant_degree_1_degree.type IN ('Degree', 'PtDegree')
  LEFT OUTER JOIN degree_bases AS applicant_degree_2_degree ON (applicant_degree_2).degree_id = applicant_degree_2_degree.id AND applicant_degree_2_degree.type IN ('Degree', 'PtDegree');


texts:
I was wondering, is it generally considered safe to have MySQL read-replicas with lower specs than the primary read/write instance?

... I'm assuming yes, since the main box has to deal with more than just the read-replicas processing the binary logs? But, I wasn't sure about best practices considering all of the factors.

If anyone has any thoughts to share, would be appreciated.


texts: Can't connect to Analysis Services or Reporting Services on localhost

texts:
I'm able to connect to my SQL Server 2008R2 localhost Database Engine and Integration Services.

However, when I try to connect to Analysis Services, it fails with the message: "A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond."

I didn't get an error when I tried to connect to Reporting Services, but didn't get a response of any kind after 10 minutes and canceled the connection attempt.

What can I do to fix this?


texts:
input: I have a table containing (any number of) user-supplied strings:

╔═══════╗
║ Value ║
╠═══════╣
║ To    ║
║ An    ║
╚═══════╝

And a table of user details:

╔════════╦══════════╗
║ UserID ║ UserName ║
╠════════╬══════════╣
║     12 ║ Tom      ║
║    132 ║ Ann      ║
║     32 ║ Dina     ║
║      2 ║ Mark     ║
╚════════╩══════════╝

I need a query to return all records from the users table where UserName is a partial string match for any Value record in the input table.  Expected output in this case is:

╔════════╦══════════╗
║ UserID ║ UserName ║
╠════════╬══════════╣
║     12 ║ Tom      ║
║    132 ║ Ann      ║
╚════════╩══════════╝


texts: When does autocommit take place when many rows are inserted as a single statement?

texts:
I have some problems with performance of insertion of about 50000 rows into a table. As an optimization it's advised to start a transaction explicitly to avoid committing on each row. However, I insert all rows in one statement, not in 50000 statements.

So I wonder, when AUTOCOMMIT mode is on, when does a commit occurs when I insert a lot of rows in single statement - after all 50000 rows are inserted or after each row is added into a table.

In other words, is this following code

INSERT INTO mytable (col1, col2) VALUES (1, 2), (3, 4), (5, 6);

is equivalent to the code

START TRANSACTION;
INSERT INTO mytable (col1, col2) VALUES (1, 2), (3, 4), (5, 6);
COMMIT

or maybe it actually means:

START TRANSACTION;
INSERT INTO mytable (col1, col2) VALUES (1, 2);
COMMIT
START TRANSACTION;
INSERT INTO mytable (col1, col2) VALUES (3, 4);
COMMIT
START TRANSACTION;
INSERT INTO mytable (col1, col2) VALUES (5, 6);
COMMIT

can you tell me?

Also, it's worth mentioning, that I have tried to insert not all 50000 rows as a single statement, but no more than 5000 rows at once (without explicit transactions, but also 5000 rows as a single statement), and I got controversial result - on some tables it got better about 6 times, while on others it got worse about twice, and on the whole, all got to work slower. I deemed that a commit occurs only after the whole statement is executed, but now I have doubts concerning it.


texts: In what order MySQL permissions are applied on database?

texts:
I want to understand the priority of executing MySQL grants privileges.

For e.g.:

I want to grant two different types of privileges on a single db Demo.

1.GRANT SELECT ON Demo.table1 TO abc@123;

2.GRANT ALL PRIVILEGES ON Demo.* TO abc@123;

As per my assumption, no 2 grant will overwrite the limitations of no 1 grant, therefore, ends up giving access to all tables including table1 to abc user.

And if I want to give SELECT privilege for table1 then I should change the order of executing permissions so that they don't get overwritten by *.

I'm not sure if this is how MySQL manages such permissions.
Anyone with a different opinion?

Thanks in advance.


texts: I need to use Data Cubes to analyse the Sales Performance. Since the product costs are highly confidential it should not be exposed to the middle management. However, the other information needs to be published to the middle management. Is there a feature in SSAS that could enable this requirement?


texts: One vs. Many Tables for Frequently but Asynchronously Updated Columns?

texts:
As part of my Postgres 10 schema, I have two tables; current and historic -- both have the same columns.

current has about 20 (+/-) columns, and holds the most up-to-date information for accounts. I need to keep a timestamp showing when the information came in, but each of the 20 columns gets updated at a different time. So, per account current will have numberOfColumns rows (as opposed to one row for one account) -- as new data comes in for one column, the row holding the information for just that column will get updated.

This is so I can quickly access the current information for the account.

However, I also need to store the history; so every time current is updated, the row (containing a single entry -- aside from metadata) will be 'moved' to the historic table. This table has the same number of columns.

So, I will end up with a very sparse table of 20 or so columns, and with each row containing a timestamp, one column's worth of 'data', and some metadata (e.g., account_id).

The current table will be read from frequently. Both INSERTs and SELECTs must be very fast.

The historic table will likely generally be used for offline analytics, and space is a priority SELECT performance, but INSERTs must be fast (because they will be done while updating current.

So, my questions are:

Is this a bad design? What is the usual solution for this type of situation (where a number of things like account details are asynchronously updated)?
Should I break it up so I have 20 tables, with 1 data column plus the metadata I need per table? It seems that would waste space.
What are the pros and cons of each approach?

Thanks in advance.

Edit(s):

In response to @JonathanFite:

Adding the schema and sample queries would use up a lot of space; but,

The 'data' columns are a number of decimal and int values and one text value, with no constraints.
There are 4 additional values with not null constraints (bigserial, text, text, and timestamptz) that will be a part of each insertion.
Each insertion consists of those 4, plus a decimal or int (or text in one case).
All 20 columns could be updated within the same second, or minutes apart.
The selects will request the results to be ordered by time, and could be for anywhere from a single data column to nearly all of them.

Does that help clarify things?


texts: Postgis ST_Intersects behaves erratically

texts:
I am using Postgres 13.0 on a Mac, with Postgis 3.0. I encountered a problem where ST_Intersects produces the wrong result, and after narrowing it down, I arrived at the following:
With the following table created:
CREATE TABLE test (
    name VARCHAR(32) PRIMARY KEY NOT NULL,
    area GEOGRAPHY NOT NULL
);

I insert a row:
INSERT INTO test (name, area)
VALUES('test1',
        ST_GeogFromText('POLYGON((-80 -60,-100 -60,-120 -60,-120 10,-100 10,-80 -60))'));

Next I query the table to see if the area intersects a given polygon:
SELECT name,
       ST_Intersects(ST_GeographyFromText('POLYGON((0 0,0 90,90 0,0 0))'), area)
FROM test ORDER BY name;

 name  | st_intersects
-------+---------------
 test1 | f
(1 row)

It does not intersect, as expected.
Now, I insert another row, which is identical to the first row, except for the name:
INSERT INTO test (name, area)
VALUES('test2',
        ST_GeogFromText('POLYGON((-80 -60,-100 -60,-120 -60,-120 10,-100 10,-80 -60))'));

Finally, I run the same query:
SELECT name,
       ST_Intersects(ST_GeographyFromText('POLYGON((0 0,0 90,90 0,0 0))'), area)
FROM test ORDER BY name;

 name  | st_intersects
-------+---------------
 test1 | t
 test2 | f
(2 rows)

To my surprise, now the first row indicates that it intersects, but not the second, while the areas are identical.
Is this a bug or am I overlooking something obvious?


texts: How can I invoke an SSIS package from SQL Agent, via T-SQL, using a proxy

texts:
Building an ETL framework with many packages and a "controlfile" table that orchestrates running them in the right order and managing errors. In order to control the overall operation of the data load I would like to do this:

Make a job(s) in SQL Agent
In the job steps invoke a custom stored procedure
In the stored procedure, call SSISDB.catalog.create_execution and related procs to
execute a package
Run this under a dedicated ETL Windows user account that is NOT a sysadmin and is not the SQL Agent service account, for security reasons.

Everything in steps 1, 2, and 3 works, but if I try to configure the agent job step to run as another account or as a proxy it doesn't work.

Background

Running a single package from an Agent job with the job step type "Integration Services"  works, because there's an option to set the step to run as a proxy user. (New Job > Steps > New Step > Type = Integration Services > RUN AS). The problem is that we need the "wrapper" logic to control all these individual packages.

Running a package from a stored procedure directly, from T-SQL, works.

Running a package from a stored procedure in a T-SQL job STEP in Agent, if the job is owned by a sysadmin works, but it runs as the SQL Server Agent Service Account which is a security problem.

Running a package from a stored procedure in a T-SQL job step in Agent, if the job is owned by a NON sysadmin user fails because of a problem in the Agent's EXECUTE AS code, where the SSIS procedures throw an error like 'could not revert' - which leads me to believe there's a problem in the Agent's EXECUTE AS logic running the T-SQL under the job owner account, then calling the SSIS procedures. I have found some information online which I hope I can re-locate an link to.

Running a package from a T-SQL Agent job step has no RUN AS capability to set the code to run under a proxy account. This is a connect item :-).

Running a package from a T-SQL Agent job where I manually put in EXECUTE AS logic in the job step code also doesn't work.

Stumper?


texts: PostgreSQL - Who or what is the "PUBLIC" role?

texts:
I was looking at information_schema.role_table_grants when I saw public in grantee column, then I've checked at information_schema.enabled_roles but this role_name does not exist.

=> Who or what is the public role?


texts: UPDATE all values of a column with values of a related table

texts:
I read so many Answers on this Website or in other blocks, but i just can't resolve this issue. I'm trying to perform an Update on a column of a table with values of an other, related table.

I'm working with ORACLE SQL Developer. And i mostly get the ORA-01427: single-row subquery returns more than one row. I'm totaly aware of what this error message is saying.
I only need the value of corresponding joined object to be written in on cell and for me it should work by declaring it with de join and with the WHERE condition. But maybe i'm missing something.

I tried:

UPDATE GH210_STELE
SET GH210_STELE.GH210_GH220_ID =(SELECT GH220_AUSSTATTUNG.GH220_ID
                                 FROM GH210_STELE
                                 INNER JOIN GH220_AUSSTATTUNG
                                     ON GH220_AUSSTATTUNG.GH220_INVENTAR_NR = GH210_STELE.GH210_NUMMER
                                 WHERE GH210_STELE.GH210_NUMMER=GH220_AUSSTATTUNG.GH220_INVENTAR_NR);

Or:

UPDATE GH210_STELE
SET (GH210_GH220_ID, desc) = (SELECT GH220_AUSSTATTUNG.GH220_ID,
                                     GH220_AUSSTATTUNG.desc
                              FROM GH210_STELE
                              INNER JOIN GH220_AUSSTATTUNG
                                  ON GH220_AUSSTATTUNG.GH220_INVENTAR_NR = GH210_STELE.GH210_NUMMER)
WHERE EXISTS (SELECT 1
              FROM GH220_AUSSTATTUNG
              WHERE GH210_STELE.GH210_NUMMER = GH220_AUSSTATTUNG.GH220_INVENTAR_NR );

Or even:

MERGE INTO GH210_STELE
USING (SELECT GH220_INVENTAR_NR, GH220_ID
       FROM GH220_AUSSTATTUNG) GH220_AUSSTATTUNG
    ON (GH220_AUSSTATTUNG.GH220_INVENTAR_NR = GH210_STELE.GH210_NUMMER)
WHEN MATCHED THEN
    UPDATE
    SET GH210_STELE.GH210_GH220_ID = GH220_AUSSTATTUNG.GH220_ID;

UPDATE GH210_STELE a
SET GH210_GH220_ID=(SELECT GH220_ID
                    FROM GH220_AUSSTATTUNG b
                    WHERE b.GH220_INVENTAR_NR = a.GH210_NUMMER);

Any help is much appreciated.

Cheers Fabi


texts: MySQL fatal error 1236 when swapping in new master, “master has purged binary logs”

texts:
Our situation: we have three MySQL 5.7.23 replica servers attached to a MySQL 5.7.23 master server running in EC2 instances in AWS. All the servers are fine and GTID replication is working properly. We wanted to increase the size of the EBS volumes by launching a new replica, and then taking one of the existing replicas with already-enlarged volumes and setting it to be the new master. However, every time we do this, we get this error:

  Got fatal error 1236 from master when reading data from binary log:
  ‘The slave is connecting using CHANGE MASTER TO MASTER_AUTO_POSITION =
  1, but the master has purged binary logs containing GTIDs that the
  slave requires.’

Our only option at that point is to switch the old master back in and let the original replicas catch up again. This is fine, but we have a lurking issue here where if the master ever does go bad, we're in danger of losing the entire cluster because we seemingly can't swap in a new master without issues.

The first time we tried this we had a server ID collision and the cluster was left without a master for a minute or so, so we assumed this was the cause of the problem. But we just tried again after ensuring all the server IDs were fine, and the new master was picked up by all the replicas fine but still threw the same error. We've also checked the expire_logs_days setting and made sure they're the same on all servers; this has no effect either, even if we wait a week before trying the operation again (expire_logs_days is set to 3 on all servers).

Our ability to collect information on this error is limited because we're doing this in a production environment and ideally we should be doing this with zero downtime. Any ideas?


texts:
I have an existing DB called MyDb in Azure SQL Server.
I have a bacpac of another DB with several tables in it. I'm interested in importing one single table (that table has no FK, it makes things easier) into a dedicated table MyDb.dbo.ImportedTable. The final goal is to be able to do some data reconstruction using that table.

Problems are:

MyDb.dbo.ImportedTable is ~60 Gb large
The main column in that table is a NVARCHAR(MAX). That forbids me to use Elastic queries in Azure. It times out since Elastic queries hates anything larger than NVARCHAR(4000) (I tried)

I guess a good approach is:
 1. Use BCP but I only have the binary *.bcp files (15'000 of them) that are inside the bacpac archive (opened as a zip, in its data folder)

But I'm unable to make it work, especially because I find no documentation about the *.bcp file format used in the bacpac.

tl;dr What is the good approach to import a single ~60Gb table fro ma bacpac in an existing database in azure SQL Server?


texts: Total hours worked per employee per day including overnight sessions

texts:
I have a table like this:

CREATE TABLE Table1
(ID int, empid int, time datetime, state int);

+--------------+---------------------+-----------------+
|    empid     |       time          |      state      |
+--------------+---------------------+-----------------+
(      4       | 2014-03-01 11:12:00 |         0       )
(      5       | 2014-03-01 12:28:06 |         0       )
(      4       | 2014-03-01 12:50:07 |         1       )
(      4       | 2014-03-01 13:38:00 |         0       )
(      5       | 2014-03-01 13:28:06 |         1       )
(      4       | 2014-03-01 18:42:15 |         1       )
(      4       | 2014-03-02 08:11:08 |         0       )
(      4       | 2014-03-02 13:26:11 |         1       )
(      5       | 2014-03-02 14:16:15 |         0       )
(      4       | 2014-03-02 16:16:15 |         0       )
(      5       | 2014-03-02 17:48:21 |         1       )
(      4       | 2014-03-02 19:39:03 |         1       )
(      5       | 2014-03-02 20:16:15 |         0       )
(      5       | 2014-03-03 04:16:15 |         1       )
+--------------+---------------------+-----------------+

primary key omitted for brevity - please see http://www.sqlfiddle.com/#!9/30503/1

0 means login - 1 means logout. There can be several logins/logouts per user per day or period of days if for example an employee works overnight (as employee 5 does between 2014-03-02 and 2014-03-03 )

I am trying to get the total sum of hours worked per employee per day/shift (e.g. some shifts will span over two days):

1      2014-03-02   total hours worked    08:32:00

I came up with this query:

SELECT CONCAT(
MOD(TIMEDIFF(MAX(CASE WHEN state = '1' THEN time END),
MIN(CASE WHEN state =    '0' THEN time END)), 24 ), ' hours ',
MINUTE(TIMEDIFF(MAX(CASE WHEN state = '1' THEN time END),
MIN(CASE WHEN state    = '0' THEN time END))), ' minutes') as HoursWorked,
empid,
Date(time)
FROM emplog T
GROUP BY empid, Date(time)

Which only gave me the time elapsed between the min login and max logout.

From an earlier question I learned how to get the total working hours per employee per day which does not include night shifts:

select empid, work_dt,
    SEC_TO_TIME(sum(TIMESTAMPDIFF(SECOND,login,logout))) as time_worked
from (
select empid, date(time) as work_dt, time as login
    , coalesce(
          (select min(time)
           from emplog as b
           where a.empid = b.empid
             and date(a.time) = date(b.time)
             and b.time >= a.time
             and b.state = 1
          ), now()) as logout
from emplog as a
where a.state = 0
) as t
group by empid, work_dt;

Still haven't figured out a way to factor in night shifts - looking forward to your input.

There will be employees working during the weekend. Holidays will not be a problem; neither will daylight saving time be.

The application does not have a check in place to prevent out of sync clock punches? For example, clock in/out records out of time-sequence or duplicated due to employee error. I already took care of those kinds of problems in the code I wrote to connect with the device and retrieve the raw data  - might be better to handle all the calculations in my code.

Night shifts count 100 % to the day the shift started on. For example, if you start work on 02.01 before midnight, the total working hours are saved with the 02.01 as the date. If you start after midnight, the total working hours are saved with the 03.01 as date.

If there is no matching logout I manually log the user out with a predefined number of hours in my code that prepares the raw data for the MySQL database.


texts: Postgres inherit from one role and not another

texts:
I'm a little new with postgres but have a decent understanding of roles. What I have are users that inherit from their normal "data_analyst" group. This group is used to manage all data analyst users with basic permissions. But I have a few users that will have elevated privileges. My initial thought was to create a new group for the elevated users and when they need their elevated privileges they would run SET ROLE elevated_role. They want to have to explicitly call those privileges.

They would essentially be a part of two roles, their limited role that they are granted and inherit from and the privileged role they have to set The business and users don't want their elevated privileges to be inherited to their main role. But this is problem based on my understanding of roles. If the user has been granted inherit they wouldn't need to SET their role because they would already have inherited it. How could I set this up? Is there another way I'm not thinking of?


texts:
I know this question may be similar to some others posted but think it may be slightly different, so hopefully it's ok.

Basically I just reinstalled Windows as I bought a new HD and I am in the process of copying all the MySQL databases back to the new installation dir; however I'm not sure if it's ok to copy all the default MySQL databases back with it? Those being:

cdcol
mysql
performance_schema
phpmyadmin
test
webauth

I assume one of these contain details I want to keep such as info about the users and also other stuff like mysql variables - unless that is found in a MySQL .ini file or something?

I'm not 100% sure but I'm pretty sure I'm still using the same version of MySQL. If it matters I am using XAMPP.

So basically my question is can I copy the old folders above over the new ones and what are these folders/databases used for exactly (other than the obvious phpmyadmin one)?


texts:
I have setup SQL Server transactional replication, and then added indexed views at the subscriber, but the indexed views appear to really slow down the subscriber and almost bringing it to a halt.

I have tried to reinitialize the subscription, and even recreate the publication, still the problem remains and as soon as I am adding an indexed view it almost brings down replication.

The interesting part is that I already many indexed views on the subscriber and it just worked well, but recently I have tried to add more indexed views and then it all started, and now I am not even able to have even a single indexed view.

Any advice?


texts: Understanding “bitmap heap scan” and “seq scan”

texts:
I'm trying to understand that which scan is efficient, Here is the explain for the query,

EXPLAIN SELECT *
FROM 'myTable_v' --view
WHERE cast(TIMESTAMP AS VARCHAR(100)) > CAST(1586415955169 AS VARCHAR(100))
AND cast(TIMESTAMP AS VARCHAR(100)) <= CAST(1586416090443 AS VARCHAR(100))

above view created as below,

CREATE VIEW 'myTable_v' AS
SELECT
msg ->> 'customerType'::text AS customertype,
msg ->> 'trackingId'::text AS "trackingId",
msg ->> 'conversationId'::text AS "conversationId",
msg ->> 'authState'::text AS authstate,
msg ->> 'tmoId'::text AS id,
msg ->> 'msisdn'::text AS sdn,
msg ->> 'ban'::text AS ban,
FROM myTable -- main table
WHERE (msg ->> 'type'::text) = 'CTYPE'::text;

Planner output is,
Without index

"Seq Scan on 'myTable' (cost=0.00..472877.98 rows=7300 width=904)"
"  Filter: (((msg ->> 'type'::text) = 'EdsConversationData'::text) AND ((("timestamp")::character varying(100))::text > '1586415955169'::text) AND ((("timestamp")::character varying(100))::text <= '1586416090443'::text))"

and after adding index like below,

CREATE INDEX idx_timestmp_n ON 'myTable' ( (msg ->> 'type'),((('timestamp')::character varying(100))))

Query planner out is,

"Bitmap Heap Scan on 'myTable'  (cost=505.45..49649.59 rows=7300 width=904)"
"  Recheck Cond: ((msg ->> 'type'::text) = 'EdsConversationData'::text)"
"  Filter: (((("timestamp")::character varying(100))::text > '1586415955169'::text) AND ((("timestamp")::character varying(100))::text <= '1586416090443'::text))"
"  ->  Bitmap Index Scan on idx_timestmp_n  (cost=0.00..505.43 rows=14517 width=0)"
"        Index Cond: ((msg ->> 'type'::text) = 'EdsConversationData'::text)"

So, here which one is suggestible to execute the query with index or without?

Appreciated for the responses.

Thank you


texts:
As we know NORMAL redundancy means 2 copies of each block.

I have created diskgroup:

CREATE DISKGROUP FG NORMAL REDUNDANCY
  FAILGROUP controller1 DISK
    'ORCL:DISK3' NAME disk3
  FAILGROUP controller2 DISK
    'ORCL:DISK4' NAME disk4;

I have cleared small number of blocks for the first time.

dd bs=1024 count=200 if=/dev/zero  of=/dev/oracleasm/disks/DISK3

I have corrupted header. And I was able to repair it by kfed repair, because second copy of header (located in aus=0 bs=256) was available.

kfed repair /dev/oracleasm/disks/DISK3  ausz=4194304

And it have been repaired.

But when I've corrupted the whole disk (disk size is 1GB)

dd bs=1M count=1024 if=/dev/zero  of=/dev/oracleasm/disks/DISK3

I was not able to recover disk with kfed

[oracle@node1 ~]$ kfed repair /dev/oracleasm/disks/DISK3  ausz=4194304
KFED-00320: Invalid block num1 = [0], num2 = [1], error = [endian_kfbh]

Can't I rescue disk when I have NORMAL redundancy diskgroup where other disks are healthy?


texts: How to troubleshoot SQL Server Merge Replication Conflict?

texts:
I have SQL Server 2008 R2 Merge Replication setup.

I have 2 publications for the same database with 20+ subscribers.

The logic of our application prevents changing the same data from 2 subscribers at the same time.

Lately I started getting conflicts for some specific tables & almost all the time the contents of the 2 conflicting rows ( the publication row & the subscriber row ) are identical with no differences at all!!

My question is: What is the standard steps to troubleshoot conflicts & trace down to the point that is causing it?


texts: Windows could not start the SQL Server service error

texts:
I have a problem when I run my web project. I get this error :

  A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

Then I found in SQL Server Configuration Manager that SQL Server Express stopped. When I'm trying to run SQL Server Express, another error appears:

  the request failed or the service did not respond in timely fashion. Consult the event log or other applicable error logs for details

When I open services.msc to start SQL Server (SQLEXPRESS instance) then this error happens:

  Window could not start the sql server (SQLEXPRESS) on local Computer. For more information  review the System Event Log. If this is a non-Microsoft service, contact the service vendor, and refer to service-specific error code 3417

Any solution to solve my problem? The version is SQL Server 2008 R2 Express.


texts: How the transaction logs are maintained in MSSQL RDS?

texts:
Just curious, I was asked that if RDS takes transactional log / incremental log backup for point in time recovery, does RDS shrink the logs too?

In MSSQL instance, we shrink logs if transactional logs are full. Do we need to monitor the transactional logs in RDS?

If RDS takes backup from transactional log, is it safe to shrink the logs manually if it goes beyond expectation?


texts: What is the difference between ALTER INDEX and DBCC DBREINDEX?

texts:
Is the only difference between

ALTER INDEX [index_name] on [object_name] REBUILD with (ONLINE=OFF, FILLFACTOR=90)

and

DBCC DBREINDEX([dbname], 90)

just that the DBCC command will reindex all of the indexes on all of the tables in the database?


texts:
I'm having difficulty understanding how to go from 1NF to 3NF. All the tutorials I've looked at are very technical and I just need someone to tell me in plain English how to do it.

I have this table in 1NF (I think) and need to do functional dependency diagram for it. I don't understand how to join the group fields/attributes with arrows i.e. I don't understand functional, partial and transitive dependencies. Help please.

Here is my table:

And here is my (unfinished) dependency diagram:


texts: Page life expectancy error in sql server

texts:
I am new to sql server monitoring.Basically system administrator who worked in organisation set monitoring in window server 2012R2 to monitor memory issues in sql server and I am getting following notification and I don't exactly understand where to look for solution.

Notification I am getting in email:

Memory/memory pressure I checked:

Seem like there isn't any memory pressure but I don't understand why I am getting page life expectancy state transition (Normal to Failed) ?

Thanks in advance for reading/sharing knowledge/helping.

Update in question as requested:

Select @@version result:
Microsoft SQL Server 2008 R2 (SP3) - 10.50.6000.34 (X64)
    Aug 19 2014 12:21:34
    Copyright (c) Microsoft Corporation
    Standard Edition (64-bit) on Windows NT 6.3  (Build 9600: ) (Hypervisor)

Other useful information:

Result of :

select cntr_value from sys.dm_os_performance_counters where object_name LIKE '%Manager%' and counter_name = 'Page life expectancy'

SELECT DISTINCT memory_node_id FROM sys.dm_os_memory_clerks Where memory_node_id<64

cntr_value = 788645; memory_node_id = 0;

I am running two instances on server and Window server 2012R2 is OS with 6GB RAM.


texts: Installing command line tool with pgAdmin4

texts: I have pgAdmin4 and the latest version of PostgreSQL installed on my machine. When I tried to insert large amount of data using INSERT statements contained in a .sql file of size 40 MB, 50MB and above, the app crashed. Hence, I am wondering if dumping heavy data using those sql files via command line is a good idea? If yes, then from where can I install that command line utility in pgAdmin4 ?


texts: Database still reports 600GB after removing 500GB File

texts:
I have a database with three files

Log
Primary Data File
Blob Data file

the database was becoming too big to manage (600GB) so we removed the blob data (500GB) into FileStorage outside the DB.

we then dropped and recreated the Tables into the Primary datafile, following that we ran

Alter Database (db) Remove_File (Blob_Data)

Then ran

DBCC ShrinkDatabase ('db',10)

. As well as a few other shrinks database properties is still reporting the database size as 600GB.

sys.database_files

still remembers the file but it is in the status of 'Offline',

sys.sysfiles

has no mention of the file.

we are currently rebuilding indexes following the shrink, and intend to test the backup and restore process to see if the speed of backup / restore is affected by this.

is this something we need to be worried about?
is there any resolution to this?


texts: How to set 'timing on' permanently in PostgreSQL?

texts:
I work with PostgreSQL in the command line interface. I would like to activate a \timing command permanently for every database in the cluster so every time I connect to a database - this parameter is on.
I searched in postgresql.conf file, but I haven't found anything like what I want to switch on.

My PostgreSQL is 9.5.4 on x86_64 Ubuntu 16.04.2.


texts: SQl Server log shipping monitor security configuration and update problems

texts:
I'm setting up log shipping between instances A and B with the instance C as a monitor. The shipping itself is working fine but I'm somehow unable to make the monitoring work. The log shipping status on C is not up to date - the reports show that no backups and restores are performed. The respective reports on A and B are perfectly up to date, showing that everything is OK. The problem is pretty much obviously in the monitor connections configuration/permissions.
SQL Agents on A and B run as NT Service\SQLSERVERAGENT.
The SA accounts on A, B and C are disabled.

using impersonation with A and B machine domain accounts added to sysadmins on C - doesn't help
using custom SQL logins which are also sysadmins on A, B and C - doesn't help and also keeps switching to SA login in the monitor connection window as well as in the linked server configuration on A and B

What can be the root cause? Is using SA account a requirement?
TIA


texts:
I saw that you could use the new portal to create an alias for your database instance.  I have already created an internal DNS to point to our Azure SQL instance but of course connecting to it wants username@randomname .  How can I make an alias for the "random server name" so it is more usable for my clients?

thanks


texts: Longer time to bring SQL online after node failover

texts:
We had a strange issue post our regular OS patching for SQL servers.
Per best practices, we apply patch on Passive and do node failovers to make current passive, active and vice versa to complete patching.
Generally node failover is seamless and completes under a min. However recently we had issue where it took 4 minutes to bring SQL online after the node failover:
I am checking logs and events but could not find the reason: below is the findings so far:
Note: The SQL server is running on VM

No increased in SQL server activity
Databases are part of DB mirroring
No increase in user connections or user queries running longer in that duration
VLF under 500 for all databases
NO CPU/Memory pressure and LPIM is enabled.
The process which was seemed to killed time out long running during failover was EXEC sp_server_diagnostics 20 running for past 86745234 secs

Please assist what else should i be checking to find the root cause?
Edit- I tried analyzing the cluster log and can see sql offline was initiated but i am not sure where it spend atleast 4 mins internally to actually shut down sql and bring it back. after 4 mins sql error log had all enteries for databases being brought up approx 10 secs. So it looks DB might not hve any involvement here for slowing down process.
Edit- some VLF info when checked currently


texts: Expression index on a citext column ignored, why?

texts:
Running on RDS with about 32M rows.

PostgreSQL 11.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-11), 64-bit

Also testing locally on macOS with about 8M rows.

PostgreSQL 11.5 on x86_64-apple-darwin16.7.0, compiled by Apple LLVM version 8.1.0 (clang-802.0.42), 64-bit

I've got a column named old_value that's of type citext. I asked about this already, but posted way to many of my discovery steps along the way. Here's a boiled down version that I'm hoping gets to the point.

Background

I've got a field change log table named record_changes_log_detail with 32M rows and growing that includes a citext field named old_value.

The data is very skeweed. Most values are less than a dozen characters, some are more than 5,000.

Postgres chokes on large values with an error about B-tree entries being limited to 2172 characters. So I believe that for a B-tree, I need to substring the source value.

My users primary interest is in an = search, a starts-with search, and, sometimes, a contains-this-substring search. So = string% and %string%

Goals

Create an index that supports those searches that the planner uses.

Tried and failed

A straight B-tree fails to build, in some cases, because of long values.

An expression B-tree like this builds, but is not used

CREATE INDEX record_changes_log_detail_old_value_ix_btree
    ON  record_changes_log_detail
    USING btree (substring(old_value,1,1024));

Adding text_pattern_opts does not help.

CREATE INDEX record_changes_log_detail_old_value_ix_btree
    ON  record_changes_log_detail
    USING btree (substring(old_value,1,1024) text_pattern_opts);

Tried and works partially

A hash index works, but only for equality. (Like it says on the tin.)

This is the closest I've gotten to success:

CREATE INDEX record_changes_log_detail_old_value_ix_btree
    ON record_changes_log_detail
    USING btree (old_value citext_pattern_ops);

This works for quality, but not for LIKE. The release notes for PG 11 say it should work for LIKE:

https://www.postgresql.org/docs/11/release-11.html

By "work" I mean "the index is used."

I was unable to substring succesfully with this approach.

What do people do in this situation with citext fields?


texts: MySQL - Column value depends on values from two different tables

texts:
I have a MySQL database that contains these tables (* denotes primary key):

TableA:

----------------------------------------------
|  *id  |  time  |  price  |  special_price  |
----------------------------------------------

TableB:

-------------------------------------
|  *table_a_id  |  *date  |  price  |
-------------------------------------

SpecialDate:

--------------------------
|  *date  |  description |
--------------------------

The value of TableB.price column of each entry in TableB will be either TableA.price or TableA.special_price of the corresponding TableA entry, depending on whether TableB.date is a special date or not (whether TableB.date exists in the SpecialDate table).

This design, as it stands, doesn't feel right as every time I'm adding a record to TableB, I have to look up the date from SpecialDate to see if it's a special date or not, then look up the corresponding entry from TableA to get the value for the price column. And every time a record from SpecialDate gets added or updated, I have to go over every entry in TableB to update its price column.

I'm thinking the column TableB.price should be removed and calculated when needed but I'm not sure how to do that using SQL. I have a lot of views that depend on that price column, for example:

TableAFullView:

-----------------------------------------------------------------------------
|  id  |  time  |  count_of_table_b_instances  |  sum_of_table_b_instances  |
-----------------------------------------------------------------------------

Where count_of_table_b_instances is the number of TableB entries that point to this TableA entry and sum_of_table_b_instances is the sum of their prices.

With my original design, this view is simple to implement and it performs well. But if I get rid of the column TableB.price, how would I implement this view? Will the performance take a hit?


texts:
I installed Oracle Client 12.2.0.1 (x32) on a Win7 x64 machine.  When I tried to create an ODBC by invoking "C:\Windows\SysWOW64\odbcad32.exe" and picking the "Oracle Client" from the list and clicked "Next", I immediately get this error message:

"... ODBC driver could not be loaded due to system error code 126: The specified module could not be found C:\app\client\oracle\product\12.2.0\client_1\BIN\SQORAS32.DLL)"

I have been Googling around and people were able to resolve this issue by installing Visual C++ Redistributable 2013 for Oracle Client 12.2.0.1.  I downloaded and installed but still get the error.

https://www.microsoft.com/en-us/download/details.aspx?id=40784

Does anyone have any idea what my problem could be?


texts: execution sequence of parallel execution plan

texts:
I am reading an article about Understanding Parallel Execution

There is a paragraph which is as follows:

But for my understanding of how to read an execution plan, this parallel execution plan starts from operation 7 not operation 14.

Why this article emphasize that the execution plan starts at operation 14?? It upside down my knowledge of execution plan.


texts: Query for orders that contain a product without using sub-query

texts:
Order
-----
OrderId

OrderDetail
-----------
OrderDetailId
OrderId
ProductId

How do I write a query that returns all orders that contain a specific product without using a sub-query?

I don't want to write a sub-query because I want to encapsulate the query in a view and use a WHERE clause on the view to supply the product id instead of having a stored procedure and supplying a parameter.

I figure the only way to do this is to join orders to details and GROUP BY on order but even then I don't know how to have the aggregate column resolve to whether the product is amongst the order details.


texts:
I have the following query

SELECT Ice.IceId, Ice.Code, Box.Ice, LEN(Box.Ice) AS IceCharLength,
       DATALENGTH(Box.Ice) AS DataLength, Box.Ice, ASCII(Box.Ice) AS ASCII,
       CAST(Box.ice AS VARBINARY) AS VarBinary
FROM dbo.tblESPShipBox Box
LEFT JOIN ReferenceManual.IceType Ice
       ON Ice.Code = CASE
                        WHEN Box.ICE IS NULL THEN 'N'
                        WHEN RTRIM(Box.ICE) = '' THEN 'N'
                        WHEN Box.Ice IN ('', ' ', '/') THEN 'N'
                        ELSE Box.ICE
                      END
WHERE BOX_SEQ_NUM = '000023' AND BOX_TYPE_ID = 0

It returns the following results:

IceId       Code Ice  IceCharLength DataLength  Ice  ASCII       VarBinary
----------- ---- ---- ------------- ----------- ---- ----------- -------------
2           N         0             1                32          0x20
NULL        NULL      1             1                0           0x00

The weird thing is that Box.ICE for the first row is ' ' (Single Space String).  The second row seems to be '' (No space string).  (The way I see this is by copying the cell out and pasting in into other text to see if it makes a space or not.)

The Box.ICE column is a char(1) so I don't really see how a '' (no space string) got in there.

But the weird part is that my case statement should still match on a '' (no space) char.  But clearly it does not.

How can I figure out what is really in this field and make it match my case statement?

Things I have tried:

• Used SSMSBoost to do a special char included copy of the cell into NotePad++ (with show all chars on).  I did this to make sure there are no unplayable unicode chars.


texts:
Is there a way to encrypt the whole data of a mysql DB using the AES 256 symmetric encryption method without encrypting the data manually using AES_ENCRYPT ?

This article explains about the same functionality in MsSql databases


texts:
I have two tables in my schema

students(id text, matric text)

attendance(id serial, a_date date, matric text references students matric)

To get records showing date and student present, I can run a query like

select a.a_date, s.matric
from students s, attendance a
where a.matric = s.matric
and a.a_date between '2015-01-01' and '2015-04-01'

I want a query for the records showing date and student absent.

For more information, please visit http://sqlfiddle.com/#!15/e43a9/6 to view the schema and sample data


texts: Should there be a temporary table to hold Order and OrderItems?

texts:
After trying out a few open-source restaurant  POS, I still couldn't decide if there should be a temporary to hold Orders and OrderItems?

I was initially thinking to have temporary tables and permanent tables for Orders and OrderItems and move a record from temporary tables to permanent tables after an order is Closed/Voided. So, temporary table is not immutable and permanent table is immutable. But it seems having both temporary table and permanent tables complex the process because records have to move from temp to permanent table, delete records in the temp table.

Will it be better to have just one permanent table?


texts: In which timezone does psql display "timestamp without timezone"?

texts:
What I mean is the following: if I create a timestamp without timezone (timestamp) column and store there some value, internally microseconds are stored, but when issuing select this is converted to a string with date and time. But in which timezone the converted time is?
I tried to change timezone in my computer (PostgreSQL is running locally, so I reopened psql), tried changing it in postgresql.conf (restarting the service) and setting timezone per session. However as far as I saw, the output doesn't change. I'm sure I'm missing something. Thanks!
Sample code:
create table tzo (wotz timestamp);
insert into tzo values (now());
table tzo;

Output:
            wotz
----------------------------
 2020-08-11 17:51:37.244901
(1 row)


texts: Can sst_state_transfer be set to rsync with innodb as storage engine in Galera cluster?

texts:
Galera cluster wsrep-sst-method docs states:

  You can only use rsync when anode is starting. You cannot use it with
  a running InnoDB storage engine.

I do have InnoDB storage engine configured in MariaDB Galera cluster and the wsrep-sst-method is set to rsync and state transfers do happen on starting nodes.

What's wrong with this configuration? Is xtrabackup the only wsrep-sst-method to be used with innodb storage engine?


texts:
I am having an issue trying to migrate data out of a filegroup to empty and remove the filegroup. I've moved all indexes/tables and all partition schemes using the filegroup to the new filegroup, but I cannot remove the last file from the old filegroup. The error I get is that the file isn't empty.
I've tried the EMPTYFILE trick but this doesn't work as it says there is still data in the file, which seems to be true as querying sys.allocation_units shows around 30 MB of data still in the file. Querying index, partition, object and other DMVs doesn't report anything still using this filegroup as data space.
What other DMVs/DMFs are there to show me what the data left in the file actually is?
SQL Server 2016 Enterprise Edition


texts: When does sp_executesql refresh the query plan?

texts:
You'll have to forgive my naivety as I'm not a DBA but my understanding is that over time the statistics of a database change and a stored procedure must be recompiled to keep the query plan up to date with the latest statistics.

Assuming I have a stored procedure in my database that is recompiled against the latest statistics at some regular interval, what are the implications of in-lining the stored procedure in code and wrapping it in an sp_executesql statement?  Do I lose the refreshing of the query plan that used to happen as part of the recompilation of the procedure?

If there's anything else (other than permissions) that I need to consider before I make this change then I'd appreciate your insights.

I read this on MSDN:

  The ability of the SQL Server query optimizer to match the new
  Transact-SQL string with an existing execution plan is hampered by the
  constantly changing parameter values in the text of the string,
  especially in complex Transact-SQL statements.

So assuming the stored procedure I'm attempting to in-line and wrap in sp_executesql does indeed contain some parameters, is this saying that although my execution plan is cached, I'm making it more difficult for SQL Server to find and reuse it?


texts: Is there really any benefit in wrapping random ad-hoc queries in a transaction?

texts:
I am no db expert, but I was very surprised when someone working at our client's company asked us to send all queries wrapped in a transaction like below:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
BEGIN TRANSACTION;
    SELECT * FROM <table_name> WHERE ..etc
COMMIT TRANSACTION;

I have never had such a request before when all we're doing is reading data (random ad-hoc queries). Is there any sense in this? Can anyone tell me why this would be necessary?

Many thanks


texts: If I manually failover my AlwaysOn AG primary replica to the secondary and take the primary database offline will it auto-sync when back online?

texts:
I have an AlwaysOn AG setup between Database A (primary replica) on Server 1 and Database B (secondary replica) on Server 2.

If I force a manual failover from my primary replica on Server 1 to the secondary replica on Server 2 and then take Database A (on Server 1) offline, will it automatically sync back up from Database B (on Server 2) when I bring it back online?

Do I have to wait for the sync to finish before manually failing back over to Database A from Database B?

Can I track it's sync progress in the AG Dashboard?

Note: I took Database A offline without removing it from the AG by stopping the SQL Server service. So I'm wondering what will happen when I start the service again?


texts: Returning rows in PostgreSQL with a table called "user"

texts:
I have a table called user

I have some SQL that is select User.* from User

This fails because User is a reserved word however, I was under the impression that Postgresql didn't care about the case of the tables when executing the SQL so I assume there is a conflict because of the reserved word.

In the database the user table is lowercase so if I run this

select * from user

I get a result, although bizarrely I only get one column returned even though I said show all columns.

If I run select * from "user" I get all columns and also SELECT "user".* FROM "user" returns all columns.

Can someone explain what's going on please?

Thanks

(Using PostgreSQL 9.3)


texts: Oracle Buffer Pool TARGET_SIZE is different from CURRENT_SIZE, but RESIZE_STATE is STATIC

texts:
According to the documentation for V$BUFFER_POOL, TARGET_SIZE shows that target size when resizing, but should be the same as CURRENT_SIZE if RESIZE_STATE is STATIC.

However, on one of our databases TARGET_SIZE is much larger than CURRENT_SIZE (which is currently far too low), but RESIZE_STATE is STATIC. How can this state come about?


texts: How to get data from MySQL table based on a junction table

texts:
I've been trying to tackle this problem but couldn't do it properly, any help is really appreciated.
This is the Distance calculation query based on latitude, longitude and distance in KMs
SELECT *
FROM (
         SELECT *,
                (
                        ((acos(sin((27.6677608 * pi() / 180)) * sin((activities.latitude * pi() / 180)) +
                               cos((27.6677608 * pi() / 180)) * cos((activities.latitude * pi() / 180)) *
                               cos(((68.8543843 - activities.longitude) * pi() / 180)))) * 180 / pi()) * 60 *
                        1.1515 * 1.609344
                    )
                    as distance
         FROM `activities`
         WHERE is_public = true
           AND (
                     DATEDIFF(end_time, start_time) > 0
                 OR
                     TIME_TO_SEC(TIMEDIFF(TIME(end_time), TIME(start_time))) > 10
             )
     ) markers
WHERE distance <= 22

This is the activity related table ER diagram

Now what i have been trying to do is that the user provides latitude, longitude and distance from which i calculate activities within the distance, lat/longs provided by the user.
PROBLEM => problem is that i want to get activities which are not only close to user's lat/long and distance but also matching certain interest_id from activity_interests table.
SCENARIO => for instance, i provide these details as payload
{
    "latitude": "27.6677608",
    "longitude": "68.8543843",
    "distance": 7,
    "interests": [1,2,3]
}

Now i want Activities which have all above payload Interest ids present in the Activity_interests table and still do the distance calculation for which i have provided the above SQL query.
MySQL version 8.0 is used
Thank you, and i hope i explained it well.


texts: How to grant a user the rights to access to all new (future) databases?

texts: On my development machine, I often create new web instances that all run as NT AUTHORITY\NETWORK SERVICE. I want to grant this account rights to access all new databases that I create in the future in my SQL Server 2008 R2 instance. I want to do this because it is too much of a hassle to grant rights to new databases every time I create a new web site.


texts: TDE enabled database backup taking too long

texts:
We have recently implemented TDE and backup compression is also enabled. Before TDE backup used to take about 20min but now it takes about one hour.

I have set @MaxTransferSize to 3MB, 2MB, 1MB and 0.5MB but didn't make any difference time wise, database is 90GB of size and it is SQL Server 2016 SP1 CU4.

We using Ola's Maintenance Solution/Scripts to backup the database. Any idea why it is taking 3x longer when TDE enabled please?


texts: with MySQL, are multiple single column indexes faster than one multi-column index?

texts: I have a performance related question, in MySQL, are multiple single column indexes faster than one multi-column index?


texts: My question is, How to know the amount of memory allocated to PostgreSQL and whether it is sufficient or not.


texts: InnoDB slow query (few derived and dependent queries)

texts:
I have a select query which always worked pretty fast but data kept increasing which is why this query became very slow (2/3mins).

I need to get all 0 values from those tables in the last WHERE clause.

I'm talking about the following query:

SELECT * FROM (
    SELECT
        p.product_id, p.productcode, p.product_naam, s.station_omschrijving,
        (SELECT COALESCE(SUM(ab.bedrag),0) FROM afschrijvingen_bedragen ab
            WHERE ab.product_id = p.product_id AND ab.station_id = pro.station_id) as afschrijvingenbedrag,
        (SELECT COALESCE(SUM(dkb.bedrag),0) FROM directe_kosten_bedragen dkb
            WHERE dkb.product_id = p.product_id AND dkb.station_id = pro.station_id) as directekostenbedrag,
        (SELECT COALESCE(SUM(ikb.bedrag),0) FROM indirecte_kosten_bedragen ikb
            WHERE ikb.product_id = p.product_id AND ikb.station_id = pro.station_id) as indirectekostenbedrag,
        (SELECT COALESCE(SUM(mb.bedrag),0) FROM materiaalverbruik_bedragen mb
            WHERE mb.product_id = p.product_id AND mb.station_id = pro.station_id) as materiaalverbruikbedrag,
        (SELECT COALESCE(SUM(pb.bedrag),0) FROM personeelsinzet_bedragen pb
            WHERE pb.product_id = p.product_id AND pb.station_id = pro.station_id) as personeelsinzetbedrag,
        (SELECT SUM(pr.aantal) FROM productie pr
            WHERE pr.product_id = p.product_id AND aantal != 0) as productieaantal
    FROM producten p
    INNER JOIN (SELECT DISTINCT product_id, station_id FROM productie) pro
            ON pro.product_id = p.product_id
    INNER JOIN (SELECT DISTINCT station_id, station_omschrijving FROM stations) s
            ON s.station_id = pro.station_id
) innerQuery
WHERE innerQuery.afschrijvingenbedrag = 0
   OR innerQuery.directekostenbedrag = 0
   OR innerQuery.indirectekostenbedrag = 0
   OR innerQuery.materiaalverbruikbedrag = 0
   OR innerQuery.personeelsinzetbedrag = 0

An EXPLAIN SELECT gives me these results:

How to speed up the query? re-do the index? query code?


texts: When is the best time to use RMAN vs when to use Data Pump?

texts: And also... if I backup a database with RMAN, is it not possible to use impdp to restore small parts of that database?


texts:
I just need a reliable Script by which I can get all the missing index report.

I found many but they don't want to work.


texts: Oracle Database Vault: Changing DV Owner and DV Account-Manager passwords hangs PX Deq: Execute Reply/row cache lock

texts:
When changing the password of the users mentioned above the sessions hangs. And it seems to be a deadlock. When changing the password it seems that two sessions are spawned:
Blocking session
C##DVAM_ROOT
1 26 58821  Concurrency row cache lock
Waiting session:
C##DVAM_ROOT
0 405 45629 fuvf4b27452y5 Idle PX Deq: Execute Reply

fuvf4b27452y5 -> /* SQL Analyze(405,0) */
SELECT /*+PARALLEL(1) NO_STATEMENT_QUEUING */ *
FROM X$KXFTASK /*kpdbReplayDDL,PDB_Replay_DDL*/

When an user with with dv_patch_admin role changes the password it works. When disabling Database Vault it works.
I didn’t find this in oracle support.
This is an Oracle Database 19.9 Multitenant.


texts: USERS Tablespace Best Practices for Application Schema

texts:
When creating a user/schema to hold external application data, what are best practices for the tablespaces that the user is defaulted to?

Should the user created be defaulted to the USER tablespace for the default tablespace?  Does this hold true for the TEMP tablespace?  Or should this user have a separate default and temp tablespaces created apart from the SYSTEM user tablespaces to hold the application data?

I tried looking for any indication of this in the Oracle documentation, but wasn't able to find anything close to what I was looking for - apologies if this is subjective in nature.


texts: SQL Server 2014 SP2, backup and restore with powershell

texts:
I have a task where i have to take the backup with encryption and restore it to destination using the powershell.

I used the invoke-sql command and use the tsql in it but this command is not reliable for us rather we want to use the SQL powershell i.e backup-sqldatabase & restore sqldatabase.

my questions are what are the process to perform it (please i dont want to use invoke-sqlcmd)

create master using powershell ( i use "create master key encryption
by password = 'Abcd1234!'" in invoke-sqlcommand)
Create certificate using powershell
backup-certificate
create backup using encryption


texts: The actual number of row of Lazy Spool is huge

texts:
The SQL Server execution plan shows the Lazy spool has 7935322 actual number of rows, but its child operator only has 32 rows.

Nested Loop (Inner Join, 2 rows)   <--- Hash match(3967661 rows)
       ^
       |
       +--- Lazy Spool (7935322 rows)  <-- Merge Join (32 rows)

If lazy spool sourced from its child rows as a temp table, it should only have 32 rows at maximum. I don't understand why does it have 7935322 rows.

Lazy Spool properties

Actual Rebinds: 16
Actual Rewinds: 3967645
Number of Executions: 3967661

Nested Loop properties:

Actual Number of Rows: 2
Number of Executions: 16


texts:
I try to access real-time data changes with pymongo.mongo_client.MongoClient.watch(), code example (Python 3.5.3, pymongo==3.7.2):

from pymongo import MongoClient
DB_CON_STR = "mongodb://10.10.10.1:27017/?replicaset=rslogs"
mongo_rs = MongoClient(MDB_CON_STR)
MDB_SCHEMA = 'logs'
MDB_COLLECTION = 'rqs'
for change in mongo_rs[MDB_SCHEMA][MDB_COLLECTION].watch():
    print(change)

and don't get any output.

Replica Set consist from five mongod instances,
Earlier two weeks ago used db.version() 3.4 with set featureCompatibilityVersion: 3.2,
then updated to db.version() 3.6.9, WiredTiger storage engine, pv1, featureCompatibilityVersion: 3.6, logSizeMB: 51200.

logs contain:

  2019-02-25T15:02:55.353+0000 I COMMAND  [conn30246] command logs.rqs command: getMore { getMore: 6649875775522786240, collection: "rqs", lsid: { id: UUID("") }, $clusterTime: { signature: { hash: BinData(0, 0000000000000000000000000000000000000000), keyId: 0 }, clusterTime:Timestamp(1551106974, 13) }, $db: "logs", $readPreference: { mode: "primary" } } originatingCommand: { aggregate: "rqs", pipeline: [ { $changeStream: { fullDocument: "default" } } ], cursor: {}, lsid: { id: UUID("") }, $clusterTime: { signature: { hash: BinData(0, 0000000000000000000000000000000000000000), keyId: 0 }, clusterTime: Timestamp(1551106971, 9) }, $db: "logs", $readPreference: { mode: "primary" } } planSummary: COLLSCAN cursorid:6649875775522786240 keysExamined:0 docsExamined:16 numYields:16 nreturned:0 reslen:210 locks:{ Global: { acquireCount: { r: 36 } }, Database: { acquireCount: { r: 18 } }, oplog: { acquireCount: { r: 17 } } } protocol:op_msg 927m

In what could be the problem?


texts: Idempotent PostgreSQL schema dump that can be applied to an existing DB

texts:
I'm looking for a way to replicate a PostgreSQL schema to a database which may already have some of it defined. I wonder if there's a simple way to produce DDL that is idempotent (i.e. that can be applied any number of times) from PostgreSQL.
Currently I obtain DDL from my source DB using pgdump -s, and manipulate it using the ad-hoc Python code below. I wonder if there's a cleaner solution.

import re

def MakeDdlIdempotent(sql):
    "Make PGSQL DDL idempotent by changing various 'create' statements to idempotent versions"
    ddlReplacements = {
        "CREATE SCHEMA":   "CREATE SCHEMA IF NOT EXISTS",
        "CREATE TABLE":    "CREATE TABLE IF NOT EXISTS",
        "CREATE INDEX":    "CREATE INDEX IF NOT EXISTS",
        "CREATE VIEW":     "CREATE OR REPLACE VIEW",
        "CREATE FUNCTION": "CREATE OR REPLACE FUNCTION"
        }

    for orig, replacement in ddlReplacements.items():
        sql = sql.replace(orig, replacement)

    addConstraintRe = re.compile(r"ALTER TABLE [^;]+ ADD CONSTRAINT [^;]+;", flags = re.DOTALL | re.MULTILINE)
    sql = addConstraintRe.sub(
        r"""DO $$
BEGIN
  BEGIN
    \g<0>
    EXCEPTION
        WHEN invalid_table_definition OR duplicate_object OR duplicate_table THEN
    END;
END $$;""",
    sql)

    return sql


texts: PostgreSQL requires "GROUP BY" when aliasing a table?

texts:
Here's something strange: there is a large table named "products" which is partitioned with inheritance such that there are two children: products_active and products_inactive, and the constraint is that in products_active, status=1, and products_inactive gets all the other statuses.

There is a large query which joins a bunch of tables, of which this is the first part:

SELECT
  products.id, products.status, products.brand_id, products.name, products.description, products.data, products.website,
  products.packaging, products.container, products.country_of_origin, products.category_id, products.product_type_id, products.tsv_keywords,
  COUNT(prices.id) as prices_count,
  ROUND(AVG(currency_convert(prices.amount,currencies.currency_code,'USD')),2) as avg_price,
  ROUND(MAX(currency_convert(prices.amount,currencies.currency_code,'USD')),2) as high_price,
  ROUND(MIN(currency_convert(prices.amount,currencies.currency_code,'USD')),2) as low_price,
  ts_rank(tsv_keywords, plainto_tsquery('merlot')) as rank,
  ROUND(AVG(ST_Distance(ST_GeographyFromText('SRID=4326;POINT(0.001 0.001)'),ST_GeographyFromText('SRID=4326;POINT(' || stores.longitude || ' ' || stores.latitude || ')')))) AS distance
FROM
  products
  JOIN product_types ON products.product_type_id = product_types.id
  JOIN categories ON products.category_id = categories.id
  JOIN prices ON prices.product_id = products.id
  JOIN currencies ON prices.currency_id = currencies.id
  JOIN stores ON prices.store_id = stores.id
  JOIN brands ON products.brand_id = brands.id
  JOIN merchants ON stores.merchant_id = merchants.id
  JOIN manufacturers ON brands.manufacturer_id = manufacturers.id
, delivery_zones
WHERE ...

(full query here: http://pastebin.com/VjJPTQWj)

The problem is: note the FROM products... part, if I replace this with FROM products_active AS products, the query errors out with:

ERROR:  column "products.status" must appear in the GROUP BY clause or be used in an aggregate function
LINE 2:   products.id, products.status, products.brand_id, products....

The two tables (products and products_active) have identical structures! They are inherited from one another!

Am I missing a subtlety in aliasing table names?


texts: How do I store data with a query that's approximated?

texts:
I'm trying to find a way to store my data with fast access (better than O(n)).

My database consists of data (4096 byte strings) that represents some information about some items.
The problem is, that the query is never exact. I get one Item, and then need to find the closest match using a function F(a,b).

just an example:

1234
3456
6466
F(a,b) = return % of similar digits

GetClosest(1233,F) = 1234

The problem is that F(a,b) is a complicated algorithm, (not a proper metric).

What I have now is just go over the whole database to search for the best match.
Is there a kind of tree or other cluster database type that can give me faster finding complexity ?

More information:

F gives back a similarity value in %percentage. where 100% is a perfect match.


texts:
I have a T-SQL agent job that I've created that adds steps to itself depending on a query of the MSDB database.

I am able to run the query outside of the agent, inside a query editor window, but when I try to run the query as a job I get the following errors:

  Executed as user: [user with sysadmin rights for testing]. The SELECT
  permission was denied on the object 'sysjobsteps' database 'msdb',
  schema 'dbo'. [SQLSTATE 42000] (error 229) The EXECUTE permission was
  denied on the object 'sp_update_jobstep', database 'msdb', schema
  'dbo'. [SQLSTATE 42000] (Error 229). The step failed

As I said in the error, I've tried this with a user with LUA, a user with the sysadmin role over the server, a user with owner role on the msdb database, and I've tried explicitly granting the needed permissions on the msdb database.

What have I done wrong in the creation of the agent job?

Let me know if you need more info.

UPDATE: I removed the Run As: user from the Advanced tab of the step and the job ran successfully.  Why doesn't that work as I expected it to?


texts: Allow public use of functions without giving them database control

texts:
I came up with a set of functions that I would like to be accessed from a variety of places.  SQL seemed like the best place for this due to accessibility, but I receive this error when trying to use them with a user with only the db_datawriter and db_datareader permissions: "The EXECUTE permission was denied on the object"... etc etc

I want to pass a date and return other dates.  Apparently I can't just

SELECT [dbo].[fn_Acct_Day_One] ('1/3/2015')

Any way I can do this without granting db_owner to the user?


texts: multiple child records in a single row by join

texts:
I have one master table (teacher) structured like

teacherId   Name        Class
1       Praveen     10
2       John        9

and having a child table (student) structured like

studentId   teacherId   Name
1       1       David
2       1       Creg
3       2       Mike
4       2       Steve
5       2       Jim

How I can get a result set like

teacherId   teacher     studentId_1 student_1   studentId_2 student_2   studentId_3 student_3

1       Praveen     1       David       2       Creg        null        null
2       John        3       Mike        4       Steve       5       Jim

Thanks in advance..

Regards,
Praveen


texts:
I have a database in MySQL 5.7 where I create a Lat/Long column using "POINT", then I create a Index on this column with main key as second column. So my database is like

PID, SurveyID, GeoPoint

Basically this is a summarize table where PID and SurveyID makes primary key for table. We do a survey on Animals and this summarize table record which Animal is surveyed at given Geo location on map.

My Index was on column GeoPoint and PID. This table store around 400K record which is summarise from main table that has 2M record for faster execution.

Now, we are planning to upgrade to MySQL 8.0 and we found that it doesn't support Spatial column been index with non-spatial column, so our index was not getting created. This results in our Query now take 8 second instead of 0.6 seconds from older version of MySQL.

More over when I create index only on Spatial Column geopoint, ST_Contains, and MBRContains do not use that Index. Basically our query is as simple as we allow user to draw a Square on Google map and then we use those coordinate to find all Animals in that region.

I am not sure how to solve this issue, as no documentation is found by me for it that help tweaking the query.

Query:

SELECT  PID, count(distinct SurveyID) as totalsurvey
    FROM  locationsummary
    where  st_contains(ST_Envelope(ST_GeomFromText(
         'LineString(137.109375 21.47351753335, 87.890625 -22.411028521559)')),
              geopoint )
    group by  PID

Table:

CREATE TABLE locationsummary (
 PID bigint(20) NOT NULL,
 SurveyID bigint(20) NOT NULL,
 Sitelat float NOT NULL,
 sitelong float NOT NULL,
 geopoint point NOT NULL,
 PRIMARY KEY (`PID`,`SurveyID`),
 SPATIAL KEY `idx_geopoint` (`geopoint`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin ROW_FORMAT=COMPACT

Edit: 12th July 2019
Today I try to rerun queries and it is now executing in 2 seconds, still not good though, but some how it become better from last run.


texts: Are files and file groups included when doing a full backup?

texts: I have a database with 4 files and 2 file groups. According to the size of the files, it's 1TB. However, the backup (non-compressed) is only 350GB. Is this typical? Or, do I need to specifically select every file/file group when doing backups?


texts: Strategy to deal with multi billion row simulation results and partitioning

texts:
I am trying to come up with a good way to deal withthe results of statistical simulations from a dba point of view. We generate about 500 million rows per day, most of which are "garbage" (i.e. the results are seen and discarded as not something we look for) and some need to be preserved. Dealing with them outside of partioning is hard.

Data is currently MOSTLY in a 3 table hierarchy (trade--order--update) with a trade having multiple orders which get multiple updates each. There is a 4th table (parameter) that contains the parameter for every simulation. This is small and unproblematic though.

We right now write the data to 3 staging tables and analyze there - temporary solution.

I would like some people to review this idea.

Partition the staging tables with x "buckets". A simulation assigns a bucket (smallint) ad then writes into this bucket. This allows fast deletion of a simulation. AS we only run about 100 simulations per week, a 1000-20000 partition set on the tables is enough to keep the data as long as we need (initial review).
When data is ok, we move it from the staging (via stored procedure) into final data warehosue tables. Again, we need to partition them, and we will use a similar bucket approach. As mulitple simulations will run into identical buckets (updating the data) this is a relatively small number of buckets.

Anyone done that?

The idea behind the bucket approach is that I can pregenerate the buckets and do not have to modify the partitioning function. Sadly SQL Server, contrary to Oracle, has no auto partition, otherwise I could use a simple ID field. I really try to avoid dynamically modifying the partitioning schema here. This way I can have a simple smallint "bucket id", a prepared partitioning schema and can basically assign every simulation / run a bucket id -easy to join. Any negatives?


texts:
Microsoft SQL Server 2016 (SP1-CU6) (KB4037354) - 13.0.4457.0 (X64)

The database's total size is 450 GB, from which 350GB is inital filestream size
The average picture size is around 200Kb, the total number is around 118,000, and same number of filestream files.
The database configuration has 1 filestream file
I have two virtual machines (with IFI enabled on both):

The first has read/write of 30MB/s, the second has 300MB/s
The backup on the first VM takes around 4 hours, restore takes around 40 hours
Restoring on the second VM takes around 4 hours

The question is, why is SQL server on both VMs not using max IO speed of disks when it is clearly available?
The only wait types during both restores are BACKUPIO and BACKUPITHREAD.


texts: Is there a way to run mysqldumpslow against slow queries logged to a table?

texts:
AWS RDS by default logs slow queries to a database table.

Is there a way to run mysqldumpslow against that table, without exporting it to a file first?  Or is there a better way to get a report of slow queries?


texts:
I have a stored procedure that runs xp_cmdshell with a user with sysadmin right.(it was created by someone else long time ago). For security purposes I need to remove the sysadmin right from this user.
So I created a proxy account and gave execute privilege to this user on the xp_cmdshell (practically followed the steps how to run xp_cmdshell with a non sysadmin user ).
But when I run the stored procedure I am getting the below error:

  error number 15466    severity 16  errorline 1    An error occurred during decryption.

I use SQL Server 2008

What could be the problem?


texts: Create failed for Availability Group 'AGN'

texts:
I tried creating availability group (AOH) many times but no luck , It got failed every time. Please help me on this case.

I configured Windows cluster (Name as 'WINCLUSTER') with two nodes. They are
1. Node1
2. Node2

in that I configured SQL server cluster (Active/Active) with  two instances.
one instance named as 'HRITS\BAK' and other 'HRITS1\BAK1'.

I made sure configuration met all prerequisite and important steps before configuring AOH.

I created on database as sampledb in 'HRITS\BAK' instance in node1.
and I tried to creating Alwayson group with necessary steps.

But I am facing one error and not able to troubleshoot. Please find the below screen shot and please provide guideline for configuring it successfully.

Please let me know if any further details are required for resolving this issue.


texts: Why is BULK INSERT Considered Dangerous?

texts:
I would like to understand why cyber-security teams in general (more than one organization I've dealt with) is dead set against granting BULK INSERT (e.g. TSQL) rights to applications and database programmers? I can't believe the "filling up the disk abuse" excuse, unless I'm missing something, as the end result is no different than an application doing something like:

for (long i = 0; i < LONG_MAX; ++i)
    executeSQL("INSERT INTO table VALUES(...)");

and INSERT is a common DML command that anyone with basic write permission can execute.

For an application's benefit, BULK INSERT is far more efficient, faster, and relieves the programmer of the need to parse files outside of SQL.

Edit: I originally asked this question on the information security site for a reason - it is not the DBA's that are against using BULK INSERT, it is the "Information Assurance" (IA for short - the Cybersecurity folks) that are forcing the issue. I'll let this question stew for another day or two, but if the bulk operation does indeed bypass constraints or triggers, I can see that being an issue.


texts: MySQL on RDS, data transfer from one instance to another as a production job

texts:
I have a MySQL RDS instance ("production") from which I need to copy incremental data to another MySQL RDS instance ("reporting").  “Incremental data” being the new rows in certain tables.  Currently I accomplish this via a PHP script which queries from “production” based on the last update time and writes on CSV files to disk and then I use mysqlimport.exe to import the files into “reporting”.
My current method takes ~80 seconds.  Is this the most robust and efficient way to do this?  I’ve found other tools for “one off” transfers, but nothing in terms of an “enterprise incremental database replication” tool.
Our current process is not a “continuous” replication because of batch operations on the reporting side.  This may be negotiable though.  Would easing that constraint open up tools that would make this easy?

Relevant related articles/questions:
Amazon’s article on importing data seems to mainly deal with the batch (initial load) case.
http://aws.amazon.com/articles/2933

This can be done with a stored proc when moving from one DB to another on the same instance (the problem with my case is they are different instances).  See: Keeping MySQL staging database on same server updated from production
I can't put both DBs on the same instance because our instances are under quite a bit of load.