texts: Does it makes sense to index a primary key if it is not used?

texts:
I have a database with many tables which can be joined by indexed keys (primary and foreign keys). Some tables are transaction tables which have an autoincremental primary key just to define a ID for each transaction.

To use this database and process the data (cleansing, adding new columns) I copy the database. The processes on the new database (updates, inserts) are quite complex. I index columns where it makes sense but I don't want to index columns which  I don't need due to reason of performance.

Now, I come to the point: there are several huge transactions tables which have been defined from the original database as primary key but which are not used as key for joining. I would like to remove these primary keys (not the column but just: ALTER TABLE trans DROP PRIMARY KEY)  to avoid too many indexed columns. Is there any reason not to remove index on a primary key?

If this question is dependent on RDBMS: I use MySQL/MariaDB.


texts:
I'm looking for advice on either optimizing multiple self-joins, or a better table/DB design.

One of the tables looks as follows (relevant cols only):

CREATE TABLE IF NOT EXISTS CountryData (
  countryDataID INT PRIMARY KEY AUTO_INCREMENT,
  dataID INT NOT NULL REFERENCES DataSources (dataID),
  dataCode VARCHAR(30) NULL,
  countryID INT NOT NULL REFERENCES Countries (countryID),
  year INT NOT NULL ,
  data DEC(20,4) NULL,
  INDEX countryDataYear (dataID, countryID, year));

The data column has values for a few hundred indicators, 90 countries, and 30 years for ~1mn rows total. A standard query requires selecting N indicators for a particular year and C countries, yielding a CxN table for 90 rows max.

With all values in a single column, self-joins seemed like the way to go. So I have experimented with various suggestions to speed those up, including indexing and creating new (temp) tables. At 9 self-joins, the query takes a little under 1 min. Beyond that, it spins forever.

The new table from where the self-joins take place has only about 1,000 rows, indexed on what seem to be the relevant variables - creation takes about 0.5 sec:

CREATE TABLE Growth
    SELECT dataID, countryID, year, data
    FROM CountryData
    WHERE dataID > 522 AND year = 2017;

CREATE INDEX growth_ix
    ON Growth (dataID, countryID);

The SELECT query then arranges up to XX indicators in the results table, with XX unfortunately <10:

SELECT
    Countries.countryName AS Country,
    em01.em,
    em02.em,
    em03.em
    ...
    emX.em
FROM
    (SELECT
        em1.data AS em,
        em1.countryID
    FROM Growth AS em1
    WHERE
    em1.dataID = 523) as em01
    JOIN
    (SELECT
        em2.data AS em,
        em2.countryID
    FROM Growth AS em2
    WHERE
    em2.dataID = 524) as em02
    USING (countryID)
    JOIN
    (SELECT
        em3.data AS em,
        em3.countryID
    FROM Growth AS em3
    WHERE
    em3.dataID = 525) as em03
    USING (countryID)
    ...
    JOIN
    (SELECT
        emX.data AS em,
        emX.countryID
    FROM Growth AS em5
    WHERE
    emX.dataID = 527) as emXX
    USING (countryID)
    JOIN Countries
    USING (countryID)

I'd actually like to retrieve a few more variables, plus potentially join other tables. Now I'm wondering whether there's a way to run this more efficiently, or whether I should take an altogether different approach, such as using wide tables with indicators in different columns to avoid self-joins.


texts:
I'm still struggling a bit in moving from Oracle to SQL Server, especially with the login-vs-user thing.  I've restored a database from a client site so we can have a development copy. I want to set up a login with that new database as the default db, and map it to a user of that database. But everything I've tried results in a "user or role already exists" error. I'd like for this login to only have access to this one particular database.  I've read about users and logins, but I'm obviously missing something here.  Can the login have the same name as the database user?  What I've read seems to imply it can, but I get an error.

Harry


texts: Why did my transaction log grow so much?

texts:
Possible Duplicate:
  Why Does the Transaction Log Keep Growing or Run Out of Space?

About a month ago I migrated my SQL Server 2008 R2 database to a new schema. I ran scripts to migrate the data in the existing DB to the new DB. When I finished the new database was about 15 GB. A month goes by and my transaction log is 130 GB. Why did this happen and what should I do about it?


texts: How to disable foreign key constraints in Postgres dump file?

texts:
I used pg_dump -U postgres -p 65432 --table=tableName --data-only --column-inserts DBName>fileName.sql to dump one particular table's rows. Now I need to insert them to another database but I get this error

  insert or update on table "tableName" violates foreign key constraint "tableName_fk1"

How to proceed by dropping the foreign key contraint?


texts: SQL Server Configuration Manager 64-bit Nodes Missing on 64-bit WIndows 10

texts:
This problem appears to have been written about quite a lot, and answers posted here too, but I have tried all the suggestions and some of my own to no avail, so please bear with me.

I have Windows 10 V1607, 64-bit. I've used SSMS for a long time, and keep up-to-date with the latest versions, especially now that it is shipped independently, and free, from SQL Server.

When I switched from Windows 7 to Windows 10 on this machine, I installed SSMS V16, and noticed then that there were no 64-bit nodes in SSCM. I didn't think too much of it at the time, and set about creating my 32-bit aliases.

Recently, I changed an old application to 64-bit, compiled and ran it in Visual Studio (2017), and tried to connect to a database. It failed.

I thought it might be that I have not set up any 64-bit aliases, so returned to SSCM to add them. But no 64-bit nodes.

I have seen various suggestions about copying files from a working copy, so I tried that. No difference.

I spotted the latest SSMS V17.1 had just been released, so installed that. It too is missing the 64-bit nodes.

I have tried repairing, reinstalling, turning it off and on again, but still they fail to appear.

Please, does anyone have an insight into why this happens, but more to the point what to do to fix it?

I have SQL Server installed on another machine. I want my application to connect to a database on that machine, remotely. To make it easier to manage the remote database I have SSMS installed locally; in that way, I don't have to use RDP to connect to the server. When I run my application and it connects to the remote database, I want to use an alias, such as 'MyRemoteDatabase', instead of using the IP address and port number directly. In the past, I have run SSCM to create the alias, but my 64-bit applications don't see my 32-bit aliases, which is why I want the 64-bit nodes.


texts: Is there a way to search the earliest or latest postgres partition for table?

texts:
BACKGROUND

I am working on a web application that has date pickers for searching. Those searches query a partitioned table. So far, so good.
However, based on the data I'm querying, we don't have a limit on the early date picker. I could impose an artificial, arbitrary value, but I'd rather have a quick query that fetches the earliest found partition table, possibly the latest as well, without knowing the date ranges of those partitions so we can get the earliest found date.

Partitions are created using a function that I believe is called in a cron job every month that creates a month+1 partition. They are named after timestamps, but only use year and month.

PROBLEM

Is there something that allows me do so this (pseudocode):

select min(date_column) from parted_table.earliest_partition;

For example, if a monthly table partition is created every new month starting from 2018, I would expect earliest_partition to be table_2018_01

select min(date_column) from table_2018_01

How would I get table_2018_01 without knowing that 2018_01 was the earliest created partition?

Disclosure: I'm working with someone else's code so I am not fully familiar with the system.

postgres 9.4

tl;dr I guess what I'm looking for is a query that searches table names and returns the earliest one. Should be doable through some built in pg_ table?


texts: Query a table with linked objects as columns

texts:
Title of the question is not clear, I'll try to describe it.

In my model I have the following :

A Network object includes Lines, and a Line object includes Routes (this is for public transportation).

Now I have a Subscription table where I handle user subscriptions for travelling on Networks, Lines or Routes :

╔════╦═════════╦══════╦═══════╗
║ ID ║ Network ║ Line ║ Route ║
╠════╬═════════╬══════╬═══════╣
║ A  ║       1 ║ 6    ║ 12    ║
║ B  ║       2 ║ 3    ║ NULL  ║
║ C  ║       1 ║ NULL ║ NULL  ║
╚════╩═════════╩══════╩═══════╝

That means :

Subscription A is valid only on route 12
Subscription B is valid on all routes of line 3
Subscriptions C is valid on all routes of network 1.

I need to get all subscriptions allowed on route#12, i.e. I need to fetch first and third row.

What kind of request would allow me to do so please ?

In the end I'll be doing this in Java with JPQL, but I'd like to understand how to do it in plain SQL first.

Here's a SQL Fiddle in case anyone is willing to help.


texts: Create multiple output tables from one query

texts:
I'm an inexperienced Postgres user and need help if someone can suggest it.

I require output tables of column 'county' so all counties in the table have their own table. I need independent tables for each to use separately. My source table is 'my_counties' and 'county' contains the variable county names I wish to separate.

CREATE OR REPLACE FUNCTION myfunction()
RETURNS SETOF my_counties AS
$BODY$
DECLARE
    county my_counties%ROWTYPE;
BEGIN

    FOR row in SELECT county DISTINCT my_counties LOOP
        EXECUTE 'SELECT * INTO myschema.test_' || row.county || ' from myschema.source WHERE source.county = ' || row.county;
    END LOOP;

RETURN;
END;
$BODY$
LANGUAGE plpgsql VOLATILE

The error I return is

  ERROR:  syntax error at or near "DISTINCT"


texts: Why can't we write ddl statement directly into the PL/SQL block

texts:
Why can't we write ddl statements directly in PL/SQL block, for example when i write

CREATE OR REPLACE PROCEDURE test IS
BEGIN
    truncate table table_name; // error
END test;
/

But,

CREATE OR REPLACE PROCEDURE test IS
BEGIN
    execute immediate 'truncate table table_name'; // works fine
END test;
/

Why second one executed successfully ?


texts: Avoiding a full re-initialization when making changes to transactional replication

texts:
When I use the GUI to make any changes to transactional replication on SQL server, I live in fear of the "the changes you have made require a reinitialization of all articles" dialog.

I have had this happen even when doing something simple like adding or removing individual tables.

How do I avoid this?

Are there replication settings that impact when a full reinitialization is required? I found this blog post that claims that publication properties @immediate_sync and @allow_anonymous should be set to 0, I've done that but I've still sometimes had it prompt to reinitialize the whole thing.

Should I be scripting the add/remove instead of using the GUI?

Also, we frequently remove a table from replication but need to add it back in later, are there scripts out there to preserve the replication settings so this process is easier?


texts: What's the deal with transactions in a stored procedure called by an SSRS report?

texts:
Consider the following:

CREATE PROCEDURE dbo.usp_trantest AS
SELECT @@TRANCOUNT as trancount;
GO

When I call usp_trantest manually from within SSMS, the trancount is 0. If I run an SSRS report that contains a dataset that queries the same stored procedure, the trancount is logged as 1.

Doing a T-SQL trace of the SSRS method shows a trace event for the stored procedure call that is identical to the one I did from SSMS.

Is there some behavior that could change based on the SSRS context? Like, an implicit transaction being opened for the dynamic sql call or something, or SSRS creating a transaction context outside of T-SQL?

Edit:

In a now-deleted answer from someone (thanks, stranger!) it was suggested that the SSRS report's dataset might have had "Use single transaction when processing the queries" checked. This was indeed the case!

