texts: How to solve 'tuple concurrently updated' error?

texts:
The following query is performed concurrently by two threads logged in with two different users:

WITH raw_stat AS (
    SELECT
       host(client_addr) as client_addr,
       pid ,
       usename
    FROM pg_stat_activity
    WHERE usename = current_user
)
INSERT INTO my_stat(id, client_addr, pid, usename)
    SELECT nextval('mystat_sequence'), t.client_addr, t.pid, t.usename
    FROM (
        SELECT client_addr, pid, usename
        FROM raw_stat s
        WHERE
            NOT EXISTS (
               SELECT NULL
               FROM my_stat u
               WHERE current_date = u.creation
                   AND s.pid = u.pid
                   AND s.client_addr = u.client_addr
                   AND s.usename = u.usename
            )
    ) t;

From time to time, I get the following error:

tuple concurrently updated

I can't figure out what throw  this error and why this error is thrown. Can you shed a light ?

Here is the sql definition of the table my_stat.

CREATE TABLE my_stat
(
  id bigint NOT NULL,
  creation date NOT NULL DEFAULT current_date,

  client_addr text NOT NULL,
  pid integer NOT NULL,
  usename name NOT NULL,
  CONSTRAINT mystat_pkey PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);


texts: Huge slowdown to SQL Server query on adding wildcard (or top)

texts:
I've got a zoo of 20 million animals which I track on my SQL Server 2005 database. About 1% of them are black and about 1% of them are swans. I wanted to get details of all the black swans and so, not wanting to swamp the results page I did:

select top 10 *
from animal
where colour like 'black'
and species like 'swan'

(Yes, inadvisedly those fields are freetext but they are both indexed). It turns out we have no such animals as the query returns an empty set in about 300 milliseconds. It would have been about twice as fast if I'd used '=' rather than 'like' but I have a premonition the latter is about to save me some typing.

It turns out the head zookeeper thinks he may have entered some of the swans as 'blackish' so I modify the query accordingly:

select top 10 *
from animal
where colour like 'black%'
and species like 'swan'

Turns out there are none of those either (and in fact there are no 'black%' animals except 'black' ones) but the query now takes about 30 seconds to return empty.

It seems it's only the combination of 'top' and 'like %' causing trouble though because

select count(*)
from animal
where colour like 'black%'
and species like 'swan'

returns 0 very quickly, and even

select *
from animal
where colour like 'black%'
and species like 'swan'

returns empty in a fraction of a second.

Does any one have any idea why 'top' and '%' should conspire to cause such a dramatic loss of performance, especially in an empty result set?

EDIT: Just to clarify, I'm not using any FreeText indexes, I just meant that the fields are freetext at the point of entry, i.e. not normalized in the database. Sorry for the confusion, poor wording on my part.


texts: Why can't I create a new schema with the owner set to dbo?

texts:
I created a test account for checking permissions against.
It currently has the db_datareader, db_datawriter, and db_ddladmin roles for the Northwind database.
But when it tries to create a new schema on Northwind, the creation fails if it tries to authorize dbo as the schema owner, what's up?

Version:
SQL Server Management Studio: 15.0.18183.0
Microsoft Analysis Services Client Tools: 15.0.1487.0
Microsoft Data Access Components (MDAC): 10.0.18362.1
Microsoft MSXML: 3.0 6.0
Microsoft Internet Explorer: 9.11.18362.0 Microsoft
.NET Framework: 4.0.30319.42000
Operating System: 10.0.18362


texts: Move Oracle Database to New Server with Dataguard in place

texts:
I have a live Oracle Database with Dataguard in placed. I want to migrate live database to new server as tech refresh, but only Primary database. How I can achieve this? I have ideas on how I want to do it but I'm not sure whether it is correct.

Create a full backup, then restore it to new server and update the dataguard configuration. Problem is, SCN in Standby database maybe different than New Primary database due to old Primary database is still running. If this happen, can i apply log from old Primary to New Primary? I'm not sure..
Switch the existing Standby database to be Primary database and create a new standby database in new server. Then switch back Primary role to new database in new server.

PS. I'm not good in English. Thanks in advance.


texts: How do I install/enable the uuid-ossp extension on Postgres 9.3?

texts:
I’m using Postgres 9.3 on Ubuntu Linux 14.04.  How do I install/enable the uuid-ossp extension?  I first verified the contrib package was installed …

root@prodbox:/home/rails/myproject# apt-get install postgresql-contrib
Reading package lists... Done
Building dependency tree
Reading state information... Done
postgresql-contrib is already the newest version.

and then I logged in and tried to create the extension …

root@prodbox:/home/rails/myproject# su - postgres
postgres@prodbox:~$ psql
psql (9.6.0, server 9.3.11)
Type "help" for help.

postgres=# CREATE EXTENSION "uuid-ossp";
ERROR:  could not open extension control file "/usr/share/postgresql/9.3/extension/uuid-ossp.control": No such file or directory

I’m all out of ideas.  What am I missing?


texts: How can I find out which tables use reference partitioning from the Oracle data dictionary?

texts:
I'm writing a generic drop-all-objects script for our Oracle databases.  It generates drop table TABLE_NAME cascade constraints purge lines for all tables, amongst other object types, by looping through user_objects.

The problem is that tables which are have reference-partitioned tables dependent on them can't be dropped in this way: ORA-14656: cannot drop the parent of a reference-partitioned table.

How can I detect which tables are the parents of reference partitioned tables from the data dictionary, so I can skip them in the first loop, and drop them in a second loop?


texts:
I have a SQL Server 2012 database.

I would like to be able to make changes to this database or to it's replication, then make the replica, the real one.

The point here is to have 0 downtime on system updates.

What will happen if on the replica I'll change the type of a column?

Am I on the right way or there is a better way to have 0 downtime when updating my system?

Thanks.


texts: Combine WHERE NOT EXISTS with ON DUPLICATE KEY UPDATE

texts:
I have 2 tables params and params_exceptions where params is supposed to be updated frequently by an external script.

INSERT INTO params (extension, type, subtype, paramvalue)
VALUES ('12345', 'FRNR1', 'FORWARD', '24268')
ON DUPLICATE KEY UPDATE paramvalue = VALUES(paramvalue);

The purpose is that params_exceptions contains records which prevents certain records in params to be updated with the new data (or using the data in params_exceptions afterwards for that record).

I've been messing around a bit and I was thinking of combining WHERE NOT EXISTS within the ON DUPLICATE KEY UPDATE? Of course all of the above is possible by using sequential statements, but it's possible that the script would run at the same time and I can't use transactions for now.

I was thinking of the following statement, but I can't seem to figure it out:

INSERT INTO params (extension, type, subtype, paramvalue)
SELECT *
FROM (SELECT '12345', 'TEST', 'TEST2', '24268') AS tmp
WHERE NOT EXISTS (
    SELECT 1, extension
    FROM params_exceptions t
    WHERE extension = '12345' AND
          type = 'FRNR1' AND
          subtype = 'FORWARD'
) LIMIT 1
ON DUPLICATE KEY UPDATE subtype=subtype, paramvalue=t.extension;

Can someone give me some advice here?


texts: Looking for a way of moving millions of record without impacting the transaction log

texts:
I need to move around 10 million records out of a table into a new "optimized" table, but I need to keep the database online.  What would be the best method in doing this without impacting the database performance?  Also, turning the Recovery Mode to Simple won't help because its a live OLTP database.

The purpose for the move is to add some new indexes.  The problem is there are a lot of records that are old and bulk out the table to around 80 million rows, so I can't just add a new index because the table is used regularly.

Would a SELECT INTO be the better option, or create the table before hand and then use an INSERT?


texts:
I am currently trying to design an Access database that will house financial data containing forecasting, actuals and prior forecast data.

The current format of the Excel "database" is

Customer Name | Contract Number | Region | Product | Fee Type | Commit Type
| Jan 19 Plan - Dec 2023 Plan | Jan 19 Prior Forecast to Dec 2023 Prior
Forecast | Jan 19 Forecast - Dec 2023 Forecast

Where Jan 19 Plan - Dec 2023 Plan is a set of 59 columns, each month with it's own column, and no column in the entire "database" is unique. And contract numbers will have N/A's or blanks etc. This is my initial design

I'd love any feedback or modifications anyone can provide


texts: How does the sys.objects Catalog View pull from itself?

texts:
I'm using this stored procedure called OBJECT_DEFINITION as was suggested. It's helping me wrap my head around the SQL Server system. However, there is one area that I can't yet figure out. On this one table sys.objects, you'll see it pulls from itself.

1> SELECT OBJECT_DEFINITION(OBJECT_ID('sys.objects'))
2> GO

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
CREATE VIEW sys.objects AS
    SELECT name,
      .. stuff.
    FROM sys.objects$

Now I'm totally lost how is sys.objects selecting from sys.objects$ what is sys.objects$. Thinking there may be a table of the same name, I first tried to create a table and a view by the same name, but I can't do that. I get (f is the name I picked). You can't do this in any database I've ever used.

Msg 2714, Level 16, State 3, Server x230, Procedure f, Line 1
There is already an object named 'f' in the database.

Microsoft docs sys.objects only as a Catalog View, it's not listed as a Base Table

The counterpart Catalog View, sys.system_objects pulls from the Base Table sys.sysschobjs, and OBJECT_DEFINITION clearly shows that.

My guess is that the SQL here isn't the actually SQL the view is executing, but some kind of comment or description on it: meta-data of sort, and that it's bugged. But, I could be totally off.


texts: Cannot connect to MariaDB (MySQL) in Manjaro Linux

texts:
I have just installed mariadb but when I try to create a user or even type mysql -u root it gives me this message:

  ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/run/mysqld/mysqld.sock' (2 "No such file or directory")

I followed this tutorial but when reaching the part I have to enter mysql_secure_installation command it gives me the same error.

What do I do?


texts: How to prevent #Error values derived from Nulls in an outer join

texts:
Suppose I have two tables to outer join

Tab1:
ID  Nam
1   Joe
2   Moe
3   Flo

Tab2:
ID  Val     y   w
1   stuff   66  33
2   duff    67  44

and one of the resulting columns is derived -- i.e., it's a function of two other columns

SELECT Tab1.*, Tab2.Val, Tab2.y, Tab2.w, week(Tab2.y,Tab2.w) AS week
FROM Tab1 LEFT JOIN Tab2 ON Tab1.ID = Tab2.ID

As expected, where there is no join, nulls appear under the actual columns, but #Error appears under the derived column:

ID  Nam Val     y   w   week
1   Joe stuff   66  33  66w33
2   Moe duff    67  44  67w44
3   Flo                 #Error

First, I thought I could prevent #Error via the useful-for-handling-null-strings function Nz() but Nz(week(Tab2.y,Tab2.w)) did nothing.
Next, I thought it was something I had to handle in my week() function -- i.e., handle cases of IsNull(y) -- but I discovered that it doesn't even get called for the "null cases".

How to prevent #Error values from showing?


texts:
hi I want to union 2 tables but I am not sure if the table is exits
this is my query:

select  a from table
union
select  case when exists(select a from table1) then (select a from  table1)
else (select a from table2) end;

got this

  ERROR: ERROR 4840:  Subquery used as an expression returned more than one row

I need to use limit 1 in a subquery but it's not the result that I wanted.

do you have other suggestions?
thanks

table, for example, I just created them in my local env for testing:

table 1

CREATE TABLE public.table1
(
    a int,
    b varchar(80),
    c int
);

CREATE TABLE public.table
    a int,
    b varchar(80),
    c int
);


texts: Query uses Primary Key Index instead of Nonclustered index defined

texts:
I have this simple query and I am confused why the index is not working.

