ERROR 1044 (42000): Access denied for user 'globacom'@'localhost' to database 'avi_globacomglobacom_data4.sql' I had created the database with the name globacom

  create database globacom;

and grant all the permissions as

    grant all on globacom.* to 'globacom'@'localhost' identified by 'globacom';

after this I have looged in using this credentials

       mysql -u globacom -pglobacom

when I import the .sql file  using this

    source E:\ravi_globacom\globacom_data4.sql

I am getting an error as

 ERROR 1044 (42000): Access denied for user 'globacom'@'localhost' to database 'avi_globacomglobacom_data4.sql'

please anyone can help me.

You need to use forward slashes for paths, even on Windows. The mysql command line is interpreting the sequence \r as a connect command and things blow up. (This quite visible in the error message: database 'avi_globacomglobacom_data4.sql'.)

So try:

source E:/ravi_globacom/globacom_data4.sql

Or don't use source, but shell/cmd redirection instead:

mysql -u globacom -pglobacom < E:\ravi_globacom\globacom_data4.sql

depending on what's most practical.


Oracle Change Data Capture: Unsubscribed column results in change set record I am using Oracle 11g's Change Data Capture to track changes on a database table. I am using synchronous mode to minimize latency. Here's an example to illustrate my problem:

Published_Table

important_column_a
important_column_b
useless_column_c

Now, both important_column_a and important_column_b are included in the PUBLICATION and SUBSCRIPTION. The third column, useless_column_c, is of no consequence and is not included in either PUBLICATION or SUBSCRIPTION.

My problem is that any time a change is made to the unpublished column (useless_column_c), a record is written to both the CDC table and the resultant CDC View on the subscription. This happens even if no other change is made to the table aside from the unpublished column.

The Published_Table experiences a significant number of transactions, but only changes to the subscribed columns require additional processing. As Oracle is sending every change across to the subscription, the result is an extensive amount of overhead as the subscriber sifts through what amounts to extra "noise."

Is there any way around this?

I imagine you've enabled suplemental logging for the table on all columns, instead of just the ones that you require to be tracked.

eg:

ALTER TABLE philtest ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

vs:

ALTER TABLE philtest
ADD SUPPLEMENTAL LOG GROUP log_group_philtest
    (PHIL_COLA, PHIL_COLB) ALWAYS;


Creating a foreign server in Postgres only if not exists As far as I know in order to create a foreign server (for a FDW) you need to do the following:

CREATE SERVER my_wrapper FOREIGN DATA WRAPPER postgres_fdw OPTIONS (
    dbname 'wrapperDB',
    host '127.0.0.1',
    port '5432'
);")

But because I create a migration script for an existing database, I want to be able to check whether this server exists with the same name and then create in a similar approach that is used upon tables:

CREATE TABLE IF NOT EXISTS mytable ...

Or in stored procedures:

CREATE OR REPLACE FUNCTION public.myfunction(text)....

Do you have any idea how to do that?

Try a DO statement:

DO
$$BEGIN
   CREATE SERVER ...;
EXCEPTION
   WHEN duplicate_object THEN
      NULL;  -- ignore
END;$$;


SQL Server 2014: missing replication option This article details exact steps to set up basic database replication with SQL Server 2014.

It starts with this:

  Right-click the Replication folder, and then click Configure Distribution.

On right clicking my Replication folder (I do believe I installed replication), I only get the following options:

Launch Replication Monitor
Generate Scripts....
Update Replication Passwords...

I can't find any other place in SQL Server Mgmt Studio to get to this section. What am I missing?

You are likely using the Express Edition.  Note that Express Edition can only act as a Subscriber.  Try using Standard Edition or above to Configure Distribution and Publishing.


MySQL MyISAM Table Locking and Delayed Inserts for Webserver Access Logs We will install a webserver that writes access logs to a MySQL table. Access logs of course will get really really big, so this will be a separate MySQL instance from the regular data and we will more librally delete the binlogs.

We will also have to delete older items from the table which means delete commands and optimize commands that will make the inserts wait.

Is there a way to have delayed inserts that are in a preserved order and that will occasionally tolerate many items in the delayed insert queue?

If the inserts will get mixed up then it is not a show stopper. We can use timestamps and milliseconds for sorting.

Also, can we prevent wasteful growth of index files? Perhaps we can optimize the indexes similar to optimizing the data files? Would requiring the indexes to be fixed width prevent data rotation waste problems?

FWIW, I think this is the wrong way to do this; unless you need to do real-time reporting on webserver access write the log as a file and bulk-load it.

Having said that, there is a way to do what you want, which if I understand it correctly is to queue up pending inserts while doing maintenance on your database. In other words, decoupling the webserver's INSERT operation from the actual INSERT in the database in some sort of buffer. The way to do it using two instances, one that is directly written to in which you will use the blackhole storage engine, and one that you will replicate to, which will be your "real" database. The pending inserts will live in the binlogs; you simply stop replicating to the slave, perform your maintenance, then resume replication again and the slave/real database catches up. The slave is available for SELECTs for your reporting. You can even multi-master for load balancing, or have multiple slaves and switch between them during maintenance activities.


Select * from View takes 4 minutes I am running in to an issue where when I run a query against a view it takes 4+ minutes.  However, when I run the guts of the query it finishes in like 1 second.

The only thing I am not sure about is the tables being joined are both temporal tables.

Ad hoc query plan:
https://www.brentozar.com/pastetheplan/?id=BykohB2p4

View query plan:
https://www.brentozar.com/pastetheplan/?id=SkIfTHh6E

Any suggestions on where to try and figure this out?

View code:

ALTER VIEW [dbo].[vwDealHistoryPITA]
AS
SELECT ROW_NUMBER() OVER (PARTITION BY cm.CodeMasterID ORDER BY cm.CodeMasterID, cm.LastUpdateDate) AS Deal_HistoryID,
       cm.CodeMasterID,
       cm.ProjectName,
       cm.[Status],
       d.CompanyID,
       d.DealTypeMasterID,
       cm.[Description],
       d.PassiveInd,
       d.ApproxTPGOwnership,
       d.NumberBoardSeats,
       d.FollowonInvestmentInd,
       d.SocialImpactInd,
       d.EquityInd,
       d.DebtInd,
       d.RealEstateInd,
       d.TargetPctgReturn,
       d.ApproxTotalDealSize,
       cm.CurrencyCode,
       d.ConflictCheck,
       cm.CreatedDate,
       cm.CreatedBy,
       cm.LastUpdateDate,
       cm.LastUpdateBy,
       d.ExpensesExceedThresholdDate,
       d.CurrentTPGCheckSize,
       d.PreferredEquityInd,
       d.ConvertibleDebtInd,
       d.OtherRealAssetsInd,
       d.InitialTPGCheckSize,
       d.DirectLendingInd,
       cm.NameApproved,
       cm.FolderID,
       cm.CodaProcessedDateTime,
       cm.DeadDate,
       d.SectorMasterID,
       d.DTODataCompleteDate,
       cm.ValidFrom AS CodeMasterValidFrom,
       cm.ValidTo   AS CodeMasterValidTo,
       d.validFrom  AS DealValidFrom,
       d.validTo    AS DealValidTo
FROM dbo.CodeMaster FOR SYSTEM_TIME ALL cm
INNER JOIN dbo.Deal FOR SYSTEM_TIME ALL d ON cm.CodeMasterID = d.CodeMasterID;
GO

Added the Partition by and get similar results to the ad hoc query.

The main performance differences

The main differences here are that the better performing query is pushing down the seek predicate on CodeMasterID on all 4 tables (2 temporal tables (actual & history)) where the select on the view appears to not do that until the end (filter operator).

TL DR;

The issue is due to to parameters not pushing down to window functions in certain cases such as views. The easiest solution is adding OPTION(RECOMPILE) to the view call as to make the optimizer 'see' the params at runtime if that is a possibility. If it is too expensive to recompile the execution plan for each query call, using an inline table valued function that expects a parameter could be a solution. There is an excellent Blogpost by Paul White on this. For a more detailed way on finding and resolving your particular issue, keep on reading.

The better performing query

Codemaster table

Deal table

I love the smell of seek predicates in the morning

The big bad query

Codemaster table

This is a predicate only zone

The Deal table

But the optimizer did not read 'The art of the deal™"

...and does not learn from the past

Until all that data reaches the filter operator

So, what gives?

The main problem here is the optimizer not 'seeing' the parameters at runtime due to the window functions in the view and not being able to use the SelOnSeqPrj (select on sequence project, further down in this post for reference).

I was able to replicate the same results with a test sample and using SP_EXECUTESQL to parameterize the call to the view. See addendum for the DDL / DML

executing a query against a test view with a window function and an INNER JOIN

SET STATISTICS IO, TIME ON;
EXEC SP_EXECUTESQL
N'SELECT * FROM  dbo.Bad
Where CodeMasterID = @P1',N'@P1 INT',@P1 = 37155;

Resulting in about 4.5s of cpu time and 3.2s elapsed time

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

When we add the sweet embrace of OPTION(RECOMPILE)

SET STATISTICS IO, TIME ON;
EXEC SP_EXECUTESQL
N'SELECT * FROM  dbo.Bad
Where CodeMasterID = @P1 OPTION(RECOMPILE)',N'@P1 INT',@P1 = 37155;

It is all good.

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

Why

This all again supports the point of not being able to apply the @P1 predicate to the tables because of the window function & parameterization resulting in the filter operator

Not only an issue for temporal tables

See addendum 2

Even when not using temporal tables, this happens:

The same result is seen when writing the query like this:

DECLARE @P1 int = 37155
SELECT * FROM  dbo.Bad2
Where CodeMasterID = @P1;

Again, the optimizer is not pushing down the predicate before applying the window function.

When omitting the ROW_NUMBER()

CREATE VIEW dbo.Bad3
as
SELECT
cm.CodeMasterID,CM.ManagerID,cm.ParentDeptID,d.DealID, d.CodeMasterID as dealcodemaster,d.EvenMoreBlaID
FROM dbo.CodeMaster2  cm
INNER JOIN dbo.Deal2  d ON cm.CodeMasterID = d.CodeMasterID;

All is well

SET STATISTICS IO, TIME ON;
EXEC SP_EXECUTESQL
N'SELECT * FROM  dbo.Bad3
Where CodeMasterID = @P1',N'@P1 INT',@P1 = 37155

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

so where does all that leave us?

The ROW_NUMBER() is calculated before the filter is applied on the bad queries.

And all this leads us to this blogpost from 2013 by Paul White
on window functions and views.

One of the important parts for our example is this statement:

  Unfortunately, the SelOnSeqPrj simplification rule only works when the
  predicate performs a comparison with a constant. For that reason, the
  following query produces the sub-optimal plan on SQL Server 2008 and
  later:

DECLARE @ProductID INT = 878;

SELECT
    mrt.ProductID,
    mrt.TransactionID,
    mrt.ReferenceOrderID,
    mrt.TransactionDate,
    mrt.Quantity
FROM dbo.MostRecentTransactionsPerProduct AS mrt
WHERE
    mrt.ProductID = @ProductID;

This part corresponds to what we have seen when declaring the parameter ourselves / using SP_EXECUTESQL on the view.

The actual solutions

1: OPTION(RECOMPILE)

We know that OPTION(RECOMPILE) to 'see' the value at runtime is a possibility. When recompiling the execution plan for each query call is too expensive, there are other solutions.

2: Inline table valued function with a parameter