I did some further testing, and with this setting unchecked, the @@TRANCOUNT is the same whether running in SSMS or from an SSRS report.

So it appears we can draw the conclusion that this Data Source setting does cause the SSRS report to create a transaction context on the database before running the queries. Since this extra transaction does not appear in the T-SQL trace, we can probably assume that it is being opened using an API method, instead of a T-SQL statement.


texts: Incorrect results using WITH RECOMPILE option

texts: If there are multiple connections concurrently executing a query with different parameters in a filter condition and the WITH RECOMPILE option is used, is there any chance that the query may produce incorrect results?


texts: Poor performance after switching from EC2 Percona to RDS MySQL. What am I missing?

texts:
Current RDS Setup: db.r3.2xlarge running in Multi-AZ, 8 VCPU, 61GB RAM, 1500GB EBS Volume with 5000PIOPS, Mysql5.6

Here are our current MySQL variable values: https://gist.github.com/jonhatalla/abfb0b8fb73690352629

What we did before: We are coming from an EC2 instance (of similar specs) running percona mysql 5.6 with ephemoral storage.

What we expected: Slightly slower read and write times, due to the round trips to EBS.

What we experienced:

We started using the RDS setup with full production traffic and initially we noticed that database queries were taking about 10ms longer than we experienced with EC2-MySQL. They were initially constant, however we started noticing spikes in response times of average queries which were longer than a second. This spike was not related to an increase of traffic or connections.  We were forced to return traffic to our EC2 database.

We weren't experiencing any application errors, all web requests were returning, just very slowly.

Note: We were serving traffic for around 2 hours on the RDS instance.  I tried to link times between cloudwatch metrics on the bottom with new relic graphs on top.

Here are our metrics from both new relic and cloudwatch:

Thank you for any help!


texts: help understanding galera consistency (certification-based replication)

texts:
Galera uses a certification-based replication to achieve strong consistency.

While doing a transaction after doing a COMMIT the other nodes will certify it and if is ok it will be successfully committed.

But what happens in the case of losing a node that certified the commit but still fetching the data, for example when writing a big blob?

From my understanding the certification process is synchronous but the data transfer is asynchronous, meaning that the client/user may think his data is stored on all the nodes after running successfully a COMMIT but while in reality is happening in the backend is that data may be still synchronizing among the nodes and in case of a network outage and depending on the type of transaction the node could be in a conflict, correct?

If this is the case, how could the cluster be configured to self-heal from possible conflicts or wondering if there is a way to achieve strong consistency even if it would impact the return time for every COMMIT since it should only be successful after the data been full transmitted on all nodes.


texts: insert into select with union needs derived table

texts:
Why do I need to do another select when I am trying to insert distinct values into a table? Also: is there a better way to do this?

WORKS:

INSERT INTO phppos_categories (name)
SELECT category FROM
    (SELECT DISTINCT category FROM phppos_items as items
    UNION
    SELECT DISTINCT category FROM phppos_item_kits as item_kits)
as category_result;

DOESN'T WORK:

INSERT INTO phppos_categories (name)
    (SELECT DISTINCT category FROM phppos_items as items
    UNION
    SELECT DISTINCT category FROM phppos_item_kits as item_kits)


texts: Can't install a OLE DB Provider for IBM DB2 ISeries on SQL Servers higher than 2008 version

texts:
I've currently had a SQL Server 2008 server, which we now need to migrate to a newer edition.

We have a couple of linked servers which I have not set up personally which utilizes a provider called IBMDASQL in SSMS. It appears as if the previous collegue responsible for this set up has used the link here in order to set up these connections.

However, when I try to run the Setup.exe from the installation I get an immediate error telling me that I have a Standard edition which is not supported, and I can see from the documentation that only SQL Server 2008 is supported.

How can I set up a OLE DB Provider for IBMDASQL (I need to get this working with a ISerie Connection)?

EDIT

I've tried to install OleDB provider for DB2 from Microsoft, and it installed successfully. It was even included in my Providers list in Management Studio, but when I tried to set up a Connection using the same name for provider etc. as the previous Connection had, I got the error that The OLD DB provider "DB2OLEDB" for linked server abc reported an error. [....] cannot initialize data source object of OLE DB provider DB2OLEDB for linked server abc. The same server used IBMDASQL earlier. I thought it was "included" in the DB2 OLE DB provider

On my previous installation I had the following settings:

Provider: IBM DB2 UDB for iSeries IBMDASQL OLE DB Provider
Product name: ISeries
Data Source: [an IP number]
Provider string:
Location:
Catalog: foo

the only difference I have in the new Connection is that

Provider: Microsoft OLE DB Provider for DB2

is used instead. Do I miss any steps?


texts: Why doesn't more memory necessarily make Postgres more performant?

texts:
The Postgres resource consumption docs state, under the shared_buffers:

  ...it is unlikely that an allocation of more than 40% of RAM to shared_buffers will work better than a smaller amount.

Why is this? I'm always told that the more RAM a server has, the better Postgres will perform. Isn't shared_buffers the quintessential memory setting for Postgres? If I'm only allocating 3GB of 12GB (the recommended 25% starting point) in my server to shared_buffers, where can I expect Postgres to take advantage of at least 6GB more?


texts: Can I reorganise index on a sql server 2005 database without performance hit on production server

texts: I would have preferred to run online index rebuild instead but our production sql server (2005) is standard edition. Hence I think I am left with reorganising index and update statistics after that. Please correct me if I am wrong. But I am wondering if I can do this without any performance hit or not.


texts: How to apply a theme to SQL Server Management Studio 2008?

texts:
Has anyone had any success with, for example, applying a 'dark' them to the Text Editor component of of SSMS 2008?

There was an old trick for 2005 which is no longer operational and has not been upgraded.  http://winterdom.com/2007/10/colorschemesinsql2005managementstudio

My eyes will thank you very much!  Cheers.


texts: MySQL CREATE statement are really slow (Mac OS X / Macport install)

texts:
I recently noticed that recreating the database for the web application I am working on is taking a lot of time. I had just reinstalled Mysql from Macport...

I am getting really lousy performances, for example :

0.293437004089 seconds for

CREATE TABLE blockip (
id INT(11) NOT NULL AUTO_INCREMENT,
ip VARCHAR(15) NOT NULL DEFAULT '',
username VARCHAR(80) NOT NULL DEFAULT '',
time INT(10) NOT NULL DEFAULT '0', PRIMARY KEY (id) )
DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci TYPE=MyISAM;

On another machine, nearby I get 0.0016529560089111 seconds for the same query.

The trouble seems to be somewhat related to the disk : disk usage goes really high when I start creating the 70 tables in my database.

Any ideas where to start for a clue ?


texts: Using indexed views for aggregates - too good to be true?

texts:
We have a data warehouse with a fairly large record count (10-20 million rows) and often run queries that count records between certain dates, or count records with certain flags, e.g.

SELECT
    f.IsFoo,
    COUNT(*) AS WidgetCount
FROM Widgets AS w
JOIN Flags AS f
    ON f.FlagId = w.FlagId
WHERE w.Date >= @startDate
GROUP BY f.IsFoo

The performance isn't awful, but can be relatively sluggish (perhaps 10 seconds on a cold cache).

Recently I discovered that I can use GROUP BY in indexed views and so tried out something similar to the following

CREATE VIEW TestView
WITH SCHEMABINDING
AS
    SELECT
        Date,
        FlagId,
        COUNT_BIG(*) AS WidgetCount
    FROM Widgets
    GROUP BY Date, FlagId;
GO

CREATE UNIQUE CLUSTERED INDEX PK_TestView ON TestView
(
    Date,
    FlagId
);

As a result the performance of my first query is now < 100ms, and the resulting view & index is < 100k (although our row count is large, the range of dates and flag IDs means that this view only contains 1000-2000 rows).

I thought that perhaps this would criple the performance of writes to the Widget table, but no - the performance of inserts and updates into this table is pretty much unaffected as far as I could tell (plus, being a data warehouse this table is updated infrequently anyway)

To me, this seems way too good to be true - is it? What do I need to be careful with when using indexed views in this way?


texts: Fixed database paths in stored procedures/views - is there a smoother way of doing this?

texts:
Our developers love to create lots of different stored procedures/views, which extracts data from lots of different databases on different servers.

Every time we migrate to a new database server, something breaks because some obscure procedure or view didn't get fixed at the same time. Documenting them all is already underway.. I still have to ask - isn't there a simpler way of doing this?

Would it be possible for a stored procedure to extract the connection string from a table - let's say "dbo.linked-databases", and have a unique row per database being references, where one of the columns specify the connecting string? Can stored procedures then SELECT this, and expand it as a variable?

Sorry if the question doesn't make any sense, as I'm quite new with SQL.


texts: Function for generate a huge and possible random relationship between tables

texts:
I have this two tables and N:M relationship between them:

Let's said I am managed to create a lot of media and emails records but I need to create the relationship between them. I can do this programmatically but I don't want to waste time on that if I can do in someway using MySQL functions or any other way. So, any advice, code example, query example?


texts: Performance Insights MySQL RDS cannot change Counter Metrics or download full SQL

texts:
This is probably some permissions issue but I am trying to use Performance Insights on the AWS console for several MySQL RDS instances that are supposedly configured for it. I am following the documentation here:
https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_PerfInsights.UsingDashboard.html
When I click on Manage metrics I just get a blank popup window with no option to chose different metrics for the Counter metrics part of the screen.
Also, when I choose a SQL statement it does not show a part of the window below the list of SQL statements with a Download button to download the full SQL text.
It is probably something simple but I thought I would ask in case someone has seen this.
Bobby


texts: Oracle "insert /*+APPEND*/" into empty table

texts:
I have following code in script:

execute immediate 'truncate table tab_a drop storage';

insert /*+APPEND*/ into tab_a
select (...)

I am wondering if this APPEND hint can cause any performance change, since table is truncated and storage dropped.
Any info, hints or resources are welcome.


texts: Mongo replica set with no PRIMARY/SECONDARY, members are STARTUP2 and RECOVERING

texts:
I have a mongo cluster with 6 replica sets.  5 are fine, one is not.  Each replica set has three members.  Here is the rs.status() for it:

{
    "set" : "rs_5",
    "date" : ISODate("2015-12-16T02:37:39Z"),
    "myState" : 5,
    "members" : [
        {
            "_id" : 0,
            "name" : "mongo_rs_5_member_1:27018",
            "health" : 1,
            "state" : 5,
            "stateStr" : "STARTUP2",
            "uptime" : 33600,
            "optime" : Timestamp(0, 0),
            "optimeDate" : ISODate("1970-01-01T00:00:00Z"),
            "lastHeartbeat" : ISODate("2015-12-16T02:37:38Z"),
            "lastHeartbeatRecv" : ISODate("2015-12-16T02:37:37Z"),
            "pingMs" : 0,
            "lastHeartbeatMessage" : "initial sync need a member to be primary or secondary to do our initial sync"
        },
        {
            "_id" : 1,
            "name" : "mongo_rs_5_member_2:27019",
            "health" : 1,
            "state" : 3,
            "stateStr" : "RECOVERING",
            "uptime" : 33842,
            "optime" : Timestamp(1449898728, 18),
            "optimeDate" : ISODate("2015-12-12T05:38:48Z"),
            "lastHeartbeat" : ISODate("2015-12-16T02:37:37Z"),
            "lastHeartbeatRecv" : ISODate("2015-12-16T02:37:37Z"),
            "pingMs" : 3,
            "lastHeartbeatMessage" : "still syncing, not yet to minValid optime 566bb328:3"
        },
        {
            "_id" : 2,
            "name" : "mongo_rs_5_member_3:27020",
            "health" : 1,
            "state" : 5,
            "stateStr" : "STARTUP2",
            "uptime" : 33845,
            "optime" : Timestamp(1449898728, 18),
            "optimeDate" : ISODate("2015-12-12T05:38:48Z"),
            "errmsg" : "still syncing, not yet to minValid optime 566bb327:1",
            "self" : true
        }
    ],
    "ok" : 1
}

In the logs, I see stuff like:

Wed Dec 16 02:40:34.033 [rsMgr] replSet I don't see a primary and I can't elect myself

and

Tue Dec 15 21:41:27.686 [rsSync] replSet initial sync need a member to be primary or secondary to do our initial sync

Here is rs.conf():

{
    "_id" : "rs_5",
    "version" : 125967,
    "members" : [
        {
            "_id" : 0,
            "host" : "mongo_rs_5_member_1:27018",
            "priority" : 3
        },
        {
            "_id" : 1,
            "host" : "mongo_rs_5_member_2:27019",
            "priority" : 2
        },
        {
            "_id" : 2,
            "host" : "mongo_rs_5_member_3:27020"
        }
    ]
}

It has been like this for a number of days.  The cpu and the network are showing no real movement indicating that nothing is happening.  Obviously, I'd like to not lose data, what do I need to do to get this back to a healthy PRIMARY/SECONDARY/SECONDARY replica set.


texts: How can I change collation for all tables and columns in MySQL database?

texts:
I have a problem with MySQL collation. It turns out that MySQL uses latin1_swedish_ci by default. Collation setup step has been missed on MySQL database instantiation for some reason. Thus, application setup wizard has created tables in MySQL database without regard to the required collation. As a result, all tables and columns have latin1_swedish_ci collation. Changing database collation with ALTER DATABASE db_name CHARACTER SET utf8 COLLATE utf8_general_ci does not help as long as it does not affect tables and columns.

I have many tables and columns in my database, it will take a lot of time to traverse whole database and set collation manually. It will be also difficult to write long SQL altering every table and column as long as there are too many of them.

The question is where there any way to change collation for tables and columns in the whole MySQL database? If there is no dedicated mysql command for this purpose, maybe there are some examples of stored procedures available?

PS: I cannot use scripting languages as long as I do not have access to the serverside. MySQL is installed in restricted network area. All I can do is use phpmyadmin in order to run SQL queries.


texts:
I am having some of the below mentioned issues in a SQL Cluster. I just joined the company and don't know who built it. When I see the status of quorum, it seems to be  online, what could be wrong? I checked the cluster configuration report as well

[![enter code here][4]][4]

In the cluster configuration manager, I saw these warnings.

Can someone please give me any pointers?


texts: SQL Server AG secondary replica database still in Restoring mode

texts:
I created an AlwaysOn in SQL Server 2019 and I selected the Full Database and Log Backup option. Now it created a database in secondary replica but it is still in Restoring mode. My DB size is 16MB and it is very small just for test.
What is the problem?


texts: Process with Command 'Killed' and State 'Executing'. Not getting killed

texts:
Last day, when I ran a count(*) query on a table. It didn't provide any results. It ran forever even. So I tried killing those queries (I have tried it multiple times). As I'm using AWS RDS, I killed the session using

CALL mysql.rds_kill(pid);

When I checked the processlist, it was showing Killed in the Command column and Executing in the State column. Why is it now killing it? The main issue is, the CPU is hitting 100% from the time when I ran this query. Even after I ran this kill command, it stays at 100%.
As I'm a newbie in DB, I would like to know why it was not getting killed fully. I have read about the time taking for rollback even after we kill a process. But here, I just tried a count(*) query.
Where can I find details regarding this? Right now, I have rebooted to bring down the CPU. But if this happens in production, things will get messed up, right?
Any help for getting details regarding this issue would be really helpful. Thanks.


texts:
I have a database of students and grades across varying report cycles. Each Report Cycle has a unique ID and each subject also has a unique ID. Students can have more than one teacher per subject so I need to average the grades given and produce just the averaged grade for their attainment scores. An example of the raw data is below:

+--------------+------+-----+----+-------------+---+
| 223599152142 |   12 |  92 |  3 | Mathematics | 0 |
| 223599152142 |   12 |  92 |  3 | Mathematics | 3 |
| 223599152142 |   12 |  92 |  7 | History     | 3 |
| 223599152142 |   12 |  92 | 12 | Economics   | 3 |
| 223599152142 |   12 |  92 | 12 | Economics   | 4 |
| 223599152142 |   12 |  92 | 26 | Latin       | 2 |
| 223599152142 |   12 | 109 |  3 | Mathematics | 3 |
| 223599152142 |   12 | 109 |  3 | Mathematics | 4 |
| 223599152142 |   12 | 109 |  7 | History     | 3 |
| 223599152142 |   12 | 109 | 26 | Latin       | 2 |
| 223599152142 |   12 | 109 | 26 | Latin       | 3 |
| 223599152142 |   12 | 110 |  3 | Mathematics | 4 |
| 223599152142 |   12 | 110 |  7 | History     | 2 |
| 223599152142 |   12 | 110 |  7 | History     | 3 |
| 223599152142 |   12 | 110 | 26 | Latin       | 2 |
| 223599152142 |   12 | 110 | 26 | Latin       | 3 |
+--------------+------+-----+----+-------------+---+

I wish to average the GradeTransposeValue based on the SubjectID for each ReportCycleID, but I am getting odd results. I have set up the below query to just focus on one student and one report cycle to show the problem.

SELECT intSubjectID, txtCurrentSubjectReportName,
       CAST(AVG(intGradeTransposeValue) AS decimal (5,2)) AS avg_attainment
FROM VwReportsManagementAcademicReports
WHERE intReportCycleAcademicYear = 2017
     AND intNCYear >6 AND intGradeID = 1
     AND txtSchoolID = 223599152142
     AND intReportCycleID= 110
GROUP BY intSubjectID, txtCurrentSubjectReportName

This is producing this result

+--------------+-----------------------------+----------------+
| intSubjectID | txtCurrentSubjectReportName | avg_attainment |
+--------------+-----------------------------+----------------+
|            7 | History                     | 2.00           |
|           26 | Latin                       | 2.00           |
|            3 | Mathematics                 | 4.00           |
+--------------+-----------------------------+----------------+

However, if we look at Latin as an example, the original values were 3 and 2, so 3+2 = 5 / 2 should = 2.5 and not 2.

Any help on this would be much appreciated.


texts:
I would like to know if it is possible given a certain backup made with RMAN in Oracle 12c is encrypted without having to restore said backup, is there any or some queries that verify if an RMAN backup is encrypted or not?

To launch the backup I use the option SET ENCRYPTION ON IDENTIFIED BY qazwsx ONLY;

Thank you very much.


texts: What exactly is a database mission-statement?  My question was prompted after submitting an academic-assignment requiring a "mission-statement" for ERD-modeling.  I've yet to encounter this requirement out in the wild as a software engineer, and I presume a mission-statement is akin to a thesis-statement, but I remain unsure after my Google-search.  Any DBA professional example or guidance explaining the ideal "database mission-statement" would be appreciated- thanks!


texts: How to convert data from XML format into Excel workbook or database table

