texts: Selecting Records In SQL Server From Joined Table Where Timestamp Occurs Between Begin/End Range

texts:
I have 2 tables

Table1 contains RecordID, UserID, Event, EventTimestamp

Table2 contains UserID, UserStatus, UserStatusSubgroup, StatusBeginTimestamp, StatusEndTimestamp

Table1 --SQL Server 2014 Table--

RecordID [char(18)]
UserID [nvarchar(50)]
Event [nvarchar(20)]
EventTimestamp [DateTime2(3)]

Table2 --SQL Server 2014 Table--

UserID [nvarchar(50)]
UserStatus [nvarchar(10)]
UserStatusSubgroup [nvarchar(20)]
StatusBeginTimestamp [DateTime2(3)]
StatusEndTimestamp [DateTime2(3)]

Primary Key is UserID

For each RecordID in Table1 there can be many Events performed by the UserID with the EventTimestamp recorded

I wish to join to Table2 to retreive Table2.UserStatus and Table2.UserStatusSubgroup where Table1.EventTimestamp occurs between Table2.StatusBeginTimestamp and Table2.StatusEndTimestamp

How can I do this?

--Setup demo data
Declare @Table1 table
    (RecordID int, UserID varchar(5), Event varchar(20), EventTimestamp DateTime2(3))
;

INSERT INTO @Table1
    (RecordID, UserID, Event, EventTimestamp)
VALUES
    (123456, 'Bob', 'EventA', '2018-05-30 19:01:29.683'),
    (123456, 'Bob', 'EventB', '2018-05-30 19:02:25.893'),
    (123456, 'Bob', 'EventC', '2018-05-30 19:03:15.323'),
    (123456, 'Bob', 'EventD', '2018-05-30 19:04:45.013'),
    (123456, 'Bob', 'EventE', '2018-05-30 19:05:12.993'),
    (123456, 'Null', 'EventF', '2018-05-30 19:06:00.999'),
    (123456, 'Null', 'EventG', '2018-05-30 19:07:00.002')

;

| RecordID  | UserID | Event      | EventTimestamp          |
|-----------|--------|------------|-------------------------|
| 123456    | Bob    | EventA     | 2018-05-30 19:01:29.683 |
| 123456    | Bob    | EventB     | 2018-05-30 19:02:25.893 |
| 123456    | Bob    | EventC     | 2018-05-30 19:03:15.323 |
| 123456    | Bob    | EventD     | 2018-05-30 19:04:45.013 |
| 123456    | Bob    | EventE     | 2018-05-30 19:05:12.993 |
| 123456    | Null   | EventF     | 2018-05-30 19:06:00.999 |
| 123456    | Null   | EventG     | 2018-05-30 19:07:00.002 |

