texts: Suggestion for a contact management database design

texts:
I am currently designing a contact management database for a chamber of commerce. The goal of the database is to store all the person (except our own staff), all recorded companies (regular companies and member of the chamber), addresses of the person and companies, tasks that the staff are currently responsible for, a list of our staff (user) and the roles within the chamber.

Business Rules

one person works for one company
one company have multiple person
person and company can have multiple address
one company can be in multiple industry
one industry can have multiple company
one company can have multiple membertype
one membertype can have multiple company
one user can play multiple role
one role can be assigned to mutliple user
one user can have multiple task
one task can be worked on by multiple user
one task can target multiple person
one person can be targeted by multiple task
one person can only be added by one user
one user can add multiple person
one company can have 0 or 1 parent_company
one parent_company can have multiple child company

I have come up with the following design and it has undergone some changes:

Issue

Are there better ways to display the user-task-person relationship?
For example, if a person can have only one email but can have multiple tel, should I make an extra table just for tel while email is still in the persontable? Would it considered to be "unclean"?
For the table membertype, should company_id and typename both be PK?
How does this schema look now? Are there still some normalisations to be done?

I'm a newbee at database, there are definitely some design flaws or errors, it would be nice if you guys could give me some suggestions so that I can correct and improve this design. Thank you ^~^


texts: Get reference count of reverse foreign key relations

texts:
Image I have a simple user table like this:

ID|Name
-------
12|Foo

I want to know: How many reverse foreign key relations point to the user with ID 12? I mean the number of rows which are a foreign key to user and contain the value 12.

I use PostgresSQL, but a portable solution would be better ...


texts: How to rebuild SYSAUX corrupted tablespace?

texts:
I have database 10g 32-bit windows without backup. I want to migrate full database to 11g, but the SYSAUX tablespace is corrupted.

Can I rebuild the SYSAUX tablespace?


texts: Ways to clean data (trim unneccesary space & chars) - MySQL

texts:
I am already familiar with methods of cleaning such as, pasting into excel, and running a TrimClean Macro.

example.

Cell contains text "Cognac                   "
I want the result to be "Cognac"

Is there an UPDATE query which can perform something similar on MySQL?


texts: SQL Mirroring Locking/Blocking Contention

texts:
We want to setup Mirroring High Performance Asynchronous. Server A Principal contains database which mirrors to ServerB. Mirroring does not allow read-queries unless  snapshot is created.

1) If we utilize snapshot for reporting queries, and high volume OLTP transactions are sent from Server A to Server B, Will Report queries create locking/blocking contention, which will prevent Server B Mirror from receiving updates?

2) If so, can I place the Server B Mirror database in Read-Committed Snapshot Isolation (RCSI), to prevent locking/blocking (while ServerA is in Read committed) ?  Or does Mirror Database always have to be in same transaction isolation level as Principal ?


texts: Why colname as input param of function = not good idea?

texts:
Someone told me that using text input for column names and formatting it, like I do below, is rarely a good idea. When I asked why, however, an answer wasn't given. That was on the postgresql IRC, and those guys seem to know their stuff. So I'd like to know why is it not advised ? I'm mostly wondering if it opens the door for sql injection.

create or replace function getItemsOrderBy(order_by_p text)
RETURNS TABLE (id int) AS $$

