Can't empty all my database in SQL Server In my SQL Server I tried to truncate all tables, but there have some constraint.

So I running this

EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all" -- Disable all constraints for database
EXEC sp_MSForEachTable 'TRUNCATE TABLE ?'

but still I get error as

  cant truncate table ... because it is being reference by a FK constraint

Why?

I've had a similar issue to this before, the issue is that you need to fully drop the FK constraints rather than disabling them. I used this code to convert non clustered primary keys to clustered ones but it'll work for this too (I've commented out the EXEC statements because somebody's going to mess up a system if I leave them in, try this as is first and then check the messages tab to see what it would actually do before running it in anger);

First query is going to put all of our Foreign Key information into a temporary table;

SELECT
     o.name [ReferencedTable]
    ,OBJECT_NAME(fkc.parent_object_id) [FKTable]
    ,c.name [FKColumnName]
    ,fk.name [FKName]
INTO #FKAgainstTableList
FROM sys.foreign_keys fk
INNER JOIN sys.foreign_key_columns fkc ON fk.object_id = fkc.constraint_object_id
INNER JOIN sys.columns c ON c.object_id = fk.parent_object_id
    AND c.column_id = fkc.parent_column_id
INNER JOIN sys.objects o ON fkc.referenced_object_id = o.object_id

This is fine, but what if we've got foreign keys that reference more than one column? Let's allow for that by making a new table, it makes sense to put in our square brackets while we're doing this;

SELECT DISTINCT
     '[' + a.ReferencedTable + ']' ReferencedTable
    ,'[' + a.[FKTable] + ']' FKTableName
    ,STUFF([ColumnName],1,1,'') FKColumnName
    ,'[' + a.[FKName] + ']' FKName
INTO #SanitisedFKList
FROM #FKAgainstTableList a
CROSS APPLY
(
    SELECT ',[' + b.FKColumnName + ']'
    FROM #FKAgainstTableList b
    WHERE a.FKName = b.FKName
    FOR XML PATH ('')
) sub (ColumnName)

We'll need some variables for the cursors coming up;

DECLARE @sql varchar(max)
DECLARE @ReferencedTable varchar(max)
DECLARE @table varchar(max)
DECLARE @FKColumnName varchar(max)
DECLARE @FKName varchar(max)

Now let's make a cursor to drop all of these Foreign Keys (i like to keep the PRINT statements so I can see what's happening in the messages tab);

DECLARE cursor1 CURSOR  FOR
SELECT * FROM #SanitisedFKList

OPEN cursor1
FETCH NEXT FROM cursor1 INTO @ReferencedTable,@table,@FKColumnName,@FKName
WHILE   @@FETCH_STATUS = 0
    BEGIN
        SET @sql ='ALTER TABLE '+@table+' DROP CONSTRAINT '+ @FKName
        PRINT @sql
        --EXEC(@sql)
        FETCH NEXT FROM cursor1 INTO @ReferencedTable,@table,@FKColumnName,@FKName
    END
CLOSE cursor1
DEALLOCATE cursor1

Then you can do your original truncate table statements without worrying about those FK's.

When you want to reapply them you'll need that temp table and do something like this;

DECLARE cursor2 CURSOR  FOR
SELECT  * FROM #SanitisedFKList
OPEN cursor2
FETCH NEXT FROM cursor2 INTO @ReferencedTable,@table,@FKColumnName,@FKName
WHILE   @@FETCH_STATUS = 0
    BEGIN
        SET @sql = 'ALTER TABLE '+@table+' WITH NOCHECK ADD  CONSTRAINT  '+ @FKName+' FOREIGN KEY('+@FKColumnName+')
        REFERENCES '+@ReferencedTable+' ('+@FKColumnName+')'
        PRINT(@sql)
        --EXEC(@sql)
        SET @sql = 'ALTER TABLE '+@table+' CHECK CONSTRAINT  '+@FKName
        PRINT(@sql)
        --EXEC(@sql)

        FETCH NEXT FROM cursor2 INTO @ReferencedTable,@table,@FKColumnName,@FKName
    END
CLOSE cursor2
DEALLOCATE cursor2


Single statement or batched delete? I need to delete 6.3 million records from 25 tables in production. I have the following two queries, but I don't know which one will be best:

delete jc
from Jtable jc
join J1Table j
on j.JobId = jc.JobId
join nonJobReProcess n
on n.jobid = j.JobId

or:

while exists (select top 1 * from dbo.Jtable jc (nolock)
join J1Table j (nolock)
on j.JobId = jc.JobId
join nonJobReProcess  n  (nolock)
on n.jobid = j.JobId)
begin

delete  top (10000) jc
from dbo.Jtable jc (nolock)
join J1Table j (nolock)
on j.JobId = jc.JobId
join nonJobReProcess n  (nolock)
on n.jobid = j.JobId

end

Please explain the reasons I might choose one over the other, or any alternative methods.

Using the second method will result in less locking, transaction log usage and Tempdb resources, because SQL Server doesn't have to perform all of the delete operations in one transaction.

However, instead of the "while exists" statement, you can use a variable that will get the value of @@rowcount after the delete. This can help a little more with performance.


Load Balancing with SQL Server What exists today to do Microsoft SQL Server Load Balancing on Windows Server and being transparent to any programmatic access. Is this built in or does a solution have to be purchased?

I'm trying to find out if there is a solution today that makes this very easy from the view point of the web servers or other database access to SQL Server.

There is no "standard" load balancing set up for MS SQL Server that you can run via a wizard.

This would be a database architecture decision and implemented at the database level not the server level. Techniques would be:

Scaling out/federating the database servers
Partitioning
Offload your reporting requirements
Perhaps replication

If anyone disagrees, then I'd like to see an article by a respected known MS SQL figure saying clustering is load balancing. The articles quoted above do not mention load balancing. For example, A Microsoftie (Chas Boyd) says it is not here.

My question to the OP would be what kind of load do you expect?

Database servers are usually IO and memory bound, so proper disk configuration (with appropriate filegroups) and as much RAM as possible will go a lot further than any solution above.

Don't forget: SQL Server 2005/Windows 2003 Enterprise 32-bit goes to 32GB RAM (of which you'd have 26-28GB data cache) and you are not limited by drive letters because of NTFS mount points. As for x64...


Postgresql replication slot used by barman has a long replay_lag I set up barman using wal streaming, I just notice I have a hug replay_lag. I'd like to keep it down to 0 but have no idea about how to do this.

I also have a database replica and it is working fine.

I have barman 2.7 and postgresl 10.8

When I check the postgresql replication status I found this:

select * from pg_stat_replication;

-[ RECORD 1 ]----+------------------------------
pid              | 23095
usesysid         | 169593
usename          | repmgr
application_name | replication_server
client_addr      | xxx.16.2.66
client_hostname  | replica_server
client_port      | 51164
backend_start    | 2019-07-05 23:03:03.194165-05
backend_xmin     |
state            | streaming
sent_lsn         | 22/30884870
write_lsn        | 22/30884870
flush_lsn        | 22/30884870
replay_lsn       | 22/30884870
write_lag        |
flush_lag        |
replay_lag       |
sync_priority    | 0
sync_state       | async
-[ RECORD 2 ]----+------------------------------
pid              | 6689
usesysid         | 66019
usename          | streaming_barman
application_name | barman_receive_wal
client_addr      | xxx.172.16.109
client_hostname  | barman_server
client_port      | 40680
backend_start    | 2019-07-16 00:00:06.903489-05
backend_xmin     |
state            | streaming
sent_lsn         | 22/30884870
write_lsn        | 22/30884870
flush_lsn        | 22/30000000
replay_lsn       |
write_lag        | 00:00:03.01204
flush_lag        | 00:00:01.085313
replay_lag       | 583:31:21.110173
sync_priority    | 0
sync_state       | async

select * from pg_replication_slots ;

-[ RECORD 1 ]-------+----------------
slot_name           | barman_slot
plugin              |
slot_type           | physical
datoid              |
database            |
temporary           | f
active              | t
active_pid          | 6689
xmin                |
catalog_xmin        |
restart_lsn         | 22/30000000
confirmed_flush_lsn |

barman status master_server

Server master_server:
        Description: master_server - streaming
        Active: True
        Disabled: False
        PostgreSQL version: 10.8
        Cluster state: in production
        pgespresso extension: Not available
        Current data size: 11.4 GiB
        PostgreSQL Data directory: /var/lib/postgresql/10/main
        Current WAL segment: 000000010000002200000030
        PostgreSQL 'archive_command' setting: barman-wal-archive barman_server master_server %p
        Last archived WAL: 00000001000000220000002F, at Fri Aug  9 06:56:05 2019
        Failures of WAL archiver: 63 (000000010000001C00000062 at Mon Jul 15 23:59:21 2019)
        Server WAL archiving rate: 2.60/hour
        Passive node: False
        Retention policies: enforced (mode: auto, retention: RECOVERY WINDOW OF 1 MONTHS, WAL retention: MAIN)
        No. of available backups: 1
        First available backup: 20190809T063454
        Last available backup: 20190809T063454
        Minimum redundancy requirements: satisfied (1/1)

Can anyone point me to a resource I can check to figure out how to fix this Failures of WAL archiver and replay_lag?

Thanks in advance

This large replay lag in pg_stat_replication seems to belong to a pg_receivewal process. Now pg_receivewal writes a copy of the WAL files, but it does not apply them anywhere. Consequently, it will not report back to the primary server that WAL was applied.

This is perfectly normal; compare commit fd7d387e05.

The “Failures of WAL archiver” are also nothing you have to worry about. This number probably comes from pg_stat_archiver and indicates that there has been a problem archiving WALs in the past. This problem must have been resolved, because “Last archived WAL” indicates that more recent WAL files have been archived successfully.

PostgreSQL won't skip archiving WALs — if archiving fails, the archiver gets stuck at that place and retries until successful.


Postgresql doesn't accept replication connection Plain old streaming replication.
PostgreSQL: 9.2.7
Windows 8.1 64 bit

My primary and secondary clusters are on same windows machine. I have already done pg_start_backup() and everything, so both nodes have exact same data.

Now the problem with replication is "replication connection" from slave server do not connect to primary server but I can connect using same params using psql shell. What I think culprit is the connection string in slave's recovery.conf:

primary_conninfo = 'host = 127.0.0.1 port = 5432 user = postgres password = postgres'

I tried localhost, 0.0.0.0, lan IP everything but pg log says:

 FATAL:  could not connect to the primary server: FATAL:  no pg_hba.conf entry for replication connection from host "127.0.0.1", user "postgres", SSL off

Now look at my Master's pg_hba.conf:

host     all     all     0.0.0.0/0   trust
host    all             postgres             127.0.0.1/0               trust
# IPv6 local connections:
host    all             all             ::1/128                 md5
hostnossl    all     postgres    127.0.0.1/32    trust
# Allow replication connections from localhost, by a user with the
# replication privilege.
#host    replication     postgres        127.0.0.1/32            md5
#host    replication     postgres        ::1/128                 md5

It like I allowed every possible connection yet slave can't connect. Can you do any help?

The database name has to be replication as all does not cover replication connections.

host    replication    postgres             127.0.0.1/0               trust

