Stored Procedure to Rename Table and Column names There is an example dataBase in which we have some tables.

The word "Account" is included in the names of 5 tables in that database.

For example we have:

Table1: FactAccount
Table2: FactAccountBalance
Table3: DimAccountTime

"Account" is also included in the name of some of the columns.

We need a stored procedure to replace the word "Account" with "Items" in both column and table names, so that at the end of the stored procedure we have:

Table1: FactItems
Table2: FactItemsBalance
Table3: DimItemsTime

I have tried a combination of sp_rename and the replace function but I was not successful.

This solution uses the sys.sp_rename() database engine stored procedure.

  Changes the name of a user-created object in the current database. This object can be a table, index, column, alias data type, or Microsoft .NET Framework common language runtime (CLR) user-defined type.

But read carefully the warning message:

  Caution: Changing any part of an object name can break scripts and stored procedures. We recommend you do not use this statement to
  rename stored procedures, triggers, user-defined functions, or views;
  instead, drop the object and re-create it with the new name.

You can get table and column names by querying the INFORMATION_SCHEMA system information schema views (but see The case against INFORMATION_SCHEMA views by Aaron Bertrand).

--= returns tables
SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE
FROM   INFORMATION_SCHEMA.TABLES
WHERE  TABLE_NAME LIKE '%account%'
AND    TABLE_TYPE = 'BASE TABLE'
AND    TABLE_CATALOG = 'rextester'

| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME         | TABLE_TYPE |
|---------------|--------------|--------------------|------------|
| rextester     | dbo          | DimAccountTime     | BASE TABLE |
| rextester     | dbo          | FactAccount        | BASE TABLE |
| rextester     | dbo          | FactAccountBalance | BASE TABLE |

--= returns columns
SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME
FROM   INFORMATION_SCHEMA.COLUMNS
WHERE  COLUMN_NAME LIKE '%account%'
AND    TABLE_CATALOG = 'rextester'

| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME         | COLUMN_NAME  |
|---------------|--------------|--------------------|--------------|
| rextester     | dbo          | FactAccount        | account_id   |
| rextester     | dbo          | FactAccount        | account_item |
| rextester     | dbo          | FactAccountBalance | account_id   |
| rextester     | dbo          | DimAccountTime     | account_id   |
| rextester     | dbo          | DimAccountTime     | account_time |

I have set up the next schema on Rextester:

CREATE TABLE FactAccount
(
     account_id int primary key,
     account_item int
);

CREATE TABLE FactAccountBalance
(
    account_id int references FactAccount(account_id),
    balance decimal
);

CREATE TABLE DimAccountTime
(
    account_id int,
    account_time datetime
);

Rename columns

  Syntax: sp_rename 'schema.table.column_name', 'new_column_name', 'COLUMN'

--= cursor to rename columns
-------------------------------------------------------------
CREATE PROCEDURE dbo.renameColumns(@Catalog sysname, @OldName sysname, @NewName sysname)
AS
BEGIN

    DECLARE @schema nvarchar(128);
    DECLARE @table sysname;
    DECLARE @column sysname;
    DECLARE @cmd nvarchar(max);

    DECLARE curRes CURSOR FAST_FORWARD FOR
    SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME
    FROM   INFORMATION_SCHEMA.COLUMNS
    WHERE  COLUMN_NAME LIKE '%' + @OldName + '%'
    AND    TABLE_CATALOG = @Catalog

    OPEN curRes;
    FETCH NEXT FROM curRes INTO @schema, @table, @column;

    WHILE @@FETCH_STATUS = 0
    BEGIN

        set @cmd =   'sp_rename ''' + @table + '.' + @column + ''', '''
                 + replace(@column, 'Account', 'Items') + ''', ''COLUMN''';

        EXEC(@cmd);

        IF @@ERROR <> 0
        BEGIN
            CLOSE curRes;
            DEALLOCATE curRes;
            RAISERROR('Error renaming columns',16,1);
            RETURN -1;
        END

        FETCH NEXT FROM curRes INTO @schema, @table, @column;
    END

    CLOSE curRes;
    DEALLOCATE curRes;
    RETURN 0;
END
GO
--=-------------------------------------------------------------

exec renameColumns 'rextester', 'account', 'items';
GO

Rextester here

Rename tables

  Syntax: sp_rename 'schema.table_name', 'schema.new_table_name'