texts:
I have some data that is given in a consistent XML format (which I can't change). I would like to import it into a database so that I can work with it, and then search, identify and export certain records into an Excel workbook, as I don't think it can be imported to Excel directly.

I have no experience working with databases so I'm not sure if this is possible or for the best approach. My question is: Can this be done with a database tool/function? It's not important what the database system is, but I would prefer MS-Access or SQL-Server Express.


texts: Simple Syntax Error - CASE WHEN DISTINCT(Column)

texts:
Trying to do a case on a distinct value and it isn't going well:

SELECT
CASE WHEN DISTINCT(Response_Master_Incident.Battalion) = '' THEN 'Unknown'
    ELSE DISTINCT(Response_Master_Incident.Battalion) END AS 'Zone'
...

and I get the error "Incorrect syntax near the keyword 'DISTINCT'."


texts: Auditing sysadmin members by a specific group

texts: A Sql Server 2012 Database Audit performs an audit on sysadmins when you specify the principal DBO. However, this causes ALL the sysadmins to be audited. What configuration or solution can be used to audit ONLY 1 specific group that has sysadmin membership without auditing all the activity by ALL sysadmins? We are trying to cut down on action events that are not necessary (i.e. sql engine service account or other service accounts that are sysadmin and do not need audited). This is a user database.


texts:
I m using postgres 9.3 and i m trying to insert data  to the view objectifsBoutique and and PostgreSQL returns following error(« objectifsboutique » relation is not found).
I have to mention that the view was successfully created

CREATE OR REPLACE VIEW "objectifsBoutique" AS
SELECT vendeur.boutique_id, objectifs.produit_id, cast(NULL as bigint) AS objectif
FROM boutique, vendeur, objectifs
WHERE boutique.id = vendeur.boutique_id AND vendeur.id = objectifs.vendeur_id
GROUP BY vendeur.boutique_id, objectifs.produit_id;
ALTER TABLE "objectifsBoutique"
OWNER TO postgres;

create table objectifsBoutiques(boutique_id bigint, produit_id bigint, objectif bigint);

CREATE RULE objectifsBoutiques_insert AS ON INSERT TO objectifsBoutique
DO INSTEAD INSERT INTO objectifsBoutiques VALUES (NEW.boutique_id, NEW.produit_id, NEW.objectif);


texts: Implement check constraint for varchar (7) to enforce YYYY-MM

texts:
I am considering the following with regards to implementing a check constraint on a column I am calling year_month. My objective is to have a check constraint to enforce YYYY-MM.

My intention is to create the column with data type: varchar(7)
example value that would be saved from my front end: 2017-08

Would I then add a regular expression to enforce only numbers, dashes and max 7 in my check constraint setup? Or could I  add some sort of regular expression to check for this pattern more exactly?


texts: I am designing a database and it has so many relationships among my tables and i need a book that teaches database design very well.I am looking for a book where table relationships simple and complex has been covered extensively and maybe case studies in the book.


texts: What are the performance implications of polymodels versus normal Bigtable models in Google App Engine?

texts:
What produces the best performance in regular Google App Engine use, a polymodel or a normal "Bigtable" model?

The polymodel, effectively, creates a column in the parent table called "class" which provides the inheritance tracking. Whereas a normal Bigtable, inherited from a parent class, creates a new and separate data structure, without the ability to query the parent and find all children of all subtyped classes.


texts: Better way to concatenate a sequence of string replacements in Amazon RDS?

texts:
I have a concatenation of string replacements in this way:

CREATE OR REPLACE FUNCTION norm(t text) RETURNS text AS $$
        declare t1 text;
        declare t2 text;
        ...
        declare t10 text;
        BEGIN
            select replace (lower(t), ',', ' ') into t1;
            select regexp_replace (t1, '[^0-9a-z\s-]', '', 'g') into t2;
            ...
            select regexp_replace (t10, 'n?oise?.*', '', 'g') into t10;
            RETURN trim(t10);
        END;
$$ LANGUAGE plpgsql;

At the moment it has 10 different operations and the number keeps on grow. Also it has the inconvenient that changing position to transformations forces to rename variables.

Is there a better way to manage this situation?


texts: MySQL Join When Record in Joined Table Does Not Exist

texts:
How I can join two tables when record in joined table doesn't exist. I'm tried something like this:

SELECT
    Confessions.ID, Confessions.WriteTitle, Confessions.WriteArea,
    Confessions.POST_DATE, Votes.Type
FROM Confessions LEFT OUTER JOIN Votes ON Confessions.ID = Votes.PostID
WHERE ForUser = :ForUser AND Status = :Status AND Votes.IP_Check = :IP_Check

but when record from votes with PostID like ID in Confessions doesn't exist MySQL gives back empty result for this record. isthere any other way?


texts: Need to use one and one only of multiple parameters in a stored procedure

texts:
The issue is that I have 4 parameters for the user to select, and he/she can only choose one of the four.  In my current WHERE clause I have this logic...

WHERE (PARAMETER1 = DBFIELD1
    OR PARAMETER2 = DBFIELD2
    OR PARAMETER3 = DBFIELD3
    OR PARAMETER4 = DBFIELD4)

But this brings back all the rows in the db.  I need to somehow NULL out the parameters that are not being used.  Is that possible within the WHERE clause?


texts: How to use WITH inside IF statement in PostgreSQL

texts:
I am writing a function that accepts a text flag based on which I  need to run some code. So I am using an IF clause. However the code that has to execute inside IF and ELSE is a recursive CTE which has different joins for the different text flags. Following is sample that I am trying to write but getting an error. I tried writing the code that starts with WITH and applying the IF with the final SELECT statement but it also gave the same error which is "syntax error at or near "IF"".
How can I effectively use IF in this function?
CREATE OR REPLACE FUNCTION public.get_something(myTextFlag VARCHAR(20)) RETURNS TABLE
(
rNum BIGINT,
Colmn1 Text
)
AS $body$

IF (myTextFlag = 'FirstPart') THEN
   WITH recursive cte_1 AS
(
code with table 1 and 2 and cte_1
)
Select some_columns from cte_1 and table 10, 11 and 12;

ELSE

   WITH recursive cte_2 AS
(
code with table 1,2,3 and 4 and cte_2
)
Select * from cte_2 and table 10, 11, 13, 14;

END IF;

$body$
LANGUAGE SQL
SECURITY DEFINER
 STABLE;


texts: Error 3203 restoring SQL Server backup: The data is invalid

texts:
I am trying to restore a .bak file (backup taken on SQL Server 2008 R2, compatibility level 100). I am restoring the backup on SQL Server 2012 edition. I get the below error. Unfortunately the original DB from where the DB was backed up is no longer available. I am new on this project. I was given the bak file on a thumb drive.

Any suggestions on how I can restore the DB to 2012 ?

  Restore of database 'TstDB' failed.
  System.Data.SqlClient.SqlError: Read on ***13(The data is invalid.)

This is all is the error message. When I run RESTORE VERIFYONLY, I get the below error:

  Msg 3203, Level 16, State 1, Line 2
  Read on "C:****" failed: 13(The data is invalid.)
  Msg 3013, Level 16, State 1, Line 2
  VERIFY DATABASE is terminating abnormally.

RESTORE HEADERONLY was successful. When I run restore database with stats=1 I get the below:

  10 percent processed.
  20 percent processed.
  [...]
  70 percent processed.
  80 percent processed.
  Msg 3203, Level 16, State 1, Line 2
  Read on "C:\ccc.bak" failed: 13(The data is invalid.)
  Msg 3013, Level 16, State 1, Line 2
  RESTORE DATABASE is terminating abnormally.


texts: Query if server is participating in an availability group

texts: Is there a way to query whether a server is participating in an availability group?


texts:
I have a table that looks like this in Google Big Query:

    custID  InteractionDate Purchased?  Sales
    1       20150312           F         0
    1       20150312           T         200
    1       20150314           T         150
    2       20150221           T         400
    2       20150221           F         0
    2       20150221           T         120

..that I need to transform to the following structure:

     custID InteractionDate Success Sales
     1     20150312          0.5    200
     1     20150314           1     150
     2     20150221         0.66    520

..where in this second table the success column is defined as

    count(purchase=Yes)/(count(purchase=Yes) + count(purchase=No)),

and sales is just the sum.

I'm quite new to BQ and no-SQL languages. I simply don't know how to do this, I've searched and seen examples that suggest using partition over but I simply haven't seen any example that does just this. Thanks a lot in advance.


texts: Is it possible to trace queries that never complete with SQL Server 2008 R2?

texts:
Running SQL Server 2008 R2.  We have some queries that typically take less than one second to complete, but, occasionally, they are taking longer than 30 seconds which causes a client timeout.

Unfortunately, these queries are not showing up in our slow query trace because there is no sp_statement_completed or sql_statement_completed event -- it seems they are not completing on the server side.

Is there a way, preferably using extended events, to trace queries that start, but never complete?  I don't want to trace everything and generate a huge amount of logs because this is something I want to leave running for a while since the problem is so intermittent.

Update

I just noticed Event Pairing targets which I think could work.  If I read correctly, it would capture any start event that doesn't have a matching end event.  Before I mark that as an answer, does anyone have experience tracking this?  Is there a lot of overhead?


texts: Adding a total sum below a column of prices, and not in its own column

texts:
I am running this select query on my database:

Select Item.ItemId, Item.ItemDescription, Bid.BidPrice, bid.BidDate,
Sum(bid.bidPrice) over () as TotalBids
from Bid
inner join Item on bid.ItemId=Item.ItemId
where BidDate between '2016-08-24' and '2017-11-15'

I am getting the below result:

ItemId  ItemDescription BidPrice    BidDate     TotalBids
1       Frame           35         2016-08-24   3624
4       Wooden chair    40         2016-10-25   3624
2       Car             3000       2017-10-26   3624
3       Stand Fan       29         2017-10-30   3624
5       Black Sofa      400        2017-11-11   3624
6       Cabinet         120        2017-11-15   3624

My question is: Is it possible that instead of the Total Bids column with a total at each row I just get one total at the bottom of the BidPrice Column?


texts: Best design of mongo replica set for DB read/write speed

texts:
I'm building a Node app that uses MongoDB. Whilst it would probably be fine to have node and mongo sitting on the same VPS, I want to ensure that the MongoDB is backed up and maintain high availability in the case the VPS goes offline.

Looking in to the options available to me, one stood out: creating a replica set.

I have a couple of questions about how best to design the replica set to ensure the maximum speed of my app (as it does a lot of DB read/write):

Surely running mongo on a remote server will slow things down considerably? Instead of performing read/write requests on the same disk, each DB operation has to be sent across datacenters to the remote server. For example, say the ping time between the two servers was 100ms, then making 10 requests would slow down the app by almost 1 second!
With that in mind, would the following setup work?:

Server 1: Node + MongoDB (primary member of replica set)
Server 2: MongoDB secondary member - located in a separate datacenter ideally close to Server 1
Server 3: MongoDB secondary member - located in a separate datacenter ideally close to Server 1

That way, whilst everything is working normally, I benefit from the speed of executing DB commands on the same server as the node app; and when things go wrong, I still have redundancy.


texts: How Can the Same Query in Two Nearly Identical Instances Generate Two Different Execution Plans?

texts:
Server A and Server B have identical hardware and instance configurations (A is Production, B is QA). B's DBs were restored from A's backups from one week ago. I was provided this query by the development team.

SELECT
        c.Start
        ,c.[End]
        ,c.Word
        ,doc.UniqueDocumentNumber
        ,doc.EID
        ,c.CUI
        ,c.Concept
        ,a.OID
        ,doc.DocumentTypeName
        ,doc.ActivityDtTm
        ,CAST(doc.DocumentTypeId AS INT) AS MedCode
        ,CASE WHEN c.[Count] = 0 THEN  CAST(0.00 AS REAL)
           ELSE CAST(LOG(c.TotalCount / c.[Count]) AS REAL) END AS 'idf'
        ,c.[Count]
        ,c.TotalCount
FROM ECHO..AEID201 a
INNER JOIN ALPHA..XADocuments doc (NOLOCK) ON a.EID = doc.EID
CROSS APPLY (SELECT t.start,t.[end],t.word,t.cui,t.eid,
   t.UniqueDocumentNumber,cu.[Count],cc.TotalCount,core.Concept
FROM HOTEL.dbo.Htf_Index AS t
INNER JOIN HOTEL..Doc_CUI_Counter AS cu ON cu.CUI=t.CUI AND cu.DocumentTypeID=t.DocumentTypeID
INNER JOIN HOTEL..Doc_Counter AS cc ON cc.DocumentTypeID=t.DocumentTypeID
INNER JOIN HOTEL..CUI_CORE AS core ON core.CUI=cu.CUI
WHERE t.eid = doc.eid AND doc.UniqueDocumentNumber=t.UniqueDocumentNumber
) AS c
WHERE a.OID='00005159-9567-4187-a278-5b8e1e2ed20c'
AND ISNUMERIC(doc.DocumentTypeId) = 1

On Server B, review of the execution plan indicates that the criteria for table [a] at bottom is part of an index seek operation at the start of the execution plan, which makes the subquery [c] resultset execute rapidly.

On Server A, review of the same execution plan indicates that subquery [c] is executing first with full index scans due to outer criteria not being applied.

Indexes utilized in both execution plans are identical. Table rows counts are slightly larger on Server A since the restore due to normal operations but index fragmentation is nearly identical. A participating index on table [t] in the subquery on Server B has double the number of pages as on Server A but identical row count. Statistics are updated nightly at the same time on both servers.

I've attempted index rebuilds on table [t] and manually updating statistics to attempt to get both execution plans to match. What other factors may be causing this change in the order of execution?

I suggested to the developer replacing the subquery with a UDF that takes the EID and UniqueDocumentNumber fields from [doc] as arguments. What other options can I explore with the developer to increase the probability of the execution plans on QA being utilized on Prod?


texts: PostgreSQL, integer arrays, index for equality

texts:
I have a huge list of integer arrays (300,000,000 records) stored in Postgres 9.2 DB. I want to efficiently search these records for an exact match (equality only). I have heard of the intarray module and the corresponding gist-gin indexes. I would like to ask the following questions:

Does PostgreSQL uses a hash function for checking equality of integer arrays or does it perform a brute-force algorithm comparing one-by-one the elements of the array?
If PostgreSQL uses a hash function, is there some PostgreSQL function code to actually get the hash function result for a particular array?
Which index will be best for such a task? B-tree, or the gist - gin indexes provided by the intarray module? The dataset will be static, i.e, once all records are inserted there will no more insertions. So, building index / updating index time is not important to me.


texts: ERROR Error reading packet from server: Lost connection to MySQL server during query ( server_errno=2013)

texts:
We are facing the issue in replication that we are not getting any error in Show slave status\G output rather facing the following issue in Error Log File

[ERROR] Error reading packet from server: Lost connection to MySQL server during query ( server_errno=2013)

Data for some tables is not being populated from Master Server and Slave does not update it self from the master.

We are using windows 2008 R2 and on Master Version is 5.6.19 and on Slave it is 5.6.26

Things looked so far are as below:
1. Change the master heartbeat value but it didn't work
2.increased the slave_net_timeout, Increased max_allowed_packet on master   and slave
3.Rebuilding Slave
4.Slave user permissions are also correct
5.Ping and telnet from both server also works and  windows firewall is off

Any help is much appreciated


texts:
I got a deadlock when having more than one sessions doing the following:

start transaction
insert data to table A
delete data from table A
commit

at the same time

They may delete the same records or different

I could replicate it with a simplified code like below, the table is empty at the start

Session 1:

BEGIN TRANSACTION
DECLARE @component_id  numeric = 1
DECLARE @relative_component_id  numeric = 0

INSERT INTO component_lock(area_id, component_id, generic_id, operator_id, session_id, lock_date, lock_type)
VALUES(1, @component_id, 1, 1, 1, '1/1/2018', 1)

WAITFOR DELAY '00:00:05'

DELETE FROM component_lock
WHERE area_id = 1
AND (component_id = @component_id OR component_id = @relative_component_id)
AND operator_id = 1
AND generic_id = 1
AND session_id = 1

COMMIT

Session 2

BEGIN TRANSACTION
DECLARE @component_id  numeric = 2
DECLARE @relative_component_id  numeric = 0

INSERT INTO component_lock(area_id, component_id, generic_id, operator_id, session_id, lock_date, lock_type)
VALUES(1, @component_id, 1, 1, 1, '1/1/2018', 1)

WAITFOR DELAY '00:00:05'

DELETE FROM component_lock
WHERE area_id = 1
AND (component_id = @component_id OR component_id = @relative_component_id)
AND operator_id = 1
AND generic_id = 1
AND session_id = 1

COMMIT

The primary key is (area_id, component_id, generic_id)

The foreign key is (operator_id)

There are 2 indexes on component_id and operator_id (nonclustered)

Deadlock graph

Actual execution plan of the delete query from session that was not chosen as the deadlock victim
https://www.brentozar.com/pastetheplan/?id=ryyPArbi7

The deadlock does not happen if I remove the OR operator and change it to 2 delete queries

DELETE FROM component_lock
WHERE area_id = 1
AND (component_id = @component_id)
AND operator_id = 1
AND generic_id = 1
AND session_id = 1

DELETE FROM component_lock
WHERE area_id = 1
AND (component_id = @relative_component_id)
AND operator_id = 1
AND generic_id = 1
AND session_id = 1

Actual execution plan
https://www.brentozar.com/pastetheplan/?id=Sy9ACrbsm

Can anyone please explain to me why the difference?


texts: Simple trigger to update two columns in one table

texts:
I want to create a trigger MySQL that updates two fields at the same time.

Whenever a record is added or inserted, I want the contents to be copied to one field or the other, depending on which one is empty/edited.

due_date is a varchar and end_date is a datetime.

e.g.

IF new record with due_date filled in but end_date empty → copy due_date to end_date after insert.
IF new record with end_date filled in but due_date empty → copy end_date to due_date after insert.
IF update to due_date → copy that to end_date after update
IF update to end_date → copy that to due_date after update

Please note, the due_date format is already the correct and same format as datetime.


texts: C++ stoi equivalent in SQL Server's T-SQL

texts:
I'm looking for something I can use in Microsot SQL Server that would act the same way the stoi function in C++ acts.  Specifically, I'm looking to be able to grab numerics at the beginning of the string, and ignore non-numerics afterwards.  I need this to sort the following strings and need them to show up in the following order:

  99ABC
  123ABCD
  ABC
  DEFGH

So I was thinking if I had something like STOI, I can do:

ORDER BY CASE WHEN (ISNUMERIC(LEFT(TEXT, 1))) THEN STOI(TEXT) ELSE 999999 END, TEXT

I can precompute the results of STOI (or the results of the above CASE statement) and store into an extra INT column nightly.  So I don't really care about real-time performance.  I guess I'm just looking for a way without having to check all the chars of the string:

DECLARE @numEnd INT = 0
WHILE (ISNUMERIC(LEFT(TEXT, @numEnd)) BEGIN
     SET @numEnd = @numEnd + 1
END
DECLARE @stoi INT = CAST(LEFT(TEXT, @numEnd) AS INT)


texts: Is it possible to tell if a database has AUTOMATIC_TUNING switched on?

texts: In SQL Server 2017 is it possible to tell if automatic tuning is switched on? This relates to tuning of the plan. So the database automatically monitors itself and if the plan gets worse it goes back to an old plan


texts: Understanding where pg_toast is coming from

texts:
I've got a fair amount of storage in pg_toast

             relation             |  size
----------------------------------+---------
 pg_toast.pg_toast_43934449       | 87 GB
 pg_toast.pg_toast_43934438       | 64 GB
 pg_toast.pg_toast_50877          | 35 GB
 pg_toast.pg_toast_16715          | 15 GB
 pg_toast.pg_toast_16813          | 13 GB
 pg_toast.pg_toast_5706469        | 1335 MB
 pg_toast.pg_toast_43934449_index | 1004 MB
 pg_toast.pg_toast_43934438_index | 942 MB
 pg_toast.pg_toast_16715_index    | 709 MB
 pg_toast.pg_toast_16813_index    | 548 MB
 pg_toast.pg_toast_50877_index    | 530 MB
 pg_toast.pg_toast_3518414        | 463 MB
 pg_toast.pg_toast_16994          | 339 MB
 pg_toast.pg_toast_46608          | 310 MB
 pg_toast.pg_toast_16994_index    | 92 MB
 pg_toast.pg_toast_22345124       | 68 MB
 pg_toast.pg_toast_46608_index    | 51 MB
 pg_toast.pg_toast_437018         | 43 MB
 pg_toast.pg_toast_5706469_index  | 15 MB
 pg_toast.pg_toast_3518414_index  | 13 MB
(20 rows)

Which is significant when the total size of the DB is currently about 420GB. This is totally expected as some of my tables are storing JSON as either text type (for some of my older tables) or jsonb for some of the newer ones.

A lot of these columns can be cleaned up at the application-level by just deleting some older data. The problem is that it's hard to know what's actually contributing to the pg_toast tables?

How can I reverse-trace a pg_toast to the actual row/column reference of another table?


texts: MySQL binary logging with just one log file?

texts:
I have a MySQL database running with binary logging turned on. Each time the database is restarted a new binary log file is created in the format :

mysql-bin.00001
mysql-bin.00002
...
mysql-bin.0000n

My question is, is there a way to have the binary log file stay the same after each database restart.

So for example it's always mysql-bin.00001

Any help would be greatly appreciated.


texts: Join two Json arrays to one with key and value

texts:
I have two jsonb columns(keys,values).
Eg: keys colum value = ["key1","key2","key3","key4"]
values column = ["val1","val2","val3","val4"]
I want to write a select query to get the output as below based on the array index.
{"key1":"val1","key2":"val2","key3":"val3","key4":"val4"}


texts: SQL Server 2008 Authentication mode changed on its own

texts: This morning we experienced an issue where our production server seemed to decide to change from Mixed authentication mode to Windows only authentication of its own volition. I was curious to know if any of you other DBAs out there had ever experienced this or might be able to point me in a good direction to try and make sure this doesn't happen to us again. The only other thing that seemed to be going on database wise, beyond typical use, was that the transaction log had just completed a backup.


texts:
Today my database character set and collation is set to latin1. All of the tables in the database are however already set to DEFAULT CHARSET=utf8 and all data is utf8.

Is if it is safe to change character set and collation of the database to utf8?

Is it safe to also set the default settings in the my.cnf file with:

[client]
default-character-set=utf8

[mysqld]
default-character-set = utf8

A typical table in the database looks like this:

CREATE TABLE `tblpayment` (
    `paymentid` int(11) NOT NULL AUTO_INCREMENT,
    `userid` mediumint(9) DEFAULT NULL,
    `payed` enum('False','True') CHARACTER SET latin1 DEFAULT 'False',
    PRIMARY KEY (`paymentid`)
) ENGINE=InnoDB AUTO_INCREMENT=72803 DEFAULT CHARSET=utf8;

As you can see the enum "payed" is still using latin1 for some reason, however the rest of the table is utf8. Is it safe to change the CHARACTER SET of the enum to utf8 instead?


texts: How to swap postgresql rows physical locations?

texts:
I'm looking for a way to reorganize my database tables rows incrementally based on their clustered index.

I would like to avoid using the CLUSTER command since it requires an ACCESS EXCLUSIVE lock on tables being clustered, making large tables unavailable for a considerable amount of time.

The pg_repack extension seems like a better approach, but it's also an "all or nothing" operation, which could take up to several hours to complete for large tables, risking to affect replication performance.

That being said, I came up with the idea of sorting table's rows in place, swapping their physical location in incremental steps, to gradually improve table's rows physical organization. Is this possible/safe with plain SQL? (ex: issuing updates based on the ctid column)

Thanks in advance.


texts: Streaming Replication on Two Postgres Systems?

texts:
There's two servers, a Primary Server and a Backup Server. Both have Postgres installed. The Primary Server has one database, and the Backup Server has two - one for a warm backup/standby of the Primary Server's database, and another for Training and whatnot.

I need a way to maintain a warm standby on a single database, the Backup Database, without using some sort of log-shipping method that will overwrite all the data on the Training Database.

I'm thinking about trying to use Streaming Replication. What I need to know is if I will be able to keep up a warm backup of the Primary Database on one database, without overwriting the Training Database on the machine.


texts: Troubleshooting extremely long running query (SQL Server 2012)

texts:
We have a purchased reporting application that executes a query that just recently is taking an extremely long time (>2 hours).  A week ago, this same query completed in less than a minute.  I've restored the DB from last week to confirm the end user's story, and sure enough, even with similar row counts with last weeks and yesterday's backup, the difference in query run time is there.

Table1 contains 1.4 million rows.  Table2 contains 16k rows.  In this case, none of the records from Table2 exist in Table1, so when the query is actually allowed to complete, no records are returned.

The table schemas are identical, with the exception that Table1 has a non-unique, non-clustered index on Field10.  There are no other indexes that exist on either table.  All fields in the join are (NVARCHAR(40), null) types.

The query looks like this:

DELETE Table1
FROM Table1 t1
INNER JOIN Table2Temp t2
    ON      t1.Field1 = t2.Field1 AND t1.Field2 = t2.Field2
        AND t1.Field3 = t2.Field3 AND t1.Field4 = t2.Field4
        AND t1.Field5 = t2.Field5 AND t1.Field6 = t2.Field6
        AND t1.Field7 = t2.Field7 AND t1.Field8 = t2.Field8
        AND t1.Field9 = t2.Field9 AND t1.Field10 = t2.Field10
        AND t1.Field11 = t2.Field11

If I simply do a "SELECT * INTO Table3 FROM Table1" and "SELECT * INTO Table4 FROM Table2" and repeat the query about, substituting Table3 for Table1, and Table4 for Table2, the same query (against the new tables) takes 4 seconds.

I'm not an expert at Execution plans, but it looks okay to me. No suggested indexes, index seek on Table1 (cost of 89%), and a table scan on Table2 (but cost is only 9%).  Here's a link to the actual execution plan that has been anonymised.  http://pastebin.com/jcyATezX

There is no blocking that is taking place.  When using sp_WhoIsActive, I can see that the Reads are sky-rocketing into the billions.

Again, this is a boxed application, so I can't really change the query.  Any ideas on what I'm missing here?  It's driving me nuts that I can't figure this out.


texts: Attempt to use mysqldump results in usage suggestions only

texts:
I attempted the following:

mysqldump -u root -p sa -n -d -t --routines --skip-triggers --all-databases > C:\backups\MySQLStoredProc.sql

but only received back;

Usage: mysqldump [OPTIONS] database [tables]
   OR  mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
   OR  mysqldump [OPTIONS] --all-databases [OPTIONS]
   For more options, use mysqldump --help

I see no problem with my command. Does anyone see a problem with it? The results of mysqldump --version is

mysqldump  Ver 10.13 Distrib 5.7.9, for Win32 (AMD64)


texts: Performance degradation after rebuilding indexes

texts:
I am optimizing some server application task that uses database (querying, complex calculations, data insertions...). Execution of this task spends about 16 minutes (I have tested it 3 more times) and it predictable time to do it.
Then I executed script:

ALTER INDEX ALL ON dbo.'+ @TableName +' REBUILD

for every table in a database.
And what I see now. The time of execution my task is increased to 24 minutes. What's going on if these is not any external influences on this task?  I was waiting for increasing of performance (due rebuilding fragmented indexes) but got degradation.


texts: Number of Worker threads available in PostgreSQL

texts:
In SQL Server we can have many worker threads per CPU core. Does anyone know if the same is true in PostgreSQL and can point me at a reference? (assume version 11).

The background is that we have some OLTP workloads currently in SQL Server that are being migrated to PostgreSQL. I'm wondering if there is a risk of running out of threads if parallelism is enabled and connections being refused (which can happen on SQL Server when we run out of threads), and whether I should recommend that parallelism is disabled by default.


texts: How to use multilinguals via UTF8 in oracle 11g

texts: My oracle(11g) DB uses UTF8 encoding, when I insert/update a row in a different language (Greek/Latin/Japanese) the row is getting updated, but when I check for the stored record in DB, it shows just "??????" and not the foreign language that I am entering in the record. Can you please guide what am I missing here?


texts: SQL query using an adjacent field value as a criterium

texts:
3 tables in a database. Table1 is tabCategory, Table2 is tabSub, Table3 is Parts.
Table3, Parts, is populated with 3 columns, multiple entries, thousands of them.   Column1 is the part number,   column2 is the Category   and column3 is the Type from tabSub.
The user must fill column1 (PartNumber) according to the part number it wants to add in the DB.
He must also enter the Category (PartType) for his part, wich is limited to a list form Table1, tabCategory.   He must also enter the SubCategory limited by a list from Table2, tabSub. Table2 is built with some 40 subcategories to which correspond only 10 different categories.
For example: part: DC23, Category:Capacitor, SubCategory:Mylar...if the part was a Transistor the Catgory would be a Semiconductor and the SubCategory could not be Mylar.
The Challenge: How can I make an SQL query that would deliver a list of all the available SubCategory using as a criteria the adjacent field Category chosen by the user?

Something like this (which does not work):
SELECT tabSub.Category, Amplifier.Category
FROM tabSub INNER JOIN Amplifier ON tabSub.Type = Amplifier.PartType
WHERE (((tabSub.Category)=[Amplifier].[Category]));
The goal being to limit the available user choices based on his entry in the Category field for that particular record. This Query result would be used to limit his list of choices.
Thanks for your suggestions. Cheers


texts:
I am having mysql tables like below

tbl_source_1

SRC_ID_1 | SOURCE_NAME_1
1        | ABC
2        | DEF
3        | GHI

tbl_source_2

SRC_ID_2 | SOURCE_NAME_2
1        | NAME_1
2        | NAME_2
3        | NAME_3

tbl_user

user_id | user_nm
1       | JACK
2       | MACK

tbl_data

dta_id | SRC_ID_1 | SRC_ID_2 | user_id | data_name
1      | 1        | 1        | 1       | XYZ
2      | 1        | 1        | 1       | SAD
3      | 2        | 2        | 2       | BAD

now i want COUNT of data (group by source_2) and want result like below

SOURCE_NAME_1 | user_nm | NAME_1         | NAME_2 | NAME_3
ABC           | JACK    | 2 (it's count) |  0     | 0
DEF           | MACK    | 0              |  1     | 0


texts: How do I increase all of my EmployeeID values by one if it is the primary key?


texts: Composite constraint of numrange and boolean

texts:
I have a current constraint like this:

ALTER TABLE myschema.my_table ADD CONSTRAINT my_constraint EXCLUDE USING gist((<uuidcol>::text) WITH =, numrange(<start>, <end>) WITH &&);

This works fine, but I need to add one more thing to the constraint, it must only be enforced for rows where a boolean column is true (let's call it active). I tried this:

ALTER TABLE myschema.my_table ADD CONSTRAINT my_constraint EXCLUDE USING gist((<uuidcol>::text) WITH =, numrange(<start>, <end>) WITH && active = TRUE);

This throws syntax error:

ERROR: syntax error at or near "active"
SQL state: 42601

Is it possible to have the constraint include the active column as well?


texts:
I've been trying to use the generate_series() function of Postgres to obtain a a table like this:

I didn't arrived to a solution yet, so any ideas would be appreciated.


texts:
I can not use InnoDB engine because of some dependency troubles in LuaSQL and PtokaX.

My current query is something like:

DELETE e.*, m.*
FROM entries e
INNER JOIN magnets m
    ON m.eid = e.id
WHERE e.id = %d

where, %d is an integer passed by the user. This query works if there is atleast one entry in the magnets table related to the entries table.

But, it fails when the entries has no dependent row in magnets table. Can I modify this query to perform deletion from both tables, independent of child-rows in magnets? Or do I need to first check if a child exists and delete accordingly?


texts: In Postgres, is it possible to get all rows from a table where a column can be cast to a different type?

texts:
I have a table in a Postgres DB that has a TEXT column, named data, that I use to store both IP addresses and domain names. I'm wondering if it's possible to select all the rows in the table whose data column is a valid IP address.

Can this be done, say with a WHERE clause based on a type cast?


texts: Delete duplicates and reroute referencing rows to new master

texts:
Given three tables:

A users table with id's:

╔════╗
║ id ║
╠════╣
║ 1  ║
╟────╢
║ 2  ║
╟────╢
║ 3  ║
╚════╝

A purposes table with user_id's and name's:

╔════╤═════════╤══════════╗
║ id │ user_id │ name     ║
╠════╪═════════╪══════════╣
║ 1  │ 3       │ Business ║
╟────┼─────────┼──────────╢
║ 2  │ 2       │ Personal ║
╟────┼─────────┼──────────╢
║ 3  │ 2       │ Personal ║
╚════╧═════════╧══════════╝

And a journeys table with user_id's and purpose_id's.

╔════╤═════════╤════════════╗
║ id │ user_id │ purpose_id ║
╠════╪═════════╪════════════╣
║ 1  │ 2       │ 3          ║
╟────┼─────────┼────────────╢
║ 2  │ 1       │ 2          ║
╟────┼─────────┼────────────╢
║ 3  │ 1       │ 1          ║
╚════╧═════════╧════════════╝

We want to construct a query, in PostgreSQL, where we find all of the records in the purposes table that have the same name and user_id, deleting those with the least number of associated journeys while preserving the one record with the maxmium number of journeys while also updating the purpose_id of those journeys whose associated purpose has been deleted.

Right now I have a query to find those duplicate purposes using a window function, however the result only returns one of the duplicates and it's not certain they it has the maximum number of journeys (as I haven't specified that in any of the clauses).

select id from (
  select
    id,
    row_number()
  over(partition by user_id, name) as dupe_count
  from
    purposes
) purposes
where purposes.dupe_count > 1;


texts: Oracle 11g varchar2 datatype with date and time as YYYY/MM/DD-HH24:MI:SS:FF3

texts:
I am working with a column 'LastUPDATE' in a database that is datatype of varchar2.
This column is listed as YYYY/MM/DD-HH24:MI:SS:FF3 format.
Example: '2012/09/06-21:17:30:019'

My first issue is that I cannot change the database or change this format due to the application that usses the data in this date and time format.

I need to know how I can work with only the date part, extracting it so that I can calulate the date from 30 days and so on.

I have been able to extract for the view by RegExpections but not able to get the data for calculation.

I am familar with Oracle because i have to work with the data in my work but not god at the date and time manipulation.

i have searched this site and others with no success on the same issue.

Can an Oracle person help me out.
Oracle 11g
Thank you
E


texts: What are the consequences, positive or negative, to having a surrogate primary key for a table which already has a guaranteed unique column?

texts:
I recently made a table that looks like the one below

CREATE TABLE example(
  id SERIAL PRIMARY KEY,
  subindusty_id INT UNIQUE NOT NULL,
  name TEXT NOT NULL
  -- other fields...
)

I realized that in this table I could just remove the column called id and make subindustry_id the primary key.

Thinking back, the only reason I can think of for why I didn't make subindustry_id the primary key is for the sake of consistency, considering that I have a lot of other tables with a column called id.

Except for things like consistency*, are there any tangible benefits/downsides to having this additional id column as the primary key when there's already another column that'd be a good primary key?

*Consistency as in "that's the way the rest of the tables look". The kind where you're enforcing a certain type of style. I guess the point of this question is to question the practice of giving every single table an id column regardless of the fact that they might already have a more suitable candidate for a primary key.


texts: Can I do profiles of an Oracle RDS instance on Amazon?

texts:
Is there any way to get histories of queries from a 3rd party application to an RDS Oracle instance?

We have a DB with less than a million rows,  and we are running some queries through a 3rd party software.  It's taking over 30 seconds to complete,  which is ridiculous for an Oracle instance with a relatively small data set. Now this software also does some calculations,  so it may be that too, we just don't know.

So we would like to see what queries are being sent in, are the joins done right, are they doing full table scans, etc...

Any tips or software that can help us understand what's taking so long?


texts:
Description:

We build a new database server Enterprisedb v9.3 (PostgreSQL commercial), so we use JMX to benchmark it with 1000 users. Here is our detailed information:

Operating System: Centos 6 x64
RAM: 8 GB
CPU: 2 CPU, Intel(R) Xeon (R) CPU @ 2.50 GHz
User quantity: 1000

Query to benchmark:

select count(distinct(a.id))
from table_a a
join table_b on a.id = b.fk_id  (*)

Running (*) before benchmark : 100 ms -> 200 ms
Running (*) during benchmark : 3000 ms -> 180000 ms (increase 30 -> 900 times)
CPU is used by benchmark: ~ 95 %
RAM is used by benchmark: ~ 3.5 GB

postgresql.conf file:

max_connections = 2000
shared_buffers = 5000MB
temp_buffers = 32MB
work_mem = 5MB
maintenance_work_mem = 1MB
effective_cache_size = 1000MB
checkpoint_segments = 128
edb_enable_icache = on
enable_indexscan = on
enable_mergejoin = on
enable_nestloop = on
edb_dynatune = 90
edb_dynatune_profile = oltp

Questions:

If resources do not change, how we can improve our configuration? (can we edit parameters in postgresql.conf?)
Can we use pgbench to benchmark with query (*) above?


texts:
I have an entity type called 'Object', and I need to model an object set as a list of unique objects with variable length. Then, each object could belongs to many object sets.

Current entity-relationship diagram (ERD)

This is my ERD so far, with a many-to-many relationship:

Tables design

At this point, we have the next tables:

Object table:

ObjectID (PrimaryKey)
ObjectAttribute1
ObjectAttribute2
[...]

ObjectSet table:

ObjectSetID (PrimaryKey)
ObjectSetAttribute1
[...]

ObjectBelongsObjectSet:

ObjectSetID (ForeignKey)
ObjectID (ForeignKey)

Using this model, we could have two different sets of objects with exactly the same combination of objects, which could never happen.

My question

How could improve my model to implement this restriction?


texts: Performing large amounts of schema updates and transaction log issues

texts:
We have an issue with growing transaction logs hitting disk size limits

When ever we upgrade our software on a client's site. our upgrade installer runs a number of scripts to add columns to tables and move data around.  once complete it will upgrade the web site that uses the db

The typical db can be of a moderate size 1GB - a few of the tables will contain 10's of millions of rows. nothing that should stress the server too much

We have very little control over our customers back up policy although we recommend full transactional logs backed up every 1hr. we cant guarentee this followed. And we have no control over when the customer runs the upgrade proccess.

Recently one of our scripts added a column to a table that typically has a large numbers of rows, 10 - 20 mil, the column had a default value so had to update all the rows.

For one customer this caused an already large transaction log to grow to the size of the disk. The script failed and at that point our installer stops

I have read a few articles regarding truncating or shrinking the log, running checkpoints , switching to simple mode.

I would like to avoid them needing to do any db maintenance before upgrading as most of them know little about it.

Does any one have a best practice to follow when running these kind of upgrade schema scripts.

The DB is MSSQL

Any help would be much appreciated

=========================== UPDATE 1====================
I will recommend the following steps  for our upgrader

•   Take DB backup before upgrading begins

•   Start of the update scripts set the recovery mode to simple

•   Before running an update that may cause the issue run a ‘checkpoint’ and then shrink the transaction log

•   Run large updates in batches (10000 rows at a time)
o     For new columns with a default value create the column allowing nulls
o     Set the default value in batches
o     Add the default value and not null constraint after
o     Run a checkpoint between each batch so that log does not need to be kept (this will avoid the log growing too large) checkpoints run once a minute by default so may not be needed

•   After all updates have been run set the recovery mode to the original state (full)

•   Backup the database to restart the full log mode


texts: What is MySQL's "Implicit Default Value" and how does it work with "strict mode"?

texts:
From this question comments,

  In the documentation (dev.mysql.com/doc/refman/5.7/en/insert.html), inserting with INSERT INTO tbl_name () VALUES(); is mentioned. It is allowed except for what the doc says : "If strict mode is not enabled, MySQL uses the implicit default value for any column that has no explicitly defined default. If strict mode is enabled, an error occurs if any column has no default value."

We were then wondering what that actually means because with strict mode,

CREATE TABLE foo ( x varchar(255) );
INSERT INTO foo VALUES (x);

is still permitted, so it seems like strict mode fails. Moreover, the CREATE TABLE implicitly creates a DEFAULT null just as if it was created explicitly.


texts: How to store file paths, which collation to use?

texts:
I have a table that stores Windows system paths. I am scratching my head about how to store them and in particular which collation to use.

The paths are unicode strings. They are case insensitive, therefore 'utf8_binis probably not the best thing to do. However all theutf8_*_ci` collations allows equality between strings corresponding to different paths.

I am aware that path comparison is generally much more involved that string comparison, due to the potential presence of parent (..) directories, symbolic links, 8.3 alternative naming, aso. I am assuming that the folders are input in a systematically consistent fashion.

What do people do when storing paths? Do they assume that folders are indeed always stored in a consistent fashion and say always store Windows paths in lower or upper case and use utf8_general? Or another collation? Or simply forget about the idea of using server-side path comparison?


texts: How to handle deadlock issue sql server 2008?

texts:
I have a simple application.This application update data of a table (B) from another table(A) which is changed every 60 sec. User may read data at any time so creating deadlock. How is this sort of problem solved in real world. Considering maximum 100 users how could I avoid deadlock. How would web application handling thousand of request avoid deadlock?

Edit:

Select Query : Select * from tableB
Update Query : update table tableB set tableB.someCOl = tableA.someCol where tableA.col1 = tableB.col3


texts: Postgres SELECT query produces exact same explain analyze results but different execution times in the results

texts:
I have a SELECT query which when running produces the exact same plan in terms of indexes being used when doing an explain analyze however the duration of the run is different (2 seconds vs 30 seconds on average).
Why would this be the case?
Query
explain analyze SELECT SUM ((t0.item_cash_staked - t0.item_cash_won))
FROM item t0, product t1
WHERE (((( t0.item_rejection_code_id IS null)
    AND (t0.item_created_on > '2019-08-01 17:38:33.613+01'))
    AND (t1.customer_id = 123456))
    AND (t1.product_id = t0.product_id));

Explain results
 Aggregate  (cost=1984710.64..1984710.65 rows=1 width=32) (actual time=26916.904..26916.905 rows=1 loops=1)
   ->  Nested Loop  (cost=1.14..1983541.69 rows=233789 width=6) (actual time=4531.244..26765.752 rows=453812 loops=1)
         ->  Index Scan using product_idx_01 on product t1  (cost=0.57..172442.67 rows=539262 width=4) (actual time=6.213..4490.454 rows=500133 loops=1)
               Index Cond: (customer_id = 123456)
         ->  Index Scan using item_idx_product_id on item t0  (cost=0.57..3.24 rows=12 width=10) (actual time=0.035..0.044 rows=1 loops=500133)
               Index Cond: (product_id = t1.product_id)
               Filter: ((item_rejection_code_id IS NULL) AND (item_created_on > '2019-08-01 17:38:33.613+01'::timestamp with time zone))
               Rows Removed by Filter: 1
 Planning time: 0.409 ms
 Execution time: 26916.999 ms
(10 rows)

Explain Result Faster
 Aggregate  (cost=1984710.64..1984710.65 rows=1 width=32) (actual time=1786.816..1786.816 rows=1 loops=1)
   ->  Nested Loop  (cost=1.14..1983541.69 rows=233789 width=6) (actual time=289.922..1687.398 rows=453812 loops=1)
         ->  Index Scan using product_idx_01 on product t1  (cost=0.57..172442.67 rows=539262 width=4) (actual time=0.013..202.082 rows=500133 loops=1)
               Index Cond: (customer_id = 123456)
         ->  Index Scan using item_idx_product_id on item t0  (cost=0.57..3.24 rows=12 width=10) (actual time=0.002..0.003 rows=1 loops=500133)
               Index Cond: (product_id = t1.product_id)
               Filter: ((item_rejection_code_id IS NULL) AND (item_created_on > '2019-08-01 17:38:33.613+01'::timestamp with time zone))
               Rows Removed by Filter: 1
 Planning time: 0.275 ms
 Execution time: 1786.866 ms
(10 rows)

I get running a query once will be slower than running it later as it will skip the planning phase, but I have had times where I have run the same query 1 after the other and it still takes approx 25 seconds to run? Why would this be?
Is there anyway of improving the query to make it better performing?
Any help is much appreciated.


texts: SQL query - Do I need a full join to produce this combined result?

texts:
With two tables structured like this:

Table1                       Table2
----------------             ----------------
id, time, x                  id, time, y, z
1   1     1                  1   2     2  2
1   2     2                  1   3     3  3

I am trying to join the tables to get a result that looks like this:

Result
--------------------------------
id, time, x, y, z
1   1     1
1   2     2  2  2
1   3        3  3

I was able to get a partial result with a query like this, but it is returning all of the values from table2 (including undesired ids) along with the joined values of table1.

select *
from Table1
full outer join Table2 on Table1.time=Table2.time and Table1.id ='1' and Table2.id ='1'
order by Table2.time

Does anyone know how to fix? I'm trying to run this query in AWS Athena/Prestodb if that matters.

Thanks for the help!


texts: Is it possible to pass in multiple values in the WHEN expression

texts:
I am trying to update a bunch of rows and their approval status, I have something like this

UPDATE table
SET [Approval] =
CASE
WHEN @approvalStatus = 'Approve' then 'Approved'
WHEN @approvalStatus = 'Reject' then 'Rejected'
END
WHERE [User ID] = @userID

I wanted something like this:

UPDATE table
SET [Approval] =
CASE
WHEN {1} = 'Approve' then 'Approved'
WHEN {1} = 'Reject' then 'Rejected'
END
WHERE [User ID] IN ({0}) AND [Approval] IN ({1})

Is there something like this possible

Edit:

Sample Data:

{ "User ID": "1", "Approval": "Approve", "Name": "John" }
{ "User ID": "2", "Approval: "Reject", "Name": "Bob" }

What I would pass in  {0} would be ('1','2') and for {1} would be ('Approve', 'Reject')


texts:
CPU utilization is very high and the SQL Server process is consuming a lot of CPU. We have run few queries and notice that there are a few SPIDs taking a long time to complete and these queries may be causing the high CPU pressure.  Is there any way to get queries which are taking a lot of CPU of each SQL Server process is consuming?

I want to put an alert when ever the CPU reaches to 90% above then mail an alert with high consuming queries and to get exact one among all. I got the below process to get the high cpu thread which is using the CPU more but I need it through script to get the information. How to do this by script?

SQL Server 2014
OS: Windows 2012 Server


texts: Could Oracle ORA-08103 'object no longer exists' error be caused by size of undo_retention parameter?

texts:
Had a once-a-day long running data warehousing procedure that I had written that was failing about 50% of the time.  Logs were showing ORA-08103 'object no longer exists'.

ORA-12012: error on auto execute of job "KIPP_NWK"."TS_GRADES_TIME_REFRESH"
ORA-08103: object no longer exists

I created the ServerError trigger suggested on by Leigh Riffel here but that didn't capture any extra level of detail beyond the errors thrown by the log.  Was a touch puzzled because most of what I could find about the error pointed to block corruption, which seemed like a dead end the more I investigated.

Some context about the job in question - we're a school district and our student info system runs on top of Oracle.  We have robust SELECT privileges, but can't create anything on the production machine.  We connect to the production db over a database link, but have noticed enormous performance differences for queries written directly to the production machine vs. executed over the dblink.  (As an aside, as research for this question I saw some new resources mentioning using the driving_site hint to improve queries over dblink, which might be another route to resolving the problem more simply).

The process that was throwing the 08103 errors above was an ETL process that

1) copied a bunch of tables from the production database into local global temporary tables (ON COMMIT PRESERVE ROWS)

2) calculated and averaged student grades, using some temporary staging tables to store intermediate results

3) truncated the result table

4) inserted (with append hint) into the final result table.

