texts: Oracle: Inserting data using a database link

texts:
I am trying to use a SELECT query to populate a table in DB2 using a database link to DB1. I am using Oracle SQL Developer on a Windows 7 VM. I connect to DB2, and run the following query.

INSERT INTO DB_NAME.TABLE_NAME (SELECT * FROM DB_NAME.TABLE_NAME@LINK_TO_DB1);

Every time, no matter what, I get the following error:

ORA-12154: TNS:could not resolve the connect identifier specified
12154. 00000 -  "TNS:could not resolve the connect identifier specified"
*Cause:    A connection to a database or other service was requested using
           a connect identifier, and the connect identifier specified could not
           be resolved into a connect descriptor using one of the naming methods
           configured. For example, if the type of connect identifier used was a
           net service name then the net service name could not be found in a
           naming method repository, or the repository could not be
           located or reached.
*Action:
           - If you are using local naming (TNSNAMES.ORA file):
           - Make sure that "TNSNAMES" is listed as one of the values of the
           NAMES.DIRECTORY_PATH parameter in the Oracle Net profile
           (SQLNET.ORA)
           - Verify that a TNSNAMES.ORA file exists and is in the proper
           directory and is accessible.
           - Check that the net service name used as the connect identifier
           exists in the TNSNAMES.ORA file.
           - Make sure there are no syntax errors anywhere in the TNSNAMES.ORA
           file.  Look for unmatched parentheses or stray characters. Errors
           in a TNSNAMES.ORA file may make it unusable.
           - If you are using directory naming:
           - Verify that "LDAP" is listed as one of the values of the
           NAMES.DIRETORY_PATH parameter in the Oracle Net profile
           (SQLNET.ORA).
           - Verify that the LDAP directory server is up and that it is
           accessible.
           - Verify that the net service name or database name used as the
           connect identifier is configured in the directory.
           - Verify that the default context being used is correct by
           specifying a fully qualified net service name or a full LDAP DN
           as the connect identifier
           - If you are using easy connect naming:
           - Verify that "EZCONNECT" is listed as one of the values of the
           NAMES.DIRETORY_PATH parameter in the Oracle Net profile
           (SQLNET.ORA).
           - Make sure the host, port and service name specified
           are correct.
           - Try enclosing the connect identifier in quote marks.
Error at Line: 1 Column: 30

I've been googling and attempting different solutions for 2 days with no luck. I've done all of the following:

Set PATH environment variable to C:\Oracle\instantclient_11_2
Set ORACLE_HOME environment variable to C:\Oracle\instantclient_11_2
Set TNS_ADMIN environment variable to C:\Oracle\instantclient_11_2\network\admin
Created a tnsnames.ora file with the correct connection information in C:\Oracle\instantclient_11_2\network\admin
Set the Tnsnames Directory field in SQL Developer (Tools > Preferences > Database > Advanced) to C:\Oracle\instantclient_11_2\network\admin

I am at a loss as to why these database links aren't working. Does anyone have any advice?


texts: Amazon Redshift: "*SELECT* 1" vs. volt_dt_0 vs. volt_tt_51343b6aa3bd4

texts:
I've noticed subqueries in Amazon Redshift can be represented in the explain plan in 3 separate ways:

-> XN Subquery Scan "*SELECT* 1"

-> XN Subquery Scan volt_dt_0

-> XN Seq Scan on volt_tt_51343b6aa3bd4

"SELECT 1" and volt_dt_0 seem to be the same thing, so:

Why the different naming convention?
If they're not the same thing, what is the difference and when is one used over the other?

volt_tt_51343b6aa3bd4 seems to mean the subquery has been executed in a CREATE TEMP TABLE statement. It seems conceptually similar to CTEs. A few questions here:

When is this chosen over "SELECT 1" or volt_dt_0?
The additional CREATE TEMP TABLE query shows up in SVL_QLOG as a separate record from the main query. Does the elapsed time in the record for the main query include the elapsed time for the CREATE TEMP TABLE subquery?
Canceling the main query doesn't seem to cancel the CREATE TEMP TABLE sub query. Is this true?
Why does running EXPLAIN require that the CREATE TEMP TABLE subquery to be executed? (maybe because of #7?)
It looks like statistics are being gathered on the temp table if STATUPDATE ON (padb_fetch_sample queries are being run). Is this true?


texts: What can cause slow planning times for "select 1"?

texts:
I have a database that is used by a Production application. It's an db.m3.medium on Amazon RDS. We've noticed that a lot of queries are starting to get slow that typically shouldn't be slow (e.g. select row by primary key).

We started to investigate the issue and found that our select 1 health check against the database is also slow. For example, here's some graphs about running explain (analyze buffers) select 1 every second over a ~20 hour period:

Duration (round trip time) (ms)

I've capped the y-axis at 500 ms.

Planning (ms)

I've capped the y-axis at 100 ms.

Execution (ms)

I've capped the y-axis at 100 ms.

It seems crazy that planning a query is taking such extreme times.

When we look at the database as a whole, there doesn't seem to be any signs that it's under heavy enough load. Here's a typical day:

CPU

Read (blue) / write (purple) throughput (MiB/s)

Network transmit (purple) / receive (blue) throughput (KiB/s)

Disk

The disk is 30 GiB and has 5.75 GiB used.

Memory

There is 3.75 GiB of RAM and 2.2 GiB is free.

The database instance is new as of December 17, 2017 (after a manual failover from another instance because of required AWS maintenance). Our hypothesis is that the single-core is causing the OS (and Postgres) to have issues context switching. However, none of the metrics of the database really point to the fact that it's running very hot. I can also confirm that we don't have any long-running queries/transactions.

What could be causing simple queries such as select 1 to have high planning and execution times?


texts: How to design two tables: A hasMany B, and one B per A has state 'active'?

texts:
Figure two tables: environments and branches.

Their relationship is: one environment has many branches. This is easy with an environment foreign key in the branches table.

The problem is adding this constraint: zero or one branch of the environment's branches can have the "active" status.

The system will be supporting these two requests:

Give me the branches of environment 'E'
Give me the active branch of the environment 'E'

A solution would be to add "activeBranchFK" in the environment table, but this produces a cyclic dependence between the tables and it looks not a good solution to play with that.

Another solution would be a boolean "active" in the branches table, but it seems to me that we can have undesired states if many branches of the same environment are active at the same time.

Did you ever have to model this pattern?


texts: How to design functionality to comment about a user joining a Facebook group?

texts:
User A joined the group DBA

The table responsible for users x groups is "Following" table.

    user_id      group_id
       1             1
       1             2
       2             1

And in my app I have a table Comments. the foreign key of it is polimorphic, because comments can happen inside posts, photos, etc..

      text       parent_type      parent_id
     "cool"         Post             1
     "nice pic"     Photo            2
     "welcome!"     Following        3

It is a good schema? The following table has_many comments?

And a more complex one would be: "How to comment on a set of people joining?"


texts: Unable to insert explicit value in table due to IDENTITY_INSERT is ON Error

texts:
I am trying to insert explicit values in one of my Real time Audit Production database. There are more than 80 columns, I have just taken here 5 columns.
I am writing syntax is as below:

use MyDatabaseName
go

set identity_insert ObjectName.TableName on

insert into ObjectName.TableName ([col1],[col2],[col3],[col4],[col5])
  values ('value1','value2','value3','value4','value5')

set identity_insert ObjectName.TableName off

After executing the above T-SQL statement, I am getting below mention error:

  Msg 8101, Level 16, State 1, Line 44
  An explicit value for the identity column in table 'ObjectName.TableName' can only be specified when a column list is used and IDENTITY_INSERT is ON.

I am sysadmin & db_owner. What is the problem?


texts: Selecting minimum value in top grouping only

texts:
I have a query built in Oracle 10 which produces a set of data like this:

    EMP_ID  WORK_DT  TIME_TYPE
---------- --------- ---------
     12345 19-DEC-14        10
     12345 18-DEC-14        10
     12345 17-DEC-14        10
     12345 16-DEC-14        20
     12345 15-DEC-14        20
     12345 14-DEC-14        10
     12345 13-DEC-14        10
     54321 19-DEC-14        10
     54321 18-DEC-14        20
[...]

and I'm looking for output like this, only showing the most recent time_type for each employee with the applicable START_DT:

    EMP_ID  START_DT   END_DT  TIME_TYPE
---------- --------- --------- ---------
     12345 17-DEC-14 19-DEC-14        10
     54321 19-DEC-14 19-DEC-14        10

The START_DT has been troublesome for me because the same TIME_TYPE can exist in multiple blocks in the input.

My background is more with other RDBMSs than Oracle. I've spent the last few days searching here and experimenting with Oracle's analytical, aggregate, and subquery functions to get these results. I feel like I'm close but I haven't had much luck.

I'm trying to keep this as an efficient set-based solution rather than resorting to a cursor or function.

Any help is very appreciated!


texts: SQL Server - Benefits of splitting databases across different logical drives

texts:
We're about to start a project to migrate a large DWH to new physical servers in a new data centre. The current server spec is SQL Server Enterprise 2016 SP2 running on Windows 2012 R2. The new servers will be MSSQL 2019 Enterprise running on Windows 2019.
SAN storage for the current and new servers is an all flash storage array. In the current environment as well as separating data and log files onto different logical drives, different databases (data files only) are also split across different logical drives.

Local SSD - TempDb
Logical Drive 1 - log files
Logical Drive 2 - data files for staging databases
Logical Drive 3 - data files for user facing databases
Logical Drive 4 - data files for support databases (ReportServer, MDS database)

As part of the server migration I am considering combining all data files onto a single logical drive.

Local SSD - TempDb
Logical Drive 1 - log files
Logical Drive 2 - data files

As well as database file management is there any performance benefits to keep the data files split across different logical drives? Does multiple logical drives give better IO, even though ultimately it's the same physical storage array?


texts:
I am reading this article and there doesn't seem a way to do something like this:

SELECT COUNT(*)
WHERE SHOW VARIABLES
WHERE Variable_name = 'innodb_ft_cache_size'

I can do the below but this doesn't work in MySQL 5.7 (No variables in table)

SELECT COUNT(*)
FROM information_schema.GLOBAL_VARIABLES
WHERE Variable_name = 'innodb_ft_cache_size' (always returns 0)

The reason I need to do this is to detect in a stored procedure if they have the ability to do full text. It needs to work in MySQL and variants of MySQL so I cannot just simply parse @@verison.

Is there another way to do this?

Here is a stored procedure that works on 5.5,5.6, and 5.7 to detect presence of full text abilities. This should also work on mysql variants

begin
set @supports_ft = 0;
set @information_schema_exists := (SELECT COUNT(SCHEMA_NAME) FROM information_schema.SCHEMATA WHERE SCHEMA_NAME = 'information_schema');

if @information_schema_exists > 0 then
    set @information_schema_exists_global_vars_exists := (SELECT COUNT(*) FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'information_schema' AND TABLE_NAME = 'GLOBAL_VARIABLES');
        if @information_schema_exists_global_vars_exists > 0 then
            set @supports_ft := (SELECT COUNT(*) FROM information_schema.GLOBAL_VARIABLES WHERE VARIABLE_NAME = 'innodb_ft_cache_size');
        end if;
end if;

if @supports_ft = 0 then
    set @performance_schema_exists := (SELECT COUNT(SCHEMA_NAME) FROM information_schema.SCHEMATA WHERE SCHEMA_NAME = 'performance_schema');
    if @performance_schema_exists > 0 then
        set @performance_schema_exists_global_vars_exists := (SELECT COUNT(*) FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'performance_schema' AND TABLE_NAME = 'GLOBAL_VARIABLES');
            if @performance_schema_exists_global_vars_exists > 0 then
                set @supports_ft := (SELECT COUNT(*) FROM performance_schema.GLOBAL_VARIABLES WHERE VARIABLE_NAME = 'innodb_ft_cache_size');
            end if;
    end if;
end if;


texts: SAN block size for SQL Server on 4KB NTFS

texts:
A potential customer wants to evaluate our storage system. They run Windows 2008R2 x64 with 4KB NTFS on a virtual test machine they've sent us. They did not seem to know this at hand, so I think it's reasonable to assume that environment was not tweaked. The tests are inserting, indexing, searching, deleting. Tool is not known to me.

Given that the windows NTFS block size is 4KB, and SQL write in 64KB chunks - is it safe to assume that a block size of 64k on the SAN is a good choice?

They run SQL Server 2008, perhaps standard.


texts: SELECT DISTINCT ON, ordered by another column


texts: Does a primary key contain exactly one Null value...?

texts:
My work

A primary key is unique and only one contains for a table.
There are many unique keys that can contain in a table.
In SQL Server, the primary key is not Nullable. But, a unique key may contain only one null value.

My question:

Practically in SQL Server, the primary key is not nullable. But when it comes in theoretical, why don't we can have exactly only one Null value in the primary key when it allows a Null value in the unique key?

Thanks for answering my question

Sorry for asking a general question, after reading the answer I got a clear idea about null values in DB.

Also, I confused with programming languages like in C++, Jave, where the null value can be comparable.
After doing some work, I understand that Null is not comparable in SQL.

  In programming languages like in C++, Jave...

Null==Null

returns TRUE

  But, in SQL

Null==Null

returns FALSE, because Null value is unknown and it could be any value.


texts: What's the difference between "expression <=1." and "expression <= 1"?

texts:
Reviewing code and a case statement has expression <= 1. as opposed to <= 1

I'm not sure what the purpose of the 1. is.  Any thoughts?


texts: How to view who gets kicked from my SQL Server Script?

texts:
I have a maintenance job in SQL that was failing because of an application that was holding a lock for a specific user. I'm now running a script before the step that runs the maintenance job to kick the user. Now the maintenance job runs without issues. I'd like to get output from the step as it runs so I can see in the job activity monitor what gets kicked. How to make the output more verbose?

--Create a cursor
declare mycursor cursor
for

-- Identify any server process id used by your user for the database. Update fields below as required. The username or database name can be removed if you wish to disconnect all users or a user from all databases.
select spid, Loginame, hostname
from master..sysProcesses
where HostName='johnpc' or loginame='browser' and dbid=db_id('approd')

open mycursor

declare @spid int, @loginame varchar(255), @cmd varchar(255), @hostname varchar(255)

-- For each row in the cursor, kill the process by spid
Fetch NEXT FROM MYCursor INTO @spid, @loginame, @hostname
While (@@FETCH_STATUS <> -1)
begin
    select @cmd = 'kill ' + cast(@spid as varchar(5))
    exec(@cmd)
    print @loginame
    Fetch NEXT FROM MYCursor INTO @spid, @loginame, @hostname
end

close mycursor
deallocate mycursor
go


texts: Can I move a system database from one version of SQL server to another?

texts:
We are moving our databases from one computer running SQL Server 2005 to a different machine running SQL Server 2008.

I am following the steps found here, in an attempt to copy over the master database, with all the user logins and server-level permissions, however it is telling me the backup cannot be restored because it was created by a different version of the server.

Is there a way to copy the master database from one version of SQL server to another version of SQL server?


texts: DNS CNAME for a SQL Managed Instance (Azure)

texts:
I currently have an on Prem SQL Instance that has a DNS CNAME set up against it via our On Prem DNS Server.

I am looking to move this server to an Azure Managed Instance and I want to point the DNS CNAME at this server (the MI is in a VNet that is connected to our internal network)

In my testing if I create a new DNS CNAME at the Manage Instance Host ServerName.dnsPrefix.database.windows.net it works from SSMS but not from any of our apps. I think it’s some sort of reverse lookup issue. If I point the App directly at the MI host (ServerName.dnsPrefix.database.windows.net) it works fine.

Is it not possible to point a on Prem DNS CNAME at an Azure MI?


texts:
Table t1 with 500k rows, 2TB in size, one text column. SQL Server 2008 R2 SP3.

CREATE TABLE [dbo].[t1](
    [TId] [int] IDENTITY(1,1) NOT NULL,
    [Weight] [int] NULL,
    [TDate] [datetime] NULL,
    [text_column] [text] NULL,
    [CreatedDate] [datetime] NULL,
    [UpdatedDate] [datetime] NULL,
    [LastUpdatedBy] [varchar](255) NULL,
 CONSTRAINT [PK_t1] PRIMARY KEY CLUSTERED
(
    [TId] ASC
)

This is running forever:

SELECT COUNT(*) FROM t1 WHERE text_column IS NOT NULL

Unable to change the table, can only fix this with new indexes. But can't create an index on the text column. No experience with full text indexes.

What should I do to improve it, please ?


texts: Update only changed columns with Mysql prepared statements

texts:
In the following procedure I have simple prepared "update" statement. I have some questions about using this.

Main question: How do I update only the changed columns in a stored procedure?  It seems like setting update on all columns will overwrite data if another user changes a different column at the right time.  In Zend Framework 2, I typically use TableGateway, which allows me to send in the form array and I think it will only update the columns that I have sent. How do I build a stored procedure to take a variable set to columns to update?
With the above in mind, I know I can build a statement with concat and skip the "prepare .. using" bound parameters, but if I do that, then I can't protect against SQL Injection, unless I build my own filtering functions. I'd really prefer to keep the bound parameters. How do I keep those, while still only building a statement that updates only changed columns?  Because I don't see how to change the USING portion to have dynamic input parameters.
Also, just a side question. Why is it in Mysql I have to make all those duplicate input parameters? ("set @col1 = v_val1")  Because I can't put v_val1 directly in the USING statement I have to make session variables. I think that really odd we can't put them in directly.
Last little question. Should I unset each session variable at the end of the procedure? "set @col1 = null" Wouldn't it be safer to clear those out? I do not use pooled connections now, but I may use this procedure from more than just PHP web server, and other systems may have pooled connections.

I'm probably missing something obvious in this, and/or I'm just asking too much. I do know there will always be some level of timing with user edits. That's not critical, and if it occurs in my current situation, I'll have a log to show users what and who changes things. But I do wonder what I can do at the Mysql procedure level to work around it. Also it seems like Mysql is really lacking in procedure capabilities compared to PostgreSQL or MSSQL, but I am stuck with Mysql for now because of backend system requirements.

Sorry about all the questions. Having hard time finding answers. Thanks so much for you help.

create procedure sp_update_mytable (
    in v_id int,
    in v_val1 varchar(10),
    in v_val2 varchar(10),
    in v_val2 varchar(10)
)
begin

    set @id = v_id;
    set @col1 = v_val1;
    set @col2 = v_val2;
    set @col3 = v_val3;

    set @sql = 'UPDATE mytable
                SET
                col1 = ?,
                col2 = ?,
                col3 = ?
                WHERE id = ?';

    prepare stmt from @sql;
    execute stmt using @col1, @col2, @col3, @id;
    deallocate prepare stmt;

end$$

Edit: A little background.
It occurs to me I may be trying to solve this problem the wrong way or wrong place. Here's why I am asking. In ZF2 I have TableGateway,and I can send just an array to update and only the columns I want to update. Its super easy and convenient. Example:

$result = $this->db->update('mytable',
            array('col2' => 'my new value 2'),
            array('id' => 2)
);

I'd like to make a stored procedure to do this update, but I can't send only the columns I want.  In one system I may want to send col1 and col3, in another, col1 only.  The columns to update are dynamic.

We are building systems that will integrate more than just a web server, which will include some ODBC, some Node.js maybe, possibly others. I wanted to be open to use other systems by building a Stored Procedure to handle certain table updates.

What's occurring to me now is that maybe I can't do this with Stored Procedures. Maybe I need "Middleware" to solve this.  However, the question still stands, is this possible to do in Mysql.  (if not, PostgreSQL?)


texts: Full-Text Search against serial numbers?

texts:
Using Dynamics 365 with MSSQL. There is an option to enable Full Text Search, however once it is enabled we cannot search by partial serial numbers.

For instance, KN567228 shows in the full text catalog but trying to search for 7228 will return no results. The Stoplist is empty, so that isn't the cause. This is a unique situation for Full-Text Search because there really isn't a thesaurus or wordbreaker that could break down serial numbers in a meaningful way. But I thought the search would work for partial text (like searching for 7228 in KN567228).

Should I just not be using Full-Text Search in this scenario?


texts:
Since few years ago I have worked and extending a application build by Forms Builder 10g and running via Oracle Application Service 10g. The application can be launched as:

http://portal.dev:7778/forms/frmservlet?config=db

Now, we want to take a new step migrating our forms to the cloud an be accessed from anywhere. My first task was finding some resource into Oracle services, and I found Java Cloud Service - SaaS Extension:

  This is the number of Oracle WebLogic Server Managed Server instances to which the applications are deployed (customer managed Java EE application container)
  Preconfigured Oracle WebLogic Server 12c or 11g cluster configured with Oracle Database Cloud Service
  This is the amount of RAM allocated to the Java heap for all of the service's Managed Servers combined to run the Oracle WebLogic Server Managed Servers and the objects consumed by your application code

So, I decided to try the free month there. Successfully the account was created and I can navigate between all services listed there. But the real question is, which of all services there should I need to open? My first impression is to start with the "Java":

But I would like to take your advice, because I am not sure at all. Please, let me know if more details are needed.


texts:
I need to analyze the client's data and for that requirement client has provided us only ibdata1 file having size around 150MB.
I am not aware of table structure and number of tables as of now. After some research I found that I will need .frm files in order to recreate the tables.
I have below questions:

Is there a way I can restore the database with only ibdata1 file?

Shall I need any other files apart from .frm files and ibdata1 file in order to restore the database?

Suppose I can restore the database using just ibdata1 file or I get .frm files in future, Do I need to install a version that matches the client's MySQL version?

Thank you!


texts:
I was trying to take backup using oplog based on ts using below code

 import pymongo
    import traceback
    from pymongo.cursor import CursorType
    c = pymongo.MongoClient('mongodb://localhost:28001')
    oplog = c.local.oplog.rs
first = next(oplog.find().sort('$natural', pymongo.DESCENDING).limit(-1))
ts = first['ts']
while True:
    cursor = oplog.find({'ts': {'$gt': ts}}, cursor_type=CursorType.TAILABLE_AWAIT, oplog_replay=True)
    while cursor.alive:
        for doc in cursor:
            ts = doc['ts']
            print doc['ns']
            db,collection=str.split(str(doc['ns']),'.',1)

It is working well when we have less opLog size but as i am using it on production where oplog size is 48 GB around it is not working.

I cannot alter oplog size.Please suggest some way to take incremental backup.


texts: How do I add values to partition function and scheme?

texts:
We have a partition function defined as

CREATE PARTITION FUNCTION [PartitionByPeriodFunction](int)
AS RANGE LEFT
FOR VALUES (20101228, ... , 20161227)

where each date is the first date of our fiscal period.

We have a partition scheme defined as

CREATE PARTITION SCHEME [PartitionByPeriodScheme]
AS PARTITION [PartitionByPeriodFunction]
TO ([FactsBefore2011], ..., [FactsP201612], [FactsAfter2016])

I want to add more periods to the partition function and more filegroups to the partition scheme - what is the best way to do this?

I have read to drop and recreate the function and scheme - will the dependent objects allow that? I am unsure how to use split since I am not wanting to split 20161227 but rather continue the pattern.


texts: MySQL: Using DISTINCT and GROUP BY together?

texts:
I saw the following MySQL query that that uses both DISTINCT and GROUP BY together:

SELECT  DISTINCT user_id, post_id, post_content
    FROM  some_table
    GROUP BY  post_id, user_id
    HAVING  post_content LIKE '%abc%';

Here is a scenario to go along with the query: Each user has a unique id, user_id, and can make multiple posts which are identified by a unique id, post_id. Each post would contain some text.

I found this confusing(after coming from Oracle DBs) and had below questions:

What is the meaning of using GROUP BY without doing any aggregation?
What is the significance of switching the order of columns in SELECT vs in GROUP BY?
What is the meaning of omitting the third column from GROUP BY?
Why is DISTINCT used along with GROUP BY? Does distinct operation run after all the groupings are done on the final result or before?


texts: SQL Server cluster resource won't come online unless its on port 1433

texts:
The 2-node SQL Server 2008 R2 cluster works fine until one of the resources got removed. I successfully added it back. However, the cluster is not able to online the SQL Service unless it is on 1433 and not the port I was using before. Below is the application error log:

  [sqsrvres] checkODBCConnectError: sqlstate = 08001; native error = 274d; message = [Microsoft][SQL Server Native Client 10.0]A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.

The service status becomes Pending Online and after a while, it becomes offline. Using the SQL Configuration Manager, I revert to 1433 and it works fine again.

I have tried disabling checkpointing of the quorum (How to Change the Dynamic Port of the SQL Server Named Instance to an Static Port in a SQL Server 2005 Cluster) but nothing works unless I use an alias with the old port number.

Am I missing something here?


texts: Find highest sold item name for each date

texts:
I have three tables

table 1: items (int,itemName)
table 2: orders(order id,oDate)
table 3: orderDetails(orderId, itemId, itemQnt)

Here is the complete schema with some demo values

What is the best way to find top sold itemName for each day.
Output would be like date,itemName.


texts:
I am getting the below error from my .net application.

Source: xxx.Services.xxx.xxx.xxxx.xxxx.xxxxRequest
    ---
    System.Data.Entity.Core.EntityException: The underlying provider failed on Open. ---> System.InvalidOperationException: Timeout expired.  The timeout period elapsed prior to obtaining a connection from the pool.  This may have occurred because all pooled connections were in use and max pool size was reached.

I have seen several posts regarding this connection pool error.I have gone through most of it and i understand why it is happening and the possible solutions from on the application side like closing the connections opened by application.
These is what i understood :

Opened connections are not closed, called connection leaking.
Morethan 100 concurrent connections are being used. Increase the max
    pool size in the connection string if we have more than 100
    simultaneous users.
Slow queries or open transactions blocks new
    connections. When the connections are being held open by slow query
    execution/open transactions and instead of being reusing connections
    the new connection are open and eventually reaches the connection
    pool maximum.

How can i monitor this from the database side.I saw some suggestions like use sp_who or sp_who2 to see the existing sessions.When i execute sp_who2 when i see these errors,i see lots of active sessions,but how can i see the connection pool information and the connections which comes under the pool.
Questions:

Is there any way to track the connections opened and relate it to a
connection pool created from database side.

I have noticed that when the issue happens,the queries which normally runs faster ,goes slow.

Is it expected?Because i am suspecting whether the slow queries are
causing the connection pool errors.


texts: Does Postgres CHECKPOINT process affects streaming replication?

texts:
Does Postgres CHECKPOINT process affect async streaming replication in any way?
If checkpoint_timeout is increased from 5min (default) to 30min, then CHECKPOINT processes will be triggered less often (some other parameters needs to be adjusted as well). If primary node crashes, then it will probably take more time to recover. But I have Patroni configured and it will failover primary to the other node in case of crash.
My question is - will failover last longer if checkpoints on old primary node are triggered less often?
I think it should not affect failover process, because WAL records are already replicated and applied on secondary node, but I want to confirm that.


texts: When interrupting a query on a Master, does it replicate to the Slave?

texts:
In MySQL, when does the master send a query to the slaves?

Here's a specific scenario that I'm trying to understand.

Admin entered an update on master that took a long time to execute.
Admin hits Ctrl-C to stop the query.

I see four scenarios that may have happened:

Nothing happened on the slave, because master waits until the query is finished executing before it sends the query to the slave.
A huge update happened on the slave, because the master passed the query along immediately, and didn't pass the ^C along to the slave.
The same update happened on the slave as the master, because the master passed the query along immediately, and also passed the ^C along to the slave.
The same updates happened on the slave as the master, because the master passed each individual update along to the slave.

Which one of these actually happened?


texts: Why is this sqlite query much slower when I index the columns?

texts:
I have a sqlite database with two tables, each with 50,000 rows in, containing names of (fake) people. I've constructed a simple query to find out how many names there are (given name, middle initial, surname) that are common to both tables:

select count(*) from fakenames_uk inner join fakenames_usa on fakenames_uk.givenname=fakenames_usa.givenname and fakenames_uk.surname=fakenames_usa.surname and fakenames_uk.middleinitial=fakenames_usa.middleinitial;

When there are no indexes except on the primary keys (irrelevant to this query), it runs quickly:

[james@marlon Downloads] $ time sqlite3 generic_data_no_indexes.sqlite "select count(*) from fakenames_uk inner join fakenames_usa on fakenames_uk.givenname=fakenames_usa.givenname and fakenames_uk.surname=fakenames_usa.surname and fakenames_uk.middleinitial=fakenames_usa.middleinitial;"
131

real    0m0.115s
user    0m0.111s
sys     0m0.004s

But if I add indexes to the three columns on each table (six indexes in all):

CREATE INDEX `idx_uk_givenname` ON `fakenames_uk` (`givenname` )
//etc.

then it runs painfully slowly:

[james@marlon Downloads] $ time sqlite3 generic_data.sqlite "select count(*) from fakenames_uk inner join fakenames_usa on fakenames_uk.givenname=fakenames_usa.givenname and fakenames_uk.surname=fakenames_usa.surname and fakenames_uk.middleinitial=fakenames_usa.middleinitial;"
131

real    1m43.102s
user    0m52.397s
sys     0m50.696s

Is there any rhyme or reason to this?

Here's the result of EXPLAIN QUERY PLAN for the version without indexes:

0|0|0|SCAN TABLE fakenames_uk
0|1|1|SEARCH TABLE fakenames_usa USING AUTOMATIC COVERING INDEX (middleinitial=? AND surname=? AND givenname=?)

This is with indexes:

0|0|0|SCAN TABLE fakenames_uk
0|1|1|SEARCH TABLE fakenames_usa USING INDEX idx_us_middleinitial (middleinitial=?)


texts: One-to-many relationship on a “‘junction’ table”

texts:
I have two entity's - Workout and Exercise. These will have a 'many-to-many' (M:N) relationship through the junction table WorkoutExercise. Things start to get complicated when I need to have a 'one-to-many' (1:M) relationship on this table from the table called Sets.

I have provided the entity-relationship diagram below to help illustrate the scenario:

Is this poor design? i.e. is it bad for performance? is there a better way?


texts:
I need to determine the dates which are the "3rd Friday of each month" for a date range of "1.1.1996 - 30.8.2014" in SQL Server.

I expect I should use a combination of DENSE_RANK() and PARTITION BY() to set "rank = 3". However, I am new to SQL and unable to find the correct code.


texts: Getting records for yesterday, but per hour

texts:
I am currently getting all data for yesterday from my database but I want to get data for each hour. This is my query:

select
    camera_id,
    count(*) as snapshot_count
from snapshots
where created_at >= TIMESTAMP 'yesterday'
  and created_at <  TIMESTAMP 'today'
group by camera_id

It's giving me all records for yesterday, but how could it be possible to get records from yesterday but for each hour?

PS: I want to run this in one query, not 24 queries


texts:
I am looking for a solution to [dramatically] improve an enterprise database performance on a system that needs to support thousands of transactions (inserts & large selects) per second. Besides the database/queries optimization, we are also looking for hardware improvements, too.

I need someone with experience in database administration that used SSD drives to store databases. What RAID configuration did you use? RAID1 looks like a good choice maybe combined with another redundancy-focused configuration, also SLC technology would be preferred for SSD drives I suppose.

The questions are: what type of SSD drives are you using (make, model)? How are they organized in RAIDs, what improvements did you achieve and how many (major) issues did you encounter? How much are you worried about the reliability of current SSD drives? Would you recommend using them for database (meaning critical) storage?

Also, what was the approximative price (at the time you implemented the solution, of course) of the implemented solution?

Maybe this post can also provide hystorical data about how SSD+database relationship has evolved over the time.

PS: If important, we are currently using SQL Server 2008 R2. Database size: over 400GB at this time, but my question is not only related to my specifics.

Thanks.

An interesting article found here: http://www.remote-dba.net/t_in_memory_cohesion_ssd.htm


texts: MSSQL - find immediate past iteration of field

texts:
Bit of a difficult to explain one here, I have a table with product SKUs of different generations (or iterations) and I need to identify the most recent iteration that exists for each.
Timeline: Product X gets entered into the table, after a few months product X gets a revision and X(B) appears and is entered, sometimes iterations or revisions get skipped in this table. This is always done in an alphabetic ascending way - (B) is the first iteration, (C) follows, etc.
I need to identify what the immediate past iteration is for every one of these.
Example data set in column A and desired result in column B - can this be achieved or is it an impossible task?
Thank you in advance.
SKU; previous iteration (if applicable);
ABCD; ;
ABCD(B); ABCD;
ABCD(E); ABCD(B);
ABDX(B); ABDX;
ABDX(F); ABDX(B);
ABDX; ;
BCDF(P); BCDF(C);
BCDF(C); ;
BCDX; ;
BCDX(B);BCDX;
BCDX(G);BCDX(B);


texts: Splitting large database into smaller ones

texts:
Our system currently stores all customer (merchant) accounts in one "flat" MySQL (5.6) DB namespace. We would like to scale out better - we are considering breaking our data up based on the merchant account ID. So instead of having:

use database single;
table sales (
  `account_id`
  ...
)

Break up merchants into separate namespaces:

use database <account_id>;
table sales (
  ... // stores data  for a single account
)

Above has several benefits:

Accounts can be placed on different servers and scale out much better. We can decide how many accounts to co-locate, and migrate as needed.
Individual account backup/restore times would be greatly reduced.
Ability to upgrade one account's database at a time (more granularity).
One bad user won't affect all accounts.
etc.

Concern:
Today, all of our DB access is via stored procedures - both reads and mutations. We would like to keep it that way as it isolates the DB schema from the app code. If we were to break up the accounts into separate db-namespaces the dot-notation used inside of the stored procedures cannot be parameterized, as far as we can tell. We would have to duplicate the stored procedures (~150 count) for each of the account-namespaces. Consequently if we need to make a change to one SP, we need to apply it to all of the namespaces' stored procedures. In fact, any DB schema change needs to be applied (via automation) to all the DB namespaces. The administrative overhead is slightly scary, even after automating the deployment of changes.

Any alternative/better solutions for scaling out?


texts: Why does this query cause the Postgres worker process to consume so much memory?

texts:
I have a PostgreSQL 9.3.4 instance running in a CentOS 5.x VM (Linux 2.6.18-308.8.2.el5xen) with 6GB RAM. I've set shared_buffers to 1GB and work_mem to 1MB (a low value for testing). Here are my custom settings.

I'm trying to figure out why a certain (admittedly nasty, it's generated by a Java application) SQL query is taking so much memory beyond the 1GB RES I'd expect to see in top. I understand that there's no direct way to cap how much memory a worker process will use, but you adjust work_mem to make it spool large sort operations and hash tables off to disk to indirectly control it. Now, the D_MC108 table in the query is "big" (12GB), but I thought Postgres would just deal with this by going through the buffer cache and spooling to disk as needed. Slow would be preferable to consuming all memory on the box. (By the way, the devs are off changing this query, but it remains quite a mystery at this point).

So when the app in question runs that query, I see in top that its RES ramps up pretty quickly to the 1GB mark (which is expected, I get that the worker process eventually maps the entirety of shared_buffers into its own address space). However it then continues to grow seemingly without bound, as you can see in top here:

Eventually it consumes all available memory and Linux's OOM killer wakes up and starts killing things.

My question is really simple: I thought the a postgres worker's RES usage could be explained as the sum of shared_buffers (if it's mapped the entirety of the buffer cache) plus some multiple of work_mem, which I understand could be a "a lot", based on the number of sorts and joins, and there are a bunch in this query. But to explain an additional 2.8GB of memory usage (3.8GB - shared_buffers) as seen in top above, that'd be 2,800 times my work_mem of 1MB. Is that nasty query I linked to capable of using 2,800 times work_mem? Or is my understanding incorrect? Here is the EXPLAIN (I can't run EXPLAIN ANALYZE for obvious reasons).