The PostgreSQL documentation further says:

  The value replication specifies that the record matches if a
  replication connection is requested (note that replication connections
  do not specify any particular database). Otherwise, this is the name
  of a specific PostgreSQL database. Multiple database names can be
  supplied by separating them with commas. A separate file containing
  database names can be specified by preceding the file name with @.


Native Sybase ODBC driver I did install latest Sybase 16 development version on my Linux box. During the installation I did select to install ODBC driver.

Now looking at the directory I see this:

igor@IgorReinCloud /opt $ find . -name *odbc*
./sap/DataAccess/bin/odbcversion
./sap/DataAccess/ODBC/samples/odbcbatch
./sap/DataAccess/ODBC/samples/odbcbatch/odbcbatch.cpp
./sap/DataAccess/ODBC/samples/odbcloblocator
./sap/DataAccess/ODBC/samples/odbcloblocator/odbcloblocator.cpp
./sap/DataAccess/ODBC/sp/odbc_mda_120.sql
./sap/DataAccess/ODBC/sp/odbc_mda_157.sql
./sap/DataAccess/ODBC/sp/odbc_mda_1251.sql
./sap/DataAccess/ODBC/sp/odbc_mda_1600000.sql
./sap/DataAccess/ODBC/sp/install_odbc_sprocs
./sap/DataAccess/ODBC/sp/odbc_mda_1570020.sql
./sap/DataAccess/ODBC/sp/odbc_mda_125.sql
./sap/DataAccess/ODBC/sp/odbc_mda_12503.sql
./sap/DataAccess/ODBC/sp/odbc_mda_150.sql
./sap/DataAccess/ODBC/sp/odbc_mda_1502.sql
./sap/DBISQL-16_0/lib/jodbc4.jar
./sap/DBISQL-16_0/bin/libdbodbc16_r.so.1
./sap/DBISQL-16_0/bin/libdbodbc16_r.so
./sap/DBISQL-16_0/bin/libdbjodbc16.so.1
./sap/DBISQL-16_0/bin/libdbjodbc16.so
./sap/ASE-16_0/lib/pca/libPCAodbc.so
./sap/ASE-16_0/symlib/pca/libPCAodbc.so
./sap/COCKPIT-4/bin/sa16/linux64/java/jodbc4.jar
./sap/COCKPIT-4/bin/sa16/linux64/lib64/libdbodbc16_r.so
./sap/COCKPIT-4/bin/sa16/linux64/lib64/libdbodbc16.so
./sap/COCKPIT-4/bin/sa16/linux64/lib64/libdbjodbc16.so
./sap/log/odbc.log
./sap/log/odbc.lst
./sap/DataAccess64/bin/odbcversion
./sap/DataAccess64/ODBC/dm/include/unixodbc.h
./sap/DataAccess64/ODBC/dm/include/odbc.h
./sap/DataAccess64/ODBC/dm/include/saodbc.h
./sap/DataAccess64/ODBC/dm/lib64/libodbc.so
./sap/DataAccess64/ODBC/samples/odbcbatch
./sap/DataAccess64/ODBC/samples/odbcbatch/odbcbatch.cpp
./sap/DataAccess64/ODBC/samples/odbcloblocator
./sap/DataAccess64/ODBC/samples/odbcloblocator/odbcloblocator.cpp
./sap/DataAccess64/ODBC/sp/odbc_mda_120.sql
./sap/DataAccess64/ODBC/sp/odbc_mda_157.sql
./sap/DataAccess64/ODBC/sp/odbc_mda_1251.sql
./sap/DataAccess64/ODBC/sp/odbc_mda_1600000.sql
./sap/DataAccess64/ODBC/sp/install_odbc_sprocs
./sap/DataAccess64/ODBC/sp/odbc_mda_1570020.sql
./sap/DataAccess64/ODBC/sp/odbc_mda_125.sql
./sap/DataAccess64/ODBC/sp/odbc_mda_12503.sql
./sap/DataAccess64/ODBC/sp/odbc_mda_150.sql
./sap/DataAccess64/ODBC/sp/odbc_mda_1502.sql

And so for the driver I am looking for:

./sap/DBISQL-16_0/bin/libdbodbc16_r.so.1
./sap/DBISQL-16_0/bin/libdbodbc16_r.so

those 2 files, correct?

So when configuring, I will put in odbcinst.ini:

[Sybase]
Driver=/opt/sap/DBISQL-16_0/bin/libdbodbc16_r.so

right?

Thank you.

P.S.: Just curious, what is "_r" means in the file name of the library? It is not the first time I see it...

You don't actually have to specify the ODBC driver as long as the driver is in the library path.

[MyIQboxOfDoom]
CommLinks=tcpip(ip=myiqboxofdoom;port=5000;DOBROADCAST=NONE;VERIFY=NO)
AutoStop=no

and connect using

dbisql -c "dsn=MyIQboxOfDoom;uid=myuser;pwd=mypass" -nogui


Postgresql 9.4 high availability I currently have a master postgresql 9.4 server containing many databases on Ubuntu 14.04.

I tried to use Barman to setup backup, but would like to set up streaming replica so that when the master goes down, I can promote the standby replica to be the new master. I looked in to repmgr, but this will only produce a read-only hot standby server. Does this mean I should go with multi-master solution if I want to promote standby to accept write transaction as well?