SELECT lop.RecordID ProspectusID,
    c.c_code CourseCode,
    c.c_desc CourseDescription,
    COALESCE(c.c_major, '') MajorName,
    lop.EffectiveYear EffectiveSchoolYear,
    COALESCE(COUNT(pc.ProspectusID), 0) SubjectCount
FROM dbo.ListOfProspectus AS lop
    INNER JOIN dbo.courselist AS c ON lop.CourseCode = c.c_code
    LEFT JOIN dbo.ProspectusContent AS pc ON lop.RecordID = pc.ProspectusID -- <== problem
WHERE lop.CourseCode IN ('BSIT')
GROUP BY lop.RecordID, c.c_code, c.c_desc, c.c_major, lop.EffectiveYear
ORDER BY CourseCode, EffectiveSchoolYear

which generates the following plan,

I have an index defined on dbo.ProspectusContent.ProspectusID.

CREATE NONCLUSTERED INDEX [idx_ProspectusContent_prospectusid]
ON [dbo].[ProspectusContent]
(
    [ProspectusID] ASC
)

But why is it still using the primary key index?

ALTER TABLE [dbo].[ProspectusContent]
ADD CONSTRAINT [ProspectusContent_pk_RecordID] PRIMARY KEY CLUSTERED
(
    [RecordID] ASC
)

If I remove the following computed column: COALESCE(COUNT(pc.ProspectusID), 0) SubjectCount in the SELECT clause, the server generates this plan:

UPDATE

However, when I try to execute a query on table dbo.ProspectusContent alone, it uses the nonclustered index,

SELECT ProspectusID
FROM dbo.ProspectusContent AS pc
WHERE pc.ProspectusID = 2

By the way, idx_ProspectusContent_prospectusid is the same with idx_dmcasarms_ProspectusContent_prospectusid, just convention name policy.

If there's anything missing detail in my question, please do me so I can update it. Thank you.


texts: ODBC can't connect to internal MySQL without external network

texts:
Running ODBC 5.2 (10.0.0.10) and MySQL 4.x (10.0.0.50). Yes, I know that ODBC 5.x dosen't officaly support MySQL 4.x but it was working fine for ~8 months till now when we have little problems with ISP and external network is not that stable.

Thing is that ODBC can make connection with MySQL fine when external network is working but in a moment when we get problem with external network the connection between 10.0.0.10 (ODBC) and 10.0.0.50 (MySQL) stops.

OLE DB provider "MSDASQL" for linked server "xyz" returned message\
"[MySQL][ODBC 5.2(w) Driver]Lost connection to MySQL server at\
'waiting for initial communication packet', system error: 10060".

Cannot initialize the data source object of OLE DB "MSDASQL" for linked server "xyz".

... and it gets even more interesting I can successfully ping between ODBC and MySQL machines and even make telnet for 3306 from 10.0.0.10 to 10.0.0.50 while external network dosen't work and ODBC connection fails.

This leads to my question... why ODBC can't make connection to MySQL without external network? Have I missed something or should I downgrade to ODBC 3.51?


texts:
Is there any way to set priority for a role if a user falls in two roles?

EXAMPLE : If the user is a member of the manager and staff roles, I want to get the features of the manager on conflicting issues.

Delete is denied in staff while granted in manager and user1 is a member of both, then user1 could delete the data.


texts:
Please help me to figure it out, help is much appreciated!

ERROR:

FATAL:  no pg_hba.conf entry for replication connection from host "172.18.0.4", user "repmgr", SSL on

The following is a list of my pg_hba entries:

The following is the query output from pg_hba_file_rules:

line_number | type  |   database    | user_name  |  address   |                 netmask                 | auth_method | options | error
------------+-------+---------------+------------+------------+-----------------------------------------+-------------+---------+-------
      85 | local | {all}         | {postgres} |            |                                         | peer        |         |
      90 | local | {all}         | {all}      |            |                                         | peer        |         |
      92 | host  | {all}         | {all}      | 127.0.0.1  | 255.255.255.255                         | md5         |         |
      94 | host  | {all}         | {all}      | ::1        | ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff | md5         |         |
      97 | local | {replication} | {all}      |            |                                         | peer        |         |
      98 | host  | {replication} | {all}      | 127.0.0.1  | 255.255.255.255                         | md5         |         |
      99 | host  | {replication} | {all}      | ::1        | ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff | md5         |         |
     101 | host  | {all}         | {repmgr}   | 172.18.0.0 | 255.255.255.0                           | trust       |         |
     102 | host  | {replication} | {repmgr}   | 172.18.0.0 | 255.255.255.0                           | trust       |         |

As you can see, the pg_hba.conf did get loaded to the database.
Where did I do wrong?


texts: What should be architecture of multi client web application?

texts:
My question is not related to a specific technology rather it's about design methodology that should be chosen. My company is about to create a web application whose users will be corporate companies. The application will store data of all the signed up/registered companies including financial data. We are unsure what should be the architecture of the application specifically from database point of view.

I can think of two approaches:

Single database for all the companies with same codebase. The company is identified using company table.
Separate Database for each company with same codebase or may be different codebase for each company/client.

Both of the approaches have their pros and cos. If single database is used then data size will increase very fast and performance issue can be encountered due to load by multiple company users hitting the same database. The benefit of this approach is that schema is very easy to manage schema. The separate database on the other hand has benefit of reduced performance overhead, privacy of company data. But the hard part is that a single change in schema needs to be replicated in all the company databases.

These are the things we are aware of. As part of our policy, we would not sell the code to the company/client. Only license will be bought by them.

We are only interested in relational databases and would not be going for NoSQL. Plus the number of client/companies will not be limited and can grow to any number.

What is the better way to design database architecture of this scenario. I know there are thousands of applications that might have faced this scenario before development, but this is my first time :)

So would really like your input on what is the better approach for that.

Thank you very much!


texts: How to create DBA user from oracle APEX 11gR2 XE