Thanks so much.

Edit: okay, sometimes it just helps to write it all down to figure it out. I think I figured it out at least. I noticed a bunch of Materialize steps in that EXPLAIN plan. Just to test I tried set enable_material=false; and found the process never went over 1GB RES. The plan was even crazier of course. So it was the Materialize steps, and whatever memory that uses is what was consuming all the memory. I can't find any way to limit that, please post something if you know. So for now, I'm at least relieved now knowing what mysterious force was at work here. The devs can fix up the query and we'll move on.


texts:
I need help getting multiple rows of data to all pull into one row based on a primary key. I have four teachers rating a student's academic and behavior performance through the year. At the end of the year, I'll have one spreadsheet per grade that's going to look something like this:

ID   First   Last   Subj   ARate   Expl              BRate   Expl
--   -----   ----   ----   -----   ---------------   -----   ---------------
345  Kris    Bell   Eng    5                         3       Talks a lot
345  Kris    Bell   Math   3       0% on EQ test     2       Disruptive
345  Kris    Bell   Sci    4                         4
345  Kris    Bell   SS     2       Failed 2 tests    4
286  Manny   Joy    Eng    5                         5
286  Manny   Joy    Math   4                         5
...

I want to create one table that compiles all 4 rows into one row. Something like this:

ID   First   Last   Eng_A   Expl              Eng_B   Expl             Math_A   Expl              Math_B
--   -----   ----   ----    ---------------   -----   ---------------  ------   ---------------   -----
345  Kris    Bell   5                         3       Talks a lot      3        0% on EQ test     2
286  Manny   Joy    5                         5                        4                          4
...