Some unrelated research into materialized view 'snapshot too old' errors lead me to the UNDO_RETENTION parameter.  I couldn't find any documentation that connected ORA-08103 to UNDO tablespace or the size of the UNDO_RETENTION parameter, but upping the size of that parameter seems to have resolved the issue for us.

So, question - given the ambiguity of the 'object no longer exists' error, is it plausible that the size of the UNDO_RETENTION parameter was at fault?  Changing UNDO_RETENTION for the entire db feels rather over-broad - are there other potential avenues/less invasive fixes worth investigating?  I can provide more detail about the procedure in question, but the answer I'm looking for would talk more about the relationship between UNDO_RETENTION, global temporary tables, and anything special about database links if there's anything there that wouldn't meet the eye.  (I'm aware that there's an implicit commit in TRUNCATE, and I don't think that has anything to do with the issue I was seeing, given that sometimes the procedure would execute successfully.)

Thanks!


texts:
We have several dozen small-medium (<1Gb) databases on our SQL Server 2008R2 (Standard Edition) server, with one or two more being created every month. The server itself is a fairly well spec'd box for the task; plenty of processor, memory, and disk space and throughput. Unfortunately, we don't have a similarly spec'd DBA to consult in-house :(

All the databases are currently in SIMPLE recovery mode, partly for convenience, mostly due to ignorance of how FULL mode works. The databases are being backed up every night (using BackupExec).

