texts: Unexpected Always On Availability groups failover happening--- databases changing to resolving

texts:
I recently had done a SQL patch on a two node SQL availability group of SQL Server 2012. Everything went well as far as I can tell. Now two days after the patch unexpected failover happened. Then today as well unexpected failover happened.

For a temporary period, the primary of the availability group goes in to resolving state and forces it into failover to resume data movement as normal.

Now, I am not sure if this is anything related to the patch. Because the error logs show other issues. Here are the logs from the two times this happened.

  Date      6/11/2018 4:40:06 AM Log        SQL Server (Current - 6/11/2018
  2:25:00 PM) Source        Logon Message Error: 983, Severity: 14, State: 1.

  Date      6/11/2018 4:40:06 AM Log        SQL Server (Current - 6/11/2018
  2:25:00 PM) Source        Logon Message Unable to access database
  'accounting' because its replica role is RESOLVING which does not
  allow connections. Try the operation again later. Date        6/11/2018
  4:40:06 AM Log        SQL Server (Current - 6/11/2018 2:25:00 PM)

  And today Date        6/14/2018 3:13:47 PM Log        SQL Server (Current -
  6/14/2018 3:14:00 PM) Source      spid6s Message SQL Server has
  encountered 1 occurrence(s) of I/O requests taking longer than 15
  seconds to complete on file [C:\Program Files\Microsoft SQL
  Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\EveriT31_Park.mdf] in database
  [EveriT31_Parx] (21).  The OS file handle is 0x0000000000000B00.  The
  offset of the latest long I/O is: 0x00000002750000

  Date      6/14/2018 3:14:04 PM Log        SQL Server (Current - 6/14/2018
  3:14:00 PM) Source        spid127 Message Error: 18056, Severity: 20, State:
  46.

  Date      6/14/2018 3:14:04 PM Log        SQL Server (Current - 6/14/2018
  3:14:00 PM) Source        spid127 Message The client was unable to reuse a
  session with SPID 127, which had been reset for connection pooling.
  The failure ID is 46. This error may have been caused by an earlier
  operation failing. Check the error logs for failed operations
  immediately before this error message. Date       6/14/2018 3:14:05 PM
  Log       SQL Server (Current - 6/14/2018 3:14:00 PM)

The AG is set for automatic failover but the alwayson group is not really changing roles. After these episodes, the primary remains primary and the secondary remains secondars.

Every log I look at the first error I get is either the I/O issue or connection pool error (Error: 18056, Severity: 20, State: 46.). I hesitate to say the problem is really that. I don't think it is related with the SQL patch I just did. I don't see anything that is related to it and I want to rule that one out.

Can anyone suggest what might be causing these? I looked at some articles, but none gave me a clear answer.


texts:
I just read this qestion: SQL Multiple Filter - Optimal Way

I tried this with Northwind.
I filter like this:

declare @supplier int = 1
declare @category int = 1
select * from products p

WHERE (@supplier  = '' or SupplierId = @supplier)
  AND (@category = '' or CategoryId = @category)

This seems to work as expected, I get two hits: Chai and Chang.
Now I want to add filter ShipPostalCode:

declare @supplier int = 1
declare @category int = 1
declare @shippostal nvarchar(50) = '01307'
select * from products p
inner join [Order Details] od on p.ProductId = od.ProductId
inner join Orders o on o.OrderId = od.OrderId
AND (@category = '' or CategoryId = @category)
AND (@shippostal = '' or ShipPostalCode = @shippostal)

Because of the join I now have multiple rows of Chai and Chang.
These filters are users who makes selection in dropdown lists, so even if shippostalcode is chosen, I still need one hit.

distinct productName

will give me one row per product, but that's not the way to solve it, is it?

EDIT:
Thank you guys,
Yes, supplier is an int.

So If there be would another many to many relation from the Products table I can just add another AND, I guess.

Sorry, this is a very sad example, but if I add OrderDetails2 and Orders2 where OrdersDetails2 will be a junction table between Products and Orders2, I can just add add another AND (and keep on filtering), like this:

declare @supplier int = 1
declare @category int = 1
declare @shippostal nvarchar(50) = '01307'
declare @city nvarchar(50) = 'boston'
select * from products p
WHERE (@supplier  = '' or SupplierId = @supplier) AND (@category = '' or CategoryId = @category)
AND ProductId in
(
Select od.ProductId from [Order Details] od
inner join Orders o on o.OrderId = od.OrderId
Where (@shippostal = '' or ShipPostalCode = @shippostal)
)
AND ProductId in
(
Select od.ProductId from OrderDetails2 od
inner join Orders2 o on o.OrderId = od.OrderId
Where (@city = '' or City = @city)
)

As you can see, the Order2 table has a column 'City'
What do you think?


texts: PostgreSQL FATAL canceling authentication due to timeout

texts:
I have a PostgreSQL 9.4 Database running on my CentOS 7 Server.

This database has only ever been available to users on the LAN,
however recently I needed to make it available to users outside the LAN
which I did using port forwarding for port 5432 on the server using
our Netgear ADSL Router.

My PostgreSQL database has been configured

postgresql.conf

listen_addresses = '*'

pg_hba.conf

host   dbname   usname   all   md5

Everything seems to be working fine, the users can access the database from the internet however there are several times during the day where they cannot connect.

When I check the logs it's always when I see the message

FATAL canceling authentication due to timeout

Why is this? Do I need to add further configurations to accommodate this?


texts:
I am using Postgis 2.4 with posgresql 10 in Windows 10.
I have the following SQL query:

WITH data (the_geom) as (
  SELECT a.gid,
          CASE WHEN ST_Within(a.geom,b.geom) THEN a.geom
               ELSE ST_Intersection(a.geom,b.geom)
          END AS the_geom
  FROM source.g100_wby_lakes_r as a
      JOIN extents.map_areas as b ON ST_Intersects(a.geom,b.geom)
  WHERE b.map_id='AA01'
)
INSERT into public.g100_wby_lakes_r (gid,geom)
select *
from data
where st_GeometryType(the_geom)='MULTIPOLYGON';

It tries to select the intersection between two multipolygons and only insert into a new table the intersected geometries which are also multipolygons (the intersection could also result in points or lines). All works except for the WHERE clause which gives the following error:

ERROR:  column reference "the_geom" is ambiguous
LINE 4: ...id,geom) select * from data where st_GeometryType(the_geom)=...
                                                             ^
SQL state: 42702
Character: 351

Why is the_geom ambiguous?
How can I change the SQL to make it work?
Thank you


texts:
I wanted to know that, can I restore backup of SQL Server 2016 Standard edition  database into SQL Server 2016 Express edition database.

Or vice-versa


texts: Joining and combining MySQL tables from Informix

texts:
I previously asked this in StackOverflow, But I couldn't find a solution, so I thought I'd share my problem here too.
I have an old system that uses Informix for data storage, and I'm working on migrating such system, which includes migrating the data to a MySQL server.

What I need to do is allow some old subsystems to query some data already stored in MySQL (corresponding to subsystems in production), and combine it with their Informix tables.

So I find myself in the need to combine MySQL and Informix tables (joins in particular), and I have no idea how to proceed.
I tried querying MySQL, and building a temporary table in Informix with the recovered data, but there are more than 4000 rows, and the insert queries are too slow.


texts: Need a help in Query to check whether Task is completed by other user or not before showing on dashboard

texts:
I have 3 tables in database Task, User & assignedTo.

Task contains information of Task created by Admin, User is list of User & assignedTo contains information about which task assign to which user.

i need to query on 'assignedTo' table to show a list of Task assigned to users on their Dashboard. Problem is a single Task is assigned to multiple user and it may possible task may be completed by one of them. So i need to show task which are in Open Status and not completed by anyone in assigned list.
Below is structure of 'assignedTo' Table:

--------------------------------
| id | taskId | userId | status|
--------------------------------
| 1  |  1     | 1      | open  |
| 2  |  1     | 2      | open  |
| 3  |  1     | 3      | open  |
| 4  |  1     | 4      | open  |
| 5  |  2     | 1      | open  |
| 6  |  2     | 2      | close |
| 7  |  3     | 1      | open  |
| 8  |  3     | 2      | open  |
--------------------------------

So as per above data, Task 1 is shown on user 1,2,3 & 4 Dashboard as it is not closed by any of them.
Task 2 is shown on no one's Dash as it is closed by User 2.
Similarly, Task 3 is shown on user 1 & 2 Dash as it is open for both of them.

i tried to join same table to it's own but failed to execute.

SELECT * FROM assignedTo  a1
JOIN assignedTo a2 ON a2.taskId = a1.taskId AND a2.userId != 1 AND a2.status = 'open'
WHERE a1.userId = 1 AND a1.status = 'open'

Please help with Query if possible.


texts: Patching of Windows Server 2012 running SQL Server 2014

texts:
We have patching on servers running SQL Server 2014. Good thing we have always on availability group. Bad thing, they are patching Primary Server first. Trying to understand how this is going to work.
1) Primary gets patched, DBs fail over to Secondary automatically.
2) Assuming all goes well, Primary will come back up and becomes secondary,
   DBS get back in Sync.
3) Next day, they patch secondary (which is at this point primary) and another
   fail over happens and roles change.

Does it sound correct?


texts: exporting oracle tables on command line as sql files

texts:
I use exp and expdp to export database tables on command line but it generates binary .dmp files. I want to export/import tables as .sql* files on command line like pl/sql developer app can do. Is there a specific command for this or extra parameters for exp/expdp?

*sql files must contain table creation scripts and insert statements.

Thanks.


texts:
I have a rounding problem with the result of a query as shown below, where it feels that somewhere along the calculation the result was truncated. The result is

The SQL query is:

SELECT
    "wos"."ISOVERDUE" AS "Broken SLA",
    (COUNT("wo"."WORKORDERID")) * 100 /(
       SELECT COUNT(*)
       FROM   "WorkOrder" "wo"
       WHERE  wo.ISPARENT = '1'
              AND wo.CREATEDTIME >= < from_thismonth >
              AND wo.CREATEDTIME <= < to_thismonth >
     ) AS "%"

FROM "WorkOrder" "wo"
LEFT JOIN "ModeDefinition" "mdd" ON "wo"."MODEID"="mdd"."MODEID"
LEFT JOIN "SDUser" "sdu" ON "wo"."REQUESTERID"="sdu"."USERID"
LEFT JOIN "AaaUser" "aau" ON "sdu"."USERID"="aau"."USER_ID"
LEFT JOIN "DepartmentDefinition" "dpt" ON "wo"."DEPTID"="dpt"."DEPTID"
LEFT JOIN "WorkOrderStates" "wos" ON "wo"."WORKORDERID"="wos"."WORKORDERID"
LEFT JOIN "CategoryDefinition" "cd" ON "wos"."CATEGORYID"="cd"."CATEGORYID"
LEFT JOIN "WorkOrder_Queue" "woq" ON "wo"."WORKORDERID"="woq"."WORKORDERID"
LEFT JOIN "QueueDefinition" "qd" ON "woq"."QUEUEID"="qd"."QUEUEID"

WHERE   wo.ISPARENT='1' AND wo.CREATEDTIME >= <from_thismonth> AND wo.CREATEDTIME <= <to_thismonth>

GROUP BY "wos"."ISOVERDUE"


texts:
Is there a way to build a query that returns a record set consisting of a date and the number of records == that date? For example, I have a DB that is tracking inspections that take place on every day. I want to build a data set to be used for a trending graph to see how many inspections took place on each day streching back 365 days.

Record set desired would look like this:

date | InspectionCount
2013-12-30  265
2013-12-29  123
2013-12-28  425
.
.
.
2013-01-01  15