I am envision the system to be

 M1 (master)  -------------via ???-------------- S1 (standby/another master)
   |                                                 |
   |                                                 |
   |via Barman                                       | via Barman
   |                                                 |
 B1 (backup server#1)                            B2 (backup server#2)

Ideally, after the M1 goes down, I can promote S1 to be the new master and it can accept read/write transaction not just read-only. Once we bring back M1, M1 can stays as standby but will streaming with S1.

Is postgres 9.4 BDR (Bi-Directional Replication) a good solution to stream between M1 and S1? Or is there any commercial product can do this?

I am not a DBA, and would really appreciate your opinions.

Thank you so much!

i use http://en.wikipedia.org/wiki/Corosync_%28project%29 and peacemaker.
maybe http://clusterlabs.org/wiki/PgSQL_Replicated_Cluster can help you.


How are databases able to process queries in parallel while still retaining correct order? How do databases process queries in parallel (more than one query at a time) while still keeping the correct order (queries are performed in the same order that they are submitted)? Is there a kind of queue structure? thanks.

Answers relevant to SQL Server.

If talking about query priority:

For SQL Server there is by default a FIFO principle adhered to. The first query to run gets priority.
However with the resource governor it is possible to not only limit the resources assigned to your sql server instances, it is also possible to create workload groups which can prioritize certain connections to be able to run before others.

If talking about parallelism:

For SQL Server it does this by running parallel streams, that are fed information by a parallel page supplier, and in their turn have their information fed through a gather streams operator into a stream aggregate operator, which gathers the information provided by the separate streams to then be passed on further.

In other words, for SQL Server this is done by gathering the processed data at the end, and bundling it a new. If needed they can then be sorted or filtered afterwards.

See this simple talk article for more details.


Can't create view using a role I am learning Oracle and have to do roles. I created an c##admin from a sys, granted him all grants. Then I created a c##manager_role and c##manager. I granted this role to c##manager. After that I started to grant privileges to c##manager_role. In addition I granted the following:
CREATE VIEW                              YES YES NO

CREATE ANY VIEW                          NO  NO  NO

DROP ANY VIEW                            YES YES NO

Above output from:
select * from role_sys_privs where ROLE = 'C##MANAGER_ROLE' order by 1;

Now, I can't understand, why CREATE ANY VIEW has 3 "NO" and why I can't create any view from c##manager.
From c##admin:
grant CREATE ANY VIEW TO c##manager_role with admin option CONTAINER=all;

From c##manager:
create view friends_v4 as select t1.nickname as "USER", t2.nickname as "FRIEND" from c##admin.clients t1, c##admin.clients t2, c##admin.friends where (t1.id = c##admin.friends.user_id and t2.id = c##admin.friends.friend_id);

Error:

Error report -
ORA-01031: привилегий недостаточно
01031. 00000 -  "insufficient privileges"
*Cause:    An attempt was made to perform a database operation without
the necessary privileges.
*Action:   Ask your database administrator or designated security
administrator to grant you the necessary privileges

All tables noted exist.

Being able to create a view is not [quite] enough.
The Owner [account] of the finished View must have read privileges on the Tables used by that view.  These privileges cannot be conferred through any Role.
grant read on friends to owner_of_friends_v4 ;
grant read on clients to owner_of_friends_v4 ;

Also, why are you working with Common users here (starting "C##")?
I would only expect to see these in the Container database, not any of the Pluggable ones that live within it (which is where I would expect to see all Application Tables and Data).


How does the spec store JSON on a table? From this question, I quoted the docs,

  "MariaDB implements this as a LONGTEXT rather, as the JSON data type contradicts the SQL standard, and MariaDB's benchmarks indicate that performance is at least equivalent."*

That spurred a comment

  I had to google and it appears that MariaDB now supports CHECK constraints. Not sure what they mean when they say that JSON contradicts the SQL standard? – Lennart yesterday

I want to know what they meant too. So what does MariaDB mean when say "contradicts the standard."

SQL:2016

This is from my own read of ISO/IEC TR 19075-6:2017: SQL support for JavaScript Object Notation (JSON)

Ignoring both (a) the track record of MySQL with regard to the spec, and (b) the fact that they're now implementing a JSON type. It seems as if they're referring to this,

  A <JSON value expression> may have an optional <JSON input clause>. This indicates that the values expression should be parsed as JSON. The standardized option is FORMAT JSON; implementations may also
  support syntax such as FORMAT AVRO or FORMAT BSON. When using the <JSON input clause>, the <value expression> may be either a character string or a binary string.

Essentially, the spec provides for JSON values to come from strings, or binary blobs. The method of making them JSON is done through a decorator which identifies it and its FORMATs. The spec explicitly mentions "implementation-defined JSON representation option" it gives BSON and Apache AVRO as examples. It would look something like this (my read of the spec)

SELECT str FORMAT JSON BSON;

From there the value construction is accessible with the "SQL/JSON data model" and the rest of the spec applies to it. However there is no binary storage mechanism, or stipulation that one must be implemented for SQL/JSON. Implementing one doesn't seem to contradict the spec. In fact it goes on to say,

  Constructor functions use values of SQL types and produce JSON values
  (JSON objects or JSON arrays) represented in SQL character or binary string types.

So they can be represented as a binary type,

  Query functions evaluate SQL/JSON path language expressions against JSON values, producing values of SQL/JSON types, which are converted to SQL types.

And SQL queries need to return "SQL types", regardless of if there is a named binary type under the hood. Most databases do that though they return character/varchar/text representations of JSON.

The spec's only example of storing JSON on a table uses strings,

CREATE TABLE employees (
emp_id INTEGER,
name CHARACTER VARYING(50),
salary DECIMAL(7,2),
dept_id INTEGER,
json_emp CHARACTER VARYING(5000) )

That essentially means the it's serialized to and parsed according to RFC7159. It's also somewhat amusing that the retrieval of json_emp is missing from the spec.

tldr: I don't think anything about implementing a binary type "contradicts" the spec, as it is explicitly permitted. I think the right word is "complements."


Why does SQL Server Profiler show some completed statements in pairs (twice)? In SQL Server profiler I am seeing pairs of completed statements. A pair is shown below. The TextData (query) is exactly the same. The SPIDs are different (86 & 88). The # of reads and writes are a little different.
(as an FYI, I am running a custom template where I am filtering out exec sp_reset_connection statements and showing just the completed statements).
I am seeing a bunch of these pairs of SQL:StmtCompleted and RPC:Completed and every pair contains the same SQL in TextData.
The statement shown was executed once by Entity Framework in the client.
My question: Is SQL Server actually running the queries twice? If yes, does this mean definitely the client is sending the query twice?

My question: Is SQL Server actually running the queries twice?

Yep!  Given that the SPIDs are different, that means they were run from completely different connections.  The fact that the duration and reads are different, as you mentioned, is also a good indicator that the query was run twice (possibly with different parameters).

If yes, does this mean definitely the client is sending the query twice?

I would say yes.  You should look into your application code.  Maybe more than one user was accessing the system at the same time, or the query is being run from multiple threads, or something else.
Since this is EF, I might think this was the N+1 query problem (disclosure: that's my blog), but that would normally be all on the same SPID.


Oracle Client Silent Install Fails I'm creating an automated package in PDQ Deploy which installs the Oracle client, and some patches. Step 1 copies the staging files to the target, which completes successfully. Step 2 attempts to install the Oracle Client silently, and hangs indefinitely.

These are the commands I'm using for the silent install, any idea what's going wrong here? I'm using a template that I found here. The Oracle client version is 11.2.0.4.0

cd C:\PDQStaging\Oracle\64bit\client\
.\setup.exe -silent -debug -force -waitforcompletion
FROM_LOCATION=C:\PDQStaging\Oracle\64bit\client\stage\products.xml
oracle.install.client.installType=”Administrator”
ORACLE_HOME=”C:\Oracle\64bit\product\11.2.0\Client”
ORACLE_HOME_NAME=”OraClient11g_Home1″
ORACLE_BASE=”C:\Oracle”
DECLINE_SECURITY_UPDATES=true

I wanted to avoid using a response file since the above seems like a simpler solution.

I fixed it using a response file, after all.

C:\PDQStaging\Oracle\64bit\client\setup.exe -silent -nowelcome -nowait -responseFile C:\PDQStaging\Oracle\64bit\client_install.rsp


MariaDB Galera + Remote Backup I'm using CentOS 8 and I have a MariaDB 10.5.4 Galera set up with three nodes. Now, I want it to create a backup to my central server from time to time. I'm thinking of mysqldump but restoration would take a lot of time and mariabackup does not support remote backups (I think). So I'm thinking of creating a Master-Slave to from my galera cluster setup to a stand-alone setup on my central server then I run mariabackup from there like this:

For now my current set up is that I had my third node act as a backup for HAProxy and it is where mariabackup is currently running (see image and remove central server). But my boss wanted me to store backup on central and let central do the backup instead of the database server.
Are there any simple alternative? I'm thinking of following this guide, but it's a bit outdated and I'm not sure if it would work. Thanks

Central backups occurring on a replica is a decent strategy.
The guide on quick glance looks ok. Ensure that your server-id is unique. Per mariadb galera guidance galera cluster members shoudl have the same server-id in most scenarios.. Its unclear why binlog-format=mixed when galera requires row so I'd just keep row.
'log-slave-updates' is the critical thing of the master node but for consistency its easy enough to have this on all galera nodes in case you need to change master at some point.
Like all backups consider the process that a restoration would take and business requirements on MTTR (mean time to recover). And test the restoration process when you've got backup to see it works and how long it takes.


SQL Server 2016 Always On ReadOnly Routing List I have an application that is about 60% write and 40% read. I have configured a Read Only Routing List for SQL 2016 Always On Availability Group based on the Microsoft's guide. here: Configure Read-Only Routing for an Availability Group

When I tried testing it using SQLCMD, I get the error below:
 C:\Users\administrator.IMGT>sqlcmd -S IMGT-AVG,5022 -E -d IMGT_DB_Prod -K ReadOnly

Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : Client unable
to establish connection because an error was encountered during
handshakes before login. Common causes include client attempting to
connect to an unsupported version of SQL Server, server too busy to
accept new connections or a resource limitation ( memory or maximum
allowed connections) on the server..
Sqlcmd: Error: Microsoft ODBC
Driver 13 for SQL Server : TCP Provider: An existing connection was
forcibly closed by the remote host. .
Sqlcmd: Error: Microsoft ODBC
Driver 13 for SQL Server : Client unable to establish connection.
Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : Client unable
to establish connection due to prelogin failure.

How else can I test if ReadOnly Routing List is working?

Since my application is about 60% WRITE and 40% READ, do i need to specify application intent in my Connection String?

Any guide on how to test this will be appreciated.

Port 5022 is the endpoint that is used for mirroring, not for client connections.  You just need to connect to the listener on same port as usual (1433 if using the default), and the availability group will route the read-only connection to the secondary.

See Connecting to ReadOnly Secondary Replica of SQL Server via the Listener by Default for further explanation.

To test a read-only connection with SQL Server Management Studio, select Options, and on the Additional Connection Parameters tab enter "ApplicationIntent=ReadOnly").  Then use a SELECT @@SERVERNAME query to confirm the connection is routed to the secondary.


Suppressing the removal of leading zeros in numeric data I initially declared the data type of the columns for some of the tables I am working on as NUMERIC. Because of that, leading zeros are being removed.  Is there anyway to suppress this behavior?  I need to preserve the leading zeros, since 08 should be considered different from 0000008.

One solution is to changing the definition of the table and changing the numeric to varchar datatype.

Is there any other way to achieve this without changing the table structure?

Retaining leading zeros for numeric data types is not possible.  Since you appear to be using this column as a non-numeric identifier, you should consider using a varchar column of appropriate length.

If the maximum length you're willing to accept in the table is 11 characters, and the value is always required, I would define the column like this:

someCol varchar(11) NOT NULL

The column will then differentiate between 08 and 000008, and the leading zeros will be retained.


best relationship for this design? There is a table users with autoincrement primary key id.

There is also a table employees (every employee is a user but not vice versa). The employees primary key is userid. The field userid is not autoincrement.

employees.userid should be a foreign key for users.id.

Which kind of relationship in Workbench it should be?

As far as I understand:

It cannot be 1:1 relationships because for a given user the corresponding employee may not exist.
It is not advisable to be 1:n relationship because n can be only 0 or 1.
It should be an identifying relation because a user is determined by an employee.

Please advise me what is the best relationship in this case.

I think it would be best that you learned about relationship types without using an IDE.

If every employee is a user, then it should be a straight 1:1 relationship.

If every employee is not a user, then you should not be using userid as the primary key. Rather, create a surrogate primary key for the employee and make userid a nullable foreign key.


Advice on setting up a Reporting server I'd like to ask advice on setting up a reporting server please.

We have a SQL Server (on a VM) that handles operations. (SQLOps)
This primary SQL server instance has a secondary Log Shipping instance (in another city)

We have a second VM now idle, which I have to set up as a Reporting server.

I have set up log shipping before in my life, but I have no idea how to approach the Reporting server now.

How do I set up the new SQL Server instance (SQLReporting).

How do I get to update the data in the 2nd SQL Server instance?
Can I send through updates every 15 minutes, like with Log shipping?

I never want data from SQL Reporting to update the original SQLOps.

Are snapshots the only option?

If I understand you properly - you want to have a second reporting instance and you are asking how you can keep that updated.

It also appears you are on SQL Server 2005 so your options are limited more so than, say, if you were on SQL Server 2012 with Availability Groups.

Your options are really going to be in the spirit of:

Log Shipping
And here you can actually restore your logs in WITH STANDBY mode - this means that you can effectively query your log shipped database for read only queries anytime. You can read more about that here and at the related links on that article. You cannot add indexes, add statistics, do any writes, etc. But you can avoid creating a snapshot. But you still require the blip of connectivity when the standby restore happens - so you haven't saved much.

Mirroring
Same idea of log shipping (at a very basic level that is) - and here you can read off of a standby. Still ned that downtime of the reporting users to rebuild your snapshot. You can do A/B switching and effectively abstract some of that through views, content switching/load balancing but you still need a downtime for the snapshot change.

Replication
You can use transactional replication and replicate data. This is near real time and updates just happen as they happen on the primary and get replicated (or you can control the frequency). This has more of an overhead on the source, latency really matters, requires a bit more DBA overhead, but it allows you to report on the secondary with no need to worry about standby restores, or new snapshots. Benefit here is you can choose what comes across and what doesn't if the reports only care about certain tables.

ETL
You could always roll your own process to extract, transform and load just the data you care about. This way you can create a reporting optimized version of the database, index it appropriately and stream data in at the frequency you want. You need to worry about concurrency and blocking risk during your loads if it needs to be updated throughout the day, but there are ways of dealing with that. More overhead and developer time required.

And I would really encourage you to think about SQL Server 2012/2014 - AlwaysOn Availability Group Active Secondaries are a benefit here potentially. The main benefit being the read only secondary database. There are some optimizations here including the ability to have temporary statistics created and stored in the TempDB database on the secondary instance. The read only doesn't require snapshots, with standby, etc. It is truly read only - so you cannot write into it - but for reporting this can be a big win. Granted the move from 2005 to 2012 is a bigger conversation, requires licensing conversations, etc. but it is an option here for sure.

If you are talking about HA  for SSRS that is a separate conversation.


How do I model a many-to-many relationship on two different columns? I am trying to understand how to model a certain type of relationship.

I have a dataset that I would like to put into a database, however, I have this relationship among two columns in my data.

Here is a sample of what I am looking at:

Company_ID1  Company_ID2
      9581091  53506312
      9581091  961273620
     79735371  53506312
     79735371  79735371
     79735371  135962137
     46667523  9122532
     55189732  9122532
     71453880  9122532
     77817617  9122532
     77817617  79834910
     79871820  9122532
     79871820  79834910
     98158277  9122532
     98158277  458182615
    134303192  9122532
    187502299  458182615

As you can see company_id2=53506312 is associated with two different company_id1's. This is just one example, but there is a many-to-many relationship between the two company_Id's. By inspection, this example is the same company.

How would I model this situation so that the database finds this kind of association and is able to group them all together?

The motivation behind this is finding sales and contract by a company. So grouping by either company_id will result in the wrong answer.

How I have solved this with text files, is I have a graph where all the nodes are the unique company_id's and the edges is the tuple (company_id1, company_id2).  Then I find all disconnected subgraphs in the graph and each ones of those is a company.

So I have something like this:

Company_ID1  Company_ID2      company_number
          9581091  53506312         1
          9581091  961273620        1
         79735371  53506312         1
         79735371  79735371         1
         79735371  135962137        1
         46667523  9122532          2
        55189732   9122532          2
        71453880   9122532          2
        77817617   9122532          2
        77817617   79834910         2
        79871820   9122532          2
        79871820   79834910         2
        98158277   9122532          2
        98158277   458182615        2
        134303192  9122532          2
        187502299  458182615        2

So then I groupby company and I can do summary reports.

But I would like to now move to a database and have the database do this calculation for me. So, two questions. What kind of relationship is this called, and how do I model it in my database?

One last think, these two columns company_id1 and company_id2 aren't the primary keys.

You have asked: What kind of relationship is this called, and how do I model it in my database?

Your scenario seems to contain a recursive many-to-many relationship: EACH company1 may have (optional) ONE OR MORE associations with another company2. Each company2 may have one or more associations with a company1.  ("company1" and "company2" are both: companies.  I have only given them these names so that the relationship's "directions" become obvious) The traditional way of "resolving" a many-to-many relationship is: creating an intersection/junction.

ERD (entity relationship diagram)

Intersection in relational model:

Doing a "fast forward" to the actual implementation, maybe the following will be useful for you:

1 - for recording all NODES in your existing model: have one table for recording unique company ids, use PK constraint.

2 - for all EDGES in your existing model: have another table for recording VALID combinations. Use 2 foreign key constraints (only existing company ids are allowed), and a PK constraint that guarantees the uniqueness of the recorded combinations. (You can also use a CHECK constraint, in order to avoid associations of a company with itself.  However, your test data values , line 4: 79735371  79735371, suggest that a node can be associated with itself.)

Example implementation (Oracle 12c):

-- ~ nodes in graph, uniqueness through pk constraint ~
create table companies(
  id number primary key
);

-- insert unique values ~ nodes ~
begin
  insert into companies (id) values (9581091);
  insert into companies (id) values (79735371);
  insert into companies (id) values (53506312);
  insert into companies (id) values (961273620);
  insert into companies (id) values (135962137);
end;

--- ~ edges in graph ~
create table associations(
  co1 number references companies(id)
, co2 number references companies(id)
);

alter table associations
add constraint apk
primary key (co1, co2);

testing

-- use your test data
begin
  insert into associations values (9581091, 53506312);
  insert into associations values (9581091, 961273620);
  insert into associations values (79735371, 53506312);
  insert into associations values (79735371, 79735371);
  insert into associations values (79735371, 135962137);
end;

-- Optional: testing the intersection/junction by inserting all possible combinations:

insert into associations
select
  c1.id
, c2.id
from companies c1, companies c2;


PROPOSED QUERY

SELECT
    A.id,
    IFNULL(GROUP_CONCAT(DISTINCT B.cnt_id),'') 'manager',
    IFNULL(GROUP_CONCAT(DISTINCT C.cnt_id),'') 'supervisor',
    IFNULL(GROUP_CONCAT(DISTINCT D.cnt_id),'') 'teamlead'
FROM apps A
LEFT JOIN contacts B ON A.id = B.id AND B.cnt_type='manager'
LEFT JOIN contacts C ON A.id = C.id AND C.cnt_type='supervisor'
LEFT JOIN contacts D ON A.id = D.id AND D.cnt_type='teamlead'
WHERE ISNULL(B.cnt_id)+ISNULL(C.cnt_id)+ISNULL(D.cnt_id) < 3
GROUP BY A.id;

SAMPLE DATA

DROP DATABASE IF EXISTS kumar;
CREATE DATABASE kumar;
USE kumar
CREATE TABLE apps
(
    id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(20) NOT NULL,
    PRIMARY KEY (id)
);
CREATE TABLE contacts
(
    id INT NOT NULL,
    cnt_type VARCHAR(20) NOT NULL,
    cnt_id INT NOT NULL
);
INSERT INTO apps (name) VALUES ('abc'),('sdf'),('dfg');
INSERT INTO contacts (id,cnt_type,cnt_id) VALUES
(1,'manager',20), (1,'supervisor',32), (1,'teamlead',45),
(1,'teamlead',54), (2,'manager',20), (2,'supervisor',32);

SAMPLE DATA LOADED

mysql> DROP DATABASE IF EXISTS kumar;
Query OK, 2 rows affected (0.05 sec)

mysql> CREATE DATABASE kumar;
Query OK, 1 row affected (0.00 sec)

mysql> USE kumar
Database changed
mysql> CREATE TABLE apps
    -> (
    ->     id INT NOT NULL AUTO_INCREMENT,
    ->     name VARCHAR(20) NOT NULL,
    ->     PRIMARY KEY (id)
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> CREATE TABLE contacts
    -> (
    ->     id INT NOT NULL,
    ->     cnt_type VARCHAR(20) NOT NULL,
    ->     cnt_id INT NOT NULL
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO apps (name) VALUES ('abc'),('sdf'),('dfg');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> INSERT INTO contacts (id,cnt_type,cnt_id) VALUES
    -> (1,'manager',20), (1,'supervisor',32), (1,'teamlead',45),
    -> (1,'teamlead',54), (2,'manager',20), (2,'supervisor',32);
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql>

SAMPLE DATA IN TABLES

mysql> select * from apps;
+----+------+
| id | name |
+----+------+
|  1 | abc  |
|  2 | sdf  |
|  3 | dfg  |
+----+------+
3 rows in set (0.00 sec)

mysql> select * from contacts;
+----+------------+--------+
| id | cnt_type   | cnt_id |
+----+------------+--------+
|  1 | manager    |     20 |
|  1 | supervisor |     32 |
|  1 | teamlead   |     45 |
|  1 | teamlead   |     54 |
|  2 | manager    |     20 |
|  2 | supervisor |     32 |
+----+------------+--------+
6 rows in set (0.00 sec)

mysql>

PROPOSED QUERY EXECUTED

mysql> SELECT
    ->     A.id,
    ->     IFNULL(GROUP_CONCAT(DISTINCT B.cnt_id),'') 'manager',
    ->     IFNULL(GROUP_CONCAT(DISTINCT C.cnt_id),'') 'supervisor',
    ->     IFNULL(GROUP_CONCAT(DISTINCT D.cnt_id),'') 'teamlead'
    -> FROM apps A
    -> LEFT JOIN contacts B ON A.id = B.id AND B.cnt_type='manager'
    -> LEFT JOIN contacts C ON A.id = C.id AND C.cnt_type='supervisor'
    -> LEFT JOIN contacts D ON A.id = D.id AND D.cnt_type='teamlead'
    -> WHERE ISNULL(B.cnt_id)+ISNULL(C.cnt_id)+ISNULL(D.cnt_id) < 3
    -> GROUP BY A.id;
+----+---------+------------+----------+
| id | manager | supervisor | teamlead |
+----+---------+------------+----------+
|  1 | 20      | 32         | 45,54    |
|  2 | 20      | 32         |          |
+----+---------+------------+----------+
2 rows in set (0.00 sec)

mysql>

GIVE IT A TRY !!!


Is there a way to catch a dbcc checkident command A colleague of mine noticed incorrect ids generated for inserted items. Most probable cause: some script issues a command like the following:

dbcc checkident('dbo.table', reseed, 1)

I have thought about setting up a DDL trigger to catch this commands, but this list of DDL events does not seem to include anything related to reseeding.

Question: is there a way to catch such commands in a similar way DDL triggers catch various database schema changes?

Sure, these are captured by the default trace (and maybe by extended events system_health session, not sure). Given the caveats in this answer (for example a command issued last year is unlikely to still exist in the trace):

What event information can I get by default from SQL Server?

You can see recent DBCC events here:

DECLARE @path NVARCHAR(260);

SELECT @path = REVERSE(SUBSTRING(REVERSE([path]),
       CHARINDEX(CHAR(92), REVERSE([path])), 260)) + N'log.trc'
FROM   sys.traces
WHERE  is_default = 1;

SELECT TextData, ApplicationName, LoginName, StartTime, DatabaseName
  FROM sys.fn_trace_gettable(@path, DEFAULT)
  WHERE EventClass = 116
  --AND DatabaseName = DB_NAME() / or constant
  ORDER BY StartTime DESC;

Note, this will show more than just CHECKIDENT events, but you can filter that further. On the other hand, it might be useful to spot check what other DBCC commands are (or are not) being run, though.


What column/s do I need an index for my queries? I need to know where to put indexes in my queries. I am at lost as how to use it although I am searching about this for quite some time now.

Here are my queries: (in PHP)

1) In operator, I do use > and =. I use = often.

SELECT {columns} FROM {table}
      WHERE {field} {operator} {value}

2) This query is for pagination, limit is always equal to 10.

SELECT {columns} FROM {table}
  WHERE {field} {operator} {value}
  ORDER BY date DESC LIMIT {start}, {limit}

3) This query is for checking the sent and received emails to and from other users.

SELECT {columns} FROM {table} alias1
  WHERE (alias1.{field} = {value} or alias1.{field2} = {value})
    AND EXISTS (SELECT max(alias2.date) alias_date FROM {table} alias2
       WHERE alias1.{field} = alias2.{field}
         and alias1.{field2} = alias2.{field2}
       GROUP BY {field}, {field2}
       HAVING alias1.date = alias_date)
       ORDER BY alias1.date DESC

4) A query using IN.