--Setup demo data
Declare @Table2 table
    (UserID varchar(5), UserStatus varchar(10), UserStatusSubgroup varchar(20), StatusBeginTimestamp DateTime2(3), StatusEndTimestamp DateTime2(3)

;

INSERT INTO @Table2
    (UserID, UserStatus, UserStatusSubgroup, StatusBeginTimestamp, StatusEndTimestamp)
VALUES
    ('Bob', 'StatusA', 'SubStatus1', '2018-05-30 19:00:00.000', '2018-05-30 19:03:00.000'),
    ('Bob', 'StatusB', 'SubStatus2', '2018-05-30 19:03:00.000', '2018-05-30 19:04:00.000'),
    ('Bob', 'StatusC', 'SubStatus3', '2018-05-30 19:04:00.000', '2018-05-30 19:06:00.000')
;

| UserID | UserStatus | UserStatusSubgroup   | StatusBeginTimestamp    |  StatusEndTimestamp     |
|--------|------------|----------------------|-------------------------|-------------------------|
| Bob    | StatusA    | SubStatus1           | 2018-05-30 19:00:00.000 | 2018-05-30 19:03:00.000 |
| Bob    | StatusB    | SubStatus2           | 2018-05-30 19:03:00.000 | 2018-05-30 19:04:00.000 |
| Bob    | StatusC    | SubStatus3           | 2018-05-30 19:04:00.000 | 2018-05-30 19:06:00.000 |


texts:
I am struggling to get my syntax correct for this update query.  I need to update Sanity.itemname based off employeecodes in SSL12.LBS  I can join to that table like so Sanity.cID = Helper1.cID and Helper1.LBS = SSL12.LBS but the issue I keep running into with my queries is that none of the tables hold true primary keys and my SWITCH() statement seems to be ignored and ALL cID are updated.

Below is sample DDL - can someone help me come to an update query that will only update the values in my SWITCH() statement, but ONLY for LBS that exist in SSL12

Create Table SSL12
(
   LBS int
  ,fname varchar(40)
) ;

Insert Into SSL12 (LBS, fname) VALUES
  (20, 'Blue'),
  (40, 'Red'),
  (60, 'Green') ;

Create Table Helper1
(
   cID int
  ,LBS int
  ,fname varchar(40)
) ;

Insert Into Helper1 (cID, LBS, fname) VALUES
  (2222, 20, 'Blue'),
  (4212, 40, 'Red'),
  (8132, 60, 'Green') ;

Create Table Sanity
(
   cID int
  ,itemname varchar(100)
) ;

Insert Into Sanity (cID, itemname) VALUES
  (2222, 'purple flower'),
  (2222, 'foghorn'),
  (2222, 'socks'),
  (4212, 'iguana'),
  (4212, 'camel'),
  (8132, 'red curtains') ;

And the UPDATE:

UPDATE [SSL12]
    INNER JOIN (Helper1 INNER JOIN Sanity ON Helper1.cID = Sanity.cID)
    ON [SSL12].[LBS] = Helper1.LBS
SET Sanity.itemname = Switch("itemname"="purple flower", "lilac",
                             "itemname"="red flower", "rose",
                             "itemname"="yellow flower", "daisy");


texts: sp_tables blocking my other statements. Who runs it?

texts:
In our application we sporadically experience a locking issue caused by the execution of an sp_tables statement.

We use SQL Server 2012. In our code and queries in our stored procedures we don't make use of sp_tables.

How can I trace who is the responsible of this call? At least how can I log when this call happens?

I read that it's also called by third parties, in this case how can I track who is responsible?

Edit:

in our application we use Hibernate as ORM and c3p0 for connection pooling.

EDIT 2:
we didn't succed in understanding when and by who the sp_tables was called but today we succeded in replicate the issue.

Seems that the sp_tables is called in responde to a failed delete tried on the schema.

The delete fails because there are other rows referencing the row that we try to delete, so we get an exception

SQLServerException: The DELETE statement conflicted with the REFERENCE constraint..

Is there evidence somewhere that this kind of errors triggers an sp_tables on the db?


texts: OLE DB provider "MSDASQL" for linked server returned message "ERROR: reserve exceeded : Out of memory "

texts:
We have SQL Server 2012 database which pulls data using a linked server to a Netezza database. We have had a memory issue reported in an overnight batch.

Error message:

OLE DB provider "MSDASQL" for linked server [name] returned message "ERROR:  reserve exceeded : Out of memory "

Can any one give me a clue on how to diagnose this error, as we have had it a couple of times recently. As far as we know no infrastructure or configuration has changed on either side of the linked server.

We are not sure if this means the SQL Server box with the provider or the Netezza box.

Google has failed me on this.


texts: MySQL lock on READ and concurrent execution

texts:
I'm building a small application (in Java) and I have arrived to a point where, depending on a field in one my database table the action to be executed has to be completely unique.

I have a Reservation table:
user_id,
total_price,
status (can be ACCEPTED, CANCELED, DENIED or PENDING)

My aim is to ONLY and ONLY refund a reservation when the reservation's status = ACCEPTED. to avoid a reservation to be wrongly refunded twice or more because of multi-threading

Basically my java code would look like this:

@Transactional
private void refund() {
        // simple select
    Reservation r = resevrationDao.get(id)
        if (reservation.getStatus() == ACCEPTED) {
          // 1) call my payment process to make the refund
          // 2) do some DB operations
          // 3) set the reservation as CANCELED and save it in the DB
        }
}

Basically I was looking to some mechanism than would set a LOCK when I get the reservation from the DB. So that every other thread/transaction that tries to access (read) that resevrationId are put on 'hold' until the session that has the lock released it by doing a commit or rollback.

I have looked around and with MySQL there is the SELECT ... FOR UPDATE that seems to fit with my problem.

Could someone with more experience than me in this domain confirm that SELECT .. FOR UPDATE is a good way to go ?
In case it is, does the transaction isolation of the refund has to be something specific ?


texts: Is there a way to call sp_spaceused in a WHILE loop?

texts:
A legacy Sybase script moves records from production to archive in 1000-row batches, but it does not check for space exhaustion on the archive. Is there a way, maybe by calling sp_spaceused, to terminate the loop if the destination is 98% full?

Edit: Yes, this is a symptom of a bigger problem, namely capacity planning, but so far my efforts to invoke FORCE PLAN on those who ought to be planning has not resulted in any detectable increase of their planning activity.


texts:
I have a table called tbemissoes. When there is an insert, I need to execute this procedure :

create procedure exec pr_replicaRemessa --49 ,  2016
@RemessaNumero int,
@RemessaAno int

AS
Begin

--Faço Insert na tabela Reps do banco do Portal de Remessa Eletronica
insert into PortalRemessasEletronicas.dbo.Reps(NumeroRep,AnoRep)
(
select r.numero, r.ano from tbReps r
inner join tbRepEmissao re on re.cod_rep = r.cod_rep
inner join tbEmissoes e on e.cod_emissao = re.cod_emissao
where  r.cod_rep = re.cod_rep and e.numero = @RemessaNumero and e.ano = @RemessaAno
)

--Faço Insert na tabela Origem do POrtal de Remessa Eletronica
insert into PortalRemessasEletronicas.dbo.Origem(TipoOrigemId, numero, ano, NomeOrgao, RepId)
(
select  o.cod_tipoorigem, o.numero, o.ano, o.orgao, pr.RepId from tbOrigens o
inner join tbReps r on o.cod_rep  = r.cod_rep
inner join tbRepEmissao re on re.cod_rep = r.cod_rep
inner join tbEmissoes e on e.cod_emissao = re.cod_emissao
inner join PortalRemessasEletronicas.dbo.Reps pr on pr.NumeroRep = r.numero and pr.AnoRep = r.ano
where r.cod_rep = o.cod_rep  and e.numero=@RemessaNumero and e.ano=@RemessaAno and o.cod_tipoorigem in (2,3,7)
)

--
insert into PortalRemessasEletronicas.dbo.Remessas (NumeroRemessa,AnoRemessa,DataEmissao,Equipe,Nucleo,OrigemId, RepId, OrgaoDestino)
    (
         select ge.numero as 'NumeroRemessa', ge.ano as 'AnoRemessa', ge.datahora_emissao as 'DataEmissao', ue.equipe as 'Equipe', un.nucleo as 'Nucleo', po.OrigemId as 'OrigemId', pr.RepId as 'RepId',ge.destinatario as 'OrgaoDestino'  from PortalRemessasEletronicas.dbo.Reps pr
         inner join PortalRemessasEletronicas.dbo.Origem po on po.RepId = pr.RepId
         inner join tbReps gr on gr.numero = pr.NumeroRep and gr.ano = pr.AnoRep
         inner join tbEmissoes ge on ge.cod_emissao = gr.cod_emissao
         inner join tbUnidades u on u.cod_unidade = ge.cod_unidade
         inner join lstUnidadesNucleos un on u.cod_nucleo = un.cod_nucleo
         left join lstUnidadesEquipes ue on ue.cod_equipe = u.cod_equipe
         where pr.NumeroRep = gr.numero and pr.AnoRep = gr.ano and ge.numero = @RemessaNumero and ge.ano = @RemessaAno
    )
end

This is the trigger:

alter trigger tr_replicaRemessa
    on tbemissoes
    for insert
        as
            begin
                declare @RemessaNumero int,
                        @RemessaAno int

                        select @RemessaNumero = numero, @remessaano = ano from inserted

                        exec pr_replicaRemessa @RemessaNumero,@remessaano

                        end

Using Profiler, I can see that the trigger is executed:

But why is this data not being inserted? Even with after insert it's not working.

Manually, when I create a scenario, inserting values in the tbemissoes, I have this:

0 rows affected.

When I run the procedure manually, with the desired variables, it inserts ok in those 3 tables inside the procedure.

Again with Profiler, it runs the "exec" before the "exec" command in the trigger. how is this possible?


texts: Insertion of data violates the foreign key constraint

texts:
Consider I have two tables Employee and Department with the fields given below:

Employee:

Fname, Minit, Lname, Ssn Bdate, Address, Sex, Salary, Super_ssn, Dno

Department:

Dname, Dnumber, Mgr_ssn, Mgr_start_date

Italics and code block represent the primary key of table.

In above case, Super_Ssn references the SSN of employee, while Dno references the Department's Dnumber.
Creating the sql for them is pretty straightforward by adding columns first and altering them later when both tables are formed. However, while inserting the data like given below:

insert into employee values('James','E','Borg','888665555','1937-11-10','asda','M',55000,NULL,1);
ERROR:  insert or update on table "employee" violates foreign key constraint "dno_ref_dnum_dpt"
DETAIL:  Key (dno)=(1) is not present in table "department".

and

insert into sp0090.department values ('Headquarter',1,'888665555','1981-06-19');
ERROR:  insert or update on table "department" violates foreign key constraint "department_mgr_ssn_fkey"
DETAIL:  Key (mgr_ssn)=(888665555) is not present in table "employee".

I am getting the foreign key constraint violated. This can be traced because to insert Mgr_ssn in department, it has to exists in Employee and to insert dno in Employee it has to exists in Department. However, I am required to fill the tables with maintaining the schema as it is. How do I solve this circular referencing? Thanks.


texts:
Server version: 5.5.47-MariaDB-log MariaDB Server / CentOS Linux 7 x86_64

I have two users: user@% and user@localhost with the same password but apparently different privileges (some privileges the 1st user has were missing from the 2nd user), and a bunch of Wordpress blogs and Rails apps connecting to this database server.

Out of a sudden a couple hours ago, everything stopped working, throwing "database access denied" errors all over the logs. After granting full privileges to the second user, all went back to normal.

I never connect to "localhost" or "127.0.0.1" or any of the public IP addresses, but use a host name instead (database.example.com) which I point to whatever IP I want either via DNS or /etc/hosts file, in this case it was mapped to 127.0.0.1.

Is there a reason why MariaDB / MySQL would pick the first user over the other at any random point in time? Nobody touched anything.


texts: How to backup PostgreSQL database (a schema) where a user has access to only one schema

texts:
I have created a sales schema in Postgres.
(so in total there are sales and the default public schemas)

I added the user salesuser to Postgres, and granted it the access to the sales schema.

This is how I did that:

CREATE ROLE salesuser WITH LOGIN PASSWORD 'password';
GRANT CONNECT ON DATABASE database_name TO salesuser;
GRANT USAGE ON SCHEMA sales TO salesuser;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA sales TO salesuser;
ALTER ROLE salesuser SET search_path to sales;

Now, I'm trying to do a backup of the schema sales using the pg_dump and the user salesuser.
The expected result is to backup only the schema sales, as it's the only schema the user salesuser is allowed access to.

The command I use is:
pg_dump -h localhost -p 5432 -U salesuser -d database_name > /tmp/2223.dmp (note there is no --schema= or -n parameter)

Unfortunately, somehow the pg_dump tries to dump also the public schema, to which the user salesuser doesn't have access rights to, as the error below is displayed:

pg_dump: [archiver (db)] query failed: ERROR:  permission denied for relation customer
pg_dump: [archiver (db)] query was: LOCK TABLE public.customer IN ACCESS SHARE MODE

I assumed that the pg_dump command with a user which has only access to a single specific schema, would backup only this schema, and other schemas (such as public) would be omitted.

I need to avoid defining the -n or --schema= parameter for the pg_dump.

I need to focus on access rights of the user with the use case to backup all the schemas the user salesuser is allowed to access to.

How is it possible?
What am I missing?


texts: Is this database fragmentation or something else? What can be done?

texts:
I have the table below in PostgreSQL (automagically generated with insertions using Marten):

create table mt_doc_order
(
    id               uuid                                                                                not null
        constraint pk_mt_doc_order
            primary key,
    data             jsonb                                                                                not null,
    mt_last_modified timestamp with time zone default transaction_timestamp(),
    mt_version       uuid                     default (md5(((random())::text || (clock_timestamp())::text)))::uuid not null,
    mt_dotnet_type   varchar
);

I am inserting a lot of data to see if there is at some point a bottleneck in terms insertion performances.

I recorded the insertion time for every 5000 records inserted and here is below a graph showing the number of records inserted in the table (x-axis), and the insertion time (y-axis):

It seems that around 25,000,000 records the database needs more time for insertion, I supposed that there is something related to the update of structure maintaining the primary key (ie. B-Tree?).

I thought at some point that it was an effect of fragmentation but as far as I know fragmentation is more something that have to do with update / delete in table filled with index(es) so that with Postgre you need to do some vacuum command to ease the b-tree maintaining those indexes.

Plus it looks that even though the auto-vacuum dameon was running, there was no vacuum command after more than 25 millions records inserted:

root@4e862966a7ad:/# ps -axww | grep autovacuum
   63 ?        Ss     0:00 postgres: autovacuum launcher
  139 pts/0    S+     0:00 grep autovacuum

SELECT
    schemaname, relname,
    last_vacuum, last_autovacuum,
    vacuum_count, autovacuum_count  -- not available on 9.0 and earlier
FROM pg_stat_user_tables;

gives:

| schemaname | relname     |last_vacuum| last_autovacuum |vacuum_count| autovacuum_count |
|------------|-------------|-----------|-----------------|------------| -----------------|
| public     | mt_doc_order|   <null>  |    <null>       |      0     |         0        |

So I am wondering:

How do you call that effect of there is relatively a "lot" of data that seems to trigger some sort of bottleneck upon insertion performance?
Is there anything that can be done  to improve the insertion performance once we reach that amount of data in one table?


texts: Updating databases that user's can manipulate

texts:
I have a product that includes a MySQL database with some defined countries (very few, not all of them). The user has some control over this table - they can add / edit / delete countries. When we update their software, we run an SQL text file that updates the database, including this table. In this case, for a new version upgrade, we might add a few more default countries.

We use one SQL text file for upgrades that is simply appended to over time, so that the user can upgrade from version X to version Y without going in some specific order. To avoid stomping over user data, I think INSERT IGNORE INTO is the best method to add these default countries. However, this will also have the effect of re-inserting countries that the user may have deleted.

What could be a better method of adding default rows to a table that the user actually has full control over? There's no option of a separate "defaults" table because the user needs control over the table.

Is it possible to check what primary keys no longer exist, and if they don't, do not insert into that table?

Let's say my INSERT statement goes

INSERT IGNORE INTO myTable ('id', 'countryName')
VALUES
(1, USA),
(2, Canada);

If the primary key is at 3, and 2 is missing, how can I avoid re-inserting it?


texts: Which of these queries is best for performance?

texts:
Which of these queries are best for performance ? Sometimes I wonder if SHORT scripts really is the best thing to focus on. These scripts performs the same task. With left joins i can achieve what i want with just a few lines. But then I tried with a longer script, using unions. Which is the best method? Between this:

SELECT p.productID, p.product, C.color, C.colorID, S.size, S.sizeID, q.qty From quantities q
        INNER join products P ON p.productID = q.productID
        LEFT JOIN colors C ON C.colorID = q.colorID
        LEFT JOIN sizes S ON S.sizeID = q.sizeID
        --WHERE q.productID = @productID

and this:

SELECT p.productID, p.product, C.color, C.colorID, S.size, S.sizeID, q.qty From quantities q
        inner join products P ON p.productID = q.productID
        INNER JOIN colors C ON C.colorID = q.colorID
        INNER JOIN sizes S ON S.sizeID = q.sizeID
        --WHERE q.productID = @productID
    UNION
    SELECT p.productID, p.product, NULL, NULL, S.size, S.sizeID, q.qty From quantities q
        inner join products P ON p.productID = q.productID
        INNER JOIN sizes S ON S.sizeID = q.sizeID
    WHERE /* q.productID = @productID AND */ q.sizeID IS NOT NULL AND q.colorID IS NULL
    UNION
    SELECT p.productID, p.product, C.color, C.colorID, NULL, NULL, q.qty From quantities q
        inner join products P ON p.productID = q.productID
        INNER JOIN colors C ON C.colorID = q.colorID
    WHERE /* q.productID = @productID AND */ q.colorID IS NOT NULL AND q.sizeID IS NULL
    UNION
    SELECT p.productID, p.product, NULL, NULL, NULL, NULL, q.qty From quantities q
        inner join products P ON p.productID = q.productID
    WHERE /* q.productID = @productID AND */ q.colorID IS NULL AND q.sizeID IS NULL

EDIT:

  SQL Server parse and compile time:     CPU time = 32 ms, elapsed time
  = 65 ms.

  (10 row(s) affected) Table 'sizes'. Scan count 1, logical reads 21,
  physical reads 0, read-ahead reads 0, lob logical reads 0, lob
  physical reads 0, lob read-ahead reads 0. Table 'colors'. Scan count
  1, logical reads 21, physical reads 0, read-ahead reads 0, lob logical
  reads 0, lob physical reads 0, lob read-ahead reads 0. Table
  'products'. Scan count 0, logical reads 20, physical reads 1,
  read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob
  read-ahead reads 0. Table 'quantities'. Scan count 1, logical reads 2,
  physical reads 1, read-ahead reads 0, lob logical reads 0, lob
  physical reads 0, lob read-ahead reads 0.

  SQL Server Execution Times:    CPU time = 0 ms,  elapsed time = 12
  ms.

  (10 row(s) affected) Table 'products'. Scan count 0, logical reads 20,
  physical reads 0, read-ahead reads 0, lob logical reads 0, lob
  physical reads 0, lob read-ahead reads 0. Table 'quantities'. Scan
  count 4, logical reads 8, physical reads 0, read-ahead reads 0, lob
  logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table
  'Worktable'. Scan count 0, logical reads 0, physical reads 0,
  read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob
  read-ahead reads 0. Table 'sizes'. Scan count 0, logical reads 18,
  physical reads 0, read-ahead reads 0, lob logical reads 0, lob
  physical reads 0, lob read-ahead reads 0. Table 'colors'. Scan count
  0, logical reads 12, physical reads 0, read-ahead reads 0, lob logical
  reads 0, lob physical reads 0, lob read-ahead reads 0.

  SQL Server Execution Times:    CPU time = 0 ms,  elapsed time = 0 ms.

It seems to me that it's the second query that is best for performance, the bigger query with unions.

Do anyone of you got a clue why this happends or do I have to provide with more information (table info and such things) ?

Execution plan:

Thanks!


texts: What rights do you need for Ola's integrity check?

texts:
I'm configuring a new server and Ola's integrity check is working for user databases (I have restored a database of around 50 GB for tests) and is failing for system databases. If I look in the logs it works for model and fails for master and msdb with The database could not be checked as a database snapshot could not be created...

The SQL Agent account is sysadmin. It works if I add the SQL Server and SQL Agent account as administrators of the Windows Server, so it's some rights missing somewhere. It's a VM running Windows Server 2016 version 1607, I don't have access to the Hyper-V host but it's probably the same version.

Same result with SQL Server 2017 and SQL Server 2014. Seems to be caused by Windows Server 2016, maybe some group policies settings.


texts: MySQL select complementary pairs on particular values

texts:
I have the following table (MySQL):

I need to select the complementary pairs of accessions having different properties grouped by folder. In other words I do not want to group accessions having same property values for one folder.

As a final result I need to get:

I came up with something like that, but I am getting doubled results (strait and reversed pairs).

select    distinct a.accession, a.folder, b.accession, b.folder
from      main a
     join main b on a.accession!= b.accession AND a.folder= b.folder
where    a.property not in (select c.property from main c where c.accession = b.accession)
and      b.property not in (select c.property from main c where c.accession = a.accession)
group by a.accession, a.folder, b.accession;


texts: Configuring cost threshold for parallelism & Max Degree of Parallelism

texts:
In production environment found that the default value of 5 is there for Cost Threshold for parallelism and 0 for Max Degree of Parallelism. From various resources I understand that it is not the optimal value and has to be changed. Now the question I am trying to figure out is what might be the optimal value for my environment.

With the help of sys.dm_os_sys_info found that the environment has:

Physical CPU = 1 & Logical CPUs = 4

So does it mean I can set Max Degree of Parallelism as 4? If not please help me understand what to look for and decide the value for it.

Made use of Jonathan Kehayias below script:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

WITH XMLNAMESPACES
   (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT
        query_plan AS CompleteQueryPlan,
        n.value('(@StatementText)[1]', 'VARCHAR(4000)') AS StatementText,
        n.value('(@StatementOptmLevel)[1]', 'VARCHAR(25)') AS StatementOptimizationLevel,
        n.value('(@StatementSubTreeCost)[1]', 'VARCHAR(128)') AS StatementSubTreeCost,
        n.query('.') AS ParallelSubTreeXML,
        ecp.usecounts,
        ecp.size_in_bytes
FROM sys.dm_exec_cached_plans AS ecp
CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS eqp
CROSS APPLY query_plan.nodes('/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple') AS qn(n)
WHERE  n.query('.').exist('//RelOp[@PhysicalOp="Parallelism"]') = 1

and this is the summary I found:

StatementSubTree cost: Mean is 36.5, Mode is 12.78 and Median is 9.38
Totally ~850 entries out of which ~750 entries subtree cost is less than 50. And ~25 is above 100 & out of which 15 is above 500.

So wondering should I change the cost threshold value as nearest to median or mean. If that is not the way to do please guide me the steps to be followed.

Wait Stats result:


texts: How to set listener.ora and tnsnames.ora for oracle instances

texts:
I set listener.ora and tnsnames.ora, but I do not know if it is well set up.
When I call the lsnrctl status command, I get instances rcadb and proddb unknown.

I'm a beginner in Oracle, I installed the Oracle 12c pre-built.

Now that I've made two databases, I have this problem, I can not find a mistake.

The database orcl12c I got with the installation,
had host = 0.0.0.0 which I changed

Listener.ora:

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = orcl12c)
      (SID_NAME = orcl12c)
      (ORACLE_HOME = /u01/app/oracle/product/12.2/db_1)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = prodDB)
      (SID_NAME = prodDB)
      (ORACLE_HOME = /u01/app/oracle/product/12.2/db_1)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = rcatDB)
      (SID_NAME = rcatDB)
      (ORACLE_HOME = /u01/app/oracle/product/12.2/db_1)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
    )
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.2)(PORT = 1524))
    )
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.3)(PORT = 1525))
    )
  )