CREATE FUNCTION dbo.BlaBla
(
    @P1 INT
)
RETURNS TABLE
WITH SCHEMABINDING AS
RETURN
    (
     SELECT
     ROW_NUMBER() OVER (PARTITION BY cm.CodeMasterID ORDER BY cm.CodeMasterID) AS Deal_HistoryID,
     cm.CodeMasterID,CM.ManagerID,
     cm.ParentDeptID,d.DealID,
     d.CodeMasterID as dealcodemaster,
     d.EvenMoreBlaID
    FROM dbo.CodeMaster2  cm
    INNER JOIN dbo.Deal2  d ON cm.CodeMasterID = d.CodeMasterID
    Where cm.CodeMasterID = @P1
    )
EXEC SP_EXECUTESQL
N'SELECT * FROM  dbo.BlaBLa(@P1)',N'@P1 INT',@P1 = 37155

Resulting in the expected seek predicates

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

With about 9 logical reads on my test

3: Writing the query without the use of a view.

The other 'solution' could be writing the query entirely without the use of a view.

4: Not keeping the ROW_NUMBER() function in the view, instead specifying it in the call to the view.

An example of this would be:

CREATE VIEW dbo.Bad2
as
SELECT
cm.CodeMasterID,CM.ManagerID,cm.ParentDeptID,d.DealID, d.CodeMasterID as dealcodemaster,d.EvenMoreBlaID
FROM dbo.CodeMaster2  cm
INNER JOIN dbo.Deal2  d ON cm.CodeMasterID = d.CodeMasterID;

GO
SET STATISTICS IO, TIME ON;
EXEC SP_EXECUTESQL
N'SELECT ROW_NUMBER() OVER (PARTITION BY CodeMasterID ORDER BY CodeMasterID) AS Deal_HistoryID,* FROM  dbo.Bad2
Where CodeMasterID = @P1',N'@P1 INT',@P1 = 37155;

There should be other creative ways around this issue, the important part is knowing what causes it.

Addendum #1

CREATE TABLE dbo.Codemaster
(
     CodeMasterID int NOT NULL PRIMARY KEY CLUSTERED
   , ManagerID INT  NULL
   , ParentDeptID int NULL
   , SysStartTime datetime2 GENERATED ALWAYS AS ROW START NOT NULL
   , SysEndTime datetime2 GENERATED ALWAYS AS ROW END NOT NULL
   , PERIOD FOR SYSTEM_TIME (SysStartTime,SysEndTime)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.Codemaster_History))
;

CREATE TABLE dbo.Deal
(
     DealID int NOT NULL PRIMARY KEY CLUSTERED
   , CodeMasterID INT  NULL
   , EvenMoreBlaID int NULL
   , SysStartTime datetime2 GENERATED ALWAYS AS ROW START NOT NULL
   , SysEndTime datetime2 GENERATED ALWAYS AS ROW END NOT NULL
   , PERIOD FOR SYSTEM_TIME (SysStartTime,SysEndTime)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.Deal_History))
;

INSERT INTO dbo.Codemaster(CodeMasterID,ManagerID,ParentDeptID)
SELECT TOP(1000000) ROW_NUMBER() OVER(ORDER BY(SELECT NULL)) as rownum1,
                    ROW_NUMBER() OVER(ORDER BY(SELECT NULL)) as rownum2,
                    ROW_NUMBER() OVER(ORDER BY(SELECT NULL)) as rownum3
FROM MASTER..spt_values as spt1
CROSS JOIN MASTER..spt_values as spt2;

INSERT INTO dbo.Deal(DealID,CodeMasterID,EvenMoreBlaID)
SELECT TOP(1000000) ROW_NUMBER() OVER(ORDER BY(SELECT NULL)) as rownum1,
                    ROW_NUMBER() OVER(ORDER BY(SELECT NULL)) as rownum2,
                    ROW_NUMBER() OVER(ORDER BY(SELECT NULL)) as rownum3
FROM MASTER..spt_values as spt1
CROSS JOIN MASTER..spt_values as spt2;

CREATE INDEX IX_CodeMasterID
ON dbo.Deal(CodeMasterId);
CREATE INDEX IX_CodeMasterID
ON dbo.Deal_History(CodeMasterId);

CREATE INDEX IX_CodeMasterID
ON dbo.Codemaster(CodeMasterId);
CREATE INDEX IX_CodeMasterID
ON dbo.Codemaster_History(CodeMasterId);

SELECT ROW_NUMBER() OVER (PARTITION BY cm.CodeMasterID ORDER BY cm.CodeMasterID, cm.SysStartTime) AS Deal_HistoryID,
cm.*, d.*
FROM dbo.CodeMaster FOR SYSTEM_TIME ALL cm
INNER JOIN dbo.Deal FOR SYSTEM_TIME ALL d ON cm.CodeMasterID = d.CodeMasterID
Where cm.CodeMasterID = 37155;

-- Guud
GO
CREATE VIEW dbo.Bad
as
SELECT ROW_NUMBER() OVER (PARTITION BY cm.CodeMasterID ORDER BY cm.CodeMasterID, cm.SysStartTime) AS Deal_HistoryID,
cm.CodeMasterID,CM.ManagerID,cm.ParentDeptID,d.DealID, d.CodeMasterID as dealcodemaster,d.EvenMoreBlaID
FROM dbo.CodeMaster FOR SYSTEM_TIME ALL cm
INNER JOIN dbo.Deal FOR SYSTEM_TIME ALL d ON cm.CodeMasterID = d.CodeMasterID

GO
EXEC SP_EXECUTESQL
N'SELECT * FROM  dbo.Bad
Where CodeMasterID = @P1',N'@P1 INT',@P1 = 37155

-- Very bad shame on you

Addendum #2

CREATE TABLE dbo.Codemaster2
(
     CodeMasterID int NOT NULL PRIMARY KEY CLUSTERED
   , ManagerID INT  NULL
   , ParentDeptID int NULL

);

CREATE TABLE dbo.Deal2
(
     DealID int NOT NULL PRIMARY KEY CLUSTERED
   , CodeMasterID INT  NULL
   , EvenMoreBlaID int NULL
);

INSERT INTO dbo.Codemaster2(CodeMasterID,ManagerID,ParentDeptID)
SELECT TOP(1000000) ROW_NUMBER() OVER(ORDER BY(SELECT NULL)) as rownum1,
                    ROW_NUMBER() OVER(ORDER BY(SELECT NULL)) as rownum2,
                    ROW_NUMBER() OVER(ORDER BY(SELECT NULL)) as rownum3
FROM MASTER..spt_values as spt1
CROSS JOIN MASTER..spt_values as spt2;

INSERT INTO dbo.Deal2(DealID,CodeMasterID,EvenMoreBlaID)
SELECT TOP(1000000) ROW_NUMBER() OVER(ORDER BY(SELECT NULL)) as rownum1,
                    ROW_NUMBER() OVER(ORDER BY(SELECT NULL)) as rownum2,
                    ROW_NUMBER() OVER(ORDER BY(SELECT NULL)) as rownum3
FROM MASTER..spt_values as spt1
CROSS JOIN MASTER..spt_values as spt2;

CREATE INDEX IX_CodeMasterID
ON dbo.Deal2(CodeMasterId);
CREATE INDEX IX_CodeMasterID
ON dbo.Codemaster2(CodeMasterId);

SELECT ROW_NUMBER() OVER (PARTITION BY cm.CodeMasterID ORDER BY cm.CodeMasterId) AS Deal_HistoryID,
cm.*, d.*
FROM dbo.CodeMaster2 cm
INNER JOIN dbo.Deal2 d ON cm.CodeMasterID = d.CodeMasterID
Where cm.CodeMasterID = 37155;

-- Guud
GO
CREATE VIEW dbo.Bad2
as
SELECT ROW_NUMBER() OVER (PARTITION BY cm.CodeMasterID ORDER BY cm.CodeMasterID) AS Deal_HistoryID,
cm.CodeMasterID,CM.ManagerID,cm.ParentDeptID,d.DealID, d.CodeMasterID as dealcodemaster,d.EvenMoreBlaID
FROM dbo.CodeMaster2  cm
INNER JOIN dbo.Deal2  d ON cm.CodeMasterID = d.CodeMasterID

GO
SET STATISTICS IO, TIME ON;
EXEC SP_EXECUTESQL
N'SELECT * FROM  dbo.Bad2
Where CodeMasterID = @P1',N'@P1 INT',@P1 = 37155


Violation of PRIMARY KEY constraint when there is no duplicate I inherited an old sql server data base, and I need to create trigger that copy particular row with new dates to the same table. I have to manually set Id of objects and I am getting Violation of PRIMARY KEY although there is no record with that Id in the table( I even tried to set Id to ridiculously high value, but error is the same)

CREATE TRIGGER [dbo].[tr_HR_WORKER_UPDATED]
   ON [server].[dbo].[HR_WORKER]
   AFTER UPDATE
AS BEGIN
    SET NOCOUNT ON;
    IF UPDATE (ID_ORGANIZATION)
    BEGIN

        DECLARE @IdCounter NUMERIC(15, 4)
        DECLARE @NextId NUMERIC(15, 4) --Yes, Id is numeric
        SET @IdCounter = 148
        SET @NextId = (SELECT wartosc from AA_COUNTER where id_licznik = IdCounter) + 1.0000 --I have to get ID from other table, increment, and update that table
        select @NextId

        UPDATE AA_COUNTER--updating counter
        SET wartosc = @NextId
        WHERE id_licznik = @IdCounter

        INSERT INTO TR_ASSIGNMENT_ORG(
                                   [ID_ASSIGNMENT_ORG]
                                  ,[FIELD1]
                                  ,[FIELD2]
                                  ,[FIELD3]
                                  ,[DATE_FROM]
                                  ,[DATE_TO]
                                  ,[ID_RESPONSIBLE_WORKER]
                                  ,[ID_WORKER]
                                  ,[ID_STRUCT_ORG]
                                  ,[STACJA]
                                  ,[CHANGE_DATE]
                                  )
        SELECT
                                   @NextId
                                  ,P.[FIELD1]
                                  ,P.[FIELD2]
                                  ,P.[FIELD3]
                                  ,GETDATE()
                                  ,NULL
                                  ,P.[ID_RESPONSIBLE_WORKER]
                                  ,P.[ID_WORKER]
                                  ,I.[ID_STRUCT_ORG]
                                  ,P.[STACJA]
                                  ,GETDATE() -- I can execute code to this line
                                  FROM dbo.TR_ASSIGNMENT_ORG as P, inserted as I
                                  WHERE ID_RESPONSIBLE_WORKER = I.ID_WORKER

        UPDATE TR_ASSIGNMENT_ORG
        SET DATE_TO = GETDATE()
        FROM inserted I
        WHERE TR_ASSIGNMENT_ORG.ID_RESPONSIBLE_WORKER = I.ID_WORKER
        AND TR_ASSIGNMENT_ORG.DATA_DO IS NULL

    END
END

Can someone explain to me why sql server throws "Violation of PRIMARY KEY X" when there is no key with this value in table?

ID_ASSIGNMENT_ORG is primary key, and i want to assign @NextId to it

Do you have recursive triggers set? If so you might be re-firing the trigger from inside.
In any case, as previous commenters have noted: you are generating a single ID value, but a trigger may get fired (or your internal join may produce) more than 1 row to be put in the table.
There is nothing to stop this clause:

FROM dbo.TR_ASSIGNMENT_ORG as P, inserted as I
    WHERE ID_RESPONSIBLE_WORKER = I.ID_WORKER

from returning many records.

Ideally you would to find a way to remove the ID generation from the inside of the trigger (eg, convert the ID to an identity & set the seed above the current high watermark of the ID column) - may require app change if records are not inserted via stored proc which are in your gift to change.