We're now looking at the best way to introduce some kind of simple redundancy in our SQL infrastructure; we have a companion server ready of similar spec to the primary server that can be quickly put to use if the primary server fails. Our requirements are fairly basic: we just need some mechanism that will take a copy of the database to another server in a regular basis. We don't need automatic failover, and we don't necessarily need the data to be replicated in real-time: once every 5-15 minutes would be fine, as long as it doesn't impact the performance of the primary server too heavily.

Ideally, the replicated database would be available for read-only queries for reporting et al.
I've looked and experimented with the following options:

Replication. I've had a horrible time with this: we've had one database replicated between servers for over a year, and although it works well most of the time, for no apparently reason the replication breaks every couple of months. I've spent hours (days?) trying to find the cause for this, but the logging is indecipherable and all advice seems to point to ultimate what I've had to do each time - blow away the replication and/or reinitialise. This is no fun on one database; having to do this semi-regularly for potentially dozens of databases is out of the question.
Mirroring. I've played around with this, and it seems to work well, but the mirrored copy database remains in recovery mode, and therefore isn't available for queries.
Log file shipping. I've not looked into this too deeply yet, but the principals seem easy enough. It seems if I set the restore operation to "Standby" I can have the secondary database available for read-only queries. However, I've seen notes that suggest that, in this mode, restores can be time-consuming, and connections may need to be closed during the restore, which may affect us if we start to rely on the read-only copy being available.