Is there a way to do that without write code that will simple loop through 365 times and sort of manually generate daily counts like this:

select count(reporting_id) as InspectionCount
from inspection
where close_conf_date >= '2013-12-30'
and close_conf_date < '2013-12-31'

while gradually winding the clock back 365 days? Please let me know if you need more info.

Update

I have just tried this:

WITH RECURSIVE t(n) AS (
VALUES (cast('2013-12-30' as timestamp))
UNION ALL
SELECT n-interval '1 day' FROM t WHERE n >=('2013-12-30'-interval '365 day')
)
SELECT  cast(t.n as date) as date_column, count(reporting_id)
FROM t
INNER JOIN inspection i ON (cast(t.n as date) = cast(i.close_conf_date as date))
GROUP BY cast(t.n as date)

and I am getting this error i do not really understand:
interval field value out of range: "2013-12-30"


texts: MongoDB SSL peer certificate validation failed: unable to get issuer certificate

texts:
I have enabled SSL on MongoDB with optional SSL connection: preferred.

I have used lets encrypt in order to obtain SSL certificates. SSLs works as expected and in mongod.log file I can see that:

2018-06-02T06:46:26.664+0000 I NETWORK  [listener] connection accepted from MY_SERVER_IP:45442 #2953818 (121 connections now open)
2018-06-02T06:46:26.664+0000 I NETWORK  [conn2953818] SSL mode is set to 'preferred' and connection 2953818 to MY_SERVER_IP:45442 is not using SSL.

Now on the same host I try to connect to mongo using mongo client command like below:

mongo --ssl --host mongo.example.com --sslPEMKeyFile /etc/ssl/mongo.pem --sslCAFile /etc/ssl/ca.pem

when I use SSL mongo gives error:

MongoDB shell version v3.6.2
connecting to: mongodb://mongo.example.com:27017/
2018-06-02T06:48:34.156+0000 E NETWORK  [thread1] SSL peer certificate validation failed: unable to get issuer certificate
2018-06-02T06:48:34.164+0000 E QUERY    [thread1] Error: socket exception [CONNECT_ERROR] for SSL peer certificate validation failed: unable to get issuer certificate :
connect@src/mongo/shell/mongo.js:251:13
@(connect):1:6
exception: connect failed

Why it reports that unable to get issuer certificate? Is there something in between that I have missed? Could someone shed some light on this?

EDIT1:

I tried the below command too and got the error network error:

root@example2:~# mongo --ssl --host='mongo.domain.com' --port=27017
MongoDB shell version: 3.2.11
connecting to: mongo.domain.com:27017/test
2018-06-02T08:31:13.501+0000 E QUERY    [thread1] Error: network error while attempting to run command 'isMaster' on host 'mongo.domain.com:27017'  :
connect@src/mongo/shell/mongo.js:231:14
@(connect):1:6

exception: connect failed

Now getting error network error while attempting to run command 'isMaster' on host.

In MongoDB server log it reports that:

Error receiving request from client: SSLHandshakeFailed: SSLHandshakeFailed. Ending connection from 127.0.0.1:32793 (connection id: 358957)


texts:
I have a SQL Server database that gets updated every week with a completely new set of data.
I am currently using two staging databases. A series of view databases are connecting to these and one week the view tables have views that point at one set of tables contained in one of the staging databases and the then the views are altered the other week when the ingest of the refreshed data is complete. Staging tables are truncated before each load. There are 12 tables which range from 50Gb to 100Gb.
This is to avoid people waiting for the lengthy ingest to complete and keeps people working.
Are there any more efficient strategies? In Oracle I would turn a materialised view into a table and then refresh the view once the ingestion was complete.
I don't think there is a similar strategy for SQL Server. I am using SQL Server 2016 enterprise.


texts: Apache Ignite as linked Server in SQLServer?

texts:
I tried to set up a linked server via ODBC connection in SQLServer. It seem to connect but showed all the caches as empty names in SSMS. Also I couldnt get a select query to work.

Has anybody had any luck with doing this?
If so.. How did you write the select statement?


texts: Which BI solutions cooperate with Postgres database and aren't too expensive?

texts: I'm looking for solution letting me and my team track changes in Postgres database with daily updates. Data doesn't have to be updated instantly, once a day is enough. We use Excel/Google Sheet reports based on CSV files generated from Postgres at this moment and we're looking for more handy solution.


texts:
I’m trying to find information about when and who shrunk a ldf file but I can't find the information anywhere.

Can someone point me in the right direction?

Microsoft SQL Server 2008 R2 (SP2) - 10.50.4266.0 (X64)
    Sep 26 2012 17:08:07
    Copyright (c) Microsoft Corporation
    Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)


texts: How to set current sequence while converting primary key serial(integer) to bigserial(bigint)

texts:
The table already has few thousands of rows and I want to convert the primary key from Integer to BigInteger. How to change the sequence for biginteger?

--Create new temp sequence (sequence-name1) for bigint
CREATE SEQUENCE public.case_audit_case_audit_uid_seq1
    INCREMENT 1
    START 5542
    MINVALUE 1
    MAXVALUE 9223372036854775807
    CACHE 1;
ALTER SEQUENCE public.case_audit_case_audit_uid_seq1  OWNER TO postgres;
--Alter column to bigint
ALTER TABLE public.case_audit ALTER COLUMN case_audit_uid TYPE bigint;
--Alter pk column with temp Sequence
ALTER TABLE public.case_audit ALTER COLUMN case_audit_uid SET DEFAULT     nextval('indsolv.case_audit_case_audit_uid_seq1'::regclass);
--Drop original sequence
DROP SEQUENCE public.case_audit_case_audit_uid_seq;
--Create new sequence for bigint with original sequence name
CREATE SEQUENCE public.case_audit_case_audit_uid_seq
    INCREMENT 1
    START 5542
    MINVALUE 1
    MAXVALUE 9223372036854775807
    CACHE 1;
ALTER SEQUENCE public.case_audit_case_audit_uid_seq OWNER TO postgres;
---Set original sequence
ALTER TABLE public.case_audit ALTER COLUMN case_audit_uid SET DEFAULT nextval('public.case_audit_case_audit_uid_seq'::regclass);
---Drop temp sequence
DROP SEQUENCE public.case_audit_case_audit_uid_seq1;

Is there any simple solution compare to this?


texts: Expecting Postgresql WAL to shrink in size but it's not

texts:
We set our max_wal_size to 24 GB recently (from the 1 GB default), did some testing, and then set it to 12 GB followed by a server restart. When I query the size of the WAL on the filesystem (total size of files in pg_xlog directory), it still shows as about 20 GB. I have issued a manual checkpoint and restarted the server, but the WAL hasn't shrunk back down to 12 GB. This is a pretty simple implementation -- no replication nor archiving in place and there are no long-running transactions that exist. Am I misunderstanding how this is supposed to work? Does it not delete the old WAL files when you lower the max_wal_size value and when all transactions have been been completed and the server restarts?

Postgres version: 9.6.15

OS: Linux Ubuntu 16.04.6 LTS

Settings:

max_wal_size = 12GB

min_wal_size = 80MB

wal_keep_segments = 0

checkpoint_timeout = 15min

checkpoint_completion_target = 0.87

wal_compression = off

archive_mode = off

wal_level = minimal


texts: How to achieve Dynamic SQL Query with Multiple parmeters to handle null values also

texts:
My code logic is given below, when one of my input parameter passed as null, the parameterlist should be managed according, how could i proceed for this.

DECLARE @SQL NVARCHAR(MAX)
DECLARE @paramlist NVARCHAR(MAX)

SET @SQL='select [Company ID],
[QSS Company ID],
[Company Name],
[Inactive]  from Company WHERE 1=1'

IF @CompanyID IS NOT NULL
SELECT @SQL = @SQL + ' AND ([Company ID] = @CompanyID)'
Select @paramlist=@paramlist+'@CompanyID BIGINT'
IF @QSSCompanyID IS NOT NULL
SELECT @SQL = @SQL + ' AND ([QSS Company ID] = @QSSCompanyID)'
IF @CompanyName IS NOT NULL
SELECT @SQL = @SQL + ' AND ([QSS Company ID] = @CompanyName)'

EXEC sp_executesql @SQL, N'@CompanyID BIGINT, @QSSCompanyID BIGINT,
@CompanyName  nvarchar',  @CompanyID,@QSSCompanyID,@CompanyName


texts: PostgreSQL on RDS: How to bulk import data with FK constraints?

texts:
Is there an efficient way to bulk import data into a PostgreSQL 9.4 database on RDS that makes extensive use of foreign key constraints? The standard method of using pg_restore fails because RDS does not appear to allow you to disable system triggers.

For example, using the command:

pg_restore --host foo.rds.amazonaws.com --port 5432 --username "bar" --role=rds_superuser --dbname "baz" --data-only --disable-triggers --no-owner --no-privileges --no-tablespaces --verbose --exit-on-error "baz.dump"

Results in error messages such as:

pg_restore: [archiver (db)] could not execute query: ERROR:  permission denied: "RI_ConstraintTrigger_a_30548" is a system trigger
    Command was: ALTER TABLE xxxx DISABLE TRIGGER ALL;


texts: Slow query over 10mil rows with group by on indexed column

texts:
I've got a slow query which I want to optimize.

SELECT
    pand.bouwjaar AS construction_date,
    count(*)
FROM pand AS pand
WHERE
    pand.begindatumtijdvakgeldigheid <= 'now'::text::timestamp without time zone
    AND (pand.einddatumtijdvakgeldigheid IS NULL OR pand.einddatumtijdvakgeldigheid >= 'now'::text::timestamp without time zone)
    AND pand.aanduidingrecordinactief = false
    AND pand.geom_valid = true
    AND pand.pandstatus <> 'Niet gerealiseerd pand'::pandstatus
    AND pand.pandstatus <> 'Pand gesloopt'::pandstatus

GROUP BY pand.bouwjaar

At the moment it takes 37999.326 ms while it's 'just' a group by and count on a indexed column. I know the query is hard, it has to process over 10 million rows, but i hope the query can do better then 38 seconds.

This is the Explain(analyze, buffers):