And I set tnsnames.ora like this:

ORCL12C =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl12c)
    )
  )

prodDB =
 (DESCRIPTION =
    (ADDRESS = (PROTOCOL=TCP)(HOST = 127.0.0.2)(PORT = 1524))
    (CONNECT_DATA =
       (SERVICE_NAME = prodDB)
    )
 )

rcatDB =
 (DESCRIPTION =
    (ADDRESS = (PROTOCOL=TCP)(HOST = 127.0.0.3)(PORT = 1525))
    (CONNECT_DATA =
       (SERVICE_NAME = rcatDB)
    )
 )

LISTENER_ORCL12C =
  (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521))

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

lsnrctl status

Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.2)(PORT=1524)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.3)(PORT=1525)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=8081))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "51c99766d7e2568de0530100007f4fae" has 1 instance(s).
  Instance "orcl12c", status READY, has 1 handler(s) for this service...
Service "orcl" has 1 instance(s).
  Instance "orcl12c", status READY, has 1 handler(s) for this service...
Service "orcl12c" has 2 instance(s).
  Instance "orcl12c", status UNKNOWN, has 1 handler(s) for this service...
  Instance "orcl12c", status READY, has 1 handler(s) for this service...
Service "orcl12cXDB" has 1 instance(s).
  Instance "orcl12c", status READY, has 1 handler(s) for this service...
Service "prodDB" has 1 instance(s).
  Instance "prodDB", status UNKNOWN, has 1 handler(s) for this service...
Service "rcatDB" has 1 instance(s).
  Instance "rcatDB", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

EDIT:

[oracle@localhost ~]$ unset TWO_TASK
[oracle@localhost ~]$ . ./.profile_productionDB
[oracle@localhost ~]$ sqlplus sys/oracle as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Tue Jan 15 05:33:45 2019

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> select name from v$database;

NAME
---------
PRODDB

SQL>


texts:
This has been one of top5 wait events in our database, even though there were not many waits but we see it as a potential threat, we want to find the root cause and solution.
Any advice will be greatly appreciated..

DB Version: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

AWR:

Top 5 Timed Foreground Events
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~                                                           Avg
                                                          wait   % DB
Event                                 Waits     Time(s)   (ms)   time Wait Class
------------------------------ ------------ ----------- ------ ------ ----------
DB CPU                                            3,887          95.0
db file sequential read             207,505         125      1    3.1 User I/O
direct path read                     33,793          69      2    1.7 User I/O
cursor: pin S wait on X                   5          43   8650    1.1 Concurrenc
db file scattered read               34,229          39      1     .9 User I/O


texts:
Can anyone tell me why i wouldn't want to the architect Option 1 for SQL Server clustering?  Option 1 is having 2 different clusters, and 2 different named instances on 2 VMs.  One's Active is the other's Passive.  There has even been discussion to add a 3rd and have an fail-over "order of operations".

Option 1:

2 VMs
2 different SQL Server clusters on 2 different WSFC
2 instances.
Instance 1 is active on Vm1, while instance 2's active is on VM2.
Instance 1's passive is on VM2, while instance 2's active is on VM2.

versus separating them out and having

Option 2:

4 VMs
2 separate clusters
one instance per VM
Instance 1, a single WSFC on Vm1 & Vm2
Instance 2, a single WSFC on Vm3 & Vm4

Besides the extra memory, CPU resources what other reasons would you chose one architecture over the other?

Thanks!


texts:
i use code like this  to prevent deadlock.

while($retry<3 and $notsone)
{
try
{
$transaction->commit();
$notdone=false;
}
catch
{
$transaction->rollback();
$retry++;
}
}
if ($retry==3)
{
throw new exeption("deadlock found!");
}

i test code with running code in 6 different browser concurrently . sometimes in some browser i see a deadlock found message but sometimes i got a message from apache http server.
apache http server has stopped working. i have 2 option. close program or check for online solution.

my question is do concurrency problem cause apache hang?
how to prevant this?


texts:
I have data that is being collected every 30 minutes from a website. This data looks like the following. The "epoch" row labels are placeholders for actual epoch timestamps of each sample. The columns of names along the top would expand indefinitely as more unique names are found.

table n (multiple tables of data like this)
-------------------------------------------
        James   Tom   Jeff   Leon   Sarah  [...]
epoch1  1       44    22     NULL   3
epoch2  4       33    23     1      NULL
epoch3  5       32    22     2      NULL
...
epochn  x       x     x      x      x

What I'm doing is scraping the website for anything classified as a name by an NLP library, and recording frequencies for the top 40 names found. So I scrape the entire "page" of this website and find that there are 22 instances of the name "Jeff", then record it in the table at Jeff/Epoch1 with frequency 22.

I already have the name tagger working, but it's just a matter of finding something that could handle this sort of data and pull metrics like "10 most popular names in table 4 at epoch between 24 hours ago and now". I've looked at PostgreSQL but it has a conservative column limit. Ideally columns would be added for each new name found, but this doesn't seem feasible with the databases I've found so far.

Anyone know of any open-source software that could store this data, or best practices?


texts: Is it ok if I use only the latest binary log file when restoring MySQL db?

texts:
mysqlbinlog --verbose --stop-datetime="2019-12-28 10:00:00"  bin.000009 > backup.sql

mysql -u root -p < backup.sql

(The stop-datetime parameter here is just for testing)

I did the recovery job like that. Everything seems to be working fine. I don't know why the other numbers are required. Is that due to "max_binlog_size"?

My DB env is MYSQL 8.0, innodb

log-bin=/var/www/dbenv/log/bin/bin.log
binlog_cache_size=2M
max_binlog_size=500M
binlog_expire_logs_seconds=120000


texts:
I'm trying to learn about covering indexes. In the Northwind database, I select from the table Categories:

As you can see the table has a non-clustered index on the column CategoryName.
This SQL query:
select CategoryName  from Categories where Categories.CategoryName like 'Beverages'

returns an execution plan with an index seek:

However, this:
select CategoryName ,Description from Categories where Categories.CategoryName like 'Beverages'

returns this execution plan with an index scan using the primary key index, which isn't expected:

I can find the expected behaviour only when I force the query with the non-clustered index:
 select CategoryName ,Description from Categories
    with(index(CategoryName))
 where Categories.CategoryName like 'Beverages'

What is the problem?


texts: how to build table relationships in a firebird database?

texts:
I am a beginner in Delphi and I have a Firebird database with 2 tables namely masterlist and daily collection. I used Zeos 7.0.3 to access my Firebird database.

My masterlist contains the following columns:

╔══════╦══════╦═════════╦════════╗
║ name ║ date ║ balance ║ status ║
╚══════╩══════╩═════════╩════════╝

My daily collection contains the following columns:

╔══════╦══════╦═════════╦═════════╗
║ date ║ name ║ payment ║ balance ║
╚══════╩══════╩═════════╩═════════╝

I would like to build a relation in which the balance from masterlist will be copied to the balance column of the daily collection, and when I update the column in the daily collection it will also update the content of the masterlist.

Hope this will be considered a good question I have tried very hard to make a useful question.


texts: Array integer[]: how to get all distinct values in a table and count them?

texts:
I'm not so good with SQL (PostgreSQL). Here is what I want to do:

I have a table, fields:

id SERIAL
inet INET
ports integer[]

 id |    inet    | ports
----+------------+------------
  2 | 1.2.2.1    | {80}
  1 | 1.2.3.4    | {80,12}
  ...

How can I

get all used "ports" values in this table: 80, 12
count how many inet addresses are on specific port:

Like this:

  port  | count
--------+------------
 12     | 1
 80     | 2
  ...

If anyone is looking for a Django version of it:

class Unnest(Func):
    function = 'UNNEST'

Model.objects \
.annotate(port=Unnest('ports', distinct=True)) \
.values('port') \
.annotate(count=Count('port')) \
.order_by('-count', '-port')


texts: Incremental encrypted per database backup and Partial restore policy for MySQL 5.7 in replication setup

texts:
I will run a replication cluster using MySQL 5.7 and I would like to set up for each database a backup policy that has a full once per day + incremental every N hours with data encrypted (based on the full of the day). The backup will be done on the slave obviously.
I've looked to Percona xtrabackup documentation that cover each subject apart but not together.
For the backup steps I do it this way
Full backup command once a day
xtrabackup --backup --encrypt=AES256
  --encrypt-key="GCHFLrDFVx6UAsRb88uLVbAVWbK+Yzfs" \
  --compress
  --safe-slave-backup # in order to be able to restore on master \
  --slave-info \
  --databases foo
  --extra-lsndir=/data/backups/db_foo/20200201/full # to get clear text binlog info
  --target-dir=/data/backups/db_foo/20200201/full

Incremental backup every 2 hours
xtrabackup --backup --encrypt=AES256
  --encrypt-key="GCHFLrDFVx6UAsRb88uLVbAVWbK+Yzfs" \
  --compress
  --safe-slave-backup # in order to be able to restore on master \
  --slave-info
  --databases foo
  --incremental-basedir=/data/backups/db_foo/20200201/full # use full directory of the day
  --extra-lsndir=/data/backups/db_foo/20200201/incr_020000
  --target-dir=/data/backups/db_foo/20200201/incr_020000

You can see for the backup I pass option --extra-lsndir to get the LSN / checkpoint info in clear text alongside the encrypted ones so the incremental backup can be done. From my test it seems to work fine.
Layout of the full dump
|/data/backups/db_foo/20200201/full
├── backup-my.cnf.qp.xbcrypt
├── foo
│   ├── db.opt.qp.xbcrypt
│   ├── people.frm.qp.xbcrypt
│   └── people.ibd.qp.xbcrypt
├── ib_buffer_pool.qp.xbcrypt
├── ibdata1.qp.xbcrypt
├── xtrabackup_checkpoints
├── xtrabackup_checkpoints.xbcrypt
├── xtrabackup_info
├── xtrabackup_info.qp.xbcrypt
├── xtrabackup_logfile.qp.xbcrypt
└── xtrabackup_slave_info.qp.xbcrypt

However it's unclear to me the order of the steps necessary to restore the data with incremental and encrypted data together.
so my questions are:

are the backup commands correct ?
do you have some information how should I do the restoration? I'm confused about the order to run the decrypt/decompress/prepare, when to use --apply-log-only even after reading this resource and also how to restore only one database and not ALL the data.

Best.


texts: SQL Graph DB Tables not supporting OLE DB Fast Load?

texts:
I try to use SSIS to load in SQL Server Graph DB an edge table, based on the documentation from MSFT it should behave like any normal table, if I use the OLE DB Destination with regular table load it works (but is super slow), so i wanted to use “table or view - fast load” configuration looks as expected but upon execution i get this error.
An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 11.0"  Hresult: 0x80004005  Description: "JSON data for INSERT/UPDATE of graph pseudocolumn '$edge_id' is malformed.”.
based on my understanding the edge_id should be auto created, therefore I’m a bit surprised, is fast load not supported on graph tables, hence the documentation is incorrect or what did I miss here ?


texts: Do mongodb's 2dsphere index works on multidimensional coordinantes array?

texts:
if i create a index like that :

db.createCollection("users")
db.users.ensureIndex({coordinates:"2dsphere"});

and then insert a point

var point = {
   type: "point",
   coordinates: [ 1, 2, 3, 4 ]
}

db.users.insert(point)

and after execute the query :

db.users.find(
  {
     coordinates:
       { $near :
          {
            $geometry: { type: "Point",  coordinates: [1, 2, 3, 3 ] },

            $maxDistance: 5000
          }
       }
   }
)

it seems to work, does de the 2dsphere index works as expected with multidimensional coordinates ?.


texts: How to find all positions of a string within another string

texts:
How can I find all the positions with patindex in a table or variable?

declare @name nvarchar(max)
set @name ='ali reza dar yek shabe barani ba yek  '
  + 'dokhtare khoshkel be disco raft va ali baraye'
  + ' 1 saat anja bud va sepas... ali...'
select patindex('%ali%',@name) as pos

This returns 1 but I want all results, e.g.:

pos
===
  1
 74
113


texts:
My user WebApp seems to have SELECT permissions on every table in every schema and I can't figure out why.

WebApp is only in the Public role.
Public doesn't own any schema.
Explicit permissions haven't been set on the tables in question
Effective permissions, as reported by SSMS, show it has SELECT permission


texts: How to concatenate arrays with user-defined types in pgsql?

texts:
Background

I am using pgsql to build database tables, and have historically defined the tables in static arrays (eg ARRAY[ROW(a,b,...)::user_defined_type,...]). With the upgrade to PostgreSQL 11.2, I’d like to manipulate dynamic arrays (specifically, to generate new arrays with a number of elements determined at runtime).

The Problem

When trying to build the arrays, I have tried to use array_append, array_cat, and || (for both array-to-array and array-to-element concatenation), as documented in §9.17. While it works with built-in types, it does not work with the user-defined types (created using CREATE TYPE). The error is:

No [function | operator] matches the given name and argument types. You might need to add explicit type casts.

However, since the function definitions of the aforementioned array functions use anyarray and anyelement as their parameters, I’m not certain why the array functions do not operate on the user-defined types (note that the error does show that an array of user-defined types appears where an anyarray parameter expected and the properly- cast user-defined type appears where an anyelement should appear).

Example

DB Fiddle here

Other Notes

I have tried to ask the PostgreSQL mail list, but the e-mail server doesn’t seem to like my e-mail address.

System

PostgreSQL v11.2

Fedora 29


texts: Speeding Inserts of longtext or longblob columns

texts:
i'm currently migrating a DB2 database to MySQL, using
SQL WORKBENCH Tool

It basically does a select from DB2 and Insert to DB2.

It works fine, but on the tables with longblob or longtext columns (for storing xml or images) it takes a huge amount of time. (about 40 hours for a table of 10M rows).

Facts:

Server: 2CPU CORES and 8GB of RAM OS: Linux RHEL 6.5
MySQL Server 5.6.13
Tables are InnoDB.
MySQL Server and the Migration tool are on the same computer so there
isn't network overhead.
Tables on MySQL have PRIMARY KEYS but not any other indexes.
I disabled binary log for the massive insert.
I configured the migration tool to do a batch insert of 10
rows and commit every insert (10 rows).

Two questions here:

What are the values recommended for the batch insert size, considering the size of the columns (longtext, longblob)?
Is there any tuning recommended on my.cnf to improve the insert performance?

Here's the contents of my.cnf file:

[mysqld]

open_files_limit=2048
table_open_cache=1024
query_cache_type=1
query_cache_size=64M
tmp_table_size=32M
thread_cache_size=9
default-storage-engine=INNODB
back_log=80
flush_time=0
join_buffer_size=1M
max_allowed_packet=1G
max_connect_errors=100
sort_buffer_size=256K
table_definition_cache=1400
binlog_row_event_max_size=8K
log-bin
log_bin_trust_function_creators=1

#INNODB
innodb_additional_mem_pool_size=8M
innodb_flush_log_at_trx_commit=1
innodb_data_file_path = ibdata1:5G:autoextend
innodb_log_buffer_size=16M
innodb_buffer_pool_size=6144M
innodb_log_file_size=1GB
innodb_thread_concurrency=3
innodb_autoextend_increment=64
innodb_concurrency_tickets=5000
innodb_old_blocks_time=1000
innodb_open_files=300
innodb_stats_on_metadata=0
innodb_file_per_table=1
innodb_checksum_algorithm=0


texts: Switching between dblinks depending on date

texts:
The database was divided in two, one with information up to 2016 and the other with information from 2017 onwards.
The DBA created two dblinks: @old.com (<=2016) and @new.com (>2016).
I have some simple searches that depending on the date need to use the corresponding dblink.

select * from user@old.com;

or

select * from user@new.com;

I would not like to use "execute immediate" and concatenate the dblink.
There is another way I can do this?


texts: Issues regarding phpMyAdmin - character set

texts:
I'm trying to migrate a Wordpress site from one server to another. Everything is going fine apart from the database migration.

I have my database 'A', and using phpMyAdmin I am attempting to export it so that I may use phpMA on the destination server to to import it.

The problem appears to be that when I'm importing the database, it doesn't recognize the character type utf8mb4 despite my efforts to change the character set/type in phpmyadmin to utf8 via queries and being told that it has changed by the console.

I also ensured that UTF8 was selected on both locations and that doesn't seem to help at all. I've tried to search for help but I've been unsuccessful.


texts: Force number precision in a view with Union

texts:
I am using Entity Framework and Oracle. Oracle's ODAC seems to have a known bug where data of type NUMBER is cast to Int64 by Entity Framework.  I thought a view would assist me but

CREATE OR REPLACE VIEW test as
select 1 AS ONE ,CAST(2 AS NUMBER(1)) AS TWO ,CAST(3 as INT) AS THREE from dual
union
select 1 AS ONE ,CAST(2 AS NUMBER(1)) AS TWO ,CAST(3 as INT) AS THREE from dual

When you look at the view the data type is Number with no precision for all of them.  This appears to be a consequence of the UNION as just

CREATE OR REPLACE VIEW test as
select 1 AS ONE ,CAST(2 AS NUMBER(1)) AS TWO ,CAST(3 as INT) AS THREE from dual

yields NUMBER, NUMBER(1), NUMBER as the datatypes

Is there a workaround to force Oracle to derive a precision in a view with a UNION?

Edit: @Phil asks the desired result is. He is correct that Entity Framework has to have a precision to work with so the desired datatype from the sample above would be
NUMBER(1)

Edit: @Phil for this version Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production on Windows 32 bit a view with a union gives a view that has NUMBER with no precision.  Entity Framework interprets this as int 64.

I am looking for a solution that shows the NUMBERS with a precision that Entity Framework can understand as a 32 bit integer.  Any precision from NUMBER(1) to NUMBER (9) does the job.


texts: How to remove specific characters from strings in sql

texts:
I've got a table with a column. I want to change a part of some strings in the column.