There will be separate columns for each subject with explanations if applicable-- I just didn't have room to include all four subjects in this StackExchange question. I have ideas about how to do this in Excel, but I also know some SQL so I'd like to learn how to use Access to do this. I thought about creating a database with one table per grade, then creating one table that pulls a one-to-one relation from each subject. Could I write some SQL code to do this? Is there an easier way besides one-to-one joining?


texts: I have two tables, which are very similar, in MySQL.  I want to combine them into a single table with new column names.  Some of the column names in Table1 or Table2 may not be used in the new combined table.  The data types for the similar columns (the ones which will be combined) are the same. I also want to be sure to capture any duplicates from both tables.  Each table has it's own auto-incremented primary key.


texts: Creating a table with many optional fields

texts:
I am a developer (student) looking for some advice on what best approach should I follow while creating a table to model an entity that has a number of optional fields.

There exists a need to model an Organization entity with a few key fields such as id and name. There is also a join table from Users to Organization that specifies who belongs to the Organization and what their role(s) are.

The question I have pertains to a number of optional fields that belong to the Organization, such as website, email, and social links. Below are my ideas of approaching the problem thus far:

Add them to the table as optional fields

Pros: Easy CRUD on one table, faster than navigating joins, etc.
Cons: It seems a bit dirty to me. Might it become difficult to do migrations in the future?

Create a contact_information table that references an organization_id, references a contact_type_id (of website, email, Facebook, etc.) from a lookup table, and has a generic value field for the actual content.

Pros: feels cleaner, allows for an arbitrary number of contact types in the future
Cons: probably a lot slower. Tons more tables.

I am leaning towards #2, because it is a similar approach to what I'm doing for physical addresses, but I'm unsure whether or not it is the best solution as DBA is not my forte. If there is a 3rd or even 4th option that I am unaware of, I'd be really interested to hear of those as well.


texts: Postgres pg_dump copy table to another database isn't copying columns

texts:
Goal: copy a single table including all columns from one database to another using psql on the command line, avoiding an issue with XML from QGIS layer styles.

I am running the following on the command line

pg_dump -t table_to_copy source_db | psql target_db

as shown in places like this.

The table_to_copy is public.layer_styles generated from QGIS containing layer styles.

The command seems to be copying the table_to_copy to the target_db just fine, but when I look in table_to_copy itself in target_db, there are no data, just empty columns.  The existing table from which I'm copying has 13 columns, each with 6 rows of data.

I'm getting the following error during this operation:

ERROR:  invalid XML content
DETAIL:  line 1: StartTag: invalid element name
<!DOCTYPE qgis PUBLIC 'http://mrcc.com/qgis.dtd' 'SYSTEM'>
CONTEXT:  COPY layer_styles, line 1, column styleqml: "<!DOCTYPE qgis PUBLIC 'http://mrcc.com/qgis.dtd' 'SYSTEM'>

This suggests an issue with the XML from QGIS.  I've seen some responses like this suggesting using SQL in pgAdmin to avoid this, but I want to do this on the command line so I can script it.  Can this be done on the command line with pg_dump, pg_restore or some other method?

I'm running Postgres 10 on macOS 10.13.6.


texts: How can a select max query return no rows?

texts:
I have a process that selects the maximum date from a Greenplum (PostgreSQL 8.2) table, and coalesces it to a default value. Once, and once only, this query returned 0 rows. How is this possible? Even if the table were emtpy, which it was not, "select max" should return a single row with a null value.

SELECT COALESCE(MAX(load_dt AT TIME ZONE 'UTC'),'1901-01-01 00:00:00'::timestamp) FROM schema.tablename

The process is running in WhereScape on MS SQL Server, connecting to Greenplum using ODBC and loading the result into a SQL Server table.

We have a lot of ODBC failures, which the system detects and recovers from, including this step where we have seen ODBC connection failures that were correctly detected and the job failed. This time, no error, just "0 rows" in the job log.


texts: How would I access a system view in SQL Azure master database without logging into master?

texts:
My service needs to periodically read sys.resource_stats which is only in "master" database of SQL Azure server. The service currently only accesses a "regular" database in the same server. Accessing "master" requires adding a "user" in "master" which will be able to login there.

Adding a new "user" to "master" feels unsafe - it's not trivial to validate which privileges a "user" has, so it'd be better to not have any users except "admin" role users in "master".

SQL Azure doesn't allow referencing views/tables in other databases, so creating a view in the "regular" database which references a view in "master" is not an option.

