Why does this derived table improve performance? I have a query which takes a json string as a parameter. The json is an array of latitude,longitude pairs.
An example input might be the following.

declare @json nvarchar(max)= N'[[40.7592024,-73.9771259],[40.7126492,-74.0120867]
,[41.8662374,-87.6908788],[37.784873,-122.4056546]]';

It calls a TVF that calculates the number of POIs around a geographical point, at 1,3,5,10 mile distances.

create or alter function [dbo].[fn_poi_in_dist](@geo geography)
returns table
with schemabinding as
return
select count_1  = sum(iif(LatLong.STDistance(@geo) <= 1609.344e * 1,1,0e))
      ,count_3  = sum(iif(LatLong.STDistance(@geo) <= 1609.344e * 3,1,0e))
      ,count_5  = sum(iif(LatLong.STDistance(@geo) <= 1609.344e * 5,1,0e))
      ,count_10 = count(*)
from dbo.point_of_interest
where LatLong.STDistance(@geo) <= 1609.344e * 10

The intent of the json query is to bulk call this function. If I call it like this the performance is very poor taking nearly 10 seconds for just 4 points:

select row=[key]
      ,count_1
      ,count_3
      ,count_5
      ,count_10
from openjson(@json)
cross apply dbo.fn_poi_in_dist(
            geography::Point(
                convert(float,json_value(value,'$[0]'))
               ,convert(float,json_value(value,'$[1]'))
               ,4326))

plan = https://www.brentozar.com/pastetheplan/?id=HJDCYd_o4

However, moving the construction of the geography inside a derived table causes the performance to improve dramatically, completing the query in about 1 second.

select row=[key]
      ,count_1
      ,count_3
      ,count_5
      ,count_10
from (
select [key]
      ,geo = geography::Point(
                convert(float,json_value(value,'$[0]'))
               ,convert(float,json_value(value,'$[1]'))
               ,4326)
from openjson(@json)
) a
cross apply dbo.fn_poi_in_dist(geo)

plan = https://www.brentozar.com/pastetheplan/?id=HkSS5_OoE

The plans look virtually identical. Neither uses parallelism and both use the spatial index. There is an additional lazy spool on the slow plan that I can eliminate with the hint option(no_performance_spool). But the query performance does not change. It still remains much slower.

Running both with the added hint in a batch will weigh both queries equally.

Sql server version =
Microsoft SQL Server 2016 (SP1-CU7-GDR) (KB4057119) - 13.0.4466.4 (X64)

So my question is why does this matter? How can I know when I should calculate values inside a derived table or not?

I can give you a partial answer that explains why you are seeing the performance difference - though that still leaves some open questions (such as can SQL Server produce the more optimal plan without introducing an intermediate table expression that projects the expression as a column?)

The difference is that in the fast plan the work needed to parse the JSON array elements and create the Geography is done 4 times (once for each row emitted from the openjson function) - whereas it is done more than 100,000 times that in the slow plan.

In the fast plan...

geography::Point(
                convert(float,json_value(value,'$[0]'))
               ,convert(float,json_value(value,'$[1]'))
               ,4326)

Is assigned to Expr1000 in the compute scalar to the left of the openjson function. This corresponds to geo in your derived table definition.

In the fast plan the filter and stream aggregate reference Expr1000. In the slow plan they reference the full underlying expression.

Stream aggregate properties

The filter is executed 116,995 times with each execution requiring an expression evaluation. The stream aggregate has 110,520 rows flowing into it for aggregation and creates three separate aggregates using this expression. 110,520 * 3 + 116,995 = 448,555. Even if each individual evaluation takes 18 microseconds this adds up to 8 seconds additional time for the query as a whole.

You can see the effect of this in the actual time statistics in the plan XML (annotated in red below from the slow plan and blue for the fast plan - times are in ms)

The stream aggregate has an elapsed time 6.209 seconds greater than its immediate child. And the bulk of the child time was taken up by the filter. This corresponds to the extra expression evaluations.

By the way.... In general it is not a sure thing that underlying expressions with labels like Expr1000 are only calculated once and not re-evaluated but clearly in this case from the execution timing discrepancy this happens here.


Getting 0KB of size file while backup mysql database using mysql dump I am using below code to backup my database using php script but I am getting 0KB size file. How can I backup my full database with all routines and functions?