Not having much experience of any of the above - we've only really used Replication before, and as mentioned our experience hasn't been good - I'm hoping that someone can advise me on the best strategy to employ. Are there any other methods I should be considering, or any third-party software that might help? Am I being naive, and it's simply not possible to satisfy all our (hopefully modest) requirements?


texts: What is the meaning of "Monotonically Changing Shard Keys"?

texts:
I am writing my first pubblication concerning MongoDB and more specific MongoDB Sharded clusters.
On the documentantion I found that the shard key in case of ranged sharding should be "Non-Monotonically Changing Shard Keys".

Can someone explain it to me? Example would be appreciate.

Thanks.


texts: Is this data duplication when two exact pieces of data have different lifespans?

texts: In my system, user has many bank accounts stored in bank_accounts table. Each bank account has a currency column. Now, users can receive payments to those bank accounts, stored in payments table. So, each payment is related to one row in bank_account table. In addition to bank_account_id, each row in payments table also has amount and currency columns. As you can see, the currency column on payments table causes data duplication, because the currency could always be derived from the related bank account. However, I'm afraid that in the future a bank account may be deleted from the system but the related payments will have to stay. Is it a valid argument to conclude that I don't have duplicated data in my schema?


texts: Does ordinal position of columns matter for SQL Server replication