You might consider aborting if you are given multiple records (insert count > 1). If you (experimentally) add that and still have a problem then for sure it's your trigger code thats generating the additional inserts.


Cleaning up bad data with REGEXP_REPLACE too aggressive with non latin chars Trying to use REGEXP_REPLACE to clean up a text field in a table.
Here's my query:
select title original ,REGEXP_REPLACE(title, '[^a-zA-Z0-9]+', ' ') clean
from tmp_table2
WHERE txn_title like '%?%'

Here are my rows:
original                                              clean
�� ���� ?���?� ����� Lionel Casson ��    Lionel Casson
��  �?� ?���?� ?���� �?� �. �?����
� Les phobies � l'enfant : impasse ou passage ?      Les phobies chez l enfant impasse ou passage
- Snälla du! Kan du sätta? : om vägledni i förskolan   Sn lla du Kan du s tta dig om v gledning i f rskolan

The first three rows above are ok for my needs, but the 4th row is not, I want to keep those non-latin characters (words with umlauts, etc...)
How can I keep those character types (non-latin) by using one regex in the replace function above, while keeping the rest of the results as shown in the 'clean' column?

Given proper lc_ctype (not C) and encoding (especially not SQL_ASCII), character classes could be used in regular expressions to include accents without having to specify ranges.

Example:

=# show lc_ctype;
  lc_ctype
-------------
 fr_FR.UTF-8

=# show server_encoding ;
 server_encoding
-----------------
 UTF8

=# \set raw_text '- Snälla du! Kan du sätta? : om vägledni i förskolan'

=# select regexp_replace(:'raw_text', '\w', ' ', 'g')
                    regexp_replace
------------------------------------------------------
   Snälla du  Kan du sätta    om vägledni i förskolan

If you wanted to keep punctuation signs:

=# select regexp_replace(:'raw_text', '[^\w[:punct:]]', ' ', 'g');
                    regexp_replace
------------------------------------------------------
 - Snälla du! Kan du sätta? : om vägledni i förskolan

Another possilibity to clean up could be to filter out non-printable characters:

=# select regexp_replace(:'raw_text', '[^[:print:]]', ' ', 'g');
                    regexp_replace
------------------------------------------------------
 - Snälla du! Kan du sätta? : om vägledni i förskolan


Search in JSON by id and name I have this table:
id number
name text

row for example:
id name
1 {"vals":[{"id":123,"name":""}],"additional":"text"}
2 {"vals":[{"id":123}],"additional":"text text"}
3 {"vals":[{"id":222},{"id":123,"name":"this is the one"}],"additional":"text text"}
4 {"vals":[{"id":222,"name":"this is the one"},{"id":123}],"additional":"text text"}

I want to return the ids where vals[*].id=123 and their other name element is not empty(not other name in the json).
So only this one will turn:
    3 {"vals":[{"id":222},{"id":123,"name":"this is the one"}],"additional":"text text"}

I'm not strong with json type queries so I only got:
select id
from tab
where name ->>"$.vals[0].id"=123

I should have done "$.vals[*].id", but that returns to me [123] and that is not = 123
Appreciate your help!

JSON is always tricky and you have to search a lot of tutorials to get the hang of it.
But you can use normalized table instead of JSON.

CREATE TABLE tab (
  `id` INTEGER,
  `name` JSON
);

INSERT INTO tab
  (`id`, `name`)
VALUES
  ('1', '{"vals":[{"id":123,"name":""}],"additional":"text"}'),
  ('2', '{"vals":[{"id":123}],"additional":"text"}'),
  ('3', '{"vals":[{"id":123,"name":"this"}],"additional":"text"}'),
  ('4', '{"vals":[{"id":222,"name":"this"}],"additional":"text"}');

SELECT id, name
FROM tab WHERE JSON_CONTAINS(name->>'$.vals[*].id','123','$')
AND (name->>'$.vals[*].name' IS NOT NULL AND  NOT `name`->'$.vals[*].name' = JSON_ARRAY("")  )
;

id | name
-: | :------------------------------------------------------------
 3 | {"vals": [{"id": 123, "name": "this"}], "additional": "text"}

db<>fiddle here


Why is a new user allowed to create a table? I'm wondering why a newly created user is allowed to create a table after connecting to a database. I have one database, project2_core:

postgres=# \l
                                          List of databases
     Name      |    Owner     | Encoding  |   Collate   |    Ctype    |       Access privileges
---------------+--------------+-----------+-------------+-------------+-------------------------------
 postgres      | postgres     | SQL_ASCII | C           | C           |
 project2_core | atm_project2 | UTF8      | de_DE.UTF-8 | de_DE.UTF-8 | project2=CTc/project2
 template0     | postgres     | SQL_ASCII | C           | C           | =c/postgres                  +
               |              |           |             |             | postgres=CTc/postgres
 template1     | postgres     | SQL_ASCII | C           | C           | =c/postgres                  +
               |              |           |             |             | postgres=CTc/postgres
(5 rows)

So far so good. Now I create a user:

postgres=# CREATE ROLE dietrich ENCRYPTED PASSWORD 'md5XXX' LOGIN NOCREATEROLE NOCREATEDB NOSUPERUSER

Okay. When I try to connect to the database, the user is not allowed to do so:

$ psql -h localhost -p 5432 -U dietrich -W project2_core
Password for user dietrich:
psql: FATAL:  permission denied for database "project2_core"
DETAIL:  User does not have CONNECT privilege.

This is what I expected. Now the strange stuff starts. I grant the user CONNECT:

postgres=# GRANT CONNECT ON DATABASE project2_core TO dietrich;
GRANT
postgres=# \l
                                          List of databases
     Name      |    Owner     | Encoding  |   Collate   |    Ctype    |       Access privileges
---------------+--------------+-----------+-------------+-------------+-------------------------------
 postgres      | postgres     | SQL_ASCII | C           | C           |
 project2_core | atm_project2 | UTF8      | de_DE.UTF-8 | de_DE.UTF-8 | project2=CTc/project2+
               |              |           |             |             | dietrich=c/project2
 template0     | postgres     | SQL_ASCII | C           | C           | =c/postgres                  +
               |              |           |             |             | postgres=CTc/postgres
 template1     | postgres     | SQL_ASCII | C           | C           | =c/postgres                  +
               |              |           |             |             | postgres=CTc/postgres
(5 rows)

And without any further grants, the user is allowed to create a table:

$ psql -h localhost -p 5432 -U dietrich -W project2_core
Password for user dietrich:
psql (9.2.3)
SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)
Type "help" for help.

project2_core=> create table adsf ();
CREATE TABLE
project2_core=> \d
        List of relations
 Schema | Name | Type  |  Owner
--------+------+-------+----------
 public | adsf | table | dietrich
(1 row)

I would have expected that the user is not allowed to do anything before I explicitly do GRANT USAGEon the schema and then GRANT SELECT on the tables.