Is there a way to reasonably solve this problem?


texts: How to count hierarchical rows count with a CTE

texts:
Suppose that I have two tables Region and Devices.  Region provides tree structure (by self referencing a ParentRegionId foreign key).  Every device belongs to one region (by the RegionId foreign key).

See this fiddle for a better understanding of the tables.

I need to count 'Region''s devices including nested ones similar to this:

+---------+------------+--------------+
| RegioId | RegionName | DevicesCount |
+---------+------------+--------------+
|       0 | Region0    |            5 |
|       1 | Region1    |            2 |
|       2 | Region2    |            2 |
|      11 | Region11   |            1 |
|      12 | Region12   |            0 |
|      21 | Region21   |            0 |
|      22 | Region22   |            1 |
+---------+------------+--------------+


texts: How to solve multiple minimal covers for functional dependencies?

texts:
I have the schema: a,b,c,d,e,g and the following functional dependencies:

b->c, dg->ce, bc->dg, e->a, g->bd

for which I want to find the minimal cover. It seems that more than one scenario is possible.

First I represent the dependencies by splitting the right-hand side:

b->c, dg->c, dg->e, bd->d, bc->g, e->a, g->b, g->d

For dg->c there's one extraneous attributed. Same goes for dg->e.

For bc->d the attribute c is extraneous as well as for bc->g.

For g->bd the attribute d is extraneous because if we have g->b then g+ = {gbcd} which contains d.

So we have 4 possible minimal covers:

b->c, g->c OR g->e, b->d OR b->g, g->b, e->a.

The correct answer is g-->c; g-->e; b-->g; e-->a; g-->b; g-->d according to this tool.

I cannot get the same answer from simplifying my minimal cover. What am I doing wrong?


texts:
I am using Access 2016 and building an in-house HR app for my team.

I have a Position  Table, an Employee Table, a Position_Employee Table and a Staffing Table

Position Table
-------------
position_id autonumber
position_number
position_title
group_id FK
level_id FK

Employee Table
-------------
employee_id autonumber
last_name
first_name
dob

Position_Employee Table
-------------
position_employee_id autonumber
start_date
end_date
is_indeterminate
is_current
position_id FK
employee_id FK

Staffing Table
-------------
staffing_id autonumber
reference_number
hr_confirmation
start_date
end_date
staffing_status_id FK
staffing_type_id FK
position_id FK
employee_id FK
hiring_manager_id FK

My logic is based on the position as more than one employee can occupy the same position.

The logic is the following:
 - An Employee can have a indeterminate position
 - An Employee can also have a determinate position while keeping their indeterminate position
 - An Employee can only have a determinate position
 - A Position can have an indeterminate Employee
 - A Position can also have a determinate Employee while having an Indeterminate employee
- A Position can also have a determinate Employee and no Indeterminate employee (i.e vacant)
 - The Staffing table is where the different staffing processes goes so an employee can be hired and put in a position as determinate, indeterminate and so on.

I was told not to use flag as it could become a nightmare to maintain later on so technically, in my table Position_Employee, I shouldn't have is_indeterminate, is_current.

What I want to do in pseudo-code is the following:

Trigger on Staffing table
If Insert in Staffing table
    If Staffing.staffing_status_id = 1  * staffing_stadus_id = 1 = Completed *
        Insert New position_employee_id in Position_Employee table
        position_employee.position_id = staffing.position_id
        position_employee.employee_id = staffing.employee_id
        position_employee.start_date = staffing.start_date
        position_employee.end_date = staffing.end_date
    If staffing_type = 1 *staffing_type = 1 = Indeterminate*
        position_employee.is_indeterminate = 'yes'
    If staffing_type <> 1
        position_employee.is_current = 'yes'

I want to be able later on to differentiate, the indeterminate position of an employee but also all the previous position that employee had occupied and be able to pull the employee indeterminate and current position which can be the same but can also be different.

Does this logic makes sense? if yes, how can I do this best?


texts: MySQL Innodb - Truncate Partition. Does it reclaims space?

texts:
Once a month I need to remove all the old data from the table. Previously I was copying all the needed data to another table, then drop the original table and rename the new table as original. When I ran drop table the space is reclaimed.

But now I am deciding to use Partitioning in the table. So my question is when I will do:

ALTER TABLE mytable TRUNCATE PARTITION partition

Will it reclaims the space? If it does not reclaims the space then am I left with only choice to OPTIMIZE the table or is there anything else I am missing? Since doing OPTIMIZE PARTITION on innodb table will lock the whole table.


texts: How to create a view from a join with multiple duplicate column names?

texts:
I have a few tables with this sort of format

myTable: id | Table1_id | Table2_id | Table3_id | value

Table1: id | text | ...

Table2: id | text | ...

Table3: id | text | ...

I want to create a view that replaces the ID's with the associated text. I can get that using 3 join statements like shown:

SELECT table1.id, table2.id, table3.id, value
FROM myTable

INNER JOIN Table1
  ON Table1_id = Table1.id
INNER JOIN Table2
  ON Table2_id = Table2.id
INNER JOIN Table3
  ON Table3_id = Table3.id;

When I try to create a view using

CREATE VIEW myView AS (

SELECT table1.id, table2.id, table3.id, value
FROM myTable

   INNER JOIN Table1
     ON Table1_id = Table1.id  -- I tried as t1 but that doesn't work
   INNER JOIN Table2
     ON Table2_id = Table2.id
   INNER JOIN Table3
     ON Table3_id = Table3.id
);

I get an ORA-00957: duplicate column name error, which makes sense, they all have the same id column. How do I use an alias or something similar so I can create the view which has a format like this:

myTable: id | t1 | t2 | t3 | value

where tn is the column name that contains tn.text.


texts:
I'm trying to install SQL Server 2012 on Windows Server 2012 Standard and got this message:

SQL Server Setup has encountered the following error:

The resource 'Microsoft.SQL.Chainer.PackageData.Package.xml' failed to load
from 'E:\PCUSOURCE\x64\Microsoft.SQL.Chainer.PackageData.Package.dll'.

Error code 0x85650003.

I applied the latest updates, but I still get this error. What should I do?

Summary text below:

--------------------
Overall summary:
  Final result:                  Passed
  Exit code (Decimal):           0
  Start time:                    2015-01-28 18:18:18
  End time:                      2015-01-28 18:18:37
  Requested action:              RunRules

Machine Properties:
  Machine name:                  WINDOWS191
  Machine processor count:       2
  OS version:                    Future Windows Version
  OS service pack:
  OS region:                     United States
  OS language:                   English (United States)
  OS architecture:               x64
  Process architecture:          64 Bit
  OS clustered:                  No

Product features discovered:
  Product              Instance             Instance ID                    Feature                                  Language             Edition              Version         Clustered

Package properties:
  Description:                   Microsoft SQL Server 2012
  ProductName:                   SQL Server 2012
  Type:                          RTM
  Version:                       11
  SPLevel:                       0
  Installation location:         C:\en_sql_server_2012_enterprise_edition_with_sp1_x64_dvd_1227976\x64\setup\
  Installation edition:          Enterprise

User Input Settings:
  ACTION:                        RunRules
  CONFIGURATIONFILE:
  ENU:                           true
  FEATURES:
  HELP:                          false
  IACCEPTSQLSERVERLICENSETERMS:  false
  INDICATEPROGRESS:              false
  INSTANCENAME:                  <empty>
  PID:                           *****
  QUIET:                         false
  QUIETSIMPLE:                   false
  RULES:                         SCCCheckRules
  UIMODE:                        Normal
  X86:                           false

 Configuration file:            C:\Program Files\Microsoft SQL Server\110\Setup Bootstrap\Log\20150128_181817\ConfigurationFile.ini

Rules with failures:

Global rules:

There are no scenario-specific rules.

Rules report file:               C:\Program Files\Microsoft SQL Server\110\Setup Bootstrap\Log\20150128_181817\SystemConfigurationCheck_Report.htm


texts:
There's a PostgreSQL PITR system running on a fairly large system, with hundreds of databases with hundreds of tables in each, and we're running into time constraint-based problems when trying to restore individual tables from a single db, or a set of dbs. PITR is being managed with Barman.

With this setup, we need to restore a whole cluster (which could contain dozens of dbs), then extract the single table and then restore the data - which can take hours.

Statistically, because of the number of dbs involved and the number of people involved, this happens often enough that it's becoming a problem.

Are there faster solutions which would give us PITR-like features? I've thought of doing manual pg_dump backups for every table individually every hour, but maybe there are better solutions?


texts: How can I design a database that easily lets me recreate historical snapshots

texts:
I'm designing a database for an accounting system. In the system there are companies, and each company has many contracts.

Each contract gives a company access to products, data types, or both. Clients use the different products to access different data types.

The initial contract may give the company Products A and B with access to data types X and Y. A second contract may come along and give the company access to data type Z within Product A. And so on.

It is important for the users of this system to be able to move through the contracts and see the state of the company's products and data type access, as of different contracts.

I am trying to think of a good approach to the architecture, and I've come up with two options:

Store the latest state of a company's products and data types in a products and data_types table. These would belong directly to the company. Each new contract would update these tables appropriately. So, querying the current state of a company's access would be a simple SQL query.

Then, in a separate table, I could store the contracts, and each contract would have many contract_items. This is where I could record which product or data type the contract is adding, updating or removing.

I could then write the logic in my software layer to recreate historical snapshots, using this contract_items table. I would simply pull all the items for a company up to the selected contract, then use the items to build up the state of the company as of that contract.
Store a snapshot of the company as of each contract. I am actually having trouble thinking through how I could do this in the database layer. One way would be to create a new rows in the companies, products and data_types tables for each contract.

This would make querying the state of the company as of any particular contract a simple SQL query. I would just select out of those three tables where contract_id equaled the current selected id. However, it seems like a lot of data duplication. Perhaps this is not a problem.

Any thoughts on this? Are there best practices in this area, or something I'm missing? Thanks in advance.


texts: How to format documentation in MS_Description extendedproperties?

texts:
I'm using a 'MS_Description' extended property to document parameters in a stored procedure.  This parameter's documentation requires more structure than just plain text.  That is I would like to describe: "if value = x, then action A is taken; if value = y, then action B is taken, ...".

What is the best way to store this structure?

The following approaches occured to me:

Use html within the 'MS_Description', e.g.

EXEC sp_addextendedproperty
    @name = N'MS_Description',
    @value = N'
        Selects the foobar.  Can have the following values:

x

does A

y

does B

'
    @level0type = N'SCHEMA',      @level0name = [dbo],
    @level1type = N'PROCEDURE',   @level1name = N'SE_DemoProc'
    @level2type = N'PARAMETER',   @level2name = N'DemoPar';