SELECT {columns} FROM {table}
  WHERE {field} IN ({in_data})
  ORDER BY FIELD ({field}, {in_data})

5) Query using ASC. Data in IN are numeric.

SELECT {columns} FROM {table}
  WHERE {field} IN ('in1', 'in2')
  ORDER BY date ASC

6) Query using AND.

SELECT {columns} FROM {table}
  WHERE {field} {operator} {value}
    AND {field2} {operator2} {value2}

7) Query with DESC.

SELECT {columns} FROM {table}
  WHERE {field} {operator} {value}
  ORDER BY date DESC

In where column/s do I need to use index? The important column//s that in need of index? I need some help.

Note

The {field} and {field2} are only one field, e.g. WHERE {lastname} = {value}.

Update

I posted a more detailed query from my codes and how to implement index based on how I understand it. Here is the link: Are the index here will suffice to make the query faster?

Most of your answers are here.  But here are some notes:

= is easier to index than > (or other inequalities).  For that reason, you list of cases is much amplified.
Using OFFSET (under the guise of LIMIT {start}. ...) requires scanning all the skipped rows.  So, see if you can "remember where you left off" as in here.
Please use fake names instead of {...}; even a, b would be easier to read and parse.
WHERE a op 3 -- usually can use INDEX(a)
WHERE a = 3 ORDER BY b -- use INDEX(a,b), but
WHERE a > 3 ORDER BY b -- won't get past the "range"; that is, INDEX(a,b) will work, but the b part will not be used.
I don't think HAVING ever factors into indexing.
IN ( constants ) is somewhere between = and > for index-ability:
WHERE a IN (constants) AND b > 8 might use both parts of INDEX(a,b), but
WHERE a IN (constants) ORDER BY b cannot use both parts of INDEX(a,b)
If {columns} is a short enough list, then consider a "covering" index.  (See my first link.)
ORDER BY:  ASC vs DESC usually does not matter; but
ORDER BY a ASC, b DESC cannot use INDEX(..., a, b) because the directions are different.  (Version 8.0 has a solution; I assume you are not there yet.)
OR is rarely optimizable -- that is it usually defies index usage.
Your case #6 has 4 sub-cases for operators; see my link.  Hint: The order of ANDs does not matter, but the order of columns in a composite index does.

Etc, etc.

One more thing... If you have most of those cases hitting the same table, it becomes tricky to devise the optimal set of indexes.  Note

INDEX(a) is redundant if you have INDEX(a,b)
There is a limit of 64 indexes on a table, but it is unwise to have more than, say, a dozen.

If you want more specifics, (and my blog does not suffice), please provide specific schema and queries.