HashAggregate  (cost=1087761.16..1087762.90 rows=174 width=5) (actual time=37998.961..37999.173 rows=855 loops=1)
  Buffers: shared hit=5312 read=606349
  ->  Seq Scan on pand  (cost=0.00..1037569.41 rows=10038351 width=5) (actual time=1.237..31246.011 rows=9935732 loops=1)
        Filter: ((NOT aanduidingrecordinactief) AND geom_valid AND (pandstatus <> 'Niet gerealiseerd pand'::pandstatus) AND (pandstatus <> 'Pand gesloopt'::pandstatus) AND (begindatumtijdvakgeldigheid <= ('now'::cstring)::timestamp without time zone) AND ( (...)
        Rows Removed by Filter: 4261215
        Buffers: shared hit=5312 read=606349
Total runtime: 37999.326 ms

If I'm ready this Explain-message correct my biggest problem lies with the HasAggregate with the high Cost.

The strange thing is I already have an index on pand.bouwjaar.
How can I speed up this query? Or should I deal with it ;)

When removing the whole Where clause I'm getting the following queryplan:

HashAggregate  (cost=824615.21..824617.66 rows=245 width=5) (actual time=23589.184..23589.612 rows=1033 loops=1)
  Buffers: shared hit=5473 read=606188
  ->  Seq Scan on pand  (cost=0.00..753630.47 rows=14196947 width=5) (actual time=2.528..13207.042 rows=14196947 loops=1)
        Buffers: shared hit=5473 read=606188
Total runtime: 23589.862 ms

Still 23.6 seconds :(

Update

As requested the information on the table and version info:

PostgreSQL 9.3.6 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu 4.8.2-19ubuntu1) 4.8.2, 64-bit

CREATE TABLE pand
(
  gid serial NOT NULL,
  identificatie numeric(16,0),
  aanduidingrecordinactief boolean,
  aanduidingrecordcorrectie integer,
  officieel boolean,
  inonderzoek boolean,
  begindatumtijdvakgeldigheid timestamp without time zone,
  einddatumtijdvakgeldigheid timestamp without time zone,
  documentnummer character varying(20),
  documentdatum date,
  pandstatus pandstatus,
  bouwjaar numeric(4,0),
  geom_valid boolean,
  geovlak geometry,
  CONSTRAINT pand_pkey PRIMARY KEY (gid),
  CONSTRAINT enforce_dims_geometrie CHECK (st_ndims(geovlak) = 3),
  CONSTRAINT enforce_geotype_geometrie CHECK (geometrytype(geovlak) = 'POLYGON'::text OR geovlak IS NULL),
  CONSTRAINT enforce_srid_geometrie CHECK (st_srid(geovlak) = 28992)
)
WITH (
  OIDS=TRUE
);

Indexes on this table:

CREATE INDEX pand_key
  ON pand
  USING btree
  (identificatie, aanduidingrecordinactief, aanduidingrecordcorrectie, begindatumtijdvakgeldigheid);

CREATE INDEX pand_idx_bouwjaar
  ON pand
  USING btree
  (bouwjaar);

CREATE INDEX pand_geom_idx
  ON pand
  USING gist
  (geovlak);


texts: What are the in-built rules of naming environmental variables in GridDB?

texts: What are the naming conventions in GridDB environmental variables?


texts:
Certain DMV queries that return various statistics that increment since boot would be useful as perfmon counters.

For example, take:

SELECT wait_type, wait_time_ms FROM sys.dm_os_wait_stats;

Most monitoring tools know how to take an incrementing number and turn it into a rate since this is how SNMP counters work. Is anyone aware of any preexisting tools that turn these sort of stats into DMV queries?


texts:
I have two very similar queries that are returning different totals. The first query, where the total [of subscription_payment.price] is calculated with PHP (and verified in Excel), is the same query that the second query is based off of.

First Query:

SELECT *,
       subscriptions_new.id AS subscription_id,
       plans_new.name AS plan_name,
       plans_new.guideid AS guideid,
       subscription_payment.date AS date,
       subscription_payment.renewal AS renewal,
       subscription_payment.price AS price,
       subscription_payment.price AS renewal_price
FROM transactions_new
JOIN accounts ON accounts.id = transactions_new.userid
JOIN subscriptions_new ON FIND_IN_SET(subscriptions_new.id, transactions_new.subscription_ids)
JOIN plans_pricing ON subscriptions_new.pricing_id = plans_pricing.id
JOIN subscription_payment ON subscription_payment.subscription_id = subscriptions_new.id
JOIN plans_new ON plans_new.id = plans_pricing.plan_id
WHERE
  subscription_payment.date >= 1417410000
  AND subscription_payment.date <= 1418187540
  AND subscription_payment.deleted != 1
GROUP BY subscriptions_new.id
ORDER BY plan_code DESC

Second Query:

SELECT SUM(subscription_payment.price) AS total,
       COUNT(*) AS qty,
       plan_code AS plan_code
FROM transactions_new
JOIN accounts ON accounts.id = transactions_new.userid
JOIN subscriptions_new ON FIND_IN_SET(subscriptions_new.id, transactions_new.subscription_ids)
JOIN plans_pricing ON subscriptions_new.pricing_id = plans_pricing.id
JOIN subscription_payment ON subscription_payment.subscription_id = subscriptions_new.id
JOIN plans_new ON plans_new.id = plans_pricing.plan_id
WHERE
  subscription_payment.date >= 1417410000
  AND subscription_payment.date <= 1418187540
  AND subscription_payment.deleted != 1
GROUP BY plan_code
ORDER BY plan_code DESC

In the second query,total is not matching what has been calculated from adding all of the records in the first query.

Any help is appreciated. Also note that the GROUP BY makes no difference when calculating the totals (so it's okay for them to be different - that's not causing the issue).


texts: Suggestion for Tooth Chart Data Structure

texts:
Hello I'm not sure if this is an OK question here but here goes, I'm quite confused on how am I going to make the data structure of my tooth chart database.  I had first this thought of making a data structure like this

teeth_table
teeth_id (Primary key)
teeth_1 (These are 32 tooth)
until teeth_32 (These are 32 tooth)
patient_id (Foreign key connected to the patient table)

my second plan is to make an individual table for each tooth (which will take a lot of time I guess?)

tooth01_table
tooth_id
tooth_name
tooth_condition
tooth_recommendation
tooth_treatment
patient_id

and that's all I thought,
my plan is to make a table where it would show the following when a patient selects a tooth from a dropdown list

Tooth | Condition | Recommendation | Treatment

So what do you guys think is a good design for a tooth chart database?

a tooth chart is like these

  http://www.mouthandteeth.com/img/FDI-tooth-numbering-system.gif


texts: behavior of a Sql Server availability group listener and a jdbc client during a failover?

texts:
i have a question about the behavior of the AG listener during a failover... when the failover happens, I realize that all the connections on both primary and secondaries are closed.
if the client apps are using one of the blessed client libraries (such as microsoft jdbc > 6.0) they will then start immediately trying to reconnect to the ip addresses associated with the listener. But what happens during the 10 second period (or whatever it is) for the failover to complete? Might a client successfully get a connection, only to get a fatal error message when it tries to use it? Or is something done to ensure that no client will get a connection successfully during failover itself, so that that when a client does successfully get a connection, it is actually usable?
(FYI regarding the behavior of the microsoft jdbc driver, judging from the source code at:
https://github.com/microsoft/mssql-jdbc/blob/dev/src/main/java/com/microsoft/sqlserver/jdbc/SQLServerConnection.java
It will divide the configured loginTimeout by 8 and so make 8 attempts before timing out.)


texts: What are some databases that are schema-full but not relational

texts:
What are some databases/datastores that are schema-full, but not relational?

In other word I'm after some NoSql databases that are not schema-less.

By schema-full I mean that, like in a traditional relational sql database, all rows/entities/values of a given 'table' should all have the same structure, which is defined upfront and checked by the datastore.

I'm interested in object database but I'm a believer in schemas / strongly typed data...

Thanks


texts:
I have a query which involves joins across 7 different tables.

Below is the query:

SELECT        /*+qb_name(select1) dynamic_sampling(nbtd 2) */
               td.tsf_no                      C1
              ,td.tsf_seq_no                  C2
              ,NVL(th.from_loc,-999)          C3
              ,NVL(th.to_loc,-999)            C4
              ,NVL(th.to_loc_type,'-1')       C5
              ,NVL (s.to_loc,'-999')          C6
              ,td.item                        C7
              ,NVL(ss.qty_expected, 0)        C8
              ,NVL(ss.carton,'-1')            C9
              ,NVL(ss.distro_no,'-1')         C10
              ,NVL(ss.distro_type,'-1')       C11
              ,NVL(ss.qty_expected, 0)        C12
              ,NVL(ss.weight_received, 0)     C13
              ,NVL(ss.weight_received_uom, 0) C14
              ,s.shipment                     C15
              ,NVL(s.bol_no,'-1')             C16
              ,NVL(s.order_no, 1)             C17
              ,ils.av_cost                    C18
              ,il.unit_retail                 C19
              ,user                           C20
              ,to_char(sysdate, 'YYYYMMDD')   C21
              ,'T'                            C22
              ,ss.seq_no                      C23
              ,nbtd.expense_ind               C24
              ,nbtd.process_expense_ind       C25
              ,to_char(th.create_date, 'YYYYMMDD')  C26
          FROM nb_tsfdetail nbtd
              ,tsfdetail td
              ,tsfhead th
              ,shipsku ss
              ,shipment s
              ,item_loc il
              ,item_loc_soh ils
         WHERE td.tsf_no        = nbtd.tsf_no
           AND td.tsf_seq_no    = nbtd.tsf_seq_no
           AND td.tsf_no        = th.tsf_no
           AND ss.distro_no     = td.tsf_no
           AND ss.item          = td.item
           AND ss.shipment      = s.shipment
           AND td.item          = il.item
           AND il.loc           = th.from_loc
           AND ils.item         = il.item
           AND ils.loc          = il.loc
           AND nbtd.process_expense_ind = 'N'
           AND nbtd.expense_ind = 'Y';

The data in each table is:

select count(1) from nb_tsfdetail;--692,336,190
select count(1) from tsfdetail;--693,317,818
select count(1) from tsfhead;--3,697,876
select count(1) from shipsku;-- 1,273,823,438
select count(1) from shipment;--514,593,396
select count(1) from item_loc;-- 109,587,391
select count(1) from item_loc_soh;--108,435,856

I have attached the explain of the above query.


texts:
This may be a silly question but I recently created an azure sql database and I need to check the backup policy but I cannot find the option manage backups that the documentation shows as you can see in the next picture:

and this is how the blade of my database look like

Am I missing something?


texts: Forcing a hash join on a temporary table generates error

texts:
I have the following query:

select
    afp.period_start
    , afp.period_end
    , COUNT(*)
from services s
    Left join
        (
            select entityId, resolutionDate
            from ...
        ) SI on si.entityId = s.Id
    left join #aff_periods afp -- or left hash join #aff_periods afp
        on isnull(si.reslutionDate, GETDATE()) <= afp.period_end
group by afp.period_start
    , afp.period_end
option (hash join); -- or this

Whenever adding either the query-level hint or join-level hint I get the following error.

  Msg 8622, Level 16, State 1, Line 50

  Query processor could not produce a query plan because of the hints defined in this query. Resubmit the query without specifying any hints and without using SET FORCEPLAN.

I've read some posts online suggesting that this might be because of a index on the table, so I dropped the non-clustered index I had created on that table and tried to re-run the query but I still get the error.

Any suggestions what I could try or why this is happening for a temp table?


texts: mongoDB - how to link one embedded object to another embedded object

texts:
Background Information

I'm trying my hand at my first mongoDB database.
So far, I've decided that I will need to use both references and embedded data models. One of the features of this application will allow users to track where ports on a network switch are mapped to.

Example:
Let's say I have switch "ABC" and "DEF".  Port a2 on ABC is linked to port d3 on DEF. On my web interface, when the user is viewing the details for switch ABC, I want to show that it's linked to DEF's port d3 using a selectable / clickable hyperlink.  When the clicks on it, I want to be able to show the details for switch DEF port d3.

Question:

I'm not sure what the best way is as far as design is concerned, to support this type of functionality.

Model

So far I have the following model in mind:

Switch types collection:

{
   _id: "HP-ProCurve-5406zl",
   name: "HP ProCurve 5406zl",
   propertyX: valueX,
   propertyY: valueY
}
{
   _id: "switch-xyz",
   name: "The best switch ever",
   propertyX: valueX,
   propertyY: valueY
}

"network_switches" collection:

{
   _id: 12345,
   Name: "ABC",
   type_id: "HP-ProCurve-5406zl",
   ports: [
                {
                  port_name: "a1",
                  visible_label: "",
                  interface_id: "1000Base-T",
                  L2_address: "",
                  linked_port:
                }
                {
                  port_name: "a2",
                  visible_label: "",
                  interface_id: "1000Base-T",
                  L2_address: "",
                  linked_port:
                }
                {
                  port_name: "a3",
                  visible_label: "",
                  interface_id: "1000Base-T",
                  L2_address: "",
                  linked_port:
                }

          ]
}

{
   _id: 12366,
   Name: "DEF",
   type_id: "switch-xyz",
   ports: [
                {
                  port_name: "a1",
                  visible_label: "",
                  interface_id: "1000Base-T",
                  L2_address: "",
                  linked_port:
                }
                {
                  port_name: "d2",
                  visible_label: "",
                  interface_id: "1000Base-T",
                  L2_address: "",
                  linked_port:
                }
                {
                  port_name: "d3",
                  visible_label: "",
                  interface_id: "1000Base-T",
                  L2_address: "",
                  linked_port:
                }

          ]
}

Possible Solution?

I was thinking of assigning port ids in addition to names using the switch name.  So for sample, for the ABC switch, port "a1" should look like this:

                  port_id: "ABC-a1",
                  port_name: "a1",

And the d3 port on switch DEF would look like this:

                  port_id: "DEF-d3",
                  port_name: "d3",

That would then allow me to do something like this with the linked_port
fields:

               {
                  port_id: "ABC-a1",
                  port_name: "a1",
                  visible_label: "",
                  interface_id: "1000Base-T",
                  L2_address: "",
                  linked_port: "DEF-d3"
                }

Before I commit to this, I wanted to make sure there were no glaring problems with this design. Is there a way in mongo for me to find this DEF object by querying using the port id?

Any comments?


texts:
I have a master-slave configuration. The slave stopped replicating with no errors. The only thing strange i see is this
Slave_SQL_Running_State: init

in show slave status; I see
enter code hereSlave_IO_State: Waiting for master to send event
              Master_Host: *
              Master_User: slave
              Master_Port: 3306
            Connect_Retry: 60
          Master_Log_File: mysql-bin.001744
      Read_Master_Log_Pos: 611616113
           Relay_Log_File: replica01-relay-bin.003476
            Relay_Log_Pos: 280710341
    Relay_Master_Log_File: mysql-bin.001733
         Slave_IO_Running: Yes
        Slave_SQL_Running: Yes
          Replicate_Do_DB: asterisk
      Replicate_Ignore_DB:
       Replicate_Do_Table:
   Replicate_Ignore_Table:
  Replicate_Wild_Do_Table:Replicate_Wild_Ignore_Table:
               Last_Errno: 0
               Last_Error:
             Skip_Counter: 0
      Exec_Master_Log_Pos: 284884080
          Relay_Log_Space: 12277016554
          Until_Condition: None
           Until_Log_File:
            Until_Log_Pos: 0
       Master_SSL_Allowed: No
       Master_SSL_CA_File:
       Master_SSL_CA_Path:
          Master_SSL_Cert:
        Master_SSL_Cipher:
           Master_SSL_Key:
    Seconds_Behind_Master: 161262Master_SSL_Verify_Server_Cert: No
            Last_IO_Errno: 0
            Last_IO_Error:
           Last_SQL_Errno: 0
           Last_SQL_Error:  Replicate_Ignore_Server_Ids:
         Master_Server_Id: 1
           Master_SSL_Crl:
       Master_SSL_Crlpath:
               Using_Gtid: No
              Gtid_IO_Pos:
  Replicate_Do_Domain_Ids:  Replicate_Ignore_Domain_Ids:
            Parallel_Mode: conservative
                SQL_Delay: 0
      SQL_Remaining_Delay: NULL
  Slave_SQL_Running_State: init

Any ideas?


texts: How do I write a query that gives me 2 results from the same table?

texts:
I wonder if anyone can help me with this at all, I'm a complete novice here! The call center I manage uses FreePBX as a phone system and try as I might I have not been able to find a solution for a dashboard for outbound call centers that doesn't cost the Earth.
I was recommended to use Reportico as a solution and the below was written for that provides:

SELECT CASE WHEN
SUBSTR(channel, INSTR(channel,'/')+1,3)=200 THEN 'Chrissie' WHEN SUBSTR(channel, INSTR(channel,'/')+1,3)=202 THEN 'Sam M' WHEN SUBSTR(channel, INSTR(channel,'/')+1,3)=205 THEN 'Leo' WHEN SUBSTR(channel, INSTR(channel,'/')+1,3)=208 THEN 'Callum' WHEN SUBSTR(channel, INSTR(channel,'/')+1,3)=209 THEN 'Sam D' ELSE 'Other' END USER, COUNT(uniqueid)
Total_Connected_Calls, SEC_TO_TIME(SUM(duration))
Call_Duration, SEC_TO_TIME(TIMESTAMPDIFF(SECOND, MIN(calldate), MAX(calldate)) - SUM(duration))
Total_Idle_Time, SEC_TO_TIME(ROUND((TIMESTAMPDIFF(SECOND, MIN(calldate), MAX(calldate)) - SUM(duration))/ COUNT(uniqueid))) Avg_Idle_Time
FROM cdr
WHERE DATE(calldate) = CURDATE() AND disposition = 'ANSWERED' AND (SUBSTR(channel, INSTR(channel,'/')+1,1) = 2 OR SUBSTR(channel, INSTR(channel,'/')+1,3) = 113)
GROUP BY SUBSTR(channel, INSTR(channel,'/')+1,3)

What I would like is additional column to show the total dials made so that I have a table showing

User
Total Dials
Total Connected Calls
Total Idle Time
Avg Idle Time

I have tried to remove the condition AND disposition = 'ANSWERED' which gives me the total dials for the day, but I can't figure out how then to get the total number of answered calls as well!
Any assistance with this would be greatly appreciated.
Thanks


texts: drop table in a begin try begin catch block - is there any loss of performance?

texts:
The code below is a dynamic sql that will give you a list of all alerts in your system.

It works.

There is a temp table in the code for reasons beyond this question.

This question is related to the way I make sure the temp table does not exist, before I create it.

I put a drop table in a try catch block as you can see in the code below.

My questions are:

Is there any loss of performance?
Any disadvantage in doing this?
Why is this not considered generally best practices?

I still have some servers sql-server-2008-r2 but mostly my servers are sql server 2016.

EXEC Sp_executesql
N'

begin try
 drop table #tmp_sp_help_alert
end try
begin catch
end catch

create table #tmp_sp_help_alert  (
id int null, name nvarchar(128) null,
event_source nvarchar(100) null,
event_category_id int null,
event_id int null,
message_id int null,
severity int null,
enabled tinyint null,
delay_between_responses int null,
last_occurrence_date int null,
last_occurrence_time int null,
last_response_date int null,
last_response_time int null,
notification_message nvarchar(512) null,
include_event_description tinyint null,
database_name nvarchar(128) null,
event_description_keyword nvarchar(100) null,
occurrence_count int null,
count_reset_date int null,
count_reset_time int null,
job_id uniqueidentifier null,
job_name nvarchar(128) null,
has_notification int null,
flags int null,
performance_condition nvarchar(512) null,
category_name nvarchar(128) null,
wmi_namespace nvarchar(max) null,
wmi_query nvarchar(max) null,
type int null)