BEGIN

    return query EXECUTE format('
    SELECT id
    FROM items
    ORDER BY %s', order_by_p) ;

END;

He also said to use execute with using instead, so what's the difference between this:

return query EXECUTE format('
SELECT id
FROM items
ORDER BY %s', order_by_p) ;

and this :

return query EXECUTE '
SELECT id
FROM items
ORDER BY $1' USING order_by_p ;

My function is more complex than what is above - the format part is only part of it. I have a choice to either create one function that can deal with multiple cases (for ordering) or create a bunch of them to deal with every ordering. I felt like doing only one was more practical. Having no function at all isn't an option.

I am actually using pg-promise but I was under the impression that since I'm doing a lot of back and forth between the back end and the DB (send something, wait response, compute something else, send again..) I should go with function and let everything happen all at once.


texts: Is it safe to rely on a shadow volume backup of the mdf and ldf files?

texts:
We are looking at replacing traditional SQL server backups with a VSS based backup of mdf and ldf files.  As a db person I'm somewhat twitchy about this, and yet I can find no evidence that this won't work?

Can anyone suggest a trial I can setup that would demonstrate where we can lose transactions with this strategy? [Yanking out the power cord during a long running transaction is fine].

The system we are looking at creates an initial snapshot of the mdf and ldf files and then copies across changes. I can't imagine a scenario where that could fail us.

Hopefully you can help me convince my boss that we need to keep traditional backups!


texts:
I'm currently monitoring log events through an log_events table which references web_pages. Each web page belongs to a domain and has a classifcation. I've used an inner join so far for the query, however it seems incredibly slow over a dataset of 12 million log_events, 7 million web pages and 4 domains. Is there a better way of writing the query below or would there be a better data structure for this type of query?

Notes:

The $__timeFilter, $classifications and $domains are variables from Grafana for visualising the results.
Using Postgres 11 with TimescaleDB extension.

SELECT
  d.name
  e.status_code,
  count(e.status_code) as "status_count",
  cl.name,
  wp.path

from
    log_events e

inner join web_pages wp
    on e.web_page_id = wp.id
inner join classifications cl
    on wp.classification_id = cl.id
inner join domains d
    on wp.domain_id = d.id

where  $__timeFilter(e.visited_at) and (e.status_code >= 300) and cl.name in ($classifications) and d.name in ($domains)
group by d.name, e.status_code, cl.name, wp.path
order by status_count, cl.name


texts: Creating Indexed View GROUP BY Epoch Date

texts:
I have a few big tables with about 6 billion rows that I was looking to optimize.  Clustered key is Epoch (unix date time which is the number of seconds that has passed after 1970) and customer ID.  This table records usage data per customer per product type.

For example, if this were for a Telco, TypeID 1 is a local call and the value is how many minutes used for that customer.  TypeID2 is a international call and is the value how many minutes were used in that hour for that customer.  Let's say TypeID3 is a special discounted rate for domestic calling.

The data is stored in 1 hour intervals.  I want the indexed view to store the aggregated 24 hour value so when we run a query for 1 day per customer, it has to only look up 1 row in the indexed view instead of 24 rows in the base table.

This is the base table:

ColRowID (bigint)
AggregateID (int)
Epoch (int)
CustomerID (int)
TypeID  (tinyint)
ErrorID (smallint)
Value (int)

We don't care about Aggregate or RowID for our reporting purposes, so I figure the indexed view will look like this:

CREATE VIEW [ixvw_AggTbl]
WITH SCHEMABINDING
AS
SELECT Epoch, CustomerID, TypeID, ErrorID, SUM(Value)
FROM DBO.BaseTbl
-- GROUP BY Epoch  (what goes here?? Epoch/86400?  If I do that I have to
-- put Epoch/86400 in the SELECT list as well)

EDIT:

Sample base data ( i left out the columns we don't need in this case, just assume the ID columns are there).  Each "TypeID" will have a value assigned to it, which the value can be 0.

For example,

    Epoch / Customer ID / TypeID / Value
    /* Epoch 90,000 is day 2 1am */

90000 (1am  day 2) / 1 / 1 / 200
90000 (1am  day 2) / 1 / 2 / 100
90000 (1am  day 2) / 1 / 3 / 120

/* Customer ID 2 as well */
90000 (1am  day 2) / 2 / 1 / 100
90000 (1am  day 2) / 2 / 2 / 50
90000 (1am  day 2) / 2 / 3 / 310

... (repeat for 30,000 customers)

/* Customer ID 1 2am day 1) */
93600 (2am day 2) / 1 / 1 / 150
93600 (2am day 2) / 1 / 2 / 0
93600 (2am day 2) / 1 / 3 / 550

/* Customer ID 2 2am day 2) */
93600 / 2 / 1 / 80
93600 / 2 / 2 / 150
93600 / 2 / 3 / 300
... (repeat for 30,000 customers)

Let's assume all the other VALUE columns are 0 for the remainder of the day since the system went down and no one could use their phones after 2am.  I want my indexed view to record the value column aggregated per day, per customerID and TypeID.

Sample would be:

172800 (Day 3 midnight) / 1 / 1 / 350  --Cust ID 1 aggregated all type id 1 in the past 24 hours
172800 (Day 3 midnight) / 1 / 2 / 100
172800 (Day 3 midnight) / 1 / 3 / 670
172800 (Day 3 midnight) / 2 / 1 / 180  --Cust ID 2 now
172800 (Day 3 midnight) / 2 / 2 / 200
172800 (Day 3 midnight) / 2 / 3 / 610
--Repeat by adding 86400 to the epoch to gather the summary data of the rows for the previous day.


texts: High Level SAN Migration Strategy. Is this a good idea?

texts:
In the company I work for we are planning decommissioning an old SAN serving our production servers Which are 2 Nodes running 2 SQL Server instances using Clustering (one is the backup for one instance and the primary for the other and visceversa). Instead of that old SAN device (I believe it is a VNX 500, but the technical specifications really doesn’t matter from what I’m asking here…) we will have a new one that more or less should replace the existing one with better capabilities and more storage. In a nutshell, what we are going to accomplish is this.

Me as a DBA, have participated in tons of SQL Server Migrations, but in this case we are not really migrating the database servers, we are only pulling the SAN rug underneath and replacing it with a flashier, fancier one…
We have discussed an approach with our enterprise architect and it goes more or less like this (veeeery high level)

Set up file synchronization using some tool (TBD) that will work at the file-block level to synchronize the contents of the data and log files. This will be running as a service while the existing database server in production is working (using the old SAN).
Have this synchronization running for a day or two and observe no errors occur.
On the night were we do the Cutover we

Shutdown the SQL Server Services (on both instance) using MS Cluster Management Console
Shutdown the Clusters themselves using MS Cluster Management Console
Present the new SAN device to the SQL Servers
Remove the old partitions (un assign letters)
Assign those letters to the LUNs on the new SAN
Bring the Cluster online
Start SQL Server Service

Test, Test, Test: consistency Checks, Verify SQL Server Error Logs and as look for other issues

In other words, we are thinking about putting SQL Server to sleep for a while, make the necessary infrastructure changes that should be transparent to SQL Server so when we bring the service online things will look to SQL just the same they were before, but only this time we are reading off the new SAN.

The reason for not doing a straight backup/restore of the databases is because we are trying to minimize downtime and we believe if we find the right tool to synchronize at the file level, we could end up just doing a final sync to catch up with the latest changes (once we shut down SQL Server Service) as opposed to major file copy operations (our databases are rather big and the downtime must be minimal).

I am not asking for a full review of the strategy or discussing this at a super low level, all I’m asking the community members who would like to chime in is:

Have you done anything remotely similar to this? did it work?
Any gotchas?
Anything missing here (at a high level please, I know I’m missing TONS of stuff, but I’m talking about ciritical high level steps)
DO you know of any tool that may provide the functionality we are looking for in order to synchronize files this way (do an initial sync and then only sync bits/sectors/clusters inside the file that have changed)

Thanks and sorry for the long post, but I felt that if I didn’t include at least a bit of detail the questions would be rather vague.


texts:
We have a global project. We will have some servers all continents. We separate the datas because it is not necessary that they be in one place together. BUT! We need unique IDs (MySQL, InnoDB).
I have two ideas.

We use the auto_increment_increment and auto_increment_offset each server. The problem: we don't know how many servers will be!
We create a custom random ID generator with VARCHAR(16-32) primary keys. The problem: How can we guarantee the unique values (maybe unique server prefix?) and it isn't slower?

Have anybody other idea? I made some fast benchmark, and I don't find differents the integer or varchar primary key.


texts: How do I create logins for all database users in a database that was just restored for a different server?

texts: I was recently tasked with moving some databases to a new server. I created backups then restored the databases to the new server and it looks like the database users were retained, however there's no corresponding logins, prohibiting the users from accessing the new server. All of the users are domain users. Is there any way to iterate over the existing users in a database and create a login for each?


texts: How to create SQL Agent job to automatically restore a database from latest backups?

texts: There are 3 different folders which store respectively full, differential, and log backups. Different types of backups occur in different schedules. Now I need to create a new job which will periodically restore the database in order to assure the consistency of the backups. There are some third party tools available but I do not want to use them. Now, I can't figure out how to specify in the Job Step script to choose the last full/dif/log backups from their folders? The thing is there are multiple files already in the folders.


texts: Find addresses shared by more than one customer_id where customer_name is is distinct

texts:
I need to return the list of addresses that have more than one customer_ID associated with them, exclude the customer_IDs that have the same customer_name and return the number of time the address repeats

Here is what the data looks like:

CUSTOMER_ID   |   CUSTOMER_NAME   |   ADDRESS           | MEMBER_SINCE
----------------------------------------------------------------------
A1000         |      John Doe     |    123 West Lane    |  2/23/2005

A1001         |      John Doe     |    123 West Lane    |  9/30/2005

A1002         |      Tom White    |    456 East Street  |  5/7/2006

A1003         |      Frank Smith  |    123 West Lane    |  12/12/2006

A1004         |      Lisa Simpson |    456 East Street  |  7/19/2007

A1005         |      Clark Kent   |    700 North Ave.   |  2/23/2008

A1006         |      Darth Vader  |    123 West Lane    |  9/29/2008

A1007         |      John Doe     |    456 East Street  |  5/23/2007

A1008         |      Bart Star    |    456 East Street  |  4/19/2006

I want the result set to look like:

CUSTOMER_ID   |   CUSTOMER_NAME   |   ADDRESS           | MEMBER_SINCE| COUNT
--------------------------------------------------------------------------------
A1000         |      John Doe     |    123 West Lane    |  2/23/2005  |  3

A1002         |      Tom White    |    456 East Street  |  5/7/2006   |  4

A1003         |      Frank Smith  |    123 West Lane    |  12/12/2006 |  3

A1004         |      Lisa Simpson |    456 East Street  |  7/19/2007  |  4

A1006         |      Darth Vader  |    123 West Lane    |  9/29/2008  |  3

A1007         |      John Doe     |    456 East Street  |  5/23/2007  |  4

A1008         |      Bart Star    |    456 East Street  |  4/19/2006   |  4

The result shows the CUSTOMER_IDs, CUSTOMER_NAMES, ADDRESSES, MEMBER_SINCE and the COUNT of distinct CUSTOMER_IDs that share the address, while ignoring the rows that have same ADDRESS and CUSTOMER_NAME as another row even though the CUSTOMER_ID is different.  Any ADDRESSES that do not have more than one CUSTOMER_ID are also excluded.

Notice that CUSTOMER_ID A1001 is absent in the result since the CUSTOMER_NAME and ADDRESS are the same and must be a duplicate, and A1005 is absent since there are no other customers with that address.

This is the start:

SELECT
  CUSTOMER_ID as CUSTOMER_ID,
  "CUSTOMER_NAME" as CUSTOMER_NAME,
  "ADDRESS",
  "MEMBER_SINCE",
  count(CUSTOMER_ID) as COUNTER
FROM CUSTOMER_TABLE
  group by ADDRESS_ID, CUSTOMER_ID, CUSTOMER_NAME
  having
  count(CUSTOMER_ID)>1
  order by CUSTOMER_ID;

But I can't manage to take out the duplicate CUSTOMER_NAMES.

This is my first post here and it is very late for me, so I apologize in advance if I didn't post the question correctly.

AR


texts: Export remote Postgres table to CSV file on local machine

texts:
I have read-only access to a database on a remote server. So, I can execute:

COPY products TO '/tmp/products.csv' DELIMITER ',';

But on that server I don't have permissions to create/save a file, so I need to do this on my local machine.

When I connect to the remote database, how can I execute a command to save the file on my local machine instead of the remote server?

Or, how can I execute a Linux command to connect to the remote database, execute a query, and save the output as a file to my local machine?


texts: Difference between a schema and a database in db2

texts:
I can not figure out what is the difference between schema and database in db2 I find this SQL so confusing for example :

CREATE TABLE schema1.table1
 ....
 ....
 ....
IN database1.tabspace1

Can we say that the database in this case contains the tabspace whereas the schema contains the tables?


texts: Find over-payment amount using a three-table join

texts:
How to find Overpayment value of only Client No 1 from below 3 tables using inner join? Each client wise I need to retrieve query.

For example 1:

Once I retrieved client 2 only, answer should be 0 because total he invoiced 300 and total he paid for invoice(s) 100 only so overpayment is 0 .

For example 2:

Once I retrieved client 1 only, answer should be 500 because total he invoiced 1500 and total he paid for invoice(s) 2000 only so overpayment is 500.


texts: Any way to view the list of commited transactions in a period of time?

texts: I want to view the list of commited transactions to my oracle database that users have issued today for exemple. Is that possible ?


texts: PostgreSQL streaming versus file-based replication (In terms of server behavior & configuration)

texts:
I am trying to understand best uses of PostgreSQL replication and how it works so I can troubleshoot in a production environment.

I am having a hard time understanding the differences between these 2 types of replication in terms of (1) Configuration (2) How the 2 servers Master/Slave perform in each scenario

Replication on PostgreSQL (9.2+) is essentially XLOG files of 16MB in size (depending on frequency settings for creating each file) are being created on Master and sent by some method to the Slave.

My Setup (for purposes of this question)

Configuration of Postgresql.conf on Master
archive_command= 'rsync -av %p postgres@[SlaveIP]:[wal_archive_folder]/%f'

Configuration of Recovery.conf on Slave to read log files
restore_command = 'cp [wal_archive_folder]/%f \"%p\"'
primary_conninfo = 'host=[MasterIP] port=5432 user=postgres'

My question is what part of this configuration makes this "streaming" replication versus "log shipping"? My master is configured to use rsync to send logs to the slave (is this log shipping?)
My slave is configured to be able to connect to the master in recovery.conf (is this streaming?)

Second part of the question: What is happening? I understand there is another protocol on PostgreSQL via WAL_sender & WAL_receiver. But I am not clear if this is used for streaming only and if so, how is the rsync being used in the Master?

:) Thank you!! And sorry if this is an obvious question. I've been doing a bunch of reading blogs/books but having a hard time understanding. Postgres wiki is so in depth that it takes a long time to get through it all (and I have deadlines)


texts:
How to perform the following select query?

with x (x, y, z) as
((2 5 6), (6,3,2))
select * from x

I am not sure if this approach is possible. Not even sure if the syntax is correct. But I don't want to make a table in my database. Actually I am writing a PL/pgSQL function where I have few numeric values like 2,5,6,3 etc in few variables and I want to create a table from those variables so that I can perform some SQL operations on them.


texts: MySQL import failed with process in 'sleep' state

texts:
I'm trying to import a dump ~500MB, ~300 tables using the SOURCE command from withing the MySQL command line environment. Everything seems to go well with tables being created and being filled with data, but then at some point it just freezes.

Opening up another MySQL command line and checking SHOW FULL PROCESSLIST, shows

| 13458 | someuser | localhost | mydatabase | Sleep| 11639 |      | NULL |

If I try and change to mydatabase and issue a SHOW TABLES, that command also freezes and if I open up a third command prompt, and issue a SHOW FULL PROCESSLIST, in addition to the original sleeping process I get the line:

| 13675 | someuser | localhost | mydatabase | Field List |  7861 | Waiting for table flush | |

I've set tee session.out for logging, but the last three lines in the log file are:

Query OK, 0 rows affected (1.24 sec)

Query OK, 0 ro

that is, the last line is truncated as shown above.

If someone knows what is wrong and how to fix it, that would be fantastic, but failing that, any advice on debugging this further would be appreciated.


texts: TSQL command to delete backup files older than N hours

texts: Can anyone please help me with a TSQL commmand to delete backup files older than N hours?


texts: SQL Server 2012, rebuild not lowering avg fragmentation

texts:
I have a script identifying what indices to rebuild.

select
    'alter index ' + name + ' on ' + @dbname + '.dbo.' + OBJECT_NAME(a.object_id) + ' rebuild;'
from sys.dm_db_index_physical_stats (DB_ID(@dbname), NULL, NULL, NULL, NULL) AS a
inner join sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id
where avg_fragmentation_in_percent > 30

Which, e.g generates:

alter index FooIndex on FooDb.dbo.FooTable rebuild;

But after I execute the alter statement the index still has a high fragmentation value and if I execute it again it don't get any lower (50%). Any input on what could be wrong would be highly appreciated.

UPDATED: I manually increased the size of the DB which managed to lower the fragmentation on some of the indices, but still not all. Still have a couple around 50%.

//Daniel


texts: Oracle SQL, how to access latest queries and their data

texts:
I have SQL Developer, + Oracle client 11g.

I wrote a program (MFC Visual C++) that read and writes data to a certain table.
usually i put logs in the project so I could see what I insert/Update/Delete from the table.
Now I am about to delete multiple rows. and I would like to count the lines I deleted.

How can I know the latest queries that used in the DB, what they affected and get some data on activities that were held on the server ?

thanks !


texts: Update a table after insertion to another table in PostgreSQL?

texts:
Let's say that my database has 2 tables (Person & Car):

Person (personID, number_of_cars)
Car (carID, owner)

where the owner attribute in Car refers to the personID in the Person table. Now, what I want is that whenever I insert a new car object into the Car table, then the number_of_cars of the owner will be incremented by one. The only solution that I have come up so far is to write the UPDATE statement right after the INSERT statement, something like this:

INSERT INTO Car (carID, owner) VALUES (1, 1);
UPDATE Person SET number_of_cars = number_of_cars + 1 WHERE personID = 1;

However, I don't feel like this is a good way of writing it because then I would have to do the update after every single insert statement. So what is a good way to achieve this in PostgreSQL?


texts: Powershell Run Stored Procedures in Parallel in Database

texts:
We want to run all stored procedures in parallel. The powershell script finds a list of stored procedures in the database (only have 5 right now), and runs them simultaneously. This code seems to be working. Is there anymore efficient or recommended method to conduct this, or does the code seem valid?

Workflow TestRunParallelExecute
{
    $ServerName = "localhost"
    $DatabaseName = "testrun"
    $Procedure_Query = "select name from sys.procedures"
    $Procedure_List = (Invoke-Sqlcmd -Server $ServerName -Database $DatabaseName -Query $Procedure_Query)

    ForEach -Parallel ($Procedure in $Procedure_List.Name)
    {
         Invoke-Sqlcmd -Server $ServerName -Database $DatabaseName -Query $Procedure
    }
}
TestRunParallelExecute
cls

I read many resources below, wanted to come up with easy way using Powershell new ForEach -Parallel function.

Run stored procedures in parallel

https://stackoverflow.com/questions/29193805/executing-stored-procedures-in-parallel

https://stackoverflow.com/questions/48803101/run-several-stored-procedure-in-parallel-in-sql-server


texts: With the advent of integrated source control, do stored procedure changes still need comments?

texts:
Back in the old days, my team had a policy that any stored procedure modification required comments in two places:

Top of the stored procedure with an overall summary of the changes
made
Comments on each line where the change was made

It typically looked like this:

CREATE PROCEDURE usp_Test
/*****************************************************************
The purpose of this stored procedure is to get data.
Created by 8kb 2001-01-01
Modified: removed OR clauses from JOIN statement..8kb 2001-06-01
******************************************************************/
AS
BEGIN

SELECT *
FROM t1
JOIN t2
ON t1.colA = t2.colA
-- Removed OR clause..8kb 2001-06-01
-- OR t1.ColB = t2.ColB

END

But now with integrated source control, I can summarize the changes in the source control and then use the compare / diff functionality to see the differences between the old and new versions.

Is there still value in writing line-by-line change comments in stored procedures that are part of integrated source control?


texts: Is storing archive logs on local disk and backups on vendor storage possible?

texts:
I am using DB2 9.7.

My LOGARCHMETH1 is set to VENDOR library. Example:

LOGARCHMETH1=VENDOR:/usr/openv/netbackup/bin/nbdb2.sl64

Is it possible to call the following backup command:

db2 backup database EAIBDB2I online \
    load '/usr/openv/netbackup/bin/nbdb2.sl64' \
    without prompting

if I change LOGARCHMETH1 to be on local disk and not on VENDOR library? How should LOGARCHMETH1be specified for that, and do I need other settings?


texts: Most efficient way to aggregate data to new table

texts:
Say I have a large table A around 151 million rows:

TableA
[Id] [bigint] IDENTITY(1,1) NOT NULL,
[MetaDataProp1] [int] NULL,
[MetaDataProp2] [int] NULL,
[MetaDataProp3] [int] NULL,
[MetaDataProp4] [int] NULL,
[MetaDataProp5] [int] NULL,
[MetaDataProp6] [int] NULL,
[MetaDataProp7] [int] NULL,
[MetaDataProp8] [int] NULL,
[MetaDataProp9] [int] NULL,
[Date] [datetime] NOT NULL,
[Cost] [decimal](10,20) NOT NULL

I need to aggregate my large table in 'n' ways, depending on what is needed by our end users.

For instance group by Prop1-3, Day and SUM(Cost) for date range X and prop5 IN (X,Y,Z).

Now I also have to sometimes join to other tables for some data aggregations on top of that. Creating 20+ indexes on each view seems like a bad idea, and would create massive write overhead. So this makes indexed views out of the question.
That leaves me with one option as far as I know: to push the query results to a new table on my end. But this has some drawbacks:

A select insert is pretty intensive as it blocks other queries while it does it's work. This would be a disaster at just 20 aggregate results.

My idea is this:

Fetch the Max Id.
Do the select from table A in paginated batches off 100k using offset-fetch where the Id < the fetched max Id. This ensures we won't be endlessly re-fetching data and new data is pushed and it also ensures that data won't suddenly fluctuate as it selects.
Push aggregate batch to aggregate table.
Keep going until nothing is found in the paginated select.

Advantage to this:

Since it's now batched, it allows other queries to flow through in between the batch select and batch insert.

Disadvantages to this:

More read I/O as each paginated fetch must order the grouped results.
Longer overall aggregation time.

Another approach could be to fetch the whole data set from a machine, hold it all in memory, then bulk save in batches from the machine. This would reduce read I/O, but that seems like a bad idea as the query being returned can be 40GB+ in size. The cost of running a machine like that is not worth it just for aggregated data.

What do you think of this solution? What's the recommended solution for type of problem?

The data is updated about 3 times a day. In which we tear out old data and push in the new data. About a few million deletes and re-inserts each go.

I've heard whispers of ColumnStore in SQL Server and I am in a position where I can use it. But never read much on it.


texts: Copying 500 GB SQL Server database from Production to Dev instance

texts:
We need to copy 500 GB database from our PRODUCTION server to a DEV server. We only have a window of 5 hours.

What would be the quickest way to achieve this?


texts: SqlDeveloper: ora-12505 TNS listener does not currently know of SID given in connect descriptor

texts:
Info

 connection name: ziv
 username:sys
 password: xxxxxxx
 connection type: basic
 role: sysdba
 host name : localhost.localdomain
 port: 1522
 sid: ziv

The QUAST is to connect to the database ziv using Sql Developer.

This database was created using dbca.

I am new to both Linux and oracle.

What I need to do... ora-12505 ^^

I think the problem is at bash

  [oracle@localhost Desktop]$ tnsping ziv1

   TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on
   09-DEC-2015 12:38:44

   Copyright (c) 1997, 2009, Oracle.  All rights reserved.

   Used parameter files:
   /u01/app/oracle/product/11.2.0/db_1/network/admin/sqlnet.ora

   Used TNSNAMES adapter to resolve the alias
   Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS =
  (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1523)))
  (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME =
   ziv1.localdomain)))
   OK (0 msec)
   [oracle@localhost Desktop]$ lssnrctl services
   bash: lssnrctl: command not found
   [oracle@localhost Desktop]$ lsnrctl services

   LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 09-DEC-2015
   12:39:59

   Copyright (c) 1991, 2009, Oracle.  All rights reserved.

   Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)
  (HOST=localhost.localdomain)(PORT=1521)))
   The listener supports no services
   The command completed successfully

