texts: Is it possible to define a cast between text and a domain representing a uuid to avoid manual casts?

texts:
I created a DOMAIN as following:

CREATE DOMAIN country_id AS uuid;

I create such domains for readability. It is much easier to read function definitions which accept a country_id instead of a uuid.

So, continuing, I have a function which accepts a country_id:

CREATE FUNCTION list_cities(id country_id) returns SET OF city AS
$$ ... $$ language sql stable;

When I have a text representing a country identifier (for example coming from JSON parsing), I need to manually add a cast as following:

SELECT list_cities((some_json->>'id')::country_id);

Is it possible to define a cast which eliminates the need to add the manual ::country_id cast each time? It would be much cleaner if I could do instead:

SELECT list_cities(some_json->>'id');

Another example could be replacing the following query:

SELECT * FROM country WHERE id = (some_json->>'id')::country_id

by:

SELECT * FROM country WHERE id = some_json->>'id'

Is it impossible to avoid such manual casts by defining an implicit cast from text to country_id/uuid?


texts: How to merge two databases (each with different updates) on the same server

texts:
I have an almost regular WordPress setup including a mySQL database (Server A). On March 1st 2018 this installation and mySQL database was copied to a new server (Server B). On the new server (B) a lot of things got improved and a new domain name was assigned. Also the prefix of the database has changed.

Between March 1st and now this database (B) did not get any user registrations, however as said above it did get several updates and improvements for pages and general behaviour.

I have successfully used phpmyadmin to export the database from the current installation (A) again (on May 5th) and import it into the new server (B) as a second database.

What should happen now, is that the latest changes of (B) [March 1st + improvements etc.] should be merged together with the latest changes of (A) [March 1st + user registrations and user activities that happend on the current page between March 1st and May 5th].

Can I merge the two databases as a whole (with one command), if the tables are the same (I have not really compared each of the tables and it's columns, but as I have not changed any plugins and all the table names are identical - no table has been added or deleted - I guess I can say the structure is identical?)?
Is there a way of using phpmyadmin to do that?
Should I be using "REPLACE" or "INSERT" or "INSERT ... ON DUPLICATE KEY UPDATE" or something completely different? I do not understand what would be correct here.
Once I have managed to mergen the data, what is a good way of updating all URL's to the new domain? In the new installation both databases (A) & (B) still have the wrong URL's.

Many thanks for your help.

On the new server (B) I'm running:

Server: Localhost via UNIX socket
Server-Typ: MySQL
Server-Version:  5.6.39 - MySQL Community Server
(GPL)
Protokoll-Version: 10
nginx/1.12.2
Datenbank-Client Version: libmysql - mysqlnd 5.0.12-dev - 20150407 - $Id: b396954eeb2d1d9ed7902b8bae237b287f21ad9e $
PHP-Erweiterung: mysqli curl mbstring
PHP-Version: 7.1.13


texts: Bulk INSERT ... ON DUPLICATE KEY UPDATE inserts only a small fraction of rows in MariaDB

texts:
I have a table like the following:

CREATE TABLE `demo_upsert` (
    `ID` BIGINT(20) NOT NULL AUTO_INCREMENT,
    `Url` TEXT NOT NULL COLLATE 'utf8mb4_unicode_ci',
    `LastListID` BIGINT(20) NULL DEFAULT NULL,
    `Statistics` BIGINT(20) NOT NULL DEFAULT '0',
    PRIMARY KEY (`ID`) USING BTREE,
    UNIQUE INDEX `Url` (`Url`) USING HASH
)
COLLATE='utf8mb4_unicode_ci'
ENGINE=InnoDB
;

When I try a bulk upsert operation:

INSERT INTO demo_upsert (Url, LastListID) VALUES (?, 9)
ON DUPLICATE KEY UPDATE LastListID=9

Out of ~15K rows for insert, I'm getting ~200 rows actually affected. The latter number depends on the previous number of rows in demo_upsert table.

The MariaDB versions tried are 10.4.12 and 10.4.13 - same problem.

UPDATE: I've implemented a reproducer program and submitted a bug https://jira.mariadb.org/browse/CONC-472


texts: How to preserve column names when constructing a row to be used with to_json in PG 10?

texts:
Is there a way to preserve column names when constructing a row to be used with to_json in a select statement?

I do not like using lengthy json_build_object or creating composite types just to achieve this simple task. Is there a way to do record() just like we do row()?

Thank you!


texts: Add aggregated value filtered by a condition to existing query

texts:
I have this table to collect data from IoT device:
CREATE TABLE public.conferimenti
(
    id bigint NOT NULL DEFAULT nextval('conferimenti_id_seq'::regclass),
    codicestazione smallint NOT NULL,
    codicetag character varying(20) COLLATE pg_catalog."default" NOT NULL,
    dataora timestamp without time zone NOT NULL,
    peso numeric(10,2) NOT NULL DEFAULT 0.0,
    tiporifiuto smallint NOT NULL,
    data character varying COLLATE pg_catalog."default"
)

I am doing a MATERIALIZED VIEW which contains the SUM of weights:
SELECT time_bucket('1 day'::interval, c.dataora) AS dataora,
    c.codicestazione,
    c.idcomune,
    sum(c.peso) AS totale_peso_conferimenti,
    count(c.id) AS totale_conferimenti
FROM conferimenti c
GROUP BY 1, c.codicestazione, c.idcomune

I need to add another column called "totale_peso_conferimento_tipo" with the SUM(c.peso) only for rows where tiporifiuto = 0.
At the moment I'm using 2 separate queries. Maybe it can be done using window functions?


texts: PostgreSQL synchronous replication time out?

texts:
I'm using PostgreSQL 9.3 with synchronous replication. I have 2 synchronous replicators and 1 async replicator. My problem is when 2 synchronous replicators are down, transaction will wait indefinitely.
I found following discussion about this,

http://postgresql.1045698.n5.nabble.com/Timeout-for-asynchronous-replication-Re-Timeout-and-wait-forever-in-sync-rep-td3293679.html

but could not found a solution. Is there a way to resolve this problem ? ( may be using tool like pg-pool )


texts: MySQL: summary query for date activity by last 7 days, week and all year totals

texts:
I have a customer table with records like

+-----+-------+-------------+-----------+
| ID  | Name  | Date        |  Status   |
+-----+-------+-------------+-----------+
| 1   | Joe   | 2014.02.01  | New       |
| 2   | Bob   | 2014.02.02  | Requested |
| 3   | Rob   | 2014.02.02  | New       |
| 3   | Dale  | 2014.02.02  | New       |
| 4   | Jane  | 2014.02.03  | New       |
+-----+-------+-------------+-----------+

... assume data going back in time here

Using a "Group By" query I can easily get a result like the following:

SELECT Status, Date, Count(*) FROM Customer GROUP BY Status, Date

+------------+-------------+-------+
| Status     | Date        | Count |
+------------+-------------+-------+
| New        | 2014.02.01  |     1 |
| New        | 2014.02.02  |     2 |
| New        | 2014.02.03  |     1 |
| Requested  | 2014.02.02  |     1 |
+------------+-------------+-------+

I want to also add rows for week total and a year to date total, so something like the following:

+------------+-------------+-------+
| Status     | Date        | Count |
+------------+-------------+-------+
| New        | 2014.02.01  |     1 |
| New        | 2014.02.02  |     2 |
| New        | 2014.02.03  |     1 |
| New        | Week1       |     4 |
| New        | YearToDate  |   500 |
| Requested  | 2014.01.02  |     1 |
| Requested  | Week1       |     1 |
| Requested  | yearToDate  |   200 |
+------------+-------------+-------+

The "Week1" would be just the total of the previous 7 days

The "YearToDate" would be just the total of that group since the start of the year.

I'd ideally like to do this with a single query.  However I don't know how.  My other alternative is to write 3 queries:

a) Select last 7 days and group by day of week

b) Select last 7 days and group by week in year

c) Select since beginning of year and group by

And then mash them together in my program (not my ideal solution).

Any help is appreciated.


texts:
We have a previously designed query that functions fairly well but I am thinking there has got to be a better way of performing the following.  Here is the query...

WITH CTE1 ( COL1, COL2)
AS
  (SELECT TOP 10 COL1, COL2
   FROM TABLE 1
   WHERE CREATEDATE < DATEADD(HOUR, -1, GETDATE())
   ORDER BY COL2
  ),
WITH CTE2 ( COL1, COL2)
AS
  (SELECT COL1,MAX(COL2)
   FROM CTE1
   GROUP BY COL1
  )
SELECT TABLE2.*
FROM TABLE2
WHERE TABLE2.IDCOL IN
   (SELECT COL2
    FROM CTE2)

So CTE1 can have results like this that require the grouping in CTE2.
CTE1 results look like this:

COL1    |   COL22
_________________
10005   |  500
10005   |  501
42344   |  502
12345   |  503

SO CTE2 is designed to consolidate CTE1 down to the single row by grouping to

COL1    |  COL2
_________________
10005   |  501
42344   |  502
12345   |  503
ETC.

I am trying to figure out if I can accomplish this same task without taking CTE1 into CTE2 and then using CTE2 results in the SELECT query.


texts: Data modeling, double foreign key problem

texts:
I have a certain problem, while creating logical/relational model of database. I get duplicate foreign key in a table. This table is referencing 2 other tables, thorugh identyfing relationship, which reference this particular key and they have to be primary keys because they are cross-reference tables.
Something like this:

How can i fix this so i don't have duplicate keys on PARAM_VAL: id_quali1 and id_quali?


texts: I'm planning to synchronize a pc that has SQL Server 2008 running on it, and the server has a SQL Server 2008 R2 running on it. Are there going to be any problems with data loss or compatibility?


texts: Entity or attribute if only one field beside key and attribute for other entity?

texts:
Let's say I have an entity person which has an attribute Email. But the person can have more than one Email address. So I have to put this attribute into a separate table.

But what I do not understand in this situation: Is Email now an entity or an attribute? I would say entity, because it has its own table. But I would say attribute, because this is the only function of it.


texts: Best practices for deploying and updating SQL Server as part of code deployment

texts:
I am a (windows) developer trying to determine the best approach for publishing DB schema changes requiring data transformations as part of my code releases. I have tried googling but did not find quite my question answered. (My google might be weak, though.) (Also, if this question belongs elsewhere, please let me know.)

We are using automated deployments with Octopus and I would like to have a foolproof way of deploying DB changes as part of this. At the moment, we are using a VS DB project/dacpac and while that works in most cases, we occasionally encounter situations where we would like to transform data/execute scripts as part of the DB deployment and that cannot be handled with dacpac deployments.

Example: We have a SubmittedApplication table with foreign keys to users, suppliers, etc. Most of this data is imported from other systems and is not 'ours' as such. Now stuff is changing upstream and data can be deleted from these tables at the source. As the data is not ours, we have decided to reflect these deletions in our imported tables. Which presents a problem wrt. the foreign keys from the data we do own; the applications. We can accept that the application table contains copies of the data instead of FKs and so would like, in a deployment, to not only change the DB schema 'destructively' as part of the deployment but, interweaved with that, copy data from the existing FKs into the new DB schema, to migrate the existing applications.