Would developpers DBAs automatically parse html in their heads?

Use visual studio xml document tags the extendedproperty, @value would become:

@value = N'
        Selects the foobar.  Can have the following values:
        <list type="bullet">
             <item><term>x</term><description>does A</description></item>
             <item><term>y</term><description>does B</description></item>
        </list>'

Does SSMS, visual studio, and/or other tools interpret Xml document tags in MS_Description extended properties?
Does extra semantics of '', '', and '' improve people's understanding more than the html?

Use a different extended property to document the values that a parameter excepts?

Which property is commonly used for this?
How to encode these multiple values?

As far as I know, an extended property can only occur once for a given (schema, procedure, parameter)-combination.

Using newlines, tabs, and '*' to format the plain text will not work since whitespace is collapsed from sql scripts.

does escaping newlines and tabs work?

Perhaps, I should avoid the mode parameter altogether and split this stored procedure into several distinct precedures.  (But that would be a different question and out of scope for this one.)


texts: grouping rows of common values to create a new 'group id' for each set

texts:
I am trying to achieve the below but was not able to so far, any help would be greatly appreciated.
I have this data (sorted from a query by id, anchor, date, and time) that I wish to group by common anchor :

id anchor date        time     'group' (the value to get)
3  2      2019-01-01  07:00     1
4  2      2019-01-01  08:00     1
5  3      2019-01-01  15:00     2
7  3      2019-01-01  16:00     2
10 3      2019-01-01  17:00     2

I'm looking to do a query in postgres where I can select this data and foreach set of common anchors, have a 'group number'
I then need a query to sum the anchor of points of same group, example above would become:

anchor sum group
2      4   1
3      9   2

thanks!

EDIT: McNets solution works perfect,
I have another case, with below data.
The anchor repeats but after a change of anchor: they're sorted by time, first it was anchor 2, then anchor 3, then again anchor 2.
I need to group after the change (ids 11 & 12) to have a new group number in this case

id anchor date        time     'group' (the value to get)
3  2      2019-01-01  07:00     1
4  2      2019-01-01  08:00     1
5  3      2019-01-01  15:00     2
7  3      2019-01-01  16:00     2
10 3      2019-01-01  17:00     2
11 2      2019-01-01  18:00     3
12 2      2019-01-01  19:00     3


texts: PostgreSQL, storing encrypted data or encrypt database with pgcrypto - what is more secure?

texts:
I have two machines: client and server (a database server). I want to connect from client to server and manipulate data from database (insert, update, delete, select) using dblink.

I would like to ask what is more secure:

Encrypting data on client side, send them to the server from client as ciphertext and then store in PostgreSQL database
Send data from client to server and do the encryption/decryption on server side with pgcrytpto?

Is that secure that server obtains the encryption key temporarily (for the time of one of possible operations only) from client / other server? Im wondering if there's a possibility that no matter if server obtains a key only temporarily, is there still posibility that the key will be available in PostgreSQL server's logs (or any other database server) somehow?


texts: Can not ping Always On listener from outside primary node

texts:
I just setup SQL Server 2012 (on Windows Server 2012) Always On feature and have a problem with connecting to SQL Server Listener from non-primary node (it works from primary node though).

Here is the scenario:

2 nodes in different subnets:
   SQL1 10.0.2.11 (primary)
   SQL2 10.0.6.11 (secondary)
Listener:
  Name: SQLPROD1
  Port: 1433
  Subnets       IP address
  10.0.2.0/24   10.0.2.102
  10.0.6.0/24   10.0.6.102

I can connect to SQLPROD1 in SSMS on primary node (either SQL1 or SQL2) but can't connect (not even ping) SQLPROD1 from secondary or from any other client in the network.

I checked firewall and it doesn't seem to be the cause of the problem. I can ping SQL1 or SQL2 from any client but can't ping SQLPROD1.

Beside this, everything else works in Always On. I can successfully fail-over and see databases on other node.


texts: dbcc checkdb differences between physical_only, full and data_purity

texts:
I'm getting lost about one thing. According to MS docs full checkdb with no option does column-value checks, so what's the gain of running data_purity? By 'gain' I mean how detailed checks are being done, I'm not considering performance now.
Do I understand it correctly that if the database was created before SQL2005 it will have dbi_dbccFlags set to 0 and full checkdb will skip column-value checks, but once such database will have at least one successfull data_purity checks the flag will be set to 2 and column-value checks will be done during every full checkdb? And if the database is created in SQL2005+ it will have dbi_dbccFlags set to 2 by default and then full checkdb with no option and with data_purity will be the same checks?
Also estimates seem to suggest that for SQL2005+ there's no distinction between these two checks, at least if it comes to space needed in TempDB:

DBCC CHECKDB('AdventureWorks2019') WITH PHYSICAL_ONLY, ESTIMATEONLY

DBCC results for 'AdventureWorks2019'.
Estimated TEMPDB space (in KB) needed for CHECKDB on database AdventureWorks2019 = 39189.

DBCC CHECKDB('AdventureWorks2019') WITH ESTIMATEONLY

DBCC results for 'AdventureWorks2019'.
Estimated TEMPDB space (in KB) needed for CHECKDB on database AdventureWorks2019 = 225996.

DBCC CHECKDB('AdventureWorks2019') WITH DATA_PURITY, ESTIMATEONLY

DBCC results for 'AdventureWorks2019'.
Estimated TEMPDB space (in KB) needed for CHECKDB on database AdventureWorks2019 = 225996.


texts: What happens when I change a table column's data type to increase its data length in SQL Server?

texts:
I believe SQL Server would create a new column under the hood and copy the original data to the newly created column. And the original space for the old column cannot be reclaimed before the table is rebuilt.

But I can't come up with a general picture in my head about what exactly is going on.

Say I have a simple table defintion:

CREATE TABLE ExampleTbl
(
 ID INT
 ,Bool BIT
)

And a certain page is completely filled with ExampleTbl's data rows. What happens if I change Bool column data type to int?

Since the page is full, does it mean the the newly created column will have to be stored in some other data page? I originally thought it should be in the same page because SQL Server will always try to use in-row allocation when the data row fits into a page(less then 8000 bytes).

But then it may involve a lot of moving around of the data rows to allocate space so the new column can be inserted into the data page, and some rows will have to be popped out the page and stored in another one, if SQL Server needs to stick to in-row allocation. But this behavior is obviously detrimental to system performance.

So exactly what happens behind the scenes when I change a column data type to one with a wider domain of values?

Thanks!


texts: Does the MongoDB background flush (MMAP) update the entire document even if only a small portion has changed? i.e. set on an array position

texts:
We are currently trying to get to the bottom of some high background flush times on our MongoDB installation. As part of this investigation we have been looking at the $set operation we perform on an array embedded in one of our document types. The array contains 348 empty documents to begin with, and over the course of a week these array element will have sub-documents inserted (if empty) and then subsequently updated (if they already exist). The sub-documents are approximately 100 bytes in size and are not indexed.

So my question is, when the document is flushed to disk, what actually gets written, the entire document or just the sub-document that has been updated?


texts: SQL Server --> Informix DB Linked Server

texts:
After about 3 days of trying, I am unable to create a linked server from SQL Server 2016 to Informix 11.5. I haven't tried this with other versions (2008R2, etc) since I wanted to set up the connection in a test environment first.

What I have Done

Installed the latest Informix OLE DB Driver on the SQL Server host machine
Configured a System DSN for the IBM Informix ODBC Driver Setup
Tested the connectivity of the DSN settings (test is good) after changing the locale settings so the client and server match, and server is correct.
Attempted to create the linked server in two ways below.
Googled and read everything I could to resolve this without success.

1) Using IBM Informix instructions with OLE DB Provider in which I get the following error (regardless if I use a DNS in provider string):

  Cannot initialize the data source object of OLE DB provider "Ifxoledbc" for linked server "UCCX". OLE DB provider "Ifxoledbc"  for linked server "UCCX" returned messazge "EIX000: (-23197)" Database locale information mismatch" (Microsoft SQL Server, Error: 7303)

The DB Locale is en_US.57372

2) Using Microsoft OLE DB Provider for SQL Server as explained in this blog which returns the error:

  Named Pipes Provider: Coould not open a connection to SQL Server. OLE DB provider SQLNCLI11 for linked server UCCX returned message "Login timeout expired"
  OLE DB provider SQLNCLI11 for linked server UCCX retunred a message "A network-related or instance-specific error has occured while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. SQLNCLI11 for linked server UCCX returned message "Invalid connection string attribute"

I have very little experience with Informix which should be apparent and this isn't a local installation, it's a Cisco instance as you probably gleaned from the blogs I was using. Any advice would be appreciated.


texts: SQL Server Database Size much higher than the backup size after restore

texts:
Possible Duplicate:
  Why is a .bak so much smaller than the database it's a backup of?

The database backup size is around 120 MB but when I run Restore FileListOnly, I see that the data file size is 206 MB and the Log file size is 19.8 GB. I also ran Restore HeaderOnly to check to see if its a compressed backup but its not.

So why does to restore the database needs around 20 GB space where as the .bak file is just 120 MB? and when the database backup file doesn't seem like its compressed.

Here are few details:

From FILELISTONLY

Type Size         BackupSize
D    206569472    118489088
L    19816644608  0

From HEADERONLY

BackupType    Compressed    BackupSize      CompressedBackupSize
1             0             119113728       119113728

Recovery model is simple and only one out of 539 vlfs is active after the restore


texts: Recovering SQL Express 2016 RC1 database

texts:
This afternoon, I tried to log on to my local SQL Server 2016 RC1 instance and it failed. Looking at the logs I learn that the service won't start because the "Evaluation Period has ended", which was great, I didn't get any warnings about this period ending which has led me into my current predicament.

I had quite a large database on there which I was working on locally while travelling and made a few significant changes to which I'd rather not have to go back and remake.

What options do I have to recover this database into a new 'fully fledged' SQL Express Instance?

I've tried the activation idea here but it doesn't upgrade the actual database engine, just the other elements. Which is similar to what happens when I try repairing it, just that more elements are repaired.

The actual Primary Database file and log are available, is there anyway I can import these?


texts: How to find out everything that is affected by a master key?

texts:
Following up after a situation on an important LIVE server -

where I have the master key backed up,

but the decryption password for that master key backup file is missing.

I can't open the master key

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'password'

I can't restore the master key

RESTORE MASTER KEY FROM FILE = 'path_to_file'
    DECRYPTION BY PASSWORD = 'password'
    ENCRYPTION BY PASSWORD = 'password'
    [ FORCE ]

