How merge multi statement into one Select statement in sql How to merge multi statement into one select statement.

I Tried but getting error: "Only one expression can be specified in the select list when the subquery is not introduced with EXISTS."

below i have mentioned all 4 statements.

Statement 1

SELECT *
FROM   (SELECT ROW_NUMBER() OVER(
            ORDER BY
                CASE WHEN ISNUMERIC(ProductCode) = 0
                     THEN CAST(LEFT(RIGHT(ProductCode,LEN(ProductCode)-CHARINDEX('-',ProductCode)), patindex('%[^0-9]%', RIGHT(ProductCode,LEN(ProductCode)-CHARINDEX('-',ProductCode))+'.') - 1)  AS INT)
                ELSE ProductCode END
              , ProductCode
            ) AS Num1
          , ProductCode + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10) + ProductName AS ProductCodeName1
        FROM  BASE_Product
        WHERE IsActive = 1
        ) BASE_Product
WHERE  Num1 > 0 AND Num1 < (95 + 1)

Statement 2

SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY CASE WHEN ISNUMERIC(ProductCode) = 0 THEN CAST(LEFT(RIGHT(ProductCode,LEN(ProductCode)-CHARINDEX('-',ProductCode)), patindex('%[^0-9]%', RIGHT(ProductCode,LEN(ProductCode)-CHARINDEX('-',ProductCode))+'.') - 1)  AS INT) ELSE ProductCode END, ProductCode) AS Num2, ProductCode + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10) + ProductName AS ProductCodeName2 `
FROM BASE_Product WHERE IsActive = 1) BASE_Product WHERE Num2 > 95 AND Num2 < ((95*2) + 1)

Statement 3

SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY CASE WHEN ISNUMERIC(ProductCode) = 0 THEN CAST(LEFT(RIGHT(ProductCode,LEN(ProductCode)-CHARINDEX('-',ProductCode)), patindex('%[^0-9]%', RIGHT(ProductCode,LEN(ProductCode)-CHARINDEX('-',ProductCode))+'.') - 1)  AS INT) ELSE ProductCode END, ProductCode) AS Num3, ProductCode + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10) + ProductName AS ProductCodeName3
FROM BASE_Product WHERE IsActive = 1) BASE_Product WHERE Num3 > (95*2) AND Num3 < ((95*3) + 1)

Statement 4

SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY CASE WHEN ISNUMERIC(ProductCode) = 0 THEN CAST(LEFT(RIGHT(ProductCode,LEN(ProductCode)-CHARINDEX('-',ProductCode)), patindex('%[^0-9]%', RIGHT(ProductCode,LEN(ProductCode)-CHARINDEX('-',ProductCode))+'.') - 1)  AS INT) ELSE ProductCode END, ProductCode) AS Num4, ProductCode + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10) + ProductName AS ProductCodeName4
FROM BASE_Product WHERE IsActive = 1) BASE_Product WHERE Num4 > (95*3) AND Num4 < ((95*4) + 1)

I Tried

You should include what you tried in the question - any detail you can give helps us help you understand.

  How to merge multi statement into one select statement.

You don't say what output you actually expect so I'm only guessing here. I suspect you want the rows from each of those four queries to be output in one resultset, in which case you need the UNION set operator, see https://docs.microsoft.com/en-us/sql/t-sql/language-elements/set-operators-union-transact-sql for details.

For a generic example:

SELECT <columns> FROM <tables_or_views>
UNION ALL
SELECT <columns> FROM <tables_or_views>
UNION ALL
-- and so on...
UNION ALL
SELECT <columns> FROM <tables_or_views>
-- note: order by applied at the end to all outputs, not in individual SELECTs
ORDER BY <columns>

If you use UNION instead of UNION ALL and the queries output any duplicate rows, those duplicates will be filtered and only one output. This is basically done the same way as DISTINCT which involves a sort operation (which may not match the sort you have applied via ORDER BY) that for results of any considerable size will involve a dump to tempdb, so unless you know duplicates may be present and want them filtered out it is more efficient to use UNION ALL instead of just plain UNION.

Also unless you need a specific ordering, avoid specifying an ORDER BY clause as that will likely result in dropping the results to tempdb too.

More specifically for the queries you included above I suspect that there is a simpler way (from the query planners PoV so likely more efficient) using single SELECT with appropriate GROUPing and/or window functions instead of several statements UNIONed. You should describe more fully what you are actually trying to get out of this code, and neaten up the included code so it is more readable as I've tried to do a bit with the first example, and someone might help you do that.


Using Triggers in SQL Server new to SQL, I'm looking for a way to setup a Trigger that will update Table B when a new row is inserted on Table A. For basic understanding here are some of the columns in my tables:

TABLE A:
+--------------+--------------+
|order_id (PK) |order_details |
+--------------+--------------+

Table B:
+-------------+--------------+-------------+
|item_id (PK) |item_details  |order_id (FK)|
+-------------+--------------+-------------+

I'm using a stored procedure like this one to insert data into Table A. Obviously this procedure doesn't work but I wanted to show where my problem starts and maybe there is a better way besides a Trigger. The problem has been getting the order_id to use within the second INSERT.

/*Parameters for the first Insert Statement*/
@customer_id INT,
@track_num NVARCHAR(50),
@pckg_num NUMERIC,

/*Parameters for the second Insert Statement*/
@manuf_name NVARCHAR(50),
@model_name NVARCHAR(50),
@qty NUMERIC,
@notes NVARCHAR(MAX)

/*Parameter I need for the second statement that doesn't exist until the first statement is committed*/
@order_id INT

AS

BEGIN
/*First Insert*/
INSERT INTO Orders
    (customer_id, track_num, pckg_num)
VALUES
    (@customer_id, @track_num, @pckg_num)
/*Second Insert*/
INSERT INTO Items
    (manuf, model, qty, notes, order_id)
VALUES
    (@manuf_name, @model_name, @qty, @notes, @order_id)

END

I'd like to fire the trigger within the same procedure that would then update Table B with item details passed to the procedure from my web app and including the PK (order_id) from the newly created row in Table A as FK in Table B.

I've tried doing this other ways with no luck, came across Triggers and thought it seemed like a good choice. If you have other suggestions about different methods, please feel free! :) Thanks in advance!

Two main ways, neither involving a trigger (generally it's better to avoid triggers if you can) -

1) Use an OUTPUT clause with the first INSERT statement to pick out the generated identity value:

DECLARE @NewOrder TABLE (order_id INT);

INSERT INTO Orders
    (customer_id, track_num, pckg_num)
OUTPUT
    inserted.order_id INTO @NewOrder
VALUES
    (@customer_id, @track_num, @pckg_num);

INSERT INTO Items
    (manuf_name, model_name, qty, notes, order_id)
SELECT
    @manuf_name, @model_name, @qty, @notes, order_id
FROM @NewOrder;

2) Rather than declaring Orders.order_id with the identity attribute, create and use a sequence object for it (requires SQL Server 2012 or later):

CREATE SEQUENCE OrderIDSeq AS INT
    START WITH 1;

CREATE TABLE Orders (
    order_id INT
        PRIMARY KEY
        DEFAULT (NEXT VALUE FOR OrderIDSeq),
    //... other columns as before
);

GO

CREATE OR ALTER PROCEDURE AddOrder(
    @customer_id INT, @track_num NVARCHAR(50), @pckg_num NUMERIC,
    @manuf_name NVARCHAR(50), @model_name NVARCHAR(50),
    @qty NUMERIC, @notes NVARCHAR(MAX)) AS
BEGIN
    DECLARE @order_id INT = NEXT VALUE FOR OrderIDSeq;

    INSERT INTO Orders
        (order_id, customer_id, track_num, pckg_num)
    VALUES
        (@order_id, @customer_id, @track_num, @pckg_num);

    INSERT INTO Items
        (order_id, manuf_name, model_name, qty, notes)
    VALUES
        (@order_id, @manuf_name, @model_name, @qty, @notes);
END;


How to join the updated result set of multiple servers I have got 4 different servers and I want to get the free diskspace of all those in a single output.
  using xp_fixeddrives we will get the free space in each drive .

Its not possible to get into all servers and check it as above.So I am trying to execute all it a once.

for example the result set of one server is as follows

drive   free_mb
C       1816
D       2553
E       2882
F       6247
G       16936
H       7281
I       2395

As I am a starter to sql please do help me to sort out this issue .

Something like this should do it:

create table #Tmp
(
  Drive char(1),
  MBFree int,
  ServerName varchar(50)
)

insert into #Tmp(Drive, MBFree) exec Server1.master.dbo.xp_fixeddrives
update #Tmp set ServerName = 'Server1' where ServerName is null

insert into #Tmp(Drive, MBFree) exec Server2.master.dbo.xp_fixeddrives
update #Tmp set ServerName = 'Server2' where ServerName is null

select *
from #Tmp
order by ServerName, Drive

drop table #Tmp

Result:

Drive MBFree      ServerName
----- ----------- --------------------------------------------------
C     57859       Server1
D     384636      Server1
F     82353       Server1
C     4963        Server2
E     10605       Server2

Server1 and Server2 has to be Linked Servers in the server where you are executing the above statements.


Slow ORDER BY with LIMIT I have this query:

SELECT *
FROM location
WHERE to_tsvector('simple',unaccent2("city"))
   @@ to_tsquery('simple',unaccent2('wroclaw'))
order by displaycount

I'm happy with it:

"Sort  (cost=3842.56..3847.12 rows=1826 width=123) (actual time=1.915..2.084 rows=1307 loops=1)"
"  Sort Key: displaycount"
"  Sort Method: quicksort  Memory: 206kB"
"  ->  Bitmap Heap Scan on location  (cost=34.40..3743.64 rows=1826 width=123) (actual time=0.788..1.208 rows=1307 loops=1)"
"        Recheck Cond: (to_tsvector('simple'::regconfig, unaccent2((city)::text)) @@ '''wroclaw'''::tsquery)"
"        ->  Bitmap Index Scan on location_lower_idx  (cost=0.00..33.95 rows=1826 width=0) (actual time=0.760..0.760 rows=1307 loops=1)"
"              Index Cond: (to_tsvector('simple'::regconfig, unaccent2((city)::text)) @@ '''wroclaw'''::tsquery)"
"Total runtime: 2.412 ms"

But when I add LIMIT, the execution takes more than 2 seconds:

SELECT *
FROM location
WHERE to_tsvector('simple',unaccent2("city"))
   @@ to_tsquery('simple',unaccent2('wroclaw'))
order by displaycount
limit 20

Explain:

"Limit  (cost=0.00..1167.59 rows=20 width=123) (actual time=2775.452..2775.643 rows=20 loops=1)"
"  ->  Index Scan using location_displaycount_index on location  (cost=0.00..106601.25 rows=1826 width=123) (actual time=2775.448..2775.637 rows=20 loops=1)"
"        Filter: (to_tsvector('simple'::regconfig, unaccent2((city)::text)) @@ '''wroclaw'''::tsquery)"
"Total runtime: 2775.693 ms"

I think that it's some issue with ORDER BY and LIMIT. How can I force PostgreSQL to use the index and do the ordering at the end?

Subquery doesn't help:

SELECT *
FROM (
    SELECT *
    FROM location
    WHERE to_tsvector('simple',unaccent2("city"))
       @@ to_tsquery('simple',unaccent2('wroclaw'))
    order by displaycount
) t
LIMIT 20;

or:

SELECT *
FROM (
    SELECT *
    FROM location
    WHERE to_tsvector('simple',unaccent2("city"))
       @@ to_tsquery('simple',unaccent2('wroclaw'))
) t
order by displaycount
LIMIT 20;

My guess is that this would fix your query:

SELECT *
FROM   location
WHERE     to_tsvector('simple',unaccent2(city))
       @@ to_tsquery('simple',unaccent2('wroclaw'))
ORDER  BY to_tsvector('simple',unaccent2(city))
       @@ to_tsquery('simple',unaccent2('wroclaw')) DESC
         ,displaycount
LIMIT  20;

I repeat the WHERE condition as first element of the ORDER BY clause - which is logically redundant, but should keep the query planner from assuming it would be better off processing rows according to the index location_displaycount_index - which turns out to be much more expensive.

The underlying problem is that the query planner obviously grossly misjudges the selectivity and / or cost of your WHERE condition. I can only speculate why that is.

Do you have autovacuum running - which should also take care of running ANALYZE on your tables? Thereby, are your table-statistics up-to-date? Any effect if you run:

ANALYZE location;

And try again?

It can also be that the selectivity of the @@ operator is being misjudged. I would imagine that it is very hard to estimate for logical reasons.

If my query should not solve the problem, and generally to verify the underlying theory do one of these two things:

temporarily delete the index location_displaycount_index
temporarily disable basic index scans by running:

SET enable_indexscan = OFF;

The latter is less intrusive and only affects the current session. It leaves the methods bitmap heap scan and bitmap index scan open, which are used by the faster plan.
Then re-run the query.

BTW: If the theory is sound, your query (as you have it now) will be much faster with a less selective search term in the FTS condition - contrary to what you might expect. Try it.


Which Optimistic Concurrency Control method will work out best in the long run? These are the options I've thought of:

DotNet Dave:

UPDATE mytable set f1=newval where id=666 and f1 := f1ValueFromSelect

Charlie Checksum:

UPDATE mytable set f1=newval where id=666 and md5(concat(id,f1)) := checksumFromSelect

Timothy Timestamp:

UPDATE mytable set f1=newval where id=666 and mytimestampfield := timestampFromSelect

Veronica Version:

UPDATE mytable set f1=newval, version=version+1 where id=666 and version=versionFromSelect

I like Dave, as no schema changes required. Have to keep all of record's original data on client though. How to compare blobs?

Charlie is good in that less data to send to client. Not sure about performance with blobs/clobs

Tim and Veronica are good as pretty simple though may require schema change. Timestamp looks a bit more foolproof. How cross-platform is it?

Thanks!

Depends on your DBMS platform to some extent.  On the SQL Server based development I did back in the late Jurassic period we used timestamps as these are automatically updated when the record is written.

You load a record with:

select Foo
      ,Bar
      ,TS
  from FooTab
 where FooID = 666

And save with

update FooTab
   set Foo = @Foo
      ,Bar = @Bar
 where FooID = @FooID
   and TS = @TS

select @@rowcount as RowCount

... or some such, depending on where the concurrency was being handled.

Oracle doesn't have such a timestamp, so the Oracle stuff I did around the same era worked by incrementing a version counter.

My view about checksumming and comparing field values is it is fiddlier and therefore easier to make a mistake with, so it's best only used when you don't have the option of using a timestamp or version on the record.  And, yes, without applying any google-fu to the question, I can't think of a way to do it with BLOBs off the top of my head either.  You might be able to do the comparison separately with something like:

... and hashbytes ('MD5', BlobField) = @BlobHash

So, as an opinion, use timestamping or versioning if you have the option - it's much simpler.  You can do field comparisons or hashing if you don't have the option of changing the database, but it's more fiddly.


In MongoDB serverStatus(), what is the meaning of "connection data handles currently active"? When running db.serverStatus() in the MongoDB shell (3.4, WiredTiger), the results contain the following data:

{
    "connection data handles currently active" : 94862,
    "connection sweep candidate became referenced" : 0,
    "connection sweep dhandles closed" : 91885,
    "connection sweep dhandles removed from hash list" : 648514,
    "connection sweep time-of-death sets" : 1514067,
    "connection sweeps" : 40077,
    "session dhandles swept" : 110175,
    "session sweep attempts" : 710643
}

What is the meaning of "connection data handles currently active"? Does it have an impact on the instance's performance?

Although I can't find any evidence for that in the documentation, it is probably the open file-handles that Mongo holds against the file storage.

I did a test that supports this idea:

Creating new mongodb instance, "connection data handles currently active" minimum value is 11 (sometimes it becomes 12,13 and then back to 11).
db.coll.insert({"foo":"bar"}) - "connection data handles currently active" min value = 13.

I did the same with other operations that cause mongo to create new files on disk, such as index creation, and I have noticed the same behavior.
We can conclude that this value, "connection data handles currently active" is related to the number of file-handles that mongo holds.

As for the question if it has an impact on the instance's performance, in some cases it could be true according to the next discussion at Jira:
https://jira.mongodb.org/browse/SERVER-27700


MySQL UPDATE to replace text in LONGTEXT field I have a WordPress table wp_posts and I'd like to run an UPDATE that will replace a url from my old domain to my new domain.

For example, let's say that one record in the field post_content has the following content:

This is my old web site.

I'd like that to become:

This is my old web site.

I've tried the following query

UPDATE wp_posts AS w`
SET w.post_content = REPLACE(w.post_content, 'my-old-site.com', 'my-new-site.com');

