texts: Unable to delete a referenced row due to a trigger on a referencing table?

texts:
I ran into some problems in using triggers to ensure only deletions from cascading deletes:
CREATE TYPE status_type AS enum ('A');

CREATE TABLE parent(
    name VARCHAR(255) PRIMARY KEY
);

CREATE TABLE child(
    name VARCHAR(255) PRIMARY KEY
);

CREATE TABLE relation(
    childname VARCHAR(255) NOT NULL REFERENCES child (name) ON DELETE CASCADE,
    parentname VARCHAR(255) NOT NULL REFERENCES parent (name) ON DELETE CASCADE,
    status status_type NOT NULL,
    PRIMARY KEY (childname, parentname)
);

CREATE OR REPLACE FUNCTION prevent_deletes_when_status_A() RETURNS trigger AS $$
    BEGIN
        IF OLD.status='A' AND EXISTS (SELECT 1 FROM parent WHERE parent.name=OLD.parentname) THEN
            RAISE EXCEPTION 'Invalid DELETE';
        ELSE
            RETURN OLD;
        END IF;
    END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER tr_onlycascading_fromParent
    BEFORE DELETE ON relation
    FOR EACH ROW EXECUTE PROCEDURE prevent_deletes_when_status_A();

INSERT into parent VALUES ('john');
INSERT into child VALUES ('doe');
INSERT into relation VALUES ('doe', 'john', 'A');
DELETE FROM child WHERE name='doe';

Why does this not work? I understand that it raises the exception message. However, the row in child should be deleted. Am I missing something here?
Shouldn't the row in child be deleted first before the delete cascades to the relation table, where only then would the trigger take effect?