insert into #tmp_sp_help_alert
exec msdb.dbo.sp_help_alert

SELECT * FROM #tmp_sp_help_alert AS tsha --WHERE (tsha.name=@_msparam_0)
--drop   table #tmp_sp_help_alert

',
    N'@_msparam_0 nvarchar(4000)',
    @_msparam_0=N'SQLPROD2 Alert - AG Role Change'


texts: My Container database (CDBROOT) is showing as read-write mode and while my pluggable database is showing as mount-state. Does that mean the application is using the container database and not the pluggable? Also, is it possible to move back to the non-container database architecture?


texts: ERROR 2026 (HY000): SSL connection error: Unable to get private key

texts:
I need help with mysql replication with ssl. I got a problem when i'm turn on ssl on my slave - error connecting to master 'replica@1.2.3.4:3306' - retry-time: 60  retries: 1

Here is my setup

Master

mysql> show variables like "%ssl%";
+---------------+--------------------------------------+
| Variable_name | Value                                |
+---------------+--------------------------------------+
| have_openssl  | YES                                  |
| have_ssl      | YES                                  |
| ssl_ca        | /etc/mysql/ssl-mysql/ca-cert.pem      |
| ssl_capath    |                                      |
| ssl_cert      | /etc/mysql/ssl-mysql/server-cert.pem |
| ssl_cipher    |                                      |
| ssl_key       | /etc/mysql/ssl-mysql/server-key.pem  |
+---------------+--------------------------------------+

Certs created on Gentoo with OpenSSL 1.0.2d    2015-07-09

Here's the interesting part. if I connect from Ubuntu 12.04 client instance specifying a client key, I get the standard error:

$ mysql -h 1.2.3.4 --ssl-ca=/etc/mysql/ssl-mysql/ca-cert.pem --ssl-cert=/etc/mysql/ssl-mysql/client-cert.pem --ssl-key=/etc/mysql/ssl-mysql/client-key.pem -u root -p
Enter password:
ERROR 2026 (HY000): SSL connection error

In logs

SSL error: Unable to get certificate from '/etc/mysql/ssl-mysql/client-cert.pem'
[ERROR] Slave I/O: error connecting to master 'replica@1.2.3.4:3306' - retry-time: 60  retries: 1, Error_code:2026

However, if I only specify the CA certificate and do not specify a client key or certificate, I can connect correctly with SSL:

mysql -h 1.2.3.4 --ssl-ca=/etc/mysql/ssl-mysql/ca-cert.pem -u replica -p

mysql> \s
--------------
mysql  Ver 14.14 Distrib 5.6.27, for Linux (x86_64) using  EditLine wrapper

Connection id:      6873
Current database:
Current user:       replica@1.2.3.5
SSL:            Cipher in use is DHE-RSA-AES256-SHA


texts: What are System Dynamics Variables on MariaDB?

texts: I'm a nweeby and i really didn't understand what is a Dynamic Variable. I didn't found on documentation. And really what is the concept of Dynamic Vatiables.


texts:
My table:

Date       Employee     Status
-----------------------------
20171106   001          At work
20171107   001          Sick leave
20171108   001          At work
20171109   001          At work
20171111   001          Sick leave (A gap here)
20171112   001          Sick leave
20171115   001          At work (Another gap)
20171116   001          At work

Desired result:

Employee      Status        StartDT                       EndDT
-------------------------------------------------------------------
001           At work       Some time in the history      20171106
001           Sick leave    20171107                      20171107
001           At work       20171108                      20171109
001           Sick leave    20171111                      20171112
001           At work       20171115                      20171116

The logic: We re-organize the source table by status, not by date. So a gap in date does not matter and should be ignored.

How to do this in Teradata 15?

Note: select min(Date), max(Date) group by employee, status; will not work, as there may be status change between two "At work"s.


texts: Get another function to return timestamp in PostgreSQL

texts:
Can I have another function to return the last timestamp.
I want to compare with this query to use the best query.
My query:
CREATE OR REPLACE FUNCTION f_last_update1(nomTable text , OUT updated timestamp)
  LANGUAGE plpgsql AS
$func$
BEGIN
   EXECUTE 'SELECT max(updated) FROM ' || nomTable
   INTO updated;
END
$func$;


texts: Postgres: find all FK constraints associated with a given PK

texts:
Given some input PK column, e.g. ani.animals.id, I want to find all FK constraints names associated with that PK.
I also need table and schema's names where each particular FK constraint is located.

So basically I am looking for all tuples ( schema_name.table_name, FK_constraint_name ) such that FK_constraint_name is referencing input PK.
As an example, the input PK ani.animals.id has:

fk_vet_animal in table ani.vets
fk_person_animal in table usr.people