"Case 2 is most popular"

If id is the PRIMARY KEY and if it is in date order:

WHERE a = const
  AND id <= $left_off
ORDER BY id DESC
LIMIT 11

INDEX(a, id)

WHERE a > const
  AND id <= $left_off
ORDER BY id DESC
LIMIT 11

(PK suffices)

Notes:

By switching from date to id (if possible), the "left off" logic becomes much simpler.
LIMIT 11 - 10 to show, 1 to see if there is any more.  (This makes the UI more friendly, rather than linking to an empty next page.)
See the second link for more discussion.


Client to use the slave when the master is down in MySQL replication Is it possible for the client to use the slave when the master is down in mysql replication?

I have already setup a simple mysql replication having a master and a slave.

The client connects to the master to access the database, any changes made to the master server is replication onto the slave. How shall I configure that when the master server is down, the client automatically connects to the slave server?

I am using MySQL Server 5.1 and MySQL workbench 5.2 CE. I edited the my.ini files for both master and slave.

The client uses a J2EE application.

What additional commands must I input in the master my.ini file to make the changes?

Also is there a way to increase or decrease the time interval between replications?

Since you have Master/Slave, you may want to implement the use of two things

Circular Replication
Database Virtual IP (aka DBVIP)

Circular Replication
Circular Replication is nothing more than first setting up Master/Slave then performing the same steps using the Slave as the Master's Master and the Master as the Slave's Slave. It just entails

explicitly user a different server_id on each DB Server
enabling binary logging on both DB servers
making sure the replication user is defined on both DB servers

Database Virtual IP (aka DBVIP)
There are products you can download and install to setup a DBVIP. One such product I use is ucarp. Another product is Linux Heartbeat. I normally do not use such things with MySQL Circular Replication or Master/Slave. Why?
Since those products can perform automatic failover, you do not want to do that in the event a Slave is some number of seconds behind in replication lag.
You should perform manual failovers.
Here is a poor man's approach to implementing DBVIP management.
Suppose you have this setup

DB Server1 has IP 10.1.2.30
DB Server2 has IP 10.1.2.40
You want to use DBVIP 10.1.2.50

Create the Script called /usr/local/sbin/MyAppDBVIP like this
echo echo 10.1.2.50 > /usr/local/sbin/MyAppDBVIP

Create the Script called /usr/local/sbin/dbvip-up
DBVIP=`/usr/local/sbin/MyAppDBVIP`
ip addr add ${DBVIP}/24 dev eth1

Create the Script called /usr/local/sbin/dbvip-down
DBVIP=`/usr/local/sbin/MyAppDBVIP`
ip addr del ${DBVIP}/24 dev eth1

Make sure all scripts are executable
chmod +x /usr/local/sbin/MyAppDBVIP
chmod +x /usr/local/sbin/dbvip-up
chmod +x /usr/local/sbin/dbvip-down

Make sure these script exist on both DB Servers
Simply run dbvip on whichever server you choose. .
So the failover process and protocol are the following:

Run dbvip-down on the DB Server that has the DBVIP. If you cannot
Run dbvip-up on the DB Server that you want to have the DBVIP
Just remember you should not run dbvip-up on both machines
After running dbvip-up, restart apache, JBoss, or any other app server contacting MySQL via the old Master


Multiply several discrete values in multiple columns and then sum I need to multiply values from two tables and sum results. The database is either Postgres or MSSQL (long story).

Table Bought_Products

ID | Name | Customer_ID | Product_ID | Foo | Bar | Blarg
1  | test | 123         | 321        | NULL| 1   | NULL
2  | tast | 123         | 231        | 5   | NULL| 20
3  | tost | 456         | 321        | NULL| 3   | NULL

Table Price

ID | Item | Product_ID | Cost
1  | Foo  | 321        | 10
2  | Bar  | 321        | 5
3  | BASE | 321        | 100
4  | BASE | 231        | 50
5  | Blarg| 231        | 0.5
6  | Foo  | 231        | 1

I need a result like this:

Customer | Products | Revenue
123      | 321, 231 | 170

For Customer 123 there are 2 products: 321 and 231, with a base price of 100 and 50 respectively; plus 1 x 'Bar' item at 5; plus 5 x 'Foo' items at 1; and 20 x 'Blarg' items at 0.5.

This is what I have for a start:

select sum(p.cost) from price p
join Bought_Products bp on bp.product_id = p.product_ID
where p.item = 'BASE'

I need to add the relevant 'Foo', 'Bar' or 'Blarg' values from Bought_Products multiplied with the respective Price.Cost (matching the column header to the item).

I've not used basic mathematical functions in SQL, yet. I'm not sure how to join between the 2 tables to then multiply the values. With subqueries and subsequent joins?

This query produces your desired result for every customer in the table:

SELECT customer_id AS customer
     , string_agg(DISTINCT product_id::text, ', ') AS products
     , sum(cost * CASE p.item
                     WHEN 'BASE'  THEN 1
                     WHEN 'Foo'   THEN b.foo
                     WHEN 'Bar'   THEN b.bar
                     WHEN 'Blarg' THEN b.blarg
                  END) AS revenue
FROM   bought_products b
LEFT   JOIN price p USING (product_id)
-- WHERE  p.item = ANY ('{BASE,Foo,Bar,Blarg}')
GROUP  BY 1;

db<>fiddle here

We only need to join once. The magic happens in the CASE expression to multiply depending on the Item.

I made it a LEFT [OUTER] JOIN to keep every product in the result even without any matching price rows. Use [INNER] JOIN to remove those instead. And/or activate the commented WHERE clause to the same effect.

The row with ID 1 in the Price table stands out, btw, as the related row in Bought_Products has Foo IS NULL.

The relational design seems a bit mysterious. I hope you have good reasons.


Convert right side of join of many to many into array When using join on many to many relationship the result is split on multiple rows. What I'd like to do is convert the right side of a join into an array so the result is one row.
Example with 3 tables:
CREATE TABLE items (
  id    serial primary key,
  title text
);

CREATE TABLE tags (
  id    serial primary key,
  title text
);

CREATE TABLE items_tags (
  item_id int references items(id),
  tag_id  int references tags(id),
  primary key (item_id, tag_id)
);

When selecting items with their tags I can do it this way:
SELECT i.id, i.title, i.title
FROM items i
INNER JOIN items_tags it
ON it.item_id = i.id
INNER JOIN tags t
ON t.id = it.tag_id;

And the result will come up as:
(1, "item n1", "sport")
(1, "item n1", "soccer")
(2, "item n2", "adventure")
(2, "item n2", "mountain climbing")
(2, "item n2", "sport")
(2, "item n2", "nature")

What I'd like to have is this:
(1, "item n1", ["sport", "soccer"])
(2, "item n2", ["adventure", "mountain climbing", "sport" , "nature"])

To aggregate most rows
While querying all or most items, it is typically substantially faster to aggregate rows from the "many"-table first and join later:
SELECT id, i.title AS item_title, t.tag_array
FROM   items      i
JOIN  (  -- or LEFT JOIN ?
   SELECT it.item_id AS id, array_agg(t.title) AS tag_array
   FROM   items_tags it
   JOIN   tags       t  ON t.id = it.tag_id
   GROUP  BY it.item_id
   ) t USING (id);

Use LEFT [OUTER] JOIN in the outer query if there can be items without tags - which would be excluded with [INNER] JOIN.
Since that does not multiply rows in the join, we need no GROUP BY in the outer SELECT.
Joining before aggregation also gets out of hands with more than one 1:n table in the FROM list (not in this simple case). See:

Two SQL LEFT JOINS produce incorrect result

To aggregate few rows
For a small percentage of rows, use a LATERAL join with an ARRAY constructor:
SELECT id, title AS item_title, t.tag_array
FROM   items i, LATERAL (  -- this is an implicit CROSS JOIN
   SELECT ARRAY (
      SELECT t.title
      FROM   items_tags it
      JOIN   tags       t  ON t.id = it.tag_id
      WHERE  it.item_id = i.id
      ) AS tag_array
   ) t;

Since an ARRAY constructor always produces a row (with empty array if the subquery is empty - subtle difference in the result!), LEFT JOIN LATERAL (...) ON true is not needed here. See:

What is the difference between LATERAL and a subquery in PostgreSQL?
Why is array_agg() slower than the non-aggregate ARRAY() constructor?
Understanding multiple table join with aggregation

Aside
You had a typo in your query. 3rd column would be t.title. I added aliases to your original (un-aggregated) query to clarify:
SELECT i.id, i.title AS item_title, t.title AS tag_title
FROM   items      i
JOIN   items_tags it ON it.item_id = i.id
JOIN   tags       t  ON t.id = it.tag_id;

"id" or "title" are typically not very distinctive and not very useful identifiers. See:

How to implement a many-to-many relationship in PostgreSQL?


mysqldump on slave (without stopping replication) Is it okay to run mysqldump --single-transaction on Slave DB without stopping replication (stop slave;)? This is for regular daily backup so we can restore Master DB in case of a storage disaster. Most of the tables are InnoDB, only a couple of them are MyISAM.

Official documentation says that:

  "You should stop replication on the slave before starting the dump
  process to ensure that the dump contains a consistent set of data".

What does it mean consistent data? Does it mean like latest data?

I know --single-transaction means that it takes data from the time it was issued, so new further changes while the dump was happening will not be recorded to the dump. Am I correct?

Do not want to run the backup on master due to lag it creates on our application.

Performing a mysqldump creates a consistent and logical dump of the data which can be point-in-time recoverable.
There are two ways you can stop replication partway
METHOD #1 : mysqldump option --dump-slave
When you perform a mysqldump with --dump-slave, it will stop only the SQL thread. The I/O thread continues collecting binary log events from the Master. Note how this is explained in the MySQL Documentation:

This option is similar to --master-data except that it is used to dump a replication slave server to produce a dump file that can be used to set up another server as a slave that has the same master as the dumped server. It causes the dump output to include a CHANGE MASTER TO statement that indicates the binary log coordinates (file name and position) of the dumped slave's master. These are the master server coordinates from which the slave should start replicating.
--dump-slave causes the coordinates from the master to be used rather than those of the dumped server, as is done by the --master-data option. In addition, specfiying this option causes the --master-data option to be overridden, if used, and effectively ignored.
The option value is handled the same way as for --master-data (setting no value or 1 causes a CHANGE MASTER TO statement to be written to the dump, setting 2 causes the statement to be written but encased in SQL comments) and has the same effect as --master-data in terms of enabling or disabling other options and in how locking is handled.
This option causes mysqldump to stop the slave SQL thread before the dump and restart it again after.
In conjunction with --dump-slave, the --apply-slave-statements and --include-master-host-port options can also be used.
This option was added in MySQL 5.5.3.

METHOD 2 : Stop the SQL Thread Yourself
Did you know you can manually stop the SQL thread ? According to the MySQL Documentation  on STOP SLAVE;, you can run the following:
mysql> STOP SLAVE SQL_THREAD;

The I/O thread continues receiving binlog events from the Master.
After running STOP SLAVE SQL_THREAD;, you can then run your mysqldump. You could probably do an LVM snapshot or anything else to the data files on the Slave.
When done with your backup, then run
mysql> START SLAVE SQL_THREAD;