lsnctl status:

I got a 3 LISTENER name LISTENER port 1521 ziv port 1522 ziv1 port 1523

lsnrctl start listener:

 [oracle@localhost Desktop]$ lsnrctl start listener
 LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 10-DEC-2015
 02:15:16
 Copyright (c) 1991, 2009, Oracle.  All rights reserved.
 TNS-01106: Listener using listener name LISTENER has already been
 started
 [oracle@localhost Desktop]$ lsnrctl status ziv
 LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 10-DEC-2015
 02:15:32
 Copyright (c) 1991, 2009, Oracle.  All rights reserved.
 Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)
 (HOST=localhost.localdomain)(PORT=1522)))
 STATUS of the LISTENER
 ------------------------
 Alias                     ziv
 Version                   TNSLSNR for Linux: Version 11.2.0.1.0 -
 Production
 Start Date                10-DEC-2015 02:05:28
 Uptime                    0 days 0 hr. 10 min. 4 sec
 Trace Level               off
 Security                  ON: Local OS Authentication
 SNMP                      OFF
 Listener Parameter File   /u01/app/oracle/product/11.2.0/db_1/network
 /admin/listener.ora
 Listener Log File         /u01/app/oracle/diag/tnslsnr/localhost
 /ziv/alert/log.xml
 Listening Endpoints Summary...
 (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1522)))
 The listener supports no services
 The command completed successfully

OK what now?


texts: Replications fails with a vague message: "The last step to run was step 3: Detect nonlogged agent shutdown"

texts:
In Transactional Replication of SQL Server, Synchronization job starts well and proceeds a while. However, in middle of the work it shows this error:

The job failed.  The Job was invoked by User WIN-XXXXX\Administrator.
The last step to run was step 3 (Detect nonlogged agent shutdown.).

It's a very generic error message. How can I get more details about the error?


texts:
I have a client that wants me to implement a webpage that allows users to enter custom queries in a text box so they can directly query the SQL Server database.  I am concerned about users entering malicious queries *.  Is there a way to assure (via syntax checking or role permissions or whatever) to assure that the database is kept secure while providing this functionality?

* Ignoring denial of service. My concern is primarily loss of data


texts:
Let's say, for SQLServer2008R2 and higher, with full recovery mode databases.

I always thought :

When a transaction is commited (COMMIT), the transaction is written to the transaction log in RAM.
When a CHECKPOINT occurs (after some time and/or some transactions and other criterias), the transactions between the last CHECKPOINT and the current are written to disk.
When a BACKUP LOG happens, the datas are written to the MDF file.

Am I correct? Some of my collegues says I'm wrong, and it's hard to find the correct answer, even with the BOL.

Thanks!


texts: Use Create Trigger to create New table using calculated values from common groupid column values

texts:
I have a TABLE_A in Database1 the contains rows with a common group id. I am wanting to use the CREATE TRIGGER function to generate a new TABLE_B in the same Database1 that contains calculated values (summed/multipled ect.) of certain columns with the same group id. and to group these all together. Using Mysql 5.5

ie:

TALBE_B (New Table Updated as TABLE_A data changes) (using CREATE TRIGGER..? )

Any help would be greatly appreciated!!!


texts: Is PostgreSQL appropriate for processing this large but simple dataset?

texts:
I have a dataset I'm not sure how to store. The structure is simple: 30 numeric attributes identified by about 15 billion x, y, and t values. We're expecting ~17k t values and maybe 90k x/y combinations, though it could be some combination that gives us 20 million records in the end.

The processing involves retrieving 1-10 columns for each x and y pair and storing various calculated numeric values. Are we nearing/passing the limit of fast response times for Postgres with this many rows?

The processing is all done in-house by one person and shouldn't need to happen more than a couple dozen times as we settle on what summaries we want. So we're not worried about a high number of writes or connections, strict security, making updates to existing records, table relations, losing data because of network connection issues. Basically, we're not concerned about the kinds of things I understand the ACID part of a RDBMS like Postgres brings to the table. But we also don't need to replicate or distribute the data, high availability, change the schema on the fly, or manage an unusual number of writes (say a dozen for each of the 90k x/y pairs)- the kinds of things I understand NoSQL DBs offer.

So I guess the real issue is read-speed out of Postgres for a table of this size and the real question is whether there's a better storage mechanism for what we need to accomplish. Does the answer change if we have 40 billion records? 60?


texts: How to access the number of rows accumulated by RETURNs in PL/pgSQL

texts:
When performing

RETURN QUERY ...

in a PL/pgSQL function, is it possible afterwards to directly access the number of rows accumulated into the pile of records, which are returned when the function ends?

Example:

RETURN QUERY SELECT * FROM tableA; -- 14 records
RETURN QUERY SELECT * FROM tableB; -- 8 records
RETURN QUERY SELECT * FROM tableC; -- 22 records

The number of accumulated records should now be 44.


texts: How can I set the auto_increment value of a column in an existing InnoDB table, without incurring downtime?

texts:
I have an InnoDB table whose primary key column is defined as follows:

id INT NOT NULL AUTO_INCREMENT

This column's auto_increment value has reached INT_MAX, so all inserts are failing with a duplicate key error. However, this table has unused ID values from 0 to 1.8 million, so I would like to reset the auto_increment value to 0 to buy time for a long-term fix to be implemented.

I cannot find any way to accomplish this without incurring significant downtime. My application servers' core functionality is broken if they can't read from this table (but not being able to write, like it is now, is okay).

I've considered the following two solutions, with no success:

Setting the auto_increment value with ALTER TABLE. This isn't possible because it rebuilds the table, which takes too long.

Creating a new table with identical schema and using RENAME TABLE to swap in the new table with the column starting at 0, then back-filling it with data from the old table. This also isn't possible, because the process of backfilling with clobber the auto_increment value (and I believe it would block inserts from the application while backfilling).

Is there any other approach I can take here, or am I completely out of luck?


texts: Is CHARSET important for tables with INT only?