Any ideas how to tackle it?
My ultimate goal is to, given some PK, drop all the found FK constraints, and re-create them with ON UPDATE CASCADE.


texts: Working out Duplicated Data in a Customer Data Base

texts:
I have a database in SQL2008 of customers, There is an ID number for the customer. What im trying to do is to create a NEWID field.

We have duplicated customers that have different ID fields, I am combining the Surname and Postcode to find the duplicated customers.

As you can see in the image with an example, i need to do the following.

1.Check if PostSur field have a match,

2.Check if there is more than 1 IDNO,

3.Check the next IDNO against the First IDNO and if there is a unique entry in the Name field. Then populate that record with the First IDNO in a new field called NewCode.

4.Otherwise the Next IDNO populate the NewCode with 'Dupe'

5.Populate all other IDNO in the NewCode also.

So i can then hide the Dupes and i should then have unique customers using NewCode.

I have the following SQL which uses ROW_NUMBER but cant figure out how to do the above with it?

   select *
 from (
  select *,
      row_number() over (partition by PostSur  order by Postsur) as rowID
 from Aron_Reporting.dbo.Customer_NewSort
   ) as rowID
 order by PostSUr

any help would be great


texts: How to reference a UNIQUE KEY CONSTRAINT in parent table as a foreign key in child table?

texts:
I have created two tables, the parent in this case being "department" and "employee" being child.

CREATE TABLE department
(
  department_id NUMBER(4) CONSTRAINT department_id_not_null NOT NULL,
  department_name VARCHAR2(30) CONSTRAINT department_name_not_null NOT NULL,
  location_id NUMBER(4),
  manager_id NUMBER(6),
  CONSTRAINT manager_unique UNIQUE (manager_id),
  CONSTRAINT department_pk PRIMARY KEY (department_id)
);

CREATE TABLE employee
(
  employee_id NUMBER(6)  CONSTRAINT employee_id_not_null NOT NULL,
  first_name VARCHAR2(20),
  last_name VARCHAR2(25) CONSTRAINT last_name_not_null NOT NULL,
  email VARCHAR2(25) CONSTRAINT email_not_null NOT NULL,
  phone_number VARCHAR2(20),
  hire_date date CONSTRAINT hire_date_not_null NOT NULL,
  job_id VARCHAR2(10) CONSTRAINT job_id_not_null NOT NULL,
  salary NUMBER (8,2),
  commission_pct NUMBER(2,2),
  manager_id NUMBER(6),
  department_id NUMBER (4),
  CONSTRAINT manager_unique FOREIGN KEY (manager_id) REFERENCES department (manager_id),
  CONSTRAINT department_fk FOREIGN KEY (department_id) REFERENCES department (department_id),
  CONSTRAINT employee_pk PRIMARY KEY (employee_id)
);

The problem is that whenever I try to create the second table "employee" it gives me this error:

  ORA-02270: no matching unique or primary key for this column-list

I haven't been sure about how to reference the unique key in the child table so I also tried using the UNIQUE constraint instead of the FOREIGN KEY constraint but to no avail. The table is created fine without the unique key reference though.


texts: How I prevent deadlock occurrence in my application?

texts:
I am developing an LMS application in PHP framework(Codeigniter 2.1.0). I am using MySQL database.
All the tables in the database have innodb engine. I also created indexes on each tables.
Now I am doing load testing using Jmeter version 2.9 locally for 200 users concurrently. During the load testing, in a specific page action I got Deadlock Found error. I changed my original query to the new one but again same error is occurring.

I have written save_interactions function which takes four parameters interaction array,module_id,course_id,user_id & is been called so many times by the AJAX script.
The following script inserts the record if the specific interaction_id is not present in that table otherwise the update query will get fire.

public function save_interactions($interaction_array,$modid,$cid,$uid)
{
    foreach($interaction_array as $key=>$interact_value)
    {
        $select_query = $this->db->query("SELECT COUNT(*) AS total FROM `scorm_interactions` WHERE `mod_id`='".$modid."' AND `course_id`='".$cid."' AND `user_id`='".$uid."' AND `interaction_id`='".$interact_value[0]."'");
        $fetchRow = $select_query->row_array();

        if($fetchRow['total']==1)
        {
            $update_data = array(
                        "interaction_type"=>$interact_value[1],
                        "time"=>$interact_value[2],
                        "weighting"=>$interact_value[3],
                        "correct_response"=>$interact_value[4],
                        "learner_response"=>$interact_value[5],
                        "result"=>$interact_value[6],
                        "latency"=>$interact_value[7],
                        "objectives"=>$interact_value[8],
                        "description"=>$interact_value[9]
            );
            $this->db->where('mod_id', $modid);
            $this->db->where('course_id', $cid);
            $this->db->where('user_id', $uid);
            $this->db->where('interaction_id', $interact_value[0]);
            $this->db->update('scorm_interactions', $update_data);
        }else
        {
            $insert_data = array(
                        "user_id"=>$uid,
                        "course_id"=>$cid,
                        "mod_id"=>$modid,
                        "interaction_id"=>$interact_value[0],
                        "interaction_type"=>$interact_value[1],
                        "time"=>$interact_value[2],
                        "weighting"=>$interact_value[3],
                        "correct_response"=>$interact_value[4],
                        "learner_response"=>$interact_value[5],
                        "result"=>$interact_value[6],
                        "latency"=>$interact_value[7],
                        "objectives"=>$interact_value[8],
                        "description"=>$interact_value[9]
            );
            $this->db->insert('scorm_interactions', $insert_data);
        }
    }
}

I got this type of error:

Deadlock found when trying to get lock; try restarting transaction

UPDATE `scorm_interactions` SET
    `interaction_type` = 'choice',
    `time` = '10:45:31',
    `weighting` = '1',
    `correct_response` = 'Knees*',
    `learner_response` = 'Knees*',
    `result` = 'correct',
    `latency` = '0000:00:02.11',
    `objectives` = 'Question2_1',
    `description` = ''
WHERE
    `mod_id` =  '4' AND
    `course_id` =  '5' AND
    `user_id` =  '185' AND
    `interaction_id` =  'Question2_1'
;

Filename: application/models/user/scorm1_2_model.php Line Number: 234

Can anyone please suggest me how to avoid Deadlock?


texts:
A coworker of mine was having an issue with BIDS. He's doing a simple fuzzy lookup and is only looking against two rows (For Testing Purposes). The weird thing is that when he tries to execute a command line prompt appears then vanishes and then the package hangs.

So I had him send me the package and decided to run it myself and found the exact same thing happening?

It will essentially sit like that forever. No error messages or anything. The last message it gives me is this.

[SSIS.Pipeline] Information: Execute phase is beginning.

Now this might be better placed on Superuser or something because I have a hunch as to what the problem is. Our computers are locked down to a frustrating extent. There are several issues I've run into with botched installs and software not working correctly.

If you have any ideas for what's happening or how to fix it let me know.

Update: Tried installing this patch to no avail. Doesn't work in 32 bit or 64 bit or as Admin.

Also followed the output and the end is this. SSIS package "Package1 (1) (1).dtsx" finished: Canceled.


texts: Why is there no predicate applied to the Index Scans?

texts:
We're having problems with some queries which are similar to this:

SELECT COUNT('A')  FROM  [dbo].[OINV] T0
INNER  JOIN [dbo].[OCRD] T2  ON  T2.[CardCode] = T0.[CardCode]
WHERE T0.[CardCode] = (@P2)  OR  T2.[FatherCard] = (@P3)

Query Plan

The indexes that it's hitting are defined as:

NONCLUSTERED INDEX [OCRD_FATHER] ON [dbo].[OCRD]
(
    [FatherCard] ASC
) INCLUDE (CardCode)

NONCLUSTERED INDEX [OINV_CUSTOMER] ON [dbo].[OINV]
(
    [CardCode] ASC
)

They're currently taking 1-2 seconds to run, and returns a count of 0 (which is what we're expecting).

I'm incredibly surprised that it's not filtering the NonClustered Indexes before it feeds into the Hash Match - it's feeding every single row.
These are vendor-software queries, so unfortunately there's no way for us to rewrite them.

Why is this, and is there any way to change it to filter before it does the Hash Match without rewriting the query?

Example Data Setup

CREATE TABLE [dbo].[OCRD]
  (
     [FatherCard] NVARCHAR(50),
     [CardCode]   NVARCHAR(50)
  );

INSERT INTO [dbo].[OCRD]
SELECT TOP (2076000) NEWID(),
                     NEWID()
FROM   master..spt_values v1,
       master..spt_values v2,
       master..spt_values v3

CREATE TABLE [dbo].[OINV]
  (
     [CardCode] NVARCHAR(50)
  )

INSERT INTO [dbo].[OINV]
SELECT TOP (5175460) NEWID()
FROM   master..spt_values v1,
       master..spt_values v2,
       master..spt_values v3

CREATE NONCLUSTERED INDEX [OCRD_FATHER]
  ON [dbo].[OCRD] ( [FatherCard] ASC )
  INCLUDE (CardCode)

CREATE NONCLUSTERED INDEX [OINV_CUSTOMER]
  ON [dbo].[OINV] ( [CardCode] ASC )


texts: MySQL - deleted IBD file, now my DB says 'Tablespace is missing for table`

texts:
I tried to free space of a cache table and I removed the IBD file of one of my tables, and I'm unable to recreate it now - 'Tablespace is missing for table ``.output_cache.'
Currently, it's corrupted, I tried ALTER,DROP,OPTIMIZE, all failed with the error of Tablespace is missing.
Any idea how I can re-create the table in order to fix it?


texts:
I have one issue in my live server, I created a job to shrink the log file and I have scheduled it to run every day with sa user

When I run this job manually from Management Studio, it is run successfully and shrinks the log file of database.

I have also checked the history for this job and it is running every day as per the scheduled time, but the issue is that that when it is run as per schedule that time it is run successfully but doesn't shrink the log file, the log file size stays the same size.

Can anyone help me to find what is the issue and how I can solve it?


texts: How do you correct a corrupted index in Oracle?

texts:
I have this ORA error in our logs:

Caused by: java.sql.BatchUpdateException: ORA-01578: ORACLE data block corrupted (file # 8, block # 22921) ORA-01110: data file 8: '/data/app/oracle/oradata/MYSRVR/datafile/o1_mf_mysrvr_88m82mdj.dbf'

I tried running this in sqlplus:

select segment_name,segment_type,tablespace_name, owner from sys.dba_extents where file_id=8 and 22921 between block_id and block_id + blocks -1;

output is:

SEGMENT_NAME             SEGMENT_TYPE   TABLESPACE_NAME     OWNER
-----------------------    ------------     ----------------    ---------------
PK_ZXCATSET_CATID      INDEX            MYSRVR_IDX_TB    MYSRVR

Now I am stuck on which index is that actual segment name. (I think :/ ) I tried doing this to rebuild that index:

SQL> alter index PK_ZXCATSET_CATID rebuild tablespace MYSRVR_IDX_TB;

ERROR at line 1: ORA-01418: specified index does not exist

Even though if I do "select index_name from dba_indexes", that index name shows up. What am I doing wrong? This is a 10.2 Oracle server.

Thanks in advance!

EDIT If I do MYSRVR.PK_ZXCATSET_CATID  the alter completes... but I still don't think my problem is fixed!


texts: Missing Non Clustered Index already part of Clustered Index

texts:
I'm debugging a slow running query and in the execution plan a non-clustered index is suggested, with 51.6648 Impact. However, the non-clustered index only includes columns that are already in the Primary Key (PK) Composite Clustered Index.

Could this be because of the order of the columns in the index? i.e. if the columns in the clustered index are not in order from most selective to least then is there potential for a non-clustered index to improve performance?

In addition the non-clustered index only contains two of the three PK columns with the third added as an included column. Is the include another reason why use of the non-clustered index could be more optimal?

Below is an example of the table structures I am working with:

Tables-

Retailers (
    RetailerID int PK,
    name ...)

Retailer_Relation_Types (
    RelationType smallint PK,
    Description nvarchar(50) ...)

Retailer_Relations (
    RetailerID int PK FK,
    RelatedRetailerID int PK FK,
    RelationType smallint PK FK,
    CreatedOn datetime ...)

The table Retailer_Relations has the following composite PK index and suggested index-

CONSTRAINT PK_Retailer_Relations
PRIMARY KEY CLUSTERED (
    RetailerID ASC,
    RelatedRetailerID ASC,
    RelationType ASC
    ) ON [PRIMARY]

CREATE NONCLUSTERED INDEX <NameOfIndex>
ON Retailer_Relations (
    RetailerID,
    RelationType
    )
INCLUDE (
    RelatedRetailerID
    )


texts:
I have a MySql database with a table that's about 20 Gb and about 2.5 million rows. At any one time, the max amount of users I have is probably around 300. The table is called 'content_versions'.

I have a piece of software built on top of it that allows writers to save content they write into this. When they save their content, it gets passed through a bunch of code that does various checks on it to remove editing marks and other various things. The initial draft of the content is then saved into a table called 'content' and then a trigger is fired to copy this initial draft into my 'content_versions' table. All subsequent edits and revisions are saved to the versions table. All good.

Recently, though, I've been running into this weird bug where the content is being cut off in random places. Sometimes it happens in the initial draft phase, sometimes it happens during revisions. It happens very infrequently, but enough that it's annoying. I've been looking through my code and can't see anything going wrong and can't duplicate the error.

But I have no idea what would cause this and not sure if it could be on the DB side of things. Is there some weird thing that could happen that would partially save a record into a db?


texts: SQL Server 2017 and TLS - client requirements

texts:
I am testing something around SSL\TLS and SQL Server 2017. I have enabled TLS on a test SQL Server instance by creating a self-signed cert and doing the required steps to add it to the Windows certificate store and then adding it to SQL Server in Configuration Manager. I have set also 'Force Encryption' to 'On', and restarted the service. So as far as I can ascertain that is enough to enable TLS on the SQL Server side.
For client applications on other computers connecting to the SQL Server instance I am confused as to what the connection string requirements are for connections using SqlClient. My understanding from reading up on it was that 'TrustServerCertificate=true' would have to be added to the connection string, but it seems possible to connect without that.
Is this because the certificate on the server is seen as being from a trusted authority?


texts:
I have a relatively large production environment (150GB) in SQL Server, and I need to duplicate it somewhere else for querying (specifically: I need to create an environment identical to the production so that queries are not hitting the live production environment - I used the word "duplicate" because I do not want to suggest a specific method).
Requirements:
1- the server has only 2 cores with 32GB RAM, so it's not very advanced hardware wise. Production environment is Standard SQL 2014, and receiving environment is Enterprise SQL Server 2017.
2- I need good latency (1 minute): this means that the delay cannot be more than a minute.
3- This is just a one-way duplication. I will not be making changes in the replica and expect it to end up in the production. This is just one-way duplication.
4- "live" connection (meaning changes made to the production must be sent back to the duplicate within 1 minute or so)
What I have done:
I thought of doing replication. But this is such a bad idea for a few reasons:
a) most tables (there are hundreds of them) do not have PKs. I understand that for replication you need PK.
b) there are so many tables, so it's hard to handpick them all for replication.
c) I understand that replication puts a lot of load on the production server. So a lighter version is better.
And then I looked at Snapshot. I was wondering if it's possible to use both, and if not, can snapshot alone be enough for above scenario? Any help/direction would be much appreciated.
NOTE: thank you for the recommendation that tables need PKs. We all know this, however, this is a vendor environment. Adding PKs is out of question, please consider that as a requirement as mentioned.


texts:
I have created an SSIS package for downloading file from FTP server. When I debug the package, everything goes fine. But when I shedule the package as Job step, it sometimes fails to connect. The job is sheduled to repeat every hour and in average every other run fails with Error: 0xC002918F - The Login request was denied.

The connection is anonymous and doesn't require password

I have tested it for a few hours now and found out some info:

When tested in FileZilla, I found out that the server denies second connection trying to download a file. Setting maximal number of concurrent connections to 1 got it working in FileZilla
FTP command in command line works fine. Maybe because it closes connection after every successful command
In FTP connection manager, I didn't found the option to limit number of connections

I have no possibility to see the settings of the FTP server.
What confuses me the most is the fact, that some sheduled tasks work fine and some fail. I haven't found any regularity in it.

What I have tried so far:

Active/pasive mode
Limiting retries in FTP connection manager to 1
Shifting the shedule time, so it doesn't collide with any other (older) tasks trying to download from the server
Setting the FTP task to ASCII mode (The server says to FileZilla, that it only accepts ASCII characters)

No luck so far. Still some attemps success and some not.

EDIT:

The FTP server is on Simatic device.

For this week, i kept shedule for every 1 hour and kept pinging the device every 30 seconds. Still some SSID attempts don't succeed and ping goes through every time :-/


texts: How to group time intervals by order of magnitude in Postgres?

texts:
I have a bunch of time intervals that I would like to analyze.  (Thousands of them.)  They range from 0 seconds up to several thousand days, but most of them are less than ten minutes.

I would like to group them by roughly how long they are.

I found that I can use the following to group them by the number of minutes (ignoring the seconds):

select count(*), date_trunc('minute', gap) as rounded from mytable
group by rounded order by rounded
;

This groups the majority of the results into the 0-9 minutes groups.

However, there are still hundreds of rows, because all of the odd amounts (e.g. 21 days 13 hours 58 minutes) are still unique when only truncated to the nearest minute.

What I would like would be to group the values more broadly—for instance, 0-10 minutes, then 10-59 minutes, 1-12 hours, 13-47 hours, 2-10 days, etc.  The exact groupings aren't particularly important, but the point is I want to round the times variably, something conceptually similar to rounding them all to two "significant digits."

How can I do this in Postgres?

(Note: The query doesn't have to be efficient; nor does it have to be simple, though simplicity is nice.  I'm using Postgres 10.)


texts: Updating TNS Entry across multiple terminals

texts:
Is there anyway I could update Host in a specific TNS entry, by way of an Batch File(sqlplus via command prompt)?

A change in the IP address have been raised and we need to update the TNS across several terminals. Any easy way to handle it rather than editing the TNSnames.ora file?

Your expertise in this is very much appreciated?


texts: Can AWS Data Migration Service be used to integrate non AWS SQL server with Redshift?

texts:
I have a SQL Server 2012 hosted on a standalone machine. I want to migrate it to my AWS Redshift (already existing data warehouse).

My question is wether it is possible via AWS Data migration service ?

I am also open to other efficient methods for migration. Currently I am doing the following steps

taking a backup of the SQL server DB in the standalone server.
uploading it to AWS-S3.
Droping and restoring the Db from S3 in AWS-RDS (Sql-server)

I would like this data to be present in my data warehouse i.e AWS-Redshift


texts: Backing up mongo database of about 120 GB size

texts:
We have a mongo database of about 120 GB size. I have run mongodump using nohup and redirecting the logs to /dev/null about 3 days back, but the dump file is ~40GB in size now, and the dump is still running. Is this expected?

If yes, what is the approximate compression ratio for a mongo database? i.e. for a 120 GB database, how much is the backup file size going to be?