Input

6|Drivers Manuals

BH-EB531

TRST-BACMN-

Desired output

Drivers Manuals

BH-EB531

TRST-BACMN

Below was the code I tired to execute

 select REPLACE(REPLACE(COL, '6|', ''),'-','') FROM Table

Output

Drivers Manuals

BHEB531

TRSTBACMN

the script need to remove '6|' and last character only if it was '-'

Can any one help me to achieve desired output


texts: How to hide the partition child tables in PostgreSQL?

texts:
I am creating partition tables in postgreSQL. And the partition is on date column so its nearly high in number to show in schema. I want to show only my parent table for other users.
I tried many searches but this kind of concept is not explained anywhere.
This is my hierarchy,

Master_table
  child_01_2013
  child_02_2013
  ....
  child_07_2019

so child table is in Monthly partition. I dont want to show all child table in my database. I am using Pg-admin 4 and DB visualizer(Free) tool for my works.
Kindly help me to resolve this.


texts: Unable to backup SQL Server from a newly added clustered drive

texts:
We have a clustered SQL server, and the backups of the databases are stored on the same drive where the data files are(F:). The storage team finally acted on my request to have a new clustered drive solely for SQL backups(G:). When I try to backup the database on the backup folder on the new clustered drive I get an error:

Cannot access the specified path or file on the server. Verify that you have the necessary security privileges and that the path or file exists.
If you know that the service account can access a specific file, type in the full path for the file in the File Name control in the Locate dialog box.

I have already:

Assigned the cluster disk to SQL Server in WSFC
Added the SQL Server service account in the Security on the disk properties, and has been inhereted by the children
Changed the database settings on the instance properties to have the new path for backup

Is there anything else that i missed? I still cant backup to the new clustered disk. Does this process require a server restart?


texts: PostgreSQL Failover - What tools should I use?

texts:
Here is the scenario:

There are two machines running CentOS 6.2 - machine0 and machine1

Both have PostgreSQL 9.1 installed.

One of them should be active, as a master system and through asynchronous streaming replication the other machine, the standby should copy changes to the database from the master system.

Assuming that machine0 is the master and machine1 is the standby at the beginning.

If the master (say machine0) fails (failure here means the postgresql server crashes) the standby should takeover from the master and become the new master.

machine1, the new master handles all the database operations and when the postgresql server in machine0 gets back online, it should become a standby, start synchronizing from the point it lost contact with machine1 and copy all changes to the database and stay in standby mode.

When machine1 fails, the whole cycle repeats.

When the standby fails and comes back online, it should start reading from the master and synchronize data.

I'm confused as to what are tools that I need to use for setting this up as I understand PostgreSQL does not come with failover by default.

If someone can link me to threads/pages that describe how to do what I am trying, I'll be really grateful.


texts: column {table_name} of relation {table_name} does not exist SQL state: 42703

texts:
I am getting an error while running an update query with table name specified along with column name:

UPDATE Temp SET Temp.Id='234',Temp.Name='Test'WHERE Id='245'

This is the error:

ERROR:  column "temp" of relation "temp" does not exist
LINE 1:      UPDATE Temp SET Temp.Id='23...
                               ^
********** Error **********

ERROR: column "temp" of relation "temp" does not exist
SQL state: 42703
Character: 24


texts: Adding indexes to very large tables in MySQL

texts:
I have a very large table (600M+ records, 260G of data on disk) within MySQL that I need to add indexes to.  I'd expected to add them directly, but doing some searching and some recommend creating a placeholder table, creating index(es) on it, dumping from first table and then loading to second table.

Can anyone advise on a preferred approach and guesstimate potential processing time improvement.


texts: How costly is changing column datatype in PostgreSQL?

texts:
I'm currently evaluating PostgreSQL for an app we are developing which comes with a weird usecase. We want to allow clients to create new fields which in turn will create new columns in a client-specific table. At the same time, suppose a field needs to be updated from type int to (say) float, it should change the datatype in the table as well (of course with consideration for what the data in the table is and if implicit conversion is even possible).

I was wondering what the impact of such an operation is on the table. Does this get an exclusive lock as with some other table updation methods? I cannot find any data in the documentation about the performance of such updates.

Obviously, this operation is not expected to happen often, but I want to ensure if/when it does, the table does not completely lock up for a long time with reads/writes being blocked.

Pointers to Postgres performance under datatype updates would be appreciated.


texts:
I'm having to change a column in MYSQL to NULL to NOT NULL, but I get the error excutar the syntax. I want to change only the last column, already performed some research but have not found the solution, which can be simple but I'm new to database and had not realized this until the moment. can anybody help me?

Example held: Quickly change NULL column to NOT NULL

ALTER TABLE glpi_ticketsatisfactions ALTER COLUMN comment BIGINT NOT NULL;

My table:

Error:

  [42000][1064] You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'BIGINT NOT NULL' at line 1

CREATE TABLE:

CREATE TABLE glpi_ticketsatisfactions
( id int(11) NOT NULL AUTO_INCREMENT,
  tickets_id int(11) NOT NULL DEFAULT '0',
  type int(11) NOT NULL DEFAULT '1',
  ate_begin datetime DEFAULT NULL,
  date_answered datetime DEFAULT NULL,
  satisfaction int(11) DEFAULT NULL,
  comment text COLLATE utf8_unicode_ci,
  PRIMARY KEY (id),
  UNIQUE KEY tickets_id (tickets_id)
) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci


texts: How to find documents that is equal or includes today in its date range?


texts: Server Principal SID showing as NULL and name as empty string

texts:
In my SQL Server 2016 server,i set up a user logins Audit for Login successful and Login Failed action.
Here is the script.

USE [master]
GO

CREATE SERVER AUDIT SPECIFICATION [ServerAuditSpecification-20190404-091348]
FOR SERVER AUDIT [Audit_SQL_Logins]
ADD (FAILED_LOGIN_GROUP),
ADD (SUCCESSFUL_LOGIN_GROUP)
WITH (STATE = ON)
GO

USE [master]
GO

CREATE SERVER AUDIT [Audit_SQL_Logins]
TO FILE
(   FILEPATH = N'D:\SQL_Logins_Audit\'
    ,MAXSIZE = 20 MB
    ,MAX_FILES = 50
    ,RESERVE_DISK_SPACE = OFF
)
WITH
(   QUEUE_DELAY = 1000
    ,ON_FAILURE = CONTINUE
    ,AUDIT_GUID = '77795756-1928-4ce4-85f5-e3ad2e3e22e5'
)
WHERE ([server_principal_name]<>'name1' AND [server_principal_name]<>'name2' AND ([server_principal_name]<>'name3' AND [server_principal_name]<>'name4') AND ([server_principal_name]<>'name5' AND [server_principal_name]<>'name6'))
ALTER SERVER AUDIT [Audit_SQL_Logins] WITH (STATE = ON)
GO

In the Audit Logs,

I can see lots of failures with Server Principal SID as NULL and name as empty string.Earlier when i set up the login i didn't give any filters and that time the Audit logs showed only success and there were no failures.

I don't understand what is getting failed here since the log shows NULL .

Additional Info:

Here is the screenshot.
This is a production database server and users are logging in from the .Net Web application.
I see the message "Network error code occurred while establishing a connection;the connection has been closed."


texts: Filegroup per Table partition - remove filegroup and re-add filegroup

texts:
I've seaching all over the web for a solution, if it is even possible... I'm not a savvy in Partitioned tables in different filegroups...

Our software is supported by SQL 2008 R2 SP2 to SQL 2017 for Express to enterprise editions (99% are setup with Simple recovery model).  I have a big table that I want to Partition into different filegroups by month (each file group will have only one partition).  I've read about the Switch, Merge and Split functionalities and the filegroup functionalities of adding and removing... but I haven't found an article that uses this to backup a filegroup.

What I'm trying to accomplish, is to somehow remove the oldest file group once a month, but without deleting the content... I woud then keep these monthly files  somewhere as a backup.  If ever I need to query data from one of the old file groups, I would re-add the filegroup and re-add it to the partition...

Our software is distributed to thousands of customers.
Due to the fact that there is a 10GB limitation of SQL Server express, I want to keep the database at a decent size and keep older months accessible by creating a procedure that would "re-attach" that data to the table for querying purposes.

Is it even possible?


texts: How to setup Oracle SSL using self-signed certificate between client and DB?

texts:
I want my DB to send the data to the client in an encrypted way.
I Googled and found that Oracle has SSL. Unfortunately, I can't find a step by step tutorial which explains how to create a self-signed certificate, configure SSL on DB side, configure SSL on client side and test it.

Please, share your knowledge with me.

Thank you in advance.

---EDIT

My steps:

Run Oracle Wallet Manager on DB server.
Create New wallet.
Create New Certificate Request.
Exported Certificate Request.
Using ssl.ca-0.1 (Downloaded from http://www.openssl.org/contrib/) created self-signed root certificate using new-root-ca.sh and imported as a trusted certificate on DB server.
Created self-signed server certificate by running the sign-server-cert.sh and imported as an user certificate on DB server.

Here I am confused , .... don't know how to configure client side

In ssl.ca-0.1 there is also files new-user-cert.sh and sign-user-cert.sh, for what they are used I don't understand.

Tried to run them using the same certification request that was exported from DB server and tried to import it on the client wallet AM I WRONG?

May be yes, because it doesn't import user certificate... I am really confused don't understand the main idea.

I copied wallet generated on DB server on the client side wallet location, AM I RIGHT?


texts: Postgres query slow despite index being used

texts:
I have the following tables:
The main lead table with close to 500M rows:
create table lead
(
    id                  integer,
    client_id           integer,
    insert_date         integer  (a transformed date that looks like 20201231)
)

create index lead_id_index
    on lead (id);

create index lead_insert_date_index
    on lead (insert_date) include (id, client_id);

create index lead_client_id_index
    on lead (client_id) include (id, insert_date);

And then the other tables
create table last_activity_with_client
(
    lead_id       integer,
    last_activity timestamp,
    last_modified timestamp,
    client_id     integer
);

create index last_activity_with_client_client_id_index
    on last_activity_with_client (client_id) include (lead_id, last_activity);

create index last_activity_with_client_last_activity_index
    on last_activity_with_client (last_activity desc);

create index last_activity_with_client_lead_id_client_id_index
    on last_activity_with_client (lead_id, client_id);

create table lead_last_response_time
(
    lead_id            integer,
    last_response_time timestamp,
    last_modified      timestamp
);

create index lead_last_response_time_last_response_time_index
    on lead_last_response_time (last_response_time desc);

create index lead_last_response_time_lead_id_index
    on lead_last_response_time (lead_id);

create table lead_last_response_time
(
    lead_id            integer,
    last_response_time timestamp,
    last_modified      timestamp
);

create index lead_last_response_time_last_response_time_index
    on lead_last_response_time (last_response_time desc);

create index lead_last_response_time_lead_id_index
    on lead_last_response_time (lead_id);

create table date_dimensions
(
    key                      integer,  (a transformed date that looks like 20201231)
    date                     date,
    description              varchar(256),
    day                      smallint,
    month                    smallint,
    quarter                  char(2),
    year                     smallint
    past_30                  boolean
);

create index date_dimensions_key_index
    on date_dimensions (key);

I try running the following query on different client_id and it is always slowed down by the bitmap index scan on client_id in the lead_table
EXPLAIN ANALYZE
with TempResult AS (
    select DISTINCT lead.id AS lead_id,
                    last_activity,
                    last_response_time
    from lead
             left join (select * from last_activity_with_client where client_id = 13189) last_activity_join on
        lead.id = last_activity_join.lead_id

             left join lead_last_response_time lead_last_response_time on
        lead.id = lead_last_response_time.lead_id

             join date_dimensions date_dimensions on
        lead.insert_date = date_dimensions.key

    where (date_dimensions.past_30 = true)
      and (lead.client_id in (13189))
),
     TempCount AS (
         select COUNT(*) as total_rows
         fromt TempResult
     )
select *
from TempResult, TempCount
order by last_response_time desc NULLS LAST
limit 25 offset 1;

A few results:
explain analyze result 1
explain analyze result 2
As you can see, it's using the index but it's quite slow. Always more than 50 seconds. What can I do to make this query run faster? I have some freedom to change the query and the tables too.


texts: How to avoid constantly querying a server

texts: I want to compare 2 table each on their own server.  I want to avoid constantly querying between the 2 server.  Would something like a temp table work well in this case?  Do I need to use linked server and extract the table into a temp table so I only query the server once?  Thanks.


texts: Display list of SSRS reports in a dropdown list, and allow users to select which report to display

texts: Our company has about 10 different SSRS reports. We would like to allow users to navigate from one report to another simply by selecting the report from a dropdown list. Is this possible?


texts: How can I create a Web-service to share data from SQL Server?

texts:
Our organization has an externally hosted website that displays data from a monitoring system. Data is currently provided by FTPing csv files.

The data is stored in a SQL Server Database. The exported data sets are only a few hundred rows of data, updated hourly.

I would like to replace this with a a web service so that data can be retrieved on demand, and we avoid handling files.

I'm a DBA not a programmer, so i'm out of my comfort zone. Is there a conventional approach for doing this? Are there standard Microsoft products that we could configure to achieve this? Or do we need to hire a programmer to develop something custom from scratch?

I know SSIS can consume data from a web-service but can it provide data as a web-service?


texts: Can't login as root and can't change root password

texts:
I have a MySQL 5.5 server on Windows 2012r2.
The database was not performing well, so I generated an ini at Percona and replaced that wit my old ini.

The server is performing good now, but now I cannot login as root, although I am sure I have the right password.
Then I wanted to change the root password. But that did not work either.

Does changing the ini has influence on user passwords?

My old ini:

[client]
port=3306

[mysql]
default-character-set=UTF8

[mysqld]
max-allowed_packet = 64M
wait-timeout = 6000
slow_query_log_file = d:/mysql/slow-queries.log
long_query_time=10
port=3306
basedir="C:/Program Files/MySQL/MySQL Server 5.5/"
datadir="D:/mysql/"
character-set-server=UTF8
default-storage-engine=INNODB
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
max_connections=150
query_cache_size=0
table_cache=256
tmp_table_size=16M
max_heap_table_size=16M
thread_cache_size=8
myisam_max_sort_file_size=100G
myisam_sort_buffer_size=69M
key_buffer_size=55M
read_buffer_size=64K
read_rnd_buffer_size=256K
sort_buffer_size=256K
innodb_additional_mem_pool_size=3M
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=2M
innodb_buffer_pool_size=3076M
innodb_log_file_size=768M
innodb_thread_concurrency=8

My new ini:

[mysql]
# CLIENT #
port = 3306
socket = D:\mysql\mysql.sock

[mysqld]
# GENERAL #
user = mysql
default-storage-engine = InnoDB
socket = D:\mysql\mysql.sock
pid-file = D:\mysql\mysql.pid

# MyISAM #
key-buffer-size = 32M
myisam-recover = FORCE,BACKUP

# SAFETY #
max-allowed-packet = 16M
max-connect-errors = 1000000
skip-name-resolve
sql-mode = STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ONLY_FULL_GROUP_BY
sysdate-is-now = 1
innodb = FORCE

# DATA STORAGE #
datadir = D:\mysql\

# BINARY LOGGING #
log-bin = D:\mysql\mysql-bin
expire-logs-days = 14
sync-binlog = 1

# CACHES AND LIMITS #
tmp-table-size = 32M
max-heap-table-size = 32M
query-cache-type = 0
query-cache-size = 0
max-connections = 500
thread-cache-size = 50
open-files-limit = 65535
table-definition-cache = 4096
table-open-cache = 10240

# INNODB #
innodb-log-files-in-group = 2
innodb-log-file-size = 768M
innodb-flush-log-at-trx-commit = 1
innodb-file-per-table = 1
innodb-buffer-pool-size = 3076M

# LOGGING #
log-error = D:\mysql\mysql-error.log
log-queries-not-using-indexes = 1
slow-query-log = 1
slow-query-log-file = D:\mysql\mysql-slow.log

It looks like all localhost-account's don't work anymore.
There is one %-account and that is still working

I hope someone has an explanation why I cannot login as root anymore.
Or even better make the root login work again.


texts: Are the following statements concerning sys.dm_exec_requests true?

texts:
Parallel plans mean that cpu_time can be greater than total_elapsed time.
Therefore, per-query wait time is not possible to determine accurately from DMVs on servers with more than one CPU thread.
When total_elapsed_time > cpu_time, we can conclude from DMVs that a query waited for at least (total_elapsed_time - cpu_time).
When cpu_time >= total_elapsed_time, we can't conclude from DMVs whether a particular query waited, nor for how long.

Not homework questions; just putting together some documentation for DMV queries that I'm writing.


texts: Slow query using SQL Server difference (soundex) function

texts:
We've got a nasty query that is always doing a clustered index scan even if I add in a non-clustered index with the Forename and Surname columns.
Query takes 2-3 seconds on a 2 million row table and it's being run very regularly. The table has approx 30 columns with lots of GUIDs in there as well (thanks devs!)
Is is possible to speed this up? I would of thought that an index with just the two columns in the query's predicate (Forename and Surname) would be faster to search through and then do a key lookup? I cannot edit the query in any way unfortunately.
select * from IGT_PEOPLE with (NOLOCK)
where ((difference('<Person 1 Name>', SURNAME) > 3)
or ((difference('<Person 1 Name>', FORENAME) > 3) and (difference('<Person 2 Name>', Surname) > 3)))


texts: How to decide whether to enable MySQL query cache using InnoDB

texts:
How should the decision to enable / disable the MySQL query cache be made, on a server that uses just InnoDB tables. Say for example, if the cache is enabled, then how should the output of:

SHOW STATUS LIKE 'Qcache%';

be interpreted to make the decision? Or what other queries/profiling can be performed to get more information, and then how should those be interpreted? If it's to be enabled, how should its size be determined? I'm on Amazon RDS, using MySQL 5.6. I will end up being around ~250 separate databases totalling ~200gb in space.


texts: How to use mongoexport command with ssl?

texts:
I am trying to export collection data from (SSL MongoDB 4.x), for this I am running below command on linux terminal.
Earlier I used mongoexport command in Linux terminal without SSL which works properly for (MongoDB without SSL).

sudo mongoexport --verbose=1 --host X.X.X.X:27018 --ssl --sslCAFile "certificate.pem"  -u username -p password --authenticationDatabase XYZ_Database --collection XYZ_Collection --out Output.csv

Error

2020-01-28T15:15:03.266+0000    could not connect to server: server selection error: server selection timeout
current topology: Type: Single
Servers:
Addr: X.X.X.X:27018, Type: Unknown, State: Connected, Average RTT: 0, Last error: connection(X.X.X.X:27018[-121]) connection is closed


texts:
I need to output some queries as csv files.  The directory is in the webroot and I want it to be writable by apache too, so I'm trying to add mysql to the www-data group (on Ubuntu).

I thought this would work, but I've missed something:

Add mysql to www-data:

ubuntu@168:~/viewer/files$ sudo usermod -a -G www-data mysql

Restart mysql:

ubuntu@168:~/viewer/files$ sudo /etc/init.d/mysql stop
 * Stopping MySQL database server mysqld                       [ OK ]
ubuntu@168:~/viewer/files$ sudo /etc/init.d/mysql start
 * Starting MySQL database server mysqld                       [ OK ]

Verify permissions:

ubuntu@168:/var/www/html/viewer/files$ pwd
/var/www/html/viewer/files
ubuntu@168:/var/www/html/viewer/files$ ls -al
total 640
drwxrwxr-x 2 ubuntu www-data   4096 Jun 10 18:50 .
drwxrwxr-x 3 ubuntu www-data   4096 Jun 10 14:31 ..

Verify group:

ubuntu@168:~$ id mysql
uid=106(mysql) gid=112(mysql) groups=112(mysql),33(www-data)

I've restarted my session - no good.  A day later and my sql query still isn't able to write out:

mysql> SELECT id, title FROM recipes WHERE id IN ('R225037', 'R225046', 'R226412') INTO OUTFILE '/var/www/html/viewer/files/ingredient_caps_6-10-14.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';
ERROR 1 (HY000): Can't create/write to file '/var/www/html/viewer/files/ingredient_caps_6-10-14.csv' (Errcode: 13)


texts: Why won't Oracle use the spatial index when JOINing tables? (Or: Why is my query with a spatial JOIN so slow in Oracle?)

texts:
I have the following table:

CREATE TABLE MY_DATA (
  MY_DATA_ID NUMBER(38,0) PRIMARY KEY,
  GEOM SDO_GEOMETRY
);

I've set its SDO metadata and created a spatial index:

INSERT INTO USER_SDO_GEOM_METADATA (TABLE_NAME, COLUMN_NAME, SRID, DIMINFO)
VALUES ('MY_DATA', 'GEOM', 4326, MDSYS.SDO_DIM_ARRAY(MDSYS.SDO_DIM_ELEMENT('X',-180.0000,180.0000,0.00000000001),MDSYS.SDO_DIM_ELEMENT('Y',-90.0000,90,0.00000000001)));

CREATE INDEX SPIX_MY_DATA ON MY_DATA (GEOM) INDEXTYPE IS MDSYS.SPATIAL_INDEX;

The table has about 150,000 rows.

I need to find rows where the geometries intersect one specific row. This seems like a simple enough task. All it takes is a JOIN and a WHERE clause:

SELECT /*+ INDEX(MD2 SPIX_MY_DATA)*/
    MD2.MY_DATA_ID
FROM MY_DATA MD1
JOIN MY_DATA MD2 ON SDO_RELATE(MD1.GEOM, MD2.GEOM, 'mask=ANYINTERACT') = 'TRUE'
WHERE MD1.MY_DATA_ID = 143668 AND MD2.MY_DATA_ID != 143668

But this query is very slow. It takes over 85 seconds to fetch only 8 related rows. We can see from the query plan that it's not using the spatial index, in spite of the index hint:

Plan hash value: 4008712617

----------------------------------------------------------------------------------------------
| Id  | Operation                    | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |               |   379 |  9854 |  1390   (1)| 00:00:17 |
|   1 |  NESTED LOOPS                |               |   379 |  9854 |  1390   (1)| 00:00:17 |
|   2 |   TABLE ACCESS BY INDEX ROWID| MY_DATA       |     1 |    13 |     2   (0)| 00:00:01 |
|*  3 |    INDEX UNIQUE SCAN         | SYS_C00755898 |     1 |       |     1   (0)| 00:00:01 |
|*  4 |   TABLE ACCESS FULL          | MY_DATA       |   379 |  4927 |  1388   (1)| 00:00:17 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("MD1"."MY_DATA_ID"=143668)
   4 - filter("MD2"."MY_DATA_ID"<>143668 AND
              "MDSYS"."SDO_RTREE_RELATE"("MD1"."GEOM","MD2"."GEOM",'mask=ANYINTERACT
              querytype=window  ')='TRUE')

(Row 2 is MD1, and row 4 is MD2.)

I tried changing the hint to /*+ INDEX(MY_DATA SPIX_MY_DATA)*/, but this also had no effect. I've made sure my statistics are up to date.

How can I speed up this query? How can I get it to use the spatial index?

I'm currently using Oracle 11.2.


texts: What happens if a DC goes down in a Postgresql, Patroni, etcd multi DC setup

texts:
I have a question regarding how Patroni and etcd handle the below scenarios.

Assume there are two sites in this deployment, Site1 and Site2 and it's a 5 node cluster spread across these two sites. This is a primary/replica HA deployment.

Site1 has

S1node1 (Patroni + etcd leader + PostgreSQL master),
S1node2 (Patroni + etcd follower + PostgreSQL replica) and
S1node3 (Patroni + etcd follower + PostgreSQL replica)

Site2 has

S2node1 (Patroni + etcd follower + PostgreSQL replica) and
S2node2 (Patroni + etcd follower + PostgreSQL replica).

Scenario 1:

My understanding is that since the etcd leader is in Site1 and quorum is (5/2)+1 = 3, in case of a network split between the two sites Site1 will continue to accept updates since it has majority and meets quorum. Site2 will stop accepting updates since quorum is not met. Once the network glitch is resolved then the Site2 nodes just join back into the cluster. No action required.

Scenario 2:

If for some reason the entire Site1 went down (disaster scenario), then in Site2 we just have two nodes which don't meet the quorum. There will be no etcd leader which means that Patroni cannot promote the PostgreSQL replica to master at Site2.

Now the question is how to ensure that the Site2 nodes can get promoted to master? Does Patroni take care of this scenario?


texts: Find SQL Server edition and version from setup files without installing

texts:
I want to know how to find SQL Server edition and version details from SQL Server setup without installing it. I have been in situations where sysadmins put SQL Server setup files on a file server without proper file names. So it confuses me a lot of time of which version and edition the setup file is.

I tried to find txt/word files having those details in setup but didn't get any. Please let me know if it is possible to find that out.

I am looking for details like SQL Server 2005/2008/2012 X64/X86 STD/ENT/DEV Edition.

The related question Determine the SQL Server version and edition from the available set up, before actually installing it? does give me version details (like 10, 10.50, 11, etc.); however, how to find the edition? It doesn't give that.


texts: What is the privilege my user requires to be able to grant CONNECT, RESOURCE and CREATE SESSION to other users in Oracle 11g?

texts:
What is the privilege my user requires to be able to grant the following privileges

CONNECT, RESOURCE and CREATE SESSION

to OTHER users in Oracle 11g?


texts:
Upgraded from SQL Server 2008 R2 to SQL Server 2017.  In SS 2008 R2 the application would occasionally get this error:

  “Snapshot isolation transaction failed in database 'tempdb' because
  the object accessed by the statement has been modified by a DDL
  statement in another concurrent transaction since the start of this
  transaction.  It is disallowed because the metadata is not versioned.
  A concurrent update to metadata can lead to inconsistency if mixed
  with snapshot isolation.”

In SS 2017 the error happens much more often.  The transaction is being started in .NET specifically setting the Isolation Level to snapshot and it runs a single stored procedure within the transaction using ExecuteNonQuery(); so the only thing going on from the .NET side is the procedure call.  I'm just flummoxed as to why the same code is producing many more errors in SS 2017 vs. SS 2008 R2.

There are some temp tables created in the procedure using SELECT INTO #Table.  I have recommended that this be changed to explicitly create the temp tables and then insert into them.

I also don't believe that snapshot isolation is necessary in this case and was supposedly added to help avoid deadlocks.  I have recommended using READ COMMITTED SNAPSHOT as I think that will solve the problem, but it requires more testing and changing the .NET code to remove the explicit use of snapshot isolation.

The procedure can be run 100's of times per minute.

I'm open to suggestions.  I can't share the exact code but could post a simple mock up potentially.

How do I stop the error?

Example .NET code:

using (SqlConnection conn = Timeco.ConnectionMgr.GetConnection())
{
conn.Open();
{
    using (SqlTransaction transaction = conn.BeginTransaction(IsolationLevel.Snapshot))
    {
        using (SqlCommand cmd = new SqlCommand("proc_name", conn, transaction))
        {
            cmd.CommandTimeout = 120; // avoid timeouts on save
            cmd.CommandType = CommandType.StoredProcedure;

            SqlParameter param1 = cmd.Parameters.AddWithValue("@param_name", param_value]);
            param1.SqlDbType = SqlDbType.Structured;

            // adds a few more parameters
            cmd.Parameters.Add("@param_anem", SqlDbType.UniqueIdentifier).Value = param_value;

            cmd.ExecuteNonQuery();
        }

        transaction.Commit();
    }
}
}


texts:
I have two tables.

One contains a hierarchical structure of topics and another table that contains optional configuration of that topic, that controls it's visibility in several contexts (one column for each).

Let's call these tables Topic and Conf respectively.
The relationship between Topic and Conf tables is 1:0..1, (a particular topic might have a row for it's respective conf or not, in which case it inherits conf from it's parent topic)

Question: How can I create a query that will list all visibility options for all topics (including those inherited from parent conf)?

I do have an idea how to create a hierarchical query of topics using START WITH ... and CONNECT BY ... clauses, but I do not know how to promote values from parent row.


texts:
What tables will be there.Also as read percentage will be significantly larger than write, is indexing on columns advised? What other precautions are necessary?

For now, I can just think of 1 table containing two columns namely domain and ip address and B+ tree index implemented on both. Is this sufficient?


texts: MySQL InnoDB Cluster can't be created. Why?

texts:
I am trying to setup a MySQL InnoDB Cluster. I configured each node involved using dba.configureInstance(...). Everything was nominal until I ran var cluster = dba.createCluster('clusterNmae'). When I run this command I get this error:
ERROR: Unable to start Group Replication for instance 'my-mysql-cluster-1:3306'.
Please check the MySQL server error log for more information.
Dba.createCluster: Group Replication failed to start: MySQL Error 2013 (HY000): my-mysql-cluster-1:3306: Lost connection to MySQL server during query (RuntimeError)

So I modified the max_allowed_packet to max_allowed_packet = 512M but I still get the same error.
Here is the complete error log for mysql:
2021-03-02T21:12:52.161487Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.23-0ubuntu0.20.04.1) starting as process 1410
2021-03-02T21:12:52.173719Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2021-03-02T21:12:52.542976Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2021-03-02T21:12:52.686661Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '127.0.0.1' port: 33060, socket: /var/run/mysqld/mysqlx.sock
2021-03-02T21:12:52.688284Z 0 [System] [MY-013587] [Repl] Plugin group_replication reported: 'Plugin 'group_replication' is starting.'
2021-03-02T21:12:52.707092Z 0 [System] [MY-010229] [Server] Starting XA crash recovery...
2021-03-02T21:12:52.722770Z 0 [System] [MY-010232] [Server] XA crash recovery finished.
2021-03-02T21:12:52.778384Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2021-03-02T21:12:52.778578Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
2021-03-02T21:12:52.802880Z 0 [Warning] [MY-010604] [Repl] Neither --relay-log nor --relay-log-index were used; so replication may break when this MySQL server acts as a slave and has his hostname changed!!>
2021-03-02T21:12:52.808702Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.23-0ubuntu0.20.04.1'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  (Ubuntu).
2021-03-02T21:12:52.815373Z 10 [System] [MY-010597] [Repl] 'CHANGE MASTER TO FOR CHANNEL 'group_replication_applier' executed'. Previous state master_host='<NULL>', master_port= 0, master_log_file='', maste>
2021-03-02T21:12:52.820635Z 2 [ERROR] [MY-011735] [Repl] Plugin group_replication reported: '[GCS] Unable to join the group: peers not configured. '
2021-03-02T21:12:52.820668Z 2 [ERROR] [MY-011639] [Repl] Plugin group_replication reported: 'Error on group communication engine start'
2021-03-02T21:12:52.820682Z 2 [ERROR] [MY-011735] [Repl] Plugin group_replication reported: '[GCS] The member is leaving a group without being on one.'

I'm running ubuntu 20.04 on all instances.
Why do I get this error(s) and what can I do to get over it? Thank you!


texts: Add article to transaction replication without generating a snapshot at all

texts:
I have a transaction replication and the subscription was synced from snapshot years ago.  Now I have a new table that needs to be added to the replication.  Problem is the table is close to a TB and I do not want to generate a snapshot for that table instead just want the new data to be synced, kinda like when you sync from backup.  Its inserts only to the table and I can sync the old data manually later.

How do I make replication to start transferring the data without generating the snapshot.


texts:
I have a schema migration plan to add json column to mariadb 10.2.
But I am required to have a reverse plan.
Here's the forward plan:

ALTER TABLE `mydb`.`table1`
ADD COLUMN jsonf JSON DEFAULT NULL;

ALTER TABLE `mydb`.`table1`
ADD CHECK(JSON_VALID(jsonf)) ;

Here's the reverse/rollback plan:

ALTER TABLE `mydb`.`table1`
DROP COLUMN jsonf;

DROP CHECK ?

What I am confused is how do I reverse the CHECK?
Thank you.

UPDATE:

I got error

 Kernel error: Error( 4025 ) 23000: "CONSTRAINT `jsonf_is_json` failed for `mydb`.`#sql-8f8_d0c5c1`"

when I try to run add constraint:

ALTER TABLE `mydb`.`table1`
ADD CONSTRAINT jsonf_is_json CHECK(JSON_VALID(jsonf));

I use mariadb 10.2.8 if that matters


texts: Regularly import tab-seperated log file to MySQL

texts:
I'm looking to keep arpwatch entries in a MySQL database to crossreference with other information I'm storing based on mac addresses. I've manually imported the arpwatch database into my mysql database, but being a novice with databases I'm not sure what the best way to continually update the database with new entries without creating duplicates would be. None of the fields can be unique, as even the time is duplicated frequently.  I'm not interested in the actual arpwatch events like flip flop or new station, just the mac/ip/time pairings.

Would a simple bash (or sql) shell script do the trick?
Would it be possible to make the mac address plus the time be a composite key of some sort?

the database is called utility, table is arpwatch, columns are mac, ip, time
a separate table named 'hosts' with columns mac, ip, type, hostname, location, notes has mac as the primary key. This table will correlate different ip addresses that a mac had over time using the arpwatch column
initial import was done with MySQL Workbench using INSERT INTO commands with creative search and replace on the text file


texts: Mysql database slow responding to connects when having only few concurrent connections

texts:
We have a database setup with in master/slave. It runs innodb and its on a 8 core with 32gb.

When i run "ab" to do a load test on a website (4 frontend webservers) which does the query's with 10 concurrent users on the database it will handle the first batch 'ok' after which the average response time will go up.

    Percentage of the requests served within a certain time (ms)
    50%     20
    66%     26
    75%     31
    80%     34
    90%     46
    95%   5018
    98%   5025
    99%   5033
    100%   5041 (longest request)

As you can see, the most are going up to 5 seconds each while the first batch was below 50 ms (which would be ok) i thought it might have something to do with those 5 seconds which stands out. I added skip resolving, but also did not help.

The example query i am using is:

  SELECT imageURL, linkURL, imageWidth, imageHeight, androidLinkURL
  FROM stuff
  WHERE clued = 1 AND status = 'a'
  ORDER BY RAND()
  LIMIT 1;

The load of the machine itself sits pretty much idle (0.3) and when i connect to the server and issue a 'show processlist' i would expect to see at least a couple of these query's coming by. I am only using the master server to talk too, same issue is also on the slave though.

Before this started i thought it was max open files, so i increased that but with only 10 connections its not. Then i decreased the time_waits for connection because that may run out, again 10 connections should not pose a problem. I also had massive IO caused by the innodb logging which i turned to 1.

 innodb_flush_log_at_trx_commit 1

I also saw my connections coming in from both ipv4 and ipv6, so i binded the mysql to ipv4 only. I checked dmesg to see if some syn attack might be causing the problem and tweaked that too but again only 10 concurrent should not be a problem.

I also saw now and then some writes to tmp file, even though it was not in this particular test query, so i also changed that to a ramfs filesystem but also did not help.

Basicly when this happens, connecting to the server grinds to a halt and starts stacking up. Above query is just a example, other query's are cached on php with memcached.

You can find my variables here: http://pastebin.com/YLASDSa5 (disregard the "logging" options that are turned on, those are normally off)

UPDATE: I believe its is no longer a specific database issue. I have installed apache locally on the box of the database server and i see no issues at all. Doing a load test from another box also works fine to the apcache on the box, is also fine.

Here is the weird part, if i do a load test with 10 concurrent users and set my resolv.conf timeout to 1 second it will stall with 1 second as shown in the load test example. If i do 100 concurrent connenctions i get this:

 50%   1084
 66%   1099
 75%   1121
 80%   1156
 90%   2146
 95%   2177
 98%  10002
 99%  10002
100%  10005 (longest request)

As if it has increments of 1 second, then to 2, then in the end to 10. Its something to do with the dns, but i am clueless to what.

Feel free to move the question to the appropriate exchange since it no longer fits Stack Exchange.


texts: What is the "SYS_C00054_14031410:34:03$" column in my regular Oracle table?

texts:
Tried to do partition exchange and it failed because of 2 extra SYS_* columns in the table:

select NAME from SYS.COL$ WHERE obj# =41900658 and name like 'SYS_%';

SYS_C00054_14031410:34:03$
SYS_C00055_14031410:34:03$

ALL_TAB_COLUMNS show nothing on these

What are these? and why CTAS didn't pick them up?

If extended stats - how do i drop them?


texts:
AS we know that entity relation diagram(ERD) is an important concept in database implementation, But I was wondering that can we skip this step when creating a small database containing a few tables i.e directly create tables in RDBMS in order to save time. or I should do all steps for better understanding i.e draw the ERD and then implement in RDBMS ?? Although it is a small database.

Just asking this question for general knowledge as I am new to this field.


texts:
I'm trying to connect my webpage to mySQL with ipage hosting. When I copy the snippet:

<?php
$link = mysql_connect('username.ipagemysql.com', 'username for mySQL', 'password for mySQL');
if (!$link) {
die('Could not connect: ' . mysql_error());
}
echo 'Connected successfully';
mysql_select_db(name of database);
?>

from ipage and link the .php-file to my website in an attempt to establish a test connection I get an error message:

"Could not connect: Access denied for user '#username'@'boscustweb3102.eigbox.net' (using password: YES)"

When I use mySQLadmin on the ipage site and enter the query:

GRANT ALL PRIVILEGES ON mydb.* TO 'myuser'@'IP-of-DBServer';

I get another error message:

"#1044 - Access denied for user '#username'@'10.%' to database '#database'"

I am currently using Dreamweaver and trying to establish a form of validation login with only 1 user to be able to create events for a calendar and an input form to create news for my website.

1st edit:

Here's some extra information:

When I try this query:

SELECT USER() RequestedUserLogin,CURRENT_USER() AllowedUserLogin;

I get the results:

RequestedUserLogin: #username@myadmin01.int.bizland.net
AllowedUserLogin: #username@10.%

By searching for ipage mySQL IP addresses by using the query:

SHOW VARIABLES WHERE Variable_name = 'hostname'

I got this:

Hostname: boscustsqlm56.eigbox.net

To find the appropriate IP adresses I used the website service http://www.hcidata.info/host2ip.cgi:

boscustsqlm56.eigbox.net - 38.113.1.135myadmin01.int.bizland.net - Cannot be resolved to an IP address


texts: Soft delete to improve performance: all wrong?

texts:
A few years back I read soft delete would improve performance of deletion, because instead of actually removing the item and impact the clustered (and all other) indices, it would just change a value.

I recently tried this and I got very disappointing performances.

So I searched through the web for a good implementation of soft deletion and the first things I ended up reading was articles/blogs saying "soft deletion is bad".

Apparently it cause most harm than good when the table gets huge...

I don't think what I did makes table way more huge tables because a nightly (each day) maintenance removes (for real) all rows marked for deletion.

So, are soft deletion really bad for performance? Do real deletion cause a whole clustered table to be fully reordered (which would be a lot of disk operations!!!)?


texts: How to dump a postgreSQL/postgis sample database filtered by a spatial extent?

texts:
My goal is to extract a subset of my postgres/gis database to create a sample dump which has to have the same data model with relations between tables and views, but which has a smaller spatial extent.

My first idea was to select only the features in my spatial extent and then extract these but I'm not sure I can do it without losing some information from the data model.

The second idea is to import only the data that I want in a new db, filtered with spatial extent (quite long).

The third idea is to copy completely the db and then filter the undesired features.

Which idea is the best? Is there a 4th idea that could be easier/quicker? The data model is quite complex : https://github.com/qwat/qwat-data-model


texts: What (kind of) DB can store store variable-length blobs without padding?

texts:
I have an offline task: store a HUGE amount of variable-length binary data on multiple hosts.  Average data size is about 16Kb, total size: maybe hunderds of Tb. I have no consistency or speed requirement as it will likely have 1 client only.

When doing an interface mockup, I used SQlite and found this: when data pieces were all of the same length, the db file was only 4% larger than the raw data, but when I added variable-length data, the DB became almost 50% bigger. So it obviously uses some padding or reserving, and I don't want to waste space like that.

Are there free databases that are known to be most efficient in terms of space for storing variable lengths of blobs? And provides an easy way to spread data across multiple hosts at same time?


texts: Problem in SSIS OLE DB Source passing list of ints as a parameter

texts:
I am using SQL SERVER 2012 and Visual Studio 2017 with SSDT (SQL Server Data Tools) installed, and have a query like the following in an SSIS OLE DB Source:

The parameter is set up like this:

and the ClientIDList variable is a string that has 1 or more int's delimited by commas.

When ClientIDList has only one value (e.g. 999000), the query returns as expected. However, if the variable has 2+ values (e.g. 999000,999001), it returns the following error:

So far, the only way I have found to resolve this is to create a string variable using the following expression:
"SELECT Name, Email FROM Customers WHERE ClientId IN (" + @[User::ClientIDList] + ")"
If I then use the 'SQL command from variable' option and pass the query as a variable, it works with both 1 and more than one values in the WHERE clause. While this is one solution to my problem, it is not very nice in terms of readability and maintainability for other developers. Does anyone know how I can make my original approach work?
Thanks!


texts: PostgreSQL data type text vs varchar without length

texts:
In PostgreSQL you can create a column with data type character varying (without length precision) or text like this:

ALTER TABLE test ADD COLUMN c1 varchar;
ALTER TABLE test ADD COLUMN c2 text;

Is there a difference between these two data types?

The documentation is not clear about it. They say :

  If character varying is used without length specifier, the type
  accepts strings of any size.

  [...]

  In addition, PostgreSQL provides
  the text type, which stores strings of any length.

It seems that these two datatypes are equivalent but it is not explicit... More info about this?

Thank you,
Nico


texts:
I am hosting an application (a game) in multiple regions around the globe. Performance is critical to this application since it's a game, and therefore I need to host a database instance in each of these locations that will serve the local application instances.
Due to the nature of my application, I am only able to use MySQL.

My main requirement is that each of these instances will be writable. I understand that in most cases this is a big problem in MySQL clusters when it comes to two servers modifying the same record.
However, in my application this is actually impossible; a player can only be connected to a single region at any given moment, which means only that region's database instance will read or write that player's data. Consistency is also not critical here and Eventual Consistency is okay for my needs. I just need to make sure it gets there eventually.

I haven't worked with replication before and therefore I need some guidance. What would be the right way to implement this, and how would I go about deploying such a cluster?


texts: postgresql GROUP BY and latest date filter by non group by statement?

texts:
currently I have the following table:

CREATE TABLE demo (
  id SERIAL PRIMARY KEY,
  key TEXT NOT NULL,
  other_key TEXT NOT NULL,
  quantity BIGINT NOT NULL,
  date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now()
);

Now I want to group by the query like that:

SELECT other_key, SUM(quantity) FROM demo GROUP BY other_key;

This works so far so good, however now I want to filter by the key and also print the latest date for the table, is there a good way to do so?

Pseudo (will fail since key is not in group by)

SELECT other_key, SUM(quantity), MAX(date) FROM demo GROUP BY other_key WHERE key = ?;

my initial idea is a sub query:

SELECT other_key, SUM(quantity), MAX(date) FROM (SELECT * FROM demo WHERE key = ?) GROUP BY other_key;

Is there a better way to do so?
And what would be a good index for the table?

My current index is:

CREATE INDEX demo_all_idx ON demo (key, other_key, quantity);

Bonus (edited):

Is there any way to sort by the MAX(date) then?
Is there a way to create a aggregate function that gets the oldest date where the quantity is great than zero? i.e. some kind of inventory event store where the newest date should not be the latest entry made and instead the newest entry made where the quantity isn't subtracted / zero?
like consider the following table:

id | key     | other_key | quantity | date
---+---------+-----------+----------+-----------------------
 6 | 0A19882 | 01/01     |      100 | 2016-08-30 00:00:00+02
 7 | 0A19882 | 01/02     |      -50 | 2016-09-01 00:00:00+02
 8 | 0A19882 | 01/01     |      100 | 2016-09-02 00:00:00+02
 9 | 0A19882 | 01/02     |      100 | 2016-08-31 00:00:00+02
11 | 0A19882 | 01/03     |      100 | 2016-08-31 00:00:00+02
12 | 0A19882 | 01/03     |     -100 | 2016-09-02 00:00:00+02
13 | 0A19882 | 01/03     |      100 | 2016-09-04 00:00:00+02

The date for 01/01 should be 2016-08-30 00:00:00+02 while for 01/03 it should be 2016-09-04 00:00:00+02 since the event with id 12 has reached zero.