texts:
I always understood that the CASE statement worked on a 'short-circuit' principle in that evaluation of subsequent steps does not take place if a prior step is evaluated to true. (This answer Does SQL Server CASE statement evaluate all conditions or exit on first TRUE condition? is related but doesn't appear to cover this situation and relates to SQL Server).
In the following example, I wish to calculate the MAX(amount) between a range of months that differs based on how many months are between the start and paid dates.
(This is obviously a constructed example but the logic has valid business reasoning in the actual code where I see the issue).
If there are < 5 months between the start and paid dates then Expression 1 will be used otherwise Expression 2 will be used.
This results in the error "ORA-01428: argument '-1' is out of range" because 1 record has an invalid data condition that results in a negative value for the start of the BETWEEN clause of the ORDER BY.
Query 1
SELECT ref_no,
       CASE WHEN MONTHS_BETWEEN(paid_date, start_date) < 5 THEN
-- Expression 1
          MAX(amount)
             OVER (PARTITION BY ref_no ORDER BY paid_date ASC
             ROWS BETWEEN MONTHS_BETWEEN(paid_date, start_date) PRECEDING
             AND CURRENT ROW)
       ELSE
-- Expression 2
           MAX(amount)
             OVER (PARTITION BY ref_no ORDER BY paid_date ASC
             ROWS BETWEEN 5 PRECEDING AND CURRENT ROW)
       END
    END
  FROM payment

So I went for this second query to first eliminate anywhere this can occur:
SELECT ref_no,
       CASE WHEN MONTHS_BETWEEN(paid_date, start_date) < 0 THEN 0
       ELSE
          CASE WHEN MONTHS_BETWEEN(paid_date, start_date) < 5 THEN
             MAX(amount)
                OVER (PARTITION BY ref_no ORDER BY paid_date ASC
                ROWS BETWEEN MONTHS_BETWEEN(paid_date, start_date) PRECEDING
                AND CURRENT ROW)
          ELSE
             MAX(amount)
                OVER (PARTITION BY ref_no ORDER BY paid_date ASC
                ROWS BETWEEN 5 PRECEDING AND CURRENT ROW)
          END
       END
  FROM payment

Unfortunately, there is some unexpected behaviour that means that the values Expression 1 WOULD use are validated, even though the statement will not be executed because the negative condition is now trapped by the outer CASE.
I can get around the issue by using ABS on the MONTHS_BETWEEN in Expression 1, but I feel like this should be unnecessary.
Is this behaviour as expected ? If so 'why' as it seems illogical to me and more like a bug ?

This will create a table and test data. The query is simply me checking that the correct path in the CASE is being taken.
CREATE TABLE payment
(ref_no NUMBER,
 start_date DATE,
 paid_date  DATE,
 amount  NUMBER)

INSERT INTO payment
VALUES (1001,TO_DATE('01-11-2015','DD-MM-YYYY'),TO_DATE('01-01-2016','DD-MM-YYYY'),3000)

INSERT INTO payment
VALUES (1001,TO_DATE('01-11-2015','DD-MM-YYYY'),TO_DATE('12-12-2015','DD-MM-YYYY'),5000)

INSERT INTO payment
VALUES (1001,TO_DATE('10-03-2016','DD-MM-YYYY'),TO_DATE('10-02-2016','DD-MM-YYYY'),2000)

INSERT INTO payment
VALUES (1001,TO_DATE('01-11-2015','DD-MM-YYYY'),TO_DATE('03-03-2016','DD-MM-YYYY'),6000)

INSERT INTO payment
VALUES (1001,TO_DATE('01-11-2015','DD-MM-YYYY'),TO_DATE('28-11-2015','DD-MM-YYYY'),10000)

SELECT ref_no,
       CASE WHEN MONTHS_BETWEEN(paid_date, start_date) < 0 THEN '<0'
       ELSE
          CASE WHEN MONTHS_BETWEEN(paid_date, start_date) < 5 THEN
             '<5'
         --    MAX(amount)
         --       OVER (PARTITION BY ref_no ORDER BY paid_date ASC ROWS
         --       BETWEEN MONTHS_BETWEEN(paid_date, start_date) PRECEDING
         --       AND CURRENT ROW)
          ELSE
             '>=5'
         --    MAX(amount)
         --       OVER (PARTITION BY ref_no ORDER BY paid_date ASC ROWS
         --       BETWEEN 5 PRECEDING AND CURRENT ROW)
          END
       END
  FROM payment


texts:
How do I know if I need to run any of VACUUM, REINDEX, ANALYSE? Because I don't want to run this every day even if it is not needed.

For example, is it a good option to run all of above after 100 updates?

Is there some query that will tell me "Yes, you need to run Vacuum on table xyz"? Something like self analysis for performance optimization.


texts: How to create a database or role in pgAdmin4?

texts:
I want to create a database in pgAdmin4 but a corresponding item in the menu is grayed out.

I also tried to create a role, thinking that I might be lacking permissions to create database in first place, but it is also grayed out.

When pgAdmin4 loads it asks for master password, which I enter. What else am I expected to do?


texts: How do I use an OR in SQL Profiler for Column Filters

texts:
I am setting up a SQL Profiler and I wanted to have it filter on any items that have a duration > 30000 OR CPU > 10000 OR Writes > 1000

It seems like if I put those into the "column filter" area that it is an AND (i.e. I only get items when the duration, cpu and writes are over those limits).

Is there a way to have those be OR so that I get any items that fit any one of those criteria?  I know I could start up three different profilers, each with the specific criteria, but it would be nice to have that all in one window/output.

I am currently on SQL 2008 R2, but hoping there is a general answer to this item.


texts:
select *  from "StudentMaster" where "PhoneNo" = NULL;

Syntax error!

i want to see all record which don't Have Phone number !

I have try this all thing.

select *  from "StudentMaster" where "PhoneNo" NOT LIKE ('%');

select *  from "StudentMaster" where "PhoneNo" NOT IN ('%');

But it show all record which have Phone Numbers but NOT NULL! Why


texts: Connection string for connecting mongo database with SSL enabled

texts:
Am facing some below problem in my mongodb replication environment

I have 3 replica set environment with SSL enabled and ssl mode as preferSSL

3 servers has 3 different Key file with common CA file

when i try to connect the mongo shell with below command it works fine

mongo --ssl --host hostname:port --sslPEMKeyFile "/home/mongod/keyfile.pem" --sslCAFile /home/mongod/CAfile.pem

My question is how to provide these details in connection string to connect the database from application perspective

MongoDB version: 4.0.2
OS: Amazon Linux

Thanks,
Vignesh


texts:
We have a handful of SQL Server databases. Probably not relevant but just for info:

OS: Windows Web Server 2008 R2
RDBMS: SQL Server 2012 Express

My question is: When using SQL Server Management Studio to back up a database, is the resulting .bak file all one would need to fully restore the database on another machine?

I've been doing this for years in development environments, so to my mind the answer is yes, but we are migrating a production server so I wouldn't want to risk anything. Are there any log files, config files, [?], that might hinder performance/integrity/[?] if omitted from a restoration?


texts: Constraint for a unique column if other column has same value

texts:
I'm wondering if it's possible to have a column be unique for each value of another column in MySQL. What I'm wanting to have is the same functionality as what a GROUP BY would do.

Here's the table:

CREATE TABLE Example (
    id INT,
    name VARCHAR(100) NOT NULL
);

The data would be similar to this:

INSERT INTO Example (id, name) VALUES(1, 'Peter Parker');

The following would work:

INSERT INTO Example (id, name) VALUES(1, 'Bruce Wayne');
INSERT INTO Example (id, name) VALUES(2, 'Peter Parker');

The following would not work (as there's the same name with that id):

INSERT INTO Example (id, name) VALUES(1, 'Peter Parker');

Hopefully this can help clear things up for what I want:

There can be multiple rows with the same ID
There can be multiple rows with the same name
There cannot be multiple rows with the same ID and name


texts: Is it possible to drop data from OLAP database (and to keep definitions)?

texts:
[SQL Server Analysis Services from MS SQL Server 2008 R2 Standard]

Our OLAP DB takes data from OLTP DB. We want to make some changes to its structure and to do this - it would be helpful if we could transfer only current OLAP DB definitions and not data which is "buffered" inside. Then OLAP DB could re-take data from OLTP DB again.

Is it possible?


texts: Double associative entity in a business environment involving Order Item Reviews?

texts:
Suppose you have Item (item_id, item_name) and Order (order_id, user_id, order_date) entities, with primary keys denoted in bold. Because an order can consist of multiple items, and an item can be present in multiple orders, there's a many-to-many relationship between these entities that's normally broken down by inserting an additional associative entity between them, in this case Item–Order (item_id, order_id, item_count) with an additional attribute, and splitting the many-to-many relationship into two one-to-many relationships.

So far we have the following:

Item (item_id, item_name)
Order (order_id, user_id, order_date)
Item–Order (item_id, order_id, item_count)

Suppose further that (a) a user can review each received item from their order, (b) providing a review is optional --i.e. the user may or may not enter a review-- and (c) a user can provide at most one review per item. Is it advisable to create the following additional entity type?

Review (item_id, order_id, grade, comment_content, comment_date),

or would it be more customary to subsume it under Item-Order, in which case we'd obtain the following:

Item–Order (item_id, order_id, item_count, grade, comment_content, comment_date)?

Of course, the latter would allow for the possibility of NULLs, and to me it doesn't look entirely conceptually sound.

Would the former be the better option if we were to generate an additional attribute, id_review, and declare it as primary key in the Review relation?

Further details

I imagined the scenario as a one-shot thing. A person places an order which contains multiple items. Once they've received their items, they can leave a review in which they'd first have to grade (say, out of 5) the items they received in order to leave a comment. If they don't leave a comment, it's stored as an empty string to which they can't make any further changes. They can leave a single review per type of item they've received, per order (meaning even if they've ordered multiple items of the same kind in the same order – they'll still be able to leave one review).


texts: Microsoft Access Move Multiple Rows In Table1 To A Single Row In Table2

texts:
I'm new to Microsoft Access so my terminolgy and understanding are limited, please bear with me. I am interfacing a piece of lab equipment via Serial/ASTM/ODBC to MS Access for Office 365 MSO 16.0.12.x.
The equipment and ASTM transmit different parameters per line via the same field names. So I end up with 23 different rows in Access and each row has a different parameter and result. I use Table1 as a queue. I need to move the captured data from multiple rows in Table1 to multiple columns in a single row in Table2. I've created a greatly simplifed version of the database to try to understand how to do this properly.  Table1 is a simplified example of captured data. Table2 is a simulated example of what I am trying to achieve. Any help would be greatly appreciated.


texts: Why are these two HADR DMVs reporting different states?

texts:
SQL Server 2012 (11.0.5058.0) Enterprise Edition

We have 8 Availability Groups in a 2(HA)+1(DR) cluster and our monitoring DMVs are reporting results that confuse me. 6 Availability Groups are configured for HA and DR, 1 is configured for HA only, and 1 is configured for DR only.

Each of the 6 HA/DR Availability Groups have "SQLB" as a primary and "SQLA" as a secondary (synchronous) HA replica and "SQLC" as a secondary (async) replica.

On both secondaries:

SELECT dhags.group_id, dhags.synchronization_health_desc
FROM sys.dm_hadr_availability_group_states dhags

reports that all Availability Group replication sync health are NOT_HEALTHY and

select replica_id,synchronization_health_desc
from sys.dm_hadr_availability_replica_states

reports that all replicas have a sync health of HEALTHY.

The primary replica reports all Availability Groups and replicas with a sync health of HEALTHY.

While I understand that one reports on replica sync health and the other reports on AG sync health, it seems logical to me that if the more granular (AG) state was not healthy, that would affect the overall health of the broader context (replica). I cannot find MSDN documentation that describes how the health is determined at each level.

Why would the secondaries report NOT_HEALTHY for Availability Group sync health, but HEALTHY for replica sync health, and why does this differ from the primary's report?


texts: Mysql (Ver 14.14/Redhat): is a new column with default values efficient on column creation?

texts:
I want to add a new column to my production database. I would like to add a default value in order to prevent all the if not null and ... business in all my queries (both new and old).

What order of add column-time penalty will I pay? I would ASSUME and HOPE that MySql would be smart enough to not actually fill in the default values for all the old rows, and instead treat the NULL value as the default (in other words, internalize the "if not null..." into the query automatically). ... and thus there should be NO penalty over and above the normal column add.

To be explicit, here are the two different MySQL alter table commands: in 5.1, is there any difference in the time efficiency of the two commands?

alter table XYZ add column newcol int ;

and

alter table XYZ add column newcol int default 0;

I assume that the answer would be version-specific. Am doing this in AWS EC2-land. Here's the version string:

mysql  Ver 14.14 Distrib 5.1.73, for redhat-linux-gnu (x86_64) using readline 5.1


texts: MySQL General log not working on Mac OS X

texts:
I want to log all queries to the database running on my MacBook Pro (Mac OS X 10.8.1). I created a file /etc/my.cnf with the following content:

[mysqld]
general_log=1
general_log_file=/Users/wdb/mysql.log

I restarted MySQL and tried some queries on the mysql command line, but nothing gets logged. The file is not getting created. I also created the file myself afterwards to test with touch, but the file remains empty.

When I check, MySQL did read my settings:

mysql> show variables like '%general_log%';
+------------------+----------------------+
| Variable_name    | Value                |
+------------------+----------------------+
| general_log      | ON                   |
| general_log_file | /Users/wdb/mysql.log |
+------------------+----------------------+
2 rows in set (0.01 sec)

Is there anything else I need to do? I suppose any query I type on the command line should get logged, right?

I am using MySQL 5.5.24


texts: Relating two fields of one table to one field of another

texts:
I have two tables that look as such:

Table1               Table2
ID | Appname         ID  | App  | Dependency
------------         -----------------------
1  | App1            1   | 2    |  3
2  | App2            2   | 3    |  1
3  | App3            3   | 2    |  1
etc....               etc...

Basically an application can have one or more dependencies that must be complete prior to executing. In Table2, both App and Dependency relates to the ID field of Table1, but I'm having a difficult time linking these together with SQL without getting errors.

What is the correct way to join the tables so that I get results that look like this:

Dependencies:
App2: App3, App1
App3: App1

(doesn't have to be laid out like that, but if it can be, that would be great)


texts:
I'm attempting to write my own mini-rollback procedure. I have a table that tracks any updates or deletes to another table using a trigger. I am attempting to make it possible to restore one or more of these tracked changes through the use of a procedure. However, I'm receiving a syntax error with the following:

-- UNDO_CHANGES_PROCEDURE - This copies the values of the work log track table back into the relevant record in the work log table if an accidental edit or deletion is made.
DROP PROCEDURE IF EXISTS UNDO_CHANGES_PROCEDURE;

DELIMITER $$

CREATE PROCEDURE UNDO_CHANGES_PROCEDURE(ID INT(6))
BEGIN
DECLARE var_trig CHAR(8);

SET var_trig = (SELECT TRIGGER_TYPE FROM WORK_LOG_TRACK WHERE WORK_LOG_TRACK.WORK_LOG_EDIT_NUMBER = ID);

IF var_trig = 'Update' THEN
UPDATE WORK_LOG SET ENTRY_NUMBER = WORK_LOG_TRACK.ENTRY_NUMBER_FK, EMPLOYEE_ID_FK = WORK_LOG_TRACK.EMPLOYEE_ID_FK, WORK_ORDER_NUMBER_FK = WORK_LOG_TRACK.WORK_ORDER_NUMBER_FK, ENTRY_TIME = WORK_LOG_TRACK.ENTRY_TIME, WORK_DONE = WORK_LOG_TRACK.WORK_DONE WHERE WORK_LOG_TRACK.WORK_LOG_EDIT_NUMBER = ID AND WORK_LOG.ENTRY_NUMBER = WORK_LOG_TRACK.ENTRY_NUMBER_FK;
ELSE
INSERT INTO WORK_LOG(ENTRY_NUMBER, EMPLOYEE_ID_FK, WORK_ORDER_NUMBER_FK, ENTRY_TIME, WORK_DONE) VALUES (WORK_LOG_TRACK.ENTRY_NUMBER_FK, WORK_LOG_TRACK.EMPLOYEE_ID_FK, WORK_LOG_TRACK.WORK_ORDER_NUMBER_FK, WORK_LOG_TRACK.ENTRY_TIME, WORK_LOG_TRACK.WORK_DONE) WHERE WORK_LOG_TRACK.WORK_LOG_EDIT_NUMBER = ID;
END IF;
END;
$$

DELIMITER ;

The syntax error comes in in regards to my update statement, any help or suggestions would be appreciated.


texts:
I have found out the msdb.dbo.suspect_pages has 15 entries. 14 of those are last updated in 2014 and of type 7 ("Deallocated").
One entry  is of type 1 (823 or 824 error) with a count of 1 and last udpated in June 2015.

Last DBCC CheckDB was ran on Nov. 9th using a maintenance plan. The only output I have is the history of this job saying it completed 100%:

  Microsoft (R) SQL Server Execute Package Utility  Version
  10.50.6000.34 for 64-bit  Copyright (C) Microsoft Corporation 2010. All rights reserved.    Started:  7:54:37 PM  Progress: 2016-11-09
  19:54:44.66     Source: {84CA0A16-9095-4D9D-9CF8-4AF8A1144015}
  Executing query "DECLARE @Guid UNIQUEIDENTIFIER      EXECUTE
  msdb..sp...".: 100% complete  End Progress  Progress: 2016-11-09
  19:54:47.22     Source: Check Database Integrity      Executing query
  "USE [dbname]  ".: 50% complete  End Progress  Progress: 2016-11-09
  22:08:15.87     Source: Check Database Integrity      Executing query
  "DBCC CHECKDB(N'dbname')  WITH NO_INFOM...".: 100% complete  End
  Progress  DTExec: The package execution returned DTSER_SUCCESS (0).
  Started:  7:54:37 PM  Finished: 10:08:16 PM  Elapsed:  8018.92
  seconds.  The package executed successfully.  The step succeeded.

So there is not further detailed information about the possible corruption's source / table / index. I am not sure if we found any issues here or not jsut because it completed 100%...

I ran "sp_blitz" and "sp_blitzindex" but it did not return any corruption or something like that.

So far there were no suspect failures or anything that maybe caused by corruption. Since it is there for many months I'm not sure if any action must be taken. Of course there are no backup existing prior that date...

I wonder where to look at next?

Edit:

Another run of DBCC Checkdb as indicated by @Randolph returned the following:

Message
DBCC CHECKDB (myDBName) WITH all_errormsgs, no_infomsgs, data_purity executed by NT AUTHORITY\SYSTEM found 0 errors and repaired 0 errors. Elapsed time: 1 hours 23 minutes 27 seconds.  Internal database snapshot has split point LSN = 007c5657:00000372:000e and first LSN = 007c5657:00000353:0001.

Are we okay? Should I delete the entries from msdb.dbo.suspect_pages ?


texts: Need advice on optimizing database on a recurring basis

texts:
I have a search engine with approx 3.5 million rows in the searchable table. There are many suppliers that feed into the table each day, so primary keys are constantly being removed and added and the fulltext column needs to be reindexed.

So I run an "optimize table" each day otherwise the search gets slow from all of the table updates.

Is there a way to constantly be updating the table, adding and deleting millions of rows without the indexes getting messy and the fulltext search getting slower?


texts:
I was testing some stuff, and added a:

grant usage on statistics.* to cptnotsoawesome@localhost identified by 'password';

so now when I do

show grants for cptnotsoawesome@localhost;

I can see that one of them is:

Grants for cptnotsoawesome@localhost
----------------------------------
GRANT USAGE ON *.* TO 'cptnotsoawesome'@'localhost' IDENTIFIED BY PASSWORD 'somePEW-PEWstring'

Now I want to remove it as I think it's a security hazard, so i do the:

REVOKE USAGE ON *.* FROM 'cptnotsoawesome'@'localhost' IDENTIFIED BY 'password';
FLUSH PRIVILEGES;

But it still shows that USAGE grant in the grant list.

Grants for cptnotsoawesome@localhost
----------------------------------
GRANT USAGE ON *.* TO 'cptnotsoawesome'@'localhost' IDENTIFIED BY PASSWORD 'somePEW-PEWstring'

Any ideas why? What am I doing wrong?


texts: How to autopopulate MS Access fields with info from another table

texts:
I am creating a food purchasing and inventory database for a restaurant. Originally, the invoice/ordering data was stored in Excel.

I created several tables in Access: "Vendors", "Invoices", and "Orderers" (we have several orderers that each handle certain vendors). I imported the list of orderers and vendors from the Excel spreadsheet into their own Access tables.

I then imported the rest of the data from Excel into the "Invoices" table in Access. The problem is that the "Vendor" field in Excel was just the name of the vendor as a text string (rather than a number). So for each of the records in the "Invoices" table, in the "Vendor" field, I have the name of the vendor instead of the vendor ID.

What I want to do is create a new field in the Invoices table called "Vendor ID" (which is linked to the Vendors table by a relationship), and then for each of the currently existing record in the Invoices table I want to autopopulate the Vendor ID field based on the text string that is currently in the "Vendor" field. (I will then remove the Vendor field from the Invoices table, once they have all been converted, and just have a Vendor ID for each invoice item)

So for instance, right now in the Invoices table I have something that looks like this:

Invoice ID  Invoice Date    Orderer ID  Price   Vendor  File Date   Vendor ID

27  5/29/2015   3   $85.00  Blue Heron Bakery   6/1/2015
28  9/30/2014   5   $42.00  Calliope Farm   10/7/2014
29  11/2/2014   5   $15.10  Calliope Farm   11/14/2014
30  9/26/2014   2   $129.19 Cash and Carry  10/7/2014

And in my Vendors table, I have:

ID  Vendor Name
2   Blue Heron Bakery
3   Calliope Farm
4   Cash and Carry

So, for instance, for Invoice 27, since the Vendor field is currently "Blue Heron Bakery", I want to autopopulate the Vendor ID field by looking up the ID for Blue Heron Bakery in the Vendors table ... How do I do this?


texts: Backup transaction log from secondary database in alwayson AG

texts: Is it possible to do the log backup from the secondary databases in the AG?


texts: Postgresql import from SQL Server - how to auto-generate sequences

texts:
As the title states I'm switching from SQL Server to Postgresql via SQL dump. The tables and views have been migrated correctly, alas the IDENTITY columns have not been properly converted into SEQUENCEs.

In short, is there a way to auto-generate or script the generation of those sequences using the highest integer value in the ID column?

Thanks, I'm looking forward to any pointers and suggestions, and sorry if this question has already been answered elsewhere!

Julian

P.S. I'm running Postgresql 9.3.6 on Debian Wheezy


texts: SQL Merge Replication: Cannot resolve conflict

texts:
I have a SQL Merge Replication over HTTP (Replisapi.dll). I'm not very familiar with the internas of the replication conflict solving because it was always working so far.

Now I have two conflicts in the replication which I can resolve but after a few seconds the same two conflicts appear again.

The looser is the subscriber DB which wants to insert a row which already exists on Publisher. Therefore I get a Primary key Violation error.

Has anybody ever discovered such a conflict which can be resolved in the Conflict Viewer but always appears again?
How can I resolve this issue?


texts: GROUP BY clause / Aggregate function error

texts:
I am having a problem creating a Table View:

DROP VIEW IF EXISTS product_variation_stock_view;

CREATE VIEW product_variation_stock_view AS
    SELECT
        product_variations.product_id AS product_id,
        product_variations.id AS product_variation_id,
        COALESCE(SUM(stocks.quantity) - COALESCE(SUM(product_variation_order.quantity), 0), 0) AS stock,
        CASE WHEN COALESCE(SUM(stocks.quantity) - COALESCE(SUM(product_variation_order.quantity), 0), 0) > 0
            THEN true
            ELSE false
        END in_stock
    FROM product_variations

    LEFT JOIN (
        SELECT
            stocks.product_variation_id AS id,
            SUM(stocks.quantity) AS quantity
        FROM stocks
        GROUP BY
            stocks.product_variation_id
    ) AS stocks USING (id)

    LEFT JOIN (
        SELECT
            product_variation_order.product_variation_id as id,
            SUM(product_variation_order.quantity) AS quantity
        FROM product_variation_order
        GROUP BY
            product_variation_order.product_variation_id
    ) AS product_variation_order USING (id)

Error:

ERROR:  column "product_variations.product_id" must appear in the GROUP BY clause or be used in an aggregate function
LINE 3:   product_variations.product_id AS product_id,
      ^

Using PostgreSQL 10.3 on Ubuntu 18.04 via Laravel Homestead
I am running the query using the Postico OS X Client.


texts: Downsides to using SQL 2012 Upgrade as opposed to a fresh install?

texts:
I currently have SQL Server 2008 R2 which I would like to upgrade to SQL 2012. I can either un-install SQL 2008 and install a new copy of SQL Server or run the Upgrade. The upgrade is less work but I am wondering if there is a downside.

I have already tried the upgrade once. Ran into problems and had a difficult time removing both SQL 2008 & SQL 2012 so I could begin again. Upon successful upgrade, I saw that in Add/Remove programs both SQL 2008 R2 & SQL 2012 are still there afterwards. Which may make future upgrades/uninstalls complicated.

Aside from that, is there any other downsides to using the upgrade versus a fresh install?


texts:
Is it possible if I wanted to set up SQL Server 2008 R2 instance so that it only accepts SSL Connection?

I am getting different answers and based on my testing, it will accept non-SSL connection even though I have force encryption as instructed on http://msdn.microsoft.com/en-us/library/ms191192%28v=sql.105%29.aspx

The last comment from Edward Dorland on this thread (SQL Server SSL connection - What am I missing?) pointed out
"SQL server uses ssl for encryption, that doesnt need a cert at the client side."

Could anyone explain in detail?

Thanks.


texts: Publishing database changes with SSDT that include adding columns to existing table to the target database through Project

texts:
Publishing database changes with SSDT that include adding columns to the existing table in the target database(which was previously created through SSDT).Instead of creating a new SSDT database. Trying to add a column through table create a script or through alter table script.i was Encountered by the same error. Even tried through Schema Compare still the table in the target database encountering with the same error. How to alter the changes to the target database objects through SSDT publishing?

  RAISERROR (N'Rows were detected. The schema update is terminating because data loss might occur.', 16, 127) WITH NOWAIT;

I tried by checking out the

Block incremental deployment if data loss might occur.
Don't ALTER replicated objects.
Do not alter Change DATA Capture Objects.


texts: How can I represent a one-to-many relationship with three tables?

texts:
I just started using MySQL Workbench drawing an entity-relationship diagram that I previously designed in paper.

I want to represent a conceptual one-to-many relationship by means of three tables. For example, I have entity A and entity B with 1-to-N (for every A I have many Bs).

How can I do that with three tables, one of them being a "middle" table?

Like in the diagram that follows

or like in the diagram below

?


texts: How do you skip databases with Ola.Hallengren's scripts?

texts:
I have a few databases in which the frontend application manages certain maintenance tasks.  In this particular instance, I'm running the INDEX OPTIMIZE job every night and every so often it clashes with the application and fails.

The following is the error that I receive:

Msg 50000, Level 16, State 1, Server MyTestServer01, Procedure CommandExecute, Line 152
Msg 2550, The index "clidx_StatisticalInterface_dPollTime" (partition 1) on table "StatisticalInterface" cannot be reorganized because it is being reorganized by another process.

I know that there are times where the vendor would rather their builtin solution manage the maintenance.  I'm sure I could find that feature in the application and turn it off, but this question is in case that I can not.

How would I configure or edit this job to ignore a specific database or a list of databases in the same instance?


texts: Creating index on TIMESTAMP column to use with range operators

texts:
TLDR: Can I create an index that's used by the following WHERE clause:

WHERE foo_date <@ tsrange('2018-01-01', '2018-02-01')

Let's say I have a table like this:

CREATE TABLE foo
(
    foo_id INTEGER GENERATED BY DEFAULT AS IDENTITY,
    foo_date timestamp without time zone NOT NULL,
    CONSTRAINT foo_pkey PRIMARY KEY (foo_id)
);

This table contains 100,000 records with dates from 2009-01-01 to 2018-12-29. I'd like to be able to query for rows in a given date range (e.g. for rows in January 2018).

Option 1

One approach is to use the BETWEEN operator:

SELECT * FROM foo WHERE foo_date BETWEEN '2018-01-01' AND '2018-01-31';

The problem of this approach is that if foo_date occurred on 2018-01-31 after midnight, they wouldn't be included in this query. So I could change the query to BETWEEN '2018-01-01' AND '2018-02-01'. The problem then, however, is records that occur on 2018-02-01 00:00:00. These would be included, which I don't want.

Option 2

Another option, put forth by Aaron Bertrand, is to use this construct:

foo_date >= '2018-01-01' AND foo_date < '2018-02-01'

(Yes, this blog is for SQL Server, but seems to be applicable here).

While this form unequivocally gives me the results I want, it's cumbersome: I have to repeat the column name twice.

Option 3

Since Postgres gives us the range data type, I thought a clearer form might be:

foo_date <@ tsrange('2018-01-01', '2018-02-01')

So my next question is, if I use this form, can I use an index to speed up operation?

With Options 1 and 2 above, a normal b-tree index can be used:

CREATE INDEX idx_foo ON foo(foo_date);

A query using Options 1 or 2 will use the index:

EXPLAIN SELECT * FROM foo
WHERE
    foo_date >= '2018-01-01'
    AND foo_date < '2018-02-01';

gives me this query plan:

Bitmap Heap Scan on foo  (cost=21.95..592.70 rows=942 width=12)
  Recheck Cond: ((foo_date >= '2018-01-01 00:00:00'::timestamp without time zone) AND (foo_date < '2018-02-01 00:00:00'::timestamp without time zone))
  ->  Bitmap Index Scan on idx_foo  (cost=0.00..21.71 rows=942 width=0)
        Index Cond: ((foo_date >= '2018-01-01 00:00:00'::timestamp without time zone) AND (foo_date < '2018-02-01 00:00:00'::timestamp without time zone))

However, if I use Option 3, the index isn't used:

EXPLAIN SELECT * FROM foo
WHERE foo_date <@ tsrange('2018-01-01', '2018-02-01');

gives me:

Seq Scan on foo  (cost=0.00..1791.00 rows=500 width=12)
  Filter: (foo_date <@ '["2018-01-01 00:00:00","2018-02-01 00:00:00")'::tsrange)

If I try to create a gist index, I initially get an error message.

ERROR:  data type timestamp without time zone has no default operator class for access method "gist"
HINT:  You must specify an operator class for the index or define a default operator class for the data type.
SQL state: 42704

After adding the btree_gist extension, I can create the index:

CREATE INDEX idx_foo ON foo USING gist (foo_date)

However, using the @> or <@ still doesn't make use of the index.

Is there something I'm missing? Or is it just not feasible to create an index that can be used by this construct?


texts: Update values from one table to another where key values are not unique

texts:
I've been trying to figure this out for hours and can't seem to get it. If it can be done, any help would be greatly appreciated! I hope I'm clear with what I'm asking :-)

Consider the following:

Table A consists of lastname (null), firstname(null), merchant, amount, transdate, reference; with only unique field value being reference. The merchant, amounts and transdate can have similar duplicated values.

example:

Row 1:  {Null},{Null},"BLANK-ROSTER NUM","34.30","2018-01-01","12345"
Row 2:  {Null},{Null},"BLANK-ROSTER NUM","34.30","2018-01-01","77777"
Row 3:  {Null},{Null},"BLANK-ROSTER NUM","16.07","2018-12-24","99999"
Row 4:  {Null},{Null},"BLANK-ROSTER NUM","16.07","2018-12-24","44444"
Row 5:  {Null},{Null},"BLANK-ROSTER NUM","34.30","2018-01-01","66666"

The other table has the lastname and firstname values I want to assign to each line, similar merchant,amount and transdate values throughout, but no reference field.

example:

Row 1:  SMITH,JOHN,"BLANK-ROSTER NUM","34.30","2018-01-01"
Row 2:  JONES,JAMES,"BLANK-ROSTER NUM","34.30","2018-01-01"
Row 3:  MILLER,STEVE,"BLANK-ROSTER NUM","16.07","2018-12-24"
Row 4:  WILSON,JACK,"BLANK-ROSTER NUM","34.30","2018-01-01"
Row 5:  DAVIS,BEN,"BLANK-ROSTER NUM","16.07","2018-12-24"

Is there a way to get names updated into the similar rows in TABLE A? It doesn't matter which goes to which so long as one name doesn't populate all similar rows in TABLE A (which is what happens to me). Any row with same merchant, amount, and date can update to any row in Table A that has same merchant, amount, and date. Avoiding the duplication of data is my issue.

Is it possible to get in TABLE A the names to distinctly assign to a row?

UPDATED TABLE A after assigning names from rows with same merchant, amount, and date distinctly:

Row 1:  "SMITH", "JOHN","BLANK-ROSTER NUM","34.30","2018-01-01","12345"
Row 2:  "JONES","JAMES","BLANK-ROSTER NUM","34.30","2018-01-01","77777"
Row 3:  "MILLER","STEVE","BLANK-ROSTER NUM","16.07","2018-12-24","99999"
Row 4:  "DAVIS","BEN","BLANK-ROSTER NUM","16.07","2018-12-24","44444"
Row 5:  "WILSON","JACK","BLANK-ROSTER NUM","34.30","2018-01-01","66666"

//////////Edited to include Table Def using SQL Server 2012

Table A:

 INSERT INTO [Recon2018].[dbo].[RECONReport]
       ([Employee - Last]
       ,[Employee - First]
       ,[MI]
       ,[Posted Amount]
       ,[Merchant]
       ,[Reference]
       ,[transdate])
 VALUES
       (<Employee - Last, nvarchar(50),>
       ,<Employee - First, nvarchar(255),>
       ,<MI, nvarchar(255),>
       ,<Posted Amount, decimal(18,2),>
       ,<Merchant, nvarchar(50),>
       ,<Reference, nvarchar(50),>
       ,<transdate, datetime2(0),>)

TABLE B:

 INSERT INTO [Recon2018].[dbo].[Summary_Final]
       ([Employee Last Name]
       ,[Employee First Name]
       ,[Employee Middle Initial]
       ,[Merchant]
       ,[CLIN Rate (Fee)]
       ,[Fee Transaction Date])
 VALUES
       (<Employee Last Name, nvarchar(max),>
       ,<Employee First Name, nvarchar(max),>
       ,<Employee Middle Initial, nvarchar(255),>
       ,<Merchant, nvarchar(max),>
       ,<CLIN Rate (Fee), float,>
       ,<Fee Transaction Date, datetime2(0),>)


texts: Recursively list dependencies / references of a stored procedure

texts:
I'm trying to import a subset of our production dump with impdp for our local development database running on Oracle XE.
To find out what other objects I have to import for a particular stored procedure to work, I'm recursively querying DBA_DEPENDENCIES with the following example from Recursively list concents of Oracle's DBA_DEPENDENCIES view:
SELECT TYPE, OWNER, NAME,
       REFERENCED_TYPE, REFERENCED_OWNER, REFERENCED_NAME
FROM ALL_DEPENDENCIES
START WITH NAME='EXAMPLE_PROCEDURE' AND OWNER='EXAMPLE_OWNER'
CONNECT BY NOCYCLE
  PRIOR REFERENCED_NAME = NAME;

Unfortunetly, while this does show me tables needed somehow by the procedure, it doesn't include the table's triggers and the trigger's dependencies, only returning me a chain like this:
PROCEDURE -> FUNCTION -> TABLE [missing: -> TRIGGER -> FUNCTION -> TABLE -> ...]
This seems to be the case because DBA_DEPENDENCIES does include triggers and their referenced objects, but does not include triggers as referenced objects from a table. So I rewrote the query like this:
SELECT * FROM (
  SELECT TYPE, OWNER, NAME,
    REFERENCED_TYPE, REFERENCED_OWNER, REFERENCED_NAME
  FROM ALL_DEPENDENCIES
  UNION
  SELECT BASE_OBJECT_TYPE as TYPE, OWNER, TABLE_NAME as NAME,
    'TRIGGER' as REFERENCED_TYPE, TABLE_OWNER as REFERENCED_OWNER, TRIGGER_NAME as REFERENCED_NAME
  FROM DBA_TRIGGERS
)
START WITH NAME='EXAMPLE_PROCEDURE' AND OWNER='EXAMPLE_OWNER'
CONNECT BY NOCYCLE
  PRIOR REFERENCED_NAME = NAME;

Now I get the referenced triggers of referenced tables in the result set, but not the references of the referenced triggers, with a chain looking like this:
PROCEDURE -> FUNCTION -> TABLE -> TRIGGER [missing: -> FUNCTION -> TABLE -> ...]
When querying DBA_DEPENDENCIES manually for referenced triggers, I can see that they actually have references.
Why aren't they returned with the CONNECT BY query, and how would I need to rewrite the query to include those too?


texts: Create table permissions issues with SQL Server

texts:
I have a database _Repository and a Login COMPANY\ANALYST_USERS which contains a handful of employees, and I am using Microsoft SQL Server 2017.

The database has some core tables in it that I want people within the Login to be able to insert rows to, update and delete rows from, but definitely not be able to drop the tables themselves. But I also want users within the Login to be able to create their own tables/views within that database and be able to drop these items they've created. Is there a permissions setup that would allow this?

In Login Properties for COMPANY\ANALYST_USERS, under User Mapping, I added them as members to the db_datareader and db_datawriter roles for the _Repository database but ensured db_ddladmin was unselected. This however stopped them from being able to create tables/views so I then granted that Login CREATE TABLE and CREATE VIEW permissions for that database. Despite this, they still don't have permissions to create tables or views.


texts:
I have six error logs plus the current.  They are taking up 10G of disk space on my hard drive on my VPS server.  Thats about 25% of the server space.  The largest file is 6G and growing.

How can I remove the error logs as I don't need them and reduce the size in which the archive files grow to before recycling.

Thanks,


texts: I am trying to delete an aliased user from a sql server 2000 database that was restored to sql server 2008 r2. Originally, I was attempting to convert the aliased user to a regular user, but it does not appear that I can do that, so now I am attempting to delete the user without deleting the login.


texts: How to use to_regclass with uppercase table names?

texts:
I am using to_regclass() to check if a table exists (from here). This works for lowercase table names, but for table names with capital letters it always returns NULL, even when the table exists:

CREATE TABLE "asdf" (s text);
CREATE TABLE "FooBar" (s text);

SELECT to_regclass('asdf'); --     -> "asdf"
SELECT to_regclass('FooBar'); --   -> NULL
SELECT to_regclass('foobar'); --   -> NULL

Fiddle


texts:
I have to update several objects in many instances. In order to achieve that I am using DBMS_METADATA to create the DDL statements.

So far I have the DDL statements and I save them in a TABLE.
I would like to save said DDL statements in a .sql file for each object.

I know I can do this with C#, but so far I have been able to achieve everything I needed to do with PL/SQL. Is there a way to create the .sql files with PL/SQL?


texts:
I am trying to backup a db on ServerA via ServerB.
I have:
DECLARE @SQL NVARCHAR(100)
SET @SQL = 'BACKUP DATABASE DBname to disk=''\\ServerB\E$\folder\DBname.bak'''
EXECUTE ServerA.master.dbo.sp_executesql @SQL

I execute this on ServerB but I get error:

Msg 3013, Level 16, State 1, Line 9 BACKUP DATABASE is terminating
abnormally. Msg 3201, Level 16, State 1, Line 9 Cannot open backup
device '\ServerB\E$\folder\DBname.bak'. Operating system error
5(Access is denied.).

I have given NT SERVICE\MSSQLSERVER full control to the folder but that didn't work.
Is what I am trying to do possible?
What am I doing wrong?
EDIT
I have also tried:
EXEC ServerA.master.dbo.sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO

EXEC ServerA.master.dbo.sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE
GO

EXEC ServerA.master.dbo.XP_CMDSHELL 'net use G: \\ServerB\E$\folder /user:user password'
EXEC ServerA.master.dbo.XP_CMDSHELL 'Dir G:'
GO

DECLARE @SQL NVARCHAR(100)
SET @SQL = 'BACKUP DATABASE DBname to disk=''G:/DBname.bak'' WITH INIT, FORMAT, COPY_ONLY'
EXECUTE ServerA.master.dbo.sp_executesql @SQL
GO

EXEC ServerA.master.dbo.XP_CMDSHELL 'net use G: /delete'
GO

EXEC ServerA.master.dbo.sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO

EXEC ServerA.master.dbo.sp_configure 'xp_cmdshell', 0
GO
RECONFIGURE
GO

It reports it has enabled xp_cmdshell etc but when I run the mapping part it errors saying it's off.
If I enable xp_cmdshell directly on ServerA the mapping part works via ServerB??


texts: Trace flag is running, but not message when deadlock happens

texts:
Trace flags are used to temporarily set specific server
  characteristics or to switch off a particular behavior

More information on the trace flags are here

DBCC TRACESTATUS

this above, is the command that shows all the trace flags that are currently running on my system.

I have 2 questions:

1) is there any other way to find the list of trace flags that are currently running, other than DBCC TRACESTATUS?

2) It can be seen on the picture above the trace flag 1222 is active but I am not receiving any email when a deadlock occurs. What else needs to be done?