texts:
I am setting up replication on SQL Server 2017. Transaction replication with push subscriptions such that many smaller database are replicated into a single larger database for reporting.

The snapshots wouldn't initialize for some tables in some databases because of "field size too small" errors. The tables had the same "schema" in that they had all the same fields with the same datatypes and primary key. However, the ordinal position of the columns varies for some of the tables in question between databases. I think the  snapshots wouldn't initialize because the bcp command it uses expects the tables' columns to be in the same order.

Two questions:

Can anyone confirm that the the ordinal position matters for replication as far as snapshots initializing goes? It makes sense to me that it would but I can't find anything that says that it does explicitly.
If I initialize the subscription manually (allowing me to skip the snapshot initialize step) would replication work with the ordinal position of the columns being different for the tables but the tables otherwise being identical? Is this supported or do I need to reorder all the columns to make sure they are the same?


texts: What function quotes an identifier in dynamic-sql with SQL Server?

texts:
What is the SQL Server method of safe-quoting identifiers for dynamic sql generation.

MySQL has quote_identifier
PostgreSQL has quote_ident

How do I ensure given a dynamically generated column name for a dynamically generated statement that the column itself isn't a SQL-injection attack.

Let's say I have a SQL Statement,

SELECT [$col] FROM table;

which is essentially the same as

'SELECT [' + $col + '] FROM table;'

What stops an injection attack where

$col = "name] FROM sys.objects; \r\n DROP TABLE my.accounts; \r\n\ --";

Resulting in

SELECT [name] FROM sys.objects;
DROP TABLE my.accounts;
-- ] FROM table;


texts: Unable to connect to postgres inside a docker container

texts:
I've started a new postgres:12 docker instance as:
$ docker run -it -d --name newdb \
    -p 5444:5432 \
    -e POSTGRES_PASSWORD=postgres \
    -e POSTGRES_USER=postgres \
    --volumes-from dbstore \
    -v /mnt/data/dbbackup:/backup postgres:12 bash \
    -c "cd /dbtestdata && tar xvf /backup/20210206_backup.tar --strip 1 && /bin/bash"

Then I attached to it:
$ docker exec -it newdb /bin/bash

Created a cluster:
# pg_createcluster 12 main

Which led to:
Creating new PostgreSQL cluster 12/main ...
/usr/lib/postgresql/12/bin/initdb -D /var/lib/postgresql/12/main --auth-local peer --auth-host md5
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "en_US.utf8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are disabled.

fixing permissions on existing directory /var/lib/postgresql/12/main ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... Etc/UTC
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

Success. You can now start the database server using:

    pg_ctlcluster 12 main start

Ver Cluster Port Status Owner    Data directory              Log file
12  main    5432 down   postgres /var/lib/postgresql/12/main /var/log/postgresql/postgresql-12-main.log

Started the service:
# service postgresql restart
[ ok ] Restarting PostgreSQL 12 database server: main.

Tried both ways to connect, but none was successful:
# psql -U postgres
psql: error: FATAL:  Peer authentication failed for user "postgres"

# psql -U postgres -W
Password: ******* <<-- postgres
psql: error: FATAL:  Peer authentication failed for user "postgres"

What could I have done wrong?


texts: Why does DBMS_UTILITY.COMPILE_SCHEMA break function-based indexes?

texts:
After mass structure updates, some views/procedures became invalid.

So I just recompiled them with:

exec dbms_utility.compile_schema( USER )

But after that, all function-based indexes, which before were in a good state, became broken, and SQL statements against those tables failed. And that is the problem.

Example of such indexes:

CREATE INDEX DOCUMENTS_NMBR_UCN_FIDX ON DOCUMENTS(Str2Number(NUMBER_UCN));

CREATE INDEX DOCUMENTS_DATE_FIDX ON DOCUMENTS(Date2Number(REG_DATE));

Why did that happen?


texts:
I have created a PDB using Database Configuration Assistant.Is there a way I can look at where the data files got created? I did not get the option in DBCA to specify my data file.

Thanks in advance


texts: Is there an actual problem in my storage IO or is the data incorrect?

texts:
Running SP_Blitz indicated that I have a slow read/write performance on my drives. Reading further, I eventually landed on this query to view the actual statistics of IO occurring on my drives:

SELECT a.io_stall, a.io_stall_read_ms, a.io_stall_write_ms, a.num_of_reads,
a.num_of_writes,
--a.sample_ms, a.num_of_bytes_read, a.num_of_bytes_written, a.io_stall_write_ms,
( ( a.size_on_disk_bytes / 1024 ) / 1024.0 ) AS size_on_disk_mb,
a.database_id,
db_name(a.database_id) AS dbname,
b.name, a.file_id,
db_file_type = CASE
                   WHEN a.file_id = 2 THEN 'Log'
                   ELSE 'Data'
                   END,
UPPER(SUBSTRING(b.physical_name, 1, 2)) AS disk_location
FROM sys.dm_io_virtual_file_stats (NULL, NULL) a
JOIN sys.master_files b ON a.file_id = b.file_id
AND a.database_id = b.database_id
ORDER BY a.io_stall DESC

*Row #1 is the database mdf, #2 is transaction log

According to the result, my database should practically be unusable but this isn't the case. We're operating a fairly large OLTP database with hundreds of transactions occurring at any given second, yet there isn't any sign of performance issues (at least none that we heard from our customers).

Should I actually be worried about the above statistics? Is there an actual  problem I need to address or is it simply the case that query is incorrect for our case?

If there is a problem, where should I start?


texts: How to delete all the tab and enter in a table?

texts:
I have a big table with several \n (enter) and \t (tab) characters scattered in my data

It looks something like the following:

col1   col2   col3
data   \ndata data
\ndata data   \tdata

I need to delete all the \n and \t characters from all the columns.

Is there a simple way to do this ?

update:
when I record the table into tsv, it will be as

data
\ndata data
\ndata data \tdata

if I simply delte all \n and \t, the tsv still look the same except the \n

data
data data
data data data