Where is my mistake? What am I doing wrong? How can I achieve what I want (that a new user is not allowed to do anything before explicitly granting her the appropriate rights.

I'm lost, and your help is greatly appreciated :)

EDIT Following the advice by @daniel-verite, I now revok all immediately after creating the database. The user dietrich is not allowed to create a table any more. Good. BUT: Now, also the owner of the database, project2, is not allowed to create a table. Even after issuing GRANT ALL PRIVILEGES ON DATABASE project2_core TO project2 and GRANT ALL PRIVILEGES ON SCHEMA public TO project2, I get an error ERROR:  no schema has been selected to create in, and when I specifically try to CREATE TABLE public.WHATEVER ();, I get ERROR:  permission denied for schema public. What am I doing wrong?

When you create a new database, any role is allowed to create objects in the public schema. To remove this possibility, you may issue immediately after the database creation:

REVOKE ALL ON schema public FROM public;

Edit: after the above command, only a superuser may create new objects inside the public schema, which is not practical. Assuming a non-superuser foo_user should be granted this privilege, this should be done with:

GRANT ALL ON schema public TO foo_user;

To know what ALL means for a schema, we must refer to GRANT in the doc, (in PG 9.2 there are no less than 14 forms of GRANT statements that apply to different things...). It appears that for a schema it means CREATE and USAGE.

On the other hand, GRANT ALL PRIVILEGES ON DATABASE... will grant CONNECT and CREATE and TEMP, but CREATE in this context relates to schemas, not permanent tables.

Regarding this error: ERROR: no schema has been selected to create in, it happens when trying to create an object without schema qualification (as in create table foo(...)) while lacking the permission to create it in any schema of the search_path.


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

I execute this on ServerB but I get error:

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

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

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

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

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

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

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

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

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

You wrote that "I have given NT SERVICE\MSSQLSERVER full control", so it looks like your SQL Server is running under "local system accounts".
If you granted full control to NT SERVICE\MSSQLSERVER on a second server, it will not work, because NT SERVICE\MSSQLSERVER is a different account on each machine.
You need to grant all network privileges to account [DomainName]\[ServerName]$ for a local service accounts to have access to network resources.
In your case it will be [DomainName]\ServerA$ account and this account will need to have access to the network share.


Foreign key: in which table? I made the tables blood_types and persons. Should the foreign key  be created in the blood_types or in the persons table like I did?

A person can have 1 blood type. A blood type can be assigned to multiple persons.

CREATE TABLE blood_types
(
 blo_id serial NOT NULL,
 blo_name character varying(10),
 CONSTRAINT blood_types_pkey PRIMARY KEY (blo_id)
)

CREATE TABLE persons
(
  per_id serial NOT NULL,
  per_passport character varying(50),
  per_name character varying(50),
  per_blood_type integer,
  CONSTRAINT persons_pkey PRIMARY KEY (per_id),
  CONSTRAINT persons_per_blood_type_fkey FOREIGN KEY (per_blood_type)
      REFERENCES per_blood_types (blo_id) MATCH SIMPLE
)

Or maybe should the foreign key be created in the blood_type table?
I have more similars and simples examples but I can't understand.
Should I think as weak and strong entities (I think both are strong) or maybe having in mind the cardinalities?

In your blood_types table,

CREATE TABLE blood_types
(
 blo_id serial NOT NULL,
 blo_name character varying(10),
 CONSTRAINT blood_types_pkey PRIMARY KEY (blo_id)
)

You correctly have blo_id as a PRIMARY KEY.

And then in the persons table, have the per_blood_type integer as a FOREIGN KEY referencing blo_id in table blood_types (as you appear to have - can't check exact syntax - don't have running PostgreSQL system at the minute). The way you have have it appears to be the correct way - blood type depends on the person and only the person - not the other way round.


How to remove Data Quality Services completely? When I setup one of our SQLServer 2012 instances a while back, I didn't realize that I have checked "Data Quality Services" feature. Looking at my installation notes, I find that I didn't end up performing any of the post-install task to install this feature so we didn't end up using this feature.

However, when I run the "Installed SQL Server features discovery report", the report would shows that this feature is configured, even though we don't have any of the DQS databases and any of its objects loaded in the instance.

Can someone tell me how to completely remove this feature so that it doesn't show up anymore in the Microsoft SQL Server 2012 Setup Discovery Report?
As always, your help is greatly appreciated.

Follow the instruction given by Microsoft in this article.

Remove Data Quality Server Objects


Fill in the gaps between rows using the previous row's values I have a road_insp table:

create table road_insp
   (
    insp_id int,
    road_id int,
    insp_date date,
    condition number(38,2)
   ) ;

insert into road_insp (insp_id, road_id, insp_date, condition) values (1,100,to_date('01-APR-04','DD-MON-YY'),.9);
insert into road_insp (insp_id, road_id, insp_date, condition) values (2,100,to_date('01-APR-11','DD-MON-YY'),.7);
insert into road_insp (insp_id, road_id, insp_date, condition) values (3,100,to_date('01-MAR-12','DD-MON-YY'),.7);
insert into road_insp (insp_id, road_id, insp_date, condition) values (4,100,to_date('01-MAR-17','DD-MON-YY'),.6);
insert into road_insp (insp_id, road_id, insp_date, condition) values (5,100,to_date('01-MAR-18','DD-MON-YY'),.6);

insert into road_insp (insp_id, road_id, insp_date, condition) values (6,200,to_date('01-JUN-10','DD-MON-YY'),.4);
insert into road_insp (insp_id, road_id, insp_date, condition) values (7,200,to_date('01-JUN-12','DD-MON-YY'),.3);

commit;

select
    insp_id,
    road_id,
    extract(year from insp_date) as insp_year,
    condition as condition
from
    road_insp
order by
    road_id,
    insp_date
;
   INSP_ID    ROAD_ID  INSP_YEAR  CONDITION
---------- ---------- ---------- ----------
         1        100       2004         .9
         2        100       2011 <-gap   .7
         3        100       2012         .7
         4        100       2017 <-gap   .6
         5        100       2018         .6

         6        200       2010         .4
         7        200       2012 <-gap   .3

I can visualize road condition over time like this (road #100):

However, as noted in red, there are time gaps between the inspections.

Inspections for road #100 are missing for these years:

2005
2006
2007
2008
2009
2010
2013
2014
2015
2016

The gaps exist because not all of the roads in the City are inspected every year. While I wouldn't consider the gaps to be errors in the data, I am concerned that when I visualize the data, the gaps paint a misleading picture. I say this, because, the time intervals between inspections are certainly not constant,
 yet when I look at the graph, I'm lead to believe that the time intervals are constant (which is not true).

To compensate for this quirk, I would like to fill in the gaps between inspections using a select query in a view. Using such a view, the data could be visualized like this:

+---------+---------+------------+-----------+
| INSP_ID | ROAD_ID | INSP_DATE  | CONDITION |
+---------+---------+------------+-----------+
|       1 |     100 | 2004-04-01 |        .9 |
|         |     100 | 2005-01-01 |        .9 |
|         |     100 | 2006-01-01 |        .9 |
|         |     100 | 2007-01-01 |        .9 |
|         |     100 | 2008-01-01 |        .9 |
|         |     100 | 2009-01-01 |        .9 |
|         |     100 | 2010-01-01 |        .9 |
|       2 |     100 | 2011-04-01 |        .7 |
|       3 |     100 | 2012-03-01 |        .7 |
|         |     100 | 2013-01-01 |        .7 |
|         |     100 | 2014-01-01 |        .7 |
|         |     100 | 2015-01-01 |        .7 |
|         |     100 | 2016-01-01 |        .7 |
|       4 |     100 | 2017-03-01 |        .6 |
|       5 |     100 | 2018-03-01 |        .6 |
+---------+---------+------------+-----------+
|       6 |     200 | 2010-06-01 |        .4 |
|         |     200 | 2011-01-01 |        .4 |
|       7 |     200 | 2012-06-01 |       .35 |
+---------+---------+------------+-----------+

As you can see, the time intervals are now constant and more intuitive to interpret.

The filler records would be populated like this:

insp_id would be null
road_id would be the id of the last completed inspection
insp_date would be January 1st of the applicable year
condition would be the condition of the last completed inspection

with
  date_range as (select min(insp_date) mind, max(insp_date) maxd from road_insp),
  insp_dates as (select add_months(trunc(mind, 'YYYY'), (level - 1)*12) as insp_date
   from date_range connect by level <= extract(year from maxd) - extract(year from mind))
select
   ri.insp_id, ri.road_id,
   nvl(case when extract(year from ri.insp_date) = extract(year from id.insp_date)
     then ri.insp_date else id.insp_date end, ri.insp_date) as insp_date,
   ri.condition
from
  insp_dates id right join
  (select insp_id, road_id, insp_date, condition as condition,
     lead(insp_date) over (partition by road_id order by insp_date) as next_insp_date
  from road_insp) ri
  on id.insp_date >= trunc(ri.insp_date, 'YYYY')
    and id.insp_date < trunc(ri.next_insp_date, 'YYYY')
order by insp_id, road_id, insp_date
;

Generate dates between min and max insp_date.

Find the next insp_date for each inspection to have them in 1 row.

Join the generated dates to the original dataset on date ranges based on year, excluding the upper bound, because that is the next inspection with existing data. Outer join, so the last inspection for a road (where next inspection is null) is included as well.

If there is generated insp_date is in the same year as the original data, use the original data, otherwise use the generated insp_date.

   INSP_ID    ROAD_ID INSP_DATE   CONDITION
---------- ---------- ---------- ----------
         1        100 2004-04-01         .9
         1        100 2005-01-01         .9
         1        100 2006-01-01         .9
         1        100 2007-01-01         .9
         1        100 2008-01-01         .9
         1        100 2009-01-01         .9
         1        100 2010-01-01         .9
         2        100 2011-04-01         .7
         3        100 2012-03-01         .7
         3        100 2013-01-01         .7
         3        100 2014-01-01         .7
         3        100 2015-01-01         .7
         3        100 2016-01-01         .7
         4        100 2017-03-01         .6
         5        100 2018-03-01         .6
         6        200 2010-06-01         .4
         6        200 2011-01-01         .4
         7        200 2012-06-01         .3


How to remove wildcard character from field I have fields that contain the character *. I've been trying to remove them but my commands fail with an error...

Example:

UPDATE name SET name = REGEXP_REPLACE (name, '*', '') WHERE category_id = 168;

I'm also struggling to remove other characters like |

Apparantly the quote should be \\:

UPDATE name SET name = REGEXP_REPLACE (name, '\\*', '');

Fiddle


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

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

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

Julian

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

In CREATE table you can use bigserial/serial type

CREATE TABLE xx (
 id  serial,
 data text
);

if you need to create sequence then
CREATE SEQUENCE serial START 1;

"Simple" way to import data from MSSQL is following
CREATE TABLE and replace IDENTITY with serial
Import all data. If you dont import id values, Postgresql replaces them with with new values. Example

INSERT into xx (data) VALUES ('test') ;

This creates new id for  row

 INSERT into xx (id, data) VALUES (1, 'test') ;

this one does not.
So import data with id and run

 select setval('xx_id_seq', max(id)) FROM xx

xx_id_seq is default naming convention where xx is table name id is column and seq id for sequence ( in my system pgadmin3 creates table with that logic)

If you want to automate whole process you need to write lot of code which reads INFORMATION_SCHEMA and does dynamic SQL , but i assume that it is not the question.

For more information see manual

sequences see : http://www.postgresql.org/docs/9.3/static/sql-createsequence.html

manipulating sequnces see: http://www.postgresql.org/docs/9.3/static/functions-sequence.html

datatypes see: http://www.postgresql.org/docs/9.3/static/datatype.html


Booking database design with a twist I'm wanting to find a way to how I can implement a booking system that lets users put a start date on the booking and generate the end date whenever.

Lets take a house renting example. Say I wanted to book a house from a particular starting date, but I'm not sure when I'll leave, the maximum stay for users who don't put a end date is 3 days, how could I do build my database so there aren't any booking overlaps?

The current structure I have is:

booking

booking_id      | integer | not null default nextval('booking_booking_id_seq'::regclass)
houseId | integer |
user_id         | integer | not null

booking_date

 booking_date_id | integer                     | not null default nextval('booking_date_booking_date_id_seq'::regclass)
 book_from       | timestamp without time zone | not null
 book_to         | timestamp without time zone | not null
 booking_id      | integer                     |

I'm wondering whether I store an extra field in the booking entity is_stayAndLeaveWhenever

and then in the booking_date I'd just store the book_to with the book_from + 3 days (max stay without choosing leave date)

Is that a solid solution?

the maximum stay for users who don't put a end date is 3 days,

Default end date in db = 3 days. Maybe marker "tentative". Issue being - if you do not know when they move out and can not have overlap, you MUST reserve the maximum stay.

So, your solution is as good as it gets.


What tools are there to generate test data for SQL Server? As you can see from another question of mine, generating test data is my theme right now.

At this point, I'm still generating my test data by hand.  However, this process always generates small amounts of data (usually five-ten rows) since it is a manual process.

Are there any tools to automate this process?  Particularly, I'd like to be able to generate 1mil+ rows.


Looking for suggestions to Migrate Amazon RDS Large Instance to non-amazon host - Live We have an Amazon RDS Large instance with a database of about 20GB that needs to be migrated without having to take our website down or minimize downtime to less than a couple of hours.

And I am looking for strategies in which this could work effectively? Our RDS has both innodb and myisam tables.  How possible is this to do while live without corrupting data and what kind of time do you think it would take to migrate a 21 Gb db? Would it be wise to migrate first to a ec2 server so we would have more capabilities? Can you create a read-only replication of the main RDS and clone that to the EC2 for more protection?
Thoughts?

Dump RDS and import it into mysql that holds all data in RAM disk:

Buy strongest possible EC2 instance that might hold your database in memory twice (you might prepare everything on a small instance and then ramp it up)
Create RAM disk, install and configure mysql 5.6 to hold both data and redolog on the RAM disk
Stop your db-using services, dump the RDS directly into the RAM-backed mysql
stop mysql, rsync the DB to a final place, kill the machine

You should do a dry run to estimate expected downtime.


Is wrapping only possible for package bodies? I am using oracle 11gR2 database in which I was trying to wrap my scripts. I read the documents and tried to wrap a sql script which contains a create table statement. I used below commands:

wrap iname=ORDER.sql oname=ORDER_TEST.sql
wrap iname=ORDER.sql oname=ORDER_TEST.wrp
wrap iname=ORDER.sql oname=ORDER_TEST.plb

But in all 3 format the files created have same codes visible, no encryption is happening. Also, I have tried same on a package body which get encrypted properly.
Now, I need to know does only package bodies has this facility of wrapping. Can I wrap other scripts like create table, trigger, view etc..?

PL/SQL Source Text Wrapping

  You can wrap the PL/SQL source text for any of these stored PL/SQL
  units, thereby preventing anyone from displaying that text with the
  static data dictionary views *_SOURCE:

  Package specification
  Package body
  Type specification
  Type body
  Function
  Procedure

A script containing a CREATE TABLE statement does not fall into any of the above categories.

Also, keep in mind, unwrapping such source takes seconds (for example: http://www.codecrete.net/unwrapit/). Oracle generously classified this method as low-assurance security (see above URL), but I would rather classify this as non-existent security.


does a subquery pull the entire table? i am attempting to run a subquery in a table:

SELECT t1.*, t2.* FROM t1
LEFT JOIN (SELECT * FROM table2 GROUP BY col1) AS t2 on t1.col1 = t2.col1
WHERE ...

My main question is, since that subquery called on it's own would pull the entire time, does it also pull the entire time while it's in a subquery, even though the WHERE condition on the parent query is only pulling just a few rows of the actual database?

I suppose I could put the where query inside the subquery, but its a pretty complex where query that involves other tables.

It shouldn't. It depends on what the system can simplify the query to.

You can demonstrate this very easily by considering the plan for queries such as:

SELECT *
FROM YourTable
WHERE ID < 5;

SELECT *
FROM (SELECT * FROM YourTable) as t
WHERE ID < 5;

The two should be identical.


Can I edit the batches received by SQL Server on the fly? I am supporting a third-party application which uses a SQL Server 2016 backend. We have no source code access, and the third-party support is slow to respond. Most of the time, I can index and magic my way out of the sometimes terrible queries that it throws at the database, but there have been multiple occasions where there is quite literally nothing I can do (within constraints of time,  money, and storage).  The query is just wrong.

Is there a way to administratively substitute the queries submitted by the application for other ones?  I am imagining some sort of regex match performed against the batch if the application name matches.

Example:

-- Submitted by application
update Table set IsBilled = 0 where ID = '{EFD5F13C-6123-4913-B530-8941FC71B969}'

-- What I want the query to be (to avoid spurious updates to Table)
-- (Table is large, and the application is submitting this query on every page view)
update Table set IsBilled = 0 where ID = '{EFD5F13C-6123-4913-B530-8941FC71B969}' and IsBilled = 1

-- Proposed regex
-- Match
update Table set IsBilled = 0 where ID = '(\{[A-F0-9\-]+\})'
-- Replace
update Table set IsBilled = 0 where ID = '$1' and IsBilled = 1

Some of my conceived uses would be to avoid useless updates, others would be in rephrasing where clauses to maintain sargability.  I am sure there are other scenarios that would come to mind.

Is there a way to administratively substitute the queries submitted by the application for other ones?

No, the closest you will get with out of the box functionality is what a plan guide can do.

  I am imagining some sort of regex match performed against the batch if the application name matches.

Nothing native inside of SQL Server can do this, currently.

  Can I edit the batches received by SQL Server on the fly?

Unfortunately (or fortunately) there is nothing built in to do this. I don't want to give you any ideas but you'd need to have a middle layer between the application and the database server. This intercepting application would need to understand TDS (which is the protocol used with SQL Server) and allow you make changes to (or, again, not) the TDS packets to change the queries on the fly.

If you made this, you should sell it :)


Running query against SQL Server with FreeTDS's bsqldb returns blank output Here is the query I ran

select CAST(PageGuid as varchar(max)) + ',' + CAST(TenantGuid as varchar(max)) + '/' + CAST(OriginalFileGuid as varchar(max))
from Pages

This worked with tsql but when run with bsqldb I get empty lines instead of results.

I confirmed that bsqldb works by running other simple queries. So why is this one not working?

Here is the verbose output

bsqldb:163: Verbose operation enabled
bsqldb:212: Query:
    select CAST(PageGuid as varchar(max)) + ',' + CAST(TenantGuid as  varchar(max)) + '/' + CAST(OriginalFileGuid as varchar(max)) from Pages
bsqldb:186: dbsqlsend(): OK
bsqldb:194: dbsqlok(): OK
bsqldb:289: calling dbresults: OK
Result set 1
Freeing prior allocations
Allocating buffers
Allocating compute buffers
Metadata
col     name                            source                          type             size    varies
------  ------------------------------  ------------------------------  ---------------  ------  ------
     1                                                                             text   64512       1

Data

Retrieving return status... none
11084 rows affected
Retrieving output parameters... none
bsqldb:615: dbresults() returned NO_MORE_RESULTS (2):
bsqldb:212: Query:

I'm using FreeTDS 0.64RC1, FreeBSD 8, and SQL Server 2008

I suspect the one of the values is NULL, so the whole expression concatenation is NULL.

You'd need this:

CAST(ISNULL(PageGuid, '') as varchar(max)) + ',' +
CAST(ISNULL(TenantGuid, '') as varchar(max)) + '/' +
CAST(ISNULL(OriginalFileGuid, '') as varchar(max))


Disable Saving/Exporting Results of TSQL Query in SSMS? I really don't want to go in to too much detail as to WHY I want to do this. Just know that it would be ideal for my use case.

Is there a way to disable a specific database role from using the Copy, Copy with Headers, Save Results As..., Print... functionality in the results window in SSMS?

No, there is no way to handle this from the database/instance, unless you just deny them access to the database at all.

SSMS is a client-side tool, and the things that it can do with the data you've already let it consume are outside of server-side controls. Even if you could take those menu items away, there is always the PrintScreen button.


(MySQL) Need an "in-place" REPLACE for adjusting historical data I have a table with around 6,000,000 records.  It is backed up every few hours.  Recently a script ran that cascade updated about 2,000,000 of the older records to bad values.  I have extracted those 2,000,000 records from the backup and have them ready for re-insert, in the form of  "INSERT INTO table (col, col) values (val, val)"

Now, I need a mechanism to insert those 2,000,000 records, replacing the bad data with the historical data, while maintaining existing IDs (meaning REPLACE is not an option -- as it deletes on duplicate and increments the ID).  Also, I need to do this without interrupting server access for too long (which means DROP TABLE, then --force trickery is not an option).

I'm sure I'm missing something, but I can't figure out for the life of me how to replace in-place within the current parameters.  Thoughts?

Using the INSERT (SELECT)...ON DUPLICATE KEY UPDATE should permit you to visit these rows and update them with original values. Also you're not correct about REPLACE. This is the mechanism used by pt-table-sync to correct data drift. The documentation details a REPLACE == DELETE AND INSERT but that's not done within the scope of AUTO_INC.

mysql> create table replace_test (id int auto_increment primary key, c1 varchar(5)) engine=innodb;

mysql> insert into replace_test(c1) values ('aaaaa'),('bbbbb'),('ccccc');

mysql> select * from replace_test;
+----+-------+
| id | c1    |
+----+-------+
|  1 | aaaaa |
|  2 | bbbbb |
|  3 | ccccc |
+----+-------+
3 rows in set (0.00 sec)

mysql> replace into replace_test (id,c1) values (2,'ddddd');
Query OK, 2 rows affected (0.00 sec)

mysql> select * from replace_test;
+----+-------+
| id | c1    |
+----+-------+
|  1 | aaaaa |
|  2 | ddddd |
|  3 | ccccc |
+----+-------+
3 rows in set (0.00 sec)

I think you expected to see something like below but that is not the behaviour.

mysql> select * from replace_test;
+----+-------+
| id | c1    |
+----+-------+
|  1 | aaaaa |
|  3 | ccccc |
|  4 | ddddd |
+----+-------+
3 rows in set (0.00 sec)

But that does not happen as you can see from above.


How to efficiently design Firebase database for Home service Booking application? I'm planning to build my own app. It's a home service App to link users with other providers.
I'm recently used a NoSQL database, Firebase, and I am stuck with the design of the database for my app. The question is how to structure the databases in this way with NoSQL, like a JSON tree?

Like

{
"uID":"01",
"username":"John",
"Booking":[{
       "Bid":"01",
       "BDetails":"Cleaner Rooms",
       "Baddress":"America",
        "providerID":"023",
        "timestamp":"1-Mar-2019"
       ....
       ....
      }]
}

Is that right?

your database should look something like this object:

{
  users : { uid, ... },
  bookings: { id, uid, ... },
}

And you access each user's bookings by querying with a condition that the column uid matches the user's id, by using firebase.auth().currentUser if you're using firebase. btw you should Cloud Firestore for better scalability and faster queries.


what is causing my windows page file to grow? I have one of my production servers that has a windows page file of over 32 GB.

The server in question has 32 GB of RAM, windows server 2008.

It is exclusively used for Sql Server. Nothing else.

The lock pages in memory setting has not been set up for any of the sql server services accounts and I think this might be related to the big size of the page file.

I am already seeing if I can add more memory to this box, but I would like to find out what is causing memory pressure (if anything inside sql server).

what could be some options as how I can do that?

I kind of like, in this case, to monitor Paging File % Usage and if something goes off threshold, I would collect all that is running on sql server at that time. Hopefully the culprits would be all there.

any ideas?

select @@Version

  Microsoft SQL Server 2005 - 9.00.5057.00 (X64)   Mar 25 2011 13:33:31
  Copyright (c) 1988-2005 Microsoft Corporation  Enterprise Edition
  (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1)

dbcc memorystatus

Understanding pagefile.sys % Usage performance monitor (perfmon) counter correctly

what is causing my windows page file to grow?

That's a good question. If you want to know what applications are causing this in a physical environment and even to a good extent a virtual environment then the following performance counters can be used:

Process()\Page File Bytes
Process()\Page File Bytes Peak

Additionally I'd capture:

Paging File\%Usage
Paging File\%Usage Peak

In a virtual environment the memory pressure is normally done within the guest os by a balloon driver which artificially inflates to grab back memory for other uses. This generally won't show as using paged memory in the file.

  The lock pages in memory setting has not been set up for any of the sql server services accounts and I think this might be related to the big size of the page file.

I get what you're saying, but it shouldn't have a bearing on the size of the page file. The system settings for page file have the bearing on the size of the page file. It may be just set to 32GB... who knows. Check out why it's set that way in the system properties.

  I am already seeing if I can add more memory to this box, but I would like to find out what is causing memory pressure (if anything inside sql server).

If your min/max memory settings are set properly then SQL Server shouldn't be the main cause of memory pressure according to Windows, especially since you said it's dedicated with nothing else running. It could be internal memory pressure, where clock hands ae sweeping caches (inside or outside hands) and that would be due to workload/amount of memory available. That should be investigated.

How do you know there is pressure? Could you update your post with what you've found?


Why is a BIGINT index larger than a CHAR 13 index? I have a MySQL DB, with a MyISAM table with 5 million records, and several fields. 1 of the fields is a BIG INT, which I understand to be 8 bytes, and another is a CHAR 13, which I thought was a byte per char.

I notice that when adding a index to each field though, the index file increases by 51MB on the CHAR 13, and by 81MB on the BIGINT.

Based on byte site of each field I expected the BIG INT to be noticeably smaller, but in fact the difference is opposite and bigger than I expected.

Why is that?

CREATE TABLE `msisdns_key` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `key` mediumint(6) unsigned NOT NULL DEFAULT '0',
  `key2` smallint(5) unsigned NOT NULL DEFAULT '0',
  `msisdn_big` bigint(20) unsigned NOT NULL,
  `msisdn_bit` binary(20) NOT NULL,
  `msisdn` char(13) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `key` (`key`),
  KEY `key2` (`key2`),
  KEY `msisdn_bit` (`msisdn_bit`(3)),
  KEY `msisdn` (`msisdn`),
  KEY `msisdn_big` (`msisdn_big`)
) ENGINE=MyISAM AUTO_INCREMENT=6745305 DEFAULT CHARSET=latin1

Benchmarks tend to be flawed.  Here are some questions that affect the outcome of your test:

How did you measure the sizes?
What are the values in the column?  In particular, are they in the same order as the order of insertion?
Did you have the index in place before populating the table?  Or did you ALTER TABLE .. ADD INDEX .. later?
Have you done any DELETEs or UPDATEs?  These tend to mess with BTrees.
Are there trailing spaces in your CHAR values?  (There are some situations where CHAR removes them -- I don't know about this situation.)
Are all the CHAR values the same?  Or consecutive values have the same first several characters?  (There may be optimizations.)

All of those questions have a bearing on the size of a MyISAM index.  (The list for InnoDB is similar.)

To answer the question...

A MyISAM index is structured as a BTree in 1KB blocks.  Each "record" contains:

Data (8 bytes for BIGINT or 13 bytes for CHAR(13) latin1)
5 (by default) byte pointer to the row in the .MYD
Some overhead?

Plus there is some overhead for the BTree.

If the rows for a BTree are inserted in order, each BTree block (except for the last) will be "full".
At the other extreme, the rows will be half full due to block splits due to inserting rows at just the wrong time.
On average, after random inserts and/or deletes, a BTree settles to about 69% full.  (UPDATEs don't matter in your case, since the records seem to be FIXED.)

Now for some math.

BIGINT: 5M rows * (8+5) bytes/row = 65MB.  This could easily grow to 81MB with overhead, 69%, etc.

CHAR: 5M * (13+5) = 90MB.  So this must be incorrect.

CHAR: 5M * (2+5) = 35MB.  This could inflate to 51MB.  Suppose you inserted 'x' and MyISAM turned it into VARCHAR(13).

CHAR, working backwards, assuming random insertions:  51MB * 69% = 35MB.  Divide by 5M rows.  That leaves 2 bytes per CHAR.  If all are the same, or all in a block have the same prefix, then it fits.

CHAR, working backwards, assuming ordered insertion:  51MB/5MB = 10 bytes per row.  5 for pointer leaves 5 for CHAR.  Still possible.

Comments on benchmarking with CHAR(13):

There are very few cases where CHAR is preferred over VARCHAR.
The database world is moving toward utf8mb4, not latin1.
It is unrealistic to put x in all rows.
If you want to test with effectively random, fixed length, latin1 values:  LEFT(MD5(id), 13)

One more note:  There are two ways that MyISAM might [re]build an index:

Via key_buffer -- this is likely to leave the index at 69% full.
Via sorting -- this is likely to leave it almost 100% full.

A quick experiment:  ALTER TABLE msisdns_key ENGINE=MyISAM;  Watch SHOW PROCESSLIST; to see which repair method is used.  See if some of the indexes shrink.

MyISAM is going away; don't spend too much time on it.

ALTER TABLE .. ADD INDEX .. happens in one of these ways:

Copy over the table and rebuild all the indexes, or
Add the new index, not effecting anything else, and not return until finished, or
Add the new index in the background.  The point here is that ls -l may not be updated yet when you look at it.

FIXED is almost never better than DYNAMIC, even with MyISAM.  The places where it might help:

Delete a short record, then insert a longer record.
Update a record, increasing the size of some column(s).

If you have a lot of those and the 'fixed' size is not much bigger than the 'dynamic' size, then maybe fixed is better.

Dynamic is usually better when the average row size is smaller.  This is because Smaller --> More cacheable --> Faster.

About the only case for MyISAM is that the disk footprint is smaller than InnoDB.  In virtually any other contest, InnoDB meets or exceeds MyISAM.

My 10% Rule of Thumb.  When trying to optimize something (usually speed or space), I guesstimate how much a proposed change will help.  If less than 10%, I move on in search of 'bigger fish'.

Is it big?.  5M records; 150MB data + index.  This is not terribly big.  I would rank it at about 80th percentile as MySQL tables go.  It should be easily cached in RAM, even if implemented in InnoDB.  If it will be several GB, let's look at specific schemas, not "oh, by the way the BIGINT is for phone numbers".

Smallest phone.  A 12 digit number can be crammed (without length info) into BINARY(5).  But, I suggest that the messiness in the code makes it not worth the effort.  (Perhaps my 10% rule kicks in?)


Generate a new Sub Plan/Sequence ID in SQL 2005/8 Business Intelligence Development Studio I have what is effectively a template SSIS package (well a maintenance plan actually) that is copied and used as the basis of new maintenance plans.
In this package there is a Sequence (or a sub-plan) containing a number of tasks.
I know I can change the package's ID in BIDS, i.e.:

However I can't change the ID of the sub-plan sequence, the ID is greyed out:

Neither BIDS for SQL Server 2005 or 2008(R2) allow me to alter this value.
Is there a way to achieve this?
Rationale:
To give you some background, these packages are automatically installed on a server using dtutil. A script is then run to create a SQL Agent job for the package. Part of that script executes a MSDB system stored procedure called msdb.dbo.sysmaintplan_subplans. This stored procedure is used to join up the dots between a job, a plan and a sub plan.
For example:
EXEC @ReturnCode = msdb.dbo.sp_maintplan_update_subplan
     @subplan_id = @thesubplanid,
     @plan_id = @theplanid,
     @name= @subplanname,
     @description = 'Created by Provisioner',
     @job_id = @jobid,
     @schedule_id = @jobscheduleid_out,
     @allow_create = 1

@plan_id is the package ID and @subplan_id is the sequence or sub-plan ID as shown in the screenshots above.
sysmaintplan_subplans adds a record to the table msdb.dbo.sysmaintplan_subplan, however the primary key is the subplan_id. This is why I need to be able to change the ID of the sub-plan in BIDS.

Look at BIDSHelper. Fantastic, free tool everyone developing SSIS and probably SSAS should be using.

Reset GUIDs

"This feature resets the IDs for all tasks, connection managers, configurations, event handlers, variables, and the package ID itself. When you copy a package, objects in both packages end up with the same IDs. This Reset GUIDs feature ensures that the IDs in the current package are unique."


EXPLAIN EXECUTE completes quickly, but is slow without EXPLAIN When I run the following query in pgAdmin3, it runs in like 74ms, and I can see the execution plan but no rows are returned. I guess that's the behavior of using EXPLAIN?

Anyway, when I remove 'EXPLAIN' in that query, it then is taking 3 minutes to run, anyone have a clue what the problem is?

PREPARE statement66 AS
select
    c.car as carId,
    c.date as manufactured_date
from v_car c
where
    c.carId > -1
    and
    (
        c.carId = 5555
        or c.truckId = 5555
        or c.motorcycleId = 5555
    )
order by c.date desc
limit 100;

EXPLAIN EXECUTE statement66;

When you run EXPLAIN, it only returns to you the plan that the database has created for running the query.  When you remove EXPLAIN, then you are asking the database to actually perform the query.

What this implies is that, essentially, your query is going to take 3 minutes.  Okay, so it's more complicated than that...

Try EXPLAIN (ANALYZE,BUFFERS)

By running a query with EXPLAIN (ANALYZE,BUFFERS) at the beginning, you are asking the database to actually perform the full query, but rather than returning results to you, return a report about the actual query performance.

Here's a few things to keep in mind:  the first time you run a query with EXPLAIN (ANALYZE,BUFFERS), it probably has to access data and indexes which are on disk, and so this will slow down the query for sure.  However, try running the same query a second time, and most often all of the data you need to perform the query is residing in the shared buffers. Then, you'll see the query typically finishes much more quickly on subsequent runs.

In the end, EXPLAIN (ANALYZE,BUFFERS) is a good tool for hunting down how long your query is actually going to take, and if your query plan is any good.

I personally don't really use prepared statements, so maybe someone else will chime in about these.  I know that it may have an effect on the quality of the query planner results.


MySQL - two tables with relation one to many - retrieve "n" fields from table1 WHERE SUM of a field on table2 is less/equal/greater than "value" Tables structure:

CREATE TABLE IF NOT EXISTS `A` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`num` int(6) NOT NULL,
`docnr` int(6) NOT NULL,
`docy` varchar(3) NOT NULL,
`docd` date NOT NULL,
`ent` int(3) NOT NULL,
`paym` tinyint(1) NOT NULL,
`cli` int(6) NOT NULL,
`rags` varchar(40) NOT NULL,
`addr` varchar(30) NOT NULL,
`cap` varchar(5) NOT NULL,
`city` varchar(30) NOT NULL,
`pro` varchar(2) NOT NULL,
`naz` varchar(3) NOT NULL,
`telp` varchar(6) NOT NULL,
`teln` int(13) NOT NULL,
`rif` text NOT NULL,
`totd` decimal(12,2) NOT NULL DEFAULT '0.00',
`toti` decimal(12,2) NOT NULL DEFAULT '0.00',
`deleted` tinyint(1) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1;

CREATE TABLE IF NOT EXISTS `B` (
`id` int(11) NOT NULL,
`num` int(6) NOT NULL,
`seq` int(4) NOT NULL,
`typ` varchar(10) NOT NULL,
`art` int(6) NOT NULL,
`art_x` varchar(17) NOT NULL,
`cod` int(3) NOT NULL,
`descr` text NOT NULL,
`qty` decimal(11,4) NOT NULL,
`um` varchar(2) NOT NULL,
`val` decimal(12,2) NOT NULL,
`sc1` decimal(4,1) NOT NULL,
`sc2` decimal(4,1) NOT NULL,
`sc3` decimal(4,1) NOT NULL,
`iv` int(3) NOT NULL,
`tot` decimal(12,2) NOT NULL DEFAULT '0.00',
`toti` decimal(12,2) NOT NULL DEFAULT '0.00',
`deleted` tinyint(1) NOT NULL DEFAULT '0',
KEY `idseq` (`id`,`seq`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

I have two tables, A and B;
the relation between them is one to many (Aone->Bmany) given by field "id";
I need to retrieve "n" fields from table A WHERE the SUM of the field B.toti on table B is less/equal/greater than an "x" given value;

Example:

table A
id | num | docnr | docy |
1  | 100 | 563   | abc  |
2  | 50  | 9978  | abc  |
3  | 150 | 56    | abc  |
4  | 150 | 679   | abc  |
5  | 150 | 67    | def  |
6  | 150 | 12    | def  |

table B
id | seq | toti
1  | 1   | 10
2  | 2   | 15
2  | 3   | 5
4  | 4   | 10
3  | 5   | 25
2  | 6   | 567
3  | 7   | 2354
4  | 8   | 768
1  | 88  | 456
1  | 33  | 30
1  | 35  | 89
4  | 31  | 4543
5  | 77  | 223
5  | 9   | 34
6  | 13  | 156
6  | 63  | 6758
5  | 93  | 6
6  | 53  | 22

So:

SUM of all B.toti with id=1 -> 585
SUM of all B.toti with id=2 -> 587
SUM of all B.toti with id=3 -> 2379
SUM of all B.toti with id=4 -> 5321
SUM of all B.toti with id=5 -> 263
etc..

I want to SELECT A.docnr, A.docy, A.paym, A.num WHERE the result of the sum above is less/equal/greater than "value" (passed to the query as STRING);

Using the sums above:
if the sum of all B.toti fields for that 'id' is greater than, let's say, '585' then retrieve 'n' fields on table A for the matching 'id', in this case retrieve the row on A for the 'id' 2,3,4;

How can I achieve this?
I already tried my own solution but it was a complete mess and soo slow!!
Consider that later I may need to retrieve some data from a couple of other tables (with a join), I prefer to specify this in the case it could be relevant for the solution;

Can't figure out a good or acceptable solution, please help me.
Cris

This is a rather simple GROUP BY / HAVING query in table B. You then only need to join table A to get the columns you want in the result:

SELECT a.*,                   -- pick the wanted columns from A
       bg.sum_toti             -- you can have this in the results, too,
                              -- if you want
FROM a
  JOIN
  ( SELECT id, SUM(toti) AS sum_toti
    FROM b
    GROYP BY id
    HAVING SUM(toti) >= ?              -- ? is the parameter, eg. 585
  ) AS bg
  ON bg.id = a.id ;

You already have an index on a (id). An index on b (id, toti) would help with efficiency.
I suggest you ditch MyISAM and use only InnoDB engine in your databases. It's the default engine in recent versions (5.6+) and gets far more development. MyISAM is pretty much a relic and offers no advantages (it used to have some, years ago), only disdvantages.


Passing NULL to SSIS variables I'm building a package to execute a procedure with parameters from a configuration table.

I've built two Execute SQL Task boxes. One populates SSIS variables from the configuration table. The second executes the procedure with those variables.

I'm facing the following behaviour. Integer variables have a 0 as a default value and strings an empty string. Even after getting NULL from the configuration table those variables keep their values.

How can I change this behavior to apply SQL Server logic of NULL as a default "value", or just to enable NULL that they get from configuration table to override their default value?

SSIS doesn't support nullable types for it's built in variables.  So - as you've experienced - a null integer in SQL will become zero when assigned as an SSIS variable.  Those are the breaks, but there ARE workarounds.

In order to keep our source table NULLs, we will need to skip over the whole  "assigning them to SSIS variables" completely.  Have a look at the OLE DB Command.  It's similar to the Execute SQL command, except it operates on the dataflow level.  It operates one SQL Command per row running through it. The advantage of this is that you never have to map a SQL result set to SSIS variables.  Working inside a dataflow is SQL out and SQL in.  We can avoid the whole "non nullable" headache altogether by passing a SELECT TOP 1 * FROM MyConfigTable into an OLE DB Command.  Inside the OLE DB Command compoenent  you can assign the ResultSet columns to SP parameters.

I know it's a change, but I do think that moving away from two Execute SQL Tasks in succession and into a Dataflow containing an OLE DB Command component is what you want.  Anything else would involve a script task.  Good luck!


Enabling TLSv1 in postgres12 / openssl1.1.1f After upgrading a server to postgres12 / OpenSSL 1.1.1f (Ubuntu 20.04) I got tlsv1 alert protocol version error messages from a client using an old openssl 1.0.1 library supporting only tlsv1.
I thought setting  ssl_min_protocol_version = 'TLSv1' in postgresql.conf should enable the protocol allowing that client to connect. However it does not. I used nmap to check for supported protocols and found only one section with the TLSv1.2 ciphers:
➜  ~ nmap --script ssl-enum-ciphers -p 5432 127.0.0.1
Starting Nmap 7.80 ( https://nmap.org ) at 2020-09-23 22:28 CEST
Nmap scan report for localhost (127.0.0.1)
Host is up (0.000064s latency).

PORT     STATE SERVICE
5432/tcp open  postgresql
| ssl-enum-ciphers:
|   TLSv1.2:
|     ciphers:
|       TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (secp256r1) - A
|       TLS_DHE_RSA_WITH_AES_256_GCM_SHA384 (dh 2048) - A
|       TLS_ECDHE_RSA_WITH_CHACHA20_POLY1305_SHA256 (secp256r1) - A
|       TLS_DHE_RSA_WITH_CHACHA20_POLY1305_SHA256 (dh 2048) - A
<snip>

I used this SE answer Ubuntu 20.04 - how to set lower SSL security level? to change the system default MinProtocol setting in openssl.cnf to TLSv1 and postgres picked up this setting (also the openssl1.0.1 client could connect with this setting):
➜  ~ sudo service postgresql restart
➜  ~ nmap --script ssl-enum-ciphers -p 5432 127.0.0.1
Starting Nmap 7.80 ( https://nmap.org ) at 2020-09-23 22:31 CEST
Nmap scan report for localhost (127.0.0.1)
Host is up (0.000062s latency).

PORT     STATE SERVICE
5432/tcp open  postgresql
| ssl-enum-ciphers:
|   TLSv1.0:
|     ciphers:
|       TLS_ECDHE_RSA_WITH_AES_256_CBC_SHA (secp256r1) - A
|       TLS_DHE_RSA_WITH_AES_256_CBC_SHA (dh 2048) - A
|       TLS_DHE_RSA_WITH_CAMELLIA_256_CBC_SHA (dh 2048) - A
|       TLS_ECDHE_RSA_WITH_AES_128_CBC_SHA (secp256r1) - A
<snip>
|   TLSv1.1:
|     ciphers:
|       TLS_ECDHE_RSA_WITH_AES_256_CBC_SHA (secp256r1) - A
|       TLS_DHE_RSA_WITH_AES_256_CBC_SHA (dh 2048) - A
|       TLS_DHE_RSA_WITH_CAMELLIA_256_CBC_SHA (dh 2048) - A
|       TLS_ECDHE_RSA_WITH_AES_128_CBC_SHA (secp256r1) - A
<snip>
|   TLSv1.2:
|     ciphers:
|       TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (secp256r1) - A
|       TLS_DHE_RSA_WITH_AES_256_GCM_SHA384 (dh 2048) - A
|       TLS_ECDHE_RSA_WITH_CHACHA20_POLY1305_SHA256 (secp256r1) - A
|       TLS_DHE_RSA_WITH_CHACHA20_POLY1305_SHA256 (dh 2048) - A
<snip>

For curiosity I changed ssl_min_protocol_version = 'TLSv1.2' in postgresql.conf to see if the postgres server uses the intersection of the two configured ranges and after some debugging help from Laurenz i found that this setting was applied.
My question remained:
How can I configure my system so that postgres will accept TLSv1 through TLSv1.2 connections while keeping the openssl default MinProtocolsetting of TLSv1.2?

As Laurenz Albe pointed out, it is not possible to configure postgres to use a protocol version older than the MinProtocol specified in openssl.cnf.
It is however possible to make postgres use its own version of openssl.cfg. This way postgres can use TLSv1 without affecting the system default.
To achieve this I added the following line to the environment file in pg's config directory:
OPENSSL_CONF = '/etc/postgresql/12/main/openssl.cnf'

I then copied /etc/ssl/openssl.cnf to this directory and made the changes there i.e.:
# beginning of openssl.cnf
openssl_conf = default_conf

# end of openssl.cnf
[ default_conf ]

ssl_conf = ssl_sect

[ssl_sect]

system_default = system_default_sect

[system_default_sect]
MinProtocol = TLSv1
CipherString = DEFAULT:@SECLEVEL=1

Now ssl_min_protocol_version = 'TLSv1' in postgresql.conf works and the rest of the system still uses the openssl default of TLSv1.2


SQL 2005- stored procedure performance troubleshooting I am investigating performance issues for a stored procedure on SQL 2005 server.
This is how I intend to do it:

Analyze the stored procedure for any bad TSQL practices (like count * etc)
Run it through DTA
Study the stored procedure's estimated query plan
Find the cached plan by running this code in the database:

SELECT  deqp.dbid ,
    deqp.objectid ,
    deqp.encrypted ,
    deqp.query_plan
FROM sys.dm_exec_query_stats deqs CROSS APPLY sys.dm_exec_query_plan(deqs.plan_handle) AS deqp
WHERE objectid = OBJECT_ID('procedurename', 'p') ;

Find the related indexes (not sure how) and check the usage
Try to find any missing indexes

How else should I do it?
Comments are really appreciated.

Thanks in advance

Things like select count(*) are just fine as long as the where clause is indexed correctly.

DTA may or may not be of any use.

The actual and estimated plan are what you need to look at.  It'll tell you if there are any indexes which are missing.  The indexes which are used will be in the execution plan.


In MySQL, should I add an index even if the query that scans the table is only ran once a month? Concretely, is it worth it to add an index to a query that scans the table of a mysql database,
even if that index would only be used once a month?

For example, having a users a table and then querying for all the users of a certain type.

EXPLAIN SELECT type FROM users WHERE type in (1,3);

+----+-------------+------------------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table            | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+-------------+------------------+------+---------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | users            | ALL  | NULL          | NULL | NULL    | NULL | 1000685 | Using where |
+----+-------------+------------------+------+---------------+------+---------+------+--------+-------------+

An IN clause breaks down to an OR

SELECT type FROM users WHERE type = 1 OR type = 3;

OR is typically (but not always) non-SARGable. That means an index won't be used too seek data but will be scanned. This is the same (roughly) as scanning the table anyway: same number of rows.

So given there is minimal benefit, I wouldn't bother in most cases.

If you have millions of rows and the query is blocking other processes, then you can do this if you have an index. No you have 2 seeks.

SELECT type FROM users WHERE type = 1
UNION ALL
SELECT type FROM users WHERE type = 3;

Summary: not worth it unless you have a big table and blocking issues.


Make another column in a table equal to the primary key column In my notes table (MySQL database) I have id as the primary key. I want the column originalid to be the value of the primary key. Here is the query I run after creating a record which I thought would work.

UPDATE notes SET originalid = (SELECT MAX(id) FROM notes) where id = (SELECT MAX(id) FROM notes);

But I get the error

  You can't specify target table 'notes' for update in FROM clause

Any advice or solutions on how I can work around this?

I explained the error message in an earlier post 4.5 years ago (Problem with MySQL subquery)
SUGGESITON : Do it as two queries
SELECT id INTO @maxid FROM notes ORDER BY id DESC LIMIT 1;
UPDATE notes SET originalid = @maxid where id = @maxid;

or
SELECT MAX(id) INTO @maxid FROM notes;
UPDATE notes SET originalid = @maxid where id = @maxid;


Building an bridge table from existing one with the use of the SELECT INTO I have one big source table in which attributes are mixed with measures together. I want to make a bridge table from them to allow analysis of the data.

Problem (in my case N is actually 22, id_column reference same table)

|id | id_column1 | id_column2 | id_columnN |  stuff |
+---+------------+------------+------------+--------|
| 1 |     2017   |    2024    |    1452    |   red  |
| 2 |     4125   |    NULL    |    5234    |   blue |
| 3 |     NULL   |    5234    |    7234    |   pink |

I want to build only one bridge table from the above, but I don't want to extract all columns in that manner, my goal is to have someting like:

|idT1| idT2 |
+----+------+
| 1  | 2017 |
| 1  | 2024 |
| 1  | 1452 |
| 2  | 4125 |
| 2  | NULL |
| 2  | 5234 |

and so on. I want to build such bridge table only from the data that I have in the problem above, and then reference idT1 and idT2 with adequate tables.

My ideas of:
- using pivot table did fail since I need to map all of the id values.
- i was thinking about using some cursor to map all of the result into one variable bounded with the id value, but also it did not work for me.

Thanks in advance for your time and help!

Instead of use a CURSOR I'd suggest you to use an iterative solution using WHILE.

CREATE TABLE MEASURES (id int, id_column1 int, id_column2 int, id_column3 int, stuff varchar(20));
INSERT INTO MEASURES VALUES
(1, 2017, 2024, 1452, 'red'),
(2, 4125, NULL, 5234, 'blue'),
(3, NULL, 5234, 7234, 'pink');
GO

CREATE TABLE BRIDGE (id int, idc int);
GO

DECLARE @idColumn INT = 1;
DECLARE @cmd VARCHAR(MAX) = '';

WHILE @idColumn <= 3
BEGIN
    SET @CMD =   'INSERT INTO BRIDGE'
               + ' SELECT id, id_column'
               + CAST(@idColumn AS VARCHAR(10))
               + ' FROM MEASURES'
               -- uncomment to remove NULL values
               --+ ' WHERE id_column'
               --+ + CAST(@idColumn AS VARCHAR(10))
               --+ ' IS NOT NULL;';

    EXEC (@CMD);

    SET @idColumn = @idColumn + 1;

    -- uncomment to check SQL statements
    --SELECT @CMD;
END

SELECT * FROM BRIDGE ORDER BY id;
GO

id |  idc
-: | ---:
 1 | 2017
 1 | 2024
 1 | 1452
 2 | 5234
 2 | null
 2 | 4125
 3 | null
 3 | 5234
 3 | 7234

dbfiddle here


ERROR 1813 (HY000) at line x: Tablespace '`mydb`.`mytable`' exists I'm starting off with a fresh mysql-server installation and am trying to import data into it. I do CREATE DATABASE mydb; and then do the following:

mysql mydb < mydb_schema.sql

mydb_schema.sql was generated by doing mysqldump mydb --no-data > mydb_schema.sql.

The problem is that mysql mydb < mydb_schema.sql is stopping midway through with the following error:

ERROR 1813 (HY000) at line x: Tablespace '`mydb`.`mytable`' exists.

Here's what's in the mydb_schema.sql file:

DROP TABLE IF EXISTS `mytable`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `mytable` (
  ...
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Those are the only two times mytable is referenced in that file. Further, the word "TABLESPACE" does not occur in that file.

https://stackoverflow.com/a/21692066/569976 doesn't really seem applicable since this is a from scratch import idk.

Any ideas?

So apparently there were two copies of mytable in my DB dump. There was mytable (all lowercase) and MyTable (camel case). On Linux that's not an issue but I guess on Mac OS X it is an issue.


Microsoft SQL Server 2008+ TIMESTAMP problem I'm currently facing a Problem, where the SQL Server refuses my Timestamps.
I am accessing the Server via EclipseLink (Java) but this shall not be the problem, as i can test the queries on the Server and receive the same results.

i created a small sample to reproduce this problem :

CREATE TABLE testDateTimes (
    id int not null,
    ts datetime not null default CURRENT_TIMESTAMP
)

insert values (one value generated by the server)

insert into testDateTimes (id) VALUES (1)

querying the values from the datetime

select * from testDateTimes;

returns me:

1   2017-05-24 10:45:17.267

now trying to manually insert a new datetime:

insert into testDateTimes (id, ts) VALUES (2, '2017-05-24 12:34:56.789')

which fails with error message: The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

i also noticed that this would be valid too:

insert into testDateTimes (id, ts) VALUES (2, '20170524 12:34:56.789')

but i can't change the way/format how EclipseLink sends the Data.

if i transform the datetime to another format:

insert into testDateTimes (id, ts) VALUES (2, '24.05.2017 12:34:56.789')

it is working...

NOTE: i am sure there are no errors in the DateTime itself (this is verified by Java)
How can i specify to take the correct format? standard and unique SQL/ISO date format instead of the one with the Dots.?
Note that i cannot do this in every query. It should be a default setting from the Server.

(i might be wrong, but this problem seems to pop up after moving from MSSQL2000 to MSSQL2008)

when i execute

set language 'croatian'

sys.syslanguages says croatian incldues dateformat ymd, the queries work as expected.

also
    SET DATEFORMAT ymd;

does solve the problem, but it is not a permanent solution.

update sys.syslanguages
set dateformat = 'ymd'
where name = @@LANGUAGE

would solve the problem, BUT this is prevented by the server!

ad hoc updates to system catalogs are not allowed

You cannot change default dateformat for all users without changing your language Which you do not want to do in your case.

You can create a new user with a default language which supports the dateformat you need will do the trick in this case. Then your application will need to use that login/user for connecting when doing the insert.

For example my test server and my login is set to following.

SELECT NAME,
       alias,
       dateformat
FROM   syslanguages
WHERE  langid = (SELECT value
                 FROM   master..sysconfigures
                 WHERE  comment = 'default language')

  name=us_english

  alias=English

  dateformat=mdy

With your example I get the same error if I try this.

INSERT INTO testDateTimes (id, ts) VALUES (2, '13.05.2017 12:34:56.789')

  Msg 242, Level 16, State 3, Line 15 The conversion of a varchar data
  type to a datetime data type resulted in an out-of-range value. The
  statement has been terminated.

Creating a new login with DEFAULT_LANGUAGE =British

USE [master]
GO
CREATE LOGIN Mary8 WITH PASSWORD = 'A2c3456$#',
CHECK_EXPIRATION = ON,
DEFAULT_LANGUAGE =British
GO
USE [test]
GO
CREATE USER [Mary8] FOR LOGIN [Mary8]
GO
USE [test]
GO
ALTER ROLE [db_owner] ADD MEMBER [Mary8]
GO

Now if I login with Mary8 and run same insert statement it will succeed.

SELECT SUSER_NAME(), USER_NAME();
GO
INSERT INTO testDateTimes (id, ts) VALUES (2, '13.05.2017 12:34:56.789')
GO

  (1 row(s) affected)

Reference I used.


Select consecutive number of rows starting from some number What I need is to select 100 consecutive rows from a table, starting at some defined point without ordering data.

As far as I can observe, inserted data to a table is stored in no order but is retrieved by identical SELECT statements in the same order.

I could get use of 100 random rows, but random rows are not useful because I always need to retrieve the same data from the database in order to compare results of the algorithm I'm developing.

I don't want to order data by any field because otherwise I'd be biasing the selected data.

The sql statement should be something like:

SELECT field1, field2 FROM table1 WHERE row_number > 70000 LIMIT 100;

or probably even better (as I think LIMIT doesn't prevent the database from outputting every record above row number 70000):

SELECT field1, field2 FROM table1 WHERE row_number > 70000 AND row_number <= 70100;

What SELECT statement should I use?

You can try something like

SELECT field1, field2
FROM table1
ORDER BY ctid
OFFSET x
LIMIT 100
;

As I commented, without ordering you can never be sure that you get the same 100 rows. This way you will get the same rows in a certain order if they don't get updated or deleted.


Does SQL Server ever delete old error log files? I know I can cycle the current error log easily by running sp_cycle_errorlog, but I'm wondering if SQL Server will ever delete the old/archived error log files at all. Can't seem to find an answer to this anywhere...

In SQL Server Management Studio, in Object Explorer > Management, there is a setting if you right click on SQL Server Logs and select Configure. You can specify how many log files you want to keep. Once it reaches that number it will start removing the old ones.


maintenance plan executing by agent I have a built-in, regular maintenance plan scheduled to run weekly.

The databases are SharePoint databases and a snap manager (netapp snap manager) backs up these databases.

The rebuild maintenance plan is executed whenever the backup runs by the SQL server agent.

Why and how is this job being called?

Is there a SQL event being throw when the snap manager starts the backup?  If so there could be an alert setup on that event which is setup to start the job.  Check the SQL Agent alerts to see if it is configured to capture anything.


onsite and offsite backup question We have an sql database of 165GB (includes filestreaming) and we do an onsite full backup every evening using veritas. We would like to also have an online backup in place just so that we don't need to take backup tapes home. We would like to be able to restore back up to 28 days. It seems that all of the online backup companies will only do sql full backup or differential backups not incremental.

My problem is, if differential backups will look at the last full backup date and only send over changes since then, our on site backup will mess this up.E.g If i do a full online backup on Sunday.  Then we do an onsite backup at 6pm every day, at 9pm on Thursday when we do the online backup, it will only find changes from 6pm on Thu -9pm Thu. I want it to find changes from the last full online backup i.e. sunday up to 9pm on Thursday.

Does anyone have any solutions or recommendations, this must be a common issue.

Assuming you are doing native backups to disk, and then Veritas backs up to tape:

Do your onsite backups to disk. Setup blob storage on Azure (or S3 with Amazon) and then script copying that file to that storage. You would then have Veritas backup that file as well from the local disk to tape.
If you happen to be on SQL Server 2012 or higher you can backup directly to Azure storage. The Azure storage, you can select geo-redundant service and this will allow your backup to be duplicated between Microsoft data centers. You could consider actually dropping doing backups to Veritas with this option, but an extra copy doesn't hurt to have locally.

Response to your comment

  We do backup to external disk. We no longer use tapes. I want to do a
  full onsite backup each day so if something went wrong then i could
  restore the full database back quickly. I just also want an offsite
  copy. Is this possible? We are using sql server 2008 R2. Does azure
  support incremental/differential backups with sql 2014 or will it only
  keep one copy which is the latest?

Think of Azure storage as an external disk, it just sits in Microsoft Data Centers. Copying your backups to Azure provides you the ability if your building gets wiped away, bring up another server with Internet access and retrieve the last backup you copied up.

Azure storage (blog storage) allows any file type, the only limit is one single file cannot exceed 1TB in size. If in the future your backups grow to that size, you will just need to split the backup into multiple files to keep it under that limit.

SQL Server 2012 and higher allow backing directly to Azure via BackupToURL. You can utilize Microsoft® SQL Server® Backup to Microsoft Azure®Tool to do similar action across any version of SQL Server. There is a write up done for using this tool here.

Azure does not delete or clear out old backup files for you, so in your design of the backup solution using this service you will need to also deal with cleaning files out if needed.


MySQL - How can I improve this query? EXPLAIN attached I have a query that I feel should go much faster then it does. Right now it'll execute around 1,5xx secs each time. I don't have any advanced knowledge of looking at the EXPLAIN and draw conclusions. Maybe someone can help?

The query has all its ID-fields indexed and unsigned.

"reservations" contains around 100,000 records and "reservation_nights" around 180,000 records. All other tables have under 1000 records each.

"profiles" is LEFT JOIN:ed because not all reservations has profiles.

Here's the query:

SELECT DISTINCT(reservations.id), `reservations`.`referenceId`, `reservations`.`referenceStatus`, `reservations`.`status`, `reservations`.`firstname`, `reservations`.`lastname`, `reservations`.`dateArrival`, `reservations`.`daysToCheckIn`, `reservations`.`rateCode`, `reservations`.`roomCode`, `reservations`.`totalNights`, `reservations`.`rateRevenue`, `reservations`.`dailyRate`, `reservations`.`totalRevenue`, `reservations`.`foodBeverageRevenue`, `reservations`.`additionalRevenue`, `channels`.`name` AS `channelName`, `channelgroups`.`name` AS `channelGroupName`, `markets`.`name` AS `marketName`, `profiles`.`totalReservations`
FROM `reservation_nights`
INNER JOIN `reservations` ON reservations.id = reservation_nights.reservationId
INNER JOIN `channels` ON channels.id = reservations.channelId
INNER JOIN `channelgroups` ON channelgroups.id = channels.channelgroupId
INNER JOIN `markets` ON markets.id = reservations.marketId
LEFT JOIN `profiles` ON profiles.id = reservations.profileId
ORDER BY `reservation_nights`.`dateStayed` DESC
LIMIT 100

Attached is the EXPLAIN from PHPMYADMIN. Where should I start optimizing this query?

First, please remove the parentheses from DISTINCT(reservations.id).
DISTINCT is not a function.
About performance, why do you join reservation_nights? You only seem to use that table in the ORDER BY. Assuming that a reservation can span several nights, you probably mean to use the last date (from the many that a reservation has) for the ordering and nothing else. This however, requires a join on reservation_nights and thus many rows for each reservation are created in the result set. So, you had to use DISTINCT to get rid of the duplicate rows. DISTINCT is often inefficient, especially when performed for big results sets and not in small/indexed tables.

Try this rewriting, that gets rid of the DISTINCT and pushes the ORDER BY / LIMIT 100 in a derived table where only base table is used. This technique which I call "LIMIT first, JOIN later" can be more efficient as the order by is done on less data (single table vs a large join) and then the joins are done on less data as well (max 100 rows from each of the joined tables):

SELECT
    r.id,
    r.referenceId, r.referenceStatus, r.status,
    r.firstname, r.lastname,
    r.dateArrival, r.daysToCheckIn,
    r.rateCode, r.roomCode,
    r.totalNights, r.rateRevenue,
    r.dailyRate, r.totalRevenue,
    r.foodBeverageRevenue, r.additionalRevenue,
    c.name AS channelName,
    cg.name AS channelGroupName,
    m.name AS marketName,
    p.totalReservations
FROM
    ( SELECT reservationId, MAX(dateStayed) AS lastDateStayed
      FROM reservation_nights
      GROUP BY reservationId
      ORDER BY MAX(dateStayed) DESC
      LIMIT 100
    ) AS rn
  INNER JOIN reservations AS r ON r.id = rn.reservationId
  INNER JOIN channels AS c ON c.id = r.channelId
  INNER JOIN channelgroups AS cg ON cg.id = c.channelgroupId
  INNER JOIN markets AS m ON m.id = r.marketId
  LEFT JOIN profiles AS p ON p.id = r.profileId
ORDER BY
    rn.lastDateStayed DESC ;

If you don't already have one, add an index on reservation_nights (reservationId, dateStayed) to improve the efficiency of the subquery.