As far as I can tell, if we stay with dacpacs, we have do this in two steps: Deploy an expansion of the existing schema, run a post-deployment script to copy data, and, in a subsequent deployment, remove the vestigial schema parts. It grates a bit, having the old columns hanging around, but this seems doable.

Otherwise, we could go with SQL scripts that the deployment process rolled onto the DB. As these would be both schema changes and transforms/data migrations, they would be manually created and curated, as far as I can tell. As I am quite good at forgetting things I dont automate, I am worried about the manual part of this. My present setup, where the dacpac just Does The Right Thing, seems more robust. But, alas, also lacking..

So, my question is whether there is some tool or best practice for handling these situations?


texts: Can't rename columns in PostgreSQL views with CREATE OR REPLACE

texts:
In PostreSQL 8.3, I'm trying to create a view which will look just like an existing table but have different column names.

This works

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

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

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

ERROR:  cannot change name of view column "family_tree_id"

How can I rename columns?


texts: Looking for help reporting duplicates and null marks

texts:
Someone over at StackOverflow suggested this might be the place to ask this.  I hope this will question is proper for this forum.  I am looking for suggestions / criticisms of alternative methods of finding errors in an Oracle table.  I am trying to report both duplicates and missing values something like this:

ID      MAINFIELD   LOCATION                            COUNTER
------- ---------   ---------------------------------   -------
16626   206000650   9A OLIVER ST CENTRAL STATION        2
18805   206000650   3 SWIFT CT CENTRAL STATION          2
22409   940000170   2 MARKET ST NEWARK DE               2
22003   940000170   1 MARKET ST NEWARK NJ               2
29533   970000030   95 MILL RD ANDOVER                  2
20256   970000030   12 RAILROAD AVE                     2
29018   978900050   44 BROAD STREET                     2
28432   978900050   WASHINGTON ST AND HAMILTON AVE      2
21831   980700050   BROADWAY NEWTOWN                    2
24147   980700050   MAIN STREET LEVITTOWN               2
26418                                                   3
26738               TEST DATA                           3
26755                                                   3

The ID is the primary key, is a number and not nullable.  The other columns are both NVARCHAR2 and nullable.  None of those are indexed.  I am not permitted to alter the tables or otherwise modify the database.  Some rows that I am reporting are outright errors.  Some are obvious typos.  Some appear to be test data.  The last three rows have a null MAINFIELD and there are three such rows (two of which have the location null also).

This was my original solution and produced what is shown above.
This appears to achieve the desired result:

SELECT a.id,
       a.mainfield,
       a.location,
       b.counter
FROM   maintable a
       INNER JOIN (
                    SELECT mainfield,
                            Count(*) counter
                    FROM   maintable
                    GROUP  BY mainfield
                    HAVING Count(mainfield) > 1 OR mainfield IS NULL
                  ) b ON a.mainfield = b.mainfield OR
                  ( a.mainfield IS NULL AND b.mainfield IS NULL )
ORDER  BY a.mainfield;

I found this to run more quickly:

SELECT a.id,
       a.mainfield,
       a.location,
       b.counter
FROM   maintable a
       INNER JOIN (
                    SELECT mainfield,
                            Count(*) counter
                    FROM   maintable
                    GROUP  BY mainfield
                    HAVING Count(mainfield) > 1 OR mainfield IS NULL
                  ) b ON NVL(a.mainfield,'***NULL***') = NVL(b.mainfield.'***NULL***')
ORDER  BY a.mainfield;

I liked this alternative, but it runs more slowly:

SELECT  id,
        mainfield,
        location,
        COUNT (id) OVER (PARTITION BY mainfield) counter
FROM    maintable a
WHERE   mainfield IS NULL
OR      EXISTS(SELECT 1 from maintable b
            WHERE mainfield = a.mainfield AND ROWID <> a.ROWID)
ORDER BY a.mainfield;

Is there a way to reference the counter [the COUNT (id) over PARTITION BY mainfield] in the WHERE clause which would allow me to speed this up?

I apologize in advance if this question is trivial and if there is some rule against cross-posting.  I am not particularly strong in this area.

Putting the following code in a response does not format it properly and this isn't so much an answer as a follow-up to the answer posted, so I am adding it here rather than in an "answer."

Is there any reason why I might want to structure the query like this instead:

WITH TempTable AS
    (
    SELECT  id,
        mainfield,
        location,
        COUNT(id) OVER (PARTITION BY mainfield) counter
    FROM    maintable
    ORDER BY mainfield
    )
SELECT  *
FROM    TempTable
WHERE   counter > 1 or mainfield is null
;

I like the syntax in the answer a bit better since it is easier to remember.  What I have above is something I was experimenting with after googling around the web.

Thanks.


texts:
I have read that you also can apply N-Tier for Businesss Intelligence
based on these criteria:
*Presentation
*Functional logic
*Data

What is your experience when you are using N-Tier?

// Fullmetalboy


texts:
I'm working on a rails app for our church, which stores some fairly sensitive information about our people in a MySQL database.

We have our own windows server running Hyper-V so the original intention was to spin up a VM and host on there.

Long term this sounds like the best solution, but at the moment our church has fairly poor internet connectivity (home ADSL) and I don't want to waste time now maintaining infrastructure and talking to service providers when I could be putting more time and energy into building a great app.

In this initial period I'd like to host our app on a VPS with Linode, Rackspace or Amazon.

Our leadership's concern (quite rightly) is security of our people's data, and while I can assure them all communication will be SSL encrypted over HTTPS they are still concerned about storing our data with a 3rd party.

In the scheme of things our data isn't a prime target for attackers, but I'd like to be able to give them the assurance that even if our server was wide open for any member of the public to log in and browse around the data would remain secure.

Is this possible?
Can a MySQL database be encrypted on disk to this level, but stil be accessible over a MySQL connection (via a socket or TCP port)?

On second thoughts I'm thinking it'd be a bit redundant anyway as the database connection credentials will need to be stored on disk for the rails app anyway!


texts: In SQL Server, what is the purpose of grouping stored procedures?

texts:
One of the most perplexing issues with which I've had to deal has to do with Stored Procedure groups. Given a stored procedure, usp_DoSomethingAwesome, I can create that proc in another group by calling it usp_DoSomethingAwesome;2.

I discovered this when troubleshooting some replication issues (Publisher: SQL 2000 Ent., Dist/Sub: 2008 R2 Ent.) that arose with some of the system-generated Insert, Update, and Delete replication stored procedures.

What is the purpose/thought behind having this "grouping" ability?


texts: Real Time data collection - Help select Database

texts:
My Scenario:

I want to collect data from various sensors[for now I have 600 sensors sending data, but this may have to scale up to 3000 more data sensors, all sensors send real time data] Now I have to run network based queries on this data, the data does not have to be in the database for a long time, as I query I have to make calculations based on the current state of the sensors. I do not need historic data. There would be multiple queries made to the database.

My requirements are;

A real time database that is quick. Scalability is an issue , but can be overlooked as I exactly know how many sensors would send data. Large number of queries on the DB. This is an academic project so my preferences are to open source.

I am not a database person , and the shear number of selections that there are is overwhelming. Any help and suggestions are really appreciated.


texts: Oracle Enterprise Manager 12c Cloud: Cannot secure agent mid-installation

texts:
Oracle support isn't being much help currently, so figured I would turn to you all. I am installing OEM in my Solaris 10 test environment. I have almost finished installing the OMS, but it bombs when it attempts to secure the agent. The log advises me to secure it manually. Securing the agent with emctl secure agent fails. Errors from various logs include the following:

gcagent.log:

WARN - Ping communication error
o.s.emSDK.agent.comm.exception.ConnectException [Failure connecting to https://[domain]:1159/empbs/upload/ , err ]
java.lang.NullPointerException [null]

emctl.log

Status output: Status agent Failure:Unable to connect to the agent at https://[domain]:3872/emd/lifecycle/main/ [Connection refused]

secure.log

openPage.834 - Opening: https://[domain]:1159/empbs/genwallet
main.260 - Failed to secure the Agent:
java.net.ConnectException: Connection refused

Troubleshooting with emctl pingOMS reveals the following message:

EMD pingOMS error: Failure connecting to https://[domain]:1159/empdbs/upload , err

I was hoping that the error message would give me a clue as to what is going on, but the error message is blank. In the support documents I've seen, there is always a message here that would help diagnose the problem but no such luck. Any assistance would be appreciated; let me know if any other information would be useful.


texts: Selecting one row from each group based on a Varchar field

texts:
Given a table Avenger whose data looks like this:

id  Avenger         Type    Power
1   Captain America foo      2
2   Captain America bar      3
3   Me              foo      5
4   You             foo      1
5   You             bar      7
6   Iron Man        foo      4

I want to return a distinct result set of the Avengers, although all other data in the table is relevant to my processing interests.  For business reasons I want to return Type "bar" type records instead of "foo" where applicable.

I can do this in two steps, certainly:

declare @avenger table
(
    id int,
    Avenger varchar(50),
    [Power] varchar(50),
    [Type] varchar(50)
)

insert into @avenger
select * from Avenger where Type = 'bar'

insert into @avenger
select t.ID, t.Avenger, t.Type, t.Power from Avenger as t
where t.Avenger not in (select Avenger from @avenger)

And then just return my result set from @avenger. Because I need the other fields from Avenger (e.g. Power) a UNION seems out of the question.  Type is varchar data and while there are 2 now there might be more in the future, so I'm uncertain about trying to sort it based on alpha or anything like that.  Is there some better/preferred way to do this?


texts: SQL server slow on Xeon machine, fast on Corei7 x990

texts:
i have searched this problem but have had no luck find any improvement.

I have a complex application that used stored procedures in SQL server.

Some of the stored procedures use cursors due to the complex nature of the SQL in them.

A few months ago I purchased a new machine expecting an improvement in speed.

The new machine is Xeon E5-2690 single CPU with 32Gig of ram.

The same application runs faster on the new machine as expected. But the database is much slower. One of the stored procedures is more than 20x slower. What takes seconds on the old machine takes minutes on the Xeon machine.

The database and application are identical. I generate the DB from scripts and have done back to back tests with clean empty databases. Still many times slower.

I have searched for answers and tried what little I could find, no help.

I have looked at the settings of SQL server on both machines, many times, and cant find a difference.

I tried turning off HT on the Xeon because I found a post that it helped someone, didn't help me however.

I have started to move some of the DB processing into the applications to try and reduce the time penalty running on the new Xeon.

Any help would be appreciated.
Cheers.


texts:
I'm searching on a field and I want to sort on 3 different fields (viewcount,rating,time) separately. Am I correct in assuming that I need to do three different compound indexes {searchField :1, viewcount:1}, {searchField :1, rating:1} and {searchField :1, time:1} ?

Also one query could search with the searchField then do a range query and a sort on the time field, the last index would be the correct one too in this situation right ?


texts:
I have Drupal site on my local server build using pressflow

I had problems with the xammp 1.7.4 server, so I delete everything except mysql and htdocs folders.

I installed the server again, but the drupal site didn't work again.

There are 186 tables in my Drupal database, but phpmyadmin shows only 119 InnoDB tables

The rest of the tables are MyISAM, but they didn't show in phpmyadmin and it looks like Drupal can't find them too

I don't know where is the actual problem and how to fix it. Help me please.


texts: Optimizing the query with 2 distinct counts in select

texts:
This SQL works perfectly but it is pretty slow for me.
I did all performance improvement in my database. More complex queries works faster. ( Postgresql 10.x )

How can I optimize or make this query faster?

SELECT
  COUNT(DISTINCT a.message_id) :: DECIMAL / COUNT(DISTINCT b.order_item_id) :: DECIMAL
FROM messages a
INNER JOIN order_items b
  ON a.product_id = b.product_id;

Aggregate  (cost=53169.94..53169.96 rows=1 width=32) (actual time=10281.758..10281.758 rows=1 loops=1)
  ->  Nested Loop  (cost=0.29..14266.71 rows=7780646 width=8) (actual time=0.015..4054.449 rows=7811063 loops=1)
        ->  Seq Scan on messages a  (cost=0.00..775.82 rows=15582 width=8) (actual time=0.007..4.527 rows=15582 loops=1)
        ->  Index Scan using order_items_product_id on order_items b  (cost=0.29..0.73 rows=14 width=8) (actual time=0.005..0.174 rows=501 loops=15582)
              Index Cond: (product_id = a.product_id)
Planning time: 0.450 ms
Execution time: 10281.813 ms


texts: Understanding the simple execution plan in PostgreSQL

texts:
I'm new to execution plans still trying to learn.
My problem is for below query

select * from item limit 3;

I got following execution plan,

"Limit  (cost=0.00..0.07 rows=3 width=75)"
"  ->  Seq Scan on item  (cost=0.00..2345.00 rows=100000 width=75)"

According to my understanding this first limit the number of records to 3 and then do a sequential scan for all the records. Why does this do a scan for all the records since it has already limited the result to 3 rows ?


texts:
I'm trying to figure out external sort performance and it's dependence on WORK_MEM value.
I've tested that increasing WORK_MEM doesn't always speed up external sorting (example below, tested on PostgreSQL 9.6).
Is there any guidance on setting WORK_MEM, say what value of WORK_MEM is optimal to sort 4GB table using external sort?
Is it possible to estimate number of IOs from table size and WORK_MEM setting?

I understand that i can study tuplesort.c and underlying algorythms, is there ready results?

create table t1
as
select
  c as key
, cast('value' as char(5)) as val
from generate_series(1, 100000000) c;

--4223 MB
select pg_size_pretty(pg_total_relation_size('t1'));

\timing

--time - 146725.833 ms
SET WORK_MEM = '128MB';
create table t_128 as select * from t1 order by key;

--time - 148889.655 ms
SET WORK_MEM = '1024MB';
create table t_1024 as select * from t1 order by key;


texts:
I want to do ABC analysis on this.
As you see, each model has severval partID, each partID has a number of counts.
As you see, the Cum_Counts is wrong when 'ties' exists.

I want to calcute Cumulated counts in each Model.

SELECT
      Model,
      PartID,
      PartDesc,
      Counts,
      SUM(Counts) OVER (PARTITION BY Model ORDER BY Counts DESC) AS Cum_Counts,
      SUM(Counts) OVER (PARTITION BY Model) AS Total_Counts
FROM ASL_2
ORDER BY Model


texts: how to optimise this group by on sys.dm_exec_query_stats?

texts:
I would like to know the last 1008 queries run in my systems and with this intent I have put this query together:

-- get the last 1008 queries run
SELECT top 1008
execquery.last_execution_time AS [Date Time],
Database_Name=DB_NAME (execsql.dbid),
execsql.text AS [Script]
FROM sys.dm_exec_query_stats AS execquery
CROSS APPLY sys.dm_exec_sql_text(execquery.sql_handle) AS execsql
ORDER BY execquery.last_execution_time DESC

and this is currently giving me the following result in one of my servers:

Looking at the picture, it seems that the same piece of code appear several times, within the same millisecond.

I modified the script and added a group by, however, the cost to run this query became too expensive.

-- get the last 1008 queries run
SELECT top 1008
execquery.last_execution_time AS [Date Time],
Database_Name=DB_NAME (execsql.dbid),
execsql.text AS [Script]
FROM sys.dm_exec_query_stats AS execquery
CROSS APPLY sys.dm_exec_sql_text(execquery.sql_handle) AS execsql
GROUP BY execquery.last_execution_time, execsql.dbid,execsql.text
ORDER BY execquery.last_execution_time DESC

What could be a way to prevent the dupplicates to show up and at the same time not to kill my server when I try to run it?


texts:
I have two databases which return different results based on the case of the query.

On the first database I run the query

FROM [Temp].[Team].[CLASS]
  WHERE [TABLE_NAME] like '%Link%'

and on the other database I run this query

FROM [Temp].[Team].[CLASS]
  WHERE [TABLE_NAME] like '%LINK%'

The first query returns nothing but the second query returns what I'd expect it to.

Checking the collation between the databases, I find they are both set to SQL_Latin1_General_CP1_CI_AS so I wouldn't expect them to behave differently.

What other settings do I need to alter to give me case insensitive queries without changing my queries?


texts: Slow enumeration of schemas and tables in a largish database

texts:
We have a Postgresql 8.4.8 database server on a Debian system with a database that has 500+ schemas with roughly 300+ tables each. The schemas and tables are created by scripts, we cannot influence the process much short of deleting unnecessary schemas every now and then. Perhaps there were 5000+ schemas during the lifetime of this database, of which 4500- are gone now.

Opening the database using pgAdmin3 takes ages. Opening a schema takes ages. Autocompletion in psql is horribly slow. This does not happen with "small" databases. I have tried VACUUM ANALYZE pg_tables, but this is not supported.

Are there some cleanup tasks that need to be carried out to handle this situation? Or perhaps there is a tuning parameter that can be set? An index on a system table missing or outdated?

As a side question: Is it possible to group several schemas so that there is yet another hierarchy level between "database" and "table"?


texts:
I have a table with columns ID, TYPE, DATE, ... and I need all data sets with TYPE <> 'A' (case 1) and the data sets with TYPE = A if there is no TYPE = B with the same ID (case 2). The first case is easy so I think to use UNION to combine the first and the second case. But I have no idea how to select the second case. I'm experimenting with self joins like:

SELECT *
FROM MY_TABLE TAB1
JOIN MY_TABLE TAB2
ON
  TAB1.ID = TAB2.ID
  AND TAB1.TYPE = 'A'
  AND TAB2.TYPE = 'B'

This way I get the sets which I don't want. I think I need a subquery but I have no idea in which direction I should work.

Maybe something like this:

SELECT *
FROM MY_TABLE TAB1
WHERE TAB1.TYPE <>'A'
UNION
SELECT *
FROM MY_TABLE TAB1
NOT IN
(SELECT *
FROM MY_TABLE TAB1
JOIN MY_TABLE TAB2
ON
  TAB1.ID = TAB2.ID
  AND TAB1.TYPE = 'A'
  AND TAB2.TYPE = 'B')

Example:

ID | TYPE | ...
---------------
1  |  B   | ...
2  |  A   | ...
2  |  B   | ...
3  |  A   | ...

I need row 1 (TYPE <> 'A'), row 3 ( TYPE <> 'A') and row 4 (there is no set with same ID and TYPE = 'B'). I don't want row 2, because TYPE = 'A' and there is a data set with same ID and TYPE = 'B'. So the result should be:

ID | TYPE | ...
---------------
1  |  B   | ...
2  |  B   | ...
3  |  A   | ...


texts: MySQL 5.7 Got an error reading communication packets

texts:
I have a PC running windows 7 and MySQL 5.6. Data is uploaded to the server via java. They work nicely without any error.  I just migrate to windows 10 and MySQL 5.7. The same program is used to upload data without any error in the java side. I also check that the data are written into the database. However, i got lots of [note] in the error log

Aborted connection 48 to db: 'XXX' user: 'root' host: 'localhost' (Got an error reading communication packets)

I have try following (MySQL Error Reading Communication Packets) and same result occurred.  Whats wrong ?

    [client]
no-beep
port=3306

[mysql]

default-character-set=utf8

port=3306

datadir=C:/ProgramData/MySQL/MySQL Server 5.7\Data

character-set-server=utf8

default-storage-engine=INNODB

sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

log-output=FILE
general-log=0
general_log_file="YANJAR2.log"
slow-query-log=1
slow_query_log_file="YANJAR2-slow.log"
long_query_time=10

log-error="YANJAR2.err"

server-id=1

secure-file-priv="C:/ProgramData/MySQL/MySQL Server 5.7/Uploads"

max_connections=1024

query_cache_size=20M

table_open_cache=2000

tmp_table_size=136M

thread_cache_size=9

myisam_max_sort_file_size=100G

myisam_sort_buffer_size=262M

key_buffer_size=8M

read_buffer_size=64K
read_rnd_buffer_size=256K

innodb_flush_log_at_trx_commit=1

innodb_log_buffer_size=8M

innodb_buffer_pool_size=1024M

innodb_log_file_size=512M

innodb_thread_concurrency=9

innodb_autoextend_increment=64M

innodb_buffer_pool_instances=8

innodb_concurrency_tickets=5000

innodb_old_blocks_time=1000

innodb_open_files=300

innodb_stats_on_metadata=0

innodb_file_per_table=1

innodb_checksum_algorithm=0

back_log=80

flush_time=0

join_buffer_size=256K

max_allowed_packet=256M

max_connect_errors=200

open_files_limit=4161

query_cache_type=1

sort_buffer_size=256K

table_definition_cache=1400

binlog_row_event_max_size=8K

sync_master_info=10000

sync_relay_log=10000

sync_relay_log_info=10000

bulk_insert_buffer_size=256M


texts: Is it possible to install SQL Server 2014 Enterprise on Windows 7?

texts:
I'm planning to install SQL Server 2014 Enterprise Edition on a 64-bit HP workstation.

I read the software and hardware requirements on:

https://msdn.microsoft.com/en-us/library/ms143506.aspx

And according to that page, it doesn't seem that Enterprise Edition supports Windows 7. Is the reference right?


texts: SQL Server Replication with 2 systems in different locations

texts:
I have 2 Servers with static public IP Addresses located one in UK and another in US.

Can I implement SQL Replication on these 2 servers located in 2 different locations accessible via public IP addresses and the 2 systems not fall under any common domain or VPN?

I have tried to implement it on my servers. Here I faced the issue that to add subscriber it's asking machine name. It's not accepting IP address.

It's giving me an error saying replication requires actual server name to make connection to the server.

Do we need to put these 2 servers in Active Directory or any other VPN?


texts: Oracle Select with xmlquery() error - How to ignore errors?

texts:
In trying to extract a subset of data from a Oracle DB table, one of the columns is XMLType, which is extracted with xmlquery() for relevant information.

The problem is that the XML data is from an outside source, which often gives us malformed XML with unescaped special characters.

ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00244: invalid use of less-than ('<') character (use &lt;)
Error at line 1
31011. 00000 -  "XML parsing failed"
*Cause:    XML parser returned an error while trying to parse the document.
*Action:   Check if the document to be parsed is valid.

It would be ok for my purposes to ignore these rows.

How can I ignore these parse errors and have Oracle continue on with the SQL statement?

Thanks


texts: Indications from obscenely high Operator Costs in Query Plan?

texts:
I have a monthly ETL process that's too complicated to explain here, but basically we get a large amount of customer data, bulk load it into various tables, then run a series of build processes.

The volume of total data is large, we add 25-30m records monthly to the largest table, which has around 700m records total.  There are support tables from 2m-300m records each as well.  Total data for this set is around 3.5TB.

This month, some processes are taking exponentially longer than usual.  One example is a proc that ordinarily completes in 30 minutes took around 40 hours before I killed it (it still hadn't completed).

Not all processes are affected, and some run even faster than usual.

Obviously the root cause is beyond the scope of something I can ask here, but there is something that bothers me:

We have actual query plans showing in the millions of % for individual operators.

Like a clustered index scan for 791,358,704%, a merge join for 75,566,494%, etc.  This occurs in multiple query plans, and these are plans taken from sys.dm_exec_query_plan.

Are these query plans indicative of some other larger issue?

I know with a certainty the problem is not out of date stats - we ran fullscans on all the major tables, and the biggest table and DB I manually rebuilt with pre-sizing to eliminate fragmentation.


texts:
I'm just getting into sql-server, so forgive me if this is a basic question. I'm building an application using .NET 4.0 and VS 2010 and ms-server2008.

For my application I need to detect the server response, suppose: in db-server I need to detect which table insert/update/delete records, and which is the affected record is?

Is there any mechanism or technique to detect the server response.

If have any questions please ask.


texts: How to reclaim database space on Azure SQL Server after setting nvarchar values to null

texts:
There is db space challenge at hand on Azure SQL Server database. 490 GB out of 500 GB is used. That's what I read from nice pie chart on Azure portal.
Notice that some of the techniques used on-prem won't work for Azure SQL Server Database.

Biggest table is DidbStudySerumLevelDeviation.

exec sp_spaceused 'RulesEngine.DidbStudySerumLevelDeviation '

returns

+------------+------------------------------+
| name       | DidbStudySerumLevelDeviation |
| rows       | 20 805 409                   |
| reserved   | 133 332 840 KB               |
| data       | 123 079 792 KB               |
| index_size | 9 772 192 KB                 |
| unused     | 480 856 KB                   |
+------------+------------------------------+

Table definition:

CREATE TABLE [RulesEngine].[DidbStudySerumLevelDeviation](
    [Id] [uniqueidentifier] NOT NULL,
    [PatientTherapyId] [uniqueidentifier] NOT NULL,
    ...
    [Abstract] [nvarchar](max) NULL,
    ...
 CONSTRAINT [PK_RulesEngine.DidbStudySerumLevelDeviation] PRIMARY KEY CLUSTERED
(
    [Id] ASC,
    [PatientTherapyId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)

Clustered index exists on primary key. There are also some nonclustered indexes.
Abstract column data takes around 90% of space of a table.

Procedure is run to set unnecessary Abstract column data to null. That should reduce problematic column "weight" to 10%. However this doesn't reduce database size. How to reclaim that database space?

Maybe I should wait and some Azure SLQ maintenance magic occurs? It sounds like wishfull thinking though.

Regarding possible duplicate

Answer to this question might me specific to Azure deployment of SQL Server. Drop in database size reported in Azure portal or read from exec sp_spaceused didn't change.


texts: How can one select a static value as a column based when using UNPIVOT?

texts:
I'm trying to unpivot a few columns in a table. This is for a reporting tool. The source data is already normalized. I am able to get my desired results with a series of UNION ALL statements. I can get close to what I want with UNPIVOT, but I'm missing one part. I'd like to get the source column name in my UNPIVOT results.

Table structure: |  itemNumber  |  type  |  code  |  description  |
                 |  BN3466774   |  AUD1  |  444   |  desc--text   |
                 |  1238udd74   |  AUD1  |  331   |  fdsafdsafdsa |
                 |  AA34fg774   |  MAN3  |  874   |  asdfasdfasdf |

Desired Output:  | itemNumber   | SourceName | SourceValue |
                 | BN3466774    | type       | AUD1        |
                 | BN3466774    | code       | 444         |
                 | BN3466774    | description| desc--text  |
                 | 1238udd74    | type       | AUD1        |
                 | 1238udd74    | code       | 331         |
                 | 1238udd74    | description| fdsafdsafdsa|
                 ... you get the idea

As mentioned before, I can get this result using a series of UNION ALL statements

SELECT *
FROM
(
SELECT itemNumber, 'type' as SourceName, type as SourceValue FROM myTable
UNION ALL
SELECT itemNumber, 'code  ' as SourceName, code as SourceValue FROM myTable
UNION ALL
SELECT itemNumber, 'description  ' as SourceName, description  as SourceValue FROM myTable
) as myValues

My query using UNPIVOT looks like this:

SELECT itemNumber, Value
FROM
(
  SELECT itemNumber, type, code, description
  FROM myTable
) AS tb
UNPIVOT
(
  Value FOR attributes IN ( type, code, description)
) AS myValues

This query results in the following data:

Desired Output:  | itemNumber   | SourceValue |
                 | BN3466774    | AUD1        |
                 | BN3466774    | 444         |
                 | BN3466774    | desc--text  |
                 | 1238udd74    | AUD1        |
                 | 1238udd74    | 331         |
                 | 1238udd74    | fdsafdsafdsa|

The reason I'd like to go with the UNPIVOT is because it tends to execute about 50% faster than the UNION ALL approach, and in one query I've got about 16m rows being returned.

I'd like to get the source column into my UNPIVOT results, but I've been unable to generate a query that does this.


texts: MySQL pivot query trouble sorting column by total

texts:
I am trying to return results for a matrix that represents the net weight that a country imports from another country.

I have managed to write a query that produces a pivot type table in mysql all data is coming from a single table. I have managed to dynamically create the columns (export countries) and sort rows by the total net weight imported by that country.

Where I am coming undone is sorting the export countries which are the columns. I am easily able to sort them alphabetically, however I need to somehow total each column when I dynamically generate that column and then sort by the sum of that column.

Here is an example of the table before the my query:

REPORTER | PARTNER | NET_WEIGHT | YEAR | COMMODITY
--------------------------------------------------
Spain    | USA     | 3          | 2010 | wheat
Mexico   | France  | 5          | 2011 | wheat
Norway   | USA     | 2          | 2012 | wheat
Egypt    | Canada  | 5          | 2010 | wheat
Germany  | UK      | 1          | 2011 | wheat
Peru     | France  | 3          | 2011 | wheat

This is an example of the structure that I am aiming to achieve.

REPORTER  | TOTAL  | USA   | France  | Canada  | UK
------------------------------------------------------
TOTAL     |        | 5     | 4       | 3       | 3
------------------------------------------------------
Spain     | 9      | 3     | 4       | 2       | NULL
Egypt     | 6      | 2     | NULL    | 1       | 3
Germany   | 3      | 1     | NULL    | NULL    | NULL

Here is the query I have produced so far which produces something similar to the above table, however not sorted correctly:

SET @@group_concat_max_len = 500000;
SET @sql = NULL;

SELECT GROUP_CONCAT(DISTINCT CONCAT('GROUP_CONCAT(IF(`Partner` = ''', `Partner`,''', `NetWeight`, NULL)) AS ''',`Partner`,'''')
ORDER BY `Partner` ASC)
INTO @sql FROM `tblAnnualData`;

SET @sql = CONCAT('SELECT `Reporter`,SUM(`NetWeight`) AS Total,', @sql,' FROM `tblAnnualData`
                    WHERE `Commodity` = ''wheat''
                    AND `Year` = 2013
                    GROUP BY `Reporter`
                    ORDER BY `Total` DESC');

As you can see in the statement I am able to sort the columns by 'Partner' alphabetically but what I would like to achieve is sorting the sum of the column net_weight descending. So that the highest values are in the top left decreasing as you go right and down the table.

Can this be done? I have seen examples using  WITH ROLLUP but can't seem to get anything to work.


texts: Logs growing and took up entire log drive even with log backups

texts:
I have a volume just for logs and another volume just for log backups.

The log volume kept  growing until it to run out of space even with log backups taken every 1 minute.

Isn't the backups suppose to free up the space within the log file to be written over again?

It seems like this isn't happening.


texts: Where does InnoDB store transaction data before committing it?

texts:
I've done some tests using READ_COMMITTED and READ_UNCOMMITTED at home, using the JDBC technology.
I see that READ_UNCOMMITTED can actually read uncommitted data, e.g. data from some transaction not yet committed (could perform an UPDATE-query).
Questions

Where is uncommitted data stored, such that a READ_UNCOMMITTED transaction can read uncommitted data from another transaction?
Why isn't it possible for a READ_COMMITTED transaction to read uncommitted data, i.e. performing a "dirty read"? What mechanism enforces this restriction?


texts: Trying to better understand how SELECT rights are managed in a/my SQL Server db

texts:
Sorry, I'm a security noob:

I've been asked to provide write a script that grants our users that belong to some yet unidentified domain group SELECT access to a table called "NewTable1." To identify the global group, I was told to check out "ReportingTable1" and see who (which domain group) has SELECT access to that table and then grant the same group access to NewTable1.

So, in SSMS (2008 R2), I script ReportingTable1 making sure that the option to script permissions was first enabled. When I inspect the generated scripts for ReportingTable1, I only see VIEW DEFINITION rights granted to various domain groups.

Understanding that one might have rights to see the defintiion of an object but not necesarily the object itself, I realize that having VIEW access does not necessarily mean that one will have SELECT access.

Q: Why don't I see SELECT accesss granted when I clearly have select access?

I realize that for a different db, my local db when I am the admin, that I must have implied SELECT rights on all of the tables simply because I belong to the admin group. Therefore, belonging to a role must also give me access rights associated with that role, yet I am unable to determine what those rights are. Are they implied and well known for all of the built-in roles?

Obviously, I have a lot to learn. I expect flames and suggestions to buy a book and read.


texts: How to set Database Recover Mode to Full for all databases?

texts:
Using a command similar to:

ALTER DATABASE test0402er2Y8 SET RECOVERY FULL
GO

How can I automatically execute this on all databases in server that are in simple mode now (using SQL Server 2012) ?

Thanks


texts:
I am helping troubleshoot some problems on a SQL Server box.  An application that used to run without problem is now failing with connection pool errors, and no changes have been made to the application that could cause this new problem.

In investigating possible causes of the connection pool error, I came across this post, which gave the "max worker thread" setting as a possible cause.  I checked the production server, and this setting was 54 (it was 89 in dev and 79 in QA).

Is there any way of seeing a log/history of when this setting was changed?
Is there any reason to have the setting this low (this MSDN article recommends a minimum setting of 256 for 32-bit processors, 512 for 64-bit)?
Does the low setting generally seem odd, since someone would have had to consciously set it?


texts:
We have an document management application uses SQL Server 2012 to store document index information and uses a Windows file share to store the documents.  We are working on migrating to store the documents within a SQL FileStream.  However, we have encountered one issue.

According to this document:
http://download.microsoft.com/download/d/9/4/d948f981-926e-40fa-a026-5bfcf076d9b9/FILESTREAM%20Design%20and%20Implementation%20Considerations.docx

It states that “It is not possible to back up a single file as a unit of the filegroup or to restore a single file from the filegroup backup. Keep in mind that you can export a single file when you restore the primary filegroup and then the FILESTREAM backup to a separate instance“.

There are numerous times when a client asks us to restore a single file because they accidentally modified it.  With our current system (using a Windows file share that is backed up continuously) we can go to our backup system (Microsoft DPM) then select and restore just a single file to it's original location.  If we use FileStream to store the documents within SQL, how can we restore just one document, not the entire database?


texts: In Amazon Redshift, running GRANT as a superuser does not grant privileges on table to a regular user

texts:
I'm trying to run a query in Amazon Redshift, but I keep getting the error

permission denied for relation mytable

As far as I understand, this is a privileges issue.

According to this question:
https://stackoverflow.com/questions/15520361/permission-denied-for-relation

Running this command as a superuser:

GRANT ALL PRIVILEGES ON TABLE mytable TO myuser;

Should get rid of the problem, but it's not.

I'm connecting to Amazon Redshift as a superuser, issuing the command and Amazon Redshift replies back with a:

GRANT executed successfully message plus the running time.

However, when I try to query that table as the user I'm granting the privileges to, Amazon Redshift keeps showing the error

permission denied for relation mytable

I tried to elevate myuser privileges to superuser as well, but that didn't work either.

Also, the error is only shown to that particular table, I can select other tables with that user without a problem, but as soon as I try to run a SELECT or EXPLAIN on that table I get the permission error.

Any help pointing me to the right direction will be greatly appreciated!


texts: nText and String manipulation. SQL SERVER

texts:
Parent question  - Thanks to Paul White and Tom V for answer.

I am making little modification's to Paul White's answer to suit my requirements.

I would like to change the following to replace a string instead of inserting:

  CREATE TABLE dbo.PhilsTable
  (
  comment ntext NULL,
  anothercomment nvarchar(50) NULL
  );

 INSERT dbo.PhilsTable
 (comment, anothercomment)
 VALUES
 (
 CONVERT(ntext,
 N'ProdProd   1Prod Prod1   Prod Production'),
 CONVERT(nvarchar(50), N'. This is inserted.')
 );

 DECLARE c
 CURSOR GLOBAL
 FORWARD_ONLY
 DYNAMIC
 SCROLL_LOCKS
 TYPE_WARNING
 FOR
 SELECT
 TxtPtr = TEXTPTR(PT.comment),
 Src = N'Test',
 Offset = PATINDEX(N'%Prod%', PT.comment)
 FROM dbo.PhilsTable AS PT
 WHERE
 PT.comment LIKE N'%Prod%';

 OPEN c;

 DECLARE
 @Ptr binary(16),
 @Src nvarchar(50),
 @Offset integer;

 SET STATISTICS XML OFF; -- No cursor fetch plans

 BEGIN TRANSACTION;

 WHILE 1 = 1
 BEGIN
 FETCH c INTO @Ptr, @Src, @Offset;

 IF @@FETCH_STATUS = -2 CONTINUE; -- row missing
 IF @@FETCH_STATUS = -1 BREAK; -- no more rows

 IF 1 = TEXTVALID('dbo.PhilsTable.comment', @Ptr)
     BEGIN

        -- Modify ntext value
        UPDATETEXT dbo.PhilsTable.comment @Ptr @Offset 0 @Src;
     END;
  END;

  COMMIT TRANSACTION;

  CLOSE c; DEALLOCATE c;

Column value:

ProdProd   1Prod Prod1   Prod Production

Expected result:

ProdProd   1Prod Prod1   Test Production

Current result:

PTestrodProd   1Prod Prod1   Prod Production


texts: Send trigger email when someone connects to a database

texts: How do I set up a trigger or alert so that when anyone connects to a specific database, an email will get send with that user's login credentials?


texts:
I'm a noob when it comes to SQL or PHP and I'm a creating a rather simple car-sharing web site. The user can engage a trip someone created and can himself create a trip. Now I'm currently designing the SQL model, and I came across some doubts that maybe someone can help me with.

I want to save the user that created a given trip. That's easy, the tables "Trips" and "Users" have to be related, and the table "Trips" has the foreign key of "User" table, containing the user's ID that's related to a given trip.

The thing is, I wanted the user to log in and be given the chance to see: a) the trips he's engaged with; b) the trips he created. So, how can I separate data in order to have not only access to the user that created the trip, but also to users that are engaged in that trip (so that I can use all the info in the site)? What's the best way to arrange this efficiently in a SQL model, to save both the user that created the trip and the users that engaged it.


texts:
I want to drop a few tables in my postgresql database. I am able to drop a few tables but there are 3 tables which I am unable to drop.

I need to drop a few tables in my Postgres DB. I tried the following

Cannot read termcap database; using dumb terminal settings.

jiradb=> drop table "AO_3A3ECC_JIRACOMMENT_MAPPING";
DROP TABLE
jiradb=> drop table "AO_3A3ECC_REMOTE_IDCF";
DROP TABLE
jiradb=> drop table "AO_3A3ECC_JIRAPROJECT_MAPPING";
ERROR:  cache lookup failed for constraint 1152954
jiradb=>

I get this Cache lookup error. How do i fix this?


texts: Querying number of subscription to each publication

texts:
We have a production box that has about 60 publications on it. Some of these are used, and some seem to not be... What I need to do is create, or find, a script that will get the count of subscribers/subscriptions per publication so I can easily identify unused publications.

We are using SQL Server 2005. :(

Thank you,
Wes


texts:
I have a test environment running on SQL Server 2017 Express that uses only recent data (a job removes data older than three months once per month).

The job does the following:

Using a while loop, it removed data older than three months
Shrinks the database, if its size is above some threshold
Reindexes all tables

This article describes how awful is to shrink the database due to index fragmentation and why reindexing also requires extra space.

Last run started from a 9GB database. Deleting tooks 3-4 minutes and reindexing about 30 seconds. The database was reduced to about 5GB.

Since this is a test environment, I can afford this downtime without any issues.

Question: Is it bad to shrink + reindex after removing a significant part of data?


texts: Child, Parent and Grand-Parent relationship where a child may not necessary have a parent or a grand parent

texts:
Here, I have some problem representing a hostel-room-inmate entities.
First an inmate is given a hostel seat,
Then he is allotted a particular hostel,
Then he is allotted a room.
My approach is that the inmate table contains a parent-link to the room table, the room table contains a parent-link to the hostel table.
table hostel(id, name)
table room(id, hostel_id, room_no)
table inmate(id, room_id, name, etc)

But while at the stage of allotment, the inmate is first allotted a hostel, in which the room_id is to be left blank, thereby meaning that the inmate is not allotted a hostel room and so on...
How do I solve this problem please...
I wanted to use it in Laravel development environment
(NB: I have some idea of creating many-to-many relationship type by creating relationship table, but just asking if there's better approach than this.)


texts: Get the id_user_app if not exists in id_user_rq

texts:
I'm building a application and have this table for users relationships. I need to build some queries like for example:

Who invited me as a friend?
Who I invited to be my friend?
Who I accept as friend?

And need some help because for me are complex queries :(. Also I don't know if open trhead for each of the question or leave all them here in this topic, what's the best?

For the first question: I have this table structure: http://www.dropmocks.com/mBjNvF and need to get the id_user_app if not exists in id_user_rq as the title said. I try this query:

SELECT DISTINCT A.*, p.*, u.email FROM default_relations_users A
    LEFT JOIN default_relations_users B ON (A.id_user_rq = B.id_user_ap
    AND A.id_user_ap = B.id_user_rq AND A.id_user_rq = 1)
    LEFT JOIN default_users u ON (u.id = A.id_user_rq)
    LEFT JOIN default_profiles p ON (p.user_id = A.id_user_rq)
    GROUP BY A.id_user_rq

But it's not working, any advice or help?


texts:
I'm trying add a count column to my table. I want to remove duplicate rows and add an additional column with the count of rows removed.

My Table looks like this:

start             institution_code  year
Indianapolis, IN    IUBLA           2004
Indianapolis, IN    IUBLA           2004
Indianapolis, IN    IUINA           2004
Bloomington,  IN    IUINA           2002
Bloomington,  IN    IUINA           2002

And I'd like the final version to look like this:

start             institution_code  year    Count
Indianapolis, IN    IUBLA           2004    2
Indianapolis, IN    IUINA           2004    1
Bloomington,  IN    IUINA           2002    2

I thought come combination of Count and Group By or even Over but I haven't been able to come up with a working example.

                                              version
---------------------------------------------------------------------------------------------------------
 PostgreSQL 10.7 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-23), 64-bit


texts: what is the difference between mysql community server and mysql cluster

texts: I am starting to look into different database's for different use cases.  I see there is a mysql community server and mysql cluster.  What is the difference between the two?  Are they both database engines/servers or is one an additional service for the other?


texts: Oracle DBAI Theory : is it REALLY not possible to have Oracle Database Corrupted after Instance Crash?

texts:
I'm studying a little DBA stuff and I've come across instance failure/recovery.

Let's say User A does DML and User B also does DML.

At this point, I believe it's safe to assume DB buffer cache contains changed table blocks and undo segments, and also that log buffer contains redo information for the new table blocks value and the new undo segments value.

Case 1 : No user issues a COMMIT and no writes to disk by DBWn and instance crash. Result : DB not corrupted. It's as if their DML never occurred.
Case 2 : Only User A issues a COMMIT. User B's uncommitted changes are written to disk then the instance crashes. LGWR will have written enough data in the redo log , especially User B's undo segments, so that the recovery's rollback phase will make the database consistent again
Case 3 : No user issues a COMMIT but their changed table blocks are old enough to make DBWn write them to disk. Then BOOM! Instance crashes. How will SMON perform rollback to clear dirty blocks from datafiles here? Especially since there is no undo segment values for them in the redo log here.


texts:
I have a large table that is already partitioned by an identifier (CustomerId). I have created new customers, and want to add new partitions to the table, and want to confirm the correct way to do this. My predecessor set this up and is no longer available so I cannot confirm.

There are currently partitions for 60 distinct CustomerIds (let's say 1-60), and a filegroup and file for each. I have created new filegroups and files for the new customers (61-68) and need to partition the existing data already in this table. So, the data that's already in the table, with customerId 61-68 needs to be moved into separate partitions.

I tried to use the partition wizard, but couldn't find an option for what I wanted. As far as I can tell, I need to run something like:

ALTER PARTITION SCHEME PS_Details  NEXT USED Filegroup61
ALTER PARTITION FUNCTION PF_Details ()  SPLIT RANGE (61)

I'm looking for someone to point me in the right direction as to best practice for this sort of thing. Do I take the data out of the table, into a new table stored in the Filegroup61 then switch the partition in? Not really sure how to approach this.


texts:
I'm having an issue where an application (MS Nav 2015) invoked query (Select) on occasion is receiving a login timeout. This issue happen sporadically, and the query itself is run at 15 minute intervals throughout the day without issue. The remote login timeout was set at 10 seconds, which I have reconfigured to 35 seconds. However, I'm curious as to why login timeouts occur in completely random intervals.
Error Message :
 DS-Log-Live, D-Plan All Day Automated JAS response processing., A call to ADODB.ConnectionClass.Execute failed with this message: OLE DB provider "SQLNCLI11" for linked server "S-FEN-SQLHYP-03" returned message "Login timeout expired".


texts:
I want to create a table in my database to store the exchange rates for currencies so I don't have to make an api call every time.

My table has the following structure:

from_currency
to_currency
rate

If I have 170 currencies the table will have 170 * 170 (~29.000) entries. This seems a bit much for me. Is this the normal way to store exchange rates?


texts: SSIS execute task running wscript.exe hangs when started from SQL Server Agent

texts:
I have a problem with sql server 2012 SSIS package which has execute step running wscript.exe utility.

When I run this package from BIDS everything works fine, when I start package from SSIS it also starts without any problem. But once I start this package as a step in sql agent job - it hangs, and do nothing. The problem is with execute step, once I disable it in package SQL agent executes package fine.

I've tried to add SQL Agent's credentials security rights on folder with wscript.exe utility, I've also tries to add sql server proxy account. But still nothing helped.

Can you please advise how can I fix it?

UPDATE:

Once I run package in Dev Tools everythings works just fine, but running package from sql server job causes wscript.exe process hang in process list in a taskmanager. Once I manually kill this process job stops working.

I assume that this behaviour is somehow related to lack of permissions, but even new credentials didn't work for me. Or maybe wscript utility asks for some promt, though running the same script from command line didn't show any.


texts: Differentiate SQL Server Express from SQL Server installed in a server

texts:
I own a VPS server and I was told SQL Server 2008 R2 Express edition was installed on Windows OS 2008R2.

When I opened the SQL Server configuration manager I saw this:

I thought I should see only the SQL Server Express but there is also SQL Server (MSSQLServer). Does that mean both SQL Server Express and SQL Server of different version are installed? If so how will I know which version of SQL Server and SQL Server Express are installed ?
I also see SQL Server full text filter daemon service running which means full text is installed but unfortunately when I right click the table I see the full text index is disabled.

I am confused as to what is installed and what is not installed. Can some one throw a light on this one

When I connect to the default instance with servername as .
I get :

  Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (X64) Jun 28 2012 08:36:30
  Copyright (c) Microsoft Corporation
  Express Edition (64-bit) on Windows NT 6.1
  (Build 7601: Service Pack 1) (Hypervisor)

When I connect to the instance with servername as machinename\sqlexpress
I get:

  Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (X64) Jun 28 2012 08:36:30
  Copyright (c) Microsoft Corporation
  Express Edition with Advanced Services (64-bit) on Windows NT 6.1
  (Build 7601: Service Pack 1) (Hypervisor)


texts: MariaDB RAM usage does not reflect configuration

texts:
I've got 256GB RAM. InnoDB pool buffer size is 128GB, and InnoDB data + indexes in all databases are about 105GB. Most of that data is frequently queried so, correct me if I am wrong, it should reside in buffer_pool?
I thought that innodb_buffer_pool resides in RAM, but top command shows:
MiB Mem : 257873.0 total,    747.0 free,  74239.4 used, 182886.6 buff/cache

Most of users complain about high RAM usage, and in my case it seems that MariaDB is not using all the RAM it should?
ulimit -v says 'unlimited'.
Is that ok? Can I force putting entire innodb_buffer_pool in RAM, and if so, then how?


texts: Sql not exist, returning 0 rows and should return one

texts:
After some time I really don't know what is going wrong in here. (SQL is not my cup of tea I would say, but still need to figure this out hehe)
I have 2 tables, in different schema, that I need to pass the information from the "primary" into the "secondaries" if the "secondaries" don't have that line, so that's why I'm trying to use the not exist.
this is the query that I'm using:
--Select all from the primary
select
    *
from
    (select
         *
     from
         [server\sqlexpress].xptoPrimary.dbo.CcArtigos
     where
         Tipo_Doc in ('INE','INS')
         and Armazem=2
         and data_mov >='01-07-2018') as tprimary
where not exists
      --That not exist in the secondary
      (select *
       from
           [server\sqlexpress].xptoSecondary.dbo.CcArtigos as tsecondary
       where
           tprimary.cod_art = tsecondary.cod_art
           and Tipo_Doc in ('INE','INS')
           and Armazem=2
           and data_mov >='01-07-2018')

So from what I did understood from what I did find online (https://www.tutorialgateway.org/sql-not-exists-operator/), I'm trying to select all from the xptoPrimary that doesn't exist on the xptoSecondary BUT it returns 0 rows and acording to the site, don't see what is the problem with the query, the logic from the site:
SELECT [Column Names]
FROM [Source]
WHERE NOT EXISTS (Write Subquery to Check)

The test db's the primary have 91 lines and the secondary has 90 lines so I should get the row from the primary that the secondary doesn't have but I get 0 rows :/
Edit:
select *
from [server\sqlexpress].xptoPrimary.dbo.CcArtigos
where not exists
  (select * from [server\sqlexpress].xptoSecondary.dbo.CcArtigos as tsecondary)

Edit 2
select * from [server\sqlexpress].xptoPrimary.dbo.CcArtigos AS tPrimary
WHERE NOT EXISTS (SELECT
                      *
                  FROM
                      [server\sqlexpress].xptoSecondary.dbo.CcArtigos as tSecondary
                  WHERE
                      tPrimary.cod_art = tSecondary.cod_art
                      AND tPrimary.Tipo_Doc = tSecondary.Tipo_Doc
                      AND tPrimary.Armazem = tSecondary.Armazem
                      AND tPrimary.data_mov = tSecondary.data_mov);


texts: How to pass parameters to delete statement?

texts:
While executing this PL/SQL block:

CREATE OR REPLACE PROCEDURE procAuth_Title_Delete (auth_ID NUMBER,
                                        title_Nos IN NUMBER)
IS
BEGIN
DELETE FROM AUTHOR_TITLE where author_id='||auth_ID||' and title_number='||title_Nos||';
dbms_output.Put_line ( 'all records have been deleted with the values auth_ID: '||auth_ID||' and title_Nos: '||title_Nos||'.' );
END;
/
EXECUTE procAuth_Title_Delete ( 1234,1 );

I got the error as

ORA-01722: invalid number

I know that there is something wrong in the 'where' clause of delete statement. How to let the delete statement substitute those parameters '(1234,1)' in place of 'auth_ID' and 'title_Nos'?


texts: I recently read about relational database normalization for the first time and I was wondering how best to design my database. If each entry has a url, some text, and a list of probabilities, what is the best way to normalize my database? I was thinking one table with columns id, url, and content, and another with id, probability, and foreign_key_id (referencing the first table), but as an SQL novice I don’t know if this is the best approach, especially since the second table would end up having several times as many rows as the first.


texts: SQL Server 2012 Agent Job not providing full failure message for SSIS Packages

texts:
(this was also posted at http://www.sqlservercentral.com/Forums/Topic1576641-2799-1.aspx)

We are having an issue with 2012 SQL Servers not reporting why a job has failed, this is happening for jobs running SSIS packages in integration services (not the Integration Services Catalog). I have tried to reproduce the error without success and am looking to see if anyone else is seeing this behavior or sees what I am missing.

Usually when you look at the job history for the specific step that failed it will display the error like so:

  Source: Execute SQL Task Execute SQL Task     Description: Executing the query "select * from madeUpTable" failed with the following error: "Invalid object name 'madeUpTable'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.  End Error  DTExec: The package execution returned DTSER_FAILURE (1).  Started:  11:38:05 AM  Finished: 11:38:05 AM  Elapsed:  0.421 seconds.  The package execution failed.  The step failed.

However on some of our jobs we now get a very generic error which makes it very difficult to troubleshoot. If you add an text file in the output for the job step that does receive the whole error, but is set to override at each run (so we lose the error message if the next run is a success).

  Executed as user: domain\username. Started:  3:00:01 AM  Finished: 4:01:47 AM  Elapsed:  3706.97 seconds.  The package execution failed.  The step failed.

I have tried the following to reproduce the problem, but every time I get the full error message:

Changed LoggingMode property in the package to disabled, currently set as UseParentSetting
Changed to execute a stored procedure
Changed procedure code to be wrapped in a try..catch with raiserror(‘test’,16,1)
Changed to raiserror(‘test’,18’1) (wondered if there was a smaller range of suitable error numbers)
Added a Cozyroc task (3rd party) and made that fail

We have this happening on multiple servers, including one that was a new 2012 named instance and one that was upgraded from 2008 R2. We were curious whether the 2008 packages were not upgraded fully/properly with the wizard but this wouldn't be the case for the new 2012 instances.

We are now wondering if this is only happening with certain types of task, but have not managed to find the tasks during testing. Is anyone else experiencing this? Or any suggestions on how to resolve this issue?


texts: How to do an =ANY(SELECT...query in postgresql?

texts:
I'm trying to select all the latitudes and longitudes for a group of users based on their id being in an array stored in another table. Here's my attempt:

SELECT latitude, longitude
FROM userloc WHERE id = ANY( SELECT interested FROM donedeals WHERE deals_id=67);

But it gives me the following error:

ERROR:  operator does not exist: integer = integer[]
LINE 1: SELECT latitude, longitude FROM userloc WHERE id = ANY( SELE...
                                                         ^
HINT:  No operator matches the given name and argument type(s).
       You might need to add explicit type casts.

donedeals has an int column for deals_id and an int array column for interested, which contains id's corresponding to the id column of userloc, which stores latitude and longitude:

 deals_id |  interested
----------+---------------
       67 | {377,387,376}
       64 | {381,384}
       66 | {377,387}

  latitude  |  longitude  | id
------------+-------------+-----
 40.6439417 |  -73.964927 | 384
 40.7554919 |  -73.925891 | 380
 40.6434067 | -73.9657654 | 385
  40.746452 |   -73.90732 | 378
  40.643459 |  -73.964586 | 381
 40.6430341 | -73.9656954 | 382

This is all in Postgres 9.3.5.

I'd like to select all latitudes and longitudes for id's corresponding to the interested array for a given deals_id. This seems like it should be doable in a single call, but I can't seem to figure out the syntax. Any recommendations would be greatly appreciated.


texts: How can I get partition name from value in MySQL/MariaDB

texts:
I have my table partitioned on day of month basis.

Now I need to archive data of the last 16th day date_add(now(), interval -16 day) to another table. For that I need to find out the partition name that contains data of the date, so that I can exchange the partition and export to another table.

But my problem is,I don't know how to find the partition name from value, "I need to exchange and archive.

Your help will be highly appreciated.

Thanks


texts: PostgreSQL logical replication and partition table

texts:
We have a large database and we want a copy of the database in our company for faster access. we are going to achieve this copy by logical replication on PostgreSQL 11.
But we have a big problem.
We have a table with more than 800M rows, for better performance we've decided to partition the table.
We create a partition table with the same name on the origin database table but we got bellow error on subscription creation :

ERROR:  logical replication target relation "public.MY_TABLE" is not a table

At first, do you suggest logical replication for this purpose? could you please give pros and cons on logical replication instead of other approaches.
Is there any solution to replicate a table in partition table or we have to replicate in a normal table and for example we write a job to move data from the table to partitioned table nightly!


texts: Postgres 10 Logical Replication without truncating existing table

texts:
I have been fooling around with Postgres 10 logical replication, trying to learn about it and potentially apply it to a couple of projects. I have observed something that I am wondering about, but I am not sure if I know enough terminology to properly search for the answer. Consider the following:

I have two machines running postgres, they are able to access each other and I have logical replication going from machine 1 (publisher) to machine 2 (subscriber). I wish to flip that relationship however - I want to make 2 the publisher, and 1 the subscriber.

I drop the subscription and the publication on their respective machines and create them on the opposite machines, so now 2 has a publication, and 1 has a subscription. Everything seems just dandy.

The problem is that when I insert something on 2, it does not get published to 1. The subscription/publication are properly configured and seem to be receiving messages from each other.

I played around and discovered that if I truncate the table being replicated from 2 to 1, the replication resumes precisely as expected. This seems like a problem. My database that I'm doing this on is very small, 8 entries, so truncating the table and reloading it is not an issue. But is it possible to resume replication without this?

Assuming I can guarantee that the two tables on two machines have the exact same data when I initialize the publication/replication, is it possible for it to function properly without having to truncate the table on the subscriber node?


texts:
I'm using SQL Server's

sp_send_dbmail

to distribute mail.  Everything is working fine; BUT I tried to test a bad email address and it always reports success; and nothing is in the failed sent items table.

Will this procedure report IF the email isn't sent?  Or is success just getting the mail to the exchange server?

Thanks in advance!


texts:
Take the following table as an example:

GroupID    AlphaID     BetaID

1          0           1
1          1           1
1          2           2
2          0           1
2          1           2
2          2           2
2          3           2
3          0           1
3          1           1

I would like to write a check constraint that checks the following:

1. AlphaID - Check Constraint

The first instance of AlphaID in a GroupID must start at 0

E.g inserting GroupID = 4 with an AlphaID = 0 will succeed
E.g inserting GroupID = 4 with an AlphaID = 1 will fail

AlphaID must be unique within each GroupID and must be +1 greater than current max AlphaID

E.g inserting GroupID = 3 with an AlphaID = 1 will fail (duplicate)
E.g inserting GroupID = 3 with an AlphaID = 2 will succeed (current max + 1)
E.g inserting GroupID = 3 with an AlphaID = 4 will fail (out of order)

2. BetaID - Check Constraint

The first instance of BetaID in a GroupID must start at 1

E.g inserting GroupID = 4 with an BetaID = 0 will fail
E.g inserting GroupID = 4 with an BetaID = 1 will succeed

BetaID can have duplicates within GroupID or may be +1 greater than current max BetaID

E.g inserting GroupID = 3 with an BetaID = 1 will succeed
E.g inserting GroupID = 3 with an BetaID = 2 will succeed
E.g inserting GroupID = 3 with an BetaID = 4 will fail (out of order)

A solution that uses a trigger may also be suitable.


texts: Is there a way to find out if value exists with addToSet?

texts:
addToSet adds values to an array if the value doesn't exist.

If the value already exists MongoDB will not return anything saying that it exsits.

Is there a way to find out if the value exists? (other than going through the array)


texts: Oracle how do i see who connected to my database

texts:
I am using oracle 11g and I would like to see who connected to my database some hours ago. In what file can I see those who have connected to my database through a network connection?

Thanks


texts: Get maximum from aggregated values in a query

texts:
I have a table details it has a column kills and a column player_slot and a column match_id. I want to find the maximum difference between the SUM of kills for player_slot 0-4 and 128-132. Can someone give me a hint?

Thats my table details:

id,
kills,
match_id,
player_id,
player_slot

Thats what I got so far, it's not much:

SELECT SUM(m."kills") AS max_kills
FROM details m
INNER JOIN matches ON matches.id = m.match_id
WHERE m.player_slot IN (0,1,2,3,4)
GROUP BY m."match_id"

SELECT SUM(m."kills") AS max_kills
FROM details m
INNER JOIN matches ON matches.id = m.match_id
WHERE m.player_slot IN (128,129,130,131,132)
GROUP BY m."match_id"


texts: SQL query to get the user, counting the minimum number of records

texts:
I have an table with some records and a column LAST_EDIT_USER.
If I want to select all users with the number of appearances for every user, I would do

SELECT   LAST_EDIT_USER,COUNT(LAST_EDIT_USER)
FROM     EDITED_ELEMENTS_TABLE
GROUP BY LAST_EDIT_USER
ORDER BY COUNT(LAST_EDIT_USER) ASC;

and this will return something like

NULL   0
user1  5
admin  7
USER2  11

How can I get from a SQL filter the user1 (with the minimum of occurrences) - excepting NULL?
LATER EDIT: or a list of the users with minimum number of occurrences


texts: How to model complex database role relationships

texts:
The purpose of my application is the creation, viewing and editing of articles.

The problem I have is that users will have different roles for an article or group of articles. In addition, the role a user has for a given article or group of articles can only be given to them from a user who has the necessary role (abilities) to make that possible for the article or articles in question.

I am struggling with how to model these relationships.


texts:
I have the following SQL query:

SELECT
  Event.ID,
  Event.IATA,
  Device.Name,
  EventType.Description,
  Event.Data1,
  Event.Data2
  Event.PLCTimeStamp,
  Event.EventTypeID
FROM
  Event
INNER JOIN EventType ON EventType.ID = Event.EventTypeID
INNER JOIN Device ON Device.ID = Event.DeviceID
WHERE
  Event.EventTypeID IN (3, 30, 40, 41, 42, 46, 49, 50)
  AND Event.PLCTimeStamp BETWEEN '2011-01-28' AND '2011-01-29'
  AND Event.IATA LIKE '%0005836217%'
ORDER BY Event.ID;

I also have an index on the Event table for the column TimeStamp. My understanding is that this index is not used because of the IN() statement. So my question is is there a way to make an index for this particular IN() statement to speed up this query?

I also tried adding Event.EventTypeID IN (2, 5, 7, 8, 9, 14) as a filter for the index on TimeStamp, but when looking at the execution plan it doesn't appear to be using this index. Any suggestions or insight into this would be greatly appreciated.

Below is the graphical plan:

And here is a link to the .sqlplan file.


texts: Oracle setup required for heavy-ish load

texts:
I am trying to make a comparison between a system setup using Hadoop and HBase and achieving the same using Oracle DB as back end. I lack knowledge on the Oracle side of things so come to a fair comparison.
I am looking for what kind of Oracle setup is required to handle a certain work load (hardware, OS, software stack, etc.).
The work load and non-functional requirements are roughly this:

12M transactions on two tables with one simple relation and multiple (non-text) indexes within 4 hours. That amounts to 833 transactions per second (TPS), sustained. This needs to be done every 8 hours.

Make sure that all writes are durable (so a running transaction survives a machine failure in case of a clustered setup) and have a decent level of availability?
With a decent level of availability, I mean that regular failures such as disk and a single network interface / tcp connection drop should not require human intervention. Rare failures, may require intervention, but should be solved by just firing up a cold standby that can take over quickly.

Additionally add another 300 TPS, but have these happen almost continuously 24/7 across many tables (but all in pairs of two with the same simple relation and multiple indexes)?

Some context: this workload is 24/7 and the system needs to hold 10 years worth of historical data available for live querying. Query performance can be a bit worse than sub-second, but must be lively enough to consider for day-to-day usage.
The ETL jobs are setup in such a way that there is little churn. Also in a relational setup, this workload would lead to little lock contention. I would expect index updates to be the major pain. To make a comparison as fair as possible, I would expect the loosest consistency level that Oracle provides.
I have no intention of bashing Oracle in favor of some non-relational DB solution. I think it is a great database for many uses. I am trying to get a feeling for the tradeoff there is between going open source (and NoSQL) like we do and using a commercially supported, proven setup.


texts: How to manage users and collections in MongoDB?

texts:
Im triying to create users in a database in mongo, I want to lock the usage of te collections for some users, I'll give an example:

Lets say that I actually have a db with 3 collections:

Books, Students, Proffesors

And 3 users:

Librarian, SecretaryManager, Director

And I want the Librarian have r/w permissions just in Books, SecretaryManager r/w permissions in Students and Professors, and finally, Director that would have the same permissions as Librarian + SecretaryManager.

I've readed the documentation of mongo main website, im aware of using db.adduser, but I haven't find the way of doing this or I've miss something.

Need help, thanks in advance.


texts: Index on a highly updated field causing excessive locks in SQL server

texts:
I'm in a bit of a conundrum about what the best course of action on this (bad?) index is. I have a table which i use as a notification holder that i watch for database changes with.

CREATE TABLE [dbo].[TrackSessions](
[DataPartID] [int] NOT NULL,
[UserMasterID] [int] NOT NULL,
[RecordID] [bigint] NOT NULL,
[DateChanged] [datetime2](7) NOT NULL,

CONSTRAINT [PK_TrackSessions] PRIMARY KEY CLUSTERED
(
    [DataPartID] ASC,
    [UserMasterID] ASC,
    [RecordID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

When a record from any number of other tables is updated i use a trigger to record the change. Then a SqlChangeNotification is triggered on the table and i use the following select to get the new info. This is done several times a second constantly.

SELECT
  [DataPartID],
  [UserMasterID],
  [RecordID],
  [DateChanged]
from dbo.TrackSessions WHERE DateChanged > @DT ORDER BY DateChanged DESC

Here is the index in question:

CREATE NONCLUSTERED INDEX [DateChanged-cr20151217] ON [dbo].[TrackSessions]
(
    [DateChanged] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

Here are the index usage statistics over a 3 week period. (courtesy of [sp_BlitzIndex])

dbo.TrackSessions.DateChanged-cr20151217 (5):
   Op Stats: 0 singleton lookups; 5,553,690 scans/seeks; 0 deletes; 0 updates;
   Reads: 5,529,416 (5,529,416 seek) Writes:1,455,333
   Row lock waits: 212,192; total duration: 226 minutes; avg duration: 0 seconds;
   Page lock waits: 232; total duration: 10 seconds; avg duration: 0 seconds;
   Lock escalation attempts: 1; Actual Escalations: 0.
   Size: 26,741 rows; 7.3MB

For reference here is one of the triggers used to update the rows.

set transaction isolation level serializable
begin transaction
    if exists(select *
                from inserted i with (updlock)
                inner join userD u on u.id = i.id
                inner join UserMaster m on m.UserLoginId = u.userid
                inner join TrackSessions s on s.DataPartID = 2 and s.UserMasterID = m.UserMasterID and s.RecordID = i.id)
    begin
        update s set DateChanged = SYSDATETIME()
        from inserted i
        inner join userD on u.id = i.id
        inner join UserMaster m on m.UserLoginId = u.userid
        inner join TrackSessions s on s.DataPartID = 2 and s.UserMasterID = m.UserMasterID and s.RecordID = i.id
    end
    else
    begin
        --First time this event fired. Should only happen once for a given user
        insert into TrackSessions (DataPartID,UserMasterID,RecordID,DateChanged)
        select 2,m.UserMasterID,i.id,SYSDATETIME()
        from inserted i
        inner join userD u on u.id = i.id
        inner join UserMaster m on m.UserLoginId = u.userid
    end
 commit

So my problem is that i'm getting deadlocks on statements associated with updating this table. I can't quite figure out what exactly the cause is but I believe this is because of[DateChanged] is being fought over.

This is by far the most used index on that table and is extremely valuable in terms of that select performing quickly. But it appears to also be incredibly expensive to maintain.

I'm not sure exactly what to do about it. Would changing the dateChanged data type to something like [datetime2](1) help with the expense of updating? How about just a [datetime]? Should i just stop trying to index it and let the select query scan the table instead?

Its ok if the select takes a second or so longer if it means that i have a reduced chance of deadlocks and i don't really care about fractions of a second as long as the select query will pickup the changed rows.

I'm running on SQL server 2012 with the latest updates in a mirror'd configuration. The pulling application is a .NET SqlConnection.


texts: Get the average of the last X records for each foreign key

texts:
My table is a list of yacht handicaps for yacht racing. After each race a new handicap is calculated based on how the yacht performed which is added to the table. The actual handicap used to modify the finish time of a yacht is the average of the last 3 races. I am having trouble getting my head around the sql to retrieve the race modifier of all yachts.

My table data (I have removed extraneous columns):

id | hcpval | yacht_id
----------------------
 1 | 0.9    |  1
 2 | 0.75   |  2
 3 | 0.84   |  1
 4 | 0.71   |  2
 5 | 0.88   |  1
 6 | 0.73   |  2
 7 | 0.81   |  1
 8 | 0.7    |  2

Obviously, the most recent results have the higher id. After the query has finished I'm hoping to get the following results:

yacht_id | hcpval
-----------------
    1    | 0.843 - avg of ids 7,5 & 3
    2    | 0.713 - avg of ids 8,6 & 4


texts: Suppressing non-ANSI warnings in SQLCMD script

texts:
I am working with a script that creates a SQL Agent job in SQL Server 2014. The job's last 2 steps are "Complete with Success" and "Complete with Failure". The script always warns of non-existent steps.

Warning: Non-existent step referenced by @on_fail_step_id.
Warning: Non-existent step referenced by @on_success_step_id.
Warning: Non-existent step referenced by @on_fail_step_id.

  SET ANSI_WARNINGS OFF

has no effect on these as they are, I imagine, not ANSI warnings.

Is there a way to suppress these?


texts: Unique index updates and statistics row modification counters

texts:
Given the following table, unique clustered index, and statistics:
CREATE TABLE dbo.Banana
(
    pk integer NOT NULL,
    c1 char(1) NOT NULL,
    c2 char(1) NOT NULL
);

CREATE UNIQUE CLUSTERED INDEX pk ON dbo.Banana (pk);

CREATE STATISTICS c1 ON dbo.Banana (c1);
CREATE STATISTICS c2 ON dbo.Banana (c2);

INSERT dbo.Banana
    (pk, c1, c2)
VALUES
    (1, 'A', 'W'),
    (2, 'B', 'X'),
    (3, 'C', 'Y'),
    (4, 'D', 'Z');

-- Populate statistics
UPDATE STATISTICS dbo.Banana;

The statistics row modification counters obviously show zero before any updates:
-- Show statistics modification counters
SELECT
    stats_name = S.[name],
    DDSP.stats_id,
    DDSP.[rows],
    DDSP.modification_counter
FROM sys.stats AS S
CROSS APPLY sys.dm_db_stats_properties(S.object_id, S.stats_id) AS DDSP
WHERE
    S.[object_id] = OBJECT_ID(N'dbo.Banana', N'U');

Incrementing each pk column value by one for every row:
-- Increment pk in every row
UPDATE dbo.Banana
SET pk += 1;

Uses the execution plan:

It produces the following statistics modification counters:

Questions

What do the Split, Sort, and Collapse operators do?
Why do the pk stats show 2 modifications, but c1 and c2 show 5?


texts: How do I know which binary log file to open for continous backup?

texts:
This really has me confused, I want to have point in time recovery so I plan to take occasional full database dumps, and then between those dumps I plan to use stop-never with mysqlbinlog. But I'm really confused on how I use each binary log file?

How do I know which one I'm meant to open when I start reading the binary log?

With stop-never do I have to keep switching binary log files when one is no longer used?

How do I find which log file contains a specific timestamp? Am I really supposed to just search each one? That sounds very inefficient.


texts: Add missing column to SSIS, data not extracted to staging table

texts:
I've inherited an environment that extracts data from a Dynamics AX 2012 ERP to a data warehouse for reporting. The original extraction set up by consultants did not include the proper date column needed by accounting. I have performed the following:

Created the column in the staging tables and destination tables
opened the packages in Visual Studio and found that each package file uses the same variable User::vSQLBase and contains the query that pulls all columns from the table needed in Dynamics AX. I altered that variable to include the date column.
I saved my changes and deployed the entire solution back to the data warehouse.
Ran the job in SQL Server, but none of the columns populated.

I did have an error appear in the debug of Visual Studio when I test executed the package.

  Warning: 0x800470C8 at Incremental Load, Read Delta [26]: The external
  columns for Read Delta are out of synchronization with the data source
  columns. The column "Document Date" needs to be added to the external
  columns.

I did contact the consultant who replied with "the new column is not mapped to the staging table in the package, be aware that both the full load and incremental load need the same mapping. and a full load is mandatory since this is a new column". I am further unable to contact the consultant due to budget limitations. What am I missing to get the column to the staging table?


texts: How to get MIN and MAX date from table data

texts:
We have a nightly job that uses sql loader to insert data from a csv file. Here is a small sample of the data:

select * from ktimov.press_schd order by job;

PLANT JOB EST_COMPLETION INSERT_DATE
10    111 6/26/2017      5/10/2017
10    111 7/1/2017       5/11/2017
10    111 7/5/2017       5/12/2017
10    222 6/20/2017      5/10/2017
10    222 6/26/2017      5/11/2017
10    333 7/26/2017      5/10/2017
10    333 6/30/2017      5/11/2017
10    333 8/10/2017      5/12/2017
10    333 8/6/2017       5/13/2017
10    333 8/7/2017       5/14/2017

INSERT_DATE is default sysdate of when the record was inserted.
EST_COMPLETION is an estimate of when the JOB will be completed. So for example:

On 5/10/17, JOB# 111 was estimated to be completed by 6/26. Then on 5/11 the estimated completion was changed to 7/1.

I'm looking to query this data to show the following:

JOB#, First EST Date, Last Est date, Count of how many times it was changed.

The end result should be like this:

JOB FIRST_DATE LAST DATE Count_Changes
111 6/26/2017  7/5/2017  3
222 6/20/2017  6/26/2017 2
333 7/26/2017  8/7/2017  5

Any ideas how I can accomplish this?


texts: Can a MSSQL job change its own active schedule?

texts:
I am planning a new job for MSSQL Sever 2016.

I'd like the job to run on Sunday, every 10 minutes, until it sees a confirmation record is added to a table it checks as the final step in the job.  If the record is there, the job modifies its current schedule so it will not run again until next Sunday.  This leads me to wonder when the schedule updates in a "natural" job cycle.  I wonder if my change would be overwritten before it could be used.

So, can I use msdb.dbo.sp_update_jobschedule in a job's final step to do this to the current job and what technical pitfalls might I encounter doing that?

Examples and/or alternate solutions are always welcomed.


texts: Replicate Specific Tables with fully qualified statements removed

texts:
Currently we have a replication system that is a master to slave, which that slave is also a master. We are replicating our web server to our stats server so we can report on users traffic as well. We've discovered recently though that our, replicate-rewrite-db rule doesn't correct fully qualified statements (e.g. databases included in the query). So we have

replicate-rewrite-db=web_live->stats

in our my.cnf but our phpmyadmin on updates/inserts generates queries like:

UPDATE
    `web_live`.`users`
SET
    `useremail` =  'chris@example.com'
WHERE
    `users`.`userid` = 85;

where as it should be:

UPDATE
    `users`
SET
    `useremail` =  'chris@example.com'
WHERE
    `users`.`userid` = 85;

or, as we expected:

UPDATE
    `stats`.`users`
SET
    `useremail` =  'chris@example.com'
WHERE
    `users`.`userid` = 85;

because we expected the replicate-rewrite-db to rewrite all calls to the older DB name, however that directive only applies to use cases.

Is there a way to either

just have the table names be called in queries or
safely and reliable remove the DB name?


texts: How to understand "NULL NOT IN" when the set is empty?

texts:
The SQL in this question is for Oracle. But you can simply remove FROM dual to make it work on SQL Server (which returns the same result as Oracle).

I have the following SQL:

SELECT 1
FROM dual
WHERE NULL IN (SELECT 1 FROM dual WHERE 1 = 0);

SELECT 1
FROM dual
WHERE NULL NOT IN (SELECT 1 FROM dual WHERE 1 = 0);

which returns an empty set and 1 respectively. When we substitute some other value for NULL, we got the exact same result.

SELECT 1
FROM dual
WHERE 33 IN (SELECT 1 FROM dual WHERE 1 = 0);

SELECT 1
FROM dual
WHERE 33 NOT IN (SELECT 1 FROM dual WHERE 1 = 0);

So how does the comparison between NULL and the empty result set work here?


texts: How to put an execution time limit for MySQL Temporary table creation

texts:
We can use SET MAX_EXECUTION_TIME=10; statement to stop the execution of a SELECT statement, if it took more than 10 milliseconds to execute the query. But the same wont work for other statements like CREATE TEMPORARY TABLE and INSERT etc.

Reference: https://dev.mysql.com/doc/refman/5.7/en/optimizer-hints.html#optimizer-hints-execution-time

Currently my temporary table creation takes more than 50s to complete. I want to put a limit like 10s. Is it possible in MySQL?


texts: MySQL 5.6 - Running mysqldumpslow on Windows

texts:
I am trying to run mysqldumpslow on Windows server with the command
mysqldumpslow.pl -t 5 -s at H:\MySQL\slow.log > H:\MySQL\summaryslowlog.txt

I have installed ActivePerl on the server, but when I ran the above, I got the following error:
Can't determine basedir from 'my_print_defaults mysqld' output at C:\Program Files\MySQL\MySQL Server 5.6\bin\mysqldumpslow.pl line.
I checked the my.ini file and the following was commented out:-
> [mysqld]
> # Path to installation directory. All paths are usually resolved relative to this.
> # basedir="C:/Program Files/MySQL/MySQL Server 5.6/"

Do I need to uncomment the line for it to work? The server is working fine, so what's the impact of this configuration?


texts: Can I edit single timestamp so it recalculate all the following timestamps in a table?

texts:
If I have a table with a list of tasks with time stamps (start_time and end_time) and minutes of duration, can I change the minutes in one records so it effect and recalculate all the other timestamps in the other records?

For example:

id      task                  start_time      end_time      minutes
1      Send email       12:00            12:10            10
2      Design banner  12:11            12:36            25

3      Call Jake           12:37            12:42            5

Let say I need more time for sending the email and I add 5 minutes so it becomes 15... can I make the whole table recalculate so the end_time for "Call Jake" would be 12:47

Is this possible?