or
mysql> START SLAVE;

This will reactivate the SQL thread.
GIVE IT A TRY !!!
Warning: Stopping the SQL thread, which terminates a local DB Connection, could make tables created with CREATE TEMPORARY TABLE disappear. If you are using statement based replication, please do additional research on your App to make sure transactions don't rollback when the SQL thread stops.
WHAT ABOUT --single-transaction ???
You can mysqldump using  --single-transaction ??? without  --dump-slave. However, there are many commands that break running transactions. See the list of those commands in my old post Transactional DDL workflow for MySQL.
UPDATE 2018-02-13 13:07 EST
You just asked me : These backups are for restoring the master DB in case of a storage disaster. Not for slave. So is --dump-slave still valid?
If you use the dump as is (without creating another slave with it and without attempting point-in-time recovery with replay of binary logs), then yes  --dump-slave is OK..
Please keep in mind that if you launch a mysqldump at midnight, that's the dump's point-in-time.
Here is an additional benefit to using  --dump-slave : the mysqldump will include the Master's log file and position between lines 23 and 25. That way, if you wish to create another slave, you have the Master's replication coordinates. If you wish to attempt point-in-time recovery and you can still retrieve the binary logs from the old Master, you can do so.


Why do information_schema queries scan all databases? I'm building a PHP application that looks up the last modified date of all databases using information_schema.  A simplified version is like this:

$sql = "SELECT update_time FROM information_schema.tables;

I have about 300 databases and this loop takes about 3.1 seconds, so I want to speed this up.  I noticed with Explain that any query on information_schema scans all databases, so I'm guessing this is why it's slow.  Is there any way to fix this?  I'm using the latest MariaDB 10.3.14.

MariaDB [(none)]> EXPLAIN SELECT update_time FROM information_schema.tables\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tables
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
        Extra: Open_full_table; Scanned all databases
1 row in set (0.000 sec)

EDIT:  I tried MySQL 8.0 and now the loop takes 0.03 seconds.  Apparently this is a bug, but is there any fix for MariaDB? https://bugs.mysql.com/bug.php?id=81347

The original metadata storage for MySQL was not ideal. It didn't allow transactional DDLs, it had reliability issues, and it was slow and buggy. One of the issues that created was that querying information_schema was slow.

MySQL 8.0, with some foundational work being done in 5.6 and 5.7, finally got rid of the old system (.frms), and that allowed migrating it to a native system, and even being able to create indexes for metadata tables, speeding it up considerably.

MariaDB still uses the old system, but there are tasks filed to (potentially) migrate to a new system, similar to that of MySQL 8: https://jira.mariadb.org/browse/MDEV-11655 (and related tasks). Subscribe that task on MariaDB tracker or consider sponsoring the work. Obviously I don't know, but based on my interactions on the MariaDB JIRA, they will probably want to fix that larger task first.


After Update/Insert trigger sometimes fails to do its job I've got this trigger:

CREATE OR REPLACE TRIGGER "DOKCENTRA"."INTDOC_AFTER" AFTER
  UPDATE OR INSERT
    ON "DOKCENTRA"."INT_DOC" FOR EACH ROW
BEGIN
IF UPDATING THEN
 IF (
     :NEW.DOCNUMBER   IS NOT NULL
   AND :OLD.DOCNUMBER IS NULL) THEN
   INSERT
   INTO
     DOC_IDS VALUES
     (
       :NEW.DOCNUMBER
     );
 END IF;
ELSIF INSERTING THEN
  IF (:NEW.DOCNUMBER IS NOT NULL) THEN
    INSERT
    INTO
      DOC_IDS VALUES
      (
        :NEW.DOCNUMBER
      );
  END IF;
END IF;
END;

I think it's obvious what it does but to make it clear let me give a brief explanation. In my INT_DOC table there's DOCNUMBER column. I assign a value to this column in Before Update trigger. And in the above trigger, which of course should fire after the Before Update Trigger, I check if the DOCNUMBER has value (and if it's for the first time) and if it does, I write that value to another table called DOC_IDS. The problem is, the above trigger sometimes, although very rarely, fails to insert the :NEW.DOCNUMBER to the DOC_IDS table. That is, sometimes I see a record in INT_DOC table with DOCNUMBER value that does not exist in DOC_IDS table.The DOC_IDS table has only one column called DOCID and it's part of a UNIQUE KEY constrsraint. Do you see any problem with this trigger?

EDIT:

I think you'll get a better idea if I post the BEFORE TRIGGER too:

create or replace
TRIGGER "DOKCENTRA"."INTDOC_BEFORE" BEFORE
  UPDATE OR
  INSERT ON "DOKCENTRA"."INT_DOC" FOR EACH ROW
 BEGIN
 IF INSERTING THEN
 IF (:NEW.CONFIRMED=1 AND :NEW.DOCNUMBER IS NULL) THEN
    SELECT DOCUMENTS.F_GENERATE_DOCNUMBER(:NEW.REGUSER,2)
    INTO :NEW.DOCNUMBER
    FROM DUAL;
  END IF;
 ELSIF UPDATING THEN
  IF (:NEW.CONFIRMED=1 AND :NEW.DOCNUMBER IS NULL) THEN
    SELECT DOCUMENTS.F_GENERATE_DOCNUMBER(:NEW.EDITUSER,2)
    INTO :NEW.DOCNUMBER
    FROM DUAL;
  END IF;
END IF;
END;

And the only place I set DOCNUMBER is this trigger.

EDIT2:

CREATE TABLE INT_DOC(
   DOCID VARCHAR2(50) NOT NULL,
   RECEIVEDDATE DATE NOT NULL,
   SENDERFULLNAME VARCHAR2(100) NOT NULL,
   SUBJECT VARCHAR2(200) NOT NULL,
   CONFIRMED NUMBER(1,0) NOT NULL DEFAULT 0,
   DOCNUMBER VARCHAR2(10),
  CONSTRAINT INT_DOC_PK PRIMARY KEY
  (
    DOCID
  )
  ENABLE
);
ALTER TABLE INT_DOC
ADD CONSTRAINT INT_DOC_UK1 UNIQUE
(
  DOCNUMBER
)
ENABLE;

CREATE TABLE DOC_IDS(
 DOCID VARCHAR2(10)
);
ALTER TABLE DOC_IDS
ADD CONSTRAINT DOC_IDS_UK1 UNIQUE
(
  DOCID
)
ENABLE;

INSERT INTO INT_DOC(DOCID,RECEIVEDDATE,SENDERFULLNAME,SUBJECT,CONFIRMED)
VALUES('AFFXRARA1',to_date('01.01.2015','DD.MM.YYYY'),'John Saunders','SomeSubject',1);--CONFIRM set to 1 at insert
INSERT INTO INT_DOC(DOCID,RECEIVEDDATE,SENDERFULLNAME,SUBJECT)
VALUES('AFCCYARA1',to_date('25.10.2015','DD.MM.YYYY'),'Steve Wonder','AnotherSubject');
INSERT INTO INT_DOC(DOCID,RECEIVEDDATE,SENDERFULLNAME,SUBJECT)
VALUES('BRTYRARA1',to_date('12.01.2015','DD.MM.YYYY'),'Ellie Johnson','YetAnotherSubject');

UPDATE INT_DOC SET CONFIRMED=1 WHERE DOCID='AFCCYARA1';--CONFIRM set to 1 with update

Here's the sample record in INT_DOC table which has the DOCNUMBER column value that does not exist in the DOC_IDS table:

           DOCID                       RECEIVEDDATE             SENDERFULLNAME                 SUBJECT                      CONFIRMED        DOCNUMBER
---------------------------------- |-----------------------|----------------------|-----------------------------------|------------------|------------
6A5889BB0190D0211A991F47BB19A777        23.02.2016 10:24:17       Rowan Priatski           Amendment to the last decree            1               1602000097

While testing with random data the above trigger worked perfectly for insertion.
But as Mat said in the comment, while updating the DOCNUMBER column of INT_DOC the after update trigger failed to update that value in DOC_IDS table.
Here is the output of the sample test:

SQL> INSERT INTO INT_DOC(DOCID,RECEIVEDDATE,SENDERFULLNAME,SUBJECT)
VALUES('BRTYRARA1',to_date('12.01.2015','DD.MM.YYYY'),'Ellie Johnson','YetAnotherSubject');

1 row created.

SQL> select * from doc_ids;

no rows selected

SQL> update int_doc set confirmed=1,docnumber=1 where docid='BRTYRARA1';

1 row updated.

SQL> select * from doc_ids;

DOCID
----------
1

SQL> INSERT INTO INT_DOC(DOCID,RECEIVEDDATE,SENDERFULLNAME,SUBJECT,confirmed)
VALUES('AFFXRARA1',to_date('12.07.2015','DD.MM.YYYY'),'Jay Hons','YetAnotherSubject',1);

1 row created.

SQL> select * from doc_ids;

DOCID
----------
1

SQL> select docnumber from int_doc;

DOCNUMBER
----------
1

SQL> update int_doc set docnumber=2 where docid='AFFXRARA1';

1 row updated.

SQL> select * from doc_ids;

DOCID
----------
1
2

SQL> select docnumber from int_doc;

DOCNUMBER
----------
1
2

SQL> update INT_DOC set docnumber = 3 where docnumber = 1;

1 row updated.

SQL> select docnumber from int_doc;

DOCNUMBER
----------
3
2

SQL> select * from doc_ids;

DOCID
----------
1
2

Here i got the DOCNUMBER 3 in INT_DOC table but not in the DOC_IDS table.


mysqldump inconsistencies I'm planning to take a dump of a table with the --single-transaction option enabled.

I know that if we execute any DDL statements during the dump process we might get an inconsistent dump. Will the same happen when there are any temporary tables created? We have temporary tables created in our app, and do not have any control over that.

First, I'll explain why DDL cannot execute during backup. The reason is that MySQL stores the definitions of relations (tables, indexes, etc) to MyISAM tables located at mysql subdirectory in version 5.6 or earlier (, therefore DDL should be prevented to execute during backup).

Next, I'll answer your question. I assume that you use only innodb tables. (If you use any MyISAM tables, --single-transaction option does not act to your wish.) In this case, the answer of your question is "Yes, you can create temporary tables during backup",  because the definition of temporary tables does not need to store  the mysql subdirectory,  and  creating temporary tables is a normal operation under transaction processing.

In other words, there is no problem because definitions and contents of temporary tables are not backed up.


Login using database user accounts I want to know is it advisable to directly use the database user and passwaord as the user of web application generally i have seen everywhere they use a separate table inside database for managing (add/edit/delete) the users .

My question why NOT use the default database user name and password for my web application ?

Note: I want to reuse the user and role system which postgres already provide instead of making a new table of users. I think this will be good because

reduce the replication of user tables
not to worry about security password mgmt
can use roles of postgres to limit the data access
many other application can directly connect the database (in my case this is important as a desktop application will also access the database still the data will be secured by roles )

You have to consider, basically two tradeoffs:

PROs

Using the "PostgreSQL users" instead of your own table(s) allows for very fine-tuning security privileges without having to add all this logic in your application. Consider whether you actually need all this finesse or not.

CONs

If you want to add extra information about a user (such as an email account to which you can send a restore password link or something similar) you still need to create a secondary table to store all of it [or use something like COMMENT ON ROLE role IS '{"email":"a@b.com", "comment:""structured text where you store all kinds of information"}', which I think denormalizes too much how you use your database].
Login roles are common to all databases within a PostgreSQL cluster, not just for a specific one. If you're hosting several of them simultaneously, this can artificially limit the usernames you can give to your users (because, maybe, you're already using the same username (=login role) for another DB for another purpose).