--= cursor to rename table's name
-------------------------------------------------------------
CREATE PROCEDURE dbo.renameTables(@Catalog sysname, @OldName sysname, @NewName sysname)
AS
BEGIN

    DECLARE @schema nvarchar(128);
    DECLARE @table sysname;
    DECLARE @cmd nvarchar(max);

    DECLARE curRes CURSOR FAST_FORWARD FOR
    SELECT TABLE_SCHEMA, TABLE_NAME
    FROM   INFORMATION_SCHEMA.TABLES
    WHERE  TABLE_CATALOG = @Catalog
    AND    TABLE_NAME LIKE '%` + @OldName + `%'
    AND    TABLE_TYPE = 'BASE TABLE';

    OPEN curRes;
    FETCH NEXT FROM curRes INTO @schema, @table;

    WHILE @@FETCH_STATUS = 0
    BEGIN

        set @cmd =   'sp_rename ''' + @schema + '.' + @table + ''', '''
                     + @schema + '.' + replace(@table, @OldName, @NewName) + '''';

        --EXEC(@cmd);
        select @cmd;
        IF @@ERROR <> 0
        BEGIN
            CLOSE curRes;
            DEALLOCATE curRes;
            RAISERROR('Error renaming tables',16,1);
            RETURN -1;
        END

        FETCH NEXT FROM curRes INTO @schema, @table;
    END

    CLOSE curRes;
    DEALLOCATE curRes;
    RETURN 0;
END
GO
--=-------------------------------------------------------------

exec renameTables 'rextester', 'account', 'items';
GO

Rextester here

Now, if you query INFORMATION_SCHEMA again, replacing account with items, this is the result:

| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME           | TABLE_TYPE |
|---------------|--------------|----------------------|------------|
| rextester     | dbo          | dbo.DimItemsTime     | BASE TABLE |
| rextester     | dbo          | dbo.FactItems        | BASE TABLE |
| rextester     | dbo          | dbo.FactItemsBalance | BASE TABLE |

| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME           | COLUMN_NAME |
|---------------|--------------|----------------------|-------------|
| rextester     | dbo          | dbo.FactItems        | Items_id    |
| rextester     | dbo          | dbo.FactItems        | Items_item  |
| rextester     | dbo          | dbo.FactItemsBalance | Items_id    |
| rextester     | dbo          | dbo.DimItemsTime     | Items_id    |
| rextester     | dbo          | dbo.DimItemsTime     | Items_time  |


PostgreSQL: Cast any boolean field to integer in SELECT * query In PostgreSQL, I can cast individual boolean fields to integer when I want to get 0/1 output instead of f/t.

SELECT some, thing, mybool::integer FROM mytable;

But is there a way to ask PostgreSQL to do it for any boolean field in the query, without specifying the field name?

If I have

SELECT * FROM mytable;

I would like any bools to be output as 0/1.

PostgreSQL doesn't have a display type for output, so the question is just somewhat kind of silly. Using libpq you have two options

Binary
Text

You don't get to customize how it delivers text back. And, it wouldn't make much sense either way. The library exists for people to build interfaces with, not for end users. Using C, if you're taking 1-byte for a boolean value, it's better to have t and f because 0 (the integer) and '0' the string are fundamentally different but share the same gylph "0" and description "zero". Now for psql I think you may have a valid feature request for that. Perhaps in the future you'll see something like

\pset numericbinary

And you're welcome to open a feature request on that.

You can see this hard coded in the source here


Transaction log uses (not backups of transaction logs) Working with an interesting scenario that quite frankly, I have not had the time to test, and would love some expert insight. I have a question concerning transaction logs, and their possible use for recovery (or anything at all for that matter). Imagine that you are using VSS to take application consistent snapshots of volumes holding both .mdf and .ldf files for the database (storage array driven). The questions I have is:

Given that only VSS snapshots are available (point in time), I am wondering if there is any use in having a database in full recovery (assuming there was a way of truncating the transaction logs as well). So, the setup is: database is in full recovery -> VSS snapshot of database -> truncate transaction logs. Can the transactions in the log be of any use to me if I were to mount a clone of the database?

I would 'suggest' that VSS snapshots are 'mostly' intended for an out-and-out disaster restore scenario.  It is my understanding that VSS snapshots allow you to restore to that snapshot time.  No time just before snapshot and no time just after the snapshot (until the next snapshot).

What are you going to do when an accidental delete occurs due to user error?
What happens 10 minutes after your VSS snapshot is taken and the server dies or a disk crashes.  What about corruption that hits 10 minutes after your VSS snapshot?

There is tremendous benefit in having your databases in FULL recovery mode and taking timely transaction log backups (even down to the minute)

Honestly, this gets down to your company RPO (Recovery Point Objective) and RTO (Recovery Time Objective).  Are YOU the one that can actually decide these things?


Suspended in index rebuild In SQL Server 2012, an Index Rebuild job is taking a very long time (up to 8 hours). However, not even one Index rebuild completed, so I stopped the index job.

In monitoring SQL task:

state = SUSPENDED
, comment = ALTER INDEX
, APPLICATION name = Microsoft SQL server Management Studio
, Query Wait = LCK_M_SCH_M
, Head Blocker = object lock
lock Partition = 15
objid=585105175
subresource=FULL
dbid=5
id=lockaa7aae200
mode=Sch-S
associatedObjectId=585105175 1386

Thanks in advance for any helpful info.

The reason that your index rebuild isn't completing is because of the LCK_M_SCH_M wait type.  What happens when you try to rebuild an index, a Sch-M lock is requested on the object that you're trying to rebuild.

Please see this below chart for lock compatibility:

As you can see here, a Sch-M lock has a conflict with almost every locking scenario (shared, exclusive, update, schema stability, etc.).

Here's a small example showing what may be happening in your environment.  To create the test object in a test database:

use TestDB;
go

create table dbo.ConcurrencyTest
(
    id int identity(1, 1) not null
        constraint PK_ConcurrencyTest_Id primary key clustered,
    some_int int not null
        default 1
);
go

insert into dbo.ConcurrencyTest
default values;
go 100

Now if one session is executing a query, and it keeps the lock open (I'm using an update query and not committing the transaction):

use TestDB;
go

begin tran;

    update dbo.ConcurrencyTest
    set some_int = 2
    where id = 7;

--commit tran;

And if another session attempts to rebuild the clustered index on that table:

use TestDB;
go

alter index PK_ConcurrencyTest_Id
on dbo.ConcurrencyTest
rebuild;
go

It's going to be blocked by the initial UPDATE query.  We can see this through a little diagnostic query below:

select
    l.resource_type,
    l.resource_associated_entity_id,
    l.request_mode,
    l.request_status,
    l.request_session_id,
    st.text as blocked_sql_text,
    r.blocking_session_id,
    stb.text as blocking_sql_text
from sys.dm_tran_locks l
inner join sys.dm_exec_connections c
on l.request_session_id = c.session_id
left join sys.dm_exec_requests r
on l.request_session_id = r.session_id
inner join sys.dm_exec_connections cb
on r.blocking_session_id = cb.session_id
cross apply sys.dm_exec_sql_text(c.most_recent_sql_handle) st
cross apply sys.dm_exec_sql_text(cb.most_recent_sql_handle) stb
where l.resource_database_id = db_id('TestDB')
and l.request_status = 'WAIT'
and r.blocking_session_id is not null
and r.blocking_session_id > 0;

My output looks like the following:

As you can see here, my ALTER INDEX ... REBUILD command is being blocked by the UPDATE query.  The request lock is the Sch-M lock that is required on the OBJECT in order for this operation to complete.

Due to this concurrency conflict, it is advisable to schedule your index maintenance (as well as other maintenance tasks) during a window where there is little to no user load.


Installed mysql 8 on Mac then mysql 5.7 now now they wont work I have a brand new Macbook pro with Mojave. I installed mysql 8 using the dmg file but realized that Sequel Pro is not playing nice with it and after researching I decided to install mysql 5.7. This time I installed brew and did it. Sequel pro keeps shutting down and my previous database is showing up even though I thought I removed it. Before I reset the mac and start over, is there a way I can completely remove mysql ( all versions ) from the computer and reinstall using brew just the 5.7 version?

If this is the wrong place to ask kindly let me know where I should ask and I will.

If you originally installed mysql from mysql.com, then the installer adds a "mysql" section in your system preferences which has an "uninstall" button there.


Cleaning up orphaned metadata in information_schema OK - so the server (Percona 5.6 in this case) 'went away' in the middle of declaring a constraint like so:

alter table mytable
add constraint fk_mytable_myothertable
foreign key (myothertable_id)
references myothertable(id)
on delete restrict
on update restrict;

Now, the constraint does not show in

show create table mytable;

nor in

show keys from mytable;

yet when I try to re-run the creation of the constraint, I get

ERROR 1022 (23000): Can't write; duplicate key in table '#sql-115b_290'

--so, I searched through information_schema and found this:

mysql> select * from INNODB_SYS_FOREIGN where id like '%fk_mytable_myothertable%';
+------------------------------+------------------+------------------+--------+------+
| ID                           | FOR_NAME         | REF_NAME         | N_COLS | TYPE |
+------------------------------+------------------+------------------+--------+------+
| mydb/fk_mytable_myothertable | mydb/#sql-8a0_e6 | mydb/myothertable|      1 |    0 |
+------------------------------+------------------+------------------+--------+------+
1 row in set (0.00 sec)

mysql> select * from INNODB_SYS_FOREIGN_COLS where id like '%fk_mytable_myothertable%';
+------------------------------+-----------------+--------------+-----+
| ID                           | FOR_COL_NAME    | REF_COL_NAME | POS |
+------------------------------+-----------------+--------------+-----+
| mydb/fk_mytable_myothertable | myothertable_id | id           |   0 |
+------------------------------+-----------------+--------------+-----+
1 row in set (0.00 sec)

question is: can I just delete these orphaned references to the non-existent constraint, or is there more to cleaning up?

The way that I found to "solve" this problem is fairly annoying, but there is a script that handles it.

Essentially, you need the ibdata1 and ib_logfile* files to go away (they contain the mappings of foreign keys, among other things). The only safe way to do this is to export all of your databases, stop mysql, remove the files, start mysql, and then import the files.

The script that helps solve this problem is https://github.com/uberhacker/shrink-ibdata1, even though the stated purpose of this script is different, it does solve the problem.


PAGE compression in SQL Server effect on running queries I am planning to apply PAGE compression to some of the big tables in my database (a data warehouse). These tables are fairly big with over 15 Billion rows.

When I applied the compression in test environment, the whole process took around 22 hours. These tables are accessed daily with quite long running queries.

While the compression is being applied would there be any effect on the queries that are running? Any lock etc I should be aware of?
Is there a staggered approach to applying compression?
Any other input/feedback you might have?

Offline ALTER ... REBUILD takes a big fat schema modification lock on the table with absolutely 0 concurrency (not even dirty reads can scan the table).

Online ALTER ... REBUILD, as the name suggests, allows for any concurrent query or DML operation.

The MSDN article How Online Index Operations Work describes the three phases (prepare, build and finalization) and the object locks required in each phase (IS, IS, SCH-M). The builder operates in batches and acquires data locks as it makes progress but it will back off on conflict, and there is some special sauce on handling deadlocks with the builder:

  Deadlocks between the index builder transaction that is holding the
  batch transaction locks and DML statements are possible, but are
  handled internally so that neither the DML operation nor the index
  builder transaction should terminate during the build phase due to a
  deadlock.

More details are in the Online Indexing Operations in SQL Server 2005 whitepaper.

Now that being said, for a 15B rows DW table the best option may be Columnstore Indexes.


my ssh is ok but masterha_check_ssh is fail my environment is vmware12 + centos6.6 + mysql5.5 + mha4mysql-manager-0.53-0.noarch.rpm + mha4mysql-node-0.53-0.noarch.rpm

my structure is one master three slaves

master  192.168.233.135 node
slave1  192.168.233.132 node
slave2  192.168.233.133 node
slave3  192.168.233.134 node + manager

My root@slave3 to root@master is OK!

[root@slave3 ~]# ssh root@master
Last login: Fri Jun  3 08:03:21 2016 from 192.168.233.134

[root@slave3 ~]# ssh root@192.168.233.135
Last login: Fri Jun  3 08:38:00 2016 from 192.168.233.134

but when I use masterha_check_ssh it fails

[root@slave3 ~]# /usr/bin/masterha_check_ssh --conf=/etc/app1.cnf
Tue May 31 17:08:23 2016 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Tue May 31 17:08:23 2016 - [info] Reading application default configurations from /etc/app1.cnf..
Tue May 31 17:08:23 2016 - [info] Reading server configurations from /etc/app1.cnf..
Tue May 31 17:08:23 2016 - [info] Starting SSH connection tests..
Tue May 31 17:08:26 2016 - [error][/usr/lib64/perl5/vendor_perl/MHA/SSHCheck.pm, ln63]
Tue May 31 17:08:25 2016 - [debug]  Connecting via SSH from root@slave3(192.168.233.134:22) to root@master(192.168.233.135:22)..
Permission denied (publickey,gssapi-keyex,gssapi-with-mic,password).
Tue May 31 17:08:26 2016 - [error][/usr/lib64/perl5/vendor_perl/MHA/SSHCheck.pm, ln107] SSH connection from root@slave3(192.168.233.134:22) to root@master(192.168.233.135:22) failed!
Tue May 31 17:08:28 2016 - [debug]
Tue May 31 17:08:24 2016 - [debug]  Connecting via SSH from root@slave1(192.168.233.132:22) to root@master(192.168.233.135:22)..
Tue May 31 17:08:26 2016 - [debug]   ok.
Tue May 31 17:08:26 2016 - [debug]  Connecting via SSH from root@slave1(192.168.233.132:22) to root@slave2(192.168.233.133:22)..
Tue May 31 17:08:26 2016 - [debug]   ok.
Tue May 31 17:08:26 2016 - [debug]  Connecting via SSH from root@slave1(192.168.233.132:22) to root@slave3(192.168.233.134:22)..
Tue May 31 17:08:28 2016 - [debug]   ok.
Tue May 31 17:08:28 2016 - [debug]
Tue May 31 17:08:23 2016 - [debug]  Connecting via SSH from root@master(192.168.233.135:22) to root@slave1(192.168.233.132:22)..
Tue May 31 17:08:25 2016 - [debug]   ok.
Tue May 31 17:08:25 2016 - [debug]  Connecting via SSH from root@master(192.168.233.135:22) to root@slave2(192.168.233.133:22)..
Tue May 31 17:08:26 2016 - [debug]   ok.
Tue May 31 17:08:26 2016 - [debug]  Connecting via SSH from root@master(192.168.233.135:22) to root@slave3(192.168.233.134:22)..
Tue May 31 17:08:28 2016 - [debug]   ok.
Tue May 31 17:08:28 2016 - [debug]
Tue May 31 17:08:24 2016 - [debug]  Connecting via SSH from root@slave2(192.168.233.133:22) to root@master(192.168.233.135:22)..
Tue May 31 17:08:26 2016 - [debug]   ok.
Tue May 31 17:08:26 2016 - [debug]  Connecting via SSH from root@slave2(192.168.233.133:22) to root@slave1(192.168.233.132:22)..
Tue May 31 17:08:27 2016 - [debug]   ok.
Tue May 31 17:08:27 2016 - [debug]  Connecting via SSH from root@slave2(192.168.233.133:22) to root@slave3(192.168.233.134:22)..
Tue May 31 17:08:28 2016 - [debug]   ok.
SSH Configuration Check Failed!
 at /usr/bin/masterha_check_ssh line 44

and my /etc/app1.cnf is

[root@slave3 ~]# cat /etc/app1.cnf

  [server default]
  user=root
  password=123456
  ssh_user=root
  master_binlog_dir= /data/mysql3306/logs/
  remote_workdir=/tmp
  secondary_check_script= /usr/bin/masterha_secondary_check -s slave3-s slave2
  ping_interval=3
  master_ip_failover_script=/script/masterha/master_ip_failover
  shutdown_script= /script/masterha/power_manager
  report_script= /script/masterha/send_master_failover_mail
  manager_workdir=/var/log/masterha/app1
  manager_log=/var/log/masterha/app1/app1.log

  [server1]
  hostname=master

  [server2]
  hostname=slave1
  candidate_master=1

  [server3]
  hostname=slave2
  candidate_master=1

  [server4]
  hostname=slave3
  no_master=1

I just encountered the above error and discovered where I got it wrong.

The content of id_rsa.pub file on slave3 (which is your manager node) also but be copied over to your authorized_keys file to enable passwordless ssh into slave3. Once I copied public ssh key (id_rsa.pub) into authorized_key, the error was gone. To confirm this, you should run ssh root@192.168.233.134 on slave3 to confirm you are able to ssh into slave3 as root without a password.


How to query transfers for single source to single destination? This should probably be easier than I'm making it, but I'm scratching my head coming up with an efficient way to query the results I want.  I'm working with a table that has a source field and a destination field (along with some other unimportant fields).  What I'm looking for are unique source-destination pairs, but only ones where exactly one source matches to exactly one destination.  The basic rules involved in the selection process are:

One source must match to exactly one destination
One destination can have only a single source
A destination must not be a source for another group
There may be more than one record containing the desired source-destination pair (but we only want to return a distinct pair)

So, given this dataset:

Source   Destination
Grp10    Grp20
Grp11    Grp21
Grp11    Grp22
Grp12    Grp23
Grp12    Grp23
Grp13    Grp24
Grp24    Grp25

I would want the following results:

Source   Destination
Grp10    Grp20
Grp12    Grp23

I've gotten something close with a single query, using multiple levels of SELECT DISTINCT sub-queries joining back on the original table, but the performance began to drop off drastically (> 1 second for less than 200 rows).

I'm wondering if it would more appropriate to do grouping on Source, then, on Destination, store those in temporary tables, and join on those to reduce the data to the results I'm looking for?  Some feedback would be great in case I'm missing some really trivial way to get the results I'm looking for.

I don't know all of your source data (or why there isn't any type of unique constraint that would prevent full-on duplicates or a source with multiple destinations), but given only the sample data supplied:

;WITH s AS
(
  -- first let's eliminate duplicates
  SELECT DISTINCT Source, Destination
    FROM dbo.MyTable
)
SELECT Source, Destination
FROM s
WHERE NOT EXISTS
(
  SELECT 1 FROM s AS d WHERE

  -- eliminate chains in either direction:
    d.Destination = s.Source OR d.Source = s.Destination

  -- eliminate any source with multiple destinations:
    OR (d.Source = s.Source AND d.Destination <> s.Destination)

  -- eliminate any destination with more than one source
    OR (d.Destination = s.Destination AND d.Source <> s.Source)
);

SQL fiddle demo


What is the best option to easily fix fragmented heap indexes? I have SQL Server 2008 R2 SP2 and also SQL Server 2012 SP2 on these servers.

I have a database in which there is a lot of heap indexes which are more than 90% fragmented.

What is the best option to easily fix these heap indexes?

Making the assumption that you are using SQL Server (because the RDBMS is going to matter here) you can do the following

ALTER TABLE tablename REBUILD

That being said you can read this article by Paul Randal as to why you shouldn't.  Unless you are using your table as a staging table where you want a quick import but then clean the table out later anyway you probably want to put a clustered index on your table.  At this point it won't be a heap and you have more options for dealing with fragmentation.

You generally want your CI to be small, unchanging and ever increasing (and unique is nice but not necessary).  The unchanging and ever increasing will minimize any fragmentation for the future.


Why MAX() of SUM() giving wrong output? I have a table employee

id    name   salary  city
1     ram    50000   c1
2     sham   20000   c2
3     jadu   80000   c1
4     madhu  90000   c4
5     hari   10000   c2
6     gopal  34000   c3
7     komal  55000   c3
8     bappa  98000   c4

query is which city earning the highest.
i tried

SELECT city, SUM(salary) AS maxSalary
FROM employee GROUP BY city ORDER BY salary DESC LIMIT 1;

it works fine but if there are more than one max earning cities then it doesn't output other max cities, only the first one.

so i tried this query

SELECT city, MAX(totalSalary) maxSalary
FROM( SELECT city, SUM(salary) AS totalSalary FROM employee GROUP BY city  ) AS tempTable

it is giving

city  max
c1    188000

but correct is

city  max
c4    188000

It means it is outputting the first most city name of the table which is c1 but not the correct max earning city name which is c4.
What is the right query?

You sent the wrong query to the database.
You hit a mysql extension that is described in the manual.
A query like

SELECT city, MAX(salary)
from employee

will not work in standard SQL. It raises an error in Oracle (error message: "ORA-00937: not a single-group group function") , in MSSqlServer 2012 (error message: "Column 'employee.city' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause")  or postgresql (error message:"ERROR: column "employee.city" must appear in the GROUP BY clause or be used in an aggregate function").

In standard SQL expressions in the select list can only be expressions of columns that are also used in the group by clause and of  aggregate functions. A column values  that is used in the group by clause is the same for all rows in these group. The aggregate is also unique for a group.
The select returns one row for each group containing this uniquely defined values for a group.

If the aggregate query does not have a group by clause ther is only one group of rows.

The standard does not allow to use an arbitrary column in the select list of an aggregate query because its value not uniquely defined: the column has different values for the rows of the query so which one should be returned for this group by the query?

In mysql there is the extension that if there is a column in an expression (not an in an aggregate function expression) that is not in the group by clause the value returned by the select statement for this group is the value of this column of an arbitrary row of this group.

So the query

SELECT city, MAX(salary)
from employee

returns the sumof all employees' salaries and the city of one of these rows. But the query return exactly one reow becuase it is an aggregate query and there is only one group that contains all records from employee table.

The query

SELECT city, MAX(salary)
from employee
group by city

returns a row for each city with the city and of the salaries of each city.

The query

SELECT city, salary
from employee
group by city

returns arow for each city with and salaty of an arbitrary employee of this city.  The server decides which employee of a city is choosen.

The query

SELECT city, zipcode
from employee
group by city

also return a row for each city with the zipcode of this city. If we assume that every city haas only one
zipcode than the rows of each group defined by a city contain al the same zipcode. so independent of the row the server selects the retuned zicode wil always be the zipcode of the city.

In standard SQL this query would be written as

SELECT city, zipcode
from employee
group by city, zipcode

to create the expected result

group by city,zipcode and group city define the same groups because of the one to one correspondence between zipcdoe an city.

The query

SELECT city, SUM(salary)
from employee
group by city

will contain not only  the needed rows but more.

A way to filter out the needed rows from this query is the following

SELECT city, SUM(salary)
from employee
group by city
having SUM(salary) >= all (select SUM(salary)  city_salary
     from employee
     group by city)

There are other ways to filter.

The largest salary sum can be found by the following query

select MAX(city_salary)
   from (select SUM(salary)  city_salary
     from employee
     group by city) tab

(Note: In Oracle the same can be achieved by

select MAX(SUM(salary))  city_salary
  from employee
  group by city

)

So we can use it to filter out the needed one:

SELECT city, SUM(salary)
from employee
group by city
having SUM(salary) = (select MAX(city_salary)
   from (select SUM(salary)  city_salary
     from employee
     group by city) tab)

This  solution was  already presented by @Mihai in his comment.


Can I run DDL in pretend mode in Oracle I have a script to drop several objects in the database (including queues) -- when it partially fails I need to recreate the objects that have already been dropped to continue testing the script.  For example, if the queue doesn't exist, the script fails when trying to stop it. I want to use this for Rails migrations: new dev and test schemas won't have the queues or database links that the teardown script attempts to drop, for example.

Many frameworks allow one to run commands in a test or pretend mode (e.g. rails generate -p).  Is there any similar functionality in Oracle for testing commands that one cannot roll back?

I understand I can do conditional checking for the presence/state of database objects, but would prefer to avoid creating a bunch of checks and dynamic SQL if there's a better way.

No need to check for existence. Just wrap your statement with an exception handler that ignores away any ORA-00955 it encounters (or modify for whatever exception number you want to ignore):

DECLARE
  ObjectExists EXCEPTION;
  PRAGMA EXCEPTION_INIT(ObjectExists,-955);
  sql_stmt VARCHAR2(100) := 'create table my_table (column_1 int)';
BEGIN
  EXECUTE IMMEDIATE sql_stmt;
-- Ignore ORA-955 errors (object already exists)
EXCEPTION WHEN ObjectExists THEN NULL;
END;


Subscribing to individual articles within a publication Can we filter the articles in the publication (which has few subscribers already) while adding the new subscriber?

look at the last statement ‘Subscribing to individual articles within a publication can get tricky’ in the below article

http://www.sqlservercentral.com/blogs/kendalvandyke/2010/08/31/replication-gotcha-2D00-including-an-article-in-multiple-publications/

Please help me in understanding.

The stored procedure sp_addsubscription allows for you to specify that you want all articles (i.e. tables) or just one.  If you want a subset of articles in a publication, you just need to run the sp_addsubscription once for each article that you want.  It looks like the sp_addsubscription defaults to @article = 'all'
My suggestion is to go through the New Subscription Wizard and have it generate the script only.  Then you can go in and modify the script as you need it.  You would need to add multiple sp_addsubscription lines.


MYSQL database how to check if database is active and on which port (Linux)? I got the server and on it is some mysql database TEST.
I tried connected on it with my root account by using DBVisualizer but I keep getting could not connect check host name and port.

I tried with root account and also with TEST user account.
Their passwords are OK.

with command

mysql> show databases;

I found my TEST database.
I do not know any other parameters how to connect to database?
I suppose either root or test can connect to it.
How to check on which port is the database or does it have to be active?

I tried with netstat and this is my response? what  does it mean?

netstat -an | grep -i mysql
unix  2      [ ACC ]     STREAM     LISTENING     8596     /var/run/mysqld/mysqld.sock

Thanks

It seems that your mysql connection is only listening by unix sockets and now by the actual TCP.

To solve that, go to your configuration file, usually on /etc/mysql/ folder with the name my.cnf on Ubuntu(if you are using some other system should be similar).

In the configuration file you should have a section called [client].

make your server to listen on 127.0.0.1:3306 by:

[client]
port        = 3306
#socket     = /var/run/mysqld/mysqld.sock
host = 127.0.0.1

And than restart the server.

Now you should have access to the mysql shell by doing on your command line.

mysql -U TEST -h 127.0.0.1 -p db_name

Or you can use any other client you want.


Live migrate a database from one instance to another with zero/minimal downtime There are 2 MySQL instances: MA and MB. They are completely separate from each other (no replication between them). Each instance contains multiple databases:

MA serves db1, db2
MB serves db3, db4

I want to migrate db1 to MB without any downtime, so the result will be:

MA serves db2
MB serves db1, db3, db4

I can instruct the application layer to pause write transactions to MA for a while ( < 10 seconds). Assume that it is possible to have replication lag (if set up) between MA and MB to be < 10 seconds.

Of course the most obvious way to do it is:

pause writes to db1 ("downtime" starts)
mysqldump db1 and apply to MB
move app layer settings to use MB for db1
unpause writes to db1 ("downtime" ends)

However, the above method is not acceptable when the mysqldump/apply takes a long time (> 10 seconds) for bigger databases. I have seen the method in https://exotel.com/blog/engineering/mysql-database-migration-with-zero-downtime/ but I think it only works when moving all databases in an instance, but for my case there is db2 in MA that still needs to be online at all times.

I have tried to fiddle around with database-specific replication, but I don't think it works well (I can guess that it might work with MySQL 8.0 filtered replication feature, but we are at 5.7 and not looking forward to an update).

Do you know of any way to do this?

This is now possible with tools like Ghostferry: https://github.com/Shopify/ghostferry


Stop INNODB MONITOR OUTPUT I use MariaDB galera cluster 10.0.31 with 3 nodes.

In one node when I tail -f err log to monitor server, Every 20 seconds MySQL dumping Show InnoDB Status to err log, and the log are getting bigger.

I don't know Which variable cause to dump output Innodb status to Err log file!

The variable you're looking for is innodb_status_output.  Setting it to OFF should stop the innodb status from being dumped into the error log.


SQL Data aggregation I have a problem with sql data aggregation problem.here is the sample sql.I am using oracle database.

select pm.city cityid, to_char(pm.sampledate, 'yyyy-MM') timepoint, count(distinct   me.id) mecount, count(distinct me.hotpointid) hpcount
from upme me, appmday pm
where pm.apid = me.id
          and pm.sampledate between to_date('2011-11-01', 'yyyy-MM-dd') and
          to_date('2012-1-31', 'yyyy-MM-dd')
group by pm.city, to_char(pm.sampledate, 'yyyy-MM')

above i get the count of my data.but i also i want to get the overall result like

select   to_char(pm.sampledate, 'yyyy-MM'), count(distinct me.id), count(distinct me.hotpointid)
from upme me, appmday pm
        where pm.apid = me.id
              and pm.sampledate between to_date('2011-11-01', 'yyyy-MM-dd') and
              to_date('2012-1-31', 'yyyy-MM-dd')
              group by to_char(pm.sampledate, 'yyyy-MM')

basically i want get some result from sql ,in the same sql i want to get the count from the previous result,they both show in the result in one sql. i did not want to union these two sql ,because each sql is very slow.

did someone have a idea? or it cannot be done by one sql without union?

thank you for the reply.

You can use analytic functions to achieve this:

select distinct pm.city cityid,
    to_char(trunc(pm.sampledate, 'MM'), 'YYYY-MM') as timepoint,
    count(distinct me.id)
        over (partition by pm.city, trunc(pm.sampledate, 'MM')) as mecount,
    count(distinct me.hotpointid)
        over (partition by pm.city, trunc(pm.sampledate, 'MM')) as hpcount,
    count(distinct me.id)
        over (partition by trunc(pm.sampledate, 'MM')) as totmecount,
    count(distinct me.hotpointid)
        over (partition by trunc(sampledate, 'MM')) as tothpcount
from upme me
join appmday pm on pm.apid = me.id
where pm.sampledate between to_date('2011-11-01', 'yyyy-MM-dd')
    and to_date('2012-1-31', 'yyyy-MM-dd');

Or instead of a distinct, use a subquery and a row_number() calculation to remove the duplicates:

select cityid, to_char(timepoint, 'YYYY-MM') as timepoint,
    mecount, hpcount, totmecount, tothpcount
from (
    select pm.city cityid,
        row_number() over (partition by pm.city, trunc(pm.sampledate, 'MM')
            order by pm.city, trunc(pm.sampledate, 'MM')) as rn,
        trunc(pm.sampledate, 'MM') as timepoint,
        count(distinct me.id)
            over (partition by pm.city, trunc(pm.sampledate, 'MM')) as mecount,
        count(distinct me.hotpointid)
            over (partition by pm.city, trunc(pm.sampledate, 'MM')) as hpcount,
        count(distinct me.id)
            over (partition by trunc(pm.sampledate, 'MM')) as totmecount,
        count(distinct me.hotpointid)
            over (partition by trunc(sampledate, 'MM')) as tothpcount
    from upme me
    join appmday pm on pm.apid = me.id
    where pm.sampledate between to_date('2011-11-01', 'yyyy-MM-dd')
        and to_date('2012-1-31', 'yyyy-MM-dd')
)
where rn = 1;

If your existing query is slow you may see a performance difference between the two options, otherwise it's a matter or preference.

(Also you may be aware of this, but if your sampledate has a time component, having your where clause end on '2012-1-31' means you'll only get data up to the start of that day, and not for example anything at 2012-01-31 01:00.)


Test to see when table is candidate for compression (row or page) Does anyone know a tool like this page https://columnscore.com/ where one can determine if a table is a good candidate for row or page compression.
Also I am trying to understand the benefits of compression. I have been reading about it, but still have these questions.

Will compression make more 8KB fit in the buffer cache? When does data become uncompressed i.e. in a select?
will backups become faster?
Is the reason that compression ads cpu overhead because e.g. inserts need to be compressed before the insert?
Is there any good reason to compress tables without investigating how the tables is being used (i.e. static vs. volatile).

A colleague has started to compress many tables (page). I don't know the analysis behind this decision. I know we sometimes have resource semaphore waits during some peak hours. I actually don't think he knows what he is doing. I can see that our sos scheduler yield waits have gone slightly up.
I think the compression is causing more harm and it seems that maintenance tasks overshoot more now (e.g. backups, checkdb...).

Does anyone know a tool like this page https://columnscore.com/ where one can determine if a table is a good candidate for row or page compression.

Tool recommendations are off-topic here, primarily because there can be any number of answers, and they tend to go out-of-date quickly. That said, SQL Server does provide a built-in procedure to estimate space savings from compression, sp_estimate_data_compression_savings. It does not attempt to predict whether compression will be a good option for the workload as a whole, just whether space savings can be expected or not. It is essentially a wrapper around TABLESAMPLE, creating a new object and extrapolating full size from there.

Will compression make more 8KB fit in the buffer cache?

If compression is effective, then yes. Pages remain compressed in the buffer pool.

When does data become uncompressed i.e. in a select?

Data is decompressed when a SQL Server component above the level of the storage engine needs to work with the data. For example, an Index Scan will present decompressed data to a Filter operator in an execution plan.

The stack trace below shows one example of decompression before presenting data to the query processor (storage engine code highlighted):

You can read more about this in Compression Strategies by Sunil Agarwal (part of the SQL Server engineering team at the time).

will backups become faster?

Many people have reported that they do. The intuition is that compressed data on persistent storage requires fewer I/Os to read for backup. If your instance is typically CPU-bound, you may see slower backups as the extra processor work needed for decompression outweighs the I/O speedup.

Is the reason that compression ads cpu overhead because e.g. inserts need to be compressed before the insert?

Certainly compression adds CPU overhead, but this is typically manageable because the data compression algorithms were chosen for efficiency rather than ultimate size. Note that data compression and backup compression are very different. Backup compression is based on a variant of gzip compression, and is intended to achieve very good size reduction at the expense of more CPU usage.
Every insert and update will incur at least some data compression overhead. This is more noticeable with PAGE compression. SQL Server may also attempt to recompress an entire page after a certain number of modifications have been made to that page.

Is there any good reason to compress tables without investigating how the tables is being used (i.e. static vs. volatile).

This is subjective, and depends on your local priorities. In general, one might choose to compress without detailed testing where it is clear substantial (and necessary) space savings will be made, and the system has plenty of spare processor time available.

A colleague has started to compress many tables (page). I don't know the analysis behind this decision.

You should discuss the rationale with your colleague.


Combining data from two databases with same structure into one database I have five SQL Server databases with the same schema in five different geographical locations. These locations send periodic backups to the central server which I restore in the five respective databases.

The requirement now is that data from these five databases MUST be combined into one database for consolidation.

Any suggestion for the solution is most welcome.

There's some lack of detail in the question; for example, it would be helpful to know:

What is the reason to combine the data together into a single database? It makes no sense to me if there isn't a further goal behind doing so. Perhaps there is a better way to make that end goal happen with fewer intermediate step(s).
Is there some kind of boundary between the sources where the backups are taken, and the destination where the backups are restored that prevents using a more direct approach?
What kind of overlap in data is there between the source sites? Obviously the user data will be different, but things like default/pre-defined rows may be common (are they user-editable?).
How well-structured is the database schema? Are there sufficient surrogate and business keys defined to be able to uniquely identify rows, or merge rows by business meaning? This is critical if you choose a programmatic/dynamic approach to merge the data.
What is the data access strategy? Is it stored-procedure-based, or is it direct table access, or by views, etc.?

In any event, here are a few different architectures you could pursue based on the answers to the questions above.

The most obvious, and probably least disruptive, is to add a piece of code to the existing environment that will merge the data together from the restored databases.

Depending on the number of tables in the database, you could simply script it out (for a small number of tables), or see if there's an off-the-shelf piece of software to do it for you (for a large number of tables). Since you're asking this question, I'm assuming the number of tables isn't trivial.

I've actually written a piece of software that essentially does this (we didn't use off-the-shelf because we have custom requirements) for our database, which is about 750 tables, with the requirement to merge common data. I will tell you now that this was not an easy task. If there are custom requirements, I would strongly consider manually scripting the transfer process, even for a relatively large number of tables. That may sound like a lot of work, and it is, but it's simpler to create and maintain -- the complexity is in size rather than code wizardry, which is much more difficult to debug and test.
Merge replication. This could be accomplished directly from the source databases, assuming there isn't any barrier between the sources and target (see above).

This introduces extra requirements, potential performance issues, and support complexity around the source databases. I would only recommend this if the schema is really clean and there is very little overlap in data between the sources.
Shell database. Create a new (empty) database at the target location that contains views that mimic the source tables or views by using 3-part names to UNION or UNION ALL the data from the individual target databases.

If the eventual plan is to do something like create a data warehouse and you're just going to end up scanning the target databases anyway, this strategy could work out really well, as it's completely transparent if you end up adding more databases down the line. It also requires very little additional storage space.

So those are a few of the architectures I could think of off the top of my head. There are undoubtedly others. What you ultimately end up doing will depend greatly on your specific environment and requirements.


MS SQL Express - Find when a Query was run I have an ecommerce website in .NET and using MS SQL Server Express, Version info:

Microsoft SQL Server 2014 - 12.0.4100.1 (X64)   Apr 20 2015 17:29:27
Copyright (c) Microsoft Corporation  Express Edition (64-bit) on
Windows NT 6.3  (Build 9600: ) (Hypervisor)

There is a DELETE query running from my code and I can not figure out what is triggering it. (My entire system shows one place that has that Delete code, but that can not be the problem.)
I need to find out from the database when the Delete is running so I can try to use that Data to help me solve my problem.
Even if I can't find going backwards, I would like to install/configure something to help me find it in the future, as it is a recurring problem

Here is an example of creating a delete trigger to capture the results of the delete
--demo setup
DROP TABLE IF EXISTS TestDelete;
DROP TABLE IF EXISTS TestDeleteSave;

--create a TestDelete table and a TestDeleteSave
--the TestDeleteSave table will hold the contents of deleted rows
--from TestDelete when the trigger is fired

CREATE TABLE TestDelete (Id int, Description varchar(50));
CREATE TABLE TestDeleteSave (Id int, Description varchar(50));
go

--insert sample row into TestDelete
INSERT INTO TestDelete (
    Id
    ,Description
    )
VALUES (
    1
    ,'Test Description'
    )
go

--create a delete trigger on the TestDelete table.
--the 'deleted' virtual table will contain the rows that were deleted
--during a transaction
CREATE TRIGGER [dbo].[TestDelete_Delete] ON [dbo].[TestDelete]
AFTER DELETE
AS
BEGIN
    SET NOCOUNT ON;

INSERT INTO TestDeleteSave
SELECT *
FROM deleted

END;
GO

ALTER TABLE [dbo].[TestDelete] ENABLE TRIGGER [TestDelete_Delete]
GO

--now, delete a row from the TestDelete table which will fire the delete trigger
delete from TestDelete where id = 1

--now, select affected rows from the TestDeleteSave table
select * from TestDeleteSave


MS SQL: get permissions of a fixed server role I need to get a list of MS SQL Server fixed roles and their permissions.
There is a query to get all server-level permissions:
SELECT * FROM sys.fn_builtin_permissions('SERVER') ORDER BY permission_name;

And there is a SP sys.sp_srvrolepermission which returns list of server roles and their 'permissions'. But those permissions don't really map to what the previous query returns.
There is a pretty good explanation here https://docs.microsoft.com/en-us/sql/relational-databases/security/authentication-access/server-level-roles?view=sql-server-ver15 with image, which shows roles and permissions. And permissions on the image map to the first query results.

The question is - is there a correct way (SP, query) to get server fixed roles to permissions mapping, or should I hardcode those as per image.

Maybe this is what you are looking for:
SELECT SYS.SERVER_ROLE_MEMBERS.ROLE_PRINCIPAL_ID,
       ROLE.NAME AS ROLENAME,
       SYS.SERVER_ROLE_MEMBERS.MEMBER_PRINCIPAL_ID,
       MEMBER.NAME AS MEMBERNAME,
       MEMBER.TYPE_DESC,
       PERMISSION_NAME,
       STATE_DESC,
       CLASS_DESC
FROM SYS.SERVER_ROLE_MEMBERS
     FULL JOIN SYS.SERVER_PRINCIPALS AS ROLE ON SYS.SERVER_ROLE_MEMBERS.ROLE_PRINCIPAL_ID = ROLE.PRINCIPAL_ID
     FULL JOIN SYS.SERVER_PRINCIPALS AS MEMBER ON SYS.SERVER_ROLE_MEMBERS.MEMBER_PRINCIPAL_ID = MEMBER.PRINCIPAL_ID
     FULL JOIN SYS.SERVER_PERMISSIONS PERMISSIONS ON PERMISSIONS.GRANTEE_PRINCIPAL_ID = MEMBER.PRINCIPAL_ID


Views that inherit a table and syntax in PostgreSQL I am getting a syntax error but don't understand what is wrong.

I am using a reference here.

This is my sql-snippet:

CREATE OR REPLACE VIEW stipa_test.stipa_view
AS
  (
    SELECT *
    FROM ...
    WHERE ...
  )
INHERITS (stipa_test.stipa_type_proj);

I hope you get the idea, everything but inheritance works. So how could it look like? both the subquery view and the type_proj table

Views cannot inherit.

Only tables can.

Aside: the query for CREATE VIEW does not need parentheses. And your whole SELECT is needlessly convoluted and buggy. Untangled and without the invalid INHERITS clause:

CREATE OR REPLACE VIEW stipa_test.stipa_view AS
SELECT aar.gid AS aar_gid, aar.geom AS aar_geom
     , aar_korr.gid AS aar_korr_gid, aar_korr.geom AS aar_korr_geom
FROM   stipa_test.stipa_type_aar  aar
JOIN   stipa_test.stipa_type_aar_korr aar_korr
                   ON ST_Within(aar.geom, ST_Buffer(aar_korr.geom, 3))
-- and 'aar_korr.dato' > 'dato.aar'  -- nonsense, always FALSE


Update 1 record across 135 tables There is an application, it maintains delete flag column i.e. if a delete happen the column marked as 1 (in the deleted column).

To retrieve the record I must take this 1 to a NULL

There are 135 tables having the deleted column, and all of them must be updated based on ID column (when it has particular value)

( SELECT *
FROM   INFORMATION_SCHEMA.columns
WHERE  COLUMN_NAME = 'deleted')

How can I perform: UPDATE all tables containing the deleted column SET deleted = NULL WHERE ID = 'XXXX'

If this is a manual update you could script out the commands you need:

SELECT 'UPDATE '+ QUOTENAME(TABLE_SCHEMA)+'.'+QUOTENAME(TABLE_NAME) +
' SET deleted = NULL
WHERE ID =1;'
FROM   INFORMATION_SCHEMA.columns
WHERE  COLUMN_NAME = 'deleted';

But, be wary of these INFORMATION_SCHEMA dmvs.

Aaron Bertrand lists some good reasons not to use them in this blogpost on bad habits:

  ... I feel similarly about the INFORMATION_SCHEMA views. Since they are
  incomplete, aren't being updated for most new features, and Microsoft
  themselves recommend against them, I find it hard to justify using
  them in cases where you can (like getting just a list of columns from
  a table), but then having to use the catalog views when you need
  information the INFORMATION_SCHEMA views simply don't have (like
  partitions, or filtered indexes, or INCLUDE columns). Unless you need
  to write code that will work on multiple RDBMS platforms (in which
  case you can't use any of the SQL Server-specific features anyway),
  why not just use the catalog views always?...

So here is another solution using sys.tables & sys.columns

SELECT 'UPDATE '+ QUOTENAME(SCHEMA_NAME([schema_id]))+'.'+QUOTENAME([name]) +
' SET deleted = NULL
WHERE ID =1;'
FROM   sys.tables t
WHERE EXISTS
(
SELECT * FROM
sys.columns c
WHERE t.object_id = c.object_id
AND  c.[name] = 'deleted'
);

Testing

CREATE TABLE dbo.tableNR1(Id int, deleted bit);
INSERT INTO dbo.tableNR1(id,deleted)
VALUES(1,1);

CREATE TABLE dbo.tableNR135(Id int, deleted bit);
INSERT INTO dbo.tableNR135(id,deleted)
VALUES(1,1);

Result for both:

UPDATE [dbo].[tableNR1] SET deleted = NULL   WHERE ID =1;
UPDATE [dbo].[tableNR135] SET deleted = NULL   WHERE ID =1;

DB<>Fiddle


Simulate deadlock with more than 2 processes How to Simulate deadlock with more than 2 processes in SQL Server test environment? Can someone help me with the code? Just to clarify my question again, I don't want deadlock simulation with only 2 processes. I know how to produce deadlock with 2 processes but am unable to produce it with more than two processes.

If you know how to simulate it with 2, you know how to simulate it with 3, or with N. You have to build a cyclic graph:

2 nodes: A->B->A  (process A waits on B, process B waits on A)
3 nodes: A->B->C->A  (process A waits on B, B waits on C, C waits on A)
...
N nodes: P1->P2->...Pn->P1

To construct the Px->Py primitive (Process X waits on Process Y) use your favorite blocking mechanism. For example, row locking on a specific key P1->P2->P3->P1:

CREATE TABLE rows (key INT NOT NULL PRIMARY KEY);

1) P1, from session 1:

BEGIN TRANSACTION
INSERT INTO rows (key) VALUES (1);

2) P2, from session 2:

BEGIN TRANSACTION
INSERT INTO rows (key) VALUES (2);

3) Have P1 wait on P2. From session 1:

SELECT key FROM rows WHERE key = 2;

4) Continue with P3, session 3:

BEGIN TRANSACTION
INSERT INTO rows (key) VALUES (3);

5) Add the P2->P3 wait, in session 2:

SELECT key FROM rows WHERE key = 3;

6) To complete the cycle, add the P3->P1 wait, in session 3:

SELECT key FROM rows WHERE key = 1;

As the wait graph created a cycle, the 3 processes are now deadlocked. In a short while the engine deadlock detection mechanism (which periodically walks the wait graphs looking for cycles) detects this cycle and breaks it by choosing a victim and aborting it's transaction then raising error 1205 in the victim session.

To extend to N nodes, repeat steps 4) and 5) as necessary.


seeding mode in SQL Server 2019 Does activating the TF9567 seeding mode trace flag speed up the synchronization process in AlwaysOn?
I am using SQL Server 2019.

Trace Flag 9567 : Enables compression of the data stream for Always On
Availability Groups during automatic seeding. Compression can
significantly reduce the transfer time during automatic seeding and
will increase the load on the processor. For more information,
see Automatically initialize Always On availability group and Tune
compression for availability group.

As the document says, just when automatic seeding process during. Synchronization doesn't relate to this trace flag.


Maximum SQL Server memory set to 12 GB, but it still goes over 12 GB. How do I prevent SQL Server going over max memory limit? User 'mrdenny' wrote that after you set SQL Server maximum memory...

  Now this isn't going to limit all aspects of SQL Server to that amount of memory. This only controls the buffer pool and the execution plan cache. Things like CLR, Full Text, the actual memory used by the SQL Server exe files, SQL Agent, extended stored procedures, etc. aren't controlled by this setting.

My problem is that my server is going over the set 12 GB memory and if things like CLR, Full Text, and Server exe files taking additional memory.

How do I configure SQL Server, so it does not go over the set max limit?

I was told there was a way to do it, but I cannot find a solution after searching on Google.

My problem is that my server is going over the set 12GB memory and if things like CLR/Full Text/Server exe files taking additional memory.

What Denny Cherry wrote is true for SQL Server version from 2005 to 2008 r2. From SQL Server 2012 onward there was considerable change in SQL Server memory allocation. Memory for SQLCLR is allocated from max server memory setting in SQL Server 2012 not outside of it. BOL says that from SQL Server 2012 onward

  Max server memory controls SQL Server memory allocation, including
  the buffer pool, compile memory, all caches, qe memory grants, lock
  manager memory, and CLR memory (basically any “clerk” as found in
  dm_os_memory_clerks). Memory for thread stacks, memory heaps, linked
  server providers other than SQL Server, or any memory allocated by a
  “non SQL Server” DLL is not controlled by max server memory.

Prior to SQL 2012, the buffer pool both “managed” memory and was a consumer of memory for database pages. It’s management of memory meant it allocated 8Kb pages of memory for other consumers like plan cache. Roll forward to 2012+, the buffer pool is a pure consumer of memory from SQLOS which manages all of the memory.

  Please tell me how to configure SQL Server, so it does not go over the set max limit.

This is not possible. In SQL Server 2012 if you are not using third party linked servers providers or if any DLL which is not Microsoft provided is not loaded in SQL Server address space there is less chance that SQL Server can consume memory outside max server memory setting. But the fact is you cannot control SQL Server not to utilize memory outside buffer pool. That is how it is designed.


linked server be made using the login's current security context Can I clarify this settings linked server be made using the login's current security context.
If I connect to serverA using windows account JOESmith and create the linked server using the above option to serverB.

What happen when BOBSmith connect to serverA and query serverB. does it use JOESmith credential or BOBSmith?

sorry if this sound's like a stupid question because "current security context" should imply it will use BOBSmith's account to serverB

But I just want to clarify

From BOL:

Be made using the login's current security context

Specify that a connection will be made using the current security context of the login for logins not defined in the list. If connected to the local server using Windows Authentication, your windows credentials will be used to connect to the remote server. If connected to the local server using SQL Server Authentication, login name and password will be used to connect to the remote server. In this case a login with the exact same name and password must exist on the remote server.

http://technet.microsoft.com/en-us/library/ms188477(v=sql.105).aspx

Using the login's current security context will use whatever user is logged in to the local machine.  Be it Windows authentication or SQL authentication.

So, in your example above, if JOEsmith logs into server A and runs a query against server B, it will execute the query on server B using JOEsmith, as long as JOEsmith is a valid logon for server B.


Missing PK/FK after database migration We recently had a server migration. A new server is now the PROD server, and what used to be the PROD server is now the staging environment.

Apparently, the data was transferred correctly, but the database is missing its PK/FK contraints. Luckily, the database hasn't been touched yet as the connected application is still in migration, which means that if the PK/FK constraints are now enabled, there shouldn't be any issues because no rows were created/altered/deleted.

We stil have the original database (which is now staging). The structure is the same, but the content has already changed numerous times during dev testing.

Is there a way in which I can keep the current PROD database, and add the PK/FK constraints to it (based on the staging database)?

Extra info:

we have no backups readily available unfortunately, else I would've fixed it that way
Using SQL Server 2008 via SSMS
I'm not the one who performed the data migration. It is office procedure to move databases by using .bak files, but I fear someone used the import/export option, hence losing the PK/FK constraints.

Script out the constraints from the staging database and apply to the problem database. Lots of options, just make sure you have backups of staging and prod before you start:

Manually, one by one. Right click the objects in SSMS and opting to 'Script'.
Manually, all together (with a bit of editing). Right click database in SSMS and Tasks / Generate Scripts / Select 'Tables'. Under Scripting Options / Advanced include PK/FK. The  script created will include table definitions, you'll have to edit to leave just PK/FK.
Use a schema comparison tool to sync staging to production.
Use a script to generate the ALTER statements for you.

    SELECT
        *
        , 'ALTER TABLE ['+ FKTableSchema +'].[' + FKTableName + '] DROP CONSTRAINT '
            + ForeignKeyName AS FKDrop
        , 'ALTER TABLE ['+ FKTableSchema +'].[' +FKTableName + '] ADD CONSTRAINT '
            + ForeignKeyName + ' FOREIGN KEY(' +
          FKColumnList +') REFERENCES ['+ PKTableSchema+'].['
            + PKTableName + '] ('+PKColumnList +')' AS FKCreate
        , 'ALTER TABLE ['+ PKTableSchema +'].[' +PKTableName + '] DROP CONSTRAINT '
            + PrimaryKeyName AS PKDrop
        , 'ALTER TABLE ['+PKTableSchema +'].[' +PKTableName + '] ADD CONSTRAINT '
            + PrimaryKeyName + ' PRIMARY KEY('+PKColumnList+')' AS PKCreate
    FROM
        (
        SELECT DISTINCT
            FKTableSchema =
                (
                SELECT DISTINCT
                    table_schema
                FROM
                    INFORMATION_SCHEMA.KEY_COLUMN_USAGE
                WHERE
                    constraint_name=rc.constraint_Name
                ),
            FKTableName =
                (
                SELECT DISTINCT
                    table_Name
                FROM
                    INFORMATION_SCHEMA.KEY_COLUMN_USAGE
                WHERE
                    constraint_name=rc.constraint_Name
                    ),
            rc.constraint_name AS ForeignKeyName,
            FKColumnList =
                (
                SELECT
                    left(t.column_name,len(t.column_name)-1) AS 'ColumnList'
                FROM
                    (
                    SELECT Column_Name + ',' FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
                    WHERE constraint_name=rc.constraint_name
                    FOR XML PATH('')
                    ) AS t(column_Name)
                ),
            cu.table_schema AS PKTableSchema,
            cu.table_name AS PKTableName,
            cu.constraint_Name AS PrimaryKeyName,
            PKColumnList =
                (
                SELECT
                    left(t.column_name,len(t.column_name)-1) AS 'ColumnList'
                FROM
                    (
                    SELECT
                        Column_Name + ','
                    FROM
                        INFORMATION_SCHEMA.KEY_COLUMN_USAGE
                    WHERE
                        constraint_name=cu.constraint_name
                    FOR XML PATH('')
                    ) AS t(column_Name)
                )
        FROM
            INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS cu
        INNER JOIN
            INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS rc
        ON  rc.Unique_Constraint_name= cu.Constraint_name
    ) AS tab


MySQL Not Recovered After Running Out of Space I've got a MySQL 5.7.26 running on Ubuntu 16.04. I'm running an app that's loading a few GB data into the database, which was running fine until I let the root volume (with MySQL on) run out of space - I let a ton of application log files build up - after which MySQL started timing out transactions. This is just a dev server, so is pretty small and not closely monitored.

Anyway, I've since deleted all the log files and restarted MySQL and my app, but it's not back to its old self. The transactions aren't being timed out, but they are taking ages (used to be sub-second, now up to 45s). I'm guessing that running out of disk space has put MySQL in an odd state, meaning it still thinks it's not got space to grow data or log files.

So is there something I can to do to "recover" MySQL? To tell it that it can start expanding files again? Thanks!

you could try to repair the table (or tables) with:

mysql -u root -p -e "repair table <tablename>"


How to allow a certain domain user to create databases on a remote SQL instance? I want to allow for a certain domain user to be able to create databases on a remote SQL instance.
What steps are necessary and is it possible to perform those steps programmatically in a C# application ?
Should I first create a login if doesn't exist and then give it the dbcreator server role?
Related question: Safe and secure implementation of logon trigger in SQL Server 2014 Express edition?

Yes you would need a Login for that domain user to exist first. If all they need is the ability to manage databases (create, alter, drop, restore them) then the server role dbcreator should be sufficient.
If you anticipate multiple domain users needing access to this, then you should instead create a Login for a correlating Active Directory Group, so you don't have to continually manage setting up multiple Logins. You will then be able to map that AD Group to the server role dbcreator. (You can even use the built in "Domain Users" group if you wanted everyone on the domain to have access to creating databases.)
As far as how to accomplish this in C#, there's a multitude of ways, and that's perhaps a better question for StackOverflow because it's dependent on how you're accessing and querying your server instance. For example, with ADO.NET you could simply write a SQL query to check if a Login exists for the current user, if not create it, and assign the role. Or you can even create a procedure that takes in the current user of the application, and handles that logic for you (which will be more consumable depending on what database access framework you're using). But you'll likely have the easiest time managing this by creating an appropriate AD Group and mapping it to a Login with the dbowner role once.


joining tables with full outer join and... COALESCE? I have a few tables in an Oracle database linked by id - fk_id, if I do:

select RAP.ID_PCT_LUCRU,SES_RAP.AN,
        TIS.CANT as c1,'R12' as modc1,
        TIT.CANT as c2,'D13' as modc2,
        TIMB.CANT as c3,'D8' as modc3,
        TIFC.CANT as c4,
        TIA.CANT as c5
     from ch_trat cht
        full outer join raportor rap on cht.fk_id_raportor = rap.id
        inner join sr ses_rap on ses_rap.id = CHT.FK_ID_SR
        full outer join sortare tis on TIS.FK_TRAT_ID = CHT.ID
        full outer join transfer tit on TIT.FK_TRAT_ID = CHT.ID
        full outer join mec_bio timb on TIMB.FK_TRAT_ID = CHT.ID
        full outer join fiz_chm tifc on TIFC.FK_TRAT_ID = CHT.ID
        full outer join altele tia on TIA.FK_TRAT_ID = CHT.ID

and the result is something like this:

ID_PCT_LUCRU    AN     c1    modc1  c2   modc2  c3   modc3  c4  c5
275382          2013         R12         D13         D8
280933          2013         R12    222  D13         D8         44
295740          2013         R12         D13         D8    111  73
297089          2013         R12         D13    333  D8         124
281137          2013         R12         D13         D8    222  19
281137          2013         R12    222  D13         D8         123
281137          2013         R12         D13         D8         14
301410          2013   5103  R12         D13    444  D8         880

So I'd want that the result doesn't show me the first row with no value on c1,c2,c3,c4,c5. I know I should use COALESCE but I don't know how to use it in this case.
Many Thanks!

You can make it a subquery:

select * from (...) a
where coalesce(a.c1, coalesce(a.c2, coalesce(a.c3, coalesce(a.c4, coalesce(a.c5, null))))) is not null

You just put your query at the dots.

EDIT:

You just put where coalesce(TIS.CANT, TIT.CANT, TIMB.CANT, TIFC.CANT, TIA.CANT) is not null as a where clause in your query, as Andriy said. :)


Why are Op Stats all 0's and yet Usage Stats show 29 seeks Why are Op Stats all 0's and yet Usage Stats show 29 seeks.  This was returned from Brent Ozar's sp_blitzindex.

Usage Stats                         Op Stats
Reads: 29 (29 seek) Writes:14    0 singleton lookups; 0 scans/seeks; 0 deletes; 0 updates;

Usage statistics come from sys.dm_db_index_usage_stats, which tracks the number of execution plans that include an operator touching that index. It's reset on SQL Server service restart, or when the index is modified.

Operational statistics come from sys.dm_db_index_operational_stats, which track the number of times the index has actually been touched. It's reset on a different schedule - when that object's metadata disappears from cache.


A timeout (30000 milliseconds) was reached while waiting for a transaction response from the MSDTC service Experiencing this intermittent issue on a few servers running MSDTC.

19/09/2018 10:54:38 a.m. -- A timeout (30000 milliseconds) was reached while waiting for a transaction response from the MSDTC$cde1a4e7-dc9d-4ae3-89b9-8114147ae420 service.

19/09/2018 10:54:38 a.m. -- Cluster resource 'MSDTC-SQLServer' of type 'Distributed Transaction Coordinator' in clustered role 'SQL Server' failed.

19/09/2018 10:54:57 a.m. -- The Distributed Transaction Coordinator (cde1a4e7-dc9d-4ae3-89b9-8114147ae420) service terminated unexpectedly.  It has done this 12 time(s).

All these servers run trend micro deep security agent ( Anti Malware protection and Intrusion Prevention ). The issue completely goes away when AV is turned off. But having the AV turned off for long is not an option.

Have configured the following AV exclusions for MSDTC.

MSDTC Service
MSDTC Log file
MSDTC Log Folder

are there any more exclusions that I should get added ?

Excluding the MSDTC log file with the full path in AV ( Trend Micro ) fixed the issue.


Enumerating short versus long column names from INFORMATION_SCHEMA views on DB2 for IBM i On the IBM i (as/400) you can have short and long column names on a table. The short names are listed in the SYSTEM_COLUMN_NAME field of QSYS2.SYSCOLUMNS and the ODBC/JDBC SQLCOLUMNS view. Is it possible to get these short column names from the INFORMATION_SCHEMA view or is it not possible since its not a feature supported in most RDBMSes?

INFORMATION_SCHEMA does not contain the long column names.


How to edit `postgresql.conf` with pgAdmin 4? Same Question as this: How to edit postgresql.conf with pgAdmin 3? but updated for the completely new (total re-write) pgAdmin 4.

➥ How does one edit the configuration files with the pgAdmin 4 app?

postgresql.conf
pg_hba.conf

I added the adminpack extension as discussed here. And I restarted pgAdmin 4. Yet I cannot identify any menu item or other interface item for editing this files.

Workaround: ALTER SYSTEM

As a partial workaround, Postgres 9.4 added a feature to dynamically set the properties seen in postgresql.conf: ALTER SYSTEM commands.

ALTER SYSTEM SET configuration_parameter { TO | = } { value | 'value' | DEFAULT }

ALTER SYSTEM RESET configuration_parameter
ALTER SYSTEM RESET ALL  -- Clears all the settings set via `ALTER SYSTEM SET`.

This provides a convenient alternative to manually editing the postgresql.conf file.

Example:

ALTER SYSTEM SET wal_level = replica;

How does it work?

A postgresql.auto.conf file is written by the ALTER SYSTEM SET command.
This file and postgresql.conf are both read, so you can combine these approaches if desired. The auto file trumps the hand-edited file for coinciding properties.
For reliability, a postgresql.auto.conf.temp file is created to rollback to the original state in case of error.

Caveats:

Settings do not take effect immediately. Read the doc.
Must be superuser to execute.
Cannot be called in a transaction block or function.
Cannot set data_directory.
Obviously misuse can be dangerous to your database system, so be careful.

Tip: To read the value of the settings, see this Question, Query for all the Postgres configuration parameters‘ current values?

Workaround: Use command-line editor as postgres user

You can run a simple text-editor like nano from the command-line run with the privileges of the postgres user. Open the pg_hba.conf file, edit, and save.

For example, on a Unix/POSIX-oriented OS such as BSD or macOS:

sudo -u postgres nano /Library/PostgreSQL/11/data/pg_hba.conf

For more info, see the Question, Switch user to 'postgres' user on macOS results in “su: Sorry” error


Granting Permissions for a limited period of time Is it possible to grant permissions to a user for just a certain period of time? For example, I ran into a situation the other day where I wanted to grant a user the ability to modify data in a table for only a couple hours. I haven't found a native way to do this, but thought I'd ask anyway. This would be specifically for Azure SQL Database.

A very simple approach would be to open up an SSMS query window that's connected to the instance you want and use either WAITFOR DELAY or WAITFOR TIME

WAITFOR DELAY '00:01:00' --waits for 1 minute
PRINT 'FINISHED' --revoke permission code
--(optional TSQL code to send an email when complete

or ------

WAITFOR TIME '08:00'  --waits until 8am
PRINT 'FINISHED' --revoke permission code
--(optional TSQL code to send an email when complete

You could even include TSQL code to send you an email indicating the process had completed.

Keep in mind that if your SSMS session dies for any reason, the revoke code will not be executed.

A more robust (and more complex) solution would be to use Service Broker BEGIN CONVERSATION TIMER


What are the practical uses for relational calculus? In my Database Design course we are learning both Relational Algebra and Relational Calculus. I can see where Relational Algebra could be useful since it is closely tied to SQL.

Our professor said that Relational Calculus was used as an alternative to SQL in some RDMBS, most of which are not around any more. Is there still a practical use for Relational Calculus, or is much of this theoretical?

I agree with  @Erik, these instruments are essentially equivalent, but I am going to add a few more ideas about this topic.
Context
In order to provide more context, it is necessary to have an authoritative definition for both terms. So, here are some excerpts from the relevant paper entitled Relational Completeness of Data Base Sublanguages, which was writen by the originator of the relational model, Dr. E. F. Codd, and published in 1972:

Relational algebra. The primary purpose of this algebra is to provide a collection of operations on relations of all degrees (not necessarily binary) suitable for selecting data from a relational data base.
Relational calculus. Having defined a relational algebra, we now consider an applied predicate calculus which may also be used in the formulation of queries on any data base consisting of a finite collection of relations in simple normal form.

In this way, one can see that they serve the same purpose, since they are two different mathematical means to express a group of operations considered valid within the relational framework.
Comparison of both approaches
In the same paper, Codd contrasts both methods in order to point out some important aspects in which each one stands out:

One advantage that might be claimed for the algebraic approach is its freedom from quantifiers. Howerver, the calculus appears to be superior to algebra in four respects.

Ease of augmentation
Scope for search optimization
Authorization capability
Closeness to Natural Language

So, these aspects should be taken into account by organizations that aim to build their own languages or database platforms.
Practical value
I do not know if there are contemporary successfully commercial RDBMS that provide query languages purely based on relational calculus, but I deem that its practical worth lies in the fact that it offers a theoretically grounded alternative for vendors who plan to design and implement a new valid relational language, or extend an existing one without departing from proven and accepted practices. In this respect, it can be useful to think of relational calculus as a blueprint that such vendors can follow in order to produce a relational language.
Since the market is largely dominated by SQL platforms, millions of users around the world are already familiar with several of its particular dialects, and billions of dollars are invested in such platforms, it would be very interesting to determine whether creating new relational lenguages would be profitable, but if so, relational calculus could always play an important role.
Of course, such projects can be much more feasible in the academic field, so there is more practical value regarding this tool.


Remote SSIS development I'm currently developing SSIS packages which include transferring data between two servers. The servers are in the same local LAN, but I'm remote to them.

Is there any way that I can develop the packages on my local workstation and get the advantages of the visual debugger, etc. while having the actual data processing only happen on my remote SSIS server, saving me from having the data transferred over the WAN and back?

I'm afraid not.

Your options are

install BIDS/SSDT and do the development on the server
develop locally and pay the network penalty
spin up local instances to negate the network penalty

I am a firm believer in the last option as it ensures my package development, pre 2012, used configuration to handle changing servers. If I deployed to Dev and it blew up, there was less stink eye directed my way vs Test when ZOMG, WE'VE BEEN WAITING ON THIS AND YOU'RE STILL MAKING CODING CHANGES!!!!

If it's a we don't allow data on developer machines, well it's a stupid rule and they need to either pony up for a tool like red gate's data generator or provide developers with a sane, safe, PII free backup they can use for local development purposes.


Find a username within instance wide objects A user's AD account keeps being locked out. The latest idea from colleagues is that their username and password is hard-coded in some database in the instance (a stored procedure for example).

Is this possible?
How would I search the instance for where this could be?

If it's in the text of a proc/function you could use the easy and free to use third party plugin of redgate:     SQL Search, there are also ways to do this with TSQL, see answer on stackexchange searching through procedures

However, very likely your proc will fetch the value from a table that stores it, so the proc doesn't have to be hard coded.
If you want to search through a bunch of columns at the same time, this link might be useful to you:
Finding string in different tables


Postgresql extension pageinspect - mean bt_metap I am using Postgresql extension pageinspect.

Could someone tell me the meaning of these columns: magic, version, root, level, fastroot, fastlevel of the bt_metap function.

This information is not presents in the documentation 1[https://www.postgresql.org/docs/9.6/static/pageinspect.html]

The height of the b-tree (position of node farthest from root to leaf), is the column Level?

See below a return query that I ran on an index called idx_l_shipmodelineitem000

------------------------------------------------------------------
postgres # SELECT * FROM bt_metap ('idx_l_shipmodelineitem000');
postgres # magic    | version   | root     | level | fastroot | fastlevel
postgres # 340322   | 2         | 41827 | 3       | 41827   | 3

Best regards

src/include/access/nbtree.h:

/*
 * The Meta page is always the first page in the btree index.
 * Its primary purpose is to point to the location of the btree root page.
 * We also point to the "fast" root, which is the current effective root;
 * see README for discussion.
 */

typedef struct BTMetaPageData
{
    uint32      btm_magic;      /* should contain BTREE_MAGIC */
    uint32      btm_version;    /* should contain BTREE_VERSION */
    BlockNumber btm_root;       /* current root location */
    uint32      btm_level;      /* tree level of the root page */
    BlockNumber btm_fastroot;   /* current "fast" root location */
    uint32      btm_fastlevel;  /* tree level of the "fast" root page */
} BTMetaPageData;

So I suggest reading src/backend/access/nbtree/README


JSONB Array of Strings (with GIN index) versus Split Rows (B-Tree Index) I have a database which stores receiver to indicate which account the data relates to. This has led to tons of duplication of data, as one set of data may create 3 separate rows, where all column data is the same with the exception of the receiver column. While redesigning the database, I have considered using an array with a GIN index instead of the current B-Tree index on receiver.

Current table definition:

CREATE TABLE public.actions (
    global_sequence bigint NOT NULL DEFAULT nextval('actions_global_sequence_seq'::regclass),
    time timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    receiver text NOT NULL,
    tx_id text NOT NULL,
    block_num integer NOT NULL,
    contract text NOT NULL,
    action text NOT NULL,
    data jsonb NOT NULL
);

Indexes:

"actions_pkey" PRIMARY KEY, btree (global_sequence, time)
"actions_time_idx" btree (time DESC)
"receiver_idx" btree (receiver)

Field details:

Global sequence is a serially incrementing ID
Block number and time are not unique, but also incrementing
Global sequence and time are primary key, as the data is internally partitioned by time

There are some receivers that have over 1 billion associated actions (each with a unique global_sequence).

Average text lengths:

Receiver: 12
tx_id: 52
contract: 12
action: 6
data: small-medium sized JSONB with action metadata

Cardinality of 3 schema options:

Current: sitting at 4.2 billion rows in this table
Receiver as array: Would be at approximately 1.8 billion rows
Normalized: There would be 3 tables:

Actions: 1.8 billion rows
Actions_Accounts: 4.2 billion rows
Accounts: 500 000 rows

Common Query:

SELECT * FROM actions WHERE receiver = 'Alpha' ORDER BY time DESC LIMIT 100

All columns are required in the query. NULL values are not seen. I believe joins in the normalized schema would slow down & query speed is #1 priority)

The optimal DB design always depends on the complete picture.

Generally, there is hardly anything faster than a plain btree index for your simple query. Introducing json or jsonb or even a plain array type in combination with a GIN index will most likely make it slower.

With your original table this multicolumn index with the right sort order should be a game changer for your common query:

CREATE INDEX game_changer ON actions (receiver, time DESC);

This way, Postgres can just pick the top 100 rows from the index directly. Super fast.

Related:

Optimizing queries on a range of timestamps (two columns)

Your current indexes receiver_idx and actions_time_idx may lose their purpose.

Next to the perfect index, storage size is an important factor for big tables, so avoiding duplication may be the right idea. But that can be achieved in various ways. Have you considered good old normalization, yet?

CREATE TABLE receiver (
   receiver_id serial PRIMARY KEY
 , receiver    text NOT NULL -- UNIQUE?
);

CREATE TABLE action (  -- I shortened the name to "action"
   action_id   bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
   -- global_sequence bigint NOT NULL DEFAULT nextval('actions_global_sequence_seq'::regclass),  -- ??
   time       timestamptz NOT NULL DEFAULT now(),
   block_num  int NOT NULL,
   tx_id      text NOT NULL,
   contract   text NOT NULL,
   action     text NOT NULL,
   data       jsonb NOT NULL
)

CREATE TABLE receiver_action (
   receiver_id int    REFERENCES receiver
 , action_id   bigint REFERENCES action
 , PRIMARY KEY (receiver_id, action_id)
);

Also note the changed order of columns in table action, saves a couple of bytes per row, which makes a couple of GB for billions of rows.

See:

Calculating and saving space in PostgreSQL
How to implement a many-to-many relationship in PostgreSQL?
Configuring PostgreSQL for read performance

Your common query changes slightly to:

SELECT a.*
FROM   receiver_action ra
JOIN   action a USING (action_id)
WHERE  ra. receiver_id = (SELECT receiver_id FROM receiver WHERE receiver = 'Alpha')
ORDER  BY a.time DESC
LIMIT  100;

Drawback: it's much harder to make your common query fast now. Related:

Can spatial index help a "range - order by - limit" query

The quick (and slightly dirty) fix: include the time column in table receiver_action redundantly (or move it there).

CREATE TABLE receiver_action (
   receiver_id int    REFERENCES receiver
 , action_id   bigint REFERENCES action
 , time        timestamptz NOT NULL DEFAULT now()  -- !
 , PRIMARY KEY (receiver_id, action_id)
);

Create an index:

CREATE INDEX game_changer ON receiver_action (receiver_id, time DESC) INCLUDE (action_id);

INCLUDE requires Postgres 11 or later. See:

Does a query with a primary key and foreign keys run faster than a query with just primary keys?

And use this query:

SELECT a.*
FROM  (
   SELECT action_id
   FROM   receiver_action
   WHERE  receiver_id = (SELECT receiver_id FROM receiver WHERE receiver = 'Alpha')
   ORDER  BY time DESC
   LIMIT  100
   )
JOIN   action a USING (action_id);

Depending on the exact story behind one set of data may create 3 separate rows more may be possible - even 3 separate columns in table action instead of the n:m implementation and a expression GIN index ...
But that's going in too deep. I tap out here.


MySQL:How to correct a corrupted MySQL binary log on Master server? We are using MySQL DB with one master and one slave. We are using this setup for months now. The slave sync stopped today and the error we got was:

Last_IO_Errno: 1236
            Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'log event entry exceeded max_allowed_packet; Increase max_allowed_packet on master'
           Last_SQL_Errno: 1594
           Last_SQL_Error: Relay log read failure: Could not parse relay log event entry. The possible reasons are: the master's binary log is corrupted (you can check this by running 'mysqlbinlog' on the binary log), the slave's relay log is corrupted (you can check this by running 'mysqlbinlog' on the relay log), a network problem, or a bug in the master's or slave's MySQL code. If you want to check the master's binary log or slave's relay log, you will be able to know their names by issuing 'SHOW SLAVE STATUS' on this slave.

We tried restarting slave's mysql service and start and stop the slave replication with no help. After further investigation we found that there was an issue with particularly big size of query. In the error log of the master server we got error log like below:

[ERROR] Error in Log_event::read_log_event(): 'Event too big', data_len: 1936941420, event_type: 109

We have identified the query that caused this issue. We can ignore the update from that query. This error is logged constantly in the mysql error log.

The issue is, the master is not able to read the from the relay log after that perticular log position. We just want to remove that particular relay log number from the binary log of the master server. How can we remove a particular relay log entry from the binary log file? And yes, we have a critical situation with the application due to this.:(

A possible solution that comes to mind is to set the master_log_position to the next one:

change master to master_log_position = <next_pos>;
start slave;

Or just use sql_slave_skip_counter = 1.
If that doesn't work, you may try to parse the binlog (if mysqlbinlog is able to parse it ...):

mysqlbinlog filename > script.sql

Edit the script and remove the part up to and including the long statement.
Run the modified script manually on the slave and set the master_logfile
to the next binlog:

change master to master_log_file ...;
start slave;

You may also check, just in case, if both master and slave use the same *max_allowed_packet*.


Is it free to download an Oracle Enterprise Edition image from Docker? I would like to learn more about Oracle (I am more of an SQL Server developer).  I am thinking about downloading the Docker image: https://hub.docker.com/_/oracle-database-enterprise-edition?tab=reviews
I am a developer and this is strictly for learning purposes.  I have read lots of other similar questions on here like this one: Is Oracle free to download? (dated 2013), however they are many years old.  One of the answers in the question I have posted a link to says: "strictly self-educational purposes".
The reason I am concerned is because before downloading the image you have to supply a company name i.e. it is a mandatory field.  There is no company because this is strictly for learning purposes and the reviewed comments on the Docker webpage are from "verified purchasers". I was thinking about downloading the express edition but some of the features I want to learn about are not available with the express edition.
Can I use the EE image for learning purposes?

Try downloading the official (example?) Docker image from Oracle.
Oracle has put many things on GitHub. Docker images are one of them.
https://github.com/oracle/docker-images/tree/master/OracleDatabase

Licensing questions are off topic for this site. But (AFAIK), for educational purpose only, you can use EE for free.


Unbelievably slow and unusable Query Store I'm beginning with Query Store and it's with problems :-( No report of Top Resource Consuming Queries is ever generated. It just keeps saying "Waiting" for hours and hours. Query on the background (see below for an example) is not able to finish and is consuming lot of CPU. No matter how am I changing configuration options, it keeps waiting and waiting (even last hour report). Is this the reality of Query Store all over? Sounds being such a great tool but is totally unusable in real?
My Query Store size is about 1,7 GB. I'm collecting 7 day behind, in 1 hour interval and Auto Capture mode - so it seems to be reasonable settings, to me.
This is an example of a query on the background which is never finished:
SELECT TOP (@results_row_count)
    p.query_id query_id,
    q.object_id object_id,
    ISNULL(OBJECT_NAME(q.object_id),'') object_name,
    qt.query_sql_text query_sql_text,
    ROUND(CONVERT(float, SUM(rs.avg_duration*rs.count_executions))*0.001,2) total_duration,
    SUM(rs.count_executions) count_executions,
    COUNT(distinct p.plan_id) num_plans
FROM sys.query_store_runtime_stats rs
    JOIN sys.query_store_plan p ON p.plan_id = rs.plan_id
    JOIN sys.query_store_query q ON q.query_id = p.query_id
    JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id
WHERE NOT (rs.first_execution_time > @interval_end_time OR rs.last_execution_time < @interval_start_time)
GROUP BY p.query_id, qt.query_sql_text, q.object_id
HAVING COUNT(distinct p.plan_id) >= 1
ORDER BY total_duration DESC

Any ideas how to make it operational?
EDITORIAL:
I have opened a case with Microsoft about this. We didn't solve it but came to a workaround. If I change Query Store Capture Mode to All (not using Auto mode) - then things go fine and the reports are generated in a timely fashion.
EDITORIAL #2
This has come to a completely different issue. The problem is not the Capture Mode but Legacy Cardinality Estimator which the database is running under.
Please vote for this Azure Feedback: https://feedback.azure.com/forums/908035-sql-server/suggestions/37971781-slow-or-non-operational-query-store-under-legacy-c

I have the same issue, and upon investigating, discovered the root issue in slow Query Store queries is repeated access of the in-memory TVFs. It doesn't matter how many rows are returned from these TVFs, only the number of times they are accessed. The fix (until MS fixes underlying stuff) will be whatever you can do to nudge your plans away from reading them multiple times. Some more information in this related question and in my blog post.

Ultimately, I was able to fix this with a plan guide in production, using hints that I found moved the optimizer away from repeated TVF executions. Since the exact query text is required, and the query rarely finishes, I built a script that would create a plan guide while the report is running by pulling the text from exec_requests. Follow the below steps to create a plan guide.

Open up Top Resource Consuming Queries
Run the below script
while the report is still spinning
Cancel the report and run
again.

--USE YourDB
DECLARE @badqry NVARCHAR(MAX)

SELECT TOP (1) @badqry = SUBSTRING(st.text, (r.statement_start_offset/2) + 1,
    ((CASE statement_end_offset
        WHEN -1 THEN DATALENGTH(st.text)
        ELSE r.statement_end_offset
    END - r.statement_start_offset)/2) + 1)
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) st
WHERE st.text LIKE '(@results_row_count int,@interval_start_time%'

IF @badqry IS NULL
RAISERROR('Missed the plan',16,1)

EXEC sp_create_plan_guide
@name = N'Fixing QueryStore Top Duration',
@stmt = @badqry,
@type = N'SQL',
@module_or_batch = NULL,
@params = N'@results_row_count int,@interval_start_time datetimeoffset(7),@interval_end_time datetimeoffset(7)',
@hints = N'OPTION (HASH JOIN, LOOP JOIN)'

You should be able to run the above script for most of the canned Top Resource queries, but make sure to change the plan guide name if you do. Also, there's certainly a better hint out there than HASH JOIN, LOOP JOIN, but it's worked well enough for me so far.