texts:
Please anyone tell me how to create DBA user from ORACLE Application express(http://127.0.0.1:8080/apex/apex_admin)?

I installed Oracle 11gR2 XE on windows 8.1 PC and I am able to login to Oracle APEX (above link) with username: "ADMIN" and password used during installation. but I can't use same username/password to login to "Oracle SQL Command Line". I am trying to connect with command

CONNECT username/password

All other solutions suggests a command "CREATE USER....." but I don't know where to write that command without connecting SQL Command line. I am missing a simple step somewhere. please help me.

My question is exactly opposite of this question. User with dba privs can't login to APEX, but can from SQL*Plus

Where I can login to Oracle APEX web interface and create new workspace, Schemas, Users but I am not able to login to SQL Command line to create Database objects.


texts: How to coordinate two sessions writing the same row to a MySQL table

texts:
I am using MySQL 5.6 and have an append-only "lookup" table that's used to store reference data:

CREATE TABLE my_table (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  my_column VARCHAR(32) NOT NULL,

  PRIMARY KEY (id),
  UNIQUE KEY my_column_uk (my_column)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

Rows are inserted, but never updated or deleted. The table is small (thousands of rows at most), so query performance is not a concern.

I have 2 different processes (running identical code) which insert rows into this table. Each process has an in-memory write-through cache of the my_column -> id value. Whenever a new entry is put into the cache that doesn't already exist, it's synchronously inserted into the database table, then added to the cache.

How do I handle the scenario where 2 (or more) processes try to insert the same my_column value at the same time?

Since the table has a unique key constraint on my_column, my thought is to have both processes try to insert the new row and get the new PK id value. One of them will succeed, and can add the new entry to its cache. The other one will fail, but then it can fall back to querying for the id of the row that the other process inserted. I call this the "write-read" approach.

Is there a better way of handling this contention between the two processes? Would a "read-write" approach be better, potentially with a different transaction isolation level ("read uncommitted" e.g.)?

The one potential downside I see with the "write-read" approach is that it involves catching a duplicate key exception, and using that as an indicator to query for the existing row. This is perhaps a misuse of an exception, because this scenario is expected to occasionally happen, and is not really exceptional.

The problem with the "read-write" approach is that I'm not sure there's a good way to have process 2 "see" the row that process 1 added, until it finishes its transaction. So this might need to be a "read-write-read", in which case I'm better off just starting with the INSERT like in the "write-read" approach.

Some more constraints:

I need to get the resulting PK ID, regardless of whether the row was inserted or already exists. However I don't want to burn through auto-increment values, so I'm not going to use the UPDATE id = LAST_INSERT_ID(id) kludge. I'd rather just execute a second SQL statement to SELECT the id value, because I'm only inserting a row every ~5 minutes into a table with ~100k rows, so query performance is not a major concern.
The reason why I'm not using the VARCHAR my_column column as the PRIMARY KEY column is that this lookup table has a FK reference from another much bigger (500M rows, 20 cols) table. I'm using this lookup table pattern to store reference data - e.g. failure codes ("eligibility.failure.not_enough_foobar_data") or time zones ("America/Los_Angeles") - so that the much larger main table can use a short FK id to reference that value (e.g. failure_code_id or time_zone_id), instead of repeating the long string.


texts: I need to alter  existing procedure in MYSQL, so as per I know we don't have that option in MYSQL.  Every time we need to drop procedure and recreate,  so is there any way  we can  alter procedure  without dropping the existing one.


texts: How to improve performance of SELECT query to same tables in Linked Server

texts:
I have a SELECT query to a linked server. It looks similar to this

SELECT     @Var1 = (SELECT  COL1
                    FROM    [LinkedServer].[dbo].[TableA]
                    WHERE   <Condition1>),
           @Var2 = (SELECT  COL1
                    FROM    [LinkedServer].[dbo].[TableA]
                    WHERE   <Condition2>),
           @Var3 = (SELECT  COL1
                    FROM    [LinkedServer].[dbo].[TableA]
                    WHERE   <Condition3>),
           ...
FROM       [LinkedServer].[dbo].[TableB]
WHERE      <Condition4>

So there are multiples SELECT statements to the same table in this linked server. Is there a way to improve the performance? Is copying TableA to a temp table in local database server a bad idea? Size of TableA is large.

[UPDATE1]: Updated my query

SELECT      VAR_1,
            (SELECT  SOL
            FROM     [LinkedServer].[dbo].[TableSol]
            WHERE    COL_1 = [TableA].COL_1
            AND      COL_2 = [TableA].COL_2
            AND      COL_A = [TableA].COL_A) AS VAR_2,
            (SELECT  SOL
            FROM     [LinkedServer].[dbo].[TableSol]
            WHERE    COL_1 = [TableA].COL_3
            AND      COL_2 = [TableA].COL_4
            AND      COL_A = [TableA].COL_A) AS VAR_3,
            (SELECT  SOL
            FROM     [LinkedServer].[dbo].[TableSol]
            WHERE    COL_1 = [TableA].COL_5
            AND      COL_2 = [TableA].COL_6
            AND      COL_A = [TableA].COL_A) AS VAR_4,
FROM        [LinkedServer].[dbo].[TableA]
INNER JOIN  [LinkedServer].[dbo].[TableB] ON [TableA].COL_10 = [TableB].COL_11

It took me 15 secs to run the above query. I changed to use crosstab like below:

SELECT      VAR_1
            SOL.VAR_2,
            SOL.VAR_3,
            SOL.VAR_4,
FROM        [LinkedServer].[dbo].[TableA]
INNER JOIN  [LinkedServer].[dbo].[TableB] ON [TableA].COL_10 = [TableB].COL_11
CROSS APPLY (SELECT
                CASE
                    WHEN COL_1 = [TableA].COL_1 AND COL_2 = [TableA].COL_2 THEN SOL
                    ELSE ''
                END AS VAR_2,
                CASE
                    WHEN COL_1 = [TableA].COL_3 AND COL_2 = [TableA].COL_4 THEN SOL
                    ELSE ''
                END AS VAR_3,
                CASE
                    WHEN COL_1 = [TableA].COL_5 AND COL_2 = [TableA].COL_6 THEN SOL
                    ELSE ''
                END AS VAR_4
             FROM    [LinkedServer].[dbo].[TableSol]
             WHERE    COL_A = [TableA].COL_A
            ) AS SOL (VAR_2, VAR_3, VAR_4)

But it took me much longer (> 10 mins and I just stop because it did not finish).
Did I miss something?


texts:
I have to pull out a range of entries from a database, all based around two pieces of data.  In itself that's fine. An example of the data is:

Record   ID    Tags
1        1423  Day, Sun, Warm
2        1423  Day, Sun, Wet
3        1742  Night, Warm
4        2743  Night, Warm, Dry, Stars
5        4832  Sunrise, Bright, Clear, Cool

Now in the above data, the ID is an internal company ID, but Record is a unique DB ID.  There are 2,500 rows of similar data in the database, with other data that's not related to this query.  Now we need to pull the data in the following style, written here in a symbolic, rather than specific SQL so people can understand it.  I've included brackets to emphasis which parts must match:

WHERE (ID=1423 AND tags contains Warm)
OR WHERE (ID=2743 AND Tags contains Dry)
OR WHERE (ID=4832 AND Tags contains Cold)

The question is how do I get the SQL statement to work right?  I've tried

SELECT DISTINCT ID
FROM DBTABLE
WHERE ID=2743 AND Tags LIKE '%Warm%'
    OR ID=4832 AND Tags LIKE '%Cool%'

But it doesn't seem to return the correct records.  I have a specific query which I know will return 5 records, but every time it returns more, including ones that don't match.  I'm thinking the SQL is mixing up, for example, where ID=2743 AND (tags like warm or 2743) AND Tags like dry or 4832 etc...

Can anyone point me in the right direction.  In essence it is multiple where statements, where each statement has to match an ID and have a 'LIKE' tag. but the statement can match one or more of the given WHERE statements.  Does that make sense?


texts: SQL geography::point null parameter error when parameter is not null?

texts:
I was getting this error:

'geography::Point' failed because parameter 1 is not allowed to be null.

On this sql:

SELECT [ID], geography::Point([lat], [long], 4326) AS [loc]
FROM (
  SELECT [ID], CONVERT(float, [lat]) AS [lat], CONVERT(float, [long]) AS [long]
  FROM (
    SELECT [ID], [lat],
    [long], ROW_NUMBER() OVER (PARTITION BY [ID] ORDER BY [EFFDT] desc) AS [sequence]
    FROM [GEO]
  ) AS temp1
  WHERE [sequence] = 1
  AND [lat] IS NOT NULL
  AND [long] IS NOT NULL
) AS temp2
ORDER BY [ID]

But there where no null values, and I was only getting the error on our production machine (Production 13.0.4422.0) and not on our development machine (Dev 13.0.1728.2). After hours of searching and retrying I found that by reordering some stuff this would work:

SELECT [ID], [loc]
FROM (
  SELECT [ID],
    geography::Point([lat], [long], 4326) AS [loc],
    ,ROW_NUMBER() OVER (PARTITION BY [ID] ORDER BY [EFFDT] desc) AS [sequence]
  FROM [GEO]
  WHERE [lat] IS NOT NULL
  AND [long] IS NOT NULL
) AS temp
WHERE [sequence] = 1

I would really like to understand what I was doing wrong in the first query and why this worked in the second query?

The first query I tried to get only the rows that have the most recent records based on a sequence number calculated using an effective date column, and then convert lat and long data to a geography type.

In the second query I convert the lat and long data and then filter.

I thought I was being more efficient with the first query.


texts: MS SQL Server does not create tables in a specified filegroup

texts:
I have three filegroups in my database:

Default is TERTIARY. Now I want to create table on PRIMARY and it shows the following error

  Incorrect syntax near the keyword 'primary'.

create table tb1
(
    id int identity primary key nonclustered,
    somecolumn nvarchar(15)
)
on PRIMARY;

When I change the default filegroup option to PRIMARY it lets to create tables on PRIMARY.
Why does it show this error when my syntax is not wrong?

Thanks for your time.


texts: How to do a minor upgrade of PostgreSQL on Windows, e.g. 9.3.0 to 9.3.1?

texts:
What is the recommended way to perform a minor upgrade from PostgreSQL e.g. 9.3.0 to 9.3.1 using the Enterprise DB built windows installer?  Should I uninstall first or just install over the existing installation?

The current installation was performed with postgresql-9.3.0-1-windows-x64.exe.  Now I want to upgrade using postgresql-9.3.1-1-windows-x64.exe.


texts: SQL Server Error, "Invalid usage of the option FIRST in the FETCH statement."

texts:
From 2012 onward the SQL Server docs show they support OFFSET..FETCH which I'm trying to use instead of a LIMIT.

The following works fine in PostgreSQL to sample a result set,

SELECT *
FROM ( VALUES (1),(2),(3) ) AS t(x)
OFFSET 0 ROWS
FETCH NEXT 1 ROWS ONLY;

However, with SQL Server, I get

Msg 153, Level 15, State 2, Line 4
Invalid usage of the option FIRST in the FETCH statement.

What's going on here? Does SQL Server support the standardized OFFSET .. FETCH?


texts: How to maintain Index and Statistics on AWS RDS?

texts:
In the process of setting up the SQL Server Index and Statistics Maintenance code on our AWS RDS instance, I've managed to set up the scripts and can run it from SSMS.

Please advice on how to setup SQL Server Agent considering the restrictions of RDS?


texts: Can I create another database on AWS read replica?

texts: I am creating an MariaDB instance using AWS RDS I will also be creating a read replica of the DBinstance, what I also need is to host another DB on the replica, is it possible?


texts: listagg in order and including a sequence of separator when missing any row of a sequence

texts:
Sorry for the long title but it's a little bit difficult to explain.

We have a table

| N rows | Atrib1  |    Atrib2 | Atrib3 | Atrib4 |  Final row |
---------------------------------------------------------------
| N1    |   R     |     X       | One   |  A1    |     True   |
| N1    |   D     |     Y       |       |        |     False  |
| N1    |   K     |     Z       |       |        |     False  |
| N1    |   N     |     A       |       |        |     True   |
---------------------------------------------------------------

We must aggregate all the Atribs column into a single line with ';' separator in the order marked in the table, because we are trying to do an export to a CSV file.

We have no problem with the aggregation but there is a special condition that we must follow. If a row or more rows, from sequence (R, D, K, N) for Atrib1, doesn't exist we must even include a set of ';' for each Atrib(1..4).

The query that we have already works fine if all the rows are filled but not when someone is missing.

select N rows, LISTAGG(Atrib1 ||';'|| coalesce(Atrib2,'') ||';'|| coalesce(Atrib3,'') ||';'|| coalesce(Atrib4,''), ';') WITHIN GROUP (ORDER BY Code_crit_0) "Employees", Final row
from TABLE
group by N rows
order by decode(Code_crit_0, 'R',1,'D',2,'K',3,'N',4, 5) asc;

Any idea of how to accomplished this special condition without any SQL procedure.

Thanks in advance.


texts:
I want to ask about database design for financial statement.
I want to combine balance sheet, profit loss, cash flow,and ratio into one big table since each statement is dependent each other.

Here is my table structure:

Id,
RegistrationNo,
Year (date),
ProfitLossNetSales,
ProfitLossMonthlySales,
.....,
BalanceReliabilityFinInformation,
BalanceCashBank,
BalanceTimeDeposit,
.....,
CashFlowInterestExpenses,
......

Is it okay to have table structure like this (having many columns in one table)?

Update:
In application, the user input balance sheet form and profit loss form (like input data in excel).
The Cash Flow and Ratio form is read only and the value are based balance and profit loss.


texts: which node will execute events in master-master setup

texts:
Am newbie in this database replication. Right now on server master-master setup with Galera setup.

If I have events running in the database. It means from which master its running.
If one master down then the second master will execute that events or for that something can be done in the configuration.

Anyone like to suggest something related to it ?

Welcome to the MariaDB monitor.  Commands end with; or \g.
Your MariaDB connection id is 4123677
Server version: 10.1.39-MariaDB-1~stretch mariadb.org binary distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab, and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(test)]>


texts:
We have an application that uses MSSync to download data from a server into a client's SQL Server Express 2005. The client's have a dual core with hyperthreading so, a total result of 4 cores.

I have done a lot of research on SQL Server Express' limitations, and I think it comes down to 1 physical socket, but using up to 4 cores in that socket. But, that raises some questions in contrast with what I perceive in practice.

Our sync process is maxing out 1 core, drowning the sync process, resulting in 10-20 times slower performance than on devices that have higher GHz and not maxing out the single core that is being used by SQL Server Express 2005.

But: if SQL Server is allowed to use 4 cores in 1 socket, why is it using only 1 for our sync process? Is this because 1 connection has a dedicated core? Or am I not understanding the limit specs correctly? Would upgrading SQL Server Express to a modern version be useful to get it to use more cores?

I have done some more research. I used SQL Server Express 2012 and even SQL Server Developer 2012 and ALL max out only 1 core. So, apparently, this has nothing to do with an Express restriction.

It is probably a technical restriction where your querying within a single connection/transaction just stays on a single core. Likely, this is a logical requirement for guaranteeing transactional consistency.

What I do see as of SQL Server 2012, is that the load will swap to another core now and then, but it will never use more than 1 core at the same time. Not even the Dev edition.

If anyone could confirm these assumptions, that would be welcome.


texts: Backup failure for a DB part of Always-On Availability group - SQL 2012 Ent Edition

texts:
We have a DB that we are unable to take full backups of or able to join it in AG group. The T-logs are getting succesful backups. The DB is stuck in doing a CHECKPOINT that is blocked by a DB STARTUP process. Is there anyway to clear this internal process without having to reboot? It has been sitting like this for a few days. We are able to restore the DB using the T-logs but the DB is not highly available due to not being part of the AG. We are on SQL 2012 Enterprise edition. Please don't give me an answer that I need to upgrade to latest version.

Has anyone experienced this issue in SQL 2012 or any other version when using AAG?


texts: How exactly does trigram word-similarity work?

texts:
The docs on the word_similarity function say:

  Returns a number that indicates how similar the first string to the most similar word of the second string. The function searches in the second string a most similar word not a most similar substring. The range of the result is zero (indicating that the two strings are completely dissimilar) to one (indicating that the first string is identical to one of the words of the second string).

But they are a bit vague about what the result should be if the 'first string' contains more than one word. What is the explanation for the following results:

select word_similarity('foo bar','foo bar baz');

| word_similarity |
| :-------------- |
| 1               |

select word_similarity('baz bar','foo bar baz');

| word_similarity |
| :-------------- |
| 1               |

select word_similarity('baz foo','foo bar baz');

| word_similarity |
| :-------------- |
| 0.8             |

dbfiddle here


texts: SQL Injection: How to fix broken SQL query with comment?

texts:
This is purposedly vulnerable test site developed by Acunetik.
http://testphp.vulnweb.com/listproducts.php?cat=1

Let's test it.
http://testphp.vulnweb.com/listproducts.php?cat=1'

Error
Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''' at line 1 Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in /hj/var/www/listproducts.php on line 74

Looking at the error message, this site is clearly vulnerable to SQL Injection.
I imagine the SQL query looks like this.
SELECT ? FROM ? WHERE cat LIKE '1';

And this query generates SQL error because of additional ' character.
SELECT ? FROM ? WHERE cat LIKE '1'';

Normally by commenting out the syntax with -- comment will make this error go away.
SELECT ? FROM ? WHERE cat LIKE '1'--';

Similar query executed from the site
http://testphp.vulnweb.com/listproducts.php?cat=1'--

But this trick is not working for this site.
I have also tried with different kind of comments, but none of them work.
cat=1' --+
cat=1' -- -
cat=1' --%20
cat=1' #

What was I missing here?


texts: Need to suppress rowcount headers when using \G

texts: Is there a command to suppress the rowcount headers and asterisks when using '\G' to execute a SQL statement?  I am executing mysql with the -s and --skip-column-name options, but these don't suppress the rowcounts.


texts: Update one instance of Sql Server 2008 R2 Standard to SP2?

texts: We have one server with multiple SQL Server 2008 R2 instances installed.  To support an application we need to upgrade one instance to SP2.  Can we only apply the service pack to the specific instance on the server, or would it have to be applied to all of the instances on the server?


texts:
If I wanted to see the date, time and length of downtime on a MySQL instance/database, how would I go about doing that?

For example, if I wanted to see if the MySQL database went down at all last week, I could see when it when down and when it came back up – therefore, with a little subtraction I could figure out the downtime.

I am not particular about the method that I get this information, that is just what I thought would be the easiest way to do it. However, I am new to MySQL and am not really sure.


texts: MySQL Tuner returns: MySQL's maximum memory usage is dangerously high

texts:
Can someone help me optimize my MSQL configuration.

I run Zabbix and Grafana on a Ubuntu 18.4. MySQL Tuner shows me that my memory usage is dangerously high, but when I use the free command it shows that I have more than enough memory.

Here is the full mysqltuner rapport: MySQLTuner

Here is the output of: SHOW GLOBAL VARIABLES and SHOW GLOBAL STATUS
and SHOW FULL PROCESSLIST

And the output of: SHOW ENGINE INNODB STATUS since Zabbix uses excursively innodb tables.

The output of the top command
And the htop command
I also have the output of innotop: Google Drive

And the output of ulimit -a

core file size          (blocks, -c) 0
data seg size           (kbytes, -d) unlimited
scheduling priority             (-e) 0
file size               (blocks, -f) unlimited
pending signals                 (-i) 31652
max locked memory       (kbytes, -l) 16384
max memory size         (kbytes, -m) unlimited
open files                      (-n) 1024
pipe size            (512 bytes, -p) 8
POSIX message queues     (bytes, -q) 819200
real-time priority              (-r) 0
stack size              (kbytes, -s) 8192
cpu time               (seconds, -t) unlimited
max user processes              (-u) 31652
virtual memory          (kbytes, -v) unlimited
file locks                      (-x) unlimited

and free -h

              total        used        free      shared  buff/cache   available
Mem:           7,8G        7,0G        146M         37M        689M        554M
Swap:          2,0G        417M        1,6G

How can I increase the mysql memory usage? How can I optimize my MySQL configuration, so that I have maximum CPU and Memory usage?

Thank you for any help!


texts: Helpful nonclustered index improved the query but raised logical reads

texts:
I attempted to improve the following query that is getting executed continuously:

SELECT TOP(?) Schema1.Object1.Column1, Schema1.Object1.Column2, Schema1.Object1.Column3
FROM         Schema1.Object1 INNER JOIN
                      Schema1.Object2 ON Schema1.Object1.Column4 = Schema1.Object2.Column1 INNER JOIN
                      Schema1.Object3 ON Schema1.Object2.Column5 = Schema1.Object3.Column1 LEFT OUTER JOIN
                      Schema1.Object4 ON Schema1.Object1.Column6 = Schema1.Object4.Column7 LEFT OUTER JOIN
                      Schema1.Object5 ON Schema1.Object5.Column1 = Schema1.Object1.Column8
WHERE     (Schema1.Object1.Column3 > ?)
          ORDER BY Schema1.Object1.Column3 ASC

Object1.Column3 is of type ROWVERSION.
The execution plan showed a Clustered Index Scan on Object1.Index1 to filter on Object1.Column3. -> The original Plan.

Subtree Total costs of the plan is 59.
DOP = 4.

The IO stats of the original version is the following:

  (50 row(s) affected) Table 'Worktable'. Scan count 0, logical reads 0,
  physical reads 0, read-ahead reads 0, lob logical reads 0, lob
  physical reads 0, lob read-ahead reads 0.
  Table 'Object1'. Scan
  count 1, logical reads 299, physical reads 0, read-ahead reads 0, lob
  logical reads 0, lob physical reads 0, lob read-ahead reads 0.

So it was obvious to create a nonclustered index using ROWVERSION. I applied the following index:

CREATE Nonclustered index index1 ON dbo.Object1
  (Column3) INCLUDE  (Column4,Column3,Column2,Column1,Column8);

This changed the plan as expected to perform an index seek operation as you can see in the new plan.

Subtree Total costs of the new version is 0.33 now.
DOP = 1.

But what confuses me is the io statistics. Did it downgrade / get worse? The joined Object2 suddenly produces more IO than before (where for unknown reasons it was not even present in the statistics)...:

  (50 row(s) affected) Table 'Object3'. Scan count 0, logical reads
  162, physical reads 0, read-ahead reads 0, lob logical reads 0, lob
  physical reads 0, lob read-ahead reads 0.
  Table 'Object2'. Scan count
  50, logical reads 248, physical reads 0, read-ahead reads 0, lob
  logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table
  'Object1'. Scan count 1, logical reads 3, physical reads 0,
  read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob
  read-ahead reads 0.

This makes a logical read total value of 413 in comparison to 299 before.
What happened here? Did I improve the query or not? I guess YES, but what about the io statistics?


texts: Matching a value with every element of a set of values - Postgresql

texts:
I'm new in Postgres and I can't achieve what I'm trying to do.
I have a list of films in a Films table

ID_FILM    FILM NAME
1          Terminator
2          Forrest Gump
3          Nueve reinas

A Country table

ID_COUNTRY  COUNTRY
1           ARGENTINA
2           ENGLAND
3           USA
4           FRANCE

Finally, I have a table that contains in which countries the film was projected

ID_FILM     ID_COUNTRY
1           2
2           4
3           1
3           2
3           3
3           4

As you can see, the film Nueve Reinas (3) was projected in all countries (1, 2, 3, 4).
Well, that's what I'm trying to show. I'm trying to show the films projected in every country.
What I got was:

SELECT a.name FROM film a
JOIN film_country b ON a.id_film = b.id_film
WHERE b.id_country = ALL (SELECT id_country FROM country)

But this isn't quite working.


texts: Could I get some feedback on my first normalisation design?

texts:
Im creating a database for a project with Olympics as my case study. I have drafted a rough table storing information I would like to store and display in my database below (an unnormalised table):

I have drawn up this diagram to understand the basic relationships between my current existing tables (Athletes, Events, Medals & Country). But I also see the need to create a Sports table as well as an Events table as different Sports contain different events. From trying to follow normalisation stages my current database design is looking as such:

Could I ask for some feedback on this structure? I'm having a hard time applying 2NF and 3NF for this database. Please note this is a school project. I'm not asking for any straight up answers. Just a guidance from anyone with a better head on them for databases than I do.


texts: Bad estimates when sorting, good estimates without sort

texts:
I'm struggling with the tuning of one of the queries on our production SQL Server 2016 SP2.
I needed to anonymize the query due to security policies.
The query is like this:
SELECT TOP 1000 Object1.Column1,Object1.Column2,Object1.Column3,Object1.Column4,Object1.Column5,Object1.Column6,Object1.Column7,Object1.Column8,Object1.Column9,Object1.Column10,Object1.Column11,Object1.Column12,Object1.Column13,Object1.Column14,Object1.Column15,Object1.Column16,Object1.Column17,Object1.Column18,Object1.Column19,Object1.Column20,Object1.Column21,Object1.Column22,Object1.Column23,Object1.Column24,Object1.Column25,Object1.Column26,Object1.Column27,Object1.Column28,Object1.Column29,Object1.Column30,Object1.Column31,Object1.Column32,Object1.Column33,Object1.Column34,Object1.Column35,Object1.Column36,Object1.Column37,Object1.Column38,Object1.Column39,Object1.Column40,Object1.Column41,Object1.Column42,Object1.Column43,Object1.Column44,Object1.Column45,Object1.Column46,Object1.Column47,Object1.Column48,Object1.Column49,Object1.Column50,Object1.Column51,Object1.Column52
FROM Schema1.Object2 Object1
LEFT OUTER JOIN Schema1.Object3 Object4 ON Object4.Column3 = Object1.Column3
WHERE ((Object1.Column43 IS NULL OR Object1.Column43='') AND (Object4.Column54 = 0 OR Object4.Column54 IS NULL) AND Object1.Column3 = N'SomeValue')
ORDER BY Object1.Column3 ASC,Object1.Column8 ASC,Object1.Column7 ASC,Object1.Column12 ASC,Object1.Column13 ASC,Object1.Column1 ASC

The actual query plan can be found here
As you can see an index seek is done on Object2.Index1.Object1 but the estimates are terrible. SQL Server estimated that 194907 rows would come back but only 710 rows are returned.
When I remove the ORDER BY clause the estimates are much better, SQL Server expected only 1181 rows to come back. The plan can be found here.
What I do not understand is why an ORDER BY can influence the estimates of an index seek. Both queries are run with the same value for column3.
Can somebody explain this to me?


texts:
I'm trying to restore an old MySQL database and its tables by using the .frm and .ibd files.

First I tried restoring the structure by using the following command I found in another post:

mysqlfrm.exe --server=root:password@localhost "C:/location_of_file/file.frm" --port=3307

When I execute the command it says the following: "ERROR: Cannot find location of mysql_system_tables.sql."

Where is this file stored? Or do I have to execute this command in a specific location?


texts: Select records with the same first column value and different second column value? SQL Server


texts: Database schema for inventory management for multiple people

texts:
I am setting up a Postgres database that keeps track of items that entities in my system own. Each incoming datum says that Entity Q has N of Item X.

My first instinct is to have a separate table for each entity (multi-tenant). But I have read a number of posts advising against that due to scalability concerns.

What is the most effective way to design a database that stores a many-to-many relationship along with quantities? Thanks.

Additional information:

My information is coming through in JSON format from an API. For example:

{
    "data": [
        {
            "entity": "John",
            "item": "apple",
            "quantity": "5"
        },
        {
            "entity": "Mary",
            "item": "apple",
            "quantity": "2"
        },
        {
            "entity": "John",
            "item": "orange",
            "quantity": "6"
        }
    ]
}

My system must be able to tell each entity what items they have and how many (John has 5 apples and 6 oranges). It does not need to show which entities own any particular item (John and Mary both own apples).

Since each entity is independent I think it might make sense to keep all information in separate tables based on the type of entity. The main concern with this is that many entity tables might contain the same item (such as apples). But I do not think there is a way to combine these duplicates because the quantity of each item is mandatory.

I hope this clarifies some of the uncertainties surrounding my problem. Thank you for your support.


texts: Restrict Postgres superusers to local only connections

texts:
I have a database with several users: foo, bar, and super. I was looking at pg_hba.conf to lock down access.

I'd like to allow foo and bar to be able to connect from any IP with password authentication. Or locally with trust authentication.

I'd like to allow super to only be able to login locally with trust authentication. Never from a non-local address.

I started with this.

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# Allow everything if you're on the same machine.
local   all             all                                     trust
host    all             all             127.0.0.1/32            trust

But, then I got stuck because I'm not sure how to say "all users, except super". I'm looking for something like this.

host    all             not_super       all                     md5

One approach I thought I could take is to list foo and bar explicitly. But, I'm wondering if there is another way.

host    all             foo             all                     md5
host    all             bar             all                     md5


texts: MySQL slow query with IN() clause on indexed column

texts:
I have a table with this structure:

CREATE TABLE `events` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `uuid` varchar(36) COLLATE utf8_unicode_ci NOT NULL COMMENT '(DC2Type:guid)',
  `playhead` int(10) unsigned NOT NULL,
  `payload` longtext COLLATE utf8_unicode_ci NOT NULL,
  `metadata` longtext COLLATE utf8_unicode_ci NOT NULL,
  `recorded_on` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
  `type` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `UNIQ_5387574AD17F50A634B91FA9` (`uuid`,`playhead`),
  KEY `type` (`type`)
) ENGINE=InnoDB AUTO_INCREMENT=16944996 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

I'm running queries with a long list of type in a IN() clause, like:

SELECT COUNT(id) FROM events WHERE type IN (<long_list_of_types)

where "long list" means around 80 type which are strings like these:

Namespace.Domain.User.Event.SomeEvent1Happened
Namespace.Domain.User.Event.SomeEvent2Happened
Namespace.Domain.User.Event.SomeEvent3Happened
Namespace.Domain.User.Event.SomeEvent4Happened
Namespace.Domain.User.Event.SomeEvent5Happened
Namespace.Domain.User.Event.SomeEvent6Happened
Namespace.Domain.User.Event.SomeEvent7Happened
Namespace.Domain.User.Event.SomeEvent8Happened
Namespace.Domain.User.Event.SomeEvent9Happened
Namespace.Domain.User.Event.SomeEvent10Happened
Namespace.Domain.User.Event.SomeEvent11Happened
...

Result of the query:

| COUNT(id) |
+-----------+
|   8941096 |
+-----------+
1 row in set (27.77 sec)

This is the output of EXAPLAIN on that query:

| id | select_type | table  | type  | possible_keys | key  | key_len | ref  | rows  | Extra                    |
+----+-------------+--------+-------+---------------+------+---------+------+-------+--------------------------+
|  1 | SIMPLE      | events | range | type          | type | 768     | NULL | 38400 | Using where; Using index |
+----+-------------+--------+-------+---------------+------+---------+------+-------+--------------------------+

The table contains roughly 16 million events, and the query takes just short of 30 seconds. Is there a way to improve execution time on this kind of query?

MySQL version 5.6.44


texts: Secondary database log file in alwayson is very big

texts:
I have an alwayson environment (AlwaysOn AG).
Database [UsersDb] in the slave server, His log file is very big .
How can I reduce his size(shrink)?


texts: SELECT query: filter unable to distinguish between two different characters

texts:
In a recent support case, an error was traced to the difference between two similar-looking characters: ― (Unicode U+2015) and ー (Unicode U+30FC). A select statement with a WHERE clause was unable to distinguish between the two characters. Below is a simplified reproduction:

USE master;
GO

-- Create test DB.
CREATE DATABASE Test COLLATE Japanese_CI_AS;
GO

USE Test;
GO

-- Create test table.
CREATE TABLE Foo
(
    id INT NOT NULL PRIMARY KEY,
    descr NVARCHAR(50) NOT NULL
);

-- Insert test data.
INSERT INTO Foo (id, descr)
    VALUES (1, NCHAR(0x2015))
        , (2, NCHAR(0x30fc));

-- Select data.
-- Regardless of the WHERE, cannot distinguish between the two different characters.
SELECT * FROM Foo WHERE descr = NCHAR(0x2015);
SELECT * FROM Foo WHERE descr = NCHAR(0x30fc);

-- Only able to distinguish characters by adding a binary collation.
SELECT * FROM Foo WHERE descr = NCHAR(0x2015) COLLATE Japanese_Unicode_BIN
SELECT * FROM Foo WHERE descr = NCHAR(0x30fc) COLLATE Japanese_Unicode_BIN2;

The resolution was to fix the data to use only one of the characters. However, I am at a loss to explain why the SELECT query is unable to distinguish between the two characters without manually applying a binary collation. I'd appreciate if someone could explain what is happening here so that similar issues can be avoided in the future.


texts: Documenting SSRS and SSIS jobs that are the Job Activity Monitor

texts:
We are running a SQL Server 2014 database using SSRS as a reporting tool.  We have data coming in via SSIS jobs and linked servers.  We have developers creating various SSRS jobs and scheduling them.

I am wondering if anyone has any recommendations on how to document all the jobs in the Job Activity monitor.  My goal is to identify the SSIS jobs as well as the SSRS scheduled reports.  I'd like to know what tables the SSRS jobs hit so I can make sure they are scheduled after any tables they hit are updated.  Does anyone have a tool or a way they have created this documentation?  I have become the accidental DBA in my group so any guidance would be appreciated.

Thanks!


texts: Unable to start mongod daemon and it looks its crashing

texts:
DummyD3 is node3 of 3 node replication cluster.
DummyD2 is primary and
DummyD1 is Secondary (which is in sync with primary).
I tried deleting the data under data path and did rsync from other secondary node back to back twice to copy the updates while 1st was running in 2nd rsycn and started the deamon.
rsync -asvh Dummy1:/mongodb/data/dbinstance/* /mongodb/data/dbinstance
rsync -asvh Dummy1:/mongodb/data/dbinstance/* /mongodb/data/dbinstance
rm /mongodb/data/dbinstance/mongod.lock
rm /mongodb/data/dbinstance/mongod.pid
su - mongoinstance -c "/usr/bin/mongod --config /etc/mongo/dbinstance/mongodb.conf &"

Ran above command in screen from problematic node.
~# mongod --version
db version v4.0.4

I'm here looking for solution that if some has solution to help me fixing this issue.
root@DummyD3:~# tail /mongodb/files/errorlogs/dbinstance/mongodb.log

 mongod(_ZN5mongo15printStackTraceERSo+0x41) [0x55c2e748caa1]
 mongod(+0x23D9CB9) [0x55c2e748bcb9]
 mongod(+0x23DA19D) [0x55c2e748c19d]
 libpthread.so.0(+0x11390) [0x7fa295382390]
 libc.so.6(gsignal+0x38) [0x7fa294fdc428]
 libc.so.6(abort+0x16A) [0x7fa294fde02a]
 mongod(_ZN5mongo32fassertFailedNoTraceWithLocationEiPKcj+0x0) [0x55c2e5ac1ea7]
 mongod(_ZN5mongo17wtRCToStatus_slowEiPKc+0x413) [0x55c2e5bb8eb3]
 mongod(_ZN5mongo18WiredTigerKVEngine15_openWiredTigerERKNSt7__cxx1112basic_stringIcSt11char_traitsIcESaIcEEES8_+0x2D1) [0x55c2e5b90161]
 mongod(_ZN5mongo18WiredTigerKVEngineC1ERKNSt7__cxx1112basic_stringIcSt11char_traitsIcESaIcEEES8_PNS_11ClockSourceES8_mbbbb+0x63F) [0x55c2e5b9611f]
 mongod(+0xAC6149) [0x55c2e5b78149]
 mongod(_ZN5mongo23initializeStorageEngineEPNS_14ServiceContextENS_22StorageEngineInitFlagsE+0x552) [0x55c2e62613a2]
 mongod(+0x993251) [0x55c2e5a45251]
 mongod(_ZN5mongo11mongoDbMainEiPPcS1_+0x10DB) [0x55c2e5b2d94b]
 mongod(main+0x9) [0x55c2e5ac3a39]
 libc.so.6(__libc_start_main+0xF0) [0x7fa294fc7830]
 mongod(_start+0x29) [0x55c2e5b2bab9]
-----  END BACKTRACE  -----


texts: Possibility on huge MySQL database replication

texts:
I have a mysql database with size 2.4TB which has lots of reads and writes happening continuously. I want to replicate this database as a backup.
But it's almost impossible to get a dump from this database with it's size. Is it possible to create a slave replica without a dump of the master?


texts: How to create some GIN index concurrently in Postgresql

texts:
I have a large table: CREATE TABLE hh(h int8[] not null, file int8 not null), and GIN-index over h field (CREATE INDEX ON hh USING gin(h)). Index was created is about 8 minutes, so I decide to split this big table into two or more tables, and create indexes over all this tables.

I assumed that these indexes will be created in parallel and I get about double speedup (slightly less because of indexes and tables places on one disk). I assumed that for two tables I got 4-5 minutes (instead of 10 minutes for one table).

But that did not happen! Both indexes created 8-9 minutes, not 4-5 mins.

I try parallel creation of B-tree of indexes over other my big tables, and yes, PostgreSQL can create such indexes in parallel. So I assumed that works for GIN too.

I have enough RAM for indexes: 32GB RAM. Original table has 1Gb size, and original index 3Gb size. Splitted tables has 0.5Gb size and index 1.3Gb size.
maintenance_work_mem = 16Gb, work_mem = 16Gb.

I also try to put second table and index in another tablespace (located in other physical disk), but acceleration was very small (7.5 mins vs 8 mins). Why??

So, how to create GIN-indexes in parallel? Is this possible?


texts: Cannot connect using tnsnames.ora but can connect by substituting its details

texts:
I'm setting up a minimal Oracle Instant Client on a non-Oracle Linux host from zip files (as per https://docs.oracle.com/database/121/SQPUG/apd.htm#SQPUG157) and have installed

instantclient-basiclite-linux.x64-12.1.0.2.0.zip
instantclient-sqlplus-linux.x64-12.1.0.2.0.zip

(note: no tnsping is available).

I've added the following environment variables:

ORACLE_SID=prod; export ORACLE_SID
ORACLE_HOME=/local/share/bin/oracle_client/instantclient_12_1; export ORACLE_HOME
SQLPLUS=$ORACLE_HOME/sqlplus; export SQLPLUS
LD_LIBRARY_PATH=$ORACLE_HOME:${LD_LIBRARY_PATH}; export LD_LIBRARY_PATH
TNS_ADMIN=/local/share/oracle/network; export TNS_ADMIN

The $TNS_ADMIN/tnsnames.ora file is provided by an Oracle host (I cannot change it), is readable from this non-Oracle host, and contains no syntax errors (it is in use by Oracle hosts).  The specific entry looks much like this:

PROD.DOMAIN =
 (DESCRIPTION =
  (ADDRESS_LIST =
   (ADDRESS = (PROTOCOL = TCP)(HOST = prod.domain)(PORT = 1234))
  )
 (CONNECT_DATA =
  (SERVER = DEDICATED)
  (SERVICE_NAME = prod)
 )
)

That said, I cannot connect on the command line using the preferred method for us:

$SQLPLUS user@prod
SQL*Plus: Release 12.1.0.2.0 Production on Mon Dec 14 14:45:22 2015

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

Enter password:
ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified

suggesting that tnsnames is not being found or parsed or... ? I believe I have everything in place as per "Connecting to a Database with SQL*Plus Instant Client" in the referenced document at the top.

Note that I can connect if I use the host name itself:

$SQLPLUS user@\"//prod.domain:1235/prod\"

or the details from tnsnames itself (proving good syntax):

$SQLPLUS user@"(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=prod.domain)(PORT=1234)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=prod)))"

Any ideas?

My current workaround is the third method using Perl to parse tnsnames into a set of one-liners and grep for the instance I want but it'd be nice to know why this isn't working and how to fix it.

Thanks.


texts: changing server roles in streaming replication

texts:
I want to implement streaming replication using postgresql-9.2.4 in my reporting application.  I came stuck on the below scenario.

create master and slave with synchronous replication which is working fine.
then I brought down master and brought up slave as master using trigger file on slave side manually.
fired log in new master which is working fine.

Now my question is that how can I bring up the old master as slave so that it points to new master without taking base backup or using rsync?

My reporting application will contain large data (approx 50 TB) and more. If I take base backup or rsync feature it will surely take more time to sync old master to new master .

Is there any other way or third party tool which help me to avoid base backup?


texts:
I'm looking for a solution in oracle 12c to deny INSERT, UPDATE and DELETE statements on tables.

I would only provide SELECT privileges and exec rights to execute procedures which INSERT, UPDATE or DELETE rows from tables. So I can ensure clean rows.

It there a way to implement triggers to deny naked sql statement access?


texts:
I know the query execution order for simple queries.

However, I get confused understanding the exact execution order in this query :

select
      M.DateKey  As DateKey ,
      PersonnelCode As PersonnelCode,
      Isnull((select top 1 BranchCode
                  from [Financial].[viwPersonnelLocation]
              where PersonnelCode = p.PersonnelCode and DateKey <= M.DateKey
                  order by DateKey desc)
                  ,p.BranchCode) As BranchCode,

          Isnull((select top 1 CertficateCode
                   from [Financial].[viwPersonnelEducation]
                   where PersonnelCode = p.PersonnelCode and DateKey <= M.DateKey
                   order by DateKey desc)
                   ,p.Educationcode) As Educationcode

    from (select DateKey,MonthOfYearKey,EDATE
          from [Common].[DimTime]
          where LastDayOfMonthKey = 1) M

                Inner Join

               [Financial].[viwPersonnelInfo] As P
                   on m.DateKey >= p.DateOfEmployeement
    where (m.DateKey <= p.DateOfLeave) or (p.DateOfLeave='' or p.DateOfLeave is null) and m.EDATE <= getdate()


texts:
In my system there are two tables called ‘faculty’ and ‘faculty_assets’.

I need to delete faculty by faculty from the ‘faculty’ table. But system should allow deleting the faculty when only faculty_assets table empty. Otherwise It should not allows to delete the faculties from ‘faculty’ table.

Can anyone modify below sql query?  (‘fac_id’ is the primary key of ‘faculty’ table )

function removeFaculty($fac_id){
    $conn=new connection();
    $sql="delete from faculty where fac_id='$fac_id'";
    $result=$conn->query($sql);
    return $result;

}


texts: How to give the least permissions for CRUD-managing and running SQLServerAgent jobs as well as Maintenance Plans?

texts:
Follow up to my question "What's the owner of SqlServerAgent job for?"

Basically, I am trying to understand how to create a user-login with least privileges for CRUD-maintaining SQLServerAgent jobs based on SQL Server Maintenance plans.

I've just checked on SQL Server 2008 R2 that SQLAgentOperatorRole, which is the most privileged role among SQL Server Agent Fixed Database Roles, doesn't have any access (even to view):

the Management\Maintenance Plans

(as well as) to

Management\Data Collection,
Management\Resource Governor)

which makes it quiet useless for maintaining SQLServerAgent jobs baased on maintenance plans (my case) ...

So, how to create a user CRUD-maintaining and running SQLServer Agent jobs as well as CRUD-managing Maintenance Plans?

Also, I'd like to better understand the phrase from msdn "Give Others Ownership of a Job":

  Assigning a job to another login does not guarantee that the new owner
  has sufficient permission to run the job successfully

What does guarantee that non-sysadmin role user as owner of a job would have sufficient permissions to run an SQL Server Agent job successfully?


texts: Should I combine these tables into one, or keep them seperate?

texts:
In a question/answer forum component I am writing as part of a site, I have two tables that theoretically look like this(that is, I want said data kept for each entity):

THREAD                 POST
thread_id              post_id
type_id (question)     writer_id
writer_id              thread_id
title                  parent_id
body (optional)        post_body
followers              date
merge_id               upvotes
date                   dnvotes
upvotes                replies
dnvotes                views
replies                post_type_id (e.g. answer, comment to an answer etc.)
views
anonymous

The only real differences between these entities are merge_id (if a question is merged into another one, this id will reference said question_id), followers, and title. Combining them into one seems a bit cumbersome and bloated to me, and seems to be starting off a bit denormalized I think. And, if they're one, both body and title would have to be be nullable which they shouldn't (I know there can be safeguards in the code).

I also thought of having thread consist of only the attributes not in post which would look like:

THREAD
thread_id (or post_id which would be primary and foreign key refing POST)
title
merge_id
followers

but that doesn't feel quite right either.

Also, if it makes any difference, I plan to have the questions displayed along with the highest ranking answer quite often, in the style of quora.

Any design advice would be much appreciated.

(also, I am keeping them someone generic (thread/post) so that if needed I can use these tables for a blog or whatever else later on.)


texts:
i have now setup a Master => Slave setup, after hard fightning its now working and this is great.

the next step is, how can tell the slave if i create a new table/stroed procured or function on my Master its need to copy that to?

right now i need to run my sync script for on the master and the slave, i think if i got a lot of slaves this will be a big problem, so i hope there are a friendly person there can explain what to do now?

so what i need is i'm only need to consentrat about my Master setup, and lat the slaves copy every thing from my master and not only the row's i, can i that? and how.


texts: How to fill/insert fake data into a MS Sql Server

texts:
I'm trying to test out some configuration settings for an MS Sql Server FILEGROUP and to do this I wish to fill my FILEGROUPdisk up so it will start populating the 2nd FILEGROUP.

I've just created a dummy table that on the PRIMARY FILEGROUP and have the following simple sql to insert some data. It's just taking so long and filling it slowly. Are there any better ways to quickly fill a table with junk data that really uses up a lot of hard disk space?

--IF OBJECT_ID('dbo.__RandomData', 'U') IS NOT NULL
--BEGIN
--    DROP TABLE dbo.__RandomData;

--    CREATE TABLE dbo.__RandomData (
--        RowId INT IDENTITY(1,1) NOT NULL,
--        SomeInt INT,
--        SomeBit BIT,
--        SomeVarchar VARCHAR(10),
--        SomeDateTime DATETIME,
--        SomeNumeric NUMERIC(16,2) )
--END
--GO

DECLARE @count INT
SET @count = 1

WHILE @count <= 1000000
BEGIN
INSERT INTO dbo.__RandomData
    SELECT    @count,
            CASE WHEN DATEPART(MILLISECOND, GETDATE()) >= 500 THEN 0 ELSE 1 END [SomeBit],
            CHAR((ABS(CHECKSUM(NEWID())) % 26) + 97) + CHAR((ABS(CHECKSUM(NEWID())) % 26) + 97) +
            CHAR((ABS(CHECKSUM(NEWID())) % 26) + 97) + CHAR((ABS(CHECKSUM(NEWID())) % 26) + 97) +
            CHAR((ABS(CHECKSUM(NEWID())) % 26) + 97) + CHAR((ABS(CHECKSUM(NEWID())) % 26) + 97) +
            CHAR((ABS(CHECKSUM(NEWID())) % 26) + 97) + CHAR((ABS(CHECKSUM(NEWID())) % 26) + 97) +
            CHAR((ABS(CHECKSUM(NEWID())) % 26) + 97) + CHAR((ABS(CHECKSUM(NEWID())) % 26) + 97) [SomeVarchar],
            DATEADD(MILLISECOND, (ABS(CHECKSUM(NEWID())) % 6000) * -1,
                DATEADD(MINUTE, (ABS(CHECKSUM(NEWID())) % 1000000) * -1, GETDATE())) [SomeDateTime],
            (ABS(CHECKSUM(NEWID())) % 100001) + ((ABS(CHECKSUM(NEWID())) % 100001) * 0.00001) [SomeNumeric]
    SET @count += 1
END


texts:
I have a database that is in production, and in some instances will have millions of records.  It has been requested that we increase the sizes of several columns, which is for the most part pretty easy.  But in one instance, we have a computed column that depends on the column I need to resize.  I've come up with this SQL script to make the change:

/**** Drop the index so we can drop and recreate the column ****/
IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[my_table]') AND name = N'column_index')
DROP INDEX [column_index] ON [dbo].[my_table] WITH ( ONLINE = OFF )
GO

ALTER TABLE my_table DROP COLUMN computed_column
ALTER TABLE my_table ALTER COLUMN main_column NVARCHAR(100)
ALTER TABLE my_table ADD computed_column AS (dbo.ComputeColumn(main_column)) PERSISTED
GO

/**** Recreate the index ****/
CREATE NONCLUSTERED INDEX [column_index] ON [dbo].[my_table] ([computed_column] ASC)
GO

The problem is, besides instances where there are millions of rows and it will likely create a heavy load on the DB server; I have to be able to run this script while the code is still being used.  If I drop the computed column, then any code that references that column will fail until it's recreated.

So I've been asked to find an alternate way to increase the column size of main_column without impacting computed_column.  Can that be done?


texts:
We have a query that we run to import a Master Do Not Call List into our database with about 200 million records.

We first mark the existing numbers in the database table with an expiration date.  This process takes about 19hrs or more, and eats up the log files, and tempdb log files.

The second part of the query is the import from a text file located on the c: drive.  This part also takes quite sometime, and last time SQL Studio crashed so the query tanked and did not finish.

Below is the query that we run:

To give some background on the server setup:
Windows 2016 standard
intel xeon 8cores 2.59ghz
128gb ram
sql 2016 standard

Data volume located on 3par 8200 SAN spinning drives.

Log volume located on 3par 8200 SAN spinning drives (current size is now 1.46tb because 3 times we ran out of space)

Temdb data and log volumes located on 3par SAN as well. (one volume)

Is there a better way to run this query and make it more efficient?  Should we create a job with two steps one for the update expiration, and second for bluk insert? Should we move data and log volumes to SSDs on the 3par...currently they are located on spinning drives.

What do you think we can do to make the process more efficient?

Any information would be greatly appreciated!
Thanks!


texts: Invalid sync source in mongodb config server

texts:
I have 3 config servers 1 primary and 2 secondaries i have received Waring alerts
in secondary servers as

  Log File Monitoring : key = error, File = /var/log/mongodb/mongod.log
  (mgdb09:log[/var/log/mongodb/mongod.log,@mongodb_error,UTF-8]):
  2018-05-05T05:42:08.913+0900 W REPL     [rsBackgroundSync] Fetcher
  stopped querying remote oplog with error: InvalidSyncSource: sync
  source mgdb08:27019 (last visible optime: { ts: Timestamp
  1525466619000|1, t: 24 }; config version: 1; sync source index: -1;
  primary index: 1) is no longer valid

  Log File Monitoring : key = error, File = /var/log/mongodb/mongod.log
  (mgdb07:log[/var/log/mongodb/mongod.log,@mongodb_error,UTF-8]):
  2018-05-05T05:37:19.219+0900 W REPL     [rsBackgroundSync] Fetcher
  stopped querying remote oplog with error: InvalidSyncSource: sync
  source mgdb08:27019 (last visible optime: { ts: Timestamp
  1525466619000|1, t: 24 }; config version: 1; sync source index: -1;
  primary index: 2) is no longer valid

  . Log File Monitoring : key = error, File =
  /var/log/mongodb/mongod.log
  (mgdb09:log[/var/log/mongodb/mongod.log,@mongodb_error,UTF-8]):
  2018-05-05T05:42:13.911+0900 I REPL     [SyncSourceFeedback]
  SyncSourceFeedback error sending update to mgdb08:27019:
  InvalidSyncSource: Sync source was cleared. Was mgdb08:27019

and later on one of the secondary became primary

what cause this kind of issue what action do i need


texts: Temp Table Filtered Index Creation is Blocked When Stored Procedure is Run Multiple Times Concurrently

texts:
Are there any known blocking issues in SQL Server 2014 that are specific to creating one or more filtered indexes on a temp table after the initial table creation? (i.e. not an inline index creation)

I am not permitted to post the actual code; so, I have changed table/column names to protect the innocent.  The representative but very rough logic below is included at the beginning of a stored procedure.

The blocking occurs when the stored procedure is run multiple times concurrently, I believe.  According to my DMV query, the stored procedure is being blocked on the 1st nonclustered index creation statement.

CREATE TABLE #temp_table_name_goes_here (
    [the_first_col] BIGINT NULL
    ,[the_second_col] INT NULL
    ,[another_col] VARCHAR(20) NULL
    );

CREATE INDEX tmp_indx_temp_table_name_goes_here_1 ON #temp_table_name_goes_here (the_first_col)
WHERE the_first_col IS NOT NULL;

CREATE INDEX tmp_indx_temp_table_name_goes_here_2 ON #temp_table_name_goes_here (the_second_col)
WHERE the_second_col IS NOT NULL;


texts: Why does outer apply cause a broadcast move?

texts:
I've got an outer apply with a condition on the distribution keys.

select e.a
      ,e.b
      ,p1.c
  from e
  outer apply
  (
    select top 1
    p.DateStamp
    from p
    where e.distributionKey = p.distributionKey
    and p.client = e.client
    and p.DateStamp > e.DateStamp
    order by p.DateStamp
  )
  as p1;

Using Explain I can see that this causes a broadcast move

<?xml version="1.0" encoding="utf-8"?>
<dsql_query number_nodes="1" number_distributions="60" number_distributions_per_node="60">
    <sql></sql>
    <dsql_operations total_cost="9047512.32" total_number_operations="5">
        <dsql_operation operation_type="RND_ID">
            <identifier>TEMP_ID_1231</identifier>
        </dsql_operation>
        <dsql_operation operation_type="ON">
            <location permanent="false" distribution="AllComputeNodes" />
            <sql_operations>
                <sql_operation type="statement"></sql_operation>
            </sql_operations>
        </dsql_operation>
        <dsql_operation operation_type="BROADCAST_MOVE">
            <operation_cost cost="9047512.32" accumulative_cost="9047512.32" average_rowsize="308" output_rows="122396000" GroupNumber="12" />
            <source_statement></source_statement>
            <destination_table>[TEMP_ID_1231]</destination_table>
        </dsql_operation>
        <dsql_operation operation_type="RETURN">
            <location distribution="AllDistributions" />
            <select></select>
        </dsql_operation>
        <dsql_operation operation_type="ON">
            <location permanent="false" distribution="AllComputeNodes" />
            <sql_operations>
                <sql_operation type="statement">DROP TABLE [tempdb].[dbo].[TEMP_ID_1231]</sql_operation>
            </sql_operations>
        </dsql_operation>
    </dsql_operations>
</dsql_query>

However, the first line of my where clause should mean the query is distribution aligned

where e.distributionKey = p.distributionKey

Why is this not the case?


texts:
I'm new in here and i don't really know where to start but here is my concern. I need to backyp a MySQL DB running under windows once a week. For e.g every friday i need to backup and transfer to another server the MYSQL DB created on the Thursday.

I have the DB created with the following naming : YYMMDD_HHMM_dbname.

i've tried loads of mysqldump options so far but can't get that specific DB that i would like too.

I would higly appreciate if you guys could help me on that.

With thks..


texts:
A multi-billion-row fact table in our database has 10 measures stored as int columns. The value ranges for some of these columns won't ever be above the +/-32K range of a smallint. To save I/O, we're investigating whether it's practical to store these columns as smallint instead of int.

But we're concerned about what problems might crop up from doing this, including:

SUM(SomeSmallInt) will frequently overflow, so many queries would have to be rewritten to cast these smallints to ints before aggregating, e.g. SUM(CAST(SomeSmallInt as int)).
This table is read in many places, so changing the data type of these columns might involve a lot of review, change, and testing.

So we're wondering if there's a lower-cost solution that would store as smallint but expose the colunms as ints to readers. Like this:

create smallint "storage" columns that the table's (only) writer will use, but otherwise no other client will care about.
to support existing readers, create int-typed computed columns that are the same names as the original int columns.

What are the pros and cons of this approach? What can go wrong?  Is there a better way to reduce storage & I/O without causing problems with overflow and requiring existing readers to be rewritten?


texts: How to handle uniqueness of many large columns in SQL Server?

texts:
I have the following table:

CREATE TABLE dbo.Document
(
    DocumentId int IDENTITY(1,1) NOT NULL CONSTRAINT PK_DocumentPRIMARY KEY CLUSTERED,
    [Timestamp] datetime2(7) NOT NULL CONSTRAINT DF_Document_Timestamp  DEFAULT (getdate()),
    CreatedBy nvarchar(128) NOT NULL CONSTRAINT DF_Document_CreatedBy  DEFAULT (dbo.getCurrentUser()),
    MonthId int NOT NULL,
    TimeModeId int NOT NULL CONSTRAINT FK_Document_TimeMode REFERENCES usr.TimeMode,
    Key1 bit NOT NULL,
    Key2 int NULL,
    Key3 varchar(max) NULL,   -- sometimes above 8000chars
    Key4 varchar(max) NULL,   -- sometimes above 8000chars
    Key5 varchar(max) NULL,   -- sometimes above 8000chars
    Key6 varchar(max) NULL,   -- sometimes above 8000chars
    Key7 varchar(max) NULL,   -- sometimes above 8000chars
    Key8 int NOT NULL,
    CONSTRAINT FK_Document_BrandType FOREIGN KEY(Key8) REFERENCES dbo.BrandType (Key8),
)

Although I have insisted to find a better natural identifier, I had to deal with the following natural unique tuple:

MonthId, TimeModeId, Key1, ... , Key8

This is way too large for an UNIQUE index (max 900 bytes in SQL Server 2014 or less), so I had to come up with something. My idea is to compute a hash for these columns, so I had a PERSISTED COMPUTED columns as above:

FiltersHash  AS (hashbytes('SHA2_256',(
        (((((((((((((((
            (CONVERT(varchar(10),MonthId)+'|')
            + CONVERT(varchar(4),TimeModeId))
            +'|')+CONVERT(varchar(4),Key1))
            +'|')+isnull(CONVERT(varchar(max),Key2),''))
            +'|')+isnull(CONVERT(varchar(max),Key3),''))
            +'|')+isnull(CONVERT(varchar(max),Key4),''))
            +'|')+isnull(CONVERT(varchar(max),Key5),''))
            +'|')+isnull(CONVERT(varchar(max),Key6),''))
            +'|')+isnull(CONVERT(varchar(max),Key7),''))
            +'|')+isnull(CONVERT(varchar(4),Key8),''))
        ) PERSISTED,
CONSTRAINT UQ_Document_FiltersHash UNIQUE NONCLUSTERED (FiltersHash),

It proved useful because, through a convoluted scenario, the application tried to duplicate a document.

Question: is this solution a good one or are there simpler or more efficient solutions to the large width uniqueness problem?

Note: In my application, I can safely ignore the collisions (if it ever happens, the consequences are rather small). Thanks to Aaron Bertrand for pointing out.


texts: If I have new data like new users and tables created after backing up  database with RMAN , in case of restoring database will the operation affect my new data made after taking the backup?


texts:
@SQLZim helped me on my ? earlier and the syntax works for about 90% of my cases, but the 10%, I can not say the same for.  Let's use sample DDL below, and the query produces accurately for emp1 but not for emp2

My Expected output would be emp1 = .4050 and emp2 = .2761 however the query results produce emp1 = .4050 BUT emp2 = .6276

Why the discrepancy in calculations?  For further breakdown, this is the calculation I am trying to achieve

emp1 = (1111-(661+(0*.75)))/1111 = .4050
emp2 = (1954.85-(1120.65+(392.7*.75)))/1954.85 = .2761

Which should lead to the results above, however if you look at my DDL below I do not get that outcome, I get

employee    AnnualSales DeptSales   Tax     SomeCalc
emp1        1111.00     661.00      0.00    0.405040
emp2        1954.85     1120.65     392.70  0.627618

DDL

Declare @Calc Table
(
  employee varchar(100)
  ,annualsales decimal(10,2)
  ,dep1sales decimal(10,2)
  ,dep2sales decimal(10,2)
  ,dep3sales decimal(10,2)
  ,dep4sales decimal(10,2)
  ,dep5sales decimal(10,2)
  ,tax decimal(10,2)
)

Insert Into @Calc (employee, annualsales, dep1sales, dep2sales, dep3sales, dep4sales, dep5sales, tax) Values
('emp1', '1111.00', '26.12', '83.00', '16.55', '21.77', '87.08', null)
,('emp1', null, '28.07', '11.00', '101.24', '47.03', '76.19', null)
,('emp1', null, '35.25', '7.00', '33.64', '43.54', '43.52', null)
,('emp2', '1954.85', NULL, '432.45', NULL, NULL, NULL, NULL)
,('emp2', NULL, NULL, '688.20', NULL, NULL, NULL, '392.70')

Select
employee
,AnnualSales=sum(isnull(annualsales,0))
,DeptSales    =sum(isnull(dep1sales,0)+isnull(dep2sales,0)+isnull(dep3sales,0)+isnull(dep4sales,0)+isnull(dep5sales,0))
,Tax        =sum(isnull(tax,0))
,[SomeCalc] =(sum(isnull(annualsales,0))
         -sum(isnull(dep1sales,0)+isnull(dep2sales,0)+isnull(dep3sales,0)+isnull(dep4sales,0)+isnull(dep5sales,0))
         +sum(isnull(tax,0))
        )
        /nullif(sum(isnull(annualsales,0)),0)
from @Calc
group by employee
order by employee asc

How should my query be altered in order for the above expected output to be produced?


texts:
Hello I have a table called Workers in my database. Also I have a stored procedure that returns a table with Id, xml Data and xml Schema by Worker.Id. I want to select all Worker.Id values and execute this procedure for each Worker.id. Here is some script:

create table #tmpXmlreturn
(
    Id int,
    Data xml,
    [Schema] xml
)

select dbo.Workers.Id as Id,
        Exec dbo.GetBranchByWorkerId Id,
into #temptable from
dbo.Workers

If you need me to provide more information please tell me.


texts: How to speed up looped INSERT INTO statements?

texts:
I currently use the following statement, for 10,000 rows it takes about 150 seconds. I tried removing the index on the target table, but this didn't help. Running the loop without the INSERT INTO takes less then 50ms. I need it to update about 300 million rows and I can't really wait 52 days (!) for it to complete.

Bottom line of the following update query is that I need to go over each row, perform calculations on a VARBINARY and extract proper values from it (we need to get rid of the packed VARBINARY fields), and store that in a new table.

FETCH NEXT FROM LocCities INTO @LocCity
WHILE (@@FETCH_STATUS = 0)
BEGIN
    -- several sets, removed calculations for clarity
    SET @LocationId = Calculation1()
    SET @CityId = Calculation2()

    IF(@LocCity <> 0)
    BEGIN
        -- left out an inner loop here on the VARBINARY based on its length
        INSERT INTO LocationCities (LocationId, CityId)
        VALUES (@LocationId, @CityId)
    END
    FETCH NEXT FROM RespCursor INTO @TuningRow
END

I understand that I can use the WITH keyword with table hints, but I am not sure what to use. I expect the final update query to run in several hours, and hope there's a way to do that. I really can't wait almost two months ;).

Isn't there something similar like BULKINSERT that I can use?


texts:
Is it possible to configure SQL Server user so that he doesn't have access to DDL statements but has access to run TRUNCATE TABLE commands. I know that behind the scenes it's a DDL.

I wasn't able to find this on search engines.


texts: Why do roles and schemas have (mostly) the same name?

texts:
When I create a database and add a user I get an interface with options. On that interface I can choose:

Owned Schemas
Membership

This question on dba.stackexchange HERE says schemas and roles are not related. This has confused me in the past since they have the same names. Why is this?

Also. In the Schemas folder, I see dbo, INFORMATION_SCHEMA, and sys. But in the user dialogue, under 'Owned Schemas' I don't see these options. Why is this?


texts: Configure Type 2 SCD on existing tables in SQL Server

texts:
I've got two tables, FACILITIES and CONTRACTS. It's a 1:M facility->contract. The tables have unique keys generated by the upstream system and are truncate-reload every night. Neither table has any primary key or foreign key/Unique key enforcement. Users typically join on the facility ID which exists on the contracts table.
I am trying to set up SCD Type 2 for these two tables and have created the appropriate and working code using the MERGE command and have tested it successfully. I know that I will need to set up new primary/foreign keys since Type 2 will cause the business key to be duplicated (what they are currently using as a unqiue identifier).
However, I'm unsure how to handle existing data. Both tables have several thousand rows, so I don't know how to link existing contracts and facilities using the new PK/FK combos I set up. I know that going forward they will work fine, but not sure about now.
How do I link the existing data with the new PK/FK setup so the associations for the new keys are correct for SCD going forward?
Here's how they look now:

FacilityID
Facility_PK (the one I set up)

12345
1

23456
2

ContractID
Contract_PK
Facility ID
Facility_FK

7890
1
12345
???

4444
2
12345
???

2345
3
23456
???

EDIT: Further context:
Once the Slowly Changing Dimension logic is implemented, the existing ID fields will be duplicated and really considered business keys.

FacilityID
Facility_PK (the one I set up)
USER
Active Indicator

12345
1
User A
0

23456
2
User A
1

12345
3
User B
1

Now there is no longer a unique link to the CONTRACTS table. So I can add in a PK to this FACILITIES table and keep them unique, but how do I update the data in the CONTRACTS Foreign key so that this works properly?
If you were to now join based on FacilityID, you'd get back both records and would be unable to tell which contract is associated with which point-in-time facility record.


texts: How to use character classes in a Postgres regex select?

texts:
I'm trying to create a query that selects strings that contain the number 21, without hitting strings such as 210 or 121. My efforts below have shown my progress, in that I've managed to apply a constraint for the beginning of the word, but I'm stumped in terms of providing delimiter at the end.
I would like to match 21 and 21X where X is a nondigit character.
Normally I'd use [] match both situations where there is a nondigit character or the end of the string, but I'm stymied on this.

I can filter based on the start of the word and character classes work:

# SELECT "buildings"."street_name" FROM "buildings"  WHERE (street_name ~ '\m21[s0]');
 street_name
-------------
 21st
 210th

And here I can successfully use the nondigit (\D) constraint:

 # SELECT "buildings"."street_name" FROM "buildings"  WHERE (street_name ~ '\m21\D');
 street_name
-------------
21st

But when I try to use the nondigit constraint to define a class i.e. \m21[\D] (in an effort to match the string '21') it will fail.

How can I define a regex that matches my target (in the case 21) with a non-digit suffix or string terminator ?


texts: Add foreign key constraint to an existing table column, referencing a column in a newly created table

texts:
Let's say I have one table defined as this

CREATE TABLE sales (
    agent_id   integer references agents(agent_id),
    sale_date  date,
    amout      numeric(10,2)
);

Then an ETL process fills this table with data

INSERT INTO sales VALUES
    (1, '2019-01-01', 1031.11),
    (1, '2019-01-02', 525.44),
    (1, '2019-01-03', 323.99);

But later, I need to add a table with actual processed dates, such as

CREATE TABLE dates (
    date_idx   date primary key,
    year       integer not null,
    month      integer not null,
    day        integer not null
);

and want to add a foreign key constraint on the old table:

ALTER TABLE sales
    ADD CONSTRAINT sales_date_fk FOREIGN KEY (sale_date)
    REFERENCES dates (date_idx);

Naturally, I get the following error:

  ERROR: insert or update on table "sales" violates foreign key constraint "sales_date_fk" Detail: Key (sale_date)=(2019-01-01) is not present in table "dates".

I know I can work around this by deleting/truncating all data in sales or prefilling dates before adding the constraint, but I would prefer not to if I can avoid it.

Is there any way to accomplish this?

I'm using Postgres 11, but this fiddle running 9.6 shows what I've tried to explain here.


texts: When can SARGable predicates be pushed into a CTE or derived table?

texts:
Sandbag

While working on Top Quality Blog Posts®, I came across some optimizer behavior I found really infuriating interesting. I don't immediately have an explanation, at least not one I'm happy with, so I'm putting it here in case someone smart shows up.

If you want to follow along, you can grab the 2013 version of the Stack Overflow data dump here. I'm using the Comments table, with one additional index on it.

CREATE INDEX [ix_ennui] ON [dbo].[Comments] ( [UserId], [Score] DESC );

Query One

When I query the table like so, I get an odd query plan.

WITH x
    AS
     (
         SELECT   TOP 101
                  c.UserId, c.Text, c.Score
         FROM     dbo.Comments AS c
         ORDER BY c.Score DESC
     )
SELECT *
FROM   x
WHERE  x.Score >= 500;

The SARGable predicate on Score isn't pushed inside the CTE. It's in a filter operator much later in the plan.

Which I find odd, since the ORDER BY is on the same column as the filter.

Query Two

If I change the query, it does get pushed.

WITH x
    AS
     (
         SELECT   c.UserId, c.Text, c.Score
         FROM     dbo.Comments AS c
     )
SELECT TOP 101 *
FROM   x
WHERE  x.Score >= 500
ORDER BY x.Score DESC;

The query plan changes, too, and runs much faster, with no spill to disk. They both produce the same results, with the predicate at the nonclustered index scan.

Query Three

This is the equivalent of writing the query like so:

SELECT   TOP 101
         c.UserId, c.Text, c.Score
FROM     dbo.Comments AS c
WHERE c.Score >= 500
ORDER BY c.Score DESC;

Query Four

Using a derived table gets the same "bad" query plan as the initial CTE query

SELECT *
FROM   (   SELECT   TOP 101
                    c.UserId, c.Text, c.Score
           FROM     dbo.Comments AS c
           ORDER BY c.Score DESC ) AS x
WHERE x.Score >= 500;

Things get even weirder when...

I change the query to order the data ascending, and the filter to <=.

To keep from making this question overlong, I'm going to put everything together.

Queries

--Derived table
SELECT *
FROM   (   SELECT   TOP 101
                    c.UserId, c.Text, c.Score
           FROM     dbo.Comments AS c
           ORDER BY c.Score ASC ) AS x
WHERE x.Score <= 500;

--TOP inside CTE
WITH x
    AS
     (
         SELECT   TOP 101
                  c.UserId, c.Text, c.Score
         FROM     dbo.Comments AS c
         ORDER BY c.Score ASC
     )
SELECT *
FROM   x
WHERE  x.Score <= 500;

--Written normally
SELECT   TOP 101
         c.UserId, c.Text, c.Score
FROM     dbo.Comments AS c
WHERE c.Score <= 500
ORDER BY c.Score ASC;

--TOP outside CTE
WITH x
    AS
     (
         SELECT   c.UserId, c.Text, c.Score
         FROM     dbo.Comments AS c
     )
SELECT TOP 101 *
FROM   x
WHERE  x.Score <= 500
ORDER BY x.Score ASC;

Plans

Plan link.

Note that none of these queries take advantage of the nonclustered index -- the only thing that changes here is the position of the filter operator. In no case is the predicate pushed to the index access.

A Question Appears!

Is there a reason that a SARGable predicate can be pushed in some scenarios and not in others? The differences within the queries sorted in descending order are interesting, but the differences between those and the ones that are ascending bizarre.

For anyone interested, here are the plans with only an index on Score:

DESC
ASC


texts: SQL Server suddenly using a lot of CPU: 300 compilations/s vs 2 batches/s

texts:
A instance of SQL Server Express 2016 that I'm in charge of has suddenly started to use a lot more CPU that usual. It's the backend database for a website, but doesn't see all that many queries due to aggressive caching in the webapp.

Normal CPU usage would be 0-2%, but it's now averaging around 25% (on a VM with 4 logical cores).

Perfmon shows avg batches/s as 2, but compilations/s is a constant 300.

All access to the database is through stored procedures, which mostly involve a single SELECT statement.

How can I find out what it's continually compiling?


texts:
I was not sure what to put in the title. I have a
SCHOOL table, with:

id,
name,
type_id

columns.

A SCHOOL_STUDENT table with:

school_id,
student_id

columns
 and a SCHOOL_TYPE table with

id,
name

columns.

Students can be enrolled in more than one schools. A school can be of only one type. Given a student I want to check if he/she is enrolled in schools with only type(high shcool), and no other schools.I want to check if the student is enrolled in schools with only type(high shcool), and no other schools. If a student is enrolled in two schools and one of them is a high school and one of them is a technology school then I want some negative indication. If both of those schools are high school , i want positive indication. Let's suppose SCHOOL_TYPE has a row with id:3 and name:"high school".

SAMPLE DATA:

SCHOOL table
id:name:type_id

1:'abc school':3
2:'xyz school':4
3:'HEH school':3

SCHOOL_TYPE table
id:name

1:'junior school'
2:'tech school'
3:'High school'

SCHOOL_STUDENT table:
school_id:student_id

1,123
3,123
6, 56

See studentID:123 is enrolled in two schools, with id:1 and id:3. And both of those schools are of type:3, that means I want a success result. If the same student is enroled in more than one school and those schools belong to other types as well (other than typeID:3) then I want a failure.

Expected output for studentID:123 is YES and for studentID:56 is NO because he is enrolled in two schools and one of them is not a high school.

I am trying this but I know it lacks something but what it lacks, I don't seem to work it out.

select
    bc.customer_id, bt.name, count(bt.[name])
from
    school_type st
    join school s on s.type_id = st.id
    join school_student ss on ss.school_id = s.id
where
    ss.student_id = 1234
group by
    ss.student_id, st.name

May be use HAVING? or CASE?

Question UPDATED:


texts: Column in query should be selected according to value from another column

texts:
Assuming I have table1 with following data:
table1
id| coffee_sender | coffee_receiver | received_bag_size | sent_bag_size| price |
21  tux              -1                  m                 -1            45
22  alsa            -1                  -1                 xl            36
23  -1               tux                 l                 -1            51

And I use the following query:
select t1.*
from (
select t.price, t.id
from table1 t
where coffee_sender in (tux, alsa)
or coffee_receiver in (tux, alsa)
and received_bag_size in (m, xl)
or sent_bag_size in (m, xl)) t1

What i want is to only fetch the rows that have coffee_sender/coffee_receiver AND sent_bag_size/received_bag_size filled with a value (rows that match the following conditions):
coffee_sender = tux, sent_bag_size= xl

OR
coffee_receiver = tux, received_bag_size= m

OR
coffee_sender = tux, sent_bag_size = xl AND coffee_receiver = tux, received_bag_size= m

So id = 21 would be excluded from the example table1.
How can i update the above query for this ? Thank you in advance.
Update: to make the question a little more clear:
What i want to see is the actual rows where coffee vendor and size are linked. Each coffee vendor (tux, alsa) has a bag size at a a certain point in time. This will be inserted in the table when shipment is done.
I hope this clarifies what I wanted to ask.