What I am left with is to regenerate the master key:

USE AdventureWorks2012;
ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD = 'dsjdkflJ435907NnmM#sX003';
GO

Before doing that, however,

I did some tests, like the one below:

what I want to do is:

find out everything that is affected my the master key
script object definitions, save the data
drop or regenerate the master key
re-create or alter any object definitions (including their permissions)
import back all data

Basically I want to backup everything affected by the master key, then regenerate it and then re-apply all the affected things back to the database

when I run the following queries I don't get any results:

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
    select * from sys.master_key_passwords ;
    select * from sys.asymmetric_keys ;
    select * from sys.crypt_properties ;
    select * from sys.credentials ;

when I run these ones though, I get results:

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
    select * from sys.certificates ;
    select * from sys.key_encryptions ;
    select * from sys.symmetric_keys ;

I cannot even add the database to the availability group, without sorting the master key first.

how can I find anything dependent on the master key?


texts: Is there a name for this "restricted one to many" relationship? Can it be enforced in the DB?

texts:
In an application I'm working on, we have a kind of "restricted one-to-many" relationship. I'm wondering if this has a name, and whether it's possible to enforce at the database level.

A standard one-to-many might be people to pets. One person can have many pets, and each pet is of a specified species. You can have 0 pets, or 2 dogs and a cat, or just a parakeet, etc.

In our case, we want to say something like "One person can have many pets, but no more than one of each type." You can have 0 pets, or just a cat, or a dog and a parakeet, but you can never have 2 of any species.

I can only see two ways to do this:

Have a column on people for each possible pet's foreign key - a cat_id and a dog_id, etc. This would mean a lot of NULLs on the table, though.
Make a standard one-to-many, where each row in pets has a foreign key to people, and enforce the uniqueness by species using code external to the database.

Any alternate ideas?


texts: Restrict contained SQL Azure user to read-only access

texts:
I am trying to create a contained user for a database in SQL Azure that only as read-only access. What I am doing is:

Connecting to the database via SSMS using my admin account.
Right click the database in question and select new query.

Then I typed the following:

CREATE USER DEMO_dbreader WITH PASSWORD = 'pass@word1'
EXEC sp_addrolemember N'db_datareader', N'DEMO_dbreader'`

After that I disconnect from the database in SSMS and I reconnect using the new SQL account I just created. then I type in the following as a test:

CREATE table testing123 (id int)

To my surprise, I can create tables (!?!) I can also truncate them and drop them (?!?) Not sure what the heck is going on here. Am I missing something?

I need the user to be contained in this database and not to exist outside of it.


texts: Couldn't restore database and couldn't change Single user mode to Multi user mode

texts:
I am unable to restore a database.

The database is stuck in single user mode.  I cannot change to multi-user mode.

I was restoring the SQL Server database from Prod to Dev as I have done many times without issue.

I run the following scripts:

--1
use master

go

ALTER DATABASE [Acct] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
Go

The database changed to single user mode.

--2
RESTORE DATABASE [Acct] FROM  DISK = N'D:\Restore_Backup\Restore_05042015\ProdAcct.bak'
WITH  FILE = 1,  MOVE N'ProdAcct' TO N'D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Acct.mdf',
 MOVE N'ProdAcct_log' TO N'D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Acct_log.ldf',
 NOUNLOAD,  REPLACE,  STATS = 10
GO

Error: Msg 3101, Level 16, State 1, Line 1
Exclusive access could not be obtained because the database is in use.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

--3 I run the following script to see the connection

select d.name, d.dbid, spid, login_time, nt_domain, nt_username, loginame
  from sysprocesses p inner join sysdatabases d on p.dbid = d.dbid
 where d.name = 'Acct'
go

--4 kill the connection
Kill 85
Kill 51

--5
exec sp_dboption 'Acct', 'single user', 'FALSE'
go

and I've tried:

ALTER DATABASE [Acct] SET MULTI_USER
Go

Both commands fail.

--6
select d.name, d.dbid, spid, login_time, nt_domain, nt_username, loginame
  from sysprocesses p inner join sysdatabases d on p.dbid = d.dbid
 where d.name = 'Acct'
go

--7 connection 51 was  accessing
kill 51

But I wasn't able to kill it.

At this point I had to call the application owners and asked them to stop the applications they were running. After they stopped their app, I was able to restore the database.

Question:

What kind of mistake did I make to run into this kind of problem?
Is there a way I am able to restore without asking people to stop their application whenever I want to refresh the Dev database?


texts:
I am trying to add a few new articles to an existing sql server replication publication.  The publication is for one way transactional replication.  I have done this multiple times on multiple servers, and in fact have done it several times on the server I am working on now. This time I am running into problems.

I am using the ssms gui to add the articles, by going to the publication properties, selecting the new articles to include, and clicking ok.  Once that is done, I rerun the snapshot agent, which shows the new articles and it finishes successfully.

The problem is that the new tables are never created on the subscriber.  The tables are missing, as well as the associated stored procedures (sp_MSins*/sp_MSdel*/etc).  I don't see any indication of errors that are occuring during the process though.

If I leave the new tables in the publication, eventually all replication grinds to a halt, as it tries to push data changes to the affected table, and the replication agent jobs are unable to find the required tables/stored procedures.  I have removed the affected articles from the publication and now transactions are being replicated again as expected.

At this point I really don't know whats going on, I don't see errors in the logs on either side, and I am not sure what my next step is, besides opening a case with microsoft, any advice for a solution, or even just someplace else to look for errors in the process would be appreciated.


texts:
I'm trying to migrate some data from postgres to mysql. for that i'm doing a simple sql in postgres and load it to mysql. since format is a bit different the flag -AF $'\t' in psql makes it tab delimiter which is great.
The missing part is that psql return null as empty string. where in mysql it represent as \N.
Is there a flag that manage it? I would really like to avoid putting ifnull around each column in the select.


texts: PostgreSQL: optimize Rarity Score calculation

texts:
I need to populate a value called "Rarity Score" for collectible items by one query. Here is the data I currently have:

Items table

+-----+--------+-------+
| id  |  name  | score |
+-----+--------+-------+
|   1 | Item 1 | NULL  |
|   2 | Item 2 | NULL  |
| ... | ...    | NULL  |
+-----+--------+-------+

Ownerships table

+---------+---------+
| item_id | user_id |
+---------+---------+
|       1 |       1 |
|       2 |       1 |
|       3 |       1 |
|       1 |       2 |
|       5 |       2 |
|       4 |       1 |
|       2 |       3 |
|     ... |     ... |
+---------+---------+

Each collectible item may be collected by different users as well as each user may collect any item.

And here is how i accomplished this task.

Rarity Score is calculated by the following formula:

score = 100 - 50 * number_of_item_ownerships / average_number_of_ownerships_per_item

Notice that I set the average number of item's ownerships to be the 50% rarity. Let's check the example:

The average number of item ownerships = (5 + 10 + 15 + 20) / 4 = 12.5
+---------------------------+----------------------------+
| number of item ownerships |           rarity           |
+---------------------------+----------------------------+
|                         5 | 100 - 50 * 5 / 12.5 = 80%  |
|                        10 | 100 - 50 * 10 / 12.5 = 60% |
|                        15 | 100 - 50 * 15 / 12.5 = 40% |
|                        20 | 100 - 50 * 20 / 12.5 = 20% |
+---------------------------+----------------------------+

The query:

UPDATE items SET score = scores.bounded_score FROM (
  WITH appearances AS (
    SELECT item_id, COUNT(*) AS number FROM ownerships GROUP BY item_id
  ),
  average AS (
    SELECT AVG(appearances.number) AS number FROM appearances
  )
  SELECT
    appearances.item_id AS item_id,
    width_bucket(100.0 - 50.0 * appearances.number / average.number, 1, 99, 98) + 1 AS bounded_score
  FROM appearances, average
) AS scores WHERE items.id = scores.item_id;

Some notes:

I'm using CTE to calculate number of item ownerships and the average value.
width_bucket keeps the score bounded between 1 and 100.

This query works fine but I have feeling that it can be greatly optimized with Aggregate Functions. Unfortunately I'm not so expert in PostgreSQL (and math in general) so I'd like to ask for help with the optimization.

Thanks in advance.


texts: Sharded DB accidentally created during update of MongoDB from 2.4.4 to 2.6.4

texts:
I am upgrading mongo version 2.4 to 2.6
I was able to upgrade my complete cluster except one primary member of my replica set
during this period my auto running script created a sharded db.

I can not delete this db as it has important data. I wanted to confirm that i can still continue with the upgrade process for last remaining server will it create an issue.
I have completed upgrade of all mongos and config server before this.

Please let me know the impact if it will be there.


texts: Database model with users, roles and rights

texts:
I have a database model with a user table and role table. I want to control the access (rights) to up to 10 different elements. The access can be granted to either a role or a single user.
Below is the table definition of users, roles and items:

CREATE TABLE users
(
  id serial NOT NULL PRIMARY KEY,
  username character varying UNIQUE,
  password character varying,
  first_name character varying,
  last_name character varying,
  ...
);

CREATE TABLE roles
(
  id serial NOT NULL PRIMARY KEY,
  name character varying NOT NULL,
  description character varying,
  ...
);

CREATE TABLE element_1
(
  id serial NOT NULL PRIMARY KEY,
  name character varying NOT NULL,
  description character varying,
  ...
);

...

Now I have two different ways of designing the rights. One table with a rights type column or 10 rights tables - one for each element I want to control the access to.

What are the pros and cons of one rights table vs. one rights table per element? - or is the a more suitable way to do this?


texts: Output count percentage in 2 decimal places

texts:
I have the following sql query code and I can not get to execute the desired output.

SELECT Deal_type, Price_type,
cast(sum(Deal_type)/count(Deal_type)*1.0 as decimal(12,2)) as results
FROM [dbo].[all]
WHERE Deal_type in ('RMBS','abs', 'cmbs', 'clo', 'cdo')
GROUP BY Deal_type,Price_type order by Deal_type

I am trying to display the percentage values in decimal and currently its displaying the error message -

  Operand data type nvarchar is invalid for sum operator.

Any help with the above query would be very much appreciated.


texts:
I'm trying to transform a table that has a rows with the some attributes that are the same, but others that differ like the table below to a specific JSON structure.

id
name
language
attr1
attr2
attr3
attr4

13
Example 1
en
0
80
10
100

13
Ejemplo 1
es
0
80
10
100

21
Beispiel 2
de
35
80
20
75

21
Exemple 2
fr
35
80
20
75

As some of the columns have the same value, I want them inside an object, and the columns that have different content as an array, like this:
{
  "accounts": [
    {
      "id": 13,
      "attr1": 0,
      "attr2": 80,
      "attr3": 10,
      "attr4": 100,
      "names": [
        {
          "language": "en",
          "name": "Example 1"
        },
        {
          "language": "es",
          "name": "Ejemplo 1"
        }
      ]
    },
    {
      "id": 21,
      "attr1": 35,
      "attr2": 80,
      "attr3": 20,
      "attr4": 75,
      "names": [
        {
          "language": "de",
          "name": "Beispiel 2"
        },
        {
          "language": "fr",
          "name": "Exemple 2"
        }
      ]
    }
  ]
}

I made a function that generates the accounts array without the names array in every object:
SELECT array_to_json(array_agg(accounts)) AS accounts
FROM (
         SELECT DISTINCT id, attr1, attr2, attr3, attr4
         FROM temp_table_from_fuction()
         ORDER BY attr1, attr2
     ) accounts

(temp_table_from_fuction() returns the first table)
This is the result:
[
  {
    "id": 13,
    "attr1": 0,
    "attr2": 80,
    "attr3": 10,
    "attr4": 100
  },
  {
    "id": 21,
    "attr1": 35,
    "attr2": 80,
    "attr3": 20,
    "attr4": 75
  }
]

How can I append an array to a JSON object calling the temp_table_from_fuction() again? Is that possible?


texts: Physical Server or a Virtual Machine SQL Server?

texts: Is there a anyway to figure out via a SQL Script to know whether SQL Server is running on a Physical Server or Virtual Server ?


texts: Galera Cluster - 3rd Quoroum Only Node - No Replication - Possible?HowTo?

texts:
I want to setup a 2 node cluster for MariaDB Galera cluster. What I read says it "requires" 3 nodes minimum. This is not correct, I am familiar with HA through using pacemaker, and basically, with only 2 nodes, you don't have quorum, which Galera Cluster Uses.

I found a good article, which basically explains, the 2 options I already figured existed.

Option 1:

Disable Quorum Check

SET GLOBAL wsrep_provider_options='pc.ignore_sb=TRUE';

Option 2:

Post Split-Brain, manually tell a node to become Primary

SET GLOBAL wsrep_provider_options='pc.bootstrap=YES';

What I'd like, is to set up a 3rd VM, that doesn't replicate any DB's, but acts an a quorum box.

Is this possible? Any links on HowTo? - Maybe I am over-thinking this, is there a way to join a cluster, but not define any Databases to replicate? (Seemed to me when I set up Galera that it just replicated everything automatically, which is good, but I'm not so sure there is a "don't replicate option")

http://galeracluster.com/documentation-webpages/twonode.html


texts: Tool for converting all VARCHAR to NVARCHAR in one or few steps

texts:
I am using SQL Server Management Studio and need to convert all VARCHAR columns to NVARCHAR. Is there a tool or something similar to do the conversion in one or few steps? It should be applied to different databases.

Addition:

Excuse me, but is it possible that you gave me a script to convert NVARCHAR to VARCHAR? I need it the other way round. Also when I run the script which was produced by your script it gave me the error

Meldung 102, Ebene 15, Status 1, Zeile 1
Falsche Syntax in der Nähe von 'GO'.

multiple times. Actually I thought that the solution your script produce, to simply alter the tables doesnt work when there are constraints related to tables. Thats why I asked whether there is a tool that can do this, because I thought a simple script has restrictions.


texts:
I'm getting the error below while creating an AG listener in SQL Server.

  Ensure that the network adapters for dependent IP address resources have access to at least one DNS server. Alternatively, enable NetBIOS for dependent IP addresses.

The user has full privileges and it can create a computer object and a DNS entry successfully.

Please help me to fix this issue.


texts:
I am trying to figure out points reside inside an rectangular area (envelope). I am having a little bit difficulty understanding performance implications of using && operator compared to ST_Intersect.

However I think I explained my own question while trying to formulate my question. I submit it anyway in case it would be useful to somebody.

The manual for && says (ironically the page name is geometry_overlaps.html):

  && — Returns TRUE if A's 2D bounding box intersects B's 2D boundingbox.`