texts:
My server default CHARSET is UTF-8 (set in my.cnf). I create tables like
CREATE TABLE t1
(
ID int(11) unsigned NOT NULL AUTO_INCREMENT,
Title varchar(255),
PRIMARY KEY(ID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_general_ci KEY_BLOCK_SIZE=2

but some tables have only int columns. Is it beneficial to use latin charset for them? Or is it better to have all tables with UTF-8 charset for consistency?
CREATE TABLE t2
(
ID int(11) unsigned NOT NULL,
TagID int(11) unsigned NOT NULL,
PRIMARY KEY(ID,TagID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_general_ci KEY_BLOCK_SIZE=1

vs.
CREATE TABLE t2
(
ID int(11) unsigned NOT NULL,
TagID int(11) unsigned NOT NULL,
PRIMARY KEY(ID,TagID)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE latin1_swedish_ci KEY_BLOCK_SIZE=1


texts: Determining the Actual Server Create Date

texts:
I am interested in determining the ages of all 70+ my servers. I would like to create a growth graph showing when servers and databases were added to the environment either/both monthly or annually. I executed the following query using the SSMS multi-query feature with only partial success.

SELECT [name], create_date FROM sys.databases

It's partial success because the master and model databases return with a date of 'April 8, 2003' when I know that I created some of these servers during the past couple years.

I refresh databases from one server to another several times a week, which appears to be causing issues with the database create date.

Is there a simple method to determine the actual create date for both servers and databases?


texts: (Got an error reading communication packets) but no error on client

texts:
i've search a lot about this thread without results.
we don't know how to resolve this:
DB : mariaDB
SERVER: Microsoft Azure
enviroment: Nodejs with Knex (pool connections, min 0 max 300)

our client app stop to catch db response, without an error, except for (Got an error reading communication packets)

if we try to connect to mysql through Navicat/TablePlus/MysqlWorkbench (or even our app in local) during the error we still receive the data from db.
we don't know how to resolve it, since wednesday.

any idea about it?


texts: Querying an Oracle view don't return records using some clients but it does using others

texts:
I was given this view that when I query it using OracleSQLDeveloper it returns nothing, but if I use Toad it does return records!

I cannot inspect the definition of the view, and no errors are logged. The query is pretty simple:

select * from weirdview ;

So I have questions:

It must be something different in the session/connection parameters, right?
Is there a way to trace on server side what went wrong? I think there must be exceptions raised but ignored, would that be possible?
How can I list the session parameters from the client that does see results so I can duplicate that configuration in the others.

regards


texts: Q&A, and Blog Site - Keep to one table or split to 2 or 3 tables?

texts:
Working on an idea for a Question/Answer/Blog site. For the content of each of these, I can store them all in one table with some columns applying or not applying to each of these different types with a type column to differentiate each - OR, I can separate them into two tables Question/Blog, and Answer (or other combo), or into 3 tables one for each type.

In the one table idea, the columns would look like: id / heading / detail / type / qid

type column would differentiate each by being 'question' / 'blog' / 'answer'

qid would reference the id of the question for Answer

QUESTION: uses heading, detail, type

BLOG: uses heading, detail, type (qid matches question, if assigned as an answer, but not typical)

ANSWER: uses detail, type, qid (qid matches question id, does not use heading column)

There may be another column or two (not shown) that may apply to one type and not the other.

I think storing all in one table may make queries simpler where there is a relationship between them, but the table gets much larger quicker... What is a good approach to a database/table design like this with the expectation this community can grow quite large over time (10K to 100K active users)?

Some typical relationships:

A would be related to Q as being the answer(s) to Q. There can be multiple answers to Q. Q, A, B will all be listed on the same window with checkbox choices to show/hide Q&A or B or BOTH. Answers to Q can be associated with A or B (users can assign a blog as an answer but expect to be less often) Quantity of A will far outweigh all of them, with Q following and B the least.

I am leaning toward one table for Q/B and another table for A - BUT I don't have a good clear rationale for it. (Don't have enough experience to see things in terms of scalability, maintainability, normality, reliability, clarity, etc. and future impact) Maybe scalability and maintainability would be prioritized?

Thanks for your thoughts!


texts:
I am using mariadb and I will create a table with two columns and an int primary key.
This table will start with about 3.000.000 rows and will be added approximately 30.000 rows per day.
each column will have 200 to 700 characters.
There will be  some searches against these two columns either as text or json query.
I am thinking of make these column as mediumtext data type and also set full text index.
I am quite new to this and I don't know, Is that a correct approach ?


texts:
I need to remotely insert 3.5 million rows to my amazon rds. Currently my data is in an access database, for which I will use bullzip, access to mysql to create dump files. I have created my schema on the RDS and defined the data types pre-insert (because when you auto upload through the app it defines most cols as varchar(255)).

So far I have used the following methods, when uploading to a test server, whilst building my application.

Direct transfer using bullzip's app. Left it to run overnight once, and 1.7 million rows had inserted after about 10 hours.
Split the file into numerous sql dumps (300k rows each at the most, split by date). Which is also incredibly slow ( over an hour so far to 200k rows)
Export to CSV, and upload through mysql workbench. ( also taking a hell of a long time)

I believe that my issue is that bullzip's application, produces an sql file which performs an insert statement for every row, rather than multi-row insert queries.

Is there a way to produce an sql file, in a multi-row insert format?

Or, do I have to just put up with it, because I'm exporting from access?

And I have read elsewhere that you can only add 50k rows to a multi-value insert. So is what I'm asking even suitable for me?

This is the SHOW CREATE TABLE:

'CREATE TABLE `1_txns` (
  `txn_id` varchar(32) DEFAULT NULL,
  `nationality_id` int(3) DEFAULT NULL,
  `transaction date` int(8) DEFAULT NULL,
  `Yr` int(4) DEFAULT NULL,
  `mth` int(2) DEFAULT NULL,
  `dy` int(2) DEFAULT NULL,
  `hr` int(2) DEFAULT NULL,
  `Min` int(2) DEFAULT NULL,
  `Flight No` varchar(12) DEFAULT NULL,
  `sku` varchar(20) DEFAULT NULL,
  `sales` decimal(8,2) DEFAULT NULL,
  `units` int(8) DEFAULT NULL,
  KEY `txnid` (`txn_id`),
  KEY `skutxnid` (`sku`,`txn_id`),
  KEY `skunatid` (`sku`,`nationality_id`,`txn_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8'


texts:
I'm developing a java web-app  for the acquisition of contracts, and that have to receive some file from an external system. For each new contract i recieve some information about the new customer and a audio file. Now I could save the file on db(I'm using Oracle 11g) or save just the link on db and then maintain the file on a server.

In your opinion What is the better solution?
What are prod and cons of the both the ways described above?


texts: CC/BCC and comments section is missing in the SSRS subscription page

texts:
We're using SSRS 2012.

We tried to limit the permission level of users, and assigned them "Manage Individual subscriptions" role, instead of "Content Manager" or "Subscription Manager", however, now when a user tries to create an email subscription for a report, the CC/BCC and Comments sections are missing.

See picture below.

With a higher role level, the subscription pages looks like:

We can live without CC/Bcc, but the comments section is important to the users.
Can we some how enable this comment section and keep the role level low (Manage Individual subscriptions only)?


texts: What permissions are necessary for truncating a table?

texts:
I have a SQL account with the following permissions on a database:

The db_executor role you see this account being a member of was created by this script:

CREATE ROLE [db_executor] AUTHORIZATION [dbo]
GO

GRANT EXECUTE TO [db_executor]
GO

When I run a select, update, insert or delete on the table, it works fine. When I try to truncate the table, it gives me this error message:

  Cannot find the object "TableName" because it does not exist or you do not have permissions.

What permission is this account missing?


texts:
I am new to Cassandra from 20 years of RDBMS, and over the last few days have been reading and watching everything I can get my hands on that applies to my situation.  I'm sure this is a basic question, but for some reason it's not clicking so please excuse me if this is answered elsewhere.

I am trying to design a table to store a list of associations between users.  Any user may have any number of associated users.  It's sort of like a friends list.

Right now I have 3 fields:

id (timeuuid) (PK)
user (uuid)
friend (uuid)

I want to be able to do:

SELECT * FROM friends WHERE user = ?

So because user is not a PK, it cannot be used in a query.  And, if I make it part of the PK, it has to be unique, meaning users can only have 1 friend max.

I sort of solved this by using a secondary index on user, but I imagine that approach is not the best idea.  The query is rather slow to respond, obviously because it has to ask all my cluster nodes for their part of the data.

So: what is the proper way to design this table?  Thank you very much in advance for any guidance.


texts: Check Oracle Service is Started from Batch Command

texts:
Uses: Oracle9i Standard Ed;

I have a schedule which performs a Dump Import and afterwards the System Reboots itself, which is happening without any problem.

After the System had rebooted, I have another task scheduled to run on System Startup; which has yet another PLSQL scripts to be run.

I witnessed after the Schedule task on System Start-up had ran, an error is thrown as

TNS: protocol adapter error

The cause was determined as The oracle Services had not Started.

So if I could check the Oracle Services has started or not prior to the query, I would solve the problem.

Or else I could use SLEEP Command in the batch file, but cant determine the exact time it takes to startup the server.

I welcome any suggestions on doing this with an alternate way as well.

Below is the Script file I run on System Start-up

@echo off
@echo %time%
echo Disable Triggers
sqlplus  "myusername/mypassword" @C:\Factory\participant-inclusion\Auto\step#4.sql<quit.txt
@echo %time%
echo Run Drop Contraints Sql
sqlplus  "myusername/mypassword" @C:\Factory\participant-inclusion\Auto\step#5x.sql<quit.txt

REM File Check
IF EXIST C:\Factory\participant-inclusion\Auto\step#5.sql (
 @echo %time%
 echo Run Drop Contraints
 sqlplus  "myusername/mypassword" @C:\Factory\participant-inclusion\Auto\step#5.sql<quit.txt
 @echo %time%
 echo Modify Type
 sqlplus  "myusername/mypassword" @C:\Factory\participant-inclusion\Auto\step#6.sql<quit.txt
) ELSE (
 @echo File Not Found
)

@echo %time%
echo Default Prefix
sqlplus  "myusername/mypassword" @C:\Factory\participant-inclusion\Auto\Prefix-Updation-ALL-JKB.sql<quit.txt
@echo %time%
echo And Finally Done!
pause

Many Thanks,


texts: How to open queries into a selected database in SQL Server Management Studio 2005

texts:
My situation:

Using Microsoft SQL Server Management Studio 2005, I connect to a server -- I’ll call it Prod -- using Windows Authentication.

Within this Prod server there are multiple databases: dbA, dbB and dbC.

On a previous computer, if I had the ‘Tables’ folder for a particular database (e.g. dbB) selected and opened an existing query (.sql file) from a local or network directory, the existing query would open “connected” to the selected database (dbB).   On my replacement computer, existing queries always open connected to 'master' on the SQL Editor toolbar instead of the expected dbB database.

A minor inconvenience, yes, but if there is a simple setting I can modify or update to restore this preferred behavior (queries open into previously selected database), I'd appreciate learning how to do it.

Thanks in advance for any suggestions.


texts: database design : Inventory with multiple item units ( wholesale and retail )

texts:
I have designed a db for drug ( pharmacy ) store which will sell medicine both wholesale and retail. There are some medicine that can be sold in multiple units, for example, strip, capsule and vial of medicine. Basically, each medicine can be sold in more than one unit. Here is my db:

I have an inventory movement records  that record the specific stock used to fill each order. if a medicine’s base unit is set to vial, and It has some records with vial in the inventory movement, and after a period of time, I want to sell that medicine with capsule unit, at this time, how do I record this to the inventory movement ? And how do I calculate the stocks from inventory ?
If I update the item_unit record to set the data like following:

And I need to scan whole InventoryMocement table to update the base unit to the new one ( capsule ) and re-calculate the quantity with the multiplier. Is this reasonable ?
Is there any flaws in my design ? Does anyone have any ideas (or examples)?


texts: How to make datapump apply directly to the destination schema

texts:
I have a schema on a server that returns and half I need to replicate it to another server, so I'll la and do an expdp on server1 sending the file to server2 and do an impdp. Solve the problem.

However, I wanted to know if I can not do the expdp and generate the file, but do an impdp directly on the destination without generating the file.

That would save a lot of time.

Original question on SoPT - Como fazer datapump aplicar diretamente no schema destino
Translated by translate.google.com.


texts:
What is the best method for WAL log transfer on Windows?

I have two servers with Windows Server 2008, PostgreSQL 9.0 64bit.

Servers do not have any shared SAN disk, they are on different hosts, different subnets.

Files from the first server must be transferred in reliable way to the second server.

Archive location must be protected by password.

I have tried to use free rsync/rsyncd solution called DeltaCopy, but permissions part is hard for me. The service does not want to run under "postgres" account. It runs as System Local acount but the files are not readable by postgres. If I use incoming chmod option in deltacd.conf (aka rsyncd.conf), it "garbles" Windows file permissions, so I have problems with some actions on file's "Security" tab.

I would like some solution which is reliable, and easy to setup and maintain.

One respondent suggested xcopy - but it has no option to provide passowrd from command line or password file.

thanks


texts: SELECT TOP 1 DATEDIFF for each hour group


texts:
MySQL 5.7 on windows'12 server, table which I am getting error crossed 2 GB in size, server memory is 4 Gb, MySQL used about 90% of memory..
Now any insertion into this table causing table full

Once I restarted MySQL works fine until the above stats come up it again starts giving exception. I am not a server guy, so need suggestions what would be the best solution here?


texts: Mysql Update columns from same table columns

texts:
I have a table where payments are located with invoice_id and payment_id and also payment_due_date => payment receiving date and period_months that is total period of the payment

I have added 2 columns in this table named service_start_date and service_end_date. I want to update these columns. service_start_date will be the first payment date and service_end_date will be payment_due_date+period_months. Then next service_start_date will be (payment_due_date+period_months)+1 day and its service_end_date will be service_start_date+period_month and so on for the next payments.

here is the queries to implement

CREATE TABLE `temp_table` (
    `invoice_id` int(11) DEFAULT NULL,
    `payment_id` int(11) NOT NULL DEFAULT '0',
    `payment_due_date` datetime DEFAULT NULL,
    `period_months` varchar(255) DEFAULT NULL,
    `service_start_date` date DEFAULT NULL,
    `service_end_date` date DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
;

insert into `temp_table`
    (`invoice_id`,     `payment_id`,     `payment_due_date`,     `period_months`,     `service_start_date`,     `service_end_date`)
values
('2065','3337','2015-12-15 00:00:00','3',null,null),  -- '' is not a valid date
('2065','3338','2016-03-14 00:00:00','3',null,null),
('2065','3339','2016-06-12 00:00:00','3',null,null),
('2065','3340','2016-09-10 00:00:00','3',null,null);

select * from temp_table returns this:

What I want is this:

Note: There are multiple invoices in this table and there are multiple payments for each table. I need this to be solve in one single query.

If you have further questions and queries, then please comment.

How can I do this?


texts: SQL Server 2017 Failover Cluster on RHEL Linux

texts:
I am trying to create Failover Cluster on SQL Server 2017 on RHEL 7.3 Linux, following this document from Microsoft:

https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-shared-disk-cluster-configure?view=sql-server-2017

I have installed SQL Server 2017 on both nodes (local storage) as per the document. But after step 3, there is note which says:

  At set up time, a Server Master Key is generated for the SQL Server
  instance and placed at var/opt/mssql/secrets/machine-key. On Linux,
  SQL Server always runs as a local account called mssql. Because it’s a
  local account, its identity isn’t shared across nodes. Therefore, you
  need to copy the encryption key from primary node to each secondary
  node so each local mssql account can access it to decrypt the Server
  Master Key.

Can anyone please help as to what exactly what to do here? Do I have to replace the server master key of the secondary node, by copying it from primary node. Or just have to copy and paste it there?


texts:
I'm very new to the Linux world. I have been trying to connect to MySQL but kept getting various errors.  Below are the three commands I started in that order.
Running the second command was a result of me researching about the error generated in the first command and etc.

Can someone take a look at the below and provide me with some help/suggestion?
Also I can't find the "mysqld.sock" anywhere on the system.

Thank you

  ------------------ root@MYSQL011:/tmp# mysql -u root -p Enter password: ERROR 2002 (HY000): Can't connect to local MySQL server
  through socket '/var/run/mysqld/mysqld.sock' (2) root@MYSQL011:/tmp#

  ------------------ root@MYSQL011:/tmp# service mysql status

  ? mysql.service - LSB: Start and stop the mysql database server daemon
  Loaded: loaded (/etc/init.d/mysql)    Active: failed (Result:
  exit-code) since Tue 2017-04-18 11:00:41 EDT; 23min ago   Process:
  5360 ExecStart=/etc/init.d/mysql start (code=exited, status=1/FAILURE)

  Apr 18 11:00:41 MYSQL011 mysql[5360]: Starting MySQL database server:
  mysqld . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
  failed! Apr 18 11:00:41 MYSQL011 systemd[1]: mysql.service: control
  process exited, code=exited status=1 Apr 18 11:00:41 MYSQL011
  systemd[1]: Failed to start LSB: Start and stop the mysql database
  server daemon. Apr 18 11:00:41 MYSQL011 systemd[1]: Unit mysql.service
  entered failed state.

  ------------------ root@MYSQL011:/tmp# journalctl -xn

  -- Logs begin at Wed 2017-04-12 11:02:35 EDT, end at Tue 2017-04-18 11:20:22 EDT. -- Apr 18 11:00:41 MYSQL011 mysql[5360]: Starting MySQL
  database server: mysqld . . . . . . . . . . . . . . . . . . . . . . .
  . . . . . . . failed! Apr 18 11:00:41 MYSQL011 systemd[1]:
  mysql.service: control process exited, code=exited status=1 Apr 18
  11:00:41 MYSQL011 systemd[1]: Failed to start LSB: Start and stop the
  mysql database server daemon.
  -- Subject: Unit mysql.service has failed
  -- Defined-By: systemd

  -- Support: http://lists.freedesktop.org/mailman/listinfo/systemd-devel

  -- Unit mysql.service has failed.

  -- The result is failed. Apr 18 11:00:41 MYSQL011 systemd[1]: Unit mysql.service entered failed state. Apr 18 11:06:23 MYSQL011
  sshd[5175]: pam_unix(sshd:session): session closed for user xyz Apr 18
  11:17:01 MYSQL011 CRON[6116]: pam_unix(cron:session): session opened
  for user root by (uid=0) Apr 18 11:17:01 MYSQL011 CRON[6117]: (root)
  CMD (   cd / && run-parts --report /etc/cron.hourly) Apr 18 11:17:01
  MYSQL011 CRON[6116]: pam_unix(cron:session): session closed for user
  root Apr 18 11:20:22 MYSQL011 systemd[1]: Starting Cleanup of
  Temporary Directories...
  -- Subject: Unit systemd-tmpfiles-clean.service has begun with start-up
  -- Defined-By: systemd

  -- Support: http://lists.freedesktop.org/mailman/listinfo/systemd-devel

  -- Unit systemd-tmpfiles-clean.service has begun starting up. Apr 18 11:20:22 MYSQL011 systemd[1]: Started Cleanup of Temporary
  Directories.
  -- Subject: Unit systemd-tmpfiles-clean.service has finished start-up
  -- Defined-By: systemd

  -- Support: http://lists.freedesktop.org/mailman/listinfo/systemd-devel

  -- Unit systemd-tmpfiles-clean.service has finished starting up.

  -- The start-up result is done. root@MYSQL011:/tmp#


texts: How to hint Remote Scan operator to estimate more than 10000 rows?

texts:
I need to read data from a linked server and insert into a local table. I need to remove duplicates in data and I need to do it on a local server, because remote server is overloaded. So, I added DISTINCT clause which does Distinct Sort as I want it to do.
The problem is that Remote Scan operator always estimates the number of rows as 10000, while the real number of rows is around 3M. So, the sorting spills to disk and becomes slow.
If there a way to hint to the optimizer that the real number of rows is much more than 10K?
Should I load raw data into a local staging table and then run DISTINCT off the local table? I didn't want to write to disk twice.
The number of rows that are duplicates is small - few hundred out of 3M.
I mean by this that before the duplicates are removed there are ~3,000,000 rows; after the duplicated are removed there are ~2,999,800 rows. So, removing the duplicates on the remote server would not noticeably reduce the amount of data that is transferred over the network.
The destination table is truncated before insert, so I'm always inserting into an empty table. Also, the destination table doesn't have any indexes, triggers or constraints. There are many columns in the table. About 110 columns. In the query below I wrote ManyManyColumns instead.
The query:
WITH
CTE_Raw
AS
(
SELECT
    [ManyManyColumns]
FROM OpenQuery([remote_server],'
SELECT
    [ManyManyColumns]
FROM
    [DB].[dbo].[remote_view]
')
)
,CTE_Converted
AS
(
    SELECT DISTINCT
        [ManyManyColumns]
    FROM
        CTE_Raw
)
INSERT INTO [dbo].[TestVBFast2]
    ([ManyManyColumns]
    )
SELECT
    [ManyManyColumns]
FROM
    CTE_Converted
;

SQL Server version:
Microsoft SQL Server 2012 (SP4) (KB4018073) - 11.0.7001.0 (X64)
    Aug 15 2017 10:23:29
    Copyright (c) Microsoft Corporation
    Standard Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)


texts:
I have a table, say, Person with fields

id  name
1    Jone
2    Jane

Got another table address

id   person_id   address_type
1    1           Permanent
2    1           Temporary

I want to select table Person to Address such that if a person has Temporary address then join it else join with Parmanent address.

I can do:

    select *
    from person p
    left join address at on at.person_id=p.id and at.type='Temporary'
    left join address al on al.person_id=p.id and at.type='Permanent'

But joining address table twice seems like overkill here. Can I just join address only once such that I get:

-> If Temporary is there, join with Temporary
-> If temporary is not there, join with permanent
-> If pernamenet is not there join address with all null fields.

Help?


texts: Converting a Crystal Report Formula to a CASE expression

texts:
With attempting to convert a crystal report formula into a SQL CASE expression, I seem to be having trouble understanding the concept of "total_pallet_weight" and "order_no" utilizing the "AND" in the formula with "cubic feet" and "order_no". I thought with my attempt below I would start out with a CASE just for "total_pallet_weight" then have a sub CASE for cubic feet to represent the "AND" in the formula. But I'm not to familiar with how the syntax "({a_ras_shipping_order_PCF_vw.total_pallet_weight},
{a_ras_truck_shipment_vw.order_no}) > 0" works. Is the forumla saying "total pallet weight + order_no > 0"?

Crystal Report Formula:

IF Sum ({a_ras_shipping_order_PCF_vw.total_pallet_weight},
{a_ras_truck_shipment_vw.order_no}) > 0
AND Sum ({a_ras_shipping_order_PCF_vw.cubic_feet},
{a_ras_truck_shipment_vw.order_no}) > 0
THEN
Sum ({a_ras_shipping_order_PCF_vw.total_pallet_weight},
{a_ras_truck_shipment_vw.order_no})
/Sum ({a_ras_shipping_order_PCF_vw.cubic_feet},
{a_ras_truck_shipment_vw.order_no})
ELSE 0

Attempt at replacing formula:

CASE WHEN (SUM(TCT.[weight]) + SUM(TPM.[weight])) > 0
     THEN
        ,CASE WHEN (SUM(CONVERT(DECIMAL(10,4), (TRCB.skid_height_inches *
         TPM.dim_ext_x * TPM.dim_ext_y) / 1728))) > 0
             THEN (SUM(TCT.[weight]) + SUM(TPM.[weight])) +
             (SUM(CONVERT(DECIMAL(10,4), (TRCB.skid_height_inches *
             TPM.dim_ext_x * TPM.dim_ext_y) / 1728)))
             ELSE 0
        END
     ELSE 0
  END AS 'Total_PCF'

Syntax for "total pallet weight"

SUM (TCT.[weight]) + SUM(TPM.[weight]) AS 'Total_Pallet_Weight'

Syntax for "cubic feet"

CASE WHEN TRCB.skid_height_inches > 0 AND TPM.dim_ext_x > 0 AND
TPM.dim_ext_y > 0
THEN CONVERT(DECIMAL(10,4), (TRCB.skid_height_inches * TPM.dim_ext_x *
TPM.dim_ext_y) / 1728)
ELSE 0 END AS 'cubic_feet',


texts: Which is suitable db2 version on windows server 2012 server?

texts:
I am planning to install db2 database server on my client machine.

So which version of db2 will be more suitable on  windows server 2012 server

  db2 10.1.0

or

  db2 10.5.0

or any other ?

Thanks in advance!


texts:
Lets say my database looks like this:

Table: messages

+----+------+----+----------+----------+
| id | from | to | double_1 | double_2 |
+----+------+----+----------+----------+
| 1  | 1    | 2  | 0        | 0        |
| 2  | 1    | 0  | 1        | 2        |
| 3  | 2    | 0  | 2        | 1        |
| 4  | 2    | 1  | 0        | 0        |
| 5  | 2    | 3  | 0        | 0        |
| 6  | 2    | 0  | 1        | 3        |
+----+------+----+----------+----------+

Then in my selection from the table, I want to GROUP BY the following:

group rows where double_1 and double_2 is the same, while to = 0
group rows where from is the same, while to = $id and double_1 = 0 and double_2 = 0
group rows where to is the same, while from = $id and double_1 = 0 and double_2 = 0

Anyone have any ideas how to do that?

EDIT: As it seems that i did make some people misunderstanding this. I will now explain better.
As I say the ways that I would like to GROUP BY, then when saying group rows where double_1 and double_2 is the same..., then its grouping the rows where double_1 and double_2 are the same. Like I have those two rows:

+----+------+----+----------+----------+
| id | from | to | double_1 | double_2 |
+----+------+----+----------+----------+
| 1  | 1    | 0  | 1        | 1        |
| 2  | 1    | 0  | 2        | 2        |
+----+------+----+----------+----------+

They should not be grouped, as ID 1's double_1 AND double_2 is not the same as ID 2's double_1 AND double_2. While this would group:

+----+------+----+----------+----------+
| id | from | to | double_1 | double_2 |
+----+------+----+----------+----------+
| 1  | 1    | 0  | 1        | 2        |
| 2  | 1    | 0  | 1        | 2        |
+----+------+----+----------+----------+

Im very sorry for the misunderstanding, hope that someone here are still willing to help me out.


texts: Does the @raise_snmp_trap do anything in @sp_add_alert?

texts:
I have a request from a client to incorporate SNMP Traps as the method for alerting in SQL Server. The only way I have found to do this is by using [dbo].[sp_add_alert] found in the [msdb] database.

-- Add Message in sysmessages to be used for the alert
EXEC sp_addmessage
 @msgnum = 2624470,
 @severity = 1,
 @msgtext = 'A DEV GOT SYSADMIN, RUN!',
 @lang = 'us_english',
 @with_log = 'TRUE'

-- Create an Alert for a user-defined message in sysmessages
EXEC msdb.dbo.sp_add_alert
 @name = N'Alert_For_UserDefined_Message',
 @message_id = 2624470,
 @severity = 0,
 @enabled = 1,
 @include_event_description_in = 1,
 @raise_snmp_trap = 1,
 @job_id = N'00000000-0000-0000-0000-000000000000'

 -- Raise error to trigger the Alert
 RAISERROR (2624470,-1,-1)

However, when I actually raise the error, I only receive the actual Message located in sysmessages.

Now, I'm not too familiar with SNMP Traps, but I feel as though there are some settings that would need to be defined; whether that be at the time of creating the alert or as settings in the server configuration.  Am I missing something here?

I came across this forum thread and all I gathered from it was that after version 6.5 this feature stopped doing anything, but there is no documentation to support its lack of functionality.


texts:
I should be ashamed of myself, for asking, I know.

Check out this screenshot attached.

My server, which is indeed not seeing HUGE amounts of traffic, should not be this overloaded. Mysql is having a party and everyones invited. The thing is I don't like these kinds of wild parties.

How do I kill this stuff? How do I regulated it so that it doesn't keep happening? Pretty soon my host is going to call the police and the party will be over for everyone.


texts: DB2 - Why new multiple C000000x subfolders are created in Archive Log folder?

texts:
After switching to Archive mode (from Circular mode to keeping archive logs on the disk) we had C0000000 folder where archive logs were copied.
Now I noticed that folders C0000001, C0000002, C0000003 and C0000004 are created where C0000001 and C0000002 and C0000003 have only one LOG file.
Archive logs keep coming to C0000004 sub folder.

Time when those folders are created are some random times (for example it is not the time when online backup was performed).

Why those C000000x folders are created and is this regular behavior or we should be concerned?

Thank you


texts: Postgres SQL - Compare Count of rows, returning where a < b or b doesn't exist

texts:
I'm trying to select the 'hash' value from the tags table where either of the following conditions are true:

Count of the 'thumb\_%' rows is Greater than Count of 'dht\_%' rows
Count of the 'thumb\_%' is Greater than 0 but no 'dht\_%' rows exist

I'm using Postgres version PostgreSQL 9.6.6

I have a 'tags' table like so:

CREATE TABLE tags (
    tag_id SERIAL PRIMARY KEY,
    hash VARCHAR(16),
    val VARCHAR(128),
    UNIQUE (hash, val)
);

INSERT INTO tags VALUES
 (243882, 'b119ceb321058d7c', 'thumb_0_1.00'),
 (243883, 'b119ceb321058d7c', 'thumb_1_2.79'),
 (243884, 'b119ceb321058d7c', 'thumb_2_5.59'),
 (243885, 'b119ceb321058d7c', 'thumb_3_8.38'),
 (243886, 'b119ceb321058d7c', 'thumb_4_11.18'),
 (243887, 'b119ceb321058d7c', 'thumb_5_13.97'),
 (244237, '8dbb1085750696c5', 'dht_0_c0b19393ad2c3c84'),
 (244238, '8dbb1085750696c5', 'dht_1_c0b193b3ad2d3884'),
 (244239, '8dbb1085750696c5', 'dht_2_c0b193b3ad2c3c84'),
 (244240, '8dbb1085750696c5', 'thumb_0_207.37'),
 (244241, '8dbb1085750696c5', 'thumb_1_276.50'),
 (244242, '8dbb1085750696c5', 'thumb_2_345.62'),
 (244286, '8dbb1085750696c5', 'thumb_3_1.00');

The tags table has other values associated with a hash besides these, however this query isn't concerned with the extra values.

This is the query I've been testing so far:

SELECT * FROM
(
    SELECT tags.hash, COUNT(*) as dh FROM tags WHERE val ILIKE 'dht\_%' GROUP BY tags.hash
) AS b LEFT OUTER JOIN
(
    SELECT tags.hash, COUNT(*) as thm FROM tags WHERE val ILIKE 'thumb_%' GROUP BY tags.hash
) AS a
ON (a.hash = b.hash AND a.thm > b.dh AND a.thm > 0) OR (a.thm > 0 and b.hash IS NULL);

The desired results would be similar to the following:

       hash       | dh | thm
------------------+----+------
 b119ceb321058d7c |    | 6     <- no dht_ exist for this result
 8dbb1085750696c5 |  3 | 4     <- dht_ is less than thumb_ results

A little background if you want to read.

This is part of a media tagging and management system I've written for some photography we take for some of our businesses. We extract thumbnails from videos and 'tag' the timestamp of each thumbnail. I also have some additional auto-tagging features which attempts to gather specific metadata and insert into this tags table. We have about 19k unique files with about 330k tags. Hashing is done via XXhash, helping us find duplicate files. The dht_ tags are dhashes of a given thumbnail image, to allow us to find videos that are very similar. This is all wrapped up in a nice little node express website and working quite well, until I tried implementing the dhashing of these thumbnail images. My query is much more complicated than the above example, however that's the portion I'm struggling with. Sadly my knowledge of sql has slipped in recent years and as this is a side-project, I've been struggling to get this to work. I am however very happy about the rest of the setup, and for media tracking and such, it's doing it's job well enough already that its saving us a bunch of time hunting down a specific media file.


texts: Cannot set up replication between two servers

texts:
I am trying to subscribe a 2016 (sp2, cu1) server to another 2017 server. I am getting the following error message:

  For merge publications, the version of the Subscriber must not exceed the version of the Publisher

The database on the subscriber is running in compatibility mode 2016 (130) and the publisher is running in 2017 (140). The version of SSMS on the subscriber is also lower than the version on the publisher.

Any advice?

Additional details

While I am aware that on surface level this should be working it still is not, could the error I am getting be hiding a different problem?

Publisher Compatibility level: 140
Subscriber Compatibility level: 130

I am unable to set the compatibility level of the subscriber to 140 because the server is running 2016 and not 2017. I am also unable to upgrade the publisher right now because of business reasons (I am unable to take the server offline for any period of time). I  addition to this, we have other 2016 (sp2, cu1) servers running as subscriber to this server. The publisher server is also the distributor, so both are running on SQL Server 2017 which is a high version of SQL Server than the subscriber.

The subscriber instance was upgraded right after the installation of SQL Server to the above SP and CU, the publisher was upgraded from 2016 to 2017 early last year.


texts: Postgres Count with different condition on the same query

texts:
EDIT Postgres 9.3

I'm working on a report which has this following schema: http://sqlfiddle.com/#!15/fd104/2

The current query is working fine which looks like this:

Basically it is a 3 table inner join. I did not make this query but the developer who left it and I want to modify the query. As you can see, TotalApplication just counts the total application based on the a.agent_id. And you can see the totalapplication column in the result. What I want is to remove that and change the totalapplication to a new two column. I want to add a completedsurvey and partitalsurvey column. So basically this part will become

SELECT a.agent_id as agent_id, COUNT(a.id) as CompletedSurvey
FROM forms a WHERE  a.created_at >= '2015-08-01' AND
a.created_at <= '2015-08-31' AND disposition = 'Completed Survey'
GROUP BY a.agent_id

I just added AND disposition = 'Completed Survey' But I need another column for partialsurvey which has the same query with completedsurvey being the only difference is

AND disposition = 'Partial Survey'

and

COUNT(a.id) as PartialSurvey

But I dunno where to put that query or how will be the query look like.So the final output has these columns

agent_id, name, completedsurvey, partialsurvey, loginhours, applicationperhour, rph

Once it is ok then applicationperhour and rph I can fix it myself


texts: Move MySQL Database to Amazon EC2 Servers

texts:
I have a website which has a 5 GB big mysql database.

I am thinking if it is a good idea to move only the database to amazon, all front-end etc. (PHP) will remain.

I've red already a lot about my Idea and I am not sure whether it is a good idea. Some people say its not a good idea to move a database file to EC2, I will need ECB for a relational Database file like MySQL.

And the amazon FAQ etc. are filled with so much informations I can hardly decide what is important and whats not.

Someone can comment on this? That would be helpful.

P.S.: I am not the owner the main person is to busy atm. and asked me to do the research.

Thanks! I hope I am not off-topic. If so I am sorry, provide me then the right StackExchange where to post such a question.


texts: Postgresql, commas in string_agg function

texts:
Need

FIELD like this, with ' and without , at the end of whole field.

{name:'color',value:'red'},{name:'width',value:'300 ml'}

Tried

string_agg(distinct '{name:\'' || param_name || '\',value:\'' || value || '\'},','') as "FIELD"

but error behind 2nd \

SQL Error [42601]: ERROR: syntax error at or near "\"

With , at the end of whole field, I don't know how to do that.

Thanks.


texts: How to grant xp_delete_file to non-sysadmin login?

texts: I want to delete old backups from shared folder, which are located on separate NAS storage, using newly created SQL login. Is there any workaround to execute xp_delete_file using another account which is not a member of sysadmin role?


texts: Archiving fga_log$: sys.dbms_audit_mgmt.clean_audit_trail doesn't delete anything

texts:
I am having a problem to archive and then delete portions of the fga_log$ table.

Here's what a script basically does

insert into fga_log_archive
select
  ...
from
  sys.fga_log$;

begin
   sys.dbms_audit_mgmt.set_last_archive_timestamp(
     audit_trail_type     => sys.dbms_audit_mgmt.audit_trail_fga_std,
     last_archive_time    => systimestamp
  );

  sys.dbms_audit_mgmt.clean_audit_trail(
   audit_trail_type        => sys.dbms_audit_mgmt.audit_trail_fga_std,
   use_last_arch_timestamp => true
  );
end;
/

The documentation for dbms_audit_mgmt.clean_audit_trail says:

  This procedure deletes audit trail records. The CLEAN_AUDIT_TRAIL procedure is usually called after the SET_LAST_ARCHIVE_TIMESTAMP Procedure has been used to set the last archived timestamp for the audit records.

So, I have expected the records that were archived with the insert statement to be deleted
from the fga_log$ table by the clean_audit_trail call. That's not the case however. Am I missing something?

Edit

The audit configuration parameters as selected with

select
  substrb(parameter_name ,1,25) name,
  substrb(parameter_value,1,20) value,
  substrb(audit_trail    ,1,20) trail
from
  dba_audit_mgmt_config_params
;

is

NAME                      VALUE                TRAIL
------------------------- -------------------- --------------------
DB AUDIT TABLESPACE       SYSAUX               STANDARD AUDIT TRAIL
DB AUDIT TABLESPACE       AUDIT_LOG_TABLESPACE FGA AUDIT TRAIL
AUDIT FILE MAX SIZE       10000                OS AUDIT TRAIL
AUDIT FILE MAX SIZE       10000                XML AUDIT TRAIL
AUDIT FILE MAX AGE        5                    OS AUDIT TRAIL
AUDIT FILE MAX AGE        5                    XML AUDIT TRAIL
DB AUDIT CLEAN BATCH SIZE 10000                STANDARD AUDIT TRAIL
DB AUDIT CLEAN BATCH SIZE 10000                FGA AUDIT TRAIL
OS FILE CLEAN BATCH SIZE  1000                 OS AUDIT TRAIL
OS FILE CLEAN BATCH SIZE  1000                 XML AUDIT TRAIL
DEFAULT CLEAN UP INTERVAL 999                  FGA AUDIT TRAIL

Edit II

select * from v$version returns

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production


texts:
I've got 3 nodes in a replica set.

Each time when performing a mongorestore from the Master, the secondaires go into a RECOVERING state. The oplog cannot keep up. unfortunately I cannot increase the oplog size because lack of disk space.

Now, I want to perform a manual restore by copying the files.
https://docs.mongodb.com/manual/tutorial/resync-replica-set-member/

Here is a snippet of the files which I got:

meee@mongo-p01:~$ sudo ls -la /var/lib/mongodb
[sudo] password for meee:
total 44052516
drwxr-x---   4 mongodb root          4096 Feb 22 17:54 .
drwxr-xr-x. 32 root    root          4096 Feb 23 01:59 ..
-rw-------   1 mongodb mongodb   67108864 Feb 22 17:54 mymongobase.0
-rw-------   1 mongodb mongodb  134217728 Feb 22 17:08 mymongobase.1
-rw-------   1 mongodb mongodb 2146435072 Feb 22 17:14 mymongobase.10
-rw-------   1 mongodb mongodb 2146435072 Feb 22 17:12 mymongobase.11
-rw-------   1 mongodb mongodb 2146435072 Feb 22 17:14 mymongobase.12
-rw-------   1 mongodb mongodb 2146435072 Feb 22 17:14 mymongobase.13
-rw-------   1 mongodb mongodb 2146435072 Feb 22 17:14 mymongobase.14
-rw-------   1 mongodb mongodb 2146435072 Feb 22 17:14 mymongobase.15
-rw-------   1 mongodb mongodb 2146435072 Feb 22 17:14 mymongobase.16
-rw-------   1 mongodb mongodb 2146435072 Feb 22 17:15 mymongobase.17
-rw-------   1 mongodb mongodb 2146435072 Feb 22 17:21 mymongobase.18
-rw-------   1 mongodb mongodb 2146435072 Feb 22 17:51 mymongobase.19
-rw-------   1 mongodb mongodb  268435456 Feb 22 17:08 mymongobase.2
-rw-------   1 mongodb mongodb 2146435072 Feb 22 17:51 mymongobase.20
-rw-------   1 mongodb mongodb 2146435072 Feb 22 17:54 mymongobase.21
-rw-------   1 mongodb mongodb 2146435072 Feb 22 17:54 mymongobase.22
-rw-------   1 mongodb mongodb  536870912 Feb 22 17:08 mymongobase.3
-rw-------   1 mongodb mongodb 1073741824 Feb 22 17:08 mymongobase.4
-rw-------   1 mongodb mongodb 2146435072 Feb 22 17:11 mymongobase.5
-rw-------   1 mongodb mongodb 2146435072 Feb 22 17:53 mymongobase.6
-rw-------   1 mongodb mongodb 2146435072 Feb 22 17:54 mymongobase.7
-rw-------   1 mongodb mongodb 2146435072 Feb 22 17:54 mymongobase.8
-rw-------   1 mongodb mongodb 2146435072 Feb 22 17:53 mymongobase.9
-rw-------   1 mongodb mongodb   16777216 Feb 22 17:54 mymongobase.ns
drwxr-xr-x   2 mongodb mongodb         40 Feb 22 17:54 journal
-rw-------   1 mongodb mongodb   67108864 Feb 22 17:07 local.0
-rw-------   1 mongodb mongodb 2146435072 Feb 22 17:54 local.1
-rw-------   1 mongodb mongodb 2146435072 Feb 22 17:54 local.2
-rw-------   1 mongodb mongodb   16777216 Feb 22 17:54 local.ns
-rwxr-xr-x   1 mongodb mongodb          6 Feb 22 17:06 mongod.lock
drwxr-xr-x   2 mongodb mongodb          6 Feb 22 17:54 _tmp

What are the correct steps I should take?

Reset all mongos by reïnstalling

Perform a mongorestore on the master

Right after the restore, stop all mongod

Copy all local.x and mymongobase.x files to other nodes

Start mongod on all nodes?

I'm a bit lost here.


texts: Oracle: How do I add users to the HTTP ACL in Oracle 11?

texts:
How do I add a user to the network ACL (access control list)?

When running this code

declare
    req utl_http.req;
begin
    req := utl_http.begin_request('http://example.com');
end;

I receive this error.

ORA-24247: network access denied by access control list (ACL)


texts:
I am using a dynamic datasource inside a SSRS report to display detailed metrics about an instance. This dynamic datasource uses a SQL Login to connect to each instance listed by this report.

The password includes a backtick and a dollar sign "abcdef`$ghijk"

And as a result, I believe that only a portion of the password is getting sent to SQL Server.

Can someone please tell me how to escape the password here?
I have already tried backslashes and quotes.

Windows authentication can't be used, because the instances are spread across two different domains


texts:
On a dev server I have had over the past few days very frequent log switches (as many as 200 per hour), plus "thread 1 cannot allocate new log" and "Checkpoint not complete" errors every few minutes. I had 3 50MB log files. Yesterday I upped to 3 256MB files, and the problem persists. I added one more 256MB, no luck. Log switches of course have dropped to 15-20 per hour which is still too high but not that extreme. "Checkpoint not complete" errors have almost disappeared but "thread 1 cannot allocate new log" errors persist.  The system has 8 CPU threads (quad-core i7) and one DB writer as given by the cores/8 rule. Should I add one more DB writer? As this is a dev server it has a SATA drive which of course cannot support high throughput.
Thanks for any advice!


texts: Why 't' and 'f' instead of TRUE and FALSE

texts:
Why does PostgreSQL return 't' and 'f' instead of TRUE and FALSE?

The documentation itself advises us to use TRUE and FALSE when inserting boolean values or making comparisons, but returns a different set of values when selecting the value from the database.

CREATE TABLE test1 (a boolean, b text);
INSERT INTO test1 VALUES (TRUE, 'sic est');
INSERT INTO test1 VALUES (FALSE, 'non est');
SELECT * FROM test1;
 a |    b
---+---------
 t | sic est
 f | non est

Is there a reason for this behavior? Have the PostgreSQL commmunity ever tried to change that?


texts: Postgres: modify existing search_path (preserve current values)

texts:
Sometimes I would like to extend the existing search path, rather than replace it, say:

To start with say it is already set like so:

SET search_path TO schema_b, schema_c, public;

I want to add my schema to the front of the line:

SET search_path TO schema_a + search_path;   --doesn't work

I'm thinking in analogy to what I'd do in BASH:

PATH=path_a:$PATH

Bonus question, perhaps related: is there a way I can store the current path temporarily, so I can change it to something totally different, and then restore it without having to know what it was?


texts: Is it needed to add additional column to make this clustered index unique?

texts:
I have a table as listed below in SQL Server 2012. There is a clustered index on RequisitionID – but this column is not unique. There can be many ProductID for one RequisitionID.

CREATE TABLE [dbo].[RequisitionProducts](
    [RequisitionID] [int] NOT NULL,
    [ProductID] [int] NOT NULL,
    [Qty] [int] NOT NULL,
    [VendorID] [int] NOT NULL,
    [UnitAmount] [decimal](10, 2) NOT NULL,
 CONSTRAINT [pk_RequisitionProducts] PRIMARY KEY NONCLUSTERED
    (
        [RequisitionID] ASC,
        [ProductID] ASC
    )
)

CREATE CLUSTERED INDEX [cidx_RequistionProducts] ON [dbo].[RequisitionProducts]
(
    [RequisitionID] ASC
)
GO

I searched a lot and found that Clustered Index can be non-unique  - but only on limited scenario. Only scenario mentioned appropriate is when there is a Range Search. In my case almost all queries will be based on RequisitionID only – and there is no range search required.

Should I add ProductID also to make the clustered index unique? What are the pros and cons?


texts: Import Excel Table to SQL Server - with date column

texts:
I'm trying to import a excel table that has a column with dates. My query works without the column date. I'm using ODBC to import.

strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & ActiveWorkbook.FullName & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"
cn.Open strCon
cn.Execute "INSERT INTO [ODBC;DRIVER=SQL Server;SERVER=MYSERVEREXPRESS;Trusted_Connection=Yes;DATABASE=TESTDB].[tbTableTest1] ([opDate], [columnFloat], [columnText]) SELECT t.MY_DATE, t.COL1, t.COL2 FROM [BD$] t"

My table:

MY_DATE     COL1    COL2
11/26/2012  21,43   MAR
11/26/2012  21,45   GFM

What am i doing wrong?


texts: Can SQL Server 2016 validate JSON on insert?

texts:
Currently SQL Server 2016 CTP 3 and later releases support NVARCHAR(max) fields with JSON content. However since the type NVARCHAR(max) is totally free-form, there is no constraint on these fields.

Can I declare a constraint that prevents insertion of a non-null value that is not valid JSON?


texts: Circumstances under which a windows server login becomes disabled

texts:
Can anyone confirm under what circumstances a SQL Server windows login account changes from Enabled to Disabled?

More specifically does this need to be a human interaction or can SQL Server automatically disable a windows account under some circumstances (say if it connects too frequently?)

I do not have access to the SQL Server logs so I can't check these

We have many SSIS jobs running against many SQL Servers under a service account. We discovered yesterday that the windows account was disabled on one source server (but still functions against many other servers)

Update: its possible this account was not disabled but I'm still interested in the answer.


texts: How am I supposed to update PostgreSQL on Windows, once installed, without manually installing new installers?

texts:
I have PostgreSQL 11.5 installed on Windows 10. It was installed with the EnterpriseDB installer, which is the only one which exists these days.

If I go to https://www.postgresql.org/download/windows/ , it links to two pages:

One page with installers for Windows. I could manually click the 11.7 installer link and install it manually, but of course, I would go insane if I had to keep that up every time a new minor version is released.
One page with ZIP versions for Windows. The same thing goes here. (Neither has any checksums mentioned anywhere, which is worrying.)

The installers and ZIP files use unpredictable paths. For example, this is the latest version:

https://get.enterprisedb.com/postgresql/postgresql-11.7-2-windows-x64.exe
http://get.enterprisedb.com/postgresql/postgresql-11.7-2-windows-x64-binaries.zip

(The ZIP archives are served over HTTP for some reason.)

As you can see, it says "postgresql-11.7-2" instead of "postgresql-11.7", so I cannot just first determine the latest minor version from the RSS feed and then directly fetch it from a known URL, since it uses an extra "-2" thing.

When trying to go directly to https://get.enterprisedb.com/postgresql/ to browse the files, I get an "Access denied" error in my browser.

Looking instead at the https://www.postgresql.org/ftp/ page, I found a "binary" subdir. However, it only has binaries for "solaris" (???) -- nothing for Windows or any other OS.

People (including myself) are simply not going to be manually downloading and installing new installers all the time. As a result, I have been running outdated PG for a long time now, always postponing it every time I fail to figure out a sane way to update it without me having to manually do it as if it were the initial installation.

When "BigSQL" still used to provide an alternative to EnterpriseDB's PostgreSQL installer, I remember that they had some kind of custom utility which was able to update PostgreSQL from the installed copy, with a binary called "pgc" or something like that. I cannot find that in my EnterpriseDB installation dir of PostgreSQL, so I assume it was just a non-standard thing that BigSQL did. (If it's available as a third-party thing, that doesn't seem sensible to me.)

Is there really no way to update PostgreSQL (between minor versions, such as 11.5 => 11.7) from itself? Or at least some way to properly fetch the latest installer for a branch from the PostgreSQL website? And if the latter is possible, is there some flag I can use for the installer so that it doesn't bring up a whole GUI and just silently updates the already installed PostgreSQL?


texts:
My table structure is as follows:

create table daily_summary
(
    id                 uuid           default uuid_generate_v4() not null
            primary key,
    machine            bigint                                    not null
    odometer_start     numeric(10, 3)                            not null,
    odometer_end       numeric(10, 3)                            not null,
    distance           numeric(10, 3)                            not null,
    date               timestamp                                 not null,
    ...

);

The data in the daily_summary table should be contigious for a machine, ie the odometer_end for a row for a machine should be the odometer_start for the same machine in the next row when ordered by date.

Is there a query using lead() or any other way that I can use to identify non contigious row?


texts: PostgreSQL Idle Session in Transactions with RELEASE_EXEC_SVP queries and lock

texts:
I am having a problem with having idle sessions in transactions. In pgAdmin Server Status, it is showing "RELEASE_EXEC_SVP_XXXXXX" (XXXXXX data are varied) as its query and it's locks also contain a lot of these "RELEASE_EXEC_SVP_XXXXXX" entries. What do these mean? These commonly cause lock blocking problems because these sessions sometimes lock other rows but the session is always idle in transaction. I do not know why there are sessions like these.

Version of PostgreSQL is 9.2.8 running on Windows using psqlodbc 9.03.0210.


texts: Need to know how to check how much time job takes to complete execution

texts:
How we can determine how much time a job takes to execute in sql server, I have a job which runs every hour, I also having some performance issue so I wanted to check if the performance issue is because the job is running or something else and that is why I have to check how much time job is taking to finish.

Job is basically to update the table.

Any help or tips will be very helpful!!!!!


texts: Read-only on an SQLite database which is being modified by another program

texts:
I wrote a program that has an SQLite database, modifying it every few seconds.

Now I want to write a small gadget program that would just read a bit of info from that database (read-only) and display it.

Can the second program open the database as "read-only", while the first program might be modifying it?
Is using Read Only=True when opening the database enough?
Or do I have to make a copy of the SQLite file, or something?
I did not find any tip about this on sqlite.org.

The second program does not need real-time info, it can be a bit outdated.


texts: How to install SQL Server 2012 on Windows 10

texts:
Currently this has been a huge pain for me.
I have a fresh Windows 10 VM. I Installed SQL Server 2012. I open up the SQL Server Studio 2012, and there is no server name that I can choose from in the "Connect to Server" dialog box that shows up.

Additional Context:
10.0.2100.60 is the sql server version
4.0 is the .net framework


texts: Cannot delete Postgresql constraints that were former unique keys

texts:
I have modified unique constraints. I am adding a field to every unique constraint. To do that, I need to drop the existing unique constraints and create the new ones.

I am trying to delete foreign key constraints,  that were former unique keys, in PostgreSQL and I created the following function:

CREATE OR REPLACE FUNCTION dropConstraintsByName(tablename VARCHAR(70), inputconstraintname VARCHAR(70))
  RETURNS VARCHAR(70) AS $$
  DECLARE
    constraintname VARCHAR(70);
  BEGIN
    FOR constraintname IN
      select constraint_name
        from information_schema.constraint_column_usage
        where table_name = tablename
          and constraint_name = inputconstraintname
    LOOP
      raise notice 'deleting constraint: %', constraintname;
      EXECUTE 'ALTER TABLE ' || tablename || ' DROP CONSTRAINT ' || constraintname;
    END LOOP;
    RETURN constraintname;
  END;
$$ LANGUAGE plpgsql;

It should delete only existing constraints, but gives me the following error:

SQL State  : 42704
Error Code : 0
Message    : ERROR: constraint "constraint_name" of relation "table_name" does not exist
  Dove: SQL statement "ALTER TABLE table_name DROP CONSTRAINT constraint_name"
PL/pgSQL function dropconstraintsbyname(character varying,character varying) line 12 at EXECUTE statement
Location   :
Line       : 1
Statement  : select cdds_dropConstraintsByName('constraint','constraint_name')

How can I fix it?


texts: SSIS to import tables from Access DB to SQL

texts:
im trying to design an SSIS package to import 80 tables from Access DB into SQL DB, I have done it through the export and import wizard then saved the SSIS package but i didnt like the way the package was designed as it had 80 different source and destination connections.

Is there a way to Loop through the Access Tables? create each one in SQL DB and import the data into SQL?

Thanks


texts: How can I select the latest record having one state where no later records exist with any other state?

texts:
I want to select the last record (by datetime) having a given state where no later records exist with any other state for each given "widget".

widget     state    timestamp
------     -----    ---------
foo        up       2011-02-11 12:34:56
foo        down     2011-02-10 10:01:10
bar        down     2011-02-09 09:00:20
bar        up       2011-02-08 08:11:20
baz        down     2011-02-07 07:04:18
baz        up       2011-02-06 06:05:18

and many more such records - more than two per widget and more than the two states shown. The timestamps are not ordered (they records are inserted without regard to the timestamp). The unique key is an ID that is generated externally (by another application).

For example, I want to select records with state = "down" and the result should include the records for "bar" and "baz", but not for "foo" since there is a later state for "foo" that is not "down".

I'm using a natural self join to select the latest event for a widget for a different query and think that maybe building on that in some way would work for this one.


texts: Why would MySQL be using Repair with 1 thread to rebuild the indexes on a table (1 primary key, 2 billion rows) when I have myisam_repair_threads set to 4?


texts: SQL Server Deadlock between two selects and one update on one table

texts:
Our application encounters a deadlock every now and again (about once a week).

The main culprit seems to be a query with two selects. One of them is to fill a temp table for performance reasons, the other is a relatively complex select with many joins to return the list of all Appointments with many details. The only potentially special thing I see about the second select is that it includes a self-join. The two-select-query is always part of the deadlock event report by SQL Server.

The other query is a simple DML query (insert or update) on the same table, though this is not always the same DML query. Both queries run with standard READ COMMITTED isolation and not within a explicit transaction.

The two queries are roughly as follows (I've shortened them for clarification)

DECLARE @futureAppointments TABLE(clientId int, StartDate date)
INSERT INTO @futureAppointments SELECT clientId, StartDate FROM Appointments where StartDate >= @startDate

SELECT *, (SELECT COUNT(*) FROM @futureAppointments fa WHERE fa.clientId = a.clientId AND fa.StartDate > a.StartDate)
FROM Appointments a
join b on a.fk_b = b.id
join c on a.fk_c = c.id
join Appointments d on c.somefield = d.anotherfield
WHERE a.StartDate >= @startDate AND a.StartDate <= @endDate

UPDATE Appointments SET someField = @value WHERE id = @id

Example 2: deadlock2.xml,
Example 3: deadlock3.xml,

How would I try to prevent deadlocks from happening in this scenario? Also, does anyone know why the first statement with two selects would acquire a U lock on the selected table's PK as in Example 3? I don't think that it matters, but it seems strange.