Alternative (having the best of both worlds)

You can have a mix: create one login role (or group role and login role) per each different role within your application, which allows for fine-tuning security; have one users table with a login role assigned to each. Your application should start with a role that can read this table, and after validation of credentials, disconnect from the database and reconnect using the proper login role. The login roles are internal to the application, and not visible to the users.


What's the (Big O) computational complexity of a PostgreSQL recursive common table expression? For example, taking this StackOverflow #44620695 question, recursive path aggregation and CTE query for top-down tree postgres as an example, which uses a recursive CTE to traverse a tree structure to determine the paths from a starting node.

The screenshot above shows the the input data, the recursive CTE result, and a visualization of the source data.

Recursive CTE are iterative over the preceding result -- right? (as suggested in the accepted answer here) -- so would the time complexity be something like O(log n)?

OK, I'm going to propose a solution.  Using the excellent article "A Gentle Introduction to Algorithm Complexity Analysis" as a guide, I believe the worst-case complexity of the example in my question (above) is as follows.

Given this recursive CTE:

WITH RECURSIVE nodes(id, src, path, tgt) AS (
    -- Anchor member:
    SELECT id, s, concat(s, '->', t), t,
    array[id] as all_parent_ids
    FROM tree WHERE s = 'a'
UNION ALL
    -- Recursive member:
    SELECT t.id, t.s, concat(path, '->', t), t.t, all_parent_ids||t.id FROM tree t
    JOIN nodes n ON n.tgt = t.s
    AND t.id <> ALL (all_parent_ids)
)
-- Invocation:
SELECT path FROM nodes;

At the Anchor member (query definition), the algorithm selects each row from the table; therefore, at this step the maximum number of iterations (i) and the maximum size (n) is the number of rows in table; i < n, if a starting point within the table is specified.
The Recursive member selects each row from the table, starting from the position specified in the anchor member, so the maximum number of iterations here once again is: i ≤ n.

So, with the recursive CTE above I believe that the overall complexity is Θ(n).


Permissions to execute a procedure on database a that reads a table on database b A user in database a has execute permission on a stored procedurte in database a, that stored procedure reads table 1 in database b however when the user executes that procedure they get the following message.

  The SELECT permission was denied on the object '1', database 'b', schema 'dbo'.

If I give the user db_datareader permission in database b then the procedure works.

I didn't think I needed to do that though. Please can somebody explain how wrong I am!

This is an issue of ownership chaining and how that doesn't automatically translate across databases as most people would expect. The following MSDN page has a good explanation of what is going on in this situation:

Ownership Chains

That article states that in order to get this to work as most would expect, you need to enable "Cross-Database Ownership Chaining". However, this is a security risk, and that article even has a section for "Potential Threats".

The more secure way to extend permissions is by using module signing. Please see my answer on the following Question (here on DBA.SE) for how to remedy this, especially if you don't want to give the user any access to Database B:

stored procedure can select and update tables in other databases - minimal permissions granted

The first time you go through this exercise it might seem terribly complicated, but after a few times it will make enough sense to not seem to bad ;-).


how to sum all the returned rows I have a query which looks like:-

select ad.STUDENTMASTERID,ad.ADVERTISEMENTCODE, cod.POSTNAME,ad.CANDIDATENAMEINNEPALI,
ad.PAYMENTSTATUS, cd.TOTALFEE, cod.CLASSNAME
from CANDIDATEAPPEARAGAINSTADVTCODE ad
inner join CANDIDATEAPPEARTOADVTNUMBER cd on ad.STUDENTMASTERID = cd.STUDENTMASTERID
inner join ADVERTISEMENTCODE cod on cd.ADVERTISEMENTCODE = cod.ADVERTISEMENTCODE
WHERE ad.STUDENTMASTERID='415562' and ad.PAYMENTSTATUS='Pending' and
cod.STATUSOFLASTDATEFORMSUBMISSION='Live';

this query returns two rows

415562  15695-15698/2073-74 अमिन वा सो सरह  पुस्मिता कुमारी एेडी    Pending 300 रा.प.अनं द्वितीय
415562  15695-15698/2073-74 अमिन वा सो सरह  पुस्मिता कुमारी एेडी    Pending 150 रा.प.अनं द्वितीय

now i need to return only one row but the values of 6th column should be sum of each 6th columns of the each row.

for eg here:-

415562  15695-15698/2073-74 अमिन वा सो सरह  पुस्मिता कुमारी एेडी    Pending 450 रा.प.अनं द्वितीय

You need to use SUM() of TOTALFEE and GROUP BY the remaining column names.

The query will be:

SELECT ad.STUDENTMASTERID, ad.ADVERTISEMENTCODE, cod.POSTNAME,
       ad.CANDIDATENAMEINNEPALI, ad.PAYMENTSTATUS, SUM(cd.TOTALFEE) AS TOTALFEE, cod.CLASSNAME
FROM CANDIDATEAPPEARAGAINSTADVTCODE ad
INNER JOIN CANDIDATEAPPEARTOADVTNUMBER cd ON ad.STUDENTMASTERID = cd.STUDENTMASTERID
INNER JOIN ADVERTISEMENTCODE cod ON cd.ADVERTISEMENTCODE = cod.ADVERTISEMENTCODE
WHERE ad.STUDENTMASTERID = '415562'
    AND ad.PAYMENTSTATUS = 'Pending'
    AND cod.STATUSOFLASTDATEFORMSUBMISSION = 'Live'
GROUP BY ad.STUDENTMASTERID, ad.ADVERTISEMENTCODE, cod.POSTNAME,
       ad.CANDIDATENAMEINNEPALI, ad.PAYMENTSTATUS, cod.CLASSNAME;


Adding new key(s) to enable partitioning, INSERT performance? I have a table to storage GPS coordinates with multiple (~30) inserts per second (from 50 different devices)  during 12 hours per day, so the table is growing fast, to improve the SELECT speed I was thinking of create a partition in the table by Date or DeviceID.

In that case I will need to add the partitioning keys as primary keys?, how will that affect the insertion performance?

CREATE TABLE `carlocation` (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `Date` datetime DEFAULT NULL,
  `GpsLatitude` double DEFAULT NULL,
  `GpsLongitude` double DEFAULT NULL,
  `DeviceID` int(11) NOT NULL,
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1

The server is a Dual Core Xeon with 2GB RAM.
Thanks.

Update: The queries are like

SELECT *
FROM carlocation
 WHERE ((carlocation.DeviceID = 2) AND (carlocation.Date> '2010-01-01') AND (carlocation.Date< '2010-01-02'))

Always quering for ONE device only, in one or multiple days.

Your one select needs this "compound" index.

INDEX(DeviceID, Date)

Do you have other queries?

But your question was about PARTITIONing.  No advantage, based on what you have said so far.


Estimate projected database growth I recently started working with SQL Server 2008 as a DBA trainee. I need calculate the size of the database but also estimate its growth over recent months and the predicted growth for the next 12 months.

I can use the sp_spaceused statement to calculate the actual size but how do I calculate everything else?

The other answers are technically correct, but not real-world correct. Here's what you need to ask the business:

What time horizon am I aiming for? In your case, you're looking for a 12-month number.

During that time, will we be archiving data, or keeping all data? In some businesses, you're allowed to (or required to) only keep a certain amount of data, like the last 12 months. In that case, you'll need to figure out the data growth (which the subsequent questions will answer) but then back down to the last rolling 12 months. You can't just say, "Right now that amount of data is 100GB," because if your data volume is growing, then the last 12 months is growing too. The time amount might be constant, but the data is not.

Will we be adding additional users? For example, the business might be growing into new territories or acquiring new customers. If they double the user base, then in some cases, the data will start doubling as well.

Do we expect the business volume to grow? If you're tracking sales on a web site, for example, and you start running Super Bowl or World Cup ads, your data volume can hit the hockey stick growth curve.

Will we be adding additional functionality in the app? If the app suddenly starts storing images, this will dramatically affect database size.

Will we be adding data from another source, or logging new data? If you start capturing web site clicks, or in a data warehouse, adding additional sources, then data volume will grow.

Will developers or DBAs be performance tuning indexes? If you're going to let people create indexes, you can easily double (or triple, or quadruple) the size of your data depending on how overzealous they get.

And as long as you're asking these questions, you should also ask if performance is expected to stay the same, degrade, or get better. I like mapping out the projected growth on a line chart, and then comparing hardware and staff training investments over that same timeline.


SORT, ORDER by Number I have the following table:

ID     Name    Items
-------------------------------------
1      John    7
2      Peter   533
3      Chang   13
4      Mike    9100

I want to order it by Items. I used ORDER BY items ASC, but it returns:

ID     Name    Items
-------------------------------------
3      Chang   13
2      Peter   533
1      John    7
4      Mike    9100

I want to return:

ID     Name    Items
-------------------------------------
1      John    7
3      Chang   13
2      Peter   533
4      Mike    9100

I think this might be a silly question, but I really don't have any ideas about how to solve this.

Thanks.

Only explanation I can think of that result, if you are sorting with Order By Items ASC as you said, is that Items column is of character datatype (either VARCHAR or CHAR) instead of numeric.

13, 533, 7, 9100 are in lexicographical order, not numerical order.

Check the data type of that column.


Is there a more concise way in Postgres to extract a portion of a string? I'm using Postgres 9.5. I have a table with a column that records URLs.  Sometimes the URL has a query string and sometimes it does not. I would like to extract the URL, minus any query string, so I came up with:

select substring(url, 0, case position('?' in url) when 0 then length(url)+1 else position('?' in url) end)
from article;

This seems a little wordy and I was wondering if there's a more concise way to do this. My table column is of type TEXT.

You can use regexp_replace() to replace everything after the first ? with nothing:

select regexp_replace(url, '\?.*$', '')

The following example:

with data (url) as (
   values
    ('http://foo.bar/some/page'),
    ('http://foo.bar/some/page?id=42&name=Marvin')
)
select url, regexp_replace(url, '\?.*$', '') as clean_url
from data;

returns:

url                                        | clean_url
-------------------------------------------+-------------------------
http://foo.bar/some/page                   | http://foo.bar/some/page
http://foo.bar/some/page?id=42&name=Marvin | http://foo.bar/some/page


Get count for different columns with different conditions from same table into one result table? I have a table, action using postgresql:
is_jump | is_throw | is_punch
true      true        false
false     true        false
false     true        true

These are all booleans.
I want to get a count for each of the actions (when true) into one result table in one query:
is_jump_count | is_throw_count | is_punch_count
     1              3                1

I can get the count for one of them at the time, that's the easy part, but, how can I combine it all in one query so that I get one result table with the counts by column like shown here?
I have implemented it this way, but, I wonder if there's a better way to accomplish this given that in the end I think I'll end up with about 15 actions to track count for...:
SELECT a.ct is_jump, b.ct is_throw
FROM
( SELECT COUNT(*) ct FROM action WHERE is_jump=true and user_id=6 ) a,
( SELECT COUNT(*) ct FROM action WHERE is_throw=true and user_id=6 ) b;

You can use filtered aggregation:
select count(*) filter (where is_jump) as is_jump_count,
       count(*) filter (where is_throw) as is_throw_count,
       count(*) filter (where is_punch) as is_punch_count
from the_table;


Queries for greatest-n-per-group can be solved using window functions:

select note_id, size, id
from (
   select note_id, size, id,
          row_number() over (partition by note_id order by size desc) as rn
   from the_table
) t
where rn = 1;

The above is standard SQL, but Postgres as another (proprietary) feature that can be used for this:

select distinct on (note_id) note_id, size, id
from the_table
order by node_id, size desc;

The distinct on () solution is usually faster then the solution using window functions.


Which tool enables using SQL query result sets as temporary tables for further analysis and set operations? For examples, lets say I need to do set operations and joins on below 3 result sets.

I don't want to write a composite query or use temporary database tables. Something which would enable faster data analysis using broken-down sets of data like below from a large database. Like as if you could join two result sets in toad or SQL developer with the help of a GUI.

SQL Query 1 result: (T1)

Col1 Col2
A    1
B    2

Query 2 result: (T2)

Col3 Col4
1    #
2    *

Query 3 result: (T3)

Col5
1
3

Required output is

 Select T1.col1,T2.col4
        from T1,T2
        where T1.col2=T2.col3
        and T1.col2 in (Select T3.col5 from T3)

(Not necessary that the tool should generate SQL like above to process the result sets, SQL is only for conveying the logic.)

Required result:

Col1  Col4
1    #

It seems to me that you might want one of two things to do what you require.

1) could be global temporary tables (GTTs), see here or here. Check out the Oracle docco here for drier technical detail.