$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = '';
$dbname='mydatabase';
$toDay = date('d-m-Y');
    //exec("mysqldump --user=$dbuser --password='$dbpass' --host=$dbhost --single-transaction $dbname > D:\Sql_Backups/".$toDay."_DB.sql");
    //exec('mysqldump --user=$dbuser --password=$dbpass --host=$dbhost $dbname > D:\Sql_Backup/file.sql');
      exec ("mysqldump --routines --h $dbhost --u $dbuser --p $dbpass --single-transaction $dbname > D:\Sql_Backup/db3_backup.sql" >

I don't know how to write this in php, but you should write mysqldump command like below, there is no need to write three command one is enough

mysqldump --user=root --password=root --host=localhost --single-transaction --routines db_name > db_name.sql

UPDATE

I did it here is the php script

<?php
define("BACKUP_PATH", "/home/abdul/");

$server_name   = "localhost";
$username      = "root";
$password      = "root";
$database_name = "world_copy";
$date_string   = date("Ymd");

$cmd = "mysqldump --routines -h {$server_name} -u {$username} -p{$password} {$database_name} > " . BACKUP_PATH . "{$date_string}_{$database_name}.sql";

exec($cmd);
?>

It generate backup file named as 20140225_world_copy.sql in /home/abdul/

For reference have a look at This link.


Postgres: difference between CTE and temporary table In Postgres is there a difference between a CTE and a temporary table other than the fact that the CTE exists just for the context of one statement?

Documentation says that

  Common Table Expressions or CTEs, can be thought of as defining temporary tables that exist just for one query.

Does "can be thought" means that they are identical?

To add to what others have said. Temporary tables can also have primary keys, constraints, indexes, etc. whereas CTEs cannot.

On the flip side, you can do some pretty neat tricks with CTEs that would be harder, I think, if done with temporary tables-- such as chaining them to perform deletes, inserts, and selects all in one statement. There are some nice examples of "cool-CTE-tricks" here.


Alert when Job isn't triggered I have a Sql Job on my server that is triggered nightly from a remote server (once it has completed its backups).

I have successfully configured the job to notify me when it fails, but I would like to set up an alert to notify me when a job has not been triggered. i.e. when the remote server fails to trigger the job on my end.

Is this possible? I've had a look at the settings, and all the alerts, etc, are all concerned with when the job fails/succeeds.

I could set the job to alert on completion (regardless of status), and I was doing that to start with (and I did that to start with), but I would rather not get continuous emails (after all, I'd probably just start ignoring them).

I have managed to find one solution to this, although it's not as neat as I would have preferred. I've created a second job that checks the last run date on my target job. This is scheduled to run daily, a couple of hours after the target job should have run.

If not exists
(
    SELECT 'x'
    FROM msdb.dbo.SysJobServers S
        INNER JOIN msdb.dbo.SysJobs J ON S.job_id = J.job_id
    WHERE J.name = 'JobName'
        AND CONVERT(datetime2, CONVERT(VARCHAR(20), last_run_date)) > GETDATE() -1
)
BEGIN
    RAISERROR ('The Job has not been executed today.', 16, 0)
END


Encrypt connection between SSRS and SQL Server We are working under a requirement that all connections to SQL Server be encrypted.  After setting up a Reporting Server (2016), I see an unencrypted connection to the SQL Server.  There are no data sources set up yet, so this is not a connection associated with report data sources, it is just the connection that RS makes to the ReportServer database.  I do not see an option for encrypting the connection anywhere in the setup where the database and credentials are configured.

How can this connection be configured from the client side to use encryption?  I don't want to force encryption on the server side as this may interrupt other connections (that we are working toward getting encrypted).  Yes, we have a cert installed and working--can connect with encryption from SSMS and other clients.

This is NOT about setting up RS to use HTTPS on the portal.  That is set up and working.

Unfortunately, you can't do this.
There are two options (RSCM and rsconfig.exe) to set the connection string that SSRS uses to connect to the Report Server database and neither of them gives you the option to add the "Use Encryption for Data=True" parameter on the connection string. And since SSRS uses the SQL native client to connect you can't configure an ODBC connection and use it instead. The inability to set connection string parameters is definitely a shortcoming. I'm afraid you'd have to force encryption at the instance level to meet your encryption requirement.


Temporal tables: history table population According to the prominent diagram here, the main table in a temporal table set-up is populated during the insert, and the history table is populated during update or delete.

However when I look at a query plan inserting or merging a temporal table tblWhatever, considerable cost is associated with the operator Table Insert [tblWhateverHistory] and when I re-create the clustered index on the history table, dropped before populating the temporal table, the allocated storage jumps considerably.

It all leads me to believe, that the history table is populated with a copy of every record in the temporal table initially, but those history copies are not accessible until and unless the temporal records are touched by updates or deletes. Can anyone confirm or prove me wrong?

I wanted to see if I could reproduce your observation of activity on the history table during inserts to the temporal table. To isolate the two tables I will place each on its own filegroup. I started with a new database in its own directory:

use master;
go

alter database DB219238 set single_user with rollback immediate;
drop database if exists DB219238;
go

CREATE DATABASE DB219238
ON PRIMARY
    -- Primary filegroup gets the default values
    ( NAME = N'DB219238',
      FILENAME = N'D:\Database\Data\DB219238\DB219238.mdf' ,
      SIZE = 3MB ,
      MAXSIZE = UNLIMITED,
      FILEGROWTH = 65536KB ),
FILEGROUP [DATA]  DEFAULT
    -- This will hold the temporal table
    ( NAME = N'Data',
      FILENAME = N'D:\Database\Data\DB219238\Data.ndf' ,
      SIZE = 1MB ,            -- deliberately small so I can observer growth events
      MAXSIZE = UNLIMITED,
      FILEGROWTH = 1MB ),     -- deliberately small
FILEGROUP [History]
    -- This will hold the history table
    ( NAME = N'History',
      FILENAME = N'D:\Database\Data\DB219238\History.ndf' ,
      SIZE = 1MB ,            -- deliberately small, as above
      MAXSIZE = UNLIMITED,
      FILEGROWTH = 1MB )      -- deliberately small
LOG ON
    -- Just the defaults
    ( NAME = N'DB219238_log',
      FILENAME = N'D:\Database\Log\DB219238_log.ldf' ,
      SIZE = 8192KB ,
      MAXSIZE = 2048GB ,
      FILEGROWTH = 65536KB );
GO

-- I don't want to be concerned with log growth
ALTER DATABASE DB219238 SET RECOVERY SIMPLE;
GO

Then I created a temporal table and its history table.

use DB219238;
go

create table dbo.Example_History
(
    ExampleId           int             not NULL,
    Information         char(4100)      not NULL,
    SysStartTimeUTC     datetime2(7)    not NULL,
    SysEndTimeUTC       datetime2(7)    not NULL,
) on History;    -- note the filegroup
GO
checkpoint;
go

create table dbo.Example
(
    ExampleId                           int IDENTITY(1,1)   not NULL,
    Information                         char(4100)          not NULL,

    SysStartTimeUTC                     datetime2(7) GENERATED ALWAYS AS ROW START  not NULL,
    SysEndTimeUTC                       datetime2(7) GENERATED ALWAYS AS ROW END    not NULL,

    constraint PK_Example primary key clustered
    (
        ExampleId ASC
    ),

    period for system_time (SysStartTimeUTC, SysEndTimeUTC)
) on Data    -- note the filegroup
with
(
    SYSTEM_VERSIONING = ON ( HISTORY_TABLE = dbo.Example_History )
);
GO
checkpoint;
go

The Information column is just over half a page long and fixed length so one row consumes a page and relatively few rows can cause a lot of page allocation. At this point the File Explorer shows the directory thus:

Now I populate the temporal table:

insert dbo.Example
(
    Information
)
select
    '';
GO 10000    -- arbitrary, but proved sufficient to demonstrate the case
checkpoint;
go

The only file growth has been on the temporal table. Or at least write activity on the History filegroup is less than the 1MB initially allocated to it.

As a further test I ran SysInternals Process Monitor (PM) while performing the inserts. The checkpoint statements throughout the code are to ensure activity is flushed to disk so PM can observe it.  I captured all events on the directory holding the DB's files. It registered 145 WriteFile operations, amongst others, against Data.ndf and no operations at all against History.ndf.

The execution plan for the INSERT is this:

It shows no activity on the history table, only the temporal table.

Building a clustered index on the history table again showed no activity on History.ndf in Process Monitor.

create clustered index IX_History on dbo.Example_History (ExampleId);
checkpoint;

I think this disproves your hypothesis, at least for my minimal setup. I'm not surprised to see file growth on a clustered index build. In SQL Server the clustered index leaf pages are the data. So to get the data in clustered sequence the storage engine copies the data from its current location to its new. Likely there would be a lot of free space left in that file after the clustered index had build, being the space where the data used to be.

I cannot guess why you saw the insert operators referencing the history table.


Fill Database With Recursive Factorial Function I want to calculate table's all of the rows's factorials value. And then i want to insert to each row's factorial value in table. so that i create table like as below:

TABLE:FACTORIALS
------------------------------
| NUMBERS | FACTORIALS       |
|----------------------------|
| 3       |                  |
| 5       |                  |
| 6       |                  |
| 9       |                  |
| 7       |                  |
| 2       |                  |
| 1       |                  |
------------------------------

First, table has only numbers. I fill the factorial fields with call recursive function fact. Factorial function like as below.

CREATE OR REPLACE FUNCTION FACT(x IN number)
RETURN NUMBER
IS
   f NUMBER;
BEGIN
   IF x=0 THEN
      f := 1;
   ELSE
      f := x * FACT(x-1);
   END IF;
RETURN f;
END;
/

Factorial function works. And i want to select all table numbers in array. Then call factorial function with array's elements. After all i want to fill table factorials's field. For all of them i write sp like as below:

DECLARE
COUNTER INTEGER := 0;
CURSOR C_NUMBERS IS SELECT  NUMBERS FROM FACTORIALS;
TYPE N_LIST IS VARRAY(7) OF FACTORIALS.NUMBERS%TYPE;
TYPE F_LIST IS VARRAY(7) OF FACTORIALS.FACTORIALS%TYPE;
NUMBER_LIST N_LIST := N_LIST();
FACTORIAL_LIST F_LIST := F_LIST();
BEGIN

FOR N IN C_NUMBERS LOOP
      COUNTER := COUNTER + 1;
      NUMBER_LIST.EXTEND;
      NUMBER_LIST(COUNTER) := N.NUMBERS;
 END LOOP;

FOR I IN 1 .. NUMBER_LIST.COUNT LOOP
      FACTORIAL_LIST.EXTEND;
      FACTORIAL_LIST(I) := FACT(NUMBER_LIST(I));
END LOOP;

FOR K IN 1..FACTORIAL_LIST.COUNT LOOP
      UPDATE FACTORIALS
      SET FACTORIALS = FACTORIAL_LIST(K)
      WHERE FACT(NUMBER_LIST(K)) = FACTORIAL_LIST(K);
END LOOP;

END;
/

But this code fill all the factorials fields with same value. This same value is table's last elements's factorial value.The table like as below :

* FACTORIALS TABLE AFTER THE SP IS RAN
------------------------------
| NUMBERS | FACTORIALS       |
|----------------------------|
| 3       |    1             |
| 5       |    1             |
| 6       |    1             |
| 9       |    1             |
| 7       |    1             |
| 2       |    1             |
| 1       |    1             |
------------------------------

FACTORIAL table's last element 1. Code insert all field last element's factorial value namely 1. How can i insert factorial values which i calculate right place in FACTORIALS table.

DECLARE
    COUNTER INTEGER := 0;
    CURSOR C_NUMBERS IS SELECT  NUMBERS FROM FACTORIALS;
    TYPE N_LIST IS VARRAY(7) OF FACTORIALS.NUMBERS%TYPE;
    TYPE F_LIST IS VARRAY(7) OF FACTORIALS.FACTORIALS%TYPE;
    NUMBER_LIST N_LIST := N_LIST();
    FACTORIAL_LIST F_LIST := F_LIST();
BEGIN
    FOR N IN C_NUMBERS LOOP
          COUNTER := COUNTER + 1;
          NUMBER_LIST.EXTEND;
          NUMBER_LIST(COUNTER) := N.NUMBERS;
     END LOOP;

    FOR I IN 1 .. NUMBER_LIST.COUNT LOOP
          FACTORIAL_LIST.EXTEND;
          FACTORIAL_LIST(I) := F_FACT_NUMBER(NUMBER_LIST(I));
    END LOOP;
/***************************FIRST SOLUTION***************************/
    FOR K IN 1..NUMBER_LIST.COUNT LOOP
          UPDATE FACTORIALS
          SET FACTORIALS = FACTORIAL_LIST(K)
          WHERE NUMBERS = NUMBER_LIST(K);
    END LOOP;
/***************************SECOND SOLUTION**************************/
    FORALL INDX IN NUMBER_LIST.FIRST..NUMBER_LIST.LAST
                UPDATE FACTORIALS SET FACTORIALS = FACTORIAL_LIST(INDX)
                WHERE NUMBERS = NUMBER_LIST(INDX);
END;
/

Problem is solved. I have two solutions which are showed in the PL/SQL code. When i execute this code and then the FACTORIALS table like as below:

* FACTORIALS TABLE AFTER THE SP IS RAN
------------------------------
| NUMBERS | FACTORIALS       |
|----------------------------|
| 3       |    6             |
| 5       |    120           |
| 6       |    720           |
| 9       |    362880        |
| 7       |    5040          |
| 2       |    2             |
| 1       |    1             |
------------------------------


Slow performance of MySQL Join Query I have inherited a site (running Coldfusion8 and MySQL 5.0.88) with a product search using multiple criteria. I'm struggling with optimizing the search, especially a pricelist check, which makes the search go from <1sec to 50sec...

Here is what's happening:

When a user searches for products in table  (a), I have to check whether any seller has this user on his allowed-list and whether he is assigned a pricelist. If so, I have to also select the pricelist price from the the pricelist table (p).

I'm pre-compiling this allowed-list check as a Coldfusion query, which returns pricelist_name and sellerID into the variable_pricelists for the current user.

This will look like:

( p.pricelist = "name_abc" AND p.iln = "sellerID_123" ) OR
( p.pricelist = "name_def" AND p.iln = "sellerID_456" ) OR
...

My actual search:

<cfquery datasource="db" name="all_art">
    SELECT count(a.id) AS total, a.nos, a.nos_anzeige
    FROM arts a

    // check for pricelist
    <cfif variables.pricelists neq "">
        LEFT JOIN pricelists p ON
            a.ean = p.ean
        AND a.iln = p.iln
        AND p.price != 0
        AND ( #variables.pricelists# )
    </cfif>
    // 2nd join
    <cfif s_farbe neq "">
        LEFT JOIN farbenzuordnung zu
            ON a.farbe = zu.farbe
    </cfif>
    WHERE a.aktiv = "ja"
    ... more criteria...

    GROUP BY a.iln, a.artikelnummer, a.preis_aktuell, a.artikelbezeichnung
    HAVING sum(a.bestand) != 0 OR (a.nos = "ja" AND a.nos_anzeige = "ja")
</cfquery>

The pricelist table contains cols

 ILN/SellerID   EAN    Currency   Price  Name_pricelist

So for products from sellers, which have a pricelist which is assigned to the user, I also need to select the pricelist price and currency to have these available once I build the result set.

Question:
How can I speed up the search. I need to run it a lot, so 50sec is out of the quesiton, when using pricelists.

I'm not sure I really need a LEFT JOIN at all, when I'm only selecting prices from the other table on matching criteria and do not want to add any records from B to the selection, because they then will be in there twice.

I have been told indexing would be a possibility but since I'm in my first month of MySQL I'm not really sure where to start. Avoiding the LEFT JOIN altogether would be nice.

So, thanks for any tips you can provide!

EDIT:
Ok, I EXPLAINED the query. This is what I get and what doesn't tell me

id   select table type  possible_keys key      key_len ref     rows    extra
1    simple   a    ref    i_aktiv     i_aktiv  7       const   13001   using where/temp/filesort
1    simple   p    all    NULL        NULL     NULL    NULL    22799
1    simple   zu   index  NULL        primary  52      NULL    53      using index

The 2nd entry is my LEFT JOIN in question. Can someone shed some light on what this means?

Please provide SHOW CREATE TABLE; the explain is useless without it.

OR is a performance killer in many contexts.

  ( p.pricelist = "name_abc" AND p.iln = "sellerID_123" ) OR
  ( p.pricelist = "name_def" AND p.iln = "sellerID_456" ) OR ...

Turn that into

JOIN ( SELECT id FROM p WHERE
( p.pricelist = "name_abc" AND p.iln = "sellerID_123" ) OR
( p.pricelist = "name_def" AND p.iln = "sellerID_456" ) OR ... ) x ON x.id = foo.id

Also needed (on p):

INDEX(pricelist, iln, id)

(With the CREATEs, I could be more specific.)

The idea behind this "trick" is to move the costly work of the OR into a subquery that returns the necessary ids.  Plus the INDEX makes it so that it can do all that work in the INDEX.


reportserver will not join basic availabiltiy group I am running into a strange situation that I have not been able to resolve.  I have a 2 node Basic AG running sql server 2016 standard.  I am trying to add the reportserver DB to an AG, but it keeps failing on joining the DB to the secondary node.

I have done the below:

fully recovered the DB on node1 (primary), made sure its in Full recovery mode
took a fresh backup, AND log backup immediately after it
restored both the full and log backup to Node2 (secondary) leaving it in restoring state.

Back on primary I created an AG via the GUI, but when I get to the end it sits on joining the DB to the AG in node2.  I get a few different errors, which essentially mean the same thing:

 "the remote copy of the db "reportserver" has not been rolled forward to a point in time that is encompassed in the local copy"

"the mirror db "reportserver" has insufficient transaction log data to preserve the log backup chain."

I know these mean that should be additional log backups to restore, but I had just prior to this taken the full AND log backup and restored both of them to the secondary, so there should be no other backups to restore...  The only thing I can think of is that something is still writing to the log on the primary, but as far as I am aware, even if that is the case, joining the DB to the AG should still work as the currently un-backed up portion of the log should be streamed to the secondary replica during the sync process.  I skipped the listener configuration for the time being.  I also stopped the SSRS service on both nodes prior to doing this.

Has anyone encountered this before and had success fixing it?  Is it because it is the reportserverDB, and it can't be joined to the same AG as reportservertempDB?

I have tried automatic seeding, and join only mode, and the strange thing is that automatic seeding gives me NO errors during the AG setup, but the DB does not ever show up on Node2. When i do join only, that is when I get the errors above.  I'm pretty much baffled by this.  I have 6 other DB's on these nodes that are each in their own basic AG, and I have not run into any issues with them.

Found the answer, but still don't understand WHY it works.  if anyone can share any insights it would be appreciated.

Issue with adding a database to an availability group

So, using the answer from the post above did NOT work when doing the join only mode.  I was able to get it to work by doing the below:

fully recovery the DB on node1.  do NOTHING on node2 with the DB so I can use automatic seeding.
Go through automatic seeding, and see that the DB did NOT show up on node2
Run the below queries on node2.  they failed for me for error 41106

ALTER AVAILABILITY GROUP [agname] JOIN;
GO
ALTER AVAILABILITY GROUP [agname] GRANT CREATE ANY DATABASE;
GO
Remove the DB from the AG on Node1, then add it back in with the GUI.

After this the DB showed up on node2 and is in the AG properly. I would love to understand WHY this works if anyone has info.


mysql ibdata1 corrupt I do not know whether this is a mysql(version 5.6.11) bug, the case is as following. today when i update mysql conf and kill -9 and restart mysqld, i find that it can't restart anymore. after having a look at the error log, i find the following:

2013-07-09 17:04:21 7872 [ERROR] InnoDB: Tried to read 1048576 bytes at offset 2097152. Was only able to read 917504.

then i make a strace to trace which file has the problem. the result is ibdata1.

-rw-rw----. 1 mysql mysql   79691776 7月   9 16:51 ibdata1

well, for performance i change log commit as following.

innodb_flush_log_at_trx_commit = 2

but i do not think this can cause data lost since OS does not reboot during mysql restart.
so can anybody help me out on what's the reason(maybe the kill -9?) that makes data corrupt and how to restore the data, thank you very much.

If you don't have backup.

I think the recommended way to recover that databases is start MySQL with innodb_force_recovery = 4 (or higher values) and dump the databases to a SQL.Then drop it and recover from backup. Instead of dropping the original database I prefer to create the new databases with another name or in another server and check the content first.

STEPS TO BE FOLLOWED

In mysqld section of my.cnf add a line innodb_force_recovery = 4  and then restart MySQL server using /etc/init.d/mysql restart.
Take backup.
Restore it on other server and verify the contents.

Please also have a look at Forcing InnoDB Recovery.

UPDATE : For your comment how to progress on that so that it would not happen again

You should not kill MySQL it is not a good practice that may cause MySQL server to crash,whenever you make any configuration changes you should proceed as follows.

Safely stop MySQL Server using /etc/init.d/mysql stop
Make changes
Restart MySQL Server


Auto-calculated field which includes letters and numbers I'm currently creating a workflow form to allow a new part creation, as a part of this I need to write an SQL statement which must be a stored procedure to auto populate the Part code field. I have done this previously for both customer and supplier using the following method:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Proc [dbo].[Udef_Wff_SUPCR_AutoSuppCode_SP]
    @FormID                 Int
    ,@UserID                VarChar(5)
As
Declare
    @SPC_IntSupCode         Int
    ,@INS_IntSupCode            Int

Select @INS_IntSupCode = MAX(SUCODE)
From PL_ACCOUNTS Where ISNUMERIC(SUCODE) = 1

Select @SPC_IntSupCode = MAX(SUCODE)
FROM SPECIALLive.dbo.PL_ACCOUNTS WHERE ISNUMERIC(SUCODE) = 1

If @INS_IntSupCode >= @SPC_IntSupCode
Begin
    Select Convert(VarChar(10), (@INS_IntSupCode+1))
End
If @INS_IntSupCode <= @SPC_IntSupCode
Begin
    Select Convert(VarChar(10), (@SPC_IntSupCode+1))
End

For both supplier and customer the data is identical in 2 databases and also the ID field is numeric. However my part ID's are in the format of

M10000001
M10000002
M10000003

and I will be updating the databases individually as the data contained will be different. As the database already contains entries I will need to locate the last entry and add 1 to the numeric part of the code. This is what I have come up with so far:

USE [INSULATIONLive]
GO
/****** Object:  StoredProcedure [dbo].[Udef_Wff_ParCR_AutoPartCode_SP]Script Date: 07/06/2016 09:06:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Proc [dbo].[Udef_Wff_ParCR_AutoPartCode_SP]
    @FormID                 Int
    ,@UserID                VarChar(5)
As
Declare
    @I          Int
    ,@PC        Varchar(16)

select @PC = Max(PMA_PART_CODE)
From [dbo].[ASC_PMA_TBL]
set @I = right(@PC,7)

Begin
select convert (varchar(16), @I +1,7)
end

No idea if it will work yet, any helpful comments much appreciated or alternative methods of achieving this

Don't do the SELECT MAX thing; you can run into concurrency problems there. If the patterns are simple enough, and have only a single increasing numeric portion, you can probably get away with using an identity column plus a persisted computed column to create the formatted string value:

CREATE TABLE IDTest (
    row_id int IDENTITY(1,1) NOT NULL,
    part_id AS 'M' + REPLICATE('0', 10 - LEN(CAST(row_id AS varchar(10)))) + CAST(row_id AS varchar(10)) PERSISTED,
    part_name varchar(50) NOT NULL
)

INSERT INTO IDTest (part_name) VALUES ('First part'), ('Second part'), ('Third part')

SELECT * FROM IDTest

If your patterns need to get fancier than that (multiple numeric segments, variations in the letters, etc.) then take a look at using sequences and triggers.


How to sync a table between oracle and sybase I have a table in Sybase (destination) that I would like to keep it synced with a table in Oracle (source)

I cannot truncate and populate the table in Sybase because it is used by an application, I can only add new records and update changes, changes only happen to one column.

In my environment I have Sybase, Oracle, SQL Server & .net development

Whats the best, simplest & reliable way to get this task done, should I develop a DTS, a .net app, use linked server of SQL Server or is there a better way?

You could use Oracle Data Integrator for this. See Oracle Data Integrator documentation for a good starting point. If you want you can build this yourself. Change Data Capture can be used. Make a change table in Oracle that receives the change records from the Oracle source and implement the change records to the sybase destination tables.


How can Excel communicate with a remote SQL Server that is disconnected from the Internet? My setup involves three computers:
1. An application sits on an Internet-connected Windows Server in the Cloud.
2. This application is connected to a SQL Server instance on another Windows Server in the Cloud, which is disconnected from the Internet
3. I have an Excel sitting on a third computer.

How can Excel send data to the Windows Server database?

One working option is to use Dropbox to synchronize text files between the Excel and the Internet-connected Windows Server, and then to use sqlcmd on the Internet-connected Windows Server to run these text files on the database.

However this solution is not ideal for a number of reasons!

Any better way? As far as I know I can't open up SQL Server to the Excel - using such approaches as described in https://leansoftware.net/Help/Excel-Database-Tasks/Worked-examples/How-to-connect-Excel-to-Remote-Web-Server-SQL-Data.aspx - because my SQL Server is not connected to the Internet.

Note that the SQL Server is the Express edition (we hope to upgrade to Azure in the not-too-distant future)

We have created an Excel Add-In for updating tables from Excel to SQL Server. The Add-In supports working in offline mode, and I'm pretty sure the off-line mode will handle this case.

You will need to install Excel and the SQL Spreads Add-In on both the non-internet connected server and on the laptop. The process will then be like this:

On your "off-line" server containing your SQL Server databases, create a new Excel file and select the SQL Server table(s) you like to update.
The data from the tables will be loaded into the Excel file. When you save the file, all the SQL Server data will be stored into the Excel file.
Move the Excel file to your laptop and edit the data there. When you save the
file, all your changes will be stored into the file.
Move the Excel file back to the server, open the file and click Save in the SQL
Spreads tab in Excel to push the updates to the SQL Server tables.

The Add-In can also check for conflicts if the data has been changed in the database while you were editing it in your off-line doc.

If that's of interest, you can read more here and download a free trial.

Disclaimer: I'm the founder of the company.


how do i update mulitple rows with dynamic data? I am using SQLite to update mulipte rows, but it does not work.
item table to keep the item data
item_ID Qty
-       -
1      10
2      10
3      10

user_basket table to keep users' basket data
user_ID item_ID  Bask_Qty
-        -       -
1        1       5
1        2       1
2        1       1

I used command like:
UPDATE item
SET Qty =
(SELECT Qty-Bask_Qty FROM user_basket
INNER JOIN item ON item.item_ID = user_basket.item_ID)
WHERE item_ID IN (SELECT item_ID FROM user_basket WHERE user_ID = 1);

After the command, I should expect item table be like that:
item_ID Qty
-       -
1      5
2      9
3      10

but instead, I got:
item_ID Qty
-       -
1      5
2      5
3      10

apparently, it used the same value to update all the rows.

UPDATE item
SET Qty = Qty -
(SELECT sum(Bask_Qty) FROM user_basket
 Where item.item_ID = user_basket.item_ID and user_basket.user_ID = 1);

This would update all items, though you may not notice it ( minus zero! ).
Better add your
WHERE item_ID IN (SELECT item_ID FROM user_basket WHERE user_ID = 1);


mongodb removeShard issue with config.system.session I have a staging mongodb 4.0.19 sharded cluster with 2 replica set shards . I want to remove one shard. I followed the documentation: https://docs.mongodb.com/v4.0/tutorial/remove-shards-from-cluster/index.html and used removeShard command.
But one chunk cannot be moved: the config.system.sessions chunk. I have the following error:
2020-07-30T09:21:07.330+0200 W SHARDING [conn21] Chunk move failed :: caused by :: OperationFailed: Data transfer error: migrate failed: InvalidUUID: Cannot create collection config.system.sessions because we already have an identically named collection with UUID b788097c-5ba1-464c-9064-0b7faed5c196, which differs from the donor's UUID 22588e86-20a2-4cdf-a70e-803fbd7b3e5a. Manually drop the collection on this shard if it contains data from a previous incarnation of config.system.sessions
This prevents draining chunks to finish. Is there any way to drop that collection or to force removeShard command to finish ?

If you login directly to that shard (and not mongos), you can drop that collection. After you have logged in to that shard, you can check with db.collection.find() command that there is no data what you want to save, before you drop it.


SQL Server DR Plan - Test Documentation I have been tasked with creating a document for the testing of our DR/BC Plan for our SQL Servers.  We are using DB Mirroring (tier-1) and transaction log shipping (tier-2) and for tier-3 databases we will rely on nightly backups to restore too (non-critical DB's with little to none data changes).

What I am interested in is getting ideas on what should be in the document for testing the plan, I am wanting an accurate an detailed document that will test as many feasable scenarios as possible.

What I need to cover is the scenarios to test the three tiers (with more focus being on the top two tiers), which has very detailed step by step instructions on how to complete the scenario.

I have never done a document like this and would be interested in other peoples thoughts and experience.

Is there examples for this type of document that I can look at?
Has anyone had any experience in creating a document like this that would be happy to share thoughts and touch on what they included in there plan?

I have done application test cases before, do I aim for a document that is similar to a test case whereby an explanation of what you want to achieve and the steps that you do including specifying what gets typed, or should it be a more open document allowing the user to push the boundaries a little?

AS this is my first attempt and my searches on the internet hasnt returned much that is of any assistance, my appologies if I have upset anyone with silly questions or incorrect use of terminology, your guidance and corrections is appreciated.

Update: Basically, I am seeking any ideas or input into the best way to structure a SQL Server DR/BC Test failover plan to enable myself or others in the organisation to test our plans to ensure they work as intended.  If anyone knows of any good resources they can direct me to it would be greatly appreciated.

I recommend keeping the document very simple and short so it is something that is easy to read, easily understood by people that are not experts in database technology and easy to update.

People have far better things to do than read long test plans, however the test plan should be detailed enough to be followed, its a tough process.

Based on this I believe the things that should be included in the test plan are:

Success Criteria for each tier (tier-1, tier-2,tier-3, etc)
Method of Testing for each of the Success Criteria
preferably including links to scripts in source control and instructions on how to execute each test.
List each Server(s) covered in each of your tiers and therefore covered by each success criteria.
Glossary of any Key terms to remove Ambiguity (plain english explanation of Recovery Time objective, what tier-1 / tier-2 means, etc)
Date Last Modified
Create a team calendar event which is used to remind the team to revise the document each 3 / 6 months to verify the document still reflects the environment.
Scope Definition (In-Scope & Out-Of-Scope)

Other things may come to mind, but simplicity I think is key.

The only other thing that pops in my mind is while you may be writing a test plan for the database, don't forget that a live database that can't be reached by applications still does not meet business continuity objectives; though it may satisfy a data protection need. Consider adding some minimal application testing to your plan or explicitly rule it out by mentioning what is In-Scope and what is Out-Of_Scope.


SQL Server: localhost vs MSSQLLocalDB I have two different local versions of SQL Server on my machine:

(localdb)\MSSQLLocalDB
localhost

I think one of them might have been installed alongside Visual Studio, but I am not sure. Is there a difference between these two (other than the version number)?

And if I only want to keep one of them, which one should it be? I am only using it for local stuff, while developing...

LocalDB is installed by Visual Studio and is intended to be minimal configuration for development work.  Check out here.

If you want to keep just one, I'd go for SQL Express as you can easily connect to there for your development work.


Convert an int column to bigint with the 4 bytes of padding available If there's 4 bytes of padding that would fit the bigint perfectly, is it possible to efficiently convert it on-the-spot by taking advantage of that gap?

CREATE TABLE a (
    b SERIAL PRIMARY KEY,
    c TIMESTAMP WITH TIME ZONE NOT NULL,
    d TIMESTAMP WITH TIME ZONE NOT NULL,
);

I understand that indexes and foreign keys would still be affected, but just wondering about the table itself.

Are you asking if the table heap size would be affected, then no the 4 extra bytes of padding would be used for the bigint, and the table would be unchanged since the following data type (timestamptz) uses 8 bytes and would be placed on the following "column" as it were. This should also hold true for indexes, I believe anything up to 8 bytes will occupy the same space per row.

Tested using postgresql 10:

insert into a (c,d) select generate_series(now(), now()+interval '100000 days', interval '1 day'), now();
INSERT 0 100001

select pg_relation_size('a');
 pg_relation_size
------------------
          5218304
(1 row)

select pg_total_relation_size('a');
 pg_total_relation_size
------------------------
                7479296
(1 row)

alter table a alter column b type bigint;

select pg_relation_size('a');
 pg_relation_size
------------------
          5218304
(1 row)

select pg_total_relation_size('a');
 pg_total_relation_size
------------------------
                7479296
(1 row)

Were you to change your id column to smallint, (smallint primary key on table can only have a maximum of 65535 rows, deleting rows would lead to a different issue...) the data size would likewise still not be affected since the following timestamp column could still not be squeezed next to it.


Upgrading Master-Slave MySQL I'm experimenting with MySQL replication with MySQL 5.5.52.
My main problem is: how to do "apt-get upgrade" on a master-slave setup properly?

Until now I upgraded the slave then failed over and upgraded the other node as a slave.

So far It worked without problems, but now I found the following:
As I upgraded the slave it created some binlogs:

mysql.CREATE TABLE IF NOT EXISTS db (...)
mysql.CREATE TABLE IF NOT EXISTS host (...)
mysql.CREATE TABLE IF NOT EXISTS func (...)
...

As this was the slave node these changes are not replicated to the master, they are lost after the failover.
If I repeat this on the other node after its role is changed from master to slave, the same will happen.

So the changes made by the upgrade will never be committed to the replicated database.

Is this an issue?

How can I avoid this?

Can I upgrade the master-slave setup without (significant) downtime, at all?

Update:
So what happens if I do it like I did it in the past?

Node A : master
Node B : slave

On node B (slave): I issue apt-get upgrade, MySQL gets patched. Some binlogs appear on Node B, containing the statements like above. As this is a slave these changes are not replicated to the master.

After Node B is patched I switch the roles (with Corosync-Pacemaker).

After the failover:

Node A : slave
Node B : master

Now the Master MySQL on Node B contains the data that was on Node A prior the failover. As the changes caused by the patching were on a slave, they are NOT in the DB.

If I now patch Node A as a slave the same happens.

So in the end I will have a patched MySQL containing the exact same data it contained prior the patch. All changes the apt-get upgrade process made are "discarded".

^this is wrong^

So it is not discarded, but only applied as soon as the Slave (B) becomes master.

Does this mean I should not wait after patching one node (so patch them both in one run) and always keep the MySQL versions the same?

You cannot switch roles in the middle of an update between major versions. MySQL-5.6 will generate binary log messages that MySQL-5.5 won't understand. So if 5.6 is every a master of a 5.5 slave you can expect replication to break.

At all stages slave version must be >= master version.

So update the slave by one major version 5.5 -> 5.6. Ensure everything is running correctly. Then update the master by one major version. The binary log messages generated with the update will have no effect on the slave already updated.

While it might be tempting to make a new node C and go through convoluted update process your are better of using a test environment, doing a slave update, a master upgrade, start slave (and repeat until at the latest major version you want). Practices a few times. Test your applications against the final version. Schedule a small outage and run through the steps prepared and tested.

Have a fallback plan, i.e package/config/datadir of original saved and ready to put into place). Test your fallback plan.


Subquery vs Join and where - which one is faster? I have two sql's which gives me same results:

Query 1:

SELECT
u.*, COUNT(po.order_id) products_count
FROM (SELECT * FROM orders o WHERE o.date >= (CURRENT_DATE() - INTERVAL 1 MONTH)) o
LEFT JOIN products_orders po ON po.order_id=o.id
JOIN users u ON u.id=o.user_id
GROUP BY po.order_id

and Query 2:

SELECT
u.*, o.id order_id, COUNT(po.order_id) products_count
FROM users u
JOIN orders o ON o.user_id=u.id
LEFT JOIN products_orders po ON po.order_id=o.id
WHERE o.date >= (CURRENT_DATE() - INTERVAL 1 MONTH)
GROUP BY po.order_id

I wonder.. which one should be faster?
Query Explains shows them to be equals... What do you think guys?

You aggregation doesn't make much sense (and most databases, and even latest versions of MySQL will reject it - See ONLY_FULL_GROUP_BY)... you probably want to GROUP BY o.user_id, (and maybe, also orders_id, but then add it also on the SELECT clause) and you can have something a bit quicker if done this way:

SELECT
    u.*, c.order_count, c.products_count
FROM
    (SELECT
         o.user_id,
         COUNT(DISTINCT o.id) AS order_count,
         COUNT(po.order_id)   AS products_count
    FROM
        orders o
        LEFT JOIN products_orders po ON po.order_id = o.id
    WHERE
        o.date >= (now() - INTERVAL 1 MONTH)
    GROUP BY
        o.user_id
    ) AS c
    JOIN users u ON u.id = c.user_id ;

The execution plan for this query is different, and uses a derived table (which MySQL or MariaDB aren't always very good at), but it will seek your users only once.

You can check out all the details at dbfiddle here


postgres and passwordless authentication We have users for out postgres database and none have passwords... Do not know the history behind it.

When I try to use clients to connect to these databases, for instance, pgadmin III, the connection always fails due to the lack of password.

How does one overcome this? I do not want to create username with passwords just to get around this. Sometimes that is not an option.

Thoughts?

Do you have administrative access to the database?

If so you can edit pg_hba.conf to adjust access control configuration. See http://developer.postgresql.org/pgdocs/postgres/auth-pg-hba-conf.html

After you make changes issue pg_ctl reload or restart the database.


SQL Server Security - Encryption I have requirement to store the some sensitive information in SQL Server 2016. Organization policy demands, it should be encrypted.

Business Requirement:

Data cannot be decrypted by DBA.
Data cannot be decrypted by Developers.
Only end users able to access the decrypted data.

I have looked into some of the encryption options:

Column Level Encryption
Always Encrypted

But both of these options, does not satisfy the requirements. In Column Level Encryption, data can be accessed by DBA and Developers.

Always Encrypted does not allow DBAs to decrypt data but still Developers can be able to view the decrypted data, since data decrypted at the client driver.

I am thinking there must be some other method or combination of techniques can be used to achieve this requirements.

Please suggest on this. Thank you!

There are three ways to accomplish this. Since you've already mentioned Always Encrypted, I must ask why you would need to give the certificate to developers in the production environment. Without the certificate, the column encryption key can't be decrypted.

Second, you can set up column level encryption and drop the key on the certificate or asymmetric key so data can only be encrypted and not decrypted, then replicate it to a more secure server that has the private key.

The option that will most likely apply is a hardware security module. Starting in SQL Server 2008 R2 Enterprise Edition, Microsoft included interfaces to integrate Extensible Key Management systems. This is a hardware module that offloads all encryption and decryption functions, including the storage of keys. If the DBA and developers do not have permissions to this system, then they will not be able to decrypt.


SQL Server Connection String: Can the password be encrypted in line? I have an app, a commercial app, that stores its connection string in clear in the registry.

When I used a trusted connection to SQL Server, this is fine (the user id and password are in the IIS app, and are stored encrypted within IIS).

I now must move to SQL auth, and away from a trusted connection (long story).

I dream of a conn string like this:

Server=myServerAddress;Database=myDataBase;User Id=myUsername;
Password=@#$@#@%#$%#$<encrypted password>@#$@#$;encryptedpassword=yes;

And a way that I can encrypt the pw (using the machine key or such).

Is there a path in this direction?

This is more for SO than DBA, but if you are using the identity/password for the app pool in IIS it will encrypt the password for you when it is stored. However, be aware that even with this process passwords can still be decrypted with not much work.

The connection string itself does not allow you to enter an encrypted password, the mechanism that is saving the connection string handles encrypting sensitive data (or should).


We have copied a drive that had a db on it and I need to open the db on a new server Before I got here, they copied the old servers drive to an external disk.  Then they blew away the old server.  Now that they have a new server and SQL 2012 is installed, they would like to see the db that was on the old server.  How would I go about restoring the db thats copied to the external drive to the new server?  The old db is 2008 r2.

Assuming your database had its MDF and LDF files hosted locally, you should look for these files.
Once you've found your MDF and LDF files you can attach them to the new SQL server using:

USE master;
GO
CREATE DATABASE MyDatabase
    ON (FILENAME = 'C:\MySQLServer\MyDatabase.mdf'),
    (FILENAME = 'C:\MySQLServer\Database.ldf')
    FOR ATTACH;
GO

MSDN article about attaching MDF and LDF files


INSERT ON CONFLICT DO NOTHING and RAISE unique_violation I am processing messages from a distributed queue that guarantees at least once delivery. My strategy for handling duplicate messages has been to use ON CONFLICT DO NOTHING in my inserts.

I had to convert a few tables in my schema to use table partitioning and a trigger to handle which subtable to direct the insert into. The trigger function checks for duplicate entries and performs a RAISE unique_violation. Unfortunately the ON CONFLICT clause doesn't catch these errors and causes my query to error.

I can change the trigger to ignore these duplicates internally, but I don't want to hide these conflicts from other users of the system.

Is there a way to catch the unique_violation from the partition table via ON CONFLICT?

TL;DR How to I RAISE in a trigger that I can handle with ON CONFLICT?

Is there a way to catch a unique_violation via ON CONFLICT?

ON CONFLICT itself is a unique_violation catching mechanism. From the docs

  ON CONFLICT can be used to specify an alternative action to raising a unique constraint or exclusion constraint violation error. (See ON CONFLICT Clause below.)

If you don't want the behavior just don't use ON CONFLICT then you catch the unique_violation yourself and the choices are endless.

That said, one of the drawbacks of partitioning from Limitations,

  Using the ON CONFLICT clause with partitioned tables will cause an error, because unique or exclusion constraints can only be created on individual partitions. There is no support for enforcing uniqueness (or an exclusion constraint) across an entire partitioning hierarchy.

That's because when you partition you don't have a single index that represents uniqueness and so you either have to lock all the indexes during the check and before the insertion, or forgo that functionality.


Why is vertical scaling expensive? As you can find here Best Practices For Horizontal Application Scaling, vertical scaling is much more expensive than horizontal scaling:

  vertical scaling cost increase exponentially whereas with horizontal
  scalability cost is linear.

Why is it so?
Vertical scaling means "adding more power to existing machines", while horizontal scaling means "adding more machines".

In my opinion, upgrading existing machines should be cheaper, as some parts already exist and you only have to buy additional processors, RAM, etc., whereas buying whole new machines should result in higher costs.

In some cases (perhaps most) the servers are already at capacity physically.  An increase in the number of CPU's would require a motherboard swap.  To add RAM to an existing server could be expensive, depending on how old the server is.  Memory modules more than 5 years old and sourced from a dealer can be prohibitively expensive.

What all this amounts to is that upgrading from an 8 processor box with 32GB RAM to a 64 processor box with 128GB would involve purchasing an entire new server to replace the old one.  This is still vertical scaling from the database/application point of view.  A server that supports this many CPU's is likely far more expensive than 7 servers with 8CPU's each (keeping the original one as the 8th).

As per mustaccio's comment: High-end hardware is not just about more processors and RAM; designing hardware to support throughput and power/cooling requirements for dozens of multicore processors is far from trivial. Thus, a 256-core 2TB RAM server might cost $150000, which is about an order of magnitude more expensive than 64 4-core 32GB commodity servers


DBCC CHECKDB Results: page ID contains an incorrect page ID in its page header DBCC CHECKDB results for 'tablename':

  Msg 8909, Level 16, State 1, Line 1
     Table error: Object ID 1637580872, index ID 1, partition ID 72057596516040704, alloc unit ID 72057594048020480 (type LOB data), page ID (23:853329) contains an incorrect page ID in its page header. The PageId in the page header = (0:96).

  Msg 8928, Level 16, State 1, Line 1
     Object ID 1637580872, index ID 1, partition ID 72057596516040704, alloc unit ID 72057594048020480 (type LOB data): Page (23:853329) could not be processed.  See other errors for details.

  Msg 8965, Level 16, State 1, Line 1
     Table error: Object ID 1637580872, index ID 1, partition ID 72057596516040704, alloc unit ID 72057594048020480 (type LOB data). The off-row data node at page (23:853329), slot 0, text ID 12904397012992 is referenced by page (16:840512), slot 0, but was not seen in the scan.

This is the only table I have a problem with.

As this issue occur when purge some old data so we don't have enough old backup to say clean. Restore option may not possible.

Now we are looking into repair. Is single table repair possible?

Can we rebuild index as problem is with clustered index?

Never use REPAIR_ALLOW_DATA_LOSS.  I would do a database restore before that.

I suggest following this order:

REPAIR_FAST (works 1 of 10 times).
REPAIR_REBUILD (works up to 50% of the time).
Restore database.

Full syntax:

DBCC CHECKALLOC
[
    ( database_name | database_id | 0
      [ , NOINDEX
      | , { REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD } ]
    )
    [ WITH
        {
          [ ALL_ERRORMSGS ]
          [ , NO_INFOMSGS ]
          [ , TABLOCK ]
          [ , ESTIMATEONLY ]
        }
    ]
]


SQL Server 2005 database engine login failed error? I installed SQL Server 2005 Enterprise Edition on Windows xp.

This account exists in administrators role.But I can't login to SQL Server database engine using Windows authentication method or sql server authentication method and I receive this error message:

An error has occurred while establishing a connection to the server. When connecting to sql server 2005, this failure may be caused by the fact that under the default setttings sql server does not allow remote connections.(Provider:named Pipes provider,error:40-could not open a connection to sql server)(Microsoft sql server,error:53).

But im trying to connect to a local instance in my computer. Also i checked SQL SERVER 2005 surface area>> surface area configuration for services and connections >>which shows error >>No sql server 2005 componenets were found on the specified computer. Anyone can give clues on this issue. Do i need to install any components to make this work? I had selected all the options while installing.

please help me on this..
 I am attaching the summary log of setup herewtih.

  Microsoft SQL Server 2005 9.00.1399.06
  ==============================  OS Version      : Microsoft Windows XP
  Professional Service Pack 3 (Build 2600)  Time            : Wed Oct 05
  09:49:21 2011    CHANGEME1 : The current system does not meet the
  minimum hardware requirements for this SQL Server release. For system
  requirements, see Hardware and Software Requirements, in the readme
  file or in SQL Server Books Online. Machine         : CHANGEME1
  Product         : Microsoft SQL Server Setup Support Files (English)
  Product Version : 9.00.1399.06  Install         : Successful  Log File
  : C:\Program Files\Microsoft SQL Server\90\Setup

  Bootstrap\LOG\Files\SQLSetup0002_CHANGEME1_SQLSupport_1.log

  Machine         : CHANGEME1  Product         : Microsoft SQL Server
  Native Client  Product Version : 9.00.1399.06  Install         :
  Successful  Log File        : C:\Program Files\Microsoft SQL
  Server\90\Setup

  Bootstrap\LOG\Files\SQLSetup0002_CHANGEME1_SQLNCLI_1.log

  Machine         : CHANGEME1  Product         : Microsoft Visual Studio
  2005 Premier Partner Edition - ENU  Product Version : 8.0.50727.42
  Install         : Successful  Log File        : C:\Program
  Files\Microsoft SQL Server\90\Setup

  Bootstrap\LOG\Files\SQLSetup0002_CHANGEME1_PPESku_1.log

  Machine         : CHANGEME1  Product         : Microsoft Office 2003
  Web Components  Product Version : 11.0.6558.0  Install         :
  Successful  Log File        : C:\Program Files\Microsoft SQL
  Server\90\Setup Bootstrap\LOG\Files\SQLSetup0002_CHANGEME1_OWC11_1.log

  Machine         : CHANGEME1  Product         : Microsoft SQL Server
  2005 Books Online (English)  Product Version : 9.00.1399.06  Install
  : Successful  Log File        : C:\Program Files\Microsoft SQL

  Server\90\Setup Bootstrap\LOG\Files\SQLSetup0002_CHANGEME1_BOL_1.log

  Machine         : CHANGEME1  Product         : Microsoft SQL Server
  2005 Backward compatibility  Product Version : 8.05.1054  Install
  : Successful  Log File        : C:\Program Files\Microsoft SQL
  Server\90\Setup

  Bootstrap\LOG\Files\SQLSetup0002_CHANGEME1_BackwardsCompat_1.log

  Machine         : CHANGEME1  Product         : MSXML 6.0 Parser
  Product Version : 6.00.3883.8  Install         : Successful  Log File
  : C:\Program Files\Microsoft SQL Server\90\Setup

  Bootstrap\LOG\Files\SQLSetup0002_CHANGEME1_MSXML6_1.log

  Machine         : CHANGEME1  Product         : SQLXML4  Product
  Version : 9.00.1399.06  Install         : Successful  Log File
  : C:\Program Files\Microsoft SQL Server\90\Setup

  Bootstrap\LOG\Files\SQLSetup0002_CHANGEME1_SQLXML4_1.log

  Machine         : CHANGEME1  Product         : Microsoft SQL Server
  2005 Tools  Product Version : 9.00.1399.06  Install         :
  Successful  Log File        : C:\Program Files\Microsoft SQL

  Server\90\Setup Bootstrap\LOG\Files\SQLSetup0002_CHANGEME1_Tools.log

As per MSDN you can't install SQL Server 2005 Enterprise Edition on Windows XP. It's not supported. It seems that you can install client tools, BOL and some legacy tools, but not the database server engine itself.

So to answer your question: the SQL Server engine has very likely not been installed when you run setup, so neither you can connect to it, nor you can start any SQL Server service.


Quickest way to migrate mirroring to AG Scenario is this, prod is two two node FCI with mirroring (primary/disaster DC). This is working just fine. New hardware is coming and a version upgrade to SQL Server 2012 (from 2008 R2) in the form of two two node FCI in primary with async AG to get the data over to the second FCI. (DR is all SAN mirroring with identical boxes in DR site)

Can I take the 2008 R2 primary and log ship all my databases to the SQL Server 2012 FCI primary while it is in an AG?

If not, I can leave the AG off and just log ship to do the migration, go live and setup the AG after the fact, that's not the end of the world. Just trying to see if I can skip the this setup.

If you want to ensure you minimize the amount of time you are without HA during your migration, here's a trick I recently used:

Set up the AG with a "dummy" database so that your AG is ready in advance.
Log ship the database(s) to both your primary & secondary AG replicas--at this point, the log shipped database is not part of the AG, you're just doing the log shipping in parallel to all nodes.
When you're ready to cut-over:

Take your final tail log backup on the old server.
Restore the tail log to both AG replicas, and on the primary node, restore WITH RECOVERY.
Take a log backup from the primary node, and restore it to secondary WITH NORECOVERY. (At this point, your secondary node is pre-seeded and ready to be added to the AG)
Add the DB to the AG, without having to do backup/restore.


reading input parameter values in stored procedures I'm using the current version of MariaDB 10.0.12 .

I created a stored procedure as specified in the accepted answer for the following question:

https://stackoverflow.com/questions/2950054/let-mysql-users-create-databases-but-allow-access-to-only-their-own-databases

In order to do that, I logged in as root using the mysql-client and typed in the following commands:

-> create user 'myUser'@'localhost';
-> create database myStoredProcedures;
-> grant execute on myStoredProcedures.* to 'myUser'@'localhost';
-> use database myStoredProcedures;
-> delimiter //
-> CREATE
->   DEFINER = CURRENT_USER
->   PROCEDURE myuser_create_db (IN dbName VARCHAR(255))
->   SQL SECURITY DEFINER
->   BEGIN
->     CREATE DATABASE dbName;
->     GRANT ALL PRIVILEGES ON dbName.* TO 'myUser'@'localhost';
->   END;
-> //
-> delimiter ;

If I log in as myUser@localhost, use the database myStoredProcedures, and then call the stored procedure:

call myuser_create_db('testit');

The database dbName is created and I can drop it as myUser. However the database' name is literally 'dbName' and not 'testit'! Why is this the case and how can I fix this?

( And now that I think about it ... would it be possible to use sql injection here? Is it possible to call myuser_create_db with a parameter that contains sql commands within the new db name? That would be bad. But I cannot test this sql injection idea now, because MariaDB interprets dbName as the string 'dbName'. )

Thanks.

Look here for a possible answer to this question.

https://dba.stackexchange.com/questions/73931/prevent-sql-injection-inside-stored-procedure

But the answer there is also a follow-up question.


Downside of large "max connections" in a connection pool from a web app I understand that if you don't pool database connections at all, then you bear the overhead of establishing a new database connection every time you need to query the database, which will make your app slow, and that you save that overhead by pooling.

If you set your max pool size too small - let's say, to 1 connection max - then the downside of that is your app has to share that single database connection for all requests, and requests will have to wait for the previous one to finish with the database connection before they can fetch it from the pool and re-use it.

But what if you just set your max pool size really big, like 1000 connections max. Shouldn’t it then favour re-using connections over establishing new ones, and only max-out the pool size if it actually needs to in order to service demand?

What's the down side of making connection pools bigger than they need to be to service the app's demand, assuming unused connections are closed / removed from the pool after an idle timeout?

I understand that if you don't pool database connections at all, then you bear the overhead of establishing a new database connection every time you need to query the database, which will make your app slow, and that you save that overhead by pooling.

Generally, but it depends... If it's a long lived process or connection, a dedicated connection can be more performant and less overhead.

  If you set your max pool size too small - let's say, to 1 connection max - then the downside of that is your app has to share that single database connection for all requests, and requests will have to wait for the previous one to finish with the database connection before they can fetch it from the pool and re-use it.

They could also get an error. I wouldn't assume it'll multiplex perfectly. This is why setting the maximum value too low can have a detrimental effect (also setting it too high!).

  But what if you just set your max pool size really big, like 1000 connections max. Shouldn’t it then favour re-using connections over establishing new ones, and only max-out the pool size if it actually needs to in order to service demand?

I'd say, if 1000 concurrent connections doesn't break anything in your app/database processing then go for it. Realistically, unless this app becomes extremely popular or there is an issue in the app/database there shouldn't be a need to hit 1000 concurrent requests from the same pool on the same app server. If it is, chances are you'll kill the app server before the database server (maybe not, depends on hardware and config) or there will be some concurrency issue at the database level (again, depends).

This will allow the pool to grow but hopefully not overwhelm the database server. If you had 10 app servers all set to this, then 10k connections may be an issue. There are a good amount of moving parts, even in this simple example but I'm trying to be optimistic.

  What's the down side of making connection pools bigger than they need to be to service the app's demand, assuming unused connections are closed / removed from the pool after an idle timeout?

The downside is that you could:

Overwhelm the database
Overwhelm the application server
Have high overhead for setting up/tearing down connections

Eventually the connections will be removed and freed, but setting them up takes resources and time. The point of the pool is to reuse those resources to reduce the cost and resource usage, overall.


Minimising downtime implementing merge replication on live system Background

We have three Servers running SQL Server 2008 which all contain a Product database. These are currently kept in sync once per day by sending a copy of the MDF to the two Subscribing servers and detaching the existing DB and replacing it with the latest copy.

The two subscribers contain a Customer database which is not present on the publisher. The Customer database contains customer purchase information, so it needs to reference ProductIDs in the Product table.

We have implemented Merge Replication on a similar set up in our testing environment and are preparing to implement these changes on our live system.

The end result should look something like:

The Problem

In order to implement these changes, we will need to generate a snapshot and synchronise the data between the Publisher and the Subscribers. While this is occurring, any client application which relies on the data in the Product database will fail, and any Stored Procedures on the Customer database which make cross database joins will also fail.

We'd like to keep this downtime to a minimum or eliminate it completely.

We've had some crazy ideas thrown around which may or may not work (using synonyms to point a subscriber to another server's Product table while snapshot generation/synchronisation takes place) but they don't seem very elegant, if they'll work at all. I'm hoping someone else has had a similar issue and might have some insight :)

The biggest time saving option is to generate the snapshot and compress it with your favorite program, like 7-Zip, copy the compressed snapshot to the subscriber using file-copy or FTP, and then apply it locally using the -AltSnapshotFolder Merge Agent parameter.  The existing Product database can be left in place while the snapshot is being copied over and client applications can operate normally.

The way this works is open the SQL Agent job for the Subscriber you wish to deploy the snapshot to and get the Run Agent. command.  It looks something like this:

-Publisher [PACIFIC] -PublisherDB [TestDB2] -Publication [TestMergePub1] -Subscriber [PACIFIC] -SubscriberDB [TestSubDB2] -Distributor [PACIFIC] -DistributorSecurityMode 1

Then, grab the snapshot files from the publisher and compress it.  For example, on my server I compress the snapshot folder found in C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\repldata\unc.  I then copy the snapshot folder to the subscriber using file-copy or FTP and decompress it in a folder called C:\TEMP\unc.

So looking at this on the Subscriber I would see this for the snapshot folder:

C:\TEMP\unc\PACIFIC_TESTDB2_TESTMERGEPUB1\20130207200123

Then from the command line on the Subscriber, run this:

c:\Program Files\Microsoft sql server\100\com\replmerg.exe -Publisher [PACIFIC] -PublisherDB [TestDB2] -Publication [TestMergePub1] -Subscriber [PACIFIC] -SubscriberDB [TestSubDB2] -Distributor [PACIFIC] -DistributorSecurityMode 1 -AltSnapshotFolder C:\TEMP

This will apply the snapshot locally and will be significantly faster then applying it over the wire.  Downtime at the Subscriber will be minimal.


SQL only the first row of each value MYSQL version 5.7.31
How do I make it show the distinct rows of the value/column mainthreadid with the highest value of date?
Note that the data is filtered using
WHERE approved = 1 AND section != 150

A fiddle is available here.
Fiddle DDL and DML
Table
CREATE TABLE forum (
    id int,
    mainthreadid int,
    section int,
    approved int,
    date int(20),
    title varchar(255),
    text varchar(255)

);

Sample Data
INSERT INTO forum (id, mainthreadid, section, approved, date, title, text)
VALUES (1, 1, 5, 1, 1000, "title1", "text1");
INSERT INTO forum (id, mainthreadid, section, approved, date, title, text)
VALUES (2, 2, 5, 1, 1000, "title2", "text2");
INSERT INTO forum (id, mainthreadid, section, approved, date, title, text)
VALUES (3, 2, 5, 1, 1001, "title3", "text3");
INSERT INTO forum (id, mainthreadid, section, approved, date, title, text)
VALUES (4, 2, 5, 1, 1002, "title4", "text4");
INSERT INTO forum (id, mainthreadid, section, approved, date, title, text)
VALUES (5, 2, 5, 1, 1003, "title5", "text5");
INSERT INTO forum (id, mainthreadid, section, approved, date, title, text)
VALUES (6, 2, 5, 1, 1004, "title6", "text6");
INSERT INTO forum (id, mainthreadid, section, approved, date, title, text)
VALUES (7, 2, 5, 0, 1005, "title7", "text7");
INSERT INTO forum (id, mainthreadid, section, approved, date, title, text)
VALUES (8, 8, 150, 1, 1004, "title8", "text8");
INSERT INTO forum (id, mainthreadid, section, approved, date, title, text)
VALUES (9, 1, 5, 1, 1006, "title9", "text9");
INSERT INTO forum (id, mainthreadid, section, approved, date, title, text)
VALUES (10, 1, 5, 1, 1005, "title10", "text10");

SQL Statement
SELECT date, id, mainthreadid
FROM `forum`
WHERE approved = 1 AND section != 150 ORDER BY date DESC

Resultset
My desired result set would be:
date    id  mainthreadid
1006    9   1
1004    6   2

Maybe you need in
SELECT forum.*
FROM forum
JOIN ( SELECT mainthreadid, MAX(`date`) `date`
       FROM forum
       GROUP BY mainthreadid ) lastdates USING (mainthreadid, `date`)

?
fiddle
For each mainthreadid only last row (the row with greatest date) is selected. If there exists more than one such row (the same maximal date) then all of them will be returned.

you dont include checks for approved = 1 and if section != 150

No problems.
SELECT forum.*
FROM forum
JOIN ( SELECT mainthreadid, MAX(`date`) `date`
       FROM forum
       WHERE approved = 1 AND section != 150
       GROUP BY mainthreadid ) lastdates USING (mainthreadid, `date`)

fiddle


How to tell what's failing at making backups? I have an instance of SQL server that is regularly getting errors like this:

02/25/2013 23:22:38,spid138,Unknown,BackupVirtualDeviceFile::SendFileInfoBegin:
failure on backup device '{E0EA0FA7-D8FA-401B-8B4D-7C479F32A35D}21'.
Operating system error 995(The I/O operation has been aborted because of either a thread exit or an application request.).

and this:

02/25/2013 23:22:38,Backup,Unknown,BACKUP failed to complete the command BACKUP DATABASE master.
Check the backup application log for detailed messages.

According to our backup administrator, our Data Protector backups are A) running at a different time and B) running successfully. If that's the case, then I've got some rogue service attempting backups and failing.

Either way, I need figure out what's causing the errors. So, is there any way to track or log what entities are trying to make backups?

You can capture backup events using SQL Server Audit.

USE master;
GO

CREATE SERVER AUDIT ServerAudit
  TO FILE (FILEPATH = 'C:\temp\', MAXSIZE = 1 GB)
  WITH (ON_FAILURE = CONTINUE);
GO

ALTER SERVER AUDIT ServerAudit
  WITH (STATE = ON);
GO

CREATE SERVER AUDIT SPECIFICATION BackuAudit
  FOR SERVER AUDIT ServerAudit
  ADD (BACKUP_RESTORE_GROUP);
GO

ALTER SERVER AUDIT SPECIFICATION BackuAudit
  WITH (STATE = ON);
GO

Then you can view the Audit Logs from within SSMS (Security > Audits).

If you can't use audit (e.g. due to edition restrictions), you can use a server-side trace:

declare @rc int, @TraceID int, @maxfilesize bigint = 5;

exec sp_trace_create @TraceID output, 0, N'c:\temp\backup_trc', @maxfilesize, NULL;

exec sp_trace_setevent @TraceID, 115, 1,  1;
exec sp_trace_setevent @TraceID, 115, 9,  1;
exec sp_trace_setevent @TraceID, 115, 3,  1;
exec sp_trace_setevent @TraceID, 115, 4,  1;
exec sp_trace_setevent @TraceID, 115, 5,  1;
exec sp_trace_setevent @TraceID, 115, 6,  1;
exec sp_trace_setevent @TraceID, 115, 7,  1;
exec sp_trace_setevent @TraceID, 115, 8,  1;
exec sp_trace_setevent @TraceID, 115, 10, 1;
exec sp_trace_setevent @TraceID, 115, 11, 1;
exec sp_trace_setevent @TraceID, 115, 12, 1;
exec sp_trace_setevent @TraceID, 115, 14, 1;
exec sp_trace_setevent @TraceID, 115, 21, 1;
exec sp_trace_setevent @TraceID, 115, 23, 1;
exec sp_trace_setevent @TraceID, 115, 26, 1;
exec sp_trace_setevent @TraceID, 115, 28, 1;
exec sp_trace_setevent @TraceID, 115, 29, 1;
exec sp_trace_setevent @TraceID, 115, 34, 1;
exec sp_trace_setevent @TraceID, 115, 35, 1;
exec sp_trace_setevent @TraceID, 115, 37, 1;
exec sp_trace_setevent @TraceID, 115, 40, 1;
exec sp_trace_setevent @TraceID, 115, 41, 1;
exec sp_trace_setevent @TraceID, 115, 49, 1;
exec sp_trace_setevent @TraceID, 115, 50, 1;
exec sp_trace_setevent @TraceID, 115, 51, 1;
exec sp_trace_setevent @TraceID, 115, 60, 1;
exec sp_trace_setevent @TraceID, 115, 64, 1;

exec sp_trace_setstatus @TraceID, 1;

select TraceID = @TraceID;

I've probably captured more columns there than you need...


Does MySQL support querying for the current running transaction id PostgreSQL supports retrieving the current running transaction id using a query like this:

select txid_current();

Does MySQL have any such equivalent?

Starting with MySQL 5.7, you can see the current transactions in the performance schema.

See http://dev.mysql.com/doc/refman/5.7/en/events-transactions-current-table.html

Now, to find out the THREAD_ID of the current connection, use

SELECT THREAD_ID FROM performance_schema.threads
  WHERE PROCESSLIST_ID = CONNECTION_ID()

See http://dev.mysql.com/doc/refman/5.7/en/threads-table.html


Why would adding a column to a view corrupted a table-valued function? I have a TVP myTVP implemented as select val.* from vw_MyView val.  The other day, I modified vw_MyView, replacing:
select Foo,Bar from DB with
select Foo, Bash, Bar from DB.

This had a strange side effect: Calling select Bar from myTVP() returned a column named Bar populated with the contents of Bash (despite the fact that Bar and Bash don't even have the same type).

Anonymized plan demonstrating the problem: https://www.brentozar.com/pastetheplan/?id=ryJLJmqdl

Note that Object2 and Object1 are identical, but the output list of the left-most Nested Loop is completely different between the two plans.

Notes:

There are no plan hints in use.  No trace flags are set.
CheckDB found no errors.
Using option(recompile) did not fix the issue.
Running right-click alter on the TVP with no changes fixed the issue.
Running right-click alter on the TVP with a name change fixed the issue, but the new TVP was identical to the previous other than the name.
Using SQL 2014 Standard Edition.

Questions:

Is it normal for adding a new column to a view to change which column
of the view is used in by other references to that view?
Under what circumstances will adding a column to a view cause such behavior?

Yes, this is expected. Functions and views that use SELECT * end up storing part of the metadata themselves relating to the underlying columns, so it is very easy for them to get confused. I talk about this problem in the following post from 2009:

Bad habits to kick : using SELECT * / omitting the column list

And also in my recent GroupBy presentation:

T-SQL : Bad Habits and Best Practices

How do you avoid this problem?

Don't use SELECT * in objects.
Make your functions and views WITH SCHEMABINDING, which means you can't modify the underlying objects without also modifying the objects that reference them (this also has the side effect of prevent * so win-win).
In this specific case, you can fix the object so that Bar returns the correct data by using:

EXEC sys.sp_refreshsqlmodule @name = N'dbo.myTVP';

Incidentally, the documentation for sys.sp_refreshsqlmodule describes this exact scenario:

  Updates the metadata for the specified non-schema-bound stored procedure, user-defined function, view, DML trigger, database-level DDL trigger, or server-level DDL trigger in the current database. Persistent metadata for these objects, such as data types of parameters, can become outdated because of changes to their underlying objects.

As an aside, you should never use FROM myTVP() but instead always specify the schema, e.g. FROM dbo.myTVP(). I talk about that in an old post, too.


Possible to have nested inserts in Postgres 8.4? I'm trying to import data into an inventory type database. Here's what I have for the tables (truncated for brevity):

create type hostrole as enum ('Physical', 'Virtual', 'Hypervisor', 'Other');

create table host (
  id serial primary key,
  hostname varchar(40),
  role hostrole
);

create table interface (
  id serial primary key,
  name varchar(20),
  host int references host(id) on delete cascade,
  mac macaddr
);

I know I can nest a select into an insert statement, my question is whether I can nest an insert into an insert (and if so, how to do so). Here's the statement I'm trying to run:

insert into interface
  (name,
   mac,
   host)
     values
  ('eth0',
   '00:50:56:9d:34:d4',
   (insert into host (hostname, hostrole) values ('foobar', 'Virtual') returning id)
);

The error comes up at the "into" of the nested insert. Can I do this in one shot, or do I have to make separate queries to do this insert?

You should be able to do something like this with a writable CTE:

WITH i AS (
   INSERT INTO host (hostname, hostrole) VALUES ('foobar', 'Virtual') RETURNING id
)
INSERT INTO interface (name, mac, host)
SELECT 'eth0', '00:50:56:9d:34:d4', id
FROM i

(untested, but it should be something like that)

Writable CTE is in PostgreSQL 9.1 and up.


need help understanding the error-message: Error Connecting to database FATAL : no pg_hba.conf entry We're trying to connect to a postgres/postgis installation on a remote server, using QGIS-Application. When accessing from another pc or from the same pc via pgadmin4v4 with the same credential/authentification settings, it works just fine. Therefore I conclude the pg_hba.conf is ok and it's in fact a problem of QGIS.

In order to better understand the problem and maybe file a bug for QGIS I need help analysing the error message. It sais:

Area: dbname = <databasename>, host <server-ip>, port 5432, user <username>, password <password>, authcfg = <id_of_auth_record>

SSL error: certificate verify failed
FATAL: no pg_hba.conf entry for host <some_other-ip>, user <username>, database <databasename>, SSL off

My main questions:

shouldn't the host-ip addresses be identical? Could it be part of the problem, that the second ip mentioned is not the correct server-ip? As far as I can see, there should be only one host (remote server) and one client involved (pc trying to get access).
why doesn't it say "no pg_hba.conf entry for client ..."
does QGIS think is it is a certificate problem, or a pg_hba.conf problem, or is that both the same

Update: further Details about the installation

Client-PC1: Win10, QGIS 3.4 [error] pgAdmin 4v4 [access ok]
Client-PC2: Win10, QGIS 3.10 [access ok] pgAdmin 4v4 [access ok]
Server: postgres 12
pg_hba.conf:

# Database administrative login by Unix domain socket
local   all             postgres                                peer

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    all             all             0.0.0.0/0               scram-sha-256
# IPv6 local connections:
hostssl all             all             ::0/0                   scram-sha-256

The no pg_hba.conf entry message is a generic message that means that something did not work out with the connection attempt. PostgreSQL uses a generic message, so that an attacker cannot draw conclusions from the kind of message she gets.

The true problem is probably:

SSL error: certificate verify failed

That would mean that client and server attempt to start an encrypted connection, but the client does not recognize the server certificate (because the certificate that was used to sign the server certificate is not a certification authority that the client recognizes).


How to get return value from pl sql procedure and assign it to variable? Code:

   execute x.procedurename(row.accc_no, row.bill_no, 0, null, row.total_balance, 'A',assgn_scen_site_cv, :assgn_scen_site_cv);

Its my procedure.

How to get return value from this proc and assign it to variable ?

You can create it like this:

CREATE OR REPLACE PROCEDURE procedurename(param1 NUMBER, param2 varchar(20), returnvalue OUT NUMBER);
IS
BEGIN
    ... your code
END;

And then use it like this:

returnvalue NUMBER;
procedurename(0, 'xxx', returnvalue);
dbms_output.putline(returnvalue);

You can look at CREATE PROCEDURE  documentation at Oracle website.


Unable to create stored procedure to access excel files in SQL Server? I need to create a stored procedure to access Excel files in SQL Server. I have created the below stored procedure. Since I am a sysadmin, the stored procedure is working fine for me.

But for the users who do not have SA privileges they are getting an error.

Could some one assist me on this, how the users can execute the stored procedure with my credentials (SYSADMIN)?

create procedure [dbo].[readexcel]
    (@filepath varchar(max))
as
    declare @Statement varchar(max)
    --set @Statement =
    EXECUTE('select * FROM OPENROWSET(' +
        '''Microsoft.ACE.OLEDB.12.0'',' +
        '''Excel 12.0;Database=' + @filepath +''',' +
        '''SELECT * FROM [Sheet1$]'')');

Error message:

  Ad hoc access to OLE DB provider 'Microsoft.ACE.OLEDB.12.0' has been
  denied. You must access this provider through a linked server

The good news is it CAN be done.  The bad news is you are opening a security hole.

Create a new database for this SP.
Make the owner of the database a sysadmin.
Set the database to trustworthy
Create your SP in the database using EXECUTE AS OWNER
For the people you want to be able to run this grant ONLY the following permissions.

CONNECT (create them a user)
EXECUTE on the stored procedure.

The reason for moving it off to it's own database & dramatically restricting permissions there I hope is fairly obvious.  By setting things up this way anyone who can run the stored procedure is in effect a sysadmin.  Now it's restricted to the code in the SP.  But what happens if someone gets db_owner access?  They can now write SPs that can grant them sysadmin.  Note: db_owner is a simple example.  There are lower permissions that could cause problems also.


SQL Profiler - Insert values? I setup an SQL profiler to run, but I am not seeing any values for updates/inserts.  Is there a way to see them?

All I see is stuff like this:

insert into some_tbl(id, col1, col2, col3) values ( @P0 , @P1 , @P2 )

I would love to actually see the values being inserted rather than just a generic variable, but can't seem to figure it out?

You can select SQL:StmtStarting and SP:StmtStarting along with SQL:BatchStarting or SP:Starting

Better to use server-side trace rather than using GUI.


how long can your replica be offline when using repmgr with Barman? I'm setting up some postgres database EC2 instances that I'd like to use for load balancing.  The application I'm running has some very expensive and very unique queries so CPU usage is a concern.  While a single instance is OK most of the time, I'd like to quickly be able to spin up some read replicas when we're expecting to process a lot of transactions.
The issue is that it could be days or weeks in between needing to bring up these machines.  Since we're using repmgr with Barman, it's very quick to clone a server.  But ideally we'd just like to start\stop instances as needed with little thought\overhead.
My question is, when a replica comes back online after being offline for a while, and the WALs on the primary have long vanished...  Is repmgr on the replicas smart enough to know to get the backup data from barman?  I would have initially thought no, except I had a replica offline for a week, brought it online and when I checked the database, it was in sync with the primary. pg_wal only had 2 days of wals on primary.
I do have restore_command='/usr/bin/barman-wal-restore barman node1 %f %p' but I thought that was more for initial cloning or recovery.

If restore_command is set to restore from an archive source (in this case Barman), the replica will attempt to fetch any WAL not available on the primary (or upstream in the case of cascading replication) from that source. So in this case as long as Barman has the WALs for the period the replica was offline, it will catch up.
You'll need to check the Barman configuration to see how long it is configured to retain backups/WAL, which will limit how long a replica can be safely offline for. Execute barman show-server $SERVER (probably node1) to list the active configuration (might take a few seconds to run) and look for retention_policy and wal_retention_policy.

I do have restore_command='/usr/bin/barman-wal-restore barman node1 %f %p'
but I thought that was more for initial cloning or recovery.

If the initial cloning is from Barman, it basically rsyncs the latest full backup from the Barman server. The restore_command will then fetch any WAL not available on the primary/upstream from Barman, and it is left in place as a backup source of WAL for situations (like the case described here) where streaming replication is interrupted for whatever reason.


Count total number of nested one to many relationships Tables: Users, Costs, Payments

Users can have many costs. Costs can have many payments

I want to get information about the user AND count of all payments that they made

This query returns result but takes very long time to run

SELECT u.id, u.name,
  (
    SELECT COUNT(p.id)
    FROM payments AS p
    WHERE p.cost_id IN (SELECT cost_id FROM costs WHERE user_id = u.id)
  ) AS payments_made
FROM users as u;

I tried outer joins but then it return duplicate if user has more than one cost

Inner join dropped all users that didn't have a cost

Let me know if this covers your desired result.

create table users (user_id int primary key, name varchar(20));
insert into users values(1, 'john'), (2, 'anne'), (3, 'peter');

create table costs(cost_id int primary key, user_id int);
insert into costs values (1, 1), (2, 1), (3, 2);

create table payments(payment_id int primary key, cost_id int);
insert into payments values
(1, 1), (2, 1), (3, 1), (4, 2), (5, 2), (6, 3), (7, 3);
GO

13 rows affected

select   c.user_id, count(*) num_payments
from     payments p
join     costs c
on       p.cost_id = c.cost_id
group by c.user_id
;
GO

user_id | num_payments
------: | -----------:
      1 |            5
      2 |            2

select    u.user_id, u.name, coalesce(p.num_payments, 0) num_payments
from      users u
left join (select   c.user_id, count(*) num_payments
           from     payments p
           join     costs c
           on       p.cost_id = c.cost_id
           group by c.user_id) p
on         u.user_id = p.user_id
GO

user_id | name  | num_payments
------: | :---- | -----------:
      1 | john  |            5
      2 | anne  |            2
      3 | peter |            0

dbfiddle here

Or better a left join solution:

select    u.user_id, u.name, count(p.payment_id) num_payments
from      users u
left join costs c
on        u.user_id = c.user_id
left join payments p
on        p.cost_id = c.cost_id
group by  u.user_id, u.name
;
GO

user_id | name  | num_payments
------: | :---- | -----------:
      1 | john  |            5
      2 | anne  |            2
      3 | peter |            0

dbfiddle here


RDS Postgres Peformance I have recently set up a new RDS instance to test out working with a AWS postgres instance vs a heroku postgres instance.  What I've found is that when I load an instance of 1000 entries in RDS its taking 100ms to read where when I do the same thing in heroku its taking 20ms.

I imagine its something I did wrong during set up.  I followed https://reinteractive.net/posts/128-heroku-app-backed-by-an-aws-rds-postgres-database for the set up.

Is there something I missing that I should have done after setup?

RDS Build:
Instance: db.m3.xlarge
Ram: 15GB
Storage: Purpose (SSD)
IOPS: disabled
StorageAmount: 100 GB
Multi-AZ: NO

Heroku Build:
1 GB RAM
64 GB storage

So I came to find out that my heroku instance wasn't in the same availability region as my rds instance.  Once I changed it over to east coast the RDS DB was slightly quicker then my heroku DB.

Incase anyone is stuck on how to create a rds instance in a different region through aws console, you need to click on region (i.e. Oregon) button in the top right corner and then create your instance.


How can I decide Rebuilding Index with DETAILED or SAMPLED on SQL Server I want to rebuild my indexes and statistics on DB.

How can I select this option on production maintenance.

SQL Server Version : Microsoft SQL Server 2012 - 11.0.2100.60 (X64)  (Build 7601: Service Pack 1)

I would prefer to rebuild the indexes where the fragmentation percent of the indexes is greater than 30% and a Re-org of indexes where fragmentation percent is in between 9% and 30%.

Coming to update stats, I would prefer to do it with Full Scan depending on the size of the databases.

While these are resource intensive database operations, I would prefer to do these operations over the weekends or after business hours.

For Production Environments, I would usually setup jobs and allow them to run over the weekend.

Please go through the below link for maintenance plans(very useful) and customize accordingly to your environment.

https://ola.hallengren.com/


how to correctly return values from a function I would like to create a function that queries a user table by passing a username and a password, the function is supposed to return 'Y' if the username / password are correct and 'N' if they're not.

the return values is initialized to 'N' and i check if the function found a match using the SQL%FOUND to assign a 'Y' to teh return variable.

i get the following error: 06503. 00000 -  "PL/SQL: Function returned without value"

CREATE OR REPLACE FUNCTION VERIFY
                     ( userName IN VARCHAR2,
                       userPassword VARCHAR2
                     )
                  RETURN VARCHAR2
AS
returnVar VARCHAR2(1):='N';
userID number;
zip number;
BEGIN
   SELECT userId,SUBSTR(ZIPCODE, 1,3)
    INTO userID,zip
FROM users
WHERE username = userName AND PASSWORD = userPassword;
 if SQL%FOUND then
   returnVar:='Y';
 end if;
return returnVar;
EXCEPTION
     WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('NO DATA FOUND');
END VERIFY;

NOTE: also in case you are wondering why i select the userID and the zip: when the function works fine and tested, i will include it into a package inorder to make the userID and Zip Code available globally, this is a requirement of the exercise i am working on.

You should return something when nothing is found. Before exception comment in the statement.
The way your routine is written it may does find nothing and then hits the end of the function without returning anything, hence the error function returned without value.
Workaround :
Handle it with the exception

exception
   when TOO_MANY_ROWS then
        return 'Error bla bla '; -- do something meaningful here
   when NO_DATA_FOUND then
        return null; -- if you want to return null on nothing found


Periodic Oracle restarts...intentionally Taking on a new dba role for an Oracle 11gR2 2 node RAC, I've been told that about 9 times a year, in schedule with other software releases, we intentionally shutdown and restart the database.  It causes all sorts of havoc -  we have unbalanced nodes and jobs that ran fine before end up on a different node running poorly, re-caching of everything, etc.  The database struggles for a few days as we put out new fires.

When I inquire as to why, it 'helped in the past' but nobody knows exactly why.  Is there a valid reason?  Is there a known memory leak somewhere in the Oracle RAC configuration that makes this needed?  (That would be shocking.) A similar question for MS SQL Server indicates 'no', but Oracle is generally quite a bit different.

Short answer is NO. There is no such need to restart.


Turn Emails into Usernames With MySQL Query I am adding a username field to my database, and I want to populate it with the username portion of the email addresses the users have supplied...

I dont have many users, so I dont expect any conflicts.

What would a query look like to take the username portion of an email address and save it to the username column?

Thank you

I'd probably suggest doing it in the code that will be creating the insert statement but it can be done in the insert statement itself too.

INSERT INTO yourtable SET username = LOWER(SUBSTR(useremail,1,LOCATE('@',useremail) - 1))

I included the LOWER so the style of the user name would be consistent in case a person entered their email address in MixedCase.