This would help me in estimating the time remaining for the dump to finish. I have no clue why it is taking up so much time, also, wanted to know if there is a faster/better way of backing up the mongo database (remote copy is not something i'm considering)?

We are running this on the live system, but are currently not using this database. So, effectively, mongodump is the only client that mongod is serving.


texts: Log Shipping: two secondary DBs on the same instance - is this possible?

texts:
This is a bit insane, I'll explain intention later:

Scenario:
+ instance_1: one database (LS_source) configured as primary for log shipping
+ instance_2: two databases (dest_1, dest_2) as secondaries for LS_source (yes, this works!)

Problem:
There is one restore job on the secondary instance. Therefore both secondaries will be restored at the same point in time. We'd like to setup two restore jobs to have different schedules for dest_1 and dest_2.

Closer look: Restore executes sqllogship.exe -Restore secondary_id

Core of the problem: Both secondary databases have the same secondary_id, so sqllogship.exe will always restore both...

Any ideas how to get two restore jobs? Can the secondary_id be changed?

Thank you for your help!

Now the idea behind this strange scenario:
We'd like to use instance_2 for load balancing read only access (secondary is standby). While trn are restored, user cannot access it for ~90 seconds. This is not acceptable, 5 seconds would be ok.
If we got two secondaries restored at different times we could switch from one to another after restore completed. Both databases must be on the same instance, so we can have one database snapshot pointing to the active secondary => no need to change connection strings for all clients.
Transactional replication is no choice, impact is to high when replication stalls. AlwaysOn is not available on Standard Edition...


texts: MySQL looking up more rows than needed (indexing issue)

texts:
In our MySQL 5.5 database, we have following InnoDB table with 30M+ rows:

+----------------+-------------+-----------------+--------+
| participant_id | question_id | given_answer_id | status |
+----------------+-------------+-----------------+--------+
|       500      |      12     |        25       |    0   |
+----------------+-------------+-----------------+--------+

The combination participant_id + question_id + given_answer_id is unique.

At the moment we have following keys:

PRIMARY KEY (in this order)

participant_id
question_id
given_answer_id

INDEX KEY

question_id

For this table, we have two kinds of select queries in our application:

[...] WHERE participant_id = x AND question_id = y AND given_answer_id = z;

and

[...] WHERE question_id = x;

Normally, every participant_id has between 0 and <100 rows with different question_ids. The other way around, every question_id can have unlimited (usually not more than 100 000) rows with different participant_ids. The first query is executed way more often than the second one.

When we execute following query, it shows us that 32096 rows where looked up:

EXPLAIN SELECT * FROM example WHERE question_id = 500;

+----+-------------+-----------+------+---------------+-------------+---------+-------+-------+-------+
| id | select_type | table     | type | possible_keys | key         | key_len | ref   | rows  | Extra |
+----+-------------+-----------+------+---------------+-------------+---------+-------+-------+-------+
| 1  | SIMPLE      | example   | ref  | question_id   | question_id | 8       | const | 32096 |       |
+----+-------------+-----------+------+---------------+-------------+---------+-------+-------+-------+

Whereas, when we execute the same query without EXPLAIN only 18732 rows are returned.

What indexes do we need on this table in order to prevent this overhead but still performing for both kinds of queries?

Here's is the code to create this table:

CREATE TABLE `example` (
    `participant_id` BIGINT(20) UNSIGNED NOT NULL,
    `question_id` BIGINT(20) UNSIGNED NOT NULL,
    `given_answer_id` BIGINT(20) UNSIGNED NOT NULL,
    `status` TINYINT(3) UNSIGNED NOT NULL DEFAULT '0',
    PRIMARY KEY (`participant_id`, `question_id`, `given_answer_id`),
    INDEX `question_id` (`question_id`)
)
ENGINE=InnoDB;


texts: Is it possible to exclude specific users in log activity of PostgreSQL?

texts:
I need to monitor the activity of users in our databases. I have set the following parameters in postgresql.conf:

log_min_duration_statement = 0
log_connections = on
log_disconnections = on
log_line_prefix = '%t %a %d %h %u |'

However, I realize that most of the log files are filled with statements executed by the postgres user, which is used by scripts I wrote for maintenance tasks: recalculate materialized views, pg_dump, pg_restore, extract views as tabulated files, etc.
The result is daily log files having over 12 Mb in size.

Is there a way to exclude specific users' activity from the log?


texts: How to generate documentation from stored procedures?

texts:
I'm working with a new set of tables and an increasing list of stored procedures.

Is there a way to generate documentation out of the comments on tables and stored procedures, like autodoc or doxygen?

Note: I'm using mysql.


texts:
I have a table abc, in which one column name where some data is encoded and some are not encoded. When I run the decode query the exception is found. How to handle it. There is a large number of rows in this table.

create table abc
(
id serial primary key,
name varchar(500)
)

select decode(name,'base64') from abc;

  exception occurs: SQL state: 22023

Now how can I convert all non-encoded data to encoded? Or how can I handle this exception.


texts: How to call modify() on a nodelist in T-SQL?

texts:
I have a process step where I have an XML document in a SQL Server column and I need to alter the text of a node that could occur multiple times. Modify() with 'replace' DML only operates on single nodes. To complicate matters, the replacement needs to happen based on a lookup table maintained locally.

I can use nodes() to get a nodelist and contrive a query to join the data appropriately, but at that point, I no longer have a reference that modify() can act on.

It appears that my only choice is to create a CLR procedure or function and use some sort of dotnet native XML iteration. That's less appealing, since the business logic would be locked up in the source code, not visible in the procedure definition. Is there any way to do this transformation using the XML methods exclusively?


texts: Building a branched versioning model for relational databases

texts:
I am database designer and at my current project I'm implementing versioning capabilities required to concurrently edit rows of data in RDBMS. The project requirements says, that data editing sessions can go on for several hours or days until performing commit. Also, conflicts are arising during simultaneous modifying of the same data by different users should be handled with possibility of manual and semi-automatic resolution. In other words, desired editing workflow is similar to one used in document-oriented version control systems, such as SVN or Git. Therefore, traditional OLTP approaches and conflict resolution strategies (MVCC, optimistic/pessimistic locks) doesn't satisfy my constraints. I have done some observation of existing tools, that offer possibilities for branched version history and multiversion workflow:

ArcSDE - ESRI's ArcGIS supports versioning for geodatabases through ArcSDE data layer;
Oracle Workspace Manager - feature of Oracle Database, providing high degree of version isolation and data history management;
SQL:2011 temporal features, including valid time and transactional time support.

SQL:2011 doesn't solve my problem, as it offers support for "linear" history of edits, not branched I'm looking for. Solutions from ESRI and Oracle are good candidates, but I'm disappointed that both have vendor-specific interfaces for manipulating versions. It seems that at this moment nobody can offer industry standard solution for branched versioning of relational data (as SQL:2011 does for temporal tables and linear version history). As a newcoming database researcher, i want to understand:

is relational database community interested in developing standard models of branched data versioning and will any contribution or research in this area be valuable? (for example, standartization as it was done for temporal features in SQL2011 in the form of language improvements)
do developers and database designers lack for database-independent open-source middleware (similar to ArcSDE), that offers support for branched version management of relational data or it would be better to introduce such features in RDBMS itself?

I think I can try to dig deeper and propose some standard model or sublanguage to deal with Git-like versioning, but i don't know where to start.


texts:
I am not DBA, just use MySQL on local machine for testing my apllications.

I have MySQL server with Workbench and Notifier on Windows machine. MySQL Notifier periodically shows message "mysql database connections will be migrated" with possibility to click "No".
What does this message means? Why it wants to migrate connections?


texts: Allowing non-sysadmin users to see SQL Server execution plans

texts: Is this possible ? On SQL Server 2012+, grant access to a non sysadmin user to see the execution plans ? And can this be done for selected databases only ?


texts:
I have a few tables I'm going to create for a website, Literature, video, and image. Is there a good reason why I shouldn't use the file name as a primary key.

For instance...

"someimage.png" as the PK for table image.

The only downside I can think of is if I had named someimage.png in multiple directories and each directory someimage.png was not the same image.  This seems like poor design and there isn't any reason why I'd do this anyways.

Thoughts?


texts: I need to move 20 Gb MySQL database from on Debian server to another. The problem is that there is no way to create its backup since there are almost no free HDD space on the source server. What would you suggest?


texts: Triggers on Slave database (in the context of replication) for auditing purpose in MySQL

texts:
I have a requirement for auditing in MySQL database. It means I am interested in what data is changed in my database and when. For fulfilling this requirement I am thinking about the triggers. I am also using row based replication which ensures that data from master database is copied on slave machine. Since triggers might reduce the performance and it can introduce additional issues like row locking, I would like to avoid it on master database. Instead I would like to define triggers only on slave machine. Since data on slave will be identical with master, my requirement of auditing will be fulfilled also I can avoid performance and locking issues on master. Following are my environment details

Environment details

MySQL version 5.6 on windows
Master and Slave database with row based replication

So my questions are

Is it possible in MySQL to define the triggers only on slave?
Is this a correct approach?
Will it create any serious issue?


texts: SQL Server Index: One new table joining to three old tables, how to?

texts:
how can I index this?  In a legacy system, I need to store (new table) and search on three possible natural key combos.

I'm thinking my new table will be:

declare @TableA table (pk int identity(1,1) primary key
                      --.more columns
                      ,fk1    char(4) not null
                      ,fk2    char(2) not null
                      ,fk3    char(3) not null
                      ,fk_a1  char(5) null
                      ,fk_a2  char(2) null
                      ,fk_b1  char(6) null
                      ,fk_c1  char(1)
                      --more columns...

this new table needs to join to three different tables on these columns:

LegacyA: fk1, fk2, fk3, fk_a1, fk_a2
LegacyB: fk1, fk2, fk3, fk_b1
LegacyC: fk1, fk2, fk3, fk_c1

I'm thinking to add the following indices:

fk1, fk2, fk3, fk_a1, fk_a2
fk1, fk2, fk3, fk_b1
fk1, fk2, fk3, fk_c1

since I'll be searching and joining to LegacyA, LegacyB, LegacyC independently and separately, these indices should be used correct?  Does this look like a sound approach?


texts:
i have an use case in my application where i need real time communication between users based on their position. Basically one user will create an "event" and it will be sent to near users. On the server side i'm planning use redis or mysql to manage events and participants. Based on questions like this one i could configure mysql with:

innodb_flush_log_at_trx_commit = 2

and it increases write performance. Do you think that properly configured MySQL could compete with Redis?, what i like of MySQL is that i can use an ORM in front of it. Or maybe i'm missing something about the difference between Redis and MySQL.

Thanks in advance.


texts:
Currently, we have Solarwinds in place for monitoring our servers. I would like to know if we are okay to leave the default metrics for AlwaysOn as is or do we need to increase that a bit. The reason I am asking is because we are facing issues with one of our environments where I do not see any issue with latency but then it is reported in Solarwinds and triggers an alert. Please find the current values below. I tried searching google if I could get a specific recommendation on what these values should be, but could not find one.

LATENCY THRESHOLD – 10 seconds
WARN LOG SEND QUEUE SIZE – 5 MB
CRIT LOG SEND QUEUE SIZE – 10 MB
WARN REDO QUEUE SIZE – 5 MB
CRIT REDO QUEUE SIZE – 10 MB


texts:
For the life of me, I cannot get the MySQL for Visual Force connector to work in VS. I just want to be able to add a MySQL database as a database connection in VS so I can build an integration.

I have the community version of VS 2017. I have followed the instructions listed on the MySQL website - I have tried the MySQL Installer as well as the standalone download.

My first problems began because the MySQL installer would not recognize the installation of Visual Studio (even though I ALSO have 2013 installed!). So I uninstalled Visual Studio, the MySQL ODBC and .NET connectors, and I tried reinstalling them over and over again. Each time I would attempt to install the MySQL for Visual Studio connector, the installer would say that Visual Studio was not installed on my computer.

So then I installed the connector by itself, BEFORE installing the ODBC or NET connectors and I got it to install. I still could not get the MySQL connector to appear when I tried to connect a new database to VS, though.

Frustrated, I decided to create a User DSN (as opposed to a System DSN) and connect that way. It seemed to work, and I was able to see the connector in VS. However, now when I try to connect, I get "ERROR [IM014] [Microsoft][ODBC Driver Manager] The specified DSN contains an architecture mismatch between the Driver and Application".

I looked up the error, and it appears that there is a mismatch between the x86 and x64 drivers. The problem is that I cannot find a x64 driver for MySQL for VS.

I'm at the end of my rope. I just want to build out a package that will allow me to pull data from the MySQL and push to a SQL server. Is there a better way to do all of this, or am I on the right track? I would appreciate any assistance.


texts: Failed to insert records into table in different server using sql server agent

texts:
My idea is to insert records from a table into another one in different server using T-SQL.

I've made maintenance plan along with its job and found below error.

  Executed as user: NT SERVICE\SQLSERVERAGENT. Login failed for user
  'MANYAR\MIT-DB2$'. [SQLSTATE 28000] (Error 18456)Unable to open Step
  output file.  The step failed.

I suspect SQLSERVERAGENT from source server might not have permission in destination server.  I've tried to execute the T-SQL script in query and the scripts work well.


texts: How to find the number of rows changed by an UPDATE statement in Oracle?

texts:
Imagine the following table:

id | name | family
---+------+--------
 0 | John | Smith
 1 | Mary | Winters
 2 | John | Doe

and now the following statement:

UPDATE t SET name = 'John' WHERE id = 0;

This statement in fact would update nothing – as it reflects what already is there. Now all the blogs, manuals and whatsnots said to check SQL%ROWCOUNT for the number of updated rows. Doing that returns 1 (affected rows – i.e. those the WHERE clause did match). Let's have another one:

UPDATE t SET family = 'Smith' WHERE name = 'John';

De facto, that would only change the row with id=2, so I want something stating "1 row updated" – but SQL%ROWCOUNT gives us 2 (again, rows affected).

So here comes the final question: What gives the number of rows changed?


texts:
Clean installs of Cassandra 3.11 nodes are working fine for me for a few days, and then ‘nodetool flush’ and ‘nodetool compact’ are starting to throw exceptions. After this, restarts after a ‘nodetool drain’, fail. forcing deletion of commitlogs. Is this a problem with 3.11 in general? If What version do we need to downgrade to, to resolve this?

(I recognise that the causes might not be related to 3.11, ie perhaps or something about the key space I have, or perhaps an issue with Ubuntu 16? But I am just asking about 3.11 specifically and if it has some sort of known flaw threat I can’t see in JIRA)


texts: Is there a better way to add failed node back to postgres cluster?

texts:
There are 2 nodes in the cluster: node1(primary) and node2(replica).
I will shutdown node1 and promote node2 as primary.
I'll add the node1 back to the cluster (as a replica this time), so I am using the following procedure on failed node1:

systemctl stop postgresql-12
rm -rf /var/lib/pgsql/12/data/*
pg_basebackup -h node2 -D /var/lib/pgsql/12/data/ -U replicator -P -v  -R -X stream -C -S pgstandby1
systemctl start postgresql-12

Is there a better way? I don't like that I'm deleting everything from /var/lib/pgsql/12/data/, and it takes some time until restoring, especially when there's a lot of data.
What are your considerations gentleman?


texts: How do I send an array of strings to PG from PHP without the former becoming confused?

texts:
I want to do this, but for more than one string:
SELECT * FROM table WHERE label <> $1

That selects all records from table where the label column is different from a single string. It works.
But now I want to do the same, but instead of just checking a single string, I have an array of strings in PHP, [ 'ignore string 1', 'ignore string 2', 'ignore string 3' ],  which I wish to have excluded.
If I just send an array of strings to that same query for the $1, it gives errors. I've tried a lot of things now. I even attempted:
SELECT * FROM table WHERE label NOT IN $1

But that doesn't work for an application-provided array of strings.
Do I simply need to do ::array to convert it or something? I couldn't get it to work either.
Yes, it's most common to have a different table in the database with "ignore phrases", but in this case, I need to provide a list of such strings as an array from the application.


texts: SSMS gives error if column name is "MERGE" where as other keywords works

texts:
IN SSMS create table if we include sql keywords in column name then it will be wrapped around square brackets "[]" automatically and the table gets created without any error generally.

But if we include some keywords such as merge then SSMS will not insert square brackets around it and while saving table error is shown as

'Table_2' table
- Unable to create table.
Incorrect syntax near the keyword 'MERGE'.
Incorrect syntax near the keyword 'NULL'.


texts: MySql - selecting into file and re-loading it with special characters

texts:
I have a MySql table called twitter_statuses which as the title suggests holds twitter statuses.  here is the table structure.

this table contains all sort of characters in the text column, and some of the column holds Ruby object which are multi lined.

Im trying to select some of the records into a file and then load them to a new table but because of all the special characters some of the rows get "messed up".

here is the query I use to dump the statuses

here is the query I use to re-load the dumped statuses

As you can see I tried replacing the commas in the text column with **** to prevent them from splitting the fields, and replaced the new line in the multi line columns (urls, hashtags, user_mentions) to prevent "cutting the line" when dumping the file to CSV.

currently the multi line fields load great (the replacement of the new line character works) my problem id with other characters that appear in other fields (all sort of character) with cause to split columns.

any ideas on what could I change there?


texts: Postgresql delete taking lots of CPU and time

texts:
I have the following SQL Query for deleting rows from my table. The query takes a long time and puts a high load on my server.

delete from message_log where from_id = ? OR to_id = ?

My table Schema

-- Table: message_log

CREATE TABLE message_log
(
  id serial NOT NULL,
  message text,
  from_id character varying(500),
  to_id character varying(500),
  match_id character varying(500),
  unix_timestamp bigint,
  own_account boolean,
  reply_batch boolean DEFAULT false,
  CONSTRAINT message_log_pkey PRIMARY KEY (id),
  CONSTRAINT message_log_message_from_id_to_id_match_id_unix_timestamp_key UNIQUE (message, from_id, to_id, match_id, unix_timestamp)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE message_log
  OWNER TO t;

CREATE INDEX delete_index
  ON message_log
  USING btree
  (from_id COLLATE pg_catalog."default", to_id COLLATE pg_catalog."default");

CREATE INDEX message_log_from_id_to_id_unix_timestamp_idx
  ON message_log
  USING btree
  (from_id COLLATE pg_catalog."default", to_id COLLATE pg_catalog."default", unix_timestamp);

CREATE INDEX message_log_match_id_unix_timestamp_idx
  ON message_log
  USING btree
  (match_id COLLATE pg_catalog."default", unix_timestamp);

I already have an index on from_id and to_id. Is there anything I can do to speed the delete process up? I am running Postgres 9.1

EDIT

I tried the explain query

EXPLAIN delete from message_log where from_id = '55fc5c2c2a0defed5b643d40' OR to_id = '55fc5c2c2a0defed5b643d40'

Three rows are returned

1. "Delete on message_log  (cost=0.00..89443.96 rows=96 width=6)"
2. "  ->  Seq Scan on message_log  (cost=0.00..89443.96 rows=96 width=6)"
3. "        Filter: (((from_id)::text = '55fc5c2c2a0defed5b643d40'::text) OR ((to_id)::text = '55fc5c2c2a0defed5b643d40'::text))"


texts: Building Timeline Feature with Complex Post Object Via Mongo and Redis

texts:
So I have a scenario where I want to build a timeline for an app. I haven't done this particular thing before, but since I work mostly in mongo my first instinct was to query to get the user's following array, then search for posts only by those authors and then sort/limit accordingly. So, using Meteor's syntactic sugar, it would be roughly something like:

Posts.find({author: {$in: followingArray}}, {sort: {date: -1}})

However it is easy to see that this would scale poorly as you have to first query for the user's following array, then search through posts and pull every matching author, then sort it. A more natural way to do this would be to use mongo for general storage, but leverage redis for its ease of use and speed in constructing timeline like apps.

But I can't fully use redis because the data structures of the post object are too complex (they're objects within objects that have arrays, etc.).

So I thought that one possible solution would be to store the _ids to the Posts in a redis list (along with separate structures for followers/following), retrieve that list, which will already be updated and ordered thanks to lpush and then search mongo for the relevant Post objects as follows:

Posts.find({_id: {$in: redisTimelineList})

Which will return all the posts that I want to push to the client. The idea behind this is that it cuts out having to query for following and searching by indexed _ids as opposed to searching by following and then order the results (limiting and sort come for free with lrange and lpush). I would have to build a more complex mongo query to ensure that the redisTimelineList returns in the same order it started out as, but this seems like an overall better solution.

Is this actually a better way of handling the issue?


texts: Two relations to same table in SQL Server

texts:
I've got two tables. When I delete a row in Table1, I want to set the ID of that row in all the rows of Table2 to NULL. I've got two relationships on Table1.

The problem is: I can only set one delete rule: Set NULL. If I try to set the other it fails. But when I try to delete ID 1 from Table1, this now also fails.

My table data looks like the following:

Table1:

ID  Text
1   Hoi
2   Hoi

Table2:

ID  ForeignID1  ForeignID2
1   1   2
2   1   1

How do I need to set my constraints, so it sets the Foreign IDs to NULL on Table2 when I delete the row in Table1?


texts: Get max value when joining 3 tables in SQL

texts:
This is based on a MySQL database.
I'm trying to join 3 tables together and get a MAX value from the third one. I'm trying to get the po.id, for each part.id, that has the latest po.date. I cannot base it on the MAX po.id. It must be based on the MAX po.date, which is what I'm stuck on. However, I need to used the poitem table to determine if the po contains the part.
Table: po
po.id, po.date
Table: poitem
poitem.id (item id that is only used for joining), poitem.partid
Table: part
part.id
This is what I'm trying to end up with:
part.id                po.id
.....                  .....
.....                  .....
.....                  .....


texts:
Is there an ANSI standard for auto-numbered columns.

Currently, we have a choice of SERIAL, AUTOINCREMENT, AUTO_INCREMENT, IDENTITY() and good old NEXTVAL() among others.

I read somewhere that there is a new standard using IDENTITY which I know that Oracle has recently implemented. I know that Oracle is hardly the last word on standards.

If there is a standard, then it’s a long time coming.


texts: Oracle Migration Upgrade 11g to 12c New Server

texts:
I'm fairly new to Oracle databases and have been tasked with assessing the implications of upgrading our application's database from 11g to 12c.

I have managed a successful in-place upgrade but a number of stakeholders will be looking to upgrade to new hardware so I need to access the viability of a migration upgrade.

Is it possible to migrate the current active database to a fresh installation of 12c and which would be the most effective method for this?


texts: Difference between oracle database desktop and server class

texts:
I installed Oracle database 11g enterprise edition to my laptop. When I was installing Oracle, at the beginning I selected Desktop Class (not server class) option.

Now I am trying to make remote connection to my Oracle database from another PC but I cannot connect to Oracle and get this error message:

  "ORA-12545: Connect failed because target host or object does not exist"

even though all Oracle services have been already started.

May the reason be why I installed Oracle as desktop class instead of server class?

My tnsnames.ora file:

ORACLR_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
    (CONNECT_DATA =
      (SID = CLRExtProc)
      (PRESENTATION = RO)
    )
  )

DERS =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST =localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = Ders)
    )
  )


texts: How can I successfully decrypt column data through the whole database safely?

texts:
We're moving to Azure SqlDb but as part of that, we're needing to migrate away from  EncryptByPassPhrase as it's not supported.

As such, we tried the process on the weekend but we ran into the log filling up and the script stopping midway through.

What are my options here? There will be zero usage of the DB during the process, can/should I change the DB to simple from full recovery model temporarily? I don't really think we care about the individual transactions during the process, just the end result.

Also, given we're doing a compute intensive task - should I bump up the CPU resources temporarily to hopefully make it go faster? It took 9+ hours to fail, I'm wondering if the log interfered.

This is:

SQL 2019 (15.0.2000.5)
DB compat is 150
We've disabled all triggers/jobs during this process

Here's part of the script:

DECLARE @Password VARCHAR(255) = '<some super cool long very high entropy password here>';

SET NOCOUNT ON;

DECLARE @TestDecryption VARCHAR(255);
SELECT TOP(1)@TestDecryption = CONVERT (VARCHAR(50), DECRYPTBYPASSPHRASE (@Password, HealthNumber_Enc))
FROM Patients.Patient
WHERE ProvinceCode = 'ON'
      AND HealthNumber_Enc IS NOT NULL
ORDER BY CreateDate DESC;
PRINT 'Test: ' + @TestDecryption;

IF @TestDecryption IS NOT NULL -- this is to ensure that our password actually works
BEGIN
    ; DISABLE TRIGGER ALL ON DATABASE;

    -- systematically decrypt data in all affected tables
    UPDATE Users.Activity
    SET [Description] = ISNULL (CONVERT (VARCHAR(1000), DECRYPTBYPASSPHRASE (@Password, Description_Enc)), [Description]);
    UPDATE Users.Activity
    SET Description_Enc = NULL;

    UPDATE Contact.[Address]
    SET Address1 = ISNULL (CONVERT (VARCHAR(1000), DECRYPTBYPASSPHRASE (@Password, Address1_Enc)), Address1), Address2 = ISNULL (CONVERT (VARCHAR(1000), DECRYPTBYPASSPHRASE (@Password, Address2_Enc)), Address2), Address3 = ISNULL (CONVERT (VARCHAR(1000), DECRYPTBYPASSPHRASE (@Password, Address3_Enc)), Address3), City = ISNULL (CONVERT (VARCHAR(255), DECRYPTBYPASSPHRASE (@Password, City_Enc)), City), PostalCode = ISNULL (CONVERT (VARCHAR(20), DECRYPTBYPASSPHRASE (@Password, PostalCode_Enc)), PostalCode), Latitude = ISNULL (CONVERT (DECIMAL(9, 6), CONVERT (VARCHAR(10), DECRYPTBYPASSPHRASE (@Password, Latitude_Enc))), Latitude), Longitude = ISNULL (CONVERT (DECIMAL(9, 6), CONVERT (VARCHAR(10), DECRYPTBYPASSPHRASE (@Password, Longitude_Enc))), Longitude);
    UPDATE Contact.[Address]
    SET Address1_Enc = NULL, Address2_Enc = NULL, Address3_Enc = NULL, City_Enc = NULL, PostalCode_Enc = NULL, Latitude_Enc = NULL, Longitude_Enc = NULL

    -- Some 50 other tables after here ..

    ;ENABLE TRIGGER ALL ON DATABASE;
END;
ELSE BEGIN
PRINT 'Test Decryption returned NULL. Will not proceed!!';
END;

Errors looked like:

Msg 9002, Level 17, State 2, Procedure TRIG_Contact_Address_UPDATE_1, Line 26 [Batch Start Line 0]
The transaction log for database 'YourExampleDb' is full due to 'LOG_BACKUP'.