2) might be CTEs - Common Table Expressions (or subquery factoring as Oracle calls it) - also known as the "WITH CLAUSE". Howtos are here and here - (again, rather dry) Oracle docco is here.

One answer to your question is that every tool which supports Oracle supports "using SQL query result sets as temporary tables for further analysis and set operations" - this capacity is native to Oracle and available to all.

As for doing this with a GUI and dragging and dropping lines between either GTTs or CTEs - I'm not sure such a tool exists - but you could experiment with creating your GTT/CTE as a real concrete table and then do the Drag'n'Drop bit and when you're finally getting the result you want, you could just use the GUI tool generated SQL. Better though to learn SQL and be able to do this without a GUI.


What is the point of column families? I've seen that NoSQL database systems like RocksDB offer a feature called column families. I believe I understand what the concept refers to, but what are the actual (practical) benefits of using them? I presume they can improve look-up performance in some cases, or space locality of key-value entires, at the very least? It wouldn't seem to affect the actual semantics of database access, however, as far as I understand. Is this correct? Is there something I'm missing?

I've just uncovered some interesting information from the RocksDB FAQ. (RocksDB is a K-V store.)

Here are some relevant extracts.

  Q: What are column families used for?

  A: The most common reasons of using column families: (1) use different
  compaction setting, comparators, compression types, merge operators,
  or compaction filters in different parts of data; (2) drop a column
  family to delete its data; (3) one column family to store metadata and
  another one to store the data.

  Q: What's the difference between storing data in multiple column
  family and in multiple rocksdb database?

  A: The main differences will be backup, atomic writes and performance
  of writes. The advantage of using multiple databases: database is the
  unit of backup or checkpoint. It's easier to copy a database to
  another host than a column family. Advantages of using multiple column
  families: (1) write batches are atomic across multiple column families
  on one database. You can't achieve this using multiple RocksDB
  databases. (2) If you issue sync writes to WAL, too many databases may
  hurt the performance.

  Q: I have different key spaces. Should I separate them by prefixes, or
  use different column families?

  A: If each key space is reasonably large, it's a good idea to put them
  in different column families. If it can be small, then you should
  consider to pack multiple key spaces into one column family, to avoid
  the trouble of maintaining too many column families.


How encryption in SQL login authentication works I am going through the below link

https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/authentication-in-sql-server

and in that it says

  When SQL Server logins are used, SQL Server login names and encrypted
  passwords are passed across the network, which makes them less secure.

how are the password encrypted and how does sql server decrypt it?  What kind of encryption is involved in it?

how are the password encrypted and how does sql server decrypt it? What kind of encryption is involved in it?

Super high level, if you have encryption setup for the instance then the certificate that is configured can be used. If you don't have it enabled, the self-signed certificate created on SQL Server startup will be used.

Regardless of which is used, the SSL/TLS setup for this is done during the pre-login negotation phase. If, however, encryption is either not set or enforced, the TLS/SSL portion will only be active for the length of the credential transfer and will immediately go back to plain text (no SSL/TLS) once this is completed for the life of the connection.

SQL Server itself doesn't encrypt or decrypt the data, it's encrypted and decrypted in transit only. Depending on your client driver and application, this may or may not exist in plain text in your process memory space. Additionally, SQL Server uses hashes to check the passwords and doesn't store the original plain text password for SQL logins (unless you want to be pedantic about proxy accounts and linked servers).


Database Directory storage is full need to change link it to new Partition My mysql Database Directory size is full. So I want to change it to another partition where there is large amount of space. The database version am using is MySQL 5.7.23 and OS is CentOS 7.

After searching internet i got 2 solutions. First one as follows:

Firstly,I created a Systemlink pointing to the new drive. Then I tried to start mysql, it fails everytime when I try to start. On removing systemlink it will start.

The error am getting is

[Note] InnoDB: Highest supported file format is Barracuda.
2018-08-21T10:08:04.839330Z 0 [ERROR] InnoDB: Operating system error number
13 in a file operation.
2018-08-21T10:08:04.839349Z 0 [ERROR] InnoDB: The error means mysqld does
not have the access rights to the directory.
2018-08-21T10:08:04.839360Z 0 [ERROR] InnoDB: os_file_readdir_next_file()
returned -1 in directory ./, crash recovery may have failed for some .ibd
files!
2018-08-21T10:08:04.839385Z 0 [ERROR] InnoDB: Plugin initialization aborted
with error Generic error
2018-08-21T10:08:05.440529Z 0 [ERROR] Plugin 'InnoDB' init function returned
error.
2018-08-21T10:08:05.440597Z 0 [ERROR] Plugin 'InnoDB' registration as a
STORAGE ENGINE failed.
2018-08-21T10:08:05.440610Z 0 [ERROR] Failed to initialize builtin plugins.
2018-08-21T10:08:05.440617Z 0 [ERROR] Aborting

2018-08-21T10:08:05.440665Z 0 [Note] Binlog end
2018-08-21T10:08:05.440770Z 0 [Note] Shutting down plugin 'CSV'
2018-08-21T10:08:05.441349Z 0 [Note] /usr/sbin/mysqld: Shutdown complete

Secondly, https://www.digitalocean.com/community/tutorials/how-to-move-a-mysql-data-directory-to-a-new-location-on-ubuntu-16-04 I followed this url and after that when tried to start mysql keep on getting this error Cant connect to localhost (111) (Error: 2003). This time nothing is showing in mysqld.log file

Here is the key:

2018-08-21T10:08:04.839360Z 0 [ERROR] InnoDB: os_file_readdir_next_file()
returned -1 in directory ./, crash recovery may have failed for some .ibd
files!

It says, it can't traverse the directory containing the database files, with the error code -1. Error -1 means

  permission denied

The user on which your mysql server is running, should have access to the directory. On most Linuxes, the command is: chown mysql:mysql -Rc /var/lib/mysql as root.


Can't rename columns in PostgreSQL views with CREATE OR REPLACE In PostreSQL 8.3, I'm trying to create a view which will look just like an existing table but have different column names.

This works

CREATE OR REPLACE VIEW gfam.nice_builds AS
 SELECT (family_tree.family_tree_id) as x,
        family_tree.family_tree_name, family_tree.family_tree_description
   FROM gfam.family_tree;

The above makes a duplicate of the family_tree table but the following attempt fails:

CREATE OR REPLACE VIEW gfam.nice_builds AS
 SELECT (family_tree.family_tree_id) as x,
        family_tree.family_tree_name, family_tree.family_tree_description
   FROM gfam.family_tree;

ERROR:  cannot change name of view column "family_tree_id"

How can I rename columns?

I can reproduce your error ... in my case, I created a column first as 'date' then as 'x' (was trying to see if it was an issue with a reserved word; it wasn't:

ERROR:  cannot change name of view column "date" to "x"

If you issue a drop view first, it'll let you re-create the view with a changed name.  I have no idea why create or replace won't do it.

Clarification by Colin 't Hart:

The documentation for CREATE VIEW explains it pretty well, I think:

  The new query must generate the same columns that were generated by the existing view query (that is, the same column names in the same order and with the same data types), but it may add additional columns to the end of the list.


pg_ctl hangs over ssh I am designing a process to test my postgresql 10.8 backups by restoring them to a random point in time in a throwaway virtual machine. I have been unable to fully automate the process though. I am blocked at step 8 (section 25.3.4) of the official documentation

  Start the server.

When executing the pg_ctl start over ssh, the command hangs until it is killed. If I ssh directly onto the VM and execute pg_ctl start, then the command returns quickly as expected.

This thread from 2012 seems to describe a similar scenario. In my case however the postgres process does start successfully even if the calling session is killed while it is hanging (possibly an improvement between 9.0.5 and 10.8 though?).

This github issue seems relevant but sadly is "solved" by a long rewrite in a language I don't know and concludes finally that it's a bug in the pg_ctl binary.

The Big Question

  How can I automate step 8 so that I can continue on to subsequent validation tests of my backup media?

Is this an outstanding bug in the binary I need to hack around? Or have I missed a sensible implementation?

It looks like pg_ctl wants a real terminal for output, which is not allocated by ssh when you simply ask it to run a command. According to the Postgres manual

  On Unix-like systems, by default, the server's standard output and standard error are sent to pg_ctl's standard output (not standard error). The standard output of pg_ctl should then be redirected to a file or piped to another process such as a log rotating program like rotatelogs; otherwise postgres will write its output to the controlling terminal (from the background) and will not leave the shell's process group. [...] These default behaviors can be changed by using -l to append the server's output to a log file. Use of either -l or output redirection is recommended.

Therefore, you either need to tell ssh to allocate a terminal for pg_ctl using the -t options (ssh -t somehost "pg_ctl start") or tell pg_ctl to not write to the terminal (ssh somehost "pg_ctl start >/dev/null" or ssh somehost "pg_ctl start -l /tmp/start.log ")


SQL Server Always On and Replication Merge How can replication Merge be done in a highly available environment? I tried to create the publisher in a partially contained database and it generates the error 21147 of sql,

Can the Express version of the SQL server be within a high availability group in the secondary nodes?

not have my database partially contained within a group of high availability that has consequences?

I hope you can help me with this topic, I thank you in advance for any contribution.

Can the Express version of the SQL server be within a high availability group in the secondary nodes?

AlwaysON Availability groups require Enterprise edition. There are basic Availability groups for standard edition.

Also, partially contained db do not support replication.

  Partially contained databases cannot use replication, change data capture, or change tracking.