The manual for ST_Intersects says:

  Returns TRUE if the Geometries/Geography "spatially intersect in 2D" - (share any portion of space) and FALSE if they don't (they are Disjoint). For geography -- tolerance is 0.00001 meters (so any points that close are considered to intersect)

ST_Intersects will return true in such cases. For my objective && operator does almost same thing since I am using a rectangular bounding box in either case. I am wondering if && is the fastest operator for my purpose? It feels like && has to do less checks so it must be much more efficient.

Here is an example which is direct copy from &&, ST_Intersects adaptation:

SELECT
        t1.id AS t1,
        t2.id AS t2,
        t1.ln && t2.ln AS "&&",
        ST_Intersects(t1.ln,t2.ln)
FROM ( VALUES
        (1, 'LINESTRING(0 0, 3 3)'::geometry),
        (2, 'LINESTRING(0 1, 0 5)'::geometry)
) AS t1(id,ln)
CROSS JOIN (VALUES
        (3, 'LINESTRING(1 2, 4 6)'::geometry)
) AS t2(id,ln);

 t1 | t2 | && | st_intersects
----+----+----+---------------
  1 |  3 | t  | f
  2 |  3 | f  | f
(2 rows)

Below is a simple graph of how these lines should look like. I just added plotted this to see how bounding box works exactly for lines. For my purposes, the points are always inside the box (while this line example does not really reflect what I am trying to do but good for illustration)

Question 1 is if && is significantly faster than ST_Intersects when using with ST_MakeEnvelope (a rectangular boundary), when finding points inside a rectangular bounding box.

Question 2 is Also am I understanding correctly that when checking points inside a rectangular boundary && does exactly same thing as ST_Intersects?


texts: Odd inconsistent behavior of SQL Server "IN" clause

texts:
Query 1:

select count(UserID)
from users
where UserID IN (select UserID from otherTable)

Returns: 178

Query 2:: (simply changed IN to NOT IN)

select count(UserID)
from users
where UserID NOT IN (select UserID from otherTable)

Returns: 0

Query 3: (remove the IN-clause completely)

select count(UserID)
from users

Returns: 1047123 (over a million)

What? I can't... Even...

The only explanation I have is my database in inconsistent/corrupted, I have to run DBCC CHECKDB or something.

PS. UserID is a primary key, so - no dupes.


texts: Postgreslq, ¿How do I create a trigger that UPDATES, null values, under conditions?

texts:
I am doing a trigger, in which:
You must first compare the "nro_persona" value of table "conductor" with table "event".
If it exists, ask if the "nombre_completo" value is NULL.
If "nro_persona" exists and is NULL, update the data in the "conductor" table to "Event".
Whenever an insert is made.
How can I validate that the value exists first, included in the trigger? It doesn't give me an error, it just doesn't work for me.
--- Insert is Null
CREATE FUNCTION TR_CONDUCTOR() RETURNS TRIGGER
AS
$$
BEGIN
IF EXISTS(SELECT nro_persona,
                 cod_operador,
                 nombre_completo,
                 genero
                 FROM evento
        WHERE nombre_completo is null) THEN

INSERT INTO evento(nro_personal, cod_operador, nombre_completo, genero)
    VALUES (new.nro_personal, new.cod_operador, new.nombre_completo, new.genero)
ON CONFLICT (nro_personal)
DO UPDATE SET
cod_operador=excluded.cod_operador,
nombre_completo=excluded.nombre_completo,
genero=excluded.genero;

ELSE
INSERT INTO probando(nro_personal, cod_operador, nombre_completo, genero)
    VALUES (new.nro_personal, new.cod_operador, new.nombre_completo, new.genero);
END IF;
RETURN NEW;
END $$
LANGUAGE plpgsql;

CREATE TRIGGER TR_CONDUCTOR_TMP BEFORE UPDATE ON evento
FOR EACH ROW
EXECUTE PROCEDURE TR_CONDUCTOR() ;

Link to the full code: https://dbfiddle.uk/?rdbms=postgres_10&fiddle=959c4cf72acccdbd81d2debcf4bc2ca9


texts:
Since my server was started one month back, I am getting 2015-07-31 as the value of last_user_update for a table.

Does that mean my table’s data hasn't been modified (inserted, updated, deleted) since 2015-07-31?

Can I 100% believe the results returned by last_user_update in sys.dm_db_index_usage_stats?

I am using SQL Server 2012. I have checked through sys.dm_os_sys_info that my server was started last on 22nd April-2015.

I am only worried about modifications to the table after 31st July. The reason why I want to be sure is because my database has been used by another party after 31st July and the data is huge so I can't confirm now whether any updates have been done or not.


texts:
if I update 10 posts in a website simultaneously and connection gets lost after one or two post updates, does this has anything to do with connect_timeout value?
currently it is set to 10.
After the connection is lost.. the error being logged in cpanel is 'PHP Warning: mysqli_query(): MySQL server has gone away in.....'
and 'PHP Warning:  mysqli_query(): Error reading result set's header in... '
I do have some slow queries in the mysql slow query log.. but if in case I can't fix those for now, does increasing connect_timeout would fix the 'lost connection' issue temporarily?


texts: Implementing Unique Index by ignoring similar rows in PostgreSQL

texts:
I am currently working on creating an append-only PostgreSQL table where I want to insert a row every time a business object is updated or deleted.
One issue I have is to replicate the guarantees provided by a unique index. Here is an example for a standard table:
create table users (
  id uuid DEFAULT uuid_generate_v4 (),
  email text not null unique
);

In my project I'd like to instead create a user_versions table like this:
create table user_versions (
  id uuid DEFAULT uuid_generate_v4 (),
  original_id uuid not null,
  email text not null, //I cannot make it unique here because I want to save multiple versions
);

The unique index generated in the users case ensures perfect guarantees with good performance.
For the  user_versions table, I am looking for a solution to ensure that :

We can insert multiple rows with the same (unique_id, email)
We can't insert another row if an email already exists in another row with a different unique_id

In other words, here is what I'd like to reproduce:
insert into users (email) values (joe@dev.null); //works
insert into users (email) values (joe@dev.null); // does not work

insert into user_versions (email, original_id) values (joe@dev.null, xxxx-xxxx-xxxx-1); // works
insert into user_versions (email, original_id) values (joe@dev.null, xxxx-xxxx-xxxx-1); // works
insert into user_versions (email, original_id) values (joe@dev.null, xxxx-xxxx-xxxx-2); // does not work

Does anyone know how to implement this use case? I'd like to avoid using transactions for that