texts: Deleting old files in /var/lib/postgresql/9.x/main after changing postgresql data_directory

texts:
Very recently, I changed the data_directory of posgresql (9.3.10) to point to a new data disk I recently added to my VM (Ubuntu 14.04 OS). All the old data has been transferred to this new disk as well.

Prior to this shift, the data_directory was residing in the disk that had the OS installed too. This was a digestible arrangement initially, but ultimately the data_directory almost came to fill the entire 30GB space available. Thus I made the shift.

My question is: can I delete the contents of the old var/lib/postgresql/9.x/main/ folder now that data_directory is entirely pointing to a new location?

The old files are still intact, taking a ton of space, and the only reason I haven't deleted them is because other elements in the postgresql config (or some daemon scripts) might be referring to this folder structure somehow. I'm not sure. Someone with experience can perhaps help out here.


texts:
In SqlServer 2000, there used to be a table called sysindex which had a column rows.  This table is now:
-sys.indexes
-sys.partitions
-sys.allocation_units
-sys.dm_db_partition_stats

(see: http://msdn.microsoft.com/en-us/library/ms187997.aspx) but none of these tables contain the row columns?  So how do you find out how many rows there are for an index?

Thanks.


texts: How to get non-overlapping distinct intervals from a PostgreSQL table?


texts: Average, between specific date/time, of the difference between current field and previous field

texts:
I'm trying to calculate the average, between a specific date/time, of the difference between the current field and the previous field. At present I've got a query which displays this but I'm unsure how I can now filter this such that I can get the average of the 'Diff' column between a specific date/time. (Apologies in advance for my sql skill, or lack of.)

SELECT
      S.Pair,
      S.Date,
      S.Time,
      S.Bid,
      S.Time,
      ABS(S.Bid - T.Bid) as 'Diff'
FROM
    dbo.Data as S
CROSS APPLY
           (
            SELECT TOP 1 T.Bid
            FROM
                dbo.Data as T
            WHERE
                 T.Id > S.Id
           ) as T

Any pointers would be appreciated.

EDIT:

http://sqlfiddle.com/#!3/d4910/1


texts:
Mysql shows the following value in the slow query log

# User@Host: user[user] @  [127.0.0.1]

I'm assuming one if the "user" entries is a proxied login. An admittedly very quick glance of the code has not come up with how the user_host value is set.

Is this the case, and if so which one?


texts: How to find admin user of pluggable database in Oracle 12c up

texts:
I'm creating a script and I need to know what is the admin user of pluggable database. I don't find nothing about it in documentation.

Other question is, what happen if I drop a admin user of the pluggable database?  is there some problem?


texts:
I have setup an Oracle 11g Database and have a question about exporting and importing a database.

I am using Enterprise Manager and have browsed to the following: Data Movement->Export to Export Files. Down the bottom of the screen, I need to supply Host Credentials. When trying to use the username SYS, I see that the error message says to logout and login using a different role.

What role should I use? Should I create a specific role for this task, and if so, what priviledges should the role have?


texts: Function To Perform Calculations Based Off Variable

texts:
I saw an answer by @ypercubeᵀᴹ that gave a great answer on how to calculate what appeared to be a quantity ordered by 3 and give whole number answers. Which that got me thinking, how would you handle a situation if there was a variable number of times to split?

Meaning this was the OP where you would always split by 3 (Original Poster

Create Table #Orders
(
    id int IDENTITY(1,1) PRIMARY KEY NOT NULL
    ,partid varchar(100) NOT NULL
    ,qtyordered int DEFAULT '0'
    ,orderedby varchar(100) NOT NULL
    ,ordereddate date DEFAULT GETDATE()
) ;

Insert Into #Orders (partid, qtyordered, orderedby) VALUES
('SS100', 10, 'James'), ('RR200', 5, 'Bob'), ('NN300', 3, 'Jake'), ('OO400', 5, 'Blue') ;

SELECT
   partid,
   qtyordered,
   [First],
   [Second],
   [Third]
FROM
    #Orders
  CROSS APPLY
    ( SELECT [Third]  = (qtyordered)                      / 3 )  AS q3
  CROSS APPLY
    ( SELECT [Second] = (qtyordered - [Third])            / 2 )  AS q2
  CROSS APPLY
    ( SELECT [First]  = (qtyordered - [Third] - [Second]) / 1 )  AS q1;

dbfiddle here

However, what if instead of always splitting by 3, you had an int variable that stated how many times to split, say same DDL but instead of splitting by 3 you use

Declare @TTS int = 5

And now you split each scenario 5 ways instead of 3.  Basically a re-usable function that can "on-the-fly" split based off a variable?


texts: Does a huge key length value for a mulibyte column affect the index performance?

texts:
When I look at the EXPLAIN results, the key len value is always calculated based on the actual column length multiplied on the maximum number of bytes for the chosen encoding. Say, for a varchar(64) using utf8 encoding the key len is 192.
Does this number affect performance in any way and should I reduce it when possible? I mean, does it make MySQL to reserve some space somewhere that remain unused, or it's just a maximum possible value while the used space is based on the exact data length?
So the actual question is: if I have a column that contains only Latin letters and numbers, should I change its encoding to latin1 from utf8 in regard of the space occupied by the index/overall index performance?


texts: Select only neighbour rows who pass a where-test

texts:
I want to apply a selection process to my data, that will not include rows which should be accepted by my WHERE clause but who follow after a row which fails the WHERE.

Example. I have a list of gps locations and want to put them in a tour entity, if they surpass a speed limit.

SELECT _id, speed FROM location;

results in (sqlite3 syntax):

1|0
2|0
3|3
4|3
5|3
6|0
7|0
8|0
9|3
10|3
11|3
12|0

So, if I SELECT for WHERE speed > 2 now, I will get 3,4,5 and also 9,10,11. These both clearly should be separated tours. Can I formulate my SQL in a way that it only results in 3,4,5 because the following entity will result in a failed WHERE speed > 2 test?

First I thought, that I can simply return all these entities, but now I think there will be 2 problems with that. First there might be a lot of these, when this SELECT is called. And second there might be deleted rows. So instead of the 11 row there might be a lot higher number as _id which still should belong to the same tour, because there are no rows between 10 and this row and both pass the WHERE speed > 2. That there are no rows failing the WHERE between 10 and the next row is totally unknown the Application waiting for the result of this SELECT.

It is on purpose that I don't ask for a database specific answer. If you know an easy solution for database X or Y there is no problem adding it as a solution. The real question is, how to solve it with standard SQL, though. I think every well known database should have some way of solving any task out of the standard solvable task set.


texts: Denying direct remote access to SQL Server

texts:
I am trying to stop our valid database users accessing our SQL Server remotely via applications other than our system application (e.g., MS Excel). These accesses need to be prevented because they cause problem with our access audit.

Among not very reliable solutions, I am interested in trying the Logon Trigger method.

Remus Rusanu provided a sample Trigger here https://stackoverflow.com/a/2867794/1957134

CREATE TRIGGER application_limit_trigger
ON ALL SERVER WITH EXECUTE AS '...'
FOR LOGON
AS
BEGIN
IF EXISTS (SELECT *
   FROM sys.dm_exec_sessions
   WHERE session_id = @@SPID
   AND program_name IN (N'Bad Program', N'Worse Program', N'Unmentionable')
    ROLLBACK;
END;

The major issue with this method is unpredictable remote application Names.

My question:
What may happen if instead of denying the access from specific application names, we include the acceptable application names and deny any other application name? Can this approach cause unexpected login issues even if we have a precise allowed application list?

Yes, someone may fake our valid app names and get connected, if he guesses the names. But isn't this exclusive method more reliable?


texts: Clustering a heap by date in stead of unique composite key

texts:
I've been working on improving the performance of a large reporting database.
This database is 2TB in size, and one of the larger tables is a heap with three non-clustered indexes on it.

The non-clustered indexes each deal with a combination of Case_ID's with relevant data.
However the case id's are not at all unique, as each case entry is stored in the same heap with the Case_ID.
Inquiries at the business side of things have led me to the fact that a combination of Case_ID, Line_Number and Document_ID is unique.

However, nearly all of the queries are claused by dates (using a datetime column named DATE).
Usually by WHERE DATE = 'xxxx-xx-xx 00:00:00.000, or WHERE DATE > 'xxxx-xx-xx 00:00:00.000'. And the Line_Number and Document_ID values are in most cases not even included in the reports.

As such, I created a clustered index on the heap (on DEV), keyed on the datetime column.
Performance of the reports has increased by a decent amount, reducing query times from 5 minutes to 3 minutes for most of my test reports, and I haven't seen any negative impact on the reports not claused by date.

I am however concerned that I'm looking too far as I understand that non unique clustered indexes are rarely the solution. Is what I'm doing a valid approach? Or should I simply index on the unique composite key, and create a covering index on the datetime column?


texts: Delete data from multiple related tables

texts:
I am using a PostgreSQL server. Suppose I have 200 values in a master table that need to be deleted. There are 9 other tables referencing this master table. The other 9 tables not only reference the master table, but reference other tables also. The 200 values in the master table are spread out in other 9 tables.
I want to find out if any of the 200 values are present in all the 10 tables and delete them.
Suppose the master table has id = 1 and this record needs to be deleted. Apart from deleting the value in this table, I need to check the remaining 9 tables whether this value is present or not and delete those records also.
For example, if I need to delete the second table, the second table references 3 tables. One is the master table and other two are different tables. That is why I am in a fix.
How can I do this?
Some of the tables have been declared with on delete no action. I am not in a position to change that. Need to find another way without doing that.
Also, when I use foreign keys and delete the other tables, the other tables have been referenced by some other tables also. It is like a chain.


texts: ORA-01017 Invalid Username/Password in SQL Developer

texts:
I installed Oracle 18c XE and SQL Developer 18.4, both are the latest.

Logged in with SYSTEM and created a user vbk in SQL Developer.

I see the user running select * from dba_users, but when I try to connect in SQL Developer, it says

ORA-01017 Invalid Username/Password

I tried to reset the password with SYSTEM user:

ALTER USER vbk IDENTIFIED BY xxx;

and I get

ORA-01918: user 'VBK' does not exist

I see that in the message the user name is upper case, even though the user was created with lower case.

How to connect with the user?


texts: Questions and answers database design for Core Data

texts:
I am designing database for a question and answer projects , and I am stuck. I do not know how to do it.

Here is information:

There will be many users
There will be many questions
Each question has 3 options (answers) and user chooses one.
Each question has 3 time constraints to answer. (Time constraints will
change as questions.)
Each time constraint has different point. (Also points will change as
questions.) (For example, question 1 has 3, 10 , 40 seconds time constraints. If user answers in 3 seconds, he/she will take 30 points. If users answers in 10 seconds, he/she will take 10 points etc.)
Only one answer is correct, other 2 are wrong.
There will be score table for users.
User will answer once a question.

My current design can be seen here: https://s32.postimg.org/lolr4vqvp/Screen_Shot_2016_06_29_at_17_14_01.png

I want to manage everything with Core data after I want to collect all data in sqlite database. Could you help me to create database design?


texts: SQL Server stored procedure producing data related to months between two dates

texts:
I'm stuck on the following problem.

I have a start range date and end range date calculated by a query and I have three columns also produced by another query, the outcome of the last query is:

PRJ_ID | START_DATE | END_DATE

I want to produce an output using a stored procedure that considering all the months between start range date and end range date produces an output like this:

PRJ_ID | MONTHS1-YEAR | MONTHS2-YEAR | ... | MONTHSN-YEAR
XXXX   | YES     | YES     | ... | NO

The yes or no is obtained checking if the current month is in between the start_date and end_date of the project (table obtained above).

I'm able to produce all the months between two dates with this code:

DECLARE @StartDate  DATETIME,
        @EndDate    DATETIME;

SELECT   @StartDate = '20110501'
        ,@EndDate   = '20110801';

SELECT
    DATENAME(MONTH, DATEADD(MONTH, x.number, @StartDate)) AS MonthName
FROM
    master.dbo.spt_values x
WHERE
    x.type = 'P'
    AND x.number <= DATEDIFF(MONTH, @StartDate, @EndDate);

But I'm stuck on producing the last result.

Here is my try, but I'm getting an error on @prjdates format that is not datetime:

DECLARE @StartDate  DATETIME,
        @EndDate    DATETIME,
        @minStart    DATETIME,
        @orig_estimated_MaxEnd    DATETIME,
        @maxEnd    DATETIME,
        @curr_planned_MaxEnd         DATETIME;

SELECT   @StartDate = '20110501'
        ,@EndDate   = '20150801';

SET @minStart = (SELECT MIN(start_date)
                 FROM [PPM].[dbo].[prj_details]
                 WHERE start_date IS NOT NULL
                   AND start_date <> '1900-01-01') ;

SET @orig_estimated_MaxEnd = (SELECT MAX( DATEADD(day,orig_estimated_duration*7, start_date)) As FinishDate
                              FROM [PPM].[dbo].[prj_details]
                              WHERE start_date IS NOT NULL);

SET @curr_planned_MaxEnd = (SELECT MAX(curr_planned_LF)
                            FROM [PPM].[dbo].[status_and_updates]
                            WHERE curr_planned_LF IS NOT NULL);

SET @maxEnd = (SELECT
                   CASE
                      WHEN @orig_estimated_MaxEnd > @curr_planned_MaxEnd
                        THEN @orig_estimated_MaxEnd
                      ELSE @curr_planned_MaxEnd
                   END AS MinValue);

print @minStart;
print @orig_estimated_MaxEnd;
print @curr_planned_MaxEnd;
print @maxEnd;

DECLARE @prjDates VARCHAR(MAX);

DECLARE @prjDatesTable TABLE
(
    [prjDate] DATETIME
);

DECLARE  @from datetime,
@to datetime;

SET @from = '20110501';
SET @to = '20150501';

;WITH Numbers (Number) AS
(
    SELECT
        row_number() over (order by object_id)
    FROM
        sys.all_objects)
INSERT INTO @prjDatesTable
    SELECT
        dateadd(month, number, @from) AS months
    FROM Numbers
    WHERE number <= datediff(month, @from, @to);

SELECT @prjDates = COALESCE(@prjDates + '],[', '') +
      (LEFT(CONVERT(VARCHAR, [prjDate], 101),6) + RIGHT(YEAR([prjDate]),2))
FROM @prjDatesTable ORDER BY [prjDate] DESC;

DECLARE @projects TABLE(PRJ_ID varchar(200),  START_DATE DATEtime, END_DATE datetime)
INSERT INTO @projects
    SELECT p.id,start_date,curr_planned_LF as END_DATE
    from [PPM].[dbo].[prj_details]as p
    left join [PPM].[dbo].[status_and_updates] as s
    on p.id=s.id
    where curr_planned_LF is not null
    and start_date IS NOT NULL;

--select * from @projects;
select *
from
    @projects
pivot
(
  count(prj_id)
  for start_date in  ([' + @prjDates + '])
) piv;


texts: How can I conditionally summarize multiple rows in Microsoft Access?

texts:
I have a table of data, in Microsoft Access 2003, listing multiple rows for each person.

I want to conditionally summarize a subset of these rows for each person, for instance, if entry for columnA = "Hats".

I've made summary tables for each (with the conditional checking) and I want to UNION the master table and the summary tables together, however each table has a different number of columns. Am I going to have to redesign my summary tables so they match the columns in the master, or is there a clever way of doing this?

I've tried to join them first (do I even need to do this?), and also to UNION CORRESPONDING them together, but MS Access doesn't understand that command it seems.

How should I do this?

Edit, here is a mockup of my master table data:

ResID| Name|  Baselocation|  Destination| CustomerID  |   Project ID    p1    p2    p3
---------------------------------------------------------------------------------
001  |  Bob|  London      |  D-London   | Company     |   "BaseWork"    X%    y%    z%
                             N/A        | myOwnCompany|   "Overhead"    x%    y%    z%
                             D-NotLondon| Company     |    Project      x%    y%    z%
                             D-London   | Company     |    Project2     x%    y%    z%


texts: Is there a way to make a DDL trigger which fires on ALTER of any object? So far the only way I see to do this is enumerate each object type (e.g. ALTER_TABLE, ALTER_PROCEDURE, etc) when what I'd really like is something like ALTER_ANY.


texts: Setting up MySQL circular replication in existing replication topology?

texts:
I have the following MySQL replication topology set up for fault tolerance/disaster recovery with master server A and slaves B and C:

A replicates to B

A replicates to C

Both B and C are fully up to date with everything from A and have nothing in addition to that.  i.e. there is nothing in B and C that is not in A.

I would like to introduce circular replication such that:

A replicates to B
B replicates to A

    A replicates to C

Changes made on B would replicate back to A. This would allow me to get more use out of a secondary server by spreading the load over two primary servers.

Primary use of each individual database on the servers would be limited to a specific server.  e.g. Users of database bookingsystem always access via server B, users of database enquirysystem always use server A. There is no danger of having to deal with changes to the same database in two different locations. The replication is purely for fault tolerance should there be a problem with either server.

Can I simply set server A to slave from server B and have this working straight away, or is there further preparatory, or subsequent work needed?


texts: sp_blitzFirst Does Not Output to DB When Run as SQL Job

texts:
I'm an accidental DBA here,

I am attempting to create a SQL Agent Job that will capture the data for sp_blitzFirst. My goal is to start using the PowerBI dashboard to help show our pain points to management.

When I run the SQL code below, it outputs the data to my tables, but when I run it as a SQL Job, it does nothing.  The job succeeds without any errors codes.

EXEC sp_BlitzFirst
  @OutputDatabaseName = 'DBATOOLS',
  @OutputSchemaName = 'dbo',
  @OutputTableName = 'BlitzFirst',
  @OutputTableNameFileStats = 'BlitzFirst_FileStats',
  @OutputTableNamePerfmonStats = 'BlitzFirst_PerfmonStats',
  @OutputTableNameWaitStats = 'BlitzFirst_WaitStats',
  @OutputTableNameBlitzCache = 'BlitzCache',
  @OutputTableNameBlitzWho = 'BlitzWho';

Here is the output from the task:

Message
Executed as user: NT SERVICE\SQLSERVERAGENT. Setting up configuration variables [SQLSTATE 01000] (Message 50000)
Now starting diagnostic analysis [SQLSTATE 01000] (Message 50000)
Checking for Read intent databases to exclude [SQLSTATE 01000] (Message 50000)
Capturing first pass of wait stats, perfmon counters, file stats [SQLSTATE 01000] (Message 50000)
Beginning investigatory queries [SQLSTATE 01000] (Message 50000)
Warning: The join order has been enforced because a local join hint is used. [SQLSTATE 01000] (Message 8625)
Warning: The join order has been enforced because a local join hint is used. [SQLSTATE 01000] (Message 8625)
Finished running investigatory queries [SQLSTATE 01000] (Message 50000)  Waiting to match @Seconds parameter [SQLSTATE 01000] (Message 50000)
Capturing second pass of wait stats, perfmon counters, file stats [SQLSTATE 01000] (Message 50000)
Analyzing changes between first and second passes of DMVs [SQLSTATE 01000] (Message 50000)
Analysis finished, outputting results [SQLSTATE 01000] (Message 50000).
The step succeeded.

Any help in the right direction would be appreciated.

Thanks!


texts: Can't retrieve data of countries and regions

texts:
Goal:
Improve my sql ability by doing some coding and reviewing existing code.

Problem:
I have spent lots of hour to find a solution based of this question
"3c. Some countries have populations more than three times that of any of their neighbours (in the same region). Give the countries and regions. "

I tried finding different solution but unfortunately I can't solve this problem :(

The link can be found "http://sqlzoo.net/1a.htm"

Again, the purpose is to improve my ability in SQL!

// Fullmetalboy


texts: How to measure improvement after splitting tablespaces into multiple disk volumes? (Oracle 10g AWR)

texts:
I have a recently released 10 000 user analytical application that is suffering from performance issues because of excessive amount of transactions. Apart from re writing many of the SQLs in the application for better performance we are also on top down approach where we are adding more volumes and tweeks to memory etc. An Oracle specialist redesigned our sever from single tablespace with 2 volumes to 2 tablespaces with 4 volumes where the indexes will have their own volume and tablespace. Each volume is a separate disk array so they do not compete for IO.

We have executed this change in development env and we are about to move into acceptance but before paying for the change the business would like to see measurable gains.

Where in the AWR report can I make comparisons to measure such data on the before and after the change is executed?


texts: Get Updated_At for any change in row on Sybase?

texts:
I am working with a Sybase SQL Anywhere table that has horrible tracking of changes. I have a windows service that queries multiple tables every 5 minutes looking for changes, but one particular table doesn't have any timestamps. In addition to that, when I added a new record, it was given an ID in the middle for some reason. There are 15,000 records and the new ID was 6775, so simply asking for records that have an ID greater than the last won't work.

I know in MySQL I could alter the table with something like:

ALTER TABLE `customer` ADD `updated_at` DATETIME on update CURRENT_TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP

Which would auto update that value any time anything was changed. That doesn't work with Sybase, so my next thought was to create a second small table:

CREATE TABLE customers_getting_updated(
   customer_id int,
   updated_at DATETIME,
   PRIMARY KEY(patient_id)
)

and then set a trigger to create / update records in that table. There are currently 150 columns in the table. I tried creating a trigger like this:

create TRIGGER "customer_record_updated" AFTER INSERT ON customer
for each row begin
    INSERT INTO customers_getting_updated (patient_id, updated_at)
    VALUES(:NEW.customer_id, NOW());
end;

I am running this on a dual processor 6 core with 64GB memory on a SSD and the query has taken 15 minutes and still hasn't finished.

Assuming this will finish eventually (I saw someone say one took them 70 minutes????), since I didn't get an error up front, is there a better way since most computers that need to run this will have substantially less hardware power.

FYI - I tried doing a AFTER INSERT OR UPDATE but got an error on the OR, so I assume I will have to write separate triggers.


texts:
I am doing a batch process which fetches rows from table 50000 times.

The query is:

SELECT * FROM <table name>  WHERE <condition; this changes for each iteration(50000)>

This batch process is done in java and uses jdbc connection.

The error shown is:

com.ibm.db2.jcc.a.SqlException: DB2 SQL error: SQLCODE: -805, SQLSTATE: 51002

This error happens at certain iteration (between 20000 to 30000)

I logged the query which caused the error and took this query and executed it using both external agent and java/jdbc; it worked fine.

what would be the problem?


texts: Get username and/or IP address responsible for a query

texts:
I use the following query to find performance improvements in queries:

SELECT TOP 20 SUBSTRING(qt.text, (qs.statement_start_offset/2)+1,
        ((CASE qs.statement_end_offset
          WHEN -1 THEN DATALENGTH(qt.text)
         ELSE qs.statement_end_offset
         END - qs.statement_start_offset)/2)+1),
  qs.execution_count,
  qs.total_logical_reads, qs.last_logical_reads, qs.min_logical_reads,
  qs.max_logical_reads, qs.total_physical_reads, qs.last_physical_reads,
  qs.min_physical_reads, qs.max_physical_reads,
  qs.total_elapsed_time / 1000000 As total_elapsed_time,
  qs.last_elapsed_time / 1000000 As last_elapsed_time,
  qs.min_elapsed_time / 1000000 As min_elapsed_time,
  qs.max_elapsed_time / 1000000 As max_elapsed_time,
  qs.last_execution_time, qs.creation_time, qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
WHERE qt.encrypted=0
AND last_execution_time >= DATEADD (MINUTE , -5 , CURRENT_TIMESTAMP )
ORDER BY qs.total_logical_reads DESC
--ORDER BY qs.total_physical_reads DESC

The issue is that I've found a query that appears to be thrown repeatedly from time to time and requires enhancement, but I'm not able to determine where it comes from (could be a user or a program, but in any case should have a login or username). How can I get this user or track this query so I can apply improvements to it?


texts: Oracle service names with a minus sign (?)

texts:
Are there any reasons not to use minus signs/dashes/hyphens in Oracle service names?
DBMS_SERVICE, tnsping and sqlplus seem to be fine, but the documentation says no.

We are switching from Oracle SIDs to service names. Apparently, they can be longer than SIDs (64 chars instead of 8), and can contain a domain name (hence dots . and minus signs -). The documentation of DBMS_SERVICE says that service_name is limited to 64 characters, and network_name is limited to the NET service_name character set
which is restricted to

[a...z] [A...Z] [0...9] _

So, what about dots and hyphens? Are they ok or not?

http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_serv.htm#ARPLS68018
http://docs.oracle.com/cd/E11882_01/network.112/e10835/syntax.htm#NETRF175


texts: INSERT gives Error Code: 1366. Incorrect string value: '\xF0\x9F\x98\x80' for column

texts:
I am facing issues with my table structure :

My_Table_Name1

CREATE TABLE `My_Table_Name1` (
  `twitter_id_str` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `twitter_screen_name` varchar(512) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  UNIQUE KEY `twitter_id_str` (`twitter_id_str`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

For database I also have same charset and collation:

CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

I'm trying to insert an emoji into this table:

insert into My_Table_Name1 values("2","")

However, I get an error:

  Error Code: 1366. Incorrect string value: '\xF0\x9F\x98\x80' for column 'twitter_screen_name' at row 1 0.00027 sec

How to solve this?

Thanks in advance.


texts:
I have two tables:

create table product
(
product_id number primary key,
item_name varchar2(128 byte) not null,
pricenumber not null,
available_from date not null
);

create table orders
(
order_id number primary key,
product_id number not null,
amount number not null,
order_price number not null,
date_dispatched date not null,
foreign key (product_id) references product(product_id)
);

I need a query to find all products that have less than 20 orders in the last year, excluding products that have been available for less than 30 days.

Here is what I suggest. Any comments and corrections are appreciated.

First solution with EXISTS:
Select product_id from products
where available_from > ADD_MONTHS(systimestamp,-1)
AND NOT EXISTS (
Select product_id
from orders
where dispatch_date > ADD_MONTHS(systimestamp,-12)
group by product_id
having count(*) > 10
)

Second solution with JOIN:
Select p.product_id, count(*)
from products p left outer join orders o on p.product_id = o.product_id
where p.available_from < ADD_MONTHS(systimestamp,-1)
and o.dispatch_date < ADD_MONTHS(systimestamp,-12)
group by p.product_id
having count(*) <  10


texts: Hundreds of TCP connections opened by sqlservr.exe

texts:
We have Tomcat on one machine and SQL Server on another.

Our SQL Server machine is Windows 2008 R2 64bit machine with 32GB usable RAM
4 processors, 16 cpus.
We use database pooling to keep db connection reusable.

I am looking at the Resource Monitor in Task Manager and I noticed hundrerds of TCP connections from application server to SQL Server machine.

There are 1014 total connections.

1009 of them are 'AWAITING COMMAND', status = 'sleeping'.
Other 5 are 'SELECT' and 'UPDATE', status = 'runnable'.

Does anyone know why is this happening?

My application-context.xml:

<bean id="hibernateProperties" class="org.springframework.beans.factory.config.PropertiesFactoryBean">
        <property name="properties">
        <props>
            <prop key="hibernate.hbm2ddl.auto">update</prop>
            <prop key="hibernate.dialect">org.hibernate.dialect.SQLServerDialect</prop>
            <prop key="hibernate.show_sql">false</prop>
            <prop key="hibernate.c3p0.min_size">4</prop>
            <prop key="hibernate.c3p0.max_size">10</prop>
            <prop key="hibernate.c3p0.timeout">600</prop>
            <prop key="hibernate.c3p0.max_statements">25</prop>
            <prop key="hibernate.c3p0.testConnectionOnCheckout">false</prop>
            <prop key="hibernate.cache.use_second_level_cache">false</prop>
        </props>
        </property>
    </bean>

And my c3p0.properties:

c3p0.maxIdleTime=900
c3p0.maxStatements=150
c3p0.maxIdleTimeExcessConnections=300
numHelperThreads=6
unreturnedConnectionTimeout=3600


texts: Check if SQL Agent job is being executed manually

texts:
I have a stored procedure that performs database backups.  Because I'm looking at Availability Groups within SQL Server 2012 I want to utilise the *sys.fn_hadr_backup_is_preferred_replica* function within that script to test whether the script should produce the backup.

If the script is run manually instead of as part of a scheduled SQL Agent job, or if the SQL Agent job is executed manually rather than via a schedule, then I want the job to fail with an error message to allow the user to see that the backup has not succeeded due to not being on the preferred replica.  If the job is run on a schedule then I obviously don't want it to fail as it would be producing errors and sending out alerts all day long on the server that is not the preferred replica.

The only bit that I don't know how to do is to check whether the job is being executed by a schedule or manually.  Is this possible?

Failing that, is there any other way that I can alert a user that the script hasn't produced a backup, whilst not causing the scheduled task to fail and produce an alert?


texts: 1 million strings and exact match search

texts:
I need to chose the best solution (software and algorithm) to solve my problem. I need to store up to million strings and data associated with them. Strings are search queries and data is cached search result. So the operations I'm going to perform are:

get a query from user
look for cached result in DB (exact match)
if found - return it
if not - perform a search and store the result

Some other facts:

As it is cache rows will expire over time.
Lookup and insert speed is a priority.
This string field is most probably the only one to search by.
I'm using PHP

Looks like hashing the string is a good idea, collisions are not a problem, if I get it I will just assume it's a cache miss and perform a new search. So I'm thinking of the following options:

MySQL with hash stored in binary field
MySQL with hash as string with index
MongoDB (maybe with capped collection instead of expiration time field)
some other solution (specialized key-value storage or something) I don't have experience with if it's good enough and worth learning for the project

I also have used Sphinx search when I needed almost the same thing but with partial match also. For exact search it seems to be an overkill.

So is hashing a good idea at all?
Which hash algorithm would you recommend?
Which option if better and why?
Does the answer change if I need to store 5 million rows?
What if I add hits field to get most popular rows from time to time?


texts: how to set Mongod config set multiple bindIp addresses

texts:
I have the following config file:

net:
  bindIp: 127.0.0.1
  port: 27017

storage:
  dbPath: c:\mongoDATA\db
  journal:
    enabled: true

##systemLog:
 ## destination: file
 ## path: C:\mongoDATA\log\mongodb.log
 ## logAppend: true

this works, but when I try to add multiple ports like:

bindIp: 127.0.0.1, 192.168.10.115
or
bindIp: "127.0.0.1, 192.168.10.115"
or
bindIp: [127.0.0.1, 192.168.10.115]

with or without the space after the comma it doesn't.  Could someone provide the write syntax?  Thanks  (using 3.6 community)


texts: SQL Server compatibility with New TLS Standards

texts:
The major browsers are moving beyond SSL3.0 and TLS1.0 .
The PCI Security Council has declared an end-of-life date for these
protocols to be considered sufficiently strong encryption.

We need to move away from these protocols, to use newer and stronger ones.
On Windows servers, you can very easily disable these old protocols, and instead offer only TLS1.1 or greater.  However, as noted elsewhere, Microsoft SQL Server 2008 R2 and SQL Server 2012 (Standard, at least) both will not start if those lower protocols are disabled.  However, there are a growing number of versions of MS SQL Server.  There are SQL Server Standard, Business Intelligence, Enterprise, Express, Web, and Compact editions.  And of course there is SQL Server 2008, 2012, 2014, and (in pre-release) 2016.

Which of these editions support or will support the use of only TLS1.1 or greater protocols?


texts: Should you design the database before the application code is written?

texts:
What is the easiest and most efficient way to design a database?  From my perspective, there are a couple of options for an application's data store design:

Design the database as best as you can initially before writing any application code.  This gives you the advantage of having a base data structure to work off of.  The disadvantage of this, in my opinion, is that you will have a lot of changes as application specifics that affect the what/where/how of data changes throughout the application development cycle.
Design the database as the application comes to fruition.  When you need some database objects as you write the application, you develop the database parallel (chronologically) to the application.  The advantages would be less changes to the database structure as I see it.  The disadvantage would be the division of time and development effort between application code and database development.

In your experience, what do you find to be the most productive and efficient method?


texts: postgres create limited user that can select tables and columns of those tables

texts:
I have an app that automates the making of a PostgreSQL database, and also automates the creation of a database user. This database user is given limited access for security purposes, as it will be handed off to a client to use.
The version I am using is Postgres 9.6

CREATE USER %USERSETUP% WITH
    LOGIN PASSWORD '%USERSETUPPASS%'
    NOSUPERUSER
    NOCREATEDB
    NOCREATEROLE
    INHERIT
    NOREPLICATION
    CONNECTION LIMIT -1;

ALTER DEFAULT PRIVILEGES IN SCHEMA %SCHEMA% GRANT INSERT, SELECT, UPDATE, DELETE, REFERENCES ON TABLES TO %USERSETUP%;

Now, my test also goes into the created database, and verifies that it was created correctly, using Query Scripts to grab the Tables names, and then grab the column names inside each table (As a bonus, also grab the column data type)

Because of these permissions, user cannot use information_schema.
Also, I am only requesting query scripts with SYSTEM CATALOG please

The script I have for the table names works:

SELECT tablename FROM pg_catalog.pg_tables where schemaname = '{schema}'

And this returns the table names as a list:

  business, location, person, customer, etc...

However, when I run the following two scripts to get column name, I get a return of 0, as if this user was trying to Query information_schema. I'm guessing it's because of the privileges:

SELECT c.oid,
  n.nspname,
  c.relname
FROM pg_catalog.pg_class c
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relname ~ 'schemaaryzdhyqoi'
  AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 2, 3;

also

SELECT c.oid,
  n.nspname,
  c.relname, t.*
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
CROSS JOIN LATERAL (
  SELECT a.attname,
    pg_catalog.format_type(a.atttypid, a.atttypmod),
    (
      SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) for 128)
      FROM pg_catalog.pg_attrdef d
      WHERE d.adrelid = a.attrelid
        AND d.adnum = a.attnum
        AND a.atthasdef
    ),
    a.attnotnull, a.attnum,
    (
      SELECT c.collname
      FROM
        pg_catalog.pg_collation c,
        pg_catalog.pg_type t
      WHERE c.oid = a.attcollation
        AND t.oid = a.atttypid
        AND a.attcollation <> t.typcollation
    ) AS attcollation
  FROM pg_catalog.pg_attribute a
  WHERE a.attrelid = c.oid
    AND a.attnum > 0
    AND NOT a.attisdropped
) AS t
WHERE n.nspname ~ '^(schemaaryzdhyqoi)$'  -- YOUR SCHEMA HERE
AND pg_catalog.pg_table_is_visible(c.oid);

Question is: How do I add the permission for the limited user to also be able to access just the name (and maybe datatype) of the columns inside each table in the schema?


texts: Shrink log with TRUNCATEONLY but there's nothing to truncate?

texts:
An external company has a job set up which shrinks the log with truncate only - I checked the default trace and for example last night DBCC SHRINKLOG WITH TRUNCATEONLY happened 300 times. If I also check autogrowth events I see that the log auto grew by 2GB only once - I thought there job may be causing the log to expand and contract hundreds of times each night, causing performance issues, but it seems their job is causing the log to expand once, then they shrink it back. I know this is still bad, but not as bad as I hoped!! (So I could present this overwhelming evidence to them and get them to stop the shrinks).

Are these SHRINKLOG WITH TRUNCATEONLY events, even if they have nothing to truncate, still causing performance issues?

Thanks


texts: Redshift: InternalError: cache lookup failed for relation

texts:
I have been speaking with Amazon support about this but their only solution is to "keep trying," which has not been successful.

The issue is when running our ETL we intermittently get the following error when trying to drop a table:

  InternalError: cache lookup failed for relation SOME_ID

Amazon say the following:

  The problem is on the Postgres DB engine caching (Redshift is based on postgres 8.0.2) . This issue is recorded on our SIM and is something to do with the System Catalog Cache access which we inherit from Postgres v 8.0.2, the issue gets reproduced when DROP is used very often and the cache is not able to retrieve data which seems to be out-of-sync. After sometime (few seconds) the case is in back in sync and query runs fine.
  The only workaround I see for the moment is to adjust the logic and retry query when fail, or pause execution of the query between DROP statements.

Has anyone else come across this? I have tried repeatedly executing the drop but it always fails.


texts:
I'm designing a database for a ticket sales system in PostgreSQL, where the admin user can create a batch of tickets for a specific event. All tickets for a given event have the same attributes (like eventId, name, price etc.) except for a serial number, which must be unique for each ticket.

What are the best practices for this situation? Should I treat each ticket as a record in a Ticket table, or should I have a TicketBatch table where I store the ticket information along with the amount available and implement the serial number logic somewhere else?

My concern is that each event can potentially have thousands of tickets for sale, so the one-ticket-per-row solution may not scale well and have slow queries over time. On the other hand, if I go the TicketBatch route, I may have problems keeping track of which tickets (identified by their serial number) were sold.


texts: Help with combining two queries into one

texts:
I have two tables, s and c.

This query works for the inner join

select s.cname, c.HName
from c inner join s on (c.pid = s.pid AND c.cid = s.cid)

I'm having trouble with a where clause. There's a flag in table c indicating an error, and I want to display only the names where the error flag is set to 'Y', but it also has to be the most recent entry (checks every hour so many many entries).

So far I have this:

select max(date),cid,pid, case WHEN error_flag='Y' THEN 'Y' ELSE 'N' END AS "Still has Error"
from c
group by cid,pid,error_flag

That query does the job, but I need to essentially make a where clause for the first query that achieves the same thing. Any suggestions?

Note: Using SQL server 2016


texts: Efficient INSERT INTO a Table With Clustered Index

texts:
I have a SQL statement that inserts rows into a table with a clustered index on the column TRACKING_NUMBER.

E.G.:

INSERT INTO TABL_NAME (TRACKING_NUMBER, COLB, COLC)
SELECT TRACKING_NUMBER, COL_B, COL_C
FROM STAGING_TABLE

My question is - does it help to use an ORDER BY clause in the SELECT statement for the clustered index column, or would any gain acheived be negated by the extra sort required for the ORDER BY clause?


texts: Clarification about master slave configuration in mysql

texts:
We are planning to migrate an existing MySQL database to a new server using the MySQL replication master slave procedure.

Unfortunately this will take a lot of time so we were thinking of doing a dump of the MySQL central db, restoring the dump on the new mysql db, and then setting up the master slave procedure.

Will using the master/slave procedure after restoring the dump of the original database overwrite the data on the new server, thereby wasting our time with the dump/restore process?  Or will the master/slave procedure replicate the data that was added to the old server after the dump took place?

I am using MySQL 5.1 on Debian squeeze.


texts: Postgres Streaming Replication: How to re-sync data in master (with standby) after failover

texts:
I am absolutely new to postgres 9.1 replication mechanisms.

I have managed to set up streaming replication between a master and standby postgres (Both have x number of records). At one point when the master server fails, using the trigger file mechanism the standby takes over, and accumulates additional data (say now has x+y number of records).

Now when the Original Master Server comes up, it still has x number of records (which is now the new master). Is there a way to fetch only the delta, i.e. newly added 'y' number of records from the standby and restart as Master.

Or do I have to always take entire base backup?


texts: remote postgresql 9.3 login using pgadmin3 and a public network server to connect to LAN-only machine

texts:
I want to connect to a machine running postgresql 9.3. The machine itself is not reachable via a direct IP but is in a LAN with another machine with a public IP address.

My current approach is:

changing postgresql.conf:

/..../
listen_addresses = '*'
port = 5432
/..../

adding to pg_hba.conf:

/..../
host    all        all        192.168.59.1/24       trust
/..../

(of cause restarting the postgres service)

connecting to the public server via:

ssh -L 9999:192.168.59.130:5432 user@theip

which works just fine (I am logged in without any error message)

But when I try to connect pgadmin3 using:

name: randomName
Host: 127.0.0.1
Port: 9999
Service:
Maintenance DB: postgres
Username: postgres
Pasword: *********
Colour:
Group: Servers

I get:
Server doesn't listen ....

checking sudo netstat -tulpn shows:

tcp        0      0 0.0.0.0:5432            0.0.0.0:*               LISTEN      8860/postgres
tcp6       0      0 :::5432                 :::*                    LISTEN      8860/postgres

Therefore postgres seems to listen for LAN addresses though I get a "do not listen".

How do I fix that?


texts: How do I know Postgres streaming replication is working?

texts:
I have set up a standby server to follow a primary Postgres server following [this wiki page][1]. The issue is that I am not sure the primary server is sending anything to the replica because I don't see any walsending and walreciving processes on either server when I grep the process list:

[primary] $ ps -ef | grep sender
[standby] $ ps -ef | grep receiver

My recovery.conf file looks like this:

standby_mode = 'on'
primary_conninfo = 'host=${MASTER_PORT_5432_TCP_ADDR} port=5432 user=${REP_USER} password=${REP_PASS}'
trigger_file = '/tmp/promote_db_slave'
recovery_target_timeline = 'latest'

I know the connection is established between the two servers because pg_basebackup succeeds, and when I create the trigger file, the standby server is brought up to date with the base backup. But none of the changes in the primary are sent to the standby.

EDIT: my postgresql.conf in the primary server

listen_addresses = '*'
wal_level = hot_standby
max_wal_senders = 10
max_connections=100
checkpoint_segments = 8
wal_keep_segments = 8
archive_mode = on
archive_command = 'cp %p /var/lib/postgresql/archive/%f'

EDIT2:

I'm using this guide.


texts: MySQL allows records longer than varchar(N)

texts:
We have a database with a field of type VARCHAR(255) but in it I can see some records having a length of larger than 255, such as 264. How can it be possible? Can it be that the database is corrupt? Or somehow MySQL allows larger records? Or is there a known bug?

MySQL version: 5.5.47

Charset: utf8mb4


texts:
I have two tables:
work:

id  w_date        other stuff
1   2017-05-10
1   2017-05-08
3   2017-05-17

home:

id  h_date       other stuff
1   2017-05-01
4   2017-05-06
1   2017-05-14

Now I need a query that I get all dates where id is 1.

Something like:

Select *
from (
    select w_date as date
    from work where id=1
),
  (select h_date as date from home where id=1)

Result should be:

id  date
1   2017-05-01
1   2017-05-08
1   2017-05-10
1   2017-05-14

Any ideas?


texts: How can I increase performance for select in select statement

texts:
The item table is like this

id    category_id    name    registered

I have to show total number of items from the beginning. Suppose between 2013-05-01 to 20-05--05, I have to count all the items before 2013-05-01 and add that to count of 2013-05-01 's items. The result have to be grouped by registered and category_id. I have to do this in one query. This is my query

SELECT registered, me.category_id,
    COUNT( * ) + (select count(*) from items where me.category_id = category_id
    and registered < '2013-05-01 00:00:00') AS Value FROM items me

WHERE (
    (
      category_id IS NOT NULL
      AND
     (me.registered BETWEEN '2013-05-01 00:00:00' AND '2013-05-02 00:00:00')
    )
)
GROUP BY registered, category_id

It takes almost 28 secs to generate the result. How can i increase the performance.

Update
I am using pgSQL. The registered and category_id is indexed. There are 568814 rows. I will be also very grateful, if any one can tell me that my sql is correct or not.

Update
Sorry for late reply.

CREATE TABLE items
(
id uuid NOT NULL DEFAULT uuid_generate_v4(),
registered timestamp without time zone NOT NULL,
modified timestamp without time zone NOT NULL,
published timestamp without time zone,
owner_id uuid,
status_id integer NOT NULL,
category_id uuid NOT NULL,
location_id uuid,
title text,
description text,
quantity integer NOT NULL DEFAULT 1,
unit text,
price_low bigint,
price bigint,
currency text NOT NULL DEFAULT 'BDT'::text,
ip text,
attributes_json text,
from_mobile boolean NOT NULL DEFAULT false,
CONSTRAINT items_pkey PRIMARY KEY (id),
CONSTRAINT items_fk_category_id FOREIGN KEY (category_id)
        REFERENCES categories (id) MATCH SIMPLE
        ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY IMMEDIATE,
CONSTRAINT items_fk_location_id FOREIGN KEY (location_id)
        REFERENCES locations (id) MATCH SIMPLE
        ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY IMMEDIATE,
CONSTRAINT items_fk_owner_id FOREIGN KEY (owner_id)
        REFERENCES users (id) MATCH SIMPLE
        ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY IMMEDIATE,
CONSTRAINT items_fk_status_id FOREIGN KEY (status_id)
        REFERENCES item_statuses (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY IMMEDIATE
)


texts: Difference between MariaDB Server and Maria DB Enterprise?

texts:
https://mariadb.com/pricing

From what I gather, the difference between "free" MariaDB and enterprise MariaDB, is solely around certification, support, and plugins. Is this correct? Is the server itself identical in these two instances?


texts: Changing filegroups from primary to user-defined for existing table in SQL Server

texts: I have 10 million records in a table. But the table is created on primary filegroup.All the indexes and primary keys were created in primary filegroup.There 5 partitions on the table which are also on Primary File group. Is there any way to move Table, indexes and primary key and partitions to user-defined filegroup Without loosing or changing the data in the table?


texts: how to use dual foreign key in oracle 10g

texts:
I have created two tables

create table ref1(id varchar2(3) primary key);
create table ref2(id varchar2(3) primary key);

Now I want to create a third table with a single column as foreign key for both ref1.id and ref2.id

create table actual(p varchar2(3) );

How can i do it?


texts: Is it possible to audit a Postgresql 9.4.5 database in such way that I could find when a specific record was deleted? I don't know what do I need to have enabled / available for such


texts: Will a function which wraps a query criteria be a bottleneck?

texts:
I've been attempting to make the following query perform better lately :
SELECT
    BIN_TO_UUID(album_id) AS e,
    firstdateofinterval AS p,
    BIN_TO_UUID(territory_id) AS t,
    SUM(playbacks) AS c1,
    SUM(userplaybacks) AS c2,
    SUM(userdownloads) AS c3,
    SUM(usersingledownloads) AS c4
FROM
    entityusage_trackbyyear
WHERE
    firstdateofinterval >= '2018-01-01'
        AND firstdateofinterval <= '2018-09-30'
        AND library_id = UUID_TO_BIN('c72cbb1e-5848-39ee-a201-664f26ee9204')
GROUP BY p , e

I've added indexes, which helps, but I also at some point did the following to see if UUID_TO_BIN in the query criteria was a bottleneck :
I moved the UUID_TO_BIN([...]) into
a variable, and used that variable in the query. The query now completed in about 1 second, compared to about  4 - 5 seconds before.
SET @library_id = UUID_TO_BIN('c72cbb1e-5848-39ee-a201-664f26ee9204');
SELECT
    BIN_TO_UUID(album_id) AS e,
    firstdateofinterval AS p,
    BIN_TO_UUID(territory_id) AS t,
    SUM(playbacks) AS c1,
    SUM(userplaybacks) AS c2,
    SUM(userdownloads) AS c3,
    SUM(usersingledownloads) AS c4
FROM
    entityusage_trackbyyear
WHERE
    firstdateofinterval >= '2018-01-01'
        AND firstdateofinterval <= '2018-09-30'
        AND library_id = @library_id
GROUP BY p , e

My understanding was original that running criteria parameters through a function would only evaluate once, and that no noticeable performance impact would be seen unless I inversed this and ran the field through a function.
In other words, I thought this was fine :
[...] AND library_id = UUID_TO_BIN(?) [...]
...while this was bad :
[...] AND BIN_TO_UUID(library_id) = ? [...]
However, it seems that moving the criteria into a session variable performs much better.
Why do I see much better performance when I replace the function wrapped criteria with a variable?


texts: Is it possible to edit sys.messages texts?

texts: Is it possible to add a new language to SQL Server 2008 and edit SQL error messages with message_id less than 50000?