but I don't get any result.

Any idea how to fix it?

There is an error in your query near

AS w`

Alias name "w" should instead be

`w`

Try this:

UPDATE wp_posts AS `w`
SET w.post_content = REPLACE(w.post_content, 'my-old-site.com', 'my-new-site.com');


How to count absence days from differents shift? I have a problem when in the counting of the employee absences depending on different shifts, so all employees do not have the same shift, and what I should get is the group and the absences of this group ( the group contains some employees ),
Groups table
GroupId    GroupName
  1         Science
  2         Physics

Employee table
EmployeeId      GroupId
   001             1
   002             1
   003             2
   004             2
   004             2

Working_days table
DayId    DayName
  1       Monday
  2       Tuesday
  3        ...

Timetable table
TimeTableId   grouptimeId  WorkingDayId
    55eds          22           1
    e8d5s          33           2

GroupTime table
GroupTimeId    TimeTableId
     1           55eds
     2           e8d5s

How can I get absences for every group except the holidays?
select groupId,(case when groupId = null then 0 else COUNT(*) end) as absence from (select groupId,
COUNT(*) as  absences1 from
(select distinct MONTh,DAY,e.groupId
from employee_c c,holiday hl,employee e,groups,Get_Calendar_Date(DATEADD(MONTH, DATEDIFF(MONTH, 0, CAST(DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0) AS datetime))+@Month-1, 0) ,DATEADD(MONTH, DATEDIFF(MONTH, -1, CAST(DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0) AS datetime))+@Month-1, -1))
where DATENAME(DD, Weekday) IN (select WorkingdayId+1 from timetable s,groups ds,grouptime de where dd.groupId=ds.groupId and dd.groupId = de.groupId and s.timetableId=de.timetableId and de.groupId=ds.groupdId) and  DATEPART(MM,hl.startDate)=@Month and
c.isActive=1
except

(select Month,Day,d.departmentId from department d,Get_Calendar_Date(DATEADD(MONTH, DATEDIFF(MONTH, 0, CAST(DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0) AS datetime))+@Month-1, 0) ,DATEADD(MONTH, DATEDIFF(MONTH, -1, CAST(DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0) AS datetime))+@Month-1, -1)),holiday hle,employee_c c,employee e where datepart(MM,hle.startDate)=@Month and cast(Date as date) between hle.startDate and hle.endDate and c.isActive=1 and d.groupId =e.groupId and c.employeeId=e.employeeId and c.isActive=1 )) sc group by Month,Day,groupId) s group by groupId

Get_Calendar function:
ALTER FUNCTION [dbo].[Get_Calendar_Date]
(
    @StartDate DATETIME
,   @EndDate DATETIME
)
RETURNS TABLE
AS

RETURN
(
    SELECT  Tbl_Obj.RNo
        ,   DATEADD(DAY,Tbl_Obj.RNo-1,@StartDate) AS [Date]
        ,   DATEPART(quarter,DATEADD(DAY,Tbl_Obj.RNo-1,@StartDate)) AS [Quarter]
        ,   DATEPART(dayofyear,DATEADD(DAY,Tbl_Obj.RNo-1,@StartDate)) AS [DayofYear]
        ,   DATEPART(WEEK,DATEADD(DAY,Tbl_Obj.RNo-1,@StartDate)) AS [WeekofYear]
        ,   DATEPART(YEAR,DATEADD(DAY,Tbl_Obj.RNo-1,@StartDate)) AS [Year]
        ,   DATEPART(MONTH,DATEADD(DAY,Tbl_Obj.RNo-1,@StartDate)) AS [Month]
        ,   DATEPART(DAY,DATEADD(DAY,Tbl_Obj.RNo-1,@StartDate)) AS [Day]
        ,   DATEPART(weekday,DATEADD(DAY,Tbl_Obj.RNo-1,@StartDate)) AS [Weekday]
        ,   DATENAME(MONTH,DATEADD(DAY,Tbl_Obj.RNo-1,@StartDate)) AS [MonthName]
        ,   DATENAME(weekday,DATEADD(DAY,Tbl_Obj.RNo-1,@StartDate)) AS [WeekdayName]
        ,   (RIGHT( REPLICATE('0',(4)) +
                CONVERT([VARCHAR],DATEPART(YEAR,DATEADD(DAY,Tbl_Obj.RNo-1,@StartDate)),0)
                ,(4)
             )+
             RIGHT( REPLICATE('0',(2)) +
                CONVERT([VARCHAR],DATEPART(MONTH,DATEADD(DAY,Tbl_Obj.RNo-1,@StartDate)),0)
                ,(2)
             )
            ) AS [Vintage]

    FROM    ( SELECT ROW_NUMBER() OVER (ORDER BY [object_id]) AS [RNo]
              FROM sys.all_objects WITH (NOLOCK)
            ) Tbl_Obj

    WHERE   DATEADD(DAY,Tbl_Obj.RNo-1,@StartDate) <= @EndDate
)

It seems like you may be going about things in a much more complicated way then they need to be. Generally for figuring out things like Holidays, Weekends, and other Date-centric Facts, a DateDimensions table makes things a lot simpler. There's a lot of articles out there on how to generate one, this article being a good example. Once you have a DateDimensions table, you can join to it in a similar way to this:
SELECT E.GroupId, COUNT(*) AS Abscences
FROM employee AS E
INNER JOIN @tablevarDepDays AS D -- Assuming this is where you store absences?
    ON E.employeeId = D.employeeId
INNER JOIN DateDimensions AS DD
    ON D.SomeDateField = DD.[Date] -- D.SomeDateField would be your absence date
WHERE DD.IsHoliday = 0 -- Filters out Holidays
GROUP BY E.GroupId

Note it would be more helpful if you could please include your table schema for all your tables involved. If so, I can update my answer to be less pseudo-code like.


How to increase the size of query audit log? I need to find the queries run against an Oracle instance.

On running the query:

SELECT
    first_load_time,
    sql_id,
    sql_fulltext
FROM
    v$sql
ORDER BY
    first_load_time ASC;

Only records from yesterday are displayed.

Running:

SELECT
    *
FROM
    dba_hist_sqltext;

Gives absolutely nothing.

What can I do to get sql history over a longer period of time, say 2 months?

For DBA_HIST_SQLTEXT, you need Enterprise Edition with the Diagnostic Pack. If you have that licensed, you can enable collection of historical data with:

alter system set control_management_pack_access='DIAG';

You can control the retention time of this data with:

begin
  dbms_workload_repository.modify_snapshot_settings
  (
    retention => 5184000
  );
end;
/

Where 5184000 is 60 days (5184000 minutes).


Why is the schema name necessary here and how to compile without it I'm working on an Oracle Forms 11g application linked with an Oracle 18c database and I stumbled upon a strange error when compiling my form.
Here's a snippet to illustrate the problem (I changed the identifiers name but it doesn't matter):
PROCEDURE SOME_PROCEDURE(...) IS
  v_arg1 relation.c_arg1%type;
  v_arg2 relation.c_arg2%type;
  v_arg3 relation.c_arg3%type;
BEGIN

  SELECT r.c_arg1, r.c_arg2, r.c_arg3
    INTO v_arg1, v_arg2, v_arg3
    FROM relation r
   WHERE ...

  -- Some more code ...

END;

When I'm compiling, the error I get is :
Compilation errors on SOME_PROCEDURE:
PL/SQL ERROR 302 at line 2, column 19
component 'C_ARG1' must be declared
PL/SQL ERROR 0 at line 2, column 10
Item ignored
PL/SQL ERROR 302 at line 3, column 19
component 'C_ARG2' must be declared
PL/SQL ERROR 0 at line 3, column 10
Item ignored
PL/SQL ERROR 302 at line 4, column 19
component 'C_ARG3' must be declared
PL/SQL ERROR 0 at line 4, column 10
Item ignored

relation here is a table name and is causing the problem. If, for testing purposes, I pick another table and field name, the code compiles fine e.g. v_arg1 other_table.other_field%type;.
I figured maybe relation is a reserved keyword (even though I couldn't find anything in the documentation about this specific keyword, yikes), so I tried to put the schema name admin before the table name:
PROCEDURE SOME_PROCEDURE(...) IS
  v_arg1 admin.relation.c_arg1%type;
  v_arg2 admin.relation.c_arg2%type;
  v_arg3 admin.relation.c_arg3%type;
BEGIN

And it works just fine. Now this is a problem, because admin is not a fixed name and will change because multiple schemas are used for multiple purposes ie development, testing, production, etc.
This is where I'm stuck. I don't know how to do this the "right" way: because the %type is a VARCHAR2 with some length, I'll use something larger than usual such as VARCHAR2(100) and hopefully it downcasts without errors in the future.
My question is, is it possible to come up with a way to reference this table without specifying the schema?
This is for my work, I don't have much freedom in changing the table/schema name, etc.
Thank you

Indeed relation is the name of a table. It must exist in the schema where you run this code (= belong to the user you are connected as).
Later you say that admin.relation works fine. This means that the table called relation belongs to a user called admin.
So ...

Either compile and run your code as admin (= connect as admin).
Or use fully qualified names and run the code from another user. Provided that user has the proper right (SELECT privilege) on table relation

Actually there is a third option that avoids the need to fully qualify the table name: have user admin create a public synonym to the table, like this:
create public synonym relation for relation;
or have a DBA do it:
create public synonym relation for admin.relation;
That way all users (provided they have the right to read that table) can refer to it by its name, without needing to fully qualify it with the name of the owner.


SQL Server 2014: Graceful shutdown of an Availability Group node What I'm trying to do is to gracefully shut down one of two nodes in our SQL Server Availiability Group, effectively draining all the connections.

Previously, we were just stopping the SQL Server instance on the node, but that results in a flurry of "The specified network name is no longer available" errors from client applications.

What is the kosher way of shutting down a node?

I'm no DBA here, so please, ask whatever questions you deem necessary.

You should failover to one of the other nodes first, before shutting down SQL.

This can be done either in SQL Management Studio (under the Always On High Availability node in the Object Explorer tree), or alternatively using the Windows Server Failover Cluster Manager program, which will be available in the Start Menu of all the Windows servers hosting the SQL instances.

Using SSMS is considered the better approach, as the WSFC utility won't know the synchronisation state of the other replicas.

To do a failover via TSQL instead, connect to the node you want to failover to (not from), and execute this, once for each listener group you have:

T-SQL : ALTER AVAILABILITY GROUP My_Group_Name FAILOVER;

In PowerShell you have this option: Switch-SqlAvailabilityGroup

(Source for code samples)


Mysql 8: count(*) on INT primary is 13500 times slower than supposed to be. any innodb dev here? I am aware there are many questions on this topic, mysql has not improved much on these single-query performance bugs in the past years but my case is more unusual.

If mysql would access the table index as it was supposed to do and count the entries it should take 70mil entries * 4 byte per entry = ~275MB at ~1000mb/sec = 270 milliseconds to count the entries
The actual mysql performance is about 13500 times slower than it should be.

Given that I have 100 gig of RAM for innodb I'd expect a subsequent count to be done in single digit milliseconds (not in again one hour)

I have the same behaviour on 3 servers, with slgihtly 3 different mysql versions on 5 different innodb tables, so it's not a localized issue.
I have had more severe cases than the current one (count takes about an hour, I've seen it going for days too).
In this case the table is compressed (which should actually increase the speed)

1590603 root    localhost       locdb1       Query   2004    Sending data    select count(*) from logos     1003611 0       0

EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "35130458.29"
    },
    "table": {
      "table_name": "logos",
      "access_type": "index",
      "key": "PRIMARY",
      "used_key_parts": [
        "id"
      ],
      "key_length": "4",
      "rows_examined_per_scan": 9851928,
      "rows_produced_per_join": 9851928,
      "filtered": "100.00",
      "using_index": true,
      "cost_info": {
        "read_cost": "34145265.49",
        "eval_cost": "985192.80",
        "prefix_cost": "35130458.29",
        "data_read_per_join": "2G"
      }
    }
  }
}

CREATE TABLE `logos` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `domain_name` varchar(64) COLLATE utf8_bin NOT NULL,
  `index_raw` mediumtext COLLATE utf8_bin,
  `a` mediumblob,
  `ab` varchar(3) COLLATE utf8_bin DEFAULT NULL,
  `b` mediumblob,
  `bb` varchar(3) COLLATE utf8_bin DEFAULT NULL,
  `last_error_code` smallint(6) DEFAULT NULL,
  `date_found` date NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=67945433 DEFAULT CHARSET=utf8 COLLATE=utf8_bin ROW_FORMAT=COMPRESSED

IBD Filesize is 370 GiB
Disk performance is reliable 24,000 IPS at max 1.2 GiB/sec
I've tried moving the table on a single non used disk and mysql was accessing it constantly at 4 MiB/sec (in that test case max speed would actually be 250MiB/sec at max 16k iops)
CPU is 20% loaded

The table in this case was freshly rebuilt, I've also tested this on freshly optimized tables without improvement.

I'd be happy with 5 seconds as well. I'm not happy with an hour.

Add INDEX(last_error_code), then do SELECT COUNT(*) FROM logos, it will run a lot faster because it will use the much smaller BTree for that secondary index.

SHOW TABLE STATUS LIKE 'logos' will give you an approximate count.

Why is the PK bloated?  InnoDB orders the data by the PRIMARY KEY; hence the PK, as an INDEX is essentially included in the data.  So to fetch a PK implies fetching the entire row.  (Caveat:  Bulky columns such as your texts and blobs are stored in a separate place, so they are not fetched when doing the COUNT(*).)

The Data+PK is in one B+Tree; each secondary index is in its own B+Tree.

Any "size" math (such as INT takes 4 bytes) needs to be multiplied by about 2 or 3 due to various overheads -- per column, per row, per block, etc.  And the block nature has its own overhead due to block splits.

If the entire table happens to be cached in RAM (that is, in the innodb_buffer_pool), then there won't be any I/O.  Still there is a non-trivial amount of CPU work to scan through your millions of rows.

I would guess that your table is slightly bigger than will fit in the buffer_pool.  So, every time you do that COUNT(*) it is competing with all other queries for room in the cache.  Not cool.

Note that the index I propose will take about 3GB, so it has a chance of being cached and perhaps even staying cached.  The first time (after a restart) you do the COUNT(*), it will be slow because of lots of I/O.  The second time (unless blocks have been bumped out of cache), it will be faster; perhaps ten times as fast.  That will probably be less than an hour, but more than a few seconds.

What is the value of innodb_buffer_pool_size?

How do others solve the sluggishness of COUNT(*)?  Have you noticed that search engines, if they say anything about 'how many', give you round numbers like "About 14,000,000 hits"?  That's one approach -- periodically get the exact number and round it off until you get around to checking again.

Or they have, say, daily subtotals.  Then the query SUMs up the daily counts.  This is much faster.  (But it only works for stuff that is continually coming in and not changing.)

Or ... (There are other techniques.)  What is your use case?

The idea of "random areas, plus extrapolation" probably has a lot of hassles; I would not embark on such.  Anyway, that is essentially what SHOW TABLE STATUS does.

MySQL 8.0.14 has Parallel scanning of by PRIMARY KEY (cf innodb_parallel_read_threads) for COUNT(*) w/o WHERE.  That will provide a little speedup for your original query.


SQL Server Job Powershell is failing I have a SQL Server 2014 instance job that runs nightly that moves backups from the local drive to a network drive. I wrote it using PowerShell.

$mypath = $('F:\Backup\CRMDB01$CRMDB012014\Prod\Full\')
$mydestination = '\\stagingshare\fromprod\'
$myfile = gci $mypath | sort LastWriteTime | select -last 1
copy-item -path ($mypath + $myfile) -destination $mydestination

The job is failing with "reason: line(1): Syntax Error". I have it set to "Run as:" proxy_1.

proxy_1 has a credentialed name next to it that has access to the share. I don't have any principals selected.

What am I missing?

The syntax error you are getting is from the $() this is taken as a token in SQL Server Agent context...so it will always bark at that; removing it should fix the syntax error.

On the copy issue you will likely need to specify the property you want to pass into Copy-Item. Just passing $myfile is not going to work because that contains the full object of the output from gci. So change it to something like:

$myfile = gci $mypath | sort LastWriteTime | select -last 1 -ExpandPropety FullName
Copy-Item -path $myfile -destination $mydestination

Now $myFile will contain the full path to your last file:


Integration Services License requirements Not sure this is something which people will know on here are not.

We currently have a BI setup where we have Integration services on one SQL instance and the data warehouse database on a separate SQL Instance.

My question is, as no users physically connect to the Integration services SQL instance and it is purely used to execute package from the integrations service catalog via SQL agent, do we need to License every user i.e. Cal's for that SQL instance.

Basically i'm wanting to know if I could utilize SQL 2016 for an integration services instance and use features like Json manipulation etc and then load this data into the 2014 version instance...but what level of licensing will I need?

If users are not accessing the 2016 Instance itself, there's no need to licence them for CALs to it, just need a licence for itself for production purposes - Server or (in this case, avoid) Core.

It is unusual that you would use a Server licence without CALs, Microsoft would probably be interested on an audit... but as you note, your users are on 2014 CALs accessing their 2014 Server Instance, so would be covered for that (assuming no pass-through or multiplexing going on). However, as at least one user will need to use SSMS or VS etc to manage the 2016 SSIS packages or indeed server, I'd suggest a 2016 CAL for them.

Disclaimer: I'm a Microsoft Licensing Sales Specialist and sell MS Licencing online in UK, any advice may be given freely but ultimate responsibility is of the licencee.


Postgres - how to randomize two columns in a table I have a table of User data which I would like to disguise/anonymize. Two of the columns have location data (e.g. lat and long, both BigDecimals).

I have anonymized all the names and other personalized information, and I would like to keep each lat/long pair together, however randomize them within the users, so that the locations stay accurate, but their association with a User is now random.

I assume this involves an UPDATE function, but I'm not sure how to perform it on the same table.

I am very new at postgres and would appreciate any help - thanks!

so that the locations stay accurate, but their association with a User
  is now random.

If I understand you right, it could work like this:

UPDATE tbl
SET lat = t.lat
  , lon = t.lon
FROM (
   SELECT t2.id, t1.lat, t1.lon
   FROM  (
      SELECT lat, lon, row_number() OVER () AS rn
      FROM   tbl t1
      ) t1
   JOIN  (
      SELECT id, row_number() OVER (ORDER BY random()) AS rn
      FROM   tbl t2
      ) t2 USING (rn)
   ) t
WHERE  tbl.id = t.id;

lat / lon have moved to another row, randomly. Every connection to it's original row is gone, but lat & lon stay together.


How can I perform a mongoimport to a host that requires an SSL Certificate? I am trying to load some sample JSON data into a MongoDB database (hosted on IBM Bluemix).

The connection requires SSL, and a SSL Certificate has been provided, but I keep getting an error when I run mongoimport from the terminal (OS X).

Here is the command I am running (with sensitive data removed, of course):

mongoimport --ssl --sslAllowInvalidCertificates --host=<host_address>:<port>/admin -u <username> -p <password> --drop --file ./sample-dataset.json --db test --collection testColl --sslPEMKeyFile ./SSLCert.pem

And the error I receive:

Failed: error connecting to db server: no reachable servers, openssl error: dial tcp <host>:<port> getsockopt: connection refused

I'm new to SSL, especially when it comes to MongoDB. Am I just not using the right options/config for mongoimport?

With mongoimport you must separate host address and authentication DB

  --host host_address:port --authenticationDatabase admin


RECURSIVE CTE does not use an INDEX. (Disabling seqscan forces it using an index however and it is faster) Suppose the following relations:

match(match_id)
event(match_id, seq, gt, ...)

There are the following indexes:

match(match_id)
event(match_id, seq)

Further notes:

gt is monotonically increasing
For a given match I have a collection of events which happens at a specific 'gt' time
both match and event are mat views.
List item

I am using postgresql 13.1
My goal is to come up with a RECURSIVE CTE query which calculates the delta between one event and the next one, however I find that very slow.
While this can be solved practically with a self-join, I am not interested in that, I want to find out why my CTE is slow. I believe it should not be that slow.
Further numbers:

number of matches is 400
each match has an average of 541 events

My RECURSIVE CTE query is the following:

WITH RECURSIVE
    delta_gts AS (
        SELECT m.match_id, 1 AS seq, 0 AS gt, 0 AS delta
        FROM matches m

        UNION

        SELECT dgt.match_id, ev.seq AS seq, ev.gt AS gt, (ev.gt - dgt.gt) AS delta
        FROM delta_gts dgt
        JOIN events ev ON ev.match_id = dgt.match_id AND ev.seq = (dgt.seq + 1)

    )

SELECT * FROM delta_gts g

Futher notes I also tried by adding the following (for one match only):
WHERE g.match_id = 'ita_1672780'

and I find out in the plan that there is no predicate pushdown. I think this was implemented in pgsql 13.1
This is the plan:
QUERY PLAN
CTE Scan on delta_gts g  (cost=160601.44..161032.40 rows=21548 width=76) (actual time=173.940..354185.831 rows=220268 loops=1)
"  Buffers: shared hit=5453034 read=596370, temp read=1340253 written=1581611"
  CTE delta_gts
    ->  Recursive Union  (cost=0.00..160601.44 rows=21548 width=76) (actual time=173.931..353944.926 rows=220268 loops=1)
"          Buffers: shared hit=5453034 read=596370, temp read=1340253 written=1580590"
          ->  Seq Scan on netcastingdocument_matches m  (cost=0.00..10.08 rows=408 width=28) (actual time=173.917..174.265 rows=408 loops=1)
                Buffers: shared hit=6
          ->  Hash Join  (cost=14121.22..16016.04 rows=2114 width=76) (actual time=259.550..305.356 rows=190 loops=1158)
                Hash Cond: ((dgt.match_id = ev.match_id) AND ((dgt.seq + 1) = ev.seq))
"                Buffers: shared hit=5453028 read=596370, temp read=1340253 written=1580590"
                ->  WorkTable Scan on delta_gts dgt  (cost=0.00..81.60 rows=4080 width=72) (actual time=0.005..0.067 rows=190 loops=1158)
                ->  Hash  (cost=8106.89..8106.89 rows=288289 width=24) (actual time=257.949..257.949 rows=288323 loops=1158)
                      Buckets: 65536  Batches: 8  Memory Usage: 2484kB
"                      Buffers: shared hit=5453022 read=596370, temp written=1565616"
                      ->  Seq Scan on netcastingdocument_events ev  (cost=0.00..8106.89 rows=288289 width=24) (actual time=0.016..92.171 rows=288323 loops=1158)
                            Buffers: shared hit=5453022 read=596370
Planning:
  Buffers: shared hit=107
Planning Time: 50.290 ms
JIT:
  Functions: 13
"  Options: Inlining false, Optimization false, Expressions true, Deforming true"
"  Timing: Generation 4.108 ms, Inlining 0.000 ms, Optimization 19.158 ms, Emission 154.531 ms, Total 177.796 ms"
Execution Time: 355489.930 ms

Considerations:

It is not using the index (match_id, seq) on the events table at all when the recursive part of the CTE is executed.
Disabling seqscan does the trick as it will use the index for events.

After some investigation it looks like that the issue is that a SeqScan is being performed for looking up the next event which is not right in my situation.

There may be several causes; I cannot be certain, because you didn't post the EXPLAIN (ANALYZE, BUFFERS) output for both executions.

PostgreSQL might mis-estimate the row counts.  Running ANALYZE as you did is a good approach here, but in a recursive CTE the row counts are often hard to predict, and it is hard to fix these estimates.
If you don't mind a nasty trick, you could try adding another superfluous join condition to make PostgreSQL think that the result will have fewer rows:
JOIN events ev
   ON ev.match_id = dgt.match_id
      AND ev.seq = dgt.seq + 1
      AND ev.seq - 1 = dgt.seq

PostgreSQL might price an index scan too high, which induces it to choose a sequential scan and a hash join instead of a nested loop join.

If you have an SSD as disk, you should lower random_page_cost to 1 or 1.1 to give the PostgreSQL optimizer an idea that index scans are not four times as expensive as sequential scans.

If you have enough RAM, you should set effective_cache_size high enough, so that PostgreSQL knows that data are likely cached. That will also lower the cost of an index scan.


Location of Maintenance Plan's Back Up Database Tasks information (SQL Server 2005) I would like to know where in the database or on the file system the information about Back Up Database Task in the Maintenance Plans Tasks.

I can find the Job in msdb.dbo.sysjobs
I can find the Subplan in msdb.dbo.sysmaintplan_subplans

But I need to find where and how the Task is being stored.

Any help will be greatly appreciated.

In SQL 2000 they were in the msdb database.

Specific table information can be found here:
http://www.toadworld.com/platforms/sql-server/w/wiki/9438.msdb-databases.aspx#Database_Maintenance_Plan_Tables

In SQL 2005 maintenance plans are modified SSIS (or DTS) packages and are stored in the dbo.sysdtspackages90 table.  (from: http://www.sqlservercentral.com/Forums/Topic501078-5-1.aspx )

In SQL 2008 and 2012 they are in the sysssispackages table.


How to collapse rows with individual, contiguous dates into single rows with date range start and end Given a data set like this:

Id
Date
Value

1
01/01/2021
100

1
01/02/2021
100

1
01/03/2021
100

...
... (rows omitted for brevity)
...

1
12/29/2021
100

1
12/30/2021
100

1
12/31/2021
100

2
01/01/2021
100

2
01/02/2021
100

2
01/03/2021
100

2
06/01/2021
100

2
06/02/2021
100

2
06/03/2021
100

Is it possible to write a query to return a result set where each row is a contiguous date range, grouped by the Id column? Like this:

Id
Start Date
End Date
Sum of Value

1
01/01/2021
12/31/2021
36500

2
01/01/2021
01/03/2021
300

2
06/01/2021
06/03/2021
300

I'm imagining it will be some combination of window functions and group bys, but I'm not even sure it's possible and didn't want to fumble towards a solution that didn't exist.
I'm using SQL Server 2017 and 2019, if that matters.
The date ranges can be long - months or years. For example, one of the collapsed rows could be a year long, coming from 365 individual rows, like in the given example.

WITH
cte1 AS ( SELECT Id, xDate, Value,
                 CASE DATEDIFF( DAY, LAG(xDate) OVER (PARTITION BY Id ORDER BY xDate), xDate )
                 WHEN 1
                 THEN 0
                 ELSE 1 END delta
          FROM test ),
cte2 AS ( SELECT Id, xDate, Value, SUM(delta) OVER (ORDER BY Id, xDate) grp
          FROM cte1 )
SELECT Id, MIN(xDate) startDate, MAX(xDate) endDate, SUM(Value) sumValue
FROM cte2
GROUP BY id, grp;

fiddle


high mysql sent traffic we have a wordpress site (Centos6.5+apache2.2.15+mysql5.1.73+php5.3), which are server in vultr, I noticed there has a terrible mysql traffic.

Traffic Tip ø per hour
Received    67 MiB  88 MiB
Sent    4,634 MiB   6,106 MiB
Total   4,701 MiB   6,194 MiB

Total   ø per hour  ø per minute    ø per second
1,481 k 1.95 M  32.52 k 541.96

 Show Full Queries  ID  User    Host    Database    Command Time    Status  SQL query
Kill    311206  root    localhost   wp  Sleep   3   --- ---
Kill    311210  root    localhost   wp  Sleep   2   --- ---
Kill    311213  root    localhost   wp  Sleep   1   --- ---
Kill    311214  root    localhost   wp  Sleep   0   --- ---

sent data is 6GB per hour. I listed in show processlist; and view the slow query log, it seems no problem.

Here is my my.cnf

port=3306
socket=/var/lib/mysql/mysql.sock
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
symbolic-links=0
skip-external-locking
bind-address=xx.xx.xx.xx
# I need some remote connection, but I have stopped them in crontab, then monitor again, in processlist and slow query log, all the queries have already come from localhost, but the sent traffic still looks terrible.
max_connections=2500
wait_timeout=30
back_log=256
key_buffer_size=256M
table_cache=2048
sort_buffer_size=128k
read_buffer_size=128k
net_buffer_length=128K
read_rnd_buffer_size=3M
myisam_sort_buffer_size=16M
thread_cache=64
query_cache_size=32M
bulk_insert_buffer_size=16M
ft_min_word_len=2

innodb_buffer_pool_size=256M
innodb_additional_mem_pool_size=8M
innodb_log_buffer_size=8M
innodb_flush_log_at_trx_commit=2
innodb_force_recovery=0
innodb_log_file_size=96M

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

httpd.conf

Timeout 45
KeepAlive Off
MaxKeepAliveRequests 256
KeepAliveTimeout 3
<IfModule prefork.c>
StartServers 8
MinSpareServers 5
MaxSpareServers 20
ServerLimit 192
MaxClients 192
MaxRequestsPerChild 2500
</IfModule>

what caused such a huge mysql traffic, Is this normal?

I have three conjectures on this situation. One or more of them may apply...
CONJECTURE #1
There may be other operations that happen apart from fetching data back-and-forth across the network. Here are a couple of of my old posts where I actually discuss Bytes_received and Bytes_sent

Apr 18, 2012 : MySQL : Does 'bytes_sent' and 'bytes_received' include mysqldump data?
Apr 13, 2014 : Determining replication traffic

In these posts, I talk about how MySQL Replication creates traffic in its framework. How ?
There are two threads involved for MySQL Replication

I/O Thread : TCP/IP Connection back to its Master read binlog events and record the binlog events in the Slave's Relay Logs.
SQL Thread : DB Connection that reads binlog events from the Relay Log and executes the SQL. If log-slave-updates and log-bin are enabled in the Slave, the binlog event whose SQL was executed in this thread is recorded in the Slave's binary logs.

If the WordPress DB Server is in Master/Slave Replication topology, note the effect

If DB Server is a Master, any Slave's I/O thread connected will make a Master's Bytes_sent increase.
If DB Server is a Slave, the I/O thread will increase Bytes_received reading binlog events from the Master and the SQL Thread will increase Bytes_received reading binlog events from the Relay Logs.

Based on the numbers you are showing, I would suspect the DB Server would be a Master and it replicates any log changes to one of more Slaves.
CONJECTURE #2
If the DB Server is not in MySQL Replication Topology at all, I would then suspect some exporting operations, such as mysqldump to another Server or even locally. Why ? A DB Conneciton is still require by mysqldump. Bytes being written to a dump have to be recorded in Bytes_sent.
CONJECTURE #3
If there are neither MySQL Replication nor regular mysqldump backups in place, then I can only see one thing running up Bytes_sent so high and leaving Bytes_received so low: DB Monitoring.
Someone asked this in ServerFault back in August 2011 : 1 billion mysql queries in 24 days? Can something be wrong?. In that post, I explained that running SHOW GLOBAL STATUS often can run up the status counts on Queries and Questions. Retrieving status values frequently can also contribute to increasing Bytes_sent.
UPDATE 2014-11-28 15:10 EST
If you control everything in Vultr and you know for sure that the connections are all from localhost, the problem may not be WordPress itself. It would have to be your either remote connections or just very frequent traffic spikes.
At this point, WordPress would be out of my wheelhouse. Try out the info in this post : How can I improve site/page performance of WordPress websites?


Adding secondary via rs.add on MongoDB I have a master and slave on Linux (centOS 7). The rs.initiate(cfg) was done, but its shows the below output when trying to add secondary via rs.add:

genienational:PRIMARY> rs.add("localhost.localdomain:27017")
{
     "ok" : 0,
     "errmsg" : "The hosts test.appraisergenie.com:27017 a
nd localhost.localdomain:27017 all map to this node new co
nfiguration version 2 for replica set genienational",
     "code": 103
}
genienational:PRIMARY> use local
switched to db local

How do I resolve?

Version: The master is on Enterprise. I think 3.2 and the slave is on 2.6.

The error message in your screenshot indicates you are trying to add identical members to your replica set under different names:

"The hosts x:27017 and y:27017 all map to this node in new configuration".

If you want to configure multiple replica set members on a single machine for testing purposes, all members need to use different ports and/or IP addresses.

  Version: The master is on Enterprise. I think 3.2 and the slave is on 2.6.

Mixed-version replica sets are only supported for consecutive major versions (i.e. 3.2 and 3.0) and expected to be used transiently during a rolling upgrade to a new major version of MongoDB. A mixed replica set with 3.2 and 2.6 members is definitely not supported.


How does optimistic locking actually enforce re-read/update? My understanding of optimistic locking is that it uses a timestamp on each record in a table to determine the "version" of the record, so that when the record is access by multiple processes at the same time, each has a reference to the record's version.

Then, when an update is performed, the timestamp is updated. Before an update is committed, it reads the timestamp on the record a 2nd time. If the timestamp (version) that it has is no longer the timestamp on the record (because it's been updated since the first read), then the process must re-read the entire record and apply the update on the new version of it.

So, if anything I have stated is not correct, please begin by making clarifications for me. But, assuming I'm more or less correct here...

How does this actually manifest itself in a RDBMS? Is this 2nd read/verification enforced in the application logic (the SQL itself) or is it a tuning parameter/configuration that the DBA makes?

I guess I'm wondering where the logic comes from to read the timestamp and perform a 2nd update if the timestamp is stale. So I ask: does the application developer enforce optimistic locking, or is it enforced by the DBA? Either way, how? Thanks in advance!

The basic technique is quite straightforward.  When you read the record you take a note of the version or timestamp column, e.g.

Select FooID
      ,Foo
      ,Bar
      ,TS      -- timestamp
  from Foobar
 where FooID = @FooID

When you go to write out the record you filter the write by the timestamp/version so that the write writes nothing if the timestamp/version has changed.  This makes the write atomic, e.g.

update Foo
   set Foo = @foo
      ,Bar = @bar
      ,TS = @timestamp
 where FooID = @FooID
   and ts = @timestamp

select @row_count = @@rowcount  -- specific to t-sql, but this is a system variable
                                -- that holds the number of rows affected by the
if @@rowcount = 0               -- most recent operation.  Other DBMS platforms do
    [deal with outdated record] -- this differently.

This allows an application to do the update without holding locks open.  This is necessary
for n-tier systems working through a connection pool, and prevents a class of deadlocks
that used to be common on two-tier client-server systems.

There is nothing enforced in the database about this.  It's all done explicitly by the application.


What are the consequences of enabling slow_query_log and flushing the cache on mysql? I have a large database that is performing poorly recently. I want to enable the slow_query_log to investigate the queries and flushing the cache but I need to know what are the consequences of doing such action on a live system that is being used frequently 24/7. (I do not have permission to create a test copy of the database).

Much appreciated.

When the slow query log is enabled, the server has to check the time after each executed query (thats quite trivial) and if needed, write the query and some info to the log file - if you have really many queries, that might need some IO capacity, but you can enable it temporarily and monitor the situation, to quickly disable it if needed. It should not be a problem unless your IO is already maxed out.

Flushing the query cache (the flush query cache; command) actually means "defragmenting" of the allocated space. It may take even few seconds depending on the cache size (hopefully it is not bigger than 128MB?) and it might block some queries for that time.

For "clearing" the cache there is a reset query cache; command, that will imho take similar amount of time as the flush.

Usual advice is to disable the query cache altogether (would need a config change and a restart), because it scales badly in parallel environments in its current implementation.


Does Index Rebuild Reduces physical read from disk Recently I have rebuild indexes uisng Ola Hallengreen script which were 99% fragmented. After rebuilt i have noticed the physical reads have reduced a lot. Does this has anything to do with Index Rebuilt ?

Immediately after you rebuild an index, its pages are more likely to be cached in memory.

Consider this scenario:

Your server has a wide variety of queries running, and a wide variety of tables are cached in memory
You run a query hitting a specific table - say, COUNT(*) from table - and all of its pages were not cached in memory. Your query does a lot of physical reads to bring that data up into cache.
You rebuild its index.
You run the query again - but this time, its pages are now cached in memory. You see less physical reads on that query.

This is one of the reasons you don't really wanna use physical reads to gauge the success of your performance tuning efforts. Start by using logical reads instead. (Heck, you could have skipped step #3 altogether and you'd still see less physical reads - but that doesn't mean you should run every query twice in order to reduce physical reads.)


Duplicate data in a table given table name For a performance testing application we need to have lot more data in our tables than what we have now. How do I duplicate the data in a table.

In the process of duplicating I also need to set new values for primary key column. I'm thinking getting the max value of primary key column and adding a incrementing number to it will solve the primary key issue.

Is it possible to do this in PL/SQL.

-- more information

This table have over 200 columns and 3 unique keys (there are no table level constants), all the operations are happening through views and oracle packages, these packages are responsible for keeping the primary key combination unique. In the duplication process If I could add a incrementing value (starting with max+1 of the column) to a single column I can keep the uniqueness of the key combination.

I like to use rownum. Using a CTE to get the current maximum PK value, you can easily double-up your data.

Example below.

Test table:

create table yourperftest
( pk number,
  col1 number,
  col2 number,
  col3 number
);

Test data:

insert into yourperftest values (1,1,1,1);
insert into yourperftest values (2,2,2,2);
insert into yourperftest values (3,3,3,3);
insert into yourperftest values (4,4,4,4);
insert into yourperftest values (5,5,5,5);

Query to "double up" the table:

insert into yourperftest
with maxvalue as (
  select max(pk) as maxvalue
  from yourperftest
)
select rownum+maxvalue, col1, col2, col3
from yourperftest,maxvalue;

I'd probably use an /*+APPEND*/ hint too, plus make sure you gather stats on the table afterwards.

Having said that, stats play a big part in testing - you want to make sure that your data/performance test environment is as close to production as possible. Going forward, the stats are the key to this. Stats that are different between test and production will cause different query plans to be picked by the optimiser, which may make your performance testing invalid when production is going tits-up.

EDIT:

Since you ask about doing this for more than 1 column, here's another example:

 insert into yourperftest
with maxvalue as (
  select max(pk) as maxvalue
  from yourperftest
), col1tab as (
   select max(col1) as col1maxvalue
   from yourperftest
)
select rownum+maxvalue, col1maxvalue+rownum, col2, col3
from yourperftest,maxvalue,col1tab ;

CTEs are brilliant.


difference between pg_dump command's --column-insert and --attribute-insert Background:

I'm trying to create a dump file that only contains the data in my database.  I would like it to create the data with the INSERT command.
In reading the manual, (http://www.postgresql.org/docs/9.2/static/app-pgdump.html#PG-DUMP-EXAMPLES) i see that I can use either

--column-insert
--attribute-insert

It wasn't clear from the manual what the difference is.  So I tried both and did a diff on the files.
There are differences and I'm wondering why.
Can someone tell me what the difference is between these two commands?

Here's the full command I'm running:

pg_dump -a --column-inserts -U postgres testdb > /tmp/test_data_as_inserts2.sql

and :

 pg_dump -a --attribute-inserts -U postgres testdb > /tmp/test_data_as_inserts.sql

Any tips would be appreciated.  In the meantime, I'm going to dig in and see what's different between these two files... why certain records are included and others not.

Thanks.

They are the same. Check the documentation (where they are mentioned together):

  --column-inserts
  --attribute-inserts
  Dump data as INSERT commands with explicit column names (INSERT INTO table (column, ...) VALUES ...). This will make restoration very
  slow; it is mainly useful for making dumps that can be loaded into
  non-PostgreSQL databases. However, since this option generates a
  separate command for each row, an error in reloading a row causes only
  that row to be lost rather than the entire table contents.


Query extremely slow when joining 2 views I will preface this by saying i am not a DBA, just a dev that is trying to figure out performance problems with the following query we have.

 SELECT
        OCCoverID as StaffID, FullName, SUM(OCWeight) AS Num
    FROM vuOCStaffAbsentExpandedMini
        INNER JOIN vuStaff ON OCCoverID = StaffID
    WHERE
        SADate = '2019-10-4'
        AND MIDent = 506
        AND SASupplyID IS NULL
        AND CoverStaffSupplyID IS NULL
        AND StaffMIDent <> 1
    GROUP BY
        OCCoverID, FullName

It takes about 1 minute to execute every time, we have double checked (we think) all required indexes, ran it through index tuner and still same issues.
Below is the actual plan that it creates.

Any help would be appreciated, we are using sql server 2012 express

https://www.brentozar.com/pastetheplan/?id=HJgOtXHur

You have a performance spool that feeds your nested loops operator (non-apply)

...with very low estimates.

Now, for each value in the top(outer) input, the table spool will produce a rebind. No rewinds are possible for performance spools on non apply nested loop joins.

  ...The lazy table spool is populated once during the first loop
  iteration. The spool rewinds its contents for each subsequent
  iteration of the join. With nested loops join, the inner side of the
  join is a static set of rows because the join predicate is on the join
  itself. The static inner-side row set can therefore be cached and
  reused multiple times via the spool. A nested loops join performance
  spool never rebinds....

Source: Paul White on Nested Loops Joins and Performance Spool

The low estimates result into the actual situation below:

You can validate the high rowcount of the spool output by multiplying both inputs (of the compute scalar & the stream aggregate).

515 728 (outer input) * 168 (inner input) = 86 642 304

Solutions

The first thing to fix would be fixing why the estimates are so low.

You could try updating the statistics of the table referenced in the view.

But it appears that you are doing a LIKE comparison between dbo.TblOnCall and the tables feeding the spool operator.

That can also be seen on the NL operator that gets the data from the spool.

(EXPR 1137 = the outer input, UNION = the inner input).

For a longterm solution,

I would fix this LIKE comparison and any other parts that will impact your estimations gravely.

We would have to see the views to give the most correct query recommendations.

For a temporary quick fix, you can disable the spool with a trace flag or a query hint.

  Performance spools can be disabled with lightly-documented trace flag
  8690, or the documented query hint NO_PERFORMANCE_SPOOL on SQL Server
  2016 or later.
  Source

An example of implementing this is running

DBCC TRACEON(8690,-1) (will be removed on instance restart)

Or better, running the query with OPTION(QUERYTRACEON 8690)

 SELECT
        OCCoverID as StaffID, FullName, SUM(OCWeight) AS Num
    FROM vuOCStaffAbsentExpandedMini
        INNER JOIN vuStaff ON OCCoverID = StaffID
    WHERE
        SADate = '2019-10-4'
        AND MIDent = 506
        AND SASupplyID IS NULL
        AND CoverStaffSupplyID IS NULL
        AND StaffMIDent <> 1
    GROUP BY
        OCCoverID, FullName
OPTION(QUERYTRACEON 8690);


Restrict SQL Server 2008 R2 User to restore only one database (but not others) It is my understanding that a user has to be a "dbcreator" in order to restore databases. However, what if I want a specific user to restore only one specific database, but not gain access or restore any other database on the server? Doesn't "dbcreator" allow that user to essentially restore any database?

Log in as a server admin.
Create a stored procedure with execute as owner that restores a particular database.
Assign permission to execute it to the user.

As for where to put that stored procedure, I'm not sure it's possible to restore the DB from which you are running the SP, in which case you might want to create a completely blank database for the only purpose of storing this stored procedure.


MariaDB FULLTEXT search with short / mandatory words Just checking I'm understanding this correctly:
CREATE TABLE customer (
    id INT NOT NULL AUTO_INCREMENT,
    name TINYTEXT NOT NULL,
    PRIMARY KEY (id),
    FULLTEXT (name)
) ENGINE = InnoDB;

INSERT INTO customer VALUES (1, "ABC.DEF");
INSERT INTO customer VALUES (2, "ABC_DEF");
INSERT INTO customer VALUES (3, "ABC'DEF");
INSERT INTO customer VALUES (4, "ABC.DE");
INSERT INTO customer VALUES (5, "ABC.DFF");

Where I've got innodb_ft_min_token_size set to 3 (default is 4).

When running:
SELECT
    c.*,
    MATCH (name) AGAINST ("+ABC +DEF" IN BOOLEAN MODE) AS m
FROM
    customer AS c

Customers 1 and 3 match, because the . and ' are seen as word separators (annoying for O'Brien).
For customer 2, because the underscore gets the whole name treated as a single word, the "DEF" word cannot be found.

If I change the MATCH to "+ABC +DE".
1, 2, or 3 do not match because this is using a full word match ("+DE" does not match "DEF").
4 does not match because... innodb_ft_min_token_size is set to 3?
As in, the 2 letter "DE" word is not in the FULLTEXT INDEX?

If I change the MATCH to use asterisks (e.g. "+ABC* +DE*"), that will use prefix matching.
But will only add customers 1 and 3 to the selection.
Because the 2 letter "DE" word for customer 4 is not in the FULLTEXT INDEX?

If I change the MATCH to use "+ABC.DE*", it matches all of them.
Note how they all get the same rank (even customer 5), and this is no different to "+ABC*", where MATCH seems to be seeing the "DE*" as a separate word, and not matching it against anything.
Whereas "+ABC DE*" is explicitly keeping it as a separate word, and the scores are handled appropriately.

While the individual points make sense, I'm not sure this creates a good system.
For a bit more consistency, I'm wondering if the database should ignore short words (tokens) in the MATCH query, in the same way it does when building the FULLTEXT INDEX.
Only because I don't think "+DE" will ever do anything useful when the min token size is 3; and it's not exactly easy for the developer to identify what the individual words in the FULLTEXT INDEX will be (i.e. to remove them).

FULLTEXT is imperfect.  You are doing the 'right thing' to analyze the data it this way.  Now switch from "abc" and "def" to strings that are realistic in your dataset.
Here are a couple of tricks that I have used reasonably successfully.

Put a + in front of long words, don't put a + in front of short words.

Use this combo to check for things that need LIKE (or RLIKE) but desire the speed of FULLTEXT:
  WHERE MATCH(text) AGAINST ('abc def IN BOOLEAN MODE')
    AND text LIKE '%abc+def%'

That is, use FT to find rows with the "words", then use LIKE to make sure they are adjacent and/or contain certain punctuation.


Move SQL Server XML column to a new table - slow performance I have a table with a XML Column, each XML document size is 15kb and I have like 2 million rows.

I need to move this XML column into a new table. I tried:

insert into newtable
    select xmlcolumn
    from originaltable

but is taking too long.

Any other way to do it? What's the best way to achieve this?


MySQL 5.7 GetDiagnostics is always returning null for SQLSTATE What am I doing wrong? I have the following Handler and the sqlexception is being caught, but GET DIAGNOSTICS is not returning any information - they are all NULL.

I have the following code:

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
  BEGIN

      GET DIAGNOSTICS @cno = NUMBER;
      GET DIAGNOSTICS CONDITION @cno @errno = MYSQL_ERRNO;

      GET DIAGNOSTICS CONDITION 1
        currentcode = RETURNED_SQLSTATE, msg = MESSAGE_TEXT;

    GET CURRENT DIAGNOSTICS CONDITION 1 @current = RETURNED_SQLSTATE;
    GET STACKED DIAGNOSTICS CONDITION 1 @stacked = RETURNED_SQLSTATE;

  SELECT @current, @stacked, @errno INTO currentcode,stackedcode,errorcode;

GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE, @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
SET @full_error = CONCAT("ERROR ", @errno, " (", @sqlstate, "): ", @text);
SELECT @full_error;

CALL debug_msg(1,@full_error);

    SET _rollback = 1;
    SET ResultCode = CONCAT('currentcode: ' , currentcode, ' , stackedcode: ' , stackedcode);
  END;

I admit that the condition_number element - I do not understand if it represents the index of conditions that were caught or if it is the error number to look at and the documentation on the MySQL site says condition_number (ok I get that it is a condition_number.)

Replace GET DIAGNOSTICS CONDITION 1

with GET STACKED DIAGNOSTICS CONDITION 1

In my case this helped me.


How to create recurrent weekdays as columns in a pivot? I am newcomer to programming and databases and I would be grateful for some help on the following scenario.

I use PHP with SQL Server. I am building an employee attendance system and I would like to create a (pivot) table with months as rows and all weekdays name as columns (for a specific year). The values in cells will be the number of day(1, 2, 3...31).

The background color of the cell (already exists as table column) declares the type of employees' leave. The table has the following columns: employee_id, leave_date, leave_type, leave_type_color.

I want to achieve a result like below:

Thank you.

The most complex part of this is just building the calendar in that format. Pivoting and surrounding it with HTML is pretty easy. First, let's start with this, your employee table with leave dates. leave_type didn't seem relevant to the problem at hand.

CREATE TABLE dbo.EmpLeave
(
  EmployeeID int,
  leave_date date,
  leave_type_color char(6)
);

INSERT dbo.EmpLeave(EmployeeID,leave_date,leave_type_color)
  VALUES(1,'2018-01-02','7777cc'),(1,'2018-04-01','ffffac');

The procedure I came up with looks like this (and warning: it assumes @@DATEFIRST = 7):

CREATE PROCEDURE dbo.BuildLeaveHTMLTable
  @EmployeeID int,
  @Year smallint = NULL
AS
BEGIN
  SET NOCOUNT ON;
  SET @Year = COALESCE(@Year, DATEPART(YEAR, GETDATE()));
  DECLARE @FirstDay date = DATEADD(YEAR, @Year-1900, 0);

  ;WITH Numbers AS ( -- 366 possible days (leap year)
    SELECT n = 1 UNION ALL SELECT n + 1 FROM Numbers WHERE n <= 365
  ),
  Calendar AS ( -- a year's worth of dates and dateparts
    SELECT [Date] = d,
      MonthStart = DATEADD(DAY, 1-DAY(d),d),
      Y  = CONVERT(smallint, DATEPART(YEAR,   d)),
      M  = CONVERT(tinyint,  DATEPART(MONTH,  d)),
      D  = CONVERT(tinyint,  DATEPART(DAY,    d)),
      WY = CONVERT(tinyint,  DATEPART(WEEK,   d)),
      DW = CONVERT(tinyint,  DATEPART(WEEKDAY,d))
    FROM
    (
      SELECT d = CONVERT(date,DATEADD(DAY, n-1, @FirstDay)) FROM Numbers
    ) AS c WHERE YEAR(d) = @Year -- in case it's not a leap year
  ),
  BaseSlots AS ( -- base set of 37 ints
   -- month can be spread across 6 weeks, but no more than 2 days in 6th week
    SELECT TOP (37) slot = n FROM Numbers ORDER BY n
  ),
  Months AS ( -- base set of 12 ints
    SELECT TOP (12) m = slot FROM BaseSlots ORDER BY slot
  ),
  SlotAlignment AS ( -- align days of week to slot numbers
    -- this is the most cryptic part of this solution
    -- determines which set of 7 slots, and which slot
    -- exactly, a given date will appear under
    SELECT c.*, slot = DW+(c.WY+1-DATEPART(WEEK,c.MonthStart)-1)*7
      FROM Calendar AS c
      INNER JOIN Months AS m ON c.M = m.m
  ),
  SlotMatrix AS ( -- extrapolate actual dates to 37 x 12 matrix
    SELECT m.m, s.slot, sa.[Date]
      FROM BaseSlots AS s
      CROSS JOIN Months AS m
      LEFT OUTER JOIN SlotAlignment AS sa
      ON sa.m = m.m AND sa.slot = s.slot
  ),
  FinalHTML AS ( -- build some HTML!
    SELECT m = '',
      slot, cell = CASE WHEN slot = 1 THEN '
'
        + COALESCE(DATENAME(MONTH,DATEADD(MONTH, m-1, 0)),'')
        + '	' ELSE '' END + '' + COALESCE(RTRIM(DATEPART(DAY,[Date])), '&nbsp;')
        + '	' + CASE WHEN slot = 37 THEN '
' ELSE '' END
      FROM SlotMatrix AS q LEFT OUTER JOIN dbo.EmpLeave AS el
      ON q.Date = el.leave_date
      AND el.EmployeeID = @EmployeeID
  ) -- now turn it sideways
  SELECT m = '',
    [1]  = '
Month	S	',    [2]  = 'M	',
    [3]  = 'T	', [4]  = 'W	', [5]  = 'T	',
    [6]  = 'F	', [7]  = 'S	', [8]  = 'S	',
    [9]  = 'M	', [10] = 'T	', [11] = 'W	',
    [12] = 'T	', [13] = 'F	', [14] = 'S	',
    [15] = 'S	', [16] = 'M	', [17] = 'T	',
    [18] = 'W	', [19] = 'T	', [20] = 'F	',
    [21] = 'S	', [22] = 'S	', [23] = 'M	',
    [24] = 'T	', [25] = 'W	', [26] = 'T	',
    [27] = 'F	', [28] = 'S	', [29] = 'S	',
    [30] = 'M	', [31] = 'T	', [32] = 'W	',
    [33] = 'T	', [34] = 'F	', [35] = 'S	',
    [36] = 'S	', [37] = 'M	'
  UNION ALL
  (
    SELECT * FROM FinalHTML PIVOT (MAX(cell) FOR slot IN
    (
     [1], [2], [3], [4], [5], [6], [7], [8], [9], [10],[11],[12],[13],[14],
     [15],[16],[17],[18],[19],[20],[21],[22],[23],[24],[25],[26],[27],[28],
     [29],[30],[31],[32],[33],[34],[35],[36],[37]
    )) AS p
  )
  ORDER BY m OPTION (MAXRECURSION 366);
END
GO

Results of this call:

EXEC dbo.BuildLeaveHTMLTable @EmployeeID = 1;

Look like this (I stopped at the 7th day column):

You'll have to add the
 /
 wrapper yourself, but here is what the output looks like when put in between those and saved as HTML (and of course you can further enhance it with CSS):

When leave falls on a weekend, the leave color trumps the weekend color, but that is easy to adjust. Change this:

  + COALESCE(' bgcolor=#' + RTRIM(el.leave_type_color),
      CASE WHEN DATEPART(WEEKDAY, [Date]) IN (1,7)
      THEN ' bgcolor=#cccccc' ELSE '' END)

To this:

  + CASE WHEN DATEPART(WEEKDAY, [Date]) IN (1,7)
      THEN ' bgcolor=#cccccc' ELSE COALESCE(' bgcolor=#'
      + RTRIM(el.leave_type_color), '') END

To convert a color in decimal format (like 65280) to its RGB equivalent (00FF00), you have to do a bunch of manipulation. I would consider storing it as RGB hex in the first place, but I updated the solution here with something similar to this:

SELECT RIGHT(CONVERT(varchar(10),CONVERT(varbinary(8), 65280),1),6);


Easiest method to retrieve the content of BLOBs from a Sybase databse I would like know the easiest way (no coding) to extract the content of the blobs of a database.

For example, I have a table which contains a column whose format is BLOB.
The result I would like to get is to extract the content of the BLOB into CSV files.

What is the most elegant method to do this?
Is there a Sybase function to perform this? Or an existing software?

Thanks in advance,

For example, I have a table which contains a column whose format is BLOB. The result I would like to get is to extract the content of the BLOB into CSV files.
What is the most elegant method to do this?

BCP OUT will be your best choice.

Make sure your database has BLOB data (Image and/or TEXT datatypes). Also, select into should be set to TRUE for the database.
Check the MAX length of the BLOB data using select max(datalength(column_name)) from table_name. Alternatively, you can use this utility to do the same thing programatically.
Use the above max datalength as -T parameter in BCP OUT.

-T text_or_image_size
allows you to specify, in bytes, the maximum length of text or image data that Adaptive Server sends. The default is 32K. If a text or an image field is larger than the value of -T or the default, bcp does not send the overflow.

Is there a Sybase function to perform this? Or an existing software?

SybaseASE does not have a native function. Also, I am not aware of an existing software (there might be, but I did not have a need to use them for getting BLOB data out of my sybaseASE).


Execute xp_cmdshell as a non-windows user So I have a bunch of database users who should be able to execute a procedure with a call to xp_cmdshell. They are not windows domain accounts, they are just sql-server logins. I want to do it using a proxy ##xp_cmdshell_proxy_account##. I am aware of security threats that it may bring. I cannot use 'WITH EXECUTE AS admin' due to our company inner version control and I obviously do not want to grant all of the users rights to execute xp_cmdshell, that is why my only choice (am I right here?) is using the proxy account.

I have a special windows-domain account for it, to whom I granted xp_cmdshell execution rights. I can successfully execute cmdshell using this account. I created the proxy account using the following command:

EXEC sp_xp_cmdshell_proxy_account  [my_domain\special_account],'SuperSecretPassword'

When I try to execute any xp_cmdshell command as a database user with no xp_cmdshell rights an error appears:

The EXECUTE permission was denied on the object 'xp_cmdshell', database 'mssqlsystemresource', schema 'sys'.

My question is - can I execute a xp_cmdshell command using an sql server login and I am simply missing something here or is it only possible using a domain account? Is there any other procedure to follow here to make it happen?

If I fail to make the above work I will have to switch some logic to a CLR procedure, but I am determined to make it work.

I am using sql-server 2012. Any help will be appreciated, thanks!

You can use a SQL Server login.

The following procedure is explained in MSDN:

  To allow non-administrators to use xp_cmdshell, and allow SQL Server
  to create child processes with the security token of a less-privileged
  account, follow these steps:

  Create and customize a Windows local user account or a domain account with the least privileges that your processes require.
  Use the sp_xp_cmdshell_proxy_account system procedure to configure xp_cmdshell to use that least-privileged account.
  Note You can also configure this proxy account using SQL
  Server Management Studio by right-clicking Properties on your server
  name in Object Explorer, and looking on the Security tab for the
  Server proxy account section.
  In Management Studio, using the master
  database, execute the GRANT exec ON xp_cmdshell TO ''
  statement to give specific non-sysadmin users the ability to execute
  xp_cmdshell. The specified login must be mapped to a user in the
  master database.


How do I restore a SQL backup, while excluding a specific table? How do I exclude a specific table during a SQL backup RESTORE operation?

My question is specific to restoring a backup sql file.

I am using Putty and bash server.

You don't.  You restore the backup as a new DB, drop the unwanted table from it, then take a new backup of this working DB.


mysql won't start, it says Table mysql.plugin doesn't exist I've had this problem for several hours, and now I'm just tired..

When I try to start MySql from terminal (mac), i get

ERROR! The server quit without updating PID file (/usr/local/mysql/data/127.0.0.1.pid).
There seem to be something wrong with the table mysql.plugin, log file says it doesn't exist.

I have uninstalled and reinstalled mysql on several ways, trying every method i have found googling around. Therefor i think there must be something wrong with the computer - not the mysql software.

Latest install is from mysql.com, a dmg-file.

If you're upgrading you should use mysql_upgrade script to install system tables.


PostgreSQL: Why is CREATE TABLE AS faster than CREATE ... INSERT INTO? Below are the two different syntaxs for the same thing.

With a COPY TABLE AS SELECT (CTAS).

CREATE TABLE main
AS
  SELECT *
  FROM other;

As separate statements with CREATE TABLE and INSERT INTO

CREATE TABLE main (like other);

INSERT INTO main
SELECT *
FROM other;

I have observed that the CTAS is faster than distinct CREATE TABLE .. INSERT. The first takes 20 seconds to complete execution. The second  syntax two takes 1 min 15 secs to complete execution.

What could be the reason for difference?

CREATE TABLE AS has some advantages over the other form, namely in the reduction or elimination of WAL.. Certainly optiimizations can be applied to a few commands (viz. CREATE TABLE AS, CREATE INDEX, CLUSTER, COPY into tables that were created or truncated in the same transaction) in certain wal-reduction modes (minimal).

  In minimal level, WAL-logging of some bulk operations can be safely skipped, which can make those operations much faster (see Section 14.4.7).

  But minimal WAL does not contain enough information to reconstruct the data from a base backup and the WAL logs, so replica or higher must be used to enable WAL archiving (archive_mode) and streaming replication.

You can read more information about this here

  14.4.7. Disable WAL Archival and Streaming Replication

  When loading large amounts of data into an installation that uses WAL archiving or streaming replication, it might be faster to take a new base backup after the load has completed than to process a large amount of incremental WAL data. To prevent incremental WAL logging while loading, disable archiving and streaming replication, by setting wal_level to minimal, archive_mode to off, and max_wal_senders to zero. But note that changing these settings requires a server restart.

  Aside from avoiding the time for the archiver or WAL sender to process the WAL data, doing this will actually make certain commands faster, because they are designed not to write WAL at all if wal_level is minimal. (They can guarantee crash safety more cheaply by doing an fsync at the end than by writing WAL.)

As a special note, if you go back to your

CREATE TABLE t1 (like t2);

And instead do

CREATE TABLE UNLOGGED t1 (like t2);

It'll be even faster. But, don't do this as you'll be missing WAL. For more information on this read

UNLOGGED docs
PostgreSQL Non-durability features


Redundant / partially duplicated data in a DB - how to structure? I'm building a website for which a large amount of user generated data will be stored in a back end db and will be discoverable by all users.

By its nature, a portion of the user entries will be redundant.  For the sake of example, let's imagine this data is made of recipes.  Suppose the following entries:

Title: Lemony dressing:
- 1 tbsp lemon juice
- 2 tbsp olive oil
Just mix!

and

Title: Lemony marinade:
- 2 tbsp lemon juice
- 2 tbsp olive oil
- 1 tbsp soy sauce
Just mix the ingredients together!

Clearly, those 2 entries have some redundant information: half the title is shared, the instructions are shared partially, one ingredient is shared exactly, another ingredient is partially shared....

Furthermore, one could easily imagine a recipe made of several sub-recipes, which can also exist on their own.  So for example, you could have a marinated flank steak recipe that uses the lemony marinade as a step.  So recipes can be defined recursively.

My question is how should I structure my SQL recipe database?  My gut feeling is that it makes sense to store the entry "lemon juice" in an Ingredients table, the quantity "2 tbsp" in a Quantity one, and then use their keys in the Recipe table.

But how far should I go?  Should I split "lemon juice" into two entries, in order to prevent duplication with "orange juice" and "lemon zest"?  After all, ingredients can also be defined recursively.  So should I store "lemon", "orange", "juice", and "zest" separately and only assemble them ("lemon juice", "orange zest", etc) later?  Or should I store "orange juice" and "lemon juice" knowing that I am duplicating "juice"?  Is it a function of word size?

But then instructions on how to use the ingredients are going to have some overlap.  Does it make sense then to store each word separately?  Why not letters?

I'm not sure how to consider the trade-offs like this.  I suppose that if I minimize the redundancy of the info in the tables, there must be some cost.  Is it performance wise, or is it a cost paid at conception?

Thank you,

JDelage

PS:  This question is not meant to ask for a specific implementation of a recipe db.  It's a more general question on how to store info that is partially duplicated.

You might get better luck on stackoverflow as you've not asked a sys admin question.

The recipe examples don't really give any useful pointers on what you're actually trying to do. If you're after info about removing duplication from a database you should look up normalization and go from there. It's a well known process.

If you're this unsure of how to use mysql with your data you need to reassess what you're doing and make sure you're picking the right tool for the job before you end up going off in the wrong direction, only to regret it later.


When is SQL Server permission check done? When a user is to execute a complex stored procedure, inside of which there are quite a few tables / views or even another stored procedure.

My question is:

Before SQL server generates an execution plan, does it check the user's permission?

If not, generating execution plan itself is an overhead cost wasted if the user does not have the EXEC permission on this stored procedure in the first place.

If yes, it seems to me there is nothing consumable to the SQL Server engine, i.e. there is no securable identifier (like the stored procedure itself, tables inside the stored procedure etc) that the SQL Server engine can use to check against the user permission. All these securable identifier will not be generated until at least parsing is done.

So from a lifecyle perspective of executing a stored procedure, when does SQL Server permission check kick in?

This is spelled out in some detail here:

https://docs.microsoft.com/en-us/sql/relational-databases/security/permissions-database-engine

But the basic answer is that stored procedures have cached and reused query plans, and permissions checks are not necessary when objects owned by the stored procedure owner are accessed from a stored procedure.  If valid ownership chains exist between the procedure and the object to be accessed, then only EXECUTE permission is checked before the stored procedure is started.  Other permissions checks are skipped.

  Could you come up with a step list about how a query (or SP) is processed after it is submitted?

The main thing to remember here is that all the queries in a batch are parsed and compiled before any of them starts to execute.  Permissions are checked during execution.  And it's even possible to reference a table in a query in such a way that permissions on that table are never actually checked.

If you watch an XE session like this:

CREATE EVENT SESSION [compiles] ON SERVER
ADD EVENT sqlserver.query_post_compilation_showplan(
    ACTION(sqlserver.sql_text)
    WHERE ([sqlserver].[client_app_name]=N'Microsoft SQL Server Management Studio - Query'))
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=1 SECONDS)

And do something like

use tempdb

create table t(id int)
create table w(id int)

create user fred without login

grant select on w to fred

Then:

dbcc freeproccache

go
execute as user='fred'

go
select * from w
select * from t
go

select 1
where 1 = case when 1=1 then 1 else (select count(*) from t) end

revert

You'll see that the query plans in the first batch are both compiled, then the first query runs, then the second fails.  The third query succeeds without a permissions check.


Suppress some output from PL/SQL script in Toad for Oracle Question:
In PL/SQL for Oracle running in Toad, can I suppress script output except for certain lines I want to see? e.g. I don't want create table, insert record, drop table. I do want my own dbms_output.put_line messages.

Example:
Here's my current output file. (This is simplified but it shows the kinds of messages I'm getting.)

  PL/SQL procedure successfully completed.
 Commit complete.
 Table created.
 1 row created.
 1 row created.
 1 row created.
 Dropping table: ER_TEST001S_RES
 Checking results for test001s
         PASS: All records on _res expected results table have matches on actual results table for test001s
         PASS: All records on _res actual results table have matches on expected results table for test001s
  PL/SQL procedure successfully completed.
 Commit complete.

My preferred output would look like this.

 Checking results for test001s
         PASS: All records on _res expected results table have matches on actual results table for test001s
         PASS: All records on _res actual results table have matches on expected results table for test001s
 Checking results for test002s
         PASS: All records on _res expected results table have matches on actual results table for test002s
         PASS: All records on _res actual results table have matches on expected results table for test002s

Context:
I'm using PL/SQL to test a calculation program that runs as a database procedure. My script inserts test records, runs the calculation procedure on them, creates tables of expected results, and compares expected to actual results. (I drop/create the test objects and expected results tables on each test run to make it quick and easy to run all tests at once when moving from dev to test environments.) Currently I have an action in Toad's Automation Designer to run the scripts for all tests, with 'set serveroutput on' and the output going to a file where I check whether any tests failed.

Goal:
Groom the script output to show only lines sent via dbms_output relating to pass/fail status of the test steps. I can't find the right search terms to get a lead on this. Is it possible?

If you were running via SQL*Plus, you could use set feedback off to suppress those messages.  TOAD supports a subset of SQL*Plus commands so I'm not certain that it would support turning feedback off but I would expect that it would.

Personally, though, I'd strongly prefer moving away from a reliance on dbms_output here.  Whatever you build is going to get screwed up as soon as someone adds a couple of dbms_output debugging commands to one of the procedures that you're testing.  Plus, you're already in a database-- it seems to make a lot more sense to have a test_results table somewhere that you insert data into and then run queries against (i.e. get the count by status) once the test suite is done.  If you store those historically and throw in some timestamps, that gives you the ability to do things like look at performance changes over time and things of that nature.


Filter the query result I have one query by which I am getting duplicate ComapnyID and I want to filter that and remove duplicate ComapnyID but don't want change the order by report.

table 1 reports

Table 2 Company

Here is the query

SELECT c.CompanyID,c.`CategoryID`,c.`CompanyName`,  r.`CreatedDate`,r.`ReportID` FROM `company`c
JOIN reports r ON r.CompanyID = c.CompanyID
WHERE r.ProductServiceID= 14 AND c.RegionID=1 AND c.IsActive = 1
ORDER BY r.`ReportID` DESC ;

Result 1

I want to remove the 2nd duplicate ComapnyID in result

I have tried these queries

Distinct
Inner join
Group by with having

if I change the query and get distinct data than the order gets change which I don't want.

Result 2

What am I missing here? Can any of you resolve this issue?

Thanks

By @Akin's help I make this query

SELECT c.CompanyID,  c.`CompanyName`, c.`CompanyCode`, c.`URLPart` FROM `company` c
JOIN reports r ON r.CompanyID = c.CompanyID
WHERE r.ProductServiceID= 14 AND c.RegionID=1 AND c.IsActive = 1
GROUP BY c.CompanyID
ORDER BY MAX(r.`CreatedDate`) DESC

which solve the issue.

Thank you very much @Akin.


In PostgreSQL, what is the behavior of the bit(n) -> "char" cast? Using PostgreSQL if I cast

65::bit(8)::"char"

I get 0, if I however cast

65::bit(8)::int::"char"

I get A as I would expect.

SELECT
  v,
  v::bit(8)::"char" AS noint,
  v::bit(8)::int::"char" AS "viaInt"
FROM (VALUES (65)) AS t(v);
 v  | noint | viaInt
----+-------+--------
 65 | 0     | A
(1 row)

That's because the cast directly to "char" without going through int first is taking the first character from the stringification of the bitfield.

SELECT 65::bit(8);
   bit
----------
 01000001
(1 row)

SELECT '01000001'::"char", '0'::"char";
 char | char
------+------
 0    | 0
(1 row)

If that was greater than 128, you'd have a one in that position,

SELECT 128::bit(8);
   bit
----------
 10000000
(1 row)

Casting that to "char" would give you a 1 rather than a 0,

SELECT 128::bit(8)::"char";
 char
------
 1
(1 row)

Keep in mind, "char" is signed, so the range is [-128,127]. For this reason you can't actually go from bit(8) to "char" without first shifting those bits. For example

SELECT 128::bit(8)::int::"char";
ERROR:  "char" out of range

See also

How exactly does the one-byte “char” type work in PostgreSQL?


Changing location of binlog prevents starting up I've copied data/binlog files between MariaDB (10.3) instances and wanted to change the location of data/log files on the new machine. I adjusted the paths in the my.cnf file. On start on the new machine, the mysqld continues to refer to the old locations, ignoring the paths specified in the my.cnf file - this obviously results in an error like so:

mysqld [ERROR] mysqld: File '<old-location>/mariadb-bin.000160' not found (Errcode: 2 "No such file or directory")
mysqld [ERROR] Failed to open log (file '<old-location>/mariadb-bin.000160', errno 2)
mysqld [ERROR] Could not open log file
mysqld [ERROR] Can't init tc log
mysqld [ERROR] Aborting'

I grepped and found that the ibdata1 file has a reference to the old path - is this something that needs to be updated with mysql CLI prior to the migration?

As far as I can tell, this is not the same as the problem described here: https://stackoverflow.com/questions/7660410/try-to-change-bin-log-directory-mysql-bin-index-not-found-errcode-13

The new instance continues to refer to the old path, despite the my.cnf change. Neither this is a permissions problem (like with apparmor or file ownership).

I finally figured it out: MariaDB keeps an "index" file, fortunately ASCII, that you need to edit: mariadb-bin.index located in the binlog directory (or wherever your log_bin_index configuration points at). This file lists all the binlog files with their full paths. This is unfortunate as the paths should be relative to the log_bin configuration setting. Example:

$ head mariadb-bin.index
/<binlog path>/mariadb-bin.000001
/<binlog path>/mariadb-bin.000002


Example of Logical Schema vs Physical Schema? I've read through multiple Q&As attempting to intuitively understand the difference between logical schemas and physical schemas.
Unfortunately, I've only been able to take away the following vague definitions.

Logical refers to a high-level definition of data including tables, data types, etc.
Physical refers to the 'implementation', i.e. how data is actually stored.

Is this correct? And if so, is there a concrete example or two that can drive home these definitions?
edit: I know that this question has probably been asked many times over, but I'm specifically asking for an illuminating example.

I think this StackOverflow Answer probably defines the difference between the two pretty well.
Specifically these two points:

A logical schema is a conceptual model of the data. In relational databases, it's often platform-agnostic - i.e. the logical schema can, in principle, be implemented on any SQL database.

The physical schema converts the logical schema into an implementation that works on a particular database platform. Sometimes, this is a largely mechanical exercise, applying the right datatypes to the attributes...

So in other words, if you had a schema that represented MovieTheaters, you'd probably have a few tables Movies, TicketSales, ConcessionSnacks. The TicketSales table would probably have a TicketId column, a Price column, and a MovieId column. This high level detail is essentially your logical schema.
Once you start implementing this schema on a specific database system, take Microsoft SQL Server for example, and using features and data types specific to Microsoft SQL Server, is when it becomes a physics schema. For example, your TicketSales table's TicketId column could be a INT data type with a primary key on it, and the MovieId could be a BIGINT field with a many-to-one foreign key on it, and the Price column could be a DECIMAL(4,2). And the Movies table can have a unique constraint on the MovieTitle column to prevent duplicate Movies being inserted into it.


Data obfuscation in SQL Server What is the best practice for Data Obfuscation in SQL Server?

We'd like to use masked Production data in our UAT system.

If we want to do it quickly, and with a higher level of obsfucation, what approach should be taken?  I'm thinking about Character scrambling for people's given name and family name, but how?  Should I create a function myself or there is any predefined functions available to use?  I don't want to spend time re-inventing the wheel :)

How about for date fields?  For example should date of birth be randomly picked from the whole table and assigned to a record, or there is a better way of doing that?

I wish I could upvote you 100 points just for thinking about this! I have seen this subject overlooked so many times it's untrue - so well done. From what I understand you actually want to scramble the data within the fields themselves, and although I understand what you are trying to achieve it might not be quite necessary to do so - although it should be considered on a case-by-case basis.

Most data protection laws revolve around the ability to correctly associate a piece of data with an individual - for example a date of birth or a phone number. You can meet the requirements of the law by ensuring that when you move your data out of production into UAT it is jumbled up so it is not easily re-mapped to the original person - especially when you jumble forename and surnames.

However, this does not address the issue for instance of let's say contact details. You can meet the requirements of the law by jumbling the data but the phone numbers are still real, the emails still real etc... they are just not assigned to the correct person. For this I recommend if at all possible clearing that data before passing it into UAT, Red Gate do a piece of software called Data Generator that can create random test data for you so that you can repopulate the fields with data that can be tested against.

As for data scrambling: there exists many applications that do this for you and honestly you are correct in not wanting to reinvent the wheel. The one that we use at our company is a product called Data Masker by a company called Net2000. The license is pretty cheap, it works extremely fast and you don't have to worry about having to disable all your constraints before scrambling the database.

You can of course roll your own solution should you not find anything that meets your requirements - if you do decide to do this I would strongly recommend using CLR procedures to do it as it is much more flexible than pure TSQL (not to say that you can't use TSQL see here).

Once you have chosen an application to perform this for you the next thing you need to decide is what is it you actually want/need to scramble? Honestly your best resource for this is your company legal team and or the company auditors. I know that sometimes we may not like working with them but they will be much nicer to you for approaching them and asking them the question rather than trying to do it on your own and get it wrong, there is absolutely nothing wrong with asking for help - especially when it is as important as this.

I hope this helps you and I wish you good luck in your quest... ;-)